[Home] [Help]
PACKAGE BODY: APPS.AP_ACCOUNTING_EVENTS_PKG
Source
1 PACKAGE BODY AP_ACCOUNTING_EVENTS_PKG AS
2 /* $Header: apeventb.pls 120.54.12010000.11 2008/12/12 16:06:34 imandal ship $ */
3
4 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
6 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_MODULE_NAME CONSTANT VARCHAR2(50) :='AP.PLSQL.AP_ACCOUNTING_EVENT_PKG.';
12
13 ---------------------------------------------------------------------
14 -- Definition of Accounting Event Entities, Classes and Types.
15 ---------------------------------------------------------------------
16 ---------------------------------------------------------------------
17 --INVOICES_ENTITY
18 ---------------------------------------------------------------------
19 INVOICES_ENTITY CONSTANT VARCHAR2(30) := 'AP_INVOICES';
20
21 INVOICES_CLASS CONSTANT VARCHAR2(30) := 'INVOICES';
22 INVOICE_VALIDATED_TYPE CONSTANT VARCHAR2(30) := 'INVOICE VALIDATED';
23 --INVOICE_ADJUSTED_TYPE CONSTANT VARCHAR2(30) := 'INVOICE ADJUSTED';
24 INVOICE_ADJUSTED_TYPE CONSTANT VARCHAR2(30) := 'INVOICE VALIDATED';
25 INVOICE_CANCELLED_TYPE CONSTANT VARCHAR2(30) := 'INVOICE CANCELLED';
26
27 CREDIT_MEMOS_CLASS CONSTANT VARCHAR2(30) := 'CREDIT MEMOS';
28 CREDIT_MEMO_VALIDATED_TYPE CONSTANT VARCHAR2(30) := 'CREDIT MEMO VALIDATED';
29 --CREDIT_MEMO_ADJUSTED_TYPE CONSTANT VARCHAR2(30) := 'CREDIT MEMO ADJUSTED';
30 CREDIT_MEMO_ADJUSTED_TYPE CONSTANT VARCHAR2(30) := 'CREDIT MEMO VALIDATED';
31 CREDIT_MEMO_CANCELLED_TYPE CONSTANT VARCHAR2(30) := 'CREDIT MEMO CANCELLED';
32
33 DEBIT_MEMOS_CLASS CONSTANT VARCHAR2(30) := 'DEBIT MEMOS';
34 DEBIT_MEMO_VALIDATED_TYPE CONSTANT VARCHAR2(30) := 'DEBIT MEMO VALIDATED';
35 --DEBIT_MEMO_ADJUSTED_TYPE CONSTANT VARCHAR2(30) := 'DEBIT MEMO ADJUSTED';
36 DEBIT_MEMO_ADJUSTED_TYPE CONSTANT VARCHAR2(30) := 'DEBIT MEMO VALIDATED';
37 DEBIT_MEMO_CANCELLED_TYPE CONSTANT VARCHAR2(30) := 'DEBIT MEMO CANCELLED';
38
39 PREPAYMENTS_CLASS CONSTANT VARCHAR2(30) := 'PREPAYMENTS';
40 PREPAYMENT_VALIDATED_TYPE CONSTANT VARCHAR2(30) := 'PREPAYMENT VALIDATED';
41 --PREPAYMENT_ADJUSTED_TYPE CONSTANT VARCHAR2(30) := 'PREPAYMENT ADJUSTED';
42 PREPAYMENT_ADJUSTED_TYPE CONSTANT VARCHAR2(30) := 'PREPAYMENT VALIDATED';
43 PREPAYMENT_CANCELLED_TYPE CONSTANT VARCHAR2(30) := 'PREPAYMENT CANCELLED';
44
45 PREPAYMENT_APPLICATIONS_CLASS CONSTANT VARCHAR2(30):=
46 'PREPAYMENT APPLICATIONS';
47 PREPAYMENT_APPLIED_TYPE CONSTANT VARCHAR2(30) := 'PREPAYMENT APPLIED';
48 PREPAYMENT_UNAPPLIED_TYPE CONSTANT VARCHAR2(30) := 'PREPAYMENT UNAPPLIED';
49 PREPAY_APP_ADJUSTED_TYPE CONSTANT VARCHAR2(30) :=
50 -- bug fix 5656160
51 -- 'PREPAY APPLICATION ADJUSTED';
52 'PREPAYMENT APPLICATION ADJ';
53
54 --------------------------------------------------------------------
55 --PAYMENTS_ENTITY
56 ---------------------------------------------------------------------
57
58 PAYMENTS_ENTITY CONSTANT VARCHAR2(30) := 'AP_PAYMENTS';
59
60 PAYMENTS_CLASS CONSTANT VARCHAR2(30) := 'PAYMENTS';
61 PAYMENT_CREATED_TYPE CONSTANT VARCHAR2(30) := 'PAYMENT CREATED';
62 PAYMENT_CANCELLED_TYPE CONSTANT VARCHAR2(30) := 'PAYMENT CANCELLED';
63 MANUAL_PAYMENT_ADJUSTED_TYPE CONSTANT VARCHAR2(30) :=
64 'MANUAL PAYMENT ADJUSTED';
65 PAYMENT_ADJUSTED_TYPE CONSTANT VARCHAR2(30) := 'PAYMENT ADJUSTED';
66 UPGRADED_MAN_PAY_REV_TYPE CONSTANT VARCHAR2(30) :=
67 'UPGRADED MANUAL PMT REVERSED';
68 UPGRADED_MAN_PAY_ADJ_TYPE CONSTANT VARCHAR2(30) :=
69 'UPGRADED MANUAL PMT ADJUSTED';
70
71 REFUNDS_CLASS CONSTANT VARCHAR2(30) := 'REFUNDS';
72 REFUND_RECORDED_TYPE CONSTANT VARCHAR2(30) := 'REFUND RECORDED';
73 REFUND_CANCELLED_TYPE CONSTANT VARCHAR2(30) := 'REFUND CANCELLED';
74 REFUND_ADJUSTED_TYPE CONSTANT VARCHAR2(30) := 'REFUND ADJUSTED';
75
76 FUTURE_DATED_PAYMENTS_CLASS CONSTANT VARCHAR2(30) := 'FUTURE DATED PAYMENTS';
77 PAYMENT_MATURED_TYPE CONSTANT VARCHAR2(30) := 'PAYMENT MATURED';
78 PAYMENT_MATURITY_REVERSED_TYPE CONSTANT VARCHAR2(30):=
79 'PAYMENT MATURITY REVERSED';
80
81 PAYMENT_MATURTY_RVRSL_TRX_TYPE CONSTANT VARCHAR2(30):=
82 'PAYMENT MATURITY REVERSAL';
83 PAYMENT_MATURTY_TRX_TYPE CONSTANT VARCHAR2(30):=
84 'PAYMENT MATURITY';
85
86 PAYMENT_MATURITY_ADJUSTED_TYPE CONSTANT VARCHAR2(30):=
87 'PAYMENT MATURITY ADJUSTED';
88
89 RECONCILED_PAYMENTS_CLASS CONSTANT VARCHAR2(30) := 'RECONCILED PAYMENTS';
90 PAYMENT_CLEARED_TYPE CONSTANT VARCHAR2(30) := 'PAYMENT CLEARED';
91 PAYMENT_UNCLEARED_TYPE CONSTANT VARCHAR2(30) := 'PAYMENT UNCLEARED';
92 PAYMENT_CLEARING_ADJUSTED_TYPE CONSTANT VARCHAR2(30) :=
93 'PAYMENT CLEARING ADJUSTED';
94
95 PAYMENT_CLEARED_TRX_TYPE CONSTANT VARCHAR2(30) := 'PAYMENT CLEARING';
96 PAYMENT_UNCLEARED_TRX_TYPE CONSTANT VARCHAR2(30) := 'PAYMENT UNCLEARING';
97
98 ---------------------------------------------------------------------
99 --Definition of derive_invoice_events procedure (private)
100 ---------------------------------------------------------------------
101 PROCEDURE derive_invoice_events
102 ( p_invoice_id IN NUMBER,
103 p_calling_sequence IN VARCHAR2
104 );
105
106 ---------------------------------------------------------------------
107 --Definition of create_invoice_event procedure (private)
108 ---------------------------------------------------------------------
109 FUNCTION create_invoice_event
110 ( p_event_type IN VARCHAR2,
111 p_invoice_id IN NUMBER,
112 p_event_date IN DATE,
113 p_calling_sequence IN VARCHAR2
114 ) RETURN NUMBER;
115
116 ---------------------------------------------------------------------
117 --Definition of create_payment_event procedure (private)
118 ---------------------------------------------------------------------
119 FUNCTION create_payment_event
120 ( p_event_type IN VARCHAR2,
121 p_check_id IN NUMBER,
122 p_event_date IN DATE,
123 p_calling_sequence IN VARCHAR2
124 ) RETURN NUMBER;
125
126 ---------------------------------------------------------------------
127 --Definition of is_event_complete function (private)
128 ---------------------------------------------------------------------
129 FUNCTION is_event_complete
130 ( p_doc_type IN VARCHAR2,
131 p_source_id IN NUMBER,
132 p_calling_sequence IN VARCHAR2
133 ) RETURN VARCHAR2;
134
135 ---------------------------------------------------------------------
136 --Definition of derive_payment_adj_event procedurs (private)
137 ---------------------------------------------------------------------
138 PROCEDURE derive_payment_adj_event
139 ( p_check_id IN NUMBER,
140 p_accounting_date IN DATE,
141 p_event_type IN VARCHAR2,
142 p_accounting_event_id OUT NOCOPY NUMBER,
143 p_calling_sequence IN VARCHAR2
144 );
145
146 ---------------------------------------------------------------------
147 --Definition of derive_cascade_events procedure (private)
148 ---------------------------------------------------------------------
149 -- Bug 6996047. Added accounting date parameter
150 PROCEDURE derive_cascade_events
151 ( p_invoice_id IN NUMBER,
152 p_adj_accounting_event_id IN NUMBER,
153 p_accounting_date IN DATE,
154 p_calling_sequence IN VARCHAR2
155 );
156
157 ---------------------------------------------------------------------
158 --Definition of derive_invoice_cancel_event procedure (private)
159 ---------------------------------------------------------------------
160 PROCEDURE derive_invoice_cancel_events
161 ( p_invoice_id IN NUMBER,
162 p_calling_sequence IN VARCHAR2
163 );
164
165 ---------------------------------------------------------------------
166 --Definition of no_action_pmt_event_update procedure (private)
167 ---------------------------------------------------------------------
168 PROCEDURE no_action_pmt_event_update
169 ( p_check_id IN NUMBER,
170 p_event_type_code IN VARCHAR2,
171 p_accounting_date IN DATE,
172 p_accounting_event_id IN NUMBER,
173 p_calling_sequence IN VARCHAR2
174 );
175
176 ---------------------------------------------------------------------
177 --Definition of get_event_class function (private)
178 ---------------------------------------------------------------------
179 FUNCTION get_event_class
180 ( p_event_type IN VARCHAR2,
181 p_calling_sequence IN VARCHAR2
182 ) RETURN VARCHAR2;
183
184 ---------------------------------------------------------------------
185 --Definition of event_security_context function (private)
186 ---------------------------------------------------------------------
187 FUNCTION get_event_security_context
188 ( p_org_id IN NUMBER,
189 p_calling_sequence IN VARCHAR2
190 ) RETURN XLA_EVENTS_PUB_PKG.T_SECURITY;
191
192 ---------------------------------------------------------------------
193 --Definition of get_invoice_event_source_info function (private)
194 ---------------------------------------------------------------------
195 FUNCTION get_invoice_event_source_info
196 ( p_legal_entity_id IN NUMBER,
197 p_ledger_id IN NUMBER,
198 p_invoice_id IN NUMBER,
199 p_calling_sequence IN VARCHAR2
200 ) RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
201
202 ---------------------------------------------------------------------
203 --Definition of get_payment_event_source_info function (private)
204 ---------------------------------------------------------------------
205 FUNCTION get_payment_event_source_info
206 ( p_legal_entity_id IN NUMBER,
207 p_ledger_id IN NUMBER,
208 p_check_id IN NUMBER,
209 p_calling_sequence IN VARCHAR2
210 ) RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
211
212 ---------------------------------------------------------------------
213 --Definition of get_invoice_info procedure (private)
214 ---------------------------------------------------------------------
215 PROCEDURE get_invoice_info
216 ( p_invoice_id IN NUMBER,
217 p_org_id OUT NOCOPY NUMBER,
218 p_legal_entity_id OUT NOCOPY NUMBER,
219 p_ledger_id OUT NOCOPY NUMBER,
220 p_transaction_date OUT NOCOPY DATE,
221 p_calling_sequence IN VARCHAR2
222 );
223
224 ---------------------------------------------------------------------
225 --Definition of get_payment_info procedure (private)
226 ---------------------------------------------------------------------
227 PROCEDURE get_payment_info
228 ( p_check_id IN NUMBER,
229 p_org_id OUT NOCOPY NUMBER,
230 p_legal_entity_id OUT NOCOPY NUMBER,
231 p_ledger_id OUT NOCOPY NUMBER,
232 p_calling_sequence IN VARCHAR2
233 );
234
235 ---------------------------------------------------------------------
236 --Definition of Insert_Prepayment_Header procedure (private)
237 ---------------------------------------------------------------------
238 PROCEDURE Insert_Prepayment_Header
239 ( p_invoice_id IN NUMBER,
240 p_invoice_line_number IN NUMBER,
241 p_accounting_event_id IN NUMBER,
242 p_accounting_date IN DATE,
243 p_invoice_adjustment_id IN NUMBER,
244 p_calling_sequence IN VARCHAR2
245 );
246
247 ---------------------------------------------------------------------
248 --Definition of Update_Prepayment_Header procedure (private)
249 ---------------------------------------------------------------------
250 -- Bug 4996808 Creating procedure to update the event_id on header
251 PROCEDURE Update_Prepayment_Header
252 ( p_invoice_id IN NUMBER,
253 p_invoice_line_number IN NUMBER,
254 p_accounting_event_id IN NUMBER,
255 p_accounting_date IN DATE,
256 p_transaction_type IN VARCHAR2,
257 p_calling_sequence IN VARCHAR2
258 );
259
260
261
262 /*============================================================================
263 | PROCEDURE - CREATE_EVENTS (PUBLIC)
264 |
265 | DESCRIPTION
266 | This procedure is the single point of entry for the creation of all
267 | events resulting from user actions. Events not resulting from user
268 | actions (i.e. Cascade events and Commitment events which are created
269 | automatically as a result of another event being created) will be
270 | created from the procedure which is called by Create_Events.
271 |
272 | PRAMETERS
273 | p_event_type:Possible values:
274 | 'INVOICES', 'INVOICE CANCELLATION', 'PAYMENT',
275 | 'PAYMENT ADJUSTMENT','UPGRADED MANUAL REVERSED PAYMENT',
276 | 'UPGRADED MANUAL ADJUSTED PAYMENT','PAYMENT CANCELLATION',
277 | 'PAYMENT CLEARING', 'PAYMENT MATURITY',
278 | 'PAYMENT MATURITY REVERSAL', 'PAYMENT BATCH'
279 | p_doc_type(IN):This parameter will be necessary to create events
280 | under the classes Credit Memos, Debit Memos, Prpayments,
281 | Refunds, Future Dated payments and Reconciled Payments.
282 | Possible values:
283 | p_event_type 'INVOICES' or 'INVOICE CANCELLATION':
284 | 'AWT', 'CREDIT,DEBIT',' EXPENSE REPORT',
285 | 'MIXED','INTEREST', 'PREPAYMENT','QUICKDEFAULT',
286 | 'QUICKMATCH', 'STANDARD'
287 | p_event_type: 'PAYMENT' or 'PAYMENT CANCELLATION':
288 | 'A' - payment batches
289 | 'M' - manual payments
290 | 'Q' - quick payments
291 | 'R' - refunds
292 | P_doc_id: check_id for payment
293 | invoice_id for invoice
294 | P_accounting_date: Accounting Date
295 | p_accounting_event_id:Accounting event id generated in this
296 | procedure
297 | p_checkrun_name: Payment batch name
298 | p_calling_sequence:Debug information
299 |
300 | KNOWN ISSUES:
301 |
302 | NOTES:
303 |
304 | MODIFICATION HISTORY
305 | Date Author Description of Change
306 |
307 *===========================================================================*/
308
309 PROCEDURE Create_Events(p_event_type IN VARCHAR2,
310 p_doc_type IN VARCHAR2,
311 p_doc_id IN NUMBER DEFAULT NULL,
312 p_accounting_date IN DATE,
313 p_accounting_event_id OUT NOCOPY NUMBER,
314 p_checkrun_name IN VARCHAR2,
315 p_calling_sequence IN VARCHAR2 DEFAULT NULL)
316 IS
317
318 l_event_type VARCHAR2(30);
319 l_curr_calling_sequence VARCHAR2(2000);
320
321 -- Logging:
322 l_procedure_name CONSTANT VARCHAR2(30) := 'Create_Events';
323 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
324
325 BEGIN
326
327 l_curr_calling_sequence :=
328 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.CREATE_EVENTS';
329
330 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
331
332 l_log_msg := 'Begin of procedure '|| l_procedure_name;
333 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
334 FND_LOG.STRING(G_LEVEL_PROCEDURE,
335 G_MODULE_NAME||l_procedure_name||'.begin',
336 l_log_msg);
337 END IF;
338
339 ----------------------------------------------------------------
340 -- Calling private procedure to handle differenct events
341 ----------------------------------------------------------------
342
343 CASE (p_event_type)
344
345 --------------------------------------------------------------
346 -- CASE: 'INVOICES'
347 --------------------------------------------------------------
348 WHEN ('INVOICES') THEN
349
350 l_log_msg := 'Before calling procedure Derive_invoice_events';
351 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
352 FND_LOG.STRING(G_LEVEL_PROCEDURE,
353 G_MODULE_NAME || l_procedure_name,
354 l_log_msg);
355 END IF;
356
357 derive_invoice_events(p_invoice_id => p_doc_id,
358 p_calling_sequence => l_curr_calling_sequence);
359
360 l_log_msg := 'After Calling Derive_invoice_events procedure';
361 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
362 FND_LOG.STRING(G_LEVEL_PROCEDURE,
363 G_MODULE_NAME || l_procedure_name,
364 l_log_msg);
365 END IF;
366
367 -------------------------------------------------------------
368 -- CASE: 'INVOICE CANCELLATION
369 -------------------------------------------------------------
370 WHEN ('INVOICE CANCELLATION') THEN
371
372 l_log_msg := 'Before calling procedure Derive_invoice_cancel_events';
373 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
374 FND_LOG.STRING(G_LEVEL_PROCEDURE,
375 G_MODULE_NAME || l_procedure_name,
376 l_log_msg);
377 END IF;
378
379 derive_invoice_cancel_events
380 (p_invoice_id => p_doc_id,
381 p_calling_sequence => l_curr_calling_sequence);
382
383 l_log_msg := 'After calling procedure Derive_invoice_cancel_event';
384 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
385 FND_LOG.STRING(G_LEVEL_PROCEDURE,
386 G_MODULE_NAME || l_procedure_name,
387 l_log_msg);
388 END IF;
389 ------------------------------------------------------------
390 -- CASE: 'PAYMENT'
391 ------------------------------------------------------------
392 WHEN ('PAYMENT') THEN
393
394 l_log_msg := 'Before calling procedure create_payment_event';
395 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
396 FND_LOG.STRING(G_LEVEL_PROCEDURE,
397 G_MODULE_NAME || l_procedure_name,
398 l_log_msg);
399 END IF;
400
401 IF (p_doc_type = 'R') THEN
402 l_event_type := REFUND_RECORDED_TYPE;
403 ELSE
404 l_event_type := PAYMENT_CREATED_TYPE;
405 END IF;
406
407 p_accounting_event_id :=
408 create_payment_event(p_event_type => l_event_type,
409 p_check_id => p_doc_id,
410 p_event_date => p_accounting_date,
411 p_calling_sequence => l_curr_calling_sequence);
412
413 l_log_msg := 'p_accounting_event_id = '|| p_accounting_event_id;
414 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
415 FND_LOG.STRING(G_LEVEL_STATEMENT,
416 G_MODULE_NAME || l_procedure_name,
417 l_log_msg);
418 END IF;
419
420 l_log_msg := 'After calling procedure create_payment_event';
421 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
422 FND_LOG.STRING(G_LEVEL_PROCEDURE,
423 G_MODULE_NAME || l_procedure_name,
424 l_log_msg);
425 END IF;
426
427 ------------------------------------------------------------
428 -- CASE: 'PAYMENT ADJUSTMENT'
429 ------------------------------------------------------------
430 WHEN ('PAYMENT ADJUSTMENT') THEN
431
432 l_log_msg := 'Before calling procedure derive_payment_adj_event';
433 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
434 FND_LOG.STRING(G_LEVEL_PROCEDURE,
435 G_MODULE_NAME || l_procedure_name,
436 l_log_msg);
437 END IF;
438
439 derive_payment_adj_event
440 (p_check_id => p_doc_id,
441 p_accounting_date => p_accounting_date,
442 p_event_type => MANUAL_PAYMENT_ADJUSTED_TYPE,
443 p_accounting_event_id => p_accounting_event_id, --OUT
444 p_calling_sequence => l_curr_calling_sequence);
445
446 l_log_msg := 'After calling procedure derive_payment_adj_event';
447 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
448 FND_LOG.STRING(G_LEVEL_PROCEDURE,
449 G_MODULE_NAME || l_procedure_name,
453 ------------------------------------------------------------
450 l_log_msg);
451 END IF;
452
454 -- CASE: 'UPGRADED MANUAL REVERSED PAYMENT'
455 ------------------------------------------------------------
456 WHEN ('UPGRADED MANUAL REVERSED PAYMENT') THEN
457
458 l_log_msg := 'Before calling procedure derive_payment_adj_event';
459 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
460 FND_LOG.STRING(G_LEVEL_PROCEDURE,
461 G_MODULE_NAME || l_procedure_name,
462 l_log_msg);
463 END IF;
464
465 derive_payment_adj_event
466 (p_check_id => p_doc_id,
467 p_accounting_date => p_accounting_date,
468 p_event_type => UPGRADED_MAN_PAY_REV_TYPE,
469 p_accounting_event_id => p_accounting_event_id, --OUT
470 p_calling_sequence => l_curr_calling_sequence);
471
472 l_log_msg := 'After calling procedure derive_payment_adj_event';
473 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
474 FND_LOG.STRING(G_LEVEL_PROCEDURE,
475 G_MODULE_NAME || l_procedure_name,
476 l_log_msg);
477 END IF;
478
479 ------------------------------------------------------------
480 -- CASE: 'UPGRADED MANUAL ADJUSTED PAYMENT'
481 ------------------------------------------------------------
482 WHEN ('UPGRADED MANUAL ADJUSTED PAYMENT') THEN
483
484 l_log_msg := 'Before calling procedure derive_payment_adj_event';
485 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
486 FND_LOG.STRING(G_LEVEL_PROCEDURE,
487 G_MODULE_NAME || l_procedure_name,
488 l_log_msg);
489 END IF;
490
491 derive_payment_adj_event
492 (p_check_id => p_doc_id,
493 p_accounting_date => p_accounting_date,
494 p_event_type => UPGRADED_MAN_PAY_ADJ_TYPE,
495 p_accounting_event_id => p_accounting_event_id, --OUT
496 p_calling_sequence => l_curr_calling_sequence);
497
498 l_log_msg := 'After calling procedure derive_payment_adj_event';
499 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
500 FND_LOG.STRING(G_LEVEL_PROCEDURE,
501 G_MODULE_NAME || l_procedure_name,
502 l_log_msg);
503 END IF;
504
505 ------------------------------------------------------------
506 -- CASE: 'PAYMENT CANCELLATION'
507 ------------------------------------------------------------
508 WHEN ('PAYMENT CANCELLATION') THEN
509
510 IF (p_doc_type = 'R') THEN
511 l_event_type := REFUND_CANCELLED_TYPE;
512 ELSE
513 l_event_type := PAYMENT_CANCELLED_TYPE;
514 END IF;
515
516 ----------------------------------------------------
517 -- Step 1: Create payment event
518 ----------------------------------------------------
519 l_log_msg := 'Before calling procedure create_payment_event';
520 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
521 FND_LOG.STRING(G_LEVEL_PROCEDURE,
522 G_MODULE_NAME || l_procedure_name,
523 l_log_msg);
524 END IF;
525
526
527 p_accounting_event_id :=
528 create_payment_event(p_event_type => l_event_type,
529 p_check_id => p_doc_id,
530 p_event_date => p_accounting_date,
531 p_calling_sequence => l_curr_calling_sequence);
532
533 l_log_msg := 'p_accounting_event_id = '|| p_accounting_event_id;
534 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
535 FND_LOG.STRING(G_LEVEL_STATEMENT,
536 G_MODULE_NAME || l_procedure_name,
537 l_log_msg);
538 END IF;
539
540 l_log_msg := 'After calling Procedure create_payment_event';
541 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
542 FND_LOG.STRING(G_LEVEL_PROCEDURE,
543 G_MODULE_NAME || l_procedure_name,
544 l_log_msg);
545 END IF;
546
547
548
549 -----------------------------------------------------
550 -- Step 2: calling update payment event to 'No Action'
551 -----------------------------------------------------
552 l_log_msg := 'comment out procedure no_action_pmt_event_update';
553 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
554 FND_LOG.STRING(G_LEVEL_PROCEDURE,
555 G_MODULE_NAME || l_procedure_name,
556 l_log_msg);
557 END IF;
558
559 -- Bug 4927664 comment out the NoAction update
560 /* start to comment out
561 no_action_pmt_event_update(p_check_id => p_doc_id,
562 p_event_type_code => l_event_type,
563 p_accounting_date => p_accounting_date,
564 p_accounting_event_id => p_accounting_event_id,
568 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
565 p_calling_sequence => l_curr_calling_sequence);
566
567 l_log_msg := 'After calling procedure no_action_pmt_event_update';
569 FND_LOG.STRING(G_LEVEL_PROCEDURE,
570 G_MODULE_NAME || l_procedure_name,
571 l_log_msg);
572 END IF; End of comment out */
573
574 ------------------------------------------------------------
575 -- CASE: 'PAYMENT CLEARING'
576 ------------------------------------------------------------
577 WHEN ('PAYMENT CLEARING') THEN
578
579
580 l_log_msg := 'Before calling procedure create_payment_event';
581 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
582 FND_LOG.STRING(G_LEVEL_PROCEDURE,
583 G_MODULE_NAME || l_procedure_name,
584 l_log_msg);
585 END IF;
586
587 p_accounting_event_id :=
588 create_payment_event(p_event_type => PAYMENT_CLEARED_TYPE,
589 p_check_id => p_doc_id,
590 p_event_date => p_accounting_date,
591 p_calling_sequence => l_curr_calling_sequence);
592
593 l_log_msg := 'p_accounting_event_id = '|| p_accounting_event_id;
594 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
595 FND_LOG.STRING(G_LEVEL_STATEMENT,
596 G_MODULE_NAME || l_procedure_name,
597 l_log_msg);
598 END IF;
599
600 l_log_msg := 'After calling procedure create_payment_event';
601 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
602 FND_LOG.STRING(G_LEVEL_PROCEDURE,
603 G_MODULE_NAME || l_procedure_name,
604 l_log_msg);
605 END IF;
606
607 ------------------------------------------------------------
608 -- CASE: 'PAYMENT UNCLEARING'
609 ------------------------------------------------------------
610 WHEN ('PAYMENT UNCLEARING') THEN
611
612 ----------------------------------------------------
613 -- Step 1: Create payment event
614 ----------------------------------------------------
615 l_log_msg := 'Before calling procedure create_payment_event';
616 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
617 FND_LOG.STRING(G_LEVEL_PROCEDURE,
618 G_MODULE_NAME || l_procedure_name,
619 l_log_msg);
620 END IF;
621
622 p_accounting_event_id :=
623 create_payment_event
624 ( p_event_type => PAYMENT_UNCLEARED_TYPE,
625 p_check_id => p_doc_id,
626 p_event_date => p_accounting_date,
627 p_calling_sequence => l_curr_calling_sequence
628 );
629
630 l_log_msg := 'p_accounting_event_id = '|| p_accounting_event_id;
631 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
632 FND_LOG.STRING(G_LEVEL_STATEMENT,
633 G_MODULE_NAME || l_procedure_name,
634 l_log_msg);
635 END IF;
636
637 l_log_msg := 'After calling procedure create_payment_event';
638 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
639 FND_LOG.STRING(G_LEVEL_PROCEDURE,
640 G_MODULE_NAME || l_procedure_name,
641 l_log_msg);
642 END IF;
643
644 ----------------------------------------------------
645 -- Step 2: Update payment event to 'No Action'
646 ----------------------------------------------------
647 l_log_msg := 'comment out calling procedure no_action_pmt_event_update';
648 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
649 FND_LOG.STRING(G_LEVEL_PROCEDURE,
650 G_MODULE_NAME || l_procedure_name,
651 l_log_msg);
652 END IF;
653
654 -- Bug 4927664 comment out the NoAction update
655 /* start to comment out
656 no_action_pmt_event_update
657 ( p_check_id => p_doc_id,
658 p_event_type_code => PAYMENT_UNCLEARED_TYPE,
659 p_accounting_date => p_accounting_date,
660 p_accounting_event_id => p_accounting_event_id,
661 p_calling_sequence => l_curr_calling_sequence
662 );
663
664 l_log_msg := 'After calling procedure no_action_pmt_event_update';
665 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
666 FND_LOG.STRING(G_LEVEL_PROCEDURE,
667 G_MODULE_NAME || l_procedure_name,
668 l_log_msg);
669 END IF; End of comment out */
670
671 ------------------------------------------------------------
672 -- CASE: 'PAYMENT MATURITY'
673 ------------------------------------------------------------
674 WHEN ('PAYMENT MATURITY') THEN
675
676 l_log_msg := 'Before calling procedure create_payment_event';
677 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
678 FND_LOG.STRING(G_LEVEL_PROCEDURE,
679 G_MODULE_NAME || l_procedure_name,
680 l_log_msg);
681 END IF;
682
683 p_accounting_event_id :=
687 p_event_date => p_accounting_date,
684 create_payment_event
685 ( p_event_type => PAYMENT_MATURED_TYPE,
686 p_check_id => p_doc_id,
688 p_calling_sequence => l_curr_calling_sequence
689 );
690
691 l_log_msg := 'p_accounting_event_id = '|| p_accounting_event_id;
692 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
693 FND_LOG.STRING(G_LEVEL_STATEMENT,
694 G_MODULE_NAME || l_procedure_name,
695 l_log_msg);
696 END IF;
697
698 l_log_msg := 'After calling procedure create_payment_event';
699 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
700 FND_LOG.STRING(G_LEVEL_PROCEDURE,
701 G_MODULE_NAME || l_procedure_name,
702 l_log_msg);
703 END IF;
704
705 ------------------------------------------------------------
706 -- CASE: 'PAYMENT MATURITY REVERSAL'
707 ------------------------------------------------------------
708 WHEN ('PAYMENT MATURITY REVERSAL') THEN
709
710 ----------------------------------------------------
711 -- Step 1: Create payment event
712 ----------------------------------------------------
713 l_log_msg := 'Before calling procedure create_payment_event';
714 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
715 FND_LOG.STRING(G_LEVEL_PROCEDURE,
716 G_MODULE_NAME || l_procedure_name,
717 l_log_msg);
718 END IF;
719
720 p_accounting_event_id :=
721 create_payment_event
722 ( p_event_type => PAYMENT_MATURITY_REVERSED_TYPE,
723 p_check_id => p_doc_id,
724 p_event_date => p_accounting_date,
725 p_calling_sequence => l_curr_calling_sequence
726 );
727
728 l_log_msg := 'p_accounting_event_id = '|| p_accounting_event_id;
729 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
730 FND_LOG.STRING(G_LEVEL_STATEMENT,
731 G_MODULE_NAME || l_procedure_name,
732 l_log_msg);
733 END IF;
734
735 l_log_msg := 'After calling procedure create_payment_event';
736 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
737 FND_LOG.STRING(G_LEVEL_PROCEDURE,
738 G_MODULE_NAME || l_procedure_name,
739 l_log_msg);
740 END IF;
741
742 ----------------------------------------------------
743 -- Step 2: Update payment event status to 'No Action'
744 ----------------------------------------------------
745 l_log_msg := 'comment out procedure no_action_pmt_event_update';
746 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
747 FND_LOG.STRING(G_LEVEL_PROCEDURE,
748 G_MODULE_NAME || l_procedure_name,
749 l_log_msg);
750 END IF;
751
752 -- Bug 4927664 comment out the NoAction update
753 /* start to comment out
754 no_action_pmt_event_update
755 ( p_check_id => p_doc_id,
756 p_event_type_code => PAYMENT_MATURITY_REVERSED_TYPE,
757 p_accounting_date => p_accounting_date,
758 p_accounting_event_id => p_accounting_event_id,
759 p_calling_sequence => l_curr_calling_sequence
760 );
761
762 l_log_msg := 'After Calling procedure no_pmt_event_update';
763 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
764 FND_LOG.STRING(G_LEVEL_PROCEDURE,
765 G_MODULE_NAME || l_procedure_name,
766 l_log_msg);
767 END IF; End of comment out */
768
769 ELSE --other p_event_type
770
771 l_log_msg := 'Exception calling sequence '
772 ||l_curr_calling_sequence
773 ||' Error:Wrong p_event_type= '
774 ||p_event_type;
775 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
776 FND_LOG.STRING(G_LEVEL_EXCEPTION,
777 G_MODULE_NAME || l_procedure_name,
778 l_log_msg);
779 END IF;
780
781 APP_EXCEPTION.RAISE_EXCEPTION();
782
783 END CASE; --p_event_type
784
785 l_log_msg := 'End of procedure '|| l_procedure_name;
786 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
787 FND_LOG.STRING(G_LEVEL_PROCEDURE,
788 G_MODULE_NAME||l_procedure_name||'.end',
789 l_log_msg);
790 END IF;
791
792 EXCEPTION
793 WHEN OTHERS THEN
794 IF (SQLCODE <> -20001) THEN
795 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
796 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
797 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
798 l_curr_calling_sequence);
799 FND_MESSAGE.SET_TOKEN('PARAMETERS',
800 'p_doc_type = '||p_doc_type
801 ||', p_doc_id = '||p_doc_id
802 ||', p_accounting_date = '||p_accounting_date
803 ||', p_checkrun_name = '||p_checkrun_name);
804 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
805 END IF;
806 APP_EXCEPTION.RAISE_EXCEPTION();
807
808 END create_events;
812 |
809
810 /*============================================================================
811 | PROCEDURE - DERIVE_INVOICE_EVENTS (PRIVATE)
813 | DESCRIPTION
814 | This procedure is responsible for creating Invoice Validated, Invoice
815 | Adjusted (and corresponding events for Credit Memos, Debit Memos, and | Prepayments), Prepayment Applied and Unapplied Events.
816 |
817 | PRAMETERS
818 | p_invoice_id: Invoice ID
819 | p_calling_sequence: Debug info
820 |
821 | KNOWN ISSUES:
822 |
823 | NOTES:
824 |
825 | MODIFICATION HISTORY
826 | Date Author Description of Change
827 |
828 *===========================================================================*/
829
830 PROCEDURE Derive_Invoice_Events (p_invoice_id IN NUMBER,
831 p_calling_sequence IN VARCHAR2)
832 IS
833
834 ----------------------------------------------------------------------------
835 -- We will check if the invoice is approved and that an event does not
836 -- already exist for this distribution. If encumbrances are being used, we
837 -- only want to select distributions with a match_status_flag of 'A',
838 -- otherwise we want to choose distributions with a status of 'A' or 'T'.
839 -----------------------------------------------------------------------------
840 CURSOR Inv_event_dists IS
841 SELECT accounting_date accounting_date,
842 sum(amount) dist_amount,
843 decode (line_type_lookup_code, 'PREPAY','PREPAY','OTHER') dist_type,
844 -- decode (line_type_lookup_code, 'PREPAY',
845 -- invoice_distribution_id, -1) invoice_distribution_id,
846 -- Bug 6931461: invoice_line_number invoice_line_number
847 decode(line_type_lookup_code, 'PREPAY', invoice_line_number, 1) invoice_line_number
848 FROM ap_invoice_distributions AID, financials_system_parameters FSP
849 WHERE AID.invoice_id = P_invoice_id
850 AND AID.awt_invoice_payment_id is NULL
851 AND AID.org_id = FSP.org_id -- Bug 4516136
852 AND AID.set_of_books_id = FSP.set_of_books_id -- Bug 5608968 Avoid full index scan of fsp
853 AND (AID.prepay_distribution_id IS NULL -- prepay_tax_parent_id obsoleted
854 OR AID.charge_Applicable_to_dist_id is NULL) --Added for bug 4643339
855 AND AID.accounting_event_id is NULL
856 AND NVL(AID.cancellation_flag, 'N') = 'N' -- replaced cancellation_date
857 AND (
858 (nvl(FSP.purch_encumbrance_flag,'N') = 'N'
859 AND match_Status_flag IN ('T','A')
860 )
861 OR
862 ((nvl(FSP.purch_encumbrance_flag,'N') = 'Y'
863 AND match_Status_flag = 'A')))
864 -- since 'OTHER' comes before 'PREPAY' alphabetically, a prepayment
865 -- event will not be created first
866 GROUP BY accounting_date,
867 decode (line_type_lookup_code, 'PREPAY','PREPAY','OTHER'),
868 -- Bug 6718967. Fix to create two events for prepayment applied
869 -- and unapplied.
870 decode (line_type_lookup_code, 'PREPAY',
871 decode(nvl(parent_reversal_id,-99), -99, 1, 2), 3),
872 -- decode (line_type_lookup_code, 'PREPAY',
873 -- invoice_distribution_id, -1),
874 decode(line_type_lookup_code, 'PREPAY', invoice_line_number, 1) --Bug 6931461
875 ORDER BY dist_type, accounting_date;
876
877 CURSOR prepay_adj_events(l_prepay_app_event_id NUMBER,
878 l_accounting_date DATE) is
879 SELECT APPH.accounting_event_id
880 FROM AP_PREPAY_HISTORY_ALL APPH, AP_INVOICE_DISTRIBUTIONS AID
881 WHERE APPH.related_prepay_app_Event_id = l_prepay_app_event_id
882 AND APPH.invoice_adjustment_event_id = AID.accounting_event_id
883 AND nvl(APPH.posted_flag,'N') = 'N'
884 AND AID.accounting_date = l_accounting_date;
885
886 l_event_num NUMBER;
887 l_accounting_event_id NUMBER := NULL;
888
889 l_event_type VARCHAR2(30);
890 l_event_class VARCHAR2(30);
891 l_same_gl_prepay_date DATE;
892 l_accounting_date DATE;
893 l_pay_accounting_event_id NUMBER;
894 l_prepay_event_id NUMBER;
895 l_event_status VARCHAR2(1);
896 l_prepay_Adj_count NUMBER;
897
898 l_legal_entity_id NUMBER(15);
899 l_ledger_id NUMBER(15);
900 l_org_id NUMBER(15);
901 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
902 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
903 l_prepay_app_event_id NUMBER;
904 l_inv_adj_event_id NUMBER;
905 l_curr_calling_sequence VARCHAR2(2000);
906 l_transaction_date AP_INVOICES_ALL.invoice_date%TYPE;
907
908 -- Logging:
909 l_procedure_name CONSTANT VARCHAR2(30) := 'DERIVE_INVOICE_EVENTS';
910 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
911
912 TYPE l_inv_dist_list IS TABLE OF ap_invoice_distributions.invoice_distribution_id%TYPE;
913 l_inv_dist_tab l_inv_dist_list;
914
915 BEGIN
916
917 l_curr_calling_sequence :=
918 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.DERIVE_INVOICE_EVENTS';
919
920 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
921
922 l_log_msg := 'Begin of procedure '|| l_procedure_name;
923 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
924 FND_LOG.STRING(G_LEVEL_PROCEDURE,
928
925 G_MODULE_NAME||l_procedure_name||'.begin',
926 l_log_msg);
927 END IF;
929 --------------------------------------------------------------------
930 -- Figure out the existing adjustment event id.
931 --------------------------------------------------------------------
932 FOR event_dist_rec IN inv_event_dists LOOP
933
934 l_accounting_event_id := NULL;
935
936 SELECT COUNT(distinct(accounting_event_id))
937 INTO l_event_num
938 FROM ap_invoice_distributions
939 WHERE invoice_id = p_invoice_id;
940
941
942
943 -- Added for prepayment events
944 IF (event_dist_rec.dist_type = 'PREPAY') THEN
945
946 IF (event_dist_rec.dist_amount < 0) THEN
947 l_event_type := PREPAYMENT_APPLIED_TYPE;
948 ELSE
949 l_event_type := PREPAYMENT_UNAPPLIED_TYPE;
950 END IF;
951 l_event_class := PREPAYMENT_APPLICATIONS_CLASS;
952
953 -- if an accounting event already exists for the invoice line related
954 -- to this distribution
955 -- then stamp this distribution with that the ID of that
956 BEGIN
957
958 SELECT distinct(AID.accounting_event_id)
959 INTO l_accounting_event_id
960 FROM ap_invoice_distributions AID,
961 ap_invoice_lines AIL
962 WHERE AID.accounting_date = event_dist_rec.accounting_date
963 AND AIL.invoice_id = P_invoice_id
964 AND AIL.line_number = event_dist_rec.invoice_line_number
965 AND AIL.invoice_id = AID.invoice_id
966 AND AIL.line_number = AID.invoice_line_number
967 AND AID.accounting_date = AIL.accounting_date
968 AND AID.line_type_lookup_code = 'PREPAY'
969 AND nvl(posted_flag,'N') = 'N'
970 AND sign(AID.amount) = sign(event_dist_rec.dist_amount)
971 AND AID.accounting_event_id IS NOT NULL;
972 EXCEPTION
973 WHEN NO_DATA_FOUND THEN
974 l_log_msg := 'When no_data_found';
975 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
976 FND_LOG.STRING(G_LEVEL_PROCEDURE,
977 G_MODULE_NAME || l_procedure_name,
978 l_log_msg);
979 END IF;
980
981 l_log_msg := 'Before calling procedure create_invoice_event';
982 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
983 FND_LOG.STRING(G_LEVEL_PROCEDURE,
984 G_MODULE_NAME || l_procedure_name,
985 l_log_msg);
986 END IF;
987
988 l_accounting_event_id :=
989 create_invoice_event (p_event_type => l_event_type,
990 p_invoice_id => p_invoice_id,
991 p_event_date => event_dist_rec.accounting_date,
992 p_calling_sequence => l_curr_calling_sequence);
993
994
995 l_log_msg := 'After calling procedure create_invoice_event';
996 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
997 FND_LOG.STRING(G_LEVEL_PROCEDURE,
998 G_MODULE_NAME || l_procedure_name,
999 l_log_msg);
1000 END IF;
1001
1002 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1003 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1004 G_MODULE_NAME || l_procedure_name,
1005 l_log_msg);
1006 END IF;
1007
1008 -- Bug 4996808. Calling the update prepay header instead of the
1009 -- insert since the prepay header will be created during validation
1010 -- before the events are created.
1011 Update_Prepayment_Header
1012 (p_invoice_id ,
1013 p_invoice_line_number => event_dist_rec.invoice_line_number,
1014 p_accounting_event_id => l_accounting_event_id,
1015 p_accounting_date => event_dist_rec.accounting_date,
1016 p_transaction_type => l_event_type,
1017 p_calling_sequence => l_curr_calling_sequence);
1018
1019 l_log_msg := 'After calling procedure update_prepayment_header';
1020 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1021 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1022 G_MODULE_NAME || l_procedure_name,
1023 l_log_msg);
1024 END IF;
1025 END;
1026
1027 IF (l_event_type = PREPAYMENT_UNAPPLIED_TYPE) THEN
1028
1029 -- Update event Prepay Application Adjustment events related to this
1030 -- Prepayment Application to 'No Action'
1031
1032 BEGIN
1033
1034 SELECT MAX(AID2.accounting_event_id)
1035 INTO l_prepay_app_event_id
1036 FROM AP_INVOICE_DISTRIBUTIONS AID, AP_INVOICE_DISTRIBUTIONS AID2
1037 WHERE AID.invoice_id = p_invoice_id
1038 AND AID.invoice_line_number = event_dist_rec.invoice_line_number
1039 AND AID.parent_reversal_id = AID2.invoice_distribution_id
1040 AND AID.accounting_date = AID2.accounting_date
1041 AND nvl(AID2.posted_flag,'N') = 'N';
1042
1043 EXCEPTION
1044 WHEN NO_DATA_FOUND THEN
1045 l_prepay_app_event_id := -1;
1049 IF l_prepay_app_event_id <> -1 THEN
1046
1047 END;
1048
1050
1051 l_log_msg := 'Before calling procedure get_invoice_info';
1052 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1053 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1054 G_MODULE_NAME || l_procedure_name,
1055 l_log_msg);
1056 END IF;
1057
1058 get_invoice_info(p_invoice_id => p_invoice_id,
1059 p_org_id => l_org_id, -- OUT
1060 p_legal_entity_id => l_legal_entity_id, -- OUT
1061 p_ledger_id => l_ledger_id, -- OUT
1062 p_transaction_date => l_transaction_date, -- OUT
1063 p_calling_sequence => l_curr_calling_sequence);
1064
1065 l_log_msg := 'After calling procedure get_insert_info executed';
1066 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1067 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1068 G_MODULE_NAME || l_procedure_name,
1069 l_log_msg);
1070 END IF;
1071
1072 l_log_msg :='Before calling proc get_event_security_context';
1073 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1074 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1075 G_MODULE_NAME || l_procedure_name,
1076 l_log_msg);
1077 END IF;
1078
1079 l_event_security_context :=
1080 get_event_security_context(p_org_id => l_org_id,
1081 p_calling_sequence => l_curr_calling_sequence);
1082
1083 l_log_msg := 'After calling proc get_event_security_context';
1084 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1085 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1086 G_MODULE_NAME || l_procedure_name,
1087 l_log_msg);
1088 END IF;
1089
1090 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1091 l_log_msg := 'Before calling proc get_invoice_event_source_info';
1092 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1093 G_MODULE_NAME || l_procedure_name,
1094 l_log_msg);
1095 END IF;
1096
1097 l_event_source_info :=
1098 get_invoice_event_source_info
1099 (p_legal_entity_id => l_legal_entity_id,
1100 p_ledger_id => l_ledger_id,
1101 p_invoice_id => p_invoice_id,
1102 p_calling_sequence => l_curr_calling_sequence);
1103
1104 l_log_msg := 'After calling proc get_invoice_envent_source_info';
1105 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1106 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1107 G_MODULE_NAME || l_procedure_name,
1108 l_log_msg);
1109 END IF;
1110
1111 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.UPDATE_EVENT';
1112 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1113 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1114 G_MODULE_NAME || l_procedure_name,
1115 l_log_msg);
1116 END IF;
1117
1118 -- Bug 4748373. Modified the event type to PREPAYMENT UNAPPLIED from
1119 -- PREPAYMENT APPLICATION UNAPPLIED
1120 --Update the Prepayment Unapplication event to No Action
1121
1122 -- Bug 4927664. For now comment out the event NOACTION update
1123 /* Start comment out
1124 AP_XLA_EVENTS_PKG.UPDATE_EVENT
1125 (p_event_source_info => l_event_source_info,
1126 p_event_id => l_Accounting_Event_id,
1127 p_event_type_code => 'PREPAYMENT UNAPPLIED',
1128 p_event_date => NULL,
1129 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
1130 p_valuation_method => NULL,
1131 p_security_context => l_event_security_context,
1132 p_calling_sequence => l_curr_calling_sequence);
1133
1134 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT';
1135 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1136 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1137 G_MODULE_NAME || l_procedure_name,
1138 l_log_msg);
1139 END IF; End comment out */
1140
1141 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.UPDATE_EVENT';
1142 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1143 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1144 G_MODULE_NAME || l_procedure_name,
1145 l_log_msg);
1146 END IF;
1147
1148 -- Bug 4748373. Modified the event type to PREPAYMENT APPLIED from
1149 -- PREPAYMENT APPLICATION APPLIED
1150 --Update the Prepayment Application Event to No Action
1151 -- Bug 4927664. For now comment out the event NOACTION update
1152 /* Start comment out
1153 AP_XLA_EVENTS_PKG.UPDATE_EVENT
1154 (p_event_source_info => l_event_source_info,
1158 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
1155 p_event_id => l_prepay_app_event_id,
1156 p_event_type_code => 'PREPAYMENT APPLIED',
1157 p_event_date => NULL,
1159 p_valuation_method => NULL,
1160 p_security_context => l_event_security_context,
1161 p_calling_sequence => l_curr_calling_sequence);
1162
1163 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT';
1164 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1165 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1166 G_MODULE_NAME || l_procedure_name,
1167 l_log_msg);
1168 END IF; End of comment out */
1169
1170
1171 l_Accounting_date:= event_dist_rec.accounting_date;
1172
1173 l_log_msg := 'prepay_adj_event loop begin.';
1174 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1175 FND_LOG.STRING(G_LEVEL_STATEMENT,
1176 G_MODULE_NAME || l_procedure_name,
1177 l_log_msg);
1178 END IF;
1179
1180 FOR l_prepay_adj_event_id IN
1181 prepay_adj_events
1182 (l_prepay_app_event_id,
1183 l_Accounting_date) LOOP
1184
1185 l_log_msg := 'Inside the prepay_adj_events cursor loop';
1186 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1187 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1188 G_MODULE_NAME || l_procedure_name,
1189 l_log_msg);
1190 END IF;
1191
1192 -- Bug 4748373. Modified the event type to PREPAYMENT APPLIED from
1193 -- PREPAYMENT APPLICATION UNAPPLIED
1194 --Update the Prepayment Application Adjusted Event to No Action
1195 -- Bug 4927664. For now comment out the event NOACTION update
1196 /* Start comment out
1197 AP_XLA_EVENTS_PKG.UPDATE_EVENT
1198 (p_event_source_info => l_event_source_info,
1199 p_event_id => l_prepay_app_event_id,
1200 p_event_type_code => 'PREPAYMENT APPLIED',
1201 p_event_date => NULL,
1202 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
1203 p_valuation_method => NULL,
1204 p_security_context => l_event_security_context,
1205 p_calling_sequence => l_curr_calling_sequence);
1206
1207 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT';
1208 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1209 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1210 G_MODULE_NAME || l_procedure_name,
1211 l_log_msg);
1212 END IF; End of comment out*/
1213
1214 END LOOP; --inv_event_dists
1215
1216 l_log_msg := 'prepay_adj_event loop end.';
1217 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1218 FND_LOG.STRING(G_LEVEL_STATEMENT,
1219 G_MODULE_NAME || l_procedure_name,
1220 l_log_msg);
1221 END IF;
1222 END IF;
1223
1224 END IF;
1225
1226 ELSIF NVL(l_event_num,0)= 0 THEN
1227
1228 SELECT DECODE ( AI.invoice_type_lookup_code,
1229 'CREDIT', CREDIT_MEMO_VALIDATED_TYPE,
1230 'DEBIT', DEBIT_MEMO_VALIDATED_TYPE,
1231 'PREPAYMENT', PREPAYMENT_VALIDATED_TYPE,
1232 INVOICE_VALIDATED_TYPE) event_type,
1233 DECODE ( AI.invoice_type_lookup_code,
1234 'CREDIT', CREDIT_MEMOS_CLASS,
1235 'DEBIT', DEBIT_MEMOS_CLASS,
1236 'PREPAYMENT', PREPAYMENTS_CLASS,
1237 INVOICES_CLASS) event_class
1238 INTO l_event_type, l_event_class
1239 FROM ap_invoices_all AI
1240 WHERE AI.invoice_id = p_invoice_id;
1241
1242 l_log_msg := 'Before calling procedure create_invoice_event';
1243 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1244 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1245 G_MODULE_NAME || l_procedure_name,
1246 l_log_msg);
1247 END IF;
1248
1249 l_accounting_event_id :=
1250 create_invoice_event
1251 ( p_event_type => l_event_type,
1252 p_invoice_id => p_invoice_id,
1253 p_event_date => event_dist_rec.accounting_date,
1254 p_calling_sequence => l_curr_calling_sequence
1255 );
1256
1257 l_log_msg := 'After calling procedure create_invoice_event executed';
1258 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1259 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1260 G_MODULE_NAME || l_procedure_name,
1261 l_log_msg);
1262 END IF;
1263
1264 ELSE
1265
1266 SELECT DECODE ( AI.invoice_type_lookup_code,
1267 'CREDIT', CREDIT_MEMO_ADJUSTED_TYPE,
1268 'DEBIT', DEBIT_MEMO_ADJUSTED_TYPE,
1269 'PREPAYMENT', PREPAYMENT_ADJUSTED_TYPE,
1270 INVOICE_ADJUSTED_TYPE) event_type,
1274 'PREPAYMENT', PREPAYMENTS_CLASS,
1271 DECODE ( AI.invoice_type_lookup_code,
1272 'CREDIT', CREDIT_MEMOS_CLASS,
1273 'DEBIT', DEBIT_MEMOS_CLASS,
1275 INVOICES_CLASS) event_class
1276 INTO l_event_type, l_event_class
1277 FROM ap_invoices_all AI
1278 WHERE AI.invoice_id = p_invoice_id;
1279
1280 BEGIN
1281 SELECT MAX(accounting_event_id)
1282 INTO l_accounting_event_id
1283 FROM ap_invoice_distributions
1284 WHERE invoice_id = p_invoice_id
1285 AND NVL(posted_flag, 'N') <> 'Y'
1286 AND line_type_lookup_code <> 'PREPAY'
1287 AND prepay_distribution_id is NULL --for prepay tax
1288 AND accounting_date = event_dist_rec.accounting_date
1289 AND awt_invoice_payment_id is null; -- Bug 7410001
1290 EXCEPTION
1291 WHEN NO_DATA_FOUND THEN
1292 l_accounting_event_id := NULL;
1293 END;
1294
1295 BEGIN
1296 SELECT MAX(accounting_event_id)
1297 INTO l_prepay_event_id
1298 FROM ap_invoice_distributions AID
1299 WHERE AID.invoice_id = p_invoice_id
1300 AND AID.line_type_lookup_code = 'PREPAY'
1301 AND AID.amount < 0
1302 AND AID.posted_flag = 'Y';
1303 EXCEPTION
1304 WHEN NO_DATA_FOUND THEN
1305 l_prepay_event_id := NULL;
1306 END;
1307
1308 BEGIN
1309 SELECT MAX(accounting_event_id)
1310 INTO l_pay_accounting_event_id
1311 FROM ap_invoice_payments AIP
1312 WHERE AIP.invoice_id = p_invoice_id
1313 AND AIP.posted_flag = 'Y';
1314 EXCEPTION
1315 WHEN NO_DATA_FOUND THEN
1316 l_pay_accounting_event_id := NULL;
1317 END;
1318
1319 -- If an unaccounted Invoice Validates or Invoice Adjustment
1320 -- event does not already exist for that date, we will create
1321 -- a new event.
1322
1323 -- bug fix 5694577: fixed the following condition to raise invoice
1324 -- validate events properly.
1325 -- IF ( (l_accounting_event_id IS NULL)
1326 -- OR
1327 -- (l_pay_accounting_event_id IS NOT NULL)
1328 -- OR
1329 -- (l_prepay_event_id IS NOT NULL)
1330 -- ) THEN
1331
1332 IF (l_accounting_event_id IS NULL) THEN
1333
1334 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1335 l_log_msg := 'Before calling procedure create_invoice_event';
1336 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1337 G_MODULE_NAME || l_procedure_name,
1338 l_log_msg);
1339 END IF;
1340
1341 l_accounting_event_id :=
1342 create_invoice_event
1343 ( p_event_type => l_event_type,
1344 p_invoice_id => p_invoice_id,
1345 p_event_date => event_dist_rec.accounting_date,
1346 p_calling_sequence => l_curr_calling_sequence
1347 );
1348
1349 l_log_msg := 'After calling procedure create_invoice_event executed';
1350 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1351 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1352 G_MODULE_NAME || l_procedure_name,
1353 l_log_msg);
1354 END IF;
1355
1356 -- raise payment/prepayment cascade event only when there is payment
1357 -- or prepayment application existed for the invoice.
1358
1359 IF ((l_pay_accounting_event_id IS NOT NULL)
1360 OR
1361 (l_prepay_event_id IS NOT NULL))
1362 THEN
1363
1364 l_log_msg := 'Before calling procedure derive_cascade_events';
1365 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1366 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1367 G_MODULE_NAME || l_procedure_name,
1368 l_log_msg);
1369 END IF;
1370
1371 -- Bug 6996047. Passing accounting date so that the adjustment
1372 -- events are created in the same gl date as the invoice event.
1373 derive_cascade_events
1374 ( p_invoice_id => p_invoice_id,
1375 p_adj_accounting_event_id => l_accounting_event_id,
1376 p_accounting_date => event_dist_rec.accounting_date,
1377 p_calling_sequence => l_curr_calling_sequence
1378 );
1379
1380 l_log_msg := 'After calling proc derive_cascade_events executed';
1381 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1382 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1383 G_MODULE_NAME || l_procedure_name,
1384 l_log_msg);
1385 END IF;
1386 END IF;
1387
1388 ELSE
1389
1390 -- For cases where we are not creating the event (since it already
1391 -- exists), we want to check for new status of those existing events.
1392
1393 IF
1394 ( is_event_complete ( p_doc_type => INVOICES_ENTITY,
1395 p_source_id => p_invoice_id,
1396 p_calling_sequence => l_curr_calling_sequence
1397 ) = 'CREATED') THEN
1398 l_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED;
1399 ELSE
1403 get_invoice_info
1400 l_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE;
1401 END IF;
1402
1404 ( p_invoice_id => p_invoice_id,
1405 p_org_id => l_org_id, -- OUT
1406 p_legal_entity_id => l_legal_entity_id, -- OUT
1407 p_ledger_id => l_ledger_id, -- OUT
1408 p_transaction_date => l_transaction_date, -- OUT
1409 p_calling_sequence => l_curr_calling_sequence
1410 );
1411
1412 l_event_security_context :=
1413 get_event_security_context
1414 ( p_org_id => l_org_id,
1415 p_calling_sequence => l_curr_calling_sequence
1416 );
1417
1418 l_event_source_info :=
1419 get_invoice_event_source_info
1420 ( p_legal_entity_id => l_legal_entity_id,
1421 p_ledger_id => l_ledger_id,
1422 p_invoice_id => p_invoice_id,
1423 p_calling_sequence => l_curr_calling_sequence
1424 );
1425
1426 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
1427 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1428 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1429 G_MODULE_NAME || l_procedure_name,
1430 l_log_msg);
1431 END IF;
1432
1433 AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
1434 ( p_event_source_info => l_event_source_info,
1435 p_event_class_code => l_event_class,
1436 p_event_type_code => l_event_type,
1437 p_event_date => event_dist_rec.accounting_date,
1438 p_event_status_code => l_event_status,
1439 p_valuation_method => NULL,
1440 p_security_context => l_event_security_context,
1441 p_calling_sequence => l_curr_calling_sequence
1442 );
1443
1444 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
1445 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1446 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1447 G_MODULE_NAME || l_procedure_name,
1448 l_log_msg);
1449 END IF;
1450
1451 END IF;
1452
1453 END IF;
1454
1455 --Added IF to ensure that only non prepay lines are updated
1456
1457 -- These updates are being done in the context of the loop as each
1458 -- cycle of the loop could be related to a different event
1459 IF ( l_event_type NOT IN ( PREPAYMENT_APPLIED_TYPE,
1460 PREPAY_APP_ADJUSTED_TYPE,
1461 PREPAYMENT_UNAPPLIED_TYPE)) THEN
1462
1463 UPDATE ap_invoice_distributions
1464 SET accounting_event_id = l_accounting_event_id
1465 WHERE invoice_id = p_invoice_id
1466 AND accounting_date = event_dist_rec.accounting_date
1467 AND awt_invoice_payment_id IS NULL
1468 AND line_type_lookup_code <> 'PREPAY'
1469 AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
1470 AND accounting_event_id IS NULL
1471 AND nvl(cancellation_flag, 'N') = 'N'; -- Bug 5455054: Added the cancellation_flag check.
1472
1473 -------------------------------------------------
1474 -- bug 5525657
1475 -- We need to stamp the same accounting event id
1476 -- for self assessed tax distributions.
1477 -------------------------------------------------
1478
1479 UPDATE ap_self_assessed_tax_dist_all
1480 SET accounting_event_id = l_accounting_event_id
1481 WHERE invoice_id = p_invoice_id
1482 AND accounting_date = event_dist_rec.accounting_date
1483 AND awt_invoice_payment_id IS NULL
1484 AND line_type_lookup_code <> 'PREPAY'
1485 AND prepay_distribution_id IS NULL
1486 AND accounting_event_id IS NULL
1487 AND nvl(cancellation_flag, 'N') = 'N';
1488
1489 ELSE -- for prepay distribution lines
1490
1491 UPDATE ap_invoice_distributions
1492 SET accounting_event_id = l_accounting_event_id
1493 WHERE accounting_event_id IS NULL
1494 AND invoice_id = p_invoice_id
1495 AND invoice_line_number = event_dist_rec.invoice_line_number
1496 AND accounting_date = event_dist_rec.accounting_date
1497 AND line_type_lookup_code = 'PREPAY'
1498 -- Bug 6718967
1499 AND sign(amount) = sign(event_dist_rec.dist_amount)
1500 RETURNING invoice_distribution_id BULK COLLECT INTO l_inv_dist_tab;
1501
1502 FORALL i IN l_inv_dist_tab.FIRST..l_inv_dist_tab.LAST
1503 UPDATE ap_invoice_distributions_all
1504 SET accounting_event_id = l_accounting_event_id
1505 WHERE line_type_lookup_code in ('REC_TAX','NONREC_TAX',
1506 'TRV','TIPV','TERV') --Bug5455985
1507 AND accounting_event_id IS NULL
1508 AND charge_applicable_to_dist_id = l_inv_dist_tab(i);
1509
1510 END IF; -- prepay distribution lines
1511
1512 END LOOP;
1513
1514 l_log_msg := 'End of procedure '|| l_procedure_name;
1515 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1516 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1517 G_MODULE_NAME||l_procedure_name||'.begin',
1518 l_log_msg);
1519 END IF;
1520
1521 EXCEPTION
1522 WHEN OTHERS THEN
1523 IF (inv_event_dists%ISOPEN) THEN
1527 IF (SQLCODE <> -20001) THEN
1524 CLOSE inv_event_dists;
1525 END IF;
1526
1528 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1529 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1530 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1531 l_curr_calling_sequence);
1532 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1533 'p_invoice_id = '||p_invoice_id);
1534 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
1535 END IF;
1536
1537 APP_EXCEPTION.RAISE_EXCEPTION();
1538 END derive_invoice_events;
1539
1540
1541 /*============================================================================
1542 | PUNCTION - CREATE_INVOICE_EVENT (PRIVATE)
1543 |
1544 | DESCRIPTION
1545 | Create accounting events for 'INVOICES' type
1546 |
1547 | PRAMETERS
1548 | p_event_type: Event type
1549 | p_invoice_id: Invoice Unique Identifier
1550 | P_event_date: Event date
1551 | p_calling_sequence: Debug information
1552 |
1553 | RETURN TYPE: NUMBER
1554 |
1555 | KNOWN ISSUES:
1556 |
1557 | NOTES:
1558 |
1559 | MODIFICATION HISTORY
1560 | Date Author Description of Change
1561 |
1562 *===========================================================================*/
1563 FUNCTION create_invoice_event(p_event_type IN VARCHAR2,
1564 p_invoice_id IN NUMBER,
1565 p_event_date IN DATE,
1566 p_calling_sequence IN VARCHAR2)
1567 RETURN NUMBER
1568 IS
1569
1570 l_event_status VARCHAR2(1);
1571 l_legal_entity_id NUMBER(15);
1572 l_ledger_id NUMBER(15);
1573 l_org_id NUMBER(15);
1574 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
1575 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
1576 l_transaction_date AP_INVOICES_ALL.invoice_date%TYPE;
1577
1578 l_curr_calling_sequence VARCHAR2(2000);
1579
1580 -- Logging:
1581 l_procedure_name CONSTANT VARCHAR2(30) := 'CREATE_INVOICE_EVENT';
1582 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1583
1584 BEGIN
1585
1586 l_curr_calling_sequence :=
1587 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.CREATE_INVOICE_EVENT';
1588
1589 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1590
1591 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1592 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1593 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1594 G_MODULE_NAME || l_procedure_name,
1595 l_log_msg);
1596 END IF;
1597
1598 IF
1599 ( is_event_complete
1600 ( p_doc_type => INVOICES_ENTITY,
1601 p_source_id => p_invoice_id,
1602 p_calling_sequence => l_curr_calling_sequence) = 'CREATED') THEN
1603 l_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED;
1604 ELSE
1605 l_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE;
1606 END IF;
1607
1608 get_invoice_info
1609 ( p_invoice_id => p_invoice_id,
1610 p_org_id => l_org_id, -- OUT
1611 p_legal_entity_id => l_legal_entity_id, -- OUT
1612 p_ledger_id => l_ledger_id, -- OUT
1613 p_transaction_date => l_transaction_date, -- OUT
1614 p_calling_sequence => l_curr_calling_sequence
1615 );
1616
1617 l_event_security_context :=
1618 get_event_security_context
1619 ( p_org_id => l_org_id,
1620 p_calling_sequence => l_curr_calling_sequence
1621 );
1622
1623 l_event_source_info :=
1624 get_invoice_event_source_info
1625 ( p_legal_entity_id => l_legal_entity_id,
1626 p_ledger_id => l_ledger_id,
1627 p_invoice_id => p_invoice_id,
1628 p_calling_sequence => l_curr_calling_sequence
1629 );
1630
1631 l_log_msg := 'End of procedure '|| l_procedure_name;
1632 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1633 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1634 G_MODULE_NAME || l_procedure_name,
1635 l_log_msg);
1636 END IF;
1637
1638 l_log_msg := 'Calling proc AP_XLA_EVENTS_PKG.CREATE_EVENT and return';
1639 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1640 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1641 G_MODULE_NAME || l_procedure_name,
1642 l_log_msg);
1643 END IF;
1644
1645 RETURN
1646 AP_XLA_EVENTS_PKG.CREATE_EVENT
1647 ( p_event_source_info => l_event_source_info,
1648 p_event_type_code => p_event_type,
1649 p_event_date => p_event_date,
1650 p_event_status_code => l_event_status,
1651 p_event_number => NULL,
1652 p_transaction_date => l_transaction_date,
1653 p_reference_info => NULL,
1654 p_valuation_method => NULL,
1655 p_security_context => l_event_security_context,
1656 p_calling_sequence => l_curr_calling_sequence
1657 );
1658
1659
1660 EXCEPTION
1661 WHEN OTHERS THEN
1662 IF (SQLCODE <> -20001) THEN
1663 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1664 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1665 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1666 l_curr_calling_sequence);
1667 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1671 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
1668 'p_event_type = '||p_event_type
1669 ||', p_invoice_id = '||p_invoice_id
1670 ||', p_event_date = '||p_event_date);
1672 END IF;
1673
1674 APP_EXCEPTION.RAISE_EXCEPTION();
1675 END create_invoice_event;
1676
1677
1678
1679 /*============================================================================
1680 | PUNCTION - CREATE_PAYMENT_EVENT (PRIVATE)
1681 |
1682 | DESCRIPTION
1683 | Create accounting events for 'PAYMENT' type
1684 |
1685 | PRAMETERS
1686 | p_event_type: Event type
1687 | p_check_id: Check ID
1688 | p_event_date: Event date
1689 | p_calling_sequence: Debug information
1690 |
1691 | RETURN TYPE: NUMBER
1692 |
1693 | KNOWN ISSUES:
1694 |
1695 | NOTES:
1696 |
1697 | MODIFICATION HISTORY
1698 | Date Author Description of Change
1699 |
1700 *===========================================================================*/
1701 FUNCTION create_payment_event( p_event_type IN VARCHAR2,
1702 p_check_id IN NUMBER,
1703 p_event_date IN DATE,
1704 p_calling_sequence IN VARCHAR2)
1705 RETURN NUMBER
1706 IS
1707
1708 l_event_status VARCHAR2(1);
1709 l_legal_entity_id NUMBER(15);
1710 l_ledger_id NUMBER(15);
1711 l_org_id NUMBER(15);
1712 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
1713 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
1714 l_matured_events_count NUMBER;
1715 l_unmatured_events_count NUMBER;
1716 l_amount NUMBER;
1717 l_currency_code VARCHAR2(15);
1718 l_maturity_exchange_rate_type VARCHAR2(30);
1719 l_maturity_exchange_date DATE;
1720 l_maturity_exchange_rate NUMBER;
1721 l_ret NUMBER;
1722 l_curr_calling_sequence VARCHAR2(2000);
1723
1724 -- Logging:
1725 l_procedure_name CONSTANT VARCHAR2(30) := 'CREATE_PAYMENT_EVENT';
1726 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1727
1728
1729 -- Bug 7374984.
1730 -- Cursor to get all the unaccounted maturity and clearing transactions
1731 CURSOR c_unacct_pmt_events IS
1732 SELECT accounting_date,
1733 accounting_event_id,
1734 transaction_type
1735 FROM ap_payment_history
1736 WHERE check_id = p_check_id
1737 AND posted_flag <> 'Y'
1738 AND transaction_type IN (PAYMENT_MATURTY_RVRSL_TRX_TYPE, PAYMENT_MATURTY_TRX_TYPE,
1739 PAYMENT_MATURITY_ADJUSTED_TYPE, PAYMENT_CLEARED_TRX_TYPE,
1740 PAYMENT_UNCLEARED_TRX_TYPE, PAYMENT_CLEARING_ADJUSTED_TYPE);
1741
1742 l_accounting_date DATE;
1743 l_accounting_event_id NUMBER;
1744 l_transaction_type VARCHAR2(30);
1745
1746 BEGIN
1747
1748 l_curr_calling_sequence :=
1749 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.CREATE_PAYMENT_EVENT';
1750
1751 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1752
1753 l_log_msg := 'Begin of procedure '|| l_procedure_name;
1754 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1755 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1756 G_MODULE_NAME || l_procedure_name,
1757 l_log_msg);
1758 END IF;
1759
1760 IF (is_event_complete
1761 (p_doc_type => PAYMENTS_ENTITY,
1762 p_source_id => p_check_id,
1763 p_calling_sequence => l_curr_calling_sequence) = 'CREATED') THEN
1764 l_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED;
1765 ELSE
1766 l_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE;
1767 END IF;
1768
1769 get_payment_info
1770 ( p_check_id => p_check_id,
1771 p_org_id => l_org_id, -- OUT
1772 p_legal_entity_id => l_legal_entity_id, -- OUT
1773 p_ledger_id => l_ledger_id, -- OUT
1774 p_calling_sequence => l_curr_calling_sequence
1775 );
1776
1777 l_event_security_context :=
1778 get_event_security_context
1779 ( p_org_id => l_org_id,
1780 p_calling_sequence => l_curr_calling_sequence
1781 );
1782
1783 l_event_source_info :=
1784 get_payment_event_source_info
1785 ( p_legal_entity_id => l_legal_entity_id,
1786 p_ledger_id => l_ledger_id,
1787 p_check_id => p_check_id,
1788 p_calling_sequence => l_curr_calling_sequence
1789 );
1790
1791 l_log_msg := 'Before calling procedure AP_XLA_EVENTS_PKG.CREATE_EVENT';
1792 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1793 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1794 G_MODULE_NAME || l_procedure_name,
1795 l_log_msg);
1796 END IF;
1797
1798 l_ret:=
1799 AP_XLA_EVENTS_PKG.CREATE_EVENT
1800 ( p_event_source_info => l_event_source_info,
1801 p_event_type_code => p_event_type,
1802 p_event_date => p_event_date,
1803 p_event_status_code => l_event_status,
1804 p_event_number => NULL,
1805 p_transaction_date => NULL,
1806 p_reference_info => NULL,
1807 p_valuation_method => NULL,
1808 p_security_context => l_event_security_context,
1809 p_calling_sequence => l_curr_calling_sequence
1810 );
1814 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1811
1812 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.CREATE_EVENT';
1813 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1815 G_MODULE_NAME || l_procedure_name,
1816 l_log_msg);
1817 END IF;
1818
1819 -- bug fix 5659451
1820 -- change hard coded constant to global constant variables
1821 IF p_event_type = PAYMENT_CANCELLED_TYPE THEN
1822
1823 select count(*)
1824 into l_matured_events_count
1825 from AP_PAYMENT_HISTORY APH
1826 where check_id = P_check_id
1827 and transaction_type = PAYMENT_MATURTY_TRX_TYPE;
1828
1829
1830 l_log_msg := 'l_matured_events_count = '
1831 ||to_char(l_matured_events_count);
1832 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1833 FND_LOG.STRING(G_LEVEL_STATEMENT,
1834 G_MODULE_NAME || l_procedure_name,
1835 l_log_msg);
1836 END IF;
1837
1838 select count(*)
1839 into l_unmatured_events_count
1840 from ap_payment_history APH
1841 where check_id = p_check_id
1842 and transaction_type = PAYMENT_MATURTY_RVRSL_TRX_TYPE;
1843
1844 l_log_msg := 'l_unmatured_events_count = '
1845 ||to_char(l_unmatured_events_count);
1846 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1847 FND_LOG.STRING(G_LEVEL_STATEMENT,
1848 G_MODULE_NAME || l_procedure_name,
1849 l_log_msg);
1850 END IF;
1851
1852 IF (l_matured_events_count > 0 and
1853 l_matured_events_count > l_unmatured_events_count) THEN
1854
1855
1856 select amount,
1857 currency_code,
1858 maturity_exchange_rate_type,
1859 maturity_exchange_date,
1860 maturity_exchange_Rate
1861 into l_amount,
1862 l_currency_code,
1863 l_maturity_exchange_rate_type,
1864 l_maturity_exchange_date,
1865 l_maturity_exchange_Rate
1866 from ap_Checks
1867 where check_id = P_check_id;
1868
1869 l_log_msg := 'Before ap_reconciliation_pkg.recon_payment_history';
1870 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1871 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1872 G_MODULE_NAME || l_procedure_name,
1873 l_log_msg);
1874 END IF;
1875
1876 ap_reconciliation_pkg.recon_payment_history
1877 (X_CHECKRUN_ID => NULL,
1878 X_CHECK_ID => P_check_id,
1879 X_TRANSACTION_TYPE => PAYMENT_MATURTY_RVRSL_TRX_TYPE,
1880 X_ACCOUNTING_DATE => p_event_date,
1881 X_CLEARED_DATE => NULL,
1882 X_TRANSACTION_AMOUNT => l_amount,
1883 X_ERROR_AMOUNT => NULL,
1884 X_CHARGE_AMOUNT => NULL,
1885 X_CURRENCY_CODE => l_currency_code,
1886 X_EXCHANGE_RATE_TYPE => l_maturity_exchange_rate_type,
1887 X_EXCHANGE_RATE_DATE => l_maturity_exchange_date,
1888 X_EXCHANGE_RATE => l_maturity_exchange_rate,
1889 X_MATCHED_FLAG => NULL,
1890 X_ACTUAL_VALUE_DATE => NULL,
1891 X_CREATION_DATE => sysdate,
1892 X_CREATED_BY => FND_GLOBAL.user_id,
1893 X_LAST_UPDATE_DATE => sysdate,
1894 X_LAST_UPDATED_BY => FND_GLOBAL.user_id,
1895 X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id,
1896 X_PROGRAM_UPDATE_DATE => NULL,
1897 X_PROGRAM_APPLICATION_ID => NULL,
1898 X_PROGRAM_ID => NULL,
1899 X_REQUEST_ID => NULL,
1900 X_CALLING_SEQUENCE => l_curr_calling_sequence);
1901
1902 l_log_msg := 'After ap_reconciliation_pkg.recon_payment_history';
1903 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1904 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1905 G_MODULE_NAME || l_procedure_name,
1906 l_log_msg);
1907 END IF;
1908 END IF;
1909
1910 l_log_msg := 'Open Cursor c_unacct_pmt_events';
1911 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1912 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1913 G_MODULE_NAME || l_procedure_name,
1914 l_log_msg);
1915 END IF;
1916
1917 -- 7374984
1918 -- Setting the event status to no action for all the unacctd
1919 -- events of this voided payment.
1920 OPEN c_unacct_pmt_events;
1921 LOOP
1922 FETCH c_unacct_pmt_events INTO
1923 l_accounting_date,
1924 l_accounting_event_id,
1925 l_transaction_type;
1926 EXIT WHEN c_unacct_pmt_events%NOTFOUND;
1927
1928 l_log_msg := 'Event id that needs to be set to no action ' ||
1929 l_accounting_event_id;
1930 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1931 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1932 G_MODULE_NAME || l_procedure_name,
1933 l_log_msg);
1934 END IF;
1935
1939 p_event_type_code => NULL,
1936 AP_XLA_EVENTS_PKG.UPDATE_EVENT
1937 ( p_event_source_info => l_event_source_info,
1938 p_event_id => l_accounting_event_id,
1940 p_event_date => NULL,
1941 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
1942 p_valuation_method => NULL,
1943 p_security_context => l_event_security_context,
1944 p_calling_sequence => l_curr_calling_sequence
1945 );
1946
1947 l_log_msg := 'End of call to AP_XLA_EVENTS_PKG.UPDATE_EVENT';
1948 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1949 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1950 G_MODULE_NAME || l_procedure_name,
1951 l_log_msg);
1952 END IF;
1953
1954 END LOOP;
1955 CLOSE c_unacct_pmt_events;
1956 -- End of bug 7374984
1957
1958 END IF;
1959
1960 l_log_msg := 'End of procedure'||l_procedure_name;
1961 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1962 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1963 G_MODULE_NAME || l_procedure_name,
1964 l_log_msg);
1965 END IF;
1966
1967 return l_ret;
1968
1969 EXCEPTION
1970 WHEN OTHERS THEN
1971 IF (SQLCODE <> -20001) THEN
1972 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1973 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1974 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1975 l_curr_calling_sequence);
1976 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1977 'p_event_type = '||p_event_type
1978 ||', p_check_id = '||p_check_id
1979 ||', p_event_date = '||p_event_date);
1980 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
1981 END IF;
1982 APP_EXCEPTION.RAISE_EXCEPTION();
1983 END create_payment_event;
1984
1985 /*============================================================================
1986 | PROCEDURE - UPDATE_INVOICE_EVENT_STATUS (PUBLIC)
1987 |
1988 | DESCRIPTION
1989 | Update invoice event's status
1990 |
1991 | PRAMETERS
1992 | p_invoice_id: Invoice ID
1993 | p_calling_sequence: Debug information
1994 |
1995 | KNOWN ISSUES:
1996 |
1997 | NOTES:
1998 |
1999 | MODIFICATION HISTORY
2000 | Date Author Description of Change
2001 |
2002 *===========================================================================*/
2003 PROCEDURE update_invoice_events_status(
2004 p_invoice_id IN NUMBER,
2005 p_calling_sequence IN VARCHAR2)
2006 IS
2007
2008 TYPE t_check_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
2009 TYPE t_accounting_event_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
2010
2011 l_legal_entity_id NUMBER(15);
2012 l_ledger_id NUMBER(15);
2013 l_org_id NUMBER(15);
2014 l_Event_count NUMBER;
2015 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
2016 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
2017 l_event_info XLA_EVENTS_PUB_PKG.T_EVENT_INFO;
2018 l_transaction_date AP_INVOICES_ALL.invoice_date%TYPE;
2019
2020 l_curr_calling_sequence VARCHAR2(2000);
2021
2022 -- Logging:
2023 l_procedure_name CONSTANT VARCHAR2(30) := 'update_invoice_events_status';
2024 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2025 --7011943
2026 l_count NUMBER;
2027 l_check_id NUMBER;
2028 l_pmt_awt_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
2029 l_event_source_info_temp XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
2030
2031 BEGIN
2032
2033 l_curr_calling_sequence := p_calling_sequence ||
2034 ' -> AP_ACCOUNTING_EVENTS_PKG.UPDATE_INVOICE_EVENTS_STATUS';
2035
2036 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2037
2038 l_log_msg := 'Begin of procedure '||l_procedure_name;
2039 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2040 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2041 G_MODULE_NAME || l_procedure_name,
2042 l_log_msg);
2043 END IF;
2044
2045 SELECT count(accounting_Event_id)
2046 INTO l_event_count
2047 FROM AP_INVOICE_DISTRIBUTIONS AID
2048 WHERE AID.accounting_Event_id is not null
2049 AND AID.invoice_id = P_Invoice_id;
2050
2051 IF l_Event_count <> 0 THEN
2052
2053 get_invoice_info
2054 ( p_invoice_id => p_invoice_id,
2055 p_org_id => l_org_id, -- OUT
2056 p_legal_entity_id => l_legal_entity_id, -- OUT
2057 p_ledger_id => l_ledger_id, -- OUT
2058 p_transaction_date => l_transaction_date, -- OUT
2059 p_calling_sequence => l_curr_calling_sequence
2060 );
2061
2062 l_event_security_context :=
2063 get_event_security_context
2064 ( p_org_id => l_org_id,
2065 p_calling_sequence => l_curr_calling_sequence
2066 );
2067
2068 DECLARE
2069
2070 CURSOR l_invoice_distributions_cur IS
2071 SELECT distinct(AID.accounting_event_id)
2072 FROM ap_invoice_distributions AID
2073 WHERE AID.invoice_id = p_invoice_id
2074 AND AID.accounting_event_id IS NOT NULL;
2075
2076 l_invoice_event_status VARCHAR2(1);
2077 l_do_updates_flag BOOLEAN;
2078 l_accounting_event_ids t_accounting_event_ids;
2082 l_event_source_info :=
2079
2080 BEGIN
2081
2083 get_invoice_event_source_info
2084 ( p_legal_entity_id => l_legal_entity_id,
2085 p_ledger_id => l_ledger_id,
2086 p_invoice_id => p_invoice_id,
2087 p_calling_sequence => l_curr_calling_sequence
2088 );
2089 l_event_source_info_temp := l_event_source_info ; --7011943
2090 IF
2091 ( is_event_complete
2092 ( p_doc_type => INVOICES_ENTITY,
2093 p_source_id => p_invoice_id,
2094 p_calling_sequence => l_curr_calling_sequence
2095 ) = 'CREATED') THEN
2096
2097 l_invoice_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED;
2098
2099 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.EVENT_EXISTS';
2100 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2101 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2102 G_MODULE_NAME || l_procedure_name,
2103 l_log_msg);
2104 END IF;
2105
2106 l_do_updates_flag :=
2107 AP_XLA_EVENTS_PKG.EVENT_EXISTS
2108 ( p_event_source_info => l_event_source_info,
2109 p_event_class_code => NULL,
2110 p_event_type_code => NULL,
2111 p_event_date => NULL,
2112 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE,
2113 p_event_number => NULL,
2114 p_valuation_method => NULL,
2115 p_security_context => l_event_security_context,
2116 p_calling_sequence => l_curr_calling_sequence
2117 );
2118
2119 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.EVENT_EXISTS';
2120 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2121 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2122 G_MODULE_NAME || l_procedure_name,
2123 l_log_msg);
2124 END IF;
2125
2126 ELSE
2127
2128 l_invoice_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE;
2129
2130 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.EVENT_EXISTS';
2131
2132 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2133 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2134 G_MODULE_NAME || l_procedure_name,
2135 l_log_msg);
2136 END IF;
2137
2138 l_do_updates_flag :=
2139 AP_XLA_EVENTS_PKG.EVENT_EXISTS
2140 ( p_event_source_info => l_event_source_info,
2141 p_event_class_code => NULL,
2142 p_event_type_code => NULL,
2143 p_event_date => NULL,
2144 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
2145 p_event_number => NULL,
2146 p_valuation_method => NULL,
2147 p_security_context => l_event_security_context,
2148 p_calling_sequence => l_curr_calling_sequence
2149 );
2150
2151 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.EVENT_EXISTS';
2152 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2153 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2154 G_MODULE_NAME || l_procedure_name,
2155 l_log_msg);
2156 END IF;
2157
2158 END IF;
2159
2160 IF (l_do_updates_flag) THEN
2161
2162 OPEN l_invoice_distributions_cur;
2163 LOOP
2164
2165 l_log_msg := 'Open cursor l_invoice_distributions_cur';
2166 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2167 FND_LOG.STRING(G_LEVEL_STATEMENT,
2168 G_MODULE_NAME || l_procedure_name,
2169 l_log_msg);
2170 END IF;
2171
2172 FETCH l_invoice_distributions_cur
2173 BULK COLLECT INTO
2174 l_accounting_event_ids
2175 LIMIT 1000;
2176
2177 FOR i IN 1 ..l_accounting_event_ids.count LOOP
2178
2179 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
2180 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2181 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2182 G_MODULE_NAME || l_procedure_name,
2183 l_log_msg);
2184 END IF;
2185
2186 --bug 7011943 There are payment time AWT dists.
2187 select count(*)
2188 into l_count
2189 from ap_invoice_distributions_all
2190 where accounting_event_id = l_accounting_event_ids(i)
2191 and invoice_id = l_event_source_info.source_id_int_1
2192 and awt_invoice_payment_id is not null;
2193
2194 if (l_count > 0) then
2195 BEGIN
2196 select ac.check_id
2197 into l_check_id
2198 from ap_invoice_payments_all aip,
2199 ap_checks_all ac
2200 where aip.check_id=ac.check_id
2201 and aip.accounting_event_id = l_accounting_event_ids(i)
2202 and aip.invoice_id=l_event_source_info.source_id_int_1;
2203
2204
2205 l_pmt_awt_event_source_info:=get_payment_event_source_info(p_legal_entity_id => l_legal_entity_id,
2206 p_ledger_id => l_ledger_id,
2207 p_check_id => l_check_id,
2208 p_calling_sequence => l_curr_calling_sequence);
2209 l_event_source_info := l_pmt_awt_event_source_info;
2210
2211 EXCEPTION
2212 WHEN OTHERS THEN
2216 l_event_source_info := l_event_source_info_temp;
2213 NULL;
2214 END;
2215 ELSE
2217 end if;
2218 --bug 7011943 ends
2219 l_event_info :=
2220 AP_XLA_EVENTS_PKG.GET_EVENT_INFO
2221 ( p_event_source_info => l_event_source_info,
2222 p_event_id => l_accounting_event_ids(i),
2223 p_valuation_method => NULL,
2224 p_security_context => l_event_security_context,
2225 p_calling_sequence => l_curr_calling_sequence
2226 );
2227
2228 --bug 7011943
2229 l_event_source_info := l_event_source_info_temp;
2230 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
2231 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2232 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2233 G_MODULE_NAME || l_procedure_name,
2234 l_log_msg);
2235 END IF;
2236
2237 IF ( l_event_info.event_status_code IN
2238 ( XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
2239 XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE
2240 ) AND
2241 l_event_info.event_status_code <> l_invoice_event_status) THEN
2242
2243 l_log_msg := 'Before AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
2244 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2245 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2246 G_MODULE_NAME || l_procedure_name,
2247 l_log_msg);
2248 END IF;
2249
2250 AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
2251 ( p_event_source_info => l_event_source_info,
2252 p_event_class_code => null,
2253 p_event_type_code => null,
2254 p_event_date => null,
2255 p_event_status_code => l_invoice_event_status,
2256 p_valuation_method => NULL,
2257 p_security_context => l_event_security_context,
2258 p_calling_sequence => l_curr_calling_sequence
2259 );
2260
2261 l_log_msg := 'After AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
2262 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2263 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2264 G_MODULE_NAME || l_procedure_name,
2265 l_log_msg);
2266 END IF;
2267 END IF;
2268
2269 END LOOP;
2270
2271 EXIT WHEN l_invoice_distributions_cur%NOTFOUND;
2272
2273 END LOOP;
2274 CLOSE l_invoice_distributions_cur;
2275
2276 END IF; -- l_do_updates_flag
2277
2278 EXCEPTION
2279 WHEN OTHERS THEN
2280 IF (l_invoice_distributions_cur%ISOPEN) THEN
2281 CLOSE l_invoice_distributions_cur;
2282 END IF;
2283
2284 l_log_msg := 'Exception calling sequence '
2285 ||l_curr_calling_sequence
2286 ||' Error: '
2287 ||SQLERRM;
2288 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2289
2290 FND_LOG.STRING(G_LEVEL_EXCEPTION,
2291 G_MODULE_NAME || l_procedure_name,
2292 l_log_msg);
2293 END IF;
2294
2295 RAISE;
2296 END;
2297
2298 DECLARE
2299
2300 -- Bug 7042296 Modified the cursor to pick the
2301 -- Payment Adjustment Events.
2302 CURSOR l_invoice_payments_cur IS --bug 7042296
2303 SELECT distinct(AIP.check_id),
2304 APH.accounting_event_id
2305 FROM ap_invoice_payments AIP, ap_payment_history_all APH
2306 WHERE AIP.invoice_id = p_invoice_id
2307 AND AIP.accounting_event_id IS NOT NULL
2308 and AIP.check_id = APH.check_id
2309 and APH.accounting_event_id is not null
2310 ORDER BY AIP.check_id;
2311
2312
2313 l_check_ids t_check_ids;
2314 l_accounting_event_ids t_accounting_event_ids;
2315 l_last_check_id NUMBER(15) := NULL;
2316 l_curr_check_id NUMBER(15);
2317 l_payment_event_status VARCHAR2(1);
2318 l_do_updates_flag BOOLEAN;
2319
2320 BEGIN
2321
2322 OPEN l_invoice_payments_cur;
2323 LOOP
2324
2325 l_log_msg := 'Cursor l_invoice_payment_cur ';
2326 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2327 FND_LOG.STRING(G_LEVEL_STATEMENT,
2328 G_MODULE_NAME || l_procedure_name,
2329 l_log_msg);
2330 END IF;
2331
2332 FETCH l_invoice_payments_cur
2333 BULK COLLECT INTO
2334 l_check_ids,
2335 l_accounting_event_ids
2336 LIMIT 1000;
2337
2338 FOR i IN 1 .. l_accounting_event_ids.count LOOP
2339
2340 l_curr_check_id := l_check_ids(i);
2341
2342 IF ( l_last_check_id IS NULL OR
2343 l_curr_check_id <> l_last_check_id) THEN
2344
2345 l_last_check_id := l_curr_check_id;
2346
2347 l_log_msg := 'Before calling proc get_payment_event_source_info';
2348 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2349 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2350 G_MODULE_NAME || l_procedure_name,
2354 l_event_source_info :=
2351 l_log_msg);
2352 END IF;
2353
2355 get_payment_event_source_info
2356 ( p_legal_entity_id => l_legal_entity_id,
2357 p_ledger_id => l_ledger_id,
2358 p_check_id => l_curr_check_id,
2359 p_calling_sequence => l_curr_calling_sequence
2360 );
2361
2362 l_log_msg := 'After calling get_payment_event_source_info';
2363 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2364 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2365 G_MODULE_NAME || l_procedure_name,
2366 l_log_msg);
2367 END IF;
2368
2369 IF ( is_event_complete
2370 ( p_doc_type => PAYMENTS_ENTITY,
2371 p_source_id => l_curr_check_id,
2372 p_calling_sequence => l_curr_calling_sequence) = 'CREATED')
2373 THEN
2374
2375 l_payment_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED;
2376
2377 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.EVENT_EIXSTS';
2378 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2379 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2380 G_MODULE_NAME || l_procedure_name,
2381 l_log_msg);
2382 END IF;
2383
2384 l_do_updates_flag :=
2385 AP_XLA_EVENTS_PKG.EVENT_EXISTS
2386 ( p_event_source_info => l_event_source_info,
2387 p_event_class_code => NULL,
2388 p_event_type_code => NULL,
2389 p_event_date => NULL,
2390 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE,
2391 p_event_number => NULL,
2392 p_valuation_method => NULL,
2393 p_security_context => l_event_security_context,
2394 p_calling_sequence => l_curr_calling_sequence
2395 );
2396
2397 l_log_msg := 'After AP_ACCOUNTING_EVENT_PKG.'
2398 ||'get_payment_event_source_info';
2399 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2400 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2401 G_MODULE_NAME || l_procedure_name,
2402 l_log_msg);
2403 END IF;
2404
2405 ELSE
2406
2407 l_payment_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE;
2408
2409 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.EVENT_EXISTS';
2410 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2411 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2412 G_MODULE_NAME || l_procedure_name,
2413 l_log_msg);
2414 END IF;
2415
2416 l_do_updates_flag :=
2417 AP_XLA_EVENTS_PKG.EVENT_EXISTS
2418 ( p_event_source_info => l_event_source_info,
2419 p_event_class_code => NULL,
2420 p_event_type_code => NULL,
2421 p_event_date => NULL,
2422 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
2423 p_event_number => NULL,
2424 p_valuation_method => NULL,
2425 p_security_context => l_event_security_context,
2426 p_calling_sequence => l_curr_calling_sequence
2427 );
2428
2429 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.EVENT_EXISTS';
2430 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2431 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2432 G_MODULE_NAME || l_procedure_name,
2433 l_log_msg);
2434 END IF;
2435 END IF;
2436
2437 END IF;
2438
2439 IF (l_do_updates_flag) THEN
2440
2441 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
2442 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2443 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2444 G_MODULE_NAME || l_procedure_name,
2445 l_log_msg);
2446 END IF;
2447
2448 l_event_info :=
2449 AP_XLA_EVENTS_PKG.GET_EVENT_INFO
2450 ( p_event_source_info => l_event_source_info,
2451 p_event_id => l_accounting_event_ids(i),
2452 p_valuation_method => NULL,
2453 p_security_context => l_event_security_context,
2454 p_calling_sequence => l_curr_calling_sequence
2455 );
2456
2457 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
2458 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2459 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2460 G_MODULE_NAME || l_procedure_name,
2461 l_log_msg);
2462 END IF;
2463
2464 IF (l_event_info.event_status_code IN
2465 ( XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
2466 XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE) AND
2467 l_event_info.event_status_code <> l_payment_event_status) THEN
2468
2469 l_log_msg := 'Before AP_XLA_EVENTS_PKG.UPDATE_EVENT_INFO';
2470 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2474 END IF;
2471 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2472 G_MODULE_NAME || l_procedure_name,
2473 l_log_msg);
2475
2476 AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
2477 ( p_event_source_info => l_event_source_info,
2478 p_event_class_code => null,
2479 p_event_type_code => null,
2480 p_event_date => null,
2481 p_event_status_code => l_payment_event_status,
2482 p_valuation_method => NULL,
2483 p_security_context => l_event_security_context,
2484 p_calling_sequence => l_curr_calling_sequence
2485 );
2486
2487 l_log_msg := 'After AP_XLA_EVENTS_PKG.UPDATE_EVENT_INFO';
2488 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2489 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2490 G_MODULE_NAME || l_procedure_name,
2491 l_log_msg);
2492 END IF;
2493 END IF;
2494 END IF; -- l_do_updates_flag
2495 END LOOP;
2496
2497 EXIT WHEN l_invoice_payments_cur%NOTFOUND;
2498 END LOOP;
2499 CLOSE l_invoice_payments_cur;
2500
2501 EXCEPTION
2502 WHEN OTHERS THEN
2503 IF (l_invoice_payments_cur%ISOPEN) THEN
2504 CLOSE l_invoice_payments_cur;
2505 END IF;
2506
2507 l_log_msg := 'Exception calling sequence '
2508 ||l_curr_calling_sequence
2509 ||' Error: '
2510 ||SQLERRM;
2511 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2512 FND_LOG.STRING(G_LEVEL_EXCEPTION,
2513 G_MODULE_NAME || l_procedure_name,
2514 l_log_msg);
2515 END IF;
2516 RAISE;
2517 END;
2518
2519 l_log_msg := 'End of procedure '||l_procedure_name;
2520 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2521 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2522 G_MODULE_NAME || l_procedure_name,
2523 l_log_msg);
2524 END IF;
2525
2526 END IF; --l_event_count<>0
2527
2528 EXCEPTION
2529 WHEN OTHERS THEN
2530 IF (SQLCODE <> -20001) THEN
2531 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2532 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2533 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
2534 l_curr_calling_sequence);
2535 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2536 'p_invoice_id = '||p_invoice_id);
2537 END IF;
2538 APP_EXCEPTION.RAISE_EXCEPTION();
2539 END update_invoice_events_status;
2540
2541
2542 /*============================================================================
2543 | PROCEDURE - UPDATE_PAYMENT_EVENTS_STATUS (PUBLIC)
2544 |
2545 | DESCRIPTION
2546 | Update payment event's status
2547 |
2548 | PRAMETERS
2549 | p_check_id: Check ID
2550 | p_calling_sequence: Debug information
2551 |
2552 | KNOWN ISSUES:
2553 |
2554 | NOTES:
2555 |
2556 | MODIFICATION HISTORY
2557 | Date Author Description of Change
2558 |
2559 *===========================================================================*/
2560 PROCEDURE update_payment_events_status (
2561 p_check_id IN NUMBER,
2562 p_calling_sequence IN VARCHAR2)
2563 IS
2564
2565 TYPE t_invoice_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
2566 TYPE t_accounting_event_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
2567
2568 l_legal_entity_id NUMBER(15);
2569 l_ledger_id NUMBER(15);
2570 l_org_id NUMBER(15);
2571 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
2572 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
2573 l_event_info XLA_EVENTS_PUB_PKG.T_EVENT_INFO;
2574 l_transaction_date AP_INVOICES_ALL.invoice_date%TYPE;
2575
2576 l_curr_calling_sequence VARCHAR2(2000);
2577
2578 -- Logging:
2579 l_procedure_name CONSTANT VARCHAR2(30) := 'UPDATE_PAYMENT_EVENTS_STATUS';
2580 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2581 --7011943
2582 l_count NUMBER;
2583 l_check_id NUMBER;
2584 l_pmt_awt_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
2585 l_event_source_info_temp XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
2586
2587
2588 BEGIN
2589
2590 l_curr_calling_sequence := p_calling_sequence ||
2591 ' -> AP_ACCOUNTING_EVENTS_PKG.UPDATE_PAYMENT_EVENTS_STATUS';
2592
2593 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2594
2595 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2596 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2597 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2598 G_MODULE_NAME || l_procedure_name,
2599 l_log_msg);
2600 END IF;
2601
2602 DECLARE
2603
2604 CURSOR l_invoice_distributions_cur IS
2605 SELECT distinct(AID.invoice_id),
2606 AID.accounting_event_id
2607 FROM ap_invoice_payments AIP,
2608 ap_invoice_distributions AID
2609 WHERE AIP.invoice_id = AID.invoice_id
2610 AND AIP.check_id = p_check_id
2614 l_invoice_ids t_invoice_ids;
2611 AND AID.accounting_event_id IS NOT NULL
2612 ORDER BY AID.invoice_id;
2613
2615 l_accounting_event_ids t_accounting_event_ids;
2616 l_last_invoice_id NUMBER(15) := NULL;
2617 l_curr_invoice_id NUMBER(15);
2618 l_invoice_event_status VARCHAR2(1);
2619 l_do_updates_flag BOOLEAN;
2620
2621 BEGIN
2622
2623 l_log_msg := 'Open cursor l_invoice_distributions_cur';
2624 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2625 FND_LOG.STRING(G_LEVEL_STATEMENT,
2626 G_MODULE_NAME || l_procedure_name,
2627 l_log_msg);
2628 END IF;
2629
2630 OPEN l_invoice_distributions_cur;
2631 LOOP
2632
2633 FETCH l_invoice_distributions_cur
2634 BULK COLLECT INTO
2635 l_invoice_ids,
2636 l_accounting_event_ids
2637 LIMIT 1000;
2638
2639
2640 FOR i IN 1 .. l_accounting_event_ids.count LOOP
2641
2642 l_curr_invoice_id := l_invoice_ids(i);
2643
2644 IF ( l_last_invoice_id IS NULL OR
2645 l_curr_invoice_id <> l_last_invoice_id) THEN
2646
2647 l_last_invoice_id := l_curr_invoice_id;
2648
2649 l_log_msg := 'Before calling procedure get_invoice_info';
2650 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2651 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2652 G_MODULE_NAME || l_procedure_name,
2653 l_log_msg);
2654 END IF;
2655
2656 get_invoice_info
2657 ( p_invoice_id => l_curr_invoice_id,
2658 p_org_id => l_org_id, -- OUT
2659 p_legal_entity_id => l_legal_entity_id, -- OUT
2660 p_ledger_id => l_ledger_id, -- OUT
2661 p_transaction_date => l_transaction_date, -- OUT
2662 p_calling_sequence => l_curr_calling_sequence
2663 );
2664
2665 l_log_msg := 'After calling procedure get_invoice_info';
2666 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2667 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2668 G_MODULE_NAME || l_procedure_name,
2669 l_log_msg);
2670 END IF;
2671
2672 l_log_msg := 'Before calling procedure get_event_security_context';
2673 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2674 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2675 G_MODULE_NAME || l_procedure_name,
2676 l_log_msg);
2677 END IF;
2678
2679 l_event_security_context :=
2680 get_event_security_context
2681 ( p_org_id => l_org_id,
2682 p_calling_sequence => l_curr_calling_sequence
2683 );
2684
2685 l_log_msg := 'After calling procedure get_event_security_context';
2686 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2687 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2688 G_MODULE_NAME || l_procedure_name,
2689 l_log_msg);
2690 END IF;
2691
2692 l_log_msg := 'Before calling get_invoice_event_source_info';
2693 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2694 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2695 G_MODULE_NAME || l_procedure_name,
2696 l_log_msg);
2697 END IF;
2698
2699 l_event_source_info :=
2700 get_invoice_event_source_info
2701 ( p_legal_entity_id => l_legal_entity_id,
2702 p_ledger_id => l_ledger_id,
2703 p_invoice_id => l_curr_invoice_id,
2704 p_calling_sequence => l_curr_calling_sequence
2705 );
2706 l_event_source_info_temp := l_event_source_info; --7011943
2707 l_log_msg := 'After calling procedure get_invoice_info';
2708 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2709 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2710 G_MODULE_NAME || l_procedure_name,
2711 l_log_msg);
2712 END IF;
2713
2714 IF (is_event_complete
2715 (p_doc_type => INVOICES_ENTITY,
2716 p_source_id => l_curr_invoice_id,
2717 p_calling_sequence => l_curr_calling_sequence) = 'CREATED') THEN
2718
2719 l_invoice_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED;
2720
2721 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.EVENT_EXISTS';
2722 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2723 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2724 G_MODULE_NAME || l_procedure_name,
2725 l_log_msg);
2726 END IF;
2727
2728 l_do_updates_flag :=
2729 AP_XLA_EVENTS_PKG.EVENT_EXISTS
2730 ( p_event_source_info => l_event_source_info,
2731 p_event_class_code => NULL,
2732 p_event_type_code => NULL,
2733 p_event_date => NULL,
2734 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE,
2735 p_event_number => NULL,
2736 p_valuation_method => NULL,
2740
2737 p_security_context => l_event_security_context,
2738 p_calling_sequence => l_curr_calling_sequence
2739 );
2741 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.EVENT_EXISTS';
2742 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2743 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2744 G_MODULE_NAME || l_procedure_name,
2745 l_log_msg);
2746 END IF;
2747 ELSE
2748
2749 l_invoice_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE;
2750
2751 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.EVENT_EXISTS';
2752 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2753 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2754 G_MODULE_NAME || l_procedure_name,
2755 l_log_msg);
2756 END IF;
2757
2758 l_do_updates_flag :=
2759 AP_XLA_EVENTS_PKG.EVENT_EXISTS
2760 ( p_event_source_info => l_event_source_info,
2761 p_event_class_code => NULL,
2762 p_event_type_code => NULL,
2763 p_event_date => NULL,
2764 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
2765 p_event_number => NULL,
2766 p_valuation_method => NULL,
2767 p_security_context => l_event_security_context,
2768 p_calling_sequence => l_curr_calling_sequence);
2769
2770 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.EVENT_EXISTS';
2771 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2772 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2773 G_MODULE_NAME || l_procedure_name,
2774 l_log_msg);
2775 END IF;
2776
2777 END IF;
2778
2779 END IF;
2780
2781 IF (l_do_updates_flag) THEN
2782
2783 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
2784 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2785 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2786 G_MODULE_NAME || l_procedure_name,
2787 l_log_msg);
2788 END IF;
2789 --bug 7011943
2790 select count(*)
2791 into l_count
2792 from ap_invoice_distributions_all
2793 where accounting_event_id = l_accounting_event_ids(i)
2794 and invoice_id = l_event_source_info.source_id_int_1
2795 and awt_invoice_payment_id is not null;
2796
2797 if (l_count > 0) then
2798 BEGIN
2799 select ac.check_id
2800 into l_check_id
2801 from ap_invoice_payments_all aip,
2802 ap_checks_all ac
2803 where aip.check_id=ac.check_id
2804 and aip.accounting_event_id = l_accounting_event_ids(i)
2805 and aip.invoice_id=l_event_source_info.source_id_int_1;
2806
2807
2808 l_pmt_awt_event_source_info:=get_payment_event_source_info(p_legal_entity_id => l_legal_entity_id,
2809 p_ledger_id => l_ledger_id,
2810 p_check_id => l_check_id,
2811 p_calling_sequence => l_curr_calling_sequence);
2812 l_event_source_info := l_pmt_awt_event_source_info;
2813
2814 EXCEPTION
2815 WHEN OTHERS THEN
2816 NULL;
2817 END;
2818 ELSE
2819 l_event_source_info := l_event_source_info_temp;
2820 end if;
2821 --bug 7011943 ends
2822
2823 l_event_info :=
2824 AP_XLA_EVENTS_PKG.GET_EVENT_INFO
2825 ( p_event_source_info => l_event_source_info,
2826 p_event_id => l_accounting_event_ids(i),
2827 p_valuation_method => NULL,
2828 p_security_context => l_event_security_context,
2829 p_calling_sequence => l_curr_calling_sequence);
2830 --bug 7011943
2831 l_event_source_info := l_event_source_info_temp;
2832
2833 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
2834 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2835 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2836 G_MODULE_NAME || l_procedure_name,
2837 l_log_msg);
2838 END IF;
2839
2840 IF ( l_event_info.event_status_code IN
2841 ( XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
2842 XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE) AND
2843 l_event_info.event_status_code <> l_invoice_event_status) THEN
2844
2845 l_log_msg := 'Before AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
2846 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2847 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2848 G_MODULE_NAME || l_procedure_name,
2849 l_log_msg);
2850 END IF;
2851
2852 AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
2853 ( p_event_source_info => l_event_source_info,
2854 p_event_class_code => null,
2855 p_event_type_code => null,
2859 p_security_context => l_event_security_context,
2856 p_event_date => null,
2857 p_event_status_code => l_invoice_event_status,
2858 p_valuation_method => NULL,
2860 p_calling_sequence => l_curr_calling_sequence);
2861
2862 l_log_msg := 'After AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
2863 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2864 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2865 G_MODULE_NAME || l_procedure_name,
2866 l_log_msg);
2867 END IF;
2868 END IF;
2869
2870
2871 END IF; -- l_do_updates_flag
2872
2873 END LOOP;
2874
2875 EXIT WHEN l_invoice_distributions_cur%NOTFOUND;
2876 END LOOP;
2877 CLOSE l_invoice_distributions_cur;
2878
2879 EXCEPTION
2880 WHEN OTHERS THEN
2881 IF (l_invoice_distributions_cur%ISOPEN) THEN
2882 CLOSE l_invoice_distributions_cur;
2883 END IF;
2884
2885 l_log_msg := 'Exception calling sequence '
2886 ||l_curr_calling_sequence
2887 ||' Error: '
2888 ||SQLERRM;
2889 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2890 FND_LOG.STRING(G_LEVEL_EXCEPTION,
2891 G_MODULE_NAME || l_procedure_name,
2892 l_log_msg);
2893 END IF;
2894 RAISE;
2895 END;
2896
2897 DECLARE
2898
2899 CURSOR l_invoice_payments_cur IS
2900 SELECT distinct(AIP.accounting_event_id)
2901 FROM ap_invoice_payments AIP
2902 WHERE AIP.check_id = p_check_id
2903 AND AIP.accounting_event_id IS NOT NULL;
2904
2905 l_payment_event_status VARCHAR2(1);
2906 l_do_updates_flag BOOLEAN;
2907 l_accounting_event_ids t_accounting_event_ids;
2908
2909 BEGIN
2910
2911 l_log_msg := 'Before calling procedure get_payment_info';
2912 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2913 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2914 G_MODULE_NAME || l_procedure_name,
2915 l_log_msg);
2916 END IF;
2917
2918 get_payment_info
2919 ( p_check_id => p_check_id,
2920 p_org_id => l_org_id, -- OUT
2921 p_legal_entity_id => l_legal_entity_id, -- OUT
2922 p_ledger_id => l_ledger_id, -- OUT
2923 p_calling_sequence => l_curr_calling_sequence
2924 );
2925
2926 l_log_msg := 'After calling procedure get_payment_info';
2927 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2928 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2929 G_MODULE_NAME || l_procedure_name,
2930 l_log_msg);
2931 END IF;
2932
2933 l_log_msg := 'Before calling procedure get_event_security_context';
2934 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2935 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2936 G_MODULE_NAME || l_procedure_name,
2937 l_log_msg);
2938 END IF;
2939
2940 l_event_security_context :=
2941 get_event_security_context
2942 ( p_org_id => l_org_id,
2943 p_calling_sequence => l_curr_calling_sequence
2944 );
2945
2946 l_log_msg := 'After calling procedure get_event_security_context';
2947 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2948 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2949 G_MODULE_NAME || l_procedure_name,
2950 l_log_msg);
2951 END IF;
2952
2953 l_log_msg := 'Before calling procedure get_payment_event_source_info';
2954 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2955 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2956 G_MODULE_NAME || l_procedure_name,
2957 l_log_msg);
2958 END IF;
2959
2960 l_event_source_info :=
2961 get_payment_event_source_info
2962 ( p_legal_entity_id => l_legal_entity_id,
2963 p_ledger_id => l_ledger_id,
2964 p_check_id => p_check_id,
2965 p_calling_sequence => l_curr_calling_sequence
2966 );
2967
2968 l_log_msg := 'After calling procedure get_payment_event_source_info';
2969 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2970 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2971 G_MODULE_NAME || l_procedure_name,
2972 l_log_msg);
2973 END IF;
2974
2975 IF (is_event_complete
2976 (p_doc_type => PAYMENTS_ENTITY,
2977 p_source_id => p_check_id,
2978 p_calling_sequence => l_curr_calling_sequence) = 'CREATED') THEN
2979
2980 l_payment_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED;
2981
2982 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.EVENT_EXISTS';
2983 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2984 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2985 G_MODULE_NAME || l_procedure_name,
2986 l_log_msg);
2987 END IF;
2988
2989 l_do_updates_flag :=
2990 AP_XLA_EVENTS_PKG.EVENT_EXISTS
2991 ( p_event_source_info => l_event_source_info,
2992 p_event_class_code => NULL,
2996 p_event_number => NULL,
2993 p_event_type_code => NULL,
2994 p_event_date => NULL,
2995 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE,
2997 p_valuation_method => NULL,
2998 p_security_context => l_event_security_context,
2999 p_calling_sequence => l_curr_calling_sequence
3000 );
3001
3002 l_log_msg := 'After calling procedure AP_XLA_EVENTS_PKG.EVENT_EXISTS';
3003 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3004 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3005 G_MODULE_NAME || l_procedure_name,
3006 l_log_msg);
3007 END IF;
3008
3009 ELSE
3010
3011 l_payment_event_status := XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE;
3012
3013 l_log_msg := 'Before calling procedure AP_XLA_EVENTS_PKG.EVENT_EXISTS';
3014 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3015 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3016 G_MODULE_NAME || l_procedure_name,
3017 l_log_msg);
3018 END IF;
3019
3020 l_do_updates_flag :=
3021 AP_XLA_EVENTS_PKG.EVENT_EXISTS
3022 ( p_event_source_info => l_event_source_info,
3023 p_event_class_code => NULL,
3024 p_event_type_code => NULL,
3025 p_event_date => NULL,
3026 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
3027 p_event_number => NULL,
3028 p_valuation_method => NULL,
3029 p_security_context => l_event_security_context,
3030 p_calling_sequence => l_curr_calling_sequence
3031 );
3032
3033 l_log_msg := 'After calling procedure AP_XLA_EVENTS_PKG.EVENT_EXISTS';
3034 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3035 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3036 G_MODULE_NAME || l_procedure_name,
3037 l_log_msg);
3038 END IF;
3039
3040 END IF;
3041
3042 IF (l_do_updates_flag) THEN
3043
3044 OPEN l_invoice_payments_cur;
3045 LOOP
3046
3047 l_log_msg := 'Fetch data from cursor l_invoice_payments_cur';
3048 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3049 FND_LOG.STRING(G_LEVEL_STATEMENT,
3050 G_MODULE_NAME || l_procedure_name,
3051 l_log_msg);
3052 END IF;
3053
3054 FETCH l_invoice_payments_cur
3055 BULK COLLECT INTO
3056 l_accounting_event_ids
3057 LIMIT 1000;
3058
3059 l_log_msg := 'Begin loop for l_accounting_event_ids';
3060 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3061 FND_LOG.STRING(G_LEVEL_STATEMENT,
3062 G_MODULE_NAME || l_procedure_name,
3063 l_log_msg);
3064 END IF;
3065
3066 FOR i IN 1 ..l_accounting_event_ids.count LOOP
3067
3068 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
3069 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3070 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3071 G_MODULE_NAME || l_procedure_name,
3072 l_log_msg);
3073 END IF;
3074
3075 l_event_info :=
3076 AP_XLA_EVENTS_PKG.GET_EVENT_INFO
3077 ( p_event_source_info => l_event_source_info,
3078 p_event_id => l_accounting_event_ids(i),
3079 p_valuation_method => NULL,
3083
3080 p_security_context => l_event_security_context,
3081 p_calling_sequence => l_curr_calling_sequence
3082 );
3084 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
3085 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3086 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3087 G_MODULE_NAME || l_procedure_name,
3088 l_log_msg);
3089 END IF;
3090
3091 IF ( l_event_info.event_status_code IN
3092 ( XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
3093 XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE) AND
3094 l_event_info.event_status_code <> l_payment_event_status) THEN
3095
3096 l_log_msg := 'Before AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
3097 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3098 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3099 G_MODULE_NAME || l_procedure_name,
3100 l_log_msg);
3101 END IF;
3102
3103 AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
3104 ( p_event_source_info => l_event_source_info,
3105 p_event_class_code =>
3106 get_event_class
3107 (
3108 p_event_type => l_event_info.event_type_code,
3109 p_calling_sequence => l_curr_calling_sequence
3110 ),
3111 p_event_type_code => l_event_info.event_type_code,
3112 p_event_date => l_event_info.event_date,
3113 p_event_status_code => l_payment_event_status,
3114 p_valuation_method => NULL,
3115 p_security_context => l_event_security_context,
3116 p_calling_sequence => l_curr_calling_sequence
3117 );
3118
3119 l_log_msg := 'After AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
3120 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3121 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3122 G_MODULE_NAME || l_procedure_name,
3123 l_log_msg);
3124 END IF;
3125
3126 END IF;
3127
3128 END LOOP;
3129 EXIT WHEN l_invoice_payments_cur%NOTFOUND;
3130 END LOOP;
3131
3132 CLOSE l_invoice_payments_cur;
3133
3134 l_log_msg := 'End loop for l_accounting_event_ids';
3135 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3136 FND_LOG.STRING(G_LEVEL_STATEMENT,
3137 G_MODULE_NAME || l_procedure_name,
3138 l_log_msg);
3139 END IF;
3140 END IF; -- l_do_updates_flag
3141
3142 EXCEPTION
3143 WHEN OTHERS THEN
3144 IF (l_invoice_payments_cur%ISOPEN) THEN
3145 CLOSE l_invoice_payments_cur;
3146 END IF;
3147 l_log_msg := 'Exception calling sequence '
3148 ||l_curr_calling_sequence
3149 ||' Error: '
3150 ||SQLERRM;
3151 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
3152 FND_LOG.STRING(G_LEVEL_EXCEPTION,
3153 G_MODULE_NAME || l_procedure_name,
3154 l_log_msg);
3155 END IF;
3156 RAISE;
3157 END;
3158
3159 l_log_msg := 'End of procedure '||l_procedure_name;
3160 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3161 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3162 G_MODULE_NAME || l_procedure_name,
3163 l_log_msg);
3164 END IF;
3165
3166 EXCEPTION
3167 WHEN OTHERS THEN
3168 IF (SQLCODE <> -20001) THEN
3169 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3170 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3171 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
3172 l_curr_calling_sequence);
3173 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3174 'p_check_id = '||p_check_id);
3175 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
3176 END IF;
3177 APP_EXCEPTION.RAISE_EXCEPTION();
3178 END update_payment_events_status;
3179
3180 /*============================================================================
3181 | PROCEDURE - update_pmt_batch_event_status
3182 |
3183 | DESCRIPTION
3184 | Update accounting events for 'PAYMENT BATCH' type
3185 |
3186 | PRAMETERS
3190 | p_set_of_books_id: the ledger id for this ou
3187 | p_completed_pmts_group_id: payment request name
3188 | p_accounting_date:Event Date
3189 | p_org_id : org id for each small batch inside the payment request
3191 | p_calling_sequence: Debug information
3192 |
3193 | KNOWN ISSUES:
3194 |
3195 | NOTES:
3196 |
3197 | MODIFICATION HISTORY
3198 | Date Author Description of Change
3199 |
3200 *===========================================================================*/
3201 PROCEDURE update_pmt_batch_event_status(
3202 p_checkrun_name IN VARCHAR2,
3203 p_completed_pmts_group_id IN NUMBER,
3204 p_org_id IN NUMBER,
3205 p_calling_sequence IN VARCHAR2)
3206 IS
3207
3208 l_record_count NUMBER;
3209 l_curr_calling_sequence VARCHAR2(2000);
3210
3211 -- Logging:
3212 --4700081
3213 l_procedure_name CONSTANT VARCHAR2(40) := 'update_payment_batch_event_status';
3214 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3215
3216 BEGIN
3217
3218 l_curr_calling_sequence := p_calling_sequence ||
3219 ' -> AP_ACCOUNTING_EVENTS_PKG.update_payment_batch_event_status';
3220
3221 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3222
3223 l_log_msg := 'Begin of procedure'||l_procedure_name;
3224 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3225 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3226 G_MODULE_NAME || l_procedure_name,
3227 l_log_msg);
3228 END IF;
3229
3230 -- Bug 5512197. Changed the reference of XL_EVENTS_GT to XLA_EVENTS_INT_GT
3231 SELECT COUNT(*)
3232 INTO l_record_count
3233 FROM XLA_EVENTS_INT_GT XEG
3234 WHERE XEG.application_id = 200
3235 AND XEG.entity_code = PAYMENTS_ENTITY
3236 AND XEG.event_type_code = PAYMENT_CREATED_TYPE;
3237
3238 IF ( l_record_count <> 0 ) THEN
3239
3240 l_log_msg := 'update the gt table set event status = no action';
3241 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3242 FND_LOG.STRING(G_LEVEL_STATEMENT,
3243 G_MODULE_NAME || l_procedure_name,
3244 l_log_msg);
3245 END IF;
3246
3247 UPDATE XLA_EVENTS_INT_GT XEG
3248 SET event_status_code = XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION
3249 WHERE XEG.application_id = 200
3250 AND XEG.source_id_int_1 IN
3251 ( SELECT AC.check_id
3252 FROM AP_CHECKS_ALL AC
3253 WHERE AC.checkrun_name = p_checkrun_name
3254 and AC.completed_pmts_group_id = p_completed_pmts_group_id
3255 and AC.org_id = p_org_id );
3256
3257 l_log_msg := 'update the gt table in success and call the api';
3258 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3259 FND_LOG.STRING(G_LEVEL_STATEMENT,
3260 G_MODULE_NAME || l_procedure_name,
3261 l_log_msg);
3262 END IF;
3263
3264 XLA_EVENTS_PUB_PKG.update_bulk_event_statuses(p_application_id => '200');
3265
3266 l_log_msg := 'after calling xla update status api';
3267 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3268 FND_LOG.STRING(G_LEVEL_STATEMENT,
3269 G_MODULE_NAME || l_procedure_name,
3270 l_log_msg);
3271 END IF;
3272
3273 ELSE
3274
3275 l_log_msg := 'XLA_EVENTS_INT_GT TABLE Has no records';
3276 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3277 FND_LOG.STRING(G_LEVEL_STATEMENT,
3278 G_MODULE_NAME || l_procedure_name,
3279 l_log_msg);
3280 END IF;
3281 APP_EXCEPTION.RAISE_EXCEPTION();
3282 END IF;
3283
3284 l_log_msg := 'End of the procedure';
3285 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3286 FND_LOG.STRING(G_LEVEL_STATEMENT,
3287 G_MODULE_NAME || l_procedure_name,
3288 l_log_msg);
3289 END IF;
3290
3291 EXCEPTION
3292 WHEN OTHERS THEN
3293
3294 IF (SQLCODE <> -20001) THEN
3295 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3296 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3297 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
3298 l_curr_calling_sequence);
3299 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3300 'p_checkrun_name = '||p_checkrun_name
3301 ||', p_completed_pmts_group_id = '||p_completed_pmts_group_id
3302 ||', p_org_id = '||p_org_id
3303 ||', p_checkrun_name = '||p_checkrun_name);
3304 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
3305 END IF;
3306 APP_EXCEPTION.RAISE_EXCEPTION();
3307 END update_pmt_batch_event_status;
3308
3309
3310 /*============================================================================
3311 | PROCEDURE - CREATE_PAYMENT_BATCH_EVENTS (PRIVATE)
3312 |
3313 | DESCRIPTION
3317 | p_checkrun_name: payment request name
3314 | Create accounting events for 'PAYMENT BATCH' type
3315 |
3316 | PRAMETERS
3318 | p_accounting_date:Event Date
3319 | p_org_id : org id for each small batch inside the payment request
3320 | p_set_of_books_id: the ledger id for this ou
3321 | p_calling_sequence: Debug information
3322 |
3323 | KNOWN ISSUES:
3324 |
3325 | NOTES:
3326 |
3327 | MODIFICATION HISTORY
3328 | Date Author Description of Change
3329 |
3330 *===========================================================================*/
3331 PROCEDURE create_payment_batch_events(
3332 p_checkrun_name IN VARCHAR2,
3333 p_completed_pmts_group_id IN NUMBER,
3334 p_accounting_date IN DATE,
3335 p_org_id IN NUMBER,
3336 p_set_of_books_id IN NUMBER,
3337 p_calling_sequence IN VARCHAR2)
3338 IS
3339
3340 l_event_type VARCHAR2(30) := PAYMENT_CREATED_TYPE;
3341 l_event_status VARCHAR2(1) := XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED;
3342 l_curr_calling_sequence VARCHAR2(2000);
3343 l_count NUMBER := 0;
3344
3345 -- Logging:
3346 l_procedure_name CONSTANT VARCHAR2(30) := 'CREATE_PAYMENT_BATCH_EVENTS';
3347 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3348
3349 BEGIN
3350
3351 l_curr_calling_sequence := p_calling_sequence ||
3352 ' -> AP_ACCOUNTING_EVENTS_PKG.CREATE_PAYMENT_BATCH_EVENTS';
3353
3354 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3355
3356 l_log_msg := 'Begin of procedure'||l_procedure_name;
3357 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3358 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3359 G_MODULE_NAME || l_procedure_name,
3360 l_log_msg);
3361
3362 END IF;
3363
3364 l_log_msg := 'p_set_of_books_id = '|| p_set_of_books_id ||
3365 'p_org_id = '|| p_org_id ||
3366 'p_completed_pmts_group_id = '|| p_completed_pmts_group_id ||
3367 'p_checkrun_name = '|| p_checkrun_name;
3368 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3369 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3370 G_MODULE_NAME || l_procedure_name,
3371 l_log_msg);
3372
3373 END IF;
3374
3375 BEGIN
3376
3377 l_log_msg := 'Begin to clean up the xla_event_gt_table';
3378 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3379 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3380 G_MODULE_NAME || l_procedure_name,
3381 l_log_msg);
3382 END IF;
3383
3384 DELETE XLA_EVENTS_INT_GT XEG
3385 WHERE application_id = 200;
3386
3387 l_log_msg := 'After clean up the xla_event_gt_table';
3388 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3389 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3390 G_MODULE_NAME || l_procedure_name,
3391 l_log_msg);
3392 END IF;
3393
3394 END;
3395
3396 BEGIN
3397
3398 l_log_msg := 'create event for group_id = ' || p_completed_pmts_group_id ;
3399 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3400 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3401 G_MODULE_NAME || l_procedure_name,
3402 l_log_msg);
3403 END IF;
3404
3405 INSERT INTO XLA_EVENTS_INT_GT (
3406 application_id,
3407 ledger_id,
3408 legal_entity_id,
3409 entity_code,
3410 transaction_number,
3411 source_id_int_1,
3412 transaction_date,
3413 security_id_int_1,
3414 event_type_code,
3415 event_date,
3416 event_status_code )
3417 (SELECT
3418 '200',
3419 p_set_of_books_id,
3420 ac.legal_entity_id,
3421 PAYMENTS_ENTITY,
3422 ac.check_number,
3423 ac.check_id,
3424 ac.check_date,
3425 ac.org_id,
3426 l_event_type,
3427 p_accounting_date,
3428 l_event_status
3429 FROM ap_checks_all ac
3430 WHERE ac.checkrun_name = p_checkrun_name
3431 AND ac.completed_pmts_group_id = p_completed_pmts_group_id
3432 AND ac.org_id = p_org_id);
3433
3434
3435 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3436
3437 SELECT COUNT(*)
3438 INTO l_count
3439 FROM XLA_EVENTS_INT_GT;
3440
3441 l_log_msg := 'event gt table has record number = '|| l_count;
3442 FND_LOG.STRING(G_LEVEL_STATEMENT,
3443 G_MODULE_NAME || l_procedure_name,
3444 l_log_msg);
3445 END IF;
3446
3447 EXCEPTION
3448 WHEN OTHERS THEN
3449 l_log_msg := 'unknown exception when try to insert into xla gt table';
3450 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3451 FND_LOG.STRING(G_LEVEL_STATEMENT,
3452 G_MODULE_NAME || l_procedure_name,
3453 l_log_msg);
3454 END IF;
3455 END;
3456
3460 G_MODULE_NAME || l_procedure_name,
3457 l_log_msg := 'after insert into xla_event_gt table';
3458 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3459 FND_LOG.STRING(G_LEVEL_STATEMENT,
3461 l_log_msg);
3462 END IF;
3463
3464 BEGIN
3465 XLA_EVENTS_PUB_PKG.create_bulk_events
3466 (p_source_application_id => NULL
3467 ,p_application_id => '200'
3468 ,p_legal_entity_id => NULL
3469 ,p_ledger_id => p_set_of_books_id
3470 ,p_entity_type_code => PAYMENTS_ENTITY );
3471 END;
3472
3473 l_log_msg := 'After calling the sla create_bulk_events';
3474 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3475 FND_LOG.STRING(G_LEVEL_STATEMENT,
3476 G_MODULE_NAME || l_procedure_name,
3477 l_log_msg);
3478 END IF;
3479
3480 l_log_msg := 'Right before insert into ap_payment_history table';
3481 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3482 FND_LOG.STRING(G_LEVEL_STATEMENT,
3483 G_MODULE_NAME || l_procedure_name,
3484 l_log_msg);
3485 END IF;
3486
3487 BEGIN
3488
3489 INSERT INTO ap_payment_history_all
3490 ( payment_history_id,
3491 check_id,
3492 accounting_date,
3493 transaction_type,
3494 posted_flag,
3495 trx_bank_amount,
3496 errors_bank_amount,
3497 charges_bank_amount,
3498 bank_currency_code,
3499 bank_to_base_xrate_type,
3500 bank_to_base_xrate_date,
3501 bank_to_base_xrate,
3502 trx_pmt_amount,
3503 errors_pmt_amount,
3504 charges_pmt_amount,
3505 pmt_currency_code,
3506 pmt_to_base_xrate_type,
3507 pmt_to_base_xrate_date,
3508 pmt_to_base_xrate,
3509 trx_base_amount,
3510 errors_base_amount,
3511 charges_base_amount,
3512 matched_flag,
3513 rev_pmt_hist_id,
3514 creation_date,
3515 created_by,
3516 last_update_date,
3517 last_updated_by,
3518 last_update_login,
3519 program_update_date,
3520 program_application_id,
3521 program_id,
3522 accounting_event_id,
3523 request_id,
3524 org_id,
3525 related_event_id ) -- Bug 5015973 -- Bug 5658623: Adding hint
3526 ( select /*+ Leading(xeg) index(ac ap_checks_u1) */
3527 ap_payment_history_s.NEXTVAL, -- payment_history_id
3528 ac.check_id, -- check_id
3529 trunc(p_accounting_date), -- accounting_date bug6602676
3530 l_event_type, -- transaction_type
3531 'N', -- posted_flag
3532 NULL, -- trx_bank_amount
3533 NULL, -- errors_bank_amount
3534 NULL, -- charges_bank_amount
3535 NULL, -- bank_currency_code
3536 NULL, -- bank_to_base_xrate_type
3537 NULL, -- bank_to_base_xrate_date
3538 NULL, -- bank_to_base_xrate
3539 ac.amount, -- trx_pmt_amount
3540 NULL, -- errors_pmt_amount
3541 NULL, -- charges_pmt_amount
3542 ac.currency_code, -- pmt_currency_code
3543 ac.exchange_rate_type, -- pmt_to_base_xrate_type
3544 ac.exchange_date, -- pmt_to_base_xrate_date
3545 ac.exchange_rate, -- pmt_to_base_xrate
3546 NVL(ac.base_amount, ac.amount), -- trx_base_amount
3547 NULL, -- errors_base_amount
3548 NULL, -- charges_base_amount
3549 NULL, -- matched_flag
3550 NULL, -- rev_pmt_hist_id
3551 SYSDATE, -- creation_date
3552 FND_GLOBAL.user_id, -- created_by
3553 SYSDATE, -- last_update_date
3554 FND_GLOBAL.user_id, -- last_updated_by
3555 FND_GLOBAL.login_id, -- last_update_login
3556 SYSDATE, -- program_update_date
3557 NULL, -- program_application_id
3558 NULL, -- program_id
3559 XEG.event_id, -- accounting_event_id
3560 NULL, -- request_id
3561 ac.org_id, -- org_id
3562 XEG.event_id -- related_event_id
3563 from ap_checks_all ac,
3564 xla_events_int_gt xeg
3565 where ac.completed_pmts_group_id = p_completed_pmts_group_id
3566 and ac.org_id = p_org_id
3567 and xeg.source_id_int_1 = ac.check_id);
3568
3569 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3570
3571 SELECT COUNT(*)
3572 INTO l_count
3573 FROM ap_payment_history_all aph,
3574 ap_checks_all ac
3575 WHERE aph.check_id = ac.check_id
3576 AND ac.completed_pmts_group_id = p_completed_pmts_group_id
3577 AND ac.org_id = p_org_id;
3578
3579 l_log_msg := 'payment history has record number = '|| l_count;
3580 FND_LOG.STRING(G_LEVEL_STATEMENT,
3581 G_MODULE_NAME || l_procedure_name,
3582 l_log_msg);
3583 END IF;
3584
3585 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3586
3587 SELECT accounting_event_id
3588 INTO l_count
3589 FROM ap_payment_history_all aph,
3590 ap_checks_all ac
3591 WHERE aph.check_id = ac.check_id
3592 AND ac.completed_pmts_group_id = p_completed_pmts_group_id
3596 l_log_msg := ' one account event_id created = '|| l_count;
3593 AND ac.org_id = p_org_id
3594 AND rownum = 1;
3595
3597 FND_LOG.STRING(G_LEVEL_STATEMENT,
3598 G_MODULE_NAME || l_procedure_name,
3599 l_log_msg);
3600 END IF;
3601
3602 EXCEPTION
3603 WHEN OTHERS THEN
3604 l_log_msg := 'unknown exception when try to insert ap_payment_history table';
3605 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3606 FND_LOG.STRING(G_LEVEL_STATEMENT,
3607 G_MODULE_NAME || l_procedure_name,
3608 l_log_msg);
3609 END IF;
3610 END;
3611
3612 l_log_msg := 'After insert ap_payment_history_table and end the procedure';
3613 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3614 FND_LOG.STRING(G_LEVEL_STATEMENT,
3615 G_MODULE_NAME || l_procedure_name,
3616 l_log_msg);
3617 END IF;
3618
3619
3620 EXCEPTION
3621 WHEN OTHERS THEN
3622
3623 IF (SQLCODE <> -20001) THEN
3624 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3625 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3626 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
3627 l_curr_calling_sequence);
3628 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3629 'p_completed_pmts_group_id = '||p_completed_pmts_group_id
3630 ||', p_set_of_books_id = '||p_set_of_books_id
3631 ||', p_org_id = '||p_set_of_books_id
3632 ||', p_accounting_date = '||p_accounting_date);
3633 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
3634 END IF;
3635 APP_EXCEPTION.RAISE_EXCEPTION();
3636 END create_payment_batch_events;
3637
3638
3639 /*============================================================================
3640 | PROCEDURE - UPDATE_AWT_INT_DISTS (PUBLIC)
3641 |
3642 | DESCRIPTION
3643 | This procedure is called by the payment event creation procs (EXCEPT for
3644 | pmt batches). Stamp the event_id on all awt invoice distributions and
3645 | on interest invoice distributions that were created by the payment for
3646 | which this event is being created.It will also stamp the Payment
3647 | Clearing Accounting event_id on the records in AIP when
3648 | when_to_acct_pmt = clrg only
3649 |
3650 | PRAMETERS
3651 | p_event_type: Event type
3652 | p_check_id: Check ID
3653 | p_event_id: Event ID
3654 | p_calling_sequence: Debug information
3655 |
3656 | KNOWN ISSUES:
3657 |
3658 | NOTES:
3659 |
3660 | MODIFICATION HISTORY
3661 | Date Author Description of Change
3662 |
3663 *===========================================================================*/
3664
3665 PROCEDURE update_awt_int_dists (
3666 p_event_type IN VARCHAR2,
3667 p_check_id IN NUMBER,
3668 p_event_id IN NUMBER,
3669 p_calling_sequence IN VARCHAR2)
3670 IS
3671
3672 l_curr_calling_sequence VARCHAR2(2000);
3673
3674 -- Logging:
3675 l_procedure_name CONSTANT VARCHAR2(30) := 'UPDATE_AWT_INT_DISTS';
3676 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3677
3678 BEGIN
3679
3680 l_curr_calling_sequence :=
3681 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.UPDATE_AWT_INT_DISTS';
3682
3683 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3684
3685 l_log_msg := 'Begin of procedure '||l_procedure_name;
3686 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3687 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3688 G_MODULE_NAME || l_procedure_name,
3689 l_log_msg);
3690 END IF;
3691
3692 IF ( p_event_type IN (PAYMENT_CREATED_TYPE,
3693 PAYMENT_ADJUSTED_TYPE,
3694 PAYMENT_CANCELLED_TYPE )) THEN
3695
3696 --Stamp the event_id on all awt invoice distributions that were
3697 --created by the payment for which this event is being created.
3698
3699 l_log_msg := 'Update ap_invoice_distribution, set accounting_event_id ='
3700 ||to_char(p_event_id);
3701 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3702 FND_LOG.STRING(G_LEVEL_STATEMENT,
3703 G_MODULE_NAME || l_procedure_name,
3704 l_log_msg);
3705 END IF;
3706
3707 UPDATE ap_invoice_distributions D
3708 SET D.accounting_event_id = p_event_id
3709 WHERE D.accounting_event_id IS NULL
3710 AND D.awt_invoice_payment_id IN
3711 (SELECT AIP1.invoice_payment_id
3712 FROM ap_invoice_payments AIP1
3713 WHERE AIP1.accounting_event_id = p_event_id
3714 AND AIP1.check_id = p_check_id);
3715
3716 --Stamp the event_id on all invoice dists that belong to interest
3717 --invoices created by the payment for which this event is created.
3718
3719 l_log_msg := 'Update ap_invoice_distribution, set accounting_event_id =
3720 '||to_char(p_event_id);
3721 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3722 FND_LOG.STRING(G_LEVEL_STATEMENT,
3723 G_MODULE_NAME || l_procedure_name,
3724 l_log_msg);
3725 END IF;
3726
3730 AND D.invoice_id IN
3727 UPDATE ap_invoice_distributions_all D
3728 SET D.accounting_event_id = p_event_id
3729 WHERE D.accounting_event_id IS NULL
3731 (SELECT AI.invoice_id
3732 FROM ap_invoice_payments_all AIP2,
3733 ap_invoices_all AI
3734 WHERE AI.invoice_id = AIP2.invoice_id
3735 AND AIP2.check_id = p_check_id
3736 AND AIP2.accounting_event_id = p_event_id
3737 AND AI.invoice_type_lookup_code = 'INTEREST');
3738
3739 END IF; --event is Payment, Payment Adjustment or Payment Cancellation
3740
3741 l_log_msg := 'End of procedure '||l_procedure_name;
3742 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3743 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3744 G_MODULE_NAME || l_procedure_name,
3745 l_log_msg);
3746 END IF;
3747
3748 EXCEPTION
3749 WHEN OTHERS THEN
3750 IF (SQLCODE <> -20001) THEN
3751 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3752 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3753 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
3754 l_curr_calling_sequence);
3755 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3756 'p_event_type = '||p_event_type
3757 ||', p_check_id = '||p_check_id
3758 ||', p_event_id = '||p_event_id);
3759 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
3760 END IF;
3761 APP_EXCEPTION.RAISE_EXCEPTION();
3762 END update_awt_int_dists;
3763
3764
3765 /*============================================================================
3766 | PROCEDURE - BATCH_UPDATE_PAYMENT_INFO (PUBLIC)
3767 |
3768 | DESCRIPTION
3769 | This procedure is called from appbip.lpc only when when_to_acct_pmt=
3770 | ALWAYS. It is used to update awt and int inv dists with accoutning
3771 | event id.
3772 |
3773 | PRAMETERS
3774 | p_checkrun_name: Payment batch's name
3775 | p_calling_sequence: Debug information
3776 |
3777 | KNOWN ISSUES:
3778 |
3779 | NOTES:
3780 |
3781 | MODIFICATION HISTORY
3782 | Date Author Description of Change
3783 |
3784 *===========================================================================*/
3785 PROCEDURE batch_update_payment_info(
3786 p_checkrun_name IN VARCHAR2,
3787 p_completed_pmts_group_id IN NUMBER,
3788 p_org_id IN NUMBER,
3789 p_calling_sequence IN VARCHAR2 DEFAULT NULL)
3790 IS
3791
3792 CURSOR get_payment_info IS
3793 SELECT AC.check_id,
3794 AIP.accounting_event_id -- Bug3343314
3795 FROM ap_checks AC,
3796 ap_invoice_payments AIP -- Bug3343314
3797 WHERE AC.check_id = AIP.check_id -- Bug3343314
3798 AND AC.checkrun_name = p_checkrun_name
3799 AND AC.status_lookup_code NOT IN ('OVERFLOW', 'SET UP')
3800 AND AC.completed_pmts_group_id = p_completed_pmts_group_id
3801 AND AC.org_id = p_org_id
3802 AND AIP.posted_flag <> 'Y'; -- Bug3343314
3803
3804 TYPE t_check_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
3805 l_check_ids t_check_ids;
3806
3807 TYPE t_accounting_event_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
3808 l_accounting_event_ids t_accounting_event_ids;
3809
3810 l_curr_calling_sequence VARCHAR2(2000);
3811
3812 -- Logging:
3813 l_procedure_name CONSTANT VARCHAR2(30) := 'BATCH_UPDATE_PAYMENT_INFO';
3814 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3815
3816 BEGIN
3817
3818 l_curr_calling_sequence := p_calling_sequence ||
3819 ' -> AP_ACCOUNTING_EVENTS_PKG.BATCH_UPDATE_PAYMENT_INFO';
3820
3821 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3822
3823 l_log_msg := 'Begin of procedure '||l_procedure_name;
3824 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3825 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3826 G_MODULE_NAME || l_procedure_name,
3827 l_log_msg);
3828 END IF;
3829
3830 OPEN get_payment_info;
3831 LOOP
3832
3833 l_log_msg := 'Get data from cursor get_payment_info';
3834 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3835 FND_LOG.STRING(G_LEVEL_STATEMENT,
3836 G_MODULE_NAME || l_procedure_name,
3837 l_log_msg);
3838 END IF;
3839
3840 FETCH get_payment_info
3841 BULK COLLECT INTO
3842 l_check_ids,
3843 l_accounting_event_ids
3844 LIMIT 1000;
3845
3846 /*
3847 || -------------------------------------------------------------------
3848 || Step: 1 We will stamp the Accounting_Event_ID on
3849 || for AWT Invoice Distributions table that were created by the
3850 || payment for which this event is being created
3851 || -------------------------------------------------------------------
3852 */
3853
3854 FORALL i IN 1 .. l_check_ids.count
3855
3856 UPDATE ap_invoice_distributions AID
3857 SET AID.accounting_event_id = l_accounting_event_ids(i)
3858 WHERE AID.accounting_event_id IS NULL
3859 AND AID.awt_invoice_payment_id IN
3860 (SELECT AIP.invoice_payment_id
3861 FROM ap_invoice_payments AIP
3865 /*
3862 WHERE AIP.check_id = l_check_ids(i) AND
3863 AIP.accounting_event_id = l_accounting_event_ids(i));
3864
3866 || -------------------------------------------------------------------
3867 || Step: 2 We will stamp the Accounting_Event_ID (of the Payment Event)
3868 || on all Invoice Distributions that belong to interest invoices
3869 || created by the payment
3870 || -------------------------------------------------------------------
3871 */
3872
3873 FORALL i IN 1 .. l_check_ids.count
3874
3875 UPDATE ap_invoice_distributions_all AID
3876 SET AID.accounting_event_id = l_accounting_event_ids(i)
3877 WHERE AID.accounting_event_id IS NULL
3878 AND AID.invoice_id IN
3879 ( SELECT AI.invoice_id
3880 FROM ap_invoice_payments_all AIP,
3881 ap_invoices_all AI
3882 WHERE AIP.invoice_id = AI.invoice_id
3883 AND AIP.accounting_event_id = l_accounting_event_ids(i)
3884 AND AIP.check_id = l_check_ids(i)
3885 AND AI.invoice_type_lookup_code = 'INTEREST');
3886
3887 EXIT WHEN get_payment_info%NOTFOUND;
3888 END LOOP;
3889 CLOSE get_payment_info;
3890
3891 l_log_msg := 'End of procedure '||l_procedure_name;
3892 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3893 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3894 G_MODULE_NAME || l_procedure_name,
3895 l_log_msg);
3896 END IF;
3897
3898 EXCEPTION
3899 WHEN OTHERS THEN
3900 IF (get_payment_info%ISOPEN) THEN
3901 CLOSE get_payment_info;
3902 END IF;
3903
3904 IF (SQLCODE <> -20001) THEN
3905 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3906 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3907 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
3908 l_curr_calling_sequence);
3909 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3910 ' p_checkrun_name = '|| p_checkrun_name);
3911 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
3912 END IF;
3913 APP_EXCEPTION.RAISE_EXCEPTION();
3914 END batch_update_payment_info;
3915
3916 /*============================================================================
3917 | FUNCTION - IS_EVENT_COMPLETE (PRIVATE)
3918 |
3919 | DESCRIPTION
3920 |
3921 |
3922 | PRAMETERS
3923 | p_doc_type:INVOICES_ENTITY or PAYMENT_ENTITY
3924 | p_source_id: Invoice ID or Check ID
3925 | p_calling_sequence: Debug information
3926 |
3927 | RETURN: VARCHAR2
3928 |
3929 | KNOWN ISSUES:
3930 |
3931 | NOTES:
3932 |
3933 | MODIFICATION HISTORY
3934 | Date Author Description of Change
3935 |
3936 *===========================================================================*/
3937 FUNCTION is_event_complete (p_doc_type IN VARCHAR2,
3938 p_source_id IN NUMBER,
3939 p_calling_sequence IN VARCHAR2)
3940 RETURN VARCHAR2
3941 IS
3942
3943 l_count NUMBER := 0;
3944 l_purch_enc_flag financials_system_parameters.purch_encumbrance_flag%TYPE;
3945
3946 l_curr_calling_sequence VARCHAR2(2000);
3947
3948 -- Logging:
3949 l_procedure_name CONSTANT VARCHAR2(30) := 'BATCH_UPDATE_PAYMENT_INFO';
3950 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3951
3952 BEGIN
3953
3954 l_curr_calling_sequence :=
3955 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.IS_EVENT_COMPLETE';
3956
3957 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3958
3959 l_log_msg := 'Begin of procedure '||l_procedure_name;
3960 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3961 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3962 G_MODULE_NAME || l_procedure_name,
3963 l_log_msg);
3964 END IF;
3965
3966 IF p_doc_type = INVOICES_ENTITY THEN -- Bug3343314
3967
3968 BEGIN
3969
3970 SELECT nvl(purch_encumbrance_flag,'N')
3971 INTO l_purch_enc_flag
3972 FROM financials_system_parameters FSP,
3973 AP_INVOICES INV
3974 WHERE INV.org_id = FSP.org_id
3975 AND INV.set_of_books_id = FSP.set_of_books_id -- Bug 5608968 Avoid full index scan of fsp
3976 AND INV.invoice_id = p_source_id;
3977 EXCEPTION
3978 WHEN NO_DATA_FOUND THEN
3979 l_purch_enc_flag := 'N';
3980 END;
3981
3982 IF l_purch_enc_flag = 'N' THEN
3983
3984 BEGIN
3985 SELECT count(*)
3986 INTO l_count
3987 FROM ap_invoice_distributions AID1
3988 WHERE AID1.invoice_id = p_source_id
3989 AND nvl(AID1.match_status_flag,'N') NOT IN ('T','A');
3990 EXCEPTION
3991 WHEN NO_DATA_FOUND THEN
3992 l_count:= 0;
3993 END;
3994
3995 ELSE
3996
3997 BEGIN
3998 SELECT count(*)
3999 INTO l_count
4000 FROM ap_invoice_distributions AID
4001 WHERE AID.invoice_id = p_source_id
4002 AND nvl(AID.match_status_flag,'N') <> 'A';
4003 EXCEPTION
4004 WHEN NO_DATA_FOUND THEN
4005 l_count:= 0;
4006 END;
4007 END IF;
4008
4012
4009 ELSIF p_doc_type = PAYMENTS_ENTITY THEN -- Bug3343314
4010
4011 BEGIN
4013 SELECT nvl(purch_encumbrance_flag,'N')
4014 INTO l_purch_enc_flag
4015 FROM financials_system_parameters FSP,
4016 AP_CHECKS AC
4017 WHERE AC.org_id = FSP.org_id
4018 AND AC.check_id = p_source_id;
4019 EXCEPTION
4020 WHEN NO_DATA_FOUND THEN
4021 l_purch_enc_flag := 'N';
4022 END;
4023
4024 IF l_purch_enc_flag = 'N' THEN
4025
4026 BEGIN
4027 SELECT count(*)
4028 INTO l_count
4029 FROM ap_invoice_distributions AID1
4030 WHERE AID1.invoice_id in (SELECT invoice_id
4031 FROM ap_invoice_payments
4032 WHERE check_id = p_source_id)
4033 AND nvl(AID1.match_status_flag,'N') NOT IN ('T','A');
4034 EXCEPTION
4035 WHEN NO_DATA_FOUND THEN
4036 l_count:= 0;
4037 END;
4038 ELSE
4039
4040 BEGIN
4041 SELECT count(*)
4042 INTO l_count
4043 FROM ap_invoice_distributions AID
4044 WHERE AID.invoice_id in (SELECT invoice_id
4045 FROM ap_invoice_payments
4046 WHERE check_id = p_source_id)
4047 AND nvl(AID.match_status_flag,'N') <> 'A' ;
4048 EXCEPTION
4049 WHEN NO_DATA_FOUND THEN
4050 l_count:= 0;
4051 END;
4052
4053 END IF; -- l_purch_enc_flag
4054
4055 END IF ; -- p_doc_type
4056
4057 IF l_count = 0 THEN
4058 IF p_doc_type = INVOICES_ENTITY THEN -- Bug3343314
4059 BEGIN
4060 SELECT count(*)
4061 INTO l_count
4062 FROM ap_holds H, ap_hold_codes C
4063 WHERE H.invoice_id = p_source_id
4064 AND H.hold_lookup_code = C.hold_lookup_code
4065 AND ((H.release_lookup_code IS NULL)
4066 AND (C.postable_flag = 'N'
4067 OR C.postable_flag = 'X'));
4068 EXCEPTION
4069 WHEN NO_DATA_FOUND THEN
4070 l_count:= 0;
4071 END;
4072
4073 ELSIF p_doc_type = PAYMENTS_ENTITY THEN -- Bug3343314
4074
4075 BEGIN
4076
4077 SELECT count(*)
4078 INTO l_count
4079 FROM ap_holds H, ap_hold_codes C
4080 WHERE H.invoice_id in (SELECT invoice_id
4081 FROM ap_invoice_payments
4082 WHERE check_id = p_source_id)
4083 AND H.hold_lookup_code = C.hold_lookup_code
4084 AND ((H.release_lookup_code IS NULL)
4085 AND (C.postable_flag ='N'
4086 OR C.postable_flag = 'X'));
4087 EXCEPTION
4088 WHEN NO_DATA_FOUND THEN
4089 l_count:= 0;
4090 END;
4091
4092 END IF; -- p_doc_type
4093
4094 END IF; -- l_count = 0
4095
4096 l_log_msg := 'End of procedure '||l_procedure_name;
4097 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4098 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4099 G_MODULE_NAME || l_procedure_name,
4100 l_log_msg);
4101 END IF;
4102
4103 IF l_count = 0 THEN
4104 Return('CREATED');
4105 ELSE
4106 Return('INCOMPLETE');
4107 END IF; -- l_count = 0
4108
4109 EXCEPTION
4110 WHEN OTHERS THEN
4111 IF (SQLCODE <> -20001) THEN
4112 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4113 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4114 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
4115 l_curr_calling_sequence);
4116 FND_MESSAGE.SET_TOKEN('PARAMETERS',
4117 'p_doc_type = '||p_doc_type
4118 ||', p_source_id = '||p_source_id);
4119 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
4120 END IF;
4121 APP_EXCEPTION.RAISE_EXCEPTION();
4122 END is_event_complete;
4123
4124 /*============================================================================
4125 | FUNCTION - DERIVE_PAYMENT_ADJ_EVENT (PRIVATE)
4126 |
4127 | DESCRIPTION
4128 | This procedure is used to derive payment adjustment event.
4129 |
4130 | PRAMETERS
4131 | p_check_id: Check ID
4132 | p_accounting_date: Accounting Date
4133 | p_event_type:Event Type
4134 | p_accounting_event_id: Derived Accounting Event ID
4135 | p_calling_sequence:Debug Information
4136 |
4137 | KNOWN ISSUES:
4138 |
4139 | NOTES:
4140 |
4141 | MODIFICATION HISTORY
4142 | Date Author Description of Change
4143 |
4144 *===========================================================================*/
4145 PROCEDURE derive_payment_adj_event (
4146 p_check_id IN NUMBER,
4147 p_accounting_date IN DATE,
4148 p_event_type IN VARCHAR2,
4149 p_accounting_event_id OUT NOCOPY NUMBER,
4150 p_calling_sequence IN VARCHAR2)
4151 IS
4152
4153 l_legal_entity_id NUMBER(15);
4154 l_ledger_id NUMBER(15);
4155 l_org_id NUMBER(15);
4156 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
4157 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
4161 l_exchange_rate_type VARCHAR2(30);
4158 l_accounting_event_id NUMBER := NULL;
4159 l_event_info XLA_EVENTS_PUB_PKG.T_EVENT_INFO;
4160
4162 l_exchange_rate_date DATE;
4163 l_exchange_rate NUMBER;
4164 l_currency_code VARCHAR2(15);
4165 l_creation_date DATE;
4166 l_last_updated_by NUMBER(15);
4167
4168 CURSOR l_event_cur IS
4169 SELECT distinct(AIP.accounting_event_id)
4170 FROM ap_invoice_payments AIP,
4171 ap_checks AC
4172 WHERE AIP.check_id = p_check_id
4173 AND AIP.check_id = AC.check_id
4174 AND nvl(AIP.posted_flag, 'N') = 'N'
4175 AND AC.void_date IS NULL;
4176
4177 l_curr_calling_sequence VARCHAR2(2000);
4178
4179 -- Logging:
4180 l_procedure_name CONSTANT VARCHAR2(30) := 'BATCH_UPDATE_PAYMENT_INFO';
4181 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4182
4183
4184 BEGIN
4185
4186 l_curr_calling_sequence := p_calling_sequence ||
4187 ' -> AP_ACCOUNTING_EVENTS_PKG.DERIVE_PAYMENT_ADJ_EVENT';
4188
4189 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4190
4191 l_log_msg := 'Begin of procedure '||l_procedure_name;
4192 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4193 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4194 G_MODULE_NAME || l_procedure_name,
4195 l_log_msg);
4196 END IF;
4197
4198 l_log_msg := 'Before calling procedure get_payment_info';
4199 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4200 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4201 G_MODULE_NAME || l_procedure_name,
4202 l_log_msg);
4203 END IF;
4204
4205 get_payment_info
4206 ( p_check_id => p_check_id,
4207 p_org_id => l_org_id, -- OUT
4208 p_legal_entity_id => l_legal_entity_id, -- OUT
4209 p_ledger_id => l_ledger_id, -- OUT
4210 p_calling_sequence => l_curr_calling_sequence
4211 );
4212
4213 l_log_msg := 'After calling procedure get_payment_info';
4214 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4215 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4216 G_MODULE_NAME || l_procedure_name,
4217 l_log_msg);
4218 END IF;
4219
4220 l_log_msg := 'Before calling procedure get_event_security_context';
4221 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4222 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4223 G_MODULE_NAME || l_procedure_name,
4224 l_log_msg);
4225 END IF;
4226
4227 l_event_security_context :=
4228 get_event_security_context
4229 ( p_org_id => l_org_id,
4230 p_calling_sequence => l_curr_calling_sequence
4231 );
4232
4233
4234 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4235 l_log_msg := 'After calling procedure get_event_security_context';
4236 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4237 G_MODULE_NAME || l_procedure_name,
4238 l_log_msg);
4239 END IF;
4240
4241 l_log_msg := 'Before calling procedure get_payment_event_source_info';
4242 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4243 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4244 G_MODULE_NAME || l_procedure_name,
4245 l_log_msg);
4246 END IF;
4247
4248 l_event_source_info :=
4249 get_payment_event_source_info
4250 ( p_legal_entity_id => l_legal_entity_id,
4251 p_ledger_id => l_ledger_id,
4252 p_check_id => p_check_id,
4253 p_calling_sequence => l_curr_calling_sequence
4254 );
4255
4256 l_log_msg := 'After calling procedure get_payment_event_source_info';
4257 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4258 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4259 G_MODULE_NAME || l_procedure_name,
4260 l_log_msg);
4261 END IF;
4262
4263 OPEN l_event_cur;
4264 LOOP
4265
4266 FETCH l_event_cur INTO
4267 l_accounting_event_id;
4268 EXIT WHEN l_event_cur%NOTFOUND;
4269
4270 l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
4271 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4272 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4273 G_MODULE_NAME || l_procedure_name,
4274 l_log_msg);
4275 END IF;
4276
4277 l_event_info :=
4278 AP_XLA_EVENTS_PKG.GET_EVENT_INFO
4279 ( p_event_source_info => l_event_source_info,
4280 p_event_id => l_accounting_event_id,
4281 p_valuation_method => NULL,
4282 p_security_context => l_event_security_context,
4283 p_calling_sequence => l_curr_calling_sequence
4284 );
4285
4286 l_log_msg := 'After calling AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
4287 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4288 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4289 G_MODULE_NAME || l_procedure_name,
4290 l_log_msg);
4291 END IF;
4292
4293
4294 IF ((P_event_type = MANUAL_PAYMENT_ADJUSTED_TYPE AND
4295 l_event_info.event_type_code = MANUAL_PAYMENT_ADJUSTED_TYPE)
4296 OR
4297 (P_event_type = UPGRADED_MAN_PAY_REV_TYPE AND
4301 l_event_info.event_type_code = UPGRADED_MAN_PAY_ADJ_TYPE)) THEN
4298 l_event_info.event_type_code = UPGRADED_MAN_PAY_REV_TYPE )
4299 OR
4300 (P_event_type = UPGRADED_MAN_PAY_ADJ_TYPE AND
4302
4303 EXIT;
4304 ELSE
4305 l_accounting_event_id := NULL;
4306 END IF;
4307
4308 END LOOP;
4309 CLOSE l_event_cur;
4310
4311 -- If an unaccounted Payment Adjustment event does not already
4312 -- exist for that date, we will create a new event
4313
4314 IF (l_accounting_event_id IS NULL) THEN
4315 l_accounting_event_id := create_payment_event
4316 (p_event_type => p_event_type,
4317 p_check_id => p_check_id,
4318 p_event_date => p_accounting_date,
4319 p_calling_sequence => l_curr_calling_sequence);
4320
4321 SELECT exchange_rate_type,
4322 exchange_date,
4323 exchange_rate,
4324 currency_code,
4325 creation_date,
4326 last_updated_by
4327 INTO l_exchange_rate_type,
4328 l_exchange_rate_date,
4329 l_exchange_rate,
4330 l_currency_code,
4331 l_creation_date,
4332 l_last_updated_by
4333 FROM AP_CHECKS AC
4334 WHERE AC.check_id = P_check_id;
4335
4336 l_log_msg := 'Before AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
4337 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4338 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4339 G_MODULE_NAME || l_procedure_name,
4340 l_log_msg);
4341 END IF;
4342
4343 AP_RECONCILIATION_PKG.insert_payment_history
4344 (
4345 x_check_id => p_check_id,
4346 x_transaction_type => p_event_type,
4347 x_accounting_date => p_accounting_date,
4348 x_trx_bank_amount => NULL,
4349 x_errors_bank_amount => NULL,
4350 x_charges_bank_amount => NULL,
4351 x_bank_currency_code => NULL,
4352 x_bank_to_base_xrate_type => NULL,
4353 x_bank_to_base_xrate_date => NULL,
4354 x_bank_to_base_xrate => NULL,
4355 x_trx_pmt_amount => 0,
4356 x_errors_pmt_amount => NULL,
4357 x_charges_pmt_amount => NULL,
4358 x_pmt_currency_code => l_currency_code,
4359 x_pmt_to_base_xrate_type => l_exchange_rate_type,
4360 x_pmt_to_base_xrate_date => l_exchange_rate_date,
4361 x_pmt_to_base_xrate => l_exchange_rate,
4362 x_trx_base_amount => 0,
4363 x_errors_base_amount => NULL,
4364 x_charges_base_amount => NULL,
4365 x_matched_flag => NULL,
4366 x_rev_pmt_hist_id => NULL,
4367 x_org_id => l_org_id, -- bug 4578865
4368 x_creation_date => SYSDATE,
4369 x_created_by => l_last_updated_by,
4370 x_last_update_date => SYSDATE,
4371 x_last_updated_by => l_last_updated_by,
4372 x_last_update_login => l_last_updated_by,
4373 x_program_update_date => NULL,
4374 x_program_application_id => NULL,
4375 x_program_id => NULL,
4376 x_request_id => NULL,
4377 x_calling_sequence => l_curr_calling_sequence,
4378 x_accounting_event_id => l_accounting_event_id
4379 );
4380
4381 l_log_msg := 'After AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
4382 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4383 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4384 G_MODULE_NAME || l_procedure_name,
4385 l_log_msg);
4386 END IF;
4387 END IF;
4388
4389 p_accounting_event_id := l_accounting_event_id;
4390
4391 l_log_msg := 'End of procedure '||l_procedure_name;
4392 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4393 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4394 G_MODULE_NAME || l_procedure_name,
4395 l_log_msg);
4396 END IF;
4397
4398 EXCEPTION
4399 WHEN OTHERS THEN
4400 IF (l_event_cur%ISOPEN) THEN
4401 CLOSE l_event_cur;
4402 END IF;
4403 IF (SQLCODE <> -20001) THEN
4404 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4405 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4406 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
4407 l_curr_calling_sequence);
4408 FND_MESSAGE.SET_TOKEN('PARAMETERS',
4409 'p_check_id = '||p_check_id
4410 ||', p_accounting_date = '||p_accounting_date
4411 ||', p_event_type = ' ||p_event_type);
4412 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
4413 END IF;
4414 APP_EXCEPTION.RAISE_EXCEPTION();
4415 END derive_payment_adj_event;
4416
4417
4418 /*============================================================================
4419 | FUNCTION - DERIVE_CASCADE_EVENT (PRIVATE)
4420 |
4421 | DESCRIPTION
4422 | This procedure creates the cascade events for an invoice adjusted
4423 | event. A cascade event is created when there is a corresponding normal
4424 | event that has already been accounted. This procedure creates
4428 | PRAMETERS
4425 | prepayment application adjusted, payment adjusted, payment maturity
4426 | adjusted, and payment clearing adjusted events.
4427 |
4429 | p_invoice_id: Invoice Id of the adjusted invoice
4430 | p_adj_accounting_event_id: Event ID of the invoice adjustment
4431 | p_calling_sequence: Debug information
4432 |
4433 | KNOWN ISSUES:
4434 |
4435 | NOTES:
4436 |
4437 | MODIFICATION HISTORY
4438 | Date Author Description of Change
4439 |
4440 *===========================================================================*/
4441 -- Bug 6996047. Added accounting date parameter
4442 PROCEDURE Derive_Cascade_Events(p_invoice_id IN NUMBER,
4443 p_adj_accounting_event_id IN NUMBER,
4444 p_accounting_date IN DATE,
4445 p_calling_sequence IN VARCHAR2)
4446 IS
4447
4448 -- Bug 6890810. Added historical flag condition to all the cursors
4449 -- so that there will not be any adjustment event created for the
4450 -- adjustments made to the upgraded invoices or payments.
4451
4452 CURSOR l_prepayment_applications_cur IS
4453 SELECT AID.invoice_id,
4454 AID.accounting_date,
4455 AID.invoice_line_number
4456 FROM ap_invoice_distributions AID
4457 WHERE AID.invoice_id = p_invoice_id
4458 AND AID.line_type_lookup_code = 'PREPAY'
4459 AND AID.amount < 0
4460 AND AID.posted_flag = 'Y'
4461 AND NVL(AID.reversal_flag, 'N') <> 'Y'
4462 AND AID.parent_reversal_id IS NULL
4463 AND NVL(AID.Historical_Flag, 'N') <> 'Y';
4464
4465 /* BUG # 7560346 added DISTINCT keyword */
4466 CURSOR l_payments_cur IS
4467 SELECT DISTINCT AIP.check_id,
4468 AIP.accounting_date
4469 FROM ap_invoice_payments AIP
4470 WHERE AIP.invoice_id = p_invoice_id
4471 AND AIP.posted_flag = 'Y'
4472 AND NVL(AIP.reversal_flag, 'N') <> 'Y'
4473 AND AIP.reversal_inv_pmt_id IS NULL
4474 -- Bug 6890810. Added the subquery
4475 AND NOT EXISTS (SELECT 'Upgraded Payment'
4476 FROM AP_Payment_History APH
4477 WHERE APH.Check_ID = AIP.Check_ID
4478 AND NVL(APH.Historical_Flag, 'N') = 'Y');
4479
4480
4481 /* BUG # 7560346 added DISTINCT keyword */
4482 CURSOR l_payment_clearings_cur IS
4483 SELECT DISTINCT APH.check_id,
4484 APH.accounting_date
4485 FROM ap_payment_history APH,
4486 ap_invoice_payments AIP
4487 WHERE AIP.invoice_id = p_invoice_id
4488 AND AIP.check_id = APH.check_id
4489 AND APH.transaction_type = 'PAYMENT CLEARING'
4490 AND AIP.posted_flag = 'Y'
4491 /* bug # 7604906. If the payment clearing is not
4492 accounted, payment clearing adjustment should not
4493 create */
4494 AND APH.posted_flag = 'Y'
4495 /* bug # 7604906 End */
4496 AND NVL(APH.Historical_Flag, 'N') <> 'Y' -- Bug 6890810
4497 AND NOT EXISTS( SELECT 'PAYMENT UNCLEARING EXISTS'
4498 FROM ap_payment_history APH1
4499 WHERE APH1.rev_pmt_hist_id = APH.payment_history_id
4500 AND APH1.transaction_type = 'PAYMENT UNCLEARING');
4501
4502 /* BUG # 7560346 added DISTINCT keyword */
4503 CURSOR l_payment_maturities_cur IS
4504 SELECT DISTINCT APH.check_id,
4505 APH.accounting_date
4506 FROM ap_payment_history APH,
4507 ap_invoice_payments AIP
4508 WHERE AIP.invoice_id = p_invoice_id
4509 AND AIP.check_id = APH.check_id
4510 AND APH.transaction_type = 'PAYMENT MATURITY'
4511 AND AIP.posted_flag = 'Y'
4512 AND NVL(APH.Historical_Flag, 'N') <> 'Y' -- Bug 6890810
4513 AND NOT EXISTS( SELECT 'PAYMENT MATURITY REVERSAL EXISTS'
4514 FROM ap_payment_history APH1
4515 WHERE APH1.rev_pmt_hist_id = APH.payment_history_id
4516 AND APH1.transaction_type = 'PAYMENT MATURITY REVERSAL');
4517
4518 -- add cursor for bug fix 5694577
4519 CURSOR c_get_payment_info (p_check_id NUMBER) IS
4520 SELECT ac.exchange_rate_type,
4521 ac.exchange_date,
4522 ac.exchange_rate,
4523 ac.currency_code,
4524 ac.creation_date,
4525 ac.last_updated_by,
4526 ac.org_id,
4527 ac.payment_type_flag,
4528 asp.automatic_offsets_flag
4529 FROM ap_checks_all ac,
4530 ap_system_parameters_all asp ---7209263 added automatic offsets flag
4531 WHERE ac.check_id = p_check_id
4532 AND ac.org_id=asp.org_id;
4533
4534 l_accounting_event_id NUMBER;
4535
4536 TYPE t_invoice_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
4537 TYPE t_accounting_dates IS TABLE OF DATE INDEX BY PLS_INTEGER;
4538 TYPE t_invoice_line_numbers IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
4539 TYPE t_check_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
4540
4541 l_prepay_app_invoice_ids t_invoice_ids;
4542 l_prepay_app_accounting_dates t_accounting_dates;
4543 l_prepay_app_invoice_line_num t_invoice_line_numbers;
4544 l_pay_check_ids t_check_ids;
4545 l_pay_accounting_dates t_accounting_dates;
4546 l_pay_clear_check_ids t_check_ids;
4547 l_pay_clear_accounting_dates t_accounting_dates;
4548 l_pay_mat_check_ids t_check_ids;
4552 l_exchange_rate_date DATE;
4549 l_pay_mat_accounting_dates t_accounting_dates;
4550
4551 l_exchange_rate_type VARCHAR2(30);
4553 l_exchange_rate NUMBER;
4554 l_currency_code VARCHAR2(15);
4555 l_creation_date DATE;
4556 l_last_updated_by NUMBER(15);
4557 l_adj_accounting_event_id NUMBER := p_adj_accounting_event_id;
4558 l_debug_info VARCHAR2(240);
4559 l_curr_calling_sequence VARCHAR2(2000);
4560 l_org_id NUMBER;
4561
4562 -- Logging:
4563 l_procedure_name CONSTANT VARCHAR2(30) := 'DERIVE_CASCADE_EVENTS';
4564 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4565
4566 l_event_type xla_events.event_type_code%TYPE;
4567 l_pay_type ap_checks_all.payment_type_flag%TYPE;
4568 l_automatic_offsets_flag ap_system_parameters_all.automatic_offsets_flag%TYPE; ---7209263
4569 l_bank_curr_code ap_payment_history_all.bank_currency_code%TYPE; ---7337949
4570
4571
4572 BEGIN
4573
4574 l_curr_calling_sequence :=
4575 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.DERIVE_CASCADE_EVENTS';
4576
4577 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4578
4579 l_log_msg := 'Begin of procedure '||l_procedure_name;
4580 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4581 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4582 G_MODULE_NAME || l_procedure_name,
4583 l_log_msg);
4584 END IF;
4585
4586 l_log_msg := 'Cascade the prepayment application events';
4587 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4588 l_log_msg := 'Cascade the prepayment application events';
4589 FND_LOG.STRING(G_LEVEL_STATEMENT,
4590 G_MODULE_NAME || l_procedure_name,
4591 l_log_msg);
4592 END IF;
4593
4594 OPEN l_prepayment_applications_cur;
4595 LOOP
4596
4597 FETCH l_prepayment_applications_cur
4598 BULK COLLECT INTO
4599 l_prepay_app_invoice_ids,
4600 l_prepay_app_accounting_dates,
4601 l_prepay_app_invoice_line_num
4602 LIMIT 1000;
4603
4604 FOR i IN 1 ..l_prepay_app_invoice_ids.count LOOP
4605
4606 l_log_msg := 'Before calling procedure create_invoice_event';
4607 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4608 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4609 G_MODULE_NAME || l_procedure_name,
4610 l_log_msg);
4611 END IF;
4612
4613 l_log_msg := 'Before calling procedure create_invoice_event';
4614 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4615 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4616 G_MODULE_NAME || l_procedure_name,
4617 l_log_msg);
4618 END IF;
4619
4620 l_accounting_event_id := create_invoice_event
4621 (p_event_type => PREPAY_APP_ADJUSTED_TYPE,
4622 p_invoice_id => l_prepay_app_invoice_ids(i),
4623 p_event_date => p_accounting_date, -- Bug 6996047
4624 p_calling_sequence => l_curr_calling_sequence);
4625
4626 l_log_msg := 'After calling procedure create_invoice_event';
4627 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4628 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4629 G_MODULE_NAME || l_procedure_name,
4630 l_log_msg);
4631 END IF;
4632
4633 l_log_msg := 'Before calling procedure Insert_Prepayment_Header';
4634 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4635 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4636 G_MODULE_NAME || l_procedure_name,
4637 l_log_msg);
4638 END IF;
4639
4640 Insert_Prepayment_Header
4641 (p_invoice_id => l_prepay_app_invoice_ids(i),
4642 p_invoice_line_number => l_prepay_app_invoice_line_num(i),
4643 p_accounting_event_id => l_accounting_event_id,
4644 p_accounting_date => p_accounting_date, -- Bug 6996047
4645 p_invoice_adjustment_id => l_adj_accounting_event_id,
4646 p_calling_sequence => l_curr_calling_sequence);
4647
4648 l_log_msg := 'After calling procedure Insert_prepayment_Header';
4649 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4650 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4651 G_MODULE_NAME || l_procedure_name,
4652 l_log_msg);
4653 END IF;
4654 END LOOP;
4655
4656 EXIT WHEN l_prepayment_applications_cur%NOTFOUND;
4657 END LOOP;
4658 CLOSE l_prepayment_applications_cur;
4659
4660
4661 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4662 l_log_msg := 'cascade the payment events';
4663 FND_LOG.STRING(G_LEVEL_STATEMENT,
4664 G_MODULE_NAME || l_procedure_name,
4665 l_log_msg);
4666 END IF;
4667
4668
4669 OPEN l_payments_cur;
4670 LOOP
4671
4672 FETCH l_payments_cur
4673 BULK COLLECT INTO
4674 l_pay_check_ids,
4675 l_pay_accounting_dates
4676 LIMIT 1000;
4677
4678 FOR i IN 1 .. l_pay_check_ids.count LOOP
4679
4683 G_MODULE_NAME || l_procedure_name,
4680 l_log_msg := 'Before calling procedure create_payment_event';
4681 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4682 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4684 l_log_msg);
4685 END IF;
4686
4687 -- bug fix 5694577
4688 OPEN c_get_payment_info(l_pay_check_ids(i));
4689 FETCH c_get_payment_info
4690 INTO l_exchange_rate_type,
4691 l_exchange_rate_date,
4692 l_exchange_rate,
4693 l_currency_code,
4694 l_creation_date,
4695 l_last_updated_by,
4696 l_org_id,
4697 l_pay_type,
4698 l_automatic_offsets_flag; ---7209263
4699 CLOSE c_get_payment_info;
4700
4701 IF (l_pay_type = 'R') THEN
4702 l_event_type := REFUND_ADJUSTED_TYPE;
4703 ELSE
4704 l_event_type := PAYMENT_ADJUSTED_TYPE;
4705 END IF;
4706
4707
4708 l_accounting_event_id :=
4709 create_payment_event
4710 ( p_event_type => l_event_type,
4711 p_check_id => l_pay_check_ids(i),
4712 p_event_date => p_accounting_date, -- Bug 6996047
4713 p_calling_sequence => l_curr_calling_sequence
4714 );
4715
4716 l_log_msg := 'After calling procedure create_payment_event';
4717 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4718 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4719 G_MODULE_NAME || l_procedure_name,
4720 l_log_msg);
4721 END IF;
4722
4723 l_log_msg := 'Before AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
4724 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4725 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4726 G_MODULE_NAME || l_procedure_name,
4727 l_log_msg);
4728 END IF;
4729
4730 AP_RECONCILIATION_PKG.insert_payment_history
4731 (
4732 x_check_id => l_pay_check_ids(i),
4733 x_transaction_type => l_event_type ,
4734 x_accounting_date => p_accounting_date, -- Bug 6996047
4735 x_trx_bank_amount => NULL,
4736 x_errors_bank_amount => NULL,
4737 x_charges_bank_amount => NULL,
4738 x_bank_currency_code => NULL,
4739 x_bank_to_base_xrate_type => NULL,
4740 x_bank_to_base_xrate_date => NULL,
4741 x_bank_to_base_xrate => NULL,
4742 x_trx_pmt_amount => 0,
4743 x_errors_pmt_amount => NULL,
4744 x_charges_pmt_amount => NULL,
4745 x_pmt_currency_code => l_currency_code,
4746 x_pmt_to_base_xrate_type => l_exchange_rate_type,
4747 x_pmt_to_base_xrate_date => l_exchange_rate_date,
4748 x_pmt_to_base_xrate => l_exchange_rate,
4749 x_trx_base_amount => 0,
4750 x_errors_base_amount => NULL,
4751 x_charges_base_amount => NULL,
4752 x_matched_flag => NULL,
4753 x_rev_pmt_hist_id => NULL,
4754 x_org_id => l_org_id, -- bug 4578865
4755 x_creation_date => SYSDATE,
4756 x_created_by => l_last_updated_by,
4757 x_last_update_date => SYSDATE,
4758 x_last_updated_by => l_last_updated_by,
4759 x_last_update_login => l_last_updated_by,
4760 x_program_update_date => NULL,
4761 x_program_application_id => NULL,
4762 x_program_id => NULL,
4763 x_request_id => NULL,
4764 x_calling_sequence => l_curr_calling_sequence,
4765 x_accounting_event_id => l_accounting_event_id,
4766 x_invoice_adjustment_event_id => l_adj_accounting_event_id -- bug fix 5694577
4767 );
4768
4769 l_log_msg := 'After AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
4770 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4771 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4772 G_MODULE_NAME || l_procedure_name,
4773 l_log_msg);
4774 END IF;
4775
4776
4777 END LOOP;
4778 EXIT WHEN l_payments_cur%NOTFOUND;
4779 END LOOP;
4780 CLOSE l_payments_cur;
4781
4782 l_log_msg := 'Cascade the payment clearing events';
4783 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4784 FND_LOG.STRING(G_LEVEL_STATEMENT,
4785 G_MODULE_NAME || l_procedure_name,
4786 l_log_msg);
4787 END IF;
4788
4789 OPEN l_payment_clearings_cur;
4790 LOOP
4791
4792 FETCH l_payment_clearings_cur
4793 BULK COLLECT INTO
4794 l_pay_clear_check_ids,
4795 l_pay_clear_accounting_dates
4796 LIMIT 1000;
4797
4798 FOR i IN 1 .. l_pay_clear_check_ids.count LOOP
4799
4800 l_log_msg := 'Before calling procedure create_payment_event';
4801 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4802 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4803 G_MODULE_NAME || l_procedure_name,
4804 l_log_msg);
4805 END IF;
4806 /* commented here for bug 7607184
4807 ---placed here due to 7209263
4808 SELECT AC.exchange_rate_type,
4812 AC.last_updated_by,
4809 AC.exchange_date,
4810 AC.exchange_rate,
4811 AC.creation_date,
4813 AC.org_id,
4814 asp.automatic_offsets_flag
4815 INTO l_exchange_rate_type,
4816 l_exchange_rate_date,
4817 l_exchange_rate,
4818 l_creation_date,
4819 l_last_updated_by,
4820 l_org_id,
4821 l_automatic_offsets_flag --7209263
4822 FROM AP_CHECKS AC, ap_system_parameters_all asp
4823 WHERE AC.check_id = l_pay_clear_check_ids(i) --bug 7278341 l_pay_check_ids(i)
4824 AND AC.org_id=asp.org_id;
4825 */
4826 --bug 7607184
4827 OPEN c_get_payment_info(l_pay_clear_check_ids(i));
4828 FETCH c_get_payment_info
4829 INTO l_exchange_rate_type,
4830 l_exchange_rate_date,
4831 l_exchange_rate,
4832 l_currency_code,
4833 l_creation_date,
4834 l_last_updated_by,
4835 l_org_id,
4836 l_pay_type,
4837 l_automatic_offsets_flag;
4838 CLOSE c_get_payment_info;
4839
4840 ---added to pick bank_currency_code from payment clearing for bug 7337949
4841
4842 SELECT aph.bank_currency_code
4843 INTO l_bank_curr_code
4844 FROM ap_payment_history_all APH
4845 WHERE APH.check_id = l_pay_clear_check_ids(i)
4846 AND APH.transaction_type = 'PAYMENT CLEARING'
4847 --added for bug 7614505
4848 AND NOT EXISTS( SELECT 'PAYMENT UNCLEARING EXISTS'
4849 FROM ap_payment_history_all APH1
4850 WHERE APH1.rev_pmt_hist_id = APH.payment_history_id
4851 AND APH1.transaction_type = 'PAYMENT UNCLEARING'
4852 AND APH1.check_id = APH.check_id);
4853
4854 ------7337949 ends
4855
4856
4857
4858 l_accounting_event_id :=
4859 create_payment_event
4860 ( p_event_type => PAYMENT_CLEARING_ADJUSTED_TYPE,
4861 p_check_id => l_pay_clear_check_ids(i),
4862 p_event_date => p_accounting_date, -- Bug 6996047
4863 p_calling_sequence => l_curr_calling_sequence);
4864
4865 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4866 l_log_msg := 'After calling procedure create_payment_event';
4867 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4868 G_MODULE_NAME || l_procedure_name,
4869 l_log_msg);
4870 END IF;
4871
4872 ----commented as placed above due to 7209263
4873 /* SELECT exchange_rate_type,
4874 exchange_date,
4875 exchange_rate,
4876 creation_date,
4877 last_updated_by,
4878 org_id
4879 INTO l_exchange_rate_type,
4880 l_exchange_rate_date,
4881 l_exchange_rate,
4882 l_creation_date,
4883 l_last_updated_by,
4884 l_org_id
4885 FROM AP_CHECKS AC
4886 WHERE AC.check_id = l_pay_check_ids(i);
4887 */
4888
4889
4890 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4891 l_log_msg := 'Before AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
4892 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4893 G_MODULE_NAME || l_procedure_name,
4894 l_log_msg);
4895 END IF;
4896
4897
4898 AP_RECONCILIATION_PKG.insert_payment_history
4899 (
4900 x_check_id => l_pay_clear_check_ids(i),
4901 x_transaction_type => 'PAYMENT CLEARING ADJUSTED',
4902 x_accounting_date => p_accounting_date, -- Bug 6996047
4903 x_trx_bank_amount => NULL,
4904 x_errors_bank_amount => NULL,
4905 x_charges_bank_amount => NULL,
4906 x_bank_currency_code => l_bank_curr_code,
4907 x_bank_to_base_xrate_type => NULL,
4908 x_bank_to_base_xrate_date => NULL,
4909 x_bank_to_base_xrate => NULL,
4910 x_trx_pmt_amount => 0,
4911 x_errors_pmt_amount => NULL,
4912 x_charges_pmt_amount => NULL,
4913 x_pmt_currency_code => l_currency_code,
4914 x_pmt_to_base_xrate_type => l_exchange_rate_type,
4915 x_pmt_to_base_xrate_date => l_exchange_rate_date,
4916 x_pmt_to_base_xrate => l_exchange_rate,
4917 x_trx_base_amount => 0,
4918 x_errors_base_amount => NULL,
4919 x_charges_base_amount => NULL,
4920 x_matched_flag => NULL,
4921 x_rev_pmt_hist_id => NULL,
4922 x_org_id => l_org_id, -- bug 4578865
4923 x_creation_date => SYSDATE,
4924 x_created_by => l_last_updated_by,
4925 x_last_update_date => SYSDATE,
4926 x_last_updated_by => l_last_updated_by,
4927 x_last_update_login => l_last_updated_by,
4928 x_program_update_date => NULL,
4929 x_program_application_id => NULL,
4930 x_program_id => NULL,
4931 x_request_id => NULL,
4932 x_calling_sequence => l_curr_calling_sequence,
4933 x_accounting_event_id => l_accounting_event_id,
4934 x_invoice_adjustment_event_id => l_adj_accounting_event_id --bug6710016
4938 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4935 );
4936
4937 l_log_msg := 'After AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY ';
4939 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4940 G_MODULE_NAME || l_procedure_name,
4941 l_log_msg);
4942 END IF;
4943
4944
4945 END LOOP;
4946 EXIT WHEN l_payment_clearings_cur%NOTFOUND;
4947 END LOOP;
4948 CLOSE l_payment_clearings_cur;
4949
4950
4951 l_log_msg := 'Cascade the payment maturity events';
4952 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4953 FND_LOG.STRING(G_LEVEL_STATEMENT,
4954 G_MODULE_NAME || l_procedure_name,
4955 l_log_msg);
4956 END IF;
4957
4958 OPEN l_payment_maturities_cur;
4959 LOOP
4960
4961 FETCH l_payment_maturities_cur
4962 BULK COLLECT INTO
4963 l_pay_mat_check_ids,
4964 l_pay_mat_accounting_dates
4965 LIMIT 1000;
4966
4967 FOR i IN 1 .. l_pay_mat_check_ids.count LOOP
4968
4969
4970 SELECT AC.exchange_rate_type,
4971 AC.exchange_date,
4972 AC.exchange_rate,
4973 AC.creation_date,
4974 AC.last_updated_by,
4975 AC.org_id,
4976 asp.automatic_offsets_flag
4977 INTO l_exchange_rate_type,
4978 l_exchange_rate_date,
4979 l_exchange_rate,
4980 l_creation_date,
4981 l_last_updated_by,
4982 l_org_id,
4983 l_automatic_offsets_flag --7209263
4984 FROM AP_CHECKS AC, ap_system_parameters_all asp
4985 WHERE AC.check_id = l_pay_mat_check_ids(i)--bug 7278341 l_pay_check_ids(i)
4986 AND AC.org_id=asp.org_id;
4987
4988
4989 l_log_msg := 'Before calling procedure create_payment_event';
4990 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4991 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4992 G_MODULE_NAME || l_procedure_name,
4993 l_log_msg);
4994 END IF;
4995
4996 l_accounting_event_id :=
4997 create_payment_event
4998 ( p_event_type => PAYMENT_MATURITY_ADJUSTED_TYPE,
4999 p_check_id => l_pay_mat_check_ids(i),
5000 p_event_date => p_accounting_date, -- Bug 6996047
5001 p_calling_sequence => l_curr_calling_sequence);
5002
5003 l_log_msg := 'After calling procedure create_payment_event';
5004 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5005 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5006 G_MODULE_NAME || l_procedure_name,
5007 l_log_msg);
5008 END IF;
5009
5010 ----Commented due to 7209263
5011 /* SELECT exchange_rate_type,
5012 exchange_date,
5013 exchange_rate,
5014 creation_date,
5015 last_updated_by,
5016 org_id
5017 INTO l_exchange_rate_type,
5018 l_exchange_rate_date,
5019 l_exchange_rate,
5020 l_creation_date,
5021 l_last_updated_by,
5022 l_org_id
5023 FROM AP_CHECKS AC
5024 WHERE AC.check_id = l_pay_check_ids(i);
5025 */
5026
5027 l_log_msg := 'Before AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
5028 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5029 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5030 G_MODULE_NAME || l_procedure_name,
5031 l_log_msg);
5032 END IF;
5033
5034 AP_RECONCILIATION_PKG.insert_payment_history
5035 (
5036 x_check_id => l_pay_mat_check_ids(i),
5037 x_transaction_type => 'PAYMENT MATURITY ADJUSTED',
5038 x_accounting_date => p_accounting_date, -- Bug 6996047
5039 x_trx_bank_amount => NULL,
5040 x_errors_bank_amount => NULL,
5041 x_charges_bank_amount => NULL,
5042 x_bank_currency_code => NULL,
5043 x_bank_to_base_xrate_type => NULL,
5044 x_bank_to_base_xrate_date => NULL,
5045 x_bank_to_base_xrate => NULL,
5046 x_trx_pmt_amount => 0,
5047 x_errors_pmt_amount => NULL,
5048 x_charges_pmt_amount => NULL,
5049 x_pmt_currency_code => l_currency_code,
5050 x_pmt_to_base_xrate_type => l_exchange_rate_type,
5051 x_pmt_to_base_xrate_date => l_exchange_rate_date,
5052 x_pmt_to_base_xrate => l_exchange_rate,
5053 x_trx_base_amount => 0,
5054 x_errors_base_amount => NULL,
5055 x_charges_base_amount => NULL,
5056 x_matched_flag => NULL,
5057 x_rev_pmt_hist_id => NULL,
5058 x_org_id => l_org_id, -- bug 4578865
5059 x_creation_date => SYSDATE,
5060 x_created_by => l_last_updated_by,
5061 x_last_update_date => SYSDATE,
5062 x_last_updated_by => l_last_updated_by,
5063 x_last_update_login => l_last_updated_by,
5064 x_program_update_date => NULL,
5065 x_program_application_id => NULL,
5069 x_accounting_event_id => l_accounting_event_id,
5066 x_program_id => NULL,
5067 x_request_id => NULL,
5068 x_calling_sequence => l_curr_calling_sequence,
5070 x_invoice_adjustment_event_id => l_adj_accounting_event_id --bug6710016
5071 );
5072
5073 l_log_msg := 'After AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
5074 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5075 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5076 G_MODULE_NAME || l_procedure_name,
5077 l_log_msg);
5078 END IF;
5079
5080 END LOOP;
5081
5082 EXIT WHEN l_payment_maturities_cur%NOTFOUND;
5083 END LOOP;
5084 CLOSE l_payment_maturities_cur;
5085
5086 l_log_msg := 'End of procedure '||l_procedure_name;
5087 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5088 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5089 G_MODULE_NAME || l_procedure_name,
5090 l_log_msg);
5091 END IF;
5092
5093 EXCEPTION
5094 WHEN OTHERS THEN
5095 IF (l_prepayment_applications_cur%ISOPEN) THEN
5096 CLOSE l_prepayment_applications_cur;
5097 END IF;
5098 IF (l_payments_cur%ISOPEN) THEN
5099 CLOSE l_payments_cur;
5100 END IF;
5101 IF (l_payment_clearings_cur%ISOPEN) THEN
5102 CLOSE l_payment_clearings_cur;
5103 END IF;
5104 IF (l_payment_maturities_cur%ISOPEN) THEN
5105 CLOSE l_payment_maturities_cur;
5106 END IF;
5107 IF (SQLCODE <> -20001) THEN
5108 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
5109 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
5110 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
5111 l_curr_calling_sequence);
5112 FND_MESSAGE.SET_TOKEN('PARAMETERS',
5113 'p_invoice_id = '||p_invoice_id
5114 ||', p_adj_accounting_event_id = '||p_adj_accounting_event_id);
5115 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
5116 END IF;
5117 APP_EXCEPTION.RAISE_EXCEPTION();
5118 END derive_cascade_events;
5119
5120
5121
5122 /*============================================================================
5123 | PROCEDURE - DERIVE_INVOICE_CANCEL_EVENTs(PRIVATE)
5124 |
5125 | DESCRIPTION
5126 | This procedure creates invoice cancellation events using the GL date
5127 | of the invoice distributions. If there are multiple invoice
5128 | distributions with different GL dates, multiple Invoice Cancellation
5129 | events will be created.
5130 |
5131 | PRAMETERS
5132 | p_invoice_id IN: Invoice Id of the cancelled invoice
5133 | p_calling_sequence IN: Debug information
5134 |
5135 | KNOWN ISSUES:
5136 |
5137 | NOTES:
5138 |
5139 | MODIFICATION HISTORY
5140 | Date Author Description of Change
5141 |
5142 *===========================================================================*/
5143 PROCEDURE derive_invoice_cancel_events (
5144 p_invoice_id IN NUMBER,
5145 p_calling_sequence IN VARCHAR2)
5146 IS
5147
5148 CURSOR inv_cancel_event_dists IS
5149 SELECT accounting_date
5150 FROM ap_invoice_distributions
5151 WHERE invoice_id = p_invoice_id
5152 AND awt_invoice_payment_id IS NULL
5153 AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
5154 AND accounting_event_id IS NULL
5155 AND cancellation_flag = 'Y'
5156 GROUP BY accounting_date
5157 ORDER BY accounting_date;
5158
5159 CURSOR no_action_prepay_dists IS
5160 SELECT accounting_event_id
5161 FROM ap_invoice_distributions
5162 WHERE invoice_id = p_invoice_id
5163 AND line_type_lookup_code = 'PREPAY'
5164 AND amount <= 0;
5165
5166 -- bug 4748638
5167 CURSOR non_preapy_dist_grp_count IS
5168 SELECT accounting_date
5169 FROM ap_invoice_distributions
5170 WHERE invoice_id = p_invoice_id
5171 AND line_type_lookup_code <> 'PREPAY'
5172 AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
5173 GROUP BY accounting_date;
5174
5175
5176 -- bug fix for 4881719
5177 CURSOR all_unaccounted_dists_cur IS
5178 SELECT accounting_event_id
5179 FROM ap_invoice_distributions
5180 WHERE invoice_id = p_invoice_id
5181 AND posted_flag <> 'Y';
5182
5183
5184 TYPE t_accounting_event_dates IS TABLE OF DATE INDEX BY PLS_INTEGER;
5185 l_accounting_event_dates t_accounting_event_dates;
5186 l_dist_gl_date_list t_accounting_event_dates;
5187
5188 TYPE t_accounting_event_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
5189 l_accounting_event_ids t_accounting_event_ids;
5190 l_unaccounted_event_id_list t_accounting_event_ids;
5191
5192
5193 l_event_type VARCHAR2(30);
5194 l_event_class VARCHAR2(30);
5195 l_accounting_event_id NUMBER; --bug 4352723
5196 l_processed_dists NUMBER;
5197 l_dist_dates NUMBER;
5198
5199 l_legal_entity_id NUMBER(15);
5200 l_ledger_id NUMBER(15);
5201 l_org_id NUMBER(15);
5202 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
5203 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
5204 l_diff_gl_date_dists NUMBER := 0;
5205 l_transaction_date AP_INVOICES_ALL.invoice_date%TYPE;
5206
5210 l_procedure_name CONSTANT VARCHAR2(30) := 'DERIVE_INVOICE_CANCEL_EVENT';
5207 l_curr_calling_sequence VARCHAR2(2000);
5208
5209 -- Logging:
5211 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5212
5213 BEGIN
5214
5215 l_curr_calling_sequence := p_calling_sequence ||
5216 ' -> AP_ACCOUNTING_EVENTS_PKG.DERIVE_INVOICE_CANCEL_EVENT';
5217
5218 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5219
5220 l_log_msg := 'Begin of procedure '||l_procedure_name;
5221 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5222 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5223 G_MODULE_NAME || l_procedure_name,
5224 l_log_msg);
5225 END IF;
5226
5227 SELECT DECODE
5228 ( AI.invoice_type_lookup_code,
5229 'CREDIT', CREDIT_MEMO_CANCELLED_TYPE,
5230 'DEBIT', DEBIT_MEMO_CANCELLED_TYPE,
5231 'PREPAYMENT', PREPAYMENT_CANCELLED_TYPE,
5232 INVOICE_CANCELLED_TYPE
5233 ) event_type,
5234 DECODE
5235 ( AI.invoice_type_lookup_code,
5236 'CREDIT', CREDIT_MEMOS_CLASS,
5237 'DEBIT', DEBIT_MEMOS_CLASS,
5238 'PREPAYMENT', PREPAYMENTS_CLASS,
5239 INVOICES_CLASS
5240 ) event_class
5241 INTO l_event_type,
5242 l_event_class
5243 FROM ap_invoices_all AI
5244 WHERE AI.invoice_id = p_invoice_id;
5245
5246 l_log_msg := 'Before calling procedure create_invoice_event';
5247 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5248 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5249 G_MODULE_NAME || l_procedure_name,
5250 l_log_msg);
5251 END IF;
5252
5253 OPEN inv_cancel_event_dists;
5254 LOOP
5255
5256 FETCH inv_cancel_event_dists
5257 BULK COLLECT INTO
5258 l_accounting_event_dates
5259 LIMIT 1000;
5260
5261 FOR i IN 1 ..l_accounting_event_dates.count LOOP
5262 l_log_msg := 'Before calling procedure create_invoice_event';
5263 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5264 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5265 G_MODULE_NAME || l_procedure_name,
5266 l_log_msg);
5267 END IF;
5268
5269 l_accounting_event_ids(i) :=
5270 create_invoice_event
5271 ( p_event_type => l_event_type,
5272 p_invoice_id => p_invoice_id,
5273 p_event_date => l_accounting_event_dates(i),
5274 p_calling_sequence => l_curr_calling_sequence
5275 );
5276
5277 l_log_msg := 'After calling procedure create_invoice_event';
5278 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5279 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5280 G_MODULE_NAME || l_procedure_name,
5281 l_log_msg);
5282 END IF;
5283
5284 END LOOP;
5285
5286 FORALL i IN 1 .. l_accounting_event_dates.count
5287 UPDATE ap_invoice_distributions
5288 SET accounting_event_id = l_accounting_event_ids(i)
5289 WHERE invoice_id = p_invoice_id
5290 AND accounting_date = l_accounting_event_dates(i)
5291 AND awt_invoice_payment_id IS NULL
5292 AND line_type_lookup_code <> 'PREPAY'
5293 AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
5294 AND accounting_event_id IS NULL
5295 AND cancellation_flag = 'Y';
5296
5297 --------------------------------------------------
5298 -- bug 5525657
5299 -- We need to stamp the accounting event id for
5300 -- self assessed tax distributions
5301 --------------------------------------------------
5302
5303 FORALL i IN 1 .. l_accounting_event_dates.count
5304 UPDATE ap_self_assessed_tax_dist_all
5305 SET accounting_event_id = l_accounting_event_ids(i)
5306 WHERE invoice_id = p_invoice_id
5307 AND accounting_date = l_accounting_event_dates(i)
5308 AND awt_invoice_payment_id IS NULL
5309 AND line_type_lookup_code <> 'PREPAY'
5310 AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
5311 AND accounting_event_id IS NULL
5312 AND cancellation_flag = 'Y';
5313
5314 EXIT WHEN inv_cancel_event_dists%NOTFOUND;
5315 END LOOP;
5316 CLOSE inv_cancel_event_dists;
5317
5318 -- Bug 4927664 Remove the complete logic trying to figure out
5319 -- if we need to update events to No Action.
5320
5321 l_log_msg :='End of procedure '||l_procedure_name;
5322 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5323 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5324 G_MODULE_NAME || l_procedure_name,
5325 l_log_msg);
5326 END IF;
5327
5328 EXCEPTION
5329 WHEN OTHERS THEN
5330 IF (inv_cancel_event_dists%ISOPEN) THEN
5331 CLOSE inv_cancel_event_dists;
5332 END IF;
5333 IF (no_action_prepay_dists%ISOPEN) THEN
5334 CLOSE no_action_prepay_dists;
5335 END IF;
5336 IF (non_preapy_dist_grp_count%ISOPEN) THEN
5337 CLOSE non_preapy_dist_grp_count;
5338 END IF;
5339 IF (all_unaccounted_dists_cur%ISOPEN) THEN
5340 CLOSE all_unaccounted_dists_cur;
5341 END IF;
5342 IF (SQLCODE <> -20001) THEN
5343 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
5344 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
5345 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
5349 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
5346 l_curr_calling_sequence);
5347 FND_MESSAGE.SET_TOKEN('PARAMETERS',
5348 'p_invoice_id = '||p_invoice_id);
5350 END IF;
5351 APP_EXCEPTION.RAISE_EXCEPTION();
5352 END derive_invoice_cancel_events;
5353
5354 /*============================================================================
5355 | PROCEDURE - NO_ACTION_PMT_EVENT_UPDATE (PRIVATE)
5356 |
5357 | DESCRIPTION
5358 | This procedure is used to create 'No Action' event in SLA.'No Action'
5359 | status indicate that it is not necessary to create any accounting for a
5360 | given event. 'No Action' event will be picked up by the SLA accounting
5361 | process, but no accounting lines will be created.
5362 |
5363 | PRAMETERS
5364 | p_check_id: Check ID
5365 | p_event_type_code: Event Type
5366 | p_accounting_date: Accounting date
5367 | p_accounting_event_id: Accounting event whose status will be
5368 | stamped as 'No Action'
5369 | p_calling_sequence: Debug information
5370 |
5371 | KNOWN ISSUES:
5372 |
5373 | NOTES:
5374 |
5375 | MODIFICATION HISTORY
5376 | Date Author Description of Change
5377 |
5378 *===========================================================================*/
5379 PROCEDURE no_action_pmt_event_update(
5380 p_check_id IN NUMBER,
5381 p_event_type_code IN VARCHAR2,
5382 p_accounting_date IN DATE,
5383 p_accounting_event_id IN NUMBER,
5384 p_calling_sequence IN VARCHAR2)
5385 IS
5386
5387 -- Bug 4748638
5388 CURSOR aip_event_id_count IS
5389 SELECT accounting_event_id
5390 FROM ap_invoice_payments
5391 WHERE check_id = p_check_id
5392 GROUP BY accounting_event_id;
5393
5394 TYPE event_id_tab_Type IS TABLE OF
5395 ap_invoice_payments.accounting_event_id%TYPE;
5396 l_event_id_list event_id_tab_Type;
5397
5398 l_legal_entity_id NUMBER(15);
5399 l_ledger_id NUMBER(15);
5400 l_org_id NUMBER(15);
5401 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
5402 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
5403
5404 l_processed_events NUMBER;
5405 l_event_count NUMBER;
5406 l_accounting_date DATE;
5407 l_event_class VARCHAR2(30);
5408
5409 l_curr_calling_sequence VARCHAR2(2000);
5410
5411 -- Logging:
5412 l_procedure_name CONSTANT VARCHAR2(30) := 'NO_ACTION_PMT_EVENT_UPDATE';
5413 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5414
5415 BEGIN
5416
5417 l_curr_calling_sequence := p_calling_sequence ||
5418 ' -> AP_ACCOUNTING_EVENTS_PKG.NO_ACTION_PMT_EVENT_UPDATE';
5419
5420 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5421
5422 l_log_msg :='Begin of procedure '||l_procedure_name;
5423 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5424 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5425 G_MODULE_NAME || l_procedure_name,
5426 l_log_msg);
5427 END IF;
5428
5429 l_log_msg :='Before calling procedure get_payment_info';
5430 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5431 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5432 G_MODULE_NAME || l_procedure_name,
5433 l_log_msg);
5434 END IF;
5435
5436 get_payment_info
5437 ( p_check_id => p_check_id,
5438 p_org_id => l_org_id, -- OUT
5439 p_legal_entity_id => l_legal_entity_id, -- OUT
5440 p_ledger_id => l_ledger_id, -- OUT
5441 p_calling_sequence => l_curr_calling_sequence
5442 );
5443
5444 l_log_msg :='Procedure get_payment_info executed';
5445 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5446 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5447 G_MODULE_NAME || l_procedure_name,
5448 l_log_msg);
5449 END IF;
5450
5451 l_log_msg :='Before calling procedure get_event_security_context';
5452 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5453 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5454 G_MODULE_NAME || l_procedure_name,
5455 l_log_msg);
5456 END IF;
5457
5458 l_event_security_context :=
5459 get_event_security_context
5460 ( p_org_id => l_org_id,
5461 p_calling_sequence => l_curr_calling_sequence
5462 );
5463
5464 l_log_msg :='After calling procedure get_event_security_context';
5465 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5466 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5467 G_MODULE_NAME || l_procedure_name,
5468 l_log_msg);
5469 END IF;
5470
5471 l_log_msg :='Before calling procedure get_payment_event_source_info';
5472 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5473 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5474 G_MODULE_NAME || l_procedure_name,
5475 l_log_msg);
5476 END IF;
5477
5478 l_event_source_info :=
5479 get_payment_event_source_info
5480 ( p_legal_entity_id => l_legal_entity_id,
5481 p_ledger_id => l_ledger_id,
5482 p_check_id => p_check_id,
5483 p_calling_sequence => l_curr_calling_sequence
5484 );
5485
5489 G_MODULE_NAME || l_procedure_name,
5486 l_log_msg :='After calling procedure get_payment_event_source_info';
5487 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5488 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5490 l_log_msg);
5491 END IF;
5492
5493 IF (p_event_type_code IN (PAYMENT_CANCELLED_TYPE, REFUND_CANCELLED_TYPE)) THEN
5494 IF (p_event_type_code = PAYMENT_CANCELLED_TYPE) THEN
5495 l_event_class := PAYMENTS_CLASS;
5496 ELSE
5497 l_event_class := REFUNDS_CLASS;
5498 END IF;
5499
5500 SELECT COUNT(*)
5501 INTO l_processed_events
5502 FROM ap_invoice_payments
5503 WHERE posted_flag = 'Y'
5504 AND check_id = p_check_id;
5505
5506 IF (l_processed_events = 0) THEN
5507
5508 -- If there is more that one event for the payment, it implies that the
5509 -- payment has been adjusted, and we do not want to update events to
5510 -- No Action
5511
5512 OPEN aip_event_id_count;
5513 FETCH aip_event_id_count
5514 BULK COLLECT INTO l_event_id_list;
5515 CLOSE aip_event_id_count;
5516
5517 IF (l_event_id_list.count <= 1) THEN
5518
5519 SELECT check_date
5520 INTO l_accounting_date
5521 FROM ap_checks
5522 WHERE check_id = p_check_id;
5523
5524 IF (l_accounting_date = p_accounting_date) THEN
5525 -- p_event_type_code is left NULL so that 'PAYMENT CREATED' and
5526 -- 'PAYMENT CANCELLED' events or 'REFUND CREATED' and
5527 -- 'REFUND CANCELLED' events will be updated.
5528
5529 l_log_msg :='Before calling P_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
5530 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5531 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5532 G_MODULE_NAME || l_procedure_name,
5533 l_log_msg);
5534 END IF;
5535
5536 AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
5537 ( p_event_source_info => l_event_source_info,
5538 p_event_class_code => l_event_class,
5539 p_event_type_code => p_event_type_code,
5540 p_event_date => l_accounting_date,
5541 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
5542 p_valuation_method => NULL,
5543 p_security_context => l_event_security_context,
5544 p_calling_sequence => l_curr_calling_sequence
5545 );
5546
5547 l_log_msg :='After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
5548 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5549 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5550 G_MODULE_NAME || l_procedure_name,
5551 l_log_msg);
5552 END IF;
5553 END IF;
5554
5555 END IF;
5556
5557 END IF;
5558
5559 ELSIF (p_event_type_code = PAYMENT_UNCLEARED_TYPE) THEN
5560
5561 /* Get the accounting date of 'clearing' payment history */
5562 SELECT APH.accounting_date
5563 INTO l_accounting_date
5564 FROM ap_payment_history APH
5565 WHERE APH.payment_history_id =
5566 (SELECT max(payment_history_id)
5567 FROM ap_payment_history APH2
5568 WHERE APH2.check_id = p_check_id
5569 AND APH2.posted_flag = 'N'
5570 AND APH2.transaction_type = 'PAYMENT CLEARING');
5571
5572 IF (l_accounting_date = p_accounting_date) THEN
5573 -- p_event_type_code is left NULL so that 'PAYMENT CLEARED' and
5574 -- 'PAYMENT UNCLEARED' events will be updated.
5575
5576 l_log_msg :='Before calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
5577 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5578 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5579 G_MODULE_NAME || l_procedure_name,
5580 l_log_msg);
5581 END IF;
5582
5583 AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
5584 ( p_event_source_info => l_event_source_info,
5585 p_event_class_code => RECONCILED_PAYMENTS_CLASS,
5586 p_event_type_code => p_event_type_code,
5587 p_event_date => l_accounting_date,
5588 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
5589 p_valuation_method => NULL,
5590 p_security_context => l_event_security_context,
5591 p_calling_sequence => l_curr_calling_sequence
5592 );
5593
5594 l_log_msg :='After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
5595 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5596 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5597 G_MODULE_NAME || l_procedure_name,
5598 l_log_msg);
5599 END IF;
5600 END IF;
5601
5602 ELSIF (p_event_type_code = PAYMENT_MATURITY_REVERSED_TYPE) THEN
5603
5604 SELECT APH.accounting_date
5605 INTO l_accounting_date
5606 FROM ap_payment_history APH
5607 WHERE payment_history_id =
5608 (SELECT max(payment_history_id)
5609 FROM ap_payment_history APH2
5610 WHERE APH2.check_id = p_check_id
5611 AND APH2.posted_flag = 'N'
5612 AND APH2.transaction_type = 'PAYMENT MATURITY');
5613
5614
5615 IF (l_accounting_date = p_accounting_date) THEN
5616
5617 l_log_msg :='Before calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
5621 l_log_msg);
5618 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5619 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5620 G_MODULE_NAME || l_procedure_name,
5622 END IF;
5623
5624 AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
5625 ( p_event_source_info => l_event_source_info,
5626 p_event_class_code => FUTURE_DATED_PAYMENTS_CLASS,
5627 p_event_type_code => p_event_type_code,
5628 p_event_date => l_accounting_date,
5629 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
5630 p_valuation_method => NULL,
5631 p_security_context => l_event_security_context,
5632 p_calling_sequence => l_curr_calling_sequence
5633 );
5634
5635 l_log_msg :='After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
5636 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5637 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5638 G_MODULE_NAME || l_procedure_name,
5639 l_log_msg);
5640 END IF;
5641 END IF;
5642
5643 END IF;
5644
5645 l_log_msg :='End of procedure '||l_procedure_name;
5646 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5647 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5648 G_MODULE_NAME || l_procedure_name,
5649 l_log_msg);
5650 END IF;
5651
5652 EXCEPTION
5653 WHEN OTHERS THEN
5654 IF (SQLCODE <> -20001) THEN
5655 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
5656 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
5657 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
5658 l_curr_calling_sequence);
5659 FND_MESSAGE.SET_TOKEN('PARAMETERS',
5660 'p_check_id = '||p_check_id
5661 ||', p_event_type_code = '||p_event_type_code
5662 ||', p_accounting_date = '||p_accounting_date
5663 ||', p_accounting_event_id = '||p_accounting_event_id);
5664 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
5665 END IF;
5666 APP_EXCEPTION.RAISE_EXCEPTION();
5667 END no_action_pmt_event_update;
5668
5669
5670 /*============================================================================
5671 | PROCEDURE - MULTI_ORG_EVENTS_SWEEP(PUBLIC)
5672 |
5673 | DESCRIPTION
5674 | This procedure is called by APXTRSWP.rdf (UPDATE_ACCTG_DATES).This
5675 | procedure is used to sweep accounting events from one accounting period
5676 | to another.
5677 |
5678 | PRAMETERS
5679 | p_ledger_id: Current ledger ID
5680 | p_period_name: Old accounting period
5681 | p_from_date: The start date of sweeping
5682 | p_to_date: The end date of the sweeping
5683 | p_sweep_to_date: The new event date
5684 | p_calling_sequence: Debug information
5685 |
5686 | KNOWN ISSUES:
5687 |
5688 | NOTES:
5689 |
5690 | MODIFICATION HISTORY
5691 | Date Author Description of Change
5692 |
5693 *===========================================================================*/
5694 PROCEDURE multi_org_events_sweep (
5695 p_ledger_id IN NUMBER,
5696 p_period_name IN VARCHAR2,
5697 p_from_date IN DATE,
5698 p_to_date IN DATE,
5699 p_sweep_to_date IN DATE,
5700 p_calling_sequence IN VARCHAR2 )
5701 IS
5702
5703 TYPE t_event_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
5704 TYPE t_invoice_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
5705 TYPE t_check_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
5706 TYPE t_org_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
5707 TYPE t_legal_entity_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
5708 TYPE t_ledger_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
5709
5710 l_event_ids t_event_ids;
5711 l_invoice_ids t_invoice_ids;
5712 l_check_ids t_check_ids;
5713 l_org_ids t_org_ids;
5714 l_legal_entity_ids t_legal_entity_ids;
5715 l_ledger_ids t_ledger_ids;
5716 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
5717 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
5718
5719 CURSOR l_aid_events_mo_cur_period IS
5720 SELECT AID.accounting_event_id,
5721 AID.invoice_id,
5722 AID.org_id,
5723 AI.legal_entity_id,
5724 AID.set_of_books_id ledger_id
5725 FROM ap_invoice_distributions_all AID,
5726 ap_invoices_all AI
5727 WHERE AP_UTILITIES_PKG.GET_CURRENT_GL_DATE(TRUNC(AID.accounting_date), aid.org_id) --bug5956469
5728 = p_period_name
5729 AND aid.posted_flag IN ('N', 'S') -- Bug 6869699
5730 AND aid.org_id = ai.org_id
5731 AND AID.org_id IN
5732 ( SELECT ASP.org_id
5733 FROM hr_organization_information OI,
5734 --hr_all_organization_units_tl LE, --bug6392886
5735 hr_all_organization_units_tl OU,
5736 ap_system_parameters_all ASP
5737 WHERE ASP.org_id = OI.organization_id
5738 AND OU.organization_id = OI.organization_id
5739 AND OI.org_information_context = 'Operating Unit Information'
5740 AND DECODE(LTRIM(OI.org_information3, '0123456789'), NULL,
5741 TO_NUMBER(OI.org_information3), NULL) = p_ledger_id
5742 --bug6392886
5743 /*AND DECODE(LTRIM(OI.org_information2, '0123456789'), NULL,
5747 --AND LE.language = USERENV('LANG')
5744 TO_NUMBER(OI.org_information2), NULL) = LE.organization_id*/
5745 AND OU.organization_id = OI.organization_id
5746 AND OU.language = USERENV('LANG')
5748 ) AND AID.invoice_id = AI.invoice_id
5749 AND AID.accounting_event_id is not NULL;--Bug6320053
5750
5751 CURSOR l_aid_events_mo_cur_no_period IS
5752 SELECT AID.accounting_event_id,
5753 AID.invoice_id,
5754 AID.org_id,
5755 AI.legal_entity_id,
5756 AID.set_of_books_id ledger_id
5757 FROM ap_invoice_distributions_all AID,
5758 ap_invoices_all AI
5759 WHERE AID.accounting_date BETWEEN p_from_date AND p_to_date
5760 AND aid.posted_flag IN ('N', 'S') -- Bug 6869699
5761 AND aid.org_id = ai.org_id
5762 AND AID.org_id IN
5763 ( SELECT ASP.org_id
5764 FROM hr_organization_information OI,
5765 --hr_all_organization_units_tl LE, --bug6392886
5766 hr_all_organization_units_tl OU,
5767 ap_system_parameters_all ASP
5768 WHERE ASP.org_id = OI.organization_id
5769 AND OU.organization_id = OI.organization_id
5770 AND OI.org_information_context = 'Operating Unit Information'
5771 AND DECODE(LTRIM(OI.org_information3, '0123456789'), NULL,
5772 TO_NUMBER(OI.org_information3), NULL) = p_ledger_id
5773 --bug6392886
5774 /*AND DECODE(LTRIM(OI.org_information2, '0123456789'), NULL,
5775 TO_NUMBER(OI.org_information2), NULL) = LE.organization_id*/
5776 AND OU.organization_id = OI.organization_id
5777 AND OU.language = USERENV('LANG')
5778 --AND LE.language = USERENV('LANG')
5779 ) AND AID.invoice_id = AI.invoice_id
5780 AND AID.accounting_event_id is not NULL ;--Bug6320053
5781
5782 CURSOR l_aph_events_mo_cur_period IS
5783 SELECT APH.accounting_event_id,
5784 APH.check_id check_id,
5785 APH.org_id,
5786 AC.legal_entity_id,
5787 ( SELECT AIP.set_of_books_id
5788 FROM ap_invoice_payments_all AIP
5789 WHERE AIP.check_id = APH.check_id
5790 AND ROWNUM = 1
5791 ) ledger_id
5792 FROM
5793 ap_payment_history_all APH,
5794 ap_checks_all AC
5795 WHERE
5796 APH.check_id = AC.check_id
5797 AND AP_UTILITIES_PKG.GET_CURRENT_GL_DATE(TRUNC(APH.accounting_date), aph.org_id) --bug5956469
5798 = p_period_name
5799 AND aph.posted_flag IN ('N', 'S') -- Bug 6869699
5800 and ac.org_id = aph.org_id
5801 AND APH.org_id IN
5802 ( SELECT ASP.org_id
5803 FROM hr_organization_information OI,
5804 --hr_all_organization_units_tl LE, --bug6392886
5805 hr_all_organization_units_tl OU,
5806 ap_system_parameters_all ASP
5807 WHERE ASP.org_id = OI.organization_id
5808 AND OU.organization_id = OI.organization_id
5809 AND OI.org_information_context = 'Operating Unit Information'
5810 AND DECODE(LTRIM(OI.org_information3,'0123456789'), NULL,
5811 TO_NUMBER(OI.org_information3), NULL) = p_ledger_id
5812 --bug6392886
5813 /*AND DECODE(LTRIM(OI.org_information2,'0123456789'), NULL,
5814 TO_NUMBER(OI.org_information2), NULL) = LE.organization_id */
5815 AND OU.organization_id = OI.organization_id
5816 AND OU.language = USERENV('LANG')
5817 --AND LE.language = USERENV('LANG')
5818 )
5819 AND APH.accounting_event_id is not NULL ;--Bug6320053
5820
5821 CURSOR l_aph_events_mo_cur_no_period IS
5822 SELECT APH.accounting_event_id,
5823 APH.check_id check_id,
5824 APH.org_id,
5825 AC.legal_entity_id,
5826 ( SELECT AIP.set_of_books_id
5827 FROM ap_invoice_payments_all AIP
5828 WHERE AIP.check_id = APH.check_id
5829 AND ROWNUM = 1
5830 ) ledger_id
5831 FROM
5832 ap_payment_history_all APH,
5833 ap_checks_all AC
5834 WHERE
5835 APH.check_id = AC.check_id
5836 AND APH.accounting_date BETWEEN p_from_date AND p_to_date
5837 AND aph.posted_flag IN ('N', 'S') -- Bug 6869699
5838 and ac.org_id = aph.org_id
5839 AND APH.org_id IN
5840 ( SELECT ASP.org_id
5841 FROM hr_organization_information OI,
5842 --hr_all_organization_units_tl LE, bug6392886
5843 hr_all_organization_units_tl OU,
5844 ap_system_parameters_all ASP
5845 WHERE ASP.org_id = OI.organization_id
5846 AND OU.organization_id = OI.organization_id
5847 AND OI.org_information_context = 'Operating Unit Information'
5848 AND DECODE(LTRIM(OI.org_information3,'0123456789'), NULL,
5849 TO_NUMBER(OI.org_information3), NULL) = p_ledger_id
5850 --bug6392886
5851 /*AND DECODE(LTRIM(OI.org_information2,'0123456789'), NULL,
5852 TO_NUMBER(OI.org_information2), NULL) = LE.organization_id*/
5853 AND OU.organization_id = OI.organization_id
5854 AND OU.language = USERENV('LANG')
5855 --AND LE.language = USERENV('LANG')
5856 )
5857 AND APH.accounting_event_id is not NULL ;--Bug6320053
5858
5859 l_curr_calling_sequence VARCHAR2(2000);
5860
5861 -- Logging:
5865 BEGIN
5862 l_procedure_name CONSTANT VARCHAR2(30) := 'MULTI_ORG_EVENTS_SWEEP';
5863 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5864
5866
5867 l_curr_calling_sequence :=
5868 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.MULTI_ORG_EVENTS_SWEEP';
5869
5870 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5871
5872 l_log_msg :='Begin of procedure '||l_procedure_name;
5873 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5874 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5875 G_MODULE_NAME || l_procedure_name,
5876 l_log_msg);
5877 END IF;
5878
5879 if (p_period_name is null) then
5880 OPEN l_aid_events_mo_cur_no_period;
5881 else
5882 OPEN l_aid_events_mo_cur_period;
5883 end if;
5884
5885 LOOP
5886
5887 if (p_period_name is null) then
5888 FETCH l_aid_events_mo_cur_no_period
5889 BULK COLLECT INTO
5890 l_event_ids,
5891 l_invoice_ids,
5892 l_org_ids,
5893 l_legal_entity_ids,
5894 l_ledger_ids
5895 LIMIT 1000;
5896 else
5897 FETCH l_aid_events_mo_cur_period
5898 BULK COLLECT INTO
5899 l_event_ids,
5900 l_invoice_ids,
5901 l_org_ids,
5902 l_legal_entity_ids,
5903 l_ledger_ids
5904 LIMIT 1000;
5905 end if;
5906
5907 FOR i IN 1 .. l_event_ids.count LOOP
5908 l_log_msg :='Before calling procedure get_event_security_context';
5909 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5910 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5911 G_MODULE_NAME || l_procedure_name,
5912 l_log_msg);
5913 END IF;
5914
5915 l_event_security_context :=
5916 get_event_security_context
5917 ( p_org_id => l_org_ids(i),
5918 p_calling_sequence => l_curr_calling_sequence
5919 );
5920
5921 l_log_msg :='After calling procedure get_event_security_context';
5922 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5923 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5924 G_MODULE_NAME || l_procedure_name,
5925 l_log_msg);
5926 END IF;
5927
5928 l_log_msg :='Before calling procedure get_invoice_event_source_info';
5929 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5930 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5931 G_MODULE_NAME || l_procedure_name,
5932 l_log_msg);
5933 END IF;
5934
5935 l_event_source_info :=
5936 get_invoice_event_source_info
5937 ( p_legal_entity_id => l_legal_entity_ids(i),
5938 p_ledger_id => l_ledger_ids(i),
5939 p_invoice_id => l_invoice_ids(i),
5940 p_calling_sequence => l_curr_calling_sequence
5941 );
5942
5943 l_log_msg :='After calling procedure get_invoice_event_source_info';
5944 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5945 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5946 G_MODULE_NAME || l_procedure_name,
5947 l_log_msg);
5948 END IF;
5949
5950 l_log_msg :='Before calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
5951 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5952 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5953 G_MODULE_NAME || l_procedure_name,
5954 l_log_msg);
5955 END IF;
5956
5957 AP_XLA_EVENTS_PKG.UPDATE_EVENT
5958 ( p_event_source_info => l_event_source_info,
5959 p_event_id => l_event_ids(i),
5960 p_event_type_code => NULL,
5961 p_event_date => p_sweep_to_date,
5962 p_event_status_code => NULL,
5963 p_valuation_method => NULL,
5964 p_security_context => l_event_security_context,
5965 p_calling_sequence => l_curr_calling_sequence
5966 );
5967
5968 --Bug 6874970
5969 UPDATE xla_ae_headers aeh
5970 SET aeh.accounting_date = p_sweep_to_date,
5971 aeh.period_name = AP_UTILITIES_PKG.get_gl_period_name(
5972 p_sweep_to_date,
5973 l_org_ids(i)),
5974 last_update_date = SYSDATE,
5975 last_updated_by = FND_GLOBAL.user_id
5976 WHERE aeh.event_id = l_event_ids(i)
5977 AND application_id = 200
5978 AND gl_transfer_status_code <> 'Y';
5979
5980 UPDATE xla_ae_lines ael
5981 SET ael.accounting_date = p_sweep_to_date,
5982 last_update_date = sysdate,
5983 last_updated_by = FND_GLOBAL.user_id
5984 WHERE ael.ae_header_id in (
5985 SELECT aeh.ae_header_id
5986 FROM xla_ae_headers aeh
5987 WHERE aeh.event_id = l_event_ids(i)
5988 AND aeh.application_id = 200
5989 AND aeh.gl_transfer_status_code <> 'Y');
5990
5991 l_log_msg :='After calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
5992 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5993 FND_LOG.STRING(G_LEVEL_PROCEDURE,
5994 G_MODULE_NAME || l_procedure_name,
5995 l_log_msg);
5999
5996 END IF;
5997
5998 END LOOP;
6000 if (p_period_name is null) then
6001 EXIT WHEN l_aid_events_mo_cur_no_period%NOTFOUND;
6002 else
6003 EXIT WHEN l_aid_events_mo_cur_period%NOTFOUND;
6004 end if;
6005
6006 END LOOP;
6007
6008 if (p_period_name is null) then
6009 CLOSE l_aid_events_mo_cur_no_period;
6010
6011 OPEN l_aph_events_mo_cur_no_period;
6012 else
6013 CLOSE l_aid_events_mo_cur_period;
6014
6015 OPEN l_aph_events_mo_cur_period;
6016 end if;
6017
6018 LOOP
6019
6020 if (p_period_name is null) then
6021 FETCH l_aph_events_mo_cur_no_period
6022 BULK COLLECT INTO
6023 l_event_ids,
6024 l_check_ids,
6025 l_org_ids,
6026 l_legal_entity_ids,
6027 l_ledger_ids
6028 LIMIT 1000;
6029 else
6030 FETCH l_aph_events_mo_cur_period
6031 BULK COLLECT INTO
6032 l_event_ids,
6033 l_check_ids,
6034 l_org_ids,
6035 l_legal_entity_ids,
6036 l_ledger_ids
6037 LIMIT 1000;
6038 end if;
6039
6040 FOR i IN 1 .. l_event_ids.count LOOP
6041
6042
6043 l_event_security_context :=
6044 get_event_security_context
6045 ( p_org_id => l_org_ids(i),
6046 p_calling_sequence => l_curr_calling_sequence
6047 );
6048
6049 l_event_source_info :=
6050 get_payment_event_source_info
6051 ( p_legal_entity_id => l_legal_entity_ids(i),
6052 p_ledger_id => l_ledger_ids(i),
6053 p_check_id => l_check_ids(i),
6054 p_calling_sequence => l_curr_calling_sequence
6055 );
6056
6057 l_log_msg :='Before calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
6058 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6059 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6060 G_MODULE_NAME || l_procedure_name,
6061 l_log_msg);
6062 END IF;
6063
6064 AP_XLA_EVENTS_PKG.UPDATE_EVENT
6065 ( p_event_source_info => l_event_source_info,
6066 p_event_id => l_event_ids(i),
6067 p_event_type_code => NULL,
6068 p_event_date => p_sweep_to_date,
6069 p_event_status_code => NULL,
6070 p_valuation_method => NULL,
6071 p_security_context => l_event_security_context,
6072 p_calling_sequence => l_curr_calling_sequence
6073 );
6074
6075 --Bug 6874970
6076 UPDATE xla_ae_headers aeh
6077 SET aeh.accounting_date = p_sweep_to_date,
6078 aeh.period_name = AP_UTILITIES_PKG.get_gl_period_name(
6079 p_sweep_to_date,
6080 l_org_ids(i)),
6081 last_update_date = SYSDATE,
6082 last_updated_by = FND_GLOBAL.user_id
6083 WHERE aeh.event_id = l_event_ids(i)
6084 AND application_id = 200
6085 AND gl_transfer_status_code <> 'Y';
6086
6087 UPDATE xla_ae_lines ael
6088 SET ael.accounting_date = p_sweep_to_date,
6089 last_update_date = sysdate,
6090 last_updated_by = FND_GLOBAL.user_id
6091 WHERE ael.ae_header_id in (
6092 SELECT aeh.ae_header_id
6093 FROM xla_ae_headers aeh
6094 WHERE aeh.event_id = l_event_ids(i)
6095 AND aeh.application_id = 200
6096 AND aeh.gl_transfer_status_code <> 'Y');
6097
6098
6099 l_log_msg :='After calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
6100 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6101 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6102 G_MODULE_NAME || l_procedure_name,
6103 l_log_msg);
6104 END IF;
6105 END LOOP;
6106
6107 if (p_period_name is null) then
6108 EXIT WHEN l_aph_events_mo_cur_no_period%NOTFOUND;
6109 else
6110 EXIT WHEN l_aph_events_mo_cur_period%NOTFOUND;
6111 end if;
6112
6113 END LOOP;
6114
6115 if (p_period_name is null) then
6116 CLOSE l_aph_events_mo_cur_no_period;
6117 else
6118 CLOSE l_aph_events_mo_cur_period;
6119 end if;
6120
6121 l_log_msg :='End of procedure '||l_procedure_name;
6122 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6123 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6124 G_MODULE_NAME || l_procedure_name,
6125 l_log_msg);
6126 END IF;
6127
6128 EXCEPTION
6129 WHEN OTHERS THEN
6130
6131 if (p_period_name is null) then
6132 IF (l_aid_events_mo_cur_no_period%ISOPEN) THEN
6133 CLOSE l_aid_events_mo_cur_no_period;
6134 END IF;
6135
6136 IF (l_aph_events_mo_cur_no_period%ISOPEN) THEN
6137 CLOSE l_aph_events_mo_cur_no_period;
6138 END IF;
6139 else
6140 IF (l_aid_events_mo_cur_period%ISOPEN) THEN
6141 CLOSE l_aid_events_mo_cur_period;
6142 END IF;
6143
6144 IF (l_aph_events_mo_cur_period%ISOPEN) THEN
6145 CLOSE l_aph_events_mo_cur_period;
6146 END IF;
6147 end if;
6148
6149 IF (SQLCODE <> -20001) THEN
6153 l_curr_calling_sequence);
6150 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
6151 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
6152 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
6154 FND_MESSAGE.SET_TOKEN('PARAMETERS',
6155 'p_ledger_id = '||p_ledger_id
6156 ||', p_period_name = '||p_period_name
6157 ||', p_from_date = '||p_from_date
6158 ||', p_to_date = '||p_to_date
6159 ||', p_sweep_to_date = '||p_sweep_to_date);
6160 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
6161 END IF;
6162 APP_EXCEPTION.RAISE_EXCEPTION();
6163 END multi_org_events_sweep;
6164
6165 /*============================================================================
6166 | PROCEDURE - SINGLE_ORG_EVENTS_SWEEP(PUBLIC)
6167 |
6168 | DESCRIPTION
6169 | This procedure is called by APXTRSWP.rdf (UPDATE_ACCTG_DATES).This
6170 | procedure is used to sweep accounting events from one accounting period
6171 | to another.
6172 |
6173 | PRAMETERS:
6174 | p_period_name: Old period's name
6175 | p_from_date: The start date to sweep
6176 | p_to_date: The end date to sweep
6177 | p_sweep_to_date: New event date
6178 | p_calling_sequence: Debug information
6179 |
6180 | KNOWN ISSUES:
6181 |
6182 | NOTES:
6183 |
6184 | MODIFICATION HISTORY
6185 | Date Author Description of Change
6186 |
6187 *===========================================================================*/
6188 PROCEDURE single_org_events_sweep(
6189 p_period_name IN VARCHAR2,
6190 p_from_date IN DATE,
6191 p_to_date IN DATE,
6192 p_sweep_to_date IN DATE,
6193 p_calling_sequence IN VARCHAR2)
6194 IS
6195
6196 TYPE t_event_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
6197 TYPE t_invoice_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
6198 TYPE t_check_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
6199 TYPE t_org_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
6200 TYPE t_legal_entity_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
6201 TYPE t_ledger_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
6202
6203 l_event_ids t_event_ids;
6204 l_invoice_ids t_invoice_ids;
6205 l_check_ids t_check_ids;
6206 l_org_ids t_org_ids;
6207 l_legal_entity_ids t_legal_entity_ids;
6208 l_ledger_ids t_ledger_ids;
6209 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
6210 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
6211
6212 CURSOR l_aid_events_cur IS
6213 SELECT AID.accounting_event_id,
6214 AID.invoice_id,
6215 AID.org_id,
6216 AI.legal_entity_id,
6217 AID.set_of_books_id ledger_id
6218 FROM ap_invoice_distributions AID,
6219 ap_invoices AI
6220 WHERE ( ( p_period_name IS NULL AND
6221 AID.accounting_date BETWEEN p_from_date AND p_to_date)
6222 OR
6223 ( p_period_name IS NOT NULL AND
6224 AP_UTILITIES_PKG.GET_CURRENT_GL_DATE(TRUNC(AID.accounting_date), aid.org_id) --bug5956469
6225 = p_period_name
6226 )
6227 )
6228 AND AID.posted_flag <> 'Y'
6229 AND AID.invoice_id = AI.invoice_id
6230 AND AID.accounting_event_id is not NULL;--Bug6320053
6231
6232 CURSOR l_aph_events_cur_period IS
6233 SELECT APH.accounting_event_id,
6234 APH.check_id,
6235 APH.org_id,
6236 AC.legal_entity_id,
6237 ( SELECT AIP.set_of_books_id
6238 FROM ap_invoice_payments_all AIP
6239 WHERE AIP.check_id = APH.check_id
6240 AND ROWNUM = 1
6241 ) ledger_id
6242 FROM ap_payment_history_all APH,
6243 ap_checks_all AC
6244 WHERE APH.check_id = AC.check_id
6245 AND APH.org_id = AC.org_id
6246 AND AP_UTILITIES_PKG.GET_CURRENT_GL_DATE(TRUNC(APH.accounting_date), aph.org_id) --bug5956469
6247 = p_period_name
6248 AND aph.posted_flag IN ('N', 'S') -- Bug 6869699
6249 AND APH.accounting_event_id is not NULL;--Bug6320053
6250
6251 CURSOR l_aph_events_cur_no_period IS
6252 SELECT APH.accounting_event_id,
6253 APH.check_id,
6254 APH.org_id,
6255 AC.legal_entity_id,
6256 ( SELECT AIP.set_of_books_id
6257 FROM ap_invoice_payments_all AIP
6258 WHERE AIP.check_id = APH.check_id
6259 AND ROWNUM = 1
6260 ) ledger_id
6261 FROM ap_payment_history_all APH,
6262 ap_checks_all AC
6263 WHERE APH.check_id = AC.check_id
6264 AND APH.org_id = AC.org_id
6265 AND APH.accounting_date BETWEEN p_from_date AND p_to_date
6266 AND aph.posted_flag IN ('N', 'S') -- Bug 6869699
6267 AND APH.accounting_event_id is not NULL;--Bug6320053
6268
6269 l_curr_calling_sequence VARCHAR2(2000);
6270
6271 -- Logging:
6272 l_procedure_name CONSTANT VARCHAR2(30) := 'SINGLE_ORG_EVENTS_SWEEP';
6273 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
6274
6275 BEGIN
6276
6277 l_curr_calling_sequence := p_calling_sequence
6278 || ' -> AP_ACCOUNTING_EVENTS_PKG.SINGLE_ORG_EVENTS_SWEEP';
6279
6280 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6281
6282 l_log_msg :='Begin of procedure '||l_procedure_name;
6286 l_log_msg);
6283 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6284 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6285 G_MODULE_NAME || l_procedure_name,
6287 END IF;
6288
6289 OPEN l_aid_events_cur;
6290 LOOP
6291
6292 FETCH l_aid_events_cur
6293 BULK COLLECT INTO
6294 l_event_ids,
6295 l_invoice_ids,
6296 l_org_ids,
6297 l_legal_entity_ids,
6298 l_ledger_ids
6299 LIMIT 1000;
6300
6301 FOR i IN 1 .. l_event_ids.count LOOP
6302
6303 l_event_security_context :=
6304 get_event_security_context
6305 ( p_org_id => l_org_ids(i),
6306 p_calling_sequence => l_curr_calling_sequence
6307 );
6308
6309 l_event_source_info :=
6310 get_invoice_event_source_info
6311 ( p_legal_entity_id => l_legal_entity_ids(i),
6312 p_ledger_id => l_ledger_ids(i),
6313 p_invoice_id => l_invoice_ids(i),
6314 p_calling_sequence => l_curr_calling_sequence
6315 );
6316
6317 l_log_msg :='Before calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
6318 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6319 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6320 G_MODULE_NAME || l_procedure_name,
6321 l_log_msg);
6322 END IF;
6323
6324 AP_XLA_EVENTS_PKG.UPDATE_EVENT
6325 ( p_event_source_info => l_event_source_info,
6326 p_event_id => l_event_ids(i),
6327 p_event_type_code => NULL,
6328 p_event_date => p_sweep_to_date,
6329 p_event_status_code => NULL,
6330 p_valuation_method => NULL,
6331 p_security_context => l_event_security_context,
6332 p_calling_sequence => l_curr_calling_sequence
6333 );
6334
6335 --Bug 6874970
6336 UPDATE xla_ae_headers aeh
6337 SET aeh.accounting_date = p_sweep_to_date,
6338 aeh.period_name = AP_UTILITIES_PKG.get_gl_period_name(
6339 p_sweep_to_date,
6340 l_org_ids(i)),
6341 last_update_date = SYSDATE,
6342 last_updated_by = FND_GLOBAL.user_id
6343 WHERE aeh.event_id = l_event_ids(i)
6344 AND application_id = 200
6345 AND gl_transfer_status_code <> 'Y';
6346
6347 UPDATE xla_ae_lines ael
6348 SET ael.accounting_date = p_sweep_to_date,
6349 last_update_date = sysdate,
6350 last_updated_by = FND_GLOBAL.user_id
6351 WHERE ael.ae_header_id in (
6352 SELECT aeh.ae_header_id
6353 FROM xla_ae_headers aeh
6354 WHERE aeh.event_id = l_event_ids(i)
6355 AND aeh.application_id = 200
6356 AND aeh.gl_transfer_status_code <> 'Y');
6357
6358 l_log_msg :='After calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
6359 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6360 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6361 G_MODULE_NAME || l_procedure_name,
6362 l_log_msg);
6363 END IF;
6364
6365 END LOOP;
6366
6367 EXIT WHEN l_aid_events_cur%NOTFOUND;
6368 END LOOP;
6369 CLOSE l_aid_events_cur;
6370
6371 if (p_period_name is null) then
6372 OPEN l_aph_events_cur_no_period;
6373 else
6374 OPEN l_aph_events_cur_period;
6375 end if;
6376
6377 LOOP
6378
6379 if (p_period_name is null) then
6380
6381 FETCH l_aph_events_cur_no_period
6382 BULK COLLECT INTO
6383 l_event_ids,
6384 l_check_ids,
6385 l_org_ids,
6386 l_legal_entity_ids,
6387 l_ledger_ids
6388 LIMIT 1000;
6389
6390 else
6391
6392 FETCH l_aph_events_cur_period
6393 BULK COLLECT INTO
6394 l_event_ids,
6395 l_check_ids,
6396 l_org_ids,
6397 l_legal_entity_ids,
6398 l_ledger_ids
6399 LIMIT 1000;
6400
6401 end if;
6402
6403 FOR i IN 1 .. l_event_ids.count LOOP
6404
6405 l_event_security_context :=
6406 get_event_security_context
6407 ( p_org_id => l_org_ids(i),
6408 p_calling_sequence => l_curr_calling_sequence
6409 );
6410
6411 l_event_source_info :=
6412 get_payment_event_source_info
6413 ( p_legal_entity_id => l_legal_entity_ids(i),
6414 p_ledger_id => l_ledger_ids(i),
6415 p_check_id => l_check_ids(i),
6416 p_calling_sequence => l_curr_calling_sequence
6417 );
6418
6419 l_log_msg :='After calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
6420 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6421 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6422 G_MODULE_NAME || l_procedure_name,
6423 l_log_msg);
6424 END IF;
6425
6426 AP_XLA_EVENTS_PKG.UPDATE_EVENT
6427 ( p_event_source_info => l_event_source_info,
6428 p_event_id => l_event_ids(i),
6429 p_event_type_code => NULL,
6430 p_event_date => p_sweep_to_date,
6431 p_event_status_code => NULL,
6432 p_valuation_method => NULL,
6436
6433 p_security_context => l_event_security_context,
6434 p_calling_sequence => l_curr_calling_sequence
6435 );
6437 --Bug 6874970
6438 UPDATE xla_ae_headers aeh
6439 SET aeh.accounting_date = p_sweep_to_date,
6440 aeh.period_name = AP_UTILITIES_PKG.get_gl_period_name(
6441 p_sweep_to_date,
6442 l_org_ids(i)),
6443 last_update_date = SYSDATE,
6444 last_updated_by = FND_GLOBAL.user_id
6445 WHERE aeh.event_id = l_event_ids(i)
6446 AND application_id = 200
6447 AND gl_transfer_status_code <> 'Y';
6448
6449 UPDATE xla_ae_lines ael
6450 SET ael.accounting_date = p_sweep_to_date,
6451 last_update_date = sysdate,
6452 last_updated_by = FND_GLOBAL.user_id
6453 WHERE ael.ae_header_id in (
6454 SELECT aeh.ae_header_id
6455 FROM xla_ae_headers aeh
6456 WHERE aeh.event_id = l_event_ids(i)
6457 AND aeh.application_id = 200
6458 AND aeh.gl_transfer_status_code <> 'Y');
6459
6460 l_log_msg :='After calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
6461 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6462 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6463 G_MODULE_NAME || l_procedure_name,
6464 l_log_msg);
6465 END IF;
6466
6467 END LOOP;
6468
6469 if (p_period_name is null) then
6470 EXIT WHEN l_aph_events_cur_no_period%NOTFOUND;
6471 else
6472 EXIT WHEN l_aph_events_cur_period%NOTFOUND;
6473 end if;
6474
6475 END LOOP;
6476
6477 if (p_period_name is null) then
6478 CLOSE l_aph_events_cur_no_period;
6479 else
6480 CLOSE l_aph_events_cur_period;
6481 end if;
6482
6483 l_log_msg :='End of procedure '||l_procedure_name;
6484 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6485 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6486 G_MODULE_NAME || l_procedure_name,
6487 l_log_msg);
6488 END IF;
6489
6490 EXCEPTION
6491 WHEN OTHERS THEN
6492 IF (l_aid_events_cur%ISOPEN) THEN
6493 CLOSE l_aid_events_cur;
6494 END IF;
6495
6496 if (p_period_name is null) then
6497 IF (l_aph_events_cur_no_period%ISOPEN) THEN
6498 CLOSE l_aph_events_cur_no_period;
6499 END IF;
6500 else
6501 IF (l_aph_events_cur_period%ISOPEN) THEN
6502 CLOSE l_aph_events_cur_period;
6503 END IF;
6504 end if;
6505
6506 IF (SQLCODE <> -20001) THEN
6507 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
6508 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
6509 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
6510 l_curr_calling_sequence);
6511 FND_MESSAGE.SET_TOKEN('PARAMETERS',
6512 'p_period_name = '||p_period_name
6513 ||', p_from_date = '||p_from_date
6514 ||', p_to_date = '||p_to_date
6515 ||', p_sweep_to_date = '||p_sweep_to_date);
6516 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
6517 END IF;
6518 APP_EXCEPTION.RAISE_EXCEPTION();
6519 END single_org_events_sweep;
6520
6521
6522 /*============================================================================
6523 | FUNCTION - GET_EVENT_CLASS(PRIVATE)
6524 |
6525 | DESCRIPTION
6526 | This procedure is used to get the event class of a particula event
6527 | type.
6528 |
6529 | PRAMETERS:
6530 | p_event_type: Event Type
6531 | p_calling_sequence: Debug information
6532 |
6533 | RETURN: VARCHAR2
6534 |
6535 | KNOWN ISSUES:
6536 |
6537 | NOTES:
6538 |
6539 | MODIFICATION HISTORY
6540 | Date Author Description of Change
6541 |
6542 *===========================================================================*/
6543 FUNCTION get_event_class(
6544 p_event_type IN VARCHAR2,
6545 p_calling_sequence IN VARCHAR2)
6546 RETURN VARCHAR2
6547 IS
6548
6549 l_curr_calling_sequence VARCHAR2(2000);
6550
6551 -- Logging:
6552 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_CLASS_CODE';
6553 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
6554
6555 BEGIN
6556
6557 l_curr_calling_sequence :=
6558 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.GET_CLASS_CODE';
6559
6560 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6561
6562 l_log_msg :='Begin of procedure '||l_procedure_name;
6563 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6564 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6565 G_MODULE_NAME || l_procedure_name,
6566 l_log_msg);
6567 END IF;
6568
6569 IF ( p_event_type IN ( INVOICE_VALIDATED_TYPE,
6570 INVOICE_ADJUSTED_TYPE,
6571 INVOICE_CANCELLED_TYPE)) THEN
6572 RETURN INVOICES_CLASS;
6573 END IF;
6574
6575 IF ( p_event_type IN ( CREDIT_MEMO_VALIDATED_TYPE,
6576 CREDIT_MEMO_ADJUSTED_TYPE,
6577 CREDIT_MEMO_CANCELLED_TYPE)) THEN
6578 RETURN CREDIT_MEMOS_CLASS;
6579 END IF;
6583 DEBIT_MEMO_CANCELLED_TYPE)) THEN
6580
6581 IF ( p_event_type IN ( DEBIT_MEMO_VALIDATED_TYPE,
6582 DEBIT_MEMO_ADJUSTED_TYPE,
6584 RETURN DEBIT_MEMOS_CLASS;
6585 END IF;
6586
6587 IF ( p_event_type IN ( PREPAYMENT_VALIDATED_TYPE,
6588 PREPAYMENT_ADJUSTED_TYPE,
6589 PREPAYMENT_CANCELLED_TYPE)) THEN
6590 RETURN PREPAYMENTS_CLASS;
6591 END IF;
6592
6593 IF ( p_event_type IN ( PREPAYMENT_APPLIED_TYPE,
6594 PREPAYMENT_UNAPPLIED_TYPE,
6595 PREPAY_APP_ADJUSTED_TYPE)) THEN
6596 RETURN PREPAYMENT_APPLICATIONS_CLASS;
6597 END IF;
6598
6599 IF ( p_event_type IN ( PAYMENT_CREATED_TYPE,
6600 PAYMENT_CANCELLED_TYPE,
6601 MANUAL_PAYMENT_ADJUSTED_TYPE,
6602 PAYMENT_ADJUSTED_TYPE)) THEN
6603 RETURN PAYMENTS_CLASS;
6604 END IF;
6605
6606 IF ( p_event_type IN ( REFUND_RECORDED_TYPE,
6607 REFUND_CANCELLED_TYPE,
6608 REFUND_ADJUSTED_TYPE)) THEN
6609 RETURN REFUNDS_CLASS;
6610 END IF;
6611
6612 IF
6613 (
6614 p_event_type IN
6615 (
6616 PAYMENT_MATURED_TYPE,
6617 PAYMENT_MATURITY_REVERSED_TYPE,
6618 PAYMENT_MATURITY_ADJUSTED_TYPE
6619 )
6620 )
6621 THEN
6622 RETURN FUTURE_DATED_PAYMENTS_CLASS;
6623 END IF;
6624
6625 IF
6626 (
6627 p_event_type IN
6628 (
6629 PAYMENT_CLEARED_TYPE,
6630 PAYMENT_UNCLEARED_TYPE,
6631 PAYMENT_CLEARING_ADJUSTED_TYPE
6632 )
6633 )
6634 THEN
6635 RETURN RECONCILED_PAYMENTS_CLASS;
6636 END IF;
6637
6638
6639 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
6640 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
6641 l_curr_calling_sequence);
6642 FND_MESSAGE.SET_TOKEN('PARAMETERS',
6643 'p_event_type = '||p_event_type );
6644 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
6645
6646 APP_EXCEPTION.RAISE_EXCEPTION();
6647 RETURN NULL; -- unreachable
6648
6649 END get_event_class;
6650
6651
6652 /*============================================================================
6653 | FUNCTION - GET_EVENT_SECURITY_CONTEXT(PRIVATE)
6654 |
6655 | DESCRIPTION
6656 | This function is used to get the event security context.
6657 |
6658 | PRAMETERS:
6659 | p_org_id: Organization ID
6660 | p_calling_sequence: Debug information
6661 |
6662 | RETURN: XLA_EVENTS_PUB_PKG.T_SECURITY
6663 |
6664 | KNOWN ISSUES:
6665 |
6666 | NOTES:
6667 |
6668 | MODIFICATION HISTORY
6669 | Date Author Description of Change
6670 |
6671 *===========================================================================*/
6672 FUNCTION get_event_security_context(
6673 p_org_id IN NUMBER,
6674 p_calling_sequence IN VARCHAR2)
6675 RETURN XLA_EVENTS_PUB_PKG.T_SECURITY
6676 IS
6677
6678 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
6679
6680 -- Logging:
6681 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_EVENT_SECURITY_CONTEXT';
6682 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
6683
6684 BEGIN
6685
6686 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6687
6688 l_log_msg :='Begin of procedure '||l_procedure_name;
6689 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6690 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6691 G_MODULE_NAME || l_procedure_name,
6692 l_log_msg);
6693 END IF;
6694
6695 l_event_security_context.security_id_int_1 := p_org_id;
6696
6697 l_log_msg :='End of procedure '||l_procedure_name;
6698 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6699 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6700 G_MODULE_NAME || l_procedure_name,
6701 l_log_msg);
6702 END IF;
6703
6704 RETURN l_event_security_context;
6705
6706 END get_event_security_context;
6707
6708 /*============================================================================
6709 | FUNCTION - GET_INVOICE_EVENT_SOURCE_INFO(PRIVATE)
6710 |
6711 | DESCRIPTION
6712 | This function is used to get invoice event source information
6713 |
6714 | PRAMETERS:
6715 | p_legal_entity_id: Legal entity ID
6716 | p_ledger_id: Ledger ID
6717 | p_invoice_id: Invoice ID
6718 | p_calling_sequence: Debug information
6719 |
6720 | RETURN: XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
6721 |
6722 | KNOWN ISSUES:
6723 |
6724 | NOTES:
6725 |
6726 | MODIFICATION HISTORY
6727 | Date Author Description of Change
6728 |
6729 *===========================================================================*/
6730 FUNCTION get_invoice_event_source_info(
6731 p_legal_entity_id IN NUMBER,
6732 p_ledger_id IN NUMBER,
6733 p_invoice_id IN NUMBER,
6734 p_calling_sequence IN VARCHAR2)
6735 RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
6736 IS
6737
6741 -- Logging:
6738 l_invoice_num VARCHAR2(50);
6739 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
6740
6742 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_EVENT_SOURCE_INFO';
6743 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
6744
6745 BEGIN
6746
6747 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6748
6749 l_log_msg :='Begin of procedure '||l_procedure_name;
6750 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6751 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6752 G_MODULE_NAME || l_procedure_name,
6753 l_log_msg);
6754 END IF;
6755
6756 select invoice_num
6757 into l_invoice_num
6758 from ap_invoices_all --bug6705052
6759 where invoice_id = p_invoice_id;
6760
6761 l_event_source_info.application_id := 200;
6762 l_event_source_info.legal_entity_id := p_legal_entity_id;
6763 l_event_source_info.ledger_id := p_ledger_id;
6764 l_event_source_info.entity_type_code := INVOICES_ENTITY;
6765 l_event_source_info.transaction_number := l_invoice_num;
6766 l_event_source_info.source_id_int_1 := p_invoice_id;
6767
6768 l_log_msg :='End of procedure '||l_procedure_name;
6769 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6770 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6771 G_MODULE_NAME || l_procedure_name,
6772 l_log_msg);
6773 END IF;
6774
6775 RETURN l_event_source_info;
6776
6777 END get_invoice_event_source_info;
6778
6779
6780 /*============================================================================
6781 | FUNCTION - GET_PAYMENT_EVENT_SOURCE_INFO(PRIVATE)
6782 |
6783 | DESCRIPTION
6784 | This procedure is used to get payment event source information.
6785 |
6786 | PRAMETERS:
6787 | p_legal_entity_id: Legal Entity ID
6788 | p_ledger_id: Ledger ID
6789 | p_invoice_id: Invoice ID
6790 | p_calling_sequence: Debug information
6791 |
6792 | RETURN: XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
6793 |
6794 | KNOWN ISSUES:
6795 |
6796 | NOTES:
6797 |
6798 | MODIFICATION HISTORY
6799 | Date Author Description of Change
6800 |
6801 *===========================================================================*/
6802 FUNCTION get_payment_event_source_info(
6803 p_legal_entity_id IN NUMBER,
6804 p_ledger_id IN NUMBER,
6805 p_check_id IN NUMBER,
6806 p_calling_sequence IN VARCHAR2)
6807 RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
6808 IS
6809
6810 l_check_number NUMBER(15);
6811 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
6812
6813 -- Logging:
6814 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_PAYMENT_EVENT_SOURCE_INFO';
6815 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
6816
6817 BEGIN
6818
6819 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6820
6821 l_log_msg :='Begin of procedure '||l_procedure_name;
6822 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6823 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6824 G_MODULE_NAME || l_procedure_name,
6825 l_log_msg);
6826 END IF;
6827
6828 select check_number
6829 into l_check_number
6830 from ap_checks_all --bug6705052
6831 where check_id = p_check_id;
6832
6833 l_event_source_info.application_id := 200;
6834 l_event_source_info.legal_entity_id := p_legal_entity_id;
6835 l_event_source_info.ledger_id := p_ledger_id;
6836 l_event_source_info.entity_type_code := PAYMENTS_ENTITY;
6837 l_event_source_info.transaction_number := l_check_number;
6838 l_event_source_info.source_id_int_1 := p_check_id;
6839
6840 l_log_msg :='End of procedure '||l_procedure_name;
6841 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6842 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6843 G_MODULE_NAME || l_procedure_name,
6844 l_log_msg);
6845 END IF;
6846
6847 RETURN l_event_source_info;
6848
6849 END get_payment_event_source_info;
6850
6851
6852 /*============================================================================
6853 | PROCEDURE - GET_INVOICE_INFO(PRIVATE)
6854 |
6855 | DESCRIPTION
6856 | This procedure is used to get invoice information.
6857 |
6858 | PRAMETERS:
6859 | p_invoice_id: Invoice ID
6860 | p_org_id: Organization ID
6861 | p_legal_entity_id: Legal Entity ID
6862 | p_ledger_id: Ledger ID
6863 | p_transaction_date: Invoice date
6864 | p_calling_sequence: Debug information
6865 |
6866 | KNOWN ISSUES:
6867 |
6868 | NOTES:
6869 |
6870 | MODIFICATION HISTORY
6871 | Date Author Description of Change
6872 |
6873 *===========================================================================*/
6874 PROCEDURE get_invoice_info(
6875 p_invoice_id IN NUMBER,
6876 p_org_id OUT NOCOPY NUMBER,
6877 p_legal_entity_id OUT NOCOPY NUMBER,
6878 p_ledger_id OUT NOCOPY NUMBER,
6879 p_transaction_date OUT NOCOPY DATE,
6880 p_calling_sequence IN VARCHAR2)
6881 IS
6882
6883 l_curr_calling_sequence VARCHAR2(2000);
6884
6885 -- Logging:
6886 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_EVENT_SOURCE_INFO';
6890
6887 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
6888
6889 BEGIN
6891 l_curr_calling_sequence :=
6892 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.GET_INVOICE_INFO';
6893
6894 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6895
6896 l_log_msg :='Begin of procedure '||l_procedure_name;
6897 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6898 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6899 G_MODULE_NAME || l_procedure_name,
6900 l_log_msg);
6901 END IF;
6902
6903 SELECT
6904 AI.org_id,
6905 AI.legal_entity_id,
6906 AI.set_of_books_id,
6907 AI.invoice_date
6908 INTO
6909 p_org_id,
6910 p_legal_entity_id,
6911 p_ledger_id,
6912 p_transaction_date
6913 FROM
6914 ap_invoices_all AI --bug6705052
6915 WHERE
6916 AI.invoice_id = p_invoice_id;
6917
6918 l_log_msg :='End of procedure '||l_procedure_name;
6919 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6920 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6921 G_MODULE_NAME || l_procedure_name,
6922 l_log_msg);
6923 END IF;
6924
6925 EXCEPTION
6926 WHEN OTHERS THEN
6927 IF (SQLCODE <> -20001) THEN
6928 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
6929 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
6930 l_curr_calling_sequence);
6931 FND_MESSAGE.SET_TOKEN('PARAMETERS',
6932 'p_org_id = '||p_org_id);
6933 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
6934 END IF;
6935 APP_EXCEPTION.RAISE_EXCEPTION();
6936 END get_invoice_info;
6937
6938
6939 /*===========================================================================
6940 | PROCEDURE - GET_PAYMENT_INFO(PRIVATE)
6941 |
6942 | DESCRIPTION
6943 | This procedure is used to get payment information.
6944 |
6945 | PRAMETERS:
6946 | p_check_id: Check ID
6947 | p_org_id: Organization ID
6948 | p_legal_entity_id: Legal entity ID
6949 | p_ledger_id: Ledger ID
6950 | p_calling_sequence: Debug information
6951 |
6952 | KNOWN ISSUES:
6953 |
6954 | NOTES:
6955 |
6956 | MODIFICATION HISTORY
6957 | Date Author Description of Change
6958 |
6959 *==========================================================================*/
6960 PROCEDURE get_payment_info(
6961 p_check_id IN NUMBER,
6962 p_org_id OUT NOCOPY NUMBER,
6963 p_legal_entity_id OUT NOCOPY NUMBER,
6964 p_ledger_id OUT NOCOPY NUMBER,
6965 p_calling_sequence IN VARCHAR2)
6966 IS
6967
6968 l_curr_calling_sequence VARCHAR2(2000);
6969
6970 -- Logging:
6971 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_PAYMENT_INFO';
6972 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
6973
6974 BEGIN
6975
6976 l_curr_calling_sequence :=
6977 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.GET_PAYMENT_INFO';
6978
6979 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6980
6981 l_log_msg :='Begin of procedure '||l_procedure_name;
6982 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6983 FND_LOG.STRING(G_LEVEL_PROCEDURE,
6984 G_MODULE_NAME || l_procedure_name,
6985 l_log_msg);
6986 END IF;
6987
6988 SELECT AC.org_id,
6989 AC.legal_entity_id,
6990 ASP.set_of_books_id
6991 INTO p_org_id,
6992 p_legal_entity_id,
6993 p_ledger_id
6994 FROM ap_checks_all AC, --bug6705052
6995 ap_system_parameters_all ASP --bug6705052
6996 WHERE AC.check_id = p_check_id
7000 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6997 AND nvl(AC.org_id,-99) = nvl(ASP.org_id,-99);
6998
6999 l_log_msg :='End of procedure '||l_procedure_name;
7001 FND_LOG.STRING(G_LEVEL_PROCEDURE,
7002 G_MODULE_NAME || l_procedure_name,
7003 l_log_msg);
7004 END IF;
7005
7006 EXCEPTION
7007 WHEN OTHERS THEN
7008 IF (SQLCODE <> -20001) THEN
7009 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
7010 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
7011 l_curr_calling_sequence);
7012 FND_MESSAGE.SET_TOKEN('PARAMETERS',
7013 'p_check_id = '||p_check_id);
7014 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
7015 END IF;
7016 APP_EXCEPTION.RAISE_EXCEPTION();
7017 END get_payment_info;
7018
7019
7020 -- Bug 4996808. Created this procedure to update the accounting_event_id on
7021 -- the prepay header and the prepayment appl dists since the header and dists
7022 -- are created during validation before the events are created.
7023 /*===========================================================================
7024 | PROCEDURE - UPDATE_PREPAYMENT_HEADER(PRIVATE)
7025 |
7026 | DESCRIPTION
7027 | This procedure is used to update prepayment header information
7028 |
7029 | PRAMETERS:
7030 | p_invoice_id: Invoice ID
7031 | p_invoice_line_number: Invoice Line Number
7032 | p_accounting_event_id: Accounting Event ID
7033 | p_calling_sequence: Debug information
7034 |
7035 *==========================================================================*/
7036 PROCEDURE Update_Prepayment_Header(
7037 p_invoice_id IN NUMBER,
7038 p_invoice_line_number IN NUMBER,
7039 p_accounting_event_id IN NUMBER,
7040 p_accounting_date IN DATE,
7041 p_transaction_type IN VARCHAR2,
7042 p_calling_sequence IN VARCHAR2)
7043 IS
7044
7045 l_curr_calling_sequence VARCHAR2(2000);
7046 TYPE l_prepay_hist_list IS TABLE OF ap_prepay_history.prepay_history_id%TYPE;
7047 l_prepay_hist_tab l_prepay_hist_list;
7048
7049
7050 -- Logging:
7051 l_procedure_name CONSTANT VARCHAR2(30) := 'Update_Prepayment_Header';
7052 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
7053
7054 BEGIN
7055
7056 l_curr_calling_sequence := p_calling_sequence ||
7057 ' -> AP_ACCOUNTING_EVENTS_PKG.UPDATE_PREPAYMENT_HEADER';
7058
7059 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7060
7061 l_log_msg :='Begin of procedure '||l_procedure_name;
7062 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
7063 FND_LOG.STRING(G_LEVEL_PROCEDURE,
7064 G_MODULE_NAME || l_procedure_name,
7065 l_log_msg);
7066 END IF;
7067
7068 UPDATE AP_Prepay_History
7069 SET Accounting_Event_ID = p_accounting_event_id
7070 WHERE Invoice_ID = p_invoice_id
7071 AND Invoice_Line_Number = p_invoice_line_number
7072 AND Accounting_Date = p_accounting_date
7073 AND Transaction_Type = p_transaction_type
7074 AND Accounting_Event_ID IS NULL
7075 RETURNING Prepay_History_ID
7076 BULK COLLECT INTO l_prepay_hist_tab;
7077
7078
7079 IF l_prepay_hist_tab.count >0 THEN
7080
7081 FORALL i IN l_prepay_hist_tab.FIRST..l_prepay_hist_tab.LAST
7082 UPDATE AP_Prepay_App_Dists APAD
7083 SET Accounting_Event_ID = p_accounting_event_id
7084 WHERE Prepay_History_ID = l_prepay_hist_tab(i);
7085
7086 END IF;
7087
7088 l_log_msg :='End of procedure '||l_procedure_name;
7089
7090 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
7091 FND_LOG.STRING(G_LEVEL_PROCEDURE,
7092 G_MODULE_NAME || l_procedure_name,
7093 l_log_msg);
7094 END IF;
7095
7096 EXCEPTION
7097 WHEN OTHERS THEN
7098
7099 IF (SQLCODE <> -20001) THEN
7100 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
7101 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
7102 l_curr_calling_sequence);
7103 FND_MESSAGE.SET_TOKEN('PARAMETERS',
7104 'p_invoice_id = '||p_invoice_id);
7105 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
7106 END IF;
7107 APP_EXCEPTION.RAISE_EXCEPTION();
7108
7109 END Update_Prepayment_Header;
7110
7111
7112 /*===========================================================================
7113 | PROCEDURE - INSERT_PREPAYMENT_HEADER(PRIVATE)
7114 |
7115 | DESCRIPTION
7116 | This procedure is used to insert prepayment header information
7117 |
7118 | PRAMETERS:
7119 | p_invoice_id: Invoice ID
7120 | p_invoice_line_number: Invoice Line Number
7121 | p_accounting_event_id: Accounting Event ID
7122 | p_invoice_adjustment_id: Invoice Adjustment ID
7123 | p_calling_sequence: Debug information
7124 |
7125 | KNOWN ISSUES:
7126 |
7127 | NOTES:
7128 |
7129 | MODIFICATION HISTORY
7130 | Date Author Description of Change
7131 |
7132 *==========================================================================*/
7133 PROCEDURE Insert_Prepayment_Header(
7134 p_invoice_id IN NUMBER,
7135 p_invoice_line_number IN NUMBER,
7136 p_accounting_event_id IN NUMBER,
7137 p_accounting_date IN DATE,
7138 p_invoice_adjustment_id IN NUMBER,
7139 p_calling_sequence IN VARCHAR2)
7140 IS
7141
7142 l_curr_calling_sequence VARCHAR2(2000);
7146 l_prepay_line_number NUMBER;
7143 l_sum_amount NUMBER;
7144 l_transaction_type VARCHAR2(30);
7145 l_prepay_invoice_id NUMBER;
7147 l_org_id NUMBER;
7148 l_related_prepay_app_event_id NUMBER;
7149
7150 -- Logging:
7151 l_procedure_name CONSTANT VARCHAR2(30) := 'Insert_Prepayment_Header';
7152 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
7153
7154 --bugfix:4936043
7155 CURSOR prepayment_invoices IS
7156 SELECT AIL.amount,
7157 AIL.invoice_id,
7158 AIL.line_number,
7159 AIL.org_id
7160 FROM ap_invoice_lines AIL,
7161 ap_invoice_distributions AID,
7162 ap_invoice_distributions AID1
7163 WHERE AID.invoice_id = p_invoice_id
7164 AND AID.invoice_line_number = p_invoice_line_number
7165 AND AID.line_type_lookup_code = 'PREPAY'
7166 AND AID.prepay_distribution_id = AID1.invoice_distribution_id
7167 AND AIL.invoice_id = AID1.invoice_id
7168 AND AIL.line_number = AID1.invoice_line_number
7169 GROUP BY ail.invoice_id,ail.line_number,ail.org_id,ail.amount;
7170
7171
7172 BEGIN
7173
7174 l_curr_calling_sequence := p_calling_sequence ||
7175 ' -> AP_ACCOUNTING_EVENTS_PKG.INSERT_PREPAYMENT_HEADER';
7176
7177 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7178
7179 l_log_msg :='Begin of procedure '||l_procedure_name;
7180 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
7181 FND_LOG.STRING(G_LEVEL_PROCEDURE,
7182 G_MODULE_NAME || l_procedure_name,
7183 l_log_msg);
7184 END IF;
7185
7186 BEGIN
7187
7188 SELECT AIL.amount,
7189 AIL.prepay_invoice_id,
7190 AIL.prepay_line_number,
7191 AIL.org_id
7192 INTO l_sum_amount,
7193 l_prepay_invoice_id,
7194 l_prepay_line_number,
7195 l_org_id
7196 FROM ap_invoice_lines AIL
7197 WHERE AIL.invoice_id = P_invoice_id
7198 AND AIL.line_type_lookup_code = 'PREPAY'
7199 AND AIL.line_number = P_invoice_line_number;
7200
7201 --Bugfix:4936043
7202 --Complex Work: Recoupment
7203 --Added the following SQL for recoupment logic, as we do not have a parent 'PREPAY' line for the
7204 --'PREPAY' distributions. For recoupment we create the 'PREPAY' distributions tied to the ITEM line
7205 --itself.
7206 EXCEPTION WHEN NO_DATA_FOUND THEN
7207
7208 OPEN prepayment_invoices;
7209 LOOP
7210 FETCH prepayment_invoices into l_sum_amount,
7211 l_prepay_invoice_id,
7212 l_prepay_line_number,
7213 l_org_id;
7214
7215 EXIT WHEN prepayment_invoices%NOTFOUND;
7216
7217 BEGIN
7218
7219 SELECT min(accounting_Event_id)
7220 INTO l_related_prepay_app_event_id
7221 FROM AP_INVOICE_DISTRIBUTIONS AID
7222 WHERE AID.line_type_lookup_code = 'PREPAY'
7223 AND nvl(posted_flag,'N') = 'Y'
7224 AND nvl(AID.amount,0) < 0
7225 AND AID.invoice_id = P_invoice_id
7226 AND AID.invoice_line_number = P_invoice_line_number;
7227
7228
7229 EXCEPTION
7230 WHEN NO_DATA_FOUND THEN
7231 l_related_prepay_app_event_id:= null;
7232 END;
7233
7234 IF P_invoice_adjustment_id is NOT NULL THEN
7235 l_transaction_type := PREPAY_APP_ADJUSTED_TYPE;
7236 ELSIF l_sum_Amount <= 0 THEN
7237 l_transaction_type := PREPAYMENT_APPLIED_TYPE;
7238 ELSE
7239 l_transaction_type := PREPAYMENT_UNAPPLIED_TYPE;
7240 END IF;
7241
7242 INSERT INTO AP_PREPAY_HISTORY_ALL
7243 ( PREPAY_HISTORY_ID
7244 ,PREPAY_INVOICE_ID
7245 ,PREPAY_LINE_NUM
7246 ,ACCOUNTING_EVENT_ID
7247 ,HISTORICAL_FLAG
7248 ,INVOICE_ID
7249 ,INVOICE_LINE_NUMBER
7250 ,ACCOUNTING_DATE
7251 ,INVOICE_ADJUSTMENT_EVENT_ID
7252 ,ORG_ID
7253 ,POSTED_FLAG
7254 ,RELATED_PREPAY_APP_EVENT_ID
7255 ,TRANSACTION_TYPE
7256 ,LAST_UPDATED_BY
7257 ,LAST_UPDATE_DATE
7258 ,LAST_UPDATE_LOGIN
7259 ,CREATED_BY
7260 ,CREATION_DATE
7261 ,PROGRAM_APPLICATION_ID
7262 ,PROGRAM_ID
7263 ,PROGRAM_UPDATE_DATE
7264 ,REQUEST_ID)
7265 VALUES
7266 (AP_PREPAY_HISTORY_S.nextval
7267 ,l_prepay_invoice_id
7268 ,l_prepay_line_number
7269 ,p_Accounting_event_id
7270 ,'N'
7271 ,P_INVOICE_ID
7272 ,p_invoice_line_number
7273 ,p_accounting_date
7274 ,p_invoice_adjustment_id
7275 ,l_org_id
7276 ,'N'
7277 ,l_related_prepay_app_event_id
7278 ,L_TRANSACTION_TYPE
7279 ,FND_GLOBAL.user_id
7280 ,sysdate
7281 ,FND_GLOBAL.login_id
7282 ,FND_GLOBAL.user_id
7283 ,sysdate
7284 ,null
7285 ,null
7286 ,null
7287 ,null);
7288
7289 END LOOP;
7290
7291 CLOSE prepayment_invoices;
7292
7293 l_log_msg :='End of procedure '||l_procedure_name;
7294
7295 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
7296 FND_LOG.STRING(G_LEVEL_PROCEDURE,
7297 G_MODULE_NAME || l_procedure_name,
7298 l_log_msg);
7299 END IF;
7300
7301 RETURN;
7302
7303 END;
7304
7305
7306 BEGIN
7307
7308 SELECT min(accounting_Event_id)
7309 INTO l_related_prepay_app_event_id
7310 FROM AP_INVOICE_DISTRIBUTIONS AID
7311 WHERE AID.line_type_lookup_code = 'PREPAY'
7312 AND nvl(posted_flag,'N') = 'Y'
7313 AND nvl(AID.amount,0) < 0
7314 AND AID.invoice_id = P_invoice_id
7315 AND AID.invoice_line_number = P_invoice_line_number;
7316
7317
7318 EXCEPTION
7319 WHEN NO_DATA_FOUND THEN
7320 l_related_prepay_app_event_id:= null;
7321
7322 END;
7323
7324 IF P_invoice_adjustment_id is NOT NULL THEN
7325 l_transaction_type := PREPAY_APP_ADJUSTED_TYPE;
7326
7327 ELSIF l_sum_Amount <= 0 THEN
7328 l_transaction_type := PREPAYMENT_APPLIED_TYPE;
7329 ELSE
7330 l_transaction_type := PREPAYMENT_UNAPPLIED_TYPE;
7331 END IF;
7332
7333 INSERT INTO AP_PREPAY_HISTORY_ALL
7334 ( PREPAY_HISTORY_ID
7335 ,PREPAY_INVOICE_ID
7336 ,PREPAY_LINE_NUM
7337 ,ACCOUNTING_EVENT_ID
7338 ,ACCOUNTING_DATE
7339 ,HISTORICAL_FLAG
7340 ,INVOICE_ID
7341 ,INVOICE_ADJUSTMENT_EVENT_ID
7342 ,ORG_ID
7343 ,POSTED_FLAG
7344 ,RELATED_PREPAY_APP_EVENT_ID
7345 ,TRANSACTION_TYPE
7346 ,LAST_UPDATED_BY
7347 ,LAST_UPDATE_DATE
7348 ,LAST_UPDATE_LOGIN
7349 ,CREATED_BY
7350 ,CREATION_DATE
7351 ,PROGRAM_APPLICATION_ID
7352 ,PROGRAM_ID
7353 ,PROGRAM_UPDATE_DATE
7354 ,REQUEST_ID)
7355 VALUES
7356 (AP_PREPAY_HISTORY_S.nextval
7357 ,l_prepay_invoice_id
7358 ,l_prepay_line_number
7359 ,p_Accounting_event_id
7360 ,p_accounting_date
7361 ,'N'
7362 ,P_INVOICE_ID
7363 ,p_invoice_adjustment_id
7364 ,l_org_id
7365 ,'N'
7366 ,l_related_prepay_app_event_id
7367 ,L_TRANSACTION_TYPE
7368 ,FND_GLOBAL.user_id
7369 ,sysdate
7370 ,FND_GLOBAL.login_id
7371 ,FND_GLOBAL.user_id
7372 ,sysdate
7373 ,null
7374 ,null
7375 ,null
7376 ,null);
7377
7378 l_log_msg :='End of procedure '||l_procedure_name;
7379
7380 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
7381 FND_LOG.STRING(G_LEVEL_PROCEDURE,
7382 G_MODULE_NAME || l_procedure_name,
7383 l_log_msg);
7384 END IF;
7385
7386 EXCEPTION
7387 WHEN OTHERS THEN
7388
7389 IF (SQLCODE <> -20001) THEN
7390 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
7391 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
7392 l_curr_calling_sequence);
7393 FND_MESSAGE.SET_TOKEN('PARAMETERS',
7394 'p_invoice_id = '||p_invoice_id);
7395 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
7396 END IF;
7397 APP_EXCEPTION.RAISE_EXCEPTION();
7398
7399 END Insert_Prepayment_Header;
7400
7401 END AP_ACCOUNTING_EVENTS_PKG;