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