[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_EXTR
Source
1 package body PJI_FM_EXTR as
2 /* $Header: PJISF06B.pls 120.12.12020000.4 2013/03/28 13:47:28 krkondur 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 , CBS_ELEMENT_ID /* Added for CBS Changes */
980 )
981 SELECT
982 grp.WORKER_ID
983 , grp.SLICE_ID
984 , grp.PROJECT_ID
985 , grp.TASK_ID
986 , grp.PERSON_ID
987 , grp.PROJECT_ORG_ID
988 , grp.PROJECT_ORGANIZATION_ID
989 , grp.PROJECT_TYPE_CLASS
990 , grp.CUSTOMER_ID
991 , grp.EXPENDITURE_ORG_ID
992 , grp.EXPENDITURE_ORGANIZATION_ID
993 , grp.JOB_ID
994 , grp.VENDOR_ID
995 , grp.WORK_TYPE_ID
996 , grp.EXP_EVT_TYPE_ID
997 , grp.EXPENDITURE_TYPE
998 , grp.EVENT_TYPE
999 , grp.EVENT_TYPE_CLASSIFICATION
1000 , grp.EXPENDITURE_CATEGORY
1001 , grp.REVENUE_CATEGORY
1002 , grp.NON_LABOR_RESOURCE
1003 , grp.BOM_LABOR_RESOURCE_ID
1004 , grp.BOM_EQUIPMENT_RESOURCE_ID
1005 , grp.INVENTORY_ITEM_ID
1006 , grp.PO_LINE_ID
1007 , grp.ASSIGNMENT_ID
1008 , grp.SYSTEM_LINKAGE_FUNCTION
1009 , grp.PJI_PROJECT_RECORD_FLAG
1010 , grp.PJI_RESOURCE_RECORD_FLAG
1011 , grp.CODE_COMBINATION_ID
1012 , grp.PRVDR_GL_DATE
1013 , grp.RECVR_GL_DATE
1014 , grp.GL_PERIOD_NAME
1015 , grp.PRVDR_PA_DATE
1016 , grp.RECVR_PA_DATE
1017 , grp.PA_PERIOD_NAME
1018 , grp.EXPENDITURE_ITEM_DATE
1019 , grp.TXN_CURRENCY_CODE
1020 , sum(grp.TXN_REVENUE)
1021 , sum(grp.TXN_RAW_COST)
1022 , sum(grp.TXN_BILL_RAW_COST)
1023 , sum(grp.TXN_BURDENED_COST)
1024 , sum(grp.TXN_BILL_BURDENED_COST)
1025 , sum(grp.TXN_UBR)
1026 , sum(grp.TXN_UER)
1027 , sum(grp.PRJ_REVENUE)
1028 , sum(grp.PRJ_RAW_COST)
1029 , sum(grp.PRJ_BILL_RAW_COST)
1030 , sum(grp.PRJ_BURDENED_COST)
1031 , sum(grp.PRJ_BILL_BURDENED_COST)
1032 , sum(grp.PRJ_UBR)
1033 , sum(grp.PRJ_UER)
1034 , sum(grp.POU_REVENUE)
1035 , sum(grp.POU_RAW_COST)
1036 , sum(grp.POU_BILL_RAW_COST)
1037 , sum(grp.POU_BURDENED_COST)
1038 , sum(grp.POU_BILL_BURDENED_COST)
1039 , sum(grp.POU_UBR)
1040 , sum(grp.POU_UER)
1041 , sum(grp.EOU_RAW_COST)
1042 , sum(grp.EOU_BILL_RAW_COST)
1043 , sum(grp.EOU_BURDENED_COST)
1044 , sum(grp.EOU_BILL_BURDENED_COST)
1045 , sum(grp.EOU_UBR)
1046 , sum(grp.EOU_UER)
1047 , sum(grp.QUANTITY)
1048 , sum(grp.BILL_QUANTITY)
1049 , grp.CBS_ELEMENT_ID /* Added for CBS Changes */
1050 FROM (
1051 SELECT /*+ ORDERED
1052 use_hash(CnR,et,exp,ei)
1053 swap_join_inputs(exp)
1054 swap_join_inputs(ei)
1055 swap_join_inputs(et)
1056 PARALLEL(exp) PARALLEL(ei) PARALLEL(et) */
1057 p_worker_id AS WORKER_ID
1058 , 1 AS SLICE_ID
1059 , CnR.Project_ID AS PROJECT_ID
1060 , ei.Task_ID AS TASK_ID
1061 , decode(exp.Incurred_BY_Person_ID,
1062 null, -1, 0, -1,
1063 exp.Incurred_BY_Person_ID) AS PERSON_ID
1064 , map.Project_Org_ID AS PROJECT_ORG_ID
1065 , map.Project_Organization_ID AS PROJECT_ORGANIZATION_ID
1066 , map.Project_Type_Class AS PROJECT_TYPE_CLASS
1067 , CnR.Customer_ID AS CUSTOMER_ID
1068 , decode(CnR.C_or_R
1069 , 'COST', CnR.Expenditure_Org_ID
1070 , ei.org_id) AS EXPENDITURE_ORG_ID
1071 , nvl(ei.Override_TO_Organization_ID,
1072 exp.Incurred_BY_Organization_ID) AS EXPENDITURE_ORGANIZATION_ID
1073 -- , CnR.Expenditure_Item_ID AS EXPENDITURE_ITEM_ID
1074 , nvl(ei.Job_ID, -1) AS JOB_ID
1075 , nvl(exp.Vendor_ID,-1) AS VENDOR_ID
1076 , decode(CnR.C_or_R,
1077 'COST', nvl(CnR.Work_Type_Id,-1),
1078 nvl(ei.Work_Type_Id, -1)) AS WORK_TYPE_ID
1079 , et.Expenditure_Type_ID AS EXP_EVT_TYPE_ID
1080 , et.Expenditure_Type AS EXPENDITURE_TYPE
1081 , PJI_FM_SUM_MAIN.g_null AS EVENT_TYPE
1082 , PJI_FM_SUM_MAIN.g_null AS EVENT_TYPE_CLASSIFICATION
1083 , et.Expenditure_Category AS EXPENDITURE_CATEGORY
1084 , et.Revenue_Category_Code AS REVENUE_CATEGORY
1085 , ei.Non_Labor_Resource AS NON_LABOR_RESOURCE
1086 , ei.Wip_Resource_ID AS BOM_LABOR_RESOURCE_ID
1087 , ei.Wip_Resource_ID AS BOM_EQUIPMENT_RESOURCE_ID
1088 , ei.Inventory_Item_ID AS INVENTORY_ITEM_ID
1089 , ei.PO_Line_ID AS PO_LINE_ID
1090 , decode(ei.Assignment_ID,
1091 null, -1, 0, -1,
1092 ei.Assignment_ID) AS ASSIGNMENT_ID
1093 , NVL(ei.src_system_linkage_function,
1094 ei.system_linkage_function) AS SYSTEM_LINKAGE_FUNCTION
1095 , decode(CnR.C_or_R,
1096 'COST', 'Y',
1097 'REVENUE', 'Y', 'N') AS PJI_PROJECT_RECORD_FLAG
1098 , decode(exp.Incurred_BY_Person_ID,
1099 null, 'N',
1100 0, 'N',
1101 decode(CnR.C_or_R,
1102 'COST', 'Y',
1103 'REVENUE', 'Y',
1104 'N')) AS PJI_RESOURCE_RECORD_FLAG
1105 , -1 AS CODE_COMBINATION_ID
1106 , greatest(CnR.Prvdr_GL_Date,l_min_date) AS PRVDR_GL_DATE
1107 , greatest(CnR.Recvr_GL_Date,l_min_date) AS RECVR_GL_DATE
1108 , CnR.GL_Period_Name AS GL_PERIOD_NAME
1109 , greatest(CnR.Prvdr_PA_Date,l_min_date) AS PRVDR_PA_DATE
1110 , greatest(CnR.Recvr_PA_Date,l_min_date) AS RECVR_PA_DATE
1111 , CnR.PA_Period_Name AS PA_PERIOD_NAME
1112 , greatest(ei.Expenditure_Item_Date,
1113 l_min_date) AS EXPENDITURE_ITEM_DATE
1114 , CnR.Txn_Currency_Code AS TXN_CURRENCY_CODE
1115 , CnR.Txn_Revenue AS TXN_REVENUE
1116 , CnR.Txn_Raw_Cost AS TXN_RAW_COST
1117 , CnR.Txn_Bill_Raw_Cost AS TXN_BILL_RAW_COST
1118 , CnR.Txn_Burdened_Cost AS TXN_BURDENED_COST
1119 , CnR.Txn_Bill_Burdened_Cost AS TXN_BILL_BURDENED_COST
1120 , CnR.Txn_Ubr AS TXN_UBR
1121 , CnR.Txn_Uer AS TXN_UER
1122 , CnR.Prj_Revenue AS PRJ_REVENUE
1123 , CnR.Prj_Raw_Cost AS PRJ_RAW_COST
1124 , CnR.Prj_Bill_Raw_Cost AS PRJ_BILL_RAW_COST
1125 , CnR.Prj_Burdened_Cost AS PRJ_BURDENED_COST
1126 , CnR.Prj_Bill_Burdened_Cost AS PRJ_BILL_BURDENED_COST
1127 , CnR.Prj_Ubr AS PRJ_UBR
1128 , CnR.Prj_Uer AS PRJ_UER
1129 , CnR.Pou_Revenue AS POU_REVENUE
1130 , CnR.Pou_Raw_Cost AS POU_RAW_COST
1131 , CnR.Pou_Bill_Raw_Cost AS POU_BILL_RAW_COST
1132 , CnR.Pou_Burdened_Cost AS POU_BURDENED_COST
1133 , CnR.Pou_Bill_Burdened_Cost AS POU_BILL_BURDENED_COST
1134 , CnR.Pou_Ubr AS POU_UBR
1135 , CnR.Pou_Uer AS POU_UER
1136 , CnR.Eou_Raw_Cost AS EOU_RAW_COST
1137 , CnR.Eou_Bill_Raw_Cost AS EOU_BILL_RAW_COST
1138 , CnR.Eou_Burdened_Cost AS EOU_BURDENED_COST
1139 , CnR.Eou_Bill_Burdened_Cost AS EOU_BILL_BURDENED_COST
1140 , CnR.Eou_Ubr AS EOU_UBR
1141 , CnR.Eou_Uer AS EOU_UER
1142 , CnR.Quantity AS QUANTITY
1143 , CnR.Bill_Quantity AS BILL_QUANTITY
1144 , CnR.CBS_ELEMENT_ID AS CBS_ELEMENT_ID /* Added for CBS Changes */
1145 FROM
1146 pji_fm_proj_batch_map map,
1147 (
1148 Select /*+ FULL(cdl) PARALLEL(cdl) */
1149 'COST' AS C_or_R
1150 , cdl.Project_ID AS PROJECT_ID
1151 , cdl.Task_ID AS TASK_ID
1152 , -1 AS CUSTOMER_ID
1153 , cdl.Org_ID AS EXPENDITURE_ORG_ID
1154 , cdl.Expenditure_Item_ID AS EXPENDITURE_ITEM_ID
1155 --- , nvl(to_number(cdl.System_Reference1),-1) AS VENDOR_ID
1156 , cdl.work_type_id AS WORK_TYPE_ID
1157 , cdl.GL_Date AS PRVDR_GL_DATE
1158 , nvl(cdl.Recvr_GL_Date,cdl.GL_Date) AS RECVR_GL_DATE
1159 , cdl.Recvr_GL_Period_Name AS GL_PERIOD_NAME
1160 , cdl.PA_DATE AS PRVDR_PA_DATE
1161 , nvl(cdl.Recvr_PA_Date,cdl.PA_Date) AS RECVR_PA_DATE
1162 , cdl.Recvr_PA_Period_Name AS PA_PERIOD_NAME
1163 , cdl.Denom_Currency_Code AS TXN_CURRENCY_CODE
1164 , to_number(null) AS TXN_REVENUE
1165 , nvl(cdl.Denom_Raw_Cost, 0) AS TXN_RAW_COST
1166 , decode(cdl.billable_flag
1167 , 'Y', nvl(cdl.Denom_Raw_Cost, 0)
1168 , 0) AS TXN_BILL_RAW_COST
1169 , nvl(cdl.Denom_Burdened_Cost, 0) AS TXN_BURDENED_COST
1170 , decode(cdl.Billable_Flag
1171 , 'Y', nvl(cdl.Denom_Burdened_Cost, 0)
1172 , 0) AS TXN_BILL_BURDENED_COST
1173 , to_number(null) AS TXN_UBR
1174 , to_number(null) AS TXN_UER
1175 , to_number(null) AS PRJ_REVENUE
1176 , nvl(cdl.Project_Raw_Cost, 0) AS PRJ_RAW_COST
1177 , decode(cdl.billable_flag
1178 , 'Y', nvl(cdl.Project_Raw_Cost, 0)
1179 , 0) AS PRJ_BILL_RAW_COST
1180 , nvl(cdl.Project_Burdened_Cost, 0) AS PRJ_BURDENED_COST
1181 , decode(cdl.Billable_Flag
1182 , 'Y', nvl(cdl.Project_Burdened_Cost, 0)
1183 , 0) AS PRJ_BILL_BURDENED_COST
1184 , to_number(null) AS PRJ_UBR
1185 , to_number(null) AS PRJ_UER
1186 , to_number(null) AS POU_REVENUE
1187 , cdl.AMOUNT AS POU_RAW_COST
1188 , decode(cdl.bILLABLE_fLAG
1189 , 'Y', nvl(cdl.Amount, 0)
1190 , 0) AS POU_BILL_RAW_COST
1191 , nvl(cdl.Burdened_Cost, 0) AS POU_BURDENED_COST
1192 , decode(cdl.Billable_Flag
1193 , 'Y', nvl(cdl.Burdened_Cost, 0)
1194 , 0) AS POU_BILL_BURDENED_COST
1195 , to_number(null) AS POU_UBR
1196 , to_number(null) AS POU_UER
1197 , nvl(cdl.Acct_Raw_Cost, 0) AS EOU_RAW_COST
1198 , decode(cdl.Billable_Flag
1199 , 'Y', nvl(cdl.Acct_Raw_Cost,0)
1200 , 0) AS EOU_BILL_RAW_COST
1201 , nvl(cdl.Acct_Burdened_Cost, 0) AS EOU_BURDENED_COST
1202 , decode(cdl.Billable_Flag
1203 , 'Y', nvl(cdl.Acct_Burdened_Cost, 0)
1204 , 0) AS EOU_BILL_BURDENED_COST
1205 , to_number(null) AS EOU_UBR
1206 , to_number(null) AS EOU_UER
1207 , decode(cdl.line_type,'R',cdl.Quantity,0)
1208 AS QUANTITY /* Modified for bug 9777532 */
1209 , decode(cdl.Billable_Flag
1210 , 'Y', decode(cdl.line_type,'R',cdl.Quantity,0)
1211 , 0) AS BILL_QUANTITY /* Modified for bug 9777532 */
1212 , cdl.CBS_ELEMENT_ID AS CBS_ELEMENT_ID /* Added for CBS Changes */
1213 From pa_cost_distribution_lines_all cdl
1214 Where 1 = 1
1215 And cdl.line_type in ('R','I')
1216 And nvl(cdl.pji_summarized_flag,'Y') <> 'N'
1217 And cdl.gl_date is not null
1218 And cdl.pa_date is not null
1219 UNION ALL
1220 Select /*+ ORDERED
1221 FULL(ag) PARALLEL(ag) use_hash(ag)
1222 FULL(cust) PARALLEL(cust) use_hash(cust)
1223 FULL(drev) PARALLEL(drev) use_hash(drev)
1224 FULL(crdl) PARALLEL(crdl) use_hash(crdl) */
1225 'REVENUE' AS C_or_R
1226 , crdl.Project_ID AS PROJECT_ID
1227 , -1 AS TASK_ID
1228 , cust.Customer_ID AS CUSTOMER_ID
1229 , -1 AS EXPENDITURE_ORG_ID
1230 , crdl.Expenditure_Item_ID AS EXPENDITURE_ITEM_ID
1231 --- , -1 AS VENDOR_ID
1232 , -1 AS WORK_TYPE_ID
1233 , drev.GL_Date AS PRVDR_GL_DATE
1234 , drev.GL_Date AS RECVR_GL_DATE
1235 , drev.GL_Period_Name AS GL_PERIOD_NAME
1236 , drev.PA_Date AS PRVDR_PA_DATE
1237 , drev.PA_Date AS RECVR_PA_DATE
1238 , drev.PA_Period_Name AS PA_PERIOD_NAME
1239 , crdl.Funding_Currency_Code AS TXN_CURRENCY_CODE
1240 , crdl.Funding_Revenue_Amount AS TXN_REVENUE
1241 , to_number(null) AS TXN_RAW_COST
1242 , to_number(null) AS TXN_BILL_RAW_COST
1243 , to_number(null) AS TXN_BURDENED_COST
1244 , to_number(null) AS TXN_BILL_BURDENED_COST
1245 , to_number(null) AS TXN_UBR
1246 , to_number(null) AS TXN_UER
1247 , crdl.Project_Revenue_Amount AS PRJ_REVENUE
1248 , to_number(null) AS PRJ_RAW_COST
1249 , to_number(null) AS PRJ_BILL_RAW_COST
1250 , to_number(null) AS PRJ_BURDENED_COST
1251 , to_number(null) AS PRJ_BILL_BURDENED_COST
1252 , to_number(null) AS PRJ_UBR
1253 , to_number(null) AS PRJ_UER
1254 , crdl.Projfunc_Revenue_Amount AS POU_REVENUE
1255 , to_number(null) AS POU_RAW_COST
1256 , to_number(null) AS POU_BILL_RAW_COST
1257 , to_number(null) AS POU_BURDENED_COST
1258 , to_number(null) AS POU_BILL_BURDENED_COST
1259 , to_number(null) AS POU_UBR
1260 , to_number(null) AS POU_UER
1261 , to_number(null) AS EOU_RAW_COST
1262 , to_number(null) AS EOU_BILL_RAW_COST
1263 , to_number(null) AS EOU_BURDENED_COST
1264 , to_number(null) AS EOU_BILL_BURDENED_COST
1265 , to_number(null) AS EOU_UBR
1266 , to_number(null) AS EOU_UER
1267 , to_number(null) AS QUANTITY
1268 , to_number(null) AS BILL_QUANTITY
1269 , -1 AS CBS_ELEMENT_ID /* Added for CBS Changes */
1270 From PJI_FM_EXTR_DREVN drev
1271 , pa_agreements_all ag
1272 , pa_project_customers cust
1273 , pa_cust_rev_dist_lines_all crdl
1274 Where 1 = 1
1275 And drev.worker_id = p_worker_id
1276 And drev.project_id = crdl.project_id
1277 And drev.draft_revenue_num = crdl.draft_revenue_num
1278 And drev.agreement_id = ag.agreement_id
1279 And drev.project_id = cust.project_id
1280 And ag.customer_id = cust.customer_id
1281 -- And NVL(cust.bill_another_project_flag,'N') <> 'Y' -- ER 6519955
1282 And crdl.function_code NOT IN ('LRL','LRB','URL','URB')
1283 And drev.gl_date is not null
1284 And drev.pa_date is not null
1285 ) CnR
1286 , pa_expenditure_items_all ei
1287 , pa_expenditures_all exp
1288 , pa_expenditure_types et
1289 WHERE 1 = 1
1290 And CnR.expenditure_item_id = ei.expenditure_item_id
1291 And exp.expenditure_id = ei.expenditure_id
1292 And ei.expenditure_type = et.expenditure_type
1293 And CnR.project_id = map.project_id
1294 -- And (NVL(ei.transaction_source,'dummy') <> 'INTERPROJECT_AP_INVOICES' -- ER 6519955
1295 -- OR CnR.C_or_R = 'REVENUE')
1296 ) grp
1297 GROUP BY
1298 grp.WORKER_ID
1299 , grp.SLICE_ID
1300 , grp.PROJECT_ID
1301 , grp.TASK_ID
1302 , grp.PERSON_ID
1303 , grp.PROJECT_ORG_ID
1304 , grp.PROJECT_ORGANIZATION_ID
1305 , grp.PROJECT_TYPE_CLASS
1306 , grp.CUSTOMER_ID
1307 , grp.EXPENDITURE_ORG_ID
1308 , grp.EXPENDITURE_ORGANIZATION_ID
1309 , grp.JOB_ID
1310 , grp.VENDOR_ID
1311 , grp.WORK_TYPE_ID
1312 , grp.EXP_EVT_TYPE_ID
1313 , grp.EXPENDITURE_TYPE
1314 , grp.EVENT_TYPE
1315 , grp.EVENT_TYPE_CLASSIFICATION
1316 , grp.EXPENDITURE_CATEGORY
1317 , grp.REVENUE_CATEGORY
1318 , grp.NON_LABOR_RESOURCE
1319 , grp.BOM_LABOR_RESOURCE_ID
1320 , grp.BOM_EQUIPMENT_RESOURCE_ID
1321 , grp.INVENTORY_ITEM_ID
1322 , grp.PO_LINE_ID
1323 , grp.ASSIGNMENT_ID
1324 , grp.SYSTEM_LINKAGE_FUNCTION
1325 , grp.PJI_PROJECT_RECORD_FLAG
1326 , grp.PJI_RESOURCE_RECORD_FLAG
1327 , grp.CODE_COMBINATION_ID
1328 , grp.PRVDR_GL_DATE
1329 , grp.RECVR_GL_DATE
1330 , grp.GL_PERIOD_NAME
1331 , grp.PRVDR_PA_DATE
1332 , grp.RECVR_PA_DATE
1333 , grp.PA_PERIOD_NAME
1334 , grp.EXPENDITURE_ITEM_DATE
1335 , grp.TXN_CURRENCY_CODE
1336 , grp.CBS_ELEMENT_ID /* Added for CBS Changes */
1337 ;
1338
1339 end if; -- EXTRACTION_TYPE = 'FULL'
1340
1341 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_CRDL_FULL(p_worker_id);');
1342
1343 commit;
1344
1345 end EXTRACT_BATCH_CDL_CRDL_FULL;
1346
1347
1348 -- -----------------------------------------------------
1349 -- procedure EXTRACT_BATCH_ERDL_FULL
1350 -- -----------------------------------------------------
1351 procedure EXTRACT_BATCH_ERDL_FULL(p_worker_id in number) is
1352
1353 l_process varchar2(30);
1354 l_from_project_id number := 0;
1355 l_to_project_id number := 0;
1356 l_min_date date;
1357
1358 begin
1359
1360 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1361
1362 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_FULL(p_worker_id);')) then
1363 return;
1364 end if;
1365
1366 l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
1367 PJI_FM_SUM_MAIN.g_date_mask);
1368
1369 if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') = 'FULL') then
1370
1371 -- insert for erdl
1372 INSERT /*+ APPEND */ INTO PJI_FM_AGGR_FIN1
1373 ( WORKER_ID
1374 , SLICE_ID
1375 , PROJECT_ID
1376 , TASK_ID
1377 , PERSON_ID
1378 , PROJECT_ORG_ID
1379 , PROJECT_ORGANIZATION_ID
1380 , PROJECT_TYPE_CLASS
1381 , CUSTOMER_ID
1382 , EXPENDITURE_ORG_ID
1383 , EXPENDITURE_ORGANIZATION_ID
1384 , JOB_ID
1385 , VENDOR_ID
1386 , WORK_TYPE_ID
1387 , EXP_EVT_TYPE_ID
1388 , EXPENDITURE_TYPE
1389 , EVENT_TYPE
1390 , EVENT_TYPE_CLASSIFICATION
1391 , EXPENDITURE_CATEGORY
1392 , REVENUE_CATEGORY
1393 , NON_LABOR_RESOURCE
1394 , BOM_LABOR_RESOURCE_ID
1395 , BOM_EQUIPMENT_RESOURCE_ID
1396 , INVENTORY_ITEM_ID
1397 , SYSTEM_LINKAGE_FUNCTION
1398 , PJI_PROJECT_RECORD_FLAG
1399 , PJI_RESOURCE_RECORD_FLAG
1400 , CODE_COMBINATION_ID
1401 , PRVDR_GL_DATE
1402 , RECVR_GL_DATE
1403 , GL_PERIOD_NAME
1404 , PRVDR_PA_DATE
1405 , RECVR_PA_DATE
1406 , PA_PERIOD_NAME
1407 , TXN_CURRENCY_CODE
1408 , TXN_REVENUE
1409 , TXN_RAW_COST
1410 , TXN_BILL_RAW_COST
1411 , TXN_BURDENED_COST
1412 , TXN_BILL_BURDENED_COST
1413 , TXN_UBR
1414 , TXN_UER
1415 , PRJ_REVENUE
1416 , PRJ_RAW_COST
1417 , PRJ_BILL_RAW_COST
1418 , PRJ_BURDENED_COST
1419 , PRJ_BILL_BURDENED_COST
1420 , PRJ_UBR
1421 , PRJ_UER
1422 , POU_REVENUE
1423 , POU_RAW_COST
1424 , POU_BILL_RAW_COST
1425 , POU_BURDENED_COST
1426 , POU_BILL_BURDENED_COST
1427 , POU_UBR
1428 , POU_UER
1429 , EOU_RAW_COST
1430 , EOU_BILL_RAW_COST
1431 , EOU_BURDENED_COST
1432 , EOU_BILL_BURDENED_COST
1433 , EOU_UBR
1434 , EOU_UER
1435 , QUANTITY
1436 , BILL_QUANTITY
1437 )
1438 Select /*+ PARALLEL(drev) FULL(drev)
1439 PARALLEL(erdl) FULL(erdl) */
1440 p_worker_id AS WORKER_ID
1441 , 1 AS SLICE_ID
1442 , erdl.Project_ID AS PROJECT_ID
1443 , nvl(ev.task_id, -1) AS TASK_ID -- Bug 6065483
1444 , -1 AS PERSON_ID
1445 , drev.Project_Org_ID AS PROJECT_ORG_ID
1446 , drev.Project_Organization_ID AS PROJECT_ORGANIZATION_ID
1447 , drev.Project_Type_Class AS PROJECT_TYPE_CLASS
1448 , cust.Customer_ID AS CUSTOMER_ID
1449 , -1 AS EXPENDITURE_ORG_ID
1450 , ev.Organization_ID AS EXPENDITURE_ORGANIZATION_ID
1451 , -1 AS JOB_ID
1452 , -1 AS VENDOR_ID
1453 , -1 AS WORK_TYPE_ID
1454 , evt.event_type_id AS EXP_EVT_TYPE_ID
1455 , PJI_FM_SUM_MAIN.g_null AS EXPENDITURE_TYPE
1456 , evt.event_type AS EVENT_TYPE
1457 , evt.event_type_classification AS EVENT_TYPE_CLASSIFICATION
1458 , PJI_FM_SUM_MAIN.g_null AS EXPENDITURE_CATEGORY
1459 , evt.revenue_category_code AS REVENUE_CATEGORY
1460 , 'PJI$NULL' AS NON_LABOR_RESOURCE
1461 , -1 AS BOM_LABOR_RESOURCE_ID
1462 , -1 AS BOM_EQUIPMENT_RESOURCE_ID
1463 , -1 AS INVENTORY_ITEM_ID
1464 , PJI_FM_SUM_MAIN.g_null AS SYSTEM_LINKAGE_FUNCTION
1465 , 'Y' AS PJI_PROJECT_RECORD_FLAG
1466 , 'N' AS PJI_RESOURCE_RECORD_FLAG
1467 , -1 AS CODE_COMBINATION_ID
1468 , Greatest(drev.GL_Date,l_min_date) AS PRVDR_GL_DATE
1469 , Greatest(drev.GL_Date,l_min_date) AS RECVR_GL_DATE
1470 , drev.GL_Period_Name AS GL_PERIOD_NAME
1471 , Greatest(drev.PA_Date,l_min_date) AS PRVDR_PA_DATE
1472 , Greatest(drev.PA_Date,l_min_date) AS RECVR_PA_DATE
1473 , drev.PA_Period_Name AS PA_PERIOD_NAME
1474 , erdl.Funding_Currency_Code AS TXN_CURRENCY_CODE
1475 , sum(erdl.Funding_Revenue_Amount) AS TXN_REVENUE
1476 , to_number(null) AS TXN_RAW_COST
1477 , to_number(null) AS TXN_BILL_RAW_COST
1478 , to_number(null) AS TXN_BURDENED_COST
1479 , to_number(null) AS TXN_BILL_BURDENED_COST
1480 , to_number(null) AS TXN_UBR
1481 , to_number(null) AS TXN_UER
1482 , sum(erdl.Project_Revenue_Amount) AS PRJ_REVENUE
1483 , to_number(null) AS PRJ_RAW_COST
1484 , to_number(null) AS PRJ_BILL_RAW_COST
1485 , to_number(null) AS PRJ_BURDENED_COST
1486 , to_number(null) AS PRJ_BILL_BURDENED_COST
1487 , to_number(null) AS PRJ_UBR
1488 , to_number(null) AS PRJ_UER
1489 , sum(erdl.Projfunc_Revenue_Amount) AS POU_REVENUE
1490 , to_number(null) AS POU_RAW_COST
1491 , to_number(null) AS POU_BILL_RAW_COST
1492 , to_number(null) AS POU_BURDENED_COST
1493 , to_number(null) AS POU_BILL_BURDENED_COST
1494 , to_number(null) AS POU_UBR
1495 , to_number(null) AS POU_UER
1496 , to_number(null) AS EOU_RAW_COST
1497 , to_number(null) AS EOU_BILL_RAW_COST
1498 , to_number(null) AS EOU_BURDENED_COST
1499 , to_number(null) AS EOU_BILL_BURDENED_COST
1500 , to_number(null) AS EOU_UBR
1501 , to_number(null) AS EOU_UER
1502 , to_number(null) AS QUANTITY
1503 , to_number(null) AS BILL_QUANTITY
1504 From pa_agreements_all ag
1505 , pa_project_customers cust
1506 , pa_events ev
1507 , pa_event_types evt
1508 , PJI_FM_EXTR_DREVN drev
1509 , pa_cust_event_rdl_all erdl
1510 Where 1 = 1
1511 And drev.worker_id = p_worker_id
1512 And drev.project_id = erdl.project_id
1513 And ev.project_id = erdl.project_id
1514 And drev.draft_revenue_num = erdl.draft_revenue_num
1515 And NVL(erdl.task_id,-1) = NVL(ev.task_id,-1)
1516 And ev.event_num = erdl.event_num
1517 And ev.event_type = evt.event_type
1518 And drev.agreement_id = ag.agreement_id
1519 And drev.project_id = cust.project_id
1520 And ag.customer_id = cust.customer_id
1521 -- And NVL(cust.bill_another_project_flag,'N') <> 'Y' ---- ER 6519955
1522 And drev.gl_date is not null
1523 And drev.pa_date is not null
1524 Group By
1525 erdl.Project_ID
1526 , nvl(ev.task_id, -1) -- Bug 6065483
1527 , drev.Project_Org_ID
1528 , drev.Project_Organization_ID
1529 , drev.Project_Type_Class
1530 , cust.Customer_ID
1531 , ev.Organization_ID
1532 , evt.event_type_id
1533 , evt.event_type
1534 , evt.event_type_classification
1535 , evt.revenue_category_code
1536 , drev.GL_Date
1537 , drev.PA_Date
1538 , drev.GL_Period_Name
1539 , drev.PA_Period_Name
1540 , erdl.Funding_Currency_Code
1541 ;
1542
1543 end if; -- EXTRACTION_TYPE = 'FULL'
1544
1545 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_FULL(p_worker_id);');
1546
1547 commit;
1548
1549 end EXTRACT_BATCH_ERDL_FULL;
1550
1551
1552 -- -----------------------------------------------------
1553 -- procedure EXTRACT_BATCH_CDL_ROWIDS
1554 -- -----------------------------------------------------
1555 procedure EXTRACT_BATCH_CDL_ROWIDS(p_worker_id in number) is
1556
1557 l_process varchar2(30);
1558 l_schema varchar2(30);
1559 l_from_project_id number;
1560 l_to_project_id number;
1561
1562 begin
1563
1564 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1565
1566 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_ROWIDS(p_worker_id);')) then
1567 return;
1568 end if;
1569
1570 if (PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') = 'FULL') then
1571
1572 insert /*+ append */ into PJI_FM_REXT_CDL
1573 (
1574 WORKER_ID
1575 , CDL_ROWID
1576 , START_DATE
1577 , END_DATE
1578 , PROJECT_ORG_ID
1579 , PROJECT_ORGANIZATION_ID
1580 , PROJECT_TYPE_CLASS
1581 , PJI_SUMMARIZED_FLAG
1582 , BATCH_ID
1583 )
1584 SELECT /*+ index_ffs(cdl, PA_COST_DISTRIBUTION_LINES_N15)
1585 parallel_index(cdl, PA_COST_DISTRIBUTION_LINES_N15) */
1586 p_worker_id
1587 , cdl.ROWID
1588 , null
1589 , null
1590 , null
1591 , null
1592 , null
1593 , cdl.PJI_SUMMARIZED_FLAG
1594 , ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold)
1595 FROM
1596 PA_COST_DISTRIBUTION_LINES_ALL cdl
1597 WHERE
1598 cdl.LINE_TYPE in ('R', 'I') and
1599 cdl.PJI_SUMMARIZED_FLAG = 'N';
1600
1601 else
1602
1603 INSERT /*+ APPEND */ INTO PJI_FM_REXT_CDL
1604 (
1605 WORKER_ID
1606 , CDL_ROWID
1607 , START_DATE
1608 , END_DATE
1609 , PROJECT_ORG_ID
1610 , PROJECT_ORGANIZATION_ID
1611 , PROJECT_TYPE_CLASS
1612 , PJI_SUMMARIZED_FLAG
1613 , BATCH_ID
1614 )
1615 SELECT
1616 p_worker_id
1617 , row_id
1618 , start_date
1619 , end_date
1620 , project_org_id
1621 , project_organization_id
1622 , project_type_class
1623 , pji_summarized_flag
1624 , ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold)
1625 FROM
1626 (
1627 SELECT /*+ ORDERED
1628 USE_NL(cdl)
1629 INDEX(cdl, PA_COST_DISTRIBUTION_LINES_N15)
1630 */
1631 cdl.rowid row_id
1632 , bat.start_date
1633 , bat.end_date
1634 , bat.project_org_id
1635 , bat.project_organization_id
1636 , bat.project_type_class
1637 , cdl.pji_summarized_flag
1638 FROM
1639 pji_fm_proj_batch_map bat
1640 , pa_cost_distribution_lines_all cdl
1641 WHERE
1642 bat.worker_id = p_worker_id
1643 and cdl.project_id = bat.project_id
1644 and cdl.line_type in ('R','I')
1645 and bat.extraction_type = 'I'
1646 and cdl.pji_summarized_flag = 'N'
1647 union all
1648 SELECT /*+ ORDERED
1649 USE_NL(cdl)
1650 INDEX(cdl, PA_COST_DISTRIBUTION_LINES_N15)
1651 */
1652 cdl.rowid row_id
1653 , bat.start_date
1654 , bat.end_date
1655 , bat.project_org_id
1656 , bat.project_organization_id
1657 , bat.project_type_class
1658 , cdl.pji_summarized_flag
1659 FROM
1660 pji_fm_proj_batch_map bat
1661 , pa_cost_distribution_lines_all cdl
1662 WHERE
1663 bat.worker_id = p_worker_id
1664 and cdl.project_id = bat.project_id
1665 and cdl.line_type in ('R','I')
1666 and bat.extraction_type <> 'I'
1667 );
1668
1669 end if;
1670
1671 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_ROWIDS(p_worker_id);');
1672
1673 commit;
1674
1675 end EXTRACT_BATCH_CDL_ROWIDS;
1676
1677
1678 -- -----------------------------------------------------
1679 -- procedure EXTRACT_BATCH_CRDL_ROWIDS
1680 -- -----------------------------------------------------
1681 procedure EXTRACT_BATCH_CRDL_ROWIDS(p_worker_id in number) is
1682
1683 l_process varchar2(30);
1684 l_schema varchar2(30);
1685
1686 begin
1687
1688 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1689
1690 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CRDL_ROWIDS(p_worker_id);')) then
1691 return;
1692 end if;
1693
1694 if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') <> 'FULL') then
1695
1696 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
1697 'CURRENT_BATCH') = 1) then
1698 -- implicit commit
1699 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
1700 tabname => 'PJI_FM_EXTR_DREVN',
1701 percent => 10,
1702 degree => PJI_UTILS.
1703 GET_DEGREE_OF_PARALLELISM);
1704 end if;
1705
1706 INSERT /*+ APPEND */ INTO PJI_FM_REXT_CRDL
1707 (
1708 WORKER_ID
1709 , CRDL_ROWID
1710 , PA_DATE
1711 , PA_PERIOD_NAME
1712 , GL_DATE
1713 , GL_PERIOD_NAME
1714 , PROJECT_ORG_ID
1715 , PROJECT_ORGANIZATION_ID
1716 , PROJECT_TYPE_CLASS
1717 , LINE_SOURCE_TYPE
1718 , BILL_ANOTHER_PROJECT_FLAG
1719 , CUSTOMER_ID
1720 )
1721 SELECT /*+ ORDERED
1722 USE_NL(ag)
1723 USE_NL(cust)
1724 USE_NL(crdl)
1725 INDEX(crdl, PA_CUST_REV_DIST_LINES_N1)
1726 */
1727 p_worker_id
1728 , crdl.rowid
1729 , drev.pa_date
1730 , drev.pa_period_name
1731 , drev.gl_date
1732 , drev.gl_period_name
1733 , drev.project_org_id
1734 , drev.project_organization_id
1735 , drev.project_type_class
1736 , drev.line_source_type
1737 , cust.bill_another_project_flag
1738 , cust.customer_id
1739 FROM
1740 PJI_FM_EXTR_DREVN drev
1741 , pa_agreements_all ag
1742 , pa_project_customers cust
1743 , pa_cust_rev_dist_lines_all crdl
1744 WHERE
1745 drev.worker_id = p_worker_id
1746 and drev.project_id = crdl.project_id
1747 and drev.draft_revenue_num = crdl.draft_revenue_num
1748 and drev.gl_date is not null
1749 and drev.pa_date is not null
1750 and drev.agreement_id = ag.agreement_id
1751 and drev.project_id = cust.project_id
1752 and ag.customer_id = cust.customer_id;
1753 -- and NVL(cust.bill_another_project_flag,'N') <> 'Y'; -- ER 6519955
1754
1755 end if; -- EXTRACTION_TYPE <> 'FULL'
1756
1757 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CRDL_ROWIDS(p_worker_id);');
1758
1759 commit;
1760
1761 end EXTRACT_BATCH_CRDL_ROWIDS;
1762
1763
1764 -- -----------------------------------------------------
1765 -- procedure EXTRACT_BATCH_ERDL_ROWIDS
1766 -- -----------------------------------------------------
1767 procedure EXTRACT_BATCH_ERDL_ROWIDS(p_worker_id in number) is
1768
1769 l_process varchar2(30);
1770 l_schema varchar2(30);
1771
1772 begin
1773
1774 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1775
1776 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_ROWIDS(p_worker_id);')) then
1777 return;
1778 end if;
1779
1780 if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') <> 'FULL') then
1781
1782 INSERT /*+ APPEND */ INTO PJI_FM_REXT_ERDL
1783 (
1784 WORKER_ID
1785 , ERDL_ROWID
1786 , PROJECT_ORG_ID
1787 , PROJECT_ORGANIZATION_ID
1788 , PROJECT_ID
1789 , PROJECT_TYPE_CLASS
1790 , EXPENDITURE_ORGANIZATION_ID
1791 , TASK_ID
1792 , EXP_EVT_TYPE_ID
1793 , EVENT_TYPE
1794 , EVENT_NUM
1795 , REVENUE_CATEGORY
1796 , EVENT_TYPE_CLASSIFICATION
1797 , LINE_SOURCE_TYPE
1798 , BILL_ANOTHER_PROJECT_FLAG
1799 , CUSTOMER_ID
1800 , TXN_DATE
1801 , PA_DATE
1802 , PA_PERIOD_NAME
1803 , GL_DATE
1804 , GL_PERIOD_NAME
1805 )
1806 SELECT /*+ ORDERED
1807 USE_NL(ag)
1808 USE_NL(cust)
1809 USE_NL(erdl)
1810 INDEX(erdl, PA_CUST_EVENT_REV_DIST_LINE_N1)
1811 */
1812 p_worker_id worker_id
1813 , erdl.rowid row_id
1814 , nvl(drev.project_org_id, -1) project_org_id
1815 , drev.project_organization_id project_organization_id
1816 , drev.project_id project_id
1817 , drev.project_type_class project_type_class
1818 , ev.organization_id expenditure_organization_id
1819 , NVL(ev.task_id,-1) task_id
1820 , evt.event_type_id exp_evt_type_id
1821 , evt.event_type event_type
1822 , ev.event_num event_num
1823 , evt.revenue_category_code revenue_category
1824 , evt.event_type_classification event_type_classification
1825 , drev.line_source_type line_source_type
1826 , cust.bill_another_project_flag bill_another_project_flag
1827 , ag.customer_id customer_id
1828 , ev.completion_date txn_date
1829 , drev.pa_date pa_date
1830 , drev.pa_period_name pa_period_name
1831 , drev.gl_date gl_date
1832 , drev.gl_period_name gl_period_name
1833 FROM
1834 PJI_FM_EXTR_DREVN drev
1835 , pa_agreements_all ag
1836 , pa_project_customers cust
1837 , pa_cust_event_rdl_all erdl /* Changed the order for bug 8668173 */
1838 , pa_events ev
1839 , pa_event_types evt
1840 WHERE
1841 drev.worker_id = p_worker_id
1842 /* and drev.project_id = ev.project_id Commented for bug 8668173 */
1843 and ev.project_id = erdl.project_id
1844 and drev.project_id = erdl.project_id
1845 and drev.draft_revenue_num = erdl.draft_revenue_num
1846 and NVL(erdl.task_id,-1) = NVL(ev.task_id,-1) -- uncommented for bug 7354140
1847 and ev.event_num = erdl.event_num -- uncommented for bug 7354140
1848 and ev.event_type = evt.event_type
1849 and drev.agreement_id = ag.agreement_id
1850 and drev.project_id = cust.project_id
1851 and ag.customer_id = cust.customer_id
1852 -- and NVL(cust.bill_another_project_flag,'N') <> 'Y' -- ER 6519955
1853 and drev.gl_date is not null
1854 and drev.pa_date is not null
1855 ;
1856
1857 end if; -- EXTRACTION_TYPE <> 'FULL'
1858
1859 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL_ROWIDS(p_worker_id);');
1860
1861 commit;
1862
1863 end EXTRACT_BATCH_ERDL_ROWIDS;
1864
1865
1866 -- -----------------------------------------------------
1867 -- procedure EXTRACT_BATCH_CDL_AND_CRDL
1868 -- -----------------------------------------------------
1869 procedure EXTRACT_BATCH_CDL_AND_CRDL (p_worker_id in number) is
1870
1871 l_process varchar2(30);
1872 l_min_date date;
1873 l_schema varchar2(30);
1874 l_row_count number;
1875
1876 begin
1877
1878 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1879
1880 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_AND_CRDL(p_worker_id);')) then
1881 return;
1882 end if;
1883
1884 l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
1885 PJI_FM_SUM_MAIN.g_date_mask);
1886
1887 if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') <> 'FULL') then
1888
1889 -- This cleanup is intentionally before the implicit commit so as not
1890 -- to interfere with the CDL extraction.
1891 if (nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1892 (PJI_FM_SUM_MAIN.g_process, 'CONFIG_PROJ_PERF_FLAG'),
1893 'N') = 'N' and
1894 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1895 (PJI_FM_SUM_MAIN.g_process, 'CONFIG_COST_FLAG'),
1896 'N') = 'N' and
1897 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1898 (PJI_FM_SUM_MAIN.g_process, 'CONFIG_PROFIT_FLAG'),
1899 'N') = 'N') then
1900 delete /*+ index (log, PA_PJI_PROJ_EVENTS_LOG_N1) */
1901 from PA_PJI_PROJ_EVENTS_LOG log
1902 where EVENT_TYPE = 'DRAFT_REVENUES';
1903 end if;
1904
1905 -- delete Non-Util --> Util resources that are getting extracted anyway
1906 delete
1907 from PJI_FM_REXT_CDL
1908 where WORKER_ID = p_worker_id and
1909 PROJECT_ORG_ID = -1 and
1910 PROJECT_ORGANIZATION_ID = -1 and
1911 CDL_ROWID in (select CDL_ROWID
1912 from PJI_FM_REXT_CDL
1913 where WORKER_ID = p_worker_id and
1914 PROJECT_ORGANIZATION_ID <> -1);
1915
1916 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
1917 'CURRENT_BATCH') = 1) then
1918 -- implicit commit
1919 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
1920 tabname => 'PJI_FM_REXT_CDL',
1921 percent => 10,
1922 degree => PJI_UTILS.
1923 GET_DEGREE_OF_PARALLELISM);
1924 -- implicit commit
1925 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
1926 tabname => 'PJI_FM_REXT_CRDL',
1927 percent => 10,
1928 degree => PJI_UTILS.
1929 GET_DEGREE_OF_PARALLELISM);
1930 end if;
1931
1932 INSERT /*+ APPEND PARALLEL(fin1_i) */ INTO PJI_FM_AGGR_FIN1 fin1_i
1933 ( WORKER_ID
1934 , SLICE_ID
1935 , PROJECT_ID
1936 , TASK_ID
1937 , PERSON_ID
1938 , PROJECT_ORG_ID
1939 , PROJECT_ORGANIZATION_ID
1940 , PROJECT_TYPE_CLASS
1941 , CUSTOMER_ID
1942 , EXPENDITURE_ORG_ID
1943 , EXPENDITURE_ORGANIZATION_ID
1944 , JOB_ID
1945 , VENDOR_ID
1946 , WORK_TYPE_ID
1947 , EXP_EVT_TYPE_ID
1948 , EXPENDITURE_TYPE
1949 , EVENT_TYPE
1950 , EVENT_TYPE_CLASSIFICATION
1951 , EXPENDITURE_CATEGORY
1952 , REVENUE_CATEGORY
1953 , NON_LABOR_RESOURCE
1954 , BOM_LABOR_RESOURCE_ID
1955 , BOM_EQUIPMENT_RESOURCE_ID
1956 , INVENTORY_ITEM_ID
1957 , PO_LINE_ID
1958 , ASSIGNMENT_ID
1959 , SYSTEM_LINKAGE_FUNCTION
1960 , PJI_PROJECT_RECORD_FLAG
1961 , PJI_RESOURCE_RECORD_FLAG
1962 , CODE_COMBINATION_ID
1963 , PRVDR_GL_DATE
1964 , RECVR_GL_DATE
1965 , GL_PERIOD_NAME
1966 , PRVDR_PA_DATE
1967 , RECVR_PA_DATE
1968 , PA_PERIOD_NAME
1969 , EXPENDITURE_ITEM_DATE
1970 , TXN_CURRENCY_CODE
1971 , TXN_REVENUE
1972 , TXN_RAW_COST
1973 , TXN_BILL_RAW_COST
1974 , TXN_BURDENED_COST
1975 , TXN_BILL_BURDENED_COST
1976 , TXN_UBR
1977 , TXN_UER
1978 , PRJ_REVENUE
1979 , PRJ_RAW_COST
1980 , PRJ_BILL_RAW_COST
1981 , PRJ_BURDENED_COST
1982 , PRJ_BILL_BURDENED_COST
1983 , PRJ_UBR
1984 , PRJ_UER
1985 , POU_REVENUE
1986 , POU_RAW_COST
1987 , POU_BILL_RAW_COST
1988 , POU_BURDENED_COST
1989 , POU_BILL_BURDENED_COST
1990 , POU_UBR
1991 , POU_UER
1992 , EOU_RAW_COST
1993 , EOU_BILL_RAW_COST
1994 , EOU_BURDENED_COST
1995 , EOU_BILL_BURDENED_COST
1996 , EOU_UBR
1997 , EOU_UER
1998 , QUANTITY
1999 , BILL_QUANTITY
2000 ,CBS_ELEMENT_ID /* Added for CBS Changes */
2001 )
2002 SELECT
2003 grp.WORKER_ID
2004 , grp.SLICE_ID
2005 , grp.PROJECT_ID
2006 , grp.TASK_ID
2007 , grp.PERSON_ID
2008 , grp.PROJECT_ORG_ID
2009 , grp.PROJECT_ORGANIZATION_ID
2010 , grp.PROJECT_TYPE_CLASS
2011 , grp.CUSTOMER_ID
2012 , grp.EXPENDITURE_ORG_ID
2013 , grp.EXPENDITURE_ORGANIZATION_ID
2014 , grp.JOB_ID
2015 , grp.VENDOR_ID
2016 , grp.WORK_TYPE_ID
2017 , grp.EXP_EVT_TYPE_ID
2018 , grp.EXPENDITURE_TYPE
2019 , grp.EVENT_TYPE
2020 , grp.EVENT_TYPE_CLASSIFICATION
2021 , grp.EXPENDITURE_CATEGORY
2022 , grp.REVENUE_CATEGORY
2023 , grp.NON_LABOR_RESOURCE
2024 , grp.BOM_LABOR_RESOURCE_ID
2025 , grp.BOM_EQUIPMENT_RESOURCE_ID
2026 , grp.INVENTORY_ITEM_ID
2027 , grp.PO_LINE_ID
2028 , grp.ASSIGNMENT_ID
2029 , grp.SYSTEM_LINKAGE_FUNCTION
2030 , grp.PJI_PROJECT_RECORD_FLAG
2031 , grp.PJI_RESOURCE_RECORD_FLAG
2032 , grp.CODE_COMBINATION_ID
2033 , grp.PRVDR_GL_DATE
2034 , grp.RECVR_GL_DATE
2035 , grp.GL_PERIOD_NAME
2036 , grp.PRVDR_PA_DATE
2037 , grp.RECVR_PA_DATE
2038 , grp.PA_PERIOD_NAME
2039 , grp.EXPENDITURE_ITEM_DATE
2040 , grp.TXN_CURRENCY_CODE
2041 , sum(grp.TXN_REVENUE)
2042 , sum(grp.TXN_RAW_COST)
2043 , sum(grp.TXN_BILL_RAW_COST)
2044 , sum(grp.TXN_BURDENED_COST)
2045 , sum(grp.TXN_BILL_BURDENED_COST)
2046 , sum(grp.TXN_UBR)
2047 , sum(grp.TXN_UER)
2048 , sum(grp.PRJ_REVENUE)
2049 , sum(grp.PRJ_RAW_COST)
2050 , sum(grp.PRJ_BILL_RAW_COST)
2051 , sum(grp.PRJ_BURDENED_COST)
2052 , sum(grp.PRJ_BILL_BURDENED_COST)
2053 , sum(grp.PRJ_UBR)
2054 , sum(grp.PRJ_UER)
2055 , sum(grp.POU_REVENUE)
2056 , sum(grp.POU_RAW_COST)
2057 , sum(grp.POU_BILL_RAW_COST)
2058 , sum(grp.POU_BURDENED_COST)
2059 , sum(grp.POU_BILL_BURDENED_COST)
2060 , sum(grp.POU_UBR)
2061 , sum(grp.POU_UER)
2062 , sum(grp.EOU_RAW_COST)
2063 , sum(grp.EOU_BILL_RAW_COST)
2064 , sum(grp.EOU_BURDENED_COST)
2065 , sum(grp.EOU_BILL_BURDENED_COST)
2066 , sum(grp.EOU_UBR)
2067 , sum(grp.EOU_UER)
2068 , sum(grp.QUANTITY)
2069 , sum(grp.BILL_QUANTITY)
2070 , grp.CBS_ELEMENT_ID /* Added for CBS Changes */
2071 FROM (
2072 SELECT /*+ ordered */
2073 p_worker_id AS WORKER_ID
2074 , decode(scope.PROJECT_ORG_ID, -1, -- Ensure that JOB_ID
2075 decode(scope.PROJECT_ORGANIZATION_ID, -- Util --> Non-Util
2076 -1, -1, 1), -- reversals do not get
2077 1) SLICE_ID -- into PSI tables.
2078 , cdl.project_id AS PROJECT_ID
2079 , cdl.task_id AS TASK_ID
2080 , decode(exp.incurred_by_person_id,
2081 null, -1, 0, -1,
2082 exp.incurred_by_person_id) AS PERSON_ID
2083 , nvl(scope.project_org_id, -1) AS PROJECT_ORG_ID
2084 , scope.project_organization_id AS PROJECT_ORGANIZATION_ID
2085 , scope.project_type_class AS PROJECT_TYPE_CLASS
2086 , -1 AS CUSTOMER_ID
2087 , cdl.org_id AS EXPENDITURE_ORG_ID
2088 , NVL(ei.override_to_organization_id, exp.incurred_by_organization_id)
2089 AS EXPENDITURE_ORGANIZATION_ID
2090 , nvl(ei.job_id, -1) AS JOB_ID
2091 , nvl(exp.vendor_id, -1) AS VENDOR_ID
2092 , nvl(cdl.work_type_id, -1) AS WORK_TYPE_ID
2093 , et.expenditure_type_id AS EXP_EVT_TYPE_ID
2094 , et.expenditure_type AS EXPENDITURE_TYPE
2095 , PJI_FM_SUM_MAIN.g_null AS EVENT_TYPE
2096 , PJI_FM_SUM_MAIN.g_null AS EVENT_TYPE_CLASSIFICATION
2097 , et.expenditure_category AS EXPENDITURE_CATEGORY
2098 , et.revenue_category_code AS REVENUE_CATEGORY
2099 , ei.Non_Labor_Resource AS NON_LABOR_RESOURCE
2100 , ei.Wip_Resource_ID AS BOM_LABOR_RESOURCE_ID
2101 , ei.Wip_Resource_ID AS BOM_EQUIPMENT_RESOURCE_ID
2102 , ei.Inventory_Item_ID AS INVENTORY_ITEM_ID
2103 , ei.PO_Line_ID AS PO_LINE_ID
2104 , decode(ei.Assignment_ID,
2105 null, -1, 0, -1,
2106 ei.Assignment_ID) AS ASSIGNMENT_ID
2107 , NVL(ei.src_system_linkage_function,
2108 ei.system_linkage_function)
2109 AS SYSTEM_LINKAGE_FUNCTION
2110 , decode(scope.PROJECT_ORG_ID,
2111 -1, decode(scope.PROJECT_ORGANIZATION_ID,
2112 -1, 'N',
2113 'Y'),
2114 'Y') AS PJI_PROJECT_RECORD_FLAG
2115 , decode(scope.PROJECT_ORG_ID,
2116 -1, decode(scope.PROJECT_ORGANIZATION_ID,
2117 -1, 'Y',
2118 decode(exp.Incurred_BY_Person_ID,
2119 null, 'N',
2120 0, 'N',
2121 'Y')),
2122 decode(exp.Incurred_BY_Person_ID,
2123 null, 'N',
2124 0, 'N',
2125 'Y')) AS PJI_RESOURCE_RECORD_FLAG
2126 , -1 AS CODE_COMBINATION_ID
2127 , Greatest(cdl.gl_date,l_min_date) AS PRVDR_GL_DATE
2128 , Greatest(nvl(cdl.recvr_gl_date, cdl.gl_date),l_min_date) AS RECVR_GL_DATE
2129 , cdl.Recvr_GL_Period_Name AS GL_PERIOD_NAME
2130 , Greatest(cdl.pa_date,l_min_date) AS PRVDR_PA_DATE
2131 , Greatest(nvl(cdl.recvr_pa_date, cdl.pa_date),l_min_date) AS RECVR_PA_DATE
2132 , cdl.Recvr_PA_Period_Name AS PA_PERIOD_NAME
2133 , Greatest(ei.Expenditure_Item_Date,
2134 l_min_date) AS EXPENDITURE_ITEM_DATE
2135 , cdl.Denom_Currency_Code AS TXN_CURRENCY_CODE
2136 , to_number(null) AS TXN_REVENUE
2137 , NVL(cdl.Denom_Raw_Cost,0) AS TXN_RAW_COST
2138 , decode(cdl.billable_flag
2139 , 'Y', nvl(cdl.Denom_Raw_Cost, 0)
2140 , 0) AS TXN_BILL_RAW_COST
2141 , nvl(cdl.Denom_Burdened_Cost, 0) AS TXN_BURDENED_COST
2142 , decode(cdl.Billable_Flag
2143 , 'Y', nvl(cdl.Denom_Burdened_Cost, 0)
2144 , 0) AS TXN_BILL_BURDENED_COST
2145 , to_number(null) AS TXN_UBR
2146 , to_number(null) AS TXN_UER
2147 , to_number(null) AS PRJ_REVENUE
2148 , NVL(cdl.project_raw_cost,0) AS PRJ_RAW_COST
2149 , decode(cdl.billable_flag
2150 , 'Y', nvl(cdl.Project_Raw_Cost, 0)
2151 , 0) AS PRJ_BILL_RAW_COST
2152 , nvl(cdl.Project_Burdened_Cost, 0) AS PRJ_BURDENED_COST
2153 , decode(cdl.Billable_Flag
2154 , 'Y', nvl(cdl.Project_Burdened_Cost, 0)
2155 , 0) AS PRJ_BILL_BURDENED_COST
2156 , to_number(null) AS PRJ_UBR
2157 , to_number(null) AS PRJ_UER
2158 , to_number(null) AS POU_REVENUE
2159 , cdl.AMOUNT AS POU_RAW_COST
2160 , decode(cdl.bILLABLE_fLAG
2161 , 'Y', nvl(cdl.Amount, 0)
2162 , 0) AS POU_BILL_RAW_COST
2163 , nvl(cdl.Burdened_Cost, 0) AS POU_BURDENED_COST
2164 , decode(cdl.Billable_Flag
2165 , 'Y', nvl(cdl.Burdened_Cost, 0)
2166 , 0) AS POU_BILL_BURDENED_COST
2167 , to_number(null) AS POU_UBR
2168 , to_number(null) AS POU_UER
2169 , nvl(cdl.Acct_Raw_Cost, 0) AS EOU_RAW_COST
2170 , decode(cdl.Billable_Flag
2171 , 'Y', nvl(cdl.Acct_Raw_Cost,0)
2172 , 0) AS EOU_BILL_RAW_COST
2173 , nvl(cdl.Acct_Burdened_Cost, 0) AS EOU_BURDENED_COST
2174 , decode(cdl.Billable_Flag
2175 , 'Y', nvl(cdl.Acct_Burdened_Cost, 0)
2176 , 0) AS EOU_BILL_BURDENED_COST
2177 , to_number(null) AS EOU_UBR
2178 , to_number(null) AS EOU_UER
2179 , decode(cdl.line_type,'R',cdl.Quantity,0)
2180 AS QUANTITY /* Modified for bug 9777532 */
2181 , decode(cdl.Billable_Flag
2182 , 'Y', decode(cdl.line_type,'R',cdl.Quantity,0)
2183 , 0) AS BILL_QUANTITY /* Modified for bug 9777532 */
2184 ,cdl.CBS_ELEMENT_ID AS CBS_ELEMENT_ID /* Added for CBS Changes */
2185 FROM
2186 PJI_FM_REXT_CDL scope
2187 , pa_cost_distribution_lines_all cdl
2188 , pa_expenditure_items_all ei
2189 , pa_expenditures_all exp
2190 , pa_expenditure_types et
2191 WHERE
2192 scope.worker_id = p_worker_id
2193 and scope.cdl_rowid = cdl.rowid
2194 and cdl.expenditure_item_id = ei.expenditure_item_id
2195 and ei.expenditure_type = et.expenditure_type
2196 and exp.expenditure_id = ei.expenditure_id
2197 and cdl.gl_date is not null
2198 and cdl.pa_date is not null
2199 -- and NVL(ei.transaction_source,'dummy') <> 'INTERPROJECT_AP_INVOICES' -- ER 6519955
2200 UNION ALL
2201 SELECT /*+ ordered */
2202 p_worker_id AS WORKER_ID
2203 , 1 AS SLICE_ID
2204 , crdl.Project_ID AS PROJECT_ID
2205 , ei.Task_ID AS TASK_ID
2206 , decode(exp.Incurred_By_Person_ID,
2207 null, -1, 0, -1,
2208 exp.Incurred_By_Person_ID) AS PERSON_ID
2209 , nvl(scope.Project_Org_ID, -1) AS PROJECT_ORG_ID
2210 , scope.Project_Organization_ID AS PROJECT_ORGANIZATION_ID
2211 , scope.Project_Type_Class AS PROJECT_TYPE_CLASS
2212 , scope.Customer_ID AS CUSTOMER_ID
2213 , ei.Org_ID AS EXPENDITURE_ORG_ID
2214 , nvl(ei.Override_To_Organization_ID, exp.Incurred_By_Organization_ID)
2215 AS EXPENDITURE_ORGANIZATION_ID
2216 , nvl(ei.Job_ID, -1) AS JOB_ID
2217 , nvl(exp.vendor_id,-1) AS VENDOR_ID
2218 , nvl(ei.Work_type_ID, -1) AS WORK_TYPE_ID
2219 , et.Expenditure_Type_ID AS EXP_EVT_TYPE_ID
2220 , et.Expenditure_Type AS EXPENDITURE_TYPE
2221 , PJI_FM_SUM_MAIN.g_null AS EVENT_TYPE
2222 , PJI_FM_SUM_MAIN.g_null AS EVENT_TYPE_CLASSIFICATION
2223 , et.Expenditure_Category AS EXPENDITURE_CATEGORY
2224 , et.Revenue_Category_Code AS REVENUE_CATEGORY
2225 , ei.Non_Labor_Resource AS NON_LABOR_RESOURCE
2226 , ei.Wip_Resource_ID AS BOM_LABOR_RESOURCE_ID
2227 , ei.Wip_Resource_ID AS BOM_EQUIPMENT_RESOURCE_ID
2228 , ei.Inventory_Item_ID AS INVENTORY_ITEM_ID
2229 , ei.PO_Line_ID AS PO_LINE_ID
2230 , decode(ei.Assignment_ID,
2231 null, -1, 0, -1,
2232 ei.Assignment_ID) AS ASSIGNMENT_ID
2233 , NVL(ei.src_system_linkage_function,
2234 ei.system_linkage_function)
2235 AS SYSTEM_LINKAGE_FUNCTION
2236 , 'Y' AS PJI_PROJECT_RECORD_FLAG
2237 , decode(exp.Incurred_By_Person_ID, null, 'N', 0, 'N', 'Y')
2238 AS PJI_RESOURCE_RECORD_FLAG
2239 , -1 AS CODE_COMBINATION_ID
2240 , Greatest(scope.GL_Date,l_min_date) AS PRVDR_GL_DATE
2241 , Greatest(scope.GL_Date,l_min_date) AS RECVR_GL_DATE
2242 , scope.GL_Period_Name AS GL_PERIOD_NAME
2243 , Greatest(scope.PA_Date,l_min_date) AS PRVDR_PA_DATE
2244 , Greatest(scope.PA_Date,l_min_date) AS RECVR_PA_DATE
2245 , scope.PA_Period_Name AS PA_PERIOD_NAME
2246 , Greatest(ei.Expenditure_Item_Date,
2247 l_min_date) AS EXPENDITURE_ITEM_DATE
2248 , crdl.Funding_Currency_Code AS TXN_CURRENCY_CODE
2249 , decode(scope.line_source_type,
2250 'R', (crdl.Funding_Revenue_Amount),
2251 'L', (-crdl.Funding_Revenue_Amount)
2252 ) AS TXN_REVENUE
2253 , to_number(null) AS TXN_RAW_COST
2254 , to_number(null) AS TXN_BILL_RAW_COST
2255 , to_number(null) AS TXN_BURDENED_COST
2256 , to_number(null) AS TXN_BILL_BURDENED_COST
2257 , to_number(null) AS TXN_UBR
2258 , to_number(null) AS TXN_UER
2259 , decode(scope.line_source_type,
2260 'R', (crdl.Project_Revenue_Amount),
2261 'L', (-crdl.Project_Revenue_Amount)
2262 ) AS PRJ_REVENUE
2263 , to_number(null) AS PRJ_RAW_COST
2264 , to_number(null) AS PRJ_BILL_RAW_COST
2265 , to_number(null) AS PRJ_BURDENED_COST
2266 , to_number(null) AS PRJ_BILL_BURDENED_COST
2267 , to_number(null) AS PRJ_UBR
2268 , to_number(null) AS PRJ_UER
2269 , decode(scope.line_source_type,
2270 'R', (crdl.Projfunc_Revenue_Amount),
2271 'L', (-crdl.Projfunc_Revenue_Amount)
2272 ) AS POU_REVENUE
2273 , to_number(null) AS POU_RAW_COST
2274 , to_number(null) AS POU_BILL_RAW_COST
2275 , to_number(null) AS POU_BURDENED_COST
2276 , to_number(null) AS POU_BILL_BURDENED_COST
2277 , to_number(null) AS POU_UBR
2278 , to_number(null) AS POU_UER
2279 , to_number(null) AS EOU_RAW_COST
2280 , to_number(null) AS EOU_BILL_RAW_COST
2281 , to_number(null) AS EOU_BURDENED_COST
2282 , to_number(null) AS EOU_BILL_BURDENED_COST
2283 , to_number(null) AS EOU_UBR
2284 , to_number(null) AS EOU_UER
2285 , to_number(null) AS QUANTITY
2286 , to_number(null) AS BILL_QUANTITY
2287 , ei.CBS_ELEMENT_ID AS CBS_ELEMENT_ID /* Added for CBS Changes */
2288 FROM
2289 PJI_FM_REXT_CRDL scope
2290 , pa_cust_rev_dist_lines_all crdl
2291 , pa_expenditure_items_all ei
2292 , pa_expenditures_all exp
2293 , pa_expenditure_types et
2294 WHERE
2295 scope.worker_id = p_worker_id
2296 and scope.crdl_rowid = crdl.rowid
2297 and crdl.function_code NOT IN ('LRL','LRB','URL','URB')
2298 and crdl.expenditure_item_id = ei.expenditure_item_id
2299 and ei.expenditure_type = et.expenditure_type
2300 and exp.expenditure_id = ei.expenditure_id
2301 ) grp
2302 GROUP BY
2303 grp.WORKER_ID
2304 , grp.SLICE_ID
2305 , grp.PROJECT_ID
2306 , grp.TASK_ID
2307 , grp.PERSON_ID
2308 , grp.PROJECT_ORG_ID
2309 , grp.PROJECT_ORGANIZATION_ID
2310 , grp.PROJECT_TYPE_CLASS
2311 , grp.CUSTOMER_ID
2312 , grp.EXPENDITURE_ORG_ID
2313 , grp.EXPENDITURE_ORGANIZATION_ID
2314 , grp.JOB_ID
2315 , grp.VENDOR_ID
2316 , grp.WORK_TYPE_ID
2317 , grp.EXP_EVT_TYPE_ID
2318 , grp.EXPENDITURE_TYPE
2319 , grp.EVENT_TYPE
2320 , grp.EVENT_TYPE_CLASSIFICATION
2321 , grp.EXPENDITURE_CATEGORY
2322 , grp.REVENUE_CATEGORY
2323 , grp.NON_LABOR_RESOURCE
2324 , grp.BOM_LABOR_RESOURCE_ID
2325 , grp.BOM_EQUIPMENT_RESOURCE_ID
2326 , grp.INVENTORY_ITEM_ID
2327 , grp.PO_LINE_ID
2328 , grp.ASSIGNMENT_ID
2329 , grp.SYSTEM_LINKAGE_FUNCTION
2330 , grp.PJI_PROJECT_RECORD_FLAG
2331 , grp.PJI_RESOURCE_RECORD_FLAG
2332 , grp.CODE_COMBINATION_ID
2333 , grp.PRVDR_GL_DATE
2334 , grp.RECVR_GL_DATE
2335 , grp.GL_PERIOD_NAME
2336 , grp.PRVDR_PA_DATE
2337 , grp.RECVR_PA_DATE
2338 , grp.PA_PERIOD_NAME
2339 , grp.EXPENDITURE_ITEM_DATE
2340 , grp.TXN_CURRENCY_CODE
2341 , grp.CBS_ELEMENT_ID /* Added for CBS Changes */
2342 ;
2343
2344 end if; -- EXTRACTION_TYPE <> 'FULL'
2345
2346 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_CDL_AND_CRDL(p_worker_id);');
2347
2348 -- truncate intermediate tables no longer required
2349 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
2350 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CRDL' , 'NORMAL',null);
2351
2352 commit;
2353
2354 end EXTRACT_BATCH_CDL_AND_CRDL;
2355
2356
2357 -- -----------------------------------------------------
2358 -- procedure MARK_EXTRACTED_CDL_ROWS_PRE
2359 -- -----------------------------------------------------
2360 procedure MARK_EXTRACTED_CDL_ROWS_PRE (p_worker_id in number) is
2361
2362 l_process varchar2(30);
2363
2364 begin
2365
2366 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2367
2368 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
2369 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_PRE(p_worker_id);')) then
2370 return;
2371 end if;
2372
2373 insert /*+ append */ into PJI_HELPER_BATCH_MAP
2374 (
2375 BATCH_ID,
2376 WORKER_ID,
2377 STATUS
2378 )
2379 select
2380 distinct
2381 BATCH_ID,
2382 null,
2383 null
2384 from
2385 PJI_FM_REXT_CDL
2386 where
2387 PJI_SUMMARIZED_FLAG is not null;
2388
2389 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2390 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_PRE(p_worker_id);');
2391
2392 commit;
2393
2394 end MARK_EXTRACTED_CDL_ROWS_PRE;
2395
2396
2397 -- -----------------------------------------------------
2398 -- procedure MARK_EXTRACTED_CDL_ROWS
2399 -- -----------------------------------------------------
2400 procedure MARK_EXTRACTED_CDL_ROWS (p_worker_id in number) is
2401
2402 l_process varchar2(30);
2403 l_leftover_batches number;
2404 l_helper_batch_id number;
2405 l_row_count number;
2406 l_parallel_processes number;
2407
2408 begin
2409
2410 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2411
2412 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS(p_worker_id);')) then
2413 return;
2414 end if;
2415
2416 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2417 (PJI_FM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
2418
2419 select count(*)
2420 into l_leftover_batches
2421 from PJI_HELPER_BATCH_MAP
2422 where WORKER_ID = p_worker_id and
2423 STATUS = 'P';
2424
2425 l_helper_batch_id := 0;
2426
2427 while l_helper_batch_id >= 0 loop
2428
2429 if (l_leftover_batches > 0) then
2430
2431 l_leftover_batches := l_leftover_batches - 1;
2432
2433 select BATCH_ID
2434 into l_helper_batch_id
2435 from PJI_HELPER_BATCH_MAP
2436 where WORKER_ID = p_worker_id and
2437 STATUS = 'P' and
2438 ROWNUM = 1;
2439
2440 else
2441
2442 update PJI_HELPER_BATCH_MAP
2443 set WORKER_ID = p_worker_id,
2444 STATUS = 'P'
2445 where WORKER_ID is null and
2446 ROWNUM = 1
2447 returning BATCH_ID
2448 into l_helper_batch_id;
2449
2450 end if;
2451
2452 if (sql%rowcount <> 0) then
2453
2454 commit;
2455
2456 update PA_COST_DISTRIBUTION_LINES_ALL cdl
2457 set cdl.PJI_SUMMARIZED_FLAG = null
2458 where cdl.ROWID in (select /*+ cardinality(cdl, 1) */
2459 cdl.CDL_ROWID
2460 from PJI_FM_REXT_CDL cdl
2461 where cdl.PJI_SUMMARIZED_FLAG = 'N' and
2462 cdl.BATCH_ID = l_helper_batch_id);
2463
2464 update PJI_HELPER_BATCH_MAP
2465 set STATUS = 'C'
2466 where WORKER_ID = p_worker_id and
2467 BATCH_ID = l_helper_batch_id;
2468
2469 commit;
2470
2471 else
2472
2473 select count(*)
2474 into l_row_count
2475 from PJI_HELPER_BATCH_MAP
2476 where nvl(STATUS, 'X') <> 'C';
2477
2478 if (l_row_count = 0) then
2479
2480 for x in 2 .. l_parallel_processes loop
2481
2482 update PJI_SYSTEM_PRC_STATUS
2483 set STEP_STATUS = 'C'
2484 where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
2485 STEP_NAME =
2486 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS(p_worker_id);' and
2487 START_DATE is null;
2488
2489 commit;
2490
2491 end loop;
2492
2493 l_helper_batch_id := -1;
2494
2495 else
2496
2497 PJI_PROCESS_UTIL.SLEEP(1); -- so the CPU is not bombarded
2498
2499 end if;
2500
2501 end if;
2502
2503 if (l_helper_batch_id >= 0) then
2504
2505 for x in 2 .. l_parallel_processes loop
2506 if (not PJI_FM_SUM_EXTR.WORKER_STATUS(x, 'OKAY')) then
2507 l_helper_batch_id := -2;
2508 end if;
2509 end loop;
2510
2511 end if;
2512
2513 end loop;
2514
2515 if (l_helper_batch_id <> -2) then
2516
2517 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2518 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS(p_worker_id);');
2519
2520 end if;
2521
2522 commit;
2523
2524 end MARK_EXTRACTED_CDL_ROWS;
2525
2526
2527 -- -----------------------------------------------------
2528 -- procedure MARK_EXTRACTED_CDL_ROWS_POST
2529 -- -----------------------------------------------------
2530 procedure MARK_EXTRACTED_CDL_ROWS_POST (p_worker_id in number) is
2531
2532 l_process varchar2(30);
2533
2534 begin
2535
2536 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2537
2538 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
2539 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_POST(p_worker_id);')) then
2540 return;
2541 end if;
2542
2543 PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
2544 'PJI_HELPER_BATCH_MAP',
2545 'NORMAL',
2546 null);
2547
2548 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
2549 'PJI_FM_EXTR.MARK_EXTRACTED_CDL_ROWS_POST(p_worker_id);');
2550
2551 if (PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') = 'FULL') then
2552 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(PJI_UTILS.GET_PJI_SCHEMA_NAME,
2553 'PJI_FM_REXT_CDL', 'NORMAL',null);
2554 end if;
2555
2556 commit;
2557
2558 end MARK_EXTRACTED_CDL_ROWS_POST;
2559
2560
2561 -- -----------------------------------------------------
2562 -- procedure EXTRACT_BATCH_ERDL
2563 -- -----------------------------------------------------
2564 procedure EXTRACT_BATCH_ERDL (p_worker_id in number) is
2565
2566 l_process varchar2(30);
2567 l_min_date date;
2568 l_schema varchar2(30);
2569
2570 begin
2571
2572 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2573
2574 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL(p_worker_id);')) then
2575 return;
2576 end if;
2577
2578 l_min_date := to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
2579 PJI_FM_SUM_MAIN.g_date_mask);
2580
2581 if ( PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE') <> 'FULL') then
2582
2583 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
2584 'CURRENT_BATCH') = 1) then
2585 -- implicit commit
2586 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
2587 tabname => 'PJI_FM_REXT_ERDL',
2588 percent => 10,
2589 degree => PJI_UTILS.
2590 GET_DEGREE_OF_PARALLELISM);
2591 end if;
2592
2593 INSERT /*+ APPEND */ INTO PJI_FM_AGGR_FIN1
2594 ( WORKER_ID
2595 , SLICE_ID
2596 , PROJECT_ID
2597 , TASK_ID
2598 , PERSON_ID
2599 , PROJECT_ORG_ID
2600 , PROJECT_ORGANIZATION_ID
2601 , PROJECT_TYPE_CLASS
2602 , CUSTOMER_ID
2603 , EXPENDITURE_ORG_ID
2604 , EXPENDITURE_ORGANIZATION_ID
2605 , JOB_ID
2606 , VENDOR_ID
2607 , WORK_TYPE_ID
2608 , EXP_EVT_TYPE_ID
2609 , EXPENDITURE_TYPE
2610 , EVENT_TYPE
2611 , EVENT_TYPE_CLASSIFICATION
2612 , EXPENDITURE_CATEGORY
2613 , REVENUE_CATEGORY
2614 , NON_LABOR_RESOURCE
2615 , BOM_LABOR_RESOURCE_ID
2616 , BOM_EQUIPMENT_RESOURCE_ID
2617 , INVENTORY_ITEM_ID
2618 , SYSTEM_LINKAGE_FUNCTION
2619 , PJI_PROJECT_RECORD_FLAG
2620 , PJI_RESOURCE_RECORD_FLAG
2621 , CODE_COMBINATION_ID
2622 , PRVDR_GL_DATE
2623 , RECVR_GL_DATE
2624 , GL_PERIOD_NAME
2625 , PRVDR_PA_DATE
2626 , RECVR_PA_DATE
2627 , PA_PERIOD_NAME
2628 , TXN_CURRENCY_CODE
2629 , TXN_REVENUE
2630 , TXN_RAW_COST
2631 , TXN_BILL_RAW_COST
2632 , TXN_BURDENED_COST
2633 , TXN_BILL_BURDENED_COST
2634 , TXN_UBR
2635 , TXN_UER
2636 , PRJ_REVENUE
2637 , PRJ_RAW_COST
2638 , PRJ_BILL_RAW_COST
2639 , PRJ_BURDENED_COST
2640 , PRJ_BILL_BURDENED_COST
2641 , PRJ_UBR
2642 , PRJ_UER
2643 , POU_REVENUE
2644 , POU_RAW_COST
2645 , POU_BILL_RAW_COST
2646 , POU_BURDENED_COST
2647 , POU_BILL_BURDENED_COST
2648 , POU_UBR
2649 , POU_UER
2650 , EOU_RAW_COST
2651 , EOU_BILL_RAW_COST
2652 , EOU_BURDENED_COST
2653 , EOU_BILL_BURDENED_COST
2654 , EOU_UBR
2655 , EOU_UER
2656 , QUANTITY
2657 , BILL_QUANTITY
2658 )
2659 SELECT
2660 grp.WORKER_ID
2661 , grp.SLICE_ID
2662 , grp.PROJECT_ID
2663 , grp.TASK_ID
2664 , grp.PERSON_ID
2665 , grp.PROJECT_ORG_ID
2666 , grp.PROJECT_ORGANIZATION_ID
2667 , grp.PROJECT_TYPE_CLASS
2668 , grp.CUSTOMER_ID
2669 , grp.EXPENDITURE_ORG_ID
2670 , grp.EXPENDITURE_ORGANIZATION_ID
2671 , grp.JOB_ID
2672 , grp.VENDOR_ID
2673 , grp.WORK_TYPE_ID
2674 , grp.EXP_EVT_TYPE_ID
2675 , grp.EXPENDITURE_TYPE
2676 , grp.EVENT_TYPE
2677 , grp.EVENT_TYPE_CLASSIFICATION
2678 , grp.EXPENDITURE_CATEGORY
2679 , grp.REVENUE_CATEGORY
2680 , grp.NON_LABOR_RESOURCE
2681 , grp.BOM_LABOR_RESOURCE_ID
2682 , grp.BOM_EQUIPMENT_RESOURCE_ID
2683 , grp.INVENTORY_ITEM_ID
2684 , grp.SYSTEM_LINKAGE_FUNCTION
2685 , grp.PJI_PROJECT_RECORD_FLAG
2686 , grp.PJI_RESOURCE_RECORD_FLAG
2687 , grp.CODE_COMBINATION_ID
2688 , grp.PRVDR_GL_DATE
2689 , grp.RECVR_GL_DATE
2690 , grp.GL_PERIOD_NAME
2691 , grp.PRVDR_PA_DATE
2692 , grp.RECVR_PA_DATE
2693 , grp.PA_PERIOD_NAME
2694 , grp.TXN_CURRENCY_CODE
2695 , sum(grp.TXN_REVENUE)
2696 , sum(grp.TXN_RAW_COST)
2697 , sum(grp.TXN_BILL_RAW_COST)
2698 , sum(grp.TXN_BURDENED_COST)
2699 , sum(grp.TXN_BILL_BURDENED_COST)
2700 , sum(grp.TXN_UBR)
2701 , sum(grp.TXN_UER)
2702 , sum(grp.PRJ_REVENUE)
2703 , sum(grp.PRJ_RAW_COST)
2704 , sum(grp.PRJ_BILL_RAW_COST)
2705 , sum(grp.PRJ_BURDENED_COST)
2706 , sum(grp.PRJ_BILL_BURDENED_COST)
2707 , sum(grp.PRJ_UBR)
2708 , sum(grp.PRJ_UER)
2709 , sum(grp.POU_REVENUE)
2710 , sum(grp.POU_RAW_COST)
2711 , sum(grp.POU_BILL_RAW_COST)
2712 , sum(grp.POU_BURDENED_COST)
2713 , sum(grp.POU_BILL_BURDENED_COST)
2714 , sum(grp.POU_UBR)
2715 , sum(grp.POU_UER)
2716 , sum(grp.EOU_RAW_COST)
2717 , sum(grp.EOU_BILL_RAW_COST)
2718 , sum(grp.EOU_BURDENED_COST)
2719 , sum(grp.EOU_BILL_BURDENED_COST)
2720 , sum(grp.EOU_UBR)
2721 , sum(grp.EOU_UER)
2722 , sum(grp.QUANTITY)
2723 , sum(grp.BILL_QUANTITY)
2724 FROM (
2725 SELECT /*+ ORDERED */
2726 p_worker_id AS WORKER_ID
2727 , 1 AS SLICE_ID
2728 , erdl.Project_ID AS PROJECT_ID
2729 , scope.Task_ID AS TASK_ID
2730 , -1 AS PERSON_ID
2731 , scope.Project_Org_ID AS PROJECT_ORG_ID
2732 , scope.Project_Organization_ID AS PROJECT_ORGANIZATION_ID
2733 , scope.Project_Type_Class AS PROJECT_TYPE_CLASS
2734 , scope.Customer_ID AS CUSTOMER_ID
2735 , -1 AS EXPENDITURE_ORG_ID
2736 , scope.Expenditure_Organization_ID AS EXPENDITURE_ORGANIZATION_ID
2737 , -1 AS JOB_ID
2738 , -1 AS VENDOR_ID
2739 , -1 AS WORK_TYPE_ID
2740 , scope.Exp_Evt_Type_ID AS EXP_EVT_TYPE_ID
2741 , PJI_FM_SUM_MAIN.g_null AS EXPENDITURE_TYPE
2742 , scope.Event_Type AS EVENT_TYPE
2743 , scope.Event_Type_Classification AS EVENT_TYPE_CLASSIFICATION
2744 , PJI_FM_SUM_MAIN.g_null AS EXPENDITURE_CATEGORY
2745 , scope.Revenue_Category AS REVENUE_CATEGORY
2746 , 'PJI$NULL' AS NON_LABOR_RESOURCE
2747 , -1 AS BOM_LABOR_RESOURCE_ID
2748 , -1 AS BOM_EQUIPMENT_RESOURCE_ID
2749 , -1 AS INVENTORY_ITEM_ID
2750 , PJI_FM_SUM_MAIN.g_null AS SYSTEM_LINKAGE_FUNCTION
2751 , 'Y' AS PJI_PROJECT_RECORD_FLAG
2752 , 'N' AS PJI_RESOURCE_RECORD_FLAG
2753 , -1 AS CODE_COMBINATION_ID
2754 , Greatest(scope.GL_Date,l_min_date) AS PRVDR_GL_DATE
2755 , Greatest(scope.GL_Date,l_min_date) AS RECVR_GL_DATE
2756 , scope.GL_Period_Name AS GL_PERIOD_NAME
2757 , Greatest(scope.PA_Date,l_min_date) AS PRVDR_PA_DATE
2758 , Greatest(scope.PA_Date,l_min_date) AS RECVR_PA_DATE
2759 , scope.PA_Period_Name AS PA_PERIOD_NAME
2760 , erdl.Funding_Currency_Code AS TXN_CURRENCY_CODE
2761 , decode(scope.line_source_type,
2762 'R', (erdl.Funding_Revenue_Amount),
2763 'L', (-erdl.Funding_Revenue_Amount)
2764 ) AS TXN_REVENUE
2765 , to_number(null) AS TXN_RAW_COST
2766 , to_number(null) AS TXN_BILL_RAW_COST
2767 , to_number(null) AS TXN_BURDENED_COST
2768 , to_number(null) AS TXN_BILL_BURDENED_COST
2769 , to_number(null) AS TXN_UBR
2770 , to_number(null) AS TXN_UER
2771 , decode(scope.line_source_type,
2772 'R', (erdl.Project_Revenue_Amount),
2773 'L', (-erdl.Project_Revenue_Amount)
2774 ) AS PRJ_REVENUE
2775 , to_number(null) AS PRJ_RAW_COST
2776 , to_number(null) AS PRJ_BILL_RAW_COST
2777 , to_number(null) AS PRJ_BURDENED_COST
2778 , to_number(null) AS PRJ_BILL_BURDENED_COST
2779 , to_number(null) AS PRJ_UBR
2780 , to_number(null) AS PRJ_UER
2781 , decode(scope.line_source_type,
2782 'R', (erdl.Projfunc_Revenue_Amount),
2783 'L', (-erdl.Projfunc_Revenue_Amount)
2784 ) AS POU_REVENUE
2785 , to_number(null) AS POU_RAW_COST
2786 , to_number(null) AS POU_BILL_RAW_COST
2787 , to_number(null) AS POU_BURDENED_COST
2788 , to_number(null) AS POU_BILL_BURDENED_COST
2789 , to_number(null) AS POU_UBR
2790 , to_number(null) AS POU_UER
2791 , to_number(null) AS EOU_RAW_COST
2792 , to_number(null) AS EOU_BILL_RAW_COST
2793 , to_number(null) AS EOU_BURDENED_COST
2794 , to_number(null) AS EOU_BILL_BURDENED_COST
2795 , to_number(null) AS EOU_UBR
2796 , to_number(null) AS EOU_UER
2797 , to_number(null) AS QUANTITY
2798 , to_number(null) AS BILL_QUANTITY
2799 FROM
2800 PJI_FM_REXT_ERDL scope
2801 , pa_cust_event_rdl_all erdl
2802 WHERE
2803 scope.worker_id = p_worker_id
2804 and scope.erdl_rowid = erdl.rowid
2805 and scope.event_num = erdl.event_num
2806 and NVL(scope.task_id,-1) = NVL(erdl.task_id,-1)
2807 ) grp
2808 GROUP BY
2809 grp.WORKER_ID
2810 , grp.SLICE_ID
2811 , grp.PROJECT_ID
2812 , grp.TASK_ID
2813 , grp.PERSON_ID
2814 , grp.PROJECT_ORG_ID
2815 , grp.PROJECT_ORGANIZATION_ID
2816 , grp.PROJECT_TYPE_CLASS
2817 , grp.CUSTOMER_ID
2818 , grp.EXPENDITURE_ORG_ID
2819 , grp.EXPENDITURE_ORGANIZATION_ID
2820 , grp.JOB_ID
2821 , grp.VENDOR_ID
2822 , grp.WORK_TYPE_ID
2823 , grp.EXP_EVT_TYPE_ID
2824 , grp.EXPENDITURE_TYPE
2825 , grp.EVENT_TYPE
2826 , grp.EVENT_TYPE_CLASSIFICATION
2827 , grp.EXPENDITURE_CATEGORY
2828 , grp.REVENUE_CATEGORY
2829 , grp.NON_LABOR_RESOURCE
2830 , grp.BOM_LABOR_RESOURCE_ID
2831 , grp.BOM_EQUIPMENT_RESOURCE_ID
2832 , grp.INVENTORY_ITEM_ID
2833 , grp.SYSTEM_LINKAGE_FUNCTION
2834 , grp.PJI_PROJECT_RECORD_FLAG
2835 , grp.PJI_RESOURCE_RECORD_FLAG
2836 , grp.CODE_COMBINATION_ID
2837 , grp.PRVDR_GL_DATE
2838 , grp.RECVR_GL_DATE
2839 , grp.GL_PERIOD_NAME
2840 , grp.PRVDR_PA_DATE
2841 , grp.RECVR_PA_DATE
2842 , grp.PA_PERIOD_NAME
2843 , grp.TXN_CURRENCY_CODE
2844 ;
2845
2846 end if; -- EXTRACTION_TYPE <> 'FULL'
2847
2848 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ERDL(p_worker_id);');
2849
2850 -- truncate intermediate tables no longer required
2851 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
2852 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_ERDL' , 'NORMAL',null);
2853
2854 commit;
2855
2856 end EXTRACT_BATCH_ERDL;
2857
2858
2859 -- -----------------------------------------------------
2860 -- procedure EXTRACT_BATCH_DINV
2861 -- -----------------------------------------------------
2862 procedure EXTRACT_BATCH_DINV (p_worker_id in number) is
2863
2864 l_process varchar2(30);
2865 l_extraction_type varchar2(30);
2866 l_from_project_id number := 0;
2867 l_to_project_id number := 0;
2868
2869 begin
2870
2871 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
2872
2873 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINV(p_worker_id);')) then
2874 return;
2875 end if;
2876
2877 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
2878
2879 INSERT /*+ APPEND */ INTO PJI_FM_EXTR_DINVC
2880 ( WORKER_ID
2881 , ROW_ID
2882 , PROJECT_ORG_ID
2883 , PROJECT_ORGANIZATION_ID
2884 , PROJECT_ID
2885 , PJI_PROJECT_STATUS
2886 , DRAFT_INVOICE_NUM
2887 , UNBILLED_RECEIVABLE_DR
2888 , UNEARNED_REVENUE_CR
2889 , TRANSFER_STATUS_CODE
2890 , GL_DATE
2891 , PA_DATE
2892 , SYSTEM_REFERENCE
2893 , APPROVED_DATE
2894 , APPROVED_BY_PERSON_ID
2895 , CANCEL_CREDIT_MEMO_FLAG
2896 , WRITE_OFF_FLAG
2897 , INTER_COMPANY_BILLING_FLAG
2898 , PJI_SUMMARIZED_FLAG
2899 , CUSTOMER_ID
2900 , APPROVED_FLAG
2901 , PJI_DATE_RANGE_FLAG
2902 )
2903 SELECT /*+ ordered
2904 full(bat) use_hash(bat) parallel(bat)
2905 full(ppa) use_hash(ppa) parallel(ppa)
2906 full(ptyp) use_hash(ptyp)
2907 full(dinv) use_hash(dinv) parallel(dinv)
2908 full(agr) use_hash(agr) parallel(agr) */
2909 p_worker_id worker_id
2910 , dinv.rowid row_id
2911 , nvl(ppa.org_id, -1) project_org_id
2912 , ppa.carrying_out_organization_id project_organization_id
2913 , dinv.project_id project_id
2914 , bat.pji_project_status pji_project_status
2915 , dinv.draft_invoice_num draft_invoice_num
2916 , dinv.unbilled_receivable_dr unbilled_receivable_dr
2917 , dinv.unearned_revenue_cr unearned_revenue_cr
2918 , dinv.transfer_status_code transfer_status_code
2919 , dinv.gl_date gl_date
2920 , dinv.pa_date pa_date
2921 , dinv.system_reference system_reference
2922 , dinv.approved_date approved_date
2923 , dinv.approved_by_person_id approved_by_person_id
2924 , nvl2(dinv.draft_invoice_num_credited,'Y','N') cancel_credit_memo_flag
2925 , dinv.write_off_flag write_off_flag
2926 , ptyp.cc_prvdr_flag inter_company_billing_flag
2927 , dinv.pji_summarized_flag pji_summarized_flag
2928 , agr.customer_id customer_id
2929 , decode(nvl(dinv.approved_by_person_id,
2930 -1), -1, 'N','Y') approved_flag
2931 , 'Y' pji_date_range_flag
2932 -- the flag cc_prvdr_flag on the project_type indicates whether
2933 -- the project is used for inter project billings
2934 -- since we are considering only external revenue to be consistent we
2935 -- need to consider only the external invoices
2936 -- NOTE for cost we will consider everything (external + internal)
2937 -- this skews the margin but ...
2938 FROM
2939 pji_fm_proj_batch_map bat
2940 , pa_projects_all ppa
2941 , pa_project_types_all ptyp
2942 , pa_draft_invoices_all dinv
2943 , pa_agreements_all agr
2944 WHERE
2945 l_extraction_type = 'FULL'
2946 and bat.worker_id = p_worker_id
2947 and ppa.project_id = bat.project_id
2948 and ppa.project_type = ptyp.project_type
2949 and nvl(ppa.org_id,-1) = nvl(ptyp.org_id,-1)
2950 -- and ptyp.cc_prvdr_flag <> 'Oracle Inter-Project'
2951 and dinv.gl_date is not null
2952 and dinv.pa_date is not null
2953 and ppa.project_id = dinv.project_id
2954 and bat.extraction_type = 'F'
2955 -- the pji_summarized_flag will have other values besides N and null
2956 -- to indicate if the invoice is still open
2957 -- Thus for incremental we need to pick all the invoices which have the
2958 -- flag as not null. Then only if the flag is N do we do the incremental
2959 -- processing. But if the value is something else then we use it only to
2960 -- check activities that might have happened on the AR side
2961 -- and dinv.gl_date between bat.start_date and bat.end_date
2962 and dinv.system_reference is not null
2963 and dinv.system_reference <> 0
2964 and dinv.agreement_id = agr.agreement_id
2965 union all
2966 SELECT /*+ ordered
2967 full(bat)
2968 index(drv, PA_DRAFT_INVOICES_U1)
2969 use_nl(dinv, ppa, ptyp, agr)
2970 parallel(bat) parallel(dinv) parallel(ppa)
2971 parallel(ptyp) parallel(agr) */
2972 p_worker_id worker_id
2973 , dinv.rowid row_id
2974 , nvl(ppa.org_id, -1) project_org_id
2975 , ppa.carrying_out_organization_id project_organization_id
2976 , dinv.project_id project_id
2977 , bat.pji_project_status pji_project_status
2978 , dinv.draft_invoice_num draft_invoice_num
2979 , dinv.unbilled_receivable_dr unbilled_receivable_dr
2980 , dinv.unearned_revenue_cr unearned_revenue_cr
2981 , dinv.transfer_status_code transfer_status_code
2982 , dinv.gl_date gl_date
2983 , dinv.pa_date pa_date
2984 , dinv.system_reference system_reference
2985 , dinv.approved_date approved_date
2986 , dinv.approved_by_person_id approved_by_person_id
2987 , nvl2(dinv.draft_invoice_num_credited,'Y','N') cancel_credit_memo_flag
2988 , dinv.write_off_flag write_off_flag
2989 , ptyp.cc_prvdr_flag inter_company_billing_flag
2990 , dinv.pji_summarized_flag pji_summarized_flag
2991 , agr.customer_id customer_id
2992 , decode(nvl(dinv.approved_by_person_id,
2993 -1), -1, 'N','Y') approved_flag
2994 , 'Y' pji_date_range_flag
2995 -- the flag cc_prvdr_flag on the project_type indicates whether
2996 -- the project is used for inter project billings
2997 -- since we are considering only external revenue to be consistent we
2998 -- need to consider only the external invoices
2999 -- NOTE for cost we will consider everything (external + internal)
3000 -- this skews the margin but ...
3001 FROM
3002 pji_fm_proj_batch_map bat
3003 , pa_draft_invoices_all dinv
3004 , pa_projects_all ppa
3005 , pa_project_types_all ptyp
3006 , pa_agreements_all agr
3007 WHERE
3008 l_extraction_type = 'INCREMENTAL'
3009 and bat.worker_id = p_worker_id
3010 and bat.project_id = dinv.project_id
3011 and ppa.project_id = bat.project_id
3012 and ppa.project_type = ptyp.project_type
3013 and nvl(ppa.org_id,-1) = nvl(ptyp.org_id,-1)
3014 -- and ptyp.cc_prvdr_flag <> 'Oracle Inter-Project'
3015 and dinv.gl_date is not null
3016 and dinv.pa_date is not null
3017 and ppa.project_id = dinv.project_id
3018 and bat.extraction_type = 'F'
3019 -- the pji_summarized_flag will have other values besides N and null
3020 -- to indicate if the invoice is still open
3021 -- Thus for incremental we need to pick all the invoices which have the
3022 -- flag as not null. Then only if the flag is N do we do the incremental
3023 -- processing. But if the value is something else then we use it only to
3024 -- check activities that might have happened on the AR side
3025 -- and dinv.gl_date between bat.start_date and bat.end_date
3026 and dinv.system_reference is not null
3027 and dinv.system_reference <> 0
3028 and dinv.agreement_id = agr.agreement_id
3029 union all
3030 SELECT /*+ ordered
3031 index(dinv PA_DRAFT_INVOICES_N11)
3032 full(bat) use_nl(dinv, ppa, ptyp, agr)
3033 parallel(bat) parallel(dinv) parallel(ppa)
3034 parallel(ptyp) parallel(agr) */
3035 p_worker_id worker_id
3036 , dinv.rowid row_id
3037 , nvl(ppa.org_id, -1) project_org_id
3038 , ppa.carrying_out_organization_id project_organization_id
3039 , dinv.project_id project_id
3040 , bat.pji_project_status pji_project_status
3041 , dinv.draft_invoice_num draft_invoice_num
3042 , dinv.unbilled_receivable_dr unbilled_receivable_dr
3043 , dinv.unearned_revenue_cr unearned_revenue_cr
3044 , dinv.transfer_status_code transfer_status_code
3045 , dinv.gl_date gl_date
3046 , dinv.pa_date pa_date
3047 , dinv.system_reference system_reference
3048 , dinv.approved_date approved_date
3049 , dinv.approved_by_person_id approved_by_person_id
3050 , nvl2(dinv.draft_invoice_num_credited,'Y','N') cancel_credit_memo_flag
3051 , dinv.write_off_flag write_off_flag
3052 , ptyp.cc_prvdr_flag inter_company_billing_flag
3053 , dinv.pji_summarized_flag pji_summarized_flag
3054 , agr.customer_id customer_id
3055 , decode(nvl(dinv.approved_by_person_id,
3056 -1), -1, 'N','Y') approved_flag
3057 , 'Y' pji_date_range_flag
3058 -- the flag cc_prvdr_flag on the project_type indicates whether
3059 -- the project is used for inter project billings
3060 -- since we are considering only external revenue to be consistent we
3061 -- need to consider only the external invoices
3062 -- NOTE for cost we will consider everything (external + internal)
3063 -- this skews the margin but ...
3064 FROM
3065 pji_fm_proj_batch_map bat
3066 , pa_draft_invoices_all dinv
3067 , pa_projects_all ppa
3068 , pa_project_types_all ptyp
3069 , pa_agreements_all agr
3070 WHERE
3071 l_extraction_type = 'INCREMENTAL'
3072 and bat.worker_id = p_worker_id
3073 and ppa.project_id = bat.project_id
3074 and dinv.project_id = bat.project_id
3075 and ppa.project_type = ptyp.project_type
3076 and nvl(ppa.org_id,-1) = nvl(ptyp.org_id,-1)
3077 -- and ptyp.cc_prvdr_flag <> 'Oracle Inter-Project'
3078 and dinv.gl_date is not null
3079 and dinv.pa_date is not null
3080 and ppa.project_id = dinv.project_id
3081 and bat.extraction_type = 'I'
3082 and dinv.pji_summarized_flag = 'N'
3083 -- the pji_summarized_flag will have other values besides N and null
3084 -- to indicate if the invoice is still open
3085 -- Thus for incremental we need to pick all the invoices which have the
3086 -- flag as not null. Then only if the flag is N do we do the incremental
3087 -- processing. But if the value is something else then we use it only to
3088 -- check activities that might have happened on the AR side
3089 -- and dinv.gl_date between bat.start_date and bat.end_date
3090 and dinv.system_reference is not null
3091 and dinv.system_reference <> 0
3092 and dinv.agreement_id = agr.agreement_id
3093 union all
3094 SELECT /*+ ordered
3095 full(bat) use_hash(bat) parallel(bat)
3096 full(ppa) use_hash(ppa) parallel(ppa)
3097 full(ptyp) use_hash(ptyp)
3098 full(dinv) use_hash(dinv) parallel(dinv)
3099 full(agr) use_hash(agr) parallel(agr) */
3100 p_worker_id worker_id
3101 , dinv.rowid row_id
3102 , nvl(ppa.org_id, -1) project_org_id
3103 , ppa.carrying_out_organization_id project_organization_id
3104 , dinv.project_id project_id
3105 , bat.pji_project_status pji_project_status
3106 , dinv.draft_invoice_num draft_invoice_num
3107 , dinv.unbilled_receivable_dr unbilled_receivable_dr
3108 , dinv.unearned_revenue_cr unearned_revenue_cr
3109 , dinv.transfer_status_code transfer_status_code
3110 , dinv.gl_date gl_date
3111 , dinv.pa_date pa_date
3112 , dinv.system_reference system_reference
3113 , dinv.approved_date approved_date
3114 , dinv.approved_by_person_id approved_by_person_id
3115 , nvl2(dinv.draft_invoice_num_credited,'Y','N') cancel_credit_memo_flag
3116 , dinv.write_off_flag write_off_flag
3117 , ptyp.cc_prvdr_flag inter_company_billing_flag
3118 , dinv.pji_summarized_flag pji_summarized_flag
3119 , agr.customer_id customer_id
3120 , decode(nvl(dinv.approved_by_person_id,
3121 -1), -1, 'N','Y') approved_flag
3122 , 'Y' pji_date_range_flag
3123 -- the flag cc_prvdr_flag on the project_type indicates whether
3124 -- the project is used for inter project billings
3125 -- since we are considering only external revenue to be consistent we
3126 -- need to consider only the external invoices
3127 -- NOTE for cost we will consider everything (external + internal)
3128 -- this skews the margin but ...
3129 FROM
3130 pji_fm_proj_batch_map bat
3131 , pa_projects_all ppa
3132 , pa_project_types_all ptyp
3133 , pa_draft_invoices_all dinv
3134 , pa_agreements_all agr
3135 WHERE
3136 l_extraction_type = 'PARTIAL'
3137 and bat.worker_id = p_worker_id
3138 and ppa.project_id = bat.project_id
3139 and ppa.project_type = ptyp.project_type
3140 and nvl(ppa.org_id,-1) = nvl(ptyp.org_id,-1)
3141 -- and ptyp.cc_prvdr_flag <> 'Oracle Inter-Project'
3142 and dinv.gl_date is not null
3143 and dinv.pa_date is not null
3144 and ppa.project_id = dinv.project_id
3145 and bat.extraction_type = 'P'
3146 -- the pji_summarized_flag will have other values besides N and null
3147 -- to indicate if the invoice is still open
3148 -- Thus for incremental we need to pick all the invoices which have the
3149 -- flag as not null. Then only if the flag is N do we do the incremental
3150 -- processing. But if the value is something else then we use it only to
3151 -- check activities that might have happened on the AR side
3152 -- and dinv.gl_date between bat.start_date and bat.end_date
3153 and dinv.system_reference is not null
3154 and dinv.system_reference <> 0
3155 and dinv.agreement_id = agr.agreement_id;
3156
3157 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINV(p_worker_id);');
3158
3159 commit;
3160
3161 end EXTRACT_BATCH_DINV;
3162
3163 -- -----------------------------------------------------
3164 -- procedure MARK_EXTRACTED_DINV_ROWS
3165 -- -----------------------------------------------------
3166 procedure MARK_EXTRACTED_DINV_ROWS (p_worker_id in number) is
3167
3168 l_process varchar2(30);
3169 l_extraction_type varchar2(15);
3170
3171 begin
3172
3173 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3174 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3175
3176 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_DINV_ROWS(p_worker_id);')) then
3177 return;
3178 end if;
3179
3180 UPDATE pa_draft_invoices_all dinv
3181 SET dinv.pji_summarized_flag = 'O'
3182 -- later the flag is updated to null for those invoices that are closed
3183 WHERE dinv.rowid in (select row_id
3184 from PJI_FM_EXTR_DINVC
3185 where worker_id = p_worker_id
3186 and transfer_status_code = 'A'
3187 )
3188 AND ( (l_extraction_type = 'INCREMENTAL'
3189 and nvl(dinv.pji_summarized_flag,'O') <> 'O')
3190 or
3191 l_extraction_type <> 'INCREMENTAL'
3192 )
3193 ;
3194
3195 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.MARK_EXTRACTED_DINV_ROWS(p_worker_id);');
3196
3197 commit;
3198
3199 end MARK_EXTRACTED_DINV_ROWS;
3200
3201
3202 -- -----------------------------------------------------
3203 -- procedure EXTRACT_BATCH_DINVITEM
3204 -- -----------------------------------------------------
3205 procedure EXTRACT_BATCH_DINVITEM (p_worker_id in number) is
3206
3207 l_process varchar2(30);
3208 l_extraction_type varchar2(30);
3209 l_from_project_id number := 0;
3210 l_to_project_id number := 0;
3211
3212 begin
3213
3214 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3215
3216 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINVITEM(p_worker_id);')) then
3217 return;
3218 end if;
3219
3220 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
3221 'CURRENT_BATCH') = 1) then
3222 -- implicit commit
3223 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
3224 tabname => 'PJI_FM_EXTR_DINVC',
3225 percent => 10,
3226 degree => PJI_UTILS.
3227 GET_DEGREE_OF_PARALLELISM);
3228 -- implicit commit
3229 FND_STATS.GATHER_COLUMN_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
3230 tabname => 'PJI_FM_EXTR_DINVC',
3231 colname => 'PROJECT_ID',
3232 percent => 10,
3233 degree => PJI_UTILS.
3234 GET_DEGREE_OF_PARALLELISM);
3235 end if;
3236
3237 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3238
3239 INSERT /*+ APPEND */ INTO PJI_FM_EXTR_DINVCITM
3240 ( WORKER_ID
3241 , PROJECT_ORG_ID
3242 , PROJECT_ORGANIZATION_ID
3243 , PROJECT_ID
3244 , DRAFT_INVOICE_NUM
3245 , GL_DATE
3246 , PA_DATE
3247 , CANCEL_CREDIT_MEMO_FLAG
3248 , WRITE_OFF_FLAG
3249 , INTER_COMPANY_BILLING_FLAG
3250 , PJI_SUMMARIZED_FLAG
3251 , POU_INVOICE_AMOUNT
3252 , PRJ_INVOICE_AMOUNT
3253 , CUSTOMER_ID
3254 , APPROVED_FLAG
3255 , PJI_DATE_RANGE_FLAG
3256 , TRANSFER_STATUS_CODE
3257 , PJI_RECORD_TYPE
3258 , AR_INVOICE_COUNT
3259 , AR_INVOICE_WRITEOFF_COUNT
3260 , AR_CREDIT_MEMO_COUNT
3261 , AR_UNAPPR_INVOICE_COUNT
3262 , AR_APPR_INVOICE_COUNT
3263 )
3264 SELECT /*+ ordered
3265 full(part) use_hash(part)
3266 full(item) use_hash(item) parallel(item) */
3267 p_worker_id worker_id
3268 , nvl(part.project_org_id, -1) project_org_id
3269 , part.project_organization_id project_organization_id
3270 , part.project_id project_id
3271 , part.draft_invoice_num draft_invoice_num
3272 , part.gl_date gl_date
3273 , part.pa_date pa_date
3274 , part.cancel_credit_memo_flag cancel_credit_memo_flag
3275 , part.write_off_flag write_off_flag
3276 , part.inter_company_billing_flag inter_company_billing_flag
3277 , part.pji_summarized_flag pji_summarized_flag
3278 , nvl(sum(item.projfunc_bill_amount),0) pou_invoice_amount
3279 , nvl(sum(item.project_bill_amount),0) prj_invoice_amount
3280 , part.customer_id customer_id
3281 , part.approved_flag approved_flag
3282 , part.pji_date_range_flag pji_date_range_flag
3283 , part.transfer_status_code transfer_status_code
3284 , decode(part.transfer_status_code, -- Activity 'A' vs Snapshot 'S'
3285 'A','A','S') pji_record_type
3286 , to_number(null) ar_invoice_count
3287 , to_number(null) ar_invoice_writeoff_count
3288 , to_number(null) ar_credit_memo_count
3289 , to_number(null) ar_unappr_invoice_count
3290 , to_number(null) ar_appr_invoice_count
3291 FROM
3292 PJI_FM_EXTR_DINVC part
3293 , pa_draft_invoice_items item
3294 WHERE
3295 l_extraction_type = 'FULL'
3296 and part.worker_id = p_worker_id
3297 and part.project_id = item.project_id
3298 and part.draft_invoice_num = item.draft_invoice_num
3299 and item.invoice_line_type <> 'NET ZERO ADJUSTMENT'
3300 and part.gl_date is not null
3301 and part.pa_date is not null
3302 GROUP BY part.project_id,
3303 nvl(part.project_org_id, -1),
3304 part.project_organization_id,
3305 part.draft_invoice_num,
3306 part.gl_date,
3307 part.pa_date,
3308 part.write_off_flag,
3309 part.customer_id,
3310 part.approved_flag,
3311 part.pji_date_range_flag,
3312 part.transfer_status_code,
3313 part.cancel_credit_memo_flag,
3314 part.inter_company_billing_flag,
3315 part.pji_summarized_flag
3316 union all
3317 SELECT /*+ ordered
3318 full(part)
3319 */
3320 p_worker_id worker_id
3321 , nvl(part.project_org_id, -1) project_org_id
3322 , part.project_organization_id project_organization_id
3323 , part.project_id project_id
3324 , part.draft_invoice_num draft_invoice_num
3325 , part.gl_date gl_date
3326 , part.pa_date pa_date
3327 , part.cancel_credit_memo_flag cancel_credit_memo_flag
3328 , part.write_off_flag write_off_flag
3329 , part.inter_company_billing_flag inter_company_billing_flag
3330 , part.pji_summarized_flag pji_summarized_flag
3331 , nvl(sum(item.projfunc_bill_amount),0) pou_invoice_amount
3332 , nvl(sum(item.project_bill_amount),0) prj_invoice_amount
3333 , part.customer_id customer_id
3334 , part.approved_flag approved_flag
3335 , part.pji_date_range_flag pji_date_range_flag
3336 , part.transfer_status_code transfer_status_code
3337 , decode(part.transfer_status_code, -- Activity 'A' vs Snapshot 'S'
3338 'A','A','S') pji_record_type
3339 , to_number(null) ar_invoice_count
3340 , to_number(null) ar_invoice_writeoff_count
3341 , to_number(null) ar_credit_memo_count
3342 , to_number(null) ar_unappr_invoice_count
3343 , to_number(null) ar_appr_invoice_count
3344 FROM
3345 PJI_FM_EXTR_DINVC part
3346 , pa_draft_invoice_items item
3347 WHERE
3348 l_extraction_type = 'INCREMENTAL'
3349 and part.worker_id = p_worker_id
3350 and part.project_id = item.project_id
3351 and part.draft_invoice_num = item.draft_invoice_num
3352 and item.invoice_line_type <> 'NET ZERO ADJUSTMENT'
3353 and part.gl_date is not null
3354 and part.pa_date is not null
3355 GROUP BY part.project_id,
3356 nvl(part.project_org_id, -1),
3357 part.project_organization_id,
3358 part.draft_invoice_num,
3359 part.gl_date,
3360 part.pa_date,
3361 part.write_off_flag,
3362 part.customer_id,
3363 part.approved_flag,
3364 part.pji_date_range_flag,
3365 part.transfer_status_code,
3366 part.cancel_credit_memo_flag,
3367 part.inter_company_billing_flag,
3368 part.pji_summarized_flag
3369 union all
3370 SELECT /*+ ordered
3371 full(part) use_hash(part)
3372 full(item) use_hash(item) parallel(item) */
3373 p_worker_id worker_id
3374 , nvl(part.project_org_id, -1) project_org_id
3375 , part.project_organization_id project_organization_id
3376 , part.project_id project_id
3377 , part.draft_invoice_num draft_invoice_num
3378 , part.gl_date gl_date
3379 , part.pa_date pa_date
3380 , part.cancel_credit_memo_flag cancel_credit_memo_flag
3381 , part.write_off_flag write_off_flag
3382 , part.inter_company_billing_flag inter_company_billing_flag
3383 , part.pji_summarized_flag pji_summarized_flag
3384 , nvl(sum(item.projfunc_bill_amount),0) pou_invoice_amount
3385 , nvl(sum(item.project_bill_amount),0) prj_invoice_amount
3386 , part.customer_id customer_id
3387 , part.approved_flag approved_flag
3388 , part.pji_date_range_flag pji_date_range_flag
3389 , part.transfer_status_code transfer_status_code
3390 , decode(part.transfer_status_code, -- Activity 'A' vs Snapshot 'S'
3391 'A','A','S') pji_record_type
3392 , to_number(null) ar_invoice_count
3393 , to_number(null) ar_invoice_writeoff_count
3394 , to_number(null) ar_credit_memo_count
3395 , to_number(null) ar_unappr_invoice_count
3396 , to_number(null) ar_appr_invoice_count
3397 FROM
3398 PJI_FM_EXTR_DINVC part
3399 , pa_draft_invoice_items item
3400 WHERE
3401 l_extraction_type = 'PARTIAL'
3402 and part.worker_id = p_worker_id
3403 and part.project_id = item.project_id
3404 and part.draft_invoice_num = item.draft_invoice_num
3405 and item.invoice_line_type <> 'NET ZERO ADJUSTMENT'
3406 and part.gl_date is not null
3407 and part.pa_date is not null
3408 GROUP BY part.project_id,
3409 nvl(part.project_org_id, -1),
3410 part.project_organization_id,
3411 part.draft_invoice_num,
3412 part.gl_date,
3413 part.pa_date,
3414 part.write_off_flag,
3415 part.customer_id,
3416 part.approved_flag,
3417 part.pji_date_range_flag,
3418 part.transfer_status_code,
3419 part.cancel_credit_memo_flag,
3420 part.inter_company_billing_flag,
3421 part.pji_summarized_flag
3422 union all
3423 SELECT
3424 p_worker_id worker_id
3425 , nvl(part.project_org_id, -1) project_org_id
3426 , part.project_organization_id project_organization_id
3427 , part.project_id project_id
3428 , part.draft_invoice_num draft_invoice_num
3429 , part.gl_date gl_date
3430 , part.pa_date pa_date
3431 , part.cancel_credit_memo_flag cancel_credit_memo_flag
3432 , part.write_off_flag write_off_flag
3433 , part.inter_company_billing_flag inter_company_billing_flag
3434 , part.pji_summarized_flag pji_summarized_flag
3435 , to_number(null) pou_invoice_amount
3436 , to_number(null) prj_invoice_amount
3437 , part.customer_id customer_id
3438 , part.approved_flag approved_flag
3439 , part.pji_date_range_flag pji_date_range_flag
3440 , part.transfer_status_code transfer_status_code
3441 , decode(part.transfer_status_code, -- Activity 'A' vs Snapshot 'S'
3442 'A','A','S') pji_record_type
3443 , decode(part.pji_date_range_flag || '_' ||
3444 decode(part.transfer_status_code,
3445 'A','A','S'),
3446 'Y_A', 1, 0) ar_invoice_count
3447 , decode(part.pji_date_range_flag || '_' ||
3448 decode(part.transfer_status_code,
3449 'A','A','S') || '_' ||
3450 part.write_off_flag,
3451 'Y_A_Y', 1,0) ar_invoice_writeoff_count
3452 , decode(part.pji_date_range_flag || '_' ||
3453 decode(part.transfer_status_code,
3454 'A','A','S') || '_' ||
3455 part.cancel_credit_memo_flag,
3456 'Y_A_Y', 1,0) ar_credit_memo_count
3457 , decode(decode(part.transfer_status_code,
3458 'A','A','S') || '_' ||
3459 part.approved_flag,
3460 'S_N',1,0) ar_unappr_invoice_count
3461 , decode(decode(part.transfer_status_code,
3462 'A','A','S') || '_' ||
3463 part.approved_flag,
3464 'S_Y',1,0) ar_appr_invoice_count
3465 FROM
3466 PJI_FM_EXTR_DINVC part
3467 WHERE
3468 part.worker_id = p_worker_id
3469 and part.gl_date is not null
3470 and part.pa_date is not null;
3471
3472 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_DINVITEM(p_worker_id);');
3473
3474 commit;
3475
3476 end EXTRACT_BATCH_DINVITEM;
3477
3478
3479 -- -----------------------------------------------------
3480 -- procedure EXTRACT_BATCH_ARINV
3481 -- -----------------------------------------------------
3482 procedure EXTRACT_BATCH_ARINV (p_worker_id in number) is
3483
3484 l_process varchar2(30);
3485 l_extraction_type varchar2(30);
3486
3487 begin
3488
3489 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3490
3491 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ARINV(p_worker_id);')) then
3492 return;
3493 end if;
3494
3495 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
3496
3497 INSERT /*+ APPEND */ INTO PJI_FM_EXTR_ARINV
3498 ( WORKER_ID
3499 , ROW_ID
3500 , PROJECT_ID
3501 , PROJECT_ORG_ID
3502 , PROJECT_ORGANIZATION_ID
3503 , DRAFT_INVOICE_NUM
3504 , CASH_APPLIED_AMOUNT
3505 , AMOUNT_DUE_REMAINING
3506 , AMOUNT_OVERDUE_REMAINING
3507 , MAX_ACTUAL_DATE_CLOSED
3508 , CUSTOMER_ID
3509 , PJI_SUMMARIZED_FLAG
3510 , BATCH_ID
3511 )
3512 SELECT
3513 p_worker_id worker_id
3514 , row_id
3515 , project_id
3516 , project_org_id
3517 , project_organization_id
3518 , draft_invoice_num
3519 , cash_applied_amount
3520 , amount_due_remaining
3521 , amount_overdue_remaining
3522 , actual_date_closed
3523 , customer_id
3524 , pji_summarized_flag
3525 , ceil(ROWNUM / PJI_FM_SUM_MAIN.g_commit_threshold)
3526 from
3527 (SELECT /*+ ordered
3528 parallel(part)
3529 full(bat) use_hash(bat) */
3530 part.rowid row_id
3531 , part.project_id project_id
3532 , bat.project_org_id
3533 , bat.project_organization_id
3534 , part.draft_invoice_num draft_invoice_num
3535 , nvl(sum(ar.amount_applied),0) cash_applied_amount
3536 , decode(sign(ar.due_date - trunc(sysdate)),
3537 -1, 0, nvl(sum(ar.amount_due_remaining),
3538 0)) amount_due_remaining
3539 , decode(sign(ar.due_date - trunc(sysdate)),
3540 -1, nvl(sum(ar.amount_due_remaining),0),
3541 0) amount_overdue_remaining
3542 , max(ar.actual_date_closed) actual_date_closed
3543 , trx.bill_to_customer_id customer_id
3544 , part.pji_summarized_flag
3545 FROM
3546 pa_draft_invoices_all part
3547 , pji_fm_proj_batch_map bat
3548 , ra_customer_trx_all trx
3549 , ar_payment_schedules_all ar
3550 WHERE
3551 bat.worker_id = p_worker_id
3552 and part.project_id = bat.project_id
3553 and part.gl_date is not null
3554 and part.pa_date is not null
3555 and part.pji_summarized_flag = 'O'
3556 and trx.customer_trx_id = part.system_reference
3557 and ar.customer_trx_id = trx.customer_trx_id
3558 GROUP BY
3559 part.rowid,
3560 part.project_id,
3561 bat.project_org_id,
3562 bat.project_organization_id,
3563 part.draft_invoice_num,
3564 ar.due_date,
3565 trx.bill_to_customer_id,
3566 part.pji_summarized_flag);
3567
3568 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_EXTR.EXTRACT_BATCH_ARINV(p_worker_id);');
3569
3570 commit;
3571
3572 end EXTRACT_BATCH_ARINV;
3573
3574
3575 -- -----------------------------------------------------
3576 -- procedure MARK_FULLY_PAID_INVOICES_PRE
3577 -- -----------------------------------------------------
3578 procedure MARK_FULLY_PAID_INVOICES_PRE (p_worker_id in number) is
3579
3580 l_process varchar2(30);
3581
3582 begin
3583
3584 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3585
3586 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
3587 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_PRE(p_worker_id);')) then
3588 return;
3589 end if;
3590
3591 insert /*+ append */ into PJI_HELPER_BATCH_MAP
3592 (
3593 BATCH_ID,
3594 WORKER_ID,
3595 STATUS
3596 )
3597 select
3598 distinct
3599 BATCH_ID,
3600 null,
3601 null
3602 from
3603 PJI_FM_EXTR_ARINV
3604 where
3605 PJI_SUMMARIZED_FLAG = 'O';
3606
3607 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3608 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_PRE(p_worker_id);');
3609
3610 commit;
3611
3612 end MARK_FULLY_PAID_INVOICES_PRE;
3613
3614
3615 -- -----------------------------------------------------
3616 -- procedure MARK_FULLY_PAID_INVOICES
3617 -- -----------------------------------------------------
3618 procedure MARK_FULLY_PAID_INVOICES (p_worker_id in number) is
3619
3620 l_process varchar2(30);
3621 l_leftover_batches number;
3622 l_helper_batch_id number;
3623 l_row_count number;
3624 l_parallel_processes number;
3625
3626 begin
3627
3628 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3629
3630 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES(p_worker_id);')) then
3631 return;
3632 end if;
3633
3634 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3635 (PJI_FM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
3636
3637 select count(*)
3638 into l_leftover_batches
3639 from PJI_HELPER_BATCH_MAP
3640 where WORKER_ID = p_worker_id and
3641 STATUS = 'P';
3642
3643 l_helper_batch_id := 0;
3644
3645 while l_helper_batch_id >= 0 loop
3646
3647 if (l_leftover_batches > 0) then
3648
3649 l_leftover_batches := l_leftover_batches - 1;
3650
3651 select BATCH_ID
3652 into l_helper_batch_id
3653 from PJI_HELPER_BATCH_MAP
3654 where WORKER_ID = p_worker_id and
3655 STATUS = 'P' and
3656 ROWNUM = 1;
3657
3658 else
3659
3660 update PJI_HELPER_BATCH_MAP
3661 set WORKER_ID = p_worker_id,
3662 STATUS = 'P'
3663 where WORKER_ID is null and
3664 ROWNUM = 1
3665 returning BATCH_ID
3666 into l_helper_batch_id;
3667
3668 end if;
3669
3670 if (sql%rowcount <> 0) then
3671
3672 commit;
3673
3674 UPDATE pa_draft_invoices_all dinv
3675 SET dinv.pji_summarized_flag = NULL
3676 WHERE dinv.rowid in (SELECT /*+ cardinality(ar, 1) */
3677 ar.row_id
3678 FROM PJI_FM_EXTR_ARINV ar
3679 WHERE 1 = 2 -- We will always extract
3680 -- the AR snapshots for now.
3681 AND ar.pji_summarized_flag = 'O'
3682 AND ar.batch_id = l_helper_batch_id);
3683
3684 update PJI_HELPER_BATCH_MAP
3685 set STATUS = 'C'
3686 where WORKER_ID = p_worker_id and
3687 BATCH_ID = l_helper_batch_id;
3688
3689 commit;
3690
3691 else
3692
3693 select count(*)
3694 into l_row_count
3695 from PJI_HELPER_BATCH_MAP
3696 where nvl(STATUS, 'X') <> 'C';
3697
3698 if (l_row_count = 0) then
3699
3700 for x in 2 .. l_parallel_processes loop
3701
3702 update PJI_SYSTEM_PRC_STATUS
3703 set STEP_STATUS = 'C'
3704 where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
3705 STEP_NAME =
3706 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES(p_worker_id);' and
3707 START_DATE is null;
3708
3709 commit;
3710
3711 end loop;
3712
3713 l_helper_batch_id := -1;
3714
3715 else
3716
3717 PJI_PROCESS_UTIL.SLEEP(1); -- so the CPU is not bombarded
3718
3719 end if;
3720
3721 end if;
3722
3723 if (l_helper_batch_id >= 0) then
3724
3725 for x in 2 .. l_parallel_processes loop
3726 if (not PJI_FM_SUM_EXTR.WORKER_STATUS(x, 'OKAY')) then
3727 l_helper_batch_id := -2;
3728 end if;
3729 end loop;
3730
3731 end if;
3732
3733 end loop;
3734
3735 if (l_helper_batch_id <> -2) then
3736
3737 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3738 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES(p_worker_id);');
3739
3740 end if;
3741
3742 commit;
3743
3744 end MARK_FULLY_PAID_INVOICES;
3745
3746
3747 -- -----------------------------------------------------
3748 -- procedure MARK_FULLY_PAID_INVOICES_POST
3749 -- -----------------------------------------------------
3750 procedure MARK_FULLY_PAID_INVOICES_POST (p_worker_id in number) is
3751
3752 l_process varchar2(30);
3753
3754 begin
3755
3756 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
3757
3758 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
3759 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_POST(p_worker_id);')) then
3760 return;
3761 end if;
3762
3763 PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
3764 'PJI_HELPER_BATCH_MAP',
3765 'NORMAL',
3766 null);
3767
3768 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
3769 'PJI_FM_EXTR.MARK_FULLY_PAID_INVOICES_POST(p_worker_id);');
3770
3771 commit;
3772
3773 end MARK_FULLY_PAID_INVOICES_POST;
3774
3775
3776 -- -----------------------------------------------------
3777 -- procedure CLEANUP
3778 -- -----------------------------------------------------
3779 procedure CLEANUP (p_worker_id in number) is
3780
3781 l_schema varchar2(30);
3782
3783 begin
3784
3785 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
3786
3787 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_FUNDG', 'NORMAL',null);
3788
3789 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DREVN', 'NORMAL',null);
3790
3791 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CDL', 'NORMAL',null);
3792
3793 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_CRDL', 'NORMAL',null);
3794
3795 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_REXT_ERDL', 'NORMAL',null);
3796
3797 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVC', 'NORMAL',null);
3798
3799 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_DINVCITM', 'NORMAL',null);
3800
3801 PJI_PROCESS_UTIL.TRUNC_INT_TABLE( l_schema , 'PJI_FM_EXTR_ARINV', 'NORMAL',null);
3802
3803 end CLEANUP;
3804
3805 end PJI_FM_EXTR;