Protected by Copyscape Web Copyright Protection Software

Search This Blog

Thursday, June 20, 2013

XXST_INT_UTILITY_PKG sample script



CREATE OR REPLACE PACKAGE XXST_INT_UTILITY_PKG
AS
/*PROCEDURE MAINTAIN_AUDIT_DATA_PRC(pn_batch_id     IN  NUMBER,
         pn_conc_request_id           IN  NUMBER,
                              pc_entity           IN  VARCHAR2,
                              pc_table_name                IN  VARCHAR2,
                              pn_count_recd                IN  NUMBER,
                              pn_count_errd                IN  NUMBER,
                              pn_count_uploaded            IN  NUMBER);*/
PROCEDURE GENERATE_ERROR_LOG_PRC (pn_batch_id              IN  NUMBER,
                              pn_conc_request_id         IN  NUMBER,
                              pc_table_name            IN  VARCHAR2,
                              pc_column_name              IN  VARCHAR2,
                              pc_key_column               IN  VARCHAR2,
                              pc_key_column_id            IN  VARCHAR2 DEFAULT NULL,
                              pc_key_column_value         IN  VARCHAR2 DEFAULT NULL,
                              pc_error_text            IN  VARCHAR2,
                              pn_rec_number      IN  NUMBER);
PROCEDURE PRT_AUDIT_ERROR_LOG_DATA( pn_batch_no   IN  NUMBER
                                        , pn_conc_request_no IN  NUMBER);
PROCEDURE GET_NEW_CUST_SITE_DTLS
( p_old_org_id     IN   NUMBER
, p_new_org_id     IN   NUMBER
, p_orig_sys_ref   IN   VARCHAR2
, p_site_use_code  IN   VARCHAR2
, p_customer_id    OUT  NUMBER
, p_site_use_id    OUT  NUMBER
, p_address_id     OUT  NUMBER
);
PROCEDURE GET_NEW_CUST_SITE_DTLS
( p_old_org_id     IN   NUMBER
, p_new_org_id     IN   NUMBER
, p_orig_sys_ref   IN   VARCHAR2
, p_customer_id    OUT  NUMBER
, p_address_id     OUT  NUMBER
);
PROCEDURE GET_NEW_CUST_NUMBER
( p_old_New_cust_number IN OUT VARCHAR2 );
FUNCTION GET_DATALOAD_USER_ID RETURN NUMBER;
END XXST_INT_UTILITY_PKG;
/



CREATE OR REPLACE PACKAGE BODY XXST_INT_UTILITY_PKG
IS
/********************************************************************************************************************************
*Name              : Rajashekar
*Purpose           : The Package is Used for Finding Mapping Values and Recording Error and Audit Information
*C and I Number    : 1
*Arguments         :
*Script Location   :
*   Arg1 - Describe arg1
*   Arg2 - Describe arg2
*Notes      : This Package Specification defines the Procedure Defined in the Package
*           : Calls 1 Packaged Procedure TKM_CONV_UTILITY_PKG
*      1. Special usage notes
*      2. Special usage notes
*Change History
*       Date      Name    Ver Description
*--------------------------------------------------------------------------------------------------------------------------------
*   27-Mar-2013  Rajashekar   1.0 Used for Finding Mapping Values and Recording Error and Audit Information
*********************************************************************************************************************************/
   /*PROCEDURE maintain_audit_data_prc (
      pn_batch_id          IN   NUMBER,
      pn_conc_request_id   IN   NUMBER,
      pc_entity            IN   VARCHAR2,
      pc_table_name        IN   VARCHAR2,
      pn_count_recd        IN   NUMBER,
      pn_count_errd        IN   NUMBER,
      pn_count_uploaded    IN   NUMBER
   )
   IS
      err_num   NUMBER;
      err_msg   VARCHAR2 (255);
   BEGIN
      INSERT INTO XXFTAL_INT_AUDIT_DATA
                  (batch_id, conc_request_id, date_run, entity,
                   table_name, count_received, count_errored,
                   count_uploaded, last_update_date, last_updated_by,
                   creation_date, created_by, last_update_login
                  )
           VALUES (pn_batch_id, pn_conc_request_id, SYSDATE, pc_entity,
                   pc_table_name, pn_count_recd, pn_count_errd,
                   pn_count_uploaded, SYSDATE, fnd_global.login_id,
                   SYSDATE, fnd_global.user_id, fnd_global.login_id
                  );
      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         err_num := SQLCODE;
         err_msg := SUBSTR (SQLERRM (err_num), 1, 100);
         DBMS_OUTPUT.put_line (   'Error Number: '
                               ||    err_num
                               ||    ' Error Msg: '
                               ||    err_msg
                              );
   END maintain_audit_data_prc;*/
-----------------------------
   PROCEDURE generate_error_log_prc (
      pn_batch_id           IN   NUMBER,
      pn_conc_request_id    IN   NUMBER,
      pc_table_name         IN   VARCHAR2,
      pc_column_name        IN   VARCHAR2,
      pc_key_column         IN   VARCHAR2,
      pc_key_column_id      IN   VARCHAR2 DEFAULT NULL,
      pc_key_column_value   IN   VARCHAR2 DEFAULT NULL,
      pc_error_text         IN   VARCHAR2,
      pn_rec_number         IN   NUMBER
   )
   IS
      err_num   NUMBER;
      err_msg   VARCHAR2 (255);
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO XXST_INT_ERROR_LOG
                  (batch_id, conc_request_id, date_run, table_name,
                   column_name, key_column, key_column_id,
                   key_column_value, ERROR_TEXT, rec_number,
                   last_update_date, last_updated_by, creation_date,
                   created_by, last_update_login
                  )
           VALUES (pn_batch_id, pn_conc_request_id, SYSDATE, pc_table_name,
                   pc_column_name, pc_key_column, pc_key_column_id,
                   pc_key_column_value, pc_error_text, pn_rec_number,
                   SYSDATE, fnd_global.login_id, SYSDATE,
                   fnd_global.user_id, fnd_global.login_id
                  );
      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         err_num := SQLCODE;
         err_msg := SUBSTR (SQLERRM (err_num), 1, 100);
         DBMS_OUTPUT.put_line (   'Error Number: '
                               || err_num
                               || ' Error Msg: '
                               || err_msg
                              );
         ROLLBACK;
   END generate_error_log_prc;
-----------------------------------
   PROCEDURE prt_audit_error_log_data (
      pn_batch_no          IN   NUMBER,
      pn_conc_request_no   IN   NUMBER
   )
   IS
      /*CURSOR lcur_file_name
      IS
         SELECT file_name
           FROM xxtk_file_process
          WHERE file_process_id = pn_batch_no;*/
      cursor cur_max_lenth(pn_batch_id number,pn_conc_request_id number) is
      select max(length(key_column))+3 key_column,max(length(key_column_id))+3 key_column_id
      from   XXST_INT_ERROR_LOG
      where  batch_id        = pn_batch_id
      AND    conc_request_id = pn_conc_request_id;
      ln_key_column_max    number(12);
      ln_key_column_id_max number(12);
      lc_file_name              VARCHAR2 (500);
      -- Audit Data Variables
      lc_error_message_server   VARCHAR2 (1000);
   BEGIN
      IF pn_batch_no IS NOT NULL AND pn_conc_request_no IS NOT NULL
      THEN
         --OPEN lcur_file_name;
         --FETCH lcur_file_name
          --INTO lc_file_name;
         --CLOSE lcur_file_name;
          --SExxtk_file_process
          -- Input Parameters Given
         -- Fnd_File.put_line(Fnd_File.OUTPUT,'Input Parameters: ');
         fnd_file.put_line (fnd_file.output,
                                 RPAD ('Audit Summary Information:', 30, ' ')
                                );
         fnd_file.put_line (fnd_file.output, '');
         fnd_file.put_line (fnd_file.output, '');
         fnd_file.put_line (fnd_file.output,
                                    RPAD ('File Name:', 30, ' ')
                                 || CHR (9)
                                 || lc_file_name
                                );
         fnd_file.put_line (fnd_file.output,
                                    RPAD ('Request Id:', 30, ' ')
                                 || CHR (9)
                                 || pn_conc_request_no
                                );
         fnd_file.put_line (fnd_file.output,
                                    RPAD ('Batch Number:', 30, ' ')
                                 || CHR (9)
                                 || pn_batch_no
                                );
         fnd_file.put_line (fnd_file.output, '');
         fnd_file.put_line (fnd_file.output, '');
         -- Start Processing the Audit Header Information Details
        /* BEGIN
            FOR cauditdatarec IN (SELECT   ROWID, cad.*
                                      FROM XXFTAL_INT_AUDIT_DATA cad
                                     WHERE batch_id = pn_batch_no
                                       AND conc_request_id =
                                                            pn_conc_request_no
                                  ORDER BY table_name)
            LOOP
               -- Start Populating the Header Information For the Report
               fnd_file.put_line (fnd_file.output,
                                          RPAD ('Entity Name:', 30, ' ')
                                       || CHR (9)
                                       || cauditdatarec.entity
                                      );
               fnd_file.put_line (fnd_file.output,
                                          RPAD ('Table Name:', 30, ' ')
                                       || CHR (9)
                                       || cauditdatarec.table_name
                                      );
               fnd_file.put_line (fnd_file.output,
                                          RPAD ('Total Records Processed:',
                                                30,
                                                ' '
                                               )
                                       || CHR (9)
                                       || cauditdatarec.count_received
                                      );
               fnd_file.put_line (fnd_file.output,
                                          RPAD ('Total Records Success:',
                                                30,
                                                ' '
                                               )
                                       || CHR (9)
                                       || cauditdatarec.count_uploaded
                                      );
               fnd_file.put_line (fnd_file.output,
                                          RPAD ('Total Records Errored:',
                                                30,
                                                ' '
                                               )
                                       || CHR (9)
                                       || cauditdatarec.count_errored
                                      );
               fnd_file.put_line (fnd_file.output, '');
            END LOOP;                                         -- End of Loop 1
         EXCEPTION
            WHEN OTHERS
            THEN
               lc_error_message_server :=
                                    lc_error_message_server || ' ' || SQLERRM;
               fnd_file.put_line
                                   (fnd_file.LOG,
                                       'ERROR IN LOOP FOR AUDIT DATA TABLE: '
                                    || CHR (9)
                                    || lc_error_message_server
                                   );
         END;*/
         fnd_file.put_line
            (fnd_file.output,
             '---------------------------------------------------------------------------------------------------------------------------------------------------------------------'
            );
         -- Start Processing the Error Log Details
         open cur_max_lenth(pn_batch_no,pn_conc_request_no);
         fetch cur_max_lenth
         into  ln_key_column_max,ln_key_column_id_max;
         close cur_max_lenth;
         if nvl(ln_key_column_max,0) = 0 then
            ln_key_column_max := 30;
         end if;
         if nvl(ln_key_column_id_max,0) = 0 then
            ln_key_column_id_max := 30;
         end if;  
         fnd_file.put_line (fnd_file.output,
                                    '|'
                                 || RPAD ('Key Columns:', ln_key_column_max, ' ')
                                 || '|'
                                 || RPAD ('Key Column Values:', ln_key_column_id_max, ' ')
                                 || '|'
                                 || 'Error  Text:|'
                                );
         BEGIN
            FOR centityerrorlogrec IN (SELECT   ROWID, cel.*
                                           FROM XXST_INT_ERROR_LOG cel
                                          WHERE batch_id = pn_batch_no
                                            AND conc_request_id =
                                                            pn_conc_request_no
                                       ORDER BY table_name)
            LOOP
               -- Star Populating the Detail Error Log Information for all the Records
               --Fnd_File.put_line(Fnd_File.OUTPUT,'|'||RPAD(CEntityErrorLogRec.REC_NUMBER,15,' ')||'|'||RPAD(CEntityErrorLogRec.TABLE_NAME,40,' ')||'|'||CEntityErrorLogRec.ERROR_TEXT||'|'); -- CMB SAJI 07022009
               fnd_file.put_line
                                   (fnd_file.output,
                                       '|'
                                    || RPAD (centityerrorlogrec.key_column,
                                             ln_key_column_max,
                                             ' '
                                            )
                                    || '|'
                                    || RPAD (centityerrorlogrec.key_column_id,
                                             ln_key_column_id_max,
                                             ' '
                                            )
                                    || '|'
                                    || centityerrorlogrec.ERROR_TEXT
                                    || '|'
                                   );                    -- ADDB SAJI 07022009
            END LOOP;                                         -- End of Loop 2
         EXCEPTION
            WHEN OTHERS
            THEN
               lc_error_message_server :=
                                    lc_error_message_server || ' ' || SQLERRM;
               fnd_file.put_line
                                    (fnd_file.LOG,
                                        'ERROR IN LOOP FOR ERROR LOG TABLE: '
                                     || CHR (9)
                                     || lc_error_message_server
                                    );
         END;
      ELSE
         fnd_file.put_line
                           (fnd_file.LOG,
                            'Value Missing for Batch_No and Conc_Request_No '
                           );
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         lc_error_message_server := lc_error_message_server || ' ' || SQLERRM;
         fnd_file.put_line (fnd_file.LOG,
                                    'ERROR IN CURSOR SELECTS -> CHECK '
                                 || CHR (9)
                                 || lc_error_message_server
                                );
   END prt_audit_error_log_data;
-----------------------------------
   PROCEDURE get_new_cust_site_dtls
/***********************************************************************
Name    : GET_NEW_CUST_SITE_DTLS
Purpose   : Get Customer Bill To Sites details
C and I Number  : < >
Arguments : NA
  p_old_org_id     IN   NUMBER   -- Old Operating Unit Id
  p_new_org_id     IN   NUMBER   -- New Operating Unit Id
  p_site_use_code  IN   VARCHAR2 -- site use code
  p_orig_sys_ref   IN   VARCHAR2 -- ORIG_SYSTEM_REFERENCE
  p_customer_id    OUT  NUMBER   -- CUST_ACCOUNT_ID(HZ_CUST_ACCOUNTS)
  p_site_use_id    OUT  NUMBER   -- SITE_USE_ID (HZ_CUST_SITE_USES_ALL)
  p_address_id     OUT  NUMBER   -- CUST_ACCT_SITE_ID (HZ_CUST_SITE_USES_ALL)
Script Location : This file is stored in $XXTKCOINV_TOP/sql
Notes   : Script to create Common utility package
------------------------------------------------------------------------
Change History
Date      Name        Ver Description
--------------------------------------------------------------------------
**************************************************************************/
   (
      p_old_org_id      IN       NUMBER,
      p_new_org_id      IN       NUMBER,
      p_orig_sys_ref    IN       VARCHAR2,
      p_site_use_code   IN       VARCHAR2,
      p_customer_id     OUT      NUMBER,
      p_site_use_id     OUT      NUMBER,
      p_address_id      OUT      NUMBER
   )
   IS
      --
      CURSOR c_locations
      IS
         SELECT location_id
           FROM hz_locations hl
          WHERE INSTR (hl.attribute1,
                       '<' || p_orig_sys_ref || ':' || p_old_org_id || '>'
                      ) > 0;
   --
   BEGIN
      --
      FOR r_locations IN c_locations
      LOOP
         --
         BEGIN
            SELECT hcas.cust_account_id, hcas.cust_acct_site_id,
                   hcsu.site_use_id
              INTO p_customer_id, p_address_id,
                   p_site_use_id
              FROM hz_party_sites hps,
                   hz_cust_acct_sites_all hcas,
                   hz_cust_site_uses_all hcsu
             WHERE hps.location_id = r_locations.location_id
               AND hcas.party_site_id = hps.party_site_id
               AND hcas.org_id = p_new_org_id
               AND hcsu.org_id = hcas.org_id
               AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
               AND hcsu.site_use_code = p_site_use_code;
            --
            EXIT;
         --
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               NULL;
         END;
      --
      END LOOP;
   --
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END get_new_cust_site_dtls;
---------------------------------------------------
   PROCEDURE get_new_cust_site_dtls
/***********************************************************************
Name    : GET_NEW_CUST_SITE_DTLS
Purpose   : Get Customer Bill To Sites details
C and I Number  : < >
Arguments : NA
  p_old_org_id     IN   NUMBER   -- Old Operating Unit Id
  p_new_org_id     IN   NUMBER   -- New Operating Unit Id
  p_orig_sys_ref   IN   VARCHAR2 -- ORIG_SYSTEM_REFERENCE
  p_customer_id    OUT  NUMBER   -- CUST_ACCOUNT_ID(HZ_CUST_ACCOUNTS)
  p_address_id     OUT  NUMBER   -- CUST_ACCT_SITE_ID (HZ_CUST_SITE_USES_ALL)
Script Location : This file is stored in $XXTKINV_TOP/sql
Notes   : Script to create Common utility package
------------------------------------------------------------------------
Change History
Date      Name        Ver Description
--------------------------------------------------------------------------
**************************************************************************/
   (
      p_old_org_id     IN       NUMBER,
      p_new_org_id     IN       NUMBER,
      p_orig_sys_ref   IN       VARCHAR2,
      p_customer_id    OUT      NUMBER,
      p_address_id     OUT      NUMBER
   )
   IS
      --
      CURSOR c_locations
      IS
         SELECT location_id
           FROM hz_locations hl
          WHERE INSTR (hl.attribute1,
                       '<' || p_orig_sys_ref || ':' || p_old_org_id || '>'
                      ) > 0;
   --
   BEGIN
      --
      FOR r_locations IN c_locations
      LOOP
         --
         BEGIN
            SELECT hcas.cust_account_id, hcas.cust_acct_site_id
              INTO p_customer_id, p_address_id
              FROM hz_party_sites hps, hz_cust_acct_sites_all hcas
             WHERE hps.location_id = r_locations.location_id
               AND hcas.party_site_id = hps.party_site_id
               AND hcas.org_id = p_new_org_id;
            --
            EXIT;
         --
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               NULL;
         END;
      --
      END LOOP;
   --
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END get_new_cust_site_dtls;
--
   PROCEDURE get_new_cust_number (p_old_new_cust_number IN OUT VARCHAR2)
   IS
   BEGIN
      --
      SELECT account_number
        INTO p_old_new_cust_number
        FROM hz_cust_accounts
       WHERE attribute2 = p_old_new_cust_number;
   --
   EXCEPTION
      WHEN OTHERS
      THEN
         p_old_new_cust_number := NULL;
   END get_new_cust_number;
---------------------------
   FUNCTION get_dataload_user_id
      RETURN NUMBER
   IS
      ln_user_id   NUMBER;
   BEGIN
      --
      SELECT user_id
        INTO ln_user_id
        FROM fnd_user
       WHERE user_name = 'DATALOAD';
      --
      RETURN ln_user_id;
   --
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         ln_user_id := 0;
         RETURN ln_user_id;
      WHEN OTHERS
      THEN
         ln_user_id := 0;
         RETURN ln_user_id;
   END get_dataload_user_id;
---------------------------
END XXST_INT_UTILITY_PKG;
/

select * from all_errors where name ='XXST_INT_UTILITY_PKG';