VIEW: APPS.OKL_CS_PAYMENT_SUMMARY_UV

Source

View Text - Preformatted

SELECT STM.Id STM_ID ,STM.KHR_ID KHR_ID ,STYT.NAME STREAM_TYPE ,STM.STY_ID STY_ID ,NULL ASSET_NUMBER ,to_number(NULL) KLE_ID ,CHRB.CONTRACT_NUMBER CONTRACT_NUMBER ,NULL LINE_TYPE ,NULL LINE_TYPE_CODE ,STM.say_code STATUS_CODE ,fnd.meaning STATUS ,okl_cs_lc_contract_pvt.get_total_stream_amount(stm.khr_id, null, stm.sty_id) amount ,CHRB.CURRENCY_CODE CURRENCY_CODE FROM OKL_STRM_TYPE_TL STYT, OKL_STRM_TYPE_B STYB, OKL_STREAMS STM, OKC_K_HEADERS_B CHRB, FND_LOOKUPS FND WHERE STM.PURPOSE_CODE IS NULL AND STM.STY_ID = STYB.ID AND STYB.ID = STYT.ID AND STYB.BILLABLE_YN = 'Y' AND STYT.LANGUAGE = USERENV('LANG') AND CHRB.ID = STM.KHR_ID AND STM.KLE_ID is null AND fnd.LOOKUP_TYPE='OKL_STREAM_ACTIVITY' AND fnd.LOOKUP_CODE=STM.say_code UNION SELECT STM.ID STM_ID, STM.KHR_ID KHR_ID , STYT.NAME STREAM_TYPE ,STM.STY_ID ,decode(LSE.LTY_CODE, 'FREE_FORM1', KLE.NAME, 'LINK_SERV_ASSET', KLE.NAME, 'LINK_FEE_ASSET', KLE.NAME, NULL) ASSET_NUMBER ,KLE.ID KLE_ID ,CHRB.CONTRACT_NUMBER CONTRACT_NUMBER ,LSE.NAME LINE_TYPE ,lse.lty_code LINE_TYPE_CODE ,STM.say_code STATUS_CODE ,fnd.meaning STATUS , okl_cs_lc_contract_pvt.get_total_stream_amount(stm.khr_id, kle.id, stm.sty_id) amount ,CHRB.CURRENCY_CODE CURRENCY_CODE FROM OKL_STRM_TYPE_TL STYT ,OKL_STRM_TYPE_B STYB ,OKL_STREAMS STM ,OKC_K_HEADERS_B CHRB ,OKC_K_LINES_V KLE ,OKC_LINE_STYLES_V LSE ,FND_LOOKUPS FND WHERE STM.PURPOSE_CODE IS NULL AND STM.STY_ID = STYB.ID AND STYB.ID = STYT.ID AND STYB.BILLABLE_YN = 'Y' AND STYT.LANGUAGE = USERENV('LANG') AND CHRB.ID = STM.KHR_ID AND KLE.ID(+) = STM.KLE_ID AND KLE.LSE_ID=LSE.ID(+) AND LSE.LTY_CODE IN ('FREE_FORM1','FEE','SOLD_SERVICE','LINK_SERV_ASSET','INSURANCE','LINK_FEE_ASSET') AND fnd.LOOKUP_TYPE='OKL_STREAM_ACTIVITY' AND fnd.LOOKUP_CODE=STM.say_code

View Text - HTML Formatted

SELECT STM.ID STM_ID
, STM.KHR_ID KHR_ID
, STYT.NAME STREAM_TYPE
, STM.STY_ID STY_ID
, NULL ASSET_NUMBER
, TO_NUMBER(NULL) KLE_ID
, CHRB.CONTRACT_NUMBER CONTRACT_NUMBER
, NULL LINE_TYPE
, NULL LINE_TYPE_CODE
, STM.SAY_CODE STATUS_CODE
, FND.MEANING STATUS
, OKL_CS_LC_CONTRACT_PVT.GET_TOTAL_STREAM_AMOUNT(STM.KHR_ID
, NULL
, STM.STY_ID) AMOUNT
, CHRB.CURRENCY_CODE CURRENCY_CODE
FROM OKL_STRM_TYPE_TL STYT
, OKL_STRM_TYPE_B STYB
, OKL_STREAMS STM
, OKC_K_HEADERS_B CHRB
, FND_LOOKUPS FND
WHERE STM.PURPOSE_CODE IS NULL
AND STM.STY_ID = STYB.ID
AND STYB.ID = STYT.ID
AND STYB.BILLABLE_YN = 'Y'
AND STYT.LANGUAGE = USERENV('LANG')
AND CHRB.ID = STM.KHR_ID
AND STM.KLE_ID IS NULL
AND FND.LOOKUP_TYPE='OKL_STREAM_ACTIVITY'
AND FND.LOOKUP_CODE=STM.SAY_CODE UNION SELECT STM.ID STM_ID
, STM.KHR_ID KHR_ID
, STYT.NAME STREAM_TYPE
, STM.STY_ID
, DECODE(LSE.LTY_CODE
, 'FREE_FORM1'
, KLE.NAME
, 'LINK_SERV_ASSET'
, KLE.NAME
, 'LINK_FEE_ASSET'
, KLE.NAME
, NULL) ASSET_NUMBER
, KLE.ID KLE_ID
, CHRB.CONTRACT_NUMBER CONTRACT_NUMBER
, LSE.NAME LINE_TYPE
, LSE.LTY_CODE LINE_TYPE_CODE
, STM.SAY_CODE STATUS_CODE
, FND.MEANING STATUS
, OKL_CS_LC_CONTRACT_PVT.GET_TOTAL_STREAM_AMOUNT(STM.KHR_ID
, KLE.ID
, STM.STY_ID) AMOUNT
, CHRB.CURRENCY_CODE CURRENCY_CODE
FROM OKL_STRM_TYPE_TL STYT
, OKL_STRM_TYPE_B STYB
, OKL_STREAMS STM
, OKC_K_HEADERS_B CHRB
, OKC_K_LINES_V KLE
, OKC_LINE_STYLES_V LSE
, FND_LOOKUPS FND
WHERE STM.PURPOSE_CODE IS NULL
AND STM.STY_ID = STYB.ID
AND STYB.ID = STYT.ID
AND STYB.BILLABLE_YN = 'Y'
AND STYT.LANGUAGE = USERENV('LANG')
AND CHRB.ID = STM.KHR_ID
AND KLE.ID(+) = STM.KLE_ID
AND KLE.LSE_ID=LSE.ID(+)
AND LSE.LTY_CODE IN ('FREE_FORM1'
, 'FEE'
, 'SOLD_SERVICE'
, 'LINK_SERV_ASSET'
, 'INSURANCE'
, 'LINK_FEE_ASSET')
AND FND.LOOKUP_TYPE='OKL_STREAM_ACTIVITY'
AND FND.LOOKUP_CODE=STM.SAY_CODE