[Home] [Help]
View: OKL_BPD_TRX_SUMMARY_UV
View Text
SELECT '1' CONTRACT_NUMBER
, SUBSTRB (PY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, PY.PARTY_ID
, CA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, HD.CURRENCY_CODE
, HD.DATE_CONSOLIDATED TRX_DATE
, SUM(PS.AMOUNT_DUE_ORIGINAL) AMOUNT_DUE_ORIGINAL
, SUM(PS.AMOUNT_DUE_REMAINING) AMOUNT_DUE_REMAINING
, HD.IBT_ID BILL_TO_SITE_USE_ID
, HD.IXX_ID CUSTOMER_ACCT_ID
, HD.ORG_ID
FROM AR_PAYMENT_SCHEDULES_ALL PS
, OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_B HD
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PY
, OKC_K_HEADERS_ALL_B CN
WHERE PS.CLASS IN ('INV'
, 'CM')
AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND CA.CUST_ACCOUNT_ID = HD.IXX_ID
AND PY.PARTY_ID = CA.PARTY_ID
AND CN.ID (+) = ST.KHR_ID GROUP BY SUBSTRB (PY.PARTY_NAME
, 1
, 50)
, PY.PARTY_ID
, CA.ACCOUNT_NUMBER
, HD.CONSOLIDATED_INVOICE_NUMBER
, HD.CURRENCY_CODE
, HD.DATE_CONSOLIDATED
, HD.IBT_ID
, HD.IXX_ID
, HD.ORG_ID
Columns
Name |
CONTRACT_NUMBER |
CUSTOMER_NAME |
PARTY_ID |
CUSTOMER_NUMBER |
CONSOLIDATED_INVOICE_NUMBER |
CURRENCY_CODE |
DUE_DATE |
AMOUNT |
BALANCE |
BILL_TO_SITE_USE_ID |
CUSTOMER_ACCT_ID |
ORG_ID |