[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_SUM_ROLLUP_FIN
Source
1 package body PJI_FM_SUM_ROLLUP_FIN as
2 /* $Header: PJISF04B.pls 120.5 2006/04/18 20:07:45 appldev noship $ */
3
4 -- -----------------------------------------------------
5 -- procedure FIN_ROWID_TABLE
6 -- -----------------------------------------------------
7 procedure FIN_ROWID_TABLE (p_worker_id in number) is
8
9 l_process varchar2(30);
10 l_schema varchar2(30);
11
12 begin
13
14 l_process := PJI_RM_SUM_MAIN.g_process || p_worker_id;
15
16 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.FIN_ROWID_TABLE(p_worker_id);')) then
17 return;
18 end if;
19
20 insert /*+ append parallel(fin_i) */ into PJI_PJI_RMAP_FIN fin_i
21 (
22 WORKER_ID,
23 STG_ROWID
24 )
25 select
26 p_worker_id WORKER_ID,
27 fin9.ROWID STG_ROWID
28 from
29 PJI_PJI_PROJ_BATCH_MAP map,
30 PJI_FM_AGGR_FIN9 fin9
31 where
32 map.WORKER_ID = p_worker_id and
33 fin9.PROJECT_ID = map.PROJECT_ID;
34
35 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.FIN_ROWID_TABLE(p_worker_id);');
36
37 commit;
38
39 end FIN_ROWID_TABLE;
40
41
42 -- -----------------------------------------------------
43 -- procedure AGGREGATE_FIN_ET_WT_SLICES
44 -- -----------------------------------------------------
45 procedure AGGREGATE_FIN_ET_WT_SLICES (p_worker_id in number) is
46
47 l_process varchar2(30);
48 l_extraction_type varchar2(30);
49
50 l_txn_currency_flag varchar2(1);
51 l_g2_currency_flag varchar2(1);
52
53 l_g1_currency_code varchar2(30);
54 l_g2_currency_code varchar2(30);
55
56 begin
57
58 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
59
60 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.AGGREGATE_FIN_ET_WT_SLICES(p_worker_id);')) then
61 return;
62 end if;
63
64 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
65 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
66
67 select
68 TXN_CURR_FLAG,
69 GLOBAL_CURR2_FLAG
70 into
71 l_txn_currency_flag,
72 l_g2_currency_flag
73 from
74 PJI_SYSTEM_SETTINGS;
75
76 l_g1_currency_code := PJI_UTILS.GET_GLOBAL_PRIMARY_CURRENCY;
77 l_g2_currency_code := PJI_UTILS.GET_GLOBAL_SECONDARY_CURRENCY;
78
79 insert /*+ append parallel(fin4_i) */ into PJI_FM_AGGR_FIN4 fin4_i
80 (
81 WORKER_ID,
82 PROJECT_ID,
83 PROJECT_ORGANIZATION_ID,
84 PROJECT_ORG_ID,
85 PROJECT_TYPE_CLASS,
86 WORK_TYPE_ID,
87 EXP_EVT_TYPE_ID,
88 TIME_ID,
89 CALENDAR_TYPE,
90 GL_CALENDAR_ID,
91 PA_CALENDAR_ID,
92 CURR_RECORD_TYPE_ID,
93 CURRENCY_CODE,
94 PERIOD_TYPE_ID,
95 REVENUE,
96 LABOR_REVENUE,
97 RAW_COST,
98 BURDENED_COST,
99 BILL_RAW_COST,
100 BILL_BURDENED_COST,
101 LABOR_RAW_COST,
102 LABOR_BURDENED_COST,
103 BILL_LABOR_RAW_COST,
104 BILL_LABOR_BURDENED_COST,
105 REVENUE_WRITEOFF,
106 LABOR_HRS,
107 BILL_LABOR_HRS,
108 QUANTITY,
109 BILL_QUANTITY
110 )
111 select
112 src3.WORKER_ID,
113 src3.PROJECT_ID,
114 src3.PROJECT_ORGANIZATION_ID,
115 src3.PROJECT_ORG_ID,
116 src3.PROJECT_TYPE_CLASS,
117 src3.WORK_TYPE_ID,
118 src3.EXP_EVT_TYPE_ID,
119 src3.TIME_ID,
120 src3.CALENDAR_TYPE,
121 src3.GL_CALENDAR_ID,
122 src3.PA_CALENDAR_ID,
123 sum(src3.CURR_RECORD_TYPE_ID) CURR_RECORD_TYPE_ID,
124 nvl(src3.CURRENCY_CODE, 'PJI$NULL') CURRENCY_CODE,
125 src3.PERIOD_TYPE_ID,
126 max(src3.REVENUE) REVENUE,
127 max(src3.LABOR_REVENUE) LABOR_REVENUE,
128 max(src3.RAW_COST) RAW_COST,
129 max(src3.BURDENED_COST) BURDENED_COST,
130 max(src3.BILL_RAW_COST) BILL_RAW_COST,
131 max(src3.BILL_BURDENED_COST) BILL_BURDENED_COST,
132 max(src3.LABOR_RAW_COST) LABOR_RAW_COST,
133 max(src3.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
134 max(src3.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
135 max(src3.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
136 max(src3.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
137 max(src3.LABOR_HRS) LABOR_HRS,
138 max(src3.BILL_LABOR_HRS) BILL_LABOR_HRS,
139 max(src3.QUANTITY) QUANTITY,
140 max(src3.BILL_QUANTITY) BILL_QUANTITY
141 from
142 (
143 select /*+ ordered */
144 p_worker_id WORKER_ID,
145 src2.PROJECT_ID,
146 src2.PROJECT_ORGANIZATION_ID,
147 src2.PROJECT_ORG_ID,
148 src2.PROJECT_TYPE_CLASS,
149 src2.WORK_TYPE_ID,
150 src2.EXP_EVT_TYPE_ID,
151 src2.TIME_ID,
152 src2.CALENDAR_TYPE,
153 src2.GL_CALENDAR_ID,
154 src2.PA_CALENDAR_ID,
155 invert.INVERT_ID CURR_RECORD_TYPE_ID,
156 decode(invert.INVERT_ID,
157 1, l_g1_currency_code,
158 2, l_g2_currency_code,
159 4, info.PF_CURRENCY_CODE,
160 8, prj.PROJECT_CURRENCY_CODE,
161 16, src2.TXN_CURRENCY_CODE,
162 32, l_g1_currency_code,
163 64, l_g2_currency_code,
164 128, info.PF_CURRENCY_CODE,
165 256, prj.PROJECT_CURRENCY_CODE) DIFF_CURRENCY_CODE,
166 src2.DIFF_ROWNUM DIFF_ROWNUM,
167 decode(invert.INVERT_ID,
168 1, l_g1_currency_code,
169 2, l_g2_currency_code,
170 4, info.PF_CURRENCY_CODE,
171 8, prj.PROJECT_CURRENCY_CODE,
172 16, src2.TXN_CURRENCY_CODE,
173 32, src2.TXN_CURRENCY_CODE,
174 64, src2.TXN_CURRENCY_CODE,
175 128, src2.TXN_CURRENCY_CODE,
176 256, src2.TXN_CURRENCY_CODE) CURRENCY_CODE,
177 1 PERIOD_TYPE_ID,
178 decode(invert.INVERT_ID,
179 1, src2.G1_REVENUE,
180 2, src2.G2_REVENUE,
181 4, src2.POU_REVENUE,
182 8, src2.PRJ_REVENUE,
183 16, src2.TXN_REVENUE,
184 32, src2.G1_REVENUE,
185 64, src2.G2_REVENUE,
186 128, src2.POU_REVENUE,
187 256, src2.PRJ_REVENUE) REVENUE,
188 decode(invert.INVERT_ID,
189 1, src2.G1_LABOR_REVENUE,
190 2, src2.G2_LABOR_REVENUE,
191 4, src2.POU_LABOR_REVENUE,
192 8, src2.PRJ_LABOR_REVENUE,
193 16, src2.TXN_LABOR_REVENUE,
194 32, src2.G1_LABOR_REVENUE,
195 64, src2.G2_LABOR_REVENUE,
196 128, src2.POU_LABOR_REVENUE,
197 256, src2.PRJ_LABOR_REVENUE) LABOR_REVENUE,
198 decode(invert.INVERT_ID,
199 1, src2.G1_RAW_COST,
200 2, src2.G2_RAW_COST,
201 4, src2.POU_RAW_COST,
202 8, src2.PRJ_RAW_COST,
203 16, src2.TXN_RAW_COST,
204 32, src2.G1_RAW_COST,
205 64, src2.G2_RAW_COST,
206 128, src2.POU_RAW_COST,
207 256, src2.PRJ_RAW_COST) RAW_COST,
208 decode(invert.INVERT_ID,
209 1, src2.G1_BRDN_COST,
210 2, src2.G2_BRDN_COST,
211 4, src2.POU_BRDN_COST,
212 8, src2.PRJ_BRDN_COST,
213 16, src2.TXN_BRDN_COST,
214 32, src2.G1_BRDN_COST,
215 64, src2.G2_BRDN_COST,
216 128, src2.POU_BRDN_COST,
217 256, src2.PRJ_BRDN_COST) BURDENED_COST,
218 decode(invert.INVERT_ID,
219 1, src2.G1_BILL_RAW_COST,
220 2, src2.G2_BILL_RAW_COST,
221 4, src2.POU_BILL_RAW_COST,
222 8, src2.PRJ_BILL_RAW_COST,
223 16, src2.TXN_BILL_RAW_COST,
224 32, src2.G1_BILL_RAW_COST,
225 64, src2.G2_BILL_RAW_COST,
226 128, src2.POU_BILL_RAW_COST,
227 256, src2.PRJ_BILL_RAW_COST) BILL_RAW_COST,
228 decode(invert.INVERT_ID,
229 1, src2.G1_BILL_BRDN_COST,
230 2, src2.G2_BILL_BRDN_COST,
231 4, src2.POU_BILL_BRDN_COST,
232 8, src2.PRJ_BILL_BRDN_COST,
233 16, src2.TXN_BILL_BRDN_COST,
234 32, src2.G1_BILL_BRDN_COST,
235 64, src2.G2_BILL_BRDN_COST,
236 128, src2.POU_BILL_BRDN_COST,
237 256, src2.PRJ_BILL_BRDN_COST) BILL_BURDENED_COST,
238 decode(invert.INVERT_ID,
239 1, src2.G1_LABOR_RAW_COST,
240 2, src2.G2_LABOR_RAW_COST,
241 4, src2.POU_LABOR_RAW_COST,
242 8, src2.PRJ_LABOR_RAW_COST,
243 16, src2.TXN_LABOR_RAW_COST,
244 32, src2.G1_LABOR_RAW_COST,
245 64, src2.G2_LABOR_RAW_COST,
246 128, src2.POU_LABOR_RAW_COST,
247 256, src2.PRJ_LABOR_RAW_COST) LABOR_RAW_COST,
248 decode(invert.INVERT_ID,
249 1, src2.G1_LABOR_BRDN_COST,
250 2, src2.G2_LABOR_BRDN_COST,
251 4, src2.POU_LABOR_BRDN_COST,
252 8, src2.PRJ_LABOR_BRDN_COST,
253 16, src2.TXN_LABOR_BRDN_COST,
254 32, src2.G1_LABOR_BRDN_COST,
255 64, src2.G2_LABOR_BRDN_COST,
256 128, src2.POU_LABOR_BRDN_COST,
257 256, src2.PRJ_LABOR_BRDN_COST) LABOR_BURDENED_COST,
258 decode(invert.INVERT_ID,
259 1, src2.G1_BILL_LABOR_RAW_COST,
260 2, src2.G2_BILL_LABOR_RAW_COST,
261 4, src2.POU_BILL_LABOR_RAW_COST,
262 8, src2.PRJ_BILL_LABOR_RAW_COST,
263 16, src2.TXN_BILL_LABOR_RAW_COST,
264 32, src2.G1_BILL_LABOR_RAW_COST,
265 64, src2.G2_BILL_LABOR_RAW_COST,
266 128, src2.POU_BILL_LABOR_RAW_COST,
267 256, src2.PRJ_BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
268 decode(invert.INVERT_ID,
269 1, src2.G1_BILL_LABOR_BRDN_COST,
270 2, src2.G2_BILL_LABOR_BRDN_COST,
271 4, src2.POU_BILL_LABOR_BRDN_COST,
272 8, src2.PRJ_BILL_LABOR_BRDN_COST,
273 16, src2.TXN_BILL_LABOR_BRDN_COST,
274 32, src2.G1_BILL_LABOR_BRDN_COST,
275 64, src2.G2_BILL_LABOR_BRDN_COST,
276 128, src2.POU_BILL_LABOR_BRDN_COST,
277 256, src2.PRJ_BILL_LABOR_BRDN_COST) BILL_LABOR_BURDENED_COST,
278 decode(invert.INVERT_ID,
279 1, src2.G1_REVENUE_WRITEOFF,
280 2, src2.G2_REVENUE_WRITEOFF,
281 4, src2.POU_REVENUE_WRITEOFF,
282 8, src2.PRJ_REVENUE_WRITEOFF,
283 16, src2.TXN_REVENUE_WRITEOFF,
284 32, src2.G1_REVENUE_WRITEOFF,
285 64, src2.G2_REVENUE_WRITEOFF,
286 128, src2.POU_REVENUE_WRITEOFF,
287 256, src2.PRJ_REVENUE_WRITEOFF) REVENUE_WRITEOFF,
288 src2.LABOR_HRS,
289 src2.BILL_LABOR_HRS,
290 src2.QUANTITY,
291 src2.BILL_QUANTITY
292 from
293 (
294 select
295 ROWNUM DIFF_ROWNUM,
296 src1.PROJECT_ID,
297 src1.PROJECT_ORGANIZATION_ID,
298 src1.PROJECT_ORG_ID,
299 src1.PROJECT_TYPE_CLASS,
300 src1.WORK_TYPE_ID,
301 src1.EXP_EVT_TYPE_ID,
302 src1.TIME_ID,
303 src1.CALENDAR_TYPE,
304 src1.GL_CALENDAR_ID,
305 src1.PA_CALENDAR_ID,
306 src1.TXN_CURRENCY_CODE,
307 src1.TXN_REVENUE,
308 src1.TXN_LABOR_REVENUE,
309 src1.TXN_RAW_COST,
310 src1.TXN_BRDN_COST,
311 src1.TXN_BILL_RAW_COST,
312 src1.TXN_BILL_BRDN_COST,
313 src1.TXN_LABOR_RAW_COST,
314 src1.TXN_LABOR_BRDN_COST,
315 src1.TXN_BILL_LABOR_RAW_COST,
316 src1.TXN_BILL_LABOR_BRDN_COST,
317 src1.TXN_REVENUE_WRITEOFF,
318 src1.PRJ_REVENUE,
319 src1.PRJ_LABOR_REVENUE,
320 src1.PRJ_RAW_COST,
321 src1.PRJ_BRDN_COST,
322 src1.PRJ_BILL_RAW_COST,
323 src1.PRJ_BILL_BRDN_COST,
324 src1.PRJ_LABOR_RAW_COST,
325 src1.PRJ_LABOR_BRDN_COST,
326 src1.PRJ_BILL_LABOR_RAW_COST,
327 src1.PRJ_BILL_LABOR_BRDN_COST,
328 src1.PRJ_REVENUE_WRITEOFF,
329 src1.POU_REVENUE,
330 src1.POU_LABOR_REVENUE,
331 src1.POU_RAW_COST,
332 src1.POU_BRDN_COST,
333 src1.POU_BILL_RAW_COST,
334 src1.POU_BILL_BRDN_COST,
338 src1.POU_BILL_LABOR_BRDN_COST,
335 src1.POU_LABOR_RAW_COST,
336 src1.POU_LABOR_BRDN_COST,
337 src1.POU_BILL_LABOR_RAW_COST,
339 src1.POU_REVENUE_WRITEOFF,
340 src1.EOU_REVENUE,
341 src1.EOU_LABOR_REVENUE,
342 src1.EOU_RAW_COST,
343 src1.EOU_BRDN_COST,
344 src1.EOU_BILL_RAW_COST,
345 src1.EOU_BILL_BRDN_COST,
346 src1.EOU_LABOR_RAW_COST,
347 src1.EOU_LABOR_BRDN_COST,
348 src1.EOU_BILL_LABOR_RAW_COST,
349 src1.EOU_BILL_LABOR_BRDN_COST,
350 src1.EOU_REVENUE_WRITEOFF,
351 src1.G1_REVENUE,
352 src1.G1_LABOR_REVENUE,
353 src1.G1_RAW_COST,
354 src1.G1_BRDN_COST,
355 src1.G1_BILL_RAW_COST,
356 src1.G1_BILL_BRDN_COST,
357 src1.G1_LABOR_RAW_COST,
358 src1.G1_LABOR_BRDN_COST,
359 src1.G1_BILL_LABOR_RAW_COST,
360 src1.G1_BILL_LABOR_BRDN_COST,
361 src1.G1_REVENUE_WRITEOFF,
362 src1.G2_REVENUE,
363 src1.G2_LABOR_REVENUE,
364 src1.G2_RAW_COST,
365 src1.G2_BRDN_COST,
366 src1.G2_BILL_RAW_COST,
367 src1.G2_BILL_BRDN_COST,
368 src1.G2_LABOR_RAW_COST,
369 src1.G2_LABOR_BRDN_COST,
370 src1.G2_BILL_LABOR_RAW_COST,
371 src1.G2_BILL_LABOR_BRDN_COST,
372 src1.G2_REVENUE_WRITEOFF,
373 src1.LABOR_HRS,
374 src1.BILL_LABOR_HRS,
375 src1.QUANTITY,
376 src1.BILL_QUANTITY
377 from
378 (
379 select
380 fin9.PROJECT_ID,
381 nvl(map.NEW_PROJECT_ORGANIZATION_ID,
382 fin9.PROJECT_ORGANIZATION_ID) PROJECT_ORGANIZATION_ID,
383 fin9.PROJECT_ORG_ID,
384 fin9.PROJECT_TYPE_CLASS,
385 fin9.WORK_TYPE_ID,
386 fin9.EXP_EVT_TYPE_ID,
387 fin9.TIME_ID,
388 fin9.CALENDAR_TYPE,
389 fin9.GL_CALENDAR_ID,
390 fin9.PA_CALENDAR_ID,
391 fin9.TXN_CURRENCY_CODE,
392 sum(fin9.TXN_REVENUE) TXN_REVENUE,
393 sum(fin9.TXN_LABOR_REVENUE) TXN_LABOR_REVENUE,
394 sum(fin9.TXN_RAW_COST) TXN_RAW_COST,
395 sum(fin9.TXN_BRDN_COST) TXN_BRDN_COST,
396 sum(fin9.TXN_BILL_RAW_COST) TXN_BILL_RAW_COST,
397 sum(fin9.TXN_BILL_BRDN_COST) TXN_BILL_BRDN_COST,
398 sum(fin9.TXN_LABOR_RAW_COST) TXN_LABOR_RAW_COST,
399 sum(fin9.TXN_LABOR_BRDN_COST) TXN_LABOR_BRDN_COST,
400 sum(fin9.TXN_BILL_LABOR_RAW_COST) TXN_BILL_LABOR_RAW_COST,
401 sum(fin9.TXN_BILL_LABOR_BRDN_COST) TXN_BILL_LABOR_BRDN_COST,
402 sum(fin9.TXN_REVENUE_WRITEOFF) TXN_REVENUE_WRITEOFF,
403 sum(fin9.PRJ_REVENUE) PRJ_REVENUE,
404 sum(fin9.PRJ_LABOR_REVENUE) PRJ_LABOR_REVENUE,
405 sum(fin9.PRJ_RAW_COST) PRJ_RAW_COST,
406 sum(fin9.PRJ_BRDN_COST) PRJ_BRDN_COST,
407 sum(fin9.PRJ_BILL_RAW_COST) PRJ_BILL_RAW_COST,
408 sum(fin9.PRJ_BILL_BRDN_COST) PRJ_BILL_BRDN_COST,
409 sum(fin9.PRJ_LABOR_RAW_COST) PRJ_LABOR_RAW_COST,
410 sum(fin9.PRJ_LABOR_BRDN_COST) PRJ_LABOR_BRDN_COST,
411 sum(fin9.PRJ_BILL_LABOR_RAW_COST) PRJ_BILL_LABOR_RAW_COST,
412 sum(fin9.PRJ_BILL_LABOR_BRDN_COST) PRJ_BILL_LABOR_BRDN_COST,
413 sum(fin9.PRJ_REVENUE_WRITEOFF) PRJ_REVENUE_WRITEOFF,
414 sum(fin9.POU_REVENUE) POU_REVENUE,
415 sum(fin9.POU_LABOR_REVENUE) POU_LABOR_REVENUE,
416 sum(fin9.POU_RAW_COST) POU_RAW_COST,
417 sum(fin9.POU_BRDN_COST) POU_BRDN_COST,
418 sum(fin9.POU_BILL_RAW_COST) POU_BILL_RAW_COST,
419 sum(fin9.POU_BILL_BRDN_COST) POU_BILL_BRDN_COST,
420 sum(fin9.POU_LABOR_RAW_COST) POU_LABOR_RAW_COST,
421 sum(fin9.POU_LABOR_BRDN_COST) POU_LABOR_BRDN_COST,
422 sum(fin9.POU_BILL_LABOR_RAW_COST) POU_BILL_LABOR_RAW_COST,
423 sum(fin9.POU_BILL_LABOR_BRDN_COST) POU_BILL_LABOR_BRDN_COST,
424 sum(fin9.POU_REVENUE_WRITEOFF) POU_REVENUE_WRITEOFF,
425 sum(fin9.EOU_REVENUE) EOU_REVENUE,
426 sum(fin9.EOU_LABOR_REVENUE) EOU_LABOR_REVENUE,
427 sum(fin9.EOU_RAW_COST) EOU_RAW_COST,
428 sum(fin9.EOU_BRDN_COST) EOU_BRDN_COST,
429 sum(fin9.EOU_BILL_RAW_COST) EOU_BILL_RAW_COST,
430 sum(fin9.EOU_BILL_BRDN_COST) EOU_BILL_BRDN_COST,
431 sum(fin9.EOU_LABOR_RAW_COST) EOU_LABOR_RAW_COST,
432 sum(fin9.EOU_LABOR_BRDN_COST) EOU_LABOR_BRDN_COST,
433 sum(fin9.EOU_BILL_LABOR_RAW_COST) EOU_BILL_LABOR_RAW_COST,
434 sum(fin9.EOU_BILL_LABOR_BRDN_COST) EOU_BILL_LABOR_BRDN_COST,
435 sum(fin9.EOU_REVENUE_WRITEOFF) EOU_REVENUE_WRITEOFF,
436 sum(fin9.G1_REVENUE) G1_REVENUE,
440 sum(fin9.G1_BILL_RAW_COST) G1_BILL_RAW_COST,
437 sum(fin9.G1_LABOR_REVENUE) G1_LABOR_REVENUE,
438 sum(fin9.G1_RAW_COST) G1_RAW_COST,
439 sum(fin9.G1_BRDN_COST) G1_BRDN_COST,
441 sum(fin9.G1_BILL_BRDN_COST) G1_BILL_BRDN_COST,
442 sum(fin9.G1_LABOR_RAW_COST) G1_LABOR_RAW_COST,
443 sum(fin9.G1_LABOR_BRDN_COST) G1_LABOR_BRDN_COST,
444 sum(fin9.G1_BILL_LABOR_RAW_COST) G1_BILL_LABOR_RAW_COST,
445 sum(fin9.G1_BILL_LABOR_BRDN_COST) G1_BILL_LABOR_BRDN_COST,
446 sum(fin9.G1_REVENUE_WRITEOFF) G1_REVENUE_WRITEOFF,
447 sum(fin9.G2_REVENUE) G2_REVENUE,
448 sum(fin9.G2_LABOR_REVENUE) G2_LABOR_REVENUE,
449 sum(fin9.G2_RAW_COST) G2_RAW_COST,
450 sum(fin9.G2_BRDN_COST) G2_BRDN_COST,
451 sum(fin9.G2_BILL_RAW_COST) G2_BILL_RAW_COST,
452 sum(fin9.G2_BILL_BRDN_COST) G2_BILL_BRDN_COST,
453 sum(fin9.G2_LABOR_RAW_COST) G2_LABOR_RAW_COST,
454 sum(fin9.G2_LABOR_BRDN_COST) G2_LABOR_BRDN_COST,
455 sum(fin9.G2_BILL_LABOR_RAW_COST) G2_BILL_LABOR_RAW_COST,
456 sum(fin9.G2_BILL_LABOR_BRDN_COST) G2_BILL_LABOR_BRDN_COST,
457 sum(fin9.G2_REVENUE_WRITEOFF) G2_REVENUE_WRITEOFF,
458 sum(fin9.LABOR_HRS) LABOR_HRS,
459 sum(fin9.BILL_LABOR_HRS) BILL_LABOR_HRS,
460 sum(fin9.QUANTITY) QUANTITY,
461 sum(fin9.BILL_QUANTITY) BILL_QUANTITY
462 from
463 PJI_PJI_RMAP_FIN fin9_r,
464 PJI_FM_AGGR_FIN9 fin9,
465 (
466 select
467 map.PROJECT_ID,
468 map.NEW_PROJECT_ORGANIZATION_ID
469 from
470 PJI_PJI_PROJ_BATCH_MAP map
471 where
472 map.NEW_PROJECT_ORGANIZATION_ID <> map.PROJECT_ORGANIZATION_ID
473 ) map
474 where
475 fin9_r.WORKER_ID = p_worker_id and
476 fin9.ROWID = fin9_r. STG_ROWID and
477 fin9.PROJECT_ID = map.PROJECT_Id (+)
478 group by
479 fin9.PROJECT_ID,
480 nvl(map.NEW_PROJECT_ORGANIZATION_ID,
481 fin9.PROJECT_ORGANIZATION_ID),
482 fin9.PROJECT_ORG_ID,
483 fin9.PROJECT_TYPE_CLASS,
484 fin9.WORK_TYPE_ID,
485 fin9.EXP_EVT_TYPE_ID,
486 fin9.TIME_ID,
487 fin9.CALENDAR_TYPE,
488 fin9.GL_CALENDAR_ID,
489 fin9.PA_CALENDAR_ID,
490 fin9.TXN_CURRENCY_CODE
491 ) src1
492 ) src2,
493 PA_PROJECTS_ALL prj,
494 PJI_ORG_EXTR_INFO info,
495 (
496 select 1 INVERT_ID from dual
497 where l_g1_currency_code is not null union all
498 select 2 INVERT_ID from dual
499 where l_g2_currency_flag = 'Y' and
500 l_g2_currency_code is not null union all
501 select 4 INVERT_ID from dual union all
502 select 8 INVERT_ID from dual
503 -- select 16 INVERT_ID from dual OMIT TXN CURRENCY FROM PJI
504 -- where l_txn_currency_flag = 'Y' union all
505 -- select 32 INVERT_ID from dual OMIT DETAIL SLICES FOR NOW
506 -- where l_g1_currency_code is not null union all
507 -- select 64 INVERT_ID from dual
508 -- where l_g2_currency_flag = 'Y' and
509 -- l_g2_currency_code is not null union all
510 -- select 128 INVERT_ID from dual union all
511 -- select 256 INVERT_ID from dual
512 ) invert
513 where
514 src2.PROJECT_ID = prj.PROJECT_ID and
515 nvl(src2.PROJECT_ORG_ID, -1) = nvl(info.ORG_ID, -1)
516 ) src3
517 group by
518 src3.WORKER_ID,
519 src3.PROJECT_ID,
520 src3.PROJECT_ORGANIZATION_ID,
521 src3.PROJECT_ORG_ID,
522 src3.PROJECT_TYPE_CLASS,
523 src3.WORK_TYPE_ID,
524 src3.EXP_EVT_TYPE_ID,
525 src3.TIME_ID,
526 src3.CALENDAR_TYPE,
527 src3.GL_CALENDAR_ID,
528 src3.PA_CALENDAR_ID,
529 src3.DIFF_CURRENCY_CODE,
530 src3.DIFF_ROWNUM,
531 nvl(src3.CURRENCY_CODE, 'PJI$NULL'),
532 src3.PERIOD_TYPE_ID;
533
534 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.AGGREGATE_FIN_ET_WT_SLICES(p_worker_id);');
535
536 commit;
537
541 -- -----------------------------------------------------
538 end AGGREGATE_FIN_ET_WT_SLICES;
539
540
542 -- procedure PURGE_FIN_DATA
543 -- -----------------------------------------------------
544 procedure PURGE_FIN_DATA (p_worker_id in number) is
545
546 l_process varchar2(30);
547 l_schema varchar2(30);
548
549 begin
550
551 l_process := PJI_RM_SUM_MAIN.g_process || p_worker_id;
552
553 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.PURGE_FIN_DATA(p_worker_id);')) then
554 return;
555 end if;
556
557 delete
558 from PJI_FM_AGGR_FIN9
559 where ROWID in (select STG_ROWID
560 from PJI_PJI_RMAP_FIN
561 where WORKER_ID = p_worker_id);
562
563 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.PURGE_FIN_DATA(p_worker_id);');
564
565 commit;
566
567 end PURGE_FIN_DATA;
568
569
570 -- -----------------------------------------------------
571 -- procedure AGGREGATE_FIN_ET_SLICES
572 -- -----------------------------------------------------
573 procedure AGGREGATE_FIN_ET_SLICES (p_worker_id in number) is
574
575 l_process varchar2(30);
576 l_schema varchar2(30);
577
578 begin
579
580 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
581
582 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.AGGREGATE_FIN_ET_SLICES(p_worker_id);')) then
583 return;
584 end if;
585
586 insert /*+ append parallel(fin5_i) */ into PJI_FM_AGGR_FIN5 fin5_i
587 (
588 WORKER_ID,
589 PROJECT_ID,
590 PROJECT_ORG_ID,
591 PROJECT_ORGANIZATION_ID,
592 PROJECT_TYPE_CLASS,
593 EXP_EVT_TYPE_ID,
594 TIME_ID,
595 PERIOD_TYPE_ID,
596 CALENDAR_TYPE,
597 GL_CALENDAR_ID,
598 PA_CALENDAR_ID,
599 CURR_RECORD_TYPE_ID,
600 CURRENCY_CODE,
601 REVENUE,
602 LABOR_REVENUE,
603 RAW_COST,
604 BURDENED_COST,
605 BILL_RAW_COST,
606 BILL_BURDENED_COST,
607 LABOR_RAW_COST,
608 LABOR_BURDENED_COST,
609 BILL_LABOR_RAW_COST,
610 BILL_LABOR_BURDENED_COST,
611 REVENUE_WRITEOFF,
612 LABOR_HRS,
613 BILL_LABOR_HRS,
614 QUANTITY,
615 BILL_QUANTITY
616 )
617 select /*+ parallel(tmp4) */
618 p_worker_id,
619 tmp4.PROJECT_ID,
620 tmp4.PROJECT_ORG_ID,
621 tmp4.PROJECT_ORGANIZATION_ID,
622 tmp4.PROJECT_TYPE_CLASS,
623 tmp4.EXP_EVT_TYPE_ID,
624 tmp4.TIME_ID,
625 tmp4.PERIOD_TYPE_ID,
626 tmp4.CALENDAR_TYPE,
627 tmp4.GL_CALENDAR_ID,
628 tmp4.PA_CALENDAR_ID,
629 tmp4.CURR_RECORD_TYPE_ID,
630 tmp4.CURRENCY_CODE,
631 sum(tmp4.REVENUE),
632 sum(tmp4.LABOR_REVENUE),
633 sum(tmp4.RAW_COST),
634 sum(tmp4.BURDENED_COST),
635 sum(tmp4.BILL_RAW_COST),
636 sum(tmp4.BILL_BURDENED_COST),
637 sum(tmp4.LABOR_RAW_COST),
638 sum(tmp4.LABOR_BURDENED_COST),
639 sum(tmp4.BILL_LABOR_RAW_COST),
640 sum(tmp4.BILL_LABOR_BURDENED_COST),
641 sum(tmp4.REVENUE_WRITEOFF),
642 sum(tmp4.LABOR_HRS),
643 sum(tmp4.BILL_LABOR_HRS),
644 sum(QUANTITY),
645 sum(BILL_QUANTITY)
646 from
647 PJI_FM_AGGR_FIN4 tmp4
648 where
649 tmp4.WORKER_ID = p_worker_id
650 group by
651 tmp4.PROJECT_ID,
652 tmp4.PROJECT_ORG_ID,
653 tmp4.PROJECT_ORGANIZATION_ID,
654 tmp4.PROJECT_TYPE_CLASS,
655 tmp4.EXP_EVT_TYPE_ID,
656 tmp4.TIME_ID,
657 tmp4.PERIOD_TYPE_ID,
658 tmp4.CALENDAR_TYPE,
659 tmp4.GL_CALENDAR_ID,
660 tmp4.PA_CALENDAR_ID,
661 tmp4.CURR_RECORD_TYPE_ID,
662 tmp4.CURRENCY_CODE;
663
664 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.AGGREGATE_FIN_ET_SLICES(p_worker_id);');
665
666 commit;
667
668 end AGGREGATE_FIN_ET_SLICES;
669
670
671 -- -----------------------------------------------------
672 -- procedure AGGREGATE_FIN_SLICES
673 -- -----------------------------------------------------
674 procedure AGGREGATE_FIN_SLICES (p_worker_id in number) is
675
676 l_process varchar2(30);
677 l_schema varchar2(30);
678
679 begin
680
681 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
682
683 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.AGGREGATE_FIN_SLICES(p_worker_id);')) then
684 return;
685 end if;
686
687 insert /*+ append parallel(fin3_i) */ into PJI_FM_AGGR_FIN3 fin3_i
688 (
689 WORKER_ID,
693 PROJECT_TYPE_CLASS,
690 PROJECT_ID,
691 PROJECT_ORG_ID,
692 PROJECT_ORGANIZATION_ID,
694 TIME_ID,
695 PERIOD_TYPE_ID,
696 CALENDAR_TYPE,
697 GL_CALENDAR_ID,
698 PA_CALENDAR_ID,
699 CURR_RECORD_TYPE_ID,
700 CURRENCY_CODE,
701 REVENUE,
702 LABOR_REVENUE,
703 RAW_COST,
704 BURDENED_COST,
705 BILL_RAW_COST,
706 BILL_BURDENED_COST,
707 LABOR_RAW_COST,
708 LABOR_BURDENED_COST,
709 BILL_LABOR_RAW_COST,
710 BILL_LABOR_BURDENED_COST,
711 REVENUE_WRITEOFF,
712 LABOR_HRS,
713 BILL_LABOR_HRS,
714 CURR_BGT_REVENUE,
715 CURR_BGT_RAW_COST,
716 CURR_BGT_BURDENED_COST,
717 CURR_BGT_LABOR_HRS,
718 ORIG_BGT_REVENUE,
719 ORIG_BGT_RAW_COST,
720 ORIG_BGT_BURDENED_COST,
721 ORIG_BGT_LABOR_HRS,
722 FORECAST_REVENUE,
723 FORECAST_RAW_COST,
724 FORECAST_BURDENED_COST,
725 FORECAST_LABOR_HRS
726 )
727 select /*+ parallel(tmp5) */
728 p_worker_id,
729 tmp5.PROJECT_ID,
730 tmp5.PROJECT_ORG_ID,
731 tmp5.PROJECT_ORGANIZATION_ID,
732 tmp5.PROJECT_TYPE_CLASS,
733 tmp5.TIME_ID,
734 tmp5.PERIOD_TYPE_ID,
735 tmp5.CALENDAR_TYPE,
736 tmp5.GL_CALENDAR_ID,
737 tmp5.PA_CALENDAR_ID,
738 tmp5.CURR_RECORD_TYPE_ID,
739 tmp5.CURRENCY_CODE,
740 sum(tmp5.REVENUE) REVENUE,
741 sum(tmp5.LABOR_REVENUE) LABOR_REVENUE,
742 sum(tmp5.RAW_COST) RAW_COST,
743 sum(tmp5.BURDENED_COST) BURDENED_COST,
744 sum(tmp5.BILL_RAW_COST) BILL_RAW_COST,
745 sum(tmp5.BILL_BURDENED_COST) BILL_BURDENED_COST,
746 sum(tmp5.LABOR_RAW_COST) LABOR_RAW_COST,
747 sum(tmp5.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
748 sum(tmp5.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
749 sum(tmp5.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
750 sum(tmp5.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
751 sum(tmp5.LABOR_HRS) LABOR_HRS,
752 sum(tmp5.BILL_LABOR_HRS) BILL_LABOR_HRS,
753 to_number(null) CURR_BGT_REVENUE,
754 to_number(null) CURR_BGT_RAW_COST,
755 to_number(null) CURR_BGT_BURDENED_COST,
756 to_number(null) CURR_BGT_LABOR_HRS,
757 to_number(null) ORIG_BGT_REVENUE,
758 to_number(null) ORIG_BGT_RAW_COST,
759 to_number(null) ORIG_BGT_BURDENED_COST,
760 to_number(null) ORIG_BGT_LABOR_HRS,
761 to_number(null) FORECAST_REVENUE,
762 to_number(null) FORECAST_RAW_COST,
763 to_number(null) FORECAST_BURDENED_COST,
764 to_number(null) FORECAST_LABOR_HRS
765 from
766 PJI_FM_AGGR_FIN5 tmp5
767 where
768 tmp5.WORKER_ID = p_worker_id
769 group by
770 tmp5.PROJECT_ID,
771 tmp5.PROJECT_ORG_ID,
772 tmp5.PROJECT_ORGANIZATION_ID,
773 tmp5.PROJECT_TYPE_CLASS,
774 tmp5.TIME_ID,
775 tmp5.PERIOD_TYPE_ID,
776 tmp5.CALENDAR_TYPE,
777 tmp5.GL_CALENDAR_ID,
778 tmp5.PA_CALENDAR_ID,
779 tmp5.CURR_RECORD_TYPE_ID,
780 tmp5.CURRENCY_CODE;
781
782 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.AGGREGATE_FIN_SLICES(p_worker_id);');
783
784 commit;
785
786 end AGGREGATE_FIN_SLICES;
787
788
789 -- -----------------------------------------------------
790 -- procedure EXPAND_FPW_CAL_EN
791 -- -----------------------------------------------------
792 procedure EXPAND_FPW_CAL_EN (p_worker_id in number) is
793
794 l_process varchar2(30);
795 l_extraction_type varchar2(30);
796
797 begin
798
799 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
800
801 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPW_CAL_EN(p_worker_id);')) then
802 return;
803 end if;
804
805 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
806 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
807
808 insert /*+ append parallel(fin4_i) */ into PJI_FM_AGGR_FIN4 fin4_i -- in EXPAND_FPW_CAL_EN
809 (
810 WORKER_ID,
811 PROJECT_ID,
812 PROJECT_ORG_ID,
813 PROJECT_ORGANIZATION_ID,
814 PROJECT_TYPE_CLASS,
815 EXP_EVT_TYPE_ID,
816 WORK_TYPE_ID,
817 TIME_ID,
818 PERIOD_TYPE_ID,
819 CALENDAR_TYPE,
820 CURR_RECORD_TYPE_ID,
821 CURRENCY_CODE,
822 REVENUE,
823 LABOR_REVENUE,
824 RAW_COST,
825 BURDENED_COST,
826 BILL_RAW_COST,
827 BILL_BURDENED_COST,
828 LABOR_RAW_COST,
829 LABOR_BURDENED_COST,
830 BILL_LABOR_RAW_COST,
831 BILL_LABOR_BURDENED_COST,
832 REVENUE_WRITEOFF,
833 LABOR_HRS,
834 BILL_LABOR_HRS,
835 QUANTITY,
839 full(time) use_hash(time) swap_join_inputs(time)
836 BILL_QUANTITY
837 )
838 select /*+ ordered
840 full(fin) use_hash(fin) parallel(fin) */
841 p_worker_id WORKER_ID,
842 fin.PROJECT_ID,
843 fin.PROJECT_ORG_ID,
844 fin.PROJECT_ORGANIZATION_ID,
845 fin.PROJECT_TYPE_CLASS,
846 fin.EXP_EVT_TYPE_ID,
847 fin.WORK_TYPE_ID,
848 case when grouping(time.ENT_YEAR_ID) = 0 and
849 grouping(time.ENT_QTR_ID) = 0 and
850 grouping(time.ENT_PERIOD_ID) = 0
851 then time.ENT_PERIOD_ID
852 when grouping(time.ENT_YEAR_ID) = 0 and
853 grouping(time.ENT_QTR_ID) = 0 and
854 grouping(time.ENT_PERIOD_ID) = 1
855 then time.ENT_QTR_ID
856 when grouping(time.ENT_YEAR_ID) = 0 and
857 grouping(time.ENT_QTR_ID) = 1 and
858 grouping(time.ENT_PERIOD_ID) = 1
859 then time.ENT_YEAR_ID
860 end TIME_ID,
861 case when grouping(time.ENT_YEAR_ID) = 0 and
862 grouping(time.ENT_QTR_ID) = 0 and
863 grouping(time.ENT_PERIOD_ID) = 0
864 then 32
865 when grouping(time.ENT_YEAR_ID) = 0 and
866 grouping(time.ENT_QTR_ID) = 0 and
867 grouping(time.ENT_PERIOD_ID) = 1
868 then 64
869 when grouping(time.ENT_YEAR_ID) = 0 and
870 grouping(time.ENT_QTR_ID) = 1 and
871 grouping(time.ENT_PERIOD_ID) = 1
872 then 128
873 end PERIOD_TYPE_ID,
874 'E' CALENDAR_TYPE,
875 bitand(fin.CURR_RECORD_TYPE_ID, 247) CURR_RECORD_TYPE_ID,
876 fin.CURRENCY_CODE,
877 sum(fin.REVENUE) REVENUE,
878 sum(fin.LABOR_REVENUE) LABOR_REVENUE,
879 sum(fin.RAW_COST) RAW_COST,
880 sum(fin.BURDENED_COST) BURDENED_COST,
881 sum(fin.BILL_RAW_COST) BILL_RAW_COST,
882 sum(fin.BILL_BURDENED_COST) BILL_BURDENED_COST,
883 sum(fin.LABOR_RAW_COST) LABOR_RAW_COST,
884 sum(fin.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
885 sum(fin.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
886 sum(fin.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
887 sum(fin.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
888 sum(fin.LABOR_HRS) LABOR_HRS,
889 sum(fin.BILL_LABOR_HRS) BILL_LABOR_HRS,
890 sum(fin.QUANTITY) QUANTITY,
891 sum(fin.BILL_QUANTITY) BILL_QUANTITY
892 from
893 FII_TIME_DAY time,
894 PJI_FM_AGGR_FIN4 fin
895 where
896 fin.WORKER_ID = p_worker_id and
897 fin.PERIOD_TYPE_ID = 1 and
898 fin.CALENDAR_TYPE = 'C' and
899 fin.CURR_RECORD_TYPE_ID not in (8, 256) and
900 fin.TIME_ID = time.REPORT_DATE_JULIAN
901 group by
902 fin.PROJECT_ID,
903 fin.PROJECT_ORG_ID,
904 fin.PROJECT_ORGANIZATION_ID,
905 fin.PROJECT_TYPE_CLASS,
906 fin.EXP_EVT_TYPE_ID,
907 fin.WORK_TYPE_ID,
908 rollup (time.ENT_YEAR_ID,
909 time.ENT_QTR_ID,
910 time.ENT_PERIOD_ID),
911 bitand(fin.CURR_RECORD_TYPE_ID, 247),
912 fin.CURRENCY_CODE
913 having
914 not (grouping(time.ENT_YEAR_ID) = 1 and
915 grouping(time.ENT_QTR_ID) = 1 and
916 grouping(time.ENT_PERIOD_ID) = 1);
917
918 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPW_CAL_EN(p_worker_id);');
919
920 commit;
921
922 end EXPAND_FPW_CAL_EN;
923
924
925 -- -----------------------------------------------------
926 -- procedure EXPAND_FPW_CAL_PA
927 -- -----------------------------------------------------
928 procedure EXPAND_FPW_CAL_PA (p_worker_id in number) is
929
930 l_process varchar2(30);
931
932 begin
933
934 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process, 'PA_CALENDAR_FLAG') = 'N') then
935 return;
936 end if;
937
938 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
939
940 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPW_CAL_PA(p_worker_id);')) then
941 return;
942 end if;
943
944 insert /*+ append parallel(fin4_i) */ into PJI_FM_AGGR_FIN4 fin4_i -- in EXPAND_FPW_CAL_PA
948 PROJECT_ORG_ID,
945 (
946 WORKER_ID,
947 PROJECT_ID,
949 PROJECT_ORGANIZATION_ID,
950 PROJECT_TYPE_CLASS,
951 EXP_EVT_TYPE_ID,
952 WORK_TYPE_ID,
953 TIME_ID,
954 PERIOD_TYPE_ID,
955 CALENDAR_TYPE,
956 CURR_RECORD_TYPE_ID,
957 CURRENCY_CODE,
958 REVENUE,
959 LABOR_REVENUE,
960 RAW_COST,
961 BURDENED_COST,
962 BILL_RAW_COST,
963 BILL_BURDENED_COST,
964 LABOR_RAW_COST,
965 LABOR_BURDENED_COST,
966 BILL_LABOR_RAW_COST,
967 BILL_LABOR_BURDENED_COST,
968 REVENUE_WRITEOFF,
969 LABOR_HRS,
970 BILL_LABOR_HRS,
971 QUANTITY,
972 BILL_QUANTITY
973 )
974 select /*+ ordered
975 full(time) use_hash(time) parallel(time) swap_join_inputs(time)
976 full(fin) use_hash(fin) parallel(fin) */
977 p_worker_id WORKER_ID,
978 fin.PROJECT_ID,
979 fin.PROJECT_ORG_ID,
980 fin.PROJECT_ORGANIZATION_ID,
981 fin.PROJECT_TYPE_CLASS,
982 fin.EXP_EVT_TYPE_ID,
983 fin.WORK_TYPE_ID,
984 case when grouping(time.CAL_YEAR_ID) = 0 and
985 grouping(time.CAL_QTR_ID) = 0 and
986 grouping(time.CAL_PERIOD_ID) = 0
987 then time.CAL_PERIOD_ID
988 when grouping(time.CAL_YEAR_ID) = 0 and
989 grouping(time.CAL_QTR_ID) = 0 and
990 grouping(time.CAL_PERIOD_ID) = 1
991 then time.CAL_QTR_ID
992 when grouping(time.CAL_YEAR_ID) = 0 and
993 grouping(time.CAL_QTR_ID) = 1 and
994 grouping(time.CAL_PERIOD_ID) = 1
995 then time.CAL_YEAR_ID
996 end TIME_ID,
997 case when grouping(time.CAL_YEAR_ID) = 0 and
998 grouping(time.CAL_QTR_ID) = 0 and
999 grouping(time.CAL_PERIOD_ID) = 0
1000 then 32
1001 when grouping(time.CAL_YEAR_ID) = 0 and
1002 grouping(time.CAL_QTR_ID) = 0 and
1003 grouping(time.CAL_PERIOD_ID) = 1
1004 then 64
1005 when grouping(time.CAL_YEAR_ID) = 0 and
1006 grouping(time.CAL_QTR_ID) = 1 and
1007 grouping(time.CAL_PERIOD_ID) = 1
1008 then 128
1009 end PERIOD_TYPE_ID,
1010 'P' CALENDAR_TYPE,
1011 fin.CURR_RECORD_TYPE_ID,
1012 fin.CURRENCY_CODE,
1013 sum(fin.REVENUE) REVENUE,
1014 sum(fin.LABOR_REVENUE) LABOR_REVENUE,
1015 sum(fin.RAW_COST) RAW_COST,
1016 sum(fin.BURDENED_COST) BURDENED_COST,
1017 sum(fin.BILL_RAW_COST) BILL_RAW_COST,
1018 sum(fin.BILL_BURDENED_COST) BILL_BURDENED_COST,
1019 sum(fin.LABOR_RAW_COST) LABOR_RAW_COST,
1020 sum(fin.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
1021 sum(fin.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
1022 sum(fin.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
1023 sum(fin.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
1024 sum(fin.LABOR_HRS) LABOR_HRS,
1025 sum(fin.BILL_LABOR_HRS) BILL_LABOR_HRS,
1026 sum(fin.QUANTITY) QUANTITY,
1027 sum(fin.BILL_QUANTITY) BILL_QUANTITY
1028 from
1029 FII_TIME_CAL_DAY_MV time,
1030 PJI_FM_AGGR_FIN4 fin
1031 where
1032 fin.WORKER_ID = p_worker_id and
1033 fin.PERIOD_TYPE_ID = 1 and
1034 fin.CALENDAR_TYPE = 'P' and
1035 to_date(to_char(fin.TIME_ID), 'J') = time.REPORT_DATE and
1036 fin.PA_CALENDAR_ID = time.CALENDAR_ID
1037 group by
1038 fin.PROJECT_ID,
1039 fin.PROJECT_ORG_ID,
1040 fin.PROJECT_ORGANIZATION_ID,
1041 fin.PROJECT_TYPE_CLASS,
1042 fin.EXP_EVT_TYPE_ID,
1043 fin.WORK_TYPE_ID,
1044 rollup (time.CAL_YEAR_ID,
1045 time.CAL_QTR_ID,
1046 time.CAL_PERIOD_ID),
1047 fin.CURR_RECORD_TYPE_ID,
1051 grouping(time.CAL_QTR_ID) = 1 and
1048 fin.CURRENCY_CODE
1049 having
1050 not (grouping(time.CAL_YEAR_ID) = 1 and
1052 grouping(time.CAL_PERIOD_ID) = 1);
1053
1054 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPW_CAL_PA(p_worker_id);');
1055
1056 commit;
1057
1058 end EXPAND_FPW_CAL_PA;
1059
1060
1061 -- -----------------------------------------------------
1062 -- procedure EXPAND_FPW_CAL_GL
1063 -- -----------------------------------------------------
1064 procedure EXPAND_FPW_CAL_GL (p_worker_id in number) is
1065
1066 l_process varchar2(30);
1067
1068 begin
1069
1070 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process, 'GL_CALENDAR_FLAG') = 'N') then
1071 return;
1072 end if;
1073
1074 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1075
1076 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPW_CAL_GL(p_worker_id);')) then
1077 return;
1078 end if;
1079
1080 insert /*+ append parallel(fin4_i) */ into PJI_FM_AGGR_FIN4 fin4_i -- in EXPAND_FPW_CAL_GL
1081 (
1082 WORKER_ID,
1083 PROJECT_ID,
1084 PROJECT_ORG_ID,
1085 PROJECT_ORGANIZATION_ID,
1086 PROJECT_TYPE_CLASS,
1087 EXP_EVT_TYPE_ID,
1088 WORK_TYPE_ID,
1089 TIME_ID,
1090 PERIOD_TYPE_ID,
1091 CALENDAR_TYPE,
1092 CURR_RECORD_TYPE_ID,
1093 CURRENCY_CODE,
1094 REVENUE,
1095 LABOR_REVENUE,
1096 RAW_COST,
1097 BURDENED_COST,
1098 BILL_RAW_COST,
1099 BILL_BURDENED_COST,
1100 LABOR_RAW_COST,
1101 LABOR_BURDENED_COST,
1102 BILL_LABOR_RAW_COST,
1103 BILL_LABOR_BURDENED_COST,
1104 REVENUE_WRITEOFF,
1105 LABOR_HRS,
1106 BILL_LABOR_HRS,
1107 QUANTITY,
1108 BILL_QUANTITY
1109 )
1110 select /*+ ordered
1111 full(time) use_hash(time) parallel(time) swap_join_inputs(time)
1112 full(fin) use_hash(fin) parallel(fin) */
1113 p_worker_id WORKER_ID,
1114 fin.PROJECT_ID,
1115 fin.PROJECT_ORG_ID,
1116 fin.PROJECT_ORGANIZATION_ID,
1117 fin.PROJECT_TYPE_CLASS,
1118 fin.EXP_EVT_TYPE_ID,
1119 fin.WORK_TYPE_ID,
1120 case when grouping(time.CAL_YEAR_ID) = 0 and
1121 grouping(time.CAL_QTR_ID) = 0 and
1122 grouping(time.CAL_PERIOD_ID) = 0
1123 then time.CAL_PERIOD_ID
1124 when grouping(time.CAL_YEAR_ID) = 0 and
1125 grouping(time.CAL_QTR_ID) = 0 and
1126 grouping(time.CAL_PERIOD_ID) = 1
1127 then time.CAL_QTR_ID
1128 when grouping(time.CAL_YEAR_ID) = 0 and
1129 grouping(time.CAL_QTR_ID) = 1 and
1130 grouping(time.CAL_PERIOD_ID) = 1
1131 then time.CAL_YEAR_ID
1132 end TIME_ID,
1133 case when grouping(time.CAL_YEAR_ID) = 0 and
1134 grouping(time.CAL_QTR_ID) = 0 and
1135 grouping(time.CAL_PERIOD_ID) = 0
1136 then 32
1137 when grouping(time.CAL_YEAR_ID) = 0 and
1138 grouping(time.CAL_QTR_ID) = 0 and
1139 grouping(time.CAL_PERIOD_ID) = 1
1140 then 64
1141 when grouping(time.CAL_YEAR_ID) = 0 and
1142 grouping(time.CAL_QTR_ID) = 1 and
1143 grouping(time.CAL_PERIOD_ID) = 1
1144 then 128
1145 end PERIOD_TYPE_ID,
1146 'G' CALENDAR_TYPE,
1147 fin.CURR_RECORD_TYPE_ID,
1148 fin.CURRENCY_CODE,
1149 sum(fin.REVENUE) REVENUE,
1150 sum(fin.LABOR_REVENUE) LABOR_REVENUE,
1151 sum(fin.RAW_COST) RAW_COST,
1152 sum(fin.BURDENED_COST) BURDENED_COST,
1153 sum(fin.BILL_RAW_COST) BILL_RAW_COST,
1154 sum(fin.BILL_BURDENED_COST) BILL_BURDENED_COST,
1155 sum(fin.LABOR_RAW_COST) LABOR_RAW_COST,
1156 sum(fin.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
1157 sum(fin.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
1158 sum(fin.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
1159 sum(fin.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
1160 sum(fin.LABOR_HRS) LABOR_HRS,
1161 sum(fin.BILL_LABOR_HRS) BILL_LABOR_HRS,
1162 sum(fin.QUANTITY) QUANTITY,
1163 sum(fin.BILL_QUANTITY) BILL_QUANTITY
1164 from
1165 FII_TIME_CAL_DAY_MV time,
1166 PJI_FM_AGGR_FIN4 fin
1167 where
1168 fin.WORKER_ID = p_worker_id and
1169 fin.PERIOD_TYPE_ID = 1 and
1170 fin.CALENDAR_TYPE = 'C' and
1171 to_date(to_char(fin.TIME_ID), 'J') = time.REPORT_DATE and
1172 fin.GL_CALENDAR_ID = time.CALENDAR_ID
1173 group by
1174 fin.PROJECT_ID,
1175 fin.PROJECT_ORG_ID,
1176 fin.PROJECT_ORGANIZATION_ID,
1177 fin.PROJECT_TYPE_CLASS,
1178 fin.EXP_EVT_TYPE_ID,
1182 time.CAL_PERIOD_ID),
1179 fin.WORK_TYPE_ID,
1180 rollup (time.CAL_YEAR_ID,
1181 time.CAL_QTR_ID,
1183 fin.CURR_RECORD_TYPE_ID,
1184 fin.CURRENCY_CODE
1185 having
1186 not (grouping(time.CAL_YEAR_ID) = 1 and
1187 grouping(time.CAL_QTR_ID) = 1 and
1188 grouping(time.CAL_PERIOD_ID) = 1);
1189
1190 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPW_CAL_GL(p_worker_id);');
1191
1192 commit;
1193
1194 end EXPAND_FPW_CAL_GL;
1195
1196
1197 -- -----------------------------------------------------
1198 -- procedure EXPAND_FPW_CAL_WK
1199 -- -----------------------------------------------------
1200 procedure EXPAND_FPW_CAL_WK (p_worker_id in number) is
1201
1202 l_process varchar2(30);
1203 l_schema varchar2(30);
1204
1205 begin
1206
1207 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1208
1209 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPW_CAL_WK(p_worker_id);')) then
1210 return;
1211 end if;
1212
1213 insert /*+ append parallel(fin4_i) */ into PJI_FM_AGGR_FIN4 fin4_i -- in EXPAND_FPW_CAL_WK
1214 (
1215 WORKER_ID,
1216 PROJECT_ID,
1217 PROJECT_ORG_ID,
1218 PROJECT_ORGANIZATION_ID,
1219 PROJECT_TYPE_CLASS,
1220 EXP_EVT_TYPE_ID,
1221 WORK_TYPE_ID,
1222 TIME_ID,
1223 PERIOD_TYPE_ID,
1224 CALENDAR_TYPE,
1225 CURR_RECORD_TYPE_ID,
1226 CURRENCY_CODE,
1227 REVENUE,
1228 LABOR_REVENUE,
1229 RAW_COST,
1230 BURDENED_COST,
1231 BILL_RAW_COST,
1232 BILL_BURDENED_COST,
1233 LABOR_RAW_COST,
1234 LABOR_BURDENED_COST,
1235 BILL_LABOR_RAW_COST,
1236 BILL_LABOR_BURDENED_COST,
1237 REVENUE_WRITEOFF,
1238 LABOR_HRS,
1239 BILL_LABOR_HRS,
1240 QUANTITY,
1241 BILL_QUANTITY
1242 )
1243 select /*+ ordered
1244 full(time) use_hash(time) swap_join_inputs(time)
1245 full(fin) use_hash(fin) parallel(fin) */
1246 p_worker_id WORKER_ID,
1247 fin.PROJECT_ID,
1248 fin.PROJECT_ORG_ID,
1249 fin.PROJECT_ORGANIZATION_ID,
1250 fin.PROJECT_TYPE_CLASS,
1251 fin.EXP_EVT_TYPE_ID,
1252 fin.WORK_TYPE_ID,
1253 time.WEEK_ID TIME_ID,
1254 16 PERIOD_TYPE_ID,
1255 'E' CALENDAR_TYPE,
1256 bitand(fin.CURR_RECORD_TYPE_ID, 247) CURR_RECORD_TYPE_ID,
1257 fin.CURRENCY_CODE,
1258 sum(fin.REVENUE) REVENUE,
1259 sum(fin.LABOR_REVENUE) LABOR_REVENUE,
1260 sum(fin.RAW_COST) RAW_COST,
1261 sum(fin.BURDENED_COST) BURDENED_COST,
1262 sum(fin.BILL_RAW_COST) BILL_RAW_COST,
1263 sum(fin.BILL_BURDENED_COST) BILL_BURDENED_COST,
1264 sum(fin.LABOR_RAW_COST) LABOR_RAW_COST,
1265 sum(fin.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
1266 sum(fin.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
1267 sum(fin.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
1268 sum(fin.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
1269 sum(fin.LABOR_HRS) LABOR_HRS,
1270 sum(fin.BILL_LABOR_HRS) BILL_LABOR_HRS,
1271 sum(fin.QUANTITY) QUANTITY,
1272 sum(fin.BILL_QUANTITY) BILL_QUANTITY
1273 from
1274 FII_TIME_DAY time,
1275 PJI_FM_AGGR_FIN4 fin
1276 where
1277 fin.WORKER_ID = p_worker_id and
1278 fin.PERIOD_TYPE_ID = 1 and
1279 fin.CALENDAR_TYPE = 'C' and
1280 fin.CURR_RECORD_TYPE_ID not in (8, 256) and
1281 fin.TIME_ID = time.REPORT_DATE_JULIAN
1282 group by
1283 fin.PROJECT_ID,
1284 fin.PROJECT_ORG_ID,
1285 fin.PROJECT_ORGANIZATION_ID,
1286 fin.PROJECT_TYPE_CLASS,
1287 fin.EXP_EVT_TYPE_ID,
1288 fin.WORK_TYPE_ID,
1289 time.WEEK_ID,
1290 bitand(fin.CURR_RECORD_TYPE_ID, 247),
1291 fin.CURRENCY_CODE;
1292
1293 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPW_CAL_WK(p_worker_id);');
1294
1295 commit;
1296
1297 end EXPAND_FPW_CAL_WK;
1298
1299
1300 -- -----------------------------------------------------
1301 -- procedure EXPAND_FPE_CAL_EN
1302 -- -----------------------------------------------------
1303 procedure EXPAND_FPE_CAL_EN (p_worker_id in number) is
1304
1305 l_process varchar2(30);
1306
1307 begin
1308
1309 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1310
1311 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPE_CAL_EN(p_worker_id);')) then
1312 return;
1313 end if;
1314
1315 insert /*+ append parallel(fin5_i) */ into PJI_FM_AGGR_FIN5 fin5_i -- in EXPAND_FPE_CAL_EN
1316 (
1317 WORKER_ID,
1321 PROJECT_TYPE_CLASS,
1318 PROJECT_ID,
1319 PROJECT_ORG_ID,
1320 PROJECT_ORGANIZATION_ID,
1322 EXP_EVT_TYPE_ID,
1323 TIME_ID,
1324 PERIOD_TYPE_ID,
1325 CALENDAR_TYPE,
1326 CURR_RECORD_TYPE_ID,
1327 CURRENCY_CODE,
1328 REVENUE,
1329 LABOR_REVENUE,
1330 RAW_COST,
1331 BURDENED_COST,
1332 BILL_RAW_COST,
1333 BILL_BURDENED_COST,
1334 LABOR_RAW_COST,
1335 LABOR_BURDENED_COST,
1336 BILL_LABOR_RAW_COST,
1337 BILL_LABOR_BURDENED_COST,
1338 REVENUE_WRITEOFF,
1339 LABOR_HRS,
1340 BILL_LABOR_HRS,
1341 QUANTITY,
1342 BILL_QUANTITY
1343 )
1344 select /*+ ordered
1345 full(time) use_hash(time) swap_join_inputs(time)
1346 full(fin) use_hash(fin) parallel(fin) */
1347 p_worker_id WORKER_ID,
1348 fin.PROJECT_ID,
1349 fin.PROJECT_ORG_ID,
1350 fin.PROJECT_ORGANIZATION_ID,
1351 fin.PROJECT_TYPE_CLASS,
1352 fin.EXP_EVT_TYPE_ID,
1353 case when grouping(time.ENT_YEAR_ID) = 0 and
1354 grouping(time.ENT_QTR_ID) = 0 and
1355 grouping(time.ENT_PERIOD_ID) = 0
1356 then time.ENT_PERIOD_ID
1357 when grouping(time.ENT_YEAR_ID) = 0 and
1358 grouping(time.ENT_QTR_ID) = 0 and
1359 grouping(time.ENT_PERIOD_ID) = 1
1360 then time.ENT_QTR_ID
1361 when grouping(time.ENT_YEAR_ID) = 0 and
1362 grouping(time.ENT_QTR_ID) = 1 and
1363 grouping(time.ENT_PERIOD_ID) = 1
1364 then time.ENT_YEAR_ID
1365 end TIME_ID,
1366 case when grouping(time.ENT_YEAR_ID) = 0 and
1367 grouping(time.ENT_QTR_ID) = 0 and
1368 grouping(time.ENT_PERIOD_ID) = 0
1369 then 32
1370 when grouping(time.ENT_YEAR_ID) = 0 and
1371 grouping(time.ENT_QTR_ID) = 0 and
1372 grouping(time.ENT_PERIOD_ID) = 1
1373 then 64
1374 when grouping(time.ENT_YEAR_ID) = 0 and
1375 grouping(time.ENT_QTR_ID) = 1 and
1376 grouping(time.ENT_PERIOD_ID) = 1
1377 then 128
1378 end PERIOD_TYPE_ID,
1379 'E' CALENDAR_TYPE,
1380 bitand(fin.CURR_RECORD_TYPE_ID, 247) CURR_RECORD_TYPE_ID,
1381 fin.CURRENCY_CODE,
1382 sum(fin.REVENUE) REVENUE,
1383 sum(fin.LABOR_REVENUE) LABOR_REVENUE,
1384 sum(fin.RAW_COST) RAW_COST,
1385 sum(fin.BURDENED_COST) BURDENED_COST,
1386 sum(fin.BILL_RAW_COST) BILL_RAW_COST,
1387 sum(fin.BILL_BURDENED_COST) BILL_BURDENED_COST,
1388 sum(fin.LABOR_RAW_COST) LABOR_RAW_COST,
1389 sum(fin.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
1390 sum(fin.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
1391 sum(fin.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
1392 sum(fin.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
1393 sum(fin.LABOR_HRS) LABOR_HRS,
1394 sum(fin.BILL_LABOR_HRS) BILL_LABOR_HRS,
1395 sum(fin.QUANTITY) QUANTITY,
1399 PJI_FM_AGGR_FIN5 fin
1396 sum(fin.BILL_QUANTITY) BILL_QUANTITY
1397 from
1398 FII_TIME_DAY time,
1400 where
1401 fin.WORKER_ID = p_worker_id and
1402 fin.PERIOD_TYPE_ID = 1 and
1403 fin.CALENDAR_TYPE = 'C' and
1404 fin.CURR_RECORD_TYPE_ID not in (8, 256) and
1405 fin.TIME_ID = time.REPORT_DATE_JULIAN
1406 group by
1407 fin.PROJECT_ID,
1408 fin.PROJECT_ORG_ID,
1409 fin.PROJECT_ORGANIZATION_ID,
1410 fin.PROJECT_TYPE_CLASS,
1411 fin.EXP_EVT_TYPE_ID,
1412 rollup (time.ENT_YEAR_ID,
1413 time.ENT_QTR_ID,
1414 time.ENT_PERIOD_ID),
1415 bitand(fin.CURR_RECORD_TYPE_ID, 247),
1416 fin.CURRENCY_CODE
1417 having
1418 not (grouping(time.ENT_YEAR_ID) = 1 and
1419 grouping(time.ENT_QTR_ID) = 1 and
1420 grouping(time.ENT_PERIOD_ID) = 1);
1421
1422 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPE_CAL_EN(p_worker_id);');
1423
1424 commit;
1425
1426 end EXPAND_FPE_CAL_EN;
1427
1428
1429 -- -----------------------------------------------------
1430 -- procedure EXPAND_FPE_CAL_PA
1431 -- -----------------------------------------------------
1432 procedure EXPAND_FPE_CAL_PA (p_worker_id in number) is
1433
1434 l_process varchar2(30);
1435
1436 begin
1437
1438 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process, 'PA_CALENDAR_FLAG') = 'N') then
1439 return;
1440 end if;
1441
1442 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1443
1444 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPE_CAL_PA(p_worker_id);')) then
1445 return;
1446 end if;
1447
1448 insert /*+ append parallel(fin5_i) */ into PJI_FM_AGGR_FIN5 fin5_i -- in EXPAND_FPE_CAL_PA
1449 (
1450 WORKER_ID,
1451 PROJECT_ID,
1452 PROJECT_ORG_ID,
1453 PROJECT_ORGANIZATION_ID,
1454 PROJECT_TYPE_CLASS,
1455 EXP_EVT_TYPE_ID,
1456 TIME_ID,
1457 PERIOD_TYPE_ID,
1458 CALENDAR_TYPE,
1459 CURR_RECORD_TYPE_ID,
1460 CURRENCY_CODE,
1461 REVENUE,
1462 LABOR_REVENUE,
1463 RAW_COST,
1464 BURDENED_COST,
1465 BILL_RAW_COST,
1466 BILL_BURDENED_COST,
1467 LABOR_RAW_COST,
1468 LABOR_BURDENED_COST,
1469 BILL_LABOR_RAW_COST,
1470 BILL_LABOR_BURDENED_COST,
1471 REVENUE_WRITEOFF,
1472 LABOR_HRS,
1473 BILL_LABOR_HRS,
1474 QUANTITY,
1475 BILL_QUANTITY
1476 )
1477 select /*+ ordered
1478 full(time) use_hash(time) parallel(time) swap_join_inputs(time)
1479 full(fin) use_hash(fin) parallel(fin) */
1480 p_worker_id WORKER_ID,
1481 fin.PROJECT_ID,
1482 fin.PROJECT_ORG_ID,
1483 fin.PROJECT_ORGANIZATION_ID,
1484 fin.PROJECT_TYPE_CLASS,
1485 fin.EXP_EVT_TYPE_ID,
1486 case when grouping(time.CAL_YEAR_ID) = 0 and
1487 grouping(time.CAL_QTR_ID) = 0 and
1488 grouping(time.CAL_PERIOD_ID) = 0
1489 then time.CAL_PERIOD_ID
1490 when grouping(time.CAL_YEAR_ID) = 0 and
1491 grouping(time.CAL_QTR_ID) = 0 and
1492 grouping(time.CAL_PERIOD_ID) = 1
1493 then time.CAL_QTR_ID
1494 when grouping(time.CAL_YEAR_ID) = 0 and
1495 grouping(time.CAL_QTR_ID) = 1 and
1496 grouping(time.CAL_PERIOD_ID) = 1
1497 then time.CAL_YEAR_ID
1498 end TIME_ID,
1499 case when grouping(time.CAL_YEAR_ID) = 0 and
1500 grouping(time.CAL_QTR_ID) = 0 and
1501 grouping(time.CAL_PERIOD_ID) = 0
1502 then 32
1506 then 64
1503 when grouping(time.CAL_YEAR_ID) = 0 and
1504 grouping(time.CAL_QTR_ID) = 0 and
1505 grouping(time.CAL_PERIOD_ID) = 1
1507 when grouping(time.CAL_YEAR_ID) = 0 and
1508 grouping(time.CAL_QTR_ID) = 1 and
1509 grouping(time.CAL_PERIOD_ID) = 1
1510 then 128
1511 end PERIOD_TYPE_ID,
1512 'P' CALENDAR_TYPE,
1513 fin.CURR_RECORD_TYPE_ID,
1514 fin.CURRENCY_CODE,
1515 sum(fin.REVENUE) REVENUE,
1516 sum(fin.LABOR_REVENUE) LABOR_REVENUE,
1517 sum(fin.RAW_COST) RAW_COST,
1518 sum(fin.BURDENED_COST) BURDENED_COST,
1519 sum(fin.BILL_RAW_COST) BILL_RAW_COST,
1520 sum(fin.BILL_BURDENED_COST) BILL_BURDENED_COST,
1521 sum(fin.LABOR_RAW_COST) LABOR_RAW_COST,
1522 sum(fin.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
1523 sum(fin.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
1524 sum(fin.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
1525 sum(fin.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
1526 sum(fin.LABOR_HRS) LABOR_HRS,
1527 sum(fin.BILL_LABOR_HRS) BILL_LABOR_HRS,
1528 sum(fin.QUANTITY) QUANTITY,
1529 sum(fin.BILL_QUANTITY) BILL_QUANTITY
1530 from
1531 FII_TIME_CAL_DAY_MV time,
1532 PJI_FM_AGGR_FIN5 fin
1533 where
1534 fin.WORKER_ID = p_worker_id and
1535 fin.PERIOD_TYPE_ID = 1 and
1536 fin.CALENDAR_TYPE = 'P' and
1537 to_date(to_char(fin.TIME_ID), 'J') = time.REPORT_DATE and
1538 fin.PA_CALENDAR_ID = time.CALENDAR_ID
1539 group by
1540 fin.PROJECT_ID,
1541 fin.PROJECT_ORG_ID,
1542 fin.PROJECT_ORGANIZATION_ID,
1543 fin.PROJECT_TYPE_CLASS,
1544 fin.EXP_EVT_TYPE_ID,
1545 rollup (time.CAL_YEAR_ID,
1546 time.CAL_QTR_ID,
1547 time.CAL_PERIOD_ID),
1548 fin.CURR_RECORD_TYPE_ID,
1549 fin.CURRENCY_CODE
1550 having
1551 not (grouping(time.CAL_YEAR_ID) = 1 and
1552 grouping(time.CAL_QTR_ID) = 1 and
1553 grouping(time.CAL_PERIOD_ID) = 1);
1554
1555 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPE_CAL_PA(p_worker_id);');
1556
1557 commit;
1558
1559 end EXPAND_FPE_CAL_PA;
1560
1561
1562 -- -----------------------------------------------------
1563 -- procedure EXPAND_FPE_CAL_GL
1564 -- -----------------------------------------------------
1565 procedure EXPAND_FPE_CAL_GL (p_worker_id in number) is
1566
1567 l_process varchar2(30);
1568
1569 begin
1570
1571 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process, 'GL_CALENDAR_FLAG') = 'N') then
1572 return;
1573 end if;
1574
1575 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1576
1580
1577 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPE_CAL_GL(p_worker_id);')) then
1578 return;
1579 end if;
1581 insert /*+ append parallel(fin5_i) */ into PJI_FM_AGGR_FIN5 fin5_i -- in EXPAND_FPE_CAL_GL
1582 (
1583 WORKER_ID,
1584 PROJECT_ID,
1585 PROJECT_ORG_ID,
1586 PROJECT_ORGANIZATION_ID,
1587 PROJECT_TYPE_CLASS,
1588 EXP_EVT_TYPE_ID,
1589 TIME_ID,
1590 PERIOD_TYPE_ID,
1591 CALENDAR_TYPE,
1592 CURR_RECORD_TYPE_ID,
1593 CURRENCY_CODE,
1594 REVENUE,
1595 LABOR_REVENUE,
1596 RAW_COST,
1597 BURDENED_COST,
1598 BILL_RAW_COST,
1599 BILL_BURDENED_COST,
1600 LABOR_RAW_COST,
1601 LABOR_BURDENED_COST,
1602 BILL_LABOR_RAW_COST,
1603 BILL_LABOR_BURDENED_COST,
1604 REVENUE_WRITEOFF,
1605 LABOR_HRS,
1606 BILL_LABOR_HRS,
1607 QUANTITY,
1608 BILL_QUANTITY
1609 )
1610 select /*+ ordered
1611 full(time) use_hash(time) parallel(time) swap_join_inputs(time)
1612 full(fin) use_hash(fin) parallel(fin) */
1613 p_worker_id WORKER_ID,
1614 fin.PROJECT_ID,
1615 fin.PROJECT_ORG_ID,
1616 fin.PROJECT_ORGANIZATION_ID,
1617 fin.PROJECT_TYPE_CLASS,
1618 fin.EXP_EVT_TYPE_ID,
1619 case when grouping(time.CAL_YEAR_ID) = 0 and
1620 grouping(time.CAL_QTR_ID) = 0 and
1621 grouping(time.CAL_PERIOD_ID) = 0
1622 then time.CAL_PERIOD_ID
1623 when grouping(time.CAL_YEAR_ID) = 0 and
1624 grouping(time.CAL_QTR_ID) = 0 and
1625 grouping(time.CAL_PERIOD_ID) = 1
1626 then time.CAL_QTR_ID
1627 when grouping(time.CAL_YEAR_ID) = 0 and
1628 grouping(time.CAL_QTR_ID) = 1 and
1629 grouping(time.CAL_PERIOD_ID) = 1
1630 then time.CAL_YEAR_ID
1631 end TIME_ID,
1632 case when grouping(time.CAL_YEAR_ID) = 0 and
1633 grouping(time.CAL_QTR_ID) = 0 and
1634 grouping(time.CAL_PERIOD_ID) = 0
1635 then 32
1636 when grouping(time.CAL_YEAR_ID) = 0 and
1637 grouping(time.CAL_QTR_ID) = 0 and
1638 grouping(time.CAL_PERIOD_ID) = 1
1639 then 64
1640 when grouping(time.CAL_YEAR_ID) = 0 and
1641 grouping(time.CAL_QTR_ID) = 1 and
1642 grouping(time.CAL_PERIOD_ID) = 1
1643 then 128
1644 end PERIOD_TYPE_ID,
1645 'G' CALENDAR_TYPE,
1646 fin.CURR_RECORD_TYPE_ID,
1647 fin.CURRENCY_CODE,
1648 sum(fin.REVENUE) REVENUE,
1649 sum(fin.LABOR_REVENUE) LABOR_REVENUE,
1650 sum(fin.RAW_COST) RAW_COST,
1651 sum(fin.BURDENED_COST) BURDENED_COST,
1652 sum(fin.BILL_RAW_COST) BILL_RAW_COST,
1653 sum(fin.BILL_BURDENED_COST) BILL_BURDENED_COST,
1654 sum(fin.LABOR_RAW_COST) LABOR_RAW_COST,
1655 sum(fin.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
1656 sum(fin.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
1657 sum(fin.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
1658 sum(fin.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
1659 sum(fin.LABOR_HRS) LABOR_HRS,
1660 sum(fin.BILL_LABOR_HRS) BILL_LABOR_HRS,
1661 sum(fin.QUANTITY) QUANTITY,
1662 sum(fin.BILL_QUANTITY) BILL_QUANTITY
1663 from
1664 FII_TIME_CAL_DAY_MV time,
1665 PJI_FM_AGGR_FIN5 fin
1666 where
1667 fin.WORKER_ID = p_worker_id and
1668 fin.PERIOD_TYPE_ID = 1 and
1672 group by
1669 fin.CALENDAR_TYPE = 'C' and
1670 to_date(to_char(fin.TIME_ID), 'J') = time.REPORT_DATE and
1671 fin.GL_CALENDAR_ID = time.CALENDAR_ID
1673 fin.PROJECT_ID,
1674 fin.PROJECT_ORG_ID,
1675 fin.PROJECT_ORGANIZATION_ID,
1676 fin.PROJECT_TYPE_CLASS,
1677 fin.EXP_EVT_TYPE_ID,
1678 rollup (time.CAL_YEAR_ID,
1679 time.CAL_QTR_ID,
1680 time.CAL_PERIOD_ID),
1681 fin.CURR_RECORD_TYPE_ID,
1682 fin.CURRENCY_CODE
1683 having
1684 not (grouping(time.CAL_YEAR_ID) = 1 and
1685 grouping(time.CAL_QTR_ID) = 1 and
1686 grouping(time.CAL_PERIOD_ID) = 1);
1687
1688 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPE_CAL_GL(p_worker_id);');
1689
1690 commit;
1691
1692 end EXPAND_FPE_CAL_GL;
1693
1694
1695 -- -----------------------------------------------------
1696 -- procedure EXPAND_FPE_CAL_WK
1697 -- -----------------------------------------------------
1698 procedure EXPAND_FPE_CAL_WK (p_worker_id in number) is
1699
1700 l_process varchar2(30);
1701 l_schema varchar2(30);
1702
1703 begin
1704
1705 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1706
1707 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPE_CAL_WK(p_worker_id);')) then
1708 return;
1709 end if;
1710
1711 insert /*+ append parallel(fin5_i) */ into PJI_FM_AGGR_FIN5 fin5_i -- in EXPAND_FPE_CAL_WK
1712 (
1713 WORKER_ID,
1714 PROJECT_ID,
1715 PROJECT_ORG_ID,
1716 PROJECT_ORGANIZATION_ID,
1717 PROJECT_TYPE_CLASS,
1718 EXP_EVT_TYPE_ID,
1719 TIME_ID,
1720 PERIOD_TYPE_ID,
1721 CALENDAR_TYPE,
1722 CURR_RECORD_TYPE_ID,
1723 CURRENCY_CODE,
1724 REVENUE,
1725 LABOR_REVENUE,
1726 RAW_COST,
1727 BURDENED_COST,
1728 BILL_RAW_COST,
1729 BILL_BURDENED_COST,
1730 LABOR_RAW_COST,
1731 LABOR_BURDENED_COST,
1732 BILL_LABOR_RAW_COST,
1733 BILL_LABOR_BURDENED_COST,
1734 REVENUE_WRITEOFF,
1735 LABOR_HRS,
1736 BILL_LABOR_HRS,
1737 QUANTITY,
1738 BILL_QUANTITY
1739 )
1740 select /*+ ordered
1741 full(time) use_hash(time) swap_join_inputs(time)
1742 full(fin) use_hash(fin) parallel(fin) */
1743 p_worker_id WORKER_ID,
1744 fin.PROJECT_ID,
1745 fin.PROJECT_ORG_ID,
1746 fin.PROJECT_ORGANIZATION_ID,
1747 fin.PROJECT_TYPE_CLASS,
1748 fin.EXP_EVT_TYPE_ID,
1749 time.WEEK_ID TIME_ID,
1750 16 PERIOD_TYPE_ID,
1751 'E' CALENDAR_TYPE,
1752 bitand(fin.CURR_RECORD_TYPE_ID, 247) CURR_RECORD_TYPE_ID,
1753 fin.CURRENCY_CODE,
1754 sum(fin.REVENUE) REVENUE,
1755 sum(fin.LABOR_REVENUE) LABOR_REVENUE,
1756 sum(fin.RAW_COST) RAW_COST,
1757 sum(fin.BURDENED_COST) BURDENED_COST,
1758 sum(fin.BILL_RAW_COST) BILL_RAW_COST,
1759 sum(fin.BILL_BURDENED_COST) BILL_BURDENED_COST,
1760 sum(fin.LABOR_RAW_COST) LABOR_RAW_COST,
1761 sum(fin.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
1762 sum(fin.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
1763 sum(fin.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
1764 sum(fin.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
1765 sum(fin.LABOR_HRS) LABOR_HRS,
1766 sum(fin.BILL_LABOR_HRS) BILL_LABOR_HRS,
1767 sum(fin.QUANTITY) QUANTITY,
1771 PJI_FM_AGGR_FIN5 fin
1768 sum(fin.BILL_QUANTITY) BILL_QUANTITY
1769 from
1770 FII_TIME_DAY time,
1772 where
1773 fin.WORKER_ID = p_worker_id and
1774 fin.PERIOD_TYPE_ID = 1 and
1775 fin.CALENDAR_TYPE = 'C' and
1776 fin.CURR_RECORD_TYPE_ID not in (8, 256) and
1777 fin.TIME_ID = time.REPORT_DATE_JULIAN
1778 group by
1779 fin.PROJECT_ID,
1780 fin.PROJECT_ORG_ID,
1781 fin.PROJECT_ORGANIZATION_ID,
1782 fin.PROJECT_TYPE_CLASS,
1783 fin.EXP_EVT_TYPE_ID,
1784 time.WEEK_ID,
1785 bitand(fin.CURR_RECORD_TYPE_ID, 247),
1786 fin.CURRENCY_CODE;
1787
1788 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPE_CAL_WK(p_worker_id);');
1789
1790 commit;
1791
1792 end EXPAND_FPE_CAL_WK;
1793
1794
1795 -- -----------------------------------------------------
1796 -- procedure EXPAND_FPP_CAL_EN
1797 -- -----------------------------------------------------
1798 procedure EXPAND_FPP_CAL_EN (p_worker_id in number) is
1799
1800 l_process varchar2(30);
1801 l_extraction_type varchar2(30);
1802
1803 begin
1804
1805 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1806
1807 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPP_CAL_EN(p_worker_id);')) then
1808 return;
1809 end if;
1810
1811 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1812 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
1813
1814 insert /*+ append parallel(fin3_i) */ into PJI_FM_AGGR_FIN3 fin3_i -- in EXPAND_FPP_CAL_EN
1815 (
1816 WORKER_ID,
1817 PROJECT_ID,
1818 PROJECT_ORG_ID,
1819 PROJECT_ORGANIZATION_ID,
1820 PROJECT_TYPE_CLASS,
1821 TIME_ID,
1822 PERIOD_TYPE_ID,
1823 CALENDAR_TYPE,
1824 CURR_RECORD_TYPE_ID,
1825 CURRENCY_CODE,
1826 REVENUE,
1827 LABOR_REVENUE,
1828 RAW_COST,
1829 BURDENED_COST,
1830 BILL_RAW_COST,
1831 BILL_BURDENED_COST,
1832 LABOR_RAW_COST,
1833 LABOR_BURDENED_COST,
1834 BILL_LABOR_RAW_COST,
1835 BILL_LABOR_BURDENED_COST,
1836 REVENUE_WRITEOFF,
1837 LABOR_HRS,
1838 BILL_LABOR_HRS,
1839 CURR_BGT_REVENUE,
1840 CURR_BGT_RAW_COST,
1841 CURR_BGT_BURDENED_COST,
1842 CURR_BGT_LABOR_HRS,
1843 ORIG_BGT_REVENUE,
1844 ORIG_BGT_RAW_COST,
1845 ORIG_BGT_BURDENED_COST,
1846 ORIG_BGT_LABOR_HRS,
1847 FORECAST_REVENUE,
1848 FORECAST_RAW_COST,
1849 FORECAST_BURDENED_COST,
1850 FORECAST_LABOR_HRS
1851 )
1852 select
1853 p_worker_id,
1854 fin.PROJECT_ID,
1855 fin.PROJECT_ORG_ID,
1856 fin.PROJECT_ORGANIZATION_ID,
1857 fin.PROJECT_TYPE_CLASS,
1858 fin.TIME_ID,
1859 fin.PERIOD_TYPE_ID,
1863 sum(fin.REVENUE),
1860 fin.CALENDAR_TYPE,
1861 fin.CURR_RECORD_TYPE_ID,
1862 fin.CURRENCY_CODE,
1864 sum(fin.LABOR_REVENUE),
1865 sum(fin.RAW_COST),
1866 sum(fin.BURDENED_COST),
1867 sum(fin.BILL_RAW_COST),
1868 sum(fin.BILL_BURDENED_COST),
1869 sum(fin.LABOR_RAW_COST),
1870 sum(fin.LABOR_BURDENED_COST),
1871 sum(fin.BILL_LABOR_RAW_COST),
1872 sum(fin.BILL_LABOR_BURDENED_COST),
1873 sum(fin.REVENUE_WRITEOFF),
1874 sum(fin.LABOR_HRS),
1875 sum(fin.BILL_LABOR_HRS),
1876 sum(fin.CURR_BGT_REVENUE),
1877 sum(fin.CURR_BGT_RAW_COST),
1878 sum(fin.CURR_BGT_BURDENED_COST),
1879 sum(fin.CURR_BGT_LABOR_HRS),
1880 sum(fin.ORIG_BGT_REVENUE),
1881 sum(fin.ORIG_BGT_RAW_COST),
1882 sum(fin.ORIG_BGT_BURDENED_COST),
1883 sum(fin.ORIG_BGT_LABOR_HRS),
1884 sum(fin.FORECAST_REVENUE),
1885 sum(fin.FORECAST_RAW_COST),
1886 sum(fin.FORECAST_BURDENED_COST),
1887 sum(fin.FORECAST_LABOR_HRS)
1888 from
1889 (
1890 select /*+ ordered
1891 full(time) use_hash(time) swap_join_inputs(time)
1892 full(fin) use_hash(fin) parallel(fin) */
1893 fin.PROJECT_ID,
1894 fin.PROJECT_ORG_ID,
1895 fin.PROJECT_ORGANIZATION_ID,
1896 fin.PROJECT_TYPE_CLASS,
1897 case when grouping(time.ENT_YEAR_ID) = 0 and
1898 grouping(time.ENT_QTR_ID) = 0 and
1899 grouping(time.ENT_PERIOD_ID) = 0
1900 then time.ENT_PERIOD_ID
1901 when grouping(time.ENT_YEAR_ID) = 0 and
1902 grouping(time.ENT_QTR_ID) = 0 and
1903 grouping(time.ENT_PERIOD_ID) = 1
1904 then time.ENT_QTR_ID
1905 when grouping(time.ENT_YEAR_ID) = 0 and
1906 grouping(time.ENT_QTR_ID) = 1 and
1907 grouping(time.ENT_PERIOD_ID) = 1
1908 then time.ENT_YEAR_ID
1909 end TIME_ID,
1910 case when grouping(time.ENT_YEAR_ID) = 0 and
1911 grouping(time.ENT_QTR_ID) = 0 and
1912 grouping(time.ENT_PERIOD_ID) = 0
1913 then 32
1914 when grouping(time.ENT_YEAR_ID) = 0 and
1915 grouping(time.ENT_QTR_ID) = 0 and
1916 grouping(time.ENT_PERIOD_ID) = 1
1917 then 64
1918 when grouping(time.ENT_YEAR_ID) = 0 and
1919 grouping(time.ENT_QTR_ID) = 1 and
1920 grouping(time.ENT_PERIOD_ID) = 1
1921 then 128
1922 end PERIOD_TYPE_ID,
1923 'E' CALENDAR_TYPE,
1924 bitand(fin.CURR_RECORD_TYPE_ID, 247) CURR_RECORD_TYPE_ID,
1925 fin.CURRENCY_CODE,
1926 sum(fin.REVENUE) REVENUE,
1927 sum(fin.LABOR_REVENUE) LABOR_REVENUE,
1928 sum(fin.RAW_COST) RAW_COST,
1929 sum(fin.BURDENED_COST) BURDENED_COST,
1930 sum(fin.BILL_RAW_COST) BILL_RAW_COST,
1931 sum(fin.BILL_BURDENED_COST) BILL_BURDENED_COST,
1932 sum(fin.LABOR_RAW_COST) LABOR_RAW_COST,
1933 sum(fin.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
1934 sum(fin.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
1935 sum(fin.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
1936 sum(fin.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
1937 sum(fin.LABOR_HRS) LABOR_HRS,
1938 sum(fin.BILL_LABOR_HRS) BILL_LABOR_HRS,
1939 to_number(null) CURR_BGT_REVENUE,
1940 to_number(null) CURR_BGT_RAW_COST,
1941 to_number(null) CURR_BGT_BURDENED_COST,
1942 to_number(null) CURR_BGT_LABOR_HRS,
1943 to_number(null) ORIG_BGT_REVENUE,
1944 to_number(null) ORIG_BGT_RAW_COST,
1945 to_number(null) ORIG_BGT_BURDENED_COST,
1946 to_number(null) ORIG_BGT_LABOR_HRS,
1947 to_number(null) FORECAST_REVENUE,
1948 to_number(null) FORECAST_RAW_COST,
1949 to_number(null) FORECAST_BURDENED_COST,
1950 to_number(null) FORECAST_LABOR_HRS
1951 from
1952 FII_TIME_DAY time,
1953 PJI_FM_AGGR_FIN3 fin
1954 where
1955 fin.WORKER_ID = p_worker_id and
1956 fin.PERIOD_TYPE_ID = 1 and
1957 fin.CALENDAR_TYPE = 'C' and
1958 fin.CURR_RECORD_TYPE_ID not in (8, 256) and
1959 fin.TIME_ID = time.REPORT_DATE_JULIAN
1960 group by
1961 fin.PROJECT_ID,
1962 fin.PROJECT_ORG_ID,
1963 fin.PROJECT_ORGANIZATION_ID,
1964 fin.PROJECT_TYPE_CLASS,
1965 rollup (time.ENT_YEAR_ID,
1966 time.ENT_QTR_ID,
1967 time.ENT_PERIOD_ID),
1968 bitand(fin.CURR_RECORD_TYPE_ID, 247),
1969 fin.CURRENCY_CODE
1970 having
1971 not (grouping(time.ENT_YEAR_ID) = 1 and
1972 grouping(time.ENT_QTR_ID) = 1 and
1973 grouping(time.ENT_PERIOD_ID) = 1)
1974 union all
1975 select /*+ ordered
1976 full(period) use_hash(period) swap_join_inputs(period)
1977 full(qtr) use_hash(qtr) swap_join_inputs(qtr)
1981 tmp1.PROJECT_ORGANIZATION_ID,
1978 full(tmp1) use_hash(tmp1) parallel(tmp1) */ -- budget data
1979 tmp1.PROJECT_ID,
1980 tmp1.PROJECT_ORG_ID,
1982 tmp1.PROJECT_TYPE_CLASS,
1983 case when grouping(qtr.ENT_YEAR_ID) = 0 and
1984 grouping(period.ENT_QTR_ID) = 0 and
1985 grouping(period.ENT_PERIOD_ID) = 0
1986 then period.ENT_PERIOD_ID
1987 when grouping(qtr.ENT_YEAR_ID) = 0 and
1988 grouping(period.ENT_QTR_ID) = 0 and
1989 grouping(period.ENT_PERIOD_ID) = 1
1990 then period.ENT_QTR_ID
1991 when grouping(qtr.ENT_YEAR_ID) = 0 and
1992 grouping(period.ENT_QTR_ID) = 1 and
1993 grouping(period.ENT_PERIOD_ID) = 1
1994 then qtr.ENT_YEAR_ID
1995 end TIME_ID,
1996 case when grouping(qtr.ENT_YEAR_ID) = 0 and
1997 grouping(period.ENT_QTR_ID) = 0 and
1998 grouping(period.ENT_PERIOD_ID) = 0
1999 then 32
2000 when grouping(qtr.ENT_YEAR_ID) = 0 and
2001 grouping(period.ENT_QTR_ID) = 0 and
2002 grouping(period.ENT_PERIOD_ID) = 1
2003 then 64
2004 when grouping(qtr.ENT_YEAR_ID) = 0 and
2005 grouping(period.ENT_QTR_ID) = 1 and
2006 grouping(period.ENT_PERIOD_ID) = 1
2007 then 128
2008 end PERIOD_TYPE_ID,
2009 'E' CALENDAR_TYPE,
2010 bitand(tmp1.CURR_RECORD_TYPE_ID, 247) CURR_RECORD_TYPE_ID,
2011 tmp1.CURRENCY_CODE,
2012 to_number(null) REVENUE,
2013 to_number(null) LABOR_REVENUE,
2014 to_number(null) RAW_COST,
2015 to_number(null) BURDENED_COST,
2016 to_number(null) BILL_RAW_COST,
2017 to_number(null) BILL_BURDENED_COST,
2018 to_number(null) LABOR_RAW_COST,
2019 to_number(null) LABOR_BURDENED_COST,
2020 to_number(null) BILL_LABOR_RAW_COST,
2021 to_number(null) BILL_LABOR_BURDENED_COST,
2022 to_number(null) REVENUE_WRITEOFF,
2023 to_number(null) LABOR_HRS,
2024 to_number(null) BILL_LABOR_HRS,
2025 sum(tmp1.CURR_BGT_REVENUE) CURR_BGT_REVENUE,
2026 sum(tmp1.CURR_BGT_RAW_COST) CURR_BGT_RAW_COST,
2027 sum(tmp1.CURR_BGT_BRDN_COST) CURR_BGT_BURDENED_COST,
2028 sum(tmp1.CURR_BGT_LABOR_HRS) CURR_BGT_LABOR_HRS,
2029 sum(tmp1.CURR_ORIG_BGT_REVENUE) ORIG_BGT_REVENUE,
2030 sum(tmp1.CURR_ORIG_BGT_RAW_COST) ORIG_BGT_RAW_COST,
2031 sum(tmp1.CURR_ORIG_BGT_BRDN_COST) ORIG_BGT_BURDENED_COST,
2032 sum(tmp1.CURR_ORIG_BGT_LABOR_HRS) ORIG_BGT_LABOR_HRS,
2033 sum(tmp1.CURR_FORECAST_REVENUE) FORECAST_REVENUE,
2034 sum(tmp1.CURR_FORECAST_RAW_COST) FORECAST_RAW_COST,
2035 sum(tmp1.CURR_FORECAST_BRDN_COST) FORECAST_BURDENED_COST,
2036 sum(tmp1.CURR_FORECAST_LABOR_HRS) FORECAST_LABOR_HRS
2037 from
2038 FII_TIME_ENT_PERIOD period,
2039 FII_TIME_ENT_QTR qtr,
2040 PJI_FM_AGGR_PLN tmp1
2041 where
2042 tmp1.CALENDAR_TYPE_CODE = 'ENT' and
2043 tmp1.CURR_RECORD_TYPE_ID not in (8, 256) and
2044 tmp1.TIME_ID = period.ENT_PERIOD_ID and
2045 period.ENT_QTR_ID = qtr.ENT_QTR_ID
2046 group by
2047 tmp1.PROJECT_ID,
2048 tmp1.PROJECT_ORG_ID,
2049 tmp1.PROJECT_ORGANIZATION_ID,
2050 tmp1.PROJECT_TYPE_CLASS,
2051 rollup (qtr.ENT_YEAR_ID,
2052 period.ENT_QTR_ID,
2053 period.ENT_PERIOD_ID),
2054 bitand(tmp1.CURR_RECORD_TYPE_ID, 247),
2055 tmp1.CURRENCY_CODE
2056 having
2057 not (grouping(qtr.ENT_YEAR_ID) = 1 and
2058 grouping(period.ENT_QTR_ID) = 1 and
2059 grouping(period.ENT_PERIOD_ID) = 1)
2060 union all
2061 select /*+ ordered full(map) parallel(map)
2062 index(fpp, PJI_FP_PROJ_F_N2) use_nl(fpp) */ -- budget reversals
2063 fpp.PROJECT_ID,
2064 fpp.PROJECT_ORG_ID,
2065 fpp.PROJECT_ORGANIZATION_ID,
2066 fpp.PROJECT_TYPE_CLASS,
2067 fpp.TIME_ID,
2068 fpp.PERIOD_TYPE_ID,
2069 fpp.CALENDAR_TYPE,
2070 fpp.CURR_RECORD_TYPE_ID,
2074 to_number(null) RAW_COST,
2071 fpp.CURRENCY_CODE,
2072 to_number(null) REVENUE,
2073 to_number(null) LABOR_REVENUE,
2075 to_number(null) BURDENED_COST,
2076 to_number(null) BILL_RAW_COST,
2077 to_number(null) BILL_BURDENED_COST,
2078 to_number(null) LABOR_RAW_COST,
2079 to_number(null) LABOR_BURDENED_COST,
2080 to_number(null) BILL_LABOR_RAW_COST,
2081 to_number(null) BILL_LABOR_BURDENED_COST,
2082 to_number(null) REVENUE_WRITEOFF,
2083 to_number(null) LABOR_HRS,
2084 to_number(null) BILL_LABOR_HRS,
2085 case when map.REVENUE_BUDGET_C_VERSION <>
2086 map.REVENUE_BUDGET_N_VERSION
2087 then -fpp.CURR_BGT_REVENUE
2088 else to_number(null)
2089 end CURR_BGT_REVENUE,
2090 case when map.COST_BUDGET_C_VERSION <>
2091 map.COST_BUDGET_N_VERSION
2092 then -fpp.CURR_BGT_RAW_COST
2093 else to_number(null)
2094 end CURR_BGT_RAW_COST,
2095 case when map.COST_BUDGET_C_VERSION <>
2096 map.COST_BUDGET_N_VERSION
2097 then -fpp.CURR_BGT_BURDENED_COST
2098 else to_number(null)
2099 end CURR_BGT_BURDENED_COST,
2100 case when map.COST_BUDGET_C_VERSION <>
2101 map.COST_BUDGET_N_VERSION
2102 then -fpp.CURR_BGT_LABOR_HRS
2103 else to_number(null)
2104 end CURR_BGT_LABOR_HRS,
2105 case when map.REVENUE_BUDGET_CO_VERSION <>
2106 map.REVENUE_BUDGET_NO_VERSION
2107 then -fpp.ORIG_BGT_REVENUE
2108 else to_number(null)
2109 end ORIG_BGT_REVENUE,
2110 case when map.COST_BUDGET_CO_VERSION <>
2111 map.COST_BUDGET_NO_VERSION
2112 then -fpp.ORIG_BGT_RAW_COST
2113 else to_number(null)
2114 end ORIG_BGT_RAW_COST,
2115 case when map.COST_BUDGET_CO_VERSION <>
2116 map.COST_BUDGET_NO_VERSION
2117 then -fpp.ORIG_BGT_BURDENED_COST
2118 else to_number(null)
2119 end ORIG_BGT_BURDENED_COST,
2120 case when map.COST_BUDGET_CO_VERSION <>
2121 map.COST_BUDGET_NO_VERSION
2122 then -fpp.ORIG_BGT_LABOR_HRS
2123 else to_number(null)
2124 end ORIG_BGT_LABOR_HRS,
2125 case when map.REVENUE_FORECAST_C_VERSION <>
2126 map.REVENUE_FORECAST_N_VERSION
2127 then -fpp.FORECAST_REVENUE
2128 else to_number(null)
2129 end FORECAST_REVENUE,
2130 case when map.COST_FORECAST_C_VERSION <>
2131 map.COST_FORECAST_N_VERSION
2132 then -fpp.FORECAST_RAW_COST
2133 else to_number(null)
2134 end FORECAST_RAW_COST,
2135 case when map.COST_FORECAST_C_VERSION <>
2136 map.COST_FORECAST_N_VERSION
2137 then -fpp.FORECAST_BURDENED_COST
2138 else to_number(null)
2139 end FORECAST_BURDENED_COST,
2140 case when map.COST_FORECAST_C_VERSION <>
2141 map.COST_FORECAST_N_VERSION
2142 then -fpp.FORECAST_LABOR_HRS
2143 else to_number(null)
2144 end FORECAST_LABOR_HRS
2145 from
2146 PJI_PJI_PROJ_BATCH_MAP map,
2147 PJI_FP_PROJ_F fpp
2148 where
2149 l_extraction_type <> 'FULL' and
2150 map.WORKER_ID = p_worker_id and
2151 (map.REVENUE_BUDGET_C_VERSION <> map.REVENUE_BUDGET_N_VERSION or
2152 map.COST_BUDGET_C_VERSION <> map.COST_BUDGET_N_VERSION or
2153 map.REVENUE_BUDGET_CO_VERSION <> map.REVENUE_BUDGET_NO_VERSION or
2154 map.COST_BUDGET_CO_VERSION <> map.COST_BUDGET_NO_VERSION or
2155 map.REVENUE_FORECAST_C_VERSION <> map.REVENUE_FORECAST_N_VERSION or
2156 map.COST_FORECAST_C_VERSION <> map.COST_FORECAST_N_VERSION) and
2157 map.PROJECT_ID = fpp.PROJECT_ID and
2158 fpp.CALENDAR_TYPE = 'E' and
2159 fpp.PERIOD_TYPE_ID <> 1
2160 ) fin
2161 group by
2162 fin.PROJECT_ID,
2163 fin.PROJECT_ORG_ID,
2164 fin.PROJECT_ORGANIZATION_ID,
2165 fin.PROJECT_TYPE_CLASS,
2166 fin.TIME_ID,
2167 fin.PERIOD_TYPE_ID,
2168 fin.CALENDAR_TYPE,
2169 fin.CURR_RECORD_TYPE_ID,
2170 fin.CURRENCY_CODE;
2171
2172 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPP_CAL_EN(p_worker_id);');
2173
2174 commit;
2175
2176 end EXPAND_FPP_CAL_EN;
2177
2178
2179 -- -----------------------------------------------------
2180 -- procedure EXPAND_FPP_CAL_PA
2181 -- -----------------------------------------------------
2182 procedure EXPAND_FPP_CAL_PA (p_worker_id in number) is
2183
2184 l_process varchar2(30);
2185 l_extraction_type varchar2(30);
2186
2187 begin
2188
2192
2189 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process, 'PA_CALENDAR_FLAG') = 'N') then
2190 return;
2191 end if;
2193 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2194
2195 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPP_CAL_PA(p_worker_id);')) then
2196 return;
2197 end if;
2198
2199 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2200 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
2201
2202 insert /*+ append parallel(fin3_i) */ into PJI_FM_AGGR_FIN3 fin3_i -- in EXPAND_FPP_CAL_PA
2203 (
2204 WORKER_ID,
2205 PROJECT_ID,
2206 PROJECT_ORG_ID,
2207 PROJECT_ORGANIZATION_ID,
2208 PROJECT_TYPE_CLASS,
2209 TIME_ID,
2210 PERIOD_TYPE_ID,
2211 CALENDAR_TYPE,
2212 CURR_RECORD_TYPE_ID,
2213 CURRENCY_CODE,
2214 REVENUE,
2215 LABOR_REVENUE,
2216 RAW_COST,
2217 BURDENED_COST,
2218 BILL_RAW_COST,
2219 BILL_BURDENED_COST,
2220 LABOR_RAW_COST,
2221 LABOR_BURDENED_COST,
2222 BILL_LABOR_RAW_COST,
2223 BILL_LABOR_BURDENED_COST,
2224 REVENUE_WRITEOFF,
2225 LABOR_HRS,
2226 BILL_LABOR_HRS,
2227 CURR_BGT_REVENUE,
2228 CURR_BGT_RAW_COST,
2229 CURR_BGT_BURDENED_COST,
2230 CURR_BGT_LABOR_HRS,
2231 ORIG_BGT_REVENUE,
2232 ORIG_BGT_RAW_COST,
2233 ORIG_BGT_BURDENED_COST,
2234 ORIG_BGT_LABOR_HRS,
2235 FORECAST_REVENUE,
2236 FORECAST_RAW_COST,
2237 FORECAST_BURDENED_COST,
2238 FORECAST_LABOR_HRS
2239 )
2240 select
2241 p_worker_id,
2242 fin.PROJECT_ID,
2243 fin.PROJECT_ORG_ID,
2244 fin.PROJECT_ORGANIZATION_ID,
2245 fin.PROJECT_TYPE_CLASS,
2246 fin.TIME_ID,
2247 fin.PERIOD_TYPE_ID,
2248 fin.CALENDAR_TYPE,
2249 fin.CURR_RECORD_TYPE_ID,
2250 fin.CURRENCY_CODE,
2251 sum(fin.REVENUE),
2252 sum(fin.LABOR_REVENUE),
2253 sum(fin.RAW_COST),
2254 sum(fin.BURDENED_COST),
2255 sum(fin.BILL_RAW_COST),
2256 sum(fin.BILL_BURDENED_COST),
2257 sum(fin.LABOR_RAW_COST),
2258 sum(fin.LABOR_BURDENED_COST),
2259 sum(fin.BILL_LABOR_RAW_COST),
2260 sum(fin.BILL_LABOR_BURDENED_COST),
2261 sum(fin.REVENUE_WRITEOFF),
2262 sum(fin.LABOR_HRS),
2263 sum(fin.BILL_LABOR_HRS),
2264 sum(fin.CURR_BGT_REVENUE),
2265 sum(fin.CURR_BGT_RAW_COST),
2266 sum(fin.CURR_BGT_BURDENED_COST),
2267 sum(fin.CURR_BGT_LABOR_HRS),
2268 sum(fin.ORIG_BGT_REVENUE),
2269 sum(fin.ORIG_BGT_RAW_COST),
2270 sum(fin.ORIG_BGT_BURDENED_COST),
2271 sum(fin.ORIG_BGT_LABOR_HRS),
2272 sum(fin.FORECAST_REVENUE),
2273 sum(fin.FORECAST_RAW_COST),
2274 sum(fin.FORECAST_BURDENED_COST),
2275 sum(fin.FORECAST_LABOR_HRS)
2276 from
2277 (
2278 select /*+ ordered
2279 full(time) use_hash(time) parallel(time) swap_join_inputs(time)
2280 full(fin) use_hash(fin) parallel(fin) */
2281 fin.PROJECT_ID,
2282 fin.PROJECT_ORG_ID,
2283 fin.PROJECT_ORGANIZATION_ID,
2284 fin.PROJECT_TYPE_CLASS,
2285 case when grouping(time.CAL_YEAR_ID) = 0 and
2286 grouping(time.CAL_QTR_ID) = 0 and
2287 grouping(time.CAL_PERIOD_ID) = 0
2288 then time.CAL_PERIOD_ID
2289 when grouping(time.CAL_YEAR_ID) = 0 and
2290 grouping(time.CAL_QTR_ID) = 0 and
2291 grouping(time.CAL_PERIOD_ID) = 1
2292 then time.CAL_QTR_ID
2293 when grouping(time.CAL_YEAR_ID) = 0 and
2294 grouping(time.CAL_QTR_ID) = 1 and
2295 grouping(time.CAL_PERIOD_ID) = 1
2296 then time.CAL_YEAR_ID
2297 end TIME_ID,
2298 case when grouping(time.CAL_YEAR_ID) = 0 and
2299 grouping(time.CAL_QTR_ID) = 0 and
2300 grouping(time.CAL_PERIOD_ID) = 0
2301 then 32
2302 when grouping(time.CAL_YEAR_ID) = 0 and
2303 grouping(time.CAL_QTR_ID) = 0 and
2304 grouping(time.CAL_PERIOD_ID) = 1
2305 then 64
2306 when grouping(time.CAL_YEAR_ID) = 0 and
2307 grouping(time.CAL_QTR_ID) = 1 and
2308 grouping(time.CAL_PERIOD_ID) = 1
2309 then 128
2310 end PERIOD_TYPE_ID,
2314 sum(fin.REVENUE) REVENUE,
2311 'P' CALENDAR_TYPE,
2312 fin.CURR_RECORD_TYPE_ID,
2313 fin.CURRENCY_CODE,
2315 sum(fin.LABOR_REVENUE) LABOR_REVENUE,
2316 sum(fin.RAW_COST) RAW_COST,
2317 sum(fin.BURDENED_COST) BURDENED_COST,
2318 sum(fin.BILL_RAW_COST) BILL_RAW_COST,
2319 sum(fin.BILL_BURDENED_COST) BILL_BURDENED_COST,
2320 sum(fin.LABOR_RAW_COST) LABOR_RAW_COST,
2321 sum(fin.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
2322 sum(fin.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
2323 sum(fin.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
2324 sum(fin.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
2325 sum(fin.LABOR_HRS) LABOR_HRS,
2326 sum(fin.BILL_LABOR_HRS) BILL_LABOR_HRS,
2327 to_number(null) CURR_BGT_REVENUE,
2328 to_number(null) CURR_BGT_RAW_COST,
2329 to_number(null) CURR_BGT_BURDENED_COST,
2330 to_number(null) CURR_BGT_LABOR_HRS,
2331 to_number(null) ORIG_BGT_REVENUE,
2332 to_number(null) ORIG_BGT_RAW_COST,
2333 to_number(null) ORIG_BGT_BURDENED_COST,
2334 to_number(null) ORIG_BGT_LABOR_HRS,
2335 to_number(null) FORECAST_REVENUE,
2336 to_number(null) FORECAST_RAW_COST,
2337 to_number(null) FORECAST_BURDENED_COST,
2338 to_number(null) FORECAST_LABOR_HRS
2339 from
2340 FII_TIME_CAL_DAY_MV time,
2341 PJI_FM_AGGR_FIN3 fin
2342 where
2343 fin.WORKER_ID = p_worker_id and
2344 fin.PERIOD_TYPE_ID = 1 and
2345 fin.CALENDAR_TYPE = 'P' and
2346 to_date(to_char(fin.TIME_ID), 'J') = time.REPORT_DATE and
2347 fin.PA_CALENDAR_ID = time.CALENDAR_ID
2348 group by
2349 fin.PROJECT_ID,
2350 fin.PROJECT_ORG_ID,
2351 fin.PROJECT_ORGANIZATION_ID,
2352 fin.PROJECT_TYPE_CLASS,
2353 rollup (time.CAL_YEAR_ID,
2354 time.CAL_QTR_ID,
2355 time.CAL_PERIOD_ID),
2356 fin.CURR_RECORD_TYPE_ID,
2357 fin.CURRENCY_CODE
2358 having
2359 not (grouping(time.CAL_YEAR_ID) = 1 and
2360 grouping(time.CAL_QTR_ID) = 1 and
2361 grouping(time.CAL_PERIOD_ID) = 1)
2362 union all
2363 select /*+ ordered
2364 full(period) use_hash(period) swap_join_inputs(period)
2365 full(qtr) use_hash(qtr) swap_join_inputs(qtr)
2366 full(tmp1) use_hash(tmp1) parallel(tmp1) */ -- budget data
2367 tmp1.PROJECT_ID,
2368 tmp1.PROJECT_ORG_ID,
2369 tmp1.PROJECT_ORGANIZATION_ID,
2370 tmp1.PROJECT_TYPE_CLASS,
2371 case when grouping(qtr.CAL_YEAR_ID) = 0 and
2372 grouping(period.CAL_QTR_ID) = 0 and
2373 grouping(period.CAL_PERIOD_ID) = 0
2374 then period.CAL_PERIOD_ID
2375 when grouping(qtr.CAL_YEAR_ID) = 0 and
2376 grouping(period.CAL_QTR_ID) = 0 and
2377 grouping(period.CAL_PERIOD_ID) = 1
2378 then period.CAL_QTR_ID
2379 when grouping(qtr.CAL_YEAR_ID) = 0 and
2380 grouping(period.CAL_QTR_ID) = 1 and
2381 grouping(period.CAL_PERIOD_ID) = 1
2382 then qtr.CAL_YEAR_ID
2383 end TIME_ID,
2384 case when grouping(qtr.CAL_YEAR_ID) = 0 and
2385 grouping(period.CAL_QTR_ID) = 0 and
2386 grouping(period.CAL_PERIOD_ID) = 0
2387 then 32
2388 when grouping(qtr.CAL_YEAR_ID) = 0 and
2389 grouping(period.CAL_QTR_ID) = 0 and
2390 grouping(period.CAL_PERIOD_ID) = 1
2391 then 64
2392 when grouping(qtr.CAL_YEAR_ID) = 0 and
2393 grouping(period.CAL_QTR_ID) = 1 and
2394 grouping(period.CAL_PERIOD_ID) = 1
2395 then 128
2396 end PERIOD_TYPE_ID,
2397 'P' CALENDAR_TYPE,
2398 tmp1.CURR_RECORD_TYPE_ID,
2399 tmp1.CURRENCY_CODE,
2400 to_number(null) REVENUE,
2401 to_number(null) LABOR_REVENUE,
2402 to_number(null) RAW_COST,
2403 to_number(null) BURDENED_COST,
2404 to_number(null) BILL_RAW_COST,
2405 to_number(null) BILL_BURDENED_COST,
2406 to_number(null) LABOR_RAW_COST,
2407 to_number(null) LABOR_BURDENED_COST,
2408 to_number(null) BILL_LABOR_RAW_COST,
2409 to_number(null) BILL_LABOR_BURDENED_COST,
2410 to_number(null) REVENUE_WRITEOFF,
2411 to_number(null) LABOR_HRS,
2412 to_number(null) BILL_LABOR_HRS,
2413 sum(tmp1.CURR_BGT_REVENUE) CURR_BGT_REVENUE,
2414 sum(tmp1.CURR_BGT_RAW_COST) CURR_BGT_RAW_COST,
2415 sum(tmp1.CURR_BGT_BRDN_COST) CURR_BGT_BURDENED_COST,
2419 sum(tmp1.CURR_ORIG_BGT_BRDN_COST) ORIG_BGT_BURDENED_COST,
2416 sum(tmp1.CURR_BGT_LABOR_HRS) CURR_BGT_LABOR_HRS,
2417 sum(tmp1.CURR_ORIG_BGT_REVENUE) ORIG_BGT_REVENUE,
2418 sum(tmp1.CURR_ORIG_BGT_RAW_COST) ORIG_BGT_RAW_COST,
2420 sum(tmp1.CURR_ORIG_BGT_LABOR_HRS) ORIG_BGT_LABOR_HRS,
2421 sum(tmp1.CURR_FORECAST_REVENUE) FORECAST_REVENUE,
2422 sum(tmp1.CURR_FORECAST_RAW_COST) FORECAST_RAW_COST,
2423 sum(tmp1.CURR_FORECAST_BRDN_COST) FORECAST_BURDENED_COST,
2424 sum(tmp1.CURR_FORECAST_LABOR_HRS) FORECAST_LABOR_HRS
2425 from
2426 FII_TIME_CAL_PERIOD period,
2427 FII_TIME_CAL_QTR qtr,
2428 PJI_FM_AGGR_PLN tmp1
2429 where
2430 tmp1.CALENDAR_TYPE_CODE = 'PA' and
2431 tmp1.TIME_ID = period.CAL_PERIOD_ID and
2432 period.CAL_QTR_ID = qtr.CAL_QTR_ID
2433 group by
2434 tmp1.PROJECT_ID,
2435 tmp1.PROJECT_ORG_ID,
2436 tmp1.PROJECT_ORGANIZATION_ID,
2437 tmp1.PROJECT_TYPE_CLASS,
2438 rollup (qtr.CAL_YEAR_ID,
2439 period.CAL_QTR_ID,
2440 period.CAL_PERIOD_ID),
2441 tmp1.CURR_RECORD_TYPE_ID,
2442 tmp1.CURRENCY_CODE
2443 having
2444 not (grouping(qtr.CAL_YEAR_ID) = 1 and
2445 grouping(period.CAL_QTR_ID) = 1 and
2446 grouping(period.CAL_PERIOD_ID) = 1)
2447 union all
2448 select /*+ ordered full(map) parallel(map)
2449 index(fpp, PJI_FP_PROJ_F_N2) use_nl(fpp) */ -- budget reversals
2450 fpp.PROJECT_ID,
2451 fpp.PROJECT_ORG_ID,
2452 fpp.PROJECT_ORGANIZATION_ID,
2453 fpp.PROJECT_TYPE_CLASS,
2454 fpp.TIME_ID,
2455 fpp.PERIOD_TYPE_ID,
2456 fpp.CALENDAR_TYPE,
2457 fpp.CURR_RECORD_TYPE_ID,
2458 fpp.CURRENCY_CODE,
2459 to_number(null) REVENUE,
2460 to_number(null) LABOR_REVENUE,
2461 to_number(null) RAW_COST,
2462 to_number(null) BURDENED_COST,
2463 to_number(null) BILL_RAW_COST,
2464 to_number(null) BILL_BURDENED_COST,
2465 to_number(null) LABOR_RAW_COST,
2466 to_number(null) LABOR_BURDENED_COST,
2467 to_number(null) BILL_LABOR_RAW_COST,
2468 to_number(null) BILL_LABOR_BURDENED_COST,
2469 to_number(null) REVENUE_WRITEOFF,
2470 to_number(null) LABOR_HRS,
2471 to_number(null) BILL_LABOR_HRS,
2472 case when map.REVENUE_BUDGET_C_VERSION <>
2473 map.REVENUE_BUDGET_N_VERSION
2474 then -fpp.CURR_BGT_REVENUE
2475 else to_number(null)
2476 end CURR_BGT_REVENUE,
2477 case when map.COST_BUDGET_C_VERSION <>
2478 map.COST_BUDGET_N_VERSION
2479 then -fpp.CURR_BGT_RAW_COST
2480 else to_number(null)
2481 end CURR_BGT_RAW_COST,
2482 case when map.COST_BUDGET_C_VERSION <>
2483 map.COST_BUDGET_N_VERSION
2484 then -fpp.CURR_BGT_BURDENED_COST
2485 else to_number(null)
2486 end CURR_BGT_BURDENED_COST,
2487 case when map.COST_BUDGET_C_VERSION <>
2488 map.COST_BUDGET_N_VERSION
2489 then -fpp.CURR_BGT_LABOR_HRS
2490 else to_number(null)
2491 end CURR_BGT_LABOR_HRS,
2492 case when map.REVENUE_BUDGET_CO_VERSION <>
2493 map.REVENUE_BUDGET_NO_VERSION
2494 then -fpp.ORIG_BGT_REVENUE
2495 else to_number(null)
2496 end ORIG_BGT_REVENUE,
2497 case when map.COST_BUDGET_CO_VERSION <>
2498 map.COST_BUDGET_NO_VERSION
2499 then -fpp.ORIG_BGT_RAW_COST
2500 else to_number(null)
2501 end ORIG_BGT_RAW_COST,
2502 case when map.COST_BUDGET_CO_VERSION <>
2503 map.COST_BUDGET_NO_VERSION
2504 then -fpp.ORIG_BGT_BURDENED_COST
2505 else to_number(null)
2506 end ORIG_BGT_BURDENED_COST,
2507 case when map.COST_BUDGET_CO_VERSION <>
2508 map.COST_BUDGET_NO_VERSION
2509 then -fpp.ORIG_BGT_LABOR_HRS
2510 else to_number(null)
2511 end ORIG_BGT_LABOR_HRS,
2512 case when map.REVENUE_FORECAST_C_VERSION <>
2513 map.REVENUE_FORECAST_N_VERSION
2514 then -fpp.FORECAST_REVENUE
2515 else to_number(null)
2516 end FORECAST_REVENUE,
2517 case when map.COST_FORECAST_C_VERSION <>
2518 map.COST_FORECAST_N_VERSION
2519 then -fpp.FORECAST_RAW_COST
2520 else to_number(null)
2521 end FORECAST_RAW_COST,
2522 case when map.COST_FORECAST_C_VERSION <>
2523 map.COST_FORECAST_N_VERSION
2524 then -fpp.FORECAST_BURDENED_COST
2525 else to_number(null)
2529 then -fpp.FORECAST_LABOR_HRS
2526 end FORECAST_BURDENED_COST,
2527 case when map.COST_FORECAST_C_VERSION <>
2528 map.COST_FORECAST_N_VERSION
2530 else to_number(null)
2531 end FORECAST_LABOR_HRS
2532 from
2533 PJI_PJI_PROJ_BATCH_MAP map,
2534 PJI_FP_PROJ_F fpp
2535 where
2536 l_extraction_type <> 'FULL' and
2537 map.WORKER_ID = p_worker_id and
2538 (map.REVENUE_BUDGET_C_VERSION <> map.REVENUE_BUDGET_N_VERSION or
2539 map.COST_BUDGET_C_VERSION <> map.COST_BUDGET_N_VERSION or
2540 map.REVENUE_BUDGET_CO_VERSION <> map.REVENUE_BUDGET_NO_VERSION or
2541 map.COST_BUDGET_CO_VERSION <> map.COST_BUDGET_NO_VERSION or
2542 map.REVENUE_FORECAST_C_VERSION <> map.REVENUE_FORECAST_N_VERSION or
2543 map.COST_FORECAST_C_VERSION <> map.COST_FORECAST_N_VERSION) and
2544 map.PROJECT_ID = fpp.PROJECT_ID and
2545 fpp.CALENDAR_TYPE = 'P' and
2546 fpp.PERIOD_TYPE_ID <> 1
2547 ) fin
2548 group by
2549 fin.PROJECT_ID,
2550 fin.PROJECT_ORG_ID,
2551 fin.PROJECT_ORGANIZATION_ID,
2552 fin.PROJECT_TYPE_CLASS,
2553 fin.TIME_ID,
2554 fin.PERIOD_TYPE_ID,
2555 fin.CALENDAR_TYPE,
2556 fin.CURR_RECORD_TYPE_ID,
2557 fin.CURRENCY_CODE;
2558
2559 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPP_CAL_PA(p_worker_id);');
2560
2561 commit;
2562
2563 end EXPAND_FPP_CAL_PA;
2564
2565
2566 -- -----------------------------------------------------
2567 -- procedure EXPAND_FPP_CAL_GL
2568 -- -----------------------------------------------------
2569 procedure EXPAND_FPP_CAL_GL (p_worker_id in number) is
2570
2571 l_process varchar2(30);
2572 l_extraction_type varchar2(30);
2573
2574 begin
2575
2576 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process, 'GL_CALENDAR_FLAG') = 'N') then
2577 return;
2578 end if;
2579
2580 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2581
2582 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPP_CAL_GL(p_worker_id);')) then
2583 return;
2584 end if;
2585
2586 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2587 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
2588
2589 insert /*+ append parallel(fin3_i) */ into PJI_FM_AGGR_FIN3 fin3_i -- in EXPAND_FPP_CAL_GL
2590 (
2591 WORKER_ID,
2592 PROJECT_ID,
2593 PROJECT_ORG_ID,
2594 PROJECT_ORGANIZATION_ID,
2595 PROJECT_TYPE_CLASS,
2596 TIME_ID,
2597 PERIOD_TYPE_ID,
2598 CALENDAR_TYPE,
2599 CURR_RECORD_TYPE_ID,
2600 CURRENCY_CODE,
2601 REVENUE,
2602 LABOR_REVENUE,
2603 RAW_COST,
2604 BURDENED_COST,
2605 BILL_RAW_COST,
2606 BILL_BURDENED_COST,
2607 LABOR_RAW_COST,
2608 LABOR_BURDENED_COST,
2609 BILL_LABOR_RAW_COST,
2610 BILL_LABOR_BURDENED_COST,
2611 REVENUE_WRITEOFF,
2612 LABOR_HRS,
2613 BILL_LABOR_HRS,
2614 CURR_BGT_REVENUE,
2615 CURR_BGT_RAW_COST,
2616 CURR_BGT_BURDENED_COST,
2617 CURR_BGT_LABOR_HRS,
2618 ORIG_BGT_REVENUE,
2619 ORIG_BGT_RAW_COST,
2620 ORIG_BGT_BURDENED_COST,
2621 ORIG_BGT_LABOR_HRS,
2622 FORECAST_REVENUE,
2623 FORECAST_RAW_COST,
2624 FORECAST_BURDENED_COST,
2625 FORECAST_LABOR_HRS
2626 )
2627 select
2628 p_worker_id,
2629 fin.PROJECT_ID,
2630 fin.PROJECT_ORG_ID,
2631 fin.PROJECT_ORGANIZATION_ID,
2632 fin.PROJECT_TYPE_CLASS,
2633 fin.TIME_ID,
2634 fin.PERIOD_TYPE_ID,
2635 fin.CALENDAR_TYPE,
2636 decode(fin.PERIOD_TYPE_ID,
2637 32, fin.CURR_RECORD_TYPE_ID,
2638 bitand(fin.CURR_RECORD_TYPE_ID,
2639 247)) CURR_RECORD_TYPE_ID,
2640 fin.CURRENCY_CODE,
2641 sum(fin.REVENUE),
2642 sum(fin.LABOR_REVENUE),
2643 sum(fin.RAW_COST),
2644 sum(fin.BURDENED_COST),
2645 sum(fin.BILL_RAW_COST),
2649 sum(fin.BILL_LABOR_RAW_COST),
2646 sum(fin.BILL_BURDENED_COST),
2647 sum(fin.LABOR_RAW_COST),
2648 sum(fin.LABOR_BURDENED_COST),
2650 sum(fin.BILL_LABOR_BURDENED_COST),
2651 sum(fin.REVENUE_WRITEOFF),
2652 sum(fin.LABOR_HRS),
2653 sum(fin.BILL_LABOR_HRS),
2654 sum(fin.CURR_BGT_REVENUE),
2655 sum(fin.CURR_BGT_RAW_COST),
2656 sum(fin.CURR_BGT_BURDENED_COST),
2657 sum(fin.CURR_BGT_LABOR_HRS),
2658 sum(fin.ORIG_BGT_REVENUE),
2659 sum(fin.ORIG_BGT_RAW_COST),
2660 sum(fin.ORIG_BGT_BURDENED_COST),
2661 sum(fin.ORIG_BGT_LABOR_HRS),
2662 sum(fin.FORECAST_REVENUE),
2663 sum(fin.FORECAST_RAW_COST),
2664 sum(fin.FORECAST_BURDENED_COST),
2665 sum(fin.FORECAST_LABOR_HRS)
2666 from
2667 (
2668 select /*+ ordered
2669 full(time) use_hash(time) parallel(time) swap_join_inputs(time)
2670 full(fin) use_hash(fin) parallel(fin) */
2671 fin.PROJECT_ID,
2672 fin.PROJECT_ORG_ID,
2673 fin.PROJECT_ORGANIZATION_ID,
2674 fin.PROJECT_TYPE_CLASS,
2675 case when grouping(time.CAL_YEAR_ID) = 0 and
2676 grouping(time.CAL_QTR_ID) = 0 and
2677 grouping(time.CAL_PERIOD_ID) = 0
2678 then time.CAL_PERIOD_ID
2679 when grouping(time.CAL_YEAR_ID) = 0 and
2680 grouping(time.CAL_QTR_ID) = 0 and
2681 grouping(time.CAL_PERIOD_ID) = 1
2682 then time.CAL_QTR_ID
2683 when grouping(time.CAL_YEAR_ID) = 0 and
2684 grouping(time.CAL_QTR_ID) = 1 and
2685 grouping(time.CAL_PERIOD_ID) = 1
2686 then time.CAL_YEAR_ID
2687 end TIME_ID,
2688 case when grouping(time.CAL_YEAR_ID) = 0 and
2689 grouping(time.CAL_QTR_ID) = 0 and
2690 grouping(time.CAL_PERIOD_ID) = 0
2691 then 32
2692 when grouping(time.CAL_YEAR_ID) = 0 and
2693 grouping(time.CAL_QTR_ID) = 0 and
2694 grouping(time.CAL_PERIOD_ID) = 1
2695 then 64
2696 when grouping(time.CAL_YEAR_ID) = 0 and
2697 grouping(time.CAL_QTR_ID) = 1 and
2698 grouping(time.CAL_PERIOD_ID) = 1
2699 then 128
2700 end PERIOD_TYPE_ID,
2701 'G' CALENDAR_TYPE,
2702 fin.CURR_RECORD_TYPE_ID,
2703 fin.CURRENCY_CODE,
2704 sum(fin.REVENUE) REVENUE,
2705 sum(fin.LABOR_REVENUE) LABOR_REVENUE,
2706 sum(fin.RAW_COST) RAW_COST,
2707 sum(fin.BURDENED_COST) BURDENED_COST,
2708 sum(fin.BILL_RAW_COST) BILL_RAW_COST,
2709 sum(fin.BILL_BURDENED_COST) BILL_BURDENED_COST,
2710 sum(fin.LABOR_RAW_COST) LABOR_RAW_COST,
2711 sum(fin.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
2712 sum(fin.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
2713 sum(fin.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
2714 sum(fin.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
2715 sum(fin.LABOR_HRS) LABOR_HRS,
2716 sum(fin.BILL_LABOR_HRS) BILL_LABOR_HRS,
2717 to_number(null) CURR_BGT_REVENUE,
2718 to_number(null) CURR_BGT_RAW_COST,
2719 to_number(null) CURR_BGT_BURDENED_COST,
2720 to_number(null) CURR_BGT_LABOR_HRS,
2721 to_number(null) ORIG_BGT_REVENUE,
2722 to_number(null) ORIG_BGT_RAW_COST,
2723 to_number(null) ORIG_BGT_BURDENED_COST,
2724 to_number(null) ORIG_BGT_LABOR_HRS,
2725 to_number(null) FORECAST_REVENUE,
2726 to_number(null) FORECAST_RAW_COST,
2727 to_number(null) FORECAST_BURDENED_COST,
2728 to_number(null) FORECAST_LABOR_HRS
2729 from
2730 FII_TIME_CAL_DAY_MV time,
2731 PJI_FM_AGGR_FIN3 fin
2732 where
2733 fin.WORKER_ID = p_worker_id and
2734 fin.PERIOD_TYPE_ID = 1 and
2735 fin.CALENDAR_TYPE = 'C' and
2736 to_date(to_char(fin.TIME_ID), 'J') = time.REPORT_DATE and
2737 fin.GL_CALENDAR_ID = time.CALENDAR_ID
2738 group by
2739 fin.PROJECT_ID,
2740 fin.PROJECT_ORG_ID,
2741 fin.PROJECT_ORGANIZATION_ID,
2742 fin.PROJECT_TYPE_CLASS,
2743 rollup (time.CAL_YEAR_ID,
2744 time.CAL_QTR_ID,
2745 time.CAL_PERIOD_ID),
2746 fin.CURR_RECORD_TYPE_ID,
2747 fin.CURRENCY_CODE
2748 having
2749 not (grouping(time.CAL_YEAR_ID) = 1 and
2750 grouping(time.CAL_QTR_ID) = 1 and
2751 grouping(time.CAL_PERIOD_ID) = 1)
2752 union all
2753 select /*+ ordered
2754 full(period) use_hash(period) swap_join_inputs(period)
2755 full(qtr) use_hash(qtr) swap_join_inputs(qtr)
2756 full(tmp1) use_hash(tmp1) parallel(tmp1) */ -- budget data
2757 tmp1.PROJECT_ID,
2758 tmp1.PROJECT_ORG_ID,
2762 grouping(period.CAL_QTR_ID) = 0 and
2759 tmp1.PROJECT_ORGANIZATION_ID,
2760 tmp1.PROJECT_TYPE_CLASS,
2761 case when grouping(qtr.CAL_YEAR_ID) = 0 and
2763 grouping(period.CAL_PERIOD_ID) = 0
2764 then period.CAL_PERIOD_ID
2765 when grouping(qtr.CAL_YEAR_ID) = 0 and
2766 grouping(period.CAL_QTR_ID) = 0 and
2767 grouping(period.CAL_PERIOD_ID) = 1
2768 then period.CAL_QTR_ID
2769 when grouping(qtr.CAL_YEAR_ID) = 0 and
2770 grouping(period.CAL_QTR_ID) = 1 and
2771 grouping(period.CAL_PERIOD_ID) = 1
2772 then qtr.CAL_YEAR_ID
2773 end TIME_ID,
2774 case when grouping(qtr.CAL_YEAR_ID) = 0 and
2775 grouping(period.CAL_QTR_ID) = 0 and
2776 grouping(period.CAL_PERIOD_ID) = 0
2777 then 32
2778 when grouping(qtr.CAL_YEAR_ID) = 0 and
2779 grouping(period.CAL_QTR_ID) = 0 and
2780 grouping(period.CAL_PERIOD_ID) = 1
2781 then 64
2782 when grouping(qtr.CAL_YEAR_ID) = 0 and
2783 grouping(period.CAL_QTR_ID) = 1 and
2784 grouping(period.CAL_PERIOD_ID) = 1
2785 then 128
2786 end PERIOD_TYPE_ID,
2787 'G' CALENDAR_TYPE,
2788 tmp1.CURR_RECORD_TYPE_ID,
2789 tmp1.CURRENCY_CODE,
2790 to_number(null) REVENUE,
2791 to_number(null) LABOR_REVENUE,
2792 to_number(null) RAW_COST,
2793 to_number(null) BURDENED_COST,
2794 to_number(null) BILL_RAW_COST,
2795 to_number(null) BILL_BURDENED_COST,
2796 to_number(null) LABOR_RAW_COST,
2797 to_number(null) LABOR_BURDENED_COST,
2798 to_number(null) BILL_LABOR_RAW_COST,
2799 to_number(null) BILL_LABOR_BURDENED_COST,
2800 to_number(null) REVENUE_WRITEOFF,
2801 to_number(null) LABOR_HRS,
2802 to_number(null) BILL_LABOR_HRS,
2803 sum(tmp1.CURR_BGT_REVENUE) CURR_BGT_REVENUE,
2804 sum(tmp1.CURR_BGT_RAW_COST) CURR_BGT_RAW_COST,
2805 sum(tmp1.CURR_BGT_BRDN_COST) CURR_BGT_BURDENED_COST,
2806 sum(tmp1.CURR_BGT_LABOR_HRS) CURR_BGT_LABOR_HRS,
2807 sum(tmp1.CURR_ORIG_BGT_REVENUE) ORIG_BGT_REVENUE,
2808 sum(tmp1.CURR_ORIG_BGT_RAW_COST) ORIG_BGT_RAW_COST,
2809 sum(tmp1.CURR_ORIG_BGT_BRDN_COST) ORIG_BGT_BURDENED_COST,
2810 sum(tmp1.CURR_ORIG_BGT_LABOR_HRS) ORIG_BGT_LABOR_HRS,
2811 sum(tmp1.CURR_FORECAST_REVENUE) FORECAST_REVENUE,
2812 sum(tmp1.CURR_FORECAST_RAW_COST) FORECAST_RAW_COST,
2813 sum(tmp1.CURR_FORECAST_BRDN_COST) FORECAST_BURDENED_COST,
2814 sum(tmp1.CURR_FORECAST_LABOR_HRS) FORECAST_LABOR_HRS
2815 from
2816 FII_TIME_CAL_PERIOD period,
2817 FII_TIME_CAL_QTR qtr,
2818 PJI_FM_AGGR_PLN tmp1
2819 where
2820 tmp1.CALENDAR_TYPE_CODE = 'GL' and
2821 tmp1.TIME_ID = period.CAL_PERIOD_ID and
2822 period.CAL_QTR_ID = qtr.CAL_QTR_ID
2823 group by
2824 tmp1.PROJECT_ID,
2825 tmp1.PROJECT_ORG_ID,
2826 tmp1.PROJECT_ORGANIZATION_ID,
2827 tmp1.PROJECT_TYPE_CLASS,
2828 rollup (qtr.CAL_YEAR_ID,
2829 period.CAL_QTR_ID,
2830 period.CAL_PERIOD_ID),
2831 tmp1.CURR_RECORD_TYPE_ID,
2832 tmp1.CURRENCY_CODE
2833 having
2834 not (grouping(qtr.CAL_YEAR_ID) = 1 and
2835 grouping(period.CAL_QTR_ID) = 1 and
2836 grouping(period.CAL_PERIOD_ID) = 1)
2837 union all
2838 select /*+ ordered full(map) parallel(map)
2839 index(fpp, PJI_FP_PROJ_F_N2) use_nl(fpp) */ -- budget reversals
2840 fpp.PROJECT_ID,
2841 fpp.PROJECT_ORG_ID,
2842 fpp.PROJECT_ORGANIZATION_ID,
2843 fpp.PROJECT_TYPE_CLASS,
2844 fpp.TIME_ID,
2845 fpp.PERIOD_TYPE_ID,
2846 fpp.CALENDAR_TYPE,
2847 fpp.CURR_RECORD_TYPE_ID,
2848 fpp.CURRENCY_CODE,
2849 to_number(null) REVENUE,
2850 to_number(null) LABOR_REVENUE,
2851 to_number(null) RAW_COST,
2852 to_number(null) BURDENED_COST,
2853 to_number(null) BILL_RAW_COST,
2854 to_number(null) BILL_BURDENED_COST,
2855 to_number(null) LABOR_RAW_COST,
2856 to_number(null) LABOR_BURDENED_COST,
2857 to_number(null) BILL_LABOR_RAW_COST,
2858 to_number(null) BILL_LABOR_BURDENED_COST,
2859 to_number(null) REVENUE_WRITEOFF,
2860 to_number(null) LABOR_HRS,
2861 to_number(null) BILL_LABOR_HRS,
2862 case when map.REVENUE_BUDGET_C_VERSION <>
2863 map.REVENUE_BUDGET_N_VERSION
2864 then -fpp.CURR_BGT_REVENUE
2868 map.COST_BUDGET_N_VERSION
2865 else to_number(null)
2866 end CURR_BGT_REVENUE,
2867 case when map.COST_BUDGET_C_VERSION <>
2869 then -fpp.CURR_BGT_RAW_COST
2870 else to_number(null)
2871 end CURR_BGT_RAW_COST,
2872 case when map.COST_BUDGET_C_VERSION <>
2873 map.COST_BUDGET_N_VERSION
2874 then -fpp.CURR_BGT_BURDENED_COST
2875 else to_number(null)
2876 end CURR_BGT_BURDENED_COST,
2877 case when map.COST_BUDGET_C_VERSION <>
2878 map.COST_BUDGET_N_VERSION
2879 then -fpp.CURR_BGT_LABOR_HRS
2880 else to_number(null)
2881 end CURR_BGT_LABOR_HRS,
2882 case when map.REVENUE_BUDGET_CO_VERSION <>
2883 map.REVENUE_BUDGET_NO_VERSION
2884 then -fpp.ORIG_BGT_REVENUE
2885 else to_number(null)
2886 end ORIG_BGT_REVENUE,
2887 case when map.COST_BUDGET_CO_VERSION <>
2888 map.COST_BUDGET_NO_VERSION
2889 then -fpp.ORIG_BGT_RAW_COST
2890 else to_number(null)
2891 end ORIG_BGT_RAW_COST,
2892 case when map.COST_BUDGET_CO_VERSION <>
2893 map.COST_BUDGET_NO_VERSION
2894 then -fpp.ORIG_BGT_BURDENED_COST
2895 else to_number(null)
2896 end ORIG_BGT_BURDENED_COST,
2897 case when map.COST_BUDGET_CO_VERSION <>
2898 map.COST_BUDGET_NO_VERSION
2899 then -fpp.ORIG_BGT_LABOR_HRS
2900 else to_number(null)
2901 end ORIG_BGT_LABOR_HRS,
2902 case when map.REVENUE_FORECAST_C_VERSION <>
2903 map.REVENUE_FORECAST_N_VERSION
2904 then -fpp.FORECAST_REVENUE
2905 else to_number(null)
2906 end FORECAST_REVENUE,
2907 case when map.COST_FORECAST_C_VERSION <>
2908 map.COST_FORECAST_N_VERSION
2909 then -fpp.FORECAST_RAW_COST
2910 else to_number(null)
2911 end FORECAST_RAW_COST,
2912 case when map.COST_FORECAST_C_VERSION <>
2913 map.COST_FORECAST_N_VERSION
2914 then -fpp.FORECAST_BURDENED_COST
2915 else to_number(null)
2916 end FORECAST_BURDENED_COST,
2917 case when map.COST_FORECAST_C_VERSION <>
2918 map.COST_FORECAST_N_VERSION
2919 then -fpp.FORECAST_LABOR_HRS
2920 else to_number(null)
2921 end FORECAST_LABOR_HRS
2922 from
2923 PJI_PJI_PROJ_BATCH_MAP map,
2924 PJI_FP_PROJ_F fpp
2925 where
2926 l_extraction_type <> 'FULL' and
2927 map.WORKER_ID = p_worker_id and
2928 (map.REVENUE_BUDGET_C_VERSION <> map.REVENUE_BUDGET_N_VERSION or
2929 map.COST_BUDGET_C_VERSION <> map.COST_BUDGET_N_VERSION or
2930 map.REVENUE_BUDGET_CO_VERSION <> map.REVENUE_BUDGET_NO_VERSION or
2931 map.COST_BUDGET_CO_VERSION <> map.COST_BUDGET_NO_VERSION or
2932 map.REVENUE_FORECAST_C_VERSION <> map.REVENUE_FORECAST_N_VERSION or
2933 map.COST_FORECAST_C_VERSION <> map.COST_FORECAST_N_VERSION) and
2934 map.PROJECT_ID = fpp.PROJECT_ID and
2935 fpp.CALENDAR_TYPE = 'G' and
2936 fpp.PERIOD_TYPE_ID <> 1
2937 ) fin
2938 where
2939 not (fin.CURR_RECORD_TYPE_ID in (8, 256) and
2940 fin.PERIOD_TYPE_ID <> 32)
2941 group by
2942 fin.PROJECT_ID,
2943 fin.PROJECT_ORG_ID,
2944 fin.PROJECT_ORGANIZATION_ID,
2945 fin.PROJECT_TYPE_CLASS,
2946 fin.TIME_ID,
2947 fin.PERIOD_TYPE_ID,
2948 fin.CALENDAR_TYPE,
2949 decode(fin.PERIOD_TYPE_ID,
2950 32, fin.CURR_RECORD_TYPE_ID,
2951 bitand(fin.CURR_RECORD_TYPE_ID,
2952 247)),
2953 fin.CURRENCY_CODE;
2954
2955 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPP_CAL_GL(p_worker_id);');
2959 end EXPAND_FPP_CAL_GL;
2956
2957 commit;
2958
2960
2961
2962 -- -----------------------------------------------------
2963 -- procedure EXPAND_FPP_CAL_WK
2964 -- -----------------------------------------------------
2965 procedure EXPAND_FPP_CAL_WK (p_worker_id in number) is
2966
2967 l_process varchar2(30);
2968 l_extraction_type varchar2(30);
2969 l_schema varchar2(30);
2970
2971 begin
2972
2973 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2974
2975 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPP_CAL_WK(p_worker_id);')) then
2976 return;
2977 end if;
2978
2979 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2980 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
2981
2982 insert /*+ append parallel(fin3_i) */ into PJI_FM_AGGR_FIN3 fin3_i -- in EXPAND_FPP_CAL_WK
2983 (
2984 WORKER_ID,
2985 PROJECT_ID,
2986 PROJECT_ORG_ID,
2987 PROJECT_ORGANIZATION_ID,
2988 PROJECT_TYPE_CLASS,
2989 TIME_ID,
2990 PERIOD_TYPE_ID,
2991 CALENDAR_TYPE,
2992 CURR_RECORD_TYPE_ID,
2993 CURRENCY_CODE,
2994 REVENUE,
2995 LABOR_REVENUE,
2996 RAW_COST,
2997 BURDENED_COST,
2998 BILL_RAW_COST,
2999 BILL_BURDENED_COST,
3000 LABOR_RAW_COST,
3001 LABOR_BURDENED_COST,
3002 BILL_LABOR_RAW_COST,
3003 BILL_LABOR_BURDENED_COST,
3004 REVENUE_WRITEOFF,
3005 LABOR_HRS,
3006 BILL_LABOR_HRS,
3007 CURR_BGT_REVENUE,
3008 CURR_BGT_RAW_COST,
3009 CURR_BGT_BURDENED_COST,
3010 CURR_BGT_LABOR_HRS,
3011 ORIG_BGT_REVENUE,
3012 ORIG_BGT_RAW_COST,
3013 ORIG_BGT_BURDENED_COST,
3014 ORIG_BGT_LABOR_HRS,
3015 FORECAST_REVENUE,
3016 FORECAST_RAW_COST,
3017 FORECAST_BURDENED_COST,
3018 FORECAST_LABOR_HRS
3019 )
3020 select
3021 p_worker_id,
3022 PROJECT_ID,
3023 PROJECT_ORG_ID,
3024 PROJECT_ORGANIZATION_ID,
3025 PROJECT_TYPE_CLASS,
3026 TIME_ID,
3027 PERIOD_TYPE_ID,
3028 CALENDAR_TYPE,
3029 CURR_RECORD_TYPE_ID,
3030 CURRENCY_CODE,
3031 sum(REVENUE),
3032 sum(LABOR_REVENUE),
3033 sum(RAW_COST),
3034 sum(BURDENED_COST),
3035 sum(BILL_RAW_COST),
3036 sum(BILL_BURDENED_COST),
3037 sum(LABOR_RAW_COST),
3038 sum(LABOR_BURDENED_COST),
3039 sum(BILL_LABOR_RAW_COST),
3040 sum(BILL_LABOR_BURDENED_COST),
3041 sum(REVENUE_WRITEOFF),
3042 sum(LABOR_HRS),
3043 sum(BILL_LABOR_HRS),
3044 sum(CURR_BGT_REVENUE),
3045 sum(CURR_BGT_RAW_COST),
3046 sum(CURR_BGT_BURDENED_COST),
3047 sum(CURR_BGT_LABOR_HRS),
3048 sum(ORIG_BGT_REVENUE),
3049 sum(ORIG_BGT_RAW_COST),
3050 sum(ORIG_BGT_BURDENED_COST),
3051 sum(ORIG_BGT_LABOR_HRS),
3052 sum(FORECAST_REVENUE),
3053 sum(FORECAST_RAW_COST),
3054 sum(FORECAST_BURDENED_COST),
3055 sum(FORECAST_LABOR_HRS)
3056 from
3057 (
3058 select /*+ ordered
3059 full(time) use_hash(time) swap_join_inputs(time)
3060 full(fin) use_hash(fin) parallel(fin) */
3061 fin.PROJECT_ID,
3062 fin.PROJECT_ORG_ID,
3063 fin.PROJECT_ORGANIZATION_ID,
3064 fin.PROJECT_TYPE_CLASS,
3065 time.WEEK_ID TIME_ID,
3066 16 PERIOD_TYPE_ID,
3067 'E' CALENDAR_TYPE,
3068 bitand(fin.CURR_RECORD_TYPE_ID, 247) CURR_RECORD_TYPE_ID,
3069 fin.CURRENCY_CODE,
3070 sum(fin.REVENUE) REVENUE,
3071 sum(fin.LABOR_REVENUE) LABOR_REVENUE,
3072 sum(fin.RAW_COST) RAW_COST,
3073 sum(fin.BURDENED_COST) BURDENED_COST,
3074 sum(fin.BILL_RAW_COST) BILL_RAW_COST,
3075 sum(fin.BILL_BURDENED_COST) BILL_BURDENED_COST,
3076 sum(fin.LABOR_RAW_COST) LABOR_RAW_COST,
3077 sum(fin.LABOR_BURDENED_COST) LABOR_BURDENED_COST,
3078 sum(fin.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
3079 sum(fin.BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
3080 sum(fin.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
3081 sum(fin.LABOR_HRS) LABOR_HRS,
3082 sum(fin.BILL_LABOR_HRS) BILL_LABOR_HRS,
3083 to_number(null) CURR_BGT_REVENUE,
3084 to_number(null) CURR_BGT_RAW_COST,
3085 to_number(null) CURR_BGT_BURDENED_COST,
3086 to_number(null) CURR_BGT_LABOR_HRS,
3087 to_number(null) ORIG_BGT_REVENUE,
3088 to_number(null) ORIG_BGT_RAW_COST,
3089 to_number(null) ORIG_BGT_BURDENED_COST,
3090 to_number(null) ORIG_BGT_LABOR_HRS,
3091 to_number(null) FORECAST_REVENUE,
3095 from
3092 to_number(null) FORECAST_RAW_COST,
3093 to_number(null) FORECAST_BURDENED_COST,
3094 to_number(null) FORECAST_LABOR_HRS
3096 FII_TIME_DAY time,
3097 PJI_FM_AGGR_FIN3 fin
3098 where
3099 fin.WORKER_ID = p_worker_id and
3100 fin.PERIOD_TYPE_ID = 1 and
3101 fin.CALENDAR_TYPE = 'C' and
3102 fin.CURR_RECORD_TYPE_ID not in (8, 256) and
3103 fin.TIME_ID = time.REPORT_DATE_JULIAN
3104 group by
3105 fin.PROJECT_ID,
3106 fin.PROJECT_ORG_ID,
3107 fin.PROJECT_ORGANIZATION_ID,
3108 fin.PROJECT_TYPE_CLASS,
3109 time.WEEK_ID,
3110 bitand(fin.CURR_RECORD_TYPE_ID, 247),
3111 fin.CURRENCY_CODE
3112 union all
3113 select /*+ parallel(tmp1) */ -- budget data
3114 tmp1.PROJECT_ID,
3115 tmp1.PROJECT_ORG_ID,
3116 tmp1.PROJECT_ORGANIZATION_ID,
3117 tmp1.PROJECT_TYPE_CLASS,
3118 tmp1.TIME_ID,
3119 16 PERIOD_TYPE_ID,
3120 'E' CALENDAR_TYPE,
3121 bitand(tmp1.CURR_RECORD_TYPE_ID, 247) CURR_RECORD_TYPE_ID,
3122 tmp1.CURRENCY_CODE,
3123 to_number(null) REVENUE,
3124 to_number(null) LABOR_REVENUE,
3125 to_number(null) RAW_COST,
3126 to_number(null) BURDENED_COST,
3127 to_number(null) BILL_RAW_COST,
3128 to_number(null) BILL_BURDENED_COST,
3129 to_number(null) LABOR_RAW_COST,
3130 to_number(null) LABOR_BURDENED_COST,
3131 to_number(null) BILL_LABOR_RAW_COST,
3132 to_number(null) BILL_LABOR_BURDENED_COST,
3133 to_number(null) REVENUE_WRITEOFF,
3134 to_number(null) LABOR_HRS,
3135 to_number(null) BILL_LABOR_HRS,
3136 sum(tmp1.CURR_BGT_REVENUE) CURR_BGT_REVENUE,
3137 sum(tmp1.CURR_BGT_RAW_COST) CURR_BGT_RAW_COST,
3138 sum(tmp1.CURR_BGT_BRDN_COST) CURR_BGT_BURDENED_COST,
3139 sum(tmp1.CURR_BGT_LABOR_HRS) CURR_BGT_LABOR_HRS,
3140 sum(tmp1.CURR_ORIG_BGT_REVENUE) ORIG_BGT_REVENUE,
3141 sum(tmp1.CURR_ORIG_BGT_RAW_COST) ORIG_BGT_RAW_COST,
3142 sum(tmp1.CURR_ORIG_BGT_BRDN_COST) ORIG_BGT_BURDENED_COST,
3143 sum(tmp1.CURR_ORIG_BGT_LABOR_HRS) ORIG_BGT_LABOR_HRS,
3144 sum(tmp1.CURR_FORECAST_REVENUE) FORECAST_REVENUE,
3145 sum(tmp1.CURR_FORECAST_RAW_COST) FORECAST_RAW_COST,
3146 sum(tmp1.CURR_FORECAST_BRDN_COST) FORECAST_BURDENED_COST,
3147 sum(tmp1.CURR_FORECAST_LABOR_HRS) FORECAST_LABOR_HRS
3148 from
3149 PJI_FM_AGGR_PLN tmp1
3150 where
3151 tmp1.CALENDAR_TYPE_CODE = 'ENTW' and
3152 tmp1.CURR_RECORD_TYPE_ID not in (8, 256)
3153 group by
3154 tmp1.PROJECT_ID,
3155 tmp1.PROJECT_ORG_ID,
3156 tmp1.PROJECT_ORGANIZATION_ID,
3157 tmp1.PROJECT_TYPE_CLASS,
3158 tmp1.TIME_ID,
3159 bitand(tmp1.CURR_RECORD_TYPE_ID, 247),
3160 tmp1.CURRENCY_CODE
3161 union all
3162 select /*+ ordered full(map) parallel(map)
3163 index(fpp, PJI_FP_PROJ_F_N2) use_nl(fpp) */ -- budget reversals
3164 fpp.PROJECT_ID,
3165 fpp.PROJECT_ORG_ID,
3166 fpp.PROJECT_ORGANIZATION_ID,
3170 fpp.CALENDAR_TYPE,
3167 fpp.PROJECT_TYPE_CLASS,
3168 fpp.TIME_ID,
3169 fpp.PERIOD_TYPE_ID,
3171 fpp.CURR_RECORD_TYPE_ID,
3172 fpp.CURRENCY_CODE,
3173 to_number(null) REVENUE,
3174 to_number(null) LABOR_REVENUE,
3175 to_number(null) RAW_COST,
3176 to_number(null) BURDENED_COST,
3177 to_number(null) BILL_RAW_COST,
3178 to_number(null) BILL_BURDENED_COST,
3179 to_number(null) LABOR_RAW_COST,
3180 to_number(null) LABOR_BURDENED_COST,
3181 to_number(null) BILL_LABOR_RAW_COST,
3182 to_number(null) BILL_LABOR_BURDENED_COST,
3183 to_number(null) REVENUE_WRITEOFF,
3184 to_number(null) LABOR_HRS,
3185 to_number(null) BILL_LABOR_HRS,
3186 case when map.REVENUE_BUDGET_C_VERSION <>
3187 map.REVENUE_BUDGET_N_VERSION
3188 then -fpp.CURR_BGT_REVENUE
3189 else to_number(null)
3190 end CURR_BGT_REVENUE,
3191 case when map.COST_BUDGET_C_VERSION <>
3192 map.COST_BUDGET_N_VERSION
3193 then -fpp.CURR_BGT_RAW_COST
3194 else to_number(null)
3195 end CURR_BGT_RAW_COST,
3196 case when map.COST_BUDGET_C_VERSION <>
3197 map.COST_BUDGET_N_VERSION
3198 then -fpp.CURR_BGT_BURDENED_COST
3199 else to_number(null)
3200 end CURR_BGT_BURDENED_COST,
3201 case when map.COST_BUDGET_C_VERSION <>
3202 map.COST_BUDGET_N_VERSION
3203 then -fpp.CURR_BGT_LABOR_HRS
3204 else to_number(null)
3205 end CURR_BGT_LABOR_HRS,
3206 case when map.REVENUE_BUDGET_CO_VERSION <>
3207 map.REVENUE_BUDGET_NO_VERSION
3208 then -fpp.ORIG_BGT_REVENUE
3209 else to_number(null)
3210 end ORIG_BGT_REVENUE,
3211 case when map.COST_BUDGET_CO_VERSION <>
3212 map.COST_BUDGET_NO_VERSION
3213 then -fpp.ORIG_BGT_RAW_COST
3214 else to_number(null)
3215 end ORIG_BGT_RAW_COST,
3216 case when map.COST_BUDGET_CO_VERSION <>
3217 map.COST_BUDGET_NO_VERSION
3218 then -fpp.ORIG_BGT_BURDENED_COST
3219 else to_number(null)
3220 end ORIG_BGT_BURDENED_COST,
3221 case when map.COST_BUDGET_CO_VERSION <>
3222 map.COST_BUDGET_NO_VERSION
3223 then -fpp.ORIG_BGT_LABOR_HRS
3224 else to_number(null)
3225 end ORIG_BGT_LABOR_HRS,
3226 case when map.REVENUE_FORECAST_C_VERSION <>
3227 map.REVENUE_FORECAST_N_VERSION
3228 then -fpp.FORECAST_REVENUE
3229 else to_number(null)
3230 end FORECAST_REVENUE,
3231 case when map.COST_FORECAST_C_VERSION <>
3232 map.COST_FORECAST_N_VERSION
3233 then -fpp.FORECAST_RAW_COST
3234 else to_number(null)
3235 end FORECAST_RAW_COST,
3236 case when map.COST_FORECAST_C_VERSION <>
3237 map.COST_FORECAST_N_VERSION
3238 then -fpp.FORECAST_BURDENED_COST
3239 else to_number(null)
3240 end FORECAST_BURDENED_COST,
3241 case when map.COST_FORECAST_C_VERSION <>
3242 map.COST_FORECAST_N_VERSION
3243 then -fpp.FORECAST_LABOR_HRS
3244 else to_number(null)
3245 end FORECAST_LABOR_HRS
3246 from
3247 PJI_PJI_PROJ_BATCH_MAP map,
3248 PJI_FP_PROJ_F fpp
3249 where
3250 l_extraction_type <> 'FULL' and
3251 map.WORKER_ID = p_worker_id and
3252 (map.REVENUE_BUDGET_C_VERSION <> map.REVENUE_BUDGET_N_VERSION or
3253 map.COST_BUDGET_C_VERSION <> map.COST_BUDGET_N_VERSION or
3254 map.REVENUE_BUDGET_CO_VERSION <> map.REVENUE_BUDGET_NO_VERSION or
3255 map.COST_BUDGET_CO_VERSION <> map.COST_BUDGET_NO_VERSION or
3256 map.REVENUE_FORECAST_C_VERSION <> map.REVENUE_FORECAST_N_VERSION or
3257 map.COST_FORECAST_C_VERSION <> map.COST_FORECAST_N_VERSION) and
3258 map.PROJECT_ID = fpp.PROJECT_ID and
3259 fpp.CALENDAR_TYPE = 'E' and
3260 fpp.PERIOD_TYPE_ID = 16
3261 ) fin
3262 group by
3263 PROJECT_ID,
3264 PROJECT_ORG_ID,
3265 PROJECT_ORGANIZATION_ID,
3266 PROJECT_TYPE_CLASS,
3267 TIME_ID,
3268 PERIOD_TYPE_ID,
3269 CALENDAR_TYPE,
3270 CURR_RECORD_TYPE_ID,
3271 CURRENCY_CODE;
3272
3273 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPP_CAL_WK(p_worker_id);');
3274
3275 -- truncate intermediate tables no longer required
3276 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
3277 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_AGGR_PLN' , 'NORMAL',null);
3278
3279 commit;
3280
3284 -- -----------------------------------------------------
3281 end EXPAND_FPP_CAL_WK;
3282
3283
3285 -- procedure MERGE_FIN_INTO_FPW
3286 -- -----------------------------------------------------
3287 procedure MERGE_FIN_INTO_FPW (p_worker_id in number) is
3288
3289 l_process varchar2(30);
3290 l_extraction_type varchar2(30);
3291 l_last_update_date date;
3292 l_last_updated_by number;
3293 l_creation_date date;
3294 l_created_by number;
3295 l_last_update_login number;
3296 l_schema varchar2(30);
3297
3298 begin
3299
3300 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
3301
3302 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
3303 (
3304 l_process,
3305 'PJI_FM_SUM_ROLLUP_FIN.MERGE_FIN_INTO_FPW(p_worker_id);'
3306 )) then
3307 return;
3308 end if;
3309
3310 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3311 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
3312
3313 l_last_update_date := sysdate;
3317 l_last_update_login := FND_GLOBAL.LOGIN_ID;
3314 l_last_updated_by := FND_GLOBAL.USER_ID;
3315 l_creation_date := sysdate;
3316 l_created_by := FND_GLOBAL.USER_ID;
3318
3319 if (l_extraction_type = 'FULL') then
3320
3321 insert /*+ append parallel(fpw) */ into PJI_FP_PROJ_ET_WT_F fpw
3322 (
3323 PROJECT_ORG_ID,
3324 PROJECT_ORGANIZATION_ID,
3325 TIME_ID,
3326 PROJECT_ID,
3327 EXP_EVT_TYPE_ID,
3328 WORK_TYPE_ID,
3329 PERIOD_TYPE_ID,
3330 CALENDAR_TYPE,
3331 CURR_RECORD_TYPE_ID,
3332 CURRENCY_CODE,
3333 PROJECT_TYPE_CLASS,
3334 RAW_COST,
3335 BURDENED_COST,
3336 BILL_RAW_COST,
3337 BILL_BURDENED_COST,
3338 CAPITALIZABLE_RAW_COST,
3339 CAPITALIZABLE_BRDN_COST,
3340 LABOR_RAW_COST,
3341 LABOR_BURDENED_COST,
3342 BILL_LABOR_RAW_COST,
3343 BILL_LABOR_BURDENED_COST,
3344 LABOR_HRS,
3345 BILL_LABOR_HRS,
3346 QUANTITY,
3347 BILL_QUANTITY,
3348 LAST_UPDATE_DATE,
3349 LAST_UPDATED_BY,
3350 CREATION_DATE,
3351 CREATED_BY,
3352 LAST_UPDATE_LOGIN
3353 )
3354 select /*+ full(fin4) parallel(fin4) */
3355 fin4.PROJECT_ORG_ID,
3356 fin4.PROJECT_ORGANIZATION_ID,
3357 fin4.TIME_ID,
3358 fin4.PROJECT_ID,
3359 fin4.EXP_EVT_TYPE_ID,
3360 fin4.WORK_TYPE_ID,
3361 fin4.PERIOD_TYPE_ID,
3362 fin4.CALENDAR_TYPE,
3363 fin4.CURR_RECORD_TYPE_ID,
3364 fin4.CURRENCY_CODE,
3365 fin4.PROJECT_TYPE_CLASS,
3366 fin4.RAW_COST,
3367 fin4.BURDENED_COST,
3368 decode(fin4.PROJECT_TYPE_CLASS,
3369 'B', fin4.BILL_RAW_COST,
3370 to_number(null)) BILL_RAW_COST,
3371 decode(fin4.Project_Type_Class,
3372 'B', fin4.BILL_BURDENED_COST,
3373 to_number(null)) BILL_BURDENED_COST,
3374 decode(fin4.PROJECT_TYPE_CLASS,
3375 'C', fin4.BILL_RAW_COST,
3376 to_number(null)) CAPITALIZABLE_RAW_COST,
3377 decode(fin4.PROJECT_TYPE_CLASS,
3378 'C', fin4.BILL_BURDENED_COST,
3379 to_number(null)) CAPITALIZABLE_BRDN_COST,
3380 fin4.LABOR_RAW_COST,
3381 fin4.LABOR_BURDENED_COST,
3382 fin4.BILL_LABOR_RAW_COST,
3383 fin4.BILL_LABOR_BURDENED_COST,
3384 fin4.LABOR_HRS,
3385 fin4.BILL_LABOR_HRS,
3386 fin4.QUANTITY,
3387 fin4.BILL_QUANTITY,
3388 l_last_update_date,
3389 l_last_updated_by,
3390 l_creation_date,
3391 l_created_by,
3392 l_last_update_login
3393 from
3394 PJI_FM_AGGR_FIN4 fin4
3395 where
3396 fin4.WORKER_ID = p_worker_id and
3397 (nvl(fin4.RAW_COST , 0) <> 0 or
3398 nvl(fin4.BURDENED_COST , 0) <> 0 or
3402 nvl(fin4.LABOR_BURDENED_COST , 0) <> 0 or
3399 nvl(fin4.BILL_RAW_COST , 0) <> 0 or
3400 nvl(fin4.BILL_BURDENED_COST , 0) <> 0 or
3401 nvl(fin4.LABOR_RAW_COST , 0) <> 0 or
3403 nvl(fin4.BILL_LABOR_RAW_COST , 0) <> 0 or
3404 nvl(fin4.BILL_LABOR_BURDENED_COST , 0) <> 0 or
3405 nvl(fin4.LABOR_HRS , 0) <> 0 or
3406 nvl(fin4.BILL_LABOR_HRS , 0) <> 0 or
3407 nvl(fin4.QUANTITY , 0) <> 0 or
3408 nvl(fin4.BILL_QUANTITY , 0) <> 0);
3409
3410 else -- not initial data load
3411
3412 merge /*+ parallel(fpw) */ into PJI_FP_PROJ_ET_WT_F fpw
3413 using
3414 (
3415 select
3416 PROJECT_ORG_ID,
3417 PROJECT_ORGANIZATION_ID,
3418 TIME_ID,
3419 PROJECT_ID,
3420 EXP_EVT_TYPE_ID,
3421 WORK_TYPE_ID,
3422 PERIOD_TYPE_ID,
3423 CALENDAR_TYPE,
3424 CURR_RECORD_TYPE_ID,
3425 CURRENCY_CODE,
3426 PROJECT_TYPE_CLASS,
3427 RAW_COST,
3428 BURDENED_COST,
3429 BILL_RAW_COST,
3430 BILL_BURDENED_COST,
3431 CAPITALIZABLE_RAW_COST,
3432 CAPITALIZABLE_BRDN_COST,
3433 LABOR_RAW_COST,
3434 LABOR_BURDENED_COST,
3435 BILL_LABOR_RAW_COST,
3436 BILL_LABOR_BURDENED_COST,
3437 LABOR_HRS,
3438 BILL_LABOR_HRS,
3439 QUANTITY,
3440 BILL_QUANTITY,
3441 l_last_update_date LAST_UPDATE_DATE,
3442 l_last_updated_by LAST_UPDATED_BY,
3443 l_creation_date CREATION_DATE,
3444 l_created_by CREATED_BY,
3445 l_last_update_login LAST_UPDATE_LOGIN
3446 from
3447 (
3448 select
3449 PROJECT_ORG_ID,
3450 PROJECT_ORGANIZATION_ID,
3451 TIME_ID,
3452 PROJECT_ID,
3453 EXP_EVT_TYPE_ID,
3454 WORK_TYPE_ID,
3455 PERIOD_TYPE_ID,
3456 CALENDAR_TYPE,
3457 CURR_RECORD_TYPE_ID,
3458 CURRENCY_CODE,
3459 PROJECT_TYPE_CLASS,
3460 sum(RAW_COST) RAW_COST,
3461 sum(BURDENED_COST) BURDENED_COST,
3462 sum(BILL_RAW_COST) BILL_RAW_COST,
3463 sum(BILL_BURDENED_COST) BILL_BURDENED_COST,
3464 sum(CAPITALIZABLE_RAW_COST) CAPITALIZABLE_RAW_COST,
3465 sum(CAPITALIZABLE_BRDN_COST) CAPITALIZABLE_BRDN_COST,
3466 sum(LABOR_RAW_COST) LABOR_RAW_COST,
3467 sum(LABOR_BURDENED_COST) LABOR_BURDENED_COST,
3468 sum(BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
3469 sum(BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
3470 sum(LABOR_HRS) LABOR_HRS,
3471 sum(BILL_LABOR_HRS) BILL_LABOR_HRS,
3472 sum(QUANTITY) QUANTITY,
3473 sum(BILL_QUANTITY) BILL_QUANTITY
3474 from
3475 (
3476 select /*+ full(fin4) parallel(fin4) */
3477 fin4.PROJECT_ORG_ID,
3478 fin4.PROJECT_ORGANIZATION_ID,
3479 fin4.TIME_ID,
3480 fin4.PROJECT_ID,
3481 fin4.EXP_EVT_TYPE_ID,
3482 fin4.WORK_TYPE_ID,
3483 fin4.PERIOD_TYPE_ID,
3484 fin4.CALENDAR_TYPE,
3485 fin4.CURR_RECORD_TYPE_ID,
3486 fin4.CURRENCY_CODE,
3487 fin4.PROJECT_TYPE_CLASS,
3488 fin4.RAW_COST,
3489 fin4.BURDENED_COST,
3490 decode(fin4.PROJECT_TYPE_CLASS,
3491 'B', fin4.BILL_RAW_COST,
3492 to_number(null)) BILL_RAW_COST,
3493 decode(fin4.Project_Type_Class,
3494 'B', fin4.BILL_BURDENED_COST,
3495 to_number(null)) BILL_BURDENED_COST,
3496 decode(fin4.PROJECT_TYPE_CLASS,
3497 'C', fin4.BILL_RAW_COST,
3498 to_number(null)) CAPITALIZABLE_RAW_COST,
3499 decode(fin4.PROJECT_TYPE_CLASS,
3500 'C', fin4.BILL_BURDENED_COST,
3501 to_number(null)) CAPITALIZABLE_BRDN_COST,
3502 fin4.LABOR_RAW_COST,
3503 fin4.LABOR_BURDENED_COST,
3504 fin4.BILL_LABOR_RAW_COST,
3505 fin4.BILL_LABOR_BURDENED_COST,
3506 fin4.LABOR_HRS,
3507 fin4.BILL_LABOR_HRS,
3508 fin4.QUANTITY,
3509 fin4.BILL_QUANTITY
3510 from
3511 PJI_FM_AGGR_FIN4 fin4
3512 where
3513 fin4.WORKER_ID = p_worker_id
3514 union all -- partial refresh
3515 select /*+ ordered full(map) parallel(map)
3516 index(fpw, PJI_FP_PROJ_ET_WT_F_N2) use_nl(fpw)*/
3517 fpw.PROJECT_ORG_ID,
3518 fpw.PROJECT_ORGANIZATION_ID,
3519 fpw.TIME_ID,
3520 fpw.PROJECT_ID,
3521 fpw.EXP_EVT_TYPE_ID,
3522 fpw.WORK_TYPE_ID,
3526 fpw.CURRENCY_CODE,
3523 fpw.PERIOD_TYPE_ID,
3524 fpw.CALENDAR_TYPE,
3525 fpw.CURR_RECORD_TYPE_ID,
3527 fpw.PROJECT_TYPE_CLASS,
3528 -fpw.RAW_COST,
3529 -fpw.BURDENED_COST,
3530 -fpw.BILL_RAW_COST,
3531 -fpw.BILL_BURDENED_COST,
3532 -fpw.CAPITALIZABLE_RAW_COST,
3533 -fpw.CAPITALIZABLE_BRDN_COST,
3534 -fpw.LABOR_RAW_COST,
3535 -fpw.LABOR_BURDENED_COST,
3536 -fpw.BILL_LABOR_RAW_COST,
3537 -fpw.BILL_LABOR_BURDENED_COST,
3538 -fpw.LABOR_HRS,
3539 -fpw.BILL_LABOR_HRS,
3540 -fpw.QUANTITY,
3541 -fpw.BILL_QUANTITY
3542 from
3543 PJI_PJI_PROJ_BATCH_MAP map,
3544 PJI_FP_PROJ_ET_WT_F fpw
3545 where
3546 l_extraction_type = 'PARTIAL' and
3547 map.WORKER_ID = p_worker_id and
3548 map.EXTRACTION_TYPE = 'P' and
3549 fpw.PROJECT_ID = map.PROJECT_ID
3550 )
3551 group by
3552 PROJECT_ORG_ID,
3553 PROJECT_ORGANIZATION_ID,
3554 TIME_ID,
3555 PROJECT_ID,
3556 EXP_EVT_TYPE_ID,
3557 WORK_TYPE_ID,
3558 PERIOD_TYPE_ID,
3559 CALENDAR_TYPE,
3560 CURR_RECORD_TYPE_ID,
3561 CURRENCY_CODE,
3562 PROJECT_TYPE_CLASS
3563 )
3564 where
3565 nvl(RAW_COST , 0) <> 0 or
3566 nvl(BURDENED_COST , 0) <> 0 or
3567 nvl(BILL_RAW_COST , 0) <> 0 or
3568 nvl(BILL_BURDENED_COST , 0) <> 0 or
3569 nvl(CAPITALIZABLE_RAW_COST , 0) <> 0 or
3570 nvl(CAPITALIZABLE_BRDN_COST , 0) <> 0 or
3571 nvl(LABOR_RAW_COST , 0) <> 0 or
3572 nvl(LABOR_BURDENED_COST , 0) <> 0 or
3573 nvl(BILL_LABOR_RAW_COST , 0) <> 0 or
3574 nvl(BILL_LABOR_BURDENED_COST, 0) <> 0 or
3575 nvl(LABOR_HRS , 0) <> 0 or
3576 nvl(BILL_LABOR_HRS , 0) <> 0 or
3577 nvl(QUANTITY , 0) <> 0 or
3578 nvl(BILL_QUANTITY , 0) <> 0
3579 ) fin
3580 on
3581 (
3582 fin.PROJECT_ORG_ID = fpw.PROJECT_ORG_ID and
3583 fin.PROJECT_ORGANIZATION_ID = fpw.PROJECT_ORGANIZATION_ID and
3584 fin.TIME_ID = fpw.TIME_ID and
3585 fin.PROJECT_ID = fpw.PROJECT_ID and
3586 fin.EXP_EVT_TYPE_ID = fpw.EXP_EVT_TYPE_ID and
3587 fin.WORK_TYPE_ID = fpw.WORK_TYPE_ID and
3588 fin.PERIOD_TYPE_ID = fpw.PERIOD_TYPE_ID and
3589 fin.CALENDAR_TYPE = fpw.CALENDAR_TYPE and
3590 fin.CURR_RECORD_TYPE_ID = fpw.CURR_RECORD_TYPE_ID and
3591 fin.CURRENCY_CODE = fpw.CURRENCY_CODE and
3592 fin.PROJECT_TYPE_CLASS = fpw.PROJECT_TYPE_CLASS
3593 )
3594 when matched then update set
3595 fpw.RAW_COST = case when fpw.RAW_COST is null and
3596 fin.RAW_COST is null
3597 then to_number(null)
3598 else nvl(fpw.RAW_COST, 0) +
3599 nvl(fin.RAW_COST, 0)
3600 end,
3601 fpw.BURDENED_COST = case when fpw.BURDENED_COST is null and
3602 fin.BURDENED_COST is null
3603 then to_number(null)
3604 else nvl(fpw.BURDENED_COST, 0) +
3605 nvl(fin.BURDENED_COST, 0)
3606 end,
3607 fpw.BILL_RAW_COST = case when fpw.BILL_RAW_COST is null and
3608 fin.BILL_RAW_COST is null
3609 then to_number(null)
3610 else nvl(fpw.BILL_RAW_COST, 0) +
3611 nvl(fin.BILL_RAW_COST, 0)
3612 end,
3613 fpw.BILL_BURDENED_COST
3614 = case when fpw.BILL_BURDENED_COST is null and
3615 fin.BILL_BURDENED_COST is null
3616 then to_number(null)
3617 else nvl(fpw.BILL_BURDENED_COST, 0) +
3618 nvl(fin.BILL_BURDENED_COST, 0)
3619 end,
3620 fpw.CAPITALIZABLE_RAW_COST
3621 = case when fpw.CAPITALIZABLE_RAW_COST is null and
3622 fin.CAPITALIZABLE_RAW_COST is null
3623 then to_number(null)
3624 else nvl(fpw.CAPITALIZABLE_RAW_COST, 0) +
3625 nvl(fin.CAPITALIZABLE_RAW_COST, 0)
3626 end,
3627 fpw.CAPITALIZABLE_BRDN_COST
3628 = case when fpw.CAPITALIZABLE_BRDN_COST is null and
3629 fin.CAPITALIZABLE_BRDN_COST is null
3630 then to_number(null)
3631 else nvl(fpw.CAPITALIZABLE_BRDN_COST, 0) +
3632 nvl(fin.CAPITALIZABLE_BRDN_COST, 0)
3633 end,
3634 fpw.LABOR_RAW_COST = case when fpw.LABOR_RAW_COST is null and
3635 fin.LABOR_RAW_COST is null
3636 then to_number(null)
3640 fpw.LABOR_BURDENED_COST
3637 else nvl(fpw.LABOR_RAW_COST, 0) +
3638 nvl(fin.LABOR_RAW_COST, 0)
3639 end,
3641 = case when fpw.LABOR_BURDENED_COST is null and
3642 fin.LABOR_BURDENED_COST is null
3643 then to_number(null)
3644 else nvl(fpw.LABOR_BURDENED_COST, 0) +
3645 nvl(fin.LABOR_BURDENED_COST, 0)
3646 end,
3647 fpw.BILL_LABOR_RAW_COST
3648 = case when fpw.BILL_LABOR_RAW_COST is null and
3649 fin.BILL_LABOR_RAW_COST is null
3650 then to_number(null)
3651 else nvl(fpw.BILL_LABOR_RAW_COST, 0) +
3652 nvl(fin.BILL_LABOR_RAW_COST, 0)
3653 end,
3654 fpw.BILL_LABOR_BURDENED_COST
3655 = case when fpw.BILL_LABOR_BURDENED_COST is null and
3656 fin.BILL_LABOR_BURDENED_COST is null
3657 then to_number(null)
3658 else nvl(fpw.BILL_LABOR_BURDENED_COST, 0) +
3659 nvl(fin.BILL_LABOR_BURDENED_COST, 0)
3660 end,
3661 fpw.LABOR_HRS = case when fpw.LABOR_HRS is null and
3662 fin.LABOR_HRS is null
3663 then to_number(null)
3664 else nvl(fpw.LABOR_HRS, 0) +
3665 nvl(fin.LABOR_HRS, 0)
3666 end,
3667 fpw.BILL_LABOR_HRS = case when fpw.BILL_LABOR_HRS is null and
3668 fin.BILL_LABOR_HRS is null
3669 then to_number(null)
3670 else nvl(fpw.BILL_LABOR_HRS, 0) +
3671 nvl(fin.BILL_LABOR_HRS, 0)
3672 end,
3673 fpw.QUANTITY = case when fpw.QUANTITY is null and
3674 fin.QUANTITY is null
3675 then to_number(null)
3676 else nvl(fpw.QUANTITY, 0) +
3677 nvl(fin.QUANTITY, 0)
3678 end,
3679 fpw.BILL_QUANTITY = case when fpw.BILL_QUANTITY is null and
3680 fin.BILL_QUANTITY is null
3681 then to_number(null)
3682 else nvl(fpw.BILL_QUANTITY, 0) +
3683 nvl(fin.BILL_QUANTITY, 0)
3684 end,
3685 fpw.LAST_UPDATE_DATE
3689 fpw.LAST_UPDATE_LOGIN
3686 = fin.LAST_UPDATE_DATE,
3687 fpw.LAST_UPDATED_BY
3688 = fin.LAST_UPDATED_BY,
3690 = fin.LAST_UPDATE_LOGIN
3691 when not matched then insert
3692 (
3693 fpw.PROJECT_ORG_ID,
3694 fpw.PROJECT_ORGANIZATION_ID,
3695 fpw.TIME_ID,
3696 fpw.PROJECT_ID,
3697 fpw.EXP_EVT_TYPE_ID,
3698 fpw.WORK_TYPE_ID,
3699 fpw.PERIOD_TYPE_ID,
3700 fpw.CALENDAR_TYPE,
3701 fpw.CURR_RECORD_TYPE_ID,
3702 fpw.CURRENCY_CODE,
3703 fpw.PROJECT_TYPE_CLASS,
3704 fpw.RAW_COST,
3705 fpw.BURDENED_COST,
3706 fpw.BILL_RAW_COST,
3707 fpw.BILL_BURDENED_COST,
3708 fpw.CAPITALIZABLE_RAW_COST,
3709 fpw.CAPITALIZABLE_BRDN_COST,
3710 fpw.LABOR_RAW_COST,
3711 fpw.LABOR_BURDENED_COST,
3712 fpw.BILL_LABOR_RAW_COST,
3713 fpw.BILL_LABOR_BURDENED_COST,
3714 fpw.LABOR_HRS,
3715 fpw.BILL_LABOR_HRS,
3716 fpw.QUANTITY,
3717 fpw.BILL_QUANTITY,
3718 fpw.LAST_UPDATE_DATE,
3719 fpw.LAST_UPDATED_BY,
3720 fpw.CREATION_DATE,
3721 fpw.CREATED_BY,
3722 fpw.LAST_UPDATE_LOGIN
3723 )
3724 values
3725 (
3726 fin.PROJECT_ORG_ID,
3727 fin.PROJECT_ORGANIZATION_ID,
3728 fin.TIME_ID,
3729 fin.PROJECT_ID,
3730 fin.EXP_EVT_TYPE_ID,
3731 fin.WORK_TYPE_ID,
3732 fin.PERIOD_TYPE_ID,
3733 fin.CALENDAR_TYPE,
3734 fin.CURR_RECORD_TYPE_ID,
3735 fin.CURRENCY_CODE,
3736 fin.PROJECT_TYPE_CLASS,
3737 fin.RAW_COST,
3738 fin.BURDENED_COST,
3739 fin.BILL_RAW_COST,
3740 fin.BILL_BURDENED_COST,
3741 fin.CAPITALIZABLE_RAW_COST,
3742 fin.CAPITALIZABLE_BRDN_COST,
3743 fin.LABOR_RAW_COST,
3744 fin.LABOR_BURDENED_COST,
3745 fin.BILL_LABOR_RAW_COST,
3746 fin.BILL_LABOR_BURDENED_COST,
3747 fin.LABOR_HRS,
3748 fin.BILL_LABOR_HRS,
3749 fin.QUANTITY,
3750 fin.BILL_QUANTITY,
3751 fin.LAST_UPDATE_DATE,
3752 fin.LAST_UPDATED_BY,
3753 fin.CREATION_DATE,
3754 fin.CREATED_BY,
3755 fin.LAST_UPDATE_LOGIN
3756 );
3757
3758 end if;
3759
3760 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
3761 (
3762 l_process,
3763 'PJI_FM_SUM_ROLLUP_FIN.MERGE_FIN_INTO_FPW(p_worker_id);'
3764 );
3765
3766 -- truncate intermediate tables no longer required
3767 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
3768 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema , 'PJI_FM_AGGR_FIN4' , 'NORMAL',null);
3769
3770 commit;
3771
3772 end MERGE_FIN_INTO_FPW;
3773
3774
3775 -- -----------------------------------------------------
3776 -- procedure MERGE_FIN_INTO_FPE
3777 -- -----------------------------------------------------
3778 procedure MERGE_FIN_INTO_FPE (p_worker_id in number) is
3779
3780 l_process varchar2(30);
3781 l_extraction_type varchar2(30);
3782 l_last_update_date date;
3783 l_last_updated_by number;
3784 l_creation_date date;
3785 l_created_by number;
3786 l_last_update_login number;
3787 l_schema varchar2(30);
3788
3789 begin
3790
3791 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
3792
3793 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
3794 (
3795 l_process,
3796 'PJI_FM_SUM_ROLLUP_FIN.MERGE_FIN_INTO_FPE(p_worker_id);'
3797 )) then
3798 return;
3799 end if;
3800
3801 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3802 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
3803
3804 l_last_update_date := sysdate;
3805 l_last_updated_by := FND_GLOBAL.USER_ID;
3806 l_creation_date := sysdate;
3807 l_created_by := FND_GLOBAL.USER_ID;
3808 l_last_update_login := FND_GLOBAL.LOGIN_ID;
3809
3810 if (l_extraction_type = 'FULL') then
3811
3812 insert /*+ append parallel(fpe) */ into PJI_FP_PROJ_ET_F fpe
3813 (
3814 PROJECT_ORG_ID,
3815 PROJECT_ORGANIZATION_ID,
3816 TIME_ID,
3817 PROJECT_ID,
3818 EXP_EVT_TYPE_ID,
3819 PERIOD_TYPE_ID,
3820 CALENDAR_TYPE,
3821 CURR_RECORD_TYPE_ID,
3822 CURRENCY_CODE,
3823 PROJECT_TYPE_CLASS,
3824 REVENUE,
3828 BILL_RAW_COST,
3825 LABOR_REVENUE,
3826 RAW_COST,
3827 BURDENED_COST,
3829 BILL_BURDENED_COST,
3830 CAPITALIZABLE_RAW_COST,
3831 CAPITALIZABLE_BRDN_COST,
3832 LABOR_RAW_COST,
3833 LABOR_BURDENED_COST,
3834 BILL_LABOR_RAW_COST,
3835 BILL_LABOR_BURDENED_COST,
3836 LABOR_HRS,
3837 BILL_LABOR_HRS,
3838 QUANTITY,
3839 BILL_QUANTITY,
3840 LAST_UPDATE_DATE,
3841 LAST_UPDATED_BY,
3842 CREATION_DATE,
3843 CREATED_BY,
3844 LAST_UPDATE_LOGIN
3845 )
3846 select /*+ full(fin5) parallel(fin5) */
3847 fin5.PROJECT_ORG_ID,
3848 fin5.PROJECT_ORGANIZATION_ID,
3849 fin5.TIME_ID,
3850 fin5.PROJECT_ID,
3851 fin5.EXP_EVT_TYPE_ID,
3852 fin5.PERIOD_TYPE_ID,
3853 fin5.CALENDAR_TYPE,
3854 fin5.CURR_RECORD_TYPE_ID,
3855 fin5.CURRENCY_CODE,
3856 fin5.PROJECT_TYPE_CLASS,
3857 fin5.REVENUE,
3858 fin5.LABOR_REVENUE,
3859 fin5.RAW_COST,
3860 fin5.BURDENED_COST,
3861 decode(fin5.PROJECT_TYPE_CLASS,
3862 'B', fin5.BILL_RAW_COST,
3863 to_number(null)) BILL_RAW_COST,
3864 decode(fin5.Project_Type_Class,
3865 'B', fin5.BILL_BURDENED_COST,
3866 to_number(null)) BILL_BURDENED_COST,
3867 decode(fin5.PROJECT_TYPE_CLASS,
3868 'C', fin5.BILL_RAW_COST,
3869 to_number(null)) CAPITALIZABLE_RAW_COST,
3870 decode(fin5.PROJECT_TYPE_CLASS,
3871 'C', fin5.BILL_BURDENED_COST,
3872 to_number(null)) CAPITALIZABLE_BRDN_COST,
3873 fin5.LABOR_RAW_COST,
3874 fin5.LABOR_BURDENED_COST,
3875 fin5.BILL_LABOR_RAW_COST,
3876 fin5.BILL_LABOR_BURDENED_COST,
3877 fin5.LABOR_HRS,
3878 fin5.BILL_LABOR_HRS,
3879 fin5.QUANTITY,
3880 fin5.BILL_QUANTITY,
3881 l_last_update_date,
3882 l_last_updated_by,
3883 l_creation_date,
3884 l_created_by,
3885 l_last_update_login
3886 from
3887 PJI_FM_AGGR_FIN5 fin5
3888 where
3889 fin5.WORKER_ID = p_worker_id and
3890 (nvl(fin5.REVENUE , 0) <> 0 or
3891 nvl(fin5.LABOR_REVENUE , 0) <> 0 or
3892 nvl(fin5.RAW_COST , 0) <> 0 or
3893 nvl(fin5.BURDENED_COST , 0) <> 0 or
3894 nvl(fin5.BILL_RAW_COST , 0) <> 0 or
3895 nvl(fin5.BILL_BURDENED_COST , 0) <> 0 or
3896 nvl(fin5.LABOR_RAW_COST , 0) <> 0 or
3897 nvl(fin5.LABOR_BURDENED_COST , 0) <> 0 or
3898 nvl(fin5.BILL_LABOR_RAW_COST , 0) <> 0 or
3899 nvl(fin5.BILL_LABOR_BURDENED_COST, 0) <> 0 or
3900 nvl(fin5.LABOR_HRS , 0) <> 0 or
3901 nvl(fin5.BILL_LABOR_HRS , 0) <> 0 or
3902 nvl(fin5.QUANTITY , 0) <> 0 or
3903 nvl(fin5.BILL_QUANTITY , 0) <> 0);
3904
3905 else -- not initial data load
3906
3907 merge /*+ parallel(fpe) */ into PJI_FP_PROJ_ET_F fpe
3908 using
3909 (
3910 select
3911 PROJECT_ORG_ID,
3912 PROJECT_ORGANIZATION_ID,
3913 TIME_ID,
3914 PROJECT_ID,
3915 EXP_EVT_TYPE_ID,
3916 PERIOD_TYPE_ID,
3917 CALENDAR_TYPE,
3918 CURR_RECORD_TYPE_ID,
3919 CURRENCY_CODE,
3920 PROJECT_TYPE_CLASS,
3921 REVENUE,
3922 LABOR_REVENUE,
3923 RAW_COST,
3924 BURDENED_COST,
3925 BILL_RAW_COST,
3926 BILL_BURDENED_COST,
3927 CAPITALIZABLE_RAW_COST,
3928 CAPITALIZABLE_BRDN_COST,
3929 LABOR_RAW_COST,
3930 LABOR_BURDENED_COST,
3931 BILL_LABOR_RAW_COST,
3932 BILL_LABOR_BURDENED_COST,
3933 LABOR_HRS,
3934 BILL_LABOR_HRS,
3935 QUANTITY,
3936 BILL_QUANTITY,
3937 l_last_update_date LAST_UPDATE_DATE,
3938 l_last_updated_by LAST_UPDATED_BY,
3939 l_creation_date CREATION_DATE,
3940 l_created_by CREATED_BY,
3941 l_last_update_login LAST_UPDATE_LOGIN
3942 from
3943 (
3944 select
3945 PROJECT_ORG_ID,
3946 PROJECT_ORGANIZATION_ID,
3947 TIME_ID,
3948 PROJECT_ID,
3949 EXP_EVT_TYPE_ID,
3950 PERIOD_TYPE_ID,
3951 CALENDAR_TYPE,
3952 CURR_RECORD_TYPE_ID,
3953 CURRENCY_CODE,
3954 PROJECT_TYPE_CLASS,
3955 sum(REVENUE) REVENUE,
3956 sum(LABOR_REVENUE) LABOR_REVENUE,
3957 sum(RAW_COST) RAW_COST,
3958 sum(BURDENED_COST) BURDENED_COST,
3959 sum(BILL_RAW_COST) BILL_RAW_COST,
3960 sum(BILL_BURDENED_COST) BILL_BURDENED_COST,
3961 sum(CAPITALIZABLE_RAW_COST) CAPITALIZABLE_RAW_COST,
3962 sum(CAPITALIZABLE_BRDN_COST) CAPITALIZABLE_BRDN_COST,
3963 sum(LABOR_RAW_COST) LABOR_RAW_COST,
3964 sum(LABOR_BURDENED_COST) LABOR_BURDENED_COST,
3965 sum(BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
3966 sum(BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
3967 sum(LABOR_HRS) LABOR_HRS,
3968 sum(BILL_LABOR_HRS) BILL_LABOR_HRS,
3969 sum(QUANTITY) QUANTITY,
3973 select /*+ full(fin5) parallel(fin5) */
3970 sum(BILL_QUANTITY) BILL_QUANTITY
3971 from
3972 (
3974 fin5.PROJECT_ORG_ID,
3975 fin5.PROJECT_ORGANIZATION_ID,
3976 fin5.TIME_ID,
3977 fin5.PROJECT_ID,
3978 fin5.EXP_EVT_TYPE_ID,
3979 fin5.PERIOD_TYPE_ID,
3980 fin5.CALENDAR_TYPE,
3981 fin5.CURR_RECORD_TYPE_ID,
3982 fin5.CURRENCY_CODE,
3983 fin5.PROJECT_TYPE_CLASS,
3984 fin5.REVENUE,
3985 fin5.LABOR_REVENUE,
3986 fin5.RAW_COST,
3987 fin5.BURDENED_COST,
3988 decode(fin5.PROJECT_TYPE_CLASS,
3989 'B', fin5.BILL_RAW_COST,
3990 to_number(null)) BILL_RAW_COST,
3991 decode(fin5.Project_Type_Class,
3992 'B', fin5.BILL_BURDENED_COST,
3993 to_number(null)) BILL_BURDENED_COST,
3994 decode(fin5.PROJECT_TYPE_CLASS,
3995 'C', fin5.BILL_RAW_COST,
3996 to_number(null)) CAPITALIZABLE_RAW_COST,
3997 decode(fin5.PROJECT_TYPE_CLASS,
3998 'C', fin5.BILL_BURDENED_COST,
3999 to_number(null)) CAPITALIZABLE_BRDN_COST,
4000 fin5.LABOR_RAW_COST,
4001 fin5.LABOR_BURDENED_COST,
4002 fin5.BILL_LABOR_RAW_COST,
4003 fin5.BILL_LABOR_BURDENED_COST,
4004 fin5.LABOR_HRS,
4005 fin5.BILL_LABOR_HRS,
4006 fin5.QUANTITY,
4007 fin5.BILL_QUANTITY
4008 from
4009 PJI_FM_AGGR_FIN5 fin5
4010 where
4011 fin5.WORKER_ID = p_worker_id
4012 union all -- partial refresh
4013 select /*+ ordered full(map) parallel(map)
4014 index(fpe, PJI_FP_PROJ_ET_F_N2) use_nl(fpe) */
4015 fpe.PROJECT_ORG_ID,
4016 fpe.PROJECT_ORGANIZATION_ID,
4017 fpe.TIME_ID,
4018 fpe.PROJECT_ID,
4019 fpe.EXP_EVT_TYPE_ID,
4020 fpe.PERIOD_TYPE_ID,
4021 fpe.CALENDAR_TYPE,
4022 fpe.CURR_RECORD_TYPE_ID,
4023 fpe.CURRENCY_CODE,
4024 fpe.PROJECT_TYPE_CLASS,
4025 -fpe.REVENUE,
4026 -fpe.LABOR_REVENUE,
4027 -fpe.RAW_COST,
4028 -fpe.BURDENED_COST,
4029 -fpe.BILL_RAW_COST,
4030 -fpe.BILL_BURDENED_COST,
4031 -fpe.CAPITALIZABLE_RAW_COST,
4032 -fpe.CAPITALIZABLE_BRDN_COST,
4033 -fpe.LABOR_RAW_COST,
4034 -fpe.LABOR_BURDENED_COST,
4035 -fpe.BILL_LABOR_RAW_COST,
4036 -fpe.BILL_LABOR_BURDENED_COST,
4037 -fpe.LABOR_HRS,
4038 -fpe.BILL_LABOR_HRS,
4039 -fpe.QUANTITY,
4040 -fpe.BILL_QUANTITY
4041 from
4042 PJI_PJI_PROJ_BATCH_MAP map,
4043 PJI_FP_PROJ_ET_F fpe
4044 where
4045 l_extraction_type = 'PARTIAL' and
4046 map.WORKER_ID = p_worker_id and
4047 map.EXTRACTION_TYPE = 'P' and
4048 fpe.PROJECT_ID = map.PROJECT_ID
4049 )
4050 group by
4051 PROJECT_ORG_ID,
4052 PROJECT_ORGANIZATION_ID,
4053 TIME_ID,
4054 PROJECT_ID,
4055 EXP_EVT_TYPE_ID,
4056 PERIOD_TYPE_ID,
4057 CALENDAR_TYPE,
4058 CURR_RECORD_TYPE_ID,
4059 CURRENCY_CODE,
4060 PROJECT_TYPE_CLASS
4061 )
4062 where
4063 nvl(REVENUE , 0) <> 0 or
4064 nvl(LABOR_REVENUE , 0) <> 0 or
4065 nvl(RAW_COST , 0) <> 0 or
4066 nvl(BURDENED_COST , 0) <> 0 or
4067 nvl(BILL_RAW_COST , 0) <> 0 or
4068 nvl(BILL_BURDENED_COST , 0) <> 0 or
4069 nvl(CAPITALIZABLE_RAW_COST , 0) <> 0 or
4070 nvl(CAPITALIZABLE_BRDN_COST , 0) <> 0 or
4071 nvl(LABOR_RAW_COST , 0) <> 0 or
4072 nvl(LABOR_BURDENED_COST , 0) <> 0 or
4073 nvl(BILL_LABOR_RAW_COST , 0) <> 0 or
4074 nvl(BILL_LABOR_BURDENED_COST, 0) <> 0 or
4075 nvl(LABOR_HRS , 0) <> 0 or
4076 nvl(BILL_LABOR_HRS , 0) <> 0 or
4077 nvl(QUANTITY , 0) <> 0 or
4078 nvl(BILL_QUANTITY , 0) <> 0
4079 ) fin
4080 on
4081 (
4082 fin.PROJECT_ORG_ID = fpe.PROJECT_ORG_ID and
4083 fin.PROJECT_ORGANIZATION_ID = fpe.PROJECT_ORGANIZATION_ID and
4084 fin.TIME_ID = fpe.TIME_ID and
4085 fin.PROJECT_ID = fpe.PROJECT_ID and
4086 fin.EXP_EVT_TYPE_ID = fpe.EXP_EVT_TYPE_ID and
4087 fin.PERIOD_TYPE_ID = fpe.PERIOD_TYPE_ID and
4088 fin.CALENDAR_TYPE = fpe.CALENDAR_TYPE and
4089 fin.CURR_RECORD_TYPE_ID = fpe.CURR_RECORD_TYPE_ID and
4090 fin.CURRENCY_CODE = fpe.CURRENCY_CODE and
4091 fin.PROJECT_TYPE_CLASS = fpe.PROJECT_TYPE_CLASS
4092 )
4093 when matched then update set
4094 fpe.REVENUE = case when fpe.REVENUE is null and
4095 fin.REVENUE is null
4096 then to_number(null)
4097 else nvl(fpe.REVENUE, 0) +
4098 nvl(fin.REVENUE, 0)
4102 then to_number(null)
4099 end,
4100 fpe.LABOR_REVENUE = case when fpe.LABOR_REVENUE is null and
4101 fin.LABOR_REVENUE is null
4103 else nvl(fpe.LABOR_REVENUE, 0) +
4104 nvl(fin.LABOR_REVENUE, 0)
4105 end,
4106 fpe.RAW_COST = case when fpe.RAW_COST is null and
4107 fin.RAW_COST is null
4108 then to_number(null)
4109 else nvl(fpe.RAW_COST, 0) +
4110 nvl(fin.RAW_COST, 0)
4111 end,
4112 fpe.BURDENED_COST = case when fpe.BURDENED_COST is null and
4113 fin.BURDENED_COST is null
4114 then to_number(null)
4115 else nvl(fpe.BURDENED_COST, 0) +
4116 nvl(fin.BURDENED_COST, 0)
4117 end,
4118 fpe.BILL_RAW_COST = case when fpe.BILL_RAW_COST is null and
4119 fin.BILL_RAW_COST is null
4120 then to_number(null)
4121 else nvl(fpe.BILL_RAW_COST, 0) +
4122 nvl(fin.BILL_RAW_COST, 0)
4123 end,
4124 fpe.BILL_BURDENED_COST
4125 = case when fpe.BILL_BURDENED_COST is null and
4126 fin.BILL_BURDENED_COST is null
4127 then to_number(null)
4128 else nvl(fpe.BILL_BURDENED_COST, 0) +
4129 nvl(fin.BILL_BURDENED_COST, 0)
4130 end,
4131 fpe.CAPITALIZABLE_RAW_COST
4132 = case when fpe.CAPITALIZABLE_RAW_COST is null and
4133 fin.CAPITALIZABLE_RAW_COST is null
4134 then to_number(null)
4135 else nvl(fpe.CAPITALIZABLE_RAW_COST, 0) +
4136 nvl(fin.CAPITALIZABLE_RAW_COST, 0)
4137 end,
4138 fpe.CAPITALIZABLE_BRDN_COST
4139 = case when fpe.CAPITALIZABLE_BRDN_COST is null and
4140 fin.CAPITALIZABLE_BRDN_COST is null
4141 then to_number(null)
4142 else nvl(fpe.CAPITALIZABLE_BRDN_COST, 0) +
4143 nvl(fin.CAPITALIZABLE_BRDN_COST, 0)
4144 end,
4145 fpe.LABOR_RAW_COST = case when fpe.LABOR_RAW_COST is null and
4146 fin.LABOR_RAW_COST is null
4147 then to_number(null)
4148 else nvl(fpe.LABOR_RAW_COST, 0) +
4149 nvl(fin.LABOR_RAW_COST, 0)
4150 end,
4151 fpe.LABOR_BURDENED_COST
4152 = case when fpe.LABOR_BURDENED_COST is null and
4153 fin.LABOR_BURDENED_COST is null
4154 then to_number(null)
4155 else nvl(fpe.LABOR_BURDENED_COST, 0) +
4156 nvl(fin.LABOR_BURDENED_COST, 0)
4157 end,
4158 fpe.BILL_LABOR_RAW_COST
4159 = case when fpe.BILL_LABOR_RAW_COST is null and
4160 fin.BILL_LABOR_RAW_COST is null
4161 then to_number(null)
4162 else nvl(fpe.BILL_LABOR_RAW_COST, 0) +
4163 nvl(fin.BILL_LABOR_RAW_COST, 0)
4164 end,
4165 fpe.BILL_LABOR_BURDENED_COST
4166 = case when fpe.BILL_LABOR_BURDENED_COST is null and
4167 fin.BILL_LABOR_BURDENED_COST is null
4168 then to_number(null)
4169 else nvl(fpe.BILL_LABOR_BURDENED_COST, 0) +
4170 nvl(fin.BILL_LABOR_BURDENED_COST, 0)
4171 end,
4172 fpe.LABOR_HRS = case when fpe.LABOR_HRS is null and
4173 fin.LABOR_HRS is null
4174 then to_number(null)
4175 else nvl(fpe.LABOR_HRS, 0) +
4176 nvl(fin.LABOR_HRS, 0)
4177 end,
4178 fpe.BILL_LABOR_HRS = case when fpe.BILL_LABOR_HRS is null and
4179 fin.BILL_LABOR_HRS is null
4180 then to_number(null)
4181 else nvl(fpe.BILL_LABOR_HRS, 0) +
4182 nvl(fin.BILL_LABOR_HRS, 0)
4183 end,
4184 fpe.QUANTITY = case when fpe.QUANTITY is null and
4185 fin.QUANTITY is null
4186 then to_number(null)
4187 else nvl(fpe.QUANTITY, 0) +
4188 nvl(fin.QUANTITY, 0)
4189 end,
4190 fpe.BILL_QUANTITY = case when fpe.BILL_QUANTITY is null and
4191 fin.BILL_QUANTITY is null
4192 then to_number(null)
4193 else nvl(fpe.BILL_QUANTITY, 0) +
4194 nvl(fin.BILL_QUANTITY, 0)
4195 end,
4196 fpe.LAST_UPDATE_DATE
4197 = fin.LAST_UPDATE_DATE,
4198 fpe.LAST_UPDATED_BY
4199 = fin.LAST_UPDATED_BY,
4200 fpe.LAST_UPDATE_LOGIN
4201 = fin.LAST_UPDATE_LOGIN
4202 when not matched then insert
4203 (
4204 fpe.PROJECT_ORG_ID,
4205 fpe.PROJECT_ORGANIZATION_ID,
4206 fpe.TIME_ID,
4207 fpe.PROJECT_ID,
4208 fpe.EXP_EVT_TYPE_ID,
4209 fpe.PERIOD_TYPE_ID,
4210 fpe.CALENDAR_TYPE,
4211 fpe.CURR_RECORD_TYPE_ID,
4212 fpe.CURRENCY_CODE,
4213 fpe.PROJECT_TYPE_CLASS,
4214 fpe.REVENUE,
4215 fpe.LABOR_REVENUE,
4216 fpe.RAW_COST,
4217 fpe.BURDENED_COST,
4218 fpe.BILL_RAW_COST,
4219 fpe.BILL_BURDENED_COST,
4220 fpe.CAPITALIZABLE_RAW_COST,
4221 fpe.CAPITALIZABLE_BRDN_COST,
4222 fpe.LABOR_RAW_COST,
4223 fpe.LABOR_BURDENED_COST,
4224 fpe.BILL_LABOR_RAW_COST,
4225 fpe.BILL_LABOR_BURDENED_COST,
4226 fpe.LABOR_HRS,
4227 fpe.BILL_LABOR_HRS,
4228 fpe.QUANTITY,
4229 fpe.BILL_QUANTITY,
4230 fpe.LAST_UPDATE_DATE,
4231 fpe.LAST_UPDATED_BY,
4232 fpe.CREATION_DATE,
4233 fpe.CREATED_BY,
4234 fpe.LAST_UPDATE_LOGIN
4235 )
4236 values
4237 (
4238 fin.PROJECT_ORG_ID,
4239 fin.PROJECT_ORGANIZATION_ID,
4240 fin.TIME_ID,
4241 fin.PROJECT_ID,
4242 fin.EXP_EVT_TYPE_ID,
4243 fin.PERIOD_TYPE_ID,
4244 fin.CALENDAR_TYPE,
4245 fin.CURR_RECORD_TYPE_ID,
4246 fin.CURRENCY_CODE,
4247 fin.PROJECT_TYPE_CLASS,
4248 fin.REVENUE,
4249 fin.LABOR_REVENUE,
4250 fin.RAW_COST,
4254 fin.CAPITALIZABLE_RAW_COST,
4251 fin.BURDENED_COST,
4252 fin.BILL_RAW_COST,
4253 fin.BILL_BURDENED_COST,
4255 fin.CAPITALIZABLE_BRDN_COST,
4256 fin.LABOR_RAW_COST,
4257 fin.LABOR_BURDENED_COST,
4258 fin.BILL_LABOR_RAW_COST,
4259 fin.BILL_LABOR_BURDENED_COST,
4260 fin.LABOR_HRS,
4261 fin.BILL_LABOR_HRS,
4262 fin.QUANTITY,
4263 fin.BILL_QUANTITY,
4264 fin.LAST_UPDATE_DATE,
4265 fin.LAST_UPDATED_BY,
4266 fin.CREATION_DATE,
4267 fin.CREATED_BY,
4268 fin.LAST_UPDATE_LOGIN
4269 );
4270
4271 end if;
4272
4273 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
4274 (
4275 l_process,
4276 'PJI_FM_SUM_ROLLUP_FIN.MERGE_FIN_INTO_FPE(p_worker_id);'
4277 );
4278
4279 -- truncate intermediate tables no longer required
4280 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
4281 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema , 'PJI_FM_AGGR_FIN5' , 'NORMAL',null);
4282
4283 commit;
4284
4285 end MERGE_FIN_INTO_FPE;
4286
4287
4288 -- -----------------------------------------------------
4289 -- procedure MERGE_FIN_INTO_FPP
4290 -- -----------------------------------------------------
4291 procedure MERGE_FIN_INTO_FPP (p_worker_id in number) is
4292
4293 l_process varchar2(30);
4294 l_extraction_type varchar2(30);
4295 l_last_update_date date;
4296 l_last_updated_by number;
4297 l_creation_date date;
4298 l_created_by number;
4299 l_last_update_login number;
4300 l_schema varchar2(30);
4301
4302 begin
4303
4304 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
4305
4306 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
4307 (
4308 l_process,
4309 'PJI_FM_SUM_ROLLUP_FIN.MERGE_FIN_INTO_FPP(p_worker_id);'
4310 )) then
4311 return;
4312 end if;
4313
4314 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
4315 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
4316
4317 l_last_update_date := sysdate;
4318 l_last_updated_by := FND_GLOBAL.USER_ID;
4319 l_creation_date := sysdate;
4320 l_created_by := FND_GLOBAL.USER_ID;
4321 l_last_update_login := FND_GLOBAL.LOGIN_ID;
4322
4323 if (l_extraction_type = 'FULL') then
4324
4325 insert /*+ append parallel(fpp) */ into PJI_FP_PROJ_F fpp
4326 (
4327 PROJECT_ORG_ID,
4328 PROJECT_ORGANIZATION_ID,
4329 TIME_ID,
4330 PROJECT_ID,
4331 PERIOD_TYPE_ID,
4332 CALENDAR_TYPE,
4333 CURR_RECORD_TYPE_ID,
4334 CURRENCY_CODE,
4335 PROJECT_TYPE_CLASS,
4336 REVENUE,
4337 LABOR_REVENUE,
4338 RAW_COST,
4339 BURDENED_COST,
4340 BILL_RAW_COST,
4341 BILL_BURDENED_COST,
4342 CAPITALIZABLE_RAW_COST,
4343 CAPITALIZABLE_BRDN_COST,
4344 LABOR_RAW_COST,
4345 LABOR_BURDENED_COST,
4346 BILL_LABOR_RAW_COST,
4347 BILL_LABOR_BURDENED_COST,
4348 REVENUE_WRITEOFF,
4349 LABOR_HRS,
4350 BILL_LABOR_HRS,
4351 CURR_BGT_REVENUE,
4352 CURR_BGT_RAW_COST,
4353 CURR_BGT_BURDENED_COST,
4354 CURR_BGT_LABOR_HRS,
4355 ORIG_BGT_REVENUE,
4356 ORIG_BGT_RAW_COST,
4357 ORIG_BGT_BURDENED_COST,
4358 ORIG_BGT_LABOR_HRS,
4359 FORECAST_REVENUE,
4360 FORECAST_RAW_COST,
4361 FORECAST_BURDENED_COST,
4362 FORECAST_LABOR_HRS,
4363 LAST_UPDATE_DATE,
4364 LAST_UPDATED_BY,
4365 CREATION_DATE,
4366 CREATED_BY,
4367 LAST_UPDATE_LOGIN
4368 )
4369 select /*+ full(fin3) parallel(fin3) */
4370 fin3.PROJECT_ORG_ID,
4371 fin3.PROJECT_ORGANIZATION_ID,
4372 fin3.TIME_ID,
4373 fin3.PROJECT_ID,
4374 fin3.PERIOD_TYPE_ID,
4375 fin3.CALENDAR_TYPE,
4376 fin3.CURR_RECORD_TYPE_ID,
4377 fin3.CURRENCY_CODE,
4378 fin3.PROJECT_TYPE_CLASS,
4379 fin3.REVENUE,
4380 fin3.LABOR_REVENUE,
4381 fin3.RAW_COST,
4382 fin3.BURDENED_COST,
4383 decode(fin3.PROJECT_TYPE_CLASS,
4384 'B', fin3.BILL_RAW_COST,
4385 to_number(null)) BILL_RAW_COST,
4386 decode(fin3.Project_Type_Class,
4387 'B', fin3.BILL_BURDENED_COST,
4388 to_number(null)) BILL_BURDENED_COST,
4389 decode(fin3.PROJECT_TYPE_CLASS,
4390 'C', fin3.BILL_RAW_COST,
4391 to_number(null)) CAPITALIZABLE_RAW_COST,
4392 decode(fin3.PROJECT_TYPE_CLASS,
4393 'C', fin3.BILL_BURDENED_COST,
4394 to_number(null)) CAPITALIZABLE_BRDN_COST,
4395 fin3.LABOR_RAW_COST,
4396 fin3.LABOR_BURDENED_COST,
4397 fin3.BILL_LABOR_RAW_COST,
4398 fin3.BILL_LABOR_BURDENED_COST,
4399 fin3.REVENUE_WRITEOFF,
4400 fin3.LABOR_HRS,
4401 fin3.BILL_LABOR_HRS,
4402 fin3.CURR_BGT_REVENUE,
4403 fin3.CURR_BGT_RAW_COST,
4404 fin3.CURR_BGT_BURDENED_COST,
4405 fin3.CURR_BGT_LABOR_HRS,
4406 fin3.ORIG_BGT_REVENUE,
4407 fin3.ORIG_BGT_RAW_COST,
4408 fin3.ORIG_BGT_BURDENED_COST,
4409 fin3.ORIG_BGT_LABOR_HRS,
4410 fin3.FORECAST_REVENUE,
4411 fin3.FORECAST_RAW_COST,
4412 fin3.FORECAST_BURDENED_COST,
4413 fin3.FORECAST_LABOR_HRS,
4414 l_last_update_date,
4415 l_last_updated_by,
4416 l_creation_date,
4417 l_created_by,
4418 l_last_update_login
4419 from
4420 PJI_FM_AGGR_FIN3 fin3
4421 where
4422 fin3.WORKER_ID = p_worker_id and
4423 (nvl(fin3.REVENUE , 0) <> 0 or
4424 nvl(fin3.LABOR_REVENUE , 0) <> 0 or
4425 nvl(fin3.RAW_COST , 0) <> 0 or
4426 nvl(fin3.BURDENED_COST , 0) <> 0 or
4427 nvl(fin3.BILL_RAW_COST , 0) <> 0 or
4428 nvl(fin3.BILL_BURDENED_COST , 0) <> 0 or
4429 nvl(fin3.LABOR_RAW_COST , 0) <> 0 or
4430 nvl(fin3.LABOR_BURDENED_COST , 0) <> 0 or
4431 nvl(fin3.BILL_LABOR_RAW_COST , 0) <> 0 or
4432 nvl(fin3.BILL_LABOR_BURDENED_COST, 0) <> 0 or
4433 nvl(fin3.REVENUE_WRITEOFF , 0) <> 0 or
4434 nvl(fin3.LABOR_HRS , 0) <> 0 or
4435 nvl(fin3.BILL_LABOR_HRS , 0) <> 0 or
4436 nvl(fin3.CURR_BGT_REVENUE , 0) <> 0 or
4437 nvl(fin3.CURR_BGT_RAW_COST , 0) <> 0 or
4438 nvl(fin3.CURR_BGT_BURDENED_COST , 0) <> 0 or
4439 nvl(fin3.CURR_BGT_LABOR_HRS , 0) <> 0 or
4440 nvl(fin3.ORIG_BGT_REVENUE , 0) <> 0 or
4441 nvl(fin3.ORIG_BGT_RAW_COST , 0) <> 0 or
4442 nvl(fin3.ORIG_BGT_BURDENED_COST , 0) <> 0 or
4443 nvl(fin3.ORIG_BGT_LABOR_HRS , 0) <> 0 or
4444 nvl(fin3.FORECAST_REVENUE , 0) <> 0 or
4445 nvl(fin3.FORECAST_RAW_COST , 0) <> 0 or
4446 nvl(fin3.FORECAST_BURDENED_COST , 0) <> 0 or
4447 nvl(fin3.FORECAST_LABOR_HRS , 0) <> 0);
4448
4449 else -- not initial data load
4450
4451 merge /*+ parallel(fpp) */ into PJI_FP_PROJ_F fpp
4452 using
4453 (
4454 select
4455 PROJECT_ORG_ID,
4456 PROJECT_ORGANIZATION_ID,
4457 TIME_ID,
4458 PROJECT_ID,
4459 PERIOD_TYPE_ID,
4460 CALENDAR_TYPE,
4461 CURR_RECORD_TYPE_ID,
4462 CURRENCY_CODE,
4463 PROJECT_TYPE_CLASS,
4464 REVENUE,
4465 LABOR_REVENUE,
4466 RAW_COST,
4467 BURDENED_COST,
4468 BILL_RAW_COST,
4469 BILL_BURDENED_COST,
4470 CAPITALIZABLE_RAW_COST,
4471 CAPITALIZABLE_BRDN_COST,
4472 LABOR_RAW_COST,
4473 LABOR_BURDENED_COST,
4474 BILL_LABOR_RAW_COST,
4475 BILL_LABOR_BURDENED_COST,
4476 REVENUE_WRITEOFF,
4477 LABOR_HRS,
4478 BILL_LABOR_HRS,
4479 CURR_BGT_REVENUE,
4480 CURR_BGT_RAW_COST,
4481 CURR_BGT_BURDENED_COST,
4482 CURR_BGT_LABOR_HRS,
4483 ORIG_BGT_REVENUE,
4484 ORIG_BGT_RAW_COST,
4485 ORIG_BGT_BURDENED_COST,
4486 ORIG_BGT_LABOR_HRS,
4487 FORECAST_REVENUE,
4488 FORECAST_RAW_COST,
4489 FORECAST_BURDENED_COST,
4490 FORECAST_LABOR_HRS,
4491 l_last_update_date LAST_UPDATE_DATE,
4492 l_last_updated_by LAST_UPDATED_BY,
4493 l_creation_date CREATION_DATE,
4494 l_created_by CREATED_BY,
4495 l_last_update_login LAST_UPDATE_LOGIN
4496 from
4497 (
4498 select
4499 PROJECT_ORG_ID,
4500 PROJECT_ORGANIZATION_ID,
4504 CALENDAR_TYPE,
4501 TIME_ID,
4502 PROJECT_ID,
4503 PERIOD_TYPE_ID,
4505 CURR_RECORD_TYPE_ID,
4506 CURRENCY_CODE,
4507 PROJECT_TYPE_CLASS,
4508 sum(REVENUE) REVENUE,
4509 sum(LABOR_REVENUE) LABOR_REVENUE,
4510 sum(RAW_COST) RAW_COST,
4511 sum(BURDENED_COST) BURDENED_COST,
4512 sum(BILL_RAW_COST) BILL_RAW_COST,
4513 sum(BILL_BURDENED_COST) BILL_BURDENED_COST,
4514 sum(CAPITALIZABLE_RAW_COST) CAPITALIZABLE_RAW_COST,
4515 sum(CAPITALIZABLE_BRDN_COST) CAPITALIZABLE_BRDN_COST,
4516 sum(LABOR_RAW_COST) LABOR_RAW_COST,
4517 sum(LABOR_BURDENED_COST) LABOR_BURDENED_COST,
4518 sum(BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST,
4519 sum(BILL_LABOR_BURDENED_COST) BILL_LABOR_BURDENED_COST,
4520 sum(REVENUE_WRITEOFF) REVENUE_WRITEOFF,
4521 sum(LABOR_HRS) LABOR_HRS,
4522 sum(BILL_LABOR_HRS) BILL_LABOR_HRS,
4523 sum(CURR_BGT_REVENUE) CURR_BGT_REVENUE,
4524 sum(CURR_BGT_RAW_COST) CURR_BGT_RAW_COST,
4525 sum(CURR_BGT_BURDENED_COST) CURR_BGT_BURDENED_COST,
4526 sum(CURR_BGT_LABOR_HRS) CURR_BGT_LABOR_HRS,
4527 sum(ORIG_BGT_REVENUE) ORIG_BGT_REVENUE,
4528 sum(ORIG_BGT_RAW_COST) ORIG_BGT_RAW_COST,
4529 sum(ORIG_BGT_BURDENED_COST) ORIG_BGT_BURDENED_COST,
4530 sum(ORIG_BGT_LABOR_HRS) ORIG_BGT_LABOR_HRS,
4531 sum(FORECAST_REVENUE) FORECAST_REVENUE,
4532 sum(FORECAST_RAW_COST) FORECAST_RAW_COST,
4533 sum(FORECAST_BURDENED_COST) FORECAST_BURDENED_COST,
4534 sum(FORECAST_LABOR_HRS) FORECAST_LABOR_HRS
4535 from
4536 (
4537 select /*+ full(fin3) parallel(fin3) */
4538 fin3.PROJECT_ORG_ID,
4539 fin3.PROJECT_ORGANIZATION_ID,
4540 fin3.TIME_ID,
4541 fin3.PROJECT_ID,
4542 fin3.PERIOD_TYPE_ID,
4543 fin3.CALENDAR_TYPE,
4544 fin3.CURR_RECORD_TYPE_ID,
4545 fin3.CURRENCY_CODE,
4546 fin3.PROJECT_TYPE_CLASS,
4547 fin3.REVENUE,
4548 fin3.LABOR_REVENUE,
4549 fin3.RAW_COST,
4550 fin3.BURDENED_COST,
4551 decode(fin3.PROJECT_TYPE_CLASS,
4552 'B', fin3.BILL_RAW_COST,
4553 to_number(null)) BILL_RAW_COST,
4554 decode(fin3.Project_Type_Class,
4555 'B', fin3.BILL_BURDENED_COST,
4556 to_number(null)) BILL_BURDENED_COST,
4557 decode(fin3.PROJECT_TYPE_CLASS,
4558 'C', fin3.BILL_RAW_COST,
4559 to_number(null)) CAPITALIZABLE_RAW_COST,
4560 decode(fin3.PROJECT_TYPE_CLASS,
4561 'C', fin3.BILL_BURDENED_COST,
4562 to_number(null)) CAPITALIZABLE_BRDN_COST,
4563 fin3.LABOR_RAW_COST,
4564 fin3.LABOR_BURDENED_COST,
4565 fin3.BILL_LABOR_RAW_COST,
4566 fin3.BILL_LABOR_BURDENED_COST,
4567 fin3.REVENUE_WRITEOFF,
4568 fin3.LABOR_HRS,
4569 fin3.BILL_LABOR_HRS,
4570 fin3.CURR_BGT_REVENUE,
4571 fin3.CURR_BGT_RAW_COST,
4572 fin3.CURR_BGT_BURDENED_COST,
4573 fin3.CURR_BGT_LABOR_HRS,
4574 fin3.ORIG_BGT_REVENUE,
4575 fin3.ORIG_BGT_RAW_COST,
4576 fin3.ORIG_BGT_BURDENED_COST,
4577 fin3.ORIG_BGT_LABOR_HRS,
4578 fin3.FORECAST_REVENUE,
4579 fin3.FORECAST_RAW_COST,
4580 fin3.FORECAST_BURDENED_COST,
4581 fin3.FORECAST_LABOR_HRS
4582 from
4583 PJI_FM_AGGR_FIN3 fin3
4584 where
4585 fin3.WORKER_ID = p_worker_id
4586 union all -- partial refresh
4587 select /*+ ordered full(map) parallel(map)
4588 index(fpp, PJI_FP_PROJ_F_N2) use_nl(fpp) */
4589 fpp.PROJECT_ORG_ID,
4590 fpp.PROJECT_ORGANIZATION_ID,
4591 fpp.TIME_ID,
4592 fpp.PROJECT_ID,
4593 fpp.PERIOD_TYPE_ID,
4594 fpp.CALENDAR_TYPE,
4595 fpp.CURR_RECORD_TYPE_ID,
4596 fpp.CURRENCY_CODE,
4597 fpp.PROJECT_TYPE_CLASS,
4598 -fpp.REVENUE,
4599 -fpp.LABOR_REVENUE,
4600 -fpp.RAW_COST,
4601 -fpp.BURDENED_COST,
4602 -fpp.BILL_RAW_COST,
4603 -fpp.BILL_BURDENED_COST,
4604 -fpp.CAPITALIZABLE_RAW_COST,
4605 -fpp.CAPITALIZABLE_BRDN_COST,
4606 -fpp.LABOR_RAW_COST,
4607 -fpp.LABOR_BURDENED_COST,
4608 -fpp.BILL_LABOR_RAW_COST,
4609 -fpp.BILL_LABOR_BURDENED_COST,
4610 -fpp.REVENUE_WRITEOFF,
4611 -fpp.LABOR_HRS,
4612 -fpp.BILL_LABOR_HRS,
4613 to_number(null) CURR_BGT_REVENUE,
4614 to_number(null) CURR_BGT_RAW_COST,
4615 to_number(null) CURR_BGT_BURDENED_COST,
4616 to_number(null) CURR_BGT_LABOR_HRS,
4617 to_number(null) ORIG_BGT_REVENUE,
4618 to_number(null) ORIG_BGT_RAW_COST,
4619 to_number(null) ORIG_BGT_BURDENED_COST,
4620 to_number(null) ORIG_BGT_LABOR_HRS,
4621 to_number(null) FORECAST_REVENUE,
4622 to_number(null) FORECAST_RAW_COST,
4626 PJI_PJI_PROJ_BATCH_MAP map,
4623 to_number(null) FORECAST_BURDENED_COST,
4624 to_number(null) FORECAST_LABOR_HRS
4625 from
4627 PJI_FP_PROJ_F fpp
4628 where
4629 l_extraction_type = 'PARTIAL' and
4630 map.WORKER_ID = p_worker_id and
4631 map.EXTRACTION_TYPE = 'P' and
4632 fpp.PROJECT_ID = map.PROJECT_ID
4633 )
4634 group by
4635 PROJECT_ORG_ID,
4636 PROJECT_ORGANIZATION_ID,
4637 TIME_ID,
4638 PROJECT_ID,
4639 PERIOD_TYPE_ID,
4640 CALENDAR_TYPE,
4641 CURR_RECORD_TYPE_ID,
4642 CURRENCY_CODE,
4643 PROJECT_TYPE_CLASS
4644 )
4645 where
4646 nvl(REVENUE , 0) <> 0 or
4647 nvl(LABOR_REVENUE , 0) <> 0 or
4648 nvl(RAW_COST , 0) <> 0 or
4649 nvl(BURDENED_COST , 0) <> 0 or
4650 nvl(BILL_RAW_COST , 0) <> 0 or
4651 nvl(BILL_BURDENED_COST , 0) <> 0 or
4652 nvl(CAPITALIZABLE_RAW_COST , 0) <> 0 or
4653 nvl(CAPITALIZABLE_BRDN_COST , 0) <> 0 or
4654 nvl(LABOR_RAW_COST , 0) <> 0 or
4655 nvl(LABOR_BURDENED_COST , 0) <> 0 or
4656 nvl(BILL_LABOR_RAW_COST , 0) <> 0 or
4657 nvl(BILL_LABOR_BURDENED_COST, 0) <> 0 or
4658 nvl(REVENUE_WRITEOFF , 0) <> 0 or
4659 nvl(LABOR_HRS , 0) <> 0 or
4660 nvl(BILL_LABOR_HRS , 0) <> 0 or
4661 nvl(CURR_BGT_REVENUE , 0) <> 0 or
4662 nvl(CURR_BGT_RAW_COST , 0) <> 0 or
4663 nvl(CURR_BGT_BURDENED_COST , 0) <> 0 or
4664 nvl(CURR_BGT_LABOR_HRS , 0) <> 0 or
4665 nvl(ORIG_BGT_REVENUE , 0) <> 0 or
4666 nvl(ORIG_BGT_RAW_COST , 0) <> 0 or
4667 nvl(ORIG_BGT_BURDENED_COST , 0) <> 0 or
4668 nvl(ORIG_BGT_LABOR_HRS , 0) <> 0 or
4669 nvl(FORECAST_REVENUE , 0) <> 0 or
4670 nvl(FORECAST_RAW_COST , 0) <> 0 or
4671 nvl(FORECAST_BURDENED_COST , 0) <> 0 or
4672 nvl(FORECAST_LABOR_HRS , 0) <> 0
4673 ) fin
4674 on
4675 (
4676 fin.PROJECT_ORG_ID = fpp.PROJECT_ORG_ID and
4677 fin.PROJECT_ORGANIZATION_ID = fpp.PROJECT_ORGANIZATION_ID and
4678 fin.TIME_ID = fpp.TIME_ID and
4679 fin.PROJECT_ID = fpp.PROJECT_ID and
4680 fin.PERIOD_TYPE_ID = fpp.PERIOD_TYPE_ID and
4681 fin.CALENDAR_TYPE = fpp.CALENDAR_TYPE and
4682 fin.CURR_RECORD_TYPE_ID = fpp.CURR_RECORD_TYPE_ID and
4683 fin.CURRENCY_CODE = fpp.CURRENCY_CODE and
4684 fin.PROJECT_TYPE_CLASS = fpp.PROJECT_TYPE_CLASS
4685 )
4686 when matched then update set
4687 fpp.REVENUE = case when fpp.REVENUE is null and
4688 fin.REVENUE is null
4689 then to_number(null)
4690 else nvl(fpp.REVENUE, 0) +
4691 nvl(fin.REVENUE, 0)
4692 end,
4693 fpp.LABOR_REVENUE = case when fpp.LABOR_REVENUE is null and
4694 fin.LABOR_REVENUE is null
4695 then to_number(null)
4696 else nvl(fpp.LABOR_REVENUE, 0) +
4697 nvl(fin.LABOR_REVENUE, 0)
4698 end,
4699 fpp.RAW_COST = case when fpp.RAW_COST is null and
4700 fin.RAW_COST is null
4701 then to_number(null)
4702 else nvl(fpp.RAW_COST, 0) +
4703 nvl(fin.RAW_COST, 0)
4704 end,
4705 fpp.BURDENED_COST = case when fpp.BURDENED_COST is null and
4706 fin.BURDENED_COST is null
4707 then to_number(null)
4708 else nvl(fpp.BURDENED_COST, 0) +
4709 nvl(fin.BURDENED_COST, 0)
4710 end,
4711 fpp.BILL_RAW_COST = case when fpp.BILL_RAW_COST is null and
4712 fin.BILL_RAW_COST is null
4713 then to_number(null)
4714 else nvl(fpp.BILL_RAW_COST, 0) +
4715 nvl(fin.BILL_RAW_COST, 0)
4716 end,
4717 fpp.BILL_BURDENED_COST
4718 = case when fpp.BILL_BURDENED_COST is null and
4719 fin.BILL_BURDENED_COST is null
4720 then to_number(null)
4721 else nvl(fpp.BILL_BURDENED_COST, 0) +
4722 nvl(fin.BILL_BURDENED_COST, 0)
4723 end,
4724 fpp.CAPITALIZABLE_RAW_COST
4725 = case when fpp.CAPITALIZABLE_RAW_COST is null and
4726 fin.CAPITALIZABLE_RAW_COST is null
4727 then to_number(null)
4728 else nvl(fpp.CAPITALIZABLE_RAW_COST, 0) +
4729 nvl(fin.CAPITALIZABLE_RAW_COST, 0)
4730 end,
4731 fpp.CAPITALIZABLE_BRDN_COST
4732 = case when fpp.CAPITALIZABLE_BRDN_COST is null and
4733 fin.CAPITALIZABLE_BRDN_COST is null
4737 end,
4734 then to_number(null)
4735 else nvl(fpp.CAPITALIZABLE_BRDN_COST, 0) +
4736 nvl(fin.CAPITALIZABLE_BRDN_COST, 0)
4738 fpp.LABOR_RAW_COST = case when fpp.LABOR_RAW_COST is null and
4739 fin.LABOR_RAW_COST is null
4740 then to_number(null)
4741 else nvl(fpp.LABOR_RAW_COST, 0) +
4742 nvl(fin.LABOR_RAW_COST, 0)
4743 end,
4744 fpp.LABOR_BURDENED_COST
4745 = case when fpp.LABOR_BURDENED_COST is null and
4746 fin.LABOR_BURDENED_COST is null
4747 then to_number(null)
4748 else nvl(fpp.LABOR_BURDENED_COST, 0) +
4749 nvl(fin.LABOR_BURDENED_COST, 0)
4750 end,
4751 fpp.BILL_LABOR_RAW_COST
4752 = case when fpp.BILL_LABOR_RAW_COST is null and
4753 fin.BILL_LABOR_RAW_COST is null
4754 then to_number(null)
4755 else nvl(fpp.BILL_LABOR_RAW_COST, 0) +
4756 nvl(fin.BILL_LABOR_RAW_COST, 0)
4757 end,
4758 fpp.BILL_LABOR_BURDENED_COST
4759 = case when fpp.BILL_LABOR_BURDENED_COST is null and
4760 fin.BILL_LABOR_BURDENED_COST is null
4761 then to_number(null)
4762 else nvl(fpp.BILL_LABOR_BURDENED_COST, 0) +
4763 nvl(fin.BILL_LABOR_BURDENED_COST, 0)
4764 end,
4765 fpp.REVENUE_WRITEOFF
4766 = case when fpp.REVENUE_WRITEOFF is null and
4767 fin.REVENUE_WRITEOFF is null
4768 then to_number(null)
4769 else nvl(fpp.REVENUE_WRITEOFF, 0) +
4770 nvl(fin.REVENUE_WRITEOFF, 0)
4771 end,
4772 fpp.LABOR_HRS = case when fpp.LABOR_HRS is null and
4773 fin.LABOR_HRS is null
4774 then to_number(null)
4775 else nvl(fpp.LABOR_HRS, 0) +
4776 nvl(fin.LABOR_HRS, 0)
4777 end,
4778 fpp.BILL_LABOR_HRS = case when fpp.BILL_LABOR_HRS is null and
4779 fin.BILL_LABOR_HRS is null
4780 then to_number(null)
4781 else nvl(fpp.BILL_LABOR_HRS, 0) +
4782 nvl(fin.BILL_LABOR_HRS, 0)
4783 end,
4784 fpp.CURR_BGT_REVENUE
4785 = case when fpp.CURR_BGT_REVENUE is null and
4786 fin.CURR_BGT_REVENUE is null
4787 then to_number(null)
4788 else nvl(fpp.CURR_BGT_REVENUE, 0) +
4789 nvl(fin.CURR_BGT_REVENUE, 0)
4790 end,
4791 fpp.CURR_BGT_RAW_COST
4792 = case when fpp.CURR_BGT_RAW_COST is null and
4793 fin.CURR_BGT_RAW_COST is null
4794 then to_number(null)
4795 else nvl(fpp.CURR_BGT_RAW_COST, 0) +
4796 nvl(fin.CURR_BGT_RAW_COST, 0)
4797 end,
4798 fpp.CURR_BGT_BURDENED_COST
4799 = case when fpp.CURR_BGT_BURDENED_COST is null and
4800 fin.CURR_BGT_BURDENED_COST is null
4801 then to_number(null)
4802 else nvl(fpp.CURR_BGT_BURDENED_COST, 0) +
4803 nvl(fin.CURR_BGT_BURDENED_COST, 0)
4804 end,
4805 fpp.CURR_BGT_LABOR_HRS
4806 = case when fpp.CURR_BGT_LABOR_HRS is null and
4807 fin.CURR_BGT_LABOR_HRS is null
4808 then to_number(null)
4809 else nvl(fpp.CURR_BGT_LABOR_HRS, 0) +
4810 nvl(fin.CURR_BGT_LABOR_HRS, 0)
4811 end,
4812 fpp.ORIG_BGT_REVENUE
4813 = case when fpp.ORIG_BGT_REVENUE is null and
4814 fin.ORIG_BGT_REVENUE is null
4815 then to_number(null)
4816 else nvl(fpp.ORIG_BGT_REVENUE, 0) +
4817 nvl(fin.ORIG_BGT_REVENUE, 0)
4818 end,
4819 fpp.ORIG_BGT_RAW_COST
4820 = case when fpp.ORIG_BGT_RAW_COST is null and
4821 fin.ORIG_BGT_RAW_COST is null
4822 then to_number(null)
4823 else nvl(fpp.ORIG_BGT_RAW_COST, 0) +
4824 nvl(fin.ORIG_BGT_RAW_COST, 0)
4825 end,
4826 fpp.ORIG_BGT_BURDENED_COST
4827 = case when fpp.ORIG_BGT_BURDENED_COST is null and
4828 fin.ORIG_BGT_BURDENED_COST is null
4829 then to_number(null)
4830 else nvl(fpp.ORIG_BGT_BURDENED_COST, 0) +
4834 = case when fpp.ORIG_BGT_LABOR_HRS is null and
4831 nvl(fin.ORIG_BGT_BURDENED_COST, 0)
4832 end,
4833 fpp.ORIG_BGT_LABOR_HRS
4835 fin.ORIG_BGT_LABOR_HRS is null
4836 then to_number(null)
4837 else nvl(fpp.ORIG_BGT_LABOR_HRS, 0) +
4838 nvl(fin.ORIG_BGT_LABOR_HRS, 0)
4839 end,
4840 fpp.FORECAST_REVENUE
4841 = case when fpp.FORECAST_REVENUE is null and
4842 fin.FORECAST_REVENUE is null
4843 then to_number(null)
4844 else nvl(fpp.FORECAST_REVENUE, 0) +
4845 nvl(fin.FORECAST_REVENUE, 0)
4846 end,
4847 fpp.FORECAST_RAW_COST
4848 = case when fpp.FORECAST_RAW_COST is null and
4849 fin.FORECAST_RAW_COST is null
4850 then to_number(null)
4851 else nvl(fpp.FORECAST_RAW_COST, 0) +
4852 nvl(fin.FORECAST_RAW_COST, 0)
4853 end,
4854 fpp.FORECAST_BURDENED_COST
4855 = case when fpp.FORECAST_BURDENED_COST is null and
4856 fin.FORECAST_BURDENED_COST is null
4857 then to_number(null)
4858 else nvl(fpp.FORECAST_BURDENED_COST, 0) +
4859 nvl(fin.FORECAST_BURDENED_COST, 0)
4860 end,
4861 fpp.FORECAST_LABOR_HRS
4862 = case when fpp.FORECAST_LABOR_HRS is null and
4863 fin.FORECAST_LABOR_HRS is null
4864 then to_number(null)
4865 else nvl(fpp.FORECAST_LABOR_HRS, 0) +
4866 nvl(fin.FORECAST_LABOR_HRS, 0)
4867 end,
4868 fpp.LAST_UPDATE_DATE
4869 = fin.LAST_UPDATE_DATE,
4870 fpp.LAST_UPDATED_BY
4871 = fin.LAST_UPDATED_BY,
4872 fpp.LAST_UPDATE_LOGIN
4873 = fin.LAST_UPDATE_LOGIN
4874 when not matched then insert
4875 (
4876 fpp.PROJECT_ORG_ID,
4877 fpp.PROJECT_ORGANIZATION_ID,
4878 fpp.TIME_ID,
4879 fpp.PROJECT_ID,
4880 fpp.PERIOD_TYPE_ID,
4881 fpp.CALENDAR_TYPE,
4882 fpp.CURR_RECORD_TYPE_ID,
4883 fpp.CURRENCY_CODE,
4884 fpp.PROJECT_TYPE_CLASS,
4885 fpp.REVENUE,
4886 fpp.LABOR_REVENUE,
4887 fpp.RAW_COST,
4888 fpp.BURDENED_COST,
4889 fpp.BILL_RAW_COST,
4890 fpp.BILL_BURDENED_COST,
4891 fpp.CAPITALIZABLE_RAW_COST,
4892 fpp.CAPITALIZABLE_BRDN_COST,
4893 fpp.LABOR_RAW_COST,
4894 fpp.LABOR_BURDENED_COST,
4895 fpp.BILL_LABOR_RAW_COST,
4896 fpp.BILL_LABOR_BURDENED_COST,
4897 fpp.REVENUE_WRITEOFF,
4898 fpp.LABOR_HRS,
4899 fpp.BILL_LABOR_HRS,
4900 fpp.CURR_BGT_REVENUE,
4901 fpp.CURR_BGT_RAW_COST,
4902 fpp.CURR_BGT_BURDENED_COST,
4903 fpp.CURR_BGT_LABOR_HRS,
4904 fpp.ORIG_BGT_REVENUE,
4905 fpp.ORIG_BGT_RAW_COST,
4906 fpp.ORIG_BGT_BURDENED_COST,
4907 fpp.ORIG_BGT_LABOR_HRS,
4908 fpp.FORECAST_REVENUE,
4909 fpp.FORECAST_RAW_COST,
4910 fpp.FORECAST_BURDENED_COST,
4911 fpp.FORECAST_LABOR_HRS,
4912 fpp.LAST_UPDATE_DATE,
4913 fpp.LAST_UPDATED_BY,
4914 fpp.CREATION_DATE,
4915 fpp.CREATED_BY,
4916 fpp.LAST_UPDATE_LOGIN
4917 )
4918 values
4919 (
4920 fin.PROJECT_ORG_ID,
4921 fin.PROJECT_ORGANIZATION_ID,
4922 fin.TIME_ID,
4923 fin.PROJECT_ID,
4924 fin.PERIOD_TYPE_ID,
4925 fin.CALENDAR_TYPE,
4926 fin.CURR_RECORD_TYPE_ID,
4927 fin.CURRENCY_CODE,
4928 fin.PROJECT_TYPE_CLASS,
4929 fin.REVENUE,
4930 fin.LABOR_REVENUE,
4931 fin.RAW_COST,
4932 fin.BURDENED_COST,
4933 fin.BILL_RAW_COST,
4934 fin.BILL_BURDENED_COST,
4935 fin.CAPITALIZABLE_RAW_COST,
4936 fin.CAPITALIZABLE_BRDN_COST,
4937 fin.LABOR_RAW_COST,
4938 fin.LABOR_BURDENED_COST,
4939 fin.BILL_LABOR_RAW_COST,
4940 fin.BILL_LABOR_BURDENED_COST,
4941 fin.REVENUE_WRITEOFF,
4942 fin.LABOR_HRS,
4943 fin.BILL_LABOR_HRS,
4944 fin.CURR_BGT_REVENUE,
4945 fin.CURR_BGT_RAW_COST,
4946 fin.CURR_BGT_BURDENED_COST,
4947 fin.CURR_BGT_LABOR_HRS,
4948 fin.ORIG_BGT_REVENUE,
4949 fin.ORIG_BGT_RAW_COST,
4950 fin.ORIG_BGT_BURDENED_COST,
4951 fin.ORIG_BGT_LABOR_HRS,
4952 fin.FORECAST_REVENUE,
4953 fin.FORECAST_RAW_COST,
4954 fin.FORECAST_BURDENED_COST,
4955 fin.FORECAST_LABOR_HRS,
4956 fin.LAST_UPDATE_DATE,
4957 fin.LAST_UPDATED_BY,
4958 fin.CREATION_DATE,
4959 fin.CREATED_BY,
4960 fin.LAST_UPDATE_LOGIN
4961 );
4962
4963 end if;
4964
4965 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
4966 (
4967 l_process,
4968 'PJI_FM_SUM_ROLLUP_FIN.MERGE_FIN_INTO_FPP(p_worker_id);'
4969 );
4970
4971 -- truncate intermediate tables no longer required
4975 commit;
4972 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
4973 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema , 'PJI_FM_AGGR_FIN3' , 'NORMAL',null);
4974
4976
4977 end MERGE_FIN_INTO_FPP;
4978
4979
4980 -- -----------------------------------------------------
4981 -- procedure PROJECT_ORGANIZATION
4982 -- -----------------------------------------------------
4983 procedure PROJECT_ORGANIZATION (p_worker_id in number) is
4984
4985 l_process varchar2(30);
4986
4987 CURSOR update_scope(c_worker_id number)
4988 IS
4989 SELECT
4990 map.PROJECT_ID
4991 , map.NEW_PROJECT_ORGANIZATION_ID
4992 FROM PJI_PJI_PROJ_BATCH_MAP map
4993 WHERE 1=1
4994 AND map.WORKER_ID = c_worker_id
4995 AND map.PROJECT_ORGANIZATION_ID <> map.NEW_PROJECT_ORGANIZATION_ID
4996 ;
4997
4998 /*
4999 * Define PL/SQL Table for storing values.
5000 */
5001 L_NEW_ORGZ_TAB PA_PLSQL_DATATYPES.IdTabTyp;
5002 L_PROJECT_TAB PA_PLSQL_DATATYPES.IdTabTyp;
5003
5004 /*
5005 * Define other variable to be used in this procedure
5006 */
5007 l_this_fetch NUMBER:=0;
5008 l_totally_fetched NUMBER:=0;
5009 l_last_fetch VARCHAR2(1):='N';
5010 I PLS_INTEGER;
5011
5012 begin
5013
5014 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
5015
5016 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
5017 (
5018 l_process,
5019 'PJI_FM_SUM_ROLLUP_FIN.PROJECT_ORGANIZATION(p_worker_id);'
5020 )) then
5021 return;
5022 end if;
5023
5024 IF update_scope%ISOPEN then
5025 CLOSE update_scope;
5026 END IF;
5027 OPEN update_scope(p_worker_id);
5028
5029 LOOP
5030 /*
5031 * Clear all PL/SQL table.
5032 */
5033 L_NEW_ORGZ_TAB.delete;
5034 L_PROJECT_TAB.delete;
5035
5036 /*
5037 * Fetch 1000 records at a time.
5038 */
5039 FETCH update_scope BULK COLLECT
5040 INTO
5041 L_PROJECT_TAB
5042 , L_NEW_ORGZ_TAB LIMIT 1000;
5043
5044 /*
5045 * To check the rows fetched in this fetch
5046 */
5047 l_this_fetch := update_scope%ROWCOUNT - l_totally_fetched;
5048 l_totally_fetched := update_scope%ROWCOUNT;
5049
5050 /*
5051 * Check if this fetch has 0 rows returned (ie last fetch was
5052 * even 1000)
5053 * This could happen in 2 cases
5054 * 1) this fetch is the very first fetch with 0 rows returned
5055 * OR 2) the last fetch returned an even 1000 rows
5056 * If either then EXIT without any processing
5057 */
5058 IF l_this_fetch = 0 then
5059 EXIT;
5060 END IF;
5061
5062 /*
5063 * Check if this fetch is the last fetch
5064 * If so then set the flag l_last_fetch so as to exit after
5065 * processing
5066 */
5067 IF l_this_fetch < 1000 then
5068 l_last_fetch := 'Y';
5069 ELSE
5070 l_last_fetch := 'N';
5071 END IF;
5072
5073 FORALL I in L_PROJECT_TAB.FIRST..L_PROJECT_TAB.LAST
5074 Update PJI_FP_PROJ_ET_WT_F
5075 Set PROJECT_ORGANIZATION_ID = L_NEW_ORGZ_TAB(I)
5076 Where PROJECT_ID = L_PROJECT_TAB(I)
5077 ;
5078
5079 FORALL I in L_PROJECT_TAB.FIRST..L_PROJECT_TAB.LAST
5080 Update PJI_FP_PROJ_ET_F
5081 Set PROJECT_ORGANIZATION_ID = L_NEW_ORGZ_TAB(I)
5082 Where PROJECT_ID = L_PROJECT_TAB(I)
5083 ;
5084
5085 FORALL I in L_PROJECT_TAB.FIRST..L_PROJECT_TAB.LAST
5086 Update PJI_FP_PROJ_F
5087 Set PROJECT_ORGANIZATION_ID = L_NEW_ORGZ_TAB(I)
5088 Where PROJECT_ID = L_PROJECT_TAB(I)
5089 ;
5090
5091 /*
5092 * Check if this loop is the last set of 100
5093 * If so then EXIT;
5094 */
5095 IF l_last_fetch='Y' THEN
5096 EXIT;
5097 END IF;
5098
5099 END LOOP;
5100
5101 CLOSE update_scope;
5102
5103
5104 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
5105 (
5106 l_process,
5107 'PJI_FM_SUM_ROLLUP_FIN.PROJECT_ORGANIZATION(p_worker_id);'
5108 );
5109
5110 commit;
5111
5112 end PROJECT_ORGANIZATION;
5113
5114
5115 -- -----------------------------------------------------
5116 -- procedure REFRESH_MVIEW_FWO
5117 -- -----------------------------------------------------
5118 procedure REFRESH_MVIEW_FWO (p_worker_id in number) is
5119
5120 l_process varchar2(30);
5121 l_extraction_type varchar2(30);
5122 l_pji_schema varchar2(30);
5123 l_apps_schema varchar2(30);
5124 l_p_degree number := 0;
5125 l_params_util_flag varchar2(1);
5126
5127 l_errbuf varchar2(255);
5128 l_retcode varchar2(255);
5129
5130 begin
5131
5132 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
5133
5137 'PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FWO(p_worker_id);'
5134 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
5135 (
5136 l_process,
5138 )) then
5139 return;
5140 end if;
5141
5142 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
5143 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
5144
5145 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
5146 l_extraction_type <> 'PARTIAL') then
5147 return;
5148 end if;
5149
5150 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
5151 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
5152 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
5153 if (l_p_degree = 1) then
5154 l_p_degree := 0;
5155 end if;
5156
5157 /*
5158 l_params_util_flag :=
5159 nvl(PJI_UTILS.GET_PARAMETER('CONFIG_UTIL_FLAG'),
5160 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_FM_SUM_MAIN.g_top_process,
5161 'CONFIG_UTIL_FLAG'), 'N'));
5162
5163 if (l_params_util_flag = 'N') then
5164 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
5165 TABNAME => 'PJI_ORG_DENORM',
5166 PERCENT => 10,
5167 DEGREE => l_p_degree);
5168 end if;
5169 */
5170
5171 if (l_extraction_type = 'FULL') then
5172 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5173 l_retcode,
5174 'PJI_FP_ORG_ET_WT_F_MV',
5175 'C',
5176 'N');
5177 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5178 l_retcode,
5179 'PJI_FP_ORGO_ET_WT_F_MV',
5180 'C',
5181 'N');
5182 else
5183 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
5184 TABNAME => 'MLOG$_PJI_FP_PROJ_ET_WT_F',
5185 PERCENT => 10,
5186 DEGREE => l_p_degree);
5187 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5188 l_retcode,
5189 'PJI_FP_ORG_ET_WT_F_MV',
5190 'F',
5191 'N');
5192 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_apps_schema,
5193 TABNAME => 'MLOG$_PJI_FP_ORG_ET_WT_F_M',
5194 PERCENT => 10,
5195 DEGREE => l_p_degree);
5196 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5197 l_retcode,
5198 'PJI_FP_ORGO_ET_WT_F_MV',
5199 'F',
5200 'N');
5201 end if;
5202
5203 if (l_extraction_type <> 'INCREMENTAL') then
5204 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5205 tabname => 'PJI_FP_ORG_ET_WT_F_MV',
5206 percent => 10,
5207 degree => l_p_degree);
5208 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5209 tabname => 'PJI_FP_ORGO_ET_WT_F_MV',
5210 percent => 10,
5211 degree => l_p_degree);
5212 end if;
5213
5214 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
5215 (
5216 l_process,
5217 'PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FWO(p_worker_id);'
5218 );
5219
5220 commit;
5221
5222 end REFRESH_MVIEW_FWO;
5223
5224
5225 -- -----------------------------------------------------
5226 -- procedure REFRESH_MVIEW_FWC
5227 -- -----------------------------------------------------
5228 procedure REFRESH_MVIEW_FWC (p_worker_id in number) is
5229
5230 l_process varchar2(30);
5231 l_extraction_type varchar2(30);
5232 l_pji_schema varchar2(30);
5233 l_apps_schema varchar2(30);
5234 l_p_degree number := 0;
5235
5236 l_errbuf varchar2(255);
5237 l_retcode varchar2(255);
5238
5239 begin
5240
5241 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
5242
5243 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
5244 (
5245 l_process,
5246 'PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FWC(p_worker_id);'
5247 )) then
5248 return;
5249 end if;
5250
5251 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
5252 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
5253
5254 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
5255 l_extraction_type <> 'PARTIAL') then
5256 return;
5257 end if;
5258
5259 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
5260 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
5261 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
5262 if (l_p_degree = 1) then
5263 l_p_degree := 0;
5264 end if;
5265
5266 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
5267 TABNAME => 'PJI_PROJECT_CLASSES',
5268 PERCENT => 10,
5269 DEGREE => l_p_degree);
5270
5271 if (l_extraction_type = 'FULL') then
5272 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5273 l_retcode,
5274 'PJI_FP_CLS_ET_WT_F_MV',
5275 'C',
5276 'N');
5280 'C',
5277 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5278 l_retcode,
5279 'PJI_FP_CLSO_ET_WT_F_MV',
5281 'N');
5282 else
5283 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5284 l_retcode,
5285 'PJI_FP_CLS_ET_WT_F_MV',
5286 'F',
5287 'N');
5288 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_apps_schema,
5289 TABNAME => 'MLOG$_PJI_FP_CLS_ET_WT_F_M',
5290 PERCENT => 10,
5291 DEGREE => l_p_degree);
5292 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5293 l_retcode,
5294 'PJI_FP_CLSO_ET_WT_F_MV',
5295 'F',
5296 'N');
5297 end if;
5298
5299 if (l_extraction_type <> 'INCREMENTAL') then
5300 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5301 tabname => 'PJI_FP_CLS_ET_WT_F_MV',
5302 percent => 10,
5303 degree => l_p_degree);
5304 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5305 tabname => 'PJI_FP_CLSO_ET_WT_F_MV',
5306 percent => 10,
5307 degree => l_p_degree);
5308 end if;
5309
5310 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
5311 (
5312 l_process,
5313 'PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FWC(p_worker_id);'
5314 );
5315
5316 commit;
5317
5318 end REFRESH_MVIEW_FWC;
5319
5320
5321 -- -----------------------------------------------------
5322 -- procedure REFRESH_MVIEW_FEO
5323 -- -----------------------------------------------------
5324 procedure REFRESH_MVIEW_FEO (p_worker_id in number) is
5325
5326 l_process varchar2(30);
5327 l_extraction_type varchar2(30);
5328 l_pji_schema varchar2(30);
5329 l_apps_schema varchar2(30);
5330 l_p_degree number := 0;
5331
5332 l_errbuf varchar2(255);
5333 l_retcode varchar2(255);
5334
5335 begin
5336
5337 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
5338
5339 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
5340 (
5341 l_process,
5342 'PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FEO(p_worker_id);'
5343 )) then
5344 return;
5345 end if;
5346
5347 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
5348 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
5349
5350 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
5351 l_extraction_type <> 'PARTIAL') then
5352 return;
5353 end if;
5354
5355 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
5356 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
5357 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
5358 if (l_p_degree = 1) then
5359 l_p_degree := 0;
5360 end if;
5361
5362 if (l_extraction_type = 'FULL') then
5363 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5364 l_retcode,
5365 'PJI_FP_ORG_ET_F_MV',
5366 'C',
5367 'N');
5368 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5369 l_retcode,
5370 'PJI_FP_ORGO_ET_F_MV',
5371 'C',
5372 'N');
5373 else
5374 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
5375 TABNAME => 'MLOG$_PJI_FP_PROJ_ET_F',
5376 PERCENT => 10,
5377 DEGREE => l_p_degree);
5378 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5379 l_retcode,
5380 'PJI_FP_ORG_ET_F_MV',
5381 'F',
5382 'N');
5383 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_apps_schema,
5384 TABNAME => 'MLOG$_PJI_FP_ORG_ET_F_MV',
5385 PERCENT => 10,
5386 DEGREE => l_p_degree);
5387 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5388 l_retcode,
5389 'PJI_FP_ORGO_ET_F_MV',
5390 'F',
5391 'N');
5392 end if;
5393
5394 if (l_extraction_type <> 'INCREMENTAL') then
5395 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5396 tabname => 'PJI_FP_ORG_ET_F_MV',
5397 percent => 10,
5398 degree => l_p_degree);
5399 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5400 tabname => 'PJI_FP_ORGO_ET_F_MV',
5401 percent => 10,
5402 degree => l_p_degree);
5403 end if;
5404
5405 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
5406 (
5407 l_process,
5408 'PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FEO(p_worker_id);'
5409 );
5410
5411 commit;
5412
5413 end REFRESH_MVIEW_FEO;
5414
5415
5416 -- -----------------------------------------------------
5417 -- procedure REFRESH_MVIEW_FEC
5421 l_process varchar2(30);
5418 -- -----------------------------------------------------
5419 procedure REFRESH_MVIEW_FEC (p_worker_id in number) is
5420
5422 l_extraction_type varchar2(30);
5423 l_apps_schema varchar2(30);
5424 l_p_degree number := 0;
5425
5426 l_errbuf varchar2(255);
5427 l_retcode varchar2(255);
5428
5429 begin
5430
5431 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
5432
5433 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
5434 (
5435 l_process,
5436 'PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FEC(p_worker_id);'
5437 )) then
5438 return;
5439 end if;
5440
5441 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
5442 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
5443
5444 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
5445 l_extraction_type <> 'PARTIAL') then
5446 return;
5447 end if;
5448
5449 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
5450 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
5451 if (l_p_degree = 1) then
5452 l_p_degree := 0;
5453 end if;
5454
5455 if (l_extraction_type = 'FULL') then
5456 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5457 l_retcode,
5458 'PJI_FP_CLS_ET_F_MV',
5459 'C',
5460 'N');
5461 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5462 l_retcode,
5463 'PJI_FP_CLSO_ET_F_MV',
5464 'C',
5465 'N');
5466 else
5467 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5468 l_retcode,
5469 'PJI_FP_CLS_ET_F_MV',
5470 'F',
5471 'N');
5472 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_apps_schema,
5473 TABNAME => 'MLOG$_PJI_FP_CLS_ET_F_MV',
5474 PERCENT => 10,
5475 DEGREE => l_p_degree);
5476 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5477 l_retcode,
5478 'PJI_FP_CLSO_ET_F_MV',
5479 'F',
5480 'N');
5481 end if;
5482
5483 if (l_extraction_type <> 'INCREMENTAL') then
5484 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5485 tabname => 'PJI_FP_CLS_ET_F_MV',
5486 percent => 10,
5487 degree => l_p_degree);
5488
5489 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5490 tabname => 'PJI_FP_CLSO_ET_F_MV',
5491 percent => 10,
5492 degree => l_p_degree);
5493 end if;
5494
5495 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
5496 (
5497 l_process,
5498 'PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FEC(p_worker_id);'
5499 );
5500
5501 commit;
5502
5503 end REFRESH_MVIEW_FEC;
5504
5505
5506 -- -----------------------------------------------------
5507 -- procedure REFRESH_MVIEW_FPO
5508 -- -----------------------------------------------------
5509 procedure REFRESH_MVIEW_FPO (p_worker_id in number) is
5510
5511 l_process varchar2(30);
5512 l_extraction_type varchar2(30);
5513 l_pji_schema varchar2(30);
5514 l_apps_schema varchar2(30);
5515 l_p_degree number := 0;
5516
5517 l_errbuf varchar2(255);
5518 l_retcode varchar2(255);
5519
5520 begin
5521
5522 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
5523
5524 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
5525 (
5526 l_process,
5527 'PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FPO(p_worker_id);'
5528 )) then
5529 return;
5530 end if;
5531
5532 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
5533 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
5534
5535 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
5536 l_extraction_type <> 'PARTIAL') then
5537 return;
5538 end if;
5539
5540 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
5541 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
5542 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
5543 if (l_p_degree = 1) then
5544 l_p_degree := 0;
5545 end if;
5546
5547 if (l_extraction_type = 'FULL') then
5548 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5549 l_retcode,
5550 'PJI_FP_ORG_F_MV',
5551 'C',
5552 'N');
5553 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5554 l_retcode,
5555 'PJI_FP_ORGO_F_MV',
5556 'C',
5557 'N');
5558 else
5559 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
5560 TABNAME => 'MLOG$_PJI_FP_PROJ_F',
5561 PERCENT => 10,
5562 DEGREE => l_p_degree);
5563 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5564 l_retcode,
5568 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_apps_schema,
5565 'PJI_FP_ORG_F_MV',
5566 'F',
5567 'N');
5569 TABNAME => 'MLOG$_PJI_FP_ORG_F_MV',
5570 PERCENT => 10,
5571 DEGREE => l_p_degree);
5572 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5573 l_retcode,
5574 'PJI_FP_ORGO_F_MV',
5575 'F',
5576 'N');
5577 end if;
5578
5579 if (l_extraction_type <> 'INCREMENTAL') then
5580 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5581 tabname => 'PJI_FP_ORG_F_MV',
5582 percent => 10,
5583 degree => l_p_degree);
5584 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5585 tabname => 'PJI_FP_ORGO_F_MV',
5586 percent => 10,
5587 degree => l_p_degree);
5588 end if;
5589
5590 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
5591 (
5592 l_process,
5593 'PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FPO(p_worker_id);'
5594 );
5595
5596 commit;
5597
5598 end REFRESH_MVIEW_FPO;
5599
5600
5601 -- -----------------------------------------------------
5602 -- procedure REFRESH_MVIEW_FPC
5603 -- -----------------------------------------------------
5604 procedure REFRESH_MVIEW_FPC (p_worker_id in number) is
5605
5606 l_process varchar2(30);
5607 l_extraction_type varchar2(30);
5608 l_apps_schema varchar2(30);
5609 l_p_degree number := 0;
5610
5611 l_errbuf varchar2(255);
5612 l_retcode varchar2(255);
5613
5614 begin
5615
5616 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
5617
5618 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
5619 (
5620 l_process,
5621 'PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FPC(p_worker_id);'
5622 )) then
5623 return;
5624 end if;
5625
5626 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
5627 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
5628
5629 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
5630 l_extraction_type <> 'PARTIAL') then
5631 return;
5632 end if;
5633
5634 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
5635 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
5636 if (l_p_degree = 1) then
5637 l_p_degree := 0;
5638 end if;
5639
5640 if (l_extraction_type = 'FULL') then
5641 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5642 l_retcode,
5643 'PJI_FP_CLS_F_MV',
5644 'C',
5645 'N');
5646 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5647 l_retcode,
5648 'PJI_FP_CLSO_F_MV',
5649 'C',
5650 'N');
5651 else
5652 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5653 l_retcode,
5654 'PJI_FP_CLS_F_MV',
5655 'F',
5656 'N');
5657 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_apps_schema,
5658 TABNAME => 'MLOG$_PJI_FP_CLS_F_MV',
5659 PERCENT => 10,
5660 DEGREE => l_p_degree);
5661 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
5662 l_retcode,
5663 'PJI_FP_CLSO_F_MV',
5664 'F',
5665 'N');
5666 end if;
5667
5668 if (l_extraction_type <> 'INCREMENTAL') then
5669 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5670 tabname => 'PJI_FP_CLS_F_MV',
5671 percent => 10,
5672 degree => l_p_degree);
5673 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
5674 tabname => 'PJI_FP_CLSO_F_MV',
5675 percent => 10,
5676 degree => l_p_degree);
5677 end if;
5678
5679 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
5680 (
5681 l_process,
5682 'PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FPC(p_worker_id);'
5683 );
5684
5685 commit;
5686
5687 end REFRESH_MVIEW_FPC;
5688
5689 end PJI_FM_SUM_ROLLUP_FIN;