View: OKL_CS_PROPERTY_TAX_UV

Product:	OKL - Lease and Finance Management
Description:	UI View for Estimated Property Tax
Implementation/DBA Data:	APPS.OKL_CS_PROPERTY_TAX_UV

View Text

SELECT CNHD.ID INVOICE_ID
, CNHD.DATE_CONSOLIDATED INVOICE_DATE
, SUM(SEL.AMOUNT) ACTUAL_PROPERTY_TAX
, 0 ESTIMATED_PROPERTY_TAX
, 0 ADJUSTED_PROPERTY_TAX
, STM.KLE_ID KLE_ID
, KHR.ID KHR_ID
, CNST.RECEIVABLES_INVOICE_ID INVOICE_LINE_ID
FROM OKL_STREAMS STM
, OKL_STRM_ELEMENTS SEL
, OKL_STRM_TYPE_V STY
, OKC_K_HEADERS_ALL_B KHR
, OKC_K_LINES_B CLE
, OKL_K_HEADERS KHL
, OKC_STATUSES_B KHS
, OKC_STATUSES_B KLS
, OKL_CNSLD_AR_STRMS_B CNST
, OKL_CNSLD_AR_LINES_B CNLN
, OKL_CNSLD_AR_HDRS_B CNHD
WHERE KHR.ID= STM.KHR_ID
AND STY.BILLABLE_YN = 'Y'
AND SEL.STM_ID = STM.ID
AND SEL.AMOUNT <> 0
AND SEL.DATE_BILLED IS NOT NULL
AND ((STM.SAY_CODE = 'CURR'
AND STM.ACTIVE_YN = 'Y') OR (STM.SAY_CODE = 'HIST'
AND STM.ACTIVE_YN = 'N'))
AND STM.STY_ID = STY.ID
AND STY.STREAM_TYPE_PURPOSE = 'ACTUAL_PROPERTY_TAX'
AND KHR.SCS_CODE IN ('LEASE'
, 'LOAN')
AND KHL.ID = STM.KHR_ID
AND KHL.DEAL_TYPE IS NOT NULL
AND KHS.CODE = KHR.STS_CODE
AND KHS.STE_CODE IN ( 'ACTIVE'
, 'TERMINATED')
AND CLE.ID (+) = STM.KLE_ID
AND KLS.CODE (+) = CLE.STS_CODE
AND NVL (KLS.STE_CODE
, 'ACTIVE') IN ('ACTIVE'
, 'TERMINATED')
AND CNST.KLE_ID = CLE.ID
AND CNST.STY_ID = STY.ID
AND CNLN.ID = CNST.LLN_ID
AND CNLN.CNR_ID = CNHD.ID
AND CNST.SEL_ID = SEL.ID
AND CNST.KHR_ID = KHR.ID GROUP BY CNHD.ID
, STM.KLE_ID
, KHR.ID
, CNHD.DATE_CONSOLIDATED
, CNST.RECEIVABLES_INVOICE_ID UNION ALL SELECT CNHD.ID INVOICE_ID
, CNHD.DATE_CONSOLIDATED INVOICE_DATE
, 0 ACTUAL_PROPERTY_TAX
, SUM(SEL.AMOUNT) ESTIMATED_PROPERTY_TAX
, 0 ADJUSTED_PROPERTY_TAX
, STM.KLE_ID KLE_ID
, KHR.ID KHR_ID
, CNST.RECEIVABLES_INVOICE_ID INVOICE_LINE_ID
FROM OKL_STREAMS STM
, OKL_STRM_ELEMENTS SEL
, OKL_STRM_TYPE_V STY
, OKC_K_HEADERS_ALL_B KHR
, OKC_K_LINES_B CLE
, OKL_K_HEADERS KHL
, OKC_STATUSES_B KHS
, OKC_STATUSES_B KLS
, OKL_CNSLD_AR_STRMS_B CNST
, OKL_CNSLD_AR_LINES_B CNLN
, OKL_CNSLD_AR_HDRS_B CNHD
WHERE KHR.ID = STM.KHR_ID
AND STY.BILLABLE_YN = 'Y'
AND SEL.STM_ID = STM.ID
AND SEL.AMOUNT <> 0
AND SEL.DATE_BILLED IS NOT NULL
AND ((STM.SAY_CODE = 'CURR'
AND STM.ACTIVE_YN = 'Y') OR (STM.SAY_CODE = 'HIST'
AND STM.ACTIVE_YN = 'N'))
AND STM.STY_ID = STY.ID
AND STY.STREAM_TYPE_PURPOSE = 'ESTIMATED_PROPERTY_TAX'
AND KHR.SCS_CODE IN ('LEASE'
, 'LOAN')
AND KHL.ID = STM.KHR_ID
AND KHL.DEAL_TYPE IS NOT NULL
AND KHS.CODE = KHR.STS_CODE
AND KHS.STE_CODE IN ( 'ACTIVE'
, 'TERMINATED')
AND CLE.ID (+) = STM.KLE_ID
AND KLS.CODE (+) = CLE.STS_CODE
AND NVL (KLS.STE_CODE
, 'ACTIVE') IN ('ACTIVE'
, 'TERMINATED')
AND CNST.KLE_ID = CLE.ID
AND CNST.STY_ID = STY.ID
AND CNLN.ID = CNST.LLN_ID
AND CNLN.CNR_ID = CNHD.ID
AND CNST.SEL_ID = SEL.ID
AND CNST.KHR_ID = KHR.ID GROUP BY CNHD.ID
, STM.KLE_ID
, KHR.ID
, CNHD.DATE_CONSOLIDATED
, CNST.RECEIVABLES_INVOICE_ID UNION ALL SELECT CNHD.ID INVOICE_ID
, CNHD.DATE_CONSOLIDATED INVOICE_DATE
, 0 ACTUAL_PROPERTY_TAX
, 0 ESTIMATED_PROPERTY_TAX
, SUM(SEL.AMOUNT) ADJUSTED_PROPERTY_TAX
, STM.KLE_ID KLE_ID
, KHR.ID KHR_ID
, CNST.RECEIVABLES_INVOICE_ID INVOICE_LINE_ID
FROM OKL_STREAMS STM
, OKL_STRM_ELEMENTS SEL
, OKL_STRM_TYPE_V STY
, OKC_K_HEADERS_ALL_B KHR
, OKC_K_LINES_B CLE
, OKL_K_HEADERS KHL
, OKC_STATUSES_B KHS
, OKC_STATUSES_B KLS
, OKL_CNSLD_AR_STRMS_B CNST
, OKL_CNSLD_AR_LINES_B CNLN
, OKL_CNSLD_AR_HDRS_B CNHD
WHERE KHR.ID = STM.KHR_ID
AND STY.BILLABLE_YN = 'Y'
AND SEL.STM_ID = STM.ID
AND SEL.AMOUNT <> 0
AND SEL.DATE_BILLED IS NOT NULL
AND ((STM.SAY_CODE = 'CURR'
AND STM.ACTIVE_YN = 'Y') OR (STM.SAY_CODE = 'HIST'
AND STM.ACTIVE_YN = 'N'))
AND STM.STY_ID = STY.ID
AND STY.STREAM_TYPE_PURPOSE = 'ADJUSTED_PROPERTY_TAX'
AND KHR.SCS_CODE IN ('LEASE'
, 'LOAN')
AND KHL.ID = STM.KHR_ID
AND KHL.DEAL_TYPE IS NOT NULL
AND KHS.CODE = KHR.STS_CODE
AND KHS.STE_CODE IN ( 'ACTIVE'
, 'TERMINATED')
AND CLE.ID (+) = STM.KLE_ID
AND KLS.CODE (+) = CLE.STS_CODE
AND NVL (KLS.STE_CODE
, 'ACTIVE') IN ('ACTIVE'
, 'TERMINATED')
AND CNST.KLE_ID = CLE.ID
AND CNST.STY_ID = STY.ID
AND CNLN.ID = CNST.LLN_ID
AND CNLN.CNR_ID = CNHD.ID
AND CNST.SEL_ID = SEL.ID
AND CNST.KHR_ID = KHR.ID GROUP BY CNHD.ID
, STM.KLE_ID
, KHR.ID
, CNHD.DATE_CONSOLIDATED
, CNST.RECEIVABLES_INVOICE_ID UNION ALL SELECT RACTRL.CUSTOMER_TRX_ID INVOICE_ID
, RACTRX.TRX_DATE INVOICE_DATE
, SUM(SEL.AMOUNT) ACTUAL_PROPERTY_TAX
, 0 ESTIMATED_PROPERTY_TAX
, 0 ADJUSTED_PROPERTY_TAX
, STM.KLE_ID KLE_ID
, KHR.ID KHR_ID
, RACTRL.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
FROM OKL_STREAMS STM
, OKL_STRM_ELEMENTS SEL
, OKL_STRM_TYPE_V STY
, OKC_K_HEADERS_ALL_B KHR
, OKC_K_LINES_B CLE
, OKL_K_HEADERS KHL
, OKC_STATUSES_B KHS
, OKC_STATUSES_B KLS
, RA_CUSTOMER_TRX_ALL RACTRX
, RA_CUSTOMER_TRX_LINES_ALL RACTRL
, OKL_TXD_AR_LN_DTLS_B TLD
, OKL_TXL_AR_INV_LNS_B TIL
WHERE KHR.ID= STM.KHR_ID
AND STY.BILLABLE_YN = 'Y'
AND SEL.STM_ID = STM.ID
AND SEL.AMOUNT <> 0
AND SEL.DATE_BILLED IS NOT NULL
AND ((STM.SAY_CODE = 'CURR'
AND STM.ACTIVE_YN = 'Y') OR (STM.SAY_CODE = 'HIST'
AND STM.ACTIVE_YN = 'N'))
AND STM.STY_ID = STY.ID
AND STY.STREAM_TYPE_PURPOSE = 'ACTUAL_PROPERTY_TAX'
AND KHR.SCS_CODE IN ('LEASE'
, 'LOAN')
AND KHL.ID = STM.KHR_ID
AND KHL.DEAL_TYPE IS NOT NULL
AND KHS.CODE = KHR.STS_CODE
AND KHS.STE_CODE IN ( 'ACTIVE'
, 'TERMINATED')
AND CLE.ID (+) = STM.KLE_ID
AND KLS.CODE (+) = CLE.STS_CODE
AND NVL (KLS.STE_CODE
, 'ACTIVE') IN ('ACTIVE'
, 'TERMINATED')
AND RACTRX.CUSTOMER_TRX_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.INTERFACE_LINE_ATTRIBUTE14 = TLD.ID
AND RACTRL.INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS'
AND RACTRL.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND TLD.TIL_ID_DETAILS = TIL.ID
AND TIL.KLE_ID = CLE.ID
AND TLD.STY_ID = STY.ID
AND TLD.SEL_ID = SEL.ID
AND TLD.KHR_ID = KHR.ID GROUP BY RACTRL.CUSTOMER_TRX_ID
, STM.KLE_ID
, KHR.ID
, RACTRX.TRX_DATE
, RACTRL.CUSTOMER_TRX_LINE_ID UNION ALL SELECT RACTRL.CUSTOMER_TRX_ID INVOICE_ID
, RACTRX.TRX_DATE INVOICE_DATE
, 0 ACTUAL_PROPERTY_TAX
, SUM(SEL.AMOUNT) ESTIMATED_PROPERTY_TAX
, 0 ADJUSTED_PROPERTY_TAX
, STM.KLE_ID KLE_ID
, KHR.ID KHR_ID
, RACTRL.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
FROM OKL_STREAMS STM
, OKL_STRM_ELEMENTS SEL
, OKL_STRM_TYPE_V STY
, OKC_K_HEADERS_ALL_B KHR
, OKC_K_LINES_B CLE
, OKL_K_HEADERS KHL
, OKC_STATUSES_B KHS
, OKC_STATUSES_B KLS
, RA_CUSTOMER_TRX_ALL RACTRX
, RA_CUSTOMER_TRX_LINES_ALL RACTRL
, OKL_TXD_AR_LN_DTLS_B TLD
, OKL_TXL_AR_INV_LNS_B TIL
WHERE KHR.ID= STM.KHR_ID
AND STY.BILLABLE_YN = 'Y'
AND SEL.STM_ID = STM.ID
AND SEL.AMOUNT <> 0
AND SEL.DATE_BILLED IS NOT NULL
AND ((STM.SAY_CODE = 'CURR'
AND STM.ACTIVE_YN = 'Y') OR (STM.SAY_CODE = 'HIST'
AND STM.ACTIVE_YN = 'N'))
AND STM.STY_ID = STY.ID
AND STY.STREAM_TYPE_PURPOSE = 'ESTIMATED_PROPERTY_TAX'
AND KHR.SCS_CODE IN ('LEASE'
, 'LOAN')
AND KHL.ID = STM.KHR_ID
AND KHL.DEAL_TYPE IS NOT NULL
AND KHS.CODE = KHR.STS_CODE
AND KHS.STE_CODE IN ( 'ACTIVE'
, 'TERMINATED')
AND CLE.ID (+) = STM.KLE_ID
AND KLS.CODE (+) = CLE.STS_CODE
AND NVL (KLS.STE_CODE
, 'ACTIVE') IN ('ACTIVE'
, 'TERMINATED')
AND RACTRX.CUSTOMER_TRX_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.INTERFACE_LINE_ATTRIBUTE14 = TLD.ID
AND RACTRL.INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS'
AND RACTRL.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND TLD.TIL_ID_DETAILS = TIL.ID
AND TIL.KLE_ID = CLE.ID
AND TLD.STY_ID = STY.ID
AND TLD.SEL_ID = SEL.ID
AND TLD.KHR_ID = KHR.ID GROUP BY RACTRL.CUSTOMER_TRX_ID
, STM.KLE_ID
, KHR.ID
, RACTRX.TRX_DATE
, RACTRL.CUSTOMER_TRX_LINE_ID UNION ALL SELECT RACTRL.CUSTOMER_TRX_ID INVOICE_ID
, RACTRX.TRX_DATE INVOICE_DATE
, 0 ACTUAL_PROPERTY_TAX
, 0 ESTIMATED_PROPERTY_TAX
, SUM(SEL.AMOUNT) ADJUSTED_PROPERTY_TAX
, STM.KLE_ID KLE_ID
, KHR.ID KHR_ID
, RACTRL.CUSTOMER_TRX_LINE_ID INVOICE_LINE_ID
FROM OKL_STREAMS STM
, OKL_STRM_ELEMENTS SEL
, OKL_STRM_TYPE_V STY
, OKC_K_HEADERS_ALL_B KHR
, OKC_K_LINES_B CLE
, OKL_K_HEADERS KHL
, OKC_STATUSES_B KHS
, OKC_STATUSES_B KLS
, RA_CUSTOMER_TRX_ALL RACTRX
, RA_CUSTOMER_TRX_LINES_ALL RACTRL
, OKL_TXD_AR_LN_DTLS_B TLD
, OKL_TXL_AR_INV_LNS_B TIL
WHERE KHR.ID= STM.KHR_ID
AND STY.BILLABLE_YN = 'Y'
AND SEL.STM_ID = STM.ID
AND SEL.AMOUNT <> 0
AND SEL.DATE_BILLED IS NOT NULL
AND ((STM.SAY_CODE = 'CURR'
AND STM.ACTIVE_YN = 'Y') OR (STM.SAY_CODE = 'HIST'
AND STM.ACTIVE_YN = 'N'))
AND STM.STY_ID = STY.ID
AND STY.STREAM_TYPE_PURPOSE = 'ADJUSTED_PROPERTY_TAX'
AND KHR.SCS_CODE IN ('LEASE'
, 'LOAN')
AND KHL.ID = STM.KHR_ID
AND KHL.DEAL_TYPE IS NOT NULL
AND KHS.CODE = KHR.STS_CODE
AND KHS.STE_CODE IN ( 'ACTIVE'
, 'TERMINATED')
AND CLE.ID (+) = STM.KLE_ID
AND KLS.CODE (+) = CLE.STS_CODE
AND NVL (KLS.STE_CODE
, 'ACTIVE') IN ('ACTIVE'
, 'TERMINATED')
AND RACTRX.CUSTOMER_TRX_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.INTERFACE_LINE_ATTRIBUTE14 = TLD.ID
AND RACTRL.INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS'
AND RACTRL.INTERFACE_LINE_ATTRIBUTE1 IS NULL
AND TLD.TIL_ID_DETAILS = TIL.ID
AND TIL.KLE_ID = CLE.ID
AND TLD.STY_ID = STY.ID
AND TLD.SEL_ID = SEL.ID
AND TLD.KHR_ID = KHR.ID GROUP BY RACTRL.CUSTOMER_TRX_ID
, STM.KLE_ID
, KHR.ID
, RACTRX.TRX_DATE
, RACTRL.CUSTOMER_TRX_LINE_ID ORDER BY 2

Columns