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;

/

how to check concurrent program parameters from backend || Query concurrent program parameters from backend

 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,

       ffv.flex_value_set_name

  FROM apps.fnd_concurrent_programs     fcp,

       apps.fnd_concurrent_programs_tl  fcpl,

       apps.fnd_descr_flex_col_usage_vl par,

       apps.fnd_flex_value_sets         ffv

 WHERE     fcp.concurrent_program_id = fcpl.concurrent_program_id

       AND fcpl.user_concurrent_program_name like '%Concurrent Program Name%' 

       AND fcpl.LANGUAGE = 'US'

       AND par.descriptive_flexfield_name =

              '$SRS$.' || fcp.concurrent_program_name

       AND ffv.flex_value_set_id = par.flex_value_set_id;

Oracle error 904: ORA-00904: : invalid identifier

 Oracle error 904: ORA-00904: : invalid identifier

ORA-06512: at "SYSTEM.AD_DDL", line 2376

ORA-06512: at line 1 has been detected in afuddl() [6_xad].

do_array_ddl(APPLSYS, PA, 2, 1, 179, PA_EVENTS_DFV): private_do_array_ddl(APPS, APPLSYS, PA, 2, 1, 179, PA_EVENTS_DFV): do_array_c_view(1, APPS, PA_EVENTS_DFV, 1, 179): array_assign_and_execute(APPS, 1, 179, FALSE): : do_apps_array_ddl(APPS, 1, 179, FALSE): : substr($statement$,1,255)='CREATE OR REPLACE VIEW  PA_EVENTS_DFV 

 (ROW_ID, "CONTEXT_VALUE", PO_Number, PO_Line_Number, Employee, Quantity, Unit_Price, Item_Code, UOM, Attachment, 1111_TRANSACTION_NUM, PO_Line_Numb_,  

 CONCATENATED_SEGMENTS)  

 AS SELECT ROWID, ATTRIBUTE_CATEGORY,'

CREATE OR REPLACE VIEW PA_EVENTS_DFV 

 AS SELECT 'View generation has failed. Check log file for error messages' VIEW_HAS_FAILED_CHECK_LOG_FILE 

 FROM SYS.DUAL

+---------------------------------------------------------------------------+

Start of log messages from FND_FILE

+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+

End of log messages from FND_FILE

+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+

No completion options were requested.

Output file size: 

866

Output is not being printed because:

The print option has been disabled for this report.

Solution:

Check the Column Names, Make sure Column name shouldn't start with Number or Special Characters

Concurrent request shows Inactive phase with No-Manager status

 A concurrent request has a life cycle consisting of the following phases: Pending, Running, and Completed. During each phase, a request has a specific status. Listed below are the possible statuses for each phase:

Pending Phase - Normal, Standby, Scheduled, Waiting

Running Phase - Normal, Paused, Resuming, Terminating

Completed Phase - Normal, Error, Warning, Cancelled, Terminated

Inactive Phase - Disabled, On Hold, No Manager

If a concurrent request is on hold or unable to run when there are no active manager processes that can run the request, the request is placed in an Inactive phase.

Review the following points when the concurrent request is in Inactive phase with No Manager status.

1. Verify that Internal Concurrent Manager(ICM) is up and running. Use any one navigation mentioned below to check the status details of Internal Manager.

i) Oracle Applications Manager(OAM) > Site Map > Monitoring > Availability > Internal Concurrent Manager > View Status.

OR

ii) System Administrator Responsibility > Concurrent > Manager > Administer

2. Verify that there is at least one active concurrent manager with/without specialization rules that allow the concurrent program to run.

i) Run the following query to check whether any specialization rule defined for any concurrent manager that includes/excludes the concurrent program in question. Query returns 'no rows selected' when there are no Include/Exclude specialization rules of Program type for the given concurrent program.

select 'Concurrent program '||fcp.concurrent_program_name||' is ' ||decode(fcqc.include_flag,'I','included in ','E','excluded from ')||fcqv.user_concurrent_queue_name specialization_rule_details from fnd_concurrent_queues_vl fcqv,fnd_concurrent_queue_content fcqc,fnd_concurrent_programs fcp where fcqv.concurrent_queue_id=fcqc.concurrent_queue_id and fcqc.type_id=fcp.concurrent_program_id and fcp.concurrent_program_name='<PROGRAM_SHORT_NAME>';

Note: Program Short Name is visible when the program is queried in concurrent program definition form.

Example:

SQL> select 'Concurrent program '||fcp.concurrent_program_name||' is ' ||decode(fcqc.include_flag,'I','included in ','E','excluded from ')||fcqv.user_concurrent_queue_name specialization_rule_details from fnd_concurrent_queues_vl fcqv,fnd_concurrent_queue_content fcqc,fnd_concurrent_programs fcp where fcqv.concurrent_queue_id=fcqc.concurrent_queue_id and fcqc.type_id=fcp.concurrent_program_id and fcp.concurrent_program_name='OKCRAQE';

SPECIALIZATION_RULE_DETAILS

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

Concurrent program OKCRAQE is included in Contracts Core Concurrent Manager

Concurrent program OKCRAQE is excluded from Standard Manager 

From the sample output above, it shows that the OKCRAQE(Listener for Events Queue) concurrent program has been excluded from the Standard Manager and included in Contracts Core Concurrent Manager. That means the concurrent request OKCRAQE can be run only by the Contracts Core Concurrent Manager which should be up and running to run and complete the OKCRAQE concurrent request.

Make sure that Concurrent Manager whose specialization rule includes the concurrent program is up and running. 

ii) Ensure that standard concurrent manager is up and running.

Follow the below step only when you have confirmed the previous points and the issue is still remaining as there may be an issue with concurrent request queue view.

3. Manually re-create the concurrent request queue view for concurrent managers by entering the following command as an applmgr user at operating system prompt.

FNDLIBR FND FNDCPBWV apps/<pwd> SYSADMIN 'System Administrator' SYSADMIN

Successful completion of the above command shows the message "View created successfully" in the generated log file.

The concurrent request queue view is used internally to map requests to managers. This view would be regenerated when concurrent managers are created, or specialization rules are altered.

Warning!!! Due to high volume of data, got out of memory exception

 

***Warning!!! Due to high volume of data, got out of memory exception...***

****Please retry with scalable option or modify the Data template to run in scalable mode...****

This Message will come Due to high volume of data.

Example:

Solution-1->

Increase the Memory for the Concurrent Program

1. Go to "System Administrator" responsibility.

2. Navigate to Concurrent - > Program - > Define.

3. Search for the report (example Account Analysis Report) or Executable Short Name ORXLAAARPT"  in Concurrent Process window Ctl+F11 or Alt V+Q+R

4. Set the Options field to -Xss2560k -Xmx2560m (make sure you DONT MISS OUT the "-" before the X).

5. Save.

6. Bounce the Concurrent Manager for the effects to take place with the help of DBA.

Xss Sets the JVM to 2.5GB .
Xmx Sets heap size to 2.5 GB. ( For 32 bit JVM this is the maximum memory which can be allocated).

solution-2 ->

 Set the scalable option in data template (XML Publisher >> Data Definition >> Data Template. Xml )

<properties>

   <property name="scalable_mode" value="on" />

</properties>