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