Query to find Responsibility and its Operating Unit

Get Responsibility Name  from Org id
--------------------------------------------------------
SELECT   frv.responsibility_name,
         fpov.profile_option_value org_id,
         hou.NAME
    FROM apps.fnd_profile_options_vl fpo,
         apps.fnd_responsibility_vl frv,
         apps.fnd_profile_option_values fpov,
         apps.hr_organization_units hou
   WHERE hou.NAME = :P_Operating_unit_name
     AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
     AND fpo.profile_option_id = fpov.profile_option_id
     AND fpo.user_profile_option_name = 'MO: Operating Unit'
     AND frv.responsibility_id = fpov.level_value
ORDER BY frv.responsibility_name

Query to Find Concurrent Program and its Parameters List

SELECT cp.user_concurrent_program_name ,
cp.concurrent_program_name ,
ap.application_name ,
cp.description,
cp.enabled_flag,
cp.output_file_type ,
cx.executable_name ,
lv.meaning ,
cx.user_executable_name ,
df.column_seq_num ,
df.end_user_column_name ,
df.description,
df.enabled_flag,
df.required_flag ,
df.security_enabled_flag ,
df.display_flag ,
fvs.flex_value_set_name ,
df.default_type ,
df.default_value
FROM apps.fnd_concurrent_programs_vl cp,
apps.fnd_executables_form_v cx,
apps.fnd_application_vl ap,
apps.fnd_descr_flex_col_usage_vl df,
apps.fnd_flex_value_sets fvs,
apps.fnd_lookup_values lv
WHERE cp.executable_id=cx.executable_id
AND cp.application_id=ap.application_id
AND fvs.flex_value_set_id=df.flex_value_set_id
AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lv.lookup_code = cx.execution_method_code
AND cp.user_concurrent_program_name like 'Concurrent Program Name'
AND df.descriptive_flexfield_name = '$SRS$.'||cp.concurrent_program_name
AND lv.language='US'
ORDER BY df.column_seq_num

Query to find Profile Options and its values

SELECT DISTINCT

pro1.profile_option_name,

pov.profile_option_id,

    substr(pro1.user_profile_option_name,1,35) profile,

    DECODE(pov.level_id,10001,'Site',10002,'Application',10003,'Resp',10004,'User') option_level,    DECODE(pov.level_id,10001,'Site',10002,appl.application_short_name,10003,resp.responsibility_name,10004,u.user_name) level_value,

    nvl(pov.profile_option_value,'Is Null') profile_option_value

FROM

    fnd_profile_option_values pov,

    fnd_responsibility_tl resp,

    fnd_application appl,

    fnd_user u,

    fnd_profile_options pro,

    fnd_profile_options_tl pro1

WHERE

    pro.profile_option_name = pro1.profile_option_name

    AND   pro.profile_option_id = pov.profile_option_id

    AND   pov.level_value = resp.responsibility_id (+)

    AND   pov.level_value = appl.application_id (+)

    AND   pov.level_value = u.user_id (+)

    AND pro1.profile_option_name IN ('profile_option_name')

ORDER BY

    1,

    2;

Query to Find Request groups for given concurrent Program

 SELECT cpt.user_concurrent_program_name     "Concurrent Program Name",
       DECODE(rgu.request_unit_type,
              'P', 'Program',
              'S', 'Set',
              rgu.request_unit_type)        "Unit Type",
       cp.concurrent_program_name           "Concurrent Program Short Name",
       rg.application_id                    "Application ID",
       rg.request_group_name                "Request Group Name",
       fat.application_name                 "Application Name",
       fa.application_short_name            "Application Short Name",
       fa.basepath                          "Basepath"
  FROM fnd_request_groups          rg,
       fnd_request_group_units     rgu,
       fnd_concurrent_programs     cp,
       fnd_concurrent_programs_tl  cpt,
       fnd_application             fa,
       fnd_application_tl          fat
 WHERE rg.request_group_id       =  rgu.request_group_id
   AND rgu.request_unit_id       =  cp.concurrent_program_id
   AND cp.concurrent_program_id  =  cpt.concurrent_program_id
   AND rg.application_id         =  fat.application_id
   AND fa.application_id         =  fat.application_id
   AND cpt.language              =  USERENV('LANG')
   AND fat.language              =  USERENV('LANG')
   AND cpt.user_concurrent_program_name = '&coc prog name';

Query to find Workflow Inbound and Outbound Server Details.

SELECT fscpv.*

              FROM fnd_svc_comp_params_b   fscpb

                  ,fnd_svc_comp_param_vals fscpv

             WHERE fscpv.parameter_id = fscpb.parameter_id

               AND fscpb.component_type = 'WF_MAILER'

               AND fscpb.parameter_name = 'OUTBOUND_SERVER'

               AND EXISTS ( SELECT 1

                              FROM fnd_svc_comp_params_b   fscpb

                                  ,fnd_svc_comp_param_vals fscpv

                             WHERE UPPER(fscpv.parameter_value) = 'SMTP'

                               AND fscpv.parameter_id   = fscpb.parameter_id

                               AND fscpb.component_type = 'WF_MAILER'

                               AND fscpb.parameter_name = 'OUTBOUND_PROTOCOL' );

FNDLOAD UPLOAD & DOWNLOAD COMMANDS

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"

Concurrent Program without value Set Values
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name" P_VSET_DOWNLOAD_CHILDREN="N"

IF P_VSET_DOWNLOAD_CHILDREN is N , LDT will not fetch values of value set.
IF P_VSET_DOWNLOAD_CHILDREN is Y , LDT will fetch all the values of value set


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

13. Adding Concurrent Program/Request set to Request group
--- ----------------------------------------------------------
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_REQUEST_GRP.ldt REQUEST_GROUP REQUEST_GROUP_NAME="REQUEST_GROUP_NAME" APPLICATION_SHORT_NAME='SQLAP' UNIT_APP="CONC_APPL_SHRT_NAME" UNIT_NAME="CONC_PRG_SHORT_NAME"

FNDLOAD apps/$pass 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_REQUEST_GRP.ldt


14.Form Personalization
-----------------------------

FNDLOAD commands to download form personalization-

FNDLOAD / 0 Y DOWNLOAD FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES form_name=

e.g.

FNDLOAD apps/apps_pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XXCMST_MSCFNSCW.ldt FND_FORM_CUSTOM_RULES form_name=MSCFNSCW

FNDLOAD commands to upload form personlization-

FNDLOAD / 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct

e.g.

FNDLOAD apps/apps_pwd 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXCMST_MSCFNSCW.ldt

XML Publisher Template and Data Definition Details Query

SELECT distinct(XL.file_name )"File Name",
xl.last_update_date,
XDDV.data_source_code "Data Definition Code",
       XDDV.data_source_name "Data Definition",
       XDDV.description "Data Definition Description",
       XTB.template_code "Template Code",
       XTT.template_name "Template Name",
       XTT.description "Template Description",
       XTB.template_type_code "Type",
       XL.file_name "File Name",
       XTB.default_output_type "Default Output Type"
  FROM apps.XDO_DS_DEFINITIONS_VL XDDV,
       apps.XDO_TEMPLATES_B XTB,
       apps.XDO_TEMPLATES_TL XTT,
       apps.XDO_LOBS XL,
       apps.FND_APPLICATION_TL FAT,
       APPS.FND_APPLICATION FA
WHERE      XDDV.DATA_SOURCE_CODE like 'XX_FILE_NAME%'
       --AND FAT.application_name = '&Application_Name'
       AND XDDV.application_short_name = FA.application_short_name
       AND FAT.application_id = FA.application_id
       AND XTB.application_short_name = XDDV.application_short_name
       AND XDDV.data_source_code = XTB.data_source_code
       AND XTT.template_code = XTB.template_code
       AND XL.LOB_CODE = XTB.TEMPLATE_CODE   
       AND XL.XDO_FILE_TYPE = XTB.TEMPLATE_TYPE_CODE;