VIEW: APPS.OKL_AMORT_SCHED_V

Source

View Text - Preformatted

SELECT sum(case when a.AMORT_TYPE='PRINCIPAL' then AMORT_VALUE else '0' end) as principal ,sum(case when a.AMORT_TYPE='INTEREST' then AMORT_VALUE else '0' end) as interest ,sum(case when a.AMORT_TYPE='PRINCIPAL_BALANCE' then AMORT_VALUE else '0' end) as principal_balance ,sum(case when a.AMORT_TYPE='LOAN_PAYMENT' then AMORT_VALUE else '0' end) as loan_payment ,to_date(max(case when a.AMORT_TYPE='DATE_FROM' then AMORT_VALUE else null end),'dd-mon-yyyy') as date_from ,case when AMORT_REPORT_FLAG='S' then to_date(max(case when a.AMORT_TYPE='DATE_TO' then AMORT_VALUE else null end),'dd-mon-yyyy') else null end date_to ,max(case when a.AMORT_TYPE='PAST_OR_PROJECTED' then AMORT_VALUE else null end) as payment_type, a.trx_req_id, a.AMORT_REPORT_FLAG, ab.currency_code, sum(case when a.amort_type = 'ORDER_BY' then amort_value else '0' end) as order_by, decode(max(case when a.AMORT_TYPE='PAST_OR_PROJECTED' then AMORT_VALUE else null end),'R','Received','B','Billed','P','Projected',max(case when a.AMORT_TYPE='PAST_OR_PROJECTED' then AMORT_VALUE else null end)) payment_type_forms, sum (case when a.amort_type = 'PROJ_INTEREST_RATE' then amort_value else '0' end) as proj_interest_rate from (select sh.AMORT_TYPE,sh.AMORT_HDR_ID,sl.AMORT_VALUE,sl.AMORT_LINE_ID,sh.AMORT_REPORT_FLAG,sh.trx_req_id from OKL_AMORT_SCHED_HDRS sh,OKL_AMORT_SCHED_LINES sl where sl.AMORT_HDR_ID=sh.AMORT_HDR_ID) a,okl_trx_requests tr,okc_k_headers_all_b ab where tr.id=a.trx_req_id and ab.id=tr.dnz_khr_id group by AMORT_LINE_ID,AMORT_REPORT_FLAG,trx_req_id,ab.currency_code order by trx_req_id,AMORT_REPORT_FLAG,order_by

View Text - HTML Formatted

SELECT SUM(CASE WHEN A.AMORT_TYPE='PRINCIPAL' THEN AMORT_VALUE ELSE '0' END) AS PRINCIPAL
, SUM(CASE WHEN A.AMORT_TYPE='INTEREST' THEN AMORT_VALUE ELSE '0' END) AS INTEREST
, SUM(CASE WHEN A.AMORT_TYPE='PRINCIPAL_BALANCE' THEN AMORT_VALUE ELSE '0' END) AS PRINCIPAL_BALANCE
, SUM(CASE WHEN A.AMORT_TYPE='LOAN_PAYMENT' THEN AMORT_VALUE ELSE '0' END) AS LOAN_PAYMENT
, TO_DATE(MAX(CASE WHEN A.AMORT_TYPE='DATE_FROM' THEN AMORT_VALUE ELSE NULL END)
, 'DD-MON-YYYY') AS DATE_FROM
, CASE WHEN AMORT_REPORT_FLAG='S' THEN TO_DATE(MAX(CASE WHEN A.AMORT_TYPE='DATE_TO' THEN AMORT_VALUE ELSE NULL END)
, 'DD-MON-YYYY') ELSE NULL END DATE_TO
, MAX(CASE WHEN A.AMORT_TYPE='PAST_OR_PROJECTED' THEN AMORT_VALUE ELSE NULL END) AS PAYMENT_TYPE
, A.TRX_REQ_ID
, A.AMORT_REPORT_FLAG
, AB.CURRENCY_CODE
, SUM(CASE WHEN A.AMORT_TYPE = 'ORDER_BY' THEN AMORT_VALUE ELSE '0' END) AS ORDER_BY
, DECODE(MAX(CASE WHEN A.AMORT_TYPE='PAST_OR_PROJECTED' THEN AMORT_VALUE ELSE NULL END)
, 'R'
, 'RECEIVED'
, 'B'
, 'BILLED'
, 'P'
, 'PROJECTED'
, MAX(CASE WHEN A.AMORT_TYPE='PAST_OR_PROJECTED' THEN AMORT_VALUE ELSE NULL END)) PAYMENT_TYPE_FORMS
, SUM (CASE WHEN A.AMORT_TYPE = 'PROJ_INTEREST_RATE' THEN AMORT_VALUE ELSE '0' END) AS PROJ_INTEREST_RATE
FROM (SELECT SH.AMORT_TYPE
, SH.AMORT_HDR_ID
, SL.AMORT_VALUE
, SL.AMORT_LINE_ID
, SH.AMORT_REPORT_FLAG
, SH.TRX_REQ_ID
FROM OKL_AMORT_SCHED_HDRS SH
, OKL_AMORT_SCHED_LINES SL
WHERE SL.AMORT_HDR_ID=SH.AMORT_HDR_ID) A
, OKL_TRX_REQUESTS TR
, OKC_K_HEADERS_ALL_B AB
WHERE TR.ID=A.TRX_REQ_ID
AND AB.ID=TR.DNZ_KHR_ID GROUP BY AMORT_LINE_ID
, AMORT_REPORT_FLAG
, TRX_REQ_ID
, AB.CURRENCY_CODE ORDER BY TRX_REQ_ID
, AMORT_REPORT_FLAG
, ORDER_BY