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