Oracle Apps Queries
QUERIES
/*=================================================
FILE NAME:
VERSION:1.0
OBJECT NAME: None
OBJECT TYPE: Anonymous block
SCOPE: PRIVATE
DESCRIPTION: This script is used to add '' program to the request group
PARAMETERS: None
RETURNS: None
HISTORY:
=================================================*/
DECLARE
l_message VARCHAR2 (4000);
l_ret_code NUMBER := 0;
BEGIN
XX_suros_conc_util_pkg.add_program_to_group
(p_application => ,p_short_name => ,
p_request_group => ,
p_group_application => ,
x_ret_code => l_ret_code,
x_ret_message => l_message
);
DBMS_OUTPUT.put_line (': ' || l_ret_code);
DBMS_OUTPUT.put_line (': ' || l_message);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('In When Others Exception: ' || SQLCODE);
DBMS_OUTPUT.put_line (SQLERRM);
END;
-----------------------------
QUERY TO Get THE details
-----------------------------
SELECT DISTINCT fcpt.user_concurrent_program_name,
frg.request_group_name,
fcp.concurrent_program_name,
-- frt.responsibility_name,
fat.application_name,
fa.APPLICATION_SHORT_NAME,
fa.BASEPATH
FROM fnd_request_group_units frgu,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_request_groups frg,
fnd_executables fe,
fnd_responsibility fr,
fnd_responsibility_tl frt,
fnd_application_tl fat,
fnd_application fa
WHERE 1 = 1
AND fat.application_id = frgu.application_id
AND frgu.request_unit_id = fcp.concurrent_program_id
AND frgu.request_group_id = frg.request_group_id
AND fe.executable_id = fcp.executable_id
AND fcp.concurrent_program_id = fcpt.concurrent_program_id
AND frg.request_group_id = fr.request_group_id
AND fr.responsibility_id = frt.responsibility_id
AND fa.APPLICATION_ID = fat.APPLICATION_ID
AND fcpt.user_concurrent_program_name LIKE "&concurrent_program_name"
Org ID Setting in Oracle R12 From Backend
BEGIN
dbms_application_info.set_client_info(101);
END;
• In this example 101 is the ORG_ID for the Operating Unit or you could have used FND_GLOBAL.APPS_INITIALIZE to set your context.
In R12 you can set your SQL session context for a single OU with the following:
BEGIN
execute mo_global.set_policy_context('S',101);
END;
• The ‘S’ means Single Org Context
• 101 is the ORG_ID you want set
Also In R12 you can set your SQL session context for multiple OU’s with the following:
BEGIN
execute mo_global.set_org_access(NULL,111,‘ONT’);
END;
Concurrent PROGRAM Backend Registration Code
-- Program Name:
-- Creation Date:
-- Version:
-- Description: Register the Program ZENSI GL Journal Entry Interface (Loader and Validation)
-- Author: RAJU.CH
--
-- Version History:
-- Date Version Author Descrip
-- -------- ----------- ---------------- ------------------------
-- 19-12-01 1.1 Change the Spelling of Journal in the message display in the
-- executable creation part and in program creation part.
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
v_param_flag VARCHAR2 (1);
v_appl_name VARCHAR2 (240) := '&1';
v_base_path VARCHAR2 (240) := '&2';
BEGIN
BEGIN
fnd_program.set_session_mode ('seed_data');
END;
-----------------------------
--- Create Executable ---
-----------------------------
BEGIN
IF fnd_program.executable_exists (
executable_short_name => 'ZENGLJEINT',
application => v_appl_name)
THEN
--DBMS_OUTPUT.PUT_LINE('EXISTS');
NULL;
ELSE
fnd_program.executable (
executable => 'ZENGLJEINT',
short_name => 'ZENGLJEINT',
application => 'EAG Customization',
description => 'ZENSI GL Journal Entry Interface (Loader and Validation)',
execution_method => 'Host',
execution_file_name => 'ZENGLJEINT',
subroutine_name => NULL,
ICON_NAME => NULL,
LANGUAGE_CODE => 'US',
EXECUTION_FILE_PATH => NULL);
END IF;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
---------------------------------------------
--- Create Program with Parameters---
----------------------------------------------
BEGIN
IF fnd_program.program_exists (program => 'ZENGLJEINT',
application => 'Oracle General Ledger')
THEN
DBMS_OUTPUT.PUT_LINE ('EXISTS');
ELSE
fnd_program.register (
program => 'ZENSI GL Journal Entry Interface (Loader and Validation)',
application => 'Oracle General Ledger',
enabled => 'Y',
short_name => 'ZENGLJEINT',
description => 'ZENSI GL Journal Entry Interface (Loader and Validation)',
executable_short_name => 'ZENGLJEINT',
executable_application => 'Oracle General Ledger',
priority => NULL,
save_output => 'Y',
PRINT => 'N',
cols => 132,
rows => 45,
style => 'Landscape',
style_required => 'N',
printer => '',
use_in_SRS => 'Y',
allow_disabled_values => 'N',
run_alone => 'N',
enable_trace => 'N',
restart => 'Y',
nls_compliant => 'Y',
output_type => 'Text',
execution_options => NULL,
request_type => NULL,
request_type_application => NULL,
icon_name => NULL,
language_code => 'US',
mls_function_short_name => NULL,
mls_function_application => NULL,
incrementor => NULL);
BEGIN
SELECT DISTINCT 'Y'
INTO v_param_flag
FROM fnd_flex_value_sets
WHERE flex_value_set_name = 'FND_CHAR240';
fnd_program.parameter (
program_short_name => 'ZENGLJEINT',
application => 'Oracle General Ledger',
sequence => 1,
parameter => 'File Path Name',
description => 'File Path Name',
enabled => 'Y',
value_set => 'FND_CHAR240',
default_type => '',
DEFAULT_VALUE => '',
required => 'Y',
enable_security => 'N',
range => '',
display => 'Y',
display_size => 30,
description_size => 50,
concatenated_description_size => 25,
prompt => 'File Path Name',
token => '');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--DBMS_OUTPUT.PUT_LINE('Value Set: FND_CHAR240 Not Defined');
RAISE;
END;
BEGIN
SELECT DISTINCT 'Y'
INTO v_param_flag
FROM fnd_flex_value_sets
WHERE flex_value_set_name = 'FND_CHAR240';
fnd_program.parameter (
program_short_name => 'ZENGLJEINT',
application => 'Oracle General Ledger',
sequence => 2,
parameter => 'Base Path',
description => 'Base Path',
enabled => 'Y',
value_set => 'FND_CHAR240',
default_type => 'Constant',
DEFAULT_VALUE => v_base_path,
required => 'Y',
enable_security => 'N',
range => '',
display => 'Y',
display_size => 15,
description_size => 50,
concatenated_description_size => 25,
prompt => 'Base Path',
token => '');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--DBMS_OUTPUT.PUT_LINE('Value Set: FND_CHAR240 Not Defined');
RAISE;
END;
BEGIN
SELECT DISTINCT 'Y'
INTO v_param_flag
FROM fnd_flex_value_sets
WHERE flex_value_set_name = 'FND_NUMBER15_REQUIRED';
fnd_program.parameter (
program_short_name => 'ZENGLJEINT',
application => 'Oracle General Ledger',
sequence => 3,
parameter => 'Set Of Book Id',
description => 'Set Of Book Id',
enabled => 'Y',
value_set => 'FND_NUMBER15_REQUIRED',
default_type => 'Profile',
DEFAULT_VALUE => 'GL_SET_OF_BKS_ID',
required => 'Y',
enable_security => 'N',
range => '',
display => 'N',
display_size => 15,
description_size => 50,
concatenated_description_size => 25,
prompt => 'Set Of Book Id',
token => '');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--DBMS_OUTPUT.PUT_LINE('Value Set: FND_NUMBER15_REQUIRED Not Defined');
RAISE;
END;
BEGIN
SELECT DISTINCT 'Y'
INTO v_param_flag
FROM fnd_flex_value_sets
WHERE flex_value_set_name = 'GL_SRS_JOURNAL_SOURCE';
fnd_program.parameter (
program_short_name => 'ZENGLJEINT',
application => 'Oracle General Ledger',
sequence => 4,
parameter => 'JE Batch Source Name',
description => 'JE Batch Source Name',
enabled => 'Y',
value_set => 'GL_SRS_JOURNAL_SOURCE',
default_type => '',
DEFAULT_VALUE => '',
required => 'Y',
enable_security => 'N',
range => '',
display => 'Y',
display_size => 25,
description_size => 50,
concatenated_description_size => 25,
prompt => 'JE Batch Source Name',
token => '');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--DBMS_OUTPUT.PUT_LINE('Value Set: GL_SRS_JOURNAL_SOURCE Not Defined');
RAISE;
END;
BEGIN
SELECT DISTINCT 'Y'
INTO v_param_flag
FROM fnd_flex_value_sets
WHERE flex_value_set_name = 'FND_NUMBER15';
fnd_program.parameter (
program_short_name => 'ZENGLJEINT',
application => 'Oracle General Ledger',
sequence => 5,
parameter => 'Group Id',
description => 'Group Id',
enabled => 'Y',
value_set => 'FND_NUMBER15',
default_type => 'Constant',
DEFAULT_VALUE => '9999',
required => 'Y',
enable_security => 'N',
range => '',
display => 'Y',
display_size => 15,
description_size => 50,
concatenated_description_size => 25,
prompt => 'Group Id',
token => '');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--DBMS_OUTPUT.PUT_LINE('Value Set: FND_NUMBER15 Not Defined');
RAISE;
END;
--DBMS_OUTPUT.PUT_LINE('Program ZENSI GL Journal Entry Interface (Loader and Validation) Successfully Created');
DBMS_OUTPUT.PUT_LINE ('SUCCESS');
END IF;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
--DBMS_OUTPUT.PUT_LINE('SQL Error: Program Terminated: Rollback Complete');
END;
/
Finding Oracle Application Product VERSION QUERY
SELECT SUBSTR (a.application_name, 1, 60) application_name,
SUBSTR (i.product_version, 1, 4) VERSION,
i.patch_level,
i.application_id,
i.last_update_date
FROM apps.fnd_product_installations i, apps.fnd_application_all_view a
WHERE i.application_id = a.application_id
ORDER BY a.application_name
Finding Oracle VERSION QUERY
SELECT product, VERSION, status
FROM product_component_version;
DOCUMENT on creation of LDT files
This document explains how TO CREATE LDT FILE FOR FOLLOWING things:
- PROFILE Options
- Forms
- FUNCTIONS
- Menus
- Responsibilities
- Request GROUPS
- Request SETS
- Lookups
- VALUE SETS
- Descriptive flex-FIELDS
- KEY flex-FIELDS
- Concurrent programs
- Form personalization
- FND USERS
- Alerts
1.Profile Options:
SOURCE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt
PROFILE PROFILE_NAME="XXPRNAME" APPLICATION_SHORT_NAME="PN"
Target:
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt
FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt PROFILE PROFILE_NAME=" XXPRNAME" APPLICATION_SHORT_NAME="PN"
2.Forms:
SOURCE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFRMNAME.ldt FORM APPLICATION_SHORT_NAME="PN" FORM_NAME="XXFRMNAME"
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct XXFRMNAME.ldt
3.Functions:
SOURCE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFUNNAME.ldt FUNCTION FUNC_APP_SHORT_NAME="PN" FUNCTION_NAME="XXFUNNAME"
Target:
FNDLOAD apps/apps O Y UPLOAD @FND:patch/115/import/afsload.lct XXFUNNAME.ldt
4.Menus:
SOURCE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXMNNAME.ldt MENU MENU_NAME="XXMNNAME"
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct XXMNNAME.ldt
5.Responsibilities:
SOURCE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY="XXRESNAME"
Target:
1. FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY="XXRESNAME" APPLICATION_SHORT_NAME="PN"
6.Request Groups:
SOURCE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XXRQGNAME" APPLICATION_SHORT_NAME="PN"
Target:
1.FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XXRQGNAME" APPLICATION_SHORT_NAME="PN"
7.Request Sets:
SOURCE:
1.FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt REQ_SET REQUEST_SET_NAME="XXRQSNAME"
2.FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt REQ_SET_LINKS REQUEST_SET_NAME="XXRQSNAME"
Target:
1.FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt
8.Lookups:
SOURCE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="PN" LOOKUP_TYPE="XXLKPNAME"
Target:
1.FNDLOAD apps/apps 0 Y UPLOAD aflvmlu.lct XXLKPNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt FND_LOOKUP_TYPE LOOKUP_TYPE="XXLKPNAME" APPLICATION_SHORT_NAME="PN"
9.Value sets:
SOURCE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME="XXVALSNAME"
Target:
1.FNDLOAD apps/apps 0 Y UPLOAD afffload.lct XXVALSNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME="XXVALSNAME" APPLICATION_SHORT_NAME="PN"
10.Descriptive Flex-fields:
SOURCE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXDFFNAME.ldt DESC_FLEX P_LEVEL='COL_ALL:REF_ALL:CTX_ONE:SEG_ALL' APPLICATION_SHORT_NAME="PN"
DESCRIPTIVE_FLEXFIELD_NAME="PN_LEASE_DETAILS" P_CONTEXT_CODE="Global Data Elements"
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct XXDFFNAME.ldt
11.Key Flex-fields:
SOURCE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXKFFNAME.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’
APPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct XXKFFNAME.ldt
12.Concurrent Programs:
SOURCE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt PROGRAM APPLICATION_SHORT_NAME="PN" CONCURRENT_PROGRAM_NAME="XXCPNAME"
Target:
1.FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afcpprog.lct XXCPNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt PROGRAM CONCURRENT_PROGRAM_NAME="XXCPNAME" APPLICATION_SHORT_NAME="PN"
13.Form Personalization:
SOURCE:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt FND_FORM_CUSTOM_RULES function_name="XXFPNAME"
Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt
14.FND Users:
SOURCE:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ./XXUSERNAME.ldt FND_USER USER_NAME='XXUSERNAME'
Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct ./ XXUSERNAME.ldt
15.Alerts:
SOURCE:
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XXALERTNAME.ldt ALR_ALERTS APPLICATION_SHORT_NAME=PER Alert_name="XXALERTNAME"
Target:
FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XXALERTNAME.ldt ALR_ALERTS APPLICATION_SHORT_NAME=PER Alert_name="XXALERTNAME"
No comments:
Post a Comment