Item conversion

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

                                    );

END ZNINV_ITEM_CONVERSION_PKG;
/


CREATE OR REPLACE PACKAGE BODY APPS.ZNINV_ITEM_CONVERSION_PKG 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 
    -- ,RESERVABLE_TYPE                --- added for Niger   
         ,SO_TRANSACTIONS_FLAG        
         ,CYCLE_COUNT_ENABLED_FLAG    
         ,COSTING_ENABLED_FLAG        
         ,INVENTORY_ASSET_FLAG        
         ,DEFAULT_INCLUDE_IN_ROLLUP_FLAG
         ,PURCHASING_ITEM_FLAG        
         ,PURCHASING_ENABLED_FLAG
     ,ALLOW_ITEM_DESC_UPDATE_FLAG        --- added for Niger
     ,RFQ_REQUIRED_FLAG             --- added for Niger
     ,TAXABLE_FLAG                --- added for Niger
         ,PURCHASING_TAX_CODE            --- added for Niger
     ,RECEIPT_REQUIRED_FLAG            --- added for Niger
       ,UNIT_OF_ISSUE                --- added for Niger
       ,ALLOW_SUBSTITUTE_RECEIPTS_FLAG    --- added for Niger
         ,REPLENISH_TO_ORDER_FLAG        --- added for Niger                
         ,SERIAL_NUM_GENERATION        
         ,LIST_PRICE_PER_UNIT            
         ,COST_OF_SALES_ACCOUNT            
         ,ENCUMBRANCE_ACCOUNT            
         ,EXPENSE_ACCOUNT                
         ,SALES_ACCOUNT                
         ,ASSET_CATEGORY_CODE            
         ,RECEIVING_ROUTING
    -- ,ENFORCE_SHIP_TO_LOCATION_CODE        --- added for Niger
    --  ,WEIGHT_UOM_CODE            --- added for Niger
    --   ,UNIT_WEIGHT                --- added for Niger
    --   ,VOLUME_UOM_CODE            --- added for Niger
     ---  ,UNIT_VOLUME                --- added for Niger
    --   ,PLANNING_MAKE_BUY_CODE        --- added for Niger
    --   ,DEFAULT_SO_SOURCE_TYPE        --- added for Niger        
         ,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;
    ---AND   BATCH_NAME = P_BATCH;
--    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.TAX_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
    and organization_code = i.organization_code;
        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
    and organization_code = i.organization_code;
            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    
       --  ,RESERVABLE_TYPE                --- added for Niger   
         ,SO_TRANSACTIONS_FLAG        
         ,CYCLE_COUNT_ENABLED_FLAG    
         ,COSTING_ENABLED_FLAG        
         ,INVENTORY_ASSET_FLAG        
         ,DEFAULT_INCLUDE_IN_ROLLUP_FLAG
         ,PURCHASING_ITEM_FLAG        
         ,PURCHASING_ENABLED_FLAG
     ,ALLOW_ITEM_DESC_UPDATE_FLAG        --- added for Niger
     ,RFQ_REQUIRED_FLAG             --- added for Niger
     ,TAXABLE_FLAG                --- added for Niger
         ,PURCHASING_TAX_CODE            --- added for Niger
     ,RECEIPT_REQUIRED_FLAG            --- added for Niger
       ,UNIT_OF_ISSUE                --- added for Niger
       ,ALLOW_SUBSTITUTE_RECEIPTS_FLAG    --- added for Niger
         ,REPLENISH_TO_ORDER_FLAG        --- added for Niger                
         ,SERIAL_NUM_GENERATION        
         ,LIST_PRICE_PER_UNIT            
         ,COST_OF_SALES_ACCOUNT            
         ,ENCUMBRANCE_ACCOUNT            
         ,EXPENSE_ACCOUNT                
         ,SALES_ACCOUNT                
         ,ASSET_CATEGORY_CODE            
         ,RECEIVING_ROUTING
    -- ,ENFORCE_SHIP_TO_LOCATION_CODE        --- added for Niger
    --  ,WEIGHT_UOM_CODE            --- added for Niger
     --  ,UNIT_WEIGHT                --- added for Niger
     --  ,VOLUME_UOM_CODE            --- added for Niger
      -- ,UNIT_VOLUME                --- added for Niger
     --  ,PLANNING_MAKE_BUY_CODE        --- added for Niger
     --  ,DEFAULT_SO_SOURCE_TYPE        --- added for Niger          
         ,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
       -- ,MRP_PLANNING_CODE,ASSEMBLE_TO_ORDER_FLAG 
    FROM zninv_mtl_system_items_b_stg 
        WHERE NVL(PROCESS_STATUS,'X') ='I';
    ---and BATCH_NAME  = P_BATCH_NAME;
--    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;
------------------------------ 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--------------------
         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,
       -- RESERVABLE_TYPE,                --- added for Niger   
                SO_TRANSACTIONS_FLAG,
                CYCLE_COUNT_ENABLED_FLAG,
                COSTING_ENABLED_FLAG,
                INVENTORY_ASSET_FLAG,        
                DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
                PURCHASING_ITEM_FLAG,
                PURCHASING_ENABLED_FLAG,
        ALLOW_ITEM_DESC_UPDATE_FLAG,        --- added for Niger
         RFQ_REQUIRED_FLAG,             --- added for Niger
         TAXABLE_FLAG,                --- added for Niger
             PURCHASING_TAX_CODE,            --- added for Niger
         RECEIPT_REQUIRED_FLAG,            --- added for Niger
           UNIT_OF_ISSUE,                --- added for Niger
           ALLOW_SUBSTITUTE_RECEIPTS_FLAG,        --- added for Niger
        ---REPLENISH_TO_ORDER_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
      --  ENFORCE_SHIP_TO_LOCATION_CODE,        --- added for Niger
       --   WEIGHT_UOM_CODE,            --- added for Niger
        --   UNIT_WEIGHT,                --- added for Niger
        --   VOLUME_UOM_CODE,            --- added for Niger
        --   UNIT_VOLUME,                --- added for Niger
         --  PLANNING_MAKE_BUY_CODE,            --- added for Niger
         --  DEFAULT_SO_SOURCE_TYPE,            --- added for Niger
--MRP_PLANNING_CODE                                       ---Added for Niger
                               )
                         VALUES(x_organization_id,
                                   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.RESERVABLE_TYPE,        
                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.ALLOW_ITEM_DESC_UPDATE_FLAG,        
         I.RFQ_REQUIRED_FLAG,             
         I.TAXABLE_FLAG,                
             I.PURCHASING_TAX_CODE,            
         I.RECEIPT_REQUIRED_FLAG,        
           I.UNIT_OF_ISSUE,            
           I.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
       -- I.ASSEMBLE_TO_ORDER_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'
      --  I.ENFORCE_SHIP_TO_LOCATION_CODE,        
         -- I.WEIGHT_UOM_CODE,            
          -- I.UNIT_WEIGHT,                
         --  I.VOLUME_UOM_CODE,            
        --   I.UNIT_VOLUME,                
        --   I.PLANNING_MAKE_BUY_CODE,            
         --  I.DEFAULT_SO_SOURCE_TYPE,I.MRP_PLANNING_CODE        
);
    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,
                          ic.category_set_name,
                          ic.category_name,
                            ic.organization_code,
                            ic.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,
           P_BATCH              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_CONVERSION_PKG;
/

Post a Comment