Customer Conversion

Customer Conversion

 CREATE OR REPLACE PACKAGE BODY APPS.XX_TP_DMIG_LEAD_PROFILES_PKG1
 AS
    error_message   VARCHAR2 (30000);
    PROCEDURE xx_tp_ar_cust_val_proc1;
    PROCEDURE xx_tp_ar_cust_process_proc1;
    /* Cursor to display the Status Report of all the records*/
    CURSOR cur_sum
    IS
         SELECT   COUNT (1) num,
                  err_status,
                  NVL (err_msg, 'Validated Records') err_msg
           FROM   XXCUS.xx_tp_dmig_lead_profiles_stg1 stg
       GROUP BY   err_status, err_msg;
    CURSOR cur_sum1
    IS
         SELECT   COUNT (1) num, err_status
           -- , NVL (err_msg, 'Processed Records') err_msg
           FROM   XXCUS.xx_tp_dmig_lead_profiles_stg1 stg
       GROUP BY   err_status;
    --                , err_msg;
    /* Replacing the Fnd_File.out */
    PROCEDURE PRINT (p_in IN VARCHAR2);
    PROCEDURE PRINT (p_in IN VARCHAR2)
    IS
    BEGIN
       fnd_file.put_line (fnd_file.output, p_in);
       DBMS_OUTPUT.put_line (p_in);
    END;
    PROCEDURE main (retbuf            OUT VARCHAR2,
                    retcode           OUT NUMBER,
                    process_flag   IN     VARCHAR2)
    IS
    BEGIN
       /*==============================================
        *   Purpose : Updating the err status to  V   in
                      XX_TP_DMIG_LEAD_PROFILES_STG once the records
              are validated.
        * =============================================*/
       IF process_flag = 'V'
       THEN
          --
          --
          fnd_file.put_line (fnd_file.LOG, ' Validation started ');
          xx_tp_ar_cust_val_proc1;
       /*=============================================
         *   Purpose : Updating the err status to S  in
                   XX_TP_DMIG_LEAD_PROFILES_STG  once the
               records are Processed
         * =============================================*/
       ELSIF process_flag = 'P'
       THEN
          fnd_file.put_line (fnd_file.LOG, ' PROCESSING DATA  started ');
          --
          -- Process the data
          --
          xx_tp_ar_cust_process_proc1;
          fnd_file.put_line (fnd_file.LOG, ' END PROCESSING DATA');
       ELSE
          --
          -- Validatiopn and Process
          --
          NULL;
       END IF;
    EXCEPTION
       WHEN OTHERS
       THEN
          fnd_file.put_line (fnd_file.LOG,
                             'Error in main due to :' || SQLERRM);
    END main;                                               -- End for the main
    /*=============================================
    * Procedure : XX_TP_AR_CUST_VAL_PROC
    * Purpose : This procedure validates the data in staging
                Table XX_TP_DMIG_LEAD_PROFILES_STG and updates the
                data err_status and err_msg with 'E'(Errored)
                or V(once successfully validated) with respective
                error message.
     * =============================================*/
    PROCEDURE xx_tp_ar_cust_val_proc1
    IS
       x_cntry_code    fnd_territories.territory_code%TYPE;
       x_site_code     ar_lookups.lookup_code%TYPE;
       x_stat_code     ar_lookups.lookup_code%TYPE;
       x_credit_rate   ar_lookups.lookup_code%TYPE;
       CURSOR cur_validate
       IS
          SELECT   stg.ROWID, stg.*
            FROM   XXCUS.xx_tp_dmig_lead_profiles_stg1 stg
           WHERE   NVL (err_status, 'X') <> 'S';
    BEGIN
       PRINT (' ********************************  ');
       PRINT (' Validation Stage for CUSTOMER ');
       PRINT (' ********************************  ');
       PRINT ('   ');
       PRINT ('ERROR DETAILS: ');
       PRINT ('************* ');
       PRINT ('   ');
       FOR rec_validate IN cur_validate
       LOOP
          error_message := NULL;
          rec_validate.err_status := NULL;
          rec_validate.err_status := 'V';
          /* =============================================
            *   Purpose : Verify wherther the Party name is
                             defined or not
           * =============================================*/
          IF rec_validate.original_name IS NULL
          THEN
             error_message := 'Party name is mandatory';
             rec_validate.err_status := 'E';
          END IF;
          /* =============================================
            *   Purpose : Verify whether the Country is
                        defined in Fnd_Territories or not.
           * =============================================*/
          BEGIN
             SELECT   territory_code
               INTO   x_cntry_code
               FROM   fnd_territories
              WHERE   territory_code = rec_validate.country_code;
          EXCEPTION
             WHEN NO_DATA_FOUND
             THEN
                error_message :=
                   error_message || ',' || 'Invalid territory code';
                rec_validate.err_status := 'E';
                PRINT (
                   rec_validate.first_name || ' :  Invalid territory code'
                );
             WHEN OTHERS
             THEN
                error_message :=
                      error_message
                   || ','
                   || 'Error while validating the territory code due to : '
                   || SQLERRM;
                rec_validate.err_status := 'E';
          END;
          /* =============================================
                * Purpose : Updating the Error Status and
                  Error Message in the Staging table.
           * =============================================*/
          UPDATE   XXCUS.XX_TP_DMIG_LEAD_PROFILES_STG1 x
             SET   err_msg = error_message,
                   err_status = rec_validate.err_status
           WHERE   x.ROWID = rec_validate.ROWID;
       END LOOP;
       COMMIT;
       PRINT ('   ');
       PRINT ('SUMMARY OF VALIDATION ');
       PRINT ('********************* ');
       PRINT ('   ');
       PRINT ('COUNT       STATUS      ERROR MESSAGE');
       PRINT ('----        ------      -------------');
       FOR rec_sum IN cur_sum
       LOOP
          PRINT(   RPAD (TO_CHAR (rec_sum.num), 12, ' ')
                || RPAD (rec_sum.err_status, 12, ' ')
                || rec_sum.err_msg);
       END LOOP;
       PRINT ('**** END OF THE REPORT *****');
    EXCEPTION
       WHEN OTHERS
       THEN
          fnd_file.put_line (
             fnd_file.output,
             'Error while validating the data due to: ' || SQLERRM
          );
    END xx_tp_ar_cust_val_proc1;                        /* End of validation */
    --
    -- Processing the Valid data
    /* =============================================
     * Procedure : XXCSC_AR_CUST_VAL_PROC
     * Purpose : This procedure processses the data into R12 base
                 Tables from staging table and updates the err_status
             to 'S' once it is processed into hz base tables.
             if any error occurs updates err_status and err_msg
             with 'E'(Errored) with respective error message.
      * =============================================*/
    PROCEDURE xx_tp_ar_cust_process_proc1
    IS
       p_cust_account_rec       hz_cust_account_v2pub.cust_account_rec_type;
       p_person_rec             hz_party_v2pub.person_rec_type;
       p_customer_profile_rec   hz_customer_profile_v2pub.customer_profile_rec_type;
       p_organization_rec       hz_party_v2pub.organization_rec_type;
       p_location_rec           hz_location_v2pub.location_rec_type;
       p_cust_site_use_rec      hz_cust_account_site_v2pub.cust_site_use_rec_type;
       p_cust_acct_site_rec     hz_cust_account_site_v2pub.cust_acct_site_rec_type;
       p_party_site_rec         hz_party_site_v2pub.party_site_rec_type;
       x_cust_account_id        NUMBER;
       x_account_number         VARCHAR2 (2000);
       x_party_id               NUMBER;
       x_party_number           VARCHAR2 (2000);
       x_profile_id             NUMBER;
       x_msg_count              NUMBER;
       x_msg_data               VARCHAR2 (2000);
       x_party_site_id          NUMBER;
       x_party_site_number      VARCHAR2 (2000);
       x_location_id            NUMBER;
       x_site_use_id            NUMBER;
       x_return_status          VARCHAR2 (2000);
       x_cust_acct_site_id      NUMBER;
       x_success_rec            NUMBER;
       x_err_count              NUMBER;
       CURSOR cur_customer (x_status VARCHAR2)
       IS
          SELECT   xcs.ROWID, xcs.*
            FROM   XXCUS.xx_tp_dmig_lead_profiles_stg1 xcs
           WHERE   err_status = x_status;
    BEGIN
       /*      PRINT ('               **********PROCESSING OF RECORDS**********                     ');
             fnd_file.put_line(fnd_file.output,'SUMMARY OF PROCESSED RECORDS');
             PRINT(RPAD(' Party ID',20,' ') || RPAD(' Cust Account Id',20,' ' )|| RPAD('Party Site Id',20,'
             PRINT(RPAD('-',20,'-') || RPAD('-',20,'-' )|| RPAD('-',20,'-'));
             PRINT( '  ');
       */
       FOR rec_cust IN cur_customer ('V')
       LOOP
          error_message := NULL;
          /* =============================================
               *Purpose : This API take the customer info'n from the staging
            table as input, and outputs the unique id's (party_id,cust_account_id,
            account_number) and pushes the whole   data into r12 hz tables.
              * =============================================*/
          p_cust_account_rec.account_name := rec_cust.original_name;
          p_cust_account_rec.created_by_module := 'TCA_V2_API';
          p_person_rec.person_first_name := rec_cust.first_name;
          hz_cust_account_v2pub.create_cust_account ('T',
                                                     p_cust_account_rec,
                                                     p_person_rec,
                                                     p_customer_profile_rec,
                                                     'F'                  --'T'
                                                        ,
                                                     x_cust_account_id,
                                                     x_account_number,
                                                     x_party_id,
                                                     x_party_number,
                                                     x_profile_id,
                                                     x_return_status,
                                                     x_msg_count,
                                                     x_msg_data);
          IF x_return_status <> 'S'
          THEN
             error_message := x_msg_data;
          END IF;
          /* =============================================
          *Purpose : This API take the Location info'n from the staging
       table as input, and outputs the unique id's (location_id)
       and pushes the whole   data into r12 hz tables.
         * =============================================*/
          IF x_return_status = 'S'
          THEN
             -- Initialization of the default values to create the  location
             p_location_rec.country := rec_cust.country_code;           ---'US'
             p_location_rec.address1 := rec_cust.address1;
             p_location_rec.city := rec_cust.city;
             p_location_rec.county := rec_cust.country_code;
             p_location_rec.postal_code := rec_cust.postal_code;
             ----TO_NUMBER (rec_cust.postal_code);
             p_location_rec.state := rec_cust.state;
             p_location_rec.created_by_module := 'TCA_V2_API';
             hz_location_v2pub.create_location ('T',
                                                p_location_rec,
                                                x_location_id,
                                                x_return_status,
                                                x_msg_count,
                                                x_msg_data);
             IF x_return_status <> 'S'
             THEN
                error_message := x_msg_data;
             END IF;
          END IF;
          /* =============================================
          *Purpose : This API take the Party Site info'n from the staging
       table as input, and outputs the party_site_id, party_site_number
       and pushes the whole data into r12 hz tables.
         * =============================================*/
          --
          IF x_return_status = 'S'
          THEN
             p_party_site_rec.party_id := x_party_id;      --rec_cust.party_id;
             p_party_site_rec.location_id := x_location_id;
             --rec_cust.location_id;
             p_party_site_rec.identifying_address_flag := 'Y';
             p_party_site_rec.created_by_module := 'TCA_V2_API';
             hz_party_site_v2pub.create_party_site ('T',
                                                    p_party_site_rec,
                                                    x_party_site_id,
                                                    x_party_site_number,
                                                    x_return_status,
                                                    x_msg_count,
                                                    x_msg_data);
             COMMIT;
             IF x_return_status <> 'S'
             THEN
                error_message := x_msg_data;
             END IF;
          END IF;
          /* =============================================
           *Purpose : This API take the Customer Account Site info'n
        from the staging table as input, and outputs the cust_acct_site_id
        and pushes the whole data into r12 hz tables.
          * =============================================*/
          IF x_return_status = 'S'
          THEN
             /*            PRINT(RPAD(x_party_id,20, ' ')||RPAD(x_cust_account_id,20, ' ') || RPAD(x_party_site
             */
             p_cust_acct_site_rec.cust_account_id := x_cust_account_id;
             p_cust_acct_site_rec.party_site_id := x_party_site_id;
             p_cust_acct_site_rec.LANGUAGE := rec_cust.LANGUAGE_code;
             p_cust_acct_site_rec.created_by_module := 'TCA_V2_API';
             hz_cust_account_site_v2pub.create_cust_acct_site (
                'T',
                p_cust_acct_site_rec,
                x_cust_acct_site_id,
                x_return_status,
                x_msg_count,
                x_msg_data
             );
             IF x_return_status <> 'S'
             THEN
                error_message := x_msg_data;
             END IF;
          END IF;
          /* =============================================
           *Purpose : This API take the Customer Site use info'n
        from the staging table as input, and outputs the site_use_id
        and pushes the whole data into r12 hz tables.
          * =============================================*/
          IF x_return_status = 'S'
          THEN
             p_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
             p_cust_site_use_rec.site_use_code := rec_cust.site_use_code;
             --rec_cust.SITE_USE_code;
             p_cust_site_use_rec.created_by_module := 'TCA_V2_API';
             hz_cust_account_site_v2pub.create_cust_site_use (
                'T',
                p_cust_site_use_rec,
                p_customer_profile_rec,
                '',
                '',
                x_site_use_id,
                x_return_status,
                x_msg_count,
                x_msg_data
             );
             IF x_return_status <> 'S'
             THEN
                error_message := x_msg_data;
             END IF;
          END IF;
          /* =============================================
          Purpose: Running Customer profile
          * =============================================*/
          /*    IF x_return_status = 'S'
                   THEN
                         p_customer_profile_rec.cust_account_id := x_cust_account_id;
                         p_customer_profile_rec.statement_cycle_id := rec_cust.statment_cycle_id;
                         p_customer_profile_rec.created_by_module := 'TCA_V2_API';
                          hz_customer_profile_v2pub.create_customer_profile (
                          p_customer_profile_rec    ,
                          p_create_profile_amt      => FND_API.G_TRUE,
                          x_cust_account_profile_id => l_cust_account_profile_id,
                          x_return_status           => x_return_status,
                          x_msg_count               => x_msg_count,
                          x_msg_data                => x_msg_data
                      IF x_return_status <> 'S'
                      THEN
                         error_message              := x_msg_data;
                      END IF;
                   END IF;
          */
          /* =============================================
            *Purpose : Updates the data into Staging table with
        respective id's whcih are returned by the API's. Also
        updates the ERR_STATUS and ERR_MSG respectively
           * =============================================*/
          UPDATE   XXCUS.xx_tp_dmig_lead_profiles_stg1 xcst
             SET   err_status = x_return_status,
                   err_msg = error_message,
                   party_id = x_party_id,
                   location_id = x_location_id,
                   cust_account_id = x_cust_account_id,
                   party_site_id = x_party_site_id,
                   cust_acct_site_id = x_cust_acct_site_id,
                   site_use_id = x_site_use_id,
                   request_id = APPS.FND_GLOBAL.CONC_REQUEST_ID,
                   created_by = APPS.FND_GLOBAL.USER_ID,
                   last_updated_by = APPS.FND_GLOBAL.USER_ID
           WHERE   ROWID = rec_cust.ROWID;
          COMMIT;
       END LOOP;
       -- ERROR REPORT
       PRINT ('  ');
       PRINT (RPAD ('*', 60, '*'));
       PRINT (
          RPAD (' ', 25, ' ') || 'SUMMARY OF RECORDS ' || RPAD (' ', 25, ' ')
       );
       PRINT (RPAD ('*', 60, '*'));
       PRINT ('  ');
       SELECT   COUNT (ORIGINAL_name)
         INTO   x_err_count
         FROM   XXCUS.xx_tp_dmig_lead_profiles_stg1
        WHERE   err_status = 'E';
       PRINT('TOTAL NUMBER OF RECORDS ERROERED OUT WHILE PROCESSING     :'
             || X_ERR_COUNT);
       SELECT   COUNT (1)
         INTO   x_err_count
         FROM   XXCUS.xx_tp_dmig_lead_profiles_stg1
        WHERE   err_status = 'S';
       PRINT('TOTAL NUMBER OF RECORDS INSERTED INTO HZ BASE TABLES      :'
             || X_ERR_COUNT);
       PRINT ('  ');
       PRINT (RPAD ('*', 60, '*'));
       PRINT (RPAD (' ', 28, ' ') || 'ERROR DATA' || RPAD (' ', 28, ' '));
       PRINT (RPAD ('*', 60, '*'));
       PRINT(   RPAD (' PARTY NAME', 30, ' ')
             || RPAD (' ', 8, ' ')
             || RPAD ('ERROR MESSAGE', 100, ' '));
       PRINT (
          RPAD ('-', 30, '-') || RPAD (' ', 8, ' ') || RPAD ('-', 100, '-')
       );
       FOR rec_cust IN cur_customer ('E')
       LOOP
          PRINT(RPAD (SUBSTR (rec_cust.ORIGINAL_name, 1, 40), 40, ' ')
                || RPAD (rec_cust.err_msg, 100, ' '));
       END LOOP;
       PRINT ('  ');
       PRINT (RPAD ('*', 60, '*'));
       PRINT(   RPAD (' ', 28, ' ')
             || 'PROCESSED RECORD DETAILS'
             || RPAD (' ', 28, ' '));
       PRINT (RPAD ('*', 60, '*'));
       PRINT ('  ');
       PRINT(   RPAD (' PARTY NAME', 40, ' ')
             || RPAD ('ACCOUNT NAME', 50, ' ')
             || RPAD ('PARTY SITE ID', 20, ' '));
       PRINT (
          RPAD ('-', 40, '-') || RPAD ('-', 50, '-') || RPAD ('-', 20, '-')
       );
       PRINT ('  ');
       FOR rec_cust IN cur_customer ('S')
       LOOP
          PRINT(   RPAD (rec_cust.ORIGINAL_name, 40, ' ')
                || RPAD (SUBSTR (rec_cust.FIRST_name, 1, 50), 50, ' ')
                || RPAD (rec_cust.party_site_id, 20, ' '));
       END LOOP;
       PRINT ('   ');
       PRINT (RPAD ('-', 60, '-'));
       PRINT (RPAD (' ', 28, ' ') || 'END REPORT' || RPAD (' ', 28, ' '));
       PRINT (RPAD ('-', 60, '-'));
       COMMIT;
       /* Report to display the Record Status*/
       --BEGIN
       /*      PRINT ('   ');
             PRINT ('   ');
             PRINT ('SUMMARY OF RECORDS ');
             PRINT ('******************* ');
       */
       /*      PRINT ('   ');
       /*      PRINT ('COUNT       STATUS     ');
             PRINT ('-----       -------      ');
            FOR rec_sum IN cur_sum1
             LOOP
             if(rec_sum.err_status='S')
             then
                PRINT ('TOTAL NUMBER OF RECORDS INSERTED INTO HZ BASE TABLES      :'||X_ERR_COUNT);
              else
                    PRINT ('TOTAL NUMBER OF RECORDS errored out      :'||X_ERR_COUNT);
           end if;
                PRINT (   RPAD (TO_CHAR (rec_sum.num)
                              , 12
                              , ' '
                               )
                       || RPAD (rec_sum.err_status
                              , 12
                              , ' '
                               ));
       --                || rec_sum.err_msg);*/
       BEGIN
          NULL;
       /*select count(*) into x_err_count from xxcsc_ar_cust_conv_stg where err_status='E';
       PRINT ('TOTAL NUMBER OF RECORDS ERROERED OUT WHILE PROCESSING     :'||X_ERR_COUNT);
       select count(*) into x_err_count from xxcsc_ar_cust_conv_stg where err_status='S';
       PRINT ('TOTAL NUMBER OF RECORDS INSERTED INTO HZ BASE TABLES      :'||X_ERR_COUNT);
       */
       EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
             PRINT('NO DATA FOUND  WHILE PRINTING THE COUNT AND STATUS OF RECORDS');
          WHEN OTHERS
          THEN
             PRINT ('ERROR WHILE PRINTING THE COUNT AND STATUS OF RECORDS');
       END;
    -- END LOOP;
    EXCEPTION
       WHEN OTHERS
       THEN
          fnd_file.put_line (
             fnd_file.LOG,
             'Error while processing records due to :' || SQLERRM
          );
    END xx_tp_ar_cust_process_proc1;
 END XX_TP_DMIG_LEAD_PROFILES_PKG1;