Product: | ASO - Order Capture |
---|---|
Description: | View related to Invoice Information in OC |
Implementation/DBA Data: | APPS.ASO_I_INVOICES_V |
SELECT DISTINCT TRX.TRX_NUMBER INVOICE_NUMBER
, TRX.TRX_DATE INVOICE_DATE
, HD.ORDER_NUMBER
, HD.HEADER_ID
, HD.ORDER_CATEGORY_CODE
, SL.MEANING ORDER_CATEGORY_DESC
, OT.NAME ORDER_TYPE
, HD.ORDER_TYPE_ID
, HD.CUST_PO_NUMBER
, AC.CUSTOMER_NAME
, HD.SOLD_TO_ORG_ID
, AC.ACCOUNT_NUMBER
, HD.ORDERED_DATE
, OT.SHIP_METHOD_CODE
, TRX.BILL_TO_CUSTOMER_ID
, L.LINE_ID
FROM OE_ORDER_HEADERS_ALL HD
, OE_ORDER_LINES_ALL L
, OE_LOOKUPS SL
, RA_CUSTOMER_TRX_ALL TRX
, RA_CUSTOMER_TRX_LINES_ALL TRXL
, ASO_I_ORDER_TYPES_V OT
, ASO_I_CUST_ACCOUNTS_V AC
WHERE HD.HEADER_ID = L.HEADER_ID
AND HD.ORDER_TYPE_ID = OT.ORDER_TYPE_ID
AND (L.CREDIT_INVOICE_LINE_ID = TRXL.CUSTOMER_TRX_LINE_ID OR L.LINE_ID = DECODE(RTRIM(TRXL.INTERFACE_LINE_ATTRIBUTE6
, '0123456789' )
, NULL
, TO_NUMBER(TRXL.INTERFACE_LINE_ATTRIBUTE6)
, NULL))
AND TRX.CUSTOMER_TRX_ID = TRXL.CUSTOMER_TRX_ID
AND HD.SOLD_TO_ORG_ID = AC.CUST_ACCOUNT_ID
AND SL.LOOKUP_CODE = HD.ORDER_CATEGORY_CODE
AND SL.LOOKUP_TYPE = 'ORDER_CATEGORY'