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