[Home] [Help]
PACKAGE BODY: APPS.PA_BIS_SUMMARY
Source
1 PACKAGE BODY PA_BIS_SUMMARY AS
2 /* $Header: PABISUMB.pls 120.1 2005/08/19 16:16:09 mwasowic noship $ */
3
4 --------------------------------------
5 -- FUNCTION/PROCEDURE IMPLEMENTATIONS
6 --
7
8 Procedure SUMMARIZE_BIS(errbuf OUT NOCOPY varchar2,ret_code OUT NOCOPY varchar2) IS --File.Sql.39 bug 4440895
9
10 x_org_id NUMBER(15);
11 v_pa_org_start_date_high date;
12 v_pa_org_start_date_low date;
13 v_gl_org_start_date_high date;
14 v_gl_org_start_date_low date;
15 --=================================================== IGOR
16 -- insert 1:
17 cursor c_prd_drills1 is
18 select
19 ORGANIZATION_ID ORGANIZATION_ID,
20 PROJECT_ID PROJECT_ID,
21 PERIOD PERIOD,
22 ACC_PTYPE ACC_PTYPE,
23 S_DATE S_DATE,
24 E_DATE E_DATE,
25 SUM(NVL(ACT_COST,0)) ACT_COST,
26 SUM(NVL(ACT_REVENUE,0)) ACT_REVENUE,
27 SUM(NVL(BUD_COST,0)) BUD_COST,
28 SUM(NVL(BUD_REVENUE,0)) BUD_REVENUE
29 from
30 (
31 SELECT
32 P.CARRYING_OUT_ORGANIZATION_ID ORGANIZATION_ID
33 , P.PROJECT_ID PROJECT_ID
34 , TA.GL_PERIOD PERIOD
35 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
36 , PER.START_DATE S_DATE
37 , PER.END_DATE E_DATE
38 , TA.TOT_BURDENED_COST ACT_COST
39 , TA.TOT_REVENUE ACT_REVENUE
40 , 0 BUD_COST
41 , 0 BUD_REVENUE
42 FROM pa_projects p
43 , pa_project_types pt
44 , pa_txn_accum ta
45 , pa_implementations i
46 , gl_period_statuses per
47 WHERE i.accumulation_period_type = 'GL'
48 AND p.project_type = pt.project_type
49 AND pt.project_type_class_code = 'CONTRACT'
50 AND ta.project_id = p.project_id
51 AND ta.gl_period = per.period_name
52 AND per.application_id = PA_Period_Process_Pkg.Application_ID
53 -- Changed from 101 for PA/GL Period Enhancements
54 AND per.set_of_books_id = i.set_of_books_id
55 AND per.adjustment_period_flag = 'N'
56 AND per.start_date <= v_gl_org_start_date_high
57 AND per.start_date > v_gl_org_start_date_low
58 UNION ALL
59 SELECT
60 P.CARRYING_OUT_ORGANIZATION_ID ORGANIZATION_ID
61 , P.PROJECT_ID PROJECT_ID
62 , TA.PA_PERIOD PERIOD
63 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
64 , PER.START_DATE S_DATE
65 , PER.END_DATE E_DATE
66 , TA.TOT_BURDENED_COST ACT_COST
67 , TA.TOT_REVENUE ACT_REVENUE
68 , 0 BUD_COST
69 , 0 BUD_REVENUE
70 FROM pa_projects p
71 , pa_project_types pt
72 , pa_txn_accum ta
73 , pa_implementations i
74 , pa_periods per
75 WHERE i.accumulation_period_type = 'PA'
76 AND p.project_type = pt.project_type
77 AND pt.project_type_class_code = 'CONTRACT'
78 AND ta.project_id = p.project_id
79 AND ta.pa_period = per.period_name
80 AND per.start_date <= v_pa_org_start_date_high
81 AND per.start_date > v_pa_org_start_date_low
82 UNION ALL
83 SELECT
84 P.CARRYING_OUT_ORGANIZATION_ID ORGANIZATION_ID
85 , P.PROJECT_ID PROJECT_ID
86 , BPA.GL_PERIOD_NAME PERIOD
87 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
88 , PER.START_DATE S_DATE
89 , PER.END_DATE E_DATE
90 , 0 ACT_COST
91 , 0 ACT_REVENUE
92 , BPA.BASE_BURDENED_COST BUD_COST
93 , BPA.BASE_REVENUE BUD_REVENUE
94 FROM pa_projects p
95 , pa_project_types pt
96 , pa_budget_by_pa_period_v bpa
97 , pa_implementations I
98 , gl_period_statuses per
99 WHERE i.accumulation_period_type = 'GL'
100 AND p.project_type = pt.project_type
101 AND pt.project_type_class_code = 'CONTRACT'
102 AND p.project_id = bpa.project_id
103 AND bpa.budget_type_code in ('AR','AC')
104 AND bpa.gl_period_name = per.period_name
105 AND per.application_id = PA_Period_Process_Pkg.Application_ID
106 -- Changed from 101 for PA/GL Period Enhancements
107 AND per.set_of_books_id = i.set_of_books_id
108 AND per.adjustment_period_flag = 'N'
109 AND per.start_date <= v_gl_org_start_date_high
110 AND per.start_date > v_gl_org_start_date_low
111 UNION ALL
112 SELECT
113 P.CARRYING_OUT_ORGANIZATION_ID ORGANIZATION_ID
114 , P.PROJECT_ID PROJECT_ID
115 , BPA.PA_PERIOD PERIOD
116 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
117 , BPA.PERIOD_START_DATE S_DATE
118 , BPA.PERIOD_END_DATE E_DATE
119 , 0 ACT_COST
120 , 0 ACT_REVENUE
121 , BPA.BASE_BURDENED_COST BUD_COST
122 , BPA.BASE_REVENUE BUD_REVENUE
123 FROM pa_projects p
124 , pa_project_types pt
125 , pa_budget_by_pa_period_v bpa
126 , pa_implementations i
127 WHERE i.accumulation_period_type = 'PA'
128 AND p.project_type = pt.project_type
129 AND pt.project_type_class_code = 'CONTRACT'
130 AND p.project_id = bpa.project_id
131 AND bpa.budget_type_code in ('AR','AC')
132 AND bpa.period_start_date <= v_pa_org_start_date_high
133 AND bpa.period_start_date > v_pa_org_start_date_low
134 UNION ALL
135 SELECT
136 P.CARRYING_OUT_ORGANIZATION_ID ORGANIZATION_ID
137 , P.PROJECT_ID PROJECT_ID
138 , PER.PERIOD_NAME PERIOD
139 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
140 , PER.START_DATE S_DATE
141 , PER.END_DATE E_DATE
142 , 0 ACT_COST
143 , 0 ACT_REVENUE
144 , 0 BUD_COST
145 , 0 BUD_REVENUE
146 FROM pa_projects p
147 , pa_project_types pt
148 , pa_implementations i
149 , gl_period_statuses per
150 WHERE i.accumulation_period_type = 'GL'
151 AND p.project_type = pt.project_type
152 AND pt.project_type_class_code = 'CONTRACT'
153 AND per.application_id = PA_Period_Process_Pkg.Application_ID
154 -- Changed from 101 for PA/GL Period Enhancements
155 AND per.set_of_books_id = i.set_of_books_id
156 AND per.adjustment_period_flag = 'N'
157 AND per.start_date <= v_gl_org_start_date_high
158 AND per.start_date > v_gl_org_start_date_low
159 UNION ALL
160 SELECT
161 P.CARRYING_OUT_ORGANIZATION_ID ORGANIZATION_ID
162 , P.PROJECT_ID PROJECT_ID
163 , PER.PERIOD_NAME PERIOD
164 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
165 , PER.START_DATE S_DATE
166 , PER.END_DATE E_DATE
167 , 0 ACT_COST
168 , 0 ACT_REVENUE
169 , 0 BUD_COST
170 , 0 BUD_REVENUE
171 FROM pa_projects p
172 , pa_project_types pt
173 , pa_implementations i
174 , pa_periods per
175 WHERE i.accumulation_period_type = 'PA'
176 AND p.project_type = pt.project_type
177 AND pt.project_type_class_code = 'CONTRACT'
178 AND per.start_date <= v_pa_org_start_date_high
179 AND per.start_date > v_pa_org_start_date_low
180 )
181 group by
182 ORGANIZATION_ID,
183 PROJECT_ID,
184 PERIOD,
185 ACC_PTYPE,
186 S_DATE,
187 E_DATE
188 ;
189 --================================================IGOR
190 -- loop 1
191 cursor c_prd_drills2 is
192 select
193 CCATEGORY CCATEGORY,
194 CCODE CCODE,
195 PROJECT_ID PROJECT_ID,
196 PERIOD PERIOD,
197 ACC_PTYPE ACC_PTYPE,
198 S_DATE S_DATE,
199 E_DATE E_DATE,
200 SUM(NVL(ACT_COST,0)) ACT_COST,
201 SUM(NVL(ACT_REVENUE,0)) ACT_REVENUE,
202 SUM(NVL(BUD_COST,0)) BUD_COST,
203 SUM(NVL(BUD_REVENUE,0)) BUD_REVENUE
204 from
205 (
206 SELECT
207 PC.CLASS_CATEGORY CCATEGORY,
208 PC.CLASS_CODE CCODE,
209 P.PROJECT_ID PROJECT_ID,
210 PER.PERIOD_NAME PERIOD,
211 I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE,
212 PER.START_DATE S_DATE,
213 PER.END_DATE E_DATE,
214 0 ACT_COST,
215 0 ACT_REVENUE,
216 0 BUD_COST,
217 0 BUD_REVENUE
218 FROM pa_projects p ,
219 pa_project_types pt ,
220 pa_implementations i ,
221 pa_periods per ,
222 pa_class_categories cc ,
223 pa_project_classes pc
224 WHERE
225 i.accumulation_period_type = 'PA' AND
226 p.project_type = pt.project_type AND pt.project_type_class_code =
227 'CONTRACT' AND p.project_id = pc.project_id AND pc.class_category =
228 cc.class_category AND cc.pick_one_code_only_flag = 'Y' AND
229 per.start_date <= v_pa_org_start_date_high
230 AND per.start_date > v_pa_org_start_date_low
231 UNION ALL
232 SELECT
233 PC.CLASS_CATEGORY CCATEGORY,
234 PC.CLASS_CODE CCODE,
235 P.PROJECT_ID PROJECT_ID,
236 TA.PA_PERIOD PERIOD,
237 I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE,
238 PER.START_DATE S_DATE,
239 PER.END_DATE E_DATE,
240 TA.TOT_BURDENED_COST ACT_COST,
241 TA.TOT_REVENUE ACT_REVENUE,
242 0 BUD_COST,
243 0 BUD_REVENUE
244 FROM
245 pa_projects p
246 , pa_project_types pt
247 , pa_txn_accum ta
248 , pa_implementations i ,
249 pa_periods per ,
250 pa_class_categories cc ,
251 pa_project_classes pc
252 WHERE
253 i.accumulation_period_type = 'PA'
254 AND p.project_type = pt.project_type
255 AND
256 pt.project_type_class_code = 'CONTRACT'
257 AND ta.project_id = p.project_id
258 AND ta.pa_period = per.period_name
259 AND p.project_id = pc.project_id AND
260 pc.class_category = cc.class_category
261 AND cc.pick_one_code_only_flag = 'Y'
262 AND per.start_date <= v_pa_org_start_date_high
263 AND per.start_date > v_pa_org_start_date_low
264 UNION ALL
265 SELECT
266 PC.CLASS_CATEGORY CCATEGORY,
267 PC.CLASS_CODE CCODE,
268 P.PROJECT_ID PROJECT_ID,
269 BPA.PA_PERIOD PERIOD,
270 I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE,
271 BPA.PERIOD_START_DATE S_DATE,
272 BPA.PERIOD_END_DATE E_DATE,
273 0 ACT_COST,
274 0 ACT_REVENUE,
275 BPA.BASE_BURDENED_COST BUD_COST,
276 BPA.BASE_REVENUE BUD_REVENUE
277 FROM pa_projects p ,
278 pa_project_types pt ,
279 pa_budget_by_pa_period_v bpa ,
280 pa_implementations i ,
281 pa_class_categories cc ,
282 pa_project_classes pc
283 WHERE i.accumulation_period_type = 'PA'
284 AND p.project_type =
285 pt.project_type
286 AND pt.project_type_class_code = 'CONTRACT'
287 AND p.project_id = bpa.project_id
288 AND bpa.budget_type_code in ('AR','AC')
289 AND
290 p.project_id = pc.project_id
291 AND pc.class_category = cc.class_category AND
292 cc.pick_one_code_only_flag = 'Y'
293 AND bpa.period_start_date <= v_pa_org_start_date_high
294 AND bpa.period_start_date > v_pa_org_start_date_low
295 )
296 GROUP BY
297 CCATEGORY,
298 CCODE,
299 PROJECT_ID,
300 PERIOD,
301 ACC_PTYPE,
302 S_DATE,
303 E_DATE;
304 --=================================================== IGOR
305 -- loop2:
306 cursor c_prd_drills3 is
307 select
308 CCATEGORY CCATEGORY,
309 CCODE CCODE,
310 PROJECT_ID PROJECT_ID,
311 PERIOD PERIOD,
312 ACC_PTYPE ACC_PTYPE,
313 S_DATE S_DATE,
314 E_DATE E_DATE,
315 SUM(NVL(ACT_COST,0)) ACT_COST,
316 SUM(NVL(ACT_REVENUE,0)) ACT_REVENUE,
317 SUM(NVL(BUD_COST,0)) BUD_COST,
318 SUM(NVL(BUD_REVENUE,0)) BUD_REVENUE
319 from
320 (
321 SELECT
322 PC.CLASS_CATEGORY CCATEGORY
323 , PC.CLASS_CODE CCODE
324 , P.PROJECT_ID PROJECT_ID
325 , PER.PERIOD_NAME PERIOD
326 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
327 , PER.START_DATE S_DATE
328 , PER.END_DATE E_DATE
329 , 0 ACT_COST
330 , 0 ACT_REVENUE
331 , 0 BUD_COST
332 , 0 BUD_REVENUE
333 FROM pa_projects p
334 , pa_project_types pt
335 , pa_implementations i
336 , gl_period_statuses per
337 , pa_class_categories cc
338 , pa_project_classes pc
339 WHERE i.accumulation_period_type = 'GL'
340 AND p.project_type = pt.project_type
341 AND pt.project_type_class_code = 'CONTRACT'
342 AND per.application_id = PA_Period_Process_Pkg.Application_ID
343 -- Changed from 101 for PA/GL Period Enhancements
344 AND per.set_of_books_id = i.set_of_books_id
345 AND p.project_id = pc.project_id
346 AND pc.class_category = cc.class_category
347 AND cc.pick_one_code_only_flag = 'Y'
348 AND per.adjustment_period_flag = 'N'
349 AND per.start_date <= v_gl_org_start_date_high
350 AND per.start_date > v_gl_org_start_date_low
351 UNION ALL
352 SELECT
353 PC.CLASS_CATEGORY CCATEGORY
354 , PC.CLASS_CODE CCODE
355 , P.PROJECT_ID PROJECT_ID
356 , TA.GL_PERIOD PERIOD
357 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
358 , PER.START_DATE S_DATE
359 , PER.END_DATE E_DATE
360 , TA.TOT_BURDENED_COST ACT_COST
361 , TA.TOT_REVENUE ACT_REVENUE
362 , 0 BUD_COST
363 , 0 BUD_REVENUE
364 FROM pa_projects p
365 , pa_project_types pt
366 , pa_txn_accum ta
367 , pa_implementations i
368 , gl_period_statuses per
369 , pa_class_categories cc
370 , pa_project_classes pc
371 WHERE i.accumulation_period_type = 'GL'
372 AND p.project_type = pt.project_type
373 AND pt.project_type_class_code = 'CONTRACT'
374 AND ta.project_id = p.project_id
375 AND ta.gl_period = per.period_name
376 AND per.application_id = PA_Period_Process_Pkg.Application_ID
377 -- Changed from 101 for PA/GL Period Enhancements
378 AND per.set_of_books_id = i.set_of_books_id
379 AND p.project_id = pc.project_id
380 AND pc.class_category = cc.class_category
381 AND cc.pick_one_code_only_flag = 'Y'
382 AND per.adjustment_period_flag = 'N'
383 AND per.start_date <= v_gl_org_start_date_high
384 AND per.start_date > v_gl_org_start_date_low
385 UNION ALL
386 SELECT
387 PC.CLASS_CATEGORY CCATEGORY
388 , PC.CLASS_CODE CCODE
389 , P.PROJECT_ID PROJECT_ID
390 , BPA.GL_PERIOD_NAME PERIOD
391 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
392 , PER.START_DATE S_DATE
393 , PER.END_DATE E_DATE
394 , 0 ACT_COST
395 , 0 ACT_REVENUE
396 , BPA.BASE_BURDENED_COST BUD_COST
397 , BPA.BASE_REVENUE BUD_REVENUE
398 FROM pa_projects p
399 , pa_project_types pt
400 , pa_budget_by_pa_period_v bpa
401 , pa_implementations I
402 , gl_period_statuses per
403 , pa_class_categories cc
404 , pa_project_classes pc
405 WHERE i.accumulation_period_type = 'GL'
406 AND p.project_type = pt.project_type
407 AND pt.project_type_class_code = 'CONTRACT'
408 AND p.project_id = bpa.project_id
412 -- Changed from 101 for PA/GL Period Enhancements
409 AND bpa.budget_type_code in ('AR','AC')
410 AND bpa.gl_period_name = per.period_name
411 AND per.application_id = PA_Period_Process_Pkg.Application_ID
413 AND per.set_of_books_id = i.set_of_books_id
414 AND p.project_id = pc.project_id
415 AND pc.class_category = cc.class_category
416 AND cc.pick_one_code_only_flag = 'Y'
417 AND per.adjustment_period_flag = 'N'
418 AND per.start_date <= v_gl_org_start_date_high
419 AND per.start_date > v_gl_org_start_date_low
420 )
421 GROUP BY
422 CCATEGORY,
423 CCODE,
424 PROJECT_ID,
425 PERIOD,
426 ACC_PTYPE,
427 S_DATE,
428 E_DATE;
429
430 --=================================================== IGOR
431 -- insert 6:
432 cursor c_prd_drills4 is
433 SELECT
434 PNAME,
435 ACC_PTYPE,
436 S_DATE,
437 E_DATE,
438 SUM(NVL(ACT_COST,0)) ACT_COST,
439 SUM(NVL(ACT_REVENUE,0)) ACT_REVENUE,
440 SUM(NVL(BUD_COST,0)) BUD_COST,
441 SUM(NVL(BUD_REVENUE,0)) BUD_REVENUE
442 from
443 (
444 SELECT
445 PER.PERIOD_NAME PNAME
446 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
447 , PER.START_DATE S_DATE
448 , PER.END_DATE E_DATE
449 , 0 ACT_COST
450 , 0 ACT_REVENUE
451 , 0 BUD_COST
452 , 0 BUD_REVENUE
453 FROM pa_periods per
454 , pa_implementations i
455 WHERE i.accumulation_period_type = 'PA'
456 AND per.start_date <= v_pa_org_start_date_high
457 AND per.start_date > v_pa_org_start_date_low
458 UNION ALL
459 SELECT
460 TA.PA_PERIOD PNAME
461 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
462 , PER.START_DATE S_DATE
463 , PER.END_DATE E_DATE
464 , TA.TOT_BURDENED_COST ACT_COST
465 , TA.TOT_REVENUE ACT_REVENUE
466 , 0 BUD_COST
467 , 0 BUD_REVENUE
468 FROM pa_projects p
469 , pa_project_types pt
470 , pa_txn_accum ta
471 , pa_implementations i
472 , pa_periods per
473 WHERE i.accumulation_period_type = 'PA'
474 AND p.project_type = pt.project_type
475 AND pt.project_type_class_code = 'CONTRACT'
476 AND ta.project_id = p.project_id
477 AND ta.pa_period = per.period_name
478 AND per.start_date <= v_pa_org_start_date_high
479 AND per.start_date > v_pa_org_start_date_low
480 UNION ALL
481 SELECT
482 BPA.PA_PERIOD PNAME
483 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
484 , BPA.PERIOD_START_DATE S_DATE
485 , BPA.PERIOD_END_DATE E_DATE
486 , 0 ACT_COST
487 , 0 ACT_REVENUE
488 , BPA.BASE_BURDENED_COST BUD_COST
489 , BPA.BASE_REVENUE BUD_REVENUE
490 FROM pa_projects p
491 , pa_project_types pt
492 , pa_budget_by_pa_period_v bpa
493 , pa_implementations i
494 WHERE i.accumulation_period_type = 'PA'
495 AND p.project_type = pt.project_type
496 AND pt.project_type_class_code = 'CONTRACT'
497 AND p.project_id = bpa.project_id
498 AND bpa.budget_type_code in ('AR','AC')
499 AND bpa.period_start_date <= v_pa_org_start_date_high
500 AND bpa.period_start_date > v_pa_org_start_date_low
501 UNION ALL
502 SELECT
503 PER.PERIOD_NAME PNAME
504 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
505 , PER.START_DATE S_DATE
506 , PER.END_DATE E_DATE
507 , 0 ACT_COST
508 , 0 ACT_REVENUE
509 , 0 BUD_COST
510 , 0 BUD_REVENUE
511 FROM pa_implementations i
512 , gl_period_statuses per
513 WHERE i.accumulation_period_type = 'GL'
514 AND per.application_id = PA_Period_Process_Pkg.Application_ID
515 -- Changed from 101 for PA/GL Period Enhancements
516 AND per.set_of_books_id = i.set_of_books_id
517 AND per.adjustment_period_flag = 'N'
518 AND per.start_date <= v_gl_org_start_date_high
519 AND per.start_date > v_gl_org_start_date_low
520 UNION ALL
521 SELECT
522 TA.GL_PERIOD PNAME
523 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
524 , PER.START_DATE S_DATE
525 , PER.END_DATE E_DATE
526 , TA.TOT_BURDENED_COST ACT_COST
527 , TA.TOT_REVENUE ACT_REVENUE
528 , 0 BUD_COST
529 , 0 BUD_REVENUE
530 FROM pa_projects p
531 , pa_project_types pt
532 , pa_txn_accum ta
533 , pa_implementations i
534 , gl_period_statuses per
535 WHERE i.accumulation_period_type = 'GL'
536 AND p.project_type = pt.project_type
537 AND pt.project_type_class_code = 'CONTRACT'
538 AND ta.project_id = p.project_id
539 AND ta.gl_period = per.period_name
540 AND per.application_id = PA_Period_Process_Pkg.Application_ID
541 -- Changed from 101 for PA/GL Period Enhancements
542 AND per.set_of_books_id = i.set_of_books_id
543 AND per.adjustment_period_flag = 'N'
544 AND per.start_date <= v_gl_org_start_date_high
545 AND per.start_date > v_gl_org_start_date_low
546 UNION ALL
547 SELECT
548 BPA.GL_PERIOD_NAME PNAME
549 , I.ACCUMULATION_PERIOD_TYPE ACC_PTYPE
550 , PER.START_DATE S_DATE
551 , PER.END_DATE E_DATE
552 , 0 ACT_COST
553 , 0 ACT_REVENUE
554 , BPA.BASE_BURDENED_COST BUD_COST
555 , BPA.BASE_REVENUE BUD_REVENUE
556 FROM pa_projects p
557 , pa_project_types pt
558 , pa_budget_by_pa_period_v bpa
562 AND p.project_type = pt.project_type
559 , pa_implementations i
560 , gl_period_statuses per
561 WHERE i.accumulation_period_type = 'GL'
563 AND pt.project_type_class_code = 'CONTRACT'
564 AND p.project_id = bpa.project_id
565 AND bpa.budget_type_code in ('AR','AC')
566 AND bpa.gl_period_name = per.period_name
567 AND per.application_id = PA_Period_Process_Pkg.Application_ID
568 -- Changed from 101 for PA/GL Period Enhancements
569 AND per.set_of_books_id = i.set_of_books_id
570 AND per.adjustment_period_flag = 'N'
571 AND per.start_date <= v_gl_org_start_date_high
572 AND per.start_date > v_gl_org_start_date_low
573 )
574 GROUP BY
575 PNAME,
576 ACC_PTYPE,
577 S_DATE,
578 E_DATE;
579 --=================================================== IGOR
580
581 prd_drills_rec1 c_prd_drills1%rowtype;
582 prd_drills_rec2 c_prd_drills2%rowtype;
583 prd_drills_rec3 c_prd_drills3%rowtype;
584 prd_drills_rec4 c_prd_drills4%rowtype;
585
586 BEGIN
587
588 Select org_id into x_org_id from pa_implementations;
589
590 -- high date for pa_org:
591 select per2.start_date
592 into v_pa_org_start_date_high
593 from pa_periods per2
594 where per2.current_pa_period_flag = 'Y';
595
596 -- low date for pa_org:
597 /* Removed the pa_implementations join from the main query and added as subquery Bug # 2634995*/
598 select DECODE(gl_pt2.number_per_fiscal_year, 52,
599 (ADD_MONTHS(per2.start_date, -3)), 26,
600 (ADD_MONTHS(per2.start_date, -6)),12,
601 (ADD_MONTHS(per2.start_date, -12)),
602 (ADD_MONTHS(per2.start_date, -3)))
603 into v_pa_org_start_date_low
604 from
605 pa_periods per2,
606 --pa_implementations i2,
607 gl_period_types gl_pt2
608 where
609 per2.current_pa_period_flag = 'Y'
610 --and i2.pa_period_type = gl_pt2.period_type
611 and gl_pt2.period_type = (select i2.pa_period_type
612 from pa_implementations i2);
613
614 -- low date for gl_org:
615 select ADD_MONTHS(gl_per.start_date, -12)
616 into v_gl_org_start_date_low
617 from gl_period_statuses gl_per
618 , pa_implementations pa_i
619 , pa_periods pa_per
620 where gl_per.application_id = PA_Period_Process_Pkg.Application_ID
621 -- Changed from 101 for PA/GL Period Enhancements
622 and gl_per.set_of_books_id = pa_i.set_of_books_id
623 and pa_per.gl_period_name = gl_per.period_name
624 and pa_per.current_pa_period_flag = 'Y';
625
626 -- high date for gl_org:
627 select gl_per.start_date
628 into v_gl_org_start_date_high
629 from gl_period_statuses gl_per
630 , pa_implementations pa_i
631 , pa_periods pa_per
632 where gl_per.application_id = PA_Period_Process_Pkg.Application_ID
633 -- Changed from 101 for PA/GL Period Enhancements
634 and gl_per.set_of_books_id = pa_i.set_of_books_id
635 and pa_per.gl_period_name = gl_per.period_name
636 and pa_per.current_pa_period_flag = 'Y';
637
638
639
640 DELETE FROM PA_BIS_PRJ_BY_PRD_DRILLS;
641 DELETE FROM PA_BIS_PRJ_TO_DATE_DRILLS;
642 DELETE FROM PA_BIS_TO_DATE_DRILLS;
643 DELETE FROM PA_BIS_TOTALS_BY_PRD;
644 DELETE FROM PA_BIS_TOTALS_TO_DATE;
645
646 COMMIT;
647
648 FOR prd_drills_rec1 IN c_prd_drills1 LOOP
649
650 INSERT INTO PA_BIS_PRJ_BY_PRD_DRILLS (
651 drilldown_type
652 , amount_type_code
653 , drilldown_key1
654 , project_id
655 , period_name
656 , accumulation_period_type
657 , start_date
658 , end_date
659 , sort_order
660 , org_id
661 , actual
662 , budget)
663 VALUES
664 ( 'ORGANIZATION'
665 ,'REVENUE'
666 ,prd_drills_rec1.ORGANIZATION_ID
667 ,prd_drills_rec1.PROJECT_ID
668 ,prd_drills_rec1.PERIOD
669 ,prd_drills_rec1.ACC_PTYPE
670 ,prd_drills_rec1.S_DATE
671 ,prd_drills_rec1.E_DATE
672 ,TO_CHAR(NULL)
673 ,x_org_id
674 ,prd_drills_rec1.ACT_REVENUE
675 ,prd_drills_rec1.BUD_REVENUE);
676
677 INSERT INTO PA_BIS_PRJ_BY_PRD_DRILLS (
678 drilldown_type
679 , amount_type_code
680 , drilldown_key1
681 , project_id
682 , period_name
683 , accumulation_period_type
684 , start_date
685 , end_date
686 , sort_order
687 , org_id
688 , actual
689 , budget)
690 VALUES
691 ( 'ORGANIZATION'
692 ,'COST'
693 ,prd_drills_rec1.ORGANIZATION_ID
694 ,prd_drills_rec1.PROJECT_ID
695 ,prd_drills_rec1.PERIOD
696 ,prd_drills_rec1.ACC_PTYPE
697 ,prd_drills_rec1.S_DATE
698 ,prd_drills_rec1.E_DATE
699 ,TO_CHAR(NULL)
700 ,x_org_id
701 ,prd_drills_rec1.ACT_COST
702 ,prd_drills_rec1.BUD_COST);
703
704 COMMIT;
705 END LOOP;
706
707 COMMIT;
708
709
710
711
712
713
714 FOR prd_drills_rec2 IN c_prd_drills2 LOOP
715 INSERT INTO PA_BIS_PRJ_BY_PRD_DRILLS
716 (
717 drilldown_type
721 , project_id
718 , amount_type_code
719 , drilldown_key1
720 , drilldown_key2
722 , period_name
723 , accumulation_period_type
724 , start_date
725 , end_date
726 , sort_order
727 , org_id
728 , actual
729 , budget)
730 VALUES
731 ( 'CLASS_CATEGORY'
732 ,'COST'
733 ,prd_drills_rec2.CCATEGORY
734 ,prd_drills_rec2.CCODE
735 ,prd_drills_rec2.PROJECT_ID
736 ,prd_drills_rec2.PERIOD
737 ,prd_drills_rec2.ACC_PTYPE
738 ,prd_drills_rec2.S_DATE
739 ,prd_drills_rec2.E_DATE
740 ,TO_CHAR(NULL)
741 ,x_org_id
742 ,prd_drills_rec2.ACT_COST
743 ,prd_drills_rec2.BUD_COST);
744
745 INSERT INTO PA_BIS_PRJ_BY_PRD_DRILLS
746 (
747 drilldown_type
748 , amount_type_code
749 , drilldown_key1
750 , drilldown_key2
751 , project_id
752 , period_name
753 , accumulation_period_type
754 , start_date
755 , end_date
756 , sort_order
757 , org_id
758 , actual
759 , budget)
760 VALUES
761 ( 'CLASS_CATEGORY'
762 ,'REVENUE'
763 ,prd_drills_rec2.CCATEGORY
764 ,prd_drills_rec2.CCODE
765 ,prd_drills_rec2.PROJECT_ID
766 ,prd_drills_rec2.PERIOD
767 ,prd_drills_rec2.ACC_PTYPE
768 ,prd_drills_rec2.S_DATE
769 ,prd_drills_rec2.E_DATE
770 ,TO_CHAR(NULL)
771 ,x_org_id
772 ,prd_drills_rec2.ACT_REVENUE
773 ,prd_drills_rec2.BUD_REVENUE);
774
775 COMMIT;
776
777 END LOOP;
778 COMMIT;
779
780
781 FOR prd_drills_rec3 IN c_prd_drills3 LOOP
782 INSERT INTO PA_BIS_PRJ_BY_PRD_DRILLS
783 (
784 drilldown_type
785 , amount_type_code
786 , drilldown_key1
787 , drilldown_key2
788 , project_id
789 , period_name
790 , accumulation_period_type
791 , start_date
792 , end_date
793 , sort_order
794 , org_id
795 , actual
796 , budget)
797 VALUES
798 ( 'CLASS_CATEGORY'
799 ,'COST'
800 ,prd_drills_rec3.CCATEGORY
801 ,prd_drills_rec3.CCODE
802 ,prd_drills_rec3.PROJECT_ID
803 ,prd_drills_rec3.PERIOD
804 ,prd_drills_rec3.ACC_PTYPE
805 ,prd_drills_rec3.S_DATE
806 ,prd_drills_rec3.E_DATE
807 ,TO_CHAR(NULL)
808 ,x_org_id
809 ,prd_drills_rec3.ACT_COST
810 ,prd_drills_rec3.BUD_COST);
811
812 INSERT INTO PA_BIS_PRJ_BY_PRD_DRILLS
813 (
814 drilldown_type
815 , amount_type_code
816 , drilldown_key1
817 , drilldown_key2
818 , project_id
819 , period_name
820 , accumulation_period_type
821 , start_date
822 , end_date
823 , sort_order
824 , org_id
825 , actual
826 , budget)
827 VALUES
828 ( 'CLASS_CATEGORY'
829 ,'REVENUE'
830 ,prd_drills_rec3.CCATEGORY
831 ,prd_drills_rec3.CCODE
832 ,prd_drills_rec3.PROJECT_ID
833 ,prd_drills_rec3.PERIOD
834 ,prd_drills_rec3.ACC_PTYPE
835 ,prd_drills_rec3.S_DATE
836 ,prd_drills_rec3.E_DATE
837 ,TO_CHAR(NULL)
838 ,x_org_id
839 ,prd_drills_rec3.ACT_REVENUE
840 ,prd_drills_rec3.BUD_REVENUE);
841
842
843 COMMIT;
844
845 END LOOP;
846
847
848 INSERT INTO PA_BIS_PRJ_TO_DATE_DRILLS
849 (
850 drilldown_type
851 , amount_type_code
852 , drilldown_key1
853 , drilldown_key2
854 , project_id
855 , project_number
856 , project_name
857 , sort_order
858 , org_id
859 , actual_ptd
860 , budget_ptd
861 , actual_ytd
862 , budget_ytd
863 )
864 SELECT
865 'CLASS_CATEGORY'
866 , 'REVENUE'
867 , PC.CLASS_CATEGORY
868 , PC.CLASS_CODE
869 , P.PROJECT_ID
870 , P.SEGMENT1
871 , P.NAME
872 , P.NAME ||'01'
873 , P.org_id
874 , SUM(NVL(PAA.REVENUE_PTD, 0))
875 , SUM(NVL(PAB.BASE_REVENUE_PTD, 0))
876 , SUM(NVL(PAA.REVENUE_YTD, 0))
877 , SUM(NVL(PAB.BASE_REVENUE_YTD, 0))
878 FROM pa_projects p
879 , pa_project_types pt
880 , pa_project_accum_headers pah
881 , pa_project_accum_actuals paa
882 , pa_project_accum_budgets pab
883 , pa_class_categories cc
884 , pa_project_classes pc
885 WHERE p.project_id = pah.project_id
886 AND p.project_type = pt.project_type
887 AND pt.project_type_class_code = 'CONTRACT'
888 AND pah.task_id = 0
889 AND pah.resource_list_member_id = 0
890 AND pah.project_accum_id = paa.project_accum_id(+)
891 AND pah.project_accum_id = pab.project_accum_id(+)
892 AND pab.budget_type_code(+) = 'AR'
893 AND p.project_id = pc.project_id
894 AND pc.class_category = cc.class_category
895 AND cc.pick_one_code_only_flag = 'Y'
899 'CLASS_CATEGORY'
896 GROUP BY PC.CLASS_CATEGORY, PC.CLASS_CODE, P.PROJECT_ID, P.SEGMENT1, P.NAME, P.org_id
897 UNION
898 SELECT
900 , 'COST'
901 , PC.CLASS_CATEGORY
902 , PC.CLASS_CODE
903 , P.PROJECT_ID
904 , P.SEGMENT1
905 , P.NAME
906 , P.NAME ||'02'
907 , P.org_id
908 , SUM(NVL(PAA.BURDENED_COST_PTD, 0))
909 , SUM(NVL(PAB.BASE_BURDENED_COST_PTD, 0))
910 , SUM(NVL(PAA.BURDENED_COST_YTD, 0))
911 , SUM(NVL(PAB.BASE_BURDENED_COST_YTD, 0))
912 FROM pa_projects p
913 , pa_project_types pt
914 , pa_project_accum_headers pah
915 , pa_project_accum_actuals paa
916 , pa_project_accum_budgets pab
917 , pa_class_categories cc
918 , pa_project_classes pc
919 WHERE p.project_id = pah.project_id
920 AND p.project_type = pt.project_type
921 AND pt.project_type_class_code = 'CONTRACT'
922 AND pah.task_id = 0
923 AND pah.resource_list_member_id = 0
924 AND pah.project_accum_id = paa.project_accum_id(+)
925 AND pah.project_accum_id = pab.project_accum_id(+)
926 AND pab.budget_type_code(+) = 'AC'
927 AND p.project_id = pc.project_id
928 AND pc.class_category = cc.class_category
929 AND cc.pick_one_code_only_flag = 'Y'
930 GROUP BY PC.CLASS_CATEGORY, PC.CLASS_CODE, P.PROJECT_ID, P.SEGMENT1, P.NAME, P.org_id
931 UNION
932 SELECT
933 'CLASS_CATEGORY'
934 , 'REVENUE'
935 , CC.CLASS_CATEGORY
936 , SUBSTRB(pa_bis_messages.get_message('PA','PA_BIS_PAPFPJCL_UNASSIGNED'),1,30)
937 , P.PROJECT_ID
938 , P.SEGMENT1
939 , P.NAME
940 , P.NAME ||'01'
941 , P.org_id
942 , SUM(NVL(PAA.REVENUE_PTD, 0))
943 , SUM(NVL(PAB.BASE_REVENUE_PTD, 0))
944 , SUM(NVL(PAA.REVENUE_YTD, 0))
945 , SUM(NVL(PAB.BASE_REVENUE_YTD, 0))
946 FROM pa_projects p
947 , pa_project_types pt
948 , pa_project_accum_headers pah
949 , pa_project_accum_actuals paa
950 , pa_project_accum_budgets pab
951 , pa_class_categories cc
952 WHERE p.project_id = pah.project_id
953 AND p.project_type = pt.project_type
954 AND pt.project_type_class_code = 'CONTRACT'
955 AND pah.task_id = 0
956 AND pah.resource_list_member_id = 0
957 AND pah.project_accum_id = paa.project_accum_id(+)
958 AND pah.project_accum_id = pab.project_accum_id(+)
959 AND pab.budget_type_code(+) = 'AR'
960 AND cc.pick_one_code_only_flag = 'Y'
961 AND NOT EXISTS (select 'x'
962 from pa_project_classes pc2
963 where pc2.project_id = p.project_id
964 and pc2.class_category = cc.class_category)
965 GROUP BY CC.CLASS_CATEGORY, P.PROJECT_ID, P.SEGMENT1, P.NAME, P.org_id
966 UNION
967 SELECT
968 'CLASS_CATEGORY'
969 , 'COST'
970 , CC.CLASS_CATEGORY
971 , SUBSTRB(pa_bis_messages.get_message('PA','PA_BIS_PAPFPJCL_UNASSIGNED'),1,30)
972 , P.PROJECT_ID
973 , P.SEGMENT1
974 , P.NAME
975 , P.NAME ||'02'
976 , P.org_id
977 , SUM(NVL(PAA.BURDENED_COST_PTD, 0))
978 , SUM(NVL(PAB.BASE_BURDENED_COST_PTD, 0))
979 , SUM(NVL(PAA.BURDENED_COST_YTD, 0))
980 , SUM(NVL(PAB.BASE_BURDENED_COST_YTD, 0))
981 FROM pa_projects p
982 , pa_project_types pt
983 , pa_project_accum_headers pah
984 , pa_project_accum_actuals paa
985 , pa_project_accum_budgets pab
986 , pa_class_categories cc
987 WHERE p.project_id = pah.project_id
988 AND p.project_type = pt.project_type
989 AND pt.project_type_class_code = 'CONTRACT'
990 AND pah.task_id = 0
991 AND pah.resource_list_member_id = 0
992 AND pah.project_accum_id = paa.project_accum_id(+)
993 AND pah.project_accum_id = pab.project_accum_id(+)
994 AND pab.budget_type_code(+) = 'AC'
995 AND cc.pick_one_code_only_flag = 'Y'
996 AND NOT EXISTS (select 'x'
997 from pa_project_classes pc2
998 where pc2.project_id = p.project_id
999 and pc2.class_category = cc.class_category)
1000 GROUP BY CC.CLASS_CATEGORY, P.PROJECT_ID, P.SEGMENT1, P.NAME, P.org_id
1001 ;
1002
1003 COMMIT;
1004
1005
1006
1007 INSERT INTO PA_BIS_PRJ_TO_DATE_DRILLS
1008 (
1009 drilldown_type
1010 , amount_type_code
1011 , drilldown_key1
1012 , project_id
1013 , project_number
1014 , project_name
1015 , sort_order
1016 , org_id
1017 , actual_ptd
1018 , budget_ptd
1019 , actual_ytd
1020 , budget_ytd )
1021 SELECT
1022 'ORGANIZATION'
1023 , 'REVENUE'
1024 , P.CARRYING_OUT_ORGANIZATION_ID
1025 , P.PROJECT_ID
1026 , P.SEGMENT1
1027 , P.NAME
1028 , P.NAME ||'01'
1029 , P.org_id
1030 , SUM(NVL(PAA.REVENUE_PTD, 0))
1031 , SUM(NVL(PAB.BASE_REVENUE_PTD, 0))
1032 , SUM(NVL(PAA.REVENUE_YTD, 0))
1033 , SUM(NVL(PAB.BASE_REVENUE_YTD, 0))
1034 FROM pa_projects p
1035 , pa_project_types pt
1036 , pa_project_accum_headers pah
1037 , pa_project_accum_actuals paa
1038 , pa_project_accum_budgets pab
1039 WHERE p.project_id = pah.project_id
1040 AND p.project_type = pt.project_type
1041 AND pt.project_type_class_code = 'CONTRACT'
1042 AND pah.task_id = 0
1043 AND pah.resource_list_member_id = 0
1044 AND pah.project_accum_id = paa.project_accum_id(+)
1045 AND pah.project_accum_id = pab.project_accum_id(+)
1046 AND pab.budget_type_code(+) = 'AR'
1047 GROUP BY P.CARRYING_OUT_ORGANIZATION_ID,P.PROJECT_ID, P.SEGMENT1, P.NAME, P.org_id
1051 , 'COST'
1048 UNION
1049 SELECT
1050 'ORGANIZATION'
1052 , P.CARRYING_OUT_ORGANIZATION_ID
1053 , P.PROJECT_ID
1054 , P.SEGMENT1
1055 , P.NAME
1056 , P.NAME ||'02'
1057 , P.org_id
1058 , SUM(NVL(PAA.BURDENED_COST_PTD, 0))
1059 , SUM(NVL(PAB.BASE_BURDENED_COST_PTD, 0))
1060 , SUM(NVL(PAA.BURDENED_COST_YTD, 0))
1061 , SUM(NVL(PAB.BASE_BURDENED_COST_YTD, 0))
1062 FROM pa_projects p
1063 , pa_project_types pt
1064 , pa_project_accum_headers pah
1065 , pa_project_accum_actuals paa
1066 , pa_project_accum_budgets pab
1067 WHERE p.project_id = pah.project_id
1068 AND p.project_type = pt.project_type
1069 AND pt.project_type_class_code = 'CONTRACT'
1070 AND pah.task_id = 0
1071 AND pah.resource_list_member_id = 0
1072 AND pah.project_accum_id = paa.project_accum_id(+)
1073 AND pah.project_accum_id = pab.project_accum_id(+)
1074 AND pab.budget_type_code(+) = 'AC'
1075 GROUP BY P.CARRYING_OUT_ORGANIZATION_ID, P.PROJECT_ID, P.SEGMENT1, P.NAME, P.org_id
1076 ;
1077
1078 COMMIT;
1079
1080
1081
1082 INSERT INTO PA_BIS_TO_DATE_DRILLS
1083 (
1084 drilldown_type
1085 , amount_type_code
1086 , drilldown_key1
1087 , sort_order
1088 , org_id
1089 , actual_ptd
1090 , budget_ptd
1091 , actual_ytd
1092 , budget_ytd
1093 )
1094 SELECT
1095 'ORGANIZATION'
1096 , 'REVENUE'
1097 , P.CARRYING_OUT_ORGANIZATION_ID
1098 , OP.NAME
1099 , P.org_id
1100 , SUM(NVL(PAA.REVENUE_PTD, 0))
1101 , SUM(NVL(PAB.BASE_REVENUE_PTD, 0))
1102 , SUM(NVL(PAA.REVENUE_YTD, 0))
1103 , SUM(NVL(PAB.BASE_REVENUE_YTD, 0))
1104 FROM pa_projects p
1105 , pa_project_types pt
1106 , pa_project_accum_headers pah
1107 , pa_project_accum_actuals paa
1108 , pa_project_accum_budgets pab
1109 , pa_organizations_proj_all_bg_v op
1110 WHERE p.project_id = pah.project_id
1111 AND p.project_type = pt.project_type
1112 AND pt.project_type_class_code = 'CONTRACT'
1113 AND pah.task_id = 0
1114 AND pah.resource_list_member_id = 0
1115 AND pah.project_accum_id = paa.project_accum_id(+)
1116 AND pah.project_accum_id = pab.project_accum_id(+)
1117 AND pab.budget_type_code(+) = 'AR'
1118 AND p.carrying_out_organization_id = op.organization_id
1119 GROUP BY P.CARRYING_OUT_ORGANIZATION_ID, OP.NAME, P.org_id
1120 UNION
1121 SELECT
1122 'ORGANIZATION'
1123 , 'COST'
1124 , P.CARRYING_OUT_ORGANIZATION_ID
1125 , OP.NAME
1126 , P.org_id
1127 , SUM(NVL(PAA.BURDENED_COST_PTD, 0))
1128 , SUM(NVL(PAB.BASE_BURDENED_COST_PTD, 0))
1129 , SUM(NVL(PAA.BURDENED_COST_YTD, 0))
1130 , SUM(NVL(PAB.BASE_BURDENED_COST_YTD, 0))
1131 FROM pa_projects p
1132 , pa_project_types pt
1133 , pa_project_accum_headers pah
1134 , pa_project_accum_actuals paa
1135 , pa_project_accum_budgets pab
1136 , pa_organizations_proj_all_bg_v op
1137 WHERE p.project_id = pah.project_id
1138 AND p.project_type = pt.project_type
1139 AND pt.project_type_class_code = 'CONTRACT'
1140 AND pah.task_id = 0
1141 AND pah.resource_list_member_id = 0
1142 AND pah.project_accum_id = paa.project_accum_id(+)
1143 AND pah.project_accum_id = pab.project_accum_id(+)
1144 AND pab.budget_type_code(+) = 'AC'
1145 AND p.carrying_out_organization_id = op.organization_id
1146 GROUP BY P.CARRYING_OUT_ORGANIZATION_ID, OP.NAME, P.org_id
1147 ;
1148
1149 COMMIT;
1150
1151
1152
1153 INSERT INTO PA_BIS_TO_DATE_DRILLS
1154 (
1155 drilldown_type
1156 , amount_type_code
1157 , drilldown_key1
1158 , drilldown_key2
1159 , sort_order
1160 , org_id
1161 , actual_ptd
1162 , budget_ptd
1163 , actual_ytd
1164 , budget_ytd
1165 )
1166 SELECT
1167 'CLASS_CATEGORY'
1168 , 'REVENUE'
1169 , PC.CLASS_CATEGORY
1170 , PC.CLASS_CODE
1171 , PC.CLASS_CODE
1172 , P.org_id
1173 , SUM(NVL(PAA.REVENUE_PTD, 0))
1174 , SUM(NVL(PAB.BASE_REVENUE_PTD, 0))
1175 , SUM(NVL(PAA.REVENUE_YTD, 0))
1176 , SUM(NVL(PAB.BASE_REVENUE_YTD, 0))
1177 FROM pa_projects p
1178 , pa_project_types pt
1179 , pa_project_accum_headers pah
1180 , pa_project_accum_actuals paa
1181 , pa_project_accum_budgets pab
1182 , pa_class_categories cc
1183 , pa_project_classes pc
1184 WHERE p.project_id = pah.project_id
1185 AND p.project_type = pt.project_type
1186 AND pt.project_type_class_code = 'CONTRACT'
1187 AND pah.task_id = 0
1188 AND pah.resource_list_member_id = 0
1189 AND pah.project_accum_id = paa.project_accum_id(+)
1190 AND pah.project_accum_id = pab.project_accum_id(+)
1191 AND pab.budget_type_code(+) = 'AR'
1192 AND p.project_id = pc.project_id
1193 AND pc.class_category = cc.class_category
1194 AND cc.pick_one_code_only_flag = 'Y'
1195 GROUP BY PC.CLASS_CATEGORY, PC.CLASS_CODE, P.org_id
1196 UNION
1197 SELECT
1198 'CLASS_CATEGORY'
1199 , 'COST'
1200 , PC.CLASS_CATEGORY
1201 , PC.CLASS_CODE
1202 , PC.CLASS_CODE
1203 , P.org_id
1204 , SUM(NVL(PAA.BURDENED_COST_PTD, 0))
1205 , SUM(NVL(PAB.BASE_BURDENED_COST_PTD, 0))
1206 , SUM(NVL(PAA.BURDENED_COST_YTD, 0))
1207 , SUM(NVL(PAB.BASE_BURDENED_COST_YTD, 0))
1208 FROM pa_projects p
1209 , pa_project_types pt
1213 , pa_class_categories cc
1210 , pa_project_accum_headers pah
1211 , pa_project_accum_actuals paa
1212 , pa_project_accum_budgets pab
1214 , pa_project_classes pc
1215 WHERE p.project_id = pah.project_id
1216 AND p.project_type = pt.project_type
1217 AND pt.project_type_class_code = 'CONTRACT'
1218 AND pah.task_id = 0
1219 AND pah.resource_list_member_id = 0
1220 AND pah.project_accum_id = paa.project_accum_id(+)
1221 AND pah.project_accum_id = pab.project_accum_id(+)
1222 AND pab.budget_type_code(+) = 'AC'
1223 AND p.project_id = pc.project_id
1224 AND pc.class_category = cc.class_category
1225 AND cc.pick_one_code_only_flag = 'Y'
1226 GROUP BY PC.CLASS_CATEGORY, PC.CLASS_CODE, P.org_id
1227 UNION
1228 SELECT
1229 'CLASS_CATEGORY'
1230 , 'REVENUE'
1231 , CC.CLASS_CATEGORY
1232 , SUBSTRB(pa_bis_messages.get_message('PA','PA_BIS_PAPFPJCL_UNASSIGNED'),1,30)
1233 , 'ZZZZZZZZZZ'
1234 , P.org_id
1235 , SUM(NVL(PAA.REVENUE_PTD, 0))
1236 , SUM(NVL(PAB.BASE_REVENUE_PTD, 0))
1237 , SUM(NVL(PAA.REVENUE_YTD, 0))
1238 , SUM(NVL(PAB.BASE_REVENUE_YTD, 0))
1239 FROM pa_projects p
1240 , pa_project_types pt
1241 , pa_project_accum_headers pah
1242 , pa_project_accum_actuals paa
1243 , pa_project_accum_budgets pab
1244 , pa_class_categories cc
1245 WHERE p.project_id = pah.project_id
1246 AND p.project_type = pt.project_type
1247 AND pt.project_type_class_code = 'CONTRACT'
1248 AND pah.task_id = 0
1249 AND pah.resource_list_member_id = 0
1250 AND pah.project_accum_id = paa.project_accum_id(+)
1251 AND pah.project_accum_id = pab.project_accum_id(+)
1252 AND pab.budget_type_code(+) = 'AR'
1253 AND cc.pick_one_code_only_flag = 'Y'
1254 AND NOT EXISTS (select 'x'
1255 from pa_project_classes pc2
1256 where pc2.project_id = p.project_id
1257 and pc2.class_category = cc.class_category)
1258 GROUP BY CC.CLASS_CATEGORY, P.org_id
1259 UNION
1260 SELECT
1261 'CLASS_CATEGORY'
1262 , 'COST'
1263 , CC.CLASS_CATEGORY
1264 , SUBSTRB(pa_bis_messages.get_message('PA','PA_BIS_PAPFPJCL_UNASSIGNED'),1,30)
1265 , 'ZZZZZZZZZZ'
1266 , P.org_id
1267 , SUM(NVL(PAA.BURDENED_COST_PTD, 0))
1268 , SUM(NVL(PAB.BASE_BURDENED_COST_PTD, 0))
1269 , SUM(NVL(PAA.BURDENED_COST_YTD, 0))
1270 , SUM(NVL(PAB.BASE_BURDENED_COST_YTD, 0))
1271 FROM pa_projects p
1272 , pa_project_types pt
1273 , pa_project_accum_headers pah
1274 , pa_project_accum_actuals paa
1275 , pa_project_accum_budgets pab
1276 , pa_class_categories cc
1277 WHERE p.project_id = pah.project_id
1278 AND p.project_type = pt.project_type
1279 AND pt.project_type_class_code = 'CONTRACT'
1280 AND pah.task_id = 0
1281 AND pah.resource_list_member_id = 0
1282 AND pah.project_accum_id = paa.project_accum_id(+)
1283 AND pah.project_accum_id = pab.project_accum_id(+)
1284 AND pab.budget_type_code(+) = 'AC'
1285 AND cc.pick_one_code_only_flag = 'Y'
1286 AND NOT EXISTS (select 'x'
1287 from pa_project_classes pc2
1288 where pc2.project_id = p.project_id
1289 and pc2.class_category = cc.class_category)
1290 GROUP BY CC.CLASS_CATEGORY, P.org_id
1291 ;
1292
1293 COMMIT;
1294
1295 FOR prd_drills_rec4 IN c_prd_drills4 LOOP
1296
1297 INSERT INTO PA_BIS_TOTALS_BY_PRD
1298 (
1299 amount_type_code
1300 , period_name
1301 , accumulation_period_type
1302 , start_date
1303 , end_date
1304 , sort_order
1305 , org_id
1306 , actual
1307 , budget
1308 )
1309 VALUES
1310 ('REVENUE'
1311 , prd_drills_rec4.pname
1312 , prd_drills_rec4.acc_ptype
1313 , prd_drills_rec4.s_date
1314 , prd_drills_rec4.e_date
1315 , TO_CHAR(NULL)
1316 , x_org_id
1317 ,prd_drills_rec4.ACT_REVENUE
1318 ,prd_drills_rec4.BUD_REVENUE);
1319
1320 INSERT INTO PA_BIS_TOTALS_BY_PRD
1321 (
1322 amount_type_code
1323 , period_name
1324 , accumulation_period_type
1325 , start_date
1326 , end_date
1327 , sort_order
1328 , org_id
1329 , actual
1330 , budget
1331 )
1332 VALUES
1333 ('COST'
1334 , prd_drills_rec4.pname
1335 , prd_drills_rec4.acc_ptype
1336 , prd_drills_rec4.s_date
1337 , prd_drills_rec4.e_date
1338 , TO_CHAR(NULL)
1339 , x_org_id
1340 ,prd_drills_rec4.ACT_COST
1341 ,prd_drills_rec4.BUD_COST);
1342
1343
1344 COMMIT;
1345
1346 END LOOP;
1347
1348 INSERT INTO PA_BIS_TOTALS_TO_DATE
1349 (
1350 amount_type_code
1351 , sort_order
1352 , drilldown_indicator
1353 , org_id
1354 , actual_ptd
1355 , budget_ptd
1356 , actual_ytd
1357 , budget_ytd
1358 )
1359 SELECT
1360 'REVENUE'
1361 , '01'
1362 , 'Y'
1363 , P.org_id
1364 , SUM(NVL(PAA.REVENUE_PTD, 0))
1365 , SUM(NVL(PAB.BASE_REVENUE_PTD, 0))
1366 , SUM(NVL(PAA.REVENUE_YTD, 0))
1367 , SUM(NVL(PAB.BASE_REVENUE_YTD, 0))
1368 FROM pa_projects p
1369 , pa_project_types pt
1370 , pa_project_accum_headers pah
1371 , pa_project_accum_actuals paa
1372 , pa_project_accum_budgets pab
1373 WHERE p.project_id = pah.project_id
1374 AND p.project_type = pt.project_type
1375 AND pt.project_type_class_code = 'CONTRACT'
1376 AND pah.task_id = 0
1377 AND pah.resource_list_member_id = 0
1378 AND pah.project_accum_id = paa.project_accum_id(+)
1379 AND pah.project_accum_id = pab.project_accum_id(+)
1380 AND pab.budget_type_code(+) = 'AR'
1381 group by P.org_id
1382 UNION
1383 SELECT
1384 'COST'
1385 , '02'
1386 , 'Y'
1387 , P.org_id
1388 , SUM(NVL(PAA.BURDENED_COST_PTD, 0))
1389 , SUM(NVL(PAB.BASE_BURDENED_COST_PTD, 0))
1390 , SUM(NVL(PAA.BURDENED_COST_YTD, 0))
1391 , SUM(NVL(PAB.BASE_BURDENED_COST_YTD, 0))
1392 FROM pa_projects p
1393 , pa_project_types pt
1394 , pa_project_accum_headers pah
1395 , pa_project_accum_actuals paa
1396 , pa_project_accum_budgets pab
1397 WHERE p.project_id = pah.project_id
1398 AND p.project_type = pt.project_type
1399 AND pt.project_type_class_code = 'CONTRACT'
1400 AND pah.task_id = 0
1401 AND pah.resource_list_member_id = 0
1402 AND pah.project_accum_id = paa.project_accum_id(+)
1403 AND pah.project_accum_id = pab.project_accum_id(+)
1404 AND pab.budget_type_code(+) = 'AC'
1405 group by P.org_id;
1406
1407 commit;
1408
1409
1410 exception
1411 when others then
1412 ret_code := SQLCODE;
1413 errbuf := SQLERRM;
1414 END SUMMARIZE_BIS;
1415
1416 END PA_BIS_SUMMARY;