[Home] [Help]
PACKAGE BODY: APPS.AP_DRILLDOWN_PUB_PKG
Source
1 PACKAGE BODY AP_DRILLDOWN_PUB_PKG as
2 /* $Header: apsladrb.pls 120.17 2010/09/16 08:50:00 kpasikan ship $ */
3
4 -- Logging Infra
5 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'AP.PLSQL.AP_DRILLDOWN_PUB_PKG.';
13 -- Logging Infra
14
15 /*----------------------------------------------------------------
16 |Private procedure: get_invoice_info
17 +---------------------------------------------------------------*/
18 PROCEDURE get_invoice_info
19 ( p_invoice_id IN NUMBER,
20 p_org_id OUT NOCOPY NUMBER,
21 p_legal_entity_id OUT NOCOPY NUMBER,
22 p_ledger_id OUT NOCOPY NUMBER,
23 p_calling_sequence IN VARCHAR2
24 );
25
26 /*----------------------------------------------------------------
27 |Private procedure: get_payment_info
28 +---------------------------------------------------------------*/
29 PROCEDURE get_payment_info
30 ( p_check_id IN NUMBER,
31 p_org_id OUT NOCOPY NUMBER,
32 p_legal_entity_id OUT NOCOPY NUMBER,
33 p_ledger_id OUT NOCOPY NUMBER,
34 p_calling_sequence IN VARCHAR2
35 );
36
37 /*---------------------------------------------------------------
38 |Private procedure: get_invoice_event_source_info
39 +---------------------------------------------------------------*/
40 FUNCTION get_invoice_event_source_info
41 ( p_legal_entity_id IN NUMBER,
42 p_ledger_id IN NUMBER,
43 p_invoice_id IN NUMBER,
44 p_calling_sequence IN VARCHAR2
45 ) RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
46
47 /*---------------------------------------------------------------
48 |Private procedure: get_payment_event_source_info
49 +---------------------------------------------------------------*/
50 FUNCTION get_payment_event_source_info
51 ( p_legal_entity_id IN NUMBER,
52 p_ledger_id IN NUMBER,
53 p_check_id IN NUMBER,
54 p_calling_sequence IN VARCHAR2
55 ) RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
56
57 /*========================================================================
58 | PROCEDURE: DRILLDOWN
59 | COMMENT: DRILLDOWN procedure provides a public API for sla to return
60 | the appropriate information via OUT parameters to open the
61 | appropriate transaction form.
62 | PARAMETERS:
63 | p_application_id : Subledger application internal identifier
64 | p_ledger_id : Event ledger identifier
65 | p_legal_entity_id : Legal entity identifier
66 | p_entity_code : Event entity internal code
67 | p_event_class_code : Event class internal code
68 | p_event_type_code : Event type internal code
69 | p_source_id_int_1 : Generic system transaction identifiers
70 | p_source_id_int_2 : Generic system transaction identifiers
71 | p_source_id_int_3 : Generic system transaction identifiers
72 | p_source_id_int_4 : Generic system transaction identifiers
73 | p_source_id_char_1 : Generic system transaction identifiers
74 | p_source_id_char_2 : Generic system transaction identifiers
75 | p_source_id_char_3 : Generic system transaction identifiers
76 | p_source_id_char_4 : Generic system transaction identifiers
77 | p_security_id_int_1 : Generic system transaction identifiers
78 | p_security_id_int_2 : Generic system transaction identifiers
79 | p_security_id_int_3 : Generic system transaction identifiers
80 | p_security_id_char_1 : Generic system transaction identifiers
81 | p_security_id_char_2 : Generic system transaction identifiers
82 | p_security_id_char_3 : Generic system transaction identifiers
83 | p_valuation_method : Valuation Method internal identifier
84 | p_user_interface_type: This parameter determines the user interface type.
85 | The possible values are FORM, HTML, or NONE.
86 | p_function_name : The name of the Oracle Application Object
87 | Library function defined to open the transaction
88 | form. This parameter is used only if the page
89 | is a FORM page.
90 | p_parameters : An Oracle Application Object Library Function
91 | can have its own arguments/parameters. SLA
92 | expects developers to return these arguments via
93 | p_parameters.
94 |
95 +===========================================================================*/
96
97 PROCEDURE DRILLDOWN
98 (p_application_id IN INTEGER
99 ,p_ledger_id IN INTEGER
100 ,p_legal_entity_id IN INTEGER DEFAULT NULL
101 ,p_entity_code IN VARCHAR2
102 ,p_event_class_code IN VARCHAR2
103 ,p_event_type_code IN VARCHAR2
104 ,p_source_id_int_1 IN INTEGER DEFAULT NULL
105 ,p_source_id_int_2 IN INTEGER DEFAULT NULL
106 ,p_source_id_int_3 IN INTEGER DEFAULT NULL
107 ,p_source_id_int_4 IN INTEGER DEFAULT NULL
108 ,p_source_id_char_1 IN VARCHAR2 DEFAULT NULL
109 ,p_source_id_char_2 IN VARCHAR2 DEFAULT NULL
110 ,p_source_id_char_3 IN VARCHAR2 DEFAULT NULL
111 ,p_source_id_char_4 IN VARCHAR2 DEFAULT NULL
112 ,p_security_id_int_1 IN INTEGER DEFAULT NULL
113 ,p_security_id_int_2 IN INTEGER DEFAULT NULL
114 ,p_security_id_int_3 IN INTEGER DEFAULT NULL
115 ,p_security_id_char_1 IN VARCHAR2 DEFAULT NULL
116 ,p_security_id_char_2 IN VARCHAR2 DEFAULT NULL
117 ,p_security_id_char_3 IN VARCHAR2 DEFAULT NULL
118 ,p_valuation_method IN VARCHAR2 DEFAULT NULL
119 ,p_user_interface_type IN OUT NOCOPY VARCHAR2
120 ,p_function_name IN OUT NOCOPY VARCHAR2
121 ,p_parameters IN OUT NOCOPY VARCHAR2)
122
123 IS
124
125 BEGIN
126
127 -- To check whether the application is AP
128 IF (p_application_id =200) THEN
129
130 IF(p_event_class_code = 'INVOICES') THEN
131 p_user_interface_type := 'FORM';
132 p_function_name := 'XLA_APXINWKB';
133 p_parameters := ' INVOICE_ID="' ||TO_CHAR(p_source_id_int_1) ||'"'
134 ||' ORG_ID="' ||TO_CHAR(p_security_id_int_1) ||'"';
135
136 ELSIF (p_event_class_code = 'CREDIT MEMOS') THEN
137 p_user_interface_type := 'FORM';
138 p_function_name := 'XLA_APXINWKB';
139 p_parameters := ' INVOICE_ID="' || TO_CHAR(p_source_id_int_1)||'"'
140 ||' ORG_ID="'||TO_CHAR(p_security_id_int_1)||'"';
141
142 ELSIF (p_event_class_code = 'DEBIT MEMOS') THEN
143 p_user_interface_type := 'FORM';
144 p_function_name := 'XLA_APXINWKB';
145 p_parameters := ' INVOICE_ID="' || TO_CHAR(p_source_id_int_1)||'"'
146 ||' ORG_ID="'||TO_CHAR(p_security_id_int_1)||'"';
147
148 ELSIF (p_event_class_code = 'PREPAYMENTS') THEN
149 p_user_interface_type := 'FORM';
150 p_function_name := 'XLA_APXINWKB';
151 p_parameters := ' INVOICE_ID="' || TO_CHAR(p_source_id_int_1)||'"'
152 ||' ORG_ID="'||TO_CHAR(p_security_id_int_1)||'"';
153
154 ELSIF (p_event_class_code = 'PREPAYMENT APPLICATIONS') THEN
155 p_user_interface_type := 'FORM';
156 p_function_name := 'XLA_APXINWKB';
157 --bug 7020850
158 p_parameters :=' INVOICE_ID="' || TO_CHAR(p_source_id_int_1)||'"'
159 ||' ORG_ID="'||TO_CHAR(p_security_id_int_1)||'"';
160 /* p_parameters :=' AP_PREPAY_HISTORY_ID="' || TO_CHAR(p_source_id_int_1)||'"'
161 ||' AP_PREPAY_INVOICE_ID="'||TO_CHAR(p_security_id_int_2)||'"'
162 ||' INVOICE_ID="'||TO_CHAR(p_security_id_int_3)||'"'
163 ||' ORG_ID="'||TO_CHAR(p_security_id_int_1)||'"';*/
164
165 ELSIF (p_event_class_code = 'INV SUPPLIER MODIFICATIONS') THEN
166 p_user_interface_type := 'FORM';
167 p_function_name := 'XLA_APXINWKB';
168 p_parameters := ' INVOICE_ID="' || TO_CHAR(p_source_id_int_1)||'"'
169 ||' ORG_ID="'||TO_CHAR(p_security_id_int_1)||'"';
170
171 ELSIF (p_event_class_code = 'PAYMENTS') THEN
172 p_user_interface_type := 'FORM';
173 p_function_name := 'XLA_APXPAWKB';
174 p_parameters := ' CHECK_ID="' || TO_CHAR(p_source_id_int_1)||'"'
175 ||' ORG_ID="'||TO_CHAR(p_security_id_int_1)||'"';
176
177 ELSIF (p_event_class_code = 'REFUNDS') THEN
178 p_user_interface_type := 'FORM';
179 p_function_name := 'XLA_APXPAWKB';
180 p_parameters := 'CHECK_ID="' || TO_CHAR(p_source_id_int_1)||'"'
181 ||' ORG_ID="'||TO_CHAR(p_security_id_int_1)||'"';
182
183 ELSIF (p_event_class_code = 'FUTURE DATED PAYMENTS') THEN
184 p_user_interface_type := 'FORM';
185 p_function_name := 'XLA_APXPAWKB';
186 p_parameters := ' CHECK_ID="' || TO_CHAR(p_source_id_int_1)||'"'
187 ||' ORG_ID="'||TO_CHAR(p_security_id_int_1)||'"';
188
189 ELSIF (p_event_class_code = 'RECONCILED PAYMENTS') THEN
190 p_user_interface_type := 'FORM';
191 p_function_name := 'XLA_APXPAWKB';
192 p_parameters := ' CHECK_ID="' || TO_CHAR(p_source_id_int_1)||'"'
193 ||' ORG_ID="'||TO_CHAR(p_security_id_int_1)||'"';
194
195 ELSIF (p_event_class_code = 'TREASURY PAYMENT ACCOMPLISHMENT') THEN
196 p_user_interface_type := 'FORM';
197 p_function_name := 'XLA_FVXITCRF';
198 p_parameters := ' CHECK_ID="' || TO_CHAR (p_source_id_int_1)||'"'
199 ||' ORG_ID="'||TO_CHAR(p_security_id_int_1)||'"';
200
201 ELSIF (p_event_class_code = 'PMT SUPPLIER MODIFICATIONS') THEN
202 p_user_interface_type := 'FORM';
203 p_function_name := 'XLA_APXPAWKB';
204 p_parameters := ' CHECK_ID="' || TO_CHAR(p_source_id_int_1)||'"'
205 ||' ORG_ID="'||TO_CHAR(p_security_id_int_1)||'"';
206
207 ELSE
208 p_user_interface_type :='NONE';
209 END IF;
210 END IF;
211
212 END DRILLDOWN;
213
214 /*========================================================================
215 | PROCEDURE: INVOICE_ONLINE_ACCOUNTING
216 | COMMENT: Invoice_online_accounting procedure will call the SLA public
217 | API to process the oneline accounting for specific invoice
218 | PARAMETERS: p_invoice_id IN --the invoice will be accounted
219 | p_accounting IN 'D' --Draft mode
220 | 'F' --Final mode
221 | 'P' --Final and post in general ledger
222 | p_errbuf OUT -- Error message
223 | p_retcode OUT -- The retcode OUT prameter returns the success
224 | code back to the caller. If the call is
225 | completed successfully, the return value is
226 | 0(Zero)
227 |
228 +===========================================================================*/
229
230 PROCEDURE INVOICE_ONLINE_ACCOUNTING
231 (p_invoice_id IN NUMBER,
232 p_accounting_mode IN VARCHAR2,
233 p_errbuf OUT NOCOPY VARCHAR2,
234 p_retcode OUT NOCOPY NUMBER,
235 p_calling_sequence IN VARCHAR2)
236
237 IS
238
239 TYPE t_event_ids_type IS TABLE OF xla_events.event_id%TYPE
240 INDEX BY PLS_INTEGER;
241 TYPE t_event_status_type IS TABLE OF xla_events.event_status_code%TYPE
242 INDEX BY PLS_INTEGER; --bug 8547225
243
244 l_org_id NUMBER(15);
245 l_legal_entity_id NUMBER(15);
246 l_ledger_id NUMBER(15);
247 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
248 l_accounting_mode VARCHAR2(1);
249 l_accounting_flag VARCHAR2(1);
250 l_gl_posting_flag VARCHAR2(1);
251 l_transfer_flag VARCHAR2(1);
252 l_accounting_batch_id NUMBER(15);
253 l_request_id NUMBER(15);
254 l_curr_calling_sequence VARCHAR2(2000);
255 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
256
257 ind BINARY_INTEGER := 1;
258 l_event_list t_event_ids_type;
259 l_event_status_list t_event_status_type; --bug 8547225
260 l_t_array_event_info xla_events_pub_pkg.t_array_event_info;
261 l_cash_basis_flag VARCHAR2(1);
262 l_procedure_name CONSTANT VARCHAR2(30) := 'invoice_online_accounting';
263
264 BEGIN
265
266 l_curr_calling_sequence :=
267 p_calling_sequence || ' -> AP_DRILLDOWN_PUB_PKG.INVOICE_ONLINE_ACCOUNTING';
268
269
270 -----------------------------------------------------------------
271 l_log_msg := 'Step 1: Get invoice information';
272 -----------------------------------------------------------------
273
274 get_invoice_info
275 ( p_invoice_id => p_invoice_id,
276 p_org_id => l_org_id, -- OUT
277 p_legal_entity_id => l_legal_entity_id, -- OUT
278 p_ledger_id => l_ledger_id, -- OUT
279 p_calling_sequence => l_curr_calling_sequence
280 );
281 -----------------------------------------------------------------
282 l_log_msg := 'Step 2: get invoice event source info';
283 -----------------------------------------------------------------
284
285 l_event_source_info :=
286 get_invoice_event_source_info
287 ( p_legal_entity_id => l_legal_entity_id,
288 p_ledger_id => l_ledger_id,
289 p_invoice_id => p_invoice_id,
290 p_calling_sequence => l_curr_calling_sequence
291 );
292
293 -----------------------------------------------------------------
294 l_log_msg := 'Step 3: Check accounting method';
295 -----------------------------------------------------------------
296 IF p_accounting_mode = 'D' THEN
297 l_accounting_mode := 'D';
298 l_accounting_flag := 'Y';
299 l_gl_posting_flag := 'N';
300 l_transfer_flag := 'N';
301 ELSIF p_accounting_mode = 'F' THEN
302 l_accounting_mode := 'F';
303 l_accounting_flag := 'Y';
304 l_gl_posting_flag := 'N';
305 l_transfer_flag := 'N';
306 ELSIF p_accounting_mode = 'P' THEN
307 l_accounting_mode := 'F';
308 l_accounting_flag := 'Y';
309 l_gl_posting_flag := 'Y';
310 l_transfer_flag := 'Y';
311 ELSE
312 APP_EXCEPTION.RAISE_EXCEPTION();
313 END IF;
314
315 -----------------------------------------------------------------
316 l_log_msg := 'Step 4: call SLA API';
317 -----------------------------------------------------------------
318 XLA_ACCOUNTING_PUB_PKG.ACCOUNTING_PROGRAM_DOCUMENT (
319 P_event_source_info => l_event_source_info,
320 P_entity_id => null,
321 P_accounting_flag => l_accounting_flag,
322 P_accounting_mode => l_accounting_mode,
323 P_transfer_flag => l_transfer_flag,
324 P_gl_posting_flag => l_gl_posting_flag,
325 P_offline_flag => 'N',
326 P_accounting_batch_id => l_accounting_batch_id, --Out
327 P_errbuf => p_errbuf, --Out
328 P_retcode => p_retcode, --Out
329 P_request_id => l_request_id --Out
330 );
331
332 -----------------------------------------------------------------
333 l_log_msg := 'Step 5: Update the posted flag';
334 -----------------------------------------------------------------
335
336 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
337 l_log_msg := 'After calling online accounting api and out prameter:' ||
338 ' p_retcode =' || p_retcode ||
339 ' accounting_mode =' || l_accounting_mode;
340 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,l_log_msg);
341 END IF;
342
343
344 IF (l_accounting_mode <> 'D') THEN
345
346 l_t_array_event_info := xla_events_pub_pkg.get_array_event_info
347 (p_event_source_info => l_event_source_info
348 ,p_event_class_code => NULL
349 ,p_event_type_code => NULL
350 ,p_event_date => NULL
351 ,p_event_status_code=> NULL
352 ,p_valuation_method => NULL
353 ,p_security_context => NULL);
354
355 IF ( l_t_array_event_info.COUNT <> 0 ) THEN
356
357 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
358 l_log_msg := 'Event processed count is not 0';
359 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
360 l_procedure_name,l_log_msg);
361 END IF;
362
366 l_event_status_list(ind) := l_t_array_event_info(num).event_status_code; --bug 8547225
363 FOR num IN 1 .. l_t_array_event_info.COUNT LOOP
364 IF ( l_t_array_event_info(num).event_status_code <> 'P') THEN
365 l_event_list(ind) := l_t_array_event_info(num).event_id;
367 ind := ind+1;
368 END IF;
369 END LOOP;
370 END IF;
371
372 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
373 l_log_msg := 'Events need to set the posted flag to n count='
374 || to_char(ind) ;
375 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
376 l_procedure_name,l_log_msg);
377 END IF;
378
379 --bug 8547225, for event_status_code 'N' posted_flag updated to'Y'
380 IF (l_event_list.count <> 0 ) THEN
381
382 --bug9444952
383 SELECT nvl(gl.sla_ledger_cash_basis_flag, 'N')
384 INTO l_cash_basis_flag
385 FROM gl_ledgers gl, ap_invoices_all ai
386 WHERE gl.ledger_id = ai.set_of_books_id
387 AND ai.invoice_id = p_invoice_id;
388
389 -- bug9444952 : Setting posted flags to appropriate statuses
390 -- in case the Event_Status_Code is 'N'
391 --
392 FORALL num in 1 .. l_event_list.COUNT
393 UPDATE AP_INVOICE_DISTRIBUTIONS
394 SET POSTED_FLAG = CASE WHEN p_retcode <> 0
395 THEN 'N'
396 WHEN p_retcode = 0 and l_event_status_list(num) = 'N'
397 THEN 'Y'
398 ELSE 'N' -- bug 9464912
399 END,
400 ACCRUAL_POSTED_FLAG =
401 CASE WHEN p_retcode <> 0
402 THEN 'N'
403 WHEN p_retcode = 0 and
404 l_event_status_list(num) = 'N' and
405 l_cash_basis_flag = 'N'
406 THEN 'Y'
407 ELSE 'N'
408 END,
409 CASH_POSTED_FLAG =
410 CASE WHEN p_retcode <> 0
411 THEN 'N'
412 WHEN p_retcode = 0 and
413 l_event_status_list(num) = 'N' and
414 l_cash_basis_flag = 'Y'
415 THEN 'Y'
416 ELSE CASH_POSTED_FLAG
417 END
418 -- bug 10101613
419 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
420 ,LAST_UPDATE_DATE = SYSDATE
421 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
422 WHERE Accounting_Event_ID = l_event_list(num);
423
424 FORALL num in 1 .. l_event_list.COUNT
425 UPDATE ap_prepay_history_all
426 SET POSTED_FLAG = CASE WHEN p_retcode <> 0
427 THEN 'N'
428 WHEN p_retcode = 0 and l_event_status_list(num) = 'N'
429 THEN 'Y'
430 ELSE 'N' -- bug 9464912
431 END
432 -- bug 10101613
433 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
434 ,LAST_UPDATE_DATE = SYSDATE
435 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
436 WHERE Accounting_Event_ID = l_event_list(num);
437
438 l_event_list.DELETE;
439 l_event_status_list.DELETE;
440 l_t_array_event_info.DELETE;
441 END IF;
442
443 END IF;
444
445 EXCEPTION
446 WHEN OTHERS THEN
447 IF (SQLCODE <> -20001) THEN
448 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
449 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
450 l_curr_calling_sequence);
451 FND_MESSAGE.SET_TOKEN('PARAMETERS',
452 ' p_invoice_id = '||p_invoice_id
453 ||' p_accounting_mode = '||p_accounting_mode );
454 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
455 END IF;
456 APP_EXCEPTION.RAISE_EXCEPTION();
457
458 END INVOICE_ONLINE_ACCOUNTING;
459
460
461 /*===========================================================================+
462 | PROCEDURE: PAYMENT_ONLINE_ACCOUNTING
463 | COMMENT: Payment_online_accounting procedure will call the SLA public
464 | API to process the oneline accounting for specific invoice
465 | PARAMETERS: p_check_id IN --the invoice will be accounted
466 | p_accounting_mode IN 'D' --Draft mode
467 | 'F' --Final mode
468 | 'P' --Final and post in general ledger
469 | p_errbuf OUT --Error message
470 | p_ret_code OUT --The retcode OUT prameter returns
471 | the success code back to the caller.
472 | If the call is completed successfully
473 | the return value is 0(Zero)
474 |
475 +===========================================================================*/
476
477 PROCEDURE PAYMENT_ONLINE_ACCOUNTING
478 (p_check_id IN NUMBER,
479 p_accounting_mode IN VARCHAR2,
480 p_errbuf OUT NOCOPY VARCHAR2,
481 p_retcode OUT NOCOPY NUMBER,
482 p_calling_sequence IN VARCHAR2)
483
484 IS
485
486 TYPE t_event_ids_type IS TABLE OF xla_events.event_id%TYPE
487 INDEX BY PLS_INTEGER;
488
492
489 TYPE t_event_status_type IS TABLE OF xla_events.event_status_code%TYPE
490 INDEX BY PLS_INTEGER;
491 l_event_status_list t_event_status_type;
493 l_org_id NUMBER(15);
494 l_legal_entity_id NUMBER(15);
495 l_ledger_id NUMBER(15);
496 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
497 l_accounting_mode VARCHAR2(1);
498 l_accounting_flag VARCHAR2(1);
499 l_gl_posting_flag VARCHAR2(1);
500 l_transfer_flag VARCHAR2(1);
501 l_accounting_batch_id NUMBER(15);
502 l_request_id NUMBER(15);
503 l_curr_calling_sequence VARCHAR2(2000);
504 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
505
506 ind BINARY_INTEGER := 1;
507 l_event_list t_event_ids_type;
508 l_t_array_event_info xla_events_pub_pkg.t_array_event_info;
509 l_procedure_name CONSTANT VARCHAR2(30) := 'payment_online_accounting';
510
511
512 BEGIN
513
514 l_curr_calling_sequence := p_calling_sequence
515 || ' -> AP_DRILLDOWN_PUB_PKG.PAYMENT_ONLINE_ACCOUNTING';
516 -----------------------------------------------------------------
517 l_log_msg := 'Step 1: Get payment information';
518 -----------------------------------------------------------------
519
520 get_payment_info
521 ( p_check_id => p_check_id,
522 p_org_id => l_org_id, -- OUT
523 p_legal_entity_id => l_legal_entity_id, -- OUT
524 p_ledger_id => l_ledger_id, -- OUT
525 p_calling_sequence => l_curr_calling_sequence
526 );
527 -----------------------------------------------------------------
528 l_log_msg := 'Step 2: get payment event source info';
529 -----------------------------------------------------------------
530
531 l_event_source_info :=
532 get_payment_event_source_info
533 ( p_legal_entity_id => l_legal_entity_id,
534 p_ledger_id => l_ledger_id,
535 p_check_id => p_check_id,
536 p_calling_sequence => l_curr_calling_sequence
537 );
538
539 -----------------------------------------------------------------
540 l_log_msg := 'Step 3: Check accounting method';
541 -----------------------------------------------------------------
542 IF p_accounting_mode = 'D' THEN
543 L_accounting_mode := 'D';
544 L_accounting_flag := 'Y';
545 L_gl_posting_flag := 'N';
546 L_transfer_flag := 'N';
547 ELSIF p_accounting_mode = 'F' THEN
548 L_accounting_mode := 'F';
549 L_accounting_flag := 'Y';
550 L_gl_posting_flag := 'N';
551 L_transfer_flag := 'N';
552 ELSIF p_accounting_mode = 'P' THEN
553 L_accounting_mode := 'F';
554 L_accounting_flag := 'Y';
555 L_gl_posting_flag := 'Y';
556 L_transfer_flag := 'Y';
557 ELSE
558 APP_EXCEPTION.RAISE_EXCEPTION();
559 END IF;
560
561 -----------------------------------------------------------------
562 l_log_msg := 'Step 4: call SLA API';
563 -----------------------------------------------------------------
564 XLA_ACCOUNTING_PUB_PKG.ACCOUNTING_PROGRAM_DOCUMENT (
565 P_event_source_info => l_event_source_info,
566 P_entity_id => null,
567 P_accounting_flag => l_accounting_flag,
568 P_accounting_mode => l_accounting_mode,
569 P_transfer_flag => l_transfer_flag,
570 P_gl_posting_flag => l_gl_posting_flag,
571 P_offline_flag => 'N',
572 P_accounting_batch_id => l_accounting_batch_id, --Out
573 P_errbuf => p_errbuf, --Out
574 P_retcode => p_retcode, --Out
575 P_request_id => l_request_id --Out
576 );
577
578 -----------------------------------------------------------------
579 l_log_msg := 'Step 5: Update the posted flag';
580 -----------------------------------------------------------------
581
582 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
583 l_log_msg := 'After calling online accounting api and out prameter:' ||
584 ' p_retcode =' || p_retcode ||
585 ' accounting_mode =' || l_accounting_mode;
586 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,l_log_msg);
587 END IF;
588
589 -- rewrote the following for bug fix 5694577
590 -- When payment accounting option is CLEAR ONLY, need to set the
591 -- posted_flag to 'Y' for payment create and maturity event after online
592 -- accounting
593
594 l_t_array_event_info := xla_events_pub_pkg.get_array_event_info
595 (p_event_source_info => l_event_source_info
596 ,p_event_class_code => NULL
597 ,p_event_type_code => NULL
598 ,p_event_date => NULL
599 ,p_event_status_code=> NULL
600 ,p_valuation_method => NULL
601 ,p_security_context => NULL);
602
603 IF ( l_t_array_event_info.COUNT <> 0 ) THEN
604
605 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
606 l_log_msg := 'Event processed count is not 0';
607 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,l_log_msg);
608 END IF;
609
610 FOR num IN 1 .. l_t_array_event_info.COUNT LOOP
611 IF ( l_t_array_event_info(num).event_status_code <> 'P') THEN
612 l_event_list(ind) := l_t_array_event_info(num).event_id;
613 l_event_status_list(ind) := l_t_array_event_info(num).event_status_code;
614 ind := ind+1;
615 END IF;
616 END LOOP;
617
618 FORALL num in 1 .. l_event_list.COUNT
619 UPDATE AP_invoice_payments_all
620 SET POSTED_FLAG = CASE
624 WHEN l_accounting_mode <> 'D' AND -- Bug 9135877
621 WHEN l_accounting_mode <> 'D' AND
622 p_retcode <> 0 AND
623 l_event_status_list(num) <> 'P' THEN 'N'
625 p_retcode = 0 AND
626 l_event_status_list(num) = 'N' THEN 'Y'
627 ELSE 'N'
628 END,
629 ACCRUAL_POSTED_FLAG =
630 CASE
631 WHEN l_accounting_mode <> 'D' AND
632 p_retcode <> 0 AND
633 l_event_status_list(num) <> 'P' THEN 'N'
634 WHEN l_accounting_mode <> 'D' AND
635 p_retcode = 0 AND
636 l_event_status_list(num) = 'N' THEN 'Y'
637 ELSE 'N'
638 END,
639 CASH_POSTED_FLAG =
640 CASE
641 WHEN l_accounting_mode <> 'D' AND
642 p_retcode <> 0 AND
643 l_event_status_list(num) <> 'P' THEN 'N'
644 WHEN l_accounting_mode <> 'D' AND
645 p_retcode = 0 AND
646 l_event_status_list(num) = 'N' THEN 'Y'
647 ELSE 'N'
648 END
649 -- bug 10101613
650 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
651 ,LAST_UPDATE_DATE = SYSDATE
652 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
653 WHERE Accounting_Event_ID = l_event_list(num);
654
655 FORALL num in 1 .. l_event_list.COUNT
656 UPDATE AP_payment_history_all APH
657 SET APH.POSTED_FLAG = CASE
658 WHEN l_accounting_mode <> 'D' AND
659 p_retcode <> 0 AND
660 l_event_status_list(num) <> 'P' THEN 'N'
661 WHEN l_accounting_mode <> 'D' AND -- Bug 7374984
662 p_retcode = 0 AND
663 l_event_status_list(num) = 'N' THEN 'Y'
664 ELSE 'N'
665 END
666 -- bug 10101613
667 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
668 ,LAST_UPDATE_DATE = SYSDATE
669 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
670 WHERE APH.Accounting_Event_ID = l_event_list(num);
671
672 l_event_list.DELETE;
673 l_event_status_list.DELETE;
674 l_t_array_event_info.DELETE;
675
676 END IF;
677
678 EXCEPTION
679 WHEN OTHERS THEN
680 IF (SQLCODE <> -20001) THEN
681 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
682 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
683 l_curr_calling_sequence);
684 FND_MESSAGE.SET_TOKEN('PARAMETERS',
685 ' p_check_id = '||p_check_id
686 ||' p_accounting_mode = '||p_accounting_mode );
687 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
688 END IF;
689 APP_EXCEPTION.RAISE_EXCEPTION();
690
691 END PAYMENT_ONLINE_ACCOUNTING;
692
693 /*============================================================================
694 | FUNCTION - GET_INVOICE_EVENT_SOURCE_INFO(PRIVATE)
695 |
696 | DESCRIPTION
697 | This function is used to get invoice event source information
698 |
699 | PRAMETERS:
700 | p_legal_entity_id: Legal entity ID
701 | p_ledger_id: Ledger ID
702 | p_invoice_id: Invoice ID
703 | p_calling_sequence: Debug information
704 |
705 | RETURN: XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
706 |
707 | KNOWN ISSUES:
708 |
709 | NOTES:
710 |
711 | MODIFICATION HISTORY
712 | Date Author Description of Change
713 |
714 *===========================================================================*/
715 FUNCTION get_invoice_event_source_info(
716 p_legal_entity_id IN NUMBER,
717 p_ledger_id IN NUMBER,
718 p_invoice_id IN NUMBER,
719 p_calling_sequence IN VARCHAR2)
720 RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
721 IS
722
723 l_invoice_num VARCHAR2(50);
724 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
725 l_curr_calling_sequence VARCHAR2(2000);
726 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
727
728 BEGIN
729
730 l_curr_calling_sequence := p_calling_sequence
731 || ' -> AP_DRILLDOWN_PUB_PKG.get_invoice_event_source_info';
732
733 ----------------------------------------------------------------
734 l_log_msg :='get invoice_num information';
735 ----------------------------------------------------------------
736
737 select invoice_num
738 into l_invoice_num
739 from ap_invoices
740 where invoice_id = p_invoice_id;
741
742 ----------------------------------------------------------------
743 l_log_msg :='get event source information';
744 ----------------------------------------------------------------
745
746 l_event_source_info.application_id := 200;
747 l_event_source_info.legal_entity_id := p_legal_entity_id;
748 l_event_source_info.ledger_id := p_ledger_id;
749 l_event_source_info.entity_type_code := 'AP_INVOICES';
750 l_event_source_info.transaction_number := l_invoice_num;
751 l_event_source_info.source_id_int_1 := p_invoice_id;
752
753 RETURN l_event_source_info;
754
755 EXCEPTION
756 WHEN OTHERS THEN
757 IF (SQLCODE <> -20001) THEN
758 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
759 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
760 l_curr_calling_sequence);
761 FND_MESSAGE.SET_TOKEN('PARAMETERS',
762 ' p_ledger_id = '||p_ledger_id
766 END IF;
763 ||' p_legal_entity_id = '||p_legal_entity_id
764 ||' p_invoice_id = '||p_invoice_id );
765 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
767 APP_EXCEPTION.RAISE_EXCEPTION();
768
769 END get_invoice_event_source_info;
770
771
772 /*============================================================================
773 | FUNCTION - GET_PAYMENT_EVENT_SOURCE_INFO(PRIVATE)
774 |
775 | DESCRIPTION
776 | This procedure is used to get payment event source information.
777 |
778 | PRAMETERS:
779 | p_legal_entity_id: Legal Entity ID
780 | p_ledger_id: Ledger ID
781 | p_invoice_id: Invoice ID
782 | p_calling_sequence: Debug information
783 |
784 | RETURN: XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
785 |
786 | KNOWN ISSUES:
787 |
788 | NOTES:
789 |
790 | MODIFICATION HISTORY
791 | Date Author Description of Change
792 |
793 *===========================================================================*/
794 FUNCTION get_payment_event_source_info(
795 p_legal_entity_id IN NUMBER,
796 p_ledger_id IN NUMBER,
797 p_check_id IN NUMBER,
798 p_calling_sequence IN VARCHAR2)
799 RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
800 IS
801
802 l_check_number NUMBER(15);
803 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
804 l_curr_calling_sequence VARCHAR2(2000);
805 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
806
807 BEGIN
808
809 l_curr_calling_sequence := p_calling_sequence
810 || ' -> AP_DRILLDOWN_PUB_PKG.get_payment_event_source_info';
811
812 ---------------------------------------------------------------
813 l_log_msg :='get check_number';
814 ---------------------------------------------------------------
815 select check_number
816 into l_check_number
817 from ap_checks
818 where check_id = p_check_id;
819
820 ---------------------------------------------------------------
821 l_log_msg :='get event source information';
822 ---------------------------------------------------------------
823 l_event_source_info.application_id := 200;
824 l_event_source_info.legal_entity_id := p_legal_entity_id;
825 l_event_source_info.ledger_id := p_ledger_id;
826 l_event_source_info.entity_type_code := 'AP_PAYMENTS';
827 l_event_source_info.transaction_number := l_check_number;
828 l_event_source_info.source_id_int_1 := p_check_id;
829
830
831 RETURN l_event_source_info;
832
833 EXCEPTION
834 WHEN OTHERS THEN
835 IF (SQLCODE <> -20001) THEN
836 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
837 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
838 l_curr_calling_sequence);
839 FND_MESSAGE.SET_TOKEN('PARAMETERS',
840 ' p_ledger_id = '||p_ledger_id
841 ||' p_legal_entity_id = '||p_legal_entity_id
842 ||' p_check_id = '||p_check_id );
843 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
844 END IF;
845 APP_EXCEPTION.RAISE_EXCEPTION();
846
847 END get_payment_event_source_info;
848
849
850 /*============================================================================
851 | PROCEDURE - GET_INVOICE_INFO(PRIVATE)
852 |
853 | DESCRIPTION
854 | This procedure is used to get invoice information.
855 |
856 | PRAMETERS:
857 | p_invoice_id: Invoice ID
858 | p_org_id: Organization ID
859 | p_legal_entity_id: Legal Entity ID
860 | p_ledger_id: Ledger ID
861 | p_calling_sequence: Debug information
862 |
863 | KNOWN ISSUES:
864 |
865 | NOTES:
866 |
867 | MODIFICATION HISTORY
868 | Date Author Description of Change
869 |
870 *===========================================================================*/
871 PROCEDURE get_invoice_info(
872 p_invoice_id IN NUMBER,
873 p_org_id OUT NOCOPY NUMBER,
874 p_legal_entity_id OUT NOCOPY NUMBER,
875 p_ledger_id OUT NOCOPY NUMBER,
876 p_calling_sequence IN VARCHAR2)
877 IS
878
879 l_curr_calling_sequence VARCHAR2(2000);
880 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
881
882 BEGIN
883
884 l_curr_calling_sequence :=
885 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.GET_INVOICE_INFO';
886
887 ----------------------------------------------------------------------
888 l_log_msg :='get org information';
889 ----------------------------------------------------------------------
890 SELECT
891 AI.org_id,
892 AI.legal_entity_id,
893 AI.set_of_books_id
894 INTO
895 p_org_id,
896 p_legal_entity_id,
897 p_ledger_id
898 FROM
899 ap_invoices AI
900 WHERE
901 AI.invoice_id = p_invoice_id;
902
903 EXCEPTION
904 WHEN OTHERS THEN
905 IF (SQLCODE <> -20001) THEN
906 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
907 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
908 l_curr_calling_sequence);
909 FND_MESSAGE.SET_TOKEN('PARAMETERS',
910 'p_org_id = '||p_org_id);
911 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
912 END IF;
913 APP_EXCEPTION.RAISE_EXCEPTION();
914 END get_invoice_info;
915
916
917 /*============================================================================
918 | PROCEDURE - GET_PAYMENT_INFO(PRIVATE)
919 |
920 | DESCRIPTION
924 | p_check_id: Check ID
921 | This procedure is used to get payment information.
922 |
923 | PRAMETERS:
925 | p_org_id: Organization ID
926 | p_legal_entity_id: Legal entity ID
927 | p_ledger_id: Ledger ID
928 | p_calling_sequence: Debug information
929 |
930 | KNOWN ISSUES:
931 |
932 | NOTES:
933 |
934 | MODIFICATION HISTORY
935 | Date Author Description of Change
936 |
937 *===========================================================================*/
938 PROCEDURE get_payment_info(
939 p_check_id IN NUMBER,
940 p_org_id OUT NOCOPY NUMBER,
941 p_legal_entity_id OUT NOCOPY NUMBER,
942 p_ledger_id OUT NOCOPY NUMBER,
943 p_calling_sequence IN VARCHAR2)
944 IS
945
946 l_curr_calling_sequence VARCHAR2(2000);
947 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
948
949 BEGIN
950
951 l_curr_calling_sequence :=
952 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.GET_PAYMENT_INFO';
953
954 --------------------------------------------------------------------
955 l_log_msg :='get org information';
956 --------------------------------------------------------------------
957 SELECT AC.org_id,
958 AC.legal_entity_id
959 INTO p_org_id,
960 p_legal_entity_id
961 FROM ap_checks AC
962 WHERE AC.check_id = p_check_id;
963
964 --------------------------------------------------------------------
965 l_log_msg :='get ledger information';
966 --------------------------------------------------------------------
967 SELECT AIP.set_of_books_id
968 INTO p_ledger_id
969 FROM ap_invoice_payments AIP
970 WHERE AIP.check_id = p_check_id
971 AND ROWNUM = 1;
972
973
974 EXCEPTION
975 WHEN OTHERS THEN
976 IF (SQLCODE <> -20001) THEN
977 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
978 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
979 l_curr_calling_sequence);
980 FND_MESSAGE.SET_TOKEN('PARAMETERS',
981 'p_check_id = '||p_check_id);
982 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
983 END IF;
984 APP_EXCEPTION.RAISE_EXCEPTION();
985 END get_payment_info;
986
987 END AP_DRILLDOWN_PUB_PKG;
988