[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.7 2007/05/14 07:04:26 bduvarag 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
23 Created this package for implementing the TDS Cancellation
24 functionality onto AP invoice.
25
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
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 ---------------------------------------------------------------------------- */
75
76 /***********************************************************************************************/
77
78 procedure process_invoice
79 (
80 errbuf out nocopy varchar2,
81 retcode out nocopy varchar2,
82 p_invoice_id in number
83 )
84 is
85
86 cursor c_jai_ap_tds_thhold_trxs (p_invoice_id number,cp_tds_event jai_ap_tds_thhold_trxs.tds_event%type) is--rchandan for bug#4428980
87 select threshold_trx_id,
88 threshold_grp_id,
89 threshold_hdr_id, --Added by Sanjikum for Bug#5131075(4718907)
90 tax_id,
91 taxable_amount,
92 tax_amount,
93 invoice_to_tds_authority_id,
94 invoice_to_vendor_id
95 from jai_ap_tds_thhold_trxs
96 where invoice_id = p_invoice_id
97 and tds_event = cp_tds_event;--rchandan for bug#4428980
98
99 cursor c_ja_in_tax_codes(p_tax_id number) is
100 select vendor_id,
101 vendor_site_id,
102 tax_rate
103 from JAI_CMN_TAXES_ALL
104 where tax_id = p_tax_id;
105
106 /* Bug 4559756. Added by Lakshmi Gopalsami
107 Added org_id for passing it to ap_utilities_pkg
108
109 */
110 cursor c_ap_invoices_all(p_invoice_id number) is
111 select invoice_id,
112 cancelled_date,
113 payment_status_flag,
114 invoice_amount,
115 set_of_books_id,
116 invoice_num,
117 org_id
118 from ap_invoices_all
119 where invoice_id = p_invoice_id;
120
121 cursor c_get_parent_inv_dtls(p_invoice_id number) is
122 select set_of_books_id,
123 invoice_currency_code,
124 exchange_rate
125 from ap_invoices_all
126 where invoice_id = p_invoice_id;
127
128
129 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
130 select threshold_grp_id,
131 actual_tax_id tax_id,
132 sum(amount) taxable_amount,
133 sum(tax_amount) tax_amount
134 from jai_ap_tds_inv_taxes jtdsi
135 where invoice_id = p_invoice_id
136 and section_type = cp_section_type --cp_section_type--rchandan for bug#4428980
137 and threshold_grp_id is not null
138 and threshold_trx_id is null
139 group by threshold_grp_id, actual_tax_id;
140
141 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
142 select currency_code
143 from gl_sets_of_books
144 where set_of_books_id = cp_set_of_books_id;
145
146 /* Bug#5131075(5193852). Added by Lakshmi Gopalsami
147 | Changed the source of ld_accounting_date to refer to
148 | ap_invoice_distributions_all.accounting_date instead of
149 | gl_date on headers.
150 */
151 CURSOR get_dist_gl_date( cp_invoice_id IN ap_invoices_all.invoice_id%TYPE)
152 IS
153 SELECT accounting_date
154 FROM ap_invoice_distributions_all
155 WHERE invoice_id = cp_invoice_id
156 AND distribution_line_number = 1;
157 -- Only one distribution will be created for TDS invoices and so
158 -- hard coded the distribution line number to 1.
159
160
161
162 r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
163 r_ap_invoices_all c_ap_invoices_all%rowtype;
164 r_get_parent_inv_dtls c_get_parent_inv_dtls%rowtype;
165 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
166
167 lv_code_path VARCHAR2(1996);
168 lv_process_flag varchar2(1);
169 lv_process_message varchar2(250);
170 lv_tds_invoice_flag varchar2(1);
171 lv_tds_invoice_message varchar2(250);
172 lv_tds_credit_memo_flag varchar2(1);
173 lv_tds_credit_memo_message varchar2(250);
174
175 lb_return_value boolean;
176
177 lv_out_message_name varchar2(240);
178 ln_out_invoice_amount number;
179 ln_out_base_amount number;
180 ln_out_tax_amount number;
181 ln_out_temp_cancelled_amount number;
182 ln_out_cancelled_by number;
183 ln_out_cancelled_amount number;
184 ld_out_cancelled_date date;
185 ld_out_last_update_date date;
186 ln_out_original_prepay_amount number;
187 ln_out_pay_curr_inv_amount number;
188
189 ld_accounting_date date; --File.Sql.35 Cbabu := sysdate;
190 lv_open_period ap_invoice_distributions_all.period_name%type;
191
192 lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
193 lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
194 ln_threshold_trx_id number;
195
196 ln_taxable_amount number;
197 ln_exchange_rate ap_invoices_all.exchange_rate%type;
198 lv_codepath VARCHAR2(1996);
199 ln_start_threshold_trx_id number;
200 ln_threshold_grp_id number;
201 ln_threshold_grp_audit_id number;
202 lv_new_transaction varchar2(1);
203 lv_token varchar2(4000);
204 --Added by Sanjikum the below 5 variables for bug#5131075(4718907)
205 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
206 lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
207 ln_after_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
208 lv_after_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
209 ln_temp_threshold_hdr_id jai_ap_tds_thhold_hdrs.threshold_hdr_id%TYPE;
210 ld_ret_accounting_date DATE ; -- bug#5131075(5193852). Added by Lakshmi Gopalsami
211
212 begin
213
214 --ld_accounting_date := sysdate; --commented by Harshita for Bug#5131075(5193852)
215
216 lv_codepath := jai_general_pkg.plot_codepath(1, lv_codepath, 'jai_ap_tds_cancellation_pkg.process_invoice', 'START'); /* 1 */
217 Fnd_File.put_line(Fnd_File.LOG, '**** Start of procedure jai_ap_tds_cancellation_pkg.process_invoice ****');
218
219 /* Check if Invoice was created after the tds clean up patch */
220 jai_ap_tds_tax_defaultation.check_old_transaction
221 (
222 p_invoice_id => p_invoice_id,
223 p_new_transaction => lv_new_transaction
224 );
225
226 if nvl(lv_new_transaction, 'N') = 'N' then
227 /* Invoice was created before application of TDS clean up, need to call the old procedure */
228 lv_codepath := jai_general_pkg.plot_codepath(1.0, lv_codepath); /* 1.0 */
229 Fnd_File.put_line(Fnd_File.LOG, '**** Transaction before application of TDS clean up Calling procedure ****');
230 Fnd_File.put_line(Fnd_File.LOG, ' Invoking OLD procedure jai_ap_tds_old_pkg.cancel_invoice');
231
232 jai_ap_tds_old_pkg.cancel_invoice
233 (
234 errbuf => errbuf,
235 retcode => retcode,
236 p_invoice_id => p_invoice_id
237 );
238
239 goto exit_from_procedure;
240
241 end if;
242
243 /* bug 4559756. Added by Lakshmi Gopalsami
244 Fetch the org_id
245 */
246 open c_ap_invoices_all(p_invoice_id);
247 fetch c_ap_invoices_all into r_ap_invoices_all;
248 close c_ap_invoices_all;
249
250
251 --Removed the code from here by Lakshmi Gopalsami for Bug#5131075(5193852)
252
253 lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath); /* 2 */
254
255 /* Get all the TDS invoices that were generated at the time of the INVOICE VALIDATE */
256 for cur_rec in c_jai_ap_tds_thhold_trxs(p_invoice_id,'INVOICE VALIDATE')
257 loop
258
259 --Start Added by Sanjikum for Bug#5131075(4718907)
260 jai_ap_tds_generation_pkg.get_tds_threshold_slab(
261 p_prepay_distribution_id => NULL,
262 p_threshold_grp_id => cur_rec.threshold_grp_id,
263 p_threshold_hdr_id => cur_rec.threshold_hdr_id,
264 p_threshold_slab_id => ln_threshold_slab_id,
265 p_threshold_type => lv_threshold_type,
266 p_process_flag => lv_process_flag,
267 p_process_message => lv_process_message,
268 p_codepath => lv_codepath);
269
270 IF lv_process_flag = 'E' THEN
271 goto end_of_main_loop;
272 END IF;
273 --End Added by Sanjikum for Bug#5131075(4718907)
274
275
276 lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath); /* 3 */
277
278 lv_tds_invoice_flag := null;
279 lv_tds_invoice_message := null;
280 lv_tds_credit_memo_flag := null;
281 lv_tds_credit_memo_message := null;
282
283 /* Get the details of the Invoice to TDS authority */
284 r_ap_invoices_all := null;
285 open c_ap_invoices_all(cur_rec.invoice_to_tds_authority_id);
286 fetch c_ap_invoices_all into r_ap_invoices_all;
287 close c_ap_invoices_all;
288
289 /* Bug#5131075(5193852). Added by Lakshmi Gopalsami
290 * Derivced the accounting_date of the original distribution
291 * as this value is also getting passed for Threshold adjustments if the
292 * TDS invoice is already paid/cancelled.
293 */
294
295 OPEN get_dist_gl_date(cur_rec.invoice_to_tds_authority_id);
296 FETCH get_dist_gl_date INTO ld_accounting_date;
297 CLOSE get_dist_gl_date;
298
299
300 /* Check if the TDS invoice is paid, no processing is required if it is already paid. */
301 if r_ap_invoices_all.payment_status_flag <> 'N' then
302 lv_codepath := jai_general_pkg.plot_codepath(4, lv_codepath); /* 4 */
303 lv_tds_invoice_flag := 'X';
304 lv_tds_invoice_message := 'Invoice to TDS Authority is already paid.';
305 lv_tds_credit_memo_flag := 'X';
306 lv_tds_credit_memo_message := 'No processing as Invoice to TDS Authority is already paid';
307 goto Continue_with_next_record;
308 end if;
309
310 /* Check if the invoice to TDS authority is already canccelled,
311 if yes, no need to do the cancel processing of the invoice */
312 lv_codepath := jai_general_pkg.plot_codepath(5, lv_codepath); /* 5 */
313 if r_ap_invoices_all.cancelled_date is not null then
314 lv_codepath := jai_general_pkg.plot_codepath(6, lv_codepath); /* 6 */
315 lv_tds_invoice_flag := 'X';
316 lv_tds_invoice_message := ' Invoice to TDS Authority is already Cancelled.';
317 goto credit_memo_processing;
318 end if;
319
320 /* Control comes here only when the Invoice to TDS Authority is not paid or not cancelled, need to cancel it */
321
322 lb_return_value := null;
323 lv_out_message_name := null;
324 ln_out_invoice_amount := null;
325 ln_out_base_amount := null;
326 ln_out_tax_amount := null;
327 ln_out_temp_cancelled_amount := null;
328 ln_out_cancelled_by := null;
329 ln_out_cancelled_amount := null;
330 ld_out_cancelled_date := null;
331 ld_out_last_update_date := null;
335
332 ln_out_original_prepay_amount := null;
333 ln_out_pay_curr_inv_amount := null;
334 lv_token := null;
336 lv_codepath := jai_general_pkg.plot_codepath(7, lv_codepath); /* 7 */
337
338 /* Start for Bug#5131075(5193852). Added by Lakshmi Gopalsami.
339 | Fetch the accounting date of TDS invoice distribution
340 | so that the same will be passed for reversal line which will get
341 | created for cancellation.
342 */
343 --Check if the given date is in current open period
344 lv_open_period:= ap_utilities_pkg.get_current_gl_date
345 (P_Date => ld_accounting_date,
346 P_Org_Id => r_ap_invoices_all.org_id);
347
348
349 if lv_open_period is null then
350
351 lv_codepath := jai_general_pkg.plot_codepath(1.1, lv_codepath); /* 1.1 */
352
353 ap_utilities_pkg.get_open_gl_date
354 (
355 p_date => ld_accounting_date, /* In date */
356 p_period_name => lv_open_period, /* out Period */
357 p_gl_date => ld_accounting_date, /* out date */
358 P_Org_Id => r_ap_invoices_all.org_id
359 );
360
361
362 if lv_open_period is null then
363 lv_codepath := jai_general_pkg.plot_codepath(1.2, lv_codepath); /* 1.2 */
364 lv_process_flag := 'E';
365 lv_process_message := 'No open accounting Period after : ' || ld_accounting_date ;
366 goto exit_from_procedure;
367 end if;
368
369 ld_accounting_date := ld_ret_accounting_date;
370
371 end if; /* lv_open_period is null */
372 ld_ret_accounting_date := NULL;
373
374 -- End for bug#5131075(5193852). Added by Lakshmi Gopalsami
375
376 /* Bug 4523064. Added by Lakshmi Gopalsami
377 Commented the following parameters
378 P_set_of_books_id
379 P_period_name
380 P_set_of_books_id
381 P_Check_id
382 and added the following OUT Parameter
383 P_Token
384 */
385
386 lb_return_value :=
387 ap_cancel_pkg.ap_cancel_single_invoice
388 (
389 P_invoice_id => cur_rec.invoice_to_tds_authority_id ,
390 P_last_updated_by => fnd_global.user_id ,
391 P_last_update_login => fnd_global.login_id ,
392 --P_set_of_books_id => r_ap_invoices_all.set_of_books_id ,
393 P_accounting_date => ld_accounting_date ,
394 --P_period_name => lv_open_period ,
395 P_message_name => lv_out_message_name ,
396 P_invoice_amount => ln_out_invoice_amount ,
397 P_base_amount => ln_out_base_amount ,
398 --P_tax_amount => ln_out_tax_amount ,
399 P_temp_cancelled_amount => ln_out_temp_cancelled_amount ,
400 P_cancelled_by => ln_out_cancelled_by ,
401 P_cancelled_amount => ln_out_cancelled_amount ,
402 P_cancelled_date => ld_out_cancelled_date ,
403 P_last_update_date => ld_out_last_update_date ,
404 P_original_prepayment_amount => ln_out_original_prepay_amount ,
405 --P_check_id => null ,
406 P_pay_curr_invoice_amount => ln_out_pay_curr_inv_amount ,
407 P_Token => lv_token,
408 P_calling_sequence => 'India Localization - cancel TDS invoice'
409 );
410
411 lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath); /* 8 */
412
413 /* Bug4523064. Check whether any value is returned in lv_token.
414 IF it is not null display the error message. */
415
416 IF nvl(lv_token,'A') <> 'A' Then
417 APP_EXCEPTION.RAISE_EXCEPTION(EXCEPTION_TYPE => 'APP',
418 EXCEPTION_CODE => NULL,
419 EXCEPTION_TEXT => lv_token);
420
421 End if;
422
423 /* Bug4523064. Added by Lakshmi Gopalsami
424 Commented the tax_amount update */
425
426 update ap_invoices_all
427 set invoice_amount = ln_out_invoice_amount ,
428 base_amount = ln_out_base_amount ,
429 --tax_amount = ln_out_tax_amount ,
430 temp_cancelled_amount = ln_out_temp_cancelled_amount ,
431 cancelled_by = ln_out_cancelled_by ,
432 cancelled_amount = ln_out_cancelled_amount ,
433 cancelled_date = ld_out_cancelled_date ,
434 last_update_date = ld_out_last_update_date ,
435 original_prepayment_amount = ln_out_original_prepay_amount ,
436 pay_curr_invoice_amount = ln_out_pay_curr_inv_amount
437 where invoice_id = cur_rec.invoice_to_tds_authority_id;
438
442
439 /*What if ap_cancel_pkg.ap_cancel_single_invoice is not there ?? */
440 lv_tds_invoice_flag := 'Y';
441 lv_tds_invoice_message := 'Invoice to TDS Authority is Cancelled ';
443 lv_codepath := jai_general_pkg.plot_codepath(9, lv_codepath); /* 9 */
444
445
446 << credit_memo_processing >>
447 /* Get the details of the Credit memo to the supplier for TDS */
448 r_ap_invoices_all := null;
449 open c_ap_invoices_all(cur_rec.invoice_to_vendor_id);
450 fetch c_ap_invoices_all into r_ap_invoices_all;
451 close c_ap_invoices_all;
452
453 /* Bug#5131075(5193852). Added by Lakshmi Gopalsami
454 * Derivced the accounting_date of the original distribution
455 * as this value is also getting passed for Threshold adjustments if the
456 * TDS CM invoice is already paid/cancelled.
457 */
458
459 OPEN get_dist_gl_date(cur_rec.invoice_to_vendor_id );
460 FETCH get_dist_gl_date INTO ld_accounting_date;
461 CLOSE get_dist_gl_date;
462
463
464 /* Check if the Credit memo to the supplier for TDS already canccelled,
465 if yes, no need to cancel it again here */
466
467 if r_ap_invoices_all.cancelled_date is not null then
468 lv_codepath := jai_general_pkg.plot_codepath(10, lv_codepath); /* 10 */
469 lv_tds_credit_memo_flag := 'X';
470 lv_tds_credit_memo_message := 'Credit memo to the supplier for TDS is already Cancelled.';
471 goto Continue_with_next_record;
472 end if;
473
474 /* Check if the Credit memo to the supplier for TDS is paid,
475 if yes a separate invoice needs to be generated, or else the same credit memo can be cancelled */
476
477 if r_ap_invoices_all.payment_status_flag = 'N' then
478 lv_codepath := jai_general_pkg.plot_codepath(11, lv_codepath); /* 11 */
479 /* Credit memo not paid, can cancel the same */
480 lv_tds_credit_memo_flag := 'Y';
481 lv_tds_credit_memo_message := 'Cancelling Credit memo to the supplier for TDS as it is not paid ';
482
483 lb_return_value := null;
484 lv_out_message_name := null;
485 ln_out_invoice_amount := null;
486 ln_out_base_amount := null;
487 ln_out_tax_amount := null;
488 ln_out_temp_cancelled_amount := null;
489 ln_out_cancelled_by := null;
490 ln_out_cancelled_amount := null;
491 ld_out_cancelled_date := null;
492 ld_out_last_update_date := null;
493 ln_out_original_prepay_amount := null;
494 ln_out_pay_curr_inv_amount := null;
495 lv_token := NULL;
496
497 lv_codepath := jai_general_pkg.plot_codepath(12, lv_codepath); /* 12 */
498
499 /* Start for Bug#5131075(5193852). Added by Lakshmi Gopalsami.
500 | Fetch the accounting date of TDS invoice distribution
501 | so that the same will be passed for reversal line which will get
502 | created for cancellation.
503 */
504 --Check if the given date is in current open period
505 lv_open_period:= ap_utilities_pkg.get_current_gl_date
506 (P_Date => ld_accounting_date,
507 P_Org_Id => r_ap_invoices_all.org_id);
508
509
510 if lv_open_period is null then
511
512 lv_codepath := jai_general_pkg.plot_codepath(1.1, lv_codepath); /* 1.1 */
513
514 ap_utilities_pkg.get_open_gl_date
515 (
516 p_date => ld_accounting_date, /* In date */
517 p_period_name => lv_open_period, /* out Period */
518 p_gl_date => ld_accounting_date, /* out date */
519 P_Org_Id => r_ap_invoices_all.org_id
520 );
521
522
523 if lv_open_period is null then
524 lv_codepath := jai_general_pkg.plot_codepath(1.2, lv_codepath); /* 1.2 */
525 lv_process_flag := 'E';
526 lv_process_message := 'No open accounting Period after : ' || ld_accounting_date ;
527 goto exit_from_procedure;
528 end if;
529
530 ld_accounting_date := ld_ret_accounting_date;
531
532 end if; /* lv_open_period is null */
533 ld_ret_accounting_date := NULL;
534
535 -- End for bug#5131075(5193852). Added by Lakshmi Gopalsami
536
537
538 /* Bug 4523064. Added by Lakshmi Gopalsami
539 Commented the following parameters
540 P_set_of_books_id
541 P_period_name
542 P_set_of_books_id
543 P_Check_id
544 and added the following OUT Parameter
545 P_Token
546 */
547 lb_return_value :=
548 ap_cancel_pkg.ap_cancel_single_invoice
549 (
550 P_invoice_id => cur_rec.invoice_to_vendor_id ,
551 P_last_updated_by => fnd_global.user_id ,
552 P_last_update_login => fnd_global.login_id ,
553 --P_set_of_books_id => r_ap_invoices_all.set_of_books_id ,
554 P_accounting_date => ld_accounting_date ,
555 --P_period_name => lv_open_period ,
559 --P_tax_amount => ln_out_tax_amount ,
556 P_message_name => lv_out_message_name ,
557 P_invoice_amount => ln_out_invoice_amount ,
558 P_base_amount => ln_out_base_amount ,
560 P_temp_cancelled_amount => ln_out_temp_cancelled_amount ,
561 P_cancelled_by => ln_out_cancelled_by ,
562 P_cancelled_amount => ln_out_cancelled_amount ,
563 P_cancelled_date => ld_out_cancelled_date ,
564 P_last_update_date => ld_out_last_update_date ,
565 P_original_prepayment_amount => ln_out_original_prepay_amount ,
566 --P_check_id => null ,
567 P_pay_curr_invoice_amount => ln_out_pay_curr_inv_amount ,
568 P_token => lv_token ,
569 P_calling_sequence => 'India Localization - cancel TDS invoice'
570 );
571
572 lv_codepath := jai_general_pkg.plot_codepath(13, lv_codepath); /* 13 */
573
574 /* Bug4523064. Check whether any value is returned in lv_token.
575 IF it is not null display the error message. */
576
577 IF nvl(lv_token,'A') <> 'A' Then
578 APP_EXCEPTION.RAISE_EXCEPTION(EXCEPTION_TYPE => 'APP',
579 EXCEPTION_CODE => NULL,
580 EXCEPTION_TEXT => lv_token);
581 End if;
582
583 /*Bug4523064. Added by Lakshmi Gopalsami
584 Commented the tax_amount update */
585 update ap_invoices_all
586 set invoice_amount = ln_out_invoice_amount ,
587 base_amount = ln_out_base_amount ,
588 -- tax_amount = ln_out_tax_amount ,
589 temp_cancelled_amount = ln_out_temp_cancelled_amount ,
590 cancelled_by = ln_out_cancelled_by ,
591 cancelled_amount = ln_out_cancelled_amount ,
592 cancelled_date = ld_out_cancelled_date ,
593 last_update_date = ld_out_last_update_date ,
594 original_prepayment_amount = ln_out_original_prepay_amount ,
595 pay_curr_invoice_amount = ln_out_pay_curr_inv_amount
596 where invoice_id = cur_rec.invoice_to_vendor_id;
597
598
599 else
600 /* Credit memo has already been paid, have to generate a new invoice to nagate the effect */
601 lv_codepath := jai_general_pkg.plot_codepath(14, lv_codepath); /* 14 */
602 ln_threshold_trx_id := 0;
603 lv_invoice_to_tds_num := null;
604 lv_invoice_to_vendor_num := null;
605
606 jai_ap_tds_generation_pkg.generate_tds_invoices
607 (
608 pn_invoice_id => p_invoice_id ,
609 pv_invoice_num_to_vendor_can => r_ap_invoices_all.invoice_num,
610 pn_taxable_amount => cur_rec.taxable_amount ,
611 pn_tax_amount => cur_rec.tax_amount ,
612 pn_tax_id => cur_rec.tax_id ,
613 pd_accounting_date => ld_accounting_date ,
614 pv_tds_event => 'INVOICE CANCEL' ,
615 pn_threshold_grp_id => cur_rec.threshold_grp_id ,
616 pv_tds_invoice_num => lv_invoice_to_tds_num ,
617 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
618 pn_threshold_trx_id => ln_threshold_trx_id ,
619 p_process_flag => lv_tds_credit_memo_flag ,
620 p_process_message => lv_tds_credit_memo_message ,
621 -- Bug 5722028. Added by CSahoo
622 pd_creation_Date => sysdate
623
624
625
626 );
627
628
629 if lv_tds_credit_memo_flag = 'E' then
630 lv_codepath := jai_general_pkg.plot_codepath(15, lv_codepath); /* 15 */
631 goto Continue_with_next_record;
632 end if;
633
634 lv_tds_credit_memo_flag := 'Y';
635 lv_tds_credit_memo_message := 'Generated Standard invoice to suppliet : ' ||lv_invoice_to_vendor_num;
636 lv_codepath := jai_general_pkg.plot_codepath(16, lv_codepath); /* 16 */
637
638 if ln_start_threshold_trx_id is null then
639 ln_start_threshold_trx_id := ln_threshold_trx_id;
640 end if;
641
642 lv_codepath := jai_general_pkg.plot_codepath(17, lv_codepath); /* 17 */
643 /* Update the total tax amount for which Cancel invoice was raised */
644
645
646 end if; /* Credit memo to the supplier paid / not paid */
647
648 /* Control comes here when either the credit memo for the tds authority is cancelled or a
649 compensating standard invoice has been made */
650
654 p_threshold_grp_id => ln_threshold_grp_id,
651 ln_threshold_grp_id := cur_rec.threshold_grp_id;
652 jai_ap_tds_generation_pkg.maintain_thhold_grps
653 (
655 p_trx_tax_paid => (-1 * cur_rec.tax_amount),
656 p_tds_event => 'INVOICE CANCEL',
657 p_invoice_id => p_invoice_id,
658 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
659 p_process_flag => lv_process_flag,
660 P_process_message => lv_process_message,
661 p_codepath => lv_codepath
662 );
663
664
665 << Continue_with_next_record >>
666
667 lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath); /* 18 */
668 /* Update threshold for the cancel invoice amount */
669
670 ln_threshold_grp_id := cur_rec.threshold_grp_id;/*added by rchandan for bug#5131075(4947469)*/
671
672 jai_ap_tds_generation_pkg.maintain_thhold_grps
673 (
674 p_threshold_grp_id => ln_threshold_grp_id,
675 p_trx_invoice_cancel_amount => cur_rec.taxable_amount,
676 p_tds_event => 'INVOICE CANCEL',
677 p_invoice_id => p_invoice_id,
678 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
679 p_process_flag => lv_process_flag,
680 P_process_message => lv_process_message,
681 p_codepath => lv_codepath
682 );
683
684 /* insert into JAI_AP_TDS_INV_CANCELS */
685 lv_codepath := jai_general_pkg.plot_codepath(19, lv_codepath); /* 19 */
686
687 insert into jai_ap_tds_inv_cancels
688 (
689 tds_inv_cancel_id ,
690 invoice_id ,
691 threshold_grp_id ,
692 cancel_amount ,
693 parent_threshold_trx_id ,
694 tax_id ,
695 tds_invoice_flag ,
696 tds_invoice_message ,
697 tds_credit_memo_flag ,
698 tds_credit_memo_message ,
699 threshold_trx_id_cancel ,
700 created_by ,
701 creation_date ,
702 last_updated_by ,
703 last_update_date ,
704 last_update_login
705 )
706 values
707 (
708 jai_ap_tds_inv_cancels_s.nextval ,
709 p_invoice_id ,
710 cur_rec.threshold_grp_id ,
711 cur_rec.taxable_amount ,
712 cur_rec.threshold_trx_id ,
713 cur_rec.tax_id ,
714 lv_tds_invoice_flag ,
715 lv_tds_invoice_message ,
716 lv_tds_credit_memo_flag ,
717 lv_tds_credit_memo_message ,
718 ln_threshold_trx_id ,
719 fnd_global.user_id ,
720 sysdate ,
721 fnd_global.user_id ,
722 sysdate ,
723 fnd_global.login_id
724 );
725
726 --Added by Sanjikum for Bug#5131075(4718907)
727 jai_ap_tds_generation_pkg.get_tds_threshold_slab(
728 p_prepay_distribution_id => NULL,
729 p_threshold_grp_id => cur_rec.threshold_grp_id,
730 p_threshold_hdr_id => cur_rec.threshold_hdr_id,
731 p_threshold_slab_id => ln_after_threshold_slab_id,
732 p_threshold_type => lv_after_threshold_type,
733 p_process_flag => lv_process_flag,
734 p_process_message => lv_process_message,
735 p_codepath => lv_codepath);
736
737 IF lv_process_flag = 'E' THEN
738 goto end_of_main_loop;
739 END IF;
740
741 r_ap_invoices_all := NULL;
742
743 OPEN c_ap_invoices_all(p_invoice_id);
744 FETCH c_ap_invoices_all into r_ap_invoices_all;
745 CLOSE c_ap_invoices_all;
746
747 jai_ap_tds_generation_pkg.process_threshold_rollback(
748 p_invoice_id => p_invoice_id,
749 p_before_threshold_type => lv_threshold_type,
750 p_after_threshold_type => lv_after_threshold_type,
751 p_before_threshold_slab_id => ln_threshold_slab_id,
752 p_after_threshold_slab_id => ln_after_threshold_slab_id,
753 p_threshold_grp_id => cur_rec.threshold_grp_id,
754 p_org_id => r_ap_invoices_all.org_id,
755 p_accounting_date => ld_accounting_date,
756 p_process_flag => lv_process_flag,
757 p_process_message => lv_process_message,
761 goto end_of_main_loop;
758 p_codepath => lv_codepath);
759
760 IF lv_process_flag = 'E' THEN
762 END IF;
763
764 <<end_of_main_loop>>
765 NULL;
766 --End Added by Sanjikum for Bug#5131075(4718907)
767
768 end loop; /* Get all the TDS invoices that were generated at the time of the INVOICE VALIDATE */
769
770 if ln_start_threshold_trx_id is not null then
771
772 /* Some invoices have been generated, call the program for invoking import and approval */
773
774 jai_ap_tds_generation_pkg.import_and_approve
775 (
776 p_invoice_id => p_invoice_id,
777 p_start_thhold_trx_id => ln_start_threshold_trx_id,
778 p_tds_event => 'INVOICE CANCEL',
779 p_process_flag => lv_tds_credit_memo_flag,
780 p_process_message => lv_tds_credit_memo_message
781 );
782 end if;
783
784 /* Process Cases where TDS invoice was not generated because of threshold not being reached. */
785
786 /* Get the exchange rate of the invoice, may be required for taxable_amount in INR for foreign currency */
787 lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath); /* 20 */
788 open c_get_parent_inv_dtls(p_invoice_id);
789 fetch c_get_parent_inv_dtls into r_get_parent_inv_dtls;
790 close c_get_parent_inv_dtls;
791
792 open c_gl_sets_of_books(r_get_parent_inv_dtls.set_of_books_id);
793 fetch c_gl_sets_of_books into r_gl_sets_of_books;
794 close c_gl_sets_of_books;
795
796 if r_gl_sets_of_books.currency_code <> r_get_parent_inv_dtls.invoice_currency_code then
797 lv_codepath := jai_general_pkg.plot_codepath(21, lv_codepath); /* 21 */
798 /* Foreign currency invoice */
799 ln_exchange_rate := r_get_parent_inv_dtls.exchange_rate;
800 end if;
801
802 lv_codepath := jai_general_pkg.plot_codepath(22, lv_codepath); /* 22 */
803 ln_exchange_rate := nvl(ln_exchange_rate, 1);
804
805 for cur_rec in c_jai_ap_tds_inv_taxes(p_invoice_id,'TDS_SECTION')--rchandan for bug#4428980
806 loop
807 lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath); /* 23 */
808 ln_threshold_grp_id := null;
809 r_ja_in_tax_codes := null;
810 ln_taxable_amount := null;
811
812 open c_ja_in_tax_codes(cur_rec.tax_id);
813 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
814 close c_ja_in_tax_codes;
815
816 if r_ja_in_tax_codes.tax_rate <> 0 then
817 lv_codepath := jai_general_pkg.plot_codepath(24, lv_codepath); /* 24 */
818 ln_taxable_amount := cur_rec.tax_amount * (100/r_ja_in_tax_codes.tax_rate);
819 ln_taxable_amount := round(ln_taxable_amount, 2);
820 else
821 /* 0 rated tax */
822 lv_codepath := jai_general_pkg.plot_codepath(25, lv_codepath); /* 25 */
823 ln_taxable_amount := cur_rec.taxable_amount * ln_exchange_rate;
824 end if;
825
826 --Start Added by Sanjikum for Bug#5131075(4718907)
827 jai_ap_tds_generation_pkg.get_tds_threshold_slab(
828 p_prepay_distribution_id => NULL,
829 p_threshold_grp_id => cur_rec.threshold_grp_id,
830 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
831 p_threshold_slab_id => ln_threshold_slab_id,
832 p_threshold_type => lv_threshold_type,
833 p_process_flag => lv_process_flag,
834 p_process_message => lv_process_message,
835 p_codepath => lv_codepath);
836
837 IF lv_process_flag = 'E' THEN
838 goto end_of_outer_loop;
839 END IF;
840 --End Added by Sanjikum for Bug#5131075(4718907)
841
842 lv_codepath := jai_general_pkg.plot_codepath(26, lv_codepath); /* 26 */
843 ln_threshold_grp_id := cur_rec.threshold_grp_id;
844 jai_ap_tds_generation_pkg.maintain_thhold_grps
845 (
846 p_threshold_grp_id => ln_threshold_grp_id,
847 p_trx_invoice_cancel_amount => ln_taxable_amount,
848 p_tds_event => 'INVOICE CANCEL',
849 p_invoice_id => p_invoice_id,
850 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
851 p_process_flag => lv_process_flag,
852 P_process_message => lv_process_message,
853 p_codepath => lv_codepath
854 );
855
856 insert into jai_ap_tds_inv_cancels
857 (
858 tds_inv_cancel_id ,
859 invoice_id ,
860 threshold_grp_id ,
861 cancel_amount ,
862 parent_threshold_trx_id ,
863 tax_id ,
864 tds_invoice_flag ,
865 tds_invoice_message ,
866 tds_credit_memo_flag ,
867 tds_credit_memo_message ,
868 threshold_trx_id_cancel ,
869 created_by ,
870 creation_date ,
871 last_updated_by ,
872 last_update_date ,
873 last_update_login
874 )
875 values
876 (
877 jai_ap_tds_inv_cancels_s.nextval ,
878 p_invoice_id ,
879 cur_rec.threshold_grp_id ,
880 ln_taxable_amount ,
881 null ,
882 cur_rec.tax_id ,
883 null ,
884 null ,
885 null ,
886 null ,
887 null ,
888 fnd_global.user_id ,
889 sysdate ,
890 fnd_global.user_id ,
891 sysdate ,
892 fnd_global.login_id
893 );
894 lv_codepath := jai_general_pkg.plot_codepath(27, lv_codepath); /* 27 */
895
896 --Added by Sanjikum for Bug#5131075(4718907)
897 jai_ap_tds_generation_pkg.get_tds_threshold_slab(
898 p_prepay_distribution_id => NULL,
899 p_threshold_grp_id => cur_rec.threshold_grp_id,
900 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
901 p_threshold_slab_id => ln_after_threshold_slab_id,
902 p_threshold_type => lv_after_threshold_type,
903 p_process_flag => lv_process_flag,
904 p_process_message => lv_process_message,
905 p_codepath => lv_codepath);
906
907 IF lv_process_flag = 'E' THEN
908 goto end_of_outer_loop;
909 END IF;
910
911 r_ap_invoices_all := NULL;
912
913 OPEN c_ap_invoices_all(p_invoice_id);
914 FETCH c_ap_invoices_all into r_ap_invoices_all;
915 CLOSE c_ap_invoices_all;
916
917 jai_ap_tds_generation_pkg.process_threshold_rollback(
918 p_invoice_id => p_invoice_id,
919 p_before_threshold_type => lv_threshold_type,
920 p_after_threshold_type => lv_after_threshold_type,
921 p_before_threshold_slab_id => ln_threshold_slab_id,
922 p_after_threshold_slab_id => ln_after_threshold_slab_id,
923 p_threshold_grp_id => cur_rec.threshold_grp_id,
924 p_org_id => r_ap_invoices_all.org_id,
925 p_accounting_date => ld_accounting_date,
926 p_process_flag => lv_process_flag,
927 p_process_message => lv_process_message,
928 p_codepath => lv_codepath);
929
930 IF lv_process_flag = 'E' THEN
931 goto end_of_outer_loop;
932 END IF;
933
934 <<end_of_outer_loop>>
935 NULL;
936 --End Added by Sanjikum for Bug#5131075(4718907)
937
938 end loop;
939
940 << exit_from_procedure >>
941
942 lv_codepath := jai_general_pkg.plot_codepath(100, lv_codepath, null, 'END'); /* 100 */
943 Fnd_File.put_line(Fnd_File.LOG, lv_codepath);
944 Fnd_File.put_line(Fnd_File.LOG, '**** END of procedure jai_ap_tds_cancellation_pkg.process_invoice ****');
945
946 errbuf := lv_codepath;
947
948 if lv_process_flag = 'E' then
949 raise_application_error(-20012, lv_process_message);
950 end if;
951 return;
952
953 exception
954 when others then
955 errbuf := 'jai_ap_tds_cancellation_pkg.process_invoice :' || sqlerrm;
956 Fnd_File.put_line(Fnd_File.LOG, 'Err:process_invoice :' || sqlerrm);
957 raise_application_error(-20013, errbuf);
958 return;
959 end process_invoice;
960
961
962 /***********************************************************************************************/
963
964
965 end jai_ap_tds_cancellation_pkg;