Showing posts with label Oracle EBS Technical Information. Show all posts
Showing posts with label Oracle EBS Technical Information. Show all posts

Sunday, February 04, 2018

Script for finding oracle Forms and Self-Service page file versions

Finding file version from Oracle Forms and Self-Service page

select distinct app_short_name
,b.bug_number
,version,f.filename 
,to_char(prb.creation_date
,'dd-mon-yyyy
,hh24:mi:ss') Patch_date
from
apps.ad_file_versions v
,apps.ad_files f
,apps.ad_patch_run_bug_actions prba
,apps.ad_patch_run_bugs prb
,apps.ad_bugs b
where v.file_id=f.file_id
and f.file_id=prba.file_id
and prba.patch_run_bug_id = prb.patch_run_bug_id
and prb.bug_id=b.bug_id
and prba.patch_file_version_id=v.file_version_id
and lower(f.filename) like lower('<file name>')
order by filename;




Please provide your valuable feedback ............

Tuesday, December 04, 2012

fnd_global.apps_initialize 11i, R12 and SQL

fnd_global.apps_initialize
In Oracle E-Business Suite, it becomes necessary to do this call when an API is called or SQL Query is run from
SQL Plus/TOAD like tools. This ‘fnd_global’ API calls sets the environment properly so that SQL Queries specific to few key base tables return rows back.
This ‘fnd_global’ API call has 3 key parameters
fnd_global.apps_initialize (user_id, responsibility_id, responsibility_application_id)
******************************************************************************
For example, in SQL Plus, this can be called as shown below:

Exec fnd_global.apps_initialize(125,400,700);
Where,
125 – user_id from fnd_user table
400 – responsibility_id from fnd_responsibility table
700 – application_id from fnd_application table
******************************************************************************
Release 12
begin
fnd_global.apps_initialiase(user_id, responsibility_id, responsibility_application_id );
mo_global.set_org_context(81,NULL,'SQLAP');
end;
 ******************************************************************************
Oracle 11i
BEGIN
fnd_global.apps_initialize(user_id, responsibility_id, responsibility_application_id);
END;
 ******************************************************************************

Tuesday, September 04, 2012

Creating a Custom Top in Applications Oracle Release R12:

  Custom Tops are required if you are creating new forms, reports, workflows, OAF pages etc. This allows you to segregate your custom written files from the standard seeded functionality that Oracle Applications provide. Customizations can therefore be preserved when applying patches or upgrades to your environment.
Step By Step Guide to Creating a Custom Application in Oracle Release R12 :
1) Make the directory structure for Custom Pages Top.
cd $JAVA_TOP
cd classes
mkdir classes/xx_hr
mkdir classes/xx_ums
mkdir classes/xx_ums/oracle/apps/
mkdir classes/ xx_ums/oracle/apps/
2) Make the directory structure for Custom Forms and Reports Custom top.
cd $APPL_TOP
mkdir appl/xx_hr
mkdir appl/xx_ums
3) Add the custom module into the environment
Apply ADX.E.1 and add the entry to topfile.txt as a standard product top entry (follow the existing model in the file) Customised environment variables can be added to AutoConfig by using the filename specificed by s_custom_file, which is then called from the APPSORA.env file. If using Forms Listener Servlet, you may also need to add $CUSTOM_TOP to formsservlet.ini in $APACHE_TOP/Jserv/etc
4) Create new tablespace for database objects
create tablespace xx_ums datafile '/emea/oracle/visuk09/visuk09data/xx_ums .dbf' size 10M default storage(initial 10k next 10k)
5) Create schema
create user xx_ums identified by xx_ums
default tablespace xx_ums
temporary tablespace temp
quota unlimited on xx_ums
quota unlimited on temp;
grant connect, resource to xx_ums;
6) Register your Oracle Schema.
Login to Applications with System Administrator responsibility Navigate to Application-->Register
Application = xx_ums Custom
Short Name = xx_ums
Basepath = xx_ums_TOP
Description = xx_ums Custom Application
7) Register Oracle User
Navigate to Security-->Oracle-->Register
Database User Name = xx_ums
Password = xx_ums
Privilege = Enabled
Install Group = 0
Description = xx_ums Custom Application User
8) Add Application to a Data Group
Navigate to Security-->Oracle-->DataGroup
Data Group = xx_ums Group
Description = xx_ums Custom Data Group
Click on "Copy Applications from" and pick Standard data Group, then add the following entry.
Application = xx_ums Custom
Oracle ID = APPS
Description = xx_ums Custom Application
9) Create custom request group
This will act as a placeholder for any custom reports we wish to make available for the Custom Responsibility (which is defined at a later stage)Navigate to Security-->responsibility-->Request
Group= xx_ums Request Group
Application = xx_ums Custom
Code= xx_ums
Description = xx_ums Custom Requests
We will not define any requests to add to the group at this stage, but you can add some now if required.
10) Create custom menu
This will act as a placeholder for any menu items we wish to make available for the Custom Responsibility (which is defined at a later stage) We will create two menus, one for Core Applications and one for Self Service. Navigate to Application-->Menu Menu= xx_ums_CUSTOM_MENU
User Menu Name = xx_ums Custom Application
Menu Type =
Description= xx_ums Custom Application Menu
Seq= 100
Prompt= View Requests
Submenu=
Function = View All Concurrent Requests
Description = View Requests

Seq= 110
Prompt= Run Requests
Submenu=
Function= Requests: Submit
Description = Submit Requests

Menu= xx_ums_CUSTOM_MENU_SSWA
User Menu Name = xx_ums Custom Application SSWA
Menu Type=
Description = xx_ums Custom Application Menu for SSWA
11) Create new responsibility.
One for Core Applications and One for Self Service (SSWA) Navigate to Security-->Responsibility-->Define
Responsibility Name= xx_ums Custom
Application= xx_ums Custom
Responsibility Key = xx_ums CUSTOM
Description= xx_ums Custom Responsibility
Available From= Oracle Applications
Data Group Name= xx_ums Group
Data Group Application = xx_ums Custom
Menu= xx_ums Custom Application
Request Group Name= xx_ums Request Group

Responsibility Name= xx_ums Custom SSWA
Application= xx_ums Custom
Responsibility Key = xx_ums CUSTOMSSWA
Description = xx_ums Custom Responsibility SSWA
Available From= Oracle Self Service Web Applications
Data Group Name= xx_ums Group
Data Group Application = xx_ums Custom
Menu= xx_ums Custom Application SSWA
Request Group Name= xx_ums Request Group
12) Add responsibility to user
Navigate to Security-->User-->DefineAdd xx_ums Custom responsibility to users as required.
13) Other considerations
You are now ready to create your database Objects, custom Reports, Forms, Packages, etc Create the source code files in the xx_ums _TOP directory appropriate for the type of object. For example forms would be located in
$xx_ums_TOP/forms/US or package source code in
$xx_ums_TOP/admin/sql for example. Database Objects, such as tables, indexes and sequences should be created in the xx_ums schema, then you need to
a) Grant all privilege from each custom data object to the APPS schema.
For example: logged in as xx_ums user
grant all privileges on myTable to apps;
b) Create a synonym in APPS for each custom data object
For example: logged in as APPS user
Create synonym myTable for xx_ums.myTable; Other database objects, such as views and packages should be created directly in the APPS schema.

Reservation Type : mtl_supply_demand_source_type

------------------MTL_DEMAND_SOURCE_TYPE---------------------
---------------------------------------------------------------------------------

select LOOKUP_TYPE, LOOKUP_CODE, MEANING
from apps.MFG_LOOKUPS
where LOOKUP_TYPE like 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'
order by LOOKUP_TYPE, LOOKUP_CODE;

Output from Backend (Toad)




 Lookup Codes

LOOKUP_TYPE LOOKUP_CODE MEANING
MTL_SUPPLY_DEMAND_SOURCE_TYPE 1 Purchase order
MTL_SUPPLY_DEMAND_SOURCE_TYPE 2 Sales order
MTL_SUPPLY_DEMAND_SOURCE_TYPE 3 Account number
MTL_SUPPLY_DEMAND_SOURCE_TYPE 4 WIP repetitive schedule
MTL_SUPPLY_DEMAND_SOURCE_TYPE 5 WIP discrete job
MTL_SUPPLY_DEMAND_SOURCE_TYPE 6 Account alias
MTL_SUPPLY_DEMAND_SOURCE_TYPE 7 WIP nonstandard job
MTL_SUPPLY_DEMAND_SOURCE_TYPE 8 Onhand quantity
MTL_SUPPLY_DEMAND_SOURCE_TYPE 9 Reserved sales order
MTL_SUPPLY_DEMAND_SOURCE_TYPE 10 Reserved account number
MTL_SUPPLY_DEMAND_SOURCE_TYPE 11 Reserved account alias
MTL_SUPPLY_DEMAND_SOURCE_TYPE 12 Intransit receipt
MTL_SUPPLY_DEMAND_SOURCE_TYPE 13 Discrete MPS
MTL_SUPPLY_DEMAND_SOURCE_TYPE 14 Repetitive MPS
MTL_SUPPLY_DEMAND_SOURCE_TYPE 15 Onhand reservation
MTL_SUPPLY_DEMAND_SOURCE_TYPE 16 User supply
MTL_SUPPLY_DEMAND_SOURCE_TYPE 17 User demand
MTL_SUPPLY_DEMAND_SOURCE_TYPE 18 PO requisition
MTL_SUPPLY_DEMAND_SOURCE_TYPE 19 Reserved user source
MTL_SUPPLY_DEMAND_SOURCE_TYPE 20 Internal requisition
MTL_SUPPLY_DEMAND_SOURCE_TYPE 21 Internal order
MTL_SUPPLY_DEMAND_SOURCE_TYPE 22 Reserved internal order
MTL_SUPPLY_DEMAND_SOURCE_TYPE 23 WIP Supply Reservation
MTL_SUPPLY_DEMAND_SOURCE_TYPE 24 Flow Schedule
MTL_SUPPLY_DEMAND_SOURCE_TYPE 30 Cycle Count Reservation
MTL_SUPPLY_DEMAND_SOURCE_TYPE 31 Purchasing Supply Reservation
MTL_SUPPLY_DEMAND_SOURCE_TYPE 32 Move Order Demand
MTL_SUPPLY_DEMAND_SOURCE_TYPE 33 Move Order Allocation
MTL_SUPPLY_DEMAND_SOURCE_TYPE 34 Move Order Supply

------------------------------------------------------------------------------------------------------------------

Oracle Application Form Personalization Extraction SQL Script

---  Form Personalization data extraction - --------------
----------------------------------------------------------
Select FPT.APPLICATION_NAME
     , FF.FORM_NAME SOURCE_FORM_NAME, FFT.USER_FORM_NAME, FFT.DESCRIPTION FORM_DESCRIPTION
     , FFF.FUNCTION_NAME, FFFT.USER_FUNCTION_NAME, FFFT.DESCRIPTION FUNCTION_DESCRIPTION
     , FFCR.SEQUENCE PERSONALIZE_RULE_SEQUENCE, FFCR.DESCRIPTION PERSONALIZE_RULE_DESCRIPTION
     , DECODE(FFCR.RULE_TYPE,'F','Form','A','Function') PERSONALIZE_RULE_LEVEL
     , FFCR.ENABLED PERSONALIZE_RULE_ENABLED
     , FFCR.TRIGGER_EVENT  PERSONALIZE_RULE_EVENT, FFCR.TRIGGER_OBJECT
     , FFCR.CONDITION PERSONALIZE_RULE_CONDITION
     , DECODE(FFCS.LEVEL_ID,10,'Industry',20,'Site',30,'Responsibility',40,'User')  CONTEXT_LEVEL
     , DECODE(FFCS.LEVEL_ID,10,'',20,'',30,FRT.RESPONSIBILITY_NAME,40,FU.USER_NAME) CONTEXT_LEVEL_VALUE
      , FFCA.SEQUENCE  ACTION_SEQUENCE
      , DECODE(FFCA.ACTION_TYPE,'P','Property','M','Message','B','Builtin','S','Menu','') ACTION_TYPE
      , FFCA.SUMMARY  ACTION_DESCRIPTION  
      , FFCA.ENABLED  ACTION_ENABLED
      , DECODE(FFCA.LANGUAGE,'*','All','US','American English','AR','Arabic') ACTION_LANGUAGE
      , DECODE(FFCA.ACTION_TYPE,'P',FFCA.OBJECT_TYPE,Null)      ACTION_OBJECT_TYPE
      , DECODE(FFCA.ACTION_TYPE,'P',FFCA.TARGET_OBJECT,Null)    ACTION_TARGET_OBJECT     
      , DECODE(FFCA.ACTION_TYPE,'P',FFCPL.PROPERTY_NAME, Null)  ACTION_PROPERTY_NAME
      , DECODE(FFCA.ACTION_TYPE,'P',DECODE(FFCA.ARGUMENT_TYPE,'B'
      , DECODE(FFCA.PROPERTY_VALUE,5,'False',4,'True')
      , FFCA.PROPERTY_VALUE),Null) ACTION_PROPERTY_VALUE
      , DECODE(FFCA.ACTION_TYPE,'M'
      , DECODE(FFCA.MESSAGE_TYPE,'S','Show','E','Error','W','Warning','H','Hint','D','Debug'),Null) ACTION_MESSAGE_TYPE
      , DECODE(FFCA.ACTION_TYPE,'M',FFCA.MESSAGE_TEXT,Null) ACTION_MESSAGE_TEXT
      , DECODE(FFCA.ACTION_TYPE,'B',FFCA.BUILTIN_TYPE,Null)        ACTION_BUILTIN_TYPE
      , DECODE(FFCA.ACTION_TYPE,'B',FFCA.BUILTIN_ARGUMENTS,Null)   ACTION_BUILTIN_ARGUMENTS
      , DECODE(FFCA.ACTION_TYPE,'B',FFCA.MENU_ARGUMENT_LONG,Null)  ACTION_BUILTIN_PARAMETERS
      , DECODE(FFCA.ACTION_TYPE,'B',FFCA.MENU_ARGUMENT_SHORT,Null) ACTION_BUILTIN_CODE
      ,DECODE(FFCA.ACTION_TYPE,'S',FFCA.MENU_ENTRY,Null)           ACTION_MENU_ENTRY
      ,DECODE(FFCA.ACTION_TYPE,'S',FFCA.MENU_LABEL,Null)           ACTION_MENU_LABEL
      ,DECODE(FFCA.ACTION_TYPE,'S',FFCA.MENU_SEPERATOR,Null)       ACTION_MENU_SEPERATOR
      ,DECODE(FFCA.ACTION_TYPE,'S',FFCA.MENU_ENABLED_IN,Null)      ACTION_MENU_BLOCKS_ENABLED
      ,DECODE(FFCA.ACTION_TYPE,'S',FFCA.MENU_ARGUMENT_SHORT,Null)  ACTION_MENU_ICON_NAME
       
From  FND_APPLICATION FP, FND_APPLICATION_TL FPT
    , FND_FORM FF, FND_FORM_TL FFT
    , FND_FORM_FUNCTIONS FFF, FND_FORM_FUNCTIONS_TL  FFFT
    , FND_FORM_CUSTOM_RULES FFCR , FND_FORM_CUSTOM_SCOPES FFCS, FND_RESPONSIBILITY_TL FRT, FND_USER FU
    , FND_FORM_CUSTOM_ACTIONS FFCA , FND_FORM_CUSTOM_PROP_LIST FFCPL

Where  ----------------APPLICATION
        FP.APPLICATION_ID  = FPT.APPLICATION_ID
    And FPT.LANGUAGE       = 'US'
    ------------------------ FORM
    And FPT.APPLICATION_ID = FF.APPLICATION_ID
    And FF.FORM_ID         = FFT.FORM_ID
    And FFT.LANGUAGE       = 'US'
    ------------------------ FUNCTION
    And FF.FORM_ID         = FFF.FORM_ID
    And FFF.FUNCTION_ID    = FFFT.FUNCTION_ID
    And FFFT.LANGUAGE      = 'US'  
    ------------------------ Custom Rule
    And FF.Form_Name       = FFCR.Form_Name
    And FFCR.FUNCTION_NAME = FFF.FUNCTION_NAME
    ------------------------ Custom Scope
    And FFCR.ID            = FFCS.RULE_ID
    And FFCS.LEVEL_VALUE   = FRT.RESPONSIBILITY_ID(+)
    And FRT.LANGUAGE(+)    = 'US'
    And FFCS.LEVEL_VALUE   = FU.USER_ID(+)
    ------------------------ Custom Actions
    And FFCR.ID            =  FFCA.RULE_ID
    And DECODE(FFCA.ACTION_TYPE,'P',FFCA.PROPERTY_NAME,79) = FFCPL.PROPERTY_ID
    And DECODE(FFCA.ACTION_TYPE,'P',FFCA.OBJECT_TYPE,'ITEM')   = FFCPL.FIELD_TYPE
   
 Order By FFT.APPLICATION_ID, FF.FORM_NAME,FFCR.FUNCTION_NAME
        , FFCR.SEQUENCE, FFCS.LEVEL_ID, FFCS.LEVEL_VALUE, FFCA.SEQUENCE    
-------------------------------------------------------------------------------------------------------------------

Sample Output from Backend (Toad)