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