[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sqlstmt := 'SELECT pmt.terms_sequence_number FII_AR_SCHD_PMT_NUMBER
,pmt.due_date FII_AR_DUE_DATE
,pmt.amount_due_original_trx FII_AR_AMOUNT
,CASE WHEN sch.discount1_date IS NULL AND sch.discount1_days IS NOT NULL
THEN pmt.trx_date + sch.discount1_days
WHEN sch.discount1_date IS NULL AND sch.discount1_days IS NULL
THEN LAST_DAY(ADD_MONTHS(pmt.trx_date,sch.discount1_months_forward -1 ))
+ sch.discount1_day_of_month
ELSE sch.discount1_date
END FII_AR_DISCOUNT_DATE
,sch.discount1_percent * pmt.amount_due_original_trx/100
FII_AR_DISCOUNT_AMT
,CASE WHEN sch.discount2_date IS NULL AND sch.discount2_days IS NOT NULL
THEN pmt.trx_date + sch.discount2_days
WHEN sch.discount2_date IS NULL AND sch.discount2_days IS NULL
THEN LAST_DAY(ADD_MONTHS(pmt.trx_date,sch.discount2_months_forward -1 ))
+ sch.discount2_day_of_month
ELSE sch.discount2_date
END FII_AR_SECOND_DISCOUNT_DATE
,sch.discount2_percent * pmt.amount_due_original_trx/100
FII_AR_SECOND_DISCOUNT_AMT
,CASE WHEN sch.discount3_date IS NULL AND sch.discount3_days IS NOT NULL
THEN pmt.trx_date + sch.discount3_days
WHEN sch.discount3_date IS NULL AND sch.discount3_days IS NULL
THEN LAST_DAY(ADD_MONTHS(pmt.trx_date,sch.discount3_months_forward -1 ))
+ sch.discount3_day_of_month
ELSE sch.discount3_date
END FII_AR_THIRD_DISCOUNT_DATE
,sch.discount3_percent * pmt.amount_due_original_trx/100
FII_AR_THIRD_DISCOUNT_AMT
,SUM(pmt.amount_due_original_trx) OVER ()
FII_AR_GT_AMOUNT
FROM fii_ar_scheduled_disc_f sch
,fii_ar_pmt_schedules_f pmt
WHERE sch.term_id (+)= pmt.term_id
AND sch.sequence_num (+) = pmt.terms_sequence_number
AND pmt.customer_trx_id = '||l_cust_trx_id||'
&ORDER_BY_CLAUSE';