Purchase order report Interface

Control File
--------------------------------
options (skip=1)
LOAD DATA
INFILE '*'
INSERT INTO table ITW_PO_STAGE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
trailing nullcols
( SNO              SEQUENCE(1,1)        ,
    po_type            ,
    VENDOR_NAME        ,
    VENDOR_SITE         ,
    SHIP_TO_ORG_CODE     ,
    SHIP_TO_LOCATION    ,
    BILL_TO_LOCATION    ,
    PO_NUMBER           ,
    PO_DATE                     ,
    CURRENCY_CODE       ,
    BUYER_NAME        ,
    DESCRIPTION        ,
    DIVISION            ,
    LOCATION            ,
    ISOFORMAT            ,
    NO_OF_ATTCHMENTS  ,
    POLINE_NUM        ,
    POLINE_TYPE       ,
    ITEM_NUMBER       ,
    ITEM_DESCRIPTION  ,
    ITEM_CATEGORY     ,
    UOM               ,
    UNIT_PRICE        ,
    QUANTITY          ,
    PAYMENT_TERM      ,
    FREIGHT           ,
    CARRIER           ,
    FOB               ,
    PO_CHARGE_ACCOUNT ,
    ERR_FLAG
)

CREATE PROCEDURE XX_ITW_POCOV_PROC(ERRBUF VARCHAR2,RETCODE NUMBER) AS

DECLARE
   CURSOR c1
   IS
  
      SELECT a.*, a.ROWID
        FROM itw_po_stage a
       WHERE po_number IN ('20812787')AND err_flag IS NULL;

   v_lookup_code     po_lookup_codes.lookup_code%TYPE;
   v_vendor_id       po_vendors.vendor_id%TYPE;
   v_vendor_name     po_vendors.vendor_name%TYPE;
   v_site_code       po_vendor_sites_all.vendor_site_code%TYPE;
   v_currency        fnd_currencies.currency_code%TYPE;
   v_item_id         mtl_system_items_b.inventory_item_id%TYPE;
   v_item_name       mtl_system_items_b.segment1%TYPE;
   v_uom             mtl_units_of_measure_tl.unit_of_measure%TYPE;
   v_bill_loc_code   hr_locations.location_code%TYPE;
   v_ship_loc_code   hr_locations.location_code%TYPE;
   v_item_category   VARCHAR2 (50);
   v_payment_term    ap_terms.NAME%TYPE;
   v_fob             fnd_lookup_values.meaning%TYPE;
   v_freight         fnd_lookup_values.meaning%TYPE;
   V_LINE_TYPE       PO_LINE_TYPES.LINE_TYPE%TYPE;
   V_CCID GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
   V_FLEX_VALUE_ATTR1  FND_FLEX_VALUES.FLEX_VALUE%TYPE;
   V_FLEX_VALUE_ATTR2  FND_FLEX_VALUES.FLEX_VALUE%TYPE;
   V_FLEX_VALUE_ATTR3  FND_FLEX_VALUES.FLEX_VALUE%TYPE;
   err_f             VARCHAR2 (1);
   err_m             VARCHAR2 (1000);
BEGIN
   FOR r1 IN c1
   LOOP
      err_f := NULL;
      err_m := NULL;

      IF r1.po_type IS NOT NULL
      THEN                                   /*IF FOR LOOKUP_CODE NOT NULL */
         BEGIN
            SELECT lookup_code
              INTO v_lookup_code
              FROM po_lookup_codes
             WHERE lookup_code = r1.po_type AND lookup_type = 'PO TYPE';

            DBMS_OUTPUT.put_line ('VALID PO_TYPE');
         EXCEPTION                   /*EXCEPTION FOR LOOKUP_CODE VALIDATION */
            WHEN NO_DATA_FOUND
            THEN
               err_f := 'Y';
               err_m := 'INVALID PO TYPE';

               UPDATE itw_po_stage
                  SET err_flag = err_f,
                      err_msg = err_m
                WHERE ROWID = r1.ROWID;
            WHEN OTHERS
            THEN
               err_f := 'Y';
               err_m := err_m || SQLCODE || '-po_type-' || SUBSTR (SQLERRM, 10, 25);

               UPDATE itw_po_stage
                  SET err_flag = err_f,
                      err_msg = err_m
                WHERE ROWID = r1.ROWID;
         END;
      ELSE
         err_f := 'Y';
         err_m := 'PO TYPE IS NULL';

         UPDATE itw_po_stage
            SET err_flag = err_f,
                err_msg = err_m
          WHERE ROWID = r1.ROWID;
      END IF;                             /*END IF FOR LOOKUP_CODE NOT NULL */

      BEGIN
         IF r1.vendor_name IS NOT NULL/*************VENDOR VALIDATION**************************/
         THEN
            BEGIN
               SELECT vendor_id, vendor_name
                 INTO v_vendor_id, v_vendor_name
                 FROM po_vendors
                WHERE vendor_name = r1.vendor_name;

               BEGIN/*************VENDOR SITE VALIDATION**************************/
                  IF r1.vendor_site IS NOT NULL
                  THEN
                     BEGIN
                        SELECT vendor_site_code
                          INTO v_site_code
                          FROM po_vendor_sites_all
                         WHERE vendor_id = v_vendor_id
                           AND vendor_site_code = r1.vendor_site
                           AND org_id = 81;
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           err_f := 'Y';
                           err_m := 'INVALID VENDOR_SITE';

                           UPDATE itw_po_stage
                              SET err_flag = err_f,
                                  err_msg = err_m
                            WHERE ROWID = r1.ROWID;
                        WHEN OTHERS
                        THEN
                           err_f := 'Y';
                           err_m :=
                                 err_m
                              || SQLCODE
                              || '-vendor_name-'
                              || SUBSTR (SQLERRM, 10, 25);

                           UPDATE itw_po_stage
                              SET err_flag = err_f,
                                  err_msg = err_m
                            WHERE ROWID = r1.ROWID;
                     END;
                  ELSE
                     err_f := 'Y';
                     err_m := err_m || 'NULL AT VENDOR_SITE';

                     UPDATE itw_po_stage
                        SET err_flag = err_f,
                            err_msg = err_m
                      WHERE ROWID = r1.ROWID;
                  END IF;
               END;/*************END OF VENDOR SITE VALIDATION**************************/
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_f := 'Y';
                  err_m := 'INVALID VENDOR_NAME';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
               WHEN OTHERS
               THEN
                  err_f := 'Y';
                  err_m :=
                          err_m || SQLCODE || '-v site-' || SUBSTR (SQLERRM, 10, 25);

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
            END;
         ELSE
            err_f := 'Y';
            err_m := err_m || 'NULL AT VENDOR_NAME';

            UPDATE itw_po_stage
               SET err_flag = err_f,
                   err_msg = err_m
             WHERE ROWID = r1.ROWID;
         END IF;
      END;/*************END OF VENDOR VALIDATION**************************/

      BEGIN
         IF r1.currency_code IS NOT NULL/*************CURRENCY CODE VALIDATION**************************/
         THEN
            BEGIN
               SELECT currency_code
                 INTO v_currency
                 FROM fnd_currencies
                WHERE currency_code = r1.currency_code AND enabled_flag = 'Y';
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_f := 'Y';
                  err_m := err_m || '*INVALID CURRENCY CODE AT ';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
               WHEN OTHERS
               THEN
                  err_f := 'Y';
                  err_m := err_m || SQLCODE || '-curr code-' || SUBSTR (SQLERRM, 1, 20);

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
            END;
         ELSE
            err_f := 'Y';
            err_m := err_m || '*NULLS AT CURRENCY_CODE';

            UPDATE itw_po_stage
               SET err_flag = err_f,
                   err_msg = err_m
             WHERE ROWID = r1.ROWID;
         END IF;
      END;  /*************CURRENCY CODE VALIDATION**************************/                                                                

      BEGIN /*************SHIP TO LOCATION VALIDATION**************************/
         IF r1.ship_to_location IS NOT NULL
         THEN
            BEGIN
               SELECT hrl.location_code
                 INTO v_ship_loc_code
                 FROM hr_locations hrl
                WHERE hrl.location_code = r1.ship_to_location;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_f := 'Y';
                  err_m := err_m || '*INVALID SHIP TO LOC CODE';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
               WHEN OTHERS
               THEN
                  err_f := 'Y';
                  err_m :=
                          err_m || SQLCODE || '-ship loc-' || SUBSTR (SQLERRM, 1, 100);

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
            END;
         ELSE
            err_f := 'Y';
            err_m := err_m || '*NULLS AT SHIP TO LOC CODE';

            UPDATE itw_po_stage
               SET err_flag = err_f,
                   err_msg = err_m
             WHERE ROWID = r1.ROWID;
         END IF;
      END;/*************END OF SHIP TO LOCATION VALIDATION**************************/

      BEGIN/*************BILL TO LOCATION VALIDATION**************************/
         IF r1.bill_to_location IS NOT NULL
         THEN
            BEGIN
               SELECT hrl.location_code
                 INTO v_bill_loc_code
                 FROM hr_locations hrl
                WHERE hrl.location_code = r1.bill_to_location;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_f := 'Y';
                  err_m := err_m || '*INVALID BILL TO LOC CODE';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
               WHEN OTHERS
               THEN
                  err_f := 'Y';
                  err_m :=
                          err_m || SQLCODE || '-bill loc-' || SUBSTR (SQLERRM, 1, 100);

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
            END;
         ELSE
            err_f := 'Y';
            err_m := err_m || '*NULLS AT BILL TO LOC CODE';

            UPDATE itw_po_stage
               SET err_flag = err_f,
                   err_msg = err_m
             WHERE ROWID = r1.ROWID;
         END IF;
      END;/*************END OF BILL TO LOCATION VALIDATION**************************/

      BEGIN/*************ITEM VALIDATION**************************/
         IF r1.item_number IS NOT NULL
         THEN
            BEGIN
               SELECT msi.inventory_item_id, segment1
                 INTO v_item_id, v_item_name
                 FROM mtl_system_items_b msi
                WHERE msi.segment1 = r1.item_number
                  AND msi.organization_id = 102;

               BEGIN/*************ITEM UOM VALIDATION**************************/
                  IF r1.uom IS NOT NULL
                  THEN
                     BEGIN
                        SELECT mum.unit_of_measure
                          INTO v_uom
                          FROM mtl_system_items_b msi,
                               mtl_units_of_measure_tl mum
                         WHERE msi.segment1 = r1.item_number
                           AND msi.organization_id = 83
                           AND mum.unit_of_measure = r1.uom;
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           err_f := 'Y';
                           err_m := err_m || '*INVALID UOM FOR THE ITEM';

                           UPDATE itw_po_stage
                              SET err_flag = err_f,
                                  err_msg = err_m
                            WHERE ROWID = r1.ROWID;
                        WHEN OTHERS
                        THEN
                           err_f := 'Y';
                           err_m :=
                                 err_m
                              || SQLCODE
                              || '-uom-'
                              || SUBSTR (SQLERRM, 1, 100);

                           UPDATE itw_po_stage
                              SET err_flag = err_f,
                                  err_msg = err_m
                            WHERE ROWID = r1.ROWID;
                     END;
                  ELSE
                     err_f := 'Y';
                     err_m := err_m || '*NULLS AT UOM';

                     UPDATE itw_po_stage
                        SET err_flag = err_f,
                            err_msg = err_m
                      WHERE ROWID = r1.ROWID;
                  END IF;
               END;/*************END OF ITEM UOM VALIDATION**************************/

               BEGIN/*************ITEM CATEGORY VALIDATION**************************/
                  IF r1.item_category IS NOT NULL
                  THEN
                     BEGIN
                        SELECT mcb.segment1 || '.' || mcb.segment2
                                                                item_category
                          INTO v_item_category
                          FROM mtl_item_categories mic, mtl_categories_b mcb
                         WHERE mic.inventory_item_id = v_item_id
                           AND mic.organization_id = 83
                           AND MCB.CATEGORY_ID=MIC.CATEGORY_ID
                           AND mcb.segment1 || '.' || mcb.segment2 =
                                                              r1.item_category;
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           err_f := 'Y';
                           err_m :=
                               err_m || '*INVALID ITEM CATEGORY FOR THE ITEM';

                           UPDATE itw_po_stage
                              SET err_flag = err_f,
                                  err_msg = err_m
                            WHERE ROWID = r1.ROWID;
                        WHEN OTHERS
                        THEN
                           err_f := 'Y';
                           err_m :=
                                 err_m
                              || SQLCODE
                              || '-cat-'
                              || SUBSTR (SQLERRM, 1, 100);

                           UPDATE itw_po_stage
                              SET err_flag = err_f,
                                  err_msg = err_m
                            WHERE ROWID = r1.ROWID;
                     END;
                  ELSE
                     err_f := 'Y';
                     err_m := err_m || '*NULLS AT ITEM CATEGORY';

                     UPDATE itw_po_stage
                        SET err_flag = err_f,
                            err_msg = err_m
                      WHERE ROWID = r1.ROWID;
                  END IF;
               END;/*************END OF ITEM CATEGORY VALIDATION**************************/
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_f := 'Y';
                  err_m := err_m || '*INVALID ITEM';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
               WHEN OTHERS
               THEN
                  err_f := 'Y';
                  err_m :=
                          err_m || SQLCODE || '-item-' || SUBSTR (SQLERRM, 1, 100);

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
            END;
         ELSE
        
         IF R1.ITEM_DESCRIPTION IS NULL THEN
            err_f := 'Y';
            err_m := err_m || '*NULLS AT ITEM AND ITEM_DESCRPTION';

            UPDATE itw_po_stage
               SET err_flag = err_f,
                   err_msg = err_m
             WHERE ROWID = r1.ROWID;
            
          END IF;
         END IF;
      END;/*************END OF ITEM VALIDATION**************************/

      IF NVL (r1.quantity, 0) = 0/******************************QUANTITY VALIDATION******************************/
      THEN
         err_f := 'Y';
         err_m := err_m || 'QUANTITY IS NULL OR ZERO';

         UPDATE itw_po_stage
            SET err_flag = err_f,
                err_msg = err_m
          WHERE ROWID = r1.ROWID;
      END IF;/******************************END OF QUANTITY VALIDATION******************************/

--BEGIN/************OPERATING UNIT VALIDATION*********************/


--END;/************END OF OPERATING UNIT VALIDATION*********************/

      BEGIN/******************************PAYMENT TERM VALIDATION******************************/
         IF r1.payment_term IS NOT NULL
         THEN
            BEGIN
               SELECT NAME
                 INTO v_payment_term
                 FROM ap_terms
                WHERE NAME = r1.payment_term;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_m := err_m || 'INVALID PAYMENT TERM/';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
               WHEN OTHERS
               THEN
                  err_m :=
                          err_m || SQLCODE || '-pt-' || SUBSTR (SQLERRM, 1, 100);
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
            END;
         ELSE
            err_f := 'Y';
            err_m := err_m || '*NULL AT PAYMENT TERM';

            UPDATE itw_po_stage
               SET err_flag = err_f,
                   err_msg = err_m
             WHERE ROWID = r1.ROWID;
         END IF;
      END;/******************************END OF PAYMENT TERM VALIDATION******************************/

      BEGIN/******************************FOB VALIDATION******************************/
         IF r1.fob IS NOT NULL
         THEN
            BEGIN
               SELECT meaning
                 INTO v_fob
                 FROM fnd_lookup_values
                WHERE lookup_type = 'FOB'
                  AND meaning = r1.fob
                  AND view_application_id = 201;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_m := err_m || 'INVALID FOB';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
               WHEN OTHERS
               THEN
                 err_m :=
                          err_m || SQLCODE || '-fob-' || SUBSTR (SQLERRM, 1, 100);
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
            END;
         ELSE
            err_f := 'Y';
            err_m := err_m || '*NULL AT FOB';

            UPDATE itw_po_stage
               SET err_flag = err_f,
                   err_msg = err_m
             WHERE ROWID = r1.ROWID;
         END IF;
      END;/******************************FOB VALIDATION******************************/

      BEGIN /******************************freight VALIDATION******************************/
         IF r1.freight IS NOT NULL
         THEN
            BEGIN
               SELECT LOOKUP_CODE
                 INTO v_freight
                 FROM fnd_lookup_values
                WHERE lookup_type = 'FREIGHT TERMS'
                  AND meaning = r1.freight
                  AND view_application_id = 201;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_m := err_m || 'INVALID FREIGHT';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
               WHEN OTHERS
               THEN
                  err_m :=
                          err_m || SQLCODE || '-freight-' || SUBSTR (SQLERRM, 1, 100);
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
            END;
         ELSE
            err_f := 'Y';
            err_m := err_m || '*NULL AT FREIGHT';

            UPDATE itw_po_stage
               SET err_flag = err_f,
                   err_msg = err_m
             WHERE ROWID = r1.ROWID;
         END IF;
      END;/******************************END OF freight VALIDATION******************************/
     
      BEGIN /*******************POLINE_TYPE VALIDATION*******************************/
     
      IF R1.POLINE_TYPE IS NOT NULL THEN
     
      BEGIN
     
        SELECT LINE_TYPE
        INTO V_LINE_TYPE
        FROM PO_LINE_TYPES
        WHERE LINE_TYPE=R1.POLINE_TYPE;
       
        EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_m := err_m || 'INVALID PO_LINE_TYPE';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
               WHEN OTHERS
               THEN
                  err_m :=
                          err_m || SQLCODE || '-po line type-' || SUBSTR (SQLERRM, 1, 100);
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
     
      END;
      ELSE
     
       err_m := err_m || 'PO_LINE_TYPE IS NULL';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
     
      END IF;
      END;/*******************END OF POLINE_TYPE VALIDATION*******************************/
     
      BEGIN /***********CHARGE ACCOUT VALIDATION*************************/
     
      IF R1.PO_CHARGE_ACCOUNT IS NOT NULL THEN
     
        BEGIN
       
            SELECT CODE_COMBINATION_ID
            INTO V_CCID
            FROM GL_CODE_COMBINATIONS
            WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7=R1.PO_CHARGE_ACCOUNT;
           
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_m := err_m || 'INVALID PO CHARGE ACCOUNT';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
               WHEN OTHERS
               THEN
                 err_m :=
                          err_m || SQLCODE || '-charge acct-' || SUBSTR (SQLERRM, 1, 100);
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
     
       
        END;
     
      ELSE
     
        err_m := err_m || 'PO CHARGE ACCOUNT IS NULL';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
     
      END IF;
     
      END; /***********END OF CHARGE ACCOUT VALIDATION*************************/


     
     
      BEGIN --***********ATTRIBUTE1 VALIDATION*************************
     
        IF R1.ATTR1_DIVISION IS NOT NULL THEN
       
            BEGIN
       
            SELECT FFV.FLEX_VALUE
            INTO V_FLEX_VALUE_ATTR1
            FROM FND_DESCR_FLEX_COL_USAGE_VL FDF,FND_FLEX_VALUES FFV
            WHERE FDF.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADERS'
            AND FDF.APPLICATION_COLUMN_NAME='ATTRIBUTE1'
            AND FFV.FLEX_VALUE=R1.ATTR1_DIVISION
            AND FDF.FLEX_VALUE_SET_ID=FFV.FLEX_VALUE_SET_ID;
           
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_m := err_m || 'INVALID ATTR1_DIVISION VALUE';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
                  
               WHEN OTHERS
               THEN
                  err_m := err_m || SUBSTR (SQLERRM, 10, 30);
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
            END;
       
        ELSE
       
        err_m := err_m || 'ATTR1_DIVISION IS NULL';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
       
        END IF;
     
      END;--***********END OF ATTRIBUTE1 VALIDATION*************************
  
   BEGIN--***********ATTRIBUTE2 VALIDATION*************************
     
        IF R1.ATTR2_LOCATION IS NOT NULL THEN
       
            BEGIN
       
            SELECT FFV.FLEX_VALUE
            INTO V_FLEX_VALUE_ATTR2
            FROM FND_DESCR_FLEX_COL_USAGE_VL FDF,FND_FLEX_VALUES FFV
            WHERE FDF.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADERS'
            AND FDF.APPLICATION_COLUMN_NAME='ATTRIBUTE2'
            AND FFV.FLEX_VALUE=R1.ATTR2_LOCATION
            AND FDF.FLEX_VALUE_SET_ID=FFV.FLEX_VALUE_SET_ID;
           
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_m := err_m || 'INVALID ATTR2_LOCATION VALUE';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
                  
               WHEN OTHERS
               THEN
                  err_m := err_m || SUBSTR (SQLERRM, 10, 30);
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
            END;
       
        ELSE
       
        err_m := err_m || 'ATTR2_LOCATION IS NULL';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
       
        END IF;
     
      END;--***********END ATTRIBUTE2 VALIDATION*************************
     
     
      BEGIN--***********ATTRIBUTE3 VALIDATION*************************
     
        IF R1.ATTR3_ISO_FORMATS IS NOT NULL THEN
       
            BEGIN
       
            SELECT FFV.FLEX_VALUE
            INTO V_FLEX_VALUE_ATTR1
            FROM FND_DESCR_FLEX_COL_USAGE_VL FDF,FND_FLEX_VALUES FFV
            WHERE FDF.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADERS'
            AND FDF.APPLICATION_COLUMN_NAME='ATTRIBUTE3'
            AND FFV.FLEX_VALUE=R1.ATTR3_ISO_FORMATS
            AND FDF.FLEX_VALUE_SET_ID=FFV.FLEX_VALUE_SET_ID;
           
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  err_m := err_m || 'INVALID ATTR3_ISO_FORMATS VALUE';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
                  
               WHEN OTHERS
               THEN
                  err_m := err_m || SUBSTR (SQLERRM, 10, 30);
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
            END;
       
        ELSE
       
        err_m := err_m || 'ATTR3_ISO_FORMATS IS NULL';
                  err_f := 'Y';

                  UPDATE itw_po_stage
                     SET err_flag = err_f,
                         err_msg = err_m
                   WHERE ROWID = r1.ROWID;
       
        END IF;
     
      END; --***********END OF ATTRIBUTE3 VALIDATION*************************
  
  
     
     

  
   END LOOP;
  
  COMMIT;
 
   PROC_PO_INS;


  
END;