[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT moqd.create_transaction_id
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.subinventory_code is not null
AND ( moqd.primary_transaction_quantity = 0
OR moqd.transaction_quantity = 0 )
AND moqd.organization_id = NVL(cp_org_id,moqd.organization_id)
AND moqd.inventory_item_id = NVL(cp_item_id, moqd.inventory_item_id)
ORDER BY moqd.create_transaction_id;
JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
sqltxt := ' select mif.item_number||''(''||mif.inventory_item_id||'')'' "Item (Id)"' ||
' , mp.organization_code|| '' (''||moqd.organization_id ||'')'' "Organization|Code (Id)"' ||
' , moqd.subinventory_code "Subinv"' ||
' , moqd.locator_id "Stock Locator"' ||
' , moqd.revision "Rev"' ||
' , moqd.primary_transaction_quantity "Prim Qty"' ||
' , moqd.create_transaction_id "Create txn_id"' ||
' from mtl_onhand_quantities_detail moqd, mtl_parameters mp,' ||
' mtl_item_flexfields mif' ||
' where subinventory_code is not null ' ||
' and moqd.organization_id = mp.organization_id' ||
' and (primary_transaction_quantity = 0 ' ||
' or transaction_quantity = 0 ) ' ||
' and moqd.inventory_item_id = mif.inventory_item_id(+)' ||
' and moqd.organization_id =mif.organization_id(+) ' ;
sqltxt := ' SELECT mmt.transaction_id "Txn Id"' ||
' , mp.organization_code|| '' (''||mmt.organization_id ||'')'' "Organization|Code (Id)"' ||
' , mif.item_number ||'' (''|| mmt.inventory_item_id ||'')'' "Item (Id)"' ||
' , mmt.transaction_date "Txn Date"' ||
' , mmt.transaction_quantity "Txn Qty"' ||
' , mmt.primary_quantity "Prim Qty"' ||
' , mmt.transaction_uom "Uom"' ||
' , tt.transaction_type_name ||'' (''||mmt.transaction_type_id||'')'' "Txn Type (Id)"' ||
' , ml.meaning || '' ('' ||mmt.transaction_action_id|| '')''' ||
' "Txn Action (Id)"' ||
' , st.transaction_source_type_name ||'' (''|| mmt.transaction_source_type_id ||'')'' "Txn Source Type (Id)"' ||
' , mmt.subinventory_code "Subinv"' ||
' , mmt.locator_id "Stock Locator"' ||
' , mmt.revision "Rev"' ||
' , mmt.physical_adjustment_id "Physical Adj Id"' ||
' , mmt.transaction_source_id "Txn Source Id"' ||
' , mmt.transaction_source_name "Txn Source"' ||
' FROM mtl_material_transactions mmt , mtl_parameters mp ' ||
' , mtl_item_flexfields mif' ||
' , mtl_transaction_types tt' ||
' , mtl_txn_source_types st' ||
' , mfg_lookups ml' ||
' WHERE mmt.transaction_id = ' || rec_tnx.create_transaction_id ||
' and mmt.organization_id = mp.organization_id' ||
' AND mmt.inventory_item_id = mif.inventory_item_id(+)' ||
' AND mmt.organization_id = mif.organization_id(+)' ||
' AND mmt.transaction_type_id = tt.transaction_type_id(+)' ||
' AND mmt.transaction_source_type_id = st.transaction_source_type_id(+)' ||
' AND mmt.transaction_action_id=ml.lookup_code' ||
' AND ml.lookup_type = ''MTL_TRANSACTION_ACTION''' ||
' ORDER BY costed_flag, transaction_id ';