Tuesday, April 26, 2016

Maximum Number of record Per page using XML Publisher with RTF template


You limit the number of lines per page when you use the xsl commands like this in your template:
<xsl:variable name="lpp" select="number(13)"/>
<?for-each@section:LIST_G_INVOICE?>
<xsl:variable xdofo:ctx="incontext" name="invLines" select=".//G_LINES[LINE_TYPE='LINE']"/>
<?for-each:$invLines?> <?if:(position()-1) mod $lpp=0?> <xsl:variable name="start" xdofo:ctx="incontext" select="position()"/>

and then you have the table where you have the data
<?for-each:$invLines?><?if:position()>=$start and position()<$start+$lpp?>
and all your lines
and then
<?end if?><?end for-each?>

    Sunday, April 24, 2016

    Sales order conversion

    create or replace procedure XX_SO_CONV_PROC_SAN(ERRBUF VARCHAR2,RETCODE VARCHAR2) is
    
       cursor C1 is select * from XXOM_SALESO_TEMP_BIN;
    
       l_header_rec OE_ORDER_PUB.Header_Rec_Type;
       l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
       l_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
       l_header_adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
       l_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_Type;
       l_header_scr_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
       l_line_scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
       l_request_rec OE_ORDER_PUB.Request_Rec_Type ;
       l_return_status      VARCHAR2 ( 1000 ) ;
       l_msg_count          NUMBER;
       l_msg_data           VARCHAR2 ( 1000 ) ;
       p_api_version_number NUMBER          :=1.0;
       p_init_msg_list      VARCHAR2 ( 10 ) := FND_API.G_FALSE;
       p_return_values      VARCHAR2 ( 10 ) := FND_API.G_FALSE;
       p_action_commit      VARCHAR2 ( 10 ) := FND_API.G_FALSE;
       x_return_status      VARCHAR2 ( 1 ) ;
       x_msg_count          NUMBER;
       x_msg_data           VARCHAR2 ( 100 ) ;
       x_header_rec OE_ORDER_PUB.Header_Rec_Type                             := OE_ORDER_PUB.G_MISS_HEADER_REC;
       p_old_header_rec OE_ORDER_PUB.Header_Rec_Type                         := OE_ORDER_PUB.G_MISS_HEADER_REC;
       p_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type                     := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
       p_old_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type                 := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
       p_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type                     := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
       p_old_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type                 := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
       p_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type             := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
       p_old_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
       p_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
       p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type     := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
       p_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type             := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
       p_old_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
       p_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
       p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type     := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
       p_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type             := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
       p_old_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type         := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
       p_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type     := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
       p_old_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
       p_line_tbl OE_ORDER_PUB.Line_Tbl_Type                                 := OE_ORDER_PUB.G_MISS_LINE_TBL;
       p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type                             := OE_ORDER_PUB.G_MISS_LINE_TBL;
       p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type                         := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
       p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type                     := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
       p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type                         := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
       p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type                     := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
       p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type                 := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
       p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
       p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
       p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type         := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
       p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type                 := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
       p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
       p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
       p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type         := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
       p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type                 := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
       p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type             := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
       p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type         := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
       p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type     := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
       p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type                     := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
       p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type                 := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
       p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type             := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
       p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type         := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
       p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type                    := OE_ORDER_PUB.G_MISS_REQUEST_TBL;
       x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
       x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
       x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
       x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
       x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
       x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
       x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
       x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
       x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
       x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
       x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
       x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
       x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
       x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
       x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
       x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
       x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
       x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
       x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
       X_DEBUG_FILE     VARCHAR2 ( 100 ) ;
       l_line_tbl_index NUMBER;
       l_msg_index_out  NUMBER ( 10 ) ;
    BEGIN
      
       fnd_global.apps_initialize ( FND_GLOBAL.USER_ID,FND_GLOBAL.RESP_ID,FND_GLOBAL.RESP_APPL_ID ) ; -- pass in user_id, responsibility_id, and application_id
       MO_GLOBAL.INIT ( 'ONT' ) ;                      
       mo_global.set_org_context ( fnd_global.org_id, NULL, 'ONT' ) ;
       fnd_global.set_nls_context ( 'AMERICAN' ) ;
       MO_GLOBAL.SET_POLICY_CONTEXT ( 'S', fnd_global.org_id) ; -- Required for R12
       oe_msg_pub.initialize;
       
       fnd_file.put_line (fnd_file.log, 'START OF NEW DEBUG' ) ;
       
       --This is to CREATE an order header and an order line
       --Create Header record
       --Initialize header record to missing
        FOR i IN C1 LOOP
    
       l_header_rec                         := OE_ORDER_PUB.G_MISS_HEADER_REC;
       l_header_rec.TRANSACTIONAL_CURR_CODE := i.TRANSACTIONAL_CURR_CODE;
       l_header_rec.pricing_date            := SYSDATE;
       l_header_rec.invoice_to_org_id        := i.invoice_to_org_id;
       l_header_rec.created_by                  :=FND_GLOBAL.user_id;
       l_header_rec.creation_date              :=sysdate;
       l_header_rec.last_updated_by          :=FND_GLOBAL.user_id;
       l_header_rec.last_update_date         :=sysdate;
       l_header_rec.last_update_login         :=FND_GLOBAL.user_id;
       l_header_rec.order_category_code    := i.order_category;
       l_header_rec.ordered_date               := i.ordered_date;
       l_header_rec.order_source_id           := i.order_source_id;
       l_header_rec.sold_to_org_id          := i.SOLD_TO_ORG_ID;
       l_header_rec.sold_from_org_id        :=i.sold_from_org_id;
       l_header_rec.price_list_id           := i.price_list_id;--1000;
       l_header_rec.ordered_date            := SYSDATE;
       l_header_rec.shipping_method_code    := 'DHL';
       l_header_rec.ship_from_org_id        := i.ship_from_org_id;
       l_header_rec.ship_to_org_id          := i.ship_to_org_id;
       l_header_rec.salesrep_id             := i.salesrep_id;
       l_header_rec.order_type_id           := i.order_type_id;
       l_header_rec.payment_term_id         :=i.payment_term_id;
       l_header_rec.operation               := OE_GLOBALS.G_OPR_CREATE;
       
       -- FIRST LINE RECORD
       -- Initialize record to missing
    
       l_line_tbl (1) := OE_ORDER_PUB.G_MISS_LINE_REC;
    
       -- Line attributes
       l_line_tbl(1).line_type_id := i.line_type_id; 
       l_line_tbl (1).inventory_item_id := i.inventory_item_id;
       l_line_tbl (1).ordered_quantity  := i.ordered_quantity;
       l_line_tbl (1).ship_from_org_id  := i.ship_from_org_id;
       l_line_tbl(1).ship_to_org_id := i.ship_to_org_id;
       l_line_tbl(1).sold_to_org_id := i.sold_to_org_id;
       l_line_tbl(1).sold_from_org_id := i.sold_from_org_id;
       l_line_tbl (1).unit_selling_price  := i.unit_selling_price;
       l_line_tbl (1).unit_list_price  := i.unit_list_price;
       l_line_tbl (1).order_source_id := i.order_source_id;
       l_line_tbl(1).orig_sys_document_ref := i.orig_sys_document_ref;
       l_line_tbl(1).orig_sys_line_ref := i.orig_sys_line_ref;   
       l_line_tbl (1).calculate_price_flag  := 'Y';
       l_line_tbl (1).operation := OE_GLOBALS.G_OPR_CREATE;
       l_line_tbl(1).created_by :=FND_GLOBAL.user_id;
       l_line_tbl(1).creation_date :=sysdate;
       l_line_tbl(1).last_updated_by :=FND_GLOBAL.user_id;
       l_line_tbl(1).last_update_date :=sysdate;
       l_line_tbl(1).last_update_login         :=FND_GLOBAL.user_id;
       l_line_tbl_index := 1;
       l_action_request_tbl(1).request_type := oe_globals.g_book_order;
       l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
      
       -- CALL TO PROCESS ORDER Check the return status and then commit.
      
     OE_ORDER_PUB.process_order ( p_api_version_number => 1.0, 
                                    p_init_msg_list => fnd_api.g_false, 
                                    p_return_values => fnd_api.g_false, 
                                    p_action_commit => fnd_api.g_false, 
                                    x_return_status => l_return_status, 
                                    x_msg_count => l_msg_count, 
                                    x_msg_data => l_msg_data, 
                                    p_header_rec => l_header_rec, 
                                    p_line_tbl => l_line_tbl, 
                                    p_action_request_tbl => l_action_request_tbl
                                    -- OUT PARAMETERS   
                                    , x_header_rec => x_header_rec
                                    , x_header_val_rec => x_header_val_rec
                                    , x_Header_Adj_tbl => x_Header_Adj_tbl
                                    , x_Header_Adj_val_tbl => x_Header_Adj_val_tbl
                                    , x_Header_price_Att_tbl => x_Header_price_Att_tbl
                                    , x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl
                                    , x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl
                                    , x_Header_Scredit_tbl => x_Header_Scredit_tbl
                                    , x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl
                                    , x_line_tbl => p_line_tbl
                                    , x_line_val_tbl => x_line_val_tbl
                                    , x_Line_Adj_tbl => x_Line_Adj_tbl
                                    , x_Line_Adj_val_tbl => x_Line_Adj_val_tbl
                                    , x_Line_price_Att_tbl => x_Line_price_Att_tbl
                                    , x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
                                    , x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl
                                    , x_Line_Scredit_tbl => x_Line_Scredit_tbl
                                    , x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl
                                    , x_Lot_Serial_tbl => x_Lot_Serial_tbl
                                    , x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl
                                    , x_action_request_tbl => x_action_request_tbl 
                                    ) ;
      
       end loop;
       fnd_file.put_line (fnd_file.log,'Header_ID : '||x_header_rec.header_id);
       -- Retrieve messages
       FOR i IN 1 .. l_msg_count
       LOOP
          Oe_Msg_Pub.get ( p_msg_index => i, p_encoded => Fnd_Api.G_FALSE, p_data => l_msg_data, p_msg_index_out => l_msg_index_out ) ;
          fnd_file.put_line (fnd_file.log,'message is: ' || l_msg_data ) ;
          fnd_file.put_line (fnd_file.log,'message index is: ' || l_msg_index_out ) ;
       END LOOP;
       -- Check the return status
       IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
          fnd_file.put_line (fnd_file.log,'Process Order Sucess' ) ;
          COMMIT;
       ELSE
          fnd_file.put_line (fnd_file.log,'Failed' ) ;
       END IF;
    EXCEPTION
       WHEN OTHERS
       THEN
          fnd_file.put_line (fnd_file.log, 'Error: '||SQLERRM ) ;   
    
    END XX_SO_CONV_PROC_SAN;
    
    
    
    
    --exec SO_CONV2;
     
     
     

    Receipt Conversion

    create or replace PROCEDURE XXCREATE_CASH(ERRBUT OUT VARCHAR2,RETCODE VARCHAR2)
    AS
               -- Standard API parameters.
                     x_p_api_version        NUMBER;
                     x_p_init_msg_list      VARCHAR2(240) := FND_API.G_FALSE;
                     x_p_commit             VARCHAR2(240) := FND_API.G_FALSE;
                     x_p_validation_level   NUMBER   := FND_API.G_VALID_LEVEL_FULL;
    --               X_p_usr_currency_code         VARCHAR2(240) DEFAULT NUL; --the translated currency code
                     x_p_currency_code             VARCHAR2(240) DEFAULT NULL;
    --                 p_usr_exchange_rate_type    VARCHAR2(240) DEFAULT NULL;
    --                 p_exchange_rate_type        VARCHAR2(240) DEFAULT NULL;
    --                 p_exchange_rate             NUMBER   DEFAULT NULL;
    --                 p_exchange_rate_date        DATE     DEFAULT NULL;
                     x_p_amount                    NUMBER;
    --                 p_factor_discount_amount    NUMBER DEFAULT NULL;
                     x_p_receipt_number           VARCHAR2(100);
                     x_p_receipt_date              DATE     DEFAULT SYSDATE;
                     x_p_gl_date                   DATE     DEFAULT SYSDATE;
                     x_p_maturity_date             DATE     DEFAULT NULL;
                     x_p_postmark_date             DATE     DEFAULT NULL;
                     x_p_customer_id              NUMBER ;
                     x_p_customer_name             VARCHAR2(240);
                     x_p_customer_number          VARCHAR2(100);
                     x_p_customer_bank_account_id  NUMBER ;
                     x_p_customer_bank_account_num     VARCHAR2(240);
                     x_p_customer_bank_account_name    VARCHAR2(240)  DEFAULT NULL;
                     x_p_payment_trxn_extension_id    NUMBER DEFAULT NULL; --payment uptake changes bichatte
    --                 p_location                   VARCHAR2(240) DEFAULT NULL;
                     x_p_customer_site_use_id     NUMBER;
                     x_p_customer_site_use VARCHAR2(240) DEFAULT NULL;
                     x_p_default_site_use          VARCHAR2(240) DEFAULT  'Y'; --bug4448307-4509459
    --                 p_customer_receipt_reference   VARCHAR2(240)  DEFAULT NULL;
    --                 p_override_remit_account_flag   VARCHAR2(240) DEFAULT NULL;
                     x_p_remittance_bank_acct_id    NUMBER ;
                     x_p_remittance_bank_acct_num   VARCHAR2(240) DEFAULT NULL;
                     x_p_remittance_bank_acct_name  VARCHAR2(240) DEFAULT NULL;
                     x_p_deposit_date               DATE     DEFAULT SYSDATE;
                     x_p_receipt_method_id          NUMBER ;
                     x_p_receipt_method_name        VARCHAR2(240) DEFAULT NULL;
                     x_p_doc_sequence_value         NUMBER   DEFAULT NULL;
                     x_p_ussgl_transaction_code     VARCHAR2(240) DEFAULT NULL;
                     x_p_anticipated_clearing_date  DATE     DEFAULT NULL;
                     x_p_called_from                VARCHAR2(240) DEFAULT NULL;
                    x_p_attribute_rec           attribute_rec_type DEFAULT attribute_rec_const;
           -- ******* Global Flexfield parameters *******
                    x_p_global_attribute_rec   global_attribute_rec_type DEFAULT global_attribute_rec_const;
                     x_p_comments              VARCHAR2(240);
          --   ***  Notes Receivable Additional Information  ***
                     x_p_issuer_name                   VARCHAR2(240)  DEFAULT NULL;
                     x_p_issue_date                    DATE   DEFAULT SYSDATE;
                     x_p_issuer_bank_branch_id         NUMBER  DEFAULT NULL;
                     x_p_org_id                        NUMBER ;
                     X_p_installment                   NUMBER  DEFAULT NULL;
          --   ** OUT NOCOPY variables
                     x_p_cr_id     NUMBER;
    BEGIN
    fnd_global.apps_initialize(1318,50559,222);
    mo_global.init('AR');
    AR_RECEIPT_API_PUB.CREATE_CASH
    (
     p_api_version     => '1.0',
            p_init_msg_list => x_p_init_msg_list,
            p_commit => x_p_commit,
            p_validation_level => x_p_validation_level,
            p_currency_code => 'USD',
            p_amount => '900',             
            p_receipt_number => '1230',
            p_receipt_date => x_p_receipt_date,            
            p_gl_date =>x_p_gl_date,
            p_maturity_date =>x_p_maturity_date,
            p_postmark_date  =>x_p_postmark_date,
            p_customer_id => '1004',
            p_customer_name=> 'Hilman and Associates',
            p_customer_number=> '1230',
            p_customer_bank_account_id=> '10010' ,
            p_customer_bank_account_num => '1230',
            p_customer_bank_account_name=> x_p_customer_bank_account_name,
            p_payment_trxn_extension_id => x_p_payment_trxn_extension_id, 
            p_customer_site_use_id => '1017',
            p_customer_site_use => x_p_customer_site_use, 
            p_remittance_bank_account_id=>'10010'  ,
            p_remittance_bank_account_num => x_p_remittance_bank_acct_num  ,
            p_remittance_bank_account_name => x_p_remittance_bank_acct_name ,
            p_deposit_date  => x_p_deposit_date,
            p_receipt_method_id => '1001',
            p_receipt_method_name=> x_p_receipt_method_name,
            p_doc_sequence_value=> x_p_doc_sequence_value,
            p_ussgl_transaction_code=> x_p_ussgl_transaction_code,
            p_anticipated_clearing_date => x_p_anticipated_clearing_date ,
            p_called_from => x_p_called_from,
            p_attribute_rec=> x_p_attribute_rec,
            p_global_attribute_rec=> x_p_global_attribute_rec  ,
            p_comments => 'RITWIK TRANSACTION',
            p_issuer_name => x_p_issuer_name,
            p_issue_date  => x_p_issue_date,
            p_issuer_bank_branch_id  => x_p_issuer_bank_branch_id,
            p_org_id    => '204',
            p_installment => x_p_installment ,
            p_cr_id =>x_p_cr_id
    );
    COMMIT;
    END;
    /

    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;
    /

    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;
    /

    How to improve blog performance

    Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...