[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_SUM_ACT
Source
1 package body PJI_FM_SUM_ACT as
2 /* $Header: PJISF08B.pls 120.1 2005/10/17 12:02:12 appldev noship $ */
3
4 -- -----------------------------------------------------
5 -- procedure BASE_SUMMARY
6 -- -----------------------------------------------------
7 procedure BASE_SUMMARY (p_worker_id in number) is
8
9 l_process varchar2(30);
10 l_batch_id number;
11 l_min_date number;
12 l_schema varchar2(30);
13
14 begin
15
16 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
17
18 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_ACT.BASE_SUMMARY(p_worker_id);')) then
19 return;
20 end if;
21
22 l_batch_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
23 (
24 l_process,
25 'CURRENT_BATCH'
26 );
27
28 l_min_date := to_number(to_char(to_date(
29 PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
30 PJI_FM_SUM_MAIN.g_date_mask), 'J'));
31
32 insert /*+ append parallel(act1_i) */ into PJI_FM_AGGR_ACT1 act1_i
33 (
34 WORKER_ID,
35 PROJECT_ID,
36 PROJECT_ORG_ID,
37 PROJECT_ORGANIZATION_ID,
38 TASK_ID,
39 CUSTOMER_ID,
40 GL_TIME_ID,
41 GL_PERIOD_NAME,
42 PA_TIME_ID,
43 PA_PERIOD_NAME,
44 TXN_CURRENCY_CODE,
45 TXN_REVENUE,
46 TXN_FUNDING,
47 TXN_INITIAL_FUNDING_AMOUNT,
48 TXN_ADDITIONAL_FUNDING_AMOUNT,
49 TXN_CANCELLED_FUNDING_AMOUNT,
50 TXN_FUNDING_ADJUSTMENT_AMOUNT,
51 TXN_REVENUE_WRITEOFF,
52 TXN_AR_INVOICE_AMOUNT,
53 TXN_AR_CASH_APPLIED_AMOUNT,
54 TXN_AR_INVOICE_WRITEOFF_AMOUNT,
55 TXN_AR_CREDIT_MEMO_AMOUNT,
56 TXN_UNBILLED_RECEIVABLES,
57 TXN_UNEARNED_REVENUE,
58 TXN_AR_UNAPPR_INVOICE_AMOUNT,
59 TXN_AR_APPR_INVOICE_AMOUNT,
60 TXN_AR_AMOUNT_DUE,
61 TXN_AR_AMOUNT_OVERDUE,
62 PRJ_REVENUE,
63 PRJ_FUNDING,
64 PRJ_INITIAL_FUNDING_AMOUNT,
65 PRJ_ADDITIONAL_FUNDING_AMOUNT,
66 PRJ_CANCELLED_FUNDING_AMOUNT,
67 PRJ_FUNDING_ADJUSTMENT_AMOUNT,
68 PRJ_REVENUE_WRITEOFF,
69 PRJ_AR_INVOICE_AMOUNT,
70 PRJ_AR_CASH_APPLIED_AMOUNT,
71 PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
72 PRJ_AR_CREDIT_MEMO_AMOUNT,
73 PRJ_UNBILLED_RECEIVABLES,
74 PRJ_UNEARNED_REVENUE,
75 PRJ_AR_UNAPPR_INVOICE_AMOUNT,
76 PRJ_AR_APPR_INVOICE_AMOUNT,
77 PRJ_AR_AMOUNT_DUE,
78 PRJ_AR_AMOUNT_OVERDUE,
79 POU_REVENUE,
80 POU_FUNDING,
81 POU_INITIAL_FUNDING_AMOUNT,
82 POU_ADDITIONAL_FUNDING_AMOUNT,
83 POU_CANCELLED_FUNDING_AMOUNT,
84 POU_FUNDING_ADJUSTMENT_AMOUNT,
85 POU_REVENUE_WRITEOFF,
86 POU_AR_INVOICE_AMOUNT,
87 POU_AR_CASH_APPLIED_AMOUNT,
88 POU_AR_INVOICE_WRITEOFF_AMOUNT,
89 POU_AR_CREDIT_MEMO_AMOUNT,
90 POU_UNBILLED_RECEIVABLES,
91 POU_UNEARNED_REVENUE,
92 POU_AR_UNAPPR_INVOICE_AMOUNT,
93 POU_AR_APPR_INVOICE_AMOUNT,
94 POU_AR_AMOUNT_DUE,
95 POU_AR_AMOUNT_OVERDUE,
96 INITIAL_FUNDING_COUNT,
97 ADDITIONAL_FUNDING_COUNT,
98 CANCELLED_FUNDING_COUNT,
99 FUNDING_ADJUSTMENT_COUNT,
100 AR_INVOICE_COUNT,
101 AR_CASH_APPLIED_COUNT,
102 AR_INVOICE_WRITEOFF_COUNT,
103 AR_CREDIT_MEMO_COUNT,
104 AR_UNAPPR_INVOICE_COUNT,
105 AR_APPR_INVOICE_COUNT,
106 AR_COUNT_DUE,
107 AR_COUNT_OVERDUE
108 )
109 select
110 p_worker_id,
111 PROJECT_ID,
112 PROJECT_ORG_ID,
113 PROJECT_ORGANIZATION_ID,
114 TASK_ID,
115 CUSTOMER_ID,
116 GL_TIME_ID,
117 GL_PERIOD_NAME,
118 PA_TIME_ID,
119 PA_PERIOD_NAME,
120 TXN_CURRENCY_CODE,
121 sum(TXN_REVENUE),
122 sum(TXN_FUNDING),
123 sum(TXN_INITIAL_FUNDING_AMOUNT),
124 sum(TXN_ADDITIONAL_FUNDING_AMOUNT),
125 sum(TXN_CANCELLED_FUNDING_AMOUNT),
126 sum(TXN_FUNDING_ADJUSTMENT_AMOUNT),
127 sum(TXN_REVENUE_WRITEOFF),
128 sum(TXN_AR_INVOICE_AMOUNT),
129 sum(TXN_AR_CASH_APPLIED_AMOUNT),
130 sum(TXN_AR_INVOICE_WRITEOFF_AMOUNT),
131 sum(TXN_AR_CREDIT_MEMO_AMOUNT),
132 sum(TXN_UNBILLED_RECEIVABLES),
133 sum(TXN_UNEARNED_REVENUE),
134 sum(TXN_AR_UNAPPR_INVOICE_AMOUNT),
135 sum(TXN_AR_APPR_INVOICE_AMOUNT),
136 sum(TXN_AR_AMOUNT_DUE),
137 sum(TXN_AR_AMOUNT_OVERDUE),
138 sum(PRJ_REVENUE),
139 sum(PRJ_FUNDING),
140 sum(PRJ_INITIAL_FUNDING_AMOUNT),
141 sum(PRJ_ADDITIONAL_FUNDING_AMOUNT),
142 sum(PRJ_CANCELLED_FUNDING_AMOUNT),
143 sum(PRJ_FUNDING_ADJUSTMENT_AMOUNT),
144 sum(PRJ_REVENUE_WRITEOFF),
145 sum(PRJ_AR_INVOICE_AMOUNT),
146 sum(PRJ_AR_CASH_APPLIED_AMOUNT),
147 sum(PRJ_AR_INVOICE_WRITEOFF_AMOUNT),
148 sum(PRJ_AR_CREDIT_MEMO_AMOUNT),
149 sum(PRJ_UNBILLED_RECEIVABLES),
150 sum(PRJ_UNEARNED_REVENUE),
151 sum(PRJ_AR_UNAPPR_INVOICE_AMOUNT),
152 sum(PRJ_AR_APPR_INVOICE_AMOUNT),
153 sum(PRJ_AR_AMOUNT_DUE),
154 sum(PRJ_AR_AMOUNT_OVERDUE),
155 sum(POU_REVENUE),
156 sum(POU_FUNDING),
157 sum(POU_INITIAL_FUNDING_AMOUNT),
158 sum(POU_ADDITIONAL_FUNDING_AMOUNT),
159 sum(POU_CANCELLED_FUNDING_AMOUNT),
160 sum(POU_FUNDING_ADJUSTMENT_AMOUNT),
161 sum(POU_REVENUE_WRITEOFF),
162 sum(POU_AR_INVOICE_AMOUNT),
163 sum(POU_AR_CASH_APPLIED_AMOUNT),
164 sum(POU_AR_INVOICE_WRITEOFF_AMOUNT),
165 sum(POU_AR_CREDIT_MEMO_AMOUNT),
166 sum(POU_UNBILLED_RECEIVABLES),
167 sum(POU_UNEARNED_REVENUE),
168 sum(POU_AR_UNAPPR_INVOICE_AMOUNT),
169 sum(POU_AR_APPR_INVOICE_AMOUNT),
170 sum(POU_AR_AMOUNT_DUE),
171 sum(POU_AR_AMOUNT_OVERDUE),
172 sum(INITIAL_FUNDING_COUNT),
173 sum(ADDITIONAL_FUNDING_COUNT),
174 sum(CANCELLED_FUNDING_COUNT),
175 sum(FUNDING_ADJUSTMENT_COUNT),
176 sum(AR_INVOICE_COUNT),
177 sum(AR_CASH_APPLIED_COUNT),
178 sum(AR_INVOICE_WRITEOFF_COUNT),
179 sum(AR_CREDIT_MEMO_COUNT),
180 sum(AR_UNAPPR_INVOICE_COUNT),
181 sum(AR_APPR_INVOICE_COUNT),
182 sum(AR_COUNT_DUE),
183 sum(AR_COUNT_OVERDUE)
184 from
185 (
186 select /*+ parallel(dinv) */ -- UBR, UER from draft invoices; functional currency only
187 dinv.PROJECT_ID,
188 dinv.PROJECT_ORG_ID,
189 dinv.PROJECT_ORGANIZATION_ID,
190 -1 TASK_ID,
191 dinv.CUSTOMER_ID,
192 greatest(to_number(to_char(dinv.GL_DATE,'J')),
193 l_min_date) GL_TIME_ID,
194 null GL_PERIOD_NAME,
195 greatest(to_number(to_char(dinv.PA_DATE,'J')),
196 l_min_date) PA_TIME_ID,
197 null PA_PERIOD_NAME,
198 null TXN_CURRENCY_CODE,
199 to_number(null) TXN_REVENUE,
200 to_number(null) TXN_FUNDING,
201 to_number(null) TXN_INITIAL_FUNDING_AMOUNT,
202 to_number(null) TXN_ADDITIONAL_FUNDING_AMOUNT,
203 to_number(null) TXN_CANCELLED_FUNDING_AMOUNT,
204 to_number(null) TXN_FUNDING_ADJUSTMENT_AMOUNT,
205 to_number(null) TXN_REVENUE_WRITEOFF,
206 to_number(null) TXN_AR_INVOICE_AMOUNT,
207 to_number(null) TXN_AR_CASH_APPLIED_AMOUNT,
208 to_number(null) TXN_AR_INVOICE_WRITEOFF_AMOUNT,
209 to_number(null) TXN_AR_CREDIT_MEMO_AMOUNT,
210 to_number(null) TXN_UNBILLED_RECEIVABLES,
211 to_number(null) TXN_UNEARNED_REVENUE,
212 to_number(null) TXN_AR_UNAPPR_INVOICE_AMOUNT,
213 to_number(null) TXN_AR_APPR_INVOICE_AMOUNT,
214 to_number(null) TXN_AR_AMOUNT_DUE,
215 to_number(null) TXN_AR_AMOUNT_OVERDUE,
216 to_number(null) PRJ_REVENUE,
217 to_number(null) PRJ_FUNDING,
218 to_number(null) PRJ_INITIAL_FUNDING_AMOUNT,
219 to_number(null) PRJ_ADDITIONAL_FUNDING_AMOUNT,
220 to_number(null) PRJ_CANCELLED_FUNDING_AMOUNT,
221 to_number(null) PRJ_FUNDING_ADJUSTMENT_AMOUNT,
222 to_number(null) PRJ_REVENUE_WRITEOFF,
223 to_number(null) PRJ_AR_INVOICE_AMOUNT,
224 to_number(null) PRJ_AR_CASH_APPLIED_AMOUNT,
225 to_number(null) PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
226 to_number(null) PRJ_AR_CREDIT_MEMO_AMOUNT,
227 to_number(null) PRJ_UNBILLED_RECEIVABLES,
228 to_number(null) PRJ_UNEARNED_REVENUE,
229 to_number(null) PRJ_AR_UNAPPR_INVOICE_AMOUNT,
230 to_number(null) PRJ_AR_APPR_INVOICE_AMOUNT,
231 to_number(null) PRJ_AR_AMOUNT_DUE,
232 to_number(null) PRJ_AR_AMOUNT_OVERDUE,
233 to_number(null) POU_REVENUE,
234 to_number(null) POU_FUNDING,
235 to_number(null) POU_INITIAL_FUNDING_AMOUNT,
236 to_number(null) POU_ADDITIONAL_FUNDING_AMOUNT,
237 to_number(null) POU_CANCELLED_FUNDING_AMOUNT,
238 to_number(null) POU_FUNDING_ADJUSTMENT_AMOUNT,
239 to_number(null) POU_REVENUE_WRITEOFF,
240 to_number(null) POU_AR_INVOICE_AMOUNT,
241 to_number(null) POU_AR_CASH_APPLIED_AMOUNT,
242 to_number(null) POU_AR_INVOICE_WRITEOFF_AMOUNT,
243 to_number(null) POU_AR_CREDIT_MEMO_AMOUNT,
244 dinv.UNBILLED_RECEIVABLE_DR POU_UNBILLED_RECEIVABLES,
245 dinv.UNEARNED_REVENUE_CR POU_UNEARNED_REVENUE,
246 to_number(null) POU_AR_UNAPPR_INVOICE_AMOUNT,
247 to_number(null) POU_AR_APPR_INVOICE_AMOUNT,
248 to_number(null) POU_AR_AMOUNT_DUE,
249 to_number(null) POU_AR_AMOUNT_OVERDUE,
250 to_number(null) INITIAL_FUNDING_COUNT,
251 to_number(null) ADDITIONAL_FUNDING_COUNT,
252 to_number(null) CANCELLED_FUNDING_COUNT,
253 to_number(null) FUNDING_ADJUSTMENT_COUNT,
254 to_number(null) AR_INVOICE_COUNT,
255 to_number(null) AR_CASH_APPLIED_COUNT,
256 to_number(null) AR_INVOICE_WRITEOFF_COUNT,
257 to_number(null) AR_CREDIT_MEMO_COUNT,
258 to_number(null) AR_UNAPPR_INVOICE_COUNT,
259 to_number(null) AR_APPR_INVOICE_COUNT,
260 to_number(null) AR_COUNT_DUE,
261 to_number(null) AR_COUNT_OVERDUE
262 from
263 PJI_FM_EXTR_DINVC dinv
264 where
265 dinv.WORKER_ID = p_worker_id
266 union all
267 select /*+ parallel(drev) */ -- UBR, UER from draft revenues; functional currency only
268 drev.PROJECT_ID,
269 drev.PROJECT_ORG_ID,
270 drev.PROJECT_ORGANIZATION_ID,
271 -1 TASK_ID,
272 drev.CUSTOMER_ID,
273 greatest(to_number(to_char(drev.GL_DATE,'J')),
274 l_min_date) GL_TIME_ID,
275 drev.GL_PERIOD_NAME,
276 greatest(to_number(to_char(drev.PA_DATE,'J')),
277 l_min_date) PA_TIME_ID,
278 drev.PA_PERIOD_NAME,
279 null TXN_CURRENCY_CODE,
280 to_number(null) TXN_REVENUE,
281 to_number(null) TXN_FUNDING,
282 to_number(null) TXN_INITIAL_FUNDING_AMOUNT,
283 to_number(null) TXN_ADDITIONAL_FUNDING_AMOUNT,
284 to_number(null) TXN_CANCELLED_FUNDING_AMOUNT,
285 to_number(null) TXN_FUNDING_ADJUSTMENT_AMOUNT,
286 to_number(null) TXN_REVENUE_WRITEOFF,
287 to_number(null) TXN_AR_INVOICE_AMOUNT,
288 to_number(null) TXN_AR_CASH_APPLIED_AMOUNT,
289 to_number(null) TXN_AR_INVOICE_WRITEOFF_AMOUNT,
290 to_number(null) TXN_AR_CREDIT_MEMO_AMOUNT,
291 to_number(null) TXN_UNBILLED_RECEIVABLES,
292 to_number(null) TXN_UNEARNED_REVENUE,
293 to_number(null) TXN_AR_UNAPPR_INVOICE_AMOUNT,
294 to_number(null) TXN_AR_APPR_INVOICE_AMOUNT,
295 to_number(null) TXN_AR_AMOUNT_DUE,
296 to_number(null) TXN_AR_AMOUNT_OVERDUE,
297 to_number(null) PRJ_REVENUE,
298 to_number(null) PRJ_FUNDING,
299 to_number(null) PRJ_INITIAL_FUNDING_AMOUNT,
300 to_number(null) PRJ_ADDITIONAL_FUNDING_AMOUNT,
301 to_number(null) PRJ_CANCELLED_FUNDING_AMOUNT,
302 to_number(null) PRJ_FUNDING_ADJUSTMENT_AMOUNT,
303 to_number(null) PRJ_REVENUE_WRITEOFF,
304 to_number(null) PRJ_AR_INVOICE_AMOUNT,
305 to_number(null) PRJ_AR_CASH_APPLIED_AMOUNT,
306 to_number(null) PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
307 to_number(null) PRJ_AR_CREDIT_MEMO_AMOUNT,
308 to_number(null) PRJ_UNBILLED_RECEIVABLES,
309 to_number(null) PRJ_UNEARNED_REVENUE,
310 to_number(null) PRJ_AR_UNAPPR_INVOICE_AMOUNT,
311 to_number(null) PRJ_AR_APPR_INVOICE_AMOUNT,
312 to_number(null) PRJ_AR_AMOUNT_DUE,
313 to_number(null) PRJ_AR_AMOUNT_OVERDUE,
314 to_number(null) POU_REVENUE,
315 to_number(null) POU_FUNDING,
319 to_number(null) POU_FUNDING_ADJUSTMENT_AMOUNT,
316 to_number(null) POU_INITIAL_FUNDING_AMOUNT,
317 to_number(null) POU_ADDITIONAL_FUNDING_AMOUNT,
318 to_number(null) POU_CANCELLED_FUNDING_AMOUNT,
320 to_number(null) POU_REVENUE_WRITEOFF,
321 to_number(null) POU_AR_INVOICE_AMOUNT,
322 to_number(null) POU_AR_CASH_APPLIED_AMOUNT,
323 to_number(null) POU_AR_INVOICE_WRITEOFF_AMOUNT,
324 to_number(null) POU_AR_CREDIT_MEMO_AMOUNT,
325 drev.POU_UBR POU_UNBILLED_RECEIVABLES,
326 drev.POU_UER POU_UNEARNED_REVENUE,
327 to_number(null) POU_AR_UNAPPR_INVOICE_AMOUNT,
328 to_number(null) POU_AR_APPR_INVOICE_AMOUNT,
329 to_number(null) POU_AR_AMOUNT_DUE,
330 to_number(null) POU_AR_AMOUNT_OVERDUE,
331 to_number(null) INITIAL_FUNDING_COUNT,
332 to_number(null) ADDITIONAL_FUNDING_COUNT,
333 to_number(null) CANCELLED_FUNDING_COUNT,
334 to_number(null) FUNDING_ADJUSTMENT_COUNT,
335 to_number(null) AR_INVOICE_COUNT,
336 to_number(null) AR_CASH_APPLIED_COUNT,
337 to_number(null) AR_INVOICE_WRITEOFF_COUNT,
338 to_number(null) AR_CREDIT_MEMO_COUNT,
339 to_number(null) AR_UNAPPR_INVOICE_COUNT,
340 to_number(null) AR_APPR_INVOICE_COUNT,
341 to_number(null) AR_COUNT_DUE,
342 to_number(null) AR_COUNT_OVERDUE
343 from
344 PJI_FM_EXTR_DREVN drev
345 where
346 drev.WORKER_ID = p_worker_id
347 union all
348 select /*+ parallel(fnd) */ -- funding in functional and project currencies
349 fnd.PROJECT_ID,
350 fnd.PROJECT_ORG_ID,
351 fnd.PROJECT_ORGANIZATION_ID,
352 -1 TASK_ID,
353 fnd.CUSTOMER_ID,
354 greatest(to_number(to_char(fnd.date_allocated,'J')),
355 l_min_date) GL_TIME_ID,
356 null GL_PERIOD_NAME,
357 greatest(to_number(to_char(fnd.date_allocated,'J')),
358 l_min_date) PA_TIME_ID,
359 null PA_PERIOD_NAME,
360 null TXN_CURRENCY_CODE,
361 to_number(null) TXN_REVENUE,
362 to_number(null) TXN_FUNDING,
363 to_number(null) TXN_INITIAL_FUNDING_AMOUNT,
364 to_number(null) TXN_ADDITIONAL_FUNDING_AMOUNT,
365 to_number(null) TXN_CANCELLED_FUNDING_AMOUNT,
366 to_number(null) TXN_FUNDING_ADJUSTMENT_AMOUNT,
367 to_number(null) TXN_REVENUE_WRITEOFF,
368 to_number(null) TXN_AR_INVOICE_AMOUNT,
369 to_number(null) TXN_AR_CASH_APPLIED_AMOUNT,
370 to_number(null) TXN_AR_INVOICE_WRITEOFF_AMOUNT,
371 to_number(null) TXN_AR_CREDIT_MEMO_AMOUNT,
372 to_number(null) TXN_UNBILLED_RECEIVABLES,
373 to_number(null) TXN_UNEARNED_REVENUE,
374 to_number(null) TXN_AR_UNAPPR_INVOICE_AMOUNT,
375 to_number(null) TXN_AR_APPR_INVOICE_AMOUNT,
376 to_number(null) TXN_AR_AMOUNT_DUE,
377 to_number(null) TXN_AR_AMOUNT_OVERDUE,
378 to_number(null) PRJ_REVENUE,
379 fnd.prj_allocated_amount PRJ_FUNDING,
380 decode(fnd.funding_category
381 , 'ORIGINAL' , fnd.prj_allocated_amount
382 , 'ADDITIONAL' , 0
383 , 'CANCELLATION' , 0
384 , 'CORRECTION' , 0
385 , 'TRANSFER' , 0
386 , 'REVALUATION' , 0
387 , fnd.prj_allocated_amount) PRJ_INITIAL_FUNDING_AMOUNT,
388 decode(fnd.funding_category
389 , 'ORIGINAL' , 0
390 , 'ADDITIONAL' , fnd.prj_allocated_amount
391 , 'CANCELLATION' , 0
392 , 'CORRECTION' , 0
393 , 'TRANSFER' , 0
394 , 'REVALUATION' , 0
398 , 'ADDITIONAL' , 0
395 , 0) PRJ_ADDITIONAL_FUNDING_AMOUNT,
396 decode(fnd.funding_category
397 , 'ORIGINAL' , 0
399 , 'CANCELLATION' , fnd.prj_allocated_amount
400 , 'CORRECTION' , 0
401 , 'TRANSFER' , 0
402 , 'REVALUATION' , 0
403 , 0) PRJ_CANCELLED_FUNDING_AMOUNT,
404 decode(fnd.funding_category
405 , 'ORIGINAL' , 0
406 , 'ADDITIONAL' , 0
407 , 'CANCELLATION' , 0
408 , 'CORRECTION' , fnd.prj_allocated_amount
409 , 'TRANSFER' , fnd.prj_allocated_amount
410 , 'REVALUATION' , fnd.prj_allocated_amount
411 , 0) PRJ_FUNDING_ADJUSTMENT_AMOUNT,
412 to_number(null) PRJ_REVENUE_WRITEOFF,
413 to_number(null) PRJ_AR_INVOICE_AMOUNT,
414 to_number(null) PRJ_AR_CASH_APPLIED_AMOUNT,
415 to_number(null) PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
416 to_number(null) PRJ_AR_CREDIT_MEMO_AMOUNT,
417 to_number(null) PRJ_UNBILLED_RECEIVABLES,
418 to_number(null) PRJ_UNEARNED_REVENUE,
419 to_number(null) PRJ_AR_UNAPPR_INVOICE_AMOUNT,
420 to_number(null) PRJ_AR_APPR_INVOICE_AMOUNT,
421 to_number(null) PRJ_AR_AMOUNT_DUE,
422 to_number(null) PRJ_AR_AMOUNT_OVERDUE,
423 to_number(null) POU_REVENUE,
424 fnd.pou_allocated_amount POU_FUNDING,
425 decode(fnd.funding_category
426 , 'ORIGINAL' , fnd.pou_allocated_amount
427 , 'ADDITIONAL' , 0
428 , 'CANCELLATION' , 0
429 , 'CORRECTION' , 0
430 , 'TRANSFER' , 0
431 , 'REVALUATION' , 0
432 , fnd.pou_allocated_amount) POU_INITIAL_FUNDING_AMOUNT,
433 decode(fnd.funding_category
434 , 'ORIGINAL' , 0
435 , 'ADDITIONAL' , fnd.pou_allocated_amount
436 , 'CANCELLATION' , 0
437 , 'CORRECTION' , 0
438 , 'TRANSFER' , 0
439 , 'REVALUATION' , 0
440 , 0) POU_ADDITIONAL_FUNDING_AMOUNT,
441 decode(fnd.funding_category
442 , 'ORIGINAL' , 0
443 , 'ADDITIONAL' , 0
444 , 'CANCELLATION' , fnd.pou_allocated_amount
445 , 'CORRECTION' , 0
446 , 'TRANSFER' , 0
447 , 'REVALUATION' , 0
448 , 0) POU_CANCELLED_FUNDING_AMOUNT,
449 decode(fnd.funding_category
450 , 'ORIGINAL' , 0
451 , 'ADDITIONAL' , 0
452 , 'CANCELLATION' , 0
453 , 'CORRECTION' , fnd.pou_allocated_amount
454 , 'TRANSFER' , fnd.pou_allocated_amount
455 , 'REVALUATION' , fnd.pou_allocated_amount
456 , 0) POU_FUNDING_ADJUSTMENT_AMOUNT,
457 to_number(null) POU_REVENUE_WRITEOFF,
458 to_number(null) POU_AR_INVOICE_AMOUNT,
459 to_number(null) POU_AR_CASH_APPLIED_AMOUNT,
460 to_number(null) POU_AR_INVOICE_WRITEOFF_AMOUNT,
461 to_number(null) POU_AR_CREDIT_MEMO_AMOUNT,
462 to_number(null) POU_UNBILLED_RECEIVABLES,
463 to_number(null) POU_UNEARNED_REVENUE,
464 to_number(null) POU_AR_UNAPPR_INVOICE_AMOUNT,
465 to_number(null) POU_AR_APPR_INVOICE_AMOUNT,
466 to_number(null) POU_AR_AMOUNT_DUE,
467 to_number(null) POU_AR_AMOUNT_OVERDUE,
468 decode(fnd.funding_category
469 , 'ORIGINAL' , 1
470 , 'ADDITIONAL' , 0
471 , 'CANCELLATION' , 0
472 , 'CORRECTION' , 0
473 , 'TRANSFER' , 0
474 , 'REVALUATION' , 0
475 , 1) INITIAL_FUNDING_COUNT,
476 decode(fnd.funding_category
477 , 'ORIGINAL' , 0
478 , 'ADDITIONAL' , 1
479 , 'CANCELLATION' , 0
480 , 'CORRECTION' , 0
481 , 'TRANSFER' , 0
482 , 'REVALUATION' , 0
483 , 0) ADDITIONAL_FUNDING_COUNT,
484 decode(fnd.funding_category
485 , 'ORIGINAL' , 0
486 , 'ADDITIONAL' , 0
487 , 'CANCELLATION' , 1
488 , 'CORRECTION' , 0
489 , 'TRANSFER' , 0
490 , 'REVALUATION' , 0
491 , 0) CANCELLED_FUNDING_COUNT,
495 , 'CANCELLATION' , 0
492 decode(fnd.funding_category
493 , 'ORIGINAL' , 0
494 , 'ADDITIONAL' , 0
496 , 'CORRECTION' , 1
497 , 'TRANSFER' , 1
498 , 'REVALUATION' , 1
499 , 0) FUNDING_ADJUSTMENT_COUNT,
500 to_number(null) AR_INVOICE_COUNT,
501 to_number(null) AR_CASH_APPLIED_COUNT,
502 to_number(null) AR_INVOICE_WRITEOFF_COUNT,
503 to_number(null) AR_CREDIT_MEMO_COUNT,
504 to_number(null) AR_UNAPPR_INVOICE_COUNT,
505 to_number(null) AR_APPR_INVOICE_COUNT,
506 to_number(null) AR_COUNT_DUE,
507 to_number(null) AR_COUNT_OVERDUE
508 from
509 PJI_FM_EXTR_FUNDG fnd
510 where
511 fnd.WORKER_ID = p_worker_id
512 union all
513 select /*+ parallel(fin1) */ -- FIN_TMP1 in functional and project currency
514 fin1.Project_ID,
515 fin1.Project_Org_ID,
516 fin1.Project_Organization_ID,
517 fin1.TASK_ID,
518 fin1.Customer_ID,
519 greatest(to_number(to_char(fin1.Recvr_GL_Date,'J')),
520 l_min_date) GL_TIME_ID,
521 fin1.GL_PERIOD_NAME,
522 greatest(to_number(to_char(fin1.Recvr_PA_Date,'J')),
523 l_min_date) PA_TIME_ID,
524 fin1.PA_PERIOD_NAME,
525 fin1.TXN_CURRENCY_CODE,
526 fin1.TXN_REVENUE,
527 to_number(null) TXN_FUNDING,
528 to_number(null) TXN_INITIAL_FUNDING_AMOUNT,
529 to_number(null) TXN_ADDITIONAL_FUNDING_AMOUNT,
530 to_number(null) TXN_CANCELLED_FUNDING_AMOUNT,
531 to_number(null) TXN_FUNDING_ADJUSTMENT_AMOUNT,
532 decode(fin1.event_type_classification,
533 'WRITE OFF', fin1.txn_revenue,
534 0) TXN_REVENUE_WRITEOFF,
535 to_number(null) TXN_AR_INVOICE_AMOUNT,
536 to_number(null) TXN_AR_CASH_APPLIED_AMOUNT,
537 to_number(null) TXN_AR_INVOICE_WRITEOFF_AMOUNT,
538 to_number(null) TXN_AR_CREDIT_MEMO_AMOUNT,
539 to_number(null) TXN_UNBILLED_RECEIVABLES,
540 to_number(null) TXN_UNEARNED_REVENUE,
541 to_number(null) TXN_AR_UNAPPR_INVOICE_AMOUNT,
542 to_number(null) TXN_AR_APPR_INVOICE_AMOUNT,
543 to_number(null) TXN_AR_AMOUNT_DUE,
544 to_number(null) TXN_AR_AMOUNT_OVERDUE,
545 fin1.Prj_Revenue PRJ_REVENUE,
549 to_number(null) PRJ_CANCELLED_FUNDING_AMOUNT,
546 to_number(null) PRJ_FUNDING,
547 to_number(null) PRJ_INITIAL_FUNDING_AMOUNT,
548 to_number(null) PRJ_ADDITIONAL_FUNDING_AMOUNT,
550 to_number(null) PRJ_FUNDING_ADJUSTMENT_AMOUNT,
551 decode(fin1.event_type_classification,
552 'WRITE OFF', fin1.prj_revenue,
553 0) PRJ_REVENUE_WRITEOFF,
554 to_number(null) PRJ_AR_INVOICE_AMOUNT,
555 to_number(null) PRJ_AR_CASH_APPLIED_AMOUNT,
556 to_number(null) PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
557 to_number(null) PRJ_AR_CREDIT_MEMO_AMOUNT,
558 to_number(null) PRJ_UNBILLED_RECEIVABLES,
559 to_number(null) PRJ_UNEARNED_REVENUE,
560 to_number(null) PRJ_AR_UNAPPR_INVOICE_AMOUNT,
561 to_number(null) PRJ_AR_APPR_INVOICE_AMOUNT,
562 to_number(null) PRJ_AR_AMOUNT_DUE,
563 to_number(null) PRJ_AR_AMOUNT_OVERDUE,
564 fin1.Pou_Revenue POU_REVENUE,
565 to_number(null) POU_FUNDING,
566 to_number(null) POU_INITIAL_FUNDING_AMOUNT,
567 to_number(null) POU_ADDITIONAL_FUNDING_AMOUNT,
568 to_number(null) POU_CANCELLED_FUNDING_AMOUNT,
569 to_number(null) POU_FUNDING_ADJUSTMENT_AMOUNT,
570 decode(fin1.event_type_classification,
571 'WRITE OFF', fin1.pou_revenue,
572 0) POU_REVENUE_WRITEOFF,
573 to_number(null) POU_AR_INVOICE_AMOUNT,
574 to_number(null) POU_AR_CASH_APPLIED_AMOUNT,
575 to_number(null) POU_AR_INVOICE_WRITEOFF_AMOUNT,
576 to_number(null) POU_AR_CREDIT_MEMO_AMOUNT,
577 fin1.POU_UBR POU_UNBILLED_RECEIVABLES,
578 fin1.POU_UER POU_UNEARNED_REVENUE,
579 to_number(null) POU_AR_UNAPPR_INVOICE_AMOUNT,
580 to_number(null) POU_AR_APPR_INVOICE_AMOUNT,
581 to_number(null) POU_AR_AMOUNT_DUE,
582 to_number(null) POU_AR_AMOUNT_OVERDUE,
583 to_number(null) INITIAL_FUNDING_COUNT,
584 to_number(null) ADDITIONAL_FUNDING_COUNT,
585 to_number(null) CANCELLED_FUNDING_COUNT,
586 to_number(null) FUNDING_ADJUSTMENT_COUNT,
587 to_number(null) AR_INVOICE_COUNT,
588 to_number(null) AR_CASH_APPLIED_COUNT,
589 to_number(null) AR_INVOICE_WRITEOFF_COUNT,
590 to_number(null) AR_CREDIT_MEMO_COUNT,
591 to_number(null) AR_UNAPPR_INVOICE_COUNT,
592 to_number(null) AR_APPR_INVOICE_COUNT,
593 to_number(null) AR_COUNT_DUE,
594 to_number(null) AR_COUNT_OVERDUE
595 from
596 PJI_FM_AGGR_FIN1 fin1
597 where
598 fin1.WORKER_ID = p_worker_id and
599 (fin1.PRJ_REVENUE <> 0 or fin1.POU_REVENUE <> 0)
600 union all
601 select /*+ parallel(dii) */
602 -- Draft invoice data in functional and project currency
603 -- For activities we use actual dates
604 -- For snapshots we use SYSDATE
605 dii.PROJECT_ID,
606 dii.PROJECT_ORG_ID,
607 dii.PROJECT_ORGANIZATION_ID,
608 -1 TASK_ID,
609 dii.CUSTOMER_ID,
610 decode(dii.pji_record_type,
611 'A', greatest(to_number(to_char(dii.GL_DATE,'J')),
612 l_min_date),
613 to_number(to_char(SYSDATE,'J'))) GL_TIME_ID,
614 null GL_PERIOD_NAME,
615 decode(dii.pji_record_type,
616 'A',greatest(to_number(to_char(dii.PA_DATE,'J')),
617 l_min_date),
618 to_number(to_char(SYSDATE,'J'))) PA_TIME_ID,
619 null GL_PERIOD_NAME,
620 null TXN_CURRENCY_CODE,
621 to_number(null) TXN_REVENUE,
622 to_number(null) TXN_FUNDING,
623 to_number(null) TXN_INITIAL_FUNDING_AMOUNT,
624 to_number(null) TXN_ADDITIONAL_FUNDING_AMOUNT,
625 to_number(null) TXN_CANCELLED_FUNDING_AMOUNT,
626 to_number(null) TXN_FUNDING_ADJUSTMENT_AMOUNT,
627 to_number(null) TXN_REVENUE_WRITEOFF,
628 to_number(null) TXN_AR_INVOICE_AMOUNT,
629 to_number(null) TXN_AR_CASH_APPLIED_AMOUNT,
630 to_number(null) TXN_AR_INVOICE_WRITEOFF_AMOUNT,
631 to_number(null) TXN_AR_CREDIT_MEMO_AMOUNT,
632 to_number(null) TXN_UNBILLED_RECEIVABLES,
633 to_number(null) TXN_UNEARNED_REVENUE,
634 to_number(null) TXN_AR_UNAPPR_INVOICE_AMOUNT,
638 to_number(null) PRJ_REVENUE,
635 to_number(null) TXN_AR_APPR_INVOICE_AMOUNT,
636 to_number(null) TXN_AR_AMOUNT_DUE,
637 to_number(null) TXN_AR_AMOUNT_OVERDUE,
639 to_number(null) PRJ_FUNDING,
640 to_number(null) PRJ_INITIAL_FUNDING_AMOUNT,
641 to_number(null) PRJ_ADDITIONAL_FUNDING_AMOUNT,
642 to_number(null) PRJ_CANCELLED_FUNDING_AMOUNT,
643 to_number(null) PRJ_FUNDING_ADJUSTMENT_AMOUNT,
644 to_number(null) PRJ_REVENUE_WRITEOFF,
645 decode(dii.pji_date_range_flag || '_' ||
646 dii.pji_record_type,
647 'Y_A', dii.prj_invoice_amount,
648 0) PRJ_AR_INVOICE_AMOUNT,
649 to_number(null) PRJ_AR_CASH_APPLIED_AMOUNT,
650 decode(dii.pji_date_range_flag || '_' ||
651 dii.pji_record_type || '_' ||
652 dii.write_off_flag,
653 'Y_A_Y', dii.prj_invoice_amount,
654 0) PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
655 decode(dii.pji_date_range_flag || '_' ||
656 dii.pji_record_type || '_' ||
657 dii.cancel_credit_memo_flag,
658 'Y_A_Y', dii.prj_invoice_amount,
659 0) PRJ_AR_CREDIT_MEMO_AMOUNT,
660 to_number(null) PRJ_UNBILLED_RECEIVABLES,
661 to_number(null) PRJ_UNEARNED_REVENUE,
662 decode(dii.pji_record_type || '_' ||
663 dii.approved_flag,
664 'S_N',dii.prj_invoice_amount,
665 0) PRJ_AR_UNAPPR_INVOICE_AMOUNT,
666 decode(dii.pji_record_type || '_' ||
667 dii.approved_flag,
668 'S_Y',dii.prj_invoice_amount,
669 0) PRJ_AR_APPR_INVOICE_AMOUNT,
670 to_number(null) PRJ_AR_AMOUNT_DUE,
671 to_number(null) PRJ_AR_AMOUNT_OVERDUE,
672 to_number(null) POU_REVENUE,
673 to_number(null) POU_FUNDING,
674 to_number(null) POU_INITIAL_FUNDING_AMOUNT,
675 to_number(null) POU_ADDITIONAL_FUNDING_AMOUNT,
676 to_number(null) POU_CANCELLED_FUNDING_AMOUNT,
677 to_number(null) POU_FUNDING_ADJUSTMENT_AMOUNT,
678 to_number(null) POU_REVENUE_WRITEOFF,
679 decode(dii.pji_date_range_flag || '_' ||
680 dii.pji_record_type,
681 'Y_A', dii.pou_invoice_amount,
682 0) POU_AR_INVOICE_AMOUNT,
683 to_number(null) POU_AR_CASH_APPLIED_AMOUNT,
684 decode(dii.pji_date_range_flag || '_' ||
685 dii.pji_record_type || '_' ||
686 dii.write_off_flag,
687 'Y_A_Y', dii.pou_invoice_amount,
688 0) POU_AR_INVOICE_WRITEOFF_AMOUNT,
689 decode(dii.pji_date_range_flag || '_' ||
690 dii.pji_record_type || '_' ||
691 dii.cancel_credit_memo_flag,
692 'Y_A_Y', dii.pou_invoice_amount,
693 0) POU_AR_CREDIT_MEMO_AMOUNT,
694 to_number(null) POU_UNBILLED_RECEIVABLES,
695 to_number(null) POU_UNEARNED_REVENUE,
696 decode(dii.pji_record_type || '_' ||
697 dii.approved_flag,
698 'S_N',dii.pou_invoice_amount,
699 0) POU_AR_UNAPPR_INVOICE_AMOUNT,
700 decode(dii.pji_record_type || '_' ||
704 to_number(null) POU_AR_AMOUNT_DUE,
701 dii.approved_flag,
702 'S_Y',dii.pou_invoice_amount,
703 0) POU_AR_APPR_INVOICE_AMOUNT,
705 to_number(null) POU_AR_AMOUNT_OVERDUE,
706 to_number(null) INITIAL_FUNDING_COUNT,
707 to_number(null) ADDITIONAL_FUNDING_COUNT,
708 to_number(null) CANCELLED_FUNDING_COUNT,
709 to_number(null) FUNDING_ADJUSTMENT_COUNT,
710 dii.AR_INVOICE_COUNT AR_INVOICE_COUNT,
711 to_number(null) AR_CASH_APPLIED_COUNT,
712 dii.AR_INVOICE_WRITEOFF_COUNT AR_INVOICE_WRITEOFF_COUNT,
713 dii.AR_CREDIT_MEMO_COUNT AR_CREDIT_MEMO_COUNT,
714 dii.AR_UNAPPR_INVOICE_COUNT AR_UNAPPR_INVOICE_COUNT,
715 dii.AR_APPR_INVOICE_COUNT AR_APPR_INVOICE_COUNT,
716 to_number(null) AR_COUNT_DUE,
717 to_number(null) AR_COUNT_OVERDUE
718 from
719 PJI_FM_EXTR_DINVCITM dii
720 where
721 dii.WORKER_ID = p_worker_id
722 union all
723 select /*+ parallel(ar) */ -- AR data in functional currency only
724 ar.PROJECT_ID,
725 ar.PROJECT_ORG_ID PROJECT_ORG_ID,
726 ar.PROJECT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID,
727 -1 TASK_ID,
728 ar.CUSTOMER_ID,
729 to_number(to_char(SYSDATE,'J')) GL_TIME_ID,
730 null GL_PERIOD_NAME,
731 to_number(to_char(SYSDATE,'J')) PA_TIME_ID,
732 null PA_PERIOD_NAME,
733 null TXN_CURRENCY_CODE,
734 to_number(null) TXN_REVENUE,
735 to_number(null) TXN_FUNDING,
736 to_number(null) TXN_INITIAL_FUNDING_AMOUNT,
737 to_number(null) TXN_ADDITIONAL_FUNDING_AMOUNT,
738 to_number(null) TXN_CANCELLED_FUNDING_AMOUNT,
739 to_number(null) TXN_FUNDING_ADJUSTMENT_AMOUNT,
740 to_number(null) TXN_REVENUE_WRITEOFF,
741 to_number(null) TXN_AR_INVOICE_AMOUNT,
742 to_number(null) TXN_AR_CASH_APPLIED_AMOUNT,
743 to_number(null) TXN_AR_INVOICE_WRITEOFF_AMOUNT,
744 to_number(null) TXN_AR_CREDIT_MEMO_AMOUNT,
745 to_number(null) TXN_UNBILLED_RECEIVABLES,
746 to_number(null) TXN_UNEARNED_REVENUE,
747 to_number(null) TXN_AR_UNAPPR_INVOICE_AMOUNT,
748 to_number(null) TXN_AR_APPR_INVOICE_AMOUNT,
749 to_number(null) TXN_AR_AMOUNT_DUE,
750 to_number(null) TXN_AR_AMOUNT_OVERDUE,
751 to_number(null) PRJ_REVENUE,
752 to_number(null) PRJ_FUNDING,
753 to_number(null) PRJ_INITIAL_FUNDING_AMOUNT,
754 to_number(null) PRJ_ADDITIONAL_FUNDING_AMOUNT,
755 to_number(null) PRJ_CANCELLED_FUNDING_AMOUNT,
756 to_number(null) PRJ_FUNDING_ADJUSTMENT_AMOUNT,
757 to_number(null) PRJ_REVENUE_WRITEOFF,
758 to_number(null) PRJ_AR_INVOICE_AMOUNT,
759 to_number(null) PRJ_AR_CASH_APPLIED_AMOUNT,
760 to_number(null) PRJ_AR_INVOICE_WRITEOFF_AMOUNT,
761 to_number(null) PRJ_AR_CREDIT_MEMO_AMOUNT,
762 to_number(null) PRJ_UNBILLED_RECEIVABLES,
763 to_number(null) PRJ_UNEARNED_REVENUE,
764 to_number(null) PRJ_AR_UNAPPR_INVOICE_AMOUNT,
765 to_number(null) PRJ_AR_APPR_INVOICE_AMOUNT,
766 to_number(null) PRJ_AR_AMOUNT_DUE,
767 to_number(null) PRJ_AR_AMOUNT_OVERDUE,
768 to_number(null) POU_REVENUE,
769 to_number(null) POU_FUNDING,
770 to_number(null) POU_INITIAL_FUNDING_AMOUNT,
771 to_number(null) POU_ADDITIONAL_FUNDING_AMOUNT,
772 to_number(null) POU_CANCELLED_FUNDING_AMOUNT,
773 to_number(null) POU_FUNDING_ADJUSTMENT_AMOUNT,
774 to_number(null) POU_REVENUE_WRITEOFF,
775 to_number(null) POU_AR_INVOICE_AMOUNT,
776 ar.cash_applied_amount POU_AR_CASH_APPLIED_AMOUNT,
777 to_number(null) POU_AR_INVOICE_WRITEOFF_AMOUNT,
778 to_number(null) POU_AR_CREDIT_MEMO_AMOUNT,
779 to_number(null) POU_UNBILLED_RECEIVABLES,
780 to_number(null) POU_UNEARNED_REVENUE,
781 to_number(null) POU_AR_UNAPPR_INVOICE_AMOUNT,
782 to_number(null) POU_AR_APPR_INVOICE_AMOUNT,
783 ar.amount_due_remaining POU_AR_AMOUNT_DUE,
784 ar.amount_overdue_remaining POU_AR_AMOUNT_OVERDUE,
785 to_number(null) INITIAL_FUNDING_COUNT,
786 to_number(null) ADDITIONAL_FUNDING_COUNT,
787 to_number(null) CANCELLED_FUNDING_COUNT,
788 to_number(null) FUNDING_ADJUSTMENT_COUNT,
792 to_number(null) AR_INVOICE_WRITEOFF_COUNT,
789 to_number(null) AR_INVOICE_COUNT,
790 to_number(null) AR_CASH_APPLIED_COUNT,
791 -- OPEN ISSUE: need to add support for AR_CASH_APPLIED_COUNT
793 to_number(null) AR_CREDIT_MEMO_COUNT,
794 to_number(null) AR_UNAPPR_INVOICE_COUNT,
795 to_number(null) AR_APPR_INVOICE_COUNT,
796 decode(sign(amount_overdue_remaining),
797 1, 0, decode(sign(ar.amount_due_remaining),
798 1, 1, 0), 0) AR_COUNT_DUE,
799 decode(sign(amount_overdue_remaining),
800 1, 1, 0) AR_COUNT_OVERDUE
801 from
802 PJI_FM_EXTR_ARINV ar
803 where
804 ar.WORKER_ID = p_worker_id
805 )
806 group by
807 PROJECT_ID,
808 PROJECT_ORG_ID,
809 PROJECT_ORGANIZATION_ID,
810 TASK_ID,
811 CUSTOMER_ID,
812 GL_TIME_ID,
813 GL_PERIOD_NAME,
814 PA_TIME_ID,
815 PA_PERIOD_NAME,
816 TXN_CURRENCY_CODE;
817
818 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_ACT.BASE_SUMMARY(p_worker_id);');
819
820 -- truncate intermediate tables no longer required
821 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
822 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_FUNDG' , 'NORMAL',null);
823 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DREVN' , 'NORMAL',null);
824 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVC' , 'NORMAL',null);
825 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVCITM' , 'NORMAL',null);
826 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_ARINV' , 'NORMAL',null);
827
828 commit;
829
830 end BASE_SUMMARY;
831
832
833 -- -----------------------------------------------------
834 -- procedure CLEANUP
835 -- -----------------------------------------------------
836 procedure CLEANUP (p_worker_id in number) is
837
838 l_schema varchar2(30);
839
840 begin
841
842 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
843
844 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_AGGR_ACT1', 'NORMAL',null);
845
846 end CLEANUP;
847
848 end PJI_FM_SUM_ACT;