SELECT 'Unprocessed Shipping Transactions' trntype, org.organization_id,
org.organization_code, org.organization_name, COUNT (*) totaltrn,
msi.segment1 item_code, error_explanation, ERROR_CODE,
wdd.delivery_detail_id, wnd.delivery_id, tt.shipment_number,
wdd.source_header_number, wdd.source_line_id, tt.transaction_date,
tt.expected_arrival_date, gp.period_code, msi.inventory_item_id,
wdd.source_line_number order_line_number, wdd.shipped_quantity,
tt.transaction_uom, tt.subinventory_code
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
mtl_transactions_interface tt,
mtl_system_items_b msi,
org_organization_definitions org,
gmf_period_statuses gp
WHERE wdd.source_code = 'OE'
AND wdd.released_status = 'C'
AND wdd.inv_interfaced_flag IN ('N', 'P')
AND wdd.organization_id = org.organization_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
--AND wdd.organization_id = tt.organization_id(+)
AND wdd.inventory_item_id = tt.inventory_item_id(+)
AND wnd.status_code IN ('CL', 'IT')
AND wdl.delivery_id = wnd.delivery_id
AND wts.pending_interface_flag IN ('Y', 'P')
AND gp.period_code = gp.period_code
AND wdd.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = wdd.organization_id
AND TRUNC (wts.actual_departure_date) >= TRUNC (gp.start_date)
AND TRUNC (wts.actual_departure_date) <= TRUNC (gp.end_date)
--AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN :P_FROM_DATE and :P_TO_DATE
---AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN TO_DATE('01-MAY-2018 00:00:00','DD-MON-YYYY HH24:MI:SS' ) AND
---TO_DATE('31-MAY-2018 23:59:59','DD-MON-YYYY HH24:MI:SS')
AND wdl.pick_up_stop_id = wts.stop_id
GROUP BY org.organization_id,
org.organization_code,
org.organization_name,
error_explanation,
ERROR_CODE,
wdd.shipped_quantity,
msi.inventory_item_id,
tt.shipment_number,
wdd.delivery_detail_id,
tt.transaction_uom,
wnd.delivery_id,
tt.subinventory_code,
msi.segment1,
wdd.source_line_number,
wdd.source_line_id,
tt.transaction_date,
wdd.source_header_number,
tt.expected_arrival_date,
gp.period_code
ORDER BY org.organization_name DESC;
No comments:
Post a Comment
Text Message