[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_SUM_ROLLUP_ACT
Source
1 package body PJI_FM_SUM_ROLLUP_ACT as
2 /* $Header: PJISF05B.pls 120.7 2006/04/18 20:08:27 appldev noship $ */
3
4 -- -----------------------------------------------------
5 -- procedure ACT_ROWID_TABLE
6 -- -----------------------------------------------------
7 procedure ACT_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_ACT.ACT_ROWID_TABLE(p_worker_id);')) then
17 return;
18 end if;
19
20 insert /*+ append parallel(act_i) */ into PJI_PJI_RMAP_ACT act_i
21 (
22 WORKER_ID,
23 STG_ROWID
24 )
25 select
26 p_worker_id WORKER_ID,
27 act5.ROWID STG_ROWID
28 from
29 PJI_PJI_PROJ_BATCH_MAP map,
30 PJI_FM_AGGR_ACT5 act5
31 where
32 map.WORKER_ID = p_worker_id and
33 act5.PROJECT_ID = map.PROJECT_ID;
34
35 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_ACT.ACT_ROWID_TABLE(p_worker_id);');
36
37 commit;
38
39 end ACT_ROWID_TABLE;
40
41
42 -- -----------------------------------------------------
43 -- procedure AGGREGATE_ACT_SLICES
44 -- -----------------------------------------------------
45 procedure AGGREGATE_ACT_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 l_pa_calendar_flag varchar2(1);
57
58 begin
59
60 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
61
62 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_ACT.AGGREGATE_ACT_SLICES(p_worker_id);')) then
63 return;
64 end if;
65
66 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
67 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
68
69 select
70 TXN_CURR_FLAG,
71 GLOBAL_CURR2_FLAG
72 into
73 l_txn_currency_flag,
74 l_g2_currency_flag
75 from
76 PJI_SYSTEM_SETTINGS;
77
78 l_g1_currency_code := PJI_UTILS.GET_GLOBAL_PRIMARY_CURRENCY;
79 l_g2_currency_code := PJI_UTILS.GET_GLOBAL_SECONDARY_CURRENCY;
80
81 l_pa_calendar_flag := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
82 (
83 PJI_RM_SUM_MAIN.g_process,
84 'PA_CALENDAR_FLAG'
85 );
86
87 insert /*+ append parallel(act3_i) */ into PJI_FM_AGGR_ACT3 act3_i
88 (
89 WORKER_ID,
90 PROJECT_ID,
91 PROJECT_ORG_ID,
92 PROJECT_ORGANIZATION_ID,
93 TIME_ID,
94 PERIOD_TYPE_ID,
95 CALENDAR_TYPE,
96 GL_CALENDAR_ID,
97 PA_CALENDAR_ID,
98 CURR_RECORD_TYPE_ID,
99 CURRENCY_CODE,
100 REVENUE,
101 FUNDING,
102 INITIAL_FUNDING_AMOUNT,
103 INITIAL_FUNDING_COUNT,
104 ADDITIONAL_FUNDING_AMOUNT,
105 ADDITIONAL_FUNDING_COUNT,
106 CANCELLED_FUNDING_AMOUNT,
107 CANCELLED_FUNDING_COUNT,
108 FUNDING_ADJUSTMENT_AMOUNT,
109 FUNDING_ADJUSTMENT_COUNT,
110 REVENUE_WRITEOFF,
111 AR_INVOICE_AMOUNT,
112 AR_INVOICE_COUNT,
113 AR_CASH_APPLIED_AMOUNT,
114 AR_CASH_APPLIED_COUNT,
115 AR_INVOICE_WRITEOFF_AMOUNT,
116 AR_INVOICE_WRITEOFF_COUNT,
117 AR_CREDIT_MEMO_AMOUNT,
118 AR_CREDIT_MEMO_COUNT,
119 UNBILLED_RECEIVABLES,
120 UNEARNED_REVENUE,
121 AR_UNAPPR_INVOICE_AMOUNT,
122 AR_UNAPPR_INVOICE_COUNT,
123 AR_APPR_INVOICE_AMOUNT,
124 AR_APPR_INVOICE_COUNT,
125 AR_AMOUNT_DUE,
126 AR_COUNT_DUE,
127 AR_AMOUNT_OVERDUE,
128 AR_COUNT_OVERDUE,
129 DORMANT_BACKLOG_INACTIV,
130 DORMANT_BACKLOG_START,
131 LOST_BACKLOG,
132 ACTIVE_BACKLOG,
133 REVENUE_AT_RISK
134 )
135 select
136 src4.WORKER_ID,
137 src4.PROJECT_ID,
138 src4.PROJECT_ORG_ID,
139 src4.PROJECT_ORGANIZATION_ID,
140 src4.TIME_ID,
141 src4.PERIOD_TYPE_ID,
142 src4.CALENDAR_TYPE,
143 src4.GL_CALENDAR_ID,
144 src4.PA_CALENDAR_ID,
145 src4.CURR_RECORD_TYPE_ID,
146 src4.CURRENCY_CODE,
147 sum(src4.REVENUE) REVENUE,
148 sum(src4.FUNDING) FUNDING,
149 sum(src4.INITIAL_FUNDING_AMOUNT) INITIAL_FUNDING_AMOUNT,
150 sum(src4.INITIAL_FUNDING_COUNT) INITIAL_FUNDING_COUNT,
151 sum(src4.ADDITIONAL_FUNDING_AMOUNT) ADDITIONAL_FUNDING_AMOUNT,
152 sum(src4.ADDITIONAL_FUNDING_COUNT) ADDITIONAL_FUNDING_COUNT,
153 sum(src4.CANCELLED_FUNDING_AMOUNT) CANCELLED_FUNDING_AMOUNT,
154 sum(src4.CANCELLED_FUNDING_COUNT) CANCELLED_FUNDING_COUNT,
155 sum(src4.FUNDING_ADJUSTMENT_AMOUNT) FUNDING_ADJUSTMENT_AMOUNT,
156 sum(src4.FUNDING_ADJUSTMENT_COUNT) FUNDING_ADJUSTMENT_COUNT,
157 sum(src4.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
158 sum(src4.AR_INVOICE_AMOUNT) AR_INVOICE_AMOUNT,
159 sum(src4.AR_INVOICE_COUNT) AR_INVOICE_COUNT,
160 sum(src4.AR_CASH_APPLIED_AMOUNT) AR_CASH_APPLIED_AMOUNT,
161 sum(src4.AR_CASH_APPLIED_COUNT) AR_CASH_APPLIED_COUNT,
162 sum(src4.AR_INVOICE_WRITEOFF_AMOUNT) AR_INVOICE_WRITEOFF_AMOUNT,
163 sum(src4.AR_INVOICE_WRITEOFF_COUNT) AR_INVOICE_WRITEOFF_COUNT,
164 sum(src4.AR_CREDIT_MEMO_AMOUNT) AR_CREDIT_MEMO_AMOUNT,
165 sum(src4.AR_CREDIT_MEMO_COUNT) AR_CREDIT_MEMO_COUNT,
166 sum(src4.UNBILLED_RECEIVABLES) UNBILLED_RECEIVABLES,
167 sum(src4.UNEARNED_REVENUE) UNEARNED_REVENUE,
168 sum(src4.AR_UNAPPR_INVOICE_AMOUNT) AR_UNAPPR_INVOICE_AMOUNT,
169 sum(src4.AR_UNAPPR_INVOICE_COUNT) AR_UNAPPR_INVOICE_COUNT,
170 sum(src4.AR_APPR_INVOICE_AMOUNT) AR_APPR_INVOICE_AMOUNT,
171 sum(src4.AR_APPR_INVOICE_COUNT) AR_APPR_INVOICE_COUNT,
172 sum(src4.AR_AMOUNT_DUE) AR_AMOUNT_DUE,
173 sum(src4.AR_COUNT_DUE) AR_COUNT_DUE,
174 sum(src4.AR_AMOUNT_OVERDUE) AR_AMOUNT_OVERDUE,
175 sum(src4.AR_COUNT_OVERDUE) AR_COUNT_OVERDUE,
176 sum(src4.DORMANT_BACKLOG_INACTIV) DORMANT_BACKLOG_INACTIV,
177 sum(src4.DORMANT_BACKLOG_START) DORMANT_BACKLOG_START,
178 sum(src4.LOST_BACKLOG) LOST_BACKLOG,
179 sum(src4.ACTIVE_BACKLOG) ACTIVE_BACKLOG,
180 sum(src4.REVENUE_AT_RISK) REVENUE_AT_RISK
181 from
182 (
183 select
184 src3.WORKER_ID,
185 src3.PROJECT_ID,
186 src3.PROJECT_ORG_ID,
187 src3.PROJECT_ORGANIZATION_ID,
188 src3.TIME_ID,
189 src3.PERIOD_TYPE_ID,
190 src3.CALENDAR_TYPE,
191 src3.GL_CALENDAR_ID,
192 src3.PA_CALENDAR_ID,
193 sum(src3.CURR_RECORD_TYPE_ID) CURR_RECORD_TYPE_ID,
194 nvl(src3.CURRENCY_CODE, 'PJI$NULL') CURRENCY_CODE,
195 max(src3.REVENUE) REVENUE,
196 max(src3.FUNDING) FUNDING,
197 max(src3.INITIAL_FUNDING_AMOUNT) INITIAL_FUNDING_AMOUNT,
198 max(src3.INITIAL_FUNDING_COUNT) INITIAL_FUNDING_COUNT,
199 max(src3.ADDITIONAL_FUNDING_AMOUNT) ADDITIONAL_FUNDING_AMOUNT,
200 max(src3.ADDITIONAL_FUNDING_COUNT) ADDITIONAL_FUNDING_COUNT,
201 max(src3.CANCELLED_FUNDING_AMOUNT) CANCELLED_FUNDING_AMOUNT,
202 max(src3.CANCELLED_FUNDING_COUNT) CANCELLED_FUNDING_COUNT,
203 max(src3.FUNDING_ADJUSTMENT_AMOUNT) FUNDING_ADJUSTMENT_AMOUNT,
204 max(src3.FUNDING_ADJUSTMENT_COUNT) FUNDING_ADJUSTMENT_COUNT,
205 max(src3.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
206 max(src3.AR_INVOICE_AMOUNT) AR_INVOICE_AMOUNT,
207 max(src3.AR_INVOICE_COUNT) AR_INVOICE_COUNT,
208 max(src3.AR_CASH_APPLIED_AMOUNT) AR_CASH_APPLIED_AMOUNT,
209 max(src3.AR_CASH_APPLIED_COUNT) AR_CASH_APPLIED_COUNT,
210 max(src3.AR_INVOICE_WRITEOFF_AMOUNT) AR_INVOICE_WRITEOFF_AMOUNT,
211 max(src3.AR_INVOICE_WRITEOFF_COUNT) AR_INVOICE_WRITEOFF_COUNT,
212 max(src3.AR_CREDIT_MEMO_AMOUNT) AR_CREDIT_MEMO_AMOUNT,
213 max(src3.AR_CREDIT_MEMO_COUNT) AR_CREDIT_MEMO_COUNT,
214 max(src3.UNBILLED_RECEIVABLES) UNBILLED_RECEIVABLES,
215 max(src3.UNEARNED_REVENUE) UNEARNED_REVENUE,
216 max(src3.AR_UNAPPR_INVOICE_AMOUNT) AR_UNAPPR_INVOICE_AMOUNT,
217 max(src3.AR_UNAPPR_INVOICE_COUNT) AR_UNAPPR_INVOICE_COUNT,
218 max(src3.AR_APPR_INVOICE_AMOUNT) AR_APPR_INVOICE_AMOUNT,
219 max(src3.AR_APPR_INVOICE_COUNT) AR_APPR_INVOICE_COUNT,
220 max(src3.AR_AMOUNT_DUE) AR_AMOUNT_DUE,
221 max(src3.AR_COUNT_DUE) AR_COUNT_DUE,
222 max(src3.AR_AMOUNT_OVERDUE) AR_AMOUNT_OVERDUE,
223 max(src3.AR_COUNT_OVERDUE) AR_COUNT_OVERDUE,
224 to_number(null) DORMANT_BACKLOG_INACTIV,
225 to_number(null) DORMANT_BACKLOG_START,
226 to_number(null) LOST_BACKLOG,
227 to_number(null) ACTIVE_BACKLOG,
228 to_number(null) REVENUE_AT_RISK
229 from
230 (
231 select /*+ ordered */
232 p_worker_id WORKER_ID,
233 src2.PROJECT_ID,
234 src2.PROJECT_ORG_ID,
235 src2.PROJECT_ORGANIZATION_ID,
236 src2.TIME_ID,
237 1 PERIOD_TYPE_ID,
238 src2.CALENDAR_TYPE,
239 src2.GL_CALENDAR_ID,
240 src2.PA_CALENDAR_ID,
241 invert.INVERT_ID CURR_RECORD_TYPE_ID,
242 decode(invert.INVERT_ID,
243 1, l_g1_currency_code,
244 2, l_g2_currency_code,
245 4, info.PF_CURRENCY_CODE,
246 8, prj.PROJECT_CURRENCY_CODE,
247 16, src2.TXN_CURRENCY_CODE,
248 32, l_g1_currency_code,
249 64, l_g2_currency_code,
250 128, info.PF_CURRENCY_CODE,
251 256, prj.PROJECT_CURRENCY_CODE) DIFF_CURRENCY_CODE,
252 src2.DIFF_ROWNUM DIFF_ROWNUM,
253 decode(invert.INVERT_ID,
254 1, l_g1_currency_code,
255 2, l_g2_currency_code,
256 4, info.PF_CURRENCY_CODE,
257 8, prj.PROJECT_CURRENCY_CODE,
258 16, src2.TXN_CURRENCY_CODE,
259 32, src2.TXN_CURRENCY_CODE,
260 64, src2.TXN_CURRENCY_CODE,
261 128, src2.TXN_CURRENCY_CODE,
262 256, src2.TXN_CURRENCY_CODE) CURRENCY_CODE,
263 decode(invert.INVERT_ID,
264 1, src2.G1_REVENUE,
265 2, src2.G2_REVENUE,
266 4, src2.POU_REVENUE,
267 8, src2.PRJ_REVENUE,
268 16, src2.TXN_REVENUE,
269 32, src2.G1_REVENUE,
270 64, src2.G2_REVENUE,
271 128, src2.POU_REVENUE,
272 256, src2.PRJ_REVENUE) REVENUE,
273 decode(invert.INVERT_ID,
274 1, src2.G1_FUNDING,
275 2, src2.G2_FUNDING,
276 4, src2.POU_FUNDING,
277 8, src2.PRJ_FUNDING,
278 16, src2.TXN_FUNDING,
279 32, src2.G1_FUNDING,
280 64, src2.G2_FUNDING,
281 128, src2.POU_FUNDING,
282 256, src2.PRJ_FUNDING) FUNDING,
283 decode(invert.INVERT_ID,
284 1, src2.G1_INITIAL_FUNDING_AMOUNT,
285 2, src2.G2_INITIAL_FUNDING_AMOUNT,
286 4, src2.POU_INITIAL_FUNDING_AMOUNT,
287 8, src2.PRJ_INITIAL_FUNDING_AMOUNT,
288 16, src2.TXN_INITIAL_FUNDING_AMOUNT,
289 32, src2.G1_INITIAL_FUNDING_AMOUNT,
290 64, src2.G2_INITIAL_FUNDING_AMOUNT,
291 128, src2.POU_INITIAL_FUNDING_AMOUNT,
292 256, src2.PRJ_INITIAL_FUNDING_AMOUNT)
293 INITIAL_FUNDING_AMOUNT,
294 src2.INITIAL_FUNDING_COUNT,
295 decode(invert.INVERT_ID,
296 1, src2.G1_ADDITIONAL_FUNDING_AMOUNT,
297 2, src2.G2_ADDITIONAL_FUNDING_AMOUNT,
298 4, src2.POU_ADDITIONAL_FUNDING_AMOUNT,
299 8, src2.PRJ_ADDITIONAL_FUNDING_AMOUNT,
300 16, src2.TXN_ADDITIONAL_FUNDING_AMOUNT,
301 32, src2.G1_ADDITIONAL_FUNDING_AMOUNT,
302 64, src2.G2_ADDITIONAL_FUNDING_AMOUNT,
303 128, src2.POU_ADDITIONAL_FUNDING_AMOUNT,
304 256, src2.PRJ_ADDITIONAL_FUNDING_AMOUNT)
305 ADDITIONAL_FUNDING_AMOUNT,
306 src2.ADDITIONAL_FUNDING_COUNT,
307 decode(invert.INVERT_ID,
308 1, src2.G1_CANCELLED_FUNDING_AMOUNT,
309 2, src2.G2_CANCELLED_FUNDING_AMOUNT,
310 4, src2.POU_CANCELLED_FUNDING_AMOUNT,
311 8, src2.PRJ_CANCELLED_FUNDING_AMOUNT,
312 16, src2.TXN_CANCELLED_FUNDING_AMOUNT,
313 32, src2.G1_CANCELLED_FUNDING_AMOUNT,
314 64, src2.G2_CANCELLED_FUNDING_AMOUNT,
315 128, src2.POU_CANCELLED_FUNDING_AMOUNT,
316 256, src2.PRJ_CANCELLED_FUNDING_AMOUNT)
317 CANCELLED_FUNDING_AMOUNT,
318 src2.CANCELLED_FUNDING_COUNT,
319 decode(invert.INVERT_ID,
320 1, src2.G1_FUNDING_ADJUSTMENT_AMOUNT,
321 2, src2.G2_FUNDING_ADJUSTMENT_AMOUNT,
322 4, src2.POU_FUNDING_ADJUSTMENT_AMOUNT,
323 8, src2.PRJ_FUNDING_ADJUSTMENT_AMOUNT,
324 16, src2.TXN_FUNDING_ADJUSTMENT_AMOUNT,
325 32, src2.G1_FUNDING_ADJUSTMENT_AMOUNT,
326 64, src2.G2_FUNDING_ADJUSTMENT_AMOUNT,
327 128, src2.POU_FUNDING_ADJUSTMENT_AMOUNT,
328 256, src2.PRJ_FUNDING_ADJUSTMENT_AMOUNT)
329 FUNDING_ADJUSTMENT_AMOUNT,
330 src2.FUNDING_ADJUSTMENT_COUNT,
331 decode(invert.INVERT_ID,
332 1, src2.G1_REVENUE_WRITEOFF,
333 2, src2.G2_REVENUE_WRITEOFF,
334 4, src2.POU_REVENUE_WRITEOFF,
335 8, src2.PRJ_REVENUE_WRITEOFF,
336 16, src2.TXN_REVENUE_WRITEOFF,
337 32, src2.G1_REVENUE_WRITEOFF,
338 64, src2.G2_REVENUE_WRITEOFF,
339 128, src2.POU_REVENUE_WRITEOFF,
340 256, src2.PRJ_REVENUE_WRITEOFF) REVENUE_WRITEOFF,
341 decode(invert.INVERT_ID,
342 1, src2.G1_AR_INVOICE_AMOUNT,
343 2, src2.G2_AR_INVOICE_AMOUNT,
344 4, src2.POU_AR_INVOICE_AMOUNT,
345 8, src2.PRJ_AR_INVOICE_AMOUNT,
346 16, src2.TXN_AR_INVOICE_AMOUNT,
347 32, src2.G1_AR_INVOICE_AMOUNT,
348 64, src2.G2_AR_INVOICE_AMOUNT,
349 128, src2.POU_AR_INVOICE_AMOUNT,
350 256, src2.PRJ_AR_INVOICE_AMOUNT) AR_INVOICE_AMOUNT,
351 src2.AR_INVOICE_COUNT,
352 decode(invert.INVERT_ID,
353 1, src2.G1_AR_CASH_APPLIED_AMOUNT,
354 2, src2.G2_AR_CASH_APPLIED_AMOUNT,
355 4, src2.POU_AR_CASH_APPLIED_AMOUNT,
356 8, src2.PRJ_AR_CASH_APPLIED_AMOUNT,
357 16, src2.TXN_AR_CASH_APPLIED_AMOUNT,
358 32, src2.G1_AR_CASH_APPLIED_AMOUNT,
359 64, src2.G2_AR_CASH_APPLIED_AMOUNT,
360 128, src2.POU_AR_CASH_APPLIED_AMOUNT,
361 256, src2.PRJ_AR_CASH_APPLIED_AMOUNT)
362 AR_CASH_APPLIED_AMOUNT,
363 src2.AR_CASH_APPLIED_COUNT,
364 decode(invert.INVERT_ID,
365 1, src2.G1_AR_INVOICE_WRITEOFF_AMOUNT,
366 2, src2.G2_AR_INVOICE_WRITEOFF_AMOUNT,
367 4, src2.POU_AR_INVOICE_WRITEOFF_AMOUNT,
368 8, src2.PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
369 16, src2.TXN_AR_INVOICE_WRITEOFF_AMOUNT,
370 32, src2.G1_AR_INVOICE_WRITEOFF_AMOUNT,
371 64, src2.G2_AR_INVOICE_WRITEOFF_AMOUNT,
372 128, src2.POU_AR_INVOICE_WRITEOFF_AMOUNT,
373 256, src2.PRJ_AR_INVOICE_WRITEOFF_AMOUNT)
374 AR_INVOICE_WRITEOFF_AMOUNT,
375 src2.AR_INVOICE_WRITEOFF_COUNT,
376 decode(invert.INVERT_ID,
377 1, src2.G1_AR_CREDIT_MEMO_AMOUNT,
378 2, src2.G2_AR_CREDIT_MEMO_AMOUNT,
379 4, src2.POU_AR_CREDIT_MEMO_AMOUNT,
380 8, src2.PRJ_AR_CREDIT_MEMO_AMOUNT,
381 16, src2.TXN_AR_CREDIT_MEMO_AMOUNT,
382 32, src2.G1_AR_CREDIT_MEMO_AMOUNT,
383 64, src2.G2_AR_CREDIT_MEMO_AMOUNT,
384 128, src2.POU_AR_CREDIT_MEMO_AMOUNT,
385 256, src2.PRJ_AR_CREDIT_MEMO_AMOUNT)
386 AR_CREDIT_MEMO_AMOUNT,
387 src2.AR_CREDIT_MEMO_COUNT,
388 decode(invert.INVERT_ID,
389 1, src2.G1_UNBILLED_RECEIVABLES,
390 2, src2.G2_UNBILLED_RECEIVABLES,
391 4, src2.POU_UNBILLED_RECEIVABLES,
392 8, src2.PRJ_UNBILLED_RECEIVABLES,
393 16, src2.TXN_UNBILLED_RECEIVABLES,
394 32, src2.G1_UNBILLED_RECEIVABLES,
395 64, src2.G2_UNBILLED_RECEIVABLES,
396 128, src2.POU_UNBILLED_RECEIVABLES,
397 256, src2.PRJ_UNBILLED_RECEIVABLES)UNBILLED_RECEIVABLES,
398 decode(invert.INVERT_ID,
399 1, src2.G1_UNEARNED_REVENUE,
400 2, src2.G2_UNEARNED_REVENUE,
401 4, src2.POU_UNEARNED_REVENUE,
402 8, src2.PRJ_UNEARNED_REVENUE,
403 16, src2.TXN_UNEARNED_REVENUE,
404 32, src2.G1_UNEARNED_REVENUE,
405 64, src2.G2_UNEARNED_REVENUE,
406 128, src2.POU_UNEARNED_REVENUE,
407 256, src2.PRJ_UNEARNED_REVENUE) UNEARNED_REVENUE,
408 decode(invert.INVERT_ID,
409 1, src2.G1_AR_UNAPPR_INVOICE_AMOUNT,
410 2, src2.G2_AR_UNAPPR_INVOICE_AMOUNT,
411 4, src2.POU_AR_UNAPPR_INVOICE_AMOUNT,
412 8, src2.PRJ_AR_UNAPPR_INVOICE_AMOUNT,
413 16, src2.TXN_AR_UNAPPR_INVOICE_AMOUNT,
414 32, src2.G1_AR_UNAPPR_INVOICE_AMOUNT,
415 64, src2.G2_AR_UNAPPR_INVOICE_AMOUNT,
416 128, src2.POU_AR_UNAPPR_INVOICE_AMOUNT,
417 256, src2.PRJ_AR_UNAPPR_INVOICE_AMOUNT)
418 AR_UNAPPR_INVOICE_AMOUNT,
419 src2.AR_UNAPPR_INVOICE_COUNT,
420 decode(invert.INVERT_ID,
421 1, src2.G1_AR_APPR_INVOICE_AMOUNT,
422 2, src2.G2_AR_APPR_INVOICE_AMOUNT,
423 4, src2.POU_AR_APPR_INVOICE_AMOUNT,
424 8, src2.PRJ_AR_APPR_INVOICE_AMOUNT,
425 16, src2.TXN_AR_APPR_INVOICE_AMOUNT,
426 32, src2.G1_AR_APPR_INVOICE_AMOUNT,
427 64, src2.G2_AR_APPR_INVOICE_AMOUNT,
428 128, src2.POU_AR_APPR_INVOICE_AMOUNT,
429 256, src2.PRJ_AR_APPR_INVOICE_AMOUNT)
430 AR_APPR_INVOICE_AMOUNT,
431 src2.AR_APPR_INVOICE_COUNT,
432 decode(invert.INVERT_ID,
433 1, src2.G1_AR_AMOUNT_DUE,
434 2, src2.G2_AR_AMOUNT_DUE,
435 4, src2.POU_AR_AMOUNT_DUE,
436 8, src2.PRJ_AR_AMOUNT_DUE,
437 16, src2.TXN_AR_AMOUNT_DUE,
438 32, src2.G1_AR_AMOUNT_DUE,
439 64, src2.G2_AR_AMOUNT_DUE,
440 128, src2.POU_AR_AMOUNT_DUE,
441 256, src2.PRJ_AR_AMOUNT_DUE) AR_AMOUNT_DUE,
442 src2.AR_COUNT_DUE,
443 decode(invert.INVERT_ID,
444 1, src2.G1_AR_AMOUNT_OVERDUE,
445 2, src2.G2_AR_AMOUNT_OVERDUE,
446 4, src2.POU_AR_AMOUNT_OVERDUE,
447 8, src2.PRJ_AR_AMOUNT_OVERDUE,
448 16, src2.TXN_AR_AMOUNT_OVERDUE,
449 32, src2.G1_AR_AMOUNT_OVERDUE,
450 64, src2.G2_AR_AMOUNT_OVERDUE,
451 128, src2.POU_AR_AMOUNT_OVERDUE,
452 256, src2.PRJ_AR_AMOUNT_OVERDUE) AR_AMOUNT_OVERDUE,
453 src2.AR_COUNT_OVERDUE
454 from
455 (
456 select
457 ROWNUM DIFF_ROWNUM,
458 src1.PROJECT_ID,
459 src1.PROJECT_ORG_ID,
460 src1.PROJECT_ORGANIZATION_ID,
461 src1.TIME_ID,
462 src1.CALENDAR_TYPE,
463 src1.GL_CALENDAR_ID,
464 src1.PA_CALENDAR_ID,
465 src1.TXN_CURRENCY_CODE,
466 src1.TXN_REVENUE,
467 src1.TXN_FUNDING,
468 src1.TXN_INITIAL_FUNDING_AMOUNT,
469 src1.TXN_ADDITIONAL_FUNDING_AMOUNT,
470 src1.TXN_CANCELLED_FUNDING_AMOUNT,
471 src1.TXN_FUNDING_ADJUSTMENT_AMOUNT,
472 src1.TXN_REVENUE_WRITEOFF,
473 src1.TXN_AR_INVOICE_AMOUNT,
474 src1.TXN_AR_CASH_APPLIED_AMOUNT,
475 src1.TXN_AR_INVOICE_WRITEOFF_AMOUNT,
476 src1.TXN_AR_CREDIT_MEMO_AMOUNT,
477 src1.TXN_UNBILLED_RECEIVABLES,
478 src1.TXN_UNEARNED_REVENUE,
479 src1.TXN_AR_UNAPPR_INVOICE_AMOUNT,
480 src1.TXN_AR_APPR_INVOICE_AMOUNT,
481 src1.TXN_AR_AMOUNT_DUE,
482 src1.TXN_AR_AMOUNT_OVERDUE,
483 src1.PRJ_REVENUE,
484 src1.PRJ_FUNDING,
485 src1.PRJ_INITIAL_FUNDING_AMOUNT,
486 src1.PRJ_ADDITIONAL_FUNDING_AMOUNT,
487 src1.PRJ_CANCELLED_FUNDING_AMOUNT,
488 src1.PRJ_FUNDING_ADJUSTMENT_AMOUNT,
489 src1.PRJ_REVENUE_WRITEOFF,
490 src1.PRJ_AR_INVOICE_AMOUNT,
491 src1.PRJ_AR_CASH_APPLIED_AMOUNT,
492 src1.PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
493 src1.PRJ_AR_CREDIT_MEMO_AMOUNT,
494 src1.PRJ_UNBILLED_RECEIVABLES,
495 src1.PRJ_UNEARNED_REVENUE,
496 src1.PRJ_AR_UNAPPR_INVOICE_AMOUNT,
497 src1.PRJ_AR_APPR_INVOICE_AMOUNT,
498 src1.PRJ_AR_AMOUNT_DUE,
499 src1.PRJ_AR_AMOUNT_OVERDUE,
500 src1.POU_REVENUE,
501 src1.POU_FUNDING,
502 src1.POU_INITIAL_FUNDING_AMOUNT,
503 src1.POU_ADDITIONAL_FUNDING_AMOUNT,
504 src1.POU_CANCELLED_FUNDING_AMOUNT,
505 src1.POU_FUNDING_ADJUSTMENT_AMOUNT,
506 src1.POU_REVENUE_WRITEOFF,
507 src1.POU_AR_INVOICE_AMOUNT,
508 src1.POU_AR_CASH_APPLIED_AMOUNT,
509 src1.POU_AR_INVOICE_WRITEOFF_AMOUNT,
510 src1.POU_AR_CREDIT_MEMO_AMOUNT,
511 src1.POU_UNBILLED_RECEIVABLES,
512 src1.POU_UNEARNED_REVENUE,
513 src1.POU_AR_UNAPPR_INVOICE_AMOUNT,
514 src1.POU_AR_APPR_INVOICE_AMOUNT,
515 src1.POU_AR_AMOUNT_DUE,
516 src1.POU_AR_AMOUNT_OVERDUE,
517 src1.EOU_REVENUE,
518 src1.EOU_FUNDING,
519 src1.EOU_INITIAL_FUNDING_AMOUNT,
520 src1.EOU_ADDITIONAL_FUNDING_AMOUNT,
521 src1.EOU_CANCELLED_FUNDING_AMOUNT,
522 src1.EOU_FUNDING_ADJUSTMENT_AMOUNT,
523 src1.EOU_REVENUE_WRITEOFF,
524 src1.EOU_AR_INVOICE_AMOUNT,
525 src1.EOU_AR_CASH_APPLIED_AMOUNT,
526 src1.EOU_AR_INVOICE_WRITEOFF_AMOUNT,
527 src1.EOU_AR_CREDIT_MEMO_AMOUNT,
528 src1.EOU_UNBILLED_RECEIVABLES,
529 src1.EOU_UNEARNED_REVENUE,
530 src1.EOU_AR_UNAPPR_INVOICE_AMOUNT,
531 src1.EOU_AR_APPR_INVOICE_AMOUNT,
532 src1.EOU_AR_AMOUNT_DUE,
533 src1.EOU_AR_AMOUNT_OVERDUE,
534 src1.INITIAL_FUNDING_COUNT,
535 src1.ADDITIONAL_FUNDING_COUNT,
536 src1.CANCELLED_FUNDING_COUNT,
537 src1.FUNDING_ADJUSTMENT_COUNT,
538 src1.AR_INVOICE_COUNT,
539 src1.AR_CASH_APPLIED_COUNT,
540 src1.AR_INVOICE_WRITEOFF_COUNT,
541 src1.AR_CREDIT_MEMO_COUNT,
542 src1.AR_UNAPPR_INVOICE_COUNT,
543 src1.AR_APPR_INVOICE_COUNT,
544 src1.AR_COUNT_DUE,
545 src1.AR_COUNT_OVERDUE,
546 src1.G1_REVENUE,
547 src1.G1_FUNDING,
548 src1.G1_INITIAL_FUNDING_AMOUNT,
549 src1.G1_ADDITIONAL_FUNDING_AMOUNT,
550 src1.G1_CANCELLED_FUNDING_AMOUNT,
551 src1.G1_FUNDING_ADJUSTMENT_AMOUNT,
552 src1.G1_REVENUE_WRITEOFF,
553 src1.G1_AR_INVOICE_AMOUNT,
554 src1.G1_AR_CASH_APPLIED_AMOUNT,
555 src1.G1_AR_INVOICE_WRITEOFF_AMOUNT,
556 src1.G1_AR_CREDIT_MEMO_AMOUNT,
557 src1.G1_UNBILLED_RECEIVABLES,
558 src1.G1_UNEARNED_REVENUE,
559 src1.G1_AR_UNAPPR_INVOICE_AMOUNT,
560 src1.G1_AR_APPR_INVOICE_AMOUNT,
561 src1.G1_AR_AMOUNT_DUE,
562 src1.G1_AR_AMOUNT_OVERDUE,
563 src1.G2_REVENUE,
564 src1.G2_FUNDING,
565 src1.G2_INITIAL_FUNDING_AMOUNT,
566 src1.G2_ADDITIONAL_FUNDING_AMOUNT,
567 src1.G2_CANCELLED_FUNDING_AMOUNT,
568 src1.G2_FUNDING_ADJUSTMENT_AMOUNT,
569 src1.G2_REVENUE_WRITEOFF,
570 src1.G2_AR_INVOICE_AMOUNT,
571 src1.G2_AR_CASH_APPLIED_AMOUNT,
572 src1.G2_AR_INVOICE_WRITEOFF_AMOUNT,
573 src1.G2_AR_CREDIT_MEMO_AMOUNT,
574 src1.G2_UNBILLED_RECEIVABLES,
575 src1.G2_UNEARNED_REVENUE,
576 src1.G2_AR_UNAPPR_INVOICE_AMOUNT,
577 src1.G2_AR_APPR_INVOICE_AMOUNT,
578 src1.G2_AR_AMOUNT_DUE,
579 src1.G2_AR_AMOUNT_OVERDUE
580 from
581 (
582 select
583 act5.PROJECT_ID,
584 act5.PROJECT_ORG_ID,
585 nvl(map.NEW_PROJECT_ORGANIZATION_ID,
586 act5.PROJECT_ORGANIZATION_ID) PROJECT_ORGANIZATION_ID,
587 act5.TIME_ID,
588 act5.CALENDAR_TYPE,
589 act5.GL_CALENDAR_ID,
590 act5.PA_CALENDAR_ID,
591 act5.TXN_CURRENCY_CODE,
592 sum(act5.TXN_REVENUE) TXN_REVENUE,
593 sum(act5.TXN_FUNDING) TXN_FUNDING,
594 sum(act5.TXN_INITIAL_FUNDING_AMOUNT)
595 TXN_INITIAL_FUNDING_AMOUNT,
596 sum(act5.TXN_ADDITIONAL_FUNDING_AMOUNT)
597 TXN_ADDITIONAL_FUNDING_AMOUNT,
598 sum(act5.TXN_CANCELLED_FUNDING_AMOUNT)
599 TXN_CANCELLED_FUNDING_AMOUNT,
600 sum(act5.TXN_FUNDING_ADJUSTMENT_AMOUNT)
601 TXN_FUNDING_ADJUSTMENT_AMOUNT,
602 sum(act5.TXN_REVENUE_WRITEOFF) TXN_REVENUE_WRITEOFF,
603 sum(act5.TXN_AR_INVOICE_AMOUNT) TXN_AR_INVOICE_AMOUNT,
604 sum(act5.TXN_AR_CASH_APPLIED_AMOUNT)
605 TXN_AR_CASH_APPLIED_AMOUNT,
606 sum(act5.TXN_AR_INVOICE_WRITEOFF_AMOUNT)
607 TXN_AR_INVOICE_WRITEOFF_AMOUNT,
608 sum(act5.TXN_AR_CREDIT_MEMO_AMOUNT)
609 TXN_AR_CREDIT_MEMO_AMOUNT,
610 sum(act5.TXN_UNBILLED_RECEIVABLES)TXN_UNBILLED_RECEIVABLES,
611 sum(act5.TXN_UNEARNED_REVENUE) TXN_UNEARNED_REVENUE,
612 sum(act5.TXN_AR_UNAPPR_INVOICE_AMOUNT)
613 TXN_AR_UNAPPR_INVOICE_AMOUNT,
614 sum(act5.TXN_AR_APPR_INVOICE_AMOUNT)
615 TXN_AR_APPR_INVOICE_AMOUNT,
616 sum(act5.TXN_AR_AMOUNT_DUE) TXN_AR_AMOUNT_DUE,
617 sum(act5.TXN_AR_AMOUNT_OVERDUE) TXN_AR_AMOUNT_OVERDUE,
618 sum(act5.PRJ_REVENUE) PRJ_REVENUE,
619 sum(act5.PRJ_FUNDING) PRJ_FUNDING,
620 sum(act5.PRJ_INITIAL_FUNDING_AMOUNT)
621 PRJ_INITIAL_FUNDING_AMOUNT,
622 sum(act5.PRJ_ADDITIONAL_FUNDING_AMOUNT)
623 PRJ_ADDITIONAL_FUNDING_AMOUNT,
624 sum(act5.PRJ_CANCELLED_FUNDING_AMOUNT)
625 PRJ_CANCELLED_FUNDING_AMOUNT,
626 sum(act5.PRJ_FUNDING_ADJUSTMENT_AMOUNT)
627 PRJ_FUNDING_ADJUSTMENT_AMOUNT,
628 sum(act5.PRJ_REVENUE_WRITEOFF) PRJ_REVENUE_WRITEOFF,
629 sum(act5.PRJ_AR_INVOICE_AMOUNT) PRJ_AR_INVOICE_AMOUNT,
630 sum(act5.PRJ_AR_CASH_APPLIED_AMOUNT)
631 PRJ_AR_CASH_APPLIED_AMOUNT,
632 sum(act5.PRJ_AR_INVOICE_WRITEOFF_AMOUNT)
633 PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
634 sum(act5.PRJ_AR_CREDIT_MEMO_AMOUNT)
635 PRJ_AR_CREDIT_MEMO_AMOUNT,
636 sum(act5.PRJ_UNBILLED_RECEIVABLES)PRJ_UNBILLED_RECEIVABLES,
637 sum(act5.PRJ_UNEARNED_REVENUE) PRJ_UNEARNED_REVENUE,
638 sum(act5.PRJ_AR_UNAPPR_INVOICE_AMOUNT)
639 PRJ_AR_UNAPPR_INVOICE_AMOUNT,
640 sum(act5.PRJ_AR_APPR_INVOICE_AMOUNT)
641 PRJ_AR_APPR_INVOICE_AMOUNT,
642 sum(act5.PRJ_AR_AMOUNT_DUE) PRJ_AR_AMOUNT_DUE,
643 sum(act5.PRJ_AR_AMOUNT_OVERDUE) PRJ_AR_AMOUNT_OVERDUE,
644 sum(act5.POU_REVENUE) POU_REVENUE,
645 sum(act5.POU_FUNDING) POU_FUNDING,
646 sum(act5.POU_INITIAL_FUNDING_AMOUNT)
647 POU_INITIAL_FUNDING_AMOUNT,
648 sum(act5.POU_ADDITIONAL_FUNDING_AMOUNT)
649 POU_ADDITIONAL_FUNDING_AMOUNT,
650 sum(act5.POU_CANCELLED_FUNDING_AMOUNT)
651 POU_CANCELLED_FUNDING_AMOUNT,
652 sum(act5.POU_FUNDING_ADJUSTMENT_AMOUNT)
653 POU_FUNDING_ADJUSTMENT_AMOUNT,
654 sum(act5.POU_REVENUE_WRITEOFF) POU_REVENUE_WRITEOFF,
655 sum(act5.POU_AR_INVOICE_AMOUNT) POU_AR_INVOICE_AMOUNT,
656 sum(act5.POU_AR_CASH_APPLIED_AMOUNT)
657 POU_AR_CASH_APPLIED_AMOUNT,
658 sum(act5.POU_AR_INVOICE_WRITEOFF_AMOUNT)
659 POU_AR_INVOICE_WRITEOFF_AMOUNT,
660 sum(act5.POU_AR_CREDIT_MEMO_AMOUNT)
661 POU_AR_CREDIT_MEMO_AMOUNT,
662 sum(act5.POU_UNBILLED_RECEIVABLES)POU_UNBILLED_RECEIVABLES,
663 sum(act5.POU_UNEARNED_REVENUE) POU_UNEARNED_REVENUE,
664 sum(act5.POU_AR_UNAPPR_INVOICE_AMOUNT)
665 POU_AR_UNAPPR_INVOICE_AMOUNT,
666 sum(act5.POU_AR_APPR_INVOICE_AMOUNT)
667 POU_AR_APPR_INVOICE_AMOUNT,
668 sum(act5.POU_AR_AMOUNT_DUE) POU_AR_AMOUNT_DUE,
669 sum(act5.POU_AR_AMOUNT_OVERDUE) POU_AR_AMOUNT_OVERDUE,
670 sum(act5.EOU_REVENUE) EOU_REVENUE,
671 sum(act5.EOU_FUNDING) EOU_FUNDING,
672 sum(act5.EOU_INITIAL_FUNDING_AMOUNT)
673 EOU_INITIAL_FUNDING_AMOUNT,
674 sum(act5.EOU_ADDITIONAL_FUNDING_AMOUNT)
675 EOU_ADDITIONAL_FUNDING_AMOUNT,
676 sum(act5.EOU_CANCELLED_FUNDING_AMOUNT)
677 EOU_CANCELLED_FUNDING_AMOUNT,
678 sum(act5.EOU_FUNDING_ADJUSTMENT_AMOUNT)
679 EOU_FUNDING_ADJUSTMENT_AMOUNT,
680 sum(act5.EOU_REVENUE_WRITEOFF) EOU_REVENUE_WRITEOFF,
681 sum(act5.EOU_AR_INVOICE_AMOUNT) EOU_AR_INVOICE_AMOUNT,
682 sum(act5.EOU_AR_CASH_APPLIED_AMOUNT)
683 EOU_AR_CASH_APPLIED_AMOUNT,
684 sum(act5.EOU_AR_INVOICE_WRITEOFF_AMOUNT)
685 EOU_AR_INVOICE_WRITEOFF_AMOUNT,
686 sum(act5.EOU_AR_CREDIT_MEMO_AMOUNT)
687 EOU_AR_CREDIT_MEMO_AMOUNT,
688 sum(act5.EOU_UNBILLED_RECEIVABLES)EOU_UNBILLED_RECEIVABLES,
689 sum(act5.EOU_UNEARNED_REVENUE) EOU_UNEARNED_REVENUE,
690 sum(act5.EOU_AR_UNAPPR_INVOICE_AMOUNT)
691 EOU_AR_UNAPPR_INVOICE_AMOUNT,
692 sum(act5.EOU_AR_APPR_INVOICE_AMOUNT)
693 EOU_AR_APPR_INVOICE_AMOUNT,
694 sum(act5.EOU_AR_AMOUNT_DUE) EOU_AR_AMOUNT_DUE,
695 sum(act5.EOU_AR_AMOUNT_OVERDUE) EOU_AR_AMOUNT_OVERDUE,
696 sum(act5.INITIAL_FUNDING_COUNT) INITIAL_FUNDING_COUNT,
697 sum(act5.ADDITIONAL_FUNDING_COUNT)ADDITIONAL_FUNDING_COUNT,
698 sum(act5.CANCELLED_FUNDING_COUNT) CANCELLED_FUNDING_COUNT,
699 sum(act5.FUNDING_ADJUSTMENT_COUNT)FUNDING_ADJUSTMENT_COUNT,
700 sum(act5.AR_INVOICE_COUNT) AR_INVOICE_COUNT,
701 sum(act5.AR_CASH_APPLIED_COUNT) AR_CASH_APPLIED_COUNT,
702 sum(act5.AR_INVOICE_WRITEOFF_COUNT)
703 AR_INVOICE_WRITEOFF_COUNT,
704 sum(act5.AR_CREDIT_MEMO_COUNT) AR_CREDIT_MEMO_COUNT,
705 sum(act5.AR_UNAPPR_INVOICE_COUNT) AR_UNAPPR_INVOICE_COUNT,
706 sum(act5.AR_APPR_INVOICE_COUNT) AR_APPR_INVOICE_COUNT,
707 sum(act5.AR_COUNT_DUE) AR_COUNT_DUE,
708 sum(act5.AR_COUNT_OVERDUE) AR_COUNT_OVERDUE,
709 sum(act5.G1_REVENUE) G1_REVENUE,
710 sum(act5.G1_FUNDING) G1_FUNDING,
711 sum(act5.G1_INITIAL_FUNDING_AMOUNT)
712 G1_INITIAL_FUNDING_AMOUNT,
713 sum(act5.G1_ADDITIONAL_FUNDING_AMOUNT)
714 G1_ADDITIONAL_FUNDING_AMOUNT,
715 sum(act5.G1_CANCELLED_FUNDING_AMOUNT)
716 G1_CANCELLED_FUNDING_AMOUNT,
717 sum(act5.G1_FUNDING_ADJUSTMENT_AMOUNT)
718 G1_FUNDING_ADJUSTMENT_AMOUNT,
719 sum(act5.G1_REVENUE_WRITEOFF) G1_REVENUE_WRITEOFF,
720 sum(act5.G1_AR_INVOICE_AMOUNT) G1_AR_INVOICE_AMOUNT,
721 sum(act5.G1_AR_CASH_APPLIED_AMOUNT)
722 G1_AR_CASH_APPLIED_AMOUNT,
723 sum(act5.G1_AR_INVOICE_WRITEOFF_AMOUNT)
724 G1_AR_INVOICE_WRITEOFF_AMOUNT,
725 sum(act5.G1_AR_CREDIT_MEMO_AMOUNT)G1_AR_CREDIT_MEMO_AMOUNT,
726 sum(act5.G1_UNBILLED_RECEIVABLES) G1_UNBILLED_RECEIVABLES,
727 sum(act5.G1_UNEARNED_REVENUE) G1_UNEARNED_REVENUE,
728 sum(act5.G1_AR_UNAPPR_INVOICE_AMOUNT)
729 G1_AR_UNAPPR_INVOICE_AMOUNT,
730 sum(act5.G1_AR_APPR_INVOICE_AMOUNT)
731 G1_AR_APPR_INVOICE_AMOUNT,
732 sum(act5.G1_AR_AMOUNT_DUE) G1_AR_AMOUNT_DUE,
733 sum(act5.G1_AR_AMOUNT_OVERDUE) G1_AR_AMOUNT_OVERDUE,
734 sum(act5.G2_REVENUE) G2_REVENUE,
735 sum(act5.G2_FUNDING) G2_FUNDING,
736 sum(act5.G2_INITIAL_FUNDING_AMOUNT)
737 G2_INITIAL_FUNDING_AMOUNT,
738 sum(act5.G2_ADDITIONAL_FUNDING_AMOUNT)
739 G2_ADDITIONAL_FUNDING_AMOUNT,
740 sum(act5.G2_CANCELLED_FUNDING_AMOUNT)
741 G2_CANCELLED_FUNDING_AMOUNT,
742 sum(act5.G2_FUNDING_ADJUSTMENT_AMOUNT)
743 G2_FUNDING_ADJUSTMENT_AMOUNT,
744 sum(act5.G2_REVENUE_WRITEOFF) G2_REVENUE_WRITEOFF,
745 sum(act5.G2_AR_INVOICE_AMOUNT) G2_AR_INVOICE_AMOUNT,
746 sum(act5.G2_AR_CASH_APPLIED_AMOUNT)
747 G2_AR_CASH_APPLIED_AMOUNT,
748 sum(act5.G2_AR_INVOICE_WRITEOFF_AMOUNT)
749 G2_AR_INVOICE_WRITEOFF_AMOUNT,
750 sum(act5.G2_AR_CREDIT_MEMO_AMOUNT)G2_AR_CREDIT_MEMO_AMOUNT,
751 sum(act5.G2_UNBILLED_RECEIVABLES) G2_UNBILLED_RECEIVABLES,
752 sum(act5.G2_UNEARNED_REVENUE) G2_UNEARNED_REVENUE,
753 sum(act5.G2_AR_UNAPPR_INVOICE_AMOUNT)
754 G2_AR_UNAPPR_INVOICE_AMOUNT,
755 sum(act5.G2_AR_APPR_INVOICE_AMOUNT)
756 G2_AR_APPR_INVOICE_AMOUNT,
757 sum(act5.G2_AR_AMOUNT_DUE) G2_AR_AMOUNT_DUE,
758 sum(act5.G2_AR_AMOUNT_OVERDUE) G2_AR_AMOUNT_OVERDUE
759 from
760 PJI_PJI_RMAP_ACT act5_r,
761 PJI_FM_AGGR_ACT5 act5,
762 (
763 select
764 map.PROJECT_ID,
765 map.NEW_PROJECT_ORGANIZATION_ID
766 from
767 PJI_PJI_PROJ_BATCH_MAP map
768 where
769 map.NEW_PROJECT_ORGANIZATION_ID <> map.PROJECT_ORGANIZATION_ID
770 ) map
771 where
772 act5_r.WORKER_ID = p_worker_id and
773 act5.ROWID = act5_r. STG_ROWID and
774 act5.PROJECT_ID = map.PROJECT_ID (+)
775 group by
776 act5.PROJECT_ID,
777 act5.PROJECT_ORG_ID,
778 nvl(map.NEW_PROJECT_ORGANIZATION_ID,
779 act5.PROJECT_ORGANIZATION_ID),
780 act5.TIME_ID,
781 act5.CALENDAR_TYPE,
782 act5.GL_CALENDAR_ID,
783 act5.PA_CALENDAR_ID,
784 act5.TXN_CURRENCY_CODE
785 ) src1
786 ) src2,
787 PA_PROJECTS_ALL prj,
788 PJI_ORG_EXTR_INFO info,
789 (
790 select 1 INVERT_ID from dual
791 where l_g1_currency_code is not null union all
792 select 2 INVERT_ID from dual
793 where l_g2_currency_flag = 'Y' and
794 l_g2_currency_code is not null union all
795 select 4 INVERT_ID from dual union all
796 select 8 INVERT_ID from dual
797 -- select 16 INVERT_ID from dual OMIT TXN CURRENCY FROM PJI
798 -- where l_txn_currency_flag = 'Y' union all
799 -- select 32 INVERT_ID from dual OMIT DETAIL SLICES FOR NOW
800 -- where l_g1_currency_code is not null union all
801 -- select 64 INVERT_ID from dual
802 -- where l_g2_currency_flag = 'Y' and
803 -- l_g2_currency_code is not null union all
804 -- select 128 INVERT_ID from dual union all
805 -- select 256 INVERT_ID from dual
806 ) invert
807 where
808 src2.PROJECT_ID = prj.PROJECT_ID and
809 nvl(src2.PROJECT_ORG_ID, -1) = nvl(info.ORG_ID, -1)
810 ) src3
811 group by
812 src3.WORKER_ID,
813 src3.PROJECT_ID,
814 src3.PROJECT_ORG_ID,
815 src3.PROJECT_ORGANIZATION_ID,
816 src3.TIME_ID,
817 src3.PERIOD_TYPE_ID,
818 src3.CALENDAR_TYPE,
819 src3.GL_CALENDAR_ID,
820 src3.PA_CALENDAR_ID,
821 src3.DIFF_CURRENCY_CODE,
822 src3.DIFF_ROWNUM,
823 nvl(src3.CURRENCY_CODE, 'PJI$NULL')
824 union all -- snapshot reversals - PART 1 - GL dates
825 -- Select old ITD amounts for snapshots with
826 -- reverse sign from base level fact
827 select /*+ ordered full(map) parallel(map)
828 index(acp, PJI_AC_PROJ_F_N2)
829 use_nl(acp)
830 full(info) */
831 p_worker_id WORKER_ID,
832 acp.PROJECT_ID,
833 acp.PROJECT_ORG_ID,
834 acp.PROJECT_ORGANIZATION_ID,
835 to_number(to_char(sysdate, 'J')) TIME_ID,
836 1 PERIOD_TYPE_ID,
837 'C' CALENDAR_TYPE,
838 info.GL_CALENDAR_ID,
839 info.PA_CALENDAR_ID,
840 acp.CURR_RECORD_TYPE_ID,
841 acp.CURRENCY_CODE,
842 to_number(null) REVENUE,
843 to_number(null) FUNDING,
844 to_number(null) INITIAL_FUNDING_AMOUNT,
845 to_number(null) INITIAL_FUNDING_COUNT,
846 to_number(null) ADDITIONAL_FUNDING_AMOUNT,
847 to_number(null) ADDITIONAL_FUNDING_COUNT,
848 to_number(null) CANCELLED_FUNDING_AMOUNT,
849 to_number(null) CANCELLED_FUNDING_COUNT,
850 to_number(null) FUNDING_ADJUSTMENT_AMOUNT,
851 to_number(null) FUNDING_ADJUSTMENT_COUNT,
852 to_number(null) REVENUE_WRITEOFF,
853 to_number(null) AR_INVOICE_AMOUNT,
854 to_number(null) AR_INVOICE_COUNT,
855 -acp.AR_CASH_APPLIED_AMOUNT,
856 to_number(null) AR_CASH_APPLIED_COUNT,
857 to_number(null) AR_INVOICE_WRITEOFF_AMOUNT,
858 to_number(null) AR_INVOICE_WRITEOFF_COUNT,
859 to_number(null) AR_CREDIT_MEMO_AMOUNT,
860 to_number(null) AR_CREDIT_MEMO_COUNT,
861 to_number(null) UNBILLED_RECEIVABLES,
862 to_number(null) UNEARNED_REVENUE,
863 -acp.AR_UNAPPR_INVOICE_AMOUNT,
864 -acp.AR_UNAPPR_INVOICE_COUNT,
865 -acp.AR_APPR_INVOICE_AMOUNT,
866 -acp.AR_APPR_INVOICE_COUNT,
867 -acp.AR_AMOUNT_DUE,
868 -acp.AR_COUNT_DUE,
869 -acp.AR_AMOUNT_OVERDUE,
870 -acp.AR_COUNT_OVERDUE,
871 to_number(null) DORMANT_BACKLOG_INACTIV,
872 to_number(null) DORMANT_BACKLOG_START,
873 to_number(null) LOST_BACKLOG,
874 to_number(null) ACTIVE_BACKLOG,
875 to_number(null) REVENUE_AT_RISK
876 from
877 PJI_PJI_PROJ_BATCH_MAP map,
878 PJI_AC_PROJ_F acp,
879 FII_TIME_RPT_STRUCT cal,
880 PJI_ORG_EXTR_INFO info
881 where
882 map.WORKER_ID = p_worker_id and
883 acp.PROJECT_ID = map.PROJECT_ID and
884 cal.REPORT_DATE = trunc(sysdate, 'J') and
885 cal.CALENDAR_TYPE = acp.CALENDAR_TYPE and
886 cal.PERIOD_TYPE_ID = acp.PERIOD_TYPE_ID and
887 cal.TIME_ID = acp.TIME_ID and
888 cal.RECORD_TYPE_ID <> 128 and
889 cal.RECORD_TYPE_ID <> 256 and
890 cal.RECORD_TYPE_ID <> 512 and
891 abs(nvl(acp.AR_CASH_APPLIED_AMOUNT,0)) +
892 abs(nvl(acp.AR_UNAPPR_INVOICE_AMOUNT,0)) +
893 abs(nvl(acp.AR_APPR_INVOICE_AMOUNT,0)) +
894 abs(nvl(acp.AR_AMOUNT_DUE,0)) +
895 abs(nvl(acp.AR_AMOUNT_OVERDUE,0)) +
896 abs(nvl(acp.AR_UNAPPR_INVOICE_COUNT,0)) +
897 abs(nvl(acp.AR_APPR_INVOICE_COUNT,0)) +
898 abs(nvl(acp.AR_COUNT_DUE,0)) +
899 abs(nvl(acp.AR_COUNT_OVERDUE,0)) > 0
900 and acp.PROJECT_ORG_ID = info.ORG_ID
901 union all -- snapshot reversals - PART 2 - PA dates
902 -- Select old ITD amounts for snapshots with
903 -- reverse sign from base level fact
904 select /*+ ordered full(map) parallel(map)
905 index(acp, PJI_AC_PROJ_F_N2)
906 use_nl(acp)
907 full(info) */
908 p_worker_id WORKER_ID,
909 acp.PROJECT_ID,
910 acp.PROJECT_ORG_ID,
911 acp.PROJECT_ORGANIZATION_ID,
912 to_number(to_char(sysdate, 'J')) TIME_ID,
913 1 PERIOD_TYPE_ID,
914 'P' CALENDAR_TYPE,
915 info.GL_CALENDAR_ID,
916 info.PA_CALENDAR_ID,
917 acp.CURR_RECORD_TYPE_ID,
918 acp.CURRENCY_CODE,
919 to_number(null) REVENUE,
920 to_number(null) FUNDING,
921 to_number(null) INITIAL_FUNDING_AMOUNT,
922 to_number(null) INITIAL_FUNDING_COUNT,
923 to_number(null) ADDITIONAL_FUNDING_AMOUNT,
924 to_number(null) ADDITIONAL_FUNDING_COUNT,
925 to_number(null) CANCELLED_FUNDING_AMOUNT,
926 to_number(null) CANCELLED_FUNDING_COUNT,
927 to_number(null) FUNDING_ADJUSTMENT_AMOUNT,
928 to_number(null) FUNDING_ADJUSTMENT_COUNT,
929 to_number(null) REVENUE_WRITEOFF,
930 to_number(null) AR_INVOICE_AMOUNT,
931 to_number(null) AR_INVOICE_COUNT,
932 -acp.AR_CASH_APPLIED_AMOUNT,
933 to_number(null) AR_CASH_APPLIED_COUNT,
934 to_number(null) AR_INVOICE_WRITEOFF_AMOUNT,
935 to_number(null) AR_INVOICE_WRITEOFF_COUNT,
936 to_number(null) AR_CREDIT_MEMO_AMOUNT,
937 to_number(null) AR_CREDIT_MEMO_COUNT,
938 to_number(null) UNBILLED_RECEIVABLES,
939 to_number(null) UNEARNED_REVENUE,
940 -acp.AR_UNAPPR_INVOICE_AMOUNT,
941 -acp.AR_UNAPPR_INVOICE_COUNT,
942 -acp.AR_APPR_INVOICE_AMOUNT,
943 -acp.AR_APPR_INVOICE_COUNT,
944 -acp.AR_AMOUNT_DUE,
945 -acp.AR_COUNT_DUE,
946 -acp.AR_AMOUNT_OVERDUE,
947 -acp.AR_COUNT_OVERDUE,
948 to_number(null) DORMANT_BACKLOG_INACTIV,
949 to_number(null) DORMANT_BACKLOG_START,
950 to_number(null) LOST_BACKLOG,
951 to_number(null) ACTIVE_BACKLOG,
952 to_number(null) REVENUE_AT_RISK
953
954 from
955 PJI_PJI_PROJ_BATCH_MAP map,
956 PJI_AC_PROJ_F acp,
957 PJI_ORG_EXTR_INFO info,
958 FII_TIME_CAL_RPT_STRUCT cal
959 where
960 l_pa_calendar_flag = 'Y' and
961 map.WORKER_ID = p_worker_id and
962 acp.PROJECT_ID = map.PROJECT_ID and
963 info.ORG_ID = acp.PROJECT_ORG_ID and
964 cal.CALENDAR_ID = info.PA_CALENDAR_ID and
965 cal.REPORT_DATE = trunc(sysdate, 'J') and
966 cal.PERIOD_TYPE_ID <> 16 and
967 acp.CALENDAR_TYPE = 'P' and
968 cal.PERIOD_TYPE_ID = acp.PERIOD_TYPE_ID and
969 cal.TIME_ID = acp.TIME_ID and
970 cal.RECORD_TYPE_ID <> 128 and
971 cal.RECORD_TYPE_ID <> 256 and
972 cal.RECORD_TYPE_ID <> 512 and
973 abs(nvl(acp.AR_CASH_APPLIED_AMOUNT,0)) +
974 abs(nvl(acp.AR_UNAPPR_INVOICE_AMOUNT,0)) +
975 abs(nvl(acp.AR_APPR_INVOICE_AMOUNT,0)) +
976 abs(nvl(acp.AR_AMOUNT_DUE,0)) +
977 abs(nvl(acp.AR_AMOUNT_OVERDUE,0)) +
978 abs(nvl(acp.AR_UNAPPR_INVOICE_COUNT,0)) +
979 abs(nvl(acp.AR_APPR_INVOICE_COUNT,0)) +
980 abs(nvl(acp.AR_COUNT_DUE,0)) +
981 abs(nvl(acp.AR_COUNT_OVERDUE,0)) > 0
982 union all -- snapshot reversals - PART 3 - PA day for week subst.
983 -- Select old ITD amounts for snapshots with
984 -- reverse sign from base level fact
985 select /*+ ordered full(map) parallel(map)
986 index(acp, PJI_AC_PROJ_F_N2)
987 use_nl(acp)
988 full(info) */
989 p_worker_id WORKER_ID,
990 acp.PROJECT_ID,
991 acp.PROJECT_ORG_ID,
992 acp.PROJECT_ORGANIZATION_ID,
993 to_number(to_char(sysdate, 'J')) TIME_ID,
994 1 PERIOD_TYPE_ID,
995 'P' CALENDAR_TYPE,
996 info.GL_CALENDAR_ID,
997 info.PA_CALENDAR_ID,
998 acp.CURR_RECORD_TYPE_ID,
999 acp.CURRENCY_CODE,
1000 to_number(null) REVENUE,
1001 to_number(null) FUNDING,
1002 to_number(null) INITIAL_FUNDING_AMOUNT,
1003 to_number(null) INITIAL_FUNDING_COUNT,
1004 to_number(null) ADDITIONAL_FUNDING_AMOUNT,
1005 to_number(null) ADDITIONAL_FUNDING_COUNT,
1006 to_number(null) CANCELLED_FUNDING_AMOUNT,
1007 to_number(null) CANCELLED_FUNDING_COUNT,
1008 to_number(null) FUNDING_ADJUSTMENT_AMOUNT,
1009 to_number(null) FUNDING_ADJUSTMENT_COUNT,
1010 to_number(null) REVENUE_WRITEOFF,
1011 to_number(null) AR_INVOICE_AMOUNT,
1012 to_number(null) AR_INVOICE_COUNT,
1013 -acp.AR_CASH_APPLIED_AMOUNT,
1014 to_number(null) AR_CASH_APPLIED_COUNT,
1015 to_number(null) AR_INVOICE_WRITEOFF_AMOUNT,
1016 to_number(null) AR_INVOICE_WRITEOFF_COUNT,
1017 to_number(null) AR_CREDIT_MEMO_AMOUNT,
1018 to_number(null) AR_CREDIT_MEMO_COUNT,
1019 to_number(null) UNBILLED_RECEIVABLES,
1020 to_number(null) UNEARNED_REVENUE,
1021 -acp.AR_UNAPPR_INVOICE_AMOUNT,
1022 -acp.AR_UNAPPR_INVOICE_COUNT,
1023 -acp.AR_APPR_INVOICE_AMOUNT,
1024 -acp.AR_APPR_INVOICE_COUNT,
1025 -acp.AR_AMOUNT_DUE,
1026 -acp.AR_COUNT_DUE,
1027 -acp.AR_AMOUNT_OVERDUE,
1028 -acp.AR_COUNT_OVERDUE,
1029 to_number(null) DORMANT_BACKLOG_INACTIV,
1030 to_number(null) DORMANT_BACKLOG_START,
1031 to_number(null) LOST_BACKLOG,
1032 to_number(null) ACTIVE_BACKLOG,
1033 to_number(null) REVENUE_AT_RISK
1034
1035 from
1036 PJI_PJI_PROJ_BATCH_MAP map,
1037 PJI_AC_PROJ_F acp,
1038 PJI_ORG_EXTR_INFO info,
1039 PJI_TIME_PA_RPT_STR_MV cal
1040 where
1041 l_pa_calendar_flag = 'Y' and
1042 map.WORKER_ID = p_worker_id and
1043 acp.PROJECT_ID = map.PROJECT_ID and
1044 info.ORG_ID = acp.PROJECT_ORG_ID and
1045 cal.CALENDAR_ID = info.PA_CALENDAR_ID and
1046 cal.REPORT_DATE = trunc(sysdate, 'J') and
1047 acp.CALENDAR_TYPE = 'P' and
1048 cal.PERIOD_TYPE_ID = acp.PERIOD_TYPE_ID and
1049 cal.TIME_ID = acp.TIME_ID and
1050 abs(nvl(acp.AR_CASH_APPLIED_AMOUNT,0)) +
1051 abs(nvl(acp.AR_UNAPPR_INVOICE_AMOUNT,0)) +
1052 abs(nvl(acp.AR_APPR_INVOICE_AMOUNT,0)) +
1053 abs(nvl(acp.AR_AMOUNT_DUE,0)) +
1054 abs(nvl(acp.AR_AMOUNT_OVERDUE,0)) +
1055 abs(nvl(acp.AR_UNAPPR_INVOICE_COUNT,0)) +
1056 abs(nvl(acp.AR_APPR_INVOICE_COUNT,0)) +
1057 abs(nvl(acp.AR_COUNT_DUE,0)) +
1058 abs(nvl(acp.AR_COUNT_OVERDUE,0)) > 0
1059 ) src4
1060 group by
1061 src4.WORKER_ID,
1062 src4.PROJECT_ID,
1063 src4.PROJECT_ORG_ID,
1064 src4.PROJECT_ORGANIZATION_ID,
1065 src4.TIME_ID,
1066 src4.PERIOD_TYPE_ID,
1067 src4.CALENDAR_TYPE,
1068 src4.GL_CALENDAR_ID,
1069 src4.PA_CALENDAR_ID,
1070 src4.CURR_RECORD_TYPE_ID,
1071 src4.CURRENCY_CODE;
1072
1073 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_ACT.AGGREGATE_ACT_SLICES(p_worker_id);');
1074
1075 commit;
1076
1077 end AGGREGATE_ACT_SLICES;
1078
1079
1080 -- -----------------------------------------------------
1081 -- procedure PURGE_ACT_DATA
1082 -- -----------------------------------------------------
1083 procedure PURGE_ACT_DATA (p_worker_id in number) is
1084
1085 l_process varchar2(30);
1086 l_schema varchar2(30);
1087
1088 begin
1089
1090 l_process := PJI_RM_SUM_MAIN.g_process || p_worker_id;
1091
1092 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ROLLUP_ACT.PURGE_ACT_DATA(p_worker_id);')) then
1093 return;
1094 end if;
1095
1096 delete
1097 from PJI_FM_AGGR_ACT5
1098 where ROWID in (select STG_ROWID
1099 from PJI_PJI_RMAP_ACT
1100 where WORKER_ID = p_worker_id);
1101
1102 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ROLLUP_ACT.PURGE_ACT_DATA(p_worker_id);');
1103
1104 commit;
1105
1106 end PURGE_ACT_DATA;
1107
1108
1109 -- -----------------------------------------------------
1110 -- procedure EXPAND_ACT_CAL_EN
1111 -- -----------------------------------------------------
1112 procedure EXPAND_ACT_CAL_EN (p_worker_id in number,
1113 p_backlog_flag in varchar2 default 'N') is
1114
1115 l_process varchar2(30);
1116
1117 begin
1118
1119 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1120
1121 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1122 (
1123 l_process,
1124 'PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_EN(p_worker_id, ''' ||
1125 p_backlog_flag || ''');'
1126 )) then
1127 return;
1128 end if;
1129
1130 insert /*+ append parallel(act3_i) */ into PJI_FM_AGGR_ACT3 act3_i -- in EXPAND_ACT_CAL_EN
1131 (
1132 WORKER_ID,
1133 PROJECT_ID,
1134 PROJECT_ORG_ID,
1135 PROJECT_ORGANIZATION_ID,
1136 TIME_ID,
1137 PERIOD_TYPE_ID,
1138 CALENDAR_TYPE,
1139 CURR_RECORD_TYPE_ID,
1140 CURRENCY_CODE,
1141 REVENUE,
1142 FUNDING,
1143 INITIAL_FUNDING_AMOUNT,
1144 INITIAL_FUNDING_COUNT,
1145 ADDITIONAL_FUNDING_AMOUNT,
1146 ADDITIONAL_FUNDING_COUNT,
1147 CANCELLED_FUNDING_AMOUNT,
1148 CANCELLED_FUNDING_COUNT,
1149 FUNDING_ADJUSTMENT_AMOUNT,
1150 FUNDING_ADJUSTMENT_COUNT,
1151 REVENUE_WRITEOFF,
1152 AR_INVOICE_AMOUNT,
1153 AR_INVOICE_COUNT,
1154 AR_CASH_APPLIED_AMOUNT,
1155 AR_CASH_APPLIED_COUNT,
1156 AR_INVOICE_WRITEOFF_AMOUNT,
1157 AR_INVOICE_WRITEOFF_COUNT,
1158 AR_CREDIT_MEMO_AMOUNT,
1159 AR_CREDIT_MEMO_COUNT,
1160 UNBILLED_RECEIVABLES,
1161 UNEARNED_REVENUE,
1162 AR_UNAPPR_INVOICE_AMOUNT,
1163 AR_UNAPPR_INVOICE_COUNT,
1164 AR_APPR_INVOICE_AMOUNT,
1165 AR_APPR_INVOICE_COUNT,
1166 AR_AMOUNT_DUE,
1167 AR_COUNT_DUE,
1168 AR_AMOUNT_OVERDUE,
1169 AR_COUNT_OVERDUE,
1170 DORMANT_BACKLOG_INACTIV,
1171 DORMANT_BACKLOG_START,
1172 LOST_BACKLOG,
1173 ACTIVE_BACKLOG,
1174 REVENUE_AT_RISK
1175 )
1176 select /*+ ordered
1177 full(time) use_hash(time) swap_join_inputs(time)
1178 full(act) use_hash(act) parallel(act) */
1179 p_worker_id,
1180 act.PROJECT_ID,
1181 act.PROJECT_ORG_ID,
1182 act.PROJECT_ORGANIZATION_ID,
1183 case when grouping(time.ENT_YEAR_ID) = 0 and
1184 grouping(time.ENT_QTR_ID) = 0 and
1185 grouping(time.ENT_PERIOD_ID) = 0
1186 then time.ENT_PERIOD_ID
1187 when grouping(time.ENT_YEAR_ID) = 0 and
1188 grouping(time.ENT_QTR_ID) = 0 and
1189 grouping(time.ENT_PERIOD_ID) = 1
1190 then time.ENT_QTR_ID
1191 when grouping(time.ENT_YEAR_ID) = 0 and
1192 grouping(time.ENT_QTR_ID) = 1 and
1193 grouping(time.ENT_PERIOD_ID) = 1
1194 then time.ENT_YEAR_ID
1195 end TIME_ID,
1196 case when grouping(time.ENT_YEAR_ID) = 0 and
1197 grouping(time.ENT_QTR_ID) = 0 and
1198 grouping(time.ENT_PERIOD_ID) = 0
1199 then 32
1200 when grouping(time.ENT_YEAR_ID) = 0 and
1201 grouping(time.ENT_QTR_ID) = 0 and
1202 grouping(time.ENT_PERIOD_ID) = 1
1203 then 64
1204 when grouping(time.ENT_YEAR_ID) = 0 and
1205 grouping(time.ENT_QTR_ID) = 1 and
1206 grouping(time.ENT_PERIOD_ID) = 1
1207 then 128
1208 end PERIOD_TYPE_ID,
1209 'E' CALENDAR_TYPE,
1210 bitand(act.CURR_RECORD_TYPE_ID, 247) CURR_RECORD_TYPE_ID,
1211 act.CURRENCY_CODE,
1212 sum(act.REVENUE),
1213 sum(act.FUNDING),
1214 sum(act.INITIAL_FUNDING_AMOUNT),
1215 sum(act.INITIAL_FUNDING_COUNT),
1216 sum(act.ADDITIONAL_FUNDING_AMOUNT),
1217 sum(act.ADDITIONAL_FUNDING_COUNT),
1218 sum(act.CANCELLED_FUNDING_AMOUNT),
1219 sum(act.CANCELLED_FUNDING_COUNT),
1220 sum(act.FUNDING_ADJUSTMENT_AMOUNT),
1221 sum(act.FUNDING_ADJUSTMENT_COUNT),
1222 sum(act.REVENUE_WRITEOFF),
1223 sum(act.AR_INVOICE_AMOUNT),
1224 sum(act.AR_INVOICE_COUNT),
1225 sum(act.AR_CASH_APPLIED_AMOUNT),
1226 sum(act.AR_CASH_APPLIED_COUNT),
1227 sum(act.AR_INVOICE_WRITEOFF_AMOUNT),
1228 sum(act.AR_INVOICE_WRITEOFF_COUNT),
1229 sum(act.AR_CREDIT_MEMO_AMOUNT),
1230 sum(act.AR_CREDIT_MEMO_COUNT),
1231 sum(act.UNBILLED_RECEIVABLES),
1232 sum(act.UNEARNED_REVENUE),
1233 sum(act.AR_UNAPPR_INVOICE_AMOUNT),
1234 sum(act.AR_UNAPPR_INVOICE_COUNT),
1235 sum(act.AR_APPR_INVOICE_AMOUNT),
1236 sum(act.AR_APPR_INVOICE_COUNT),
1237 sum(act.AR_AMOUNT_DUE),
1238 sum(act.AR_COUNT_DUE),
1239 sum(act.AR_AMOUNT_OVERDUE),
1240 sum(act.AR_COUNT_OVERDUE),
1241 sum(act.DORMANT_BACKLOG_INACTIV),
1242 sum(act.DORMANT_BACKLOG_START),
1243 sum(act.LOST_BACKLOG),
1244 sum(act.ACTIVE_BACKLOG),
1245 sum(act.REVENUE_AT_RISK)
1246 from
1247 FII_TIME_DAY time,
1248 PJI_FM_AGGR_ACT3 act
1249 where
1250 act.PERIOD_TYPE_ID = 1 and
1251 act.CALENDAR_TYPE = 'C' and
1252 act.CURR_RECORD_TYPE_ID not in (8, 256) and
1253 act.TIME_ID = time.REPORT_DATE_JULIAN
1254 group by
1255 act.PROJECT_ID,
1256 act.PROJECT_ORG_ID,
1257 act.PROJECT_ORGANIZATION_ID,
1258 rollup (time.ENT_YEAR_ID,
1259 time.ENT_QTR_ID,
1260 time.ENT_PERIOD_ID),
1261 bitand(act.CURR_RECORD_TYPE_ID, 247),
1262 act.CURRENCY_CODE
1263 having
1264 not (grouping(time.ENT_YEAR_ID) = 1 and
1265 grouping(time.ENT_QTR_ID) = 1 and
1266 grouping(time.ENT_PERIOD_ID) = 1);
1267
1268 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1269 (
1270 l_process,
1271 'PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_EN(p_worker_id, ''' ||
1272 p_backlog_flag || ''');'
1273 );
1274
1275 commit;
1276
1277 end EXPAND_ACT_CAL_EN;
1278
1279
1280 -- -----------------------------------------------------
1281 -- procedure EXPAND_ACT_CAL_PA
1282 -- -----------------------------------------------------
1283 procedure EXPAND_ACT_CAL_PA (p_worker_id in number,
1284 p_backlog_flag in varchar2 default 'N') is
1285
1286 l_process varchar2(30);
1287
1288 begin
1289
1290 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1291 (
1292 PJI_RM_SUM_MAIN.g_process,
1293 'PA_CALENDAR_FLAG'
1294 ) = 'N') then
1295 return;
1296 end if;
1297
1298 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1299
1300 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1301 (
1302 l_process,
1303 'PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_PA(p_worker_id, ''' ||
1304 p_backlog_flag || ''');'
1305 )) then
1306 return;
1307 end if;
1308
1309 insert /*+ append parallel(act3_i) */ into PJI_FM_AGGR_ACT3 act3_i -- in EXPAND_ACT_CAL_PA
1310 (
1311 WORKER_ID,
1312 PROJECT_ID,
1313 PROJECT_ORG_ID,
1314 PROJECT_ORGANIZATION_ID,
1315 TIME_ID,
1316 PERIOD_TYPE_ID,
1317 CALENDAR_TYPE,
1318 CURR_RECORD_TYPE_ID,
1319 CURRENCY_CODE,
1320 REVENUE,
1321 FUNDING,
1322 INITIAL_FUNDING_AMOUNT,
1323 INITIAL_FUNDING_COUNT,
1324 ADDITIONAL_FUNDING_AMOUNT,
1325 ADDITIONAL_FUNDING_COUNT,
1326 CANCELLED_FUNDING_AMOUNT,
1327 CANCELLED_FUNDING_COUNT,
1328 FUNDING_ADJUSTMENT_AMOUNT,
1329 FUNDING_ADJUSTMENT_COUNT,
1330 REVENUE_WRITEOFF,
1331 AR_INVOICE_AMOUNT,
1332 AR_INVOICE_COUNT,
1333 AR_CASH_APPLIED_AMOUNT,
1334 AR_CASH_APPLIED_COUNT,
1335 AR_INVOICE_WRITEOFF_AMOUNT,
1336 AR_INVOICE_WRITEOFF_COUNT,
1337 AR_CREDIT_MEMO_AMOUNT,
1338 AR_CREDIT_MEMO_COUNT,
1339 UNBILLED_RECEIVABLES,
1340 UNEARNED_REVENUE,
1341 AR_UNAPPR_INVOICE_AMOUNT,
1342 AR_UNAPPR_INVOICE_COUNT,
1343 AR_APPR_INVOICE_AMOUNT,
1344 AR_APPR_INVOICE_COUNT,
1345 AR_AMOUNT_DUE,
1346 AR_COUNT_DUE,
1347 AR_AMOUNT_OVERDUE,
1348 AR_COUNT_OVERDUE,
1349 DORMANT_BACKLOG_INACTIV,
1350 DORMANT_BACKLOG_START,
1351 LOST_BACKLOG,
1352 ACTIVE_BACKLOG,
1353 REVENUE_AT_RISK
1354 )
1355 select /*+ ordered
1356 full(time) use_hash(time) parallel(time) swap_join_inputs(time)
1357 full(act) use_hash(act) parallel(act) */
1358 p_worker_id,
1359 act.PROJECT_ID,
1360 act.PROJECT_ORG_ID,
1361 act.PROJECT_ORGANIZATION_ID,
1362 case when grouping(time.CAL_YEAR_ID) = 0 and
1363 grouping(time.CAL_QTR_ID) = 0 and
1364 grouping(time.CAL_PERIOD_ID) = 0
1365 then time.CAL_PERIOD_ID
1366 when grouping(time.CAL_YEAR_ID) = 0 and
1367 grouping(time.CAL_QTR_ID) = 0 and
1368 grouping(time.CAL_PERIOD_ID) = 1
1369 then time.CAL_QTR_ID
1370 when grouping(time.CAL_YEAR_ID) = 0 and
1371 grouping(time.CAL_QTR_ID) = 1 and
1372 grouping(time.CAL_PERIOD_ID) = 1
1373 then time.CAL_YEAR_ID
1374 end,
1375 case when grouping(time.CAL_YEAR_ID) = 0 and
1376 grouping(time.CAL_QTR_ID) = 0 and
1377 grouping(time.CAL_PERIOD_ID) = 0
1378 then 32
1379 when grouping(time.CAL_YEAR_ID) = 0 and
1380 grouping(time.CAL_QTR_ID) = 0 and
1381 grouping(time.CAL_PERIOD_ID) = 1
1382 then 64
1383 when grouping(time.CAL_YEAR_ID) = 0 and
1384 grouping(time.CAL_QTR_ID) = 1 and
1385 grouping(time.CAL_PERIOD_ID) = 1
1386 then 128
1387 end,
1388 'P',
1389 act.CURR_RECORD_TYPE_ID,
1390 act.CURRENCY_CODE,
1391 sum(act.REVENUE),
1392 sum(act.FUNDING),
1393 sum(act.INITIAL_FUNDING_AMOUNT),
1394 sum(act.INITIAL_FUNDING_COUNT),
1395 sum(act.ADDITIONAL_FUNDING_AMOUNT),
1396 sum(act.ADDITIONAL_FUNDING_COUNT),
1397 sum(act.CANCELLED_FUNDING_AMOUNT),
1398 sum(act.CANCELLED_FUNDING_COUNT),
1399 sum(act.FUNDING_ADJUSTMENT_AMOUNT),
1400 sum(act.FUNDING_ADJUSTMENT_COUNT),
1401 sum(act.REVENUE_WRITEOFF),
1402 sum(act.AR_INVOICE_AMOUNT),
1403 sum(act.AR_INVOICE_COUNT),
1404 sum(act.AR_CASH_APPLIED_AMOUNT),
1405 sum(act.AR_CASH_APPLIED_COUNT),
1406 sum(act.AR_INVOICE_WRITEOFF_AMOUNT),
1407 sum(act.AR_INVOICE_WRITEOFF_COUNT),
1408 sum(act.AR_CREDIT_MEMO_AMOUNT),
1409 sum(act.AR_CREDIT_MEMO_COUNT),
1410 sum(act.UNBILLED_RECEIVABLES),
1411 sum(act.UNEARNED_REVENUE),
1412 sum(act.AR_UNAPPR_INVOICE_AMOUNT),
1413 sum(act.AR_UNAPPR_INVOICE_COUNT),
1414 sum(act.AR_APPR_INVOICE_AMOUNT),
1415 sum(act.AR_APPR_INVOICE_COUNT),
1416 sum(act.AR_AMOUNT_DUE),
1417 sum(act.AR_COUNT_DUE),
1418 sum(act.AR_AMOUNT_OVERDUE),
1419 sum(act.AR_COUNT_OVERDUE),
1420 sum(act.DORMANT_BACKLOG_INACTIV),
1421 sum(act.DORMANT_BACKLOG_START),
1422 sum(act.LOST_BACKLOG),
1423 sum(act.ACTIVE_BACKLOG),
1424 sum(act.REVENUE_AT_RISK)
1425 from
1426 FII_TIME_CAL_DAY_MV time,
1427 PJI_FM_AGGR_ACT3 act
1428 where
1429 act.PERIOD_TYPE_ID = 1 and
1430 act.CALENDAR_TYPE = 'P' and
1431 to_date(to_char(act.TIME_ID), 'J') = time.REPORT_DATE and
1432 act.PA_CALENDAR_ID = time.CALENDAR_ID
1433 group by
1434 act.PROJECT_ID,
1435 act.PROJECT_ORG_ID,
1436 act.PROJECT_ORGANIZATION_ID,
1437 rollup (time.CAL_YEAR_ID,
1438 time.CAL_QTR_ID,
1439 time.CAL_PERIOD_ID),
1440 act.CURR_RECORD_TYPE_ID,
1441 act.CURRENCY_CODE
1442 having
1443 not (grouping(time.CAL_YEAR_ID) = 1 and
1444 grouping(time.CAL_QTR_ID) = 1 and
1445 grouping(time.CAL_PERIOD_ID) = 1);
1446
1447 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1448 (
1449 l_process,
1450 'PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_PA(p_worker_id, ''' ||
1451 p_backlog_flag || ''');'
1452 );
1453
1454 commit;
1455
1456 end EXPAND_ACT_CAL_PA;
1457
1458
1459 -- -----------------------------------------------------
1460 -- procedure EXPAND_ACT_CAL_GL
1461 -- -----------------------------------------------------
1462 procedure EXPAND_ACT_CAL_GL (p_worker_id in number,
1463 p_backlog_flag in varchar2 default 'N') is
1464
1465 l_process varchar2(30);
1466
1467 begin
1468
1469 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1470 (
1471 PJI_RM_SUM_MAIN.g_process,
1472 'GL_CALENDAR_FLAG'
1473 ) = 'N') then
1474 return;
1475 end if;
1476
1477 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1478
1479 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1480 (
1481 l_process,
1482 'PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_GL(p_worker_id, ''' ||
1483 p_backlog_flag || ''');'
1484 )) then
1485 return;
1486 end if;
1487
1488 insert /*+ append parallel(act3_i) */ into PJI_FM_AGGR_ACT3 act3_i -- in EXPAND_ACT_CAL_GL
1489 (
1490 WORKER_ID,
1491 PROJECT_ID,
1492 PROJECT_ORG_ID,
1493 PROJECT_ORGANIZATION_ID,
1494 TIME_ID,
1495 PERIOD_TYPE_ID,
1496 CALENDAR_TYPE,
1497 CURR_RECORD_TYPE_ID,
1498 CURRENCY_CODE,
1499 REVENUE,
1500 FUNDING,
1501 INITIAL_FUNDING_AMOUNT,
1502 INITIAL_FUNDING_COUNT,
1503 ADDITIONAL_FUNDING_AMOUNT,
1504 ADDITIONAL_FUNDING_COUNT,
1505 CANCELLED_FUNDING_AMOUNT,
1506 CANCELLED_FUNDING_COUNT,
1507 FUNDING_ADJUSTMENT_AMOUNT,
1508 FUNDING_ADJUSTMENT_COUNT,
1509 REVENUE_WRITEOFF,
1510 AR_INVOICE_AMOUNT,
1511 AR_INVOICE_COUNT,
1512 AR_CASH_APPLIED_AMOUNT,
1513 AR_CASH_APPLIED_COUNT,
1514 AR_INVOICE_WRITEOFF_AMOUNT,
1515 AR_INVOICE_WRITEOFF_COUNT,
1516 AR_CREDIT_MEMO_AMOUNT,
1517 AR_CREDIT_MEMO_COUNT,
1518 UNBILLED_RECEIVABLES,
1519 UNEARNED_REVENUE,
1520 AR_UNAPPR_INVOICE_AMOUNT,
1521 AR_UNAPPR_INVOICE_COUNT,
1522 AR_APPR_INVOICE_AMOUNT,
1523 AR_APPR_INVOICE_COUNT,
1524 AR_AMOUNT_DUE,
1525 AR_COUNT_DUE,
1526 AR_AMOUNT_OVERDUE,
1527 AR_COUNT_OVERDUE,
1528 DORMANT_BACKLOG_INACTIV,
1529 DORMANT_BACKLOG_START,
1530 LOST_BACKLOG,
1531 ACTIVE_BACKLOG,
1532 REVENUE_AT_RISK
1533 )
1534 select
1535 p_worker_id,
1536 act.PROJECT_ID,
1537 act.PROJECT_ORG_ID,
1538 act.PROJECT_ORGANIZATION_ID,
1539 act.TIME_ID,
1540 act.PERIOD_TYPE_ID,
1541 act.CALENDAR_TYPE,
1542 decode(act.PERIOD_TYPE_ID,
1543 32, act.CURR_RECORD_TYPE_ID,
1544 bitand(act.CURR_RECORD_TYPE_ID, 247)) CURR_RECORD_TYPE_ID,
1545 act.CURRENCY_CODE,
1546 act.REVENUE,
1547 act.FUNDING,
1548 act.INITIAL_FUNDING_AMOUNT,
1549 act.INITIAL_FUNDING_COUNT,
1550 act.ADDITIONAL_FUNDING_AMOUNT,
1551 act.ADDITIONAL_FUNDING_COUNT,
1552 act.CANCELLED_FUNDING_AMOUNT,
1553 act.CANCELLED_FUNDING_COUNT,
1554 act.FUNDING_ADJUSTMENT_AMOUNT,
1555 act.FUNDING_ADJUSTMENT_COUNT,
1556 act.REVENUE_WRITEOFF,
1557 act.AR_INVOICE_AMOUNT,
1558 act.AR_INVOICE_COUNT,
1559 act.AR_CASH_APPLIED_AMOUNT,
1560 act.AR_CASH_APPLIED_COUNT,
1561 act.AR_INVOICE_WRITEOFF_AMOUNT,
1562 act.AR_INVOICE_WRITEOFF_COUNT,
1563 act.AR_CREDIT_MEMO_AMOUNT,
1564 act.AR_CREDIT_MEMO_COUNT,
1565 act.UNBILLED_RECEIVABLES,
1566 act.UNEARNED_REVENUE,
1567 act.AR_UNAPPR_INVOICE_AMOUNT,
1568 act.AR_UNAPPR_INVOICE_COUNT,
1569 act.AR_APPR_INVOICE_AMOUNT,
1570 act.AR_APPR_INVOICE_COUNT,
1571 act.AR_AMOUNT_DUE,
1572 act.AR_COUNT_DUE,
1573 act.AR_AMOUNT_OVERDUE,
1574 act.AR_COUNT_OVERDUE,
1575 act.DORMANT_BACKLOG_INACTIV,
1576 act.DORMANT_BACKLOG_START,
1577 act.LOST_BACKLOG,
1578 act.ACTIVE_BACKLOG,
1579 act.REVENUE_AT_RISK
1580 from
1581 (
1582 select /*+ ordered
1583 full(time) use_hash(time) parallel(time) swap_join_inputs(time)
1584 full(act) use_hash(act) parallel(act) */
1585 act.PROJECT_ID,
1586 act.PROJECT_ORG_ID,
1587 act.PROJECT_ORGANIZATION_ID,
1588 case when grouping(time.CAL_YEAR_ID) = 0 and
1589 grouping(time.CAL_QTR_ID) = 0 and
1590 grouping(time.CAL_PERIOD_ID) = 0
1591 then time.CAL_PERIOD_ID
1592 when grouping(time.CAL_YEAR_ID) = 0 and
1593 grouping(time.CAL_QTR_ID) = 0 and
1594 grouping(time.CAL_PERIOD_ID) = 1
1595 then time.CAL_QTR_ID
1596 when grouping(time.CAL_YEAR_ID) = 0 and
1597 grouping(time.CAL_QTR_ID) = 1 and
1598 grouping(time.CAL_PERIOD_ID) = 1
1599 then time.CAL_YEAR_ID
1600 end TIME_ID,
1601 case when grouping(time.CAL_YEAR_ID) = 0 and
1602 grouping(time.CAL_QTR_ID) = 0 and
1603 grouping(time.CAL_PERIOD_ID) = 0
1604 then 32
1605 when grouping(time.CAL_YEAR_ID) = 0 and
1606 grouping(time.CAL_QTR_ID) = 0 and
1607 grouping(time.CAL_PERIOD_ID) = 1
1608 then 64
1609 when grouping(time.CAL_YEAR_ID) = 0 and
1610 grouping(time.CAL_QTR_ID) = 1 and
1611 grouping(time.CAL_PERIOD_ID) = 1
1612 then 128
1613 end PERIOD_TYPE_ID,
1614 'G' CALENDAR_TYPE,
1615 act.CURR_RECORD_TYPE_ID,
1616 act.CURRENCY_CODE,
1617 sum(act.REVENUE) REVENUE,
1618 sum(act.FUNDING) FUNDING,
1619 sum(act.INITIAL_FUNDING_AMOUNT) INITIAL_FUNDING_AMOUNT,
1620 sum(act.INITIAL_FUNDING_COUNT) INITIAL_FUNDING_COUNT,
1621 sum(act.ADDITIONAL_FUNDING_AMOUNT) ADDITIONAL_FUNDING_AMOUNT,
1622 sum(act.ADDITIONAL_FUNDING_COUNT) ADDITIONAL_FUNDING_COUNT,
1623 sum(act.CANCELLED_FUNDING_AMOUNT) CANCELLED_FUNDING_AMOUNT,
1624 sum(act.CANCELLED_FUNDING_COUNT) CANCELLED_FUNDING_COUNT,
1625 sum(act.FUNDING_ADJUSTMENT_AMOUNT) FUNDING_ADJUSTMENT_AMOUNT,
1626 sum(act.FUNDING_ADJUSTMENT_COUNT) FUNDING_ADJUSTMENT_COUNT,
1627 sum(act.REVENUE_WRITEOFF) REVENUE_WRITEOFF,
1628 sum(act.AR_INVOICE_AMOUNT) AR_INVOICE_AMOUNT,
1629 sum(act.AR_INVOICE_COUNT) AR_INVOICE_COUNT,
1630 sum(act.AR_CASH_APPLIED_AMOUNT) AR_CASH_APPLIED_AMOUNT,
1631 sum(act.AR_CASH_APPLIED_COUNT) AR_CASH_APPLIED_COUNT,
1632 sum(act.AR_INVOICE_WRITEOFF_AMOUNT) AR_INVOICE_WRITEOFF_AMOUNT,
1633 sum(act.AR_INVOICE_WRITEOFF_COUNT) AR_INVOICE_WRITEOFF_COUNT,
1634 sum(act.AR_CREDIT_MEMO_AMOUNT) AR_CREDIT_MEMO_AMOUNT,
1635 sum(act.AR_CREDIT_MEMO_COUNT) AR_CREDIT_MEMO_COUNT,
1636 sum(act.UNBILLED_RECEIVABLES) UNBILLED_RECEIVABLES,
1637 sum(act.UNEARNED_REVENUE) UNEARNED_REVENUE,
1638 sum(act.AR_UNAPPR_INVOICE_AMOUNT) AR_UNAPPR_INVOICE_AMOUNT,
1639 sum(act.AR_UNAPPR_INVOICE_COUNT) AR_UNAPPR_INVOICE_COUNT,
1640 sum(act.AR_APPR_INVOICE_AMOUNT) AR_APPR_INVOICE_AMOUNT,
1641 sum(act.AR_APPR_INVOICE_COUNT) AR_APPR_INVOICE_COUNT,
1642 sum(act.AR_AMOUNT_DUE) AR_AMOUNT_DUE,
1643 sum(act.AR_COUNT_DUE) AR_COUNT_DUE,
1644 sum(act.AR_AMOUNT_OVERDUE) AR_AMOUNT_OVERDUE,
1645 sum(act.AR_COUNT_OVERDUE) AR_COUNT_OVERDUE,
1646 sum(act.DORMANT_BACKLOG_INACTIV) DORMANT_BACKLOG_INACTIV,
1647 sum(act.DORMANT_BACKLOG_START) DORMANT_BACKLOG_START,
1648 sum(act.LOST_BACKLOG) LOST_BACKLOG,
1649 sum(act.ACTIVE_BACKLOG) ACTIVE_BACKLOG,
1650 sum(act.REVENUE_AT_RISK) REVENUE_AT_RISK
1651 from
1652 FII_TIME_CAL_DAY_MV time,
1653 PJI_FM_AGGR_ACT3 act
1654 where
1655 act.PERIOD_TYPE_ID = 1 and
1656 act.CALENDAR_TYPE = 'C' and
1657 to_date(to_char(act.TIME_ID), 'J') = time.REPORT_DATE and
1658 act.GL_CALENDAR_ID = time.CALENDAR_ID
1659 group by
1660 act.PROJECT_ID,
1661 act.PROJECT_ORG_ID,
1662 act.PROJECT_ORGANIZATION_ID,
1663 rollup (time.CAL_YEAR_ID,
1664 time.CAL_QTR_ID,
1665 time.CAL_PERIOD_ID),
1666 act.CURR_RECORD_TYPE_ID,
1667 act.CURRENCY_CODE
1668 having
1669 not (grouping(time.CAL_YEAR_ID) = 1 and
1670 grouping(time.CAL_QTR_ID) = 1 and
1671 grouping(time.CAL_PERIOD_ID) = 1)
1672 ) act
1673 where
1674 not (act.CURR_RECORD_TYPE_ID in (8, 256) and
1675 act.PERIOD_TYPE_ID <> 32);
1676
1677 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1678 (
1679 l_process,
1680 'PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_GL(p_worker_id, ''' ||
1681 p_backlog_flag || ''');'
1682 );
1683
1684 commit;
1685
1686 end EXPAND_ACT_CAL_GL;
1687
1688
1689 -- -----------------------------------------------------
1690 -- procedure EXPAND_ACT_CAL_WK
1691 -- -----------------------------------------------------
1692 procedure EXPAND_ACT_CAL_WK (p_worker_id in number,
1693 p_backlog_flag in varchar2 default 'N') is
1694
1695 l_process varchar2(30);
1696
1697 begin
1698
1699 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1700
1701 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1702 (
1703 l_process,
1704 'PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_WK(p_worker_id, ''' ||
1705 p_backlog_flag || ''');'
1706 )) then
1707 return;
1708 end if;
1709
1710 insert /*+ append parallel(act3_i) */ into PJI_FM_AGGR_ACT3 act3_i -- in EXPAND_ACT_CAL_WK
1711 (
1712 WORKER_ID,
1713 PROJECT_ID,
1714 PROJECT_ORG_ID,
1715 PROJECT_ORGANIZATION_ID,
1716 TIME_ID,
1717 PERIOD_TYPE_ID,
1718 CALENDAR_TYPE,
1719 CURR_RECORD_TYPE_ID,
1720 CURRENCY_CODE,
1721 REVENUE,
1722 FUNDING,
1723 INITIAL_FUNDING_AMOUNT,
1724 INITIAL_FUNDING_COUNT,
1725 ADDITIONAL_FUNDING_AMOUNT,
1726 ADDITIONAL_FUNDING_COUNT,
1727 CANCELLED_FUNDING_AMOUNT,
1728 CANCELLED_FUNDING_COUNT,
1729 FUNDING_ADJUSTMENT_AMOUNT,
1730 FUNDING_ADJUSTMENT_COUNT,
1731 REVENUE_WRITEOFF,
1732 AR_INVOICE_AMOUNT,
1733 AR_INVOICE_COUNT,
1734 AR_CASH_APPLIED_AMOUNT,
1735 AR_CASH_APPLIED_COUNT,
1736 AR_INVOICE_WRITEOFF_AMOUNT,
1737 AR_INVOICE_WRITEOFF_COUNT,
1738 AR_CREDIT_MEMO_AMOUNT,
1739 AR_CREDIT_MEMO_COUNT,
1740 UNBILLED_RECEIVABLES,
1741 UNEARNED_REVENUE,
1742 AR_UNAPPR_INVOICE_AMOUNT,
1743 AR_UNAPPR_INVOICE_COUNT,
1744 AR_APPR_INVOICE_AMOUNT,
1745 AR_APPR_INVOICE_COUNT,
1746 AR_AMOUNT_DUE,
1747 AR_COUNT_DUE,
1748 AR_AMOUNT_OVERDUE,
1749 AR_COUNT_OVERDUE,
1750 DORMANT_BACKLOG_INACTIV,
1751 DORMANT_BACKLOG_START,
1752 LOST_BACKLOG,
1753 ACTIVE_BACKLOG,
1754 REVENUE_AT_RISK
1755 )
1756 select /*+ ordered
1757 full(time) use_hash(time) swap_join_inputs(time)
1758 full(act) use_hash(act) parallel(act) */
1759 p_worker_id,
1760 act.PROJECT_ID,
1761 act.PROJECT_ORG_ID,
1762 act.PROJECT_ORGANIZATION_ID,
1763 time.WEEK_ID TIME_ID,
1764 16,
1765 'E',
1766 bitand(act.CURR_RECORD_TYPE_ID, 247) CURR_RECORD_TYPE_ID,
1767 act.CURRENCY_CODE,
1768 sum(act.REVENUE),
1769 sum(act.FUNDING),
1770 sum(act.INITIAL_FUNDING_AMOUNT),
1771 sum(act.INITIAL_FUNDING_COUNT),
1772 sum(act.ADDITIONAL_FUNDING_AMOUNT),
1773 sum(act.ADDITIONAL_FUNDING_COUNT),
1774 sum(act.CANCELLED_FUNDING_AMOUNT),
1775 sum(act.CANCELLED_FUNDING_COUNT),
1776 sum(act.FUNDING_ADJUSTMENT_AMOUNT),
1777 sum(act.FUNDING_ADJUSTMENT_COUNT),
1778 sum(act.REVENUE_WRITEOFF),
1779 sum(act.AR_INVOICE_AMOUNT),
1780 sum(act.AR_INVOICE_COUNT),
1781 sum(act.AR_CASH_APPLIED_AMOUNT),
1782 sum(act.AR_CASH_APPLIED_COUNT),
1783 sum(act.AR_INVOICE_WRITEOFF_AMOUNT),
1784 sum(act.AR_INVOICE_WRITEOFF_COUNT),
1785 sum(act.AR_CREDIT_MEMO_AMOUNT),
1786 sum(act.AR_CREDIT_MEMO_COUNT),
1787 sum(act.UNBILLED_RECEIVABLES),
1788 sum(act.UNEARNED_REVENUE),
1789 sum(act.AR_UNAPPR_INVOICE_AMOUNT),
1790 sum(act.AR_UNAPPR_INVOICE_COUNT),
1791 sum(act.AR_APPR_INVOICE_AMOUNT),
1792 sum(act.AR_APPR_INVOICE_COUNT),
1793 sum(act.AR_AMOUNT_DUE),
1794 sum(act.AR_COUNT_DUE),
1795 sum(act.AR_AMOUNT_OVERDUE),
1796 sum(act.AR_COUNT_OVERDUE),
1797 sum(act.DORMANT_BACKLOG_INACTIV),
1798 sum(act.DORMANT_BACKLOG_START),
1799 sum(act.LOST_BACKLOG),
1800 sum(act.ACTIVE_BACKLOG),
1801 sum(act.REVENUE_AT_RISK)
1802 from
1803 FII_TIME_DAY time,
1804 PJI_FM_AGGR_ACT3 act
1805 where
1806 act.PERIOD_TYPE_ID = 1 and
1807 act.CALENDAR_TYPE = 'C' and
1808 act.CURR_RECORD_TYPE_ID not in (8, 256) and
1809 act.TIME_ID = time.REPORT_DATE_JULIAN
1810 group by
1811 act.PROJECT_ID,
1812 act.PROJECT_ORG_ID,
1813 act.PROJECT_ORGANIZATION_ID,
1814 time.WEEK_ID,
1815 bitand(act.CURR_RECORD_TYPE_ID, 247),
1816 act.CURRENCY_CODE;
1817
1818 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1819 (
1820 l_process,
1821 'PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_WK(p_worker_id, ''' ||
1822 p_backlog_flag || ''');'
1823 );
1824
1825 commit;
1826
1827 end EXPAND_ACT_CAL_WK;
1828
1829
1830 -- -----------------------------------------------------
1831 -- procedure MERGE_ACT_INTO_ACP
1832 -- -----------------------------------------------------
1833 procedure MERGE_ACT_INTO_ACP (p_worker_id in number,
1834 p_backlog_flag in varchar2 default 'N') is
1835
1836 l_process varchar2(30);
1837 l_extraction_type varchar2(30);
1838 l_last_update_date date;
1839 l_last_updated_by number;
1840 l_creation_date date;
1841 l_created_by number;
1842 l_last_update_login number;
1843 l_schema varchar2(30);
1844
1845 begin
1846
1847 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1848
1849 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1850 (
1851 l_process,
1852 'PJI_FM_SUM_ROLLUP_ACT.MERGE_ACT_INTO_ACP(p_worker_id, ''' ||
1853 p_backlog_flag ||
1854 ''');'
1855 )) then
1856 return;
1857 end if;
1858
1859 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1860 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
1861
1862 l_last_update_date := sysdate;
1863 l_last_updated_by := FND_GLOBAL.USER_ID;
1864 l_creation_date := sysdate;
1865 l_created_by := FND_GLOBAL.USER_ID;
1866 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1867
1868 if (l_extraction_type = 'FULL' and nvl(p_backlog_flag, 'N') = 'N') then
1869
1870 insert /*+ append parallel(acp) */ into PJI_AC_PROJ_F acp
1871 (
1872 PROJECT_ORG_ID,
1873 PROJECT_ORGANIZATION_ID,
1874 TIME_ID,
1875 PROJECT_ID,
1876 PERIOD_TYPE_ID,
1877 CALENDAR_TYPE,
1878 CURR_RECORD_TYPE_ID,
1879 CURRENCY_CODE,
1880 REVENUE,
1881 INITIAL_FUNDING_AMOUNT,
1882 INITIAL_FUNDING_COUNT,
1883 ADDITIONAL_FUNDING_AMOUNT,
1884 ADDITIONAL_FUNDING_COUNT,
1885 CANCELLED_FUNDING_AMOUNT,
1886 CANCELLED_FUNDING_COUNT,
1887 FUNDING_ADJUSTMENT_AMOUNT,
1888 FUNDING_ADJUSTMENT_COUNT,
1889 REVENUE_WRITEOFF,
1890 AR_INVOICE_AMOUNT,
1891 AR_INVOICE_COUNT,
1892 AR_CASH_APPLIED_AMOUNT,
1893 AR_INVOICE_WRITEOFF_AMOUNT,
1894 AR_INVOICE_WRITEOFF_COUNT,
1895 AR_CREDIT_MEMO_AMOUNT,
1896 AR_CREDIT_MEMO_COUNT,
1897 UNBILLED_RECEIVABLES,
1898 UNEARNED_REVENUE,
1899 AR_UNAPPR_INVOICE_AMOUNT,
1900 AR_UNAPPR_INVOICE_COUNT,
1901 AR_APPR_INVOICE_AMOUNT,
1902 AR_APPR_INVOICE_COUNT,
1903 AR_AMOUNT_DUE,
1904 AR_COUNT_DUE,
1905 AR_AMOUNT_OVERDUE,
1906 AR_COUNT_OVERDUE,
1907 DORMANT_BACKLOG_INACTIV,
1908 DORMANT_BACKLOG_START,
1909 LOST_BACKLOG,
1910 ACTIVE_BACKLOG,
1911 REVENUE_AT_RISK,
1912 LAST_UPDATE_DATE,
1913 LAST_UPDATED_BY,
1914 CREATION_DATE,
1915 CREATED_BY,
1916 LAST_UPDATE_LOGIN
1917 )
1918 select /*+ full(act) parallel(act) */
1919 PROJECT_ORG_ID,
1920 PROJECT_ORGANIZATION_ID,
1921 TIME_ID,
1922 PROJECT_ID,
1923 PERIOD_TYPE_ID,
1924 CALENDAR_TYPE,
1925 CURR_RECORD_TYPE_ID,
1926 CURRENCY_CODE,
1927 REVENUE,
1928 INITIAL_FUNDING_AMOUNT,
1929 INITIAL_FUNDING_COUNT,
1930 ADDITIONAL_FUNDING_AMOUNT,
1931 ADDITIONAL_FUNDING_COUNT,
1932 CANCELLED_FUNDING_AMOUNT,
1933 CANCELLED_FUNDING_COUNT,
1934 FUNDING_ADJUSTMENT_AMOUNT,
1935 FUNDING_ADJUSTMENT_COUNT,
1936 REVENUE_WRITEOFF,
1937 AR_INVOICE_AMOUNT,
1938 AR_INVOICE_COUNT,
1939 AR_CASH_APPLIED_AMOUNT,
1940 AR_INVOICE_WRITEOFF_AMOUNT,
1941 AR_INVOICE_WRITEOFF_COUNT,
1942 AR_CREDIT_MEMO_AMOUNT,
1943 AR_CREDIT_MEMO_COUNT,
1944 UNBILLED_RECEIVABLES,
1945 UNEARNED_REVENUE,
1946 AR_UNAPPR_INVOICE_AMOUNT,
1947 AR_UNAPPR_INVOICE_COUNT,
1948 AR_APPR_INVOICE_AMOUNT,
1949 AR_APPR_INVOICE_COUNT,
1950 AR_AMOUNT_DUE,
1951 AR_COUNT_DUE,
1952 AR_AMOUNT_OVERDUE,
1953 AR_COUNT_OVERDUE,
1954 DORMANT_BACKLOG_INACTIV,
1955 DORMANT_BACKLOG_START,
1956 LOST_BACKLOG,
1957 ACTIVE_BACKLOG,
1958 REVENUE_AT_RISK,
1959 l_last_update_date,
1960 l_last_updated_by,
1961 l_creation_date,
1962 l_created_by,
1963 l_last_update_login
1964 from
1965 PJI_FM_AGGR_ACT3 act
1966 where
1967 (nvl(REVENUE , 0) <> 0 or
1968 nvl(INITIAL_FUNDING_AMOUNT , 0) <> 0 or
1969 nvl(INITIAL_FUNDING_COUNT , 0) <> 0 or
1970 nvl(ADDITIONAL_FUNDING_AMOUNT , 0) <> 0 or
1971 nvl(ADDITIONAL_FUNDING_COUNT , 0) <> 0 or
1972 nvl(CANCELLED_FUNDING_AMOUNT , 0) <> 0 or
1973 nvl(CANCELLED_FUNDING_COUNT , 0) <> 0 or
1974 nvl(FUNDING_ADJUSTMENT_AMOUNT , 0) <> 0 or
1975 nvl(FUNDING_ADJUSTMENT_COUNT , 0) <> 0 or
1976 nvl(REVENUE_WRITEOFF , 0) <> 0 or
1977 nvl(AR_INVOICE_AMOUNT , 0) <> 0 or
1978 nvl(AR_INVOICE_COUNT , 0) <> 0 or
1979 nvl(AR_CASH_APPLIED_AMOUNT , 0) <> 0 or
1980 nvl(AR_INVOICE_WRITEOFF_AMOUNT, 0) <> 0 or
1981 nvl(AR_INVOICE_WRITEOFF_COUNT , 0) <> 0 or
1982 nvl(AR_CREDIT_MEMO_AMOUNT , 0) <> 0 or
1983 nvl(AR_CREDIT_MEMO_COUNT , 0) <> 0 or
1984 nvl(UNBILLED_RECEIVABLES , 0) <> 0 or
1985 nvl(UNEARNED_REVENUE , 0) <> 0 or
1986 nvl(AR_UNAPPR_INVOICE_AMOUNT , 0) <> 0 or
1987 nvl(AR_UNAPPR_INVOICE_COUNT , 0) <> 0 or
1988 nvl(AR_APPR_INVOICE_AMOUNT , 0) <> 0 or
1989 nvl(AR_APPR_INVOICE_COUNT , 0) <> 0 or
1990 nvl(AR_AMOUNT_DUE , 0) <> 0 or
1991 nvl(AR_COUNT_DUE , 0) <> 0 or
1992 nvl(AR_AMOUNT_OVERDUE , 0) <> 0 or
1993 nvl(AR_COUNT_OVERDUE , 0) <> 0 or
1994 nvl(DORMANT_BACKLOG_INACTIV , 0) <> 0 or
1995 nvl(DORMANT_BACKLOG_START , 0) <> 0 or
1996 nvl(LOST_BACKLOG , 0) <> 0 or
1997 nvl(ACTIVE_BACKLOG , 0) <> 0 or
1998 nvl(REVENUE_AT_RISK , 0) <> 0);
1999
2000 else -- not initial data load
2001
2002 merge /*+ parallel(acp) */ into PJI_AC_PROJ_F acp
2003 using
2004 (
2005 select
2006 PROJECT_ORG_ID,
2007 PROJECT_ORGANIZATION_ID,
2008 TIME_ID,
2009 PROJECT_ID,
2010 PERIOD_TYPE_ID,
2011 CALENDAR_TYPE,
2012 CURR_RECORD_TYPE_ID,
2013 CURRENCY_CODE,
2014 REVENUE,
2015 INITIAL_FUNDING_AMOUNT,
2016 INITIAL_FUNDING_COUNT,
2017 ADDITIONAL_FUNDING_AMOUNT,
2018 ADDITIONAL_FUNDING_COUNT,
2019 CANCELLED_FUNDING_AMOUNT,
2020 CANCELLED_FUNDING_COUNT,
2021 FUNDING_ADJUSTMENT_AMOUNT,
2022 FUNDING_ADJUSTMENT_COUNT,
2023 REVENUE_WRITEOFF,
2024 AR_INVOICE_AMOUNT,
2025 AR_INVOICE_COUNT,
2026 AR_CASH_APPLIED_AMOUNT,
2027 AR_INVOICE_WRITEOFF_AMOUNT,
2028 AR_INVOICE_WRITEOFF_COUNT,
2029 AR_CREDIT_MEMO_AMOUNT,
2030 AR_CREDIT_MEMO_COUNT,
2031 UNBILLED_RECEIVABLES,
2032 UNEARNED_REVENUE,
2033 AR_UNAPPR_INVOICE_AMOUNT,
2034 AR_UNAPPR_INVOICE_COUNT,
2035 AR_APPR_INVOICE_AMOUNT,
2036 AR_APPR_INVOICE_COUNT,
2037 AR_AMOUNT_DUE,
2038 AR_COUNT_DUE,
2039 AR_AMOUNT_OVERDUE,
2040 AR_COUNT_OVERDUE,
2041 DORMANT_BACKLOG_INACTIV,
2042 DORMANT_BACKLOG_START,
2043 LOST_BACKLOG,
2044 ACTIVE_BACKLOG,
2045 REVENUE_AT_RISK,
2046 l_last_update_date LAST_UPDATE_DATE,
2047 l_last_updated_by LAST_UPDATED_BY,
2048 l_creation_date CREATION_DATE,
2049 l_created_by CREATED_BY,
2050 l_last_update_login LAST_UPDATE_LOGIN
2051 from
2052 (
2053 select
2054 PROJECT_ORG_ID,
2055 PROJECT_ORGANIZATION_ID,
2056 TIME_ID,
2057 PROJECT_ID,
2058 PERIOD_TYPE_ID,
2059 CALENDAR_TYPE,
2060 CURR_RECORD_TYPE_ID,
2061 CURRENCY_CODE,
2062 sum(REVENUE) REVENUE,
2063 sum(INITIAL_FUNDING_AMOUNT) INITIAL_FUNDING_AMOUNT,
2064 sum(INITIAL_FUNDING_COUNT) INITIAL_FUNDING_COUNT,
2065 sum(ADDITIONAL_FUNDING_AMOUNT) ADDITIONAL_FUNDING_AMOUNT,
2066 sum(ADDITIONAL_FUNDING_COUNT) ADDITIONAL_FUNDING_COUNT,
2067 sum(CANCELLED_FUNDING_AMOUNT) CANCELLED_FUNDING_AMOUNT,
2068 sum(CANCELLED_FUNDING_COUNT) CANCELLED_FUNDING_COUNT,
2069 sum(FUNDING_ADJUSTMENT_AMOUNT) FUNDING_ADJUSTMENT_AMOUNT,
2070 sum(FUNDING_ADJUSTMENT_COUNT) FUNDING_ADJUSTMENT_COUNT,
2071 sum(REVENUE_WRITEOFF) REVENUE_WRITEOFF,
2072 sum(AR_INVOICE_AMOUNT) AR_INVOICE_AMOUNT,
2073 sum(AR_INVOICE_COUNT) AR_INVOICE_COUNT,
2074 sum(AR_CASH_APPLIED_AMOUNT) AR_CASH_APPLIED_AMOUNT,
2075 sum(AR_INVOICE_WRITEOFF_AMOUNT) AR_INVOICE_WRITEOFF_AMOUNT,
2076 sum(AR_INVOICE_WRITEOFF_COUNT) AR_INVOICE_WRITEOFF_COUNT,
2077 sum(AR_CREDIT_MEMO_AMOUNT) AR_CREDIT_MEMO_AMOUNT,
2078 sum(AR_CREDIT_MEMO_COUNT) AR_CREDIT_MEMO_COUNT,
2079 sum(UNBILLED_RECEIVABLES) UNBILLED_RECEIVABLES,
2080 sum(UNEARNED_REVENUE) UNEARNED_REVENUE,
2081 sum(AR_UNAPPR_INVOICE_AMOUNT) AR_UNAPPR_INVOICE_AMOUNT,
2082 sum(AR_UNAPPR_INVOICE_COUNT) AR_UNAPPR_INVOICE_COUNT,
2083 sum(AR_APPR_INVOICE_AMOUNT) AR_APPR_INVOICE_AMOUNT,
2084 sum(AR_APPR_INVOICE_COUNT) AR_APPR_INVOICE_COUNT,
2085 sum(AR_AMOUNT_DUE) AR_AMOUNT_DUE,
2086 sum(AR_COUNT_DUE) AR_COUNT_DUE,
2087 sum(AR_AMOUNT_OVERDUE) AR_AMOUNT_OVERDUE,
2088 sum(AR_COUNT_OVERDUE) AR_COUNT_OVERDUE,
2089 sum(DORMANT_BACKLOG_INACTIV) DORMANT_BACKLOG_INACTIV,
2090 sum(DORMANT_BACKLOG_START) DORMANT_BACKLOG_START,
2091 sum(LOST_BACKLOG) LOST_BACKLOG,
2092 sum(ACTIVE_BACKLOG) ACTIVE_BACKLOG,
2093 sum(REVENUE_AT_RISK) REVENUE_AT_RISK
2094 from
2095 (
2096 select /*+ full(act) parallel(act) */
2097 PROJECT_ORG_ID,
2098 PROJECT_ORGANIZATION_ID,
2099 TIME_ID,
2100 PROJECT_ID,
2101 PERIOD_TYPE_ID,
2102 CALENDAR_TYPE,
2103 CURR_RECORD_TYPE_ID,
2104 CURRENCY_CODE,
2105 REVENUE,
2106 INITIAL_FUNDING_AMOUNT,
2107 INITIAL_FUNDING_COUNT,
2108 ADDITIONAL_FUNDING_AMOUNT,
2109 ADDITIONAL_FUNDING_COUNT,
2110 CANCELLED_FUNDING_AMOUNT,
2111 CANCELLED_FUNDING_COUNT,
2112 FUNDING_ADJUSTMENT_AMOUNT,
2113 FUNDING_ADJUSTMENT_COUNT,
2114 REVENUE_WRITEOFF,
2115 AR_INVOICE_AMOUNT,
2116 AR_INVOICE_COUNT,
2117 AR_CASH_APPLIED_AMOUNT,
2118 AR_INVOICE_WRITEOFF_AMOUNT,
2119 AR_INVOICE_WRITEOFF_COUNT,
2120 AR_CREDIT_MEMO_AMOUNT,
2121 AR_CREDIT_MEMO_COUNT,
2122 UNBILLED_RECEIVABLES,
2123 UNEARNED_REVENUE,
2124 AR_UNAPPR_INVOICE_AMOUNT,
2125 AR_UNAPPR_INVOICE_COUNT,
2126 AR_APPR_INVOICE_AMOUNT,
2127 AR_APPR_INVOICE_COUNT,
2128 AR_AMOUNT_DUE,
2129 AR_COUNT_DUE,
2130 AR_AMOUNT_OVERDUE,
2131 AR_COUNT_OVERDUE,
2132 DORMANT_BACKLOG_INACTIV,
2133 DORMANT_BACKLOG_START,
2134 LOST_BACKLOG,
2135 ACTIVE_BACKLOG,
2136 REVENUE_AT_RISK
2137 from
2138 PJI_FM_AGGR_ACT3 act
2139 union all -- partial refresh
2140 select /*+ ordered full(map) parallel(map)
2141 index(acp, PJI_AC_PROJ_F_N2) use_nl(acp) */
2142 acp.PROJECT_ORG_ID,
2143 acp.PROJECT_ORGANIZATION_ID,
2144 acp.TIME_ID,
2145 acp.PROJECT_ID,
2146 acp.PERIOD_TYPE_ID,
2147 acp.CALENDAR_TYPE,
2148 acp.CURR_RECORD_TYPE_ID,
2149 acp.CURRENCY_CODE,
2150 -acp.REVENUE,
2151 -acp.INITIAL_FUNDING_AMOUNT,
2152 -acp.INITIAL_FUNDING_COUNT,
2153 -acp.ADDITIONAL_FUNDING_AMOUNT,
2154 -acp.ADDITIONAL_FUNDING_COUNT,
2155 -acp.CANCELLED_FUNDING_AMOUNT,
2156 -acp.CANCELLED_FUNDING_COUNT,
2157 -acp.FUNDING_ADJUSTMENT_AMOUNT,
2158 -acp.FUNDING_ADJUSTMENT_COUNT,
2159 -acp.REVENUE_WRITEOFF,
2160 -acp.AR_INVOICE_AMOUNT,
2161 -acp.AR_INVOICE_COUNT,
2162 to_number(null) AR_CASH_APPLIED_AMOUNT,
2163 -acp.AR_INVOICE_WRITEOFF_AMOUNT,
2164 -acp.AR_INVOICE_WRITEOFF_COUNT,
2165 -acp.AR_CREDIT_MEMO_AMOUNT,
2166 -acp.AR_CREDIT_MEMO_COUNT,
2167 -acp.UNBILLED_RECEIVABLES,
2168 -acp.UNEARNED_REVENUE,
2169 to_number(null) AR_UNAPPR_INVOICE_AMOUNT,
2170 to_number(null) AR_UNAPPR_INVOICE_COUNT,
2171 to_number(null) AR_APPR_INVOICE_AMOUNT,
2172 to_number(null) AR_APPR_INVOICE_COUNT,
2173 to_number(null) AR_AMOUNT_DUE,
2174 to_number(null) AR_COUNT_DUE,
2175 to_number(null) AR_AMOUNT_OVERDUE,
2176 to_number(null) AR_COUNT_OVERDUE,
2177 to_number(null) DORMANT_BACKLOG_INACTIV,
2178 to_number(null) DORMANT_BACKLOG_START,
2179 to_number(null) LOST_BACKLOG,
2180 to_number(null) ACTIVE_BACKLOG,
2181 to_number(null) REVENUE_AT_RISK
2182 from
2183 PJI_PJI_PROJ_BATCH_MAP map,
2184 PJI_AC_PROJ_F acp
2185 where
2186 nvl(p_backlog_flag, 'N') = 'N' and
2187 l_extraction_type = 'PARTIAL' and
2188 map.WORKER_ID = p_worker_id and
2189 map.EXTRACTION_TYPE = 'P' and
2190 acp.PROJECT_ID = map.PROJECT_ID
2191 )
2192 group by
2193 PROJECT_ORG_ID,
2194 PROJECT_ORGANIZATION_ID,
2195 TIME_ID,
2196 PROJECT_ID,
2197 PERIOD_TYPE_ID,
2198 CALENDAR_TYPE,
2199 CURR_RECORD_TYPE_ID,
2200 CURRENCY_CODE
2201 )
2202 where
2203 nvl(REVENUE , 0) <> 0 or
2204 nvl(INITIAL_FUNDING_AMOUNT , 0) <> 0 or
2205 nvl(INITIAL_FUNDING_COUNT , 0) <> 0 or
2206 nvl(ADDITIONAL_FUNDING_AMOUNT , 0) <> 0 or
2207 nvl(ADDITIONAL_FUNDING_COUNT , 0) <> 0 or
2208 nvl(CANCELLED_FUNDING_AMOUNT , 0) <> 0 or
2209 nvl(CANCELLED_FUNDING_COUNT , 0) <> 0 or
2210 nvl(FUNDING_ADJUSTMENT_AMOUNT , 0) <> 0 or
2211 nvl(FUNDING_ADJUSTMENT_COUNT , 0) <> 0 or
2212 nvl(REVENUE_WRITEOFF , 0) <> 0 or
2213 nvl(AR_INVOICE_AMOUNT , 0) <> 0 or
2214 nvl(AR_INVOICE_COUNT , 0) <> 0 or
2215 nvl(AR_CASH_APPLIED_AMOUNT , 0) <> 0 or
2216 nvl(AR_INVOICE_WRITEOFF_AMOUNT, 0) <> 0 or
2217 nvl(AR_INVOICE_WRITEOFF_COUNT , 0) <> 0 or
2218 nvl(AR_CREDIT_MEMO_AMOUNT , 0) <> 0 or
2219 nvl(AR_CREDIT_MEMO_COUNT , 0) <> 0 or
2220 nvl(UNBILLED_RECEIVABLES , 0) <> 0 or
2221 nvl(UNEARNED_REVENUE , 0) <> 0 or
2222 nvl(AR_UNAPPR_INVOICE_AMOUNT , 0) <> 0 or
2223 nvl(AR_UNAPPR_INVOICE_COUNT , 0) <> 0 or
2224 nvl(AR_APPR_INVOICE_AMOUNT , 0) <> 0 or
2225 nvl(AR_APPR_INVOICE_COUNT , 0) <> 0 or
2226 nvl(AR_AMOUNT_DUE , 0) <> 0 or
2227 nvl(AR_COUNT_DUE , 0) <> 0 or
2228 nvl(AR_AMOUNT_OVERDUE , 0) <> 0 or
2229 nvl(AR_COUNT_OVERDUE , 0) <> 0 or
2230 nvl(DORMANT_BACKLOG_INACTIV , 0) <> 0 or
2231 nvl(DORMANT_BACKLOG_START , 0) <> 0 or
2232 nvl(LOST_BACKLOG , 0) <> 0 or
2233 nvl(ACTIVE_BACKLOG , 0) <> 0 or
2234 nvl(REVENUE_AT_RISK , 0) <> 0
2235 ) act
2236 on
2237 (
2238 act.PROJECT_ORG_ID = acp.PROJECT_ORG_ID and
2239 act.PROJECT_ORGANIZATION_ID = acp.PROJECT_ORGANIZATION_ID and
2240 act.TIME_ID = acp.TIME_ID and
2241 act.PROJECT_ID = acp.PROJECT_ID and
2242 act.PERIOD_TYPE_ID = acp.PERIOD_TYPE_ID and
2243 act.CALENDAR_TYPE = acp.CALENDAR_TYPE and
2244 act.CURR_RECORD_TYPE_ID = acp.CURR_RECORD_TYPE_ID and
2245 act.CURRENCY_CODE = acp.CURRENCY_CODE
2246 )
2247 when matched then update set
2248 acp.REVENUE = case when acp.REVENUE is null and
2249 act.REVENUE is null
2250 then to_number(null)
2251 else nvl(acp.REVENUE, 0) +
2252 nvl(act.REVENUE, 0)
2253 end,
2254 acp.INITIAL_FUNDING_AMOUNT
2255 = case when acp.INITIAL_FUNDING_AMOUNT is null and
2256 act.INITIAL_FUNDING_AMOUNT is null
2257 then to_number(null)
2258 else nvl(acp.INITIAL_FUNDING_AMOUNT, 0) +
2259 nvl(act.INITIAL_FUNDING_AMOUNT, 0)
2260 end,
2261 acp.INITIAL_FUNDING_COUNT
2262 = case when acp.INITIAL_FUNDING_COUNT is null and
2263 act.INITIAL_FUNDING_COUNT is null
2264 then to_number(null)
2265 else nvl(acp.INITIAL_FUNDING_COUNT, 0) +
2266 nvl(act.INITIAL_FUNDING_COUNT, 0)
2267 end,
2268 acp.ADDITIONAL_FUNDING_AMOUNT
2269 = case when acp.ADDITIONAL_FUNDING_AMOUNT is null and
2270 act.ADDITIONAL_FUNDING_AMOUNT is null
2271 then to_number(null)
2272 else nvl(acp.ADDITIONAL_FUNDING_AMOUNT, 0) +
2273 nvl(act.ADDITIONAL_FUNDING_AMOUNT, 0)
2274 end,
2275 acp.ADDITIONAL_FUNDING_COUNT
2276 = case when acp.ADDITIONAL_FUNDING_COUNT is null and
2277 act.ADDITIONAL_FUNDING_COUNT is null
2278 then to_number(null)
2279 else nvl(acp.ADDITIONAL_FUNDING_COUNT, 0) +
2280 nvl(act.ADDITIONAL_FUNDING_COUNT, 0)
2281 end,
2282 acp.CANCELLED_FUNDING_AMOUNT
2283 = case when acp.CANCELLED_FUNDING_AMOUNT is null and
2284 act.CANCELLED_FUNDING_AMOUNT is null
2285 then to_number(null)
2286 else nvl(acp.CANCELLED_FUNDING_AMOUNT, 0) +
2287 nvl(act.CANCELLED_FUNDING_AMOUNT, 0)
2288 end,
2289 acp.CANCELLED_FUNDING_COUNT
2290 = case when acp.CANCELLED_FUNDING_COUNT is null and
2291 act.CANCELLED_FUNDING_COUNT is null
2292 then to_number(null)
2293 else nvl(acp.CANCELLED_FUNDING_COUNT, 0) +
2294 nvl(act.CANCELLED_FUNDING_COUNT, 0)
2295 end,
2296 acp.FUNDING_ADJUSTMENT_AMOUNT
2297 = case when acp.FUNDING_ADJUSTMENT_AMOUNT is null and
2298 act.FUNDING_ADJUSTMENT_AMOUNT is null
2299 then to_number(null)
2300 else nvl(acp.FUNDING_ADJUSTMENT_AMOUNT, 0) +
2301 nvl(act.FUNDING_ADJUSTMENT_AMOUNT, 0)
2302 end,
2303 acp.FUNDING_ADJUSTMENT_COUNT
2304 = case when acp.FUNDING_ADJUSTMENT_COUNT is null and
2305 act.FUNDING_ADJUSTMENT_COUNT is null
2306 then to_number(null)
2307 else nvl(acp.FUNDING_ADJUSTMENT_COUNT, 0) +
2308 nvl(act.FUNDING_ADJUSTMENT_COUNT, 0)
2309 end,
2310 acp.REVENUE_WRITEOFF
2311 = case when acp.REVENUE_WRITEOFF is null and
2312 act.REVENUE_WRITEOFF is null
2313 then to_number(null)
2314 else nvl(acp.REVENUE_WRITEOFF, 0) +
2315 nvl(act.REVENUE_WRITEOFF, 0)
2316 end,
2317 acp.AR_INVOICE_AMOUNT
2318 = case when acp.AR_INVOICE_AMOUNT is null and
2319 act.AR_INVOICE_AMOUNT is null
2320 then to_number(null)
2321 else nvl(acp.AR_INVOICE_AMOUNT, 0) +
2322 nvl(act.AR_INVOICE_AMOUNT, 0)
2323 end,
2324 acp.AR_INVOICE_COUNT
2325 = case when acp.AR_INVOICE_COUNT is null and
2326 act.AR_INVOICE_COUNT is null
2327 then to_number(null)
2328 else nvl(acp.AR_INVOICE_COUNT, 0) +
2329 nvl(act.AR_INVOICE_COUNT, 0)
2330 end,
2331 acp.AR_CASH_APPLIED_AMOUNT
2332 = case when acp.AR_CASH_APPLIED_AMOUNT is null and
2333 act.AR_CASH_APPLIED_AMOUNT is null
2334 then to_number(null)
2335 else nvl(acp.AR_CASH_APPLIED_AMOUNT, 0) +
2336 nvl(act.AR_CASH_APPLIED_AMOUNT, 0)
2337 end,
2338 acp.AR_INVOICE_WRITEOFF_AMOUNT
2339 = case when acp.AR_INVOICE_WRITEOFF_AMOUNT is null and
2340 act.AR_INVOICE_WRITEOFF_AMOUNT is null
2341 then to_number(null)
2342 else nvl(acp.AR_INVOICE_WRITEOFF_AMOUNT, 0) +
2343 nvl(act.AR_INVOICE_WRITEOFF_AMOUNT, 0)
2344 end,
2345 acp.AR_INVOICE_WRITEOFF_COUNT
2346 = case when acp.AR_INVOICE_WRITEOFF_COUNT is null and
2347 act.AR_INVOICE_WRITEOFF_COUNT is null
2348 then to_number(null)
2349 else nvl(acp.AR_INVOICE_WRITEOFF_COUNT, 0) +
2350 nvl(act.AR_INVOICE_WRITEOFF_COUNT, 0)
2351 end,
2352 acp.AR_CREDIT_MEMO_AMOUNT
2353 = case when acp.AR_CREDIT_MEMO_AMOUNT is null and
2354 act.AR_CREDIT_MEMO_AMOUNT is null
2355 then to_number(null)
2356 else nvl(acp.AR_CREDIT_MEMO_AMOUNT, 0) +
2357 nvl(act.AR_CREDIT_MEMO_AMOUNT, 0)
2358 end,
2359 acp.AR_CREDIT_MEMO_COUNT
2360 = case when acp.AR_CREDIT_MEMO_COUNT is null and
2361 act.AR_CREDIT_MEMO_COUNT is null
2362 then to_number(null)
2363 else nvl(acp.AR_CREDIT_MEMO_COUNT, 0) +
2364 nvl(act.AR_CREDIT_MEMO_COUNT, 0)
2365 end,
2366 acp.UNBILLED_RECEIVABLES
2367 = case when acp.UNBILLED_RECEIVABLES is null and
2368 act.UNBILLED_RECEIVABLES is null
2369 then to_number(null)
2370 else nvl(acp.UNBILLED_RECEIVABLES, 0) +
2371 nvl(act.UNBILLED_RECEIVABLES, 0)
2372 end,
2373 acp.UNEARNED_REVENUE
2374 = case when acp.UNEARNED_REVENUE is null and
2375 act.UNEARNED_REVENUE is null
2376 then to_number(null)
2377 else nvl(acp.UNEARNED_REVENUE, 0) +
2378 nvl(act.UNEARNED_REVENUE, 0)
2379 end,
2380 acp.AR_UNAPPR_INVOICE_AMOUNT
2381 = case when acp.AR_UNAPPR_INVOICE_AMOUNT is null and
2382 act.AR_UNAPPR_INVOICE_AMOUNT is null
2383 then to_number(null)
2384 else nvl(acp.AR_UNAPPR_INVOICE_AMOUNT, 0) +
2385 nvl(act.AR_UNAPPR_INVOICE_AMOUNT, 0)
2386 end,
2387 acp.AR_UNAPPR_INVOICE_COUNT
2388 = case when acp.AR_UNAPPR_INVOICE_COUNT is null and
2389 act.AR_UNAPPR_INVOICE_COUNT is null
2390 then to_number(null)
2391 else nvl(acp.AR_UNAPPR_INVOICE_COUNT, 0) +
2392 nvl(act.AR_UNAPPR_INVOICE_COUNT, 0)
2393 end,
2394 acp.AR_APPR_INVOICE_AMOUNT
2395 = case when acp.AR_APPR_INVOICE_AMOUNT is null and
2396 act.AR_APPR_INVOICE_AMOUNT is null
2397 then to_number(null)
2398 else nvl(acp.AR_APPR_INVOICE_AMOUNT, 0) +
2399 nvl(act.AR_APPR_INVOICE_AMOUNT, 0)
2400 end,
2401 acp.AR_APPR_INVOICE_COUNT
2402 = case when acp.AR_APPR_INVOICE_COUNT is null and
2403 act.AR_APPR_INVOICE_COUNT is null
2404 then to_number(null)
2405 else nvl(acp.AR_APPR_INVOICE_COUNT, 0) +
2406 nvl(act.AR_APPR_INVOICE_COUNT, 0)
2407 end,
2408 acp.AR_AMOUNT_DUE
2409 = case when acp.AR_AMOUNT_DUE is null and
2410 act.AR_AMOUNT_DUE is null
2411 then to_number(null)
2412 else nvl(acp.AR_AMOUNT_DUE, 0) +
2413 nvl(act.AR_AMOUNT_DUE, 0)
2414 end,
2415 acp.AR_COUNT_DUE = case when acp.AR_COUNT_DUE is null and
2416 act.AR_COUNT_DUE is null
2417 then to_number(null)
2418 else nvl(acp.AR_COUNT_DUE, 0) +
2419 nvl(act.AR_COUNT_DUE, 0)
2420 end,
2421 acp.AR_AMOUNT_OVERDUE
2422 = case when acp.AR_AMOUNT_OVERDUE is null and
2423 act.AR_AMOUNT_OVERDUE is null
2424 then to_number(null)
2425 else nvl(acp.AR_AMOUNT_OVERDUE, 0) +
2426 nvl(act.AR_AMOUNT_OVERDUE, 0)
2427 end,
2428 acp.AR_COUNT_OVERDUE
2429 = case when acp.AR_COUNT_OVERDUE is null and
2430 act.AR_COUNT_OVERDUE is null
2431 then to_number(null)
2432 else nvl(acp.AR_COUNT_OVERDUE, 0) +
2433 nvl(act.AR_COUNT_OVERDUE, 0)
2434 end,
2435 acp.DORMANT_BACKLOG_INACTIV
2436 = case when acp.DORMANT_BACKLOG_INACTIV is null and
2437 act.DORMANT_BACKLOG_INACTIV is null
2438 then to_number(null)
2439 else nvl(acp.DORMANT_BACKLOG_INACTIV, 0) +
2440 nvl(act.DORMANT_BACKLOG_INACTIV, 0)
2441 end,
2442 acp.DORMANT_BACKLOG_START
2443 = case when acp.DORMANT_BACKLOG_START is null and
2444 act.DORMANT_BACKLOG_START is null
2445 then to_number(null)
2446 else nvl(acp.DORMANT_BACKLOG_START, 0) +
2447 nvl(act.DORMANT_BACKLOG_START, 0)
2448 end,
2449 acp.LOST_BACKLOG
2450 = case when acp.LOST_BACKLOG is null and
2451 act.LOST_BACKLOG is null
2452 then to_number(null)
2453 else nvl(acp.LOST_BACKLOG, 0) +
2454 nvl(act.LOST_BACKLOG, 0)
2455 end,
2456 acp.ACTIVE_BACKLOG
2457 = case when acp.ACTIVE_BACKLOG is null and
2458 act.ACTIVE_BACKLOG is null
2459 then to_number(null)
2460 else nvl(acp.ACTIVE_BACKLOG, 0) +
2461 nvl(act.ACTIVE_BACKLOG, 0)
2462 end,
2463 acp.REVENUE_AT_RISK
2464 = case when acp.REVENUE_AT_RISK is null and
2465 act.REVENUE_AT_RISK is null
2466 then to_number(null)
2467 else nvl(acp.REVENUE_AT_RISK, 0) +
2468 nvl(act.REVENUE_AT_RISK, 0)
2469 end,
2470 acp.LAST_UPDATE_DATE
2471 = act.LAST_UPDATE_DATE,
2472 acp.LAST_UPDATED_BY
2473 = act.LAST_UPDATED_BY,
2474 acp.LAST_UPDATE_LOGIN
2475 = act.LAST_UPDATE_LOGIN
2476 when not matched then insert
2477 (
2478 acp.PROJECT_ORG_ID,
2479 acp.PROJECT_ORGANIZATION_ID,
2480 acp.TIME_ID,
2481 acp.PROJECT_ID,
2482 acp.PERIOD_TYPE_ID,
2483 acp.CALENDAR_TYPE,
2484 acp.CURR_RECORD_TYPE_ID,
2485 acp.CURRENCY_CODE,
2486 acp.REVENUE,
2487 acp.INITIAL_FUNDING_AMOUNT,
2488 acp.INITIAL_FUNDING_COUNT,
2489 acp.ADDITIONAL_FUNDING_AMOUNT,
2490 acp.ADDITIONAL_FUNDING_COUNT,
2491 acp.CANCELLED_FUNDING_AMOUNT,
2492 acp.CANCELLED_FUNDING_COUNT,
2493 acp.FUNDING_ADJUSTMENT_AMOUNT,
2494 acp.FUNDING_ADJUSTMENT_COUNT,
2495 acp.REVENUE_WRITEOFF,
2496 acp.AR_INVOICE_AMOUNT,
2497 acp.AR_INVOICE_COUNT,
2498 acp.AR_CASH_APPLIED_AMOUNT,
2499 acp.AR_INVOICE_WRITEOFF_AMOUNT,
2500 acp.AR_INVOICE_WRITEOFF_COUNT,
2501 acp.AR_CREDIT_MEMO_AMOUNT,
2502 acp.AR_CREDIT_MEMO_COUNT,
2503 acp.UNBILLED_RECEIVABLES,
2504 acp.UNEARNED_REVENUE,
2505 acp.AR_UNAPPR_INVOICE_AMOUNT,
2506 acp.AR_UNAPPR_INVOICE_COUNT,
2507 acp.AR_APPR_INVOICE_AMOUNT,
2508 acp.AR_APPR_INVOICE_COUNT,
2509 acp.AR_AMOUNT_DUE,
2510 acp.AR_COUNT_DUE,
2511 acp.AR_AMOUNT_OVERDUE,
2512 acp.AR_COUNT_OVERDUE,
2513 acp.DORMANT_BACKLOG_INACTIV,
2514 acp.DORMANT_BACKLOG_START,
2515 acp.LOST_BACKLOG,
2516 acp.ACTIVE_BACKLOG,
2517 acp.REVENUE_AT_RISK,
2518 acp.LAST_UPDATE_DATE,
2519 acp.LAST_UPDATED_BY,
2520 acp.CREATION_DATE,
2521 acp.CREATED_BY,
2522 acp.LAST_UPDATE_LOGIN
2523 )
2524 values
2525 (
2526 act.PROJECT_ORG_ID,
2527 act.PROJECT_ORGANIZATION_ID,
2528 act.TIME_ID,
2529 act.PROJECT_ID,
2530 act.PERIOD_TYPE_ID,
2531 act.CALENDAR_TYPE,
2532 act.CURR_RECORD_TYPE_ID,
2533 act.CURRENCY_CODE,
2534 act.REVENUE,
2535 act.INITIAL_FUNDING_AMOUNT,
2536 act.INITIAL_FUNDING_COUNT,
2537 act.ADDITIONAL_FUNDING_AMOUNT,
2538 act.ADDITIONAL_FUNDING_COUNT,
2539 act.CANCELLED_FUNDING_AMOUNT,
2540 act.CANCELLED_FUNDING_COUNT,
2541 act.FUNDING_ADJUSTMENT_AMOUNT,
2542 act.FUNDING_ADJUSTMENT_COUNT,
2543 act.REVENUE_WRITEOFF,
2544 act.AR_INVOICE_AMOUNT,
2545 act.AR_INVOICE_COUNT,
2546 act.AR_CASH_APPLIED_AMOUNT,
2547 act.AR_INVOICE_WRITEOFF_AMOUNT,
2548 act.AR_INVOICE_WRITEOFF_COUNT,
2549 act.AR_CREDIT_MEMO_AMOUNT,
2550 act.AR_CREDIT_MEMO_COUNT,
2551 act.UNBILLED_RECEIVABLES,
2552 act.UNEARNED_REVENUE,
2553 act.AR_UNAPPR_INVOICE_AMOUNT,
2554 act.AR_UNAPPR_INVOICE_COUNT,
2555 act.AR_APPR_INVOICE_AMOUNT,
2556 act.AR_APPR_INVOICE_COUNT,
2557 act.AR_AMOUNT_DUE,
2558 act.AR_COUNT_DUE,
2559 act.AR_AMOUNT_OVERDUE,
2560 act.AR_COUNT_OVERDUE,
2561 act.DORMANT_BACKLOG_INACTIV,
2562 act.DORMANT_BACKLOG_START,
2563 act.LOST_BACKLOG,
2564 act.ACTIVE_BACKLOG,
2565 act.REVENUE_AT_RISK,
2566 act.LAST_UPDATE_DATE,
2567 act.LAST_UPDATED_BY,
2568 act.CREATION_DATE,
2569 act.CREATED_BY,
2570 act.LAST_UPDATE_LOGIN
2571 );
2572
2573 end if;
2574
2575 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
2576 (
2577 l_process,
2578 'PJI_FM_SUM_ROLLUP_ACT.MERGE_ACT_INTO_ACP(p_worker_id, ''' ||
2579 p_backlog_flag || ''');'
2580 );
2581
2582 if (p_backlog_flag = 'Y') then
2583
2584 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
2585
2586 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema ,
2587 'PJI_FM_AGGR_ACT3',
2588 'NORMAL',
2589 null);
2590
2591 end if;
2592
2593 commit;
2594
2595 end MERGE_ACT_INTO_ACP;
2596
2597
2598 -- -----------------------------------------------------
2599 -- procedure PROJECT_ORGANIZATION
2600 -- -----------------------------------------------------
2601 procedure PROJECT_ORGANIZATION (p_worker_id in number) is
2602
2603 l_process varchar2(30);
2604
2605 CURSOR update_scope(c_worker_id number)
2606 IS
2607 SELECT
2608 map.PROJECT_ID
2609 , map.NEW_PROJECT_ORGANIZATION_ID
2610 FROM PJI_PJI_PROJ_BATCH_MAP map
2611 WHERE 1=1
2612 AND map.WORKER_ID = c_worker_id
2613 AND map.PROJECT_ORGANIZATION_ID <> map.NEW_PROJECT_ORGANIZATION_ID
2614 ;
2615
2616 /*
2617 * Define PL/SQL Table for storing values.
2618 */
2619 L_NEW_ORGZ_TAB PA_PLSQL_DATATYPES.IdTabTyp;
2620 L_PROJECT_TAB PA_PLSQL_DATATYPES.IdTabTyp;
2621
2622 /*
2623 * Define other variable to be used in this procedure
2624 */
2625 l_this_fetch NUMBER:=0;
2626 l_totally_fetched NUMBER:=0;
2627 l_last_fetch VARCHAR2(1):='N';
2628 I PLS_INTEGER;
2629
2630 begin
2631
2632 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2633
2634 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
2635 (
2636 l_process,
2637 'PJI_FM_SUM_ROLLUP_ACT.PROJECT_ORGANIZATION(p_worker_id);'
2638 )) then
2639 return;
2640 end if;
2641
2642 IF update_scope%ISOPEN then
2643 CLOSE update_scope;
2644 END IF;
2645 OPEN update_scope(p_worker_id);
2646
2647 LOOP
2648 /*
2649 * Clear all PL/SQL table.
2650 */
2651 L_NEW_ORGZ_TAB.delete;
2652 L_PROJECT_TAB.delete;
2653
2654 /*
2655 * Fetch 1000 records at a time.
2656 */
2657 FETCH update_scope BULK COLLECT
2658 INTO
2659 L_PROJECT_TAB
2660 , L_NEW_ORGZ_TAB LIMIT 1000;
2661
2662 /*
2663 * To check the rows fetched in this fetch
2664 */
2665 l_this_fetch := update_scope%ROWCOUNT - l_totally_fetched;
2666 l_totally_fetched := update_scope%ROWCOUNT;
2667
2668 /*
2669 * Check if this fetch has 0 rows returned (ie last fetch was
2670 * even 1000)
2671 * This could happen in 2 cases
2672 * 1) this fetch is the very first fetch with 0 rows returned
2673 * OR 2) the last fetch returned an even 1000 rows
2674 * If either then EXIT without any processing
2675 */
2676 IF l_this_fetch = 0 then
2677 EXIT;
2678 END IF;
2679
2680 /*
2681 * Check if this fetch is the last fetch
2682 * If so then set the flag l_last_fetch so as to exit after
2683 * processing
2684 */
2685 IF l_this_fetch < 1000 then
2686 l_last_fetch := 'Y';
2687 ELSE
2688 l_last_fetch := 'N';
2689 END IF;
2690
2691 FORALL I in L_PROJECT_TAB.FIRST..L_PROJECT_TAB.LAST
2692 Update PJI_AC_PROJ_F
2693 Set PROJECT_ORGANIZATION_ID = L_NEW_ORGZ_TAB(I)
2694 Where PROJECT_ID = L_PROJECT_TAB(I)
2695 ;
2696
2697 /*
2698 * Check if this loop is the last set of 100
2699 * If so then EXIT;
2700 */
2701 IF l_last_fetch='Y' THEN
2702 EXIT;
2703 END IF;
2704
2705 END LOOP;
2706
2707 CLOSE update_scope;
2708
2709
2710 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
2711 (
2712 l_process,
2713 'PJI_FM_SUM_ROLLUP_ACT.PROJECT_ORGANIZATION(p_worker_id);'
2714 );
2715
2716 commit;
2717
2718 end PROJECT_ORGANIZATION;
2719
2720
2721 -- -----------------------------------------------------
2722 -- procedure REFRESH_MVIEW_ACO
2723 -- -----------------------------------------------------
2724 procedure REFRESH_MVIEW_ACO (p_worker_id in number) is
2725
2726 l_process varchar2(30);
2727 l_extraction_type varchar2(30);
2728 l_pji_schema varchar2(30);
2729 l_apps_schema varchar2(30);
2730 l_p_degree number := 0;
2731
2732 l_errbuf varchar2(255);
2733 l_retcode varchar2(255);
2734
2735 begin
2736
2737 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2738
2739 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
2740 (
2741 l_process,
2742 'PJI_FM_SUM_ROLLUP_ACT.REFRESH_MVIEW_ACO(p_worker_id);'
2743 )) then
2744 return;
2745 end if;
2746
2747 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2748 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
2749
2750 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
2751 l_extraction_type <> 'PARTIAL') then
2752 return;
2753 end if;
2754
2755 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
2756 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
2757 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
2758 if (l_p_degree = 1) then
2759 l_p_degree := 0;
2760 end if;
2761
2762 /* Stats gathered for this table in costing mviews refresh.
2763 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
2764 TABNAME => 'PJI_ORG_DENORM',
2765 PERCENT => 10,
2766 DEGREE => l_p_degree);
2767 */
2768
2769 if (l_extraction_type = 'FULL') then
2770 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
2771 l_retcode,
2772 'PJI_AC_ORG_F_MV',
2773 'C',
2774 'N');
2775 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
2776 l_retcode,
2777 'PJI_AC_ORGO_F_MV',
2778 'C',
2779 'N');
2780 else
2781 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
2782 TABNAME => 'MLOG$_PJI_AC_PROJ_F',
2783 PERCENT => 10,
2784 DEGREE => l_p_degree);
2785 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
2786 l_retcode,
2787 'PJI_AC_ORG_F_MV',
2788 'F',
2789 'N');
2790 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_apps_schema,
2791 TABNAME => 'MLOG$_PJI_AC_ORG_F_MV',
2792 PERCENT => 10,
2793 DEGREE => l_p_degree);
2794 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
2795 l_retcode,
2796 'PJI_AC_ORGO_F_MV',
2797 'F',
2798 'N');
2799 end if;
2800
2801 if (l_extraction_type <> 'INCREMENTAL') then
2802 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
2803 tabname => 'PJI_AC_ORG_F_MV',
2804 percent => 10,
2805 degree => l_p_degree);
2806 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
2807 tabname => 'PJI_AC_ORGO_F_MV',
2808 percent => 10,
2809 degree => l_p_degree);
2810 end if;
2811
2812 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
2813 (
2814 l_process,
2815 'PJI_FM_SUM_ROLLUP_ACT.REFRESH_MVIEW_ACO(p_worker_id);'
2816 );
2817
2818 commit;
2819
2820 end REFRESH_MVIEW_ACO;
2821
2822
2823 -- -----------------------------------------------------
2824 -- procedure REFRESH_MVIEW_ACC
2825 -- -----------------------------------------------------
2826 procedure REFRESH_MVIEW_ACC (p_worker_id in number) is
2827
2828 l_process varchar2(30);
2829 l_extraction_type varchar2(30);
2830 l_pji_schema varchar2(30);
2831 l_apps_schema varchar2(30);
2832 l_p_degree number;
2833
2834 l_errbuf varchar2(255);
2835 l_retcode varchar2(255);
2836
2837 begin
2838
2839 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2840
2841 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
2842 (
2843 l_process,
2844 'PJI_FM_SUM_ROLLUP_ACT.REFRESH_MVIEW_ACC(p_worker_id);'
2845 )) then
2846 return;
2847 end if;
2848
2849 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2850 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
2851
2852 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
2853 l_extraction_type <> 'PARTIAL') then
2854 return;
2855 end if;
2856
2857 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
2858 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
2859 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
2860 if (l_p_degree = 1) then
2861 l_p_degree := 0;
2862 end if;
2863
2864 /* Stats gathered for this table in costing mviews refresh.
2865 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
2866 TABNAME => 'PJI_PROJECT_CLASSES',
2867 PERCENT => 10,
2868 DEGREE => l_p_degree);
2869 */
2870
2871 if (l_extraction_type = 'FULL') then
2872 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
2873 l_retcode,
2874 'PJI_AC_CLS_F_MV',
2875 'C',
2876 'N');
2877 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
2878 l_retcode,
2879 'PJI_AC_CLSO_F_MV',
2880 'C',
2881 'N');
2882 else
2883 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
2884 l_retcode,
2885 'PJI_AC_CLS_F_MV',
2886 'F',
2887 'N');
2888 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_apps_schema,
2889 TABNAME => 'MLOG$_PJI_AC_CLS_F_MV',
2890 PERCENT => 10,
2891 DEGREE => l_p_degree);
2892 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
2893 l_retcode,
2894 'PJI_AC_CLSO_F_MV',
2895 'F',
2896 'N');
2897 end if;
2898
2899 if (l_extraction_type <> 'INCREMENTAL') then
2900 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
2901 tabname => 'PJI_AC_CLS_F_MV',
2902 percent => 10,
2903 degree => l_p_degree);
2904 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
2905 tabname => 'PJI_AC_CLSO_F_MV',
2906 percent => 10,
2907 degree => l_p_degree);
2908 end if;
2909
2910 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
2911 (
2912 l_process,
2913 'PJI_FM_SUM_ROLLUP_ACT.REFRESH_MVIEW_ACC(p_worker_id);'
2914 );
2915
2916 commit;
2917
2918 end REFRESH_MVIEW_ACC;
2919
2920 end PJI_FM_SUM_ROLLUP_ACT;