[Home] [Help]
PACKAGE BODY: APPS.FV_TREASURY_PAYMENTS_PKG
Source
1 PACKAGE BODY FV_TREASURY_PAYMENTS_PKG AS
2 -- $Header: FVAPPAYB.pls 120.31.12020000.5 2013/02/12 20:56:35 snama ship $
3
4 g_module_name VARCHAR2(200) := 'fv.plsql.fv_treasury_payments_pkg.';
5 g_errmsg VARCHAR2(1000);
6 g_ledger_id gl_ledgers.ledger_id%TYPE;
7 g_org_id fv_operating_units.org_id%TYPE;
8 g_treasury_conf_id fv_treasury_confirmations.treasury_confirmation_id%TYPE;
9 g_accounting_date fv_treasury_confirmations.treasury_doc_date%TYPE;
10 g_payment_instr_id iby_pay_instructions_all.payment_instruction_id%TYPE;
11 g_checkrun_name ap_checks_all.checkrun_name%TYPE;
12 x_err_code NUMBER;
13 x_err_stage VARCHAR2(2000);
14 --g_dit_flag VARCHAR2(1);
15 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
16 g_do_accounting VARCHAR2(1);
17
18
19 PROCEDURE CREATE_TREASURY_PAYMENT_EVENT
20 (p_calling_sequence IN VARCHAR2
21 ,p_event_type IN VARCHAR2
22 ,p_treasury_conf_id IN NUMBER
23 ,x_status_code OUT NOCOPY VARCHAR2
24 ,x_return_status OUT NOCOPY VARCHAR2);
25 PROCEDURE DO_CONFIRM_PROCESS (x_status_code OUT NOCOPY VARCHAR2
26 ,x_return_status OUT NOCOPY VARCHAR2);
27
28 PROCEDURE DO_BACKOUT_PROCESS(x_status_code OUT NOCOPY VARCHAR2
29 ,x_return_status OUT NOCOPY VARCHAR2);
30
31 /* Bug: 5727409 - Forward declaration of Procedure get_open_period */
32
33 PROCEDURE GET_OPEN_PERIOD(p_accounting_date IN OUT NOCOPY DATE);
34
35 PROCEDURE Main(x_errbuf OUT NOCOPY VARCHAR2
36 ,x_retcode OUT NOCOPY VARCHAR2
37 ,p_treas_conf_id IN VARCHAR2
38 ,p_button_name IN VARCHAR2
39 ,p_do_accounting IN VARCHAR2)
40 IS
41 l_module_name VARCHAR2(200);
42 l_dummy NUMBER;
43 --l_dit_flag fv_operating_units.dit_flag%TYPE;
44 X_status_code VARCHAR2(100);
45 X_return_status VARCHAR2(100);
46 BEGIN
47 l_module_name := g_module_name || 'Main ';
48 SAVEPOINT FV_TREAS;
49 -- Initialize variables
50 x_err_code := 0;
51 g_treasury_conf_id := TO_NUMBER(p_treas_conf_id);
52 g_do_accounting := p_do_accounting;
53
54 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
55 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
56 'Treasury Confirmation Id = '||g_treasury_conf_id);
57 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
58 'p_do_accounting: '||p_do_accounting);
59 END IF;
60
61
62 BEGIN
63 SELECT 1
64 INTO l_dummy
65 FROM gl_je_categories
66 WHERE je_category_name = 'Treasury Confirmation';
67
68 EXCEPTION
69 WHEN NO_DATA_FOUND THEN
70 l_dummy := 0;
71 END;
72
73 IF (l_dummy = 0) THEN
74 IF p_button_name = 'TREASURY_CONFIRMATION.CONFIRM' THEN
75 UPDATE fv_treasury_confirmations
76 SET confirmation_status_flag = 'N'
77 WHERE treasury_confirmation_id = g_treasury_conf_id;
78 ELSIF p_button_name = 'TREASURY_CONFIRMATION.BACK_OUT' THEN
79 UPDATE fv_treasury_confirmations
80 SET confirmation_status_flag = 'Y'
81 WHERE treasury_confirmation_id = g_treasury_conf_id;
82 END IF;
83 commit;
84 x_retcode := 2;
85 x_errbuf := 'The Treasury Confirmation journal category has not been seeded';
86 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
87 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
88 'p_button_name = '||p_button_name);
89 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
90 'Treasury Confirmation Id = '||g_treasury_conf_id);
91 END IF;
92 Return;
93 END IF;
94
95
96 BEGIN
97 SELECT payment_instruction_id
98 ,treasury_doc_date
99 ,set_of_books_id
100 ,org_id
101 ,checkrun_name
102 INTO g_payment_instr_id
103 ,g_accounting_date
104 ,g_ledger_id
105 ,g_org_id
106 ,g_checkrun_name
107 FROM fv_treasury_confirmations
108 WHERE treasury_confirmation_id = g_treasury_conf_id;
109
110 /* Bug: 5727409 - getting open period accounting date */
111 get_open_period(g_accounting_date);
112 --Check whether dit_flag is enabled in fv_operating_units table
113 --g_dit_flag := Null;
114 /*
115 SELECT dit_flag
116 INTO l_dit_flag
117 FROM fv_operating_units
118 where org_id = g_org_id ;
119
120 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
121 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
122 'dit flag = '||l_dit_flag);
123 END IF;
124 g_dit_flag := l_dit_flag;
125
126 IF l_dit_flag <> 'Y' THEN
127 x_retcode := 0;
128 x_errbuf := 'Disbursement in transit checkbox is disabled in Define Federal Options form'||
129 '-no accounting created for Treasury Confirmation ';
130 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
131 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
132 'No Accounting created for Treasury Confirmation -'||
133 'disbursement in transit checkbox is disabled in Define Federal Options form');
134 END IF;
135 END IF;
136 */
137
138 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
139 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
140 'g_payment_instr_id = '||g_payment_instr_id);
141 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
142 'g_accounting_date = '||g_accounting_date);
143 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
144 'g_ledger_id = '||g_ledger_id);
145 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
146 'g_org_id = '||g_org_id);
147 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
148 'g_checkrun_name = '||g_checkrun_name);
149 END IF;
150 EXCEPTION
151 WHEN OTHERS THEN
152 x_retcode := 2;
153 x_errbuf := 'The Treasury Confirmation rows are not available';
154 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
155 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
156 'p_button_name = '||p_button_name);
157 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
158 'Treasury Confirmation Id = '||g_treasury_conf_id);
159 END IF;
160 END;
161
162 IF (x_retcode = 2) THEN
163 IF p_button_name = 'TREASURY_CONFIRMATION.CONFIRM' THEN
164 UPDATE fv_treasury_confirmations
165 SET confirmation_status_flag = 'N'
166 WHERE treasury_confirmation_id = g_treasury_conf_id;
167 ELSIF p_button_name = 'TREASURY_CONFIRMATION.BACK_OUT' THEN
168 UPDATE fv_treasury_confirmations
169 SET confirmation_status_flag = 'Y'
170 WHERE treasury_confirmation_id = g_treasury_conf_id;
171 END IF;
172 commit;
173 RETURN;
174 END IF;
175
176 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
177 'Button='||p_button_name);
178
179 IF p_button_name = 'TREASURY_CONFIRMATION.CONFIRM' THEN
180 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
181 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
182 'Confirmation Process begins');
183 END IF;
184 do_confirm_process(x_status_code,x_return_status);
185 x_retcode := x_status_code;
186
187 IF (x_status_code = 'SUCCESS') THEN
188 UPDATE fv_treasury_confirmations
189 SET confirmation_status_flag = 'Y'
190 WHERE treasury_confirmation_id = g_treasury_conf_id;
191
192 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
193 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
194 'The Confirm Process is Successful');
195 END IF;
196 ELSE
197
198 ROLLBACK TO FV_TREAS;
199 x_retcode:=2;
200
201
202 UPDATE fv_treasury_confirmations
203 SET confirmation_status_flag = 'N'
204 WHERE treasury_confirmation_id = g_treasury_conf_id;
205
206 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
207 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
208 'The Confirm Process has failed.');
209 END IF;
210
211 END IF;
212
213 ELSIF p_button_name = 'TREASURY_CONFIRMATION.BACK_OUT' THEN
214 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
215 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
216 'Backout Process begins');
217 END IF;
218 --if g_dit_flag = 'Y' THEN
219 do_backout_process(x_status_code,x_return_status);
220 x_retcode := x_status_code;
221 --Else
222 -- x_retcode :=0;
223 -- x_status_code := 'SUCCESS';
224
225 --END IF;
226
227 IF (x_status_code = 'SUCCESS') THEN
228 UPDATE fv_treasury_confirmations
229 SET confirmation_status_flag = 'B'
230 WHERE treasury_confirmation_id = g_treasury_conf_id;
231
232 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
233 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
234 'The Backout Process is Successful');
235 END IF;
236 ELSE
237 ROLLBACK TO FV_TREAS;
238 x_retcode:=2;
239
240 UPDATE fv_treasury_confirmations
241 SET confirmation_status_flag = 'Y'
242 WHERE treasury_confirmation_id = g_treasury_conf_id;
243
244 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
245 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
246 'The Backout Process has failed.');
247 END IF;
248
249 END IF;
250 ELSE
251 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
252 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
253 'No Treasury Confirmation process');
254 END IF;
255 RETURN;
256 END IF;
257
258 Commit;
259
260 EXCEPTION
261 WHEN OTHERS THEN
262 g_errmsg := SQLERRM;
263 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_errmsg);
264 RAISE;
265 END Main;
266
267 PROCEDURE DO_CONFIRM_PROCESS (x_status_code OUT NOCOPY VARCHAR2
268 ,x_return_status OUT NOCOPY VARCHAR2)
269 IS
270 l_dummy NUMBER;
271 l_begin_doc fv_treasury_confirmations.begin_doc_num%TYPE;
272 l_end_doc fv_treasury_confirmations.end_doc_num%TYPE;
273 l_diff NUMBER;
274 l_row_num NUMBER;
275 l_module_name VARCHAR2(200);
276 l_void_count NUMBER;
277
278 l_void_status_code VARCHAR2(2000);
279 l_void_return_status VARCHAR2(1);
280
281 --Variables used for 11i Upgrade rows
282 l_pay_fmt_program_name ap_payment_programs.program_name%TYPE;
283 l_checkrun_name fv_treasury_confirmations_all.checkrun_name%TYPE;
284 l_select_str VARCHAR2(1000);
285 TYPE t_refcur IS REF CURSOR;
286 l_upg_check_id_cur t_refcur;
287 l_corr_treas_pay_num fv_tc_offsets.corrected_treasury_pay_number%TYPE;
288 l_offset_check_id fv_tc_offsets.check_id%TYPE;
289
290 -- declare array to store check_ids
291 TYPE l_check_row IS RECORD (CHECK_ID NUMBER(15)) ;
292 TYPE l_check_tbl_type IS TABLE OF l_check_row INDEX BY BINARY_INTEGER;
293 l_check_tbl l_check_tbl_type;
294
295 CURSOR cur_get_checks IS
296 SELECT ac.check_id
297 FROM ap_checks ac
298 ,fv_treasury_confirmations ftc
299 WHERE ftc.treasury_confirmation_id = g_treasury_conf_id
300 AND ftc.payment_instruction_id = ac.payment_instruction_id
301 AND ac.org_id = g_org_id
302 ORDER BY ac.check_id;
303
304 CURSOR cur_corr_treas_pay_num IS
305 SELECT fto.corrected_treasury_pay_number, fto.check_id
306 FROM fv_tc_offsets fto,
307 ap_checks ac,
308 iby_pay_instructions_all ipa
309 WHERE ac.check_id = fto.check_id
310 AND ipa.payment_instruction_id = ac.payment_instruction_id
311 AND ipa.payment_instruction_id = g_payment_instr_id;
312
313 CURSOR c_check_ranges IS
314 SELECT ftcr.range_from, ftcr.range_to
315 FROM fv_treasury_check_ranges ftcr
316 WHERE ftcr.treasury_confirmation_id = g_treasury_conf_id;
317
318 l_calling_sequence VARCHAR2(1000);
319 l_return_status VARCHAR2(100);
320 l_status_code VARCHAR2(100);
321
322 BEGIN
323
324 l_module_name := g_module_name ||'do_confirm_process';
325
326 l_calling_sequence := 'FV_TREASURY_PAYMENTS_PKG.do_confirm_process';
327
328 l_void_count :=0;
329
330 x_status_code := 'SUCCESS';
331
332 -- select statement for 11i upgrade rows
333 BEGIN
334 SELECT checkrun_name
335 INTO l_checkrun_name
336 FROM FV_TREASURY_CONFIRMATIONS_ALL
337 WHERE payment_instruction_id = g_payment_instr_id
338 AND org_id = g_org_id;
339
340 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
341 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
342 'l_checkrun_name = '||l_checkrun_name);
343 END IF;
344 EXCEPTION
345 WHEN OTHERS THEN
346 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
347 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
348 'No Upgrade entries');
349 END IF;
350 l_checkrun_name := NULL;
351 END;
352
353 IF l_checkrun_name IS NOT NULL THEN
354
355
356
357 SELECT appp.program_name
358 INTO l_pay_fmt_program_name
359 FROM ap_inv_selection_criteria_all apisc ,
360 ap_payment_programs appp
361 WHERE apisc.checkrun_name = g_checkrun_name
362 AND apisc.org_id = g_org_id
363 AND appp.program_id = apisc.program_id ;
364
365
366 -- setting the predefined order of the check_ids based on Payment Format
367
368 IF l_pay_fmt_program_name IN ('FVBLCCDP' , 'FVBLPPDP','FVTPCCD','FVTIACHP',
369 'FVTPPPD','FVTPPPDP','FVSPCCD','FVSPCCDP',
370 'FVSPPPDP','FVSPPPD' ) THEN
371 l_select_str := 'SELECT check_id FROM fv_tc_check_v WHERE' ||
372 ' checkrun_name = g_checkrun_name ORDER BY '||
373 ' routing_transit_num , num_1099, check_number' ;
374 ELSIF l_pay_fmt_program_name IN ('FVBLNCR','FVBLSLTR','FVTIACHB','FVSPNCR')
375 THEN
376 l_select_str := 'SELECT check_id FROM fv_tc_check_v WHERE' ||
377 ' checkrun_name = g_checkrun_name ORDER BY '||
378 ' num_1099, check_number' ;
379 ELSE
380 l_select_str := 'SELECT check_id FROM fv_tc_check_v WHERE' ||
381 ' checkrun_name = g_checkrun_name' ||
382 ' ORDER BY check_number';
383 END IF;
384 -- Get all the 11i upgrade rows check_id values
385 l_row_num := 1;
386 OPEN l_upg_check_id_cur FOR l_select_str;
387 LOOP
388 FETCH l_upg_check_id_cur INTO l_check_tbl(l_row_num).check_id;
389 l_row_num := l_row_num + 1;
390 EXIT WHEN l_upg_check_id_cur %NOTFOUND;
391 END LOOP;
392 ELSE
393 --Get all the R12 checks related to this treasury confirmation id
394 l_row_num := 1;
395 OPEN cur_get_checks;
396 LOOP
397 FETCH cur_get_checks INTO l_check_tbl(l_row_num).check_id;
398 l_row_num := l_row_num + 1;
399 EXIT WHEN cur_get_checks %NOTFOUND;
400 END LOOP;
401
402 END IF;
403
404 l_row_num := 1;
405
406 -- Assigning the treasury Pay number to the respective checks
407 FOR c_check_range_rec IN c_check_ranges
408 LOOP
409 l_begin_doc := c_check_range_rec.range_from;
410 l_end_doc := c_check_range_rec.range_to;
411
412 IF (l_begin_doc IS NULL) OR (l_end_doc IS NULL) OR
413 (g_payment_instr_id IS NULL) OR ( g_accounting_date IS NULL) THEN
414 x_err_code := 20;
415 x_err_stage := 'Data in treasury confirmation table is missing';
416 RETURN;
417 END IF;
418
419 l_diff := l_end_doc - l_begin_doc + 1;
420
421 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
422 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
423 'l_diff is ' || l_diff);
424 END IF;
425
426 FOR i IN 1.. l_diff
427 LOOP
428 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
429 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
430 'l_row_num:'||l_row_num);
431 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
432 'l_check_tbl(l_row_num).check_id:'||
433 l_check_tbl(l_row_num).check_id);
434 END IF;
435
436 -- Update ap_checks if a corrected treasury pay number
437 -- for a payment within the batch being processed has been entered
438 UPDATE ap_checks c
439 SET treasury_pay_number = l_begin_doc,
440 treasury_pay_date = g_accounting_date,
441 last_update_date = SYSDATE,
442 last_updated_by = fnd_global.user_id,
443 last_update_login = fnd_global.login_id
444 WHERE c.check_id = l_check_tbl(l_row_num).check_id;
445
446
447 INSERT INTO fv_voided_checks
448 (
449 void_id,
450 checkrun_name,
451 check_id,
452 processed_flag,
453 creation_date,
454 created_by,
455 last_update_date,
456 last_updated_by,
457 last_update_login,
458 org_id
459 )
460 SELECT fv_voided_checks_s.nextval,
461 ac.checkrun_name,
462 ac.check_id,
463 'N',
464 SYSDATE,
465 fnd_global.user_id,
466 SYSDATE,
467 fnd_global.user_id,
468 fnd_global.login_id,
469 ac.org_id
470 FROM ap_checks ac,
471 ap_payment_history aph
472 WHERE ac.check_id = l_check_tbl(l_row_num).check_id
473 AND ac.void_date IS NOT NULL
474 AND (ac.checkrun_name IS NOT NULL OR ac.payment_id IS NOT NULL)
475 AND aph.check_id = ac.check_id
476 AND aph.transaction_type = 'PAYMENT CANCELLED'
477 AND aph.posted_flag = 'Y'
478 AND NOT EXISTS (SELECT 1
479 FROM fv_voided_checks fvc
480 WHERE fvc.check_id = ac.check_id
481 AND fvc.org_id = ac.org_id);
482
483 l_row_num := l_row_num+1;
484 l_begin_doc := l_begin_doc +1;
485
486 END LOOP;
487 END LOOP;
488
489
490 OPEN cur_corr_treas_pay_num;
491 LOOP
492 FETCH cur_corr_treas_pay_num INTO l_corr_treas_pay_num, l_offset_check_id;
493 EXIT WHEN cur_corr_treas_pay_num%NOTFOUND;
494
495 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
496 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
497 'In corrected treasury pay number loop');
498 END IF;
499
500 IF l_corr_treas_pay_num IS NOT NULL THEN
501 UPDATE ap_checks
502 SET treasury_pay_number = l_corr_treas_pay_num
503 WHERE check_id = l_offset_check_id;
504 END IF;
505 END LOOP;
506 CLOSE cur_corr_treas_pay_num;
507 --Modified for ER: 11841305
508 --If g_do_accounting is N then it means that this
509 --is a cash payment and no budgetary accounting is to be done
510 --since cash payments budgetary accounting has already been done
511 --in AP when cash payment is made.
512 --IF g_dit_flag = 'Y' THEN
513 IF g_do_accounting = 'Y' THEN
514 create_treasury_payment_event(l_calling_sequence,
515 'TREASURY_CONFIRM',
516 g_treasury_conf_id ,
517 x_status_code,
518 x_return_status);
519
520 IF x_status_code= 'SUCCESS' THEN
521 UPDATE fv_treasury_confirmations
522 SET confirmation_status_flag = 'Y'
523 WHERE treasury_confirmation_id = g_treasury_conf_id;
524 commit;
525 END IF;
526 --update dit_cash_tran_code to cash_pay which denotes that
527 --no budgetary accounting is done for this cash payment since it has
528 --already been done in AP
529 ELSIF g_do_accounting = 'N' THEN
530
531 UPDATE fv_treasury_confirmations
532 SET confirmation_status_flag = 'Y',
533 dit_cash_tran_code = 'CASH_PAY'
534 WHERE treasury_confirmation_id = g_treasury_conf_id;
535 commit;
536
537 END IF;
538
539 EXCEPTION
540 WHEN OTHERS THEN
541 x_status_code := 'FAILURE';
542
543 END do_confirm_process;
544
545 PROCEDURE DO_BACKOUT_PROCESS (x_status_code OUT NOCOPY VARCHAR2,
546 x_return_status OUT NOCOPY VARCHAR2)
547 IS
548 l_module_name VARCHAR2(100);
549 l_calling_sequence VARCHAR2(1000);
550 BEGIN
551
552 l_module_name := g_module_name ||'do_backout_process';
553
554 l_calling_sequence := 'FV_TREASURY_PAYMENTS_PKG.do_backout_process';
555 create_treasury_payment_event(l_calling_sequence,
556 'TREASURY_BACKOUT',
557 g_treasury_conf_id ,
558 x_status_code,
559 x_return_status);
560
561 END do_backout_process;
562
563 PROCEDURE GET_OPEN_PERIOD(p_accounting_date IN OUT NOCOPY DATE)
564 IS
565 v_status gl_period_statuses.closing_status%type;
566 v_pyear gl_period_statuses.period_year%type;
567 l_module_name VARCHAR2(200);
568 v_pnum gl_period_statuses.effective_period_num%type;
569 BEGIN
570
571 /* To find out whether period is open for particular gl_accounting_date */
572 l_module_name := g_module_name ||' get_open_period';
573
574 SELECT closing_status,period_year,effective_period_num
575 INTO v_status,v_pyear,v_pnum
576 FROM gl_period_statuses gps
577 WHERE gps.ledger_id = g_ledger_id
578 AND gps.application_id = 101
579 AND p_accounting_date BETWEEN gps.start_date AND gps.end_date
580 AND gps.adjustment_period_flag = 'N';
581
582 IF v_status = 'C' THEN /* If Period is closed then get starting
583 accounting date of next open period */
584
585 BEGIN
586
587 SELECT start_date
588 INTO p_accounting_date
589 FROM gl_period_statuses gps
590 WHERE gps.ledger_id = g_ledger_id
591 AND gps.application_id = 101
592 AND gps.period_year >= v_pyear
593 AND effective_period_num > v_pnum
594 AND gps.closing_status = 'O'
595 AND gps.adjustment_period_flag = 'N'
596 AND ROWNUM < 2
597 ORDER BY period_year,period_num ASC ;
598
599 EXCEPTION
600 WHEN others THEN
601 fv_utility.log_mesg(fnd_log.level_exception,l_module_name||
602 'Error in getting next Open Period',SQLERRM);
603 RETURN;
604
605 END;
606 ELSE
607 p_accounting_date := p_accounting_date;
608
609 END IF; --- end of Closed Period
610
611 EXCEPTION
612 WHEN OTHERS THEN
613 fv_utility.log_mesg(fnd_log.level_exception,l_module_name,SQLERRM);
614
615 END GET_OPEN_PERIOD;
616
617 PROCEDURE CREATE_TREASURY_PAYMENT_EVENT
618 (p_calling_sequence IN VARCHAR2
619 ,p_event_type IN VARCHAR2
620 ,p_treasury_conf_id IN NUMBER
621 ,x_status_code OUT NOCOPY VARCHAR2
622 ,x_return_status OUT NOCOPY VARCHAR2)
623 IS
624 l_calling_sequence VARCHAR2(1000);
625 l_module_name VARCHAR2(200);
626
627 l_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
628 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
629 l_reference_info XLA_EVENTS_PUB_PKG.T_EVENT_REFERENCE_INFO;
630
631 CURSOR cur_get_payment_info(p_treasury_conf_id NUMBER) IS
632 SELECT distinct ac.legal_entity_id, ftc.event_id
633 FROM ap_checks ac
634 ,fv_treasury_confirmations ftc
635 WHERE ftc.treasury_confirmation_id = p_treasury_conf_id
636 AND ftc.payment_instruction_id = ac.payment_instruction_id
637 AND ac.org_id = g_org_id;
638
639 CURSOR cur_get_void_info IS
640 SELECT
641 FVC.event_id,
642 FTC.payment_instruction_id,
643 FVC.check_id,
644 FTC.treasury_confirmation_id
645 FROM fv_voided_checks FVC,
646 fv_treasury_confirmations_all FTC,
647 ap_checks_all ac
648 WHERE
649 ftc.org_id = g_org_id
650 AND FVC.org_id = ftc.org_id
651 AND ac.org_id = FVC.org_id
652 AND FTC.treasury_confirmation_id = p_treasury_conf_id
653 AND FTC.payment_instruction_id = ac.payment_instruction_id
654 AND ac.check_id = fvc.check_id
655 AND fvc.processed_flag = 'U'
656 AND FTC.confirmation_status_flag = 'Y';
657
658
659 CURSOR cur_void_acctg_date(l_check_id NUMBER) IS
660 SELECT accounting_date
661 FROM ap_invoice_payments_all
662 WHERE check_id = l_check_id
663 AND amount < 0
664 GROUP BY check_id, accounting_date;
665
666 l_event_status_code VARCHAR2(1);
667 l_pay_hist_id AP_PAYMENT_HISTORY_ALL.payment_history_id%TYPE;
668 l_check_id NUMBER(15);
669 l_check_number NUMBER(15);
670 l_legal_entity_id NUMBER(15);
671 l_batch NUMBER;
672 l_errbuf VARCHAR2(1000);
673 l_retcode NUMBER;
674 l_api_message VARCHAR2(1000);
675 l_payment_instr_id NUMBER(15);
676 l_treas_conf_id NUMBER(15);
677 l_void_acctg_date DATE;
678 l_tc_event_id NUMBER(15);
679 l_void_event_id NUMBER(15);
680 l_pmt_id fv_treasury_confirmations_all.payment_instruction_id%TYPE;
681
682 BEGIN
683 l_calling_sequence := p_calling_sequence || ' -> FV_TREASURY_PAYMENTS_PKG.CREATE_TREASURY_PAYMENT_EVENT';
684 l_module_name := g_module_name||'Create_Treasury_Payment_Event';
685
686 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
687 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
688 'Calling Sequence: '||p_calling_sequence);
689 END IF;
690
691 --Set the reference info value based on event type
692 IF p_event_type = 'TREASURY_CONFIRM' THEN
693 l_reference_info.reference_char_1 := 'CONFIRM';
694 ELSIF p_event_type = 'TREASURY_BACKOUT' THEN
695 l_reference_info.reference_char_1 := 'BACKOUT';
696 ELSIF p_event_type = 'TREASURY_VOID' THEN
697 l_reference_info.reference_char_1 := 'VOID';
698 ELSE
699 l_reference_info.reference_char_1 := NULL;
700 END IF;
701 l_event_status_code := XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED;
702
703
704 SELECT payment_instruction_id
705 INTO
706 l_pmt_id
707 FROM fv_treasury_confirmations ftc
708 WHERE
709 ftc.treasury_confirmation_id = p_treasury_conf_id;
710
711 l_event_source_info.application_id := 8901;
712 l_event_source_info.ledger_id := g_ledger_id;
713 l_event_source_info.entity_type_code := 'TREASURY_CONFIRMATION';
714 l_event_source_info.transaction_number := l_pmt_id; --p_treasury_conf_id;
715 l_event_source_info.source_id_int_1 := p_treasury_conf_id;
716 l_security_context.security_id_int_1 := g_org_id;
717
718 --Bug9829499
719 --Call fv_cleanup_xla_gt to clean up the xla gt tables
720 fv_utility.fv_cleanup_xla_gt;
721
722 IF p_event_type = 'TREASURY_CONFIRM' OR p_event_type = 'TREASURY_BACKOUT' THEN
723
724 OPEN cur_get_payment_info(p_treasury_conf_id);
725 FETCH cur_get_payment_info INTO l_legal_entity_id,l_tc_event_id;
726 l_event_source_info.legal_entity_id := l_legal_entity_id;
727
728 IF XLA_EVENTS_PUB_PKG.event_exists
729 (p_event_source_info => l_event_source_info
730 ,p_event_type_code => p_event_type
731 ,p_event_date => g_accounting_date
732 ,p_event_status_code => l_event_status_code
733 ,p_event_number => NULL
734 ,p_valuation_method => NULL
735 ,p_security_context => l_security_context) THEN
736
737 IF (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL ) THEN
738 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,'Event exists! event_id =' || l_tc_event_id);
739 END IF;
740
741 --- call the xla_events_pub_pkg.get_array_event_info
742 /*XLA_EVENTS_PUB_PKG.DELETE_EVENT(
743 p_event_source_info => l_event_source_info,
744 p_event_id => l_tc_event_id,
745 p_valuation_method => NULL,
746 p_security_context => l_security_context);
747
748 IF (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL ) THEN
749 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name, 'After delete '||l_tc_event_id);
750 END IF;*/
751
752 END IF;
753
754 l_tc_event_id := NULL;
755
756 l_tc_event_id := Xla_Events_Pub_Pkg.Create_Event
757 (
758 p_event_source_info => l_event_source_info,
759 p_event_type_code => p_event_type,
760 p_event_date => g_accounting_date,
761 p_event_status_code => l_event_status_code,
762 p_event_number => NULL,
763 p_reference_info => l_reference_info,
764 p_valuation_method => NULL,
765 p_security_context => l_security_context
766 -- p_budgetary_control_flag => 'Y'
767 );
768
769 IF FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL THEN
770 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Event ID: '||l_tc_event_id );
771 END IF;
772
773 IF l_tc_event_id is NULL THEN
774 IF FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL THEN
775 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'Event creation failed for: Treasury confirmation ID: '
776 || to_char(l_event_source_info.transaction_number));
777 END IF;
778 RAISE FND_API.g_exc_error;
779 END IF;
780
781 Insert into XLA_ACCT_PROG_EVENTS_GT (Event_Id)
782 values (l_tc_Event_id);
783
784 Update fv_treasury_confirmations_all
785 Set event_id = l_tc_event_id
786 Where treasury_confirmation_id = p_treasury_conf_id;
787
788 ELSIF p_event_type = 'TREASURY_VOID' THEN
789 OPEN cur_get_void_info;
790 LOOP
791 FETCH cur_get_void_info INTO l_void_event_id,
792 l_payment_instr_id,
793 l_check_id
794 ,l_treas_conf_id;
795
796 EXIT WHEN cur_get_void_info%NOTFOUND;
797
798 /*
799 OPEN cur_get_payment_info(l_treas_conf_id);
800 FETCH cur_get_payment_info INTO l_tc_event_id, l_legal_entity_id;
801 CLOSE cur_get_payment_info;
802 */
803 l_event_source_info.legal_entity_id := l_legal_entity_id;
804
805 OPEN cur_void_acctg_date(l_check_id);
806 FETCH cur_void_acctg_date INTO l_void_acctg_date;
807 CLOSE cur_void_acctg_date;
808 /* Bug: 5727409 */
809
810 get_open_period(l_void_acctg_date);
811
812 /*
813 IF XLA_EVENTS_PUB_PKG.event_exists
814 (p_event_source_info => l_event_source_info
815 ,p_event_type_code => p_event_type
816 ,p_event_date => l_void_acctg_date
817 ,p_event_status_code => l_event_status_code
818 ,p_event_number => NULL
819 ,p_valuation_method => NULL
820 ,p_security_context => l_security_context) THEN
821
822 IF (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL ) THEN
823 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name,'Event exists! event_id =' || l_void_event_id);
824 END IF;
825
826 XLA_EVENTS_PUB_PKG.DELETE_EVENT(
827 p_event_source_info => l_event_source_info,
828 p_event_id => l_void_event_id,
829 p_valuation_method => NULL,
830 p_security_context => l_security_context);
831
832 IF (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL ) THEN
833 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module_name, 'After delete '||l_void_event_id);
834 END IF;
835 END IF;
836 */
837 --If the event does not exist, create a new event,
838 --else use the same event
839 IF l_void_event_id IS NULL THEN
840
841 --l_void_event_id := NULL;
842
843 l_void_event_id := Xla_Events_Pub_Pkg.Create_Event
844 (
845 p_event_source_info => l_event_source_info,
846 p_event_type_code => p_event_type,
847 p_event_date => l_void_acctg_date,
848 p_event_status_code => l_event_status_code,
849 p_event_number => NULL,
850 p_reference_info => l_reference_info,
851 p_valuation_method => NULL,
852 p_security_context => l_security_context
853 -- p_budgetary_control_flag => 'Y'
854 );
855
856 IF FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL THEN
857 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Event ID: '||l_void_event_id );
858 END IF;
859 END IF;
860
861 IF l_void_event_id is NULL THEN
862 IF FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL THEN
863 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'Event creation failed for: Treasury confirmation ID: '
864 || to_char(l_event_source_info.transaction_number)||'Check ID: '||l_check_id);
865 END IF;
866 RAISE FND_API.g_exc_error;
867 END IF;
868
869 INSERT INTO XLA_ACCT_PROG_EVENTS_GT (Event_Id)
870 VALUES (l_void_Event_id);
871
872 UPDATE fv_voided_checks
873 SET event_id = l_void_event_id,
874 payment_instruction_id = l_payment_instr_id
875 WHERE check_id = l_check_id
876 AND org_id = g_org_id;
877
878 END LOOP;
879 CLOSE cur_get_void_info;
880
881 END IF;
882 x_return_status := FND_API.G_RET_STS_SUCCESS;
883 l_batch := NULL;
884 l_errbuf:= NULL;
885 l_retcode:= NULL;
886
887 xla_accounting_pub_pkg.accounting_program_events
888 (p_application_id => 8901
889 ,p_accounting_mode => 'FINAL'
890 ,p_gl_posting_flag => 'N'
891 ,p_accounting_batch_id => l_batch
892 ,p_errbuf => l_errbuf
893 ,p_retcode => l_retcode
894 );
895
896 IF l_retcode <> 0 THEN
897 l_api_message := 'Error Accounting for Events in SLA:'||l_errbuf;
898 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
899 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name , l_api_message);
900 END IF;
901 x_status_code := 'FAILURE';
902 RAISE FND_API.g_exc_error;
903 END IF;
904
905 x_status_code := 'SUCCESS';
906
907 EXCEPTION
908 WHEN FND_API.g_exc_error THEN
909 x_return_status := FND_API.g_ret_sts_error;
910 END create_treasury_payment_event;
911
912 PROCEDURE Void
913 (X_errbuf OUT NOCOPY VARCHAR2
914 ,X_retcode OUT NOCOPY VARCHAR2 )
915
916 IS
917 l_module_name VARCHAR2(200);
918 -- l_group_id NUMBER;
919 l_err_code NUMBER;
920 l_err_stage VARCHAR2(2000);
921 l_reference1 gl_interface.reference1%TYPE;
922 l_calling_sequence VARCHAR2(2000);
923 l_return_status VARCHAR2(30);
924 l_status_code VARCHAR2(30);
925 l_ledger_name VARCHAR2(100);
926 l_void_count NUMBER;
927 l_event_status xla_events.event_status_code%TYPE;
928 l_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
929 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
930
931 CURSOR cur_treas_conf
932 IS
933 SELECT max(fvtreas.TREASURY_CONFIRMATION_ID) TREASURY_CONFIRMATION_ID
934 FROM fv_voided_checks fvc , ap_checks_all apchk,fv_treasury_confirmations_all fvtreas
935 WHERE
936 apchk.org_id = g_org_id
937 AND apchk.org_id = fvtreas.org_id
938 AND apchk.check_id = fvc.check_id
939 AND apchk.payment_instruction_id = fvtreas.payment_instruction_id
940 AND fvc.processed_flag = 'U'
941 GROUP BY fvc.check_id;
942
943 CURSOR event_id_cur(p_treas_conf_id IN NUMBER)
944 IS
945 SELECT fc.event_id
946 FROM fv_voided_checks fc
947 WHERE fc.processed_flag = 'U'
948 AND fc.org_id = g_org_id
949 and fc.check_id in ( select check_id
950 from fv_treasury_confirmations_all fvtreas ,
951 ap_checks_all ac
952 where fvtreas.org_id = g_org_id
953 and ac.org_id = fvtreas.org_id
954 and fvtreas.treasury_confirmation_id = p_treas_conf_id
955 and fvtreas.payment_instruction_id = ac.payment_instruction_id
956 and ac.void_date is not null
957 );
958
959 CURSOR unprocessed_event_cur
960 IS
961 SELECT fv.event_id
962 FROM fv_voided_checks fv
963 WHERE processed_flag = 'P'
964 AND org_id = g_org_id
965 AND EXISTS (SELECT event_id FROM xla_events xe
966 WHERE xe.application_id = 8901
967 AND xe.event_type_code = 'TREASURY_VOID'
968 AND xe.event_id = fv.event_id
969 AND xe.event_status_code = 'U'
970 )
971 AND NOT EXISTS
972 (SELECT event_id FROM xla_ae_headers xh
973 WHERE xh.application_id = 8901
974 AND xh.event_type_code = 'TREASURY_VOID'
975 AND xh.event_id = fv.event_id);
976
977 treas_conf_rec cur_treas_conf%ROWTYPE;
978 cur_event_id fv_voided_checks.event_id%TYPE;
979 p_treasury_conf_id NUMBER;
980 l_pmt_id fv_treasury_confirmations_all.payment_instruction_id%TYPE;
981
982 BEGIN
983 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'Start Of Void');
984
985 l_module_name := g_module_name || 'Void';
986 l_calling_sequence := 'FV_TREASURY_PAYMENTS_PKG.Void_Payments';
987 l_reference1 := 'Void';
988
989 IF g_org_id IS NULL THEN
990 g_org_id := MO_GLOBAL.get_current_org_id;
991 MO_UTILS.get_ledger_info(g_org_id, g_ledger_id, l_ledger_name);
992 END IF;
993
994 --Update the processed flag to U, of the void rows which
995 --were erroneously set to P in the earlier version of the code
996 --The events of these rows will be unprocessed in xla_events
997 --and such events will not exist in fv_xla_ae_headers.
998 BEGIN
999 OPEN unprocessed_event_cur ;
1000 LOOP
1001 FETCH unprocessed_event_cur into cur_event_id;
1002 EXIT WHEN unprocessed_event_cur%NOTFOUND;
1003
1004 SELECT max(fvtreas.TREASURY_CONFIRMATION_ID) TREASURY_CONFIRMATION_ID into p_treasury_conf_id
1005 FROM fv_voided_checks fvc , ap_checks_all apchk,
1006 fv_treasury_confirmations_all fvtreas
1007 WHERE apchk.org_id = g_org_id
1008 AND apchk.org_id = fvtreas.org_id
1009 AND apchk.check_id = fvc.check_id
1010 AND apchk.payment_instruction_id = fvtreas.payment_instruction_id
1011 AND fvc.event_id = cur_event_id;
1012
1013 SELECT payment_instruction_id
1014 INTO l_pmt_id
1015 FROM fv_treasury_confirmations ftc
1016 WHERE ftc.treasury_confirmation_id = p_treasury_conf_id;
1017
1018 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1019 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module_name,
1020 'p_treasury_conf_id :'||p_treasury_conf_id || 'l_pmt_id :'||l_pmt_id || 'event_id:'||cur_event_id);
1021 END IF;
1022
1023 UPDATE fv_voided_checks
1024 SET processed_flag = 'U', event_id = NULL
1025 WHERE processed_flag = 'P'
1026 AND event_id = cur_event_id
1027 AND org_id = g_org_id;
1028
1029 l_security_context.security_id_int_1 := g_org_id;
1030 l_event_source_info.application_id := 8901;
1031 l_event_source_info.ledger_id := g_ledger_id;
1032 l_event_source_info.entity_type_code := 'TREASURY_CONFIRMATION';
1033 l_event_source_info.transaction_number := l_pmt_id; --p_treasury_conf_id;
1034 l_event_source_info.source_id_int_1 := p_treasury_conf_id;
1035 l_security_context.security_id_int_1 := g_org_id;
1036
1037 XLA_EVENTS_PUB_PKG.DELETE_EVENT(
1038 p_event_source_info => l_event_source_info,
1039 p_event_id => cur_event_id,
1040 p_valuation_method => NULL,
1041 p_security_context => l_security_context);
1042 END LOOP;
1043 CLOSE unprocessed_event_cur ;
1044
1045 EXCEPTION
1046 WHEN NO_DATA_FOUND THEN
1047 fv_utility.log_mesg('No rows found for updating status to U.');
1048 WHEN OTHERS THEN
1049 l_err_code := SQLCODE;
1050 l_err_stage := SQLERRM;
1051 X_retcode:=2;
1052 fv_utility.log_mesg(fnd_log.level_exception,l_module_name||' insert fv_voided_checks2',l_err_stage);
1053 END;
1054
1055 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1056 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'INSERT INTO fv_voided_checks');
1057 END IF;
1058
1059 BEGIN
1060 INSERT INTO fv_voided_checks
1061 (
1062 void_id,
1063 checkrun_name,
1064 check_id,
1065 processed_flag,
1066 creation_date,
1067 created_by,
1068 last_update_date,
1069 last_updated_by,
1070 last_update_login,
1071 org_id
1072 )
1073 SELECT fv_voided_checks_s.nextval,
1074 ac.checkrun_name,
1075 ac.check_id,
1076 'U',
1077 SYSDATE,
1078 fnd_global.user_id,
1079 SYSDATE,
1080 fnd_global.user_id,
1081 fnd_global.login_id,
1082 ac.org_id
1083 FROM ap_checks_all ac,
1084 fv_treasury_confirmations_all fvtc
1085 , ap_payment_history_all aph
1086 WHERE ac.org_id = g_org_id
1087 AND fvtc.org_id = ac.org_id
1088 AND fvtc.payment_instruction_id = ac.payment_instruction_id
1089 AND ac.void_date IS NOT NULL
1090 AND (ac.checkrun_name IS NOT NULL OR ac.payment_id IS NOT NULL)
1091 AND aph.check_id = ac.check_id
1092 AND aph.transaction_type = 'PAYMENT CANCELLED'
1093 AND aph.posted_flag = 'Y'
1094 --Added for ER: 11841305
1095 and NVL(fvtc.dit_cash_tran_code, 'ZX') <> 'CASH_PAY'
1096 AND NOT EXISTS (SELECT 1
1097 FROM fv_voided_checks fvc
1098 WHERE fvc.check_id = ac.check_id
1099 AND fvc.org_id = ac.org_id);
1100
1101 fv_utility.log_mesg('Inserted: '||sql%rowcount||' row(s).');
1102
1103 EXCEPTION
1104 WHEN NO_DATA_FOUND THEN
1105 fv_utility.log_mesg('No new accounted void payments found.');
1106 WHEN OTHERS THEN
1107 l_err_code := SQLCODE;
1108 l_err_stage := SQLERRM;
1109 X_retcode:=2;
1110 fv_utility.log_mesg(fnd_log.level_exception,l_module_name||' insert fv_voided_checks1',l_err_stage);
1111 END;
1112
1113 l_status_code:='SUCCESS';
1114
1115 OPEN cur_treas_conf ;
1116 LOOP
1117 FETCH cur_treas_conf INTO treas_conf_rec;
1118 EXIT WHEN (l_status_code <> 'SUCCESS' OR cur_treas_conf%NOTFOUND );
1119
1120 l_status_code:='';
1121
1122 l_void_count:=0;
1123
1124 SELECT COUNT(ac.check_id) INTO l_void_count
1125 FROM ap_checks_all ac
1126 ,fv_treasury_confirmations_all ftc
1127 , fv_voided_checks fvc
1128 WHERE ftc.treasury_confirmation_id = treas_conf_rec.TREASURY_CONFIRMATION_ID
1129 AND ftc.payment_instruction_id = ac.payment_instruction_id
1130 AND ac.org_id = g_org_id
1131 AND ac.org_id = ftc.org_id
1132 AND ac.void_date IS NOT NULL
1133 AND fvc.check_id = ac.check_id
1134 AND fvc.processed_flag = 'U';
1135
1136 IF l_void_count <> 0 THEN
1137 create_treasury_payment_event(l_calling_sequence,
1138 'TREASURY_VOID',
1139 treas_conf_rec.TREASURY_CONFIRMATION_ID,
1140 l_status_code,
1141 l_return_status);
1142
1143 BEGIN
1144
1145 IF (l_status_code = 'SUCCESS') THEN
1146 --IF the accounting program returns success, check whether
1147 --the created event status is processed. If it is processed,
1148 --then update the voided check row as process, else no.
1149 FOR event_id_rec IN event_id_cur(treas_conf_rec.TREASURY_CONFIRMATION_ID) LOOP
1150 SELECT event_status_code
1151 INTO l_event_status
1152 FROM xla_events
1153 WHERE event_id = event_id_rec.event_id
1154 AND application_id = 8901;
1155
1156 IF l_event_status = 'P' THEN
1157 UPDATE fv_voided_checks
1158 SET processed_flag = l_event_status
1159 WHERE org_id = g_org_id
1160 AND event_id = event_id_rec.event_id;
1161 END IF;
1162
1163 END LOOP;
1164 /*
1165 UPDATE fv_voided_checks
1166 SET processed_flag = 'P'
1167 WHERE processed_flag = 'U'
1168 AND org_id = g_org_id
1169 and check_id in ( select check_id
1170 from fv_treasury_confirmations_all fvtreas ,
1171 ap_checks_all ac
1172 where
1173 fvtreas.org_id = g_org_id
1174 and ac.org_id = fvtreas.org_id
1175 and fvtreas.treasury_confirmation_id = treas_conf_rec.TREASURY_CONFIRMATION_ID
1176 and fvtreas.payment_instruction_id = ac.payment_instruction_id
1177 and ac.void_date is not null
1178 );
1179 */
1180 ELSE
1181 X_errbuf := 'Create Accounting has failed. Please check the logs and resubmit the process.';
1182 fv_utility.log_mesg(fnd_log.level_exception,l_module_name ,X_errbuf);
1183 X_retcode := 2;
1184 RETURN;
1185 END IF;
1186
1187 EXCEPTION
1188 WHEN OTHERS THEN
1189 l_err_code := SQLCODE;
1190 l_err_stage := SQLERRM;
1191 fv_utility.log_mesg(fnd_log.level_exception,l_module_name||
1192 'update fv_voided_checks1',l_err_stage);
1193 END;
1194 ELSE
1195 l_status_code:='SUCCESS';
1196 END IF ;
1197
1198 END LOOP;
1199 CLOSE cur_treas_conf ;
1200
1201 IF nvl(l_void_count, 0) = 0 THEN
1202 fv_utility.log_mesg('No accounted void payments found for processing!');
1203 fv_utility.log_mesg('Please make sure that voided payments have been accounted in payables');
1204 fv_utility.log_mesg('and then submit the DIT void process.');
1205 END IF;
1206 /*
1207 BEGIN
1208 IF (l_status_code = 'SUCCESS') THEN
1209 UPDATE fv_voided_checks
1210 SET processed_flag = 'P'
1211 WHERE processed_flag = 'U'
1212 AND org_id = g_org_id;
1213 ELSE
1214 UPDATE fv_voided_checks
1215 SET processed_flag = 'X'
1216 WHERE processed_flag = 'U'
1217 AND org_id = g_org_id;
1218
1219 END IF;
1220 EXCEPTION
1221 WHEN OTHERS THEN
1222 l_err_code := SQLCODE;
1223 l_err_stage := SQLERRM;
1224 fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'update fv_voided_checks1',l_err_stage);
1225 END;
1226 */
1227
1228 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'End Of Void');
1229 X_retcode:=0;
1230 END Void;
1231
1232
1233 END FV_TREASURY_PAYMENTS_PKG;