1 PACKAGE BODY ap_sla_processing_pkg AS
2 /* $Header: apslappb.pls 120.50 2011/10/31 14:24:57 zrehman ship $ */
3
4
5 -------------------------------------------------------------------------------
6 --
7 -- ap_invoice_payments_all
8 -- \|/ \|/
9 -- | |
10 -- +-----------------------+ +------------------------+
11 -- | |
12 -- ap_checks_all ap_invoices_all
13 -- | |
14 -- | |
15 -- | |
16 -- /|\ /|\
17 -- ap_payment_history_all ap_invoice_lines_all
18 -- | |
19 -- | |
20 -- | |
21 -- | /|\
22 -- | ap_invoice_distributions_all
23 -- | | |
24 -- | +---------------------------------------------+ |
25 -- | | |
26 -- /|\ /|\ /|\
27 -- ap_payment_hist_dists ap_prepay_app_dists
28 --
29 --
30 -- Each record in the AP_INVOICE_PAYMENTS_ALL table relates a portion of a
31 -- payment to an invoice.
32 --
33 -- Each record in the AP_INVOICE_DISTRIBUTIONS_ALL table relates a portion of
34 -- the cost of an invoice to an accounting cost object.
35 --
36 -- Each record in the AP_PAYMENT_HIST_DISTS table relates a payment
37 -- distribution to a distribution.
38 --
39 -- Each record in the AP_PREPAY_APP_DISTS table relates a prepayment
40 -- distribution to a distribution.
41 --
42 --
43 -- +-----------------+------------+---------------+
44 -- | | Batch Mode | Document Mode |
45 -- +-----------------+------------+---------------+
46 -- | Pre-accounting | Yes | No |
47 -- | Extract | Yes | Yes |
48 -- | Post-processing | Yes | Yes |
49 -- | Post-accounting | Yes | No |
50 -- +-----------------+------------+---------------+
51 --
52 -------------------------------------------------------------------------------
53
54 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
55 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
56 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
57 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
58 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
59 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
60 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
61 G_MODULE_NAME CONSTANT VARCHAR2(50) :='AP.PLSQL.AP_SLA_PROCESSING_PKG.';
62 G_COMMIT_SIZE CONSTANT NUMBER := 10000;
63
64 TYPE l_event_ids_typ IS TABLE OF NUMBER(15)
65 INDEX BY PLS_INTEGER;
66 /*============================================================================
67 | PROCEDURE - TRACE (PRIVATE)
68 |
69 | DESCRIPTION
70 | This procedure is used to trace the log information.
71 |
72 | PRAMETERS
73 | p_level: The level of log message. The possible values are:
74 | G_LEVEL_UNEXPECTED
75 | G_LEVEL_ERROR
76 | G_LEVEL_EXCEPTION
77 | G_LEVEL_EVENT
78 | G_LEVEL_PROCEDURE
79 | G_LEVEL_STATEMENT
80 | p_procedure: The procedure's name
81 | p_debug_info: The log message
82 | KNOWN ISSUES:
83 |
84 | NOTES:
85 |
86 | MODIFICATION HISTORY
87 | Date Author Description of Change
88 |
89 *===========================================================================*/
90 PROCEDURE trace (
91 p_level IN NUMBER,
92 p_procedure_name IN VARCHAR2,
93 p_debug_info IN VARCHAR2
94 )
95 IS
96
97 BEGIN
98 IF (p_level >= G_CURRENT_RUNTIME_LEVEL ) THEN
99 FND_LOG.STRING(p_level,
100 G_MODULE_NAME||p_procedure_name,
101 p_debug_info);
102 END IF;
103
104 END trace;
105
106 /*============================================================================
107 | PROCEDURE - Lock_Documents_autonomous (PRIVATE)
108 |
109 | DESCRIPTION
110 | This procedure is used to update posted flag of document such
111 | as invoice or payment, invoice payment so that user will not
112 | make changes during accounting process via form.
113 |
114 | PRAMETERS
115 | p_level:
116 | p_procedure: The procedure's name
117 | p_debug_info: The log message
118 | KNOWN ISSUES:
119 |
120 | NOTES:
121 |
122 |
123 | MODIFICATION HISTORY
124 | Date Author Description of Change
125 | 03/MAR/2010 GAGRAWAL Added the parameter p_accounting_mode
126 | for the bug9377273
127 *===========================================================================*/
128 PROCEDURE lock_documents_autonomous (
129 p_event_ids IN l_event_ids_typ,
130 p_accounting_mode IN VARCHAR2,
131 p_calling_sequence IN VARCHAR2
132 )
133 IS
134 -- PRAGMA AUTONOMOUS_TRANSACTION; bug 7351478
135
136 l_debug_info VARCHAR2(240);
137 l_procedure_name CONSTANT VARCHAR2(30) :='LOCK_DOCUMENTS_AUTONOMOUS';
138 l_curr_calling_sequence VARCHAR2(2000);
139
140 BEGIN
141
142 l_curr_calling_sequence := 'ap_sla_processing_pkg'||l_procedure_name
143 ||'<-'||p_calling_sequence;
144 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
145
146 ---------------------------------------------------------------------
147 l_debug_info := 'Begin of procedure '||l_procedure_name;
148 trace(G_LEVEL_PROCEDURE, l_procedure_name, l_debug_info);
149 ---------------------------------------------------------------------
150
151
152 -- bug9377273, added the IF
153 IF p_accounting_mode IN ('F','D') THEN
154
155 ---------------------------------------------------------------------
156 l_debug_info := 'Mark payment history posted_flag';
157 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
158 ---------------------------------------------------------------------
159 FORALL i IN 1 .. p_event_ids.count
160 UPDATE ap_payment_history_All APH
161 SET POSTED_FLAG = 'S'
162 -- bug 10101613
163 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
164 ,LAST_UPDATE_DATE = SYSDATE
165 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
166 WHERE APH.accounting_event_id = p_event_ids(i);
167
168 ---------------------------------------------------------------------
169 l_debug_info := 'Mark the payments posted_flag';
170 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
171 -- Payments with a POSTED_FLAG of 'Y' won't have their POSTED_FLAGs
172 -- updated to 'S' because events that have already been accounted are
173 -- not in the XLA_ENTITY_EVENTS_V view (because no event is ever
174 -- accounted more than once). Only payments with a POSTED_FLAG of 'N'
175 -- or 'S' will their POSTED_FLAGs update to 'S'.
176 ---------------------------------------------------------------------
177
178 FORALL i IN 1 .. p_event_ids.count
179 UPDATE ap_invoice_payments_all AIP
180 SET AIP.posted_flag = 'S'
181 -- bug 10101613
182 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
183 ,LAST_UPDATE_DATE = SYSDATE
184 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
185 WHERE AIP.accounting_event_id = p_event_ids(i);
186
187 END IF;
188
189 -- bug9377273, added the IF
190 IF p_accounting_mode = 'F' THEN
191 ---------------------------------------------------------------------
192 l_debug_info := 'Mark the invoice distributions posted flag ' ;
193 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
194 --
195 -- Distributions with a POSTED_FLAG of 'Y' will not have their
196 -- POSTED_FLAGs updated to 'S' because events that have already been
197 -- accounted are not in the XLA_ENTITY_EVENTS_V view (because no event
198 -- is ever accounted more than once). Only distributions with a
199 -- POSTED_FLAG of 'N' or 'S' will their POSTED_FLAGs update to 'S'.
200 ---------------------------------------------------------------------
201
202 FORALL i IN 1 .. p_event_ids.count
203 UPDATE ap_invoice_distributions_all AID
204 SET AID.posted_flag = 'S'
205 -- bug 10101613
206 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
207 ,LAST_UPDATE_DATE = SYSDATE
208 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
209 WHERE AID.accounting_event_id = p_event_ids(i);
210
211 -- bug fix 6975868
212 FORALL i IN 1 .. p_event_ids.count
213 UPDATE ap_self_assessed_tax_dist_all STID
214 SET STID.posted_flag = 'S'
215 -- bug 10101613
216 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
217 ,LAST_UPDATE_DATE = SYSDATE
218 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
219 WHERE STID.accounting_event_id = p_event_ids(i);
220
221 ---------------------------------------------------------------------
222 l_debug_info := 'Mark prepayment history posted flag';
223 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
224 ---------------------------------------------------------------------
225
226 FORALL i IN 1 .. p_event_ids.count
227 UPDATE ap_prepay_history_all APPH
228 SET POSTED_FLAG = 'S'
229 -- bug 10101613
230 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
231 ,LAST_UPDATE_DATE = SYSDATE
232 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
233 WHERE APPH.accounting_event_id = p_event_ids(i);
234
235 END IF;
236
237 -- COMMIT; bug 7351478
238 ---------------------------------------------------------------------
239 l_debug_info := 'END of procedure '||l_procedure_name;
240 trace(G_LEVEL_PROCEDURE, l_procedure_name, l_debug_info);
241 ---------------------------------------------------------------------
242 EXCEPTION
243 WHEN OTHERS THEN
244 IF (SQLCODE <> -20001) THEN
245 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
246 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
247 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
248 END IF;
249
250 ROLLBACK;
251
252 APP_EXCEPTION.RAISE_EXCEPTION;
253 END lock_documents_autonomous;
254
255 /*============================================================================
256 | PROCEDURE - PREACCOUNTING(PUBLIC)
257 |
258 | DESCRIPTION
259 | This procedure is the AP SLA preaccounting procedure. This procedure
260 | will be called by SLA through an API.
261 |
262 | PRAMETERS
263 | p_application_id:
264 | This parameter is the application ID of the application that the SLA
265 | workflow event is for. This procedure must exit without doing anything
266 | if this parameter is not 200 to ensure that this procedure is only
267 | executed when the workflow event is for AP. This parameter will never
268 | be NULL.
269 | p_ledger_id:
270 | This parameter is the ledger ID of the ledger to account.This
271 | parameter is purely informational. This procedure selects from the
272 | XLA_ENTITY_EVENTS_V view, which does not include events incompatible
273 | with this parameter. This parameter will never be NULL.
274 | p_process_category:
275 | This parameter is the "process category" of the events to account. This
276 | parameter is purely informational. This procedure selects from the
277 | XLA_ENTITY_EVENTS_V view, which does not include events incompatible
278 | with this parameter.Possible values are as following:
279 | +------------+------------------------------------------+
280 | | Value | Meaning |
281 | +------------+------------------------------------------+
282 | | 'Invoices' | process invoices |
283 | | 'Payments' | process payments and reconciled payments |
284 | | 'All' | process everything |
285 | +------------+------------------------------------------+
286 | p_end_date
287 | This parameter is the maximum event date of the events to be processed
288 | in this run of the accounting. This procedure selects from the
289 | XLA_ENTITY_EVENTS_V view, which does not include events incompatible
290 | with this parameter. This parameter will never be NULL.
291 | p_accounting_mode
292 | This parameter is the "accounting mode" that the accounting is being
293 | run in. This parameter will never be NULL.
294 | +-------+------------------------------------------------------------+
295 | | Value | Meaning |
296 | +-------+------------------------------------------------------------+
297 | | 'D' | The accounting is being run in "draft mode". Draft mode is |
298 | | | used to examine what the accounting entries would look for |
299 | | | an event without actually creating the accounting entries. |
300 | | | without actually creating the accounting entries. |
301 | | 'F' | The accounting is being run in "final mode". Final mode is |
302 | | | used to create accounting entries. |
303 | +-------+------------------------------------------------------------+
304 | p_valuation_method
305 | This parameter is unused by AP. This parameter is purely informational.
306 | This procedure selects from the XLA_ENTITY_EVENTS_V view, which does
307 | not include events incompatible with this parameter.
308 | p_security_id_int_1
309 | This parameter is unused by AP.
310 | p_security_id_int_2
311 | This parameter is unused by AP.
312 | p_security_id_int_3
313 | This parameter is unused by AP.
314 | p_security_id_char_1
315 | This parameter is unused by AP.
316 | p_security_id_char_2
317 | This parameter is unused by AP.
318 | p_security_id_char_3
319 | This parameter is unused by AP.
320 | p_report_request_id
321 | This parameter is the concurrent request ID of the concurrent request
322 | that is this run of the accounting. This parameter is used to specify
323 | which events in the XLA_ENTITY_EVENTS_V view are to be accounted in
324 | this run of the accounting. This parameter will never be NULL.
325 | KNOWN ISSUES:
326 |
327 | NOTES:
328 | 1) This procedure is run in final mode and draft mode.
329 | 2) This procedure is run in batch mode but not in document mode.
330 | 3) This procedure is in its own commit cycle.
331 |
332 | MODIFICATION HISTORY
333 | Date Author Description of Change
334 |
335 *===========================================================================*/
336
337 PROCEDURE preaccounting
338 (
339 p_application_id IN NUMBER,
340 p_ledger_id IN INTEGER,
341 p_process_category IN VARCHAR2,
342 p_end_date IN DATE,
343 p_accounting_mode IN VARCHAR2,
344 p_valuation_method IN VARCHAR2,
345 p_security_id_int_1 IN INTEGER,
346 p_security_id_int_2 IN INTEGER,
347 p_security_id_int_3 IN INTEGER,
348 p_security_id_char_1 IN VARCHAR2,
349 p_security_id_char_2 IN VARCHAR2,
350 p_security_id_char_3 IN VARCHAR2,
351 p_report_request_id IN INTEGER
352 )
353 IS
354 l_debug_info VARCHAR2(240);
355 l_procedure_name CONSTANT VARCHAR2(30) :='PRE_ACCOUNTING_PROC';
356 l_curr_calling_sequence VARCHAR2(2000);
357
358 BEGIN
359
360 l_curr_calling_sequence := 'AP_SLA_PROCESSING_PKG.PRE_ACCOUNTING_PROC';
361 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
362 ---------------------------------------------------------------------
363 l_debug_info := 'Begin of procedure '||l_procedure_name;
364 trace(G_LEVEL_PROCEDURE, l_procedure_name, l_debug_info);
365 ---------------------------------------------------------------------
366
367 ---------------------------------------------------------------------
368 -- This procedure should only called by 'AP', whose application id
369 -- is 200. Otherwise exit the procedure.
370 ---------------------------------------------------------------------
371 IF (p_application_id <> 200) THEN
372 RETURN;
373 END IF;
374
375 IF ( p_accounting_mode IS NOT NULL ) THEN
376
377 CASE (p_accounting_mode)
378
379 WHEN ('F') THEN -- p_accounting_mode
380 -----------------------------------------------------------------------
381 -- FINAL MODE
382 l_debug_info := 'p_accounting_mode :=' ||p_accounting_mode;
383 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
384 -----------------------------------------------------------------------
385 NULL;
386 WHEN ('D') THEN -- p_accounting_mode
387 -----------------------------------------------------------------------
388 l_debug_info := 'p_accounting_mode ='||p_accounting_mode;
389 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
390 -- DRAFT MODE
391 -----------------------------------------------------------------------
392 NULL;
393
394 WHEN ('FUNDS_CHECK') THEN -- p_accounting_mode
395
396 -----------------------------------------------------------------------
397 l_debug_info := 'p_accounting_mode ='||p_accounting_mode;
398 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
399 --FUNDS CHECK MODE
400 -----------------------------------------------------------------------
401 NULL;
402
403 WHEN ('FUNDS_RESERVE') THEN -- p_accounting_mode
404
405 -----------------------------------------------------------------------
406 l_debug_info := 'p_accounting_mode ='||p_accounting_mode;
407 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
408 -- FUNDS RESERVE MODE
409 -----------------------------------------------------------------------
410 NULL;
411 ELSE
412 -----------------------------------------------------------------------
413 l_debug_info := 'Wrong p_accounting_mode ='||p_accounting_mode;
414 trace(G_LEVEL_EXCEPTION, l_procedure_name, l_debug_info);
415 -----------------------------------------------------------------------
416 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
417 FND_MESSAGE.SET_TOKEN('ERROR','Wrong p_accounting_mode');
418 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
419 l_curr_calling_sequence);
420 FND_MESSAGE.SET_TOKEN('PARAMETERS',
421 'p_process_category = '||p_process_category
422 || 'p_accounting_mode = '||p_accounting_mode);
423 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
424
425 app_exception.raise_exception();
426
427 END CASE; -- p_accounting_mode
428 END IF; -- END of checking p_accounting_mode
429 -------------------------------------------------------------------------
430 l_debug_info := 'End of procedure '||l_procedure_name;
431 trace(G_LEVEL_PROCEDURE, l_procedure_name, l_debug_info);
432 -------------------------------------------------------------------------
433 EXCEPTION
434
435 WHEN OTHERS THEN
436
437 IF (SQLCODE <> -20001) THEN
438 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
439 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
440 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
441 l_curr_calling_sequence);
442 FND_MESSAGE.SET_TOKEN('PARAMETERS',
443 'p_application_id=' || p_application_id || ' ' ||
444 'p_ledger_id=' || p_ledger_id || ' ' ||
445 'p_process_category=' || p_process_category || ' ' ||
446 'p_end_date=' || p_end_date || ' ' ||
447 'p_accounting_mode=' || p_accounting_mode || ' ' ||
448 'p_valuation_method=' || p_valuation_method || ' ' ||
449 'p_security_id_int_1=' || p_security_id_int_1 || ' ' ||
450 'p_security_id_int_2=' || p_security_id_int_2 || ' ' ||
451 'p_security_id_int_3=' || p_security_id_int_3 || ' ' ||
452 'p_security_id_char_1=' || p_security_id_char_1 || ' ' ||
453 'p_security_id_char_2=' || p_security_id_char_2 || ' ' ||
454 'p_security_id_char_3=' || p_security_id_char_3 || ' ' ||
455 'p_report_request_id=' || p_report_request_id);
456 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
457 END IF;
458
459 app_exception.raise_exception();
460
461 END preaccounting;
462
463
464 /*============================================================================
465 | PROCEDURE - POSTPROCESSING (PUBLIC)
466 |
467 | DESCRIPTION
468 | This procedure is the AP SLA post-processing procedure. This procedure
469 | will be called by SLA thorugh an API.
470 |
471 | PRAMETERS
472 | p_application_id
473 | This parameter is the application ID of the application that the SLA
474 | workflow event is for. This procedure must exit without doing anything
475 | if this parameter is not 200 to ensure that this procedure is only
476 | executed when the workflow event is for AP. This parameter will never
477 | be NULL.
478 | p_accounting_mode
479 | This parameter is the "accounting mode" that the accounting is being
480 | run in. This parameter will never be NULL.
481 | +-------+-----------------------------------------------------------+
482 | | Value | Meaning |
483 | +-------+-----------------------------------------------------------+
484 | | 'D' | The accounting is being run in "draft mode". Draft mode is|
485 | | | used TO examine what the accounting entries would look for|
486 | | | an event without actually creating the accounting entries |
487 | | 'F' | The accounting is being run in "final mode". Final mode is|
488 | | | used to create accounting entries. |
489 | +-------+-----------------------------------------------------------+
490 |
491 | KNOWN ISSUES:
492 |
493 | NOTES:
494 | 1) This procedure is run in final mode and draft mode.
495 | 2) This procedure is run in batch mode and document mode.
496 | 3) This procedure is part of the accounting commit cycle.
497 |
498 | MODIFICATION HISTORY
499 | Date Author Description of Change
500 |
501 *===========================================================================*/
502
503 PROCEDURE postprocessing
504 (
505 p_application_id IN NUMBER,
506 p_accounting_mode IN VARCHAR2
507 )
508 IS
509
510 -----------------------------------------------------------------------------
511 -- The XLA_POST_ACCTG_EVENTS_V view contains only the successfully accounted
512 -- events.
513 -----------------------------------------------------------------------------
514
515 CURSOR l_events_cur IS
516 SELECT XPAE.event_id event_id,
517 XPAE.event_type_code event_type_code,
518 XPAE.SOURCE_ID_INT_1 source_id,
519 GSOB.sla_ledger_cash_basis_flag cash_basis_flag,
520 APSP.when_To_Account_pmt,
521 XPAE.ledger_id ledger_id,
522 XTE.entity_code /* Bug 12560872 */
523 FROM XLA_POST_ACCTG_EVENTS_V XPAE,
524 XLA_TRANSACTION_ENTITIES XTE,
525 GL_SETS_OF_BOOKS GSOB,
526 AP_SYSTEM_PARAMETERS_ALL APSP
527 WHERE XPAE.ledger_id = GSOB.set_of_books_id
528 AND XPAE.entity_id = XTE.entity_id
529 AND XTE.application_id = 200
530 AND XTE.security_id_int_1 = APSP.org_id;
531
532 --bug9377273
533 CURSOR l_events_gt_cur IS
534 SELECT XEG.event_id
535 FROM xla_events_gt XEG
536 WHERE XEG.application_id = 200
537 AND XEG.entity_code = 'AP_PAYMENTS';
538
539 --bug 10412623, removed code introduced in 9936620
540
541 TYPE event_tab_type IS TABLE OF l_events_cur%rowtype INDEX BY PLS_INTEGER;
542 TYPE invIDType IS TABLE OF ap_invoice_lines_all.invoice_id%type INDEX BY PLS_INTEGER;
543 TYPE checkIDType IS TABLE OF ap_checks_all.check_id%type INDEX BY PLS_INTEGER;
544
545 TYPE checkStatusType IS TABLE OF
546 ap_checks_all.status_lookup_code%type INDEX BY PLS_INTEGER;
547
548 l_Status AP_CHECKS_ALL.status_lookup_code%type;
549 l_matched_flag AP_PAYMENT_HISTORY_ALL.matched_flag%type;
550 l_debug_info VARCHAR2(240);
551 l_procedure_name CONSTANT VARCHAR2(30):='POSTPROCESSING_PROC';
552 l_curr_calling_sequence VARCHAR2(2000);
553
554 l_event_rec l_events_cur%ROWTYPE;
555 l_process_list event_tab_type;
556
557 l_event_list l_event_ids_typ;
558 l_accrual_event_ids l_event_ids_typ;
559 l_cash_event_ids l_event_ids_typ;
560 l_prepay_event_list l_event_ids_typ;
561 l_payclear_event_list l_event_ids_typ;
562 l_cash_inv_event_list l_event_ids_typ; /* Bug 12560872 */
563 l_other_event_list l_event_ids_typ;
564
565 l_invID_list invIDType;
566 l_check_status_list checkStatusType;
567 l_check_id_list checkIDType;
568
569 i BINARY_INTEGER := 1;
570 j BINARY_INTEGER := 1;
571 k BINARY_INTEGER := 1;
572 l BINARY_INTEGER := 1; /* Bug 12560872 */
573 m BINARY_INTEGER := 1;
574 n BINARY_INTEGER := 1;
575 ind BINARY_INTEGER := 1;
576 dd BINARY_INTEGER := 1;
577 l_dbi_count NUMBER := 0;
578 l_tax_count NUMBER := 0;
579
580 --Bug 4640244 DBI logging
581 -- bug fix 5663077
582 -- Add the ap_dbi_pkg.r_dbi_key_value_arr() to initialize the plsql table.
583 l_dbi_key_value_list1 ap_dbi_pkg.r_dbi_key_value_arr := ap_dbi_pkg.r_dbi_key_value_arr();
584 l_dbi_key_final_list ap_dbi_pkg.r_dbi_key_value_arr := ap_dbi_pkg.r_dbi_key_value_arr();
585
586 l_tax_dist_id_list1 zx_api_pub.tax_dist_id_tbl_type;
587 l_tax_dist_id_final_list zx_api_pub.tax_dist_id_tbl_type;
588 l_return_status_service VARCHAR2(4000);
589 l_msg_count NUMBER;
590 l_msg_data VARCHAR2(4000);
591 l_event_ids l_event_ids_typ;
592
593 BEGIN
594 l_curr_calling_sequence := 'AP_SLA_PROCESSING_PKG.POSTPROCESSING_PROC';
595 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
596
597 -------------------------------------------------------------------------
598 l_debug_info := 'Begin of procedure '||l_procedure_name;
599 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
600 -------------------------------------------------------------------------
601 ---------------------------------------------------------------------
602 -- This procedure should only called by 'AP', whose application id
603 -- is 200. Otherwise exit the procedure.
604 ---------------------------------------------------------------------
605
606
607 IF (p_application_id <> 200) THEN
608 RETURN;
609 END IF;
610
611 ---------------------------------------------------------------------
612 l_debug_info := 'p_accounting_mode =' ||p_accounting_mode;
613 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
614 ---------------------------------------------------------------------
615 IF ( p_accounting_mode IS NOT NULL ) THEN
616
617 CASE (p_accounting_mode)
618
619 WHEN ('F') THEN -- p_accounting_mode
620 -------------------------------------------------------------------------
621 l_debug_info := 'final mode';
622 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
623 -- FINAL MODE
624 -------------------------------------------------------------------------
625
626 OPEN l_events_cur;
627 LOOP
628 FETCH l_events_cur
629 BULK COLLECT INTO l_process_list LIMIT G_COMMIT_SIZE;
630
631 IF ( l_process_list.COUNT <> 0 ) THEN
632
633 -----------------------------------------------------------------------
634 l_debug_info :=
635 'loop to the events and build list for accrual/cash basis and count=' ||
636 l_process_list.COUNT;
637 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
638 -----------------------------------------------------------------------
639
640 i := 1;
641 j := 1;
642 k := 1;
643 l := 1; /* Bug 12560872 */
644 m := 1;
645 n := 1;
646 ind := 1;
647 dd := 1;
648 l_dbi_count := 0;
649
650 -----------------------------------------------------------------------
651 l_debug_info :=
652 'After initialize all the index numbers';
653 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
654 -----------------------------------------------------------------------
655
656 FOR num IN 1 .. l_process_list.COUNT LOOP
657 l_event_rec := l_process_list(num);
658 l_event_list(num) := l_event_rec.event_id;
659
660
661 IF ( l_event_rec.cash_basis_flag = 'Y' ) THEN
662 -- cash basis
663 -----------------------------------------------------------------------
664 l_debug_info :=
665 'Add one event id to cash event id list and count =' ||
666 l_cash_event_ids.COUNT;
667 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
668 -----------------------------------------------------------------------
669 l_cash_event_ids(n) := l_event_rec.event_id;
670 n := n+1;
671
672 --------------------------------------------------------------------
673 l_debug_info :=
674 'cash basis event:' || l_event_rec.event_id ||
675 'cash basis event_type_code:' || l_event_rec.event_type_code ||
676 'source_id:'|| l_event_rec.source_id ||
677 'cash_basis_flag:'|| l_event_rec.cash_basis_flag ||
678 'when_To_Account_pmt:'|| l_event_rec.when_To_Account_pmt ||
679 'cash basis ledger_id:'|| l_event_rec.ledger_id ||
680 'entity code:'|| l_event_rec.entity_code; /* Bug 12560872 */
681 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
682 --------------------------------------------------------------------
683 IF l_event_rec.event_type_code IN ('PREPAYMENT APPLIED',
684 'PREPAYMENT UNAPPLIED') THEN
685 l_prepay_event_list(j) := l_event_rec.event_id;
686 l_invID_list(j) := l_event_rec.source_id;
687 j := j+1;
688 ELSIF (l_event_rec.When_to_Account_Pmt = 'ALWAYS' AND
689 l_event_rec.event_type_code in ('PAYMENT CANCELLED',
690 'PAYMENT ADJUSTED'))
691 OR (l_event_rec.When_to_Account_Pmt <> 'ALWAYS' AND
692 l_event_rec.event_type_code in ( 'PAYMENT CLEARED',
693 'PAYMENT UNCLEARED')) THEN
694 l_payclear_event_list(k) := l_event_rec.event_id;
695 k := k+1;
696 ELSIF (l_event_rec.entity_code = 'AP_INVOICES') THEN /* Bug 12560872 */
697 l_cash_inv_event_list(l) := l_event_rec.event_id;
698 l := l+1;
699
700 ELSE
701 l_other_event_list(m) := l_event_rec.event_id;
702 m := m+1;
703 END IF; -- end l_event_rec.event_type_code
704
705
706 ELSE
707 -- accrual basis event
708 --------------------------------------------------------------------
709 l_debug_info :=
710 'accrual basis event:' || l_event_rec.event_id ||
711 'accrual basis event_type_code:' || l_event_rec.event_type_code ||
712 'source_id:'|| l_event_rec.source_id ||
713 'cash_basis_flag:'|| l_event_rec.cash_basis_flag ||
714 'when_To_Account_pmt:'|| l_event_rec.when_To_Account_pmt ||
715 'accrual basis ledger_id:'|| l_event_rec.ledger_id;
716 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
717 --------------------------------------------------------------------
718 l_accrual_event_ids(i) := l_event_rec.event_id;
719 i := i+1;
720
721 -----------------------------------------------------------------------
722 l_debug_info :=
723 'After add one event id to accrual event id list and count =' ||
724 l_accrual_event_ids.COUNT;
725 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
726 -----------------------------------------------------------------------
727
728 END IF;
729
730 -- loop through the whole list to build list to update check
731 IF (l_event_rec.event_type_code in ('PAYMENT CLEARED',
732 'PAYMENT UNCLEARED')) THEN
733 -----------------------------------------------------------------------
734 l_debug_info :=
735 'process all Events building list to update AP_CHECKS for event tyep' ||
736 l_event_rec.event_type_code ||
737 'event_id = ' || l_event_rec.event_id;
738 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
739 -----------------------------------------------------------------------
740
741 SELECT APH.matched_flag, AC.status_lookup_code
742 INTO l_matched_flag, l_status
743 FROM AP_Payment_History_all APH, AP_CHECKS_all AC
744 WHERE AC.check_id = APH.check_id
745 AND APH.accounting_Event_id = l_event_rec.event_id;
746
747 IF l_status in ('RECONCILED UNACCOUNTED',
748 'CLEARED BUT UNACCOUNTED') THEN
749
750 l_check_id_list(ind) := l_event_rec.source_id;
751
752 IF( l_matched_flag = 'Y' ) THEN
753
754 l_check_status_list(ind) := 'RECONCILED';
755 ELSE
756
757 IF ( l_status = 'RECONCILED UNACCOUNTED' ) THEN
758 l_check_status_list(ind) := 'RECONCILED';
759 ELSE
760 l_check_status_list(ind) := 'CLEARED';
761 END IF; -- end of l_status
762 END IF; -- end of l_matched_flag
763 ind := ind+1;
764 END IF; -- end of first check l_status
765
766 END IF; -- end of check event type to build check list
767
768 END LOOP;
769
770 -- accrual basis
771 -----------------------------------------------------------------------
772 l_debug_info :=
773 'start to update for accrual basis list and count=' ||
774 l_accrual_event_ids.COUNT;
775 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
776 -----------------------------------------------------------------------
777
778
779 FORALL num in 1 .. l_accrual_event_ids.COUNT
780 UPDATE AP_Invoice_Payments_all
781 SET Posted_Flag = 'Y', Accrual_Posted_Flag = 'Y'
782 -- bug 10101613
783 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
784 ,LAST_UPDATE_DATE = SYSDATE
785 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
786 WHERE Accounting_Event_ID = l_accrual_event_ids(num);
787
788 FORALL num in 1 .. l_accrual_event_ids.COUNT
789 UPDATE AP_Invoice_Distributions_all
790 SET Posted_Flag = 'Y', Accrual_Posted_Flag = 'Y'
791 -- bug 10101613
792 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
793 ,LAST_UPDATE_DATE = SYSDATE
794 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
795 WHERE Accounting_Event_ID = l_accrual_event_ids(num)
796 RETURNING invoice_distribution_id,detail_tax_dist_id
797 BULK COLLECT INTO l_dbi_key_value_list1,
798 l_tax_dist_id_list1;
799
800 l_dbi_key_final_list := l_dbi_key_value_list1;
801 l_tax_dist_id_final_list := l_tax_dist_id_list1;
802 -- initialize the collection
803 l_dbi_key_value_list1.delete;
804 l_tax_dist_id_list1.delete;
805 -----------------------------------------------------------------------
806 l_debug_info :=
807 'Initialize the final dbi list with l_dbi_key_value_list1 and count1=' ||
808 l_dbi_key_final_list.COUNT;
809 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
810 -----------------------------------------------------------------------
811
812 -- bug fix 6975868 begin
813 FORALL num in 1 .. l_accrual_event_ids.COUNT
814 UPDATE ap_self_assessed_tax_dist_all
815 SET Posted_Flag = 'Y', Accrual_Posted_Flag = 'Y'
816 -- bug 10101613
817 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
818 ,LAST_UPDATE_DATE = SYSDATE
819 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
820 WHERE Accounting_Event_ID = l_accrual_event_ids(num)
821 RETURNING detail_tax_dist_id
822 BULK COLLECT INTO l_tax_dist_id_list1;
823
824 -----------------------------------------------------------------------
825 l_debug_info :=
826 'Append the final tax deail list with l_tax_dist_id_list1 from self_assessed_tax dists';
827 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
828 -----------------------------------------------------------------------
829
830 IF ( l_tax_dist_id_list1.COUNT <> 0 ) THEN
831 -----------------------------------------------------------------------
832 l_debug_info :=
833 'l_tax_dist_id_list1 is not null';
834 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
835 -----------------------------------------------------------------------
836 l_tax_count := l_tax_dist_id_final_list.COUNT;
837
838 FOR num in 1 .. l_tax_dist_id_list1.COUNT LOOP
839
840 -----------------------------------------------------------------------
841 l_debug_info :=
842 'inside the loop to build l_tax_dist_final_list';
843 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
844 -----------------------------------------------------------------------
845 dd := l_tax_count + num;
846 l_tax_dist_id_final_list(dd) := l_tax_dist_id_list1(num);
847 END LOOP;
848 l_tax_dist_id_list1.delete;
849 END IF;
850
851
852 -----------------------------------------------------------------------
853 l_debug_info :=
854 'After append self_assessed tax dists to the final tax list and the count ='||
855 l_tax_dist_id_final_list.COUNT;
856 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
857 -----------------------------------------------------------------------
858
859 -- bug fix 6975868 end
860
861 -- cash basis
862 -----------------------------------------------------------------------
863 l_debug_info :=
864 'start to process event list for CASH basis list and count=' ||
865 l_cash_event_ids.COUNT;
866 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
867 -----------------------------------------------------------------------
868
869 FORALL num in 1 .. l_cash_event_ids.COUNT
870 UPDATE AP_Invoice_Payments_all
871 SET Posted_Flag = 'Y', Accrual_Posted_Flag = 'N', Cash_Posted_Flag = 'Y'
872 -- bug 10101613
873 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
874 ,LAST_UPDATE_DATE = SYSDATE
875 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
876 WHERE Accounting_Event_ID = l_cash_event_ids(num);
877
878 -- update for prepay event
879
880 -----------------------------------------------------------------------
881 l_debug_info :=
882 'update for cash basis prepay event list by event and count=' ||
883 l_prepay_event_list.COUNT;
884 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
885 -----------------------------------------------------------------------
886
887 FORALL num in 1 .. l_prepay_event_list.COUNT
888 UPDATE AP_Invoice_Distributions_ALL AID
889 SET AID.Posted_Flag = 'Y',
890 AID.Accrual_Posted_Flag = 'N',
891 AID.Cash_Posted_Flag = 'Y'
892 -- bug 10101613
893 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
894 ,LAST_UPDATE_DATE = SYSDATE
895 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
896 WHERE AID.Accounting_Event_ID = l_prepay_event_list(num)
897 RETURNING invoice_distribution_id,detail_tax_dist_id
898 BULK COLLECT INTO l_dbi_key_value_list1,
899 l_tax_dist_id_list1;
900
901
902 -----------------------------------------------------------------------
903 l_debug_info :=
904 'Append the final dbi list with l_dbi_key_value_list1';
905 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
906 -----------------------------------------------------------------------
907
908 IF ( l_dbi_key_value_list1.COUNT <> 0 ) THEN
909 -----------------------------------------------------------------------
910 l_debug_info :=
911 'l_dbi_key_value_list1 is not null';
912 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
913 -----------------------------------------------------------------------
914 l_dbi_count := l_dbi_key_final_list.COUNT;
915 l_dbi_key_final_list.extend(l_dbi_key_value_list1.COUNT); -- bug fix 5663077
916
917 FOR num in 1 .. l_dbi_key_value_list1.COUNT LOOP
918
919 -----------------------------------------------------------------------
920 l_debug_info :=
921 'inside the loop to build l_dbi_key_final_list';
922 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
923 -----------------------------------------------------------------------
924 dd := l_dbi_count + num;
925 l_dbi_key_final_list(dd) := l_dbi_key_value_list1(num);
926 END LOOP;
927 -- initialize
928 l_dbi_key_value_list1.delete;
929 END IF;
930
931 -----------------------------------------------------------------------
932 l_debug_info :=
933 'Append the final tax deail list with l_tax_dist_id_list1';
934 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
935 -----------------------------------------------------------------------
936
937 IF ( l_tax_dist_id_list1.COUNT <> 0 ) THEN
938 -----------------------------------------------------------------------
939 l_debug_info :=
940 'l_tax_dist_id_list1 is not null';
941 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
942 -----------------------------------------------------------------------
943 l_tax_count := l_tax_dist_id_final_list.COUNT;
944
945 FOR num in 1 .. l_tax_dist_id_list1.COUNT LOOP
946
947 -----------------------------------------------------------------------
948 l_debug_info :=
949 'inside the loop to build l_tax_dist_final_list';
950 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
951 -----------------------------------------------------------------------
952 dd := l_tax_count + num;
953 l_tax_dist_id_final_list(dd) := l_tax_dist_id_list1(num);
954 END LOOP;
955 l_tax_dist_id_list1.delete;
956 END IF;
957
958
959 -----------------------------------------------------------------------
960 l_debug_info :=
961 'After append the final dbi list and the count' ||
962 l_dbi_key_final_list.COUNT ||
963 'After append the final tas list and the count ='||
964 l_tax_dist_id_final_list.COUNT;
965 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
966 -----------------------------------------------------------------------
967
968 -- bug fix 6975868 begin
969 -----------------------------------------------------------------------
970 l_debug_info :=
971 'update self_assessed tax dists for cash basis prepay event list by event and count=' ||
972 l_prepay_event_list.COUNT;
973 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
974 -----------------------------------------------------------------------
975
976 FORALL num in 1 .. l_prepay_event_list.COUNT
977 UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL STID
978 SET STID.Posted_Flag = 'Y',
979 STID.Accrual_Posted_Flag = 'N',
980 STID.Cash_Posted_Flag = 'Y'
981 -- bug 10101613
982 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
983 ,LAST_UPDATE_DATE = SYSDATE
984 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
985 WHERE STID.Accounting_Event_ID = l_prepay_event_list(num)
986 RETURNING detail_tax_dist_id
987 BULK COLLECT INTO l_tax_dist_id_list1;
988
989 -----------------------------------------------------------------------
990 l_debug_info :=
991 'Append the final tax deail list with l_tax_dist_id_list1 from self_assessed tax dists';
992 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
993 -----------------------------------------------------------------------
994
995 IF ( l_tax_dist_id_list1.COUNT <> 0 ) THEN
996 -----------------------------------------------------------------------
997 l_debug_info :=
998 'l_tax_dist_id_list1 is not null';
999 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1000 -----------------------------------------------------------------------
1001 l_tax_count := l_tax_dist_id_final_list.COUNT;
1002
1003 FOR num in 1 .. l_tax_dist_id_list1.COUNT LOOP
1004
1005 -----------------------------------------------------------------------
1006 l_debug_info :=
1007 'inside the loop to build l_tax_dist_final_list';
1008 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1009 -----------------------------------------------------------------------
1010 dd := l_tax_count + num;
1011 l_tax_dist_id_final_list(dd) := l_tax_dist_id_list1(num);
1012 END LOOP;
1013 l_tax_dist_id_list1.delete;
1014 END IF;
1015
1016
1017 -----------------------------------------------------------------------
1018 l_debug_info :=
1019 'After append self assessed tax dists to the final tas list and the count ='||
1020 l_tax_dist_id_final_list.COUNT;
1021 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1022 -----------------------------------------------------------------------
1023
1024 -- bug fix 6975868 end
1025
1026 -----------------------------------------------------------------------
1027 l_debug_info :=
1028 'Update the all payment history records POSTED_FLAGs.';
1029 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1030 -----------------------------------------------------------------------
1031
1032 FORALL num in 1 .. l_event_list.COUNT
1033 UPDATE ap_payment_history_all APH
1034 SET APH.posted_flag = 'Y'
1035 -- bug 10101613
1036 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1037 ,LAST_UPDATE_DATE = SYSDATE
1038 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1039 WHERE APH.accounting_event_id = l_event_list(num);
1040
1041 -----------------------------------------------------------------------
1042 l_debug_info :=
1043 'Update the prepayment history records POSTED_FLAGs';
1044 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1045 -----------------------------------------------------------------------
1046
1047 FORALL num in 1 .. l_event_list.COUNT
1048 UPDATE ap_prepay_history_all APPH
1049 SET APPH.posted_flag = 'Y'
1050 -- bug 10101613
1051 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1052 ,LAST_UPDATE_DATE = SYSDATE
1053 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1054 WHERE APPH.accounting_event_id = l_event_list(num);
1055
1056 -----------------------------------------------------------------------
1057 l_debug_info :=
1058 'Update the check staus for l_check_status_list and count=' ||
1059 l_check_id_list.COUNT;
1060 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1061 -----------------------------------------------------------------------
1062 FORALL num in 1 .. l_check_id_list.COUNT
1063 UPDATE AP_Checks_All
1064 SET status_lookup_code = l_check_status_list(num)
1065 -- bug 10101613
1066 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1067 ,LAST_UPDATE_DATE = SYSDATE
1068 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1069 WHERE check_id = l_check_id_list(num)
1070 --Bug 8973135 Start
1071 AND not exists (SELECT 1 FROM ap_payment_history_all aph,
1072 ap_system_parameters_all asp
1073 WHERE aph.check_id=l_check_id_list(num)
1074 AND posted_flag<>'Y'
1075 AND aph.org_id = asp.org_id
1076 AND (nvl(asp.when_to_account_pmt,'ALWAYS') ='ALWAYS'
1077 OR (asp.when_to_account_pmt ='CLEARING ONLY'
1078 AND aph.transaction_type in ('PAYMENT CLEARING',
1079 'PAYMENT UNCLEARING','PAYMENT CLEARING ADJUSTED',
1080 'PAYMENT UNCLEARING ADJUSTED'))));
1081 --Bug 8973135 End
1082
1083 -----------------------------------------------------------------------
1084 l_debug_info :=
1085 'update for cash basis prepay event list by invoice_id and count=' ||
1086 l_prepay_event_list.COUNT;
1087 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1088 -----------------------------------------------------------------------
1089 FORALL num in 1 .. l_prepay_event_list.COUNT
1090 UPDATE AP_Invoice_Distributions_all AID
1091 SET AID.Posted_Flag = 'Y', AID.Accrual_Posted_Flag = 'N' , --9698155
1092 AID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
1093 l_prepay_event_list(num),
1094 AID.Invoice_Distribution_ID,
1095 AID.Amount,
1096 l_curr_calling_sequence)
1097 -- bug 10101613
1098 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1099 ,LAST_UPDATE_DATE = SYSDATE
1100 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1101 WHERE AID.Invoice_ID = l_invID_list(num)
1102 AND AID.Prepay_Distribution_ID IS NULL
1103 AND AID.prepay_tax_parent_id IS NULL
1104 AND nvl(AID.cancellation_flag,'N') <> 'Y'
1105 RETURNING invoice_distribution_id,detail_tax_dist_id
1106 BULK COLLECT INTO l_dbi_key_value_list1,
1107 l_tax_dist_id_list1;
1108
1109 -----------------------------------------------------------------------
1110 l_debug_info :=
1111 'Append the final dbi list with l_dbi_key_value_list1 and count3';
1112 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1113 -----------------------------------------------------------------------
1114 IF ( l_dbi_key_value_list1.COUNT <> 0 ) THEN
1115 l_dbi_count := l_dbi_key_final_list.COUNT;
1116 l_dbi_key_final_list.extend(l_dbi_key_value_list1.COUNT); -- bug fix 5663077
1117
1118 FOR num in 1 .. l_dbi_key_value_list1.COUNT LOOP
1119 dd := l_dbi_count + num;
1120 l_dbi_key_final_list(dd) := l_dbi_key_value_list1(num);
1121 END LOOP;
1122 -- initialize
1123 l_dbi_key_value_list1.delete;
1124 END IF;
1125
1126 -----------------------------------------------------------------------
1127 l_debug_info :=
1128 'Append the final tax list with l_tax_dist_id_list1 and count3';
1129 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1130 -----------------------------------------------------------------------
1131 IF ( l_tax_dist_id_list1.COUNT <> 0 ) THEN
1132 l_tax_count := l_tax_dist_id_final_list.COUNT;
1133 FOR num in 1 .. l_tax_dist_id_list1.COUNT LOOP
1134 dd := l_tax_count + num;
1135 l_tax_dist_id_final_list(dd) := l_tax_dist_id_list1(num);
1136 END LOOP;
1137 l_tax_dist_id_list1.delete;
1138 END IF;
1139
1140 -----------------------------------------------------------------------
1141 l_debug_info :=
1142 'After append the final dbi list and the count=' ||
1143 l_dbi_key_final_list.COUNT;
1144 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1145 -----------------------------------------------------------------------
1146
1147 -- bug fix 6975868 begin
1148 -----------------------------------------------------------------------
1149 l_debug_info :=
1150 'update for cash basis prepay event list by invoice_id and count=' ||
1151 l_prepay_event_list.COUNT;
1152 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1153 -----------------------------------------------------------------------
1154 FORALL num in 1 .. l_prepay_event_list.COUNT
1155 UPDATE ap_self_assessed_tax_dist_all STID
1156 SET STID.Posted_Flag = 'Y', STID.Accrual_Posted_Flag = 'N' , --9698155
1157 STID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
1158 l_prepay_event_list(num),
1159 STID.Invoice_Distribution_ID,
1160 STID.Amount,
1161 l_curr_calling_sequence)
1162 -- bug 10101613
1163 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1164 ,LAST_UPDATE_DATE = SYSDATE
1165 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1166 WHERE STID.Invoice_ID = l_invID_list(num)
1167 AND STID.Prepay_Distribution_ID IS NULL
1168 AND nvl(STID.cancellation_flag,'N') <> 'Y'
1169 RETURNING detail_tax_dist_id
1170 BULK COLLECT INTO l_tax_dist_id_list1;
1171
1172 -----------------------------------------------------------------------
1173 l_debug_info :=
1174 'Append the final tax list with l_tax_dist_id_list1 from self_assessed tax dists and count3';
1175 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1176 -----------------------------------------------------------------------
1177 IF ( l_tax_dist_id_list1.COUNT <> 0 ) THEN
1178 l_tax_count := l_tax_dist_id_final_list.COUNT;
1179 FOR num in 1 .. l_tax_dist_id_list1.COUNT LOOP
1180 dd := l_tax_count + num;
1181 l_tax_dist_id_final_list(dd) := l_tax_dist_id_list1(num);
1182 END LOOP;
1183 l_tax_dist_id_list1.delete;
1184 END IF;
1185
1186 -----------------------------------------------------------------------
1187 l_debug_info :=
1188 'After append the l_tax_dist_id_final_list from self_assessed tax dists and the count=' ||
1189 l_tax_dist_id_final_list.COUNT;
1190 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1191 -----------------------------------------------------------------------
1192
1193 -- bug fix 6975868 end
1194
1195 -----------------------------------------------------------------------
1196 l_debug_info :=
1197 'update for cash basis payclear event list and count=' ||
1198 l_payclear_event_list.COUNT;
1199 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1200 -----------------------------------------------------------------------
1201 FORALL num in 1 .. l_payclear_event_list.COUNT
1202 UPDATE AP_Invoice_Distributions_all AID --Bug 4659793
1203 SET AID.Posted_Flag = 'Y', AID.Accrual_Posted_Flag = 'N' ,
1204 AID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
1205 l_payclear_event_list(num),
1206 AID.Invoice_Distribution_ID,
1207 AID.Amount,
1208 l_curr_calling_sequence),
1209 AID.amount_to_post = AID.amount
1210 -nvl(Get_Amt_Already_Accounted(
1211 l_payclear_event_list(num),
1212 -1,
1213 AID.invoice_distribution_id,
1214 'SQL'),0)
1215 -- bug 10101613
1216 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1217 ,LAST_UPDATE_DATE = SYSDATE
1218 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1219 WHERE AID.Invoice_ID IN (SELECT distinct AIP.invoice_id
1220 FROM Ap_Invoice_Payments_All AIP,
1221 Ap_Payment_History_All APH --bug 9151717
1222 WHERE AIP.check_id = APH.check_id
1223 AND APH.Accounting_Event_ID
1224 = l_payclear_event_list(num))
1225 AND AID.Prepay_Distribution_ID IS NULL
1226 AND AID.prepay_tax_parent_id IS NULL
1227 AND nvl(AID.cancellation_flag,'N') <> 'Y' -- Bug 2587500
1228 RETURNING invoice_distribution_id,detail_tax_dist_id
1229 BULK COLLECT INTO l_dbi_key_value_list1,
1230 l_tax_dist_id_list1;
1231
1232 -----------------------------------------------------------------------
1233 l_debug_info :=
1234 'Append the final dbi list with l_dbi_key_value_list1 and count4';
1235 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1236 -----------------------------------------------------------------------
1237
1238 IF ( l_dbi_key_value_list1.COUNT <> 0 ) THEN
1239 l_dbi_count := l_dbi_key_final_list.COUNT;
1240 l_dbi_key_final_list.extend(l_dbi_key_value_list1.COUNT); -- bug fix 5663077
1241
1242 FOR num in 1 .. l_dbi_key_value_list1.COUNT LOOP
1243 dd := l_dbi_count + num;
1244 l_dbi_key_final_list(dd) := l_dbi_key_value_list1(num);
1245 END LOOP;
1246 -- initialize
1247 l_dbi_key_value_list1.delete;
1248 END IF;
1249
1250 -----------------------------------------------------------------------
1251 l_debug_info :=
1252 'Append the final tax list with l_tax_dist_id_list1 and count4';
1253 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1254 -----------------------------------------------------------------------
1255 IF ( l_tax_dist_id_list1.COUNT <> 0 ) THEN
1256 l_tax_count := l_tax_dist_id_final_list.COUNT;
1257 FOR num in 1 .. l_tax_dist_id_list1.COUNT LOOP
1258 dd := l_tax_count + num;
1259 l_tax_dist_id_final_list(dd) := l_tax_dist_id_list1(num);
1260 END LOOP;
1261 l_tax_dist_id_list1.delete;
1262 END IF;
1263
1264 -----------------------------------------------------------------------
1265 l_debug_info :=
1266 'After append the final dbi list and the count=' ||
1267 l_dbi_key_final_list.COUNT;
1268 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1269 -----------------------------------------------------------------------
1270
1271 -- bug fix 6975868 begin
1272 -----------------------------------------------------------------------
1273 l_debug_info :=
1274 'update self_assessed tax dists for cash basis payclear event list and count=' ||
1275 l_payclear_event_list.COUNT;
1276 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1277 -----------------------------------------------------------------------
1278 FORALL num in 1 .. l_payclear_event_list.COUNT
1279 UPDATE ap_self_assessed_tax_dist_all STID
1280 SET STID.Posted_Flag = 'Y', STID.Accrual_Posted_Flag = 'N' ,
1281 STID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
1282 l_payclear_event_list(num),
1283 STID.Invoice_Distribution_ID,
1284 STID.Amount,
1285 l_curr_calling_sequence)
1286 -- bug 10101613
1287 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1288 ,LAST_UPDATE_DATE = SYSDATE
1289 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1290 WHERE STID.Invoice_ID IN (SELECT distinct AIP.invoice_id
1291 FROM Ap_Invoice_Payments_All AIP,
1292 Ap_Payment_History_All APH --bug 9151717
1293 WHERE AIP.check_id = APH.check_id
1294 AND APH.Accounting_Event_ID
1295 = l_payclear_event_list(num))
1296 AND STID.Prepay_Distribution_ID IS NULL
1297 AND nvl(STID.cancellation_flag,'N') <> 'Y'
1298 RETURNING detail_tax_dist_id
1299 BULK COLLECT INTO l_tax_dist_id_list1;
1300
1301 -----------------------------------------------------------------------
1302 l_debug_info :=
1303 'Append the final tax list with l_tax_dist_id_list1 from self_assessed tax dists and count4';
1304 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1305 -----------------------------------------------------------------------
1306 IF ( l_tax_dist_id_list1.COUNT <> 0 ) THEN
1307 l_tax_count := l_tax_dist_id_final_list.COUNT;
1308 FOR num in 1 .. l_tax_dist_id_list1.COUNT LOOP
1309 dd := l_tax_count + num;
1310 l_tax_dist_id_final_list(dd) := l_tax_dist_id_list1(num);
1311 END LOOP;
1312 l_tax_dist_id_list1.delete;
1313 END IF;
1314
1315 -----------------------------------------------------------------------
1316 l_debug_info :=
1317 'After append the l_tax_dist_id_final_list from self_assessed tax dists and the count=' ||
1318 l_tax_dist_id_final_list.COUNT;
1319 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1320 -----------------------------------------------------------------------
1321
1322 -- bug fix 6975868 end
1323
1324 -- update for other payment accounting options
1325 -----------------------------------------------------------------------
1326 l_debug_info :=
1327 'update for cash basis other payment event list and count=' ||
1328 l_other_event_list.COUNT;
1329 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1330 -----------------------------------------------------------------------
1331 FORALL num in 1 .. l_other_event_list.COUNT
1332 UPDATE AP_Invoice_Distributions_all AID --Bug 4659793
1333 SET AID.Posted_Flag = 'Y', AID.Accrual_Posted_Flag = 'N' ,
1334 AID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
1335 l_other_event_list(num),
1336 AID.Invoice_Distribution_ID,
1337 AID.Amount,
1338 l_curr_calling_sequence)
1339 -- bug 10101613
1340 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1341 ,LAST_UPDATE_DATE = SYSDATE
1342 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1343 WHERE AID.Invoice_ID IN (SELECT AIP.invoice_id
1344 FROM Ap_Invoice_Payments_All AIP
1345 WHERE AIP.Accounting_Event_ID
1346 = l_other_event_list(num))
1347 AND AID.Prepay_Distribution_ID IS NULL
1348 AND AID.prepay_tax_parent_id IS NULL
1349 AND nvl(AID.cancellation_flag,'N') <> 'Y'
1350 RETURNING invoice_distribution_id,detail_tax_dist_id
1351 BULK COLLECT INTO l_dbi_key_value_list1,
1352 l_tax_dist_id_list1;
1353
1354 -----------------------------------------------------------------------
1355 l_debug_info :=
1356 'Append the final dbi list with l_dbi_key_value_list1 and count5';
1357 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1358 -----------------------------------------------------------------------
1359 IF ( l_dbi_key_value_list1.COUNT <> 0 ) THEN
1360 l_dbi_count := l_dbi_key_final_list.COUNT;
1361 l_dbi_key_final_list.extend(l_dbi_key_value_list1.COUNT); -- bug fix 5663077
1362
1363 FOR num in 1 .. l_dbi_key_value_list1.COUNT LOOP
1364 dd := l_dbi_count + num;
1365 l_dbi_key_final_list(dd) := l_dbi_key_value_list1(num);
1366 END LOOP;
1367 -- initialize
1368 l_dbi_key_value_list1.delete;
1369 END IF;
1370
1371 -----------------------------------------------------------------------
1372 l_debug_info :=
1373 'Append the final tax list with l_tax_dist_id_list1 and count5';
1374 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1375 -----------------------------------------------------------------------
1376 IF ( l_tax_dist_id_list1.COUNT <> 0 ) THEN
1377 l_tax_count := l_tax_dist_id_final_list.COUNT;
1378 FOR num in 1 .. l_tax_dist_id_list1.COUNT LOOP
1379 dd := l_tax_count + num;
1380 l_tax_dist_id_final_list(dd) := l_tax_dist_id_list1(num);
1381 END LOOP;
1382 l_tax_dist_id_list1.delete;
1383 END IF;
1384
1385
1386 -----------------------------------------------------------------------
1387 l_debug_info :=
1388 'After append the final dbi list and the count=' ||
1389 l_dbi_key_final_list.COUNT;
1390 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1391 -----------------------------------------------------------------------
1392 -- bug fix 6975868 begin
1393
1394 -----------------------------------------------------------------------
1395 l_debug_info :=
1396 'update self_assessed tax dists for cash basis other payment event list and count=' ||
1397 l_other_event_list.COUNT;
1398 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1399 -----------------------------------------------------------------------
1400 FORALL num in 1 .. l_other_event_list.COUNT
1401 UPDATE ap_self_assessed_tax_dist_all STID
1402 SET STID.Posted_Flag = 'Y', STID.Accrual_Posted_Flag = 'N' ,
1403 STID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
1404 l_other_event_list(num),
1405 STID.Invoice_Distribution_ID,
1406 STID.Amount,
1407 l_curr_calling_sequence)
1408 -- bug 10101613
1409 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1410 ,LAST_UPDATE_DATE = SYSDATE
1411 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1412 WHERE STID.Invoice_ID IN (SELECT AIP.invoice_id
1413 FROM Ap_Invoice_Payments_All AIP
1414 WHERE AIP.Accounting_Event_ID
1415 = l_other_event_list(num))
1416 AND STID.Prepay_Distribution_ID IS NULL
1417 AND nvl(STID.cancellation_flag,'N') <> 'Y'
1418 RETURNING detail_tax_dist_id
1419 BULK COLLECT INTO l_tax_dist_id_list1;
1420
1421 -----------------------------------------------------------------------
1422 l_debug_info :=
1423 'Append the final tax list with l_tax_dist_id_list1 from self_assessed tax dists and count5';
1424 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1425 -----------------------------------------------------------------------
1426 IF ( l_tax_dist_id_list1.COUNT <> 0 ) THEN
1427 l_tax_count := l_tax_dist_id_final_list.COUNT;
1428 FOR num in 1 .. l_tax_dist_id_list1.COUNT LOOP
1429 dd := l_tax_count + num;
1430 l_tax_dist_id_final_list(dd) := l_tax_dist_id_list1(num);
1431 END LOOP;
1432 l_tax_dist_id_list1.delete;
1433 END IF;
1434
1435 -----------------------------------------------------------------------
1436 l_debug_info :=
1437 'After append the l_tax_dist_id_final_list from self_assessed tax dists and the count=' ||
1438 l_tax_dist_id_final_list.COUNT;
1439 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1440 -----------------------------------------------------------------------
1441 -- bug fix 6975868 end
1442 /*Bug 12560872 start */
1443
1444 FORALL num in 1 .. l_cash_inv_event_list.COUNT
1445 UPDATE AP_Invoice_Distributions_all AID
1446 SET AID.Posted_Flag = 'N',
1447 AID.Accrual_Posted_Flag = 'N' ,
1448 AID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
1449 l_cash_inv_event_list(num),
1450 AID.Invoice_Distribution_ID,
1451 AID.Amount,
1452 l_curr_calling_sequence)
1453 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1454 ,LAST_UPDATE_DATE = SYSDATE
1455 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1456 WHERE AID.accounting_event_id = l_cash_inv_event_list(num)
1457 AND AID.Prepay_Distribution_ID IS NULL
1458 AND AID.prepay_tax_parent_id IS NULL
1459 RETURNING invoice_distribution_id,detail_tax_dist_id
1460 BULK COLLECT INTO l_dbi_key_value_list1,
1461 l_tax_dist_id_list1;
1462
1463 /*Bug 12560872 end */
1464 -----------------------------------------------------------------------
1465 l_debug_info :=
1466 'Append the final dbi list with l_dbi_key_value_list1 and count5';
1467 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1468 -----------------------------------------------------------------------
1469 IF ( l_dbi_key_value_list1.COUNT <> 0 ) THEN
1470 l_dbi_count := l_dbi_key_final_list.COUNT;
1471 l_dbi_key_final_list.extend(l_dbi_key_value_list1.COUNT); -- bug fix 5663077
1472
1473 FOR num in 1 .. l_dbi_key_value_list1.COUNT LOOP
1474 dd := l_dbi_count + num;
1475 l_dbi_key_final_list(dd) := l_dbi_key_value_list1(num);
1476 END LOOP;
1477 -- initialize
1478 l_dbi_key_value_list1.delete;
1479 END IF;
1480
1481 -----------------------------------------------------------------------
1482 l_debug_info :=
1483 'Append the final tax list with l_tax_dist_id_list1 and count5';
1484 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1485 -----------------------------------------------------------------------
1486 IF ( l_tax_dist_id_list1.COUNT <> 0 ) THEN
1487 l_tax_count := l_tax_dist_id_final_list.COUNT;
1488 FOR num in 1 .. l_tax_dist_id_list1.COUNT LOOP
1489 dd := l_tax_count + num;
1490 l_tax_dist_id_final_list(dd) := l_tax_dist_id_list1(num);
1491 END LOOP;
1492 l_tax_dist_id_list1.delete;
1493 END IF;
1494
1495 -----------------------------------------------------------------------
1496 l_debug_info :=
1497 'After append the final dbi list and the count=' ||
1498 l_dbi_key_final_list.COUNT;
1499 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1500 -----------------------------------------------------------------------
1501 -----------------------------------------------------------------------
1502 l_debug_info :=
1503 'update self_assessed tax dists for cash basis other payment event list and count=' ||
1504 l_other_event_list.COUNT;
1505 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1506 -----------------------------------------------------------------------
1507 FORALL num in 1 .. l_cash_inv_event_list.COUNT
1508 UPDATE ap_self_assessed_tax_dist_all STID
1509 SET STID.Posted_Flag = 'Y',
1510 STID.Accrual_Posted_Flag = 'N' ,
1511 STID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
1512 l_cash_inv_event_list(num),
1513 STID.Invoice_Distribution_ID,
1514 STID.Amount,
1515 l_curr_calling_sequence)
1516 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1517 ,LAST_UPDATE_DATE = SYSDATE
1518 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1519 WHERE STID.accounting_event_id = l_cash_inv_event_list(num)
1520 AND STID.Prepay_Distribution_ID IS NULL
1521 RETURNING detail_tax_dist_id
1522 BULK COLLECT INTO l_tax_dist_id_list1;
1523 -----------------------------------------------------------------------
1524 l_debug_info :=
1525 'Append the final tax list with l_tax_dist_id_list1 from self_assessed tax dists and count5';
1526 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1527 -----------------------------------------------------------------------
1528 IF ( l_tax_dist_id_list1.COUNT <> 0 ) THEN
1529 l_tax_count := l_tax_dist_id_final_list.COUNT;
1530 FOR num in 1 .. l_tax_dist_id_list1.COUNT LOOP
1531 dd := l_tax_count + num;
1532 l_tax_dist_id_final_list(dd) := l_tax_dist_id_list1(num);
1533 END LOOP;
1534 l_tax_dist_id_list1.delete;
1535 END IF;
1536 -----------------------------------------------------------------------
1537 l_debug_info :=
1538 'After append the l_tax_dist_id_final_list from self_assessed tax dists and the count=' ||
1539 l_tax_dist_id_final_list.COUNT;
1540 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1541 -----------------------------------------------------------------------
1542 -----------------------------------------------------------------------
1543 l_debug_info :=
1544 'calling DBI API and fial list count =' ||
1545 l_dbi_key_final_list.COUNT;
1546 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1547 -----------------------------------------------------------------------
1548 AP_DBI_PKG.Maintain_DBI_Summary
1549 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1550 p_operation => 'U',
1551 p_key_value1 => NULL,
1552 p_key_value_list => l_dbi_key_final_list,
1553 p_calling_sequence => l_curr_calling_sequence);
1554 IF ( l_tax_dist_id_final_list.COUNT <> 0 ) THEN
1555 -----------------------------------------------------------------------
1556 l_debug_info :='Need to call eTax api to update the posted flag';
1557 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1558 -----------------------------------------------------------------------
1559 zx_api_pub.update_posting_flag(
1560 p_api_version => 1.0,
1561 p_init_msg_list => FND_API.G_TRUE,
1562 p_commit => FND_API.G_FALSE,
1563 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1564 x_return_status => l_return_status_service,
1565 x_msg_count => l_msg_count,
1566 x_msg_data => l_msg_data,
1567 p_tax_dist_id_tbl => l_tax_dist_id_final_list );
1568 l_tax_dist_id_final_list.DELETE;
1569
1570 IF ( l_return_status_service <> FND_API.G_RET_STS_SUCCESS ) THEN
1571
1572 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1573 FND_MESSAGE.SET_TOKEN('ERROR','calling etax api fails');
1574 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1575 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1576 app_exception.raise_exception();
1577
1578 END IF;
1579
1580 END IF;
1581
1582 END IF ; -- end of l_process_list.COUNT<> 0
1583
1584 -----------------------------------------------------------------------
1585 l_debug_info :=
1586 'Doing pl/sql table cleanup within the commit size cycle';
1587 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1588 -----------------------------------------------------------------------
1589 IF ( l_process_list.count <> 0 ) THEN
1590 l_process_list.DELETE;
1591 END IF;
1592
1593 IF ( l_event_list.count <> 0 ) THEN
1594 l_event_list.DELETE;
1595 END IF;
1596
1597 IF ( l_accrual_event_ids.count <> 0 ) THEN
1598 l_accrual_event_ids.DELETE;
1599 END IF;
1600
1601 IF ( l_cash_event_ids.count <> 0 ) THEN
1602 l_cash_event_ids.DELETE;
1603 END IF;
1604
1605 IF ( l_prepay_event_list.count <> 0 ) THEN
1606 l_prepay_event_list.DELETE;
1607 END IF;
1608
1609 IF ( l_payclear_event_list.count <> 0 ) THEN
1610 l_payclear_event_list.DELETE;
1611 END IF;
1612
1613 IF ( l_other_event_list.count <> 0 ) THEN
1614 l_other_event_list.DELETE;
1615 END IF;
1616
1617 IF ( l_invID_list.count <> 0 ) THEN
1618 l_invID_list.DELETE;
1619 END IF;
1620
1621 IF ( l_check_status_list.count <> 0 ) THEN
1622 l_check_status_list.DELETE;
1623 END IF;
1624
1625 IF ( l_check_id_list.count <> 0 ) THEN
1626 l_check_id_list.DELETE;
1627 END IF;
1628
1629 IF ( l_cash_inv_event_list.count <> 0 ) THEN
1630 l_cash_inv_event_list.DELETE;
1631 END IF;
1632 EXIT WHEN l_events_cur%NOTFOUND;
1633 END LOOP;
1634 CLOSE l_events_cur;
1635
1636 --bug 10412623, removed code introduced in 9936620
1637
1638 WHEN ('D') THEN -- p_accounting_mode
1639 -----------------------------------------------------------------------
1640 l_debug_info := 'draft mode';
1641 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1642 -- DRAFT MODE
1643 ----------------------------------------------------------------------
1644 -- bug9377273
1645 OPEN l_events_gt_cur;
1646 LOOP
1647 FETCH l_events_gt_cur
1648 BULK COLLECT INTO l_event_ids LIMIT 1000;
1649
1650 IF l_event_ids.COUNT > 0 THEN
1651
1652 ---------------------------------------------------------------------
1653 l_debug_info := 'Mark payment history posted_flag';
1654 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1655 ---------------------------------------------------------------------
1656 FORALL i IN 1 .. l_event_ids.count
1657 UPDATE ap_payment_history_All APH
1658 SET POSTED_FLAG = 'N'
1659 -- bug 10101613
1660 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1661 ,LAST_UPDATE_DATE = SYSDATE
1662 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1663 WHERE POSTED_FLAG = 'S'
1664 AND APH.accounting_event_id = l_event_ids(i);
1665
1666 ---------------------------------------------------------------------
1667 l_debug_info := 'Mark the payments posted_flag';
1668 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1669 ---------------------------------------------------------------------
1670 FORALL i IN 1 .. l_event_ids.count
1671 UPDATE ap_invoice_payments_all AIP
1672 SET AIP.posted_flag = 'N'
1673 -- bug 10101613
1674 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
1675 ,LAST_UPDATE_DATE = SYSDATE
1676 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
1677 WHERE POSTED_FLAG = 'S'
1678 AND AIP.accounting_event_id = l_event_ids(i);
1679
1680 END IF;
1681 EXIT WHEN l_events_gt_cur%NOTFOUND;
1682 END LOOP;
1683 CLOSE l_events_gt_cur;
1684
1685 IF l_event_ids.COUNT <> 0 THEN
1686 l_event_ids.DELETE;
1687 END IF;
1688
1689 WHEN ('FUNDS_CHECK') THEN -- p_accounting_mode
1690 -----------------------------------------------------------------------
1691 l_debug_info :='funds check mode';
1692 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1693 -- FUNDS CHECK MODE
1694 ----------------------------------------------------------------------
1695 NULL;
1696
1697 WHEN ('FUNDS_RESERVE') THEN -- p_accounting_mode
1698 -----------------------------------------------------------------------
1699 l_debug_info := 'funds reserve mode';
1700 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1701 -- funds reserve mode
1702 ----------------------------------------------------------------------
1703 NULL;
1704
1705 ELSE
1706 ---------------------------------------------------------------------
1707 l_debug_info := 'Others: p_accounting_mode = '|| p_accounting_mode;
1708 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1709 ---------------------------------------------------------------------
1710 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1711 FND_MESSAGE.SET_TOKEN('ERROR','Wrong p_accounting_mode');
1712 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1713 l_curr_calling_sequence);
1714 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1715 'p_accounting_mode = '||p_accounting_mode);
1716 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1717
1718 app_exception.raise_exception();
1719
1720 END CASE; -- p_accounting_mode
1721
1722 END IF; -- end of checking p_accountinng_mode is not null
1723
1724 -------------------------------------------------------------------------
1725 l_debug_info := 'End of procedure'||l_procedure_name;
1726 trace(G_LEVEL_PROCEDURE, l_procedure_name, l_debug_info);
1727 -------------------------------------------------------------------------
1728
1729
1730 EXCEPTION
1731
1732 WHEN OTHERS THEN
1733
1734 IF (l_events_cur%ISOPEN) THEN
1735
1736 CLOSE l_events_cur;
1737
1738 END IF;
1739
1740 --bug9377273
1741 IF (l_events_gt_cur%ISOPEN) THEN
1742
1743 CLOSE l_events_gt_cur;
1744
1745 END IF;
1746
1747 --bug 10412623, removed code introduced in 9936620
1748 /*
1749 ---------------------------------------------------------------------
1750 l_debug_info := 'clean up and set back posted flag value to N';
1751 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1752 ---------------------------------------------------------------------
1753 */
1754 /* -- need to make the following autonmous commit
1755 UPDATE ap_payment_history_All APH
1756 SET POSTED_FLAG = 'N'
1757 WHERE APH.accounting_event_id in
1758 ( select event_id from xla_events_gt);
1759
1760 UPDATE ap_invoice_distributions_All AID
1761 SET POSTED_FLAG = 'N'
1762 WHERE AID.accounting_event_id in
1763 ( select event_id from xla_events_gt);
1764
1765 UPDATE ap_self_assessed_tax_dist_all AID
1766 SET POSTED_FLAG = 'N'
1767 WHERE AID.accounting_event_id in
1768 ( select event_id from xla_events_gt);
1769
1770 UPDATE ap_invoice_payments_all AIP
1771 SET POSTED_FLAG = 'N'
1772 WHERE AIP.accounting_event_id in
1773 ( select event_id from xla_events_gt);
1774
1775 UPDATE ap_prepay_history_all APPH
1776 SET APPH.posted_flag = 'N'
1777 WHERE APPH.accounting_event_id in
1778 ( select event_id from xla_events_gt);
1779 -- */
1780
1781
1782 IF (SQLCODE <> -20001) THEN
1783 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1784 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1785 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1786 l_curr_calling_sequence);
1787 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1788 'p_application_id = '|| p_application_id ||' '||
1789 'p_accounting_mode = '|| p_accounting_mode);
1790 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1791 END IF;
1792
1793 app_exception.raise_exception();
1794
1795 END postprocessing;
1796
1797 /*============================================================================
1798 | PROCEDURE - EXTRACT (PUBLIC)
1799 |
1800 | DESCRIPTION
1801 | This procedure is the AP SLA extract procedure. This procedure
1802 | will be called by SLA thorugh an API.
1803 |
1804 | PRAMETERS
1805 | p_application_id
1806 | This parameter is the application ID of the application that the SLA
1807 | workflow event is for. This procedure must exit without doing anything
1808 | if this parameter is not 200 to ensure that this procedure is only
1809 | executed when the workflow event is for AP. This parameter will never
1810 | be NULL.
1811 | p_accounting_mode
1812 | This parameter is the "accounting mode" that the accounting is being
1813 | run in. This parameter will never be NULL.
1814 | +-------+-----------------------------------------------------------+
1815 | | Value | Meaning |
1816 | +-------+-----------------------------------------------------------+
1817 | | 'D' | The accounting is being run in "draft mode". Draft mode is|
1818 | | | used TO examine what the accounting entries would look for|
1819 | | | an event without actually creating the accounting entries |
1820 | | 'F' | The accounting is being run in "final mode". Final mode is|
1821 | | | used to create accounting entries. |
1822 | +-------+-----------------------------------------------------------+
1823 |
1824 | KNOWN ISSUES:
1825 |
1826 | NOTES:
1827 | 1) This procedure is run in final mode and draft mode.
1828 | 2) This procedure is run in batch mode and document mode.
1829 | 3) This procedure is part of the accounting commit cycle.
1830 |
1831 | MODIFICATION HISTORY
1832 | Date Author Description of Change
1833 |
1834 *===========================================================================*/
1835
1836 PROCEDURE extract
1837 (
1838 p_application_id IN NUMBER,
1839 p_accounting_mode IN VARCHAR2
1840 )
1841 IS
1842
1843 CURSOR l_events_cur IS
1844 SELECT XEG.event_id
1845 FROM xla_events_gt XEG
1846 WHERE XEG.application_id = 200;
1847
1848 l_event_ids l_event_ids_typ;
1849 l_debug_info VARCHAR2(240);
1850 l_procedure_name CONSTANT VARCHAR2(30):='EXTRACT';
1851 l_curr_calling_sequence VARCHAR2(2000);
1852
1853 BEGIN
1854
1855 l_curr_calling_sequence := 'AP_SLA_PROCESSING_PKG.EXTRACT';
1856 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1857
1858 -------------------------------------------------------------------------
1859 l_debug_info := 'Begin of procedure '||l_procedure_name;
1860 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1861 -------------------------------------------------------------------------
1862 -------------------------------------------------------------------------
1863 -- This procedure should only called by 'AP', whose application id
1864 -- is 200. Otherwise exit the procedure.
1865 IF (p_application_id <> 200) THEN
1866 RETURN;
1867 END IF;
1868
1869
1870 -------------------------------------------------------------------------
1871 l_debug_info := 'About to call lock_documents to update posted flag';
1872 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1873 -------------------------------------------------------------------------
1874 --bug9377273, commented the if condition
1875 --IF ( p_accounting_mode IS NOT NULL AND p_accounting_mode = 'F') THEN
1876
1877 OPEN l_events_cur;
1878 LOOP
1879 FETCH l_events_cur
1880 BULK COLLECT INTO l_event_ids LIMIT 1000;
1881
1882 Lock_Documents_autonomous( p_event_ids => l_event_ids,
1883 p_accounting_mode => p_accounting_mode, --bug9377273
1884 p_calling_sequence => l_curr_calling_sequence);
1885 EXIT WHEN l_events_cur%NOTFOUND;
1886 END LOOP;
1887 CLOSE l_events_cur;
1888
1889 --END IF;
1890
1891 -------------------------------------------------------------------------
1892 l_debug_info := 'About to call AP_ACCOUNTING_PAY_PKG.Do_Pay_Accounting';
1893 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
1894 -------------------------------------------------------------------------
1895
1896 -------------------------------------------------------------------------
1897 -- The call below will populate the prepayment distributions table
1898 -- and the payment distributions table for the events that are
1899 -- in xla_events_gt
1900 -------------------------------------------------------------------------
1901
1902 AP_ACCOUNTING_PAY_PKG.Do_Pay_Accounting(l_curr_calling_sequence);
1903
1904 -------------------------------------------------------------------------
1905 l_debug_info := 'End of procedure'||l_procedure_name;
1906 trace(G_LEVEL_PROCEDURE, l_procedure_name, l_debug_info);
1907 -------------------------------------------------------------------------
1908
1909
1910 EXCEPTION
1911
1912 WHEN OTHERS THEN
1913
1914 IF (l_events_cur%ISOPEN) THEN
1915
1916 CLOSE l_events_cur;
1917
1918 END IF;
1919
1920 IF (SQLCODE <> -20001) THEN
1921 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1922 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1923 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1924 l_curr_calling_sequence);
1925 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1926 'p_application_id = '|| p_application_id ||' '||
1927 'p_accounting_mode = '|| p_accounting_mode);
1928 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1929 END IF;
1930
1931 app_exception.raise_exception();
1932
1933 END extract;
1934
1935
1936
1937 /*============================================================================
1938 | PROCEDURE - POSTACCOUNTING(PUBLIC)
1939 |
1940 | DESCRIPTION
1941 | This procedure is the AP SLA post-accounting procedure. This procedure
1942 | will be called by SLA through an API.
1943 |
1944 | PRAMETERS
1945 | p_application_id
1946 | This parameter is the application ID of the application that the SLA
1947 | workflow event is for. This procedure must exit without doing anything
1948 | if this parameter is not 200 to ensure that this procedure is only
1949 | executed when the workflow event is for AP. This parameter will never
1950 | be NULL.
1951 | p_ledger_id
1952 | This parameter is the ledger ID of the ledger to account. This
1953 | parameter is purely informational. This procedure selects from the
1954 | XLA_ENTITY_EVENTS_V view, which does not include events incompatible
1955 | with this parameter. This parameter will never be NULL.
1956 | p_process_category
1957 | This parameter is the "process category" of the events to account.
1958 | This parameter is purely informational. This procedure selects from
1959 | the XLA_ENTITY_EVENTS_V view, which does not include events
1960 | incompatible with this parameter.Possible values are as following:
1961 | +------------+-------------------------------+
1962 | | Value | Meaning |
1963 | +------------+-------------------------------+
1964 | | 'Invoices' | process invoices |
1965 | | 'Payments' | process payments and receipts |
1966 | | 'All' | process everything |
1967 | +------------+-------------------------------+
1968 | p_end_date
1969 | This parameter is the maximum event date of the events to be processed
1970 | in this run of the accounting. This procedure selects from the
1971 | XLA_ENTITY_EVENTS_V view, which does not include events incompatible
1972 | with this parameter. This parameter will never be NULL.
1973 | p_accounting_mode
1974 | This parameter is the "accounting mode" that the accounting is being
1975 | run in. This parameter will never be NULL.
1976 | +-------+-------------------------------------------------------------+
1977 | | Value | Meaning |
1978 | +-------+-------------------------------------------------------------+
1979 | | 'D' | The accounting is being run in "draft mode". Draft mode is |
1980 | | | used to examine what the accounting entries would look for |
1981 | | | an event without actually creating the accounting entries. |
1982 | | 'F' | The accounting is being run in "final mode". Final mode is |
1983 | | | used to create accounting entries. |
1984 | +-------+-------------------------------------------------------------+
1985 | p_valuation_method
1986 | This parameter is unused by AP. This parameter is purely informational
1987 | This procedure selects from the XLA_ENTITY_EVENTS_V view, which does
1988 | not include events incompatible with this parameter.
1989 | p_security_id_int_1
1990 | This parameter is unused by AP.
1991 | p_security_id_int_2
1992 | This parameter is unused by AP.
1993 | p_security_id_int_3
1994 | This parameter is unused by AP.
1995 | p_security_id_char_1
1996 | This parameter is unused by AP.
1997 | p_security_id_char_2
1998 | This parameter is unused by AP.
1999 | p_security_id_char_3
2000 | This parameter is unused by AP.
2001 | p_report_request_id
2002 | This parameter is the concurrent request ID of the concurrent request
2003 | that is this run of the accounting. This parameter is used to specify
2004 | which events in the XLA_ENTITY_EVENTS_V view are to be accounted in
2005 | this run of the accounting. This parameter will never be NULL.
2006 |
2007 | KNOWN ISSUES:
2008 |
2009 | NOTES:
2010 | 1) This procedure is run in final mode and draft mode.
2011 | 2) This procedure is run in batch mode but not in document mode.
2012 | 3) This procedure is in its own commit cycle.
2013 |
2014 | MODIFICATION HISTORY
2015 | Date Author Description of Change
2016 |
2017 *===========================================================================*/
2018
2019 PROCEDURE postaccounting
2020 (
2021 p_application_id IN NUMBER,
2022 p_ledger_id IN INTEGER,
2023 p_process_category IN VARCHAR2,
2024 p_end_date IN DATE,
2025 p_accounting_mode IN VARCHAR2,
2026 p_valuation_method IN VARCHAR2,
2027 p_security_id_int_1 IN INTEGER,
2028 p_security_id_int_2 IN INTEGER,
2029 p_security_id_int_3 IN INTEGER,
2030 p_security_id_char_1 IN VARCHAR2,
2031 p_security_id_char_2 IN VARCHAR2,
2032 p_security_id_char_3 IN VARCHAR2,
2033 p_report_request_id IN INTEGER
2034 )
2035 IS
2036
2037 TYPE l_event_ids_typ IS
2038 TABLE OF NUMBER(15)
2039 INDEX BY PLS_INTEGER;
2040
2041 TYPE l_event_status_typ IS
2042 TABLE OF xla_events.event_status_code%TYPE
2043 INDEX BY PLS_INTEGER;
2044
2045 -----------------------------------------------------------------------------
2046 -- The XLA_EVENTS table contains all the events processed in this run
2047 -- of the accounting.
2048 -----------------------------------------------------------------------------
2049
2050 CURSOR l_events_cur IS
2051 SELECT XEE.event_id, XEE.event_status_code
2052 FROM xla_events XEE
2053 WHERE XEE.application_id = 200
2054 AND XEE.request_id = p_report_request_id
2055 AND XEE.event_status_code <> 'P';
2056
2057 l_event_status l_event_status_typ;
2058 l_event_ids l_event_ids_typ;
2059 l_debug_info VARCHAR2(240);
2060 l_procedure_name CONSTANT VARCHAR2(30):='POST_ACCOUNTING_PROC';
2061 l_cash_basis_flag VARCHAR2(1);
2062 l_curr_calling_sequence VARCHAR2(2000);
2063
2064 BEGIN
2065
2066 l_curr_calling_sequence := 'AP_SLA_PROCESSING_PKG.POST_ACCOUNTING_PROC';
2067 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2068 ---------------------------------------------------------------------
2069 l_debug_info := 'Begin of procedure '||l_procedure_name;
2070 trace(G_LEVEL_PROCEDURE, l_procedure_name, l_debug_info);
2071 ---------------------------------------------------------------------
2072
2073 IF (p_application_id <> 200) THEN
2074 RETURN;
2075 END IF;
2076
2077
2078 ---------------------------------------------------------------------
2079 l_debug_info := 'case p_accounting_mode :='||p_accounting_mode;
2080 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
2081 ---------------------------------------------------------------------
2082
2083 IF ( p_accounting_mode IS NOT NULL ) THEN
2084
2085 ---------------------------------------------------------------------
2086 l_debug_info := 'p_accounting_mode not null - not transfer only mode';
2087 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
2088 ---------------------------------------------------------------------
2089 ---------------------------------------------------------------------
2090 l_debug_info := 'case p_accounting_mode :='||p_accounting_mode;
2091 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
2092 ---------------------------------------------------------------------
2093 CASE (p_accounting_mode)
2094
2095 -------------------------------------------------------------------------
2096 -- FINAL MODE
2097 -------------------------------------------------------------------------
2098 WHEN ('F') THEN -- p_accounting_mode
2099
2100 -- deriving the cash basis flag for the ledger
2101 -- bug9444952
2102 SELECT sla_ledger_cash_basis_flag
2103 INTO l_cash_basis_flag
2104 FROM gl_ledgers
2105 WHERE ledger_id = p_ledger_id;
2106
2107 OPEN l_events_cur;
2108 LOOP
2109
2110 FETCH l_events_cur
2111 BULK COLLECT INTO
2112 l_event_ids,
2113 l_event_status
2114 LIMIT 1000;
2115
2116 ---------------------------------------------------------------------
2117 l_debug_info :=
2118 'Update the payment distributions'' POSTED_FLAGs.';
2119 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
2120 ---------------------------------------------------------------------
2121 -- Rewrote for bug fix 5694577
2122 -- When payment accounting option is CLEAR ONLY, need to set the
2123 -- posted_flag to 'Y' for payment create and maturity event after
2124 -- Create Accounting program
2125
2126 -- Bug 7374984. Updating the posted flag to 'Y' if the events
2127 -- are set to no action.
2128
2129 FORALL i IN 1 .. l_event_ids.count
2130 UPDATE ap_payment_history_all APH
2131 SET APH.POSTED_FLAG = CASE
2132 WHEN l_event_status(i) = 'N' THEN 'Y'
2133 ELSE 'N'
2134 END
2135 -- bug 10101613
2136 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
2137 ,LAST_UPDATE_DATE = SYSDATE
2138 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
2139 WHERE APH.accounting_event_id = l_event_ids(i);
2140
2141 ---------------------------------------------------------------------
2142 l_debug_info :=
2143 'Update the prepayment header'' POSTED_FLAGs.';
2144 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
2145 ---------------------------------------------------------------------
2146
2147 -- bug9444952 : Setting posted flags to appropriate statuses
2148 -- in case the Event_Status_Code is 'N'
2149 --
2150 FORALL i IN 1 .. l_event_ids.count
2151 UPDATE ap_prepay_history_all APPH
2152 SET APPH.posted_flag =
2153 CASE
2154 WHEN l_event_status(i) = 'N' THEN 'Y'
2155 ELSE 'N'
2156 END
2157 -- bug 10101613
2158 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
2159 ,LAST_UPDATE_DATE = SYSDATE
2160 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
2161 WHERE APPH.accounting_event_id = l_event_ids(i);
2162
2163 ---------------------------------------------------------------------
2164 l_debug_info :=
2165 'Update the payments'' POSTED_FLAGs.';
2166 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
2167 ---------------------------------------------------------------------
2168 -- rewrote for bug fix 5694577
2169 -- When payment accounting option is CLEAR ONLY, need to set the
2170 -- posted_flag to 'Y' for payment create and maturity event after
2171 -- Create Accounting program
2172
2173 -- Bug 7374984. Updating the posted flag to 'Y' if the events
2174 -- are set to no action.
2175
2176 FORALL i IN 1 .. l_event_ids.count
2177 UPDATE ap_invoice_payments_all AIP
2178 SET AIP.Posted_flag = CASE
2179 WHEN l_event_status(i) = 'N' THEN 'Y'
2180 ELSE 'N'
2181 END,
2182 AIP.Accrual_Posted_Flag =
2183 CASE
2184 WHEN l_event_status(i) = 'N' THEN 'Y'
2185 ELSE 'N'
2186 END,
2187 AIP.Cash_Posted_Flag =
2188 CASE
2189 WHEN l_event_status(i) = 'N' THEN 'Y'
2190 ELSE 'N'
2191 END
2192 -- bug 10101613
2193 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
2194 ,LAST_UPDATE_DATE = SYSDATE
2195 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
2196 WHERE AIP.accounting_event_id = l_event_ids(i);
2197
2198 ---------------------------------------------------------------------
2199 l_debug_info :=
2200 'Update the distributions'' POSTED_FLAGs.';
2201 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
2202 ---------------------------------------------------------------------
2203
2204 -- bug9444952 : Setting posted flags to appropriate statuses
2205 -- in case the Event_Status_Code is 'N'
2206 --
2207 FORALL i IN 1 .. l_event_ids.count
2208 UPDATE ap_invoice_distributions_all AID
2209 SET AID.posted_flag = CASE
2210 WHEN l_event_status(i) = 'N' THEN 'Y'
2211 ELSE 'N'
2212 END,
2213 AID.accrual_posted_flag =
2214 CASE
2215 WHEN l_event_status(i) = 'N' AND
2216 l_cash_basis_flag = 'N' THEN 'Y'
2217 ELSE 'N'
2218 END,
2219 AID.Cash_posted_flag =
2220 CASE
2221 WHEN l_event_status(i) = 'N' AND
2222 l_cash_basis_flag = 'Y' THEN 'Y'
2223 ELSE AID.Cash_posted_flag
2224 END
2225 -- bug 10101613
2226 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
2227 ,LAST_UPDATE_DATE = SYSDATE
2228 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
2229 WHERE AID.accounting_event_id = l_event_ids(i);
2230
2231 -- bug9444952 : Setting posted flags to appropriate statuses
2232 -- in case the Event_Status_Code is 'N'
2233 --
2234 FORALL i IN 1 .. l_event_ids.count
2235 UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL AID
2236 SET AID.posted_flag = CASE
2237 WHEN l_event_status(i) = 'N' THEN 'Y'
2238 ELSE 'N'
2239 END,
2240 AID.accrual_posted_flag =
2241 CASE
2242 WHEN l_event_status(i) = 'N' AND
2243 l_cash_basis_flag = 'N' THEN 'Y'
2244 ELSE 'N'
2245 END,
2246 AID.Cash_posted_flag =
2247 CASE
2248 WHEN l_event_status(i) = 'N' AND
2249 l_cash_basis_flag = 'Y' THEN 'Y'
2250 ELSE AID.Cash_posted_flag
2251 END
2252 -- bug 10101613
2253 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
2254 ,LAST_UPDATE_DATE = SYSDATE
2255 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
2256 WHERE AID.accounting_event_id = l_event_ids(i);
2257
2258 EXIT WHEN l_events_cur%NOTFOUND;
2259 END LOOP;
2260 CLOSE l_events_cur;
2261
2262 -------------------------------------------------------------------------
2263 -- DRAFT MODE
2264 -------------------------------------------------------------------------
2265 WHEN ('D') THEN -- p_accounting_mode
2266 NULL;
2267
2268 WHEN ('FUNDS_CHECK') THEN -- p_accounting_mode
2269 -----------------------------------------------------------------------
2270 l_debug_info := 'p_accounting_mode ='||p_accounting_mode;
2271 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
2272 -- funds check
2273 -----------------------------------------------------------------------
2274 NULL;
2275
2276 WHEN ('FUNDS_RESERVE') THEN -- p_accounting_mode
2277 -----------------------------------------------------------------------
2278 l_debug_info := 'p_accounting_mode ='||p_accounting_mode;
2279 trace(G_LEVEL_STATEMENT, l_procedure_name, l_debug_info);
2280 -- funds reserve
2281 -----------------------------------------------------------------------
2282 NULL;
2283
2284 ELSE -- different value for p_accounting_mode
2285 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2286 FND_MESSAGE.SET_TOKEN('ERROR','Wrong p_accounting_mode');
2287 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
2288 l_curr_calling_sequence);
2289 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2290 'p_accounting_mode = '|| p_accounting_mode);
2291 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2292
2293 app_exception.raise_exception();
2294
2295 END CASE; -- p_accounting_mode
2296
2297 END IF; -- END of checking p_accounting_mode
2298
2299 ------------------------------------------------------------------------
2300 l_debug_info :=
2301 'End of procedure '||l_procedure_name;
2302 trace(G_LEVEL_PROCEDURE, l_procedure_name, l_debug_info);
2303 ------------------------------------------------------------------------
2304 COMMIT;
2305
2306 EXCEPTION
2307
2308 WHEN OTHERS THEN
2309
2310 IF (l_events_cur%ISOPEN) THEN
2311
2312 CLOSE l_events_cur;
2313
2314 END IF;
2315
2316 IF (SQLCODE <> -20001) THEN
2317 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2318 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2319 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
2320 l_curr_calling_sequence);
2321 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2322 'p_application_id=' || p_application_id || ' ' ||
2323 'p_ledger_id=' || p_ledger_id || ' ' ||
2324 'p_process_category=' || p_process_category || ' ' ||
2325 'p_end_date=' || p_end_date || ' ' ||
2326 'p_accounting_mode=' || p_accounting_mode || ' ' ||
2327 'p_valuation_method=' || p_valuation_method || ' ' ||
2328 'p_security_id_int_1=' || p_security_id_int_1 || ' ' ||
2329 'p_security_id_int_2=' || p_security_id_int_2 || ' ' ||
2330 'p_security_id_int_3=' || p_security_id_int_3 || ' ' ||
2331 'p_security_id_char_1=' || p_security_id_char_1 || ' ' ||
2332 'p_security_id_char_2=' || p_security_id_char_2 || ' ' ||
2333 'p_security_id_char_3=' || p_security_id_char_3 || ' ' ||
2334 'p_report_request_id=' || p_report_request_id);
2335 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2336 END IF;
2337
2338 app_exception.raise_exception();
2339
2340 END postaccounting;
2341
2342
2343 -------------------------------------------------------------------------------
2344 -- FUNCTION Get_Amt_Already_Accounted RETURN NUMBER
2345 -- RETURN the amount already accounted for a distribution in this set of books.
2346 -- This function is primarily used in figuring out NOCOPY the amount of the
2347 -- distribution that has already been accounted in Cash Basis.
2348
2349 -- Parameters
2350 ----------
2351 -- SOB_ID - The set of books in which this amount is to be calculated
2352 -- Invoice_Distribution_ID - The distribution for which this amount is
2353 -- to be calculated
2354 -------------------------------------------------------------------------------
2355 FUNCTION Get_Amt_Already_Accounted
2356 (P_event_id IN NUMBER
2357 ,P_invoice_payment_id IN NUMBER
2358 ,P_invoice_distribution_id IN NUMBER
2359 ,P_calling_sequence IN VARCHAR2
2360 ) RETURN NUMBER IS
2361
2362 l_amt_already_accounted NUMBER := 0 ;
2363 l_curr_calling_sequence VARCHAR2(2000);
2364
2365 l_transaction_type VARCHAR2(30);
2366 l_paid_acctd_amt NUMBER;
2367 l_prepaid_acctd_amt NUMBER;
2368
2369
2370 BEGIN
2371
2372 l_curr_calling_sequence :=
2373 'AP_SLA_PROCESSING_PKG.Get_Amt_Already_Accounted<-' || P_calling_sequence;
2374
2375 BEGIN
2376 SELECT APH.Transaction_Type
2377 INTO l_transaction_type
2378 FROM AP_Payment_History_All APH
2379 WHERE APH.Accounting_Event_ID = P_Event_ID;
2380 EXCEPTION
2381 WHEN others THEN
2382 l_transaction_type := 'PAYMENT CREATED';
2383 END;
2384
2385
2386 IF (l_transaction_type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING',
2387 'PAYMENT CLEARING ADJUSTED')) THEN
2388
2389 /* Getting the sum of dist amount for the given distribution */
2390 -- 9803363 , added code to get dist id and awt calculated on it
2391 SELECT SUM(APHD.Invoice_Dist_Amount)
2392 INTO l_paid_acctd_amt
2393 FROM AP_Payment_Hist_Dists APHD,
2394 AP_Payment_History_All APH
2395 WHERE APHD.Invoice_Distribution_ID in ( select p_invoice_distribution_id from dual
2396 union
2397 select distinct aphd_awt.invoice_distribution_id
2398 from ap_payment_hist_dists aphd_awt,
2399 ap_payment_hist_dists aphd_item
2400 where 1=1
2401 and aphd_item.invoice_distribution_id = p_invoice_distribution_id
2402 and aphd_item.pay_dist_lookup_code <> 'AWT'
2403 and aphd_awt.awt_related_id = aphd_item.invoice_distribution_id
2404 and aphd_awt.pay_dist_lookup_code = 'AWT')
2405 AND APH.Posted_Flag = 'Y'
2406 AND APH.Payment_History_ID = APHD.Payment_History_ID
2407 AND APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING',
2408 'PAYMENT CLEARING ADJUSTED');
2409
2410 ELSIF (l_transaction_type IN ('PAYMENT MATURITY', 'PAYMENT MATURITY REVERSED',
2411 'PAYMENT MATURITY ADJUSTED')) THEN
2412
2413 /* Getting the sum of dist amount for the given distribution */
2414 -- 9803363 , added code to get dist id and awt calculated on it
2415 SELECT SUM(APHD.Invoice_Dist_Amount)
2416 INTO l_paid_acctd_amt
2417 FROM AP_Payment_Hist_Dists APHD,
2418 AP_Payment_History_All APH
2419 WHERE APHD.Invoice_Distribution_ID in ( select p_invoice_distribution_id from dual
2420 union
2421 select distinct aphd_awt.invoice_distribution_id
2422 from ap_payment_hist_dists aphd_awt,
2423 ap_payment_hist_dists aphd_item
2424 where 1=1
2425 and aphd_item.invoice_distribution_id = p_invoice_distribution_id
2426 and aphd_item.pay_dist_lookup_code <> 'AWT'
2427 and aphd_awt.awt_related_id = aphd_item.invoice_distribution_id
2428 and aphd_awt.pay_dist_lookup_code = 'AWT')
2429 AND APH.Posted_Flag = 'Y'
2430 AND APH.Payment_History_ID = APHD.Payment_History_ID
2431 AND APH.Transaction_Type IN ('PAYMENT MATURITY', 'PAYMENT MATURITY REVERSED',
2432 'PAYMENT MATURITY ADJUSTED');
2433
2434 ELSE
2435
2436 /* Getting the sum of dist amount for the given distribution */
2437 -- 9803363 , added code to get dist id and awt calculated on it
2438 SELECT SUM(APHD.Invoice_Dist_Amount)
2439 INTO l_paid_acctd_amt
2440 FROM AP_Payment_Hist_Dists APHD,
2441 AP_Payment_History_All APH
2442 WHERE APHD.Invoice_Distribution_ID in ( select p_invoice_distribution_id from dual
2443 union
2444 select distinct aphd_awt.invoice_distribution_id
2445 from ap_payment_hist_dists aphd_awt,
2446 ap_payment_hist_dists aphd_item
2447 where 1=1
2448 and aphd_item.invoice_distribution_id = p_invoice_distribution_id
2449 and aphd_item.pay_dist_lookup_code <> 'AWT'
2450 and aphd_awt.awt_related_id = aphd_item.invoice_distribution_id
2451 and aphd_awt.pay_dist_lookup_code = 'AWT')
2452 AND APH.Posted_Flag = 'Y'
2453 AND APH.Payment_History_ID = APHD.Payment_History_ID
2454 AND APH.Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED', 'PAYMENT ADJUSTED',
2455 'MANUAL PAYMENT ADJUSTED', 'REFUND RECORDED', 'REFUND ADJUSTED',
2456 'REFUND CANCELLED', 'MANUAL REFUND ADJUSTED'); --bug 10336668
2457 END IF;
2458
2459 /* Get the total prepaid amount from the ap_prepay_app_dists table */
2460 -- 9803363 , added code to get dist id and awt calculated on it
2461 SELECT SUM(APAD.Amount)
2462 INTO l_prepaid_acctd_amt
2463 FROM AP_Prepay_App_Dists APAD,
2464 AP_Prepay_History_All APH
2465 WHERE APAD.Invoice_Distribution_ID in (select p_invoice_distribution_id from dual
2466 union
2467 /* awt distributions which are applied on the p_invoice_distribution_id*/
2468 select distinct aphd_awt.invoice_distribution_id
2469 from ap_prepay_app_dists aphd_awt,
2470 ap_prepay_app_dists aphd_item
2471 where 1=1
2472 and aphd_item.invoice_distribution_id = p_invoice_distribution_id
2473 and aphd_item.prepay_dist_lookup_code <> 'AWT'
2474 and aphd_awt.awt_related_id = aphd_item.invoice_distribution_id
2475 and aphd_awt.prepay_dist_lookup_code = 'AWT')
2476 AND APAD.Prepay_History_ID = APH.Prepay_History_ID
2477 AND APH.Posted_Flag = 'Y';
2478
2479 l_amt_already_accounted := NVL(l_paid_acctd_amt,0) - NVL(l_prepaid_acctd_amt,0); -- 9803363
2480
2481
2482
2483 RETURN nvl(l_amt_already_accounted,0) ;
2484
2485 END Get_Amt_Already_Accounted;
2486
2487
2488 -------------------------------------------------------------------------------
2489 -- FUNCTION Derive_Cash_Posted_Flag RETURN VARCHAR2
2490 -- For a distribution, this function figures out NOCOPY the amount that has already
2491 -- been accounted in Cash Basis and then RETURN the proper value for the
2492 -- cash_posted_flag.
2493
2494 -- Parameters
2495 ----------
2496 -- SOB_ID - The cash SOB ID
2497 -- Distribution_ID - The Invoice Distribution for which the cash_posted_flag
2498 -- has to be derived
2499 -------------------------------------------------------------------------------
2500 FUNCTION Derive_Cash_Posted_Flag
2501 (P_event_id IN NUMBER
2502 ,P_distribution_id IN NUMBER
2503 ,P_dist_amount IN NUMBER
2504 ,P_calling_sequence IN VARCHAR2
2505 ) RETURN VARCHAR2 IS
2506
2507 l_already_accounted_amt NUMBER;
2508 l_distribution_amount NUMBER;
2509 l_curr_calling_sequence VARCHAR2(2000);
2510
2511
2512 BEGIN
2513
2514 l_curr_calling_sequence := 'AP_SLA_PROCESSING_PKG.Derive_Cash_Posted_Flag<-'
2515 || P_calling_sequence;
2516
2517 /*
2518 Since accounting is done either for all SOB's or None, it is safe to assume
2519 that the accounted amount will be the same for all cash SOB's. Hence, we
2520 are taking the liberty of passing the Main Cash SOB ID and not bothering
2521 about its reporting SOB's.
2522 */
2523 l_already_accounted_amt := Get_Amt_Already_Accounted (P_event_id,
2524 -1, P_distribution_id, l_curr_calling_sequence) ;
2525
2526
2527 IF l_already_accounted_amt = P_dist_amount THEN
2528
2529 return ('Y') ;
2530 ELSIF l_already_accounted_amt = 0 THEN
2531
2532 return ('N') ;
2533
2534 ELSIF ABS(l_already_accounted_amt) < ABS(P_dist_amount) THEN
2535
2536 return ('P') ;
2537
2538 ELSE
2539
2540 return ('Y') ;
2541
2542 END IF;
2543
2544 END Derive_Cash_Posted_Flag;
2545
2546 END ap_sla_processing_pkg;
[Home] [Help]