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