1 PACKAGE BODY FII_AR_BILL_ACT_PKG AS
2 /* $Header: FIIARDBIBAB.pls 120.19 2007/05/15 20:46:27 vkazhipu ship $ */
3
4 -- This package will provide sql statements to retrieve data for Billing Activity
5
6
7 -- --------------------------------------------------------------------------
8 -- Name : get_billing_activity
9 -- Type : Procedure
10 -- Description : This procedure passes SQL to PMV for Billing Activity Report
11 -----------------------------------------------------------------------------
12
13
14 PROCEDURE get_billing_activity (
15 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, bill_act_sum_sql out NOCOPY VARCHAR2,
16 bill_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
17
18 l_sql_stmt VARCHAR2(30000);
19 l_sql_stmt1 VARCHAR2(20000);
20 l_view_by_flag NUMBER(1);
21 l_view_by VARCHAR2(120);
22 l_customer_where VARCHAR2(60);
23 l_cust_acct_where VARCHAR2(60);
24 l_org_where VARCHAR2(30);
25 l_industry_where VARCHAR2(240);
26 l_where_clause VARCHAR2(1000);
27 l_inner_from_clause VARCHAR2(60);
28 l_inner_where_clause VARCHAR2(60);
29 l_child_party_where VARCHAR2(60);
30
31 l_bill_act_amt_drill VARCHAR2(120);
32 l_cust_acct_or_leaf_amt_drill VARCHAR2(240);
33 l_bill_act_count_drill VARCHAR2(120);
34 l_acct_or_leaf_count_drill VARCHAR2(120);
35 l_open_rec_drill VARCHAR2(120);
36 l_cust_acct_or_leaf_rec_drill VARCHAR2(240);
37 l_customer_drill VARCHAR2(120);
38
39 l_self_mesg VARCHAR2(20);
40 l_dso_period NUMBER(3);
41 l_cust_suffix VARCHAR2(5);
42 l_curr_suffix VARCHAR2(4);
43 l_viewby_id VARCHAR2(30);
44
45 l_inv_flag VARCHAR2(1);
46 l_dm_flag VARCHAR2(1);
47 l_cb_flag VARCHAR2(1);
48 l_br_flag VARCHAR2(1);
49 l_dep_flag VARCHAR2(1);
50 l_cm_flag VARCHAR2(1);
51 l_undep_flag VARCHAR2(1);
52 l_unrec_flag VARCHAR2(1);
53 l_oacb_flag VARCHAR2(1);
54 l_ocb_flag VARCHAR2(1);
55
56 l_class_inclusion VARCHAR2(1000);
57 l_unapp_amount VARCHAR2(30);
58
59 l_order_by varchar2(500);
60 l_order_column varchar2(100);
61 l_gt_hint varchar2(500);
62 BEGIN
63
64
65
66 /* Reset Global Variables */
67 fii_ar_util_pkg.reset_globals;
68
69
70
71
72 /* Get the parameters that the user has selected */
73 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
74
75
76 /* Populate the dimension combination(s) that the user has access to */
77 fii_ar_util_pkg.populate_summary_gt_tables;
78
79
80
81 -- Get the view by
82 l_view_by := fii_ar_util_pkg.g_view_by;
83
84 /* Check whether join on party is is reqd. or not */
85 IF (fii_ar_util_pkg.g_party_id <> '-111' OR l_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
86 l_child_party_where := ' AND f.party_id = gt.party_id ';
87 END IF;
88
89 -- Check whether we need a filter for parent_party_id. This is applicable only if the Ct. dimension is hierarchial and view by
90 -- is customer.
91 IF fii_ar_util_pkg.g_is_hierarchical_flag = 'Y' and l_view_by = 'CUSTOMER+FII_CUSTOMERS' THEN
92 l_customer_where := ' and f.parent_party_id = gt.parent_party_id';
93 l_view_by_flag :=2; -- for Customer
94 END IF;
95
96 l_gt_hint := ' leading(t) cardinality(t 1) ';
97
98 -- Checks whether filter for Ct. acct. is reqd or not
99 IF l_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
100 l_cust_acct_where := ' and f.cust_account_id = gt.cust_account_id';
101 l_view_by_flag:=3;
102 --Added by vkazhipu for performance reasons.
103 l_gt_hint := ' leading(t.gt) cardinality(t.gt 1) ';
104 END IF;
105
106 /* Org filter will be there in all cases */
107 l_org_where := ' and f.org_id=gt.org_id';
108
109 /* Check whether industry filter is reqd or not */
110 IF l_view_by = 'CUSTOMER+FII_CUSTOMERS' THEN
111 l_industry_where := NULL;
112 ELSIF fii_ar_util_pkg.g_industry_id <> '-111' OR l_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
113 l_industry_where := ' and f.class_code=gt.class_code and f.class_category=gt.class_category';
114 END IF;
115
116 -- The below mentioned variable will make the code easy to understand.
117 l_where_clause := l_child_party_where||l_customer_where || l_cust_acct_where || l_org_where||l_industry_where;
118
119
120 -- Drills
121 -- Drill on amount to Billing Activity with view by as ct acct
122 -- Drill on Open Receivables. Drill to Open Receivable Summary with view by as ct. acct.
123 -- Drill on View By. This is true only in case the view by is Ct. and the implementation is hierarchial
124 IF (l_view_by_flag = 2 ) /* View by is ct. and implementation is heirarchial */ THEN
125 --vkazhipu changed
126 l_bill_act_amt_drill := 'pFunctionName=FII_AR_BILLING_ACTIVITY&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
127 l_open_rec_drill := 'pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
128 -- IF (l_view_by = 'CUSTOMER+FII_CUSTOMERS' and fii_ar_util_pkg.g_is_hierarchical_flag='Y' ) THEN
129 -- l_view_by_flag:=2;
130 l_customer_drill:= 'pFunctionName=FII_AR_BILLING_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y';
131 -- END IF;
132 END IF;
133 IF l_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' OR
134 l_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
135 IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
136 l_bill_act_amt_drill := 'pFunctionName=FII_AR_BILLING_ACTIVITY&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
137 l_open_rec_drill := 'pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
138 ELSE
139 --l_bill_act_amt_drill := 'pFunctionName=FII_AR_BILLING_ACTIVITY&VIEW_BY=CUSTOMER+FII_CUSTOMERS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
140 --l_open_rec_drill := 'pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY=CUSTOMER+FII_CUSTOMERS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
141 l_bill_act_amt_drill := 'pFunctionName=FII_AR_BILLING_ACTIVITY&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
142 l_open_rec_drill := 'pFunctionName=FII_AR_OPEN_REC_SUMMARY&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
143
144 END IF;
145 l_view_by_flag := 1; -- for OU, Industry
146 END IF;
147
148 -- Drill on amount and view by is ct acct or ct is leaf. Drilll to Billing Activity Detail
149 -- added the if for Bug#5140376
150 IF l_view_by_flag = 3 THEN
151 l_cust_acct_or_leaf_amt_drill := 'pFunctionName=FII_AR_BILLING_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_AR_CUST_ACCOUNT=VIEWBYID';
152 ELSE
153 l_cust_acct_or_leaf_amt_drill := 'pFunctionName=FII_AR_BILLING_ACT_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
154 END IF;
155
156
157 -- Drill on Count. Drill to Billing Activity Trend in all cases except when the view by is Ct. acct.
158 -- As per mail from Renu , the drill for count should be enabled even in case of view by as ct acct.
159 /* IF l_view_by_flag=3 THEN
160 l_bill_act_count_drill := NULL;
161 ELSE */
162 l_bill_act_count_drill := 'pFunctionName=FII_AR_BILLING_ACT_TREND&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
163 -- END IF;
164
165 --Bug#5114003: Pass party_id to trend report for view by ct. acct.
166 IF l_view_by_flag=3 THEN
167 l_bill_act_count_drill:=l_bill_act_count_drill||'&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=';
168 END IF;
169
170 -- Drill on open rec and view by is ct. acct or ct is leaf. Drill to open rec detail.
171 -- added the if for bug Bug#5140376
172 IF l_view_by_flag = 3 THEN
173 l_cust_acct_or_leaf_rec_drill := 'pFunctionName=FII_AR_OPEN_REC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_AR_CUST_ACCOUNT=VIEWBYID';
174 ELSE
175 l_cust_acct_or_leaf_rec_drill := 'pFunctionName=FII_AR_OPEN_REC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
176 END IF;
177
178 l_dso_period := fiI_ar_util_pkg.g_dso_period;
179
180
181 l_cust_suffix := fii_ar_util_pkg.g_cust_suffix;
182 l_curr_suffix := fii_ar_util_pkg.g_curr_suffix;
183
184
185 /* Get the transaction classes based on DSO setup */
186 fii_ar_util_pkg.get_dso_table_values;
187
188 l_class_inclusion := 0;
189
190
191 For i in fii_ar_util_pkg.g_dso_table.FIRST..fii_ar_util_pkg.g_dso_table.LAST LOOP
192
193 IF fii_ar_util_pkg.g_dso_table(i).dso_value = 'Y' THEN
194 IF fii_ar_util_pkg.g_dso_table(i).dso_type = 'INV' THEN
195 l_class_inclusion := l_class_inclusion ||'+ f.INV_AMOUNT';
196 ELSIF fii_ar_util_pkg.g_dso_table(i).dso_type = 'DM' THEN
197 l_class_inclusion := l_class_inclusion ||'+ f.DM_AMOUNT';
198 ELSIF fii_ar_util_pkg.g_dso_table(i).dso_type = 'CB' THEN
199 l_class_inclusion := l_class_inclusion ||'+ f.CB_AMOUNT';
200 ELSIF fii_ar_util_pkg.g_dso_table(i).dso_type = 'BR' THEN
201 l_class_inclusion := l_class_inclusion ||'+ f.BR_AMOUNT';
202 ELSIF fii_ar_util_pkg.g_dso_table(i).dso_type = 'DEP' THEN
203 l_class_inclusion := l_class_inclusion ||'+ f. DEP_AMOUNT';
204 ELSIF fii_ar_util_pkg.g_dso_table(i).dso_type = 'CM' THEN
205 l_class_inclusion := l_class_inclusion ||'+ f.ON_ACCOUNT_CREDIT_AMOUNT';
206 ELSIF fii_ar_util_pkg.g_dso_table(i).dso_type = 'UNDEP' THEN
207 l_class_inclusion := l_class_inclusion ||'- f.UNAPP_DEP_AMOUNT';
208 ELSIF fii_ar_util_pkg.g_dso_table(i).dso_type = 'UNREC' THEN
209 -- l_class_inclusion := l_class_inclusion ||'- f.UNAPP_AMOUNT';
210 l_unapp_amount := '- f.UNAPP_AMOUNT';
211 ELSIF fii_ar_util_pkg.g_dso_table(i).dso_type = 'OACB' THEN
212 l_class_inclusion := l_class_inclusion ||'- f.ON_ACCOUNT_CASH_AMOUNT';
213 ELSIF fii_ar_util_pkg.g_dso_table(i).dso_type = 'OCB' THEN
214 l_class_inclusion := l_class_inclusion ||'- f.CLAIM_AMOUNT ';
215 ELSIF fii_ar_util_pkg.g_dso_table(i).dso_type = 'PREPAY' THEN
216 l_class_inclusion := l_class_inclusion ||'- f.PREPAYMENT_AMOUNT ';
217 END IF;
218
219 END IF;
220 END LOOP;
221
222
223
224 -- Construct the order by
225 IF(instr(fii_ar_util_pkg.g_order_by,',') <> 0) THEN
226
227 /*This means no particular sort column is selected in the report
228 So sort on the default column in descending order
229 NVL is added to make sure the NULL values appear last*/
230
231 l_order_by := 'ORDER BY NVL(FII_AR_BILL_ACT_AMT, -999999999) DESC';
232
233 ELSIF(instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0)THEN
234
235 /*This means a particular sort column is clicked to have descending order
236 in which case we would want all the NULL values to appear last in the
237 report so add an NVL to that column*/
238
239 l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
240 l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
241
242 ELSE
243
244 /*This is the case when user has asked for an ascending order sort.
245 Use PMV's order by clause*/
246
247 l_order_by := '&ORDER_BY_CLAUSE';
248
249 END IF;
250
251 IF l_view_by_flag=3 THEN /* Viewby is Ct. acct */
252 l_inner_from_clause := fii_ar_util_pkg.get_from_statement;
253 l_inner_where_clause := ' and '||fii_ar_util_pkg.get_where_statement;
254 ELSE
255 l_inner_from_clause := 'fii_ar_summary_gt ';
256 l_inner_where_clause := NULL;
257 END IF;
258
259 -- Now the variable initialization is done, Start to build the PMV Query
260
261
262 l_sql_stmt := 'SELECT VIEWBY,
263 VIEW_BY_ID VIEWBYID,
264 SUM(FII_AR_BILL_ACT_AMT_PRIOR) FII_AR_BILL_ACT_AMT_PRIOR,
265 NULLIF(SUM(FII_AR_BILL_ACT_AMT),0) FII_AR_BILL_ACT_AMT,
266 (( SUM(FII_AR_BILL_ACT_AMT)-SUM(FII_AR_BILL_ACT_AMT_PRIOR) ) /
267 NULLIF(SUM(FII_AR_BILL_ACT_AMT_PRIOR),0 )) * 100 FII_AR_BILL_ACT_AMT_CHG,
268 SUM(FII_AR_BILL_ACT_COUNT) FII_AR_BILL_ACT_COUNT,
269 (( SUM(FII_AR_BILL_ACT_COUNT)-SUM(FII_AR_BILL_ACT_COUNT_PRIOR) ) /
270 NULLIF(SUM(FII_AR_BILL_ACT_COUNT_PRIOR),0) ) * 100 FII_AR_BILL_ACT_COUNT_CHG,
271 NULLIF(SUM(FII_AR_OPEN_REC_AMT),0) FII_AR_BILL_OPEN_REC_AMT,
272 (SUM(net_receivable_amount) / NULLIF(SUM(billed_amount),0)) *'||l_dso_period||' FII_AR_BILL_ACT_DSO,
273 NULLIF(SUM(SUM(FII_AR_BILL_ACT_AMT)) over(),0) FII_AR_GT_BILL_ACT_AMT ,
274 ((SUM(SUM(FII_AR_BILL_ACT_AMT)) over() - SUM(SUM(FII_AR_BILL_ACT_AMT_PRIOR)) over() )/
275 NULLIF(SUM(SUM(FII_AR_BILL_ACT_AMT_PRIOR)) over(),0)) *100
276 FII_AR_GT_BILL_ACT_AMT_CHG,
277 NULLIF(SUM(SUM(FII_AR_BILL_ACT_COUNT)) over(),0) FII_AR_GT_BILL_ACT_COUNT,
278 ((SUM(SUM(FII_AR_BILL_ACT_COUNT)) over() - SUM(SUM(FII_AR_BILL_ACT_COUNT_PRIOR)) over() )/
279 NULLIF(SUM(SUM(FII_AR_BILL_ACT_COUNT_PRIOR)) over(),0)) *100
280 FII_AR_GT_BILL_ACT_COUNT_CHG,
281 NULLIF(SUM(SUM(FII_AR_OPEN_REC_AMT)) over(),0) FII_AR_GT_OPEN_REC_AMT,
282 (SUM(SUM(net_receivable_amount)) over()/NULLIF(SUM(SUM(billed_amount)) over(),0) )*'||l_dso_period||' FII_AR_GT_BILL_ACT_DSO,
283 (SUM(prior_net_receivable_amount) / NULLIF(SUM(prior_billed_amount),0)) *'||l_dso_period||' FII_AR_PRIOR_DSO_KPI,
284 (SUM(SUM(prior_net_receivable_amount)) OVER()/NULLIF(SUM(SUM(prior_billed_amount)) OVER(),0) )*'||l_dso_period||' FII_AR_GT_PRIOR_DSO_KPI,
285 SUM (SUM(FII_AR_BILL_ACT_AMT_PRIOR)) OVER () FII_AR_GT_BILL_ACT_PRIOR_AMT, ';
286
287 IF l_view_by_flag = 1 THEN /* It means that the view by is either ou or Industry */
288
289 l_sql_stmt := l_sql_stmt ||'DECODE(NVL(SUM(FII_AR_BILL_ACT_AMT),0),0,'''','''|| l_bill_act_amt_drill ||''') FII_AR_BILL_ACT_AMT_DRILL ,
290 DECODE(NVL(SUM(FII_AR_BILL_ACT_COUNT),0),0,'''','''||l_bill_act_count_drill ||''') FII_AR_BILL_ACT_COUNT_DRILL ,
291 DECODE(NVL(SUM(FII_AR_OPEN_REC_AMT),0),0,'''','''||l_open_rec_drill || ''') FII_AR_BILL_OPEN_REC_AMT_DRILL ,
292 NULL FII_AR_VIEW_BY_DRILL';
293
294 ELSIF /*( l_view_by_flag = 3 )
295 or */ (l_view_by = 'CUSTOMER+FII_CUSTOMERS' and fii_ar_util_pkg.g_is_hierarchical_flag = 'N' ) THEN
296
297 l_sql_stmt := l_sql_stmt ||'DECODE(NVL(SUM(FII_AR_BILL_ACT_AMT),0),0,'''','''|| l_cust_acct_or_leaf_amt_drill || ''') FII_AR_BILL_ACT_AMT_DRILL ,
298 DECODE(NVL(SUM(FII_AR_BILL_ACT_COUNT),0),0,'''','''||l_bill_act_count_drill || ''') FII_AR_BILL_ACT_COUNT_DRILL ,
299 DECODE(NVL(SUM(FII_AR_OPEN_REC_AMT),0),0,'''','''||l_cust_acct_or_leaf_rec_drill || ''') FII_AR_BILL_OPEN_REC_AMT_DRILL ,
300 NULL FII_AR_VIEW_BY_DRILL ';
301
302 ELSIF l_view_by_flag =2 or l_view_by_flag=3 THEN /* It means that the View by Is ct and implementation is hierarchial */
303
304 l_sql_stmt := l_sql_stmt || ' DECODE(NVL(SUM(FII_AR_BILL_ACT_AMT),0),0,'''',FII_AR_BILL_ACT_AMT_DRILL) FII_AR_BILL_ACT_AMT_DRILL,
305 DECODE(NVL(SUM(FII_AR_BILL_ACT_COUNT),0),0,'''',FII_AR_BILL_ACT_COUNT_DRILL) FII_AR_BILL_ACT_COUNT_DRILL,
306 DECODE(NVL(SUM(FII_AR_OPEN_REC_AMT),0),0,'''',FII_AR_BILL_OPEN_REC_AMT_DRILL) FII_AR_BILL_OPEN_REC_AMT_DRILL,
307 FII_AR_VIEW_BY_DRILL ';
308 END IF;
309
310 l_sql_stmt := l_sql_stmt || ' FROM ( select /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
311 CASE WHEN gt.report_date=:PREVIOUS_ASOF_DATE and BITAND(gt.record_type_id,:BITAND)= :BITAND THEN
312 f.billing_activity_amount
313 ELSE
314 NULL
315 END FII_AR_BILL_ACT_AMT_PRIOR,
316 CASE WHEN gt.report_date=:ASOF_DATE and BITAND(gt.record_type_id,:BITAND)= :BITAND THEN
317 f.billing_activity_amount
318 ELSE
319 NULL
320 END FII_AR_BILL_ACT_AMT,
321 CASE WHEN gt.report_date=:ASOF_DATE and BITAND(gt.record_type_id,:BITAND)= :BITAND THEN
322 f.billing_activity_count
323 ELSE
324 NULL
325 END FII_AR_BILL_ACT_COUNT,
326 CASE WHEN gt.report_date=:PREVIOUS_ASOF_DATE and BITAND(gt.record_type_id,:BITAND)= :BITAND THEN
327 f.billing_activity_count
328 ELSE
329 NULL
330 END FII_AR_BILL_ACT_COUNT_PRIOR,
331 CASE when gt.report_date=:ASOF_DATE and BITAND(gt.record_type_Id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
332 f.total_open_amount
333 ELSE
334 NULL
335 END FII_AR_OPEN_REC_AMT,
336 CASE when gt.report_date=:ASOF_DATE and BITAND(gt.record_type_id,:BITAND_INC_TODATE)= :BITAND_INC_TODATE THEN '||
337 l_class_inclusion ||'
338 ELSE
339 NULL
340 END NET_RECEIVABLE_AMOUNT,
341 CASE when gt.report_date=:ASOF_DATE and BITAND(gt.record_type_id,:DSO_BITAND)= :DSO_BITAND THEN
342 f.billed_amount
343 ELSE
344 NULL
345 END BILLED_AMOUNT ,
346 CASE when gt.report_date=:PREVIOUS_ASOF_DATE and BITAND(gt.record_type_id,:BITAND_INC_TODATE)= :BITAND_INC_TODATE THEN '||
347 l_class_inclusion ||'
348 ELSE
349 NULL
350 END PRIOR_NET_RECEIVABLE_AMOUNT,
351 CASE when gt.report_date=:PREVIOUS_ASOF_DATE and BITAND(gt.record_type_id,:DSO_BITAND)= :DSO_BITAND THEN
352 f.billed_amount
353 ELSE
354 NULL
355 END PRIOR_BILLED_AMOUNT,
356 gt.viewby viewby,
357 gt.viewby_code VIEW_BY_ID';
358
359 IF l_view_by_flag = 3 THEN
360
361 l_cust_acct_or_leaf_amt_drill := l_cust_acct_or_leaf_amt_drill||'&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=';
362 l_cust_acct_or_leaf_rec_drill := l_cust_acct_or_leaf_rec_drill||'&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=';
363 l_sql_stmt:=l_sql_stmt || ','''||l_cust_acct_or_leaf_amt_drill||'''||gt.party_id||'''' FII_AR_BILL_ACT_AMT_DRILL,';
364 l_sql_stmt:=l_sql_stmt || ''''||l_bill_act_count_drill||'''||gt.party_id||'''' FII_AR_BILL_ACT_COUNT_DRILL,';
365 l_sql_stmt:=l_sql_stmt || ''''||l_cust_acct_or_leaf_rec_drill||'''||gt.party_id||'''' FII_AR_BILL_OPEN_REC_AMT_DRILL,';
366 l_sql_stmt:=l_sql_stmt ||' NULL FII_AR_VIEW_BY_DRILL ';
367 END IF;
368
369
370
371 IF l_view_by_flag = 2 THEN /* The view by is ct. and the implementation is heirarchial */
372 l_sql_stmt1 := ', gt.is_leaf_flag IS_LEAF_FLAG ,';
373 l_sql_Stmt1 :=l_sql_stmt1 || ' CASE WHEN gt.is_leaf_flag = ''Y'' OR gt.is_self_flag = ''Y'' THEN '''||
374 l_cust_acct_or_leaf_amt_drill||'''
375 ELSE '''||
376 l_bill_act_amt_drill||'''
377 END FII_AR_BILL_ACT_AMT_DRILL ,'''||l_bill_act_count_drill ||''' FII_AR_BILL_ACT_COUNT_DRILL,
378 CASE WHEN gt.is_leaf_flag = ''Y'' OR gt.is_self_flag = ''Y'' THEN '''||
379 l_cust_acct_or_leaf_rec_drill||'''
380 ELSE '''||
381 l_open_rec_drill||'''
382 END FII_AR_BILL_OPEN_REC_AMT_DRILL ,
383 DECODE(gt.is_self_flag,''Y'','''',DECODE(gt.is_leaf_flag,''Y'','''','''||l_customer_drill||''')) FII_AR_VIEW_BY_DRILL ';
384 l_sql_stmt := l_sql_stmt||l_sql_stmt1;
385
386 END IF;
387
388
389
390
391 l_sql_stmt:=l_sql_stmt||' from fii_ar_net_rec'||l_cust_suffix||'_mv'||l_curr_suffix||' f,
392 (select /*+ no_merge '||l_gt_hint|| ' */ * from fii_time_structures cal, '||l_inner_from_clause||' t
393 where cal.report_date in(:ASOF_DATE ,:PREVIOUS_ASOF_DATE)
394 and (BITAND(cal.record_type_id,:BITAND)= :BITAND
395 OR BITAND(cal.record_type_id,:BITAND_INC_TODATE)= :BITAND_INC_TODATE
396 OR BITAND(cal.record_type_id,:DSO_BITAND)=:DSO_BITAND ) '||l_inner_where_clause ||' ) gt
397 where f.time_id = gt.time_id
398 and gt.period_type_id=f.period_type_id
399 '|| l_where_clause||' and '||fii_ar_util_pkg.get_mv_where_statement;
400 IF l_unapp_amount IS NOT NULL THEN
401 l_sql_stmt:=l_sql_stmt||' UNION ALL
402 SELECT /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_MV_N1)*/ NULL FII_AR_BILL_ACT_AMT_PRIOR,
403 NULL FII_AR_BILL_ACT_AMT,
404 NULL FII_AR_BILL_ACT_COUNT,
405 NULL FII_AR_BILL_ACT_COUNT_PRIOR,
406 NULL FII_AR_OPEN_REC_AMT,
407 CASE when gt.report_date=:ASOF_DATE and BITAND(gt.record_type_id,:BITAND_INC_TODATE)= :BITAND_INC_TODATE THEN '||
408 l_unapp_amount || ' ELSE NULL END NET_RECEIVABLE_AMOUNT,
409 NULL BILLED_AMOUNT,
410 CASE when gt.report_date=:PREVIOUS_ASOF_DATE and BITAND(gt.record_type_id,:BITAND_INC_TODATE)= :BITAND_INC_TODATE THEN '||
411 l_unapp_amount || ' ELSE NULL END PRIOR_NET_RECEIVABLE_AMOUNT,
412 NULL PRIOR_BILLED_AMOUNT,
413 gt.viewby viewby,
414 gt.viewby_code VIEW_BY_ID';
415 IF l_view_by_flag = 3 THEN
416 -- commented out for bug5107816
417 -- This piece was redundant as it was already done for the 1st select and was not reqd for 2nd select
418 /* l_cust_acct_or_leaf_amt_drill := l_cust_acct_or_leaf_amt_drill||'&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=';
419 l_cust_acct_or_leaf_rec_drill := l_cust_acct_or_leaf_rec_drill||'&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS='; */
420 l_sql_stmt:=l_sql_stmt || ','''||l_cust_acct_or_leaf_amt_drill||'''||gt.party_id||'''' FII_AR_BILL_ACT_AMT_DRILL,';
421 l_sql_stmt:=l_sql_stmt || ''''||l_bill_act_count_drill||'''||gt.party_id||'''' FII_AR_BILL_ACT_COUNT_DRILL,';
422 l_sql_stmt:=l_sql_stmt || ''''||l_cust_acct_or_leaf_rec_drill||'''||gt.party_id||'''' FII_AR_BILL_OPEN_REC_AMT_DRILL,';
423 l_sql_stmt:=l_sql_stmt ||' NULL FII_AR_VIEW_BY_DRILL ';
424 END IF;
425
426 IF l_view_by_flag = 2 THEN /* The view by is ct. and the implementation is heirarchial */
427 l_sql_stmt:=l_sql_stmt||l_sql_stmt1;
428 END IF;
429
430 l_sql_stmt:=l_sql_stmt||' from fii_ar_rct_aging'||l_cust_suffix||'_mv'||l_curr_suffix||' f,
431 (select /*+ no_merge '||l_gt_hint|| ' */ * from fii_time_structures cal, '||l_inner_from_clause||' t
432 where cal.report_date in(:ASOF_DATE ,:PREVIOUS_ASOF_DATE)
433 and (BITAND(cal.record_type_id,:BITAND)= :BITAND
434 OR BITAND(cal.record_type_id,:BITAND_INC_TODATE)= :BITAND_INC_TODATE
435 OR BITAND(cal.record_type_id,:DSO_BITAND)=:DSO_BITAND ) '||l_inner_where_clause ||' ) gt
436 where f.time_id = gt.time_id
437 and gt.period_type_id=f.period_type_id
438 AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' '|| l_where_clause;
439
440
441 END IF;
442
443 l_sql_stmt:=l_sql_stmt||' ) GROUP BY viewby,view_by_id ';
444
445
446
447 IF l_view_by_flag = 2 or l_view_by_flag= 3 THEN
448 l_sql_stmt := l_sql_stmt || ',FII_AR_BILL_ACT_AMT_DRILL,FII_AR_BILL_ACT_COUNT_DRILL,FII_AR_BILL_OPEN_REC_AMT_DRILL, FII_AR_VIEW_BY_DRILL ';
449 END IF;
450
451 l_sql_stmt := l_sql_stmt || l_order_by;
452
453
454
455
456 /* Pass back the pmv sql along with bind variables to PMV */
457 fii_ar_util_pkg.bind_variable(l_sql_stmt, p_page_parameter_tbl, bill_act_sum_sql, bill_sum_output);
458
459 END get_billing_activity;
460
461
462
463 END ;
464
[Home] [Help]