Inventory to PO , SO(Sales Order) , WO(Work Order) Drill Down Query With Lot Numbers (Item Transaction Report)

 select 

TAB.RECEIPT_DATE TRANSACTION_DATE, 

TAB.SUPPLY_TYPE_CODE DOCUMENT_TYPE,  

TAB.DOCUMENT,

TAB.CustomerorSupplier,

TAB.TO_ORG_PRIMARY_QUANTITY QUANTITY, 

TAB.TO_ORG_PRIMARY_UOM_CODE UOM, 

TAB.onhand_qty, 

TAB.lot_number,

TAB.item,

TAB.SECONDARY_TRANSACTION_QUANTITY SECONDARY_QUANTITY,

TAB.TRANSFER_SUBINVENTORY TRANSFER_TANK,

TAB.SHIPMENT_NUMBER,

TAB.transfer_org,

TAB.RECEIPT_NUM RECEIPT

from 

(

select 

to_char(imt.TRANSACTION_DATE, 'mm/dd/yyyy') RECEIPT_DATE,

itst.TRANSACTION_SOURCE_TYPE_NAME SUPPLY_TYPE_CODE,

poh.segment1 document,

(select hp.party_name

FROM poz_suppliers ps,

hz_parties hp

WHERE 1=1

and hp.party_id = ps.party_id

and ps.vendor_id=poh.vendor_id)CustomerorSupplier,

imt.TRANSACTION_QUANTITY TO_ORG_PRIMARY_QUANTITY,

imt.TRANSACTION_UOM TO_ORG_PRIMARY_UOM_CODE,

itln.lot_number,

(select nvl(sum(a.primary_TRANSACTION_QUANTITY),0) from inv_onhand_quantities_detail a

                    where a.INVENTORY_ITEM_ID = esi.inventory_item_id

                    and a.ORGANIZATION_ID =esi.organization_id) onhand_qty,

esi.ITEM_NUMBER  item,

imt.SECONDARY_TRANSACTION_QUANTITY,

imt.TRANSFER_SUBINVENTORY,

imt.SHIPMENT_NUMBER,

(select hou.name from hr_organization_units hou where hou.ORGANIZATION_ID=imt.TRANSFER_ORGANIZATION_ID) transfer_org,

RSH.RECEIPT_NUM

from INV_MATERIAL_TXNS imt,

     INV_TRANSACTION_LOT_NUMBERS itln,

egp_system_items esi,

INV_TXN_SOURCE_TYPES_TL itst,

PO_LINES_ALL pla,

PO_HEADERS_ALL poh

,RCV_SHIPMENT_LINES RSL,

     RCV_SHIPMENT_HEADERS RSH,

rcv_transactions RV

where 1=1

and imt.ORGANIZATION_id=itln.ORGANIZATION_id

and imt.TRANSACTION_ID=itln.TRANSACTION_ID

and imt.INVENTORY_ITEM_ID=esi.INVENTORY_ITEM_ID

and imt.TRANSACTION_SOURCE_TYPE_ID=itst.TRANSACTION_SOURCE_TYPE_ID

and itst.LANGUAGE='US'

AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID

 AND RSL.PO_HEADER_ID = POH.PO_HEADER_ID

 AND RSL.PO_LINE_ID = PLA.PO_LINE_ID

 AND RV.TRANSACTION_ID = IMT.RCV_TRANSACTION_ID

 AND RV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID

 --AND RV.TRANSACTION_TYPE = 'DELIVER'

 AND RV.DESTINATION_TYPE_CODE = 'INVENTORY'

 and itln.TRANSACTION_SOURCE_TYPE_ID in (1)

--and poh.segment1 = '31'

and pla.ITEM_ID=imt.inventory_item_id

and pla.PO_HEADER_ID=poh.PO_HEADER_ID

and esi.ORGANIZATION_id=imt.ORGANIZATION_id

AND poh.po_header_id = imt.transaction_source_id

AND (itst.TRANSACTION_SOURCE_TYPE_NAME IN(:P_TRX_SOURCE_TYPE) OR LEAST(:P_TRX_SOURCE_TYPE)IS NULL)

AND (imt.SUBINVENTORY_CODE IN(:P_SUBINVENTORY_CODE) OR LEAST(:P_SUBINVENTORY_CODE)IS NULL)

AND (esi.item_number IN(:p_item) OR LEAST(:p_item)IS NULL)               --100000004025858

and imt.ORGANIZATION_ID in (select organization_id from hr_organization_units where 1=1 and (name IN(:p_orgId) OR LEAST(:p_orgId)IS NULL))  --300000003803116

AND imt.TRANSACTION_DATE BETWEEN NVL(:P_FromDate,to_date('01/01/1970', 'mm/dd/yyyy')) AND NVL(:P_ToDate,to_date('01/01/4710', 'mm/dd/yyyy'))

UNION ALL

select  to_char(imt.TRANSACTION_DATE, 'mm/dd/yyyy') RECEIPT_DATE,

    itst.TRANSACTION_SOURCE_TYPE_NAME SUPPLY_TYPE_CODE,

iso.SALES_ORDER_NUMBER document,

(select hp.party_name

         FROM HZ_PARTIES hp

         where 1=1

         AND hp.PARTY_ID=iso.SOLD_TO_PARTY_ID)CustomerorSupplier,

       imt.TRANSACTION_QUANTITY TO_ORG_PRIMARY_QUANTITY,

   imt.TRANSACTION_UOM TO_ORG_PRIMARY_UOM_CODE,

   itln.lot_number,

(select nvl(sum(a.primary_TRANSACTION_QUANTITY),0) from inv_onhand_quantities_detail a

                    where a.INVENTORY_ITEM_ID = esi.inventory_item_id

                    and a.ORGANIZATION_ID =esi.organization_id) onhand_qty,

esi.ITEM_NUMBER  item,

imt.SECONDARY_TRANSACTION_QUANTITY,

imt.TRANSFER_SUBINVENTORY,

imt.SHIPMENT_NUMBER,

(select hou.name from hr_organization_units hou where hou.ORGANIZATION_ID=imt.TRANSFER_ORGANIZATION_ID) transfer_org,

NULL RECEIPT_NUM

from INV_SALES_ORDERS iso,

     INV_MATERIAL_TXNS imt,

     egp_system_items esi,

INV_TXN_SOURCE_TYPES_TL itst,

INV_TRANSACTION_LOT_NUMBERS itln

WHERE 1=1

AND iso.SALES_ORDER_ID  = imt.transaction_source_id

--and iso.SALES_ORDER_NUMBER  = '150'

AND imt.inventory_item_id = esi.inventory_item_id

and imt.ORGANIZATION_ID = esi.organization_id

and itst.LANGUAGE='US'

and itln.inventory_item_id = esi.inventory_item_id

and itln.ORGANIZATION_ID = esi.ORGANIZATION_ID

and itln.TRANSACTION_SOURCE_TYPE_ID = 2

and imt.TRANSACTION_SOURCE_TYPE_ID=itst.TRANSACTION_SOURCE_TYPE_ID

and imt.TRANSACTION_ID = itln.TRANSACTION_ID

AND (itst.TRANSACTION_SOURCE_TYPE_NAME IN(:P_TRX_SOURCE_TYPE) OR LEAST(:P_TRX_SOURCE_TYPE)IS NULL)

AND (imt.SUBINVENTORY_CODE IN(:P_SUBINVENTORY_CODE) OR LEAST(:P_SUBINVENTORY_CODE)IS NULL)

AND (esi.item_number IN(:p_item) OR LEAST(:p_item)IS NULL)               

and imt.ORGANIZATION_ID in (select organization_id from hr_organization_units where 1=1 and (name IN(:p_orgId) OR LEAST(:p_orgId)IS NULL))  

AND imt.TRANSACTION_DATE BETWEEN NVL(:P_FromDate,to_date('01/01/1970', 'mm/dd/yyyy')) AND NVL(:P_ToDate,to_date('01/01/4710', 'mm/dd/yyyy'))

UNION ALL

select  to_char(imt.TRANSACTION_DATE, 'mm/dd/yyyy') RECEIPT_DATE,

    itst.TRANSACTION_SOURCE_TYPE_NAME SUPPLY_TYPE_CODE,

wwo.WORK_ORDER_NUMBER document,

NULL CustomerorSupplier,

       imt.TRANSACTION_QUANTITY TO_ORG_PRIMARY_QUANTITY,

   imt.TRANSACTION_UOM TO_ORG_PRIMARY_UOM_CODE,

   itln.lot_number,

(select nvl(sum(a.primary_TRANSACTION_QUANTITY),0) from inv_onhand_quantities_detail a

                    where a.INVENTORY_ITEM_ID = esi.inventory_item_id

                    and a.ORGANIZATION_ID =esi.organization_id) onhand_qty,

  esi.ITEM_NUMBER  item,

imt.SECONDARY_TRANSACTION_QUANTITY,

imt.TRANSFER_SUBINVENTORY,

imt.SHIPMENT_NUMBER,

(select hou.name from hr_organization_units hou where hou.ORGANIZATION_ID=imt.TRANSFER_ORGANIZATION_ID) transfer_org,

NULL RECEIPT_NUM

from WIE_WORK_ORDERS_B wwo,

     INV_MATERIAL_TXNS imt,

     egp_system_items esi,

INV_TXN_SOURCE_TYPES_TL itst,

INV_TRANSACTION_LOT_NUMBERS itln

WHERE 1=1

AND wwo.WORK_ORDER_ID  = imt.TRANSACTION_SOURCE_ID

--and iso.SALES_ORDER_NUMBER  = '150'

AND imt.inventory_item_id = esi.inventory_item_id

and imt.ORGANIZATION_ID = esi.organization_id

and itst.LANGUAGE='US'

and itln.inventory_item_id = esi.inventory_item_id

and itln.ORGANIZATION_ID = esi.ORGANIZATION_ID

and itln.TRANSACTION_SOURCE_TYPE_ID = 5

and imt.TRANSACTION_SOURCE_TYPE_ID=itst.TRANSACTION_SOURCE_TYPE_ID

and imt.TRANSACTION_ID = itln.TRANSACTION_ID

AND (itst.TRANSACTION_SOURCE_TYPE_NAME IN(:P_TRX_SOURCE_TYPE) OR LEAST(:P_TRX_SOURCE_TYPE)IS NULL)

AND (imt.SUBINVENTORY_CODE IN(:P_SUBINVENTORY_CODE) OR LEAST(:P_SUBINVENTORY_CODE)IS NULL)

AND (esi.item_number IN(:p_item) OR LEAST(:p_item)IS NULL)               

and imt.ORGANIZATION_ID in (select organization_id from hr_organization_units where 1=1 and (name IN(:p_orgId) OR LEAST(:p_orgId)IS NULL))  

AND imt.TRANSACTION_DATE BETWEEN NVL(:P_FromDate,to_date('01/01/1970', 'mm/dd/yyyy')) AND NVL(:P_ToDate,to_date('01/01/4710', 'mm/dd/yyyy'))

UNION ALL

select to_char(imt.TRANSACTION_DATE, 'mm/dd/yyyy') RECEIPT_DATE,

    itst.TRANSACTION_SOURCE_TYPE_NAME SUPPLY_TYPE_CODE,

IGD.SEGMENT1 document,

NULL CustomerorSupplier,

       imt.TRANSACTION_QUANTITY TO_ORG_PRIMARY_QUANTITY,

   imt.TRANSACTION_UOM TO_ORG_PRIMARY_UOM_CODE,

   itln.lot_number,

(select nvl(sum(a.primary_TRANSACTION_QUANTITY),0) from inv_onhand_quantities_detail a

                    where a.INVENTORY_ITEM_ID = esi.inventory_item_id

                    and a.ORGANIZATION_ID =esi.organization_id) onhand_qty,

esi.ITEM_NUMBER  item,

imt.SECONDARY_TRANSACTION_QUANTITY,

imt.TRANSFER_SUBINVENTORY,

imt.SHIPMENT_NUMBER,

(select hou.name from hr_organization_units hou where hou.ORGANIZATION_ID=imt.TRANSFER_ORGANIZATION_ID) transfer_org,

NULL RECEIPT_NUM

from INV_GENERIC_DISPOSITIONS  IGD,

     INV_MATERIAL_TXNS imt,

     egp_system_items esi,

INV_TXN_SOURCE_TYPES_TL itst,

INV_TRANSACTION_LOT_NUMBERS itln

WHERE 1=1

AND IGD.DISPOSITION_ID = IMT.TRANSACTION_SOURCE_ID

AND imt.inventory_item_id = esi.inventory_item_id

and imt.ORGANIZATION_ID = esi.organization_id

and itst.LANGUAGE='US'

and itln.inventory_item_id = esi.inventory_item_id

and itln.ORGANIZATION_ID = esi.ORGANIZATION_ID

and itln.TRANSACTION_SOURCE_TYPE_ID = 6

and imt.TRANSACTION_SOURCE_TYPE_ID=itst.TRANSACTION_SOURCE_TYPE_ID

and imt.TRANSACTION_ID = itln.TRANSACTION_ID

AND (itst.TRANSACTION_SOURCE_TYPE_NAME IN(:P_TRX_SOURCE_TYPE) OR LEAST(:P_TRX_SOURCE_TYPE)IS NULL)

AND (imt.SUBINVENTORY_CODE IN(:P_SUBINVENTORY_CODE) OR LEAST(:P_SUBINVENTORY_CODE)IS NULL)

AND (esi.item_number IN(:p_item) OR LEAST(:p_item)IS NULL)               

and imt.ORGANIZATION_ID in (select organization_id from hr_organization_units where 1=1 and (name IN(:p_orgId) OR LEAST(:p_orgId)IS NULL))  

AND imt.TRANSACTION_DATE BETWEEN NVL(:P_FromDate,to_date('01/01/1970', 'mm/dd/yyyy')) AND NVL(:P_ToDate,to_date('01/01/4710', 'mm/dd/yyyy'))

UNION ALL

select  to_char(imt.TRANSACTION_DATE, 'mm/dd/yyyy') RECEIPT_DATE,

    itst.TRANSACTION_SOURCE_TYPE_NAME SUPPLY_TYPE_CODE,

NULL document,

NULL CustomerorSupplier,

       imt.TRANSACTION_QUANTITY TO_ORG_PRIMARY_QUANTITY,

   imt.TRANSACTION_UOM TO_ORG_PRIMARY_UOM_CODE,

   itln.lot_number,

(select nvl(sum(a.primary_TRANSACTION_QUANTITY),0) from inv_onhand_quantities_detail a

                    where a.INVENTORY_ITEM_ID = esi.inventory_item_id

                    and a.ORGANIZATION_ID =esi.organization_id) onhand_qty,

esi.ITEM_NUMBER  item,

imt.SECONDARY_TRANSACTION_QUANTITY,

imt.TRANSFER_SUBINVENTORY,

imt.SHIPMENT_NUMBER,

(select hou.name from hr_organization_units hou where hou.ORGANIZATION_ID=imt.TRANSFER_ORGANIZATION_ID) transfer_org,

NULL RECEIPT_NUM

from 

     INV_MATERIAL_TXNS imt,

     egp_system_items esi,

INV_TXN_SOURCE_TYPES_TL itst,

INV_TRANSACTION_LOT_NUMBERS itln

WHERE 1=1

AND imt.inventory_item_id = esi.inventory_item_id

and imt.ORGANIZATION_ID = esi.organization_id

and itst.LANGUAGE='US'

and itln.inventory_item_id = esi.inventory_item_id

and itln.ORGANIZATION_ID = esi.ORGANIZATION_ID

and itln.TRANSACTION_SOURCE_TYPE_ID IN (5,13)

--and itln.TRANSACTION_SOURCE_TYPE_ID IN 13

and imt.TRANSACTION_SOURCE_TYPE_ID=itst.TRANSACTION_SOURCE_TYPE_ID

and imt.TRANSACTION_ID = itln.TRANSACTION_ID

AND (itst.TRANSACTION_SOURCE_TYPE_NAME IN(:P_TRX_SOURCE_TYPE) OR LEAST(:P_TRX_SOURCE_TYPE)IS NULL)

AND (imt.SUBINVENTORY_CODE IN(:P_SUBINVENTORY_CODE) OR LEAST(:P_SUBINVENTORY_CODE)IS NULL)

AND (esi.item_number IN(:p_item) OR LEAST(:p_item)IS NULL)               

and imt.ORGANIZATION_ID in (select organization_id from hr_organization_units where 1=1 and (name IN(:p_orgId) OR LEAST(:p_orgId)IS NULL))  

AND imt.TRANSACTION_DATE BETWEEN NVL(:P_FromDate,to_date('01/01/1970', 'mm/dd/yyyy')) AND NVL(:P_ToDate,to_date('01/01/4710', 'mm/dd/yyyy'))

UNION ALL

select  to_char(imt.TRANSACTION_DATE, 'mm/dd/yyyy') RECEIPT_DATE,

    itst.TRANSACTION_SOURCE_TYPE_NAME SUPPLY_TYPE_CODE,

( select RA_DOCUMENT_NUMBER 

           from rcv_shipment_headers rsh 

           where rt.CUSTOMER_ID = rsh.CUSTOMER_ID

           and rt.RECEIPT_ADVICE_HEADER_ID = rsh.SHIPMENT_HEADER_ID

           and rt.DESTINATION_TYPE_CODE = 'INVENTORY' 

           ) DOC_NUMBER,

   NULL CustomerorSupplier,

       imt.TRANSACTION_QUANTITY TO_ORG_PRIMARY_QUANTITY,

   imt.TRANSACTION_UOM TO_ORG_PRIMARY_UOM_CODE,

   itln.lot_number,

(select nvl(sum(a.primary_TRANSACTION_QUANTITY),0) from inv_onhand_quantities_detail a

                    where a.INVENTORY_ITEM_ID = esi.inventory_item_id

                    and a.ORGANIZATION_ID =esi.organization_id) onhand_qty,

esi.ITEM_NUMBER  item,

imt.SECONDARY_TRANSACTION_QUANTITY,

imt.TRANSFER_SUBINVENTORY,

imt.SHIPMENT_NUMBER,

(select hou.name from hr_organization_units hou where hou.ORGANIZATION_ID=imt.TRANSFER_ORGANIZATION_ID) transfer_org,

(select RECEIPT_NUM from rcv_shipment_headers where SHIPMENT_HEADER_ID = rt.SHIPMENT_HEADER_ID ) RCV_SHIP_RECEIPT

from 

     INV_MATERIAL_TXNS imt,

     egp_system_items esi,

INV_TXN_SOURCE_TYPES_TL itst,

INV_TRANSACTION_LOT_NUMBERS itln,

  rcv_transactions rt

WHERE 1=1

AND imt.inventory_item_id = esi.inventory_item_id

and imt.ORGANIZATION_ID = esi.organization_id

and itst.LANGUAGE='US'

and itln.inventory_item_id = esi.inventory_item_id

and itln.ORGANIZATION_ID = esi.ORGANIZATION_ID

and itln.TRANSACTION_SOURCE_TYPE_ID IN (12)

and imt.TRANSACTION_SOURCE_TYPE_ID=itst.TRANSACTION_SOURCE_TYPE_ID

and imt.TRANSACTION_ID = itln.TRANSACTION_ID

and rt.transaction_id  = imt.RCV_TRANSACTION_ID

AND (itst.TRANSACTION_SOURCE_TYPE_NAME IN(:P_TRX_SOURCE_TYPE) OR LEAST(:P_TRX_SOURCE_TYPE)IS NULL)

AND (imt.SUBINVENTORY_CODE IN(:P_SUBINVENTORY_CODE) OR LEAST(:P_SUBINVENTORY_CODE)IS NULL)

AND (esi.item_number IN(:p_item) OR LEAST(:p_item)IS NULL)               

and imt.ORGANIZATION_ID in (select organization_id from hr_organization_units where 1=1 and (name IN(:p_orgId) OR LEAST(:p_orgId)IS NULL))  

AND imt.TRANSACTION_DATE BETWEEN NVL(:P_FromDate,to_date('01/01/1970', 'mm/dd/yyyy')) AND NVL(:P_ToDate,to_date('01/01/4710', 'mm/dd/yyyy'))

) TAB

order by 1

Inventory Miscellaneous Receipt or Miscellaneous issue scripts from backend in oracle apps

Inventory Miscellaneous Receipt or Miscellaneous issue scripts in oracle apps

Step1 : Run the Below Procedure 

create or replace PROCEDURE PKMTL_TRANSACTION(p_item_id           IN   VARCHAR2,
                           p_organization_id   IN   NUMBER,
                           p_subinv            IN   VARCHAR2,
                           p_loc               IN   VARCHAR2,
                           p_lot_no            IN   VARCHAR2,
                           p_uom_code          IN   VARCHAR2,
                           p_quantity          IN   NUMBER,
                           p_transaction_type  IN   VARCHAR2,
                           p_trans_type_ref    IN   VARCHAR2,
                           p_primary_qty       IN   NUMBER,
                           x_return_status     OUT  VARCHAR2,
                           x_msg_count         OUT  NUMBER,
                           x_msg_data          OUT  VARCHAR2)
IS

l_transaction_id        NUMBER;
l_loc_id                        NUMBER;
l_trn_type_id           NUMBER;
l_segment1                      VARCHAR2(150);
l_segment2                      VARCHAR2(150);
l_segment3                      VARCHAR2(150);
l_segment4                      VARCHAR2(150);
l_segment5                      VARCHAR2(150);
l_segment6                      VARCHAR2(150);
l_segment7                      VARCHAR2(150);
l_segment8                      VARCHAR2(150);
l_segment9                      VARCHAR2(150);
l_segment10             VARCHAR2(150);
l_segment11              VARCHAR2(150);
l_expiration_date       DATE;
l_trans_count       NUMBER;
lc_result           NUMBER;
l_subinv_code mtl_item_locations_kfv.subinventory_code%type;
r_mtl_trns_rec mtl_transactions_interface%rowtype;
R_MTL_LOT_REC MTL_TRANSACTION_LOTS_INTERFACE%ROWTYPE;

G_L_TRANSACTION_ID  number;
G_L_LPN_ID          number:=0;

CURSOR cur_tns_rec
IS
SELECT mtt.*
FROM MTL_TRANSACTION_TYPES mtt
where MTT.TRANSACTION_TYPE_NAME = P_TRANSACTION_TYPE;
R_TNS_REC CUR_TNS_REC%ROWTYPE;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
      x_return_status:= null;
      x_msg_data := null;

dbms_output.put_line('================================================================================');
      BEGIN
           SELECT mtl_material_transactions_s.NEXTVAL
           INTO   l_transaction_id
           FROM   DUAL;
      EXCEPTION WHEN OTHERS THEN
          l_transaction_id := NULL;
      END;
      OPEN  cur_tns_rec;
      FETCH cur_tns_rec INTO r_tns_rec;
      CLOSE cur_tns_rec;

      BEGIN
      
      
      --l_segment1:=01;
                 l_segment1:='01';
                 l_segment2:='510';
                 l_segment3:='4110';
                 l_segment4:='0000';
                 l_segment5:='000';
                 l_segment6:=null;
                 l_segment7:=null;
                 l_segment8:=null;
                 l_segment9:=null;
                 l_segment10:=null;
           /*SELECT gcc.segment1,
                 gcc.segment2,
                 gcc.segment3,
                 gcc.segment4,
                 gcc.segment5,
                 gcc.segment6,
                 gcc.segment7,
                 gcc.segment8,
                 gcc.segment9,
                 gcc.segment10
           INTO   l_segment11,
                 l_segment2,
                 l_segment3,
                 l_segment4,
                 l_segment5,
                 l_segment6,
                 l_segment7,
                 l_segment8,
                 l_segment9,
                 l_segment10
           FROM   gl_code_combinations_kfv gcck, gl_code_combinations gcc
           where  GCC.CODE_COMBINATION_ID= 19547
               AND    gcc.code_combination_id = gcck.code_combination_id;*/
          EXCEPTION WHEN OTHERS THEN
                   l_segment1 :=NULL;
                   l_segment2 :=NULL;
                   l_segment6 :=NULL;
                   l_segment3 :=NULL;
                   l_segment4 :=NULL;
                   l_segment5 :=NULL;
                   l_segment7 :=NULL;
                   l_segment8 :=NULL;
                   l_segment9 :=NULL;
                   l_segment10 :=NULL;
          END;

          BEGIN
              SELECT inventory_location_id,subinventory_code
              INTO   l_loc_id,l_subinv_code
              FROM   mtl_item_locations_kfv
              WHERE  concatenated_segments = p_loc
              AND    organization_id = p_organization_id;
          EXCEPTION WHEN OTHERS THEN
                    l_loc_id := NULL;
          END;

          dbms_output.put_line('p_loc: '||p_loc);
          dbms_output.put_line('l_loc_id: '||l_loc_id);

          dbms_output.put_line('Transaction_type: '||r_tns_rec.transaction_type_name);
          dbms_output.put_line('p_quantity: '||p_quantity);
          BEGIN
              r_mtl_trns_rec := NULL;
              g_l_transaction_id := NULL;
              r_mtl_trns_rec.transaction_uom             :=  p_uom_code;
              r_mtl_trns_rec.transaction_date            :=  SYSDATE;
              r_mtl_trns_rec.source_code                 :=  p_transaction_type;
              r_mtl_trns_rec.source_line_id              :=  l_transaction_id; -- 422170
              r_mtl_trns_rec.source_header_id            :=  l_transaction_id ;  -- 167091
              r_mtl_trns_rec.process_flag                :=  1;
              r_mtl_trns_rec.transaction_mode            :=  3;
              r_mtl_trns_rec.lock_flag                   :=  2;
              r_mtl_trns_rec.locator_id                  :=  l_loc_id;
              r_mtl_trns_rec.last_update_date            :=  SYSDATE;
              r_mtl_trns_rec.last_updated_by             :=  fnd_global.user_id;
              r_mtl_trns_rec.creation_date               :=  SYSDATE;
              r_mtl_trns_rec.created_by                  :=  fnd_global.user_id;
              r_mtl_trns_rec.inventory_item_id           :=  p_item_id;
              r_mtl_trns_rec.subinventory_code           :=  nvl(p_subinv,l_subinv_code);
              r_mtl_trns_rec.organization_id             :=  p_organization_id;
              r_mtl_trns_rec.transaction_quantity        :=  p_quantity;
              r_mtl_trns_rec.primary_quantity            :=  p_primary_qty;
              R_MTL_TRNS_REC.TRANSACTION_TYPE_ID         :=  R_TNS_REC.TRANSACTION_TYPE_ID;
              r_mtl_trns_rec.transaction_source_type_id  :=  r_tns_rec.transaction_source_type_id;
              r_mtl_trns_rec.transaction_action_id       :=  r_tns_rec.transaction_action_id;
              r_mtl_trns_rec.dst_segment1                :=  l_segment1;
              r_mtl_trns_rec.dst_segment2                :=  l_segment2;
              r_mtl_trns_rec.dst_segment3                :=  l_segment3;
              r_mtl_trns_rec.dst_segment4                :=  l_segment4;
              r_mtl_trns_rec.dst_segment5                :=  l_segment5;
              r_mtl_trns_rec.dst_segment6                :=  l_segment6;
              r_mtl_trns_rec.dst_segment7                :=  l_segment7;
              r_mtl_trns_rec.dst_segment8                :=  l_segment8;
              r_mtl_trns_rec.dst_segment9                :=  l_segment9;
              r_mtl_trns_rec.dst_segment10               :=  l_segment10;
              r_mtl_trns_rec.transaction_interface_id    :=  l_transaction_id;
              r_mtl_trns_rec.transaction_header_id       :=  l_transaction_id;
              r_mtl_trns_rec.transaction_reference       :=  p_trans_type_ref;
              IF g_l_lpn_id >0 THEN
                 r_mtl_trns_rec.transfer_lpn_id      := g_l_lpn_id;

                 dbms_output.put_line('g_l_lpn_id: '||g_l_lpn_id);
              END IF;

              -- Insert MTI for inv open transactions.
              insert into MTL_TRANSACTIONS_INTERFACE values R_MTL_TRNS_REC;
              dbms_output.put_line('Record inserted into mtl_transactions_interface with transaction_header_id: '||l_transaction_id);
              g_l_transaction_id := l_transaction_id;

              --dbms_output.put_line('Inserted a record'||l_transaction_id);
              x_return_status := fnd_api.g_ret_sts_success;
          EXCEPTION WHEN OTHERS THEN
                 fnd_file.put_line (fnd_file.output,'in exception ---- inserting data into  MTL_TRANSACTIONS_INTERFACE : '|| SQLERRM);
                 dbms_output.put_line('Error'||SQLERRM);
                 x_return_status := fnd_api.g_ret_sts_error;
                 x_msg_count := x_msg_count+1;
                 x_msg_data := 'Error While Inserting data to MTL_TRANSACTIONS_INTERFACE :'||SQLERRM;
          END;

  DBMS_OUTPUT.PUT_LINE('STEP1');
         IF x_return_status != fnd_api.g_ret_sts_error  THEN --Successful return of first insert
            IF p_lot_no IS NOT NULL THEN
               --Derive lot expiration date for the lot number
               BEGIN
                    SELECT expiration_date
                    INTO l_expiration_date
                    FROM MTL_LOT_NUMBERS
                    WHERE lot_number = p_lot_no
                    AND inventory_item_id = p_item_id
                    AND organization_id = p_organization_id;
                    
               EXCEPTION WHEN OTHERS THEN
                 dbms_output.put_line('Exception in fetching lot expiration_date: '||sqlerrm);
                 l_expiration_date:= null;
                 DBMS_OUTPUT.PUT_LINE('STEP2');
               END;
DBMS_OUTPUT.PUT_LINE('STEP3');
               dbms_output.put_line('Expiration_date: '||l_expiration_date);
DBMS_OUTPUT.PUT_LINE('STEP3.1');
               BEGIN
               DBMS_OUTPUT.PUT_LINE('STEP4');
                    --Insert lot number interface if the item is lot controlled.
                    r_mtl_lot_rec := NULL;
                    g_l_transaction_id := l_transaction_id;
                    r_mtl_lot_rec.transaction_interface_id    := l_transaction_id;
                    r_mtl_lot_rec.source_code                 := p_transaction_type;
                    r_mtl_lot_rec.source_line_id              := l_transaction_id;
                    r_mtl_lot_rec.last_update_date            := SYSDATE;
                    r_mtl_lot_rec.last_updated_by             := FND_GLOBAL.USER_ID;
                    r_mtl_lot_rec.creation_date               := SYSDATE;
                    r_mtl_lot_rec.created_by                  := FND_GLOBAL.USER_ID;
                    r_mtl_lot_rec.last_update_login           := FND_GLOBAL.LOGIN_ID ;
                    r_mtl_lot_rec.lot_number                  := p_lot_no;
                    r_mtl_lot_rec.lot_expiration_date         := l_expiration_date;
                    r_mtl_lot_rec.transaction_quantity        := p_quantity   ;
                    r_mtl_lot_rec.primary_quantity            := p_quantity;
                    r_mtl_lot_rec.serial_transaction_temp_id  := l_transaction_id  ;
DBMS_OUTPUT.PUT_LINE('STEP5');
                    INSERT INTO  MTL_TRANSACTION_LOTS_INTERFACE VALUES r_mtl_lot_rec;
                    DBMS_OUTPUT.PUT_LINE('STEP6');
               EXCEPTION WHEN OTHERS THEN
                   x_return_status := fnd_api.g_ret_sts_error;
                   x_msg_count := x_msg_count+1;
                   x_msg_data := 'Error While Inserting data to MTL_TRANSACTION_LOTS_INTERFACE :'||SQLERRM;
                   DBMS_OUTPUT.PUT_LINE('STEP7');
               END;
            END IF;
DBMS_OUTPUT.PUT_LINE('STEP8');
            COMMIT;
            --call Inventory open transaction api to perform transfer of material from source locator to destination locator.
            DBMS_OUTPUT.PUT_LINE('STEP9');
            x_return_status := null;
            x_msg_count := 0;
            X_MSG_DATA := null;
            dbms_output.put_line('Before Calling process_transactions'||l_transaction_id);
            lc_result := inv_txn_manager_pub.process_transactions
                                                 (p_api_version           => 1.0,
                                                  p_init_msg_list         => fnd_api.g_false,
                                                  p_commit                => fnd_api.g_true,
                                                  p_validation_level      => fnd_api.g_valid_level_full,
                                                  x_return_status         => x_return_status,
                                                  x_msg_count             => x_msg_count,
                                                  x_msg_data              => x_msg_data,
                                                  x_trans_count           => l_trans_count,
                                                  p_table                 => 1,
                                                  P_HEADER_ID             => L_TRANSACTION_ID
                                                 );
                                                 DBMS_OUTPUT.PUT_LINE('STEP10');
            dbms_output.put_line('Return Status'||x_return_status);
            IF NVL(x_return_status,'N') <> FND_API.g_ret_sts_success THEN
               x_return_status := FND_API.g_ret_sts_error;
               dbms_output.put_line('Return Status: '||x_return_status);
               dbms_output.put_line('Message: '||x_msg_data);
               DBMS_OUTPUT.PUT_LINE('STEP11');
            END IF;
         END IF;
         COMMIT;
      EXCEPTION WHEN OTHERS THEN
                dbms_output.put_line('In Exception'||sqlerrm);
                fnd_msg_pub.count_and_get (p_count        => x_msg_count,
                                           p_data         => x_msg_data,
                                           p_encoded      =>
                                          fnd_api.g_false
                                          );
                                          DBMS_OUTPUT.PUT_LINE('STEP12');
                 ROLLBACK;



end PKMTL_TRANSACTION;

Note :In the above procedure change the l_segment values accordingly.

Step2 : Run the Below anonymous block and pass the parameters accordingly for "Miscellaneous issue"

declare
X_RETURN_STATUS     varchar2(1000);
X_MSG_COUNT         number;
X_MSG_DATA          VARCHAR2(1000);

BEGIN
  
    BEGIN
      PKMTL_TRANSACTION(287955, 207, 'Stores',NULL, NULL, 'Ea', -1, 'Miscellaneous issue',
                NULL, null, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);
                
dbms_output.put_line('X_RETURN_STATUS->'||X_RETURN_STATUS||'    X_MSG_COUNT->'||X_MSG_COUNT||'    X_MSG_DATA->'||X_MSG_DATA);

    END;

Run the Below anonymous block and pass the parameters accordingly for "Miscellaneous receipt"

declare
X_RETURN_STATUS     varchar2(1000);
X_MSG_COUNT         number;
X_MSG_DATA          VARCHAR2(1000);

BEGIN
  
    BEGIN
      PKMTL_TRANSACTION(287955, 207, 'Stores',NULL, NULL, 'Ea', 1, 'Miscellaneous receipt',
                NULL, null, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);
                
dbms_output.put_line('X_RETURN_STATUS->'||X_RETURN_STATUS||'    X_MSG_COUNT->'||X_MSG_COUNT||'    X_MSG_DATA->'||X_MSG_DATA);

    END;