/*Back ordered Ageing Report */
SELECT TRUNC (SYSDATE) - TRUNC (wdd.creation_date) age,
source_header_type_name,
hca.customer_class_code,
hca.ACCOUNT_NUMBER cust_number,
wdd.organization_id
FROM wsh_delivery_details wdd,
hz_cust_accounts hca,
oe_order_headers_all ooha,
oe_order_lines_all oola
WHERE 1 = 1
AND ooha.header_id = oola.header_id
AND wdd.source_header_id = ooha.header_id
AND wdd.source_line_id = oola.line_id
AND wdd.inventory_item_id = oola.inventory_item_id
AND wdd.customer_id = hca.cust_account_id
AND wdd.source_code = 'OE'
AND wdd.organization_id = NVL (P_ORG_ID, wdd.organization_id)
AND wdd.SOURCE_HEADER_TYPE_ID = 1007 -- Order type ID
AND SUBSTR (hca.customer_class_code, 1, 2) IN
NVL (p_business, SUBSTR (hca.customer_class_code, 1, 2)) -- Business code
AND wdd.released_status IN ('B')
No comments:
Post a Comment