add

About Me

My photo
Oracle Apps - Techno Functional consultant

Wednesday, October 26

Oracle Usefull Queries

Adding A concurrent PROGRAM TO Request GROUP
/*=================================================
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;
TheS’ 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:

  1. PROFILE Options
  2. Forms
  3. FUNCTIONS
  4. Menus
  5. Responsibilities
  6. Request GROUPS
  7. Request SETS
  8. Lookups
  9. VALUE SETS
  10. Descriptive flex-FIELDS
  11. KEY flex-FIELDS
  12. Concurrent programs
  13. Form personalization
  14. FND USERS
  15. 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"

Note : apps/apps is Database User Name/PassWord

No comments: