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