OM Sales Order Inventory Details (Oracle Fusion SQL)

Supply Chain & Manufacturing (SCM) Order Management (OM-Fusion-SQL's)
1

This OM Sales Order Inventory Details query displays sales order and invoice details for each customer, including fulfillment quantities, pricing, and sales rep information, to support financial reconciliation and performance reporting across Order Management and Receivables modules.

#sqlquery

SELECT
--SQL4FUSION (An Orbit Analytics Project) 
--This query fetches sales order and invoice details across Oracle Order Management and Receivables modules
  HAOU.NAME OPERATING_UNIT,
   HP.PARTY_NAME,
  HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
  DHA.ORDER_NUMBER,
  DHA.ORDERED_DATE,
  RCTA.TRX_DATE INVOICE_DATE,
  RCTA.TRX_NUMBER INVOICE_NUMBER,
  RCTLA.LINE_NUMBER INVOICE_LINE_NUMBER,
  RCTLA.EXTENDED_AMOUNT INVOICE_LINE_AMOUNT,
  RCTLA.DESCRIPTION INVOICE_LINE_DESCRIPTION,
  NVL (RCTLA.QUANTITY_INVOICED, 0) INVOICED_QUANTITY,
  NVL (RCTLA.UNIT_SELLING_PRICE, 0) INVOICED_SELLING_PRICE,
  HCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID,
   HP.PARTY_ID,
   HP.CATEGORY_CODE,
  HCA.CUSTOMER_TYPE,
  HCA.STATUS,
  DFLA.ORDERED_QTY,
  DFLA.CANCELED_QTY,
  DFLA.SHIPPED_QTY,
  DFLA.ORDERED_UOM,
  DFLA.FULFILLED_QTY,
  DFLA.TAX_EXEMPT_FLAG,
  DFLA.UNIT_SELLING_PRICE,
  NVL (DFLA.UNIT_SELLING_PRICE, 0) * NVL (DFLA.ORDERED_QTY, 0) ORDER_LINE_AMOUNT,
  DFLA.UNIT_LIST_PRICE,
  DFLA.STATUS_CODE,
  DHA.STATUS_CODE FLOW_STATUS_CODE,
  JRS.SALESREP_NUMBER,
  DLA.INVENTORY_ITEM_ID,
  DHA.ORG_ID,
  DHA.HEADER_ID,
  DHA.SOURCE_ORG_ID,
  DHA.FULFILL_ORG_ID,
  DLA.LINE_ID,
  DHA.OPEN_FLAG,
  DHA.PARTIAL_SHIP_ALLOWED_FLAG,
  DHA.CANCELED_FLAG,
  DLA.OPEN_FLAG AS LINE_OPEN_FLAG,
  DLA.CANCELED_FLAG AS LINE_CANCEL_FLAG,
  RCTA.DEFAULT_TAX_EXEMPT_FLAG,
  RCTA.REASON_CODE,
  RCTA.STATUS_TRX,
  HAOU.ORGANIZATION_ID USER_SEC_ORG_ID
 FROM
  DOO_FULFILL_LINES_ALL DFLA,
  DOO_HEADERS_ALL DHA,
  DOO_LINES_ALL DLA,
  HZ_PARTIES HP,
  RA_CUSTOMER_TRX_ALL RCTA,
  RA_CUSTOMER_TRX_LINES_ALL RCTLA,
  JTF_RS_SALESREPS JRS,
  HZ_CUST_ACCOUNTS HCA,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND DFLA.HEADER_ID = DHA.HEADER_ID 
  AND DFLA.HEADER_ID = DLA.HEADER_ID
  AND DHA.HEADER_ID = DLA.HEADER_ID
  AND RCTLA.LINE_TYPE = 'LINE'
  AND RCTLA.SALES_ORDER_LINE IS NOT NULL
  AND HCA.PARTY_ID =  HP.PARTY_ID
  AND RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
  AND RCTA.ORG_ID = RCTLA.ORG_ID
  AND DHA.ORG_ID = RCTA.ORG_ID
  AND DHA.ORG_ID = HAOU.ORGANIZATION_ID
    AND DHA.SALESPERSON_ID = JRS.RESOURCE_SALESREP_ID