FND Load Script ldt file download and upload script

 1-> Lookups

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

FNDLOAD apps/apps O 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"

 

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

 

2-> Concurrent Program

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

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

 

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

 

3-> Profile

-- ---------

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"

 

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

 

4-> Request Set and Link

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

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_CUSTOM_RS.ldt REQ_SET REQUEST_SET_NAME='REQUEST_SET_NAME'

 

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XX_CUSTOM_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

 

5-> FND Message

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

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"

 

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

 

6-> D2K FORMS

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

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt FORM FORM_NAME="FORM_NAME"

      

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

 

7-> Form Function

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

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt FUNCTION FUNCTION_NAME="FORM_FUNCTION_NAME"

 

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

 

8-> Alerts

-- ---------

FNDLOAD apps/apps 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"

 

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE

 

9-> Value Set

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

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

 

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

 

10-> Data Definition and Associated Template

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

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_SOURCE_CODE'

 

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt

 

11-> DATA_TEMPLATE (Data Source .xml file)

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

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -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

 

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -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 -XDO_FILE_TYPE XML -FILE_NAME $DATA_FILE_PATH/$DATA_FILE_NAME.xml -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

 

12-> RTF TEMPLATE (Report Layout .rtf file)

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

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -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 US -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

 

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE RTF -FILE_NAME $RTF_FILE_PATH/$RTF_FILE_NAME.rtf -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

How to add system administrator responsibility from backend || How to add system administrator responsibility to user from backend

 How to add system administrator responsibility from backend || How to add system administrator responsibility to user from backend

BEGIN

  

   FND_USER_PKG.ADDRESP(

      USERNAME        =>  'MMANOHAR',              -- Add your User Name Here

      RESP_APP        =>  'SYSADMIN',             -- Apps Short Name

      RESP_KEY        =>  'SYSTEM_ADMINISTRATOR', -- Responsibility Key

      SECURITY_GROUP  =>  'STANDARD',

      DESCRIPTION     =>  NULL,

      START_DATE      =>  SYSDATE,

      END_DATE        =>  NULL);

  

   COMMIT;

  

   DBMS_OUTPUT.PUT_LINE('SYSADMIN Responsibility successfully added');

    

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('SYSADMIN responsibility not added due to ' || SQLERRM);

      ROLLBACK;

END;

how to get Responsibility used by Concurrent Request Id

##how to get Responsibility  used by Concurrent Request Id.###

####Provide the Concurrent Program Request_ID  as input###

select c.request_id, r.responsibility_name

from fnd_responsibility_vl r, fnd_concurrent_requests c

where c.request_id = 106765679

and c.responsibility_application_id = r.application_id

and c.responsibility_id = r.responsibility_id

####SQL to get Concurrent Request ID for a Concurrent Program###

SELECT a.REQUEST_ID,d.user_concurrent_program_name FROM  APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_programs c,APPLSYS.fnd_concurrent_programs_tl d  

where  a.program_application_id = d.application_id

and a.concurrent_program_id = c.concurrent_program_id

and a.program_application_id = c.application_id 

and d.user_concurrent_program_name like 'NAPP Missing Access Key%'

####SQL to get the details of the Scheduled Concurrrent Programs #####

SELECT fcr.request_id,

       DECODE(fcpt.user_concurrent_program_name,

              'Report Set',

              'Report Set:' || fcr.description,

              fcpt.user_concurrent_program_name) CONC_PROG_NAME,

       argument_text PARAMETERS,

       NVL2(fcr.resubmit_interval,

            'PERIODICALLY',

            NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,

       DECODE(NVL2(fcr.resubmit_interval,

                   'PERIODICALLY',

                   NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),

              'PERIODICALLY',

              'EVERY ' || fcr.resubmit_interval || ' ' ||

              fcr.resubmit_interval_unit_code || ' FROM ' ||

              fcr.resubmit_interval_type_code || ' OF PREV RUN',

              'ONCE',

              'AT :' ||

              TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),

              'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,

       fu.user_name USER_NAME,

       requested_start_date START_DATE

  FROM apps.fnd_concurrent_programs_tl fcpt,

       apps.fnd_concurrent_requests    fcr,

       apps.fnd_user                   fu,

       apps.fnd_conc_release_classes   fcrc

 WHERE fcpt.application_id = fcr.program_application_id

   AND fcpt.concurrent_program_id = fcr.concurrent_program_id

   AND fcr.requested_by = fu.user_id

   AND fcr.phase_code = 'P'

   AND fcr.requested_start_date > SYSDATE

   AND fcpt.LANGUAGE = 'US'

   AND fcrc.release_class_id(+) = fcr.release_class_id

   AND fcrc.application_id(+) = fcr.release_class_app_id;

### Sql Query to get Concurrent program name and its parameter 

SELECT fcpl.user_concurrent_program_name     , 

               fcp.concurrent_program_name     , 

               par.end_user_column_name     , 

               par.form_left_prompt prompt     , 

               par.enabled_flag     , 

               par.required_flag     , 

               par.display_flag

FROM   fnd_concurrent_programs fcp     , 

              fnd_concurrent_programs_tl fcpl     , 

              fnd_descr_flex_col_usage_vl par

WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id

     AND  fcpl.user_concurrent_program_name = &conc_prg_name

     AND  fcpl.LANGUAGE = 'US'

     AND  par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name

Sql Query to get the responsibility name,request group name when request set name as input. 

select frt.responsibility_name,  

         frg.request_group_name,

         frgu.request_unit_type,

         frgu.request_unit_id,

         fcpt.user_request_set_name

From apps.fnd_Responsibility fr,

         apps.fnd_responsibility_tl frt,

         apps.fnd_request_groups frg,

         apps.fnd_request_group_units frgu,

         apps.fnd_request_Sets_tl fcpt

where frt.responsibility_id = fr.responsibility_id

    and frg.request_group_id = fr.request_group_id

    and frgu.request_group_id = frg.request_group_id

    and fcpt.request_set_id = frgu.request_unit_id

    and frt.language = USERENV('LANG')

    and fcpt.language = USERENV('LANG')

    and fcpt.user_request_set_name = '&request_set_name'

 order by 1,2,3,4

**** How to change table rows into column ****

 **** How to change table rows into 1 column  ****

Ex:1

select listagg(aa.name, ',') within group (order by aa.name) from (

SELECT *  

FROM apps.pa_projects_all ppa

where ppa.segment1='15832'

) aa

**** How to change table rows into column  ****

Ex:2

select listagg(aa.name, ',') within group (order by aa.name) from (

SELECT *  

FROM apps.pa_projects_all ppa

where ppa.project_type like '%Admin Support%'

and last_update_date>sysdate-30

) aa

How To Submit a Concurrent Request from backend

 /*********************************************************

*PURPOSE: To Submit a Concurrent Request from backend    *

*AUTHOR: Shailender Thallam                              *

**********************************************************/

--

DECLARE

l_responsibility_id NUMBER;

l_application_id    NUMBER;

l_user_id            NUMBER;

l_request_id            NUMBER;

BEGIN

  --

  SELECT DISTINCT fr.responsibility_id,

    frx.application_id

     INTO l_responsibility_id,

    l_application_id

     FROM apps.fnd_responsibility frx,

    apps.fnd_responsibility_tl fr

    WHERE fr.responsibility_id = frx.responsibility_id

  AND LOWER (fr.responsibility_name) LIKE LOWER('XXTest Resp');

  --

   SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'ENTER YOUR USER NAME';

  --

  --To set environment context.

  --

  apps.fnd_global.apps_initialize (l_user_id,l_responsibility_id,l_application_id);

  --

  --Submitting Concurrent Request

  --

  l_request_id := fnd_request.submit_request ( 

                            application   => 'XXCUST', 

                            program       => 'XXEMP', 

                            description   => 'XXTest Employee Details', 

                            start_time    => sysdate, 

                            sub_request   => FALSE,

    argument1     => 'Smith'

  );

  --

  COMMIT;

  --

  IF l_request_id = 0

  THEN

     dbms_output.put_line ('Concurrent request failed to submit');

  ELSE

     dbms_output.put_line('Successfully Submitted the Concurrent Request');

  END IF;

  --

EXCEPTION

WHEN OTHERS THEN

  dbms_output.put_line('Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||sqlerrm);

END;

/