[Home] [Help]
PACKAGE BODY: APPS.BIM_DBI_BGT_MGMT_PVT
Source
1 PACKAGE BODY BIM_DBI_BGT_MGMT_PVT AS
2 /* $Header: bimvbgtb.pls 120.6 2006/05/16 01:20:29 arvikuma noship $ */
3
4 l_prog_view CONSTANT varchar2(1) := fnd_profile.VALUE('BIM_VIEW_PROGRAM');
5 l_prog_cost CONSTANT varchar2(40) := fnd_profile.VALUE('BIM_PROG_COST');
6
7 PROCEDURE get_bim_page_params (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
8 l_as_of_date OUT NOCOPY DATE,
9 l_period_type in OUT NOCOPY VARCHAR2,
10 l_record_type_id OUT NOCOPY NUMBER,
11 l_comp_type OUT NOCOPY VARCHAR2,
12 l_country in OUT NOCOPY VARCHAR2,
13 l_view_by in OUT NOCOPY VARCHAR2,
14 l_cat_id in OUT NOCOPY VARCHAR2,
15 l_campaign_id in OUT NOCOPY VARCHAR2,
16 l_fund_id in OUT NOCOPY VARCHAR2,
17 l_bcat_id in OUT NOCOPY VARCHAR2,
18 l_curr in OUT NOCOPY VARCHAR2
19 )
20 IS
21
22 l_sql_errm VARCHAR2(32000);
23 BEGIN
24
25 IF (p_page_parameter_tbl.count > 0) THEN
26 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
27
28 /*INSERT INTO bim_param_test values(p_page_parameter_tbl(i).parameter_name,
29 p_page_parameter_tbl(i).parameter_value,
30 p_page_parameter_tbl(i).parameter_id);*/
31
32 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
33 l_period_type := p_page_parameter_tbl(i).parameter_value;
34 END IF;
35 IF p_page_parameter_tbl(i).parameter_name= 'AS_OF_DATE' THEN
36 l_as_of_date := sysdate;
37 END IF;
38
39 IF p_page_parameter_tbl(i).parameter_name= 'TIME_COMPARISON_TYPE' THEN
40 l_comp_type := p_page_parameter_tbl(i).parameter_value;
41 END IF;
42
43 IF( p_page_parameter_tbl(i).parameter_name= 'VIEW_BY') THEN
44 l_view_by := p_page_parameter_tbl(i).parameter_value;
45 if l_view_by is null then l_view_by := 'CAMPAIGN+CAMPAIGN';
46 end if;
47 END IF;
48
49 IF ( p_page_parameter_tbl(i).parameter_name= 'ITEM+ENI_ITEM_VBH_CAT') THEN
50 l_cat_id := p_page_parameter_tbl(i).parameter_id;
51 END IF;
52
53 IF ( p_page_parameter_tbl(i).parameter_name= 'CAMPAIGN+CAMPAIGN') THEN
54 l_campaign_id := p_page_parameter_tbl(i).parameter_id;
55 END IF;
56 IF ( p_page_parameter_tbl(i).parameter_name= 'BIM_MARK_BUDGET+BIM_BUDGET_NAME') THEN
57 l_fund_id := p_page_parameter_tbl(i).parameter_id;
58 END IF;
59 IF ( p_page_parameter_tbl(i).parameter_name= 'BIM_MARK_BUDGET+BIM_BUDGET_CATEGORY') THEN
60 l_bcat_id := p_page_parameter_tbl(i).parameter_id;
61 END IF;
62 IF ( p_page_parameter_tbl(i).parameter_name= 'CURRENCY+FII_CURRENCIES') THEN
63 l_curr := p_page_parameter_tbl(i).parameter_id;
64 END IF;
65 IF p_page_parameter_tbl(i).parameter_name= 'GEOGRAPHY+COUNTRY' THEN
66 l_country := p_page_parameter_tbl(i).parameter_id;
67 IF (l_country = '''ALL''')
68 THEN l_country := 'N';
69 END IF;
70
71 IF (l_country IS NULL)
72 THEN l_country := 'N';
73 END IF;
74 IF (instr(l_country,'''') >=0) THEN
75 l_country := replace(l_country, '''','');
76 END IF;
77 END IF;
78
79 END LOOP;
80 END IF;
81
82 IF l_comp_type IS NULL THEN l_comp_type := 'YEARLY'; END IF;
83
84 IF l_period_type IS NULL THEN l_period_type := 'FII_TIME_WEEK'; END IF;
85
86 IF l_country IS NULL THEN l_country := 'N'; END IF;
87
88 -- Retrieve l_period_type info using CASE
89
90 CASE l_period_type
91 WHEN 'FII_TIME_WEEK' THEN l_record_type_id := 11;
92 WHEN 'FII_TIME_ENT_PERIOD' THEN l_record_type_id := 23;
93 WHEN 'FII_TIME_ENT_QTR' THEN l_record_type_id := 55;
94 WHEN 'FII_TIME_ENT_YEAR' THEN l_record_type_id := 119;
95 ELSE l_record_type_id := 11;
96 END CASE;
97
98 /*INSERT INTO bim_param_test values('get_bim_page_params success',
99 nvl(l_comp_type,'NULL'),nvl(l_period_type,'NULL'),
100 DBMS_UTILITY.get_time,l_country,NULL,null);
101 COMMIT;
102 */
103 EXCEPTION
104 WHEN OTHERS THEN
105 l_sql_errm := SQLERRM;
106 /*INSERT INTO bim_param_test values('get_bim_page_params excpetion',
107 nvl(l_comp_type,'NULL'),nvl(l_period_type,'NULL'),
108 DBMS_UTILITY.get_time,l_country,l_sql_errm,null);
109 COMMIT;
110 */
111 END get_bim_page_params;
112 PROCEDURE write_debug(p_param_name varchar2, p_param_id varchar2 ,
113 p_param_value varchar2 ,
114 p_query varchar2 := NULL, p_dimension varchar2 := NULL,
115 p_period_date date := null)
116 IS
117 BEGIN
118
119 --INSERT INTO BIM_PARAM_TEST values(p_param_name,p_param_id,
120 -- p_param_value,DBMS_UTILITY.get_time,p_query,p_dimension,p_period_date);
121 --COMMIT;
122 NULL;
123 END;
124
125 FUNCTION GET_RESOURCE_ID return NUMBER IS
126 l_resource_id NUMBER := NULL;
127 CURSOR c_rid IS
128 SELECT resource_id
129 FROM JTF_RS_RESOURCE_EXTNS
130 WHERE user_id = FND_GLOBAL.user_id;
131 BEGIN
132 OPEN c_rid;
133 FETCH c_rid INTO l_resource_id;
134 CLOSE c_rid;
135 if (l_resource_id=null) then
136 l_resource_id := -1;
137 end if;
138 if (l_resource_id='') then
139 l_resource_id := -1;
140 end if;
141
142 return l_resource_id;
143 END GET_RESOURCE_ID;
144
145 FUNCTION GET_DIM RETURN VARCHAR2 IS
146 period_id NUMBER;
147 BEGIN
148 period_id := -1;
149 return '&AS_OF_DATE='||TO_CHAR(TRUNC(sysdate),'DD-MON-YYYY')||
150 '&BIM_DIM5='||'TIME_COMPARISON_TYPE+YEARLY'||
151 '&BIM_DIM2_FROM='||period_id||'&BIM_DIM2_TO='||period_id||
152 '&BIM_DIM7=All&BIM_DIM8=All&VIEW_BY=CAMPAIGN+CAMPAIGN' ;
153
154 END GET_DIM;
155
156 FUNCTION GET_ADMIN_STATUS return VARCHAR2 IS
157 l_admin_count NUMBER := 0;
158 l_admin_flag varchar2(20);
159 CURSOR c_rid IS
160 SELECT count(*)
161 FROM JTF_RS_RESOURCE_EXTNS r, bim_i_admin_group a
162 WHERE user_id = FND_GLOBAL.user_id
163 AND r.resource_id = a.resource_id;
164
165 BEGIN
166 If GET_RESOURCE_ID is null
167 then
168 l_admin_flag := 'Y';
169
170 else
171
172 OPEN c_rid;
173 FETCH c_rid INTO l_admin_count;
174 CLOSE c_rid;
175 if (l_admin_count<=0)
176 then
177 l_admin_flag := 'N';
178 else
179 l_admin_flag := 'Y';
180 end if;
181 end if;
182 return l_admin_flag;
183 END GET_ADMIN_STATUS;
184
185 PROCEDURE GET_BGT_SUM_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
186 x_custom_sql OUT NOCOPY VARCHAR2,
187 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
188 IS
189 l_sqltext varchar2(12000);
190 iFlag number;
191 l_period_type_hc number;
192 l_as_of_date DATE;
193 l_period_type varchar2(2000);
194 l_record_type_id NUMBER;
195 l_comp_type varchar2(2000);
196 l_country varchar2(4000);
197 l_view_by varchar2(4000);
198 l_sql_errm varchar2(4000);
199 l_previous_report_start_date DATE;
200 l_current_report_start_date DATE;
201 l_previous_as_of_date DATE;
202 l_period_type_id NUMBER;
203 l_user_id NUMBER;
204 l_resource_id NUMBER;
205 l_time_id_column VARCHAR2(1000);
206 l_admin_status VARCHAR2(20);
207 l_admin_flag VARCHAR2(1);
208 l_admin_count Number;
209 l_rsid NUMBER;
210 l_curr_aod_str varchar2(80);
211 l_country_clause varchar2(4000);
212 l_access_clause varchar2(4000);
213 l_access_table varchar2(4000);
214 --l_cat_id NUMBER;
215 l_campaign_id VARCHAR2(50);
216 l_cat_id VARCHAR2(50):=NULL;
217 l_custom_rec BIS_QUERY_ATTRIBUTES;
218 l_fund_id VARCHAR2(50);
219 l_bcat_id VARCHAR2(50);
220 l_curr VARCHAR2(50);
221 l_curr_suffix VARCHAR2(50);
222 l_url_str VARCHAR2(1000);
223 BEGIN
224 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
225 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
226 get_bim_page_params(p_page_parameter_tbl,
227 l_as_of_date,
228 l_period_type,
229 l_record_type_id,
230 l_comp_type,
231 l_country,
232 l_view_by,
233 l_cat_id,
234 l_campaign_id,
235 l_fund_id,
236 l_bcat_id,
237 l_curr
238 );
239
240 --l_curr_aod_str := 'to_date('||to_char(l_as_of_date,'J')||',''J'')';
241 IF (l_curr = '''FII_GLOBAL1''')
242 THEN l_curr_suffix := '';
243 ELSIF (l_curr = '''FII_GLOBAL2''')
244 THEN l_curr_suffix := '_s';
245 ELSE l_curr_suffix := '';
246 END IF;
247 l_admin_status := GET_ADMIN_STATUS;
248
249 l_url_str :='pFunctionName=BIM_I_BGT_SUM_PHP&pParamIds=Y&VIEW_BY='||l_view_by||'&VIEW_BY_NAME=VIEW_BY_ID';
250 --budget name
251 IF l_fund_id is null then
252 l_sqltext :=
253 'SELECT name VIEWBY, viewbyid,
254 fund_type BIM_ATTRIBUTE2,
255 fund_category BIM_ATTRIBUTE20,
256 original_budget BIM_ATTRIBUTE3,
257 pre_balance BIM_ATTRIBUTE4,
258 transfer_in BIM_ATTRIBUTE5,
259 transfer_out BIM_ATTRIBUTE6,
260 holdback BIM_ATTRIBUTE9,
261 accrual BIM_ATTRIBUTE7,
262 committed BIM_ATTRIBUTE8,
263 cur_balance BIM_ATTRIBUTE10,
264 planned BIM_ATTRIBUTE11,
265 utilized BIM_ATTRIBUTE12,
266 decode(viewbyid,null,NULL,'||''''||l_url_str||''''||' ) bim_url1,
267 sum(original_budget) over() BIM_GRAND_TOTAL1,
268 sum(pre_balance) over() BIM_GRAND_TOTAL2,
269 sum(transfer_in) over() BIM_GRAND_TOTAL3,
270 sum(transfer_out) over() BIM_GRAND_TOTAL4,
271 sum(holdback) over() BIM_GRAND_TOTAL7,
272 sum(accrual) over() BIM_GRAND_TOTAL5,
273 sum(committed) over() BIM_GRAND_TOTAL6,
274 sum(cur_balance) over() BIM_GRAND_TOTAL8,
275 sum(planned) over() BIM_GRAND_TOTAL9,
276 sum(utilized) over() BIM_GRAND_TOTAL10
277 FROM
278 (
279 SELECT
280 VIEWBYID,
281 e.short_name name,
282 l.meaning fund_type,
283 cat.category_name fund_category,
284 sum(original_budget) original_budget,
285 sum(pre_balance) pre_balance,
286 sum(transfer_in) transfer_in,
287 sum(transfer_out) transfer_out,
288 sum(holdback) holdback,
289 sum(accrual)accrual,
290 sum(committed) committed,
291 sum(cur_balance) cur_balance,
292 sum(planned) planned,
293 sum(utilized) utilized
294 FROM
295 ( SELECT
296 decode(a.leaf_node_flag,''Y'',null,a.fund_id) VIEWBYID,
297 a.fund_id fund_id,
298 a.fund_type fund_type,
299 a.category_id category_id,
300 0 original_budget,
301 0 pre_balance,
302 sum(transfer_in'||l_curr_suffix||') transfer_in,
303 sum(transfer_out'||l_curr_suffix||') transfer_out,
304 sum(holdback'||l_curr_suffix||') holdback,
305 sum(accrual'||l_curr_suffix||')accrual,
306 sum(committed'||l_curr_suffix||') committed,
307 0 cur_balance,
308 0 planned,
309 sum(utilized'||l_curr_suffix||') utilized
310 FROM BIM_I_BGT_LVL_MV a,
311 fii_time_rpt_struct_v cal';
312 IF l_admin_status = 'N' THEN
313 l_sqltext := l_sqltext ||
314 ' , ams_act_access_denorm b';
315 END IF;
316 l_sqltext := l_sqltext ||
317 ' WHERE a.time_id = cal.time_id
318 AND a.period_type_id = cal.period_type_id
319 AND a.parent_fund_id is null';
320 IF l_admin_status = 'N' THEN
321 l_sqltext := l_sqltext ||
322 ' AND b.resource_id = :l_resource_id
323 AND b.object_type=''FUND''
324 AND a.fund_id =b.object_id';
325 END IF;
326 l_sqltext := l_sqltext ||
327 ' AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id';
328 l_sqltext := l_sqltext ||
329 ' AND cal.report_date = &BIS_CURRENT_ASOF_DATE
330 AND cal.calendar_id=-1
331 GROUP BY a.fund_id,decode(a.leaf_node_flag,''Y'',null,a.fund_id),a.fund_type,a.category_id
332 UNION ALL
333 SELECT
334 decode(a.leaf_node_flag,''Y'',null,a.fund_id) VIEWBYID,
335 a.fund_id fund_id,
336 a.fund_type fund_type,
337 a.category_id category_id,
338 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.original_budget'||l_curr_suffix||',0)) original_budget,
339 sum(decode(cal.report_date,&BIS_CURRENT_EFFECTIVE_START_DATE - 1,
340 a.original_budget'||l_curr_suffix||'+a.accrual'||l_curr_suffix||'+a.transfer_in'||l_curr_suffix||'-a.transfer_out'||l_curr_suffix||'-a.holdback'||l_curr_suffix||'-a.committed'||l_curr_suffix||',0)) pre_balance,
341 0 transfer_in,
342 0 transfer_out,
343 0 holdback,
344 0 accrual,
345 0 committed,
346 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
347 a.original_budget'||l_curr_suffix||'+a.accrual'||l_curr_suffix||'+a.transfer_in'||l_curr_suffix||'-a.transfer_out'||l_curr_suffix||'-a.holdback'||l_curr_suffix||'-a.committed'||l_curr_suffix||',0)) cur_balance,
348 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.planned'||l_curr_suffix||'-a.committed'||l_curr_suffix||',0)) planned,
349 0 utilized
350 FROM BIM_I_BGT_LVL_MV a,
351 fii_time_rpt_struct_v cal
352 ';
353 IF l_admin_status = 'N' THEN
354 l_sqltext := l_sqltext ||
355 ' , ams_act_access_denorm b';
356 END IF;
357 l_sqltext := l_sqltext ||
358 ' WHERE a.time_id = cal.time_id
359 AND a.period_type_id = cal.period_type_id
360 AND a.parent_fund_id is null';
361 IF l_admin_status = 'N' THEN
362 l_sqltext := l_sqltext ||
363 ' AND b.resource_id = :l_resource_id
364 AND b.object_type=''FUND''
365 AND a.fund_id =b.object_id';
366 END IF;
367 l_sqltext := l_sqltext ||
368 ' AND BITAND(cal.record_type_id,1143)= cal.record_type_id';
369 l_sqltext := l_sqltext ||
370 ' AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_CURRENT_EFFECTIVE_START_DATE - 1)
371 AND cal.calendar_id=-1
372 GROUP BY a.fund_id,decode(a.leaf_node_flag,''Y'',null,a.fund_id),a.fund_type,a.category_id
373 )inner,
374 ozf_funds_all_tl e,
375 ams_categories_tl cat,
376 ozf_lookups l
377 WHERE e.fund_id = inner.fund_id
378 AND e.language =USERENV(''LANG'')
379 AND l.lookup_type=''OZF_FUND_TYPE''
380 AND l.lookup_code=inner.fund_type
381 AND cat.category_id = inner.category_id
382 AND cat.language=USERENV(''LANG'')
383 GROUP BY e.short_name,l.meaning,cat.category_name,VIEWBYID
384 HAVING
385 sum(original_budget) +sum(accrual)>0
386 or ( sum(pre_balance) >0
387 or sum(transfer_in) >0
388 or sum(transfer_out) >0
389 or sum(holdback)>0
390 or sum(accrual)>0
391 or sum(committed) >0
392 or sum(cur_balance) >0
393 or sum(planned) >0
394 or sum(utilized)>0)
395 )
396 &ORDER_BY_CLAUSE';
397 ELSE --budget_name not null
398 l_sqltext :=
399 'SELECT name VIEWBY,
400 viewbyid,
401 fund_type BIM_ATTRIBUTE2,
402 fund_category BIM_ATTRIBUTE20,
403 original_budget BIM_ATTRIBUTE3,
404 pre_balance BIM_ATTRIBUTE4,
405 transfer_in BIM_ATTRIBUTE5,
406 transfer_out BIM_ATTRIBUTE6,
407 holdback BIM_ATTRIBUTE9,
408 accrual BIM_ATTRIBUTE7,
409 committed BIM_ATTRIBUTE8,
410 cur_balance BIM_ATTRIBUTE10,
411 planned BIM_ATTRIBUTE11,
412 utilized BIM_ATTRIBUTE12,
413 decode(viewbyid,null,NULL,'||''''||l_url_str||''''||' ) bim_url1,
414 sum(original_budget) over() BIM_GRAND_TOTAL1,
415 sum(pre_balance) over() BIM_GRAND_TOTAL2,
416 sum(transfer_in) over() BIM_GRAND_TOTAL3,
417 sum(transfer_out) over() BIM_GRAND_TOTAL4,
418 sum(holdback) over() BIM_GRAND_TOTAL7,
419 sum(accrual) over() BIM_GRAND_TOTAL5,
420 sum(committed) over() BIM_GRAND_TOTAL6,
421 sum(cur_balance) over() BIM_GRAND_TOTAL8,
422 sum(planned) over() BIM_GRAND_TOTAL9,
423 sum(utilized) over() BIM_GRAND_TOTAL10
424 FROM
425 (
426 SELECT
430 cat.category_name fund_category,
427 VIEWBYID,
428 e.short_name name,
429 l.meaning fund_type,
431 sum(original_budget) original_budget,
432 sum(pre_balance) pre_balance,
433 sum(transfer_in) transfer_in,
434 sum(transfer_out) transfer_out,
435 sum(holdback) holdback,
436 sum(accrual)accrual,
437 sum(committed) committed,
438 sum(cur_balance) cur_balance,
439 sum(planned) planned,
440 sum(utilized) utilized
441 FROM
442 ( SELECT
443 decode(a.leaf_node_flag,''Y'',null,a.fund_id) VIEWBYID,
444 a.fund_id fund_id,
445 a.fund_type fund_type,
446 a.category_id category_id,
447 0 original_budget,
448 0 pre_balance,
449 sum(transfer_in'||l_curr_suffix||') transfer_in,
450 sum(transfer_out'||l_curr_suffix||') transfer_out,
451 sum(holdback'||l_curr_suffix||') holdback,
452 sum(accrual'||l_curr_suffix||')accrual,
453 sum(committed'||l_curr_suffix||') committed,
454 0 cur_balance,
455 0 planned,
456 sum(utilized'||l_curr_suffix||') utilized
457 FROM BIM_I_BGT_LVL_MV a,
458 fii_time_rpt_struct_v cal';
459 IF l_admin_status = 'N' THEN
460 l_sqltext := l_sqltext ||
461 ' , ams_act_access_denorm b';
462 END IF;
463 l_sqltext := l_sqltext ||
464 ' WHERE a.time_id = cal.time_id
465 AND a.period_type_id = cal.period_type_id
466 AND a.parent_fund_id =&BIM_MARK_BUDGET+BIM_BUDGET_NAME
467 ';
468 IF l_admin_status = 'N' THEN
469 l_sqltext := l_sqltext ||
470 ' AND b.resource_id = :l_resource_id
471 AND b.object_type=''FUND''
472 AND a.fund_id =b.object_id';
473 END IF;
474 l_sqltext := l_sqltext ||
475 ' AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id';
476 l_sqltext := l_sqltext ||
477 ' AND cal.report_date = &BIS_CURRENT_ASOF_DATE
478 AND cal.calendar_id=-1
479 GROUP BY a.fund_id,decode(a.leaf_node_flag,''Y'',null,a.fund_id),a.fund_type,a.category_id
480 UNION ALL
481 SELECT
482 decode(a.leaf_node_flag,''Y'',null,a.fund_id) VIEWBYID,
483 a.fund_id fund_id,
484 a.fund_type fund_type,
485 a.category_id category_id,
486 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.original_budget'||l_curr_suffix||',0)) original_budget,
487 sum(decode(cal.report_date,&BIS_CURRENT_EFFECTIVE_START_DATE - 1,
488 a.original_budget'||l_curr_suffix||'+a.accrual'||l_curr_suffix||'+a.transfer_in'||l_curr_suffix||'
489 -a.transfer_out'||l_curr_suffix||'-a.holdback'||l_curr_suffix||'-a.committed'||l_curr_suffix||',0)) pre_balance,
490 0 transfer_in,
491 0 transfer_out,
492 0 holdback,
493 0 accrual,
494 0 committed,
495 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
496 a.original_budget'||l_curr_suffix||'+a.accrual'||l_curr_suffix||'+a.transfer_in'||l_curr_suffix||'-a.transfer_out'||l_curr_suffix||'
497 -a.holdback'||l_curr_suffix||'-a.committed'||l_curr_suffix||',0)) cur_balance,
498 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.planned'||l_curr_suffix||'-a.committed'||l_curr_suffix||',0)) planned,
499 0 utilized
500 FROM BIM_I_BGT_LVL_MV a,
501 fii_time_rpt_struct_v cal';
502 IF l_admin_status = 'N' THEN
503 l_sqltext := l_sqltext ||
504 ' , ams_act_access_denorm b';
505 END IF;
506 l_sqltext := l_sqltext ||
507 ' WHERE a.time_id = cal.time_id
508 AND a.period_type_id = cal.period_type_id
509 AND a.parent_fund_id =&BIM_MARK_BUDGET+BIM_BUDGET_NAME
510 ';
511 IF l_admin_status = 'N' THEN
512 l_sqltext := l_sqltext ||
513 ' AND b.resource_id = :l_resource_id
514 AND b.object_type=''FUND''
515 AND a.fund_id =b.object_id';
516 END IF;
517 l_sqltext := l_sqltext ||
518 ' AND BITAND(cal.record_type_id,1143)= cal.record_type_id';
519 l_sqltext := l_sqltext ||
520 ' AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_CURRENT_EFFECTIVE_START_DATE - 1)
521 AND cal.calendar_id=-1
522 GROUP BY a.fund_id,decode(a.leaf_node_flag,''Y'',null,a.fund_id),a.fund_type,a.category_id
523 )inner,
524 ozf_funds_all_tl e,
525 ams_categories_tl cat,
526 ozf_lookups l
527 WHERE e.fund_id = inner.fund_id
528 AND e.language =USERENV(''LANG'')
529 AND l.lookup_type=''OZF_FUND_TYPE''
530 AND l.lookup_code=inner.fund_type
531 AND cat.category_id = inner.category_id
532 AND cat.language=USERENV(''LANG'')
533 GROUP BY e.short_name,l.meaning,cat.category_name,VIEWBYID
534 HAVING
535 sum(original_budget)+sum(accrual) >0
536 or (sum(pre_balance) >0
537 or sum(transfer_in) >0
538 or sum(transfer_out) >0
539 or sum(holdback)>0
540 or sum(accrual)>0
541 or sum(committed) >0
542 or sum(cur_balance) >0
543 or sum(planned) >0
544 or sum(utilized)>0)
545 )
546 &ORDER_BY_CLAUSE';
547 END IF;--end of budget name
548
549 /*IF (l_view_by = 'CAMPAIGN+CAMPAIGN') THEN
550 end if; */--end of campaign
551 /*
552 IF (l_view_by = 'GEOGRAPHY+COUNTRY') THEN
553 END IF;
554 IF (l_view_by = 'MEDIA+MEDIA') THEN
555 END IF;
556 */
557 x_custom_sql := l_sqltext;
558 l_custom_rec.attribute_name := ':l_record_type';
559 l_custom_rec.attribute_value := l_record_type_id;
560 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
561 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
562 x_custom_output.EXTEND;
563 x_custom_output(1) := l_custom_rec;
564
565 l_custom_rec.attribute_name := ':l_resource_id';
569 x_custom_output.EXTEND;
566 l_custom_rec.attribute_value := GET_RESOURCE_ID;
567 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
568 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
570 x_custom_output(2) := l_custom_rec;
571
572 l_custom_rec.attribute_name := ':l_admin_flag';
573 l_custom_rec.attribute_value := GET_ADMIN_STATUS;
574 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
575 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
576 x_custom_output.EXTEND;
577 x_custom_output(3) := l_custom_rec;
578
579 l_custom_rec.attribute_name := ':l_country';
580 l_custom_rec.attribute_value := l_country;
581 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
582 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
583 x_custom_output.EXTEND;
584 x_custom_output(4) := l_custom_rec;
585
586 l_custom_rec.attribute_name := ':l_fund_id';
587 l_custom_rec.attribute_value := l_fund_id;
588 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
589 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
590 x_custom_output.EXTEND;
591 x_custom_output(5) := l_custom_rec;
592
593 /* l_custom_rec.attribute_name := ':l_campaign_id';
594 l_custom_rec.attribute_value := l_campaign_id;
595 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
596 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
597 x_custom_output.EXTEND;
598 x_custom_output(6) := l_custom_rec;*/
599
600 write_debug('GET_BGT_SUM_SQL','QUERY','_',l_sqltext);
601 --return l_sqltext;
602 --INSERT INTO bim_test_sql values(l_view_by,l_sqltext);
603 EXCEPTION
604 WHEN others THEN
605 l_sql_errm := SQLERRM;
606 write_debug('GET_BGT_SUM_SQL','ERROR',l_sql_errm,l_sqltext);
607 END GET_BGT_SUM_SQL;
608
609 PROCEDURE GET_BGT_CAT_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
610 x_custom_sql OUT NOCOPY VARCHAR2,
611 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
612 IS
613 l_sqltext varchar2(12000);
614 iFlag number;
615 l_period_type_hc number;
616 l_as_of_date DATE;
617 l_period_type varchar2(2000);
618 l_record_type_id NUMBER;
619 l_comp_type varchar2(2000);
620 l_country varchar2(4000);
621 l_view_by varchar2(4000);
622 l_sql_errm varchar2(4000);
623 l_previous_report_start_date DATE;
624 l_current_report_start_date DATE;
625 l_previous_as_of_date DATE;
626 l_period_type_id NUMBER;
627 l_user_id NUMBER;
628 l_resource_id NUMBER;
629 l_time_id_column VARCHAR2(1000);
630 l_admin_status VARCHAR2(20);
631 l_admin_flag VARCHAR2(1);
632 l_admin_count Number;
633 l_rsid NUMBER;
634 l_curr_aod_str varchar2(80);
635 l_country_clause varchar2(4000);
636 l_access_clause varchar2(4000);
637 l_access_table varchar2(4000);
638 --l_cat_id NUMBER;
639 l_campaign_id VARCHAR2(50);
640 l_cat_id VARCHAR2(50):=NULL;
641 l_custom_rec BIS_QUERY_ATTRIBUTES;
642 l_fund_id VARCHAR2(50);
643 l_bcat_id VARCHAR2(50);
644 l_curr VARCHAR2(50);
645 l_curr_suffix VARCHAR2(50);
646 l_url_str VARCHAR2(1000);
647 BEGIN
648 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
649 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
650 get_bim_page_params(p_page_parameter_tbl,
651 l_as_of_date,
652 l_period_type,
653 l_record_type_id,
654 l_comp_type,
655 l_country,
656 l_view_by,
657 l_cat_id,
658 l_campaign_id,
659 l_fund_id,
660 l_bcat_id,
661 l_curr
662 );
663 IF (l_curr = '''FII_GLOBAL1''')
664 THEN l_curr_suffix := '';
665 ELSIF (l_curr = '''FII_GLOBAL2''')
666 THEN l_curr_suffix := '_s';
667 ELSE l_curr_suffix := '';
668 END IF;
669 --l_curr_aod_str := 'to_date('||to_char(l_as_of_date,'J')||',''J'')';
670
671 l_admin_status := GET_ADMIN_STATUS;
672
673 l_url_str :='pFunctionName=BIM_I_BGT_CAT_PHP&pParamIds=Y&VIEW_BY='||l_view_by||'&VIEW_BY_NAME=VIEW_BY_ID';
674 IF (l_bcat_id is not null) then
675 l_sqltext :=
676 'SELECT name VIEWBY,
677 VIEWBYID,
678 original_budget BIM_ATTRIBUTE3,
679 pre_balance BIM_ATTRIBUTE4,
680 transfer_in BIM_ATTRIBUTE5,
681 transfer_out BIM_ATTRIBUTE6,
682 holdback BIM_ATTRIBUTE9,
683 accrual BIM_ATTRIBUTE7,
684 committed BIM_ATTRIBUTE8,
685 cur_balance BIM_ATTRIBUTE10,
686 planned BIM_ATTRIBUTE11,
687 utilized BIM_ATTRIBUTE12,
688 decode(viewbyid,null,NULL,'||''''||l_url_str||''''||' ) bim_url1,
689 sum(original_budget) over() BIM_GRAND_TOTAL1,
690 sum(pre_balance) over() BIM_GRAND_TOTAL2,
691 sum(transfer_in) over() BIM_GRAND_TOTAL3,
692 sum(transfer_out) over() BIM_GRAND_TOTAL4,
693 sum(holdback) over() BIM_GRAND_TOTAL7,
697 sum(planned) over() BIM_GRAND_TOTAL9,
694 sum(accrual) over() BIM_GRAND_TOTAL5,
695 sum(committed) over() BIM_GRAND_TOTAL6,
696 sum(cur_balance) over() BIM_GRAND_TOTAL8,
698 sum(utilized) over() BIM_GRAND_TOTAL10
699 FROM
700 (
701 SELECT
702 VIEWBYID,
703 e.category_name name,
704 sum(original_budget) original_budget,
705 sum(pre_balance) pre_balance,
706 sum(transfer_in) transfer_in,
707 sum(transfer_out) transfer_out,
708 sum(holdback) holdback,
709 sum(accrual)accrual,
710 sum(committed) committed,
711 sum(cur_balance) cur_balance,
712 sum(planned) planned,
713 sum(utilized) utilized
714 FROM
715 ( SELECT
716 decode(a.leaf_node_flag,''Y'',null,a.category_id) VIEWBYID,
717 a.category_id category_id,
718 0 original_budget,
719 0 pre_balance,
720 sum(transfer_in'||l_curr_suffix||') transfer_in,
721 sum(transfer_out'||l_curr_suffix||') transfer_out,
722 sum(holdback'||l_curr_suffix||') holdback,
723 sum(accrual'||l_curr_suffix||')accrual,
724 sum(committed'||l_curr_suffix||') committed,
725 0 cur_balance,
726 0 planned,
727 sum(utilized) utilized
728 FROM BIM_I_BGT_CAT_MV a,
729 fii_time_rpt_struct_v cal';
730 IF l_admin_status = 'N' THEN
731 l_sqltext := l_sqltext ||
732 ' , ams_act_access_denorm b';
733 END IF;
734 l_sqltext := l_sqltext ||
735 ' WHERE a.time_id = cal.time_id
736 AND a.period_type_id = cal.period_type_id
737 AND a.parent_category_id =&BIM_MARK_BUDGET+BIM_BUDGET_CATEGORY
738 ';
739 IF l_admin_status = 'N' THEN
740 l_sqltext := l_sqltext ||
741 ' AND b.resource_id = :l_resource_id
742 AND b.object_type=''FUND''
743 AND a.fund_id =b.object_id';
744 END IF;
745 l_sqltext := l_sqltext ||
746 ' AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id';
747 l_sqltext := l_sqltext ||
748 ' AND cal.report_date = &BIS_CURRENT_ASOF_DATE
749 AND cal.calendar_id=-1
750 GROUP BY decode(a.leaf_node_flag,''Y'',null,a.category_id),a.category_id
751 UNION ALL
752 SELECT
753 decode(a.leaf_node_flag,''Y'',null,a.category_id) VIEWBYID,
754 a.category_id category_id,
755 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.original_budget'||l_curr_suffix||',0)) original_budget,
756 sum(decode(cal.report_date,&BIS_CURRENT_EFFECTIVE_START_DATE - 1,
757 a.original_budget'||l_curr_suffix||'+a.accrual'||l_curr_suffix||'+a.transfer_in'||l_curr_suffix||'
758 -a.transfer_out'||l_curr_suffix||'-a.holdback'||l_curr_suffix||'-a.committed'||l_curr_suffix||',0)) pre_balance,
759 0 transfer_in,
760 0 transfer_out,
761 0 holdback,
762 0 accrual,
763 0 committed,
764 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.original_budget'||l_curr_suffix||'
765 +a.accrual'||l_curr_suffix||'+a.transfer_in'||l_curr_suffix||'-a.transfer_out'||l_curr_suffix||'-a.holdback'||l_curr_suffix||'-a.committed'||l_curr_suffix||',0)) cur_balance,
766 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.planned'||l_curr_suffix||'-a.committed'||l_curr_suffix||',0)) planned,
767 0 utilized
768 FROM BIM_I_BGT_CAT_MV a,
769 fii_time_rpt_struct_v cal';
770 IF l_admin_status = 'N' THEN
771 l_sqltext := l_sqltext ||
772 ' , ams_act_access_denorm b';
773 END IF;
774 l_sqltext := l_sqltext ||
775 ' WHERE a.time_id = cal.time_id
776 AND a.period_type_id = cal.period_type_id
777 AND a.parent_category_id =&BIM_MARK_BUDGET+BIM_BUDGET_CATEGORY
778 ';
779 IF l_admin_status = 'N' THEN
780 l_sqltext := l_sqltext ||
781 ' AND b.resource_id = :l_resource_id
782 AND b.object_type=''FUND''
783 AND a.fund_id =b.object_id';
784 END IF;
785 l_sqltext := l_sqltext ||
786 ' AND BITAND(cal.record_type_id,1143)= cal.record_type_id';
787 l_sqltext := l_sqltext ||
788 ' AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_CURRENT_EFFECTIVE_START_DATE - 1)
789 AND cal.calendar_id=-1
790 GROUP BY decode(a.leaf_node_flag,''Y'',null,a.category_id),a.category_id
791 ) inner, ams_categories_tl e
792 WHERE inner.category_id=e.category_id
793 AND e.language =USERENV(''LANG'')
794 GROUP BY e.category_name,VIEWBYID
795 HAVING
796 sum(original_budget) >0
797 or sum(pre_balance) >0
798 or sum(transfer_in) >0
799 or sum(transfer_out) >0
800 or sum(holdback) >0
801 or sum(accrual)>0
802 or sum(committed) >0
803 or sum(cur_balance) >0
804 or sum(planned) >0
805 or sum(utilized)>0
806 )
807 &ORDER_BY_CLAUSE';
808 ELSE --
809 l_sqltext :=
810 'SELECT name VIEWBY,
811 VIEWBYID,
812 original_budget BIM_ATTRIBUTE3,
813 pre_balance BIM_ATTRIBUTE4,
814 transfer_in BIM_ATTRIBUTE5,
815 transfer_out BIM_ATTRIBUTE6,
816 holdback BIM_ATTRIBUTE9,
817 accrual BIM_ATTRIBUTE7,
818 committed BIM_ATTRIBUTE8,
819 cur_balance BIM_ATTRIBUTE10,
820 planned BIM_ATTRIBUTE11,
821 utilized BIM_ATTRIBUTE12,
822 decode(viewbyid,null,NULL,'||''''||l_url_str||''''||' ) bim_url1,
823 sum(original_budget) over() BIM_GRAND_TOTAL1,
824 sum(pre_balance) over() BIM_GRAND_TOTAL2,
825 sum(transfer_in) over() BIM_GRAND_TOTAL3,
826 sum(transfer_out) over() BIM_GRAND_TOTAL4,
827 sum(holdback) over() BIM_GRAND_TOTAL7,
828 sum(accrual) over() BIM_GRAND_TOTAL5,
829 sum(committed) over() BIM_GRAND_TOTAL6,
830 sum(cur_balance) over() BIM_GRAND_TOTAL8,
831 sum(planned) over() BIM_GRAND_TOTAL9,
832 sum(utilized) over() BIM_GRAND_TOTAL10
833 FROM
834 (
835 SELECT
836 VIEWBYID,
837 e.category_name name,
838 sum(original_budget) original_budget,
839 sum(pre_balance) pre_balance,
840 sum(transfer_in) transfer_in,
841 sum(transfer_out) transfer_out,
845 sum(cur_balance) cur_balance,
842 sum(holdback) holdback,
843 sum(accrual)accrual,
844 sum(committed) committed,
846 sum(planned) planned,
847 sum(utilized) utilized
848 FROM
849 ( SELECT
850 decode(a.leaf_node_flag,''Y'',null,a.category_id) VIEWBYID,
851 a.category_id category_id,
852 0 original_budget,
853 0 pre_balance,
854 sum(transfer_in'||l_curr_suffix||') transfer_in,
855 sum(transfer_out'||l_curr_suffix||') transfer_out,
856 sum(holdback'||l_curr_suffix||') holdback,
857 sum(accrual'||l_curr_suffix||')accrual,
858 sum(committed'||l_curr_suffix||') committed,
859 0 cur_balance,
860 0 planned,
861 sum(utilized'||l_curr_suffix||') utilized
862 FROM BIM_I_BGT_CAT_MV a,
863 fii_time_rpt_struct_v cal';
864 IF l_admin_status = 'N' THEN
865 l_sqltext := l_sqltext ||
866 ' , ams_act_access_denorm b';
867 END IF;
868 l_sqltext := l_sqltext ||
869 ' WHERE a.time_id = cal.time_id
870 AND a.period_type_id = cal.period_type_id
871 AND a.parent_category_id is null
872 ';
873 IF l_admin_status = 'N' THEN
874 l_sqltext := l_sqltext ||
875 ' AND b.resource_id = :l_resource_id
876 AND b.object_type=''FUND''
877 AND a.fund_id =b.object_id';
878 END IF;
879 l_sqltext := l_sqltext ||
880 ' AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id';
881 l_sqltext := l_sqltext ||
882 ' AND cal.report_date = &BIS_CURRENT_ASOF_DATE
883 AND cal.calendar_id=-1
884 GROUP BY decode(a.leaf_node_flag,''Y'',null,a.category_id),a.category_id
885 UNION ALL
886 SELECT
887 decode(a.leaf_node_flag,''Y'',null,a.category_id) VIEWBYID,
888 a.category_id category_id,
889 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.original_budget'||l_curr_suffix||',0)) original_budget,
890 sum(decode(cal.report_date,&BIS_CURRENT_EFFECTIVE_START_DATE - 1,a.original_budget'||l_curr_suffix||'
891 +a.accrual'||l_curr_suffix||'+a.transfer_in'||l_curr_suffix||'-a.transfer_out'||l_curr_suffix||'
892 -a.holdback'||l_curr_suffix||'-a.committed'||l_curr_suffix||',0)) pre_balance,
893 0 transfer_in,
894 0 transfer_out,
895 0 holdback,
896 0 accrual,
897 0 committed,
898 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.original_budget'||l_curr_suffix||'+a.accrual'||l_curr_suffix||'
899 +a.transfer_in'||l_curr_suffix||'-a.transfer_out'||l_curr_suffix||'-a.holdback'||l_curr_suffix||'-a.committed'||l_curr_suffix||',0)) cur_balance,
900 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,a.planned'||l_curr_suffix||'-a.committed'||l_curr_suffix||',0)) planned,
901 0 utilized
902 FROM BIM_I_BGT_CAT_MV a,
903 fii_time_rpt_struct_v cal';
904 IF l_admin_status = 'N' THEN
905 l_sqltext := l_sqltext ||
906 ' , ams_act_access_denorm b';
907 END IF;
908 l_sqltext := l_sqltext ||
909 ' WHERE a.time_id = cal.time_id
910 AND a.period_type_id = cal.period_type_id
911 AND a.parent_category_id is null
912 ';
913 IF l_admin_status = 'N' THEN
914 l_sqltext := l_sqltext ||
915 ' AND b.resource_id = :l_resource_id
916 AND b.object_type=''FUND''
917 AND a.fund_id =b.object_id';
918 END IF;
919 l_sqltext := l_sqltext ||
923 AND cal.calendar_id=-1
920 ' AND BITAND(cal.record_type_id,1143)= cal.record_type_id';
921 l_sqltext := l_sqltext ||
922 ' AND cal.report_date in (&BIS_CURRENT_ASOF_DATE,&BIS_CURRENT_EFFECTIVE_START_DATE - 1)
924 GROUP BY decode(a.leaf_node_flag,''Y'',null,a.category_id),a.category_id
925 ) inner, ams_categories_tl e
926 WHERE inner.category_id = e.category_id
927 AND e.language =USERENV(''LANG'')
928 GROUP BY e.category_name,VIEWBYID
929 HAVING
930 sum(original_budget) >0
931 or sum(pre_balance) >0
932 or sum(transfer_in) >0
933 or sum(transfer_out) >0
934 or sum(holdback) >0
935 or sum(accrual)>0
936 or sum(committed) >0
937 or sum(cur_balance) >0
938 or sum(planned) >0
939 or sum(utilized)>0
940 )
941 &ORDER_BY_CLAUSE';
942 END IF;
943 x_custom_sql := l_sqltext;
944 l_custom_rec.attribute_name := ':l_record_type';
945 l_custom_rec.attribute_value := l_record_type_id;
946 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
947 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
948 x_custom_output.EXTEND;
949 x_custom_output(1) := l_custom_rec;
950
951 l_custom_rec.attribute_name := ':l_resource_id';
952 l_custom_rec.attribute_value := GET_RESOURCE_ID;
953 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
954 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
955 x_custom_output.EXTEND;
956 x_custom_output(2) := l_custom_rec;
957
958 l_custom_rec.attribute_name := ':l_admin_flag';
959 l_custom_rec.attribute_value := GET_ADMIN_STATUS;
960 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
961 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
962 x_custom_output.EXTEND;
963 x_custom_output(3) := l_custom_rec;
964
965 l_custom_rec.attribute_name := ':l_country';
966 l_custom_rec.attribute_value := l_country;
967 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
968 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
969 x_custom_output.EXTEND;
970 x_custom_output(4) := l_custom_rec;
971
972 l_custom_rec.attribute_name := ':l_fund_id';
973 l_custom_rec.attribute_value := l_fund_id;
974 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
975 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
976 x_custom_output.EXTEND;
977 x_custom_output(5) := l_custom_rec;
978
979 l_custom_rec.attribute_name := ':l_bcat_id';
980 l_custom_rec.attribute_value := l_bcat_id;
981 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
982 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
983 x_custom_output.EXTEND;
984 x_custom_output(5) := l_custom_rec;
985
986 /* l_custom_rec.attribute_name := ':l_campaign_id';
987 l_custom_rec.attribute_value := l_campaign_id;
988 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
989 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
990 x_custom_output.EXTEND;
991 x_custom_output(6) := l_custom_rec;*/
992
993 write_debug('GET_BGT_CAT_SQL','QUERY','_',l_sqltext);
997 WHEN others THEN
994 --return l_sqltext;
995
996 EXCEPTION
998 l_sql_errm := SQLERRM;
999 write_debug('GET_BGT_SUM_SQL','ERROR',l_sql_errm,l_sqltext);
1000 END GET_BGT_CAT_SQL;
1001
1002 PROCEDURE GET_BGT_UTL_SQL(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
1003 x_custom_sql OUT NOCOPY VARCHAR2,
1004 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1005 IS
1006 --l_sqltext varchar2(12000);
1007 l_sqltext varchar2(30000);
1008 iFlag number;
1009 l_period_type_hc number;
1010 l_as_of_date DATE;
1011 l_date date;
1012 l_period_type varchar2(2000);
1013 l_record_type_id NUMBER;
1014 l_comp_type varchar2(2000);
1015 l_country varchar2(4000);
1016 l_view_by varchar2(4000);
1017 l_sql_errm varchar2(4000);
1018 l_previous_report_start_date DATE;
1019 l_current_report_start_date DATE;
1020 l_previous_as_of_date DATE;
1021 l_period_type_id NUMBER;
1022 l_user_id NUMBER;
1023 l_resource_id NUMBER;
1024 l_time_id_column VARCHAR2(1000);
1025 l_admin_status VARCHAR2(20);
1026 l_admin_flag VARCHAR2(1);
1027 l_admin_count Number;
1028 l_rsid NUMBER;
1029 l_curr_aod_str varchar2(80);
1030 l_select_clause varchar2(1000);
1031 l_sel1 varchar2(1000);
1032 l_sel2 varchar2(1000);
1033 l_access_table varchar2(4000);
1034 l_campaign_id VARCHAR2(50);
1035 l_cat_id VARCHAR2(50):=NULL;
1036 l_select VARCHAR2 (20000); -- to build inner select to pick data from mviews
1037 l_pc_select VARCHAR2 (20000); -- to build inner select to pick data directly assigned to the product category hirerachy
1038 l_select_cal VARCHAR2 (20000); -- to build select calculation part
1039 l_select_filter VARCHAR2 (20000); -- to build select filter part
1040 l_from VARCHAR2 (20000); -- assign common table in clause
1041 l_where VARCHAR2 (2000); -- static where clause
1042 l_where1 VARCHAR2 (2000); -- static where clause
1043 l_where2 VARCHAR2 (2000); -- static where clause
1044 l_groupby VARCHAR2 (2000); -- to build group by clause
1045 l_pc_from VARCHAR2 (20000); -- from clause to handle product category
1046 l_pc_where VARCHAR2 (20000); -- where clause to handle product category
1047 l_filtercol VARCHAR2 (2000);
1048 l_pc_col VARCHAR2(100);
1049 l_pc_groupby VARCHAR2(200);
1050 l_view VARCHAR2 (20);
1051 l_comm_col1 VARCHAR2(2000);
1052 l_comm_col2 VARCHAR2(2000);
1053 l_comm_cols VARCHAR2 (20000);
1054 l_view_disp VARCHAR2(100);
1055 l_url_str VARCHAR2(1000);
1056 l_url_str_jtf VARCHAR2(1000);
1057 l_url_str_csch VARCHAR2(1000);
1058 l_url_str_type VARCHAR2(1000);
1059 l_top_cond VARCHAR2(100);
1060 l_meaning VARCHAR2 (500);
1061 l_table VARCHAR2(2000);
1062 l_fund_id VARCHAR2(50);
1063 l_bcat_id VARCHAR2(50);
1064 l_curr VARCHAR2(50);
1065 l_curr_suffix VARCHAR2(50);
1066 l_url varchar2(2000);
1067 l_col_id number;
1068 l_area VARCHAR2(50);
1069 l_media VARCHAR2(50);
1070 l_report_name VARCHAR2(50);
1071
1072 /* variables to hold columns names in l_select clauses */
1073 l_col VARCHAR2(1000);
1074 /* cursor to get type of object passed from the page ******/
1075 cursor get_obj_type
1076 is
1077 select object_type
1078 from bim_i_source_codes
1079 where source_code_id=replace(l_campaign_id,'''');
1080 /*********************************************************/
1081 l_object_type varchar2(30);
1082 l_custom_rec BIS_QUERY_ATTRIBUTES;
1083 l_curr_suffix1 VARCHAR2(50);
1084 l_table_bud VARCHAR2(300);
1085 l_where_bud VARCHAR2(300);
1086 l_prog_cost1 VARCHAR2(20);
1087
1088 BEGIN
1089 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1090 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1091 bim_pmv_dbi_utl_pkg.get_bim_page_params(p_page_parameter_tbl,
1092 l_as_of_date,
1093 l_period_type,
1094 l_record_type_id,
1095 l_comp_type,
1096 l_country,
1097 l_view_by,
1098 l_cat_id,
1099 l_campaign_id,
1100 l_curr,
1101 l_col_id,
1102 l_area,
1103 l_media,
1104 l_report_name );
1105
1106 for i in get_obj_type
1107 loop
1108 l_object_type:=i.object_type;
1109 end loop;
1110
1111 l_meaning:=' null meaning '; -- assigning default value
1112 IF (l_curr = '''FII_GLOBAL1''')
1113 THEN l_curr_suffix := '';
1114 ELSIF (l_curr = '''FII_GLOBAL2''')
1115 THEN l_curr_suffix := '_s';
1116 ELSE l_curr_suffix := '';
1117 END IF;
1118 --l_curr_aod_str := 'to_date('||to_char(l_as_of_date,'J')||',''J'')';
1119
1120 l_admin_status := GET_ADMIN_STATUS;
1121
1122 l_url_str :='pFunctionName=BIM_I_BGT_UTL_PHP&pParamIds=Y&VIEW_BY='||l_view_by||'&VIEW_BY_NAME=VIEW_BY_ID';
1126 l_url_str_type :='pFunctionName=AMS_WB_CSCH_RPRT&addBreadCrumb=Y&OAPB=AMS_CAMP_WORKBENCH_BRANDING&objType=CSCH&objId=';
1123 l_url_str_jtf :='pFunctionName=BIM_I_CSCH_START_DRILL&pParamIds=Y&VIEW_BY='||l_view_by||'&VIEW_BY_NAME=VIEW_BY_ID&PAGE.OBJ.ID_NAME1=customSetupId&PAGE.OBJ.ID1=1&PAGE.OBJ.objType=CSCH&PAGE.OBJ.objAttribute=DETL&PAGE.OBJ.ID_NAME0=objId&PAGE.OBJ.ID0=';
1124
1125 l_url_str_csch :='pFunctionName=AMS_WB_CSCH_UPDATE&omomode=UPDATE&MidTab=TargetAccDSCRN&searchType=customize&OA_SubTabIdx=3&retainAM=Y&addBreadCrumb=S&addBreadCrumb=Y&objId=';
1127
1128 IF l_country IS NULL THEN
1129 l_country := 'N';
1130 END IF;
1131
1132
1133 /** to add meaning in select clause only in case of campaign view by */
1134 IF (l_view_by = 'CAMPAIGN+CAMPAIGN') THEN
1135 l_meaning:=' meaning,object_id,object_type,usage ';
1136 l_filtercol:=',meaning,object_id,object_type,usage ';
1137 l_table :='bim_i_obj_mets_mv';
1138 else
1139 l_meaning:=' null meaning , null object_id, null object_type, null usage ';
1140 l_table :='bim_obj_chnl_mv';
1141 end if;
1142
1143 /*** to display Directly assigned **/
1144
1145 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') AND l_cat_id is not null THEN
1146 l_view_disp:=' DECODE(viewbyid,-999,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'DASS'||''''||')'||',viewby) viewby,';
1147 ELSE
1148 l_view_disp:='viewby,';
1149 END IF;
1150
1151 if l_view_by ='GEOGRAPHY+COUNTRY' then
1152 l_url:=' null bim_url1,
1153 null BIM_URL2,
1154 NULL BIM_URL3, ';
1155 else
1156 l_url:= ' decode(viewbyid,-999,NULL,-1,NULL,-777,null,'||''''||l_url_str||''''||' ) bim_url1,
1157 decode(object_type,''CSCH'',decode(usage,''LITE'','||''''||l_url_str_csch||''''||'||object_id,'||''''||l_url_str_jtf||''''||'||object_id),''EVEO'',NULL,''EONE'',NULL,'||''''||l_url_str||''''||' ) BIM_URL2,
1158 decode(object_type,''CSCH'','||''''||l_url_str_type||''''||'||object_id,NULL ) BIM_URL3, ';
1159 end if;
1160
1161
1162 /* l_select_cal is common part of select statement for all view by to calculate grand totals and change */
1163 l_select_cal :='
1164 SELECT '||
1165 l_view_disp ||'
1166 viewbyid,
1167 bim_attribute2,
1168 bim_attribute3,
1169 bim_attribute4,
1170 bim_attribute5,
1171 bim_attribute6,
1172 bim_attribute8,
1173 bim_attribute7,'||l_url||'
1174 bim_grand_total1,
1175 bim_grand_total2,
1176 bim_grand_total3,
1177 bim_grand_total4,
1178 bim_grand_total5
1179 FROM
1180 (
1181 SELECT name VIEWBY,object_id,object_type,usage,
1182 meaning BIM_ATTRIBUTE2,
1183 approved BIM_ATTRIBUTE3,
1184 utilized BIM_ATTRIBUTE4,
1185 total_approved BIM_ATTRIBUTE5,
1186 total_utilized BIM_ATTRIBUTE6,
1187 total_approved BIM_ATTRIBUTE8,
1188 balance BIM_ATTRIBUTE7,
1189 sum(approved) over() BIM_GRAND_TOTAL1,
1190 sum(utilized) over() BIM_GRAND_TOTAL2,
1191 sum(total_approved) over() BIM_GRAND_TOTAL3,
1192 sum(total_utilized) over() BIM_GRAND_TOTAL4,
1193 sum(balance) over() BIM_GRAND_TOTAL5,
1194 VIEWBYID
1195 FROM
1196 (
1197 SELECT
1198 viewbyid,
1199 name,'||
1200 l_meaning||
1201 ',sum(approved) approved,
1202 sum(utilized) utilized,
1203 sum(total_approved) total_approved,
1204 sum(total_utilized) total_utilized,
1205 sum(total_approved)-sum(total_utilized) balance
1206 FROM
1207 ( ';
1208
1209 l_curr_suffix1 :=l_curr_suffix;
1210
1211 IF l_object_type in ('CAMP','EVEH','CSCH') AND l_prog_cost ='BIM_APPROVED_BUDGET' THEN
1212
1213 l_table_bud := ' ,bim_i_marketing_facts facts';
1214 l_where_bud := ' AND facts.source_code_id = a.source_code_id';
1215 IF l_curr_suffix is null THEN
1216 l_prog_cost1 := 'facts.metric1';
1217 ELSE
1218 l_curr_suffix1 := null;
1219 l_prog_cost1 := 'facts.metric2';
1220 END IF;
1221 ELSE
1222 l_prog_cost1 :='a.budget_approved';
1223
1224 END IF;
1225
1226 IF (l_view_by = 'CAMPAIGN+CAMPAIGN') THEN
1227 l_comm_col1:= ' ,name.object_id,name.object_type,name.child_object_usage usage
1228 ,sum('||l_prog_cost1||l_curr_suffix1||') approved,
1229 sum(a.cost_actual'||l_curr_suffix||') utilized,
1230 0 total_approved ,
1231 0 total_utilized ,
1232 0 balance';
1233 l_comm_col2:= ' ,name.object_id,name.object_type,name.child_object_usage usage
1234 ,0 approved,
1235 0 utilized,
1236 sum('||l_prog_cost1||l_curr_suffix1||') total_approved ,
1237 sum(a.cost_actual'||l_curr_suffix||') total_utilized ,
1238 0 balance';
1239 ELSE
1240 l_comm_col1:= ' ,null object_id,null object_type, null usage
1241 ,sum(budget_approved'||l_curr_suffix||') approved,
1242 sum(cost_actual'||l_curr_suffix||') utilized,
1243 0 total_approved ,
1244 0 total_utilized ,
1245 0 balance';
1249 sum(budget_approved'||l_curr_suffix||') total_approved ,
1246 l_comm_col2:= ' ,null object_id,null object_type, null usage
1247 ,0 approved,
1248 0 utilized,
1250 sum(cost_actual'||l_curr_suffix||') total_utilized ,
1251 0 balance';
1252 END IF;
1253
1254 /* l_from contains time dimension table common to all select statement for all view by */
1255 l_from :=',fii_time_rpt_struct_v cal ';
1256 /* l_where contains where clause to join time dimension table common to all select statement for all view by */
1257 l_where :=' WHERE a.time_id = cal.time_id
1258 AND a.period_type_id = cal.period_type_id
1259 AND cal.calendar_id= -1
1260 ';
1261 l_where1 :=' AND BITAND(cal.record_type_id,:l_record_type)= cal.record_type_id ';
1262 l_where2 :=' AND BITAND(cal.record_type_id,1143)= cal.record_type_id ';
1263 /* l_select_filter contains group by and filter clause to remove uneccessary records with zero values */
1264 l_select_filter := ' ) GROUP BY viewbyid,name '||l_filtercol||
1265 ')
1266 )
1267 WHERE
1268 bim_attribute3 <> 0
1269 or bim_attribute4 <> 0
1270 or bim_attribute5 <> 0
1271 or bim_attribute6 <> 0
1272 or bim_attribute7 <> 0
1273 or bim_attribute8 <> 0
1274 &ORDER_BY_CLAUSE ';
1275 /*********************** security handling ***********************/
1276
1277 IF l_campaign_id is null THEN /******* no security checking at child level ********/
1278 IF l_admin_status = 'N' THEN
1279 IF l_view_by = 'CAMPAIGN+CAMPAIGN' then
1280 /*************** program view is enable **************/
1281 IF l_prog_view='Y' then
1282 l_view := ',''RCAM''';
1283 l_from := l_from ||',bim_i_top_objects ac ';
1284 l_where := l_where ||' AND a.source_code_id=ac.source_code_id
1285 AND ac.resource_id = :l_resource_id ';
1286 /************************************************/
1287 ELSE
1288
1289 l_from := l_from ||',ams_act_access_denorm ac,bim_i_source_codes src ';
1290 l_where := l_where ||' AND a.source_code_id=src.source_code_id
1291 AND src.object_id=ac.object_id
1292 AND src.object_type=ac.object_type
1293 AND ac.resource_id = :l_resource_id ';
1294
1295 END IF;
1296
1297 ELSE
1298 l_from := l_from ||',bim_i_top_objects ac ';
1299 l_where := l_where ||' AND a.source_code_id=ac.source_code_id
1300 AND ac.resource_id = :l_resource_id ';
1301 END IF;
1302
1303 ELSE
1304 IF l_view_by = 'CAMPAIGN+CAMPAIGN' then
1305 IF l_prog_view='Y' THEN
1306 l_view := ',''RCAM''';
1307 l_top_cond :=' AND a.immediate_parent_id is null ';
1308 END IF;
1309 ELSE
1310 /******** to append parent object id is null for other view by (country and product category) ***/
1311 l_top_cond :=' AND a.immediate_parent_id is null ';
1312 /***********/
1313 END IF;
1314 END IF;
1315 END IF;
1316 --end of security handling
1317 /************************************************************************/
1318 /* product category handling */
1319 IF l_cat_id is not null then
1320 l_pc_from := ', eni_denorm_hierarchies edh,mtl_default_category_sets d';
1324 AND d.functional_area_id = 11
1321 l_pc_where := ' AND a.category_id = edh.child_id
1322 AND edh.object_type = ''CATEGORY_SET''
1323 AND edh.object_id = d.category_set_id
1325 AND edh.dbi_flag = ''Y''
1326 AND edh.parent_id = :l_cat_id ';
1327 ELSE
1328 l_pc_where := ' AND a.category_id = -9 ';
1329 END IF;
1330
1331 IF (l_view_by = 'CAMPAIGN+CAMPAIGN') THEN
1332
1333 /* forming from clause for the tables which is common to all union all */
1334 if l_cat_id is not null then
1335 l_from :=' FROM '||l_table||' a, bim_i_obj_name_mv name '||l_from||l_pc_from;
1336 else
1337 l_from :=' FROM '||l_table||' a, bim_i_obj_name_mv name '||l_from;
1338 end if;
1339
1340
1341 /* forming where clause which is common to all union all */
1342 l_where :=l_where||' AND a.source_code_id = name.source_code_id
1343 AND name.language=USERENV(''LANG'')
1344 AND a.object_country = :l_country'||
1345 l_pc_where;
1346
1347
1348 /* forming group by clause for the common columns for all union all */
1349 IF l_view_by = 'CAMPAIGN+CAMPAIGN' then
1350 l_groupby:=' GROUP BY a.source_code_id,name.object_type_mean,name.name,name.object_id,name.object_type, name.child_object_usage ';
1351 ELSE
1352 l_groupby:=' GROUP BY a.source_code_id,name.object_type_mean, ';
1353 END IF;
1354
1355 /*** campaign id null means No drill down and view by is camapign hirerachy*/
1356 IF l_campaign_id is null THEN
1357 /*appending l_select_cal for calculation and sql clause to pick data and filter clause to filter records with zero values***/
1358 l_sqltext:= l_select_cal||
1359 /******** inner select start from here */
1360 /* select to get camapigns and programs */
1361 ' SELECT
1362 a.source_code_id VIEWBYID,
1363 name.name name,
1364 name.object_type_mean meaning '||
1365 l_comm_col1 ||
1366 l_from ||l_where||l_where1||l_top_cond||
1367 ' AND cal.report_date=&BIS_CURRENT_ASOF_DATE
1368 and name.language=USERENV(''LANG'')'
1369 ||l_groupby|| ',name.name'||
1370 ' UNION ALL
1371 SELECT
1372 a.source_code_id VIEWBYID,
1373 name.name name,
1374 name.object_type_mean meaning '||
1375 l_comm_col2 ||
1376 l_from ||l_where||
1377 l_where2 ||l_top_cond||
1378 ' AND cal.report_date=&BIS_CURRENT_ASOF_DATE
1379 and name.language=USERENV(''LANG'')'
1380 || l_groupby|| ',name.name '||
1381 l_select_filter /* appending filter clause */
1382 ;
1383 ELSE
1384
1385
1386 /* source_code_id is passed from the page, object selected from the page to be drill may be program,campaign,event,one off event*****/
1387 /* appending table in l_form and joining conditon for the bim_i_source_codes */
1388 l_where :=l_where ||' AND a.immediate_parent_id = :l_campaign_id ';
1389 -- checking for the object type passed from page
1390
1391 for i in get_obj_type
1392 loop
1393 l_object_type:=i.object_type;
1394 end loop;
1395
1396 -- if program is selected from the page means it may have childern as programs,campaigns,events or one off events
1397 /* changed the following to use the bim_i_obj_name_mv */
1398 l_sqltext:= l_select_cal||
1399 ' SELECT
1400 a.source_code_id VIEWBYID,
1401 name.name name,
1402 name.object_type_mean meaning '||
1403 l_comm_col1 ||
1404 l_from ||l_table_bud||l_where||l_where1||l_where_bud||
1405 ' AND cal.report_date=&BIS_CURRENT_ASOF_DATE
1406 and name.language=USERENV(''LANG'')'||
1407 l_groupby||
1408 ' ,name.name'||
1409 ' UNION ALL
1410 SELECT
1411 a.source_code_id VIEWBYID,
1412 name.name name,
1413 name.object_type_mean meaning '||
1414 l_comm_col2 ||
1415 l_from ||l_table_bud||l_where||l_where2||l_where_bud||
1416 ' AND cal.report_date=&BIS_CURRENT_ASOF_DATE
1417 and name.language=USERENV(''LANG'')'||
1418 l_groupby||
1419 ' ,name.name'||
1420 l_select_filter ;
1421
1422 END IF;
1423 /***** END CAMPAIGN HIRERACHY VIEW HANDLING ******************/
1424
1425 ELSE
1426 /* view by is product category */
1427 IF (l_view_by ='ITEM+ENI_ITEM_VBH_CAT') THEN
1428
1429 /******** handling product category hirerachy ****/
1430 /* picking up value of top level node from product category denorm for category present in bim_i_obj_mets_mv */
1431 IF l_cat_id is null then
1432 l_from:=l_from||
1433 ',eni_denorm_hierarchies edh
1434 ,mtl_default_category_sets d
1435 ,( SELECT e.parent_id parent_id ,e.value value
1436 FROM eni_item_vbh_nodes_v e
1437 WHERE e.top_node_flag=''Y''
1438 AND e.child_id = e.parent_id) p ';
1439 l_where := l_where||
1440 ' AND a.category_id = edh.child_id
1441 AND edh.object_type = ''CATEGORY_SET''
1442 AND edh.object_id = d.category_set_id
1443 AND d.functional_area_id = 11
1444 AND edh.dbi_flag = ''Y''
1445 AND edh.parent_id = p.parent_id';
1446 l_col:=' SELECT
1447 p.value name,
1448 p.parent_id viewbyid,
1449 null meaning ';
1453
1450 l_groupby := ' GROUP BY p.value,p.parent_id ';
1451 ELSE
1452 /* passing id from page and getting immediate child to build hirerachy */
1454 /** reassigning value to l_pc_from and l_pc_where for product category hirerachy drill down for values directly assigned to prodcut select from the page*/
1455
1456 l_pc_from:= l_from||
1457 ',(select e.id id,e.value value
1458 from eni_item_vbh_nodes_v e
1459 where e.parent_id = :l_cat_id
1460 AND e.parent_id = e.child_id
1461 AND leaf_node_flag <> ''Y''
1462 ) p ';
1463
1464 l_pc_where :=l_where||
1465 ' AND a.category_id = p.id ';
1466
1467 l_from:= l_from||
1468 ',eni_denorm_hierarchies edh
1469 ,mtl_default_category_sets mdc
1470 ,(select e.id,e.value,leaf_node_flag
1471 from eni_item_vbh_nodes_v e
1472 where
1473 e.parent_id =:l_cat_id
1474 AND e.id = e.child_id
1475 AND((e.leaf_node_flag=''N'' AND e.parent_id<>e.id) OR e.leaf_node_flag=''Y'')
1476 ) p ';
1477
1478 l_where := l_where||'
1479 AND a.category_id = edh.child_id
1480 AND edh.object_type = ''CATEGORY_SET''
1481 AND edh.object_id = mdc.category_set_id
1482 AND mdc.functional_area_id = 11
1483 AND edh.dbi_flag = ''Y''
1484 AND edh.parent_id = p.id ';
1485
1486 l_col:=' SELECT
1487 p.value name,
1488 decode(p.leaf_node_flag,''Y'',-777,p.id) viewbyid,
1489 null meaning ';
1490 l_groupby := ' GROUP BY p.value,decode(p.leaf_node_flag,''Y'',-777,p.id) ';
1491 END IF;
1492 /*********************/
1493
1494 IF l_campaign_id is null then /* no drilll down in campaign hirerachy */
1495 IF l_admin_status ='Y' THEN
1496 l_from:=' FROM bim_i_obj_mets_mv a
1497 '||l_from;
1498 l_where := l_where ||l_top_cond||
1499 ' AND a.object_country = :l_country';
1500 IF l_cat_id is not null then
1501 l_pc_from := ' FROM bim_i_obj_mets_mv a
1502 '||l_pc_from;
1503 l_pc_where := l_pc_where ||l_top_cond||
1504 ' AND a.object_country = :l_country';
1505 END IF;
1506 ELSE
1507 l_from:=' FROM bim_i_obj_mets_mv a
1508 '||l_from;
1509 l_where := l_where ||
1510 ' AND a.object_country = :l_country';
1511
1512 IF l_cat_id is not null then
1513 l_pc_from := ' FROM bim_i_obj_mets_mv a
1514 '||l_pc_from;
1515 l_pc_where := l_pc_where ||
1516 ' AND a.object_country = :l_country';
1517 END IF;
1518
1519 END IF;
1520 ELSE
1521 l_from := ' FROM bim_i_obj_mets_mv a '||l_from ;
1522 l_where := l_where ||
1523 -- ' AND a.parent_denorm_type = b.object_type
1524 -- AND a.parent_object_id = b.object_id
1525 ' AND a.source_code_id = :l_campaign_id
1526 -- AND b.child_object_id=0
1527 AND a.object_country = :l_country' ;
1528 IF l_cat_id is not null then
1529 l_pc_from := ' FROM bim_i_obj_mets_mv a '||l_pc_from ;
1530 l_pc_where := l_pc_where ||
1531 --' AND a.parent_denorm_type = b.object_type
1532 -- AND a.parent_object_id = b.object_id
1533 'AND a.source_code_id = :l_campaign_id
1534 -- AND b.child_object_id=0
1535 AND a.object_country = :l_country' ;
1536 END IF;
1537 END IF;
1538 /* building l_pc_select to get values directly assigned to product category passed from the page */
1539 IF l_cat_id is not null THEN
1540 l_pc_col:=' SELECT
1541 p.value name,
1542 -999 viewbyid,
1543 null meaning ';
1544 l_pc_groupby := ' GROUP BY p.value,p.id ';
1545
1546 l_pc_select :=
1547 ' UNION ALL ' ||
1548 l_pc_col||
1549 l_comm_col1||
1550 l_pc_from||
1551 l_pc_where ||l_where1||' AND cal.report_date =&BIS_CURRENT_ASOF_DATE'||
1552 l_pc_groupby ||
1553 ' UNION ALL ' ||
1554 l_pc_col||
1555 l_comm_col2||
1556 l_pc_from||
1557 l_pc_where ||l_where2||' AND cal.report_date =&BIS_CURRENT_ASOF_DATE'||
1558 l_pc_groupby
1559 ;
1560 END IF;
1561 ELSIF (l_view_by ='GEOGRAPHY+COUNTRY') THEN
1562 /** product category handling**/
1563 IF l_cat_id is null then
1564 l_where := l_where ||l_pc_where;
1565 ELSE
1566 l_from := l_from ||l_pc_from;
1567 l_where := l_where||l_pc_where;
1568 END IF;
1569 l_col:=' SELECT
1570 decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) name,
1571 a.object_country viewbyid,
1572 null meaning ';
1576 IF l_admin_status ='Y' THEN
1573 l_groupby := ' GROUP BY a.object_country,decode(d.name,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.name) ';
1574 l_from:=' FROM bis_countries_v d '||l_from;
1575 IF l_campaign_id is null then
1577 l_from:=l_from||' , '||l_table||' a ';
1578 l_where := l_where ||l_top_cond||
1579 ' AND a.object_country =d.country_code (+)';
1580 ELSE
1581 l_from:=l_from||' , '||l_table||' a ';
1582 l_where := l_where ||
1583 ' AND a.object_country =d.country_code (+)';
1584 END IF;
1585 ELSE
1586 l_from := l_from||' , '||l_table||' a ';
1587 l_where := l_where ||
1588 -- ' AND a.parent_denorm_type = b.object_type
1589 -- AND a.parent_object_id = b.object_id
1590 -- AND b.child_object_id=0
1591 ' AND a.source_code_id = :l_campaign_id
1592 AND a.object_country =d.country_code (+) ';
1593 END IF;
1594 IF l_country <>'N' THEN
1595 l_where := l_where ||' AND a.object_country = :l_country';
1596 ELSE
1597 l_where := l_where ||' AND a.object_country <> ''N''';
1598 END IF;
1599 ELSIF (l_view_by ='MEDIA+MEDIA') THEN
1600 /** product category handling**/
1601 IF l_cat_id is null then
1602 l_where := l_where ||l_pc_where;
1603 ELSE
1604 l_from := l_from ||l_pc_from;
1605 l_where := l_where||l_pc_where;
1606 END IF;
1607 l_col:=' SELECT
1608 decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
1609 null viewbyid,
1610 null meaning ';
1611 l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
1612 l_from:=' FROM bim_dimv_media d '||l_from;
1613 IF l_campaign_id is null then
1614 IF l_admin_status ='Y' THEN
1615 l_from:=l_from||' ,bim_mkt_chnl_mv a ';
1616 l_where := l_where ||
1617 ' AND d.id (+)= a.activity_id
1618 AND a.object_country = :l_country';
1619 ELSE
1620 l_from:=l_from||' ,bim_obj_chnl_mv a ';
1621 l_where := l_where ||
1622 ' AND d.id (+)= a.activity_id
1623 AND a.object_country = :l_country';
1624 END IF;
1625 ELSE
1626 l_from := l_from||' ,bim_obj_chnl_mv a ';
1627 l_where := l_where ||
1628 ' AND a.source_code_id = :l_campaign_id
1629 AND d.id (+)= a.activity_id
1630 AND a.object_country = :l_country';
1631 END IF;
1632 ELSIF (l_view_by ='GEOGRAPHY+AREA') THEN
1633 /** product category handling**/
1634 IF l_cat_id is null then
1635 l_where := l_where ||l_pc_where;
1636 ELSE
1637 l_from := l_from ||l_pc_from;
1638 l_where := l_where||l_pc_where;
1639 END IF;
1640 l_col:=' SELECT
1641 decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) name,
1642 null viewbyid,
1643 null meaning ';
1644 l_groupby := ' GROUP BY decode(d.value,null,bim_pmv_dbi_utl_pkg.get_lookup_value('||''''||'UNA'||''''||')'||',d.value) ';
1645 l_from:=' FROM bis_areas_v d '||l_from;
1646 IF l_campaign_id is null then
1647 IF l_admin_status ='Y' THEN
1648 l_from:=l_from||' ,bim_mkt_regn_mv a ';
1649 l_where := l_where ||
1650 ' AND d.id (+)= a.object_region
1651 AND a.object_country = :l_country';
1652 ELSE
1653 l_from:=l_from||' ,bim_obj_regn_mv a';
1654 l_where := l_where ||
1655 ' AND d.id (+)= a.object_region
1656 AND a.object_country = :l_country';
1657 END IF;
1658 ELSE
1659 l_from := l_from||' ,bim_obj_regn_mv a ';
1660 l_where := l_where ||
1661 ' AND a.source_code_id = :l_campaign_id
1662 AND d.id (+)= a.object_region
1663 AND a.object_country = :l_country';
1664 END IF;
1665 END IF;
1666
1667 /* combine sql one to pick up current period values and sql two to pick previous period values */
1668 l_select := l_col||
1669 l_comm_col1||
1670 l_from||
1671 l_where ||l_where1||' AND cal.report_date =&BIS_CURRENT_ASOF_DATE '||
1672 l_groupby ||
1673 ' UNION ALL'||
1674 l_col||
1675 l_comm_col2||
1676 l_from||
1677 l_where ||l_where2||' AND cal.report_date =&BIS_CURRENT_ASOF_DATE '||
1678 l_groupby ||
1679 l_pc_select /* l_pc_select only applicable when product category is not all and view by is product category */
1680 ;
1681
1682 /* prepare final sql */
1683
1684 l_sqltext:= l_select_cal||
1685 l_select||
1686 l_select_filter;
1687
1688 END IF;
1689
1690 x_custom_sql := l_sqltext;
1691 l_custom_rec.attribute_name := ':l_record_type';
1692 l_custom_rec.attribute_value := l_record_type_id;
1693 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1694 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1695 x_custom_output.EXTEND;
1696 x_custom_output (1) := l_custom_rec;
1697 l_custom_rec.attribute_name := ':l_resource_id';
1701 x_custom_output.EXTEND;
1698 l_custom_rec.attribute_value := get_resource_id;
1699 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1700 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1702 x_custom_output (2) := l_custom_rec;
1703 l_custom_rec.attribute_name := ':l_admin_flag';
1704 l_custom_rec.attribute_value := get_admin_status;
1705 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1706 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1707 x_custom_output.EXTEND;
1708 x_custom_output (3) := l_custom_rec;
1709 l_custom_rec.attribute_name := ':l_country';
1710 l_custom_rec.attribute_value := l_country;
1711 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1712 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1713 x_custom_output.EXTEND;
1714 x_custom_output (4) := l_custom_rec;
1715 l_custom_rec.attribute_name := ':l_cat_id';
1716 l_custom_rec.attribute_value := l_cat_id;
1717 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1718 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1719 x_custom_output.EXTEND;
1720 x_custom_output (5) := l_custom_rec;
1721 l_custom_rec.attribute_name := ':l_campaign_id';
1722 l_custom_rec.attribute_value := l_campaign_id;
1723 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1724 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1725 x_custom_output.EXTEND;
1726 x_custom_output (6) := l_custom_rec;
1727
1728 write_debug('GET_BGT_UTL_SQL','QUERY','_',l_sqltext);
1729 --return l_sqltext;
1730 --INSERT INTO bim_test_sql values(l_view_by,l_sqltext);
1731 EXCEPTION
1732 WHEN others THEN
1733 l_sql_errm := SQLERRM;
1734 write_debug('GET_BGT_UTL_SQL','ERROR',l_sql_errm,l_sqltext);
1735 END GET_BGT_UTL_SQL;
1736
1737 END BIM_DBI_BGT_MGMT_PVT;