1 PACKAGE BODY pa_integration AS
2 --$Header: PAXPINTB.pls 120.7 2010/05/13 10:15:55 lamalviy ship $
3
4 l_invoice_id NUMBER;
5 l_invoice_status Varchar2(30);
6 l_status_type Varchar2(30);
7
8 G_PrevPeriodName pa_cost_distribution_lines_all.gl_period_name%TYPE;
9 G_PrevPdStDate DATE;
10 G_PrevPdEdDate DATE;
11 G_PrevSOBId NUMBER;
12
13 -- FUNCTION get_period_name /*2835063*/
14 FUNCTION get_period_name RETURN pa_cost_distribution_lines_all.pa_period_name%TYPE is
15 BEGIN
16 /* Please note that this function should be used only after ensuring that
17 get_raw_cdl_pa_date() is called, so that the returned variable's value has a
18 non-NULL value */
19 return g_prvdr_pa_period_name;
20 end get_period_name;
21
22 FUNCTION pending_vi_adjustments_exists( P_invoice_id IN NUMBER )
23 RETURN varchar2 IS
24 --
25 -- CDL's that are not yet transfered to AP
26 --
27 CURSOR pending_transfer IS
28 SELECT 'AP_PROJ_TASK_EXIST_PA'
29 FROM
30 PA_COST_DISTRIBUTION_LINES CDL,
31 PA_EXPENDITURE_ITEMS EI
32 WHERE
33 EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
34 AND CDL.TRANSFER_STATUS_CODE IN ('P','R','X')
35 AND EI.SYSTEM_LINKAGE_FUNCTION in ( 'VI','ER')
36 AND CDL.LINE_TYPE = 'R'
37 AND CDL.system_reference2 = to_char(P_invoice_id);
38 --
39 -- Expenditure items that are
40 -- split/transfered but not cost distributed.
41 --
42 CURSOR pending_ei IS
43 SELECT 'AP_SPLIT_EXIST_PA'
44 FROM
45 PA_COST_DISTRIBUTION_LINES CDL
46 WHERE
47 CDL.system_reference2 = to_char(P_invoice_id)
48 AND CDL.transfer_status_code||'' IN ('V','A')
49 AND CDL.line_type = 'R'
50 AND EXISTS
51 ( SELECT ' There are Splits/Transfers on EI'
52 FROM PA_EXPENDITURE_ITEMS EI
53 WHERE EI.SYSTEM_LINKAGE_FUNCTION in ( 'VI', 'ER' )
54 AND EI.TRANSFERRED_FROM_EXP_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
55 AND EI.ADJUSTED_EXPENDITURE_ITEM_ID IS NULL
56 AND EI.COST_DISTRIBUTED_FLAG||'' = 'N'
57 );
58 --
59 -- Expenditure items that are marked for recalc
60 --
61
62 CURSOR pending_recalc IS
63 SELECT 'AP_RECALC_COST_PA'
64 FROM
65 PA_COST_DISTRIBUTION_LINES CDL
66 WHERE
67 CDL.system_reference2 = to_char(P_invoice_id)
68 AND CDL.transfer_status_code||'' IN ( 'V', 'A' )
69 AND CDL.line_type = 'R'
70 AND EXISTS
71 ( SELECT 'Marked for recalc'
72 FROM PA_EXPENDITURE_ITEMS EI
73 WHERE EI.SYSTEM_LINKAGE_FUNCTION in ( 'VI','ER')
74 AND EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
75 AND EI.COST_DISTRIBUTED_FLAG = 'N'
76 );
77 --
78 -- Checking for reversals
79 --
80
81 CURSOR pending_reversed IS
82 SELECT 'AP_ADJ_EXIST_PA'
83 FROM
84 PA_COST_DISTRIBUTION_LINES CDL
85 WHERE
86 CDL.system_reference2 = to_char(P_invoice_id)
87 AND CDL.transfer_status_code ||'' IN ('V','A')
88 AND CDL.line_type = 'R'
89 AND EXISTS
90 ( SELECT ' Reversed EI '
91 FROM PA_EXPENDITURE_ITEMS EI
92 WHERE EI.SYSTEM_LINKAGE_FUNCTION in ( 'VI','ER')
93 AND EI.ADJUSTED_EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
94 AND EI.COST_DISTRIBUTED_FLAG||'' = 'N'
95 );
96
97 v_error_code varchar2(30) := 'Y';
98
99 BEGIN
100 OPEN pending_transfer;
101 FETCH pending_transfer INTO v_error_code;
102 IF ( v_error_code <> 'Y' ) THEN
103 CLOSE pending_transfer;
104 RETURN v_error_code;
105 END IF;
106 CLOSE pending_transfer; -- Added for Bug#5381711
107
108 OPEN pending_ei;
109 FETCH pending_ei INTO v_error_code;
110 IF ( v_error_code <> 'Y' ) THEN
111 CLOSE pending_ei;
112 RETURN v_error_code;
113 END IF;
114 CLOSE pending_ei; -- Added for Bug#5381711
115
116 OPEN pending_recalc;
117 FETCH pending_recalc INTO v_error_code;
118 IF ( v_error_code <> 'Y' ) THEN
119 CLOSE pending_recalc;
120 RETURN v_error_code;
121 END IF;
122 CLOSE pending_recalc; -- Added for Bug#5381711
123
124 OPEN pending_reversed;
125 FETCH pending_reversed INTO v_error_code;
126 IF ( v_error_code <> 'Y' ) THEN
127 CLOSE pending_reversed; -- Modified for Bug#5381711
128 RETURN v_error_code;
129 END IF;
130 CLOSE pending_reversed; -- Added for Bug#5381711
131
132 -- If you can get here, then there are no pending adjustments in PA
133 --
134 v_error_code := 'N';
135 RETURN v_error_code;
136
137 EXCEPTION WHEN others THEN
138 RAISE;
139 END pending_vi_adjustments_exists;
140
141 FUNCTION check_ap_invoices(p_invoice_id IN NUMBER,
142 p_status_type IN VARCHAR2) RETURN VARCHAR2 IS
143 v_error_code VARCHAR2(30) :='';
144 v_cancelled_date DATE;
145 v_cancelled_by NUMBER;
146 BEGIN
147 -- v_error_code := AP_PA_API_PKG.get_invoice_status(p_invoice_id,p_status_type); /* bug#5010877 */
148
149 -- Added this section to replace the above function call.
150 IF p_status_type = 'ADJUSTMENTS' THEN
151
152 SELECT CANCELLED_DATE,
153 CANCELLED_BY
154 INTO v_cancelled_date,
155 v_cancelled_by
156 FROM ap_invoices_all
157 WHERE invoice_id = p_invoice_id;
158
159 If (v_cancelled_date IS NOT NULL AND v_cancelled_by IS NOT NULL) THEN
160 v_error_code := 'PA_INV_CANCELLED';
161 else
162 v_error_code := 'N';
163 End if;
164
165 END IF;
166
167 RETURN(v_error_code);
168
169 EXCEPTION WHEN OTHERS THEN
170 RAISE;
171 END check_ap_invoices;
172
173 PROCEDURE init_ap_invoices IS
174 BEGIN
175 l_invoice_id := -1;
176 l_invoice_status := '';
177 l_status_type :='';
178 END init_ap_invoices;
179
180 FUNCTION ap_invoice_status( p_invoice_id IN NUMBER,
181 p_status_type In VARCHAR2) RETURN VARCHAR2 IS
182 pa_check_status VARCHAR2(30); /* For Bug 1969501 */
183 BEGIN
184 IF (( l_invoice_id = p_invoice_id ) and (l_status_type = p_status_type)) THEN
185 RETURN l_invoice_status;
186 ELSE
187 l_invoice_id := p_invoice_id;
188 l_status_type := p_status_type;
189
190 pa_check_status := pa_integration.check_ap_invoices(p_invoice_id,p_status_type);
191
192 IF pa_check_status = 'N' THEN
193 l_invoice_status := 'N';
194 ELSIF pa_check_status = 'PA_INV_CANCELLED' THEN
195 l_invoice_status := 'C';
196 ELSE
197 l_invoice_status := 'Y';
198 END IF;
199 RETURN l_invoice_status;
200 END IF;
201 END ap_invoice_status;
202
203 ---------------------------------------------------------------------------
204 --This Procedure refresh_pa_cache() is used by get_raw_cdl_date and get_raw_cdl_recvr_pa_date
205 --for caching purposes. Global variables defined in PAXPINTS.pls are used for caching.
206 ---------------------------------------------------------------------------
207
208 PROCEDURE refresh_pa_cache ( p_org_id IN NUMBER ,
209 p_expenditure_item_date IN DATE ,
210 p_accounting_date IN DATE,
211 p_caller_flag IN VARCHAR2
212 )
213 IS
214 -- local variables
215 l_earliest_start_date DATE ;
216 l_earliest_end_date DATE ;
217 l_earliest_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE;
218 l_pa_date DATE ;
219 l_start_date DATE ; -- start date for the l_pa_date.
220 l_end_date DATE ; -- end date for the l_pa_date ( equals l_pa_date ).
221 l_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE;
222
223 l_prof_new_gldate_derivation VARCHAR2(1) := 'N' ;
224
225 BEGIN
226 /* Changed from value_specific to value for bug 5472333 */
227 l_prof_new_gldate_derivation := NVL(fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION'), 'N') ;
228
229 IF( l_prof_new_gldate_derivation = 'Y' )
230 THEN
231 /*
232 *SQL to select the earliest open PA_DATE.
233 *Select the earliest open date ONLY if the global earliest date is NOT yet populated.
234 *Because , earliest pa_date will remain the same for a run.
235 */
236
237 IF ( p_caller_flag = 'R' AND g_r_earliest_pa_start_date IS NULL ) OR
238 ( p_caller_flag = 'P' AND g_p_earliest_pa_start_date IS NULL ) THEN
239
240 -- Note : This SQL uses the p_accounting_date filter criteria.
241
242 SELECT pap1.start_date
243 ,pap1.end_date
244 ,pap1.period_name
245 INTO l_earliest_start_date
246 ,l_earliest_end_date
247 ,l_earliest_period_name
248 FROM pa_periods_all pap1
249 WHERE pap1.status IN ('O','F')
250 AND pap1.org_id = p_org_id
251 AND pap1.start_date = ( SELECT MIN(pap.start_date)
252 FROM pa_periods_all pap
253 WHERE status IN ('O','F')
254 AND org_id = p_org_id
255 );
256 END IF ;
257
258 -- SQL to select the PA_DATE for the current EI.
259 /* Code fix for Bug 1657231...
260 Added Begin... Exception...END to Handle No_Data_Found Exception */
261
262 /*
263 * EPP.
264 * Modified the following sql to get p_accounting_date as l_pa_date
265 * rather then end_date.
266 */
267 BEGIN /* Added for Bug 1657231 */
268 SELECT pap.start_date
269 ,pap.end_date
270 ,p_accounting_date
271 ,pap.period_name
272 INTO l_start_date
273 ,l_end_date
274 ,l_pa_date
275 ,l_period_name
276 FROM pa_periods_all pap
277 WHERE pap.status in ('O','F')
278 AND pap.end_date >= TRUNC(p_expenditure_item_date)
279 AND p_accounting_date BETWEEN pap.start_date and pap.end_date
280 AND org_id = p_org_id ;
281
282 EXCEPTION
283 WHEN NO_DATA_FOUND THEN
284 l_pa_date := NULL;
285 l_period_name := NULL;
286 END; /* Added for Bug 1657231 */
287
288 /*If the l_pa_date obtained is NULL, try to find a pa_date without the accounting-date
289 *check. This approach was used even previously.
290 *This SQL will FAIL - if there are more than one row in pa_periods_all - with the same end_date.
291 */
292
293 IF ( l_pa_date IS NULL )
294 THEN
295 SELECT pap1.start_date
296 ,pap1.end_date
297 ,pap1.start_date
298 ,pap1.period_name
299 INTO l_start_date
300 ,l_end_date
301 ,l_pa_date
302 ,l_period_name
303 FROM pa_periods_all pap1
304 WHERE pap1.org_id = p_org_id /*Added While fixing bug 1657231*/
305 AND pap1.start_date = ( SELECT MIN(pap.start_date)
306 FROM pa_periods_all pap
307 WHERE status IN ('O','F')
308 AND pap.start_date >= TRUNC(p_expenditure_item_date)
309 AND org_id = p_org_id
310 );
311 END IF; -- l_pa_date IS NULL
312
313 ELSE -- profile option is not set.
314 /*
315 *SQL to select the earliest open PA_DATE.
316 *Select the earliest open date ONLY if the global earliest date is NOT yet populated.
317 *Because , earliest pa_date will remain the same for a run.
318 */
319
320 IF ( p_caller_flag = 'R' AND g_r_earliest_pa_start_date IS NULL ) OR
321 ( p_caller_flag = 'P' AND g_p_earliest_pa_start_date IS NULL ) THEN
322
323 -- Note : This SQL uses the p_accounting_date filter criteria.
324
325 SELECT pap1.start_date
326 ,pap1.end_date
327 ,pap1.period_name
328 INTO l_earliest_start_date
329 ,l_earliest_end_date
330 ,l_earliest_period_name
331 FROM pa_periods_all pap1
332 WHERE pap1.status IN ('O', 'F')
333 AND pap1.org_id = p_org_id
334 AND pap1.end_date = ( SELECT MIN(pap.end_date)
335 FROM pa_periods_all pap
336 WHERE pap.status IN ('O','F')
337 -- AND p_accounting_date BETWEEN pap.start_date AND pap.end_date /* commented for bug 1982225 */
338 AND pap.org_id = p_org_id
339 );
340 END IF ;
341
342 -- SQL to select the PA_DATE for the current EI.
343 /* Code fix for Bug 1657231...
344 Added Begin... Exception...END to Handle No_Data_Found Exception */
345
346 BEGIN /* Added for Bug 1657231 */
347 SELECT pap.start_date
348 ,pap.end_date
349 ,pap.end_date
350 ,pap.period_name
351 INTO l_start_date
352 ,l_end_date
353 ,l_pa_date
354 ,l_period_name
355 FROM pa_periods_all pap
356 WHERE status in ('O','F')
357 AND pap.end_date >= TRUNC(p_expenditure_item_date)
358 AND p_accounting_date BETWEEN pap.start_date and pap.end_date
359 AND org_id = p_org_id ;
360
361 EXCEPTION
362 WHEN NO_DATA_FOUND THEN
363 l_pa_date := NULL;
364 l_period_name := NULL;
365 END; /* Added for Bug 1657231 */
366
367 /*If the l_pa_date obtained is NULL, try to find a pa_date without the accounting-date
368 *check. This approach was used even previously.
369 *This SQL will FAIL - if there are more than one row in pa_periods_all - with the same end_date.
370 */
371
372 IF ( l_pa_date IS NULL )
373 THEN
374 SELECT pap1.start_date
375 ,pap1.end_date
376 ,pap1.end_date
377 ,pap1.period_name
378 INTO l_start_date
379 ,l_end_date
380 ,l_pa_date
381 ,l_period_name
382 FROM pa_periods_all pap1
383 WHERE pap1.end_date = ( SELECT MIN(pap.end_date)
384 FROM pa_periods_all pap
385 WHERE pap.status IN ('O','F')
386 AND pap.end_date >= TRUNC(p_expenditure_item_date)
387 AND pap.org_id = p_org_id
388 )
389 AND pap1.org_id = p_org_id; /* Added While fixing bug 1657231
390 Although not related to the bug */
391 END IF;
392 END IF; -- profile check
393
394 /*
395 * Populate global variables.
396 */
397 IF ( p_caller_flag = 'R' ) THEN
398 -- Populate receiver cache.
399 g_r_earliest_pa_start_date := l_earliest_start_date ;
400 g_r_earliest_pa_end_date := l_earliest_end_date ;
401 g_r_earliest_pa_period_name := l_earliest_period_name ;
402 g_recvr_org_id := p_org_id ;
403 g_recvr_pa_start_date := l_start_date ;
404 g_recvr_pa_end_date := l_end_date ;
405 g_recvr_pa_date := l_pa_date ;
406 g_recvr_pa_period_name := l_period_name ;
407 ELSIF ( p_caller_flag = 'P' ) THEN
408 -- Populate provider cache
409 g_p_earliest_pa_start_date := l_earliest_start_date ;
410 g_p_earliest_pa_end_date := l_earliest_end_date ;
411 g_p_earliest_pa_period_name := l_earliest_period_name ;
412 g_prvdr_org_id := p_org_id ;
413 g_prvdr_pa_start_date := l_start_date ;
414 g_prvdr_pa_end_date := l_end_date ;
415 g_prvdr_pa_date := l_pa_date ;
416 g_prvdr_pa_period_name := l_period_name ;
417 END IF; -- caller flag
418
419 EXCEPTION
420 WHEN NO_DATA_FOUND THEN
421 /*
422 * Earliest dates are NULLed to ensure that the cache gets
423 * refreshed the next time.
424 */
425 IF ( p_caller_flag = 'R' ) THEN
426 -- Populate receiver cache.
427 g_r_earliest_pa_start_date := NULL ;
428 g_r_earliest_pa_end_date := NULL ;
429 g_r_earliest_pa_period_name := NULL ;
430 g_recvr_pa_start_date := NULL ;
431 g_recvr_pa_end_date := NULL ;
432 g_recvr_pa_date := NULL ;
433 g_recvr_pa_period_name := NULL ;
434 ELSIF ( p_caller_flag = 'P' ) THEN
435 -- Populate provider cache
436 g_p_earliest_pa_start_date := NULL ;
437 g_p_earliest_pa_end_date := NULL ;
438 g_p_earliest_pa_period_name := NULL ;
439 g_prvdr_pa_start_date := NULL ;
440 g_prvdr_pa_end_date := NULL ;
441 g_prvdr_pa_date := NULL ;
442 g_recvr_pa_period_name := NULL ;
443 END IF; -- caller flag
444 WHEN OTHERS THEN
445 RAISE ;
446
447 END refresh_pa_cache ;
448
449 -------------------------------------------------------------------------------
450 -- Function - get_raw_cdl_pa_date
451 -- Comments are at Package specification level.
452 -- This function is created for Bug No : 1103257. Function will be called from
453 -- PAVVIT process ( Suppllier invoice interface from payables module ). This
454 -- function will ensure that PA_DATE populated for Raw CDLs will be always
455 -- Greater than Payables Accounting date for Raw CDLs.
456 --------------------------------------------------------------------------
457 --This function was modified to use caching. The actual DB access happens in
458 -- pa_integration.refresh_pa_cache().
459 --This is to get the pa_date for the provider part. The receiver part is done
460 -- by get_raw_cdl_recvr_pa_date().
461 --------------------------------------------------------------------------
462 FUNCTION get_raw_cdl_pa_date ( p_expenditure_item_date IN DATE,
463 p_accounting_date IN DATE,
464 p_org_id IN NUMBER
465 )
466 RETURN DATE
467 IS
468 l_prof_new_gldate_derivation VARCHAR2(1);
469 BEGIN
470 /* Changed from value_specific to value for bug 5472333 */
471 l_prof_new_gldate_derivation := NVL(fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION'), 'N') ;
472
473
474 IF ( g_p_earliest_pa_start_date IS NOT NULL
475 and p_org_id = g_prvdr_org_id ) /* 1982225. cache should be referred only if orgs are same */
476
477 THEN
478 -- values are already available in the provider_cache.
479 -- so, check the provider_cache and return pa_date accordingly.
480
481 IF ( l_prof_new_gldate_derivation = 'Y')
482 THEN
483 IF ( p_accounting_date BETWEEN g_prvdr_pa_start_date AND g_prvdr_pa_end_date AND
484 p_expenditure_item_date <= g_prvdr_pa_start_date )
485 THEN
486 return ( p_accounting_date ) ;
487 ELSIF ( p_accounting_date <= g_p_earliest_pa_start_date AND
488 p_expenditure_item_date <= g_p_earliest_pa_start_date )
489 THEN
490 g_prvdr_pa_start_date := g_p_earliest_pa_start_date;
491 g_prvdr_pa_end_date := g_p_earliest_pa_end_date;
492 g_prvdr_pa_period_name := g_p_earliest_pa_period_name;
493 return ( g_prvdr_pa_start_date ) ;
494 END IF ; -- p_accounting_date
495 ELSE
496 IF ( p_accounting_date BETWEEN g_prvdr_pa_start_date AND g_prvdr_pa_end_date AND
497 p_expenditure_item_date <= g_prvdr_pa_end_date )
498 THEN
499 return ( g_prvdr_pa_end_date );
500 ELSIF ( p_accounting_date <= g_p_earliest_pa_end_date AND
501 p_expenditure_item_date <= g_p_earliest_pa_end_date )
502 THEN
503 g_prvdr_pa_start_date := g_p_earliest_pa_start_date;
504 g_prvdr_pa_end_date := g_p_earliest_pa_end_date;
505 g_prvdr_pa_period_name := g_p_earliest_pa_period_name;
506 return ( g_prvdr_pa_end_date ) ;
507 END IF; -- p_accounting_date
508 END IF ; -- profile
509 END IF ; -- g_p_earliest_pa_start_date
510
511 /* If control comes here, it means that EITHER the cache is empty OR
512 * the provider Cache is NOT reusable.
513 * Access the DB and refresh cache and return pa_date.
514 */
515
516 pa_integration.refresh_pa_cache( p_org_id , p_expenditure_item_date, p_accounting_date, 'P' );
517 RETURN ( g_prvdr_pa_date ) ;
518 EXCEPTION
519 WHEN OTHERS THEN
520 RAISE ;
521
522 END get_raw_cdl_pa_date;
523 -------------------------------------------------------------------------------------------------------
524 --This is to get the pa_date for the receiver part. The provider part is done
525 -- by get_raw_cdl_pa_date().
526 --------------------------------------------------------------------------
527
528 /**This is to get the pa_date for the receiver part **/
529 FUNCTION get_raw_cdl_recvr_pa_date ( p_expenditure_item_date IN DATE,
530 p_accounting_date IN DATE ,
531 p_org_id IN NUMBER
532 )
533 RETURN DATE
534 IS
535 l_prof_new_gldate_derivation VARCHAR2(1);
536 BEGIN
537 /* Changed from value_specific to value for bug 5472333 */
538 l_prof_new_gldate_derivation := NVL(fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION'), 'N') ;
539
540 IF ( g_r_earliest_pa_start_date IS NOT NULL
541 and p_org_id = g_recvr_org_id ) /* 1982225. cache should be referred only if orgs are same */
542 THEN
543 -- receiver cache IS available.
544 -- Hence, try to re-use the receiver cache.
545
546 IF ( l_prof_new_gldate_derivation = 'Y' )
547 THEN
548 IF ( p_accounting_date BETWEEN g_recvr_pa_start_date AND g_recvr_pa_end_date AND
549 p_expenditure_item_date <= g_recvr_pa_start_date )
550 THEN
551 return ( p_accounting_date ) ;
552 ELSIF ( p_accounting_date <= g_r_earliest_pa_start_date AND
553 p_expenditure_item_date <= g_r_earliest_pa_start_date )
554 THEN
555 g_recvr_pa_start_date := g_p_earliest_pa_start_date;
556 g_recvr_pa_end_date := g_p_earliest_pa_end_date;
557 g_recvr_pa_period_name := g_p_earliest_pa_period_name;
558 return ( g_recvr_pa_start_date ) ;
559 END IF ;
560 ELSE
561 IF ( p_accounting_date BETWEEN g_recvr_pa_start_date AND g_recvr_pa_end_date AND
562 p_expenditure_item_date <= g_recvr_pa_end_date )
563 THEN
564 return ( p_accounting_date ) ;
565 ELSIF ( p_accounting_date <= g_r_earliest_pa_end_date AND
566 p_expenditure_item_date <= g_r_earliest_pa_end_date )
567 THEN
568 g_recvr_pa_start_date := g_p_earliest_pa_start_date;
569 g_recvr_pa_end_date := g_p_earliest_pa_end_date;
570 g_recvr_pa_period_name := g_p_earliest_pa_period_name;
571 return ( g_recvr_pa_end_date ) ;
572 END IF ;
573 END IF; -- profile
574
575 -- receiver cache is EMPTY.
576 -- Try to use the provider cache.
577
578 ELSIF ( g_p_earliest_pa_start_date IS NOT NULL /* 1982225 . we should check if prvdr cache is available or not. */
579 and g_prvdr_org_id = p_org_id )
580 THEN
581 IF ( l_prof_new_gldate_derivation = 'Y' )
582 THEN
583 IF ( p_accounting_date BETWEEN g_prvdr_pa_start_date AND g_prvdr_pa_end_date AND
584 p_expenditure_item_date <= g_prvdr_pa_start_date )
585 THEN
586 -- copy provider cache to receiver cache.
587 g_recvr_org_id := g_prvdr_org_id ;
588 g_r_earliest_pa_start_date := g_p_earliest_pa_start_date ;
589 g_r_earliest_pa_end_date := g_p_earliest_pa_end_date ;
590 g_r_earliest_pa_period_name := g_p_earliest_pa_period_name ;
591 g_recvr_pa_start_date := g_prvdr_pa_start_date ;
592 g_recvr_pa_end_date := g_prvdr_pa_end_date ;
593 g_recvr_pa_period_name := g_prvdr_pa_period_name ;
594 g_recvr_pa_date := g_prvdr_pa_date ;
595 return ( p_accounting_date ) ;
596 ELSIF ( p_accounting_date <= g_p_earliest_pa_start_date AND
597 p_expenditure_item_date <= g_p_earliest_pa_start_date )
598 THEN
599 -- copy provider cache to receiver cache.
600 g_recvr_org_id := g_prvdr_org_id ;
601 g_r_earliest_pa_start_date := g_p_earliest_pa_start_date ;
602 g_r_earliest_pa_end_date := g_p_earliest_pa_end_date ;
603 g_r_earliest_pa_period_name := g_p_earliest_pa_period_name ;
604 g_recvr_pa_start_date := g_p_earliest_pa_start_date ;
605 g_recvr_pa_end_date := g_p_earliest_pa_end_date ;
606 g_recvr_pa_period_name := g_p_earliest_pa_period_name ;
607 g_recvr_pa_date := g_prvdr_pa_date ;
608 return ( g_recvr_pa_start_date ) ;
609 END IF; --p_accounting_date
610 ELSE -- profile not set
611 IF ( p_accounting_date BETWEEN g_prvdr_pa_start_date AND g_prvdr_pa_end_date AND
612 p_expenditure_item_date <= g_prvdr_pa_end_date )
613 THEN
614 -- copy provider cache to receiver cache.
615 g_recvr_org_id := g_prvdr_org_id ;
616 g_r_earliest_pa_start_date := g_p_earliest_pa_start_date ;
617 g_r_earliest_pa_end_date := g_p_earliest_pa_end_date ;
618 g_r_earliest_pa_period_name := g_p_earliest_pa_period_name ;
619 g_recvr_pa_start_date := g_prvdr_pa_start_date ;
620 g_recvr_pa_end_date := g_prvdr_pa_end_date ;
621 g_recvr_pa_period_name := g_prvdr_pa_period_name ;
622 g_recvr_pa_date := g_prvdr_pa_date ;
623 return ( g_recvr_pa_end_date ) ;
624 ELSIF ( p_accounting_date <= g_p_earliest_pa_end_date AND
625 p_expenditure_item_date <= g_p_earliest_pa_end_date )
626 THEN
627 -- copy provider cache to receiver cache.
628 g_recvr_org_id := g_prvdr_org_id ;
629 g_r_earliest_pa_start_date := g_p_earliest_pa_start_date ;
630 g_r_earliest_pa_end_date := g_p_earliest_pa_end_date ;
631 g_r_earliest_pa_period_name := g_p_earliest_pa_period_name ;
632 g_recvr_pa_start_date := g_p_earliest_pa_start_date ;
633 g_recvr_pa_end_date := g_p_earliest_pa_end_date ;
634 g_recvr_pa_period_name := g_p_earliest_pa_period_name ;
635 g_recvr_pa_date := g_prvdr_pa_date ;
636 return ( g_p_earliest_pa_end_date ) ;
637 END IF; --p_accounting_date
638 END IF ; -- profile
639 END IF ; -- recvr cache check
640 /*
641 *If control comes here,
642 *EITHER receiver cache is EMPTY or ( Both provider AND receiver caches are NOT reusable )
643 *hence hit the DB and populate/refresh receiver cache.
644 *then return g_recvr_pa_date.
645 */
646
647 pa_integration.refresh_pa_cache ( p_org_id , p_expenditure_item_date , p_accounting_date, 'R' );
648 RETURN ( g_recvr_pa_date ) ;
649 EXCEPTION
650 WHEN OTHERS THEN
651 RAISE ;
652 END get_raw_cdl_recvr_pa_date ;
653 -------------------------------------------------------------------------------------------------------
654
655 -- FUnction get_burden_cdl_pa_date
656 -- This function is created for Bug no : 1103257. FUnction will be called by
657 -- PACODTBC process (Distribute total burden cost). FUnction will ruturn the
658 -- Date to be populated as PA_DATE for Burden CDLs. FUnction will be called
659 -- only when the C and D types of the rows will be created for Supplier
660 -- Invoices.
661 ---------------------------------------------------------------------------
662 /*
663 * EPP.
664 * This function is NOT used anymore. Instead pa_utils2.get_pa_date is used
665 * since the functionality is same in both the procedures. Only the parameter
666 * is different.
667 */
668 FUNCTION get_burden_cdl_pa_date ( p_raw_cdl_date IN DATE )
669 RETURN DATE
670 IS
671 l_pa_period_end_date DATE;
672 BEGIN
673 SELECT MIN(pap.end_date)
674 INTO l_pa_period_end_date
675 FROM pa_periods pap
676 WHERE pap.status in ( 'O', 'F')
677 AND pap.end_date >= p_raw_cdl_date;
678
679 RETURN l_pa_period_end_date;
680 END get_burden_cdl_pa_date;
681 ---------------------------------------------------------------------------
682 --End FUnction get_burden_cdl_pa_date
683 ---------------------------------------------------------------------------
684 /*
685 * EPP.
686 * This function can be called for both Provider and Receiver gl dates
687 * by passing the appropriate parameters.
688 */
689 FUNCTION get_gl_period_name ( p_gl_date IN pa_cost_distribution_lines_all.gl_date%TYPE
690 ,p_set_of_books_id IN pa_implementations_all.set_of_books_id%TYPE
691 )
692 RETURN pa_cost_distribution_lines_all.gl_period_name%TYPE
693 IS
694 l_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE;
695 l_gl_start_date DATE;
696 l_gl_end_date DATE;
697 BEGIN
698
699 If (trunc(p_gl_date) between trunc(G_PrevPdStDate) and trunc(G_PrevPdEdDate)) AND
700 G_PrevSOBId = p_set_of_books_id Then
701
702 l_gl_period_name := G_PrevPeriodName;
703
704 Else
705 SELECT PERIOD.period_name, PERIOD.start_date, PERIOD.end_date
706 INTO l_gl_period_name, l_gl_start_date, l_gl_end_date
707 FROM GL_PERIOD_STATUSES PERIOD
708 WHERE PERIOD.set_of_books_id = p_set_of_books_id
709 AND PERIOD.application_id = Pa_Period_Process_Pkg.Application_Id
710 AND PERIOD.adjustment_period_flag = 'N'
711 AND p_gl_date BETWEEN PERIOD.start_date AND PERIOD.end_date
712 ;
713
714 G_PrevPeriodName := l_gl_period_name;
715 G_PrevPdStDate := l_gl_start_date;
716 G_PrevPdEdDate := l_gl_end_date;
717 G_PrevSOBId := p_set_of_books_id;
718
719 End If;
720
721 RETURN l_gl_period_name;
722 EXCEPTION
723 WHEN NO_DATA_FOUND
724 THEN
725 l_gl_period_name := NULL;
726 RETURN l_gl_period_name;
727 END get_gl_period_name;
728 ---------------------------------------------------------------------------
729
730 /*
731 * The period information calculation is same for all transactions coming
732 * into PA thro transaction import. The following procedure does not distinguish
733 * between system linkages.
734 */
735 PROCEDURE get_period_information ( p_expenditure_item_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
736 ,p_prvdr_gl_date IN pa_cost_distribution_lines_all.gl_date%TYPE
737 ,p_line_type IN pa_cost_distribution_lines_all.line_type%TYPE
738 ,p_prvdr_org_id IN pa_expenditure_items_all.org_id%TYPE
739 ,p_recvr_org_id IN pa_expenditure_items_all.org_id%TYPE
740 ,p_prvdr_sob_id IN pa_implementations_all.set_of_books_id%TYPE
741 ,p_recvr_sob_id IN pa_implementations_all.set_of_books_id%TYPE
742 ,x_prvdr_pa_date OUT NOCOPY pa_cost_distribution_lines_all.pa_date%TYPE
743 ,x_prvdr_pa_period_name OUT NOCOPY pa_cost_distribution_lines_all.pa_period_name%TYPE
744 ,x_prvdr_gl_period_name OUT NOCOPY pa_cost_distribution_lines_all.gl_period_name%TYPE
745 ,x_recvr_pa_date OUT NOCOPY pa_cost_distribution_lines_all.recvr_pa_date%TYPE
746 ,x_recvr_pa_period_name OUT NOCOPY pa_cost_distribution_lines_all.recvr_pa_period_name%TYPE
747 ,x_recvr_gl_date OUT NOCOPY pa_cost_distribution_lines_all.recvr_gl_date%TYPE
748 ,x_recvr_gl_period_name OUT NOCOPY pa_cost_distribution_lines_all.recvr_gl_period_name%TYPE
749 ,x_return_status OUT NOCOPY NUMBER
750 ,x_error_code OUT NOCOPY VARCHAR2
751 ,x_error_stage OUT NOCOPY NUMBER
752 )
753 IS
754 l_prvdr_pa_date pa_cost_distribution_lines_all.pa_date%TYPE;
755 l_prvdr_pa_period_name pa_periods.period_name%TYPE;
756 l_prvdr_gl_period_name gl_periods.period_name%TYPE;
757
758 l_recvr_pa_date pa_cost_distribution_lines_all.pa_date%TYPE;
759 l_recvr_pa_period_name pa_periods.period_name%TYPE;
760 l_recvr_gl_date pa_cost_distribution_lines_all.gl_date%TYPE;
761 l_recvr_gl_period_name gl_periods.period_name%TYPE;
762
763 l_pa_gl_app_id NUMBER := 8721;
764 l_gl_app_id NUMBER := 101;
765
766 /*
767 * Processing related variables.
768 */
769 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
770 l_error_code VARCHAR2(30);
771 l_error_stage VARCHAR2(30);
772 l_debug_mode VARCHAR2(1);
773 l_stage NUMBER ;
774
775 l_prof_new_gldate_derivation VARCHAR2(1) := 'N';
776 l_use_same_pa_gl_period_prvdr VARCHAR2(1) := 'N';
777 l_use_same_pa_gl_period_recvr VARCHAR2(1) := 'N';
778 BEGIN
779 pa_debug.init_err_stack('pa_integration.get_period_information');
780
781 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
782 l_debug_mode := NVL(l_debug_mode, 'N');
783
784 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
785
786 l_stage := 100;
787 IF l_debug_mode = 'Y' THEN
788 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':From get_period_information';
789 pa_debug.write_file(pa_debug.g_err_stage);
790 END IF;
791
792 /*
793 * Populating setup related variables.
794 */
795 /* Changed from value_specific to value for bug 5472333 */
796 l_prof_new_gldate_derivation := NVL(fnd_profile.value('PA_EN_NEW_GLDATE_DERIVATION'), 'N') ;
797 l_use_same_pa_gl_period_prvdr := NVL(PA_PERIOD_PROCESS_PKG.Use_Same_PA_GL_Period(p_prvdr_org_id), 'N');
798 l_use_same_pa_gl_period_recvr := NVL(PA_PERIOD_PROCESS_PKG.Use_Same_PA_GL_Period(p_recvr_org_id), 'N');
799
800 IF ( l_prof_new_gldate_derivation = 'Y' )
801 THEN
802 l_stage := 200;
803 /*
804 * Get Gl periods based on ei date.
805 */
806 l_prvdr_gl_period_name := pa_integration.get_gl_period_name( p_gl_date => p_prvdr_gl_date
807 ,p_set_of_books_id => p_prvdr_sob_id
808 );
809
810 -- Bug 2248543 Added provider and receiver org_id check
811 if ( p_prvdr_org_id <> p_recvr_org_id ) then
812 l_recvr_gl_date := pa_utils2.get_recvr_gl_date( p_reference_date => p_expenditure_item_date
813 ,p_application_id => l_pa_gl_app_id
814 ,p_set_of_books_id => p_recvr_sob_id
815 );
816 l_recvr_gl_period_name := pa_utils2.g_recvr_gl_period_name;
817 else
818 l_recvr_gl_date := p_prvdr_gl_date;
819 l_recvr_gl_period_name := l_prvdr_gl_period_name;
820 end if;
821
822 /*
823 * Deriving PA period information for Provider.
824 */
825 IF ( l_use_same_pa_gl_period_prvdr = 'Y' )
826 THEN
827 l_stage := 300;
828 /*
829 * Copy Gl period information to Pa periods.
830 */
831 l_prvdr_pa_date := p_prvdr_gl_date;
832 l_prvdr_pa_period_name := l_prvdr_gl_period_name;
836 * Get Pa periods based on ei date.
833 ELSE -- implementation option is not set
834 l_stage := 400;
835 /*
837 */
838
839 l_prvdr_pa_date := pa_utils2.get_pa_date
840 ( p_ei_date => p_expenditure_item_date
841 ,p_gl_date => SYSDATE
842 ,p_org_id => p_prvdr_org_id
843 );
844 l_prvdr_pa_period_name := pa_utils2.g_prvdr_pa_period_name;
845
846 END IF; -- implementations option
847 /*
848 * Deriving PA period information for Receiver.
849 */
850 IF ( l_use_same_pa_gl_period_recvr = 'Y' )
851 THEN
852 l_stage := 425;
853 /*
854 * Copy Gl period information to Pa periods.
855 */
856 l_recvr_pa_date := l_recvr_gl_date;
857 l_recvr_pa_period_name := l_recvr_gl_period_name;
858 ELSE -- implementation option is not set
859 l_stage := 450;
860 /*
861 * Get Pa periods based on ei date.
862 */
863
864 -- Bug 2248543 Added provider and receiver org_id check
865 if ( p_prvdr_org_id <> p_recvr_org_id ) then
866 l_recvr_pa_date := pa_utils2.get_recvr_pa_date
867 ( p_ei_date => p_expenditure_item_date
868 ,p_gl_date => SYSDATE
869 ,p_org_id => p_recvr_org_id
870 );
871 l_recvr_pa_period_name := pa_utils2.g_recvr_pa_period_name;
872 else
873 l_recvr_pa_date := l_prvdr_pa_date;
874 l_recvr_pa_period_name := l_prvdr_pa_period_name;
875 end if;
876
877 END IF; -- implementations option
878 ELSE -- profile option is not set.
879 l_stage := 500;
880 /*
881 * Get Pa periods based on ei date.
882 */
883 l_prvdr_pa_date := pa_integration.get_raw_cdl_pa_date
884 ( p_expenditure_item_date => p_expenditure_item_date
885 ,p_accounting_date => p_prvdr_gl_date
886 ,p_org_id => p_prvdr_org_id
887 );
888 l_prvdr_pa_period_name := g_prvdr_pa_period_name;
889
890 /*
891 * recvr_gl_date is not available from txn import.
892 * should find a way out.
893 */
894 -- Bug 2248543 Added provider and receiver org_id check
895 if ( p_prvdr_org_id <> p_recvr_org_id ) then
896 l_recvr_pa_date := pa_utils2.get_recvr_pa_date
897 ( p_ei_date => p_expenditure_item_date
898 ,p_gl_date => SYSDATE
899 ,p_org_id => p_recvr_org_id
900 );
901 l_recvr_pa_period_name := pa_utils2.g_recvr_pa_period_name;
902 else
903 l_recvr_pa_date := l_prvdr_pa_date;
904 l_recvr_pa_period_name := l_prvdr_pa_period_name;
905 end if;
906
907 /*
908 * Get Gl periods based on above derived Pa date.
909 */
910 l_prvdr_gl_period_name := get_gl_period_name( p_gl_date => p_prvdr_gl_date
911 ,p_set_of_books_id => p_prvdr_sob_id
912 );
913
914 -- Bug 2248543 Added provider and receiver org_id check
915 if ( p_prvdr_org_id <> p_recvr_org_id) then
916 l_recvr_gl_date := pa_utils2.get_recvr_gl_date( p_reference_date => l_recvr_pa_date
917 ,p_application_id => l_gl_app_id
918 ,p_set_of_books_id => p_recvr_sob_id
919 );
920 l_recvr_gl_period_name := pa_utils2.g_recvr_gl_period_name;
921 else
922 l_recvr_gl_date := p_prvdr_gl_date;
923 l_recvr_gl_period_name := l_prvdr_gl_period_name;
924 end if;
925
926 END IF; -- profile option
927 l_stage := 600;
928
929 /*
930 * Populate the out variables.
931 */
932 x_prvdr_pa_date := l_prvdr_pa_date;
933 x_prvdr_pa_period_name := l_prvdr_pa_period_name;
934 x_prvdr_gl_period_name := l_prvdr_gl_period_name;
935
936 x_recvr_pa_date := l_recvr_pa_date;
937 x_recvr_pa_period_name := l_recvr_pa_period_name;
938 x_recvr_gl_date := l_recvr_gl_date;
939 x_recvr_gl_period_name := l_recvr_gl_period_name;
940
941 x_return_status := 0;
942
943 -- reset the error stack
944 PA_DEBUG.reset_err_stack;
945
946 EXCEPTION
947 WHEN NO_DATA_FOUND THEN
948 x_prvdr_pa_date := NULL;
949 x_prvdr_pa_period_name := NULL;
950 x_prvdr_gl_period_name := NULL;
951
952 x_recvr_pa_date := NULL;
953 x_recvr_pa_period_name := NULL;
954 x_recvr_gl_date := NULL;
955 x_recvr_gl_period_name := NULL;
956 WHEN OTHERS THEN
957 RAISE ;
958 END; -- get_period_information
959
960
961 END pa_integration;
[Home] [Help]