[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_SUM_PSI
Source
1 package body PJI_FM_SUM_PSI as
2 /* $Header: PJISF09B.pls 120.24.12020000.7 2013/04/23 22:41:44 sachandr ship $ */
3
4 -- -----------------------------------------------------
5 -- procedure RESOURCE_LOOKUP_TABLE
6 --
7 -- History
8 -- 19-MAR-2004 SVERMETT Created
9 --
10 -- Internal PJP Summarization API.
11 --
12 -- -----------------------------------------------------
13 procedure RESOURCE_LOOKUP_TABLE (p_worker_id in number) is
14
15 l_process varchar2(30);
16 l_extraction_type varchar2(15);
17
18 begin
19
20 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
21
22 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
23
24 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.RESOURCE_LOOKUP_TABLE(p_worker_id);')) then
25 return;
26 end if;
27
28 insert into PJI_FM_AGGR_RES_TYPES
29 (
30 EXP_TYPE_CLASS,
31 RESOURCE_CLASS_ID
32 )
33 select 'OT' EXP_TYPE_CLASS, -- actuals lookups
34 cls.RESOURCE_CLASS_ID
35 from PA_RESOURCE_CLASSES_B cls
36 where cls.RESOURCE_CLASS_CODE = 'PEOPLE'
37 union all
38 select 'ER' EXP_TYPE_CLASS,
39 cls.RESOURCE_CLASS_ID
40 from PA_RESOURCE_CLASSES_B cls
41 where cls.RESOURCE_CLASS_CODE = 'FINANCIAL_ELEMENTS'
42 union all
43 select 'ST' EXP_TYPE_CLASS,
44 cls.RESOURCE_CLASS_ID
45 from PA_RESOURCE_CLASSES_B cls
46 where cls.RESOURCE_CLASS_CODE = 'PEOPLE'
47 union all
48 select 'INV' EXP_TYPE_CLASS,
49 cls.RESOURCE_CLASS_ID
50 from PA_RESOURCE_CLASSES_B cls
51 where cls.RESOURCE_CLASS_CODE = 'MATERIAL_ITEMS'
52 union all
53 select 'VI$FINANCIAL' EXP_TYPE_CLASS,
54 cls.RESOURCE_CLASS_ID
55 from PA_RESOURCE_CLASSES_B cls
56 where cls.RESOURCE_CLASS_CODE = 'FINANCIAL_ELEMENTS'
57 union all
58 select 'VI$MATERIAL' EXP_TYPE_CLASS,
59 cls.RESOURCE_CLASS_ID
60 from PA_RESOURCE_CLASSES_B cls
61 where cls.RESOURCE_CLASS_CODE = 'MATERIAL_ITEMS'
62 union all
63 select 'VI$PEOPLE' EXP_TYPE_CLASS,
64 cls.RESOURCE_CLASS_ID
65 from PA_RESOURCE_CLASSES_B cls
66 where cls.RESOURCE_CLASS_CODE = 'PEOPLE'
67 union all
68 select 'PJ' EXP_TYPE_CLASS,
69 cls.RESOURCE_CLASS_ID
70 from PA_RESOURCE_CLASSES_B cls
71 where cls.RESOURCE_CLASS_CODE = 'FINANCIAL_ELEMENTS'
72 union all
73 select 'BTC' EXP_TYPE_CLASS,
74 cls.RESOURCE_CLASS_ID
75 from PA_RESOURCE_CLASSES_B cls
76 where cls.RESOURCE_CLASS_CODE = 'FINANCIAL_ELEMENTS'
77 union all
78 select 'WIP$EQUIPMENT' EXP_TYPE_CLASS,
79 cls.RESOURCE_CLASS_ID
80 from PA_RESOURCE_CLASSES_B cls
81 where cls.RESOURCE_CLASS_CODE = 'EQUIPMENT'
82 union all
83 select 'WIP$PEOPLE' EXP_TYPE_CLASS,
84 cls.RESOURCE_CLASS_ID
85 from PA_RESOURCE_CLASSES_B cls
86 where cls.RESOURCE_CLASS_CODE = 'PEOPLE'
87 union all
88 select 'WIP$OTHER' EXP_TYPE_CLASS,
89 cls.RESOURCE_CLASS_ID
90 from PA_RESOURCE_CLASSES_B cls
91 where cls.RESOURCE_CLASS_CODE = 'FINANCIAL_ELEMENTS'
92 union all
93 select 'USG$Y' EXP_TYPE_CLASS,
94 cls.RESOURCE_CLASS_ID
95 from PA_RESOURCE_CLASSES_B cls
96 where cls.RESOURCE_CLASS_CODE = 'EQUIPMENT'
97 union all
98 select 'USG$N' EXP_TYPE_CLASS,
99 cls.RESOURCE_CLASS_ID
100 from PA_RESOURCE_CLASSES_B cls
101 where cls.RESOURCE_CLASS_CODE = 'FINANCIAL_ELEMENTS'
102 union all
103 select 'PJI$NULL' EXP_TYPE_CLASS,
104 cls.RESOURCE_CLASS_ID
105 from PA_RESOURCE_CLASSES_B cls
106 where cls.RESOURCE_CLASS_CODE = 'FINANCIAL_ELEMENTS'
107 union all
108 select cls.RESOURCE_CLASS_CODE EXP_TYPE_CLASS, -- commitments lookups
109 cls.RESOURCE_CLASS_ID
110 from PA_RESOURCE_CLASSES_B cls
111 where cls.RESOURCE_CLASS_CODE in ('FINANCIAL_ELEMENTS',
112 'MATERIAL_ITEMS',
113 'EQUIPMENT',
114 'PEOPLE');
115
116 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.RESOURCE_LOOKUP_TABLE(p_worker_id);');
117
118 commit;
119
120 end RESOURCE_LOOKUP_TABLE;
121
122
123 -- -----------------------------------------------------
124 -- procedure PURGE_FP_BALANCES
125 --
126 -- History
127 -- 19-MAR-2004 SVERMETT Created
128 --
129 -- Internal PJP Summarization API.
130 --
131 -- -----------------------------------------------------
132 procedure PURGE_FP_BALANCES (p_worker_id in number) is
133
134 l_process varchar2(30);
135 l_extraction_type varchar2(15);
136
137 begin
138
139 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
140
141 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.PURGE_FP_BALANCES(p_worker_id);')) then
142 return;
143 end if;
144
145 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
146
147 if (l_extraction_type = 'PARTIAL') then
148
149 delete
150 from PJI_FM_AGGR_FIN7 fin7
151 where fin7.RECORD_TYPE = 'A' and
152 fin7.PROJECT_ID in (select map.PROJECT_ID
153 from PJI_FM_PROJ_BATCH_MAP map
154 where map.WORKER_ID = p_worker_id);
155
156 delete
157 from PJI_FP_TXN_ACCUM bal
158 where bal.PROJECT_ID in (select map.PROJECT_ID
159 from PJI_FM_PROJ_BATCH_MAP map
160 where map.WORKER_ID = p_worker_id);
161 /*
162
163 delete
164 from PA_FIN7_OLAP fin7
165 where fin7.RECORD_TYPE = 'A' and
166 fin7.PROJECT_ID in (select map.PROJECT_ID
167 from PJI_FM_PROJ_BATCH_MAP map
168 where map.WORKER_ID = p_worker_id);
169
170 delete
171 from PA_STAGE1_BATCH_OLAP bal
172 where bal.PROJECT_ID in (select map.PROJECT_ID
173 from PJI_FM_PROJ_BATCH_MAP map
174 where map.WORKER_ID = p_worker_id);
175 */ -- COMMENTED TO REVERT OLAP CHANGES FOR 15900841
176
177 end if;
178
179 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.PURGE_FP_BALANCES(p_worker_id);');
180
181 commit;
182
183 end PURGE_FP_BALANCES;
184
185
186 -- -----------------------------------------------------
187 -- procedure PURGE_CMT_BALANCES
188 --
189 -- History
190 -- 19-MAR-2004 SVERMETT Created
191 --
192 -- Internal PJP Summarization API.
193 --
194 -- -----------------------------------------------------
195 procedure PURGE_CMT_BALANCES (p_worker_id in number) is
196
197 l_process varchar2(30);
198 l_extraction_type varchar2(15);
199 l_extract_commitments varchar2(30);
200
201 begin
202
203 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
204
205 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.PURGE_CMT_BALANCES(p_worker_id);')) then
206 return;
207 end if;
208
209 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
210
211 l_extract_commitments := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
212 (PJI_FM_SUM_MAIN.g_process,
213 'EXTRACT_COMMITMENTS');
214
215 if (l_extraction_type <> 'FULL' and l_extract_commitments = 'Y') then
216
217 delete
218 from PJI_FM_AGGR_FIN7 fin7
219 where fin7.RECORD_TYPE = 'M' and
220 fin7.PROJECT_ID in (select map.PROJECT_ID
221 from PJI_FM_PROJ_BATCH_MAP map
222 where map.WORKER_ID = p_worker_id);
223
224 delete
225 from PJI_FP_TXN_ACCUM1 bal
226 where bal.PROJECT_ID in (select map.PROJECT_ID
227 from PJI_FM_PROJ_BATCH_MAP map
228 where map.WORKER_ID = p_worker_id);
229
230 end if;
231
232 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.PURGE_CMT_BALANCES(p_worker_id);');
233
234 commit;
235
236 end PURGE_CMT_BALANCES;
237
238
239 -- -----------------------------------------------------
240 -- procedure PURGE_AC_BALANCES
241 --
242 -- History
243 -- 19-MAR-2004 SVERMETT Created
244 --
245 -- Internal PJP Summarization API.
246 --
247 -- -----------------------------------------------------
248 procedure PURGE_AC_BALANCES (p_worker_id in number) is
249
250 l_process varchar2(30);
251 l_extraction_type varchar2(15);
252
253 begin
254
255 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
256
257 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.PURGE_AC_BALANCES(p_worker_id);')) then
258 return;
259 end if;
260
261 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
262
263 if (l_extraction_type = 'PARTIAL') then
264
265 delete
266 from PJI_FM_AGGR_ACT4 act4
267 where act4.PROJECT_ID in (select map.PROJECT_ID
268 from PJI_FM_PROJ_BATCH_MAP map
269 where map.WORKER_ID = p_worker_id);
270
271 elsif (l_extraction_type = 'INCREMENTAL') then
272
273 -- clean up snapshots and activities
274
275 update PJI_FM_AGGR_ACT4 act4
276 set act4.TXN_AR_INVOICE_AMOUNT = to_number(null),
277 act4.TXN_AR_CASH_APPLIED_AMOUNT = to_number(null),
278 act4.TXN_AR_INVOICE_WRITEOFF_AMOUNT = to_number(null),
279 act4.TXN_AR_CREDIT_MEMO_AMOUNT = to_number(null),
280 act4.TXN_AR_UNAPPR_INVOICE_AMOUNT = to_number(null),
281 act4.TXN_AR_APPR_INVOICE_AMOUNT = to_number(null),
282 act4.TXN_AR_AMOUNT_DUE = to_number(null),
283 act4.TXN_AR_AMOUNT_OVERDUE = to_number(null),
284 act4.PRJ_AR_INVOICE_AMOUNT = to_number(null),
285 act4.PRJ_AR_CASH_APPLIED_AMOUNT = to_number(null),
286 act4.PRJ_AR_INVOICE_WRITEOFF_AMOUNT = to_number(null),
287 act4.PRJ_AR_CREDIT_MEMO_AMOUNT = to_number(null),
288 act4.PRJ_AR_UNAPPR_INVOICE_AMOUNT = to_number(null),
289 act4.PRJ_AR_APPR_INVOICE_AMOUNT = to_number(null),
290 act4.PRJ_AR_AMOUNT_DUE = to_number(null),
291 act4.PRJ_AR_AMOUNT_OVERDUE = to_number(null),
292 act4.POU_AR_INVOICE_AMOUNT = to_number(null),
293 act4.POU_AR_CASH_APPLIED_AMOUNT = to_number(null),
294 act4.POU_AR_INVOICE_WRITEOFF_AMOUNT = to_number(null),
295 act4.POU_AR_CREDIT_MEMO_AMOUNT = to_number(null),
296 act4.POU_AR_UNAPPR_INVOICE_AMOUNT = to_number(null),
297 act4.POU_AR_APPR_INVOICE_AMOUNT = to_number(null),
298 act4.POU_AR_AMOUNT_DUE = to_number(null),
299 act4.POU_AR_AMOUNT_OVERDUE = to_number(null),
300 act4.AR_INVOICE_COUNT = to_number(null),
301 act4.AR_INVOICE_WRITEOFF_COUNT = to_number(null),
302 act4.AR_CREDIT_MEMO_COUNT = to_number(null),
303 act4.AR_UNAPPR_INVOICE_COUNT = to_number(null),
304 act4.AR_APPR_INVOICE_COUNT = to_number(null),
305 act4.AR_COUNT_DUE = to_number(null),
306 act4.AR_COUNT_OVERDUE = to_number(null),
307 act4.G1_AR_INVOICE_AMOUNT = to_number(null),
308 act4.G1_AR_CASH_APPLIED_AMOUNT = to_number(null),
309 act4.G1_AR_INVOICE_WRITEOFF_AMOUNT = to_number(null),
310 act4.G1_AR_CREDIT_MEMO_AMOUNT = to_number(null),
311 act4.G1_AR_UNAPPR_INVOICE_AMOUNT = to_number(null),
312 act4.G1_AR_APPR_INVOICE_AMOUNT = to_number(null),
313 act4.G1_AR_AMOUNT_DUE = to_number(null),
314 act4.G1_AR_AMOUNT_OVERDUE = to_number(null),
315 act4.G2_AR_INVOICE_AMOUNT = to_number(null),
316 act4.G2_AR_CASH_APPLIED_AMOUNT = to_number(null),
317 act4.G2_AR_INVOICE_WRITEOFF_AMOUNT = to_number(null),
318 act4.G2_AR_CREDIT_MEMO_AMOUNT = to_number(null),
319 act4.G2_AR_UNAPPR_INVOICE_AMOUNT = to_number(null),
320 act4.G2_AR_APPR_INVOICE_AMOUNT = to_number(null),
321 act4.G2_AR_AMOUNT_DUE = to_number(null),
322 act4.G2_AR_AMOUNT_OVERDUE = to_number(null)
323 where act4.PROJECT_ID in (select map.PROJECT_ID
324 from PJI_FM_PROJ_BATCH_MAP map
325 where map.WORKER_ID = p_worker_id) and
326 not (nvl(act4.TXN_AR_INVOICE_AMOUNT , 0) = 0 and
327 nvl(act4.TXN_AR_CASH_APPLIED_AMOUNT , 0) = 0 and
328 nvl(act4.TXN_AR_INVOICE_WRITEOFF_AMOUNT , 0) = 0 and
329 nvl(act4.TXN_AR_CREDIT_MEMO_AMOUNT , 0) = 0 and
330 nvl(act4.TXN_AR_UNAPPR_INVOICE_AMOUNT , 0) = 0 and
331 nvl(act4.TXN_AR_APPR_INVOICE_AMOUNT , 0) = 0 and
332 nvl(act4.TXN_AR_AMOUNT_DUE , 0) = 0 and
333 nvl(act4.TXN_AR_AMOUNT_OVERDUE , 0) = 0 and
334 nvl(act4.PRJ_AR_INVOICE_AMOUNT , 0) = 0 and
335 nvl(act4.PRJ_AR_CASH_APPLIED_AMOUNT , 0) = 0 and
336 nvl(act4.PRJ_AR_INVOICE_WRITEOFF_AMOUNT , 0) = 0 and
337 nvl(act4.PRJ_AR_CREDIT_MEMO_AMOUNT , 0) = 0 and
338 nvl(act4.PRJ_AR_UNAPPR_INVOICE_AMOUNT , 0) = 0 and
339 nvl(act4.PRJ_AR_APPR_INVOICE_AMOUNT , 0) = 0 and
340 nvl(act4.PRJ_AR_AMOUNT_DUE , 0) = 0 and
341 nvl(act4.PRJ_AR_AMOUNT_OVERDUE , 0) = 0 and
342 nvl(act4.POU_AR_INVOICE_AMOUNT , 0) = 0 and
343 nvl(act4.POU_AR_CASH_APPLIED_AMOUNT , 0) = 0 and
344 nvl(act4.POU_AR_INVOICE_WRITEOFF_AMOUNT , 0) = 0 and
345 nvl(act4.POU_AR_CREDIT_MEMO_AMOUNT , 0) = 0 and
346 nvl(act4.POU_AR_UNAPPR_INVOICE_AMOUNT , 0) = 0 and
347 nvl(act4.POU_AR_APPR_INVOICE_AMOUNT , 0) = 0 and
348 nvl(act4.POU_AR_AMOUNT_DUE , 0) = 0 and
349 nvl(act4.POU_AR_AMOUNT_OVERDUE , 0) = 0 and
350 nvl(act4.AR_INVOICE_COUNT , 0) = 0 and
351 nvl(act4.AR_INVOICE_WRITEOFF_COUNT , 0) = 0 and
352 nvl(act4.AR_CREDIT_MEMO_COUNT , 0) = 0 and
353 nvl(act4.AR_UNAPPR_INVOICE_COUNT , 0) = 0 and
354 nvl(act4.AR_APPR_INVOICE_COUNT , 0) = 0 and
355 nvl(act4.AR_COUNT_DUE , 0) = 0 and
356 nvl(act4.AR_COUNT_OVERDUE , 0) = 0 and
357 nvl(act4.G1_AR_INVOICE_AMOUNT , 0) = 0 and
358 nvl(act4.G1_AR_CASH_APPLIED_AMOUNT , 0) = 0 and
359 nvl(act4.G1_AR_INVOICE_WRITEOFF_AMOUNT , 0) = 0 and
360 nvl(act4.G1_AR_CREDIT_MEMO_AMOUNT , 0) = 0 and
361 nvl(act4.G1_AR_UNAPPR_INVOICE_AMOUNT , 0) = 0 and
362 nvl(act4.G1_AR_APPR_INVOICE_AMOUNT , 0) = 0 and
363 nvl(act4.G1_AR_AMOUNT_DUE , 0) = 0 and
364 nvl(act4.G1_AR_AMOUNT_OVERDUE , 0) = 0 and
365 nvl(act4.G2_AR_INVOICE_AMOUNT , 0) = 0 and
366 nvl(act4.G2_AR_CASH_APPLIED_AMOUNT , 0) = 0 and
367 nvl(act4.G2_AR_INVOICE_WRITEOFF_AMOUNT , 0) = 0 and
368 nvl(act4.G2_AR_CREDIT_MEMO_AMOUNT , 0) = 0 and
369 nvl(act4.G2_AR_UNAPPR_INVOICE_AMOUNT , 0) = 0 and
370 nvl(act4.G2_AR_APPR_INVOICE_AMOUNT , 0) = 0 and
371 nvl(act4.G2_AR_AMOUNT_DUE , 0) = 0 and
372 nvl(act4.G2_AR_AMOUNT_OVERDUE , 0) = 0);
373
374 delete
375 from PJI_FM_AGGR_ACT4 act4
376 where act4.PROJECT_ID in (select map.PROJECT_ID
377 from PJI_FM_PROJ_BATCH_MAP map
378 where map.WORKER_ID = p_worker_id) and
379 nvl(act4.TXN_REVENUE , 0) = 0 and
380 nvl(act4.TXN_FUNDING , 0) = 0 and
381 nvl(act4.TXN_INITIAL_FUNDING_AMOUNT , 0) = 0 and
382 nvl(act4.TXN_ADDITIONAL_FUNDING_AMOUNT , 0) = 0 and
383 nvl(act4.TXN_CANCELLED_FUNDING_AMOUNT , 0) = 0 and
384 nvl(act4.TXN_FUNDING_ADJUSTMENT_AMOUNT , 0) = 0 and
385 nvl(act4.TXN_REVENUE_WRITEOFF , 0) = 0 and
386 nvl(act4.TXN_AR_INVOICE_AMOUNT , 0) = 0 and
387 nvl(act4.TXN_AR_CASH_APPLIED_AMOUNT , 0) = 0 and
388 nvl(act4.TXN_AR_INVOICE_WRITEOFF_AMOUNT , 0) = 0 and
389 nvl(act4.TXN_AR_CREDIT_MEMO_AMOUNT , 0) = 0 and
390 nvl(act4.TXN_UNBILLED_RECEIVABLES , 0) = 0 and
391 nvl(act4.TXN_UNEARNED_REVENUE , 0) = 0 and
392 nvl(act4.TXN_AR_UNAPPR_INVOICE_AMOUNT , 0) = 0 and
393 nvl(act4.TXN_AR_APPR_INVOICE_AMOUNT , 0) = 0 and
394 nvl(act4.TXN_AR_AMOUNT_DUE , 0) = 0 and
395 nvl(act4.TXN_AR_AMOUNT_OVERDUE , 0) = 0 and
396 nvl(act4.PRJ_REVENUE , 0) = 0 and
397 nvl(act4.PRJ_FUNDING , 0) = 0 and
398 nvl(act4.PRJ_INITIAL_FUNDING_AMOUNT , 0) = 0 and
399 nvl(act4.PRJ_ADDITIONAL_FUNDING_AMOUNT , 0) = 0 and
400 nvl(act4.PRJ_CANCELLED_FUNDING_AMOUNT , 0) = 0 and
401 nvl(act4.PRJ_FUNDING_ADJUSTMENT_AMOUNT , 0) = 0 and
402 nvl(act4.PRJ_REVENUE_WRITEOFF , 0) = 0 and
403 nvl(act4.PRJ_AR_INVOICE_AMOUNT , 0) = 0 and
404 nvl(act4.PRJ_AR_CASH_APPLIED_AMOUNT , 0) = 0 and
405 nvl(act4.PRJ_AR_INVOICE_WRITEOFF_AMOUNT , 0) = 0 and
406 nvl(act4.PRJ_AR_CREDIT_MEMO_AMOUNT , 0) = 0 and
407 nvl(act4.PRJ_UNBILLED_RECEIVABLES , 0) = 0 and
408 nvl(act4.PRJ_UNEARNED_REVENUE , 0) = 0 and
409 nvl(act4.PRJ_AR_UNAPPR_INVOICE_AMOUNT , 0) = 0 and
410 nvl(act4.PRJ_AR_APPR_INVOICE_AMOUNT , 0) = 0 and
411 nvl(act4.PRJ_AR_AMOUNT_DUE , 0) = 0 and
412 nvl(act4.PRJ_AR_AMOUNT_OVERDUE , 0) = 0 and
413 nvl(act4.POU_REVENUE , 0) = 0 and
414 nvl(act4.POU_FUNDING , 0) = 0 and
415 nvl(act4.POU_INITIAL_FUNDING_AMOUNT , 0) = 0 and
416 nvl(act4.POU_ADDITIONAL_FUNDING_AMOUNT , 0) = 0 and
417 nvl(act4.POU_CANCELLED_FUNDING_AMOUNT , 0) = 0 and
418 nvl(act4.POU_FUNDING_ADJUSTMENT_AMOUNT , 0) = 0 and
419 nvl(act4.POU_REVENUE_WRITEOFF , 0) = 0 and
420 nvl(act4.POU_AR_INVOICE_AMOUNT , 0) = 0 and
421 nvl(act4.POU_AR_CASH_APPLIED_AMOUNT , 0) = 0 and
422 nvl(act4.POU_AR_INVOICE_WRITEOFF_AMOUNT , 0) = 0 and
423 nvl(act4.POU_AR_CREDIT_MEMO_AMOUNT , 0) = 0 and
424 nvl(act4.POU_UNBILLED_RECEIVABLES , 0) = 0 and
425 nvl(act4.POU_UNEARNED_REVENUE , 0) = 0 and
426 nvl(act4.POU_AR_UNAPPR_INVOICE_AMOUNT , 0) = 0 and
427 nvl(act4.POU_AR_APPR_INVOICE_AMOUNT , 0) = 0 and
428 nvl(act4.POU_AR_AMOUNT_DUE , 0) = 0 and
429 nvl(act4.POU_AR_AMOUNT_OVERDUE , 0) = 0 and
430 nvl(act4.INITIAL_FUNDING_COUNT , 0) = 0 and
431 nvl(act4.ADDITIONAL_FUNDING_COUNT , 0) = 0 and
432 nvl(act4.CANCELLED_FUNDING_COUNT , 0) = 0 and
433 nvl(act4.FUNDING_ADJUSTMENT_COUNT , 0) = 0 and
434 nvl(act4.AR_INVOICE_COUNT , 0) = 0 and
435 nvl(act4.AR_CASH_APPLIED_COUNT , 0) = 0 and
436 nvl(act4.AR_INVOICE_WRITEOFF_COUNT , 0) = 0 and
437 nvl(act4.AR_CREDIT_MEMO_COUNT , 0) = 0 and
438 nvl(act4.AR_UNAPPR_INVOICE_COUNT , 0) = 0 and
439 nvl(act4.AR_APPR_INVOICE_COUNT , 0) = 0 and
440 nvl(act4.AR_COUNT_DUE , 0) = 0 and
441 nvl(act4.AR_COUNT_OVERDUE , 0) = 0 and
442 nvl(act4.G1_REVENUE , 0) = 0 and
443 nvl(act4.G1_FUNDING , 0) = 0 and
444 nvl(act4.G1_INITIAL_FUNDING_AMOUNT , 0) = 0 and
445 nvl(act4.G1_ADDITIONAL_FUNDING_AMOUNT , 0) = 0 and
446 nvl(act4.G1_CANCELLED_FUNDING_AMOUNT , 0) = 0 and
447 nvl(act4.G1_FUNDING_ADJUSTMENT_AMOUNT , 0) = 0 and
448 nvl(act4.G1_REVENUE_WRITEOFF , 0) = 0 and
449 nvl(act4.G1_AR_INVOICE_AMOUNT , 0) = 0 and
450 nvl(act4.G1_AR_CASH_APPLIED_AMOUNT , 0) = 0 and
451 nvl(act4.G1_AR_INVOICE_WRITEOFF_AMOUNT , 0) = 0 and
452 nvl(act4.G1_AR_CREDIT_MEMO_AMOUNT , 0) = 0 and
453 nvl(act4.G1_UNBILLED_RECEIVABLES , 0) = 0 and
454 nvl(act4.G1_UNEARNED_REVENUE , 0) = 0 and
455 nvl(act4.G1_AR_UNAPPR_INVOICE_AMOUNT , 0) = 0 and
456 nvl(act4.G1_AR_APPR_INVOICE_AMOUNT , 0) = 0 and
457 nvl(act4.G1_AR_AMOUNT_DUE , 0) = 0 and
458 nvl(act4.G1_AR_AMOUNT_OVERDUE , 0) = 0 and
459 nvl(act4.G2_REVENUE , 0) = 0 and
460 nvl(act4.G2_FUNDING , 0) = 0 and
461 nvl(act4.G2_INITIAL_FUNDING_AMOUNT , 0) = 0 and
462 nvl(act4.G2_ADDITIONAL_FUNDING_AMOUNT , 0) = 0 and
463 nvl(act4.G2_CANCELLED_FUNDING_AMOUNT , 0) = 0 and
464 nvl(act4.G2_FUNDING_ADJUSTMENT_AMOUNT , 0) = 0 and
465 nvl(act4.G2_REVENUE_WRITEOFF , 0) = 0 and
466 nvl(act4.G2_AR_INVOICE_AMOUNT , 0) = 0 and
467 nvl(act4.G2_AR_CASH_APPLIED_AMOUNT , 0) = 0 and
468 nvl(act4.G2_AR_INVOICE_WRITEOFF_AMOUNT , 0) = 0 and
469 nvl(act4.G2_AR_CREDIT_MEMO_AMOUNT , 0) = 0 and
470 nvl(act4.G2_UNBILLED_RECEIVABLES , 0) = 0 and
471 nvl(act4.G2_UNEARNED_REVENUE , 0) = 0 and
472 nvl(act4.G2_AR_UNAPPR_INVOICE_AMOUNT , 0) = 0 and
473 nvl(act4.G2_AR_APPR_INVOICE_AMOUNT , 0) = 0 and
474 nvl(act4.G2_AR_AMOUNT_DUE , 0) = 0 and
475 nvl(act4.G2_AR_AMOUNT_OVERDUE , 0) = 0;
476
477 end if;
478
479 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.PURGE_AC_BALANCES(p_worker_id);');
480
481 commit;
482
483 end PURGE_AC_BALANCES;
484
485
486 -- -----------------------------------------------------
487 -- procedure AGGREGATE_FPR_PERIODS
488 --
489 -- History
490 -- 19-MAR-2004 SVERMETT Created
491 --
492 -- Internal PJP Summarization API.
493 --
494 -- -----------------------------------------------------
495 procedure AGGREGATE_FPR_PERIODS (p_worker_id in number) is
496
497 l_process varchar2(30);
498
499 begin
500
501 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
502
503 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.AGGREGATE_FPR_PERIODS(p_worker_id);')) then
504 return;
505 end if;
506
507 insert /*+ append parallel(tmp6_i) */ into PJI_FM_AGGR_FIN6 tmp6_i
508 (
509 WORKER_ID,
510 RECORD_TYPE,
511 PERSON_ID,
512 EXPENDITURE_ORG_ID,
513 EXPENDITURE_ORGANIZATION_ID,
514 RESOURCE_CLASS_ID,
515 JOB_ID,
516 VENDOR_ID,
517 WORK_TYPE_ID,
518 EXPENDITURE_CATEGORY_ID,
519 EXPENDITURE_TYPE_ID,
520 EVENT_TYPE_ID,
521 EXP_EVT_TYPE_ID,
522 EXPENDITURE_TYPE,
523 EVENT_TYPE,
524 EVENT_TYPE_CLASSIFICATION,
525 EXPENDITURE_CATEGORY,
526 REVENUE_CATEGORY,
527 NON_LABOR_RESOURCE_ID,
528 BOM_LABOR_RESOURCE_ID,
529 BOM_EQUIPMENT_RESOURCE_ID,
530 ITEM_CATEGORY_ID,
531 INVENTORY_ITEM_ID,
532 PROJECT_ROLE_ID,
533 NAMED_ROLE,
534 PERSON_TYPE,
535 SYSTEM_LINKAGE_FUNCTION,
536 PROJECT_ID,
537 PROJECT_ORG_ID,
538 PROJECT_ORGANIZATION_ID,
539 PROJECT_TYPE_CLASS,
540 TASK_ID,
541 ASSIGNMENT_ID,
542 RECVR_PERIOD_TYPE,
543 RECVR_PERIOD_ID,
544 TXN_CURRENCY_CODE,
545 TXN_REVENUE,
546 TXN_RAW_COST,
547 TXN_BRDN_COST,
548 TXN_BILL_RAW_COST,
549 TXN_BILL_BRDN_COST,
550 TXN_SUP_INV_COMMITTED_COST,
551 TXN_PO_COMMITTED_COST,
552 TXN_PR_COMMITTED_COST,
553 TXN_OTH_COMMITTED_COST,
554 PRJ_REVENUE,
555 PRJ_RAW_COST,
556 PRJ_BRDN_COST,
557 PRJ_BILL_RAW_COST,
558 PRJ_BILL_BRDN_COST,
559 PRJ_REVENUE_WRITEOFF,
560 PRJ_SUP_INV_COMMITTED_COST,
561 PRJ_PO_COMMITTED_COST,
562 PRJ_PR_COMMITTED_COST,
563 PRJ_OTH_COMMITTED_COST,
564 POU_REVENUE,
565 POU_RAW_COST,
566 POU_BRDN_COST,
567 POU_BILL_RAW_COST,
568 POU_BILL_BRDN_COST,
569 POU_REVENUE_WRITEOFF,
570 POU_SUP_INV_COMMITTED_COST,
571 POU_PO_COMMITTED_COST,
572 POU_PR_COMMITTED_COST,
573 POU_OTH_COMMITTED_COST,
574 EOU_REVENUE,
575 EOU_RAW_COST,
576 EOU_BRDN_COST,
577 EOU_BILL_RAW_COST,
578 EOU_BILL_BRDN_COST,
579 EOU_SUP_INV_COMMITTED_COST,
580 EOU_PO_COMMITTED_COST,
581 EOU_PR_COMMITTED_COST,
582 EOU_OTH_COMMITTED_COST,
583 QUANTITY,
584 BILL_QUANTITY,
585 G1_REVENUE,
586 G1_RAW_COST,
587 G1_BRDN_COST,
588 G1_BILL_RAW_COST,
589 G1_BILL_BRDN_COST,
590 G1_REVENUE_WRITEOFF,
591 G1_SUP_INV_COMMITTED_COST,
592 G1_PO_COMMITTED_COST,
593 G1_PR_COMMITTED_COST,
594 G1_OTH_COMMITTED_COST,
595 G2_REVENUE,
596 G2_RAW_COST,
597 G2_BRDN_COST,
598 G2_BILL_RAW_COST,
599 G2_BILL_BRDN_COST,
600 G2_REVENUE_WRITEOFF,
601 G2_SUP_INV_COMMITTED_COST,
602 G2_PO_COMMITTED_COST,
603 G2_PR_COMMITTED_COST,
604 G2_OTH_COMMITTED_COST,
605 CBS_ELEMENT_ID /*Added for CBS Changes */
606 )
607 select /*+ full(tmp2) parallel(tmp2) use_hash(tmp2)
608 full(gl_cal) parallel(gl_cal) use_hash(gl_cal)
609 full(pa_cal) parallel(pa_cal) use_hash(pa_cal)
610 full(res) use_hash(res)
611 full(res_typs) use_hash(res_typs)
612 full(mcsts) use_hash(mcsts)
613 full(cls) use_hash(cls)
614 parallel(cat) */
615 p_worker_id WORKER_ID,
616 tmp2.RECORD_TYPE,
617 tmp2.PERSON_ID PERSON_ID,
618 -- temporary fix for bug 3660160
619 -1 EXPENDITURE_ORG_ID,
620 -- tmp2.EXPENDITURE_ORG_ID EXPENDITURE_ORG_ID,
621 tmp2.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID,
622 nvl(res_typs.RESOURCE_CLASS_ID, -1) RESOURCE_CLASS_ID,
623 tmp2.JOB_ID,
624 tmp2.VENDOR_ID,
625 -- temporary fix for bug 3660160
626 -1 WORK_TYPE_ID,
627 -- tmp2.WORK_TYPE_ID,
628 nvl(exp_cat.EXPENDITURE_CATEGORY_ID, -1) EXPENDITURE_CATEGORY_ID,
629 decode(tmp2.EVENT_TYPE, 'PJI$NULL',
630 tmp2.EXP_EVT_TYPE_ID, -1) EXPENDITURE_TYPE_ID,
631 decode(tmp2.EXPENDITURE_TYPE, 'PJI$NULL',
632 tmp2.EXP_EVT_TYPE_ID, -1) EVENT_TYPE_ID,
633 -- temporary fix for bug 3813982
634 -1 EXP_EVT_TYPE_ID,
635 -- tmp2.EXP_EVT_TYPE_ID,
636 -- temporary fix for bug 3813982
637 -- 'PJI$NULL' EXPENDITURE_TYPE,
638 tmp2.EXPENDITURE_TYPE,
639 tmp2.EVENT_TYPE,
640 tmp2.EVENT_TYPE_CLASSIFICATION,
641 -- temporary fix for bug 3813982
642 -- 'PJI$NULL' EXPENDITURE_CATEGORY,
643 tmp2.EXPENDITURE_CATEGORY,
644 tmp2.REVENUE_CATEGORY,
645 tmp2.NON_LABOR_RESOURCE_ID,
646 tmp2.BOM_LABOR_RESOURCE_ID,
647 tmp2.BOM_EQUIPMENT_RESOURCE_ID,
648 nvl(inv.ITEM_CATEGORY_ID, -1) ITEM_CATEGORY_ID,
649 tmp2.INVENTORY_ITEM_ID,
650 tmp2.PROJECT_ROLE_ID,
651 tmp2.NAMED_ROLE,
652 tmp2.PERSON_TYPE,
653 -- temporary fix for bug 3813982
654 'PJI$NULL' SYSTEM_LINKAGE_FUNCTION,
655 -- tmp2.SYSTEM_LINKAGE_FUNCTION,
656 tmp2.PROJECT_ID,
657 tmp2.PROJECT_ORG_ID,
658 tmp2.PROJECT_ORGANIZATION_ID,
659 tmp2.PROJECT_TYPE_CLASS,
660 tmp2.TASK_ID,
661 tmp2.ASSIGNMENT_ID,
662 decode(invert.INVERT_ID,
663 'ENT', 'ENT',
664 'GL', 'GL',
665 'PA', 'PA') RECVR_PERIOD_TYPE,
666 decode(invert.INVERT_ID,
667 'ENT', tmp2.RECVR_ENT_PERIOD_ID,
668 'GL', gl_cal.CAL_PERIOD_ID,
669 'PA', pa_cal.CAL_PERIOD_ID) RECVR_PERIOD_ID,
670 tmp2.TXN_CURRENCY_CODE,
671 sum(decode(tmp2.RECORD_TYPE,
672 'A', tmp2.TXN_REVENUE,
673 to_number(null))) TXN_REVENUE,
674 sum(decode(tmp2.RECORD_TYPE,
675 'A', tmp2.TXN_RAW_COST,
676 to_number(null))) TXN_RAW_COST,
677 sum(decode(tmp2.RECORD_TYPE,
678 'A', tmp2.TXN_BRDN_COST,
679 to_number(null))) TXN_BRDN_COST,
680 sum(decode(tmp2.RECORD_TYPE,
681 'A', tmp2.TXN_BILL_RAW_COST,
682 to_number(null))) TXN_BILL_RAW_COST,
683 sum(decode(tmp2.RECORD_TYPE,
684 'A', tmp2.TXN_BILL_BRDN_COST,
685 to_number(null))) TXN_BILL_BRDN_COST,
686 sum(decode(tmp2.RECORD_TYPE || '_' ||
687 tmp2.CMT_RECORD_TYPE,
688 'M_I', tmp2.TXN_BRDN_COST,
689 to_number(null))) TXN_SUP_INV_COMMITTED_COST,
690 sum(decode(tmp2.RECORD_TYPE || '_' ||
691 tmp2.CMT_RECORD_TYPE,
692 'M_P', tmp2.TXN_BRDN_COST,
693 to_number(null))) TXN_PO_COMMITTED_COST,
694 sum(decode(tmp2.RECORD_TYPE || '_' ||
695 tmp2.CMT_RECORD_TYPE,
696 'M_R', tmp2.TXN_BRDN_COST,
697 to_number(null))) TXN_PR_COMMITTED_COST,
698 sum(decode(tmp2.RECORD_TYPE || '_' ||
699 tmp2.CMT_RECORD_TYPE,
700 'M_O', tmp2.TXN_BRDN_COST,
701 to_number(null))) TXN_OTH_COMMITTED_COST,
702 sum(decode(tmp2.RECORD_TYPE,
703 'A', tmp2.PRJ_REVENUE,
704 to_number(null))) PRJ_REVENUE,
705 sum(decode(tmp2.RECORD_TYPE,
706 'A', tmp2.PRJ_RAW_COST,
707 to_number(null))) PRJ_RAW_COST,
708 sum(decode(tmp2.RECORD_TYPE,
709 'A', tmp2.PRJ_BRDN_COST,
710 to_number(null))) PRJ_BRDN_COST,
711 sum(decode(tmp2.RECORD_TYPE,
712 'A', tmp2.PRJ_BILL_RAW_COST,
713 to_number(null))) PRJ_BILL_RAW_COST,
714 sum(decode(tmp2.RECORD_TYPE,
715 'A', tmp2.PRJ_BILL_BRDN_COST,
716 to_number(null))) PRJ_BILL_BRDN_COST,
717 sum(decode(tmp2.RECORD_TYPE,
718 'A', tmp2.PRJ_REVENUE_WRITEOFF,
719 to_number(null))) PRJ_REVENUE_WRITEOFF,
720 sum(decode(tmp2.RECORD_TYPE || '_' ||
721 tmp2.CMT_RECORD_TYPE,
722 'M_I', tmp2.PRJ_BRDN_COST,
723 to_number(null))) PRJ_SUP_INV_COMMITTED_COST,
724 sum(decode(tmp2.RECORD_TYPE || '_' ||
725 tmp2.CMT_RECORD_TYPE,
726 'M_P', tmp2.PRJ_BRDN_COST,
727 to_number(null))) PRJ_PO_COMMITTED_COST,
728 sum(decode(tmp2.RECORD_TYPE || '_' ||
729 tmp2.CMT_RECORD_TYPE,
730 'M_R', tmp2.PRJ_BRDN_COST,
731 to_number(null))) PRJ_PR_COMMITTED_COST,
732 sum(decode(tmp2.RECORD_TYPE || '_' ||
733 tmp2.CMT_RECORD_TYPE,
734 'M_O', tmp2.PRJ_BRDN_COST,
735 to_number(null))) PRJ_OTH_COMMITTED_COST,
736 sum(decode(tmp2.RECORD_TYPE,
737 'A', tmp2.POU_REVENUE,
738 to_number(null))) POU_REVENUE,
739 sum(decode(tmp2.RECORD_TYPE,
740 'A', tmp2.POU_RAW_COST,
741 to_number(null))) POU_RAW_COST,
742 sum(decode(tmp2.RECORD_TYPE,
743 'A', tmp2.POU_BRDN_COST,
744 to_number(null))) POU_BRDN_COST,
745 sum(decode(tmp2.RECORD_TYPE,
746 'A', tmp2.POU_BILL_RAW_COST,
747 to_number(null))) POU_BILL_RAW_COST,
748 sum(decode(tmp2.RECORD_TYPE,
749 'A', tmp2.POU_BILL_BRDN_COST,
750 to_number(null))) POU_BILL_BRDN_COST,
751 sum(decode(tmp2.RECORD_TYPE,
752 'A', tmp2.POU_REVENUE_WRITEOFF,
753 to_number(null))) POU_REVENUE_WRITEOFF,
754 sum(decode(tmp2.RECORD_TYPE || '_' ||
755 tmp2.CMT_RECORD_TYPE,
756 'M_I', tmp2.POU_BRDN_COST,
757 to_number(null))) POU_SUP_INV_COMMITTED_COST,
758 sum(decode(tmp2.RECORD_TYPE || '_' ||
759 tmp2.CMT_RECORD_TYPE,
760 'M_P', tmp2.POU_BRDN_COST,
761 to_number(null))) POU_PO_COMMITTED_COST,
762 sum(decode(tmp2.RECORD_TYPE || '_' ||
763 tmp2.CMT_RECORD_TYPE,
764 'M_R', tmp2.POU_BRDN_COST,
765 to_number(null))) POU_PR_COMMITTED_COST,
766 sum(decode(tmp2.RECORD_TYPE || '_' ||
767 tmp2.CMT_RECORD_TYPE,
768 'M_O', tmp2.POU_BRDN_COST,
769 to_number(null))) POU_OTH_COMMITTED_COST,
770 sum(decode(tmp2.RECORD_TYPE,
771 'A', tmp2.EOU_REVENUE,
772 to_number(null))) EOU_REVENUE,
773 sum(decode(tmp2.RECORD_TYPE,
774 'A', tmp2.EOU_RAW_COST,
775 to_number(null))) EOU_RAW_COST,
776 sum(decode(tmp2.RECORD_TYPE,
777 'A', tmp2.EOU_BRDN_COST,
778 to_number(null))) EOU_BRDN_COST,
779 sum(decode(tmp2.RECORD_TYPE,
780 'A', tmp2.EOU_BILL_RAW_COST,
781 to_number(null))) EOU_BILL_RAW_COST,
782 sum(decode(tmp2.RECORD_TYPE,
783 'A', tmp2.EOU_BILL_BRDN_COST,
784 to_number(null))) EOU_BILL_BRDN_COST,
785 sum(decode(tmp2.RECORD_TYPE || '_' ||
786 tmp2.CMT_RECORD_TYPE,
787 'M_I', tmp2.EOU_BRDN_COST,
788 to_number(null))) EOU_SUP_INV_COMMITTED_COST,
789 sum(decode(tmp2.RECORD_TYPE || '_' ||
790 tmp2.CMT_RECORD_TYPE,
791 'M_P', tmp2.EOU_BRDN_COST,
792 to_number(null))) EOU_PO_COMMITTED_COST,
793 sum(decode(tmp2.RECORD_TYPE || '_' ||
794 tmp2.CMT_RECORD_TYPE,
795 'M_R', tmp2.EOU_BRDN_COST,
796 to_number(null))) EOU_PR_COMMITTED_COST,
797 sum(decode(tmp2.RECORD_TYPE || '_' ||
798 tmp2.CMT_RECORD_TYPE,
799 'M_O', tmp2.EOU_BRDN_COST,
800 to_number(null))) EOU_OTH_COMMITTED_COST,
801 sum(decode(tmp2.RECORD_TYPE,
802 'A', tmp2.QUANTITY,
803 to_number(null))) QUANTITY,
804 sum(decode(tmp2.RECORD_TYPE,
805 'A', tmp2.BILL_QUANTITY,
806 to_number(null))) BILL_QUANTITY,
807 sum(decode(tmp2.RECORD_TYPE || '_' ||
808 invert.INVERT_ID,
809 'A_ENT', tmp2.GG1_REVENUE,
810 'A_GL', tmp2.GG1_REVENUE,
811 'A_PA', tmp2.GP1_REVENUE,
812 to_number(null))) G1_REVENUE,
813 sum(decode(tmp2.RECORD_TYPE || '_' ||
814 invert.INVERT_ID,
815 'A_ENT', tmp2.GG1_RAW_COST,
816 'A_GL', tmp2.GG1_RAW_COST,
817 'A_PA', tmp2.GP1_RAW_COST,
818 to_number(null))) G1_RAW_COST,
819 sum(decode(tmp2.RECORD_TYPE || '_' ||
820 invert.INVERT_ID,
821 'A_ENT', tmp2.GG1_BRDN_COST,
822 'A_GL', tmp2.GG1_BRDN_COST,
823 'A_PA', tmp2.GP1_BRDN_COST,
824 to_number(null))) G1_BRDN_COST,
825 sum(decode(tmp2.RECORD_TYPE || '_' ||
826 invert.INVERT_ID,
827 'A_ENT', tmp2.GG1_BILL_RAW_COST,
828 'A_GL', tmp2.GG1_BILL_RAW_COST,
829 'A_PA', tmp2.GP1_BILL_RAW_COST,
830 to_number(null))) G1_BILL_RAW_COST,
831 sum(decode(tmp2.RECORD_TYPE || '_' ||
832 invert.INVERT_ID,
833 'A_ENT', tmp2.GG1_BILL_BRDN_COST,
834 'A_GL', tmp2.GG1_BILL_BRDN_COST,
835 'A_PA', tmp2.GP1_BILL_BRDN_COST,
836 to_number(null))) G1_BILL_BRDN_COST,
837 sum(decode(tmp2.RECORD_TYPE || '_' ||
838 invert.INVERT_ID,
839 'A_ENT', tmp2.GG1_REVENUE_WRITEOFF,
840 'A_GL', tmp2.GG1_REVENUE_WRITEOFF,
841 'A_PA', tmp2.GP1_REVENUE_WRITEOFF,
842 to_number(null))) G1_REVENUE_WRITEOFF,
843 sum(decode(tmp2.RECORD_TYPE || '_' ||
844 invert.INVERT_ID || '_' ||
845 tmp2.CMT_RECORD_TYPE,
846 'M_ENT_I', tmp2.GG1_BRDN_COST,
847 'M_GL_I', tmp2.GG1_BRDN_COST,
848 'M_PA_I', tmp2.GP1_BRDN_COST,
849 to_number(null))) G1_SUP_INV_COMMITTED_COST, -- Bug 6410765. Modified from M_GL_I to M_ENT_I
850 sum(decode(tmp2.RECORD_TYPE || '_' ||
851 invert.INVERT_ID || '_' ||
852 tmp2.CMT_RECORD_TYPE,
853 'M_ENT_P', tmp2.GG1_BRDN_COST,
854 'M_GL_P', tmp2.GG1_BRDN_COST,
855 'M_PA_P', tmp2.GP1_BRDN_COST,
856 to_number(null))) G1_PO_COMMITTED_COST, -- Bug 6410765. Modified from M_GL_P to M_ENT_P
857 sum(decode(tmp2.RECORD_TYPE || '_' ||
858 invert.INVERT_ID || '_' ||
859 tmp2.CMT_RECORD_TYPE,
860 'M_ENT_R', tmp2.GG1_BRDN_COST,
861 'M_GL_R', tmp2.GG1_BRDN_COST,
862 'M_PA_R', tmp2.GP1_BRDN_COST,
863 to_number(null))) G1_PR_COMMITTED_COST, -- Bug 6410765. Modified from M_GL_R to M_ENT_R
864 sum(decode(tmp2.RECORD_TYPE || '_' ||
865 invert.INVERT_ID || '_' ||
866 tmp2.CMT_RECORD_TYPE,
867 'M_ENT_O', tmp2.GG1_BRDN_COST,
868 'M_GL_O', tmp2.GG1_BRDN_COST,
869 'M_PA_O', tmp2.GP1_BRDN_COST,
870 to_number(null))) G1_OTH_COMMITTED_COST, -- Bug 6410765. Modified from M_GL_O to M_ENT_O
871 sum(decode(tmp2.RECORD_TYPE || '_' ||
872 invert.INVERT_ID,
873 'A_ENT', tmp2.GG2_REVENUE,
874 'A_GL', tmp2.GG2_REVENUE,
875 'A_PA', tmp2.GP2_REVENUE,
876 to_number(null))) G2_REVENUE,
877 sum(decode(tmp2.RECORD_TYPE || '_' ||
878 invert.INVERT_ID,
879 'A_ENT', tmp2.GG2_RAW_COST,
880 'A_GL', tmp2.GG2_RAW_COST,
881 'A_PA', tmp2.GP2_RAW_COST,
882 to_number(null))) G2_RAW_COST,
883 sum(decode(tmp2.RECORD_TYPE || '_' ||
884 invert.INVERT_ID,
885 'A_ENT', tmp2.GG2_BRDN_COST,
886 'A_GL', tmp2.GG2_BRDN_COST,
887 'A_PA', tmp2.GP2_BRDN_COST,
888 to_number(null))) G2_BRDN_COST,
889 sum(decode(tmp2.RECORD_TYPE || '_' ||
890 invert.INVERT_ID,
891 'A_ENT', tmp2.GG2_BILL_RAW_COST,
892 'A_GL', tmp2.GG2_BILL_RAW_COST,
893 'A_PA', tmp2.GP2_BILL_RAW_COST,
894 to_number(null))) G2_BILL_RAW_COST,
895 sum(decode(tmp2.RECORD_TYPE || '_' ||
896 invert.INVERT_ID,
897 'A_ENT', tmp2.GG2_BILL_BRDN_COST,
898 'A_GL', tmp2.GG2_BILL_BRDN_COST,
899 'A_PA', tmp2.GP2_BILL_BRDN_COST,
900 to_number(null))) G2_BILL_BRDN_COST,
901 sum(decode(tmp2.RECORD_TYPE || '_' ||
902 invert.INVERT_ID,
903 'A_ENT', tmp2.GG2_REVENUE_WRITEOFF,
904 'A_GL', tmp2.GG2_REVENUE_WRITEOFF,
905 'A_PA', tmp2.GP2_REVENUE_WRITEOFF,
906 to_number(null))) G2_REVENUE_WRITEOFF,
907 sum(decode(tmp2.RECORD_TYPE || '_' ||
908 invert.INVERT_ID || '_' ||
909 tmp2.CMT_RECORD_TYPE,
910 'M_ENT_I', tmp2.GG2_BRDN_COST,
911 'M_GL_I', tmp2.GG2_BRDN_COST,
912 'M_PA_I', tmp2.GP2_BRDN_COST,
913 to_number(null))) G2_SUP_INV_COMMITTED_COST,
914 sum(decode(tmp2.RECORD_TYPE || '_' ||
915 invert.INVERT_ID || '_' ||
916 tmp2.CMT_RECORD_TYPE,
917 'M_ENT_P', tmp2.GG2_BRDN_COST,
918 'M_GL_P', tmp2.GG2_BRDN_COST,
919 'M_PA_P', tmp2.GP2_BRDN_COST,
920 to_number(null))) G2_PO_COMMITTED_COST,
921 sum(decode(tmp2.RECORD_TYPE || '_' ||
922 invert.INVERT_ID || '_' ||
923 tmp2.CMT_RECORD_TYPE,
924 'M_ENT_R', tmp2.GG2_BRDN_COST,
925 'M_GL_R', tmp2.GG2_BRDN_COST,
926 'M_PA_R', tmp2.GP2_BRDN_COST,
927 to_number(null))) G2_PR_COMMITTED_COST,
928 sum(decode(tmp2.RECORD_TYPE || '_' ||
929 invert.INVERT_ID || '_' ||
930 tmp2.CMT_RECORD_TYPE,
931 'M_ENT_O', tmp2.GG2_BRDN_COST,
932 'M_GL_O', tmp2.GG2_BRDN_COST,
933 'M_PA_O', tmp2.GP2_BRDN_COST,
934 to_number(null))) G2_OTH_COMMITTED_COST,
935 tmp2.CBS_ELEMENT_ID CBS_ELEMENT_ID /*Added for CBS Changes */
936 from
937 (
938 select /*+ ordered
939 full(tmp2) parallel(tmp2) use_hash(tmp2)
940 full(ent) parallel(ent) use_hash(ent) */
941 tmp2.RECORD_TYPE,
942 tmp2.CMT_RECORD_TYPE,
943 nvl(tmp2.PERSON_ID, -1) PERSON_ID,
944 nvl(tmp2.EXPENDITURE_ORG_ID, -1) EXPENDITURE_ORG_ID,
945 nvl(tmp2.EXPENDITURE_ORGANIZATION_ID, -1) EXPENDITURE_ORGANIZATION_ID,
946 nvl(tmp2.JOB_ID, -1) JOB_ID,
947 nvl(tmp2.VENDOR_ID, -1) VENDOR_ID,
948 nvl(tmp2.WORK_TYPE_ID, -1) WORK_TYPE_ID,
949 nvl(tmp2.EXP_EVT_TYPE_ID, -1) EXP_EVT_TYPE_ID,
950 nvl(tmp2.EXPENDITURE_TYPE, 'PJI$NULL') EXPENDITURE_TYPE,
951 nvl(tmp2.EVENT_TYPE, 'PJI$NULL') EVENT_TYPE,
952 nvl(tmp2.EVENT_TYPE_CLASSIFICATION, 'PJI$NULL')
953 EVENT_TYPE_CLASSIFICATION,
954 nvl(tmp2.EXPENDITURE_CATEGORY, 'PJI$NULL') EXPENDITURE_CATEGORY,
955 nvl(tmp2.REVENUE_CATEGORY, 'PJI$NULL') REVENUE_CATEGORY,
956 nvl(nlr.NON_LABOR_RESOURCE_ID, -1) NON_LABOR_RESOURCE_ID,
957 decode(tmp2.SYSTEM_LINKAGE_FUNCTION || decode(bom.RESOURCE_TYPE, 2,
958 '$PEOPLE', null),
959 'WIP$PEOPLE', nvl(tmp2.BOM_LABOR_RESOURCE_ID, -1),
960 -1) BOM_LABOR_RESOURCE_ID,
961 decode(tmp2.SYSTEM_LINKAGE_FUNCTION || decode(bom.RESOURCE_TYPE, 1,
962 '$EQUIPMENT', null),
963 'WIP$EQUIPMENT', nvl(tmp2.BOM_EQUIPMENT_RESOURCE_ID, -1),
964 -1) BOM_EQUIPMENT_RESOURCE_ID,
965 nvl(tmp2.INVENTORY_ITEM_ID, -1) INVENTORY_ITEM_ID,
966 nvl(asg.PROJECT_ROLE_ID, -1) PROJECT_ROLE_ID,
967 nvl(asg.ASSIGNMENT_NAME, 'PJI$NULL') NAMED_ROLE,
968 nvl(typ.SYSTEM_PERSON_TYPE, 'PJI$NULL') PERSON_TYPE,
969 nvl(tmp2.SYSTEM_LINKAGE_FUNCTION, 'PJI$NULL')
970 SYSTEM_LINKAGE_FUNCTION,
971 decode
972 (tmp2.RECORD_TYPE,
973 'A',
974 decode
975 (tmp2.SYSTEM_LINKAGE_FUNCTION,
976 'WIP', 'WIP$' || decode
977 (bom.RESOURCE_TYPE,
978 1, 'EQUIPMENT',
979 2, 'PEOPLE',
980 'OTHER'),
981 'USG', 'USG$' || nvl(nlr.EQUIPMENT_RESOURCE_FLAG, 'N'),
982 'VI', 'VI$' || decode
983 (nvl(tmp2.INVENTORY_ITEM_ID, -1),
984 -1,
985 decode
986 (lt.ORDER_TYPE_LOOKUP_CODE,
987 'RATE',
988 decode
989 (imp.XFACE_CWK_TIMECARDS_FLAG,
990 'Y', 'PEOPLE', 'FINANCIAL'),
991 'FINANCIAL'),
992 'MATERIAL'),
993 nvl(tmp2.SYSTEM_LINKAGE_FUNCTION, 'PJI$NULL')),
994 'M',
995 tmp2.RESOURCE_CLASS_CODE) SYSTEM_LINKAGE_FUNCTION_R,
996 tmp2.PROJECT_ID,
997 tmp2.PROJECT_ORG_ID,
998 tmp2.PROJECT_ORGANIZATION_ID,
999 tmp2.PROJECT_TYPE_CLASS,
1000 tmp2.TASK_ID,
1001 tmp2.ASSIGNMENT_ID,
1002 ent.ENT_PERIOD_ID RECVR_ENT_PERIOD_ID,
1003 tmp2.GL_PERIOD_NAME RECVR_GL_PERIOD_NAME,
1004 tmp2.PA_PERIOD_NAME RECVR_PA_PERIOD_NAME,
1005 tmp2.PJ_GL_CALENDAR_ID RECVR_GL_CALENDAR_ID,
1006 tmp2.PJ_PA_CALENDAR_ID RECVR_PA_CALENDAR_ID,
1007 tmp2.TXN_CURRENCY_CODE,
1008 sum(tmp2.TXN_REVENUE) TXN_REVENUE,
1009 sum(tmp2.TXN_RAW_COST) TXN_RAW_COST,
1010 sum(tmp2.TXN_BRDN_COST) TXN_BRDN_COST,
1011 sum(tmp2.TXN_BILL_RAW_COST) TXN_BILL_RAW_COST,
1012 sum(tmp2.TXN_BILL_BRDN_COST) TXN_BILL_BRDN_COST,
1013 sum(tmp2.PRJ_REVENUE) PRJ_REVENUE,
1014 sum(tmp2.PRJ_RAW_COST) PRJ_RAW_COST,
1015 sum(tmp2.PRJ_BRDN_COST) PRJ_BRDN_COST,
1016 sum(tmp2.PRJ_BILL_RAW_COST) PRJ_BILL_RAW_COST,
1017 sum(tmp2.PRJ_BILL_BRDN_COST) PRJ_BILL_BRDN_COST,
1018 sum(tmp2.PRJ_REVENUE_WRITEOFF) PRJ_REVENUE_WRITEOFF,
1019 sum(tmp2.POU_REVENUE) POU_REVENUE,
1020 sum(tmp2.POU_RAW_COST) POU_RAW_COST,
1021 sum(tmp2.POU_BRDN_COST) POU_BRDN_COST,
1022 sum(tmp2.POU_BILL_RAW_COST) POU_BILL_RAW_COST,
1023 sum(tmp2.POU_BILL_BRDN_COST) POU_BILL_BRDN_COST,
1024 sum(tmp2.POU_REVENUE_WRITEOFF) POU_REVENUE_WRITEOFF,
1025 sum(tmp2.EOU_REVENUE) EOU_REVENUE,
1026 sum(tmp2.EOU_RAW_COST) EOU_RAW_COST,
1027 sum(tmp2.EOU_BRDN_COST) EOU_BRDN_COST,
1028 sum(tmp2.EOU_BILL_RAW_COST) EOU_BILL_RAW_COST,
1029 sum(tmp2.EOU_BILL_BRDN_COST) EOU_BILL_BRDN_COST,
1030 sum(tmp2.TOTAL_HRS_A) QUANTITY,
1031 sum(tmp2.BILL_HRS_A) BILL_QUANTITY,
1032 sum(tmp2.GG1_REVENUE) GG1_REVENUE,
1033 sum(tmp2.GG1_RAW_COST) GG1_RAW_COST,
1034 sum(tmp2.GG1_BRDN_COST) GG1_BRDN_COST,
1035 sum(tmp2.GG1_BILL_RAW_COST) GG1_BILL_RAW_COST,
1036 sum(tmp2.GG1_BILL_BRDN_COST) GG1_BILL_BRDN_COST,
1037 sum(tmp2.GG1_REVENUE_WRITEOFF) GG1_REVENUE_WRITEOFF,
1038 sum(tmp2.GP1_REVENUE) GP1_REVENUE,
1039 sum(tmp2.GP1_RAW_COST) GP1_RAW_COST,
1040 sum(tmp2.GP1_BRDN_COST) GP1_BRDN_COST,
1041 sum(tmp2.GP1_BILL_RAW_COST) GP1_BILL_RAW_COST,
1042 sum(tmp2.GP1_BILL_BRDN_COST) GP1_BILL_BRDN_COST,
1043 sum(tmp2.GP1_REVENUE_WRITEOFF) GP1_REVENUE_WRITEOFF,
1044 sum(tmp2.GG2_REVENUE) GG2_REVENUE,
1045 sum(tmp2.GG2_RAW_COST) GG2_RAW_COST,
1046 sum(tmp2.GG2_BRDN_COST) GG2_BRDN_COST,
1047 sum(tmp2.GG2_BILL_RAW_COST) GG2_BILL_RAW_COST,
1048 sum(tmp2.GG2_BILL_BRDN_COST) GG2_BILL_BRDN_COST,
1049 sum(tmp2.GG2_REVENUE_WRITEOFF) GG2_REVENUE_WRITEOFF,
1050 sum(tmp2.GP2_REVENUE) GP2_REVENUE,
1051 sum(tmp2.GP2_RAW_COST) GP2_RAW_COST,
1052 sum(tmp2.GP2_BRDN_COST) GP2_BRDN_COST,
1053 sum(tmp2.GP2_BILL_RAW_COST) GP2_BILL_RAW_COST,
1054 sum(tmp2.GP2_BILL_BRDN_COST) GP2_BILL_BRDN_COST,
1055 sum(tmp2.GP2_REVENUE_WRITEOFF) GP2_REVENUE_WRITEOFF,
1056 tmp2.CBS_ELEMENT_ID CBS_ELEMENT_ID /*Added for CBS Changes */
1057 from
1058 (
1059 select /*+ parallel(tmp2) */ /* Added for bug 13030627 */
1060 tmp2.WORKER_ID,
1061 tmp2.RECORD_TYPE,
1062 tmp2.CMT_RECORD_TYPE,
1063 tmp2.PERSON_ID,
1064 tmp2.EXPENDITURE_ORG_ID,
1065 tmp2.EXPENDITURE_ORGANIZATION_ID,
1066 tmp2.JOB_ID,
1067 tmp2.VENDOR_ID,
1068 tmp2.WORK_TYPE_ID,
1069 tmp2.EXP_EVT_TYPE_ID,
1070 tmp2.EXPENDITURE_TYPE,
1071 tmp2.EVENT_TYPE,
1072 tmp2.EVENT_TYPE_CLASSIFICATION,
1073 tmp2.EXPENDITURE_CATEGORY,
1074 tmp2.REVENUE_CATEGORY,
1075 tmp2.NON_LABOR_RESOURCE,
1076 tmp2.BOM_LABOR_RESOURCE_ID,
1077 tmp2.BOM_EQUIPMENT_RESOURCE_ID,
1078 tmp2.INVENTORY_ITEM_ID,
1079 tmp2.PO_LINE_ID,
1080 tmp2.SYSTEM_LINKAGE_FUNCTION,
1081 tmp2.RESOURCE_CLASS_CODE,
1082 tmp2.PROJECT_ID,
1083 tmp2.PROJECT_ORG_ID,
1084 tmp2.PROJECT_ORGANIZATION_ID,
1085 tmp2.PROJECT_TYPE_CLASS,
1086 tmp2.TASK_ID,
1087 tmp2.ASSIGNMENT_ID,
1088 tmp2.RECVR_GL_TIME_ID,
1089 tmp2.GL_PERIOD_NAME,
1090 tmp2.PA_PERIOD_NAME,
1091 tmp2.PJ_GL_CALENDAR_ID,
1092 tmp2.PJ_PA_CALENDAR_ID,
1093 tmp2.TXN_CURRENCY_CODE,
1094 tmp2.TXN_REVENUE,
1095 tmp2.TXN_RAW_COST,
1096 tmp2.TXN_BRDN_COST,
1097 tmp2.TXN_BILL_RAW_COST,
1098 tmp2.TXN_BILL_BRDN_COST,
1099 tmp2.PRJ_REVENUE,
1100 tmp2.PRJ_RAW_COST,
1101 tmp2.PRJ_BRDN_COST,
1102 tmp2.PRJ_BILL_RAW_COST,
1103 tmp2.PRJ_BILL_BRDN_COST,
1104 tmp2.PRJ_REVENUE_WRITEOFF,
1105 tmp2.POU_REVENUE,
1106 tmp2.POU_RAW_COST,
1107 tmp2.POU_BRDN_COST,
1108 tmp2.POU_BILL_RAW_COST,
1109 tmp2.POU_BILL_BRDN_COST,
1110 tmp2.POU_REVENUE_WRITEOFF,
1111 tmp2.EOU_REVENUE,
1112 tmp2.EOU_RAW_COST,
1113 tmp2.EOU_BRDN_COST,
1114 tmp2.EOU_BILL_RAW_COST,
1115 tmp2.EOU_BILL_BRDN_COST,
1116 tmp2.TOTAL_HRS_A,
1117 tmp2.BILL_HRS_A,
1118 tmp2.GG1_REVENUE,
1119 tmp2.GG1_RAW_COST,
1120 tmp2.GG1_BRDN_COST,
1121 tmp2.GG1_BILL_RAW_COST,
1122 tmp2.GG1_BILL_BRDN_COST,
1123 tmp2.GG1_REVENUE_WRITEOFF,
1124 tmp2.GP1_REVENUE,
1125 tmp2.GP1_RAW_COST,
1126 tmp2.GP1_BRDN_COST,
1127 tmp2.GP1_BILL_RAW_COST,
1128 tmp2.GP1_BILL_BRDN_COST,
1129 tmp2.GP1_REVENUE_WRITEOFF,
1130 tmp2.GG2_REVENUE,
1131 tmp2.GG2_RAW_COST,
1132 tmp2.GG2_BRDN_COST,
1133 tmp2.GG2_BILL_RAW_COST,
1134 tmp2.GG2_BILL_BRDN_COST,
1135 tmp2.GG2_REVENUE_WRITEOFF,
1136 tmp2.GP2_REVENUE,
1137 tmp2.GP2_RAW_COST,
1138 tmp2.GP2_BRDN_COST,
1139 tmp2.GP2_BILL_RAW_COST,
1140 tmp2.GP2_BILL_BRDN_COST,
1141 tmp2.GP2_REVENUE_WRITEOFF,
1142 tmp2.CBS_ELEMENT_ID /* Added for CBS Changes */
1143 from
1144 PJI_FM_AGGR_FIN2 tmp2
1145 where
1146 tmp2.WORKER_ID = p_worker_id and
1147 tmp2.GL_PERIOD_NAME is not null and
1148 tmp2.PA_PERIOD_NAME is not null and
1149 tmp2.PJI_PROJECT_RECORD_FLAG = 'Y'
1150 union all
1151 select /*+ ordered
1152 full(tmp2) parallel(tmp2) */
1153 tmp2.WORKER_ID,
1154 tmp2.RECORD_TYPE,
1155 tmp2.CMT_RECORD_TYPE,
1156 tmp2.PERSON_ID,
1157 tmp2.EXPENDITURE_ORG_ID,
1158 tmp2.EXPENDITURE_ORGANIZATION_ID,
1159 tmp2.JOB_ID,
1160 tmp2.VENDOR_ID,
1161 tmp2.WORK_TYPE_ID,
1162 tmp2.EXP_EVT_TYPE_ID,
1163 tmp2.EXPENDITURE_TYPE,
1164 tmp2.EVENT_TYPE,
1165 tmp2.EVENT_TYPE_CLASSIFICATION,
1166 tmp2.EXPENDITURE_CATEGORY,
1167 tmp2.REVENUE_CATEGORY,
1168 tmp2.NON_LABOR_RESOURCE,
1169 tmp2.BOM_LABOR_RESOURCE_ID,
1170 tmp2.BOM_EQUIPMENT_RESOURCE_ID,
1171 tmp2.INVENTORY_ITEM_ID,
1172 tmp2.PO_LINE_ID,
1173 tmp2.SYSTEM_LINKAGE_FUNCTION,
1174 tmp2.RESOURCE_CLASS_CODE,
1175 tmp2.PROJECT_ID,
1176 tmp2.PROJECT_ORG_ID,
1177 tmp2.PROJECT_ORGANIZATION_ID,
1178 tmp2.PROJECT_TYPE_CLASS,
1179 tmp2.TASK_ID,
1180 tmp2.ASSIGNMENT_ID,
1181 tmp2.RECVR_GL_TIME_ID,
1182 gl_per.PERIOD_NAME GL_PERIOD_NAME,
1183 pa_per.PERIOD_NAME PA_PERIOD_NAME,
1184 tmp2.PJ_GL_CALENDAR_ID,
1185 tmp2.PJ_PA_CALENDAR_ID,
1186 tmp2.TXN_CURRENCY_CODE,
1187 tmp2.TXN_REVENUE,
1188 tmp2.TXN_RAW_COST,
1189 tmp2.TXN_BRDN_COST,
1190 tmp2.TXN_BILL_RAW_COST,
1191 tmp2.TXN_BILL_BRDN_COST,
1192 tmp2.PRJ_REVENUE,
1193 tmp2.PRJ_RAW_COST,
1194 tmp2.PRJ_BRDN_COST,
1195 tmp2.PRJ_BILL_RAW_COST,
1196 tmp2.PRJ_BILL_BRDN_COST,
1197 tmp2.PRJ_REVENUE_WRITEOFF,
1198 tmp2.POU_REVENUE,
1199 tmp2.POU_RAW_COST,
1200 tmp2.POU_BRDN_COST,
1201 tmp2.POU_BILL_RAW_COST,
1202 tmp2.POU_BILL_BRDN_COST,
1203 tmp2.POU_REVENUE_WRITEOFF,
1204 tmp2.EOU_REVENUE,
1205 tmp2.EOU_RAW_COST,
1206 tmp2.EOU_BRDN_COST,
1207 tmp2.EOU_BILL_RAW_COST,
1208 tmp2.EOU_BILL_BRDN_COST,
1209 tmp2.TOTAL_HRS_A,
1210 tmp2.BILL_HRS_A,
1211 tmp2.GG1_REVENUE,
1212 tmp2.GG1_RAW_COST,
1213 tmp2.GG1_BRDN_COST,
1214 tmp2.GG1_BILL_RAW_COST,
1215 tmp2.GG1_BILL_BRDN_COST,
1216 tmp2.GG1_REVENUE_WRITEOFF,
1217 tmp2.GP1_REVENUE,
1218 tmp2.GP1_RAW_COST,
1219 tmp2.GP1_BRDN_COST,
1220 tmp2.GP1_BILL_RAW_COST,
1221 tmp2.GP1_BILL_BRDN_COST,
1222 tmp2.GP1_REVENUE_WRITEOFF,
1223 tmp2.GG2_REVENUE,
1224 tmp2.GG2_RAW_COST,
1225 tmp2.GG2_BRDN_COST,
1226 tmp2.GG2_BILL_RAW_COST,
1227 tmp2.GG2_BILL_BRDN_COST,
1228 tmp2.GG2_REVENUE_WRITEOFF,
1229 tmp2.GP2_REVENUE,
1230 tmp2.GP2_RAW_COST,
1231 tmp2.GP2_BRDN_COST,
1232 tmp2.GP2_BILL_RAW_COST,
1233 tmp2.GP2_BILL_BRDN_COST,
1234 tmp2.GP2_REVENUE_WRITEOFF,
1235 tmp2.CBS_ELEMENT_ID /*Added for CBS Changes */
1236 from
1237 PJI_FM_AGGR_FIN2 tmp2,
1238 PA_TIME_CAL_NAME gl_cal, /* Modified for bug 12979524 */
1239 GL_PERIODS gl_per,
1240 PA_PERIODS_ALL pa_per
1241 where
1242 tmp2.WORKER_ID = p_worker_id and
1243 (tmp2.GL_PERIOD_NAME is null or
1244 tmp2.PA_PERIOD_NAME is null) and
1245 tmp2.PJI_PROJECT_RECORD_FLAG = 'Y' and
1246 gl_cal.CALENDAR_ID = tmp2.PJ_GL_CALENDAR_ID and
1247 gl_per.PERIOD_SET_NAME = gl_cal.PERIOD_SET_NAME and
1248 gl_per.PERIOD_TYPE = gl_cal.PERIOD_TYPE and
1249 to_date(to_char(tmp2.RECVR_GL_TIME_ID), 'J')
1250 between gl_per.START_DATE and gl_per.END_DATE and
1251 pa_per.ORG_ID = tmp2.PROJECT_ORG_ID and
1252 to_date(to_char(tmp2.RECVR_PA_TIME_ID), 'J')
1253 between pa_per.START_DATE and pa_per.END_DATE
1254 ) tmp2,
1255 PJI_TIME_ENT_PERIOD_V ent,
1256 (
1257 select
1258 distinct
1259 usg.PERSON_ID,
1260 usg.EFFECTIVE_START_DATE,
1261 usg.EFFECTIVE_END_DATE,
1262 typ.SYSTEM_PERSON_TYPE
1263 from
1264 PER_PERSON_TYPES typ,
1265 PER_PERSON_TYPE_USAGES_F usg
1266 where
1267 typ.SYSTEM_PERSON_TYPE in ('EMP', 'CWK') and
1268 typ.PERSON_TYPE_ID = usg.PERSON_TYPE_ID
1269 ) typ, -- (+)
1270 BOM_RESOURCES bom, -- (+)
1271 PA_NON_LABOR_RESOURCES nlr, -- (+)
1272 PO_LINES_ALL pol, -- (+)
1273 PO_LINE_TYPES_B lt, -- (+)
1274 PA_PROJECT_ASSIGNMENTS asg, -- (+)
1275 PA_IMPLEMENTATIONS_ALL imp
1276 where
1277 tmp2.WORKER_ID = p_worker_id and
1278 to_date(to_char(tmp2.RECVR_GL_TIME_ID), 'J')
1279 between ent.START_DATE and ent.END_DATE and
1280 tmp2.PERSON_ID = typ.PERSON_ID (+) and
1281 to_date(to_char(tmp2.RECVR_GL_TIME_ID), 'J')
1282 between typ.EFFECTIVE_START_DATE (+) and
1283 typ.EFFECTIVE_END_DATE (+) and
1284 tmp2.NON_LABOR_RESOURCE = nlr.NON_LABOR_RESOURCE (+) and
1285 tmp2.BOM_LABOR_RESOURCE_ID = bom.RESOURCE_ID (+) and
1286 tmp2.PO_LINE_ID = pol.PO_LINE_ID (+) and
1287 pol.LINE_TYPE_ID = lt.LINE_TYPE_ID (+) and
1288 tmp2.ASSIGNMENT_ID = asg.ASSIGNMENT_ID (+) and
1289 nvl(tmp2.PROJECT_ORG_ID, -1) = nvl(imp.ORG_ID, -1)
1290 group by
1291 tmp2.RECORD_TYPE,
1292 tmp2.CMT_RECORD_TYPE,
1293 nvl(tmp2.PERSON_ID, -1),
1294 nvl(tmp2.EXPENDITURE_ORG_ID, -1),
1295 nvl(tmp2.EXPENDITURE_ORGANIZATION_ID, -1),
1296 nvl(tmp2.JOB_ID, -1),
1297 nvl(tmp2.VENDOR_ID, -1),
1298 nvl(tmp2.WORK_TYPE_ID, -1),
1299 nvl(tmp2.EXP_EVT_TYPE_ID, -1),
1300 nvl(tmp2.EXPENDITURE_TYPE, 'PJI$NULL'),
1301 nvl(tmp2.EVENT_TYPE, 'PJI$NULL'),
1302 nvl(tmp2.EVENT_TYPE_CLASSIFICATION, 'PJI$NULL'),
1303 nvl(tmp2.EXPENDITURE_CATEGORY, 'PJI$NULL'),
1304 nvl(tmp2.REVENUE_CATEGORY, 'PJI$NULL'),
1305 nvl(nlr.NON_LABOR_RESOURCE_ID, -1),
1306 decode(tmp2.SYSTEM_LINKAGE_FUNCTION || decode(bom.RESOURCE_TYPE, 2,
1307 '$PEOPLE', null),
1308 'WIP$PEOPLE', nvl(tmp2.BOM_LABOR_RESOURCE_ID, -1),
1309 -1),
1310 decode(tmp2.SYSTEM_LINKAGE_FUNCTION || decode(bom.RESOURCE_TYPE, 1,
1311 '$EQUIPMENT', null),
1312 'WIP$EQUIPMENT', nvl(tmp2.BOM_EQUIPMENT_RESOURCE_ID, -1),
1313 -1),
1314 nvl(tmp2.INVENTORY_ITEM_ID, -1),
1315 nvl(asg.PROJECT_ROLE_ID, -1),
1316 nvl(asg.ASSIGNMENT_NAME, 'PJI$NULL'),
1317 nvl(typ.SYSTEM_PERSON_TYPE, 'PJI$NULL'),
1318 nvl(tmp2.SYSTEM_LINKAGE_FUNCTION, 'PJI$NULL'),
1319 decode
1320 (tmp2.RECORD_TYPE,
1321 'A',
1322 decode
1323 (tmp2.SYSTEM_LINKAGE_FUNCTION,
1324 'WIP', 'WIP$' || decode
1325 (bom.RESOURCE_TYPE,
1326 1, 'EQUIPMENT',
1327 2, 'PEOPLE',
1328 'OTHER'),
1329 'USG', 'USG$' || nvl(nlr.EQUIPMENT_RESOURCE_FLAG, 'N'),
1330 'VI', 'VI$' || decode
1331 (nvl(tmp2.INVENTORY_ITEM_ID, -1),
1332 -1,
1333 decode
1334 (lt.ORDER_TYPE_LOOKUP_CODE,
1335 'RATE',
1336 decode
1337 (imp.XFACE_CWK_TIMECARDS_FLAG,
1338 'Y', 'PEOPLE', 'FINANCIAL'),
1339 'FINANCIAL'),
1340 'MATERIAL'),
1341 nvl(tmp2.SYSTEM_LINKAGE_FUNCTION, 'PJI$NULL')),
1342 'M',
1343 tmp2.RESOURCE_CLASS_CODE),
1344 tmp2.PROJECT_ID,
1345 tmp2.PROJECT_ORG_ID,
1346 tmp2.PROJECT_ORGANIZATION_ID,
1347 tmp2.PROJECT_TYPE_CLASS,
1348 tmp2.TASK_ID,
1349 tmp2.ASSIGNMENT_ID,
1350 ent.ENT_PERIOD_ID,
1351 tmp2.GL_PERIOD_NAME,
1352 tmp2.PA_PERIOD_NAME,
1353 tmp2.PJ_GL_CALENDAR_ID,
1354 tmp2.PJ_PA_CALENDAR_ID,
1355 tmp2.TXN_CURRENCY_CODE,
1356 tmp2.CBS_ELEMENT_ID /*Added for CBS Changes */
1357 ) tmp2,
1358 PJI_TIME_CAL_PERIOD_V gl_cal,
1359 PJI_TIME_CAL_PERIOD_V pa_cal,
1360 PJI_FM_AGGR_RES_TYPES res_typs,
1361 (
1362 select
1363 cat.CATEGORY_ID ITEM_CATEGORY_ID,
1364 cat.INVENTORY_ITEM_ID,
1365 cat.ORGANIZATION_ID
1366 from
1367 PA_RESOURCE_CLASSES_B classes,
1368 PA_PLAN_RES_DEFAULTS cls,
1369 MTL_ITEM_CATEGORIES cat -- (+) big
1370 where
1371 classes.RESOURCE_CLASS_CODE = 'MATERIAL_ITEMS' and
1372 cls.RESOURCE_CLASS_ID = classes.RESOURCE_CLASS_ID and
1373 cls.ITEM_CATEGORY_SET_ID = cat.CATEGORY_SET_ID
1374 ) inv,
1375 PA_EXPENDITURE_CATEGORIES exp_cat, -- (+)
1376 (
1377 select 'ENT' INVERT_ID from dual union all
1378 select 'GL' INVERT_ID from dual union all
1379 select 'PA' INVERT_ID from dual
1380 ) invert
1381 where
1382 tmp2.RECVR_GL_CALENDAR_ID = gl_cal.CALENDAR_ID and
1383 tmp2.RECVR_GL_PERIOD_NAME = gl_cal.NAME and
1384 tmp2.RECVR_PA_CALENDAR_ID = pa_cal.CALENDAR_ID and
1385 tmp2.RECVR_PA_PERIOD_NAME = pa_cal.NAME and
1386 tmp2.SYSTEM_LINKAGE_FUNCTION_R = res_typs.EXP_TYPE_CLASS and
1387 tmp2.EXPENDITURE_ORGANIZATION_ID = inv.ORGANIZATION_ID (+) and
1388 tmp2.INVENTORY_ITEM_ID = inv.INVENTORY_ITEM_ID (+) and
1389 tmp2.EXPENDITURE_CATEGORY = exp_cat.EXPENDITURE_CATEGORY (+)
1390 group by
1391 tmp2.RECORD_TYPE,
1392 tmp2.PERSON_ID,
1393 -- temporary fix for bug 3660160
1394 -- tmp2.EXPENDITURE_ORG_ID,
1395 tmp2.EXPENDITURE_ORGANIZATION_ID,
1396 nvl(res_typs.RESOURCE_CLASS_ID, -1),
1397 tmp2.JOB_ID,
1398 tmp2.VENDOR_ID,
1399 -- temporary fix for bug 3660160
1400 -- tmp2.WORK_TYPE_ID,
1401 nvl(exp_cat.EXPENDITURE_CATEGORY_ID, -1),
1402 decode(tmp2.EVENT_TYPE, 'PJI$NULL',
1403 tmp2.EXP_EVT_TYPE_ID, -1),
1404 decode(tmp2.EXPENDITURE_TYPE, 'PJI$NULL',
1405 tmp2.EXP_EVT_TYPE_ID, -1),
1406 -- temporary fix for bug 3813982
1407 -- tmp2.EXP_EVT_TYPE_ID,
1408 -- temporary fix for bug 3813982
1409 -- 'PJI$NULL',
1410 tmp2.EXPENDITURE_TYPE,
1411 tmp2.EVENT_TYPE,
1412 tmp2.EVENT_TYPE_CLASSIFICATION,
1413 -- temporary fix for bug 3813982
1414 -- 'PJI$NULL',
1415 tmp2.EXPENDITURE_CATEGORY,
1416 tmp2.REVENUE_CATEGORY,
1417 tmp2.NON_LABOR_RESOURCE_ID,
1418 tmp2.BOM_LABOR_RESOURCE_ID,
1419 tmp2.BOM_EQUIPMENT_RESOURCE_ID,
1420 nvl(inv.ITEM_CATEGORY_ID, -1),
1421 tmp2.INVENTORY_ITEM_ID,
1422 tmp2.PROJECT_ROLE_ID,
1423 tmp2.NAMED_ROLE,
1424 tmp2.PERSON_TYPE,
1425 -- temporary fix for bug 3813982
1426 -- tmp2.SYSTEM_LINKAGE_FUNCTION,
1427 tmp2.PROJECT_ID,
1428 tmp2.PROJECT_ORG_ID,
1429 tmp2.PROJECT_ORGANIZATION_ID,
1430 tmp2.PROJECT_TYPE_CLASS,
1431 tmp2.TASK_ID,
1432 tmp2.ASSIGNMENT_ID,
1433 decode(invert.INVERT_ID,
1434 'ENT', 'ENT',
1435 'GL', 'GL',
1436 'PA', 'PA'),
1437 decode(invert.INVERT_ID,
1438 'ENT', tmp2.RECVR_ENT_PERIOD_ID,
1439 'GL', gl_cal.CAL_PERIOD_ID,
1440 'PA', pa_cal.CAL_PERIOD_ID),
1441 tmp2.TXN_CURRENCY_CODE,
1442 tmp2.CBS_ELEMENT_ID /*Added for CBS Changes */;
1443
1444 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.AGGREGATE_FPR_PERIODS(p_worker_id);');
1445
1446 commit;
1447
1448 end AGGREGATE_FPR_PERIODS;
1449
1450
1451 -- -----------------------------------------------------
1452 -- procedure AGGREGATE_ACR_PERIODS
1453 --
1454 -- History
1455 -- 19-MAR-2004 SVERMETT Created
1456 --
1457 -- Internal PJP Summarization API.
1458 --
1459 -- -----------------------------------------------------
1460 procedure AGGREGATE_ACR_PERIODS (p_worker_id in number) is
1461
1462 l_process varchar2(30);
1463
1464 begin
1465
1466 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1467
1468 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.AGGREGATE_ACR_PERIODS(p_worker_id);')) then
1469 return;
1470 end if;
1471
1472 insert /*+ append parallel(tmp4_i) */ into PJI_FM_AGGR_ACT4 tmp4_i
1473 (
1474 WORKER_ID,
1475 PROJECT_ID,
1476 PROJECT_ORG_ID,
1477 PROJECT_ORGANIZATION_ID,
1478 TASK_ID,
1479 PERIOD_TYPE,
1480 PERIOD_ID,
1481 TXN_CURRENCY_CODE,
1482 TXN_REVENUE,
1483 TXN_FUNDING,
1484 TXN_INITIAL_FUNDING_AMOUNT,
1485 TXN_ADDITIONAL_FUNDING_AMOUNT,
1486 TXN_CANCELLED_FUNDING_AMOUNT,
1487 TXN_FUNDING_ADJUSTMENT_AMOUNT,
1488 TXN_REVENUE_WRITEOFF,
1489 TXN_AR_INVOICE_AMOUNT,
1490 TXN_AR_CASH_APPLIED_AMOUNT,
1491 TXN_AR_INVOICE_WRITEOFF_AMOUNT,
1492 TXN_AR_CREDIT_MEMO_AMOUNT,
1493 TXN_UNBILLED_RECEIVABLES,
1494 TXN_UNEARNED_REVENUE,
1495 TXN_AR_UNAPPR_INVOICE_AMOUNT,
1496 TXN_AR_APPR_INVOICE_AMOUNT,
1497 TXN_AR_AMOUNT_DUE,
1498 TXN_AR_AMOUNT_OVERDUE,
1499 PRJ_REVENUE,
1500 PRJ_FUNDING,
1501 PRJ_INITIAL_FUNDING_AMOUNT,
1502 PRJ_ADDITIONAL_FUNDING_AMOUNT,
1503 PRJ_CANCELLED_FUNDING_AMOUNT,
1504 PRJ_FUNDING_ADJUSTMENT_AMOUNT,
1505 PRJ_REVENUE_WRITEOFF,
1506 PRJ_AR_INVOICE_AMOUNT,
1507 PRJ_AR_CASH_APPLIED_AMOUNT,
1508 PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
1509 PRJ_AR_CREDIT_MEMO_AMOUNT,
1510 PRJ_UNBILLED_RECEIVABLES,
1511 PRJ_UNEARNED_REVENUE,
1512 PRJ_AR_UNAPPR_INVOICE_AMOUNT,
1513 PRJ_AR_APPR_INVOICE_AMOUNT,
1514 PRJ_AR_AMOUNT_DUE,
1515 PRJ_AR_AMOUNT_OVERDUE,
1516 POU_REVENUE,
1517 POU_FUNDING,
1518 POU_INITIAL_FUNDING_AMOUNT,
1519 POU_ADDITIONAL_FUNDING_AMOUNT,
1520 POU_CANCELLED_FUNDING_AMOUNT,
1521 POU_FUNDING_ADJUSTMENT_AMOUNT,
1522 POU_REVENUE_WRITEOFF,
1523 POU_AR_INVOICE_AMOUNT,
1524 POU_AR_CASH_APPLIED_AMOUNT,
1525 POU_AR_INVOICE_WRITEOFF_AMOUNT,
1526 POU_AR_CREDIT_MEMO_AMOUNT,
1527 POU_UNBILLED_RECEIVABLES,
1528 POU_UNEARNED_REVENUE,
1529 POU_AR_UNAPPR_INVOICE_AMOUNT,
1530 POU_AR_APPR_INVOICE_AMOUNT,
1531 POU_AR_AMOUNT_DUE,
1532 POU_AR_AMOUNT_OVERDUE,
1533 INITIAL_FUNDING_COUNT,
1534 ADDITIONAL_FUNDING_COUNT,
1535 CANCELLED_FUNDING_COUNT,
1536 FUNDING_ADJUSTMENT_COUNT,
1537 AR_INVOICE_COUNT,
1538 AR_CASH_APPLIED_COUNT,
1539 AR_INVOICE_WRITEOFF_COUNT,
1540 AR_CREDIT_MEMO_COUNT,
1541 AR_UNAPPR_INVOICE_COUNT,
1542 AR_APPR_INVOICE_COUNT,
1543 AR_COUNT_DUE,
1544 AR_COUNT_OVERDUE,
1545 G1_REVENUE,
1546 G1_FUNDING,
1547 G1_INITIAL_FUNDING_AMOUNT,
1548 G1_ADDITIONAL_FUNDING_AMOUNT,
1549 G1_CANCELLED_FUNDING_AMOUNT,
1550 G1_FUNDING_ADJUSTMENT_AMOUNT,
1551 G1_REVENUE_WRITEOFF,
1552 G1_AR_INVOICE_AMOUNT,
1553 G1_AR_CASH_APPLIED_AMOUNT,
1554 G1_AR_INVOICE_WRITEOFF_AMOUNT,
1555 G1_AR_CREDIT_MEMO_AMOUNT,
1556 G1_UNBILLED_RECEIVABLES,
1557 G1_UNEARNED_REVENUE,
1558 G1_AR_UNAPPR_INVOICE_AMOUNT,
1559 G1_AR_APPR_INVOICE_AMOUNT,
1560 G1_AR_AMOUNT_DUE,
1561 G1_AR_AMOUNT_OVERDUE,
1562 G2_REVENUE,
1563 G2_FUNDING,
1564 G2_INITIAL_FUNDING_AMOUNT,
1565 G2_ADDITIONAL_FUNDING_AMOUNT,
1566 G2_CANCELLED_FUNDING_AMOUNT,
1567 G2_FUNDING_ADJUSTMENT_AMOUNT,
1568 G2_REVENUE_WRITEOFF,
1569 G2_AR_INVOICE_AMOUNT,
1570 G2_AR_CASH_APPLIED_AMOUNT,
1571 G2_AR_INVOICE_WRITEOFF_AMOUNT,
1572 G2_AR_CREDIT_MEMO_AMOUNT,
1573 G2_UNBILLED_RECEIVABLES,
1574 G2_UNEARNED_REVENUE,
1575 G2_AR_UNAPPR_INVOICE_AMOUNT,
1576 G2_AR_APPR_INVOICE_AMOUNT,
1577 G2_AR_AMOUNT_DUE,
1578 G2_AR_AMOUNT_OVERDUE
1579 )
1580 select
1581 p_worker_id,
1582 tmp2.PROJECT_ID,
1583 tmp2.PROJECT_ORG_ID,
1584 tmp2.PROJECT_ORGANIZATION_ID,
1585 tmp2.TASK_ID,
1586 decode(invert.INVERT_ID,
1587 'ENT', 'ENT',
1588 'GL', 'GL',
1589 'PA', 'PA') PERIOD_TYPE,
1590 decode(invert.INVERT_ID,
1591 'ENT', tmp2.ENT_PERIOD_ID,
1592 'GL', gl_cal.CAL_PERIOD_ID,
1593 'PA', pa_cal.CAL_PERIOD_ID) PERIOD_ID,
1594 tmp2.TXN_CURRENCY_CODE,
1595 sum(tmp2.TXN_REVENUE) TXN_REVENUE,
1596 sum(tmp2.TXN_FUNDING) TXN_FUNDING,
1597 sum(tmp2.TXN_INITIAL_FUNDING_AMOUNT) TXN_INITIAL_FUNDING_AMOUNT,
1598 sum(tmp2.TXN_ADDITIONAL_FUNDING_AMOUNT) TXN_ADDITIONAL_FUNDING_AMOUNT,
1599 sum(tmp2.TXN_CANCELLED_FUNDING_AMOUNT) TXN_CANCELLED_FUNDING_AMOUNT,
1600 sum(tmp2.TXN_FUNDING_ADJUSTMENT_AMOUNT) TXN_FUNDING_ADJUSTMENT_AMOUNT,
1601 sum(tmp2.TXN_REVENUE_WRITEOFF) TXN_REVENUE_WRITEOFF,
1602 sum(tmp2.TXN_AR_INVOICE_AMOUNT) TXN_AR_INVOICE_AMOUNT,
1603 sum(tmp2.TXN_AR_CASH_APPLIED_AMOUNT) TXN_AR_CASH_APPLIED_AMOUNT,
1604 sum(tmp2.TXN_AR_INVOICE_WRITEOFF_AMOUNT) TXN_AR_INVOICE_WRITEOFF_AMOUNT,
1605 sum(tmp2.TXN_AR_CREDIT_MEMO_AMOUNT) TXN_AR_CREDIT_MEMO_AMOUNT,
1606 sum(tmp2.TXN_UNBILLED_RECEIVABLES) TXN_UNBILLED_RECEIVABLES,
1607 sum(tmp2.TXN_UNEARNED_REVENUE) TXN_UNEARNED_REVENUE,
1608 sum(tmp2.TXN_AR_UNAPPR_INVOICE_AMOUNT) TXN_AR_UNAPPR_INVOICE_AMOUNT,
1609 sum(tmp2.TXN_AR_APPR_INVOICE_AMOUNT) TXN_AR_APPR_INVOICE_AMOUNT,
1610 sum(tmp2.TXN_AR_AMOUNT_DUE) TXN_AR_AMOUNT_DUE,
1611 sum(tmp2.TXN_AR_AMOUNT_OVERDUE) TXN_AR_AMOUNT_OVERDUE,
1612 sum(tmp2.PRJ_REVENUE) PRJ_REVENUE,
1613 sum(tmp2.PRJ_FUNDING) PRJ_FUNDING,
1614 sum(tmp2.PRJ_INITIAL_FUNDING_AMOUNT) PRJ_INITIAL_FUNDING_AMOUNT,
1615 sum(tmp2.PRJ_ADDITIONAL_FUNDING_AMOUNT) PRJ_ADDITIONAL_FUNDING_AMOUNT,
1616 sum(tmp2.PRJ_CANCELLED_FUNDING_AMOUNT) PRJ_CANCELLED_FUNDING_AMOUNT,
1617 sum(tmp2.PRJ_FUNDING_ADJUSTMENT_AMOUNT) PRJ_FUNDING_ADJUSTMENT_AMOUNT,
1618 sum(tmp2.PRJ_REVENUE_WRITEOFF) PRJ_REVENUE_WRITEOFF,
1619 sum(tmp2.PRJ_AR_INVOICE_AMOUNT) PRJ_AR_INVOICE_AMOUNT,
1620 sum(tmp2.PRJ_AR_CASH_APPLIED_AMOUNT) PRJ_AR_CASH_APPLIED_AMOUNT,
1621 sum(tmp2.PRJ_AR_INVOICE_WRITEOFF_AMOUNT) PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
1622 sum(tmp2.PRJ_AR_CREDIT_MEMO_AMOUNT) PRJ_AR_CREDIT_MEMO_AMOUNT,
1623 sum(tmp2.PRJ_UNBILLED_RECEIVABLES) PRJ_UNBILLED_RECEIVABLES,
1624 sum(tmp2.PRJ_UNEARNED_REVENUE) PRJ_UNEARNED_REVENUE,
1625 sum(tmp2.PRJ_AR_UNAPPR_INVOICE_AMOUNT) PRJ_AR_UNAPPR_INVOICE_AMOUNT,
1626 sum(tmp2.PRJ_AR_APPR_INVOICE_AMOUNT) PRJ_AR_APPR_INVOICE_AMOUNT,
1627 sum(tmp2.PRJ_AR_AMOUNT_DUE) PRJ_AR_AMOUNT_DUE,
1628 sum(tmp2.PRJ_AR_AMOUNT_OVERDUE) PRJ_AR_AMOUNT_OVERDUE,
1629 sum(tmp2.POU_REVENUE) POU_REVENUE,
1630 sum(tmp2.POU_FUNDING) POU_FUNDING,
1631 sum(tmp2.POU_INITIAL_FUNDING_AMOUNT) POU_INITIAL_FUNDING_AMOUNT,
1632 sum(tmp2.POU_ADDITIONAL_FUNDING_AMOUNT) POU_ADDITIONAL_FUNDING_AMOUNT,
1633 sum(tmp2.POU_CANCELLED_FUNDING_AMOUNT) POU_CANCELLED_FUNDING_AMOUNT,
1634 sum(tmp2.POU_FUNDING_ADJUSTMENT_AMOUNT) POU_FUNDING_ADJUSTMENT_AMOUNT,
1635 sum(tmp2.POU_REVENUE_WRITEOFF) POU_REVENUE_WRITEOFF,
1636 sum(tmp2.POU_AR_INVOICE_AMOUNT) POU_AR_INVOICE_AMOUNT,
1637 sum(tmp2.POU_AR_CASH_APPLIED_AMOUNT) POU_AR_CASH_APPLIED_AMOUNT,
1638 sum(tmp2.POU_AR_INVOICE_WRITEOFF_AMOUNT) POU_AR_INVOICE_WRITEOFF_AMOUNT,
1639 sum(tmp2.POU_AR_CREDIT_MEMO_AMOUNT) POU_AR_CREDIT_MEMO_AMOUNT,
1640 sum(tmp2.POU_UNBILLED_RECEIVABLES) POU_UNBILLED_RECEIVABLES,
1641 sum(tmp2.POU_UNEARNED_REVENUE) POU_UNEARNED_REVENUE,
1642 sum(tmp2.POU_AR_UNAPPR_INVOICE_AMOUNT) POU_AR_UNAPPR_INVOICE_AMOUNT,
1643 sum(tmp2.POU_AR_APPR_INVOICE_AMOUNT) POU_AR_APPR_INVOICE_AMOUNT,
1644 sum(tmp2.POU_AR_AMOUNT_DUE) POU_AR_AMOUNT_DUE,
1645 sum(tmp2.POU_AR_AMOUNT_OVERDUE) POU_AR_AMOUNT_OVERDUE,
1646 sum(tmp2.INITIAL_FUNDING_COUNT) INITIAL_FUNDING_COUNT,
1647 sum(tmp2.ADDITIONAL_FUNDING_COUNT) ADDITIONAL_FUNDING_COUNT,
1648 sum(tmp2.CANCELLED_FUNDING_COUNT) CANCELLED_FUNDING_COUNT,
1649 sum(tmp2.FUNDING_ADJUSTMENT_COUNT) FUNDING_ADJUSTMENT_COUNT,
1650 sum(tmp2.AR_INVOICE_COUNT) AR_INVOICE_COUNT,
1651 sum(tmp2.AR_CASH_APPLIED_COUNT) AR_CASH_APPLIED_COUNT,
1652 sum(tmp2.AR_INVOICE_WRITEOFF_COUNT) AR_INVOICE_WRITEOFF_COUNT,
1653 sum(tmp2.AR_CREDIT_MEMO_COUNT) AR_CREDIT_MEMO_COUNT,
1654 sum(tmp2.AR_UNAPPR_INVOICE_COUNT) AR_UNAPPR_INVOICE_COUNT,
1655 sum(tmp2.AR_APPR_INVOICE_COUNT) AR_APPR_INVOICE_COUNT,
1656 sum(tmp2.AR_COUNT_DUE) AR_COUNT_DUE,
1657 sum(tmp2.AR_COUNT_OVERDUE) AR_COUNT_OVERDUE,
1658 sum(decode(invert.INVERT_ID,
1659 'ENT', tmp2.GG1_REVENUE,
1660 'GL', tmp2.GG1_REVENUE,
1661 'PA', tmp2.GP1_REVENUE)) G1_REVENUE,
1662 sum(decode(invert.INVERT_ID,
1663 'ENT', tmp2.GG1_FUNDING,
1664 'GL', tmp2.GG1_FUNDING,
1665 'PA', tmp2.GP1_FUNDING)) G1_FUNDING,
1666 sum(decode(invert.INVERT_ID,
1667 'ENT', tmp2.GG1_INITIAL_FUNDING_AMOUNT,
1668 'GL', tmp2.GG1_INITIAL_FUNDING_AMOUNT,
1669 'PA', tmp2.GP1_INITIAL_FUNDING_AMOUNT))
1670 G1_INITIAL_FUNDING_AMOUNT,
1671 sum(decode(invert.INVERT_ID,
1672 'ENT', tmp2.GG1_ADDITIONAL_FUNDING_AMOUNT,
1673 'GL', tmp2.GG1_ADDITIONAL_FUNDING_AMOUNT,
1674 'PA', tmp2.GP1_ADDITIONAL_FUNDING_AMOUNT))
1675 G1_ADDITIONAL_FUNDING_AMOUNT,
1676 sum(decode(invert.INVERT_ID,
1677 'ENT', tmp2.GG1_CANCELLED_FUNDING_AMOUNT,
1678 'GL', tmp2.GG1_CANCELLED_FUNDING_AMOUNT,
1679 'PA', tmp2.GP1_CANCELLED_FUNDING_AMOUNT))
1680 G1_CANCELLED_FUNDING_AMOUNT,
1681 sum(decode(invert.INVERT_ID,
1682 'ENT', tmp2.GG1_FUNDING_ADJUSTMENT_AMOUNT,
1683 'GL', tmp2.GG1_FUNDING_ADJUSTMENT_AMOUNT,
1684 'PA', tmp2.GP1_FUNDING_ADJUSTMENT_AMOUNT))
1685 G1_FUNDING_ADJUSTMENT_AMOUNT,
1686 sum(decode(invert.INVERT_ID,
1687 'ENT', tmp2.GG1_REVENUE_WRITEOFF,
1688 'GL', tmp2.GG1_REVENUE_WRITEOFF,
1689 'PA', tmp2.GP1_REVENUE_WRITEOFF))
1690 G1_REVENUE_WRITEOFF,
1691 sum(decode(invert.INVERT_ID,
1692 'ENT', tmp2.GG1_AR_INVOICE_AMOUNT,
1693 'GL', tmp2.GG1_AR_INVOICE_AMOUNT,
1694 'PA', tmp2.GP1_AR_INVOICE_AMOUNT))
1695 G1_AR_INVOICE_AMOUNT,
1696 sum(decode(invert.INVERT_ID,
1697 'ENT', tmp2.GG1_AR_CASH_APPLIED_AMOUNT,
1698 'GL', tmp2.GG1_AR_CASH_APPLIED_AMOUNT,
1699 'PA', tmp2.GP1_AR_CASH_APPLIED_AMOUNT))
1700 G1_AR_CASH_APPLIED_AMOUNT,
1701 sum(decode(invert.INVERT_ID,
1702 'ENT', tmp2.GG1_AR_INVOICE_WRITEOFF_AMOUNT,
1703 'GL', tmp2.GG1_AR_INVOICE_WRITEOFF_AMOUNT,
1704 'PA', tmp2.GP1_AR_INVOICE_WRITEOFF_AMOUNT))
1705 G1_AR_INVOICE_WRITEOFF_AMOUNT,
1706 sum(decode(invert.INVERT_ID,
1707 'ENT', tmp2.GG1_AR_CREDIT_MEMO_AMOUNT,
1708 'GL', tmp2.GG1_AR_CREDIT_MEMO_AMOUNT,
1709 'PA', tmp2.GP1_AR_CREDIT_MEMO_AMOUNT))
1710 G1_AR_CREDIT_MEMO_AMOUNT,
1711 sum(decode(invert.INVERT_ID,
1712 'ENT', tmp2.GG1_UNBILLED_RECEIVABLES,
1713 'GL', tmp2.GG1_UNBILLED_RECEIVABLES,
1714 'PA', tmp2.GP1_UNBILLED_RECEIVABLES))
1715 G1_UNBILLED_RECEIVABLES,
1716 sum(decode(invert.INVERT_ID,
1717 'ENT', tmp2.GG1_UNEARNED_REVENUE,
1718 'GL', tmp2.GG1_UNEARNED_REVENUE,
1719 'PA', tmp2.GP1_UNEARNED_REVENUE))
1720 G1_UNEARNED_REVENUE,
1721 sum(decode(invert.INVERT_ID,
1722 'ENT', tmp2.GG1_AR_UNAPPR_INVOICE_AMOUNT,
1723 'GL', tmp2.GG1_AR_UNAPPR_INVOICE_AMOUNT,
1724 'PA', tmp2.GP1_AR_UNAPPR_INVOICE_AMOUNT))
1725 G1_AR_UNAPPR_INVOICE_AMOUNT,
1726 sum(decode(invert.INVERT_ID,
1727 'ENT', tmp2.GG1_AR_APPR_INVOICE_AMOUNT,
1728 'GL', tmp2.GG1_AR_APPR_INVOICE_AMOUNT,
1729 'PA', tmp2.GP1_AR_APPR_INVOICE_AMOUNT))
1730 G1_AR_APPR_INVOICE_AMOUNT,
1731 sum(decode(invert.INVERT_ID,
1732 'ENT', tmp2.GG1_AR_AMOUNT_DUE,
1733 'GL', tmp2.GG1_AR_AMOUNT_DUE,
1734 'PA', tmp2.GP1_AR_AMOUNT_DUE)) G1_AR_AMOUNT_DUE,
1735 sum(decode(invert.INVERT_ID,
1736 'ENT', tmp2.GG1_AR_AMOUNT_OVERDUE,
1737 'GL', tmp2.GG1_AR_AMOUNT_OVERDUE,
1738 'PA', tmp2.GP1_AR_AMOUNT_OVERDUE))
1739 G1_AR_AMOUNT_OVERDUE,
1740 sum(decode(invert.INVERT_ID,
1741 'ENT', tmp2.GG2_REVENUE,
1742 'GL', tmp2.GG2_REVENUE,
1743 'PA', tmp2.GP2_REVENUE)) G2_REVENUE,
1744 sum(decode(invert.INVERT_ID,
1745 'ENT', tmp2.GG2_FUNDING,
1746 'GL', tmp2.GG2_FUNDING,
1747 'PA', tmp2.GP2_FUNDING)) G2_FUNDING,
1748 sum(decode(invert.INVERT_ID,
1749 'ENT', tmp2.GG2_INITIAL_FUNDING_AMOUNT,
1750 'GL', tmp2.GG2_INITIAL_FUNDING_AMOUNT,
1751 'PA', tmp2.GP2_INITIAL_FUNDING_AMOUNT))
1752 G2_INITIAL_FUNDING_AMOUNT,
1753 sum(decode(invert.INVERT_ID,
1754 'ENT', tmp2.GG2_ADDITIONAL_FUNDING_AMOUNT,
1755 'GL', tmp2.GG2_ADDITIONAL_FUNDING_AMOUNT,
1756 'PA', tmp2.GP2_ADDITIONAL_FUNDING_AMOUNT))
1757 G2_ADDITIONAL_FUNDING_AMOUNT,
1758 sum(decode(invert.INVERT_ID,
1759 'ENT', tmp2.GG2_CANCELLED_FUNDING_AMOUNT,
1760 'GL', tmp2.GG2_CANCELLED_FUNDING_AMOUNT,
1761 'PA', tmp2.GP2_CANCELLED_FUNDING_AMOUNT))
1762 G2_CANCELLED_FUNDING_AMOUNT,
1763 sum(decode(invert.INVERT_ID,
1764 'ENT', tmp2.GG2_FUNDING_ADJUSTMENT_AMOUNT,
1765 'GL', tmp2.GG2_FUNDING_ADJUSTMENT_AMOUNT,
1766 'PA', tmp2.GP2_FUNDING_ADJUSTMENT_AMOUNT))
1767 G2_FUNDING_ADJUSTMENT_AMOUNT,
1768 sum(decode(invert.INVERT_ID,
1769 'ENT', tmp2.GG2_REVENUE_WRITEOFF,
1770 'GL', tmp2.GG2_REVENUE_WRITEOFF,
1771 'PA', tmp2.GP2_REVENUE_WRITEOFF))
1772 G2_REVENUE_WRITEOFF,
1773 sum(decode(invert.INVERT_ID,
1774 'ENT', tmp2.GG2_AR_INVOICE_AMOUNT,
1775 'GL', tmp2.GG2_AR_INVOICE_AMOUNT,
1776 'PA', tmp2.GP2_AR_INVOICE_AMOUNT))
1777 G2_AR_INVOICE_AMOUNT,
1778 sum(decode(invert.INVERT_ID,
1779 'ENT', tmp2.GG2_AR_CASH_APPLIED_AMOUNT,
1780 'GL', tmp2.GG2_AR_CASH_APPLIED_AMOUNT,
1781 'PA', tmp2.GP2_AR_CASH_APPLIED_AMOUNT))
1782 G2_AR_CASH_APPLIED_AMOUNT,
1783 sum(decode(invert.INVERT_ID,
1784 'ENT', tmp2.GG2_AR_INVOICE_WRITEOFF_AMOUNT,
1785 'GL', tmp2.GG2_AR_INVOICE_WRITEOFF_AMOUNT,
1786 'PA', tmp2.GP2_AR_INVOICE_WRITEOFF_AMOUNT))
1787 G2_AR_INVOICE_WRITEOFF_AMOUNT,
1788 sum(decode(invert.INVERT_ID,
1789 'ENT', tmp2.GG2_AR_CREDIT_MEMO_AMOUNT,
1790 'GL', tmp2.GG2_AR_CREDIT_MEMO_AMOUNT,
1791 'PA', tmp2.GP2_AR_CREDIT_MEMO_AMOUNT))
1792 G2_AR_CREDIT_MEMO_AMOUNT,
1793 sum(decode(invert.INVERT_ID,
1794 'ENT', tmp2.GG2_UNBILLED_RECEIVABLES,
1795 'GL', tmp2.GG2_UNBILLED_RECEIVABLES,
1796 'PA', tmp2.GP2_UNBILLED_RECEIVABLES))
1797 G2_UNBILLED_RECEIVABLES,
1798 sum(decode(invert.INVERT_ID,
1799 'ENT', tmp2.GG2_UNEARNED_REVENUE,
1800 'GL', tmp2.GG2_UNEARNED_REVENUE,
1801 'PA', tmp2.GP2_UNEARNED_REVENUE))
1802 G2_UNEARNED_REVENUE,
1803 sum(decode(invert.INVERT_ID,
1804 'ENT', tmp2.GG2_AR_UNAPPR_INVOICE_AMOUNT,
1805 'GL', tmp2.GG2_AR_UNAPPR_INVOICE_AMOUNT,
1806 'PA', tmp2.GP2_AR_UNAPPR_INVOICE_AMOUNT))
1807 G2_AR_UNAPPR_INVOICE_AMOUNT,
1808 sum(decode(invert.INVERT_ID,
1809 'ENT', tmp2.GG2_AR_APPR_INVOICE_AMOUNT,
1810 'GL', tmp2.GG2_AR_APPR_INVOICE_AMOUNT,
1811 'PA', tmp2.GP2_AR_APPR_INVOICE_AMOUNT))
1812 G2_AR_APPR_INVOICE_AMOUNT,
1813 sum(decode(invert.INVERT_ID,
1814 'ENT', tmp2.GG2_AR_AMOUNT_DUE,
1815 'GL', tmp2.GG2_AR_AMOUNT_DUE,
1816 'PA', tmp2.GP2_AR_AMOUNT_DUE)) G2_AR_AMOUNT_DUE,
1817 sum(decode(invert.INVERT_ID,
1818 'ENT', tmp2.GG2_AR_AMOUNT_OVERDUE,
1819 'GL', tmp2.GG2_AR_AMOUNT_OVERDUE,
1820 'PA', tmp2.GP2_AR_AMOUNT_OVERDUE))
1821 G2_AR_AMOUNT_OVERDUE
1822 from
1823 (
1824 select /*+ ordered full(tmp2) parallel(tmp2) */
1825 tmp2.WORKER_ID,
1826 tmp2.PROJECT_ID,
1827 tmp2.PROJECT_ORG_ID,
1828 tmp2.PROJECT_ORGANIZATION_ID,
1829 tmp2.TASK_ID,
1830 ent.ENT_PERIOD_ID,
1831 tmp2.GL_PERIOD_NAME,
1832 tmp2.PA_PERIOD_NAME,
1833 tmp2.GL_CALENDAR_ID,
1834 tmp2.PA_CALENDAR_ID,
1835 tmp2.TXN_CURRENCY_CODE,
1836 sum(tmp2.TXN_REVENUE) TXN_REVENUE,
1837 sum(tmp2.TXN_FUNDING) TXN_FUNDING,
1838 sum(tmp2.TXN_INITIAL_FUNDING_AMOUNT) TXN_INITIAL_FUNDING_AMOUNT,
1839 sum(tmp2.TXN_ADDITIONAL_FUNDING_AMOUNT) TXN_ADDITIONAL_FUNDING_AMOUNT,
1840 sum(tmp2.TXN_CANCELLED_FUNDING_AMOUNT) TXN_CANCELLED_FUNDING_AMOUNT,
1841 sum(tmp2.TXN_FUNDING_ADJUSTMENT_AMOUNT) TXN_FUNDING_ADJUSTMENT_AMOUNT,
1842 sum(tmp2.TXN_REVENUE_WRITEOFF) TXN_REVENUE_WRITEOFF,
1843 sum(tmp2.TXN_AR_INVOICE_AMOUNT) TXN_AR_INVOICE_AMOUNT,
1844 sum(tmp2.TXN_AR_CASH_APPLIED_AMOUNT) TXN_AR_CASH_APPLIED_AMOUNT,
1845 sum(tmp2.TXN_AR_INVOICE_WRITEOFF_AMOUNT)TXN_AR_INVOICE_WRITEOFF_AMOUNT,
1846 sum(tmp2.TXN_AR_CREDIT_MEMO_AMOUNT) TXN_AR_CREDIT_MEMO_AMOUNT,
1847 sum(tmp2.TXN_UNBILLED_RECEIVABLES) TXN_UNBILLED_RECEIVABLES,
1848 sum(tmp2.TXN_UNEARNED_REVENUE) TXN_UNEARNED_REVENUE,
1849 sum(tmp2.TXN_AR_UNAPPR_INVOICE_AMOUNT) TXN_AR_UNAPPR_INVOICE_AMOUNT,
1850 sum(tmp2.TXN_AR_APPR_INVOICE_AMOUNT) TXN_AR_APPR_INVOICE_AMOUNT,
1851 sum(tmp2.TXN_AR_AMOUNT_DUE) TXN_AR_AMOUNT_DUE,
1852 sum(tmp2.TXN_AR_AMOUNT_OVERDUE) TXN_AR_AMOUNT_OVERDUE,
1853 sum(tmp2.PRJ_REVENUE) PRJ_REVENUE,
1854 sum(tmp2.PRJ_FUNDING) PRJ_FUNDING,
1855 sum(tmp2.PRJ_INITIAL_FUNDING_AMOUNT) PRJ_INITIAL_FUNDING_AMOUNT,
1856 sum(tmp2.PRJ_ADDITIONAL_FUNDING_AMOUNT) PRJ_ADDITIONAL_FUNDING_AMOUNT,
1857 sum(tmp2.PRJ_CANCELLED_FUNDING_AMOUNT) PRJ_CANCELLED_FUNDING_AMOUNT,
1858 sum(tmp2.PRJ_FUNDING_ADJUSTMENT_AMOUNT) PRJ_FUNDING_ADJUSTMENT_AMOUNT,
1859 sum(tmp2.PRJ_REVENUE_WRITEOFF) PRJ_REVENUE_WRITEOFF,
1860 sum(tmp2.PRJ_AR_INVOICE_AMOUNT) PRJ_AR_INVOICE_AMOUNT,
1861 sum(tmp2.PRJ_AR_CASH_APPLIED_AMOUNT) PRJ_AR_CASH_APPLIED_AMOUNT,
1862 sum(tmp2.PRJ_AR_INVOICE_WRITEOFF_AMOUNT)PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
1863 sum(tmp2.PRJ_AR_CREDIT_MEMO_AMOUNT) PRJ_AR_CREDIT_MEMO_AMOUNT,
1864 sum(tmp2.PRJ_UNBILLED_RECEIVABLES) PRJ_UNBILLED_RECEIVABLES,
1865 sum(tmp2.PRJ_UNEARNED_REVENUE) PRJ_UNEARNED_REVENUE,
1866 sum(tmp2.PRJ_AR_UNAPPR_INVOICE_AMOUNT) PRJ_AR_UNAPPR_INVOICE_AMOUNT,
1867 sum(tmp2.PRJ_AR_APPR_INVOICE_AMOUNT) PRJ_AR_APPR_INVOICE_AMOUNT,
1868 sum(tmp2.PRJ_AR_AMOUNT_DUE) PRJ_AR_AMOUNT_DUE,
1869 sum(tmp2.PRJ_AR_AMOUNT_OVERDUE) PRJ_AR_AMOUNT_OVERDUE,
1870 sum(tmp2.POU_REVENUE) POU_REVENUE,
1871 sum(tmp2.POU_FUNDING) POU_FUNDING,
1872 sum(tmp2.POU_INITIAL_FUNDING_AMOUNT) POU_INITIAL_FUNDING_AMOUNT,
1873 sum(tmp2.POU_ADDITIONAL_FUNDING_AMOUNT) POU_ADDITIONAL_FUNDING_AMOUNT,
1874 sum(tmp2.POU_CANCELLED_FUNDING_AMOUNT) POU_CANCELLED_FUNDING_AMOUNT,
1875 sum(tmp2.POU_FUNDING_ADJUSTMENT_AMOUNT) POU_FUNDING_ADJUSTMENT_AMOUNT,
1876 sum(tmp2.POU_REVENUE_WRITEOFF) POU_REVENUE_WRITEOFF,
1877 sum(tmp2.POU_AR_INVOICE_AMOUNT) POU_AR_INVOICE_AMOUNT,
1878 sum(tmp2.POU_AR_CASH_APPLIED_AMOUNT) POU_AR_CASH_APPLIED_AMOUNT,
1879 sum(tmp2.POU_AR_INVOICE_WRITEOFF_AMOUNT)POU_AR_INVOICE_WRITEOFF_AMOUNT,
1880 sum(tmp2.POU_AR_CREDIT_MEMO_AMOUNT) POU_AR_CREDIT_MEMO_AMOUNT,
1881 sum(tmp2.POU_UNBILLED_RECEIVABLES) POU_UNBILLED_RECEIVABLES,
1882 sum(tmp2.POU_UNEARNED_REVENUE) POU_UNEARNED_REVENUE,
1883 sum(tmp2.POU_AR_UNAPPR_INVOICE_AMOUNT) POU_AR_UNAPPR_INVOICE_AMOUNT,
1884 sum(tmp2.POU_AR_APPR_INVOICE_AMOUNT) POU_AR_APPR_INVOICE_AMOUNT,
1885 sum(tmp2.POU_AR_AMOUNT_DUE) POU_AR_AMOUNT_DUE,
1886 sum(tmp2.POU_AR_AMOUNT_OVERDUE) POU_AR_AMOUNT_OVERDUE,
1887 sum(tmp2.INITIAL_FUNDING_COUNT) INITIAL_FUNDING_COUNT,
1888 sum(tmp2.ADDITIONAL_FUNDING_COUNT) ADDITIONAL_FUNDING_COUNT,
1889 sum(tmp2.CANCELLED_FUNDING_COUNT) CANCELLED_FUNDING_COUNT,
1890 sum(tmp2.FUNDING_ADJUSTMENT_COUNT) FUNDING_ADJUSTMENT_COUNT,
1891 sum(tmp2.AR_INVOICE_COUNT) AR_INVOICE_COUNT,
1892 sum(tmp2.AR_CASH_APPLIED_COUNT) AR_CASH_APPLIED_COUNT,
1893 sum(tmp2.AR_INVOICE_WRITEOFF_COUNT) AR_INVOICE_WRITEOFF_COUNT,
1894 sum(tmp2.AR_CREDIT_MEMO_COUNT) AR_CREDIT_MEMO_COUNT,
1895 sum(tmp2.AR_UNAPPR_INVOICE_COUNT) AR_UNAPPR_INVOICE_COUNT,
1896 sum(tmp2.AR_APPR_INVOICE_COUNT) AR_APPR_INVOICE_COUNT,
1897 sum(tmp2.AR_COUNT_DUE) AR_COUNT_DUE,
1898 sum(tmp2.AR_COUNT_OVERDUE) AR_COUNT_OVERDUE,
1899 sum(tmp2.GG_REVENUE) GG1_REVENUE,
1900 sum(tmp2.GG_FUNDING) GG1_FUNDING,
1901 sum(tmp2.GG_INITIAL_FUNDING_AMOUNT) GG1_INITIAL_FUNDING_AMOUNT,
1902 sum(tmp2.GG_ADDITIONAL_FUNDING_AMOUNT) GG1_ADDITIONAL_FUNDING_AMOUNT,
1903 sum(tmp2.GG_CANCELLED_FUNDING_AMOUNT) GG1_CANCELLED_FUNDING_AMOUNT,
1904 sum(tmp2.GG_FUNDING_ADJUSTMENT_AMOUNT) GG1_FUNDING_ADJUSTMENT_AMOUNT,
1905 sum(tmp2.GG_REVENUE_WRITEOFF) GG1_REVENUE_WRITEOFF,
1906 sum(tmp2.GG_AR_INVOICE_AMOUNT) GG1_AR_INVOICE_AMOUNT,
1907 sum(tmp2.GG_AR_CASH_APPLIED_AMOUNT) GG1_AR_CASH_APPLIED_AMOUNT,
1908 sum(tmp2.GG_AR_INVOICE_WRITEOFF_AMOUNT) GG1_AR_INVOICE_WRITEOFF_AMOUNT,
1909 sum(tmp2.GG_AR_CREDIT_MEMO_AMOUNT) GG1_AR_CREDIT_MEMO_AMOUNT,
1910 sum(tmp2.GG_UNBILLED_RECEIVABLES) GG1_UNBILLED_RECEIVABLES,
1911 sum(tmp2.GG_UNEARNED_REVENUE) GG1_UNEARNED_REVENUE,
1912 sum(tmp2.GG_AR_UNAPPR_INVOICE_AMOUNT) GG1_AR_UNAPPR_INVOICE_AMOUNT,
1913 sum(tmp2.GG_AR_APPR_INVOICE_AMOUNT) GG1_AR_APPR_INVOICE_AMOUNT,
1914 sum(tmp2.GG_AR_AMOUNT_DUE) GG1_AR_AMOUNT_DUE,
1915 sum(tmp2.GG_AR_AMOUNT_OVERDUE) GG1_AR_AMOUNT_OVERDUE,
1916 sum(tmp2.GP_REVENUE) GP1_REVENUE,
1917 sum(tmp2.GP_FUNDING) GP1_FUNDING,
1918 sum(tmp2.GP_INITIAL_FUNDING_AMOUNT) GP1_INITIAL_FUNDING_AMOUNT,
1919 sum(tmp2.GP_ADDITIONAL_FUNDING_AMOUNT) GP1_ADDITIONAL_FUNDING_AMOUNT,
1920 sum(tmp2.GP_CANCELLED_FUNDING_AMOUNT) GP1_CANCELLED_FUNDING_AMOUNT,
1921 sum(tmp2.GP_FUNDING_ADJUSTMENT_AMOUNT) GP1_FUNDING_ADJUSTMENT_AMOUNT,
1922 sum(tmp2.GP_REVENUE_WRITEOFF) GP1_REVENUE_WRITEOFF,
1923 sum(tmp2.GP_AR_INVOICE_AMOUNT) GP1_AR_INVOICE_AMOUNT,
1924 sum(tmp2.GP_AR_CASH_APPLIED_AMOUNT) GP1_AR_CASH_APPLIED_AMOUNT,
1925 sum(tmp2.GP_AR_INVOICE_WRITEOFF_AMOUNT) GP1_AR_INVOICE_WRITEOFF_AMOUNT,
1926 sum(tmp2.GP_AR_CREDIT_MEMO_AMOUNT) GP1_AR_CREDIT_MEMO_AMOUNT,
1927 sum(tmp2.GP_UNBILLED_RECEIVABLES) GP1_UNBILLED_RECEIVABLES,
1928 sum(tmp2.GP_UNEARNED_REVENUE) GP1_UNEARNED_REVENUE,
1929 sum(tmp2.GP_AR_UNAPPR_INVOICE_AMOUNT) GP1_AR_UNAPPR_INVOICE_AMOUNT,
1930 sum(tmp2.GP_AR_APPR_INVOICE_AMOUNT) GP1_AR_APPR_INVOICE_AMOUNT,
1931 sum(tmp2.GP_AR_AMOUNT_DUE) GP1_AR_AMOUNT_DUE,
1932 sum(tmp2.GP_AR_AMOUNT_OVERDUE) GP1_AR_AMOUNT_OVERDUE,
1933 sum(tmp2.GG2_REVENUE) GG2_REVENUE,
1934 sum(tmp2.GG2_FUNDING) GG2_FUNDING,
1935 sum(tmp2.GG2_INITIAL_FUNDING_AMOUNT) GG2_INITIAL_FUNDING_AMOUNT,
1936 sum(tmp2.GG2_ADDITIONAL_FUNDING_AMOUNT) GG2_ADDITIONAL_FUNDING_AMOUNT,
1937 sum(tmp2.GG2_CANCELLED_FUNDING_AMOUNT) GG2_CANCELLED_FUNDING_AMOUNT,
1938 sum(tmp2.GG2_FUNDING_ADJUSTMENT_AMOUNT) GG2_FUNDING_ADJUSTMENT_AMOUNT,
1939 sum(tmp2.GG2_REVENUE_WRITEOFF) GG2_REVENUE_WRITEOFF,
1940 sum(tmp2.GG2_AR_INVOICE_AMOUNT) GG2_AR_INVOICE_AMOUNT,
1941 sum(tmp2.GG2_AR_CASH_APPLIED_AMOUNT) GG2_AR_CASH_APPLIED_AMOUNT,
1942 sum(tmp2.GG2_AR_INVOICE_WRITEOFF_AMOUNT)GG2_AR_INVOICE_WRITEOFF_AMOUNT,
1943 sum(tmp2.GG2_AR_CREDIT_MEMO_AMOUNT) GG2_AR_CREDIT_MEMO_AMOUNT,
1944 sum(tmp2.GG2_UNBILLED_RECEIVABLES) GG2_UNBILLED_RECEIVABLES,
1945 sum(tmp2.GG2_UNEARNED_REVENUE) GG2_UNEARNED_REVENUE,
1946 sum(tmp2.GG2_AR_UNAPPR_INVOICE_AMOUNT) GG2_AR_UNAPPR_INVOICE_AMOUNT,
1947 sum(tmp2.GG2_AR_APPR_INVOICE_AMOUNT) GG2_AR_APPR_INVOICE_AMOUNT,
1948 sum(tmp2.GG2_AR_AMOUNT_DUE) GG2_AR_AMOUNT_DUE,
1949 sum(tmp2.GG2_AR_AMOUNT_OVERDUE) GG2_AR_AMOUNT_OVERDUE,
1950 sum(tmp2.GP2_REVENUE) GP2_REVENUE,
1951 sum(tmp2.GP2_FUNDING) GP2_FUNDING,
1952 sum(tmp2.GP2_INITIAL_FUNDING_AMOUNT) GP2_INITIAL_FUNDING_AMOUNT,
1953 sum(tmp2.GP2_ADDITIONAL_FUNDING_AMOUNT) GP2_ADDITIONAL_FUNDING_AMOUNT,
1954 sum(tmp2.GP2_CANCELLED_FUNDING_AMOUNT) GP2_CANCELLED_FUNDING_AMOUNT,
1955 sum(tmp2.GP2_FUNDING_ADJUSTMENT_AMOUNT) GP2_FUNDING_ADJUSTMENT_AMOUNT,
1956 sum(tmp2.GP2_REVENUE_WRITEOFF) GP2_REVENUE_WRITEOFF,
1957 sum(tmp2.GP2_AR_INVOICE_AMOUNT) GP2_AR_INVOICE_AMOUNT,
1958 sum(tmp2.GP2_AR_CASH_APPLIED_AMOUNT) GP2_AR_CASH_APPLIED_AMOUNT,
1959 sum(tmp2.GP2_AR_INVOICE_WRITEOFF_AMOUNT)GP2_AR_INVOICE_WRITEOFF_AMOUNT,
1960 sum(tmp2.GP2_AR_CREDIT_MEMO_AMOUNT) GP2_AR_CREDIT_MEMO_AMOUNT,
1961 sum(tmp2.GP2_UNBILLED_RECEIVABLES) GP2_UNBILLED_RECEIVABLES,
1962 sum(tmp2.GP2_UNEARNED_REVENUE) GP2_UNEARNED_REVENUE,
1963 sum(tmp2.GP2_AR_UNAPPR_INVOICE_AMOUNT) GP2_AR_UNAPPR_INVOICE_AMOUNT,
1964 sum(tmp2.GP2_AR_APPR_INVOICE_AMOUNT) GP2_AR_APPR_INVOICE_AMOUNT,
1965 sum(tmp2.GP2_AR_AMOUNT_DUE) GP2_AR_AMOUNT_DUE,
1966 sum(tmp2.GP2_AR_AMOUNT_OVERDUE) GP2_AR_AMOUNT_OVERDUE
1967 from
1968 (
1969 select
1970 tmp2.WORKER_ID,
1971 tmp2.PROJECT_ID,
1972 tmp2.PROJECT_ORG_ID,
1973 tmp2.PROJECT_ORGANIZATION_ID,
1974 tmp2.TASK_ID,
1975 tmp2.GL_TIME_ID,
1976 tmp2.GL_PERIOD_NAME,
1977 tmp2.PA_PERIOD_NAME,
1978 tmp2.GL_CALENDAR_ID,
1979 tmp2.PA_CALENDAR_ID,
1980 tmp2.TXN_CURRENCY_CODE,
1981 tmp2.TXN_REVENUE,
1982 tmp2.TXN_FUNDING,
1983 tmp2.TXN_INITIAL_FUNDING_AMOUNT,
1984 tmp2.TXN_ADDITIONAL_FUNDING_AMOUNT,
1985 tmp2.TXN_CANCELLED_FUNDING_AMOUNT,
1986 tmp2.TXN_FUNDING_ADJUSTMENT_AMOUNT,
1987 tmp2.TXN_REVENUE_WRITEOFF,
1988 tmp2.TXN_AR_INVOICE_AMOUNT,
1989 tmp2.TXN_AR_CASH_APPLIED_AMOUNT,
1990 tmp2.TXN_AR_INVOICE_WRITEOFF_AMOUNT,
1991 tmp2.TXN_AR_CREDIT_MEMO_AMOUNT,
1992 tmp2.TXN_UNBILLED_RECEIVABLES,
1993 tmp2.TXN_UNEARNED_REVENUE,
1994 tmp2.TXN_AR_UNAPPR_INVOICE_AMOUNT,
1995 tmp2.TXN_AR_APPR_INVOICE_AMOUNT,
1996 tmp2.TXN_AR_AMOUNT_DUE,
1997 tmp2.TXN_AR_AMOUNT_OVERDUE,
1998 tmp2.PRJ_REVENUE,
1999 tmp2.PRJ_FUNDING,
2000 tmp2.PRJ_INITIAL_FUNDING_AMOUNT,
2001 tmp2.PRJ_ADDITIONAL_FUNDING_AMOUNT,
2002 tmp2.PRJ_CANCELLED_FUNDING_AMOUNT,
2003 tmp2.PRJ_FUNDING_ADJUSTMENT_AMOUNT,
2004 tmp2.PRJ_REVENUE_WRITEOFF,
2005 tmp2.PRJ_AR_INVOICE_AMOUNT,
2006 tmp2.PRJ_AR_CASH_APPLIED_AMOUNT,
2007 tmp2.PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
2008 tmp2.PRJ_AR_CREDIT_MEMO_AMOUNT,
2009 tmp2.PRJ_UNBILLED_RECEIVABLES,
2010 tmp2.PRJ_UNEARNED_REVENUE,
2011 tmp2.PRJ_AR_UNAPPR_INVOICE_AMOUNT,
2012 tmp2.PRJ_AR_APPR_INVOICE_AMOUNT,
2013 tmp2.PRJ_AR_AMOUNT_DUE,
2014 tmp2.PRJ_AR_AMOUNT_OVERDUE,
2015 tmp2.POU_REVENUE,
2016 tmp2.POU_FUNDING,
2017 tmp2.POU_INITIAL_FUNDING_AMOUNT,
2018 tmp2.POU_ADDITIONAL_FUNDING_AMOUNT,
2019 tmp2.POU_CANCELLED_FUNDING_AMOUNT,
2020 tmp2.POU_FUNDING_ADJUSTMENT_AMOUNT,
2021 tmp2.POU_REVENUE_WRITEOFF,
2022 tmp2.POU_AR_INVOICE_AMOUNT,
2023 tmp2.POU_AR_CASH_APPLIED_AMOUNT,
2024 tmp2.POU_AR_INVOICE_WRITEOFF_AMOUNT,
2025 tmp2.POU_AR_CREDIT_MEMO_AMOUNT,
2026 tmp2.POU_UNBILLED_RECEIVABLES,
2027 tmp2.POU_UNEARNED_REVENUE,
2028 tmp2.POU_AR_UNAPPR_INVOICE_AMOUNT,
2029 tmp2.POU_AR_APPR_INVOICE_AMOUNT,
2030 tmp2.POU_AR_AMOUNT_DUE,
2031 tmp2.POU_AR_AMOUNT_OVERDUE,
2032 tmp2.INITIAL_FUNDING_COUNT,
2033 tmp2.ADDITIONAL_FUNDING_COUNT,
2034 tmp2.CANCELLED_FUNDING_COUNT,
2035 tmp2.FUNDING_ADJUSTMENT_COUNT,
2036 tmp2.AR_INVOICE_COUNT,
2037 tmp2.AR_CASH_APPLIED_COUNT,
2038 tmp2.AR_INVOICE_WRITEOFF_COUNT,
2039 tmp2.AR_CREDIT_MEMO_COUNT,
2040 tmp2.AR_UNAPPR_INVOICE_COUNT,
2041 tmp2.AR_APPR_INVOICE_COUNT,
2042 tmp2.AR_COUNT_DUE,
2043 tmp2.AR_COUNT_OVERDUE,
2044 tmp2.GG_REVENUE,
2045 tmp2.GG_FUNDING,
2046 tmp2.GG_INITIAL_FUNDING_AMOUNT,
2047 tmp2.GG_ADDITIONAL_FUNDING_AMOUNT,
2048 tmp2.GG_CANCELLED_FUNDING_AMOUNT,
2049 tmp2.GG_FUNDING_ADJUSTMENT_AMOUNT,
2050 tmp2.GG_REVENUE_WRITEOFF,
2051 tmp2.GG_AR_INVOICE_AMOUNT,
2052 tmp2.GG_AR_CASH_APPLIED_AMOUNT,
2053 tmp2.GG_AR_INVOICE_WRITEOFF_AMOUNT,
2054 tmp2.GG_AR_CREDIT_MEMO_AMOUNT,
2055 tmp2.GG_UNBILLED_RECEIVABLES,
2056 tmp2.GG_UNEARNED_REVENUE,
2057 tmp2.GG_AR_UNAPPR_INVOICE_AMOUNT,
2058 tmp2.GG_AR_APPR_INVOICE_AMOUNT,
2059 tmp2.GG_AR_AMOUNT_DUE,
2060 tmp2.GG_AR_AMOUNT_OVERDUE,
2061 tmp2.GP_REVENUE,
2062 tmp2.GP_FUNDING,
2063 tmp2.GP_INITIAL_FUNDING_AMOUNT,
2064 tmp2.GP_ADDITIONAL_FUNDING_AMOUNT,
2065 tmp2.GP_CANCELLED_FUNDING_AMOUNT,
2066 tmp2.GP_FUNDING_ADJUSTMENT_AMOUNT,
2067 tmp2.GP_REVENUE_WRITEOFF,
2068 tmp2.GP_AR_INVOICE_AMOUNT,
2069 tmp2.GP_AR_CASH_APPLIED_AMOUNT,
2070 tmp2.GP_AR_INVOICE_WRITEOFF_AMOUNT,
2071 tmp2.GP_AR_CREDIT_MEMO_AMOUNT,
2072 tmp2.GP_UNBILLED_RECEIVABLES,
2073 tmp2.GP_UNEARNED_REVENUE,
2074 tmp2.GP_AR_UNAPPR_INVOICE_AMOUNT,
2075 tmp2.GP_AR_APPR_INVOICE_AMOUNT,
2076 tmp2.GP_AR_AMOUNT_DUE,
2077 tmp2.GP_AR_AMOUNT_OVERDUE,
2078 tmp2.GG2_REVENUE,
2079 tmp2.GG2_FUNDING,
2080 tmp2.GG2_INITIAL_FUNDING_AMOUNT,
2081 tmp2.GG2_ADDITIONAL_FUNDING_AMOUNT,
2082 tmp2.GG2_CANCELLED_FUNDING_AMOUNT,
2083 tmp2.GG2_FUNDING_ADJUSTMENT_AMOUNT,
2084 tmp2.GG2_REVENUE_WRITEOFF,
2085 tmp2.GG2_AR_INVOICE_AMOUNT,
2086 tmp2.GG2_AR_CASH_APPLIED_AMOUNT,
2087 tmp2.GG2_AR_INVOICE_WRITEOFF_AMOUNT,
2088 tmp2.GG2_AR_CREDIT_MEMO_AMOUNT,
2089 tmp2.GG2_UNBILLED_RECEIVABLES,
2090 tmp2.GG2_UNEARNED_REVENUE,
2091 tmp2.GG2_AR_UNAPPR_INVOICE_AMOUNT,
2092 tmp2.GG2_AR_APPR_INVOICE_AMOUNT,
2093 tmp2.GG2_AR_AMOUNT_DUE,
2094 tmp2.GG2_AR_AMOUNT_OVERDUE,
2095 tmp2.GP2_REVENUE,
2096 tmp2.GP2_FUNDING,
2097 tmp2.GP2_INITIAL_FUNDING_AMOUNT,
2098 tmp2.GP2_ADDITIONAL_FUNDING_AMOUNT,
2099 tmp2.GP2_CANCELLED_FUNDING_AMOUNT,
2100 tmp2.GP2_FUNDING_ADJUSTMENT_AMOUNT,
2101 tmp2.GP2_REVENUE_WRITEOFF,
2102 tmp2.GP2_AR_INVOICE_AMOUNT,
2103 tmp2.GP2_AR_CASH_APPLIED_AMOUNT,
2104 tmp2.GP2_AR_INVOICE_WRITEOFF_AMOUNT,
2105 tmp2.GP2_AR_CREDIT_MEMO_AMOUNT,
2106 tmp2.GP2_UNBILLED_RECEIVABLES,
2107 tmp2.GP2_UNEARNED_REVENUE,
2108 tmp2.GP2_AR_UNAPPR_INVOICE_AMOUNT,
2109 tmp2.GP2_AR_APPR_INVOICE_AMOUNT,
2110 tmp2.GP2_AR_AMOUNT_DUE,
2111 tmp2.GP2_AR_AMOUNT_OVERDUE
2112 from
2113 PJI_FM_AGGR_ACT2 tmp2
2114 where
2115 tmp2.WORKER_ID = p_worker_id and
2116 tmp2.GL_PERIOD_NAME is not null and
2117 tmp2.PA_PERIOD_NAME is not null
2118 union all
2119 select
2120 tmp2.WORKER_ID,
2121 tmp2.PROJECT_ID,
2122 tmp2.PROJECT_ORG_ID,
2123 tmp2.PROJECT_ORGANIZATION_ID,
2124 tmp2.TASK_ID,
2125 tmp2.GL_TIME_ID,
2126 gl_per.PERIOD_NAME GL_PERIOD_NAME,
2127 pa_per.PERIOD_NAME PA_PERIOD_NAME,
2128 tmp2.GL_CALENDAR_ID,
2129 tmp2.PA_CALENDAR_ID,
2130 tmp2.TXN_CURRENCY_CODE,
2131 tmp2.TXN_REVENUE,
2132 tmp2.TXN_FUNDING,
2133 tmp2.TXN_INITIAL_FUNDING_AMOUNT,
2134 tmp2.TXN_ADDITIONAL_FUNDING_AMOUNT,
2135 tmp2.TXN_CANCELLED_FUNDING_AMOUNT,
2136 tmp2.TXN_FUNDING_ADJUSTMENT_AMOUNT,
2137 tmp2.TXN_REVENUE_WRITEOFF,
2138 tmp2.TXN_AR_INVOICE_AMOUNT,
2139 tmp2.TXN_AR_CASH_APPLIED_AMOUNT,
2140 tmp2.TXN_AR_INVOICE_WRITEOFF_AMOUNT,
2141 tmp2.TXN_AR_CREDIT_MEMO_AMOUNT,
2142 tmp2.TXN_UNBILLED_RECEIVABLES,
2143 tmp2.TXN_UNEARNED_REVENUE,
2144 tmp2.TXN_AR_UNAPPR_INVOICE_AMOUNT,
2145 tmp2.TXN_AR_APPR_INVOICE_AMOUNT,
2146 tmp2.TXN_AR_AMOUNT_DUE,
2147 tmp2.TXN_AR_AMOUNT_OVERDUE,
2148 tmp2.PRJ_REVENUE,
2149 tmp2.PRJ_FUNDING,
2150 tmp2.PRJ_INITIAL_FUNDING_AMOUNT,
2151 tmp2.PRJ_ADDITIONAL_FUNDING_AMOUNT,
2152 tmp2.PRJ_CANCELLED_FUNDING_AMOUNT,
2153 tmp2.PRJ_FUNDING_ADJUSTMENT_AMOUNT,
2154 tmp2.PRJ_REVENUE_WRITEOFF,
2155 tmp2.PRJ_AR_INVOICE_AMOUNT,
2156 tmp2.PRJ_AR_CASH_APPLIED_AMOUNT,
2157 tmp2.PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
2158 tmp2.PRJ_AR_CREDIT_MEMO_AMOUNT,
2159 tmp2.PRJ_UNBILLED_RECEIVABLES,
2160 tmp2.PRJ_UNEARNED_REVENUE,
2161 tmp2.PRJ_AR_UNAPPR_INVOICE_AMOUNT,
2162 tmp2.PRJ_AR_APPR_INVOICE_AMOUNT,
2163 tmp2.PRJ_AR_AMOUNT_DUE,
2164 tmp2.PRJ_AR_AMOUNT_OVERDUE,
2165 tmp2.POU_REVENUE,
2166 tmp2.POU_FUNDING,
2167 tmp2.POU_INITIAL_FUNDING_AMOUNT,
2168 tmp2.POU_ADDITIONAL_FUNDING_AMOUNT,
2169 tmp2.POU_CANCELLED_FUNDING_AMOUNT,
2170 tmp2.POU_FUNDING_ADJUSTMENT_AMOUNT,
2171 tmp2.POU_REVENUE_WRITEOFF,
2172 tmp2.POU_AR_INVOICE_AMOUNT,
2173 tmp2.POU_AR_CASH_APPLIED_AMOUNT,
2174 tmp2.POU_AR_INVOICE_WRITEOFF_AMOUNT,
2175 tmp2.POU_AR_CREDIT_MEMO_AMOUNT,
2176 tmp2.POU_UNBILLED_RECEIVABLES,
2177 tmp2.POU_UNEARNED_REVENUE,
2178 tmp2.POU_AR_UNAPPR_INVOICE_AMOUNT,
2179 tmp2.POU_AR_APPR_INVOICE_AMOUNT,
2180 tmp2.POU_AR_AMOUNT_DUE,
2181 tmp2.POU_AR_AMOUNT_OVERDUE,
2182 tmp2.INITIAL_FUNDING_COUNT,
2183 tmp2.ADDITIONAL_FUNDING_COUNT,
2184 tmp2.CANCELLED_FUNDING_COUNT,
2185 tmp2.FUNDING_ADJUSTMENT_COUNT,
2186 tmp2.AR_INVOICE_COUNT,
2187 tmp2.AR_CASH_APPLIED_COUNT,
2188 tmp2.AR_INVOICE_WRITEOFF_COUNT,
2189 tmp2.AR_CREDIT_MEMO_COUNT,
2190 tmp2.AR_UNAPPR_INVOICE_COUNT,
2191 tmp2.AR_APPR_INVOICE_COUNT,
2192 tmp2.AR_COUNT_DUE,
2193 tmp2.AR_COUNT_OVERDUE,
2194 tmp2.GG_REVENUE,
2195 tmp2.GG_FUNDING,
2196 tmp2.GG_INITIAL_FUNDING_AMOUNT,
2197 tmp2.GG_ADDITIONAL_FUNDING_AMOUNT,
2198 tmp2.GG_CANCELLED_FUNDING_AMOUNT,
2199 tmp2.GG_FUNDING_ADJUSTMENT_AMOUNT,
2200 tmp2.GG_REVENUE_WRITEOFF,
2201 tmp2.GG_AR_INVOICE_AMOUNT,
2202 tmp2.GG_AR_CASH_APPLIED_AMOUNT,
2203 tmp2.GG_AR_INVOICE_WRITEOFF_AMOUNT,
2204 tmp2.GG_AR_CREDIT_MEMO_AMOUNT,
2205 tmp2.GG_UNBILLED_RECEIVABLES,
2206 tmp2.GG_UNEARNED_REVENUE,
2207 tmp2.GG_AR_UNAPPR_INVOICE_AMOUNT,
2208 tmp2.GG_AR_APPR_INVOICE_AMOUNT,
2209 tmp2.GG_AR_AMOUNT_DUE,
2210 tmp2.GG_AR_AMOUNT_OVERDUE,
2211 tmp2.GP_REVENUE,
2212 tmp2.GP_FUNDING,
2213 tmp2.GP_INITIAL_FUNDING_AMOUNT,
2214 tmp2.GP_ADDITIONAL_FUNDING_AMOUNT,
2215 tmp2.GP_CANCELLED_FUNDING_AMOUNT,
2216 tmp2.GP_FUNDING_ADJUSTMENT_AMOUNT,
2217 tmp2.GP_REVENUE_WRITEOFF,
2218 tmp2.GP_AR_INVOICE_AMOUNT,
2219 tmp2.GP_AR_CASH_APPLIED_AMOUNT,
2220 tmp2.GP_AR_INVOICE_WRITEOFF_AMOUNT,
2221 tmp2.GP_AR_CREDIT_MEMO_AMOUNT,
2222 tmp2.GP_UNBILLED_RECEIVABLES,
2223 tmp2.GP_UNEARNED_REVENUE,
2224 tmp2.GP_AR_UNAPPR_INVOICE_AMOUNT,
2225 tmp2.GP_AR_APPR_INVOICE_AMOUNT,
2226 tmp2.GP_AR_AMOUNT_DUE,
2227 tmp2.GP_AR_AMOUNT_OVERDUE,
2228 tmp2.GG2_REVENUE,
2229 tmp2.GG2_FUNDING,
2230 tmp2.GG2_INITIAL_FUNDING_AMOUNT,
2231 tmp2.GG2_ADDITIONAL_FUNDING_AMOUNT,
2232 tmp2.GG2_CANCELLED_FUNDING_AMOUNT,
2233 tmp2.GG2_FUNDING_ADJUSTMENT_AMOUNT,
2234 tmp2.GG2_REVENUE_WRITEOFF,
2235 tmp2.GG2_AR_INVOICE_AMOUNT,
2236 tmp2.GG2_AR_CASH_APPLIED_AMOUNT,
2237 tmp2.GG2_AR_INVOICE_WRITEOFF_AMOUNT,
2238 tmp2.GG2_AR_CREDIT_MEMO_AMOUNT,
2239 tmp2.GG2_UNBILLED_RECEIVABLES,
2240 tmp2.GG2_UNEARNED_REVENUE,
2241 tmp2.GG2_AR_UNAPPR_INVOICE_AMOUNT,
2242 tmp2.GG2_AR_APPR_INVOICE_AMOUNT,
2243 tmp2.GG2_AR_AMOUNT_DUE,
2244 tmp2.GG2_AR_AMOUNT_OVERDUE,
2245 tmp2.GP2_REVENUE,
2246 tmp2.GP2_FUNDING,
2247 tmp2.GP2_INITIAL_FUNDING_AMOUNT,
2248 tmp2.GP2_ADDITIONAL_FUNDING_AMOUNT,
2249 tmp2.GP2_CANCELLED_FUNDING_AMOUNT,
2250 tmp2.GP2_FUNDING_ADJUSTMENT_AMOUNT,
2251 tmp2.GP2_REVENUE_WRITEOFF,
2252 tmp2.GP2_AR_INVOICE_AMOUNT,
2253 tmp2.GP2_AR_CASH_APPLIED_AMOUNT,
2254 tmp2.GP2_AR_INVOICE_WRITEOFF_AMOUNT,
2255 tmp2.GP2_AR_CREDIT_MEMO_AMOUNT,
2256 tmp2.GP2_UNBILLED_RECEIVABLES,
2257 tmp2.GP2_UNEARNED_REVENUE,
2258 tmp2.GP2_AR_UNAPPR_INVOICE_AMOUNT,
2259 tmp2.GP2_AR_APPR_INVOICE_AMOUNT,
2260 tmp2.GP2_AR_AMOUNT_DUE,
2261 tmp2.GP2_AR_AMOUNT_OVERDUE
2262 from
2263 PJI_FM_AGGR_ACT2 tmp2,
2264 PA_TIME_CAL_NAME gl_cal, /* Modified for bug 12979524 */
2265 GL_PERIODS gl_per,
2266 PA_PERIODS_ALL pa_per
2267 where
2268 tmp2.WORKER_ID = p_worker_id and
2269 (tmp2.GL_PERIOD_NAME is null or
2270 tmp2.PA_PERIOD_NAME is null) and
2271 gl_cal.CALENDAR_ID = tmp2.GL_CALENDAR_ID and
2272 gl_per.PERIOD_SET_NAME = gl_cal.PERIOD_SET_NAME and
2273 gl_per.PERIOD_TYPE = gl_cal.PERIOD_TYPE and
2274 to_date(to_char(tmp2.GL_TIME_ID), 'J') between gl_per.START_DATE
2275 and gl_per.END_DATE and
2276 pa_per.ORG_ID = tmp2.PROJECT_ORG_ID and
2277 to_date(to_char(tmp2.PA_TIME_ID), 'J') between pa_per.START_DATE
2278 and pa_per.END_DATE
2279 ) tmp2,
2280 PJI_TIME_ENT_PERIOD_V ent
2281 where
2282 tmp2.WORKER_ID = p_worker_id and
2283 to_date(to_char(tmp2.GL_TIME_ID), 'J') between ent.START_DATE and
2284 ent.END_DATE
2285 group by
2286 tmp2.WORKER_ID,
2287 tmp2.PROJECT_ID,
2288 tmp2.PROJECT_ORG_ID,
2289 tmp2.PROJECT_ORGANIZATION_ID,
2290 tmp2.TASK_ID,
2291 ent.ENT_PERIOD_ID,
2292 tmp2.GL_PERIOD_NAME,
2293 tmp2.PA_PERIOD_NAME,
2294 tmp2.GL_CALENDAR_ID,
2295 tmp2.PA_CALENDAR_ID,
2296 tmp2.TXN_CURRENCY_CODE
2297 ) tmp2,
2298 PJI_TIME_CAL_PERIOD_V gl_cal,
2299 PJI_TIME_CAL_PERIOD_V pa_cal,
2300 (
2301 select 'ENT' INVERT_ID from dual union all
2302 select 'GL' INVERT_ID from dual union all
2303 select 'PA' INVERT_ID from dual
2304 ) invert
2305 where
2306 tmp2.GL_CALENDAR_ID = gl_cal.CALENDAR_ID and
2307 tmp2.GL_PERIOD_NAME = gl_cal.NAME and
2308 tmp2.PA_CALENDAR_ID = pa_cal.CALENDAR_ID and
2309 tmp2.PA_PERIOD_NAME = pa_cal.NAME
2310 group by
2311 tmp2.PROJECT_ID,
2312 tmp2.PROJECT_ORG_ID,
2313 tmp2.PROJECT_ORGANIZATION_ID,
2314 tmp2.TASK_ID,
2315 decode(invert.INVERT_ID,
2316 'ENT', 'ENT',
2317 'GL', 'GL',
2318 'PA', 'PA'),
2319 decode(invert.INVERT_ID,
2320 'ENT', tmp2.ENT_PERIOD_ID,
2321 'GL', gl_cal.CAL_PERIOD_ID,
2322 'PA', pa_cal.CAL_PERIOD_ID),
2323 tmp2.TXN_CURRENCY_CODE;
2324
2325 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.AGGREGATE_ACR_PERIODS(p_worker_id);');
2326
2327 commit;
2328
2329 end AGGREGATE_ACR_PERIODS;
2330
2331
2332 -- -----------------------------------------------------
2333 -- procedure INSERT_NEW_HEADERS
2334 --
2335 -- History
2336 -- 19-MAR-2004 SVERMETT Created
2337 --
2338 -- Internal PJP Summarization API.
2339 --
2340 -- -----------------------------------------------------
2341 procedure INSERT_NEW_HEADERS (p_worker_id in number) is
2342
2343 l_process varchar2(30);
2344 l_last_update_date date;
2345 l_last_updated_by number;
2346 l_creation_date date;
2347 l_created_by number;
2348 l_last_update_login number;
2349 l_extraction_type varchar2(15);
2350
2351 begin
2352
2353 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2354
2355 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.INSERT_NEW_HEADERS(p_worker_id);')) then
2356 return;
2357 end if;
2358
2359 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
2360
2361 l_last_update_date := sysdate;
2362 l_last_updated_by := FND_GLOBAL.USER_ID;
2363 l_creation_date := sysdate;
2364 l_created_by := FND_GLOBAL.USER_ID;
2365 l_last_update_login := FND_GLOBAL.LOGIN_ID;
2366
2367 insert /*+ append parallel(hdr_i) */ into PJI_FP_TXN_ACCUM_HEADER hdr_i
2368 (
2369 TXN_ACCUM_HEADER_ID,
2370 PERSON_ID,
2371 EXPENDITURE_ORG_ID,
2372 EXPENDITURE_ORGANIZATION_ID,
2373 RESOURCE_CLASS_ID,
2374 JOB_ID,
2375 VENDOR_ID,
2376 WORK_TYPE_ID,
2377 EXPENDITURE_CATEGORY_ID,
2378 EXPENDITURE_TYPE_ID,
2379 EVENT_TYPE_ID,
2380 EXP_EVT_TYPE_ID,
2381 EXPENDITURE_TYPE,
2382 EVENT_TYPE,
2383 EVENT_TYPE_CLASSIFICATION,
2384 EXPENDITURE_CATEGORY,
2385 REVENUE_CATEGORY,
2386 NON_LABOR_RESOURCE_ID,
2387 BOM_LABOR_RESOURCE_ID,
2388 BOM_EQUIPMENT_RESOURCE_ID,
2389 ITEM_CATEGORY_ID,
2390 INVENTORY_ITEM_ID,
2391 PROJECT_ROLE_ID,
2392 PERSON_TYPE,
2393 SYSTEM_LINKAGE_FUNCTION,
2394 NAMED_ROLE, /* Added for bug 9108213 (base bug 9099907) */
2395 LAST_UPDATE_DATE,
2396 LAST_UPDATED_BY,
2397 CREATION_DATE,
2398 CREATED_BY,
2399 LAST_UPDATE_LOGIN,
2400 CBS_ELEMENT_ID /* Added for CBS Changes */
2401 )
2402 select
2403 PJI_FP_TXN_ACCUM_HEADER_S.NEXTVAL TXN_ACCUM_HEADER_ID,
2404 PERSON_ID,
2405 EXPENDITURE_ORG_ID,
2406 EXPENDITURE_ORGANIZATION_ID,
2407 RESOURCE_CLASS_ID,
2408 JOB_ID,
2409 VENDOR_ID,
2410 WORK_TYPE_ID,
2411 EXPENDITURE_CATEGORY_ID,
2412 EXPENDITURE_TYPE_ID,
2413 EVENT_TYPE_ID,
2414 EXP_EVT_TYPE_ID,
2415 EXPENDITURE_TYPE,
2416 EVENT_TYPE,
2417 EVENT_TYPE_CLASSIFICATION,
2418 EXPENDITURE_CATEGORY,
2419 REVENUE_CATEGORY,
2420 NON_LABOR_RESOURCE_ID,
2421 BOM_LABOR_RESOURCE_ID,
2422 BOM_EQUIPMENT_RESOURCE_ID,
2423 ITEM_CATEGORY_ID,
2424 INVENTORY_ITEM_ID,
2425 PROJECT_ROLE_ID,
2426 PERSON_TYPE,
2427 SYSTEM_LINKAGE_FUNCTION,
2428 NAMED_ROLE, /* Added for bug 9108213 (base bug 9099907) */
2429 l_last_update_date,
2430 l_last_updated_by,
2431 l_creation_date,
2432 l_created_by,
2433 l_last_update_login,
2434 CBS_ELEMENT_ID /* Added for CBS Changes */
2435 from
2436 (
2437 select /*+ full(tmp6) parallel(tmp6) */
2438 distinct
2439 PERSON_ID,
2440 EXPENDITURE_ORG_ID,
2441 EXPENDITURE_ORGANIZATION_ID,
2442 RESOURCE_CLASS_ID,
2443 JOB_ID,
2444 VENDOR_ID,
2445 WORK_TYPE_ID,
2446 EXPENDITURE_CATEGORY_ID,
2447 EXPENDITURE_TYPE_ID,
2448 EVENT_TYPE_ID,
2449 EXP_EVT_TYPE_ID,
2450 EXPENDITURE_TYPE,
2451 EVENT_TYPE,
2452 EVENT_TYPE_CLASSIFICATION,
2453 EXPENDITURE_CATEGORY,
2454 REVENUE_CATEGORY,
2455 NON_LABOR_RESOURCE_ID,
2456 BOM_LABOR_RESOURCE_ID,
2457 BOM_EQUIPMENT_RESOURCE_ID,
2458 ITEM_CATEGORY_ID,
2459 INVENTORY_ITEM_ID,
2460 PROJECT_ROLE_ID,
2461 PERSON_TYPE,
2462 SYSTEM_LINKAGE_FUNCTION,
2463 NAMED_ROLE , /* Added for bug 9108213 (base bug 9099907) */
2464 to_number(null) AS CBS_ELEMENT_ID /* Added for CBS Changes */
2465 from
2466 PJI_FM_AGGR_FIN6 tmp6
2467 where
2468 WORKER_ID = p_worker_id
2469 ) tmp6
2470 where
2471 not exists
2472 (select
2473 1
2474 from
2475 PJI_FP_TXN_ACCUM_HEADER hdr
2476 where
2477 tmp6.PERSON_ID = hdr.PERSON_ID and
2478 tmp6.EXPENDITURE_ORG_ID = hdr.EXPENDITURE_ORG_ID and
2479 tmp6.EXPENDITURE_ORGANIZATION_ID = hdr.EXPENDITURE_ORGANIZATION_ID and
2480 tmp6.RESOURCE_CLASS_ID = hdr.RESOURCE_CLASS_ID and
2481 tmp6.JOB_ID = hdr.JOB_ID and
2482 tmp6.VENDOR_ID = hdr.VENDOR_ID and
2483 tmp6.WORK_TYPE_ID = hdr.WORK_TYPE_ID and
2484 tmp6.EXPENDITURE_CATEGORY_ID = hdr.EXPENDITURE_CATEGORY_ID and
2485 tmp6.EXPENDITURE_TYPE_ID = hdr.EXPENDITURE_TYPE_ID and
2486 tmp6.EVENT_TYPE_ID = hdr.EVENT_TYPE_ID and
2487 tmp6.EXP_EVT_TYPE_ID = hdr.EXP_EVT_TYPE_ID and
2488 tmp6.EXPENDITURE_TYPE = hdr.EXPENDITURE_TYPE and
2489 tmp6.EVENT_TYPE = hdr.EVENT_TYPE and
2490 tmp6.EVENT_TYPE_CLASSIFICATION = hdr.EVENT_TYPE_CLASSIFICATION and
2491 tmp6.EXPENDITURE_CATEGORY = hdr.EXPENDITURE_CATEGORY and
2492 tmp6.REVENUE_CATEGORY = hdr.REVENUE_CATEGORY and
2493 tmp6.NON_LABOR_RESOURCE_ID = hdr.NON_LABOR_RESOURCE_ID and
2494 tmp6.BOM_LABOR_RESOURCE_ID = hdr.BOM_LABOR_RESOURCE_ID and
2495 tmp6.BOM_EQUIPMENT_RESOURCE_ID = hdr.BOM_EQUIPMENT_RESOURCE_ID and
2496 tmp6.ITEM_CATEGORY_ID = hdr.ITEM_CATEGORY_ID and
2497 tmp6.INVENTORY_ITEM_ID = hdr.INVENTORY_ITEM_ID and
2498 tmp6.PROJECT_ROLE_ID = hdr.PROJECT_ROLE_ID and
2499 tmp6.PERSON_TYPE = hdr.PERSON_TYPE and
2500 tmp6.SYSTEM_LINKAGE_FUNCTION = hdr.SYSTEM_LINKAGE_FUNCTION and
2501 tmp6.NAMED_ROLE = nvl(hdr.NAMED_ROLE,'PJI$NULL')); /* Added for bug 13595112 */
2502
2503
2504 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.INSERT_NEW_HEADERS(p_worker_id);');
2505
2506 commit;
2507
2508 end INSERT_NEW_HEADERS;
2509
2510
2511 -- -----------------------------------------------------
2512 -- procedure BALANCES_INSERT
2513 --
2514 -- History
2515 -- 19-MAR-2004 SVERMETT Created
2516 --
2517 -- Internal PJP Summarization API.
2518 --
2519 -- -----------------------------------------------------
2520 procedure BALANCES_INSERT (p_worker_id in number) is
2521
2522 l_process varchar2(30);
2523 l_last_update_date date;
2524 l_last_updated_by number;
2525 l_creation_date date;
2526 l_created_by number;
2527 l_last_update_login number;
2528 l_extraction_type varchar2(15);
2529
2530 begin
2531
2532 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2533
2534 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.BALANCES_INSERT(p_worker_id);')) then
2535 return;
2536 end if;
2537
2538 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
2539
2540 l_last_update_date := sysdate;
2541 l_last_updated_by := FND_GLOBAL.USER_ID;
2542 l_creation_date := sysdate;
2543 l_created_by := FND_GLOBAL.USER_ID;
2544 l_last_update_login := FND_GLOBAL.LOGIN_ID;
2545
2546 if (l_extraction_type = 'FULL' or
2547 l_extraction_type = 'PARTIAL') then
2548
2549 insert /*+ append parallel(bal) */ into PJI_FP_TXN_ACCUM bal
2550 (
2551 TXN_ACCUM_HEADER_ID,
2552 RESOURCE_CLASS_ID,
2553 PROJECT_ID,
2554 PROJECT_ORG_ID,
2555 PROJECT_ORGANIZATION_ID,
2556 PROJECT_TYPE_CLASS,
2557 TASK_ID,
2558 ASSIGNMENT_ID,
2559 NAMED_ROLE,
2560 RECVR_PERIOD_TYPE,
2561 RECVR_PERIOD_ID,
2562 TXN_CURRENCY_CODE,
2563 TXN_RAW_COST,
2564 TXN_BILL_RAW_COST,
2565 TXN_BRDN_COST,
2566 TXN_BILL_BRDN_COST,
2567 TXN_REVENUE,
2568 PRJ_RAW_COST,
2569 PRJ_BILL_RAW_COST,
2570 PRJ_BRDN_COST,
2571 PRJ_BILL_BRDN_COST,
2572 PRJ_REVENUE,
2573 POU_RAW_COST,
2574 POU_BILL_RAW_COST,
2575 POU_BRDN_COST,
2576 POU_BILL_BRDN_COST,
2577 POU_REVENUE,
2578 EOU_RAW_COST,
2579 EOU_BILL_RAW_COST,
2580 EOU_BRDN_COST,
2581 EOU_BILL_BRDN_COST,
2582 G1_RAW_COST,
2583 G1_BILL_RAW_COST,
2584 G1_BRDN_COST,
2585 G1_BILL_BRDN_COST,
2586 G1_REVENUE,
2587 G2_RAW_COST,
2588 G2_BILL_RAW_COST,
2589 G2_BRDN_COST,
2590 G2_BILL_BRDN_COST,
2591 G2_REVENUE,
2592 QUANTITY,
2593 BILL_QUANTITY,
2594 LAST_UPDATE_DATE,
2595 LAST_UPDATED_BY,
2596 CREATION_DATE,
2597 CREATED_BY,
2598 LAST_UPDATE_LOGIN,
2599 CBS_ELEMENT_ID /* Added for CBS Changes */
2600 )
2601 select /*+ ordered
2602 full(tmp6) parallel(tmp6) use_hash(tmp6)
2603 full(hdr) parallel(hdr)
2604 pq_distribute(tmp2, hash, hash) */
2605 hdr.TXN_ACCUM_HEADER_ID,
2606 hdr.RESOURCE_CLASS_ID,
2607 tmp6.PROJECT_ID,
2608 tmp6.PROJECT_ORG_ID,
2609 tmp6.PROJECT_ORGANIZATION_ID,
2610 tmp6.PROJECT_TYPE_CLASS,
2611 tmp6.TASK_ID,
2612 tmp6.ASSIGNMENT_ID,
2613 tmp6.NAMED_ROLE,
2614 tmp6.RECVR_PERIOD_TYPE,
2615 tmp6.RECVR_PERIOD_ID,
2616 tmp6.TXN_CURRENCY_CODE,
2617 sum(tmp6.TXN_RAW_COST) TXN_RAW_COST,
2618 sum(tmp6.TXN_BILL_RAW_COST) TXN_BILL_RAW_COST,
2619 sum(tmp6.TXN_BRDN_COST) TXN_BRDN_COST,
2620 sum(tmp6.TXN_BILL_BRDN_COST) TXN_BILL_BRDN_COST,
2621 sum(tmp6.TXN_REVENUE) TXN_REVENUE,
2622 sum(tmp6.PRJ_RAW_COST) PRJ_RAW_COST,
2623 sum(tmp6.PRJ_BILL_RAW_COST) PRJ_BILL_RAW_COST,
2624 sum(tmp6.PRJ_BRDN_COST) PRJ_BRDN_COST,
2625 sum(tmp6.PRJ_BILL_BRDN_COST) PRJ_BILL_BRDN_COST,
2626 sum(tmp6.PRJ_REVENUE) PRJ_REVENUE,
2627 sum(tmp6.POU_RAW_COST) POU_RAW_COST,
2628 sum(tmp6.POU_BILL_RAW_COST) POU_BILL_RAW_COST,
2629 sum(tmp6.POU_BRDN_COST) POU_BRDN_COST,
2630 sum(tmp6.POU_BILL_BRDN_COST) POU_BILL_BRDN_COST,
2631 sum(tmp6.POU_REVENUE) POU_REVENUE,
2632 sum(tmp6.EOU_RAW_COST) EOU_RAW_COST,
2633 sum(tmp6.EOU_BILL_RAW_COST) EOU_BILL_RAW_COST,
2634 sum(tmp6.EOU_BRDN_COST) EOU_BRDN_COST,
2635 sum(tmp6.EOU_BILL_BRDN_COST) EOU_BILL_BRDN_COST,
2636 sum(tmp6.G1_RAW_COST) G1_RAW_COST,
2637 sum(tmp6.G1_BILL_RAW_COST) G1_BILL_RAW_COST,
2638 sum(tmp6.G1_BRDN_COST) G1_BRDN_COST,
2639 sum(tmp6.G1_BILL_BRDN_COST) G1_BILL_BRDN_COST,
2640 sum(tmp6.G1_REVENUE) G1_REVENUE,
2641 sum(tmp6.G2_RAW_COST) G2_RAW_COST,
2642 sum(tmp6.G2_BILL_RAW_COST) G2_BILL_RAW_COST,
2643 sum(tmp6.G2_BRDN_COST) G2_BRDN_COST,
2644 sum(tmp6.G2_BILL_BRDN_COST) G2_BILL_BRDN_COST,
2645 sum(tmp6.G2_REVENUE) G2_REVENUE,
2646 sum(tmp6.QUANTITY) QUANTITY,
2647 sum(tmp6.BILL_QUANTITY) BILL_QUANTITY,
2648 l_last_update_date LAST_UPDATE_DATE,
2649 l_last_updated_by LAST_UPDATED_BY,
2650 l_creation_date CREATION_DATE,
2651 l_created_by CREATED_BY,
2652 l_last_update_login LAST_UPDATE_LOGIN,
2653 tmp6.CBS_ELEMENT_ID CBS_ELEMENT_ID /* Added for CBS Changes */
2654 from
2655 PJI_FM_AGGR_FIN6 tmp6,
2656 PJI_FP_TXN_ACCUM_HEADER hdr
2657 where
2658 tmp6.WORKER_ID = p_worker_id and
2659 tmp6.RECORD_TYPE = 'A' and
2660 tmp6.PERSON_ID = hdr.PERSON_ID and
2661 tmp6.EXPENDITURE_ORG_ID = hdr.EXPENDITURE_ORG_ID and
2662 tmp6.EXPENDITURE_ORGANIZATION_ID = hdr.EXPENDITURE_ORGANIZATION_ID and
2663 tmp6.RESOURCE_CLASS_ID = hdr.RESOURCE_CLASS_ID and
2664 tmp6.JOB_ID = hdr.JOB_ID and
2665 tmp6.VENDOR_ID = hdr.VENDOR_ID and
2666 tmp6.WORK_TYPE_ID = hdr.WORK_TYPE_ID and
2667 tmp6.EXPENDITURE_CATEGORY_ID = hdr.EXPENDITURE_CATEGORY_ID and
2668 tmp6.EXPENDITURE_TYPE_ID = hdr.EXPENDITURE_TYPE_ID and
2669 tmp6.EVENT_TYPE_ID = hdr.EVENT_TYPE_ID and
2670 tmp6.EXP_EVT_TYPE_ID = hdr.EXP_EVT_TYPE_ID and
2671 tmp6.EXPENDITURE_TYPE = hdr.EXPENDITURE_TYPE and
2672 tmp6.EVENT_TYPE = hdr.EVENT_TYPE and
2673 tmp6.EVENT_TYPE_CLASSIFICATION = hdr.EVENT_TYPE_CLASSIFICATION and
2674 tmp6.EXPENDITURE_CATEGORY = hdr.EXPENDITURE_CATEGORY and
2675 tmp6.REVENUE_CATEGORY = hdr.REVENUE_CATEGORY and
2676 tmp6.NON_LABOR_RESOURCE_ID = hdr.NON_LABOR_RESOURCE_ID and
2677 tmp6.BOM_LABOR_RESOURCE_ID = hdr.BOM_LABOR_RESOURCE_ID and
2678 tmp6.BOM_EQUIPMENT_RESOURCE_ID = hdr.BOM_EQUIPMENT_RESOURCE_ID and
2679 tmp6.ITEM_CATEGORY_ID = hdr.ITEM_CATEGORY_ID and
2680 tmp6.INVENTORY_ITEM_ID = hdr.INVENTORY_ITEM_ID and
2681 tmp6.PROJECT_ROLE_ID = hdr.PROJECT_ROLE_ID and
2682 tmp6.PERSON_TYPE = hdr.PERSON_TYPE and
2683 tmp6.SYSTEM_LINKAGE_FUNCTION = hdr.SYSTEM_LINKAGE_FUNCTION
2684 group by
2685 hdr.TXN_ACCUM_HEADER_ID,
2686 hdr.RESOURCE_CLASS_ID,
2687 tmp6.PROJECT_ID,
2688 tmp6.PROJECT_ORG_ID,
2689 tmp6.PROJECT_ORGANIZATION_ID,
2690 tmp6.PROJECT_TYPE_CLASS,
2691 tmp6.TASK_ID,
2692 tmp6.ASSIGNMENT_ID,
2693 tmp6.NAMED_ROLE,
2694 tmp6.RECVR_PERIOD_TYPE,
2695 tmp6.RECVR_PERIOD_ID,
2696 tmp6.TXN_CURRENCY_CODE,
2697 tmp6.CBS_ELEMENT_ID /* Added for CBS Changes */;
2698
2699 elsif (l_extraction_type = 'INCREMENTAL') then
2700
2701 -- insert both commitments and actuals into delta table
2702
2703 insert /*+ append parallel(tmp7) */ into PJI_FM_AGGR_FIN7 tmp7
2704 (
2705 WORKER_ID,
2706 TXN_ACCUM_HEADER_ID,
2707 RECORD_TYPE,
2708 RESOURCE_CLASS_ID,
2709 PROJECT_ID,
2710 PROJECT_ORG_ID,
2711 PROJECT_ORGANIZATION_ID,
2712 PROJECT_TYPE_CLASS,
2713 TASK_ID,
2714 ASSIGNMENT_ID,
2715 NAMED_ROLE,
2716 RECVR_PERIOD_TYPE,
2717 RECVR_PERIOD_ID,
2718 TXN_CURRENCY_CODE,
2719 TXN_REVENUE,
2720 TXN_RAW_COST,
2721 TXN_BRDN_COST,
2722 TXN_BILL_RAW_COST,
2723 TXN_BILL_BRDN_COST,
2724 TXN_SUP_INV_COMMITTED_COST,
2725 TXN_PO_COMMITTED_COST,
2726 TXN_PR_COMMITTED_COST,
2727 TXN_OTH_COMMITTED_COST,
2728 PRJ_REVENUE,
2729 PRJ_RAW_COST,
2730 PRJ_BRDN_COST,
2731 PRJ_BILL_RAW_COST,
2732 PRJ_BILL_BRDN_COST,
2733 PRJ_REVENUE_WRITEOFF,
2734 PRJ_SUP_INV_COMMITTED_COST,
2735 PRJ_PO_COMMITTED_COST,
2736 PRJ_PR_COMMITTED_COST,
2737 PRJ_OTH_COMMITTED_COST,
2738 POU_REVENUE,
2739 POU_RAW_COST,
2740 POU_BRDN_COST,
2741 POU_BILL_RAW_COST,
2742 POU_BILL_BRDN_COST,
2743 POU_REVENUE_WRITEOFF,
2744 POU_SUP_INV_COMMITTED_COST,
2745 POU_PO_COMMITTED_COST,
2746 POU_PR_COMMITTED_COST,
2747 POU_OTH_COMMITTED_COST,
2748 EOU_REVENUE,
2749 EOU_RAW_COST,
2750 EOU_BRDN_COST,
2751 EOU_BILL_RAW_COST,
2752 EOU_BILL_BRDN_COST,
2753 EOU_SUP_INV_COMMITTED_COST,
2754 EOU_PO_COMMITTED_COST,
2755 EOU_PR_COMMITTED_COST,
2756 EOU_OTH_COMMITTED_COST,
2757 QUANTITY,
2758 BILL_QUANTITY,
2759 G1_REVENUE,
2760 G1_RAW_COST,
2761 G1_BRDN_COST,
2762 G1_BILL_RAW_COST,
2763 G1_BILL_BRDN_COST,
2764 G1_REVENUE_WRITEOFF,
2765 G1_SUP_INV_COMMITTED_COST,
2766 G1_PO_COMMITTED_COST,
2767 G1_PR_COMMITTED_COST,
2768 G1_OTH_COMMITTED_COST,
2769 G2_REVENUE,
2770 G2_RAW_COST,
2771 G2_BRDN_COST,
2772 G2_BILL_RAW_COST,
2773 G2_BILL_BRDN_COST,
2774 G2_REVENUE_WRITEOFF,
2775 G2_SUP_INV_COMMITTED_COST,
2776 G2_PO_COMMITTED_COST,
2777 G2_PR_COMMITTED_COST,
2778 G2_OTH_COMMITTED_COST,
2779 CBS_ELEMENT_ID /* Added for CBS Changes */
2780 )
2781 select /*+ ordered
2782 full(tmp6) parallel(tmp6) use_hash(tmp6)
2783 full(hdr) parallel(hdr)
2784 pq_distribute(tmp2, hash, hash) */
2785 tmp6.WORKER_ID,
2786 hdr.TXN_ACCUM_HEADER_ID,
2787 tmp6.RECORD_TYPE,
2788 hdr.RESOURCE_CLASS_ID,
2789 tmp6.PROJECT_ID,
2790 tmp6.PROJECT_ORG_ID,
2791 tmp6.PROJECT_ORGANIZATION_ID,
2792 tmp6.PROJECT_TYPE_CLASS,
2793 tmp6.TASK_ID,
2794 tmp6.ASSIGNMENT_ID,
2795 tmp6.NAMED_ROLE,
2796 tmp6.RECVR_PERIOD_TYPE,
2797 tmp6.RECVR_PERIOD_ID,
2798 tmp6.TXN_CURRENCY_CODE,
2799 tmp6.TXN_REVENUE,
2800 tmp6.TXN_RAW_COST,
2801 tmp6.TXN_BRDN_COST,
2802 tmp6.TXN_BILL_RAW_COST,
2803 tmp6.TXN_BILL_BRDN_COST,
2804 tmp6.TXN_SUP_INV_COMMITTED_COST,
2805 tmp6.TXN_PO_COMMITTED_COST,
2806 tmp6.TXN_PR_COMMITTED_COST,
2807 tmp6.TXN_OTH_COMMITTED_COST,
2808 tmp6.PRJ_REVENUE,
2809 tmp6.PRJ_RAW_COST,
2810 tmp6.PRJ_BRDN_COST,
2811 tmp6.PRJ_BILL_RAW_COST,
2812 tmp6.PRJ_BILL_BRDN_COST,
2813 tmp6.PRJ_REVENUE_WRITEOFF,
2814 tmp6.PRJ_SUP_INV_COMMITTED_COST,
2815 tmp6.PRJ_PO_COMMITTED_COST,
2816 tmp6.PRJ_PR_COMMITTED_COST,
2817 tmp6.PRJ_OTH_COMMITTED_COST,
2818 tmp6.POU_REVENUE,
2819 tmp6.POU_RAW_COST,
2820 tmp6.POU_BRDN_COST,
2821 tmp6.POU_BILL_RAW_COST,
2822 tmp6.POU_BILL_BRDN_COST,
2823 tmp6.POU_REVENUE_WRITEOFF,
2824 tmp6.POU_SUP_INV_COMMITTED_COST,
2825 tmp6.POU_PO_COMMITTED_COST,
2826 tmp6.POU_PR_COMMITTED_COST,
2827 tmp6.POU_OTH_COMMITTED_COST,
2828 tmp6.EOU_REVENUE,
2829 tmp6.EOU_RAW_COST,
2830 tmp6.EOU_BRDN_COST,
2831 tmp6.EOU_BILL_RAW_COST,
2832 tmp6.EOU_BILL_BRDN_COST,
2833 tmp6.EOU_SUP_INV_COMMITTED_COST,
2834 tmp6.EOU_PO_COMMITTED_COST,
2835 tmp6.EOU_PR_COMMITTED_COST,
2836 tmp6.EOU_OTH_COMMITTED_COST,
2837 tmp6.QUANTITY,
2838 tmp6.BILL_QUANTITY,
2839 tmp6.G1_REVENUE,
2840 tmp6.G1_RAW_COST,
2841 tmp6.G1_BRDN_COST,
2842 tmp6.G1_BILL_RAW_COST,
2843 tmp6.G1_BILL_BRDN_COST,
2844 tmp6.G1_REVENUE_WRITEOFF,
2845 tmp6.G1_SUP_INV_COMMITTED_COST,
2846 tmp6.G1_PO_COMMITTED_COST,
2847 tmp6.G1_PR_COMMITTED_COST,
2848 tmp6.G1_OTH_COMMITTED_COST,
2849 tmp6.G2_REVENUE,
2850 tmp6.G2_RAW_COST,
2851 tmp6.G2_BRDN_COST,
2852 tmp6.G2_BILL_RAW_COST,
2853 tmp6.G2_BILL_BRDN_COST,
2854 tmp6.G2_REVENUE_WRITEOFF,
2855 tmp6.G2_SUP_INV_COMMITTED_COST,
2856 tmp6.G2_PO_COMMITTED_COST,
2857 tmp6.G2_PR_COMMITTED_COST,
2858 tmp6.G2_OTH_COMMITTED_COST,
2859 tmp6.CBS_ELEMENT_ID /* Added for CBS Changes */
2860 from
2861 PJI_FM_AGGR_FIN6 tmp6,
2862 PJI_FP_TXN_ACCUM_HEADER hdr
2863 where
2864 tmp6.WORKER_ID = p_worker_id and
2865 tmp6.PERSON_ID = hdr.PERSON_ID and
2866 tmp6.EXPENDITURE_ORG_ID = hdr.EXPENDITURE_ORG_ID and
2867 tmp6.EXPENDITURE_ORGANIZATION_ID = hdr.EXPENDITURE_ORGANIZATION_ID and
2868 tmp6.RESOURCE_CLASS_ID = hdr.RESOURCE_CLASS_ID and
2869 tmp6.JOB_ID = hdr.JOB_ID and
2870 tmp6.VENDOR_ID = hdr.VENDOR_ID and
2871 tmp6.WORK_TYPE_ID = hdr.WORK_TYPE_ID and
2872 tmp6.EXPENDITURE_CATEGORY_ID = hdr.EXPENDITURE_CATEGORY_ID and
2873 tmp6.EXPENDITURE_TYPE_ID = hdr.EXPENDITURE_TYPE_ID and
2874 tmp6.EVENT_TYPE_ID = hdr.EVENT_TYPE_ID and
2875 tmp6.EXP_EVT_TYPE_ID = hdr.EXP_EVT_TYPE_ID and
2876 tmp6.EXPENDITURE_TYPE = hdr.EXPENDITURE_TYPE and
2877 tmp6.EVENT_TYPE = hdr.EVENT_TYPE and
2878 tmp6.EVENT_TYPE_CLASSIFICATION = hdr.EVENT_TYPE_CLASSIFICATION and
2879 tmp6.EXPENDITURE_CATEGORY = hdr.EXPENDITURE_CATEGORY and
2880 tmp6.REVENUE_CATEGORY = hdr.REVENUE_CATEGORY and
2881 tmp6.NON_LABOR_RESOURCE_ID = hdr.NON_LABOR_RESOURCE_ID and
2882 tmp6.BOM_LABOR_RESOURCE_ID = hdr.BOM_LABOR_RESOURCE_ID and
2883 tmp6.BOM_EQUIPMENT_RESOURCE_ID = hdr.BOM_EQUIPMENT_RESOURCE_ID and
2884 tmp6.ITEM_CATEGORY_ID = hdr.ITEM_CATEGORY_ID and
2885 tmp6.INVENTORY_ITEM_ID = hdr.INVENTORY_ITEM_ID and
2886 tmp6.PROJECT_ROLE_ID = hdr.PROJECT_ROLE_ID and
2887 tmp6.PERSON_TYPE = hdr.PERSON_TYPE and
2888 tmp6.SYSTEM_LINKAGE_FUNCTION = hdr.SYSTEM_LINKAGE_FUNCTION and
2889 tmp6.PROJECT_ID in (select pjp.PROJECT_ID
2890 from PJI_PJP_PROJ_EXTR_STATUS pjp);
2891
2892 end if;
2893
2894 -- OLAP
2895 --PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.BALANCES_INSERT(p_worker_id);');
2896
2897 -- End OLAP
2898 commit;
2899
2900 -- OLAP Change
2901
2902 /*
2903 if (l_extraction_type = 'FULL' or
2904 l_extraction_type = 'PARTIAL') then
2905
2906 insert into PA_STAGE1_BATCH_OLAP
2907 (
2908 PROJECT_ID
2909 )
2910 select
2911 distinct tmp6.PROJECT_ID
2912 from
2913 PJI_FM_AGGR_FIN6 tmp6,
2914 PJI_FP_TXN_ACCUM_HEADER hdr,
2915 pa_projects_all prj
2916 where
2917 tmp6.WORKER_ID = p_worker_id and
2918 tmp6.RECORD_TYPE = 'A' and
2919 tmp6.PERSON_ID = hdr.PERSON_ID and
2920 tmp6.EXPENDITURE_ORG_ID = hdr.EXPENDITURE_ORG_ID and
2921 tmp6.EXPENDITURE_ORGANIZATION_ID = hdr.EXPENDITURE_ORGANIZATION_ID and
2922 tmp6.RESOURCE_CLASS_ID = hdr.RESOURCE_CLASS_ID and
2923 tmp6.JOB_ID = hdr.JOB_ID and
2924 tmp6.VENDOR_ID = hdr.VENDOR_ID and
2925 tmp6.WORK_TYPE_ID = hdr.WORK_TYPE_ID and
2926 tmp6.EXPENDITURE_CATEGORY_ID = hdr.EXPENDITURE_CATEGORY_ID and
2927 tmp6.EXPENDITURE_TYPE_ID = hdr.EXPENDITURE_TYPE_ID and
2928 tmp6.EVENT_TYPE_ID = hdr.EVENT_TYPE_ID and
2929 tmp6.EXP_EVT_TYPE_ID = hdr.EXP_EVT_TYPE_ID and
2930 tmp6.EXPENDITURE_TYPE = hdr.EXPENDITURE_TYPE and
2931 tmp6.EVENT_TYPE = hdr.EVENT_TYPE and
2932 tmp6.EVENT_TYPE_CLASSIFICATION = hdr.EVENT_TYPE_CLASSIFICATION and
2933 tmp6.EXPENDITURE_CATEGORY = hdr.EXPENDITURE_CATEGORY and
2934 tmp6.REVENUE_CATEGORY = hdr.REVENUE_CATEGORY and
2935 tmp6.NON_LABOR_RESOURCE_ID = hdr.NON_LABOR_RESOURCE_ID and
2936 tmp6.BOM_LABOR_RESOURCE_ID = hdr.BOM_LABOR_RESOURCE_ID and
2937 tmp6.BOM_EQUIPMENT_RESOURCE_ID = hdr.BOM_EQUIPMENT_RESOURCE_ID and
2938 tmp6.ITEM_CATEGORY_ID = hdr.ITEM_CATEGORY_ID and
2939 tmp6.INVENTORY_ITEM_ID = hdr.INVENTORY_ITEM_ID and
2940 tmp6.PROJECT_ROLE_ID = hdr.PROJECT_ROLE_ID and
2941 tmp6.PERSON_TYPE = hdr.PERSON_TYPE and
2942 tmp6.SYSTEM_LINKAGE_FUNCTION = hdr.SYSTEM_LINKAGE_FUNCTION and
2943 tmp6.project_id = prj.project_id and
2944 prj.pjt_rollup_enabled_flag = 'Y' and
2945 tmp6.PROJECT_ID not in (select pjp.PROJECT_ID
2946 from PA_STAGE1_BATCH_OLAP pjp);
2947
2948
2949 elsif (l_extraction_type = 'INCREMENTAL') then
2950
2951 -- insert both commitments and actuals into delta table
2952
2953 insert /*+ append parallel(tmp7) */
2954 /* into PA_FIN7_OLAP tmp7
2955 (
2956 WORKER_ID,
2957 TXN_ACCUM_HEADER_ID,
2958 RECORD_TYPE,
2959 RESOURCE_CLASS_ID,
2960 PROJECT_ID,
2961 PROJECT_ORG_ID,
2962 PROJECT_ORGANIZATION_ID,
2963 PROJECT_TYPE_CLASS,
2964 TASK_ID,
2965 ASSIGNMENT_ID,
2966 NAMED_ROLE,
2967 RECVR_PERIOD_TYPE,
2968 RECVR_PERIOD_ID,
2969 TXN_CURRENCY_CODE,
2970 TXN_REVENUE,
2971 TXN_RAW_COST,
2972 TXN_BRDN_COST,
2973 TXN_BILL_RAW_COST,
2974 TXN_BILL_BRDN_COST,
2975 TXN_SUP_INV_COMMITTED_COST,
2976 TXN_PO_COMMITTED_COST,
2977 TXN_PR_COMMITTED_COST,
2978 TXN_OTH_COMMITTED_COST,
2979 PRJ_REVENUE,
2980 PRJ_RAW_COST,
2981 PRJ_BRDN_COST,
2982 PRJ_BILL_RAW_COST,
2983 PRJ_BILL_BRDN_COST,
2984 PRJ_REVENUE_WRITEOFF,
2985 PRJ_SUP_INV_COMMITTED_COST,
2986 PRJ_PO_COMMITTED_COST,
2987 PRJ_PR_COMMITTED_COST,
2988 PRJ_OTH_COMMITTED_COST,
2989 POU_REVENUE,
2990 POU_RAW_COST,
2991 POU_BRDN_COST,
2992 POU_BILL_RAW_COST,
2993 POU_BILL_BRDN_COST,
2994 POU_REVENUE_WRITEOFF,
2995 POU_SUP_INV_COMMITTED_COST,
2996 POU_PO_COMMITTED_COST,
2997 POU_PR_COMMITTED_COST,
2998 POU_OTH_COMMITTED_COST,
2999 EOU_REVENUE,
3000 EOU_RAW_COST,
3001 EOU_BRDN_COST,
3002 EOU_BILL_RAW_COST,
3003 EOU_BILL_BRDN_COST,
3004 EOU_SUP_INV_COMMITTED_COST,
3005 EOU_PO_COMMITTED_COST,
3006 EOU_PR_COMMITTED_COST,
3007 EOU_OTH_COMMITTED_COST,
3008 QUANTITY,
3009 BILL_QUANTITY,
3010 G1_REVENUE,
3011 G1_RAW_COST,
3012 G1_BRDN_COST,
3013 G1_BILL_RAW_COST,
3014 G1_BILL_BRDN_COST,
3015 G1_REVENUE_WRITEOFF,
3016 G1_SUP_INV_COMMITTED_COST,
3017 G1_PO_COMMITTED_COST,
3018 G1_PR_COMMITTED_COST,
3019 G1_OTH_COMMITTED_COST,
3020 G2_REVENUE,
3021 G2_RAW_COST,
3022 G2_BRDN_COST,
3023 G2_BILL_RAW_COST,
3024 G2_BILL_BRDN_COST,
3025 G2_REVENUE_WRITEOFF,
3026 G2_SUP_INV_COMMITTED_COST,
3027 G2_PO_COMMITTED_COST,
3028 G2_PR_COMMITTED_COST,
3029 G2_OTH_COMMITTED_COST
3030 )
3031 select /*+ ordered
3032 full(tmp6) parallel(tmp6) use_hash(tmp6)
3033 full(hdr) parallel(hdr)
3034 pq_distribute(tmp2, hash, hash) */
3035 /* tmp6.WORKER_ID,
3036 hdr.TXN_ACCUM_HEADER_ID,
3037 tmp6.RECORD_TYPE,
3038 hdr.RESOURCE_CLASS_ID,
3039 tmp6.PROJECT_ID,
3040 tmp6.PROJECT_ORG_ID,
3041 tmp6.PROJECT_ORGANIZATION_ID,
3042 tmp6.PROJECT_TYPE_CLASS,
3043 tmp6.TASK_ID,
3044 tmp6.ASSIGNMENT_ID,
3045 tmp6.NAMED_ROLE,
3046 tmp6.RECVR_PERIOD_TYPE,
3047 tmp6.RECVR_PERIOD_ID,
3048 tmp6.TXN_CURRENCY_CODE,
3049 tmp6.TXN_REVENUE,
3050 tmp6.TXN_RAW_COST,
3051 tmp6.TXN_BRDN_COST,
3052 tmp6.TXN_BILL_RAW_COST,
3053 tmp6.TXN_BILL_BRDN_COST,
3054 tmp6.TXN_SUP_INV_COMMITTED_COST,
3055 tmp6.TXN_PO_COMMITTED_COST,
3056 tmp6.TXN_PR_COMMITTED_COST,
3057 tmp6.TXN_OTH_COMMITTED_COST,
3058 tmp6.PRJ_REVENUE,
3059 tmp6.PRJ_RAW_COST,
3060 tmp6.PRJ_BRDN_COST,
3061 tmp6.PRJ_BILL_RAW_COST,
3062 tmp6.PRJ_BILL_BRDN_COST,
3063 tmp6.PRJ_REVENUE_WRITEOFF,
3064 tmp6.PRJ_SUP_INV_COMMITTED_COST,
3065 tmp6.PRJ_PO_COMMITTED_COST,
3066 tmp6.PRJ_PR_COMMITTED_COST,
3067 tmp6.PRJ_OTH_COMMITTED_COST,
3068 tmp6.POU_REVENUE,
3069 tmp6.POU_RAW_COST,
3070 tmp6.POU_BRDN_COST,
3071 tmp6.POU_BILL_RAW_COST,
3072 tmp6.POU_BILL_BRDN_COST,
3073 tmp6.POU_REVENUE_WRITEOFF,
3074 tmp6.POU_SUP_INV_COMMITTED_COST,
3075 tmp6.POU_PO_COMMITTED_COST,
3076 tmp6.POU_PR_COMMITTED_COST,
3077 tmp6.POU_OTH_COMMITTED_COST,
3078 tmp6.EOU_REVENUE,
3079 tmp6.EOU_RAW_COST,
3080 tmp6.EOU_BRDN_COST,
3081 tmp6.EOU_BILL_RAW_COST,
3082 tmp6.EOU_BILL_BRDN_COST,
3083 tmp6.EOU_SUP_INV_COMMITTED_COST,
3084 tmp6.EOU_PO_COMMITTED_COST,
3085 tmp6.EOU_PR_COMMITTED_COST,
3086 tmp6.EOU_OTH_COMMITTED_COST,
3087 tmp6.QUANTITY,
3088 tmp6.BILL_QUANTITY,
3089 tmp6.G1_REVENUE,
3090 tmp6.G1_RAW_COST,
3091 tmp6.G1_BRDN_COST,
3092 tmp6.G1_BILL_RAW_COST,
3093 tmp6.G1_BILL_BRDN_COST,
3094 tmp6.G1_REVENUE_WRITEOFF,
3095 tmp6.G1_SUP_INV_COMMITTED_COST,
3096 tmp6.G1_PO_COMMITTED_COST,
3097 tmp6.G1_PR_COMMITTED_COST,
3098 tmp6.G1_OTH_COMMITTED_COST,
3099 tmp6.G2_REVENUE,
3100 tmp6.G2_RAW_COST,
3101 tmp6.G2_BRDN_COST,
3102 tmp6.G2_BILL_RAW_COST,
3103 tmp6.G2_BILL_BRDN_COST,
3104 tmp6.G2_REVENUE_WRITEOFF,
3105 tmp6.G2_SUP_INV_COMMITTED_COST,
3106 tmp6.G2_PO_COMMITTED_COST,
3107 tmp6.G2_PR_COMMITTED_COST,
3108 tmp6.G2_OTH_COMMITTED_COST
3109 from
3110 PJI_FM_AGGR_FIN6 tmp6,
3111 PJI_FP_TXN_ACCUM_HEADER hdr,
3112 pa_projects_all prj
3113 where
3114 tmp6.WORKER_ID = p_worker_id and
3115 tmp6.PERSON_ID = hdr.PERSON_ID and
3116 tmp6.EXPENDITURE_ORG_ID = hdr.EXPENDITURE_ORG_ID and
3117 tmp6.EXPENDITURE_ORGANIZATION_ID = hdr.EXPENDITURE_ORGANIZATION_ID and
3118 tmp6.RESOURCE_CLASS_ID = hdr.RESOURCE_CLASS_ID and
3119 tmp6.JOB_ID = hdr.JOB_ID and
3120 tmp6.VENDOR_ID = hdr.VENDOR_ID and
3121 tmp6.WORK_TYPE_ID = hdr.WORK_TYPE_ID and
3122 tmp6.EXPENDITURE_CATEGORY_ID = hdr.EXPENDITURE_CATEGORY_ID and
3123 tmp6.EXPENDITURE_TYPE_ID = hdr.EXPENDITURE_TYPE_ID and
3124 tmp6.EVENT_TYPE_ID = hdr.EVENT_TYPE_ID and
3125 tmp6.EXP_EVT_TYPE_ID = hdr.EXP_EVT_TYPE_ID and
3126 tmp6.EXPENDITURE_TYPE = hdr.EXPENDITURE_TYPE and
3127 tmp6.EVENT_TYPE = hdr.EVENT_TYPE and
3128 tmp6.EVENT_TYPE_CLASSIFICATION = hdr.EVENT_TYPE_CLASSIFICATION and
3129 tmp6.EXPENDITURE_CATEGORY = hdr.EXPENDITURE_CATEGORY and
3130 tmp6.REVENUE_CATEGORY = hdr.REVENUE_CATEGORY and
3131 tmp6.NON_LABOR_RESOURCE_ID = hdr.NON_LABOR_RESOURCE_ID and
3132 tmp6.BOM_LABOR_RESOURCE_ID = hdr.BOM_LABOR_RESOURCE_ID and
3133 tmp6.BOM_EQUIPMENT_RESOURCE_ID = hdr.BOM_EQUIPMENT_RESOURCE_ID and
3134 tmp6.ITEM_CATEGORY_ID = hdr.ITEM_CATEGORY_ID and
3135 tmp6.INVENTORY_ITEM_ID = hdr.INVENTORY_ITEM_ID and
3136 tmp6.PROJECT_ROLE_ID = hdr.PROJECT_ROLE_ID and
3137 tmp6.PERSON_TYPE = hdr.PERSON_TYPE and
3138 tmp6.SYSTEM_LINKAGE_FUNCTION = hdr.SYSTEM_LINKAGE_FUNCTION and
3139 tmp6.PROJECT_ID in (select pjp.PROJECT_ID
3140 from PJI_PJP_PROJ_EXTR_STATUS pjp);
3141
3142
3143 end if;
3144
3145 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.BALANCES_INSERT(p_worker_id);');
3146 commit;
3147 -- End OLAP change
3148 */ -- COMMENTED TO REVERT OLAP CHANGES FOR 15900841
3149
3150 end BALANCES_INSERT;
3151
3152
3153 -- -----------------------------------------------------
3154 -- procedure BALANCES_INCR_NEW_PRJ
3155 --
3156 -- History
3157 -- 19-MAR-2004 SVERMETT Created
3158 --
3159 -- Internal PJP Summarization API.
3160 --
3161 -- -----------------------------------------------------
3162 procedure BALANCES_INCR_NEW_PRJ (p_worker_id in number) is
3163
3164 l_process varchar2(30);
3165 l_last_update_date date;
3166 l_last_updated_by number;
3167 l_creation_date date;
3168 l_created_by number;
3169 l_last_update_login number;
3170 l_extraction_type varchar2(15);
3171
3172 begin
3173
3174 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3175
3176 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.BALANCES_INCR_NEW_PRJ(p_worker_id);')) then
3177 return;
3178 end if;
3179
3180 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3181
3182 l_last_update_date := sysdate;
3183 l_last_updated_by := FND_GLOBAL.USER_ID;
3184 l_creation_date := sysdate;
3185 l_created_by := FND_GLOBAL.USER_ID;
3186 l_last_update_login := FND_GLOBAL.LOGIN_ID;
3187
3188 if (l_extraction_type = 'INCREMENTAL') then
3189
3190 insert /*+ append parallel(bal) */ into PJI_FP_TXN_ACCUM bal
3191 (
3192 TXN_ACCUM_HEADER_ID,
3193 RESOURCE_CLASS_ID,
3194 PROJECT_ID,
3195 PROJECT_ORG_ID,
3196 PROJECT_ORGANIZATION_ID,
3197 PROJECT_TYPE_CLASS,
3198 TASK_ID,
3199 ASSIGNMENT_ID,
3200 NAMED_ROLE,
3201 RECVR_PERIOD_TYPE,
3202 RECVR_PERIOD_ID,
3203 TXN_CURRENCY_CODE,
3204 TXN_RAW_COST,
3205 TXN_BILL_RAW_COST,
3206 TXN_BRDN_COST,
3207 TXN_BILL_BRDN_COST,
3208 TXN_REVENUE,
3209 PRJ_RAW_COST,
3210 PRJ_BILL_RAW_COST,
3211 PRJ_BRDN_COST,
3212 PRJ_BILL_BRDN_COST,
3213 PRJ_REVENUE,
3214 POU_RAW_COST,
3215 POU_BILL_RAW_COST,
3216 POU_BRDN_COST,
3217 POU_BILL_BRDN_COST,
3218 POU_REVENUE,
3219 EOU_RAW_COST,
3220 EOU_BILL_RAW_COST,
3221 EOU_BRDN_COST,
3222 EOU_BILL_BRDN_COST,
3223 G1_RAW_COST,
3224 G1_BILL_RAW_COST,
3225 G1_BRDN_COST,
3226 G1_BILL_BRDN_COST,
3227 G1_REVENUE,
3228 G2_RAW_COST,
3229 G2_BILL_RAW_COST,
3230 G2_BRDN_COST,
3231 G2_BILL_BRDN_COST,
3232 G2_REVENUE,
3233 QUANTITY,
3234 BILL_QUANTITY,
3235 LAST_UPDATE_DATE,
3236 LAST_UPDATED_BY,
3237 CREATION_DATE,
3238 CREATED_BY,
3239 LAST_UPDATE_LOGIN,
3240 CBS_ELEMENT_ID /* Added for CBS Changes */
3241 )
3242 select /*+ ordered
3243 full(tmp6) parallel(tmp6) use_hash(tmp6)
3244 full(hdr) parallel(hdr)
3245 pq_distribute(tmp2, hash, hash) */
3246 hdr.TXN_ACCUM_HEADER_ID,
3247 hdr.RESOURCE_CLASS_ID,
3248 tmp6.PROJECT_ID,
3249 tmp6.PROJECT_ORG_ID,
3250 tmp6.PROJECT_ORGANIZATION_ID,
3251 tmp6.PROJECT_TYPE_CLASS,
3252 tmp6.TASK_ID,
3253 tmp6.ASSIGNMENT_ID,
3254 tmp6.NAMED_ROLE,
3255 tmp6.RECVR_PERIOD_TYPE,
3256 tmp6.RECVR_PERIOD_ID,
3257 tmp6.TXN_CURRENCY_CODE,
3258 sum(tmp6.TXN_RAW_COST) TXN_RAW_COST,
3259 sum(tmp6.TXN_BILL_RAW_COST) TXN_BILL_RAW_COST,
3260 sum(tmp6.TXN_BRDN_COST) TXN_BRDN_COST,
3261 sum(tmp6.TXN_BILL_BRDN_COST) TXN_BILL_BRDN_COST,
3262 sum(tmp6.TXN_REVENUE) TXN_REVENUE,
3263 sum(tmp6.PRJ_RAW_COST) PRJ_RAW_COST,
3264 sum(tmp6.PRJ_BILL_RAW_COST) PRJ_BILL_RAW_COST,
3265 sum(tmp6.PRJ_BRDN_COST) PRJ_BRDN_COST,
3266 sum(tmp6.PRJ_BILL_BRDN_COST) PRJ_BILL_BRDN_COST,
3267 sum(tmp6.PRJ_REVENUE) PRJ_REVENUE,
3268 sum(tmp6.POU_RAW_COST) POU_RAW_COST,
3269 sum(tmp6.POU_BILL_RAW_COST) POU_BILL_RAW_COST,
3270 sum(tmp6.POU_BRDN_COST) POU_BRDN_COST,
3271 sum(tmp6.POU_BILL_BRDN_COST) POU_BILL_BRDN_COST,
3272 sum(tmp6.POU_REVENUE) POU_REVENUE,
3273 sum(tmp6.EOU_RAW_COST) EOU_RAW_COST,
3274 sum(tmp6.EOU_BILL_RAW_COST) EOU_BILL_RAW_COST,
3275 sum(tmp6.EOU_BRDN_COST) EOU_BRDN_COST,
3276 sum(tmp6.EOU_BILL_BRDN_COST) EOU_BILL_BRDN_COST,
3277 sum(tmp6.G1_RAW_COST) G1_RAW_COST,
3278 sum(tmp6.G1_BILL_RAW_COST) G1_BILL_RAW_COST,
3279 sum(tmp6.G1_BRDN_COST) G1_BRDN_COST,
3280 sum(tmp6.G1_BILL_BRDN_COST) G1_BILL_BRDN_COST,
3281 sum(tmp6.G1_REVENUE) G1_REVENUE,
3282 sum(tmp6.G2_RAW_COST) G2_RAW_COST,
3283 sum(tmp6.G2_BILL_RAW_COST) G2_BILL_RAW_COST,
3284 sum(tmp6.G2_BRDN_COST) G2_BRDN_COST,
3285 sum(tmp6.G2_BILL_BRDN_COST) G2_BILL_BRDN_COST,
3286 sum(tmp6.G2_REVENUE) G2_REVENUE,
3287 sum(tmp6.QUANTITY) QUANTITY,
3288 sum(tmp6.BILL_QUANTITY) BILL_QUANTITY,
3289 l_last_update_date LAST_UPDATE_DATE,
3290 l_last_updated_by LAST_UPDATED_BY,
3291 l_creation_date CREATION_DATE,
3292 l_created_by CREATED_BY,
3293 l_last_update_login LAST_UPDATE_LOGIN,
3294 tmp6.CBS_ELEMENT_ID CBS_ELEMENT_ID /* Added for CBS Changes */
3295 from
3296 PJI_FM_AGGR_FIN6 tmp6,
3297 PJI_FP_TXN_ACCUM_HEADER hdr
3298 where
3299 tmp6.WORKER_ID = p_worker_id and
3300 tmp6.RECORD_TYPE = 'A' and
3301 tmp6.PERSON_ID = hdr.PERSON_ID and
3302 tmp6.EXPENDITURE_ORG_ID = hdr.EXPENDITURE_ORG_ID and
3303 tmp6.EXPENDITURE_ORGANIZATION_ID = hdr.EXPENDITURE_ORGANIZATION_ID and
3304 tmp6.RESOURCE_CLASS_ID = hdr.RESOURCE_CLASS_ID and
3305 tmp6.JOB_ID = hdr.JOB_ID and
3306 tmp6.VENDOR_ID = hdr.VENDOR_ID and
3307 tmp6.WORK_TYPE_ID = hdr.WORK_TYPE_ID and
3308 tmp6.EXPENDITURE_CATEGORY_ID = hdr.EXPENDITURE_CATEGORY_ID and
3309 tmp6.EXPENDITURE_TYPE_ID = hdr.EXPENDITURE_TYPE_ID and
3310 tmp6.EVENT_TYPE_ID = hdr.EVENT_TYPE_ID and
3311 tmp6.EXP_EVT_TYPE_ID = hdr.EXP_EVT_TYPE_ID and
3312 tmp6.EXPENDITURE_TYPE = hdr.EXPENDITURE_TYPE and
3313 tmp6.EVENT_TYPE = hdr.EVENT_TYPE and
3314 tmp6.EVENT_TYPE_CLASSIFICATION = hdr.EVENT_TYPE_CLASSIFICATION and
3315 tmp6.EXPENDITURE_CATEGORY = hdr.EXPENDITURE_CATEGORY and
3316 tmp6.REVENUE_CATEGORY = hdr.REVENUE_CATEGORY and
3317 tmp6.NON_LABOR_RESOURCE_ID = hdr.NON_LABOR_RESOURCE_ID and
3318 tmp6.BOM_LABOR_RESOURCE_ID = hdr.BOM_LABOR_RESOURCE_ID and
3319 tmp6.BOM_EQUIPMENT_RESOURCE_ID = hdr.BOM_EQUIPMENT_RESOURCE_ID and
3320 tmp6.ITEM_CATEGORY_ID = hdr.ITEM_CATEGORY_ID and
3321 tmp6.INVENTORY_ITEM_ID = hdr.INVENTORY_ITEM_ID and
3322 tmp6.PROJECT_ROLE_ID = hdr.PROJECT_ROLE_ID and
3323 tmp6.PERSON_TYPE = hdr.PERSON_TYPE and
3324 tmp6.SYSTEM_LINKAGE_FUNCTION = hdr.SYSTEM_LINKAGE_FUNCTION and
3325 tmp6.PROJECT_ID not in (select pjp.PROJECT_ID
3326 from PJI_PJP_PROJ_EXTR_STATUS pjp)
3327 group by
3328 hdr.TXN_ACCUM_HEADER_ID,
3329 hdr.RESOURCE_CLASS_ID,
3330 tmp6.PROJECT_ID,
3331 tmp6.PROJECT_ORG_ID,
3332 tmp6.PROJECT_ORGANIZATION_ID,
3333 tmp6.PROJECT_TYPE_CLASS,
3334 tmp6.TASK_ID,
3335 tmp6.ASSIGNMENT_ID,
3336 tmp6.NAMED_ROLE,
3337 tmp6.RECVR_PERIOD_TYPE,
3338 tmp6.RECVR_PERIOD_ID,
3339 tmp6.TXN_CURRENCY_CODE,
3340 tmp6.CBS_ELEMENT_ID /* Added for CBS Changes */;
3341
3342 /*
3343 end if;
3344
3345 -- OLAP
3346 --PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.BALANCES_INCR_NEW_PRJ(p_worker_id);');
3347
3348 -- End OLAP
3349 commit;
3350
3351
3352 -- OLAP
3353 if (l_extraction_type = 'INCREMENTAL') then
3354
3355 insert into PA_STAGE1_BATCH_OLAP
3356 (
3357 PROJECT_ID
3358 )
3359 select
3360 tmp6.PROJECT_ID
3361 from
3362 PJI_FM_AGGR_FIN6 tmp6,
3363 PJI_FP_TXN_ACCUM_HEADER hdr,
3364 pa_projects_all prj
3365 where
3366 tmp6.WORKER_ID = p_worker_id and
3367 tmp6.RECORD_TYPE = 'A' and
3368 tmp6.PERSON_ID = hdr.PERSON_ID and
3369 tmp6.EXPENDITURE_ORG_ID = hdr.EXPENDITURE_ORG_ID and
3370 tmp6.EXPENDITURE_ORGANIZATION_ID = hdr.EXPENDITURE_ORGANIZATION_ID and
3371 tmp6.RESOURCE_CLASS_ID = hdr.RESOURCE_CLASS_ID and
3372 tmp6.JOB_ID = hdr.JOB_ID and
3373 tmp6.VENDOR_ID = hdr.VENDOR_ID and
3374 tmp6.WORK_TYPE_ID = hdr.WORK_TYPE_ID and
3375 tmp6.EXPENDITURE_CATEGORY_ID = hdr.EXPENDITURE_CATEGORY_ID and
3376 tmp6.EXPENDITURE_TYPE_ID = hdr.EXPENDITURE_TYPE_ID and
3377 tmp6.EVENT_TYPE_ID = hdr.EVENT_TYPE_ID and
3378 tmp6.EXP_EVT_TYPE_ID = hdr.EXP_EVT_TYPE_ID and
3379 tmp6.EXPENDITURE_TYPE = hdr.EXPENDITURE_TYPE and
3380 tmp6.EVENT_TYPE = hdr.EVENT_TYPE and
3381 tmp6.EVENT_TYPE_CLASSIFICATION = hdr.EVENT_TYPE_CLASSIFICATION and
3382 tmp6.EXPENDITURE_CATEGORY = hdr.EXPENDITURE_CATEGORY and
3383 tmp6.REVENUE_CATEGORY = hdr.REVENUE_CATEGORY and
3384 tmp6.NON_LABOR_RESOURCE_ID = hdr.NON_LABOR_RESOURCE_ID and
3385 tmp6.BOM_LABOR_RESOURCE_ID = hdr.BOM_LABOR_RESOURCE_ID and
3386 tmp6.BOM_EQUIPMENT_RESOURCE_ID = hdr.BOM_EQUIPMENT_RESOURCE_ID and
3387 tmp6.ITEM_CATEGORY_ID = hdr.ITEM_CATEGORY_ID and
3388 tmp6.INVENTORY_ITEM_ID = hdr.INVENTORY_ITEM_ID and
3389 tmp6.PROJECT_ROLE_ID = hdr.PROJECT_ROLE_ID and
3390 tmp6.PERSON_TYPE = hdr.PERSON_TYPE and
3391 tmp6.SYSTEM_LINKAGE_FUNCTION = hdr.SYSTEM_LINKAGE_FUNCTION and
3392 tmp6.PROJECT_ID not in (select pjp.PROJECT_ID
3393 from PJI_PJP_PROJ_EXTR_STATUS pjp) and
3394 tmp6.project_id = prj.project_id and
3395 prj.pjt_rollup_enabled_flag = 'Y' and
3396 tmp6.PROJECT_ID not in (select pjp.PROJECT_ID
3397 from PA_STAGE1_BATCH_OLAP pjp);
3398
3399 */ -- COMMENTED TO REVERT OLAP CHANGES FOR 15900841
3400
3401
3402 end if;
3403
3404 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.BALANCES_INCR_NEW_PRJ(p_worker_id);');
3405
3406 commit;
3407
3408 -- End OLAP Change
3409
3410 end BALANCES_INCR_NEW_PRJ;
3411
3412
3413 -- -----------------------------------------------------
3414 -- procedure BALANCES_INSERT_CMT
3415 --
3416 -- History
3417 -- 19-MAR-2004 SVERMETT Created
3418 --
3419 -- Internal PJP Summarization API.
3420 --
3421 -- -----------------------------------------------------
3422 procedure BALANCES_INSERT_CMT (p_worker_id in number) is
3423
3424 l_process varchar2(30);
3425 l_last_update_date date;
3426 l_last_updated_by number;
3427 l_creation_date date;
3428 l_created_by number;
3429 l_last_update_login number;
3430 l_extraction_type varchar2(15);
3431 l_extract_commitments varchar2(30);
3432
3433 begin
3434
3435 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3436
3437 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.BALANCES_INSERT_CMT(p_worker_id);')) then
3438 return;
3439 end if;
3440
3441 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3442
3443 l_extract_commitments := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3444 (PJI_FM_SUM_MAIN.g_process,
3445 'EXTRACT_COMMITMENTS');
3446
3447 l_last_update_date := sysdate;
3448 l_last_updated_by := FND_GLOBAL.USER_ID;
3449 l_creation_date := sysdate;
3450 l_created_by := FND_GLOBAL.USER_ID;
3451 l_last_update_login := FND_GLOBAL.LOGIN_ID;
3452
3453 if ((l_extraction_type = 'FULL' or
3454 l_extraction_type = 'PARTIAL') and
3455 l_extract_commitments = 'Y') then
3456
3457 -- Only insert commitments during FULL run since INCREMENTAL commitments
3458 -- data is handled at the same time as INCREMETNAL actuals data above.
3459
3460 insert /*+ append parallel(bal) */ into PJI_FP_TXN_ACCUM1 bal
3461 (
3462 TXN_ACCUM_HEADER_ID,
3463 PROJECT_ID,
3464 PROJECT_ORG_ID,
3465 PROJECT_ORGANIZATION_ID,
3466 TASK_ID,
3467 RECVR_PERIOD_TYPE,
3468 RECVR_PERIOD_ID,
3469 TXN_CURRENCY_CODE,
3470 TXN_SUP_INV_COMMITTED_COST,
3471 TXN_PO_COMMITTED_COST,
3472 TXN_PR_COMMITTED_COST,
3473 TXN_OTH_COMMITTED_COST,
3474 PRJ_SUP_INV_COMMITTED_COST,
3475 PRJ_PO_COMMITTED_COST,
3476 PRJ_PR_COMMITTED_COST,
3477 PRJ_OTH_COMMITTED_COST,
3478 POU_SUP_INV_COMMITTED_COST,
3479 POU_PO_COMMITTED_COST,
3480 POU_PR_COMMITTED_COST,
3481 POU_OTH_COMMITTED_COST,
3482 EOU_SUP_INV_COMMITTED_COST,
3483 EOU_PO_COMMITTED_COST,
3484 EOU_PR_COMMITTED_COST,
3485 EOU_OTH_COMMITTED_COST,
3486 G1_SUP_INV_COMMITTED_COST,
3487 G1_PO_COMMITTED_COST,
3488 G1_PR_COMMITTED_COST,
3489 G1_OTH_COMMITTED_COST,
3490 G2_SUP_INV_COMMITTED_COST,
3491 G2_PO_COMMITTED_COST,
3492 G2_PR_COMMITTED_COST,
3493 G2_OTH_COMMITTED_COST,
3494 LAST_UPDATE_DATE,
3495 LAST_UPDATED_BY,
3496 CREATION_DATE,
3497 CREATED_BY,
3498 LAST_UPDATE_LOGIN,
3499 CBS_ELEMENT_ID /* Added for CBS Changes */
3500 )
3501 select /*+ ordered
3502 full(tmp6) parallel(tmp6) use_hash(tmp6)
3503 full(hdr) parallel(hdr)
3504 pq_distribute(tmp2, hash, hash) */
3505 hdr.TXN_ACCUM_HEADER_ID,
3506 tmp6.PROJECT_ID,
3507 tmp6.PROJECT_ORG_ID,
3508 tmp6.PROJECT_ORGANIZATION_ID,
3509 tmp6.TASK_ID,
3510 tmp6.RECVR_PERIOD_TYPE,
3511 tmp6.RECVR_PERIOD_ID,
3512 tmp6.TXN_CURRENCY_CODE,
3513 sum(tmp6.TXN_SUP_INV_COMMITTED_COST) TXN_SUP_INV_COMMITTED_COST,
3514 sum(tmp6.TXN_PO_COMMITTED_COST) TXN_PO_COMMITTED_COST,
3515 sum(tmp6.TXN_PR_COMMITTED_COST) TXN_PR_COMMITTED_COST,
3516 sum(tmp6.TXN_OTH_COMMITTED_COST) TXN_OTH_COMMITTED_COST,
3517 sum(tmp6.PRJ_SUP_INV_COMMITTED_COST) PRJ_SUP_INV_COMMITTED_COST,
3518 sum(tmp6.PRJ_PO_COMMITTED_COST) PRJ_PO_COMMITTED_COST,
3519 sum(tmp6.PRJ_PR_COMMITTED_COST) PRJ_PR_COMMITTED_COST,
3520 sum(tmp6.PRJ_OTH_COMMITTED_COST) PRJ_OTH_COMMITTED_COST,
3521 sum(tmp6.POU_SUP_INV_COMMITTED_COST) POU_SUP_INV_COMMITTED_COST,
3522 sum(tmp6.POU_PO_COMMITTED_COST) POU_PO_COMMITTED_COST,
3523 sum(tmp6.POU_PR_COMMITTED_COST) POU_PR_COMMITTED_COST,
3524 sum(tmp6.POU_OTH_COMMITTED_COST) POU_OTH_COMMITTED_COST,
3525 sum(tmp6.EOU_SUP_INV_COMMITTED_COST) EOU_SUP_INV_COMMITTED_COST,
3526 sum(tmp6.EOU_PO_COMMITTED_COST) EOU_PO_COMMITTED_COST,
3527 sum(tmp6.EOU_PR_COMMITTED_COST) EOU_PR_COMMITTED_COST,
3528 sum(tmp6.EOU_OTH_COMMITTED_COST) EOU_OTH_COMMITTED_COST,
3529 sum(tmp6.G1_SUP_INV_COMMITTED_COST) G1_SUP_INV_COMMITTED_COST,
3530 sum(tmp6.G1_PO_COMMITTED_COST) G1_PO_COMMITTED_COST,
3531 sum(tmp6.G1_PR_COMMITTED_COST) G1_PR_COMMITTED_COST,
3532 sum(tmp6.G1_OTH_COMMITTED_COST) G1_OTH_COMMITTED_COST,
3533 sum(tmp6.G2_SUP_INV_COMMITTED_COST) G2_SUP_INV_COMMITTED_COST,
3534 sum(tmp6.G2_PO_COMMITTED_COST) G2_PO_COMMITTED_COST,
3535 sum(tmp6.G2_PR_COMMITTED_COST) G2_PR_COMMITTED_COST,
3536 sum(tmp6.G2_OTH_COMMITTED_COST) G2_OTH_COMMITTED_COST,
3537 l_last_update_date LAST_UPDATE_DATE,
3538 l_last_updated_by LAST_UPDATED_BY,
3539 l_creation_date CREATION_DATE,
3540 l_created_by CREATED_BY,
3541 l_last_update_login LAST_UPDATE_LOGIN,
3542 tmp6.CBS_ELEMENT_ID CBS_ELEMENT_ID /* Added for CBS Changes */
3543 from
3544 PJI_FM_AGGR_FIN6 tmp6,
3545 PJI_FP_TXN_ACCUM_HEADER hdr
3546 where
3547 tmp6.WORKER_ID = p_worker_id and
3548 tmp6.RECORD_TYPE = 'M' and
3549 tmp6.PERSON_ID = hdr.PERSON_ID and
3550 tmp6.EXPENDITURE_ORG_ID = hdr.EXPENDITURE_ORG_ID and
3551 tmp6.EXPENDITURE_ORGANIZATION_ID = hdr.EXPENDITURE_ORGANIZATION_ID and
3552 tmp6.RESOURCE_CLASS_ID = hdr.RESOURCE_CLASS_ID and
3553 tmp6.JOB_ID = hdr.JOB_ID and
3554 tmp6.VENDOR_ID = hdr.VENDOR_ID and
3555 tmp6.WORK_TYPE_ID = hdr.WORK_TYPE_ID and
3556 tmp6.EXPENDITURE_CATEGORY_ID = hdr.EXPENDITURE_CATEGORY_ID and
3557 tmp6.EXPENDITURE_TYPE_ID = hdr.EXPENDITURE_TYPE_ID and
3558 tmp6.EVENT_TYPE_ID = hdr.EVENT_TYPE_ID and
3559 tmp6.EXP_EVT_TYPE_ID = hdr.EXP_EVT_TYPE_ID and
3560 tmp6.EXPENDITURE_TYPE = hdr.EXPENDITURE_TYPE and
3561 tmp6.EVENT_TYPE = hdr.EVENT_TYPE and
3562 tmp6.EVENT_TYPE_CLASSIFICATION = hdr.EVENT_TYPE_CLASSIFICATION and
3563 tmp6.EXPENDITURE_CATEGORY = hdr.EXPENDITURE_CATEGORY and
3564 tmp6.REVENUE_CATEGORY = hdr.REVENUE_CATEGORY and
3565 tmp6.NON_LABOR_RESOURCE_ID = hdr.NON_LABOR_RESOURCE_ID and
3566 tmp6.BOM_LABOR_RESOURCE_ID = hdr.BOM_LABOR_RESOURCE_ID and
3567 tmp6.BOM_EQUIPMENT_RESOURCE_ID = hdr.BOM_EQUIPMENT_RESOURCE_ID and
3568 tmp6.ITEM_CATEGORY_ID = hdr.ITEM_CATEGORY_ID and
3569 tmp6.INVENTORY_ITEM_ID = hdr.INVENTORY_ITEM_ID and
3570 tmp6.PROJECT_ROLE_ID = hdr.PROJECT_ROLE_ID and
3571 tmp6.NAMED_ROLE = hdr.NAMED_ROLE and
3572 tmp6.PERSON_TYPE = hdr.PERSON_TYPE and
3573 tmp6.SYSTEM_LINKAGE_FUNCTION = hdr.SYSTEM_LINKAGE_FUNCTION
3574 group by
3575 hdr.TXN_ACCUM_HEADER_ID,
3576 tmp6.PROJECT_ID,
3577 tmp6.PROJECT_ORG_ID,
3578 tmp6.PROJECT_ORGANIZATION_ID,
3579 tmp6.TASK_ID,
3580 tmp6.RECVR_PERIOD_TYPE,
3581 tmp6.RECVR_PERIOD_ID,
3582 tmp6.TXN_CURRENCY_CODE,
3583 tmp6.CBS_ELEMENT_ID /* Added for CBS Changes */;
3584
3585 end if;
3586
3587 -- OLAP
3588 --PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.BALANCES_INSERT_CMT(p_worker_id);');
3589 -- End OLAP
3590
3591 commit;
3592 /*
3593 -- OLAP
3594
3595 if ((l_extraction_type = 'FULL' or
3596 l_extraction_type = 'PARTIAL') and
3597 l_extract_commitments = 'Y') then
3598
3599 -- Only insert commitments during FULL run since INCREMENTAL commitments
3600 -- data is handled at the same time as INCREMETNAL actuals data above.
3601
3602 insert into PA_STAGE1_BATCH_OLAP
3603 (
3604 PROJECT_ID
3605 )
3606 select
3607 tmp6.PROJECT_ID
3608 from
3609 PJI_FM_AGGR_FIN6 tmp6,
3610 PJI_FP_TXN_ACCUM_HEADER hdr,
3611 pa_projects_all prj
3612 where
3613 tmp6.WORKER_ID = p_worker_id and
3614 tmp6.RECORD_TYPE = 'M' and
3615 tmp6.PERSON_ID = hdr.PERSON_ID and
3616 tmp6.EXPENDITURE_ORG_ID = hdr.EXPENDITURE_ORG_ID and
3617 tmp6.EXPENDITURE_ORGANIZATION_ID = hdr.EXPENDITURE_ORGANIZATION_ID and
3618 tmp6.RESOURCE_CLASS_ID = hdr.RESOURCE_CLASS_ID and
3619 tmp6.JOB_ID = hdr.JOB_ID and
3620 tmp6.VENDOR_ID = hdr.VENDOR_ID and
3621 tmp6.WORK_TYPE_ID = hdr.WORK_TYPE_ID and
3622 tmp6.EXPENDITURE_CATEGORY_ID = hdr.EXPENDITURE_CATEGORY_ID and
3623 tmp6.EXPENDITURE_TYPE_ID = hdr.EXPENDITURE_TYPE_ID and
3624 tmp6.EVENT_TYPE_ID = hdr.EVENT_TYPE_ID and
3625 tmp6.EXP_EVT_TYPE_ID = hdr.EXP_EVT_TYPE_ID and
3626 tmp6.EXPENDITURE_TYPE = hdr.EXPENDITURE_TYPE and
3627 tmp6.EVENT_TYPE = hdr.EVENT_TYPE and
3628 tmp6.EVENT_TYPE_CLASSIFICATION = hdr.EVENT_TYPE_CLASSIFICATION and
3629 tmp6.EXPENDITURE_CATEGORY = hdr.EXPENDITURE_CATEGORY and
3630 tmp6.REVENUE_CATEGORY = hdr.REVENUE_CATEGORY and
3631 tmp6.NON_LABOR_RESOURCE_ID = hdr.NON_LABOR_RESOURCE_ID and
3632 tmp6.BOM_LABOR_RESOURCE_ID = hdr.BOM_LABOR_RESOURCE_ID and
3633 tmp6.BOM_EQUIPMENT_RESOURCE_ID = hdr.BOM_EQUIPMENT_RESOURCE_ID and
3634 tmp6.ITEM_CATEGORY_ID = hdr.ITEM_CATEGORY_ID and
3635 tmp6.INVENTORY_ITEM_ID = hdr.INVENTORY_ITEM_ID and
3636 tmp6.PROJECT_ROLE_ID = hdr.PROJECT_ROLE_ID and
3637 tmp6.NAMED_ROLE = hdr.NAMED_ROLE and
3638 tmp6.PERSON_TYPE = hdr.PERSON_TYPE and
3639 tmp6.SYSTEM_LINKAGE_FUNCTION = hdr.SYSTEM_LINKAGE_FUNCTION and
3640 tmp6.project_id = prj.project_id and
3641 prj.pjt_rollup_enabled_flag = 'Y' and
3642 tmp6.PROJECT_ID not in (select pjp.PROJECT_ID
3643 from PA_STAGE1_BATCH_OLAP pjp);
3644
3645
3646
3647 end if;
3648
3649 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.BALANCES_INSERT_CMT(p_worker_id);');
3650 commit;
3651
3652 -- End OLAP
3653 */ -- COMMENTED TO REVERT OLAP CHANGES FOR 15900841
3654
3655 end BALANCES_INSERT_CMT;
3656
3657
3658 -- -----------------------------------------------------
3659 -- procedure BALANCES_INCR_NEW_PRJ_CMT
3660 --
3661 -- History
3662 -- 19-MAR-2004 SVERMETT Created
3663 --
3664 -- Internal PJP Summarization API.
3665 --
3666 -- -----------------------------------------------------
3667 procedure BALANCES_INCR_NEW_PRJ_CMT (p_worker_id in number) is
3668
3669 l_process varchar2(30);
3670 l_last_update_date date;
3671 l_last_updated_by number;
3672 l_creation_date date;
3673 l_created_by number;
3674 l_last_update_login number;
3675 l_extraction_type varchar2(15);
3676 l_extract_commitments varchar2(30);
3677
3678 begin
3679
3680 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3681
3682 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.BALANCES_INCR_NEW_PRJ_CMT(p_worker_id);')) then
3683 return;
3684 end if;
3685
3686 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3687
3688 l_extract_commitments := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3689 (PJI_FM_SUM_MAIN.g_process,
3690 'EXTRACT_COMMITMENTS');
3691
3692 l_last_update_date := sysdate;
3693 l_last_updated_by := FND_GLOBAL.USER_ID;
3694 l_creation_date := sysdate;
3695 l_created_by := FND_GLOBAL.USER_ID;
3696 l_last_update_login := FND_GLOBAL.LOGIN_ID;
3697
3698 if (l_extraction_type = 'INCREMENTAL' and l_extract_commitments = 'Y') then
3699
3700 insert /*+ append parallel(bal) */ into PJI_FP_TXN_ACCUM1 bal
3701 (
3702 TXN_ACCUM_HEADER_ID,
3703 PROJECT_ID,
3704 PROJECT_ORG_ID,
3705 PROJECT_ORGANIZATION_ID,
3706 TASK_ID,
3707 RECVR_PERIOD_TYPE,
3708 RECVR_PERIOD_ID,
3709 TXN_CURRENCY_CODE,
3710 TXN_SUP_INV_COMMITTED_COST,
3711 TXN_PO_COMMITTED_COST,
3712 TXN_PR_COMMITTED_COST,
3713 TXN_OTH_COMMITTED_COST,
3714 PRJ_SUP_INV_COMMITTED_COST,
3715 PRJ_PO_COMMITTED_COST,
3716 PRJ_PR_COMMITTED_COST,
3717 PRJ_OTH_COMMITTED_COST,
3718 POU_SUP_INV_COMMITTED_COST,
3719 POU_PO_COMMITTED_COST,
3720 POU_PR_COMMITTED_COST,
3721 POU_OTH_COMMITTED_COST,
3722 EOU_SUP_INV_COMMITTED_COST,
3723 EOU_PO_COMMITTED_COST,
3724 EOU_PR_COMMITTED_COST,
3725 EOU_OTH_COMMITTED_COST,
3726 G1_SUP_INV_COMMITTED_COST,
3727 G1_PO_COMMITTED_COST,
3728 G1_PR_COMMITTED_COST,
3729 G1_OTH_COMMITTED_COST,
3730 G2_SUP_INV_COMMITTED_COST,
3731 G2_PO_COMMITTED_COST,
3732 G2_PR_COMMITTED_COST,
3733 G2_OTH_COMMITTED_COST,
3734 LAST_UPDATE_DATE,
3735 LAST_UPDATED_BY,
3736 CREATION_DATE,
3737 CREATED_BY,
3738 LAST_UPDATE_LOGIN,
3739 CBS_ELEMENT_ID /* Added for CBS Changes */
3740 )
3741 select /*+ ordered
3742 full(tmp6) parallel(tmp6) use_hash(tmp6)
3743 full(hdr) parallel(hdr)
3744 pq_distribute(tmp2, hash, hash) */
3745 hdr.TXN_ACCUM_HEADER_ID,
3746 tmp6.PROJECT_ID,
3747 tmp6.PROJECT_ORG_ID,
3748 tmp6.PROJECT_ORGANIZATION_ID,
3749 tmp6.TASK_ID,
3750 tmp6.RECVR_PERIOD_TYPE,
3751 tmp6.RECVR_PERIOD_ID,
3752 tmp6.TXN_CURRENCY_CODE,
3753 sum(tmp6.TXN_SUP_INV_COMMITTED_COST) TXN_SUP_INV_COMMITTED_COST,
3754 sum(tmp6.TXN_PO_COMMITTED_COST) TXN_PO_COMMITTED_COST,
3755 sum(tmp6.TXN_PR_COMMITTED_COST) TXN_PR_COMMITTED_COST,
3756 sum(tmp6.TXN_OTH_COMMITTED_COST) TXN_OTH_COMMITTED_COST,
3757 sum(tmp6.PRJ_SUP_INV_COMMITTED_COST) PRJ_SUP_INV_COMMITTED_COST,
3758 sum(tmp6.PRJ_PO_COMMITTED_COST) PRJ_PO_COMMITTED_COST,
3759 sum(tmp6.PRJ_PR_COMMITTED_COST) PRJ_PR_COMMITTED_COST,
3760 sum(tmp6.PRJ_OTH_COMMITTED_COST) PRJ_OTH_COMMITTED_COST,
3761 sum(tmp6.POU_SUP_INV_COMMITTED_COST) POU_SUP_INV_COMMITTED_COST,
3762 sum(tmp6.POU_PO_COMMITTED_COST) POU_PO_COMMITTED_COST,
3763 sum(tmp6.POU_PR_COMMITTED_COST) POU_PR_COMMITTED_COST,
3764 sum(tmp6.POU_OTH_COMMITTED_COST) POU_OTH_COMMITTED_COST,
3765 sum(tmp6.EOU_SUP_INV_COMMITTED_COST) EOU_SUP_INV_COMMITTED_COST,
3766 sum(tmp6.EOU_PO_COMMITTED_COST) EOU_PO_COMMITTED_COST,
3767 sum(tmp6.EOU_PR_COMMITTED_COST) EOU_PR_COMMITTED_COST,
3768 sum(tmp6.EOU_OTH_COMMITTED_COST) EOU_OTH_COMMITTED_COST,
3769 sum(tmp6.G1_SUP_INV_COMMITTED_COST) G1_SUP_INV_COMMITTED_COST,
3770 sum(tmp6.G1_PO_COMMITTED_COST) G1_PO_COMMITTED_COST,
3771 sum(tmp6.G1_PR_COMMITTED_COST) G1_PR_COMMITTED_COST,
3772 sum(tmp6.G1_OTH_COMMITTED_COST) G1_OTH_COMMITTED_COST,
3773 sum(tmp6.G2_SUP_INV_COMMITTED_COST) G2_SUP_INV_COMMITTED_COST,
3774 sum(tmp6.G2_PO_COMMITTED_COST) G2_PO_COMMITTED_COST,
3775 sum(tmp6.G2_PR_COMMITTED_COST) G2_PR_COMMITTED_COST,
3776 sum(tmp6.G2_OTH_COMMITTED_COST) G2_OTH_COMMITTED_COST,
3777 l_last_update_date LAST_UPDATE_DATE,
3778 l_last_updated_by LAST_UPDATED_BY,
3779 l_creation_date CREATION_DATE,
3780 l_created_by CREATED_BY,
3781 l_last_update_login LAST_UPDATE_LOGIN,
3782 tmp6.CBS_ELEMENT_ID CBS_ELEMENT_ID /* Added for CBS Changes */
3783 from
3784 PJI_FM_AGGR_FIN6 tmp6,
3785 PJI_FP_TXN_ACCUM_HEADER hdr
3786 where
3787 tmp6.WORKER_ID = p_worker_id and
3788 tmp6.RECORD_TYPE = 'M' and
3789 tmp6.PERSON_ID = hdr.PERSON_ID and
3790 tmp6.EXPENDITURE_ORG_ID = hdr.EXPENDITURE_ORG_ID and
3791 tmp6.EXPENDITURE_ORGANIZATION_ID = hdr.EXPENDITURE_ORGANIZATION_ID and
3792 tmp6.RESOURCE_CLASS_ID = hdr.RESOURCE_CLASS_ID and
3793 tmp6.JOB_ID = hdr.JOB_ID and
3794 tmp6.VENDOR_ID = hdr.VENDOR_ID and
3795 tmp6.WORK_TYPE_ID = hdr.WORK_TYPE_ID and
3796 tmp6.EXPENDITURE_CATEGORY_ID = hdr.EXPENDITURE_CATEGORY_ID and
3797 tmp6.EXPENDITURE_TYPE_ID = hdr.EXPENDITURE_TYPE_ID and
3798 tmp6.EVENT_TYPE_ID = hdr.EVENT_TYPE_ID and
3799 tmp6.EXP_EVT_TYPE_ID = hdr.EXP_EVT_TYPE_ID and
3800 tmp6.EXPENDITURE_TYPE = hdr.EXPENDITURE_TYPE and
3801 tmp6.EVENT_TYPE = hdr.EVENT_TYPE and
3802 tmp6.EVENT_TYPE_CLASSIFICATION = hdr.EVENT_TYPE_CLASSIFICATION and
3803 tmp6.EXPENDITURE_CATEGORY = hdr.EXPENDITURE_CATEGORY and
3804 tmp6.REVENUE_CATEGORY = hdr.REVENUE_CATEGORY and
3805 tmp6.NON_LABOR_RESOURCE_ID = hdr.NON_LABOR_RESOURCE_ID and
3806 tmp6.BOM_LABOR_RESOURCE_ID = hdr.BOM_LABOR_RESOURCE_ID and
3807 tmp6.BOM_EQUIPMENT_RESOURCE_ID = hdr.BOM_EQUIPMENT_RESOURCE_ID and
3808 tmp6.ITEM_CATEGORY_ID = hdr.ITEM_CATEGORY_ID and
3809 tmp6.INVENTORY_ITEM_ID = hdr.INVENTORY_ITEM_ID and
3810 tmp6.PROJECT_ROLE_ID = hdr.PROJECT_ROLE_ID and
3811 tmp6.NAMED_ROLE = hdr.NAMED_ROLE and
3812 tmp6.PERSON_TYPE = hdr.PERSON_TYPE and
3813 tmp6.SYSTEM_LINKAGE_FUNCTION = hdr.SYSTEM_LINKAGE_FUNCTION and
3814 tmp6.PROJECT_ID not in (select pjp.PROJECT_ID
3815 from PJI_PJP_PROJ_EXTR_STATUS pjp)
3816 group by
3817 hdr.TXN_ACCUM_HEADER_ID,
3818 tmp6.PROJECT_ID,
3819 tmp6.PROJECT_ORG_ID,
3820 tmp6.PROJECT_ORGANIZATION_ID,
3821 tmp6.TASK_ID,
3822 tmp6.RECVR_PERIOD_TYPE,
3823 tmp6.RECVR_PERIOD_ID,
3824 tmp6.TXN_CURRENCY_CODE,
3825 tmp6.CBS_ELEMENT_ID /* Added for CBS Changes */;
3826 end if;
3827 /*
3828 -- OLAP
3829 --PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.BALANCES_INCR_NEW_PRJ_CMT(p_worker_id);');
3830 -- End OLAP
3831
3832 commit;
3833
3834 -- OLAP
3835 if (l_extraction_type = 'INCREMENTAL' and l_extract_commitments = 'Y') then
3836
3837 insert into PA_STAGE1_BATCH_OLAP
3838 (
3839 PROJECT_ID
3840 )
3841 select
3842 tmp6.PROJECT_ID
3843 from
3844 PJI_FM_AGGR_FIN6 tmp6,
3845 PJI_FP_TXN_ACCUM_HEADER hdr,
3846 pa_projects_all prj
3847 where
3848 tmp6.WORKER_ID = p_worker_id and
3849 tmp6.RECORD_TYPE = 'M' and
3850 tmp6.PERSON_ID = hdr.PERSON_ID and
3851 tmp6.EXPENDITURE_ORG_ID = hdr.EXPENDITURE_ORG_ID and
3852 tmp6.EXPENDITURE_ORGANIZATION_ID = hdr.EXPENDITURE_ORGANIZATION_ID and
3853 tmp6.RESOURCE_CLASS_ID = hdr.RESOURCE_CLASS_ID and
3854 tmp6.JOB_ID = hdr.JOB_ID and
3855 tmp6.VENDOR_ID = hdr.VENDOR_ID and
3856 tmp6.WORK_TYPE_ID = hdr.WORK_TYPE_ID and
3857 tmp6.EXPENDITURE_CATEGORY_ID = hdr.EXPENDITURE_CATEGORY_ID and
3858 tmp6.EXPENDITURE_TYPE_ID = hdr.EXPENDITURE_TYPE_ID and
3859 tmp6.EVENT_TYPE_ID = hdr.EVENT_TYPE_ID and
3860 tmp6.EXP_EVT_TYPE_ID = hdr.EXP_EVT_TYPE_ID and
3861 tmp6.EXPENDITURE_TYPE = hdr.EXPENDITURE_TYPE and
3862 tmp6.EVENT_TYPE = hdr.EVENT_TYPE and
3863 tmp6.EVENT_TYPE_CLASSIFICATION = hdr.EVENT_TYPE_CLASSIFICATION and
3864 tmp6.EXPENDITURE_CATEGORY = hdr.EXPENDITURE_CATEGORY and
3865 tmp6.REVENUE_CATEGORY = hdr.REVENUE_CATEGORY and
3866 tmp6.NON_LABOR_RESOURCE_ID = hdr.NON_LABOR_RESOURCE_ID and
3867 tmp6.BOM_LABOR_RESOURCE_ID = hdr.BOM_LABOR_RESOURCE_ID and
3868 tmp6.BOM_EQUIPMENT_RESOURCE_ID = hdr.BOM_EQUIPMENT_RESOURCE_ID and
3869 tmp6.ITEM_CATEGORY_ID = hdr.ITEM_CATEGORY_ID and
3870 tmp6.INVENTORY_ITEM_ID = hdr.INVENTORY_ITEM_ID and
3871 tmp6.PROJECT_ROLE_ID = hdr.PROJECT_ROLE_ID and
3872 tmp6.NAMED_ROLE = hdr.NAMED_ROLE and
3873 tmp6.PERSON_TYPE = hdr.PERSON_TYPE and
3874 tmp6.SYSTEM_LINKAGE_FUNCTION = hdr.SYSTEM_LINKAGE_FUNCTION and
3875 tmp6.PROJECT_ID not in (select pjp.PROJECT_ID
3876 from PJI_PJP_PROJ_EXTR_STATUS pjp) and
3877 tmp6.project_id = prj.project_id and
3878 prj.pjt_rollup_enabled_flag = 'Y' and
3879 tmp6.PROJECT_ID not in (select pjp.PROJECT_ID
3880 from PA_STAGE1_BATCH_OLAP pjp);
3881
3882
3883
3884 end if;
3885 */
3886 -- COMMENTED TO REVERT OLAP CHANGES FOR 15900841
3887 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.BALANCES_INCR_NEW_PRJ_CMT(p_worker_id);');
3888
3889
3890 commit;
3891
3892 -- End OLAP
3893
3894 end BALANCES_INCR_NEW_PRJ_CMT;
3895
3896
3897 -- -----------------------------------------------------
3898 -- procedure FORCE_SUBSEQUENT_RUN
3899 --
3900 -- History
3901 -- 19-MAR-2004 SVERMETT Created
3902 --
3903 -- Internal PJP Summarization API.
3904 --
3905 -- -----------------------------------------------------
3906 procedure FORCE_SUBSEQUENT_RUN (p_worker_id in number) is
3907
3908 l_worker_id number;
3909 l_process varchar2(30);
3910 l_extraction_type varchar2(15);
3911
3912 l_newline varchar2(10) := '
3913 ';
3914 l_no_selection varchar2(50);
3915
3916 l_from_project_tg varchar2(40);
3917 l_to_project_tg varchar2(40);
3918 l_plan_type_tg varchar2(40);
3919
3920 l_from_project_id number;
3921 l_to_project_id number;
3922 l_plan_type_id number;
3923
3924 l_from_project varchar2(50);
3925 l_to_project varchar2(50);
3926 l_plan_type varchar2(200);
3927
3928 l_from_project_num varchar2(50);
3929 l_to_project_num varchar2(50);
3930
3931 l_operating_unit number := null;
3932 l_project_operating_unit_tg varchar2(40);
3933 l_project_operating_unit_name varchar2(240);
3934 l_print_rpt_flag varchar2(1) :='Y';
3935 begin
3936 --Commenting out this procedure for Bug 8365073. Please check bug for details
3937 /*
3938 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3939
3940 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.FORCE_SUBSEQUENT_RUN(p_worker_id);')) then
3941 return;
3942 end if;
3943
3944 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3945
3946 if (l_extraction_type = 'PARTIAL') then
3947 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_NO_SELECTION');
3948
3949 l_no_selection := FND_MESSAGE.GET;
3950
3951 l_operating_unit := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3952 (PJI_FM_SUM_MAIN.g_process, 'PROJECT_OPERATING_UNIT');
3953
3954 if (nvl(l_operating_unit, -1) = -1) then
3955 l_project_operating_unit_name := l_no_selection;
3956 else
3957 select NAME
3958 into l_project_operating_unit_name
3959 from HR_OPERATING_UNITS
3960 where ORGANIZATION_ID = l_operating_unit;
3961 end if;
3962 */
3963
3964 /* 4604355 l_from_project_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3965 (PJI_FM_SUM_MAIN.g_process, 'FROM_PROJECT_ID');
3966
3967 if (nvl(l_from_project_id, -1) = -1) then
3968
3969 l_from_project := l_no_selection;
3970
3971 else
3972
3973 select SEGMENT1
3974 into l_from_project
3975 from PA_PROJECTS_ALL
3976 where PROJECT_ID = l_from_project_id;
3977
3978 end if;
3979 4604355 *//*
3980 l_from_project_num := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3981 (PJI_FM_SUM_MAIN.g_process, 'FROM_PROJECT');
3982
3983 if (nvl(l_from_project_num,'PJI$NULL') = 'PJI$NULL') then
3984
3985 l_from_project := l_no_selection;
3986 else
3987
3988 l_from_project := l_from_project_num;
3989 end if;*/
3990
3991 /*4604355 l_to_project_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3992 (PJI_FM_SUM_MAIN.g_process, 'TO_PROJECT_ID');
3993
3994 if (nvl(l_to_project_id, -1) = -1) then
3995
3996 l_to_project := l_no_selection;
3997
3998 else
3999
4000 select SEGMENT1
4001 into l_to_project
4002 from PA_PROJECTS_ALL
4003 where PROJECT_ID = l_to_project_id;
4004
4005 end if;
4006 4604355 */
4007 /*
4008 l_to_project_num := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
4009 (PJI_FM_SUM_MAIN.g_process, 'TO_PROJECT');
4010
4011 if (nvl(l_to_project_num, 'PJI$NULL') = 'PJI$NULL') then
4012
4013 l_to_project := l_no_selection;
4014 else
4015 l_to_project := l_to_project_num;
4016
4017 end if;
4018
4019 l_plan_type_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
4020 (PJI_FM_SUM_MAIN.g_process, 'PLAN_TYPE_ID');
4021
4022 if (nvl(l_plan_type_id, -1) = -1) then
4023
4024 l_plan_type := l_no_selection;
4025
4026 else
4027
4028 select NAME
4029 into l_plan_type
4030 from PA_FIN_PLAN_TYPES_VL
4031 where FIN_PLAN_TYPE_ID = l_plan_type_id;
4032
4033 end if;
4034
4035 commit;
4036
4037 BEGIN
4038 PJI_PJP_SUM_MAIN.INIT_PROCESS(l_worker_id,
4039 'P',
4040 l_operating_unit,
4041 null,
4042 null,
4043 l_from_project_num,
4044 l_to_project_num,
4045 l_plan_type_id,
4046 null,
4047 null,
4048 null,
4049 null);
4050
4051 EXCEPTION
4052 when others then
4053 IF SQLCODE = -20041 then
4054 l_print_rpt_flag:='N';
4055 else
4056 raise;
4057 end if;
4058 END;
4059 end if;
4060
4061 if (l_extraction_type = 'PARTIAL' and l_print_rpt_flag ='Y') then
4062
4063 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_FORCE_PRTL');
4064
4065 PJI_UTILS.WRITE2OUT(l_newline ||
4066 l_newline ||
4067 FND_MESSAGE.GET ||
4068 l_newline ||
4069 l_newline);
4070
4071 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_PRJ_OP_UNIT');
4072
4073 l_project_operating_unit_tg := substr(FND_MESSAGE.GET, 1, 30);
4074
4075 PJI_UTILS.WRITE2OUT(l_project_operating_unit_tg ||
4076 PJI_FM_SUM_MAIN.my_pad(30 - length(l_project_operating_unit_tg),
4077 ' ') ||
4078 ': ' ||
4079 l_project_operating_unit_name ||
4080 l_newline);
4081
4082
4083 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_FROM_PRJ');
4084
4085 l_from_project_tg := substr(FND_MESSAGE.GET, 1, 30);
4086
4087 PJI_UTILS.WRITE2OUT(l_from_project_tg ||
4088 PJI_FM_SUM_MAIN.my_pad(30-length(l_from_project_tg),
4089 ' ') ||
4090 ': ' ||
4091 l_from_project ||
4092 l_newline);
4093
4094
4095 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_TO_PRJ');
4096
4097 l_to_project_tg := substr(FND_MESSAGE.GET, 1, 30);
4098
4099 PJI_UTILS.WRITE2OUT(l_to_project_tg ||
4100 PJI_FM_SUM_MAIN.my_pad(30 - length(l_to_project_tg),
4101 ' ') ||
4102 ': ' ||
4103 l_to_project ||
4104 l_newline);
4105
4106
4107 FND_MESSAGE.SET_NAME('PJI', 'PJI_PJP_SUM_PLAN_TYPE');
4108
4109 l_plan_type_tg := substr(FND_MESSAGE.GET, 1, 30);
4110
4111 PJI_UTILS.WRITE2OUT(l_plan_type_tg ||
4112 PJI_FM_SUM_MAIN.my_pad(30 - length(l_plan_type_tg),
4113 ' ') ||
4114 ': ' ||
4115 l_plan_type ||
4116 l_newline);
4117
4118 end if;
4119 */
4120
4121 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.FORCE_SUBSEQUENT_RUN(p_worker_id);');
4122
4123 commit;
4124
4125 end FORCE_SUBSEQUENT_RUN;
4126
4127
4128 -- -----------------------------------------------------
4129 -- procedure BALANCES_ROWID_TABLE
4130 --
4131 --
4132 -- NOTE: This API is called from stage 3 summarization.
4133 --
4134 --
4135 -- History
4136 -- 19-MAR-2004 SVERMETT Created
4137 --
4138 -- Internal PJP Summarization API.
4139 --
4140 -- -----------------------------------------------------
4141 procedure BALANCES_ROWID_TABLE (p_worker_id in number) is
4142
4143 l_process varchar2(30);
4144 l_extraction_type varchar2(15);
4145
4146 begin
4147
4148 l_process := PJI_PJP_SUM_MAIN.g_process || p_worker_id;
4149
4150 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.BALANCES_ROWID_TABLE(p_worker_id);')) then
4151 return;
4152 end if;
4153
4154 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
4155
4156 if (l_extraction_type = 'INCREMENTAL') then
4157
4158 -- Actuals
4159 insert into PJI_PJP_RMAP_FPR psi_i
4160 (
4161 WORKER_ID,
4162 STG_ROWID,
4163 TXN_ROWID,
4164 RECORD_TYPE
4165 )
4166 select /* ordered */
4167 distinct
4168 p_worker_id WORKER_ID,
4169 tmp7.ROWID STG_ROWID,
4170 psi.ROWID TXN_ROWID,
4171 tmp7.RECORD_TYPE
4172 from
4173 PJI_PJP_PROJ_BATCH_MAP map,
4174 PJI_FM_AGGR_FIN7 tmp7,
4175 PJI_FP_TXN_ACCUM psi
4176 where
4177 map.WORKER_ID = p_worker_id and
4178 tmp7.PROJECT_ID = map.PROJECT_ID and
4179 tmp7.RECORD_TYPE = 'A' and
4180 tmp7.TXN_ACCUM_HEADER_ID = psi.TXN_ACCUM_HEADER_ID (+) and
4181 tmp7.RESOURCE_CLASS_ID = psi.RESOURCE_CLASS_ID (+) and
4182 tmp7.PROJECT_ID = psi.PROJECT_ID (+) and
4183 tmp7.PROJECT_ORG_ID = psi.PROJECT_ORG_ID (+) and
4184 tmp7.PROJECT_ORGANIZATION_ID = psi.PROJECT_ORGANIZATION_ID (+) and
4185 tmp7.TASK_ID = psi.TASK_ID (+) and
4186 nvl(tmp7.ASSIGNMENT_ID,-1) = nvl(psi.ASSIGNMENT_ID(+),-1) and
4187 tmp7.ASSIGNMENT_ID = psi.ASSIGNMENT_ID (+) and
4188 tmp7.NAMED_ROLE = psi.NAMED_ROLE (+) and
4189 tmp7.RECVR_PERIOD_TYPE = psi.RECVR_PERIOD_TYPE (+) and
4190 tmp7.RECVR_PERIOD_ID = psi.RECVR_PERIOD_ID (+) and
4191 tmp7.TXN_CURRENCY_CODE = psi.TXN_CURRENCY_CODE (+) and
4192 nvl(tmp7.cbs_element_id,-1) = nvl(psi.cbs_element_id(+),-1) ;
4193
4194 -- coMmitments
4195 insert into PJI_PJP_RMAP_FPR psi_i
4196 (
4197 WORKER_ID,
4198 STG_ROWID,
4199 TXN_ROWID,
4200 RECORD_TYPE
4201 )
4202 select /* ordered */
4203 distinct
4204 p_worker_id WORKER_ID,
4205 tmp7.ROWID STG_ROWID,
4206 null TXN_ROWID,
4207 tmp7.RECORD_TYPE
4208 from
4209 PJI_PJP_PROJ_BATCH_MAP map,
4210 PJI_FM_AGGR_FIN7 tmp7
4211 where
4212 map.WORKER_ID = p_worker_id and
4213 tmp7.PROJECT_ID = map.PROJECT_ID and
4214 tmp7.RECORD_TYPE = 'M';
4215
4216 end if;
4217
4218 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.BALANCES_ROWID_TABLE(p_worker_id);');
4219
4220 commit;
4221
4222 end BALANCES_ROWID_TABLE;
4223
4224
4225 -- -----------------------------------------------------
4226 -- procedure BALANCES_UPDATE_DELTA
4227 --
4228 --
4229 -- NOTE: This API is called from stage 3 summarization.
4230 --
4231 --
4232 -- History
4233 -- 19-MAR-2004 SVERMETT Created
4234 --
4235 -- Internal PJP Summarization API.
4236 --
4237 -- -----------------------------------------------------
4238 procedure BALANCES_UPDATE_DELTA (p_worker_id in number) is
4239
4240 l_process varchar2(30);
4241 l_last_update_date date;
4242 l_last_updated_by number;
4243 l_last_update_login number;
4244 l_extraction_type varchar2(15);
4245
4246 begin
4247
4248 l_process := PJI_PJP_SUM_MAIN.g_process || p_worker_id;
4249
4250 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.BALANCES_UPDATE_DELTA(p_worker_id);')) then
4251 return;
4252 end if;
4253
4254 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
4255
4256 l_last_update_date := sysdate;
4257 l_last_updated_by := FND_GLOBAL.USER_ID;
4258 l_last_update_login := FND_GLOBAL.LOGIN_ID;
4259
4260 if (l_extraction_type = 'INCREMENTAL') then
4261
4262 update PJI_FP_TXN_ACCUM psi
4263 set (TXN_RAW_COST,
4264 TXN_BILL_RAW_COST,
4265 TXN_BRDN_COST,
4266 TXN_BILL_BRDN_COST,
4267 TXN_REVENUE,
4268 PRJ_RAW_COST,
4269 PRJ_BILL_RAW_COST,
4270 PRJ_BRDN_COST,
4271 PRJ_BILL_BRDN_COST,
4272 PRJ_REVENUE,
4273 POU_RAW_COST,
4274 POU_BILL_RAW_COST,
4275 POU_BRDN_COST,
4276 POU_BILL_BRDN_COST,
4277 POU_REVENUE,
4278 EOU_RAW_COST,
4279 EOU_BILL_RAW_COST,
4280 EOU_BRDN_COST,
4281 EOU_BILL_BRDN_COST,
4282 G1_RAW_COST,
4283 G1_BILL_RAW_COST,
4284 G1_BRDN_COST,
4285 G1_BILL_BRDN_COST,
4286 G1_REVENUE,
4287 G2_RAW_COST,
4288 G2_BILL_RAW_COST,
4289 G2_BRDN_COST,
4290 G2_BILL_BRDN_COST,
4291 G2_REVENUE,
4292 QUANTITY,
4293 BILL_QUANTITY,
4294 LAST_UPDATE_DATE,
4295 LAST_UPDATED_BY,
4296 LAST_UPDATE_LOGIN) =
4297 (select /*+ ordered index(tmp7_r, PJI_PJP_RMAP_FPR_N1) rowid(tmp7) */
4298 decode(nvl(psi.TXN_RAW_COST, 0) + nvl(sum(tmp7.TXN_RAW_COST), 0),
4299 0, null,
4300 nvl(psi.TXN_RAW_COST, 0) + nvl(sum(tmp7.TXN_RAW_COST), 0)),
4301 decode(nvl(psi.TXN_BILL_RAW_COST, 0) + nvl(sum(tmp7.TXN_BILL_RAW_COST), 0),
4302 0, null,
4303 nvl(psi.TXN_BILL_RAW_COST, 0) + nvl(sum(tmp7.TXN_BILL_RAW_COST), 0)),
4304 decode(nvl(psi.TXN_BRDN_COST, 0) + nvl(sum(tmp7.TXN_BRDN_COST), 0),
4305 0, null,
4306 nvl(psi.TXN_BRDN_COST, 0) + nvl(sum(tmp7.TXN_BRDN_COST), 0)),
4307 decode(nvl(psi.TXN_BILL_BRDN_COST, 0) + nvl(sum(tmp7.TXN_BILL_BRDN_COST), 0),
4308 0, null,
4309 nvl(psi.TXN_BILL_BRDN_COST, 0) + nvl(sum(tmp7.TXN_BILL_BRDN_COST), 0)),
4310 decode(nvl(psi.TXN_REVENUE, 0) + nvl(sum(tmp7.TXN_REVENUE), 0),
4311 0, null,
4312 nvl(psi.TXN_REVENUE, 0) + nvl(sum(tmp7.TXN_REVENUE), 0)),
4313 decode(nvl(psi.PRJ_RAW_COST, 0) + nvl(sum(tmp7.PRJ_RAW_COST), 0),
4314 0, null,
4315 nvl(psi.PRJ_RAW_COST, 0) + nvl(sum(tmp7.PRJ_RAW_COST), 0)),
4316 decode(nvl(psi.PRJ_BILL_RAW_COST, 0) + nvl(sum(tmp7.PRJ_BILL_RAW_COST), 0),
4317 0, null,
4318 nvl(psi.PRJ_BILL_RAW_COST, 0) + nvl(sum(tmp7.PRJ_BILL_RAW_COST), 0)),
4319 decode(nvl(psi.PRJ_BRDN_COST, 0) + nvl(sum(tmp7.PRJ_BRDN_COST), 0),
4320 0, null,
4321 nvl(psi.PRJ_BRDN_COST, 0) + nvl(sum(tmp7.PRJ_BRDN_COST), 0)),
4322 decode(nvl(psi.PRJ_BILL_BRDN_COST, 0) + nvl(sum(tmp7.PRJ_BILL_BRDN_COST), 0),
4323 0, null,
4324 nvl(psi.PRJ_BILL_BRDN_COST, 0) + nvl(sum(tmp7.PRJ_BILL_BRDN_COST), 0)),
4325 decode(nvl(psi.PRJ_REVENUE, 0) + nvl(sum(tmp7.PRJ_REVENUE), 0),
4326 0, null,
4327 nvl(psi.PRJ_REVENUE, 0) + nvl(sum(tmp7.PRJ_REVENUE), 0)),
4328 decode(nvl(psi.POU_RAW_COST, 0) + nvl(sum(tmp7.POU_RAW_COST), 0),
4329 0, null,
4330 nvl(psi.POU_RAW_COST, 0) + nvl(sum(tmp7.POU_RAW_COST), 0)),
4331 decode(nvl(psi.POU_BILL_RAW_COST, 0) + nvl(sum(tmp7.POU_BILL_RAW_COST), 0),
4332 0, null,
4333 nvl(psi.POU_BILL_RAW_COST, 0) + nvl(sum(tmp7.POU_BILL_RAW_COST), 0)),
4334 decode(nvl(psi.POU_BRDN_COST, 0) + nvl(sum(tmp7.POU_BRDN_COST), 0),
4335 0, null,
4336 nvl(psi.POU_BRDN_COST, 0) + nvl(sum(tmp7.POU_BRDN_COST), 0)),
4337 decode(nvl(psi.POU_BILL_BRDN_COST, 0) + nvl(sum(tmp7.POU_BILL_BRDN_COST), 0),
4338 0, null,
4339 nvl(psi.POU_BILL_BRDN_COST, 0) + nvl(sum(tmp7.POU_BILL_BRDN_COST), 0)),
4340 decode(nvl(psi.POU_REVENUE, 0) + nvl(sum(tmp7.POU_REVENUE), 0),
4341 0, null,
4342 nvl(psi.POU_REVENUE, 0) + nvl(sum(tmp7.POU_REVENUE), 0)),
4343 decode(nvl(psi.EOU_RAW_COST, 0) + nvl(sum(tmp7.EOU_RAW_COST), 0),
4344 0, null,
4345 nvl(psi.EOU_RAW_COST, 0) + nvl(sum(tmp7.EOU_RAW_COST), 0)),
4346 decode(nvl(psi.EOU_BILL_RAW_COST, 0) + nvl(sum(tmp7.EOU_BILL_RAW_COST), 0),
4347 0, null,
4348 nvl(psi.EOU_BILL_RAW_COST, 0) + nvl(sum(tmp7.EOU_BILL_RAW_COST), 0)),
4349 decode(nvl(psi.EOU_BRDN_COST, 0) + nvl(sum(tmp7.EOU_BRDN_COST), 0),
4350 0, null,
4351 nvl(psi.EOU_BRDN_COST, 0) + nvl(sum(tmp7.EOU_BRDN_COST), 0)),
4352 decode(nvl(psi.EOU_BILL_BRDN_COST, 0) + nvl(sum(tmp7.EOU_BILL_BRDN_COST), 0),
4353 0, null,
4354 nvl(psi.EOU_BILL_BRDN_COST, 0) + nvl(sum(tmp7.EOU_BILL_BRDN_COST), 0)),
4355 decode(nvl(psi.G1_RAW_COST, 0) + nvl(sum(tmp7.G1_RAW_COST), 0),
4356 0, null,
4357 nvl(psi.G1_RAW_COST, 0) + nvl(sum(tmp7.G1_RAW_COST), 0)),
4358 decode(nvl(psi.G1_BILL_RAW_COST, 0) + nvl(sum(tmp7.G1_BILL_RAW_COST), 0),
4359 0, null,
4360 nvl(psi.G1_BILL_RAW_COST, 0) + nvl(sum(tmp7.G1_BILL_RAW_COST), 0)),
4361 decode(nvl(psi.G1_BRDN_COST, 0) + nvl(sum(tmp7.G1_BRDN_COST), 0),
4362 0, null,
4363 nvl(psi.G1_BRDN_COST, 0) + nvl(sum(tmp7.G1_BRDN_COST), 0)),
4364 decode(nvl(psi.G1_BILL_BRDN_COST, 0) + nvl(sum(tmp7.G1_BILL_BRDN_COST), 0),
4365 0, null,
4366 nvl(psi.G1_BILL_BRDN_COST, 0) + nvl(sum(tmp7.G1_BILL_BRDN_COST), 0)),
4367 decode(nvl(psi.G1_REVENUE, 0) + nvl(sum(tmp7.G1_REVENUE), 0),
4368 0, null,
4369 nvl(psi.G1_REVENUE, 0) + nvl(sum(tmp7.G1_REVENUE), 0)),
4370 decode(nvl(psi.G2_RAW_COST, 0) + nvl(sum(tmp7.G2_RAW_COST), 0),
4371 0, null,
4372 nvl(psi.G2_RAW_COST, 0) + nvl(sum(tmp7.G2_RAW_COST), 0)),
4373 decode(nvl(psi.G2_BILL_RAW_COST, 0) + nvl(sum(tmp7.G2_BILL_RAW_COST), 0),
4374 0, null,
4375 nvl(psi.G2_BILL_RAW_COST, 0) + nvl(sum(tmp7.G2_BILL_RAW_COST), 0)),
4376 decode(nvl(psi.G2_BRDN_COST, 0) + nvl(sum(tmp7.G2_BRDN_COST), 0),
4377 0, null,
4378 nvl(psi.G2_BRDN_COST, 0) + nvl(sum(tmp7.G2_BRDN_COST), 0)),
4379 decode(nvl(psi.G2_BILL_BRDN_COST, 0) + nvl(sum(tmp7.G2_BILL_BRDN_COST), 0),
4380 0, null,
4381 nvl(psi.G2_BILL_BRDN_COST, 0) + nvl(sum(tmp7.G2_BILL_BRDN_COST), 0)),
4382 decode(nvl(psi.G2_REVENUE, 0) + nvl(sum(tmp7.G2_REVENUE), 0),
4383 0, null,
4384 nvl(psi.G2_REVENUE, 0) + nvl(sum(tmp7.G2_REVENUE), 0)),
4385 decode(nvl(psi.QUANTITY, 0) + nvl(sum(tmp7.QUANTITY), 0),
4386 0, null,
4387 nvl(psi.QUANTITY, 0) + nvl(sum(tmp7.QUANTITY), 0)),
4388 decode(nvl(psi.BILL_QUANTITY, 0) + nvl(sum(tmp7.BILL_QUANTITY), 0),
4389 0, null,
4390 nvl(psi.BILL_QUANTITY, 0) + nvl(sum(tmp7.BILL_QUANTITY), 0)),
4391 l_last_update_date,
4392 l_last_updated_by,
4393 l_last_update_login
4394 from
4395 PJI_PJP_RMAP_FPR tmp7_r,
4396 PJI_FM_AGGR_FIN7 tmp7
4397 where
4398 tmp7_r.WORKER_ID = p_worker_id and
4399 tmp7_r.RECORD_TYPE = 'A' and
4400 tmp7_r.TXN_ROWID is not null and
4401 tmp7.ROWID = tmp7_r.STG_ROWID and
4402 psi.ROWID = tmp7_r.TXN_ROWID)
4403 where psi.ROWID in
4404 (select /*+ index(tmp7_r, PJI_PJP_RMAP_FPR_N1) */
4405 tmp7_r.TXN_ROWID
4406 from
4407 PJI_PJP_RMAP_FPR tmp7_r
4408 where
4409 tmp7_r.WORKER_ID = p_worker_id and
4410 tmp7_r.RECORD_TYPE = 'A' and
4411 tmp7_r.TXN_ROWID is not null);
4412
4413 end if;
4414
4415 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.BALANCES_UPDATE_DELTA(p_worker_id);');
4416
4417 commit;
4418
4419 end BALANCES_UPDATE_DELTA;
4420
4421
4422 -- -----------------------------------------------------
4423 -- procedure BALANCES_INSERT_DELTA
4424 --
4425 --
4426 -- NOTE: This API is called from stage 3 summarization.
4427 --
4428 --
4429 -- History
4430 -- 19-MAR-2004 SVERMETT Created
4431 --
4432 -- Internal PJP Summarization API.
4433 --
4434 -- -----------------------------------------------------
4435 procedure BALANCES_INSERT_DELTA (p_worker_id in number) is
4436
4437 l_process varchar2(30);
4438 l_last_update_date date;
4439 l_last_updated_by number;
4440 l_creation_date date;
4441 l_created_by number;
4442 l_last_update_login number;
4443 l_extraction_type varchar2(15);
4444
4445 begin
4446
4447 l_process := PJI_PJP_SUM_MAIN.g_process || p_worker_id;
4448
4449 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.BALANCES_INSERT_DELTA(p_worker_id);')) then
4450 return;
4451 end if;
4452
4453 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
4454
4455 l_last_update_date := sysdate;
4456 l_last_updated_by := FND_GLOBAL.USER_ID;
4457 l_creation_date := sysdate;
4458 l_created_by := FND_GLOBAL.USER_ID;
4459 l_last_update_login := FND_GLOBAL.LOGIN_ID;
4460
4461 if (l_extraction_type = 'INCREMENTAL') then
4462
4463 insert /*+ append parallel(bal_i) */ into PJI_FP_TXN_ACCUM bal_i
4464 (
4465 TXN_ACCUM_HEADER_ID,
4466 RESOURCE_CLASS_ID,
4467 PROJECT_ID,
4468 PROJECT_ORG_ID,
4469 PROJECT_ORGANIZATION_ID,
4470 PROJECT_TYPE_CLASS,
4471 TASK_ID,
4472 ASSIGNMENT_ID,
4473 NAMED_ROLE,
4474 RECVR_PERIOD_TYPE,
4475 RECVR_PERIOD_ID,
4476 TXN_CURRENCY_CODE,
4477 TXN_RAW_COST,
4478 TXN_BILL_RAW_COST,
4479 TXN_BRDN_COST,
4480 TXN_BILL_BRDN_COST,
4481 TXN_REVENUE,
4482 PRJ_RAW_COST,
4483 PRJ_BILL_RAW_COST,
4484 PRJ_BRDN_COST,
4485 PRJ_BILL_BRDN_COST,
4486 PRJ_REVENUE,
4487 POU_RAW_COST,
4488 POU_BILL_RAW_COST,
4489 POU_BRDN_COST,
4490 POU_BILL_BRDN_COST,
4491 POU_REVENUE,
4492 EOU_RAW_COST,
4493 EOU_BILL_RAW_COST,
4494 EOU_BRDN_COST,
4495 EOU_BILL_BRDN_COST,
4496 G1_RAW_COST,
4497 G1_BILL_RAW_COST,
4498 G1_BRDN_COST,
4499 G1_BILL_BRDN_COST,
4500 G1_REVENUE,
4501 G2_RAW_COST,
4502 G2_BILL_RAW_COST,
4503 G2_BRDN_COST,
4504 G2_BILL_BRDN_COST,
4505 G2_REVENUE,
4506 QUANTITY,
4507 BILL_QUANTITY,
4508 LAST_UPDATE_DATE,
4509 LAST_UPDATED_BY,
4510 CREATION_DATE,
4511 CREATED_BY,
4512 LAST_UPDATE_LOGIN,
4513 CBS_ELEMENT_ID
4514 )
4515 select /*+ ordered
4516 full(tmp7_r) parallel(tmp7_r)
4517 rowid(tmp7) */
4518 tmp7.TXN_ACCUM_HEADER_ID,
4519 tmp7.RESOURCE_CLASS_ID,
4520 tmp7.PROJECT_ID,
4521 tmp7.PROJECT_ORG_ID,
4522 tmp7.PROJECT_ORGANIZATION_ID,
4523 tmp7.PROJECT_TYPE_CLASS,
4524 tmp7.TASK_ID,
4525 tmp7.ASSIGNMENT_ID,
4526 tmp7.NAMED_ROLE,
4527 tmp7.RECVR_PERIOD_TYPE,
4528 tmp7.RECVR_PERIOD_ID,
4529 tmp7.TXN_CURRENCY_CODE,
4530 sum(tmp7.TXN_RAW_COST) TXN_RAW_COST,
4531 sum(tmp7.TXN_BILL_RAW_COST) TXN_BILL_RAW_COST,
4532 sum(tmp7.TXN_BRDN_COST) TXN_BRDN_COST,
4533 sum(tmp7.TXN_BILL_BRDN_COST) TXN_BILL_BRDN_COST,
4534 sum(tmp7.TXN_REVENUE) TXN_REVENUE,
4535 sum(tmp7.PRJ_RAW_COST) PRJ_RAW_COST,
4536 sum(tmp7.PRJ_BILL_RAW_COST) PRJ_BILL_RAW_COST,
4537 sum(tmp7.PRJ_BRDN_COST) PRJ_BRDN_COST,
4538 sum(tmp7.PRJ_BILL_BRDN_COST) PRJ_BILL_BRDN_COST,
4539 sum(tmp7.PRJ_REVENUE) PRJ_REVENUE,
4540 sum(tmp7.POU_RAW_COST) POU_RAW_COST,
4541 sum(tmp7.POU_BILL_RAW_COST) POU_BILL_RAW_COST,
4542 sum(tmp7.POU_BRDN_COST) POU_BRDN_COST,
4543 sum(tmp7.POU_BILL_BRDN_COST) POU_BILL_BRDN_COST,
4544 sum(tmp7.POU_REVENUE) POU_REVENUE,
4545 sum(tmp7.EOU_RAW_COST) EOU_RAW_COST,
4546 sum(tmp7.EOU_BILL_RAW_COST) EOU_BILL_RAW_COST,
4547 sum(tmp7.EOU_BRDN_COST) EOU_BRDN_COST,
4548 sum(tmp7.EOU_BILL_BRDN_COST) EOU_BILL_BRDN_COST,
4549 sum(tmp7.G1_RAW_COST) G1_RAW_COST,
4550 sum(tmp7.G1_BILL_RAW_COST) G1_BILL_RAW_COST,
4551 sum(tmp7.G1_BRDN_COST) G1_BRDN_COST,
4552 sum(tmp7.G1_BILL_BRDN_COST) G1_BILL_BRDN_COST,
4553 sum(tmp7.G1_REVENUE) G1_REVENUE,
4554 sum(tmp7.G2_RAW_COST) G2_RAW_COST,
4555 sum(tmp7.G2_BILL_RAW_COST) G2_BILL_RAW_COST,
4556 sum(tmp7.G2_BRDN_COST) G2_BRDN_COST,
4557 sum(tmp7.G2_BILL_BRDN_COST) G2_BILL_BRDN_COST,
4558 sum(tmp7.G2_REVENUE) G2_REVENUE,
4559 sum(tmp7.QUANTITY) QUANTITY,
4560 sum(tmp7.BILL_QUANTITY) BILL_QUANTITY,
4561 l_last_update_date LAST_UPDATE_DATE,
4562 l_last_updated_by LAST_UPDATED_BY,
4563 l_creation_date CREATION_DATE,
4564 l_created_by CREATED_BY,
4565 l_last_update_login LAST_UPDATE_LOGIN,
4566 cbs_element_id cbs_element_id
4567 from
4568 PJI_PJP_RMAP_FPR tmp7_r,
4569 PJI_FM_AGGR_FIN7 tmp7
4570 where
4571 tmp7_r.WORKER_ID = p_worker_id and
4572 tmp7_r.TXN_ROWID is null and
4573 tmp7_r.RECORD_TYPE = 'A' and
4574 tmp7.ROWID = tmp7_r.STG_ROWID
4575 group by
4576 tmp7.TXN_ACCUM_HEADER_ID,
4577 tmp7.RESOURCE_CLASS_ID,
4578 tmp7.PROJECT_ID,
4579 tmp7.PROJECT_ORG_ID,
4580 tmp7.PROJECT_ORGANIZATION_ID,
4581 tmp7.PROJECT_TYPE_CLASS,
4582 tmp7.TASK_ID,
4583 tmp7.ASSIGNMENT_ID,
4584 tmp7.NAMED_ROLE,
4585 tmp7.RECVR_PERIOD_TYPE,
4586 tmp7.RECVR_PERIOD_ID,
4587 tmp7.TXN_CURRENCY_CODE,
4588 tmp7.cbs_element_id;
4589
4590 end if;
4591
4592 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.BALANCES_INSERT_DELTA(p_worker_id);');
4593
4594 commit;
4595
4596 end BALANCES_INSERT_DELTA;
4597
4598
4599 -- -----------------------------------------------------
4600 -- procedure PURGE_INCREMENTAL_BALANCES
4601 --
4602 --
4603 -- NOTE: This API is called from stage 3 summarization.
4604 --
4605 --
4606 -- History
4607 -- 19-MAR-2004 SVERMETT Created
4608 --
4609 -- Internal PJP Summarization API.
4610 --
4611 -- -----------------------------------------------------
4612 procedure PURGE_INCREMENTAL_BALANCES (p_worker_id in number) is
4613
4614 l_process varchar2(30);
4615 l_extraction_type varchar2(15);
4616
4617 begin
4618
4619 l_process := PJI_PJP_SUM_MAIN.g_process || p_worker_id;
4620
4621 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.PURGE_INCREMENTAL_BALANCES(p_worker_id);')) then
4622 return;
4623 end if;
4624
4625 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
4626
4627 if (l_extraction_type = 'INCREMENTAL') then
4628
4629 delete from PJI_FM_AGGR_FIN7
4630 where ROWID in (select STG_ROWID
4631 from PJI_PJP_RMAP_FPR
4632 where WORKER_ID = p_worker_id);
4633
4634 end if;
4635
4636 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.PURGE_INCREMENTAL_BALANCES(p_worker_id);');
4637
4638 commit;
4639
4640 end PURGE_INCREMENTAL_BALANCES;
4641
4642
4643 -- -----------------------------------------------------
4644 -- procedure PURGE_BALANCES_CMT
4645 --
4646 --
4647 -- NOTE: This API is called from stage 3 summarization.
4648 --
4649 --
4650 -- History
4651 -- 19-MAR-2004 SVERMETT Created
4652 --
4653 -- Internal PJP Summarization API.
4654 --
4655 -- -----------------------------------------------------
4656 procedure PURGE_BALANCES_CMT (p_worker_id in number) is
4657
4658 l_process varchar2(30);
4659 l_extraction_type varchar2(30);
4660
4661 begin
4662
4663 l_process := PJI_PJP_SUM_MAIN.g_process || p_worker_id;
4664
4665 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.PURGE_BALANCES_CMT(p_worker_id);')) then
4666 return;
4667 end if;
4668
4669 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
4670
4671 if (l_extraction_type = 'INCREMENTAL') then
4672
4673 delete
4674 from PJI_FP_TXN_ACCUM1 bal
4675 where bal.PROJECT_ID in (select distinct
4676 fin7.PROJECT_ID
4677 from PJI_PJP_RMAP_FPR tmp7_r,
4678 PJI_FM_AGGR_FIN7 fin7
4679 where tmp7_r.WORKER_ID = p_worker_id and
4680 tmp7_r.RECORD_TYPE = 'M' and
4681 fin7.ROWID = tmp7_r.STG_ROWID);
4682
4683
4684 end if;
4685
4686 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.PURGE_BALANCES_CMT(p_worker_id);');
4687
4688 commit;
4689
4690 end PURGE_BALANCES_CMT;
4691
4692
4693 -- -----------------------------------------------------
4694 -- procedure BALANCES_INSERT_DELTA_CMT
4695 --
4696 --
4697 -- NOTE: This API is called from stage 3 summarization.
4698 --
4699 --
4700 -- History
4701 -- 19-MAR-2004 SVERMETT Created
4702 --
4703 -- Internal PJP Summarization API.
4704 --
4705 -- -----------------------------------------------------
4706 procedure BALANCES_INSERT_DELTA_CMT (p_worker_id in number) is
4707
4708 l_process varchar2(30);
4709 l_last_update_date date;
4710 l_last_updated_by number;
4711 l_creation_date date;
4712 l_created_by number;
4713 l_last_update_login number;
4714 l_extraction_type varchar2(15);
4715
4716 begin
4717
4718 l_process := PJI_PJP_SUM_MAIN.g_process || p_worker_id;
4719
4720 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.BALANCES_INSERT_DELTA_CMT(p_worker_id);')) then
4721 return;
4722 end if;
4723
4724 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
4725
4726 l_last_update_date := sysdate;
4727 l_last_updated_by := FND_GLOBAL.USER_ID;
4728 l_creation_date := sysdate;
4729 l_created_by := FND_GLOBAL.USER_ID;
4730 l_last_update_login := FND_GLOBAL.LOGIN_ID;
4731
4732 if (l_extraction_type = 'INCREMENTAL') then
4733
4734 insert /*+ append parallel(bal_i) */ into PJI_FP_TXN_ACCUM1 bal_i
4735 (
4736 TXN_ACCUM_HEADER_ID,
4737 PROJECT_ID,
4738 PROJECT_ORG_ID,
4739 PROJECT_ORGANIZATION_ID,
4740 TASK_ID,
4741 RECVR_PERIOD_TYPE,
4742 RECVR_PERIOD_ID,
4743 TXN_CURRENCY_CODE,
4744 TXN_SUP_INV_COMMITTED_COST,
4745 TXN_PO_COMMITTED_COST,
4746 TXN_PR_COMMITTED_COST,
4747 TXN_OTH_COMMITTED_COST,
4748 PRJ_SUP_INV_COMMITTED_COST,
4749 PRJ_PO_COMMITTED_COST,
4750 PRJ_PR_COMMITTED_COST,
4751 PRJ_OTH_COMMITTED_COST,
4752 POU_SUP_INV_COMMITTED_COST,
4753 POU_PO_COMMITTED_COST,
4754 POU_PR_COMMITTED_COST,
4755 POU_OTH_COMMITTED_COST,
4756 EOU_SUP_INV_COMMITTED_COST,
4757 EOU_PO_COMMITTED_COST,
4758 EOU_PR_COMMITTED_COST,
4759 EOU_OTH_COMMITTED_COST,
4760 G1_SUP_INV_COMMITTED_COST,
4761 G1_PO_COMMITTED_COST,
4762 G1_PR_COMMITTED_COST,
4763 G1_OTH_COMMITTED_COST,
4764 G2_SUP_INV_COMMITTED_COST,
4765 G2_PO_COMMITTED_COST,
4766 G2_PR_COMMITTED_COST,
4767 G2_OTH_COMMITTED_COST,
4768 LAST_UPDATE_DATE,
4769 LAST_UPDATED_BY,
4770 CREATION_DATE,
4771 CREATED_BY,
4772 LAST_UPDATE_LOGIN,
4773 CBS_ELEMENT_ID
4774 )
4775 select
4776 tmp7.TXN_ACCUM_HEADER_ID,
4777 tmp7.PROJECT_ID,
4778 tmp7.PROJECT_ORG_ID,
4779 tmp7.PROJECT_ORGANIZATION_ID,
4780 tmp7.TASK_ID,
4781 tmp7.RECVR_PERIOD_TYPE,
4782 tmp7.RECVR_PERIOD_ID,
4783 tmp7.TXN_CURRENCY_CODE,
4784 sum(tmp7.TXN_SUP_INV_COMMITTED_COST) TXN_SUP_INV_COMMITTED_COST,
4785 sum(tmp7.TXN_PO_COMMITTED_COST) TXN_PO_COMMITTED_COST,
4786 sum(tmp7.TXN_PR_COMMITTED_COST) TXN_PR_COMMITTED_COST,
4787 sum(tmp7.TXN_OTH_COMMITTED_COST) TXN_OTH_COMMITTED_COST,
4788 sum(tmp7.PRJ_SUP_INV_COMMITTED_COST) PRJ_SUP_INV_COMMITTED_COST,
4789 sum(tmp7.PRJ_PO_COMMITTED_COST) PRJ_PO_COMMITTED_COST,
4790 sum(tmp7.PRJ_PR_COMMITTED_COST) PRJ_PR_COMMITTED_COST,
4791 sum(tmp7.PRJ_OTH_COMMITTED_COST) PRJ_OTH_COMMITTED_COST,
4792 sum(tmp7.POU_SUP_INV_COMMITTED_COST) POU_SUP_INV_COMMITTED_COST,
4793 sum(tmp7.POU_PO_COMMITTED_COST) POU_PO_COMMITTED_COST,
4794 sum(tmp7.POU_PR_COMMITTED_COST) POU_PR_COMMITTED_COST,
4795 sum(tmp7.POU_OTH_COMMITTED_COST) POU_OTH_COMMITTED_COST,
4796 sum(tmp7.EOU_SUP_INV_COMMITTED_COST) EOU_SUP_INV_COMMITTED_COST,
4797 sum(tmp7.EOU_PO_COMMITTED_COST) EOU_PO_COMMITTED_COST,
4798 sum(tmp7.EOU_PR_COMMITTED_COST) EOU_PR_COMMITTED_COST,
4799 sum(tmp7.EOU_OTH_COMMITTED_COST) EOU_OTH_COMMITTED_COST,
4800 sum(tmp7.G1_SUP_INV_COMMITTED_COST) G1_SUP_INV_COMMITTED_COST,
4801 sum(tmp7.G1_PO_COMMITTED_COST) G1_PO_COMMITTED_COST,
4802 sum(tmp7.G1_PR_COMMITTED_COST) G1_PR_COMMITTED_COST,
4803 sum(tmp7.G1_OTH_COMMITTED_COST) G1_OTH_COMMITTED_COST,
4804 sum(tmp7.G2_SUP_INV_COMMITTED_COST) G2_SUP_INV_COMMITTED_COST,
4805 sum(tmp7.G2_PO_COMMITTED_COST) G2_PO_COMMITTED_COST,
4806 sum(tmp7.G2_PR_COMMITTED_COST) G2_PR_COMMITTED_COST,
4807 sum(tmp7.G2_OTH_COMMITTED_COST) G2_OTH_COMMITTED_COST,
4808 l_last_update_date LAST_UPDATE_DATE,
4809 l_last_updated_by LAST_UPDATED_BY,
4810 l_creation_date CREATION_DATE,
4811 l_created_by CREATED_BY,
4812 l_last_update_login LAST_UPDATE_LOGIN,
4813 cbs_element_id cbs_element_id
4814 from
4815 PJI_PJP_RMAP_FPR tmp7_r,
4816 PJI_FM_AGGR_FIN7 tmp7
4817 where
4818 tmp7_r.WORKER_ID = p_worker_id and
4819 tmp7_r.TXN_ROWID is null and
4820 tmp7_r.RECORD_TYPE = 'M' and
4821 tmp7.ROWID = tmp7_r.STG_ROWID
4822 group by
4823 tmp7.TXN_ACCUM_HEADER_ID,
4824 tmp7.PROJECT_ID,
4825 tmp7.PROJECT_ORG_ID,
4826 tmp7.PROJECT_ORGANIZATION_ID,
4827 tmp7.TASK_ID,
4828 tmp7.RECVR_PERIOD_TYPE,
4829 tmp7.RECVR_PERIOD_ID,
4830 tmp7.TXN_CURRENCY_CODE,
4831 tmp7.cbs_element_id;
4832
4833 end if;
4834
4835 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.BALANCES_INSERT_DELTA_CMT(p_worker_id);');
4836
4837 commit;
4838
4839 end BALANCES_INSERT_DELTA_CMT;
4840
4841
4842 -- -----------------------------------------------------
4843 -- procedure ACT_ROWID_TABLE
4844 --
4845 --
4846 -- NOTE: This API is called from stage 3 summarization.
4847 --
4848 --
4849 -- History
4850 -- 30-SEP-2004 SVERMETT Created
4851 --
4852 -- Internal PJP Summarization API.
4853 --
4854 -- -----------------------------------------------------
4855 procedure ACT_ROWID_TABLE (p_worker_id in number) is
4856
4857 l_process varchar2(30);
4858 l_extraction_type varchar2(15);
4859
4860 begin
4861
4862 l_process := PJI_PJP_SUM_MAIN.g_process || p_worker_id;
4863
4864 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.ACT_ROWID_TABLE(p_worker_id);')) then
4865 return;
4866 end if;
4867
4868 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
4869
4870 insert into PJI_PJP_RMAP_ACR psi_i
4871 (
4872 WORKER_ID,
4873 STG_ROWID
4874 )
4875 select /* ordered */
4876 p_worker_id WORKER_ID,
4877 act4.ROWID STG_ROWID
4878 from
4879 PJI_PJP_PROJ_BATCH_MAP map,
4880 PJI_FM_AGGR_ACT4 act4
4881 where
4882 map.WORKER_ID = p_worker_id and
4883 act4.PROJECT_ID = map.PROJECT_ID;
4884
4885 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.ACT_ROWID_TABLE(p_worker_id);');
4886
4887 commit;
4888
4889 end ACT_ROWID_TABLE;
4890
4891
4892 -- -----------------------------------------------------
4893 -- procedure PURGE_BALANCES_ACT
4894 --
4895 --
4896 -- NOTE: This API is called from stage 3 summarization.
4897 --
4898 --
4899 -- History
4900 -- 19-MAR-2004 SVERMETT Created
4901 --
4902 -- Internal PJP Summarization API.
4903 --
4904 -- -----------------------------------------------------
4905 procedure PURGE_BALANCES_ACT (p_worker_id in number) is
4906
4907 l_process varchar2(30);
4908 l_extraction_type varchar2(30);
4909
4910 begin
4911
4912 l_process := PJI_PJP_SUM_MAIN.g_process || p_worker_id;
4913
4914 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_PSI.PURGE_BALANCES_ACT(p_worker_id);')) then
4915 return;
4916 end if;
4917
4918 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
4919
4920 if (l_extraction_type = 'FULL' or
4921 l_extraction_type = 'INCREMENTAL' or
4922 l_extraction_type = 'PARTIAL') then
4923
4924 delete
4925 from PJI_FM_AGGR_ACT4
4926 where ROWID in (select STG_ROWID
4927 from PJI_PJP_RMAP_ACR
4928 where WORKER_ID = p_worker_id);
4929
4930 end if;
4931
4932 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_PSI.PURGE_BALANCES_ACT(p_worker_id);');
4933
4934 commit;
4935
4936 end PURGE_BALANCES_ACT;
4937
4938
4939 -- -----------------------------------------------------
4940 -- procedure CLEANUP
4941 --
4942 -- History
4943 -- 19-MAR-2004 SVERMETT Created
4944 --
4945 -- Internal PJP Summarization API.
4946 --
4947 -- -----------------------------------------------------
4948 procedure CLEANUP (p_worker_id in number) is
4949
4950 l_schema varchar2(30);
4951
4952 begin
4953
4954 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
4955
4956 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
4957 'PJI_FM_AGGR_RES_TYPES',
4958 'NORMAL',
4959 null);
4960
4961 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
4962 'PJI_FM_AGGR_FIN1',
4963 'NORMAL',
4964 null);
4965
4966 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
4967 'PJI_FM_AGGR_FIN2',
4968 'NORMAL',
4969 null);
4970
4971 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
4972 'PJI_FM_AGGR_FIN6',
4973 'NORMAL',
4974 null);
4975
4976 end CLEANUP;
4977
4978 end PJI_FM_SUM_PSI;