[Home] [Help]
PACKAGE BODY: APPS.AP_DRILLDOWN_PUB_PKG
Source
1 PACKAGE BODY AP_DRILLDOWN_PUB_PKG as
2 /* $Header: apsladrb.pls 120.6.12010000.3 2008/09/08 20:20:22 hredredd 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
242 l_org_id NUMBER(15);
243 l_legal_entity_id NUMBER(15);
244 l_ledger_id NUMBER(15);
245 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
246 l_accounting_mode VARCHAR2(1);
247 l_accounting_flag VARCHAR2(1);
248 l_gl_posting_flag VARCHAR2(1);
249 l_transfer_flag VARCHAR2(1);
250 l_accounting_batch_id NUMBER(15);
251 l_request_id NUMBER(15);
252 l_curr_calling_sequence VARCHAR2(2000);
253 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
254
255 ind BINARY_INTEGER := 1;
256 l_event_list t_event_ids_type;
257 l_t_array_event_info xla_events_pub_pkg.t_array_event_info;
258 l_procedure_name CONSTANT VARCHAR2(30) := 'invoice_online_accounting';
259
260 BEGIN
261
262 l_curr_calling_sequence :=
263 p_calling_sequence || ' -> AP_DRILLDOWN_PUB_PKG.INVOICE_ONLINE_ACCOUNTING';
264
265
266 -----------------------------------------------------------------
267 l_log_msg := 'Step 1: Get invoice information';
268 -----------------------------------------------------------------
269
270 get_invoice_info
271 ( p_invoice_id => p_invoice_id,
272 p_org_id => l_org_id, -- OUT
273 p_legal_entity_id => l_legal_entity_id, -- OUT
274 p_ledger_id => l_ledger_id, -- OUT
275 p_calling_sequence => l_curr_calling_sequence
276 );
277 -----------------------------------------------------------------
278 l_log_msg := 'Step 2: get invoice event source info';
279 -----------------------------------------------------------------
280
281 l_event_source_info :=
282 get_invoice_event_source_info
283 ( p_legal_entity_id => l_legal_entity_id,
284 p_ledger_id => l_ledger_id,
285 p_invoice_id => p_invoice_id,
286 p_calling_sequence => l_curr_calling_sequence
287 );
288
289 -----------------------------------------------------------------
290 l_log_msg := 'Step 3: Check accounting method';
291 -----------------------------------------------------------------
292 IF p_accounting_mode = 'D' THEN
293 l_accounting_mode := 'D';
294 l_accounting_flag := 'Y';
295 l_gl_posting_flag := 'N';
296 l_transfer_flag := 'N';
297 ELSIF p_accounting_mode = 'F' THEN
298 l_accounting_mode := 'F';
299 l_accounting_flag := 'Y';
300 l_gl_posting_flag := 'N';
301 l_transfer_flag := 'N';
302 ELSIF p_accounting_mode = 'P' THEN
303 l_accounting_mode := 'F';
304 l_accounting_flag := 'Y';
305 l_gl_posting_flag := 'Y';
309 END IF;
306 l_transfer_flag := 'Y';
307 ELSE
308 APP_EXCEPTION.RAISE_EXCEPTION();
310
311 -----------------------------------------------------------------
312 l_log_msg := 'Step 4: call SLA API';
313 -----------------------------------------------------------------
314 XLA_ACCOUNTING_PUB_PKG.ACCOUNTING_PROGRAM_DOCUMENT (
315 P_event_source_info => l_event_source_info,
316 P_entity_id => null,
317 P_accounting_flag => l_accounting_flag,
318 P_accounting_mode => l_accounting_mode,
319 P_transfer_flag => l_transfer_flag,
320 P_gl_posting_flag => l_gl_posting_flag,
321 P_offline_flag => 'N',
322 P_accounting_batch_id => l_accounting_batch_id, --Out
323 P_errbuf => p_errbuf, --Out
324 P_retcode => p_retcode, --Out
325 P_request_id => l_request_id --Out
326 );
327
328 -----------------------------------------------------------------
329 l_log_msg := 'Step 5: Update the posted flag';
330 -----------------------------------------------------------------
331
332 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
333 l_log_msg := 'After calling online accounting api and out prameter:' ||
334 ' p_retcode =' || p_retcode ||
335 ' accounting_mode =' || l_accounting_mode;
336 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,l_log_msg);
337 END IF;
338
339
340 IF ( l_accounting_mode <> 'D' AND p_retcode <> 0 ) THEN
341
342 l_t_array_event_info := xla_events_pub_pkg.get_array_event_info
343 (p_event_source_info => l_event_source_info
344 ,p_event_class_code => NULL
345 ,p_event_type_code => NULL
346 ,p_event_date => NULL
347 ,p_event_status_code=> NULL
348 ,p_valuation_method => NULL
349 ,p_security_context => NULL);
350
351 IF ( l_t_array_event_info.COUNT <> 0 ) THEN
352
353 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
354 l_log_msg := 'Event processed count is not 0';
355 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
356 l_procedure_name,l_log_msg);
357 END IF;
358
362 ind := ind+1;
359 FOR num IN 1 .. l_t_array_event_info.COUNT LOOP
360 IF ( l_t_array_event_info(num).event_status_code <> 'P') THEN
361 l_event_list(ind) := l_t_array_event_info(num).event_id;
363 END IF;
364 END LOOP;
365 END IF;
366
367 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
368 l_log_msg := 'Events need to set the posted flag to n count='
369 || to_char(ind) ;
370 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||
371 l_procedure_name,l_log_msg);
372 END IF;
373
374
375 IF (l_event_list.count <> 0 ) THEN
376 FORALL num in 1 .. l_event_list.COUNT
377 UPDATE AP_INVOICE_DISTRIBUTIONS
378 SET POSTED_FLAG = 'N'
379 WHERE Accounting_Event_ID = l_event_list(num);
380
381 FORALL num in 1 .. l_event_list.COUNT
382 UPDATE ap_prepay_history_all
383 SET POSTED_FLAG = 'N'
384 WHERE Accounting_Event_ID = l_event_list(num);
385
386 l_event_list.DELETE;
387 l_t_array_event_info.DELETE;
388 END IF;
389
390 END IF;
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 IF (SQLCODE <> -20001) THEN
395 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
396 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
397 l_curr_calling_sequence);
398 FND_MESSAGE.SET_TOKEN('PARAMETERS',
399 ' p_invoice_id = '||p_invoice_id
400 ||' p_accounting_mode = '||p_accounting_mode );
401 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
402 END IF;
403 APP_EXCEPTION.RAISE_EXCEPTION();
404
405 END INVOICE_ONLINE_ACCOUNTING;
406
407
408 /*===========================================================================+
409 | PROCEDURE: PAYMENT_ONLINE_ACCOUNTING
410 | COMMENT: Payment_online_accounting procedure will call the SLA public
411 | API to process the oneline accounting for specific invoice
412 | PARAMETERS: p_check_id IN --the invoice will be accounted
413 | p_accounting_mode IN 'D' --Draft mode
414 | 'F' --Final mode
415 | 'P' --Final and post in general ledger
416 | p_errbuf OUT --Error message
417 | p_ret_code OUT --The retcode OUT prameter returns
418 | the success code back to the caller.
419 | If the call is completed successfully
420 | the return value is 0(Zero)
421 |
422 +===========================================================================*/
423
424 PROCEDURE PAYMENT_ONLINE_ACCOUNTING
425 (p_check_id IN NUMBER,
426 p_accounting_mode IN VARCHAR2,
427 p_errbuf OUT NOCOPY VARCHAR2,
428 p_retcode OUT NOCOPY NUMBER,
429 p_calling_sequence IN VARCHAR2)
430
431 IS
432
433 TYPE t_event_ids_type IS TABLE OF xla_events.event_id%TYPE
434 INDEX BY PLS_INTEGER;
435
436 TYPE t_event_status_type IS TABLE OF xla_events.event_status_code%TYPE
437 INDEX BY PLS_INTEGER;
438 l_event_status_list t_event_status_type;
439
440 l_org_id NUMBER(15);
441 l_legal_entity_id NUMBER(15);
442 l_ledger_id NUMBER(15);
443 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
444 l_accounting_mode VARCHAR2(1);
445 l_accounting_flag VARCHAR2(1);
446 l_gl_posting_flag VARCHAR2(1);
447 l_transfer_flag VARCHAR2(1);
448 l_accounting_batch_id NUMBER(15);
449 l_request_id NUMBER(15);
450 l_curr_calling_sequence VARCHAR2(2000);
451 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
452
453 ind BINARY_INTEGER := 1;
454 l_event_list t_event_ids_type;
455 l_t_array_event_info xla_events_pub_pkg.t_array_event_info;
456 l_procedure_name CONSTANT VARCHAR2(30) := 'payment_online_accounting';
457
458
459 BEGIN
460
461 l_curr_calling_sequence := p_calling_sequence
462 || ' -> AP_DRILLDOWN_PUB_PKG.PAYMENT_ONLINE_ACCOUNTING';
463 -----------------------------------------------------------------
464 l_log_msg := 'Step 1: Get payment information';
465 -----------------------------------------------------------------
466
467 get_payment_info
468 ( p_check_id => p_check_id,
469 p_org_id => l_org_id, -- OUT
470 p_legal_entity_id => l_legal_entity_id, -- OUT
471 p_ledger_id => l_ledger_id, -- OUT
472 p_calling_sequence => l_curr_calling_sequence
473 );
474 -----------------------------------------------------------------
475 l_log_msg := 'Step 2: get payment event source info';
476 -----------------------------------------------------------------
477
478 l_event_source_info :=
479 get_payment_event_source_info
480 ( p_legal_entity_id => l_legal_entity_id,
481 p_ledger_id => l_ledger_id,
482 p_check_id => p_check_id,
483 p_calling_sequence => l_curr_calling_sequence
484 );
485
489 IF p_accounting_mode = 'D' THEN
486 -----------------------------------------------------------------
487 l_log_msg := 'Step 3: Check accounting method';
488 -----------------------------------------------------------------
490 L_accounting_mode := 'D';
491 L_accounting_flag := 'Y';
492 L_gl_posting_flag := 'N';
493 L_transfer_flag := 'N';
494 ELSIF p_accounting_mode = 'F' THEN
495 L_accounting_mode := 'F';
496 L_accounting_flag := 'Y';
497 L_gl_posting_flag := 'N';
498 L_transfer_flag := 'N';
499 ELSIF p_accounting_mode = 'P' THEN
500 L_accounting_mode := 'F';
501 L_accounting_flag := 'Y';
502 L_gl_posting_flag := 'Y';
503 L_transfer_flag := 'Y';
504 ELSE
505 APP_EXCEPTION.RAISE_EXCEPTION();
506 END IF;
507
508 -----------------------------------------------------------------
509 l_log_msg := 'Step 4: call SLA API';
510 -----------------------------------------------------------------
511 XLA_ACCOUNTING_PUB_PKG.ACCOUNTING_PROGRAM_DOCUMENT (
512 P_event_source_info => l_event_source_info,
513 P_entity_id => null,
514 P_accounting_flag => l_accounting_flag,
515 P_accounting_mode => l_accounting_mode,
516 P_transfer_flag => l_transfer_flag,
517 P_gl_posting_flag => l_gl_posting_flag,
518 P_offline_flag => 'N',
519 P_accounting_batch_id => l_accounting_batch_id, --Out
520 P_errbuf => p_errbuf, --Out
521 P_retcode => p_retcode, --Out
522 P_request_id => l_request_id --Out
523 );
524
525 -----------------------------------------------------------------
526 l_log_msg := 'Step 5: Update the posted flag';
527 -----------------------------------------------------------------
528
529 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
530 l_log_msg := 'After calling online accounting api and out prameter:' ||
531 ' p_retcode =' || p_retcode ||
532 ' accounting_mode =' || l_accounting_mode;
533 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,l_log_msg);
534 END IF;
535
536 -- rewrote the following for bug fix 5694577
537 -- When payment accounting option is CLEAR ONLY, need to set the
538 -- posted_flag to 'Y' for payment create and maturity event after online
539 -- accounting
540
541 l_t_array_event_info := xla_events_pub_pkg.get_array_event_info
542 (p_event_source_info => l_event_source_info
543 ,p_event_class_code => NULL
544 ,p_event_type_code => NULL
545 ,p_event_date => NULL
546 ,p_event_status_code=> NULL
547 ,p_valuation_method => NULL
548 ,p_security_context => NULL);
549
550 IF ( l_t_array_event_info.COUNT <> 0 ) THEN
551
552 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
553 l_log_msg := 'Event processed count is not 0';
554 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,l_log_msg);
555 END IF;
556
557 FOR num IN 1 .. l_t_array_event_info.COUNT LOOP
558 l_event_list(ind) := l_t_array_event_info(num).event_id;
559 l_event_status_list(ind) := l_t_array_event_info(num).event_status_code;
560 ind := ind+1;
561 END LOOP;
562
563 FORALL num in 1 .. l_event_list.COUNT
564 UPDATE AP_invoice_payments_all
565 SET POSTED_FLAG = CASE WHEN l_accounting_mode <> 'D'
566 AND p_retcode <> 0
567 AND l_event_status_list(num) <> 'P'
568 THEN 'N'
569 WHEN l_accounting_mode <> 'D'
570 AND p_retcode = 0
571 AND l_event_status_list(num) = 'U'
572 AND EXISTS(SELECT 1
573 FROM ap_system_parameters asp, ap_payment_history_all aph
574 WHERE asp.when_to_account_pmt = 'CLEARING ONLY'
575 --AND asp.org_id = l_org_id
576 AND asp.org_id = aph.org_id
577 AND aph.accounting_event_id = l_event_list(num)
578 AND aph.transaction_type in ('PAYMENT CREATED', 'PAYMENT MATURITY')
579 )
580 THEN 'Y'
581 ELSE POSTED_FLAG
582 END
583 WHERE Accounting_Event_ID = l_event_list(num);
584
585 FORALL num in 1 .. l_event_list.COUNT
586 UPDATE AP_payment_history_all APH
587 SET APH.POSTED_FLAG = CASE WHEN l_accounting_mode <> 'D'
588 AND p_retcode <> 0
589 AND l_event_status_list(num) <> 'P'
590 THEN 'N'
591 WHEN l_accounting_mode <> 'D'
592 AND p_retcode = 0
593 AND l_event_status_list(num) = 'U'
594 AND EXISTS(SELECT 1
595 FROM ap_system_parameters asp
599 AND aph.accounting_event_id = l_event_list(num)
596 WHERE asp.when_to_account_pmt = 'CLEARING ONLY'
597 --AND asp.org_id = l_org_id
598 AND asp.org_id = aph.org_id
600 AND aph.transaction_type in ('PAYMENT CREATED', 'PAYMENT MATURITY')
601 )
602 THEN 'Y'
603 WHEN l_accounting_mode <> 'D' -- Bug 7374984
604 AND p_retcode = 0
605 AND l_event_status_list(num) = 'N'
606 THEN 'Y'
607 ELSE POSTED_FLAG
608 END
609 WHERE APH.Accounting_Event_ID = l_event_list(num);
610
611 l_event_list.DELETE;
612 l_event_status_list.DELETE;
613 l_t_array_event_info.DELETE;
614
615 END IF;
616
617 EXCEPTION
618 WHEN OTHERS THEN
619 IF (SQLCODE <> -20001) THEN
620 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
621 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
622 l_curr_calling_sequence);
623 FND_MESSAGE.SET_TOKEN('PARAMETERS',
624 ' p_check_id = '||p_check_id
625 ||' p_accounting_mode = '||p_accounting_mode );
626 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
627 END IF;
628 APP_EXCEPTION.RAISE_EXCEPTION();
629
630 END PAYMENT_ONLINE_ACCOUNTING;
631
632 /*============================================================================
633 | FUNCTION - GET_INVOICE_EVENT_SOURCE_INFO(PRIVATE)
634 |
635 | DESCRIPTION
636 | This function is used to get invoice event source information
637 |
638 | PRAMETERS:
639 | p_legal_entity_id: Legal entity ID
640 | p_ledger_id: Ledger ID
641 | p_invoice_id: Invoice ID
642 | p_calling_sequence: Debug information
643 |
644 | RETURN: XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
645 |
646 | KNOWN ISSUES:
647 |
648 | NOTES:
649 |
650 | MODIFICATION HISTORY
651 | Date Author Description of Change
652 |
653 *===========================================================================*/
654 FUNCTION get_invoice_event_source_info(
655 p_legal_entity_id IN NUMBER,
656 p_ledger_id IN NUMBER,
657 p_invoice_id IN NUMBER,
658 p_calling_sequence IN VARCHAR2)
659 RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
660 IS
661
662 l_invoice_num VARCHAR2(50);
663 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
664 l_curr_calling_sequence VARCHAR2(2000);
665 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
666
667 BEGIN
668
669 l_curr_calling_sequence := p_calling_sequence
670 || ' -> AP_DRILLDOWN_PUB_PKG.get_invoice_event_source_info';
671
672 ----------------------------------------------------------------
673 l_log_msg :='get invoice_num information';
674 ----------------------------------------------------------------
675
676 select invoice_num
677 into l_invoice_num
678 from ap_invoices
679 where invoice_id = p_invoice_id;
680
681 ----------------------------------------------------------------
682 l_log_msg :='get event source information';
683 ----------------------------------------------------------------
684
685 l_event_source_info.application_id := 200;
686 l_event_source_info.legal_entity_id := p_legal_entity_id;
687 l_event_source_info.ledger_id := p_ledger_id;
688 l_event_source_info.entity_type_code := 'AP_INVOICES';
689 l_event_source_info.transaction_number := l_invoice_num;
690 l_event_source_info.source_id_int_1 := p_invoice_id;
691
692 RETURN l_event_source_info;
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 IF (SQLCODE <> -20001) THEN
697 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
698 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
699 l_curr_calling_sequence);
700 FND_MESSAGE.SET_TOKEN('PARAMETERS',
701 ' p_ledger_id = '||p_ledger_id
702 ||' p_legal_entity_id = '||p_legal_entity_id
703 ||' p_invoice_id = '||p_invoice_id );
704 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
705 END IF;
706 APP_EXCEPTION.RAISE_EXCEPTION();
707
708 END get_invoice_event_source_info;
709
710
711 /*============================================================================
712 | FUNCTION - GET_PAYMENT_EVENT_SOURCE_INFO(PRIVATE)
713 |
714 | DESCRIPTION
715 | This procedure is used to get payment event source information.
716 |
717 | PRAMETERS:
718 | p_legal_entity_id: Legal Entity ID
719 | p_ledger_id: Ledger ID
720 | p_invoice_id: Invoice ID
721 | p_calling_sequence: Debug information
722 |
723 | RETURN: XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
724 |
725 | KNOWN ISSUES:
726 |
727 | NOTES:
728 |
729 | MODIFICATION HISTORY
730 | Date Author Description of Change
731 |
735 p_ledger_id IN NUMBER,
732 *===========================================================================*/
733 FUNCTION get_payment_event_source_info(
734 p_legal_entity_id IN NUMBER,
736 p_check_id IN NUMBER,
737 p_calling_sequence IN VARCHAR2)
738 RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
739 IS
740
741 l_check_number NUMBER(15);
742 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
743 l_curr_calling_sequence VARCHAR2(2000);
744 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
745
746 BEGIN
747
748 l_curr_calling_sequence := p_calling_sequence
749 || ' -> AP_DRILLDOWN_PUB_PKG.get_payment_event_source_info';
750
751 ---------------------------------------------------------------
752 l_log_msg :='get check_number';
753 ---------------------------------------------------------------
754 select check_number
755 into l_check_number
756 from ap_checks
757 where check_id = p_check_id;
758
759 ---------------------------------------------------------------
760 l_log_msg :='get event source information';
761 ---------------------------------------------------------------
762 l_event_source_info.application_id := 200;
763 l_event_source_info.legal_entity_id := p_legal_entity_id;
764 l_event_source_info.ledger_id := p_ledger_id;
765 l_event_source_info.entity_type_code := 'AP_PAYMENTS';
766 l_event_source_info.transaction_number := l_check_number;
767 l_event_source_info.source_id_int_1 := p_check_id;
768
769
770 RETURN l_event_source_info;
771
772 EXCEPTION
773 WHEN OTHERS THEN
774 IF (SQLCODE <> -20001) THEN
775 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
776 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
777 l_curr_calling_sequence);
778 FND_MESSAGE.SET_TOKEN('PARAMETERS',
779 ' p_ledger_id = '||p_ledger_id
780 ||' p_legal_entity_id = '||p_legal_entity_id
781 ||' p_check_id = '||p_check_id );
782 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
783 END IF;
784 APP_EXCEPTION.RAISE_EXCEPTION();
785
786 END get_payment_event_source_info;
787
788
789 /*============================================================================
790 | PROCEDURE - GET_INVOICE_INFO(PRIVATE)
791 |
792 | DESCRIPTION
793 | This procedure is used to get invoice information.
794 |
795 | PRAMETERS:
796 | p_invoice_id: Invoice ID
797 | p_org_id: Organization ID
798 | p_legal_entity_id: Legal Entity ID
799 | p_ledger_id: Ledger ID
800 | p_calling_sequence: Debug information
801 |
802 | KNOWN ISSUES:
803 |
804 | NOTES:
805 |
806 | MODIFICATION HISTORY
807 | Date Author Description of Change
808 |
809 *===========================================================================*/
810 PROCEDURE get_invoice_info(
811 p_invoice_id IN NUMBER,
812 p_org_id OUT NOCOPY NUMBER,
813 p_legal_entity_id OUT NOCOPY NUMBER,
814 p_ledger_id OUT NOCOPY NUMBER,
815 p_calling_sequence IN VARCHAR2)
816 IS
817
818 l_curr_calling_sequence VARCHAR2(2000);
819 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
820
821 BEGIN
822
823 l_curr_calling_sequence :=
824 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.GET_INVOICE_INFO';
825
826 ----------------------------------------------------------------------
827 l_log_msg :='get org information';
828 ----------------------------------------------------------------------
829 SELECT
830 AI.org_id,
831 AI.legal_entity_id,
832 AI.set_of_books_id
833 INTO
834 p_org_id,
835 p_legal_entity_id,
836 p_ledger_id
837 FROM
838 ap_invoices AI
839 WHERE
840 AI.invoice_id = p_invoice_id;
841
842 EXCEPTION
843 WHEN OTHERS THEN
844 IF (SQLCODE <> -20001) THEN
845 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
846 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
847 l_curr_calling_sequence);
848 FND_MESSAGE.SET_TOKEN('PARAMETERS',
849 'p_org_id = '||p_org_id);
850 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
851 END IF;
852 APP_EXCEPTION.RAISE_EXCEPTION();
853 END get_invoice_info;
854
855
856 /*============================================================================
857 | PROCEDURE - GET_PAYMENT_INFO(PRIVATE)
858 |
859 | DESCRIPTION
860 | This procedure is used to get payment information.
861 |
862 | PRAMETERS:
863 | p_check_id: Check ID
864 | p_org_id: Organization ID
865 | p_legal_entity_id: Legal entity ID
866 | p_ledger_id: Ledger ID
867 | p_calling_sequence: Debug information
868 |
869 | KNOWN ISSUES:
870 |
871 | NOTES:
872 |
873 | MODIFICATION HISTORY
874 | Date Author Description of Change
875 |
876 *===========================================================================*/
877 PROCEDURE get_payment_info(
878 p_check_id IN NUMBER,
879 p_org_id OUT NOCOPY NUMBER,
880 p_legal_entity_id OUT NOCOPY NUMBER,
881 p_ledger_id OUT NOCOPY NUMBER,
882 p_calling_sequence IN VARCHAR2)
883 IS
884
885 l_curr_calling_sequence VARCHAR2(2000);
886 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
887
888 BEGIN
889
890 l_curr_calling_sequence :=
891 p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.GET_PAYMENT_INFO';
892
893 --------------------------------------------------------------------
894 l_log_msg :='get org information';
895 --------------------------------------------------------------------
896 SELECT AC.org_id,
897 AC.legal_entity_id
898 INTO p_org_id,
899 p_legal_entity_id
900 FROM ap_checks AC
901 WHERE AC.check_id = p_check_id;
902
903 --------------------------------------------------------------------
904 l_log_msg :='get ledger information';
905 --------------------------------------------------------------------
906 SELECT AIP.set_of_books_id
907 INTO p_ledger_id
908 FROM ap_invoice_payments AIP
909 WHERE AIP.check_id = p_check_id
910 AND ROWNUM = 1;
911
912
913 EXCEPTION
914 WHEN OTHERS THEN
915 IF (SQLCODE <> -20001) THEN
916 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
917 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
918 l_curr_calling_sequence);
919 FND_MESSAGE.SET_TOKEN('PARAMETERS',
920 'p_check_id = '||p_check_id);
921 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_log_msg);
922 END IF;
923 APP_EXCEPTION.RAISE_EXCEPTION();
924 END get_payment_info;
925
926 END AP_DRILLDOWN_PUB_PKG;
927