Item Conversion Update

CREATE OR REPLACE PACKAGE APPS.ZNINV_ITEM_CONV_PKG_UP AS
PROCEDURE ZNinv_item_validate_proc;
PROCEDURE ZNinv_item_insert_proc;
PROCEDURE ZNINV_ITEM_CONV_MAIN_PROC ( errbuf  OUT VARCHAR2,
                                      retcode OUT VARCHAR2         

                                    );

END ZNINV_ITEM_CONV_PKG_UP;
/




CREATE OR REPLACE PACKAGE BODY ZNINV_ITEM_CONV_PKG_UP AS

-- Global Variables
   g_user_id               fnd_user.user_id%TYPE ;
   g_login_id              NUMBER(15):=0;
   g_item_found            NUMBER:=0;
   g_item_processed        NUMBER:=0;
   g_item_rejected         NUMBER:=0;

/* -----------------------------------------------------------------------------------------------------------------
   Name        :  ZNinv_item_validate_proc
   Description :  This is the  procedure called by the main procedure to validate the records in the staging tables.
   Parameters  :  None
   Returns     :  None
 ------------------------------------------------------------------------------------------------------------------*/
   PROCEDURE ZNinv_item_validate_proc
   AS
  /*--------------------------------------------------------------------------------------------------
  This cursor will fetch all the records from the staging table ZNinv_mtl_system_items_b_stg
  ---------------------------------------------------------------------------------------------------*/
   CURSOR c_staging_item_create
   IS
--    SELECT   ROWID msirowid, xmsi.*
--    FROM zninv_mtl_system_items_b_stg xmsi
--    WHERE xmsi.process_status IS NULL;
       SELECT      BATCH_NAME
         ,ITEM_NUMBER
         ,DESCRIPTION
         ,TEMPLATE_NAME
         ,ATTRIBUTE_CATEGORY
         ,ATTRIBUTE1
         ,ATTRIBUTE2
         ,ATTRIBUTE3
         ,ORGANIZATION_CODE
        ,PRIMARY_UNIT_OF_MEASURE
         ,ITEM_TYPE
         ,INVENTORY_ITEM_FLAG
         ,STOCK_ENABLED_FLAG
         ,MTL_TRANSACTIONS_ENABLED_FLAG
         ,SO_TRANSACTIONS_FLAG
         ,CYCLE_COUNT_ENABLED_FLAG
         ,COSTING_ENABLED_FLAG
         ,INVENTORY_ASSET_FLAG
         ,DEFAULT_INCLUDE_IN_ROLLUP_FLAG
         ,PURCHASING_ITEM_FLAG
         ,PURCHASING_ENABLED_FLAG
         ,SERIAL_NUM_GENERATION
         ,LIST_PRICE_PER_UNIT
         ,COST_OF_SALES_ACCOUNT
         ,ENCUMBRANCE_ACCOUNT
         ,EXPENSE_ACCOUNT
         ,SALES_ACCOUNT
         ,ASSET_CATEGORY_CODE
         ,RECEIVING_ROUTING
         ,INVENTORY_PLANNING_CODE
         ,MIN_MINMAX_QUANTITY
         ,MAX_MINMAX_QUANTITY
         ,MINIMUM_ORDER_QUANTITY
         ,MAXIMUM_ORDER_QUANTITY
         ,SOURCE_TYPE
         ,SOURCE_ORGANIZATION
         ,SOURCE_SUBINVENTORY
         ,PLANNER_CODE
         ,CUSTOMER_ORDER_FLAG
         ,CUSTOMER_ORDER_ENABLED_FLAG
         ,SHIPPABLE_ITEM_FLAG
         ,INTERNAL_ORDER_FLAG
         ,INTERNAL_ORDER_ENABLED_FLAG
         ,RETURNABLE_FLAG
         ,INVOICEABLE_ITEM_FLAG
         ,INVOICE_ENABLED_FLAG
         ,TAX_CODE
        ,CATEGORY_SET_NAME
        ,CATEGORY_NAME
        ,PROCESS_STATUS
        ,ERROR_MESSAGE
    FROM zninv_mtl_system_items_b_stg
        WHERE PROCESS_STATUS IS NULL;

--    FOR UPDATE OF PROCESS_STATUS, ERROR_MESSAGE;


   -- Local Variables
     x_process_id                      NUMBER;
     x_error_flag                      VARCHAR2(1):='N';
     x_error_message                   VARCHAR2(4000);
     x_item_err_details                VARCHAR2(4000);
     x_uom_code                        NUMBER;
     x_uom                             NUMBER;
     x_weight_uom_code                 NUMBER;
     x_item_type                       fnd_common_lookups.lookup_code%TYPE;
     x_cat_set_nm                      mtl_category_sets.category_set_name%TYPE;
     x_cat_set_id                      mtl_category_sets.category_set_id%TYPE;
     x_cat_id                          NUMBER;
     x_structure_id                    NUMBER;
     x_cat_segment1                    VARCHAR2(40);
     x_organization_code               VARCHAR2(3);
     x_category_name                   VARCHAR2(400);
     x_organization_id                NUMBER;
     x_item_count            NUMBER;
     x_cnt_temp_name            NUMBER;
     x_cnt_uom                NUMBER;
     x_cnt_item_type            NUMBER;
     X_ENCUMB_ACC_ID            NUMBER;
     X_EXP_ACC_ID            NUMBER;
     X_SALES_ACC_ID            NUMBER;
     X_COST_OF_SALES_ACC_ID        NUMBER;
     x_debug_point            NUMBER;
     x_cnt_INV_PLAN_CODE        NUMBER;
     X_CNT_ASSET_CAT_CODE        NUMBER;
     x_cnt_tax_code            NUMBER;

   BEGIN --Item Validate Procedure

--    Update zninv_mtl_system_items_b_stg Set category_name = Replace(category_name, chr(13), '')
--    Where  process_status IS NULL;

    COMMIT;

       BEGIN

         SELECT COUNT(1)
          INTO   g_item_found
         FROM   zninv_mtl_system_items_b_stg
         WHERE  process_status IS NULL;

       EXCEPTION
              WHEN OTHERS THEN
                fnd_file.put_line (fnd_file.LOG,'Error: '|| SQLCODE || '  '|| SQLERRM);
       END;

       fnd_file.put_line (fnd_file.LOG,'Number of Items for Migration ='||g_item_found);
       g_item_processed :=0;
       g_item_rejected  :=0;

       FOR i IN c_staging_item_create LOOP

    x_error_flag:='N';
           x_error_message :=NULL;
           x_item_err_details:=NULL;
           x_uom_code:=0;
           x_uom:=0;
           x_weight_uom_code:=0;
           x_cat_set_nm:=NULL;
           x_cat_segment1:=NULL;
           x_organization_code:=NULL;
           X_ENCUMB_ACC_ID            :=NULL;
           X_EXP_ACC_ID            :=NULL;
           X_SALES_ACC_ID            :=NULL;
           X_COST_OF_SALES_ACC_ID        :=NULL;
           x_cnt_INV_PLAN_CODE        :=NULL;
           X_CNT_ASSET_CAT_CODE        :=0;
           x_organization_id            :=0;
           x_item_count            :=0;
           x_cnt_temp_name            :=0;
           x_cnt_uom            :=0;
           x_cnt_item_type            :=0;
    x_cnt_tax_code            :=0;
      -- x_category_name:=i.cat_seg1||'.'||i.cat_seg2||i.cat_seg3||i.cat_seg4;


    x_debug_point:=1;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag -'|| x_error_flag);


/*-------------------------Validation starts for Items-------------------------------*/

    x_debug_point:= 2;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before organization code Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag -'|| x_error_flag);


---------------------------Validation for organization code and Fetch the value of x_organization_id ---------------------------

       IF i.ORGANIZATION_CODE IS NOT NULL THEN
              BEGIN
                 SELECT organization_id
                   INTO x_organization_id
                   FROM ORG_ORGANIZATION_DEFINITIONS
                  WHERE organization_code = i.ORGANIZATION_CODE;

            EXCEPTION
                  WHEN OTHERS THEN
            fnd_file.put_line (fnd_file.LOG,'organization_id is not defined for the given organization_code -'||i.ORGANIZATION_CODE);

              END;
       ELSE
          x_error_flag := 'Y';
          x_error_message := x_error_message||'There is no ORGANIZATION_CODE';
          x_item_err_details := 'Error in batch: ' || i.batch_name || ' - ORGANIZATION_CODE: ' || i.ORGANIZATION_CODE || ' - ' || 'is Null';
      fnd_file.put_line (fnd_file.LOG, x_item_err_details);
       END IF ;


    x_debug_point:= 3;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Item_number Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag -'|| x_error_flag);

---------------------------Validation for Item_number---------------------------
/****
    IF i.item_number IS NULL THEN
             x_error_flag := 'Y';
             x_error_message := x_error_message||'There is no item_number.';
             x_item_err_details := 'Error in batch: ' || i.batch_name || ' -item_number: ' || i.item_number || ' - ' || ' is Null';
         fnd_file.put_line (fnd_file.LOG, x_item_err_details);
      ELSE
             SELECT COUNT (1)
             INTO x_item_count
                FROM mtl_system_items_b msi
                WHERE msi.organization_id = x_organization_id
                AND msi.segment1 = i.item_number;

         IF x_item_count>0 THEN
            x_error_flag := 'Y';
            x_error_message := x_error_message||'Item_number already exists';
            x_item_err_details := 'Error in batch: ' || i.batch_name || ' -Item_number: ' || i.Item_number || ' - ' || 'already exists.';
        fnd_file.put_line (fnd_file.LOG, x_item_err_details);
         END IF;

      END IF ;

      fnd_file.put_line (fnd_file.LOG,'x_error_flag'||x_error_flag);
****/
    x_debug_point:= 4;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Description Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
 --comment here for items updation
---------------------------Validation for Description---------------------------

    IF i.DESCRIPTION IS NULL THEN
             x_error_flag := 'Y';
             x_error_message := x_error_message||'There is no DESCRIPTION.';
             x_item_err_details := 'Error in batch: ' || i.batch_name || '- DESCRIPTION: ' || i.DESCRIPTION || ' - ' || ' is Null';
         fnd_file.put_line (fnd_file.LOG, x_item_err_details);
    END IF;


    x_debug_point:= 5;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before ATTRIBUTE_CATEGORY Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);

/*-----------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------Validation for ATTRIBUTE_CATEGORY---------------------------

    IF i.ATTRIBUTE_CATEGORY IS NULL THEN
             x_error_flag := 'Y';
             x_error_message := x_error_message||'There is no ATTRIBUTE_CATEGORY.';
             x_item_err_details := 'Error in batch: ' || i.batch_name || '- ATTRIBUTE_CATEGORY: ' || i.ATTRIBUTE_CATEGORY || ' - ' || ' is Null';
         fnd_file.put_line (fnd_file.LOG, x_item_err_details);
    END IF;


    x_debug_point:= 6;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before ATTRIBUTE1 Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);

---------------------------Validation for ATTRIBUTE1---------------------------

    IF i.ATTRIBUTE1 IS NULL THEN
             x_error_flag := 'Y';
             x_error_message := x_error_message||'There is no ATTRIBUTE1 Value.';
             x_item_err_details := 'Error in batch: ' || i.batch_name || '- ATTRIBUTE1: ' || i.ATTRIBUTE1 || ' - ' || ' is Null';
         fnd_file.put_line (fnd_file.LOG, x_item_err_details);
    END IF;

    x_debug_point:= 7;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before ATTRIBUTE2 Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);


---------------------------Validation for ATTRIBUTE2---------------------------

    IF i.ATTRIBUTE2 IS NULL THEN
             x_error_flag := 'Y';
             x_error_message := x_error_message||'There is no ATTRIBUTE1 Value.';
             x_item_err_details := 'Error in batch: ' || i.batch_name || '- ATTRIBUTE2: ' || i.ATTRIBUTE2 || ' - ' || ' is Null';
         fnd_file.put_line (fnd_file.LOG, x_item_err_details);
    END IF;

    x_debug_point:= 8;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before ATTRIBUTE2 Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);


---------------------------Validation for ATTRIBUTE3---------------------------

    IF i.ATTRIBUTE3 IS NULL THEN
             x_error_flag := 'Y';
             x_error_message := x_error_message||'There is no ATTRIBUTE1 Value.';
             x_item_err_details := 'Error in batch: ' || i.batch_name || '- ATTRIBUTE3: ' || i.ATTRIBUTE3 || ' - ' || ' is Null';
         fnd_file.put_line (fnd_file.LOG, x_item_err_details);
    END IF;

    x_debug_point:= 10;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Primary  Unit of Measure Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
************************************************************************************************************************************************************/

---------------------------Validation for Primary  Unit of Measure---------------------------

/****
    IF    i.PRIMARY_UNIT_OF_MEASURE IS NULL THEN
          x_error_flag       := 'Y';
              x_error_message    := x_error_message||'Primary Unit of Measure cannot be NULL.';
              x_item_err_details := 'Error in batch: ' || i.batch_name || ' - PRIMARY_UNIT_OF_MEASURE : ' || i.PRIMARY_UNIT_OF_MEASURE || ' - ' || ' is Null';

          fnd_file.put_line (fnd_file.LOG, x_item_err_details);

    ELSE  SELECT COUNT(1)
              INTO x_cnt_uom
              FROM MTL_UNITS_OF_MEASURE
              WHERE UPPER(UNIT_OF_MEASURE) = UPPER(RTRIM(LTRIM(i.PRIMARY_UNIT_OF_MEASURE)))
          AND NVL(TRUNC(disable_date),TRUNC(SYSDATE+1))>TRUNC(SYSDATE);

             IF x_cnt_uom = 0 THEN
                   x_error_flag       := 'Y';
                   x_error_message    := x_error_message||'UOM is Invalid';
                   x_item_err_details := 'Error in batch: ' || i.batch_name || ' -PRIMARY_UNIT_OF_MEASURE: ' || i.PRIMARY_UNIT_OF_MEASURE || ' - ' || 'is Invalid.';
               fnd_file.put_line (fnd_file.LOG, x_item_err_details);
             END IF;

    END IF;

    fnd_file.put_line (fnd_file.LOG,'x_error_flag'||x_error_flag);

****/
    x_debug_point:= 11;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before ITEM_TYPE Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);

---------------------------Validation for Item Type------------------------------

    IF i.ITEM_TYPE IS NOT NULL THEN

                  SELECT COUNT(1)
                  INTO   x_cnt_item_type
                  FROM   FND_COMMON_LOOKUPS
                  WHERE  lookup_type ='ITEM_TYPE'
          AND enabled_flag = 'Y'
                  AND    UPPER(TRIM(MEANING)) = UPPER(TRIM(i.ITEM_TYPE))
          AND    SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE, SYSDATE+1);

        x_debug_point:= 888;
            FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Template name Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_cnt_item_type - '|| x_cnt_item_type||i.ITEM_TYPE);

                  IF x_cnt_item_type = 0 THEN
                    x_error_flag :='Y';
                    x_error_message := x_error_message ||'Invalid Item Type Lookup Code';
                    x_item_err_details := 'Error in batch: ' || i.batch_name || ' - Item Type: ' ||i.ITEM_TYPE  || ' - ' || ' No Data Found for Item Type Lookup Code';
                fnd_file.put_line (fnd_file.LOG, x_item_err_details);
          END IF;

         END IF;

      fnd_file.put_line (fnd_file.LOG,'x_error_flag'||x_error_flag);


    x_debug_point:= 12;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before COST_OF_SALES_ACCOUNT Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);

---------------------------Validation for COST_OF_SALES_ACCOUNT------------------------------

    IF i.COST_OF_SALES_ACCOUNT IS NOT NULL THEN

        BEGIN
            SELECT CODE_COMBINATION_ID
                        INTO   X_COST_OF_SALES_ACC_ID
                        FROM   gl_code_combinations
                        WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7  = i.COST_OF_SALES_ACCOUNT
                        AND    enabled_flag          = 'Y'
                        AND    summary_flag          = 'N'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));

        EXCEPTION
                       WHEN NO_DATA_FOUND THEN
                                x_error_flag := 'Y';
                     x_error_message := x_error_message||'Code Combination ID does not exists for the given COST_OF_SALES_ACCOUNT Value.';
                     x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;

                        WHEN OTHERS THEN
                           x_error_flag := 'Y';
                     x_error_message := x_error_message||'Code Combination ID does not exists for the given COST_OF_SALES_ACCOUNT Value.';
                     x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;

               END;

--    ELSE --- i.COST_OF_SALES_ACCOUNT IS NULL
--                 x_error_flag := 'Y';
--                 x_error_message := x_error_message||'There is no COST_OF_SALES_ACCOUNT Value.';
--                 x_item_err_details := 'Error in batch: ' || i.batch_name || '- COST_OF_SALES_ACCOUNT: ' || i.COST_OF_SALES_ACCOUNT|| ' - ' || ' is Null';

    END IF;
    x_debug_point:= 13;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before LIST_PRICE_PER_UNIT Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);


---------------------------Validation for LIST_PRICE_PER_UNIT------------------------------

/******************************************************************************************
IF i.LIST_PRICE_PER_UNIT IS NULL THEN

         x_error_flag := 'Y';
         x_error_message := x_error_message||'There is no LIST_PRICE_PER_UNIT.';
         x_item_err_details := 'Error in batch: ' || i.batch_name || ' -LIST_PRICE_PER_UNIT: ' || i.LIST_PRICE_PER_UNIT || ' - ' || ' is Null';
     fnd_file.put_line (fnd_file.LOG, x_item_err_details);
END IF;

    x_debug_point:= 14;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before ENCUMBRANCE_ACCOUNT Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);

******************************************************************************************/

---------------------------Validation for ENCUMBRANCE_ACCOUNT------------------------------

    IF i.ENCUMBRANCE_ACCOUNT IS NOT NULL THEN

        BEGIN
            SELECT CODE_COMBINATION_ID
                        INTO   X_ENCUMB_ACC_ID
                        FROM   gl_code_combinations
                        WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7  = i.ENCUMBRANCE_ACCOUNT
                        AND    enabled_flag          = 'Y'
                        AND    summary_flag          = 'N'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));

        EXCEPTION
                       WHEN NO_DATA_FOUND THEN
                                x_error_flag := 'Y';
                     x_error_message := x_error_message||'Code Combination ID does not exists for the given ENCUMBRANCE_ACCOUNT Value.';
                     x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;

                        WHEN OTHERS THEN
                           x_error_flag := 'Y';
                     x_error_message := x_error_message||'Code Combination ID does not exists for the given ENCUMBRANCE_ACCOUNT Value.';
                     x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;

                       END;

--    ELSE --- i.ENCUMBRANCE_ACCOUNT IS NULL
--                 x_error_flag := 'Y';
--                 x_error_message := x_error_message||'There is no ENCUMBRANCE_ACCOUNT Value.';
--                 x_item_err_details := 'Error in batch: ' || i.batch_name || '- ENCUMBRANCE_ACCOUNT: ' || i.ENCUMBRANCE_ACCOUNT|| ' - ' || ' is Null';

    END IF;

    x_debug_point:= 15;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before EXPENSE_ACCOUNT Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);

---------------------------Validation for EXPENSE_ACCOUNT------------------------------

    IF i.EXPENSE_ACCOUNT IS NOT NULL THEN

        BEGIN
            SELECT CODE_COMBINATION_ID
                        INTO   X_EXP_ACC_ID
                        FROM   gl_code_combinations
                        WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7  = i.EXPENSE_ACCOUNT
                        AND    enabled_flag          = 'Y'
                        AND    summary_flag          = 'N'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));

        EXCEPTION
                       WHEN NO_DATA_FOUND THEN
                                x_error_flag := 'Y';
                     x_error_message := x_error_message||'Code Combination ID does not exists for the given EXPENSE_ACCOUNT Value.';
                     x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;

                        WHEN OTHERS THEN
                           x_error_flag := 'Y';
                     x_error_message := x_error_message||'Code Combination ID does not exists for the given EXPENSE_ACCOUNT Value.';
                     x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;

                       END;
--    ELSE --- i.EXPENSE_ACCOUNT IS NULL
--                 x_error_flag := 'Y';
--                 x_error_message := x_error_message||'There is no EXPENSE_ACCOUNT Value.';
--                 x_item_err_details := 'Error in batch: ' || i.batch_name || '- EXPENSE_ACCOUNT: ' || i.EXPENSE_ACCOUNT|| ' - ' || ' is Null';

    END IF;
/****

    x_debug_point:= 16;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before ASSET_CATEGORY_CODE Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);

---------------------------Validation for ASSET_CATEGORY_CODE------------------------------

    IF i.ASSET_CATEGORY_CODE IS NOT NULL THEN

        SELECT COUNT(1)
                INTO   X_CNT_ASSET_CAT_CODE
                FROM   fa_categories_b
                WHERE UPPER(SEGMENT1)||'.'||UPPER(SEGMENT2)||'.'||UPPER(SEGMENT3)||'.'||UPPER(SEGMENT4)  = UPPER(I.ASSET_CATEGORY_CODE)
                AND    enabled_flag          = 'Y'
                AND    summary_flag          = 'N'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE)); ****/
/**********************************************************************************
        IF X_CNT_ASSET_CAT_CODE = 0 THEN
            x_error_flag := 'Y';
                 x_error_message := x_error_message||'Category ID does not exists for the given ASSET_CATEGORY_CODE Value.';
                 x_item_err_details := 'Error in batch: ' || i.batch_name || '- Category ID does not exists for the given ASSET_CATEGORY_CODE: ' || i.ASSET_CATEGORY_CODE;
        end if;
***********************************************************************************/
/****
    END IF;

    x_debug_point:= 222;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before INVENTORY_PLANNING_CODE Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);

---------------------------Validation for INVENTORY_PLANNING_CODE-----------------------

--    IF i.INVENTORY_PLANNING_CODE IS NULL THEN
--         x_error_flag := 'Y';
--         x_error_message := x_error_message||'There is no INVENTORY_PLANNING_CODE.';
--         x_item_err_details := 'Error in batch: ' || i.batch_name || ' -INVENTORY_PLANNING_CODE: ' || i.INVENTORY_PLANNING_CODE || ' - ' || ' is Null';
--       fnd_file.put_line (fnd_file.LOG, x_item_err_details);

    IF i.INVENTORY_PLANNING_CODE IS NOT NULL THEN

        SELECT COUNT(1)
                INTO   x_cnt_INV_PLAN_CODE
                FROM   fnd_lookup_values_vl lvl
                WHERE  UPPER(TRIM(lvl.lookup_type)) = UPPER(TRIM('MTL_MATERIAL_PLANNING'))
                AND    UPPER(TRIM(lvl.MEANING)) = UPPER(TRIM(I.INVENTORY_PLANNING_CODE))
                AND    ENABLED_FLAG='Y'
                AND    SYSDATE BETWEEN NVL(lvl.START_DATE_ACTIVE, SYSDATE-1) AND NVL(lvl.END_DATE_ACTIVE, SYSDATE+1);

        IF x_cnt_INV_PLAN_CODE = 0 THEN
                                x_error_flag := 'Y';
                     x_error_message := x_error_message||'Given INVENTORY_PLANNING_CODE is not defined in the lookups.';
                     x_item_err_details := 'Error in batch: ' || i.batch_name || '- INVENTORY_PLANNING_CODE is not defined in the lookups: ' || i.INVENTORY_PLANNING_CODE;
        END IF;

    END IF;


    x_debug_point:= 17;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before SALES_ACCOUNT Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);


--------------------------- Validation for PLANNER_CODE -----------------------

    IF i.PLANNER_CODE IS NOT NULL THEN

        SELECT     COUNT(1)
                INTO       x_cnt_INV_PLAN_CODE
        FROM      MTL_PLANNERS
        WHERE    PLANNER_CODE = i.PLANNER_CODE
        AND    ORGANIZATION_ID = x_organization_id
        AND    SYSDATE < NVL(DISABLE_DATE, SYSDATE+1);

        IF x_cnt_INV_PLAN_CODE = 0 THEN
                         x_error_flag := 'Y';
                 x_error_message := x_error_message||'PLANNER_CODE is not defined for the organization ' || i.PLANNER_CODE ;
                 x_item_err_details := 'Error in batch: ' || i.batch_name || '- PLANNER_CODE is not defined for the organization: ' || i.PLANNER_CODE;
        END IF;

    END IF;

--------------------------- Validation for TAX_CODE -----------------------

    IF i.PLANNER_CODE IS NOT NULL THEN

        SELECT     COUNT(1)
        INTO    x_cnt_tax_code
        FROM    ZX_RATES_B  ZRB
        WHERE     ZRB.TAX_JURISDICTION_CODE IN ('DIRECTION GENERALE DE GE')
        AND     ZRB.TAX_RATE_CODE = i.TAX_CODE
        AND     ZRB.ACTIVE_FLAG = 'Y'
        AND     TRUNC(SYSDATE) BETWEEN ZRB.EFFECTIVE_FROM AND NVL(ZRB.EFFECTIVE_TO, TRUNC(SYSDATE));

        IF x_cnt_tax_code = 0 THEN
                         x_error_flag := 'Y';
                 x_error_message := x_error_message||'TAX_CODE is not defined for the organization ' || i.TAX_CODE;
                 x_item_err_details := 'Error in batch: ' || i.batch_name || '- TAX_CODE is not defined for the organization: ' || i.TAX_CODE;
        END IF;

    END IF;

****/
---------------------------Validation for SALES_ACCOUNT------------------------------

    IF i.SALES_ACCOUNT IS NOT NULL THEN

        BEGIN
            SELECT CODE_COMBINATION_ID
                        INTO   X_SALES_ACC_ID
                        FROM   gl_code_combinations
                        WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7  = i.SALES_ACCOUNT
                        AND    enabled_flag          = 'Y'
                        AND    summary_flag          = 'N'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));

        EXCEPTION
                       WHEN NO_DATA_FOUND THEN
                                x_error_flag := 'Y';
                     x_error_message := x_error_message||'Code Combination ID does not exists for the given SALES_ACCOUNT Value.';
                     x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;

                        WHEN OTHERS THEN
                           x_error_flag := 'Y';
                     x_error_message := x_error_message||'Code Combination ID does not exists for the given SALES_ACCOUNT Value.';
                     x_item_err_details := 'Error in batch: ' || i.batch_name || '- Code Combination ID does not exists for the given Item: ' || i.Item_number;

                END;

    END IF;
/****
    x_debug_point:= 18;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Category Set Name Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);


---------------------------Validation for Category Set Name------------------------------

     IF i.CATEGORY_SET_NAME IS NOT NULL THEN

         BEGIN

             SELECT  category_set_name,
                     category_set_id,
                     structure_id
             INTO    x_cat_set_nm,x_cat_set_id,x_structure_id
             FROM    mtl_category_sets
             WHERE   UPPER(TRIM(category_set_name))= UPPER(TRIM(i.CATEGORY_SET_NAME));

         EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                    x_error_flag :='Y';
                    x_error_message := x_error_message ||'Invalid Category Set Name';
                    x_item_err_details := 'Error in batch: ' || i.batch_name || ' - Category Set Name: ' ||i.CATEGORY_SET_NAME  || ' - ' || ' No Data Found             for Category Set Name';
                fnd_file.put_line (fnd_file.LOG,x_item_err_details);

                 WHEN OTHERS THEN
                    x_error_flag :='Y';
                    x_error_message := x_error_message || '\' ||SQLCODE || '  '|| SQLERRM;
                    x_item_err_details := 'Error in batch cat set name: ' || i.batch_name || '-' ||SQLCODE || '  '|| SQLERRM;
                fnd_file.put_line (fnd_file.LOG,x_item_err_details);
         END;
     END IF;

    x_debug_point:= 19;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Category Name Validation starts '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
****/
---------------------------Validation for Category Name------------------------------
/***************************************************************************************************************************************
      IF i.CATEGORY_NAME IS NULL THEN

          x_error_flag :='Y';
      x_error_message := x_error_message || '-'||'Invalid i.CATEGORY_NAME : ' ||x_category_name;
      ELSE
     BEGIN
       SELECT category_id
       INTO x_cat_id
       FROM mtl_categories_b
--       WHERE UPPER(segment1)||'.'||UPPER(segment2) ||'.'||UPPER(segment3)||'.'||UPPER(segment4) = UPPER(TRIM(i.category_name))
       WHERE UPPER(segment1)||'.'||UPPER(segment2) ||'.'||UPPER(segment3)||'.'||UPPER(segment4) LIKE UPPER(TRIM(i.category_name))
       AND  structure_id = x_structure_id;


         EXCEPTION

                 WHEN NO_DATA_FOUND THEN
        x_debug_point:= 999111;
            FND_FILE.PUT_LINE(FND_FILE.LOG,'iN eXCEPTION '|| ' at debug point : ' ||x_debug_point||' '||'x_cat_id - '|| x_cat_id||i.category_name);

                    x_error_flag :='Y';
                    x_error_message := x_error_message ||'Invalid Category Name';
                    x_item_err_details := 'Error in batch: ' || i.batch_name || ' - Category Name: ' ||i.CATEGORY_NAME ||  ' No Data Found for Category Name';
                fnd_file.put_line (fnd_file.log,x_item_err_details);

                 WHEN OTHERS THEN
                    x_error_flag :='Y';
                    x_error_message := x_error_message || '\' ||SQLCODE || '  '|| SQLERRM;
                    x_item_err_details := 'Error in batch cat name: ' || i.batch_name || '-' ||SQLCODE || '  '|| SQLERRM;
                fnd_file.put_line (fnd_file.log, x_item_err_details);
         END;
       END IF;

       x_debug_point:= 20;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'End of Item Validations '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);

*******************************************************************************************************************************************/
/****
     IF i.CATEGORY_NAME IS NOT NULL THEN

         BEGIN
       SELECT category_id
       INTO x_cat_id
       FROM mtl_categories_kfv
       WHERE UPPER(concatenated_segments) = UPPER(i.category_name)
--       WHERE UPPER(segment1)||'.'||UPPER(segment2) ||'.'||UPPER(segment3)||'.'||UPPER(segment4) LIKE UPPER(TRIM(i.category_name))
       AND  structure_id = x_structure_id;


         EXCEPTION

                 WHEN NO_DATA_FOUND THEN
        x_debug_point:= 999111;
            FND_FILE.PUT_LINE(FND_FILE.LOG,'iN eXCEPTION '|| ' at debug point : ' ||x_debug_point||' '||'x_cat_id - '|| x_cat_id||i.category_name);

                    x_error_flag :='Y';
                    x_error_message := x_error_message ||'Invalid Category Name';
                    x_item_err_details := 'Error in batch: ' || i.batch_name || ' - Category Name: ' ||i.CATEGORY_NAME ||  ' No Data Found for Category Name';
                fnd_file.put_line (fnd_file.LOG,x_item_err_details);

                 WHEN OTHERS THEN
                    x_error_flag :='Y';
                    x_error_message := x_error_message || '\' ||SQLCODE || '  '|| SQLERRM;
                    x_item_err_details := 'Error in batch cat name: ' || i.batch_name || '-' ||SQLCODE || '  '|| SQLERRM;
                fnd_file.put_line (fnd_file.LOG, x_item_err_details);
         END;
       END IF;

       x_debug_point:= 20;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'End of Item Validations '|| ' at debug point : ' ||x_debug_point||' '||'x_error_flag - '|| x_error_flag);
****/
---------------------------Validation Ends for Items----------------------------------
-----*** If there is an error in Items then update Items staging tables*****----

        fnd_file.put_line (fnd_file.LOG,'x_error_flag'||x_error_flag);

    IF  NVL(x_error_flag,'N')='Y' THEN

            UPDATE  zninv_mtl_system_items_b_stg
            SET     PROCESS_STATUS = 'E',
                    ERROR_MESSAGE   = SUBSTR(x_item_err_details,1,2000)
--          WHERE   ROWID       = i.msirowid;
--        WHERE CURRENT OF c_staging_item_create;
        WHERE BATCH_NAME = i.BATCH_NAME
        AND   ITEM_NUMBER = i.ITEM_NUMBER;


        g_item_rejected  := g_item_rejected  + 1;

        ELSE  -- IF  x_error_flag = 'N'

        UPDATE    zninv_mtl_system_items_b_stg
        SET       PROCESS_STATUS ='I'
--          WHERE     ROWID   = i.msirowid;
--        WHERE CURRENT OF c_staging_item_create;
        WHERE BATCH_NAME = i.BATCH_NAME
        AND   ITEM_NUMBER = i.ITEM_NUMBER;

            g_item_processed := g_item_processed  + 1;

       END IF;

    x_debug_point:= 21;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'After updating staging table with Process Flag : '|| ' at debug point : ' ||x_debug_point);

       COMMIT;

    x_debug_point:= 22;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'After commiting : '|| ' at debug point : ' ||x_debug_point);

     END LOOP;

    x_debug_point:= 23;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'After looping though all records : '|| ' at debug point : ' ||x_debug_point);

END ZNinv_item_validate_proc;

PROCEDURE ZNinv_item_insert_proc  AS
/*--------------------------------------------------------------------------------------
       Name        :      ZNinv_item_insert_proc
       Description :      This is the  procedure called by the main procedure to insert
                          records into the interface  tables after validation.
       Parameters  :      None
       Returns     :      None
---------------------------------------------------------------------------------------*/

       g_user_id  NUMBER   := fnd_global.user_id;
       g_login_id NUMBER   := fnd_global.user_id;
       --x_item_err_details   VARCHAR2(4000);

--       CURSOR c_insert_item IS
--          SELECT xmsi.ROWID msirowid, xmsi.*
--          FROM   zninv_mtl_system_items_b_stg xmsi
--          WHERE  xmsi.PROCESS_STATUS ='I';

       CURSOR c_insert_item IS
         SELECT DISTINCT BATCH_NAME
         ,ITEM_NUMBER
         ,DESCRIPTION
         ,TEMPLATE_NAME
         ,ATTRIBUTE_CATEGORY
         ,ATTRIBUTE1
         ,ATTRIBUTE2
         ,ATTRIBUTE3
         ,ORGANIZATION_CODE
        ,PRIMARY_UNIT_OF_MEASURE
         ,ITEM_TYPE
         ,INVENTORY_ITEM_FLAG
         ,STOCK_ENABLED_FLAG
         ,MTL_TRANSACTIONS_ENABLED_FLAG
         ,SO_TRANSACTIONS_FLAG
         ,CYCLE_COUNT_ENABLED_FLAG
         ,COSTING_ENABLED_FLAG
         ,INVENTORY_ASSET_FLAG
         ,DEFAULT_INCLUDE_IN_ROLLUP_FLAG
         ,PURCHASING_ITEM_FLAG
         ,PURCHASING_ENABLED_FLAG
         ,SERIAL_NUM_GENERATION
         ,LIST_PRICE_PER_UNIT
         ,COST_OF_SALES_ACCOUNT
         ,ENCUMBRANCE_ACCOUNT
         ,EXPENSE_ACCOUNT
         ,SALES_ACCOUNT
         ,ASSET_CATEGORY_CODE
         ,RECEIVING_ROUTING
         ,INVENTORY_PLANNING_CODE
         ,MIN_MINMAX_QUANTITY
         ,MAX_MINMAX_QUANTITY
         ,MINIMUM_ORDER_QUANTITY
         ,MAXIMUM_ORDER_QUANTITY
         ,SOURCE_TYPE
         ,SOURCE_ORGANIZATION
         ,SOURCE_SUBINVENTORY
         ,PLANNER_CODE
         ,CUSTOMER_ORDER_FLAG
         ,CUSTOMER_ORDER_ENABLED_FLAG
         ,SHIPPABLE_ITEM_FLAG
         ,INTERNAL_ORDER_FLAG
         ,INTERNAL_ORDER_ENABLED_FLAG
         ,RETURNABLE_FLAG
         ,INVOICEABLE_ITEM_FLAG
         ,INVOICE_ENABLED_FLAG
         ,TAX_CODE
        ,PROCESS_STATUS
    FROM zninv_mtl_system_items_b_stg
        WHERE NVL(PROCESS_STATUS,'X') ='I';

--    FOR UPDATE OF PROCESS_STATUS;


       CURSOR c_insert_item_catg (P_BATCH_NAME VARCHAR2, P_ITEM_NUMBER VARCHAR2) IS
      SELECT BATCH_NAME
        ,ITEM_NUMBER
        ,ORGANIZATION_CODE
        ,CATEGORY_SET_NAME
        ,CATEGORY_NAME
      FROM    zninv_mtl_system_items_b_stg
      WHERE BATCH_NAME  = P_BATCH_NAME
      AND   ITEM_NUMBER = P_ITEM_NUMBER
      AND   CATEGORY_SET_NAME IS NOT NULL
      AND   CATEGORY_NAME IS NOT NULL;

CURSOR c1 (p_item_number VARCHAR2) IS
SELECT DISTINCT organization_id
FROM mtl_system_items_b
WHERE segment1 = p_item_number;

x_organization_code VARCHAR2(100);

------------------------------ Local Variables --------------------------------------

           x_ins_item_error        VARCHAR2(4000);
    x_organization_id    NUMBER;
    X_pri_uom_code        VARCHAR2(4000);
    X_TEMPLATE_ID        VARCHAR2(4000);
    X_COST_OF_SALES_ACC_ID    NUMBER;
    X_ENCUMB_ACC_ID        NUMBER;
    X_EXP_ACC_ID        NUMBER;
    X_SALES_ACC_ID        NUMBER;
    x_cat_set_id        NUMBER;
    x_structure_id        NUMBER;
    x_cat_id        NUMBER;
    x_debug_point        NUMBER;
    x_inv_plan_code        NUMBER;
    X_ASSET_CATEGORY_ID    NUMBER;
    x_item_type_code    FND_COMMON_LOOKUPS.LOOKUP_CODE%TYPE;
    x_srl_num_control_code    MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE%TYPE;
    x_receiving_routing_id    MTL_SYSTEM_ITEMS_B.RECEIVING_ROUTING_ID%TYPE;
    x_source_type        MTL_SYSTEM_ITEMS_B.SOURCE_TYPE%TYPE;
    x_src_organization_id    NUMBER;



 BEGIN

     FOR i IN  c_insert_item
     LOOP
        x_ins_item_error     := NULL;
    x_organization_id    := 0;
    X_pri_uom_code        := NULL;
    x_template_id        := NULL;
    X_COST_OF_SALES_ACC_ID    := NULL;
    X_ENCUMB_ACC_ID        := NULL;
    X_EXP_ACC_ID        := NULL;
    X_SALES_ACC_ID        := NULL;
    x_inv_plan_code        := NULL;
    X_ASSET_CATEGORY_ID    := NULL;
    x_item_type_code    := NULL;
    x_srl_num_control_code    := NULL;
    x_receiving_routing_id    := NULL;
    x_source_type        := NULL;
    x_src_organization_id    := NULL;


       x_debug_point:= 31;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Loop Begins '|| ' at debug point : ' ||x_debug_point);

--------------------------- Fetch the value of organization_id---------------------------

              BEGIN
                 SELECT organization_id
                   INTO x_organization_id
                   FROM ORG_ORGANIZATION_DEFINITIONS
                  WHERE organization_code = i.ORGANIZATION_CODE;

            EXCEPTION
                  WHEN OTHERS THEN
            fnd_file.put_line (fnd_file.LOG,'organization_id is not defined for the given organization_code -'||i.ORGANIZATION_CODE);

              END;

        x_debug_point:= 32;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'After Ftech the value of x_organization_id '|| ' at debug point : ' ||x_debug_point||'-'|| x_organization_id);

--------------------------- Fetch the value of primary_uom_code---------------------------
/****
        BEGIN
                 SELECT UOM_CODE
                      INTO X_pri_uom_code
                      FROM MTL_UNITS_OF_MEASURE
                      WHERE UPPER(UNIT_OF_MEASURE) = UPPER(RTRIM(LTRIM(i.PRIMARY_UNIT_OF_MEASURE)))
                  AND NVL(TRUNC(disable_date),TRUNC(SYSDATE+1))>TRUNC(SYSDATE);

            EXCEPTION
                  WHEN OTHERS THEN
            fnd_file.put_line (fnd_file.LOG,'primary_uom_code is not defined for the given PRIMARY_UNIT_OF_MEASURE -'||i.PRIMARY_UNIT_OF_MEASURE);

              END;

    x_debug_point:= 33;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'After Ftech the value of X_pri_uom_code   '|| ' at debug point : ' ||x_debug_point||'-'|| X_pri_uom_code);

--------------------------- Fetch the value of x_template_id---------------------------

    IF i.template_name IS NOT NULL THEN

        BEGIN
                 SELECT TEMPLATE_ID
            INTO x_template_id
            FROM mtl_item_templates
            WHERE UPPER(TRIM(template_name)) = UPPER(TRIM(i.template_name));

            EXCEPTION
                  WHEN OTHERS THEN
            fnd_file.put_line (fnd_file.LOG,'There is no TEMPLATE_ID for the given template_name -'||i.template_name);

              END;
    END IF;
     ****/
        x_debug_point:= 34;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'After Ftech the value of x_template_id  '|| ' at debug point : ' ||x_debug_point||'-'||x_template_id);

----------------------------- Fetch the value of X_COST_OF_SALES_ACC_ID---------------------------

    IF i.COST_OF_SALES_ACCOUNT IS NOT NULL THEN

        BEGIN
            SELECT CODE_COMBINATION_ID
                        INTO   X_COST_OF_SALES_ACC_ID
                        FROM   gl_code_combinations
                        WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7  = i.COST_OF_SALES_ACCOUNT
                        AND    enabled_flag          = 'Y'
                        AND    summary_flag          = 'N'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));

        EXCEPTION
                       WHEN NO_DATA_FOUND THEN
                                fnd_file.put_line (fnd_file.LOG,'Code combination ID does not exists for the given COST_OF_SALES_ACCOUNT -'||i.COST_OF_SALES_ACCOUNT);
                        WHEN OTHERS THEN
                           fnd_file.put_line (fnd_file.LOG,'Code combination ID does not exists for the given COST_OF_SALES_ACCOUNT -'||i.COST_OF_SALES_ACCOUNT);

                 END;

    END IF;

    x_debug_point:= 35;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'After Ftech the value of X_COST_OF_SALES_ACC_ID  '|| ' at debug point : ' ||x_debug_point||'-'||X_COST_OF_SALES_ACC_ID);

--------------------------- Fetch the value of X_ENCUMB_ACC_ID---------------------------

    IF i.ENCUMBRANCE_ACCOUNT IS NOT NULL THEN

        BEGIN
            SELECT CODE_COMBINATION_ID
                        INTO   X_ENCUMB_ACC_ID
                        FROM   gl_code_combinations
                        WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7  = i.ENCUMBRANCE_ACCOUNT
                        AND    enabled_flag          = 'Y'
                        AND    summary_flag          = 'N'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));

        EXCEPTION
                       WHEN NO_DATA_FOUND THEN
                                fnd_file.put_line (fnd_file.LOG,'Code combination ID does not exists for the given ENCUMBRANCE_ACCOUNT -'||i.ENCUMBRANCE_ACCOUNT);

                        WHEN OTHERS THEN
                               fnd_file.put_line (fnd_file.LOG,'Code combination ID does not exists for the given ENCUMBRANCE_ACCOUNT -'||i.ENCUMBRANCE_ACCOUNT);

                 END;

    END IF;

    x_debug_point:= 36;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'After Ftech the value of X_ENCUMB_ACC_ID  '|| ' at debug point : ' ||x_debug_point||'-'||X_ENCUMB_ACC_ID);

--------------------------- Fetch the value of X_EXP_ACC_ID---------------------------

    IF i.EXPENSE_ACCOUNT IS NOT NULL THEN

        BEGIN
            SELECT CODE_COMBINATION_ID
                        INTO   X_EXP_ACC_ID
                        FROM   gl_code_combinations
                        WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7  = i.EXPENSE_ACCOUNT
                        AND    enabled_flag          = 'Y'
                        AND    summary_flag          = 'N'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));

        EXCEPTION
                       WHEN NO_DATA_FOUND THEN
                                fnd_file.put_line (fnd_file.LOG,'Code combination ID does not exists for the given EXPENSE_ACCOUNT -'||i.EXPENSE_ACCOUNT);

                        WHEN OTHERS THEN
                               fnd_file.put_line (fnd_file.LOG,'Code combination ID does not exists for the given EXPENSE_ACCOUNT -'||i.EXPENSE_ACCOUNT);

                 END;

    END IF;

    x_debug_point:= 37;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'After Ftech the value of X_EXP_ACC_ID '|| ' at debug point : ' ||x_debug_point||'-'||X_EXP_ACC_ID);

--------------------------- Fetch the value of ASSET_CATEGORY_ID---------------------------
/****
    IF i.ASSET_CATEGORY_CODE IS NOT NULL THEN

        BEGIN
            SELECT CATEGORY_ID
                    INTO   X_ASSET_CATEGORY_ID
                        FROM   fa_categories_b
                    WHERE UPPER(SEGMENT1)||'.'||UPPER(SEGMENT2)||'.'||UPPER(SEGMENT3)||'.'||UPPER(SEGMENT4)  = UPPER(I.ASSET_CATEGORY_CODE)
                    AND    enabled_flag          = 'Y'
                    AND    summary_flag          = 'N'
                AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));

        EXCEPTION
                       WHEN NO_DATA_FOUND THEN
                                fnd_file.put_line (fnd_file.LOG,'CATEGORY_ID does not exists for the given ASSET_CATEGORY_CODE -'||i.ASSET_CATEGORY_CODE);

                        WHEN OTHERS THEN
                               fnd_file.put_line (fnd_file.LOG,'CATEGORY_IDdoes not exists for the given ASSET_CATEGORY_CODE -'||i.ASSET_CATEGORY_CODE);

            END;
    ELSE
        X_ASSET_CATEGORY_ID := NULL;

    END IF;

--------------------------- Fetch the value of INVENTORY_PLANNING_CODE---------------------------

    IF I.INVENTORY_PLANNING_CODE IS NOT NULL THEN

        BEGIN
            SELECT lookup_code
                      INTO   x_inv_plan_code
                      FROM   fnd_lookup_values_vl lvl
                      WHERE  UPPER(TRIM(lvl.lookup_type)) = UPPER(TRIM('MTL_MATERIAL_PLANNING'))
                      AND    UPPER(TRIM(lvl.MEANING)) = UPPER(TRIM(I.INVENTORY_PLANNING_CODE))
                     AND    ENABLED_FLAG='Y'
                      AND    SYSDATE BETWEEN NVL(lvl.START_DATE_ACTIVE, SYSDATE-1) AND NVL(lvl.END_DATE_ACTIVE, SYSDATE+1);

        EXCEPTION
                       WHEN NO_DATA_FOUND THEN
                                fnd_file.put_line (fnd_file.LOG,'INVENTORY_PLAN_CODE does not exists for the given INVENTORY_PLANNING_CODE -'||i.INVENTORY_PLANNING_CODE);

                        WHEN OTHERS THEN
                               fnd_file.put_line (fnd_file.LOG,'INVENTORY_PLAN_CODE does not exists for the given INVENTORY_PLANNING_CODE -'||i.INVENTORY_PLANNING_CODE);

        END;

    END IF;
****/
--------------------------- Fetch the value of X_SALES_ACC_ID---------------------------

    IF i.SALES_ACCOUNT IS NOT NULL THEN

        BEGIN
            SELECT CODE_COMBINATION_ID
                        INTO   X_SALES_ACC_ID
                        FROM   gl_code_combinations
                        WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7  = i.SALES_ACCOUNT
                        AND    enabled_flag          = 'Y'
                        AND    summary_flag          = 'N'
            AND    TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));

        EXCEPTION
                       WHEN NO_DATA_FOUND THEN

                                fnd_file.put_line (fnd_file.LOG,'Code combination ID does not exists for the given SALES_ACCOUNT -'||i.SALES_ACCOUNT);

                        WHEN OTHERS THEN

                           fnd_file.put_line (fnd_file.LOG,'Code combination ID does not exists for the given SALES_ACCOUNT -'||i.SALES_ACCOUNT);

                END;

    ELSE
        X_SALES_ACC_ID := NULL;

    END IF;

--------------------------- Fetch the value of x_item_type_code ---------------------------

        IF i.ITEM_TYPE IS NOT NULL THEN

        BEGIN

                  SELECT lookup_code
                  INTO   x_item_type_code
                  FROM   FND_COMMON_LOOKUPS
                  WHERE  lookup_type ='ITEM_TYPE'
          AND enabled_flag = 'Y'
                  AND    UPPER(TRIM(MEANING)) = UPPER(TRIM(i.ITEM_TYPE))
          AND    SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE, SYSDATE+1);

             EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                       fnd_file.put_line (fnd_file.LOG,'Item Type does not exists -'||i.ITEM_TYPE);
                   WHEN OTHERS THEN
                       fnd_file.put_line (fnd_file.LOG,'Invalid Item Type -'||i.ITEM_TYPE);
             END;

    END IF;

--------------------------- Fetch the value of X_srl_num_control_code ---------------------------
/****
        IF i.SERIAL_NUM_GENERATION IS NOT NULL THEN

        BEGIN

                  SELECT TO_NUMBER(LOOKUP_CODE)
                  INTO   x_srl_num_control_code
                  FROM   FND_LOOKUP_VALUES_VL
                  WHERE  LOOKUP_TYPE = 'MTL_SERIAL_NUMBER'
          AND      ENABLED_FLAG = 'Y'
                  AND    UPPER(TRIM(MEANING)) = UPPER(TRIM(i.SERIAL_NUM_GENERATION))
          AND    SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE, SYSDATE+1);

             EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                       fnd_file.put_line (fnd_file.LOG,'Item Serial Number Generation code does not exists -'||i.SERIAL_NUM_GENERATION);
                   WHEN OTHERS THEN
                       fnd_file.put_line (fnd_file.LOG,'Invalid Item Serial Number Generation code -'||i.SERIAL_NUM_GENERATION);
             END;

    END IF;

--------------------------- Fetch the value of x_receiving_routing_id ---------------------------

        IF i.RECEIVING_ROUTING IS NOT NULL THEN

        BEGIN

                  SELECT TO_NUMBER(LOOKUP_CODE)
                  INTO   x_receiving_routing_id
                  FROM   FND_LOOKUP_VALUES_VL
                  WHERE  LOOKUP_TYPE = 'RCV_ROUTING_HEADERS'
          AND      ENABLED_FLAG = 'Y'
                  AND    UPPER(TRIM(MEANING)) = UPPER(TRIM(i.RECEIVING_ROUTING))
          AND    SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE, SYSDATE+1);

             EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                       fnd_file.put_line (fnd_file.LOG,'Receipt Routing code does not exists -'||i.RECEIVING_ROUTING);
                   WHEN OTHERS THEN
                       fnd_file.put_line (fnd_file.LOG,'Invalid Receipt Routing code -'||i.RECEIVING_ROUTING);
             END;

    END IF;


--------------------------- Fetch the value of x_source_type ---------------------------

        IF i.SOURCE_TYPE IS NOT NULL THEN

        BEGIN

                  SELECT TO_NUMBER(LOOKUP_CODE)
                  INTO   x_source_type
                  FROM   FND_LOOKUP_VALUES_VL
                  WHERE  LOOKUP_TYPE = 'MTL_SOURCE_TYPES'
          AND      ENABLED_FLAG = 'Y'
                  AND    UPPER(TRIM(MEANING)) = UPPER(TRIM(i.SOURCE_TYPE))
          AND    SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE-1) AND NVL(END_DATE_ACTIVE, SYSDATE+1);

             EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                       fnd_file.put_line (fnd_file.LOG,'Source Type does not exists -'||i.SOURCE_TYPE);
                   WHEN OTHERS THEN
                       fnd_file.put_line (fnd_file.LOG,'Invalid Source Type -'||i.SOURCE_TYPE);
             END;

    END IF;


    x_debug_point:= 444;
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Inserting the records into mtl_system_items_interface  '|| ' at debug point : ' ||x_debug_point);

--------------------------- Fetch the value of x_src_organization_id ---------------------------

              BEGIN
                 SELECT organization_id
                   INTO x_src_organization_id
                   FROM ORG_ORGANIZATION_DEFINITIONS
                  WHERE organization_code = i.SOURCE_ORGANIZATION;

            EXCEPTION
                  WHEN OTHERS THEN
            fnd_file.put_line (fnd_file.LOG,'organization_id is not defined for the given organization_code -'||i.ORGANIZATION_CODE);

              END;
****/
-------------------------Inserting into mtl_system_items_interface table--------------------

FOR v1 IN c1(i.item_number)
LOOP

                 SELECT organization_code
                   INTO x_organization_code
                   FROM ORG_ORGANIZATION_DEFINITIONS
                  WHERE organization_id = v1.ORGANIZATION_ID;

         INSERT INTO mtl_system_items_interface
                              (organization_id,
                               organization_code,
                               last_update_date,
                               last_updated_by,
                               creation_date,
                               created_by,
                               last_update_login,
                   description,
                               segment1,
    ----                           primary_uom_code,
    ----                           primary_unit_of_measure,
    ----                           item_number,
                               item_type,
                 -- TEMPLATE_NAME,
    /****                TEMPLATE_ID,
                ATTRIBUTE_CATEGORY,
                ATTRIBUTE1,
                ATTRIBUTE2,
                ATTRIBUTE3,****/
                COST_OF_SALES_ACCOUNT,
  ----              LIST_PRICE_PER_UNIT,
                ENCUMBRANCE_ACCOUNT,
                EXPENSE_ACCOUNT,
   /****             ASSET_CATEGORY_ID,
                INVENTORY_PLANNING_CODE,
                MIN_MINMAX_QUANTITY,
                MAX_MINMAX_QUANTITY,
                MINIMUM_ORDER_QUANTITY,
                MAXIMUM_ORDER_QUANTITY,
                SOURCE_TYPE,
                SOURCE_ORGANIZATION_ID,
                SOURCE_SUBINVENTORY,
                PLANNER_CODE,
                TAX_CODE,
        PURCHASING_TAX_CODE,               ----Added by Hassan to populate Input Tax code in Item Master Screen
    ****/            SALES_ACCOUNT,
   /****             SERIAL_NUMBER_CONTROL_CODE,
                RECEIVING_ROUTING_ID,
                INVENTORY_ITEM_FLAG,
                STOCK_ENABLED_FLAG,
                MTL_TRANSACTIONS_ENABLED_FLAG,
                SO_TRANSACTIONS_FLAG,
                CYCLE_COUNT_ENABLED_FLAG,
                COSTING_ENABLED_FLAG,
                INVENTORY_ASSET_FLAG,
                DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
                PURCHASING_ITEM_FLAG,
                PURCHASING_ENABLED_FLAG,
                CUSTOMER_ORDER_FLAG,
                CUSTOMER_ORDER_ENABLED_FLAG,
                SHIPPABLE_ITEM_FLAG,
                INTERNAL_ORDER_FLAG,
                INTERNAL_ORDER_ENABLED_FLAG,
                RETURNABLE_FLAG,
                INVOICEABLE_ITEM_FLAG,
                INVOICE_ENABLED_FLAG, ****/
                set_process_id,
                process_flag,
                transaction_type
                               )
                         VALUES(v1.organization_id,--x_organization_id,
                                 x_organization_code,  --i.organization_code,
                                  SYSDATE,
                                  g_user_id,
                                  SYSDATE,
                                  g_user_id,
                                  g_login_id,
                                  i.description,
                                  i.ITEM_NUMBER,
     ----                 X_pri_uom_code,
     ----           I.primary_unit_of_measure,
      ----              I.ITEM_NUMBER,
                 -- i.item_type,
                x_item_type_code,
                 -- I.TEMPLATE_NAME,
        /****        x_template_id,
                I.ATTRIBUTE_CATEGORY,
                I.ATTRIBUTE1,
                I.ATTRIBUTE2,
                I.ATTRIBUTE3,****/
                X_COST_OF_SALES_ACC_ID,
----                ROUND(I.LIST_PRICE_PER_UNIT,2),
                X_ENCUMB_ACC_ID,
                X_EXP_ACC_ID,
    /****            X_ASSET_CATEGORY_ID,
                x_inv_plan_code,
                I.MIN_MINMAX_QUANTITY,
                I.MAX_MINMAX_QUANTITY,
                I.MINIMUM_ORDER_QUANTITY,
                I.MAXIMUM_ORDER_QUANTITY,
                 -- I.SOURCE_TYPE,
                x_source_type,
                 -- I.SOURCE_ORGANIZATION_ID,
                x_src_organization_id,
                I.SOURCE_SUBINVENTORY,
                I.PLANNER_CODE,
                I.TAX_CODE,
        I.TAX_CODE,****/
                X_SALES_ACC_ID,
/****                x_srl_num_control_code,
                x_receiving_routing_id,
                I.INVENTORY_ITEM_FLAG,
                I.STOCK_ENABLED_FLAG,
                I.MTL_TRANSACTIONS_ENABLED_FLAG,
                I.SO_TRANSACTIONS_FLAG,
                I.CYCLE_COUNT_ENABLED_FLAG,
                I.COSTING_ENABLED_FLAG,
                I.INVENTORY_ASSET_FLAG,
                I.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
                I.PURCHASING_ITEM_FLAG,
                I.PURCHASING_ENABLED_FLAG,
                I.CUSTOMER_ORDER_FLAG,
                I.CUSTOMER_ORDER_ENABLED_FLAG,
                I.SHIPPABLE_ITEM_FLAG,
                I.INTERNAL_ORDER_FLAG,
                I.INTERNAL_ORDER_ENABLED_FLAG,
                I.RETURNABLE_FLAG,
                I.INVOICEABLE_ITEM_FLAG,
                I.INVOICE_ENABLED_FLAG,****/
                                1,
                1,
               'UPDATE');
            ----    'CREATE');

END LOOP;
    x_debug_point:= 666;
        FND_FILE.PUT_LINE(FND_FILE.LOG, 'After Inserting the records into mtl_system_items_interface  '|| ' at debug point : ' ||x_debug_point);
/****
    FOR ic IN c_insert_item_catg (I.BATCH_NAME, I.ITEM_NUMBER) LOOP

        x_cat_set_id        := NULL;
        x_structure_id        := NULL;
        x_cat_id        := NULL;

--------------------------- Fetch the value of x_cat_set_id---------------------------

        BEGIN
                     SELECT  category_set_id,structure_id
                     INTO   x_cat_set_id, x_structure_id
                     FROM    mtl_category_sets
                     WHERE   category_set_name = ic.CATEGORY_SET_NAME;

             EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                        fnd_file.put_line (fnd_file.LOG,'category_set_id does not exists for the given CATEGORY_SET_NAME -'||ic.CATEGORY_SET_NAME);

                 WHEN OTHERS THEN
                   fnd_file.put_line (fnd_file.LOG,'category_set_id does not exists for the given CATEGORY_SET_NAME -'||ic.CATEGORY_SET_NAME);

                END;


--------------------------- Fetch the value of x_cat_id---------------------------

        BEGIN
****/
/***********************************************************************************************************************************************
               SELECT category_id
               INTO x_cat_id
               FROM mtl_categories_b
               WHERE UPPER(segment1)||'.'||UPPER(segment2) ||'.'||UPPER(segment3)||'.'||UPPER(segment4) = UPPER(TRIM(ic.category_name))
               AND  structure_id = x_structure_id;
***********************************************************************************************************************************************/
/****
            SELECT category_id
               INTO x_cat_id
               FROM mtl_categories_kfv
               WHERE UPPER(concatenated_segments) = UPPER(ic.category_name)
--               WHERE UPPER(segment1)||'.'||UPPER(segment2) ||'.'||UPPER(segment3)||'.'||UPPER(segment4) LIKE UPPER(TRIM(i.category_name))
               AND  structure_id = x_structure_id;

             EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                            fnd_file.put_line (fnd_file.LOG,'category_id does not exists for the given CATEGORY_SET_NAME -'||ic.CATEGORY_SET_NAME);
                     WHEN OTHERS THEN
                            fnd_file.put_line (fnd_file.LOG,'category_id does not exists for the given CATEGORY_SET_NAME -'||ic.CATEGORY_SET_NAME);
             END;


-------------------------Inserting into mtl_item_categories_interface table--------------------

                    INSERT INTO mtl_item_categories_interface
                           (inventory_item_id,
                            category_set_id,
                            category_id,
                            last_update_date,
                            last_updated_by,
                            creation_date,
                            created_by,
                            last_update_login,
                        --  request_id,
                        --  program_application_id,
                        --  program_id,
                        --  program_update_date,
                            organization_id,
                            transaction_id,
                        --  category_set_name,
                        --  category_name,
                            organization_code,
                            item_number,
                            transaction_type,
                            process_flag,
                            set_process_id
                           )
                    VALUES (NULL,
                            x_cat_set_id,
                            x_cat_id,
                            SYSDATE,
                            g_user_id,
                            SYSDATE,
                            g_user_id,
                            g_login_id,
                         -- NULL,
                         -- NULL,
                         -- NULL,
                         -- NULL,
                            x_organization_id,
                            NULL,
                         -- i.category_set_name,
                         -- i.category_name,
                            i.organization_code,
                            i.item_number,
                            'CREATE',
                            1,
                            1);


        x_debug_point:= 777;
            FND_FILE.PUT_LINE(FND_FILE.LOG,'After Inserting the records into mtl_item_categories_interface  '|| ' at debug point : ' ||x_debug_point);

     END LOOP; -- End of Item Category Loop
****/
         UPDATE    ZNinv_mtl_system_items_b_stg
     SET       PROCESS_STATUS ='C'
--       WHERE     ROWID   = i.msirowid;
--     WHERE CURRENT OF c_insert_item;
     WHERE ITEM_NUMBER = I.ITEM_NUMBER
     AND   BATCH_NAME  = I.BATCH_NAME;


         COMMIT;

    END LOOP; -- End of Item Loop

END ZNinv_item_insert_proc;


----------------------------------Main Procedure----------------------------------

    PROCEDURE ZNINV_ITEM_CONV_MAIN_PROC (
               errbuf       OUT      VARCHAR2,
               retcode      OUT      VARCHAR2
               ) AS
    BEGIN

     -- Call the  validate procedure
     ZNinv_item_validate_proc;
     -- Call the Insert Procedure
     ZNinv_item_insert_proc;
     fnd_file.put_line(fnd_file.OUTPUT,'Number of Records found for items  : '||g_item_found);
     fnd_file.put_line(fnd_file.OUTPUT,'Number of Records processed for items : '||g_item_processed);
     fnd_file.put_line(fnd_file.OUTPUT,'Number of Records rejected  for items : '||g_item_rejected);

     END ZNINV_ITEM_CONV_MAIN_PROC;

END ZNINV_ITEM_CONV_PKG_UP;
/

Post a Comment