ORACLE APPS TECHNICAL: 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;

PO Conversion

CREATE OR REPLACE PROCEDURE PO_Int12(Errbuf OUT VARCHAR2,
                                      Retcode OUT VARCHAR2) AS
CURSOR c1 IS SELECT * FROM XX_TEMP_HEADERS;
CURSOR c2 IS SELECT * FROM XX_TEMP_LINES;
l_vendor_id   number(10);
l_item        varchar2(150);
l_flag        varchar2(4) default 'A';
l_msg         varchar2(200);
l_site_code     varchar2(100);
l_curr_code     varchar2(10);
l_org_id     number(6);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
COMMIT;
FOR x1 IN c1 LOOP
BEGIN
   SELECT vendor_id
   INTO   l_vendor_id
   FROM   po_vendors
   WHERE vendor_name = x1.VENDOR_NAME;
EXCEPTION
   WHEN OTHERS THEN
    l_flag        := 'E';
    l_msg       := 'Vendor id is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
--Vendor Site code Validation
begin
    select vendor_site_code
    into    l_site_code
    from    po_vendor_sites_all
    where   vendor_site_code = x1.vendor_site_code;
    EXCEPTION
       WHEN OTHERS THEN
        l_flag        := 'E';
        l_msg         := 'Vendor Site Code is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
    END;
--End of Site Code Validation
--Currency Code Validation
Begin
   select currency_code
   into   l_curr_code
   from   fnd_currencies
   where currency_code = x1.CURRENCY_CODE;
EXCEPTION
      WHEN OTHERS THEN
        l_flag        := 'E';
        l_msg         := 'Currency Code is Invalid';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
    END;
--End of te Currency Validation
--Operating Unit ID Validation
Begin
   select organization_id
   into   l_org_id
   from   hr_operating_units
   where organization_id = x1.org_id;
   EXCEPTION
      WHEN OTHERS THEN
        l_flag        := 'E';
        l_msg         := 'Invalid Organization ID';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
    END;
--End of the ORG ID Validation
IF l_flag != 'E' THEN
     INSERT INTO po_headers_interface
   (
   INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
   ,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,creation_date
,APPROVAL_STATUS
,APPROVED_DATE
,FREIGHT_TERMS
)
VALUES
(
   x1.INTERFACE_HEADER_ID
,x1.batch_id
,x1.action
,x1.org_id
,x1.document_type_code
,x1.CURRENCY_CODE
,x1.AGENT_NAME
,x1.VENDOR_NAME
,x1.VENDOR_SITE_CODE
,x1.SHIP_TO_LOCATION
,x1.BILL_TO_LOCATION
,SYSDATE-10
,x1.APPROVAL_STATUS
,SYSDATE
,x1.FREIGHT_TERMS
);
end if;
END LOOP;
FOR x2 IN c2 LOOP
l_flag := 'A';
--Item Validation
begin
select segment1
into   l_item
from   mtl_system_items_b
     where segment1        = x2.item
     AND    ORGANIZATION_ID = fnd_profile.value('ORG_ID');
exception
when others then
    l_flag        := 'E';
    l_msg       := 'Item is not valid Item';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
--End of the Item Validation
if l_flag != 'E' then
INSERT INTO PO_LINES_INTERFACE
(
INTERFACE_LINE_ID
,INTERFACE_HEADER_ID
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,item_id
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,SHIP_TO_LOCATION
,NEED_BY_DATE
,PROMISED_DATE
,list_price_per_unit
)
VALUES
(
   x2.INTERFACE_LINE_ID
,x2.INTERFACE_HEADER_ID
,x2.LINE_NUM
,x2.SHIPMENT_NUM
,x2.LINE_TYPE
,x2.ITEM
,x2.ITEM_DESCRIPTION
,x2.item_id
,x2.UOM_CODE
,x2.QUANTITY,
   X2.UNIT_PRICE,
X2.SHIP_TO_ORGANIZATION_CODE,
X2.SHIP_TO_LOCATION,
sysdate,
sysdate,
X2.LIST_PRICE_PER_UNIT);
END IF;
END LOOP;
COMMIT;
END PO_INT12;
/

Bills Of Material(BOM) Conversion

CREATE OR REPLACE PACKAGE BODY
                    APPS.ftxs_118c_bom_conv_pkg
AS
/*****************************************************************
*
******************************************************************
* $Header: $
******************************************************************
*
*   Filename: ftxs_118c_bom_conv_pkg.pkb
*
*   Purpose : This Package contains procedures and
*          to be used for Bom and Routing Conversion.
*
*****************************************************************/

--**********************************************************************************************
--This Procedure is used to validate the data in the FTXS_118C_BOM_HEADER_TEMP Staging Table ---
--**********************************************************************************************
PROCEDURE validate_headers
as
l_head_count   number;
v_org      varchar2(20);
l_err_msg     varchar2(1000);
l_item       number;
l_valid_head_count number;
l_org_id     number;
l_err_count number;

CURSOR bom_head_cur IS SELECT fbh.* , rowid row_id
                        FROM FTXS_118C_BOM_HEADER_TEMP fbh
                        WHERE status_flag = 'N';

CURSOR bom_head_err_cur IS SELECT fbh.* , rowid row_id
                        FROM FTXS_118C_BOM_HEADER_TEMP fbh
                        WHERE status_flag = 'E';

BEGIN
l_head_count := 0;
l_valid_head_count := 0;
l_err_count :=0;
FOR bom_head_rec in bom_head_cur
LOOP
l_head_count := l_head_count + 1;
l_item := null;
l_err_msg := NULL;
v_org := 0;
l_item := 0;

   BEGIN



         -- fetch the organization_code
        BEGIN
         SELECT organization_code INTO v_org FROM
         ftxs_118c_inv_org_map
           WHERE source_inv_org =bom_head_rec. organization_code;
        EXCEPTION
          WHEN no_data_found THEN
             l_err_msg := 'No organization_code has mapped for this ' || ' - '||bom_head_rec. organization_code;
           WHEN others THEN
             l_err_msg := SQLCODE || ' - ' || SQLERRM;
        END;
           --   Validate Organization_code

      BEGIN
         SELECT organization_id INTO l_org_id
         FROM mtl_parameters WHERE organization_code = v_org;
      EXCEPTION
        WHEN no_data_found THEN
          l_err_msg := l_err_msg || ' - '||bom_head_rec.organization_code||'Organization code does not exists ';


        WHEN others THEN
          l_err_msg := l_err_msg || ' - '||bom_head_rec.organization_code||SQLCODE||'-'||SQLERRM;

      END;


      -- Validate item Number

      Begin

        SELECT inventory_item_id INTO
                l_item
          FROM mtl_system_items_b
          WHERE organization_id = l_org_id
          AND segment1 = bom_head_rec.item_number
          AND   bom_enabled_flag = 'Y';

      EXCEPTION
        WHEN no_data_found THEN
          l_err_msg := l_err_msg || ' - '|| bom_head_rec.item_number||'Item Number does not exists ';


        WHEN others THEN
          l_err_msg := l_err_msg || ' - '||bom_head_rec.item_number||SQLCODE||'-'||SQLERRM;

      END;


     IF l_err_msg is NULL THEN
      l_valid_head_count := l_valid_head_count + 1;

      UPDATE FTXS_118C_BOM_HEADER_TEMP
      SET    status_flag = 'V'
            ,err_msg = null
      WHERE rowid = bom_head_rec.row_id;

     ELSE
      UPDATE FTXS_118C_BOM_HEADER_TEMP
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE rowid = bom_head_rec.row_id;
      l_err_count := l_err_count + 1;

      END IF;

   EXCEPTION



   WHEN others THEN

      UPDATE FTXS_118C_BOM_HEADER_TEMP
      SET    status_flag= 'E'
            ,err_msg = l_err_msg
      WHERE rowid = bom_head_rec.row_id;
      l_err_count := l_err_count + 1;
   END;

   IF MOD(l_valid_head_count,100) = 0 THEN

     COMMIT;

   END IF;

END LOOP;

COMMIT;
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_valid_head_count);
    Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
    Fnd_file.put_line(fnd_file.log,'**************************************************');

IF l_err_count >=1 then
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR bom_head_err_rec IN bom_head_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,bom_head_err_rec.err_msg);
    END LOOP;
Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;

END validate_headers;


--**********************************************************************************************
--This is Procedure is used to move the data from staging table to interface table
--*********************************************************************************************
PROCEDURE Insert_headers
AS
    l_userid   NUMBER:=fnd_global.user_id;
    l_count     NUMBER;
    v_date      DATE := SYSDATE;
    l_err_msg    varchar2(1000);
    l_err_count number;

CURSOR ins_headers_cur IS SELECT fbh.* ,rowid row_id
                             FROM FTXS_118C_BOM_HEADER_TEMP fbh
                             WHERE status_flag = 'V';

   CURSOR ins_headers_err_cur IS SELECT fbh.* ,rowid row_id
                             FROM FTXS_118C_BOM_HEADER_TEMP fbh
                             WHERE status_flag = 'E';

BEGIN

   l_count := 0;
   l_err_count :=0;



    FOR ins_headers_rec in ins_headers_cur
    LOOP
     BEGIN
       l_err_msg := null;

     INSERT INTO bom_bill_of_mtls_interface
                (organization_code
                ,assembly_type
                ,process_flag
                ,item_number
                ,transaction_type
                ,last_update_date
                ,last_updated_by
                ,creation_date
                ,created_by
                ,last_update_login
                )
        VALUES (ins_headers_rec.organization_code
               ,ins_headers_rec.assembly_type
               ,1
               ,ins_headers_rec.item_number
               ,'CREATE'
               ,v_date
               ,l_userid
               ,v_date
               ,l_userid
               ,l_userid
               );

               l_count := l_count + 1;

         IF mod(l_count,100) = 0 THEN

           COMMIT;

         END IF;

     EXCEPTION
       WHEN others THEN
          l_err_msg := SQLCODE || ' - '|| SQLERRM;

                UPDATE FTXS_118C_BOM_HEADER_TEMP
                  SET    status_flag= 'E'
                         ,err_msg = l_err_msg
                  WHERE rowid = ins_headers_rec.row_id;
               l_err_count := l_err_count + 1;

     END;



    END LOOP;
    COMMIT;
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_count);
    Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
    Fnd_file.put_line(fnd_file.log,'**************************************************');

IF l_err_count >=1 then
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR ins_headers_err_rec IN ins_headers_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,ins_headers_err_rec.err_msg);
    END LOOP;
    Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;

    END Insert_headers;


--**********************************************************************************************
--This Procedure is used to validate the data in the FTXS_118C_BOM_COMPS_TEMP Staging Table ---
--**********************************************************************************************
PROCEDURE validate_bom_comp
AS
l_comp_count   number;
v_org        varchar2(20);
l_err_msg     varchar2(1000);
l_item       number;
l_org_id     number;
l_valid_comp_count number;
v_num_comp_inv_id number;
l_err_count    number;

CURSOR bom_comp_cur IS SELECT fbc.*
                               ,rowid row_id
                        FROM FTXS_118C_BOM_COMPS_TEMP fbc
                        WHERE status_flag='N';
CURSOR bom_comp_err_cur IS SELECT fbc.*
                               ,rowid row_id
                        FROM FTXS_118C_BOM_COMPS_TEMP fbc
                        WHERE status_flag='E';

BEGIN
l_comp_count := 0;
l_valid_comp_count := 0;
l_err_count := 0;
FOR bom_comp_rec in bom_comp_cur
LOOP
l_comp_count := l_comp_count + 1;
l_err_msg := NULL;
v_org := NULL;
l_item := NULL;
l_org_id := NULL;
v_num_comp_inv_id:=NULL;
l_err_count := 0;

   BEGIN

         -- fetch the organization_code
        BEGIN
         SELECT organization_code INTO v_org FROM
         ftxs_118c_inv_org_map
           WHERE source_inv_org =bom_comp_rec. organization_code;
        EXCEPTION
          WHEN no_data_found THEN
             l_err_msg := 'No organization_code has mapped for this ' || ' - '||bom_comp_rec. organization_code;
           WHEN others THEN
             l_err_msg := bom_comp_rec. organization_code || ' - ' || SQLCODE || ' - ' || SQLERRM;
        END;

      --   Validate Organization_code
      BEGIN

         SELECT organization_id INTO l_org_id
         FROM mtl_parameters WHERE organization_code = v_org;

      EXCEPTION
        WHEN no_data_found THEN
          l_err_msg := l_err_msg || ' - ' ||bom_comp_rec.organization_code|| ' - '||'Organization code does not exists ';


        WHEN others THEN
          l_err_msg := l_err_msg || ' - ' ||bom_comp_rec.organization_code|| ' - ' ||SQLCODE||'-'||SQLERRM;

      END;


      -- Validate Assembly item Number ---------

      BEGIN

        SELECT inventory_item_id INTO
                l_item
          FROM mtl_system_items_b
          WHERE organization_id = l_org_id
          AND   segment1 = bom_comp_rec.item_number
          AND   bom_enabled_flag = 'Y';

      EXCEPTION
        WHEN no_data_found THEN
          l_err_msg := l_err_msg || ' - ' ||bom_comp_rec.item_number||'Item Number does not exists ';


        WHEN others THEN
          l_err_msg := l_err_msg|| ' - ' ||bom_comp_rec.item_number|| ' - '||SQLCODE||'-'||SQLERRM;

      END;


      --validate component item number----------------------------

     BEGIN
         SELECT inventory_item_id
         INTO v_num_comp_inv_id
         FROM mtl_system_items_b
         WHERE segment1 = bom_comp_rec.component_item_number
         AND organization_id = l_org_id
         AND bom_enabled_flag = 'Y';

     EXCEPTION
       WHEN no_data_found THEN
          l_err_msg := l_err_msg|| ' - ' ||bom_comp_rec.component_item_number||'Error in getting the inventory item id for the component item Number ';


        WHEN others THEN
          l_err_msg := l_err_msg|| ' - ' ||bom_comp_rec.organization_code||SQLCODE||'-'||SQLERRM;

      END;

    IF l_err_msg IS NULL THEN
        l_valid_comp_count := l_valid_comp_count + 1;

      UPDATE FTXS_118C_BOM_COMPS_TEMP
      SET    status_flag = 'V'
            ,err_msg = null
      WHERE rowid = bom_comp_rec.row_id;
    ELSE
      UPDATE FTXS_118C_BOM_COMPS_TEMP
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE rowid = bom_comp_rec.row_id;
      l_err_count:=l_err_count + 1;


    END IF;

   EXCEPTION


   WHEN others THEN

      UPDATE FTXS_118C_BOM_COMPS_TEMP
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE rowid = bom_comp_rec.row_id;
      l_err_count :=l_err_count + 1;

   END;

   IF MOD(l_valid_comp_count,100) = 0 THEN

     COMMIT;

   END IF;

END LOOP;

COMMIT;
Fnd_file.put_line(fnd_file.log,'**************************************************');
Fnd_file.put_line(fnd_file.log,'Total no of records fetched :'||l_comp_count);
Fnd_file.put_line(fnd_file.log,'Total no of valid records     :'||l_valid_comp_count);
Fnd_file.put_line(fnd_file.log,'Total no of errored records   :'||l_err_count);
Fnd_file.put_line(fnd_file.log,'**************************************************');

IF l_err_count >=1 THEN
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR bom_comp_err_rec IN bom_comp_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,bom_comp_err_rec.err_msg);
    END LOOP;
    Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;

END validate_bom_comp;


--**********************************************************************************************
--This is Procedure is used to move the data from staging table to interface tables
--*********************************************************************************************
PROCEDURE Insert_bom_comp
as

l_userid   NUMBER:=fnd_global.user_id;
v_date      date := SYSDATE;
l_count     number;
l_err_msg    varchar2(1000);
l_err_count   number;

CURSOR ins_comp_cur IS SELECT fbc.* ,rowid row_id
                             FROM FTXS_118C_BOM_COMPS_TEMP fbc
                             WHERE status_flag = 'V';
CURSOR ins_comp_err_cur IS SELECT fbc.* ,rowid row_id
                             FROM FTXS_118C_BOM_COMPS_TEMP fbc
                             WHERE status_flag = 'E';

BEGIN

   l_count := 0;
   l_err_count := NULL;


    FOR ins_comp_rec IN ins_comp_cur
    LOOP

    BEGIN
     l_err_msg := NULL;

    INSERT INTO bom_inventory_comps_interface
                ( organization_code
                 ,assembly_item_number
                 ,operation_seq_num
                 ,component_item_number
                 ,component_quantity
                 ,wip_supply_type
                 ,supply_subinventory
                 ,process_flag
                 ,transaction_type
                 ,last_update_date
                 ,last_updated_by
                 ,creation_date
                 ,created_by
                 ,last_update_login
                )
        VALUES (ins_comp_rec.organization_code
               ,ins_comp_rec.item_number
               ,ins_comp_rec.operation_seq_num
               ,ins_comp_rec.component_item_number
               ,ins_comp_rec.component_quantity
               ,ins_comp_rec.wip_supply_type
              ,ins_comp_rec.supply_subinventory
               ,1
               ,'CREATE'
               ,v_date
               ,l_userid
               ,v_date
               ,l_userid
               ,l_userid
               );

               l_count := l_count + 1;


       IF MOD(l_count,100) = 0 THEN

         COMMIT;

       END IF;
    EXCEPTION
      WHEN others THEN
        l_err_msg := l_err_msg || ' - ' ||SQLCODE|| ' - ' ||SQLERRM;
      UPDATE FTXS_118C_BOM_COMPS_TEMP
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE rowid = ins_comp_rec.row_id;
      l_err_count := l_err_count +1;

    END;
    End LOOP;
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_count);
    Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
    Fnd_file.put_line(fnd_file.log,'**************************************************');

IF l_err_count >=1 then
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR ins_comp_err_rec IN ins_comp_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,ins_comp_err_rec.err_msg);
    END LOOP;
    Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;

END Insert_bom_comp;



--**********************************************************************************************
--This Procedure is used to validate the data in the FTXS_118C_ROUTING_HEADER_TEMP Staging Table ---
--**********************************************************************************************
PROCEDURE validate_rout_headers
AS
l_rout_count   number;
V_org         varchar2(10);
l_err_msg     varchar2(1000);
l_item         number;
l_valid_rout_count number;
l_err_count    number;
l_org_id      number;

CURSOR bom_rout_head_cur IS SELECT frh.*
                              ,rowid row_id
                        FROM FTXS_118C_ROUTING_HEADER_TEMP frh
                        WHERE status_flag='N';

CURSOR bom_rout_head_err_cur IS SELECT frh.*
                              ,rowid row_id
                        FROM FTXS_118C_ROUTING_HEADER_TEMP frh
                       WHERE status_flag='E';

BEGIN
l_rout_count := 0;
l_valid_rout_count := 0;
l_err_count := 0;
FOR bom_rout_head_rec IN bom_rout_head_cur
LOOP
l_rout_count := l_rout_count + 1;
l_err_msg := NULL;
v_org := NULL;
l_item := NULL;
l_org_id := NULL;

   BEGIN

              -- fetch the organization_code
        BEGIN
         SELECT organization_code INTO v_org FROM
         ftxs_118c_inv_org_map
           WHERE source_inv_org =bom_rout_head_rec.organization_code;
        EXCEPTION
          WHEN no_data_found THEN
             l_err_msg := 'No organization_code has mapped for this ' || ' - '||bom_rout_head_rec.organization_code;
           WHEN others THEN
             l_err_msg := SQLCODE || ' - ' || SQLERRM;
        END;
           --   Validate Organization_code

      BEGIN

         SELECT organization_id INTO l_org_id
         FROM mtl_parameters where organization_code = v_org;
      Exception
        WHEN no_data_found THEN
          l_err_msg := l_err_msg || ' - '||bom_rout_head_rec.organization_code||'Organization code does not exists ';


        WHEN others THEN
          l_err_msg := l_err_msg || ' - '||bom_rout_head_rec.organization_code||SQLCODE||'-'||SQLERRM;

      END;






      -- Validate assembly item Number -------



      BEGIN

        SELECT inventory_item_id INTO
                l_item
          FROM mtl_system_items_b
          WHERE organization_id = l_org_id
          AND   segment1 = bom_rout_head_rec.item_number
          AND   bom_enabled_flag = 'Y';

      EXCEPTION
        WHEN no_data_found THEN
          l_err_msg := l_err_msg || ' - ' ||bom_rout_head_rec.item_number||'Item Number does not exists ';


        WHEN others THEN
          l_err_msg := l_err_msg|| ' - ' ||bom_rout_head_rec.item_number|| ' - '||SQLCODE||'-'||SQLERRM;

      END;


     IF l_err_msg IS NULL THEN

      l_valid_rout_count := l_valid_rout_count + 1;

      UPDATE   FTXS_118C_ROUTING_HEADER_TEMP
      SET   status_flag = 'V'
            ,err_msg = null
      WHERE rowid = bom_rout_head_rec.row_id;
     ELSE
      UPDATE   FTXS_118C_ROUTING_HEADER_TEMP
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE rowid = bom_rout_head_rec.row_id;
      l_err_count := l_err_count+1;

     END IF;


   EXCEPTION

   WHEN others THEN

      UPDATE   FTXS_118C_ROUTING_HEADER_TEMP
      SET   status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE rowid = bom_rout_head_rec.row_id;
      l_err_count :=l_err_count + 1;

   END;

   IF MOD(l_valid_rout_count,100) = 0 THEN

     COMMIT;

   END IF;

END LOOP;

COMMIT;

Fnd_file.put_line(fnd_file.log,'**************************************************');
Fnd_file.put_line(fnd_file.log,'Total no of records fetched   :'||l_rout_count);
Fnd_file.put_line(fnd_file.log,'Total no of valid records     :'||l_valid_rout_count);
Fnd_file.put_line(fnd_file.log,'Total no of errored records   :'||l_err_count);
Fnd_file.put_line(fnd_file.log,'**************************************************');

IF l_err_count >=1 THEN
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR bom_rout_head_err_rec IN bom_rout_head_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,bom_rout_head_err_rec.err_msg);
    END LOOP;
    Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;

END validate_rout_headers;

--**********************************************************************************************
--This is Procedure is used to move the data from staging table to interface tables
--*********************************************************************************************
PROCEDURE Insert_rout_headers
AS

l_userid   NUMBER:=0;
v_date      date := SYSDATE;
l_count     number;
l_err_msg      varchar2(1000);
l_err_count    number;

CURSOR ins_rout_header_cur IS SELECT frh.* ,rowid row_id
                             FROM FTXS_118C_ROUTING_HEADER_TEMP frh
                             WHERE status_flag = 'V';

CURSOR ins_rout_header_err_cur IS SELECT frh.* ,rowid row_id
                                FROM FTXS_118C_ROUTING_HEADER_TEMP frh
                                WHERE status_flag = 'E';

BEGIN

   l_count := 0;
   l_userid := fnd_global.user_id;
   l_err_count :=0;

    FOR ins_rout_header_rec IN ins_rout_header_cur
    LOOP

    BEGIN
    l_err_msg := NULL;

    INSERT INTO bom_op_routings_interface
                (organization_code
                ,assembly_item_number
                ,routing_type
                ,process_flag
                ,transaction_type
                ,last_update_date
                ,last_updated_by
                ,creation_date
                ,created_by
                ,last_update_login
                )
        VALUES (ins_rout_header_rec.organization_code
               ,ins_rout_header_rec.item_number
               ,ins_rout_header_rec.routing_type
               ,1
               ,'CREATE'
               ,v_date
               ,l_userid
               ,v_date
               ,l_userid
               ,l_userid
               );

               l_count := l_count + 1;
    EXCEPTION
       WHEN others THEN
          UPDATE   FTXS_118C_ROUTING_HEADER_TEMP
          SET    status_flag = 'E'
            ,err_msg = l_err_msg
          WHERE rowid = ins_rout_header_rec.row_id;
          l_err_count := l_err_count + 1;

    END;

    END LOOP;

    Fnd_file.put_line(fnd_file.log,'**************************************************');
    Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_count);
    Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
    Fnd_file.put_line(fnd_file.log,'**************************************************');

IF l_err_count >=1 THEN
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR ins_rout_header_err_rec IN ins_rout_header_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,ins_rout_header_err_rec.err_msg);
    END LOOP;
    Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;


    END Insert_rout_headers;


--**********************************************************************************************
--This Procedure is used to validate the data in the FTXS_118C_ROUTING_OPS_TEMP Staging Table ---
--**********************************************************************************************
PROCEDURE validate_routing_ops
AS
l_rout_ops_count   number;
V_org        varchar2(20);
l_err_msg     varchar2(1000);
l_item       number;
l_valid_rout_ops_count number;
v_num_dept_id   number;
l_org_id       number;
l_err_count   number;

CURSOR bom_routing_ops_cur IS SELECT fro.*
                              ,rowid row_id
                        FROM FTXS_118C_ROUTING_OPS_TEMP fro
                        WHERE status_flag='N';

CURSOR bom_routing_ops_err_cur IS SELECT fro.*
                              ,rowid row_id
                        FROM FTXS_118C_ROUTING_OPS_TEMP fro
                        WHERE status_flag='E';

BEGIN
l_rout_ops_count := 0;
l_valid_rout_ops_count := 0;
l_err_count := 0;
FOR bom_routing_ops_rec in bom_routing_ops_cur
LOOP
l_rout_ops_count :=l_rout_ops_count + 1;
l_err_msg := NULL;
v_org := NULL;
l_item := NULL;
l_org_id := NULL;
v_num_dept_id:=NULL;

   BEGIN

                 -- fetch the organization_code
        BEGIN
         SELECT organization_code INTO v_org FROM
         ftxs_118c_inv_org_map
           WHERE source_inv_org =bom_routing_ops_rec.organization_code;
        EXCEPTION
          WHEN no_data_found THEN
             l_err_msg := 'No organization_code has mapped for this ' || ' - '||bom_routing_ops_rec.organization_code;
           WHEN others THEN
             l_err_msg := SQLCODE || ' - ' || SQLERRM;
        END;
           --   Validate Organization_code

      BEGIN
         SELECT organization_id INTO l_org_id
         FROM mtl_parameters WHERE organization_code = v_org;
      Exception
        WHEN no_data_found THEN
          l_err_msg := l_err_msg || ' - '||bom_routing_ops_rec.organization_code||'Organization code does not exists ';


        WHEN others THEN
          l_err_msg := l_err_msg || ' - '||bom_routing_ops_rec.organization_code||SQLCODE||'-'||SQLERRM;

      END;



      -- Validate assembly item Number ----------------------------------------------


      BEGIN

        SELECT inventory_item_id INTO
                l_item
          FROM mtl_system_items_b
          WHERE organization_id = l_org_id
          AND   segment1 = bom_routing_ops_rec.item_number
          AND   bom_enabled_flag = 'Y';

      Exception
        WHEN no_data_found THEN
          l_err_msg := l_err_msg || ' - ' ||bom_routing_ops_rec.item_number||'Item Number does not exists ';


        WHEN others THEN
          l_err_msg := l_err_msg|| ' - ' ||bom_routing_ops_rec.item_number|| ' - '||SQLCODE||'-'||SQLERRM;

      END;



      ---validate department code----------------------------------------

      BEGIN

        SELECT department_id INTO
                v_num_dept_id
          FROM bom_departments
          WHERE department_code=bom_routing_ops_rec.department_code
          AND organization_id = l_org_id;


      EXCEPTION
        WHEN no_data_found THEN
          l_err_msg := bom_routing_ops_rec.department_code||'department code does not exists ';


        WHEN others THEN
          l_err_msg :=bom_routing_ops_rec.department_code||SQLCODE||'-'||SQLERRM;

      END;


    IF l_err_msg IS NULL THEN
      l_valid_rout_ops_count := l_valid_rout_ops_count + 1;

      UPDATE   FTXS_118C_ROUTING_OPS_TEMP
      SET    status_flag = 'V'
            ,err_msg = NULL
      WHERE rowid = bom_routing_ops_rec.row_id;
    ELSE
             UPDATE   FTXS_118C_ROUTING_OPS_TEMP
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE rowid = bom_routing_ops_rec.row_id;
      l_err_count := l_err_count +1;
    END IF;

   EXCEPTION

   WHEN others THEN

      UPDATE FTXS_118C_ROUTING_OPS_TEMP
      SET    status_flag = 'E'
            ,err_msg = l_err_msg
      WHERE rowid = bom_routing_ops_rec.row_id;
      l_err_count := l_err_count + 1;

   End;

   IF MOD(l_valid_rout_ops_count,100) = 0 THEN

     COMMIT;

   END IF;

END LOOP;

COMMIT;

Fnd_file.put_line(fnd_file.log,'**************************************************');
Fnd_file.put_line(fnd_file.log,'Total no of records fetched   :'||l_rout_ops_count);
Fnd_file.put_line(fnd_file.log,'Total no of valid records     :'||l_valid_rout_ops_count);
Fnd_file.put_line(fnd_file.log,'Total no of errored records   :'||l_err_count);
Fnd_file.put_line(fnd_file.log,'**************************************************');

IF l_err_count >=1 then
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR bom_routing_ops_err_rec IN bom_routing_ops_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,bom_routing_ops_err_rec.err_msg);
    END LOOP;
    Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;

END validate_routing_ops;


--**********************************************************************************************
--This is Procedure is used to move the data from staging table to interface tables
--*********************************************************************************************
PROCEDURE Insert_routing_ops
as

l_userid   NUMBER:=0;
v_date      date := sysdate;
l_count     number;
l_err_msg   varchar2(1000);
l_err_count number;

CURSOR ins_routing_ops_cur IS SELECT fro.* ,rowid row_id
                             FROM FTXS_118C_ROUTING_OPS_TEMP fro
                             WHERE status_flag = 'V';
CURSOR ins_routing_ops_err_cur IS SELECT fro.* ,rowid row_id
                             FROM FTXS_118C_ROUTING_OPS_TEMP fro
                             WHERE status_flag = 'E';

BEGIN

   l_count := 0;
   l_userid := fnd_global.user_id;
   l_err_count := 0;

    FOR ins_routing_ops_rec IN ins_routing_ops_cur
    LOOP

    BEGIN
    l_err_msg := NULL;

    INSERT INTO bom_op_sequences_interface
                (organization_code
                ,assembly_item_number
                ,operation_seq_num
                ,department_code
                ,effectivity_date
                ,process_flag
                ,transaction_type
                ,last_update_date
                ,last_updated_by
                ,creation_date
                ,created_by
                ,last_update_login
                )
        VALUES (ins_routing_ops_rec.organization_code
               ,ins_routing_ops_rec.item_number
               ,ins_routing_ops_rec.operation_seq_num
               ,ins_routing_ops_rec.department_code
               ,ins_routing_ops_rec.effectivity_date
               ,1
               ,'CREATE'
               ,v_date
               ,l_userid
               ,v_date
               ,l_userid
               ,l_userid
               );

               l_count := l_count + 1;

    EXCEPTION
      WHEN others THEN
                UPDATE FTXS_118C_ROUTING_OPS_TEMP
               SET    status_flag = 'E'
                     ,err_msg = l_err_msg
              WHERE    rowid = ins_routing_ops_rec.row_id;
              l_err_count:=l_err_count+1;

    END;
    END LOOP;

   Fnd_file.put_line(fnd_file.log,'**************************************************');
    Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_count);
    Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
    Fnd_file.put_line(fnd_file.log,'**************************************************');

IF l_err_count >=1 THEN
    Fnd_file.put_line(fnd_file.log,'**************************************************');
    FOR ins_routing_ops_err_rec IN ins_routing_ops_err_cur
    LOOP
      Fnd_file.put_line(fnd_file.log,ins_routing_ops_err_rec.err_msg);
    END LOOP;
    Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;

    END Insert_routing_ops;






PROCEDURE main_procedure(errbuf OUT varchar2
                         ,retcode OUT number) is
BEGIN


       validate_headers;

       Insert_headers;

       validate_bom_comp;

       Insert_bom_comp;

        validate_rout_headers;

        Insert_rout_headers;

        validate_routing_ops;

        Insert_routing_ops;

EXCEPTION
    WHEN others THEN
    fnd_file.put_line( fnd_file.log,' error occoured while inserting data into interface table '||SQLCODE||SQLERRM);



END main_procedure;

END;
/

Sales order headers and lines information queries

Query to retrive the header information of the Sales Order Form is:
SELECT ooha.header_id, ooha.order_number, ott.NAME "ORDER TYPE",
hp.party_name "CUSTOMER", hca.account_number "CUSTOMER NUMBER",
ooha.ordered_date "DATE ORDERED", qh.NAME "PRICE LIST",
ooha.transactional_curr_code "CURRENCY",
ooha.cust_po_number "CUSTOMER PO",
ooha.freight_carrier_code "SHIPPING METHOD",
ooha.flow_status_code "STATUS", rtt.NAME "PAYMENT TERMS",
mp.organization_code "WARE HOUSE", ol.meaning "FREIGHT TERMS",
ol1.meaning "SHIPMENT PRIORITY", al.meaning "FOB",
rsa.NAME "SALESPERSON",
hcsua.LOCATION
','
hl.address2
','
hl.city
','
hl.state
','
hl.postal_code
','
hl.county "BILL TO LOCATION",
hcsua1.LOCATION
','
hl1.address2
','
hl1.city
','
hl1.state
','
hl1.postal_code
','
hl1.county "SHIP TO LOCATION"
FROM oe_order_headers_all ooha,
oe_transaction_types_tl ott,
qp_list_headers qh,
ra_terms_tl rtt,
mtl_parameters mp,
ra_salesreps_all rsa,
hz_cust_accounts hca,
hz_parties hp,
hz_parties hp1,
hz_locations hl,
hz_locations hl1,
hz_cust_acct_sites_all hcasa,
hz_cust_acct_sites_all hcasa1,
hz_cust_site_uses_all hcsua,
hz_cust_site_uses_all hcsua1,
hz_party_sites hps,
hz_party_sites hps1,
oe_lookups ol,
oe_lookups ol1,
ar_lookups al
WHERE 1 = 1
AND ooha.order_number = 10265
AND ooha.sold_to_org_id = hca.cust_account_id
AND ooha.order_type_id = ott.transaction_type_id
AND ott.LANGUAGE = USERENV ('LANG')
AND rtt.LANGUAGE = USERENV ('LANG')
AND rtt.term_id = ooha.payment_term_id
AND qh.list_header_id = ooha.price_list_id
AND mp.organization_id = ooha.ship_from_org_id
AND ooha.salesrep_id = rsa.salesrep_id
AND hca.party_id = hp.party_id
AND hca.party_id = hp1.party_id
AND ooha.invoice_to_org_id = hcsua.site_use_id(+)
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id(+)
AND hcasa.party_site_id = hps.party_site_id(+)
AND hl.location_id(+) = hps.location_id
AND ooha.ship_to_org_id = hcsua1.site_use_id(+)
AND hcsua1.cust_acct_site_id = hcasa1.cust_acct_site_id(+)
AND hcasa1.party_site_id = hps1.party_site_id(+)
AND hl1.location_id(+) = hps1.location_id
AND ooha.freight_terms_code = ol.lookup_code
AND ooha.shipment_priority_code = ol1.lookup_code
AND al.lookup_code = ooha.fob_point_code;
Query to retrieve the line information of the Sales Order Form is:
SELECT oola.line_number "LINE NUMBER", oola.ordered_item "ORDERED ITEM",
oola.ordered_quantity "QTY", oola.order_quantity_uom "UOM",
oola.unit_selling_price "UNIT SELLING PRICE",
oola.cancelled_quantity "QTY CANCELLED",
oola.shipped_quantity "QTY SHIPPED", oola.tax_code "TAX CODE",
ott.NAME "LINE TYPE",
DECODE (opa.line_id,
NULL, DECODE (opa.credit_or_charge_flag,
'C', (-1) * opa.operand,
opa.operand
),
DECODE (opa.credit_or_charge_flag,
'C', DECODE (opa.arithmetic_operator,
'LUMPSUM', (-1) * (opa.operand),
(-1)
* ( oola.ordered_quantity
* opa.adjusted_amount
)
),
DECODE (opa.arithmetic_operator,
'LUMPSUM', opa.operand,
(oola.ordered_quantity * opa.adjusted_amount
)
)
)
) "LINE_CHARGES",
ol.meaning "CALCULATE PRICE FLAG", oola.pricing_quantity,
oola.unit_selling_price, oola.unit_list_price, oola.tax_value,
(oola.shipped_quantity) * (oola.unit_selling_price) "LINE TOTAL"
((oola.shipped_quantity) * (oola.unit_selling_price)
)
+ (DECODE (opa.line_id,
NULL, DECODE (opa.credit_or_charge_flag,
'C', (-1) * opa.operand,
opa.operand
),
DECODE (opa.credit_or_charge_flag,
'C', DECODE (opa.arithmetic_operator,
'LUMPSUM', (-1) * (opa.operand),
(-1)
* ( oola.ordered_quantity
* opa.adjusted_amount
)
),
DECODE (opa.arithmetic_operator,
'LUMPSUM', opa.operand,
(oola.ordered_quantity * opa.adjusted_amount
)
)
)
)
) "ORDER TOTAL"
FROM oe_order_lines_all oola,
oe_transaction_types_tl ott,
oe_price_adjustments opa,
oe_order_headers_all ooha,
oe_lookups ol
WHERE 1 = 1
AND oola.line_type_id = ott.transaction_type_id
AND opa.header_id = ooha.header_id
AND opa.line_id = oola.line_id(+)
AND opa.list_line_type_code = 'FREIGHT_CHARGE'
AND opa.applied_flag = 'Y'
AND ott.LANGUAGE = USERENV ('LANG')
AND oola.header_id = 1547
AND ol.lookup_type = 'CALCULATE_PRICE_FLAG'
AND oola.calculate_price_flag = ol.lookup_code;

ORACLE APPS TECHNICAL

Thursday, 5 January 2012

Conversion

PO Conversion

Bills Of Material(BOM) Conversion

Sales order headers and lines information queries

No comments:

Post a Comment