SELECT OOH.ORDER_NUMBER,
OOH.FLOW_STATUS_CODE HEADERSTATUS,
OOH.ORDERED_DATE ORDER_DATE,
OTT.NAME ORDER_TYPE,
HP.PARTY_NUMBER CUSTOMER_NUMBER,
HP.PARTY_NAME CUSTOMER_NAME,
QLHT.NAME PRICELIST_NAME,
RSA.NAME SALESREP_NAME,
HCSU1.LOCATION CUST_SHIPTO_LOC,
HL1.ADDRESS1 SHIPTO_ADDRESS,
HL1.CITY SHIPTO_CITY,
HL1.POSTAL_CODE SHIPTO_POSTAL_CODE,
HCSU2.LOCATION CUST_BILLTO_LOC,
HL2.ADDRESS1 BILLTO_ADDRESS,
HL2.CITY BILLTO_CITY,
HL2.POSTAL_CODE BILLTO_POSTAL_CODE,
OOL.ORDERED_ITEM,
OOL.ORDERED_QUANTITY,
OOL.ORDER_QUANTITY_UOM UOM,
OOL.UNIT_SELLING_PRICE UNIT_PRICE,
OOL.FLOW_STATUS_CODE LINESTATUS,
WDD.RELEASED_STATUS RELEASED_STATUS,
WDA.DELIVERY_ID DELIVERYID,
WND.DELIVERY_TYPE DELIVERYTYPE,
RCTA.TRX_NUMBER INVOICENO,
RCTA.TRX_DATE INVOICEDATE,
RCTLA.LINE_TYPE,
ARAA.AMOUNT_APPLIED INVOICE_AMOUNT,
ACRA.RECEIPT_NUMBER RECEIPTNO,
ACRA.RECEIPT_DATE RECEIPTDATE,
ACRA.AMOUNT RECEIPTAMOUNT,
ACRA.TYPE RECEIPTTYPE,
APSA.AMOUNT_APPLIED PAYMENTAMOUNT,
HCAA.ACCOUNT_NUMBER ACCTNO,
HCAA.ACCOUNT_NAME ACCTNAME
FROM OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
OE_TRANSACTION_TYPES_TL OTT,
HZ_PARTIES HP,
QP_LIST_HEADERS_TL QLHT,
RA_SALESREPS_ALL RSA,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCT_SITES_ALL HCAS1,
HZ_CUST_SITE_USES_ALL HCSU1,
HZ_LOCATIONS HL1,
HZ_CUST_ACCT_SITES_ALL HCAS2,
HZ_CUST_SITE_USES_ALL HCSU2,
HZ_LOCATIONS HL2,
MTL_SYSTEM_ITEMS_B MSIB,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUSTOMER_TRX_LINES_ALL RCTLA,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
AR_CASH_RECEIPTS_ALL ACRA,
AR_PAYMENT_SCHEDULES_ALL APSA,
HZ_CUST_ACCOUNTS_ALL HCAA
WHERE OOH.ORG_ID = 204
AND OOH.ORDER_NUMBER = 15025
AND OOH.HEADER_ID = OOL.HEADER_ID
AND OOH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
AND OOH.SOLD_TO_ORG_ID = HP.PARTY_ID
AND OOH.PRICE_LIST_ID = QLHT.LIST_HEADER_ID
AND OOH.SALESREP_ID = RSA.SALESREP_ID
AND HCAS1.CUST_ACCT_SITE_ID = HCSU1.CUST_ACCT_SITE_ID
AND OOH.SHIP_TO_ORG_ID = HCSU1.SITE_USE_ID
AND HPS.LOCATION_ID = HL1.LOCATION_ID
AND HPS.PARTY_SITE_ID = HCAS2.PARTY_SITE_ID
AND HCAS2.CUST_ACCT_SITE_ID = HCSU2.CUST_ACCT_SITE_ID
AND OOH.SHIP_TO_ORG_ID = HCSU2.SITE_USE_ID
AND HPS.LOCATION_ID = HL2.LOCATION_ID
AND OOL.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND OOL.ORG_ID = MSIB.ORGANIZATION_ID
AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID
AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID
AND RCTA.CUSTOMER_TRX_ID = RCTLA.CUSTOMER_TRX_ID
AND RCTA.INTERFACE_HEADER_ATTRIBUTE1 = TO_CHAR (OOH.ORDER_NUMBER)
AND RCTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR (OOL.LINE_ID)
AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND ARAA.CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID
AND ARAA.APPLIED_PAYMENT_SCHEDULE_ID = APSA.PAYMENT_SCHEDULE_ID
AND ACRA.PAY_FROM_CUSTOMER = HCAA.CUST_ACCOUNT_ID
