Oracle R12 Inventory Item with HSN Code query

--Item Master with HSN

  SELECT msi.segment1 item_code,msi.description,

        primary_unit_of_measure uom,

        msi.auto_lot_alpha_prefix,msi.start_auto_lot_number,

        lot.lot_number,item_type,msi.inventory_item_status_code status_code,

        mic.category_concat_segs  Item_Classification

       ,mic.SEGMENT1,mic.SEGMENT3,mic.SEGMENT4

       ,(SELECT reporting_code

        FROM apps.jai_item_templ_hdr jith,

             apps.jai_reporting_associations jra,

             apps.jai_regimes jr

        WHERE     jith.template_hdr_id = jra.entity_id

              AND jra.reporting_usage = 'LR'--Legal Reporting

              AND jra.effective_to IS NULL

              AND jr.regime_id = jra.regime_id

              AND jr.regime_code = 'GST' ---Depen on setup

              AND jith.organization_id = msi.organization_id

              AND jith.inventory_item_id = msi.inventory_item_id

        GROUP BY reporting_code) HSN_CODE

        ,(SELECT reporting_code_description

        FROM apps.jai_item_templ_hdr jith,

             apps.jai_reporting_associations jra

        WHERE     jith.template_hdr_id = jra.entity_id

              AND jra.reporting_usage = 'TD' --Tax Determination 

              AND jra.effective_to IS NULL

              AND jith.organization_id = msi.organization_id

              AND jith.inventory_item_id = msi.inventory_item_id

        GROUP BY reporting_code_description) TAX_TYPE

    FROM mtl_system_items_b msi

        ,mtl_lot_numbers lot

        ,mtl_item_categories_v mic

   WHERE msi.organization_id=122 ---Master Org 

     AND (NVL (msi.enabled_flag, 'N') = NVL ('N', 'N') OR NVL ('N', 'N') = 'N')

   AND msi.inventory_item_id = lot.inventory_item_id(+)

   AND msi.organization_id = lot.organization_id(+)

   AND msi.inventory_item_id = mic.inventory_item_id(+)

   AND msi.organization_id = mic.organization_id(+)

Order by 1  

Oracle Inventory MTL Temporary Data Delete Script

--Oracle Inventory Material Transactions Temporary data Delete 

 --Check first 

SELECT * FROM MTL_TRANSACTIONS_TEMP_ALL_V; 

SELECT *

  FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT

 WHERE TRANSACTION_TEMP_ID = 6651466;

---Delete from temp

DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT

 WHERE TRANSACTION_TEMP_ID = 6651466;

 

COMMIT; 

 

Oracle R12 Item Update via API

 Oracle R12 Inventory Item Update through API - Sample for  SHIPPABLE_ITEM_FLAG update

--CREATE OR REPLACE PROCEDURE JG_ITEMS_UPDATE_API

--AS

DECLARE

   x_item_tbl                EGO_ITEM_PUB.ITEM_TBL_TYPE;

   x_message_list            Error_Handler.Error_Tbl_Type;

   x_return_status           VARCHAR2 (2);

   x_msg_count               NUMBER := 0;

   l_user_id                 NUMBER := -1;

   l_resp_id                 NUMBER := -1;

   l_application_id          NUMBER := -1;

   l_rowcnt                  NUMBER := 1;

   l_api_version             NUMBER := 1.0;

   l_init_msg_list           VARCHAR2 (2) := FND_API.G_TRUE;

   l_commit                  VARCHAR2 (2) := FND_API.G_FALSE;

   l_item_tbl                EGO_ITEM_PUB.ITEM_TBL_TYPE;

   l_role_grant_tbl          EGO_ITEM_PUB.ROLE_GRANT_TBL_TYPE;

   l_user_name               VARCHAR2 (30) := 'JGTECH';

   l_resp_name               VARCHAR2 (30) := 'India Local Inventory';

   l_item_catalog_group_id   NUMBER := 0;

   CURSOR C1

   IS

      SELECT 271 ORGANIZATION_ID,

             13018 INVENTORY_ITEM_ID,

--             '4946992' ITEM,

             'Y' SHIPPABLE_ITEM_FLAG

             /*PREPROCESSING_LEAD_TIME,

             FULL_LEAD_TIME,

             POSTPROCESSING_LEAD_TIME,

             FIXED_LEAD_TIME,

             VARIABLE_LEAD_TIME,

             CUM_MANUFACTURING_LEAD_TIME,

             CUMULATIVE_TOTAL_LEAD_TIME,

             BUYER,

             MRP_PLANNING_CODE,

             ATO_FORECAST_CONTROL,

             NVL(UPPER(END_ASSEMBLY_PEGGING_FLAG),'B')END_ASSEMBLY_PEGGING_FLAG,

             DECODE(UPPER(CREATE_SUPPLY_FLAG),'YES','Y','N')CREATE_SUPPLY_FLAG,

             PLANNER_CODE,

             PLANNING_MAKE_BUY_CODE,

             INVENTORY_PLANNING_CODE,

             MIN_MINMAX_QUANTITY,

             MAX_MINMAX_QUANTITY,

             DECODE(UPPER(BOM_ENABLED_FLAG),'YES','Y','N')BOM_ENABLED_FLAG,

             DECODE(UPPER(BUILD_IN_WIP_FLAG),'YES','Y','N')BUILD_IN_WIP_FLAG,

             DECODE(UPPER(PURCHASING_ENABLED_FLAG),'YES','Y','N')PURCHASING_ENABLED_FLAG,

             TRANSACTION_TYPE*/

        FROM DUAL; --XX_ITEM_UPDATE_STG;

BEGIN

   -- Get the user_id

   SELECT user_id

     INTO l_user_id

     FROM fnd_user

    WHERE user_name = l_user_name;

    

 FOR i IN C1 LOOP

 

   -- Get the application_id and responsibility_id

   SELECT application_id, responsibility_id

     INTO l_application_id, l_resp_id

     FROM fnd_responsibility_vl

    WHERE responsibility_name = l_resp_name

   --FND_GLOBAL.APPS_INITIALIZE (1380, 51037, 7000);

   

   FND_GLOBAL.APPS_INITIALIZE (l_user_id, l_resp_id, l_application_id);

   DBMS_OUTPUT.put_line (

         'Initialized applications context: '

      || l_user_id

      || ' '

      || l_resp_id

      || ' '

      || l_application_id);

   -- Load l_item_tbl with the data

   l_item_tbl (l_rowcnt).Organization_id := i.Organization_id;       -- Organization Id

   l_item_tbl (l_rowcnt).inventory_item_id := i.inventory_item_id;

   l_item_tbl (l_rowcnt).transaction_type := 'UPDATE';

--   l_item_tbl (l_rowcnt).Segment1 := i.item;             -- Item Number

   l_item_tbl (l_rowcnt).SHIPPABLE_ITEM_FLAG := i.SHIPPABLE_ITEM_FLAG;     -- Item Description

   --        l_item_tbl(l_rowcnt).Template_Name               := 'Finished Good';          -- Item template  (** should be associated to ICC, Not mandatory)

   -- call API to load Items

   DBMS_OUTPUT.PUT_LINE ('=====================================');

   DBMS_OUTPUT.PUT_LINE ('Calling EGO_ITEM_PUB.Process_Items API');

   EGO_ITEM_PUB.PROCESS_ITEMS (p_api_version      => l_api_version,

                               p_init_msg_list    => l_init_msg_list,

                               p_commit           => l_commit,

                               p_item_tbl         => l_item_tbl,

                               p_role_grant_tbl   => l_role_grant_tbl,

                               x_item_tbl         => x_item_tbl,

                               x_return_status    => x_return_status,

                               x_msg_count        => x_msg_count);

   DBMS_OUTPUT.PUT_LINE ('=====================================');

   DBMS_OUTPUT.PUT_LINE ('Return Status: ' || x_return_status);

   IF (x_return_status = FND_API.G_RET_STS_SUCCESS)

   THEN

      FOR i IN 1 .. x_item_tbl.COUNT

      LOOP

         DBMS_OUTPUT.PUT_LINE (

               'Inventory Item Id :'

            || TO_CHAR (x_item_tbl (i).inventory_item_id));

         DBMS_OUTPUT.PUT_LINE (

            'Organization Id   :' || TO_CHAR (x_item_tbl (i).organization_id));

      END LOOP;

   ELSE

      DBMS_OUTPUT.PUT_LINE ('Error Messages ::');

      Error_Handler.GET_MESSAGE_LIST (x_message_list => x_message_list);

      FOR i IN 1 .. x_message_list.COUNT

      LOOP

         DBMS_OUTPUT.PUT_LINE (x_message_list (i).MESSAGE_TEXT);

      END LOOP;

   END IF;

 END LOOP;

 COMMIT;

   DBMS_OUTPUT.PUT_LINE ('=====================================');

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE ('Exception Occured :');

      DBMS_OUTPUT.PUT_LINE (SQLERRM || ':' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

      DBMS_OUTPUT.PUT_LINE ('=====================================');

      RAISE;

END JG_ITEMS_UPDATE_API;

Oracle EBS R12 Item Master Query

--Oracle EBS R12 Active Item Master Query with Category, Organization and Buyer

SELECT msi.segment1 "Item Code",

       msi.description "Item Description",

       msi.primary_uom_code "UOM Code",

       misi.secondary_inventory_name "Sub Inventory",

       ood.organization_name "Inv Organization Name",

       mcv.segment1 "Categorie seg1", mcv.segment2 "Categorie seg2",         mcv.segment3 "Categorie seg3",

       msi.cycle_count_enabled_flag "Cycle Count",

       msi.inspection_required_flag "Inspection",

       msi.stock_enabled_flag "Stockable",

       msi.mtl_transactions_enabled_flag "Transactable",

       msi.lot_control_code "Lot Control",

       msi.location_control_code "Location",

       msi.purchasing_enabled_flag "Purchasable",

       msi.must_use_approved_vendor_flag "Use Approve Supplier List",

       --msi.buyer_id,

       pap.full_name "Buyer Name",

       msi.list_price_per_unit "List Price",

       msi.expense_account "Expense Account",

       msi.RESERVABLE_TYPE "Reservable",

       msi.PURCHASING_ITEM_FLAG "Purchased",

       msi.OUTSIDE_OPERATION_FLAG "OSP item",

       msi.OUTSIDE_OPERATION_UOM_TYPE "Unit Type",

       msi.RECEIVING_ROUTING_ID "Receive Routing",

       msi.INVENTORY_PLANNING_CODE "Inventory Planning method",

       msi.MIN_MINMAX_QUANTITY "Min Max(Minimum Qty)",

       msi.MAX_MINMAX_QUANTITY "Min Max(Maximum Qty)",

       msi.MINIMUM_ORDER_QUANTITY "Order Qty (Minimum Qty)",

       msi.MAXIMUM_ORDER_QUANTITY "Order Qty (Maximum Qty)",

       msi.SOURCE_TYPE "Source Type",

       msi.SOURCE_SUBINVENTORY "Source Sub inventory",

       msi.PLANNING_MAKE_BUY_CODE "MAKE BUY",

       msi.FIXED_LOT_MULTIPLIER "Fixed lot multiplier",

       msi.PLANNER_CODE "Planner",

       msi.MRP_PLANNING_CODE "Planning method",

       msi.ATO_FORECAST_CONTROL "Forecast control",

       msi.END_ASSEMBLY_PEGGING_FLAG "Pegging",

       msi.RELEASE_TIME_FENCE_CODE "Release Time Fence",

       msi.PLANNING_TIME_FENCE_DAYS "Planning time fence",

       msi.PREPROCESSING_LEAD_TIME "Preprocessing",

       msi.FULL_LEAD_TIME "Processing",

       msi.POSTPROCESSING_LEAD_TIME "Post processing"

  FROM mtl_system_items msi,

       mtl_secondary_locators msl,

       mtl_item_locations mil,

       per_all_people_f pap,

       mtl_secondary_inventories misi,

       org_organization_definitions ood,

       mtl_item_categories mic,

       mtl_categories_vl mcv,

       mtl_category_sets mcs

 WHERE     msi.inventory_item_id = msl.inventory_item_id(+)

       AND msi.organization_id = msl.organization_id(+)

       AND msl.secondary_locator = mil.inventory_location_id(+)

       AND msl.organization_id = mil.organization_id(+)

       AND pap.person_id(+) = msi.buyer_id

       AND msi.organization_id = misi.organization_id(+)

       AND ood.organization_id = msi.organization_id

       AND mic.inventory_item_id = msi.inventory_item_id

       AND mic.organization_id = msi.organization_id

       AND mcs.category_set_id = mic.category_set_id

       AND mcs.structure_id = mcv.structure_id

       AND mcv.category_id = mic.category_id

       AND msi.inventory_item_status_code = 'Active'

       AND ood.organization_id IN

              (SELECT DISTINCT master_organization_id FROM mtl_parameters)

Order By 1 

Material Transactions Type wise organization and SubInventory Data

 --- Organization and Sub Inventory and Transactions Type wise Material Transactions data 

SELECT ood.organization_code,

       ood.organization_name,

       mmt.subinventory_code,

       TO_CHAR (transaction_date, 'YYYY') trans_years,

       mtt.transaction_type_name,

       mtt.description transaction_description,

       msi.segment1 item_code,

       mmt.primary_quantity,

       transaction_date,

       transaction_uom,

       transaction_quantity

  FROM mtl_material_transactions mmt,

       mtl_system_items_b msi,

       mtl_transaction_types mtt,

       org_organization_definitions ood

 WHERE     msi.inventory_item_id = mmt.inventory_item_id

       AND mmt.organization_id = msi.organization_id

       AND ood.organization_id = mmt.organization_id

       AND mmt.transaction_type_id = mtt.transaction_type_id

       AND TO_CHAR (transaction_date, 'YYYY') >= 2021 ---2021 to tail data

Order by  mtt.transaction_type_name;

How to Update FIFO Layer Cost through Interface table

---Perform Layer Cost Update operations via the MTL_TRANSACTIONS_INTERFACE table.

-- Sample code below:

 declare

l_trx_id Number;

begin
fnd_global.apps_initialize(FND_PROFILE.VALUE ('USER_ID')

            , FND_PROFILE.VALUE ('RESP_ID')

            , FND_PROFILE.VALUE ('RESP_APPL_ID')

        );

select mtl_material_transactions_s.nextval into l_trx_id from dual;
INSERT INTO MTL_TXN_COST_DET_INTERFACE
(TRANSACTION_INTERFACE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ORGANIZATION_ID
,COST_ELEMENT_ID
,LEVEL_TYPE
,NEW_AVERAGE_COST
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,program_id
,program_update_date
,transaction_cost
,percentage_change
, value_change
)VALUES
(l_trx_id
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.user_id
,1522
,5
,1
,215.43
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL);

-- Insert into Transactions Interface
INSERT INTO MTL_TRANSACTIONS_INTERFACE
( TRANSACTION_INTERFACE_ID
,SOURCE_CODE
,PROCESS_FLAG
,TRANSACTION_MODE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_QUANTITY
,PRIMARY_QUANTITY
,TRANSACTION_UOM
,TRANSACTION_DATE
,ACCT_PERIOD_ID
,TRANSACTION_SOURCE_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_REFERENCE
,TRANSACTION_COST
,DISTRIBUTION_ACCOUNT_ID
,MATERIAL_ACCOUNT
,MATERIAL_OVERHEAD_ACCOUNT
,RESOURCE_ACCOUNT
,OUTSIDE_PROCESSING_ACCOUNT
,OVERHEAD_ACCOUNT
,COST_GROUP_ID
,SOURCE_LINE_ID
,SOURCE_HEADER_ID
)
VALUES
(l_trx_id
,'Layer Cost Update'
,1
,3 -- transaction_mode
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.user_id
,2356
,1522
,26
,26
,'Ea'
,SYSDATE
,15022
,1059
,15
,24
,28
,'Yog'
,29
,15342
,15342
,15342
,15342
,15342
,15342
,12435
,1
,1
);
end;

WARNING :
-- Layer cost update transactions can only be performed for LIFO or FIFO costing organizations.

FIFO:

Layer Item Cost

The first layer cost is cost of the first layer with a positive quantity. 

The layer item cost is the unit cost shared by the layer quantity. 

It is the average unit cost of a layer.

Layer Item Cost = layer's acquisition cost / layer's quantity

FIFO item cost is the weighted–average of all inventory layer costs divided by the sum of layer quantities.

Layer 1 = A

For example:

Layer 1 LQ1 = 20 ea. LC1 = $2/each

Layer 2 LQ2 = 10 ea. LC2 = $1.40/each

FIFO Item cost = ($2*20 + $1.40*10) / (20+10) = $1.80

Inventory Item assigned Organization

 SELECT   moa.organization_id, moa.inventory_item_id, organization_code,msi.segment1 item_code,

        organization_name, primary_unit_of_measure_tl, assigned_flag,

         moa.cost_of_sales_account, moa.sales_account, moa.expense_account,

         moa.encumbrance_account

    FROM mtl_org_assign_v moa

    ,mtl_system_items_b msi

   WHERE master_organization_id = 1823 --master organization

   AND msi.inventory_item_id = moa.inventory_item_id

   AND msi.organization_id = moa.organization_id

   AND (NVL (eam_enabled_flag, 'N') = NVL ('N', 'N') OR NVL ('N', 'N') = 'N')

     AND EXISTS (

            SELECT 'x'

              FROM hr_organization_information ood

             WHERE ood.organization_id = moa.organization_id

               AND ood.org_information2 = 'Y'

               AND ood.org_information1 = 'INV'

               AND (ood.org_information_context || '') = 'CLASS')

ORDER BY msi.segment1,--DECODE (organization_id, 1990, 1, 2) ASC,

         moa.assigned_flag DESC,

         moa.organization_code

Inventory Organization and Item attributes Query

 SELECT MP.ORGANIZATION_CODE,MP.ATTRIBUTE_CATEGORY,MP.ATTRIBUTE1,
 MP.ATTRIBUTE2,MP.ATTRIBUTE3,MP.ATTRIBUTE4,MP.ATTRIBUTE5,MP.ATTRIBUTE6,

MP.ATTRIBUTE7, MP.ATTRIBUTE8,MP.ATTRIBUTE9,MP.ATTRIBUTE10,MP.ATTRIBUTE11,

MP.ATTRIBUTE12,MP.ATTRIBUTE13,MP.ATTRIBUTE14,MP.ATTRIBUTE15,

MSI.SEGMENT1 ITEM_CODE, MSI.DESCRIPTION ITEM_DESCRIPTION,
MSI.ATTRIBUTE_CATEGORY, 
MSI.ATTRIBUTE1,MSI.ATTRIBUTE2,MSI.ATTRIBUTE3,MSI.ATTRIBUTE4,MSI.ATTRIBUTE5,MSI.ATTRIBUTE6,MSI.ATTRIBUTE7,MSI.ATTRIBUTE8,MSI.ATTRIBUTE9,MSI.ATTRIBUTE10,

MSI.ATTRIBUTE11,MSI.ATTRIBUTE12,MSI.ATTRIBUTE13,MSI.ATTRIBUTE14,MSI.ATTRIBUTE15,
MSI.GLOBAL_ATTRIBUTE_CATEGORY,
MSI.GLOBAL_ATTRIBUTE1,MSI.GLOBAL_ATTRIBUTE2,MSI.GLOBAL_ATTRIBUTE3,MSI.GLOBAL_ATTRIBUTE4,MSI.GLOBAL_ATTRIBUTE5,
MSI.GLOBAL_ATTRIBUTE6,MSI.GLOBAL_ATTRIBUTE7,MSI.GLOBAL_ATTRIBUTE8,MSI.GLOBAL_ATTRIBUTE9,MSI.GLOBAL_ATTRIBUTE10,
MSI.GLOBAL_ATTRIBUTE11,MSI.GLOBAL_ATTRIBUTE12,MSI.GLOBAL_ATTRIBUTE13,MSI.GLOBAL_ATTRIBUTE14,MSI.GLOBAL_ATTRIBUTE15
FROM MTL_PARAMETERS MP
    ,MTL_SYSTEM_ITEMS_B MSI
WHERE MP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ENABLED_FLAG = 'Y'

MTL Transaction Lot number to find OPM Batch Number Query

SELECT gbh.batch_no 

FROM gme_batch_header gbh

WHERE EXISTS

(SELECT 1          

   FROM mtl_transaction_lot_numbers mtln,mtl_lot_numbers lot,

        mtl_material_transactions mmt

  WHERE mmt.transaction_type_id = 35  --- WIP Issue 

    AND mtln.lot_number = lot.lot_number 

    AND mtln.transaction_id = mmt.transaction_id

    AND mmt.organization_id = NVL(:p_organization_id,mmt.organization_id)

    AND lot.lot_number = NVL(:P_lot_number,lot.lot_number)

    AND mmt.transaction_source_id is not null

    AND gbh.batch_id = mmt.transaction_source_id)

/

How to Upload Item in Inventory through Interface

Step1: Create Staging Table.

CREATE TABLE APPS.XXCONA_ITEM_UPLOAD_STG
(
  ITEM_NUMBER        VARCHAR2(40 BYTE),
  DESCRIPTION        VARCHAR2(240 BYTE),
  LONG_DESCRIPTION   VARCHAR2(400 BYTE),
  ENABLED_FLAG       VARCHAR2(1 BYTE),
  PRIMARY_UOM_CODE   VARCHAR2(20 BYTE),
  TEMPLATE_NAME      VARCHAR2(100 BYTE),
  ORGANIZATION_CODE  VARCHAR2(10 BYTE),
  TRANSACTION_TYPE   VARCHAR2(40 BYTE),
  ITEM_TYPE          VARCHAR2(40 BYTE),
  LOT_CONTROL_CODE   VARCHAR2(40 BYTE),
  ITEM_STATUS_CODE   VARCHAR2(40 BYTE),
  VERIFY_FLAG        VARCHAR2(1 BYTE),
  ERROR_MSG          VARCHAR2(200 BYTE),
  ERROR_MESSAGE      VARCHAR2(2000 BYTE)
)

After Create Staging Table

Import data to the Staging Table.

DECLARE
  L_MST_ORG_ID      NUMBER;
  L_VERIFY_FLAG     VARCHAR2(2):='Y';
  L_UOM             VARCHAR2(10);
  L_TEMPLATE_ID     NUMBER;
  L_COUNT           NUMBER;
  L_ITEM_TYPE       VARCHAR2(20);
  L_CATEGORY_ID     NUMBER;
  L_ERROR_MESSAGE   VARCHAR2(200):= NULL;
  L_CATEGORY_SET_ID NUMBER;
  L_ERRM            VARCHAR2(2000);
  ret_num           NUMBER;
  L_User_Id         NUMBER := fnd_profile.VALUE ('USER_ID');
  L_resp_app_id     NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');
  L_resp_id         NUMBER := fnd_profile.VALUE ('RESP_ID');
  L_Organization_id NUMBER := fnd_profile.VALUE ('MFG_ORGANIZATION_ID');

  CURSOR CUR_ITEM
  IS
    SELECT * FROM XXCONA_ITEM_UPLOAD_STG
       WHERE  NVL(VERIFY_FLAG,'N') ='N';
      
 BEGIN

  FOR ITEM IN CUR_ITEM
  LOOP
   /*ORGANIZATION VALIDATION*/
    BEGIN
      SELECT ORGANIZATION_ID
      INTO L_MST_ORG_ID
      FROM ORG_ORGANIZATION_DEFINITIONS
      WHERE UPPER(TRIM(ORGANIZATION_CODE))=UPPER(TRIM(ITEM.ORGANIZATION_CODE));
     dbms_output.PUT_LINE(CUR_ITEM%ROWCOUNT||'.ORGANIZATION ID: '||L_MST_ORG_ID);
    EXCEPTION
    WHEN OTHERS THEN
      L_VERIFY_FLAG  :='N';
      L_ERROR_MESSAGE:='INVALID ORGANIZATION';
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_ERROR_MESSAGE);
      dbms_output.PUT_LINE('INVALID ORGANIZATION');
    END;
   
     /*TEMPLATE VALIDATION*/
    BEGIN
      SELECT TEMPLATE_ID
      into L_TEMPLATE_ID
      FROM MTL_ITEM_TEMPLATES
      WHERE upper(trim(TEMPLATE_NAME)) = upper(trim(ITEM.TEMPLATE_NAME));
     dbms_output.PUT_LINE('L_TEMPLATE_ID'||L_TEMPLATE_ID);
    EXCEPTION
    WHEN OTHERS THEN
      L_VERIFY_FLAG  :='N';
      L_ERROR_MESSAGE:='INVALID Template Name';
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_ERROR_MESSAGE);
      dbms_output.PUT_LINE(L_ERROR_MESSAGE);
    END;
   
    /*UOM VALIDATION*/
    BEGIN
      SELECT UOM_CODE
      INTO L_UOM
      FROM MTL_UNITS_OF_MEASURE
      WHERE UPPER(TRIM(UNIT_OF_MEASURE))=UPPER(TRIM(ITEM.PRIMARY_UOM_CODE));
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_UOM);
      --dbms_output.PUT_LINE(L_UOM);
        dbms_output.PUT_LINE('L_UOM: '||L_UOM);
    EXCEPTION
    WHEN OTHERS THEN
      L_VERIFY_FLAG  :='N';
      L_ERROR_MESSAGE:=L_ERROR_MESSAGE||'UOM IS NOT VALID';
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_ERROR_MESSAGE);
      dbms_output.PUT_LINE('UOM IS NOT VALID');
    END;
   
        /* ITEM IS EXIST OR NOT*/
    BEGIN
      L_COUNT := 0;
      SELECT COUNT(*)
      INTO L_COUNT
      FROM MTL_SYSTEM_ITEMS_B
      WHERE UPPER(TRIM(SEGMENT1))= UPPER(TRIM(ITEM.ITEM_NUMBER))
      AND ORGANIZATION_ID        = L_MST_ORG_ID;
   
     IF L_COUNT                 > 0 THEN
        L_VERIFY_FLAG           := 'N';
        L_ERROR_MESSAGE         :=L_ERROR_MESSAGE || 'ITEM ALREADY EXISTING' ;
     END IF;
  
    EXCEPTION
    WHEN OTHERS THEN
      L_ERROR_MESSAGE:=L_ERROR_MESSAGE|| 'ITEM CHECKING FAILED' ;
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_ERROR_MESSAGE);
      dbms_output.PUT_LINE('ITEM CHECKING FAILED');
    END;

  /*CATEGORY_SET_ID VALIDATION*/
    BEGIN
      SELECT CATEGORY_SET_ID
      INTO L_CATEGORY_SET_ID
      FROM MTL_CATEGORY_SETS_TL
      WHERE UPPER(TRIM(CATEGORY_SET_NAME)) = UPPER(TRIM('Inventory'));
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_CATEGORY_SET_ID);
      dbms_output.PUT_LINE('CATEGORY_SET_ID: '||L_CATEGORY_SET_ID);
    EXCEPTION
    WHEN OTHERS THEN
      L_VERIFY_FLAG  := 'N';
      L_ERROR_MESSAGE:= L_ERROR_MESSAGE||'INVALID CATEGORY SET';
      FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_ERROR_MESSAGE);
      dbms_output.PUT_LINE('INVALID CATEGORY SET');
    END;
   
    /*IF ALL CONDITION SATISFY THEN INSERT INTO TABLE*/
    IF ITEM.VERIFY_FLAG<>'N' or L_VERIFY_FLAG <> 'N' THEN
   
      BEGIN
     
        -----MASTER ORGANIZATION  -----------  
        FND_FILE.put_line (FND_FILE.LOG,'1');
        INSERT
        INTO APPS.MTL_SYSTEM_ITEMS_INTERFACE
          ( INVENTORY_ITEM_ID,
            SEGMENT1,
            DESCRIPTION,
            LONG_DESCRIPTION,
            PRIMARY_UOM_CODE,
            SET_PROCESS_ID,
            TEMPLATE_ID,
            ORGANIZATION_ID,
            TRANSACTION_TYPE,
            PROCESS_FLAG,
            --ITEM_TYPE,
            INVENTORY_ITEM_STATUS_CODE,
         --   LOT_CONTROL_CODE,
         --   DEFAULT_LOT_STATUS_ID,
         --   INVENTORY_PLANNING_CODE,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATED_BY,
            LAST_UPDATE_DATE
          )
          VALUES
          (MTL_SYSTEM_ITEMS_B_S.nextval,
            ITEM.ITEM_NUMBER,
            ITEM.DESCRIPTION,
            ITEM.LONG_DESCRIPTION,
            L_UOM,
            1,   ---SET_PROCESS_ID
            L_TEMPLATE_ID,
            L_MST_ORG_ID,
            'CREATE',   ---TRANSACTION_TYPE, ---CREATE
            1, ---PROCESS_FLAG
            --ITEM.ITEM_TYPE,
            ITEM.ITEM_STATUS_CODE,
            --DECODE(LOWER(ITEM.LOT_CONTROL_CODE),'yes',2,1),
            --ITEM.AUTO_LOT_ALPHA_PREFIX,
            --2,
            --ITEM.SHELF_LIFE_DAYS,
           -- 83,
           -- 6,
            SYSDATE,
            L_USER_ID,
            L_USER_ID,
            SYSDATE
          );
         
          ------------------requered organization---------
        
    
        FND_FILE.put_line (FND_FILE.LOG,'1');
        INSERT
        INTO APPS.MTL_SYSTEM_ITEMS_INTERFACE
          (INVENTORY_ITEM_ID,
            SEGMENT1,
            DESCRIPTION,
            LONG_DESCRIPTION,
            PRIMARY_UOM_CODE,
            SET_PROCESS_ID,
            TEMPLATE_ID,
            ORGANIZATION_ID,
            TRANSACTION_TYPE,
            PROCESS_FLAG,
            --ITEM_TYPE,
            INVENTORY_ITEM_STATUS_CODE,
         --   LOT_CONTROL_CODE,
         --   DEFAULT_LOT_STATUS_ID,
         --   INVENTORY_PLANNING_CODE,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATED_BY,
            LAST_UPDATE_DATE
          )
          VALUES
          ( MTL_SYSTEM_ITEMS_B_S.currval,
            ITEM.ITEM_NUMBER,
            ITEM.DESCRIPTION,
            ITEM.LONG_DESCRIPTION,
            L_UOM,
            1,
            L_TEMPLATE_ID,
            142, --- UNITII_IO 
            'CREATE', --TRANSACTION_TYPE,  ---CREATE 
            1, ---PROCESS_FLAG
            --ITEM.ITEM_TYPE,
            ITEM.ITEM_STATUS_CODE,
            --DECODE(LOWER(ITEM.LOT_CONTROL_CODE),'yes',2,1),
            --ITEM.AUTO_LOT_ALPHA_PREFIX,
            --2,
            --ITEM.SHELF_LIFE_DAYS,
           -- 83,
           -- 6,
            SYSDATE,
            L_USER_ID,
            L_USER_ID,
            SYSDATE
          );
         
        UPDATE XXCONA_ITEM_UPLOAD_STG
        SET VERIFY_FLAG = 'Y'
            ,ERROR_MESSAGE = L_ERROR_MESSAGE
        WHERE ITEM_NUMBER =ITEM.ITEM_NUMBER;
        COMMIT;
       
      EXCEPTION
      WHEN OTHERS THEN
        L_ERRM := SQLERRM;
        FND_FILE.put_line (FND_FILE.LOG,'Message Text ' || L_ERRM);
        DBMS_OUTPUT.PUT_LINE(L_ERRM);
        UPDATE XXCONA_ITEM_UPLOAD_STG
        SET VERIFY_FLAG = 'N'
          , ERROR_MSG =SUBSTR(L_ERRM,1,150)
          , ERROR_MESSAGE = L_ERROR_MESSAGE
        WHERE ITEM_NUMBER=ITEM.ITEM_NUMBER;
         COMMIT;
      END;
     
     
    ELSE
      UPDATE XXCONA_ITEM_UPLOAD_STG
      SET VERIFY_FLAG = 'N'
       , ERROR_MSG =SUBSTR(L_ERRM,1,150)
       , ERROR_MESSAGE = L_ERROR_MESSAGE
      WHERE ITEM_NUMBER =ITEM.ITEM_NUMBER;
      COMMIT;
     
     
    END IF;
   
   
  END LOOP;
  COMMIT;
  fnd_global.apps_initialize (L_User_Id,L_resp_id,L_resp_app_id);
  ret_num := fnd_request.submit_request('INV', 'INCOIN', NULL, NULL, False, L_Organization_id, 1, 1, 1, 1, NULL, 1, 1, chr(0));
 COMMIT;
END;