How to check if a patch is applied in Oracle E Business Suite.
For R12.2.X eBS version use the below query/script to get the patch information:
SELECT AD_PATCH.IS_PATCH_APPLIED('$release’,'$appltop_id','$patch_no','$language') patch_info
FROM dual;
Below example for single app tier installations:-
SELECT ad_patch.is_patch_applied ('R12', -1,8246403) patch_info
FROM dual;
Expected results as follows:
EXPLICIT = Applied
NOT APPLIED = Not applied Or Aborted.
For 11i or R12.1.X eBS version use the below query/script to get the patch information:-
SELECT *
FROM ad_bugs
WHERE bug_number = '<patch_number>'; --8246403
SELECT *
FROM ad_applied_patches
WHERE patch_name= '<patch_number>'; --8246403
SELECT aap.applied_patch_id,
aap.patch_name,
aap.patch_type,
apd.driver_file_name,
apd.orig_patch_name,
apd.creation_date,
apd.platform,
apd.source_code,
apd.creation_date,
apd.file_size,
apd.merged_driver_flag,
apd.merge_date
FROM AD_APPLIED_PATCHES aap
,AD_PATCH_DRIVERS apd
WHERE aap.APPLIED_PATCH_ID = apd.APPLIED_PATCH_ID
AND aap.PATCH_NAME = '<patch_number>'; --8246403
Showing posts with label Application Object Library - AOL. Show all posts
Showing posts with label Application Object Library - AOL. Show all posts
Wednesday, 10 February 2021
How to check if a patch is applied in Oracle E Business Suite.
Sunday, 5 January 2020
Query to get Oracle Apps Cloned Datetime, Version and URL details
--Query to get cloned date_time from prod of an oracle instance
SELECT xx.resetlogs_time clone_date_time
,xx.name instance_name
,xx.*
FROM v$database xx;
--Query to get the frontend URL from backend
SELECT xx.home_url
,xx.*
FROM icx_parameters xx;
--Query to get Database and Application servers info
SELECT xx.name
,xx.server_type
FROM fnd_app_servers fas
,fnd_nodes fn
WHERE fas.node_id = fn.node_id
SELECT xx.resetlogs_time clone_date_time
,xx.name instance_name
,xx.*
FROM v$database xx;
--Query to get the frontend URL from backend
SELECT xx.home_url
,xx.*
FROM icx_parameters xx;
--Query to get Database and Application servers info
SELECT xx.name
,xx.server_type
FROM fnd_app_servers fas
,fnd_nodes fn
WHERE fas.node_id = fn.node_id
Thursday, 8 August 2019
Define and assign Data Access Set in Oracle Apps.
Data Access Set:
Data access set is used to
restrict balancing segment values or management segment values with read or
write privileges to a particular responsibility or user.
Once we complete a ledger with the
entire ledger options system internally generates a default data access set
with the same name as our primary ledger which is going to give access to full
ledger with read and write privileges. This will be automatically assigned to
the responsibility using GL data access set profile option. The default data access set will give access
to full ledger with read and write privilege. We cannot modify default data access set.
We can also define additional
data access set based on our business requirement. We can define any number of
data access sets but only one data access set can be assigned to general ledger
responsibility by using GL data access set profile option at any point of time.
We cannot perform open/close
periods from a responsibility to which we have a restricted data access set. We
will be allowed to perform open/close periods only those
responsibilities to which we have full ledger access
Access set types:
1.
Full ledger
2. Balancing
segment value
3.
Management segment value
Full
ledger:
Full ledger accesses means
having full read and write access to the ledger and all of its balancing
segment values or management segment values
Balancing
segment value:
If you assign specific balancing
segment values to legal entities and ledgers, you will only be able to use
those balancing segment values during transaction processing and journal entry.
Management segment value:
When
securing management segment values for a ledger, take note of the management
segment values you used when you assigned the default accounts to the ledger,
such as the retained earnings account and the cumulative translation adjustment
account.
The privileges available for all the three Data Access sets are:
Read only: Users
allow viewing data in ledgers and balancing or management segment values.
Read and Write: Allows users to view and enter data in
ledgers and balancing or management segment values.
Steps required for defining data access set:
Step1:
Define
data access set
Navigation:
Go to GL Super User Responsibility => Setup
=> financials => data access set
1.
Enter data access set name.
2.
Select chart of accounts.
3.
Select calendar.
4.
Select access set type.
5.
Enter access details.
6.
Select ledger/ledger set.
7.
Select specific balancing or management segment
value.
8.
Select privileges read and write or read only as
per our requirement.
9.
Save your work.
Step2:
Assign
data access set to GL responsibility
Navigation:
Application:
System administrator
Profile
=> system
Tuesday, 12 February 2019
Query to get India timezone Date & Time.
--Query to get India timezone Date & Time
SELECT TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP) +
(5.5/24), 'DD/Mon/RRRR HH24:MI:SS') IST_DATE_TIME
FROM dual;
SELECT TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP) +
(5.5/24), 'DD/Mon/RRRR HH24:MI:SS') IST_DATE_TIME
FROM dual;
Script to Enable & Disable ERP Users through API.
--Script to Disable ERP User
BEGIN
FND_USER_PKG.DisableUser(username => 'XYZ_USER');
commit;
END;
----Script to Enable ERP User
BEGIN
FND_USER_PKG.EnableUser(username => 'XYZ_USER');
commit;
END;
BEGIN
FND_USER_PKG.DisableUser(username => 'XYZ_USER');
commit;
END;
----Script to Enable ERP User
BEGIN
FND_USER_PKG.EnableUser(username => 'XYZ_USER');
commit;
END;
Query to find request group and application information for a concurrent program
SELECT cpt.user_concurrent_program_name
,DECODE(rgu.request_unit_type,
'P', 'Program',
'S', 'Set',
rgu.request_unit_type) request_unit_type
,cp.concurrent_program_name Concurrent_Program_Short_Name
,rg.application_id
,rg.request_group_name
,fat.application_name
,fa.application_short_name
,fa.basepath application_basepath
FROM fnd_request_groups rg,
fnd_request_group_units rgu,
fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt,
fnd_application fa,
fnd_application_tl fat
WHERE rg.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND rg.application_id = fat.application_id
AND fa.application_id = fat.application_id
AND cpt.language = USERENV('LANG')
AND fat.language = USERENV('LANG')
AND (cpt.user_concurrent_program_name = :p_user_program_name OR :p_user_program_name IS NULL)
,DECODE(rgu.request_unit_type,
'P', 'Program',
'S', 'Set',
rgu.request_unit_type) request_unit_type
,cp.concurrent_program_name Concurrent_Program_Short_Name
,rg.application_id
,rg.request_group_name
,fat.application_name
,fa.application_short_name
,fa.basepath application_basepath
FROM fnd_request_groups rg,
fnd_request_group_units rgu,
fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt,
fnd_application fa,
fnd_application_tl fat
WHERE rg.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND rg.application_id = fat.application_id
AND fa.application_id = fat.application_id
AND cpt.language = USERENV('LANG')
AND fat.language = USERENV('LANG')
AND (cpt.user_concurrent_program_name = :p_user_program_name OR :p_user_program_name IS NULL)
Wednesday, 23 January 2019
Query to Find Scheduled Concurrent Programs
SELECT DISTINCT
fcr.request_id
,fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) Concurrent_program_name
,fu.user_name requestor
,fu.description requested_by
,fu.email_address
,frt.responsibility_name requested_by_resp
,(SELECT meaning
FROM fnd_lookups
WHERE lookup_type= 'CP_PHASE_CODE'
AND lookup_code =fcr.phase_code) program_Phase
,(SELECT meaning
FROM fnd_lookups
WHERE lookup_type= 'CP_STATUS_CODE'
AND lookup_code =fcr.status_code) program_Status
,fcr.argument_text program_parameters
, TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
,TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start
,TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
,DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
,CASE WHEN fcr.hold_flag = 'Y' THEN
Substr( fu.description, 0 , 40)
END last_update_by
,CASE WHEN fcr.hold_flag = 'Y' THEN
fcr.last_update_date
END last_update_date
,fcr.increment_dates
,CASE WHEN fcrc.CLASS_INFO IS NULL THEN
'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
ELSE 'N/A'
END RUN_ONCE
,CASE WHEN fcrc.class_type = 'P' THEN
'Repeat every ' ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
ELSE 'N/A'
END set_days_of_week
,fcr.resubmit_interval||' '||fcr.resubmit_interval_unit_code Resubmit_Interval
,NVL2(fcr.resubmit_interval,'PERIODICALLY',NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS','ONCE')) schedule_type
FROM fnd_concurrent_requests fcr
,fnd_user fu
,fnd_concurrent_programs fcp
,fnd_concurrent_programs_tl fcpt
,fnd_printer_styles_tl fpst
,fnd_conc_release_classes fcrc
,fnd_responsibility_tl frt
,fnd_lookups fl
WHERE 1=1
AND fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = 'CP_STATUS_CODE'
AND (fcpt.user_concurrent_program_name = :P_PROGRAM_NAME OR :P_PROGRAM_NAME IS NULL)
Order By Fu.Description;
fcr.request_id
,fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) Concurrent_program_name
,fu.user_name requestor
,fu.description requested_by
,fu.email_address
,frt.responsibility_name requested_by_resp
,(SELECT meaning
FROM fnd_lookups
WHERE lookup_type= 'CP_PHASE_CODE'
AND lookup_code =fcr.phase_code) program_Phase
,(SELECT meaning
FROM fnd_lookups
WHERE lookup_type= 'CP_STATUS_CODE'
AND lookup_code =fcr.status_code) program_Status
,fcr.argument_text program_parameters
, TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
,TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start
,TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
,DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
,CASE WHEN fcr.hold_flag = 'Y' THEN
Substr( fu.description, 0 , 40)
END last_update_by
,CASE WHEN fcr.hold_flag = 'Y' THEN
fcr.last_update_date
END last_update_date
,fcr.increment_dates
,CASE WHEN fcrc.CLASS_INFO IS NULL THEN
'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
ELSE 'N/A'
END RUN_ONCE
,CASE WHEN fcrc.class_type = 'P' THEN
'Repeat every ' ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
ELSE 'N/A'
END set_days_of_week
,fcr.resubmit_interval||' '||fcr.resubmit_interval_unit_code Resubmit_Interval
,NVL2(fcr.resubmit_interval,'PERIODICALLY',NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS','ONCE')) schedule_type
FROM fnd_concurrent_requests fcr
,fnd_user fu
,fnd_concurrent_programs fcp
,fnd_concurrent_programs_tl fcpt
,fnd_printer_styles_tl fpst
,fnd_conc_release_classes fcrc
,fnd_responsibility_tl frt
,fnd_lookups fl
WHERE 1=1
AND fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = 'CP_STATUS_CODE'
AND (fcpt.user_concurrent_program_name = :P_PROGRAM_NAME OR :P_PROGRAM_NAME IS NULL)
Order By Fu.Description;
Thursday, 17 January 2019
Query to list all objects of responsibility ie. submenu, menu and function
-- Query to list all objects of responsibility--
SELECT lvl r_lvl,
rownumber rw_num,
entry_sequence seq,
(lvl || '.' || rownumber || '.' || entry_sequence) menu_seq,
menu_name,
sub_menu_name,
prompt,
fm.description,
TYPE,
function_name,
user_function_name,
fff.description form_description
FROM ( SELECT LEVEL lvl,
ROW_NUMBER ()
OVER (PARTITION BY LEVEL, menu_id, entry_sequence
ORDER BY entry_sequence)
AS rownumber,
entry_sequence,
(SELECT user_menu_name
FROM fnd_menus_vl fmvl
WHERE 1 = 1 AND fmvl.menu_id = fmv.menu_id)
menu_name,
(SELECT user_menu_name
FROM fnd_menus_vl fmvl
WHERE 1 = 1 AND fmvl.menu_id = fmv.sub_menu_id)
sub_menu_name,
function_id,
prompt,
description
FROM apps.fnd_menu_entries_vl fmv
START WITH menu_id =
(SELECT menu_id
FROM apps.fnd_responsibility_vl
WHERE UPPER (responsibility_name) =
UPPER (:resp_name))
CONNECT BY PRIOR sub_menu_id = menu_id) fm,
apps.fnd_form_functions_vl fff
WHERE fff.function_id(+) = fm.function_id
ORDER BY lvl, entry_sequence;
/
NOTE:
1. Please pass the responsibility in parameter.
2. It'll show all submenu and function level wise.
3. R_lvl is a sequence number which starts with Parent Menu Level and drills down to child menu
4. ROW_NUM is sequence number for sub menus.
5. Seq is a sequence number as per the Oracle Apps.
SELECT lvl r_lvl,
rownumber rw_num,
entry_sequence seq,
(lvl || '.' || rownumber || '.' || entry_sequence) menu_seq,
menu_name,
sub_menu_name,
prompt,
fm.description,
TYPE,
function_name,
user_function_name,
fff.description form_description
FROM ( SELECT LEVEL lvl,
ROW_NUMBER ()
OVER (PARTITION BY LEVEL, menu_id, entry_sequence
ORDER BY entry_sequence)
AS rownumber,
entry_sequence,
(SELECT user_menu_name
FROM fnd_menus_vl fmvl
WHERE 1 = 1 AND fmvl.menu_id = fmv.menu_id)
menu_name,
(SELECT user_menu_name
FROM fnd_menus_vl fmvl
WHERE 1 = 1 AND fmvl.menu_id = fmv.sub_menu_id)
sub_menu_name,
function_id,
prompt,
description
FROM apps.fnd_menu_entries_vl fmv
START WITH menu_id =
(SELECT menu_id
FROM apps.fnd_responsibility_vl
WHERE UPPER (responsibility_name) =
UPPER (:resp_name))
CONNECT BY PRIOR sub_menu_id = menu_id) fm,
apps.fnd_form_functions_vl fff
WHERE fff.function_id(+) = fm.function_id
ORDER BY lvl, entry_sequence;
/
NOTE:
1. Please pass the responsibility in parameter.
2. It'll show all submenu and function level wise.
3. R_lvl is a sequence number which starts with Parent Menu Level and drills down to child menu
4. ROW_NUM is sequence number for sub menus.
5. Seq is a sequence number as per the Oracle Apps.
Tuesday, 27 November 2018
How to enable/disable "View Accounting" functionality for Tools menu in Transactions and Receipts forms.
The subfunction "XLA: View Accounting Lines" is responsible for enabling the View Accounting option in Tools Menu.
- Check the menu associated to the Responsibility
Responsibility: System Administrator
Navigation: Security > Responsibility > Define
- Query for the Receivable Responsibility attached to the user.
- Check field Menu, typically this will contain AR_NAVIGATE_GUI
- To ENABLE View Accounting, do the following:
Navigation : Application > Menu
- Query for the Menu value from step 2 above (i.e. AR_NAVIGATE_GUI)
- Scroll down and add following lines :
Sequence: Next Number
Prompt: Leave blank
Submenu: Leave blank
Function: 'XLA: View Accounting Lines' from the LOV
Description: Anything say "View Accounting Lines"
- Save the changes. This will kick off the Compile Security concurrent process. Please ensure that this completes successfully.
- Check that the Tools menu now has the option: View Accounting in both the Transactions and Receipts form.
The View Accounting option is ONLY visible for completed transactions. Please verify that the transaction for which you are attempting to View Accounting Lines is complete. -
To DISABLE View Accounting, do the following:
Do step1. above
Responsibility: System Administrator
Navigation: Application > Menu
- Remove the line containing Function: 'XLA: View Accounting Lines' from the list of function attached to the menu.
- Save the changes. This will kick off the Compile Security concurrent process. Please ensure that this completes successfully.
Monday, 26 November 2018
Oracle R12 How To Turn Off Low-Level Diagnostic Logging is turned on.
“Low-level Diagnostic Logging is turned on. This may temporarily reduce performance”
Disable diagnostic logging with the following steps:
1. Login as a user with System Administrator responsibility and then navigate to:
Profile > System.
Set the following profile option at the User level:
FND: Debug Log Enabled = No
2. Logout of the application. You may also want to clear the browser's cache.
3. Login again and confirm that the message no longer appears.
Disable diagnostic logging with the following steps:
1. Login as a user with System Administrator responsibility and then navigate to:
Profile > System.
Set the following profile option at the User level:
FND: Debug Log Enabled = No
2. Logout of the application. You may also want to clear the browser's cache.
3. Login again and confirm that the message no longer appears.
Wednesday, 24 October 2018
Query to get all profile options which are recently changed.
--Query to get all profile options which are recently changed.
SELECT DISTINCT p.profile_option_name SHORT_NAME
,n.user_profile_option_name "PROFILE NAME"
,DECODE(v.level_id, 10001, 'Site Level',
10002, 'Application Level',
10003, 'Responsibility Level',
10004, 'User Level',
10005, 'Server Level',
10007, 'SERVRESP',
'UnDef') LEVEL_SET
,DECODE(TO_CHAR(v.level_id), '10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT"
,v.profile_option_value VALUE
,v.LAST_UPDATE_DATE
FROM fnd_profile_options p
,fnd_profile_option_values v
,fnd_profile_options_tl n
,fnd_application app
,fnd_responsibility rsp
,fnd_nodes svr
,hr_operating_units org
,fnd_user usr
WHERE p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
and v.LAST_UPDATE_DATE is not null
--and upper(n.user_profile_option_name) like upper('BNE%')
and trunc(v.LAST_UPDATE_DATE) between trunc(sysdate-10) AND trunc(sysdate)
ORDER BY last_update_date desc
,short_name, level_set;
SELECT DISTINCT p.profile_option_name SHORT_NAME
,n.user_profile_option_name "PROFILE NAME"
,DECODE(v.level_id, 10001, 'Site Level',
10002, 'Application Level',
10003, 'Responsibility Level',
10004, 'User Level',
10005, 'Server Level',
10007, 'SERVRESP',
'UnDef') LEVEL_SET
,DECODE(TO_CHAR(v.level_id), '10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT"
,v.profile_option_value VALUE
,v.LAST_UPDATE_DATE
FROM fnd_profile_options p
,fnd_profile_option_values v
,fnd_profile_options_tl n
,fnd_application app
,fnd_responsibility rsp
,fnd_nodes svr
,hr_operating_units org
,fnd_user usr
WHERE p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
and v.LAST_UPDATE_DATE is not null
--and upper(n.user_profile_option_name) like upper('BNE%')
and trunc(v.LAST_UPDATE_DATE) between trunc(sysdate-10) AND trunc(sysdate)
ORDER BY last_update_date desc
,short_name, level_set;
Wednesday, 17 October 2018
FNDLOAD LDT Script for Download and Upload.
FNDLOAD LDT Script for Download and Upload.
1. CONCURRENT PROGRAM.
--Download Script
FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUSTAPP" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM";
--Upload Script
FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt CUSTOM_MODE=FORCE UPLOAD_MODE=REPLACE;
2. DATA DEFINITION AND ASSOCIATED TEMPLATE.
--Download Script
FNDLOAD apps/appspwd 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_CUSTOM_DD_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_CUSTOM_DT_CODE';
--Upload Script
FNDLOAD apps/appspwd 0 Y UPLOAD ${XDO_TOP}/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt;
3. DATA_TEMPLATE (Data Source .xml file)
--Download Script
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD appspwd -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST='XX_HOST_NAME')(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME;
--Upload Script
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appspwd -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST='XX_HOST_NAME')(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME XXCUST -LOB_CODE XX_TEMPLATE -NLS_LANG en -TERRITORY 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME DATA_TEMPLATE_XXCUST_XXGL_XX_TEMPLATE.xml -CUSTOM_MODE FORCE -LOG_FILE $LOG_FILE_NAME;
4. RTF TEMPLATE (Report Layout .rtf file)
--Download Script
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD appspwd -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST='XX_HOST_NAME')(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -TERRITORY 00 -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME;
--Upload Script
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appspwd -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST='XX_HOST_NAME')(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE TEMPLATE -APPS_SHORT_NAME XXCUST -LOB_CODE XX_TEMPLATE -LANGUAGE en -TERRITORY 00 -XDO_FILE_TYPE RTF -FILE_NAME TEMPLATE_SOURCE_XXCUST_XX_TEMPLATE_en.rtf -CUSTOM_MODE FORCE -LOG_FILE $LOG_FILE_NAME;
5. ALERTS.
--Download Script
FNDLOAD apps/appspwd 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - ALERT_NAME";
--Upload Script
FNDLOAD apps/appspwd 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE;
6. VALUE SET.
--Download Script
$FND_TOP/bin/FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX - Value_Set_Name";
--Upload Script
$FND_TOP/bin/FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE;
7. VALUE SET WITH VALUES.
--Download Script
$FND_TOP/bin/FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="XX - Value_Set_Name";
--Upload Script
$FND_TOP/bin/FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE;
8. WebADI - Web Desktop Integrator.
FNDLOAD apps/appspwd 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XX_INTEGRATOR.ldt BNE_INTEGRATORS INTEGRATOR_ASN="XXCUST" INTEGRATOR_CODE="XX_INTEGRATOR_CODE";
--Upload Script
FNDLOAD apps/appspwd 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XX_INTEGRATOR.ldt CUSTOM_MODE=FORCE UPLOAD_MODE=REPLACE;
9. FND MESSAGES.
--Download Script
FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="XX MESSAGE_NAME" ;
--Upload Script
FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE;
10. PROFILE.
--Download Script
FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRFL.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST";
--Upload Script
$FND_TOP/bin/FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRFL.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE;
11. LOOKUP.
--Download Script--
--Download Script
FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE";
--Upload Script
FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE;
12. REQUEST SET.
--Download Script
FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_REQ_SET.ldt REQ_SET REQUEST_SET_NAME='XX - REQUEST SET NAME';
--Upload Script
FNDLOAD apps/appspwd O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_REQ_SET.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE;
13. FORM FUNCTION.
--Download Script
FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt FUNCTION FUNCTION_NAME="XXFORM_FUNCTION_NAME";
--Upload Script
$FND_TOP/bin/FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE;
14. REQUEST GROUP
--Download Script
FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_REQST_GRP.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XX - Request Group Name" APPLICATION_SHORT_NAME="XXCUST";
--Upload Script
FNDLOAD apps/appspwd 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_REQST_GRP.ldt CUSTOM_MODE=FORCE UPLOAD_MODE=REPLACE;
Monday, 15 October 2018
Function not available to this responsibility. Change responsibility or contact your System Administrator.
After every fresh clone of any Test/Development instance from Production instance, application team used to report that:
'Function not available to this responsibility. Change responsibility or contact your System Administrator.'
error when they are trying to access custom form.
CAUSE: There is missing the entry of CUSTOM_TOP in default.env file.
Solution:
1. Login to APPLMGR user to application Linux server.
[applmgr@TEST]$ sudo su – applmgr
2. Go to $INST_TOP/ora/10.1.2/forms/
[applmgr@TEST]$ cd $INST_TOP/ora/10.1.2/forms/
3. Add the missing CUSTOM_TOP entry to default.env file.
XXCUSTOM_TOP=/U01/applmgr/r12/
4. Restart the middle tier services.
5. Retest the issue.
Wednesday, 5 September 2018
Query to get Responsibilities under specific user
SELECT frv.RESPONSIBILITY_NAME
,frv.responsibility_key
,fat.application_name
,fa.APPLICATION_SHORT_NAME
,fu.user_name
FROM fnd_user fu
,fnd_user_resp_groups furg
,fnd_responsibility_vl frv
,fnd_application_tl fat
,fnd_application fa
WHERE furg.responsibility_id = frv.responsibility_id
AND fu.user_id = furg.user_id
AND fa.APPLICATION_ID = fat.APPLICATION_ID
AND frv.APPLICATION_ID = fat.APPLICATION_ID
AND upper(fu.user_name) = UPPER ('USER_NAME')
AND fat.LANGUAGE = 'US'
ORDER BY 1;
,frv.responsibility_key
,fat.application_name
,fa.APPLICATION_SHORT_NAME
,fu.user_name
FROM fnd_user fu
,fnd_user_resp_groups furg
,fnd_responsibility_vl frv
,fnd_application_tl fat
,fnd_application fa
WHERE furg.responsibility_id = frv.responsibility_id
AND fu.user_id = furg.user_id
AND fa.APPLICATION_ID = fat.APPLICATION_ID
AND frv.APPLICATION_ID = fat.APPLICATION_ID
AND upper(fu.user_name) = UPPER ('USER_NAME')
AND fat.LANGUAGE = 'US'
ORDER BY 1;
Thursday, 23 August 2018
Query to get Request Set Information with Request Group
--Query to get Request Set Information with Request Group
SELECT rs.user_request_set_name Request_Set_Name
,rss.display_sequence Seq
,cp.user_concurrent_program_name Concurrent_Program_Name
,frg.request_group_name
,e.EXECUTABLE_NAME
,e.execution_file_name
,lv.meaning file_type
,fat.application_name Application_Name
,fa.application_short_name Application_Short_Name
,fat.application_name application_name
FROM fnd_request_sets_vl rs
,fnd_req_set_stages_form_v rss
,fnd_request_set_programs rsp
,fnd_concurrent_programs_vl cp
,fnd_executables e
,fnd_lookup_values lv
,fnd_application_tl fat
,fnd_request_group_units frgu
,fnd_request_groups frg
,fnd_application fa
WHERE 1=1
AND rs.application_id = rss.set_application_id
AND fa.application_id = fat.application_id
AND rs.request_set_id = rss.request_set_id
AND e.APPLICATION_ID =FAT.APPLICATION_ID
AND rss.set_application_id = rsp.set_application_id
AND rss.request_set_id = rsp.request_set_id
AND rss.request_set_stage_id = rsp.request_set_stage_id
AND rsp.program_application_id = cp.application_id
AND rsp.concurrent_program_id = cp.concurrent_program_id
AND cp.executable_id = e.executable_id
AND cp.executable_application_id = e.application_id
AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lv.lookup_code = e.execution_method_code
AND lv.language = 'US'
AND fat.language = 'US'
AND rs.end_date_active IS NULL
AND rs.request_set_id = frgu.request_unit_id
AND frgu.request_group_id = frg.request_group_id
AND rs.user_request_set_name = :P_REQUEST_SET_NAME
ORDER BY 1;
SELECT rs.user_request_set_name Request_Set_Name
,rss.display_sequence Seq
,cp.user_concurrent_program_name Concurrent_Program_Name
,frg.request_group_name
,e.EXECUTABLE_NAME
,e.execution_file_name
,lv.meaning file_type
,fat.application_name Application_Name
,fa.application_short_name Application_Short_Name
,fat.application_name application_name
FROM fnd_request_sets_vl rs
,fnd_req_set_stages_form_v rss
,fnd_request_set_programs rsp
,fnd_concurrent_programs_vl cp
,fnd_executables e
,fnd_lookup_values lv
,fnd_application_tl fat
,fnd_request_group_units frgu
,fnd_request_groups frg
,fnd_application fa
WHERE 1=1
AND rs.application_id = rss.set_application_id
AND fa.application_id = fat.application_id
AND rs.request_set_id = rss.request_set_id
AND e.APPLICATION_ID =FAT.APPLICATION_ID
AND rss.set_application_id = rsp.set_application_id
AND rss.request_set_id = rsp.request_set_id
AND rss.request_set_stage_id = rsp.request_set_stage_id
AND rsp.program_application_id = cp.application_id
AND rsp.concurrent_program_id = cp.concurrent_program_id
AND cp.executable_id = e.executable_id
AND cp.executable_application_id = e.application_id
AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lv.lookup_code = e.execution_method_code
AND lv.language = 'US'
AND fat.language = 'US'
AND rs.end_date_active IS NULL
AND rs.request_set_id = frgu.request_unit_id
AND frgu.request_group_id = frg.request_group_id
AND rs.user_request_set_name = :P_REQUEST_SET_NAME
ORDER BY 1;
Query to get list of Custom or Customised Workflows
--Query to get list of custom or customized workflows
SELECT distinct item_type,
trunc(begin_date)
FROM apps.wf_activities a
WHERE begin_date > date '2002-01-01'
AND NOT EXISTS
(select 'x'
from apps.ad_patch_runs pr
where a.begin_date between pr.start_date - 1 and pr.end_date + 1
)
AND item_type like 'XX%'
order by item_type, trunc(begin_date)
--Query to get list of customized Process and Activity in workflows
SELECT distinct process_item_type
,process_name
,activity_name
,instance_label
FROM wf_process_activities
WHERE process_name like 'XX' OR activity_name like '%XX%';
SELECT distinct item_type,
trunc(begin_date)
FROM apps.wf_activities a
WHERE begin_date > date '2002-01-01'
AND NOT EXISTS
(select 'x'
from apps.ad_patch_runs pr
where a.begin_date between pr.start_date - 1 and pr.end_date + 1
)
AND item_type like 'XX%'
order by item_type, trunc(begin_date)
--Query to get list of customized Process and Activity in workflows
SELECT distinct process_item_type
,process_name
,activity_name
,instance_label
FROM wf_process_activities
WHERE process_name like 'XX' OR activity_name like '%XX%';
Thursday, 7 June 2018
Query to get Concurrent Program and Related Resposnibility, Request Group in which its assigned.
--Query to get Standalone Request/Concurrent Program Info
SELECT distinct frv.responsibility_name,
frg.request_group_name,
frgu.request_unit_type,
frgu.request_unit_id,
fcpt.user_concurrent_program_name,
null request_set_name
FROM fnd_responsibility_vl frv
,fnd_request_groups frg
,FND_REQUEST_GROUP_UNITS frgu
,fnd_concurrent_programs fcp
,fnd_concurrent_programs_tl fcpt
WHERE 1=1
AND frv.request_group_id= frg.request_group_id
AND frgu.request_group_id= frg.request_group_id
AND fcpt.application_id= frgu.unit_application_id
AND fcp.concurrent_program_id= frgu.request_unit_id
AND fcpt.concurrent_program_id=fcp.concurrent_program_id
AND fcpt.user_concurrent_program_name like '%Concurrent Program Name%'
SELECT distinct frv.responsibility_name,
frg.request_group_name,
frgu.request_unit_type,
frgu.request_unit_id,
fcpt.user_concurrent_program_name,
null request_set_name
FROM fnd_responsibility_vl frv
,fnd_request_groups frg
,FND_REQUEST_GROUP_UNITS frgu
,fnd_concurrent_programs fcp
,fnd_concurrent_programs_tl fcpt
WHERE 1=1
AND frv.request_group_id= frg.request_group_id
AND frgu.request_group_id= frg.request_group_id
AND fcpt.application_id= frgu.unit_application_id
AND fcp.concurrent_program_id= frgu.request_unit_id
AND fcpt.concurrent_program_id=fcp.concurrent_program_id
AND fcpt.user_concurrent_program_name like '%Concurrent Program Name%'
Script/Query to Delete Concurrent Program and Executable from Backend
--**1. Script to Delete Data Definition**--
BEGIN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW('APPL NAME','DATA DEFINITION CODE');
--
COMMIT;
--
END;
--**Script to Delete Data Template**--
BEGIN
XDO_TEMPLATES_PKG.DELETE_ROW('APPL NAME','DATA TEMPLATE CODE');
--
COMMIT;
--
END;
--2. Script/Query to delete Concurrent Program and Executable from Backend
DECLARE
prog_short_name VARCHAR2(240);
appl_short_name VARCHAR2(240);
BEGIN
prog_short_name := 'XXXXXXXX'; --Concurrent Program Short name
appl_short_name := 'XXXX'; --Application Short name
--Checking for program and executable is exist or not
IF fnd_program.program_exists (prog_short_name, appl_short_name) OR
fnd_program.executable_exists (prog_short_name, appl_short_name)
THEN
--deleting the program
fnd_program.delete_program(prog_short_name, appl_short_name);
--deleting the executable
fnd_program.delete_executable(prog_short_name, appl_short_name);
COMMIT;
DBMS_OUTPUT.PUT_LINE (prog_short_name || ' deleted successfully');
ELSE
DBMS_OUTPUT.PUT_LINE (prog_short_name || ' not found');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
END;
BEGIN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW('APPL NAME','DATA DEFINITION CODE');
--
COMMIT;
--
END;
--**Script to Delete Data Template**--
BEGIN
XDO_TEMPLATES_PKG.DELETE_ROW('APPL NAME','DATA TEMPLATE CODE');
--
COMMIT;
--
END;
--2. Script/Query to delete Concurrent Program and Executable from Backend
DECLARE
prog_short_name VARCHAR2(240);
appl_short_name VARCHAR2(240);
BEGIN
prog_short_name := 'XXXXXXXX'; --Concurrent Program Short name
appl_short_name := 'XXXX'; --Application Short name
--Checking for program and executable is exist or not
IF fnd_program.program_exists (prog_short_name, appl_short_name) OR
fnd_program.executable_exists (prog_short_name, appl_short_name)
THEN
--deleting the program
fnd_program.delete_program(prog_short_name, appl_short_name);
--deleting the executable
fnd_program.delete_executable(prog_short_name, appl_short_name);
COMMIT;
DBMS_OUTPUT.PUT_LINE (prog_short_name || ' deleted successfully');
ELSE
DBMS_OUTPUT.PUT_LINE (prog_short_name || ' not found');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
END;
Tuesday, 8 May 2018
Query to list all the registered Concurrent Programs and Forms by Specific module.
--Query to list all the registered concurrent programs and Forms by Specific module.
SELECT DISTINCT a.application_name
,b.application_short_name
,DECODE(SUBSTR(cp.user_concurrent_program_name,4,1),':'
,'Concurrent Manager Executable'
,'Subprogram or Function') TYPE
,DECODE (d.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL* Plus',
'H', 'Host',
'L', 'SQL* Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
'K', 'XML - Java Concurrent Program',
d.execution_method_code
) exe_method
,d.concurrent_program_name PROGRAM_SHORT_NAME
,cp.user_concurrent_program_name USER_PROGRAM_NAME
,d.ENABLED_FLAG
FROM FND_CONCURRENT_PROGRAMS_TL cp
,FND_CONCURRENT_PROGRAMS d
,FND_APPLICATION_TL a
,fnd_application b
WHERE cp.application_id = a.application_id
AND d.CONCURRENT_PROGRAM_ID = cp.CONCURRENT_PROGRAM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('XXAP%')
UNION ALL
SELECT DISTINCT SUBSTR(a.application_name,1,60) APPLICATION_NAME
,b.application_short_name APPLICATION_SHORT_NAME
,'Form Executable' TYPE
,NULL EXE_METHOD
,SUBSTR(f.form_name,1,16) PROGRAM_SHORT_NAME
,SUBSTR(d.user_form_name,1,55) USER_PROGRAM_NAME
,NULL ENABLED_FLAG
FROM fnd_form f
,FND_APPLICATION_TL a
,fnd_application b
,FND_FORM_TL d
WHERE f.application_id = a.application_id
AND d.FORM_ID = f.FORM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('XXPA%')
ORDER BY 6
SELECT DISTINCT a.application_name
,b.application_short_name
,DECODE(SUBSTR(cp.user_concurrent_program_name,4,1),':'
,'Concurrent Manager Executable'
,'Subprogram or Function') TYPE
,DECODE (d.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL* Plus',
'H', 'Host',
'L', 'SQL* Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
'K', 'XML - Java Concurrent Program',
d.execution_method_code
) exe_method
,d.concurrent_program_name PROGRAM_SHORT_NAME
,cp.user_concurrent_program_name USER_PROGRAM_NAME
,d.ENABLED_FLAG
FROM FND_CONCURRENT_PROGRAMS_TL cp
,FND_CONCURRENT_PROGRAMS d
,FND_APPLICATION_TL a
,fnd_application b
WHERE cp.application_id = a.application_id
AND d.CONCURRENT_PROGRAM_ID = cp.CONCURRENT_PROGRAM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('XXAP%')
UNION ALL
SELECT DISTINCT SUBSTR(a.application_name,1,60) APPLICATION_NAME
,b.application_short_name APPLICATION_SHORT_NAME
,'Form Executable' TYPE
,NULL EXE_METHOD
,SUBSTR(f.form_name,1,16) PROGRAM_SHORT_NAME
,SUBSTR(d.user_form_name,1,55) USER_PROGRAM_NAME
,NULL ENABLED_FLAG
FROM fnd_form f
,FND_APPLICATION_TL a
,fnd_application b
,FND_FORM_TL d
WHERE f.application_id = a.application_id
AND d.FORM_ID = f.FORM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('XXPA%')
ORDER BY 6
Tuesday, 3 April 2018
Query to get User and Assigned Responsibilities
SELECT c.user_name
,a.responsibility_name responsibility
,b.start_date
,b.end_date
FROM fnd_user_resp_groups_indirect b
,fnd_responsibility_tl a
,fnd_user c,
(SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_service p1
WHERE p1.business_group_id = 81
UNION
SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_placement p2
WHERE p2.business_group_id = 81) d
WHERE a.responsibility_id = b.responsibility_id
AND b.user_id = c.user_id
AND a.language='US'
AND upper(a.responsibility_name) like 'AP%'
AND d.person_id = c.employee_id
AND NVL(d.actual_termination_date,sysdate+1) > sysdate
AND NVL(b.end_date,sysdate+1) >sysdate
UNION
SELECT c.user_name
,a.responsibility_name ROLE
,b.start_date
,b.end_date
FROM fnd_user_resp_groups_direct b
,fnd_responsibility_tl a
,fnd_user c,
(SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_service p1
WHERE p1.business_group_id = 81
UNION
SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_placement p2
WHERE p2.business_group_id = 81) d
WHERE a.responsibility_id = b.responsibility_id
AND b.user_id = c.user_id
AND a.language='US'
AND upper(a.responsibility_name) like 'AP%'
AND d.person_id = c.employee_id
AND NVL(d.actual_termination_date,sysdate+1) > sysdate
AND NVL(b.end_date,sysdate+1) >sysdate
ORDER BY start_date
,a.responsibility_name responsibility
,b.start_date
,b.end_date
FROM fnd_user_resp_groups_indirect b
,fnd_responsibility_tl a
,fnd_user c,
(SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_service p1
WHERE p1.business_group_id = 81
UNION
SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_placement p2
WHERE p2.business_group_id = 81) d
WHERE a.responsibility_id = b.responsibility_id
AND b.user_id = c.user_id
AND a.language='US'
AND upper(a.responsibility_name) like 'AP%'
AND d.person_id = c.employee_id
AND NVL(d.actual_termination_date,sysdate+1) > sysdate
AND NVL(b.end_date,sysdate+1) >sysdate
UNION
SELECT c.user_name
,a.responsibility_name ROLE
,b.start_date
,b.end_date
FROM fnd_user_resp_groups_direct b
,fnd_responsibility_tl a
,fnd_user c,
(SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_service p1
WHERE p1.business_group_id = 81
UNION
SELECT person_id
,date_start
,actual_termination_date
FROM per_periods_of_placement p2
WHERE p2.business_group_id = 81) d
WHERE a.responsibility_id = b.responsibility_id
AND b.user_id = c.user_id
AND a.language='US'
AND upper(a.responsibility_name) like 'AP%'
AND d.person_id = c.employee_id
AND NVL(d.actual_termination_date,sysdate+1) > sysdate
AND NVL(b.end_date,sysdate+1) >sysdate
ORDER BY start_date
Subscribe to:
Posts (Atom)