[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_DTC_CANCELLATION_PKG
Source
1 PACKAGE BODY jai_ap_dtc_cancellation_pkg AS
2 /* $Header: jai_ap_dtc_can.plb 120.7.12020000.2 2013/03/25 03:10:28 cholei noship $ */
3
4 /* ----------------------------------------------------------------------------
5 FILENAME : jai_ap_dtc_cancellation_pkg.sql
6
7 Created By : Chong Lei
8
9 Created Date : 23-Dec-2011
10
11 Bug :
12
13 Purpose : Implementation of cancellation functionality for TDS.
14
15 Called from : Trigger
16
17 CHANGE HISTORY:
18 -------------------------------------------------------------------------------
19 S.No Date Author and Details
20 -------------------------------------------------------------------------------
21
22 /***********************************************************************************************/
23
24 PROCEDURE process_invoice_cancellation(errbuf out nocopy varchar2,
25 retcode out nocopy varchar2,
26 p_invoice_id in number) is
27
28 cursor c_jai_ap_tds_thhold_trxs(p_invoice_id number) is --rchandan for bug#4428980
29 select threshold_trx_id,
30 threshold_grp_id,
31 threshold_hdr_id, --Added by Sanjikum for Bug#5131075(4718907)
32 tax_category_id,
33 tds_section_code,
34 taxable_amount,
35 tax_amount,
36 invoice_to_tds_authority_id,
37 invoice_to_vendor_id,
38 tds_event, -- Added by Jia for FP Bug#7312295
39 calc_tax_amount -- Bug 5751783
40 from jai_ap_tds_thhold_trxs
41 where invoice_id = p_invoice_id
42 and (tds_event = 'INVOICE VALIDATE' or
43 tds_event like 'THRESHOLD TRANSITION%' -- Added by ChongLei for DTC Bug#13359892
44 --or tds_event = 'SURCHARGE_CALCULATE' --Bug 7312295 - Added condition 'or tds_event = 'SURCHARGE_CALCULATE' --Commented Surcharge_calcualte by Chong for bug#16414088
45 ) --surcharge_calcualte should be processed in thurcharge rollback procedure. 20130319
46 and tds_rollbacked is NULL -- Added by ChongLei for DTC Bug#13359892
47 order by threshold_trx_id; -- Added by Jia for FP Bug#7312295
48
49 cursor c_ja_in_tax_codes(p_tax_id number) is
50 select vendor_id, vendor_site_id, tax_rate
51 from JAI_CMN_TAXES_ALL
52 where tax_id = p_tax_id;
53 -- Added by ChongLei for DTC Bug#13768894, begin on 20120222
54 -------------------------------------------------------------------------------
55 CURSOR c_get_section_type(c_section_code VARCHAR2) IS
56 SELECT LTRIM(UPPER(lookup_type),'JAI_') section_type
57 FROM ja_lookups
58 WHERE lookup_code = c_section_code
59 AND lookup_type IN(
60 SELECT DISTINCT 'JAI_' || b.lookup_code
61 FROM ja_lookups b
62 WHERE b.lookup_type = 'JAI_AP_TDS_SECTION_TYPES'
63 );
64 lv_section_type varchar2(30);
65 -------------------------------------------------------------------------------
66 -- Added by ChongLei for DTC Bug#13768894 end on 20120222
67 -- Added by ChongLei for DTC Bug#13359892, Begin
68 -------------------------------------------------------------------------------
69 CURSOR c_get_trx_invoice_amount(p_inv_id NUMBER
70 ,p_threshold_grp_id NUMBER
71 ) IS
72 SELECT NVL(SUM(trx_invoice_amount),0) trx_invoice_amount
73 FROM jai_ap_tds_thgrp_audits
74 WHERE invoice_id = p_inv_id
75 AND threshold_grp_id = p_threshold_grp_id
76 AND tds_event = 'INVOICE VALIDATE'
77 AND trx_invoice_amount IS NOT NULL
78 ORDER BY threshold_grp_audit_id DESC;
79 -------------------------------------------------------------------------------
80 -- Added by ChongLei for DTC Bug#13359892, End
81
82 /* Bug 4559756. Added by Lakshmi Gopalsami
83 Added org_id for passing it to ap_utilities_pkg
84 */
85 cursor c_ap_invoices_all(p_invoice_id number) is
86 select invoice_id,
87 cancelled_date,
88 payment_status_flag,
89 invoice_amount,
90 set_of_books_id,
91 invoice_num,
92 org_id
93 from ap_invoices_all
94 where invoice_id = p_invoice_id;
95
96 cursor c_get_parent_inv_dtls(p_invoice_id number) is
97 select set_of_books_id, invoice_currency_code, exchange_rate
98 from ap_invoices_all
99 where invoice_id = p_invoice_id;
100
101 cursor c_jai_ap_tds_inv_taxes(p_invoice_id number,
102 cp_section_type jai_ap_tds_inv_taxes.section_type%type) is --rchandan for bug#4428980
103 select threshold_grp_id,
104 sum(amount) taxable_amount,
105 sum(tax_amount) tax_amount
106 from jai_ap_tds_inv_taxes jtdsi
107 where invoice_id = p_invoice_id
108 and section_type = cp_section_type --cp_section_type--rchandan for bug#4428980
109 and threshold_grp_id is not null
110 and threshold_trx_id is null
111 -- Added by ChongLei for DTC Bug#13359892, Begin
112 -------------------------------------------------------------------------------
113 and not exists
114 (select jattt.invoice_to_tds_authority_id
115 from jai_ap_tds_thhold_trxs jattt
116 ,ap_invoices_all aia
117 where jattt.threshold_trx_id in(
118 select max(threshold_trx_id)
119 from jai_ap_tds_thhold_trxs
120 where invoice_id = p_invoice_id
121 and tds_section_code = jtdsi.actual_section_code
122 and tds_event not like 'THRESHOLD ROLLBACK%'
123 and tds_event <> 'INVOICE CANCEL' -- Added by ChongLei for DTC Bug#13859847
124 group by tds_section_code)
125 and invoice_to_tds_authority_id = aia.invoice_id
126 and aia.cancelled_date is not NULL
127 )
128 -------------------------------------------------------------------------------
129 -- Added by ChongLei for DTC Bug#13359892, End
130 group by threshold_grp_id;
131
132 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
133 select currency_code
134 from gl_sets_of_books
135 where set_of_books_id = cp_set_of_books_id;
136 /*start-ETDS FVU3.1 11896260*/
137 cursor c_threshold_breach(p_invoice_id number) is
138 select jattt.invoice_id,
139 jattt.tds_event,
140 jattt.invoice_to_tds_authority_id
141 from jai_ap_tds_inv_taxes jatit, jai_ap_tds_thhold_trxs jattt
142 where jatit.invoice_id = p_invoice_id
143 and jatit.default_cum_threshold_stage = 'BEFORE THRESHOLD'
144 and jatit.threshold_grp_id = jattt.threshold_grp_id
145 and jattt.tds_event like 'THRESHOLD%'
146 and not exists (select '1'
147 from jai_ap_tds_thhold_trxs
148 where invoice_id = jatit.invoice_id); /*to ensure that no TDS is deducted*/
149 lv_count number := 0;
150 lv_payment varchar2(1) := 'N';
151 /*end-ETDS FVU3.1 11896260*/
152 /* Bug#5131075(5193852). Added by Lakshmi Gopalsami
153 | Changed the source of ld_accounting_date to refer to
154 | ap_invoice_distributions_all.accounting_date instead of
155 | gl_date on headers.
156 */
157 CURSOR get_dist_gl_date(cp_invoice_id IN ap_invoices_all.invoice_id%TYPE) IS
158 SELECT accounting_date
159 FROM ap_invoice_distributions_all
160 WHERE invoice_id = cp_invoice_id
161 AND distribution_line_number = 1;
162 -- Only one distribution will be created for TDS invoices and so
163 -- hard coded the distribution line number to 1.
164
165 /*Bug 8830302 - Get Accounting Date from AP_INVOICE_LINES_ALL.
166 This is fail safe if Distributions does not have accounting date*/
167 CURSOR c_get_lines_acct_date(cp_invoice_id IN ap_invoices_all.invoice_id%TYPE) IS
168 SELECT accounting_date
169 FROM ap_invoice_lines_all
170 WHERE invoice_id = cp_invoice_id
171 AND line_number = 1;
172
173 -- Added by Jia for FP Bug#7312295, Begin
174 -------------------------------------------------------------------------------
175 CURSOR c_get_threshold_grp_dtl(p_threshold_grp_id NUMBER) IS
176 SELECT *
177 FROM jai_ap_tds_thhold_grps
178 WHERE threshold_grp_id = p_threshold_grp_id;
179
180 CURSOR c_jai_ap_tds_thhold_slabs(p_threshold_hdr_id NUMBER,
181 p_threshold_type VARCHAR2,
182 p_amount NUMBER) IS
183 SELECT threshold_slab_id,
184 threshold_type_id,
185 from_amount,
186 to_amount,
187 tax_rate
188 FROM jai_ap_tds_thhold_slabs
189 WHERE threshold_hdr_id = p_threshold_hdr_id
190 AND threshold_type_id in
191 (SELECT threshold_type_id
192 FROM jai_ap_tds_thhold_types
193 WHERE threshold_hdr_id = p_threshold_hdr_id
194 AND threshold_type = p_threshold_type
195 AND trunc(sysdate) between from_date and
196 nvl(to_date, sysdate + 1))
197 AND from_amount <= p_amount
198 AND NVL(to_amount, p_amount) >= p_amount
199 ORDER BY from_amount asc;
200
201 r_get_threshold_grp_dtl c_get_threshold_grp_dtl%ROWTYPE;
202 ln_effective_invoice_amt NUMBER;
203 ln_effective_inv_amt_after NUMBER;
204 r_jai_ap_tds_thhold_slabs c_jai_ap_tds_thhold_slabs%ROWTYPE;
205 ln_threshold_slab_id_after jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
206 ln_threshold_slab_id_before jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
207 ln_taxable_amt number := 0;
208 -------------------------------------------------------------------------------
209 -- Added by Jia for FP Bug#7312295, End
210
211 r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
212 r_ap_invoices_all c_ap_invoices_all%rowtype;
213 r_get_parent_inv_dtls c_get_parent_inv_dtls%rowtype;
214 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
215
216 lv_code_path VARCHAR2(1996);
217 lv_process_flag varchar2(1);
218 lv_process_message varchar2(250);
219 lv_tds_invoice_flag varchar2(1);
220 lv_tds_invoice_message varchar2(250);
221 lv_tds_credit_memo_flag varchar2(1);
222 lv_tds_credit_memo_message varchar2(250);
223
224 lb_return_value boolean;
225
226 lv_out_message_name varchar2(240);
227 ln_out_invoice_amount number;
228 ln_out_base_amount number;
229 ln_out_tax_amount number;
230 ln_out_temp_cancelled_amount number;
231 ln_out_cancelled_by number;
232 ln_out_cancelled_amount number;
233 ld_out_cancelled_date date;
234 ld_out_last_update_date date;
235 ln_out_original_prepay_amount number;
236 ln_out_pay_curr_inv_amount number;
237
238 ld_accounting_date date; --File.Sql.35 Cbabu := sysdate;
239 ld_out_accounting_date date; --Added by Chong for ZX investigation 20120816
240 lv_open_period ap_invoice_distributions_all.period_name%type;
241
242 lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
243 lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
244 ln_threshold_trx_id NUMBER;
245
246 ln_taxable_amount NUMBER;
247 ln_exchange_rate ap_invoices_all.exchange_rate%type;
248 lv_codepath VARCHAR2(1996);
249 ln_start_threshold_trx_id NUMBER;
250 ln_threshold_grp_id NUMBER;
251 ln_threshold_grp_audit_id number;
252 ln_trx_invoice_amount NUMBER;
253 lv_new_transaction varchar2(1);
254 lv_token varchar2(4000);
255 --Added by Sanjikum the below 5 variables for bug#5131075(4718907)
256 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
257 lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
258 ln_after_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
259 lv_after_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
260 ln_temp_threshold_hdr_id jai_ap_tds_thhold_hdrs.threshold_hdr_id%TYPE;
261 ld_ret_accounting_date DATE; -- bug#5131075(5193852). Added by Lakshmi Gopalsami
262
263 lv_proc_name VARCHAR2 (100) := 'process_invoice_cancellation';
264 ln_proc_level NUMBER := FND_LOG.level_procedure;
265 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
266
267 BEGIN
268
269 lv_codepath := jai_general_pkg.plot_codepath(1,
270 lv_codepath,
271 'jai_ap_dtc_cancellation_pkg.process_invoice_cancellation',
272 'START'); /* 1 */
273 --Fnd_File.put_line(Fnd_File.LOG,
274 -- '**** Start of procedure jai_ap_dtc_cancellation_pkg.process_invoice_cancellation ****');
275 IF ( ln_proc_level >= ln_dbg_level)
276 THEN
277 FND_LOG.STRING ( ln_proc_level
278 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
279 , '**** Start of procedure jai_ap_dtc_cancellation_pkg.process_invoice_cancellation ****'
280 );
281 END IF; --( ln_proc_level >= ln_dbg_level)
282
283 /* Check if Invoice was created after the tds clean up patch */
284 jai_ap_tds_tax_defaultation.check_old_transaction(p_invoice_id => p_invoice_id,
285 p_new_transaction => lv_new_transaction);
286
287 if nvl(lv_new_transaction, 'N') = 'N' then
288 /* Invoice was created before application of TDS clean up, need to call the old procedure */
289 lv_codepath := jai_general_pkg.plot_codepath(1.0, lv_codepath); /* 1.0 */
290 --Fnd_File.put_line(Fnd_File.LOG,
291 -- '**** Transaction before application of TDS clean up Calling procedure ****');
292 --Fnd_File.put_line(Fnd_File.LOG,
293 -- ' Invoking OLD procedure jai_ap_tds_old_pkg.cancel_invoice');
294 IF ( ln_proc_level >= ln_dbg_level)
295 THEN
296 FND_LOG.STRING ( ln_proc_level
297 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
298 , '**** Transaction before application of TDS clean up Calling procedure ****'
299 );
300 END IF; --( ln_proc_level >= ln_dbg_level)
301 IF ( ln_proc_level >= ln_dbg_level)
302 THEN
303 FND_LOG.STRING ( ln_proc_level
304 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
305 , 'Invoking OLD procedure jai_ap_tds_old_pkg.cancel_invoice'
306 );
307 END IF; --( ln_proc_level >= ln_dbg_level)
308
309 jai_ap_tds_old_pkg.cancel_invoice(errbuf => errbuf,
310 retcode => retcode,
311 p_invoice_id => p_invoice_id);
312
313 goto exit_from_procedure;
314
315 end if;
316
317 /* bug 4559756. Added by Lakshmi Gopalsami
318 Fetch the org_id
319 */
320 open c_ap_invoices_all(p_invoice_id);
321 fetch c_ap_invoices_all
322 into r_ap_invoices_all;
323 close c_ap_invoices_all;
324
325 --Removed the code from here by Lakshmi Gopalsami for Bug#5131075(5193852)
326
327 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath); /* 2 */
328
329 /* Get all the TDS invoices that were generated at the time of the INVOICE VALIDATE */
330 FOR cur_rec in c_jai_ap_tds_thhold_trxs(p_invoice_id) LOOP
331 --Fnd_File.put_line(Fnd_File.LOG, ' Inside Loop');
332 IF ( ln_proc_level >= ln_dbg_level)
333 THEN
334 FND_LOG.STRING ( ln_proc_level
335 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
336 , 'Inside Loop'
337 );
338 END IF; --( ln_proc_level >= ln_dbg_level)
339
340 --variables initialize
341 -- ln_threshold_slab_id_before := NULL;
342 -- ln_threshold_slab_id_after := NULL;
343
344 --get slab before cancel
345 jai_ap_dtc_generation_pkg.get_tds_threshold_slab(p_prepay_distribution_id => NULL,
346 p_invoice_id => p_invoice_id,
347 p_threshold_grp_id => cur_rec.threshold_grp_id,
348 p_threshold_hdr_id => cur_rec.threshold_hdr_id,
349 p_threshold_slab_id => ln_threshold_slab_id,
350 p_threshold_type => lv_threshold_type,
351 p_process_flag => lv_process_flag,
352 p_process_message => lv_process_message,
353 p_codepath => lv_codepath);
354 IF lv_process_flag = 'E' THEN
355 GOTO end_of_main_loop;
356 END IF;
357
358 IF cur_rec.tds_event = 'INVOICE VALIDATE' OR cur_rec.tds_event LIKE 'THRESHOLD TRANSITION%' THEN
359 ln_threshold_slab_id_before := ln_threshold_slab_id;
360 ln_taxable_amt := cur_rec.taxable_amount;
361
362 OPEN c_get_threshold_grp_dtl(cur_rec.threshold_grp_id);
363 FETCH c_get_threshold_grp_dtl
364 INTO r_get_threshold_grp_dtl;
365 CLOSE c_get_threshold_grp_dtl;
366
367 ln_effective_invoice_amt := r_get_threshold_grp_dtl.total_invoice_amount -
368 r_get_threshold_grp_dtl.total_invoice_cancel_amount -
369 r_get_threshold_grp_dtl.total_invoice_apply_amount +
370 r_get_threshold_grp_dtl.total_invoice_unapply_amount;
371 --amount after cancel
372 ln_effective_inv_amt_after := ln_effective_invoice_amt -
373 cur_rec.taxable_amount;
374 --get slab after cancel
375 jai_ap_dtc_generation_pkg.get_tds_threshold_slab(p_prepay_distribution_id => NULL,
376 p_invoice_id => p_invoice_id,
377 p_threshold_grp_id => cur_rec.threshold_grp_id,
378 p_threshold_hdr_id => cur_rec.threshold_hdr_id,
379 p_effective_invoice_amt => ln_effective_inv_amt_after,
380 p_threshold_slab_id => ln_threshold_slab_id_after,
381 p_threshold_type => lv_threshold_type,
382 p_process_flag => lv_process_flag,
383 p_process_message => lv_process_message,
384 p_codepath => lv_codepath);
385 IF lv_process_flag = 'E' THEN
386 GOTO end_of_main_loop;
387 END IF;
388 END IF;
389
390 IF (cur_rec.tds_event = 'INVOICE VALIDATE') OR
391 (cur_rec.tds_event LIKE 'THRESHOLD TRANSITION%') OR
392 (cur_rec.tds_event = 'SURCHARGE_CALCULATE' AND
393 ln_threshold_slab_id_before <> ln_threshold_slab_id_after) THEN
394
395 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath); /* 3 */
396
397 lv_tds_invoice_flag := null;
398 lv_tds_invoice_message := null;
399 lv_tds_credit_memo_flag := null;
400 lv_tds_credit_memo_message := null;
401
402 /* Get the details of the Invoice to TDS authority */
403 r_ap_invoices_all := null;
404 open c_ap_invoices_all(cur_rec.invoice_to_tds_authority_id);
405 fetch c_ap_invoices_all
406 into r_ap_invoices_all;
407 close c_ap_invoices_all;
408
409 /* Bug#5131075(5193852). Added by Lakshmi Gopalsami
410 * Derivced the accounting_date of the original distribution
411 * as this value is also getting passed for Threshold adjustments if the
412 * TDS invoice is already paid/cancelled.
413 */
414 --Fnd_File.put_line(Fnd_File.LOG,
415 -- ' cur_rec.invoice_to_tds_authority_id ' ||
416 -- cur_rec.invoice_to_tds_authority_id);
417 IF ( ln_proc_level >= ln_dbg_level)
418 THEN
419 FND_LOG.STRING ( ln_proc_level
420 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
421 , ' cur_rec.invoice_to_tds_authority_id ' || cur_rec.invoice_to_tds_authority_id
422 );
423 END IF; --( ln_proc_level >= ln_dbg_level)
424 OPEN get_dist_gl_date(cur_rec.invoice_to_tds_authority_id);
425 FETCH get_dist_gl_date
426 INTO ld_accounting_date;
427 CLOSE get_dist_gl_date;
428
429 /*Bug 8830302 - Fetch Accouting Date from AP_INVOICE_LINES_ALL if Distributions are not saved yet*/
430 if (ld_accounting_date is NULL) then
431 OPEN c_get_lines_acct_date(cur_rec.invoice_to_tds_authority_id);
432 FETCH c_get_lines_acct_date
433 INTO ld_accounting_date;
434 CLOSE c_get_lines_acct_date;
435 end if;
436
437 /* Check if the TDS invoice is paid, no processing is required if it is already paid. */
438 if r_ap_invoices_all.payment_status_flag <> 'N' then
439 lv_codepath := jai_general_pkg.plot_codepath(4,
440 lv_codepath); /* 4 */
441 lv_tds_invoice_flag := 'X';
442 lv_tds_invoice_message := 'Invoice to TDS Authority is already paid.';
443 lv_tds_credit_memo_flag := 'X';
444 lv_tds_credit_memo_message := 'No processing as Invoice to TDS Authority is already paid';
445 goto Continue_with_next_record;
446 end if;
447
448 /* Check if the invoice to TDS authority is already canccelled,
449 if yes, no need to do the cancel processing of the invoice */
450 lv_codepath := jai_general_pkg.plot_codepath(5, lv_codepath); /* 5 */
451 if r_ap_invoices_all.cancelled_date is not null then
452 lv_codepath := jai_general_pkg.plot_codepath(6,
453 lv_codepath); /* 6 */
454 lv_tds_invoice_flag := 'X';
455 lv_tds_invoice_message := ' Invoice to TDS Authority is already Cancelled.';
456 goto credit_memo_processing;
457 end if;
458
459 /* Control comes here only when the Invoice to TDS Authority is not paid or not cancelled, need to cancel it */
460
461 lb_return_value := null;
462 lv_out_message_name := null;
463 ln_out_invoice_amount := null;
464 ln_out_base_amount := null;
465 ln_out_tax_amount := null;
466 ln_out_temp_cancelled_amount := null;
467 ln_out_cancelled_by := null;
468 ln_out_cancelled_amount := null;
469 ld_out_cancelled_date := null;
470 ld_out_last_update_date := null;
471 ln_out_original_prepay_amount := null;
472 ln_out_pay_curr_inv_amount := null;
473 lv_token := null;
474
475 lv_codepath := jai_general_pkg.plot_codepath(7, lv_codepath); /* 7 */
476
477 /* Start for Bug#5131075(5193852). Added by Lakshmi Gopalsami.
478 | Fetch the accounting date of TDS invoice distribution
479 | so that the same will be passed for reversal line which will get
480 | created for cancellation.
481 */
482 --Check if the given date is in current open period
483 lv_open_period := ap_utilities_pkg.get_current_gl_date(P_Date => ld_accounting_date,
484 P_Org_Id => r_ap_invoices_all.org_id);
485 if lv_open_period is null then
486
487 lv_codepath := jai_general_pkg.plot_codepath(1.1, lv_codepath); /* 1.1 */
488
489 ap_utilities_pkg.get_open_gl_date(p_date => ld_accounting_date, /* In date */
490 p_period_name => lv_open_period, /* out Period */
491 p_gl_date => ld_out_accounting_date, /* out date */--Updated by Chong for ZX investigation 20120816
492 P_Org_Id => r_ap_invoices_all.org_id);
493
494 /*Commented by Chong for ZX investigation 20120816
495 if lv_open_period is null then
496 lv_codepath := jai_general_pkg.plot_codepath(1.2,
497 lv_codepath); \* 1.2 *\
498 lv_process_flag := 'E';
499 lv_process_message := 'No open accounting Period after : ' ||
500 ld_accounting_date;
501 goto exit_from_procedure;
502 end if;
503 ld_accounting_date := ld_ret_accounting_date;
504 Commented by Chong for ZX investigation 20120816*/
505
506 end if; /* lv_open_period is null */
507 ld_ret_accounting_date := NULL;
508
509 -- End for bug#5131075(5193852). Added by Lakshmi Gopalsami
510
511 /* Bug 4523064. Added by Lakshmi Gopalsami
512 Commented the following parameters
513 P_set_of_books_id
514 P_period_name
515 P_set_of_books_id
516 P_Check_id
517 and added the following OUT Parameter
518 P_Token
519 */
520
521 /*Added set_policy_context call to implement MOAC - Bug 8475540*/
522
523 --fnd_file.put_line(FND_FILE.LOG,
524 -- ' Org id ' || r_ap_invoices_all.org_id);
525 IF ( ln_proc_level >= ln_dbg_level)
526 THEN
527 FND_LOG.STRING ( ln_proc_level
528 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
529 , ' Org id ' || r_ap_invoices_all.org_id
530 );
531 END IF; --( ln_proc_level >= ln_dbg_level)
532 mo_global.set_policy_context('S', r_ap_invoices_all.org_id);
533
534 lb_return_value := ap_cancel_pkg.ap_cancel_single_invoice(P_invoice_id => cur_rec.invoice_to_tds_authority_id,
535 P_last_updated_by => fnd_global.user_id,
536 P_last_update_login => fnd_global.login_id,
537 --P_set_of_books_id => r_ap_invoices_all.set_of_books_id ,
538 P_accounting_date => ld_accounting_date,
539 --P_period_name => lv_open_period ,
540 P_message_name => lv_out_message_name,
541 P_invoice_amount => ln_out_invoice_amount,
542 P_base_amount => ln_out_base_amount,
543 --P_tax_amount => ln_out_tax_amount ,
544 P_temp_cancelled_amount => ln_out_temp_cancelled_amount,
545 P_cancelled_by => ln_out_cancelled_by,
546 P_cancelled_amount => ln_out_cancelled_amount,
547 P_cancelled_date => ld_out_cancelled_date,
548 P_last_update_date => ld_out_last_update_date,
549 P_original_prepayment_amount => ln_out_original_prepay_amount,
550 --P_check_id => null ,
551 P_pay_curr_invoice_amount => ln_out_pay_curr_inv_amount,
552 P_Token => lv_token,
553 P_calling_sequence => 'India Localization - cancel TDS invoice');
554
555 lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath); /* 8 */
556
557 /* Bug4523064. Check whether any value is returned in lv_token.
558 IF it is not null display the error message. */
559
560 IF nvl(lv_token, 'A') <> 'A' Then
561 APP_EXCEPTION.RAISE_EXCEPTION(EXCEPTION_TYPE => 'APP',
562 EXCEPTION_CODE => NULL,
563 EXCEPTION_TEXT => lv_token);
564
565 End if;
566
567 /* Bug4523064. Added by Lakshmi Gopalsami
568 Commented the tax_amount update */
569
570 update ap_invoices_all
571 set invoice_amount = ln_out_invoice_amount,
572 base_amount = ln_out_base_amount,
573 --tax_amount = ln_out_tax_amount ,
574 temp_cancelled_amount = ln_out_temp_cancelled_amount,
575 cancelled_by = ln_out_cancelled_by,
576 cancelled_amount = ln_out_cancelled_amount,
577 cancelled_date = ld_out_cancelled_date,
578 last_update_date = ld_out_last_update_date,
579 original_prepayment_amount = ln_out_original_prepay_amount,
580 pay_curr_invoice_amount = ln_out_pay_curr_inv_amount
581 where invoice_id = cur_rec.invoice_to_tds_authority_id;
582
583 /*What if ap_cancel_pkg.ap_cancel_single_invoice is not there ?? */
584 lv_tds_invoice_flag := 'Y';
585 lv_tds_invoice_message := 'Invoice to TDS Authority is Cancelled ';
586
587 lv_codepath := jai_general_pkg.plot_codepath(9, lv_codepath); /* 9 */
588
589 <<credit_memo_processing>>
590 /* Get the details of the Credit memo to the supplier for TDS */
591 r_ap_invoices_all := null;
592 open c_ap_invoices_all(cur_rec.invoice_to_vendor_id);
593 fetch c_ap_invoices_all
594 into r_ap_invoices_all;
595 close c_ap_invoices_all;
596
597 /* Bug#5131075(5193852). Added by Lakshmi Gopalsami
598 * Derivced the accounting_date of the original distribution
599 * as this value is also getting passed for Threshold adjustments if the
600 * TDS CM invoice is already paid/cancelled.
601 */
602
603 OPEN get_dist_gl_date(cur_rec.invoice_to_vendor_id);
604 FETCH get_dist_gl_date
605 INTO ld_accounting_date;
606 CLOSE get_dist_gl_date;
607
608 /*Bug 8830302 - Fetch Accouting Date from AP_INVOICE_LINES_ALL if Distributions are not saved yet*/
609 if (ld_accounting_date is NULL) then
610 OPEN c_get_lines_acct_date(cur_rec.invoice_to_vendor_id);
611 FETCH c_get_lines_acct_date
612 INTO ld_accounting_date;
613 CLOSE c_get_lines_acct_date;
614 end if;
615
616 /* Check if the Credit memo to the supplier for TDS already canccelled,
617 if yes, no need to cancel it again here */
618
619 if r_ap_invoices_all.cancelled_date is not null then
620 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath); /* 10 */
621 lv_tds_credit_memo_flag := 'X';
622 lv_tds_credit_memo_message := 'Credit memo to the supplier for TDS is already Cancelled.';
623 goto Continue_with_next_record;
624 end if;
625
626 /* Check if the Credit memo to the supplier for TDS is paid,
627 if yes a separate invoice needs to be generated, or else the same credit memo can be cancelled */
628
629 if r_ap_invoices_all.payment_status_flag = 'N' then
630 lv_codepath := jai_general_pkg.plot_codepath(11, lv_codepath); /* 11 */
631 /* Credit memo not paid, can cancel the same */
632 lv_tds_credit_memo_flag := 'Y';
633 lv_tds_credit_memo_message := 'Cancelling Credit memo to the supplier for TDS as it is not paid ';
634
635 lb_return_value := null;
636 lv_out_message_name := null;
637 ln_out_invoice_amount := null;
638 ln_out_base_amount := null;
639 ln_out_tax_amount := null;
640 ln_out_temp_cancelled_amount := null;
641 ln_out_cancelled_by := null;
642 ln_out_cancelled_amount := null;
643 ld_out_cancelled_date := null;
644 ld_out_last_update_date := null;
645 ln_out_original_prepay_amount := null;
646 ln_out_pay_curr_inv_amount := null;
647 lv_token := NULL;
648
649 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath); /* 12 */
650
651 /* Start for Bug#5131075(5193852). Added by Lakshmi Gopalsami.
652 | Fetch the accounting date of TDS invoice distribution
653 | so that the same will be passed for reversal line which will get
654 | created for cancellation.
655 */
656 --Check if the given date is in current open period
657 lv_open_period := ap_utilities_pkg.get_current_gl_date(P_Date => ld_accounting_date,
658 P_Org_Id => r_ap_invoices_all.org_id);
659
660 if lv_open_period is null then
661
662 lv_codepath := jai_general_pkg.plot_codepath(1.1, lv_codepath); /* 1.1 */
663
664 ap_utilities_pkg.get_open_gl_date(p_date => ld_accounting_date, /* In date */
665 p_period_name => lv_open_period, /* out Period */
666 p_gl_date => ld_out_accounting_date, /* out date */ ----Updated by Chong for ZX investigation 20120816
667 P_Org_Id => r_ap_invoices_all.org_id);
668
669 /*Commented by Chong for ZX investigation 20120816
670 if lv_open_period is null then
671 lv_codepath := jai_general_pkg.plot_codepath(1.2,
672 lv_codepath); \* 1.2 *\
673 lv_process_flag := 'E';
674 lv_process_message := 'No open accounting Period after : ' ||
675 ld_accounting_date;
676 goto exit_from_procedure;
677 end if;
678 ld_accounting_date := ld_ret_accounting_date;
679 Commented by Chong for ZX investigation 20120816*/
680
681 end if; /* lv_open_period is null */
682 ld_ret_accounting_date := NULL;
683
684 -- End for bug#5131075(5193852). Added by Lakshmi Gopalsami
685
686 /* Bug 4523064. Added by Lakshmi Gopalsami
687 Commented the following parameters
688 P_set_of_books_id
689 P_period_name
690 P_set_of_books_id
691 P_Check_id
692 and added the following OUT Parameter
693 P_Token
694 */
695
696 /*Added set_policy_context call to implement MOAC - Bug 8475540*/
697
698 --fnd_file.put_line(FND_FILE.LOG,
699 -- ' Org id ' || r_ap_invoices_all.org_id);
700 IF ( ln_proc_level >= ln_dbg_level)
701 THEN
702 FND_LOG.STRING ( ln_proc_level
703 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
704 , ' Org id ' || r_ap_invoices_all.org_id
705 );
706 END IF; --( ln_proc_level >= ln_dbg_level)
707 mo_global.set_policy_context('S', r_ap_invoices_all.org_id);
708
709 lb_return_value := ap_cancel_pkg.ap_cancel_single_invoice(P_invoice_id => cur_rec.invoice_to_vendor_id,
710 P_last_updated_by => fnd_global.user_id,
711 P_last_update_login => fnd_global.login_id,
712 --P_set_of_books_id => r_ap_invoices_all.set_of_books_id ,
713 P_accounting_date => ld_accounting_date,
714 --P_period_name => lv_open_period ,
715 P_message_name => lv_out_message_name,
716 P_invoice_amount => ln_out_invoice_amount,
717 P_base_amount => ln_out_base_amount,
718 --P_tax_amount => ln_out_tax_amount ,
719 P_temp_cancelled_amount => ln_out_temp_cancelled_amount,
720 P_cancelled_by => ln_out_cancelled_by,
721 P_cancelled_amount => ln_out_cancelled_amount,
722 P_cancelled_date => ld_out_cancelled_date,
723 P_last_update_date => ld_out_last_update_date,
724 P_original_prepayment_amount => ln_out_original_prepay_amount,
725 --P_check_id => null ,
726 P_pay_curr_invoice_amount => ln_out_pay_curr_inv_amount,
727 P_token => lv_token,
728 P_calling_sequence => 'India Localization - cancel TDS invoice');
729
730 lv_codepath := jai_general_pkg.plot_codepath(13, lv_codepath); /* 13 */
731
732 /* Bug4523064. Check whether any value is returned in lv_token.
733 IF it is not null display the error message. */
734
735 IF nvl(lv_token, 'A') <> 'A' Then
736 APP_EXCEPTION.RAISE_EXCEPTION(EXCEPTION_TYPE => 'APP',
737 EXCEPTION_CODE => NULL,
738 EXCEPTION_TEXT => lv_token);
739 End if;
740
741 /*Bug4523064. Added by Lakshmi Gopalsami
742 Commented the tax_amount update */
743 update ap_invoices_all
744 set invoice_amount = ln_out_invoice_amount,
745 base_amount = ln_out_base_amount,
746 -- tax_amount = ln_out_tax_amount ,
747 temp_cancelled_amount = ln_out_temp_cancelled_amount,
748 cancelled_by = ln_out_cancelled_by,
749 cancelled_amount = ln_out_cancelled_amount,
750 cancelled_date = ld_out_cancelled_date,
751 last_update_date = ld_out_last_update_date,
752 original_prepayment_amount = ln_out_original_prepay_amount,
753 pay_curr_invoice_amount = ln_out_pay_curr_inv_amount
754 where invoice_id = cur_rec.invoice_to_vendor_id;
755
756 else
757 /* Credit memo has already been paid, have to generate a new invoice to nagate the effect */
758 lv_codepath := jai_general_pkg.plot_codepath(14,
759 lv_codepath); /* 14 */
760 ln_threshold_trx_id := 0;
761 lv_invoice_to_tds_num := null;
762 lv_invoice_to_vendor_num := null;
763
764 -- Added by ChongLei for DTC Bug#13768894, begin on 20120222
765 OPEN c_get_section_type(cur_rec.tds_section_code);
766 FETCH c_get_section_type INTO lv_section_type;
767 CLOSE c_get_section_type;
768 -- Added by ChongLei for DTC Bug#13768894 end on 20120222
769
770 jai_ap_dtc_generation_pkg.generate_dtc_invoices(pn_invoice_id => p_invoice_id,
771 pv_invoice_num_to_vendor_can => r_ap_invoices_all.invoice_num,
772 pn_taxable_amount => cur_rec.taxable_amount,
773 pn_tax_amount => cur_rec.tax_amount,
774 pn_tax_category_id => cur_rec.tax_category_id,
775 pv_section_type => lv_section_type, -- 'TDS_SECTION_TYPE', Updated by Chong.Lei for Bug#13768894 on 20120222
776 pv_section_code => cur_rec.tds_section_code,
777 pd_accounting_date => ld_accounting_date,
778 pv_tds_event => 'INVOICE CANCEL',
779 pn_threshold_grp_id => cur_rec.threshold_grp_id,
780 pv_tds_invoice_num => lv_invoice_to_tds_num,
781 pv_cm_invoice_num => lv_invoice_to_vendor_num,
782 pn_threshold_trx_id => ln_threshold_trx_id,
783 p_process_flag => lv_tds_credit_memo_flag,
784 p_process_message => lv_tds_credit_memo_message,
785 pd_creation_Date => sysdate,
786 pn_calc_tax_amount => 0);
787
788 if lv_tds_credit_memo_flag = 'E' then
789 lv_codepath := jai_general_pkg.plot_codepath(15, lv_codepath); /* 15 */
790 goto Continue_with_next_record;
791 end if;
792
793 lv_tds_credit_memo_flag := 'Y';
794 lv_tds_credit_memo_message := 'Generated Standard invoice to suppliet : ' ||
795 lv_invoice_to_vendor_num;
796 lv_codepath := jai_general_pkg.plot_codepath(16,
797 lv_codepath); /* 16 */
798
799 if ln_start_threshold_trx_id is null then
800 ln_start_threshold_trx_id := ln_threshold_trx_id;
801 end if;
802
803 lv_codepath := jai_general_pkg.plot_codepath(17, lv_codepath); /* 17 */
804 /* Update the total tax amount for which Cancel invoice was raised */
805
806 end if; /* Credit memo to the supplier paid / not paid */
807
808 /* Control comes here when either the credit memo for the tds authority is cancelled or a
809 compensating standard invoice has been made */
810
811 ln_threshold_grp_id := cur_rec.threshold_grp_id;
812 jai_ap_dtc_generation_pkg.maintain_thhold_grps(p_threshold_grp_id => ln_threshold_grp_id,
813 p_trx_tax_paid => (-1 *
814 cur_rec.tax_amount),
815 p_tds_event => 'INVOICE CANCEL',
816 p_invoice_id => p_invoice_id,
817 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
818 p_process_flag => lv_process_flag,
819 P_process_message => lv_process_message,
820 p_codepath => lv_codepath);
821
822 <<Continue_with_next_record>>
823
824 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath); /* 18 */
825 /* Update threshold for the cancel invoice amount */
826
827 ln_threshold_grp_id := cur_rec.threshold_grp_id; /*added by rchandan for bug#5131075(4947469)*/
828
829 jai_ap_dtc_generation_pkg.maintain_thhold_grps(p_threshold_grp_id => ln_threshold_grp_id,
830 --p_trx_invoice_cancel_amount => cur_rec.taxable_amount, -- Comments by Jia for FP Bug#7312295
831 p_trx_invoice_cancel_amount => ln_taxable_amt, -- Modified by Jia for FP Bug#7312295
832 p_tds_event => 'INVOICE CANCEL',
833 p_invoice_id => p_invoice_id,
834 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
835 p_process_flag => lv_process_flag,
836 P_process_message => lv_process_message,
837 p_codepath => lv_codepath);
838
839 /* insert into JAI_AP_TDS_INV_CANCELS */
840 lv_codepath := jai_general_pkg.plot_codepath(19, lv_codepath); /* 19 */
841
842 --fnd_file.put_line(FND_FILE.LOG, 'Insert cancel: p_invoice_id' || p_invoice_id);
843 IF ( ln_proc_level >= ln_dbg_level)
844 THEN
845 FND_LOG.STRING ( ln_proc_level
846 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
847 , 'Insert cancel: p_invoice_id' || p_invoice_id
848 );
849 END IF; --( ln_proc_level >= ln_dbg_level)
850
851 insert into jai_ap_tds_inv_cancels
852 (tds_inv_cancel_id,
853 invoice_id,
854 threshold_grp_id,
855 cancel_amount,
856 parent_threshold_trx_id,
857 tds_section_code,
858 tax_category_id,
859 tds_invoice_flag,
860 tds_invoice_message,
861 tds_credit_memo_flag,
862 tds_credit_memo_message,
863 threshold_trx_id_cancel,
864 created_by,
865 creation_date,
866 last_updated_by,
867 last_update_date,
868 last_update_login)
869 values
870 (jai_ap_tds_inv_cancels_s.nextval,
871 p_invoice_id,
872 cur_rec.threshold_grp_id,
873 cur_rec.taxable_amount,
874 cur_rec.threshold_trx_id,
875 cur_rec.tds_section_code,
876 cur_rec.tax_category_id,
877 lv_tds_invoice_flag,
878 lv_tds_invoice_message,
879 lv_tds_credit_memo_flag,
880 lv_tds_credit_memo_message,
881 ln_threshold_trx_id,
882 fnd_global.user_id,
883 sysdate,
884 fnd_global.user_id,
885 sysdate,
886 fnd_global.login_id);
887
888 --Added by Sanjikum for Bug#5131075(4718907)
889 jai_ap_dtc_generation_pkg.get_tds_threshold_slab(p_prepay_distribution_id => NULL,
890 p_invoice_id => p_invoice_id,
891 p_threshold_grp_id => cur_rec.threshold_grp_id,
892 p_threshold_hdr_id => cur_rec.threshold_hdr_id,
893 p_threshold_slab_id => ln_after_threshold_slab_id,
894 p_threshold_type => lv_after_threshold_type,
895 p_process_flag => lv_process_flag,
896 p_process_message => lv_process_message,
897 p_codepath => lv_codepath);
898
899 IF lv_process_flag = 'E' THEN
900 goto end_of_main_loop;
901 END IF;
902
903 /*update TDS_ROLLBACKED to Y for this transaction record*/
904 UPDATE JAI_AP_TDS_THHOLD_TRXS
905 SET TDS_ROLLBACKED = 'Y'
906 WHERE threshold_trx_id = cur_rec.threshold_trx_id;
907
908 r_ap_invoices_all := NULL;
909
910 OPEN c_ap_invoices_all(p_invoice_id);
911 FETCH c_ap_invoices_all
912 into r_ap_invoices_all;
913 CLOSE c_ap_invoices_all;
914
915 jai_ap_dtc_generation_pkg.process_threshold_rollback(p_invoice_id => p_invoice_id,
916 p_before_threshold_type => lv_threshold_type,
917 p_after_threshold_type => lv_after_threshold_type,
918 p_before_threshold_slab_id => ln_threshold_slab_id,
919 p_after_threshold_slab_id => ln_after_threshold_slab_id,
920 p_threshold_grp_id => cur_rec.threshold_grp_id,
921 p_org_id => r_ap_invoices_all.org_id,
922 p_accounting_date => ld_accounting_date,
923 p_invoice_distribution_id => NULL,
924 p_prepay_distribution_id => NULL,
925 p_process_flag => lv_process_flag,
926 p_process_message => lv_process_message,
927 p_called_from => 'CANCEL',
928 p_codepath => lv_codepath);
929
930 IF lv_process_flag = 'E' THEN
931 goto end_of_main_loop;
932 END IF;
933
934 END IF;
935 ln_taxable_amt := 0;
936
937 <<end_of_main_loop>>
938 NULL;
939 --End Added by Sanjikum for Bug#5131075(4718907)
940
941 END LOOP; /* Get all the TDS invoices that were generated at the time of the INVOICE VALIDATE */
942
943 if ln_start_threshold_trx_id is not null then
944
945 /* Some invoices have been generated, call the program for invoking import and approval */
946
947 jai_ap_tds_generation_pkg.import_and_approve(p_invoice_id => p_invoice_id,
948 p_start_thhold_trx_id => ln_start_threshold_trx_id,
949 p_tds_event => 'INVOICE CANCEL',
950 p_process_flag => lv_tds_credit_memo_flag,
951 p_process_message => lv_tds_credit_memo_message);
952 end if;
953
954 /* Process Cases where TDS invoice was not generated because of threshold not being reached. */
955
956 /* Get the exchange rate of the invoice, may be required for taxable_amount in INR for foreign currency */
957 lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath); /* 20 */
958 open c_get_parent_inv_dtls(p_invoice_id);
959 fetch c_get_parent_inv_dtls
960 into r_get_parent_inv_dtls;
961 close c_get_parent_inv_dtls;
962
963 open c_gl_sets_of_books(r_get_parent_inv_dtls.set_of_books_id);
964 fetch c_gl_sets_of_books
965 into r_gl_sets_of_books;
966 close c_gl_sets_of_books;
967
968 if r_gl_sets_of_books.currency_code <>
969 r_get_parent_inv_dtls.invoice_currency_code then
970 lv_codepath := jai_general_pkg.plot_codepath(21, lv_codepath); /* 21 */
971 /* Foreign currency invoice */
972 ln_exchange_rate := r_get_parent_inv_dtls.exchange_rate;
973 end if;
974
975 lv_codepath := jai_general_pkg.plot_codepath(22, lv_codepath); /* 22 */
976 ln_exchange_rate := nvl(ln_exchange_rate, 1);
977 /*ETDS FVU3.1 Bug#11896260*/
978 /*To check the invoices for which the TDS has not been deducted as threshold is not breached and same threshold having crossed with another invoice and is paid.*/
979 /*Commented by Chong for ZX investigation 20120816
980 for r_threshold_breach in c_threshold_breach(p_invoice_id) loop
981 if (r_threshold_breach.tds_event like 'THRESHOLD TRANSITION%') then
982 lv_count := lv_count + 1;
983
984 elsif (r_threshold_breach.tds_event like 'THRESHOLD ROLLBACK%') then
985 lv_count := lv_count - 1;
986 end if;
987 for tds_id in (select invoice_to_tds_authority_id
988 from jai_ap_tds_thhold_trxs
989 where invoice_id = r_threshold_breach.invoice_id) loop
990
991 open c_ap_invoices_all(tds_id.invoice_to_tds_authority_id);
992 fetch c_ap_invoices_all
993 into r_ap_invoices_all;
994 close c_ap_invoices_all;
995 if r_ap_invoices_all.payment_status_flag <> 'N' and
996 lv_payment = 'N' then
997 lv_payment := 'Y';
998 end if;
999 end loop;
1000 end loop;
1001 if ((mod(lv_count, 2) <> 0) and lv_payment = 'Y') then
1002 lv_process_flag := 'E';
1003 lv_process_message := 'Threshold transition TDS has already been paid for the attached threshold_grp_id.Cannot cancel the invoice';
1004 goto exit_from_procedure;
1005 end if;
1006 Commented by Chong for ZX investigation 20120816*/
1007 /*End-ETDS FVU3.1 Bug 11896260*/
1008 for cur_rec in c_jai_ap_tds_inv_taxes(p_invoice_id, 'TDS_SECTION') --rchandan for bug#4428980
1009 loop
1010 --Fnd_File.put_line(Fnd_File.LOG, 'outer Loop');
1011 IF ( ln_proc_level >= ln_dbg_level)
1012 THEN
1013 FND_LOG.STRING ( ln_proc_level
1014 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
1015 , 'outer Loop'
1016 );
1017 END IF; --( ln_proc_level >= ln_dbg_level)
1018 lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath); /* 23 */
1019 ln_threshold_grp_id := null;
1020 r_ja_in_tax_codes := null;
1021 ln_taxable_amount := null;
1022
1023 ln_trx_invoice_amount := NULL;
1024 ln_threshold_grp_audit_id := null;
1025
1026 /* commented by ChongLei start
1027
1028 open c_ja_in_tax_codes(cur_rec.tax_id);
1029 fetch c_ja_in_tax_codes
1030 into r_ja_in_tax_codes;
1031 close c_ja_in_tax_codes;
1032
1033 if r_ja_in_tax_codes.tax_rate <> 0 then
1034 lv_codepath := jai_general_pkg.plot_codepath(24, lv_codepath); \* 24 *\
1035 ln_taxable_amount := cur_rec.tax_amount *
1036 (100 / r_ja_in_tax_codes.tax_rate);
1037 ln_taxable_amount := round(ln_taxable_amount, 2);
1038 else
1039 \* 0 rated tax *\
1040 lv_codepath := jai_general_pkg.plot_codepath(25, lv_codepath); \* 25 *\
1041 ln_taxable_amount := cur_rec.taxable_amount * ln_exchange_rate;
1042 end if;
1043
1044 */
1045 --TRX_INVOICE_AMOUNT from JAI_AP_TDS_THGRP_AUDITS
1046 OPEN c_get_trx_invoice_amount(p_invoice_id,cur_rec.threshold_grp_id );
1047 FETCH c_get_trx_invoice_amount INTO ln_trx_invoice_amount;
1048 CLOSE c_get_trx_invoice_amount;
1049
1050 --Get the current Threshold Slab
1051 jai_ap_dtc_generation_pkg.get_tds_threshold_slab(p_prepay_distribution_id => NULL,
1052 p_invoice_id => p_invoice_id,
1053 p_threshold_grp_id => cur_rec.threshold_grp_id,
1054 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
1055 p_threshold_slab_id => ln_threshold_slab_id,
1056 p_threshold_type => lv_threshold_type,
1057 p_process_flag => lv_process_flag,
1058 p_process_message => lv_process_message,
1059 p_codepath => lv_codepath);
1060
1061 IF lv_process_flag = 'E' THEN
1062 goto end_of_outer_loop;
1063 END IF;
1064
1065 --Update the Threshold Group with the Invoice Amount fetched in the above step.
1066 lv_codepath := jai_general_pkg.plot_codepath(26, lv_codepath); /* 26 */
1067 ln_threshold_grp_id := cur_rec.threshold_grp_id;
1068 jai_ap_dtc_generation_pkg.maintain_thhold_grps(p_threshold_grp_id => ln_threshold_grp_id,
1069 p_trx_invoice_cancel_amount => ln_trx_invoice_amount,
1070 p_tds_event => 'INVOICE CANCEL',
1071 p_invoice_id => p_invoice_id,
1072 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
1073 p_process_flag => lv_process_flag,
1074 P_process_message => lv_process_message,
1075 p_codepath => lv_codepath);
1076
1077 --fnd_file.put_line(FND_FILE.LOG, 'lv_process_flag: ' || lv_process_flag);
1078 IF ( ln_proc_level >= ln_dbg_level)
1079 THEN
1080 FND_LOG.STRING ( ln_proc_level
1081 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
1082 , 'lv_process_flag: ' || lv_process_flag
1083 );
1084 END IF; --( ln_proc_level >= ln_dbg_level)
1085 IF lv_process_flag = 'E' THEN
1086 goto end_of_outer_loop;
1087 END IF;
1088
1089 --fnd_file.put_line(FND_FILE.LOG, 'Insert cancel 2: p_invoice_id' || p_invoice_id);
1090 IF ( ln_proc_level >= ln_dbg_level)
1091 THEN
1092 FND_LOG.STRING ( ln_proc_level
1093 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
1094 , 'Insert cancel 2: p_invoice_id' || p_invoice_id
1095 );
1096 END IF; --( ln_proc_level >= ln_dbg_level)
1097
1098 insert into jai_ap_tds_inv_cancels
1099 (tds_inv_cancel_id,
1100 invoice_id,
1101 threshold_grp_id,
1102 cancel_amount,
1103 parent_threshold_trx_id,
1104 tds_invoice_flag,
1105 tds_invoice_message,
1106 tds_credit_memo_flag,
1107 tds_credit_memo_message,
1108 threshold_trx_id_cancel,
1109 created_by,
1110 creation_date,
1111 last_updated_by,
1112 last_update_date,
1113 last_update_login)
1114 values
1115 (jai_ap_tds_inv_cancels_s.nextval,
1116 p_invoice_id,
1117 cur_rec.threshold_grp_id,
1118 ln_taxable_amount,
1119 null,
1120 null,
1121 null,
1122 null,
1123 null,
1124 null,
1125 fnd_global.user_id,
1126 sysdate,
1127 fnd_global.user_id,
1128 sysdate,
1129 fnd_global.login_id);
1130 lv_codepath := jai_general_pkg.plot_codepath(27, lv_codepath); /* 27 */
1131
1132 jai_ap_dtc_generation_pkg.get_tds_threshold_slab(p_prepay_distribution_id => NULL,
1133 p_invoice_id => p_invoice_id,
1134 p_threshold_grp_id => cur_rec.threshold_grp_id,
1135 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
1136 p_threshold_slab_id => ln_after_threshold_slab_id,
1137 p_threshold_type => lv_after_threshold_type,
1138 p_process_flag => lv_process_flag,
1139 p_process_message => lv_process_message,
1140 p_codepath => lv_codepath);
1141
1142 IF lv_process_flag = 'E' THEN
1143 goto end_of_outer_loop;
1144 END IF;
1145
1146 r_ap_invoices_all := NULL;
1147
1148 OPEN c_ap_invoices_all(p_invoice_id);
1149 FETCH c_ap_invoices_all
1150 into r_ap_invoices_all;
1151 CLOSE c_ap_invoices_all;
1152
1153 jai_ap_dtc_generation_pkg.process_threshold_rollback(p_invoice_id => p_invoice_id,
1154 p_before_threshold_type => lv_threshold_type,
1155 p_after_threshold_type => lv_after_threshold_type,
1156 p_before_threshold_slab_id => ln_threshold_slab_id,
1157 p_after_threshold_slab_id => ln_after_threshold_slab_id,
1158 p_threshold_grp_id => cur_rec.threshold_grp_id,
1159 p_org_id => r_ap_invoices_all.org_id,
1160 p_accounting_date => sysdate, --modified ld_accounting_date to sysdate by Bgowrava for bug#8682951
1161 p_invoice_distribution_id => NULL,
1162 p_prepay_distribution_id => NULL,
1163 p_process_flag => lv_process_flag,
1164 p_process_message => lv_process_message,
1165 p_called_from => 'CANCEL',
1166 p_codepath => lv_codepath);
1167
1168 IF lv_process_flag = 'E' THEN
1169 goto end_of_outer_loop;
1170 END IF;
1171
1172 <<end_of_outer_loop>>
1173 NULL;
1174
1175 end loop;
1176
1177 --Added by Chong for bug#16414088 eTDS ER Start
1178 ----------------------------------------------------------------------
1179 --Hook code add here after all cancellation logic processed
1180 IF lv_process_flag = 'E' THEN
1181 NULL;
1182 ELSE
1183 jai_ap_tds_pop_rpst_pkg.process_cancellation (
1184 pn_source_invoice_id => p_invoice_id
1185 );
1186 END IF; --n_threshold_trx_id IS NULL
1187 ----------------------------------------------------------------------
1188 --Added by Chong for bug#16414088 eTDS ER End
1189
1190 <<exit_from_procedure>>
1191
1192 lv_codepath := jai_general_pkg.plot_codepath(100, lv_codepath, null,'END'); /* 100 */
1193 --Fnd_File.put_line(Fnd_File.LOG, lv_codepath);
1194 --Fnd_File.put_line(Fnd_File.LOG, '**** END of procedure jai_ap_dtc_cancellation_pkg.process_invoice ****');
1195 IF ( ln_proc_level >= ln_dbg_level)
1196 THEN
1197 FND_LOG.STRING ( ln_proc_level
1198 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
1199 , lv_codepath
1200 );
1201 END IF; --( ln_proc_level >= ln_dbg_level)
1202 IF ( ln_proc_level >= ln_dbg_level)
1203 THEN
1204 FND_LOG.STRING ( ln_proc_level
1205 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
1206 , '**** END of procedure jai_ap_dtc_cancellation_pkg.process_invoice ****'
1207 );
1208 END IF; --( ln_proc_level >= ln_dbg_level)
1209 errbuf := lv_codepath;
1210
1211 if lv_process_flag = 'E' then
1212 --raise_application_error(-20012, lv_process_message); --Commented by Chong for ZX investigation 20120816
1213 Errbuf := lv_process_message; --Added by Chong for ZX investigation 20120816
1214 Retcode := 'E'; --Added by Chong for ZX investigation 20120816
1215 end if;
1216 return;
1217
1218 exception
1219 when others then
1220 errbuf := 'jai_ap_dtc_cancellation_pkg.process_invoice :' || sqlerrm;
1221 --Fnd_File.put_line(Fnd_File.LOG, 'Err:process_invoice :' || sqlerrm);
1222 IF ( ln_proc_level >= ln_dbg_level)
1223 THEN
1224 FND_LOG.STRING ( ln_proc_level
1225 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
1226 , 'Err:process_invoice :' || sqlerrm
1227 );
1228 END IF; --( ln_proc_level >= ln_dbg_level)
1229 --raise_application_error(-20013, errbuf); --Commented by Chong for ZX investigation 20120816
1230 Retcode := 'E'; --Added by Chong for ZX investigation 20120816
1231 return;
1232 end process_invoice_cancellation;
1233
1234 end jai_ap_dtc_cancellation_pkg;