Monday, 13 May 2013

Query to Back ordered Ageing Report


/*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