Introduction:
This Post illustrates the steps required to check the stuck records in Inventory transactions.
Scripts:
–Unprocessed Material Transactions
SELECT *
FROM mtl_material_transactions_temp
WHERE organization_id = (SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND transaction_date <
(SELECT schedule_close_date + 1
FROM apps.org_acct_periods
WHERE organization_id =
(SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND period_name = :p_period_name)
AND NVL (transaction_status, 0) <> 2;
–Uncosted Material Transactions
SELECT *
FROM mtl_material_transactions mmt
WHERE organization_id = (SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND transaction_date <
(SELECT schedule_close_date + 1
FROM apps.org_acct_periods
WHERE organization_id =
(SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND period_name = :p_period_name)
AND costed_flag IS NOT NULL;
–Pending WIP Costing Transactions
SELECT *
FROM wip_cost_txn_interface
WHERE organization_id = (SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND transaction_date <
(SELECT schedule_close_date + 1
FROM apps.org_acct_periods
WHERE organization_id =
(SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND period_name = :p_period_name);
–Uncosted WSM Transactions
SELECT *
FROM wsm_split_merge_transactions
WHERE organization_id = (SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND transaction_date <
(SELECT schedule_close_date + 1
FROM apps.org_acct_periods
WHERE organization_id =
(SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND period_name = :p_period_name);
–Uncosted WSM Interface
SELECT *
FROM wsm_split_merge_txn_interface
WHERE organization_id = (SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND transaction_date <
(SELECT schedule_close_date + 1
FROM apps.org_acct_periods
WHERE organization_id =
(SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND period_name = :p_period_name)
—
–Pending Receiving Transactions
SELECT *
FROM rcv_transactions_interface
WHERE transaction_date <
(SELECT schedule_close_date + 1
FROM apps.org_acct_periods
WHERE organization_id =
(SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND period_name = :p_period_name)
AND destination_type_code = ‘INVENTORY’;
–Pending Material Transactions
SELECT *
FROM mtl_transactions_interface
WHERE organization_id = (SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND transaction_date <
(SELECT schedule_close_date + 1
FROM apps.org_acct_periods
WHERE organization_id =
(SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND period_name = :p_period_name)
AND process_flag <> 9;
–Pending Shop Floor Move Transactions
SELECT *
FROM wip_move_txn_interface
WHERE organization_id = (SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND transaction_date <
(SELECT schedule_close_date + 1
FROM apps.org_acct_periods
WHERE organization_id =
(SELECT organization_id
FROM apps.org_organization_definitions
WHERE organization_code = :p_organization_code)
AND period_name = :p_period_name)
–Unprocessed Shipping Transactions
SELECT wdd.source_header_id, ooh.order_number, wnd.delivery_id, wnd.NAME,
wdd.delivery_detail_id, wdl.pick_up_stop_id, wdd.inv_interfaced_flag
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
oe_order_headers_all ooh,
oe_order_lines_all ool
WHERE wdd.source_code = ‘OE’
AND wdd.released_status = ‘C’
AND wdd.inv_interfaced_flag IN (‘N’, ‘P’)
AND wdd.organization_id = &organization_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wnd.status_code IN (‘CL’, ‘IT’)
AND wdl.delivery_id = wnd.delivery_id
AND TRUNC (wts.actual_departure_date) BETWEEN ‘1-FEB-2020′
AND ’29-FEB-2020’
AND wdl.pick_up_stop_id = wts.stop_id
AND wdd.source_header_id = ooh.header_id
AND wdd.source_line_id = ool.line_id
Queries
Do drop a note by writing us at venkatesh.b@doyensys.com or use the comment section below to ask your questions
