[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_EXTR
Source
1 package body PJI_FM_EXTR as
2 /* $Header: PJISF06B.pls 120.9.12010000.2 2008/09/17 11:00:50 rballamu ship $ */
3
4 -- -----------------------------------------------------
5 -- procedure EXTRACT_BATCH_FND
6 -- -----------------------------------------------------
7 procedure EXTRACT_BATCH_FND (p_worker_id in number) is
8
9 l_process varchar2(30);
10 l_extraction_type varchar2(30);
11 l_from_project_id number := 0;
12 l_to_project_id number := 0;
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_EXTR.EXTRACT_BATCH_FND(p_worker_id);')) then
19 return;
20 end if;
21
22 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
23
24 INSERT /*+ APPEND */ INTO PJI_FM_EXTR_FUNDG
25 (
26 worker_id
27 ,project_org_id
28 ,project_organization_id
29 ,project_id
30 ,customer_id
31 ,date_allocated
32 ,funding_category
33 ,pou_allocated_amount
34 ,prj_allocated_amount
35 ,pji_summarized_flag
36 ,row_id
37 ,batch_id
38 )
39 SELECT /*+ ordered
40 full(bat) use_hash(bat) parallel(bat)
41 full(pf) use_hash(pf) parallel(pf)
42 full(arg) use_hash(agr) parallel(agr)
43 full(cust) use_hash(cust) parallel(cust) */
44 p_worker_id WORKER_ID
45 ,nvl(bat.project_org_id, -1) PROJECT_OU_ID
46 ,bat.project_organization_id PROJECT_ORG_ID
47 ,pf.project_id PROJECT_ID
48 ,agr.customer_id CUSTOMER_ID
49 ,trunc(pf.date_allocated) DATE_ALLOCATED
50 ,nvl(pf.funding_category,
51 PJI_FM_SUM_MAIN.g_null) FUNDING_CATEGORY
52 ,pf.projfunc_allocated_amount POU_ALLOCATED_AMOUNT
53 ,pf.project_allocated_amount PRJ_ALLOCATED_AMOUNT
54 ,pf.pji_summarized_flag PJI_SUMMARIZED_FLAG
55 ,pf.rowid ROW_ID
56 ,ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold) BATCH_ID
57 FROM pji_fm_proj_batch_map bat
58 ,pa_project_fundings pf
59 ,pa_agreements_all agr
60 ,pa_project_customers cust
61 WHERE l_extraction_type = 'FULL'
62 AND pf.agreement_id = agr.agreement_id
63 AND pf.project_id = bat.project_id
64 AND bat.worker_id = p_worker_id
65 AND bat.extraction_type = 'F'
66 AND pf.project_id = cust.project_id
67 AND pf.BUDGET_TYPE_CODE = 'BASELINE'
68 AND agr.customer_id = cust.customer_id
69 -- AND NVL(cust.bill_another_project_flag,'N') <> 'Y' -- ER 6519955
70 AND pf.date_allocated is not null
71 union all
72 SELECT /*+ ordered
73 full(bat)
74 index(pf,PA_PROJECT_FUNDINGS_N2) use_nl(pf)
75 */
76 p_worker_id WORKER_ID
77 ,nvl(bat.project_org_id, -1) PROJECT_OU_ID
78 ,bat.project_organization_id PROJECT_ORG_ID
79 ,pf.project_id PROJECT_ID
80 ,agr.customer_id CUSTOMER_ID
81 ,trunc(pf.date_allocated) DATE_ALLOCATED
82 ,nvl(pf.funding_category,
83 PJI_FM_SUM_MAIN.g_null) FUNDING_CATEGORY
84 ,pf.projfunc_allocated_amount POU_ALLOCATED_AMOUNT
85 ,pf.project_allocated_amount PRJ_ALLOCATED_AMOUNT
86 ,pf.pji_summarized_flag PJI_SUMMARIZED_FLAG
87 ,pf.rowid ROW_ID
88 ,ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold) BATCH_ID
89 FROM pji_fm_proj_batch_map bat
90 ,pa_project_fundings pf
91 ,pa_agreements_all agr
92 ,pa_project_customers cust
93 WHERE l_extraction_type = 'INCREMENTAL'
94 AND pf.agreement_id = agr.agreement_id
95 AND pf.project_id = bat.project_id
96 AND bat.worker_id = p_worker_id
97 AND bat.extraction_type = 'F'
98 AND pf.project_id = cust.project_id
99 AND pf.BUDGET_TYPE_CODE = 'BASELINE'
100 AND agr.customer_id = cust.customer_id
101 -- AND NVL(cust.bill_another_project_flag,'N') <> 'Y' -- ER 6519955
102 AND pf.date_allocated is not null
103 union all
104 SELECT /*+ ordered
105 index(pf, PA_PROJECT_FUNDINGS_N4)
106 */
107 p_worker_id WORKER_ID
108 ,nvl(bat.project_org_id, -1) PROJECT_OU_ID
109 ,bat.project_organization_id PROJECT_ORG_ID
110 ,pf.project_id PROJECT_ID
111 ,agr.customer_id CUSTOMER_ID
112 ,trunc(pf.date_allocated) DATE_ALLOCATED
113 ,nvl(pf.funding_category,
114 PJI_FM_SUM_MAIN.g_null) FUNDING_CATEGORY
115 ,pf.projfunc_allocated_amount POU_ALLOCATED_AMOUNT
116 ,pf.project_allocated_amount PRJ_ALLOCATED_AMOUNT
117 ,pf.pji_summarized_flag PJI_SUMMARIZED_FLAG
118 ,pf.rowid ROW_ID
119 ,ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold) BATCH_ID
120 FROM pji_fm_proj_batch_map bat
121 ,pa_project_fundings pf
122 ,pa_agreements_all agr
123 ,pa_project_customers cust
124 WHERE l_extraction_type = 'INCREMENTAL'
125 AND pf.agreement_id = agr.agreement_id
126 AND pf.project_id = bat.project_id
127 AND bat.worker_id = p_worker_id
128 AND bat.extraction_type = 'I'
129 AND pf.pji_summarized_flag = 'N'
130 AND pf.project_id = cust.project_id
131 AND pf.BUDGET_TYPE_CODE = 'BASELINE'
132 AND agr.customer_id = cust.customer_id
133 -- AND NVL(cust.bill_another_project_flag,'N') <> 'Y' -- ER 6519955
134 AND pf.date_allocated is not null
135 union all
136 SELECT /*+ ordered
137 full(bat) use_hash(bat) parallel(bat)
138 full(pf) use_hash(pf) parallel(pf)
139 full(arg) use_hash(agr) parallel(ag)
140 full(cust) use_hash(cust) parallel(cust) */
141 p_worker_id WORKER_ID
142 ,nvl(bat.project_org_id, -1) PROJECT_OU_ID
143 ,bat.project_organization_id PROJECT_ORG_ID
144 ,pf.project_id PROJECT_ID
145 ,agr.customer_id CUSTOMER_ID
146 ,trunc(pf.date_allocated) DATE_ALLOCATED
147 ,nvl(pf.funding_category,
148 PJI_FM_SUM_MAIN.g_null) FUNDING_CATEGORY
149 ,pf.projfunc_allocated_amount POU_ALLOCATED_AMOUNT
150 ,pf.project_allocated_amount PRJ_ALLOCATED_AMOUNT
151 ,pf.pji_summarized_flag PJI_SUMMARIZED_FLAG
152 ,pf.rowid ROW_ID
153 ,ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold) BATCH_ID
154 FROM pji_fm_proj_batch_map bat
155 ,pa_project_fundings pf
156 ,pa_agreements_all agr
157 ,pa_project_customers cust
158 WHERE l_extraction_type = 'PARTIAL'
159 AND pf.agreement_id = agr.agreement_id
160 AND pf.project_id = bat.project_id
161 AND bat.worker_id = p_worker_id
162 AND bat.extraction_type = 'P'
163 AND pf.project_id = cust.project_id
164 AND pf.BUDGET_TYPE_CODE = 'BASELINE'
165 AND agr.customer_id = cust.customer_id
166 -- AND NVL(cust.bill_another_project_flag,'N') <> 'Y' -- ER 6519955
167 AND pf.date_allocated is not null;
168
169 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_FND(p_worker_id);');
170
171 commit;
172
173 end EXTRACT_BATCH_FND;
174
175
176 -- -----------------------------------------------------
177 -- procedure MARK_EXTRACTED_FND_ROWS_PRE
178 -- -----------------------------------------------------
179 procedure MARK_EXTRACTED_FND_ROWS_PRE (p_worker_id in number) is
180
181 l_process varchar2(30);
182
183 begin
184
185 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
186
187 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
188 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS_PRE(p_worker_id);')) then
189 return;
190 end if;
191
192 insert /*+ append */ into PJI_HELPER_BATCH_MAP
193 (
194 BATCH_ID,
195 WORKER_ID,
196 STATUS
197 )
198 select
199 distinct
200 BATCH_ID,
201 null,
202 null
203 from
204 PJI_FM_EXTR_FUNDG
205 where
206 PJI_SUMMARIZED_FLAG is not null;
207
208 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
209 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS_PRE(p_worker_id);');
210
211 commit;
212
213 end MARK_EXTRACTED_FND_ROWS_PRE;
214
215
216 -- -----------------------------------------------------
217 -- procedure MARK_EXTRACTED_FND_ROWS
218 -- -----------------------------------------------------
219 procedure MARK_EXTRACTED_FND_ROWS (p_worker_id in number) is
220
221 l_process varchar2(30);
222 l_leftover_batches number;
223 l_helper_batch_id number;
224 l_row_count number;
225 l_parallel_processes number;
226
227 begin
228
229 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
230
231 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS(p_worker_id);')) then
232 return;
233 end if;
234
235 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
236 (PJI_FM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
237
238 select count(*)
239 into l_leftover_batches
240 from PJI_HELPER_BATCH_MAP
241 where WORKER_ID = p_worker_id and
242 STATUS = 'P';
243
244 l_helper_batch_id := 0;
245
246 while l_helper_batch_id >= 0 loop
247
248 if (l_leftover_batches > 0) then
249
250 l_leftover_batches := l_leftover_batches - 1;
251
252 select BATCH_ID
253 into l_helper_batch_id
254 from PJI_HELPER_BATCH_MAP
255 where WORKER_ID = p_worker_id and
256 STATUS = 'P' and
257 ROWNUM = 1;
258
259 else
260
261 update PJI_HELPER_BATCH_MAP
262 set WORKER_ID = p_worker_id,
263 STATUS = 'P'
264 where WORKER_ID is null and
265 ROWNUM = 1
266 returning BATCH_ID
267 into l_helper_batch_id;
268
269 end if;
270
271 if (sql%rowcount <> 0) then
272
273 commit;
274
275 update pa_project_fundings
276 set pji_summarized_flag = NULL
277 where rowid in (select /*+ cardinality(fnd, 1) */
278 fnd.row_id
279 from PJI_FM_EXTR_FUNDG fnd
280 where fnd.pji_summarized_flag is not null and
281 fnd.batch_id = l_helper_batch_id);
282
283 update PJI_HELPER_BATCH_MAP
284 set STATUS = 'C'
285 where WORKER_ID = p_worker_id and
286 BATCH_ID = l_helper_batch_id;
287
288 commit;
289
290 else
291
292 select count(*)
293 into l_row_count
294 from PJI_HELPER_BATCH_MAP
295 where nvl(STATUS, 'X') <> 'C';
296
297 if (l_row_count = 0) then
298
299 for x in 2 .. l_parallel_processes loop
300
301 update PJI_SYSTEM_PRC_STATUS
302 set STEP_STATUS = 'C'
303 where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
304 STEP_NAME =
305 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS(p_worker_id);' and
306 START_DATE is null;
307
308 commit;
309
310 end loop;
311
312 l_helper_batch_id := -1;
313
314 else
315
316 PJI_PROCESS_UTIL.SLEEP(1); -- so the CPU is not bombarded
317
318 end if;
319
320 end if;
321
322 if (l_helper_batch_id >= 0) then
323
324 for x in 2 .. l_parallel_processes loop
325 if (not PJI_FM_SUM_EXTR.WORKER_STATUS(x, 'OKAY')) then
326 l_helper_batch_id := -2;
327 end if;
328 end loop;
329
330 end if;
331
332 end loop;
333
334 if (l_helper_batch_id <> -2) then
335
336 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
337 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS(p_worker_id);');
338
339 end if;
340
341 commit;
342
343 end MARK_EXTRACTED_FND_ROWS;
344
345
346 -- -----------------------------------------------------
347 -- procedure MARK_EXTRACTED_FND_ROWS_POST
348 -- -----------------------------------------------------
349 procedure MARK_EXTRACTED_FND_ROWS_POST (p_worker_id in number) is
350
351 l_process varchar2(30);
352
353 begin
354
355 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
356
357 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
358 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS_POST(p_worker_id);')) then
359 return;
360 end if;
361
362 PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
363 'PJI_HELPER_BATCH_MAP',
364 'NORMAL',
365 null);
366
367 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
368 'PJI_FM_EXTR.MARK_EXTRACTED_FND_ROWS_POST(p_worker_id);');
369
370 commit;
371
372 end MARK_EXTRACTED_FND_ROWS_POST;
373
374
375 -- -----------------------------------------------------
376 -- procedure EXTRACT_BATCH_DREV
377 -- -----------------------------------------------------
378 procedure EXTRACT_BATCH_DREV (p_worker_id in number) is
379
380 l_process varchar2(30);
381 l_extraction_type varchar2(30);
382 l_from_project_id number := 0;
383 l_to_project_id number := 0;
384 l_transition_flag varchar2(1);
385
386 begin
387
388 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
389
390 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DREV(p_worker_id);')) then
391 return;
392 end if;
393
394 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
395
396 l_transition_flag :=
397 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_FM_SUM_MAIN.g_process,
398 'TRANSITION');
399
400 INSERT /*+ APPEND */ INTO PJI_FM_EXTR_DREVN
401 (
402 ROW_ID
403 , WORKER_ID
404 , LINE_SOURCE_TYPE
405 , POU_UBR
406 , POU_UER
407 , PROJECT_ORG_ID
408 , PROJECT_ORGANIZATION_ID
409 , PROJECT_ID
410 , PROJECT_TYPE_CLASS
411 , DRAFT_REVENUE_NUM
412 , AGREEMENT_ID
413 , PA_DATE
414 , PA_PERIOD_NAME
415 , GL_DATE
416 , GL_PERIOD_NAME
417 , LOG_EVENT_ID
418 , PJI_SUMMARIZED_FLAG
419 , CUSTOMER_ID
420 , BATCH_ID
421 )
422 SELECT /*+ ordered
423 full(bat) use_hash(bat) parallel(bat)
424 full(drv) use_hash(drv) parallel(drv)
425 full(agr) use_hash(agr) parallel(agr) */
426 drv.rowid row_id
427 , p_worker_id worker_id
428 , 'R' line_source_type
429 , drv.unbilled_receivable_dr POU_ubr
430 , drv.unearned_revenue_cr POU_uer
431 , nvl(bat.project_org_id, -1) project_org_id
432 , bat.project_organization_id project_organization_id /*also PSI */
433 , drv.project_id project_id
434 , bat.project_type_class project_type_class
435 , drv.draft_revenue_num draft_revenue_num
436 , drv.agreement_id agreement_id
437 , drv.pa_date pa_date
438 , drv.pa_period_name pa_period_name
439 , drv.gl_date gl_date
440 , drv.gl_period_name gl_period_name
441 , -1 log_event_id
442 , drv.pji_summarized_flag PJI_SUMMARIZED_FLAG
443 , agr.customer_id customer_id
444 , ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold) batch_id
445 FROM
446 pji_fm_proj_batch_map bat
447 , pa_draft_revenues_all drv
448 , pa_agreements_all agr
449 WHERE
450 l_extraction_type = 'FULL'
451 and bat.worker_id = p_worker_id
452 and bat.project_id = drv.project_id
453 and drv.released_date IS NOT NULL
454 and drv.transfer_status_code = 'A'
455 and bat.extraction_type = 'F'
456 and drv.gl_date is not null
457 and drv.pa_date is not null
458 and agr.agreement_id = drv.agreement_id
459 and ((nvl(l_transition_flag, 'N') = 'N') or
460 (nvl(l_transition_flag, 'N') = 'Y' and
461 nvl(drv.pji_summarized_flag, 'Y') <> 'N'))
462 union all
463 SELECT /*+ ordered
464 full(bat)
465 index(drv, PA_DRAFT_REVENUES_U1) use_nl(drv)
466 */
467 drv.rowid row_id
468 , p_worker_id worker_id
469 , 'R' line_source_type
470 , drv.unbilled_receivable_dr POU_ubr
471 , drv.unearned_revenue_cr POU_uer
472 , nvl(bat.project_org_id, -1) project_org_id
473 , bat.project_organization_id project_organization_id /*also PSI */
474 , drv.project_id project_id
475 , bat.project_type_class project_type_class
476 , drv.draft_revenue_num draft_revenue_num
477 , drv.agreement_id agreement_id
478 , drv.pa_date pa_date
479 , drv.pa_period_name pa_period_name
480 , drv.gl_date gl_date
481 , drv.gl_period_name gl_period_name
482 , -1 log_event_id
483 , drv.pji_summarized_flag PJI_SUMMARIZED_FLAG
484 , agr.customer_id customer_id
485 , ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold) batch_id
486 FROM
487 pji_fm_proj_batch_map bat
488 , pa_draft_revenues_all drv
489 , pa_agreements_all agr
490 WHERE
491 l_extraction_type = 'INCREMENTAL'
492 and bat.worker_id = p_worker_id
493 and bat.project_id = drv.project_id
494 and drv.released_date IS NOT NULL
495 and drv.transfer_status_code = 'A'
496 and bat.extraction_type = 'F'
497 and drv.gl_date is not null
498 and drv.pa_date is not null
499 and agr.agreement_id = drv.agreement_id
500 union all
501 SELECT /*+ ordered
502 full(bat) use_hash(bat)
503 index(drv,PA_DRAFT_REVENUES_N12)
504 */
505 drv.rowid row_id
506 , p_worker_id worker_id
507 , 'R' line_source_type
508 , drv.unbilled_receivable_dr POU_ubr
509 , drv.unearned_revenue_cr POU_uer
510 , nvl(bat.project_org_id, -1) project_org_id
511 , bat.project_organization_id project_organization_id /*also PSI */
512 , drv.project_id project_id
513 , bat.project_type_class project_type_class
514 , drv.draft_revenue_num draft_revenue_num
515 , drv.agreement_id agreement_id
516 , drv.pa_date pa_date
517 , drv.pa_period_name pa_period_name
518 , drv.gl_date gl_date
519 , drv.gl_period_name gl_period_name
520 , -1 log_event_id
521 , drv.pji_summarized_flag PJI_SUMMARIZED_FLAG
522 , agr.customer_id customer_id
523 , ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold) batch_id
524 FROM
525 pji_fm_proj_batch_map bat
526 , pa_draft_revenues_all drv
527 , pa_agreements_all agr
528 WHERE
529 l_extraction_type = 'INCREMENTAL'
530 and bat.worker_id = p_worker_id
531 and bat.project_id = drv.project_id
532 and drv.released_date IS NOT NULL
533 and drv.transfer_status_code = 'A'
534 and bat.extraction_type = 'I'
535 and drv.pji_summarized_flag = 'N'
536 and drv.gl_date is not null
537 and drv.pa_date is not null
538 and agr.agreement_id = drv.agreement_id
539 union all
540 SELECT /*+ ordered
541 full(bat) use_hash(bat) parallel(bat)
542 full(drv) use_hash(drv) parallel(drv)
543 full(agr) use_hash(agr) parallel(agr) */
544 drv.rowid row_id
545 , p_worker_id worker_id
546 , 'R' line_source_type
547 , drv.unbilled_receivable_dr POU_ubr
548 , drv.unearned_revenue_cr POU_uer
549 , nvl(bat.project_org_id, -1) project_org_id
550 , bat.project_organization_id project_organization_id /*also PSI */
551 , drv.project_id project_id
552 , bat.project_type_class project_type_class
553 , drv.draft_revenue_num draft_revenue_num
554 , drv.agreement_id agreement_id
555 , drv.pa_date pa_date
556 , drv.pa_period_name pa_period_name
557 , drv.gl_date gl_date
558 , drv.gl_period_name gl_period_name
559 , -1 log_event_id
560 , drv.pji_summarized_flag PJI_SUMMARIZED_FLAG
561 , agr.customer_id customer_id
562 , ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold) batch_id
563 FROM
564 pji_fm_proj_batch_map bat
565 , pa_draft_revenues_all drv
566 , pa_agreements_all agr
567 WHERE
568 l_extraction_type = 'PARTIAL'
569 and bat.worker_id = p_worker_id
570 and bat.project_id = drv.project_id
571 and drv.released_date IS NOT NULL
572 and drv.transfer_status_code = 'A'
573 and bat.extraction_type = 'P'
574 and drv.gl_date is not null
575 and drv.pa_date is not null
576 and agr.agreement_id = drv.agreement_id
577 union all
578 SELECT /*+ ordered
579 index(log, PA_PJI_PROJ_EVENTS_LOG_N1)
580 full(imp) use_hash(imp)
581 full(paprd) use_hash(paprd)
582 full(glprd) use_hash(glprd)
583 full(sob) use_hash(sob)
584 */
585 log.rowid row_id
586 , p_worker_id worker_id
587 , 'L' line_source_type
588 , -to_number(log.attribute2) POU_ubr
589 , -to_number(log.attribute3) POU_uer
590 , nvl(bat.project_org_id, -1) project_org_id
591 , bat.project_organization_id project_organization_id /* also PSI */
592 , to_number(log.event_object) project_id
593 , bat.project_type_class project_type_class
594 , to_number(log.attribute1) draft_revenue_num
595 , to_number(log.attribute4) agreement_id
596 , to_date(log.attribute5, PJI_FM_SUM_MAIN.g_date_mask) pa_date
597 , paprd.period_name pa_period_name
598 , to_date(log.attribute6, PJI_FM_SUM_MAIN.g_date_mask) gl_date
599 , glprd.period_name gl_period_name
600 , log.event_id log_event_id
601 , null pji_summarized_flag
602 , agr.customer_id customer_id
603 , ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold) batch_id
604 FROM
605 pa_pji_proj_events_log log
606 , pji_fm_proj_batch_map bat
607 , pa_agreements_all agr
608 /* Note:
609 * The tables below are not needed if Billing Team
610 * populates the PA and GL period_names while
611 * inserting records into the log table
612 */
613 , pa_implementations_all imp
614 , gl_periods paprd
615 , gl_periods glprd
616 , gl_sets_of_books sob
617 WHERE
618 l_extraction_type = 'INCREMENTAL'
619 and bat.worker_id = p_worker_id
620 and bat.project_id = log.event_object
621 and log.event_type = 'DRAFT_REVENUES'
622 and log.attribute5 is not null
623 and log.attribute6 is not null
624 and agr.agreement_id = to_number(log.attribute4)
625 and bat.extraction_type = 'I'
626 and nvl(bat.PROJECT_ORG_ID,-1) = nvl(imp.org_id ,-1)
627 and imp.period_set_name = paprd.period_set_name
628 and imp.pa_period_type = paprd.period_type
629 and to_date(log.attribute5, PJI_FM_SUM_MAIN.g_date_mask)
630 between paprd.START_DATE and paprd.END_DATE
631 and imp.period_set_name = glprd.period_set_name
632 and imp.set_of_books_id = sob.set_of_books_id
633 and sob.accounted_period_type = glprd.period_type
634 and to_date(log.attribute6, PJI_FM_SUM_MAIN.g_date_mask)
635 between glprd.START_DATE and glprd.END_DATE
636 ;
637
638 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DREV(p_worker_id);');
639
640 commit;
641
642 end EXTRACT_BATCH_DREV;
643
644
645 -- -----------------------------------------------------
646 -- procedure MARK_EXTRACTED_DREV_PRE
647 -- -----------------------------------------------------
648 procedure MARK_EXTRACTED_DREV_PRE (p_worker_id in number) is
649
650 l_process varchar2(30);
651
652 begin
653
654 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
655
656 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
657 'PJI_FM_EXTR.MARK_EXTRACTED_DREV_PRE(p_worker_id);')) then
658 return;
659 end if;
660
661 insert /*+ append */ into PJI_HELPER_BATCH_MAP
662 (
663 BATCH_ID,
664 WORKER_ID,
665 STATUS
666 )
667 select
668 distinct
669 BATCH_ID,
670 null,
671 null
672 from
673 PJI_FM_EXTR_DREVN
674 where
675 (LINE_SOURCE_TYPE = 'R' and
676 PJI_SUMMARIZED_FLAG is not null) or
677 (LINE_SOURCE_TYPE = 'L');
678
679 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
680 'PJI_FM_EXTR.MARK_EXTRACTED_DREV_PRE(p_worker_id);');
681
682 commit;
683
684 end MARK_EXTRACTED_DREV_PRE;
685
686
687 -- -----------------------------------------------------
688 -- procedure MARK_EXTRACTED_DREV
689 -- -----------------------------------------------------
690 procedure MARK_EXTRACTED_DREV (p_worker_id in number) is
691
692 l_process varchar2(30);
693 l_leftover_batches number;
694 l_helper_batch_id number;
695 l_row_count number;
696 l_parallel_processes number;
697
698 begin
699
700 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
701
702 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_DREV(p_worker_id);')) then
703 return;
704 end if;
705
706 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
707 (PJI_FM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
708
709 select count(*)
710 into l_leftover_batches
711 from PJI_HELPER_BATCH_MAP
712 where WORKER_ID = p_worker_id and
713 STATUS = 'P';
714
715 l_helper_batch_id := 0;
716
717 while l_helper_batch_id >= 0 loop
718
719 if (l_leftover_batches > 0) then
720
721 l_leftover_batches := l_leftover_batches - 1;
722
723 select BATCH_ID
724 into l_helper_batch_id
725 from PJI_HELPER_BATCH_MAP
726 where WORKER_ID = p_worker_id and
727 STATUS = 'P' and
728 ROWNUM = 1;
729
730 else
731
732 update PJI_HELPER_BATCH_MAP
733 set WORKER_ID = p_worker_id,
734 STATUS = 'P'
735 where WORKER_ID is null and
736 ROWNUM = 1
737 returning BATCH_ID
738 into l_helper_batch_id;
739
740 end if;
741
742 if (sql%rowcount <> 0) then
743
744 commit;
745
746 UPDATE pa_draft_revenues_all drv
747 SET drv.pji_summarized_flag = null
748 WHERE drv.rowid in (select /*+ cardinality(drvn, 1) */
749 drvn.row_id
750 from PJI_FM_EXTR_DREVN drvn
751 where drvn.pji_summarized_flag is not null
752 and drvn.LINE_SOURCE_TYPE = 'R'
753 and drvn.batch_id = l_helper_batch_id);
754
755 -- Clean up log table
756
757 DELETE pa_pji_proj_events_log
758 WHERE rowid in (select row_id
759 from PJI_FM_EXTR_DREVN
760 where line_source_type = 'L'
761 and batch_id = l_helper_batch_id);
762
763 update PJI_HELPER_BATCH_MAP
764 set STATUS = 'C'
765 where WORKER_ID = p_worker_id and
766 BATCH_ID = l_helper_batch_id;
767
768 commit;
769
770 else
771
772 select count(*)
773 into l_row_count
774 from PJI_HELPER_BATCH_MAP
775 where nvl(STATUS, 'X') <> 'C';
776
777 if (l_row_count = 0) then
778
779 for x in 2 .. l_parallel_processes loop
780
781 update PJI_SYSTEM_PRC_STATUS
782 set STEP_STATUS = 'C'
783 where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
784 STEP_NAME =
785 'PJI_FM_EXTR.MARK_EXTRACTED_DREV(p_worker_id);' and
786 START_DATE is null;
787
788 commit;
789
790 end loop;
791
792 l_helper_batch_id := -1;
793
794 else
795
796 PJI_PROCESS_UTIL.SLEEP(1); -- so the CPU is not bombarded
797
798 end if;
799
800 end if;
801
802 if (l_helper_batch_id >= 0) then
803
804 for x in 2 .. l_parallel_processes loop
805 if (not PJI_FM_SUM_EXTR.WORKER_STATUS(x, 'OKAY')) then
806 l_helper_batch_id := -2;
807 end if;
808 end loop;
809
810 end if;
811
812 end loop;
813
814 if (l_helper_batch_id <> -2) then
815
816 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
817 'PJI_FM_EXTR.MARK_EXTRACTED_DREV(p_worker_id);');
818
819 end if;
820
821 commit;
822
823 end MARK_EXTRACTED_DREV;
824
825
826 -- -----------------------------------------------------
827 -- procedure MARK_EXTRACTED_DREV_POST
828 -- -----------------------------------------------------
829 procedure MARK_EXTRACTED_DREV_POST (p_worker_id in number) is
830
831 l_process varchar2(30);
832
833 begin
834
835 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
836
837 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
838 'PJI_FM_EXTR.MARK_EXTRACTED_DREV_POST(p_worker_id);')) then
839 return;
840 end if;
841
842 PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
843 'PJI_HELPER_BATCH_MAP',
844 'NORMAL',
845 null);
846
847 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
848 'PJI_FM_EXTR.MARK_EXTRACTED_DREV_POST(p_worker_id);');
849
850 commit;
851
852 end MARK_EXTRACTED_DREV_POST;
853
854
855 -- -----------------------------------------------------
856 -- procedure EXTRACT_BATCH_CDL_CRDL_FULL
857 -- -----------------------------------------------------
858 procedure EXTRACT_BATCH_CDL_CRDL_FULL(p_worker_id in number) is
859
860 l_process varchar2(30);
861 l_from_project_id number := 0;
862 l_to_project_id number := 0;
863 l_min_date date;
864
865 begin
866
867 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
868
869 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_CRDL_FULL(p_worker_id);')) then
870 return;
871 end if;
872
873 l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
874 PJI_FM_SUM_MAIN.g_date_mask);
875
876 if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') = 'FULL') then
877
878 -- This cleanup is intentionally before the implicit commit so as not
879 -- to interfere with the CDL extraction.
880 if (nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
881 (PJI_FM_SUM_MAIN.g_process, 'CONFIG_PROJ_PERF_FLAG'),
882 'N') = 'N' and
883 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
884 (PJI_FM_SUM_MAIN.g_process, 'CONFIG_COST_FLAG'),
885 'N') = 'N' and
886 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
887 (PJI_FM_SUM_MAIN.g_process, 'CONFIG_PROFIT_FLAG'),
888 'N') = 'N') then
889 delete /*+ index (log, PA_PJI_PROJ_EVENTS_LOG_N1) */
890 from PA_PJI_PROJ_EVENTS_LOG log
891 where EVENT_TYPE = 'DRAFT_REVENUES';
892 end if;
893
894 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
895 'CURRENT_BATCH') = 1) then
896 -- implicit commit
897 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
898 tabname => 'PJI_FM_EXTR_DREVN',
899 percent => 10,
900 degree => PJI_UTILS.
901 GET_DEGREE_OF_PARALLELISM);
902 -- implicit commit
903 FND_STATS.GATHER_COLUMN_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
904 tabname => 'PJI_FM_EXTR_DREVN',
905 colname => 'PROJECT_ID',
906 percent => 10,
907 degree => PJI_UTILS.
908 GET_DEGREE_OF_PARALLELISM);
909 end if;
910
911 INSERT /*+ APPEND PARALLEL(fin1_i) */ INTO PJI_FM_AGGR_FIN1 fin1_i
912 ( WORKER_ID
913 , SLICE_ID
914 , PROJECT_ID
915 , TASK_ID
916 , PERSON_ID
917 , PROJECT_ORG_ID
918 , PROJECT_ORGANIZATION_ID
919 , PROJECT_TYPE_CLASS
920 , CUSTOMER_ID
921 , EXPENDITURE_ORG_ID
922 , EXPENDITURE_ORGANIZATION_ID
923 , JOB_ID
924 , VENDOR_ID
925 , WORK_TYPE_ID
926 , EXP_EVT_TYPE_ID
927 , EXPENDITURE_TYPE
928 , EVENT_TYPE
929 , EVENT_TYPE_CLASSIFICATION
930 , EXPENDITURE_CATEGORY
931 , REVENUE_CATEGORY
932 , NON_LABOR_RESOURCE
933 , BOM_LABOR_RESOURCE_ID
934 , BOM_EQUIPMENT_RESOURCE_ID
935 , INVENTORY_ITEM_ID
936 , PO_LINE_ID
937 , ASSIGNMENT_ID
938 , SYSTEM_LINKAGE_FUNCTION
939 , PJI_PROJECT_RECORD_FLAG
940 , PJI_RESOURCE_RECORD_FLAG
941 , CODE_COMBINATION_ID
942 , PRVDR_GL_DATE
943 , RECVR_GL_DATE
944 , GL_PERIOD_NAME
945 , PRVDR_PA_DATE
946 , RECVR_PA_DATE
947 , PA_PERIOD_NAME
948 , EXPENDITURE_ITEM_DATE
949 , TXN_CURRENCY_CODE
950 , TXN_REVENUE
951 , TXN_RAW_COST
952 , TXN_BILL_RAW_COST
953 , TXN_BURDENED_COST
954 , TXN_BILL_BURDENED_COST
955 , TXN_UBR
956 , TXN_UER
957 , PRJ_REVENUE
958 , PRJ_RAW_COST
959 , PRJ_BILL_RAW_COST
960 , PRJ_BURDENED_COST
961 , PRJ_BILL_BURDENED_COST
962 , PRJ_UBR
963 , PRJ_UER
964 , POU_REVENUE
965 , POU_RAW_COST
966 , POU_BILL_RAW_COST
967 , POU_BURDENED_COST
968 , POU_BILL_BURDENED_COST
969 , POU_UBR
970 , POU_UER
971 , EOU_RAW_COST
972 , EOU_BILL_RAW_COST
973 , EOU_BURDENED_COST
974 , EOU_BILL_BURDENED_COST
975 , EOU_UBR
976 , EOU_UER
977 , QUANTITY
978 , BILL_QUANTITY
979 )
980 SELECT
981 grp.WORKER_ID
982 , grp.SLICE_ID
983 , grp.PROJECT_ID
984 , grp.TASK_ID
985 , grp.PERSON_ID
986 , grp.PROJECT_ORG_ID
987 , grp.PROJECT_ORGANIZATION_ID
988 , grp.PROJECT_TYPE_CLASS
989 , grp.CUSTOMER_ID
990 , grp.EXPENDITURE_ORG_ID
991 , grp.EXPENDITURE_ORGANIZATION_ID
992 , grp.JOB_ID
993 , grp.VENDOR_ID
994 , grp.WORK_TYPE_ID
995 , grp.EXP_EVT_TYPE_ID
996 , grp.EXPENDITURE_TYPE
997 , grp.EVENT_TYPE
998 , grp.EVENT_TYPE_CLASSIFICATION
999 , grp.EXPENDITURE_CATEGORY
1000 , grp.REVENUE_CATEGORY
1001 , grp.NON_LABOR_RESOURCE
1002 , grp.BOM_LABOR_RESOURCE_ID
1003 , grp.BOM_EQUIPMENT_RESOURCE_ID
1004 , grp.INVENTORY_ITEM_ID
1005 , grp.PO_LINE_ID
1006 , grp.ASSIGNMENT_ID
1007 , grp.SYSTEM_LINKAGE_FUNCTION
1008 , grp.PJI_PROJECT_RECORD_FLAG
1009 , grp.PJI_RESOURCE_RECORD_FLAG
1010 , grp.CODE_COMBINATION_ID
1011 , grp.PRVDR_GL_DATE
1012 , grp.RECVR_GL_DATE
1013 , grp.GL_PERIOD_NAME
1014 , grp.PRVDR_PA_DATE
1015 , grp.RECVR_PA_DATE
1016 , grp.PA_PERIOD_NAME
1017 , grp.EXPENDITURE_ITEM_DATE
1018 , grp.TXN_CURRENCY_CODE
1019 , sum(grp.TXN_REVENUE)
1020 , sum(grp.TXN_RAW_COST)
1021 , sum(grp.TXN_BILL_RAW_COST)
1022 , sum(grp.TXN_BURDENED_COST)
1023 , sum(grp.TXN_BILL_BURDENED_COST)
1024 , sum(grp.TXN_UBR)
1025 , sum(grp.TXN_UER)
1026 , sum(grp.PRJ_REVENUE)
1027 , sum(grp.PRJ_RAW_COST)
1028 , sum(grp.PRJ_BILL_RAW_COST)
1029 , sum(grp.PRJ_BURDENED_COST)
1030 , sum(grp.PRJ_BILL_BURDENED_COST)
1031 , sum(grp.PRJ_UBR)
1032 , sum(grp.PRJ_UER)
1033 , sum(grp.POU_REVENUE)
1034 , sum(grp.POU_RAW_COST)
1035 , sum(grp.POU_BILL_RAW_COST)
1036 , sum(grp.POU_BURDENED_COST)
1037 , sum(grp.POU_BILL_BURDENED_COST)
1038 , sum(grp.POU_UBR)
1039 , sum(grp.POU_UER)
1040 , sum(grp.EOU_RAW_COST)
1041 , sum(grp.EOU_BILL_RAW_COST)
1042 , sum(grp.EOU_BURDENED_COST)
1043 , sum(grp.EOU_BILL_BURDENED_COST)
1044 , sum(grp.EOU_UBR)
1045 , sum(grp.EOU_UER)
1046 , sum(grp.QUANTITY)
1047 , sum(grp.BILL_QUANTITY)
1048 FROM (
1049 SELECT /*+ ORDERED
1050 use_hash(CnR,et,exp,ei)
1051 swap_join_inputs(exp)
1052 swap_join_inputs(ei)
1053 swap_join_inputs(et)
1054 PARALLEL(exp) PARALLEL(ei) PARALLEL(et) */
1055 p_worker_id AS WORKER_ID
1056 , 1 AS SLICE_ID
1057 , CnR.Project_ID AS PROJECT_ID
1058 , ei.Task_ID AS TASK_ID
1059 , decode(exp.Incurred_BY_Person_ID,
1060 null, -1, 0, -1,
1061 exp.Incurred_BY_Person_ID) AS PERSON_ID
1062 , map.Project_Org_ID AS PROJECT_ORG_ID
1063 , map.Project_Organization_ID AS PROJECT_ORGANIZATION_ID
1064 , map.Project_Type_Class AS PROJECT_TYPE_CLASS
1065 , CnR.Customer_ID AS CUSTOMER_ID
1066 , decode(CnR.C_or_R
1067 , 'COST', CnR.Expenditure_Org_ID
1068 , ei.org_id) AS EXPENDITURE_ORG_ID
1069 , nvl(ei.Override_TO_Organization_ID,
1070 exp.Incurred_BY_Organization_ID) AS EXPENDITURE_ORGANIZATION_ID
1071 -- , CnR.Expenditure_Item_ID AS EXPENDITURE_ITEM_ID
1072 , nvl(ei.Job_ID, -1) AS JOB_ID
1073 , nvl(exp.Vendor_ID,-1) AS VENDOR_ID
1074 , decode(CnR.C_or_R,
1075 'COST', nvl(CnR.Work_Type_Id,-1),
1076 nvl(ei.Work_Type_Id, -1)) AS WORK_TYPE_ID
1077 , et.Expenditure_Type_ID AS EXP_EVT_TYPE_ID
1078 , et.Expenditure_Type AS EXPENDITURE_TYPE
1079 , PJI_FM_SUM_MAIN.g_null AS EVENT_TYPE
1080 , PJI_FM_SUM_MAIN.g_null AS EVENT_TYPE_CLASSIFICATION
1081 , et.Expenditure_Category AS EXPENDITURE_CATEGORY
1082 , et.Revenue_Category_Code AS REVENUE_CATEGORY
1083 , ei.Non_Labor_Resource AS NON_LABOR_RESOURCE
1084 , ei.Wip_Resource_ID AS BOM_LABOR_RESOURCE_ID
1085 , ei.Wip_Resource_ID AS BOM_EQUIPMENT_RESOURCE_ID
1086 , ei.Inventory_Item_ID AS INVENTORY_ITEM_ID
1087 , ei.PO_Line_ID AS PO_LINE_ID
1088 , decode(ei.Assignment_ID,
1089 null, -1, 0, -1,
1090 ei.Assignment_ID) AS ASSIGNMENT_ID
1091 , NVL(ei.src_system_linkage_function,
1092 ei.system_linkage_function) AS SYSTEM_LINKAGE_FUNCTION
1093 , decode(CnR.C_or_R,
1094 'COST', 'Y',
1095 'REVENUE', 'Y', 'N') AS PJI_PROJECT_RECORD_FLAG
1096 , decode(exp.Incurred_BY_Person_ID,
1097 null, 'N',
1098 0, 'N',
1099 decode(CnR.C_or_R,
1100 'COST', 'Y',
1101 'REVENUE', 'Y',
1102 'N')) AS PJI_RESOURCE_RECORD_FLAG
1103 , -1 AS CODE_COMBINATION_ID
1104 , greatest(CnR.Prvdr_GL_Date,l_min_date) AS PRVDR_GL_DATE
1105 , greatest(CnR.Recvr_GL_Date,l_min_date) AS RECVR_GL_DATE
1106 , CnR.GL_Period_Name AS GL_PERIOD_NAME
1107 , greatest(CnR.Prvdr_PA_Date,l_min_date) AS PRVDR_PA_DATE
1108 , greatest(CnR.Recvr_PA_Date,l_min_date) AS RECVR_PA_DATE
1109 , CnR.PA_Period_Name AS PA_PERIOD_NAME
1110 , greatest(ei.Expenditure_Item_Date,
1111 l_min_date) AS EXPENDITURE_ITEM_DATE
1112 , CnR.Txn_Currency_Code AS TXN_CURRENCY_CODE
1113 , CnR.Txn_Revenue AS TXN_REVENUE
1114 , CnR.Txn_Raw_Cost AS TXN_RAW_COST
1115 , CnR.Txn_Bill_Raw_Cost AS TXN_BILL_RAW_COST
1116 , CnR.Txn_Burdened_Cost AS TXN_BURDENED_COST
1117 , CnR.Txn_Bill_Burdened_Cost AS TXN_BILL_BURDENED_COST
1118 , CnR.Txn_Ubr AS TXN_UBR
1119 , CnR.Txn_Uer AS TXN_UER
1120 , CnR.Prj_Revenue AS PRJ_REVENUE
1121 , CnR.Prj_Raw_Cost AS PRJ_RAW_COST
1122 , CnR.Prj_Bill_Raw_Cost AS PRJ_BILL_RAW_COST
1123 , CnR.Prj_Burdened_Cost AS PRJ_BURDENED_COST
1124 , CnR.Prj_Bill_Burdened_Cost AS PRJ_BILL_BURDENED_COST
1125 , CnR.Prj_Ubr AS PRJ_UBR
1126 , CnR.Prj_Uer AS PRJ_UER
1127 , CnR.Pou_Revenue AS POU_REVENUE
1128 , CnR.Pou_Raw_Cost AS POU_RAW_COST
1129 , CnR.Pou_Bill_Raw_Cost AS POU_BILL_RAW_COST
1130 , CnR.Pou_Burdened_Cost AS POU_BURDENED_COST
1131 , CnR.Pou_Bill_Burdened_Cost AS POU_BILL_BURDENED_COST
1132 , CnR.Pou_Ubr AS POU_UBR
1133 , CnR.Pou_Uer AS POU_UER
1134 , CnR.Eou_Raw_Cost AS EOU_RAW_COST
1135 , CnR.Eou_Bill_Raw_Cost AS EOU_BILL_RAW_COST
1136 , CnR.Eou_Burdened_Cost AS EOU_BURDENED_COST
1137 , CnR.Eou_Bill_Burdened_Cost AS EOU_BILL_BURDENED_COST
1138 , CnR.Eou_Ubr AS EOU_UBR
1139 , CnR.Eou_Uer AS EOU_UER
1140 , CnR.Quantity AS QUANTITY
1141 , CnR.Bill_Quantity AS BILL_QUANTITY
1142 FROM
1143 pji_fm_proj_batch_map map,
1144 (
1145 Select /*+ FULL(cdl) PARALLEL(cdl) */
1146 'COST' AS C_or_R
1147 , cdl.Project_ID AS PROJECT_ID
1148 , cdl.Task_ID AS TASK_ID
1149 , -1 AS CUSTOMER_ID
1150 , cdl.Org_ID AS EXPENDITURE_ORG_ID
1151 , cdl.Expenditure_Item_ID AS EXPENDITURE_ITEM_ID
1152 --- , nvl(to_number(cdl.System_Reference1),-1) AS VENDOR_ID
1153 , cdl.work_type_id AS WORK_TYPE_ID
1154 , cdl.GL_Date AS PRVDR_GL_DATE
1155 , nvl(cdl.Recvr_GL_Date,cdl.GL_Date) AS RECVR_GL_DATE
1156 , cdl.Recvr_GL_Period_Name AS GL_PERIOD_NAME
1157 , cdl.PA_DATE AS PRVDR_PA_DATE
1158 , nvl(cdl.Recvr_PA_Date,cdl.PA_Date) AS RECVR_PA_DATE
1159 , cdl.Recvr_PA_Period_Name AS PA_PERIOD_NAME
1160 , cdl.Denom_Currency_Code AS TXN_CURRENCY_CODE
1161 , to_number(null) AS TXN_REVENUE
1162 , nvl(cdl.Denom_Raw_Cost, 0) AS TXN_RAW_COST
1163 , decode(cdl.billable_flag
1164 , 'Y', nvl(cdl.Denom_Raw_Cost, 0)
1165 , 0) AS TXN_BILL_RAW_COST
1166 , nvl(cdl.Denom_Burdened_Cost, 0) AS TXN_BURDENED_COST
1167 , decode(cdl.Billable_Flag
1168 , 'Y', nvl(cdl.Denom_Burdened_Cost, 0)
1169 , 0) AS TXN_BILL_BURDENED_COST
1170 , to_number(null) AS TXN_UBR
1171 , to_number(null) AS TXN_UER
1172 , to_number(null) AS PRJ_REVENUE
1173 , nvl(cdl.Project_Raw_Cost, 0) AS PRJ_RAW_COST
1174 , decode(cdl.billable_flag
1175 , 'Y', nvl(cdl.Project_Raw_Cost, 0)
1176 , 0) AS PRJ_BILL_RAW_COST
1177 , nvl(cdl.Project_Burdened_Cost, 0) AS PRJ_BURDENED_COST
1178 , decode(cdl.Billable_Flag
1179 , 'Y', nvl(cdl.Project_Burdened_Cost, 0)
1180 , 0) AS PRJ_BILL_BURDENED_COST
1181 , to_number(null) AS PRJ_UBR
1182 , to_number(null) AS PRJ_UER
1183 , to_number(null) AS POU_REVENUE
1184 , cdl.AMOUNT AS POU_RAW_COST
1185 , decode(cdl.bILLABLE_fLAG
1186 , 'Y', nvl(cdl.Amount, 0)
1187 , 0) AS POU_BILL_RAW_COST
1188 , nvl(cdl.Burdened_Cost, 0) AS POU_BURDENED_COST
1189 , decode(cdl.Billable_Flag
1190 , 'Y', nvl(cdl.Burdened_Cost, 0)
1191 , 0) AS POU_BILL_BURDENED_COST
1192 , to_number(null) AS POU_UBR
1193 , to_number(null) AS POU_UER
1194 , nvl(cdl.Acct_Raw_Cost, 0) AS EOU_RAW_COST
1195 , decode(cdl.Billable_Flag
1196 , 'Y', nvl(cdl.Acct_Raw_Cost,0)
1197 , 0) AS EOU_BILL_RAW_COST
1198 , nvl(cdl.Acct_Burdened_Cost, 0) AS EOU_BURDENED_COST
1199 , decode(cdl.Billable_Flag
1200 , 'Y', nvl(cdl.Acct_Burdened_Cost, 0)
1201 , 0) AS EOU_BILL_BURDENED_COST
1202 , to_number(null) AS EOU_UBR
1203 , to_number(null) AS EOU_UER
1204 , cdl.Quantity AS QUANTITY
1205 , decode(cdl.Billable_Flag
1206 , 'Y', cdl.Quantity
1207 , 0) AS BILL_QUANTITY
1208 From pa_cost_distribution_lines_all cdl
1209 Where 1 = 1
1210 And cdl.line_type in ('R','I')
1211 And nvl(cdl.pji_summarized_flag,'Y') <> 'N'
1212 And cdl.gl_date is not null
1213 And cdl.pa_date is not null
1214 UNION ALL
1215 Select /*+ ORDERED
1216 FULL(ag) PARALLEL(ag) use_hash(ag)
1217 FULL(cust) PARALLEL(cust) use_hash(cust)
1218 FULL(drev) PARALLEL(drev) use_hash(drev)
1219 FULL(crdl) PARALLEL(crdl) use_hash(crdl) */
1220 'REVENUE' AS C_or_R
1221 , crdl.Project_ID AS PROJECT_ID
1222 , -1 AS TASK_ID
1223 , cust.Customer_ID AS CUSTOMER_ID
1224 , -1 AS EXPENDITURE_ORG_ID
1225 , crdl.Expenditure_Item_ID AS EXPENDITURE_ITEM_ID
1226 --- , -1 AS VENDOR_ID
1227 , -1 AS WORK_TYPE_ID
1228 , drev.GL_Date AS PRVDR_GL_DATE
1229 , drev.GL_Date AS RECVR_GL_DATE
1230 , drev.GL_Period_Name AS GL_PERIOD_NAME
1231 , drev.PA_Date AS PRVDR_PA_DATE
1232 , drev.PA_Date AS RECVR_PA_DATE
1233 , drev.PA_Period_Name AS PA_PERIOD_NAME
1234 , crdl.Funding_Currency_Code AS TXN_CURRENCY_CODE
1235 , crdl.Funding_Revenue_Amount AS TXN_REVENUE
1236 , to_number(null) AS TXN_RAW_COST
1237 , to_number(null) AS TXN_BILL_RAW_COST
1238 , to_number(null) AS TXN_BURDENED_COST
1239 , to_number(null) AS TXN_BILL_BURDENED_COST
1240 , to_number(null) AS TXN_UBR
1241 , to_number(null) AS TXN_UER
1242 , crdl.Project_Revenue_Amount AS PRJ_REVENUE
1243 , to_number(null) AS PRJ_RAW_COST
1244 , to_number(null) AS PRJ_BILL_RAW_COST
1245 , to_number(null) AS PRJ_BURDENED_COST
1246 , to_number(null) AS PRJ_BILL_BURDENED_COST
1247 , to_number(null) AS PRJ_UBR
1248 , to_number(null) AS PRJ_UER
1249 , crdl.Projfunc_Revenue_Amount AS POU_REVENUE
1250 , to_number(null) AS POU_RAW_COST
1251 , to_number(null) AS POU_BILL_RAW_COST
1252 , to_number(null) AS POU_BURDENED_COST
1253 , to_number(null) AS POU_BILL_BURDENED_COST
1254 , to_number(null) AS POU_UBR
1255 , to_number(null) AS POU_UER
1256 , to_number(null) AS EOU_RAW_COST
1257 , to_number(null) AS EOU_BILL_RAW_COST
1258 , to_number(null) AS EOU_BURDENED_COST
1259 , to_number(null) AS EOU_BILL_BURDENED_COST
1260 , to_number(null) AS EOU_UBR
1261 , to_number(null) AS EOU_UER
1262 , to_number(null) AS QUANTITY
1263 , to_number(null) AS BILL_QUANTITY
1264 From PJI_FM_EXTR_DREVN drev
1265 , pa_agreements_all ag
1266 , pa_project_customers cust
1267 , pa_cust_rev_dist_lines_all crdl
1268 Where 1 = 1
1269 And drev.worker_id = p_worker_id
1270 And drev.project_id = crdl.project_id
1271 And drev.draft_revenue_num = crdl.draft_revenue_num
1272 And drev.agreement_id = ag.agreement_id
1273 And drev.project_id = cust.project_id
1274 And ag.customer_id = cust.customer_id
1275 -- And NVL(cust.bill_another_project_flag,'N') <> 'Y' -- ER 6519955
1276 And crdl.function_code NOT IN ('LRL','LRB','URL','URB')
1277 And drev.gl_date is not null
1278 And drev.pa_date is not null
1279 ) CnR
1280 , pa_expenditure_items_all ei
1281 , pa_expenditures_all exp
1282 , pa_expenditure_types et
1283 WHERE 1 = 1
1284 And CnR.expenditure_item_id = ei.expenditure_item_id
1285 And exp.expenditure_id = ei.expenditure_id
1286 And ei.expenditure_type = et.expenditure_type
1287 And CnR.project_id = map.project_id
1288 -- And (NVL(ei.transaction_source,'dummy') <> 'INTERPROJECT_AP_INVOICES' -- ER 6519955
1289 -- OR CnR.C_or_R = 'REVENUE')
1290 ) grp
1291 GROUP BY
1292 grp.WORKER_ID
1293 , grp.SLICE_ID
1294 , grp.PROJECT_ID
1295 , grp.TASK_ID
1296 , grp.PERSON_ID
1297 , grp.PROJECT_ORG_ID
1298 , grp.PROJECT_ORGANIZATION_ID
1299 , grp.PROJECT_TYPE_CLASS
1300 , grp.CUSTOMER_ID
1301 , grp.EXPENDITURE_ORG_ID
1302 , grp.EXPENDITURE_ORGANIZATION_ID
1303 , grp.JOB_ID
1304 , grp.VENDOR_ID
1305 , grp.WORK_TYPE_ID
1306 , grp.EXP_EVT_TYPE_ID
1307 , grp.EXPENDITURE_TYPE
1308 , grp.EVENT_TYPE
1309 , grp.EVENT_TYPE_CLASSIFICATION
1310 , grp.EXPENDITURE_CATEGORY
1311 , grp.REVENUE_CATEGORY
1312 , grp.NON_LABOR_RESOURCE
1313 , grp.BOM_LABOR_RESOURCE_ID
1314 , grp.BOM_EQUIPMENT_RESOURCE_ID
1315 , grp.INVENTORY_ITEM_ID
1316 , grp.PO_LINE_ID
1317 , grp.ASSIGNMENT_ID
1318 , grp.SYSTEM_LINKAGE_FUNCTION
1319 , grp.PJI_PROJECT_RECORD_FLAG
1320 , grp.PJI_RESOURCE_RECORD_FLAG
1321 , grp.CODE_COMBINATION_ID
1322 , grp.PRVDR_GL_DATE
1323 , grp.RECVR_GL_DATE
1324 , grp.GL_PERIOD_NAME
1325 , grp.PRVDR_PA_DATE
1326 , grp.RECVR_PA_DATE
1327 , grp.PA_PERIOD_NAME
1328 , grp.EXPENDITURE_ITEM_DATE
1329 , grp.TXN_CURRENCY_CODE
1330 ;
1331
1332 end if; -- EXTRACTION_TYPE = 'FULL'
1333
1334 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_CRDL_FULL(p_worker_id);');
1335
1336 commit;
1337
1338 end EXTRACT_BATCH_CDL_CRDL_FULL;
1339
1340
1341 -- -----------------------------------------------------
1342 -- procedure EXTRACT_BATCH_ERDL_FULL
1343 -- -----------------------------------------------------
1344 procedure EXTRACT_BATCH_ERDL_FULL(p_worker_id in number) is
1345
1346 l_process varchar2(30);
1347 l_from_project_id number := 0;
1348 l_to_project_id number := 0;
1349 l_min_date date;
1350
1351 begin
1352
1353 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1354
1355 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_FULL(p_worker_id);')) then
1356 return;
1357 end if;
1358
1359 l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
1360 PJI_FM_SUM_MAIN.g_date_mask);
1361
1362 if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') = 'FULL') then
1363
1364 -- insert for erdl
1365 INSERT /*+ APPEND */ INTO PJI_FM_AGGR_FIN1
1366 ( WORKER_ID
1367 , SLICE_ID
1368 , PROJECT_ID
1369 , TASK_ID
1370 , PERSON_ID
1371 , PROJECT_ORG_ID
1372 , PROJECT_ORGANIZATION_ID
1373 , PROJECT_TYPE_CLASS
1374 , CUSTOMER_ID
1375 , EXPENDITURE_ORG_ID
1376 , EXPENDITURE_ORGANIZATION_ID
1377 , JOB_ID
1378 , VENDOR_ID
1379 , WORK_TYPE_ID
1380 , EXP_EVT_TYPE_ID
1381 , EXPENDITURE_TYPE
1382 , EVENT_TYPE
1383 , EVENT_TYPE_CLASSIFICATION
1384 , EXPENDITURE_CATEGORY
1385 , REVENUE_CATEGORY
1386 , NON_LABOR_RESOURCE
1387 , BOM_LABOR_RESOURCE_ID
1388 , BOM_EQUIPMENT_RESOURCE_ID
1389 , INVENTORY_ITEM_ID
1390 , SYSTEM_LINKAGE_FUNCTION
1391 , PJI_PROJECT_RECORD_FLAG
1392 , PJI_RESOURCE_RECORD_FLAG
1393 , CODE_COMBINATION_ID
1394 , PRVDR_GL_DATE
1395 , RECVR_GL_DATE
1396 , GL_PERIOD_NAME
1397 , PRVDR_PA_DATE
1398 , RECVR_PA_DATE
1399 , PA_PERIOD_NAME
1400 , TXN_CURRENCY_CODE
1401 , TXN_REVENUE
1402 , TXN_RAW_COST
1403 , TXN_BILL_RAW_COST
1404 , TXN_BURDENED_COST
1405 , TXN_BILL_BURDENED_COST
1406 , TXN_UBR
1407 , TXN_UER
1408 , PRJ_REVENUE
1409 , PRJ_RAW_COST
1410 , PRJ_BILL_RAW_COST
1411 , PRJ_BURDENED_COST
1412 , PRJ_BILL_BURDENED_COST
1413 , PRJ_UBR
1414 , PRJ_UER
1415 , POU_REVENUE
1416 , POU_RAW_COST
1417 , POU_BILL_RAW_COST
1418 , POU_BURDENED_COST
1419 , POU_BILL_BURDENED_COST
1420 , POU_UBR
1421 , POU_UER
1422 , EOU_RAW_COST
1423 , EOU_BILL_RAW_COST
1424 , EOU_BURDENED_COST
1425 , EOU_BILL_BURDENED_COST
1426 , EOU_UBR
1427 , EOU_UER
1428 , QUANTITY
1429 , BILL_QUANTITY
1430 )
1431 Select /*+ PARALLEL(drev) FULL(drev)
1432 PARALLEL(erdl) FULL(erdl) */
1433 p_worker_id AS WORKER_ID
1434 , 1 AS SLICE_ID
1435 , erdl.Project_ID AS PROJECT_ID
1436 , nvl(ev.task_id, -1) AS TASK_ID -- Bug 6065483
1437 , -1 AS PERSON_ID
1438 , drev.Project_Org_ID AS PROJECT_ORG_ID
1439 , drev.Project_Organization_ID AS PROJECT_ORGANIZATION_ID
1440 , drev.Project_Type_Class AS PROJECT_TYPE_CLASS
1441 , cust.Customer_ID AS CUSTOMER_ID
1442 , -1 AS EXPENDITURE_ORG_ID
1443 , ev.Organization_ID AS EXPENDITURE_ORGANIZATION_ID
1444 , -1 AS JOB_ID
1445 , -1 AS VENDOR_ID
1446 , -1 AS WORK_TYPE_ID
1447 , evt.event_type_id AS EXP_EVT_TYPE_ID
1448 , PJI_FM_SUM_MAIN.g_null AS EXPENDITURE_TYPE
1449 , evt.event_type AS EVENT_TYPE
1450 , evt.event_type_classification AS EVENT_TYPE_CLASSIFICATION
1451 , PJI_FM_SUM_MAIN.g_null AS EXPENDITURE_CATEGORY
1452 , evt.revenue_category_code AS REVENUE_CATEGORY
1453 , 'PJI$NULL' AS NON_LABOR_RESOURCE
1454 , -1 AS BOM_LABOR_RESOURCE_ID
1455 , -1 AS BOM_EQUIPMENT_RESOURCE_ID
1456 , -1 AS INVENTORY_ITEM_ID
1457 , PJI_FM_SUM_MAIN.g_null AS SYSTEM_LINKAGE_FUNCTION
1458 , 'Y' AS PJI_PROJECT_RECORD_FLAG
1459 , 'N' AS PJI_RESOURCE_RECORD_FLAG
1460 , -1 AS CODE_COMBINATION_ID
1461 , Greatest(drev.GL_Date,l_min_date) AS PRVDR_GL_DATE
1462 , Greatest(drev.GL_Date,l_min_date) AS RECVR_GL_DATE
1463 , drev.GL_Period_Name AS GL_PERIOD_NAME
1464 , Greatest(drev.PA_Date,l_min_date) AS PRVDR_PA_DATE
1465 , Greatest(drev.PA_Date,l_min_date) AS RECVR_PA_DATE
1466 , drev.PA_Period_Name AS PA_PERIOD_NAME
1467 , erdl.Funding_Currency_Code AS TXN_CURRENCY_CODE
1468 , sum(erdl.Funding_Revenue_Amount) AS TXN_REVENUE
1469 , to_number(null) AS TXN_RAW_COST
1470 , to_number(null) AS TXN_BILL_RAW_COST
1471 , to_number(null) AS TXN_BURDENED_COST
1472 , to_number(null) AS TXN_BILL_BURDENED_COST
1473 , to_number(null) AS TXN_UBR
1474 , to_number(null) AS TXN_UER
1475 , sum(erdl.Project_Revenue_Amount) AS PRJ_REVENUE
1476 , to_number(null) AS PRJ_RAW_COST
1477 , to_number(null) AS PRJ_BILL_RAW_COST
1478 , to_number(null) AS PRJ_BURDENED_COST
1479 , to_number(null) AS PRJ_BILL_BURDENED_COST
1480 , to_number(null) AS PRJ_UBR
1481 , to_number(null) AS PRJ_UER
1482 , sum(erdl.Projfunc_Revenue_Amount) AS POU_REVENUE
1483 , to_number(null) AS POU_RAW_COST
1484 , to_number(null) AS POU_BILL_RAW_COST
1485 , to_number(null) AS POU_BURDENED_COST
1486 , to_number(null) AS POU_BILL_BURDENED_COST
1487 , to_number(null) AS POU_UBR
1488 , to_number(null) AS POU_UER
1489 , to_number(null) AS EOU_RAW_COST
1490 , to_number(null) AS EOU_BILL_RAW_COST
1491 , to_number(null) AS EOU_BURDENED_COST
1492 , to_number(null) AS EOU_BILL_BURDENED_COST
1493 , to_number(null) AS EOU_UBR
1494 , to_number(null) AS EOU_UER
1495 , to_number(null) AS QUANTITY
1496 , to_number(null) AS BILL_QUANTITY
1497 From pa_agreements_all ag
1498 , pa_project_customers cust
1499 , pa_events ev
1500 , pa_event_types evt
1501 , PJI_FM_EXTR_DREVN drev
1502 , pa_cust_event_rdl_all erdl
1503 Where 1 = 1
1504 And drev.worker_id = p_worker_id
1505 And drev.project_id = erdl.project_id
1506 And ev.project_id = erdl.project_id
1507 And drev.draft_revenue_num = erdl.draft_revenue_num
1508 And NVL(erdl.task_id,-1) = NVL(ev.task_id,-1)
1509 And ev.event_num = erdl.event_num
1510 And ev.event_type = evt.event_type
1511 And drev.agreement_id = ag.agreement_id
1512 And drev.project_id = cust.project_id
1513 And ag.customer_id = cust.customer_id
1514 -- And NVL(cust.bill_another_project_flag,'N') <> 'Y' ---- ER 6519955
1515 And drev.gl_date is not null
1516 And drev.pa_date is not null
1517 Group By
1518 erdl.Project_ID
1519 , nvl(ev.task_id, -1) -- Bug 6065483
1520 , drev.Project_Org_ID
1521 , drev.Project_Organization_ID
1522 , drev.Project_Type_Class
1523 , cust.Customer_ID
1524 , ev.Organization_ID
1525 , evt.event_type_id
1526 , evt.event_type
1527 , evt.event_type_classification
1528 , evt.revenue_category_code
1529 , drev.GL_Date
1530 , drev.PA_Date
1531 , drev.GL_Period_Name
1532 , drev.PA_Period_Name
1533 , erdl.Funding_Currency_Code
1534 ;
1535
1536 end if; -- EXTRACTION_TYPE = 'FULL'
1537
1538 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_FULL(p_worker_id);');
1539
1540 commit;
1541
1542 end EXTRACT_BATCH_ERDL_FULL;
1543
1544
1545 -- -----------------------------------------------------
1546 -- procedure EXTRACT_BATCH_CDL_ROWIDS
1547 -- -----------------------------------------------------
1548 procedure EXTRACT_BATCH_CDL_ROWIDS(p_worker_id in number) is
1549
1550 l_process varchar2(30);
1551 l_schema varchar2(30);
1552 l_from_project_id number;
1553 l_to_project_id number;
1554
1555 begin
1556
1557 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1558
1559 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_ROWIDS(p_worker_id);')) then
1560 return;
1561 end if;
1562
1563 if (PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') = 'FULL') then
1564
1565 insert /*+ append */ into PJI_FM_REXT_CDL
1566 (
1567 WORKER_ID
1568 , CDL_ROWID
1569 , START_DATE
1570 , END_DATE
1571 , PROJECT_ORG_ID
1572 , PROJECT_ORGANIZATION_ID
1573 , PROJECT_TYPE_CLASS
1574 , PJI_SUMMARIZED_FLAG
1575 , BATCH_ID
1576 )
1577 SELECT /*+ index_ffs(cdl, PA_COST_DISTRIBUTION_LINES_N15)
1578 parallel_index(cdl, PA_COST_DISTRIBUTION_LINES_N15) */
1579 p_worker_id
1580 , cdl.ROWID
1581 , null
1582 , null
1583 , null
1584 , null
1585 , null
1586 , cdl.PJI_SUMMARIZED_FLAG
1587 , ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold)
1588 FROM
1589 PA_COST_DISTRIBUTION_LINES_ALL cdl
1590 WHERE
1591 cdl.LINE_TYPE in ('R', 'I') and
1592 cdl.PJI_SUMMARIZED_FLAG = 'N';
1593
1594 else
1595
1596 INSERT /*+ APPEND */ INTO PJI_FM_REXT_CDL
1597 (
1598 WORKER_ID
1599 , CDL_ROWID
1600 , START_DATE
1601 , END_DATE
1602 , PROJECT_ORG_ID
1603 , PROJECT_ORGANIZATION_ID
1604 , PROJECT_TYPE_CLASS
1605 , PJI_SUMMARIZED_FLAG
1606 , BATCH_ID
1607 )
1608 SELECT
1609 p_worker_id
1610 , row_id
1611 , start_date
1612 , end_date
1613 , project_org_id
1614 , project_organization_id
1615 , project_type_class
1616 , pji_summarized_flag
1617 , ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold)
1618 FROM
1619 (
1620 SELECT /*+ ORDERED
1621 USE_NL(cdl)
1622 INDEX(cdl, PA_COST_DISTRIBUTION_LINES_N15)
1623 */
1624 cdl.rowid row_id
1625 , bat.start_date
1626 , bat.end_date
1627 , bat.project_org_id
1628 , bat.project_organization_id
1629 , bat.project_type_class
1630 , cdl.pji_summarized_flag
1631 FROM
1632 pji_fm_proj_batch_map bat
1633 , pa_cost_distribution_lines_all cdl
1634 WHERE
1635 bat.worker_id = p_worker_id
1636 and cdl.project_id = bat.project_id
1637 and cdl.line_type in ('R','I')
1638 and bat.extraction_type = 'I'
1639 and cdl.pji_summarized_flag = 'N'
1640 union all
1641 SELECT /*+ ORDERED
1642 USE_NL(cdl)
1643 INDEX(cdl, PA_COST_DISTRIBUTION_LINES_N15)
1644 */
1645 cdl.rowid row_id
1646 , bat.start_date
1647 , bat.end_date
1648 , bat.project_org_id
1649 , bat.project_organization_id
1650 , bat.project_type_class
1651 , cdl.pji_summarized_flag
1652 FROM
1653 pji_fm_proj_batch_map bat
1654 , pa_cost_distribution_lines_all cdl
1655 WHERE
1656 bat.worker_id = p_worker_id
1657 and cdl.project_id = bat.project_id
1658 and cdl.line_type in ('R','I')
1659 and bat.extraction_type <> 'I'
1660 );
1661
1662 end if;
1663
1664 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_ROWIDS(p_worker_id);');
1665
1666 commit;
1667
1668 end EXTRACT_BATCH_CDL_ROWIDS;
1669
1670
1671 -- -----------------------------------------------------
1672 -- procedure EXTRACT_BATCH_CRDL_ROWIDS
1673 -- -----------------------------------------------------
1674 procedure EXTRACT_BATCH_CRDL_ROWIDS(p_worker_id in number) is
1675
1676 l_process varchar2(30);
1677 l_schema varchar2(30);
1678
1679 begin
1680
1681 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1682
1683 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CRDL_ROWIDS(p_worker_id);')) then
1684 return;
1685 end if;
1686
1687 if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') <> 'FULL') then
1688
1689 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
1690 'CURRENT_BATCH') = 1) then
1691 -- implicit commit
1692 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
1693 tabname => 'PJI_FM_EXTR_DREVN',
1694 percent => 10,
1695 degree => PJI_UTILS.
1696 GET_DEGREE_OF_PARALLELISM);
1697 end if;
1698
1699 INSERT /*+ APPEND */ INTO PJI_FM_REXT_CRDL
1700 (
1701 WORKER_ID
1702 , CRDL_ROWID
1703 , PA_DATE
1704 , PA_PERIOD_NAME
1705 , GL_DATE
1706 , GL_PERIOD_NAME
1707 , PROJECT_ORG_ID
1708 , PROJECT_ORGANIZATION_ID
1709 , PROJECT_TYPE_CLASS
1710 , LINE_SOURCE_TYPE
1711 , BILL_ANOTHER_PROJECT_FLAG
1712 , CUSTOMER_ID
1713 )
1714 SELECT /*+ ORDERED
1715 USE_NL(ag)
1716 USE_NL(cust)
1717 USE_NL(crdl)
1718 INDEX(crdl, PA_CUST_REV_DIST_LINES_N1)
1719 */
1720 p_worker_id
1721 , crdl.rowid
1722 , drev.pa_date
1723 , drev.pa_period_name
1724 , drev.gl_date
1725 , drev.gl_period_name
1726 , drev.project_org_id
1727 , drev.project_organization_id
1728 , drev.project_type_class
1729 , drev.line_source_type
1730 , cust.bill_another_project_flag
1731 , cust.customer_id
1732 FROM
1733 PJI_FM_EXTR_DREVN drev
1734 , pa_agreements_all ag
1735 , pa_project_customers cust
1736 , pa_cust_rev_dist_lines_all crdl
1737 WHERE
1738 drev.worker_id = p_worker_id
1739 and drev.project_id = crdl.project_id
1740 and drev.draft_revenue_num = crdl.draft_revenue_num
1741 and drev.gl_date is not null
1742 and drev.pa_date is not null
1743 and drev.agreement_id = ag.agreement_id
1744 and drev.project_id = cust.project_id
1745 and ag.customer_id = cust.customer_id;
1746 -- and NVL(cust.bill_another_project_flag,'N') <> 'Y'; -- ER 6519955
1747
1748 end if; -- EXTRACTION_TYPE <> 'FULL'
1749
1750 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CRDL_ROWIDS(p_worker_id);');
1751
1752 commit;
1753
1754 end EXTRACT_BATCH_CRDL_ROWIDS;
1755
1756
1757 -- -----------------------------------------------------
1758 -- procedure EXTRACT_BATCH_ERDL_ROWIDS
1759 -- -----------------------------------------------------
1760 procedure EXTRACT_BATCH_ERDL_ROWIDS(p_worker_id in number) is
1761
1762 l_process varchar2(30);
1763 l_schema varchar2(30);
1764
1765 begin
1766
1767 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1768
1769 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_ROWIDS(p_worker_id);')) then
1770 return;
1771 end if;
1772
1773 if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') <> 'FULL') then
1774
1775 INSERT /*+ APPEND */ INTO PJI_FM_REXT_ERDL
1776 (
1777 WORKER_ID
1778 , ERDL_ROWID
1779 , PROJECT_ORG_ID
1780 , PROJECT_ORGANIZATION_ID
1781 , PROJECT_ID
1782 , PROJECT_TYPE_CLASS
1783 , EXPENDITURE_ORGANIZATION_ID
1784 , TASK_ID
1785 , EXP_EVT_TYPE_ID
1786 , EVENT_TYPE
1787 , EVENT_NUM
1788 , REVENUE_CATEGORY
1789 , EVENT_TYPE_CLASSIFICATION
1790 , LINE_SOURCE_TYPE
1791 , BILL_ANOTHER_PROJECT_FLAG
1792 , CUSTOMER_ID
1793 , TXN_DATE
1794 , PA_DATE
1795 , PA_PERIOD_NAME
1796 , GL_DATE
1797 , GL_PERIOD_NAME
1798 )
1799 SELECT /*+ ORDERED
1800 USE_NL(ag)
1801 USE_NL(cust)
1802 USE_NL(erdl)
1803 INDEX(erdl, PA_CUST_EVENT_REV_DIST_LINE_N1)
1804 */
1805 p_worker_id worker_id
1806 , erdl.rowid row_id
1807 , nvl(drev.project_org_id, -1) project_org_id
1808 , drev.project_organization_id project_organization_id
1809 , drev.project_id project_id
1810 , drev.project_type_class project_type_class
1811 , ev.organization_id expenditure_organization_id
1812 , NVL(ev.task_id,-1) task_id
1813 , evt.event_type_id exp_evt_type_id
1814 , evt.event_type event_type
1815 , ev.event_num event_num
1816 , evt.revenue_category_code revenue_category
1817 , evt.event_type_classification event_type_classification
1818 , drev.line_source_type line_source_type
1819 , cust.bill_another_project_flag bill_another_project_flag
1820 , ag.customer_id customer_id
1821 , ev.completion_date txn_date
1822 , drev.pa_date pa_date
1823 , drev.pa_period_name pa_period_name
1824 , drev.gl_date gl_date
1825 , drev.gl_period_name gl_period_name
1826 FROM
1827 PJI_FM_EXTR_DREVN drev
1828 , pa_agreements_all ag
1829 , pa_project_customers cust
1830 , pa_events ev
1831 , pa_event_types evt
1832 , pa_cust_event_rdl_all erdl
1833 WHERE
1834 drev.worker_id = p_worker_id
1835 and drev.project_id = ev.project_id
1836 and ev.project_id = erdl.project_id
1837 and drev.project_id = erdl.project_id
1838 and drev.draft_revenue_num = erdl.draft_revenue_num
1839 and NVL(erdl.task_id,-1) = NVL(ev.task_id,-1) -- uncommented for bug 7354140
1840 and ev.event_num = erdl.event_num -- uncommented for bug 7354140
1841 and ev.event_type = evt.event_type
1842 and drev.agreement_id = ag.agreement_id
1843 and drev.project_id = cust.project_id
1844 and ag.customer_id = cust.customer_id
1845 -- and NVL(cust.bill_another_project_flag,'N') <> 'Y' -- ER 6519955
1846 and drev.gl_date is not null
1847 and drev.pa_date is not null
1848 ;
1849
1850 end if; -- EXTRACTION_TYPE <> 'FULL'
1851
1852 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_ROWIDS(p_worker_id);');
1853
1854 commit;
1855
1856 end EXTRACT_BATCH_ERDL_ROWIDS;
1857
1858
1859 -- -----------------------------------------------------
1860 -- procedure EXTRACT_BATCH_CDL_AND_CRDL
1861 -- -----------------------------------------------------
1862 procedure EXTRACT_BATCH_CDL_AND_CRDL (p_worker_id in number) is
1863
1864 l_process varchar2(30);
1865 l_min_date date;
1866 l_schema varchar2(30);
1867 l_row_count number;
1868
1869 begin
1870
1871 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1872
1873 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_AND_CRDL(p_worker_id);')) then
1874 return;
1875 end if;
1876
1877 l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
1878 PJI_FM_SUM_MAIN.g_date_mask);
1879
1880 if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') <> 'FULL') then
1881
1882 -- This cleanup is intentionally before the implicit commit so as not
1883 -- to interfere with the CDL extraction.
1884 if (nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1885 (PJI_FM_SUM_MAIN.g_process, 'CONFIG_PROJ_PERF_FLAG'),
1886 'N') = 'N' and
1887 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1888 (PJI_FM_SUM_MAIN.g_process, 'CONFIG_COST_FLAG'),
1889 'N') = 'N' and
1890 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1891 (PJI_FM_SUM_MAIN.g_process, 'CONFIG_PROFIT_FLAG'),
1892 'N') = 'N') then
1893 delete /*+ index (log, PA_PJI_PROJ_EVENTS_LOG_N1) */
1894 from PA_PJI_PROJ_EVENTS_LOG log
1895 where EVENT_TYPE = 'DRAFT_REVENUES';
1896 end if;
1897
1898 -- delete Non-Util --> Util resources that are getting extracted anyway
1899 delete
1900 from PJI_FM_REXT_CDL
1901 where WORKER_ID = p_worker_id and
1902 PROJECT_ORG_ID = -1 and
1903 PROJECT_ORGANIZATION_ID = -1 and
1904 CDL_ROWID in (select CDL_ROWID
1905 from PJI_FM_REXT_CDL
1906 where WORKER_ID = p_worker_id and
1907 PROJECT_ORGANIZATION_ID <> -1);
1908
1909 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
1910 'CURRENT_BATCH') = 1) then
1911 -- implicit commit
1912 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
1913 tabname => 'PJI_FM_REXT_CDL',
1914 percent => 10,
1915 degree => PJI_UTILS.
1916 GET_DEGREE_OF_PARALLELISM);
1917 -- implicit commit
1918 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
1919 tabname => 'PJI_FM_REXT_CRDL',
1920 percent => 10,
1921 degree => PJI_UTILS.
1922 GET_DEGREE_OF_PARALLELISM);
1923 end if;
1924
1925 INSERT /*+ APPEND PARALLEL(fin1_i) */ INTO PJI_FM_AGGR_FIN1 fin1_i
1926 ( WORKER_ID
1927 , SLICE_ID
1928 , PROJECT_ID
1929 , TASK_ID
1930 , PERSON_ID
1931 , PROJECT_ORG_ID
1932 , PROJECT_ORGANIZATION_ID
1933 , PROJECT_TYPE_CLASS
1934 , CUSTOMER_ID
1935 , EXPENDITURE_ORG_ID
1936 , EXPENDITURE_ORGANIZATION_ID
1937 , JOB_ID
1938 , VENDOR_ID
1939 , WORK_TYPE_ID
1940 , EXP_EVT_TYPE_ID
1941 , EXPENDITURE_TYPE
1942 , EVENT_TYPE
1943 , EVENT_TYPE_CLASSIFICATION
1944 , EXPENDITURE_CATEGORY
1945 , REVENUE_CATEGORY
1946 , NON_LABOR_RESOURCE
1947 , BOM_LABOR_RESOURCE_ID
1948 , BOM_EQUIPMENT_RESOURCE_ID
1949 , INVENTORY_ITEM_ID
1950 , PO_LINE_ID
1951 , ASSIGNMENT_ID
1952 , SYSTEM_LINKAGE_FUNCTION
1953 , PJI_PROJECT_RECORD_FLAG
1954 , PJI_RESOURCE_RECORD_FLAG
1955 , CODE_COMBINATION_ID
1956 , PRVDR_GL_DATE
1957 , RECVR_GL_DATE
1958 , GL_PERIOD_NAME
1959 , PRVDR_PA_DATE
1960 , RECVR_PA_DATE
1961 , PA_PERIOD_NAME
1962 , EXPENDITURE_ITEM_DATE
1963 , TXN_CURRENCY_CODE
1964 , TXN_REVENUE
1965 , TXN_RAW_COST
1966 , TXN_BILL_RAW_COST
1967 , TXN_BURDENED_COST
1968 , TXN_BILL_BURDENED_COST
1969 , TXN_UBR
1970 , TXN_UER
1971 , PRJ_REVENUE
1972 , PRJ_RAW_COST
1973 , PRJ_BILL_RAW_COST
1974 , PRJ_BURDENED_COST
1975 , PRJ_BILL_BURDENED_COST
1976 , PRJ_UBR
1977 , PRJ_UER
1978 , POU_REVENUE
1979 , POU_RAW_COST
1980 , POU_BILL_RAW_COST
1981 , POU_BURDENED_COST
1982 , POU_BILL_BURDENED_COST
1983 , POU_UBR
1984 , POU_UER
1985 , EOU_RAW_COST
1986 , EOU_BILL_RAW_COST
1987 , EOU_BURDENED_COST
1988 , EOU_BILL_BURDENED_COST
1989 , EOU_UBR
1990 , EOU_UER
1991 , QUANTITY
1992 , BILL_QUANTITY
1993 )
1994 SELECT
1995 grp.WORKER_ID
1996 , grp.SLICE_ID
1997 , grp.PROJECT_ID
1998 , grp.TASK_ID
1999 , grp.PERSON_ID
2000 , grp.PROJECT_ORG_ID
2001 , grp.PROJECT_ORGANIZATION_ID
2002 , grp.PROJECT_TYPE_CLASS
2003 , grp.CUSTOMER_ID
2004 , grp.EXPENDITURE_ORG_ID
2005 , grp.EXPENDITURE_ORGANIZATION_ID
2006 , grp.JOB_ID
2007 , grp.VENDOR_ID
2008 , grp.WORK_TYPE_ID
2009 , grp.EXP_EVT_TYPE_ID
2010 , grp.EXPENDITURE_TYPE
2011 , grp.EVENT_TYPE
2012 , grp.EVENT_TYPE_CLASSIFICATION
2013 , grp.EXPENDITURE_CATEGORY
2014 , grp.REVENUE_CATEGORY
2015 , grp.NON_LABOR_RESOURCE
2016 , grp.BOM_LABOR_RESOURCE_ID
2017 , grp.BOM_EQUIPMENT_RESOURCE_ID
2018 , grp.INVENTORY_ITEM_ID
2019 , grp.PO_LINE_ID
2020 , grp.ASSIGNMENT_ID
2021 , grp.SYSTEM_LINKAGE_FUNCTION
2022 , grp.PJI_PROJECT_RECORD_FLAG
2023 , grp.PJI_RESOURCE_RECORD_FLAG
2024 , grp.CODE_COMBINATION_ID
2025 , grp.PRVDR_GL_DATE
2026 , grp.RECVR_GL_DATE
2027 , grp.GL_PERIOD_NAME
2028 , grp.PRVDR_PA_DATE
2029 , grp.RECVR_PA_DATE
2030 , grp.PA_PERIOD_NAME
2031 , grp.EXPENDITURE_ITEM_DATE
2032 , grp.TXN_CURRENCY_CODE
2033 , sum(grp.TXN_REVENUE)
2034 , sum(grp.TXN_RAW_COST)
2035 , sum(grp.TXN_BILL_RAW_COST)
2036 , sum(grp.TXN_BURDENED_COST)
2037 , sum(grp.TXN_BILL_BURDENED_COST)
2038 , sum(grp.TXN_UBR)
2039 , sum(grp.TXN_UER)
2040 , sum(grp.PRJ_REVENUE)
2041 , sum(grp.PRJ_RAW_COST)
2042 , sum(grp.PRJ_BILL_RAW_COST)
2043 , sum(grp.PRJ_BURDENED_COST)
2044 , sum(grp.PRJ_BILL_BURDENED_COST)
2045 , sum(grp.PRJ_UBR)
2046 , sum(grp.PRJ_UER)
2047 , sum(grp.POU_REVENUE)
2048 , sum(grp.POU_RAW_COST)
2049 , sum(grp.POU_BILL_RAW_COST)
2050 , sum(grp.POU_BURDENED_COST)
2051 , sum(grp.POU_BILL_BURDENED_COST)
2052 , sum(grp.POU_UBR)
2053 , sum(grp.POU_UER)
2054 , sum(grp.EOU_RAW_COST)
2055 , sum(grp.EOU_BILL_RAW_COST)
2056 , sum(grp.EOU_BURDENED_COST)
2057 , sum(grp.EOU_BILL_BURDENED_COST)
2058 , sum(grp.EOU_UBR)
2059 , sum(grp.EOU_UER)
2060 , sum(grp.QUANTITY)
2061 , sum(grp.BILL_QUANTITY)
2062 FROM (
2063 SELECT /*+ ordered */
2064 p_worker_id AS WORKER_ID
2065 , decode(scope.PROJECT_ORG_ID, -1, -- Ensure that JOB_ID
2066 decode(scope.PROJECT_ORGANIZATION_ID, -- Util --> Non-Util
2067 -1, -1, 1), -- reversals do not get
2068 1) SLICE_ID -- into PSI tables.
2069 , cdl.project_id AS PROJECT_ID
2070 , cdl.task_id AS TASK_ID
2071 , decode(exp.incurred_by_person_id,
2072 null, -1, 0, -1,
2073 exp.incurred_by_person_id) AS PERSON_ID
2074 , nvl(scope.project_org_id, -1) AS PROJECT_ORG_ID
2075 , scope.project_organization_id AS PROJECT_ORGANIZATION_ID
2076 , scope.project_type_class AS PROJECT_TYPE_CLASS
2077 , -1 AS CUSTOMER_ID
2078 , cdl.org_id AS EXPENDITURE_ORG_ID
2079 , NVL(ei.override_to_organization_id, exp.incurred_by_organization_id)
2080 AS EXPENDITURE_ORGANIZATION_ID
2081 , nvl(ei.job_id, -1) AS JOB_ID
2082 , nvl(exp.vendor_id, -1) AS VENDOR_ID
2083 , nvl(cdl.work_type_id, -1) AS WORK_TYPE_ID
2084 , et.expenditure_type_id AS EXP_EVT_TYPE_ID
2085 , et.expenditure_type AS EXPENDITURE_TYPE
2086 , PJI_FM_SUM_MAIN.g_null AS EVENT_TYPE
2087 , PJI_FM_SUM_MAIN.g_null AS EVENT_TYPE_CLASSIFICATION
2088 , et.expenditure_category AS EXPENDITURE_CATEGORY
2089 , et.revenue_category_code AS REVENUE_CATEGORY
2090 , ei.Non_Labor_Resource AS NON_LABOR_RESOURCE
2091 , ei.Wip_Resource_ID AS BOM_LABOR_RESOURCE_ID
2092 , ei.Wip_Resource_ID AS BOM_EQUIPMENT_RESOURCE_ID
2093 , ei.Inventory_Item_ID AS INVENTORY_ITEM_ID
2094 , ei.PO_Line_ID AS PO_LINE_ID
2095 , decode(ei.Assignment_ID,
2096 null, -1, 0, -1,
2097 ei.Assignment_ID) AS ASSIGNMENT_ID
2098 , NVL(ei.src_system_linkage_function,
2099 ei.system_linkage_function)
2100 AS SYSTEM_LINKAGE_FUNCTION
2101 , decode(scope.PROJECT_ORG_ID,
2102 -1, decode(scope.PROJECT_ORGANIZATION_ID,
2103 -1, 'N',
2104 'Y'),
2105 'Y') AS PJI_PROJECT_RECORD_FLAG
2106 , decode(scope.PROJECT_ORG_ID,
2107 -1, decode(scope.PROJECT_ORGANIZATION_ID,
2108 -1, 'Y',
2109 decode(exp.Incurred_BY_Person_ID,
2110 null, 'N',
2111 0, 'N',
2112 'Y')),
2113 decode(exp.Incurred_BY_Person_ID,
2114 null, 'N',
2115 0, 'N',
2116 'Y')) AS PJI_RESOURCE_RECORD_FLAG
2117 , -1 AS CODE_COMBINATION_ID
2118 , Greatest(cdl.gl_date,l_min_date) AS PRVDR_GL_DATE
2119 , Greatest(nvl(cdl.recvr_gl_date, cdl.gl_date),l_min_date) AS RECVR_GL_DATE
2120 , cdl.Recvr_GL_Period_Name AS GL_PERIOD_NAME
2121 , Greatest(cdl.pa_date,l_min_date) AS PRVDR_PA_DATE
2122 , Greatest(nvl(cdl.recvr_pa_date, cdl.pa_date),l_min_date) AS RECVR_PA_DATE
2123 , cdl.Recvr_PA_Period_Name AS PA_PERIOD_NAME
2124 , Greatest(ei.Expenditure_Item_Date,
2125 l_min_date) AS EXPENDITURE_ITEM_DATE
2126 , cdl.Denom_Currency_Code AS TXN_CURRENCY_CODE
2127 , to_number(null) AS TXN_REVENUE
2128 , NVL(cdl.Denom_Raw_Cost,0) AS TXN_RAW_COST
2129 , decode(cdl.billable_flag
2130 , 'Y', nvl(cdl.Denom_Raw_Cost, 0)
2131 , 0) AS TXN_BILL_RAW_COST
2132 , nvl(cdl.Denom_Burdened_Cost, 0) AS TXN_BURDENED_COST
2133 , decode(cdl.Billable_Flag
2134 , 'Y', nvl(cdl.Denom_Burdened_Cost, 0)
2135 , 0) AS TXN_BILL_BURDENED_COST
2136 , to_number(null) AS TXN_UBR
2137 , to_number(null) AS TXN_UER
2138 , to_number(null) AS PRJ_REVENUE
2139 , NVL(cdl.project_raw_cost,0) AS PRJ_RAW_COST
2140 , decode(cdl.billable_flag
2141 , 'Y', nvl(cdl.Project_Raw_Cost, 0)
2142 , 0) AS PRJ_BILL_RAW_COST
2143 , nvl(cdl.Project_Burdened_Cost, 0) AS PRJ_BURDENED_COST
2144 , decode(cdl.Billable_Flag
2145 , 'Y', nvl(cdl.Project_Burdened_Cost, 0)
2146 , 0) AS PRJ_BILL_BURDENED_COST
2147 , to_number(null) AS PRJ_UBR
2148 , to_number(null) AS PRJ_UER
2149 , to_number(null) AS POU_REVENUE
2150 , cdl.AMOUNT AS POU_RAW_COST
2151 , decode(cdl.bILLABLE_fLAG
2152 , 'Y', nvl(cdl.Amount, 0)
2153 , 0) AS POU_BILL_RAW_COST
2154 , nvl(cdl.Burdened_Cost, 0) AS POU_BURDENED_COST
2155 , decode(cdl.Billable_Flag
2156 , 'Y', nvl(cdl.Burdened_Cost, 0)
2157 , 0) AS POU_BILL_BURDENED_COST
2158 , to_number(null) AS POU_UBR
2159 , to_number(null) AS POU_UER
2160 , nvl(cdl.Acct_Raw_Cost, 0) AS EOU_RAW_COST
2161 , decode(cdl.Billable_Flag
2162 , 'Y', nvl(cdl.Acct_Raw_Cost,0)
2163 , 0) AS EOU_BILL_RAW_COST
2164 , nvl(cdl.Acct_Burdened_Cost, 0) AS EOU_BURDENED_COST
2165 , decode(cdl.Billable_Flag
2166 , 'Y', nvl(cdl.Acct_Burdened_Cost, 0)
2167 , 0) AS EOU_BILL_BURDENED_COST
2168 , to_number(null) AS EOU_UBR
2169 , to_number(null) AS EOU_UER
2170 , cdl.Quantity AS QUANTITY
2171 , decode(cdl.Billable_Flag
2172 , 'Y', cdl.Quantity
2173 , 0) AS BILL_QUANTITY
2174 FROM
2175 PJI_FM_REXT_CDL scope
2176 , pa_cost_distribution_lines_all cdl
2177 , pa_expenditure_items_all ei
2178 , pa_expenditures_all exp
2179 , pa_expenditure_types et
2180 WHERE
2181 scope.worker_id = p_worker_id
2182 and scope.cdl_rowid = cdl.rowid
2183 and cdl.expenditure_item_id = ei.expenditure_item_id
2184 and ei.expenditure_type = et.expenditure_type
2185 and exp.expenditure_id = ei.expenditure_id
2186 and cdl.gl_date is not null
2187 and cdl.pa_date is not null
2188 -- and NVL(ei.transaction_source,'dummy') <> 'INTERPROJECT_AP_INVOICES' -- ER 6519955
2189 UNION ALL
2190 SELECT /*+ ordered */
2191 p_worker_id AS WORKER_ID
2192 , 1 AS SLICE_ID
2193 , crdl.Project_ID AS PROJECT_ID
2194 , ei.Task_ID AS TASK_ID
2195 , decode(exp.Incurred_By_Person_ID,
2196 null, -1, 0, -1,
2197 exp.Incurred_By_Person_ID) AS PERSON_ID
2198 , nvl(scope.Project_Org_ID, -1) AS PROJECT_ORG_ID
2199 , scope.Project_Organization_ID AS PROJECT_ORGANIZATION_ID
2200 , scope.Project_Type_Class AS PROJECT_TYPE_CLASS
2201 , scope.Customer_ID AS CUSTOMER_ID
2202 , ei.Org_ID AS EXPENDITURE_ORG_ID
2203 , nvl(ei.Override_To_Organization_ID, exp.Incurred_By_Organization_ID)
2204 AS EXPENDITURE_ORGANIZATION_ID
2205 , nvl(ei.Job_ID, -1) AS JOB_ID
2206 , nvl(exp.vendor_id,-1) AS VENDOR_ID
2207 , nvl(ei.Work_type_ID, -1) AS WORK_TYPE_ID
2208 , et.Expenditure_Type_ID AS EXP_EVT_TYPE_ID
2209 , et.Expenditure_Type AS EXPENDITURE_TYPE
2210 , PJI_FM_SUM_MAIN.g_null AS EVENT_TYPE
2211 , PJI_FM_SUM_MAIN.g_null AS EVENT_TYPE_CLASSIFICATION
2212 , et.Expenditure_Category AS EXPENDITURE_CATEGORY
2213 , et.Revenue_Category_Code AS REVENUE_CATEGORY
2214 , ei.Non_Labor_Resource AS NON_LABOR_RESOURCE
2215 , ei.Wip_Resource_ID AS BOM_LABOR_RESOURCE_ID
2216 , ei.Wip_Resource_ID AS BOM_EQUIPMENT_RESOURCE_ID
2217 , ei.Inventory_Item_ID AS INVENTORY_ITEM_ID
2218 , ei.PO_Line_ID AS PO_LINE_ID
2219 , decode(ei.Assignment_ID,
2220 null, -1, 0, -1,
2221 ei.Assignment_ID) AS ASSIGNMENT_ID
2222 , NVL(ei.src_system_linkage_function,
2223 ei.system_linkage_function)
2224 AS SYSTEM_LINKAGE_FUNCTION
2225 , 'Y' AS PJI_PROJECT_RECORD_FLAG
2226 , decode(exp.Incurred_By_Person_ID, null, 'N', 0, 'N', 'Y')
2227 AS PJI_RESOURCE_RECORD_FLAG
2228 , -1 AS CODE_COMBINATION_ID
2229 , Greatest(scope.GL_Date,l_min_date) AS PRVDR_GL_DATE
2230 , Greatest(scope.GL_Date,l_min_date) AS RECVR_GL_DATE
2231 , scope.GL_Period_Name AS GL_PERIOD_NAME
2232 , Greatest(scope.PA_Date,l_min_date) AS PRVDR_PA_DATE
2233 , Greatest(scope.PA_Date,l_min_date) AS RECVR_PA_DATE
2234 , scope.PA_Period_Name AS PA_PERIOD_NAME
2235 , Greatest(ei.Expenditure_Item_Date,
2236 l_min_date) AS EXPENDITURE_ITEM_DATE
2237 , crdl.Funding_Currency_Code AS TXN_CURRENCY_CODE
2238 , decode(scope.line_source_type,
2239 'R', (crdl.Funding_Revenue_Amount),
2240 'L', (-crdl.Funding_Revenue_Amount)
2241 ) AS TXN_REVENUE
2242 , to_number(null) AS TXN_RAW_COST
2243 , to_number(null) AS TXN_BILL_RAW_COST
2244 , to_number(null) AS TXN_BURDENED_COST
2245 , to_number(null) AS TXN_BILL_BURDENED_COST
2246 , to_number(null) AS TXN_UBR
2247 , to_number(null) AS TXN_UER
2248 , decode(scope.line_source_type,
2249 'R', (crdl.Project_Revenue_Amount),
2250 'L', (-crdl.Project_Revenue_Amount)
2251 ) AS PRJ_REVENUE
2252 , to_number(null) AS PRJ_RAW_COST
2253 , to_number(null) AS PRJ_BILL_RAW_COST
2254 , to_number(null) AS PRJ_BURDENED_COST
2255 , to_number(null) AS PRJ_BILL_BURDENED_COST
2256 , to_number(null) AS PRJ_UBR
2257 , to_number(null) AS PRJ_UER
2258 , decode(scope.line_source_type,
2259 'R', (crdl.Projfunc_Revenue_Amount),
2260 'L', (-crdl.Projfunc_Revenue_Amount)
2261 ) AS POU_REVENUE
2262 , to_number(null) AS POU_RAW_COST
2263 , to_number(null) AS POU_BILL_RAW_COST
2264 , to_number(null) AS POU_BURDENED_COST
2265 , to_number(null) AS POU_BILL_BURDENED_COST
2266 , to_number(null) AS POU_UBR
2267 , to_number(null) AS POU_UER
2268 , to_number(null) AS EOU_RAW_COST
2269 , to_number(null) AS EOU_BILL_RAW_COST
2270 , to_number(null) AS EOU_BURDENED_COST
2271 , to_number(null) AS EOU_BILL_BURDENED_COST
2272 , to_number(null) AS EOU_UBR
2273 , to_number(null) AS EOU_UER
2274 , to_number(null) AS QUANTITY
2275 , to_number(null) AS BILL_QUANTITY
2276 FROM
2277 PJI_FM_REXT_CRDL scope
2278 , pa_cust_rev_dist_lines_all crdl
2279 , pa_expenditure_items_all ei
2280 , pa_expenditures_all exp
2281 , pa_expenditure_types et
2282 WHERE
2283 scope.worker_id = p_worker_id
2284 and scope.crdl_rowid = crdl.rowid
2285 and crdl.function_code NOT IN ('LRL','LRB','URL','URB')
2286 and crdl.expenditure_item_id = ei.expenditure_item_id
2287 and ei.expenditure_type = et.expenditure_type
2288 and exp.expenditure_id = ei.expenditure_id
2289 ) grp
2290 GROUP BY
2291 grp.WORKER_ID
2292 , grp.SLICE_ID
2293 , grp.PROJECT_ID
2294 , grp.TASK_ID
2295 , grp.PERSON_ID
2296 , grp.PROJECT_ORG_ID
2297 , grp.PROJECT_ORGANIZATION_ID
2298 , grp.PROJECT_TYPE_CLASS
2299 , grp.CUSTOMER_ID
2300 , grp.EXPENDITURE_ORG_ID
2301 , grp.EXPENDITURE_ORGANIZATION_ID
2302 , grp.JOB_ID
2303 , grp.VENDOR_ID
2304 , grp.WORK_TYPE_ID
2305 , grp.EXP_EVT_TYPE_ID
2306 , grp.EXPENDITURE_TYPE
2307 , grp.EVENT_TYPE
2308 , grp.EVENT_TYPE_CLASSIFICATION
2309 , grp.EXPENDITURE_CATEGORY
2310 , grp.REVENUE_CATEGORY
2311 , grp.NON_LABOR_RESOURCE
2312 , grp.BOM_LABOR_RESOURCE_ID
2313 , grp.BOM_EQUIPMENT_RESOURCE_ID
2314 , grp.INVENTORY_ITEM_ID
2315 , grp.PO_LINE_ID
2316 , grp.ASSIGNMENT_ID
2317 , grp.SYSTEM_LINKAGE_FUNCTION
2318 , grp.PJI_PROJECT_RECORD_FLAG
2319 , grp.PJI_RESOURCE_RECORD_FLAG
2320 , grp.CODE_COMBINATION_ID
2321 , grp.PRVDR_GL_DATE
2322 , grp.RECVR_GL_DATE
2323 , grp.GL_PERIOD_NAME
2324 , grp.PRVDR_PA_DATE
2325 , grp.RECVR_PA_DATE
2326 , grp.PA_PERIOD_NAME
2327 , grp.EXPENDITURE_ITEM_DATE
2328 , grp.TXN_CURRENCY_CODE
2329 ;
2330
2331 end if; -- EXTRACTION_TYPE <> 'FULL'
2332
2333 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_AND_CRDL(p_worker_id);');
2334
2335 -- truncate intermediate tables no longer required
2336 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
2337 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CRDL' , 'NORMAL',null);
2338
2339 commit;
2340
2341 end EXTRACT_BATCH_CDL_AND_CRDL;
2342
2343
2344 -- -----------------------------------------------------
2345 -- procedure MARK_EXTRACTED_CDL_ROWS_PRE
2346 -- -----------------------------------------------------
2347 procedure MARK_EXTRACTED_CDL_ROWS_PRE (p_worker_id in number) is
2348
2349 l_process varchar2(30);
2350
2351 begin
2352
2353 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2354
2355 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
2356 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_PRE(p_worker_id);')) then
2357 return;
2358 end if;
2359
2360 insert /*+ append */ into PJI_HELPER_BATCH_MAP
2361 (
2362 BATCH_ID,
2363 WORKER_ID,
2364 STATUS
2365 )
2366 select
2367 distinct
2368 BATCH_ID,
2369 null,
2370 null
2371 from
2372 PJI_FM_REXT_CDL
2373 where
2374 PJI_SUMMARIZED_FLAG is not null;
2375
2376 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2377 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_PRE(p_worker_id);');
2378
2379 commit;
2380
2381 end MARK_EXTRACTED_CDL_ROWS_PRE;
2382
2383
2384 -- -----------------------------------------------------
2385 -- procedure MARK_EXTRACTED_CDL_ROWS
2386 -- -----------------------------------------------------
2387 procedure MARK_EXTRACTED_CDL_ROWS (p_worker_id in number) is
2388
2389 l_process varchar2(30);
2390 l_leftover_batches number;
2391 l_helper_batch_id number;
2392 l_row_count number;
2393 l_parallel_processes number;
2394
2395 begin
2396
2397 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2398
2399 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS(p_worker_id);')) then
2400 return;
2401 end if;
2402
2403 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2404 (PJI_FM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
2405
2406 select count(*)
2407 into l_leftover_batches
2408 from PJI_HELPER_BATCH_MAP
2409 where WORKER_ID = p_worker_id and
2410 STATUS = 'P';
2411
2412 l_helper_batch_id := 0;
2413
2414 while l_helper_batch_id >= 0 loop
2415
2416 if (l_leftover_batches > 0) then
2417
2418 l_leftover_batches := l_leftover_batches - 1;
2419
2420 select BATCH_ID
2421 into l_helper_batch_id
2422 from PJI_HELPER_BATCH_MAP
2423 where WORKER_ID = p_worker_id and
2424 STATUS = 'P' and
2425 ROWNUM = 1;
2426
2427 else
2428
2429 update PJI_HELPER_BATCH_MAP
2430 set WORKER_ID = p_worker_id,
2431 STATUS = 'P'
2432 where WORKER_ID is null and
2433 ROWNUM = 1
2434 returning BATCH_ID
2435 into l_helper_batch_id;
2436
2437 end if;
2438
2439 if (sql%rowcount <> 0) then
2440
2441 commit;
2442
2443 update PA_COST_DISTRIBUTION_LINES_ALL cdl
2444 set cdl.PJI_SUMMARIZED_FLAG = null
2445 where cdl.ROWID in (select /*+ cardinality(cdl, 1) */
2446 cdl.CDL_ROWID
2447 from PJI_FM_REXT_CDL cdl
2448 where cdl.PJI_SUMMARIZED_FLAG = 'N' and
2449 cdl.BATCH_ID = l_helper_batch_id);
2450
2451 update PJI_HELPER_BATCH_MAP
2452 set STATUS = 'C'
2453 where WORKER_ID = p_worker_id and
2454 BATCH_ID = l_helper_batch_id;
2455
2456 commit;
2457
2458 else
2459
2460 select count(*)
2461 into l_row_count
2462 from PJI_HELPER_BATCH_MAP
2463 where nvl(STATUS, 'X') <> 'C';
2464
2465 if (l_row_count = 0) then
2466
2467 for x in 2 .. l_parallel_processes loop
2468
2469 update PJI_SYSTEM_PRC_STATUS
2470 set STEP_STATUS = 'C'
2471 where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
2472 STEP_NAME =
2473 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS(p_worker_id);' and
2474 START_DATE is null;
2475
2476 commit;
2477
2478 end loop;
2479
2480 l_helper_batch_id := -1;
2481
2482 else
2483
2484 PJI_PROCESS_UTIL.SLEEP(1); -- so the CPU is not bombarded
2485
2486 end if;
2487
2488 end if;
2489
2490 if (l_helper_batch_id >= 0) then
2491
2492 for x in 2 .. l_parallel_processes loop
2493 if (not PJI_FM_SUM_EXTR.WORKER_STATUS(x, 'OKAY')) then
2494 l_helper_batch_id := -2;
2495 end if;
2496 end loop;
2497
2498 end if;
2499
2500 end loop;
2501
2502 if (l_helper_batch_id <> -2) then
2503
2504 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2505 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS(p_worker_id);');
2506
2507 end if;
2508
2509 commit;
2510
2511 end MARK_EXTRACTED_CDL_ROWS;
2512
2513
2514 -- -----------------------------------------------------
2515 -- procedure MARK_EXTRACTED_CDL_ROWS_POST
2516 -- -----------------------------------------------------
2517 procedure MARK_EXTRACTED_CDL_ROWS_POST (p_worker_id in number) is
2518
2519 l_process varchar2(30);
2520
2521 begin
2522
2523 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2524
2525 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
2526 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_POST(p_worker_id);')) then
2527 return;
2528 end if;
2529
2530 PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
2531 'PJI_HELPER_BATCH_MAP',
2532 'NORMAL',
2533 null);
2534
2535 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2536 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_POST(p_worker_id);');
2537
2538 if (PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') = 'FULL') then
2539 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(PJI_UTILS.GET_PJI_SCHEMA_NAME,
2540 'PJI_FM_REXT_CDL', 'NORMAL',null);
2541 end if;
2542
2543 commit;
2544
2545 end MARK_EXTRACTED_CDL_ROWS_POST;
2546
2547
2548 -- -----------------------------------------------------
2549 -- procedure EXTRACT_BATCH_ERDL
2550 -- -----------------------------------------------------
2551 procedure EXTRACT_BATCH_ERDL (p_worker_id in number) is
2552
2553 l_process varchar2(30);
2554 l_min_date date;
2555 l_schema varchar2(30);
2556
2557 begin
2558
2559 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2560
2561 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL(p_worker_id);')) then
2562 return;
2563 end if;
2564
2565 l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
2566 PJI_FM_SUM_MAIN.g_date_mask);
2567
2568 if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') <> 'FULL') then
2569
2570 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
2571 'CURRENT_BATCH') = 1) then
2572 -- implicit commit
2573 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
2574 tabname => 'PJI_FM_REXT_ERDL',
2575 percent => 10,
2576 degree => PJI_UTILS.
2577 GET_DEGREE_OF_PARALLELISM);
2578 end if;
2579
2580 INSERT /*+ APPEND */ INTO PJI_FM_AGGR_FIN1
2581 ( WORKER_ID
2582 , SLICE_ID
2583 , PROJECT_ID
2584 , TASK_ID
2585 , PERSON_ID
2586 , PROJECT_ORG_ID
2587 , PROJECT_ORGANIZATION_ID
2588 , PROJECT_TYPE_CLASS
2589 , CUSTOMER_ID
2590 , EXPENDITURE_ORG_ID
2591 , EXPENDITURE_ORGANIZATION_ID
2592 , JOB_ID
2593 , VENDOR_ID
2594 , WORK_TYPE_ID
2595 , EXP_EVT_TYPE_ID
2596 , EXPENDITURE_TYPE
2597 , EVENT_TYPE
2598 , EVENT_TYPE_CLASSIFICATION
2599 , EXPENDITURE_CATEGORY
2600 , REVENUE_CATEGORY
2601 , NON_LABOR_RESOURCE
2602 , BOM_LABOR_RESOURCE_ID
2603 , BOM_EQUIPMENT_RESOURCE_ID
2604 , INVENTORY_ITEM_ID
2605 , SYSTEM_LINKAGE_FUNCTION
2606 , PJI_PROJECT_RECORD_FLAG
2607 , PJI_RESOURCE_RECORD_FLAG
2608 , CODE_COMBINATION_ID
2609 , PRVDR_GL_DATE
2610 , RECVR_GL_DATE
2611 , GL_PERIOD_NAME
2612 , PRVDR_PA_DATE
2613 , RECVR_PA_DATE
2614 , PA_PERIOD_NAME
2615 , TXN_CURRENCY_CODE
2616 , TXN_REVENUE
2617 , TXN_RAW_COST
2618 , TXN_BILL_RAW_COST
2619 , TXN_BURDENED_COST
2620 , TXN_BILL_BURDENED_COST
2621 , TXN_UBR
2622 , TXN_UER
2623 , PRJ_REVENUE
2624 , PRJ_RAW_COST
2625 , PRJ_BILL_RAW_COST
2626 , PRJ_BURDENED_COST
2627 , PRJ_BILL_BURDENED_COST
2628 , PRJ_UBR
2629 , PRJ_UER
2630 , POU_REVENUE
2631 , POU_RAW_COST
2632 , POU_BILL_RAW_COST
2633 , POU_BURDENED_COST
2634 , POU_BILL_BURDENED_COST
2635 , POU_UBR
2636 , POU_UER
2637 , EOU_RAW_COST
2638 , EOU_BILL_RAW_COST
2639 , EOU_BURDENED_COST
2640 , EOU_BILL_BURDENED_COST
2641 , EOU_UBR
2642 , EOU_UER
2643 , QUANTITY
2644 , BILL_QUANTITY
2645 )
2646 SELECT
2647 grp.WORKER_ID
2648 , grp.SLICE_ID
2649 , grp.PROJECT_ID
2650 , grp.TASK_ID
2651 , grp.PERSON_ID
2652 , grp.PROJECT_ORG_ID
2653 , grp.PROJECT_ORGANIZATION_ID
2654 , grp.PROJECT_TYPE_CLASS
2655 , grp.CUSTOMER_ID
2656 , grp.EXPENDITURE_ORG_ID
2657 , grp.EXPENDITURE_ORGANIZATION_ID
2658 , grp.JOB_ID
2659 , grp.VENDOR_ID
2660 , grp.WORK_TYPE_ID
2661 , grp.EXP_EVT_TYPE_ID
2662 , grp.EXPENDITURE_TYPE
2663 , grp.EVENT_TYPE
2664 , grp.EVENT_TYPE_CLASSIFICATION
2665 , grp.EXPENDITURE_CATEGORY
2666 , grp.REVENUE_CATEGORY
2667 , grp.NON_LABOR_RESOURCE
2668 , grp.BOM_LABOR_RESOURCE_ID
2669 , grp.BOM_EQUIPMENT_RESOURCE_ID
2670 , grp.INVENTORY_ITEM_ID
2671 , grp.SYSTEM_LINKAGE_FUNCTION
2672 , grp.PJI_PROJECT_RECORD_FLAG
2673 , grp.PJI_RESOURCE_RECORD_FLAG
2674 , grp.CODE_COMBINATION_ID
2675 , grp.PRVDR_GL_DATE
2676 , grp.RECVR_GL_DATE
2677 , grp.GL_PERIOD_NAME
2678 , grp.PRVDR_PA_DATE
2679 , grp.RECVR_PA_DATE
2680 , grp.PA_PERIOD_NAME
2681 , grp.TXN_CURRENCY_CODE
2682 , sum(grp.TXN_REVENUE)
2683 , sum(grp.TXN_RAW_COST)
2684 , sum(grp.TXN_BILL_RAW_COST)
2685 , sum(grp.TXN_BURDENED_COST)
2686 , sum(grp.TXN_BILL_BURDENED_COST)
2687 , sum(grp.TXN_UBR)
2688 , sum(grp.TXN_UER)
2689 , sum(grp.PRJ_REVENUE)
2690 , sum(grp.PRJ_RAW_COST)
2691 , sum(grp.PRJ_BILL_RAW_COST)
2692 , sum(grp.PRJ_BURDENED_COST)
2693 , sum(grp.PRJ_BILL_BURDENED_COST)
2694 , sum(grp.PRJ_UBR)
2695 , sum(grp.PRJ_UER)
2696 , sum(grp.POU_REVENUE)
2697 , sum(grp.POU_RAW_COST)
2698 , sum(grp.POU_BILL_RAW_COST)
2699 , sum(grp.POU_BURDENED_COST)
2700 , sum(grp.POU_BILL_BURDENED_COST)
2701 , sum(grp.POU_UBR)
2702 , sum(grp.POU_UER)
2703 , sum(grp.EOU_RAW_COST)
2704 , sum(grp.EOU_BILL_RAW_COST)
2705 , sum(grp.EOU_BURDENED_COST)
2706 , sum(grp.EOU_BILL_BURDENED_COST)
2707 , sum(grp.EOU_UBR)
2708 , sum(grp.EOU_UER)
2709 , sum(grp.QUANTITY)
2710 , sum(grp.BILL_QUANTITY)
2711 FROM (
2712 SELECT /*+ ORDERED */
2713 p_worker_id AS WORKER_ID
2714 , 1 AS SLICE_ID
2715 , erdl.Project_ID AS PROJECT_ID
2716 , scope.Task_ID AS TASK_ID
2717 , -1 AS PERSON_ID
2718 , scope.Project_Org_ID AS PROJECT_ORG_ID
2719 , scope.Project_Organization_ID AS PROJECT_ORGANIZATION_ID
2720 , scope.Project_Type_Class AS PROJECT_TYPE_CLASS
2721 , scope.Customer_ID AS CUSTOMER_ID
2722 , -1 AS EXPENDITURE_ORG_ID
2723 , scope.Expenditure_Organization_ID AS EXPENDITURE_ORGANIZATION_ID
2724 , -1 AS JOB_ID
2725 , -1 AS VENDOR_ID
2726 , -1 AS WORK_TYPE_ID
2727 , scope.Exp_Evt_Type_ID AS EXP_EVT_TYPE_ID
2728 , PJI_FM_SUM_MAIN.g_null AS EXPENDITURE_TYPE
2729 , scope.Event_Type AS EVENT_TYPE
2730 , scope.Event_Type_Classification AS EVENT_TYPE_CLASSIFICATION
2731 , PJI_FM_SUM_MAIN.g_null AS EXPENDITURE_CATEGORY
2732 , scope.Revenue_Category AS REVENUE_CATEGORY
2733 , 'PJI$NULL' AS NON_LABOR_RESOURCE
2734 , -1 AS BOM_LABOR_RESOURCE_ID
2735 , -1 AS BOM_EQUIPMENT_RESOURCE_ID
2736 , -1 AS INVENTORY_ITEM_ID
2737 , PJI_FM_SUM_MAIN.g_null AS SYSTEM_LINKAGE_FUNCTION
2738 , 'Y' AS PJI_PROJECT_RECORD_FLAG
2739 , 'N' AS PJI_RESOURCE_RECORD_FLAG
2740 , -1 AS CODE_COMBINATION_ID
2741 , Greatest(scope.GL_Date,l_min_date) AS PRVDR_GL_DATE
2742 , Greatest(scope.GL_Date,l_min_date) AS RECVR_GL_DATE
2743 , scope.GL_Period_Name AS GL_PERIOD_NAME
2744 , Greatest(scope.PA_Date,l_min_date) AS PRVDR_PA_DATE
2745 , Greatest(scope.PA_Date,l_min_date) AS RECVR_PA_DATE
2746 , scope.PA_Period_Name AS PA_PERIOD_NAME
2747 , erdl.Funding_Currency_Code AS TXN_CURRENCY_CODE
2748 , decode(scope.line_source_type,
2749 'R', (erdl.Funding_Revenue_Amount),
2750 'L', (-erdl.Funding_Revenue_Amount)
2751 ) AS TXN_REVENUE
2752 , to_number(null) AS TXN_RAW_COST
2753 , to_number(null) AS TXN_BILL_RAW_COST
2754 , to_number(null) AS TXN_BURDENED_COST
2755 , to_number(null) AS TXN_BILL_BURDENED_COST
2756 , to_number(null) AS TXN_UBR
2757 , to_number(null) AS TXN_UER
2758 , decode(scope.line_source_type,
2759 'R', (erdl.Project_Revenue_Amount),
2760 'L', (-erdl.Project_Revenue_Amount)
2761 ) AS PRJ_REVENUE
2762 , to_number(null) AS PRJ_RAW_COST
2763 , to_number(null) AS PRJ_BILL_RAW_COST
2764 , to_number(null) AS PRJ_BURDENED_COST
2765 , to_number(null) AS PRJ_BILL_BURDENED_COST
2766 , to_number(null) AS PRJ_UBR
2767 , to_number(null) AS PRJ_UER
2768 , decode(scope.line_source_type,
2769 'R', (erdl.Projfunc_Revenue_Amount),
2770 'L', (-erdl.Projfunc_Revenue_Amount)
2771 ) AS POU_REVENUE
2772 , to_number(null) AS POU_RAW_COST
2773 , to_number(null) AS POU_BILL_RAW_COST
2774 , to_number(null) AS POU_BURDENED_COST
2775 , to_number(null) AS POU_BILL_BURDENED_COST
2776 , to_number(null) AS POU_UBR
2777 , to_number(null) AS POU_UER
2778 , to_number(null) AS EOU_RAW_COST
2779 , to_number(null) AS EOU_BILL_RAW_COST
2780 , to_number(null) AS EOU_BURDENED_COST
2781 , to_number(null) AS EOU_BILL_BURDENED_COST
2782 , to_number(null) AS EOU_UBR
2783 , to_number(null) AS EOU_UER
2784 , to_number(null) AS QUANTITY
2785 , to_number(null) AS BILL_QUANTITY
2786 FROM
2787 PJI_FM_REXT_ERDL scope
2788 , pa_cust_event_rdl_all erdl
2789 WHERE
2790 scope.worker_id = p_worker_id
2791 and scope.erdl_rowid = erdl.rowid
2792 and scope.event_num = erdl.event_num
2793 and NVL(scope.task_id,-1) = NVL(erdl.task_id,-1)
2794 ) grp
2795 GROUP BY
2796 grp.WORKER_ID
2797 , grp.SLICE_ID
2798 , grp.PROJECT_ID
2799 , grp.TASK_ID
2800 , grp.PERSON_ID
2801 , grp.PROJECT_ORG_ID
2802 , grp.PROJECT_ORGANIZATION_ID
2803 , grp.PROJECT_TYPE_CLASS
2804 , grp.CUSTOMER_ID
2805 , grp.EXPENDITURE_ORG_ID
2806 , grp.EXPENDITURE_ORGANIZATION_ID
2807 , grp.JOB_ID
2808 , grp.VENDOR_ID
2809 , grp.WORK_TYPE_ID
2810 , grp.EXP_EVT_TYPE_ID
2811 , grp.EXPENDITURE_TYPE
2812 , grp.EVENT_TYPE
2813 , grp.EVENT_TYPE_CLASSIFICATION
2814 , grp.EXPENDITURE_CATEGORY
2815 , grp.REVENUE_CATEGORY
2816 , grp.NON_LABOR_RESOURCE
2817 , grp.BOM_LABOR_RESOURCE_ID
2818 , grp.BOM_EQUIPMENT_RESOURCE_ID
2819 , grp.INVENTORY_ITEM_ID
2820 , grp.SYSTEM_LINKAGE_FUNCTION
2821 , grp.PJI_PROJECT_RECORD_FLAG
2822 , grp.PJI_RESOURCE_RECORD_FLAG
2823 , grp.CODE_COMBINATION_ID
2824 , grp.PRVDR_GL_DATE
2825 , grp.RECVR_GL_DATE
2826 , grp.GL_PERIOD_NAME
2827 , grp.PRVDR_PA_DATE
2828 , grp.RECVR_PA_DATE
2829 , grp.PA_PERIOD_NAME
2830 , grp.TXN_CURRENCY_CODE
2831 ;
2832
2833 end if; -- EXTRACTION_TYPE <> 'FULL'
2834
2835 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL(p_worker_id);');
2836
2837 -- truncate intermediate tables no longer required
2838 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
2839 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_ERDL' , 'NORMAL',null);
2840
2841 commit;
2842
2843 end EXTRACT_BATCH_ERDL;
2844
2845
2846 -- -----------------------------------------------------
2847 -- procedure EXTRACT_BATCH_DINV
2848 -- -----------------------------------------------------
2849 procedure EXTRACT_BATCH_DINV (p_worker_id in number) is
2850
2851 l_process varchar2(30);
2852 l_extraction_type varchar2(30);
2853 l_from_project_id number := 0;
2854 l_to_project_id number := 0;
2855
2856 begin
2857
2858 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2859
2860 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINV(p_worker_id);')) then
2861 return;
2862 end if;
2863
2864 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
2865
2866 INSERT /*+ APPEND */ INTO PJI_FM_EXTR_DINVC
2867 ( WORKER_ID
2868 , ROW_ID
2869 , PROJECT_ORG_ID
2870 , PROJECT_ORGANIZATION_ID
2871 , PROJECT_ID
2872 , PJI_PROJECT_STATUS
2873 , DRAFT_INVOICE_NUM
2874 , UNBILLED_RECEIVABLE_DR
2875 , UNEARNED_REVENUE_CR
2876 , TRANSFER_STATUS_CODE
2877 , GL_DATE
2878 , PA_DATE
2879 , SYSTEM_REFERENCE
2880 , APPROVED_DATE
2881 , APPROVED_BY_PERSON_ID
2882 , CANCEL_CREDIT_MEMO_FLAG
2883 , WRITE_OFF_FLAG
2884 , INTER_COMPANY_BILLING_FLAG
2885 , PJI_SUMMARIZED_FLAG
2886 , CUSTOMER_ID
2887 , APPROVED_FLAG
2888 , PJI_DATE_RANGE_FLAG
2889 )
2890 SELECT /*+ ordered
2891 full(bat) use_hash(bat) parallel(bat)
2892 full(ppa) use_hash(ppa) parallel(ppa)
2893 full(ptyp) use_hash(ptyp)
2894 full(dinv) use_hash(dinv) parallel(dinv)
2895 full(agr) use_hash(agr) parallel(agr) */
2896 p_worker_id worker_id
2897 , dinv.rowid row_id
2898 , nvl(ppa.org_id, -1) project_org_id
2899 , ppa.carrying_out_organization_id project_organization_id
2900 , dinv.project_id project_id
2901 , bat.pji_project_status pji_project_status
2902 , dinv.draft_invoice_num draft_invoice_num
2903 , dinv.unbilled_receivable_dr unbilled_receivable_dr
2904 , dinv.unearned_revenue_cr unearned_revenue_cr
2905 , dinv.transfer_status_code transfer_status_code
2906 , dinv.gl_date gl_date
2907 , dinv.pa_date pa_date
2908 , dinv.system_reference system_reference
2909 , dinv.approved_date approved_date
2910 , dinv.approved_by_person_id approved_by_person_id
2911 , nvl2(dinv.draft_invoice_num_credited,'Y','N') cancel_credit_memo_flag
2912 , dinv.write_off_flag write_off_flag
2913 , ptyp.cc_prvdr_flag inter_company_billing_flag
2914 , dinv.pji_summarized_flag pji_summarized_flag
2915 , agr.customer_id customer_id
2916 , decode(nvl(dinv.approved_by_person_id,
2917 -1), -1, 'N','Y') approved_flag
2918 , 'Y' pji_date_range_flag
2919 -- the flag cc_prvdr_flag on the project_type indicates whether
2920 -- the project is used for inter project billings
2921 -- since we are considering only external revenue to be consistent we
2922 -- need to consider only the external invoices
2923 -- NOTE for cost we will consider everything (external + internal)
2924 -- this skews the margin but ...
2925 FROM
2926 pji_fm_proj_batch_map bat
2927 , pa_projects_all ppa
2928 , pa_project_types_all ptyp
2929 , pa_draft_invoices_all dinv
2930 , pa_agreements_all agr
2931 WHERE
2932 l_extraction_type = 'FULL'
2933 and bat.worker_id = p_worker_id
2934 and ppa.project_id = bat.project_id
2935 and ppa.project_type = ptyp.project_type
2936 and nvl(ppa.org_id,-1) = nvl(ptyp.org_id,-1)
2937 -- and ptyp.cc_prvdr_flag <> 'Oracle Inter-Project'
2938 and dinv.gl_date is not null
2939 and dinv.pa_date is not null
2940 and ppa.project_id = dinv.project_id
2941 and bat.extraction_type = 'F'
2942 -- the pji_summarized_flag will have other values besides N and null
2943 -- to indicate if the invoice is still open
2944 -- Thus for incremental we need to pick all the invoices which have the
2945 -- flag as not null. Then only if the flag is N do we do the incremental
2946 -- processing. But if the value is something else then we use it only to
2947 -- check activities that might have happened on the AR side
2948 -- and dinv.gl_date between bat.start_date and bat.end_date
2949 and dinv.system_reference is not null
2950 and dinv.system_reference <> 0
2951 and dinv.agreement_id = agr.agreement_id
2952 union all
2953 SELECT /*+ ordered
2954 full(bat)
2955 index(drv, PA_DRAFT_INVOICES_U1)
2956 use_nl(dinv, ppa, ptyp, agr)
2957 parallel(bat) parallel(dinv) parallel(ppa)
2958 parallel(ptyp) parallel(agr) */
2959 p_worker_id worker_id
2960 , dinv.rowid row_id
2961 , nvl(ppa.org_id, -1) project_org_id
2962 , ppa.carrying_out_organization_id project_organization_id
2963 , dinv.project_id project_id
2964 , bat.pji_project_status pji_project_status
2965 , dinv.draft_invoice_num draft_invoice_num
2966 , dinv.unbilled_receivable_dr unbilled_receivable_dr
2967 , dinv.unearned_revenue_cr unearned_revenue_cr
2968 , dinv.transfer_status_code transfer_status_code
2969 , dinv.gl_date gl_date
2970 , dinv.pa_date pa_date
2971 , dinv.system_reference system_reference
2972 , dinv.approved_date approved_date
2973 , dinv.approved_by_person_id approved_by_person_id
2974 , nvl2(dinv.draft_invoice_num_credited,'Y','N') cancel_credit_memo_flag
2975 , dinv.write_off_flag write_off_flag
2976 , ptyp.cc_prvdr_flag inter_company_billing_flag
2977 , dinv.pji_summarized_flag pji_summarized_flag
2978 , agr.customer_id customer_id
2979 , decode(nvl(dinv.approved_by_person_id,
2980 -1), -1, 'N','Y') approved_flag
2981 , 'Y' pji_date_range_flag
2982 -- the flag cc_prvdr_flag on the project_type indicates whether
2983 -- the project is used for inter project billings
2984 -- since we are considering only external revenue to be consistent we
2985 -- need to consider only the external invoices
2986 -- NOTE for cost we will consider everything (external + internal)
2987 -- this skews the margin but ...
2988 FROM
2989 pji_fm_proj_batch_map bat
2990 , pa_draft_invoices_all dinv
2991 , pa_projects_all ppa
2992 , pa_project_types_all ptyp
2993 , pa_agreements_all agr
2994 WHERE
2995 l_extraction_type = 'INCREMENTAL'
2996 and bat.worker_id = p_worker_id
2997 and bat.project_id = dinv.project_id
2998 and ppa.project_id = bat.project_id
2999 and ppa.project_type = ptyp.project_type
3000 and nvl(ppa.org_id,-1) = nvl(ptyp.org_id,-1)
3001 -- and ptyp.cc_prvdr_flag <> 'Oracle Inter-Project'
3002 and dinv.gl_date is not null
3003 and dinv.pa_date is not null
3004 and ppa.project_id = dinv.project_id
3005 and bat.extraction_type = 'F'
3006 -- the pji_summarized_flag will have other values besides N and null
3007 -- to indicate if the invoice is still open
3008 -- Thus for incremental we need to pick all the invoices which have the
3009 -- flag as not null. Then only if the flag is N do we do the incremental
3010 -- processing. But if the value is something else then we use it only to
3011 -- check activities that might have happened on the AR side
3012 -- and dinv.gl_date between bat.start_date and bat.end_date
3013 and dinv.system_reference is not null
3014 and dinv.system_reference <> 0
3015 and dinv.agreement_id = agr.agreement_id
3016 union all
3017 SELECT /*+ ordered
3018 index(dinv PA_DRAFT_INVOICES_N11)
3019 full(bat) use_nl(dinv, ppa, ptyp, agr)
3020 parallel(bat) parallel(dinv) parallel(ppa)
3021 parallel(ptyp) parallel(agr) */
3022 p_worker_id worker_id
3023 , dinv.rowid row_id
3024 , nvl(ppa.org_id, -1) project_org_id
3025 , ppa.carrying_out_organization_id project_organization_id
3026 , dinv.project_id project_id
3027 , bat.pji_project_status pji_project_status
3028 , dinv.draft_invoice_num draft_invoice_num
3029 , dinv.unbilled_receivable_dr unbilled_receivable_dr
3030 , dinv.unearned_revenue_cr unearned_revenue_cr
3031 , dinv.transfer_status_code transfer_status_code
3032 , dinv.gl_date gl_date
3033 , dinv.pa_date pa_date
3034 , dinv.system_reference system_reference
3035 , dinv.approved_date approved_date
3036 , dinv.approved_by_person_id approved_by_person_id
3037 , nvl2(dinv.draft_invoice_num_credited,'Y','N') cancel_credit_memo_flag
3038 , dinv.write_off_flag write_off_flag
3039 , ptyp.cc_prvdr_flag inter_company_billing_flag
3040 , dinv.pji_summarized_flag pji_summarized_flag
3041 , agr.customer_id customer_id
3042 , decode(nvl(dinv.approved_by_person_id,
3043 -1), -1, 'N','Y') approved_flag
3044 , 'Y' pji_date_range_flag
3045 -- the flag cc_prvdr_flag on the project_type indicates whether
3046 -- the project is used for inter project billings
3047 -- since we are considering only external revenue to be consistent we
3048 -- need to consider only the external invoices
3049 -- NOTE for cost we will consider everything (external + internal)
3050 -- this skews the margin but ...
3051 FROM
3052 pji_fm_proj_batch_map bat
3053 , pa_draft_invoices_all dinv
3054 , pa_projects_all ppa
3055 , pa_project_types_all ptyp
3056 , pa_agreements_all agr
3057 WHERE
3058 l_extraction_type = 'INCREMENTAL'
3059 and bat.worker_id = p_worker_id
3060 and ppa.project_id = bat.project_id
3061 and dinv.project_id = bat.project_id
3062 and ppa.project_type = ptyp.project_type
3063 and nvl(ppa.org_id,-1) = nvl(ptyp.org_id,-1)
3064 -- and ptyp.cc_prvdr_flag <> 'Oracle Inter-Project'
3065 and dinv.gl_date is not null
3066 and dinv.pa_date is not null
3067 and ppa.project_id = dinv.project_id
3068 and bat.extraction_type = 'I'
3069 and dinv.pji_summarized_flag = 'N'
3070 -- the pji_summarized_flag will have other values besides N and null
3071 -- to indicate if the invoice is still open
3072 -- Thus for incremental we need to pick all the invoices which have the
3073 -- flag as not null. Then only if the flag is N do we do the incremental
3074 -- processing. But if the value is something else then we use it only to
3075 -- check activities that might have happened on the AR side
3076 -- and dinv.gl_date between bat.start_date and bat.end_date
3077 and dinv.system_reference is not null
3078 and dinv.system_reference <> 0
3079 and dinv.agreement_id = agr.agreement_id
3080 union all
3081 SELECT /*+ ordered
3082 full(bat) use_hash(bat) parallel(bat)
3083 full(ppa) use_hash(ppa) parallel(ppa)
3084 full(ptyp) use_hash(ptyp)
3085 full(dinv) use_hash(dinv) parallel(dinv)
3086 full(agr) use_hash(agr) parallel(agr) */
3087 p_worker_id worker_id
3088 , dinv.rowid row_id
3089 , nvl(ppa.org_id, -1) project_org_id
3090 , ppa.carrying_out_organization_id project_organization_id
3091 , dinv.project_id project_id
3092 , bat.pji_project_status pji_project_status
3093 , dinv.draft_invoice_num draft_invoice_num
3094 , dinv.unbilled_receivable_dr unbilled_receivable_dr
3095 , dinv.unearned_revenue_cr unearned_revenue_cr
3096 , dinv.transfer_status_code transfer_status_code
3097 , dinv.gl_date gl_date
3098 , dinv.pa_date pa_date
3099 , dinv.system_reference system_reference
3100 , dinv.approved_date approved_date
3101 , dinv.approved_by_person_id approved_by_person_id
3102 , nvl2(dinv.draft_invoice_num_credited,'Y','N') cancel_credit_memo_flag
3103 , dinv.write_off_flag write_off_flag
3104 , ptyp.cc_prvdr_flag inter_company_billing_flag
3105 , dinv.pji_summarized_flag pji_summarized_flag
3106 , agr.customer_id customer_id
3107 , decode(nvl(dinv.approved_by_person_id,
3108 -1), -1, 'N','Y') approved_flag
3109 , 'Y' pji_date_range_flag
3110 -- the flag cc_prvdr_flag on the project_type indicates whether
3111 -- the project is used for inter project billings
3112 -- since we are considering only external revenue to be consistent we
3113 -- need to consider only the external invoices
3114 -- NOTE for cost we will consider everything (external + internal)
3115 -- this skews the margin but ...
3116 FROM
3117 pji_fm_proj_batch_map bat
3118 , pa_projects_all ppa
3119 , pa_project_types_all ptyp
3120 , pa_draft_invoices_all dinv
3121 , pa_agreements_all agr
3122 WHERE
3123 l_extraction_type = 'PARTIAL'
3124 and bat.worker_id = p_worker_id
3125 and ppa.project_id = bat.project_id
3126 and ppa.project_type = ptyp.project_type
3127 and nvl(ppa.org_id,-1) = nvl(ptyp.org_id,-1)
3128 -- and ptyp.cc_prvdr_flag <> 'Oracle Inter-Project'
3129 and dinv.gl_date is not null
3130 and dinv.pa_date is not null
3131 and ppa.project_id = dinv.project_id
3132 and bat.extraction_type = 'P'
3133 -- the pji_summarized_flag will have other values besides N and null
3134 -- to indicate if the invoice is still open
3135 -- Thus for incremental we need to pick all the invoices which have the
3136 -- flag as not null. Then only if the flag is N do we do the incremental
3137 -- processing. But if the value is something else then we use it only to
3138 -- check activities that might have happened on the AR side
3139 -- and dinv.gl_date between bat.start_date and bat.end_date
3140 and dinv.system_reference is not null
3141 and dinv.system_reference <> 0
3142 and dinv.agreement_id = agr.agreement_id;
3143
3144 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINV(p_worker_id);');
3145
3146 commit;
3147
3148 end EXTRACT_BATCH_DINV;
3149
3150 -- -----------------------------------------------------
3151 -- procedure MARK_EXTRACTED_DINV_ROWS
3152 -- -----------------------------------------------------
3153 procedure MARK_EXTRACTED_DINV_ROWS (p_worker_id in number) is
3154
3155 l_process varchar2(30);
3156 l_extraction_type varchar2(15);
3157
3158 begin
3159
3160 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3161 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3162
3163 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_DINV_ROWS(p_worker_id);')) then
3164 return;
3165 end if;
3166
3167 UPDATE pa_draft_invoices_all dinv
3168 SET dinv.pji_summarized_flag = 'O'
3169 -- later the flag is updated to null for those invoices that are closed
3170 WHERE dinv.rowid in (select row_id
3171 from PJI_FM_EXTR_DINVC
3172 where worker_id = p_worker_id
3173 and transfer_status_code = 'A'
3174 )
3175 AND ( (l_extraction_type = 'INCREMENTAL'
3176 and nvl(dinv.pji_summarized_flag,'O') <> 'O')
3177 or
3178 l_extraction_type <> 'INCREMENTAL'
3179 )
3180 ;
3181
3182 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_DINV_ROWS(p_worker_id);');
3183
3184 commit;
3185
3186 end MARK_EXTRACTED_DINV_ROWS;
3187
3188
3189 -- -----------------------------------------------------
3190 -- procedure EXTRACT_BATCH_DINVITEM
3191 -- -----------------------------------------------------
3192 procedure EXTRACT_BATCH_DINVITEM (p_worker_id in number) is
3193
3194 l_process varchar2(30);
3195 l_extraction_type varchar2(30);
3196 l_from_project_id number := 0;
3197 l_to_project_id number := 0;
3198
3199 begin
3200
3201 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3202
3203 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINVITEM(p_worker_id);')) then
3204 return;
3205 end if;
3206
3207 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
3208 'CURRENT_BATCH') = 1) then
3209 -- implicit commit
3210 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
3211 tabname => 'PJI_FM_EXTR_DINVC',
3212 percent => 10,
3213 degree => PJI_UTILS.
3214 GET_DEGREE_OF_PARALLELISM);
3215 -- implicit commit
3216 FND_STATS.GATHER_COLUMN_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
3217 tabname => 'PJI_FM_EXTR_DINVC',
3218 colname => 'PROJECT_ID',
3219 percent => 10,
3220 degree => PJI_UTILS.
3221 GET_DEGREE_OF_PARALLELISM);
3222 end if;
3223
3224 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3225
3226 INSERT /*+ APPEND */ INTO PJI_FM_EXTR_DINVCITM
3227 ( WORKER_ID
3228 , PROJECT_ORG_ID
3229 , PROJECT_ORGANIZATION_ID
3230 , PROJECT_ID
3231 , DRAFT_INVOICE_NUM
3232 , GL_DATE
3233 , PA_DATE
3234 , CANCEL_CREDIT_MEMO_FLAG
3235 , WRITE_OFF_FLAG
3236 , INTER_COMPANY_BILLING_FLAG
3237 , PJI_SUMMARIZED_FLAG
3238 , POU_INVOICE_AMOUNT
3239 , PRJ_INVOICE_AMOUNT
3240 , CUSTOMER_ID
3241 , APPROVED_FLAG
3242 , PJI_DATE_RANGE_FLAG
3243 , TRANSFER_STATUS_CODE
3244 , PJI_RECORD_TYPE
3245 , AR_INVOICE_COUNT
3246 , AR_INVOICE_WRITEOFF_COUNT
3247 , AR_CREDIT_MEMO_COUNT
3248 , AR_UNAPPR_INVOICE_COUNT
3249 , AR_APPR_INVOICE_COUNT
3250 )
3251 SELECT /*+ ordered
3252 full(part) use_hash(part)
3253 full(item) use_hash(item) parallel(item) */
3254 p_worker_id worker_id
3255 , nvl(part.project_org_id, -1) project_org_id
3256 , part.project_organization_id project_organization_id
3257 , part.project_id project_id
3258 , part.draft_invoice_num draft_invoice_num
3259 , part.gl_date gl_date
3260 , part.pa_date pa_date
3261 , part.cancel_credit_memo_flag cancel_credit_memo_flag
3262 , part.write_off_flag write_off_flag
3263 , part.inter_company_billing_flag inter_company_billing_flag
3264 , part.pji_summarized_flag pji_summarized_flag
3265 , nvl(sum(item.projfunc_bill_amount),0) pou_invoice_amount
3266 , nvl(sum(item.project_bill_amount),0) prj_invoice_amount
3267 , part.customer_id customer_id
3268 , part.approved_flag approved_flag
3269 , part.pji_date_range_flag pji_date_range_flag
3270 , part.transfer_status_code transfer_status_code
3271 , decode(part.transfer_status_code, -- Activity 'A' vs Snapshot 'S'
3272 'A','A','S') pji_record_type
3273 , to_number(null) ar_invoice_count
3274 , to_number(null) ar_invoice_writeoff_count
3275 , to_number(null) ar_credit_memo_count
3276 , to_number(null) ar_unappr_invoice_count
3277 , to_number(null) ar_appr_invoice_count
3278 FROM
3279 PJI_FM_EXTR_DINVC part
3280 , pa_draft_invoice_items item
3281 WHERE
3282 l_extraction_type = 'FULL'
3283 and part.worker_id = p_worker_id
3284 and part.project_id = item.project_id
3285 and part.draft_invoice_num = item.draft_invoice_num
3286 and item.invoice_line_type <> 'NET ZERO ADJUSTMENT'
3287 and part.gl_date is not null
3288 and part.pa_date is not null
3289 GROUP BY part.project_id,
3290 nvl(part.project_org_id, -1),
3291 part.project_organization_id,
3292 part.draft_invoice_num,
3293 part.gl_date,
3294 part.pa_date,
3295 part.write_off_flag,
3296 part.customer_id,
3297 part.approved_flag,
3298 part.pji_date_range_flag,
3299 part.transfer_status_code,
3300 part.cancel_credit_memo_flag,
3301 part.inter_company_billing_flag,
3302 part.pji_summarized_flag
3303 union all
3304 SELECT /*+ ordered
3305 full(part)
3306 */
3307 p_worker_id worker_id
3308 , nvl(part.project_org_id, -1) project_org_id
3309 , part.project_organization_id project_organization_id
3310 , part.project_id project_id
3311 , part.draft_invoice_num draft_invoice_num
3312 , part.gl_date gl_date
3313 , part.pa_date pa_date
3314 , part.cancel_credit_memo_flag cancel_credit_memo_flag
3315 , part.write_off_flag write_off_flag
3316 , part.inter_company_billing_flag inter_company_billing_flag
3317 , part.pji_summarized_flag pji_summarized_flag
3318 , nvl(sum(item.projfunc_bill_amount),0) pou_invoice_amount
3319 , nvl(sum(item.project_bill_amount),0) prj_invoice_amount
3320 , part.customer_id customer_id
3321 , part.approved_flag approved_flag
3322 , part.pji_date_range_flag pji_date_range_flag
3323 , part.transfer_status_code transfer_status_code
3324 , decode(part.transfer_status_code, -- Activity 'A' vs Snapshot 'S'
3325 'A','A','S') pji_record_type
3326 , to_number(null) ar_invoice_count
3327 , to_number(null) ar_invoice_writeoff_count
3328 , to_number(null) ar_credit_memo_count
3329 , to_number(null) ar_unappr_invoice_count
3330 , to_number(null) ar_appr_invoice_count
3331 FROM
3332 PJI_FM_EXTR_DINVC part
3333 , pa_draft_invoice_items item
3334 WHERE
3335 l_extraction_type = 'INCREMENTAL'
3336 and part.worker_id = p_worker_id
3337 and part.project_id = item.project_id
3338 and part.draft_invoice_num = item.draft_invoice_num
3339 and item.invoice_line_type <> 'NET ZERO ADJUSTMENT'
3340 and part.gl_date is not null
3341 and part.pa_date is not null
3342 GROUP BY part.project_id,
3343 nvl(part.project_org_id, -1),
3344 part.project_organization_id,
3345 part.draft_invoice_num,
3346 part.gl_date,
3347 part.pa_date,
3348 part.write_off_flag,
3349 part.customer_id,
3350 part.approved_flag,
3351 part.pji_date_range_flag,
3352 part.transfer_status_code,
3353 part.cancel_credit_memo_flag,
3354 part.inter_company_billing_flag,
3355 part.pji_summarized_flag
3356 union all
3357 SELECT /*+ ordered
3358 full(part) use_hash(part)
3359 full(item) use_hash(item) parallel(item) */
3360 p_worker_id worker_id
3361 , nvl(part.project_org_id, -1) project_org_id
3362 , part.project_organization_id project_organization_id
3363 , part.project_id project_id
3364 , part.draft_invoice_num draft_invoice_num
3365 , part.gl_date gl_date
3366 , part.pa_date pa_date
3367 , part.cancel_credit_memo_flag cancel_credit_memo_flag
3368 , part.write_off_flag write_off_flag
3369 , part.inter_company_billing_flag inter_company_billing_flag
3370 , part.pji_summarized_flag pji_summarized_flag
3371 , nvl(sum(item.projfunc_bill_amount),0) pou_invoice_amount
3372 , nvl(sum(item.project_bill_amount),0) prj_invoice_amount
3373 , part.customer_id customer_id
3374 , part.approved_flag approved_flag
3375 , part.pji_date_range_flag pji_date_range_flag
3376 , part.transfer_status_code transfer_status_code
3377 , decode(part.transfer_status_code, -- Activity 'A' vs Snapshot 'S'
3378 'A','A','S') pji_record_type
3379 , to_number(null) ar_invoice_count
3380 , to_number(null) ar_invoice_writeoff_count
3381 , to_number(null) ar_credit_memo_count
3382 , to_number(null) ar_unappr_invoice_count
3383 , to_number(null) ar_appr_invoice_count
3384 FROM
3385 PJI_FM_EXTR_DINVC part
3386 , pa_draft_invoice_items item
3387 WHERE
3388 l_extraction_type = 'PARTIAL'
3389 and part.worker_id = p_worker_id
3390 and part.project_id = item.project_id
3391 and part.draft_invoice_num = item.draft_invoice_num
3392 and item.invoice_line_type <> 'NET ZERO ADJUSTMENT'
3393 and part.gl_date is not null
3394 and part.pa_date is not null
3395 GROUP BY part.project_id,
3396 nvl(part.project_org_id, -1),
3397 part.project_organization_id,
3398 part.draft_invoice_num,
3399 part.gl_date,
3400 part.pa_date,
3401 part.write_off_flag,
3402 part.customer_id,
3403 part.approved_flag,
3404 part.pji_date_range_flag,
3405 part.transfer_status_code,
3406 part.cancel_credit_memo_flag,
3407 part.inter_company_billing_flag,
3408 part.pji_summarized_flag
3409 union all
3410 SELECT
3411 p_worker_id worker_id
3412 , nvl(part.project_org_id, -1) project_org_id
3413 , part.project_organization_id project_organization_id
3414 , part.project_id project_id
3415 , part.draft_invoice_num draft_invoice_num
3416 , part.gl_date gl_date
3417 , part.pa_date pa_date
3418 , part.cancel_credit_memo_flag cancel_credit_memo_flag
3419 , part.write_off_flag write_off_flag
3420 , part.inter_company_billing_flag inter_company_billing_flag
3421 , part.pji_summarized_flag pji_summarized_flag
3422 , to_number(null) pou_invoice_amount
3423 , to_number(null) prj_invoice_amount
3424 , part.customer_id customer_id
3425 , part.approved_flag approved_flag
3426 , part.pji_date_range_flag pji_date_range_flag
3427 , part.transfer_status_code transfer_status_code
3428 , decode(part.transfer_status_code, -- Activity 'A' vs Snapshot 'S'
3429 'A','A','S') pji_record_type
3430 , decode(part.pji_date_range_flag || '_' ||
3431 decode(part.transfer_status_code,
3432 'A','A','S'),
3433 'Y_A', 1, 0) ar_invoice_count
3434 , decode(part.pji_date_range_flag || '_' ||
3435 decode(part.transfer_status_code,
3436 'A','A','S') || '_' ||
3437 part.write_off_flag,
3438 'Y_A_Y', 1,0) ar_invoice_writeoff_count
3439 , decode(part.pji_date_range_flag || '_' ||
3440 decode(part.transfer_status_code,
3441 'A','A','S') || '_' ||
3442 part.cancel_credit_memo_flag,
3443 'Y_A_Y', 1,0) ar_credit_memo_count
3444 , decode(decode(part.transfer_status_code,
3445 'A','A','S') || '_' ||
3446 part.approved_flag,
3447 'S_N',1,0) ar_unappr_invoice_count
3448 , decode(decode(part.transfer_status_code,
3449 'A','A','S') || '_' ||
3450 part.approved_flag,
3451 'S_Y',1,0) ar_appr_invoice_count
3452 FROM
3453 PJI_FM_EXTR_DINVC part
3454 WHERE
3455 part.worker_id = p_worker_id
3456 and part.gl_date is not null
3457 and part.pa_date is not null;
3458
3459 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINVITEM(p_worker_id);');
3460
3461 commit;
3462
3463 end EXTRACT_BATCH_DINVITEM;
3464
3465
3466 -- -----------------------------------------------------
3467 -- procedure EXTRACT_BATCH_ARINV
3468 -- -----------------------------------------------------
3469 procedure EXTRACT_BATCH_ARINV (p_worker_id in number) is
3470
3471 l_process varchar2(30);
3472 l_extraction_type varchar2(30);
3473
3474 begin
3475
3476 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3477
3478 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ARINV(p_worker_id);')) then
3479 return;
3480 end if;
3481
3482 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3483
3484 INSERT /*+ APPEND */ INTO PJI_FM_EXTR_ARINV
3485 ( WORKER_ID
3486 , ROW_ID
3487 , PROJECT_ID
3488 , PROJECT_ORG_ID
3489 , PROJECT_ORGANIZATION_ID
3490 , DRAFT_INVOICE_NUM
3491 , CASH_APPLIED_AMOUNT
3492 , AMOUNT_DUE_REMAINING
3493 , AMOUNT_OVERDUE_REMAINING
3494 , MAX_ACTUAL_DATE_CLOSED
3495 , CUSTOMER_ID
3496 , PJI_SUMMARIZED_FLAG
3497 , BATCH_ID
3498 )
3499 SELECT
3500 p_worker_id worker_id
3501 , row_id
3502 , project_id
3503 , project_org_id
3504 , project_organization_id
3505 , draft_invoice_num
3506 , cash_applied_amount
3507 , amount_due_remaining
3508 , amount_overdue_remaining
3509 , actual_date_closed
3510 , customer_id
3511 , pji_summarized_flag
3512 , ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold)
3513 from
3514 (SELECT /*+ ordered
3515 parallel(part)
3516 full(bat) use_hash(bat) */
3517 part.rowid row_id
3518 , part.project_id project_id
3519 , bat.project_org_id
3520 , bat.project_organization_id
3521 , part.draft_invoice_num draft_invoice_num
3522 , nvl(sum(ar.amount_applied),0) cash_applied_amount
3523 , decode(sign(ar.due_date - trunc(sysdate)),
3524 -1, 0, nvl(sum(ar.amount_due_remaining),
3525 0)) amount_due_remaining
3526 , decode(sign(ar.due_date - trunc(sysdate)),
3527 -1, nvl(sum(ar.amount_due_remaining),0),
3528 0) amount_overdue_remaining
3529 , max(ar.actual_date_closed) actual_date_closed
3530 , trx.bill_to_customer_id customer_id
3531 , part.pji_summarized_flag
3532 FROM
3533 pa_draft_invoices_all part
3534 , pji_fm_proj_batch_map bat
3535 , ra_customer_trx_all trx
3536 , ar_payment_schedules_all ar
3537 WHERE
3538 bat.worker_id = p_worker_id
3539 and part.project_id = bat.project_id
3540 and part.gl_date is not null
3541 and part.pa_date is not null
3542 and part.pji_summarized_flag = 'O'
3543 and trx.customer_trx_id = part.system_reference
3544 and ar.customer_trx_id = trx.customer_trx_id
3545 GROUP BY
3546 part.rowid,
3547 part.project_id,
3548 bat.project_org_id,
3549 bat.project_organization_id,
3550 part.draft_invoice_num,
3551 ar.due_date,
3552 trx.bill_to_customer_id,
3553 part.pji_summarized_flag);
3554
3555 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ARINV(p_worker_id);');
3556
3557 commit;
3558
3559 end EXTRACT_BATCH_ARINV;
3560
3561
3562 -- -----------------------------------------------------
3563 -- procedure MARK_FULLY_PAID_INVOICES_PRE
3564 -- -----------------------------------------------------
3565 procedure MARK_FULLY_PAID_INVOICES_PRE (p_worker_id in number) is
3566
3567 l_process varchar2(30);
3568
3569 begin
3570
3571 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3572
3573 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
3574 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_PRE(p_worker_id);')) then
3575 return;
3576 end if;
3577
3578 insert /*+ append */ into PJI_HELPER_BATCH_MAP
3579 (
3580 BATCH_ID,
3581 WORKER_ID,
3582 STATUS
3583 )
3584 select
3585 distinct
3586 BATCH_ID,
3587 null,
3588 null
3589 from
3590 PJI_FM_EXTR_ARINV
3591 where
3592 PJI_SUMMARIZED_FLAG = 'O';
3593
3594 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3595 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_PRE(p_worker_id);');
3596
3597 commit;
3598
3599 end MARK_FULLY_PAID_INVOICES_PRE;
3600
3601
3602 -- -----------------------------------------------------
3603 -- procedure MARK_FULLY_PAID_INVOICES
3604 -- -----------------------------------------------------
3605 procedure MARK_FULLY_PAID_INVOICES (p_worker_id in number) is
3606
3607 l_process varchar2(30);
3608 l_leftover_batches number;
3609 l_helper_batch_id number;
3610 l_row_count number;
3611 l_parallel_processes number;
3612
3613 begin
3614
3615 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3616
3617 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES(p_worker_id);')) then
3618 return;
3619 end if;
3620
3621 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3622 (PJI_FM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
3623
3624 select count(*)
3625 into l_leftover_batches
3626 from PJI_HELPER_BATCH_MAP
3627 where WORKER_ID = p_worker_id and
3628 STATUS = 'P';
3629
3630 l_helper_batch_id := 0;
3631
3632 while l_helper_batch_id >= 0 loop
3633
3634 if (l_leftover_batches > 0) then
3635
3636 l_leftover_batches := l_leftover_batches - 1;
3637
3638 select BATCH_ID
3639 into l_helper_batch_id
3640 from PJI_HELPER_BATCH_MAP
3641 where WORKER_ID = p_worker_id and
3642 STATUS = 'P' and
3643 ROWNUM = 1;
3644
3645 else
3646
3647 update PJI_HELPER_BATCH_MAP
3648 set WORKER_ID = p_worker_id,
3649 STATUS = 'P'
3650 where WORKER_ID is null and
3651 ROWNUM = 1
3652 returning BATCH_ID
3653 into l_helper_batch_id;
3654
3655 end if;
3656
3657 if (sql%rowcount <> 0) then
3658
3659 commit;
3660
3661 UPDATE pa_draft_invoices_all dinv
3662 SET dinv.pji_summarized_flag = NULL
3663 WHERE dinv.rowid in (SELECT /*+ cardinality(ar, 1) */
3664 ar.row_id
3665 FROM PJI_FM_EXTR_ARINV ar
3666 WHERE 1 = 2 -- We will always extract
3667 -- the AR snapshots for now.
3668 AND ar.pji_summarized_flag = 'O'
3669 AND ar.batch_id = l_helper_batch_id);
3670
3671 update PJI_HELPER_BATCH_MAP
3672 set STATUS = 'C'
3673 where WORKER_ID = p_worker_id and
3674 BATCH_ID = l_helper_batch_id;
3675
3676 commit;
3677
3678 else
3679
3680 select count(*)
3681 into l_row_count
3682 from PJI_HELPER_BATCH_MAP
3683 where nvl(STATUS, 'X') <> 'C';
3684
3685 if (l_row_count = 0) then
3686
3687 for x in 2 .. l_parallel_processes loop
3688
3689 update PJI_SYSTEM_PRC_STATUS
3690 set STEP_STATUS = 'C'
3691 where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
3692 STEP_NAME =
3693 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES(p_worker_id);' and
3694 START_DATE is null;
3695
3696 commit;
3697
3698 end loop;
3699
3700 l_helper_batch_id := -1;
3701
3702 else
3703
3704 PJI_PROCESS_UTIL.SLEEP(1); -- so the CPU is not bombarded
3705
3706 end if;
3707
3708 end if;
3709
3710 if (l_helper_batch_id >= 0) then
3711
3712 for x in 2 .. l_parallel_processes loop
3713 if (not PJI_FM_SUM_EXTR.WORKER_STATUS(x, 'OKAY')) then
3714 l_helper_batch_id := -2;
3715 end if;
3716 end loop;
3717
3718 end if;
3719
3720 end loop;
3721
3722 if (l_helper_batch_id <> -2) then
3723
3724 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3725 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES(p_worker_id);');
3726
3727 end if;
3728
3729 commit;
3730
3731 end MARK_FULLY_PAID_INVOICES;
3732
3733
3734 -- -----------------------------------------------------
3735 -- procedure MARK_FULLY_PAID_INVOICES_POST
3736 -- -----------------------------------------------------
3737 procedure MARK_FULLY_PAID_INVOICES_POST (p_worker_id in number) is
3738
3739 l_process varchar2(30);
3740
3741 begin
3742
3743 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3744
3745 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
3746 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_POST(p_worker_id);')) then
3747 return;
3748 end if;
3749
3750 PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
3751 'PJI_HELPER_BATCH_MAP',
3752 'NORMAL',
3753 null);
3754
3755 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3756 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_POST(p_worker_id);');
3757
3758 commit;
3759
3760 end MARK_FULLY_PAID_INVOICES_POST;
3761
3762
3763 -- -----------------------------------------------------
3764 -- procedure CLEANUP
3765 -- -----------------------------------------------------
3766 procedure CLEANUP (p_worker_id in number) is
3767
3768 l_schema varchar2(30);
3769
3770 begin
3771
3772 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
3773
3774 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_FUNDG', 'NORMAL',null);
3775
3776 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DREVN', 'NORMAL',null);
3777
3778 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CDL', 'NORMAL',null);
3779
3780 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CRDL', 'NORMAL',null);
3781
3782 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_ERDL', 'NORMAL',null);
3783
3784 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVC', 'NORMAL',null);
3785
3786 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVCITM', 'NORMAL',null);
3787
3788 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_ARINV', 'NORMAL',null);
3789
3790 end CLEANUP;
3791
3792 end PJI_FM_EXTR;