VIEW: APPS.OKL_BPD_TRX_SUM_ALL_STRMS_UV

Source

View Text - Preformatted

SELECT HD.IXX_ID , HD.ID CNR_ID , HD.DUE_DATE , PY.PARTY_NAME , HD.CURRENCY_CODE , HD.CONSOLIDATED_INVOICE_NUMBER , HD.DATE_CONSOLIDATED , HD.IRM_ID , ARM.NAME RECEIPT_METHOD_NAME , HD.INF_ID , NVL(INF.NAME,'NONE') NAME , LOC.ADDRESS1 , LOC.ADDRESS2 , LOC.ADDRESS3 , LOC.ADDRESS4 , LOC.CITY , LOC.STATE , LOC.POSTAL_CODE , LOC.COUNTRY , sum(ps.amount_due_original) sum_amount_due_original , sum(ps.AMOUNT_DUE_REMAINING) sum_balance FROM OKL_CNSLD_AR_HDRS_B HD,OKL_CNSLD_AR_LINES_V LN,OKL_CNSLD_AR_STRMS_V ST,OKL_STRM_TYPE_V SM,AR_PAYMENT_SCHEDULES_ALL PS,HZ_CUST_ACCOUNTS CA,HZ_PARTIES PY,OKL_INVOICE_FORMATS_V INF,AR_RECEIPT_METHODS ARM,HZ_PARTY_SITES PST,HZ_LOCATIONS LOC,HZ_CUST_ACCT_SITES_ALL CAS 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 CAS.CUST_ACCOUNT_ID = HD.IXX_ID AND CAS.CUST_ACCT_SITE_ID = HD.IBT_ID AND PST.PARTY_SITE_ID = CAS.PARTY_SITE_ID AND LOC.LOCATION_ID = PST.LOCATION_ID AND PY.PARTY_ID = CA.PARTY_ID AND SM.ID = ST.STY_ID AND HD.IRM_ID = ARM.RECEIPT_METHOD_ID (+) AND HD.INF_ID = INF.ID(+) GROUP BY HD.IXX_ID, HD.ID , HD.DUE_DATE, PY.PARTY_NAME, HD.CURRENCY_CODE, HD.CONSOLIDATED_INVOICE_NUMBER , HD.DATE_CONSOLIDATED, HD.IRM_ID, ARM.NAME, HD.INF_ID, NVL(INF.NAME,'NONE') , LOC.ADDRESS1, LOC.ADDRESS2, LOC.ADDRESS3, LOC.ADDRESS4, LOC.CITY, LOC.STATE, LOC.POSTAL_CODE, LOC.COUNTRY

View Text - HTML Formatted

SELECT HD.IXX_ID
, HD.ID CNR_ID
, HD.DUE_DATE
, PY.PARTY_NAME
, HD.CURRENCY_CODE
, HD.CONSOLIDATED_INVOICE_NUMBER
, HD.DATE_CONSOLIDATED
, HD.IRM_ID
, ARM.NAME RECEIPT_METHOD_NAME
, HD.INF_ID
, NVL(INF.NAME
, 'NONE') NAME
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.STATE
, LOC.POSTAL_CODE
, LOC.COUNTRY
, SUM(PS.AMOUNT_DUE_ORIGINAL) SUM_AMOUNT_DUE_ORIGINAL
, SUM(PS.AMOUNT_DUE_REMAINING) SUM_BALANCE
FROM OKL_CNSLD_AR_HDRS_B HD
, OKL_CNSLD_AR_LINES_V LN
, OKL_CNSLD_AR_STRMS_V ST
, OKL_STRM_TYPE_V SM
, AR_PAYMENT_SCHEDULES_ALL PS
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PY
, OKL_INVOICE_FORMATS_V INF
, AR_RECEIPT_METHODS ARM
, HZ_PARTY_SITES PST
, HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL CAS
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 CAS.CUST_ACCOUNT_ID = HD.IXX_ID
AND CAS.CUST_ACCT_SITE_ID = HD.IBT_ID
AND PST.PARTY_SITE_ID = CAS.PARTY_SITE_ID
AND LOC.LOCATION_ID = PST.LOCATION_ID
AND PY.PARTY_ID = CA.PARTY_ID
AND SM.ID = ST.STY_ID
AND HD.IRM_ID = ARM.RECEIPT_METHOD_ID (+)
AND HD.INF_ID = INF.ID(+) GROUP BY HD.IXX_ID
, HD.ID
, HD.DUE_DATE
, PY.PARTY_NAME
, HD.CURRENCY_CODE
, HD.CONSOLIDATED_INVOICE_NUMBER
, HD.DATE_CONSOLIDATED
, HD.IRM_ID
, ARM.NAME
, HD.INF_ID
, NVL(INF.NAME
, 'NONE')
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.STATE
, LOC.POSTAL_CODE
, LOC.COUNTRY