[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_SUM_BKLG
Source
1 PACKAGE BODY PJI_FM_SUM_BKLG AS
2 /* $Header: PJISF12B.pls 120.4 2011/09/22 14:30:49 arbandyo ship $ */
3
4 g_last_activity_date number;
5 g_pji_schema varchar2(30);
6
7
8 -- -----------------------------------------------------
9 -- procedure ROWID_ACTIVITY_DATES_FIN
10 -- -----------------------------------------------------
11 procedure ROWID_ACTIVITY_DATES_FIN (p_worker_id in number) is
12
13 l_process varchar2(30);
14
15 begin
16
17 l_process := PJI_RM_SUM_MAIN.g_process || p_worker_id;
18
19 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_BKLG.ROWID_ACTIVITY_DATES_FIN(p_worker_id);')) then
20 return;
21 end if;
22 /*
23 -- Get the minimum activity dates for each project in the current batch
24 insert /*+ append * into PJI_FM_RMAP_FIN
25 (
26 WORKER_ID
27 , MAP_ROWID
28 , PROJECT_ID
29 , ACTIVITY_MIN_GL_DATE
30 , ACTIVITY_MIN_PA_DATE
31 , REVENUE
32 )
33 select /*+ ordered
34 full(fin9) use_hash(fin9) parallel(fin9)
35 full(map) use_hash(map) swap_join_inputs(map)
36 *
37 p_worker_id
38 , map.rowid
39 , map.project_id
40 -- temptemp should consider GL and PA seperately
41 , to_date(min(fin9.TIME_ID), 'J')
42 , to_date(min(fin9.TIME_ID), 'J')
43 -- , to_date(min(fin9.RECVR_GL_TIME_ID),'J')
44 -- , to_date(min(fin9.RECVR_PA_TIME_ID),'J')
45 , sum(abs(fin9.POU_REVENUE))
46 from pji_pji_proj_batch_map map
47 , PJI_FM_AGGR_FIN9 fin9
48 where 1 = 1
49 and map.worker_id = p_worker_id
50 and fin9.project_id = map.project_id
51 -- temptemp should consider GL and PA seperately
52 and nvl(fin9.TIME_ID, 0) > 0
53 -- and nvl(fin9.RECVR_GL_TIME_ID,0) > 0
54 -- and nvl(fin9.RECVR_PA_TIME_ID,0) > 0
55 group by
56 map.rowid
57 , map.project_id
58 ;Commented for bug 13011859 */
59
60 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_BKLG.ROWID_ACTIVITY_DATES_FIN(p_worker_id);');
61
62 commit;
63
64 end ROWID_ACTIVITY_DATES_FIN;
65
66
67 -- -----------------------------------------------------
68 -- procedure UPDATE_ACTIVITY_DATES_FIN
69 -- -----------------------------------------------------
70 procedure UPDATE_ACTIVITY_DATES_FIN (p_worker_id in number) is
71
72 l_process varchar2(30);
73 l_extraction_type varchar2(30);
74
75 begin
76
77 l_process := PJI_RM_SUM_MAIN.g_process || p_worker_id;
78
79 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_BKLG.UPDATE_ACTIVITY_DATES_FIN(p_worker_id);')) then
80 return;
81 end if;
82
83 /* l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
84 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
85
86 -- Update the table pji_pji_proj_batch_map with the
87 -- minimum activity dates for each project in the current batch
88 UPDATE /*+ index(map, PJI_PJI_PROJ_BATCH_MAP_N1) *
89 pji_pji_proj_batch_map map
90 SET ( map.ACTIVITY_MIN_GL_DATE
91 , map.ACTIVITY_MIN_PA_DATE) =
92 (select scope.ACTIVITY_MIN_GL_DATE
93 , scope.ACTIVITY_MIN_PA_DATE
94 from PJI_FM_RMAP_FIN scope
95 where scope.worker_id = p_worker_id
96 and scope.MAP_ROWID = map.rowid
97 )
98 WHERE 1 = 1
99 AND map.WORKER_ID = p_worker_id
100 AND map.PROJECT_ID in (select scope2.PROJECT_ID
101 from PJI_FM_RMAP_FIN scope2
102 where scope2.worker_id = p_worker_id
103 and ((l_extraction_type <> 'INCREMENTAL')
104 or
105 scope2.REVENUE <>0
106 )
107 )
108 ;Commented for bug 13011859 */
109
110 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_BKLG.UPDATE_ACTIVITY_DATES_FIN(p_worker_id);');
111
112 commit;
113
114 end UPDATE_ACTIVITY_DATES_FIN;
115
116
117 -- -----------------------------------------------------
118 -- procedure ROWID_ACTIVITY_DATES_ACT
119 -- -----------------------------------------------------
120 procedure ROWID_ACTIVITY_DATES_ACT (p_worker_id in number) is
121
122 l_process varchar2(30);
123
124 begin
125
126 l_process := PJI_RM_SUM_MAIN.g_process || p_worker_id;
127
128 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_BKLG.ROWID_ACTIVITY_DATES_ACT(p_worker_id);')) then
129 return;
130 end if;
131
132 -- Get minimum funding dates for each project in the current batch
133 /* insert /*+ append * into PJI_FM_RMAP_ACT
134 (
135 WORKER_ID
136 , MAP_ROWID
137 , PROJECT_ID
138 , FUNDING_MIN_DATE
139 , FUNDING
140 )
141 select /* ordered
142 full(act5) use_hash(act5) parallel(act5)
143 full(map) use_hash(map) parallel(map)
144 *
145 p_worker_id
146 , map.rowid
147 , map.project_id
148 -- temptemp should consider GL and PA seperately
149 , to_date(min(act5.TIME_ID), 'J')
150 -- , to_date(min(LEAST(act5.GL_TIME_ID,act5.PA_TIME_ID)),'J')
151 , sum(abs(act5.POU_FUNDING))
152 from PJI_FM_AGGR_ACT5 act5
153 , pji_pji_proj_batch_map map
154 where 1 = 1
155 and act5.worker_id = p_worker_id
156 and act5.project_id = map.project_id
157 and map.worker_id = p_worker_id
158 group by
159 map.rowid
160 , map.project_id
161 ;Commented for bug 13011859 */
162
163 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_BKLG.ROWID_ACTIVITY_DATES_ACT(p_worker_id);');
164
165 commit;
166
167 end ROWID_ACTIVITY_DATES_ACT;
168
169
170 -- -----------------------------------------------------
171 -- procedure UPDATE_ACTIVITY_DATES_ACT
172 -- -----------------------------------------------------
173 procedure UPDATE_ACTIVITY_DATES_ACT (p_worker_id in number) is
174
175 l_process varchar2(30);
176 l_extraction_type varchar2(30);
177
178 begin
179
180 l_process := PJI_RM_SUM_MAIN.g_process || p_worker_id;
181
182 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_FM_SUM_BKLG.UPDATE_ACTIVITY_DATES_ACT(p_worker_id);')) then
183 return;
184 end if;
185
186 /* l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
187 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
188
189 -- Update the table pji_pji_proj_batch_map with the
190 -- minimum funding dates for each project in the current batch
191 UPDATE /*+ index(map, PJI_PJI_PROJ_BATCH_MAP_N1) *
192 pji_pji_proj_batch_map map
193 SET ( map.FUNDING_MIN_DATE ) =
194 (select scope.FUNDING_MIN_DATE
195 from PJI_FM_RMAP_ACT scope
196 where scope.worker_id = p_worker_id
197 and scope.MAP_ROWID = map.rowid
198 )
199 WHERE 1 = 1
200 AND map.WORKER_ID = p_worker_id
201 AND map.PROJECT_ID in (select scope2.PROJECT_ID
202 from PJI_FM_RMAP_ACT scope2
203 where scope2.worker_id = p_worker_id
204 and ((l_extraction_type <> 'INCREMENTAL')
205 or
206 scope2.FUNDING <>0
207 )
208 )
209 ;Commented for bug 13011859 */
210
211 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_FM_SUM_BKLG.UPDATE_ACTIVITY_DATES_ACT(p_worker_id);');
212
213 commit;
214
215 end UPDATE_ACTIVITY_DATES_ACT;
216
217
218 -- -----------------------------------------------------
219 -- procedure SCOPE_PROJECTS_BKLG
220 -- -----------------------------------------------------
221 procedure SCOPE_PROJECTS_BKLG (p_worker_id in number) is
222
223 l_process varchar2(30);
224 l_schema varchar2(30);
225 l_extraction_type varchar2(30);
226
227 begin
228
229 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
230
231 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
232 ( l_process, 'PJI_FM_SUM_BKLG.SCOPE_PROJECTS_BKLG(p_worker_id);'
233 )) then
234 return;
235 end if;
236
237 /* l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
238 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
239
240 UPDATE PJI_PJI_PROJ_BATCH_MAP upd
241 set upd.BACKLOG_EXTRACTION_STATUS=null
242 where upd.project_id in
243 (
244 select /*+ ORDERED
245 full(fin2)
246 *
247 distinct
248 map.project_id project_id
249 From
250 PJI_PJI_PROJ_BATCH_MAP map
251 , PJI_FM_AGGR_ACT3 act3
252 Where 1=1
253 and map.worker_id = p_worker_id
254 and map.project_id = act3.project_id
255 and (
256 (abs(nvl(act3.initial_funding_amount, 0))
257 +abs(nvl(act3.additional_funding_amount, 0))
258 +abs(nvl(act3.cancelled_funding_amount, 0))
259 +abs(nvl(act3.funding_adjustment_amount, 0))
260 ) > 0
261 OR
262 abs(act3.revenue) > 0
263 OR
264 nvl(map.OLD_CLOSED_DATE,sysdate) <> nvl(map.NEW_CLOSED_DATE,sysdate)
265 OR
266 l_extraction_type <> 'INCREMENTAL'
267 )
268 )
269 ;Commented for bug 13011859 */
270
271 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
272 (l_process, 'PJI_FM_SUM_BKLG.SCOPE_PROJECTS_BKLG(p_worker_id);');
273
274 commit;
275
276 end SCOPE_PROJECTS_BKLG;
277
278
279 -- -----------------------------------------------------
280 -- procedure CLEANUP_INT_TABLE
281 -- -----------------------------------------------------
282 procedure CLEANUP_INT_TABLE (p_worker_id in number) is
283
284 l_process varchar2(30);
285 l_schema varchar2(30);
286
287 begin
288
289 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
290
291 /* PJI_FM_DEBUG.CLEANUP_HOOK(l_process);
292
293 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
294 (
295 l_process,
296 'PJI_FM_SUM_BKLG.CLEANUP_INT_TABLE(p_worker_id);'
297 )) then
298 return;
299 end if;
300
301 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
302
303 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema ,
304 'PJI_FM_AGGR_ACT3',
305 'NORMAL',
306 null);Commented for bug 13011859 */
307
308 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
309 (
310 l_process,
311 'PJI_FM_SUM_BKLG.CLEANUP_INT_TABLE(p_worker_id);'
312 );
313
314 commit;
315
316 end;
317
318
319 -- -----------------------------------------------------
320 -- Procedure Insert_Backlog_Updates_pvt
321 -- -----------------------------------------------------
322 Procedure Insert_Backlog_Updates_pvt
323 ( p_worker_id IN number
324 , p_project_id IN number
325 , p_time_id IN number
326 , p_curr_record_type_id IN number
327 , p_currency_code IN varchar2
328 , p_gl_calendar_id IN number
329 , p_pa_calendar_id IN number
330 , p_drmt_bklg IN number
331 , p_strt_bklg IN number
332 , p_lost_bklg IN number
333 , p_actv_bklg IN number
334 , p_risky_rev IN number
335 , p_project_org_id IN number
336 , p_project_organization_id IN number
337 , p_calendar_type IN varchar2
338 , p_ex_drmt_bklg IN number
339 , p_ex_strt_bklg IN number
340 , p_ex_lost_bklg IN number
341 , p_ex_actv_bklg IN number
342 , p_ex_risky_rev IN number
343 ) is
344 Begin
345 null;
346 /* INSERT INTO PJI_FM_AGGR_ACT3(
347 WORKER_ID
348 , PROJECT_ID
349 , PROJECT_ORG_ID
350 , PROJECT_ORGANIZATION_ID
351 , TIME_ID
352 , PERIOD_TYPE_ID
353 , CALENDAR_TYPE
354 , GL_CALENDAR_ID
355 , PA_CALENDAR_ID
356 , CURR_RECORD_TYPE_ID
357 , CURRENCY_CODE
358 , REVENUE
359 , FUNDING
360 , INITIAL_FUNDING_AMOUNT
361 , INITIAL_FUNDING_COUNT
362 , ADDITIONAL_FUNDING_AMOUNT
363 , ADDITIONAL_FUNDING_COUNT
364 , CANCELLED_FUNDING_AMOUNT
365 , CANCELLED_FUNDING_COUNT
366 , FUNDING_ADJUSTMENT_AMOUNT
367 , FUNDING_ADJUSTMENT_COUNT
368 , REVENUE_WRITEOFF
369 , AR_INVOICE_AMOUNT
370 , AR_INVOICE_COUNT
371 , AR_CASH_APPLIED_AMOUNT
372 , AR_CASH_APPLIED_COUNT
373 , AR_INVOICE_WRITEOFF_AMOUNT
374 , AR_INVOICE_WRITEOFF_COUNT
375 , AR_CREDIT_MEMO_AMOUNT
376 , AR_CREDIT_MEMO_COUNT
377 , UNBILLED_RECEIVABLES
378 , UNEARNED_REVENUE
379 , AR_UNAPPR_INVOICE_AMOUNT
380 , AR_UNAPPR_INVOICE_COUNT
381 , AR_APPR_INVOICE_AMOUNT
385 , AR_AMOUNT_OVERDUE
382 , AR_APPR_INVOICE_COUNT
383 , AR_AMOUNT_DUE
384 , AR_COUNT_DUE
386 , AR_COUNT_OVERDUE
387 , DORMANT_BACKLOG_INACTIV
388 , DORMANT_BACKLOG_START
389 , LOST_BACKLOG
390 , ACTIVE_BACKLOG
391 , REVENUE_AT_RISK
392 )
393 SELECT
394 1 WORKER_ID
395 , p_project_id PROJECT_ID
396 , p_project_org_id PROJECT_ORG_ID
397 , p_project_organization_id PROJECT_ORGANIZATION_ID
398 , p_time_id TIME_ID
399 , 1 PERIOD_TYPE_ID
400 , p_calendar_type CALENDAR_TYPE
401 , p_gl_calendar_id GL_CALENDAR_ID
402 , p_pa_calendar_id PA_CALENDAR_ID
403 , p_curr_record_type_id CURR_RECORD_TYPE_ID
404 , p_currency_code CURRENCY_CODE
405 , to_number(null) REVENUE
406 , to_number(null) FUNDING
407 , to_number(null) INITIAL_FUNDING_AMOUNT
408 , to_number(null) INITIAL_FUNDING_COUNT
409 , to_number(null) ADDITIONAL_FUNDING_AMOUNT
410 , to_number(null) ADDITIONAL_FUNDING_COUNT
411 , to_number(null) CANCELLED_FUNDING_AMOUNT
412 , to_number(null) CANCELLED_FUNDING_COUNT
413 , to_number(null) FUNDING_ADJUSTMENT_AMOUNT
414 , to_number(null) FUNDING_ADJUSTMENT_COUNT
415 , to_number(null) REVENUE_WRITEOFF
416 , to_number(null) AR_INVOICE_AMOUNT
417 , to_number(null) AR_INVOICE_COUNT
418 , to_number(null) AR_CASH_APPLIED_AMOUNT
419 , to_number(null) AR_CASH_APPLIED_COUNT
420 , to_number(null) AR_INVOICE_WRITEOFF_AMOUNT
421 , to_number(null) AR_INVOICE_WRITEOFF_COUNT
422 , to_number(null) AR_CREDIT_MEMO_AMOUNT
423 , to_number(null) AR_CREDIT_MEMO_COUNT
424 , to_number(null) UNBILLED_RECEIVABLES
425 , to_number(null) UNEARNED_REVENUE
426 , to_number(null) AR_UNAPPR_INVOICE_AMOUNT
427 , to_number(null) AR_UNAPPR_INVOICE_COUNT
428 , to_number(null) AR_APPR_INVOICE_AMOUNT
429 , to_number(null) AR_APPR_INVOICE_COUNT
430 , to_number(null) AR_AMOUNT_DUE
431 , to_number(null) AR_COUNT_DUE
432 , to_number(null) AR_AMOUNT_OVERDUE
433 , to_number(null) AR_COUNT_OVERDUE
434 , nvl(-p_ex_drmt_bklg,0) + p_drmt_bklg DORMANT_BACKLOG_INACTIV
435 , nvl(-p_ex_strt_bklg,0) + p_strt_bklg DORMANT_BACKLOG_START
436 , nvl(-p_ex_lost_bklg,0) + p_lost_bklg LOST_BACKLOG
437 , nvl(-p_ex_actv_bklg,0) + p_actv_bklg ACTIVE_BACKLOG
438 , nvl(-p_ex_risky_rev,0) + p_risky_rev REVENUE_AT_RISK
439 FROM dual
440 ;Commented for bug 13011859 */
441 End Insert_Backlog_Updates_pvt;
442
443
444 -- -----------------------------------------------------
445 -- Procedure Backlog_Bucketing_pvt
446 -- -----------------------------------------------------
447 Procedure Backlog_Bucketing_pvt
448 ( p_worker_id IN number
449 , p_project_id IN number
450 , p_curr_record_type_id IN number
451 , p_currency_code IN varchar2
452 , p_gl_calendar_id IN number
453 , p_pa_calendar_id IN number
454 , p_itd_rev IN OUT nocopy number
455 , p_itd_fnd IN OUT nocopy number
456 , p_curr_rev IN number
457 , p_curr_fnd IN number
458 , p_curr_date IN number
459 , p_scope_min_date IN number
460 , p_min_julian_date IN number
461 , p_close_date IN number
462 , p_dbklg_days IN number
463 , p_record_type IN varchar2
464 , p_drmt_bklg IN OUT nocopy number
465 , p_strt_bklg IN OUT nocopy number
466 , p_lost_bklg IN OUT nocopy number
467 , p_actv_bklg IN OUT nocopy number
468 , p_risky_rev IN OUT nocopy number
469 , p_curr_status IN OUT nocopy varchar2
470 , p_project_org_id IN number
471 , p_project_organization_id IN number
472 , p_calendar_type IN varchar2
473 , p_ex_drmt_bklg IN OUT nocopy number
474 , p_ex_strt_bklg IN OUT nocopy number
475 , p_ex_lost_bklg IN OUT nocopy number
476 , p_ex_actv_bklg IN OUT nocopy number
477 , p_ex_risky_rev IN OUT nocopy number
478 ) is
479
480 l_before_status VARCHAR2(30);
481
482 Begin
483
484 l_before_status := p_curr_status;
485
486
487 If ( p_curr_date = -1 ) Then
488 if ( sign(abs(p_ex_risky_rev)) = 1) then
489 p_drmt_bklg := 0;
490 p_strt_bklg := 0;
491 p_lost_bklg := 0;
495 elsif ( sign(abs(p_ex_lost_bklg)) = 1) then
492 p_actv_bklg := 0;
493 p_risky_rev := p_ex_risky_rev;
494 p_curr_status := 'REV_AT_RISK';
496 p_drmt_bklg := 0;
497 p_strt_bklg := 0;
498 p_lost_bklg := p_ex_lost_bklg;
499 p_actv_bklg := 0;
500 p_risky_rev := 0;
501 p_curr_status := 'LOST_BACKLOG';
502 elsif ( sign(abs(p_ex_strt_bklg)) = 1) then
503 p_drmt_bklg := 0;
504 p_strt_bklg := p_ex_strt_bklg;
505 p_lost_bklg := 0;
506 p_actv_bklg := 0;
507 p_risky_rev := 0;
508 p_curr_status := 'BACKLOG_NOT_STARTED';
509 elsif ( sign(abs(p_ex_drmt_bklg)) = 1) then
510 p_drmt_bklg := p_ex_drmt_bklg;
511 p_strt_bklg := 0;
512 p_lost_bklg := 0;
513 p_actv_bklg := 0;
514 p_risky_rev := 0;
515 p_curr_status := 'DORMANT_BACKLOG';
516 elsif ( sign(abs(p_ex_actv_bklg)) = 1) then
517 p_drmt_bklg := 0;
518 p_strt_bklg := 0;
519 p_lost_bklg := 0;
520 p_actv_bklg := p_ex_actv_bklg;
521 p_risky_rev := 0;
522 p_curr_status := 'ACTIVE_BACKLOG';
523 else
524 p_drmt_bklg := 0;
525 p_strt_bklg := 0;
526 p_lost_bklg := 0;
527 p_actv_bklg := 0;
528 p_risky_rev := 0;
529 p_curr_status := 'BACKLOG_NOT_STARTED';
530 end if;
531 Else -- ( when p_curr_date <> -1 )
532
533 IF (p_itd_fnd+p_curr_fnd-p_itd_rev-p_curr_rev) < 0 THEN
534
535 iF p_record_type <> 'V' then
536 p_curr_status := 'REV_AT_RISK';
537 p_drmt_bklg := -p_drmt_bklg;
538 p_strt_bklg := -p_strt_bklg;
539 p_lost_bklg := -p_lost_bklg;
540 p_actv_bklg := -p_actv_bklg;
541 If l_before_status = p_curr_status Then
542 p_risky_rev := (p_curr_rev-p_curr_fnd);
543 Else
544 p_risky_rev := (p_itd_rev+p_curr_rev-p_itd_fnd-p_curr_fnd);
545 end If;
546 enD iF;
547
548 ELSIF ( p_close_date <= p_curr_date ) THEN
549
550 iF p_record_type <> 'V' then
551 p_curr_status := 'LOST_BACKLOG';
552 p_drmt_bklg := -p_drmt_bklg;
553 p_strt_bklg := -p_strt_bklg;
554 If l_before_status = p_curr_status Then
555 p_lost_bklg := (p_curr_fnd-p_curr_rev);
556 Else
557 p_lost_bklg := (p_itd_fnd+p_curr_fnd-p_itd_rev-p_curr_rev);
558 end If;
559 p_actv_bklg := -p_actv_bklg;
560 p_risky_rev := -p_risky_rev;
561 enD iF;
562
563 ELSIF (abs(p_itd_rev)+abs(p_curr_rev)) = 0 THEN
564
565 iF p_record_type <> 'V' then
566 p_curr_status := 'BACKLOG_NOT_STARTED';
567 p_drmt_bklg := -p_drmt_bklg;
568 If l_before_status = p_curr_status Then
569 p_strt_bklg := p_curr_fnd;
570 Else
571 p_strt_bklg := (p_itd_fnd+p_curr_fnd);
572 end If;
573 p_lost_bklg := -p_lost_bklg;
574 p_actv_bklg := -p_actv_bklg;
575 p_risky_rev := -p_risky_rev;
576 enD iF;
577
578 ELSIF ( (p_record_type = 'V')
579 or
580 (p_record_type = 'N'
581 and l_before_status = 'DORMANT_BACKLOG'
582 and p_curr_rev = 0)
583 or
584 (g_last_activity_date+p_dbklg_days+1<p_curr_date
585 and p_curr_rev = 0 ) -- corner case of funding after before 3 buckets
586 ) THEN
587
588 p_curr_status := 'DORMANT_BACKLOG';
589 If l_before_status = p_curr_status Then
590 p_drmt_bklg := (p_curr_fnd-p_curr_rev);
591 Else
592 p_drmt_bklg := (p_itd_fnd+p_curr_fnd-p_itd_rev-p_curr_rev);
593 end If;
594 p_strt_bklg := -p_strt_bklg;
595 p_lost_bklg := -p_lost_bklg;
596 p_actv_bklg := -p_actv_bklg;
597 p_risky_rev := -p_risky_rev;
598
599 ELSE
600
601 p_curr_status := 'ACTIVE_BACKLOG';
602 p_drmt_bklg := -p_drmt_bklg;
603 p_strt_bklg := -p_strt_bklg;
604 p_lost_bklg := -p_lost_bklg;
605 If l_before_status = p_curr_status Then
606 p_actv_bklg := (p_curr_fnd-p_curr_rev);
607 Else
608 p_actv_bklg := (p_itd_fnd+p_curr_fnd-p_itd_rev-p_curr_rev);
609 end If;
610 p_risky_rev := -p_risky_rev;
611 END IF;
612
613 End If; -- ( p_curr_date = -1 )
614
615 p_itd_rev := p_itd_rev + p_curr_rev;
616 p_itd_fnd := p_itd_fnd + p_curr_fnd;
617
618
619 -- Need to insert a record into the tmp table only for those days where :
620 -- the project is closed
624 -- or
621 -- or
622 -- there is an existing record in the fact table
623 -- and the date is after the l_min_julian_date
625 -- there is a change in status (virtual day)
626 -- and the date is within scope
627 -- or
628 -- (to take care of the closing date of the previous run, if present)
629 -- there is an existing record in the fact table
630 -- and the date is before update scope
631 -- there are no relevant txns on the date
632
633 IF ( (p_record_type = 'C')
634 OR
635 (p_record_type = 'N'
636 and p_curr_date >= p_min_julian_date)
637 OR
638 (p_record_type = 'V'
639 and p_curr_date >= p_min_julian_date
640 and l_before_status = 'ACTIVE_BACKLOG'
641 and p_curr_status = 'DORMANT_BACKLOG')
642 OR
643 (p_record_type = 'N'
644 and p_curr_date > p_scope_min_date
645 and p_curr_date < p_min_julian_date
646 and p_curr_rev = 0
647 and p_curr_fnd = 0)
648 ) THEN
649 Insert_Backlog_Updates_pvt
650 ( p_worker_id => p_worker_id
651 , p_project_id => p_project_id
652 , p_time_id => p_curr_date
653 , p_curr_record_type_id => p_curr_record_type_id
654 , p_currency_code => p_currency_code
655 , p_gl_calendar_id => p_gl_calendar_id
656 , p_pa_calendar_id => p_pa_calendar_id
657 , p_drmt_bklg => p_drmt_bklg
658 , p_strt_bklg => p_strt_bklg
659 , p_lost_bklg => p_lost_bklg
660 , p_actv_bklg => p_actv_bklg
661 , p_risky_rev => p_risky_rev
662 , p_project_org_id => p_project_org_id
663 , p_project_organization_id => p_project_organization_id
664 , p_calendar_type => p_calendar_type
665 , p_ex_drmt_bklg => p_ex_drmt_bklg
666 , p_ex_strt_bklg => p_ex_strt_bklg
667 , p_ex_lost_bklg => p_ex_lost_bklg
668 , p_ex_actv_bklg => p_ex_actv_bklg
669 , p_ex_risky_rev => p_ex_risky_rev
670 );
671 END IF;
672
673
674 -- Resetting the values so that only one of the 5 has a +ve itd value
675 IF (p_curr_status = 'REV_AT_RISK') THEN
676 p_risky_rev := (p_itd_rev-p_itd_fnd);
677 p_lost_bklg := 0;
678 p_strt_bklg := 0;
679 p_drmt_bklg := 0;
680 p_actv_bklg := 0;
681 ELSIF (p_curr_status = 'LOST_BACKLOG') THEN
682 p_risky_rev := 0;
683 p_lost_bklg := (p_itd_fnd-p_itd_rev);
684 p_strt_bklg := 0;
685 p_drmt_bklg := 0;
686 p_actv_bklg := 0;
687 ELSIF (p_curr_status = 'BACKLOG_NOT_STARTED') THEN
688 p_risky_rev := 0;
689 p_lost_bklg := 0;
690 p_strt_bklg := (p_itd_fnd);
691 p_drmt_bklg := 0;
692 p_actv_bklg := 0;
693 ELSIF (p_curr_status = 'DORMANT_BACKLOG') THEN
694 p_risky_rev := 0;
695 p_lost_bklg := 0;
696 p_strt_bklg := 0;
697 p_drmt_bklg := (p_itd_fnd-p_itd_rev);
698 p_actv_bklg := 0;
699 ELSIF (p_curr_status = 'ACTIVE_BACKLOG') THEN
700 p_risky_rev := 0;
701 p_lost_bklg := 0;
702 p_strt_bklg := 0;
703 p_drmt_bklg := 0;
704 p_actv_bklg := (p_itd_fnd-p_itd_rev);
705 ELSE null;
706 END IF;
707
708
709 End Backlog_Bucketing_pvt;
710
711
712 -- -----------------------------------------------------
713 -- Procedure Calculate_Drmt_Bklg
714 -- -----------------------------------------------------
715 Procedure Calculate_Drmt_Bklg(
716 p_worker_id IN NUMBER
717 , p_project_id IN NUMBER
718 , p_project_org_id IN NUMBER
719 , p_project_organization_id IN NUMBER
720 , p_calendar_id IN NUMBER DEFAULT NULL
721 , p_gl_calendar_id IN NUMBER
722 , p_pa_calendar_id IN NUMBER
723 , p_dbklg_days IN NUMBER
724 , p_min_julian_date IN NUMBER
725 , p_close_julian_date IN NUMBER
726 )
727 IS
728 -- Local Variable Declaration
729 l_worker_id NUMBER := p_worker_id;
730 l_project_id NUMBER := p_project_id;
731 l_project_org_id NUMBER := p_project_org_id;
732 l_project_organization_id NUMBER := p_project_organization_id;
733 l_calendar_id NUMBER := p_calendar_id;
734 l_gl_calendar_id NUMBER := p_gl_calendar_id;
735 l_pa_calendar_id NUMBER := p_pa_calendar_id;
739 l_daily_calendar_type VARCHAR2(1) := NULL;
736 l_dbklg_days NUMBER := p_dbklg_days;
737 l_min_julian_date NUMBER := p_min_julian_date;
738 l_close_julian_date NUMBER := p_close_julian_date;
740 l_aggr_calendar_type VARCHAR2(1) := NULL;
741 l_period_type VARCHAR2(3) := NULL;
742 l_curr_record_type_id NUMBER := NULL;
743 l_txn_currency_flag VARCHAR2(1);
744 l_g2_currency_flag VARCHAR2(1);
745 l_g1_currency_code VARCHAR2(30);
746 l_g2_currency_code VARCHAR2(30);
747
748 l_scope_min_date NUMBER;
749 l_prev_activity_date NUMBER;
750 l_itd_rev NUMBER;
751 l_itd_fnd NUMBER;
752 l_curr_rev NUMBER;
753 l_curr_fnd NUMBER;
754 l_drmt_bklg NUMBER;
755 l_strt_bklg NUMBER;
756 l_lost_bklg NUMBER;
757 l_actv_bklg NUMBER;
758 l_risky_rev NUMBER;
759 l_curr_status VARCHAR2(30);
760 l_prev_status VARCHAR2(30);
761 l_rowcount NUMBER;
762 l_temp_date NUMBER;
763 l_last_activity_date NUMBER;
764 l_last_date NUMBER;
765 l_prev_date NUMBER;
766 l_first_currency NUMBER;
767 l_bkt_dual NUMBER :=0;
768 l_called_before VARCHAR2(1) :='N';
769 l_curr_date NUMBER;
770 l_max_date DATE := PJI_RM_SUM_MAIN.g_max_date;
771 l_record_type VARCHAR2(1);
772 l_ex_drmt_bklg NUMBER;
773 l_ex_strt_bklg NUMBER;
774 l_ex_lost_bklg NUMBER;
775 l_ex_actv_bklg NUMBER;
776 l_ex_risky_rev NUMBER;
777
778 /* Cursor csr_bklg is
779 SELECT
780 WORKER_ID
781 , PROJECT_ID
782 , PROJECT_ORG_ID
783 , PROJECT_ORGANIZATION_ID
784 , TIME_ID
785 , PERIOD_TYPE_ID
786 , CALENDAR_TYPE
787 , CURR_RECORD_TYPE_ID
788 , CURRENCY_CODE
789 , nvl(REVENUE, 0) REVENUE
790 , nvl(FUNDING, 0) FUNDING
791 , nvl(DORMANT_BACKLOG_INACTIV, 0) DORMANT_BACKLOG_INACTIV
792 , nvl(DORMANT_BACKLOG_START, 0) DORMANT_BACKLOG_START
793 , nvl(LOST_BACKLOG, 0) LOST_BACKLOG
794 , nvl(ACTIVE_BACKLOG, 0) ACTIVE_BACKLOG
795 , nvl(REVENUE_AT_RISK, 0) REVENUE_AT_RISK
796 FROM (
797 Select
798 -l_worker_id worker_id
799 , fct.project_id project_id
800 , fct.PROJECT_ORG_ID PROJECT_ORG_ID
801 , fct.PROJECT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID
802 , fct.time_id time_id
803 , 1 period_type_id
804 , fct.CALENDAR_TYPE CALENDAR_TYPE
805 , bitand(fct.curr_record_type_id, 247) curr_record_type_id
806 , fct.currency_code currency_code
807 , sum(fct.revenue) REVENUE
808 , sum(nvl(fct.initial_funding_amount, 0) +
809 nvl(fct.additional_funding_amount, 0) +
810 nvl(fct.cancelled_funding_amount, 0) +
811 nvl(fct.funding_adjustment_amount, 0)) FUNDING
812 , sum(DORMANT_BACKLOG_INACTIV) DORMANT_BACKLOG_INACTIV
813 , sum(DORMANT_BACKLOG_START) DORMANT_BACKLOG_START
814 , sum(LOST_BACKLOG) LOST_BACKLOG
815 , sum(ACTIVE_BACKLOG) ACTIVE_BACKLOG
816 , sum(REVENUE_AT_RISK) REVENUE_AT_RISK
817 FROM pji_ac_proj_f fct
818 WHERE 1 = 1
819 And fct.project_id = l_project_id
820 And fct.calendar_type = l_daily_calendar_type
821 And fct.time_id >= l_min_julian_date - l_dbklg_days
822 And fct.curr_record_type_id not in (8, 256)
823 And fct.period_type_id = 1
824 Group By
825 fct.project_id
826 , fct.PROJECT_ORG_ID
827 , fct.PROJECT_ORGANIZATION_ID
828 , fct.time_id
829 , fct.CALENDAR_TYPE
830 , bitand(fct.curr_record_type_id, 247)
831 , fct.currency_code
832 union all
833 Select
834 -l_worker_id worker_id
835 , fct.project_id project_id
836 , to_number(l_project_org_id) project_org_id
837 , to_number(l_project_organization_id) project_organization_id
838 , -1 time_id
839 , 1 period_type_id
840 , to_char(l_daily_calendar_type) calendar_type
841 , bitand(fct.curr_record_type_id, 247) curr_record_type_id
842 , fct.currency_code currency_code
843 , sum(fct.revenue) REVENUE
844 , sum(nvl(fct.initial_funding_amount, 0) +
845 nvl(fct.additional_funding_amount, 0) +
846 nvl(fct.cancelled_funding_amount, 0) +
847 nvl(fct.funding_adjustment_amount, 0)) FUNDING
848 , sum(fct.DORMANT_BACKLOG_INACTIV) DORMANT_BACKLOG_INACTIV
849 , sum(fct.DORMANT_BACKLOG_START) DORMANT_BACKLOG_START
850 , sum(fct.LOST_BACKLOG) LOST_BACKLOG
854 , pji_ac_proj_f fct
851 , sum(fct.ACTIVE_BACKLOG) ACTIVE_BACKLOG
852 , sum(fct.REVENUE_AT_RISK) REVENUE_AT_RISK
853 From PJI_PMV_ITD_DIM_TMP time
855 Where fct.time_id = time.id
856 And fct.project_id = l_project_id
857 And (fct.calendar_type = l_aggr_calendar_type or fct.calendar_type = l_daily_calendar_type)
858 And fct.curr_record_type_id not in (8, 256)
859 Group By fct.project_id
860 , bitand(fct.curr_record_type_id, 247)
861 , fct.currency_code
862 union all
863 Select
864 -l_worker_id as worker_id
865 , to_number(l_project_id) as project_id
866 , to_number(l_project_org_id) as project_org_id
867 , to_number(l_project_organization_id) as project_organization_id
868 , to_number(to_char(l_max_date-1,'J')) as time_id
869 , 1 as period_type_id
870 , to_char(l_daily_calendar_type) as calendar_type
871 , 1 as curr_record_type_id
872 , l_g1_currency_code as currency_code
873 , to_number(null) as REVENUE
874 , to_number(null) as FUNDING
875 , to_number(null) as DORMANT_BACKLOG_INACTIV
876 , to_number(null) as DORMANT_BACKLOG_START
877 , to_number(null) as LOST_BACKLOG
878 , to_number(null) as ACTIVE_BACKLOG
879 , to_number(null) as REVENUE_AT_RISK
880 From Dual
881 union all
882 Select
883 -l_worker_id as worker_id
884 , to_number(l_project_id) as project_id
885 , to_number(l_project_org_id) as project_org_id
886 , to_number(l_project_organization_id) as project_organization_id
887 , to_number(to_char(l_max_date-1,'J')) as time_id
888 , 1 as period_type_id
889 , to_char(l_daily_calendar_type) as calendar_type
890 , 2 as curr_record_type_id
891 , l_g2_currency_code as currency_code
892 , to_number(null) as REVENUE
893 , to_number(null) as FUNDING
894 , to_number(null) as DORMANT_BACKLOG_INACTIV
895 , to_number(null) as DORMANT_BACKLOG_START
896 , to_number(null) as LOST_BACKLOG
897 , to_number(null) as ACTIVE_BACKLOG
898 , to_number(null) as REVENUE_AT_RISK
899 From Dual
900 Where l_g2_currency_flag = 'Y'
901 union all
902 Select
903 -l_worker_id as worker_id
904 , to_number(l_project_id) as project_id
905 , to_number(l_project_org_id) as project_org_id
906 , to_number(l_project_organization_id) as project_organization_id
907 , to_number(to_char(l_max_date-1,'J')) as time_id
908 , 1 as period_type_id
909 , to_char(l_daily_calendar_type) as calendar_type
910 , 4 as curr_record_type_id
911 , pf_currency_code as currency_code
912 , to_number(null) as REVENUE
913 , to_number(null) as FUNDING
914 , to_number(null) as DORMANT_BACKLOG_INACTIV
915 , to_number(null) as DORMANT_BACKLOG_START
916 , to_number(null) as LOST_BACKLOG
917 , to_number(null) as ACTIVE_BACKLOG
918 , to_number(null) as REVENUE_AT_RISK
919 From PJI_ORG_EXTR_INFO
920 Where nvl(ORG_ID, -1) = nvl(l_project_org_id, -1)
921 )
922 ORDER BY
923 PROJECT_ID
924 , CURR_RECORD_TYPE_ID
925 , CURRENCY_CODE
926 , TIME_ID
927 ;
928
929 rec_bklg csr_bklg%ROWTYPE;Commented for bug 13011859 */
930
931
932
933 BEGIN
934
935 select
936 TXN_CURR_FLAG,
937 GLOBAL_CURR2_FLAG
938 into
939 l_txn_currency_flag,
940 l_g2_currency_flag
941 from
942 PJI_SYSTEM_SETTINGS;
943
944 l_g1_currency_code := PJI_UTILS.GET_GLOBAL_PRIMARY_CURRENCY;
945 l_g2_currency_code := PJI_UTILS.GET_GLOBAL_SECONDARY_CURRENCY;
946
947 IF l_calendar_id IS NULL THEN
948 l_daily_calendar_type := 'C';
949 l_aggr_calendar_type := 'E';
950
951 l_period_type := 'ITD';
952 ELSE
953 l_daily_calendar_type := 'P';
954 l_aggr_calendar_type := 'P';
955
956 l_period_type := 'PA';
957 END IF;
958
959 /* DELETE FROM PJI_PMV_ITD_DIM_TMP;
960
961 begin
962 -- Using Enterprise Calendar, Build PJI_PMV_ITD_DIM_TMP for Subsequent ITD Insert SQL
963 PJI_PMV_ENGINE.Convert_ITD_NViewBY_AS_OF_DATE(
964 p_As_Of_Date => l_min_julian_date - l_dbklg_days - 1
965 , p_Period_Type => l_period_type
966 , p_Calendar_ID => l_calendar_id
967 );
968 exception when others then null;
969 end;Commented for bug 13011859 */
970
971
972 l_scope_min_date := l_min_julian_date - l_dbklg_days - 1;
973
974
975 -- Next we open the cursor for processing backlog
976
977 /* IF csr_bklg%ISOPEN THEN
978 CLOSE csr_bklg;
979 END IF;
983 For rec_bklg in csr_bklg LOOP
980
981 l_first_currency := 0;
982
984
985 l_bkt_dual := 0;
986
987 IF rec_bklg.time_id = -1 THEN
988
989 l_itd_rev := rec_bklg.revenue;
990 l_itd_fnd := rec_bklg.funding;
991 l_curr_rev := 0;
992 l_curr_fnd := 0;
993
994 l_drmt_bklg := 0;
995 l_strt_bklg := 0;
996 l_lost_bklg := 0;
997 l_actv_bklg := 0;
998 l_risky_rev := 0;
999
1000 l_curr_status := 'UNKNOWN';
1001
1002 l_first_currency := rec_bklg.curr_record_type_id;
1003 g_last_activity_date := l_scope_min_date;
1004 l_bkt_dual := 1;
1005 l_called_before := 'N';
1006
1007 ELSIF (rec_bklg.time_id = to_number(to_char(l_max_date-1,'j'))) THEN
1008 -- check dormancy for the last record
1009
1010 l_curr_rev := 0;
1011 l_curr_fnd := 0;
1012
1013 l_bkt_dual := 0;
1014
1015 ELSE
1016 l_bkt_dual := 0;
1017
1018 If (bitand(l_first_currency,
1019 rec_bklg.curr_record_type_id) = 0) Then
1020 -- corner case of no ITD record
1021 l_itd_rev := 0;
1022 l_itd_fnd := 0;
1023
1024 l_drmt_bklg := 0;
1025 l_strt_bklg := 0;
1026 l_lost_bklg := 0;
1027 l_actv_bklg := 0;
1028 l_risky_rev := 0;
1029
1030 l_curr_status := 'UNKNOWN';
1031 l_first_currency := rec_bklg.curr_record_type_id;
1032 g_last_activity_date := rec_bklg.time_id;
1033 l_bkt_dual := 1;
1034 l_called_before := 'N';
1035
1036 End If;
1037
1038 END IF; -- rec_bklg.time_id = -1
1039
1040
1041
1042 -- Next we call the backlog bucketing procedure in a loop thrice
1043 -- for each record in the cursor
1044 -- The first time for the project closed date if required
1045 -- The second time for a virtual date if required
1046 -- The third time for the normal date
1047
1048
1049 WHILE l_bkt_dual < 3 Loop
1050
1051 l_ex_drmt_bklg := rec_bklg.DORMANT_BACKLOG_INACTIV;
1052 l_ex_strt_bklg := rec_bklg.DORMANT_BACKLOG_START;
1053 l_ex_lost_bklg := rec_bklg.LOST_BACKLOG;
1054 l_ex_actv_bklg := rec_bklg.ACTIVE_BACKLOG;
1055 l_ex_risky_rev := rec_bklg.REVENUE_AT_RISK;
1056
1057
1058 IF ( l_bkt_dual < 1
1059 and g_last_activity_date < l_close_julian_date
1060 and l_close_julian_date <= rec_bklg.time_id
1061 and (rec_bklg.time_id <> to_number(to_char(l_max_date-1,'j'))
1062 or
1063 l_close_julian_date < (g_last_activity_date + l_dbklg_days + 1)
1064 )
1065 ) THEN -- the closed date case
1066
1067 l_record_type := 'C';
1068 l_curr_date := l_close_julian_date ;
1069 l_curr_rev := rec_bklg.revenue;
1070 l_curr_fnd := rec_bklg.funding;
1071
1072 l_bkt_dual := 1;
1073
1074 ELSIF ( l_bkt_dual < 2
1075 and (g_last_activity_date + l_dbklg_days + 1) <= rec_bklg.time_id
1076 and (g_last_activity_date + l_dbklg_days + 1) < l_close_julian_date
1077 and l_called_before = 'N' ) THEN -- the virtual case
1078
1079 l_record_type := 'V';
1080 l_curr_date := g_last_activity_date + l_dbklg_days + 1;
1081 l_curr_rev := 0;
1082 l_curr_fnd := 0;
1083 l_ex_drmt_bklg := 0;
1084 l_ex_strt_bklg := 0;
1085 l_ex_lost_bklg := 0;
1086 l_ex_actv_bklg := 0;
1087 l_ex_risky_rev := 0;
1088
1089 IF (rec_bklg.time_id <> to_number(to_char(l_max_date-1,'j'))) THEN
1090 l_bkt_dual :=2;
1091 ELSE -- the last record is virtual
1092 -- thus the normal case should not be called
1093 l_bkt_dual :=99;
1094 END IF;
1095
1096 l_called_before := 'Y';
1097
1098 ELSE -- the normal case
1099
1100 l_record_type := 'N';
1101 l_curr_date := rec_bklg.time_id;
1102 IF (rec_bklg.time_id = -1) THEN
1103 l_curr_rev := 0;
1104 l_curr_fnd := 0;
1105 ELSE
1106 l_curr_rev := rec_bklg.revenue;
1107 l_curr_fnd := rec_bklg.funding;
1108 END IF;
1109
1110 l_bkt_dual := 3;
1111 END IF;
1112
1113 IF (l_curr_date < to_number(to_char(l_max_date-1,'j'))) THEN
1114 Backlog_Bucketing_pvt
1115 ( p_worker_id => p_worker_id
1116 , p_project_id => p_project_id
1117 , p_curr_record_type_id => rec_bklg.curr_record_type_id
1118 , p_currency_code => rec_bklg.currency_code
1119 , p_gl_calendar_id => l_gl_calendar_id
1120 , p_pa_calendar_id => l_pa_calendar_id
1121 , p_itd_rev => l_itd_rev
1122 , p_itd_fnd => l_itd_fnd
1123 , p_curr_rev => l_curr_rev
1124 , p_curr_fnd => l_curr_fnd
1128 , p_close_date => l_close_julian_date
1125 , p_curr_date => l_curr_date
1126 , p_scope_min_date => l_scope_min_date
1127 , p_min_julian_date => l_min_julian_date
1129 , p_dbklg_days => l_dbklg_days
1130 , p_record_type => l_record_type
1131 , p_drmt_bklg => l_drmt_bklg
1132 , p_strt_bklg => l_strt_bklg
1133 , p_lost_bklg => l_lost_bklg
1134 , p_actv_bklg => l_actv_bklg
1135 , p_risky_rev => l_risky_rev
1136 , p_curr_status => l_curr_status
1137 , p_project_org_id => p_project_org_id
1138 , p_project_organization_id => p_project_organization_id
1139 , p_calendar_type => rec_bklg.calendar_type
1140 , p_ex_drmt_bklg => l_ex_drmt_bklg
1141 , p_ex_strt_bklg => l_ex_strt_bklg
1142 , p_ex_lost_bklg => l_ex_lost_bklg
1143 , p_ex_actv_bklg => l_ex_actv_bklg
1144 , p_ex_risky_rev => l_ex_risky_rev
1145 );
1146 END IF; -- (l_curr_date < to_number(to_char(l_max_date-1,'j')))
1147
1148 End Loop; -- for l_bkt_dual
1149
1150 if l_curr_rev <> 0 then
1151 g_last_activity_date := l_curr_date;
1152 l_called_before := 'N';
1153 end if;
1154 END LOOP; -- For rec_bklg in csr_bklgCommented for bug 13011859 */
1155
1156
1157 END Calculate_Drmt_Bklg;
1158
1159
1160
1161 -- -----------------------------------------------------
1162 -- Procedure Process_Drmt_Bklg
1163 -- -----------------------------------------------------
1164 Procedure Process_Drmt_Bklg(p_worker_id IN NUMBER)
1165 IS
1166
1167 -- Local Variable Declaration
1168 -- IN Variables
1169 l_worker_id NUMBER := NULL;
1170 l_process VARCHAR2(30) := NULL;
1171 l_project_id NUMBER := NULL;
1172 l_min_date DATE := NULL;
1173 l_close_date DATE := NULL;
1174 l_min_julian_date NUMBER := NULL;
1175 l_close_julian_date NUMBER := NULL;
1176 l_project_org_id NUMBER := NULL;
1177 l_project_organization_id NUMBER := NULL;
1178 l_calendar_id NUMBER := NULL;
1179 l_gl_calendar_id NUMBER := NULL;
1180 l_pa_calendar_id NUMBER := NULL;
1181 l_pa_calendar_flag VARCHAR2(1) := 'N';
1182 l_dbklg_days NUMBER := 0;
1183 l_max_date DATE := PJI_RM_SUM_MAIN.g_max_date;
1184 --temptemp needs to be PJI_PJI_LAST_EXTR_DATE
1185 last_extr_date DATE := to_date(PJI_UTILS.GET_PARAMETER('LAST_FM_EXTR_DATE'),'YYYY/MM/DD');
1186 l_commit_counter NUMBER := 0;
1187 l_dual_limit NUMBER := 0;
1188 l_dual NUMBER := 0;
1189 l_min_nocal_date DATE;
1190 l_min_cal_date DATE;
1191 l_prev_project NUMBER;
1192 l_rowcount number := 0;
1193 l_curr_project_id number := 0;
1194 l_parallel_processes number := null;
1195
1196 l_try_upd_again varchar2(1);
1197 l_count_for_upd number;
1198
1199
1200 BEGIN
1201
1202 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1203
1204 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1205 ( l_process, 'PJI_FM_SUM_BKLG.PROCESS_DRMT_BKLG(p_worker_id);')) then
1206 return;
1207 end if;
1208
1209 -- Assigning Values to the variables
1210
1211 l_worker_id := p_worker_id;
1212
1213 l_dbklg_days := to_number(PJI_UTILS.GET_SETUP_PARAMETER('DORMANT_BACKLOG_DAYS'));
1214
1215 l_pa_calendar_flag := PJI_UTILS.GET_SETUP_PARAMETER('PA_PERIOD_FLAG');
1216
1217 -- Code below has been added so that each programming unit
1218 -- handles the exception of the previous run
1219
1220 /* delete
1221 from PJI_FM_AGGR_ACT3
1222 where worker_id = 1
1223 and project_id in
1224 (select project_id
1225 from pji_pji_proj_batch_map
1226 where worker_id = 1
1227 and BACKLOG_EXTRACTION_STATUS = 'P'
1228 and PARALLEL_BACKLOG_WORKER_ID = p_worker_id
1229 )
1230 ;
1231
1232 Update pji_pji_proj_batch_map
1233 Set BACKLOG_EXTRACTION_STATUS = null
1234 , PARALLEL_BACKLOG_WORKER_ID = null
1235 Where WORKER_ID = 1
1236 And PARALLEL_BACKLOG_WORKER_ID = p_worker_id
1237 And BACKLOG_EXTRACTION_STATUS = 'P'
1238 ;Commented for bug 13011859 */
1239
1240 -- Code above has been added so that each programming unit
1241 -- handles the exception of the previous run
1242
1243 -- Next we determine how many times the main procedure needs to be called
1244 -- for a given project
1245
1246 IF l_pa_calendar_flag = 'Y' THEN
1247 l_dual_limit := 2;
1248 ELSE
1249 l_dual_limit := 1;
1250 END IF;
1251
1252
1253 l_prev_project := -99;
1254
1255 /* LOOP
1256
1257 -- Exit if the main process is not running
1258 IF (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1259 (PJI_RM_SUM_MAIN.g_process, 'PROCESS_RUNNING') <> 'Y' ) THEN
1260 exit;
1261 END IF;
1262
1263 l_try_upd_again := 'N';
1264
1268 Where WORKER_ID = 1
1265 Update pji_pji_proj_batch_map
1266 Set BACKLOG_EXTRACTION_STATUS='P'
1267 , PARALLEL_BACKLOG_WORKER_ID = p_worker_id
1269 And BACKLOG_EXTRACTION_STATUS is null
1270 And rownum = 1
1271 And not exists
1272 ( select 1
1273 from pji_pji_proj_batch_map
1274 where worker_id = 1
1275 and parallel_backlog_worker_id = p_worker_id
1276 and backlog_extraction_status = 'P'
1277 )
1278 And ( ACTIVITY_MIN_GL_DATE is not null
1279 or ACTIVITY_MIN_PA_DATE is not null
1280 or FUNDING_MIN_DATE is not null
1281 or nvl(OLD_CLOSED_DATE, l_max_date)
1282 <> nvl(NEW_CLOSED_DATE, l_max_date)
1283 )
1284 ;Commented for bug 13011859 */
1285
1286 l_rowcount := SQL%ROWCOUNT;
1287
1288
1289 -- The following IF is to optimize the distribution of load on
1290 -- the helpers in case one of them hit upon a data contention and
1291 -- was not able to commit the record that it updated above
1292 /* IF ( l_rowcount <> 0 ) THEN
1293 commit;
1294 ELSE -- either there are no more records to be processed
1295 -- or there has been data contention
1296 select count(*)
1297 into l_count_for_upd
1298 from pji_pji_proj_batch_map
1299 where WORKER_ID = p_worker_id
1300 and BACKLOG_EXTRACTION_STATUS is null
1301 and ( ACTIVITY_MIN_GL_DATE is not null
1302 or ACTIVITY_MIN_PA_DATE is not null
1303 or FUNDING_MIN_DATE is not null
1304 or nvl(OLD_CLOSED_DATE, l_max_date)
1305 <> nvl(NEW_CLOSED_DATE, l_max_date)
1306 )
1307 ;
1308
1309 if ( l_count_for_upd = 0 ) then
1310 exit;
1311 else
1312 l_try_upd_again := 'Y';
1313 end if;
1314 END IF; -- ( l_rowcount <> 0 )
1315
1316
1317 IF ( l_try_upd_again = 'Y' ) Then
1318 -- wait for some time and try again
1319 PJI_PROCESS_UTIL.sleep(PJI_RM_SUM_MAIN.g_process_delay);
1320 ELSE -- go ahead with the processing for the record updated
1321
1322 Select project_id
1323 Into l_curr_project_id
1324 From pji_pji_proj_batch_map
1325 Where BACKLOG_EXTRACTION_STATUS='P'
1326 And PARALLEL_BACKLOG_WORKER_ID = p_worker_id
1327 ;
1328
1329 IF (l_prev_project <> l_curr_project_id) THEN
1330 l_dual := 0;
1331 END IF;
1332
1333 WHILE l_dual < l_dual_limit Loop
1334
1335
1336 SELECT /*+ ORDERED *
1337 map.project_id
1338 , least(
1339 (nvl(map.ACTIVITY_MIN_GL_DATE, l_max_date))
1340 , (nvl(map.FUNDING_MIN_DATE, l_max_date))
1341 , (nvl(map.OLD_CLOSED_DATE, l_max_date))
1342 , (nvl(map.NEW_CLOSED_DATE, l_max_date))
1343 , (nvl(last_extr_date , l_max_date))
1344 ) as min_nocal_date
1345 , least(
1346 (nvl(map.ACTIVITY_MIN_PA_DATE, l_max_date))
1347 , (nvl(map.FUNDING_MIN_DATE, l_max_date))
1348 , (nvl(map.OLD_CLOSED_DATE, l_max_date))
1349 , (nvl(map.NEW_CLOSED_DATE, l_max_date))
1350 , (nvl(last_extr_date , l_max_date))
1351 ) as min_cal_date
1352 , nvl(map.NEW_CLOSED_DATE, l_max_date) as new_closed_date
1353 , map.PROJECT_ORG_ID
1354 , map.PROJECT_ORGANIZATION_ID
1355 , org_info.PA_CALENDAR_ID
1356 , org_info.GL_CALENDAR_ID
1357 , org_info.PA_CALENDAR_ID
1358 INTO l_project_id
1359 , l_min_nocal_date
1360 , l_min_cal_date
1361 , l_close_date
1362 , l_project_org_id
1363 , l_project_organization_id
1364 , l_calendar_id
1365 , l_gl_calendar_id
1366 , l_pa_calendar_id
1367 FROM pji_pji_proj_batch_map map
1368 , PJI_ORG_EXTR_INFO org_info
1369 WHERE map.WORKER_ID = 1
1370 AND map.PROJECT_ORG_ID = org_info.org_id
1371 AND map.project_id = l_curr_project_id
1372 ;
1373
1374
1375 l_close_julian_date := to_number(to_char(l_close_date,'j'));
1376
1377
1378
1379 -- First we call the main procedure for the period type of ITD
1380 -- that is when the pa_calendar_id is null
1381 -- The second time we call for the period type of PA
1382 -- that is when the pa_calendar_id is NOT null
1383
1384 IF (l_dual = 0) Then
1385 l_min_julian_date := to_number(to_char(l_min_nocal_date,'j'));
1386 l_calendar_id := null;
1387 ELSIF (l_dual = 1) Then
1388 l_min_julian_date := to_number(to_char(l_min_cal_date,'j'));
1389 END IF;
1390
1391 Calculate_Drmt_Bklg(
1392 p_worker_id => l_worker_id
1393 , p_project_id => l_project_id
1394 , p_project_org_id => l_project_org_id
1395 , p_project_organization_id => l_project_organization_id
1396 , p_calendar_id => l_calendar_id
1400 , p_min_julian_date => l_min_julian_date
1397 , p_gl_calendar_id => l_gl_calendar_id
1398 , p_pa_calendar_id => l_pa_calendar_id
1399 , p_dbklg_days => l_dbklg_days
1401 , p_close_julian_date => l_close_julian_date
1402 );
1403
1404 l_dual := l_dual + 1;
1405
1406 end loop; -- WHILE l_dual < 3
1407
1408 l_prev_project := l_curr_project_id;
1409
1410 Update pji_pji_proj_batch_map
1411 Set BACKLOG_EXTRACTION_STATUS='X'
1412 Where project_id = l_curr_project_id
1413 ;
1414
1415
1416 commit;
1417
1418
1419 END IF; -- ( l_try_upd_again = 'Y' )
1420
1421
1422 END LOOP;
1423
1424
1425 select count(*)
1426 into l_count_for_upd
1427 from pji_pji_proj_batch_map
1428 where WORKER_ID = p_worker_id
1429 and NVL(BACKLOG_EXTRACTION_STATUS,'P') = 'P'
1430 ;
1431
1432 IF ( l_count_for_upd = 0 ) THEN
1433
1434 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
1435
1436 for x in 2 .. l_parallel_processes loop
1437
1438 update PJI_SYSTEM_PRC_STATUS
1439 set STEP_STATUS = 'C'
1440 where PROCESS_NAME = PJI_RM_SUM_MAIN.g_process || to_char(x) and
1441 STEP_NAME = 'PJI_FM_SUM_BKLG.PROCESS_DRMT_BKLG(p_worker_id);';
1442
1443 commit;
1444
1445 end loop;
1446
1447 END IF;
1448
1449 IF (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1450 (PJI_RM_SUM_MAIN.g_process, 'PROCESS_RUNNING') <> 'Y' ) THEN
1451 -- no need to raise any error here
1452 null;
1453
1454 ELSE
1455 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1456 ( l_process, 'PJI_FM_SUM_BKLG.PROCESS_DRMT_BKLG(p_worker_id);');
1457
1458 commit;
1459
1460 END IF;Commented for bug 13011859 */
1461
1462
1463 END Process_Drmt_Bklg;
1464
1465
1466
1467 begin
1468 g_pji_schema := pji_utils.get_pji_schema_name;
1469
1470 end PJI_FM_SUM_BKLG;