處理時間：每月最後一週，即可開始檢查有沒有異常的庫存交易需要處理
建議參考文件：Oracle Resolving Period Close Inventory Pending Transaction
How to review pending transaction by inventory accounting period ?
Responsibility : Inventory super user
Navigator : Accounting Close Cycle > Inventory Accounting Periods
==> Choose the organization you want to view.
You can use below sql to view each pending item ...
------------------------- Resolution Required -------------------------
Unprocessed Material
| SELECT mmtt.transaction_temp_id,mmtt.organization_id , mp.organization_code inv_org , TRUNC (mmtt.transaction_date) trx_date , msib.segment1 item , (SELECT license_plate_number FROM wms_license_plate_numbers WHERE lpn_id = mmtt.lpn_id) lpn , (SELECT license_plate_number FROM wms_license_plate_numbers WHERE lpn_id = mmtt.transfer_lpn_id) transfer_lpn , mmtt.primary_quantity , mmtt.subinventory_code , mmtt.transfer_subinventory , mmtt.error_explanation , mmtt.ERROR_CODE , mmtt.source_line_id , mmtt.rcv_transaction_id , mts.description source_type , mm.transaction_type_name , mmtt.process_flag ,mmtt.transaction_source_type_id FROM mtl_material_transactions_temp mmtt , mtl_parameters mp , mtl_system_items_b msib , mtl_txn_source_types mts , mtl_transaction_types mm WHERE mp.organization_id = mmtt.organization_id AND msib.inventory_item_id = mmtt.inventory_item_id AND msib.organization_id = mmtt.organization_id AND mts.transaction_source_type_id = mmtt.transaction_source_type_id AND mmtt.transaction_type_id = mm.transaction_type_id AND mp.attribute6 = 'Y' AND mp.organization_id IN (91,92, 93,94,101,102,325,374) and (mts.description ='Inventory' or mmtt.process_flag ='E' ) ORDER BY 3; |
- Uncosted Material (只需處裡cost_flag=E 的)
| SELECT (SELECT organization_code FROM mtl_parameters WHERE organization_id = mmt.organization_id) org, mmt.organization_id, transaction_id, transaction_type_id, transaction_quantity, transaction_date, transaction_group_id, ERROR_CODE, error_explanation FROM mtl_material_transactions mmt WHERE (costed_flag = 'N' OR costed_flag = 'E') AND organization_id IN (91, 92, 93, 94, 101, 102, 325, 374); |
Pending WIP costing
| 目前無SQL |
- Uncosted WSM
| 目前無SQL |
- Pending WSM interface
| 目前無SQL |
- ------------------------- Resolution Recommendend -------------------------
Pending Receiving
| SELECT rti.to_organization_id, Pending Material SELECT mti.organization_id, |
- Pending Shop Floor Move
| 無 |
- ------------------------- Unprocessed Shipping Transactions -------------------------
Pending Transactions
| SELECT mp.organization_code inv_org , trunc(x.initial_pickup_date) shipped_date , x.NAME delivery_no , x.status_code , wl.meaning , 'Pending Trip-Stop' error_status FROM wsh_new_deliveries x , mtl_parameters mp , wsh_lookups wl WHERE mp.organization_id = x.organization_id AND mp.attribute6 = 'Y' AND wl.lookup_type = 'DELIVERY_STATUS' AND wl.lookup_code = x.status_code AND x.status_code = 'CL' AND EXISTS ( SELECT wdl.delivery_id FROM wsh_delivery_legs wdl , wsh_trip_stops wts WHERE wts.pending_interface_flag = 'Y' AND wdl.pick_up_stop_id = wts.stop_id AND x.delivery_id = wdl.delivery_id AND wts.stop_location_id = x.initial_pickup_location_id) AND NVL (x.shipment_direction, 'O') IN ('O', 'IO') UNION ALL SELECT mp.organization_code inv_org , trunc(x.initial_pickup_date) shipped_date , x.NAME delivery_no , x.status_code , wl.meaning , 'Not Closed' error_status FROM wsh_new_deliveries x , mtl_parameters mp , wsh_lookups wl WHERE mp.organization_id = x.organization_id AND mp.attribute6 = 'Y' AND wl.lookup_type = 'DELIVERY_STATUS' AND wl.lookup_code = x.status_code AND x.status_code = 'CO'; |
留言列表
Oracle eBS (11.5.10.2) (13)