[Home] [Help]
PACKAGE BODY: APPS.FII_AR_COLL_EFF_IND_PKG
Source
1 PACKAGE BODY fii_ar_coll_eff_ind_pkg AS
2 /* $Header: FIIARDBICEIB.pls 120.33 2007/07/05 10:41:08 arcdixit ship $ */
3
4 --------------------Global Variable Declaration--------------------------
5 g_scaling_factor VARCHAR2(100);
6 g_scale_sign VARCHAR2(1);
7 g_scaling_factor_cons NUMBER;
8 g_scale_sign_cons VARCHAR2(1);
9 g_current_sequence number;
10
11 --------------------------------------------------------------------------
12 -- For constructing the order by clause for the report
13 FUNCTION get_order_by return VARCHAR2 IS
14 --For Order by Clause
15 l_order_by varchar2(500);
16 l_order_column varchar2(100);
17
18 BEGIN
19
20 --Frame the order by clause for the report sql
21 IF(instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0)THEN
22
23 /*This means a particular sort column is clicked to have descending order
24 in which case we would want all the null values to appear last in the
25 report so add an NVL to that column*/
26
27 l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
28 l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
29
30 ELSE
31
32 /*This is the case when user has asked for an ascending order sort.
33 Use PMV's order by clause*/
34
35 l_order_by := '&ORDER_BY_CLAUSE';
36
37 END IF;
38
39 return l_order_by;
40
41 END get_order_by;
42
43 ---------------------------------------------------------------------
44 -- Procedure to set the Scaling factor and sign for billed amount
45 PROCEDURE get_scaling_factor IS
46 BEGIN
47
48 --Code to decide the scaling factor for Billed Amount
49 --The billed amount needs to be scaled to a month before calculating the CEI.
50
51 -- These global variables will be used in Collection Effectiveness Index and Collection Effectiveness Reports
52 -- and trend report for the current period
53 IF (fii_ar_util_pkg.g_curr_per_end = fii_ar_util_pkg.g_as_of_date) THEN
54 IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
55 g_scaling_factor := 4.35;
56 ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
57 g_scaling_factor := 3;
58 ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN
59 g_scaling_factor := 12;
60 ELSE
61 g_scaling_factor := 1;
62 END IF;
63 ELSE
64 g_scaling_factor := to_char((((fii_ar_util_pkg.g_as_of_date - fii_ar_util_pkg.g_curr_per_start) + 1) /30), '99999D9999999999999','NLS_NUMERIC_CHARACTERS=''.,');
65 END IF;
66
67 g_scale_sign := '/';
68
69 -- This will be used in the trend report as there would be months, weeks, quarters and years with full period
70 IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
71 g_scaling_factor_cons := 4.35;
72 g_scale_sign_cons := '/';
73 ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
74 g_scaling_factor_cons := 3;
75 g_scale_sign_cons := '/';
76 ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN
77 g_scaling_factor_cons := 12;
78 g_scale_sign_cons := '/';
79 ELSE
80 g_scaling_factor_cons := 1;
81 g_scale_sign_cons := '/';
82 END IF;
83
84 -- To get the sequence of the current period. It will always be the maximum sequence displayed in the trend report
85 IF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
86 select sequence into g_current_sequence from fii_time_week where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
87
88 ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
89 select sequence into g_current_sequence from FII_TIME_ENT_QTR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
90
91 ELSIF(fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR') THEN
92 select sequence into g_current_sequence from FII_TIME_ENT_YEAR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
93
94 ELSE
95 select sequence into g_current_sequence from FII_TIME_ENT_PERIOD where fii_ar_util_pkg.g_as_of_date between start_date and end_date;
96
97 END IF;
98
99 END get_scaling_factor;
100
101 -----------------------------------------------------------------------------------------------------------
102 -- This procedure will provide sql statements to retrieve data for Collection Effectiveness Index Summary
103 PROCEDURE get_coll_eff_index(
104 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, coll_eff_sql out NOCOPY VARCHAR2,
105 coll_eff_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
106
107 sqlstmt VARCHAR2(30000);
108
109 --Variables for where clauses
110 l_collector_where VARCHAR2(300);
111 l_party_where VARCHAR2(300);
112 l_child_party_where VARCHAR2(300);
113 l_cust_acct_where VARCHAR2(300);
114
115 --Date for beginning open receivables drill
116 l_curr_per_start VARCHAR2(30);
117
118 --Variables for drills
119 l_self_drill VARCHAR2(300);
120 l_beg_open_rec_drill VARCHAR2(300);
121 l_end_open_rec_drill VARCHAR2(300);
122 l_end_curr_rec_drill VARCHAR2(300);
123 l_beg_open_rec_drill_l VARCHAR2(300);
124 l_end_open_rec_drill_l VARCHAR2(300);
125 l_end_curr_rec_drill_l VARCHAR2(300);
126 l_beg_open_rec_drill_2 VARCHAR2(300);
127 l_end_open_rec_drill_2 VARCHAR2(300);
128 l_end_curr_rec_drill_2 VARCHAR2(300);
129
130 --Select sql's
131 l_select_sql1 varchar2(500);
132 l_select_sql2 varchar2(500);
133 l_select_sql3 varchar2(500);
134
135 --Only for viewby Customer
136 l_inner_cst_select varchar2(70);
137 l_inner_cst_group varchar2(50);
138 l_self_drill_select varchar2(200);
139
140 --For Order by Clause
141 l_order_by varchar2(500);
142
143 --For sending Customer id to detail reports
144 l_customer_select varchar2(500);
145 l_gt_hint varchar2(500);
146 BEGIN
147 --Call to reset the parameter variables
148 fii_ar_util_pkg.reset_globals;
149
150 --Call to get all the parameters in the report
151 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
152
153 --Get the order by clause for the report
154 l_order_by := get_order_by;
155
156 --This call will populate fii_ar_summary_gt table
157 fii_ar_util_pkg.populate_summary_gt_tables;
158
159 --Call to set up the global variables for scaling factor and sign for billed amount
160 get_scaling_factor;
161 l_gt_hint := ' leading(gt) cardinality(gt 1) ';
162 -- Date to be passed in the drill on Beginning Open Receivables amount
163 l_curr_per_start := to_char(trunc(fii_ar_util_pkg.g_curr_per_start), 'DD/MM/YYYY');
164
165 --Amount Drills
166 l_beg_open_rec_drill := 'AS_OF_DATE='||l_curr_per_start||'&pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
167
168 l_end_open_rec_drill := 'pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
169
170 l_end_curr_rec_drill := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
171
172
173 IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
174
175 l_beg_open_rec_drill_2 := 'AS_OF_DATE='||l_curr_per_start||'&pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
176
177 l_end_open_rec_drill_2 := 'pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
178
179 l_end_curr_rec_drill_2 := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
180
181 ELSE
182
183 l_beg_open_rec_drill_2 := 'AS_OF_DATE='||l_curr_per_start||'&pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
184
185 l_end_open_rec_drill_2 := 'pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
186
187 l_end_curr_rec_drill_2 := 'pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
188
189 END IF;
190
191
192 --Detail drills require Customer and Account as parameter in case the drill is from view by Customer Account
193 IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
194
195 l_beg_open_rec_drill_l := 'AS_OF_DATE='||l_curr_per_start||'&pFunctionName=FII_AR_OPEN_REC_DTL&FII_AR_CUST_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
196
197 l_end_open_rec_drill_l := 'pFunctionName=FII_AR_OPEN_REC_DTL&FII_AR_CUST_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
198
199 l_end_curr_rec_drill_l := 'pFunctionName=FII_AR_CURR_REC_DTL&FII_AR_CUST_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
200 ELSE
201 l_beg_open_rec_drill_l := 'AS_OF_DATE='||l_curr_per_start||'&pFunctionName=FII_AR_OPEN_REC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
202
203 l_end_open_rec_drill_l := 'pFunctionName=FII_AR_OPEN_REC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
204
205 l_end_curr_rec_drill_l := 'pFunctionName=FII_AR_CURR_REC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
206 END IF;
207
208 -- Default View by select clause and self drill select clause
209 --This will change in case of View by Customer
210 l_self_drill_select := '''''';
211
212 --Setting up the where clauses based on the Parameter and viewby
213 --for Collector Dimension
214 IF (fii_ar_util_pkg.g_collector_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR') THEN
215 l_collector_where := 'AND f.collector_id = t.collector_id';
216 END IF;
217
218 --Customer Dimension where clause
219 IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
220 l_child_party_where := ' AND f.party_id = t.party_id ';
221 END IF;
222
223 -- Select, where, group by clauses based on viewby
224 If((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS') OR (fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR')) THEN
225 l_select_sql1 := ''''||l_beg_open_rec_drill_2||'''';
226
227 l_select_sql2 := ''''||l_end_open_rec_drill_2||'''';
228
229 l_select_sql3 := ''''||l_end_curr_rec_drill_2||'''';
230
231 ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN
232 l_select_sql1 := ''''||l_beg_open_rec_drill_l||'''';
233
234 l_select_sql2 := ''''||l_end_open_rec_drill_l||'''';
235
236 l_select_sql3 := ''''||l_end_curr_rec_drill_l||'''';
237
238 l_cust_acct_where := 'AND f.cust_account_id = t.cust_account_id';
239
240 l_customer_select := ' t.party_id party_id, ';
241 l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
242
243 ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
244 IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN
245 l_select_sql1 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_beg_open_rec_drill_l||''', DECODE(is_leaf_flag,''Y'', '''||l_beg_open_rec_drill_l||''', '''||l_beg_open_rec_drill||'''))';
246
247 l_select_sql2 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_end_open_rec_drill_l||''', DECODE(is_leaf_flag,''Y'', '''||l_end_open_rec_drill_l||''', '''||l_end_open_rec_drill||'''))';
248
249 l_select_sql3 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_end_curr_rec_drill_l||''', DECODE(is_leaf_flag,''Y'', '''||l_end_curr_rec_drill_l||''', '''||l_end_curr_rec_drill||'''))';
250
251 --Self drill. This is reqd only in case of Viewby Customer
252 --Check dynamically if the node is leaf or not. In case of non-leaf this drill is to be enabled.
253
254 l_self_drill := 'pFunctionName=FII_AR_COLL_EFF_INDEX&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
255
256 l_self_drill_select := 'DECODE(inline_view.is_self_flag, ''Y'', '''', DECODE(inline_view.is_leaf_flag, ''Y'','''','''||l_self_drill||'''))';
257
258 --Select and Groupp by Clauses
259 l_inner_cst_select := 'is_self_flag is_self_flag, is_leaf_flag is_leaf_flag,';
260 l_inner_cst_group := ', is_self_flag, is_leaf_flag';
261
262
263 ELSE
264 l_select_sql1 := ''''||l_beg_open_rec_drill_l||'''';
265
266 l_select_sql2 := ''''||l_end_open_rec_drill_l||'''';
267
268 l_select_sql3 := ''''||l_end_curr_rec_drill_l||'''';
269 END IF;
270
271 -- Where clause clause for Customer Dimension
272 l_party_where := 'AND f.parent_party_id = t.parent_party_id';
273
274 END IF;
275
276 IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
277 l_inner_cst_group := l_inner_cst_group || ' ,t.party_id';
278 END IF;
279
280 -- Report Query
281 sqlstmt := 'SELECT
282 inline_view.viewby VIEWBY,
283 inline_view.viewby_id VIEWBYID,
284 ((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC) +
285 (DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
286 - DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC))/
287 NULLIF((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)
288 + (DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
289 - DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)),0)) * 100 FII_AR_COLL_EFF_INDEX,
290 (((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC) +
291 (DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
292 -DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC))/
293 NULLIF((NULLIF(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0,FII_AR_BEG_OPEN_REC), 0)
294 +(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
295 -DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)),0))* 100) -
296 (((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
297 +(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
298 - DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC))/
299 NULLIF((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
300 +(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
301 - DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)),0))*100) FII_AR_COLL_EFF_CHANGE,
302 FII_AR_BEG_OPEN_REC,
303 FII_AR_BILLED_AMOUNT,
304 FII_AR_END_OPEN_REC,
305 FII_AR_BEG_CURR_REC,
306 FII_AR_PAST_DUE_REC,
307 FII_AR_END_CURR_REC,
308 ((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)) OVER() +
309 SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
310 -SUM(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)) OVER())/
311 NULLIF((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))OVER()
312 +SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
313 -SUM(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)) OVER()),0)) *100 FII_AR_GT_COLL_EFF_IND,
314 (((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)) OVER()
315 +SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
316 -SUM(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)) OVER())/
317 NULLIF((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))OVER()
318 +SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
319 -SUM(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)) OVER()),0))* 100) -
320 (((SUM(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)) OVER()
321 +SUM(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||') OVER()
322 -SUM(DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC)) OVER())/
323 NULLIF((SUM(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)) OVER()
324 +SUM(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||') OVER()
325 -SUM(DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)) OVER()),0))* 100) FII_AR_GT_COLL_EFF_CHG,
326 SUM(FII_AR_BEG_OPEN_REC) OVER() FII_AR_GT_BEG_OPEN_REC,
327 SUM(FII_AR_BILLED_AMOUNT) OVER() FII_AR_GT_BILLED_AMT,
328 SUM(FII_AR_END_OPEN_REC) OVER() FII_AR_GT_END_OPEN_REC,
329 SUM(FII_AR_END_CURR_REC) OVER() FII_AR_GT_END_CURR_REC,
330 ((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
331 + (DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
332 - DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC))/
333 NULLIF((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
334 + (DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
335 - DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)),0)) * 100 FII_AR_PRIO_COLL_EFF_INDEX,
336 FII_AR_END_PAST_DUE_REC,
337 DECODE(FII_AR_BEG_OPEN_REC,0,'''',DECODE(NVL(FII_AR_BEG_OPEN_REC,-999999),-999999,'''',
338 '||l_select_sql1||')) FII_AR_BEG_OPEN_REC_DRILL,
339 DECODE(FII_AR_END_OPEN_REC,0,'''',DECODE(NVL(FII_AR_END_OPEN_REC,-999999),-999999,'''',
340 '||l_select_sql2||')) FII_AR_END_OPEN_REC_DRILL,
341 DECODE(FII_AR_END_CURR_REC,0,'''',DECODE(NVL(FII_AR_END_CURR_REC,-999999),-999999,'''',
342 '||l_select_sql3||')) FII_AR_END_CURR_REC_DRILL,
343 '||l_self_drill_select ||' FII_AR_CUST_SELF_DRILL ,
344 ((SUM(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)) OVER() +
345 SUM(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
346 -SUM(DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC)) OVER())/
347 NULLIF((SUM(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC))OVER()
348 +SUM(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
349 -SUM(DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)) OVER()),0)) * 100 FII_AR_GT_PRIO_COLL_EFF_INDEX
350 FROM (
351 SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ VIEWBY,
352 viewby_code viewby_id,
353 '||l_inner_cst_select||l_customer_select||'
354 SUM(DECODE(t.report_date, :CURR_PERIOD_START ,
355 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
356 THEN total_open_amount ELSE NULL END) ) ) FII_AR_BEG_OPEN_REC,
357 SUM(DECODE(t.report_date, :ASOF_DATE,
358 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
359 THEN total_open_amount ELSE NULL END) ) ) FII_AR_END_OPEN_REC,
360 SUM(DECODE(t.report_date, :ASOF_DATE,
361 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
362 THEN current_open_amount ELSE NULL END) ) ) FII_AR_END_CURR_REC,
363 SUM(DECODE(t.report_date, :CURR_PERIOD_START,
364 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
365 THEN current_open_amount ELSE NULL END) ) ) FII_AR_BEG_CURR_REC,
366 SUM(DECODE(t.report_date, :CURR_PERIOD_START, /*This date will be starting date of the period*/
367 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
368 THEN past_due_open_amount ELSE NULL END) ) ) FII_AR_PAST_DUE_REC,
369 SUM(DECODE(t.report_date, :ASOF_DATE, /*This date will be the as-of-date*/
370 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
371 THEN past_due_open_amount ELSE NULL END) ) ) FII_AR_END_PAST_DUE_REC,
372 SUM(DECODE(t.report_date, :ASOF_DATE, /*This date will be the as-of-date*/
373 (CASE WHEN bitand(t.record_type_id,:BITAND) = :BITAND
374 THEN billed_amount ELSE NULL END) ) ) FII_AR_BILLED_AMOUNT,
375 SUM(DECODE(t.report_date, :PRIOR_PERIOD_START, /*This date will be starting date of the prior period*/
376 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
377 THEN total_open_amount ELSE NULL END) ) ) FII_AR_PRIOR_BEG_OPEN_REC,
378 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE, /*This date will be the prior as-of-date*/
379 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
380 THEN total_open_amount ELSE NULL END) ) ) FII_AR_PRIOR_END_OPEN_REC,
381 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE, /*This date will be the prior as-of-date*/
382 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
383 THEN current_open_amount ELSE NULL END) ) ) FII_AR_PRIOR_END_CURR_REC,
384 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE, /*This date will be the prior as-of-date*/
385 (CASE WHEN bitand(t.record_type_id,:BITAND) = :BITAND
386 THEN billed_amount ELSE NULL END) ) ) FII_AR_PRIOR_BILLED_AMOUNT
387 FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
388 ||' f,( SELECT /*+ no_merge '||l_gt_hint|| ' */ *
389 FROM fii_time_structures cal,
390 '||fii_ar_util_pkg.get_from_statement||' gt
391 WHERE report_date IN ( :CURR_PERIOD_START ,
392 :ASOF_DATE,
393 :PREVIOUS_ASOF_DATE,
394 :PRIOR_PERIOD_START
395 )
396 AND ( bitand(cal.record_type_id, :BITAND) = :BITAND OR
397 bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
398 )
399 AND '||fii_ar_util_pkg.get_where_statement||'
400 ) t
401 WHERE f.time_id = t.time_id
402 AND f.period_type_id = t.period_type_id '||l_child_party_where||'
403 AND f.org_id = t.org_id
404 AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' '|| l_cust_acct_where ||'
405 GROUP BY viewby_code, VIEWBY '||l_inner_cst_group||') inline_view
406 '||l_order_by;
407
408 -- Call Util package to bind the variables
409 fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, coll_eff_sql, coll_eff_output);
410
411 END get_coll_eff_index;
412
413 ----------------------------------------------------------------------------------------------
414 --Procedure for Collection Effectiveness Report
415 PROCEDURE get_coll_eff(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
416 coll_eff_sql out NOCOPY VARCHAR2, coll_eff_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
417
418 sqlstmt VARCHAR2(15000);
419
420 --Variables for where clauses
421 l_collector_where VARCHAR2(300);
422 l_party_where VARCHAR2(300);
423 l_child_party_where VARCHAR2(300);
424 l_cust_acct_where VARCHAR2(300);
425 l_industry_where VARCHAR2(300);
426
427
428 --Variables for drills
429 l_self_drill VARCHAR2(300);
430 l_rec_amt_drill VARCHAR2(300);
431 l_rec_amt_drill_1 VARCHAR2(300);
432 l_wadp_drill VARCHAR2(300);
433 l_rec_amt_drill_2 VARCHAR2(300);
434 l_wadp_drill_1 VARCHAR2(300);
435
436 --Select sql's
437 l_select_sql1 varchar2(500);
438 l_select_sql2 varchar2(500);
439
440 --Only for viewby Customer
441 l_inner_cst_select varchar2(70);
442 l_inner_cst_group varchar2(50);
443 l_self_drill_select varchar2(200);
444
445 --For Sorting
446 l_order_by varchar2(500);
447
448 --For sending Customer id to detail reports
449 l_customer_select varchar2(500);
450 l_gt_hint varchar2(500);
451 BEGIN
452
453 fii_ar_util_pkg.reset_globals;
454
455 --Call to get all the parameters in the report
456 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
457
458 --Get the order by clause for the report
459 l_order_by := get_order_by;
460
461 --This call will populate fii_ar_summary_gt table
462 fii_ar_util_pkg.populate_summary_gt_tables;
463 l_gt_hint := ' leading(gt) cardinality(gt 1) ';
464 --Amount Drills
465
466 IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
467 l_rec_amt_drill_2 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
468 l_wadp_drill_1 := 'pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
469 ELSE
470 l_rec_amt_drill_2 := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
471 l_wadp_drill_1 := 'pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
472 END IF;
473
474 l_rec_amt_drill := 'pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
475
476 --Customer Account Parameter is to be sent to the detail report only in case of viewby Customer Account
477 IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
478 l_rec_amt_drill_1 := 'pFunctionName=FII_AR_RCT_ACT_DTL&BIS_PMV_DRILL_CODE_FII_CUSTOMER_ACCOUNT=VIEWBYID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||inline_view.party_id||''&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
479 l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
480 ELSE
481 l_rec_amt_drill_1 := 'pFunctionName=FII_AR_RCT_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
482 END IF;
483
484 l_wadp_drill := 'pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y';
485
486 -- Default View by select clause and self drill select clause
487 --This will change in case of View by Customer
488 l_self_drill_select := '''''';
489
490 --Setting up the where clauses based on the Parameter and viewby
491 --for Collector Dimension
492 IF (fii_ar_util_pkg.g_collector_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR') THEN
493 l_collector_where := 'AND f.collector_id = t.collector_id';
494 END IF;
495
496 --Customer Dimension where clause
497 IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
498 l_child_party_where := ' AND f.party_id = t.party_id ';
499 END IF;
500
501 -- Defining industry where clause for specific industry (when view by is not Customer) or when viewby is Industry
502 IF (fii_ar_util_pkg.g_industry_id <> '-111' AND fii_ar_util_pkg.g_view_by <> 'CUSTOMER+FII_CUSTOMERS') OR
503 fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
504
505 l_industry_where := ' AND t.class_code = f.class_code AND t.class_category = f.class_category';
506 END IF;
507
508 -- Select, where, group by clauses based on viewby
509 If((fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS')
510 OR (fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR')
511 OR (fii_ar_util_pkg.g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS')) THEN
512
513 l_select_sql1 := ''''||l_rec_amt_drill_2||'''';
514 l_select_sql2 := ''''||l_wadp_drill_1||'''';
515
516 ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN
517
518 l_select_sql1 := ''''||l_rec_amt_drill_1||'''';
519 l_select_sql2 := '''''';
520
521 l_cust_acct_where := 'AND f.cust_account_id = t.cust_account_id';
522 l_customer_select := ' t.party_id party_id, ';
523
524 ELSIF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
525
526 IF (fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') THEN
527 l_select_sql1 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_rec_amt_drill_1||''', DECODE(is_leaf_flag,''Y'', '''||l_rec_amt_drill_1||''', '''||l_rec_amt_drill||'''))';
528 l_select_sql2 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''', DECODE(is_leaf_flag,''Y'', '''', '''||l_wadp_drill||'''))';
529
530 --Self drill. This is reqd only in case of Viewby Customer
531 --Check dynamically if the node is leaf or not. In case of non-leaf this drill is to be enabled.
532 l_self_drill := 'pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
533 l_self_drill_select := 'DECODE(inline_view.is_self_flag, ''Y'' , '''', DECODE(inline_view.is_leaf_flag, ''Y'','''','''||l_self_drill||'''))';
534
535 --Select and group by clauses for hierarchical Customer dimension
536 l_inner_cst_select := 'is_self_flag is_self_flag, is_leaf_flag is_leaf_flag,';
537 l_inner_cst_group := ', is_self_flag, is_leaf_flag';
538
539 ELSE
540 l_select_sql1 := ''''||l_rec_amt_drill_1||'''';
541 l_select_sql2 := '''''';
542
543 END IF;
544
545 -- Where clause for view by Customer Dimension
546 l_party_where := 'AND f.parent_party_id = t.parent_party_id';
547
548
549 END IF;
550
551 IF fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
552 l_inner_cst_group := l_inner_cst_group || ' ,t.party_id';
553 END IF;
554
555 --Call to set up the global variables for scaling factor and sign for billed amount
556 get_scaling_factor;
557
558 --Formation of the sql for the report
559 sqlstmt := 'SELECT
560 inline_view.viewby VIEWBY,
561 inline_view .viewby_id VIEWBYID,
562 ((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)
563 + (DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
564 - DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC))/
565 NULLIF((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)
566 + (DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
567 - DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)),0)) * 100 FII_AR_COLL_EFF_INDEX,
568 (((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)
569 +(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
570 -DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC))/
571 NULLIF((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)
572 +(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
573 -DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)),0)) * 100) -
574 (((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
575 +(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
576 -DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC))/
577 NULLIF((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
578 +(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
579 -DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)),0)) * 100) FII_AR_COLL_EFF_CHANGE,
580 (FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_DAYS_PAID,
581 ((FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) - (FII_AR_PRIOR_WTD_DP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0)))FII_AR_CHANGE_DP,
582 ((FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) - (FII_AR_WTD_TERMS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0))) FII_AR_DAYS_DELQ,
583 (((FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) - (FII_AR_WTD_TERMS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)))
584 -
585 ((FII_AR_PRIOR_WTD_DP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0)) - (FII_AR_PRIOR_WTD_TP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0))) ) FII_AR_CHANGE_DD,
586 (FII_AR_WTD_TERMS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_TERMS_PAID,
587 ((FII_AR_WTD_TERMS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) - (FII_AR_PRIOR_WTD_TP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0))) FII_AR_CHANGE_TP,
588 FII_AR_BILLED_AMOUNT FII_AR_BILLED_AMOUNT,
589 FII_AR_REC_AMT FII_AR_REC_AMT,
590 ((sum(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)) over()
591 +sum(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') over()
592 -sum(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)) over())/
593 NULLIF((sum(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))over()
594 +sum(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') over()
595 -sum(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)) over()),0)) * 100 FII_AR_GT_COLL_EFF_IND,
596 (((sum(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)) over()
597 +sum(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') over()
598 -sum(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)) over())/
599 NULLIF((sum(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))over()
600 +sum(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') over()
601 -sum(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)) over()),0)) * 100 ) -
602 (((sum(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)) over()
603 +sum(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||') over()
604 -sum(DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC)) over())/
605 NULLIF((sum(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)) over()
606 +sum(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||') over()
607 -sum(DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)) over()),0)) * 100 ) FII_AR_GT_COLL_EFF_CHG,
608 (SUM(FII_AR_WTD_DAYS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)) FII_AR_GT_DP,
609 ((SUM(FII_AR_WTD_DAYS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)) - (SUM(FII_AR_PRIOR_WTD_DP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0))) FII_AR_GT_DP_CHG,
610 ((SUM(FII_AR_WTD_DAYS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)) - (SUM(FII_AR_WTD_TERMS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0))) FII_AR_GT_DD,
611 (((SUM(FII_AR_WTD_DAYS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)) - (SUM(FII_AR_WTD_TERMS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)))
612 -
613 ((SUM(FII_AR_PRIOR_WTD_DP_NUM) OVER() /NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0)) - (SUM(FII_AR_PRIOR_WTD_TP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0))) ) FII_AR_GT_DD_CHG,
614 (SUM(FII_AR_WTD_TERMS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)) FII_AR_GT_TP,
615 ((SUM(FII_AR_WTD_TERMS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)) - (SUM(FII_AR_PRIOR_WTD_TP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0))) FII_AR_GT_TP_CHG,
616 SUM(FII_AR_BILLED_AMOUNT) OVER() FII_AR_GT_BILLED_AMT,
617 SUM(FII_AR_REC_AMT) OVER() FII_AR_GT_REC_AMT,
618 ((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
619 + (DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
620 - DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC))/
621 NULLIF((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0,FII_AR_PRIOR_BEG_OPEN_REC)
622 + (DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
623 - DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)),0)) * 100 FII_AR_PRIO_COLL_EFF_INDEX_G,
624 ((FII_AR_PRIOR_WTD_DP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0)) - (FII_AR_PRIOR_WTD_TP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0))) FII_AR_AVG_DD_PRIOR_G,
625 '||l_self_drill_select ||' FII_AR_VIEW_BY_DRILL,
626 DECODE(FII_AR_REC_AMT,0,'''',DECODE(NVL(FII_AR_REC_AMT,-999999),-999999,'''',
627 '||l_select_sql1||')) FII_AR_REC_AMT_DRILL,
628 DECODE((FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)),0,'''',DECODE(NVL((FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)),-999999),-999999,'''',
629 '||l_select_sql2||')) FII_AR_WADP_DRILL,
630 FII_AR_PRIOR_REC_AMT FII_AR_PRIOR_REC_AMT,
631 SUM(FII_AR_PRIOR_REC_AMT) OVER() FII_AR_GT_PRIOR_REC_AMT,
632 FII_AR_PRIOR_WTD_DP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0) FII_AR_PRIOR_WTD_AVG_DP,
633 ((FII_AR_PRIOR_WTD_DP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0)) - (FII_AR_PRIOR_WTD_TP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0))) FII_AR_PRIOR_WTD_AVG_DD,
634 NVL((SUM(FII_AR_PRIOR_WTD_DP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0)),0) FII_AR_GT_PRIOR_WTD_AVG_DP,
635 NVL( ((SUM(FII_AR_PRIOR_WTD_DP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0))
636 - (SUM(FII_AR_PRIOR_WTD_TP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0))),0) FII_AR_GT_PRIOR_WTD_AVG_DD
637 FROM (
638 SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ VIEWBY,
639 viewby_code viewby_id,
640 '|| l_inner_cst_select || l_customer_select ||'
641 SUM(DECODE(t.report_date, :CURR_PERIOD_START ,
642 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
643 THEN total_open_amount ELSE NULL END) ) ) FII_AR_BEG_OPEN_REC,
644 SUM(DECODE(t.report_date, :ASOF_DATE,
645 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
646 THEN total_open_amount ELSE NULL END) ) ) FII_AR_END_OPEN_REC,
647 SUM(DECODE(t.report_date, :ASOF_DATE,
648 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
649 THEN current_open_amount ELSE NULL END) ) ) FII_AR_END_CURR_REC,
650 SUM(DECODE(t.report_date, :ASOF_DATE,
651 (CASE WHEN bitand(t.record_type_id,:BITAND) = :BITAND
652 THEN billed_amount ELSE NULL END) ) ) FII_AR_BILLED_AMOUNT,
653 SUM(DECODE(t.report_date, :ASOF_DATE,
654 (CASE WHEN bitand(t.record_type_id,:BITAND) = :BITAND
655 THEN app_amount ELSE NULL END) ) ) FII_AR_APPLIED_AMOUNT,
656 SUM(DECODE(t.report_date, :ASOF_DATE,
657 (CASE WHEN bitand(t.record_type_id,:BITAND) = :BITAND
658 THEN wtd_days_paid_num ELSE NULL END) ) ) FII_AR_WTD_DAYS_PAID_NUM,
659 SUM(DECODE(t.report_date, :ASOF_DATE,
660 (CASE WHEN bitand(t.record_type_id,:BITAND) = :BITAND
661 THEN wtd_terms_paid_num ELSE NULL END) ) ) FII_AR_WTD_TERMS_PAID_NUM,
662 SUM(DECODE(t.report_date, :ASOF_DATE,
663 (CASE WHEN (f.header_filter_date <= :ASOF_DATE) and
664 (f.header_filter_date >= :CURR_PERIOD_START) and (bitand(t.record_type_id,:BITAND) = :BITAND)
665 THEN total_receipt_amount ELSE NULL END) ) ) FII_AR_REC_AMT,
666 SUM(DECODE(t.report_date, :PRIOR_PERIOD_START,
667 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
668 THEN total_open_amount ELSE NULL END) ) ) FII_AR_PRIOR_BEG_OPEN_REC,
669 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
670 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
671 THEN total_open_amount ELSE NULL END) ) ) FII_AR_PRIOR_END_OPEN_REC,
672 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
673 (CASE WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
674 THEN current_open_amount ELSE NULL END) ) ) FII_AR_PRIOR_END_CURR_REC,
675 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
676 (CASE WHEN bitand(t.record_type_id,:BITAND) = :BITAND
677 THEN billed_amount ELSE NULL END) ) ) FII_AR_PRIOR_BILLED_AMOUNT,
678 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
679 (CASE WHEN bitand(t.record_type_id,:BITAND) = :BITAND
680 THEN app_amount ELSE NULL END) ) ) FII_AR_PRIOR_APPLIED_AMOUNT,
681 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
682 (CASE WHEN bitand(t.record_type_id,:BITAND) = :BITAND
683 THEN wtd_days_paid_num ELSE NULL END) ) ) FII_AR_PRIOR_WTD_DP_NUM,
684 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
685 (CASE WHEN bitand(t.record_type_id,:BITAND) = :BITAND
686 THEN wtd_terms_paid_num ELSE NULL END) ) ) FII_AR_PRIOR_WTD_TP_NUM,
687 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
688 (CASE WHEN (f.header_filter_date <= :PREVIOUS_ASOF_DATE) and
689 (f.header_filter_date >= :PRIOR_PERIOD_START) and (bitand(t.record_type_id,:BITAND) = :BITAND)
690 THEN total_receipt_amount ELSE NULL END) ) ) FII_AR_PRIOR_REC_AMT
691 FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
692 ||' f,( SELECT /*+ no_merge '||l_gt_hint|| ' */ *
693 FROM fii_time_structures cal,
694 '||fii_ar_util_pkg.get_from_statement||' gt
695 WHERE report_date in ( :CURR_PERIOD_START ,
696 :ASOF_DATE,
697 :PREVIOUS_ASOF_DATE,
698 :PRIOR_PERIOD_START
699 )
700 AND ( bitand(cal.record_type_id, :BITAND) = :BITAND OR
701 bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
702 )
703 AND '||fii_ar_util_pkg.get_where_statement||'
704 ) t
705 WHERE f.time_id = t.time_id
706 AND f.period_type_id = t.period_type_id '||l_child_party_where||'
707 AND f.org_id = t.org_id
708 AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' '|| l_cust_acct_where ||' '|| l_industry_where ||'
709 GROUP BY viewby_code, VIEWBY '||l_inner_cst_group||' ) inline_view
710 '||l_order_by;
711
712 fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, coll_eff_sql, coll_eff_output);
713
714 END get_coll_eff;
715
716 -- This procedure will provide sql statement to retrieve data for Collection Effectiveness Report
717 PROCEDURE get_coll_eff_trend(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
718 coll_eff_trend_sql out NOCOPY VARCHAR2, coll_eff_trend_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
719
720 sqlstmt VARCHAR2(32767); --Final sql statement
721 l_curr_sql_stmt VARCHAR2(10000); --Intermediate sql statement to hold the Current periods sql
722 l_select_clause VARCHAR2(30000); --Intermediate sql statement for the upper select part of the final sql
723
724 --Variables for where clauses
725 l_collector_where VARCHAR2(300); --Collector Dimension where clause
726 l_party_where VARCHAR2(300); --Customer Dimension where clause
727 l_industry_where VARCHAR2(300); --Industry Dimension where clause
728
729 --Time table variable
730 l_time_table varchar2(30); --Variable to hold time table based on Period Type parameter
731
732 --Date bind variable
733 l_date_bind varchar2(20); --Date bind variable decided based on as of date
734
735 --Variables for prior columns select
736 l_prior_column VARCHAR2(5000); --Prior columns select clause
737 l_current_prior_column varchar2(5000); --Prior columns select clause for current period
738
739 BEGIN
740
741 fii_ar_util_pkg.reset_globals;
742 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
743 fii_ar_util_pkg.g_view_by := 'ORGANIZATION+FII_OPERATING_UNITS';
744
745 --Call to set up the global variables for scaling factor and sign for billed amount
746 get_scaling_factor;
747
748 --This call will populate fii_ar_summary_gt table
749 fii_ar_util_pkg.populate_summary_gt_tables;
750
751 --Decide which time table needs to be hit
752 IF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK') THEN
753 l_time_table := 'FII_TIME_WEEK';
754 ELSIF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_PERIOD') THEN
755 l_time_table := 'FII_TIME_ENT_PERIOD';
756 ELSIF (fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') THEN
757 l_time_table := 'FII_TIME_ENT_QTR';
758 ELSE
759 l_time_table := 'FII_TIME_ENT_YEAR';
760 END IF;
761
762 --Setting up the where clauses based on the Parameter
763 --for Collector Dimension
764 IF (fii_ar_util_pkg.g_collector_id <> '-111' ) THEN
765 l_collector_where := 'AND f.collector_id = t.collector_id';
766 END IF;
767
768 --Customer Dimension where clause
769 IF (fii_ar_util_pkg.g_party_id <> '-111' ) THEN
770 l_party_where := ' AND f.party_id = t.party_id ';
771 END IF;
772
773 --Industry where clause
774 IF fii_ar_util_pkg.g_industry_id <> '-111' THEN
775 l_industry_where := ' AND t.class_code = f.class_code AND t.class_category = f.class_category';
776 END IF;
777
778 -------------------------------------------------------------------------------
779 --Drills over WADP, CEI and Receipts Amount
780 --1) WADP Drills to Collection Effectiveness Report with viewby OU
781 --2) CEI Drills to Collection Effectiveness Index Summary Report with viewby OU
782 --3) Receipts Amount drills to Receipts Activity Report with viewby OU
783 -------------------------------------------------------------------------------
784
785 /*-------------------------------------------------------------------------------
786 Bind Variable Description
787 -------------- -------------
788 :ASOF_DATE As Of Date
789 :CURR_PERIOD_START Start Date of the current period
790 :BITAND XTD Bitand value
791 :BITAND_INC_TODATE Bitand for ITD
792 :PREVIOUS_ASOF_DATE Prior as of date
793 :PRIOR_PERIOD_START Start Date of the prior period
794 :CURR_PERIOD_END End Date of Current Period
795 :SD_SDATE Start Date for Current Data
796 :SD_PRIOR_PRIOR Start Date of the prior prior period
797 :SD_PRIOR Start date of the prior period
798 -------------------------------------------------------------------------------*/
799
800 /* This condition handles, whether parameter compare to chosen is Prior Period.
801 IF Yes, then no need to show Prior Data Otherwise show Prior data. */
802 -------------------------------------------------------------------
803 --Per FDD when COMPARE TO = PRIOR PERIOD/'SEQUENTIAL' then
804 --No prior data is to be shown
805 --and when COMPARE TO <> PRIOR PERIOD/'SEQUENTIAL' then
806 --Prior Data is to be shown with an exception of period type Year
807 -------------------------------------------------------------------
808
809 IF fii_ar_util_pkg.g_time_comp = 'SEQUENTIAL' OR fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
810 l_prior_column:= ' NULL FII_PRIOR_AR_BEG_OPEN_REC,
811 NULL FII_PRIOR_AR_END_OPEN_REC,
812 NULL FII_PRIOR_AR_END_CURR_REC,
813 NULL FII_PRIOR_AR_BILLED_AMOUNT, ';
814
815 l_current_prior_column:= ' NULL FII_PRIOR_AR_BEG_OPEN_REC,
816 NULL FII_PRIOR_AR_END_OPEN_REC,
817 NULL FII_PRIOR_AR_END_CURR_REC,
818 NULL FII_PRIOR_AR_BILLED_AMOUNT, ';
819
820 ELSE
821 l_prior_column:= ' SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
822 WHERE start_date = t.report_date), ''Y'' ,
823 CASE
824 WHEN t.report_date < :SD_SDATE
825 AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
826 THEN f.total_open_amount
827 ELSE null
828 END )) FII_PRIOR_AR_BEG_OPEN_REC,
829 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
830 WHERE start_date = t.report_date), ''Y'', null,
831 CASE
832 WHEN t.report_date < :SD_SDATE
833 AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
834 THEN f.total_open_amount
835 ELSE null
836 END )) FII_PRIOR_AR_END_OPEN_REC,
837 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
838 WHERE start_date = t.report_date), ''Y'' , null,
839 CASE
840 WHEN t.report_date < :SD_SDATE
841 AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
842 THEN f.current_open_amount
843 ELSE null
844 END )) FII_PRIOR_AR_END_CURR_REC,
845 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
846 WHERE start_date = t.report_date), ''Y'', null,
847 CASE
848 WHEN t.report_date < :SD_SDATE
849 AND bitand(t.record_type_id, :BITAND) = :BITAND
850 THEN f.billed_amount
851 ELSE NULL
852 END )) FII_PRIOR_AR_BILLED_AMOUNT, ';
853
854 l_current_prior_column := ' SUM(DECODE(bitand(t.record_type_id,:BITAND_INC_TODATE), :BITAND_INC_TODATE,
855 CASE
856 WHEN t.report_date = :PRIOR_PERIOD_START /*This needs to be the first date of the month */
857 THEN f.total_open_amount
858 ELSE null
859 END
860 )) FII_PRIOR_AR_BEG_OPEN_REC,
861 SUM(DECODE(bitand(t.record_type_id,:BITAND_INC_TODATE), :BITAND_INC_TODATE,
862 CASE
863 WHEN t.report_date = :PREVIOUS_ASOF_DATE
864 AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
865 THEN f.total_open_amount
866 ELSE null
867 END )) FII_PRIOR_AR_END_OPEN_REC,
868 SUM(DECODE(bitand(t.record_type_id,:BITAND_INC_TODATE), :BITAND_INC_TODATE,
869 CASE
870 WHEN t.report_date = :PREVIOUS_ASOF_DATE
871 AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
872 THEN f.current_open_amount
873 ELSE null
874 END )) FII_PRIOR_AR_END_CURR_REC,
875 SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
876 CASE
877 WHEN t.report_date = :PREVIOUS_ASOF_DATE
878 AND bitand(t.record_type_id, :BITAND) = :BITAND
879 THEN f.billed_amount
880 ELSE NULL
881 END )) FII_PRIOR_AR_BILLED_AMOUNT, ';
882
883 END IF;
884
885 ---------------------------------------------------------------
886 --Code to decide which Date bind variable to be used in the sql
887 --IF as of date = current period end then
888 --union all is not required
889 --else union all part will be used for the current period
890 ----------------------------------------------------------------
891 IF( fii_ar_util_pkg.g_as_of_date = fii_ar_util_pkg.g_curr_per_end) THEN
892 l_date_bind := ':ASOF_DATE';
893
894 l_curr_sql_stmt := ' ';
895
896 /*Upper select clause. This is common for all period types*/
897 -- In this case Union All is not required which means all periods are fully exhausted and
898 -- hence the scaling factor are constant for all
899
900 l_select_clause := 'SELECT
901 cy_per.name FII_AR_COLL_EFF_VIEW_BY,
902 FII_AR_COLL_EFF_INDEX * 100 FII_AR_COLL_EFF_INDEX,
903 FII_AR_PRIO_COLL_EFF_INDEX_G * 100 FII_AR_PRIO_COLL_EFF_INDEX_G,
904 FII_AR_WTD_AVG_DP,
905 (FII_AR_WTD_AVG_DP - FII_AR_WTD_AVG_TP) FII_AR_DAYS_DELQ,
906 FII_AR_WTD_AVG_TP,
907 FII_AR_REC_AMT,
908 DECODE(FII_AR_WTD_AVG_DP,0,'''',DECODE(NVL(FII_AR_WTD_AVG_DP, -99999),-99999, '''',DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
909 ''pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
910 ''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'' )))
911 FII_AR_DAYS_PAID_DRILL,
912 DECODE(FII_AR_COLL_EFF_INDEX,0,'''',DECODE(NVL(FII_AR_COLL_EFF_INDEX, -99999),-99999, '''',DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
913 ''pFunctionName=FII_AR_COLL_EFF_INDEX&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
914 ''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_COLL_EFF_INDEX&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'' )))
915 FII_AR_CEI_DRILL,
916 DECODE(FII_AR_REC_AMT,0,'''',DECODE(NVL(FII_AR_REC_AMT, -99999),-99999, '''', DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
917 ''pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
918 ''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'' )))
919 FII_AR_RCT_AMT_DRILL
920 FROM '||l_time_table||' cy_per, (
921 SELECT
922 sequence sequence,
923 (SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))
924 +(SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
925 - SUM(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)))
926 /NULLIF((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))
927 +(SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
928 -SUM(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC))),0) FII_AR_COLL_EFF_INDEX,
929 SUM(FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_WTD_AVG_DP,
930 SUM(FII_AR_AVG_DD_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_DAYS_DELQ,
931 SUM(FII_AR_WTD_TERMS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_WTD_AVG_TP,
932 SUM(FII_AR_REC_AMT) FII_AR_REC_AMT,
933 (SUM(DECODE(FII_PRIOR_AR_BEG_OPEN_REC, NULL, 0, FII_PRIOR_AR_BEG_OPEN_REC))
934 +(SUM(DECODE(FII_PRIOR_AR_BILLED_AMOUNT, NULL, 0, FII_PRIOR_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
935 -SUM(DECODE(FII_PRIOR_AR_END_OPEN_REC, NULL, 0, FII_PRIOR_AR_END_OPEN_REC)))
936 /NULLIF((SUM(DECODE(FII_PRIOR_AR_BEG_OPEN_REC, NULL, 0, FII_PRIOR_AR_BEG_OPEN_REC))
937 +(SUM(DECODE(FII_PRIOR_AR_BILLED_AMOUNT, NULL, 0, FII_PRIOR_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
938 - SUM(DECODE(FII_PRIOR_AR_END_CURR_REC, NULL, 0, FII_PRIOR_AR_END_CURR_REC))),0) FII_AR_PRIO_COLL_EFF_INDEX_G
939 from (
940 SELECT per.name,per.sequence,
941 sum(FII_AR_REC_AMT) FII_AR_REC_AMT,
942 sum(FII_AR_BEG_OPEN_REC) FII_AR_BEG_OPEN_REC,
943 sum(FII_AR_END_OPEN_REC) FII_AR_END_OPEN_REC,
944 sum(FII_AR_END_CURR_REC) FII_AR_END_CURR_REC,
945 sum(FII_PRIOR_AR_BEG_OPEN_REC) FII_PRIOR_AR_BEG_OPEN_REC,
946 sum(FII_PRIOR_AR_END_OPEN_REC)FII_PRIOR_AR_END_OPEN_REC,
947 sum(FII_PRIOR_AR_END_CURR_REC) FII_PRIOR_AR_END_CURR_REC,
948 sum(FII_PRIOR_AR_BILLED_AMOUNT) FII_PRIOR_AR_BILLED_AMOUNT,
949 sum(FII_AR_BILLED_AMOUNT) FII_AR_BILLED_AMOUNT,
950 sum(FII_AR_APPLIED_AMOUNT) FII_AR_APPLIED_AMOUNT,
951 sum(FII_AR_WTD_DAYS_PAID_NUM) FII_AR_WTD_DAYS_PAID_NUM,
952 sum(FII_AR_WTD_TERMS_PAID_NUM) FII_AR_WTD_TERMS_PAID_NUM,
953 sum(FII_AR_AVG_DD_NUM) FII_AR_AVG_DD_NUM FROM '||l_time_table||' per,(';
954
955 ELSE
956 l_date_bind := ':CURR_PERIOD_START';
957 l_curr_sql_stmt := ' UNION ALL
958 SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
959 per.name,
960 per.sequence sequence,
961 SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
962 CASE
963 WHEN (t.report_date = :ASOF_DATE)
964 AND (f.header_filter_date <= :ASOF_DATE )
965 AND (f.header_filter_date >= :CURR_PERIOD_START)
966 THEN f.total_receipt_amount
967 ELSE null
968 END
969 )) FII_AR_REC_AMT,
970 SUM(DECODE(bitand(t.record_type_id,:BITAND_INC_TODATE), :BITAND_INC_TODATE,
971 CASE
972 WHEN t.report_date = :CURR_PERIOD_START
973 THEN f.total_open_amount
974 ELSE null
975 END
976 )) FII_AR_BEG_OPEN_REC,
977 SUM(DECODE(bitand(t.record_type_id,:BITAND_INC_TODATE), :BITAND_INC_TODATE,
978 CASE
979 WHEN t.report_date = :ASOF_DATE
980 THEN f.total_open_amount
981 ELSE null
982 END
983 )) FII_AR_END_OPEN_REC,
984 SUM(DECODE(bitand(t.record_type_id,:BITAND_INC_TODATE), :BITAND_INC_TODATE,
985 CASE
986 WHEN t.report_date = :ASOF_DATE
987 THEN f.current_open_amount
988 ELSE null
989 END
990 )) FII_AR_END_CURR_REC,
991 '||l_current_prior_column||'
992 SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
993 CASE
994 WHEN t.report_date = :ASOF_DATE
995 THEN f.billed_amount
996 ELSE null
997 END
998 )) FII_AR_BILLED_AMOUNT,
999 SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
1000 CASE
1001 WHEN t.report_date = :ASOF_DATE
1002 THEN f.app_amount
1003 ELSE null
1004 END
1005 )) FII_AR_APPLIED_AMOUNT,
1006 SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
1007 CASE
1008 WHEN t.report_date = :ASOF_DATE
1009 THEN f.wtd_days_paid_num
1010 ELSE null
1011 END
1012 )) FII_AR_WTD_DAYS_PAID_NUM,
1013 SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
1014 CASE
1015 WHEN t.report_date = :ASOF_DATE
1016 THEN f.wtd_terms_paid_num
1017 ELSE null
1018 END
1019 )) FII_AR_WTD_TERMS_PAID_NUM,
1020 SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
1021 CASE
1022 WHEN t.report_date = :ASOF_DATE
1023 THEN f.avg_dd_num
1024 ELSE null
1025 END
1026 )) FII_AR_AVG_DD_NUM
1027 FROM '||l_time_table||' per,
1028 FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
1029 (
1030 SELECT /*+ no_merge leading(gt) cardinality(gt 1)*/ *
1031 FROM fii_time_structures cal,
1032 fii_ar_summary_gt gt
1033 WHERE report_date in(:ASOF_DATE, :PREVIOUS_ASOF_DATE, :PRIOR_PERIOD_START, :CURR_PERIOD_START)
1034 AND
1035 (
1036 bitand(cal.record_type_id, :BITAND) = :BITAND
1037 OR bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
1038 )
1039 ) t
1040 WHERE f.time_id = t.time_id
1041 AND f.period_type_id = t.period_type_id
1042 AND f.org_id = t.org_id '|| l_party_where ||' ' || l_collector_where ||' ' || l_industry_where || '
1043 AND per.end_date = :CURR_PERIOD_END
1044 AND '||fii_ar_util_pkg.get_mv_where_statement||'
1045 GROUP BY t.report_date, per.sequence, name';
1046
1047 /*Upper select clause. This is common for all period types*/
1048 -- In case when Union All is used which means that the current period should use a different scaling factor compared to
1049 -- the other periods
1050
1051 l_select_clause := 'SELECT
1052 cy_per.name FII_AR_COLL_EFF_VIEW_BY,
1053 FII_AR_COLL_EFF_INDEX * 100 FII_AR_COLL_EFF_INDEX,
1054 FII_AR_PRIO_COLL_EFF_INDEX_G * 100 FII_AR_PRIO_COLL_EFF_INDEX_G,
1055 FII_AR_WTD_AVG_DP,
1056 (FII_AR_WTD_AVG_DP - FII_AR_WTD_AVG_TP) FII_AR_DAYS_DELQ,
1057 FII_AR_WTD_AVG_TP,
1058 FII_AR_REC_AMT,
1059 DECODE(FII_AR_WTD_AVG_DP,0,'''',DECODE(NVL(FII_AR_WTD_AVG_DP, -99999),-99999, '''',DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
1060 ''pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
1061 ''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'' )))
1062 FII_AR_DAYS_PAID_DRILL,
1063 DECODE(FII_AR_COLL_EFF_INDEX,0,'''',DECODE(NVL(FII_AR_COLL_EFF_INDEX, -99999),-99999, '''',DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
1064 ''pFunctionName=FII_AR_COLL_EFF_INDEX&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
1065 ''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_COLL_EFF_INDEX&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'' )))
1066 FII_AR_CEI_DRILL,
1067 DECODE(FII_AR_REC_AMT,0,'''',DECODE(NVL(FII_AR_REC_AMT, -99999),-99999, '''', DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
1068 ''pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
1069 ''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'' )))
1070 FII_AR_RCT_AMT_DRILL
1071 FROM '||l_time_table||' cy_per, (
1072 SELECT
1073 sequence sequence,
1074 DECODE (sequence, ' || g_current_sequence ||',
1075 (SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))
1076 +(SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT))'||g_scale_sign||' '||g_scaling_factor||')
1077 - SUM(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)))/NULLIF((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))
1078 +(SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT))'||g_scale_sign||' '||g_scaling_factor||')
1079 -SUM(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC))),0),
1080 (SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))
1081 +(SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
1082 - SUM(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)))/NULLIF((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))
1083 +(SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
1084 -SUM(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC))),0) )FII_AR_COLL_EFF_INDEX,
1085 SUM(FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_WTD_AVG_DP,
1086 SUM(FII_AR_AVG_DD_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_DAYS_DELQ,
1087 SUM(FII_AR_WTD_TERMS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_WTD_AVG_TP,
1088 SUM(FII_AR_REC_AMT) FII_AR_REC_AMT,
1089 DECODE (sequence, ' || g_current_sequence ||',
1090 (SUM(DECODE(FII_PRIOR_AR_BEG_OPEN_REC, NULL, 0, FII_PRIOR_AR_BEG_OPEN_REC))
1091 +(SUM(DECODE(FII_PRIOR_AR_BILLED_AMOUNT, NULL, 0, FII_PRIOR_AR_BILLED_AMOUNT))'||g_scale_sign||' '||g_scaling_factor||')
1092 -SUM(DECODE(FII_PRIOR_AR_END_OPEN_REC, NULL, 0, FII_PRIOR_AR_END_OPEN_REC)))/NULLIF((SUM(DECODE(FII_PRIOR_AR_BEG_OPEN_REC, NULL, 0, FII_PRIOR_AR_BEG_OPEN_REC))
1093 +(SUM(DECODE(FII_PRIOR_AR_BILLED_AMOUNT, NULL, 0, FII_PRIOR_AR_BILLED_AMOUNT))'||g_scale_sign||' '||g_scaling_factor||')
1094 - SUM(DECODE(FII_PRIOR_AR_END_CURR_REC, NULL, 0, FII_PRIOR_AR_END_CURR_REC))),0),
1095 (SUM(DECODE(FII_PRIOR_AR_BEG_OPEN_REC, NULL, 0, FII_PRIOR_AR_BEG_OPEN_REC))
1096 +(SUM(DECODE(FII_PRIOR_AR_BILLED_AMOUNT, NULL, 0, FII_PRIOR_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
1097 -SUM(DECODE(FII_PRIOR_AR_END_OPEN_REC, NULL, 0, FII_PRIOR_AR_END_OPEN_REC)))/NULLIF((SUM(DECODE(FII_PRIOR_AR_BEG_OPEN_REC, NULL, 0, FII_PRIOR_AR_BEG_OPEN_REC))
1098 +(SUM(DECODE(FII_PRIOR_AR_BILLED_AMOUNT, NULL, 0, FII_PRIOR_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
1099 - SUM(DECODE(FII_PRIOR_AR_END_CURR_REC, NULL, 0, FII_PRIOR_AR_END_CURR_REC))),0) ) FII_AR_PRIO_COLL_EFF_INDEX_G
1100 from (
1101 SELECT per.name,per.sequence,
1102 sum(FII_AR_REC_AMT) FII_AR_REC_AMT,
1103 sum(FII_AR_BEG_OPEN_REC) FII_AR_BEG_OPEN_REC,
1104 sum(FII_AR_END_OPEN_REC) FII_AR_END_OPEN_REC,
1105 sum(FII_AR_END_CURR_REC) FII_AR_END_CURR_REC,
1106 sum(FII_PRIOR_AR_BEG_OPEN_REC) FII_PRIOR_AR_BEG_OPEN_REC,
1107 sum(FII_PRIOR_AR_END_OPEN_REC)FII_PRIOR_AR_END_OPEN_REC,
1108 sum(FII_PRIOR_AR_END_CURR_REC) FII_PRIOR_AR_END_CURR_REC,
1109 sum(FII_PRIOR_AR_BILLED_AMOUNT) FII_PRIOR_AR_BILLED_AMOUNT,
1110 sum(FII_AR_BILLED_AMOUNT) FII_AR_BILLED_AMOUNT,
1111 sum(FII_AR_APPLIED_AMOUNT) FII_AR_APPLIED_AMOUNT,
1112 sum(FII_AR_WTD_DAYS_PAID_NUM) FII_AR_WTD_DAYS_PAID_NUM,
1113 sum(FII_AR_WTD_TERMS_PAID_NUM) FII_AR_WTD_TERMS_PAID_NUM,
1114 sum(FII_AR_AVG_DD_NUM) FII_AR_AVG_DD_NUM FROM '||l_time_table||' per,(';
1115
1116
1117 END IF;
1118
1119
1120 IF fii_ar_util_pkg.g_page_period_type = 'FII_TIME_WEEK' OR fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_PERIOD' OR fii_ar_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR' THEN
1121 sqlstmt := l_select_clause || '
1122 SELECT /*+ no_merge INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
1123 t.report_date,
1124 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1125 WHERE start_date = t.report_date), ''Y'' , null,
1126 CASE
1127 WHEN (t.report_date >= :SD_SDATE )
1128 AND (f.header_filter_date <= t.report_date)
1129 AND (f.header_filter_date >= (select start_date
1130 from '||l_time_table||'
1131 where end_date = t.report_date))
1132 AND (bitand(t.record_type_id, :BITAND) = :BITAND )
1133 THEN f.total_receipt_amount
1134 ELSE NULL
1135 END )) FII_AR_REC_AMT,
1136 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1137 WHERE start_date = t.report_date), ''Y'' ,
1138 CASE
1139 WHEN t.report_date >= :SD_SDATE
1140 AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE /*This needs to be the first date of the month */
1141 THEN f.total_open_amount
1142 ELSE null
1143 END )) FII_AR_BEG_OPEN_REC,
1144 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1145 WHERE start_date = t.report_date), ''Y'', null,
1146 CASE
1147 WHEN t.report_date >= :SD_SDATE
1148 AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
1149 THEN f.total_open_amount
1150 ELSE null
1151 END
1152 )) FII_AR_END_OPEN_REC,
1153 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1154 WHERE start_date = t.report_date), ''Y'' , null,
1155 CASE
1156 WHEN t.report_date >= :SD_SDATE
1157 AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
1158 THEN f.current_open_amount
1159 ELSE null
1160 END
1161 )) FII_AR_END_CURR_REC,
1162 '||l_prior_column||'
1163 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1164 WHERE start_date = t.report_date), ''Y'' , null,
1165 CASE
1166 WHEN t.report_date >= :SD_SDATE
1167 AND bitand(t.record_type_id, :BITAND) = :BITAND
1168 THEN f.billed_amount
1169 ELSE NULL
1170 END))
1171 FII_AR_BILLED_AMOUNT,
1172 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1173 WHERE start_date = t.report_date), ''Y'' , null,
1174 CASE
1175 WHEN t.report_date >= :SD_SDATE
1176 AND bitand(t.record_type_id, :BITAND) = :BITAND
1177 THEN f.app_amount
1178 ELSE NULL
1179 END )) FII_AR_APPLIED_AMOUNT,
1180 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1181 WHERE start_date = t.report_date), ''Y'', null,
1182 CASE
1183 WHEN t.report_date >= :SD_SDATE
1184 AND bitand(t.record_type_id, :BITAND) = :BITAND
1185 THEN f.wtd_days_paid_num
1186 ELSE NULL
1187 END )) FII_AR_WTD_DAYS_PAID_NUM,
1188 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1189 WHERE start_date = t.report_date), ''Y'' , null,
1190 CASE
1191 WHEN t.report_date >= :SD_SDATE
1192 AND bitand(t.record_type_id, :BITAND) = :BITAND
1193 THEN f.wtd_terms_paid_num
1194 ELSE NULL
1195 END )) FII_AR_WTD_TERMS_PAID_NUM,
1196 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1197 WHERE start_date = t.report_date), ''Y'' , null,
1198 CASE
1199 WHEN t.report_date >= :SD_SDATE
1200 AND bitand(t.record_type_id, :BITAND) = :BITAND
1201 THEN f.avg_dd_num
1202 ELSE NULL
1203 END )) FII_AR_AVG_DD_NUM
1204 FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
1205 ||' f,
1206 (
1207 SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) leading(gt) cardinality(gt 1)*/ *
1208 FROM fii_time_structures cal,
1209 fii_ar_summary_gt gt
1210 WHERE report_date in
1211 (
1212 SELECT end_date
1213 FROM '||l_time_table||' cy_per
1214 WHERE cy_per.start_date < '||l_date_bind||'
1215 AND cy_per.start_date >= :SD_PRIOR_PRIOR
1216 UNION
1217 SELECT start_date
1218 FROM '||l_time_table||' cy_per
1219 WHERE cy_per.start_date <'||l_date_bind||'
1220 AND cy_per.start_date >=:SD_PRIOR_PRIOR
1221 )
1222 AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE OR bitand(cal.record_type_id, :BITAND) = :BITAND )
1223 )
1224 t
1225 WHERE f.time_id = t.time_id
1226 AND f.period_type_id = t.period_type_id
1227 AND f.org_id = t.org_id
1228 AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' ' || l_industry_where || '
1229 GROUP BY t.report_date) mv
1230 WHERE per.start_date >= :SD_PRIOR_PRIOR
1231 and ( per.end_date = mv.report_date
1232 OR per.start_date = mv.report_date)
1233 GROUP BY per.sequence, per.name
1234 '||l_curr_sql_stmt||'
1235 )
1236 group by sequence)outer_view
1237 WHERE cy_per.start_date <= :ASOF_DATE
1238 AND cy_per.start_date > :SD_PRIOR
1239 AND cy_per.sequence = outer_view.sequence (+)
1240 ORDER BY cy_per.start_date ';
1241
1242 ELSE
1243 /*This part will be called in case of year
1244 Prior data is not required in case of Year*/
1245
1246
1247 sqlstmt := l_select_clause || '
1248 SELECT
1249 /*+ no_merge INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
1250 t.report_date,
1251 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1252 WHERE start_date = t.report_date), ''Y'' , null,
1253 CASE
1254 WHEN (f.header_filter_date <= t.report_date)
1255 AND (f.header_filter_date >= (select start_date
1256 from '||l_time_table||'
1257 where end_date = t.report_date))
1258 AND (bitand(t.record_type_id, :BITAND) = :BITAND)
1259 THEN f.total_receipt_amount
1260 ELSE NULL
1261 END )) FII_AR_REC_AMT,
1262 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1263 WHERE start_date = t.report_date), ''Y'' ,
1264 CASE
1265 WHEN bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE /*This needs to be the first date of the month */
1266 THEN f.total_open_amount
1267 ELSE null
1268 END )) FII_AR_BEG_OPEN_REC,
1269 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1270 WHERE start_date = t.report_date), ''Y'' , null,
1271 CASE
1272 WHEN bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
1273 THEN f.total_open_amount
1274 ELSE null
1275 END
1276 )) FII_AR_END_OPEN_REC,
1277 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1278 WHERE start_date = t.report_date), ''Y'' , null,
1279 CASE
1280 WHEN bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
1281 THEN f.current_open_amount
1282 ELSE null
1283 END
1284 )) FII_AR_END_CURR_REC,
1285 '||l_prior_column||'
1286 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1287 WHERE start_date = t.report_date), ''Y'' , null,
1288 CASE
1289 WHEN bitand(t.record_type_id, :BITAND) = :BITAND
1290 THEN f.billed_amount
1291 ELSE NULL
1292 END))
1293 FII_AR_BILLED_AMOUNT,
1294 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1295 WHERE start_date = t.report_date), ''Y'' , null,
1296 CASE
1297 WHEN bitand(t.record_type_id, :BITAND) = :BITAND
1298 THEN f.app_amount
1299 ELSE NULL
1300 END )) FII_AR_APPLIED_AMOUNT,
1301 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1302 WHERE start_date = t.report_date), ''Y'' , null,
1303 CASE
1304 WHEN bitand(t.record_type_id, :BITAND) = :BITAND
1305 THEN f.wtd_days_paid_num
1306 ELSE NULL
1307 END )) FII_AR_WTD_DAYS_PAID_NUM,
1308 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1309 WHERE start_date = t.report_date), ''Y'' , null,
1310 CASE
1311 WHEN bitand(t.record_type_id, :BITAND) = :BITAND
1312 THEN f.wtd_terms_paid_num
1313 ELSE NULL
1314 END )) FII_AR_WTD_TERMS_PAID_NUM,
1315 SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
1316 WHERE start_date = t.report_date), ''Y'' , null,
1317 CASE
1318 WHEN bitand(t.record_type_id, :BITAND) = :BITAND
1319 THEN f.avg_dd_num
1320 ELSE NULL
1321 END )) FII_AR_AVG_DD_NUM
1322 FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
1323 ||' f,
1324 (
1325 SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) leading(gt) cardinality(gt 1) */ *
1326 FROM fii_time_structures cal,
1327 fii_ar_summary_gt gt
1328 WHERE report_date in
1329 (
1330 SELECT end_date
1331 FROM '||l_time_table||' cy_per
1332 WHERE cy_per.start_date < '||l_date_bind||'
1333 AND cy_per.start_date >= :SD_PRIOR_PRIOR
1334 UNION
1335 SELECT start_date
1336 FROM '||l_time_table||' cy_per
1337 WHERE cy_per.start_date < '||l_date_bind||'
1338 AND cy_per.start_date >=:SD_PRIOR_PRIOR
1339 )
1340 AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE OR bitand(cal.record_type_id, :BITAND) = :BITAND )
1341 ) t
1342 WHERE f.time_id = t.time_id
1343 AND f.period_type_id = t.period_type_id
1344 AND f.org_id = t.org_id
1345 AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||'
1346 ' || l_collector_where ||'
1347 ' || l_industry_where || '
1348 GROUP BY t.report_date) mv
1349 WHERE per.start_date >= :SD_PRIOR_PRIOR
1350 and ( per.end_date = mv.report_date
1351 OR per.start_date = mv.report_date
1352 )
1353 GROUP BY per.sequence, per.name
1354 '||l_curr_sql_stmt||'
1355 )
1356 group by sequence)outer_view
1357 WHERE cy_per.start_date <= :ASOF_DATE
1358 AND cy_per.start_date > :SD_PRIOR
1359 AND cy_per.sequence = outer_view.sequence (+)
1360 ORDER BY cy_per.start_date ';
1361
1362 END IF;
1363
1364 fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, coll_eff_trend_sql, coll_eff_trend_output);
1365
1366 END get_coll_eff_trend;
1367
1368 END fii_ar_coll_eff_ind_pkg;
1369