1 PACKAGE BODY fii_ar_schd_pmt_discnt_pkg AS
2 /* $Header: FIIARDBISPDB.pls 120.6.12000000.1 2007/02/23 02:28:58 applrt ship $ */
3
4 -- This package will provide SQL statement to retrieve data for Scheduled Payments and Discounts Report
5
6 PROCEDURE get_schd_pmt_discnt ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
7 ,p_schd_pmt_discnt_sql OUT NOCOPY VARCHAR2
8 ,p_schd_pmt_discnt_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
9 ) IS
10
11 l_sqlstmt VARCHAR2(30000);
12 l_cust_trx_id VARCHAR2(30);
13
14 BEGIN
15 -- Call to reset the parameter variables
16
17 fii_ar_util_pkg.reset_globals;
18
19 -- Call to get all the parameters in the report
20
21 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
22
23 -- Done for bug# 5151282
24 IF fii_ar_util_pkg.g_cust_trx_id = 'All' THEN
25 l_cust_trx_id := '-99999';
26 ELSE
27 l_cust_trx_id := ':CUST_TRX_ID';
28 END IF;
29
30 -- PMV SQL to display data on Scheduled Payments and Discounts Report
31
32 l_sqlstmt := 'SELECT pmt.terms_sequence_number FII_AR_SCHD_PMT_NUMBER
33 ,pmt.due_date FII_AR_DUE_DATE
34 ,pmt.amount_due_original_trx FII_AR_AMOUNT
35 ,CASE WHEN sch.discount1_date IS NULL AND sch.discount1_days IS NOT NULL
36 THEN pmt.trx_date + sch.discount1_days
37 WHEN sch.discount1_date IS NULL AND sch.discount1_days IS NULL
38 THEN LAST_DAY(ADD_MONTHS(pmt.trx_date,sch.discount1_months_forward -1 ))
39 + sch.discount1_day_of_month
40 ELSE sch.discount1_date
41 END FII_AR_DISCOUNT_DATE
42 ,sch.discount1_percent * pmt.amount_due_original_trx/100
43 FII_AR_DISCOUNT_AMT
44 ,CASE WHEN sch.discount2_date IS NULL AND sch.discount2_days IS NOT NULL
45 THEN pmt.trx_date + sch.discount2_days
46 WHEN sch.discount2_date IS NULL AND sch.discount2_days IS NULL
47 THEN LAST_DAY(ADD_MONTHS(pmt.trx_date,sch.discount2_months_forward -1 ))
48 + sch.discount2_day_of_month
49 ELSE sch.discount2_date
50 END FII_AR_SECOND_DISCOUNT_DATE
51 ,sch.discount2_percent * pmt.amount_due_original_trx/100
52 FII_AR_SECOND_DISCOUNT_AMT
53 ,CASE WHEN sch.discount3_date IS NULL AND sch.discount3_days IS NOT NULL
54 THEN pmt.trx_date + sch.discount3_days
55 WHEN sch.discount3_date IS NULL AND sch.discount3_days IS NULL
56 THEN LAST_DAY(ADD_MONTHS(pmt.trx_date,sch.discount3_months_forward -1 ))
57 + sch.discount3_day_of_month
58 ELSE sch.discount3_date
59 END FII_AR_THIRD_DISCOUNT_DATE
60 ,sch.discount3_percent * pmt.amount_due_original_trx/100
61 FII_AR_THIRD_DISCOUNT_AMT
62 ,SUM(pmt.amount_due_original_trx) OVER ()
63 FII_AR_GT_AMOUNT
64 FROM fii_ar_scheduled_disc_f sch
65 ,fii_ar_pmt_schedules_f pmt
66 WHERE sch.term_id (+)= pmt.term_id
67 AND sch.sequence_num (+) = pmt.terms_sequence_number
68 AND pmt.customer_trx_id = '||l_cust_trx_id||'
69 &ORDER_BY_CLAUSE';
70
71 -- Call to UTIL package to bind the variables
72
73 fii_ar_util_pkg.bind_variable(l_sqlstmt
74 ,p_page_parameter_tbl
75 ,p_schd_pmt_discnt_sql
76 ,p_schd_pmt_discnt_output
77 );
78
79 END get_schd_pmt_discnt;
80
81 END fii_ar_schd_pmt_discnt_pkg;
[Home] [Help]