1. Creation of USER:
NAVIGATION: SYSTEM ADMINISTRATOR à SECURITY à USER à DEFINE
CREATE A NEW USER:
Enter user name, password and Re-enter the password.
Query to check user is created or not:
To Add Responsibilities: -
Click on Direct Responsibilities tab
Select responsibility from LOV.
Then click on save.
Create an user through API
I.
CREATE AN
USER
/*=======================================================
-- API TO CREATE AN USER
-- FND_USER_PKG.CREATEUSER
==========================================================*/
P_USER_NAME VARCHAR2(200) := 'SYSCNTEST' ;
P_OWNER VARCHAR2(200) := NULL;
P_UNENCRYPTED_PASSWORD VARCHAR2(200) := 'ORACLE123';
P_SESSION_NUMBER NUMBER :=USERENV('SESSIONID');
P_START_DATE DATE := SYSDATE;
P_END_DATE DATE := NULL;
P_LAST_LOGON_DATE DATE := NULL;
P_DESCRIPTION VARCHAR2(200) := 'TEAMSEARCH';
P_PASSWORD_DATE DATE := SYSDATE - 1;
P_PASSWORD_ACCESSES_LEFT NUMBER := 1000;
P_PASSWORD_LIFESPAN_ACCESSES NUMBER := 1000;
P_PASSWORD_LIFESPAN_DAYS NUMBER := 1000;
P_EMPLOYEE_ID NUMBER := NULL;
P_EMAIL_ADDRESS VARCHAR2(200) := NULL;
P_FAX VARCHAR2(200) := NULL;
P_CUSTOMER_ID NUMBER := NULL;
P_SUPPLIER_ID NUMBER := NULL;
V_USER_ID NUMBER;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (USER_ID => 1318,
RESP_ID => 21623,
RESP_APPL_ID => 660);
FND_USER_PKG.CREATEUSER(
X_USER_NAME => P_USER_NAME,
X_OWNER => P_OWNER,
X_UNENCRYPTED_PASSWORD => P_UNENCRYPTED_PASSWORD,
X_SESSION_NUMBER => P_SESSION_NUMBER,
X_START_DATE => P_START_DATE,
X_END_DATE => P_END_DATE,
X_LAST_LOGON_DATE => P_LAST_LOGON_DATE,
X_DESCRIPTION => P_DESCRIPTION,
X_PASSWORD_DATE => P_PASSWORD_DATE,
X_PASSWORD_ACCESSES_LEFT => P_PASSWORD_ACCESSES_LEFT,
X_PASSWORD_LIFESPAN_ACCESSES => P_PASSWORD_LIFESPAN_ACCESSES,
X_PASSWORD_LIFESPAN_DAYS => P_PASSWORD_LIFESPAN_DAYS,
X_EMPLOYEE_ID => P_EMPLOYEE_ID,
X_EMAIL_ADDRESS => P_EMAIL_ADDRESS,
X_FAX => P_FAX,
X_CUSTOMER_ID => P_CUSTOMER_ID,
X_SUPPLIER_ID => P_SUPPLIER_ID
DBMS_OUTPUT.PUT_LINE ('USER_ID : ' ||V_USER_ID);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR WHILE CREATING A USER: '||SQLERRM);
END;
QUERY TO GET USER
-----------------------------------
User syscntest created in oracle apps.
Log in with the user account that has just created by API.
We have successfully login with new user.
II. DISABLE USER
-- API TO DISABLE USER
-- FND_USER_PKG.DISABLEUSER
==========================================================*/
-------SCRIPT-------
FND_USER_PKG.DISABLEUSER(USERNAME => 'SYSCNTEST') ;
END;
III. CHANGE/UPDATE THE USER PASSWORD
-- API TO CHANGE/UPDATE THE USER PASSWORD
-- FND_USER_PKG.CHANGEPASSWORD
==========================================================*/
-------SCRIPT-------
LB_RESULT BOOLEAN;
BEGIN
LB_RESULT := FND_USER_PKG.CHANGEPASSWORD(USERNAME => 'SYSCNTEST ',
NEWPASSWORD => 'DBA#1234'
);
IF LB_RESULT
DBMS_OUTPUT.PUT_LINE('SUCESSFULLY UPDATED');
END IF;
END;
IV. API TO CHANGE/UPDATE THE USER
/*=======================================================
--
API TO CHANGE/UPDATE THE USER
--
FND_USER_PKG.UPDATEUSER
==========================================================*/
V_USER_NAME VARCHAR2(100) := 'SYSCNTEST';
V_EMAIL_ADDRESS VARCHAR2(100) := 'TESTUSER@XYZ.COM';
FND_USER_PKG.UPDATEUSER(X_USER_NAME => V_USER_NAME,
X_OWNER => NULL,
X_UNENCRYPTED_PASSWORD => NULL,
X_SESSION_NUMBER => 0,
X_START_DATE => NULL,
X_END_DATE => NULL,
X_LAST_LOGON_DATE => NULL,
X_DESCRIPTION => NULL,
X_PASSWORD_DATE => NULL,
X_PASSWORD_ACCESSES_LEFT => NULL,
X_PASSWORD_LIFESPAN_ACCESSES => NULL,
X_PASSWORD_LIFESPAN_DAYS => NULL,
X_EMPLOYEE_ID => NULL,
X_EMAIL_ADDRESS => V_EMAIL_ADDRESS,
X_FAX => NULL,
X_CUSTOMER_ID => NULL,
X_SUPPLIER_ID => NULL,
X_USER_GUID => NULL,
X_CHANGE_SOURCE => NULL);
COMMIT;
DBMS_OUTPUT.PUT_LINE('USER '|| V_USER_NAME|| ' IS UPDATED SUCCESSFULLY');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR ENCOUNTERED WHILE UPDATING THE USER AND THE ERROR IS ' || SQLERRM);
END;
2. Responsibility
I.
API TO ADD A
RESPONSIBILITY TO A USER
/*=======================================================
--
API TO ADD A RESPONSIBILITY TO A USER
--
FND_USER_PKG.ADDRESP
==========================================================*/
---------SCRIPT----------
DECLARE
LV_USER_NAME VARCHAR2 (20) := 'SYSCNTEST';
LV_REQ_RESP_KEY VARCHAR2 (50) := 'APPLICATION_DEVELOPER';
LV_DESCRIPTION VARCHAR2 (100):= 'ADDING RESPONSIBILITY TO USER USING API';
LV_REQ_RESP_NAME VARCHAR2 (200);
LV_APPL_SHRT_NAME VARCHAR2 (20);
LV_APPL_NAME VARCHAR2 (50);
LV_RESP_KEY VARCHAR2 (50);
BEGIN
SELECT FAV.APPLICATION_SHORT_NAME,
FAV.APPLICATION_NAME,
FRV.RESPONSIBILITY_NAME
LV_APPL_NAME,
LV_REQ_RESP_NAME
FROM FND_APPLICATION_VL FAV,
FND_RESPONSIBILITY_VL FRV
WHERE FRV.APPLICATION_ID = FAV.APPLICATION_ID
FND_USER_PKG.ADDRESP (USERNAME => LV_USER_NAME,
RESP_APP => LV_APPL_SHRT_NAME,
RESP_KEY => LV_REQ_RESP_KEY,
SECURITY_GROUP => 'STANDARD',
DESCRIPTION => LV_DESCRIPTION,
START_DATE => SYSDATE,
END_DATE => NULL
);
COMMIT;
DBMS_OUTPUT.PUT_LINE ('THE RESPONSIBILITY ' || LV_REQ_RESP_NAME || ' IS ADDED TO THE USER ' || LV_USER_NAME);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('RESPONSIBILITY IS NOT ADDED DUE TO ' || SQLCODE || '; ' || SUBSTR (SQLERRM, 1, 250));
ROLLBACK;
END;
Assigned
application developer responsibility to the user syscntest.
II.
API TO
REMOVE A RESPONSIBILITY FROM USER
/*=======================================================
--
API TO REMOVE A RESPONSIBILITY FROM USER
--
FND_USER_PKG.DELRESP
==========================================================*/
V_USER_NAME VARCHAR2 (100) := 'SYSCNTEST';
V_RESPONSIBILITY_NAME VARCHAR2 (100) := 'APPLICATION DEVELOPER';
V_APPLICATION_NAME VARCHAR2 (100) := NULL;
V_RESPONSIBILITY_KEY VARCHAR2 (100) := NULL;
V_SECURITY_GROUP VARCHAR2 (100) := NULL;
BEGIN
SELECT FA.APPLICATION_SHORT_NAME,
FR.RESPONSIBILITY_KEY,
FRG.SECURITY_GROUP_KEY
V_RESPONSIBILITY_KEY,
V_SECURITY_GROUP
FROM FND_RESPONSIBILITY FR,
FND_APPLICATION FA,
FND_SECURITY_GROUPS FRG,
FND_RESPONSIBILITY_TL FRT
WHERE FR.APPLICATION_ID = FA.APPLICATION_ID
AND FRT.RESPONSIBILITY_NAME = V_RESPONSIBILITY_NAME;
FND_USER_PKG.DELRESP (USERNAME => V_USER_NAME,
RESP_APP => V_APPLICATION_NAME,
RESP_KEY => V_RESPONSIBILITY_KEY,
SECURITY_GROUP => V_SECURITY_GROUP);
DBMS_OUTPUT.PUT_LINE( 'RESPONSIBLITY '
|| V_RESPONSIBILITY_NAME
|| ' IS REMOVED FROM THE USER '
|| V_USER_NAME
|| ' SUCCESSFULLY');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('ERROR ENCOUNTERED WHILE DELETING RESPONSIBILTY FROM THE USER AND THE ERROR IS ' || SQLERRM);
COMMIT;
END;
Removed
application developer responsibility to the user syscntest.
Query to get active responsibilities assigned to a particular user:
FU.USER_NAME,
FU.DESCRIPTION,
FRT.RESPONSIBILITY_ID RESPONSIBILITY_ID,
FRT.RESPONSIBILITY_NAME RESPONSIBILITY,
FAT.APPLICATION_ID APPLICATION_ID,
FAT.APPLICATION_NAME APPLICATION_NAME
FND_USER_RESP_GROUPS_DIRECT FURG,
FND_RESPONSIBILITY_TL FRT,
FND_APPLICATION_TL FAT
WHERE 1=1
AND FU.USER_ID = FURG.USER_ID
AND FRT.LANGUAGE = 'US'
AND NVL(TRUNC(FURG.END_DATE),SYSDATE+1) >= SYSDATE
AND FU.USER_NAME LIKE '<USER_NAME>' --'SYSCNTEST'
ORDER BY FU.USER_NAME;
-->TO CHECK ACTIVE RESPONSIBILITIES OF THE
USER:
b.user_id,
b.start_date,
b.end_date,
ftl.application_name,
c.responsibility_name,
-->TO CHECK THE ACTIVE RESPONSBILIOTS OF THE USER
a.start_date responsibility_start_date,
a.end_date responsibility_end_date,
a.description
fnd_user b,
fnd_responsibility_vl c,
fnd_application_tl ftl
WHERE a.user_id = b.user_id
AND SYSDATE BETWEEN a.start_date AND NVL(a.end_date,SYSDATE + 1)
AND SYSDATE BETWEEN b.start_date AND NVL(b.end_date,SYSDATE + 1)
and sysdate between c.start_date and nvl(c.end_date,sysdate + 1)
and UPPER(b.user_name) = UPPER('<USER_NAME>');
3. Concurrent Program
Navigation: Application Developer -> Concurrent -> Executable
I. Create an Executable
Types of execution methods:
/*=======================================================
--
API to create executable
--
apps.fnd_program.executable
==========================================================*/
L_EXECUTABLE VARCHAR2 (200) := 'XX_EMP_INSERT_EXE';
L_APPLICATION VARCHAR2 (200) := 'FND';
L_SHORT_NAME VARCHAR2 (200) := 'XX_EMP_INSERT_EXE';
L_DESCRIPTION VARCHAR2 (200) := 'TEST SCRIPT FOR CREATING EXECUTABLE FROM BACKEND';
L_EXECUTION_METHOD VARCHAR2 (200) := 'PL/SQL STORED PROCEDURE';
L_EXECUTION_FILE_NAME VARCHAR2 (200) :='XXX_EMP_INSERT_UPDATE.XXX_EMP_INSERT';
L_SUBROUTINE_NAME VARCHAR2 (200) := NULL;
L_ICON_NAME VARCHAR2 (200) := NULL;
L_LANGUAGE_CODE VARCHAR2 (200) := 'US';
L_EXECUTION_FILE_PATH VARCHAR2 (200) := NULL;
L_CHECK VARCHAR2 (2);
BEGIN
APPS.FND_PROGRAM.EXECUTABLE
APPLICATION => L_APPLICATION,
SHORT_NAME => L_SHORT_NAME,
DESCRIPTION => L_DESCRIPTION,
EXECUTION_METHOD => L_EXECUTION_METHOD,
EXECUTION_FILE_NAME => L_EXECUTION_FILE_NAME,
SUBROUTINE_NAME => L_SUBROUTINE_NAME,
ICON_NAME => L_ICON_NAME,
LANGUAGE_CODE => L_LANGUAGE_CODE,
EXECUTION_FILE_PATH => L_EXECUTION_FILE_PATH
COMMIT;
BEGIN
--TO CHECK WHETHER EXECUTABLE IS CREATED OR NOT
SELECT 'Y'
INTO L_CHECK
DBMS_OUTPUT.PUT_LINE ('EXECUTABLE CREATED SUCCESSFULLY');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE ('EXECUTABLE REGISTRATION FAILED');
END;
END;
Effected Table:
II. Create a Concurrent Program for above Executable.
Navigation: Application Developer à Concurrent à Program
I.
API TO
CREATE CONCURRENT PROGRAM
-- API TO CREATE CONCURRENT PROGRAM
-- FND_PROGRAM.REGISTER
==========================================================*/
----
SCRIPT
V_NAME VARCHAR2 (50) := 'XXX EMPLOYEE INSERT PROGRAM';
V_SHORT_NAME VARCHAR2 (50) := 'XX_EMP_INSERT_EXE';
BEGIN
FND_GLOBAL.APPS_INITIALIZE (USER_ID => 1318,
RESP_ID => 21623,
RESP_APPL_ID => 660
FND_PROGRAM.REGISTER (PROGRAM => V_NAME,
APPLICATION => 'FND',
ENABLED => 'Y',
SHORT_NAME => V_SHORT_NAME,
EXECUTABLE_SHORT_NAME => V_SHORT_NAME,
EXECUTABLE_APPLICATION => 'FND',
STYLE => 'A4',
OUTPUT_TYPE => 'TEXT',
USE_IN_SRS => 'Y'
);
COMMIT;
DBMS_OUTPUT.PUT_LINE ('SUCCEEFULLY CREATED CONCURRENT PROGRAM '|| V_SHORT_NAME);
END;
Effected Table:
Click on parameters to create a parameter in concurrent program.
I.
API TO
CREATE PARAMETERS IN THE CONCURRENT PROGRAM
-- API TO CREATE PARAMETERS IN THE CONCURRENT PROGRAM
-- FND_PROGRAM.PARAMETERS
==========================================================*/
DECLARE
L_PROGRAM_SHORT_NAME VARCHAR2 (200) := 'XX_EMP_INSERT_EXE';
L_APPLICATION VARCHAR2 (200) := 'FND';
L_SEQUENCE NUMBER := 10;
L_PARAMETER VARCHAR2 (200) := 'STUDENT ID';
L_DESCRIPTION VARCHAR2 (200) := 'STUDENT ID';
L_ENABLED VARCHAR2 (200) := 'Y';
L_VALUE_SET VARCHAR2 (200) := 'NUMBER';
L_DEFAULT_TYPE VARCHAR2 (200) := NULL;
L_DEFAULT_VALUE VARCHAR2 (200) := NULL;
L_REQUIRED VARCHAR2 (200) :='N';
L_ENABLE_SECURITY VARCHAR2 (200) :='N';
L_RANGE VARCHAR2 (200) := NULL;
L_DISPLAY VARCHAR2 (200) := 'Y';
L_DISPLAY_SIZE NUMBER := 50;
L_DESCRIPTION_SIZE NUMBER := 50;
L_CONCATENATED_DESCR_SIZE NUMBER := 50;
L_PROMPT VARCHAR2 (200) := 'STUDENT ID';
L_TOKEN VARCHAR2 (200) := NULL;
L_CD_PARAMETER VARCHAR2 (200) := NULL;
L_CHECK VARCHAR2 (2);
BEGIN
--CALLING API TO CREATE A PARAMETER FOR A CONCURRENT PROGRAM DEFINITION
APPS.FND_PROGRAM.PARAMETER
APPLICATION => L_APPLICATION,
SEQUENCE => L_SEQUENCE,
PARAMETER => L_PARAMETER,
DESCRIPTION => L_DESCRIPTION,
ENABLED => L_ENABLED,
VALUE_SET => L_VALUE_SET,
DEFAULT_TYPE => L_DEFAULT_TYPE,
DEFAULT_VALUE => L_DEFAULT_VALUE,
REQUIRED => L_REQUIRED,
ENABLE_SECURITY => L_ENABLE_SECURITY,
RANGE => L_RANGE,
DISPLAY => L_DISPLAY,
DISPLAY_SIZE => L_DISPLAY_SIZE,
DESCRIPTION_SIZE => L_DESCRIPTION_SIZE,
CONCATENATED_DESCRIPTION_SIZE => L_CONCATENATED_DESCR_SIZE,
PROMPT => L_PROMPT,
TOKEN => L_TOKEN,
CD_PARAMETER => L_CD_PARAMETER
--TO CHECK WHETHER A PARAMETER IS ASSIGNED TO A CONCURRENT PROGRAM OR NOT
SELECT 'Y'
INTO L_CHECK
|| L_PROGRAM_SHORT_NAME
AND END_USER_COLUMN_NAME = L_PARAMETER;
--
DBMS_OUTPUT.PUT_LINE ('CONCURRENT PROGRAM PARAMETER REGISTERED SUCCESSFULLY');
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('CONCURRENT PROGRAM PARAMETER REGISTRATION FAILED');
END;
END;
/*=======================================================
--
API TO DELETE PARAMETERS TO THE CONCURRENT PROGRAM
--
FND_PROGRAM.DELETE_PARAMETER
==========================================================*/
V_PROGRAM_SHORT_NAME VARCHAR2 (200) := 'XX_EMP_INSERT_EXE';--CONCURRENT PROGRAM SHORT NAME;
V_APPLICATION VARCHAR2 (200) := 'FND';-- APPLICATION SHORTNAME;
V_PARAMETER VARCHAR2 (200) := 'STUDENT ID'; -- PARAMETER NAME;
BEGIN
APPS.FND_PROGRAM.DELETE_PARAMETER
APPLICATION => V_APPLICATION,
PARAMETER => V_PARAMETER
COMMIT;
END;
/*=======================================================
-- API DELETE CONCURRENT PROGRAM AND
EXECUTABLE
--
FND_PROGRAM.DELETE_PROGRAM
==========================================================*/
FND_PROGRAM.DELETE_PROGRAM('CONCURRENT_PROGRAM_NAME','APPLICATION_SHORT_NAME');
FND_PROGRAM.DELETE_EXECUTABLE('CONCURRENT_PROGRAM_NAME','APPLICATION_SHORT_NAME');
COMMIT;
END;
III. Add Concurrent Program to Request group.
Navigation: System Administration -> Security -> Responsibility -> Define
Navigation: System Administration -> Security -> Responsibility -> Request
Query the request group and add concurrent program to the request group
Effected Table:
I.
API TO ADD
CONCURRENT PROGRAM TO THE REQUEST GROUP
/*=======================================================
-- API ADD CONCURRENT PROGRAM TO THE
REQUEST GROUP
--
FND_PROGRAM.ADD_TO_GROUP
==========================================================*/
DECLARE
L_PROGRAM_SHORT_NAME VARCHAR2 (200) := 'XX_EMP_INSERT_EXE';
L_PROGRAM_APPLICATION VARCHAR2 (200) := 'FND';
L_REQUEST_GROUP VARCHAR2 (200) := 'SYSTEM ADMINISTRATOR REPORTS';
L_GROUP_APPLICATION VARCHAR2 (200) := 'APPLICATION OBJECT LIBRARY';
--
BEGIN
--CALLING API TO ASSIGN CONCURRENT PROGRAM TO A REQEST GROUP
APPS.FND_PROGRAM.ADD_TO_GROUP
PROGRAM_APPLICATION => L_PROGRAM_APPLICATION,
REQUEST_GROUP => L_REQUEST_GROUP,
GROUP_APPLICATION => L_GROUP_APPLICATION
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('ADDING CONCURRENT PROGRAM TO REQUEST GROUP FAILED');
END;
API REMOVE CONCURRENT PROGRAM TO THE REQUEST GROUP
/*=======================================================
-- API REMOVE CONCURRENT PROGRAM TO THE
REQUEST GROUP
-- FND_PROGRAM. REMOVE_FROM_GROUP
==========================================================*/
DECLARE
L_PROGRAM_SHORT_NAME VARCHAR2 (200) := 'XX_EMP_INSERT_EXE';
L_PROGRAM_APPLICATION VARCHAR2 (200) := 'FND';
L_REQUEST_GROUP VARCHAR2 (200) := 'SYSTEM ADMINISTRATOR REPORTS';
L_GROUP_APPLICATION VARCHAR2 (200) := 'APPLICATION OBJECT LIBRARY';
--
BEGIN
--CALLING API TO ASSIGN CONCURRENT PROGRAM TO A REQEST GROUP
APPS.FND_PROGRAM.REMOVE_FROM_GROUP
PROGRAM_APPLICATION => L_PROGRAM_APPLICATION,
REQUEST_GROUP => L_REQUEST_GROUP,
GROUP_APPLICATION => L_GROUP_APPLICATION
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('ADDING CONCURRENT PROGRAM TO REQUEST GROUP FAILED');
END;
API TO
SUBMIT CONCURRENT PROGRAM
/*=======================================================
--
API TO SUBMIT CONCURRENT PROGRAM
--
FND_REQUEST.SUBMIT_REQUEST
==========================================================*/
L_REQUEST_ID NUMBER;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (USER_ID =>1318
,RESP_ID =>20420
,RESP_APPL_ID =>1);
,PROGRAM => 'XXX_STUDENT_INSERT'
,DESCRIPTION => NULL
,START_TIME => SYSDATE
,SUB_REQUEST => FALSE
,ARGUMENT1 => '140'
,ARGUMENT2 => 'JK'
,ARGUMENT3 => 'EEE'
,ARGUMENT4 => '12');
COMMIT;
IF L_REQUEST_ID = 0 THEN
DBMS_OUTPUT.PUT_LINE('REQUEST NOT SUBMITTED ERROR '|| FND_MESSAGE.GET);
ELSE
DBMS_OUTPUT.PUT_LINE('REQUEST SUBMITTED SUCCESSFULLY REQUEST ID ' || L_REQUEST_ID);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('UNEXPECTED ERRRO ' || SQLERRM);
-- Query
to get responsibilities
for the concurrent program
FCP.CONCURRENT_PROGRAM_NAME,
FAT.APPLICATION_NAME,
FRG.REQUEST_GROUP_NAME,
FNRT.RESPONSIBILITY_NAME
APPS.FND_CONCURRENT_PROGRAMS_TL FCPT,
APPS.FND_APPLICATION_TL FAT,
APPS.FND_REQUEST_GROUP_UNITS FRGU,
APPS.FND_REQUEST_GROUPS FRG,
APPS.FND_RESPONSIBILITY FNR,
APPS.FND_RESPONSIBILITY_TL FNRT
AND FCP.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
AND FNRT.LANGUAGE = 'US'
AND FCP.CONCURRENT_PROGRAM_NAME LIKE 'XX_EMP_INSERT_EXE';
--> QUERY FOR CONCURRENT PROGRAM NAME, CONCURRENT
PROGRAM NAME, APPLICATION NAME AND RESPONSIBILITY NAME:
FCP.CONCURRENT_PROGRAM_NAME,
FAT.APPLICATION_NAME,
FET.EXECUTABLE_NAME,
FATE.APPLICATION_NAME EXE_APPLICATION,
FLV.MEANING EXECUTION_METHOD,
FET.EXECUTION_FILE_NAME,
FCP.ENABLE_TRACE
FND_CONCURRENT_PROGRAMS FCP,
FND_APPLICATION_TL FAT,
FND_EXECUTABLES FET,
FND_APPLICATION_TL FATE,
FND_LOOKUP_VALUES FLV
WHERE FCPT.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FLV.LOOKUP_CODE = FET.EXECUTION_METHOD_CODE
4. Lookups
Navigation: Application Developer à Application à Lookups à Common
SELECT *
FROM
FND_LOOKUP_TYPES
WHERE
LOOKUP_TYPE LIKE 'XXX_REPONSIBILITY_NAME_LOOKUP';
Add values to the existing Lookup.
SELECT *
FROM
FND_LOOKUP_VALUES
WHERE
LOOKUP_TYPE LIKE 'XXX_REPONSIBILITY_NAME_LOOKUP';
API TO CREATE A LOKKUP TYPE:
V_ROWID VARCHAR2 (400);
BEGIN
FND_LOOKUP_TYPES_PKG.INSERT_ROW
X_LOOKUP_TYPE => 'XXX_TEST_LOOKUP',
X_SECURITY_GROUP_ID => 0,
X_VIEW_APPLICATION_ID => 3,
X_APPLICATION_ID => 0,
X_CUSTOMIZATION_LEVEL => 'U',
X_MEANING => 'XXX_TEST_LOOKUP',
X_DESCRIPTION => 'XXX_TEST_LOOKUP',
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => 0,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => 0,
X_LAST_UPDATE_LOGIN => -1);
DBMS_OUTPUT.PUT_LINE (V_ROWID);
COMMIT;
END;
SELECT *
FROM FND_LOOKUP_TYPES
WHERE LOOKUP_TYPE LIKE 'XXX_TEST_LOOKUP';
API to Add values to a existing lookup:
V_ROWID VARCHAR2 (400);
BEGIN
FND_LOOKUP_VALUES_PKG.INSERT_ROW (
X_ROWID => V_ROWID,
X_LOOKUP_TYPE => 'XXX_TEST_LOOKUP',
X_SECURITY_GROUP_ID => 0,
X_VIEW_APPLICATION_ID => 3,
X_LOOKUP_CODE => 'CODE1',
X_TAG => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ENABLED_FLAG => 'Y',
X_START_DATE_ACTIVE => SYSDATE,
X_END_DATE_ACTIVE => NULL,
X_TERRITORY_CODE => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_MEANING => 'XXX_TEST_LOOKUP',
X_DESCRIPTION => 'XXX_TEST_LOOKUP',
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => 0,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => 0,
X_LAST_UPDATE_LOGIN => -1
);
COMMIT;
END;
- Dependent
- Independent
- None
- Pair
- Special
- Table
- Translatable Independent
- Translatable Dependent
Here is an example for Creation of Independent Value Set.
Navigation: Application Developer à Application à Validation à Set
- Value set Name = User Defined Name
- List Type = List of Values , Long List of Values, Pop List
- Security Type = No Security, Hierarchical Security, Non Hierarchical Security
- Format Type = Char, Date, Date time, Number, Standard date, Standard Date time, time
- Maximum Size = Not more then 240
- Validation Type = Refer Above.
Now we need to Add values to the Dependent values set based on the dependent value set values.
Query to get value set values:
SELECT FFVS.FLEX_VALUE_SET_ID,FFVS.FLEX_VALUE_SET_NAME,
FFVS.DESCRIPTION SET_DESCRIPTION,
FFVS.VALIDATION_TYPE,
FFV.FLEX_VALUE,
FFVT.DESCRIPTION VALUE_DESCRIPTION,
FFV.ENABLED_FLAG,
FFV.LAST_UPDATE_DATE,
FFV.LAST_UPDATED_BY,
FFV.ATTRIBUTE1,
FFV.ATTRIBUTE2,
FFV.ATTRIBUTE3 --INCLUDE ATTRIBUTE VALUES BASED ON DFF SEGMENTS
FROM FND_FLEX_VALUE_SETS FFVS,
FND_FLEX_VALUES FFV,
FND_FLEX_VALUES_TL FFVT
WHERE FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
AND FLEX_VALUE_SET_NAME LIKE 'XX_COUNTRY_SET'
ORDER BY FLEX_VALUE
No comments:
Post a Comment