For Lot Control Apply

CREATE OR REPLACE PROCEDURE APPS.pran_item_stock_stg_proc_RLOT as
cursor c1 is

SELECT TRANSACTION_INTERFACE_ID,    SOURCE_CODE,    SOURCE_LINE_ID,    SOURCE_HEADER_ID,    INVENTORY_ITEM_ID,    INVENTORY_ITEM_CODE,    PRIMARY_UOM_CODE,    PROCESS_FLAG,    VALIDATION_REQUIRED,    TRANSACTION_MODE,    -1*SUM(TRANSACTION_QUANTITY) TRANSACTION_QUANTITY,    ORGANIZATION_ID,    ORGANIZATION_CODE,    PRIMARY_QUANTITY,    TRANSACTION_UOM,    TRANSACTION_DATE,    SUBINVENTORY_CODE,    LOCATOR_ID,    TRANSACTION_TYPE_ID,    TRANSACTION_COST,    DISTRIBUTION_ACCOUNT_ID,    LAST_UPDATE_DATE,    LAST_UPDATED_BY,    CREATION_DATE,    CREATED_BY FROM (
select distinct transaction_interface_id,source_code,source_line_id,
source_header_id,i.inventory_item_id, inventory_item_code , primary_uom_code ,process_flag,validation_required,
transaction_mode,transaction_quantity,o.organization_id, o.organization_code, primary_quantity,
primary_uom_code transaction_uom ,transaction_date ,subinventory_code,locator_id,
transaction_type_id,transaction_cost,op.MATERIAL_ACCOUNT distribution_account_id,
si.last_update_date,si.last_updated_by,si.creation_date,si.created_by from pran_item_stock_stg_M si, mtl_system_items_b i, org_organization_definitions o, mtl_parameters op
where o.ORGANIZATION_ID=op.ORGANIZATION_ID and op.ORGANIZATION_CODE=si.ORGANIZATION_CODE and  si.INVENTORY_ITEM_CODE=i.SEGMENT1
and si.ORGANIZATION_CODE=o.ORGANIZATION_CODE and o.ORGANIZATION_ID=i.ORGANIZATION_ID --and FLAG ='Y'--is null
 --and INVENTORY_ITEM_CODE='47428'  and o.ORGANIZATION_CODE='D27'
 )
 GROUP BY TRANSACTION_INTERFACE_ID,    SOURCE_CODE,    SOURCE_LINE_ID,    SOURCE_HEADER_ID,    INVENTORY_ITEM_ID,    INVENTORY_ITEM_CODE,    PRIMARY_UOM_CODE,    PROCESS_FLAG,    VALIDATION_REQUIRED,    TRANSACTION_MODE,     ORGANIZATION_ID,    ORGANIZATION_CODE,    PRIMARY_QUANTITY,    TRANSACTION_UOM,    TRANSACTION_DATE,    SUBINVENTORY_CODE,    LOCATOR_ID,    TRANSACTION_TYPE_ID,    TRANSACTION_COST,    DISTRIBUTION_ACCOUNT_ID,    LAST_UPDATE_DATE,    LAST_UPDATED_BY,    CREATION_DATE,    CREATED_BY;


c1_rec c1%rowtype;
v_organization_id number(5);
v_inventory_item_id number(10);
v_primary_uom_code varchar2(6);
v_inventory_location_id number(10);
v_subinventory_code varchar2(10);
v_code_combination_id number(10);
v_transaction_type_id number(8);
   interface_id number := 0;
Begin




for c1_rec in c1 loop
exit when c1%notfound;

begin

-- location
select
inventory_location_id,
subinventory_code
into v_inventory_location_id, v_subinventory_code
from mtl_item_locations
where subinventory_code =c1_rec.subinventory_code;


--Declare

--begin
  select mtl_material_transactions_s.nextval
  into interface_id
  from dual;



exception
when no_data_found then

v_organization_id :=null;
v_inventory_item_id :=null;
v_primary_uom_code :=null;
v_inventory_location_id :=null;
v_subinventory_code :=null;


end;


 

if

v_inventory_location_id is not null
and v_subinventory_code is not null then

update
pran_item_stock_stg_m set flag='Y' where
INVENTORY_ITEM_CODE=c1_rec.INVENTORY_ITEM_CODE
and organization_code =c1_rec.organization_code
and SUBINVENTORY_CODE =c1_rec.SUBINVENTORY_CODE
;



insert into mtl_transactions_interface
(
transaction_interface_id,
source_code,
source_header_id,
source_line_id,
inventory_item_id,
process_flag,
validation_required,
transaction_mode,
transaction_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by,
organization_id,
transaction_date,
transaction_type_id,
distribution_account_id,
transaction_uom,
subinventory_code,
locator_id,
TRANSACTION_COST,
SOURCE_LOT_NUMBER
)
values
(
--mtl_material_transactions_s.NEXTVAL,
interface_id,
--c1_rec.source_code,
'Stock Upload for Lot Control',
1,
1,
c1_rec.inventory_item_id,
1,
2,
3,
c1_rec.transaction_quantity,
sysdate,
1187,
sysdate,
1187,
c1_rec.organization_id,
--c1_rec.transaction_date,
to_date('09/01/2021','dd-mm-yy'),
--
42,
c1_rec.distribution_account_id,
--128305,
c1_rec.transaction_uom,
c1_rec.subinventory_code,
--c1_rec.locator_id,
v_inventory_location_id,
null,
'09012021'
);

else

update pran_item_stock_stg_m set flag='N' where
inventory_item_code=c1_rec.inventory_item_code
and organization_code =c1_rec.organization_code
and transaction_uom =c1_rec.transaction_uom
and locator_id =c1_rec.locator_id
and subinventory_code =c1_rec.subinventory_code
and distribution_account_id =c1_rec.distribution_account_id
and transaction_type_id =c1_rec.transaction_type_id;

end if;



Insert into apps.mtl_transaction_lots_interface
( transaction_interface_id
, source_code
, source_line_id
, lot_number
, lot_expiration_date
, transaction_quantity
  , PRIMARY_QUANTITY
, last_update_date
, last_updated_by
, creation_date
, created_by
)
Values
(interface_id
, 'Stock Upload for Lot Control'
, 1
, '09012021'
, to_date('09/01/2021','dd-mm-yy')
, c1_rec.transaction_quantity
, null
, SYSDATE
, 1187
, SYSDATE
, 1187
);

commit;
end loop;


End pran_item_stock_stg_proc_RLOT;
/