XXAA_ACCT_FLEXFIELD_REP - XXAA Account Flex Field Report

------>>>=================================================================================

XXAA_ACCT_FLEXFIELD_REP - XXAA Account Flex Field Report

------>>>=================================================================================

APPS123                         -->Replace with your APPS password

XXAA_ACCT_FLEXFIELD_REP         -->Replace with your concurrent program short name

XXAA Account Flex Field Report -->Replace with your Concurrent program name

CUST                            -->Replace with application short name

XXAA_GL_SRS_LEDGER_FLEXFIELD    -->Replace with your value set name

------>>>=================================================================================

DOWNLOAD

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

--> VALUE SET

FNDLOAD apps/apps123 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXAA_GL_SRS_LEDGER_FLEXFIELD.ldt VALUE_SET FLEX_VALUE_SET_NAME="XXAA_GL_SRS_LEDGER_FLEXFIELD"

-->CONC PRG:

FNDLOAD apps/apps123 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXAA_ACCT_FLEXFIELD_REP_CONC.ldt PROGRAM APPLICATION_SHORT_NAME="CUST" CONCURRENT_PROGRAM_NAME="XXAA_ACCT_FLEXFIELD_REP"

--> REQUEST GROUP

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXAA_ACCT_FLEXFIELD_REP_REQ.ldt REQUEST_GROUP REQUEST_GROUP_NAME="All Reports" APPLICATION_SHORT_NAME="SQLAP"

-->TEMP DD :

FNDLOAD apps/apps123 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXAA_ACCT_FLEXFIELD_REP_TEMP.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME = CUST DATA_SOURCE_CODE = XXAA_ACCT_FLEXFIELD_REP

-->Template:

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \

-DB_USERNAME apps \

-DB_PASSWORD apps123 \

-JDBC_CONNECTION '<CONNECTION NAME>' \

-LOB_TYPE TEMPLATE_SOURCE \

-APPS_SHORT_NAME CUST \

-LOB_CODE XXAA_ACCT_FLEXFIELD_REP \

-LANGUAGE en \

-XDO_FILE_TYPE RTF \

-FILE_CONTENT_TYPE 'application/rtf' \

-FILE_NAME $CUST_TOP/bin/XXAA_ACCT_FLEXFIELD_REP.rtf -CUSTOM_MODE FORCE

------>>>=================================================================================

UPLOAD

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

-->VALUE SET

FNDLOAD apps/apps123 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afffload.lct XXAA_GL_SRS_LEDGER_FLEXFIELD.ldt VALUE_SET FLEX_VALUE_SET_NAME="XXAA_GL_SRS_LEDGER_FLEXFIELD" APPLICATION_SHORT_NAME="XXPHT"

-->CONC PRG:

FNDLOAD apps/apps123 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXAA_ACCT_FLEXFIELD_REP_CONC.ldt - CUSTOM_MODE=FORCE

--> REQUEST GROUP

FNDLOAD apps/apps123 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXAA_ACCT_FLEXFIELD_REP_REQ.ldt

--> TEMP DD :

FNDLOAD apps/apps123 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXAA_ACCT_FLEXFIELD_REP_TEMP.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=CUST DATA_SOURCE_CODE=XXAA_ACCT_FLEXFIELD_REP TMPL_APP_SHORT_NAME=CUST TEMPLATE_CODE=XXAA_ACCT_FLEXFIELD_REP - CUSTOM_MODE=FORCE

-->TEMPLATE:

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \

-DB_USERNAME apps \

-DB_PASSWORD apps123 \

-JDBC_CONNECTION '<CONNECTION NAME>' \

-LOB_TYPE TEMPLATE_SOURCE \

-APPS_SHORT_NAME CUST \

-LOB_CODE XXAA_ACCT_FLEXFIELD_REP \

-LANGUAGE en \

-XDO_FILE_TYPE RTF \

-FILE_CONTENT_TYPE 'application/rtf' \

-FILE_NAME $CUST_TOP/bin/XXAA_ACCT_FLEXFIELD_REP.rtf -CUSTOM_MODE FORCE

------>>>=================================================================================

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

--> 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,

    fe.EXECUTION_FILE_NAME

FROM

    fnd_request_group_units frgu, fnd_concurrent_programs fcp,

    fnd_concurrent_programs_tl fcpt, fnd_request_groups frg,

    fnd_executables_vl 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 'XXAA Account Flex Field Report'

--   and frt.responsibility_name LIKE 'Project Billing Project Mgr - PHT'

   -- and frt.responsibility_name LIKE 'Project Billing ALL Project Access'

--ORDER BY 6,4,3,2

;  

==============================================================================

-->  To Delete The Concurrent Program and Excutable from Backend

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

BEGIN

    FND_PROGRAM.DELETE_PROGRAM('XXAA_ACCT_FLEXFIELD_REP','CUST');

    FND_PROGRAM.DELETE_EXECUTABLE('XXAA_ACCT_FLEXFIELD_REP','CUST');

    COMMIT;

END;

/

==============================================================================

SET SERVEROUTPUT ON

DECLARE

   -- Change the following two parameters

   var_templateCode    VARCHAR2 (100) := 'XXAA_ACCT_FLEXFIELD_REP';     -- Template Code

   boo_deleteDataDef   BOOLEAN := TRUE;     -- delete the associated Data Def.

BEGIN

   FOR RS

      IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,

                 T1.DATA_SOURCE_CODE,

                 T2.APPLICATION_SHORT_NAME DEF_APP_NAME

            FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2

           WHERE T1.TEMPLATE_CODE = var_templateCode

                 AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)

   LOOP

      XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

      DELETE FROM XDO_LOBS

            WHERE     LOB_CODE = var_templateCode

                  AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME

                  AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

      DELETE FROM XDO_CONFIG_VALUES

            WHERE     APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME

                  AND TEMPLATE_CODE = var_templateCode

                  AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE

                  AND CONFIG_LEVEL = 50;

      DBMS_OUTPUT.PUT_LINE ('Template ' || var_templateCode || ' deleted.');

      IF boo_deleteDataDef

      THEN

         XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,

                                            RS.DATA_SOURCE_CODE);

         DELETE FROM XDO_LOBS

               WHERE LOB_CODE = RS.DATA_SOURCE_CODE

                     AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME

                     AND LOB_TYPE IN

                            ('XML_SCHEMA',

                             'DATA_TEMPLATE',

                             'XML_SAMPLE',

                             'BURSTING_FILE');

         DELETE FROM XDO_CONFIG_VALUES

               WHERE     APPLICATION_SHORT_NAME = RS.DEF_APP_NAME

                     AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE

                     AND CONFIG_LEVEL = 30;

         DBMS_OUTPUT.PUT_LINE (

            'Data Defintion ' || RS.DATA_SOURCE_CODE || ' deleted.');

      END IF;

   END LOOP;

COMMIT;

--   DBMS_OUTPUT.PUT_LINE ('Issue a COMMIT to make the changes or ROLLBACK to revert.');

EXCEPTION

   WHEN OTHERS

   THEN

      ROLLBACK;

      DBMS_OUTPUT.PUT_LINE (

         'Unable to delete XML Publisher Template ' || var_templateCode);

      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));

END;

/

########################################################################################