View: OKL_LA_STX_SMRY_UV

Product:	OKL - Lease and Finance Management
Description:	Sales Tax Summary
Implementation/DBA Data:	APPS.OKL_LA_STX_SMRY_UV

View Text

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

Columns