杂收数量导入on hand

--Concurrent programs:

--Process transaction interface  N: INV/Setup/Transactions/Interface Managers/Process transaction interface/Tools/Launch Manager

SELECT

*

FROM mtl_transactions_interface mti

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

 SELECT

*

FROM mtl_transaction_lots_interface

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

SELECT

*

FROM mtl_serial_numbers_interface

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

SELECT

*

FROM cst_comp_snap_interface

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

SELECT

mti.ERROR_CODE, mti.error_explanation

FROM mtl_transactions_interface mti

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

 --FOR ERROR INFORMATIONS

SELECT

*

FROM mtl_transactions_interface

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

 

--******************************************************************************

--delete data

 DELETE

FROM mtl_transactions_interface mti

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

 DELETE

FROM mtl_transaction_lots_interface

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

DELETE

FROM mtl_serial_numbers_interface

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

DELETE

FROM cst_comp_snap_interface

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

 --FOR ERROR INFORMATIONS

DELETE

FROM mtl_transactions_interface

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

 

--******************************************************************************

--base table

SELECT

*

FROM mtl_material_transactions

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

 SELECT

*

FROM mtl_transaction_accounts

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

SELECT

*

FROM mtl_transaction_lot_numbers

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

SELECT

*

FROM mtl_serial_numbers

WHERETRUNC(creation_date)=TRUNC(SYSDATE);

SELECT

*

FROM cst_comp_snapshot

 WHERETRUNC(creation_date)=TRUNC(SYSDATE);

--Dont ust xxx_temp table it will go directly to the base table

--******************************************************************************

 

--Table Relation:

--Lot

--mtl_transactions_interface.transaction_interface_id = mtl_transaction_lots_interface.transaction_interface_id

--serial

--mtl_transactions_interface.transaction_interface_id = mtl_serial_numbers_interface.transaction_interface_id

--lot and serial

--mtl_transactions_interface.transaction_interface_id = mtl_transaction_lots_interface.transaction_interface_id

--mtl_transaction_lots_interface.serial_transaction_temp_id = mtl_serial_numbers_interface.transaction_interface_id

--mtl_transactions_interface

 

SELECT

mti.ERROR_CODE, mti.error_explanation

FROM mtl_transactions_interface mti;

--******************************************************************************

 

--Miscellaneous transaction

DECLARE

  l_iface_rec        inv.mtl_transactions_interface%ROWTYPE;

 l_cur_mfg_org_id  NUMBER :=204;--Current Inv Organization

  l_user_id          NUMBER    :=1318;--User ID, Sysadmin here

BEGIN

  l_iface_rec.last_update_date:=SYSDATE;

 l_iface_rec.last_updated_by:= l_user_id;

l_iface_rec.creation_date:=SYSDATE;

 l_iface_rec.created_by:= l_user_id;

  l_iface_rec.last_update_login:=-1;

SELECT mtl_material_transactions_s.NEXTVAL

INTO l_iface_rec.transaction_interface_id

FROM DUAL;

   l_iface_rec.transaction_header_id:= l_iface_rec.transaction_interface_id;

   l_iface_rec.transaction_mode:=3;

  l_iface_rec.process_flag:=1;

  l_iface_rec.transaction_type_id:=42;             --mtl_transaction_type

--l_iface_rec.transaction_source_id := 2;

  l_iface_rec.organization_id:= l_cur_mfg_org_id;

  l_iface_rec.inventory_item_id:=20830;

   l_iface_rec.subinventory_code:='Stores';

 l_iface_rec.transaction_quantity:=500;

  l_iface_rec.transaction_uom:='Ea';

 l_iface_rec.transaction_date:=SYSDATE;

  l_iface_rec.distribution_account_id:=17347;

  l_iface_rec.source_code:='Test Only';

 l_iface_rec.source_header_id:=987654321;

l_iface_rec.source_line_id:=987654321;

INSERTINTO inv.mtl_transactions_interface

 VALUES l_iface_rec;

COMMIT;

END;