[Home] [Help]
PACKAGE BODY: APPS.FV_DISB_IN_TRANSIT
Source
1 PACKAGE BODY fv_disb_in_transit AS
2 --$Header: FVAPDITB.pls 120.30.12000000.2 2007/10/03 10:37:01 nisgupta ship $
3 g_module_name VARCHAR2(100);
4 g_errmsg VARCHAR2(200);
5
6 -- v_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE;
7
8 v_set_of_books_id gl_ledgers_public_v.ledger_id%TYPE;
9 v_org_id fv_operating_units.org_id%TYPE;
10 c_gl_appl_id gl_period_statuses.application_id%TYPE;
11 g_treasury_confirmation_id NUMBER;
12 v_treasury_doc_date fv_treasury_confirmations.treasury_doc_date%TYPE;
13 c_application_short_name VARCHAR2(10);
14 c_key_flex_code VARCHAR2(4);
15 -- v_flex_num gl_sets_of_books.chart_of_accounts_id%TYPE;
16 v_flux_num gl_ledgers_public_v.chart_of_accounts_id%TYPE;
17 v_segment_value VARCHAR2(25);
18 a_segments fnd_flex_ext.SegmentArray;
19 gl_seg_name VARCHAR2(30);
20
21 -- AKA, 8/10/99, moved declaration globally
22 v_treasury_confirmation_id NUMBER;
23 v_rowid VARCHAR2(25);
24
25 -- select all lines for the checks for the treasury confirmation that are not voided
26 -- AKA, 8/10/99, is cursor for backout, moved globally to be used for main and backout,
27 -- also more restrictive, also selecting rowid
28 CURSOR c_je_lines IS
29 SELECT ac.check_id,gjh.currency_code,gjh.currency_conversion_type,
30 gjh.currency_conversion_rate,ac.exchange_date,
31 gjl.accounted_dr,gjl.accounted_cr,
32 gjl.entered_dr, gjl.entered_cr, gjl.code_combination_id,
33 gjl.ROWID,
34 gjl.reference_4
35 FROM ap_checks ac, gl_je_lines gjl, gl_je_headers gjh
36 WHERE ac.void_date IS NULL
37 AND gjl.reference_1 = TO_CHAR(v_treasury_confirmation_id)
38 AND ac.check_id = gjl.reference_3
39 AND gjh.je_header_id = gjl.je_header_id
40 AND gjh.je_category = 'Treasury Confirmation'
41 AND gjh.je_source = 'Payables';
42 ----------------------------------------------------------------------------------------------------------------------------
43
44 PROCEDURE process_clean_up IS
45
46 BEGIN
47
48 ROLLBACK;
49
50 UPDATE fv_treasury_confirmations
51 SET confirmation_status_flag = 'N'
52 WHERE treasury_confirmation_id = g_treasury_confirmation_id;
53
54 COMMIT;
55
56 RETURN;
57
58
59 EXCEPTION
60 WHEN OTHERS THEN
61 NULL;
62
63 END Process_clean_up;
64
65 ----------------------------------------------------------------------------------------------------------------------------
66
67 PROCEDURE confirm_treas_payment(
68 X_treasury_confirmation_id IN NUMBER,
69 X_err_code IN OUT NOCOPY NUMBER,
70 X_err_stage IN OUT NOCOPY VARCHAR2,
71 v_period OUT NOCOPY VARCHAR2)
72 IS
73 l_module_name VARCHAR2(200);
74 v_doc_ctr NUMBER(15);
75 v_chk_ctr NUMBER(15);
76 v_begin_doc fv_treasury_confirmations.begin_doc_num%TYPE;
77 v_end_doc fv_treasury_confirmations.end_doc_num%TYPE;
78 v_payment_instruction_id iby_pay_instructions_all.payment_instruction_id%TYPE;
79 v_confirm_date fv_treasury_confirmations.treasury_doc_date%TYPE;
80 v_diff NUMBER;
81 v_check_num NUMBER;
82 v_delta NUMBER(15);
83 v_treasury_pay_number ap_checks.treasury_pay_number%TYPE;
84
85 -- AKA declare variables for offsets
86 v_corr_treas_pay_num fv_tc_offsets.corrected_treasury_pay_number%TYPE;
87 v_offset_check_id fv_tc_offsets.check_id%TYPE;
88 v_pay_fmt_program_name ap_payment_programs.program_name%TYPE;
89
90 -- declare array to store check_ids
91 TYPE l_check_row IS RECORD ( CHECK_ID NUMBER(15)) ;
92 TYPE l_check_tbl_type IS TABLE OF l_check_row INDEX BY BINARY_INTEGER;
93 l_check_tbl l_check_tbl_type ;
94 TYPE t_refcur IS REF CURSOR;
95 vl_check_id_cur t_refcur;
96 l_row_num NUMBER := 1;
97 L_SELECT_STR VARCHAR2(1000) ;
98
99 -- AKA, declare cursor to select
100 -- corrected treasury pay number and check id from offsets table
101 -- need to join to ap_checks on check id to get the correct batch name
102
103 CURSOR cur_corr_treas_pay_num IS
104 SELECT fto.corrected_treasury_pay_number, fto.check_id
105 FROM fv_tc_offsets fto,
106 ap_checks ac,
107 iby_payments_all ipa
108 WHERE ac.check_id = fto.check_id
109 AND ac.payment_id = ipa.payment_id
110 AND ipa.payment_instruction_id = v_payment_instruction_id
111 AND ipa.org_id = v_org_id;
112
113 CURSOR c_check_ranges IS
114 SELECT ftcr.range_from, ftcr.range_to, ftc.payment_instruction_id,ftc.treasury_doc_date
115 FROM fv_treasury_confirmations ftc,
116 fv_treasury_check_ranges ftcr
117 WHERE ftc.treasury_confirmation_id = g_treasury_confirmation_id
118 AND ftc.treasury_confirmation_id = ftcr.treasury_confirmation_id;
119
120 BEGIN
121 l_module_name := g_module_name || 'confirm_treas_payment ';
122
123 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
124 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'x_treasury_confirmation_id is ' ||
125 x_treasury_confirmation_id);
126 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'starting confirm_treas_payment');
127 END IF;
128 g_treasury_confirmation_id := x_treasury_confirmation_id;
129 x_err_code := 0;
130
131 -- Getting the name of the Payment Instruction
132 SELECT ftc.payment_instruction_id
133 INTO v_payment_instruction_id
134 FROM fv_treasury_confirmations ftc
135 WHERE ftc.treasury_confirmation_id = g_treasury_confirmation_id;
136
137 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
138 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name, 'v_payment_instruction_id is ' || v_payment_instruction_id);
139 END IF;
140
141 -- initializing table with check_ids
142
143 OPEN vl_check_id_cur FOR l_select_str USING v_payment_instruction_id;
144 LOOP
145 FETCH vl_check_id_cur INTO l_check_tbl(l_row_num).check_id;
146
147 l_row_num := l_row_num + 1;
148
149 EXIT WHEN vl_check_id_cur %NOTFOUND;
150 END LOOP;
151
152 l_row_num := 1;
153
154 -- Assigning the treasury Pay number to the respective checks
155 FOR c_check_range_rec IN c_check_ranges LOOP
156 v_begin_doc := c_check_range_rec.range_from;
157 v_end_doc := c_check_range_rec.range_to;
158 v_confirm_date := c_check_range_rec.treasury_doc_date;
159
160 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
161 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'v_payment_instruction_id is ' || v_payment_instruction_id);
162 END IF;
163
164 IF (v_begin_doc IS NULL) OR (v_end_doc IS NULL) OR (v_payment_instruction_id IS NULL) OR ( v_confirm_date IS NULL) THEN
165 x_err_code := 20;
166 x_err_stage := 'Data in treasury confirmation table is missing';
167 RETURN;
168 END IF;
169
170 v_diff := v_end_doc - v_begin_doc + 1;
171
172 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
173 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'v_diff is ' || v_diff);
174 END IF;
175
176 FOR i IN 1.. v_diff
177 LOOP
178
179 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
180 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'l_row_num:'||l_row_num);
181 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'l_check_tbl(l_row_num).check_id:'||
182 l_check_tbl(l_row_num).check_id);
183 END IF;
184
185 UPDATE ap_checks c
186 SET treasury_pay_number = v_begin_doc,
187 treasury_pay_date = v_confirm_date,
188 last_update_date = SYSDATE,
189 last_updated_by = fnd_global.user_id,
190 last_update_login = fnd_global.login_id
191 WHERE c.check_id = l_check_tbl(l_row_num).check_id;
192 l_row_num := l_row_num+1;
193 v_begin_doc := v_begin_doc +1;
194 END LOOP;
195
196 END LOOP;
197
198 -- AKA, need to update ap_checks if a corrected treasury pay number
199 -- for a payment within the batch being processed has been entered
200 OPEN cur_corr_treas_pay_num;
201
202 LOOP
203 FETCH cur_corr_treas_pay_num INTO v_corr_treas_pay_num, v_offset_check_id;
204 EXIT WHEN cur_corr_treas_pay_num%NOTFOUND;
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, 'in corrected treasury pay number loop');
208 END IF;
209
210 IF v_corr_treas_pay_num IS NOT NULL THEN
211 UPDATE ap_checks
212 SET treasury_pay_number = v_corr_treas_pay_num
213 WHERE check_id = v_offset_check_id;
214 END IF;
215 END LOOP;
216 CLOSE cur_corr_treas_pay_num;
217
218
219 -- get name of open period that treasury doc date is in
220 BEGIN
221 SELECT period_name
222 INTO v_period
223 FROM gl_period_statuses g
224 WHERE g.application_id = c_gl_appl_id
225 AND g.set_of_books_id = v_set_of_books_id
226 AND g.closing_status IN ('O', 'F')
227 AND v_confirm_date BETWEEN
228 g.start_date AND g.end_date
229 AND g.adjustment_period_flag = 'N';
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 -- if treasury doc date was not in an open period, then
233 -- get name of next open period
234 BEGIN
235 SELECT period_name
236 INTO v_period
237 FROM gl_period_statuses g
238 WHERE g.application_id = c_gl_appl_id
239 AND g.set_of_books_id = v_set_of_books_id
240 AND g.adjustment_period_flag = 'N'
241 AND g.start_date = (SELECT MIN(start_date)
242 FROM gl_period_statuses g2
243 WHERE g2.application_id = c_gl_appl_id
244 AND g2.set_of_books_id = v_set_of_books_id
245 AND g2.end_date > v_confirm_date
246 AND g2.closing_status IN ('O', 'F')
247 AND g2.adjustment_period_flag = 'N');
248 EXCEPTION
249 WHEN NO_DATA_FOUND THEN
250 x_err_code := 30;
251 x_err_stage :=
252 'No open or future period after treasury documentation date';
253 RETURN;
254 END;
255 END;
256
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 x_err_code := SQLCODE;
261 x_err_stage := SQLERRM;
262 g_errmsg := SQLERRM;
263 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,G_errmsg);
264 RAISE;
265
266
267
268 END confirm_treas_payment;
269
270 ----------------------------------------------------------------------------------------
271
272
273 PROCEDURE get_interface_data
274 (v_chart_of_accounts_id OUT NOCOPY gl_ledgers_public_v.chart_of_accounts_id%TYPE,
275 v_currency_code OUT NOCOPY gl_ledgers_public_v.currency_code%TYPE,
276 x_err_code IN OUT NOCOPY NUMBER,
277 x_err_stage IN OUT NOCOPY VARCHAR2)
278 IS
279 l_module_name VARCHAR2(200);
280 BEGIN
281 l_module_name := g_module_name || 'get_interface_data ';
282 -- Get chart of accounts id, and currency code
283 SELECT chart_of_accounts_id, currency_code
284 INTO v_chart_of_accounts_id, v_currency_code
285 FROM gl_ledgers_public_v
286 WHERE ledger_id = v_set_of_books_id;
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 x_err_code := SQLCODE;
291 x_err_stage := SQLERRM;
292
293 g_errmsg := SQLERRM;
294 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_errmsg);
295 RAISE;
296 END get_interface_data;
297
298 ----------------------------------------------------------------------------------------------------------------------------
299
300 PROCEDURE populate_gl_interface( x_treasury_confirmation_id IN NUMBER,
301 x_group_id IN NUMBER,
302 v_period IN VARCHAR2,
303 x_err_code IN OUT NOCOPY NUMBER,
304 x_err_stage IN OUT NOCOPY VARCHAR2)
305 IS
306 l_module_name VARCHAR2(200);
307 v_structure_number NUMBER;
308 v_segment_number NUMBER;
309 v_segment_name VARCHAR2(30);
310 v_chart_of_accounts_id gl_ledgers_public_v.chart_of_accounts_id%TYPE;
311 c_flex_qual_name VARCHAR2(10);
312 v_n_segments NUMBER;
313 --a_segments fnd_flex_ext.SegmentArray;
314 --LGOEL: Change the declaration for these two variables
315 /*
316 v_amount ap_payment_distributions.amount%TYPE;
317 v_combination_id ap_payment_distributions.dist_code_combination_id%TYPE;
318 */
319 v_amount_acct ap_ae_lines_all.accounted_dr%TYPE;
320 v_amount ap_ae_lines_all.entered_dr%TYPE;
321 v_combination_id ap_ae_lines_all.code_combination_id%TYPE;
322 --LGOEL: Define the following new variables
323 v_cr_amount ap_ae_lines_all.entered_cr%TYPE;
324 v_dr_amount ap_ae_lines_all.entered_dr%TYPE;
325 v_line_type_code ap_ae_lines_all.ae_line_type_code%TYPE;
326 v_functional_curr_code gl_ledgers_public_v.currency_code%TYPE;
327
328 v_accounting_date gl_interface.accounting_date%TYPE;
329 v_boolean BOOLEAN;
330 c_reference1 gl_interface.reference1%TYPE;
331 v_reference21 gl_interface.reference21%TYPE;
332 v_reference3 gl_interface.reference3%TYPE;
333 v_currency_code gl_ledgers_public_v.currency_code%TYPE;
334 v_dr_account_segment_value gl_ussgl_account_pairs.dr_account_segment_value%TYPE;
335 v_cr_account_segment_value gl_ussgl_account_pairs.cr_account_segment_value%TYPE;
336 v_checkrun_name fv_treasury_confirmations.checkrun_name%TYPE ;
337 v_check_id ap_checks.check_id%TYPE;
338 v_vendor_id po_vendors.vendor_id%TYPE;
339 v_period_start_date gl_period_statuses.start_date%TYPE;
340 v_period_end_date gl_period_statuses.end_date%TYPE;
341 v_invoice_id ap_invoice_payments.invoice_id%TYPE;
342 v_cr_acct_amt ap_ae_lines.accounted_cr%TYPE;
343 v_dr_acct_amt ap_ae_lines.accounted_dr%TYPE;
344 v_curr_conv_type ap_ae_lines.currency_conversion_type%TYPE;
345 v_curr_conv_date ap_ae_lines.currency_conversion_date%TYPE;
346 v_curr_conv_rate ap_ae_lines.currency_conversion_rate%TYPE;
347
348 seg_app_name VARCHAR2(40);
349 seg_prompt VARCHAR2(25);
350 seg_value_set_name VARCHAR2(40);
351
352 -- cursor to select all payment distributions that belong to this payment batch that are not voided
353
354 CURSOR payment_dists_cur IS
355 SELECT apd.code_combination_id,
356 apd.entered_cr, -- transaction currency amt
357 apd.entered_dr,
358 apd.accounted_cr, -- functional currency amt
359 apd.accounted_dr,
360 apd.currency_code, -- transction currency
361 apd.currency_conversion_type,
362 apd.currency_conversion_date,
363 apd.currency_conversion_rate,
364 apd.ae_line_type_code,
365 aip.invoice_id,
366 ac.check_id
367 FROM fv_treasury_confirmations ftc,
368 ap_checks ac,
369 ap_invoice_payments aip,
370 ap_ae_lines apd,
371 iby_payments_all ipa
372 WHERE ftc.treasury_confirmation_id = x_treasury_confirmation_id
373 AND ftc.payment_instruction_id = ipa.payment_instruction_id
374 AND ipa.payment_id = ac.payment_id
375 AND ac.check_id = aip.check_id
376 AND (( aip.invoice_payment_id = apd.source_id
377 AND apd.source_table ='AP_INVOICE_PAYMENTS')
378 OR
379 ( aip.check_id = apd.source_id
380 AND apd.source_table = 'AP_CHECKS'))
381 AND apd.ae_line_type_code IN ('CASH','CASH CLEARING','FUTURE PAYMENT')
382 AND ac.void_date IS NULL;
383
384 BEGIN
385
386 l_module_name := g_module_name || 'populate_gl_interface ';
387 c_flex_qual_name := 'GL_ACCOUNT';
388 c_reference1 := 'Treasury';
389
390 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
391 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'starting populate_gl_interface');
392 END IF;
393 x_err_code := 0;
394
395 -- get the data needed to populate the gl_interface table
396 get_interface_data(v_chart_of_accounts_id,
397 v_functional_curr_code, -- base currency
398 x_err_code,
399 x_err_stage);
400 IF x_err_code <> 0 THEN
401 RETURN;
402 END IF;
403
404 -- Set structure number variable
405 v_structure_number := v_chart_of_accounts_id;
406
407
408 v_boolean:=FND_FLEX_APIS.GET_segment_column(c_gl_appl_id,
409 c_key_flex_code,
410 v_structure_number ,
411 'GL_ACCOUNT',
412 v_segment_name);
413
414 v_segment_number := SUBSTR(RTRIM(v_segment_name),8);
415
416 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
417 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'v_segment_number = '||v_segment_number);
418 END IF;
419
420 IF v_boolean = FALSE THEN
421 x_err_code := 20;
422 x_err_stage := 'Get qualifier segment number failed';
423 RETURN;
424 END IF;
425
426
427 -- Obtain DR/CR account segment values pertaining to transaction code
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,'v_period is = ' || v_period);
430 END IF;
431
432 SELECT t.treasury_doc_date
433 INTO v_treasury_doc_date
434 FROM fv_treasury_confirmations t
435 WHERE treasury_confirmation_id = x_treasury_confirmation_id;
436
437 SELECT p.start_date, p.end_date
438 INTO v_period_start_date, v_period_end_date
439 FROM gl_period_statuses p
440 WHERE p.period_name = v_period
441 AND p.application_id = c_gl_appl_id
442 AND p.set_of_books_id = v_set_of_books_id
443 AND p.adjustment_period_flag = 'N';
444
445 -- if treasury doc date is in an open period then use that date,
446 -- otherwise, use the end date of the next open period
447 IF (v_treasury_doc_date
448 BETWEEN v_period_start_date AND v_period_end_date) THEN
449 v_accounting_date := v_treasury_doc_date;
450 ELSE
451 v_accounting_date := v_period_end_date;
452 END IF;
453
454 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
455 FV_UTilitY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'v_chart_of_accounts_id = '||v_chart_of_accounts_id);
456 END IF;
457
458 OPEN payment_dists_cur;
459 LOOP
460 FETCH payment_dists_cur
461 INTO v_combination_id, v_cr_amount,
462 v_dr_amount,
463 v_cr_acct_amt,
464 v_dr_acct_amt,
465 v_currency_code,
466 v_curr_conv_type,
467 v_curr_conv_date,
468 v_curr_conv_rate,
469 v_line_type_code,
470 v_invoice_id, v_check_id;
471 EXIT WHEN payment_dists_cur%NOTFOUND;
472
473 IF (v_line_type_code = 'CASH CLEARING') THEN
474 x_err_code := 1;
475 x_err_stage := 'Perform Payment Reconciliation or Cash Management before running Treasury Confirmation';
476 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,l_module_name||'.cash_clearing',x_err_stage);
477 EXIT;
478 ELSIF (v_line_type_code = 'FUTURE PAYMENT') THEN
479 x_err_code := 1;
480 x_err_stage := 'Create the accounting entries once the payment has matured';
481 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,l_module_name||'.future_payment', 'The accounting entries have not been created for payment maturity');
482 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,l_module_name||'.future_payment', 'Create the accounting entries once the payment has matured');
483 EXIT;
484 END IF;
485
486 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
487 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'in payment_dists_cur cursor loop');
488 END IF;
489
490
491 get_segment_values(v_combination_id);
492
493 -- Set reference21 to be the treasury confirmation id to be stored in
494 -- gl_interface table
495 v_reference21 := x_treasury_confirmation_id;
496
497 -- set reference3 to be the check id to be stored in the gl_interface table
498 v_reference3 := v_check_id;
499
500 -- Overlay natural account segment of the array with the DR account
501 -- segment value and insert into gl_interface
502 IF (v_cr_amount <> 0) THEN
503 a_segments(v_segment_number) := v_dr_account_segment_value;
504 v_amount := v_cr_amount;
505 v_amount_acct := v_cr_acct_amt; -- capture functional amount
506 ELSE
507 a_segments(v_segment_number) := v_cr_account_segment_value;
508 v_amount := v_dr_amount;
509 v_amount_acct := v_dr_acct_amt; -- capture functional amount
510 END IF;
511
512 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
513 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'inserting into gl_interface');
514 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Amount is '||TO_CHAR(v_amount));
515 END IF;
516
517 INSERT INTO gl_interface(status, set_of_books_id,
518 accounting_date, currency_code,
519 functional_currency_code,
520 user_currency_conversion_type,
521 currency_conversion_date,
522 currency_conversion_rate,
523 date_created, created_by,
524 actual_flag, user_je_category_name,
525 user_je_source_name, segment1,
526 segment2, segment3,
527 segment4, segment5,
528 segment6, segment7,
529 segment8, segment9,
530 segment10, segment11,
531 segment12, segment13,
532 segment14, segment15,
533 segment16, segment17,
534 segment18, segment19,
535 segment20, segment21,
536 segment22, segment23,
537 segment24, segment25,
538 segment26, segment27,
539 segment28, segment29,
540 segment30, entered_dr,
541 accounted_dr,
542 reference1, reference21,
543 reference23, reference24,
544 reference25, reference26,
545 group_id)
546 VALUES('NEW', v_set_of_books_id,
547 v_accounting_date, v_currency_code,
548 v_functional_curr_code,
549 v_curr_conv_type,
550 v_curr_conv_date,
551 v_curr_conv_rate,
552 SYSDATE, fnd_global.user_id,
553 'A', 'Treasury Confirmation',
554 'Payables', a_segments(1),
555 a_segments(2), a_segments(3),
556 a_segments(4), a_segments(5),
557 a_segments(6), a_segments(7),
558 a_segments(8), a_segments(9),
559 a_segments(10), a_segments(11),
560 a_segments(12), a_segments(13),
561 a_segments(14), a_segments(15),
562 a_segments(16), a_segments(17),
563 a_segments(18), a_segments(19),
564 a_segments(20), a_segments(21),
565 a_segments(22), a_segments(23),
566 a_segments(24), a_segments(25),
567 a_segments(26), a_segments(27),
568 a_segments(28), a_segments(29),
569 a_segments(30), v_amount, -- transaction amt
570 v_amount_acct, -- functional amt
571 c_reference1, v_reference21,
572 v_reference3, v_invoice_id,
573 v_org_id, v_treasury_doc_date,
574 x_group_id);
575
576 -- Overlay natural account segment of the array with the CR account
577 -- segment value and insert into gl_interface
578 --a_segments(v_segment_number) := v_cr_account_segment_value;
579 IF (v_cr_amount <> 0) THEN
580 a_segments(v_segment_number) := v_cr_account_segment_value;
581 ELSE
582 a_segments(v_segment_number) := v_dr_account_segment_value;
583 END IF;
584 INSERT INTO gl_interface(status, set_of_books_id,
585 accounting_date, currency_code,
586 functional_currency_code,
587 user_currency_conversion_type,
588 currency_conversion_date,
589 currency_conversion_rate,
590 date_created, created_by,
591 actual_flag, user_je_category_name,
592 user_je_source_name, segment1,
593 segment2, segment3,
594 segment4, segment5,
595 segment6, segment7,
596 segment8, segment9,
597 segment10, segment11,
598 segment12, segment13,
599 segment14, segment15,
600 segment16, segment17,
601 segment18, segment19,
602 segment20, segment21,
603 segment22, segment23,
604 segment24, segment25,
605 segment26, segment27,
606 segment28, segment29,
607 segment30, entered_cr, -- transaction amt
608 accounted_cr, -- functional amt
609 reference1, reference21,
610 reference23, reference24,
611 reference25, reference26,
612 group_id)
613 VALUES('NEW', v_set_of_books_id,
614 v_accounting_date, v_currency_code,
615 v_functional_curr_code,
616 v_curr_conv_type,
617 v_curr_conv_date,
618 v_curr_conv_rate,
619 SYSDATE, fnd_global.user_id,
620 'A', 'Treasury Confirmation',
621 'Payables', a_segments(1),
622 a_segments(2), a_segments(3),
623 a_segments(4), a_segments(5),
624 a_segments(6), a_segments(7),
625 a_segments(8), a_segments(9),
626 a_segments(10), a_segments(11),
627 a_segments(12), a_segments(13),
628 a_segments(14), a_segments(15),
629 a_segments(16), a_segments(17),
630 a_segments(18), a_segments(19),
631 a_segments(20), a_segments(21),
632 a_segments(22), a_segments(23),
633 a_segments(24), a_segments(25),
634 a_segments(26), a_segments(27),
635 a_segments(28), a_segments(29),
636 a_segments(30), v_amount,-- transaction amt
637 v_amount_acct, -- functional amt
638 c_reference1, v_reference21,
639 v_reference3, v_invoice_id,
640 v_org_id, v_treasury_doc_date,
641 x_group_id);
642 END LOOP;
643 CLOSE payment_dists_cur;
644
645 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
646 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'insert into gl_interface');
647 END IF;
648 EXCEPTION
649 WHEN OTHERS THEN
650 g_errmsg := SQLERRM;
651 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_errmsg);
652 RAISE;
653 END populate_gl_interface;
654
655 ----------------------------------------------------------------------------------------------------------------------------
656 PROCEDURE cleanup_gl_interface( X_treasury_confirmation_id IN NUMBER,
657 v_process_job IN VARCHAR2,
658 x_group_id IN NUMBER,
659 X_err_code IN OUT NOCOPY NUMBER,
660 X_err_stage IN OUT NOCOPY VARCHAR2)
661 IS
662 l_module_name VARCHAR2(200);
663 BEGIN
664 l_module_name := g_module_name || 'cleanup_gl_interface ';
665 x_err_code := 0;
666
667 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'An error has occurred during Journal Import. Please review Journal Import Execution Report.');
668 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'The journal has been removed from the Interface table.');
669 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'After correcting error please reselect Confirm or Back OUT NOCOPY from Treasury Confirmation or resubmit Disbursements in Transit Voided Checks.');
670 -- Delete records from GL-INTERFACE for a particular group id
671 -- and update the confirmation status flag to 'N'
672 DELETE FROM GL_INTERFACE
673 WHERE user_je_source_name = 'Payables'
674 AND set_of_books_id = v_set_of_books_id
675 AND group_id = x_group_id;
676
677 -- if the button that is called is either confirm or backout, not void
678 IF v_process_job IN ('C', 'B') THEN
679 UPDATE fv_treasury_confirmations
680 -- if the process is confirm, status is Not Confirmed,
681 -- if the process is backout, status is Confirmed
682 SET confirmation_status_flag = DECODE(v_process_job, 'C', 'N', 'B', 'Y'),
683 gl_period = NULL,
684 last_update_date = SYSDATE,
685 last_updated_by = fnd_global.user_id,
686 last_update_login = fnd_global.login_id
687 WHERE treasury_confirmation_id = x_treasury_confirmation_id;
688 END IF;
689
690 x_err_code := 2;
691 X_err_stage := 'There was an error importing GL Interface records. Look at the GL Import Log File.';
692
693 EXCEPTION
694 WHEN OTHERS THEN
695 x_err_code := SQLCODE;
696 x_err_stage := SQLERRM;
697 END cleanup_gl_interface;
698
699 ----------------------------------------------------------------------------------------------------------------------------
700
701 PROCEDURE do_confirm_process( v_treasury_confirmation_id IN NUMBER,
702 x_group_id IN OUT NOCOPY NUMBER,
703 x_err_code IN OUT NOCOPY NUMBER,
704 x_err_stage IN OUT NOCOPY VARCHAR2,
705 v_period IN OUT NOCOPY VARCHAR2)
706 IS
707 l_module_name VARCHAR2(200);
708 v_dit_flag fv_operating_units.dit_flag%TYPE;
709 BEGIN
710 l_module_name := g_module_name || 'do_confirm_process ';
711 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
712 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'starting do_confirm_process');
713 END IF;
714
715 -- initialize variables
716 x_err_code := 0;
717 x_group_id := 0;
718
719
720 -- Call first program to update treasury payments FV_DISB_IN_TRANSIT.
721 fv_disb_in_transit.confirm_treas_payment (
722 v_treasury_confirmation_id,
723 x_err_code,
724 x_err_stage,
725 v_period);
726
727 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
728 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'x_err_code is ' || x_err_code);
729 END IF;
730
731 IF (x_err_code <> 0) THEN
732 RETURN;
733 END IF;
734
735 -- Assign the group id to be a sequence number from the gl_interface_control seq
736 SELECT gl_interface_control_s.NEXTVAL
737 INTO x_group_id
738 FROM dual;
739
740 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
741 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'x_group_id = '||x_group_id);
742 END IF;
743
744 -- Assign the dit_flag from fv_operating_units_all table
745 SELECT dit_flag
746 INTO v_dit_flag
747 FROM fv_operating_units;
748
749 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
750 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'dit flag = '||v_dit_flag);
751 END IF;
752 IF v_dit_flag = 'Y' THEN
753 -- If dit_flag = 'Y' then populate the gl_interface table
754 -- otherwise do not do anything.
755
756 fv_disb_in_transit.populate_gl_interface(
757 v_treasury_confirmation_id,
758 x_group_id, v_period, x_err_code,x_err_stage);
759
760 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
761 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'called populate gl_interface from do confirm process');
762 END IF;
763
764 IF x_err_code <> 0 THEN
765 RETURN;
766 END IF;
767
768 END IF;
769
770 EXCEPTION
771 WHEN OTHERS THEN
772 x_err_code := SQLCODE;
773 x_err_stage := SQLERRM;
774 g_errmsg := SQLERRM;
775 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_errmsg);
776 RAISE;
777 END do_confirm_process;
778
779 ----------------------------------------------------------------------------------------------------------------------------
780
781 PROCEDURE do_back_out_process
782 -- AKA, 8/10/99, removed passing of v_treasury_confirmation_id
783 (x_group_id IN OUT NOCOPY NUMBER,
784 x_err_code IN OUT NOCOPY NUMBER,
785 x_err_stage IN OUT NOCOPY VARCHAR2)
786 IS
787 l_module_name VARCHAR2(200);
788 v_checkrun_name fv_treasury_confirmations.checkrun_name%TYPE;
789 v_chart_of_accounts_id gl_ledgers_public_v.chart_of_accounts_id%TYPE;
790 v_currency_code gl_ledgers_public_v.currency_code%TYPE;
791 v_func_currency_code gl_ledgers_public_v.currency_code%TYPE;
792 v_credit_amount gl_je_lines.entered_dr%TYPE;
793 v_debit_amount gl_je_lines.entered_cr%TYPE;
794 v_cc_id gl_je_lines.code_combination_id%TYPE;
795 v_n_segments NUMBER;
796 -- a_segments fnd_flex_ext.SegmentArray;
797 v_boolean BOOLEAN;
798 c_reference1 gl_interface.reference1%TYPE;
799 v_tc_id gl_interface.reference1%TYPE;
800 v_check_id ap_checks.check_id%TYPE;
801 v_invoice_id gl_je_lines.reference_4%TYPE;
802 v_period gl_period_statuses.period_name%TYPE;
803 v_period_start_date gl_period_statuses.start_date%TYPE;
804 v_period_end_date gl_period_statuses.end_date%TYPE;
805 v_accounting_date gl_interface.accounting_date%TYPE;
806 v_cr_acct_amt gl_je_lines.accounted_cr%TYPE;
807 v_dr_acct_amt gl_je_lines.accounted_dr%TYPE;
808 v_curr_con_type gl_je_headers.currency_conversion_type%TYPE;
809 v_curr_con_rate gl_je_headers.currency_conversion_rate%TYPE;
810 v_curr_con_date ap_checks.exchange_date%TYPE;
811
812 BEGIN
813 -- Initialize variables
814 l_module_name := g_module_name || 'do_back_out_process ';
815 c_reference1 := 'Treasury';
816 x_err_code := 0;
817 x_group_id := NULL;
818
819 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
820 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
821 'starting do_back_out_process');
822 END IF;
823
824 SELECT treasury_doc_date
825 INTO v_treasury_doc_date
826 FROM fv_treasury_confirmations
827 WHERE treasury_confirmation_id = v_treasury_confirmation_id;
828
829
830 -- get name of open period that treasury doc date is in
831 BEGIN
832 SELECT period_name,g.start_date,g.end_date
833 INTO v_period,v_period_start_date,v_period_end_date
834 FROM gl_period_statuses g
835 WHERE g.application_id = c_gl_appl_id
836 AND g.set_of_books_id = v_set_of_books_id
837 AND g.closing_status IN ('O', 'F')
838 AND v_treasury_doc_date BETWEEN
839 g.start_date AND g.end_date
840 AND g.adjustment_period_flag = 'N';
841 EXCEPTION
842 WHEN NO_DATA_FOUND THEN
843 -- if treasury doc date was not in an open period, then
844 -- get name of next open period
845 BEGIN
846 SELECT period_name,g.end_date,g.start_date
847 INTO v_period,v_period_end_date,v_period_start_date
848 FROM gl_period_statuses g
849 WHERE g.application_id = c_gl_appl_id
850 AND g.set_of_books_id = v_set_of_books_id
851 AND g.adjustment_period_flag = 'N'
852 AND g.start_date = (SELECT MIN(start_date)
853 FROM gl_period_statuses g2
854 WHERE g2.application_id = c_gl_appl_id
855 AND g2.set_of_books_id = v_set_of_books_id
856 AND g2.end_date > v_treasury_doc_date
857 AND g2.closing_status IN ('O', 'F')
858 AND g2.adjustment_period_flag = 'N');
859 EXCEPTION
860 WHEN NO_DATA_FOUND THEN
861 x_err_code := 30;
862 x_err_stage :=
863 'No open or future period after treasury documentation date'; RETURN;
864 END;
865 END;
866
867 -- if treasury doc date is in an open period then use that date,
868 -- otherwise, use the end date of the next open period
869 IF (v_treasury_doc_date
870 BETWEEN v_period_start_date AND v_period_end_date) THEN
871 v_accounting_date := v_treasury_doc_date;
872 ELSE
873 v_accounting_date := v_period_end_date;
874 END IF;
875
876
877
878 -- get data to populate the gl_interface table.
879 get_interface_data(v_chart_of_accounts_id, v_func_currency_code,
880 x_err_code, x_err_stage);
881
882 IF x_err_code <> 0 THEN
883 RETURN;
884 END IF;
885
886 -- Assign the group id to be a sequence number
887 --from the gl_interface_control seq
888
889 SELECT gl_interface_control_s.NEXTVAL
890 INTO x_group_id
891 FROM dual;
892
893 -- Find the gl_je_lines records that are associated with the
894 -- treasury_confirmation_id that is being backed out.
895
896 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
897 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
898 'starting je_lines loop for backout');
899 END IF;
900
901 v_credit_amount := NULL;
902 v_debit_amount := NULL;
903 v_cr_acct_amt := NULL;
904 v_dr_acct_amt := NULL;
905 FOR c_je_lines_rec IN c_je_lines LOOP
906 -- Initialize variables
907
908 -- back out what occurred by setting the debit amount =
909 -- original credit
910 -- amount and the credit amount = original debit amount.
911 v_credit_amount := c_je_lines_rec.entered_dr;
912 v_debit_amount := c_je_lines_rec.entered_cr;
913 v_cc_id := c_je_lines_rec.code_combination_id;
914 v_check_id := c_je_lines_rec.check_id;
915 v_invoice_id := c_je_lines_rec.reference_4;
916 v_currency_code := c_je_lines_rec.currency_code;
917 v_cr_acct_amt := c_je_lines_rec.accounted_dr;
918 v_dr_acct_amt := c_je_lines_rec.accounted_cr;
919 v_curr_con_type := c_je_lines_rec.currency_conversion_type;
920 v_curr_con_rate := c_je_lines_rec.currency_conversion_rate;
921 v_curr_con_date := c_je_lines_rec.exchange_date;
922 get_segment_values(v_cc_id);
923
924 v_tc_id := TO_CHAR(v_treasury_confirmation_id);
925
926 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
927 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'starting insert');
928 END IF;
929
930
931
932 INSERT INTO gl_interface(status, set_of_books_id,
933 accounting_date, currency_code,
934 user_currency_conversion_type,
935 currency_conversion_rate,
936 currency_conversion_date,
937 functional_currency_code,
938 date_created, created_by,
939 actual_flag, user_je_category_name,
940 user_je_source_name, segment1,
941 segment2, segment3,
942 segment4, segment5,
943 segment6, segment7,
944 segment8, segment9,
945 segment10, segment11,
946 segment12, segment13,
947 segment14, segment15,
948 segment16, segment17,
949 segment18, segment19,
950 segment20, segment21,
951 segment22, segment23,
952 segment24, segment25,
953 segment26, segment27,
954 segment28, segment29,
955 segment30,
956 entered_dr, entered_cr,
957 accounted_dr,accounted_cr,
958 reference1,
959 reference21,
960 reference23,
961 reference24,
962 reference25,
963 reference26,
964 group_id)
965 VALUES('NEW', v_set_of_books_id,
966 v_accounting_date, v_currency_code,
967 v_curr_con_type,v_curr_con_rate,
968 v_curr_con_date,
969 v_func_currency_code,
970 SYSDATE, fnd_global.user_id,
971 'A', 'Treasury Confirmation',
972 'Payables', a_segments(1),
973 a_segments(2), a_segments(3),
974 a_segments(4), a_segments(5),
975 a_segments(6), a_segments(7),
976 a_segments(8), a_segments(9),
977 a_segments(10), a_segments(11),
978 a_segments(12), a_segments(13),
979 a_segments(14), a_segments(15),
980 a_segments(16), a_segments(17),
981 a_segments(18), a_segments(19),
982 a_segments(20), a_segments(21),
983 a_segments(22), a_segments(23),
984 a_segments(24), a_segments(25),
985 a_segments(26), a_segments(27),
986 a_segments(28), a_segments(29),
987 a_segments(30),
988 v_debit_amount, v_credit_amount,
989 v_dr_acct_amt,v_cr_acct_amt,
990 c_reference1,
991 v_tc_id,
992 v_check_id,
993 v_invoice_id,
994 v_org_id,
995 v_treasury_doc_date,
996 x_group_id);
997
998 v_credit_amount := NULL;
999 v_debit_amount := NULL;
1000 v_cr_acct_amt := NULL;
1001 v_dr_acct_amt := NULL;
1002 END LOOP;
1003
1004 EXCEPTION
1005 WHEN OTHERS THEN
1006 g_errmsg := SQLERRM;
1007 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_errmsg);
1008 RAISE;
1009 END do_back_out_process;
1010 ----------------------------------------------------------------------------------------------------------------------------
1011 PROCEDURE void
1012 (
1013 errbuf OUT NOCOPY VARCHAR2,
1014 retcode OUT NOCOPY VARCHAR2
1015 )
1016 IS
1017 l_module_name VARCHAR2(200);
1018 l_group_id NUMBER;
1019 l_err_code NUMBER;
1020 l_err_stage VARCHAR2(2000);
1021 l_chart_of_accounts_id gl_ledgers_public_v.chart_of_accounts_id%TYPE;
1022 l_org_id NUMBER;
1023 l_func_currency_code gl_ledgers_public_v.currency_code%TYPE;
1024 l_reference1 gl_interface.reference1%TYPE;
1025 l_insert_required BOOLEAN := FALSE;
1026 l_count_void NUMBER := 0;
1027 l_interface_run_id NUMBER;
1028 l_req_id NUMBER;
1029 l_call_status BOOLEAN;
1030 l_rphase VARCHAR2(30);
1031 l_rstatus VARCHAR2(30);
1032 l_dphase VARCHAR2(30);
1033 l_dstatus VARCHAR2(30);
1034 l_message VARCHAR2(240);
1035 l_dummy NUMBER;
1036 -- l_je_exists_for_check BOOLEAN := FALSE;
1037 l_processed_flag fv_voided_checks.processed_flag%TYPE;
1038 l_treasury_conf_id1 NUMBER;
1039 l_insert_je_line BOOLEAN := FALSE;
1040 l_treasury_conf_id NUMBER;
1041 v_accounting_date gl_period_statuses.end_date%TYPE;
1042 l_set_of_books_name VARCHAR2(200);
1043
1044 CURSOR voided_checks_list_cursor
1045 (
1046 p_set_of_books_id NUMBER,
1047 p_org_id NUMBER
1048 ) IS
1049 SELECT /*+ ordered */ DISTINCT
1050 fvt.check_id,
1051 fvt.rowid,
1052 ftc.treasury_confirmation_id,
1053 fvt.processed_flag,
1054 ac.exchange_date,
1055 ac.void_date
1056 FROM fv_treasury_confirmations_all ftc,
1057 fv_voided_checks fvt,
1058 ap_checks_all ac ,
1059 ap_invoice_payments_all apip,
1060 iby_payments_all ipa
1061 WHERE ac.check_id = fvt.check_id
1062 AND ac.payment_id = ipa.payment_id
1063 AND ipa.payment_instruction_id = ftc.payment_instruction_id
1064 AND ftc.checkrun_name IS NULL
1065 AND NVL(fvt.processed_flag,'U') IN ('U', 'S')
1066 AND ftc.set_of_books_id = p_set_of_books_id
1067 AND ftc.org_id = p_org_id
1068 AND ac.org_id = p_org_id
1069 AND fvt.org_id = p_org_id
1070 AND apip.check_id = ac.check_id
1071 AND apip.reversal_flag = 'Y'
1072 AND apip.reversal_inv_pmt_id is not null
1073 AND ac.last_update_date < fvt.creation_date
1074 AND ac.status_lookup_code = 'VOIDED'
1075 AND (
1076 (ftc.confirmation_status_flag = 'Y'
1077 AND apip.creation_date > ftc.creation_date)
1078 OR
1079 (ftc.confirmation_status_flag in ('B','N')
1080 AND apip.creation_date BETWEEN ftc.creation_date and ftc.last_update_date)
1081 )
1082 AND EXISTS (SELECT null
1083 FROM gl_ledgers_public_v glpv,
1084 gl_je_headers gjh,
1085 gl_je_lines gjl
1086 WHERE glpv.ledger_id = p_set_of_books_id
1087 --AND gjh.set_of_books_id = gsob.set_of_books_id
1088 AND gjh.ledger_id = glpv.ledger_id
1089 AND gjh.je_category = 'Treasury Confirmation'
1090 AND gjh.je_source = 'Payables'
1091 AND gjh.je_header_id = gjl.je_header_id
1092 --AND gjl.set_of_books_id = gsob.set_of_books_id
1093 AND gjl.ledger_id = glpv.ledger_id
1094 AND gjl.reference_3 = to_char(ac.check_id)
1095 AND gjl.reference_1 = to_char(ftc.treasury_confirmation_id)
1096 AND gjl.reference_4 = to_char(apip.invoice_id)
1097 );
1098
1099
1100 CURSOR cur_process_void_check
1101 (
1102 p_set_of_books_id NUMBER,
1103 p_check_id NUMBER,
1104 p_treas_conf_id NUMBER
1105 ) IS
1106 SELECT /*+ choose*/
1107 gjl.entered_dr,
1108 gjl.entered_cr,
1109 gjh.currency_conversion_type,
1110 gjh.currency_conversion_rate,
1111 gjl.code_combination_id,
1112 gjl.accounted_cr,
1113 gjl.accounted_dr,
1114 gjh.currency_code,
1115 gjl.reference_4 invoice_id,
1116 gjl.rowid gl_rowid
1117 FROM gl_ledgers_public_v glpv,
1118 gl_je_lines gjl,
1119 gl_je_headers gjh
1120 WHERE glpv.ledger_id = p_set_of_books_id
1121 AND gjh.ledger_id = glpv.ledger_id
1122 AND gjl.ledger_id = glpv.ledger_id
1123 AND gjh.je_category = 'Treasury Confirmation'
1124 AND gjh.je_source = 'Payables'
1125 AND gjl.reference_3 = p_check_id
1126 AND gjl.reference_1 = p_treas_conf_id
1127 AND gjh.je_header_id = gjl.je_header_id;
1128
1129
1130 BEGIN
1131 l_module_name := g_module_name || 'void';
1132 l_reference1 := 'Void';
1133 errbuf := NULL;
1134 retcode := '0';
1135
1136 l_org_id := mo_global.get_current_org_id;
1137
1138 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1139 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'org id = '|| l_org_id);
1140 END IF;
1141
1142 mo_utils.get_ledger_info(l_org_id,v_set_of_books_id,l_set_of_books_name);
1143 -- v_set_of_books_id := TO_NUMBER(fnd_profile.value('GL_SET_OF_BKS_ID')); --;>--
1144
1145 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1146 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'set of books id = '||v_set_of_books_id);
1147 END IF;
1148
1149 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1150 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'INSERT INTO fv_voided_checks');
1151 END IF;
1152
1153 BEGIN
1154 INSERT INTO fv_voided_checks
1155 (
1156 void_id,
1157 checkrun_name,
1158 check_id,
1159 processed_flag,
1160 creation_date,
1161 created_by,
1162 last_update_date,
1163 last_updated_by,
1164 last_update_login,
1165 org_id
1166 )
1167 SELECT fv_voided_checks_s.nextval,
1168 checkrun_name,
1169 check_id,
1170 'U',
1171 SYSDATE,
1172 fnd_global.user_id,
1173 SYSDATE,
1174 fnd_global.user_id,
1175 fnd_global.login_id,
1176 org_id
1177 FROM ap_checks_all ac
1178 WHERE org_id = l_org_id
1179 AND void_date IS NOT NULL
1180 AND (ac.checkrun_name IS NOT NULL OR ac.payment_id IS NOT NULL)
1181 AND NOT EXISTS (SELECT 1
1182 FROM fv_voided_checks fvc
1183 WHERE fvc.check_id = ac.check_id
1184 AND fvc.org_id = ac.org_id);
1185
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 l_err_code := SQLCODE;
1189 l_err_stage := SQLERRM;
1190 retcode := '2';
1191 fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'insert fv_voided_checks1',l_err_stage);
1192 END;
1193
1194
1195 IF (retcode = '0') THEN
1196 get_interface_data
1197 (
1198 v_chart_of_accounts_id => l_chart_of_accounts_id,
1199 v_currency_code => l_func_currency_code,
1200 x_err_code => l_err_code,
1201 x_err_stage => l_err_stage
1202 );
1203
1204 IF l_err_code <> 0 THEN
1205 fv_utility.debug_mesg(fnd_log.level_error,l_module_name,'Error '||l_err_code||' at '||l_err_stage);
1206 retcode := '2';
1207 errbuf := 'Error in get_interface_data';
1208 END IF;
1209 END IF;
1210
1211 IF (retcode = '0') THEN
1212 SELECT gl_interface_control_s.NEXTVAL
1213 INTO l_group_id
1214 FROM dual;
1215
1216 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1217 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'l_group_id is ' || l_group_id);
1218 END IF;
1219 END IF;
1220
1221 -- Find the gl_je_lines records that are associated with the
1222 -- check_id that is being backed out.
1223
1224 IF (retcode = '0') THEN
1225 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1226 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'starting je_lines loop for voided checks');
1227 END IF;
1228
1229 FOR voided_checks_list_rec IN voided_checks_list_cursor (v_set_of_books_id, l_org_id) LOOP
1230 FOR cur_process_void_check_rec IN cur_process_void_check (v_set_of_books_id,
1231 voided_checks_list_rec.check_id,
1232 voided_checks_list_rec.treasury_confirmation_id) LOOP
1233
1234 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1235 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name, 'Fetched check id is = '||voided_checks_list_rec.check_id);
1236 END IF;
1237
1238 select accounting_date into v_accounting_date
1239 from ap_invoice_payments_all
1240 where check_id =voided_checks_list_rec.check_id
1241 and REVERSAL_INV_PMT_ID is not null
1242 and REVERSAL_FLAG = 'Y'
1243 and rownum =1;
1244
1245 -- get name of open period that void gl date of check is in
1246 BEGIN
1247 SELECT end_date
1248 INTO v_accounting_date
1249 FROM gl_period_statuses g
1250 WHERE g.application_id = c_gl_appl_id
1251 AND g.set_of_books_id = v_set_of_books_id
1252 AND g.closing_status IN ('O', 'F')
1253 AND v_accounting_date BETWEEN
1254 g.start_date AND g.end_date
1255 AND g.adjustment_period_flag = 'N';
1256 EXCEPTION
1257 WHEN NO_DATA_FOUND THEN
1258
1259 -- if void gl date was not in an open period, then
1260 -- get name of next open period
1261 BEGIN
1262 SELECT end_date
1263 INTO v_accounting_date
1264 FROM gl_period_statuses g
1265 WHERE g.application_id = c_gl_appl_id
1266 AND g.set_of_books_id = v_set_of_books_id
1267 AND g.adjustment_period_flag = 'N'
1268 AND g.start_date = (SELECT MIN(start_date)
1269 FROM gl_period_statuses g2
1270 WHERE g2.application_id = c_gl_appl_id
1271 AND g2.set_of_books_id = v_set_of_books_id
1272 AND g2.end_date > v_accounting_date
1273 AND g2.closing_status IN ('O', 'F')
1274 AND g2.adjustment_period_flag = 'N');
1275
1276
1277 EXCEPTION
1278 WHEN NO_DATA_FOUND THEN
1279
1280 l_err_code := SQLCODE;
1281 l_err_stage := SQLERRM;
1282 retcode := '2';
1283 errbuf := l_err_stage;
1284 fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'No open or future period after voiding check date',l_err_stage);
1285
1286 --fnd_file.put_line (fnd_file.log ,'No open or future period after voiding check date');
1287
1288 EXIT;
1289 END;
1290
1291 END;
1292
1293
1294
1295 BEGIN
1296 INSERT INTO gl_interface
1297 (
1298 status,
1299 set_of_books_id,
1300 accounting_date,
1301 currency_code,
1302 user_currency_conversion_type,
1303 currency_conversion_date,
1304 currency_conversion_rate,
1305 functional_currency_code,
1306 date_created,
1307 created_by,
1308 actual_flag,
1309 user_je_category_name,
1310 user_je_source_name,
1311 entered_dr,
1312 entered_cr,
1313 accounted_dr,
1314 accounted_cr,
1315 reference1,
1316 reference24,
1317 reference21,
1318 reference23,
1319 reference25,
1320 reference26,
1321 group_id,
1322 code_combination_id
1323 )
1324 VALUES
1325 (
1326 'NEW',
1327 v_set_of_books_id,
1328 v_accounting_date,
1329 cur_process_void_check_rec.currency_code,
1330 cur_process_void_check_rec.currency_conversion_type,
1331 voided_checks_list_rec.exchange_date,
1332 cur_process_void_check_rec.currency_conversion_rate,
1333 l_func_currency_code,
1334 SYSDATE,
1335 fnd_global.user_id,
1336 'A',
1337 'Treasury Confirmation',
1338 'Payables',
1339 cur_process_void_check_rec.entered_cr,
1340 cur_process_void_check_rec.entered_dr,
1341 cur_process_void_check_rec.accounted_cr,
1342 cur_process_void_check_rec.accounted_dr,
1343 l_reference1,
1344 cur_process_void_check_rec.invoice_id,
1345 voided_checks_list_rec.treasury_confirmation_id,
1346 voided_checks_list_rec.check_id,
1347 l_org_id,
1348 voided_checks_list_rec.void_date,
1349 l_group_id,
1350 cur_process_void_check_rec.code_combination_id
1351 );
1352
1353 l_count_void := l_count_void + 1;
1354 EXCEPTION
1355 WHEN OTHERS THEN
1356 l_err_code := SQLCODE;
1357 l_err_stage := SQLERRM;
1358 retcode := '2';
1359 fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'insert gl_interface',l_err_stage);
1360 EXIT;
1361 END;
1362
1363 IF (retcode = '0') THEN
1364 BEGIN
1365
1366 UPDATE fv_voided_checks
1367 SET processed_flag = 'S'
1368 WHERE check_id = voided_checks_list_rec.check_id
1369 AND org_id = l_org_id;
1370
1371 EXCEPTION
1372 WHEN OTHERS THEN
1373 l_err_code := SQLCODE;
1374 l_err_stage := SQLERRM;
1375 retcode := '2';
1376 fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'update fv_voided_checks1',l_err_stage);
1377 EXIT;
1378 END;
1379 END IF;
1380 IF (retcode <> '0') THEN
1381 EXIT;
1382 END IF;
1383 END LOOP;
1384 END LOOP;
1385 END IF;
1386
1387
1388 IF ((retcode = '0') AND (l_count_void > 0)) THEN
1389 -- Obtain the interface run id
1390 l_interface_run_id := gl_interface_control_pkg.get_unique_run_id;
1391
1392 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1393 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'interface_run_id = '||l_interface_run_id);
1394 END IF;
1395 END IF;
1396
1397
1398 IF ((retcode = '0') AND (l_count_void > 0)) THEN
1399 -- Insert a control record in Gl_INTERFACE record for the Gl
1400 -- Import to work
1401 BEGIN
1402 INSERT INTO gl_interface_control
1403 (
1404 je_source_name,
1405 status,
1406 interface_run_id,
1407 group_id,
1408 set_of_books_id
1409 )
1410 VALUES
1411 (
1412 'Payables',
1413 'S',
1414 l_interface_run_id,
1415 l_group_id,
1416 v_set_of_books_id
1417 );
1418 EXCEPTION
1419 WHEN OTHERS THEN
1420 l_err_code := SQLCODE;
1421 l_err_stage := SQLERRM;
1422 retcode := '2';
1423 fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'insert gl_interface_control',l_err_stage);
1424 END;
1425 END IF;
1426
1427 IF ((retcode = '0') AND (l_count_void > 0)) THEN
1428
1429 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1430 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'submitting a request');
1431 END IF;
1432
1433 l_req_id := fnd_request.submit_request
1434 (
1435 'SQLGL',
1436 'GLLEZL',
1437 '',
1438 '',
1439 FALSE,
1440 TO_CHAR(l_interface_run_id),
1441 TO_CHAR(v_set_of_books_id),
1442 'N', '', '', 'N', 'N');
1443
1444 -- if concurrent request submission failed then abort process
1445 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1446 fv_utility.debug_mesg(fnd_log.level_statement,l_module_name,'req_id = '||l_req_id);
1447 END IF;
1448
1449 IF (l_req_id = 0) THEN
1450 errbuf := 'Can not submit journal import program';
1451 retcode := '2';
1452 fv_utility.log_mesg(fnd_log.level_statement,l_module_name,errbuf);
1453 END IF;
1454 END IF;
1455
1456 IF (retcode = '0') THEN
1457 COMMIT;
1458 ELSE
1459 ROLLBACK;
1460 END IF;
1461
1462 IF ((retcode = '0') AND (l_count_void > 0)) THEN
1463 -- Check status of completed concurrent program
1464 -- and if complete exit
1465 l_call_status := fnd_concurrent.wait_for_request
1466 (
1467 l_req_id,
1468 20,
1469 0,
1470 l_rphase,
1471 l_rstatus,
1472 l_dphase,
1473 l_dstatus,
1474 l_message
1475 );
1476
1477 IF (l_call_status = FALSE) THEN
1478 errbuf := 'Can not wait for the status of journal import';
1479 retcode := '2';
1480 fv_utility.log_mesg(fnd_log.level_exception,l_module_name,errbuf);
1481 END IF;
1482 END IF;
1483
1484 IF ((retcode = '0') AND (l_count_void > 0)) THEN
1485 -- Do rows exist in the GL_INTERFACE table ?
1486
1487 SELECT COUNT(*)
1488 INTO l_dummy
1489 FROM gl_interface
1490 WHERE group_id = l_group_id
1491 AND set_of_books_id = v_set_of_books_id
1492 AND user_je_source_name = 'Payables';
1493
1494 -- If any records exist in GL_INTERFACE then clean them up
1495 IF (l_dummy > 0) THEN
1496 cleanup_gl_interface
1497 (
1498 NULL,
1499 NULL,
1500 l_group_id,
1501 l_err_code,
1502 l_err_stage
1503 );
1504 retcode := l_err_code;
1505 errbuf := l_err_stage;
1506 ELSE
1507 BEGIN
1508 UPDATE fv_voided_checks
1509 SET processed_flag = 'P'
1510 WHERE processed_flag = 'S'
1511 AND org_id = l_org_id;
1512 EXCEPTION
1513 WHEN OTHERS THEN
1514 l_err_code := SQLCODE;
1515 l_err_stage := SQLERRM;
1516 retcode := '2';
1517 errbuf := l_err_stage;
1518 fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'update fv_voided_checks2',l_err_stage);
1519 END;
1520 BEGIN
1521 UPDATE fv_voided_checks
1522 SET processed_flag = 'X'
1523 WHERE processed_flag = 'U'
1524 AND org_id = l_org_id;
1525 EXCEPTION
1526 WHEN OTHERS THEN
1527 l_err_code := SQLCODE;
1528 l_err_stage := SQLERRM;
1529 retcode := '2';
1530 errbuf := l_err_stage;
1531 fv_utility.log_mesg(fnd_log.level_exception,l_module_name||'update fv_voided_checks2',l_err_stage);
1532 END;
1533 END IF;
1534 END IF;
1535
1536 IF ((retcode = '0') AND (l_count_void = 0)) THEN
1537 retcode := '1';
1538 errbuf := 'There are no void transactions to be submitted for DIT';
1539 fv_utility.log_mesg(fnd_log.level_exception,l_module_name,errbuf);
1540 END IF;
1541 COMMIT;
1542 EXCEPTION
1543 WHEN OTHERS THEN
1544 errbuf := SQLERRM;
1545 retcode := '2';
1546 fv_utility.log_mesg(fnd_log.level_unexpected, l_module_name||'.final_exception',errbuf);
1547 END;
1548
1549 ---------------------------------------------------------------------------------------------------------------------------
1550 PROCEDURE get_segment_values
1551 (
1552 v_gs_ccid NUMBER
1553 ) IS
1554 l_module_name VARCHAR2(200);
1555 BEGIN
1556 l_module_name := g_module_name || 'get_segment_values ';
1557 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1558 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'in get_segment_values proc with v_gs_ccid'||v_gs_ccid);
1559 END IF;
1560
1561 FOR i IN 1..30 LOOP
1562 a_segments(i) := NULL;
1563 END LOOP;
1564
1565 BEGIN
1566 SELECT segment1,
1567 segment2,
1568 segment3,
1569 segment4,
1570 segment5,
1571 segment6,
1572 segment7,
1573 segment8,
1574 segment9,
1575 segment10,
1576 segment11,
1577 segment12,
1578 segment13,
1579 segment14,
1580 segment15,
1581 segment16,
1582 segment17,
1583 segment18,
1584 segment19,
1585 segment20,
1586 segment21,
1587 segment22,
1588 segment23,
1589 segment24,
1590 segment25,
1591 segment26,
1592 segment27,
1593 segment28,
1594 segment29,
1595 segment30
1596 INTO a_segments(1),
1597 a_segments(2),
1598 a_segments(3),
1599 a_segments(4),
1600 a_segments(5),
1601 a_segments(6),
1602 a_segments(7),
1603 a_segments(8),
1604 a_segments(9),
1605 a_segments(10),
1606 a_segments(11),
1607 a_segments(12),
1608 a_segments(13),
1609 a_segments(14),
1610 a_segments(15),
1611 a_segments(16),
1612 a_segments(17),
1613 a_segments(18),
1614 a_segments(19),
1615 a_segments(20),
1616 a_segments(21),
1617 a_segments(22),
1618 a_segments(23),
1619 a_segments(24),
1620 a_segments(25),
1621 a_segments(26),
1622 a_segments(27),
1623 a_segments(28),
1624 a_segments(29),
1625 a_segments(30)
1626 FROM gl_code_combinations
1627 WHERE code_combination_id = v_gs_ccid;
1628
1629 EXCEPTION
1630 WHEN NO_DATA_FOUND THEN
1631 NULL;
1632 END;
1633 EXCEPTION
1634 WHEN OTHERS THEN
1635 g_errmsg := SQLERRM;
1636 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_errmsg);
1637 RAISE;
1638 END get_segment_values;
1639
1640 ----------------------------------------------------------------------------------------------------------------------------
1641 PROCEDURE main ( errbuf OUT NOCOPY VARCHAR2,
1642 retcode OUT NOCOPY VARCHAR2,
1643 x_char_treas_conf_id IN VARCHAR2,
1644 v_button_name IN VARCHAR2)
1645
1646 IS
1647 l_module_name VARCHAR2(200);
1648 x_group_id NUMBER(15);
1649 x_interface_run_id NUMBER(15);
1650 x_err_code NUMBER;
1651 x_err_stage VARCHAR2(2000);
1652 v_boolean BOOLEAN;
1653 req_id NUMBER;
1654 call_status BOOLEAN;
1655 rphase VARCHAR2(30);
1656 rstatus VARCHAR2(30);
1657 dphase VARCHAR2(30);
1658 dstatus VARCHAR2(30);
1659 message VARCHAR2(240);
1660 v_dummy NUMBER;
1661 v_process_job VARCHAR2(1);
1662 v_period fv_treasury_confirmations.gl_period%TYPE;
1663 l_set_of_books_name VARCHAR2(200);
1664
1665 BEGIN
1666
1667 l_module_name := g_module_name || 'main ';
1668 -- initialise variables
1669 x_err_code := 0;
1670
1671 -- AKA, 8/10/99, moved from declare section to body, so don't need to pass value
1672 v_treasury_confirmation_id := TO_NUMBER(x_char_treas_conf_id);
1673
1674 -- Obtain the org id
1675
1676 --- v_org_id := TO_NUMBER(FND_PROFILE.VALUE('ORG_ID'));
1677 v_org_id := mo_global.get_current_org_id;
1678
1679 -- Obtain the set of books id
1680
1681 mo_utils.get_ledger_info(v_org_id,v_set_of_books_id,l_set_of_books_name);
1682 -- v_set_of_books_id := TO_NUMBER(FND_PROFILE.VALUE('GL_SET_OF_BKS_ID'));--;>--
1683
1684
1685
1686 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1687 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'set of books id = '||v_set_of_books_id);
1688 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'org id = '||v_org_id);
1689 END IF;
1690
1691 --Fix for bug 1715321: LG
1692 BEGIN
1693
1694 SELECT 1
1695 INTO v_dummy
1696 FROM gl_je_categories
1697 WHERE je_category_name = 'Treasury Confirmation';
1698
1699 EXCEPTION WHEN NO_DATA_FOUND THEN
1700 v_dummy := 0;
1701
1702 END;
1703
1704 IF (v_dummy = 0) THEN
1705 retcode := 2;
1706 errbuf := 'The Treasury Confirmation journal category has not been seeded';
1707 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1708 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'v_button_name = '||v_button_name);
1709 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'v_treasury_confirmation_id = '||TO_CHAR(v_treasury_confirmation_id));
1710 END IF;
1711 UPDATE fv_treasury_confirmations
1712 SET confirmation_status_flag = DECODE(v_button_name, 'TREASURY_CONFIRMATION.CONFIRM', 'N', 'TREASURY_CONFIRMATION.BACK_OUT', 'Y')
1713 WHERE treasury_confirmation_id = v_treasury_confirmation_id;
1714 RETURN;
1715 END IF;
1716
1717
1718 IF v_button_name = 'TREASURY_CONFIRMATION.CONFIRM' THEN
1719 -- if the user pressed the confirm button do the confirm
1720
1721 v_process_job := 'C'; -- this is so main will know a concurrent
1722 -- process needs to be submitted for the confirm process
1723
1724 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1725 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'v_process_job is = ' || v_process_job);
1726 END IF;
1727
1728 fv_disb_in_transit.do_confirm_process
1729 ( v_treasury_confirmation_id,
1730 x_group_id,
1731 x_err_code,
1732 x_err_stage,
1733 v_period);
1734
1735
1736 IF x_err_code <> 0 THEN
1737 errbuf := x_err_stage;
1738 IF (x_err_code = 1) THEN
1739 retcode := '1';
1740 ELSE
1741 retcode := '2';
1742 END IF;
1743 process_clean_up;
1744 RETURN;
1745 END IF;
1746
1747
1748 ELSIF v_button_name = 'TREASURY_CONFIRMATION.BACK_OUT' THEN
1749 -- do the back_out process.
1750
1751 v_process_job := 'B'; -- this is so main will know a concurrent
1752 -- process needs to be submitted for the backout process
1753
1754 -- AKA, 8/10/99, removed passing of v_treasury_confirmation_id
1755 fv_disb_in_transit.do_back_out_process
1756 ( x_group_id,
1757 x_err_code,
1758 x_err_stage);
1759
1760 IF x_err_code <> 0 THEN
1761 errbuf := x_err_stage;
1762 retcode := '2';
1763 ROLLBACK;
1764 COMMIT;
1765 RETURN;
1766
1767 END IF;
1768 END IF;
1769
1770 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1771 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'v_process_job is = ' || v_process_job);
1772 END IF;
1773
1774 IF v_process_job IN ('C','B') THEN
1775 -- if the v_process_job is 'C' or 'B' then there is a concurrent process
1776 -- to be submitted (c = confirm, b = back out)
1777
1778 -- Obtain the interface run id
1779 x_interface_run_id :=gl_interface_control_pkg.get_unique_run_id;
1780 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1781 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'interface_run_id = '||x_interface_run_id);
1782 END IF;
1783
1784 -- Insert a control record in Gl_INTERFACE record for the Gl
1785 -- Import to work
1786 INSERT INTO gl_interface_control(je_source_name,status,
1787 interface_run_id,group_id,set_of_books_id)
1788 VALUES ('Payables', 'S', x_interface_run_id, x_group_id,
1789 v_set_of_books_id);
1790
1791 -- Submit a Concurrent request to invoke journal import
1792 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1793 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'submitting a request');
1794 END IF;
1795 req_id := FND_REQUEST.SUBMIT_REQUEST( 'SQLGL',
1796 'GLLEZL',
1797 '',
1798 '',
1799 FALSE,
1800 TO_CHAR(x_interface_run_id),
1801 TO_CHAR(v_set_of_books_id),
1802 'N', '', '', 'N', 'N');
1803
1804 -- if concurrent request submission failed then abort process
1805 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1806 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'req_id = '||req_id);
1807 END IF;
1808
1809 IF req_id = 0 THEN
1810 errbuf := 'Can not submit journal import program';
1811 retcode := '2';
1812
1813 IF v_Process_job = 'C' THEN
1814 process_clean_up;
1815 -- if the process is a backout, want to rollback and set status to Y for the treasury confirmation id
1816 ELSIF v_process_job = 'B' THEN
1817 ROLLBACK;
1818 UPDATE fv_treasury_confirmations
1819 SET confirmation_status_flag = 'Y'
1820 WHERE treasury_confirmation_id = v_treasury_confirmation_id;
1821 COMMIT;
1822 END IF;
1823 RETURN;
1824 ELSE
1825 COMMIT;
1826 END IF;
1827
1828 -- Check status of completed concurrent program
1829 -- and if complete exit
1830 call_status := fnd_concurrent.wait_for_request(
1831 req_id, 20, 0, rphase, rstatus,
1832 dphase, dstatus, message);
1833
1834 IF call_status = FALSE THEN
1835 errbuf := 'Can not wait for the status of journal import';
1836 retcode := '2';
1837 END IF;
1838
1839 v_dummy := 0;
1840
1841
1842 -- Do rows exist in the GL_INTERFACE table ?
1843
1844 SELECT COUNT(*)
1845 INTO v_dummy
1846 FROM gl_interface
1847 WHERE group_id = x_group_id
1848 AND set_of_books_id = v_set_of_books_id
1849 AND user_je_source_name = 'Payables';
1850
1851 -- If any records exist in GL_INTERFACE then clean them up
1852 IF v_dummy > 0 THEN
1853 fv_disb_in_transit.cleanup_gl_interface(
1854 v_treasury_confirmation_id,
1855 v_process_job,
1856 x_group_id,
1857 x_err_code,
1858 x_err_stage);
1859 -- if no interface records..everything is ok
1860 ELSE
1861
1862 IF v_button_name = 'TREASURY_CONFIRMATION.BACK_OUT' THEN
1863
1864 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1865 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Cleaning up Back OUT NOCOPY data');
1866 END IF;
1867
1868 -- back_out the info in ap_checks
1869 UPDATE AP_CHECKS
1870 SET treasury_pay_number = NULL,
1871 treasury_pay_date = NULL,
1872 last_update_date = SYSDATE,
1873 last_updated_by = fnd_global.user_id,
1874 last_update_login = fnd_global.login_id
1875 WHERE payment_id IN
1876 (SELECT payment_id
1877 FROM fv_treasury_confirmations ftca,
1878 iby_payments_all ipa
1879 WHERE ftca.treasury_confirmation_id = v_treasury_confirmation_id
1880 AND ftca.payment_instruction_id = ipa.payment_instruction_id);
1881
1882 UPDATE fv_treasury_confirmations
1883 SET confirmation_status_flag = 'B',
1884 last_update_date = SYSDATE,
1885 last_updated_by = fnd_global.user_id,
1886 last_update_login = fnd_global.login_id
1887 WHERE treasury_confirmation_id = v_treasury_confirmation_id;
1888
1889 --delete the offset record related to this payment
1890 --batch.
1891
1892 DELETE FROM fv_tc_offsets
1893 WHERE check_id IN (SELECT check_id
1894 FROM ap_checks ac,
1895 fv_treasury_confirmations ftc,
1896 iby_payments_all ipa
1897 WHERE ftc.treasury_confirmation_id = v_treasury_confirmation_id
1898 AND ftc.payment_instruction_id = ipa.payment_instruction_id
1899 AND ipa.payment_id = ac.payment_id);
1900
1901
1902 ELSIF v_button_name = 'TREASURY_CONFIRMATION.CONFIRM' THEN
1903
1904 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1905 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Updating confirm status');
1906 END IF;
1907 UPDATE fv_treasury_confirmations
1908 SET confirmation_status_flag = 'Y',
1909 gl_period = v_period,
1910 last_update_date = SYSDATE,
1911 last_updated_by = fnd_global.user_id,
1912 last_update_login = fnd_global.login_id
1913 WHERE treasury_confirmation_id = v_treasury_confirmation_id;
1914 END IF;
1915
1916 END IF;
1917
1918 IF x_err_code <> 0 THEN
1919 errbuf := x_err_stage;
1920 IF (x_err_code = 1) THEN
1921 retcode := '1';
1922 ELSE
1923 retcode := '2';
1924 END IF;
1925 END IF;
1926 END IF;
1927
1928 COMMIT;
1929
1930 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1931 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'Process Complete');
1932 END IF;
1933 EXCEPTION
1934 WHEN OTHERS THEN
1935 g_errmsg := SQLERRM;
1936 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_errmsg);
1937 RAISE;
1938 END main;
1939 BEGIN
1940 g_module_name := 'fv.plsql.fv_disb_in_transit.';
1941 c_gl_appl_id := 101;
1942 c_application_short_name := 'SQLGL';
1943 c_key_flex_code := 'GL#';
1944 END fv_disb_in_transit;