Thursday, December 19, 2013

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;
/
########################################################################################

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect