VIEW: APPS.OKL_LA_STX_SMRY_UV

Source

View Text - Preformatted

SELECT txs.khr_id contract_id, txs.kle_id asset_id, txs.asset_number asset_number, decode(lkp1.meaning, NULL,(select fnd1.meaning from okc_rules_b rle1, fnd_lookups fnd1 where rle1.dnz_chr_id = txs.khr_id and rle1.rule_information_category = 'LASTPR' and fnd1.lookup_type = 'OKL_ASSET_UPFRONT_TAX' and lookup_code = rle1.rule_information1), lkp1.meaning) Upfront_tax, decode(rle.rule_information11, null, (select name from okl_strm_type_v strm1, okc_rules_b rle2 where rle2.dnz_chr_id = txs.khr_id and rle2.rule_information_category = 'LASTPR' and strm1.id = decode(rle2.rule_information1, 'BILLED', rle2.rule_information2, 'FINANCE',rle2.rule_information3, 'CAPITALIZE',rle2.rule_information4)), (select name from okl_strm_type_v strm2, okc_rules_b rle3 where rle3.dnz_chr_id = txs.khr_id and rle3.rule_information_category = 'LASTPR' and strm2.id = decode(rle.rule_information11, 'BILLED', rle3.rule_information2, 'FINANCE',rle3.rule_information3, 'CAPITALIZE',rle3.rule_information4))) stream_name, sum(txl.tax_amount) tax_amount FROM OKC_RULE_GROUPS_B RGP,OKC_RULES_B RLE,OKL_TAX_SOURCES TXS,OKL_TAX_TRX_DETAILS TXL,FND_LOOKUPS LKP1 WHERE rgp.dnz_chr_id = txs.khr_id and rle.rgp_id = rgp.id and rgp.cle_id = txs.kle_id and txs.id = txl.txs_id and rle.rule_information_category = 'LAASTX' and rgp.rgd_code = 'LAASTX' and txs.tax_call_type_code = 'UPFRONT_TAX' and txs.tax_line_status_code = 'ACTIVE' and lkp1.lookup_type(+) = 'OKL_ASSET_UPFRONT_TAX' and lkp1.lookup_code(+) = rle.rule_information11 group by txs.khr_id, txs.kle_id, txs.asset_number, lkp1.meaning, rle.rule_information11 union SELECT txs.khr_id contract_id, txs.kle_id asset_id, txs.asset_number asset_number, fnd.meaning Upfront_tax, (select name from okl_strm_type_v strm1, okc_rules_b rle2 where rle2.dnz_chr_id = txs.khr_id and rle2.rule_information_category = 'LASTPR' and strm1.id = rle2.rule_information2) stream_name, sum(txl.tax_amount) tax_amount FROM OKL_TAX_SOURCES TXS, OKL_TAX_TRX_DETAILS TXL, fnd_lookups fnd WHERE txs.id = txl.txs_id and txs.kle_id IS NULL and txs.tax_call_type_code = 'UPFRONT_TAX' and txs.tax_line_status_code = 'ACTIVE' and fnd.lookup_type = 'OKL_ASSET_UPFRONT_TAX' and fnd.lookup_code = 'BILLED' group by txs.khr_id, txs.kle_id, txs.asset_number, fnd.meaning

View Text - HTML Formatted

SELECT TXS.KHR_ID CONTRACT_ID
, TXS.KLE_ID ASSET_ID
, TXS.ASSET_NUMBER ASSET_NUMBER
, DECODE(LKP1.MEANING
, NULL
, (SELECT FND1.MEANING
FROM OKC_RULES_B RLE1
, FND_LOOKUPS FND1
WHERE RLE1.DNZ_CHR_ID = TXS.KHR_ID
AND RLE1.RULE_INFORMATION_CATEGORY = 'LASTPR'
AND FND1.LOOKUP_TYPE = 'OKL_ASSET_UPFRONT_TAX'
AND LOOKUP_CODE = RLE1.RULE_INFORMATION1)
, LKP1.MEANING) UPFRONT_TAX
, DECODE(RLE.RULE_INFORMATION11
, NULL
, (SELECT NAME
FROM OKL_STRM_TYPE_V STRM1
, OKC_RULES_B RLE2
WHERE RLE2.DNZ_CHR_ID = TXS.KHR_ID
AND RLE2.RULE_INFORMATION_CATEGORY = 'LASTPR'
AND STRM1.ID = DECODE(RLE2.RULE_INFORMATION1
, 'BILLED'
, RLE2.RULE_INFORMATION2
, 'FINANCE'
, RLE2.RULE_INFORMATION3
, 'CAPITALIZE'
, RLE2.RULE_INFORMATION4))
, (SELECT NAME
FROM OKL_STRM_TYPE_V STRM2
, OKC_RULES_B RLE3
WHERE RLE3.DNZ_CHR_ID = TXS.KHR_ID
AND RLE3.RULE_INFORMATION_CATEGORY = 'LASTPR'
AND STRM2.ID = DECODE(RLE.RULE_INFORMATION11
, 'BILLED'
, RLE3.RULE_INFORMATION2
, 'FINANCE'
, RLE3.RULE_INFORMATION3
, 'CAPITALIZE'
, RLE3.RULE_INFORMATION4))) STREAM_NAME
, SUM(TXL.TAX_AMOUNT) TAX_AMOUNT
FROM OKC_RULE_GROUPS_B RGP
, OKC_RULES_B RLE
, OKL_TAX_SOURCES TXS
, OKL_TAX_TRX_DETAILS TXL
, FND_LOOKUPS LKP1
WHERE RGP.DNZ_CHR_ID = TXS.KHR_ID
AND RLE.RGP_ID = RGP.ID
AND RGP.CLE_ID = TXS.KLE_ID
AND TXS.ID = TXL.TXS_ID
AND RLE.RULE_INFORMATION_CATEGORY = 'LAASTX'
AND RGP.RGD_CODE = 'LAASTX'
AND TXS.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND TXS.TAX_LINE_STATUS_CODE = 'ACTIVE'
AND LKP1.LOOKUP_TYPE(+) = 'OKL_ASSET_UPFRONT_TAX'
AND LKP1.LOOKUP_CODE(+) = RLE.RULE_INFORMATION11 GROUP BY TXS.KHR_ID
, TXS.KLE_ID
, TXS.ASSET_NUMBER
, LKP1.MEANING
, RLE.RULE_INFORMATION11 UNION SELECT TXS.KHR_ID CONTRACT_ID
, TXS.KLE_ID ASSET_ID
, TXS.ASSET_NUMBER ASSET_NUMBER
, FND.MEANING UPFRONT_TAX
, (SELECT NAME
FROM OKL_STRM_TYPE_V STRM1
, OKC_RULES_B RLE2
WHERE RLE2.DNZ_CHR_ID = TXS.KHR_ID
AND RLE2.RULE_INFORMATION_CATEGORY = 'LASTPR'
AND STRM1.ID = RLE2.RULE_INFORMATION2) STREAM_NAME
, SUM(TXL.TAX_AMOUNT) TAX_AMOUNT
FROM OKL_TAX_SOURCES TXS
, OKL_TAX_TRX_DETAILS TXL
, FND_LOOKUPS FND
WHERE TXS.ID = TXL.TXS_ID
AND TXS.KLE_ID IS NULL
AND TXS.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND TXS.TAX_LINE_STATUS_CODE = 'ACTIVE'
AND FND.LOOKUP_TYPE = 'OKL_ASSET_UPFRONT_TAX'
AND FND.LOOKUP_CODE = 'BILLED' GROUP BY TXS.KHR_ID
, TXS.KLE_ID
, TXS.ASSET_NUMBER
, FND.MEANING