[Home] [Help]
PACKAGE BODY: APPS.AP_FUNDS_CONTROL_PKG
Source
1 PACKAGE BODY AP_FUNDS_CONTROL_PKG AS
2 /* $Header: aprfundb.pls 120.58 2011/10/21 12:00:21 rseeta ship $ */
3
4 -- Logging Infra
5 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
11 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
12 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
13 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'AP.PLSQL.AP_FUNDS_CONTROL_PKG.';
14 G_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
15 --1-STATEMENT, 2-PROCEDURE, 3-EVENT, 4-EXCEPTION, 5-ERROR, 6-UNEXPECTED
16
17 g_log_level NUMBER;
18 g_log_enabled BOOLEAN;
19
20 /*=============================================================================
21 |Private (Non Public) Procedure Specifications
22 *===========================================================================*/
23 --Bug 5487757. Added org_id as parameter
24 FUNCTION Encumbrance_Enabled(p_org_id IN NUMBER) RETURN BOOLEAN;
25
26 PROCEDURE Setup_Gl_FundsCtrl_Params(
27 p_bc_mode IN OUT NOCOPY VARCHAR2,
28 p_called_by IN VARCHAR2,
29 p_calling_sequence IN VARCHAR2);
30
31 PROCEDURE FundsReserve_Init(
32 p_invoice_id IN NUMBER,
33 p_system_user IN NUMBER,
34 p_override_mode IN OUT NOCOPY VARCHAR2,
35 p_fundschk_user_id IN OUT NOCOPY NUMBER,
36 p_fundschk_resp_id IN OUT NOCOPY NUMBER,
37 p_calling_sequence IN VARCHAR2);
38
39
40 PROCEDURE FundsCheck_Init(
41 p_invoice_id IN NUMBER,
42 p_set_of_books_id IN OUT NOCOPY NUMBER,
43 p_xrate_gain_ccid IN OUT NOCOPY NUMBER,
44 p_xrate_loss_ccid IN OUT NOCOPY NUMBER,
45 p_base_currency_code IN OUT NOCOPY VARCHAR2,
46 p_inv_enc_type_id IN OUT NOCOPY NUMBER,
47 p_gl_user_id IN OUT NOCOPY NUMBER,
48 p_calling_sequence IN VARCHAR2);
49
50 PROCEDURE Get_GL_FundsChk_Result_Code(
51 p_fc_result_code IN OUT NOCOPY VARCHAR2);
52
53
54 /*=============================================================================
55 | Procedure Definitions
56 *===========================================================================*/
57
58 /*============================================================================
59 | PRIVATE PROCEDURE ENCUMBRANCE_ENABLED
60 |
61 | DESCRIPTION
62 | It is a function that returns boolean. True if encumbrance is
63 | enabled, false otherwise
64 |
65 | PARAMETERS
66 |
67 | NOTE
68 |
69 | MODIFICATION HISTORY
70 | Date Author Description of Change
71 |
72 *==========================================================================*/
73
74
75
76 FUNCTION Encumbrance_Enabled (p_org_id IN NUMBER)
77 RETURN BOOLEAN IS
78
79 l_enc_enabled VARCHAR2(1);
80
81 -- Logging Infra:
82 l_procedure_name CONSTANT VARCHAR2(30) := 'Encumbrance_Enabled';
83 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
84
85 BEGIN
86
87 -- Logging Infra: Procedure level
88 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
89 l_log_msg := 'Begin of procedure '|| l_procedure_name;
90 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
91 l_procedure_name||'.begin', l_log_msg);
92 END IF;
93
94 BEGIN
95 SELECT nvl(purch_encumbrance_flag,'N')
96 INTO l_enc_enabled
97 FROM FINANCIALS_SYSTEM_PARAMS_ALL
98 WHERE org_id = p_org_id; -- Bug 5487757
99 EXCEPTION
100 WHEN NO_DATA_FOUND THEN
101 RETURN(FALSE);
102 END;
103
104 IF (l_enc_enabled = 'N') THEN
105 RETURN(FALSE);
106 ELSE
107 RETURN(TRUE);
108 END IF;
109
110 -- Logging Infra: Procedure level
111 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
112 l_log_msg := 'End of procedure '|| l_procedure_name;
113 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
114 l_procedure_name, l_log_msg);
115 END IF;
116
117 EXCEPTION
118 WHEN OTHERS THEN
119 IF (SQLCODE <> -20001) THEN
120 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
121 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
122 END IF;
123 APP_EXCEPTION.RAISE_EXCEPTION;
124 END Encumbrance_Enabled;
125
126 /*=============================================================================
127 | Procedure Definitions
128 *===========================================================================*/
129 --procedure added for bug 8733916
130 /*==============================================================================
131 | PROCEDURE ENCUM_UNPROCESSED_EVENTS_DEL
132 |
133 | DESCRIPTION
134 | It is a procedure that checks all the unprocessed bc events for
135 | for an invoice and deletes the events from xla.
136 | Also we null out the bc event values in all tables corresponding to the
137 | invoice
138 | PARAMETERS
139 | p_invoice_id - Invoice_id
140 | p_line_number- Invoice line number which we are discarding
141 | p_calling_mode - to check if it called during CANCELING or DISCARDING
142 | p_calling_sequence -Debugging string to indicate path of module
143 |
144 | NOTE
145 |
146 | MODIFICATION HISTORY
147 | Date Author Description of Change
148 |
149 *==========================================================================*/
150
151 PROCEDURE Encum_Unprocessed_Events_Del(
152 p_invoice_id IN NUMBER,
153 p_calling_sequence IN VARCHAR2 DEFAULT NULL)
154 IS
155 l_curr_calling_sequence VARCHAR2(2000);
156 l_procedure_name CONSTANT VARCHAR2(30) := 'Encum_Unprocessed_Events_Del';
157 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
158 l_enc_enabled VARCHAR2(1);
159 l_org_id NUMBER;
160
161 -- bug13114217, added join with AP_INVOICES_ALL
162 -- changed the join conditions for XTE to pick up
163 -- index XLA_TRANSACTION_ENTITIES_N1
164 --
165 CURSOR c_get_unprocessed_events IS
166 SELECT xla.event_id
167 FROM xla_events xla,
168 xla_transaction_entities xte,
169 ap_invoices_all ai
170 WHERE NVL(xla.budgetary_control_flag, 'N') ='Y'
171 AND xla.application_id = 200
172 AND xte.application_id = 200
173 AND xla.event_status_code <> 'P'
174 AND xla.process_status_code <> 'P'
175 AND xla.entity_id = xte.entity_id
176 AND xla.application_id = xte.application_id
177 AND NVL(xte.source_id_int_1, -99) = ai.invoice_id
178 AND xte.entity_code = 'AP_INVOICES'
179 AND xte.ledger_id = ai.set_of_books_id
180 AND ai.invoice_id = p_invoice_id;
181
182 BEGIN
183
184 -- Logging Infra: Procedure level
185 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
186 l_log_msg := 'Begin of procedure '|| l_procedure_name;
187 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
188 l_procedure_name, l_log_msg);
189 END IF;
190
191 l_curr_calling_sequence := 'AP_FUNDS_CONTROL_PKG.'||'<-'||p_calling_sequence;
192
193 SELECT org_id
194 INTO l_org_id
195 FROM ap_invoices_all
196 where invoice_id=p_invoice_id;
197
198 SELECT nvl(purch_encumbrance_flag,'N')
199 INTO l_enc_enabled
200 FROM financials_system_params_all
201 WHERE org_id = l_org_id;
202
203 IF (l_enc_enabled='Y') THEN
204
205 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
206 l_log_msg := 'Before deletion of unprocessed events';
207 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
208 END IF;
209
210 FOR rec_event IN c_get_unprocessed_events
211 LOOP
212
213 AP_ACCOUNTING_EVENTS_PKG.delete_invoice_event
214 (p_accounting_event_id => rec_event.event_id,
215 p_Invoice_Id => p_invoice_id,
216 p_calling_sequence => l_curr_calling_sequence);
217
218 UPDATE ap_prepay_app_dists
219 SET bc_event_id = NULL
220 WHERE prepay_history_id in
221 (SELECT prepay_history_id
222 FROM ap_prepay_history_all
223 WHERE invoice_id = p_invoice_id)
224 AND bc_event_id =rec_event.event_id;
225
226 UPDATE ap_prepay_history_all
227 SET bc_event_id = NULL
228 WHERE invoice_id = p_invoice_id
229 AND bc_event_id = rec_event.event_id;
230
231 UPDATE ap_invoice_distributions
232 SET bc_event_id=NULL
233 WHERE invoice_id = p_invoice_id
234 AND bc_event_id=rec_event.event_id
235 AND nvl(encumbered_flag,'N') <> 'Y';
236
237 UPDATE ap_self_assessed_tax_dist_all
238 SET bc_event_id=NULL
239 WHERE invoice_id = p_invoice_id
240 AND bc_event_id=rec_event.event_id
241 AND nvl(encumbered_flag,'N') <> 'Y';
242
243 END LOOP;
244
245 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
246 l_log_msg := 'End of procedure '|| l_procedure_name;
247 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
248 l_procedure_name, l_log_msg);
249 END IF;
250 END IF;
251
252 EXCEPTION
253 WHEN OTHERS THEN
254 IF (SQLCODE <> -20001) THEN
255 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
256 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
257 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
258 FND_MESSAGE.SET_TOKEN('PARAMETERS',
259 'Invoice_id ='|| p_invoice_id);
260 END IF;
261 APP_EXCEPTION.RAISE_EXCEPTION;
262
263 END Encum_Unprocessed_Events_Del;
264
265 --End of bug 8733916
266
267
268
269 /*============================================================================
270 | PRIVATE PROCEDURE Setup_Gl_FundsCtrl_Params
271 |
272 | DESCRIPTION
273 | Procedure that sets up parameters needed by gl_fundschecker, such as
274 | retrieving the packet_id, setting the appropriate mode and
275 | partial_reservation_flag depending on whether it is for fundschecking
276 | or approval's funds reservation.
277 |
278 | PARAMETERS
279 | p_packet_id - Get one from sequence for Invoice level funds reserve
280 | or funds check
281 | p_status_code - C for Fundscheck and P for Funds reserve
282 | p_bc_mode - GL Fundschecking mode to be populated by this procedure
283 | ('C' for funds check and 'R' for funds reservation)
284 | p_partial_resv_flag - GL Fundschecking partial reservation flag
285 | to be populated by this procedure.
286 | ('Y' for fundschecking,
287 | 'N' for approval's funds reservation.)
288 | p_called_by - Which Program this api is called by
289 | ( APPRVOAL' or 'FUNDSCHKER')
290 | p_calling_sequence - Debugging string to indicate path of module
291 | calls to be printed out NOCOPY upon error.
292 |
293 | NOTE
294 |
295 | MODIFICATION HISTORY
296 | Date Author Description of Change
297 |
298 *==========================================================================*/
299
300 PROCEDURE Setup_Gl_FundsCtrl_Params(
301 p_bc_mode IN OUT NOCOPY VARCHAR2,
302 p_called_by IN VARCHAR2,
303 p_calling_sequence IN VARCHAR2) IS
304
305 l_curr_calling_sequence VARCHAR2(2000);
306
307
308 -- Logging Infra:
309 l_procedure_name CONSTANT VARCHAR2(30) := 'Setup_Gl_FundsCtrl_Params';
310 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
311
312 BEGIN
313
314 -- Logging Infra: Procedure level
315 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
316 l_log_msg := 'Begin of procedure '|| l_procedure_name;
317 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
318 l_procedure_name, l_log_msg);
319 END IF;
320
321 /*-----------------------------------------------------------------+
322 |Init p_bc_mode and p_partial_resv_flag depends on calling program|
323 +-----------------------------------------------------------------*/
324
325 -- bug12997784, added the calling mode PAYMENT REQUEST
326 IF (p_called_by in ( 'APPROVE', 'CANCEL', 'PAYMENT REQUEST') ) THEN
327
328
329 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
330 l_log_msg := 'Calling mode is ' || p_called_by ;
331 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
332 l_procedure_name, l_log_msg);
333 END IF;
334
335 p_bc_mode := 'P'; -- reserve funds --
336
337 ELSE
338
339 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
340 l_log_msg := 'Called by Funds Check';
341 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
342 l_procedure_name||'.begin', l_log_msg);
343 END IF;
344
345 p_bc_mode := 'C'; -- check funds --
346
347 END IF;
348
349 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
350 l_log_msg := 'End of procedure '|| l_procedure_name;
351 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
352 l_procedure_name, l_log_msg);
353 END IF;
354
355 EXCEPTION
356 WHEN OTHERS THEN
357 IF (SQLCODE <> -20001) THEN
358 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
359 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
360 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
361 FND_MESSAGE.SET_TOKEN('PARAMETERS',
362 'Called_by = '|| p_called_by
363 ||', Mode = '|| p_bc_mode);
364 END IF;
365 APP_EXCEPTION.RAISE_EXCEPTION;
366 END Setup_Gl_FundsCtrl_Params;
367
368 /*============================================================================
369 | PRIVATE PROCEDURE FundsReserve_Init
370 |
371 | DESCRIPTION
372 | Procedure initialize the parameter values needed by funds reserve
373 |
374 | PARAMETERS
375 | p_invoice_id - invoice id
376 | p_system_user - caller's user id
377 | p_override_mode - Out parameter
378 | p_fundschk_user_id - out and set to the one who release the hold
379 | p_fundschk_resp_id - out and set to the responsibilty who release
380 | the hold
381 | p_calling_sequence - Debugging string to indicate path of module
382 | calls to be printed out NOCOPY upon error.
383 | NOTE
384 |
385 | MODIFICATION HISTORY
386 | Date Author Description of Change
387 |
388 *==========================================================================*/
389
390 PROCEDURE FundsReserve_Init(
391 p_invoice_id IN NUMBER,
392 p_system_user IN NUMBER,
393 p_override_mode IN OUT NOCOPY VARCHAR2,
394 p_fundschk_user_id IN OUT NOCOPY NUMBER,
395 p_fundschk_resp_id IN OUT NOCOPY NUMBER,
396 p_calling_sequence IN VARCHAR2) IS
397
398 l_curr_calling_sequence VARCHAR2(2000);
399 l_hold_reason VARCHAR2(240);
400 l_hold_status VARCHAR2(25);
401 l_user_id NUMBER;
402 l_resp_id NUMBER;
403
404 -- Logging Infra:
405 l_procedure_name CONSTANT VARCHAR2(30) := 'FundsReserve_Init';
406 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
407
408 BEGIN
409
410 l_curr_calling_sequence := 'AP_FUNDS_CONTROL_PKG.'||
411 '<-'||p_calling_sequence;
412
413 /*-----------------------------------------------------------------+
414 | Step 1 - Set the override mode for funds reserve |
415 | Note - Bug 2184558 Indicates we always want the |
416 | override mode to be set to 'Y' |
417 +-----------------------------------------------------------------*/
418
419 -- Logging Infra: Procedure level
420 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
421 l_log_msg := 'Begin of procedure '|| l_procedure_name;
422 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
423 l_procedure_name, l_log_msg);
424 END IF;
425
426 p_override_mode := 'Y';
427
428 /*-----------------------------------------------------------------+
429 | Step 2 - Get hold status and set the user and resp id |
430 | Check if insufficient funds hold was user released |
431 +-----------------------------------------------------------------*/
432
433
434 -- Logging Infra: Procedure level
435 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
436 l_log_msg := 'Check if insufficient funds hold was user released';
437 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
438 l_procedure_name, l_log_msg);
439 END IF;
440
441 AP_APPROVAL_PKG.Get_Hold_Status(
442 p_invoice_id,
443 null,
444 null,
445 'INSUFFICIENT FUNDS',
446 p_system_user,
447 l_hold_status,
448 l_hold_reason,
449 l_user_id,
450 l_resp_id,
451 l_curr_calling_sequence);
452
453 /*-----------------------------------------------------------------+
454 | Step 3 - fundschecking to Forced Mode if hold is released by |
455 | user |
456 +-----------------------------------------------------------------*/
457
458 IF (l_hold_status = 'RELEASED BY USER') THEN
459
460
461 -- Logging Infra: Procedure level
462 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
463 l_log_msg := 'Hold was released by user.';
464 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME|| l_procedure_name, l_log_msg);
465 END IF;
466
467 IF (l_resp_id IS NOT NULL) THEN
468
469 p_fundschk_user_id := l_user_id;
470 p_fundschk_resp_id := l_resp_id;
471
472 END IF; -- end of check l_resp_id
473
474 END IF; -- l_hold_status = 'RELEASED BY USER' --
475
476 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
477 l_log_msg := 'End of procedure '|| l_procedure_name;
478 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
479 l_procedure_name, l_log_msg);
480 END IF;
481 EXCEPTION
482 WHEN OTHERS THEN
483 IF (SQLCODE <> -20001) THEN
484 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
485 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
486 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
487 FND_MESSAGE.SET_TOKEN('PARAMETERS',
488 'Invoice_id = '|| to_char(p_invoice_id)
489 ||', system_user = '|| to_char(p_system_user) );
490 END IF;
491 APP_EXCEPTION.RAISE_EXCEPTION;
492 END FundsReserve_Init;
493
494
495 /*============================================================================
496 | PRIVATE PROCEDURE FUNDSCHECK_INIT
497 |
498 | DESCRIPTION
499 | Procedure to retrieve system parameters to be used in fundschecker
500 |
501 | PARAMETERS
502 | p_chart_of_accounts_id - Variable for the procedure to populate with
503 | the chart of accounts id
504 | p_set_of_books_id - Variable for the procedure to populate with the
505 | set of books id
506 | p_xrate_gain_ccid - Variable for the procedure to populate with the
507 | exchange rate variance gain ccid
508 | p_xrate_loss_ccid - Variable for the procedure to populate with the
509 | exchange rate variance loss ccid
510 | p_base_currency_code - Variable for the procedure to populate with the
511 | base currency code
512 | p_inv_enc_type_id - Variable for the procedure to populate with the
513 | invoice encumbrance type id
514 | p_gl_user_id - Variable for the procedure to populate with the
515 | profile option user_id to be used for the
516 | gl_fundschecker
517 | p_calling_sequence - Debugging string to indicate path of module calls
518 | to be printed out NOCOPY upon error.
519 |
520 | NOTE
521 |
522 | MODIFICATION HISTORY
523 | Date Author Description of Change
524 |
525 *==========================================================================*/
526 PROCEDURE FundsCheck_Init(
527 p_invoice_id IN NUMBER,
528 p_set_of_books_id IN OUT NOCOPY NUMBER,
529 p_xrate_gain_ccid IN OUT NOCOPY NUMBER,
530 p_xrate_loss_ccid IN OUT NOCOPY NUMBER,
531 p_base_currency_code IN OUT NOCOPY VARCHAR2,
532 p_inv_enc_type_id IN OUT NOCOPY NUMBER,
533 p_gl_user_id IN OUT NOCOPY NUMBER,
534 p_calling_sequence IN VARCHAR2) IS
535
536 l_curr_calling_sequence VARCHAR2(2000);
537 l_procedure_name CONSTANT VARCHAR2(30) := 'Fundscheck_Init';
538 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
539
540 BEGIN
541
542 -- Update the calling sequence --
543
544 l_curr_calling_sequence := 'AP_FUNDS_CONTROL_PKG.'||l_procedure_name||
545 '<-'||p_calling_sequence;
546
547 /*----------------------------------------------------------------+
548 | Retrieving system parameters for fundschecker |
549 +-----------------------------------------------------------------*/
550
551 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
552 l_log_msg := 'Begin of procedure '|| l_procedure_name;
553 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
554 l_procedure_name, l_log_msg);
555 END IF;
556
557 BEGIN
558
559 SELECT sp.set_of_books_id,
560 nvl(sp.rate_var_gain_ccid, -1),
561 nvl(sp.rate_var_loss_ccid, -1),
562 nvl(sp.base_currency_code, 'USD'),
563 nvl(fp.inv_encumbrance_type_id, -1)
564 INTO p_set_of_books_id,
565 p_xrate_gain_ccid,
566 p_xrate_loss_ccid,
567 p_base_currency_code,
568 p_inv_enc_type_id
569 FROM ap_system_parameters sp,
570 financials_system_parameters fp,
571 gl_sets_of_books gls,
572 ap_invoices ai
573 WHERE sp.set_of_books_id = gls.set_of_books_id
574 AND sp.set_of_books_id = ai.set_of_books_id
575 AND ai.invoice_id = p_invoice_id;
576
577 EXCEPTION WHEN NO_DATA_FOUND THEN
578 NULL;
579 END;
580
581 /*-----------------------------------------------------------------+
582 | Retrieving profile optpon user id |
583 +-----------------------------------------------------------------*/
584
585 FND_PROFILE.GET('USER_ID', p_gl_user_id);
586
587 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
588 l_log_msg := 'End of procedure '|| l_procedure_name;
589 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
590 l_procedure_name, l_log_msg);
591 END IF;
592
593
594 EXCEPTION
595 WHEN OTHERS THEN
596 IF (SQLCODE <> -20001) THEN
597 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
598 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
599 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
600 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
601 END IF;
602 APP_EXCEPTION.RAISE_EXCEPTION;
603 END FundsCheck_Init;
604
605 /*============================================================================
606 | PRIVATE PROCEDURE GET_GL_FUNDSCHK_RESULT_CODE
607 |
608 | DESCRIPTION
609 | Procedure to retrieve the GL_Fundschecker result code after the
610 | GL_Fundschecker has been run.
611 |
612 | PARAMETERS
613 | p_fc_result_code : Variable to contain the gl funds checker result
614 | code
615 |
616 | NOTE
617 |
618 | MODIFICATION HISTORY
619 | Date Author Description of Change
620 |
621 *==========================================================================*/
622
623 PROCEDURE Get_GL_FundsChk_Result_Code(
624 p_fc_result_code IN OUT NOCOPY VARCHAR2) IS
625
626 -- Logging Infra:
627 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_GL_FundsChk_Result_Code';
628 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
629
630 BEGIN
631
632 ---------------------------------------------------------------
633 -- Retrieve GL Fundschecker Failure Result Code --
634 ---------------------------------------------------------------
635
636 -- Logging Infra: Procedure level
637 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
638 l_log_msg := 'Begin of procedure '|| l_procedure_name;
639 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
640 l_procedure_name, l_log_msg);
641 END IF;
642
643 BEGIN
644 SELECT l.lookup_code
645 INTO p_fc_result_code
646 FROM gl_lookups l
647 WHERE lookup_type = 'FUNDS_CHECK_RESULT_CODE'
648 AND EXISTS ( SELECT 'x'
649 FROM gl_bc_packets bc,
650 xla_events_gt e
651 WHERE bc.event_id = e.event_id
652 AND result_code like 'F%'
653 AND bc.result_code = l.lookup_code)
654 AND rownum = 1;
655 EXCEPTION
656 WHEN OTHERS THEN
657 NULL;
658 END;
659
660 -- Logging Infra: Procedure level
661 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
662 l_log_msg := 'End of procedure '|| l_procedure_name;
663 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
664 l_procedure_name, l_log_msg);
665 END IF;
666
667 EXCEPTION
668 WHEN OTHERS THEN
669 IF (SQLCODE <> -20001) THEN
670 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
671 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
672 END IF;
673 APP_EXCEPTION.RAISE_EXCEPTION;
674 END Get_GL_FundsChk_Result_Code;
675
676 /*=============================================================================
677 |Public Procedure Definition
678 *===========================================================================*/
679
680 /*============================================================================
681 | PUBLIC PROCEDURE FUNDS_RESERVE
682 |
683 | DESCRIPTION
684 | Procedure to performs funds reservations.
685 |
686 | PARAMETERS
687 | p_invoice_id - Invoice Id
688 | p_unique_packet_id_per - ('INVOICE' or 'DISTRIBUTION')
689 | p_set_of_books_id - Set of books Id
690 | p_base_currency_code - Base Currency Code
691 | p_conc_flag ('Y' or 'N') - indicating if procedure is to be called as
692 | a concurrent program or userexit.
693 | p_system_user - Approval Program User Id
694 | p_holds - Holds Array
695 | p_hold_count - Holds Count Array
696 | p_release_count - Release Count Array
697 | p_calling_sequence - Debugging string to indicate path of module calls
698 | to be printed out NOCOPY upon error.
699 | NOTE
700 |
701 | MODIFICATION HISTORY
702 | Date Author Description of Change
703 | 15-OCT-2009 GAGRAWAL bug9026201, instead of raising an API EXCEPTION
704 | in case of PSA api returning a failure or
705 | giving an Exception, we would be putting the
706 | invoice on a hold.
707 *==========================================================================*/
708
709 PROCEDURE Funds_Reserve(
710 p_calling_mode IN VARCHAR2 DEFAULT 'APPROVE',
711 p_invoice_id IN NUMBER,
712 p_set_of_books_id IN NUMBER,
713 p_base_currency_code IN VARCHAR2,
714 p_conc_flag IN VARCHAR2,
715 p_system_user IN NUMBER,
716 p_holds IN OUT NOCOPY AP_APPROVAL_PKG.HOLDSARRAY,
717 p_hold_count IN OUT NOCOPY AP_APPROVAL_PKG.COUNTARRAY,
718 p_release_count IN OUT NOCOPY AP_APPROVAL_PKG.COUNTARRAY,
719 p_funds_return_code OUT NOCOPY VARCHAR2, -- 4276409 (3462325)
720 p_calling_sequence IN VARCHAR2) IS
721
722
723 CURSOR cur_fc_dist IS --bc FundsReserve_Inv_Dist_Cur IS
724 SELECT I.invoice_id, -- invoice_id
725 I.invoice_num, -- invoice_num
726 I.legal_entity_id, -- BCPSA bug
727 I.invoice_type_lookup_code, -- invoice_type_code
728 D.invoice_line_number, -- inv_line_num
729 D.invoice_distribution_id , -- inv_distribution_id
730 D.accounting_date, -- accounting_date
731 D.LINE_TYPE_LOOKUP_CODE, -- distribution_type
732 D.amount, -- distribution_amount
733 D.set_of_books_id, -- set_of_books_id
734 D.bc_event_id, -- bc_event_id
735 D.org_id, -- org_id
736 NULL, --result_code
737 NULL, --status_code
738 'N' self_assessed_flag --self_assessed_flag --bug7109594
739 FROM gl_period_statuses PER,
740 ap_invoices I,
741 ap_invoice_distributions_all D,
742 ap_invoice_lines L
743 WHERE D.invoice_id = I.invoice_id
744 AND D.invoice_line_number = L.line_number
745 AND L.invoice_id = D.invoice_id
746 AND D.posted_flag in ('N', 'P')
747 AND nvl(D.encumbered_flag, 'N') in ('N', 'H', 'P')
748 AND L.line_type_lookup_code NOT IN ('AWT')
749 AND D.period_name = PER.period_name
750 AND PER.set_of_books_id = p_set_of_books_id
751 AND PER.application_id = 200
752 AND NVL(PER.adjustment_period_flag, 'N') = 'N'
753 AND I.invoice_id = p_invoice_id
754 AND D.po_distribution_id is NULL
755 AND (( D.match_status_flag = 'S')
756 AND (NOT EXISTS (SELECT 'X'
757 FROM ap_holds H,
758 ap_hold_codes C
759 WHERE H.invoice_id = D.invoice_id
760 AND H.line_location_id is null
761 AND H.hold_lookup_code = C.hold_lookup_code
762 AND ((H.release_lookup_code IS NULL)
763 AND ((C.postable_flag = 'N') OR
764 (C.postable_flag = 'X')))
765 AND H.hold_lookup_code <> 'CANT FUNDS CHECK'
766 AND H.hold_lookup_code <> 'INSUFFICIENT FUNDS'
767 AND H.hold_lookup_code <> 'Encumbrance Acctg Fail'))) --Bug 9136390
768 UNION ALL
769 SELECT I.invoice_id, -- invoice_id
770 I.invoice_num, -- invoice_num
771 I.legal_entity_id, -- BCPSA bug
772 I.invoice_type_lookup_code, -- invoice_type_code
773 D.invoice_line_number, -- inv_line_num
774 D.invoice_distribution_id , -- inv_distribution_id
775 D.accounting_date, -- accounting_date
776 D.LINE_TYPE_LOOKUP_CODE, -- distribution_type
777 D.amount, -- distribution_amount
778 D.set_of_books_id, -- set_of_books_id
779 D.bc_event_id, -- bc_event_id
780 D.org_id, -- org_id
781 NULL, --result_code
782 NULL, --status_code
783 'N' self_assessed_flag --self_assessed_flag --bug7109594
784 FROM gl_period_statuses PER,
785 ap_invoices I,
786 ap_invoice_distributions_all D,
787 ap_invoice_lines L,
788 po_distributions_all pod
789 WHERE D.invoice_id = I.invoice_id
790 AND D.invoice_line_number = L.line_number
791 AND L.invoice_id = D.invoice_id
792 AND ( (D.line_type_lookup_code = 'ITEM' AND
793 NVL(pod.accrue_on_receipt_flag,'N') <> 'Y')
794 OR
795 (D.line_type_lookup_code NOT IN
796 ( 'RETAINAGE', 'ACCRUAL', 'ITEM' )) )
797 AND D.posted_flag in ('N', 'P')
798 AND nvl(D.encumbered_flag, 'N') in ('N', 'H', 'P')
799 AND L.line_type_lookup_code NOT IN ('AWT')
800 AND D.period_name = PER.period_name
801 AND PER.set_of_books_id = p_set_of_books_id
802 AND PER.application_id = 200
803 AND NVL(PER.adjustment_period_flag, 'N') = 'N'
804 AND I.invoice_id = p_invoice_id
805 AND (( D.match_status_flag = 'S')
806 AND (NOT EXISTS (SELECT 'X'
807 FROM ap_holds H,
808 ap_hold_codes C
809 WHERE H.invoice_id = D.invoice_id
810 AND H.line_location_id is null
811 AND H.hold_lookup_code = C.hold_lookup_code
812 AND ((H.release_lookup_code IS NULL)
813 AND ((C.postable_flag = 'N') OR
814 (C.postable_flag = 'X')))
815 AND H.hold_lookup_code <> 'CANT FUNDS CHECK'
816 AND H.hold_lookup_code <> 'INSUFFICIENT FUNDS'
817 AND H.hold_lookup_code <> 'Encumbrance Acctg Fail'))) --Bug 9136390
818 AND D.po_distribution_id IS NOT NULL
819 AND D.po_distribution_id = pod.po_distribution_id
820 AND NOT EXISTS ( select 'Advance Exists'
821 from po_distributions_all pod,
822 po_headers_all poh,
823 ap_invoice_distributions_all ainvd,
824 ap_invoices_all ainv,
825 po_doc_style_headers pdsa
826 where pod.po_distribution_id = D.po_distribution_id
827 and poh.po_header_id = pod.po_header_id
828 and poh.style_id = pdsa.style_id
829 and ainv.invoice_id = D.invoice_id
830 and ainv.invoice_id = ainvd.invoice_id
831 and ainvd.po_distribution_id = pod.po_distribution_id
832 and nvl(pdsa.advances_flag, 'N') = 'Y'
833 and (ainvd.line_type_lookup_code = 'PREPAY'
834 OR
835 ainv.invoice_type_lookup_code = 'PREPAYMENT') )
836 UNION ALL
837 SELECT I.invoice_id, -- invoice_id
838 I.invoice_num, -- invoice_num
839 I.legal_entity_id, -- BCPSA bug
840 I.invoice_type_lookup_code, -- invoice_type_code
841 T.invoice_line_number, -- inv_line_num
842 T.invoice_distribution_id , -- inv_distribution_id
843 T.accounting_date, -- accounting_date
844 T.LINE_TYPE_LOOKUP_CODE, -- distribution_type
845 T.amount, -- distribution_amount
846 T.set_of_books_id, -- set_of_books_id
847 T.bc_event_id, -- bc_event_id
848 T.org_id, -- org_id
849 NULL, --result_code
850 NULL, --status_code
851 T.self_assessed_flag --self_assessed_flag --bug7109594
852 FROM gl_period_statuses PER,
853 ap_invoices I,
854 ap_self_assessed_tax_dist_all T
855 WHERE T.invoice_id = I.invoice_id
856 AND T.posted_flag in ('N', 'P')
857 AND nvl(T.encumbered_flag, 'N') in ('N', 'H', 'P')
858 AND T.period_name = PER.period_name
859 AND PER.set_of_books_id = p_set_of_books_id
860 AND PER.application_id = 200
861 AND NVL(PER.adjustment_period_flag, 'N') = 'N'
862 AND I.invoice_id = p_invoice_id
863 AND T.po_distribution_id is NULL
864 AND (( T.match_status_flag = 'S')
865 AND (NOT EXISTS (SELECT 'X'
866 FROM ap_holds H,
867 ap_hold_codes C
868 WHERE H.invoice_id = T.invoice_id
869 AND H.line_location_id is null
870 AND H.hold_lookup_code = C.hold_lookup_code
871 AND ((H.release_lookup_code IS NULL)
872 AND ((C.postable_flag = 'N') OR
873 (C.postable_flag = 'X')))
874 AND H.hold_lookup_code <> 'CANT FUNDS CHECK'
875 AND H.hold_lookup_code <> 'INSUFFICIENT FUNDS'
876 AND H.hold_lookup_code <> 'Encumbrance Acctg Fail'))) --Bug 9136390
877 UNION ALL
878 SELECT I.invoice_id, -- invoice_id
879 I.invoice_num, -- invoice_num
880 I.legal_entity_id, -- BCPSA bug
881 I.invoice_type_lookup_code, -- invoice_type_code
882 T.invoice_line_number, -- inv_line_num
883 T.invoice_distribution_id , -- inv_distribution_id
884 T.accounting_date, -- accounting_date
885 T.LINE_TYPE_LOOKUP_CODE, -- distribution_type
886 T.amount, -- distribution_amount
887 T.set_of_books_id, -- set_of_books_id
888 T.bc_event_id, -- bc_event_id
889 T.org_id, -- org_id
890 NULL, --result_code
891 NULL, --status_code
892 T.self_assessed_flag --self_assessed_flag --bug7109594
893 FROM gl_period_statuses PER,
894 ap_invoices I,
895 ap_self_assessed_tax_dist_all T
896 WHERE T.invoice_id = I.invoice_id
897 AND T.posted_flag in ('N', 'P')
898 AND nvl(T.encumbered_flag, 'N') in ('N', 'H', 'P')
899 AND T.period_name = PER.period_name
900 AND PER.set_of_books_id = p_set_of_books_id
901 AND PER.application_id = 200
902 AND NVL(PER.adjustment_period_flag, 'N') = 'N'
903 AND I.invoice_id = p_invoice_id
904 AND (( T.match_status_flag = 'S')
905 AND (NOT EXISTS (SELECT 'X'
906 FROM ap_holds H,
907 ap_hold_codes C
908 WHERE H.invoice_id = T.invoice_id
909 AND H.line_location_id is null
910 AND H.hold_lookup_code = C.hold_lookup_code
911 AND ((H.release_lookup_code IS NULL)
912 AND ((C.postable_flag = 'N') OR
913 (C.postable_flag = 'X')))
914 AND H.hold_lookup_code <> 'CANT FUNDS CHECK'
915 AND H.hold_lookup_code <> 'INSUFFICIENT FUNDS'
916 AND H.hold_lookup_code <> 'Encumbrance Acctg Fail'))) --Bug 9136390
917 AND T.po_distribution_id is NOT NULL
918 AND NOT EXISTS ( select 'Advance Exists'
919 from po_distributions_all pod,
920 po_headers_all poh,
921 ap_invoice_distributions_all ainvd,
922 ap_invoices_all ainv,
923 po_doc_style_headers pdsa
924 where pod.po_distribution_id = T.po_distribution_id
925 and poh.po_header_id = pod.po_header_id
926 and poh.style_id = pdsa.style_id
927 and ainv.invoice_id = T.invoice_id
928 and ainv.invoice_id = ainvd.invoice_id
929 and ainvd.po_distribution_id = pod.po_distribution_id
930 and nvl(pdsa.advances_flag, 'N') = 'Y'
931 and (ainvd.line_type_lookup_code = 'PREPAY'
932 OR
933 ainv.invoice_type_lookup_code = 'PREPAYMENT') );
934
935 l_debug_loc VARCHAR2(2000) := 'Funds_Reserve';
936 l_curr_calling_sequence VARCHAR2(2000);
937 l_debug_info VARCHAR2(2000);
938
939 l_partial_reserv_flag VARCHAR2(1);
940 l_insuff_funds_exists VARCHAR2(1);
941 l_cant_fundsck_exists VARCHAR2(1);
942 l_enc_acctg_fail_exists VARCHAR2(1); --Bug 9136390
943 l_fundschk_user_id NUMBER(15);
944 l_fundschk_resp_id NUMBER(15);
945 l_user_id NUMBER;
946 l_resp_id NUMBER;
947 l_bc_mode VARCHAR2(1) := 'R';
948 l_status_code VARCHAR2(1);
949 l_override_mode VARCHAR2(1) := 'N';
950 l_return_code VARCHAR2(30);
951
952 t_funds_dist_tab PSA_AP_BC_PVT.Funds_Dist_Tab_Type;
953
954 l_dist_rec_count NUMBER := 0;
955 i BINARY_INTEGER := 1;
956 j BINARY_INTEGER := 1;
957 ind BINARY_INTEGER := 1;
958 num BINARY_INTEGER := 1;
959
960 l_return_status VARCHAR2(30);
961 l_msg_count NUMBER;
962 l_cfc_hold_cnt NUMBER; --Bug 9168747
963 l_msg_data VARCHAR2(2000);
964 l_result_code VARCHAR2(30);
965 l_packet_id NUMBER; --Bug 4535804
966 l_bc_event_id XLA_EVENTS.EVENT_ID%TYPE;
967 l_bc_event_status XLA_EVENTS.EVENT_STATUS_CODE%TYPE;
968 l_count_unproc NUMBER;
969
970 l_org_id NUMBER; --Bug 5487757
971
972 PSA_API_EXCEPTION EXCEPTION;
973
974 -- Logging Infra:
975 l_procedure_name CONSTANT VARCHAR2(30) := 'Funds_Reserve';
976 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
977
978
979 BEGIN
980 l_curr_calling_sequence := 'AP_FUNDS_CONTROL_PKG.'||l_debug_loc||
981 '<-'||p_calling_sequence;
982
983 -- Logging Infra: Procedure level
984 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
985 l_log_msg := 'Begin of procedure '|| l_procedure_name;
986 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
987 l_procedure_name, l_log_msg);
988 END IF;
989 l_debug_info := 'Initialize other variables';
990
991 l_insuff_funds_exists := 'N';
992 l_cant_fundsck_exists := 'N';
993 l_enc_acctg_fail_exists := 'N'; --Bug 9136390
994 l_fundschk_user_id := NULL;
995 l_fundschk_resp_id := NULL;
996
997 --Bug 5487757
998 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
999 l_log_msg := 'Selecting Org_Id for determining Encumbrance Enabled or not' ;
1000 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
1001 l_procedure_name, l_log_msg);
1002 END IF;
1003
1004 SELECT org_id
1005 INTO l_org_id
1006 FROM AP_INVOICES_ALL
1007 WHERE invoice_id = p_invoice_id;
1008
1009 IF (Encumbrance_Enabled(l_org_id)) THEN
1010
1011 ------------------------------------------------------------
1012 -- Encumbrance enabled, setup gl_fundschecker parameters --
1013 ------------------------------------------------------------
1014
1015 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
1016 l_log_msg := 'FUNDSRESERVE - Encumbrance enabled and ' ||
1017 'setup gl_fundschecker parameters ';
1018 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
1019 l_procedure_name, l_log_msg);
1020 END IF;
1021
1022 /*-----------------------------------------------------------------+
1023 | Step 1 - Set Funds Control Parameters |
1024 +-----------------------------------------------------------------*/
1025
1026 Setup_Gl_FundsCtrl_Params(
1027 l_bc_mode,
1028 p_calling_mode,
1029 l_curr_calling_sequence);
1030
1031 /*-----------------------------------------------------------------+
1032 | Step 2 - Get override mode and re-set the userid and |
1033 | responsibility id to who ever release the invoice |
1034 | hold |
1035 +-----------------------------------------------------------------*/
1036
1037
1038 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
1039 l_log_msg := ' call api to get override mode ' ||
1040 'ID informaiton';
1041 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
1042 l_procedure_name, l_log_msg);
1043 END IF;
1044
1045 FundsReserve_Init(
1046 p_invoice_id => p_invoice_id,
1047 p_system_user => p_system_user,
1048 p_override_mode => l_override_mode,
1049 p_fundschk_user_id => l_fundschk_user_id,
1050 p_fundschk_resp_id => l_fundschk_resp_id,
1051 p_calling_sequence => l_curr_calling_sequence );
1052
1053 /*-----------------------------------------------------------------+
1054 | Step 2.5 - Update the encumbered_flag for recoverable tax |
1055 | distributions to R so that these are not sent to PSA |
1056 | for encumbering |
1057 +-----------------------------------------------------------------*/
1058
1059
1060 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
1061 l_log_msg := ' Update encumbered flag of recoverable ' ||
1062 'tax distributions to R';
1063 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
1064 l_procedure_name, l_log_msg);
1065 END IF;
1066
1067 Update ap_invoice_distributions_all
1068 set encumbered_flag = 'R'
1069 where invoice_id = p_invoice_id
1070 and line_type_lookup_code = 'REC_TAX';
1071
1072
1073 /*-----------------------------------------------------------------+
1074 | Step 3 - Get all the selected distributions for processing |
1075 +-----------------------------------------------------------------*/
1076
1077 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1078 l_log_msg := 'Step 3 - Open FundsCntrl_Inv_Dist_Cur Cursor';
1079 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1080 l_procedure_name, l_log_msg);
1081 END IF;
1082
1083 OPEN cur_fc_dist;
1084 FETCH cur_fc_dist BULK COLLECT INTO t_funds_dist_tab;
1085 CLOSE cur_fc_dist;
1086
1087
1088
1089 /*-----------------------------------------------------------------+
1090 | Step 4 - Accounting Event Handling - Create, Stamp, Cleanup |
1091 +-----------------------------------------------------------------*/
1092
1093 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1094 l_log_msg := 'Step 3 - Call psa_ap_bc_pvt.Create_Events';
1095 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1096 l_procedure_name, l_log_msg);
1097 END IF;
1098
1099 IF ( t_funds_dist_tab.COUNT <> 0 ) THEN
1100
1101
1102 --
1103 -- Bug 5376406
1104 -- Modified the code as discussed with Anne/Jayanta
1105 -- The code used to be called after the create events
1106 -- which had the problems dscribed in bug 5374571.
1107 -- Bug 5455072
1108 -- Commented p_calling_mode check. Reinstate API
1109 -- should be called irrespective of the mode. API
1110 -- should do the necessary checks to reinstate PO
1111 -- encumbrance.
1112 --IF ( P_calling_mode = 'CANCEL') THEN
1113
1114 -- bug 9026201
1115 BEGIN
1116 psa_ap_bc_pvt.Reinstate_PO_Encumbrance(
1117 p_calling_mode => p_calling_mode,
1118 p_tab_fc_dist => t_funds_dist_tab,
1119 p_calling_sequence => l_curr_calling_sequence,
1120 x_return_status => l_return_status,
1121 x_msg_count => l_msg_count,
1122 x_msg_data => l_msg_data);
1123
1124 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1125 l_log_msg := 'Call psa_ap_bc_pvt.reinstate_po_encumbrance returned' ||
1126 'l_return_status =' || l_return_status;
1127 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1128 l_procedure_name, l_log_msg);
1129 END IF;
1130
1131 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1132 l_cant_fundsck_exists := 'Y';
1133 RAISE PSA_API_EXCEPTION;
1134 END IF;
1135
1136 EXCEPTION
1137 WHEN OTHERS THEN
1138 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1139 l_log_msg := 'Call psa_ap_bc_pvt.reinstate_po_encumbrance '||
1140 'raised an Exception' ||SQLERRM;
1141 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1142 l_procedure_name, l_log_msg);
1143 END IF;
1144
1145 l_cant_fundsck_exists := 'Y';
1146 RAISE PSA_API_EXCEPTION;
1147
1148 END;
1149
1150 -- bug9026201
1151 BEGIN
1152 psa_ap_bc_pvt.Create_Events (
1153 p_init_msg_list => fnd_api.g_true,
1154 p_tab_fc_dist => t_funds_dist_tab,
1155 p_calling_mode => p_calling_mode,
1156 p_bc_mode => l_bc_mode,
1157 p_calling_sequence => l_curr_calling_sequence,
1158 x_return_status => l_return_status,
1159 x_msg_count => l_msg_count,
1160 x_msg_data => l_msg_data);
1161
1162 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1163 l_log_msg := 'Call psa_ap_bc_pvt.Create_Events returned' ||
1164 'l_return_status =' || l_return_status;
1165 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1166 l_procedure_name, l_log_msg);
1167 END IF;
1168
1169 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1170 l_cant_fundsck_exists := 'Y';
1171 RAISE PSA_API_EXCEPTION;
1172 END IF;
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1177 l_log_msg := 'Call psa_ap_bc_pvt.Create_Events '||
1178 'raised an Exception' ||SQLERRM;
1179 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1180 l_procedure_name, l_log_msg);
1181 END IF;
1182
1183 l_cant_fundsck_exists := 'Y';
1184 RAISE PSA_API_EXCEPTION;
1185
1186 END;
1187
1188
1189 /*-----------------------------------------------------------------+
1190 | Step 5 - Call PSA BUDGETARY CONTROL API |
1191 +-----------------------------------------------------------------*/
1192
1193 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1194 l_log_msg := 'Step 4 - Call PSA_BC_XLA_PUB.Budgetary_Control';
1195 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1196 l_procedure_name, l_log_msg);
1197 END IF;
1198
1199 -- bug9026201
1200 BEGIN
1201 PSA_BC_XLA_PUB.Budgetary_Control(
1202 p_api_version => 1.0,
1203 p_init_msg_list => Fnd_Api.G_False,
1204 x_return_status => l_return_status,
1205 x_msg_count => l_msg_count,
1206 x_msg_data => l_msg_data,
1207 p_application_id => 200,
1208 p_bc_mode => l_bc_mode,
1209 p_override_flag => l_override_mode,
1210 P_user_id => l_fundschk_user_id,
1211 P_user_resp_id => l_fundschk_resp_id,
1212 x_status_code => l_return_code,
1213 x_Packet_ID => l_packet_id );
1214
1215 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1216 l_log_msg := 'Call psa_ap_bc_pvt.Budgetary_Control returned' ||
1217 'l_return_status =' || l_return_status ||
1218 'l_packet_id =' || to_char(l_packet_id);
1219 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1220 l_procedure_name, l_log_msg);
1221 END IF;
1222
1223 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1224 l_cant_fundsck_exists := 'Y';
1225 RAISE PSA_API_EXCEPTION;
1226 END IF;
1227
1228 EXCEPTION
1229 WHEN OTHERS THEN
1230 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1231 l_log_msg := 'Call psa_ap_bc_pvt.Budgetary_Control '||
1232 'raised an Exception' ||SQLERRM;
1233 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1234 l_procedure_name, l_log_msg);
1235 END IF;
1236
1237 l_cant_fundsck_exists := 'Y';
1238 RAISE PSA_API_EXCEPTION;
1239
1240 END;
1241
1242
1243 IF (l_return_code in ('FATAL', 'FAIL', 'PARTIAL',
1244 'XLA_ERROR','XLA_NO_JOURNAL' )) THEN
1245
1246 /*-----------------------------------------------------------------+
1247 | Funds Reserve failed for the whole invoice |
1248 +-----------------------------------------------------------------*/
1249
1250 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1251 l_log_msg := 'Step 6.1 - process return code =' || l_return_code ;
1252 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1253 l_procedure_name, l_log_msg);
1254 END IF;
1255
1256 --Bug 9136390 Starts
1257 IF ( l_return_code IN ('XLA_ERROR','XLA_NO_JOURNAL' )) THEN
1258 l_enc_acctg_fail_exists := 'Y';
1259 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1260 l_log_msg := 'l_enc_acctg_fail_exists is set' ||
1261 'l_enc_acctg_fail_exists' || l_enc_acctg_fail_exists;
1262 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1263 l_procedure_name, l_log_msg);
1264 END IF;
1265 --Bug 9136390 Ends
1266 ELSIF ( l_return_code = 'FATAL' ) THEN --Bug 9136390
1267
1268 l_cant_fundsck_exists := 'Y';
1269 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1270 l_log_msg := 'l_cant_fundsck_exists is set' ||
1271 'l_cant_fundsck_exists' || l_cant_fundsck_exists;
1272 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1273 l_procedure_name, l_log_msg);
1274 END IF;
1275
1276 ELSE
1277
1278 l_insuff_funds_exists := 'Y';
1279 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1280 l_log_msg := 'l_insuff_funds_exists is set' ||
1281 'l_insuff_funds_exists' || l_insuff_funds_exists;
1282 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1283 l_procedure_name, l_log_msg);
1284 END IF;
1285
1286 END IF;
1287
1288 IF l_return_code = 'PARTIAL' THEN
1289
1290 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1291 l_log_msg := 'funds reservation returned Partial and calling '||
1292 'psa_ap_bc_pvt.Get_Detailed_Results';
1293 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1294 l_procedure_name, l_log_msg);
1295 END IF;
1296
1297 -- bug9026201
1298 BEGIN
1299 psa_ap_bc_pvt.Get_Detailed_Results (
1300 p_init_msg_list => FND_API.g_true,
1301 p_tab_fc_dist => t_funds_dist_tab,
1302 p_calling_sequence => l_curr_calling_sequence,
1303 x_return_status => l_return_status,
1304 x_msg_count => l_msg_count,
1305 x_msg_data => l_msg_data);
1306
1307 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1308 l_log_msg := 'Call psa_ap_bc_pvt.Get_Detailed_Results returned' ||
1309 'l_return_status =' || l_return_status;
1310
1311 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1312 l_procedure_name, l_log_msg);
1313 END IF;
1314
1315 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1316 l_cant_fundsck_exists := 'Y';
1317 RAISE PSA_API_EXCEPTION;
1318 END IF;
1319
1320 EXCEPTION
1321 WHEN OTHERS THEN
1322 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1323 l_log_msg := 'Call psa_ap_bc_pvt.Get_Detailed_Results '||
1324 'raised an Exception' ||SQLERRM;
1325 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1326 l_procedure_name, l_log_msg);
1327 END IF;
1328
1329 l_cant_fundsck_exists := 'Y';
1330 RAISE PSA_API_EXCEPTION;
1331
1332 END;
1333
1334 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1335 l_log_msg := 'process t_funds_dist_tab touched by PSA' ||
1336 'PL/SQL TABLE COUNT IS' || to_char(t_funds_dist_tab.COUNT)||
1337 'now beginning to check the data sanity';
1338 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1339 l_procedure_name, l_log_msg);
1340 END IF;
1341
1342
1343 l_count_unproc := 0;
1344 FOR i IN 1..t_funds_dist_tab.COUNT LOOP
1345 IF t_funds_dist_tab(i).result_code = 'S' THEN
1346
1347 BEGIN
1348 SELECT aid.bc_event_id
1349 INTO l_bc_event_id
1350 FROM ap_invoice_distributions_all aid
1351 WHERE aid.invoice_distribution_id = t_funds_dist_tab(i).inv_distribution_id;
1352
1353 IF l_bc_event_id IS NOT NULL THEN
1354 SELECT xe.event_status_code
1355 INTO l_bc_event_status
1356 FROM xla_events xe
1357 WHERE xe.application_id = 200
1358 AND xe.event_id = l_bc_event_id;
1359
1360 IF l_bc_event_status <> 'P' THEN
1361
1362 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1363 l_log_msg := 'for the invoice_distribution_id' ||t_funds_dist_tab(i).inv_distribution_id||
1364 'the BC event_id '||l_bc_event_id||
1365 'has a status '||l_bc_event_status||
1366 'thus existing the loop for sanity check, AP will not update distributions'||
1367 'to encumbered';
1368 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1369 l_procedure_name, l_log_msg);
1370 END IF;
1371
1372 l_count_unproc := l_count_unproc + 1;
1373 exit;
1374 END IF;
1375 END IF;
1376
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 NULL;
1380 END;
1381 END IF;
1382 END LOOP;
1383
1384 IF l_count_unproc = 0 THEN
1385
1386 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1387 l_log_msg := 'none of the BC events for the distributions returned as Successfully '||
1388 'encumbered by PSA were unprocessed';
1389 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1390 l_procedure_name, l_log_msg);
1391 END IF;
1392
1393
1394 FOR i IN 1..t_funds_dist_tab.COUNT LOOP
1395 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1396 l_log_msg := 'in the loop to update encumbrance flag' ||
1397 'for distribution table for distribution_id=' ||
1398 to_char(t_funds_dist_tab(i).inv_distribution_id);
1399 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1400 l_procedure_name, l_log_msg);
1401 END IF;
1402
1403 IF t_funds_dist_tab(i).result_code = 'S' THEN
1404 -- Bug 6695993 added additional where clause
1405 -- at the suggestion of the PSA team.
1406
1407 --Bug7153696 modified the below update to catter the self accessed tax invoices
1408 IF nvl(t_funds_dist_tab(i).SELF_ASSESSED_FLAG , 'N') = 'N' THEN
1409
1410 UPDATE ap_invoice_distributions_all aid
1411 SET aid.encumbered_flag = 'Y'
1412 WHERE aid.invoice_distribution_id = t_funds_dist_tab(i).inv_distribution_id
1413 AND aid.bc_event_id is not null;
1414
1415 ELSE
1416
1417 UPDATE ap_self_assessed_tax_dist_all sad
1418 SET sad.encumbered_flag = 'Y'
1419 WHERE sad.invoice_distribution_id = t_funds_dist_tab(i).inv_distribution_id
1420 AND sad.bc_event_id is not null;
1421
1422 END IF;
1423
1424 END IF;
1425
1426 END LOOP;
1427
1428 ELSE
1429 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1430 l_log_msg := 'PSA returned an incorrect status for atleast one distribution '||
1431 'setting up the variable for CANT FUNDS CHECK hold';
1432 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1433 l_procedure_name, l_log_msg);
1434 END IF;
1435
1436 l_cant_fundsck_exists := 'Y';
1437 END IF;
1438
1439 END IF; -- end of dealing partial
1440
1441 ELSE
1442
1443 l_count_unproc := 0;
1444 FOR i IN 1..t_funds_dist_tab.COUNT LOOP
1445 BEGIN
1446 SELECT aid.bc_event_id
1447 INTO l_bc_event_id
1448 FROM ap_invoice_distributions_all aid
1449 WHERE aid.invoice_distribution_id = t_funds_dist_tab(i).inv_distribution_id;
1450
1451 IF l_bc_event_id IS NOT NULL THEN
1452 SELECT xe.event_status_code
1453 INTO l_bc_event_status
1454 FROM xla_events xe
1455 WHERE xe.application_id = 200
1456 AND xe.event_id = l_bc_event_id;
1457
1458 IF l_bc_event_status <> 'P' THEN
1459
1460 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1461 l_log_msg := 'for the invoice_distribution_id' ||t_funds_dist_tab(i).inv_distribution_id||
1462 'PSA returned a status code ' ||t_funds_dist_tab(i).result_code||
1463 'but the BC event_id '||l_bc_event_id||
1464 'has a status '||l_bc_event_status||
1465 'thus existing the loop for sanity check, AP will not update distributions'||
1466 'to encumbered';
1467
1468 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1469 l_procedure_name, l_log_msg);
1470 END IF;
1471
1472 l_count_unproc := l_count_unproc + 1;
1473 exit;
1474 END IF;
1475 END IF;
1476 EXCEPTION
1477 WHEN OTHERS THEN
1478 NULL;
1479 END;
1480 END LOOP;
1481
1482 /*-----------------------------------------------------------------+
1483 | Step 6.2 - Funds Reserve success for whole invoice |
1484 | We need to do clean up - update the invoice |
1485 | distributions packetid and encumbered flag |
1486 | should be SUCCESS and ADVISORY |
1487 +-----------------------------------------------------------------*/
1488 IF l_count_unproc = 0 THEN
1489
1490 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1491 l_log_msg := 'none of the BC events for the distributions returned as Successfully '||
1492 'encumbered by PSA were unprocessed';
1493 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1494 l_procedure_name, l_log_msg);
1495 END IF;
1496
1497 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1498 l_log_msg := 'Step 6.2 - funds reserve is done fully' ||
1499 ' and process sucess return code =' || l_return_code ;
1500 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1501 l_procedure_name, l_log_msg);
1502 END IF;
1503
1504 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1505 l_log_msg := 'number of distributions get funds reserved=' ||
1506 to_char(t_funds_dist_tab.COUNT);
1507 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1508 l_procedure_name, l_log_msg);
1509 END IF;
1510
1511 BEGIN
1512 FOR i IN 1..t_funds_dist_tab.COUNT LOOP
1513 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1514 l_log_msg := 'update encumbered flag for distribution id=' ||
1515 to_char(t_funds_dist_tab(i).inv_distribution_id);
1516 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1517 l_procedure_name, l_log_msg);
1518 END IF;
1519
1520 -- Bug 6695993 added additional where clause
1521 -- at the suggestion of the PSA team.
1522
1523 --Bug7153696 modified the below update to catter the self accessed tax invoices
1524 IF nvl(t_funds_dist_tab(i).SELF_ASSESSED_FLAG , 'N') = 'N' THEN
1525
1526 UPDATE ap_invoice_distributions_all aid
1527 SET aid.encumbered_flag = 'Y'
1528 WHERE aid.invoice_distribution_id = t_funds_dist_tab(i).inv_distribution_id
1529 AND aid.bc_event_id is not null;
1530
1531 ELSE
1532
1533 UPDATE ap_self_assessed_tax_dist_all sad
1534 SET sad.encumbered_flag = 'Y'
1535 WHERE sad.invoice_distribution_id = t_funds_dist_tab(i).inv_distribution_id
1536 AND sad.bc_event_id is not null;
1537
1538 END IF;
1539 END LOOP;
1540
1541 END;
1542
1543 ELSE
1544 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1545 l_log_msg := 'PSA returned an incorrect status for atleast one distribution '||
1546 'setting up the variable for CANT FUNDS CHECK hold';
1547 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1548 l_procedure_name, l_log_msg);
1549 END IF;
1550
1551 l_cant_fundsck_exists := 'Y';
1552 END IF;
1553
1554 END IF; -- check Funds Reservation Passed --
1555
1556
1557 p_funds_return_code := l_return_code;
1558
1559 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1560 l_log_msg := 'p_funds_return_code out param is set' ||
1561 'p_funds_return_code = ' || l_return_code ;
1562 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1563 l_procedure_name, l_log_msg);
1564 END IF;
1565
1566 /*-----------------------------------------------------------------+
1567 | Step 7 - Process Hold if insufficient funds hold exists |
1568 +-----------------------------------------------------------------*/
1569
1570 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
1571 l_log_msg := 'step 7 - process hold if exists';
1572 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
1573 l_procedure_name, l_log_msg);
1574 END IF;
1575
1576
1577 AP_APPROVAL_PKG.Process_Inv_Hold_Status(
1578 p_invoice_id,
1579 null,
1580 null,
1581 'INSUFFICIENT FUNDS',
1582 l_insuff_funds_exists,
1583 null,
1584 p_system_user,
1585 p_holds,
1586 p_hold_count,
1587 p_release_count,
1588 l_curr_calling_sequence);
1589 --Bug 9136390 Starts
1590 /*----------------------------------------------------------------------+
1591 | Step 7.1 - Process Hold if PSA Accounging fails hold exists |
1592 +------------------------------------------------------------------------*/
1593 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
1594 l_log_msg := 'step 7.1 - process PSA accoutning hold if exists';
1595 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
1596 l_procedure_name, l_log_msg);
1597 END IF;
1598 AP_APPROVAL_PKG.Process_Inv_Hold_Status(
1599 p_invoice_id,
1600 null,
1601 null,
1602 'Encumbrance Acctg Fail',
1603 l_enc_acctg_fail_exists,
1604 null,
1605 p_system_user,
1606 p_holds,
1607 p_hold_count,
1608 p_release_count,
1609 l_curr_calling_sequence);
1610 --Bug 9136390 Ends
1611
1612 /*-----------------------------------------------------------------+
1613 | Step 8 - Process Hold if can not do funds check hold exists |
1614 +-----------------------------------------------------------------*/
1615
1616 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
1617 l_log_msg := 'Step 8 - put CANT FUNDS CHECK';
1618 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
1619 l_procedure_name, l_log_msg);
1620 END IF;
1621
1622
1623 AP_APPROVAL_PKG.Process_Inv_Hold_Status(
1624 p_invoice_id,
1625 null,
1626 null,
1627 'CANT FUNDS CHECK',
1628 l_cant_fundsck_exists,
1629 null,
1630 p_system_user,
1631 p_holds,
1632 p_hold_count,
1633 p_release_count,
1634 l_curr_calling_sequence);
1635 ELSE
1636
1637 /*-----------------------------------------------------------------+
1638 | NO distribution needs to be funds checked or reserved. |
1639 | Bug 9168747 Starts |
1640 | and releasing any existing CANT FUNDS CHECK hold. |
1641 +-----------------------------------------------------------------*/
1642
1643 AP_APPROVAL_PKG.Release_fund_holds
1644 (P_Invoice_ID => p_Invoice_ID,
1645 p_system_user => p_system_user,
1646 p_calling_mode => 'NO DIST IN TAB', --bug11659334
1647 p_holds => p_holds,
1648 p_holds_count => p_hold_count,
1649 p_release_count => p_release_count,
1650 p_calling_sequence => l_curr_calling_sequence);
1651
1652
1653 /* removing the code added as apart of bugfix :9168747 , as fix handled through 11659334 */
1654
1655 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1656 l_log_msg := 'Step 3 - no Call of psa_ap_bc_pvt.Create_Events' ||
1657 'distribution cursor count = 0';
1658 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
1659 l_procedure_name, l_log_msg);
1660 END IF;
1661
1662 END IF;
1663
1664 ELSE
1665
1666 /*-----------------------------------------------------------------+
1667 | Encumbrance accounting option is turned on |
1668 +-----------------------------------------------------------------*/
1669 NULL;
1670
1671 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
1672 l_log_msg := 'encumbered flag is not enabled for the OU';
1673 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
1674 l_procedure_name, l_log_msg);
1675 END IF;
1676
1677 END IF; -- Encumbrance Enabled --
1678
1679
1680 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
1681 l_log_msg := 'End of '|| l_procedure_name;
1682 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
1683 l_procedure_name, l_log_msg);
1684 END IF;
1685
1686 EXCEPTION
1687 WHEN PSA_API_EXCEPTION THEN
1688 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1689 l_log_msg := 'Encountered an Exception in the PSA api, inside the exception block';
1690 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1691 END IF;
1692
1693 AP_APPROVAL_PKG.Process_Inv_Hold_Status(
1694 p_invoice_id,
1695 null,
1696 null,
1697 'CANT FUNDS CHECK',
1698 l_cant_fundsck_exists,
1699 null,
1700 p_system_user,
1701 p_holds,
1702 p_hold_count,
1703 p_release_count,
1704 l_curr_calling_sequence);
1705
1706 WHEN OTHERS THEN
1707 IF (SQLCODE <> -20001) THEN
1708 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1709 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1710 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1711 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1712 'Invoice_id = '|| to_char(p_invoice_id)
1713 ||' Set of books id = '||to_char(p_set_of_books_id)
1714 ||' System user = '||to_char(p_system_user));
1715 END IF;
1716 APP_EXCEPTION.RAISE_EXCEPTION;
1717 END Funds_Reserve;
1718
1719
1720 /*=============================================================================
1721 | PUBLIC PROCEDURE GET_ERV_CCID
1722 |
1723 | DESCRIPTION
1724 | Procedure to retrieve exchange rate variance ccid depending on the po
1725 | distribution destination type. If the destination type is EXPENSE,
1726 | erv_ccid equals to po distribution variance ccid or distribution ccid
1727 | depends on the accrue_on_receipt_flag value. If the destination
1728 | type is INVENDTORY, the erv_ccid depends on whether it is a gain or
1729 | loss to be assigned to the system level exchange rate variance
1730 | gain/loss ccid.
1731 |
1732 | PARAMETERS
1733 | p_chart_of_account_id: Chart of Accounts Id
1734 | p_sys_xrate_gain_ccid: System level Exchange Rate Variance Gain Ccid
1735 | p_sys_xrate_loss_ccid: System level Exchange Rate Variance Loss Ccid
1736 | p_dist_ccid: Invoice Distribution Line Ccid
1737 | p_expense_ccid: PO Distribution Expense Ccid
1738 | p_variance_ccid: PO Distribution Variance Ccid
1739 | p_destination_type: PO Distribution Destination Type
1740 | p_price_var_ccid: Variable to contain the invoice price variance ccid
1741 | that is determined by the po distribution
1742 | destination type.
1743 | p_erv: Variable to contain the exchange rate variacne calculated by
1744 | the procedure.
1745 | p_erv_ccid: Variable to contains the exchange rate variance ccid that
1746 | is determined by the po distribution destination type and
1747 | if automatic offsets is on or not.
1748 | p_calling_sequence: Debugging string to indicate path of module calls
1749 | to be printed out NOCOPY upon error.
1750 |
1751 | NOTE
1752 |
1753 | MODIFICATION HISTORY
1754 | Date Author Description of Change
1755 |
1756 *==========================================================================*/
1757
1758 PROCEDURE GET_ERV_CCID(
1759 p_sys_xrate_gain_ccid IN NUMBER,
1760 p_sys_xrate_loss_ccid IN NUMBER,
1761 p_dist_ccid IN NUMBER,
1762 p_variance_ccid IN NUMBER,
1763 p_destination_type IN VARCHAR2,
1764 p_inv_distribution_id IN NUMBER,
1765 p_related_id IN NUMBER,
1766 p_erv IN NUMBER,
1767 p_erv_ccid IN OUT NOCOPY NUMBER,
1768 p_calling_sequence IN VARCHAR2) IS
1769
1770 l_debug_loc VARCHAR2(2000) := 'GET_ERV_CCID';
1771 l_curr_calling_sequence VARCHAR2(2000);
1772 l_debug_info VARCHAR2(2000);
1773
1774
1775 -- Logging Infra:
1776 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_ERV_CCID';
1777 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1778
1779
1780
1781 BEGIN
1782
1783 -- Update the calling sequence --
1784 l_curr_calling_sequence := 'AP_FUNDS_CONTROL_PKG.'||l_debug_loc||
1785 '<-'||p_calling_sequence;
1786
1787
1788 /*-----------------------------------------------------------------+
1789 | Determine erv_ccid - if existing no need to overlay |
1790 | Just query, otherwise build the account |
1791 +-----------------------------------------------------------------*/
1792
1793 -- Logging Infra: Procedure level
1794 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
1795 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1796 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
1797 l_procedure_name, l_log_msg);
1798 END IF;
1799
1800
1801 IF (p_related_id is not null and
1802 p_inv_distribution_id = p_related_id and
1803 nvl(p_erv, 0 ) <> 0 ) THEN
1804
1805 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1806 l_log_msg := 'GET_ERV_CCID - Query the exising erv ccid';
1807 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1808 END IF;
1809
1810 BEGIN
1811 SELECT D.dist_code_combination_id
1812 INTO p_erv_ccid
1813 FROM ap_invoice_distributions D
1814 WHERE D.related_id = p_related_id
1815 AND D.line_type_lookup_code = 'ERV';
1816 EXCEPTION
1817 WHEN NO_DATA_FOUND THEN
1818 p_erv_ccid := -1;
1819 END;
1820 END IF;
1821
1822 IF ( nvl( p_erv_ccid, -1) = -1 ) THEN
1823
1824 IF (g_debug_mode = 'Y') THEN
1825 l_debug_info := 'GET_ERV_CCID - try to find erv ccid';
1826 AP_Debug_Pkg.Print(g_debug_mode, l_debug_info);
1827 END IF;
1828
1829 IF ( nvl(p_erv,0 ) <> 0 ) THEN
1830
1831 IF (p_destination_type = 'EXPENSE') THEN
1832
1833 ---------------------------------------------------------------
1834 -- expense line, so erv account should equal expense account --
1835 -- bug 1666428 states that this should always be equal to the--
1836 -- dist_ccid on the invoice distribution making the change --
1837 -- Fix for 2122441 commented above statement and wrote
1838 -- the below one,the FundsCntrl_Inv_Dist_Cur cursor takes
1839 -- care that in case of accure on receipt is Y then the
1840 -- charge account of PO is taken
1841 ---------------------------------------------------------------
1842
1843 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
1844 l_log_msg := 'GET_ERV_CCID - expense item ';
1845 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||l_procedure_name, l_log_msg);
1846 END IF;
1847
1848 p_erv_ccid := p_variance_ccid ;
1849
1850 ELSE
1851
1852 ---------------------------------------------------------------------
1853 -- 1. if it is not expense destination type, we will populate the line
1854 -- with its related distribution line ccid. please note, this could
1855 -- could be either the accrual account or expense account. Due to
1856 -- checking 11i behavior, it is as above. We have decide to use
1857 -- use accrual or expense account pending on the "accrual on
1858 -- receipt option.
1859 -- 2. when the item destination type is "inventory", we still need
1860 -- flex build the distribution account with system rate gain/loss
1861 -- account depending on automatic offset value. This operation now
1862 -- is moved to SLA accounting rule
1863 -- 3. please see the changes detail in bug 5545704
1864 ---------------------------------------------------------------------
1865
1866 p_erv_ccid := p_dist_ccid;
1867
1868 -- the following code is comment out for bug 5545704
1869 -- put is here for future reference.
1870 /* IF ( p_erv < 0) THEN
1871 -------------------------
1872 -- exchange rate gain --
1873 -------------------------
1874 p_erv_ccid := p_sys_xrate_gain_ccid;
1875
1876 ELSE
1877 ------------------------
1878 -- exchange rate loss --
1879 ------------------------
1880 p_erv_ccid := p_sys_xrate_loss_ccid;
1881
1882 END IF; */
1883
1884 END IF; -- destination_type = 'EXPENSE' --
1885 END IF; -- end of p_erv <> 0 check
1886 END IF; -- end of p_erv_ccid check
1887
1888 -- Logging Infra: Procedure level
1889 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
1890 l_log_msg := 'End of procedure '|| l_procedure_name;
1891 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
1892 l_procedure_name, l_log_msg);
1893 END IF;
1894 EXCEPTION
1895 WHEN OTHERS THEN
1896 IF (SQLCODE <> -20001) THEN
1897 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1898 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1899 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1900 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1901 END IF;
1902 APP_EXCEPTION.RAISE_EXCEPTION;
1903 END GET_ERV_CCID;
1904
1905
1906 /*=============================================================================
1907 | PUBLIC PROCEDURE CALC_QV
1908 |
1909 | DESCRIPTION
1910 | Procedure to calculate the quantity variance and base quantity
1911 | variance and also return the invoice distribution line number
1912 | and parent line number that the quantity variances should
1913 | be applied to.
1914 |
1915 | PARAMETERS
1916 | p_invoice_id: Invoice Id
1917 | p_po_dist_id: Po Distribution Id that the invoice is matched to
1918 | p_inv_currency_code: Invoice Currency Code
1919 | p_base_currency_code: Base Currency Code
1920 | p_po_price: Po Price
1921 | p_po_qty: Po Quantity
1922 | p_match_option:
1923 | p_rtxn_uom:
1924 | p_po_uom:
1925 | p_item_id:
1926 | p_qv: Variable to contain the quantity variance of the invoice to be
1927 | calculated by the procedure
1928 | p_bqv: Variable to contain the base quantity variance of the invoice to
1929 | be calculated by the procedure
1930 |
1931 | p_update_line_num: Variable to contain the distribution parent line
1932 | number of the invoice that the qv should be
1933 | applied to
1934 | p_update_dist_num: Variable to contain the distribution line number
1935 | of the invoice that the qv should be applied to
1936 | p_calling_sequence: Debugging string to indicate path of module calls
1937 | to be printed out NOCOPY upon error
1938 |
1939 | NOTE
1940 |
1941 | MODIFICATION HISTORY
1942 | Date Author Description of Change
1943 |
1944 *==========================================================================*/
1945
1946 PROCEDURE Calc_QV(
1947 p_invoice_id IN NUMBER,
1948 p_po_dist_id IN NUMBER,
1949 p_inv_currency_code IN VARCHAR2,
1950 p_base_currency_code IN VARCHAR2,
1951 p_po_price IN NUMBER,
1952 p_po_qty IN NUMBER,
1953 p_match_option IN VARCHAR2,
1954 p_po_uom IN VARCHAR2,
1955 p_item_id IN NUMBER,
1956 p_qv IN OUT NOCOPY NUMBER,
1957 p_bqv IN OUT NOCOPY NUMBER,
1958 p_update_line_num IN OUT NOCOPY NUMBER,
1959 p_update_dist_num IN OUT NOCOPY NUMBER,
1960 p_calling_sequence IN VARCHAR2) IS
1961
1962 l_old_qty_var NUMBER;
1963 l_old_base_qty_var NUMBER;
1964 l_new_qty_var NUMBER;
1965 l_new_base_qty_var NUMBER;
1966 l_unapproved_qty NUMBER;
1967 l_unapproved_amt NUMBER;
1968 l_debug_loc VARCHAR2(2000) := 'Calc_QV';
1969 l_curr_calling_sequence VARCHAR2(2000);
1970 l_debug_info VARCHAR2(2000);
1971 l_rate NUMBER;
1972 l_accr_on_receipt_flag VARCHAR2(1);
1973 l_qty_received NUMBER;
1974
1975
1976 -- Logging Infra:
1977 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_QV';
1978 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1979
1980
1981 BEGIN
1982
1983 -- Logging Infra: Procedure level
1984 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
1985 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1986 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
1987 l_procedure_name, l_log_msg);
1988 END IF;
1989
1990 l_curr_calling_sequence := 'AP_FUNDS_CONTROL_PKG.'||l_debug_loc||
1991 '<-'||p_calling_sequence;
1992
1993 -----------------------------------------------------------------------
1994 -- new_qty_variance = (inv_qty - po_qty)*po_price - qty_variance --
1995 -- where inv_qty = inv_qty for all approved invoice distributions --
1996 -- matched to current po_distribution + --
1997 -- inv_qty of current invoice. --
1998
1999 -- Change during coding of receipt matching project
2000 -- The QV was calulated and stored even when accrue_on_receipt = 'Y'
2001 -- but was not being used anywhere else , when accrue_on_receipt = 'N'
2002 -- we encumber the extra qty var between the Invoice and the PO, and this
2003 -- is the same whether we match to PO or Receipt.
2004 -- therefore changed the following in the select statement
2005 -- greatest(to_number(p_po_qty),
2006 -- decode(pd.accrue_on_receipt_flag,
2007 -- 'Y',poll.quantity_received,
2008 -- p_po_qty)))
2009 -- to just p_po_qty
2010 -----------------------------------------------------------------------
2011
2012 -- If matched to the receipt the UOM may be different, so we need to
2013 -- convert the quantity_invoiced to the PO UOM before multiplying
2014 -- with the PO price
2015
2016 -----------------------------------------------------------------------
2017 -- Bug 2455810 Code modified by MSWAMINA on 11-July-02
2018 --
2019 -- The Select statement below would identify the cumulative QV
2020 -- for the invoices matched to the one po_distribution_id.
2021 -- If the Invoice is matched to receipt or in cases like ERS, and if it
2022 -- has TAX distributions, the TAX distributions will not have the
2023 -- PO attributes like UOM, etc.
2024 -- When the PO API is called for the Tax distribution it will fail.
2025 --
2026 -- As discussed with Bob, Jayanta on 11-July-02, Added a NVL to the
2027 -- D.matched_uom_lookup_code to the p_po_uom itself. By this way,
2028 -- the PO API to get the conversion rate will never fail.
2029 ----------------------------------------------------------------------
2030
2031 -- bug11840315, included corrected_quantity in the SQL
2032 select decode(p_inv_currency_code,
2033 p_base_currency_code,1,
2034 nvl(PD.rate,1)), -- l_rate
2035 -- l_accrue_on_receipt_flag
2036 PD.accrue_on_receipt_flag,
2037 -- l_quantity_received,
2038 POLL.quantity_received,
2039 -- old_qty_variance
2040 sum(nvl(D.quantity_variance,0)),
2041 -- 0ld_base_qty_variance
2042 decode(p_inv_currency_code,
2043 p_base_currency_code,1,
2044 nvl(PD.rate,1)) * sum(nvl(d.quantity_variance,0)),
2045 --new_qty_variance
2046 (((sum(decode(d.match_status_flag,
2047 'A',nvl(decode(p_match_option,
2048 'R', (qty.quantity_invoiced *
2049 po_uom_s.po_uom_convert(
2050 nvl(d.matched_uom_lookup_code,
2051 p_po_uom), p_po_uom
2052 ,p_item_id)),
2053 qty.quantity_invoiced), 0),
2054 decode(d.invoice_id, p_invoice_id,
2055 nvl(decode(p_match_option,
2056 'R',
2057 (qty.quantity_invoiced *
2058 po_uom_s.po_uom_convert(
2059 nvl(d.matched_uom_lookup_code, p_po_uom), p_po_uom, p_item_id)),
2060 qty.quantity_invoiced), 0),
2061 decode(d.match_status_flag, 'A', 0,
2062 nvl(decode(p_match_option,
2063 'R',
2064 (qty.quantity_invoiced *
2065 po_uom_s.po_uom_convert(
2066 nvl(d.matched_uom_lookup_code, p_po_uom), p_po_uom, p_item_id)),
2067 qty.quantity_invoiced), 0)))
2068 )) - p_po_qty ) * p_po_price)
2069 - sum(nvl(d.quantity_variance,0))),
2070 -- new_base_qty_variance
2071 decode(p_inv_currency_code,
2072 p_base_currency_code,1,
2073 nvl(PD.rate,1)) *
2074 (((sum(decode(d.match_status_flag,
2075 'A',nvl(decode(p_match_option,
2076 'R',(qty.quantity_invoiced *
2077 po_uom_s.po_uom_convert(
2078 nvl(d.matched_uom_lookup_code
2079 ,p_po_uom), p_po_uom
2080 ,p_item_id)),
2081 qty.quantity_invoiced),
2082 0),
2083 decode(d.invoice_id, p_invoice_id,
2084 nvl(decode(p_match_option,
2085 'R',
2086 (qty.quantity_invoiced *
2087 po_uom_s.po_uom_convert(
2088 nvl(d.matched_uom_lookup_code, p_po_uom), p_po_uom, p_item_id)),
2089 qty.quantity_invoiced), 0),
2090 decode(d.match_status_flag, 'A', 0,
2091 nvl(decode(p_match_option,
2092 'R',
2093 (qty.quantity_invoiced *
2094 po_uom_s.po_uom_convert(
2095 nvl(d.matched_uom_lookup_code, p_po_uom), p_po_uom, p_item_id)),
2096 qty.quantity_invoiced), 0)))
2097 )) - p_po_qty ) * p_po_price)
2098 - sum(nvl(d.quantity_variance,0))),
2099 -- l_unapproved_qty
2100 sum(decode(d.invoice_id, p_invoice_id,
2101 decode(match_status_flag,
2102 'A',0,
2103 nvl(decode(p_match_option, 'R', (qty.quantity_invoiced *
2104 po_uom_s.po_uom_convert(
2105 nvl(d.matched_uom_lookup_code,
2106 p_po_uom), p_po_uom, p_item_id))
2107 ,qty.quantity_invoiced),0)),
2108 0)),
2109 -- l_unapproved_amount
2110 (p_po_price * sum(decode(d.invoice_id, p_invoice_id,
2111 decode(match_status_flag,
2112 'A',0,
2113 nvl(decode(p_match_option,'R',
2114 (qty.quantity_invoiced *
2115 po_uom_s.po_uom_convert(
2116 nvl(d.matched_uom_lookup_code,
2117 p_po_uom), p_po_uom
2118 , p_item_id))
2119 ,qty.quantity_invoiced),0)),
2120 0)) )
2121 into l_rate, --bug:1826323
2122 l_accr_on_receipt_flag, --bug:1826323
2123 l_qty_received, --bug:1826323
2124 l_old_qty_var,
2125 l_old_base_qty_var,
2126 l_new_qty_var,
2127 l_new_base_qty_var,
2128 l_unapproved_qty,
2129 l_unapproved_amt
2130 from ap_invoice_distributions d,
2131 (SELECT d1.Invoice_distribution_id,
2132 d1.Invoice_ID,
2133 decode(d1.dist_match_type,
2134 'PRICE_CORRECTION', 0,
2135 'AMOUNT_CORRECTION', 0,
2136 'ITEM_TO_SERVICE_PO', 0,
2137 'ITEM_TO_SERVICE_RECEIPT', 0,
2138 NVL(d1.corrected_quantity, 0) +
2139 NVL(d1.quantity_invoiced,0)
2140 ) quantity_invoiced
2141 FROM ap_invoice_distributions_all d1) qty,
2142 po_distributions pd,
2143 po_line_locations poll
2144 where pd.po_distribution_id = d.po_distribution_id
2145 and d.po_distribution_id = p_po_dist_id
2146 and d.line_type_lookup_code NOT IN ('NONREC_TAX','TRV','TIPV')
2147 and d.invoice_distribution_id = qty.invoice_distribution_id
2148 and d.invoice_id = qty.invoice_id
2149 and poll.line_location_id = pd.line_location_id
2150 group by decode(p_inv_currency_code,
2151 p_base_currency_code,1,
2152 nvl(PD.rate,1)),
2153 pd.accrue_on_receipt_flag,
2154 poll.quantity_received;
2155
2156 /*-----------------------------------------------------------------+
2157 | round all amounts |
2158 +-----------------------------------------------------------------*/
2159
2160 l_old_qty_var := AP_UTILITIES_PKG.ap_round_currency(l_old_qty_var,
2161 p_inv_currency_code);
2162
2163 l_old_base_qty_var := AP_UTILITIES_PKG.ap_round_currency(l_old_base_qty_var,
2164 p_base_currency_code);
2165
2166 l_new_qty_var := AP_UTILITIES_PKG.ap_round_currency(l_new_qty_var,
2167 p_inv_currency_code);
2168
2169 l_new_base_qty_var := AP_UTILITIES_PKG.ap_round_currency(l_new_base_qty_var,
2170 p_base_currency_code);
2171 l_unapproved_amt := AP_UTILITIES_PKG.ap_round_currency(l_unapproved_amt,
2172 p_inv_currency_code);
2173
2174 p_qv := l_new_qty_var;
2175 p_bqv := l_new_base_qty_var;
2176
2177
2178 IF ((l_unapproved_qty < 0) AND (l_old_qty_var > 0)) THEN
2179
2180 -----------------------------------------------------------------------
2181 -- Aggregate quantity_invoiced for this invoice is negative, which --
2182 -- means that reversals exceed any new positive quantity --
2183 -- distributions. Book it to the distribution with the LOWEST --
2184 -- unapproved quantity. --
2185 -- Note: We only book a negative quantity variance if there has --
2186 -- been a reversal AND there was an existing positive --
2187 -- quantity variance. --
2188 -----------------------------------------------------------------------
2189
2190 ---------------------------------------------------
2191 --Do not allow total qty variance to be negative --
2192 ---------------------------------------------------
2193
2194 IF (l_unapproved_amt < -l_old_qty_var) THEN
2195
2196 -----------------------------------------------------------------
2197 -- Book a qv that is the additive inverse of total approved qv --
2198 -----------------------------------------------------------------
2199
2200 p_qv := -l_old_qty_var;
2201 p_bqv := -l_old_base_qty_var;
2202
2203 END IF;
2204
2205 ----------------------------------------------------------------------
2206 -- Retrieve the dist_line_num with the SMALLEST unapproved quantity --
2207 ----------------------------------------------------------------------
2208 l_debug_info := 'CALC_QV - find dist line with min qty for ' ||
2209 'negative qty_variance';
2210
2211 -- bug11840315, included corrected_quantity in the sql below
2212 SELECT line_number,
2213 dist_line_number
2214 INTO p_update_line_num,
2215 p_update_dist_num
2216 FROM (SELECT nvl(invoice_line_number,0) line_number,
2217 nvl(distribution_line_number,0) dist_line_number,
2218 row_number() OVER (ORDER BY invoice_line_number,
2219 distribution_line_number) R
2220 FROM ap_invoice_distributions_all
2221 WHERE invoice_id = p_invoice_id
2222 AND po_distribution_id = p_po_dist_id
2223 AND nvl(encumbered_flag,'N') IN ('N','H','P')
2224 AND (match_status_flag IS NULL OR match_status_flag <> 'A')
2225 AND dist_match_type NOT IN ('PRICE_CORRECTION',
2226 'AMOUNT_CORRECTION',
2227 'ITEM_TO_SERVICE_PO',
2228 'ITEM_TO_SERVICE_RECEIPT')
2229 AND (NVL(corrected_quantity, 0) + NVL(quantity_invoiced,0)) =
2230 (SELECT min(NVL(corrected_quantity, 0) + NVL(quantity_invoiced,0))
2231 FROM ap_invoice_distributions_all
2232 WHERE invoice_id = p_invoice_id
2233 AND po_distribution_id = p_po_dist_id
2234 AND nvl(encumbered_flag,'N') IN ('N','H','P')
2235 AND (match_status_flag IS NULL OR match_status_flag <> 'A')
2236 AND dist_match_type NOT IN ('PRICE_CORRECTION',
2237 'AMOUNT_CORRECTION',
2238 'ITEM_TO_SERVICE_PO',
2239 'ITEM_TO_SERVICE_RECEIPT')
2240 )
2241 )
2242 WHERE R = 1;
2243 -- bug 7458713: modify end
2244
2245 ELSIF (l_new_qty_var > 0) THEN
2246
2247 --------------------------------------------------------------
2248 -- If new_qty_variance > 0 then there are positive quantity --
2249 -- variances. Book a positive-quantity variance on the --
2250 -- distribution with the LARGEST unapproved quantity --
2251 --------------------------------------------------------------
2252 l_debug_info := 'CALC_QV - find dist line with max qty for ' ||
2253 'positive qty_variance';
2254
2255 -- bug11840315, included corrected_quantity in the sql below. Also
2256 -- changed the SQL logically to ensure a valid line/dist combination
2257 --
2258 SELECT line_number,
2259 dist_line_number
2260 INTO p_update_line_num,
2261 p_update_dist_num
2262 FROM (SELECT nvl(invoice_line_number,0) line_number,
2263 nvl(distribution_line_number,0) dist_line_number,
2264 row_number() OVER (ORDER BY invoice_line_number,
2265 distribution_line_number) R
2266 FROM ap_invoice_distributions_all
2267 WHERE invoice_id = p_invoice_id
2268 AND po_distribution_id = p_po_dist_id
2269 AND nvl(encumbered_flag,'N') IN ('N','H','P')
2270 AND (match_status_flag IS NULL OR match_status_flag <> 'A')
2271 AND dist_match_type NOT IN ('PRICE_CORRECTION',
2272 'AMOUNT_CORRECTION',
2273 'ITEM_TO_SERVICE_PO',
2274 'ITEM_TO_SERVICE_RECEIPT')
2275 AND (NVL(corrected_quantity, 0) + NVL(quantity_invoiced,0)) =
2276 (SELECT max(NVL(corrected_quantity, 0) + NVL(quantity_invoiced,0))
2277 FROM ap_invoice_distributions_all
2278 WHERE invoice_id = p_invoice_id
2279 AND po_distribution_id = p_po_dist_id
2280 AND nvl(encumbered_flag,'N') IN ('N','H','P')
2281 AND (match_status_flag IS NULL OR match_status_flag <> 'A')
2282 AND dist_match_type NOT IN ('PRICE_CORRECTION',
2283 'AMOUNT_CORRECTION',
2284 'ITEM_TO_SERVICE_PO',
2285 'ITEM_TO_SERVICE_RECEIPT')
2286 )
2287 )
2288 WHERE R = 1;
2289
2290 ELSE
2291 -------------------------------------------------------
2292 -- No quantity variance for this invoice and PO dist --
2293 -------------------------------------------------------
2294
2295 l_debug_info := 'CALC_QV - NO quantity variance exists';
2296 p_qv := 0;
2297 p_bqv := 0;
2298
2299 END IF;
2300
2301 -- Logging Infra: Procedure level
2302 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
2303 l_log_msg := 'End of procedure '|| l_procedure_name;
2304 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
2305 l_procedure_name, l_log_msg);
2306 END IF;
2307
2308 EXCEPTION
2309 WHEN OTHERS THEN
2310 IF (SQLCODE <> -20001) THEN
2311 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2312 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2313 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2314 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2315 'Invoice_id = '|| to_char(p_invoice_id)
2316 ||', Po_dist_id = '|| to_char(p_po_dist_id)
2317 ||', Inv_currency_code = '|| p_inv_currency_code
2318 ||', Po_price = '|| to_char(p_po_price)
2319 ||', Po_qty = '|| to_char(p_po_qty));
2320 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2321 END IF;
2322 APP_EXCEPTION.RAISE_EXCEPTION;
2323 END Calc_QV;
2324
2325 /*=============================================================================
2326 | PUBLIC PROCEDURE CALC_AV
2327 |
2328 | DESCRIPTION
2329 | Procedure to calculate the amount variance and base amount
2330 | variance and also return the invoice distribution line number
2331 | and parent line number that the amount variances should
2332 | be applied to.
2333 |
2334 | PARAMETERS
2335 | p_invoice_id: Invoice Id
2336 | p_po_dist_id: Po Distribution Id that the invoice is matched to
2337 | p_inv_currency_code: Invoice Currency Code
2338 | p_base_currency_code: Base Currency Code
2339 | p_po_amt: Po Amount
2340 | p_match_option:
2341 | p_rtxn_uom:
2342 | p_po_uom:
2343 | p_item_id:
2344 | p_av: Variable to contain the amount variance of the invoice to be
2345 | calculated by the procedure
2346 | p_bav: Variable to contain the base amount variance of the invoice to
2347 | be calculated by the procedure
2348 |
2349 | p_update_line_num: Variable to contain the distribution parent line
2350 | number of the invoice that the av should be
2351 | applied to
2352 | p_update_dist_num: Variable to contain the distribution line number
2353 | of the invoice that the av should be applied to
2354 | p_calling_sequence: Debugging string to indicate path of module calls
2355 | to be printed out NOCOPY upon error
2356 |
2357 | NOTE
2358 |
2359 | MODIFICATION HISTORY
2360 | Date Author Description of Change
2361 |
2362 *==========================================================================*/
2363
2364
2365 PROCEDURE Calc_AV(
2366 p_invoice_id IN NUMBER,
2367 p_po_dist_id IN NUMBER,
2368 p_inv_currency_code IN VARCHAR2,
2369 p_base_currency_code IN VARCHAR2,
2370 p_po_amt IN NUMBER,
2371 p_av IN OUT NOCOPY NUMBER,
2372 p_bav IN OUT NOCOPY NUMBER,
2373 p_update_line_num IN OUT NOCOPY NUMBER,
2374 p_update_dist_num IN OUT NOCOPY NUMBER,
2375 p_calling_sequence IN VARCHAR2) IS
2376
2377 l_old_amt_var NUMBER;
2378 l_old_base_amt_var NUMBER;
2379 l_new_amt_var NUMBER;
2380 l_new_base_amt_var NUMBER;
2381 l_unapproved_amt NUMBER;
2382 l_debug_loc VARCHAR2(2000) := 'Calc_AV';
2383 l_curr_calling_sequence VARCHAR2(2000);
2384 l_debug_info VARCHAR2(2000);
2385 l_rate NUMBER;
2386 l_accr_on_receipt_flag VARCHAR2(1);
2387 l_amt_received NUMBER;
2388
2389 -- Logging Infra:
2390 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_AV';
2391 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2392
2393 BEGIN
2394
2395 -- Update the calling sequence --
2396
2397 l_curr_calling_sequence := 'AP_FUNDS_CONTROL_PKG.'||l_debug_loc||
2398 '<-'||p_calling_sequence;
2399
2400 -- Logging Infra: Procedure level
2401 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
2402 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2403 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
2404 l_procedure_name, l_log_msg);
2405 END IF;
2406
2407 -----------------------------------------------------------------------
2408 -- new_amt_variance = (inv_amt - po_amt) - amt_variance --
2409 -- where inv_amt = inv_amt for all approved invoice distributions --
2410 -- matched to current po_distribution + --
2411 -- inv_amt of current invoice. --
2412
2413 -----------------------------------------------------------------------
2414
2415 select decode(p_inv_currency_code,
2416 p_base_currency_code,1,
2417 nvl(PD.rate,1)),
2418 pd.accrue_on_receipt_flag,
2419 poll.amount_received,
2420 -- old_amt_variance
2421 sum(nvl(d.amount_variance,0)),
2422 -- 0ld_base_amt_variance
2423 decode(p_inv_currency_code,
2424 p_base_currency_code,1,nvl(PD.rate,1))
2425 * sum(nvl(d.amount_variance,0)),
2426 --new_amt_variance
2427 ((sum (decode(d.match_status_flag,
2428 'A',nvl(d.amount,0),
2429 decode(d.invoice_id,
2430 p_invoice_id,nvl(d.amount,0),
2431 0)
2432 )
2433 ) - p_po_amt
2434 ) - sum(nvl(d.amount_variance,0))
2435 ),
2436 -- new_base_amt_variance
2437 decode(p_inv_currency_code,
2438 p_base_currency_code,1,nvl(PD.rate,1))
2439 *((sum(decode (d.match_status_flag,
2440 'A',nvl(d.amount,0),
2441 decode(d.invoice_id,
2442 p_invoice_id,nvl(d.amount,0),
2443 0)
2444 )
2445 )-p_po_amt
2446 ) - sum(nvl(d.amount_variance,0))
2447 ),
2448 -- l_unapproved_amount
2449 sum(decode(d.invoice_id,
2450 p_invoice_id,decode(match_status_flag,
2451 'A',0,nvl(d.amount,0)
2452 ),
2453 0)
2454 )
2455 into l_rate,
2456 l_accr_on_receipt_flag,
2457 l_amt_received,
2458 l_old_amt_var,
2459 l_old_base_amt_var,
2460 l_new_amt_var,
2461 l_new_base_amt_var,
2462 l_unapproved_amt
2463 from ap_invoice_distributions d,
2464 po_distributions pd,
2465 po_line_locations poll
2466 where pd.po_distribution_id = d.po_distribution_id
2467 and d.po_distribution_id = p_po_dist_id
2468 and poll.line_location_id = pd.line_location_id
2469 and d.line_type_lookup_code IN ('ITEM','ACCRUAL') --bugfix:3881673
2470 group by decode(p_inv_currency_code,
2471 p_base_currency_code,1,
2472 nvl(PD.rate,1)),
2473 pd.accrue_on_receipt_flag, poll.amount_received;
2474
2475 -- round all amounts
2476
2477 l_old_amt_var := AP_UTILITIES_PKG.ap_round_currency(l_old_amt_var, p_inv_currency_code);
2478
2479 l_old_base_amt_var := AP_UTILITIES_PKG.ap_round_currency(l_old_base_amt_var, p_base_currency_code);
2480
2481 l_new_amt_var := AP_UTILITIES_PKG.ap_round_currency(l_new_amt_var, p_inv_currency_code);
2482
2483 l_new_base_amt_var := AP_UTILITIES_PKG.ap_round_currency(l_new_base_amt_var, p_base_currency_code);
2484
2485 l_unapproved_amt := AP_UTILITIES_PKG.ap_round_currency(l_unapproved_amt, p_inv_currency_code);
2486
2487 p_av := l_new_amt_var;
2488 p_bav := l_new_base_amt_var;
2489
2490 IF ((l_unapproved_amt < 0) AND (l_old_amt_var > 0)) THEN
2491
2492 -----------------------------------------------------------------------
2493 -- Aggregate amount_invoiced for this invoice is negative, which --
2494 -- means that reversals exceed any new positive amount --
2495 -- distributions. Book it to the distribution with the LOWEST --
2496 -- unapproved amount. --
2497 -- Note: We only book a negative amount variance if there has --
2498 -- been a reversal AND there was an existing positive --
2499 -- amount variance. --
2500 -----------------------------------------------------------------------
2501
2502 ---------------------------------------------------
2503 --Do not allow total amt variance to be negative --
2504 ---------------------------------------------------
2505
2506 IF (l_unapproved_amt < -l_old_amt_var) THEN
2507
2508 -----------------------------------------------------------------
2509 -- Book a av that is the additive inverse of total approved av --
2510 -----------------------------------------------------------------
2511
2512 p_av := -l_old_amt_var;
2513 p_bav := -l_old_base_amt_var;
2514
2515 END IF;
2516
2517 ----------------------------------------------------------------------
2518 -- Retrieve the dist_line_num with the SMALLEST unapproved amount --
2519 ----------------------------------------------------------------------
2520
2521 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
2522 l_log_msg := 'CALC_AV - find dist line with min amt for ' ||
2523 'negative amt_variance';
2524 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
2525 l_procedure_name, l_log_msg);
2526 END IF;
2527
2528 select nvl(distribution_line_number,0),
2529 nvl(invoice_line_number,0)
2530 into p_update_dist_num,
2531 p_update_line_num
2532 from ap_invoice_distributions
2533 where (invoice_line_number, distribution_line_number) =
2534 (select nvl(min(invoice_line_number),0), nvl(min(distribution_line_number),0)
2535 from ap_invoice_distributions
2536 where invoice_id = p_invoice_id
2537 and po_distribution_id = p_po_dist_id
2538 and nvl(encumbered_flag,'N') in ('N','H','P')
2539 and (match_status_flag is null or
2540 match_status_flag <> 'A')
2541 and amount =
2542 (select min(amount)
2543 from ap_invoice_distributions
2544 where invoice_id = p_invoice_id
2545 and po_distribution_id = p_po_dist_id
2546 and nvl(encumbered_flag,'N') in ('N','H','P')
2547 and (match_status_flag is null or
2548 match_status_flag <> 'A')) )
2549 and (match_status_flag is null or match_status_flag <> 'A')
2550 and invoice_id = p_invoice_id
2551 and po_distribution_id = p_po_dist_id
2552 and rownum < 2;
2553
2554 ELSIF (l_new_amt_var > 0) THEN
2555
2556 --------------------------------------------------------------
2557 -- If new_amt_variance > 0 then there are positive amount --
2558 -- variances. Book a positive-amount variance on the --
2559 -- distribution with the LARGEST unapprived amount --
2560 --------------------------------------------------------------
2561 l_debug_info := 'CALC_AV - find dist line with max amt for ' ||
2562 'positive amt_variance';
2563
2564 select nvl(distribution_line_number,0),
2565 invoice_line_number
2566 into p_update_dist_num,
2567 p_update_line_num
2568 from ap_invoice_distributions
2569 where (invoice_line_number, distribution_line_number) =
2570 (select nvl(min(invoice_line_number),0), nvl(min(distribution_line_number),0)
2571 from ap_invoice_distributions
2572 where invoice_id = p_invoice_id
2573 and po_distribution_id = p_po_dist_id
2574 and nvl(encumbered_flag,'N') in ('N','H','P')
2575 and (match_status_flag is null or
2576 match_status_flag <> 'A')
2577 and amount =
2578 (select max(amount)
2579 from ap_invoice_distributions
2580 where invoice_id = p_invoice_id
2581 and po_distribution_id = p_po_dist_id
2582 and nvl(encumbered_flag,'N') in ('N','H','P')
2583 and (match_status_flag is null or
2584 match_status_flag <> 'A')) )
2585 and (match_status_flag is null or match_status_flag <> 'A')
2586 and invoice_id = p_invoice_id
2587 and po_distribution_id = p_po_dist_id
2588 and rownum < 2;
2589
2590 ELSE
2591 -------------------------------------------------------
2592 -- No amount variance for this invoice and PO dist --
2593 -------------------------------------------------------
2594
2595 p_av := 0;
2596 p_bav := 0;
2597
2598 END IF;
2599
2600 -- Logging Infra: Procedure level
2601 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
2602 l_log_msg := 'End of procedure '|| l_procedure_name;
2603 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
2604 l_procedure_name, l_log_msg);
2605 END IF;
2606
2607 EXCEPTION
2608 WHEN OTHERS THEN
2609 IF (SQLCODE <> -20001) THEN
2610 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2611 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2612 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
2613 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2614 'Invoice_id = '|| to_char(p_invoice_id)
2615 ||', Po_dist_id = '|| to_char(p_po_dist_id)
2616 ||', Inv_currency_code = '|| p_inv_currency_code
2617 ||', Po_amt = '|| to_char(p_po_amt));
2618 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2619 END IF;
2620 APP_EXCEPTION.RAISE_EXCEPTION;
2621 END Calc_AV;
2622
2623 /*=============================================================================
2624 | PUBLIC PROCEDURE Funds_Check
2625 |
2626 | DESCRIPTION
2627 | Procedure to perform fundschecking on a whole invoice if p_line_num
2628 | and p_dist_line_num are null or a particular invoice line or invoice
2629 | distribution line if p_dist_line_num is provided
2630 |
2631 | PARAMETERS
2632 | p_invoice_id: Invoice_Id to perform funds_checking on
2633 | p_line_num: Invoice Line Number represents the parent line of
2634 | distribution
2635 | p_dist_line_num: Invoice Distribution Line Number if populated,
2636 | tells the api to fundscheck a particular invoice
2637 | distribution instead of all the distribution lines
2638 | of the invoice
2639 | p_return_message_name: Message returned to the calling module of
2640 | status of invoice
2641 | p_calling_sequence: Debugging string to indicate path of module calls
2642 | to be printed out NOCOPY upon error.
2643 |
2644 | NOTE
2645 |
2646 | MODIFICATION HISTORY
2647 | Date Author Description of Change
2648 |
2649 *==========================================================================*/
2650
2651 PROCEDURE Funds_Check(
2652 p_invoice_id IN NUMBER,
2653 p_inv_line_num IN NUMBER,
2654 p_dist_line_num IN NUMBER,
2655 p_return_message_name IN OUT NOCOPY VARCHAR2,
2656 p_calling_sequence IN VARCHAR2) IS
2657
2658 CURSOR funds_check_dist_cursor IS
2659 SELECT AI.invoice_id, -- invoice_id
2660 AI.invoice_num, -- invoice_num
2661 AI.legal_entity_id, -- BCPSA bug
2662 AI.invoice_type_lookup_code, -- invoice_type_code
2663 AID.invoice_line_number, -- inv_line_num
2664 AID.invoice_distribution_id , -- inv_distribution_id
2665 AID.accounting_date, -- accounting_date
2666 AID.LINE_TYPE_LOOKUP_CODE, -- distribution_type
2667 AID.amount, -- distribution_amount
2668 AID.set_of_books_id, -- set_of_books_id
2669 AID.bc_event_id, -- bc_event_id
2670 AID.org_id, -- org_id
2671 NULL, --result_code
2672 NULL, --status_code
2673 'N' self_assessed_flag --self_assessed_flag --bug7109594
2674 FROM ap_invoice_distributions_all aid,
2675 ap_invoices_all ai,
2676 ap_invoice_lines_all ail,
2677 gl_period_statuses per
2678 WHERE ai.invoice_id = p_invoice_id
2679 AND aid.invoice_id = ai.invoice_id
2680 AND ail.invoice_id = aid.invoice_id
2681 AND ail.line_number = aid.invoice_line_number
2682 AND (p_dist_line_num IS NULL OR
2683 (p_dist_line_num IS NOT NULL
2684 AND aid.distribution_line_number = p_dist_line_num))
2685 AND ( p_inv_line_num IS NULL OR
2686 (p_inv_line_num IS NOT NULL
2687 AND aid.invoice_line_number = p_inv_line_num))
2688 AND nvl(aid.encumbered_flag, 'N') in ('N', 'H', 'P')
2689 AND aid.posted_flag in ('N', 'P')
2690 AND ail.line_type_lookup_code NOT IN ('AWT')
2691 AND aid.period_name = per.period_name
2692 AND per.set_of_books_id = ai.set_of_books_id
2693 AND per.application_id = 200
2694 AND nvl(per.adjustment_period_flag, 'N') = 'N'
2695 AND aid.po_distribution_id is NULL
2696 UNION ALL
2697 SELECT AI.invoice_id, -- invoice_id
2698 AI.invoice_num, -- invoice_num
2699 AI.legal_entity_id, -- BCPSA bug
2700 AI.invoice_type_lookup_code, -- invoice_type_code
2701 AID.invoice_line_number, -- inv_line_num
2702 AID.invoice_distribution_id , -- inv_distribution_id
2703 AID.accounting_date, -- accounting_date
2704 AID.LINE_TYPE_LOOKUP_CODE, -- distribution_type
2705 AID.amount, -- distribution_amount
2706 AID.set_of_books_id, -- set_of_books_id
2707 AID.bc_event_id, -- bc_event_id
2708 AID.org_id, -- org_id
2709 NULL, -- result_code
2710 NULL, -- status_code
2711 'N' self_assessed_flag -- self_assessed_flag --bug7109594
2712 FROM ap_invoice_distributions_all aid,
2713 ap_invoices_all ai,
2714 ap_invoice_lines_all ail,
2715 gl_period_statuses per,
2716 po_distributions_all pod
2717 WHERE ai.invoice_id = p_invoice_id
2718 AND aid.invoice_id = ai.invoice_id
2719 AND ail.invoice_id = aid.invoice_id
2720 AND ail.line_number = aid.invoice_line_number
2721 AND (p_dist_line_num IS NULL OR
2722 (p_dist_line_num IS NOT NULL
2723 AND aid.distribution_line_number = p_dist_line_num))
2724 AND ( p_inv_line_num IS NULL OR
2725 (p_inv_line_num IS NOT NULL
2726 AND aid.invoice_line_number = p_inv_line_num))
2727 AND ( (aid.line_type_lookup_code = 'ITEM' AND
2728 NVL(pod.accrue_on_receipt_flag,'N') <> 'Y')
2729 OR
2730 (aid.line_type_lookup_code NOT IN
2731 ( 'RETAINAGE', 'ACCRUAL','ITEM' )) )
2732 AND nvl(aid.encumbered_flag, 'N') in ('N', 'H', 'P')
2733 AND aid.posted_flag in ('N', 'P')
2734 AND ail.line_type_lookup_code NOT IN ('AWT')
2735 AND aid.period_name = per.period_name
2736 AND per.set_of_books_id = ai.set_of_books_id
2737 AND per.application_id = 200
2738 AND nvl(per.adjustment_period_flag, 'N') = 'N'
2739 AND aid.po_distribution_id is not NULL
2740 AND aid.po_distribution_id = pod.po_distribution_id
2741 AND NOT EXISTS ( select 'Advance Exists'
2742 from po_distributions_all pod,
2743 po_headers_all poh,
2744 ap_invoice_distributions_all ainvd,
2745 ap_invoices_all ainv,
2746 po_doc_style_headers pdsa
2747 where pod.po_distribution_id = aid.po_distribution_id
2748 and poh.po_header_id = pod.po_header_id
2749 and poh.style_id = pdsa.style_id
2750 and ainv.invoice_id = ai.invoice_id
2751 and ainv.invoice_id = ainvd.invoice_id
2752 and ainvd.po_distribution_id = pod.po_distribution_id
2753 and nvl(pdsa.advances_flag, 'N') = 'Y'
2754 and (ainvd.line_type_lookup_code = 'PREPAY'
2755 OR
2756 ainv.invoice_type_lookup_code = 'PREPAYMENT') )
2757 UNION ALL
2758 SELECT AI.invoice_id, -- invoice_id
2759 AI.invoice_num, -- invoice_num
2760 AI.legal_entity_id, -- BCPSA bug
2761 AI.invoice_type_lookup_code, -- invoice_type_code
2762 T.invoice_line_number, -- inv_line_num
2763 T.invoice_distribution_id , -- inv_distribution_id
2764 T.accounting_date, -- accounting_date
2765 T.LINE_TYPE_LOOKUP_CODE, -- distribution_type
2766 T.amount, -- distribution_amount
2767 T.set_of_books_id, -- set_of_books_id
2768 T.bc_event_id, -- bc_event_id
2769 T.org_id, -- org_id
2770 NULL, --result_code
2771 NULL, --status_code
2772 T.self_assessed_flag --self_assessed_flag --bug7109594
2773 FROM ap_self_assessed_tax_dist_all t,
2774 ap_invoices_all ai,
2775 gl_period_statuses per
2776 WHERE ai.invoice_id = p_invoice_id
2777 AND t.invoice_id = ai.invoice_id
2778 AND (p_inv_line_num IS NULL OR
2779 (p_inv_line_num IS NOT NULL
2780 AND t.invoice_line_number = p_inv_line_num))
2781 AND (p_dist_line_num IS NULL OR
2782 (p_dist_line_num IS NOT NULL
2783 AND t.distribution_line_number = p_dist_line_num))
2784 AND nvl(t.encumbered_flag, 'N') in ('N', 'H', 'P')
2785 AND t.posted_flag in ('N', 'P')
2786 AND t.period_name = per.period_name
2787 AND per.set_of_books_id = ai.set_of_books_id
2788 AND per.application_id = 200
2789 AND nvl(per.adjustment_period_flag, 'N') = 'N'
2790 AND t.po_distribution_id is NULL
2791 UNION ALL
2792 SELECT AI.invoice_id, -- invoice_id
2793 AI.invoice_num, -- invoice_num
2794 AI.legal_entity_id, -- BCPSA bug
2795 AI.invoice_type_lookup_code, -- invoice_type_code
2796 T.invoice_line_number, -- inv_line_num
2797 T.invoice_distribution_id , -- inv_distribution_id
2798 T.accounting_date, -- accounting_date
2799 T.LINE_TYPE_LOOKUP_CODE, -- distribution_type
2800 T.amount, -- distribution_amount
2801 T.set_of_books_id, -- set_of_books_id
2802 T.bc_event_id, -- bc_event_id
2803 T.org_id, -- org_id
2804 NULL, -- result_code
2805 NULL, -- status_code
2806 T.self_assessed_flag -- self_assessed_flag --bug7109594
2807 FROM ap_self_assessed_tax_dist_all t,
2808 ap_invoices_all ai,
2809 gl_period_statuses per
2810 WHERE ai.invoice_id = p_invoice_id
2811 AND t.invoice_id = ai.invoice_id
2812 AND (p_inv_line_num IS NULL OR
2813 (p_inv_line_num IS NOT NULL
2814 AND t.invoice_line_number = p_inv_line_num))
2815 AND (p_dist_line_num IS NULL OR
2816 (p_dist_line_num IS NOT NULL
2817 AND t.distribution_line_number = p_dist_line_num))
2818 AND nvl(t.encumbered_flag, 'N') in ('N', 'H', 'P')
2819 AND t.posted_flag in ('N', 'P')
2820 AND t.period_name = per.period_name
2821 AND per.set_of_books_id = ai.set_of_books_id
2822 AND per.application_id = 200
2823 AND nvl(per.adjustment_period_flag, 'N') = 'N'
2824 AND t.po_distribution_id is NOT NULL
2825 AND NOT EXISTS ( select 'Advance Exists'
2826 from po_distributions_all pod,
2827 po_headers_all poh,
2828 ap_invoice_distributions_all ainvd,
2829 ap_invoices_all ainv,
2830 po_doc_style_headers pdsa
2831 where pod.po_distribution_id = t.po_distribution_id
2832 and poh.po_header_id = pod.po_header_id
2833 and poh.style_id = pdsa.style_id
2834 and ainv.invoice_id = t.invoice_id
2835 and ainv.invoice_id = ainvd.invoice_id
2836 and ainvd.po_distribution_id = pod.po_distribution_id
2837 and nvl(pdsa.advances_flag, 'N') = 'Y'
2838 and (ainvd.line_type_lookup_code = 'PREPAY'
2839 OR
2840 ainv.invoice_type_lookup_code = 'PREPAYMENT') );
2841
2842 l_debug_loc VARCHAR2(2000) := 'Funds_Check';
2843 l_curr_calling_sequence VARCHAR2(2000);
2844 l_debug_info VARCHAR2(2000);
2845
2846 l_return_code VARCHAR(30);
2847
2848 l_status_code VARCHAR2(1);
2849
2850 t_funds_dist_tab PSA_AP_BC_PVT.Funds_Dist_Tab_Type;--bc
2851
2852 l_bc_mode VARCHAR2(1) := 'C'; --bc
2853 l_set_of_books_id NUMBER;
2854 l_chart_of_accounts_id NUMBER;
2855 l_flex_method VARCHAR2(25);
2856 l_auto_offsets_flag VARCHAR2(1);
2857 l_sys_xrate_gain_ccid NUMBER;
2858 l_sys_xrate_loss_ccid NUMBER;
2859 l_base_currency_code VARCHAR2(15);
2860 l_inv_enc_type_id NUMBER;
2861 l_gl_user NUMBER;
2862
2863 l_dist_rec_count NUMBER;
2864 l_return_status VARCHAR2(30); --bc
2865 l_msg_count NUMBER; --bc
2866 l_msg_data VARCHAR2(2000); --bc
2867 l_packet_id NUMBER; -- Bug 4535804
2868
2869 l_org_id NUMBER; -- Bug 5487757
2870 PSA_API_EXCEPTION EXCEPTION;
2871
2872 -- Logging Infra:
2873 l_procedure_name CONSTANT VARCHAR2(30) := 'Funds_Check';
2874 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2875
2876 BEGIN
2877
2878 -- Update the calling sequence --
2879
2880 l_curr_calling_sequence := 'AP_FUNDS_CONTROL_PKG.'||l_debug_loc||
2881 '<-'||p_calling_sequence;
2882
2883 -- Logging Infra: Procedure level
2884 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
2885 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2886 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
2887 l_procedure_name, l_log_msg);
2888 END IF;
2889
2890 --Bug 5487757
2891 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
2892 l_log_msg := 'Selecting Org_Id for determining Encumbrance Enabled or not' ;
2893 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
2894 l_procedure_name, l_log_msg);
2895 END IF;
2896
2897 SELECT org_id
2898 INTO l_org_id
2899 FROM AP_INVOICES_ALL
2900 WHERE invoice_id = p_invoice_id;
2901
2902
2903 /*-----------------------------------------------------------------+
2904 | Check if System Encumbrance option is turned on |
2905 +-----------------------------------------------------------------*/
2906
2907 IF (Encumbrance_Enabled(l_org_id)) THEN
2908
2909 /*-----------------------------------------------------------------+
2910 | Step 1 - setup gl_fundschecker parameters |
2911 +-----------------------------------------------------------------*/
2912
2913 -- Logging Infra: Procedure level
2914 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
2915 l_log_msg := 'Setup Gl Fundsctrl Param';
2916 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
2917 l_procedure_name, l_log_msg);
2918 END IF;
2919
2920 Setup_Gl_FundsCtrl_Params(
2921 l_bc_mode,
2922 'FUNDSCHECK',
2923 l_curr_calling_sequence);
2924
2925
2926 /*-----------------------------------------------------------------+
2927 | Step 1.5 - Update the encumbered_flag for recoverable tax |
2928 | distributions to R so that these are not sent to PSA |
2929 | for encumbering -- added for bug#8936952 |
2930 +-----------------------------------------------------------------*/
2931 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
2932 l_log_msg := ' Update encumbered flag of recoverable ' ||
2933 'tax distributions to R';
2934 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
2935 l_procedure_name, l_log_msg);
2936 END IF;
2937 Update ap_invoice_distributions_all
2938 set encumbered_flag = 'R'
2939 where invoice_id = p_invoice_id
2940 and line_type_lookup_code = 'REC_TAX';
2941
2942 /*-----------------------------------------------------------------+
2943 | Step 2 - Get all the selected distributions for processing |
2944 +-----------------------------------------------------------------*/
2945
2946 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
2947 l_log_msg := 'Step 2 - Open FundsCntrl_Inv_Dist_Cur Cursor';
2948 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
2949 l_procedure_name, l_log_msg);
2950 END IF;
2951
2952 OPEN Funds_Check_Dist_Cursor;
2953 FETCH Funds_Check_Dist_Cursor
2954 BULK COLLECT INTO t_funds_dist_tab;
2955 CLOSE Funds_Check_Dist_Cursor;
2956
2957 /*-----------------------------------------------------------------+
2958 | Step 3 - Accounting Event Handling - Create, Stamp, Cleanup |
2959 +-----------------------------------------------------------------*/
2960
2961 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
2962 l_log_msg := 'Step 3 - Call psa_ap_bc_pvt.Create_Events';
2963 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
2964 l_procedure_name, l_log_msg);
2965 END IF;
2966
2967 IF ( t_funds_dist_tab.COUNT <> 0 ) THEN
2968
2969 psa_ap_bc_pvt.Create_Events (
2970 p_init_msg_list => fnd_api.g_true,
2971 p_tab_fc_dist => t_funds_dist_tab,
2972 p_calling_mode => 'APPROVE',
2973 p_bc_mode => l_bc_mode,
2974 p_calling_sequence => l_curr_calling_sequence,
2975 x_return_status => l_return_status,
2976 x_msg_count => l_msg_count,
2977 x_msg_data => l_msg_data);
2978
2979 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
2980 l_log_msg := 'Call psa_ap_bc_pvt.Create_Events status result ' ||
2981 'l_return_status =' || l_return_status;
2982 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
2983 l_procedure_name, l_log_msg);
2984 END IF;
2985
2986 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2987
2988 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
2989 l_log_msg := 'Step 3 - Call psa_ap_bc_pvt.Create_Events not success ' ||
2990 'l_return_status =' || l_return_status;
2991 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
2992 l_procedure_name, l_log_msg);
2993 END IF;
2994
2995 RAISE PSA_API_EXCEPTION;
2996
2997 END IF;
2998
2999 /*-------------------------------------------------------------------+
3000 | Step 4 - Call PSA BUDGETARY CONTROL API |
3001 +-------------------------------------------------------------------*/
3002
3003 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
3004 l_log_msg := 'Step 4 - Call PSA_BC_XLA_PUB.Budgetary_Control';
3005 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
3006 l_procedure_name, l_log_msg);
3007 END IF;
3008
3009 PSA_BC_XLA_PUB.Budgetary_Control(
3010 p_api_version => 1.0,
3011 p_init_msg_list => Fnd_Api.G_False,
3012 x_return_status => l_return_status,
3013 x_msg_count => l_msg_count,
3014 x_msg_data => l_msg_data,
3015 p_application_id => 200,
3016 p_bc_mode => l_bc_mode,
3017 p_override_flag => 'N',
3018 P_user_id => NULL,
3019 P_user_resp_id => NULL,
3020 x_status_code => l_return_code,
3021 x_Packet_ID => l_packet_id );
3022
3023
3024 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
3025 l_log_msg := 'Call PSA_BC_XLA_PUB.Budgetary_Control success' ||
3026 'l_return_code =' || l_return_code ||
3027 'l_packet_id =' || to_char(l_packet_id);
3028 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
3029 l_procedure_name, l_log_msg);
3030 END IF;
3031
3032
3033 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3034
3035 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
3036 l_log_msg := 'Call PSA_BC_XLA_PUB.Budgetary_Control not success' ||
3037 'l_return_status =' || l_return_status;
3038 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
3039 l_procedure_name, l_log_msg);
3040 END IF;
3041
3042 RAISE PSA_API_EXCEPTION;
3043 END IF;
3044
3045
3046
3047 /*-------------------------------------------------------------------+
3048 | Step 5 - Process PSA BUDGETARY CONTROL return codes |
3049 +-------------------------------------------------------------------*/
3050
3051 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
3052 l_log_msg := 'FUNDSCHECK - Process_Return_Code of GL funds check';
3053 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3054 END IF;
3055
3056 IF (l_return_code in ('FAIL', 'PARTIAL')) THEN
3057
3058 -------------------------------------------------------------------
3059 -- Step 5a - Process PSA BUDGETARY CONTROL FAILED
3060 -------------------------------------------------------------------
3061
3062 p_return_message_name := 'AP_FCK_INSUFFICIENT_FUNDS';
3063
3064 ELSIF l_return_code = 'FATAL' THEN
3065 -------------------------------------------------------------------
3066 -- Step 5b - Process PSA BUDGETARY CONTROL SUCCESS
3067 -------------------------------------------------------------------
3068 p_return_message_name := 'AP_FCK_FAILED_FUNDSCHECKER';
3069
3070 ELSIF l_return_code = 'XLA_ERROR' THEN
3071 -------------------------------------------------------------------
3072 -- Step 5C - Process PSA BUDGETARY CONTROL SUCCESS
3073 -------------------------------------------------------------------
3074 p_return_message_name := 'AP_FCK_XLA_ERROR';
3075
3076 ELSIF l_return_code = 'XLA_NO_JOURNAL' THEN
3077
3078 p_return_message_name := 'AP_FCK_XLA_NO_JOURNAL';
3079
3080 ELSE
3081 -------------------------------------------------------------------
3082 -- Step 5d - Process PSA BUDGETARY CONTROL SUCCESS
3083 -------------------------------------------------------------------
3084
3085 IF (l_return_code = 'ADVISORY') THEN
3086 p_return_message_name := 'AP_FCK_PASSED_FUNDS_ADVISORY';
3087 ELSE
3088 p_return_message_name := 'AP_FCK_PASSED_FUNDSCHECKER';
3089 END IF;
3090 END IF; -- end of check Fundscheck passed --
3091
3092 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
3093 l_log_msg := 'returned message to form is ' ||
3094 'p_return_message_name =' || p_return_message_name;
3095 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
3096 l_procedure_name, l_log_msg);
3097 END IF;
3098
3099 ELSE
3100
3101 /*-------------------------------------------------------------------+
3102 | Process PSA BUDGETARY CONTROL return codes |
3103 +-------------------------------------------------------------------*/
3104 p_return_message_name := 'AP_ENC_NO_DIST_APPL'; --added for bug 8639979
3105 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
3106 l_log_msg := 'no Call of psa_ap_bc_pvt.Create_Events' ||
3107 'distribution cursor count = 0';
3108 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
3109 l_procedure_name, l_log_msg);
3110 END IF;
3111
3112 END IF;
3113
3114 ELSE
3115 /*-------------------------------------------------------------------+
3116 | System Encumbrance option is turned off |
3117 +-------------------------------------------------------------------*/
3118
3119 IF (G_LEVEL_STATEMENT >= g_log_level ) THEN
3120 l_log_msg := 'encumberance is off';
3121 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
3122 l_procedure_name, l_log_msg);
3123 END IF;
3124
3125 p_return_message_name := 'AP_ALL_ENC_OFF';
3126
3127
3128 END IF;
3129
3130 -- Logging Infra: Procedure level
3131 IF (G_LEVEL_PROCEDURE >= g_log_level ) THEN
3132 l_log_msg := 'End of procedure '|| l_procedure_name;
3133 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||
3134 l_procedure_name, l_log_msg);
3135 END IF;
3136
3137 EXCEPTION
3138 WHEN PSA_API_EXCEPTION THEN
3139 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3140 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3141 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
3142 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3143 'invoice_id = '|| to_char(p_invoice_id) );
3144 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_msg_data);
3145 APP_EXCEPTION.RAISE_EXCEPTION;
3146
3147 WHEN OTHERS THEN
3148 IF (SQLCODE <> -20001) THEN
3149 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3150 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3151 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
3152 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3153 'Invoice_id = '|| to_char(p_invoice_id)
3154 ||', Invoice_line_num = '|| to_char(p_inv_line_num)
3155 ||', Dist_line_num = '|| to_char(p_dist_line_num));
3156 END IF;
3157 APP_EXCEPTION.RAISE_EXCEPTION;
3158 END Funds_Check;
3159
3160
3161 -- ETAX: Validation
3162 -- Added the Funds_Check_Processor as part of ETAX: Validation project.
3163 -- This is called from Check Funds Menu Option.
3164 /*-----------------------------------------------------------------------
3165 | FUNDS CHECK PROCESSOR |
3166 ------------------------------------------------------------------------*/
3167 FUNCTION Funds_Check_Processor ( P_Invoice_Id IN NUMBER,
3168 P_Invoice_Line_Number IN NUMBER,
3169 p_dist_line_num IN NUMBER,
3170 P_Invoice_Needs_Validation IN VARCHAR2,
3171 P_Error_Code OUT NOCOPY VARCHAR2,
3172 P_Token1 OUT NOCOPY NUMBER,
3173 P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN IS
3174
3175 CURSOR Invoice_Lines_Cursor(P_Line_Number NUMBER) IS
3176 SELECT INVOICE_ID,
3177 LINE_NUMBER,
3178 LINE_TYPE_LOOKUP_CODE,
3179 REQUESTER_ID,
3180 DESCRIPTION,
3181 LINE_SOURCE,
3182 ORG_ID,
3183 LINE_GROUP_NUMBER,
3184 INVENTORY_ITEM_ID,
3185 ITEM_DESCRIPTION,
3186 SERIAL_NUMBER,
3187 MANUFACTURER,
3188 MODEL_NUMBER,
3189 WARRANTY_NUMBER,
3190 GENERATE_DISTS,
3191 MATCH_TYPE,
3192 DISTRIBUTION_SET_ID,
3193 ACCOUNT_SEGMENT,
3194 BALANCING_SEGMENT,
3195 COST_CENTER_SEGMENT,
3196 OVERLAY_DIST_CODE_CONCAT,
3197 DEFAULT_DIST_CCID,
3198 PRORATE_ACROSS_ALL_ITEMS,
3199 ACCOUNTING_DATE,
3200 PERIOD_NAME ,
3201 DEFERRED_ACCTG_FLAG ,
3202 DEF_ACCTG_START_DATE ,
3203 DEF_ACCTG_END_DATE,
3204 DEF_ACCTG_NUMBER_OF_PERIODS,
3205 DEF_ACCTG_PERIOD_TYPE ,
3206 SET_OF_BOOKS_ID,
3207 AMOUNT,
3208 BASE_AMOUNT,
3209 ROUNDING_AMT,
3210 QUANTITY_INVOICED,
3211 UNIT_MEAS_LOOKUP_CODE ,
3212 UNIT_PRICE,
3213 WFAPPROVAL_STATUS,
3214 DISCARDED_FLAG,
3215 ORIGINAL_AMOUNT,
3216 ORIGINAL_BASE_AMOUNT ,
3217 ORIGINAL_ROUNDING_AMT ,
3218 CANCELLED_FLAG ,
3219 INCOME_TAX_REGION,
3220 TYPE_1099 ,
3221 STAT_AMOUNT ,
3222 PREPAY_INVOICE_ID ,
3223 PREPAY_LINE_NUMBER ,
3224 INVOICE_INCLUDES_PREPAY_FLAG ,
3225 CORRECTED_INV_ID ,
3226 CORRECTED_LINE_NUMBER ,
3227 PO_HEADER_ID,
3228 PO_LINE_ID ,
3229 PO_RELEASE_ID ,
3230 PO_LINE_LOCATION_ID ,
3231 PO_DISTRIBUTION_ID,
3232 RCV_TRANSACTION_ID,
3233 FINAL_MATCH_FLAG,
3234 ASSETS_TRACKING_FLAG ,
3235 ASSET_BOOK_TYPE_CODE ,
3236 ASSET_CATEGORY_ID ,
3237 PROJECT_ID ,
3238 TASK_ID ,
3239 EXPENDITURE_TYPE ,
3240 EXPENDITURE_ITEM_DATE ,
3241 EXPENDITURE_ORGANIZATION_ID ,
3242 PA_QUANTITY, PA_CC_AR_INVOICE_ID ,
3243 PA_CC_AR_INVOICE_LINE_NUM ,
3244 PA_CC_PROCESSED_CODE ,
3245 AWARD_ID,
3246 AWT_GROUP_ID ,
3247 REFERENCE_1 ,
3248 REFERENCE_2 ,
3249 RECEIPT_VERIFIED_FLAG ,
3250 RECEIPT_REQUIRED_FLAG ,
3251 RECEIPT_MISSING_FLAG ,
3252 JUSTIFICATION ,
3253 EXPENSE_GROUP ,
3254 START_EXPENSE_DATE ,
3255 END_EXPENSE_DATE ,
3256 RECEIPT_CURRENCY_CODE ,
3257 RECEIPT_CONVERSION_RATE,
3258 RECEIPT_CURRENCY_AMOUNT ,
3259 DAILY_AMOUNT ,
3260 WEB_PARAMETER_ID ,
3261 ADJUSTMENT_REASON ,
3262 MERCHANT_DOCUMENT_NUMBER ,
3263 MERCHANT_NAME ,
3264 MERCHANT_REFERENCE ,
3265 MERCHANT_TAX_REG_NUMBER,
3266 MERCHANT_TAXPAYER_ID ,
3267 COUNTRY_OF_SUPPLY,
3268 CREDIT_CARD_TRX_ID ,
3269 COMPANY_PREPAID_INVOICE_ID,
3270 CC_REVERSAL_FLAG ,
3271 CREATION_DATE ,
3272 CREATED_BY,
3273 LAST_UPDATED_BY ,
3274 LAST_UPDATE_DATE ,
3275 LAST_UPDATE_LOGIN ,
3276 PROGRAM_APPLICATION_ID ,
3277 PROGRAM_ID ,
3278 PROGRAM_UPDATE_DATE,
3279 REQUEST_ID ,
3280 ATTRIBUTE_CATEGORY,
3281 ATTRIBUTE1,
3282 ATTRIBUTE2,
3283 ATTRIBUTE3,
3284 ATTRIBUTE4,
3285 ATTRIBUTE5,
3286 ATTRIBUTE6,
3287 ATTRIBUTE7,
3288 ATTRIBUTE8,
3289 ATTRIBUTE9,
3290 ATTRIBUTE10,
3291 ATTRIBUTE11,
3292 ATTRIBUTE12,
3293 ATTRIBUTE13,
3294 ATTRIBUTE14,
3295 ATTRIBUTE15,
3296 GLOBAL_ATTRIBUTE_CATEGORY,
3297 GLOBAL_ATTRIBUTE1,
3298 GLOBAL_ATTRIBUTE2,
3299 GLOBAL_ATTRIBUTE3,
3300 GLOBAL_ATTRIBUTE4,
3301 GLOBAL_ATTRIBUTE5,
3302 GLOBAL_ATTRIBUTE6,
3303 GLOBAL_ATTRIBUTE7,
3304 GLOBAL_ATTRIBUTE8,
3305 GLOBAL_ATTRIBUTE9,
3306 GLOBAL_ATTRIBUTE10,
3307 GLOBAL_ATTRIBUTE11,
3308 GLOBAL_ATTRIBUTE12,
3309 GLOBAL_ATTRIBUTE13,
3310 GLOBAL_ATTRIBUTE14,
3311 GLOBAL_ATTRIBUTE15,
3312 GLOBAL_ATTRIBUTE16,
3313 GLOBAL_ATTRIBUTE17,
3314 GLOBAL_ATTRIBUTE18,
3315 GLOBAL_ATTRIBUTE19,
3316 GLOBAL_ATTRIBUTE20,
3317 --ETAX: Invwkb
3318 INCLUDED_TAX_AMOUNT,
3319 PRIMARY_INTENDED_USE,
3320 --Bugfix:4673607
3321 APPLICATION_ID,
3322 PRODUCT_TABLE,
3323 REFERENCE_KEY1,
3324 REFERENCE_KEY2,
3325 REFERENCE_KEY3,
3326 REFERENCE_KEY4,
3327 REFERENCE_KEY5,
3328 --bugfix:4674194
3329 SHIP_TO_LOCATION_ID,
3330 --bug 7022001
3331 PAY_AWT_GROUP_ID
3332 FROM ap_invoice_lines
3333 WHERE invoice_id = p_invoice_id
3334 AND line_number = nvl(p_line_number,line_number)
3335 --Invoice Lines: Distributions
3336 ORDER BY decode(line_type_lookup_code,'ITEM',1,2), line_number;
3337
3338 l_result NUMBER;
3339 l_success BOOLEAN := TRUE;
3340 t_inv_lines_table AP_INVOICES_PKG.t_invoice_lines_table;
3341 i NUMBER;
3342 l_holds AP_APPROVAL_PKG.HOLDSARRAY;
3343 l_hold_count AP_APPROVAL_PKG.COUNTARRAY;
3344 l_release_count AP_APPROVAL_PKG.COUNTARRAY;
3345 l_system_user NUMBER := 5;
3346 l_chart_of_accounts_id NUMBER;
3347 l_auto_offsets_flag VARCHAR2(1);
3348 l_sys_xrate_gain_ccid NUMBER;
3349 l_sys_xrate_loss_ccid NUMBER;
3350 l_base_currency_code FND_CURRENCIES.CURRENCY_CODE%TYPE;
3351 l_xrate_flex_qualifier_name VARCHAR2(12);
3352 l_xrate_flex_seg_delimiter VARCHAR2(1);
3353 l_xrate_flex_segment_number NUMBER;
3354 l_xrate_flex_num_of_segments NUMBER;
3355 l_xrate_gain_segments FND_FLEX_EXT.SEGMENTARRAY;
3356 l_xrate_loss_segments FND_FLEX_EXT.SEGMENTARRAY;
3357 l_xrate_cant_flexbuild_flag BOOLEAN;
3358 l_xrate_cant_flexbuild_reason VARCHAR2(2000);
3359 l_flex_method VARCHAR2(25);
3360 l_inv_env_type_id NUMBER;
3361 l_gl_user_id NUMBER;
3362 l_set_of_books_id NUMBER;
3363 l_error_code VARCHAR2(4000);
3364 l_insufficient_data_exist BOOLEAN := FALSE;
3365 l_batch_id AP_BATCHES.BATCH_ID%TYPE;
3366 l_invoice_date AP_INVOICES.INVOICE_DATE%TYPE;
3367 l_vendor_id AP_INVOICES.VENDOR_ID%TYPE;
3368 l_invoice_currency_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
3369 l_exchange_rate AP_INVOICES.EXCHANGE_RATE%TYPE;
3370 l_exchange_rate_type AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE;
3371 l_exchange_date AP_INVOICES.EXCHANGE_DATE%TYPE;
3372 l_return_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
3373 l_line_type_lookup_code AP_INVOICE_LINES.LINE_TYPE_LOOKUP_CODE%TYPE;
3374 l_line_number AP_INVOICE_LINES.LINE_NUMBER%TYPE;
3375 l_debug_info VARCHAR2(1000);
3376 l_curr_calling_sequence VARCHAR2(2000);
3377
3378 l_api_name VARCHAR2(50);
3379
3380 BEGIN
3381
3382 l_api_name := 'Funds_Check_Processor';
3383
3384 l_curr_calling_sequence := 'Funds_Check_Processor <-'||p_calling_sequence;
3385
3386 IF (p_invoice_needs_validation = 'Y') THEN
3387
3388 l_debug_info := 'Calculate Tax';
3389 l_success := ap_etax_pkg.calling_etax(
3390 p_invoice_id => p_invoice_id,
3391 p_calling_mode => 'CALCULATE',
3392 p_all_error_messages => 'N',
3393 p_error_code => l_error_code,
3394 p_calling_sequence => l_curr_calling_sequence);
3395
3396 IF (NOT l_success) THEN
3397
3398 p_error_code := l_error_code;
3399 return(FALSE);
3400
3401 END IF;
3402
3403 SELECT batch_id,
3404 vendor_id,
3405 invoice_date,
3406 invoice_currency_code,
3407 exchange_rate,
3408 exchange_rate_type,
3409 exchange_date
3410 INTO l_batch_id,
3411 l_vendor_id,
3412 l_invoice_date,
3413 l_invoice_currency_code,
3414 l_exchange_rate,
3415 l_exchange_rate_type,
3416 l_exchange_date
3417 FROM ap_invoices
3418 WHERE invoice_id = p_invoice_id;
3419
3420 --If the funds check is called for a ITEM line, then
3421 --generate the candidate distributions for just that line,
3422 --else of a charge line we will generate candidate distributions
3423 --for all the lines due to the dependency between distribution generation
3424 --of charge lines on the item lines.
3425
3426 IF (p_invoice_line_number IS NOT NULL) THEN
3427
3428 SELECT line_type_lookup_code
3429 INTO l_line_type_lookup_code
3430 FROM ap_invoice_lines ail
3431 WHERE ail.invoice_id = p_invoice_id
3432 AND ail.line_number = p_invoice_line_number;
3433
3434 IF (l_line_type_lookup_code = 'ITEM') THEN
3435 l_line_number := p_invoice_line_number;
3436 END IF;
3437
3438 END IF;
3439
3440 Fundscheck_init(p_invoice_id => p_invoice_id,
3441 p_set_of_books_id => l_set_of_books_id,
3442 p_xrate_gain_ccid => l_sys_xrate_gain_ccid,
3443 p_xrate_loss_ccid => l_sys_xrate_loss_ccid,
3444 p_base_currency_code => l_base_currency_code,
3445 p_inv_enc_type_id => l_inv_env_type_id,
3446 p_gl_user_id => l_gl_user_id,
3447 p_calling_sequence => l_curr_calling_sequence);
3448
3449
3450 OPEN Invoice_Lines_Cursor(l_line_number);
3451 FETCH Invoice_Lines_Cursor BULK COLLECT INTO t_inv_lines_table;
3452 CLOSE Invoice_Lines_Cursor;
3453
3454 FOR i in t_inv_lines_table.first .. t_inv_lines_table.count LOOP
3455
3456 IF ( t_inv_lines_table(i).line_type_lookup_code <> 'TAX' AND
3457 t_inv_lines_table(i).generate_dists = 'Y' ) THEN
3458
3459 AP_Approval_Pkg.Check_Insufficient_Line_Data(
3460 p_inv_line_rec => t_inv_lines_table(i),
3461 p_system_user => l_system_user,
3462 p_holds => l_holds,
3463 p_holds_count => l_hold_count,
3464 p_release_count => l_release_count,
3465 p_insufficient_data_exist => l_insufficient_data_exist,
3466 p_calling_mode => 'CANDIDATE_DISTRIBUTIONS',
3467 p_calling_sequence => l_curr_calling_sequence );
3468
3469 IF ( NOT l_insufficient_data_exist ) THEN
3470
3471 l_success := AP_Approval_Pkg.Execute_Dist_Generation_Check(
3472 p_batch_id => l_batch_id,
3473 p_invoice_date => l_invoice_date,
3474 p_vendor_id => l_vendor_id,
3475 p_invoice_currency => l_invoice_currency_code,
3476 p_exchange_rate => l_exchange_rate,
3477 p_exchange_rate_type => l_exchange_rate_type,
3478 p_exchange_date => l_exchange_date,
3479 p_inv_line_rec => t_inv_lines_table(i),
3480 p_system_user => l_system_user,
3481 p_holds => l_holds,
3482 p_holds_count => l_hold_count,
3483 p_release_count => l_release_count,
3484 p_generate_permanent => 'N',
3485 p_calling_mode => 'CANDIDATE_DISTRIBUTIONS',
3486 p_error_code => l_error_code,
3487 p_curr_calling_sequence => l_curr_calling_sequence);
3488
3489 l_debug_info := 'Distributions could not be generated for' ||
3490 'this Invoice line, return FALSE';
3491
3492 IF (NOT l_success) THEN
3493 p_error_code := l_error_code;
3494 return(FALSE);
3495 END IF;
3496
3497 ELSE -- Insufficient line data exists
3498
3499 p_error_code := 'AP_INSUFFICIENT_LINE_DATA';
3500 p_token1 := t_inv_lines_table(i).line_number;
3501 return(FALSE);
3502
3503 END IF; -- end of sufficient data check
3504
3505 END IF; -- end of generate_dist check
3506
3507 --Calculate IPV, ERV for po/rcv matched lines
3508 IF (t_inv_lines_table(i).match_type in ('ITEM_TO_PO',
3509 'ITEM_TO_RECEIPT',
3510 'PRICE_CORRECTION',
3511 'QTY_CORRECTION' ) ) THEN
3512
3513 l_debug_info := 'Calculate Matched Variances';
3514 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3515 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3516 END IF;
3517
3518 AP_APPROVAL_MATCHED_PKG.Exec_Matched_Variance_Checks(
3519 p_invoice_id => p_invoice_id,
3520 p_inv_line_number => t_inv_lines_table(i).line_number,
3521 p_base_currency_code => l_base_currency_code,
3522 p_inv_currency_code => l_invoice_currency_code,
3523 p_sys_xrate_gain_ccid => l_sys_xrate_gain_ccid,
3524 p_sys_xrate_loss_ccid => l_sys_xrate_loss_ccid,
3525 p_system_user => l_system_user,
3526 p_holds => l_holds,
3527 p_hold_count => l_hold_count,
3528 p_release_count => l_release_count,
3529 p_calling_sequence => l_curr_calling_sequence );
3530
3531 END IF;
3532
3533 END LOOP;
3534
3535 l_debug_info := 'Calculate Quantity Variance: '||p_invoice_id;
3536 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3537 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3538 END IF;
3539
3540 AP_APPROVAL_MATCHED_PKG.Exec_Qty_Variance_Check(
3541 p_invoice_id => p_invoice_id,
3542 p_base_currency_code => l_base_currency_code,
3543 p_inv_currency_code => l_invoice_currency_code,
3544 p_system_user => l_system_user,
3545 p_calling_sequence => l_curr_calling_sequence );
3546
3547 l_debug_info := 'Create Tax Distributions';
3548 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3549 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3550 END IF;
3551
3552 l_success := ap_etax_pkg.calling_etax (
3553 p_invoice_id => p_invoice_id,
3554 p_calling_mode => 'DISTRIBUTE',
3555 p_all_error_messages => 'N',
3556 p_error_code => l_error_code,
3557 p_calling_sequence => l_curr_calling_sequence);
3558
3559 IF (NOT l_success) THEN
3560 p_error_code := l_error_code;
3561 return(FALSE);
3562 END IF;
3563
3564 END IF; -- p_invoice_needs_validation
3565
3566 l_debug_info := 'Before calling funds_check';
3567 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3568 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3569 END IF;
3570
3571 ap_funds_control_pkg.funds_check(
3572 p_invoice_id => p_invoice_id,
3573 p_inv_line_num => p_invoice_line_number,
3574 p_dist_line_num => p_dist_line_num,
3575 p_return_message_name => l_return_message_name,
3576 p_calling_sequence => l_curr_calling_sequence);
3577
3578 l_debug_info := 'After calling funds_check: l_return_message_name: '||l_return_message_name;
3579 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3580 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3581 END IF;
3582
3583 p_error_code := l_return_message_name;
3584 return(TRUE);
3585
3586 EXCEPTION
3587 WHEN OTHERS THEN
3588 IF (SQLCODE <> -20001) THEN
3589 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3590 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3591 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
3592 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3593 'Invoice_id = '|| to_char(p_invoice_id));
3594 END IF;
3595
3596 APP_EXCEPTION.RAISE_EXCEPTION;
3597
3598 END Funds_Check_Processor;
3599
3600
3601 BEGIN
3602 g_log_level := G_CURRENT_RUNTIME_LEVEL;
3603 g_log_enabled := fnd_log.test
3604 (log_level => g_log_level
3605 ,module => G_MODULE_NAME);
3606
3607 IF NOT g_log_enabled THEN
3608 g_log_level := G_LEVEL_LOG_DISABLED;
3609 END IF;
3610
3611 END AP_FUNDS_CONTROL_PKG;
3612
3613