Stock Upload

Stock Upload insert into pran_item_stock_stg_M(SOURCE_CODE,SOURCE_LINE_ID,SOURCE_HEADER_ID,INVENTORY_ITEM_CODE,PROCESS_FLAG,VALIDATION_REQUIRED,TRANSACTION_MODE,TRANSACTION_QUANTITY,ORGANIZATION_CODE,SUBINVENTORY_CODE,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY)
select 'Adjusted Stock Upload' SOURCE_CODE, 1 SOURCE_LINE_ID, 1 SOURCE_HEADER_ID, ITEM_CODE INVENTORY_ITEM_CODE, 1 PROCESS_FLAG, 2 VALIDATION_REQUIRED, 3 TRANSACTION_MODE, qty TRANSACTION_QUANTITY, ORG_CODE ORGANIZATION_CODE, SUBINV_CODE SUBINVENTORY_CODE, TO_DATE('29/05/2015','dd-mm-yy') LAST_UPDATE_DATE, '1110' last_updated_by, TO_DATE('29/05/2015','dd-mm-yy') CREATION_DATE, '1110' CREATED_BY
from apps.xxtemp_org where upper(file_name) like upper(&flnm)



For Stock Upload:
Crated by Mahfuz


CREATE TABLE APPS.PRAN_ITEM_STOCK_STG_M
(
TRANSACTION_INTERFACE_ID NUMBER,
SOURCE_CODE VARCHAR2(30 BYTE),
SOURCE_LINE_ID NUMBER,
SOURCE_HEADER_ID NUMBER,
INVENTORY_ITEM_CODE VARCHAR2(10 BYTE),
PROCESS_FLAG NUMBER(1),
VALIDATION_REQUIRED NUMBER,
TRANSACTION_MODE NUMBER,
TRANSACTION_QUANTITY NUMBER,
ORGANIZATION_CODE VARCHAR2(10 BYTE),
PRIMARY_QUANTITY NUMBER,
TRANSACTION_UOM VARCHAR2(3 BYTE),
TRANSACTION_DATE DATE,
SUBINVENTORY_CODE VARCHAR2(10 BYTE),
LOCATOR_ID NUMBER,
TRANSACTION_TYPE_ID NUMBER,
TRANSACTION_COST NUMBER,
DISTRIBUTION_ACCOUNT_ID NUMBER,
LAST_UPDATE_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER NOT NULL,
CREATION_DATE DATE NOT NULL,
CREATED_BY NUMBER NOT NULL,
FLAG VARCHAR2(2 BYTE)
);


insert into pran_item_stock_stg_M(SOURCE_CODE,SOURCE_LINE_ID,SOURCE_HEADER_ID,INVENTORY_ITEM_CODE,PROCESS_FLAG,VALIDATION_REQUIRED,TRANSACTION_MODE,TRANSACTION_QUANTITY,ORGANIZATION_CODE,SUBINVENTORY_CODE,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY) values ('Adjusted Stock Upload',1,1,'32706',1,2,3,'1738', 'G03','G03.FG',to_date('31/01/2015','dd-mm-yy'),1110,to_date('31/01/2015','dd-mm-yy'),1110);


CREATE OR REPLACE PROCEDURE APPS.pran_item_stock_stg_proc as
cursor c1 is

select 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,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
where si.INVENTORY_ITEM_CODE=i.SEGMENT1 and si.ORGANIZATION_CODE=o.ORGANIZATION_CODE and o.ORGANIZATION_ID=i.ORGANIZATION_ID and FLAG is null

;

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



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
)
values
(
mtl_material_transactions_s.NEXTVAL,
--c1_rec.source_code,
'Adjusted Stock Upload',
1,
1,
c1_rec.inventory_item_id,
1,
2,
3,
c1_rec.transaction_quantity,
sysdate,
1110,
sysdate,
1110,
c1_rec.organization_id,
--c1_rec.transaction_date,
to_date('31/01/2015','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
);
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;

end loop;

commit;
End pran_item_stock_stg_proc;
/
Run Lunch process transaction manager.
Navigate: PFL Inventory Super User > Setup > Transactions > Interface Manager

select Material Transactions and then Tools > Launch Manager