VIEW: APPS.OKL_CS_PROPERTY_TAX_UV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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