VIEW: APPS.OKL_BPD_TRX_SUMMARY_UV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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