[Home] [Help]
PACKAGE BODY: APPS.FII_AR_DISCOUNT_SUMMARY_PKG
Source
1 PACKAGE BODY fii_ar_discount_summary_pkg AS
2 /* $Header: FIIARDBIDSUMB.pls 120.11 2007/05/15 20:49:25 vkazhipu ship $ */
3
4 -- This package will provide SQL statements to retrieve data for Discount Summary report
5
6 PROCEDURE get_discount_summary( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
7 ,p_discount_summary_sql OUT NOCOPY VARCHAR2
8 ,p_discount_summary_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
9 ) IS
10
11 l_sqlstmt VARCHAR2(30000);
12
13 -- Variables for where clauses
14 l_industry_where VARCHAR2(500);
15 l_party_where VARCHAR2(500);
16 l_child_party_where VARCHAR2(500);
17 l_cust_acct_where VARCHAR2(500);
18
19 -- Variables for drills
20 l_viewby_drill VARCHAR2(5000);
21 l_discount_amt_drill VARCHAR2(2000);
22 l_discount_amt_detail_drill VARCHAR2(2000);
23 l_discount_amt_final_drill VARCHAR2(5000);
24 l_app_rec_amt_drill VARCHAR2(2000);
25 l_app_rec_amt_detail_drill VARCHAR2(2000);
26 l_app_rec_amt_final_drill VARCHAR2(5000);
27 l_days_paid_drill VARCHAR2(5000);
28 l_days_paid_drill_1 VARCHAR2(5000);
29 l_discount_amt_drill_1 VARCHAR2(2000);
30 l_app_rec_amt_drill_1 VARCHAR2(2000);
31
32 -- Only for viewby Customer
33 l_inner_cust_columns VARCHAR2(2000);
34
35 -- For Order by Clause
36 l_order_by VARCHAR2(500);
37 l_order_column VARCHAR2(500);
38 l_gt_hint varchar2(500);
39 BEGIN
40 -- Call to reset the parameter variables
41 fii_ar_util_pkg.reset_globals;
42
43 -- Call to get all the parameters in the report
44 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
45 l_gt_hint := ' leading(gt) cardinality(gt 1) ';
46 -- Frame the order by clause for the report sql
47 IF(INSTR(fii_ar_util_pkg.g_order_by,',') <> 0) THEN
48
49 -- Above means that more than one column is sortable on the report
50 -- So, sort on the default column, FII_AR_DISCOUNT_AMT in descending order
51 -- NVL is added to make sure the null values appear last
52
53 l_order_by := 'ORDER BY NVL(FII_AR_DISCOUNT_AMT, -999999999) DESC';
54
55 ELSIF(INSTR(fii_ar_util_pkg.g_order_by, ' DESC') <> 0)THEN
56
57 -- Above means that a particular sort column is clicked to have descending order
58 -- Here, all the null values should appear last on the report
59
60 l_order_column := SUBSTR(fii_ar_util_pkg.g_order_by,1,INSTR(fii_ar_util_pkg.g_order_by, ' DESC'));
61 l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
62
63 ELSE
64
65 -- Following is the case when a sort in ascending order is chosen.
66 -- Following variable is provided by PMV
67
68 l_order_by := '&ORDER_BY_CLAUSE';
69
70 END IF;
71
72 -- Call to populate fii_ar_summary_gt table
73
74 fii_ar_util_pkg.populate_summary_gt_tables;
75
76 -- Assigning VIEWBY drill to NULL
77
78 l_viewby_drill := '''''';
79
80 -- Defining industry where clause for specific industry or when viewby is Industry
81 IF (fii_ar_util_pkg.g_industry_id <> '-111' AND fii_ar_util_pkg.g_view_by <> 'CUSTOMER+FII_CUSTOMERS') OR
82 fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
83
84 l_industry_where := ' AND time.class_code = f.class_code AND time.class_category = f.class_category';
85 END IF;
86
87 -- Customer Dimension where clause
88 IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
89 l_child_party_where := ' AND f.party_id = time.party_id ';
90 END IF;
91
92 -- Defining drills for Discount Amount, Applied Receipt Amount and Days Paid column shown on the report
93
94 l_discount_amt_drill := 'pFunctionName=FII_AR_DISCOUNT_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
95 l_discount_amt_detail_drill := 'pFunctionName=FII_AR_APP_RCT_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
96 l_app_rec_amt_drill := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
97 l_app_rec_amt_detail_drill := 'pFunctionName=FII_AR_APP_RCT_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
98 l_days_paid_drill := 'pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
99
100 -- Done for drill to detailed reports
101 IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
102 l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
103 l_discount_amt_detail_drill := 'pFunctionName=FII_AR_APP_RCT_ACT_DTL&BIS_PMV_DRILL_CODE_FII_CUSTOMER_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inner_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
104 l_app_rec_amt_detail_drill := 'pFunctionName=FII_AR_APP_RCT_ACT_DTL&BIS_PMV_DRILL_CODE_FII_CUSTOMER_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inner_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
105 END IF;
106
107 -- Select, where, group by clauses based on viewby
108 IF((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS')
109 OR (fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')) THEN
110 IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
111 l_discount_amt_drill_1 := 'pFunctionName=FII_AR_DISCOUNT_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
112 l_app_rec_amt_drill_1 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
113 l_days_paid_drill_1 := 'pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
114 ELSE
115 l_discount_amt_drill_1 := 'pFunctionName=FII_AR_DISCOUNT_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
116 l_app_rec_amt_drill_1 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
117 l_days_paid_drill_1 := 'pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
118 END IF;
119
120 l_discount_amt_final_drill := ''''||l_discount_amt_drill_1||'''';
121 l_app_rec_amt_final_drill := ''''||l_app_rec_amt_drill_1||'''';
122 l_days_paid_drill := l_days_paid_drill_1;
123 ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN
124 l_discount_amt_final_drill := ''''||l_discount_amt_detail_drill||'''';
125
126 l_app_rec_amt_final_drill := ''''||l_app_rec_amt_detail_drill||'''';
127
128 l_days_paid_drill := ''; -- Disabling drill on Weighted Average Days Paid column
129
130 -- WHERE clause for Customer Account
131 l_cust_acct_where := 'AND f.cust_account_id = time.cust_account_id';
132
133 ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
134 IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN
135 l_discount_amt_final_drill :=
136 'CASE WHEN is_self_flag = ''Y'' OR is_leaf_flag = ''Y''
137 THEN '''||l_discount_amt_detail_drill||'''
138 ELSE '''||l_discount_amt_drill||'''
139 END';
140
141 l_app_rec_amt_final_drill :=
142 'CASE WHEN is_self_flag = ''Y'' OR is_leaf_flag = ''Y''
143 THEN '''||l_app_rec_amt_detail_drill||'''
144 ELSE '''||l_app_rec_amt_drill||'''
145 END';
146
147 -- Self drill. Reqd only in case of Viewby Customer
148 -- Check dynamically if the node is leaf or not. In case of Non-Leaf Node, following drill is to be enabled.
149
150 l_viewby_drill := 'pFunctionName=FII_AR_DISCOUNT_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
151 l_viewby_drill := 'CASE WHEN is_self_flag = ''Y'' OR is_leaf_flag = ''Y''
152 THEN ''''
153 ELSE '''||l_viewby_drill||'''
154 END';
155 ELSE
156 l_discount_amt_final_drill := ''''||l_discount_amt_detail_drill||'''';
157
158 l_app_rec_amt_final_drill := ''''||l_app_rec_amt_detail_drill||'''';
159
160 END IF;
161
162 -- SELECT clause, GROUP BY clause and WHERE clause for Customer Dimension
163 l_inner_cust_columns := ',is_self_flag, is_leaf_flag';
164 l_party_where := 'AND f.parent_party_id = time.parent_party_id';
165
166 END IF;
167
168 -- Included party_id which is passed on the DRILL to detailed reports
169
170 IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
171 l_inner_cust_columns := l_inner_cust_columns || ' ,time.party_id';
172 END IF;
173
174 -- PMV SQL to display data on Discount Summary Report
175
176 l_sqlstmt :=
177 'SELECT inner_view.viewby VIEWBY
178 ,viewby_id VIEWBYID
179 ,FII_AR_DISCOUNT_AMT
180 ,(FII_AR_DISCOUNT_AMT - (FII_AR_PRIOR_UNEARNED_DISC_AMT + FII_AR_PRIOR_EARNED_DISC_AMT))*100
181 /NULLIF((FII_AR_PRIOR_UNEARNED_DISC_AMT + FII_AR_PRIOR_EARNED_DISC_AMT),0)
182 FII_AR_DISCOUNT_CHANGE
183 ,FII_AR_APP_REC_PERCENT
184 ,FII_AR_EARNED_DISC_PERCENT
185 ,FII_AR_EARNED_DISC_AMT
186 ,(FII_AR_EARNED_DISC_AMT - FII_AR_PRIOR_EARNED_DISC_AMT)*100
187 /NULLIF(FII_AR_PRIOR_EARNED_DISC_AMT,0) FII_AR_EARNED_DISC_CHANGE
188 ,FII_AR_UNEARNED_DISC_PERCENT
189 ,FII_AR_UNEARNED_DISC_AMT
190 ,(FII_AR_UNEARNED_DISC_AMT - FII_AR_PRIOR_UNEARNED_DISC_AMT)*100
191 /NULLIF(FII_AR_PRIOR_UNEARNED_DISC_AMT,0) FII_AR_UNEARNED_DISC_CHANGE
192 ,FII_AR_APP_REC_AMT
193 ,FII_AR_WTD_DAYS_PAID/NULLIF(FII_AR_APP_REC_AMT,0) FII_AR_DAYS_PAID
194 ,FII_AR_WTD_TERMS_PAID/NULLIF(FII_AR_APP_REC_AMT,0) FII_AR_TERMS_PAID
195 ,SUM (FII_AR_DISCOUNT_AMT) OVER () FII_AR_GT_DISCOUNT_AMT
196 ,(SUM(FII_AR_DISCOUNT_AMT) OVER () - (SUM(FII_AR_PRIOR_UNEARNED_DISC_AMT) OVER ()
197 + SUM(FII_AR_PRIOR_EARNED_DISC_AMT) OVER ()))*100
198 /NULLIF((SUM(FII_AR_PRIOR_UNEARNED_DISC_AMT) OVER () + SUM(FII_AR_PRIOR_EARNED_DISC_AMT) OVER ()),0)
199 FII_AR_GT_DISCOUNT_CHANGE
200 ,(SUM(FII_AR_UNEARNED_DISC_AMT) OVER () + SUM(FII_AR_EARNED_DISC_AMT) OVER ())*100
201 /NULLIF(SUM(FII_AR_APP_REC_AMT) OVER (),0) FII_AR_GT_APP_REC_PERCENT
202 ,SUM(FII_AR_EARNED_DISC_AMT) OVER ()*100
203 /NULLIF((SUM(FII_AR_UNEARNED_DISC_AMT) OVER () + SUM(FII_AR_EARNED_DISC_AMT) OVER ()),0)
204 FII_AR_GT_EARNED_DISC_PERCENT
205 ,SUM(FII_AR_EARNED_DISC_AMT) OVER () FII_AR_GT_EARNED_DISC_AMT
206 ,(SUM(FII_AR_EARNED_DISC_AMT) OVER () - SUM(FII_AR_PRIOR_EARNED_DISC_AMT) OVER ())*100
207 /NULLIF(SUM(FII_AR_PRIOR_EARNED_DISC_AMT) OVER (),0) FII_AR_GT_EARNED_DISC_CHANGE
208 ,SUM(FII_AR_UNEARNED_DISC_AMT) OVER ()*100
209 /NULLIF((SUM(FII_AR_UNEARNED_DISC_AMT) OVER () + SUM(FII_AR_EARNED_DISC_AMT) OVER ()),0)
210 FII_AR_GT_UNEARN_DISC_PERCENT
211 ,SUM(FII_AR_UNEARNED_DISC_AMT) OVER () FII_AR_GT_UNEARN_DISC_AMT
212 ,(SUM(FII_AR_UNEARNED_DISC_AMT) OVER () - SUM(FII_AR_PRIOR_UNEARNED_DISC_AMT) OVER ())*100
213 /NULLIF(SUM(FII_AR_PRIOR_UNEARNED_DISC_AMT) OVER (),0) FII_AR_GT_UNEARN_DISC_CHANGE
214 ,SUM(FII_AR_APP_REC_AMT) OVER () FII_AR_GT_APP_REC_AMT
215 ,SUM(FII_AR_WTD_DAYS_PAID) OVER ()/NULLIF(SUM(FII_AR_APP_REC_AMT) OVER (),0) FII_AR_GT_DAYS_PAID
216 ,SUM(FII_AR_WTD_TERMS_PAID) OVER ()/NULLIF(SUM(FII_AR_APP_REC_AMT) OVER (),0) FII_AR_GT_TERMS_PAID
217 ,FII_AR_PRIOR_UNEARNED_DISC_AMT
218 ,FII_AR_PRIOR_EARNED_DISC_AMT
219 ,FII_AR_PRIOR_APP_REC_PERCENT
220 ,CASE WHEN FII_AR_DISCOUNT_AMT = 0 OR FII_AR_DISCOUNT_AMT IS NULL THEN NULL
221 ELSE '||l_discount_amt_final_drill||'
222 END FII_AR_DISCOUNT_AMT_DRILL
223 ,CASE WHEN FII_AR_APP_REC_AMT = 0 OR FII_AR_APP_REC_AMT IS NULL THEN NULL
224 ELSE '||l_app_rec_amt_final_drill||'
225 END FII_AR_APP_REC_AMT_DRILL
226 ,CASE WHEN FII_AR_WTD_DAYS_PAID/NULLIF(FII_AR_APP_REC_AMT,0) = 0
227 OR FII_AR_WTD_DAYS_PAID/NULLIF(FII_AR_APP_REC_AMT,0) IS NULL THEN NULL
228 ELSE '''||l_days_paid_drill||'''
229 END FII_AR_DAYS_PAID_DRILL
230 ,'||l_viewby_drill||' FII_AR_VIEW_BY_DRILL
231 FROM
232 (SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ VIEWBY
233 ,time.viewby_code viewby_id
234 '||l_inner_cust_columns||'
235 ,SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount
236 ELSE NULL END) + SUM(CASE WHEN report_date = :ASOF_DATE THEN earned_discount_amount
237 ELSE NULL END) FII_AR_DISCOUNT_AMT
238 ,SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE
239 THEN unearned_discount_amount ELSE NULL END) FII_AR_PRIOR_UNEARNED_DISC_AMT
240 ,SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE THEN earned_discount_amount
241 ELSE NULL END) FII_AR_PRIOR_EARNED_DISC_AMT
242 ,(SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount ELSE NULL END)
243 + SUM(CASE WHEN report_date = :ASOF_DATE THEN earned_discount_amount ELSE NULL END))*100
244 /NULLIF(SUM(CASE WHEN report_date = :ASOF_DATE THEN app_amount ELSE NULL END),0)
245 FII_AR_APP_REC_PERCENT
246 ,SUM(CASE WHEN report_date = :ASOF_DATE THEN earned_discount_amount ELSE NULL END)*100
247 /NULLIF((SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount
248 ELSE NULL END) + SUM(CASE WHEN report_date = :ASOF_DATE
249 THEN earned_discount_amount ELSE NULL END)),0)
250 FII_AR_EARNED_DISC_PERCENT
251 ,SUM(CASE WHEN report_date = :ASOF_DATE THEN earned_discount_amount
252 ELSE NULL END) FII_AR_EARNED_DISC_AMT
253 ,SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount ELSE NULL END)*100
254 /NULLIF((SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount ELSE NULL END)
255 + SUM(CASE WHEN report_date = :ASOF_DATE THEN earned_discount_amount ELSE NULL END)),0)
256 FII_AR_UNEARNED_DISC_PERCENT
257 ,SUM(CASE WHEN report_date = :ASOF_DATE THEN unearned_discount_amount
258 ELSE NULL END) FII_AR_UNEARNED_DISC_AMT
259 ,SUM(CASE WHEN report_date = :ASOF_DATE THEN app_amount ELSE NULL END) FII_AR_APP_REC_AMT
260 ,SUM(CASE WHEN report_date = :ASOF_DATE THEN wtd_days_paid_num
261 ELSE NULL END) FII_AR_WTD_DAYS_PAID
262 ,SUM(CASE WHEN report_date = :ASOF_DATE THEN wtd_terms_paid_num
263 ELSE NULL END) FII_AR_WTD_TERMS_PAID
264 ,(SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE THEN unearned_discount_amount ELSE NULL END)
265 + SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE THEN earned_discount_amount ELSE NULL END))*100
266 /NULLIF(SUM(CASE WHEN report_date = :PREVIOUS_ASOF_DATE THEN app_amount ELSE NULL END),0)
267 FII_AR_PRIOR_APP_REC_PERCENT
268 FROM fii_ar_net_rec'||fii_ar_util_pkg.g_cust_suffix||'_mv'||fii_ar_util_pkg.g_curr_suffix||' f
269 ,(SELECT /*+ no_merge '||l_gt_hint|| ' */ cal.time_id time_id
270 ,cal.period_type_id period_type_id
271 ,cal.report_date report_date
272 ,gt.* -- Picking all the columns from Security table -- parent_party_id,party_id,org_id,
273 -- collector_id, is_leaf_flag,class_code,class_category,viewby, viewby_code
274 FROM fii_time_structures cal
275 ,'||fii_ar_util_pkg.get_from_statement||' gt -- Security table
276 WHERE report_date IN (:ASOF_DATE,:PREVIOUS_ASOF_DATE)
277 AND BITAND(cal.record_type_id, :BITAND) = :BITAND -- Bitand value changes with PeriodType
278 AND '||fii_ar_util_pkg.get_where_statement||'
279 ) time
280 WHERE f.time_id = time.time_id
281 AND f.period_type_id = time.period_type_id
282 AND f.org_id = time.org_id
283 AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_child_party_where||'
284 '||l_cust_acct_where||'
285 '||l_party_where||'
286 '||l_industry_where||'
287 GROUP BY time.viewby_code '||l_inner_cust_columns||', VIEWBY
288 ) inner_view
289 '||l_order_by;
290
291 -- Call to UTIL package to bind the variables
292
293 fii_ar_util_pkg.bind_variable(l_sqlstmt
294 ,p_page_parameter_tbl
295 ,p_discount_summary_sql
296 ,p_discount_summary_output
297 );
298
299 END get_discount_summary;
300
301 END fii_ar_discount_summary_pkg;