[Home] [Help]
PACKAGE BODY: APPS.FII_AR_UTIL_PKG
Source
1 PACKAGE BODY fii_ar_util_pkg AS
2 /* $Header: FIIARUTILB.pls 120.50 2006/12/13 17:00:15 vkazhipu ship $ */
3
4 g_min_start_date date;
5 -- -------------------------------------------------
6 -- Re-set the globals variables to NULL
7 -- -------------------------------------------------
8 PROCEDURE reset_globals IS
9 BEGIN
10
11 g_as_of_date := NULL;
12 g_page_period_type := NULL;
13 g_currency := NULL;
14 g_view_by := NULL;
15 g_region_code := NULL;
16 g_session_id := NULL;
17 g_time_comp := NULL;
18 g_party_id := NULL;
19 g_cust_account_id := '-111';
20 g_org_id := '-111';
21 g_collector_id := '-111';
22 g_industry_id := '-111';
23 g_curr_suffix := NULL;
24 g_cust_suffix := NULL;
25 g_cust_view_by := NULL;
26 g_curr_per_start := NULL;
27 g_curr_per_end := NULL;
28 g_prior_per_start := NULL;
29 g_prior_per_end := NULL;
30 g_curr_month_start := NULL;
31 g_bitand := NULL;
32 g_dso_bitand := NULL;
33 g_bitand_inc_todate := NULL;
34 g_bitand_rolling_30_days := NULL;
35 g_self_msg := NULL;
36 g_previous_asof_date := NULL;
37 g_is_hierarchical_flag := NULL;
38 g_count_parent_party_id := NULL;
39 g_dso_period := NULL;
40 g_industry_class_type := NULL;
41 g_security_profile_id := NULL;
42 g_security_org_id := NULL;
43 g_operating_unit := NULL;
44 g_prim_global_currency_code := get_prim_global_currency_code;
45 g_sec_global_currency_code := get_sec_global_currency_code;
46 g_det_ou_lov := NULL;
47 g_business_group_id := NULL;
48 g_all_operating_unit := NULL;
49 g_order_by := NULL;
50 g_sd_prior := NULL;
51 g_sd_prior_prior := NULL;
52 g_sd_curr_sdate := NULL;
53 g_function_name := NULL;
54 g_col_curr_suffix := NULL;
55 g_cash_receipt_id := Null;
56 g_cust_trx_id := NULL;
57 g_tran_num := NULL;
58 g_tran_class := NULL;
59 g_cust_account := NULL;
60 g_account_num := NULL;
61 g_app_cust_trx_id := NULL;
62 g_bucket_num := NULL;
63 g_page_refresh_date := NULL;
64
65 END reset_globals;
66
67 PROCEDURE get_parameters (
68 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL) IS
69
70 l_retcode NUMBER;
71 is_hierarchical VARCHAR2(100);
72
73 BEGIN
74
75 -- -------------------------------------------------
76 -- Parse thru the parameter table and set globals
77 -- -------------------------------------------------
78 IF (p_page_parameter_tbl.count > 0) THEN
79 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
80
81 IF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
82 g_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD/MM/YYYY');
83 ELSIF p_page_parameter_tbl(i).parameter_name = 'BIS_PREVIOUS_ASOF_DATE' THEN
84 g_previous_asof_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD/MM/YYYY');
85 ELSIF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
86 g_page_period_type := p_page_parameter_tbl(i).parameter_value;
87 ELSIF p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES' THEN
88 g_currency := substr(p_page_parameter_tbl(i).parameter_id,2,11);
89 ELSIF p_page_parameter_tbl(i).parameter_name = 'VIEW_BY' THEN
90 g_view_by := p_page_parameter_tbl(i).parameter_value;
91 ELSIF p_page_parameter_tbl(i).parameter_name = 'BIS_REGION_CODE' THEN
92 g_region_code := p_page_parameter_tbl(i).parameter_value;
93 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME_COMPARISON_TYPE' THEN
94 g_time_comp := p_page_parameter_tbl(i).parameter_value;
95 ELSIF p_page_parameter_tbl(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS' THEN
96 g_party_id := nvl(replace(p_page_parameter_tbl(i).parameter_id, '''', null), '-111');
97 ELSIF p_page_parameter_tbl(i).parameter_name = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
98 g_cust_account_id := nvl(p_page_parameter_tbl(i).parameter_id,'-111');
99 ELSIF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
100 g_org_id := nvl(replace(p_page_parameter_tbl(i).parameter_id, '''', null), '-111');
101 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_COLLECTOR+FII_COLLECTOR' THEN
102 g_collector_id := NVL(p_page_parameter_tbl(i).parameter_id,'-111');
103 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
104 g_industry_id := NVL(p_page_parameter_tbl(i).parameter_id,'-111');
105 ELSIF p_page_parameter_tbl(i).parameter_name = 'ORDERBY' THEN
106 g_order_by := p_page_parameter_tbl(i).parameter_value;
107 ELSIF p_page_parameter_tbl(i).parameter_name = 'BIS_FXN_NAME' THEN
108 g_function_name := p_page_parameter_tbl(i).parameter_value;
109 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_AR_CASH_RECEIPT_ID' THEN
110 g_cash_receipt_id := p_page_parameter_tbl(i).parameter_value;
111 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_AR_CUST_TRX_ID' THEN
112 g_cust_trx_id := p_page_parameter_tbl(i).parameter_value;
113 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_AR_TRAN_NUM' THEN
114 g_tran_num := p_page_parameter_tbl(i).parameter_value;
115 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_AR_TRAN_CLASS' THEN
116 g_tran_class := p_page_parameter_tbl(i).parameter_value;
117 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_AR_CUST_ACCOUNT' THEN
118 g_cust_account := p_page_parameter_tbl(i).parameter_value;
119 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_AR_ACCOUNT_NUM' THEN
120 g_account_num := p_page_parameter_tbl(i).parameter_value;
121 ELSIF p_page_parameter_tbl(i).parameter_name = 'BIS_ICX_SESSION_ID' THEN
122 g_session_id := NVL(p_page_parameter_tbl(i).parameter_value,0);
123 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_AR_APP_CUST_TRX_ID' THEN
124 g_app_cust_trx_id := p_page_parameter_tbl(i).parameter_value;
125 ELSIF p_page_parameter_tbl(i).parameter_name = 'FII_AR_BUCKET_NUM' THEN
126 g_bucket_num := p_page_parameter_tbl(i).parameter_value;
127
128 END IF;
129 END LOOP;
130 END IF;
131
132 -- Done for bug# 5089114
133 IF g_function_name = 'FII_AR_DSO_TREND_GRAPH' THEN
134 g_page_period_type := 'FII_TIME_ENT_PERIOD';
135 END IF;
136
137 IF g_collector_id = 'All' THEN
138 g_collector_id := '-111';
139 END IF;
140
141 IF g_org_id = 'All' THEN
142 g_org_id := '-111';
143 ELSIF g_org_id='null' THEN
144 g_org_id := '-999';
145 END IF;
146
147 IF g_party_id = 'All' OR g_party_id IS NULL THEN
148 g_party_id := '-111';
149 END IF;
150 IF g_industry_id = 'All' THEN
151 g_industry_id := '-111';
152 END IF;
153
154
155 /* This code is written to default the view by to OU, in the case when any other viewby is chosen other than
156 below mentioned. We need to populate GT table with OU data in the case when viewby chosen is Month/Time etc */
157
158 IF g_view_by is null or
159 (g_view_by <> 'CUSTOMER+FII_CUSTOMERS'
160 AND g_view_by <> 'CUSTOMER+FII_CUSTOMER_ACCOUNTS'
161 AND g_view_by <> 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS'
162 AND g_view_by <> 'FII_COLLECTOR+FII_COLLECTOR'
163 AND g_view_by <> 'ORGANIZATION+FII_OPERATING_UNITS') THEN
164
165 g_view_by := 'ORGANIZATION+FII_OPERATING_UNITS';
166
167 END IF;
168
169 /* If user views in primary global, use 1st view which SELECTs the primary amount. For secondary global
170 currency, use 2nd view which SELECTs secondary amount for Functional currency, use 3rd view which
171 SELECTs functional amount. */
172
173 IF g_currency = 'FII_GLOBAL1' THEN
174 g_curr_suffix := '_p_v';
175 g_col_curr_suffix := '_prim';
176 ELSIF g_currency = 'FII_GLOBAL2' THEN
177 g_curr_suffix := '_s_v';
178 g_col_curr_suffix := '_sec';
179 ELSE
180 g_curr_suffix := '_f_v';
181 g_col_curr_suffix := '_func';
182 END IF;
183
184
185 /* FND PROFILE for Customer Hierarchy: will be created through Bug 4637815 */
186 is_hierarchical := nvl(fnd_profile.value('BIS_CUST_HIER_TYPE'), 'N');
187
188 IF is_hierarchical = 'N' THEN
189 g_is_hierarchical_flag := 'N';
190 ELSE
191 g_is_hierarchical_flag :='Y';
192 END IF;
193
194 g_industry_class_type := nvl(fnd_profile.value('BIS_CUST_CLASS_TYPE'), -1);
195
196 /* The following procedure parses the g_party_id i.e. party_id parameter value passed by
197 BIS from PMV parameter region. After that in the case of customers defined is hierarchical
198 and if both parent and children are chosen together from customer parameter then only the
199 parent should be stored in g_party_id variable. */
200
201 fii_ar_util_pkg.populate_party_id;
202
203 SELECT nvl(min(start_date), trunc(sysdate)) INTO g_min_start_date
204 FROM fii_time_ent_period;
205
206 SELECT nvl(min(start_date), g_min_start_date) INTO g_curr_month_start
207 FROM fii_time_ent_period
208 WHERE g_as_of_date between start_date and END_date;
209
210
211 IF g_previous_asof_date IS NULL THEN
212 g_previous_asof_date := g_min_start_date;
213 END IF;
214
215 /* Bitand for inception to-date */
216 g_bitand_inc_todate := 512;
217
218 /* Bitand for rolling 30 days */
219 g_bitand_rolling_30_days := 2048;
220
221 CASE g_page_period_type
222
223 WHEN 'FII_TIME_WEEK' THEN
224 g_bitand := 32;
225
226 SELECT NVL(fii_time_api.pwk_end(g_as_of_date-84),g_min_start_date) INTO g_sd_prior FROM DUAL;
227 SELECT NVL(fii_time_api.ent_sd_lysper_end(fii_time_api.ent_sd_lysper_end(g_as_of_date)),g_min_start_date) INTO g_sd_prior_prior FROM DUAL;
228 SELECT NVL(fii_time_api.cwk_start(g_sd_prior), g_min_start_date) INTO g_sd_curr_sdate FROM DUAL;
229
230 SELECT NVL(fii_time_api.cwk_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;
231 SELECT NVL(fii_time_api.cwk_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;
232 SELECT NVL(fii_time_api.pwk_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;
233 SELECT NVL(fii_time_api.pwk_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;
234
235 WHEN 'FII_TIME_ENT_PERIOD' THEN
236 g_bitand := 64;
237
238 IF g_time_comp = 'SEQUENTIAL' THEN
239 SELECT NVL(fii_time_api.ent_sd_pper_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
240 ELSE
241 SELECT NVL(fii_time_api.ent_sd_lysper_END(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
242 END IF;
243
244 SELECT NVL(fii_time_api.ent_sd_lysper_end(g_as_of_date), g_min_start_date) INTO g_sd_prior FROM DUAL;
245 SELECT NVL(fii_time_api.ent_sd_lysper_end(g_sd_prior), g_min_start_date) INTO g_sd_prior_prior FROM DUAL;
246 SELECT fii_time_api.ent_cper_end(g_sd_prior)+1 INTO g_sd_curr_sdate FROM DUAL;
247
248
249 SELECT NVL(fii_time_api.ent_cper_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;
250 SELECT NVL(fii_time_api.ent_cper_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;
251 SELECT NVL(fii_time_api.ent_cper_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;
252 SELECT NVL(fii_time_api.ent_cper_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;
253
254
255 WHEN 'FII_TIME_ENT_QTR' THEN
256 g_bitand := 128;
257
258 IF g_time_comp = 'SEQUENTIAL' THEN
259 SELECT NVL(fii_time_api.ent_sd_pqtr_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
260 ELSE
261 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
262 END IF;
263
264 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date) INTO g_sd_prior FROM dual;
265 SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_sd_prior),g_min_start_date) INTO g_sd_prior_prior FROM dual;
266 SELECT fii_time_api.ent_cqtr_end(g_sd_prior)+1 INTO g_sd_curr_sdate FROM dual;
267
268 SELECT NVL(fii_time_api.ent_cqtr_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;
269 SELECT NVL(fii_time_api.ent_cqtr_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;
270 SELECT NVL(fii_time_api.ent_cqtr_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;
271 SELECT NVL(fii_time_api.ent_cqtr_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;
272
273 WHEN 'FII_TIME_ENT_YEAR' THEN
274 g_bitand := 256;
275
276 SELECT NVL(fii_time_api.ent_pyr_end(fii_time_api.ent_pyr_end(fii_time_api.ent_pyr_end(fii_time_api.ent_pyr_end(g_as_of_date)))), g_min_start_date) INTO g_sd_prior FROM DUAL;
277 SELECT NVL(fii_time_api.ent_pyr_end(g_sd_prior), g_min_start_date) INTO g_sd_prior_prior FROM DUAL;
278
279 SELECT NVL(fii_time_api.ent_cyr_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;
280 SELECT NVL(fii_time_api.ent_cyr_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;
281 SELECT NVL(fii_time_api.ent_cyr_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;
282 SELECT NVL(fii_time_api.ent_cyr_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;
283
284 ELSE g_bitand := 64;
285 END CASE;
286
287 IF g_sd_prior IS NULL THEN
288 SELECT NVL(fii_time_api.ent_sd_lysper_end(g_as_of_date), g_min_start_date) INTO g_sd_prior FROM DUAL;
289 SELECT NVL(fii_time_api.ent_sd_lysper_end(g_sd_prior), g_min_start_date) INTO g_sd_prior_prior FROM DUAL;
290 SELECT fii_time_api.ent_cper_end(g_sd_prior)+1 INTO g_sd_curr_sdate FROM DUAL;
291 END IF;
292
293 g_self_msg := FND_MESSAGE.get_string('FII', 'FII_AR_SELF');
294
295 CASE g_view_by
296 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN
297 IF g_is_hierarchical_flag = 'Y' THEN
298 g_cust_suffix := '_agrt';
299 ELSE
300 g_cust_suffix := '_base';
301 END IF;
302 g_cust_view_by := 'VIEW_BY_CUST';
303 WHEN 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
304 g_cust_suffix := '_base';
305 g_cust_view_by := 'VIEW_BY_ACCT';
306 ELSE
307 g_cust_suffix := '_base';
308 g_cust_view_by := NULL;
309 END CASE;
310
311 SELECT fii_ar_util_pkg.get_dso_period_profile
312 INTO g_dso_period
313 FROM dual;
314
315 CASE
316 WHEN g_dso_period = 365 THEN
317 g_dso_bitand := 8192;
318 WHEN g_dso_period = 180 THEN
319 g_dso_bitand := 65536;
320 WHEN g_dso_period = 90 THEN
321 g_dso_bitand := 4096;
322 WHEN g_dso_period = 60 THEN
323 g_dso_bitand := 32768;
324 WHEN g_dso_period = 45 THEN
325 g_dso_bitand := 16384;
326 WHEN g_dso_period = 30 THEN
327 g_dso_bitand := 2048;
328 END CASE;
329
330 END get_parameters;
331
332
333
334 /*
335 PROCEDURE get_viewby_id(p_viewby_id OUT NOCOPY VARCHAR2) IS
336
337 BEGIN
338
339 CASE g_view_by
340 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN
341 p_viewby_id := 'f.party_id';
342 WHEN 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
343 p_viewby_id := 'f.cust_account_id';
344 WHEN 'ORGANIZATION+FII_OPERATING_UNITS' THEN
345 p_viewby_id := 'f.org_id';
346 WHEN 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
347 p_viewby_id := 'f.class_code';
348 WHEN 'FII_COLLECTOR+FII_COLLECTOR' THEN
349 p_viewby_id := 'f.collector_id';
350 END CASE;
351 END get_viewby_id;
352 */
353
354 FUNCTION get_trend_viewby return VARCHAR2 IS
355 BEGIN
356
357 IF g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
358 return(fnd_message.get_string('FII', 'FII_AR_DBI_YEAR'));
359 ELSIF g_page_period_type = 'FII_TIME_ENT_QTR' THEN
360 return(fnd_message.get_string('FII', 'FII_AR_DBI_QUARTER'));
361 ELSIF g_page_period_type = 'FII_TIME_ENT_PERIOD' THEN
362 return(fnd_message.get_string('FII', 'FII_AR_DBI_MONTH'));
363 ELSIF g_page_period_type = 'FII_TIME_WEEK' THEN
364 return(fnd_message.get_string('FII', 'FII_AR_DBI_WEEK'));
365 END IF;
366
367 END get_trend_viewby;
368
369
370 PROCEDURE Bind_Variable (p_sqlstmt IN Varchar2,
371 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
372 p_sql_output OUT NOCOPY Varchar2,
373 p_bind_output_table OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
374
375 l_bind_rec BIS_QUERY_ATTRIBUTES;
376
377 BEGIN
378
379 p_bind_output_table := BIS_QUERY_ATTRIBUTES_TBL();
380 l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
381 p_sql_output := p_sqlstmt;
382
383 p_bind_output_table.EXTEND;
384 l_bind_rec.attribute_name := ':ASOF_DATE';
385 l_bind_rec.attribute_value := to_char(g_as_of_date, 'DD/MM/YYYY');
386 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
387 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
388 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
389
390 p_bind_output_table.EXTEND;
391 l_bind_rec.attribute_name := ':PREVIOUS_ASOF_DATE';
392 l_bind_rec.attribute_value := to_char(g_previous_asof_date, 'DD/MM/YYYY');
393 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
394 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
395 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
396
397 p_bind_output_table.EXTEND;
398 l_bind_rec.attribute_name := ':CURRENCY';
399 l_bind_rec.attribute_value := to_char(g_currency);
400 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
401 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
402 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
403
404 /*
405 p_bind_output_table.EXTEND;
406 l_bind_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
407 l_bind_rec.attribute_value := g_view_by;
408 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
409 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
410 */
411
412 p_bind_output_table.EXTEND;
413 l_bind_rec.attribute_name := ':PARTY_ID';
414 l_bind_rec.attribute_value := to_char(g_party_id);
415 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
416 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
417 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
418
419 p_bind_output_table.EXTEND;
420 l_bind_rec.attribute_name := ':CUST_ACCOUNT_ID';
421 l_bind_rec.attribute_value := to_char(g_cust_account_id);
422 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
423 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
424 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
425
426 p_bind_output_table.EXTEND;
427 l_bind_rec.attribute_name := ':ORG_ID';
428 l_bind_rec.attribute_value := to_char(g_org_id);
429 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
430 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
431 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
432
433 p_bind_output_table.EXTEND;
434 l_bind_rec.attribute_name := ':COLLECTOR_ID';
435 l_bind_rec.attribute_value := to_char(g_collector_id);
436 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
437 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
438 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
439
440 p_bind_output_table.EXTEND;
441 l_bind_rec.attribute_name := ':INDUSTRY_ID';
442 l_bind_rec.attribute_value := to_char(g_industry_id);
443 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
444 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
445 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
446
447 p_bind_output_table.EXTEND;
448 l_bind_rec.attribute_name := ':CURR_PERIOD_START';
449 l_bind_rec.attribute_value := to_char(g_curr_per_start, 'DD/MM/YYYY');
450 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
451 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
452 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
453
454 p_bind_output_table.EXTEND;
455 l_bind_rec.attribute_name := ':CURR_PERIOD_END';
456 l_bind_rec.attribute_value := to_char(g_curr_per_end, 'DD/MM/YYYY');
457 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
458 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
459 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
460
461 p_bind_output_table.EXTEND;
462 l_bind_rec.attribute_name := ':PRIOR_PERIOD_START';
463 l_bind_rec.attribute_value := to_char(g_prior_per_start, 'DD/MM/YYYY');
464 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
465 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
466 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
467
468 p_bind_output_table.EXTEND;
469 l_bind_rec.attribute_name := ':PRIOR_PERIOD_END';
470 l_bind_rec.attribute_value := to_char(g_prior_per_end, 'DD/MM/YYYY');
471 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
472 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
473 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
474
475 p_bind_output_table.EXTEND;
476 l_bind_rec.attribute_name := ':CURR_MONTH_START';
477 l_bind_rec.attribute_value := to_char(g_curr_month_start, 'DD/MM/YYYY');
478 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
479 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
480 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
481
482 p_bind_output_table.EXTEND;
483 l_bind_rec.attribute_name := ':BITAND';
484 l_bind_rec.attribute_value := to_char(g_bitand);
485 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
486 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
487 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
488
489 p_bind_output_table.EXTEND;
490 l_bind_rec.attribute_name := ':DSO_BITAND';
491 l_bind_rec.attribute_value := to_char(g_dso_bitand);
492 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
493 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
494 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
495
496 p_bind_output_table.EXTEND;
497 l_bind_rec.attribute_name := ':BITAND_INC_TODATE';
498 l_bind_rec.attribute_value := to_char(g_bitand_inc_todate);
499 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
500 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
501 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
502
503 p_bind_output_table.EXTEND;
504 l_bind_rec.attribute_name := ':BITAND_ROLLING_30_DAYS';
505 l_bind_rec.attribute_value := to_char(g_bitand_rolling_30_days);
506 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
507 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
508 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
509
510 p_bind_output_table.EXTEND;
511 l_bind_rec.attribute_name := ':SELF_MSG';
512 l_bind_rec.attribute_value := to_char(g_self_msg);
513 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
514 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
515 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
516
517
518 p_bind_output_table.EXTEND;
519 l_bind_rec.attribute_name := ':CURR_SUFIX';
520 l_bind_rec.attribute_value := to_char(g_curr_suffix);
521 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
522 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
523 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
524
525 p_bind_output_table.EXTEND;
526 l_bind_rec.attribute_name := ':DSO_PERIOD';
527 l_bind_rec.attribute_value := to_char(g_dso_period);
528 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
529 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
530 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
531
532 p_bind_output_table.EXTEND;
533 l_bind_rec.attribute_name := ':SD_PRIOR';
534 l_bind_rec.attribute_value := to_char(g_sd_prior, 'DD/MM/YYYY');
535 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
536 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
537 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
538
539 p_bind_output_table.EXTEND;
540 l_bind_rec.attribute_name := ':SD_PRIOR_PRIOR';
541 l_bind_rec.attribute_value := to_char(g_sd_prior_prior, 'DD/MM/YYYY');
542 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
543 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
544 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
545
546 p_bind_output_table.EXTEND;
547 l_bind_rec.attribute_name := ':SD_SDATE';
548 l_bind_rec.attribute_value := to_char(g_sd_curr_sdate, 'DD/MM/YYYY');
549 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
550 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
551 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
552
553 p_bind_output_table.EXTEND;
554 l_bind_rec.attribute_name := ':CASH_RECEIPT_ID';
555 l_bind_rec.attribute_value := to_char(g_cash_receipt_id);
556 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
557 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
558 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
559
560 p_bind_output_table.EXTEND;
561 l_bind_rec.attribute_name := ':CUST_TRX_ID';
562 l_bind_rec.attribute_value := to_char(g_cust_trx_id);
563 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
564 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
565 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
566
567 p_bind_output_table.EXTEND;
568 l_bind_rec.attribute_name := ':TRAN_NUM';
569 l_bind_rec.attribute_value := to_char(g_tran_num);
570 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
571 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
572 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
573
574 p_bind_output_table.EXTEND;
575 l_bind_rec.attribute_name := ':TRAN_CLASS';
576 l_bind_rec.attribute_value := to_char(g_tran_class);
577 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
578 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
579 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
580
581 p_bind_output_table.EXTEND;
582 l_bind_rec.attribute_name := ':CUST_ACCOUNT';
583 l_bind_rec.attribute_value := to_char(g_cust_account);
584 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
585 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
586 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
587
588 p_bind_output_table.EXTEND;
589 l_bind_rec.attribute_name := ':APP_CUST_TRX_ID';
590 l_bind_rec.attribute_value := to_char(g_app_cust_trx_id);
591 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
592 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
593 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
594
595 p_bind_output_table.EXTEND;
596 l_bind_rec.attribute_name := ':PAGE_REFRESH_DATE';
597 l_bind_rec.attribute_value := to_char(g_page_refresh_date, 'DD/MM/YYYY');
598 l_bind_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
599 l_bind_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
600 p_bind_output_table(p_bind_output_table.COUNT) := l_bind_rec;
601
602 END bind_variable;
603
604 FUNCTION get_sec_profile RETURN NUMBER IS
605 stmt NUMBER;
606 BEGIN
607 stmt := nvl(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'), -1);
608 RETURN stmt;
609 END get_sec_profile;
610
611 FUNCTION get_dso_period_profile RETURN NUMBER IS
612 stmt NUMBER;
613 BEGIN
614 stmt := nvl(fnd_profile.value('FII_AR_DSO_PERIOD'), -1);
615 RETURN stmt;
616 END get_dso_period_profile;
617
618
619 FUNCTION get_dso_setup_value(p_category IN VARCHAR2) RETURN VARCHAR2 IS
620 l_flag VARCHAR2(1) DEFAULT 'N';
621
622 BEGIN
623 SELECT dso_value INTO l_flag FROM FII_AR_DSO_SETUP WHERE dso_type = p_category;
624 RETURN l_flag;
625
626 END get_dso_setup_value;
627
628
629 PROCEDURE get_dso_table_values IS
630 l_dso_sql VARCHAR2(100);
631 BEGIN
632 l_dso_sql := ' SELECT dso_type,dso_value FROM FII_AR_DSO_SETUP ';
633 EXECUTE IMMEDIATE l_dso_sql BULK COLLECT INTO g_dso_table;
634
635 END get_dso_table_values;
636
637
638 FUNCTION determine_OU_LOV RETURN NUMBER IS
639 l_all_org_flag VARCHAR2(30);
640 l_business_group_id NUMBER;
641
642 BEGIN
643 g_security_profile_id := fii_ar_util_pkg.get_sec_profile;
644
645 g_security_org_id := fnd_profile.value('ORG_ID');
646
647 IF g_security_profile_id is NOT NULL AND g_security_profile_id <> -1 THEN
648 SELECT view_all_organizations_flag, business_group_id
649 INTO l_all_org_flag, l_business_group_id
650 FROM per_security_profiles
651 WHERE security_profile_id = g_security_profile_id;
652
653 /* 'MO: Security Profile' is defined with a global view all security profile.*/
654 IF l_all_org_flag = 'Y' and l_business_group_id is NULL THEN
655 return 1;
656 /* 'MO: Security Profile' is defined with a business group view all security profile.*/
657 ELSIF l_all_org_flag = 'Y' and l_business_group_id is NOT NULL THEN
658 return 2;
659 ELSE
660 /* 'MO: Security Profile' is not defined with a view all security profile.*/
661 return 3;
662 END IF;
663 ELSE
664 /* 'MO: Security Profile' is not defined. */
665 return 4;
666 END IF;
667
668 END determine_OU_LOV;
669
670 FUNCTION get_business_group RETURN NUMBER IS
671 l_business_group_id NUMBER;
672 BEGIN
673 g_security_profile_id := fii_ar_util_pkg.get_sec_profile;
674
675 SELECT business_group_id
676 INTO l_business_group_id
677 FROM per_security_profiles
678 WHERE security_profile_id = g_security_profile_id;
679
680 return NVL(l_business_group_id,-1);
681 EXCEPTION
682 when too_many_rows then
683 return -1;
684 when others then
685 return -1;
686
687 END get_business_group;
688
689 FUNCTION get_display_currency(p_selected_operating_unit IN VARCHAR2) RETURN VARCHAR2 IS
690 l_org_id NUMBER;
691 BEGIN
692 IF g_security_profile_id is null then
693 g_security_profile_id := nvl(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'), -1);
694 END IF;
695
696 g_det_ou_lov := determine_OU_LOV;
697
698 g_business_group_id := fii_ar_util_pkg.get_business_group;
699
700 IF(p_selected_operating_unit <> 'ALL') then
701 IF (g_operating_unit is null or g_operating_unit <> p_selected_operating_unit) THEN
702 g_operating_unit := p_selected_operating_unit;
703
704 select currency_code
705 into g_functional_currency_code
706 from ar_system_parameters_all fsp,
707 gl_sets_of_books gsob
708 where fsp.org_id = p_selected_operating_unit
709 and fsp.set_of_books_id = gsob.set_of_books_id;
710 END IF;
711
712 IF (g_functional_currency_code = g_prim_global_currency_code) OR
713 (g_functional_currency_code = g_sec_global_currency_code) THEN
714 return NULL;
715 ELSE
716 return g_functional_currency_code;
717 END IF;
718
719 ELSE -- operating unit is 'All'
720 -- IF g_all_operating_unit is null THEN
721 -- g_all_operating_unit := p_selected_operating_unit;
722
723 IF g_det_ou_lov=1 THEN
724
725 select distinct currency_code
726 into g_common_functional_currency
727 from ar_system_parameters_all fsp,
728 gl_sets_of_books gsob
729 where fsp.set_of_books_id = gsob.set_of_books_id
730 and fsp.org_id = fsp.org_id ;
731
732 ELSIF g_det_ou_lov=2 THEN
733 select distinct currency_code
734 into g_common_functional_currency
735 from ar_system_parameters_all fsp,
736 gl_sets_of_books gsob
737 where fsp.set_of_books_id = gsob.set_of_books_id
738 AND fsp.org_id in (SELECT organization_id
739 FROM hr_operating_units
740 WHERE business_group_id = fii_ar_util_pkg.g_business_group_id) ;
741
742 ELSIF g_det_ou_lov=3 THEN
743 select distinct currency_code
744 into g_common_functional_currency
745 from ar_system_parameters_all fsp,
746 gl_sets_of_books gsob
747 where fsp.set_of_books_id = gsob.set_of_books_id
748 AND fsp.org_id in (SELECT organization_id
749 FROM per_organization_list
750 WHERE security_profile_id = g_security_profile_id) ;
751
752 ELSIF g_det_ou_lov=4 THEN
753 l_org_id := nvl(fnd_profile.value('ORG_ID'), -1);
754 select distinct currency_code
755 into g_common_functional_currency
756 from ar_system_parameters_all fsp,
757 gl_sets_of_books gsob
758 where fsp.set_of_books_id = gsob.set_of_books_id
759 AND fsp.org_id = l_org_id ;
760
761 END IF;
762
763 /*
764 select distinct currency_code
765 into g_common_functional_currency
766 from ar_system_parameters_all fsp,
767 gl_sets_of_books_v gsob
768 where fsp.set_of_books_id = gsob.set_of_books_id
769 AND (
770 (
771 g_det_ou_lov=1 AND fsp.org_id = fsp.org_id
772 )
773 OR (
774 g_det_ou_lov=2
775 AND fsp.org_id in (
776 SELECT organization_id
777 FROM hr_operating_units
778 WHERE business_group_id = fii_ar_util_pkg.g_business_group_id
779 )
780 )
781 OR (
782 g_det_ou_lov=3
783 AND fsp.org_id in (
784 SELECT organization_id
785 FROM per_organization_list
786 WHERE security_profile_id = g_security_profile_id
787 )
788 )
789 OR(
790 g_det_ou_lov=4 AND fsp.org_id = nvl(fnd_profile.value('ORG_ID'), -1)
791 )
792 );
793
794 */
795 -- END IF;
796
797
798 IF (g_common_functional_currency = g_prim_global_currency_code) OR
799 (g_common_functional_currency = g_sec_global_currency_code) THEN
800 return NULL;
801 ELSE
802 return g_common_functional_currency;
803 END IF;
804
805 END IF;
806
807 EXCEPTION
808 when too_many_rows then
809 g_common_functional_currency := 'N/A';
810 return 'N/A';
811 when others then
812 return 'N/A';
813 END get_display_currency;
814
815
816 FUNCTION get_curr RETURN VARCHAR2 IS
817 stmt VARCHAR2(240);
818
819 BEGIN
820 SELECT id INTO stmt FROM fii_currencies_v WHERE id = 'FII_GLOBAL1';
821 RETURN stmt;
822
823 END get_curr;
824
825
826 /* This procedure is for populating global temporary table FII_AR_SUMMARY_GT. */
827 PROCEDURE populate_summary_gt_tables IS
828
829 l_schema_name VARCHAR2(10);
830 l_cust_account_id VARCHAR2(100);
831 l_org_count NUMBER;
832 l_org_from VARCHAR2(1000):=NULL;
833 l_org_list VARCHAR2(240):=NULL;
834 l_group_by VARCHAR2(240):=NULL;
835 l_party_select VARCHAR2(500):=NULL;
836 l_party_from VARCHAR2(500):=NULL;
837 l_party_where VARCHAR2(2000):=NULL;
838 l_party_group_by VARCHAR2(500):=NULL;
839 l_industry_group_by VARCHAR2(500):=NULL;
840 l_cust_account_where VARCHAR2(240):=NULL;
841 l_org_select VARCHAR2(240):=NULL;
842 l_select VARCHAR2(1500):=NULL;
843 l_select2 VARCHAR2(1500):=NULL;
844 l_collector_select VARCHAR2(100):=NULL;
845 l_collector_where VARCHAR2(1000):=NULL;
846 l_org_group_by VARCHAR2(100):=NULL;
847 l_org_specific_where VARCHAR2(100):=NULL;
848 l_parent_select VARCHAR2(500):=NULL;
849 l_parent_group_by VARCHAR2(100):=NULL;
850 l_industry_from VARCHAR2(240):=NULL;
851 l_industry_where VARCHAR2(500):=NULL;
852 l_industry_select VARCHAR2(500):=NULL;
853 l_all_org_flag VARCHAR2(30);
854 l_business_group_id NUMBER;
855 l_org_count NUMBER;
856 l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
857 l_unid_message VARCHAR2(30) := FND_MESSAGE.get_string('FII', 'FII_AR_UNID_CUSTOMER');
858 l_unassigned_message VARCHAR2(30) := FND_MESSAGE.get_string('BIS', 'EDW_UNASSIGNED');
859
860 i number;
861
862 TYPE larray IS TABLE OF VARCHAR2(3000) INDEX BY BINARY_INTEGER;
863
864 tbl_parent_party_id larray;
865 tbl_party_id larray;
866 tbl_cust_account_id larray;
867 tbl_org_id larray;
868 tbl_collector_id larray;
869 tbl_view_by larray;
870 tbl_viewby_code larray;
871 tbl_is_leaf_flag larray;
872 tbl_class_code larray;
873 tbl_class_category larray;
874 tbl_is_self_flag larray;
875
876 l_table_count NUMBER;
877
878 BEGIN
879
880 l_schema_name := FII_UTIL.get_schema_name('FII');
881
882 EXECUTE IMMEDIATE 'truncate table '||l_schema_name||'.fii_ar_summary_gt';
883
884
885 /* This dynamic select in the case of customer dimension is hierarchical checks if the
886 party chosen is leaf node. If True it gets the immediate parent_party_id from
887 fii_customer_hierarches.*/
888
889 IF g_is_hierarchical_flag = 'Y'
890 AND (g_view_by = 'CUSTOMER+FII_CUSTOMERS' or g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS')
891 AND (g_count_parent_party_id = 1 AND g_party_id <> '-111' ) THEN
892
893 l_parent_select := ' f.next_level_is_leaf_flag,(SELECT decode (x.next_level_is_leaf_flag,
894 ''Y'', x.parent_party_id, f.child_party_id)
895 FROM fii_customer_hierarchies x
896 WHERE x.next_level_party_id = f.child_party_id
897 AND x.child_party_id = f.child_party_id
898 AND x.child_party_id <> x.parent_party_id) parent_party_id ';
899
900 l_parent_group_by := '';--', f.next_level_is_leaf_flag, f.child_party_id ';
901
902 ELSIF g_is_hierarchical_flag = 'Y'
903 AND (g_view_by = 'CUSTOMER+FII_CUSTOMERS' or g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS')
904 AND (g_count_parent_party_id > 1 AND g_party_id <> '-111') Then
905
906 l_parent_select := ' f.next_level_is_leaf_flag,(SELECT decode (x.next_level_is_leaf_flag,
907 ''Y'', x.parent_party_id, f.child_party_id)
908 FROM fii_customer_hierarchies x
909 WHERE x.next_level_party_id = f.child_party_id
910 AND x.child_party_id = f.child_party_id
911 AND x.child_party_id <> x.parent_party_id) parent_party_id ';
912
913 l_parent_group_by := '';--', f.next_level_is_leaf_flag, f.child_party_id ';
914
915 ELSIF g_is_hierarchical_flag = 'Y'
916 AND (g_view_by = 'CUSTOMER+FII_CUSTOMERS' or g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS')
917 AND (g_party_id = '-111') Then
918
919 l_parent_select := ' f.next_level_is_leaf_flag,(SELECT decode (x.next_level_is_leaf_flag,
920 ''Y'', x.parent_party_id, f.child_party_id)
921 FROM fii_customer_hierarchies x
922 WHERE x.next_level_party_id = f.child_party_id
923 AND x.child_party_id = f.child_party_id
924 AND x.child_party_id <> x.parent_party_id) parent_party_id ';
925
926 l_parent_group_by := '';--', f.next_level_is_leaf_flag, f.child_party_id ';
927
928 --l_parent_select := ' f.next_level_is_leaf_flag, f.parent_party_id ';
929 --l_parent_group_by := ', f.next_level_is_leaf_flag, f.parent_party_id ';
930
931 ELSIF g_is_hierarchical_flag = 'N'
932 AND (g_view_by = 'CUSTOMER+FII_CUSTOMERS' or g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') Then
933
934 l_parent_select := 'f.next_level_is_leaf_flag, f.parent_party_id parent_party_id ' ;
935 l_parent_group_by := ' ';--', f.next_level_is_leaf_flag, f.parent_party_id ';
936
937 ELSE
938 l_parent_select := 'NULL is_leaf_flag, NULL parent_party_id ' ;
939 l_parent_group_by := NULL;
940
941 END IF;
942
943
944 /*The below if condition handles the where clause to show rolled up amount for parent
945 party, when the user chooses multiple parties and out of which one is child of another
946 party selected. In the case when only 1 party is chosen, the where clause is built to
947 show both parent self record and all its child parties.*/
948
949
950 IF g_is_hierarchical_flag = 'Y' AND g_count_parent_party_id > 1 THEN
951
952 IF g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
953
954 l_party_select := ' ,f.child_party_id child_party_id ';
955 l_party_where := ' f.next_level_party_id IN ('||g_party_id||')
956 AND f.parent_party_id <> f.child_party_id ';
957
958 l_party_from :=' fii_customer_hierarchies f';
959 l_party_group_by := '';--'f.child_party_id, p.organization_id, p.name ';
960
961 ELSIF g_view_by = 'CUSTOMER+FII_CUSTOMERS'
962 or g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
963 l_party_select := ' ,f.child_party_id child_party_id ';
964 l_party_where := ' f.next_level_party_id IN ('||g_party_id||')
965 AND f.parent_party_id <> f.child_party_id
966 AND f.next_level_party_id = hz.party_id ';
967
968 l_party_from :=' fii_customer_hierarchies f, hz_parties hz ';
969 l_party_group_by := '';--'f.parent_party_id, f.child_party_id, hz.party_name, hz.party_id ';
970
971 ELSIF g_view_by = 'FII_COLLECTOR+FII_COLLECTOR' THEN
972 l_party_select := ' ,f.child_party_id child_party_id ';
973 l_party_where := ' f.next_level_party_id IN ('||g_party_id||')
974 AND f.parent_party_id <> f.child_party_id ';
975 --AND f.next_level_party_id = hz.party_id ';
976
977 --l_party_from :=' fii_customer_hierarchies f, fii_collectors hz, ar_collectors c ';
978 --l_party_from :=' fii_customer_hierarchies f, ar_collectors c ';
979 l_party_from :=' fii_customer_hierarchies f, fii_ar_help_collectors c ';
980 l_party_group_by := ' ';--' f.child_party_id, c.collector_id,c.name ';
981
982 ELSIF g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
983 l_party_select := ' ,f.child_party_id child_party_id ';
984 l_party_where := ' f.next_level_party_id IN ('||g_party_id||')
985 AND f.parent_party_id <> f.child_party_id ';
986 --AND f.next_level_party_id = hz.party_id ';
987
988 --l_party_from :=' fii_customer_hierarchies f, fii_party_mkt_class hz, fnd_lookup_values c ';
989 l_party_from :=' fii_customer_hierarchies f, fii_ar_help_mkt_classes c ';
990 l_party_group_by := ' ';--' f.child_party_id, c.class_name, c.class_code ';
991
992 END IF;
993
994 ELSE
995
996 IF g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
997
998 IF g_party_id = '-111' THEN
999 l_party_select := ' ,NULL child_party_id ';
1000 l_party_where := NULL;
1001 l_party_from := NULL;
1002 l_party_group_by := NULL;
1003 ELSE
1004
1005 l_party_select := ' ,f.child_party_id child_party_id ';
1006 if g_party_id = '-2' then
1007 --Bug 5088391
1008 l_party_where := ' f.child_party_id IN ('||g_party_id||' ) ';
1009 else
1010 l_party_where := ' f.parent_party_id IN ('||g_party_id||' ) ';
1011 end if;
1012
1013 l_party_from :=' fii_customer_hierarchies f ';
1014 l_party_group_by := '';--'f.child_party_id, p.organization_id,p.name ';
1015 END IF;
1016 ELSIF g_view_by = 'CUSTOMER+FII_CUSTOMERS'
1017 or g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
1018
1019 l_party_select := ' ,f.child_party_id child_party_id ';
1020 IF g_party_id = '-111' THEN
1021 IF g_is_hierarchical_flag = 'Y' THEN
1022 l_party_where := ' f.parent_party_id = -999 ';
1023 ELSE
1024 l_party_where := ' f.parent_party_id = f.child_party_id ';
1025 END IF;
1026 ELSE
1027 l_party_where := ' f.parent_party_id IN ('||g_party_id||' ) ';
1028 END IF;
1029 l_party_where := l_party_where||' AND f.next_level_party_id = hz.party_id ';
1030 l_party_from :=' fii_customer_hierarchies f, hz_parties hz ';
1031 l_party_group_by := '';--'f.parent_party_id, f.child_party_id, hz.party_name, hz.party_id ';
1032
1033 ELSIF g_view_by = 'FII_COLLECTOR+FII_COLLECTOR' THEN
1034 IF g_party_id = '-111' THEN
1035 l_party_select := ' ,NULL child_party_id ';
1036 l_party_where := NULL;
1037 l_party_from := ' ar_collectors c ';
1038 l_party_group_by := ' ';--'c.collector_id,c.name ';
1039 ELSE
1040 l_party_select := ' ,f.child_party_id child_party_id ';
1041 l_party_where := ' f.parent_party_id IN ('||g_party_id||' ) ';
1042 --AND f.child_party_id = hz.party_id ';
1043 --l_party_from :=' fii_customer_hierarchies f, fii_collectors hz, ar_collectors c ';
1044 --l_party_from :=' fii_customer_hierarchies f, ar_collectors c ';
1045 l_party_from :=' fii_customer_hierarchies f, fii_ar_help_collectors c ';
1046 l_party_group_by := ' ';--' f.child_party_id, c.collector_id,c.name ';
1047 END IF;
1048
1049 ELSIF g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
1050
1051 IF g_party_id = '-111' THEN
1052 l_party_select := ' ,NULL child_party_id ';
1053 l_party_where := NULL;
1054 --l_party_from := ' fii_party_mkt_class hz, fnd_lookup_values c ';
1055 l_party_from := ' fii_ar_help_mkt_classes c ';
1056 l_party_group_by := ' c.class_name, c.class_code ';
1057 ELSE
1058 l_party_select := ' ,f.child_party_id child_party_id ';
1059 l_party_where := ' f.parent_party_id IN ('||g_party_id||' ) ';
1060 --l_party_from :=' fii_customer_hierarchies f, fii_party_mkt_class hz, fnd_lookup_values c ';
1061 l_party_from :=' fii_customer_hierarchies f, fii_ar_help_mkt_classes c ';
1062 l_party_group_by := ' ';--' f.child_party_id, c.class_name, c.class_code ';
1063 END IF;
1064
1065 END IF;
1066
1067 END IF;
1068
1069
1070 /* Handles Org related SELECT, FROM and WHERE clause */
1071
1072 g_security_profile_id := fii_ar_util_pkg.get_sec_profile;
1073 g_security_org_id := fnd_profile.value('ORG_ID');
1074
1075 /* Security is dictated by 'MO: Security Profile'. */
1076
1077 IF g_security_profile_id is not null AND g_security_profile_id <> -1 THEN
1078
1079
1080 SELECT view_all_organizations_flag, business_group_id
1081 INTO l_all_org_flag, l_business_group_id
1082 FROM per_security_profiles
1083 WHERE security_profile_id = g_security_profile_id;
1084
1085 IF g_org_id = -111 THEN
1086 l_org_specific_where := NULL;
1087 ELSE
1088 l_org_specific_where := ' AND per.organization_id= '||g_org_id;
1089 END IF;
1090
1091
1092 IF l_all_org_flag = 'Y' and l_business_group_id is NOT NULL THEN
1093
1094 l_org_select := ' p.organization_id, ';
1095 l_org_group_by := ' , p.organization_id ';
1096
1097 IF g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
1098
1099 l_org_from := ' ( SELECT per.organization_id,hr.name
1100 FROM hr_operating_units per, ar_system_parameters_all ar, hr_all_organization_units hr
1101 WHERE per.business_group_id = '||l_business_group_id ||'
1102 AND per.organization_id = ar.org_id
1103 AND per.organization_id = hr.organization_id '||l_org_specific_where||') p ';
1104
1105 ELSE
1106 l_org_from := ' (SELECT per.organization_id
1107 FROM hr_operating_units per, ar_system_parameters_all ar
1108 WHERE per.business_group_id = '||l_business_group_id ||'
1109 AND per.organization_id = ar.org_id '||l_org_specific_where||') p ';
1110
1111 END IF;
1112 ELSIF l_all_org_flag = 'Y' and l_business_group_id is NULL THEN
1113
1114 l_org_select := ' p.organization_id, ';
1115 l_org_group_by := ' , p.organization_id ';
1116
1117 IF g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
1118
1119 l_org_from := ' ( SELECT per.organization_id,hr.name
1120 FROM hr_operating_units per, hr_all_organization_units hr
1121 WHERE per.organization_id = hr.organization_id '||l_org_specific_where||') p ';
1122
1123 ELSE
1124 l_org_from := ' (SELECT per.organization_id
1125 FROM hr_operating_units per
1126 WHERE 1=1 '||l_org_specific_where||') p ';
1127
1128 END IF;
1129 ELSE
1130
1131 l_org_select := ' p.organization_id, ';
1132 l_org_group_by := ' , p.organization_id ';
1133
1134 IF g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
1135
1136 l_org_from := ' ( SELECT per.organization_id,hr.name
1137 FROM per_organization_list per, ar_system_parameters_all ar, hr_all_organization_units hr
1138 WHERE per.security_profile_id = '||g_security_profile_id||'
1139 AND per.organization_id = ar.org_id
1140 AND per.organization_id = hr.organization_id '||l_org_specific_where||') p ';
1141
1142 ELSE
1143 l_org_from := ' (SELECT organization_id
1144 FROM per_organization_list per, ar_system_parameters_all ar
1145 WHERE per.security_profile_id = '||g_security_profile_id ||'
1146 AND per.organization_id = ar.org_id '||l_org_specific_where||') p ';
1147 END IF;
1148
1149 END IF;
1150
1151 /*g_security_profile_id IS NULL i.e. no security profile defined , user has acces to single org*/
1152 ELSIF g_security_org_id is not null THEN
1153
1154 IF g_org_id =-111 OR g_org_id = g_security_org_id THEN
1155 l_org_select := g_security_org_id||' organization_id, ';
1156 ELSE
1157 l_org_select := '-1 organization_id, ';
1158 END IF;
1159
1160 IF g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
1161 l_org_from := ' (SELECT organization_id, name
1162 FROM hr_all_organization_units
1163 WHERE organization_id ='||g_security_org_id||') p ';
1164 ELSE
1165 l_org_from := NULL;
1166 END IF;
1167 ELSE
1168 /*User has access to no organizations.*/
1169 l_org_select := '-1 organization_id, ';
1170 l_org_group_by := NULL;
1171
1172 IF g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
1173 l_org_from :=' (SELECT organization_id, name
1174 FROM hr_all_organization_units
1175 WHERE organization_id =-1) p ';
1176 ELSE
1177 l_org_from := NULL;
1178 END IF;
1179 END IF;
1180
1181
1182 /* Handles the select clause for Collectors */
1183
1184 IF g_view_by = 'FII_COLLECTOR+FII_COLLECTOR' THEN
1185 l_collector_select := ' c.collector_id, ';
1186 IF g_party_id = '-111' THEN
1187 l_collector_where := null;
1188 ELSE
1189 l_collector_where := ' exists (select ''x''
1190 from fii_collectors hz
1191 where hz.collector_id = c.collector_id
1192 and hz.party_id = f.child_party_id) ';
1193 END IF;
1194
1195 /*
1196 l_collector_where := ' exists (select ''x''
1197 from fii_collectors hz
1198 where hz.collector_id = c.collector_id '||
1199 case when g_party_id <> '-111' then 'AND hz.party_id = f.child_party_id' else '' end||') ';
1200 */
1201
1202 IF g_collector_id <> '-111' then
1203 if l_collector_where is not null then
1204 l_collector_where := l_collector_where||' AND c.collector_id = '||g_collector_id;
1205 else
1206 l_collector_where := ' c.collector_id = '||g_collector_id;
1207 end if;
1208 END IF;
1209 ELSE
1210 l_collector_select := NULL;
1211 l_collector_where := NULL;
1212
1213 IF g_collector_id = '-111' then
1214 l_collector_select := ' NULL collector_id, ';
1215 ELSE
1216 l_collector_select := g_collector_id||' collector_id, ';
1217 END IF;
1218 END IF;
1219
1220 /* Handles the where clause for Industry */
1221
1222 IF g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
1223 /*
1224 l_industry_select := ' hz.class_code class_code, hz.class_category class_category, ';
1225 l_industry_where := ' hz.class_category = c.lookup_type (+)
1226 AND hz.class_code = c.lookup_code (+)
1227 AND nvl(c.language,userenv(''LANG'')) = userenv(''LANG'')
1228 AND nvl(c.view_application_id,222) = 222 ';
1229 */
1230
1231 l_industry_select := ' c.class_code class_code, c.class_category class_category, ';
1232 IF g_party_id = '-111' THEN
1233 l_industry_where := null;
1234 ELSE
1235 l_industry_where := ' exists (select ''x''
1236 from fii_party_mkt_class hz
1237 where hz.class_code = c.class_code
1238 and hz.party_id = f.child_party_id) ';
1239 END IF;
1240 /*
1241 if g_industry_id <> '-111' then
1242 l_industry_where := l_industry_where ||'AND hz.class_code='||g_industry_id||' and hz.class_category='''||g_industry_class_type||''' ';
1243 end if;
1244 */
1245 IF g_industry_id <> '-111' then
1246 if l_industry_where is not null then
1247 l_industry_where := l_industry_where ||'AND c.class_code='||g_industry_id||' and c.class_category='''||g_industry_class_type||''' ';
1248 else
1249 l_industry_where := ' c.class_code='||g_industry_id||' and c.class_category='''||g_industry_class_type||''' ';
1250 end if;
1251 END IF;
1252
1253 l_party_group_by := ' ';--l_party_group_by||', c.class_code, c.class_category';
1254 ELSE
1255
1256 IF g_industry_id = '-111' THEN
1257 l_industry_select := ' NULL class_code, NULL class_category, ';
1258 l_industry_from := NULL;
1259 l_industry_where := NULL;
1260 l_industry_group_by := ' ';
1261 ELSE
1262 IF g_view_by = 'CUSTOMER+FII_CUSTOMERS'
1263 or g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
1264 l_industry_select := ' '||g_industry_id||', '''||g_industry_class_type||''', ';
1265 l_industry_from := ', fii_party_mkt_class m ';
1266 l_industry_where := ' f.child_party_id = m.party_id
1267 AND m.class_code='||g_industry_id||' and m.class_category='''||g_industry_class_type||'''';
1268 l_industry_group_by := ' ';--', m.class_code, m.class_category';
1269 ELSE
1270 l_industry_select := ' '||g_industry_id||' class_code, '||''''||g_industry_class_type||''''||' class_category,';
1271 l_industry_where := ' m.class_code='||g_industry_id||' and m.class_category='''||g_industry_class_type||'''';
1272
1273 END IF;
1274 END IF;
1275
1276 END IF;
1277
1278 /* Code for appending comma, WHERE, AND, GROUP BY clauses to the dynamic SELECT clause */
1279
1280 IF l_party_from IS NOT NULL AND l_org_from IS NOT NULL THEN
1281 l_org_from := ','||l_org_from;
1282 END IF;
1283
1284 IF l_party_where IS NOT NULL THEN
1285 l_party_where := ' WHERE '||l_party_where;
1286 END IF;
1287
1288 IF l_collector_where IS NOT NULL and l_party_where IS NULL THEN
1289 l_collector_where := ' WHERE '||l_collector_where;
1290 ELSIF l_collector_where IS NOT NULL and l_party_where IS NOT NULL THEN
1291 l_collector_where := ' AND '||l_collector_where;
1292 END IF;
1293
1294 IF l_industry_where IS NOT NULL and l_party_where IS NULL THEN
1295 l_industry_where := ' WHERE '||l_industry_where;
1296 ELSIF l_industry_where IS NOT NULL and l_party_where IS NOT NULL THEN
1297 l_industry_where := ' AND '||l_industry_where;
1298 END IF;
1299
1300 IF l_party_group_by IS NOT NULL THEN
1301 l_party_group_by := ' GROUP BY '||l_party_group_by;
1302 ELSIF l_parent_group_by IS NOT NULL THEN
1303 l_parent_group_by := ' GROUP BY '||l_parent_group_by;
1304 END IF;
1305
1306
1307 --For cases where view by is not Industry, we dont need to use group by
1308 /*If g_view_by = 'CUSTOMER+FII_CUSTOMERS'
1309 or g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS'
1310 or g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS'
1311 or g_view_by = 'FII_COLLECTOR+FII_COLLECTOR' THEN
1312 */
1313 l_party_group_by := ' ';
1314 l_parent_group_by := ' ';
1315 l_org_group_by := ' ';
1316 l_industry_group_by := ' ';
1317
1318 --END IF;
1319
1320
1321
1322 /* VIEW BY CUSTOMERS */
1323
1324 IF g_view_by = 'CUSTOMER+FII_CUSTOMERS'
1325 or g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
1326 /*
1327 l_select := ' SELECT '||l_parent_select||l_party_select||', '
1328 ||l_org_select||l_collector_select||l_industry_select||
1329 ' hz.party_name view_by FROM '||l_party_from||l_org_from||l_party_where||
1330 l_party_group_by||l_parent_group_by||l_org_group_by;
1331 */
1332
1333 l_select := 'SELECT '||l_parent_select||l_party_select||', '
1334 ||l_org_select||l_collector_select||l_industry_select||
1335 ' case when f.parent_party_id = hz.party_id
1336 and f.next_level_is_leaf_flag <> ''Y''
1337 then hz.party_name||'' '||g_self_msg||
1338 ''' else hz.party_name end view_by,
1339 hz.party_id viewby_code,
1340 case when f.parent_party_id = hz.party_id
1341 and f.next_level_is_leaf_flag <> ''Y''
1342 then ''Y''
1343 else ''N'' end is_self_flag FROM '||l_party_from||l_org_from||l_industry_from||l_party_where||l_industry_where||
1344 l_party_group_by||l_parent_group_by||l_org_group_by||l_industry_group_by;
1345
1346 END IF;
1347
1348
1349 /* VIEW BY INDUSTRY */
1350
1351 IF g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
1352
1353 l_select := ' SELECT '||l_parent_select||l_party_select||', '||l_org_select||l_collector_select||l_industry_select||' c.class_name view_by,
1354 c.class_code viewby_code, null is_self_flag FROM '||l_party_from||l_org_from||l_industry_from||l_party_where||l_industry_where||
1355 l_party_group_by||l_org_group_by||l_parent_group_by;
1356
1357 END IF;
1358
1359
1360 /* VIEW BY OPERATING UNIT */
1361
1362 IF g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
1363
1364 l_select := ' SELECT '||l_parent_select||l_party_select||', '||l_org_select||l_collector_select||l_industry_select||
1365 ' p.name viewby, p.organization_id viewby_code, null is_self_flag FROM '||l_party_from||l_org_from||l_party_where||l_party_group_by||l_parent_group_by;
1366
1367 END IF;
1368
1369
1370 /* VIEW BY COLLECTOR */
1371
1372 IF g_view_by = 'FII_COLLECTOR+FII_COLLECTOR' THEN
1373
1374 l_select := ' SELECT '||l_parent_select||l_party_select||', '
1375 ||l_org_select||l_collector_select||l_industry_select||' c.name view_by,
1376 c.collector_id viewby_code, null is_self_flag FROM '||l_party_from||l_org_from||l_party_where||l_collector_where||
1377 l_party_group_by||l_org_group_by||l_parent_group_by;
1378
1379 END IF;
1380
1381 IF l_debug_mode = 'Y' THEN
1382
1383 SELECT COUNT(*) INTO l_table_count
1384 FROM all_tables
1385 WHERE table_name = 'FII_AR_DEBUG_STATEMENTS'
1386 and owner = l_schema_name;
1387
1388 IF l_table_count = 0 THEN
1389
1390 EXECUTE IMMEDIATE 'CREATE TABLE '||l_schema_name||'.FII_AR_DEBUG_STATEMENTS (PACKAGE VARCHAR2(50),
1391 PROCEDURE VARCHAR2(50),
1392 SESSION_ID NUMBER,
1393 REGION_CODE VARCHAR2(50),
1394 SQL_STATEMENT VARCHAR2(50),
1395 VALUE VARCHAR2(1500))';
1396
1397 EXECUTE IMMEDIATE 'CREATE INDEX '||l_schema_name||'.FII_AR_DEBUG_STATEMENTS_N1 ON '||l_schema_name||'.FII_AR_DEBUG_STATEMENTS
1398 (region_code,
1399 session_id)';
1400
1401 ELSE
1402 EXECUTE IMMEDIATE 'DELETE FROM '||l_schema_name||'.FII_AR_DEBUG_STATEMENTS WHERE REGION_CODE = '''||g_region_code||''' AND SESSION_ID = '||g_session_id;
1403
1404 END IF;
1405
1406 EXECUTE IMMEDIATE 'INSERT INTO '||l_schema_name||'.FII_AR_DEBUG_STATEMENTS (package,
1407 procedure,
1408 session_id,
1409 region_code,
1410 sql_statement,
1411 value)
1412 VALUES (''FII_AR_UTIL_PKG'',
1413 ''POPULATE_SUMMARY_GT_TABLES'',
1414 '||g_session_id||','''||g_region_code||''' ,
1415 ''l_select'',
1416 '''||REPLACE(l_select, '''', '''''')||''')';
1417 commit;
1418
1419 END IF;
1420
1421 /*
1422 EXECUTE IMMEDIATE l_select BULK COLLECT INTO tbl_is_leaf_flag, tbl_parent_party_id, tbl_party_id,
1423 tbl_org_id, tbl_collector_id, tbl_class_code, tbl_class_category, tbl_view_by, tbl_viewby_code, tbl_is_self_flag;
1424
1425 FORALL i in 1 .. tbl_party_id.count
1426 INSERT INTO fii_ar_summary_gt (parent_party_id, party_id, org_id, collector_id, viewby, viewby_code, is_leaf_flag, class_code, class_category, is_self_flag)
1427 VALUES
1428 (tbl_parent_party_id(i), tbl_party_id(i), tbl_org_id(i), tbl_collector_id(i),
1429 tbl_view_by(i), tbl_viewby_code(i), tbl_is_leaf_flag(i), tbl_class_code(i), tbl_class_category(i), tbl_is_self_flag(i));
1430 */
1431
1432 EXECUTE IMMEDIATE 'INSERT INTO FII_AR_SUMMARY_GT
1433 (is_leaf_flag, parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_self_flag) '
1434 || l_select;
1435
1436 commit;
1437
1438 --Bug 5055449,5071096, 5173574
1439 ------------------------------
1440 IF --g_region_code = 'FII_AR_UNAPP_RCT_SUMMARY'
1441 (g_view_by = 'CUSTOMER+FII_CUSTOMERS'
1442 or g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS')
1443 AND (g_party_id = '-111' or g_party_id = '-2') THEN
1444
1445 IF l_org_from is not null THEN
1446
1447 l_select2 := 'SELECT ''Y'' is_leaf_flag, -999 parent_party_id, -2 party_id, '
1448 ||l_org_select||l_collector_select||' -1 class_code, '''||g_industry_class_type||''', '||
1449 ' '''||l_unid_message||''' view_by,
1450 -2 viewby_code,
1451 ''N'' is_self_flag FROM '|| LTRIM(l_org_from, ',');
1452 ELSE
1453
1454 l_select2 := 'SELECT ''Y'' is_leaf_flag, -999 parent_party_id, -2 party_id, '
1455 ||l_org_select||l_collector_select||' -1 class_code, '''||g_industry_class_type||''', '||
1456 ' '''||l_unid_message||''' view_by,
1457 -2 viewby_code,
1458 ''N'' is_self_flag FROM dual';
1459
1460 END IF;
1461 /*
1462 EXECUTE IMMEDIATE l_select2 BULK COLLECT INTO tbl_is_leaf_flag, tbl_parent_party_id, tbl_party_id,
1463 tbl_org_id, tbl_collector_id, tbl_class_code, tbl_class_category, tbl_view_by, tbl_viewby_code, tbl_is_self_flag;
1464
1465
1466 FORALL i in 1 .. tbl_party_id.count
1467 INSERT INTO fii_ar_summary_gt (parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_leaf_flag, is_self_flag)
1468 VALUES
1469 (tbl_parent_party_id(i), tbl_party_id(i), tbl_org_id(i), tbl_collector_id(i), tbl_class_code(i), tbl_class_category(i),
1470 tbl_view_by(i), tbl_viewby_code(i), tbl_is_leaf_flag(i), tbl_is_self_flag(i));
1471 */
1472 EXECUTE IMMEDIATE 'INSERT INTO FII_AR_SUMMARY_GT
1473 (is_leaf_flag, parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_self_flag) '
1474 || l_select2;
1475
1476 ELSIF g_view_by = 'FII_COLLECTOR+FII_COLLECTOR'
1477 AND ((g_collector_id = '-111' AND g_party_id = '-111')
1478 or g_party_id = '-2') THEN
1479
1480 IF l_org_from is not null THEN
1481
1482 l_select2 := 'SELECT null is_leaf_flag, null parent_party_id, '||case when g_party_id = '-2' then '-2' else 'null' end||' party_id, '
1483 ||l_org_select||' -1 collector_id, '||l_industry_select||
1484 ' '''||l_unid_message||''' view_by,
1485 -1 viewby_code,
1486 null is_self_flag FROM '|| LTRIM(l_org_from, ',');
1487 ELSE
1488
1489 l_select2 := 'SELECT null is_leaf_flag, null parent_party_id, '||case when g_party_id = '-2' then '-2' else 'null' end||' party_id, '
1490 ||l_org_select||' -1 collector_id, '||l_industry_select||
1491 ' '''||l_unid_message||''' view_by,
1492 -1 viewby_code,
1493 null is_self_flag FROM dual';
1494
1495 END IF;
1496
1497 /*
1498 EXECUTE IMMEDIATE l_select2 BULK COLLECT INTO tbl_is_leaf_flag, tbl_parent_party_id, tbl_party_id,
1499 tbl_org_id, tbl_collector_id, tbl_class_code, tbl_class_category, tbl_view_by, tbl_viewby_code, tbl_is_self_flag;
1500
1501
1502 FORALL i in 1 .. tbl_party_id.count
1503 INSERT INTO fii_ar_summary_gt (parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_leaf_flag, is_self_flag)
1504 VALUES
1505 (tbl_parent_party_id(i), tbl_party_id(i), tbl_org_id(i), tbl_collector_id(i), tbl_class_code(i), tbl_class_category(i),
1506 tbl_view_by(i), tbl_viewby_code(i), tbl_is_leaf_flag(i), tbl_is_self_flag(i));
1507 */
1508 EXECUTE IMMEDIATE 'INSERT INTO FII_AR_SUMMARY_GT
1509 (is_leaf_flag, parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_self_flag) '
1510 || l_select2;
1511
1512
1513 ELSIF g_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS'
1514 AND g_party_id = '-2' THEN
1515
1516 IF l_org_from is not null THEN
1517
1518 l_select2 := 'SELECT ''Y'' is_leaf_flag, -999 parent_party_id, -2 party_id, '
1519 ||l_org_select||l_collector_select||' -1 class_code, '''||g_industry_class_type||''', '||
1520 ' '''||l_unid_message||''' view_by,
1521 -2 viewby_code,
1522 ''N'' is_self_flag FROM '|| LTRIM(l_org_from, ',');
1523 ELSE
1524
1525 l_select2 := 'SELECT ''Y'' is_leaf_flag, -999 parent_party_id, -2 party_id, '
1526 ||l_org_select||l_collector_select||' -1 class_code, '''||g_industry_class_type||''', '||
1527 ' '''||l_unid_message||''' view_by,
1528 -2 viewby_code,
1529 ''N'' is_self_flag FROM dual';
1530
1531 END IF;
1532 /*
1533 EXECUTE IMMEDIATE l_select2 BULK COLLECT INTO tbl_is_leaf_flag, tbl_parent_party_id, tbl_party_id,
1534 tbl_org_id, tbl_collector_id, tbl_class_code, tbl_class_category, tbl_view_by, tbl_viewby_code, tbl_is_self_flag;
1535
1536
1537 FORALL i in 1 .. tbl_party_id.count
1538 INSERT INTO fii_ar_summary_gt (parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_leaf_flag, is_self_flag)
1539 VALUES
1540 (tbl_parent_party_id(i), tbl_party_id(i), tbl_org_id(i), tbl_collector_id(i), tbl_class_code(i), tbl_class_category(i),
1541 tbl_view_by(i), tbl_viewby_code(i), tbl_is_leaf_flag(i), tbl_is_self_flag(i));
1542 */
1543 EXECUTE IMMEDIATE 'INSERT INTO FII_AR_SUMMARY_GT
1544 (is_leaf_flag, parent_party_id, party_id, org_id, collector_id, class_code, class_category, viewby, viewby_code, is_self_flag) '
1545 || l_select2;
1546
1547
1548 END IF;
1549
1550 IF l_debug_mode = 'Y' THEN
1551 insert_into_debug_table;
1552
1553 EXECUTE IMMEDIATE 'INSERT INTO '||l_schema_name||'.FII_AR_DEBUG_STATEMENTS (package,
1554 procedure,
1555 session_id,
1556 region_code,
1557 sql_statement,
1558 value)
1559 VALUES (''FII_AR_UTIL_PKG'',
1560 ''POPULATE_SUMMARY_GT_TABLES'',
1561 '||g_session_id||','''||g_region_code||''' ,
1562 ''l_select2'',
1563 '''||REPLACE(l_select2, '''', '''''')||''')';
1564
1565 commit;
1566
1567 END IF;
1568
1569 END populate_summary_gt_tables;
1570
1571
1572
1573 PROCEDURE insert_into_debug_table IS
1574 /* logic for this api...
1575 1. We first search for existence of debug table. If it doesn't exist, we create it else we delete the records inserted for the same session_id and region code.
1576 2. We then insert all columns of corresponding gt table + session_id and report_region_code into debug tables.
1577 */
1578
1579 l_table_count NUMBER := 0;
1580 l_schema_name VARCHAR2(10) := FII_UTIL.get_schema_name('FII');
1581
1582 BEGIN
1583
1584 SELECT COUNT(*) INTO l_table_count
1585 FROM all_tables
1586 WHERE table_name = 'FII_AR_DEBUG_SUMMARY'
1587 and owner = l_schema_name;
1588
1589 IF l_table_count = 0 THEN
1590
1591 EXECUTE IMMEDIATE 'CREATE TABLE '||l_schema_name||'.FII_AR_DEBUG_SUMMARY (PARENT_PARTY_ID NUMBER(15),
1592 PARTY_ID NUMBER(15),
1593 ORG_ID NUMBER(15),
1594 COLLECTOR_ID NUMBER(15),
1595 IS_LEAF_FLAG VARCHAR2(10),
1596 IS_SELF_FLAG VARCHAR2(10),
1597 CLASS_CODE VARCHAR2(30),
1598 CLASS_CATEGORY VARCHAR2(30),
1599 VIEWBY VARCHAR2(360),
1600 VIEWBY_CODE VARCHAR2(30),
1601 SESSION_ID NUMBER,
1602 REGION_CODE VARCHAR2(50))';
1603
1604 EXECUTE IMMEDIATE 'CREATE INDEX '||l_schema_name||'.FII_AR_DEBUG_SUMMARY_N1 ON '||l_schema_name||'.FII_AR_DEBUG_SUMMARY
1605 (region_code,
1606 session_id)';
1607
1608 ELSE
1609 EXECUTE IMMEDIATE 'DELETE FROM '||l_schema_name||'.FII_AR_DEBUG_SUMMARY WHERE REGION_CODE = '''||g_region_code||''' AND SESSION_ID = '||g_session_id;
1610
1611 END IF;
1612
1613 EXECUTE IMMEDIATE 'INSERT INTO '||l_schema_name||'.FII_AR_DEBUG_SUMMARY (PARENT_PARTY_ID,
1614 PARTY_ID,
1615 ORG_ID,
1616 COLLECTOR_ID,
1617 IS_LEAF_FLAG,
1618 IS_SELF_FLAG,
1619 CLASS_CODE,
1620 CLASS_CATEGORY,
1621 VIEWBY,
1622 VIEWBY_CODE,
1623 SESSION_ID,
1624 REGION_CODE)
1625 SELECT gt.PARENT_PARTY_ID,
1626 gt.PARTY_ID,
1627 gt.ORG_ID,
1628 gt.COLLECTOR_ID,
1629 gt.IS_LEAF_FLAG,
1630 gt.IS_SELF_FLAG,
1631 gt.CLASS_CODE,
1632 gt.CLASS_CATEGORY,
1633 gt.VIEWBY,
1634 gt.VIEWBY_CODE,
1635 '||g_session_id||','''||g_region_code||'''
1636 FROM fii_ar_summary_gt gt';
1637
1638 END insert_into_debug_table;
1639
1640
1641 /*The following procedure parses the g_party_id i.e. party_id parameter value passed by BIS from
1642 PMV parameter region. After that in the case of customers defined is hierarchical and if both
1643 parent and children are chosen together from customer parameter then only the parent should be
1644 stored in g_party_id variable.*/
1645
1646 PROCEDURE populate_party_id AS
1647
1648 l_parse_party_id VARCHAR2(5000):=NULL;
1649 l_party_id VARCHAR2(5000):=NULL;
1650 l_tmp_party_id VARCHAR2(5000):=NULL;
1651 l_new_party_id VARCHAR2(5000):=NULL;
1652
1653 l_select VARCHAR2(10000);
1654 l_count_party_id NUMBER:=1;
1655 l_substr_start NUMBER:=1;
1656 l_occur NUMBER:=1;
1657 l_position NUMBER;
1658 l_str_length NUMBER;
1659 l_substr_val NUMBER;
1660
1661 TYPE larray IS TABLE OF VARCHAR2(3000) INDEX BY BINARY_INTEGER;
1662 tbl_parent_party_id larray;
1663
1664
1665 BEGIN
1666
1667 IF g_party_id IS NOT NULL THEN /* Check party_id not Null */
1668 LOOP
1669
1670 SELECT INSTR(g_party_id,',', 1, l_occur) into l_position FROM DUAL;
1671 IF l_position =0 THEN
1672 SELECT LENGTH(g_party_id) INTO l_str_length FROM DUAL;
1673 l_substr_val := l_str_length - (l_substr_start-1);
1674 ELSE
1675 l_substr_val := l_position - l_substr_start;
1676 END IF;
1677 SELECT SUBSTR(g_party_id, l_substr_start, l_substr_val) into l_tmp_party_id FROM DUAL;
1678
1679 IF l_parse_party_id IS NULL THEN
1680 l_parse_party_id := l_tmp_party_id;
1681 ELSE
1682 l_parse_party_id := l_parse_party_id||','||l_tmp_party_id;
1683 l_count_party_id := l_count_party_id +1;
1684 END IF;
1685 l_occur := l_occur+1;
1686 l_substr_start := l_position+1;
1687 EXIT when l_position=0;
1688 END LOOP;
1689
1690
1691 g_count_parent_party_id := 0;
1692 IF g_is_hierarchical_flag = 'Y' and l_count_party_id > 1 THEN
1693
1694 l_select := ' SELECT parent_party_id FROM fii_customer_hierarchies p
1695 WHERE p.parent_party_id=p.next_level_party_id
1696 AND p.next_level_party_id = p.child_party_id
1697 AND p.child_party_id IN ('||l_parse_party_id||')
1698 AND NOT EXISTS (SELECT c.child_party_id FROM fii_customer_hierarchies c
1699 WHERE c.child_party_id IN ('||l_parse_party_id||')
1700 AND c.parent_party_id IN ('||l_parse_party_id||')
1701 AND c.parent_party_id <> c.next_level_party_id
1702 and c.child_party_id = p.child_party_id) ';
1703
1704 EXECUTE IMMEDIATE l_select BULK COLLECT INTO tbl_parent_party_id;
1705
1706 FOR a IN tbl_parent_party_id.FIRST..tbl_parent_party_id.LAST LOOP
1707 IF l_party_id IS NULL THEN
1708 l_party_id := tbl_parent_party_id(a);
1709
1710 ELSE
1711 l_party_id := l_party_id||','||tbl_parent_party_id(a);
1712
1713 END IF;
1714 g_count_parent_party_id := g_count_parent_party_id +1;
1715
1716 END LOOP;
1717
1718 ELSE
1719 l_party_id := l_parse_party_id;
1720 g_count_parent_party_id := l_count_party_id;
1721 END IF;
1722
1723 g_party_id := l_party_id;
1724
1725 END IF; /* Check party_id not Null */
1726
1727 END populate_party_id;
1728
1729 FUNCTION get_prim_global_currency_code RETURN VARCHAR2 IS
1730 BEGIN
1731 RETURN bis_common_parameters.get_currency_code;
1732 END get_prim_global_currency_code;
1733
1734 FUNCTION get_sec_global_currency_code RETURN VARCHAR2 IS
1735 BEGIN
1736 RETURN bis_common_parameters.get_secondary_currency_code;
1737 END get_sec_global_currency_code;
1738
1739 FUNCTION get_from_statement RETURN VARCHAR2 IS
1740 BEGIN
1741 IF g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
1742 RETURN 'fii_ar_summary_gt_v';
1743 ELSE
1744 RETURN 'fii_ar_summary_gt';
1745 END IF;
1746 END get_from_statement;
1747
1748 FUNCTION get_where_statement RETURN VARCHAR2 IS
1749 BEGIN
1750 RETURN '1=1';
1751 END get_where_statement;
1752
1753 FUNCTION get_mv_where_statement RETURN VARCHAR2 IS
1754 BEGIN
1755
1756 IF g_cust_suffix = '_base' THEN
1757 IF g_region_code IN ('FII_AR_DISCOUNT_SUMMARY',
1758 'FII_AR_COLL_EFF_INDEX',
1759 'FII_AR_DSO',
1760 'FII_AR_DSO_TREND',
1761 'FII_AR_BILLING_ACTIVITY',
1762 'FII_AR_BILL_ACTIVITY_TABLE',
1763 'FII_AR_UNAPP_RCT_SUMMARY',
1764 'FII_AR_UNAPP_RCT_SUMM_TBL',
1765 'FII_AR_NET_REC_SUM',
1766 'FII_AR_CURR_REC_SUMMARY',
1767 'FII_AR_PDUE_REC_TREND',
1768 'FII_AR_RECEIVABLES_AGING',
1769 'FII_AR_PASTDUE_REC_AGING',
1770 'FII_AR_TOP_PDUE_CUSTOMER',
1771 'FII_AR_OPEN_REC_SUMMARY',
1772 'FII_AR_OPEN_REC_PDUE') THEN
1773
1774 IF g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
1775
1776 IF (g_org_id <> '-111' AND g_party_id = '-111' AND g_collector_id = '-111'
1777 AND g_industry_id = '-111')
1778 THEN
1779 RETURN 'gid = 1271' ;
1780 ELSIF (g_org_id = '-111' AND g_party_id = '-111' AND g_collector_id = '-111'
1781 AND g_industry_id = '-111')
1782 THEN
1783 RETURN 'gid = 1271 ';
1784 ELSE
1785 RETURN 'gid = 1025' ;
1786 END IF;
1787
1788 ELSE
1789
1790 RETURN 'gid = 1025';
1791
1792 END IF;
1793
1794 ELSIF g_region_code IN ('FII_AR_REC_ACTIVITY_TREND',
1795 'FII_AR_REC_ACTIVITY',
1796 'FII_AR_COLL_EFFECTIVENESS',
1797 'FII_AR_UNAPP_RCT_TREND',
1798 'FII_AR_COLL_EFF_TREND') THEN
1799
1800 IF g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
1801
1802 IF (g_org_id <> '-111' AND g_party_id = '-111' AND g_collector_id = '-111'
1803 AND g_industry_id = '-111')
1804 THEN
1805 RETURN 'gid = 246' ;
1806 ELSIF (g_org_id = '-111' AND g_party_id = '-111' AND g_collector_id = '-111'
1807 AND g_industry_id = '-111')
1808 THEN RETURN 'gid = 246 ';
1809 ELSE
1810 RETURN 'gid = 0';
1811 END IF;
1812
1813 ELSE
1814
1815 RETURN 'gid = 0';
1816
1817 END IF;
1818
1819 ELSIF
1820 g_region_code IN ('FII_AR_RECEIVABLES_AGING') THEN
1821 IF g_party_id = '-111' AND g_collector_id = '-111' AND g_org_id <> '-111'
1822 THEN
1823
1824 RETURN 'gid = 1271';
1825 ELSIF g_party_id = '-111' AND g_collector_id = '-111' AND g_org_id = '-111'
1826 THEN RETURN 'gid=1271';
1827 ELSE
1828
1829 RETURN 'gid = 1025';
1830
1831 END IF;
1832
1833 ELSE --If the region code does not match
1834
1835 RETURN '1=1';
1836
1837 END IF;
1838
1839
1840 ELSIF g_cust_suffix = '_agrt' THEN
1841 IF g_region_code IN ('FII_AR_DISCOUNT_SUMMARY',
1842 'FII_AR_COLL_EFF_INDEX',
1843 'FII_AR_DSO',
1844 'FII_AR_DSO_TREND',
1845 'FII_AR_BILLING_ACTIVITY',
1846 'FII_AR_BILL_ACTIVITY_TABLE',
1847 'FII_AR_UNAPP_RCT_SUMMARY',
1848 'FII_AR_UNAPP_RCT_SUMM_TBL',
1849 'FII_AR_NET_REC_SUM',
1850 'FII_AR_CURR_REC_SUMMARY',
1851 'FII_AR_PDUE_REC_TREND',
1852 'FII_AR_RECEIVABLES_AGING',
1853 'FII_AR_PASTDUE_REC_AGING',
1854 'FII_AR_TOP_PDUE_CUSTOMER',
1855 'FII_AR_OPEN_REC_SUMMARY',
1856 'FII_AR_OPEN_REC_PDUE') THEN
1857
1858
1859 RETURN 'gid = 1025';
1860
1861 ELSIF g_region_code IN ('FII_AR_REC_ACTIVITY_TREND',
1862 'FII_AR_REC_ACTIVITY',
1863 'FII_AR_COLL_EFFECTIVENESS',
1864 'FII_AR_UNAPP_RCT_TREND',
1865 'FII_AR_COLL_EFF_TREND') THEN
1866
1867 RETURN 'gid = 0';
1868
1869 ELSE --If the region code does not match
1870
1871 RETURN '1=1';
1872
1873 END IF;
1874 END IF;
1875
1876
1877
1878 END get_mv_where_statement;
1879
1880 FUNCTION get_rct_mv_where_statement RETURN VARCHAR2 IS
1881 BEGIN
1882
1883 IF g_cust_suffix = '_base' THEN
1884
1885 IF g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
1886 IF (g_org_id <> '-111' AND g_party_id = '-111' AND g_collector_id = '-111'
1887 AND g_industry_id = '-111')
1888 THEN
1889 RETURN 'gid = 123' ;
1890 ELSIF (g_org_id = '-111' AND g_party_id = '-111' AND g_collector_id = '-111'
1891 AND g_industry_id = '-111') THEN
1892 RETURN 'gid = 123';
1893 ELSE
1894 RETURN 'gid = 0';
1895 END IF;
1896 ELSE
1897 RETURN 'gid = 0';
1898 END IF;
1899 ELSE
1900 RETURN '1=1';
1901 END IF;
1902
1903
1904 END get_rct_mv_where_statement;
1905
1906 PROCEDURE get_page_refresh_date IS
1907
1908 BEGIN
1909
1910 select nvl(last_refresh_date,sysdate) INTO g_page_refresh_date
1911 from bis_obj_properties
1912 where object_name = 'FII_AR_STATUS_DASHBOARD'
1913 and object_type = 'PAGE';
1914 EXCEPTION
1915 WHEN OTHERS THEN
1916 g_page_refresh_date := sysdate;
1917 END get_page_refresh_date;
1918
1919 END fii_ar_util_pkg;