1 PACKAGE BODY PA_UTILS2 AS
2 /* $Header: PAXGUT2B.pls 120.15.12010000.2 2008/08/22 16:16:51 mumohan ship $ */
3
4 -- FUNCTION get_period_name /*2835063*/
5 FUNCTION get_period_name RETURN pa_cost_distribution_lines_all.pa_period_name%TYPE is
6 BEGIN
7 /* Please note that this function should be used only after ensuring that
8 get_pa_date() is called for the returned variable's value to be set to
9 a non-NULL value */
10 return g_prvdr_pa_period_name;
11 end get_period_name;
12
13
14 -- ==========================================================================
15 -- = FUNCTION CheckExpOrg
16 -- ==========================================================================
17
18 FUNCTION CheckExpOrg (x_org_id IN NUMBER,
19 x_txn_date in date ) RETURN VARCHAR2 IS
20
21 -- This function returns 'Y' if a given org is a Exp organization ,
22 -- otherwise , it returns 'N'
23
24 CURSOR l_exp_org_csr IS
25 SELECT 'x'
26 FROM pa_organizations_expend_v
27 WHERE organization_id = x_org_id
28 and active_flag = 'Y'
29 and trunc(x_txn_date) between date_from and nvl(date_to,trunc(x_txn_date));
30
31 l_dummy VARCHAR2(1);
32
33 BEGIN
34
35 IF (x_org_id = G_PREV_ORG_ID AND
36 trunc(x_txn_date) = G_PREV_TXN_DATE) THEN
37
38 RETURN(G_PREV_EXP_ORG);
39
40 ELSE
41
42 G_PREV_ORG_ID := x_org_id;
43 G_PREV_TXN_DATE := trunc(x_txn_date);
44
45 OPEN l_exp_org_csr;
46 FETCH l_exp_org_csr INTO l_dummy;
47
48 IF l_exp_org_csr%NOTFOUND THEN
49
50 close l_exp_org_csr; -- bug 5347506
51 G_PREV_EXP_ORG := 'N';
52 RETURN 'N';
53
54 ELSE
55
56 close l_exp_org_csr; -- bug 5347506
57 G_PREV_EXP_ORG := 'Y';
58 RETURN 'Y';
59
60 END IF;
61
62 -- CLOSE l_exp_org_csr; -- bug 5347506
63
64 END IF;
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 G_PREV_ORG_ID := x_org_id;
69 G_PREV_TXN_DATE := trunc(x_txn_date);
70 G_PREV_EXP_ORG := 'N';
71 RETURN 'N';
72
73 END CheckExpOrg;
74
75 FUNCTION CheckSysLinkFuncActive(x_exp_type IN VARCHAR2,
76 x_ei_date IN DATE,
77 x_sys_link_func IN VARCHAR2) RETURN BOOLEAN
78 IS
79
80 x_dummy NUMBER DEFAULT 0;
81
82 BEGIN
83
84 select count(*)
85 into x_dummy
86 from pa_expenditure_types_expend_v
87 where x_ei_date between expnd_typ_start_date_active
88 and nvl(expnd_typ_end_date_active,x_ei_date)
89 and x_ei_date between SYS_LINK_START_DATE_ACTIVE
90 and nvl(sys_link_end_date_active,x_ei_date)
91 and system_linkage_function = x_sys_link_func
92 and expenditure_type = x_exp_type;
93
94 IF ( x_dummy = 0 ) THEN
95 RETURN ( FALSE );
96 ELSE
97 RETURN ( TRUE );
98 END IF;
99
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 RETURN ( FALSE );
103 WHEN OTHERS THEN
104 RAISE;
105
106 END CheckSysLinkFuncActive;
107
108 FUNCTION CheckAdjFlag (x_exp_item_id In Number) RETURN VARCHAR2 IS
109
110 x_return_flag VARCHAR2(1);
111
112 BEGIN
113
114 SELECT NVL(NET_ZERO_ADJUSTMENT_FLAG,'N')
115 INTO x_return_flag
116 FROM PA_EXPENDITURE_ITEMS
117 WHERE EXPENDITURE_ITEM_ID = x_exp_item_id;
118
119 RETURN ( x_return_flag ) ;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 RAISE;
124
125 END CheckAdjFlag;
126
127 ---------------------------------------------------------------
128 --=================================================================================
129 -- These are the new procedures and functions added for Archive / Purge
130 --=================================================================================
131
132 -- ==========================================================================
133 -- = FUNCTION IsSourcePurged
134 -- ==========================================================================
135
136 FUNCTION IsSourcePurged ( X_exp_id IN NUMBER ) RETURN VARCHAR2
137 IS
138 l_dummy VARCHAR2(1) := 'N';
139 BEGIN
140 SELECT
141 'Y'
142 INTO
143 l_dummy
144 FROM
145 pa_expend_item_adj_activities eia
146 WHERE
147 eia.expenditure_item_id = X_exp_id
148 AND eia.exception_activity_code = 'SOURCE ITEM PURGED';
149
150 RETURN ( 'N' );
151
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 RETURN ( 'N' );
155
156 WHEN OTHERS THEN
157 RAISE ;
158
159 END IsSourcePurged;
160
161 -- ==========================================================================
162 -- = FUNCTION IsDestPurged
163 -- ==========================================================================
164
165 FUNCTION IsDestPurged ( X_exp_id IN NUMBER ) RETURN VARCHAR2
166 IS
167 l_dummy VARCHAR2(1) := 'N';
168 BEGIN
169 SELECT
170 'Y'
171 INTO
172 l_dummy
173 FROM
174 pa_expend_item_adj_activities eia
175 WHERE
176 eia.expenditure_item_id = X_exp_id
177 AND eia.exception_activity_code = 'DESTINATION ITEM PURGED';
178
179 RETURN ( l_dummy );
180
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 RETURN ( 'N' );
184
185 WHEN OTHERS THEN
186 RAISE ;
187
188 END IsDestPurged;
189
190 -- ==========================================================================
191 -- = FUNCTION IsProjectClosed
192 -- ==========================================================================
193
194 FUNCTION IsProjectClosed ( X_project_system_status_code IN VARCHAR2 ) RETURN VARCHAR2
195 IS
196 l_dummy VARCHAR2(1);
197 BEGIN
198 if X_project_system_status_code in ( 'CLOSED',
199 'PENDING_PURGE',
200 'PARTIALLY_PURGED',
201 'PURGED') then
202
203 RETURN ( 'Y');
204 else
205 RETURN ( 'N');
206 end if;
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 RETURN ( 'N' );
211
212 END IsProjectClosed;
213
214 -- ==========================================================================
215 -- = FUNCTION IsProjectInPurgeStatus
216 -- ==========================================================================
217
218 FUNCTION IsProjectInPurgeStatus ( X_project_system_status_code IN VARCHAR2 )
219 RETURN VARCHAR2
220 IS
221 l_dummy VARCHAR2(1);
222 BEGIN
223 if X_project_system_status_code in ( 'PENDING_PURGE',
224 'PARTIALLY_PURGED',
225 'PURGED') then
226
227 RETURN ( 'Y');
228 else
229 RETURN ( 'N');
230 end if;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 RETURN ( 'N' );
235
236 END IsProjectInPurgeStatus;
237
238 -- ==========================================================================
239 -- = PROCEDURE IsActivePrjTxnsPurged
240 -- ==========================================================================
241
242 PROCEDURE IsActivePrjTxnsPurged(p_project_id IN NUMBER,
243 x_message_code IN OUT NOCOPY VARCHAR2,
244 x_token IN OUT NOCOPY DATE)
245
246 is
247
248 -- cursor C1 is
249 -- select pp.txn_to_date
250 -- from pa_purge_projects pp, pa_purge_batches pb
251 -- where pp.project_id = p_project_id
252 -- and pp.purge_batch_id = pb.purge_batch_id
253 -- and pb.active_closed_flag = 'A' ;
254 --
255
256 l_txn_to_date DATE ;
257
258 begin
259 -- open C1;
260 -- fetch C1 into l_txn_to_date ;
261 -- if C1%FOUND then
262 -- x_message_code := 'PA_TR_APE_PRIOR_TXNS_PURGED' ;
263 -- x_token := l_txn_to_date ;
264 -- end if;
265 -- close C1;
266 NULL ;
267 exception
268 WHEN others then
269 RAISE ;
270 end IsActivePrjTxnsPurged ;
271
272 -- ==========================================================================
273 -- = FUNCTION IsProjectTxnsPurged
274 -- ==========================================================================
275
276 function IsProjectTxnsPurged(p_project_id IN NUMBER) RETURN BOOLEAN
277
278 is
279
280 -- cursor C1 is
281 -- select 'X'
282 -- from pa_purge_projects pp, pa_purge_batches pb
283 -- where pp.project_id = p_project_id
284 -- and pp.purge_batch_id = pb.purge_batch_id
285 -- and pp.purge_actuals_flag = 'Y'
286 -- and pb.batch_status_code in ('C','P') ;
287 --
288
289 l_dummy VARCHAR2(1);
290
291 begin
292 -- open C1;
293 -- fetch C1 into l_dummy ;
294 -- if C1%FOUND then
295 -- close C1 ;
296 -- return TRUE ;
297 -- end if;
298 -- close C1;
299 return FALSE ;
300 NULL ;
301 exception
302 WHEN others then
303 RAISE ;
304 end IsProjectTxnsPurged ;
305
306 -- ==========================================================================
307 -- = FUNCTION IsProjectCapitalPurged
308 -- ==========================================================================
309
310 function IsProjectCapitalPurged(p_project_id IN NUMBER) RETURN BOOLEAN
311
312 is
313
314 -- cursor C1 is
315 -- select 'X'
316 -- from pa_purge_projects pp, pa_purge_batches pb
317 -- where pp.project_id = p_project_id
318 -- and pp.purge_batch_id = pb.purge_batch_id
319 -- and pp.purge_capital_flag = 'Y'
320 -- and pb.batch_status_code in ('C','P') ;
321 --
322
323 l_dummy VARCHAR2(1);
324
325 begin
326 -- open C1;
327 -- fetch C1 into l_dummy ;
328 -- if C1%FOUND then
329 -- close C1 ;
330 -- return TRUE ;
331 -- end if;
332 -- close C1;
333 return FALSE ;
334 NULL ;
335 exception
336 WHEN others then
337 RAISE ;
338 end IsProjectCapitalPurged ;
339
340 -- ==========================================================================
341 -- = FUNCTION IsProjectBudgetsPurged
342 -- ==========================================================================
343
344 function IsProjectBudgetsPurged(p_project_id IN NUMBER) RETURN BOOLEAN
345
346 is
347
348 -- cursor C1 is
349 -- select 'X'
350 -- from pa_purge_projects pp, pa_purge_batches pb
351 -- where pp.project_id = p_project_id
352 -- and pp.purge_batch_id = pb.purge_batch_id
353 -- and pp.purge_budgets_flag = 'Y'
354 -- and pb.batch_status_code in ('C','P') ;
355 --
356
357 l_dummy VARCHAR2(1);
358
359 begin
360 -- open C1;
361 -- fetch C1 into l_dummy ;
362 -- if C1%FOUND then
363 -- close C1 ;
364 -- return TRUE ;
365 -- end if;
366 -- close C1;
367 return FALSE ;
368 NULL ;
369 exception
370 WHEN others then
371 RAISE ;
372 end IsProjectBudgetsPurged ;
373
374 -- ==========================================================================
375 -- = PROCEDURE IsProjectSummaryPurged
376 -- ==========================================================================
377
378 function IsProjectSummaryPurged(p_project_id IN NUMBER) RETURN BOOLEAN
379
380 is
381
382 -- cursor C1 is
383 -- select 'X'
384 -- from pa_purge_projects pp, pa_purge_batches pb
385 -- where pp.project_id = p_project_id
386 -- and pp.purge_batch_id = pb.purge_batch_id
387 -- and pp.purge_summary_flag = 'Y'
388 -- and pb.batch_status_code in ('C','P') ;
389 --
390
391 l_dummy VARCHAR2(1);
392
393 begin
394 -- open C1;
395 -- fetch C1 into l_dummy ;
396 -- if C1%FOUND then
397 -- close C1 ;
398 -- return TRUE ;
399 -- end if;
400 -- close C1;
401 return FALSE ;
402 NULL ;
403 exception
404 WHEN others then
405 RAISE ;
406 end IsProjectSummaryPurged ;
407
408 FUNCTION GetProductRelease RETURN VARCHAR2 is
409
410 cursor GetRelease is
411 select release_name
412 from fnd_product_groups ;
413
414 l_dummy varchar2(50);
415 Begin
416
417 open GetRelease ;
418 fetch GetRelease into l_dummy ;
419 close GetRelease;
420 return l_dummy ;
421 exception
422 when others then
423 raise ;
424 end GetProductRelease ;
425
426 -- ==========================================================================
427 -- = FUNCTION GetLaborCostMultiplier
428 -- ==========================================================================
429
430 function GetLaborCostMultiplier (x_task_id In Number) RETURN VARCHAR2 IS
431
432 l_lcm_name VARCHAR2(20);
433
434 BEGIN
435
436 IF (x_task_id = G_PREV_TASK_ID) THEN
437
438 RETURN G_PREV_LCM_NAME;
439
440 ELSE
441
442 G_PREV_TASK_ID := x_task_id;
443
444 SELECT T.labor_cost_multiplier_name
445 INTO l_lcm_name
446 FROM PA_TASKS T
447 WHERE T.task_id = x_task_id;
448
449 G_PREV_LCM_NAME := l_lcm_name;
450
451 RETURN ( l_lcm_name ) ;
452
453 END IF;
454
455 EXCEPTION
456 WHEN OTHERS THEN
457 G_PREV_TASK_ID := x_task_id;
458 G_PREV_LCM_NAME := NULL;
459 RAISE;
460
461 END GetLaborCostMultiplier;
462
463 --=================================================================================
464
465
466 FUNCTION GetPrjOrgId(p_project_id NUMBER,
467 p_task_id NUMBER )
468 RETURN NUMBER IS
469
470 l_org_id NUMBER ;
471 BEGIN
472
473 IF (p_project_id = G_PREV_PROJ_ID AND
474 p_task_id = G_PREV_TASK_ID2) THEN
475
476 RETURN (G_PREV_ORG_ID2);
477
478 ELSE
479
480 IF p_project_id IS NOT NULL THEN
481
482 G_PREV_PROJ_ID := p_project_id;
483 G_PREV_TASK_ID2 := p_task_id;
484
485 -- This section of IF is executed if project id is
486 -- passed to the function
487 SELECT org_id
488 INTO l_org_id
489 FROM pa_projects_all
490 WHERE project_id = p_project_id ;
491
492 G_PREV_ORG_ID2 := l_org_id;
493
494 ELSE
495
496 G_PREV_PROJ_ID := p_project_id;
497 G_PREV_TASK_ID2 := p_task_id;
498
499 -- This section of IF is executed if task_id id is
500 -- passed without a project id.
501
502 SELECT p.org_id
503 INTO l_org_id
504 FROM pa_projects_all p,
505 pa_tasks t
506 WHERE p.project_id = t.project_id
507 AND t.task_id = p_task_id ;
508
509 G_PREV_ORG_ID2 := l_org_id;
510
511 END IF;
512
513 RETURN (l_org_id ) ;
514
515 END IF;
516
517 EXCEPTION
518 WHEN OTHERS THEN
519 G_PREV_PROJ_ID := p_project_id;
520 G_PREV_TASK_ID2 := p_task_id;
521 G_PREV_ORG_ID2 := NULL;
522 RAISE ;
523 END GetPrjOrgId ;
524
525 --------------------------------------------------------------
526 /*
527 * This procedure will be called from both get_pa_date and get_recvr_pa_date -
528 * does caching - accesses the database and populate the global variables.
529 * This procedure will hit the database for a given org_id and ei_date and then populates
530 * the global variables of either receiver or provider based on the flag ( 'R' or 'P' ).
531 */
532
533
534 PROCEDURE refresh_pa_cache (p_org_id IN number , p_ei_date IN date , p_caller_flag IN varchar2 )
535 IS
536 -- local variables
537 l_earliest_start_date date ;
538 l_earliest_end_date date ;
539 l_earliest_period_name varchar2(15) ;
540 l_pa_date date ;
541 l_start_date date ; -- start date for the l_pa_date.
542 l_end_date date ;
543 l_period_name varchar2(15);
544
545 l_stage NUMBER;
546
547 BEGIN
548
549 l_stage := 10;
550 /*
551 * SQL to select the earliest open PA_DATE
552 * Select the earliest open date only if the global earliest date is not yet populated.
553 * Because , earliest pa_date will remain the same for a run.
554 */
555
556 IF ( p_caller_flag = 'R' AND g_r_earliest_pa_start_date IS NULL ) OR
557 ( p_caller_flag = 'P' AND g_p_earliest_pa_start_date IS NULL ) THEN
558
559 l_stage := 20;
560 /*
561 * EPP.
562 * This sql is modified to get both the start and end dates.
563 */
564 SELECT pap1.start_date
565 ,pap1.end_date
566 ,pap1.period_name
567 INTO l_earliest_start_date
568 ,l_earliest_end_date
569 ,l_earliest_period_name
570 FROM pa_periods_all pap1
571 WHERE pap1.status in ('O','F')
572 AND nvl( pap1.org_id, -99 ) = nvl( p_org_id, -99 )
573 AND pap1.start_date = ( SELECT MIN (pap.start_date)
574 FROM pa_periods_all pap
575 WHERE pap.status in ('O','F')
576 AND nvl( pap.org_id, -99 ) = nvl( p_org_id, -99 )
577 );
578 l_stage := 25;
579 END IF ;
580
581 IF ( fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION') = 'Y' ) /*For Bug 5391468*/
582 THEN
583 l_stage := 30;
584 /*
585 * If the profile option is set,
586 * -- the pa date should equal the ei date (if the ei date falls in a open
587 * pa period.)
588 * -- the pa date should equal the start date of the immediate next open
589 * period (if the ei date doesnt fall in a open period.)
590 */
591
592 -- SQL to select the PA_DATE of the current EI.
593
594 l_pa_date := NULL;
595 l_start_date := NULL;
596 l_end_date := NULL;
597 l_period_name := NULL;
598 /*
599 * EPP.
600 * This sql has been changed.
601 * If the txn falls in an open period, store the start,end dates
602 * and period_name for caching purposes.
603 */
604 BEGIN
605 l_stage := 40;
606 SELECT p_ei_date
607 ,pap.start_date
608 ,pap.end_date
609 ,pap.period_name
610 INTO l_pa_date
611 ,l_start_date
612 ,l_end_date
613 ,l_period_name
614 FROM pa_periods_all pap
615 WHERE pap.status in ('O','F')
616 AND trunc(p_ei_date) between pap.start_date and pap.end_date
617 AND pap.org_id = p_org_id ; --removed nvl for the bug#6343739
618 EXCEPTION
619 WHEN NO_DATA_FOUND
620 THEN
621 /*
622 * The txn does not fall in a open period.
623 * Select the immediate available open or future period.
624 */
625 SELECT pap1.start_date
626 ,pap1.start_date
627 ,pap1.end_date
628 ,pap1.period_name
629 INTO l_pa_date
630 ,l_start_date
631 ,l_end_date
632 ,l_period_name
633 FROM pa_periods_all pap1
634 WHERE pap1.status in ('O','F')
635 AND nvl( pap1.org_id, -99 ) = nvl( p_org_id, -99 )
636 AND pap1.start_date = ( SELECT MIN (pap.start_date)
637 FROM pa_periods_all pap
638 WHERE pap.status in ('O','F')
639 AND pap.org_id = p_org_id --removed nvl for the bug#6343739
640 AND trunc(p_ei_date) <= pap.start_date
641 );
642 END; -- local block
643
644 if ( p_caller_flag = 'R' ) then
645 -- Populate receiver cache.
646 g_r_earliest_pa_start_date := l_earliest_start_date ;
647 g_r_earliest_pa_end_date := l_earliest_end_date ;
648 g_r_earliest_pa_period_name := l_earliest_period_name ;
649
650 g_recvr_pa_start_date := l_start_date ;
651 g_recvr_pa_end_date := l_end_date ;
652 g_recvr_pa_period_name := l_period_name;
653
654 g_recvr_pa_date := l_pa_date;
655 g_recvr_org_id := p_org_id ;
656 else
657 if ( p_caller_flag = 'P' ) then
658 -- Populate provider cache
659 g_p_earliest_pa_start_date := l_earliest_start_date ;
660 g_p_earliest_pa_end_date := l_earliest_end_date ;
661 g_p_earliest_pa_period_name := l_earliest_period_name ;
662
663 g_prvdr_pa_start_date := l_start_date ;
664 g_prvdr_pa_end_date := l_end_date ;
665 g_prvdr_pa_period_name := l_period_name ;
666
667 g_prvdr_pa_date := l_pa_date ;
668 g_prvdr_org_id := p_org_id ;
669 end if ;
670 end if;
671
672 ELSE -- profile is NOT set.
673 l_stage := 26;
674
675 /*
676 * If the profile option NOT set,
677 * -- the pa dates should equal the end date of the period (if the ei date
678 * falls in the respective open periods.)
679 * -- the pa date should equal the end date of the immediate next open
680 * period (if the ei date doesnt fall in a open period.)
681 */
682
683 -- SQL to select the PA_DATE of the current EI.
684
685 l_pa_date := NULL;
686 l_start_date := NULL;
687 l_end_date := NULL;
688 l_period_name := NULL;
689 /*
690 * pa_gl period related changes:
691 * This sql has been changed.
692 * If the txn falls in an open period, store the start and end dates
693 * of that period for caching purposes.
694 */
695 BEGIN
696 l_stage := 27;
697 SELECT pap.end_date
698 ,pap.start_date
699 ,pap.end_date
700 ,pap.period_name
701 INTO l_pa_date
702 ,l_start_date
703 ,l_end_date
704 ,l_period_name
705 FROM pa_periods_all pap
706 WHERE pap.status in ('O','F')
707 AND trunc(p_ei_date) between pap.start_date and pap.end_date
708 AND NVL(pap.org_id, -99) = NVL(p_org_id, -99) ;
709 l_stage := 28;
710 EXCEPTION
711 WHEN NO_DATA_FOUND
712 THEN
713 /*
714 * The txn does not fall in a open period.
715 * Select the immediate available open or future period.
716 */
717 SELECT pap1.end_date
718 ,pap1.start_date
719 ,pap1.end_date
720 ,pap1.period_name
721 INTO l_pa_date
722 ,l_start_date
723 ,l_end_date
724 ,l_period_name
725 FROM pa_periods_all pap1
726 WHERE pap1.status in ('O','F')
727 AND nvl( pap1.org_id, -99 ) = nvl( p_org_id, -99 )
728 AND pap1.start_date = ( SELECT MIN (pap.start_date)
729 FROM pa_periods_all pap
730 WHERE pap.status in ('O','F')
731 AND nvl( pap.org_id, -99 ) = nvl( p_org_id, -99 )
732 AND trunc(p_ei_date) <= pap.start_date
733 );
734 l_stage := 29;
735 END; -- local block
736 l_stage := 31;
737
738 if ( p_caller_flag = 'R' ) then
739 l_stage := 32;
740 -- Populate receiver cache.
741 g_r_earliest_pa_start_date := l_earliest_start_date ;
742 l_stage := 321;
743 g_r_earliest_pa_end_date := l_earliest_end_date ;
744 l_stage := 322;
745 g_r_earliest_pa_period_name := l_earliest_period_name ;
746 l_stage := 323;
747
748 g_recvr_pa_start_date := l_start_date ;
749 l_stage := 324;
750 g_recvr_pa_end_date := l_end_date ;
751 l_stage := 325;
752 g_recvr_pa_period_name := l_period_name;
753 l_stage := 326;
754
755 g_recvr_pa_date := l_pa_date;
756 l_stage := 327;
757 g_recvr_org_id := p_org_id ;
758 l_stage := 356;
759 else
760 l_stage := 33;
761 if ( p_caller_flag = 'P' ) then
762 l_stage := 34;
763 -- Populate provider cache
764 g_p_earliest_pa_start_date := l_earliest_start_date ;
765 g_p_earliest_pa_end_date := l_earliest_end_date ;
766 g_p_earliest_pa_period_name := l_earliest_period_name ;
767
768 g_prvdr_pa_start_date := l_start_date ;
769 g_prvdr_pa_end_date := l_end_date ;
770 g_prvdr_pa_period_name := l_period_name ;
771
772 g_prvdr_pa_date := l_pa_date ;
773 g_prvdr_org_id := p_org_id ;
774 end if ;
775 end if;
776
777 END IF ; -- profile option check.
778
779 EXCEPTION
780 WHEN no_data_found THEN
781 if ( p_caller_flag = 'P' ) then
782 g_prvdr_pa_date := NULL ;
783 g_prvdr_pa_period_name := NULL ;
784 /** Added for 2810747 **/
785 g_prvdr_pa_start_date := NULL;
786 g_prvdr_pa_end_date := NULL;
787 /** End Added for 2810747 **/
788 /*elsif ( p_caller_flag = 'P' ) then Bug2724294*/
789 elsif ( p_caller_flag = 'R' ) then
790 g_recvr_pa_date := NULL ;
791 g_recvr_pa_period_name := NULL ;
792 /** Added for 2810747 **/
793 g_recvr_pa_start_date := NULL;
794 g_recvr_pa_end_date := NULL;
795 /** End Added for 2810747 **/
796 end if;
797 WHEN others THEN
798 RAISE ;
799
800 END refresh_pa_cache ;
801 --=================================================================================
802 -- Function : get_pa_date
803 -- Derive PA date from GL date and ei date .
804 -- This function accepts the expenditure item date and the GL date
805 -- and derives the period name based on this. This is mainly used
806 -- for AP invoices and transactions imported from other systems
807 -- where the GL date is known in advance and the PA date has to
808 -- be determined. In the current logic, the PA date is derived solely
809 -- based on the EI date. The GL date which is passed as a parameter is
810 -- ignored. However, it is still retained as a parameter in case the
811 -- logic for the derivation of the PA date is changed on a later date.
812 -----------------------------------------------------------------------
813
814 /**This function was previously part-of pa_utils.
815 **It was moved here and changed for CBGA and caching.
816 **/
817
818 FUNCTION get_pa_date( p_ei_date IN date, p_gl_date IN date, p_org_id IN number) return date
819 IS
820 BEGIN
821
822 /**
823 Global variables for the Provider Cache.
824 g_prvdr_org_id, g_prvdr_earliest_pa_date, g_prvdr_pa_start_date, g_prvdr_pa_end_date,
825 g_prvdr_pa_date
826
827 Logic :-
828 ~~~~~
829 If the Cache is NOT already populated,
830 access DB, populate cache and return g_pa_date.
831 If it is already populated, check if the p_ei_date falls between start and end dates of the
832 cache. If yes, return g_pa_date from the cache.
833 If the p_ei_date doesnt' fall between the start and end dates, chek if its lesser
834 than the earliest available pa_date. If YES, return g_earliest_pa_date.
835 If NO, access the DB and refresh the cache and return new g_pa_date.
836 **/
837
838 -- Coding Starts
839 /*
840 * Validate the input parameters.
841 * If the essential input parameters have NULL values, set the global variables
842 * appropriately and return NULL value.
843 */
844 IF ( p_ei_date IS NULL )
845 THEN
846 return NULL;
847 END IF;
848
849 IF ( g_p_earliest_pa_start_date IS NOT NULL
850 and nvl(p_org_id,-99) = nvl(g_prvdr_org_id,-99) ) /* 1982225. check the orgs before accessing cache */
851 THEN
852 -- values are already available in the provider_cache.
853 -- so, check the provider_cache and return pa_date accordingly.
854
855 IF ( p_ei_date BETWEEN g_prvdr_pa_start_date AND g_prvdr_pa_end_date )
856 THEN
857 IF ( fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION') = 'Y' ) /*For Bug 5391468 */
858 THEN
859 return ( p_ei_date ) ;
860 ELSE
861 return ( g_prvdr_pa_end_date ) ;
862 END IF; -- profile
863 ELSE
864 IF ( fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION') = 'Y' ) /*For Bug 5391468*/
865 THEN
866 IF ( p_ei_date <= g_p_earliest_pa_start_date )
867 THEN
868 g_prvdr_pa_start_date := g_p_earliest_pa_start_date;
869 g_prvdr_pa_end_date := g_p_earliest_pa_end_date;
870 g_prvdr_pa_period_name := g_p_earliest_pa_period_name;
871 return (g_prvdr_pa_start_date) ;
872 ELSIF ( p_ei_date <= g_p_earliest_pa_end_date )
873 THEN
874 g_prvdr_pa_start_date := g_p_earliest_pa_start_date;
875 g_prvdr_pa_end_date := g_p_earliest_pa_end_date;
876 g_prvdr_pa_period_name := g_p_earliest_pa_period_name;
877 return ( g_prvdr_pa_end_date ) ;
878 END IF; -- p_ei_date
879 END IF; -- profile
880 END IF ; -- p_ei_date
881 END IF ; -- g_prvdr_earliest_pa_date
882
883 -- If control comes here, it means that either the cache is empty or
884 -- the provider Cache is not reusable.
885 -- Access the DB and refresh cache and return pa_date.
886
887 pa_utils2.refresh_pa_cache( p_org_id , p_ei_date, 'P' );
888 /*
889 * Here we can return g_prvdr_pa_date - because the profile option
890 * is taken care during the refresh_pa_cache.
891 */
892 return ( g_prvdr_pa_date ) ;
893 EXCEPTION
894 WHEN OTHERS THEN
895 RAISE ;
896 END get_pa_date ;
897
898
899 /* Functions derive pa_date_profile() and pa_period_name_profile() added as as part of BUG# 3384892 */
900 /* caching the value of the profile PA_EN_NEW_GLDATE_DERIVATION */
901
902 FUNCTION pa_date_profile(exp_item_date IN DATE, accounting_date IN DATE, org_id IN NUMBER)
903 RETURN DATE IS
904 l_return_date DATE;
905 BEGIN
906 IF g_profile_cache_first_time = 'Y' THEN
907 g_profile_value := fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION'); /*For Bug 5391468*/
908 g_profile_cache_first_time :='N' ;
909 END IF;
910
911 SELECT decode(nvl(g_profile_value,'N'),
912 'Y', pa_utils2.get_pa_date( exp_item_date,accounting_date,org_id),
913 'N', pa_integration.get_raw_cdl_pa_date(exp_item_date,accounting_date,org_id))
914 INTO l_return_date
915 FROM DUAL;
916 RETURN l_return_date;
917 END pa_date_profile;
918
919 FUNCTION pa_period_name_profile
920 RETURN VARCHAR2 IS
921 l_return_name varchar2(25);
922 BEGIN
923 IF g_profile_cache_first_time = 'Y' THEN
924 g_profile_value := fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION');/*For Bug 5391468*/
925 g_profile_cache_first_time :='N' ;
926 END IF;
927
928 SELECT decode(nvl(g_profile_value,'N'),
929 'Y', pa_utils2.get_period_name(),
930 'N', pa_integration.get_period_name())
931 INTO l_return_name
932 FROM DUAL;
933 RETURN l_return_name;
934 END pa_period_name_profile;
935
936 --------------------------------------------------------------
937 -- Function : get_recvr_pa_date
938 -- Included during CBGA changes.
939 -- Derive PA date from GL date and ei date .
940 -- This function accepts the expenditure item date and the GL date
941 -- and derives the period name based on this. This is mainly used
942 -- for AP invoices and transactions imported from other systems
943 -- where the GL date is known in advance and the PA date has to
944 -- be determined. In the current logic, the PA date is derived solely
945 -- based on the EI date. The GL date which is passed as a parameter is
946 -- ignored. However, it is still retained as a parameter in case the
947 -- logic for the derivation of the PA date is changed on a later date.
948 -----------------------------------------------------------------------
949
950 -- Global variables for receiver cache.
951 -- g_recvr_org_id, g_recvr_earliest_pa_date, g_recvr_start_date, g_recvr_end_date,
952 -- g_recvr_pa_date
953
954 /**
955 Logic :-
956 ~~~~~
957 If the receiver cache is already populated,
958 Try using the receiver cache.
959 If its not reusable,
960 Try using the provider cache.
961 If either receiver cache is EMPTY or both provider and receiver are reusable,
962 hit the DB and populate/refresh receiver cache.
963 **/
964
965 FUNCTION get_recvr_pa_date( p_ei_date IN date, p_gl_date IN date , p_org_id IN number ) return date
966 IS
967 l_stage NUMBER ;
968 BEGIN
969 l_stage := 100;
970
971 /*
972 * Validate the input parameters.
973 * If the essential input parameters have NULL values, set the global variables
974 * appropriately and return NULL value.
975 */
976 IF ( p_ei_date IS NULL )
977 THEN
978 l_stage := 200;
979 return NULL;
980 END IF;
981
982 IF ( g_r_earliest_pa_start_date IS NOT NULL
983 and nvl(p_org_id,-99) = nvl(g_recvr_org_id,-99) ) /* 1982225. check the orgs before accessing cache */
984 THEN
985 l_stage := 300;
986 -- receiver cache is available.
987 -- should try to re-use the receiver cache.
988
989 IF ( p_ei_date BETWEEN g_recvr_pa_start_date AND g_recvr_pa_end_date )
990 THEN
991 l_stage := 400;
992 IF ( fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION') = 'Y' ) /* For Bug 5391468 */
993 THEN
994 l_stage := 500;
995 return ( p_ei_date ) ;
996 ELSE
997 l_stage := 600;
998 return ( g_recvr_pa_end_date ) ;
999 END IF ; -- profile
1000 ELSE
1001 l_stage := 700;
1002 IF ( fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION') = 'Y' ) /*For Bug 5391468 */
1003 THEN
1004 l_stage := 800;
1005 IF ( p_ei_date <= g_r_earliest_pa_start_date )
1006 THEN
1007 l_stage := 900;
1008 g_recvr_pa_start_date := g_r_earliest_pa_start_date ;
1009 g_recvr_pa_end_date := g_r_earliest_pa_end_date ;
1010 g_recvr_pa_period_name := g_r_earliest_pa_period_name ;
1011 return ( g_r_earliest_pa_start_date ) ;
1012 END IF; -- p_ei_date
1013 ELSIF (p_ei_date <= g_r_earliest_pa_end_date )
1014 THEN
1015 l_stage := 1000;
1016 g_recvr_pa_start_date := g_r_earliest_pa_start_date ;
1017 g_recvr_pa_end_date := g_r_earliest_pa_end_date ;
1018 g_recvr_pa_period_name := g_r_earliest_pa_period_name ;
1019 return ( g_r_earliest_pa_end_date ) ;
1020 END IF; -- profile
1021 END IF ; -- p_ei_date
1022 ELSE
1023 l_stage := 110;
1024 -- receiver cache is empty.
1025 -- should try to use the provider cache.
1026
1027 IF ( nvl( g_prvdr_org_id, -99 ) = nvl( p_org_id, -99 )
1028 and g_p_earliest_pa_start_date IS NOT NULL ) /* 1982225. check if prvdr cache is available or not */
1029 THEN
1030 l_stage := 1100;
1031 IF ( p_ei_date BETWEEN g_prvdr_pa_start_date AND g_prvdr_pa_end_date )
1032 THEN
1033 l_stage := 1200;
1034
1035 -- copy provider cache to receiver cache.
1036 g_recvr_org_id := g_prvdr_org_id ;
1037 g_recvr_pa_date := g_prvdr_pa_date ;
1038 g_r_earliest_pa_start_date := g_p_earliest_pa_start_date ;
1039 g_r_earliest_pa_end_date := g_p_earliest_pa_end_date ;
1040 g_r_earliest_pa_period_name := g_p_earliest_pa_period_name ;
1041 g_recvr_pa_start_date := g_prvdr_pa_start_date ;
1042 g_recvr_pa_end_date := g_prvdr_pa_end_date ;
1043 g_recvr_pa_period_name := g_prvdr_pa_period_name ;
1044
1045 IF ( fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION') = 'Y' ) /*For Bug 5391468*/
1046 THEN
1047 l_stage := 1300;
1048 return ( p_ei_date ) ;
1049 ELSE
1050 l_stage := 1400;
1051 return ( g_recvr_pa_end_date ) ;
1052 END IF ; -- profile
1053 ELSE
1054 l_stage := 1500;
1055 IF ( fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION') = 'Y' ) /*For Bug 5391468*/
1056 THEN
1057 l_stage := 1600;
1058 IF (p_ei_date <= g_p_earliest_pa_start_date )
1059 THEN
1060 l_stage := 1700;
1061 -- copy provider cache to receiver cache.
1062 g_recvr_org_id := g_prvdr_org_id ;
1063 g_r_earliest_pa_start_date := g_p_earliest_pa_start_date ;
1064 g_r_earliest_pa_end_date := g_p_earliest_pa_end_date ;
1065 g_r_earliest_pa_period_name := g_p_earliest_pa_period_name ;
1066 g_recvr_pa_start_date := g_p_earliest_pa_start_date ;
1067 g_recvr_pa_end_date := g_p_earliest_pa_end_date ;
1068 g_recvr_pa_period_name := g_p_earliest_pa_period_name ;
1069
1070 g_recvr_pa_date := g_r_earliest_pa_start_date ;
1071 return ( g_r_earliest_pa_start_date ) ;
1072 END IF; -- p_ei_date
1073 ELSE
1074 IF ( p_ei_date <= g_p_earliest_pa_end_date )
1075 THEN
1076 -- copy provider cache to receiver cache.
1077 g_recvr_org_id := g_prvdr_org_id ;
1078 g_r_earliest_pa_start_date := g_p_earliest_pa_start_date ;
1079 g_r_earliest_pa_end_date := g_p_earliest_pa_end_date ;
1080 g_r_earliest_pa_period_name := g_p_earliest_pa_period_name ;
1081 g_recvr_pa_start_date := g_p_earliest_pa_start_date ;
1082 g_recvr_pa_end_date := g_p_earliest_pa_end_date ;
1083 g_recvr_pa_period_name := g_p_earliest_pa_period_name ;
1084
1085 g_recvr_pa_date := g_r_earliest_pa_end_date ;
1086 return ( g_r_earliest_pa_end_date ) ;
1087 END IF; -- p_ei_date
1088 END IF ; -- profile
1089 END IF ; -- p_ei_date
1090 END IF ; -- org_id
1091 END IF ; -- g_r_earliest_pa_start_date
1092 /**
1093 **If control comes here,
1094 **either receiver cache is EMPTY or ( Both provider AND receiver caches are not reusable )
1095 **hence hit the DB and populate/refresh receiver cache.
1096 **then return g_recvr_pa_date.
1097 **/
1098
1099 pa_utils2.refresh_pa_cache ( p_org_id , p_ei_date , 'R' );
1100 return ( g_recvr_pa_date ) ;
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 RAISE ;
1104 END get_recvr_pa_date ;
1105 -----------------------------------------------------------------------
1106 PROCEDURE populate_gl_dates( p_local_set_size IN NUMBER,
1107 p_application_id IN PA_PLSQL_DATATYPES.IDTabTyp,
1108 p_request_id IN PA_PLSQL_DATATYPES.IDTabTyp,
1109 p_cdl_rowid IN PA_PLSQL_DATATYPES.Char30TabTyp ,
1110 p_prvdr_sob_id IN PA_PLSQL_DATATYPES.IDTabTyp,
1111 p_recvr_sob_id IN PA_PLSQL_DATATYPES.IDTabTyp,
1112 p_expnd_id IN PA_PLSQL_DATATYPES.IDTabTyp ,
1113 p_sys_linkage_type IN VARCHAR2
1114 )
1115
1116 IS
1117 l_prvdr_gl_date_tab PA_PLSQL_DATATYPES.DateTabTyp ;
1118 l_recvr_gl_date_tab PA_PLSQL_DATATYPES.DateTabTyp ;
1119 l_pa_date PA_PLSQL_DATATYPES.DateTabTyp ;
1120 l_recvr_pa_date PA_PLSQL_DATATYPES.DateTabTyp ;
1121 l_reject_meaning VARCHAR2(81);
1122
1123 l_gl_date_old PA_PLSQL_DATATYPES.DateTabTyp ;
1124 l_gl_period_old PA_PLSQL_DATATYPES.Char15TabTyp ;
1125 l_recvr_org_id PA_PLSQL_DATATYPES.IDTabTyp ;
1126
1127 /** Bug 3668005 Begins **/
1128 l_gl_date_new PA_PLSQL_DATATYPES.DateTabTyp ;
1129 l_gl_period_new PA_PLSQL_DATATYPES.Char15TabTyp ;
1130 l_recvr_gl_date_new PA_PLSQL_DATATYPES.DateTabTyp ;
1131 l_recvr_gl_period_new PA_PLSQL_DATATYPES.Char15TabTyp ;
1132 p_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
1133 l_err_code NUMBER ;
1134 l_err_stage VARCHAR2(2000);
1135 l_err_stack VARCHAR2(255) ;
1136 l_cwk_lab_to_gl pa_implementations_all.XFACE_CWK_LABOR_TO_GL_FLAG%type;
1137 l_labor_to_gl pa_implementations_all.interface_labor_to_gl_flag%type;
1138 l_usage_to_gl pa_implementations_all.interface_usage_to_gl_flag%type;
1139 l_interface_to_gl PA_PLSQL_DATATYPES.Char1TabTyp;
1140
1141 -- Bug 4374769 : The following variables are introduced as part of this bug.
1142 v_gl_per_end_dt DATE;
1143 l_adj_exp_item_id pa_expenditure_items_all.adjusted_expenditure_item_id%type;
1144 l_exp_item_id pa_expenditure_items_all.expenditure_item_id%type;
1145 l_gl_date DATE;
1146 l_pji_summarized_flag VARCHAR2(1);
1147 l_prvdr_accr_date DATE;
1148 l_billable_flag pa_cost_distribution_lines_all.billable_flag%type;
1149 l_line_type VARCHAR2(1);
1150 l_line_num NUMBER ;
1151 l_denom_currency_code pa_expenditure_items_all.denom_currency_code%type;
1152 l_acct_currency_code pa_expenditure_items_all.acct_currency_code%type;
1153 l_acct_rate_date pa_expenditure_items_all.acct_rate_date%type;
1154 l_acct_rate_type pa_expenditure_items_all.acct_rate_type%type;
1155 l_acct_exchange_rate pa_expenditure_items_all.acct_exchange_rate%type;
1156 l_project_currency_code pa_expenditure_items_all.project_currency_code%type;
1157 l_project_rate_date pa_expenditure_items_all.project_rate_date%type;
1158 l_project_rate_type pa_expenditure_items_all.project_rate_type%type;
1159 l_project_exchange_rate pa_expenditure_items_all.project_exchange_rate%type;
1160 l_projfunc_currency_code pa_expenditure_items_all.projfunc_currency_code%type;
1161 l_projfunc_cost_rate_date pa_expenditure_items_all.projfunc_cost_rate_date%type;
1162 l_projfunc_cost_rate_type pa_expenditure_items_all.projfunc_cost_rate_type%type;
1163 l_projfunc_cost_exchange_rate pa_expenditure_items_all.projfunc_cost_exchange_rate%type;
1164 l_work_type_id pa_expenditure_items_all.work_type_id%type;
1165 l_sob_id pa_implementations.set_of_books_id%type;
1166
1167
1168 -- Cursor to pick up all CDLs with intermediate status 'Y' for creation of
1169 -- reversing and new CDLs .
1170
1171 /* Bug 4374769 : The cursor c_sel_cdl is modified to also select the line_num for a cdl with transfer_status_code as 'Y'.
1172 This line_num is passed to Pa_Costing.ReverseCdl when it is being called from the
1173 populate_gl_dates procedure to create reversing and new lines for the line_num that is being passed. */
1174
1175 Cursor c_sel_cdl Is
1176 SELECT
1177 ei.expenditure_item_id,
1178 cdl.billable_flag,
1179 cdl.line_type,
1180 cdl.line_num, -- Added as part of Bug 4374769
1181 ei.transaction_source,
1182 tr.gl_accounted_flag,
1183 ei.denom_currency_code,
1184 ei.acct_currency_code,
1185 ei.acct_rate_date,
1186 ei.acct_rate_type,
1187 ei.acct_exchange_rate,
1188 ei.project_currency_code,
1189 ei.project_rate_date,
1190 ei.project_rate_type,
1191 ei.project_exchange_rate,
1192 tr.system_linkage_function,
1193 ei.projfunc_currency_code,
1194 ei.projfunc_cost_rate_date,
1195 ei.projfunc_cost_rate_type,
1196 ei.projfunc_cost_exchange_rate,
1197 ei.work_type_id
1198 FROM pa_expenditure_items_all ei,
1199 pa_cost_distribution_lines cdl,
1200 pa_transaction_sources tr
1201 WHERE tr.transaction_source(+) = ei.transaction_source
1202 AND ei.expenditure_item_id = cdl.expenditure_item_id
1203 AND CDL.Transfer_Status_Code = 'Y';
1204
1205 /** Bug 3668005 ends **/
1206
1207 BEGIN
1208
1209
1210
1211 select interface_labor_to_gl_flag , interface_usage_to_gl_flag , XFACE_CWK_LABOR_TO_GL_FLAG , set_of_books_id
1212 Into l_labor_to_gl , l_usage_to_gl , l_cwk_lab_to_gl , l_sob_id
1213 from pa_implementations;
1214
1215 /* Bug 4374769 : If the populate_gl_dates procedure is called for miscellaneous transactions for which the "Reverse Expenditure in a future period"
1216 is checked then the "PRC: Interface Usage and Miscellaneous costs to General Ledger" calls this procedure with the
1217 p_sys_linkage_type parameter as 'PJ' for the CDLs of reversing EI. */
1218
1219 IF (p_sys_linkage_type = 'PJ') THEN
1220
1221 SELECT
1222 ei.expenditure_item_id,
1223 ei.adjusted_expenditure_item_id,
1224 cdl.gl_date,
1225 cdl.pji_summarized_flag,
1226 cdl.billable_flag,
1227 cdl.line_type,
1228 cdl.line_num,
1229 ei.denom_currency_code,
1230 ei.acct_currency_code,
1231 ei.acct_rate_date,
1232 ei.acct_rate_type,
1233 ei.acct_exchange_rate,
1234 ei.project_currency_code,
1235 ei.project_rate_date,
1236 ei.project_rate_type,
1237 ei.project_exchange_rate,
1238 ei.projfunc_currency_code,
1239 ei.projfunc_cost_rate_date,
1240 ei.projfunc_cost_rate_type,
1241 ei.projfunc_cost_exchange_rate,
1242 ei.work_type_id
1243 INTO l_exp_item_id,
1244 l_adj_exp_item_id,
1245 l_gl_date,
1246 l_pji_summarized_flag,
1247 l_billable_flag,
1248 l_line_type,
1249 l_line_num,
1250 l_denom_currency_code,
1251 l_acct_currency_code,
1252 l_acct_rate_date,
1253 l_acct_rate_type,
1254 l_acct_exchange_rate,
1255 l_project_currency_code,
1256 l_project_rate_date,
1257 l_project_rate_type,
1258 l_project_exchange_rate,
1259 l_projfunc_currency_code,
1260 l_projfunc_cost_rate_date,
1261 l_projfunc_cost_rate_type,
1262 l_projfunc_cost_exchange_rate,
1263 l_work_type_id
1264 FROM PA_COST_DISTRIBUTION_LINES_ALL CDL,
1265 PA_EXPENDITURE_ITEMS_ALL EI
1266 WHERE CDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
1267 AND CDL.ROWID = chartorowid( p_cdl_rowid(1));
1268
1269 /* Bug 4374769 : The code in the "PRC: Interface and Usage Transactions to General Ledger" process that updated the CDLs of reversing EI with
1270 next GL period of CDL of original EI, is shifted to populate_gl_dates as below */
1271
1272 /* Bug 4374769 : The following query selects the end_date of the GL period stamped on the 'R' line of the original expenditure item. */
1273
1274 SELECT GPS.end_date
1275 INTO v_gl_per_end_dt
1276 FROM pa_cost_distribution_lines CDL,
1277 gl_period_statuses GPS
1278 WHERE GPS.application_id = 101
1279 AND GPS.set_of_books_id = l_sob_id
1280 AND GPS.adjustment_period_flag = 'N'
1281 AND CDL.expenditure_item_id = l_adj_exp_item_id
1282 AND CDL.gl_date BETWEEN GPS.start_date AND GPS.end_date
1283 AND CDL.LINE_TYPE = 'R';
1284
1285 /* Bug 4374769 : If the date selected in the above query is greater than or equal to the GL date on the cdl of the reversing EI and
1286 a) If the PJI_Summarized_flag on the cdl is 'N' then we directly update the GL_Date of the cdl with the start date
1287 of a GL Period that is next to that of the cdl of the original EI.
1288 b) If the the PJI_Summarized_flag on the cdl is NULL then the ReverseCdl procedure is called to create the reversing
1289 and new 'I' lines. Finally we update the GL_Date of the 'R' and the new 'I' line with the start date of a GL Period
1290 that is next to that of the cdl of the original EI. */
1291
1292 IF (l_gl_date <= v_gl_per_end_dt) THEN
1293
1294 SELECT GPS.start_date
1295 INTO l_prvdr_accr_date
1296 FROM gl_period_statuses GPS
1297 WHERE GPS.application_id = 101
1298 AND GPS.set_of_books_id = l_sob_id
1299 AND GPS.adjustment_period_flag = 'N'
1300 AND GPS.start_date = (SELECT min(GPS1.start_date)
1301 FROM gl_period_statuses GPS1
1302 WHERE GPS1.application_id = 101
1303 AND GPS1.set_of_books_id = l_sob_id
1304 AND GPS1.adjustment_period_flag = 'N'
1305 AND GPS1.start_date > v_gl_per_end_dt);
1306
1307
1308 IF (l_pji_summarized_flag = 'N') THEN
1309
1310 UPDATE PA_Cost_Distribution_lines CDL
1311 SET CDL.gl_date = l_prvdr_accr_date
1312 WHERE CDL.ROWID = chartorowid( p_cdl_rowid(1))
1313 AND CDL.TRANSFER_STATUS_CODE in ('P','R');
1314
1315 ELSE
1316
1317 Pa_Costing.ReverseCdl
1318 ( X_expenditure_item_id => l_exp_item_id
1319 , X_billable_flag => l_billable_flag
1320 , X_amount => NULL
1321 , X_quantity => NULL
1322 , X_burdened_cost => NULL
1323 , X_dr_ccid => NULL
1324 , X_cr_ccid => NULL
1325 , X_tr_source_accounted => 'Y'
1326 , X_line_type => l_line_type
1327 , X_user => p_user_id
1328 , X_denom_currency_code => l_denom_currency_code
1329 , X_denom_raw_cost => NULL
1330 , X_denom_burden_cost => NULL
1331 , X_acct_currency_code => l_acct_currency_code
1332 , X_acct_rate_date => l_acct_rate_date
1333 , X_acct_rate_type => l_acct_rate_type
1334 , X_acct_exchange_rate => l_acct_exchange_rate
1335 , X_acct_raw_cost => NULL
1336 , X_acct_burdened_cost => NULL
1337 , X_project_currency_code => l_project_currency_code
1338 , X_project_rate_date => l_project_rate_date
1339 , X_project_rate_type => l_project_rate_type
1340 , X_project_exchange_rate => l_project_exchange_rate
1341 , X_err_code => l_err_code
1342 , X_err_stage => l_err_stage
1343 , X_err_stack => l_err_stack
1344 , P_Projfunc_currency_code => l_projfunc_currency_code
1345 , P_Projfunc_cost_rate_date => l_projfunc_cost_rate_date
1346 , P_Projfunc_cost_rate_type => l_projfunc_cost_rate_type
1347 , P_Projfunc_cost_exchange_rate => l_projfunc_cost_exchange_rate
1348 , P_project_raw_cost => null
1349 , P_project_burdened_cost => null
1350 , P_Work_Type_Id => l_work_type_id
1351 , P_mode => 'INTERFACE'
1352 , X_line_num => l_line_num
1353 );
1354
1355 UPDATE PA_Cost_Distribution_lines CDL
1356 SET CDL.GL_DATE = l_prvdr_accr_date,
1357 CDL.GL_PERIOD_NAME = pa_utils2.get_gl_period_name (l_prvdr_accr_date,CDL.org_id)
1358 WHERE CDL.EXPENDITURE_ITEM_ID = l_exp_item_id
1359 AND CDL.LINE_NUM_REVERSED IS NULL
1360 AND CDL.TRANSFER_STATUS_CODE in ('P','R','G');
1361
1362
1363 END IF;
1364
1365
1366 END IF;
1367
1368
1369 End If;
1370
1371 /*
1372 *The calculation of pa_date used to arrive at the gl_date varies between
1373 *Expense reports ( sys_link 'ER' ) and non Expense reports.
1374 *The pa_date to be passed to the functions get_prvdr_gl_date/get_recvr_gl_date
1375 *is calculated accordingly based on the sys_linkage value passed.
1376 */
1377
1378 IF (p_sys_linkage_type = 'ER') THEN
1379 -- Decide the rejection reason.
1380 /*
1381 * The SQL can be moved within the FORALL.
1382 * But, since the SELECT is kind-of static i.e., its enough
1383 * that it be executed only once per call, i'm retaining it here.
1384 * In-fact because of its staticness, it can be even moved out of this
1385 * package and retained in patmv.lpc and the reject_reason can be passed
1386 * as parameter.
1387 */
1388 SELECT Meaning
1389 INTO l_reject_meaning
1390 FROM PA_Lookups LOOK
1391 WHERE LOOK.Lookup_Type = 'TRANSFER REJECTION CODE'
1392 AND LOOK.Lookup_Code = 'TRANS_INV_DATA';
1393
1394 FORALL i IN 1..p_local_set_size
1395 UPDATE PA_Cost_Distribution_lines CDL
1396 SET CDL.request_id = p_request_id(i)
1397 ,CDL.transfer_rejection_reason = l_reject_meaning
1398 ,CDL.transfer_status_code = 'X'
1399 ,CDL.Transferred_Date = SYSDATE
1400 /*
1401 * Bug#2085814
1402 * -- Since gl period information is getting populated during costing, it is no
1403 * -- longer needed to populate GL info during transfer to AP.
1404 * -- Ideally this procedure itself need not be called from patmv.lpc. The updates
1405 * -- to other columns like request_id can be done in patmv.lpc itself.
1406 * -- Calling this procedure from pro*C requires some array related processing which
1407 * -- can be avoided if this procedure is not called from pro*C. This change has to
1408 * -- be done - at some point of time.
1409 *
1410 * ,CDL.GL_Date = ( SELECT pa_utils2.get_prvdr_gl_date(
1411 * MAX(CDL.pa_date)
1412 * ,p_application_id(i)
1413 * ,p_prvdr_sob_id(i))
1414 * FROM pa_cost_distribution_lines CDL,
1415 * pa_expenditure_items ITEM
1416 * WHERE ITEM.expenditure_item_id = CDL.expenditure_item_id
1417 * AND CDL.line_type = 'R'
1418 * AND ITEM.expenditure_id = p_expnd_id(i)
1419 * )
1420 * ,CDL.Recvr_Gl_Date = ( SELECT pa_utils2.get_recvr_gl_date(
1421 * MAX(CDL.recvr_pa_date)
1422 * ,p_application_id(i)
1423 * ,p_recvr_sob_id(i))
1424 * FROM pa_cost_distribution_lines CDL,
1425 * pa_expenditure_items ITEM
1426 * WHERE ITEM.expenditure_item_id = CDL.expenditure_item_id
1427 * AND CDL.line_type = 'R'
1428 * AND ITEM.expenditure_id = p_expnd_id(i)
1429 * )
1430 */
1431 WHERE CDL.Transfer_Status_Code || '' IN ('P','R')
1432 AND CDL.line_type = 'R'
1433 AND CDL.Batch_name IS NOT NULL
1434 AND CDL.Expenditure_Item_ID IN
1435 (
1436 SELECT ITEM.Expenditure_Item_ID
1437 FROM PA_Expenditure_Items ITEM
1438 WHERE ITEM.Cost_Distributed_Flag||'' = 'S'
1439 AND ITEM.expenditure_id = p_expnd_id(i)
1440 );
1441 ELSE
1442 -- If the sys_linkage received is NOT Expense-report.
1443
1444 /* Enhanced Period Processing : Commenting the code the gl_date Recvr_gl_date updation, This will
1445 Populated during Distributing the cost */
1446
1447 /****************** Bug 3668005 : GL Derivation Changes for M .************************
1448 Now in interface process the GL date will be derived for those CDLs
1449 whose GL dates fall in closed GL periods.
1450 (1)If these CDLs have not been summarized the CDLs would be updated.
1451 (2)If these CDLs have been summarized then the CDL would be reversed and a new line
1452 created with the rederived GL date while all other attribute would remain same
1453 including the PA Dates.
1454 ****************************************************************************************/
1455
1456 IF gms_pa_api2.is_grants_enabled = 'N' THEN
1457
1458 -- Commented for Bug 4374769
1459 /* select interface_labor_to_gl_flag , interface_usage_to_gl_flag , XFACE_CWK_LABOR_TO_GL_FLAG
1460 Into l_labor_to_gl , l_usage_to_gl , l_cwk_lab_to_gl
1461 from pa_implementations; */
1462
1463 --Get the GL Info into PLSQL table for all the CDL_row_Ids passed to this procedure .
1464 FOR i IN 1..p_local_set_size
1465 LOOP
1466 /* 4130583 - The following SELECT raises "no data found" exception if the interface flag
1467 corresponding to the CDL is unchecked. The exception block will set the date and
1468 period values in the local PL/SQL table to NULL and set l_interface_to_gl(i) to 'N' */
1469
1470
1471 BEGIN
1472 Select
1473 pa_utils2.get_prvdr_gl_date(
1474 CDL.gl_date
1475 ,p_application_id(i)
1476 ,p_prvdr_sob_id(i)) gl_date,
1477 pa_utils2.get_gl_period_name (
1478 pa_utils2.get_prvdr_gl_date(
1479 CDL.gl_date
1480 ,p_application_id(i)
1481 ,p_prvdr_sob_id(i))
1482 ,CDL.org_id) gl_period_name,
1483 pa_utils2.get_recvr_gl_date(
1484 CDL.recvr_gl_date
1485 ,p_application_id(i)
1486 ,p_recvr_sob_id(i)) recvr_gl_date,
1487 pa_utils2.get_gl_period_name (
1488 pa_utils2.get_recvr_gl_date(
1489 CDL.recvr_gl_date
1490 ,p_application_id(i)
1491 ,p_recvr_sob_id(i))
1492 ,nvl(EI.recvr_org_id,CDL.org_id)) recvr_gl_period_name,
1493 'Y' -- Interface to GL
1494 Into l_gl_date_new(i) , l_gl_period_new(i) , l_recvr_gl_date_new(i) , l_recvr_gl_period_new(i) , l_interface_to_gl(i)
1495 From PA_Cost_Distribution_lines CDL,PA_Expenditure_items_all EI,PA_Expenditures EXP
1496 Where CDL.Rowid = chartorowid( p_cdl_rowid(i) )
1497 AND CDL.Transfer_Status_Code in ('P','R')
1498 AND CDL.expenditure_item_id = EI.expenditure_item_id
1499 AND EXP.expenditure_id = EI.Expenditure_Id
1500 AND decode(EI.system_linkage_function /* If the interface to GL is not ticked we donot rederive the GL Dates */
1501 ,'ST',nvl(Decode(nvl(EXP.person_type, 'EMP')
1502 ,'EMP',l_labor_to_gl
1503 ,l_cwk_lab_to_gl),'N')
1504 ,'OT',nvl(Decode(nvl(EXP.person_type, 'EMP')
1505 ,'EMP',l_labor_to_gl
1506 ,l_cwk_lab_to_gl),'N')
1507 ,'VI','Y'
1508 ,'ER','Y'
1509 ,nvl(l_usage_to_gl,'N')) = 'Y';
1510 EXCEPTION
1511 WHEN NO_DATA_FOUND THEN
1512 l_gl_date_new(i) := NULL;
1513 l_gl_period_new(i) := NULL;
1514 l_recvr_gl_date_new(i) := NULL;
1515 l_recvr_gl_period_new(i) := NULL;
1516 l_interface_to_gl(i) := 'N';
1517 END;
1518 End Loop;
1519
1520 /* When the CDL is not summarized then update the CDLs with the rederived GL dates. */
1521 /* Bug 3669746 : Modified the update to check if the derived gl date/receiver gl date is null
1522 i.e. no future open periods exist then update the transfer_status_code to 'R' and reason with appropriate value.
1523 In case of rejection the date and period info is not nulled out.
1524 */
1525 /* 4130583 - l_interface_to_gl(i),CDL GL Date/Period and Receiver GL Date/Period will never be NULL.
1526 Modified update stmt accordingly */
1527
1528 /* Bug 4374769 : Both PJI Summarized and Non Summarized cdls are handled in a single update statement. The logic is as follows :
1529 a) When the CDL is not summarized then update the CDLs with the rederived GL dates.
1530 b) When the CDL to be transferred is already summarized the transfer_status_code
1531 is updated to 'X' if the Gl_date on the CDL is still in open period.
1532 If the GL period is closed then we mark the transfer_status_code to intermediate status 'Y'.
1533 For all CDL's stamped with 'Y' reversing line and new line with proper GL info will be created subsequently.
1534 (1)The reversing CDL would be created with line_type as 'I',transfer_status_code as 'G' and the same GL Date/Period as
1535 that of the original 'R' cdl.
1536 (2)The new CDL would be created with line_type as 'I', transfer_status_code as 'G' and GL Date/Period as those of the
1537 next open GL Period .
1538 (3)The Original CDL would be updated with transfer_status_code as 'X' and GL Date/Period as those of the next open
1539 GL Period. The reversed_flag will be NULL for the original 'R' cdl. */
1540
1541
1542 FORALL i IN 1..p_local_set_size
1543 UPDATE PA_Cost_Distribution_lines CDL
1544 SET CDL.request_id = p_request_id(i)
1545 ,CDL.transfer_status_code = Decode(l_interface_to_gl(i),
1546 'Y', DECODE(l_gl_date_new(i),
1547 NULL,'R',
1548 DECODE(l_recvr_gl_date_new(i)
1549 ,NULL,'R',
1550 DECODE (CDL.PJI_SUMMARIZED_FLAG,
1551 'N', 'X',
1552 DECODE (CDL.gl_date ,
1553 l_gl_date_new(i), 'X',
1554 'Y'
1555 )
1556
1557 )
1558 )
1559 ),
1560 'X'
1561 )
1562 ,CDL.Transfer_Rejection_Reason =
1563 (
1564 SELECT Meaning
1565 FROM PA_Lookups LOOK
1566 WHERE LOOK.Lookup_Type = 'TRANSFER REJECTION CODE'
1567 AND LOOK.Lookup_Code = Decode(l_interface_to_gl(i),'Y'
1568 ,DECODE(l_gl_date_new(i),NULL,'NO_GL_DATE'
1569 ,DECODE(l_recvr_gl_date_new(i),NULL,'NO_RECVR_GL_DATE',NULL)),NULL)
1570 )
1571 ,CDL.Transferred_Date = SYSDATE
1572 ,CDL.gl_date = Decode(l_interface_to_gl(i),
1573 'N',CDL.gl_date,
1574 DECODE ( CDL.PJI_SUMMARIZED_FLAG,
1575 'N', nvl(l_gl_date_new(i),CDL.gl_date) ,
1576 CDL.GL_DATE
1577 )
1578
1579 )
1580 ,CDL.gl_period_name = Decode(l_interface_to_gl(i),
1581 'N',CDL.gl_period_name,
1582 DECODE ( CDL.PJI_SUMMARIZED_FLAG,
1583 'N', nvl(l_gl_period_new(i),CDL.gl_period_name),
1584 CDL.gl_period_name
1585 )
1586
1587 )
1588 ,CDL.recvr_gl_date = Decode(l_interface_to_gl(i),
1589 'N',CDL.recvr_gl_date,
1590 DECODE ( CDL.PJI_SUMMARIZED_FLAG,
1591 'N', nvl(l_recvr_gl_date_new(i),CDL.recvr_gl_date),
1592 CDL.recvr_gl_date
1593 )
1594
1595 )
1596 ,CDL.recvr_gl_period_name = Decode(l_interface_to_gl(i),
1597 'N', CDL.recvr_gl_period_name ,
1598 DECODE ( CDL.PJI_SUMMARIZED_FLAG,
1599 'N', nvl(l_recvr_gl_period_new(i),CDL.recvr_gl_period_name),
1600 CDL.recvr_gl_period_name
1601 )
1602 )
1603 WHERE CDL.Rowid = chartorowid( p_cdl_rowid(i) )
1604 AND CDL.Transfer_Status_Code in ('P','R') ; /* Bug#3114404 */
1605 -- Commented for Bug 4374769
1606 /*AND ((CDL.pji_summarized_flag = 'N' AND l_interface_to_gl(i) = 'Y')
1607 OR l_interface_to_gl(i) = 'N') ; */
1608
1609
1610 -- When the CDL to be transferred is already summarized the transfer_status_code
1611 -- is updated to 'X' if the Gl_date on the CDL is still in open period.
1612 -- If the GL period is closed then we mark the transfer_status_code to intermediate status 'Y'.
1613 -- For all CDL's stamped with 'Y' reversing line and new line with proper GL info will be created subsequently.
1614 --(1)The Original CDL would be stamped with 'G' .
1615 --(2)The reversing CDL would have the same GL info as the original line with status 'G' .
1616 --(3)The new CDL would have the proper GL info with stutus 'X' for further processing.
1617
1618 -- The following code is commented for Bug 4374769
1619 /* FORALL i IN 1..p_local_set_size
1620 UPDATE PA_Cost_Distribution_lines CDL
1621 SET CDL.request_id = p_request_id(i)
1622 ,CDL.transfer_status_code = DECODE(l_gl_date_new(i) -- Bug 3669746
1623 , NULL ,'R'
1624 ,DECODE(l_recvr_gl_date_new(i)
1625 ,NULL,'R'
1626 ,Decode(CDL.gl_date
1627 ,l_gl_date_new(i),'X'
1628 ,'Y')))
1629 ,CDL.Transfer_Rejection_Reason = -- Bug 3669746
1630 (
1631 SELECT Meaning
1632 FROM PA_Lookups LOOK
1633 WHERE LOOK.Lookup_Type = 'TRANSFER REJECTION CODE'
1634 AND LOOK.Lookup_Code = DECODE(l_gl_date_new(i)
1635 , NULL,'NO_GL_DATE'
1636 ,DECODE(l_recvr_gl_date_new(i)
1637 ,NULL,'NO_RECVR_GL_DATE'
1638 ,NULL))
1639 )
1640 ,CDL.Transferred_Date = decode (CDL.gl_date
1641 ,l_gl_date_new(i),SYSDATE
1642 ,NULL)
1643 WHERE CDL.Rowid = chartorowid( p_cdl_rowid(i) )
1644 AND CDL.Transfer_Status_Code in ('P','R')
1645 AND CDL.pji_summarized_flag is NULL ; */
1646
1647
1648 -- Creating REVERSING and NEW CDLs .
1649
1650 /* Bug 4374769 : The line_num is passed to Pa_Costing.ReverseCdl to create reversing and new lines for the line_num that is being passed. */
1651
1652 For cdlsel in c_sel_cdl Loop
1653 Pa_Costing.ReverseCdl
1654 ( X_expenditure_item_id => cdlsel.expenditure_item_id
1655 , X_billable_flag => cdlsel.billable_flag
1656 , X_amount => NULL
1657 , X_quantity => NULL
1658 , X_burdened_cost => NULL
1659 , X_dr_ccid => NULL
1660 , X_cr_ccid => NULL
1661 , X_tr_source_accounted => 'Y'
1662 , X_line_type => cdlsel.line_type
1663 , X_user => p_user_id
1664 , X_denom_currency_code => cdlsel.denom_currency_code
1665 , X_denom_raw_cost => NULL
1666 , X_denom_burden_cost => NULL
1667 , X_acct_currency_code => cdlsel.acct_currency_code
1668 , X_acct_rate_date => cdlsel.acct_rate_date
1669 , X_acct_rate_type => cdlsel.acct_rate_type
1670 , X_acct_exchange_rate => cdlsel.acct_exchange_rate
1671 , X_acct_raw_cost => NULL
1672 , X_acct_burdened_cost => NULL
1673 , X_project_currency_code => cdlsel.project_currency_code
1674 , X_project_rate_date => cdlsel.project_rate_date
1675 , X_project_rate_type => cdlsel.project_rate_type
1676 , X_project_exchange_rate => cdlsel.project_exchange_rate
1677 , X_err_code => l_err_code
1678 , X_err_stage => l_err_stage
1679 , X_err_stack => l_err_stack
1680 , P_Projfunc_currency_code => cdlsel.projfunc_currency_code
1681 , P_Projfunc_cost_rate_date => cdlsel.projfunc_cost_rate_date
1682 , P_Projfunc_cost_rate_type => cdlsel.projfunc_cost_rate_type
1683 , P_Projfunc_cost_exchange_rate => cdlsel.projfunc_cost_exchange_rate
1684 , P_project_raw_cost => null
1685 , P_project_burdened_cost => null
1686 , P_Work_Type_Id => cdlsel.work_type_id
1687 , P_mode => 'INTERFACE'
1688 , X_line_num => cdlsel.line_num
1689 );
1690 End Loop;
1691
1692
1693 --Marking the ORIGINAL and REVERSING CDLs with transfer_status_code 'G'.
1694
1695 -- Commented for Bug 4374769
1696 /* FORALL i IN 1..p_local_set_size
1697 UPDATE PA_Cost_Distribution_lines CDL
1698 SET CDL.request_id = p_request_id(i)
1699 ,CDL.transfer_status_code = 'G'
1700 ,CDL.Transferred_Date = SYSDATE
1701 WHERE CDL.Transfer_Status_Code in ('Y')
1702 AND (CDL.line_num_reversed is NOT NULL
1703 OR CDL.reversed_flag = 'Y'); */
1704
1705 --Marking the NEWLY created CDLs with transfer_status_code 'X' for further processing.
1706 /* Bug 4374769 : The following code is modified to set the TRANSFER_STATUS_CODE to 'X' and rederive the GL_DATE, GL_PERIOD_NAME, RECVR_GL_DATE and
1707 RECVR_GL_PERIOD_NAME for the reversing and new cdls of line_type 'R' to further process and transfer them to GL. */
1708
1709 FORALL i IN 1..p_local_set_size
1710 UPDATE PA_Cost_Distribution_lines CDL
1711 SET CDL.request_id = p_request_id(i)
1712 ,CDL.transfer_status_code = 'X'
1713 ,CDL.Transferred_Date = SYSDATE
1714 ,CDL.gl_date = Decode(l_interface_to_gl(i),'N',CDL.gl_date,nvl(l_gl_date_new(i),CDL.gl_date))
1715 ,CDL.gl_period_name = Decode(l_interface_to_gl(i),'N',CDL.gl_period_name,nvl(l_gl_period_new(i),CDL.gl_period_name))
1716 ,CDL.recvr_gl_date = Decode(l_interface_to_gl(i),'N',CDL.recvr_gl_date,nvl(l_recvr_gl_date_new(i),CDL.recvr_gl_date))
1717 ,CDL.recvr_gl_period_name = Decode(l_interface_to_gl(i),'N',CDL.recvr_gl_period_name
1718 ,nvl(l_recvr_gl_period_new(i),CDL.recvr_gl_period_name))
1719 WHERE CDL.Transfer_Status_Code in ('Y')
1720 AND CDL.reversed_flag is NULL;
1721 -- Commented for Bug 4374769
1722 /* AND CDL.line_num_reversed is NULL ; */
1723
1724
1725 /*************************** Bug 3668005 Ends ****************************/
1726
1727 ELSE
1728 FORALL i IN 1..p_local_set_size
1729 UPDATE PA_Cost_Distribution_lines CDL
1730 SET CDL.request_id = p_request_id(i)
1731 ,CDL.transfer_status_code = 'X'
1732 ,CDL.Transferred_Date = SYSDATE
1733 WHERE CDL.Rowid = chartorowid( p_cdl_rowid(i) )
1734 AND CDL.Transfer_Status_Code in ('P','R'); /* Bug#3114404 */
1735
1736 END IF;
1737 END IF;
1738 END populate_gl_dates;
1739 -----------------------------------------------------------------------
1740 PROCEDURE refresh_gl_cache ( p_reference_date IN DATE,
1741 p_application_id IN NUMBER,
1742 p_set_of_books_id IN NUMBER,
1743 p_caller_flag IN VARCHAR2
1744 )
1745 IS
1746 l_earliest_start_date gl_period_statuses.start_date%TYPE;
1747 l_earliest_end_date gl_period_statuses.end_date%TYPE;
1748 l_earliest_period_name gl_period_statuses.period_name%TYPE;
1749 l_gl_date gl_period_statuses.start_date%TYPE;
1750 l_period_name gl_period_statuses.period_name%TYPE;
1751 l_start_date gl_period_statuses.start_date%TYPE; -- start date for the l_gl_date.
1752 l_end_date gl_period_statuses.start_date%TYPE; -- end date for the l_gl_date.
1753
1754 CURSOR c_get_gl_date (c_reference_date DATE) IS
1755 SELECT PERIOD.start_date,
1756 PERIOD.end_date,
1757 PERIOD.period_name
1758 FROM GL_PERIOD_STATUSES PERIOD
1759 WHERE PERIOD.application_id = p_application_id
1760 AND PERIOD.set_of_books_id = p_set_of_books_id
1761 AND PERIOD.closing_status||'' IN ('O','F')
1762 AND PERIOD.adjustment_period_flag = 'N'
1763 AND trunc(c_reference_date) BETWEEN PERIOD.start_date and PERIOD.end_date;
1764
1765 BEGIN
1766
1767 IF ( p_caller_flag = 'R' AND g_r_earliest_gl_start_date IS NULL ) OR
1768 ( p_caller_flag = 'P' AND g_p_earliest_gl_start_date IS NULL ) THEN
1769 SELECT PERIOD.start_date
1770 ,PERIOD.end_date
1771 ,PERIOD.period_name
1772 INTO l_earliest_start_date
1773 ,l_earliest_end_date
1774 ,l_earliest_period_name
1775 FROM GL_PERIOD_STATUSES PERIOD
1776 WHERE PERIOD.set_of_books_id = p_set_of_books_id
1777 AND PERIOD.application_id = p_application_id
1778 AND PERIOD.adjustment_period_flag = 'N'
1779 AND PERIOD.end_date = (
1780 SELECT MIN (PERIOD1.end_date)
1781 FROM GL_PERIOD_STATUSES PERIOD1
1782 WHERE PERIOD1.closing_status in ('O','F')
1783 AND PERIOD1.application_id = p_application_id /* Bug# 1899771 */
1784 AND PERIOD1.adjustment_period_flag = 'N' /* Bug# 1899771 */
1785 AND PERIOD1.set_of_books_id = p_set_of_books_id) ;
1786
1787 -- the earliest global variables will be populated ONLY ONCE.
1788
1789 IF ( p_caller_flag = 'P' )
1790 THEN
1791 g_p_earliest_gl_start_date := l_earliest_start_date ;
1792 g_p_earliest_gl_end_date := l_earliest_end_date ;
1793 g_p_earliest_gl_period_name := l_earliest_period_name ;
1794 ELSIF ( p_caller_flag = 'R' )
1795 THEN
1796 g_r_earliest_gl_start_date := l_earliest_start_date ;
1797 g_r_earliest_gl_end_date := l_earliest_end_date ;
1798 g_r_earliest_gl_period_name := l_earliest_period_name ;
1799 END IF;
1800 END IF ;
1801
1802 IF ( fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION') = 'N' ) /*For Bug 5391468*/
1803 THEN
1804 /*
1805 * The profile option is NOT set,(Gldate is based on the pa_date)
1806 * -- the gl date should equal the end date (if the pa date falls in a open
1807 * gl period.)
1808 * -- the gl date should equal the end date of the immediate next open
1809 * period (if the pa date doesnt fall in a open period.)
1810 */
1811
1812 SELECT PERIOD.start_date,
1813 PERIOD.end_date,
1814 PERIOD.end_date,
1815 PERIOD.period_name
1816 INTO l_start_date,
1817 l_end_date,
1818 l_gl_date,
1819 l_period_name
1820 FROM GL_PERIOD_STATUSES PERIOD
1821 WHERE PERIOD.application_id = p_application_id
1822 AND PERIOD.set_of_books_id = p_set_of_books_id
1823 AND PERIOD.effective_period_num =
1824 ( SELECT min(PERIOD1.effective_period_num)
1825 FROM GL_PERIOD_STATUSES PERIOD1
1826 WHERE PERIOD1.application_id = p_application_id
1827 AND PERIOD1.set_of_books_id = p_set_of_books_id
1828 AND PERIOD1.closing_status||'' IN ('O','F')
1829 AND PERIOD1.adjustment_period_flag = 'N'
1830 AND PERIOD1.effective_period_num >=
1831 ( SELECT PERIOD2.effective_period_num
1832 FROM GL_PERIOD_STATUSES PERIOD2,
1833 GL_DATE_PERIOD_MAP DPM,
1834 GL_SETS_OF_BOOKS SOB
1835 WHERE SOB.set_of_books_id = p_set_of_books_id
1836 AND DPM.period_set_name = SOB.period_set_name
1837 AND DPM.period_type = SOB.accounted_period_type
1838 AND trunc(DPM.accounting_date) = trunc(p_reference_date)
1839 AND DPM.period_name = PERIOD2.period_name
1840 AND PERIOD2.application_id = p_application_id
1841 AND PERIOD2.set_of_books_id = p_set_of_books_id ))
1842 AND PERIOD.End_Date >= TRUNC(p_reference_date)
1843 AND PERIOD.set_of_books_id = p_set_of_books_id ;
1844
1845 -- Populating cache.
1846 if ( p_caller_flag = 'R' ) then
1847 g_recvr_set_of_books_id := p_set_of_books_id ;
1848 g_recvr_gl_start_date := l_start_date ;
1849 g_recvr_gl_end_date := l_end_date ;
1850 g_recvr_gl_date := l_gl_date ;
1851 g_recvr_gl_period_name := l_period_name ;
1852 elsif ( p_caller_flag = 'P' ) then
1853 g_prvdr_set_of_books_id := p_set_of_books_id ;
1854 g_prvdr_gl_start_date := l_start_date ;
1855 g_prvdr_gl_end_date := l_end_date ;
1856 g_prvdr_gl_date := l_gl_date ;
1857 g_prvdr_gl_period_name := l_period_name ;
1858 end if;
1859
1860 ELSE -- profile option is SET.
1861 /*
1862 * Check whether the reference_date falls in an Open or Future Period.
1863 */
1864
1865 OPEN c_get_gl_date (p_reference_date);
1866
1867 FETCH c_get_gl_date
1868 INTO l_start_date
1869 ,l_end_date
1870 ,l_period_name;
1871
1872 IF (c_get_gl_date%NOTFOUND)
1873 THEN
1874 /*
1875 * Get the earliest available date.
1876 */
1877 SELECT PERIOD.start_date
1878 ,PERIOD.start_date
1879 ,PERIOD.end_date
1880 ,PERIOD.period_name
1881 INTO l_gl_date
1882 ,l_start_date
1883 ,l_end_date
1884 ,l_period_name
1885 FROM GL_PERIOD_STATUSES PERIOD
1886 WHERE PERIOD.application_id = p_application_id
1887 AND PERIOD.set_of_books_id = p_set_of_books_id
1888 AND PERIOD.effective_period_num =
1889 ( SELECT min(PERIOD1.effective_period_num)
1890 FROM GL_PERIOD_STATUSES PERIOD1
1891 WHERE PERIOD1.application_id = p_application_id
1892 AND PERIOD1.set_of_books_id = p_set_of_books_id
1893 AND PERIOD1.closing_status||'' IN ('O','F')
1894 AND PERIOD1.adjustment_period_flag = 'N'
1895 AND PERIOD1.effective_period_num >=
1896 ( SELECT PERIOD2.effective_period_num
1897 FROM GL_PERIOD_STATUSES PERIOD2,
1898 GL_DATE_PERIOD_MAP DPM,
1899 GL_SETS_OF_BOOKS SOB
1900 WHERE SOB.set_of_books_id = p_set_of_books_id
1901 AND DPM.period_set_name = SOB.period_set_name
1902 AND DPM.period_type = SOB.accounted_period_type
1903 AND trunc(DPM.accounting_date) = trunc(p_reference_date)
1904 AND DPM.period_name = PERIOD2.period_name
1905 AND PERIOD2.application_id = p_application_id
1906 AND PERIOD2.set_of_books_id = p_set_of_books_id ))
1907 AND PERIOD.Start_Date > TRUNC(p_reference_date);
1908 ELSE
1909 l_gl_date := p_reference_date ;
1910 END IF;
1911
1912 CLOSE c_get_gl_date;
1913
1914 -- Populating cache.
1915 if ( p_caller_flag = 'R' ) then
1916 g_recvr_set_of_books_id := p_set_of_books_id ;
1917 g_recvr_gl_start_date := l_start_date ;
1918 g_recvr_gl_end_date := l_end_date ;
1919 g_recvr_gl_date := l_gl_date ;
1920 g_recvr_gl_period_name := l_period_name ;
1921 elsif ( p_caller_flag = 'P' ) then
1922 g_prvdr_set_of_books_id := p_set_of_books_id ;
1923 g_prvdr_gl_start_date := l_start_date ;
1924 g_prvdr_gl_end_date := l_end_date ;
1925 g_prvdr_gl_date := l_gl_date ;
1926 g_prvdr_gl_period_name := l_period_name ;
1927 end if;
1928 END IF;
1929 EXCEPTION
1930 WHEN no_data_found THEN
1931 if ( p_caller_flag = 'R' ) then
1932 /** Added for 2810747 **/
1933 g_recvr_gl_start_date := NULL;
1934 g_recvr_gl_end_date := NULL;
1935 /** End Added for 2810747 **/
1936
1937 g_recvr_gl_date := NULL ;
1938 g_recvr_gl_period_name := NULL ;
1939 elsif ( p_caller_flag = 'P' ) then
1940
1941 /** Added for 2810747 **/
1942 g_prvdr_gl_start_date := NULL;
1943 g_prvdr_gl_end_date := NULL;
1944 /** End Added for 2810747 **/
1945
1946 g_prvdr_gl_date := NULL ;
1947 g_prvdr_gl_period_name := NULL ;
1948 end if;
1949 WHEN others THEN
1950 RAISE ;
1951
1952 END refresh_gl_cache ;
1953 -----------------------------------------------------------------------
1954 /*
1955 * EPP.
1956 * Modified the name of the first parameter.
1957 */
1958 FUNCTION get_prvdr_gl_date( p_reference_date IN DATE,
1959 p_application_id IN NUMBER ,
1960 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE
1961 )
1962 return date
1963 IS
1964 l_prof_new_gldate_derivation varchar2(1);
1965 BEGIN
1966 l_prof_new_gldate_derivation := fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION') ; /*For Bug 5391468*/
1967
1968 /*
1969 * Validate the input parameters.
1970 * If the essential input parameters have NULL values, set the global variables
1971 * appropriately and return NULL value.
1972 */
1973 IF ( p_reference_date IS NULL OR p_application_id IS NULL OR p_set_of_books_id IS NULL )
1974 THEN
1975 return NULL;
1976 END IF;
1977
1978 IF ( p_set_of_books_id = g_prvdr_set_of_books_id )
1979 THEN
1980 -- if sob is not the same, we HAVE to hit the DB.
1981 IF ( g_p_earliest_gl_start_date IS NOT NULL )
1982 THEN
1983 -- the cache is NOT empty.
1984 -- check whether provider_cache is re-usable..
1985 IF p_reference_date BETWEEN g_prvdr_gl_start_date AND g_prvdr_gl_end_date THEN
1986
1987 /*Added for bug 4277525 -Start */
1988 IF g_prvdr_gl_date IS NULL OR g_prvdr_gl_period_name IS null
1989 then
1990 pa_utils2.refresh_gl_cache( p_reference_date,
1991 p_application_id,
1992 p_set_of_books_id,
1993 'P'
1994 );
1995 END IF ;
1996 /*Added for bug 4277525 -End */
1997
1998 IF ( l_prof_new_gldate_derivation = 'Y' )
1999 THEN
2000 return ( p_reference_date ) ;
2001 ELSE
2002 return ( g_prvdr_gl_end_date ) ;
2003 END IF ; -- profile
2004 ELSE -- p_reference_date
2005 IF ( l_prof_new_gldate_derivation = 'Y' )
2006 THEN
2007 IF ( p_reference_date <= g_p_earliest_gl_start_date )
2008 THEN
2009 g_prvdr_gl_start_date := g_p_earliest_gl_start_date ;
2010 g_prvdr_gl_end_date := g_p_earliest_gl_end_date ;
2011 g_prvdr_gl_period_name := g_p_earliest_gl_period_name ;
2012 return ( g_p_earliest_gl_start_date ) ;
2013 END IF; -- p_reference_date
2014 ELSIF ( p_reference_date <= g_p_earliest_gl_end_date )
2015 THEN
2016 g_prvdr_gl_start_date := g_p_earliest_gl_start_date ;
2017 g_prvdr_gl_end_date := g_p_earliest_gl_end_date ;
2018 g_prvdr_gl_period_name := g_p_earliest_gl_period_name ;
2019 return ( g_p_earliest_gl_end_date ) ;
2020 END IF ; -- profile
2021 END IF ; -- p_reference_date
2022 END IF ; -- g_p_earliest_gl_start_date
2023 END IF ; -- sob
2024
2025 -- If control comes here, it means that
2026 -- 1. sob doesnt match or
2027 -- 2. cache is empty or
2028 -- 3. the Cache is not reusable.
2029 -- Access the DB and refresh cache and return gl_date.
2030
2031 pa_utils2.refresh_gl_cache( p_reference_date,
2032 p_application_id,
2033 p_set_of_books_id,
2034 'P'
2035 );
2036 return ( g_prvdr_gl_date ) ;
2037 EXCEPTION
2038 WHEN OTHERS THEN
2039 RAISE ;
2040 END get_prvdr_gl_date ;
2041 -----------------------------------------------------------------------
2042 /*
2043 * EPP.
2044 * Modified the name of the first parameter.
2045 */
2046
2047 FUNCTION get_recvr_gl_date( p_reference_date IN DATE,
2048 p_application_id IN NUMBER ,
2049 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE
2050 )
2051 return date
2052 IS
2053 l_prof_new_gldate_derivation VARCHAR2(1) := 'N';
2054 BEGIN
2055 l_prof_new_gldate_derivation := fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION') ; /*For Bug 5391468*/
2056
2057 /*
2058 * Validate the input parameters.
2059 * If the essential input parameters have NULL values,
2060 * return NULL value.
2061 */
2062 IF ( p_reference_date IS NULL OR p_application_id IS NULL OR p_set_of_books_id IS NULL )
2063 THEN
2064 return NULL;
2065 END IF;
2066
2067 IF ( p_set_of_books_id = g_recvr_set_of_books_id )
2068 THEN
2069 IF ( g_r_earliest_gl_start_date IS NOT NULL )
2070 THEN
2071 -- receiver cache is NOT empty.
2072 -- try to re-use the receiver cache.
2073 IF p_reference_date BETWEEN g_recvr_gl_start_date AND g_recvr_gl_end_date
2074 THEN
2075 IF ( l_prof_new_gldate_derivation = 'Y' )
2076 THEN
2077 return ( p_reference_date ) ;
2078 ELSE
2079 return ( g_recvr_gl_end_date ) ;
2080 END IF;
2081 ELSE
2082 IF ( l_prof_new_gldate_derivation = 'Y' )
2083 THEN
2084 IF ( p_reference_date <= g_r_earliest_gl_start_date )
2085 THEN
2086 g_recvr_gl_start_date := g_r_earliest_gl_start_date;
2087 g_recvr_gl_end_date := g_r_earliest_gl_end_date;
2088 g_recvr_gl_period_name := g_r_earliest_gl_period_name;
2089
2090 g_recvr_gl_date := g_r_earliest_gl_start_date ;
2091 return ( g_r_earliest_gl_start_date ) ;
2092 END IF; -- profile
2093 ELSIF ( p_reference_date <= g_r_earliest_gl_end_date )
2094 THEN
2095 g_recvr_gl_start_date := g_r_earliest_gl_start_date;
2096 g_recvr_gl_end_date := g_r_earliest_gl_end_date;
2097 g_recvr_gl_period_name := g_r_earliest_gl_period_name;
2098 return ( g_r_earliest_gl_end_date ) ;
2099 END IF; -- profile
2100 END IF ; -- p_reference_date
2101 ELSE -- g_r_earliest_gl_start_date
2102 -- receiver cache is empty.
2103 -- should try to use the provider cache.
2104
2105 IF ( p_set_of_books_id = g_prvdr_set_of_books_id ) THEN
2106 IF p_reference_date BETWEEN g_prvdr_gl_start_date AND g_prvdr_gl_end_date THEN
2107
2108 -- copy provider cache to receiver cache.
2109 g_recvr_gl_date := g_prvdr_gl_date ;
2110 g_r_earliest_gl_start_date := g_p_earliest_gl_start_date ;
2111 g_r_earliest_gl_end_date := g_p_earliest_gl_end_date ;
2112 g_r_earliest_gl_period_name := g_p_earliest_gl_period_name ;
2113 g_recvr_gl_start_date := g_prvdr_gl_start_date ;
2114 g_recvr_gl_end_date := g_prvdr_gl_end_date ;
2115 g_recvr_gl_period_name := g_prvdr_gl_period_name ;
2116 g_recvr_set_of_books_id := g_prvdr_set_of_books_id ;
2117 IF ( l_prof_new_gldate_derivation = 'Y' )
2118 THEN
2119 return ( p_reference_date ) ;
2120 ELSE
2121 return ( g_prvdr_gl_end_date ) ;
2122 END IF;
2123 ELSIF ( l_prof_new_gldate_derivation = 'Y' )
2124 THEN
2125 IF ( p_reference_date <= g_p_earliest_gl_start_date )
2126 THEN
2127 -- copy provider cache to receiver cache.
2128 g_r_earliest_gl_start_date := g_p_earliest_gl_start_date ;
2129 g_r_earliest_gl_end_date := g_p_earliest_gl_end_date ;
2130 g_r_earliest_gl_period_name := g_p_earliest_gl_period_name ;
2131 g_recvr_gl_start_date := g_p_earliest_gl_start_date ;
2132 g_recvr_gl_end_date := g_p_earliest_gl_end_date ;
2133 g_recvr_gl_period_name := g_p_earliest_gl_period_name ;
2134 g_recvr_set_of_books_id := g_prvdr_set_of_books_id ;
2135
2136 g_recvr_gl_date := g_prvdr_gl_date ;
2137 return ( g_p_earliest_gl_start_date ) ;
2138 ELSIF ( p_reference_date <= g_p_earliest_gl_end_date )
2139 THEN
2140 g_r_earliest_gl_start_date := g_p_earliest_gl_start_date ;
2141 g_r_earliest_gl_end_date := g_p_earliest_gl_end_date ;
2142 g_r_earliest_gl_period_name := g_p_earliest_gl_period_name ;
2143 g_recvr_gl_start_date := g_p_earliest_gl_start_date ;
2144 g_recvr_gl_end_date := g_p_earliest_gl_end_date ;
2145 g_recvr_gl_period_name := g_p_earliest_gl_period_name ;
2146 g_recvr_set_of_books_id := g_prvdr_set_of_books_id ;
2147
2148 g_recvr_gl_date := g_p_earliest_gl_end_date ;
2149 return ( g_p_earliest_gl_end_date ) ;
2150 END IF; -- p_reference_date
2151 END IF ; -- p_reference_date
2152 END IF ; -- sob
2153 END IF; -- g_r_earliest_gl_start_date
2154 END IF ; -- p_set_of_books_id
2155
2156 pa_utils2.refresh_gl_cache ( p_reference_date,
2157 p_application_id,
2158 p_set_of_books_id,
2159 'R');
2160 return ( g_recvr_gl_date ) ;
2161 EXCEPTION
2162 WHEN OTHERS THEN
2163 RAISE ;
2164 END get_recvr_gl_date ;
2165 -----------------------------------------------------------------------
2166 -- ==========================================================================
2167 -- = PROCEDURE GetProjInfo
2168 -- ==========================================================================
2169
2170 FUNCTION GetBusinessGroupId ( P_Business_Group_Name IN VARCHAR2 ) RETURN NUMBER
2171 IS
2172
2173 x_business_group_id NUMBER;
2174 BEGIN
2175
2176 IF (P_Business_Group_Name = G_PREV_BUS_GRP_NAME) THEN
2177
2178 RETURN G_PREV_BUS_GRP_ID;
2179
2180 ELSE
2181
2182 G_PREV_BUS_GRP_NAME := P_Business_Group_Name;
2183
2184 SELECT
2185 ho.organization_id
2186 INTO
2187 x_business_group_id
2188 FROM
2189 hr_all_organization_units ho
2190 WHERE
2191 ho.name = P_Business_Group_Name
2192 AND
2193 ho.organization_id = ho.business_group_id; /* Added this clause for bug 1649495 */
2194
2195 G_PREV_BUS_GRP_ID := x_business_group_id;
2196 RETURN ( x_business_group_id );
2197
2198 END IF;
2199
2200 EXCEPTION
2201 WHEN NO_DATA_FOUND THEN
2202 G_PREV_BUS_GRP_NAME := P_Business_Group_Name;
2203 G_PREV_BUS_GRP_ID := NULL;
2204 return NULL ;
2205 WHEN OTHERS THEN
2206 G_PREV_BUS_GRP_NAME := P_Business_Group_Name;
2207 G_PREV_BUS_GRP_ID := NULL;
2208
2209 RAISE ;
2210
2211 END GetBusinessGroupId;
2212
2213 -- ==========================================================================
2214 -- = FUNCTION GetEmpId
2215 -- ==========================================================================
2216 -- Fixed Bug 1534973, 1581184
2217 -- Added P_EiDate parameter and performing date check in the query
2218 -- If adding new parameters please add before P_EiDate parameter
2219
2220 -- cwk changes: added new parameter P_Person_Type and modified procedure to return the person id for
2221 --contingent workers as well as employees based on the P_Person_Type parameter
2222
2223 PROCEDURE GetEmpId ( P_Business_Group_Id IN NUMBER
2224 , P_Employee_Number IN VARCHAR2
2225 , X_Employee_Id OUT NOCOPY VARCHAR2
2226 , P_Person_Type IN VARCHAR2
2227 , P_EiDate IN DATE )
2228 IS
2229 X_person_id NUMBER;
2230 BEGIN
2231
2232 IF (p_business_group_id = G_PREV_BUSGRP_ID AND
2233 p_employee_number = G_PREV_EMP_NUM AND
2234 p_eidate = G_PREV_EI_DATE AND
2235 nvl(p_person_type,'EMP') = NVL(G_PREV_PERSON_TYPE,'EMP') AND /* Bug 3972641*/
2236 G_Return_Status IS NULL) THEN
2237
2238 x_employee_id := G_PREV_EMP_ID;
2239
2240 ELSE
2241
2242 G_PREV_BUSGRP_ID:= P_Business_Group_Id;
2243 G_PREV_EMP_NUM := P_Employee_Number;
2244 G_PREV_EI_DATE := trunc(P_EiDate);
2245 G_PREV_PERSON_TYPE := P_Person_Type;
2246
2247 IF nvl(P_Person_Type,'EMP') NOT IN ('EMP','CWK') THEN
2248 G_PREV_EMP_ID := NULL;
2249 G_Return_Status := 'INVALID_PERSON_TYPE' ;
2250 ELSE
2251
2252 /* Bug 3972641/
2253 SELECT
2254 person_id
2255 INTO
2256 X_person_id
2257 FROM
2258 per_people_f
2259 WHERE
2260 decode(p_person_type,'CWK', npw_number,employee_number) = P_Employee_Number
2261 AND (business_group_id = P_Business_Group_Id
2262 OR P_Business_Group_Id is NULL)
2263 AND trunc(P_EiDate) between trunc(effective_start_date) and trunc(effective_end_date);
2264
2265 Bug 3972641 */
2266 /* Bug 3972641 */
2267
2268 IF NVL(P_Person_Type,'EMP') = 'EMP' THEN
2269
2270 SELECT
2271 person_id
2272 INTO
2273 X_person_id
2274 FROM
2275 per_people_f
2276 WHERE
2277 employee_number = P_Employee_Number
2278 AND (business_group_id = P_Business_Group_Id
2279 OR P_Business_Group_Id is NULL)
2280 AND trunc(P_EiDate) between trunc(effective_start_date) and trunc(effective_end_date);
2281
2282 ELSE
2283
2284 SELECT
2285 person_id
2286 INTO
2287 X_person_id
2288 FROM
2289 per_people_f
2290 WHERE
2291 npw_number = P_Employee_Number
2292 AND (business_group_id = P_Business_Group_Id
2293 OR P_Business_Group_Id is NULL)
2294 AND trunc(P_EiDate) between trunc(effective_start_date) and trunc(effective_end_date);
2295
2296 END IF;
2297 /* Bug 3972641 */
2298
2299 G_PREV_EMP_ID := x_person_id;
2300 X_Employee_Id := X_person_id;
2301 G_Return_Status := NULL;
2302
2303 END IF; /* person type */
2304
2305 END IF;
2306
2307 EXCEPTION
2308 WHEN NO_DATA_FOUND THEN
2309 G_PREV_BUSGRP_ID:= P_Business_Group_Id;
2310 G_PREV_EMP_NUM := P_Employee_Number;
2311 G_PREV_EI_DATE := trunc(P_EiDate);
2312 G_PREV_EMP_ID := NULL;
2313 G_PREV_PERSON_TYPE := P_Person_Type;
2314 G_Return_Status := 'PA_INVALID_EMPLOYEE' ;
2315
2316 WHEN TOO_MANY_ROWS THEN
2317 G_PREV_BUSGRP_ID:= P_Business_Group_Id;
2318 G_PREV_EMP_NUM := P_Employee_Number;
2319 G_PREV_EI_DATE := trunc(P_EiDate);
2320 G_PREV_EMP_ID := NULL;
2321 G_PREV_PERSON_TYPE := P_Person_Type;
2322 G_Return_Status := 'PA_TOO_MANY_EMPLOYEES' ;
2323
2324 WHEN OTHERS THEN
2325 G_PREV_BUSGRP_ID:= P_Business_Group_Id;
2326 G_PREV_EMP_NUM := P_Employee_Number;
2327 G_PREV_EI_DATE := trunc(P_EiDate);
2328 G_PREV_EMP_ID := NULL;
2329 G_PREV_PERSON_TYPE := P_Person_Type;
2330 RAISE ;
2331
2332 END GetEmpId;
2333
2334 ----------------------------------------------------------------------------
2335
2336 ----------------------------------------------------------------------------
2337 -- Function : GetGlPeriodName
2338 -- This function is called by Transaction Import to get the gl period name.
2339 ----------------------------------------------------------------------------
2340 PROCEDURE GetGlPeriodNameDate( p_pa_date IN DATE,
2341 p_application_id IN NUMBER ,
2342 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
2343 x_gl_date OUT NOCOPY DATE,
2344 x_period_name OUT NOCOPY VARCHAR2
2345 )
2346 IS
2347 l_prof_new_gldate_derivation VARCHAR2(1) := 'N';
2348 BEGIN
2349
2350 l_prof_new_gldate_derivation := fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION') ; /*For Bug 5391468*/
2351
2352 IF ( p_set_of_books_id = g_prvdr_set_of_books_id )
2353 THEN
2354 -- if sob is not the same, we HAVE to hit the DB.
2355 IF ( g_p_earliest_gl_start_date IS NOT NULL )
2356 THEN
2357 -- the cache is NOT empty.
2358 -- check whether provider_cache is re-usable..
2359 IF ( p_pa_date BETWEEN g_prvdr_gl_start_date AND g_prvdr_gl_end_date )
2360 THEN
2361 IF ( l_prof_new_gldate_derivation = 'Y' )
2362 THEN
2363 x_gl_date := p_pa_date ;
2364 x_period_name := g_prvdr_gl_period_name ;
2365 ELSE
2366 x_gl_date := g_prvdr_gl_end_date ;
2367 x_period_name := g_prvdr_gl_period_name ;
2368 END IF; -- profile
2369 ELSE
2370 IF ( l_prof_new_gldate_derivation = 'Y' )
2371 THEN
2372 IF ( p_pa_date <= g_p_earliest_gl_start_date )
2373 THEN
2374 x_gl_date := g_p_earliest_gl_start_date ;
2375 x_period_name := g_p_earliest_gl_period_name ;
2376 END IF;
2377 ELSE
2378 IF (p_pa_date <= g_p_earliest_gl_end_date )
2379 THEN
2380 x_gl_date := g_p_earliest_gl_end_date ;
2381 x_period_name := g_p_earliest_gl_period_name ;
2382 END IF; -- p_pa_date
2383 END IF ; -- profile
2384 END IF ; -- p_pa_date
2385 END IF ; -- g_p_earliest_gl_start_date
2386 END IF ; -- p_set_of_books_id
2387
2388 -- If control comes here, it means that
2389 -- 1. sob doesnt match or
2390 -- 2. cache is empty or
2391 -- 3. the Cache is not reusable.
2392 -- Access the DB and refresh cache and return gl_date.
2393
2394 pa_utils2.refresh_gl_cache( p_pa_date,
2395 p_application_id,
2396 p_set_of_books_id,
2397 'P'
2398 );
2399 x_gl_date := g_prvdr_gl_date ;
2400 x_period_name := g_prvdr_gl_period_name ;
2401 EXCEPTION
2402 WHEN OTHERS THEN
2403 RAISE ;
2404 END GetGlPeriodNameDate;
2405
2406 -----------------------------------------------------------------------
2407 /*============================================================================*
2408 * This procedure, *
2409 * -- ensures that all cdls of an Expense-Report expenditure *
2410 * gets the same gl_date. *
2411 * -- ensures that the pa_date for a burden VI cdl is based on *
2412 * the pa_date on the raw_cdl. *
2413 * Bug#2103722 *
2414 * -- To derive the recvr PA information, implementations option of the exp. *
2415 * OU was being used. The code was changed to use the respective imp. *
2416 * options. *
2417 * Bug#2150196 *
2418 * -- When this procedure is called from Generate Draft invoice process, the calling module be *
2419 * either AR_INSTALLED_INVOICE or AR_NOT_INSTALLED_INVOICE. *
2420 * If AR_INSTALLED_INVOICE, to derive GL information, use AR's application id. ( 222 ). *
2421 * Also, if this procedure is called from the Generate Draft invoice process, PA and GL *
2422 * periods should not be made the same - even if implementation option Use_Same_PA_GL_Period *
2423 * is set. *
2424 * -- Shouldnt derive receiver side dates when call is from get_ou_period_information (ou *
2425 * context). In which case, no info about the receiver is available. Also when call is for *
2426 * CCDL, receiver side dates are not required. *
2427 * Capital Interest - PA.L *
2428 * o For transactions with transaction source 'Capitalized Interest', GL period is always *
2429 * derived based on application id 101 irrespective of the EPP profile option. *
2430 *==============================================================================================*/
2431
2432 PROCEDURE get_period_information ( p_expenditure_item_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
2433 ,p_expenditure_id IN pa_expenditure_items_all.expenditure_id%TYPE
2434 ,p_system_linkage_function IN pa_expenditure_items_all.system_linkage_function%TYPE
2435 ,p_line_type IN pa_cost_distribution_lines_all.line_type%TYPE
2436 ,p_prvdr_raw_pa_date IN pa_cost_distribution_lines_all.pa_date%TYPE
2437 ,p_recvr_raw_pa_date IN pa_cost_distribution_lines_all.pa_date%TYPE
2438 ,p_prvdr_raw_gl_date IN pa_cost_distribution_lines_all.gl_date%TYPE
2439 ,p_recvr_raw_gl_date IN pa_cost_distribution_lines_all.gl_date%TYPE
2440 ,p_prvdr_org_id IN pa_expenditure_items_all.org_id%TYPE
2441 ,p_recvr_org_id IN pa_expenditure_items_all.org_id%TYPE
2442 ,p_prvdr_sob_id IN pa_implementations_all.set_of_books_id%TYPE
2443 ,p_recvr_sob_id IN pa_implementations_all.set_of_books_id%TYPE
2444 ,p_calling_module IN VARCHAR2
2445 ,p_ou_context IN VARCHAR2
2446 ,x_prvdr_pa_date OUT NOCOPY pa_cost_distribution_lines_all.pa_date%TYPE
2447 ,x_prvdr_pa_period_name OUT NOCOPY pa_cost_distribution_lines_all.pa_period_name%TYPE
2448 ,x_prvdr_gl_date OUT NOCOPY pa_cost_distribution_lines_all.gl_date%TYPE
2449 ,x_prvdr_gl_period_name OUT NOCOPY pa_cost_distribution_lines_all.gl_period_name%TYPE
2450 ,x_recvr_pa_date OUT NOCOPY pa_cost_distribution_lines_all.recvr_pa_date%TYPE
2451 ,x_recvr_pa_period_name OUT NOCOPY pa_cost_distribution_lines_all.recvr_pa_period_name%TYPE
2452 ,x_recvr_gl_date OUT NOCOPY pa_cost_distribution_lines_all.recvr_gl_date%TYPE
2453 ,x_recvr_gl_period_name OUT NOCOPY pa_cost_distribution_lines_all.recvr_gl_period_name%TYPE
2454 ,x_return_status OUT NOCOPY NUMBER
2455 ,x_error_code OUT NOCOPY VARCHAR2
2456 ,x_error_stage OUT NOCOPY NUMBER
2457 )
2458 IS
2459 l_prvdr_pa_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
2460 l_prvdr_pa_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
2461 l_prvdr_gl_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
2462 l_prvdr_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE := NULL;
2463
2464 l_recvr_pa_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
2465 l_recvr_pa_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
2466 l_recvr_gl_date pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
2467 l_recvr_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE := NULL;
2468
2469 l_pa_gl_app_id NUMBER := 8721 ;
2470 l_gl_app_id NUMBER := 101;
2471 l_ar_app_id NUMBER := 222;
2472 l_app_id NUMBER := NULL ;
2473
2474 TYPE DeriveType IS RECORD (receiver VARCHAR2(1) := 'Y'
2475 );
2476 derive DeriveType;
2477
2478
2479
2480 /*
2481 * Processing related variables.
2482 */
2483 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
2484 l_error_code VARCHAR2(30);
2485 l_error_stage VARCHAR2(30);
2486 l_debug_mode VARCHAR2(1);
2487 l_stage NUMBER ;
2488
2489 l_prof_new_gldate_derivation VARCHAR2(1) := 'N';
2490 l_use_same_pa_gl_period_prvdr VARCHAR2(1) := 'N' ;
2491 l_use_same_pa_gl_period_recvr VARCHAR2(1) := 'N' ;
2492 BEGIN
2493
2494 x_return_status := -1 ;
2495 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2496 l_debug_mode := NVL(l_debug_mode, 'N');
2497 l_stage := 100;
2498 IF (l_debug_mode = 'Y') THEN
2499 pa_debug.init_err_stack('pa_utils2.get_period_information');
2500 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2501 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':From get_period_information';
2502 pa_debug.write_file(pa_debug.g_err_stage);
2503 END IF;
2504
2505 if g_prof_new_gldate_derivation IS NULL then
2506 l_prof_new_gldate_derivation := nvl(fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION'),'N') ; /*For Bug 5391468*/
2507 g_prof_new_gldate_derivation := l_prof_new_gldate_derivation;
2508 else
2509 l_prof_new_gldate_derivation := g_prof_new_gldate_derivation;
2510 end if;
2511 l_use_same_pa_gl_period_prvdr := NVL(PA_PERIOD_PROCESS_PKG.Use_Same_PA_GL_Period(p_prvdr_org_id), 'N') ;
2512 l_use_same_pa_gl_period_recvr := NVL(PA_PERIOD_PROCESS_PKG.Use_Same_PA_GL_Period(p_recvr_org_id), 'N') ;
2513
2514 IF ( l_debug_mode = 'Y' ) THEN
2515 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Profile option is [' || l_prof_new_gldate_derivation || ']';
2516 pa_debug.write_file(pa_debug.g_err_stage);
2517 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Same PA and GL for Prvdr [' || to_char(p_prvdr_org_id) || '] is ['
2518 || l_use_same_pa_gl_period_prvdr || ']' ;
2519 pa_debug.write_file(pa_debug.g_err_stage);
2520 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Same PA and GL for Recvr [' || to_char(p_recvr_org_id) || '] is ['
2521 || l_use_same_pa_gl_period_recvr || ']' ;
2522 pa_debug.write_file(pa_debug.g_err_stage);
2523 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_expenditure_item_date is [' || to_char(p_expenditure_item_date) || ']';
2524 pa_debug.write_file(pa_debug.g_err_stage);
2525 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_expenditure_id is [' || to_char(p_expenditure_id) || ']';
2526 pa_debug.write_file(pa_debug.g_err_stage);
2527 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_system_linkage_function is [' || p_system_linkage_function || ']';
2528 pa_debug.write_file(pa_debug.g_err_stage);
2529 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_line_type is [' || p_line_type || ']';
2530 pa_debug.write_file(pa_debug.g_err_stage);
2531 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_prvdr_raw_pa_date is [' || to_char(p_prvdr_raw_pa_date) || ']';
2532 pa_debug.write_file(pa_debug.g_err_stage);
2533 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_recvr_raw_pa_date is [' || to_char(p_recvr_raw_pa_date) || ']';
2534 pa_debug.write_file(pa_debug.g_err_stage);
2535 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_prvdr_raw_gl_date is [' || to_char(p_prvdr_raw_gl_date) || ']';
2536 pa_debug.write_file(pa_debug.g_err_stage);
2537 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_recvr_raw_gl_date is [' || to_char(p_recvr_raw_gl_date) || ']';
2538 pa_debug.write_file(pa_debug.g_err_stage);
2539 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_prvdr_org_id is [' || to_char(p_prvdr_org_id) || ']';
2540 pa_debug.write_file(pa_debug.g_err_stage);
2541 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_recvr_org_id is [' || to_char(p_recvr_org_id) || ']';
2542 pa_debug.write_file(pa_debug.g_err_stage);
2543 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_prvdr_sob_id is [' || to_char(p_prvdr_sob_id) || ']';
2544 pa_debug.write_file(pa_debug.g_err_stage);
2545 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_recvr_sob_id is [' || to_char(p_recvr_sob_id) || ']';
2546 pa_debug.write_file(pa_debug.g_err_stage);
2547 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': p_calling_module is [' || p_calling_module || ']';
2548 pa_debug.write_file(pa_debug.g_err_stage);
2549 END IF;
2550
2551 /*
2552 * Decide whether to derive the receiver part.
2553 */
2554 IF ( p_ou_context = 'Y' OR p_calling_module = 'CCDL' )
2555 THEN
2556 derive.receiver := 'N' ;
2557 END IF;
2558
2559
2560 IF ( l_prof_new_gldate_derivation ='Y' )
2561 THEN
2562 /*
2563 * Get Gl periods based on ei date.
2564 */
2565 IF (p_system_linkage_function = 'ER' AND p_expenditure_id = g_prev_expenditure_id
2566 AND g_prev_expenditure_id IS NOT NULL AND p_calling_module = 'CDL')
2567 THEN
2568 l_stage := 200;
2569 IF ( l_debug_mode = 'Y' )
2570 THEN
2571 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': ER - Same Expenditure GL Cache used.' ;
2572 pa_debug.write_file(pa_debug.g_err_stage);
2573 END IF;
2574 l_prvdr_gl_date := g_prev_prvdr_gl_date;
2575 l_prvdr_gl_period_name := g_prev_prvdr_gl_period_name;
2576 l_recvr_gl_date := g_prev_recvr_gl_date;
2577 l_recvr_gl_period_name := g_prev_recvr_gl_period_name;
2578 ELSIF ( p_line_type <> 'R' AND p_system_linkage_function = 'VI' )
2579 THEN
2580 l_stage := 300;
2581 IF ( l_debug_mode = 'Y' )
2582 THEN
2583 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Burden VI.' ;
2584 pa_debug.write_file(pa_debug.g_err_stage);
2585 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Deriving GL Info.';
2586 pa_debug.write_file(pa_debug.g_err_stage);
2587 END IF; -- debug
2588 /*
2589 * The gl_date for the burden CDL is derived based on the gl_date in the
2590 * Raw CDL.(if the Profile option is SET.) If the profile option is NOT set
2591 * gl_date for the burden CDL is derived based on the pa_date in the Raw CDL.
2592 */
2593 IF ( p_calling_module = 'CDL' AND p_prvdr_raw_gl_date IS NOT NULL )
2594 THEN
2595 l_prvdr_gl_date := pa_utils2.get_prvdr_gl_date( p_reference_date => p_prvdr_raw_gl_date
2596 ,p_application_id => l_pa_gl_app_id
2597 ,p_set_of_books_id => p_prvdr_sob_id
2598 );
2599 l_prvdr_gl_period_name := g_prvdr_gl_period_name;
2600 END IF; -- p_calling_module
2601
2602 IF ( p_calling_module = 'CDL' AND p_recvr_raw_gl_date IS NOT NULL )
2603 THEN
2604 l_recvr_gl_date := pa_utils2.get_recvr_gl_date( p_reference_date => p_recvr_raw_gl_date
2605 ,p_application_id => l_pa_gl_app_id
2606 ,p_set_of_books_id => p_recvr_sob_id
2607 );
2608 l_recvr_gl_period_name := g_recvr_gl_period_name;
2609 END IF;
2610 ELSE
2611 l_stage := 400;
2612 IF ( l_debug_mode = 'Y' )
2613 THEN
2614 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': New ER expenditure/ Raw VI/ Other.' ;
2615 pa_debug.write_file(pa_debug.g_err_stage);
2616 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Deriving GL Info.';
2617 pa_debug.write_file(pa_debug.g_err_stage);
2618 END IF; -- debug
2619 IF ( p_calling_module = 'AR_INSTALLED_INVOICE' )
2620 THEN
2621 l_app_id := l_ar_app_id ;
2622 ELSIF ( p_calling_module = 'AR_NOT_INSTALLED_INVOICE' ) -- Bug 3710905
2623 THEN
2624 l_app_id := l_gl_app_id ;
2625 ELSE
2626 l_app_id := l_pa_gl_app_id ;
2627 END IF;
2628
2629 IF ( p_calling_module = 'CAP_INT' )
2630 THEN
2631 l_app_id := l_pa_gl_app_id; /* Modified l_gl_app_id for Bug 6904977 */
2632 END IF;
2633
2634 l_prvdr_gl_date := pa_utils2.get_prvdr_gl_date( p_reference_date => p_expenditure_item_date
2635 ,p_application_id => l_app_id
2636 ,p_set_of_books_id => p_prvdr_sob_id
2637 );
2638 l_prvdr_gl_period_name := g_prvdr_gl_period_name;
2639
2640 IF ( derive.receiver <> 'N' AND p_expenditure_item_date IS NOT NULL )
2641 THEN
2642 l_recvr_gl_date := pa_utils2.get_recvr_gl_date( p_reference_date => p_expenditure_item_date
2643 ,p_application_id => l_app_id
2644 ,p_set_of_books_id => p_recvr_sob_id
2645 );
2646 l_recvr_gl_period_name := g_recvr_gl_period_name;
2647 END IF;
2648 END IF; -- expense report check
2649 /*
2650 * Deriving PA periods for Provider.
2651 */
2652 IF ( l_use_same_pa_gl_period_prvdr = 'Y' AND
2653 ( p_calling_module <> 'AR_INSTALLED_INVOICE' OR p_calling_module <> 'AR_NOT_INSTALLED_INVOICE')
2654 )
2655 THEN
2656 l_stage := 500;
2657 IF ( l_debug_mode = 'Y' )
2658 THEN
2659 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Copying Provider GL info to Provider PA';
2660 pa_debug.write_file(pa_debug.g_err_stage);
2661 END IF; -- debug
2662 /*
2663 * Copy Provider Gl period information to Provider Pa periods.
2664 */
2665 l_prvdr_pa_date := l_prvdr_gl_date;
2666 l_prvdr_pa_period_name := l_prvdr_gl_period_name;
2667 ELSE
2668 /*
2669 * Get Provider Pa periods based on ei date.
2670 */
2671 l_stage := 500;
2672 IF ( l_debug_mode = 'Y' )
2673 THEN
2674 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Deriving Provider PA Info.';
2675 pa_debug.write_file(pa_debug.g_err_stage);
2676 END IF; -- debug
2677 IF (p_line_type <> 'R' AND p_system_linkage_function = 'VI')
2678 THEN
2679 IF ( p_prvdr_raw_pa_date IS NOT NULL )
2680 THEN
2681 l_prvdr_pa_date := pa_utils2.get_pa_date( p_ei_date => p_prvdr_raw_pa_date
2682 ,p_gl_date => SYSDATE
2683 ,p_org_id => p_prvdr_org_id
2684 );
2685 l_prvdr_pa_period_name := g_prvdr_pa_period_name;
2686 END IF;
2687 ELSE
2688 IF ( p_expenditure_item_date IS NOT NULL )
2689 THEN
2690 l_prvdr_pa_date := pa_utils2.get_pa_date( p_ei_date => p_expenditure_item_date
2691 ,p_gl_date => SYSDATE
2692 ,p_org_id => p_prvdr_org_id
2693 );
2694 l_prvdr_pa_period_name := g_prvdr_pa_period_name;
2695 END IF;
2696 END IF; -- burden VI cdl check.
2697 END IF; -- implementations option
2698 /*
2699 * Deriving PA periods for Receiver.
2700 * Receiver information need not be derived if calling module is CCDL.
2701 */
2702 IF ( p_calling_module <> 'CCDL' )
2703 THEN
2704 IF ( l_use_same_pa_gl_period_recvr = 'Y' AND p_calling_module <> 'AR_INSTALLED_INVOICE' )
2705 THEN
2706 /*
2707 * Copy Receiver Gl period information to Receiver Pa periods.
2708 */
2709 l_stage := 600;
2710 IF ( l_debug_mode = 'Y' )
2711 THEN
2712 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Copying Receiver GenLedg Info to Receiver ProjAacc';
2713 pa_debug.write_file(pa_debug.g_err_stage);
2714 END IF; -- debug
2715 l_recvr_pa_date := l_recvr_gl_date;
2716 l_recvr_pa_period_name := l_recvr_gl_period_name;
2717 ELSE
2718 /*
2719 * Get Receiver Pa periods based on ei date.
2720 */
2721 l_stage := 700;
2722 IF ( l_debug_mode = 'Y' )
2723 THEN
2724 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Deriving Receiver PA Info.';
2725 pa_debug.write_file(pa_debug.g_err_stage);
2726 END IF; -- debug
2727 IF (p_line_type <> 'R' AND p_system_linkage_function = 'VI')
2728 THEN
2729 IF ( derive.receiver <> 'N' AND p_recvr_raw_pa_date IS NOT NULL )
2730 THEN
2731 l_recvr_pa_date := pa_utils2.get_recvr_pa_date( p_ei_date => p_recvr_raw_pa_date
2732 ,p_gl_date => SYSDATE
2733 ,p_org_id => p_recvr_org_id
2734 );
2735 l_recvr_pa_period_name := g_recvr_pa_period_name;
2736 END IF;
2737 ELSE
2738 IF ( derive.receiver <> 'N' AND p_expenditure_item_date IS NOT NULL )
2739 THEN
2740 l_recvr_pa_date := pa_utils2.get_recvr_pa_date( p_ei_date => p_expenditure_item_date
2741 ,p_gl_date => SYSDATE
2742 ,p_org_id => p_recvr_org_id
2743 );
2744 l_recvr_pa_period_name := g_recvr_pa_period_name;
2745 END IF;
2746 END IF; -- burden VI cdl check.
2747 END IF; -- implementations option
2748 END IF ; -- CCDL Check
2749 ELSE
2750 /*
2751 * Profile Option is NOT SET.
2752 */
2753 /*
2754 * Get Pa periods based on ei date.
2755 * Get Gl periods based on above derived Pa date.
2756 */
2757 IF (p_line_type <> 'R' AND p_system_linkage_function = 'VI')
2758 THEN
2759 -- this area has to be revisited.
2760 IF ( p_prvdr_raw_pa_date IS NOT NULL )
2761 THEN
2762 l_prvdr_pa_date := pa_utils2.get_pa_date( p_ei_date => p_prvdr_raw_pa_date
2763 ,p_gl_date => SYSDATE
2764 ,p_org_id => p_prvdr_org_id
2765 );
2766 l_prvdr_pa_period_name := g_prvdr_pa_period_name;
2767 END IF;
2768 IF ( derive.receiver <> 'N' AND p_recvr_raw_pa_date IS NOT NULL )
2769 THEN
2770 l_recvr_pa_date := pa_utils2.get_recvr_pa_date( p_ei_date => p_recvr_raw_pa_date
2771 ,p_gl_date => SYSDATE
2772 ,p_org_id => p_recvr_org_id
2773 );
2774 l_recvr_pa_period_name := g_recvr_pa_period_name;
2775 END IF; -- p_calling_module
2776 ELSE
2777 IF ( p_expenditure_item_date IS NOT NULL )
2778 THEN
2779 l_prvdr_pa_date := pa_utils2.get_pa_date( p_ei_date => p_expenditure_item_date
2780 ,p_gl_date => SYSDATE
2781 ,p_org_id => p_prvdr_org_id
2782 );
2783 l_prvdr_pa_period_name := g_prvdr_pa_period_name;
2784 END IF;
2785
2786 IF ( derive.receiver <> 'N' AND p_expenditure_item_date IS NOT NULL )
2787 THEN
2788 l_recvr_pa_date := pa_utils2.get_recvr_pa_date( p_ei_date => p_expenditure_item_date
2789 ,p_gl_date => SYSDATE
2790 ,p_org_id => p_recvr_org_id
2791 );
2792 l_recvr_pa_period_name := g_recvr_pa_period_name;
2793 END IF; -- p_calling_module
2794 END IF; -- burden VI cdl check.
2795 IF (p_system_linkage_function = 'ER' AND p_expenditure_id = g_prev_expenditure_id
2796 AND g_prev_expenditure_id IS NOT NULL AND p_calling_module = 'CDL')
2797 THEN
2798 IF ( l_debug_mode = 'Y' )
2799 THEN
2800 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': ER - Same Expenditure Populating GL from Cache';
2801 pa_debug.write_file(pa_debug.g_err_stage);
2802 END IF; -- debug
2803 l_prvdr_gl_date := g_prev_prvdr_gl_date;
2804 l_prvdr_gl_period_name := g_prev_prvdr_gl_period_name;
2805 l_recvr_gl_date := g_prev_recvr_gl_date;
2806 l_recvr_gl_period_name := g_prev_recvr_gl_period_name;
2807 ELSE -- Either system_linkage is NOT 'ER' or expenditure_id has changed.
2808 IF ( l_debug_mode = 'Y' )
2809 THEN
2810 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': NOT ER/exp_id has changed.' ;
2811 pa_debug.write_file(pa_debug.g_err_stage);
2812 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Deriving GL Info.';
2813 pa_debug.write_file(pa_debug.g_err_stage);
2814 END IF; -- debug
2815
2816 IF ( p_calling_module = 'AR_INSTALLED_INVOICE' )
2817 THEN
2818 l_app_id := l_ar_app_id ;
2819 ELSE
2820 l_app_id := l_gl_app_id ;
2821 END IF;
2822
2823 IF ( p_calling_module = 'CAP_INT' )
2824 THEN
2825 l_app_id := l_pa_gl_app_id; /* Modified l_gl_app_id for Bug 6904977 */
2826 END IF;
2827
2828 /* Bug 2965043: Passing expenditure item date instead of pa dates */
2829 /*Bug# 3617395 :Modified fix of 2965043 */
2830 IF (p_calling_module = 'AR_INSTALLED_INVOICE' OR p_calling_module = 'AR_NOT_INSTALLED_INVOICE')
2831 THEN
2832 IF ( p_expenditure_item_date IS NOT NULL )
2833 THEN
2834 l_prvdr_gl_date := pa_utils2.get_prvdr_gl_date( p_reference_date => p_expenditure_item_date
2835 ,p_application_id => l_app_id
2836 ,p_set_of_books_id => p_prvdr_sob_id
2837 );
2838 l_prvdr_gl_period_name := g_prvdr_gl_period_name;
2839 END IF;
2840
2841 IF ( derive.receiver <> 'N' AND p_expenditure_item_date IS NOT NULL )
2842 THEN
2843 l_recvr_gl_date := pa_utils2.get_recvr_gl_date( p_reference_date => p_expenditure_item_date
2844 ,p_application_id => l_app_id
2845 ,p_set_of_books_id => p_recvr_sob_id
2846 );
2847 /* Code changes end for bug 2965043 */
2848 l_recvr_gl_period_name := g_recvr_gl_period_name;
2849 END IF;
2850 ELSE /*Bug# 3617395*/
2851 IF ( l_prvdr_pa_date IS NOT NULL )
2852 THEN
2853 l_prvdr_gl_date := pa_utils2.get_prvdr_gl_date( p_reference_date => l_prvdr_pa_date
2854 ,p_application_id => l_app_id
2855 ,p_set_of_books_id => p_prvdr_sob_id
2856 );
2857 l_prvdr_gl_period_name := g_prvdr_gl_period_name;
2858 END IF;
2859
2860 IF ( derive.receiver <> 'N' AND l_recvr_pa_date IS NOT NULL )
2861 THEN
2862 l_recvr_gl_date := pa_utils2.get_recvr_gl_date( p_reference_date => l_recvr_pa_date
2863 ,p_application_id => l_app_id
2864 ,p_set_of_books_id => p_recvr_sob_id
2865 );
2866 l_recvr_gl_period_name := g_recvr_gl_period_name;
2867 END IF;
2868 END IF; -- p_calling_module /*Bug# 3617395*/
2869 END IF; -- expense report check
2870 END IF; -- profile option
2871 /*
2872 * Caching.
2873 * Caching not required for ccdl.
2874 */
2875 IF (p_system_linkage_function = 'ER' AND p_calling_module = 'CCDL' )
2876 THEN
2877 IF (g_prev_expenditure_id <> p_expenditure_id OR g_prev_expenditure_id IS NULL )
2878 THEN
2879 IF ( l_debug_mode = 'Y' )
2880 THEN
2881 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': ER - New Expenditure - Populating Cache.';
2882 pa_debug.write_file(pa_debug.g_err_stage);
2883 END IF; -- debug
2884
2885 /*
2886 * Indicates new expenditure batch.
2887 */
2888 g_prev_expenditure_id := p_expenditure_id;
2889 g_prev_prvdr_gl_date := l_prvdr_gl_date;
2890 g_prev_prvdr_gl_period_name := l_prvdr_gl_period_name;
2891 g_prev_recvr_gl_date := l_recvr_gl_date;
2892 g_prev_recvr_gl_period_name := l_recvr_gl_period_name;
2893
2894 IF ( l_debug_mode = 'Y' )
2895 THEN
2896 pa_debug.g_err_stage := TO_CHAR(l_stage) ||
2897 ': g_prev_expenditure_id is [' || to_char(g_prev_expenditure_id) ||
2898 '] g_prev_prvdr_gl_date [' || to_char(g_prev_prvdr_gl_date) ||
2899 '] g_prev_prvdr_gl_period_name [' || g_prev_prvdr_gl_period_name ||']';
2900 pa_debug.write_file(pa_debug.g_err_stage);
2901 pa_debug.g_err_stage := TO_CHAR(l_stage) ||
2902 ': g_prev_recvr_gl_date is [' || to_char(g_prev_recvr_gl_date) ||
2903 '] g_prev_recvr_gl_period_name [' || g_prev_recvr_gl_period_name ||']';
2904 pa_debug.write_file(pa_debug.g_err_stage);
2905 END IF; -- debug
2906 ELSE
2907 /*
2908 * Leave the cache as it is.
2909 */
2910 NULL;
2911 END IF;
2912 ELSE -- system link is NOT 'ER'
2913 IF ( l_debug_mode = 'Y' )
2914 THEN
2915 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Not an ER. Wiping the Cache.';
2916 pa_debug.write_file(pa_debug.g_err_stage);
2917 END IF; -- debug
2918
2919 /*
2920 * Wipe-off the cache.
2921 */
2922 g_prev_prvdr_gl_date := NULL;
2923 g_prev_prvdr_gl_period_name := NULL;
2924 g_prev_recvr_gl_date := NULL;
2925 g_prev_recvr_gl_period_name := NULL;
2926 g_prev_expenditure_id := NULL;
2927 END IF; -- system link check.
2928
2929 /*
2930 * Populate the out variables.
2931 */
2932 /*
2933 * Check the availability of periods and set the error_code.
2934 */
2935 IF (l_prvdr_pa_date IS NULL OR l_prvdr_pa_period_name IS NULL)
2936 THEN
2937 x_error_code := 'NO_PA_DATE';
2938 ELSIF (l_prvdr_gl_date IS NULL OR l_prvdr_gl_period_name IS NULL)
2939 THEN
2940 x_error_code := 'NO_PRVDR_GL_DATE';
2941 ELSIF ( derive.receiver <> 'N' AND ( l_recvr_pa_date IS NULL OR l_recvr_pa_period_name IS NULL) )
2942 THEN
2943 x_error_code := 'NO_RECVR_PA_DATE';
2944 ELSIF ( derive.receiver <> 'N' AND ( l_recvr_gl_date IS NULL OR l_recvr_gl_period_name IS NULL) )
2945 THEN
2946 x_error_code := 'NO_RECVR_GL_DATE';
2947 END IF;
2948
2949 x_prvdr_pa_date := l_prvdr_pa_date;
2950 x_prvdr_pa_period_name := l_prvdr_pa_period_name;
2951 x_prvdr_gl_date := l_prvdr_gl_date;
2952 x_prvdr_gl_period_name := l_prvdr_gl_period_name;
2953 x_recvr_pa_date := l_recvr_pa_date;
2954 x_recvr_pa_period_name := l_recvr_pa_period_name;
2955 x_recvr_gl_date := l_recvr_gl_date;
2956 x_recvr_gl_period_name := l_recvr_gl_period_name;
2957
2958 IF ( l_debug_mode = 'Y' )
2959 THEN
2960 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': x_prvdr_pa_date is [' || to_char(x_prvdr_pa_date) ||
2961 '] x_prvdr_pa_period_name is ['|| x_prvdr_pa_period_name || ']';
2962 pa_debug.write_file(pa_debug.g_err_stage);
2963 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': x_prvdr_gl_date is [' || to_char(x_prvdr_gl_date) ||
2964 '] x_prvdr_gl_period_name is ['|| x_prvdr_gl_period_name || ']';
2965 pa_debug.write_file(pa_debug.g_err_stage);
2966 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': x_recvr_pa_date is [' || to_char(x_recvr_pa_date) ||
2967 '] x_recvr_pa_period_name is ['|| x_recvr_pa_period_name || ']';
2968 pa_debug.write_file(pa_debug.g_err_stage);
2969 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': x_recvr_gl_date is [' || to_char(x_recvr_gl_date) ||
2970 '] x_recvr_gl_period_name is ['|| x_recvr_gl_period_name || ']';
2971 pa_debug.write_file(pa_debug.g_err_stage);
2972 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': x_error_code is [' || x_error_code || ']';
2973 pa_debug.write_file(pa_debug.g_err_stage);
2974 END IF;
2975
2976 x_return_status := 0;
2977 pa_debug.reset_err_stack;
2978
2979 EXCEPTION
2980 WHEN others THEN
2981 x_error_stage := l_stage ;
2982 RAISE ;
2983
2984 END get_period_information ;
2985 -----------------------------------------------------------------------
2986 PROCEDURE get_OU_period_information ( p_reference_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
2987 ,p_calling_module IN VARCHAR2
2988 ,x_pa_date OUT NOCOPY pa_cost_distribution_lines_all.pa_date%TYPE
2989 ,x_pa_period_name OUT NOCOPY pa_cost_distribution_lines_all.pa_period_name%TYPE
2990 ,x_gl_date OUT NOCOPY pa_cost_distribution_lines_all.gl_date%TYPE
2991 ,x_gl_period_name OUT NOCOPY pa_cost_distribution_lines_all.gl_period_name%TYPE
2992 ,x_return_status OUT NOCOPY NUMBER
2993 ,x_error_code OUT NOCOPY VARCHAR2
2994 ,x_error_stage OUT NOCOPY NUMBER
2995 )
2996 IS
2997 l_org_id pa_implementations_all.org_id%TYPE;
2998 l_sob_id pa_implementations_all.set_of_books_id%TYPE;
2999
3000 l_pa_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
3001 l_pa_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
3002 l_gl_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
3003 l_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE := NULL;
3004
3005 l_return_status NUMBER ;
3006 l_stage NUMBER ;
3007 l_error_code VARCHAR2(30);
3008 l_debug_mode VARCHAR2(1);
3009
3010 l_date_dummy DATE;
3011 l_name_dummy VARCHAR2(30);
3012 l_calling_module VARCHAR2(30) := NULL ;
3013 BEGIN
3014 pa_debug.init_err_stack('pa_utils2.get_ou_period_information');
3015
3016 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3017 l_debug_mode := NVL(l_debug_mode, 'N');
3018
3019 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3020
3021 l_stage := 100;
3022 IF ( l_debug_mode = 'Y' )
3023 THEN
3024 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':From get_ou_period_information';
3025 pa_debug.write_file(pa_debug.g_err_stage);
3026 END IF; -- debug
3027
3028 x_return_status := -1 ;
3029 l_calling_module := p_calling_module ;
3030
3031 SELECT NVL(imp.org_id, -99)
3032 ,imp.set_of_books_id
3033 INTO l_org_id
3034 ,l_sob_id
3035 FROM pa_implementations imp;
3036
3037 l_stage := 200 ;
3038 IF ( l_debug_mode = 'Y' )
3039 THEN
3040 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Calling pa_utils2.get_period_information';
3041 pa_debug.write_file(pa_debug.g_err_stage);
3042 END IF; -- debug
3043
3044 pa_utils2.get_period_information
3045 ( p_expenditure_item_date => p_reference_date
3046 ,p_prvdr_org_id => l_org_id
3047 ,p_prvdr_sob_id => l_sob_id
3048 ,p_calling_module => l_calling_module
3049 ,p_ou_context => 'Y'
3050 ,x_prvdr_pa_date => l_pa_date
3051 ,x_prvdr_pa_period_name => l_pa_period_name
3052 ,x_prvdr_gl_date => l_gl_date
3053 ,x_prvdr_gl_period_name => l_gl_period_name
3054 ,x_recvr_pa_date => l_date_dummy
3055 ,x_recvr_pa_period_name => l_name_dummy
3056 ,x_recvr_gl_date => l_date_dummy
3057 ,x_recvr_gl_period_name => l_name_dummy
3058 ,x_return_status => l_return_status
3059 ,x_error_code => l_error_code
3060 ,x_error_stage => l_stage
3061 );
3062
3063 l_stage := 300 ;
3064 IF ( l_debug_mode = 'Y' )
3065 THEN
3066 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After call to pa_utils2.get_period_information';
3067 pa_debug.write_file(pa_debug.g_err_stage);
3068 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':PA date [' || to_char(l_pa_date) || '] name [' || l_pa_period_name || ']';
3069 pa_debug.write_file(pa_debug.g_err_stage);
3070 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':GL date [' || to_char(l_gl_date) || '] name [' || l_gl_period_name || ']';
3071 pa_debug.write_file(pa_debug.g_err_stage);
3072 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Error Code [' || l_error_code || ']';
3073 pa_debug.write_file(pa_debug.g_err_stage);
3074 END IF; -- debug
3075
3076 /*
3077 * Populate the out variables.
3078 */
3079 x_pa_date := l_pa_date;
3080 x_pa_period_name := l_pa_period_name;
3081 x_gl_date := l_gl_date;
3082 x_gl_period_name := l_gl_period_name;
3083 x_error_code := l_error_code;
3084
3085 x_return_status := 0;
3086 pa_debug.reset_err_stack;
3087
3088 EXCEPTION
3089 WHEN OTHERS THEN
3090 x_error_stage := l_stage ;
3091 RAISE;
3092 END get_OU_period_information;
3093
3094 -----------------------------------------------------------------------
3095 FUNCTION get_gl_period_name ( p_gl_date IN pa_cost_distribution_lines_all.gl_date%TYPE
3096 ,p_org_id IN pa_cost_distribution_lines_all.org_id%TYPE
3097 )
3098 RETURN pa_cost_distribution_lines_all.gl_period_name%TYPE
3099 IS
3100 l_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE ;
3101 l_gl_period_start_date pa_cost_distribution_lines_all.gl_date%TYPE ;
3102 l_gl_period_end_date pa_cost_distribution_lines_all.gl_date%TYPE ;
3103
3104 BEGIN
3105 /*
3106 * Try to reuse the global cache.
3107 * Otherwise, hit the database.
3108 */
3109 IF ( NVL(p_org_id, -99) = NVL(g_org_id, -99) AND
3110 TRUNC(p_gl_date) BETWEEN TRUNC(g_gl_period_start_date) AND TRUNC(g_gl_period_end_date))
3111 THEN
3112 RETURN g_gl_period_name ;
3113 ELSE
3114 SELECT glp.period_name
3115 ,glp.start_date
3116 ,glp.end_date
3117 INTO l_gl_period_name
3118 ,l_gl_period_start_date
3119 ,l_gl_period_end_date
3120 FROM gl_periods glp
3121 ,gl_sets_of_books glsob
3122 ,pa_implementations_all imp
3123 WHERE glsob.period_set_name = glp.period_set_name
3124 AND glp.period_type = glsob.accounted_period_type
3125 AND glp.adjustment_period_flag <> 'Y'
3126 AND glsob.set_of_books_id = imp.set_of_books_id
3127 AND TRUNC(p_gl_date) BETWEEN TRUNC(glp.start_date) AND TRUNC(glp.end_date)
3128 AND imp.org_id = p_org_id; --removed nvl for bug#6343739
3129 /*
3130 * Refresh the global variables.
3131 */
3132 g_org_id := p_org_id ;
3133 g_gl_period_start_date := l_gl_period_start_date ;
3134 g_gl_period_end_date := l_gl_period_end_date ;
3135 g_gl_period_name := l_gl_period_name ;
3136
3137 RETURN l_gl_period_name ;
3138 END IF;
3139
3140 EXCEPTION
3141 WHEN OTHERS
3142 THEN
3143 l_gl_period_name := NULL ;
3144 RAISE;
3145 END get_gl_period_name ;
3146 ---------------------------------------------------------------
3147 FUNCTION get_set_of_books_id (p_org_id IN pa_implementations_all.org_id%TYPE)
3148 RETURN NUMBER IS
3149 l_set_of_books_id pa_implementations_all.set_of_books_id%TYPE;
3150 BEGIN
3151
3152 SELECT imp.set_of_books_id
3153 INTO l_set_of_books_id
3154 FROM pa_implementations_all imp
3155 WHERE NVL(imp.org_id,-99) = NVL(p_org_id, -99);
3156
3157 RETURN l_set_of_books_id;
3158 EXCEPTION
3159 WHEN OTHERS THEN
3160 RETURN NULL;
3161 END get_set_of_books_id;
3162
3163 ---------------------------------------------------------------
3164 /*
3165 * This function returns the end_date of the PA period in which the input date falls.
3166 */
3167 FUNCTION get_pa_period_end_date_OU ( p_date IN pa_periods_all.end_date%TYPE)
3168 RETURN pa_periods_all.end_date%TYPE
3169 IS
3170 l_end_date pa_periods_all.end_date%TYPE ;
3171 BEGIN
3172
3173 SELECT pap.end_date
3174 INTO l_end_date
3175 FROM pa_periods pap
3176 WHERE trunc(p_date) between pap.start_date AND pap.end_date ;
3177
3178 RETURN l_end_date ;
3179 EXCEPTION
3180 WHEN OTHERS THEN
3181 RAISE;
3182
3183 END get_pa_period_end_date_OU;
3184 ---------------------------------------------------------------
3185 ----------------------------------------------------------------------------------------------------------------
3186 -- API : get_accrual_pa_dt_period
3187 -- Description : This procedure returns the pa_date and period_name based on the reversing EI gl period Name
3188 -- and EI Date for Reversing and original transactions respectively.
3189 -- Parameters :
3190 -- IN :p_gl_period - GL period of the reversing EI.
3191 -- p_ei_date - EI Date of the Trx.(Used for Org EI PA Date Derivation).
3192 -- p_org_id - Organization Id.
3193 -- p_prvdr_recvr_flg - Provider/Receiver Flag.
3194 -- p_epp_flag - EPP Enabled Flag.
3195 -- p_org_rev_fl - Orginal/Reversing Transaction Flag.
3196 -- OUT :x_pa_date - PA Date of the Trx.
3197 -- x_pa_period_name - PA Period Name for above date.
3198 -- x_return_status - Return status.
3199 -- x_error_code - Return Error Code.
3200 ----------------------------------------------------------------------------------------------------------------
3201 PROCEDURE get_accrual_pa_dt_period( p_gl_period IN VARCHAR2
3202 ,p_ei_date IN DATE
3203 ,p_org_id IN pa_expenditure_items_all.org_id%TYPE
3204 ,p_prvdr_recvr_flg IN VARCHAR2
3205 ,p_epp_flag IN VARCHAR2
3206 ,p_org_rev_flg IN VARCHAR2
3207 ,x_pa_date OUT NOCOPY DATE
3208 ,x_pa_period_name OUT NOCOPY VARCHAR2
3209 ,x_return_status OUT NOCOPY VARCHAR2
3210 ,x_error_code OUT NOCOPY VARCHAR2
3211 )
3212 IS
3213 l_org_pa_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
3214 l_org_pa_start_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
3215 l_org_pa_end_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
3216 l_org_pa_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE;
3217 l_rev_pa_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
3218 l_rev_pa_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE;
3219 l_org_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE;
3220 l_pa_overlaps_gl VARCHAR2(1) := 'N';
3221 l_debug_mode VARCHAR2(1);
3222 BEGIN
3223
3224 ---Initialize the out var.
3225
3226 x_pa_date := NULL;
3227 x_pa_period_name := NULL;
3228 x_return_status := FND_API.G_RET_STS_SUCCESS;
3229 x_error_code := NULL;
3230
3231 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3232 l_debug_mode := NVL(l_debug_mode, 'N');
3233
3234 IF ( l_debug_mode = 'Y' ) THEN
3235 pa_debug.g_err_stage := 'get_accrual_pa_dt_per dt - '||to_char(p_ei_date);
3236 pa_debug.write_file(pa_debug.g_err_stage);
3237 pa_debug.g_err_stage := 'get_accrual_pa_dt_per org - '||p_org_id;
3238 pa_debug.write_file(pa_debug.g_err_stage);
3239 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Org_Rev_Flg - '||p_org_rev_flg;
3240 pa_debug.write_file(pa_debug.g_err_stage);
3241 pa_debug.g_err_stage := 'get_accrual_pa_dt_per prvdr_recvr_flg - '||p_prvdr_recvr_flg;
3242 pa_debug.write_file(pa_debug.g_err_stage);
3243 END IF;
3244
3245 IF p_org_rev_flg = 'O' THEN -- This is for Original Transaction-----------------------------{
3246
3247 IF p_prvdr_recvr_flg = 'P' THEN ------------------------{
3248 IF ((trunc(p_ei_date) BETWEEN g_prv_accr_prvdr_pa_start_date AND g_prv_accr_prvdr_pa_end_date)
3249 AND g_prv_accr_prvdr_pa_start_date IS NOT NULL
3250 AND g_prv_accr_prvdr_gl_period=p_gl_period) THEN
3251
3252 IF p_epp_flag = 'Y' THEN
3253 l_org_pa_date := p_ei_date;
3254 ELSE
3255 l_org_pa_date := g_prv_accr_prvdr_pa_end_date;
3256 END IF;
3257 IF ( l_debug_mode = 'Y' ) THEN
3258 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Org P Cache PA dt- '||to_char(l_org_pa_date);
3259 pa_debug.write_file(pa_debug.g_err_stage);
3260 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Org P Cache PA Per-'||g_prv_accr_prvdr_pa_period;
3261 pa_debug.write_file(pa_debug.g_err_stage);
3262 END IF;
3263
3264 x_pa_date := l_org_pa_date;
3265 x_pa_period_name := g_prv_accr_prvdr_pa_period;
3266 return;
3267 END IF;
3268 ELSE ---- p_prvdr_recvr_flg = 'R'
3269 IF ((trunc(p_ei_date) BETWEEN g_prv_accr_recvr_pa_start_date AND g_prv_accr_recvr_pa_end_date)
3270 AND g_prv_accr_recvr_pa_start_date IS NOT NULL
3271 AND g_prv_accr_recvr_gl_period=p_gl_period) THEN
3272
3273 IF p_epp_flag = 'Y' THEN
3274 l_org_pa_date := p_ei_date;
3275 ELSE
3276 l_org_pa_date := g_prv_accr_recvr_pa_end_date;
3277 END IF;
3278 IF ( l_debug_mode = 'Y' ) THEN
3279 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Org R Cache PA dt- '||to_char(l_org_pa_date);
3280 pa_debug.write_file(pa_debug.g_err_stage);
3281 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Org R Cache PA Per-'||g_prv_accr_recvr_pa_period;
3282 pa_debug.write_file(pa_debug.g_err_stage);
3283 END IF;
3284
3285 x_pa_date := l_org_pa_date;
3286 x_pa_period_name := g_prv_accr_recvr_pa_period;
3287 return;
3288 END IF;
3289 END IF; -------p_prvdr_recvr_flg = P-----------}
3290
3291 -- Either the Cache is empty / date is not in the range
3292 BEGIN
3293 SELECT papl.end_date,papl.start_date,papl.period_name,papl.gl_period_name
3294 INTO l_org_pa_end_date,l_org_pa_start_date,l_org_pa_period_name,l_org_gl_period_name
3295 FROM pa_periods_all papl
3296 WHERE nvl(papl.org_id, -99 ) = nvl( p_org_id, -99 )
3297 AND trunc(p_ei_date) between papl.start_date and papl.end_date;
3298
3299 IF ( l_org_gl_period_name <> p_gl_period )
3300 THEN
3301 /* Bug#2476554
3302 * If PA Period derived above overlaps GL periods, PA date will be the end_date
3303 * of the last period in the earlier GL period. (In this case pa_date will
3304 * will usually end-up lesser than ei_date. This is OK.) The end_date will be
3305 * used for both EPP and non-EPP.
3306 */
3307 IF ( l_debug_mode = 'Y' ) THEN
3308 pa_debug.g_err_stage := 'DEBUG: gl periods are different ' || l_org_gl_period_name || p_gl_period;
3309 pa_debug.write_file(pa_debug.g_err_stage);
3310 END IF;
3311
3312 l_pa_overlaps_gl := 'Y';
3313 SELECT papl.end_date,papl.start_date,papl.period_name
3314 INTO l_org_pa_end_date,l_org_pa_start_date,l_org_pa_period_name
3315 FROM pa_periods_all papl
3316 WHERE nvl(papl.org_id, -99 ) = nvl( p_org_id, -99 )
3317 AND papl.gl_period_name=p_gl_period
3318 AND papl.start_date= ( SELECT MAX(papl1.start_date)
3319 FROM pa_periods_all papl1
3320 WHERE nvl(papl1.org_id, -99 ) = nvl( p_org_id, -99 )
3321 AND papl1.gl_period_name=p_gl_period
3322 );
3323 END IF;
3324 EXCEPTION
3325 WHEN NO_DATA_FOUND THEN
3326 IF ( l_debug_mode = 'Y' ) THEN
3327 pa_debug.g_err_stage := 'NDF - get_accrual_pa_dt_per Org PA dt ';
3328 pa_debug.write_file(pa_debug.g_err_stage);
3329 END IF;
3330 x_return_status := FND_API.G_RET_STS_ERROR ;
3331 IF p_prvdr_recvr_flg = 'P' THEN
3332 x_error_code := 'NO_PA_DATE';
3333 ELSE
3334 x_error_code := 'NO_RECVR_PA_DATE';
3335 END IF;
3336 return;
3337 WHEN OTHERS THEN
3338 IF ( l_debug_mode = 'Y' ) THEN
3339 pa_debug.g_err_stage := 'WO Excep - get_accrual_pa_dt_per Org PA Date';
3340 pa_debug.write_file(pa_debug.g_err_stage);
3341 END IF;
3342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3343 raise;
3344 END;
3345 --- Assign the global variables
3346 IF p_prvdr_recvr_flg = 'P' THEN
3347 g_prv_accr_prvdr_pa_start_date := l_org_pa_start_date;
3348 g_prv_accr_prvdr_pa_end_date := l_org_pa_end_date;
3349 g_prv_accr_prvdr_pa_period := l_org_pa_period_name;
3350 g_prv_accr_prvdr_gl_period := l_org_gl_period_name;
3351 ELSE
3352 g_prv_accr_recvr_pa_start_date := l_org_pa_start_date;
3353 g_prv_accr_recvr_pa_end_date := l_org_pa_end_date;
3354 g_prv_accr_recvr_pa_period := l_org_pa_period_name;
3355 g_prv_accr_recvr_gl_period := l_org_gl_period_name;
3356 END IF;
3357
3358 IF ( p_epp_flag = 'N' or l_pa_overlaps_gl = 'Y' )
3359 THEN
3360 l_org_pa_date := l_org_pa_end_date;
3361 ELSE
3362 l_org_pa_date := p_ei_date;
3363 END IF;
3364 IF ( l_debug_mode = 'Y' ) THEN
3365 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Org SEL PA dt- '||to_char(l_org_pa_date);
3366 pa_debug.write_file(pa_debug.g_err_stage);
3367 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Org SEL PA Per-'||l_org_pa_period_name;
3368 pa_debug.write_file(pa_debug.g_err_stage);
3369 END IF;
3370
3371 x_pa_date := l_org_pa_date;
3372 x_pa_period_name := l_org_pa_period_name;
3373 return;
3374
3375 ELSE ---- REV Transaction---p_org_rev_flg = 'R'--------------------------
3376
3377 IF p_prvdr_recvr_flg = 'P' THEN
3378
3379 IF p_gl_period = g_p_gl_period THEN
3380
3381 l_rev_pa_date := g_p_accr_rev_pa_date;
3382 IF ( l_debug_mode = 'Y' ) THEN
3383 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Rev P Cache PA dt- '||to_char(l_rev_pa_date);
3384 pa_debug.write_file(pa_debug.g_err_stage);
3385 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Rev P Cache PA Per-'||g_p_accr_rev_pa_period;
3386 pa_debug.write_file(pa_debug.g_err_stage);
3387 END IF;
3388
3389 x_pa_date := l_rev_pa_date;
3390 /*2476554*/
3391 if (p_epp_flag = 'Y' and p_ei_date >= l_rev_pa_date) then
3392 x_pa_date := p_ei_date;
3393 end if;
3394 x_pa_period_name := g_p_accr_rev_pa_period;
3395 return;
3396
3397 END IF;
3398 ELSE --- Recvr
3399
3400 IF p_gl_period = g_r_gl_period THEN
3401 l_rev_pa_date := g_r_accr_rev_pa_date;
3402 IF ( l_debug_mode = 'Y' ) THEN
3403 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Rev R Cache PA dt- '||to_char(l_rev_pa_date);
3404 pa_debug.write_file(pa_debug.g_err_stage);
3405 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Rev R Cache PA Per-'||g_r_accr_rev_pa_period;
3406 pa_debug.write_file(pa_debug.g_err_stage);
3407 END IF;
3408
3409 x_pa_date := l_rev_pa_date;
3410 /*2476554*/
3411 if (p_epp_flag = 'Y' and p_ei_date >= l_rev_pa_date) then
3412 x_pa_date := p_ei_date;
3413 end if;
3414 x_pa_period_name := g_r_accr_rev_pa_period;
3415 return;
3416 END IF;
3417 END IF;
3418
3419
3420 ---- Either the Cache is empty/ Date is not in the range.
3421 ----Rev EI GL date is used to get the pa date. EPP flag is checked in the select itself.
3422 BEGIN
3423 SELECT min(decode(p_epp_flag,'Y',papl.start_date,papl.end_date))
3424 INTO l_rev_pa_date
3425 FROM pa_periods_all papl
3426 WHERE nvl(papl.org_id, -99 ) = nvl(p_org_id, -99 )
3427 AND papl.gl_period_name = p_gl_period ;
3428 END;
3429 IF ( l_debug_mode = 'Y' ) THEN
3430 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Rev SEL min(Date) For GL per '||to_char(l_rev_pa_date);
3431 pa_debug.write_file(pa_debug.g_err_stage);
3432 END IF;
3433
3434 IF l_rev_pa_date IS NOT NULL THEN
3435
3436 -- Get the Period Name
3437 BEGIN
3438 SELECT period_name
3439 INTO l_rev_pa_period_name
3440 FROM pa_periods_all papl
3441 WHERE nvl(papl.org_id, -99 ) = nvl(p_org_id, -99 )
3442 AND trunc(l_rev_pa_date) between papl.start_date and papl.end_date;
3443 EXCEPTION
3444 WHEN NO_DATA_FOUND THEN
3445 IF ( l_debug_mode = 'Y' ) THEN
3446 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Rev NDF';
3447 pa_debug.write_file(pa_debug.g_err_stage);
3448 END IF;
3449 x_return_status := FND_API.G_RET_STS_ERROR ;
3450 IF p_prvdr_recvr_flg = 'P' THEN
3451 x_error_code := 'PA_NO_REV_PRVDR_ACCR_PA_DATE';
3452 ELSE
3453 x_error_code := 'PA_NO_REV_RECVR_ACCR_PA_DATE';
3454 END IF;
3455 return;
3456 WHEN OTHERS THEN
3457 IF ( l_debug_mode = 'Y' ) THEN
3458 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Rev WO';
3459 pa_debug.write_file(pa_debug.g_err_stage);
3460 END IF;
3461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3462 raise;
3463 END;
3464
3465 IF p_prvdr_recvr_flg = 'P' THEN
3466 g_p_gl_period := p_gl_period;
3467 g_p_accr_rev_pa_period := l_rev_pa_period_name;
3468 g_p_accr_rev_pa_date := l_rev_pa_date;
3469 ELSE
3470 g_r_gl_period := p_gl_period;
3471 g_r_accr_rev_pa_period := l_rev_pa_period_name;
3472 g_r_accr_rev_pa_date := l_rev_pa_date;
3473 END IF;
3474
3475 x_pa_date := l_rev_pa_date;
3476 /*2476554*/
3477 if (p_epp_flag = 'Y' and p_ei_date >= l_rev_pa_date) then
3478 x_pa_date := p_ei_date;
3479 end if;
3480 x_pa_period_name := l_rev_pa_period_name;
3481 ELSE
3482 ---- Handle the error. PA_DATE_PERIOD NOT DEFINED for Reverse trx.
3483 IF ( l_debug_mode = 'Y' ) THEN
3484 pa_debug.g_err_stage := 'get_accrual_pa_dt_per Rev PA Period not defined';
3485 pa_debug.write_file(pa_debug.g_err_stage);
3486 END IF;
3487
3488 x_return_status := FND_API.G_RET_STS_ERROR ;
3489
3490 IF p_prvdr_recvr_flg = 'P' THEN
3491 x_error_code := 'PA_NO_REV_PRVDR_ACCR_PA_DATE';
3492 ELSE
3493 x_error_code := 'PA_NO_REV_RECVR_ACCR_PA_DATE';
3494 END IF;
3495 return;
3496
3497 END IF;
3498 IF ( l_debug_mode = 'Y' ) THEN
3499 pa_debug.g_err_stage := 'Aft get_accrual_pa_dt_per sel pa dt - '||to_char(x_pa_date);
3500 pa_debug.write_file(pa_debug.g_err_stage);
3501 pa_debug.g_err_stage := 'Aft get_accrual_pa_dt_per sel pa period - '||x_pa_period_name;
3502 pa_debug.write_file(pa_debug.g_err_stage);
3503 END IF;
3504
3505 END IF; -------------------------------------------------------------------------------------------}
3506
3507 END;
3508 ----------------------------------------------------------------------------------------------------------------
3509 -- API : get_rev_accrual_date
3510 -- Description : This function returns the reversing accrual_dates.
3511 -- Parameters :
3512 -- IN :p_calling_module - Calling Module(CDL,PAXTREPE,TRXIMPORT)
3513 -- p_reference_date - Most of the time it is accrual Date of the Org Trx.
3514 -- p_application_id - Application Id (101).
3515 -- p_set_of_books_id - Set of Books for that Org.
3516 -- p_prvdr_recvr_flg - Provider/Receiver Flag.
3517 -- p_epp_flag - EPP Enabled Flag.
3518 -- OUT :x_gl_period_name - GL Period Name for that corresponding accr/gl date.
3519 -- x_return_status - Return status.
3520 -- x_error_code - Return Error Code.
3521 -- x_error_stage - Var to Capture the error messages.
3522 ----------------------------------------------------------------------------------------------------------------
3523 FUNCTION get_rev_accrual_date( p_calling_module IN VARCHAR2,
3524 p_reference_date IN DATE,
3525 p_application_id IN NUMBER ,
3526 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
3527 p_prvdr_recvr_flg IN VARCHAR2,
3528 p_epp_flag IN VARCHAR2,
3529 x_gl_period_name OUT NOCOPY VARCHAR2,
3530 x_return_status OUT NOCOPY VARCHAR2,
3531 x_error_code OUT NOCOPY VARCHAR2,
3532 x_error_stage OUT NOCOPY VARCHAR2
3533 )
3534 return date
3535 IS
3536 l_org_accr_start_date pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
3537 l_org_accr_end_date pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
3538 l_rev_accr_nxt_st_dt pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
3539 l_rev_accr_nxt_end_dt pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
3540
3541 l_rev_accr_dt pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
3542 l_period_status gl_period_statuses.closing_status%TYPE := NULL;
3543 l_period_name gl_period_statuses.period_name%TYPE := NULL;
3544 l_debug_mode VARCHAR2(1);
3545
3546 BEGIN
3547 ---Initialize the out variables.
3548 x_gl_period_name := NULL;
3549 x_return_status := FND_API.G_RET_STS_SUCCESS;
3550 x_error_code := NULL;
3551 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3552 l_debug_mode := NVL(l_debug_mode, 'N');
3553
3554
3555 pa_debug.g_err_stage := 'get_rev_accrual_date() for ref dt- ['||to_char(p_reference_date)||']';
3556 IF ( l_debug_mode = 'Y' ) THEN
3557 pa_debug.write_file(pa_debug.g_err_stage);
3558 END IF;
3559 pa_debug.g_err_stage := 'get_rev_accrual_date() sob - ['||p_set_of_books_id||']';
3560 IF ( l_debug_mode = 'Y' ) THEN
3561 pa_debug.write_file(pa_debug.g_err_stage);
3562 END IF;
3563 pa_debug.g_err_stage := 'get_rev_accrual_date() prvdr_recvr_flg - ['||p_prvdr_recvr_flg||']';
3564 IF ( l_debug_mode = 'Y' ) THEN
3565 pa_debug.write_file(pa_debug.g_err_stage);
3566 END IF;
3567
3568
3569 IF p_prvdr_recvr_flg = 'P' THEN ------------------------------------{
3570
3571 IF ((trunc(p_reference_date) BETWEEN g_p_org_accr_start_date AND g_p_org_accr_end_date )
3572 AND g_p_org_accr_end_date IS NOT NULL) THEN
3573 pa_debug.g_err_stage := 'Returning the accrual date from cache - get_rev_accrual_date()';
3574 IF ( l_debug_mode = 'Y' ) THEN
3575 pa_debug.write_file(pa_debug.g_err_stage);
3576 END IF;
3577
3578 IF p_epp_flag = 'Y' THEN
3579 l_rev_accr_dt := g_p_rev_accr_nxt_st_dt;
3580 ELSE
3581 l_rev_accr_dt := g_p_rev_accr_nxt_end_dt;
3582 END IF;
3583 x_gl_period_name := g_p_rev_gl_period_name;
3584 return(l_rev_accr_dt);
3585 END IF;
3586 ELSIF p_prvdr_recvr_flg = 'R' THEN
3587
3588 IF ((trunc(p_reference_date) BETWEEN g_r_org_accr_start_date AND g_r_org_accr_end_date )
3589 AND g_r_org_accr_end_date IS NOT NULL) THEN
3590
3591 pa_debug.g_err_stage := 'Returning the accrual date from cache - get_rev_accrual_date()';
3592 IF ( l_debug_mode = 'Y' ) THEN
3593 pa_debug.write_file(pa_debug.g_err_stage);
3594 END IF;
3595
3596 IF p_epp_flag = 'Y' THEN
3597 l_rev_accr_dt := g_r_rev_accr_nxt_st_dt;
3598 ELSE
3599 l_rev_accr_dt := g_r_rev_accr_nxt_end_dt;
3600 END IF;
3601 x_gl_period_name := g_r_rev_gl_period_name;
3602 return(l_rev_accr_dt);
3603
3604 END IF;
3605
3606 END IF; ------------------p_prvdr_recvr_flg = 'P'--------------------}
3607
3608 ---Either the Cache is empty or the date is not in the range.
3609
3610 pa_debug.g_err_stage := 'Before select get_rev_accrual_date() for ref dt-'||to_char(p_reference_date);
3611 IF ( l_debug_mode = 'Y' ) THEN
3612 pa_debug.write_file(pa_debug.g_err_stage);
3613 END IF;
3614
3615 BEGIN
3616 SELECT PERIOD.start_date,PERIOD.end_date
3617 INTO l_org_accr_start_date,l_org_accr_end_date
3618 FROM GL_PERIOD_STATUSES PERIOD
3619 WHERE PERIOD.application_id = p_application_id
3620 AND PERIOD.set_of_books_id = p_set_of_books_id
3621 AND PERIOD.adjustment_period_flag = 'N'
3622 AND trunc(p_reference_date) BETWEEN PERIOD.start_date and PERIOD.end_date;
3623 EXCEPTION
3624 WHEN NO_DATA_FOUND THEN
3625 x_return_status := FND_API.G_RET_STS_ERROR ;
3626 IF p_prvdr_recvr_flg = 'P' THEN
3627 x_error_code := 'PA_GL_REV_PRVDR_ACCR_NDEF';
3628 ELSE
3629 x_error_code := 'PA_GL_REV_RECVR_ACCR_NDEF';
3630 END IF;
3631 pa_debug.g_err_stage :='NDF - Prvdr GL Period SELECT';
3632 IF ( l_debug_mode = 'Y' ) THEN
3633 pa_debug.write_file(pa_debug.g_err_stage);
3634 END IF;
3635 return(NULL);
3636 WHEN OTHERS THEN
3637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3638 x_error_stage := 'Procedure: Pa_Utils2.Get_Accrual_Period_Information() ::: ' || pa_debug.g_err_stage ||':: '|| SQLERRM;
3639 raise;
3640 END;
3641
3642
3643 BEGIN
3644 SELECT PERIOD.start_date,PERIOD.end_date,PERIOD.closing_status,PERIOD.period_name
3645 INTO l_rev_accr_nxt_st_dt,l_rev_accr_nxt_end_dt,l_period_status,l_period_name
3646 FROM GL_PERIOD_STATUSES PERIOD
3647 WHERE PERIOD.application_id = p_application_id
3648 AND PERIOD.set_of_books_id = p_set_of_books_id
3649 AND PERIOD.adjustment_period_flag = 'N'
3650 AND PERIOD.start_date = ( SELECT min(PERIOD.start_date)
3651 FROM GL_PERIOD_STATUSES PERIOD
3652 WHERE PERIOD.application_id = p_application_id
3653 AND PERIOD.set_of_books_id = p_set_of_books_id
3654 AND PERIOD.adjustment_period_flag = 'N'
3655 AND PERIOD.start_date > l_org_accr_end_date);
3656 END;
3657
3658 --Check the status here.
3659 IF l_period_status IN ('O','F') THEN -------------------------{
3660 IF p_prvdr_recvr_flg = 'P' THEN
3661 g_p_rev_accr_nxt_st_dt := l_rev_accr_nxt_st_dt;
3662 g_p_rev_accr_nxt_end_dt := l_rev_accr_nxt_end_dt;
3663 g_p_org_accr_start_date := l_org_accr_start_date ;
3664 g_p_org_accr_end_date := l_org_accr_end_date ;
3665 g_p_rev_gl_period_name := l_period_name ;
3666 ELSIF p_prvdr_recvr_flg = 'R' THEN
3667 g_r_rev_accr_nxt_st_dt := l_rev_accr_nxt_st_dt;
3668 g_r_rev_accr_nxt_end_dt := l_rev_accr_nxt_end_dt;
3669 g_r_org_accr_start_date := l_org_accr_start_date ;
3670 g_r_org_accr_end_date := l_org_accr_end_date ;
3671 g_r_rev_gl_period_name := l_period_name;
3672 END IF;
3673 ELSE -- Period is closed.
3674 x_return_status := FND_API.G_RET_STS_ERROR;
3675 IF p_prvdr_recvr_flg = 'P' THEN
3676 x_error_code := 'PA_GL_REV_PRVDR_ACCR_CLOSED';
3677 ELSE
3678 x_error_code := 'PA_GL_REV_RECVR_ACCR_CLOSED';
3679 END IF;
3680 return(NULL);
3681 END IF; -----------------l_period_status IN ('O','F')----------}
3682 pa_debug.g_err_stage := 'get_rev_accrual_date st date is '||to_char(l_rev_accr_nxt_st_dt);
3683 IF ( l_debug_mode = 'Y' ) THEN
3684 pa_debug.write_file(pa_debug.g_err_stage);
3685 END IF;
3686 pa_debug.g_err_stage := 'get_rev_accrual_date end date is '||to_char(l_rev_accr_nxt_end_dt);
3687 IF ( l_debug_mode = 'Y' ) THEN
3688 pa_debug.write_file(pa_debug.g_err_stage);
3689 END IF;
3690
3691 IF p_epp_flag = 'Y' THEN
3692 l_rev_accr_dt := l_rev_accr_nxt_st_dt;
3693 ELSE
3694 l_rev_accr_dt := l_rev_accr_nxt_end_dt;
3695 END IF;
3696 x_gl_period_name := l_period_name;
3697 return(l_rev_accr_dt);
3698 EXCEPTION
3699 WHEN OTHERS THEN
3700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3701 x_error_stage := 'Procedure: Pa_Utils2.Get_Accrual_Period_Information() ::: ' || pa_debug.g_err_stage ||':: '|| SQLERRM;
3702 RAISE;
3703 END;
3704 ----------------------------------------------------------------------------------------------------------------
3705 -- API : get_accrual_gl_dt_period
3706 -- Description : This procedure is used to check if the gl/accrual date passed falls in a 'O'/'F' period.
3707 -- If Yes then return the period name. In case of accrual dates check though we don't need
3708 -- the period name, it is just used to check if the Dates are still in an O/F Period.
3709 -- Parameters :
3710 -- IN :p_calling_module - Calling Module(CDL,PAXTREPE,TRXIMPORT)
3711 -- p_reference_date - Accrual Date/GL Date of the Trx.
3712 -- p_application_id - Application Id (101).
3713 -- p_set_of_books_id - Set of Books for that Org.
3714 -- p_prvdr_recvr_flg - Provider/Receiver Flag.
3715 -- p_epp_flag - EPP Enabled Flag.
3716 -- OUT :x_gl_accr_period_name - GL Period Name for that corresponding accr/gl date.
3717 -- x_gl_accr_dt - GL Date.
3718 -- x_return_status - Return status.
3719 -- x_error_code - Return Error Code.
3720 -- x_error_stage - Var to Capture the error messages.
3721 ----------------------------------------------------------------------------------------------------------------
3722 PROCEDURE get_accrual_gl_dt_period(p_calling_module IN VARCHAR2,
3723 p_reference_date IN DATE,
3724 p_application_id IN NUMBER ,
3725 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
3726 p_prvdr_recvr_flg IN VARCHAR2,
3727 p_epp_flag IN VARCHAR2,
3728 x_gl_accr_period_name OUT NOCOPY VARCHAR2,
3729 x_gl_accr_dt OUT NOCOPY DATE,
3730 x_return_status OUT NOCOPY VARCHAR2,
3731 x_error_code OUT NOCOPY VARCHAR2,
3732 x_error_stage OUT NOCOPY VARCHAR2
3733 )
3734 IS
3735 l_accr_gl_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
3736 l_accr_gl_dt pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
3737 l_accr_gl_period_st_dt pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
3738 l_accr_gl_period_end_dt pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
3739 l_period_status gl_period_statuses.closing_status%TYPE :=NULL;
3740 l_debug_mode VARCHAR2(1);
3741 BEGIN
3742
3743 ---Initialize the out variables.
3744 x_gl_accr_period_name := NULL;
3745 x_gl_accr_dt := NULL;
3746 x_return_status := FND_API.G_RET_STS_SUCCESS;
3747 x_error_code := NULL;
3748 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3749 l_debug_mode := NVL(l_debug_mode, 'N');
3750
3751 pa_debug.g_err_stage := 'get_accrual_gl_dt_period() for ref dt- ['||to_char(p_reference_date)||']';
3752 IF ( l_debug_mode = 'Y' ) THEN
3753 pa_debug.write_file(pa_debug.g_err_stage);
3754 END IF;
3755 pa_debug.g_err_stage := 'get_accrual_gl_dt_period() sob - ['||p_set_of_books_id||']';
3756 IF ( l_debug_mode = 'Y' ) THEN
3757 pa_debug.write_file(pa_debug.g_err_stage);
3758 END IF;
3759 pa_debug.g_err_stage := 'get_accrual_gl_dt_period() prvdr_recvr_flg - ['||p_prvdr_recvr_flg||']';
3760 IF ( l_debug_mode = 'Y' ) THEN
3761 pa_debug.write_file(pa_debug.g_err_stage);
3762 END IF;
3763
3764 IF p_prvdr_recvr_flg = 'P' THEN -------------------------------------------------{
3765
3766 IF ((trunc(p_reference_date) BETWEEN g_p_accr_gl_per_st_dt AND g_p_accr_gl_per_end_dt )
3767 AND g_p_accr_gl_per_st_dt IS NOT NULL) THEN ----------------------------P Cache------{
3768
3769 ---From Cache
3770 IF p_epp_flag = 'Y' THEN
3771 l_accr_gl_dt := p_reference_date;
3772 ELSE
3773 l_accr_gl_dt := g_p_accr_gl_per_end_dt;
3774 END IF;
3775
3776 pa_debug.g_err_stage := 'get_accrual_gl_dt_period() P Cache dt- ['||to_char(l_accr_gl_dt)||']';
3777 IF ( l_debug_mode = 'Y' ) THEN
3778 pa_debug.write_file(pa_debug.g_err_stage);
3779 END IF;
3780 pa_debug.g_err_stage := 'get_accrual_gl_dt_period() P Cache Per- ['||g_p_accr_gl_per_name||']';
3781 IF ( l_debug_mode = 'Y' ) THEN
3782 pa_debug.write_file(pa_debug.g_err_stage);
3783 END IF;
3784 --Assign the out variables.
3785 x_gl_accr_dt := l_accr_gl_dt ;
3786 x_gl_accr_period_name := g_p_accr_gl_per_name;
3787 return;
3788 END IF; ------------------------------------------------------------------------P Cache------}
3789
3790 ELSIF p_prvdr_recvr_flg = 'R' THEN
3791
3792 IF ((trunc(p_reference_date) BETWEEN g_r_accr_gl_per_st_dt AND g_r_accr_gl_per_end_dt )
3793 AND g_r_accr_gl_per_st_dt IS NOT NULL) THEN ----------------------------R Cache------{
3794
3795 ---From Cache
3796 IF p_epp_flag = 'Y' THEN
3797 l_accr_gl_dt := p_reference_date;
3798 ELSE
3799 l_accr_gl_dt := g_r_accr_gl_per_end_dt;
3800 END IF;
3801 pa_debug.g_err_stage := 'get_accrual_gl_dt_period() R Cache dt- ['||to_char(l_accr_gl_dt)||']';
3802 IF ( l_debug_mode = 'Y' ) THEN
3803 pa_debug.write_file(pa_debug.g_err_stage);
3804 END IF;
3805 pa_debug.g_err_stage := 'get_accrual_gl_dt_period() R Cache Per- ['||g_r_accr_gl_per_name||']';
3806 IF ( l_debug_mode = 'Y' ) THEN
3807 pa_debug.write_file(pa_debug.g_err_stage);
3808 END IF;
3809 --Assign the out variables.
3810 x_gl_accr_dt := l_accr_gl_dt ;
3811 x_gl_accr_period_name := g_r_accr_gl_per_name;
3812 return;
3813 END IF;
3814
3815 END IF;---------------p_prvdr_recvr_flg = 'P'-----------------------------}
3816 --- Either the cache is empty or the reference date is not in the range.
3817 BEGIN
3818 SELECT PERIOD.period_name,PERIOD.start_date,PERIOD.end_date,PERIOD.closing_status
3819 INTO l_accr_gl_period_name, l_accr_gl_period_st_dt,l_accr_gl_period_end_dt,l_period_status
3820 FROM GL_PERIOD_STATUSES PERIOD
3821 WHERE PERIOD.application_id = p_application_id
3822 AND PERIOD.set_of_books_id = p_set_of_books_id
3823 AND PERIOD.adjustment_period_flag = 'N'
3824 AND trunc(p_reference_date) BETWEEN PERIOD.start_date and PERIOD.end_date;
3825 EXCEPTION
3826 WHEN NO_DATA_FOUND THEN
3827 x_return_status := FND_API.G_RET_STS_ERROR ;
3828 IF p_prvdr_recvr_flg = 'P' THEN
3829 x_error_code := 'PA_GL_PER_PRVDR_ACCR_NOT_DEF';
3830 ELSE
3831 x_error_code := 'PA_GL_PER_RECVR_ACCR_NOT_DEF';
3832 END IF;
3833 pa_debug.g_err_stage := 'get_accrual_gl_dt_period()-NDF for ref dt- ['||to_char(p_reference_date)||']';
3834 IF ( l_debug_mode = 'Y' ) THEN
3835 pa_debug.write_file(pa_debug.g_err_stage);
3836 END IF;
3837 pa_debug.g_err_stage := 'get_accrual_gl_dt_period()-NDF sob - ['||p_set_of_books_id||']';
3838 IF ( l_debug_mode = 'Y' ) THEN
3839 pa_debug.write_file(pa_debug.g_err_stage);
3840 END IF;
3841 pa_debug.g_err_stage := 'get_accrual_gl_dt_period()-NDF prvdr_recvr_flg - ['||p_prvdr_recvr_flg||']';
3842 IF ( l_debug_mode = 'Y' ) THEN
3843 pa_debug.write_file(pa_debug.g_err_stage);
3844 END IF;
3845 return;
3846 WHEN OTHERS THEN
3847 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3848
3849 pa_debug.g_err_stage := 'get_accrual_gl_dt_period()-WO sob - ['||p_set_of_books_id||']';
3850 IF ( l_debug_mode = 'Y' ) THEN
3851 pa_debug.write_file(pa_debug.g_err_stage);
3852 END IF;
3853 pa_debug.g_err_stage := 'get_accrual_gl_dt_period()-WO prvdr_recvr_flg - ['||p_prvdr_recvr_flg||']';
3854 IF ( l_debug_mode = 'Y' ) THEN
3855 pa_debug.write_file(pa_debug.g_err_stage);
3856 END IF;
3857 pa_debug.g_err_stage := 'get_accrual_gl_dt_period()-WO for ref dt- ['||to_char(p_reference_date)||']';
3858 IF ( l_debug_mode = 'Y' ) THEN
3859 pa_debug.write_file(pa_debug.g_err_stage);
3860 END IF;
3861 x_error_stage := 'Procedure: Pa_Utils2.Get_Accrual_Period_Information() ::: ' || pa_debug.g_err_stage ||':: '|| SQLERRM;
3862 raise;
3863 END;
3864 -- EPP Derivation.
3865 IF p_epp_flag = 'Y' THEN
3866 l_accr_gl_dt := p_reference_date;
3867 ELSE
3868 l_accr_gl_dt := l_accr_gl_period_end_dt;
3869 END IF;
3870
3871 --Check the status here.
3872 IF l_period_status in ('O','F') THEN --------------------------{
3873 IF p_prvdr_recvr_flg = 'P' THEN
3874 g_p_accr_gl_per_name := l_accr_gl_period_name;
3875 g_p_accr_gl_per_st_dt := l_accr_gl_period_st_dt ;
3876 g_p_accr_gl_per_end_dt := l_accr_gl_period_end_dt;
3877 ELSIF p_prvdr_recvr_flg = 'R' THEN
3878 g_r_accr_gl_per_name := l_accr_gl_period_name;
3879 g_r_accr_gl_per_st_dt := l_accr_gl_period_st_dt ;
3880 g_r_accr_gl_per_end_dt := l_accr_gl_period_end_dt;
3881 END IF;
3882
3883 pa_debug.g_err_stage := 'get_accrual_gl_dt_period()-SEL for accr/gl dt- ['||to_char(l_accr_gl_dt)||']';
3884 IF ( l_debug_mode = 'Y' ) THEN
3885 pa_debug.write_file(pa_debug.g_err_stage);
3886 END IF;
3887 pa_debug.g_err_stage := 'get_accrual_gl_dt_period()-SEL sob - ['||p_set_of_books_id||']';
3888 IF ( l_debug_mode = 'Y' ) THEN
3889 pa_debug.write_file(pa_debug.g_err_stage);
3890 END IF;
3891 pa_debug.g_err_stage := 'get_accrual_gl_dt_period()-SEL prvdr_recvr_flg - ['||p_prvdr_recvr_flg||']';
3892 IF ( l_debug_mode = 'Y' ) THEN
3893 pa_debug.write_file(pa_debug.g_err_stage);
3894 END IF;
3895 pa_debug.g_err_stage := 'get_accrual_gl_dt_period()-SEL gl period name - ['||l_accr_gl_period_name||']';
3896 IF ( l_debug_mode = 'Y' ) THEN
3897 pa_debug.write_file(pa_debug.g_err_stage);
3898 END IF;
3899 x_gl_accr_dt := l_accr_gl_dt ;
3900 x_gl_accr_period_name := l_accr_gl_period_name ;
3901
3902 ELSE -- Period is closed.
3903 x_return_status := FND_API.G_RET_STS_ERROR;
3904 x_gl_accr_period_name := l_accr_gl_period_name ; -- We need this for accounted TRX.
3905 IF p_prvdr_recvr_flg = 'P' THEN
3906 x_error_code := 'PA_GL_PER_PRVDR_ACCR_CLOSED';
3907 ELSE
3908 x_error_code := 'PA_GL_PER_RECVR_ACCR_CLOSED';
3909 END IF;
3910
3911 pa_debug.g_err_stage := 'get_accrual_gl_dt_period() Period Clsd ref dt- ['||to_char(p_reference_date)||']';
3912 IF ( l_debug_mode = 'Y' ) THEN
3913 pa_debug.write_file(pa_debug.g_err_stage);
3914 END IF;
3915 pa_debug.g_err_stage := ' get_accrual_gl_dt_period()Period Clsd sob - ['||p_set_of_books_id||']';
3916 IF ( l_debug_mode = 'Y' ) THEN
3917 pa_debug.write_file(pa_debug.g_err_stage);
3918 END IF;
3919 pa_debug.g_err_stage := 'get_accrual_gl_dt_period()Period Clsd prvdr_recvr_flg - ['||p_prvdr_recvr_flg||']';
3920 IF ( l_debug_mode = 'Y' ) THEN
3921 pa_debug.write_file(pa_debug.g_err_stage);
3922 END IF;
3923 END IF;---------------------l_period_status in ('O','F')------------}
3924
3925 EXCEPTION
3926 WHEN OTHERS THEN
3927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3928 x_error_stage := 'Procedure: Pa_Utils2.Get_Accrual_Period_Information() ::: ' || pa_debug.g_err_stage ||':: '|| SQLERRM;
3929 raise;
3930 END;
3931 ----------------------------------------------------------------------------------------------------------------
3932 -- API : get_accrual_period_information
3933 -- Description : This is main procedure to derive the accrual dates, gl dates, GL Periods, PA Dates
3934 -- and the Corresponding PA Periods.If any of them are null/not in a O/F period, then
3935 -- an appropriate error code is returned to the calling program.
3936 -- Parameters :
3937 -- IN :p_expenditure_item_date - EI Date of the Trx.
3938 -- p_reference_date - Accrual Date/GL Date of the Trx.
3939 -- p_application_id - Application Id (101).
3940 -- p_prvdr_org_id - Provider Organization Id.
3941 -- p_recvr_org_id - Receiver Organization Id.
3942 -- p_prvdr_sob_id - Provider Set of Books for that Org.
3943 -- p_recvr_sob_id - Receiver Set of Books for that Org.
3944 -- p_calling_module - Calling Module(CDL,PAXTREPE,TRXIMPORT)
3945 -- p_adj_ei_id - Adjusted item id. We check if it is not null or not.
3946 -- p_acct_flag - Flag to indicate an accounted Transaction.
3947 -- OUT :x_prvdr_pa_date - Provider PA Date.
3948 -- x_prvdr_gl_period_name- Provider GL Period Name for that corresponding gl date.
3949 -- x_prvdr_pa_period_name- Provider PA Period Name for that corresponding ei date.
3950 -- x_recvr_pa_date - Receiver PA Date.
3951 -- x_recvr_gl_period_name- Receiver GL Period Name for that corresponding gl date.
3952 -- x_recvr_pa_period_name- Receiver PA Period Name for that corresponding ei date.
3953 -- x_return_status - Return status.
3954 -- x_error_code - Return Error Code.
3955 -- x_error_stage - Var to Capture the error messages.
3956 -- IN OUT :x_prvdr_accrual_date - Provider Accrual Date. Values is passed in the same var for Rev Trx.
3957 -- x_recvr_accrual_date - Receiver Accrual Date. Values is passed in the same var for Rev Trx.
3958 -- x_prvdr_gl_date - Provider GL Date.IN (TRXIMPORT) OUT(CDL)
3959 /*=====================================================================================================*
3960 * This procedure is called from Costing prog/Trx import/PAXTREPE (Pre-Approved Batches) *
3961 * Before each call, the accrual data/accrual flag,accounted_flag, and *
3962 * system_linkage_function = 'PJ'(misc) is checked. *
3963 * Process Logic : *
3964 * 1.Accrual Dates - *
3965 * If the Call is from the Costing program then the accrual dates will be not null, where as *
3966 * for Transaction import, and the exp entry form, we need to derive the accrual dates. *
3967 _____________________________________________________________________________________________________
3968 |Description : Derive Accrual Dates |
3969 |___________________________________________________________________________________________________ |
3970 | EI Date |REV EI | EPP | Accrual Date | GL Date | PA Date | Logic |
3971 |__________|_______|______|______________|___________|__________|____________________________________|
3972 |27-JAN-02 | NO | YES | 27-JAN-02 | - | - | Accrual date is same as EI Date |
3973 |__________|_______|______|______________|___________|__________|____________________________________|
3974 |27-JAN-02 | NO | NO | 31-JAN-02 | - | - | Last day of the current GL Period |
3975 | | | | | | | GL Period 01-JAN-02 to 31-JAN-02 |
3976 |----------|-----------------------------------------------------------------------------------------|
3977 |27-JAN-02 | YES | YES | 01-FEB-02 | - | - | First Day of the Next O,F GL Period|
3978 |__________|_______|______|______________|___________|__________|____________________________________|
3979 |27-JAN-02 | YES | NO | 28-FEB-02 | - | - | Last Day of the Next O,F GL Period |
3980 |__________|_______|______|______________|___________|__________|____________________________________|
3981 | |
3982 |Description : Derive GL Dates. GL Date is same as accrual Date, but it has to be in an open period. |
3983 |___________________________________________________________________________________________________ |
3984 |27-JAN-02 | NO | YES | 27-JAN-02 |27-JAN-02 | - | GL Date is same as the Accrual Dt. |
3985 |__________|_______|______|______________|___________|__________|____________________________________|
3986 |27-JAN-02 | NO | NO | 31-JAN-02 |31-JAN-02 | - | GL Date is same as the Accrual Dt. |
3987 | | | | | | | |
3988 |----------|-----------------------------------------------------------------------------------------|
3989 |27-JAN-02 | YES | YES | 01-FEB-02 | 01-FEB-02 | - | GL Date is same as the Accrual Dt. |
3990 |__________|_______|______|______________|___________|__________|____________________________________|
3991 |27-JAN-02 | YES | NO | 28-FEB-02 | 28-FEB-02 | - | GL Date is same as the Accrual Dt. |
3992 |__________|_______|______|______________|___________|__________|____________________________________|
3993 | |
3994 |Description : Derive PA Dates. |
3995 |___________________________________________________________________________________________________ |
3996 |27-JAN-02 | NO | YES | 27-JAN-02 |27-JAN-02 |27-JAN-02 | Same as the EI Date. |
3997 |__________|_______|______|______________|___________|__________|____________________________________|
3998 |27-JAN-02 | NO | NO | 31-JAN-02 |31-JAN-02 |31-JAN-02 | End date of the PA Period where |
3999 | | | | | | | the EI falls bt. start and End Dt. |
4000 |----------|-----------------------------------------------------------------------------------------|
4001 |27-JAN-02 | YES | YES | 01-FEB-02 | 01-FEB-02 |01-FEB-02 | First PA Period Date where the Rev |
4002 | | | | | | | GL Per.is bt.the start and end dt. |
4003 | | | | | | | Its the min(start_date). |
4004 |----------|-----------------------------------------------------------------------------------------|
4005 |27-JAN-02 | YES | NO | 28-FEB-02 | 28-FEB-02 |28-FEB-02 | Last PA Period Date where the Rev |
4006 | | | | | | | GL Per. is bt.the start and end dt.|
4007 | | | | | | | Its the min(end_date). |
4008 ------------------------------------------------------------------------------------------------------
4009 *======================================================================================================*/
4010 ----------------------------------------------------------------------------------------------------------------
4011 PROCEDURE get_accrual_period_information(p_expenditure_item_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
4012 ,x_prvdr_accrual_date IN OUT NOCOPY pa_cost_distribution_lines_all.gl_date%TYPE
4013 ,x_recvr_accrual_date IN OUT NOCOPY pa_cost_distribution_lines_all.gl_date%TYPE
4014 ,p_prvdr_org_id IN pa_expenditure_items_all.org_id%TYPE
4015 ,p_recvr_org_id IN pa_expenditure_items_all.org_id%TYPE
4016 ,p_prvdr_sob_id IN pa_implementations_all.set_of_books_id%TYPE
4017 ,p_recvr_sob_id IN pa_implementations_all.set_of_books_id%TYPE
4018 ,p_calling_module IN VARCHAR2
4019 ,x_prvdr_pa_date OUT NOCOPY pa_cost_distribution_lines_all.pa_date%TYPE
4020 ,x_prvdr_pa_period_name OUT NOCOPY pa_cost_distribution_lines_all.pa_period_name%TYPE
4021 ,x_prvdr_gl_date IN OUT NOCOPY pa_cost_distribution_lines_all.gl_date%TYPE
4022 ,x_prvdr_gl_period_name OUT NOCOPY pa_cost_distribution_lines_all.gl_period_name%TYPE
4023 ,x_recvr_pa_date OUT NOCOPY pa_cost_distribution_lines_all.recvr_pa_date%TYPE
4024 ,x_recvr_pa_period_name OUT NOCOPY pa_cost_distribution_lines_all.recvr_pa_period_name%TYPE
4025 ,x_recvr_gl_date OUT NOCOPY pa_cost_distribution_lines_all.recvr_gl_date%TYPE
4026 ,x_recvr_gl_period_name OUT NOCOPY pa_cost_distribution_lines_all.recvr_gl_period_name%TYPE
4027 ,p_adj_ei_id IN pa_expenditure_items_all.expenditure_item_id%type
4028 ,p_acct_flag IN VARCHAR2
4029 ,x_return_status OUT NOCOPY VARCHAR2
4030 ,x_error_code OUT NOCOPY VARCHAR2
4031 ,x_error_stage OUT NOCOPY VARCHAR2
4032 )
4033 IS
4034 l_prvdr_pa_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
4035 l_prvdr_pa_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
4036 l_prvdr_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE := NULL;
4037 l_recvr_pa_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
4038 l_recvr_pa_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
4039 l_recvr_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE := NULL;
4040 l_prvdr_accrual_date pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
4041 l_prvdr_gl_date pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
4042 l_recvr_gl_date pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
4043 l_recvr_accrual_date pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
4044
4045 l_pa_gl_app_id NUMBER := 101; -- We always go against the GL Period.
4046 l_gl_app_id NUMBER := 101;
4047 l_app_id NUMBER := NULL ;
4048
4049 /*
4050 * Processing related variables.
4051 */
4052 l_error_stage VARCHAR2(2000);
4053 l_debug_mode VARCHAR2(1);
4054
4055 l_prof_new_gldate_derivation VARCHAR2(1) := 'N';
4056
4057 BEGIN
4058
4059
4060
4061 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4062 l_debug_mode := NVL(l_debug_mode, 'N');
4063 pa_debug.g_err_stage :='From get_accrual_period_information';
4064 IF(l_debug_mode = 'Y') THEN
4065 pa_debug.init_err_stack('pa_utils2.get_accrual_period_information');
4066 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
4067
4068 pa_debug.write_file(pa_debug.g_err_stage);
4069 END IF;
4070
4071 if g_prof_new_gldate_derivation IS NULL then
4072 l_prof_new_gldate_derivation := nvl(fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION'),'N') ; /*For Bug 5391468*/
4073 g_prof_new_gldate_derivation := l_prof_new_gldate_derivation;
4074 else
4075 l_prof_new_gldate_derivation := g_prof_new_gldate_derivation;
4076 end if;
4077 pa_debug.g_err_stage :='EPP Flag is :['||l_prof_new_gldate_derivation||']';
4078 IF(l_debug_mode = 'Y') THEN
4079 pa_debug.write_file(pa_debug.g_err_stage);
4080 END IF;
4081
4082 ---Initialize the error var.
4083 x_error_code := NULL;
4084 x_return_status := FND_API.G_RET_STS_SUCCESS;
4085
4086 pa_debug.g_err_stage :=' Profile option is [' || l_prof_new_gldate_derivation || ']';
4087 IF(l_debug_mode = 'Y') THEN
4088 pa_debug.write_file(pa_debug.g_err_stage);
4089 END IF;
4090 pa_debug.g_err_stage :='x_prvdr_accrual_date is [' || to_char(x_prvdr_accrual_date) || ']';
4091 IF(l_debug_mode = 'Y') THEN
4092 pa_debug.write_file(pa_debug.g_err_stage);
4093 END IF;
4094 pa_debug.g_err_stage :='x_recvr_accrual_date is [' || to_char(x_recvr_accrual_date) || ']';
4095 IF(l_debug_mode = 'Y') THEN
4096 pa_debug.write_file(pa_debug.g_err_stage);
4097 END IF;
4098 pa_debug.g_err_stage :='p_expenditure_item_date is [' || to_char(p_expenditure_item_date) || ']';
4099 IF(l_debug_mode = 'Y') THEN
4100 pa_debug.write_file(pa_debug.g_err_stage);
4101 END IF;
4102 pa_debug.g_err_stage :='x_prvdr_gl_date is [' || to_char(x_prvdr_gl_date) || ']';
4103 IF(l_debug_mode = 'Y') THEN
4104 pa_debug.write_file(pa_debug.g_err_stage);
4105 END IF;
4106 pa_debug.g_err_stage :='x_recvr_gl_date is [' || to_char(x_recvr_gl_date) || ']';
4107 IF(l_debug_mode = 'Y') THEN
4108 pa_debug.write_file(pa_debug.g_err_stage);
4109 END IF;
4110 pa_debug.g_err_stage :='p_prvdr_org_id is [' || to_char(p_prvdr_org_id) || ']';
4111 IF(l_debug_mode = 'Y') THEN
4112 pa_debug.write_file(pa_debug.g_err_stage);
4113 END IF;
4114 pa_debug.g_err_stage :='p_recvr_org_id is [' || to_char(p_recvr_org_id) || ']';
4115 IF(l_debug_mode = 'Y') THEN
4116 pa_debug.write_file(pa_debug.g_err_stage);
4117 END IF;
4118 pa_debug.g_err_stage :='p_prvdr_sob_id is [' || to_char(p_prvdr_sob_id) || ']';
4119 IF(l_debug_mode = 'Y') THEN
4120 pa_debug.write_file(pa_debug.g_err_stage);
4121 END IF;
4122 pa_debug.g_err_stage :='p_recvr_sob_id is [' || to_char(p_recvr_sob_id) || ']';
4123 IF(l_debug_mode = 'Y') THEN
4124 pa_debug.write_file(pa_debug.g_err_stage);
4125 END IF;
4126 pa_debug.g_err_stage :='p_calling_module is [' || p_calling_module || ']';
4127 IF(l_debug_mode = 'Y') THEN
4128 pa_debug.write_file(pa_debug.g_err_stage);
4129 END IF;
4130
4131 --- We derive the accrual dates for TRXIMPORT(Unaccounted)/Pre-Approved Batch(PAXTREPE).
4132
4133 IF p_calling_module in ( 'TRXIMPORT','PAXTREPE') AND NVL(p_acct_flag,'N') = 'N' THEN ------------------------{
4134 pa_debug.g_err_stage :='Getting the accrual Dates for TRX/PAX';
4135 IF(l_debug_mode = 'Y') THEN
4136 pa_debug.write_file(pa_debug.g_err_stage);
4137 END IF;
4138 x_error_stage := pa_debug.g_err_stage;
4139 IF p_adj_ei_id IS NULL THEN -------------ORG EI--------------------{
4140 pa_debug.g_err_stage :='Getting the accrual Dates for TRX/PAX ORG EI';
4141 IF(l_debug_mode = 'Y') THEN
4142 pa_debug.write_file(pa_debug.g_err_stage);
4143 END IF;
4144 x_error_stage := pa_debug.g_err_stage;
4145
4146 --- Though this api returns the period name also, we don't make use of it at this stage.
4147 BEGIN
4148 pa_utils2.get_accrual_gl_dt_period( p_calling_module => p_calling_module
4149 ,p_reference_date => p_expenditure_item_date
4150 ,p_application_id => l_pa_gl_app_id
4151 ,p_set_of_books_id => p_prvdr_sob_id
4152 ,p_prvdr_recvr_flg => 'P'
4153 ,p_epp_flag => l_prof_new_gldate_derivation
4154 ,x_gl_accr_period_name => l_prvdr_gl_period_name
4155 ,x_gl_accr_dt => l_prvdr_accrual_date
4156 ,x_return_status => x_return_status
4157 ,x_error_code => x_error_code
4158 ,x_error_stage => x_error_stage
4159 );
4160 END;
4161 -- Error encountered!!!
4162 IF x_error_code is NOT NULL THEN
4163 return;
4164 END IF;
4165 pa_debug.g_err_stage := 'Prvdr accrual Date for ORG EI TRX/PAX -'||to_char(l_prvdr_accrual_date);
4166 IF(l_debug_mode = 'Y') THEN
4167 pa_debug.write_file(pa_debug.g_err_stage);
4168 END IF;
4169 x_error_stage := pa_debug.g_err_stage;
4170 --- If provider and receiver are of same org, then don't derive the accrual date for RECVR
4171 IF p_prvdr_org_id = p_recvr_org_id THEN ---------------------------------{
4172
4173 l_recvr_accrual_date := l_prvdr_accrual_date;
4174 pa_debug.g_err_stage := 'Recvr accrual Date is Same as Prvdr for ORG EI TRX/PAX';
4175 IF(l_debug_mode = 'Y') THEN
4176 pa_debug.write_file(pa_debug.g_err_stage);
4177 END IF;
4178 x_error_stage := pa_debug.g_err_stage;
4179
4180 ELSE ----Do the check for O/F status.
4181
4182 -- Though we are not deriving the gl_period_name , this api is multi-function.
4183 -- It checks if the ei date is in an O/F period. This check is for RECVR.
4184 BEGIN
4185 pa_utils2.get_accrual_gl_dt_period( p_calling_module => p_calling_module
4186 ,p_reference_date => p_expenditure_item_date
4187 ,p_application_id => l_pa_gl_app_id
4188 ,p_set_of_books_id => p_recvr_sob_id
4189 ,p_prvdr_recvr_flg => 'R'
4190 ,p_epp_flag => l_prof_new_gldate_derivation
4191 ,x_gl_accr_period_name => l_recvr_gl_period_name
4192 ,x_gl_accr_dt => l_recvr_accrual_date
4193 ,x_return_status => x_return_status
4194 ,x_error_code => x_error_code
4195 ,x_error_stage => x_error_stage
4196 );
4197 END;
4198 -- Error encountered!!!
4199 IF x_error_code IS NOT NULL THEN
4200 return;
4201 END IF;
4202 pa_debug.g_err_stage := 'Recvr accrual Date for ORG EI TRX/PAX -'||to_char(l_recvr_accrual_date);
4203 IF(l_debug_mode = 'Y') THEN
4204 pa_debug.write_file(pa_debug.g_err_stage);
4205 END IF;
4206 x_error_stage := pa_debug.g_err_stage;
4207
4208 END IF; ----------------------p_prvdr_org_id = p_recvr_org_id------------------}
4209
4210 ELSE -----------------------For Reversing EI
4211
4212 --- For REV EI, the accrual dates of the
4213 --- ORG EI are passed. Since its an IN OUT parameter, we read and write to the same parameter.
4214 --- For REV EI, the accrual date is the first/last day of the next O,F GL Period depending on EPP.
4215
4216 l_prvdr_accrual_date := pa_utils2.get_rev_accrual_date(p_calling_module => p_calling_module
4217 ,p_reference_date =>x_prvdr_accrual_date
4218 ,p_application_id => l_pa_gl_app_id
4219 ,p_set_of_books_id => p_prvdr_sob_id
4220 ,p_prvdr_recvr_flg => 'P'
4221 ,p_epp_flag => l_prof_new_gldate_derivation
4222 ,x_gl_period_name => l_prvdr_gl_period_name
4223 ,x_return_status => x_return_status
4224 ,x_error_code => x_error_code
4225 ,x_error_stage => x_error_stage);
4226 -- Error encountered!!!
4227 IF x_error_code IS NOT NULL THEN
4228 return;
4229 END IF;
4230 pa_debug.g_err_stage := 'Prvdr accrual Date for Rev EI TRX/PAX -'||to_char(l_prvdr_accrual_date);
4231 IF(l_debug_mode = 'Y') THEN
4232 pa_debug.write_file(pa_debug.g_err_stage);
4233 END IF;
4234 x_error_stage := pa_debug.g_err_stage;
4235
4236 IF p_prvdr_org_id = p_recvr_org_id THEN -------------------------{
4237
4238 l_recvr_accrual_date := l_prvdr_accrual_date;
4239 pa_debug.g_err_stage := 'Recvr accrual Date= Prvdr Accrual Date for Rev EI TRX/PAX';
4240 IF(l_debug_mode = 'Y') THEN
4241 pa_debug.write_file(pa_debug.g_err_stage);
4242 END IF;
4243 x_error_stage := pa_debug.g_err_stage;
4244
4245 ELSE
4246 l_recvr_accrual_date := pa_utils2.get_rev_accrual_date( p_calling_module => p_calling_module
4247 ,p_reference_date =>x_recvr_accrual_date
4248 ,p_application_id => l_pa_gl_app_id
4249 ,p_set_of_books_id => p_recvr_sob_id
4250 ,p_prvdr_recvr_flg => 'R'
4251 ,p_epp_flag => l_prof_new_gldate_derivation
4252 ,x_gl_period_name => l_recvr_gl_period_name
4253 ,x_return_status => x_return_status
4254 ,x_error_code => x_error_code
4255 ,x_error_stage => x_error_stage);
4256
4257 -- Error encountered!!!
4258 IF x_error_code IS NOT NULL THEN
4259 return;
4260 END IF;
4261 pa_debug.g_err_stage := 'Recvr accrual Date for Rev EI Trx/Pax -'||to_char(l_recvr_accrual_date);
4262 IF(l_debug_mode = 'Y') THEN
4263 pa_debug.write_file(pa_debug.g_err_stage);
4264 END IF;
4265 x_error_stage := pa_debug.g_err_stage;
4266 END IF; ---------------p_prvdr_org_id = p_recvr_org_id-------------}
4267
4268 END IF; ---------------------ORG EI---------------------------------------------------}
4269
4270
4271 x_prvdr_accrual_date := l_prvdr_accrual_date;
4272 x_recvr_accrual_date := l_recvr_accrual_date;
4273
4274 pa_debug.g_err_stage := ' x_prvdr accr Dt for EI TRX/PAX -'||to_char(x_prvdr_accrual_date);
4275 IF(l_debug_mode = 'Y') THEN
4276 pa_debug.write_file(pa_debug.g_err_stage);
4277 END IF;
4278 pa_debug.g_err_stage := 'x_recvr accr Dt for EI TRX/PAX -'||to_char(x_recvr_accrual_date);
4279 IF(l_debug_mode = 'Y') THEN
4280 pa_debug.write_file(pa_debug.g_err_stage);
4281 END IF;
4282 return; -- We don't need anything after this point for Unaccounted TRXIMPORT and PAXTREPE Transactions.
4283
4284 END IF; ------( 'TRXIMPORT','PAXTREPE') AND NVL(p_acct_flag,'N') = 'N' --------------------------------}
4285
4286 ---- Call is for CDL/Accounted Transaction Import(TRXIMPRT)
4287
4288 IF p_calling_module = 'CDL' OR NVL(p_acct_flag,'N') = 'Y' THEN ------------------------{
4289
4290 IF p_calling_module = 'CDL' THEN-------------------------{
4291
4292 pa_debug.g_err_stage :='Call is from Costing program';
4293 IF(l_debug_mode = 'Y') THEN
4294 pa_debug.write_file(pa_debug.g_err_stage);
4295 END IF;
4296 pa_debug.g_err_stage := 'Prvdr accr Dt from CDL call -'||to_char(x_prvdr_accrual_date);
4297 IF(l_debug_mode = 'Y') THEN
4298 pa_debug.write_file(pa_debug.g_err_stage);
4299 END IF;
4300 pa_debug.g_err_stage := ' Recvr accr Dt from CDL call -'||to_char(x_recvr_accrual_date);
4301 IF(l_debug_mode = 'Y') THEN
4302 pa_debug.write_file(pa_debug.g_err_stage);
4303 END IF;
4304
4305 --- We make use of the GL period name.
4306 --- This is just to check if the accrual date is still in an open period.
4307 BEGIN
4308 pa_utils2.get_accrual_gl_dt_period( p_calling_module => p_calling_module
4309 ,p_reference_date => x_prvdr_accrual_date
4310 ,p_application_id => l_pa_gl_app_id
4311 ,p_set_of_books_id => p_prvdr_sob_id
4312 ,p_prvdr_recvr_flg => 'P'
4313 ,p_epp_flag => l_prof_new_gldate_derivation
4314 ,x_gl_accr_period_name => l_prvdr_gl_period_name
4315 ,x_gl_accr_dt => l_prvdr_accrual_date
4316 ,x_return_status => x_return_status
4317 ,x_error_code => x_error_code
4318 ,x_error_stage => x_error_stage
4319 );
4320 END;
4321 -- Error encountered!!!
4322 IF x_error_code IS NOT NULL THEN
4323 return;
4324 ELSE --- Assign the passed accrual date to gl Date.
4325 l_prvdr_accrual_date := x_prvdr_accrual_date;
4326 l_prvdr_gl_date := l_prvdr_accrual_date;
4327 END IF;
4328
4329 pa_debug.g_err_stage :='Prvdr GL Date is -'||to_char(l_prvdr_gl_date);
4330 IF(l_debug_mode = 'Y') THEN
4331 pa_debug.write_file(pa_debug.g_err_stage);
4332 END IF;
4333 pa_debug.g_err_stage :='Prvdr GL Period is -'||l_prvdr_gl_period_name;
4334 IF(l_debug_mode = 'Y') THEN
4335 pa_debug.write_file(pa_debug.g_err_stage);
4336 END IF;
4337 --- If provider and receiver are of same org, then don't check the RECVR accrual date
4338 IF p_prvdr_org_id = p_recvr_org_id THEN ---------------------------------{
4339
4340 l_recvr_accrual_date := nvl(x_recvr_accrual_date,l_prvdr_accrual_date);
4341 l_recvr_gl_period_name := l_prvdr_gl_period_name ;
4342 l_recvr_gl_date := l_recvr_accrual_date;
4343
4344 pa_debug.g_err_stage :='Prvdr GL Date is = Recvr GL Date';
4345 IF(l_debug_mode = 'Y') THEN
4346 pa_debug.write_file(pa_debug.g_err_stage);
4347 END IF;
4348 ELSE ----Do the check for O/F status.
4349
4350 -- It checks if the accrual date is in an O/F period. This check is for RECVR.
4351 BEGIN
4352 pa_utils2.get_accrual_gl_dt_period( p_calling_module => p_calling_module
4353 ,p_reference_date => x_recvr_accrual_date
4354 ,p_application_id => l_pa_gl_app_id
4355 ,p_set_of_books_id => p_recvr_sob_id
4356 ,p_prvdr_recvr_flg => 'R'
4357 ,p_epp_flag => l_prof_new_gldate_derivation
4358 ,x_gl_accr_period_name => l_recvr_gl_period_name
4359 ,x_gl_accr_dt => l_recvr_accrual_date
4360 ,x_return_status => x_return_status
4361 ,x_error_code => x_error_code
4362 ,x_error_stage => x_error_stage
4363 );
4364 END;
4365 -- Error encountered!!!
4366 IF x_error_code IS NOT NULL THEN
4367 return;
4368 ELSE --- Assign the passed accrual date.
4369 l_recvr_accrual_date := x_recvr_accrual_date;
4370 l_recvr_gl_date := x_recvr_accrual_date;
4371 END IF;
4372
4373 pa_debug.g_err_stage :='Recvr GL Date is -'||to_char(l_recvr_gl_date);
4374 IF(l_debug_mode = 'Y') THEN
4375 pa_debug.write_file(pa_debug.g_err_stage);
4376 END IF;
4377 pa_debug.g_err_stage :='Recvr GL Period is -'||l_recvr_gl_period_name;
4378 IF(l_debug_mode = 'Y') THEN
4379 pa_debug.write_file(pa_debug.g_err_stage);
4380 END IF;
4381
4382 END IF; ----------------------p_prvdr_org_id = p_recvr_org_id------------------}
4383
4384 END IF; ---------p_calling_module = 'CDL' ---------------------}
4385
4386 --- The transaction is accounted, so assign the gl date to accrual date.
4387
4388 IF nvl(p_acct_flag,'N') = 'Y' THEN-------Accounted Transaction---------------------------{
4389
4390 IF p_adj_ei_id IS NULL THEN -------------ORG EI----------------------------------{
4391
4392 l_prvdr_accrual_date := x_prvdr_gl_date;
4393 l_prvdr_gl_date := x_prvdr_gl_date;
4394
4395 pa_debug.g_err_stage := 'Prvdr accrual Date for ORG EI Acct TRX -'||to_char(l_prvdr_accrual_date);
4396 IF(l_debug_mode = 'Y') THEN
4397 pa_debug.write_file(pa_debug.g_err_stage);
4398 END IF;
4399 pa_debug.g_err_stage := 'Prvdr GL Date for ORG EI Acct TRX -'||to_char(l_prvdr_gl_date);
4400 IF(l_debug_mode = 'Y') THEN
4401 pa_debug.write_file(pa_debug.g_err_stage);
4402 END IF;
4403 -- We need the Period name.
4404 BEGIN
4405 pa_utils2.get_accrual_gl_dt_period( p_calling_module => p_calling_module
4406 ,p_reference_date => l_prvdr_accrual_date
4407 ,p_application_id => l_pa_gl_app_id
4408 ,p_set_of_books_id => p_prvdr_sob_id
4409 ,p_prvdr_recvr_flg => 'P'
4410 ,p_epp_flag => l_prof_new_gldate_derivation
4411 ,x_gl_accr_period_name => l_prvdr_gl_period_name
4412 ,x_gl_accr_dt => l_prvdr_gl_date
4413 ,x_return_status => x_return_status
4414 ,x_error_code => x_error_code
4415 ,x_error_stage => x_error_stage
4416 );
4417 END;
4418 --- GL Date may be in a closed period , we just need the period name.
4419 IF l_prvdr_gl_period_name IS NOT NULL AND x_error_code is NOT NULL THEN
4420
4421 pa_debug.g_err_stage := 'Prvdr GL Period for ORG EI Acct TRX -'||l_prvdr_gl_period_name;
4422 IF(l_debug_mode = 'Y') THEN
4423 pa_debug.write_file(pa_debug.g_err_stage);
4424 END IF;
4425 x_return_status := NULL;
4426 x_error_code := NULL;
4427 ELSIF x_error_code IS NOT NULL THEN
4428
4429 pa_debug.g_err_stage := 'Prvdr GL Period Not Found for ORG EI Acct TRX -';
4430 IF(l_debug_mode = 'Y') THEN
4431 pa_debug.write_file(pa_debug.g_err_stage);
4432 END IF;
4433 x_return_status := FND_API.G_RET_STS_ERROR;
4434 x_error_code := 'PA_GL_PER_PRVDR_ACCR_CLOSED';
4435 return; -- Could not find the Period Name.
4436 END IF;
4437
4438 -- If Provider and Receiver are of same ORG, then assign the same prvdr gl date to recvrs' accr date.
4439 IF p_prvdr_org_id = p_recvr_org_id THEN -----------------------------------{
4440 l_recvr_accrual_date := x_prvdr_gl_date;
4441 l_recvr_gl_period_name := l_prvdr_gl_period_name;
4442 l_recvr_gl_date := l_recvr_accrual_date;
4443 ELSE --- derive the recvr_accrual_date based on the ei date.
4444 BEGIN
4445 pa_utils2.get_accrual_gl_dt_period( p_calling_module => p_calling_module
4446 ,p_reference_date => p_expenditure_item_date
4447 ,p_application_id => l_pa_gl_app_id
4448 ,p_set_of_books_id => p_recvr_sob_id
4449 ,p_prvdr_recvr_flg => 'R'
4450 ,p_epp_flag => l_prof_new_gldate_derivation
4451 ,x_gl_accr_period_name => l_recvr_gl_period_name
4452 ,x_gl_accr_dt => l_recvr_accrual_date
4453 ,x_return_status => x_return_status
4454 ,x_error_code => x_error_code
4455 ,x_error_stage => x_error_stage
4456 );
4457 END;
4458 -- Error encountered!!!
4459 IF x_error_code IS NOT NULL THEN
4460 return;
4461 ELSE --- Assign the recvr GL Date
4462 l_recvr_gl_date := l_recvr_accrual_date;
4463 END IF;
4464 END IF; -----------------------p_prvdr_org_id = p_recvr_org_id-------------}
4465 ELSE ----------------------------REV EI-----------------------------------------------
4466 --- For REV EI, the accrual dates of the
4467 --- ORG EI are passed. Since its an IN OUT parameter, we read and write to the same parameter.
4468 --- For REV EI, the accrual date is the first/last day of the next O,F GL Period depending on EPP.
4469
4470 l_prvdr_accrual_date := pa_utils2.get_rev_accrual_date(p_calling_module => p_calling_module
4471 ,p_reference_date =>x_prvdr_accrual_date
4472 ,p_application_id => l_pa_gl_app_id
4473 ,p_set_of_books_id => p_prvdr_sob_id
4474 ,p_prvdr_recvr_flg => 'P'
4475 ,p_epp_flag => l_prof_new_gldate_derivation
4476 ,x_gl_period_name => l_prvdr_gl_period_name
4477 ,x_return_status => x_return_status
4478 ,x_error_code => x_error_code
4479 ,x_error_stage => x_error_stage);
4480 -- Error encountered!!!
4481 IF x_error_code IS NOT NULL THEN
4482 return;
4483 END IF;
4484
4485 l_prvdr_gl_date := l_prvdr_accrual_date; --- Accounted Rev Trx.
4486
4487 pa_debug.g_err_stage := 'Prvdr GL/accrual Date for Rev EI Acct TRX -'||to_char(l_prvdr_accrual_date);
4488 IF(l_debug_mode = 'Y') THEN
4489 pa_debug.write_file(pa_debug.g_err_stage);
4490 END IF;
4491 pa_debug.g_err_stage := 'Prvdr GL Period for Rev EI Acct TRX -'||l_prvdr_gl_period_name;
4492 IF(l_debug_mode = 'Y') THEN
4493 pa_debug.write_file(pa_debug.g_err_stage);
4494 END IF;
4495 IF p_prvdr_org_id = p_recvr_org_id THEN -------------------------{
4496
4497 l_recvr_accrual_date := l_prvdr_accrual_date;
4498 l_recvr_gl_date := l_recvr_accrual_date;
4499 l_recvr_gl_period_name := l_prvdr_gl_period_name;
4500
4501 pa_debug.g_err_stage := 'Recvr is same as Prvdr GL/accrual Date for Rev EI Acct TRX ';
4502 IF(l_debug_mode = 'Y') THEN
4503 pa_debug.write_file(pa_debug.g_err_stage);
4504 END IF;
4505
4506 ELSE
4507 l_recvr_accrual_date := pa_utils2.get_rev_accrual_date( p_calling_module => p_calling_module
4508 ,p_reference_date =>x_recvr_accrual_date
4509 ,p_application_id => l_pa_gl_app_id
4510 ,p_set_of_books_id => p_recvr_sob_id
4511 ,p_prvdr_recvr_flg => 'R'
4512 ,p_epp_flag => l_prof_new_gldate_derivation
4513 ,x_gl_period_name => l_recvr_gl_period_name
4514 ,x_return_status => x_return_status
4515 ,x_error_code => x_error_code
4516 ,x_error_stage => x_error_stage);
4517 -- Error encountered!!!
4518 IF x_error_code IS NOT NULL THEN
4519 return;
4520 END IF;
4521 l_recvr_gl_date := l_recvr_accrual_date;
4522
4523 pa_debug.g_err_stage := ':Recvr accrual Date for Rev EI Acct TRX -'||to_char(l_recvr_accrual_date);
4524 IF(l_debug_mode = 'Y') THEN
4525 pa_debug.write_file(pa_debug.g_err_stage);
4526 END IF;
4527
4528 END IF; ---------------p_prvdr_org_id = p_recvr_org_id-------------}
4529 END IF; ------------------p_adj_ei_id IS NULL---------------------------------------}
4530 END IF; ------------------------Accounted Transaction----------------------------------------------}
4531
4532
4533 --Assign the out parameters
4534 x_prvdr_accrual_date := l_prvdr_accrual_date;
4535 x_recvr_accrual_date := l_recvr_accrual_date;
4536 x_prvdr_gl_date := nvl(x_prvdr_gl_date,l_prvdr_gl_date); --Don't overwrite the gl date from TRXIMPORT
4537 x_recvr_gl_date := l_recvr_gl_date ;
4538 x_prvdr_gl_period_name := l_prvdr_gl_period_name;
4539 x_recvr_gl_period_name := l_recvr_gl_period_name;
4540
4541 -- Deriving PA periods
4542
4543 pa_debug.g_err_stage := 'Deriving the PA Dates';
4544 IF(l_debug_mode = 'Y') THEN
4545 pa_debug.write_file(pa_debug.g_err_stage);
4546 END IF;
4547
4548 IF p_adj_ei_id IS NULL THEN ------------ORG EI-------------------------{
4549
4550 BEGIN
4551 --- Derive the org prvdr pa date and pa period.
4552 pa_utils2.get_accrual_pa_dt_period( p_gl_period=>l_prvdr_gl_period_name
4553 ,p_ei_date => p_expenditure_item_date
4554 ,p_org_id =>p_prvdr_org_id
4555 ,p_prvdr_recvr_flg => 'P'
4556 ,p_epp_flag => l_prof_new_gldate_derivation
4557 ,p_org_rev_flg => 'O'
4558 ,x_pa_date => l_prvdr_pa_date
4559 ,x_pa_period_name => l_prvdr_pa_period_name
4560 ,x_return_status =>x_return_status
4561 ,x_error_code =>x_error_code);
4562 END;
4563 --Error
4564 IF x_error_code IS NOT NULL THEN
4565 return;
4566 END IF;
4567
4568 pa_debug.g_err_stage := 'Prvdr PA Date -'||to_char(l_prvdr_pa_date);
4569 IF(l_debug_mode = 'Y') THEN
4570 pa_debug.write_file(pa_debug.g_err_stage);
4571 END IF;
4572 pa_debug.g_err_stage :='Prvdr PA Period Name -'||l_prvdr_pa_period_name;
4573 IF(l_debug_mode = 'Y') THEN
4574 pa_debug.write_file(pa_debug.g_err_stage);
4575 END IF;
4576 IF p_prvdr_org_id = p_recvr_org_id THEN
4577 l_recvr_pa_date := l_prvdr_pa_date;
4578 l_recvr_pa_period_name := l_prvdr_pa_period_name;
4579
4580 pa_debug.g_err_stage :='Prvdr PA Derivation = Recvr PA Derivation ORG EI ';
4581 IF(l_debug_mode = 'Y') THEN
4582 pa_debug.write_file(pa_debug.g_err_stage);
4583 END IF;
4584 ELSE
4585 --- Derive the org recvr pa date and pa period.
4586 BEGIN
4587 pa_utils2.get_accrual_pa_dt_period( p_gl_period=>l_recvr_gl_period_name
4588 ,p_ei_date => p_expenditure_item_date
4589 ,p_org_id =>p_recvr_org_id
4590 ,p_prvdr_recvr_flg => 'R'
4591 ,p_epp_flag => l_prof_new_gldate_derivation
4592 ,p_org_rev_flg => 'O'
4593 ,x_pa_date => l_recvr_pa_date
4594 ,x_pa_period_name => l_recvr_pa_period_name
4595 ,x_return_status =>x_return_status
4596 ,x_error_code =>x_error_code);
4597 END;
4598 --Error
4599 IF x_error_code IS NOT NULL THEN
4600 return;
4601 END IF;
4602
4603 pa_debug.g_err_stage := 'Recvr PA Date -'||to_char(l_recvr_pa_date);
4604 IF(l_debug_mode = 'Y') THEN
4605 pa_debug.write_file(pa_debug.g_err_stage);
4606 END IF;
4607 pa_debug.g_err_stage := 'Recvr PA Period Name -'||l_recvr_pa_period_name;
4608 IF(l_debug_mode = 'Y') THEN
4609 pa_debug.write_file(pa_debug.g_err_stage);
4610 END IF;
4611 END IF;
4612
4613 ELSE -------------------------REV ITEM-----------------------
4614
4615 pa_debug.g_err_stage := 'Deriving the PA Dates for REV EI';
4616 IF(l_debug_mode = 'Y') THEN
4617 pa_debug.write_file(pa_debug.g_err_stage);
4618 END IF;
4619 ----For Prvdr
4620 BEGIN
4621 --- Derive the org prvdr pa date and pa period.
4622 pa_utils2.get_accrual_pa_dt_period( p_gl_period=>l_prvdr_gl_period_name
4623 ,p_ei_date => p_expenditure_item_date
4624 ,p_org_id =>p_prvdr_org_id
4625 ,p_prvdr_recvr_flg => 'P'
4626 ,p_epp_flag => l_prof_new_gldate_derivation
4627 ,p_org_rev_flg => 'R'
4628 ,x_pa_date => l_prvdr_pa_date
4629 ,x_pa_period_name => l_prvdr_pa_period_name
4630 ,x_return_status =>x_return_status
4631 ,x_error_code =>x_error_code);
4632 END;
4633 --Error
4634 IF x_error_code IS NOT NULL THEN
4635 return;
4636 END IF;
4637
4638 pa_debug.g_err_stage := 'Prvdr PA Date -'||to_char(l_prvdr_pa_date);
4639 IF(l_debug_mode = 'Y') THEN
4640 pa_debug.write_file(pa_debug.g_err_stage);
4641 END IF;
4642 pa_debug.g_err_stage :='Prvdr PA Period Name -'||l_prvdr_pa_period_name;
4643 IF(l_debug_mode = 'Y') THEN
4644 pa_debug.write_file(pa_debug.g_err_stage);
4645 END IF;
4646
4647 ----For Recvr
4648 IF p_prvdr_org_id = p_recvr_org_id THEN
4649 l_recvr_pa_date := l_prvdr_pa_date;
4650 l_recvr_pa_period_name :=l_prvdr_pa_period_name ;
4651
4652 pa_debug.g_err_stage :='Prvdr PA Derivation = Recvr PA Derivation REV EI ';
4653 IF(l_debug_mode = 'Y') THEN
4654 pa_debug.write_file(pa_debug.g_err_stage);
4655 END IF;
4656 ELSE
4657 --- Derive the org recvr pa date and pa period.
4658 BEGIN
4659 pa_utils2.get_accrual_pa_dt_period( p_gl_period=>l_recvr_gl_period_name
4660 ,p_ei_date => p_expenditure_item_date
4661 ,p_org_id =>p_recvr_org_id
4662 ,p_prvdr_recvr_flg => 'R'
4663 ,p_epp_flag => l_prof_new_gldate_derivation
4664 ,p_org_rev_flg => 'R'
4665 ,x_pa_date => l_recvr_pa_date
4666 ,x_pa_period_name => l_recvr_pa_period_name
4667 ,x_return_status =>x_return_status
4668 ,x_error_code =>x_error_code);
4669 END;
4670 --Error
4671 IF x_error_code IS NOT NULL THEN
4672 return;
4673 END IF;
4674
4675 END IF;
4676
4677 pa_debug.g_err_stage := 'Recvr PA Date -'||to_char(l_recvr_pa_date);
4678 IF(l_debug_mode = 'Y') THEN
4679 pa_debug.write_file(pa_debug.g_err_stage);
4680 END IF;
4681 pa_debug.g_err_stage :='Recvr PA Period Name -'||l_recvr_pa_period_name;
4682 IF(l_debug_mode = 'Y') THEN
4683 pa_debug.write_file(pa_debug.g_err_stage);
4684 END IF;
4685
4686 END IF; ---------------ORG EI--------------------------------------------}
4687 ---Assign the out parameters.
4688 x_prvdr_pa_date := l_prvdr_pa_date;
4689 x_prvdr_pa_period_name := l_prvdr_pa_period_name;
4690 x_recvr_pa_date := l_recvr_pa_date;
4691 x_recvr_pa_period_name := l_recvr_pa_period_name;
4692 END IF; -----------p_calling_module = 'CDL' AND NVL(p_acct_flag,'N') = 'Y'------------------------}
4693
4694 pa_debug.g_err_stage :='x_prvdr_accr_date is [' || to_char(x_prvdr_accrual_date) ||
4695 '] x_recvr_accr_date is ['|| to_char(x_recvr_accrual_date) || ']';
4696 IF(l_debug_mode = 'Y') THEN
4697 pa_debug.write_file(pa_debug.g_err_stage);
4698 END IF;
4699
4700 pa_debug.g_err_stage :='x_prvdr_pa_date is [' || to_char(x_prvdr_pa_date) ||
4701 '] x_prvdr_pa_period_name is ['|| x_prvdr_pa_period_name || ']';
4702 IF(l_debug_mode = 'Y') THEN
4703 pa_debug.write_file(pa_debug.g_err_stage);
4704 END IF;
4705 pa_debug.g_err_stage :='x_prvdr_gl_date is [' || to_char(x_prvdr_gl_date) ||
4706 '] x_prvdr_gl_period_name is ['|| x_prvdr_gl_period_name || ']';
4707 IF(l_debug_mode = 'Y') THEN
4708 pa_debug.write_file(pa_debug.g_err_stage);
4709 END IF;
4710 pa_debug.g_err_stage :='x_recvr_pa_date is [' || to_char(x_recvr_pa_date) ||
4711 '] x_recvr_pa_period_name is ['|| x_recvr_pa_period_name || ']';
4712 IF(l_debug_mode = 'Y') THEN
4713 pa_debug.write_file(pa_debug.g_err_stage);
4714 END IF;
4715 pa_debug.g_err_stage :='x_recvr_gl_date is [' || to_char(x_recvr_gl_date) ||
4716 '] x_recvr_gl_period_name is ['|| x_recvr_gl_period_name || ']';
4717 IF(l_debug_mode = 'Y') THEN
4718 pa_debug.write_file(pa_debug.g_err_stage);
4719 END IF;
4720 pa_debug.g_err_stage :=' x_error_code is [' || x_error_code || ']';
4721 IF(l_debug_mode = 'Y') THEN
4722 pa_debug.write_file(pa_debug.g_err_stage);
4723 END IF;
4724
4725 x_return_status := FND_API.G_RET_STS_SUCCESS;
4726 pa_debug.reset_err_stack;
4727
4728 EXCEPTION
4729 WHEN NO_DATA_FOUND THEN
4730 RAISE;
4731 WHEN OTHERS THEN
4732 RAISE ;
4733 END get_accrual_period_information ;
4734 -----------------------------------------------------------------------
4735 FUNCTION get_pa_period_name( p_txn_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
4736 ,p_org_id IN pa_implementations_all.org_id%TYPE
4737 )
4738 RETURN pa_periods.period_name%TYPE
4739 IS
4740 l_pa_period_name pa_periods.period_name%TYPE;
4741 l_pa_date date;
4742 BEGIN
4743
4744 IF ( p_txn_date IS NOT NULL )
4745 THEN
4746 l_pa_date := pa_utils2.get_pa_date( p_ei_date => p_txn_date
4747 ,p_gl_date => SYSDATE
4748 ,p_org_id => p_org_id
4749 );
4750 l_pa_period_name := g_prvdr_pa_period_name;
4751 END IF;
4752 RETURN l_pa_period_name ;
4753 EXCEPTION
4754 WHEN OTHERS
4755 THEN
4756 RAISE;
4757 END get_pa_period_name ;
4758
4759 -----------------------------------------------------------------------
4760 --Start Bug 3069632
4761 FUNCTION get_ts_allow_burden_flag( p_transaction_source IN pa_expenditure_items_all.transaction_source%TYPE)
4762 RETURN pa_transaction_sources.allow_burden_flag%TYPE
4763 IS
4764 l_allow_burden_flag pa_transaction_sources.allow_burden_flag%TYPE := 'N';
4765 BEGIN
4766 IF ( p_transaction_source IS NOT NULL )
4767 THEN
4768 If p_transaction_source = pa_utils2.g_transaction_source Then
4769 Return g_ts_allow_burden_flag;
4770
4771 Else
4772
4773 select ts.allow_burden_flag
4774 into l_allow_burden_flag
4775 from pa_transaction_sources ts
4776 where ts.transaction_source = p_transaction_source;
4777
4778 pa_utils2.g_ts_allow_burden_flag := l_allow_burden_flag;
4779 pa_utils2.g_transaction_source := p_transaction_source;
4780 RETURN l_allow_burden_flag;
4781 End If;
4782 ELSE
4783 RETURN 'N';
4784 END IF;
4785 EXCEPTION
4786 WHEN OTHERS
4787 THEN
4788 RAISE;
4789 END get_ts_allow_burden_flag ;
4790
4791 -----------------------------------------------------------------------
4792
4793 --Start Bug 3059344
4794 Function Get_Burden_Amt_Display_Method(P_Project_Id in Number) Return Varchar2
4795 Is
4796 l_Found BOOLEAN := FALSE;
4797 x_burden_method VARCHAR2(1);
4798 Begin
4799 -- Check if there are any records in the pl/sql table.
4800 If G_BdMethodProjID_Tab.COUNT > 0 Then
4801 --Dbms_Output.Put_Line('count > 0');
4802 Begin
4803 X_Burden_Method := G_BdMethodProjID_Tab(P_Project_Id);
4804 l_Found := TRUE;
4805 --Dbms_Output.Put_Line('l_found TRUE');
4806 Exception
4807 When No_Data_Found Then
4808 l_Found := FALSE;
4809 When Others Then
4810 Raise;
4811 End;
4812
4813 End If;
4814
4815 If Not l_Found Then
4816 --Dbms_Output.Put_Line('l_found FALSE');
4817
4818 If G_BdMethodProjID_Tab.COUNT > 999 Then
4819 --Dbms_Output.Put_Line('count > 199');
4820 G_BdMethodProjID_Tab.Delete;
4821 End If;
4822 Begin
4823 --Dbms_Output.Put_Line('select');
4824 SELECT DECODE(pt.burden_amt_display_method, 'D', 'D'
4825 , DECODE(pt.BURDEN_SUM_DEST_PROJECT_ID, NULL
4826 , DECODE(pt.BURDEN_SUM_DEST_TASK_ID, NULL, 'S', 'D'), 'D'))
4827 INTO x_burden_method
4828 FROM pa_project_types_all pt
4829 ,pa_projects_all p
4830 WHERE p.project_id = P_Project_Id
4831 AND p.project_type = pt.project_type
4832 AND pt.org_id = p.org_id
4833 AND pt.burden_cost_flag = 'Y';
4834
4835 G_BdMethodProjID_Tab(P_Project_Id) := x_burden_method;
4836 --Dbms_Output.Put_Line('after select');
4837 Exception
4838 When No_Data_Found Then
4839 --Dbms_Output.Put_Line('wndf ');
4840 x_burden_method := 'N';
4841 G_BdMethodProjID_Tab(P_Project_Id) := 'N';
4842 End;
4843
4844 End If;
4845
4846 Return x_burden_method;
4847
4848 Exception
4849 When Others Then
4850 RETURN 'N';
4851
4852 End Get_Burden_Amt_Display_Method;
4853
4854
4855 /* S.N. Bug4746949 */
4856 Function Proj_Type_Burden_Disp_Method(P_Project_Id in Number) Return Varchar2
4857 Is
4858 l_Found BOOLEAN := FALSE;
4859 x_burden_method VARCHAR2(1);
4860 Begin
4861 -- Check if there are any records in the pl/sql table.
4862 If G_Bd_MethodProjID_Tab.COUNT > 0 Then
4863 --Dbms_Output.Put_Line('count > 0');
4864 Begin
4865 X_Burden_Method := G_Bd_MethodProjID_Tab(P_Project_Id);
4866 l_Found := TRUE;
4867 --Dbms_Output.Put_Line('l_found TRUE');
4868 Exception
4869 When No_Data_Found Then
4870 l_Found := FALSE;
4871 When Others Then
4872 Raise;
4873 End;
4874
4875 End If;
4876
4877 If Not l_Found Then
4878 --Dbms_Output.Put_Line('l_found FALSE');
4879
4880 If G_Bd_MethodProjID_Tab.COUNT > 999 Then
4881 --Dbms_Output.Put_Line('count > 199');
4882 G_Bd_MethodProjID_Tab.Delete;
4883 End If;
4884 Begin
4885 --Dbms_Output.Put_Line('select');
4886 SELECT pt.burden_amt_display_method
4887 INTO x_burden_method
4888 FROM pa_project_types_all pt
4889 ,pa_projects_all p
4890 WHERE p.project_id = P_Project_Id
4891 AND p.project_type = pt.project_type
4892 -- begin bug 5614790
4893 -- AND NVL(pt.org_id,-99) = nvl(p.org_id,-99)
4894 AND pt.org_id = p.org_id
4895 -- end bug 5614790
4896 AND pt.burden_cost_flag = 'Y';
4897
4898 G_Bd_MethodProjID_Tab(P_Project_Id) := x_burden_method;
4899 --Dbms_Output.Put_Line('after select');
4900 Exception
4901 When No_Data_Found Then
4902 --Dbms_Output.Put_Line('wndf ');
4903 x_burden_method := 'N';
4904 G_Bd_MethodProjID_Tab(P_Project_Id) := 'N';
4905 End;
4906
4907 End If;
4908
4909 Return x_burden_method;
4910
4911 Exception
4912 When Others Then
4913 RETURN 'N';
4914
4915 End Proj_Type_Burden_Disp_Method;
4916
4917 /* E.N. Bug4746949 */
4918
4919 Function get_capital_cost_type_code( p_project_id IN pa_projects_all.project_id%TYPE)
4920 RETURN pa_project_types_all.CAPITAL_COST_TYPE_CODE%TYPE
4921 Is
4922 l_Found BOOLEAN := FALSE;
4923 x_capital_cost_type VARCHAR2(1);
4924 Begin
4925 -- Check if there are any records in the pl/sql table.
4926 If G_CapCostTypProjID_Tab.COUNT > 0 Then
4927 --Dbms_Output.Put_Line('count > 0');
4928 Begin
4929 x_capital_cost_type := G_CapCostTypProjID_Tab(P_Project_Id);
4930 l_Found := TRUE;
4931 --Dbms_Output.Put_Line('l_found TRUE');
4932 Exception
4933 When No_Data_Found Then
4934 l_Found := FALSE;
4935 When Others Then
4936 Raise;
4937 End;
4938
4939 End If;
4940
4941 If Not l_Found Then
4942 --Dbms_Output.Put_Line('l_found FALSE');
4943
4944 If G_CapCostTypProjID_Tab.COUNT > 999 Then
4945 --Dbms_Output.Put_Line('count > 199');
4946 G_CapCostTypProjID_Tab.Delete;
4947 End If;
4948 Begin
4949 --Dbms_Output.Put_Line('select');
4950 SELECT pt.capital_cost_type_code
4951 INTO x_capital_cost_type
4952 FROM pa_project_types_all pt
4953 ,pa_projects_all p
4954 WHERE p.project_id = P_Project_Id
4955 AND p.project_type = pt.project_type
4956 -- begin bug 5614790
4957 -- AND NVL(pt.org_id,-99) = nvl(p.org_id,-99)
4958 AND pt.org_id = p.org_id
4959 -- end bug 5614790
4960 AND pt.project_type_class_code = 'CAPITAL';
4961
4962 G_CapCostTypProjID_Tab(P_Project_Id) := x_capital_cost_type;
4963 --Dbms_Output.Put_Line('after select');
4964
4965 Exception
4966 When No_Data_Found Then
4967 --Dbms_Output.Put_Line('wndf ');
4968 x_capital_cost_type := 'N';
4969 G_CapCostTypProjID_Tab(P_Project_Id) := 'N';
4970 End;
4971
4972 End If;
4973
4974 Return x_capital_cost_type;
4975
4976 Exception
4977 When Others Then
4978 RETURN 'N';
4979
4980 End get_capital_cost_type_code;
4981 --End Bug 3059344
4982
4983 FUNCTION IsEnhancedBurdeningEnabled
4984 RETURN VARCHAR2
4985 IS
4986 BEGIN
4987
4988 IF ( NVL(fnd_profile.value('PA_ENHANCED_BURDENING'),'N') = 'Y' )
4989 THEN
4990 IF (PA_GMS_API.vert_install)
4991 THEN
4992 RETURN 'N';
4993 ELSE
4994 RETURN 'Y';
4995 END IF;
4996 ELSE
4997 RETURN 'N';
4998 END IF;
4999
5000 EXCEPTION
5001 WHEN OTHERS
5002 THEN
5003 RETURN 'N';
5004 End IsEnhancedBurdeningEnabled;
5005
5006 /* Bug 5374282 */
5007 PROCEDURE get_gl_dt_period (p_reference_date IN DATE,
5008 x_gl_period_name OUT NOCOPY pa_draft_revenues_all.gl_period_name%TYPE,
5009 x_gl_dt OUT NOCOPY pa_draft_revenues_all.gl_date%TYPE,
5010 x_return_status OUT NOCOPY NUMBER,
5011 x_error_code OUT NOCOPY VARCHAR2,
5012 x_error_stage OUT NOCOPY VARCHAR2
5013 )
5014 IS
5015 l_gl_period_name pa_draft_revenues_all.gl_period_name%TYPE := NULL;
5016 l_gl_dt pa_draft_revenues_all.gl_date%TYPE := NULL;
5017 l_gl_period_st_dt pa_draft_revenues_all.gl_date%TYPE := NULL;
5018 l_gl_period_end_dt pa_draft_revenues_all.gl_date%TYPE := NULL;
5019 l_period_status gl_period_statuses.closing_status%TYPE := NULL;
5020
5021 l_set_of_books_id pa_implementations_all.set_of_books_id%TYPE;
5022
5023 l_gl_app_id NUMBER := 101;
5024 l_epp_flag VARCHAR2(1) := 'N';
5025 l_application_id NUMBER := NULL ;
5026
5027 BEGIN
5028
5029 pa_debug.write_file('PA_UTILS2.GET_GL_PERIOD: Entering procedure');
5030
5031 /* Changed from value_specific to value for bug 5472333 */
5032 l_epp_flag := fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION') ;
5033
5034 pa_debug.write_file('PA_UTILS2.GET_GL_PERIOD: EPP Flag status: ' || l_epp_flag);
5035
5036 l_application_id := l_gl_app_id ;
5037
5038 SELECT imp.set_of_books_id
5039 INTO l_set_of_books_id
5040 FROM pa_implementations imp;
5041
5042 pa_debug.write_file('PA_UTILS2.GET_GL_PERIOD: Using (appl_id, sob_id): (' || to_char(l_application_id) || ', ' || to_char(l_set_of_books_id) || ')' );
5043
5044 ---Initialize the out variables.
5045 x_gl_period_name := NULL;
5046 x_gl_dt := NULL;
5047 x_return_status := 0;
5048 x_error_code := NULL;
5049
5050 pa_debug.write_file('PA_UTILS2.GET_GL_PERIOD: Trying reference_date with cached start and end date values' );
5051
5052 IF ((trunc(p_reference_date) BETWEEN g_gl_dt_period_str_dt AND g_gl_dt_period_end_dt )
5053 AND g_gl_dt_period_str_dt IS NOT NULL) THEN
5054
5055 pa_debug.write_file('PA_UTILS2.GET_GL_PERIOD: Using cached values' );
5056
5057 ---From Cache
5058 IF l_epp_flag = 'Y' THEN
5059 l_gl_dt := p_reference_date;
5060 ELSE
5061 l_gl_dt := g_gl_dt_period_end_dt;
5062 END IF;
5063
5064 --Assign the out variables.
5065 x_gl_dt := l_gl_dt ;
5066 x_gl_period_name := g_gl_dt_period_name;
5067
5068 pa_debug.write_file('PA_UTILS2.GET_GL_PERIOD: Leaving procedure');
5069
5070 return;
5071
5072 END IF;
5073
5074 --- Either the cache is empty or the reference date is not in the range.
5075
5076 pa_debug.write_file('PA_UTILS2.GET_GL_PERIOD: Cache values empty/reference date not between cached start and end date values' );
5077
5078 BEGIN
5079
5080 SELECT PERIOD.period_name,PERIOD.start_date,PERIOD.end_date,PERIOD.closing_status
5081 INTO l_gl_period_name, l_gl_period_st_dt,l_gl_period_end_dt,l_period_status
5082 FROM GL_PERIOD_STATUSES PERIOD
5083 WHERE PERIOD.application_id = l_application_id
5084 AND PERIOD.set_of_books_id = l_set_of_books_id
5085 AND PERIOD.adjustment_period_flag = 'N'
5086 AND trunc(p_reference_date) BETWEEN PERIOD.start_date and PERIOD.end_date;
5087
5088 EXCEPTION
5089 WHEN NO_DATA_FOUND THEN
5090 x_return_status := -1;
5091 x_error_code := 'PA_GL_PER_PRVDR_ACCR_NOT_DEF';
5092
5093 return;
5094 WHEN OTHERS THEN
5095 x_return_status := -1;
5096 x_error_stage := 'PA_UTILS2.GET_GL_PERIOD:: ' || pa_debug.g_err_stage ||':: '|| SQLERRM;
5097
5098 raise;
5099 END;
5100
5101 -- EPP Derivation.
5102 IF l_epp_flag = 'Y' THEN
5103 l_gl_dt := p_reference_date;
5104 ELSE
5105 l_gl_dt := l_gl_period_end_dt;
5106 END IF;
5107
5108 pa_debug.write_file('PA_UTILS2.GET_GL_PERIOD: Checking for period status' );
5109
5110 --Checking for period status.
5111 IF l_period_status NOT IN ('O','F') THEN
5112
5113 pa_debug.write_file('PA_UTILS2.GET_GL_PERIOD: Period is closed' );
5114
5115 IF l_epp_flag = 'N' THEN
5116
5117 pa_debug.g_err_stage := 'EPP = N, Querying next open period'; /* Added Code for Bug 6139975 */
5118
5119 SELECT PERIOD.start_date,
5120 PERIOD.end_date,
5121 PERIOD.end_date,
5122 PERIOD.period_name
5123 INTO l_gl_period_st_dt,
5124 l_gl_period_end_dt,
5125 l_gl_dt,
5126 l_gl_period_name
5127 FROM GL_PERIOD_STATUSES PERIOD
5128 WHERE PERIOD.application_id = l_application_id
5129 AND PERIOD.set_of_books_id = l_set_of_books_id
5130 AND PERIOD.effective_period_num =
5131 (SELECT min(PERIOD1.effective_period_num)
5132 FROM GL_PERIOD_STATUSES PERIOD1
5133 WHERE PERIOD1.application_id = l_application_id
5134 AND PERIOD1.set_of_books_id = l_set_of_books_id
5135 AND PERIOD1.closing_status||'' IN ('O','F')
5136 AND PERIOD1.adjustment_period_flag = 'N'
5137 AND PERIOD1.effective_period_num >=
5138 (SELECT PERIOD2.effective_period_num
5139 FROM GL_PERIOD_STATUSES PERIOD2,
5140 GL_DATE_PERIOD_MAP DPM,
5141 GL_SETS_OF_BOOKS SOB
5142 WHERE SOB.set_of_books_id = l_set_of_books_id
5143 AND DPM.period_set_name = SOB.period_set_name
5144 AND DPM.period_type = SOB.accounted_period_type
5145 AND trunc(DPM.accounting_date) = trunc(p_reference_date)
5146 AND DPM.period_name = PERIOD2.period_name
5147 AND PERIOD2.application_id = l_application_id
5148 AND PERIOD2.set_of_books_id = l_set_of_books_id ))
5149 AND PERIOD.End_Date >= TRUNC(p_reference_date)
5150 AND PERIOD.set_of_books_id = l_set_of_books_id ;
5151
5152 pa_debug.g_err_stage := 'EPP = N, Fetched next open period'; /* Added Code for Bug 6139975 */
5153
5154 ELSE
5155
5156 pa_debug.g_err_stage := 'EPP = Y, Querying next open period'; /* Added Code for Bug 6139975 */
5157
5158 SELECT PERIOD.start_date,
5159 PERIOD.start_date,
5160 PERIOD.end_date,
5161 PERIOD.period_name
5162 INTO l_gl_dt,
5163 l_gl_period_st_dt,
5164 l_gl_period_end_dt,
5165 l_gl_period_name
5166 FROM GL_PERIOD_STATUSES PERIOD
5167 WHERE PERIOD.application_id = l_application_id
5168 AND PERIOD.set_of_books_id = l_set_of_books_id
5169 AND PERIOD.effective_period_num =
5170 (SELECT min(PERIOD1.effective_period_num)
5171 FROM GL_PERIOD_STATUSES PERIOD1
5172 WHERE PERIOD1.application_id = l_application_id
5173 AND PERIOD1.set_of_books_id = l_set_of_books_id
5174 AND PERIOD1.closing_status||'' IN ('O','F')
5175 AND PERIOD1.adjustment_period_flag = 'N'
5176 AND PERIOD1.effective_period_num >=
5177 (SELECT PERIOD2.effective_period_num
5178 FROM GL_PERIOD_STATUSES PERIOD2,
5179 GL_DATE_PERIOD_MAP DPM,
5180 GL_SETS_OF_BOOKS SOB
5181 WHERE SOB.set_of_books_id = l_set_of_books_id
5182 AND DPM.period_set_name = SOB.period_set_name
5183 AND DPM.period_type = SOB.accounted_period_type
5184 AND trunc(DPM.accounting_date) = trunc(p_reference_date)
5185 AND DPM.period_name = PERIOD2.period_name
5186 AND PERIOD2.application_id = l_application_id
5187 AND PERIOD2.set_of_books_id = l_set_of_books_id ))
5188 AND PERIOD.Start_Date > TRUNC(p_reference_date);
5189
5190 pa_debug.g_err_stage := 'EPP = Y, Fetched next open period'; /* Added Code for Bug 6139975 */
5191
5192 END IF;
5193
5194 END IF;
5195
5196 pa_debug.write_file('PA_UTILS2.GET_GL_PERIOD: Caching dates' );
5197
5198 g_gl_dt_period_name := l_gl_period_name;
5199 g_gl_dt_period_str_dt := l_gl_period_st_dt ;
5200 g_gl_dt_period_end_dt := l_gl_period_end_dt;
5201
5202 x_gl_dt := l_gl_dt ;
5203 x_gl_period_name := l_gl_period_name ;
5204
5205 pa_debug.write_file('PA_UTILS2.GET_GL_PERIOD: Leaving procedure');
5206
5207 EXCEPTION
5208
5209 /* Start of code added for Bug 6139975 */
5210
5211 WHEN NO_DATA_FOUND THEN
5212 x_return_status := -1;
5213 x_error_code := 'NO_GL_DATE';
5214 x_error_stage := 'PA_UTILS2.GET_GL_PERIOD:: ' || pa_debug.g_err_stage ||':: '|| SQLERRM;
5215
5216 return;
5217
5218 /* End of code added for Bug 6139975 */
5219
5220 WHEN OTHERS THEN
5221 x_return_status := -1;
5222 x_error_stage := 'PA_UTILS2.GET_GL_PERIOD:: ' || pa_debug.g_err_stage ||':: '|| SQLERRM;
5223
5224 raise;
5225 END get_gl_dt_period ;
5226
5227 /* 5374282 ends */
5228
5229
5230 END pa_utils2;
[Home] [Help]