[Home] [Help]
PACKAGE BODY: APPS.RCI_OPEN_ISSUE_SUMM_PKG
Source
1 PACKAGE BODY RCI_OPEN_ISSUE_SUMM_PKG as
2 /*$Header: rciopenisssummb.pls 120.12 2006/09/21 13:51:44 dpatel noship $*/
3 --need to remove this function if it is not used in any other package
4 FUNCTION get_first_day(date_id NUMBER, type VARCHAR2) return varchar2
5 IS
6 v_year varchar2(4);
7 v_month varchar2(2);
8 v_qtr number(1);
9 BEGIN
10 IF type='M' THEN
11 v_year := SUBSTR(date_id,1,4);
12 v_month := SUBSTR(date_id,6);
13 ELSIF type='Q' THEN
14 v_year := SUBSTR(date_id,1,4);
15 v_qtr := SUBSTR(date_id,5,1);
16 CASE v_qtr
17 WHEN 1 THEN v_month := '01';
18 WHEN 2 THEN v_month := '04';
19 WHEN 3 THEN v_month := '07';
20 WHEN 4 THEN v_month := '10';
21 END CASE;
22 ELSIF type='Y' THEN
23 v_year := date_id;
24 v_month := '01';
25 END IF;
26 return v_year||v_month;
27 END;
28
29 procedure initial_load(
30 errbuf IN OUT NOCOPY VARCHAR2
31 ,retcode IN OUT NOCOPY NUMBER) is
32
33 l_cert_id number;
34 l_org_id number;
35 l_proc_id number;
36 l_past_due number;
37 l_age number;
38 l_age_distribution_1 number;
39 l_age_distribution_2 number;
40 l_age_distribution_3 number;
41 l_age_distribution_4 number;
42 l_FIN_CERT_ID number;
43 l_FIN_CERT_TYPE varchar2(10);
44 l_FIN_CERT_STATUS varchar2(30);
45 l_period_year number;
46 l_period_num number;
47 l_quarter_num number;
48 l_ent_period_id number;
49 l_ent_qtr_id number;
50 l_ent_year_id number;
51 l_report_date_julian number;
52 l_ent_period_end date;
53 l_ent_qtr_end date;
54 l_ent_yr_end date;
55 l_open_per number;
56 l_past_due_per number;
57 l_age_per number;
58 l_age_distribution1_per number;
59 l_age_distribution2_per number;
60 l_age_distribution3_per number;
61 l_age_distribution4_per number;
62 l_open_yr number;
63 l_past_due_yr number;
64 l_age_yr number;
65 l_age_distribution1_yr number;
66 l_age_distribution2_yr number;
67 l_age_distribution3_yr number;
68 l_age_distribution4_yr number;
69 l_open_qtr number;
70 l_past_due_qtr number;
71 l_age_qtr number;
72 l_age_distribution1_qtr number;
73 l_age_distribution2_qtr number;
74 l_age_distribution3_qtr number;
75 l_age_distribution4_qtr number;
76
77 cursor cur_f is
78 select * from rci_open_issues_f;
79
80 cur_rec cur_f%rowtype;
81 begin
82
83 EXECUTE IMMEDIATE ('TRUNCATE TABLE amw.rci_open_issues_f');
84
85 -- is the change_order open?
86 -- status_code, status_type can be confusing. For simplicity, for my purpose,
87 -- if the status_code is not in (0, 11), I'll consider this open
88
89
90 insert into rci_open_issues_f(
91 change_id,
92 change_name,
93 description,
94 status_type,
95 status_code,
96 change_order_type_id,
97 change_mgmt_type_code,
98 initiation_date,
99 need_by_date,
100 priority_code,
101 reason_code,
102 certification_id,
103 organization_id,
104 process_id,
105 fin_cert_id,
106 fin_cert_type,
107 fin_cert_status,
108 open,
109 past_due,
110 age,
111 age_distribution_1,
112 age_distribution_2,
113 age_distribution_3,
114 age_distribution_4,
115 implementation_date,
116 cancellation_date,
117 period_year,
118 period_num,
119 quarter_num,
120 ent_year_id,
121 ent_qtr_id,
122 ent_period_id,
123 report_date_julian)(
124 select eec.change_id,
125 change_name,
126 eec.description,
127 status_type,
128 status_code,
129 change_order_type_id,
130 change_mgmt_type_code,
131 initiation_date,
132 need_by_date,
133 priority_code,
134 reason_code,
135 afpcr.PROC_CERT_ID,/** as certificationId,**/
136 null, /**as organizationId,**/
137 null, /**as processId,**/
138 afpcr.FIN_STMT_CERT_ID, /**as finCertId,**/
139 fin_cert.CERTIFICATION_TYPE, /**as finCertType,**/
140 fin_cert.CERTIFICATION_STATUS, /**as finCertStatus,**/
141 decode(status_code, 0, 0, 11, 0, 1),/** as open,**/
142 0,/** as pastDue,**/
143 0,/** as age,**/
144 0,/** as ageDistribution1,**/
145 0,/** as ageDistribution2,**/
146 0,/** as ageDistribution3,**/
147 0,/** as ageDistribution4,**/
148 implementation_date,
149 cancellation_date,
150 agpv.PERIOD_YEAR, /**as periodYear,**/
151 agpv.PERIOD_NUM, /**as periodNum,**/
152 agpv.QUARTER_NUM, /**as quarterNum,**/
153 ftd.ENT_YEAR_ID, /**as entYearId,**/
154 ftd.ENT_QTR_ID, /**as entQuarterId,**/
155 ftd.ENT_PERIOD_ID, /**as entPeriodId,**/
156 to_number(to_char(agpv.end_date,'J')) /**as reportDateJulian**/
157 from eng_engineering_changes eec,
158 eng_change_subjects ecs,
159 AMW_FIN_PROC_CERT_RELAN afpcr,
160 amw_certification_b proc_cert,
161 amw_certification_b fin_cert,
162 amw_gl_periods_v agpv,
163 fii_time_day ftd
164 where change_order_type_id in (select change_order_type_id
165 from eng_change_order_types
166 where type_classification='HEADER'
167 and change_mgmt_type_code='AMW_PROC_CERT_ISSUES')
168 and ecs.CHANGE_ID = eec.CHANGE_ID
169 and ecs.ENTITY_NAME = 'CERTIFICATION'
170 and afpcr.END_DATE is null
171 and proc_cert.CERTIFICATION_ID = ecs.PK1_VALUE
172 and proc_cert.OBJECT_TYPE = 'PROCESS'
173 and proc_cert.CERTIFICATION_ID = afpcr.PROC_CERT_ID
174 and afpcr.FIN_STMT_CERT_ID = fin_cert.CERTIFICATION_ID
175 and fin_cert.CERTIFICATION_PERIOD_NAME = agpv.PERIOD_NAME
176 and fin_cert.CERTIFICATION_PERIOD_SET_NAME = agpv.PERIOD_SET_NAME
177 and ftd.REPORT_DATE_JULIAN = to_number(to_char(agpv.END_DATE,'J')));
178
179
180 for cur_rec in cur_f loop
181 exit when cur_f%notfound;
182
183 l_cert_id := 0;
184 l_org_id := 0;
185 l_proc_id := 0;
186 l_past_due := 0;
187 l_age := 0;
188 l_age_distribution_1 := 0;
189 l_age_distribution_2 := 0;
190 l_age_distribution_3 := 0;
191 l_age_distribution_4 := 0;
192
193 /*** the below is not needed, as certificationId is prepopulated above
194 begin
195 select pk1_value
196 into l_cert_id
197 from ENG_CHANGE_SUBJECTS where change_id = cur_rec.change_id
198 and entity_name = 'CERTIFICATION';
199 exception
200 when no_data_found then
201 l_cert_id := null;
202 end;**/
203
204 /**dbms_output.put_line( '*************** cur_rec.change_id: '||cur_rec.change_id||', l_cert_id: '||l_cert_id );**/
205
206 begin
207 select pk1_value
208 into l_org_id
209 from ENG_CHANGE_SUBJECTS where change_id = cur_rec.change_id
210 and entity_name = 'ORGANIZATION';
211 exception
212 when no_data_found then
213 l_org_id := null;
214 end;
215
216 /**dbms_output.put_line( '*************** l_org_id: '||l_org_id );**/
217
218 begin
219 select pk1_value
220 into l_proc_id
221 from ENG_CHANGE_SUBJECTS where change_id = cur_rec.change_id
222 and entity_name = 'PROCESS';
223 exception
224 when no_data_found then
225 l_proc_id := null;
226 end;
227
228 /**dbms_output.put_line( '*************** l_proc_id: '||l_proc_id );**/
229 -- is it past due? check only for open change orders.
230
231 if (cur_rec.open <> 0) then
232 if (cur_rec.need_by_date is not null) and (sysdate > cur_rec.need_by_date) then
233 l_past_due := 1;
234 else
235 l_past_due := 0;
236 end if;
237
238 l_age := sysdate - cur_rec.initiation_date;
239 if ((l_age >= 0) and (l_age <= 1)) then
240 l_age_distribution_1 := 1;
241 elsif ((l_age >= 2) and (l_age <= 5)) then
242 l_age_distribution_2 := 1;
243 elsif ((l_age >= 6) and (l_age <= 10)) then
244 l_age_distribution_3 := 1;
245 elsif (l_age > 10) then
246 l_age_distribution_4 := 1;
247 end if;
248 else
249 l_past_due := 0;
250 l_age := 0;
251 l_age_distribution_1 := 0;
252 l_age_distribution_2 := 0;
253 l_age_distribution_3 := 0;
254 l_age_distribution_4 := 0;
255 end if;
256
257 /**dbms_output.put_line( '*************** outside the cur_rec.open IF-ELSE block' );**/
258
259 /*** do not need the below block, as all the values are prepopulated above
260 begin
261 l_FIN_CERT_ID := null;
262 l_FIN_CERT_TYPE := null;
263 l_FIN_CERT_STATUS := null;
264
265 select cert2.certification_id
266 ,cert2.CERTIFICATION_TYPE
267 ,cert2.CERTIFICATION_STATUS,
268 agpv.period_year,
269 agpv.period_num,
270 agpv.quarter_num,
271 ftd.ent_period_id,
272 ftd.ent_qtr_id,
273 ftd.ent_year_id,
274 --to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)||to_char(agpv.period_num)),
275 --to_number(to_char(agpv.period_year)||to_char(agpv.quarter_num)),
276 --agpv.period_year,
277 to_number(to_char(agpv.end_date,'J')),
278 ftd.ent_period_end_date,
279 ftd.ent_qtr_end_date,
280 ftd.ent_year_end_date
281 into l_FIN_CERT_ID, l_FIN_CERT_TYPE, l_FIN_CERT_STATUS,
282 l_period_year, l_period_num, l_quarter_num, l_ent_period_id,
283 l_ent_qtr_id, l_ent_year_id, l_report_date_julian,
284 l_ent_period_end, l_ent_qtr_end, l_ent_yr_end
285 from AMW_CERTIFICATION_B cert
286 ,AMW_FIN_PROC_CERT_RELAN rln
287 ,AMW_CERTIFICATION_B cert2
288 ,amw_gl_periods_v agpv
289 ,fii_time_day ftd
290 where cert.OBJECT_TYPE = 'PROCESS'
291 and rln.PROC_CERT_ID=cert.CERTIFICATION_ID
292 and rln.END_DATE IS NULL
293 and rln.fin_stmt_cert_id = cert2.certification_id
294 and cert.CERTIFICATION_ID = l_cert_id
295 and cert2.certification_period_name = agpv.period_name
296 and cert2.certification_period_set_name = agpv.period_set_name
297 and ftd.report_date_julian = to_number(to_char(agpv.end_date,'J'));
298
299 dbms_output.put_line( '***1 --> cur_rec.change_id: '||cur_rec.change_id||', l_cert_id: '||l_cert_id||', l_FIN_CERT_ID: '||l_FIN_CERT_ID );
300
301 exception
302 when others then
303 dbms_output.put( '##### in null, l_cert_id: '||l_cert_id );
304 null;
305 end;***/
306
307 /**dbms_output.put_line( '***2 --> l_FIN_CERT_ID: '||l_FIN_CERT_ID||', l_FIN_CERT_TYPE: '||l_FIN_CERT_TYPE||', l_FIN_CERT_STATUS: '||l_FIN_CERT_STATUS );**/
308 update rci_open_issues_f
309 set /*certification_id = l_cert_id,*/
310 organization_id = l_org_id,
311 process_id = l_proc_id,
312 past_due = l_past_due,
313 age = l_age,
314 age_distribution_1 = l_age_distribution_1,
318 FIN_CERT_ID = l_FIN_CERT_ID,
315 age_distribution_2 = l_age_distribution_2,
316 age_distribution_3 = l_age_distribution_3,
317 age_distribution_4 = l_age_distribution_4/**,
319 FIN_CERT_TYPE = l_FIN_CERT_TYPE,
320 FIN_CERT_STATUS = l_FIN_CERT_STATUS,
321 period_year = l_period_year,
322 period_num = l_period_num,
323 quarter_num = l_quarter_num,
324 ent_period_id = l_ent_period_id,
325 ent_qtr_id = l_ent_qtr_id,
326 ent_year_id = l_ent_year_id,
327 report_date_julian = l_report_date_julian**/
328 where change_id = cur_rec.change_id;
329
330
331 -- populating the newly added age/etc columns per period-type.
332 -- I could have merged this in the above code, just wanted to keep this seperate
333 -- for readbility
334
335 -- consider qtr
336 l_open_qtr := 0;
337 l_past_due_qtr := 0;
338 l_age_qtr := 0;
339 l_age_distribution1_qtr := 0;
340 l_age_distribution2_qtr := 0;
341 l_age_distribution3_qtr := 0;
342 l_age_distribution4_qtr := 0;
343
344 if (cur_rec.status_code <> 0) and (cur_rec.status_code <> 11) then
345 l_open_qtr := 1;
346 elsif ( (cur_rec.implementation_date is not null)
347 and (cur_rec.implementation_date > l_ent_qtr_end) ) then
348 l_open_qtr := 1;
349 elsif ( (cur_rec.cancellation_date is not null)
350 and (cur_rec.cancellation_date > l_ent_qtr_end) ) then
351 l_open_qtr := 1;
352 end if;
353
354 if (cur_rec.open_qtr <> 0) then
355 if (cur_rec.need_by_date is not null) and (l_ent_qtr_end > cur_rec.need_by_date) then
356 l_past_due_qtr := 1;
357 else
358 l_past_due_qtr := 0;
359 end if;
360
361 l_age_qtr := l_ent_qtr_end - cur_rec.initiation_date;
362
363
364 if ((l_age_qtr >= 0) and (l_age_qtr <= 1)) then
365 l_age_distribution1_qtr := 1;
366 elsif ((l_age_qtr >= 2) and (l_age_qtr <= 5)) then
367 l_age_distribution2_qtr := 1;
368 elsif ((l_age_qtr >= 6) and (l_age_qtr <= 10)) then
369 l_age_distribution3_qtr := 1;
370 elsif (l_age_qtr > 10) then
371 l_age_distribution4_qtr := 1;
372 end if;
373
374 else
375 l_past_due_qtr := 0;
376 l_age_qtr := 0;
377 l_age_distribution1_qtr := 0;
378 l_age_distribution2_qtr := 0;
379 l_age_distribution3_qtr := 0;
380 l_age_distribution4_qtr := 0;
381
382 end if;
383
384
385 -- consider yr
386 l_open_yr := 0;
387 l_past_due_yr := 0;
388 l_age_yr := 0;
389 l_age_distribution1_yr := 0;
390 l_age_distribution2_yr := 0;
391 l_age_distribution3_yr := 0;
392 l_age_distribution4_yr := 0;
393
394 if (cur_rec.status_code <> 0) and (cur_rec.status_code <> 11) then
395 l_open_yr := 1;
396 elsif ( (cur_rec.implementation_date is not null)
397 and (cur_rec.implementation_date > l_ent_yr_end) ) then
398 l_open_yr := 1;
399 elsif ( (cur_rec.cancellation_date is not null)
400 and (cur_rec.cancellation_date > l_ent_yr_end) ) then
401 l_open_yr := 1;
402 end if;
403
404 if (cur_rec.open_yr <> 0) then
405 if (cur_rec.need_by_date is not null) and (l_ent_yr_end > cur_rec.need_by_date) then
406 l_past_due_yr := 1;
407 else
408 l_past_due_yr := 0;
409 end if;
410
411 l_age_yr := l_ent_yr_end - cur_rec.initiation_date;
412
413
414 if ((l_age_yr >= 0) and (l_age_yr <= 1)) then
415 l_age_distribution1_yr := 1;
416 elsif ((l_age_yr >= 2) and (l_age_yr <= 5)) then
417 l_age_distribution2_yr := 1;
418 elsif ((l_age_yr >= 6) and (l_age_yr <= 10)) then
419 l_age_distribution3_yr := 1;
420 elsif (l_age_yr > 10) then
421 l_age_distribution4_yr := 1;
422 end if;
423
424 else
425 l_past_due_yr := 0;
426 l_age_yr := 0;
427 l_age_distribution1_yr := 0;
428 l_age_distribution2_yr := 0;
429 l_age_distribution3_yr := 0;
430 l_age_distribution4_yr := 0;
431
432 end if;
433
434
435 -- consider per
436 l_open_per := 0;
437 l_past_due_per := 0;
438 l_age_per := 0;
439 l_age_distribution1_per := 0;
440 l_age_distribution2_per := 0;
441 l_age_distribution3_per := 0;
442 l_age_distribution4_per := 0;
443
444 if (cur_rec.status_code <> 0) and (cur_rec.status_code <> 11) then
445 l_open_per := 1;
446 elsif ( (cur_rec.implementation_date is not null)
447 and (cur_rec.implementation_date > l_ent_period_end) ) then
448 l_open_per := 1;
449 elsif ( (cur_rec.cancellation_date is not null)
450 and (cur_rec.cancellation_date > l_ent_period_end) ) then
451 l_open_per := 1;
452 end if;
453
454 if (cur_rec.open_per <> 0) then
455 if (cur_rec.need_by_date is not null) and (l_ent_period_end > cur_rec.need_by_date) then
456 l_past_due_per := 1;
457 else
458 l_past_due_per := 0;
459 end if;
460
461 l_age_per := l_ent_period_end - cur_rec.initiation_date;
462
463
464 if ((l_age_per >= 0) and (l_age_per <= 1)) then
465 l_age_distribution1_per := 1;
466 elsif ((l_age_per >= 2) and (l_age_per <= 5)) then
467 l_age_distribution2_per := 1;
468 elsif ((l_age_per >= 6) and (l_age_per <= 10)) then
469 l_age_distribution3_per := 1;
470 elsif (l_age_per > 10) then
471 l_age_distribution4_per := 1;
472 end if;
473
474 else
475 l_past_due_per := 0;
476 l_age_per := 0;
477 l_age_distribution1_per := 0;
478 l_age_distribution2_per := 0;
479 l_age_distribution3_per := 0;
483
480 l_age_distribution4_per := 0;
481
482 end if;
484 update rci_open_issues_f
485 set open_per = l_open_per,
486 past_due_per = l_past_due_per,
487 age_per = l_age_per,
488 age_distribution1_per = l_age_distribution1_per,
489 age_distribution2_per = l_age_distribution2_per,
490 age_distribution3_per = l_age_distribution3_per,
491 age_distribution4_per = l_age_distribution4_per,
492 open_yr = l_open_yr,
493 past_due_yr = l_past_due_yr,
494 age_yr = l_age_yr,
495 age_distribution1_yr = l_age_distribution1_yr,
496 age_distribution2_yr = l_age_distribution2_yr,
497 age_distribution3_yr = l_age_distribution3_yr,
498 age_distribution4_yr = l_age_distribution4_yr,
499 open_qtr = l_open_qtr,
500 past_due_qtr = l_past_due_qtr,
501 age_qtr = l_age_qtr,
502 age_distribution1_qtr = l_age_distribution1_qtr,
503 age_distribution2_qtr = l_age_distribution2_qtr,
504 age_distribution3_qtr = l_age_distribution3_qtr,
505 age_distribution4_qtr = l_age_distribution4_qtr
506 where change_id = cur_rec.change_id;
507
508
509 end loop;
510
511 end initial_load;
512
513
514 -- currently incremental - initial, this needs to be reviewed
515 procedure incremental_load(
516 errbuf IN OUT NOCOPY VARCHAR2
517 ,retcode IN OUT NOCOPY NUMBER) is
518 begin
519 initial_load(
520 errbuf => errbuf
521 ,retcode => retcode);
522 end incremental_load;
523
524
525
526 PROCEDURE get_summ_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
527 x_custom_sql OUT NOCOPY VARCHAR2,
528 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
529 is
530 l_query0 VARCHAR2(32767);
531 l_query1 VARCHAR2(32767);
532 l_query2 VARCHAR2(32767);
533 l_query3 VARCHAR2(32767);
534 l_query4 VARCHAR2(32767);
535 l_query22 varchar2(32767);
536 l_act_sqlstmt varchar2(32767);
537 where_flag number := 1;
538 proc varchar2(100);
539 org varchar2(100);
540 l_end_date varchar2(100);
541 l_start_date varchar2(100);
542 l_report_date_julian number;
543
544 l_dumm1 varchar2(100);
545 l_dumm2 varchar2(100);
546 l_dumm3 varchar2(100);
547 l_dumm4 varchar2(100);
548 l_dumm5 varchar2(100);
549 begin
550 l_dumm1 := ',''ALL'' ';
551 l_dumm2 := ',''ALL'' ';
552 l_dumm3 := ',''ALL'' ';
553 l_dumm4 := ',''ALL'' ';
554 l_dumm5 := ',''ALL'' ';
555
556 l_query0 := '';
557 l_query1 := '';
558 l_query2 := '';
559 l_query3 := '';
560 l_query4 := '';
561
562 FOR i in 1..p_param.COUNT LOOP
563
564 IF(p_param(i).parameter_name = 'VIEW_BY' AND
565 p_param(i).parameter_id = 'ORGANIZATION+RCI_ORG_AUDIT') THEN
566 l_dumm1 := ',organization_id ';
567 l_query0 := 'select organization_id as VIEWBYID
568 ,name as VIEWBY
569 ,-1000 as RCI_OPEN_ISSUE_MEASURE1
570 ,sum(past_due) as RCI_OPEN_ISSUE_MEASURE2
571 ,round(((sum(past_due)/count(change_id))*100),2) as RCI_OPEN_ISSUE_MEASURE3
572 ,floor(sum(open_days)/count(change_id)) as RCI_OPEN_ISSUE_MEASURE4
573 ,sum(age_buck1) as RCI_OPEN_ISSUE_MEASURE5
574 ,sum(age_buck2) as RCI_OPEN_ISSUE_MEASURE6
575 ,sum(age_buck3) as RCI_OPEN_ISSUE_MEASURE7
576 ,sum(age_buck4) as RCI_OPEN_ISSUE_MEASURE8
577 /**,organization_id as RCI_ORG_CERT_URL1
578 ,''ALL'' as RCI_ORG_CERT_URL2
579 ,''ALL'' as RCI_ORG_CERT_URL3
580 ,''ALL'' as RCI_ORG_CERT_URL4
581 ,''ALL'' as RCI_ORG_CERT_URL5
582 from ( **/' ;
583
584 l_query1 := ' select distinct roif.organization_id
585 ,aauv.name
586 ,roif.change_id
587 ,eec.change_name
588 ,eec.initiation_date ';
589
590 l_query22 := ' from rci_open_issues_f roif
591 ,eng_engineering_changes eec
592 ,amw_audit_units_v aauv
593 ,amw_certification_vl acv
594 where roif.change_id=eec.change_id
595 and roif.organization_id=aauv.organization_id
596 and roif.fin_cert_id = acv.certification_id ';
597
598 l_query4 := ') group by organization_id,name ';
599 END IF;
600
601 IF(p_param(i).parameter_name = 'VIEW_BY' AND
602 p_param(i).parameter_id = 'RCI_BP_CERT+RCI_BP_PROCESS') THEN
603 l_dumm2 := ',process_id ';
604 l_query0 := 'select process_id as VIEWBYID
605 ,display_name as VIEWBY
606 ,-1000 as RCI_OPEN_ISSUE_MEASURE1
607 ,sum(past_due) as RCI_OPEN_ISSUE_MEASURE2
608 ,round(((sum(past_due)/count(change_id))*100),2) as RCI_OPEN_ISSUE_MEASURE3
609 ,floor(sum(open_days)/count(change_id)) as RCI_OPEN_ISSUE_MEASURE4
610 ,sum(age_buck1) as RCI_OPEN_ISSUE_MEASURE5
611 ,sum(age_buck2) as RCI_OPEN_ISSUE_MEASURE6
612 ,sum(age_buck3) as RCI_OPEN_ISSUE_MEASURE7
613 ,sum(age_buck4) as RCI_OPEN_ISSUE_MEASURE8
614 /**,organization_id as RCI_ORG_CERT_URL1
615 ,''ALL'' as RCI_ORG_CERT_URL2
619 from ( **/';
616 ,''ALL'' as RCI_ORG_CERT_URL3
617 ,''ALL'' as RCI_ORG_CERT_URL4
618 ,''ALL'' as RCI_ORG_CERT_URL5
620
621 l_query1 := ' select distinct roif.process_id
622 ,alrv.display_name
623 ,roif.change_id
624 ,eec.change_name
625 ,eec.initiation_date ';
626
627 l_query22 := ' from rci_open_issues_f roif
628 ,eng_engineering_changes eec
629 ,amw_certification_vl acv
630 ,amw_latest_revisions_v alrv
631 where roif.change_id=eec.change_id
632 and roif.process_id=alrv.process_id
633 and roif.fin_cert_id = acv.certification_id ';
634
635 l_query4 := ') group by process_id,display_name ';
636
637 END IF;
638
639 IF(p_param(i).parameter_name = 'VIEW_BY' AND
640 p_param(i).parameter_id = 'RCI_FS_CERT+RCI_FS_CERT') THEN
641
642 l_query0 := 'select fin_cert_id as VIEWBYID
643 ,certification_name as VIEWBY
644 ,-1000 as RCI_OPEN_ISSUE_MEASURE1
645 ,sum(past_due) as RCI_OPEN_ISSUE_MEASURE2
646 ,round(((sum(past_due)/count(change_id))*100),2) as RCI_OPEN_ISSUE_MEASURE3
647 ,floor(sum(open_days)/count(change_id)) as RCI_OPEN_ISSUE_MEASURE4
648 ,sum(age_buck1) as RCI_OPEN_ISSUE_MEASURE5
649 ,sum(age_buck2) as RCI_OPEN_ISSUE_MEASURE6
650 ,sum(age_buck3) as RCI_OPEN_ISSUE_MEASURE7
651 ,sum(age_buck4) as RCI_OPEN_ISSUE_MEASURE8
652 /**,organization_id as RCI_ORG_CERT_URL1
653 ,''ALL'' as RCI_ORG_CERT_URL2
654 ,''ALL'' as RCI_ORG_CERT_URL3
655 ,''ALL'' as RCI_ORG_CERT_URL4
656 ,''ALL'' as RCI_ORG_CERT_URL5
657 from ( **/';
658
659 l_query1 := ' select distinct roif.fin_cert_id
660 ,acv.certification_name
661 ,roif.change_id
662 ,eec.change_name
663 ,eec.initiation_date ';
664
665 l_query22 := ' from rci_open_issues_f roif
666 ,eng_engineering_changes eec
667 ,amw_certification_vl acv
668 where roif.change_id=eec.change_id
669 and roif.fin_cert_id=acv.certification_id ';
670
671 l_query4 := ') group by fin_cert_id,certification_name ';
672
673 END IF;
674
675 IF(p_param(i).parameter_name = 'VIEW_BY' AND
676 p_param(i).parameter_id = 'RCI_ISSUE_PHASE+RCI_ISSUE_PHASE') THEN
677 l_dumm3 := ',status_code ';
678 l_query0 := 'select status_code as VIEWBYID
679 ,value as VIEWBY
680 ,-1000 as RCI_OPEN_ISSUE_MEASURE1
681 ,sum(past_due) as RCI_OPEN_ISSUE_MEASURE2
682 ,round(((sum(past_due)/count(change_id))*100),2) as RCI_OPEN_ISSUE_MEASURE3
683 ,floor(sum(open_days)/count(change_id)) as RCI_OPEN_ISSUE_MEASURE4
687 ,sum(age_buck4) as RCI_OPEN_ISSUE_MEASURE8
684 ,sum(age_buck1) as RCI_OPEN_ISSUE_MEASURE5
685 ,sum(age_buck2) as RCI_OPEN_ISSUE_MEASURE6
686 ,sum(age_buck3) as RCI_OPEN_ISSUE_MEASURE7
688 /**,organization_id as RCI_ORG_CERT_URL1
689 ,''ALL'' as RCI_ORG_CERT_URL2
690 ,''ALL'' as RCI_ORG_CERT_URL3
691 ,''ALL'' as RCI_ORG_CERT_URL4
692 ,''ALL'' as RCI_ORG_CERT_URL5
693 from ( **/';
694
695 l_query1 := ' select distinct roif.status_code
696 ,ripv.value
697 ,roif.change_id
698 ,eec.change_name
699 ,eec.initiation_date ';
700
701 l_query22 := ' from rci_open_issues_f roif
702 ,eng_engineering_changes eec
703 ,amw_certification_vl acv
704 ,rci_issue_phase_v ripv
705 where roif.change_id=eec.change_id
706 and roif.status_code=ripv.id
707 and roif.fin_cert_id = acv.certification_id ';
708
709 l_query4 := ') group by status_code,value ';
710
711 END IF;
712
716 l_query0 := 'select priority_code as VIEWBYID
713 IF(p_param(i).parameter_name = 'VIEW_BY' AND
714 p_param(i).parameter_id = 'RCI_ISSUE_PRIORITY+RCI_ISSUE_PRIORITY') THEN
715 l_dumm4 := ',priority_code ';
717 ,value as VIEWBY
718 ,-1000 as RCI_OPEN_ISSUE_MEASURE1
719 ,sum(past_due) as RCI_OPEN_ISSUE_MEASURE2
720 ,round(((sum(past_due)/count(change_id))*100),2) as RCI_OPEN_ISSUE_MEASURE3
721 ,floor(sum(open_days)/count(change_id)) as RCI_OPEN_ISSUE_MEASURE4
722 ,sum(age_buck1) as RCI_OPEN_ISSUE_MEASURE5
723 ,sum(age_buck2) as RCI_OPEN_ISSUE_MEASURE6
724 ,sum(age_buck3) as RCI_OPEN_ISSUE_MEASURE7
725 ,sum(age_buck4) as RCI_OPEN_ISSUE_MEASURE8
726 /**,organization_id as RCI_ORG_CERT_URL1
727 ,''ALL'' as RCI_ORG_CERT_URL2
728 ,''ALL'' as RCI_ORG_CERT_URL3
729 ,''ALL'' as RCI_ORG_CERT_URL4
730 ,''ALL'' as RCI_ORG_CERT_URL5
731 from ( **/';
732
733 l_query1 := ' select distinct roif.priority_code
734 ,ripv.value
735 ,roif.change_id
736 ,eec.change_name
737 ,eec.initiation_date ';
738
739 l_query22 := ' from rci_open_issues_f roif
740 ,eng_engineering_changes eec
741 ,amw_certification_vl acv
742 ,RCI_ISSUE_PRIORITY_V ripv
743 where roif.change_id=eec.change_id
744 and roif.priority_code=ripv.id
745 and roif.fin_cert_id = acv.certification_id ';
746
747 l_query4 := ') group by priority_code,value ';
748
749 END IF;
750
751 IF(p_param(i).parameter_name = 'VIEW_BY' AND
752 p_param(i).parameter_id = 'RCI_ISSUE_REASON+RCI_ISSUE_REASON') THEN
753 l_dumm5 := ',reason_code ';
754 l_query0 := 'select reason_code as VIEWBYID
755 ,value as VIEWBY
756 ,-1000 as RCI_OPEN_ISSUE_MEASURE1
757 ,sum(past_due) as RCI_OPEN_ISSUE_MEASURE2
758 ,round(((sum(past_due)/count(change_id))*100),2) as RCI_OPEN_ISSUE_MEASURE3
759 ,floor(sum(open_days)/count(change_id)) as RCI_OPEN_ISSUE_MEASURE4
760 ,sum(age_buck1) as RCI_OPEN_ISSUE_MEASURE5
761 ,sum(age_buck2) as RCI_OPEN_ISSUE_MEASURE6
762 ,sum(age_buck3) as RCI_OPEN_ISSUE_MEASURE7
763 ,sum(age_buck4) as RCI_OPEN_ISSUE_MEASURE8
764 /**,organization_id as RCI_ORG_CERT_URL1
765 ,''ALL'' as RCI_ORG_CERT_URL2
766 ,''ALL'' as RCI_ORG_CERT_URL3
767 ,''ALL'' as RCI_ORG_CERT_URL4
768 ,''ALL'' as RCI_ORG_CERT_URL5
769 from ( **/';
770
771 l_query1 := ' select distinct roif.reason_code
772 ,rirv.value
773 ,roif.change_id
774 ,eec.change_name
775 ,eec.initiation_date ';
776
777 l_query22 := ' from rci_open_issues_f roif
778 ,eng_engineering_changes eec
779 ,amw_certification_vl acv
780 ,RCI_ISSUE_REASON_V rirv
781 where roif.change_id=eec.change_id
782 and roif.reason_code=rirv.id
783 and roif.fin_cert_id = acv.certification_id ';
784
785 l_query4 := ') group by reason_code,value ';
786
787 END IF;
788
789
790 /* IF(p_param(i).parameter_name = 'VIEW_BY' AND
791 p_param(i).parameter_id = 'RCI_FINANCIAL_ACCT+RCI_FINANCIAL_ACCT') THEN
792
793 l_query0 :=
794
795 l_query2 := ' group by (f.account_group_id, f.natural_account_id)';
796
797 END IF;
798 */
799
800 IF(p_param(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT' AND
801 p_param(i).parameter_id is NOT null) THEN
802 l_query3 := l_query3 || ' and roif.FIN_CERT_ID = '||p_param(i).parameter_id;
803 END IF;
804
805 IF(p_param(i).parameter_name = 'ORGANIZATION+RCI_ORG_AUDIT' AND
806 p_param(i).parameter_id is NOT null) THEN
807 l_query3 := l_query3 || ' and roif.organization_id = '||p_param(i).parameter_id;
808 l_dumm1 := ','||p_param(i).parameter_id;
809 END IF;
810
811 IF(p_param(i).parameter_name = 'RCI_BP_CERT+RCI_BP_PROCESS' AND
812 p_param(i).parameter_id is NOT null) THEN
813 l_query3 := l_query3 || ' and roif.process_id = '||p_param(i).parameter_id;
814 l_dumm2 := ','||p_param(i).parameter_id;
815 END IF;
816
817 /* IF(p_param(i).parameter_name = 'RCI_FINANCIAL_ACCT+RCI_FINANCIAL_ACCT' AND
821 */
818 p_param(i).parameter_id is NOT null) THEN
819 l_query1 := l_query1 || ' and natural_account_id = '||p_param(i).parameter_id;
820 END IF;
822
823 IF(p_param(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_STATUS' AND
824 p_param(i).parameter_id is NOT null) THEN
825 l_query3 := l_query3 || ' and acv.certification_status = '||p_param(i).parameter_id;
826 END IF;
827
828 IF(p_param(i).parameter_name = 'RCI_FS_CERT+RCI_FS_CERT_TYPE' AND
829 p_param(i).parameter_id is NOT null) THEN
830 l_query3 := l_query3 || ' and acv.certification_type = '||p_param(i).parameter_id;
831 END IF;
832
833 IF(p_param(i).parameter_name = 'RCI_ISSUE_PHASE+RCI_ISSUE_PHASE' AND
834 p_param(i).parameter_id is NOT null) THEN
835 l_query3 := l_query3 || ' and roif.status_code = '||p_param(i).parameter_id;
836 l_dumm3 := ','||p_param(i).parameter_id;
837 END IF;
838
839 IF(p_param(i).parameter_name = 'RCI_ISSUE_PRIORITY+RCI_ISSUE_PRIORITY' AND
840 p_param(i).parameter_id is NOT null) THEN
841 l_query3 := l_query3 || ' and roif.priority_code = '||p_param(i).parameter_id;
842 l_dumm4 := ','||p_param(i).parameter_id;
843 END IF;
844
845 IF(p_param(i).parameter_name = 'RCI_ISSUE_REASON+RCI_ISSUE_REASON' AND
846 p_param(i).parameter_id is NOT null) THEN
847 l_query3 := l_query3 || ' and roif.reason_code = '||p_param(i).parameter_id;
848 l_dumm5 := ','||p_param(i).parameter_id;
849 END IF;
850
851 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM' AND
852 p_param(i).parameter_id is NOT null) THEN
853 select distinct last_day(to_date(to_char(ent_period_end_date,'YYYYMM'),'YYYYMM'))
854 into l_end_date
855 from fii_time_day
856 where ent_period_id=p_param(i).parameter_id;
857
858 select min(distinct last_day(to_date(to_char(ent_period_start_date,'YYYYMM'),'YYYYMM')))
859 into l_start_date /*gives in the form 30-SEP-06*/
860 from fii_time_day
861 where ent_period_id=p_param(i).parameter_id;
862
863 l_query3 := l_query3 ||' and eec.initiation_date < to_date('''||l_end_date||''',''DD-MON-YYYY'')
864 and (eec.status_code not in (0,11)
865 or (eec.status_code=11 and eec.last_update_date > to_date('''||l_end_date||''',''DD-MON-YYYY'')))';
866
867 END IF;
868
869 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM' AND
870 p_param(i).parameter_id is NOT null) THEN
871 select distinct last_day(to_date(to_char(ent_qtr_end_date,'YYYYMM'),'YYYYMM'))
872 into l_end_date
873 from fii_time_day
874 where ent_qtr_id=p_param(i).parameter_id;
875
876 select min(distinct last_day(to_date(to_char(ent_qtr_start_date,'YYYYMM'),'YYYYMM')))
877 into l_start_date /*gives in the form 30-SEP-06*/
878 from fii_time_day
879 where ent_qtr_id=p_param(i).parameter_id;
880
881 l_query3 := l_query3 ||' and eec.initiation_date < to_date('''||l_end_date||''',''DD-MON-YYYY'')
882 and (eec.status_code not in (0,11)
883 or (eec.status_code=11 and eec.last_update_date > to_date('''||l_end_date||''',''DD-MON-YYYY'')))';
884
885 END IF;
886
887 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM' AND
888 p_param(i).parameter_id is NOT null) THEN
889 select min(distinct last_day(to_date(to_char(ent_year_end_date,'YYYYMM'),'YYYYMM')))
890 into l_end_date /*gives in the form 30-SEP-06*/
891 from fii_time_day
892 where ent_year_id=p_param(i).parameter_id;
893
897 where ent_year_id=p_param(i).parameter_id;
894 select min(distinct last_day(to_date(to_char(ent_year_start_date,'YYYYMM'),'YYYYMM')))
895 into l_start_date /*gives in the form 30-SEP-06*/
896 from fii_time_day
898
899 l_query3 := l_query3 ||' and eec.initiation_date < to_date('''||l_end_date||''',''DD-MON-YYYY'')
900 and (eec.status_code not in (0,11)
901 or (eec.status_code=11 and eec.last_update_date > to_date('''||l_end_date||''',''DD-MON-YYYY'')))';
902
903 END IF;
904
905
906 END LOOP;
907
908 /** 09.20.2006 npanandi: Nilesh's version**/
909 l_query2 := ' ,decode(eec.need_by_date,null,trunc(to_date('''||l_end_date||''',''DD-MON-YYYY'')-eec.initiation_date),
910 trunc(to_date('''||l_end_date||''',''DD-MON-YYYY'')-eec.need_by_date)) past_due_days
911 ,case when (to_number(to_char(to_date('''||l_end_date||''',''DD-MON-YYYY''),''J'')) > to_number(to_char(eec.need_by_date,''J'')))
912 then 1 else 0 end past_due
913 ,trunc(last_day(to_date('''||l_end_date||''',''DD-MON-YYYY''))-eec.initiation_date) open_days
914 ,case when (to_number(to_char(to_date('''||l_end_date||''',''DD-MON-YYYY''),''J'')) - to_number(to_char(eec.need_by_date,''J'')) = 1)
915 then 1 else 0 end age_buck1
916 ,case when (to_number(to_char(to_date('''||l_end_date||''',''DD-MON-YYYY''),''J'')) - to_number(to_char(eec.need_by_date,''J'')) between 2 and 5)
917 then 1 else 0 end age_buck2
918 ,case when (to_number(to_char(to_date('''||l_end_date||''',''DD-MON-YYYY''),''J'')) - to_number(to_char(eec.need_by_date,''J'')) between 6 and 10)
919 then 1 else 0 end age_buck3
920 ,case when (to_number(to_char(to_date('''||l_end_date||''',''DD-MON-YYYY''),''J'')) - to_number(to_char(eec.need_by_date,''J'')) > 10)
921 then 1 else 0 end age_buck4 ';
922
923 /*** 09.20.2006 npanandi: commenting this out -- not sure why this is needed**/
924 /***l_query3 := l_query3 ||' and (eec.implementation_date is null or (eec.implementation_date > to_date('''||v_yyyymm||''',''YYYYMM'')))
925 and ((eec.status_code not in (0,11)) or (eec.status_code=11 and eec.last_update_date > last_day(to_date('||v_yyyymm||',''YYYYMM''))))';
926 ***/
927
928 l_query0 := l_query0||' '||l_dumm1||' as RCI_ORG_CERT_URL1 '||
929 l_dumm2||' as RCI_ORG_CERT_URL2 '||
930 l_dumm3||' as RCI_ORG_CERT_URL3 '||
934 /** 09.18.2006 npanandi: added SQL below to handle order_by_clause -- bug 5510667 **/
931 l_dumm4||' as RCI_ORG_CERT_URL4 '||
932 l_dumm5||' as RCI_ORG_CERT_URL5 from ( ';
933
935 l_act_sqlstmt := 'select VIEWBYID,VIEWBY,RCI_OPEN_ISSUE_MEASURE1,RCI_OPEN_ISSUE_MEASURE2
936 ,RCI_OPEN_ISSUE_MEASURE3,RCI_OPEN_ISSUE_MEASURE4
937 ,RCI_OPEN_ISSUE_MEASURE5,RCI_OPEN_ISSUE_MEASURE6
938 ,RCI_OPEN_ISSUE_MEASURE7,RCI_OPEN_ISSUE_MEASURE8
939 ,RCI_ORG_CERT_URL1,RCI_ORG_CERT_URL2,RCI_ORG_CERT_URL3
940 ,RCI_ORG_CERT_URL4,RCI_ORG_CERT_URL5
941 from (select t.*
942 ,(rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last) - 1) col_rank
943 from ( '||l_query0||l_query1||l_query2||l_query22||l_query3||l_query4||'
944 ) t ) a
945 order by a.col_rank ';
946
947 /**x_custom_sql := l_query0||l_query1||l_query2||l_query22||l_query3||l_query4;**/
948 x_custom_sql := l_act_sqlstmt;
949
950 end;
951
952
953
954 end RCI_OPEN_ISSUE_SUMM_PKG;