[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_TDS_GENERATION_PKG
Source
1 PACKAGE BODY jai_ap_tds_generation_pkg
2 /* $Header: jai_ap_tds_gen.plb 120.26.12010000.6 2009/01/07 12:57:28 mbremkum ship $ */
3 AS
4 /* ----------------------------------------------------------------------------
5 FILENAME : jai_ap_tds_gen.plb
6
7 Created By : Aparajita
8
9 Created Date : 24-dec-2004
10
11 Bug :
12
13 Purpose : Implementation of tax defaultation functionality on AP invoice.
14
15 Called from : Trigger ja_in_ap_aia_after_trg
16 Trigger ja_in_ap_aida_after_trg
17
18 CHANGE HISTORY:
19 -------------------------------------------------------------------------------
20 S.No Date Author and Details
21 -------------------------------------------------------------------------------
22 1. 24/12/2004 Aparajita for bug#4088186. version#115.0. TDS Clean Up.
23 Created this package for implementing the tax defaultation
24 functionality onto AP invoice.
25
26 2. 11/05/2005 rchandan for bug#4333449. Version 116.1
27 A new procedure to insert into jai_ap_tds_thhold_trxs table is added.
28
29 India Original Invoice for TDS DFF is eliminated. So attribute1 of ap_invoices_al
30 is not populated whenever an invoice is generated. Instead the Invoice details are
31 populated into jai_ap_tds_thhold_trxs. So whenever data is inserted into interface
32 tables the jai_ap_tds_thhold_trxs table is also populated.
33
34 3. 11/05/2005 rchandan for bug#4323338. Version 116.2
35 India Org Info DFF is eliminated as a part of JA migration. A table by name JAI_AP_TDS_ORG_TANS is dropped
36 and a view jai_ap_tds_org_tan_v is created to retrieve the PAN NO.
37
38 4. 24/05/2005 Ramananda for bug#4388958 File Version: 116.1
39 Changed AP Lookup code from 'TDS' to 'INDIA TDS'
40
41 5. 02/06/2005 Ramananda for bug# 4407184 File Version: 116.2
42 SQL Bind variable compliance is done
43
44 6. 08-Jun-2005 File Version 116.3. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
45 as required for CASE COMPLAINCE.
46
47 7. 14-Jun-2005 rchandan for bug#4428980, Version 116.4
48 Modified the object to remove literals from DML statements and CURSORS.
49
50 8. 08-Jul-2005 Sanjikum for Bug#4482462
51 1) In the procedure - generate_tds_invoices, removed the column payment_method_lookup_code
52 from cursors - c_po_vendor_sites_all, c_po_vendors
53 2) In the procedure generate_tds_invoices, commented the if condition of payment_method_lookup_code
54 3) In the procedure generate_tds_invoices, commented the value of parameter - p_payment_method_lookup_code
55 while calling procedure - jai_ap_utils_pkg.insert_ap_inv_interface
56
57 Ramananda for bug# 4407184
58 Re-Done: SQL Bind variable compliance is done
59
60 9. 29-Jun-2005 ssumaith - bug#4448789 - removal of hr_operating_units.legal_entity_id from this trigger.
61
62 10. 14-Jul-2005 rchandan for bug#4487676.File version 117.2
63 Sequnece jai_ap_tds_invoice_num_s is renamed to JAI_AP_TDS_THHOLD_TRXS_S1
64
65 11. 25-Jul-2005 Bug4513458. added by Lakshmi Gopalsami version 120.2
66 Issue:
67 ------
68 TDS tax is always rounded to 2 decimal places
69 Fix:
70 ----
71 1) Changed the statement "ln_tax_amount :=
72 round(pn_tax_amount, 2);"
73 to "ln_tax_amount := pn_tax_amount;"
74 2) Before creating the invoice for TDS authority,
75 added the following condition -
76 "ln_invoice_to_tds_amount :=
77 ROUND(ln_invoice_to_tds_amount,0);"
78 3) In the IF of Supplier Invoice section, added the
79 following condition
80 "ELSE
81 ln_invoice_to_vendor_amount := round(
82 ln_invoice_to_vendor_amount, 0);"
83
84 12. 28-Jul-2005 Bug4522507. Added by Lakshmi Gopalsami Version 120.3
85 1) In the Procedure generate_tds_invoices,
86 changed the condition -
87 if ln_tax_amount <= 0 then to
88 if ROUND(ln_tax_amount,2) <= 0 then
89
90 Dependency(Functional)
91 -----------------------
92 jai_ap_tds_ppay.plb
93
94 13. 29-Jul-2005 Bug4522540. Added by Lakshmi Gopalsami Version 120.4
95 Start date and end date of a threshold type was not
96 being considered while selecting the applicable
97 threshold. This has been modified to check
98 threshold validity date range against the GL_date of
99 invoice distributions.
100
101 Dependency (Functional)
102 -----------------------
103 jai_ap_tds_dflt.plb Version 120.3
104
105 14. 18-Aug-2005 Ramananda for bug#4560109 during R12 Sanity Testing. File Verion 120.5
106 In generate_tds_invoices procedure:
107 Added the WHO columns in the 'insert into JAI_AP_TDS_INVOICES' statement
108
109 15. 19-Aug-2005 Ramananda for bug#4562793. File Version 120.6
110 1) Moved the Cursor - c_ja_in_tax_codes, up from below the cursor c_po_vendor_sites_all
111 2) Changed the parameters being passed to cursor - c_po_vendors and c_po_vendor_sites_all
112 3) In the procedure maintain_thhold_grps, while updating the table - jai_ap_tds_thhold_grps,
113 changed the update for column - current_threshold_slab_id
114
115 Dependency Due to this Bug
116 --------------------------
117 No
118
119 16. 19-Aug-2005 Ramananda for bug#4562801. File Version 120.6
120 Following changes are done in procedure - generate_tds_invoices
121 1) While inserting into table ja_in_ap_tds_invoices, value of column - invoice_amount is changed
122 2) Calculation for the new added variable - ln_invoice_amount is done
123
124 17. 23-Aug-2005 Bug 4559756. Added by Lakshmi Gopalsami Version 120.7
125 Added org_id in call to ap_utilities_pkg to get the correct gl_date and period_name.
126
127 18. 02-Sep-2005 Ramananda for Bug#4584221, File Version 120.8
128 Made the following changes -
129 1) Before submitting the request - APXIIMPT,
130 called the jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id) to get the batch_name.
131 2) In submitting the request - APXIIMPT,
132 changed the parameter batch_name from hardcoded value to variable - lv_batch_name
133
134 Dependency Due to this Bug (Functional)
135 --------------------------
136 jai_ap_utils.pls (120.2)
137 jai_ap_utils.plb (120.2)
138 jai_ap_tds_old.plb (120.3)
139 jai_constants.pls (120.3)
140 jaiorgdffsetup.sql (120.2)
141 jaivmlu.ldt (120.3)
142
143 19. 02-sep-2005 Bug 4774647. Added by Lakshmi Gopalsami version 120.9
144 Passed operating unit also as this parameter
145 has been added by base.
146
147 20. 07-Dec-2005 Bug 4870243. Added by Harshita version 120.11
148 Issue : Invoice Distribution Cursor has no filter based on the Invoice_distribution_id ,
149 line_num and tds_section.
150 Fix : Added the filter conditions in the filter.
151
152 21. 13-Jan-2006 Bug 4943949 Added by Lakshmi Gopalsami 120.13
153 Issue:
154 ------
155 Wrong number of arguments while trying to validate
156 the standard invoice. This is due to the parameter
157 P_FUNDS_RETURN_CODE added by base in ap_approval_pkg.
158
159 Fix:
160 ----
161 Added the parameter P_FUNDS_RETURN_CODE in call to
162 ap_approval_pkg.
163 22. 19-Jan-2006 avallabh for bug 4926736. File version 120.14
164 Removed the procedure process_tds_batch, since it is no longer used.
165
166 23. 27/03/2006 Hjujjuru for Bug 5096787 , File Version 120.15
167 Spec changes have been made in this file as a part og Bug 5096787.
168 Now, the r12 Procedure/Function specs is in this file are in
169 sync with their corrsponding 11i counterparts
170
171 24. 03/11/2006 Sanjikum for Bug#5131075, File Version 120.17
172 1) Changes are done for forward porting of bugs - 4722011, 4718907, 4685754, 5346558
173
174 Dependency Due to this Bug
175 --------------------------
176 Yes, as Package spec is changed and there are multiple files changed as part of current
177 25 23/02/07 bduvarag for bug#4716884,File version 120.18
178 Forward porting the changes done in 11i bug 4629783
179 bduvarag for bug#4667681,File version 120.18
180 Forward porting the changes done in 11i bug 4576084
181
182
183 26. 03/05/2007 Bug 5722028. Added by csahoo 120.19
184 Forward Porting to R12
185 Added parameter p_creation_date for the follownig procedures
186 process_tds_at_inv_validate
187 maintain_thhold_grps
188 and pd_creation_date in generate_tds_invoices.
189 Added global variables
190 gn_tds_rounding_factor
191 gd_tds_rounding_effective_date and function get_rnded_value
192 is created.
193
194 updated jai_ap_tdS_inv_taxes and jai_ap_tds_thhold_grps
195 withe the rounded values. This is done in procedure
196 process_tds_at_inv_validate and maintain_thhold_grps.
197 In generate_tds_invoices derived the logic for rounding.
198 Added conditions in queries for fetching the taxable
199 amount in procedure process_threshold_transition and
200 process_threshold_rollback. Added the parameters p_creation_date
201 or pd_creation_date wherever required.
202
203 Depedencies:
204 =============
205 jai_ap_tds_gen.pls - 120.5
206 jai_ap_tds_gen.plb - 120.19
207 jai_ap_tds_ppay.pls - 120.2
208 jai_ap_tds_ppay.plb - 120.5
209 jai_ap_tds_can.plb - 120.6
210
211 27. 22/06/2007 Bug# 6119216, File Version 120.20
212 Issue: RTN DOCS ARE NOT GENERATED ON APPLICATION OF PREPAYMENT INVOICE
213 Fix:
214 1. Changed where clause of the cursor c_check_not_validate.
215 2. Changed import_and_approve procedure, here a call to fnd_request.submit_request was passing
216 p_invoice_id instead of lv_group_id
217
218 28. 11/01/2008 Changes done by nprashar for bug # 6720018.
219 Issue# APAC:PEN:R12:INDIA LOCALIZATION VALIDATING FUNCTION OF TDS INVOICE NOT WORKING.
220
221 29. 18/11/2008 Bgowrava for Bug#4549019, File Version 120.16.12000000.12, 120.26.12010000.4, 120.31
222 Changes done in procedure - generate_tds_invoices
223 1) Changed the condition - if lv_invoice_to_tds_num is not null and lv_invoice_to_tds_type is not null then
224 2) Added an if condition before calling - jai_ap_interface_pkg.insert_ap_inv_interface for Supplier invoice
225
226 30. 26-Nov-2008 Bgowrava for Bug#7389849, File Version 120.16.12000000.13, 120.26.12010000.5, 120.32
227 modified code to check the enddate of a tax with the invoice date of an invoice rather than sysdate
228
229 ---------------------------------------------------------------------------- */
230
231 procedure status_update_chk_validate
232 (
233 p_invoice_id in number,
234 p_invoice_line_number in number default null, /* AP lines uptake */
238 p_process_flag out nocopy varchar2,
235 p_invoice_distribution_id in number default null,
236 p_match_status_flag in varchar2 default null,
237 p_is_invoice_validated out nocopy varchar2,
239 p_process_message out nocopy varchar2,
240 p_codepath in out nocopy varchar2
241 )
242 is
243
244 lv_section_type VARCHAR2(15) ;
245
246
247 cursor c_check_not_validate(p_invoice_id number, p_section_type VARCHAR2 ) is
248 select count(tds_inv_tax_id) total_count, sum(decode(match_status_flag, 'A', 1, 0)) validated_count
249 from jai_ap_tds_inv_taxes
250 where invoice_id = p_invoice_id
251 -- Harshita for Bug 4870243
252 and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, invoice_line_number)
253 and invoice_distribution_id = nvl(p_invoice_distribution_id, invoice_distribution_id) -- Bug 6119216
254 and section_type = p_section_type ;
255
256
257 /*select tds_inv_tax_id
258 from jai_ap_tds_inv_taxes
259 where invoice_id = p_invoice_id
260 and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
261 and nvl(invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
262 and section_type = p_section_type; */
263
264
265
266 cursor c_ap_holds_all(p_invoice_id number) is
267 select count(invoice_id)
268 from ap_holds_all
269 where invoice_id = p_invoice_id
270 and release_reason is null;
271
272
273 ln_total_count number;
274 ln_validated_cnt number;
275 ln_no_of_holds number;
276
277 begin
278
279 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_generation_pkg.status_update_chk_validate', 'START'); /* 1 */
280
281 if p_invoice_distribution_id is not null and p_match_status_flag is not null then
282 update jai_ap_tds_inv_taxes
283 set match_status_flag = p_match_status_flag
284 where invoice_id = p_invoice_id
285 and invoice_distribution_id = p_invoice_distribution_id;
286 end if;
287
288 ln_total_count := 0;
289 ln_validated_cnt := 0;
290
291 lv_section_type := 'TDS_SECTION' ; -- Harshita for Bug 4870243
292
293 open c_check_not_validate(p_invoice_id, lv_section_type); -- Harshita, added lv_section_type for Bug 4870243
294 fetch c_check_not_validate into ln_total_count, ln_validated_cnt;
295 close c_check_not_validate;
296
297 fnd_file.put_line(FND_FILE.LOG, ' Value of total cnt '|| ln_total_count);
298
299 fnd_file.put_line(FND_FILE.LOG, ' Value ofvalidated cnt '|| ln_validated_cnt);
300
301 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
302
303 if ln_total_count = ln_validated_cnt then
304 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
305
306 p_is_invoice_validated := 'Y';
307 else
308 p_is_invoice_validated := 'N';
309 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
310 end if;
311
312 if p_match_status_flag is not null then
313 /* Scenarios other than holds release */
314 open c_ap_holds_all(p_invoice_id);
315 fetch c_ap_holds_all into ln_no_of_holds;
316 close c_ap_holds_all;
317
318 if nvl(ln_no_of_holds, 0) > 0 then
319 p_is_invoice_validated := 'N';
320 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
321 end if;
322
323 end if;
324
325 fnd_file.put_line(FND_FILE.LOG, 'Status_update_chk_validate - Status of parent invoice '|| p_is_invoice_validated);
326
327 << exit_from_procedure >>
328 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath, null, 'END'); /* 6 */
329 return;
330
331 exception
332 when others then
333 p_process_flag := 'E';
334 P_process_message := 'jai_ap_tds_generation_pkg.status_update_chk_validate :' || sqlerrm;
335 return;
336 end status_update_chk_validate;
337
338 /* ************************************* process_invoice ************************************ */
339
340
341 /* ************************************* process_tds_at_inv_validate ************************************ */
342
343 procedure process_tds_at_inv_validate
344 (
345 p_invoice_id in number,
346 p_vendor_id in number,
347 p_vendor_site_id in number,
348 p_accounting_date in date,
349 p_invoice_currency_code in varchar2,
350 p_exchange_rate in number,
351 p_set_of_books_id in number,
352 p_org_id in number,
353 p_call_from in varchar2,
354 -- Bug 5722028. Added by Lakshmi Gopalsami
355 p_creation_date in date,
356 p_process_flag out nocopy varchar2,
357 p_process_message out nocopy varchar2,
361
358 p_codepath in out nocopy varchar2
359 )
360 is
362 cursor c_check_if_exists(p_invoice_id number) is
363 select count(tds_inv_tax_id)
364 from jai_ap_tds_inv_taxes
365 where invoice_id = p_invoice_id
366 and (actual_tax_id is not null or default_tax_id is not null);
367
368 cursor c_check_if_processed(p_invoice_id number,p_process_status jai_ap_tds_inv_taxes.process_status%type) is
369 select count(tds_inv_tax_id)
370 from jai_ap_tds_inv_taxes
371 where invoice_id = p_invoice_id
372 and process_status = p_process_status;
373
374 cursor c_calculate_tax(p_invoice_id number) is
375 select tds_inv_tax_id, actual_tax_id, amount
376 from jai_ap_tds_inv_taxes
377 where invoice_id = p_invoice_id
378 and actual_tax_id is not null;
379
380 cursor c_ja_in_tax_codes(p_tax_id number) is
381 select tax_rate,
382 section_code,
383 end_date,
384 sysdate,
385 'Tax : ' || tax_name || ' is end dated as on ' || to_char(end_date, 'dd-mon-yyyy') ||
386 '. Setup needs modification.' tax_end_dated_message
387 from JAI_CMN_TAXES_ALL
388 where tax_id = p_tax_id;
389
390 cursor c_for_each_tds_section(p_invoice_id number, p_exchange_rate number,p_section_type jai_ap_tds_inv_taxes.section_type%type) is--rchandan for bug#4428980
391 select actual_section_code, sum(amount*p_exchange_rate) invoice_amount, sum(tax_amount) section_amount
392 from jai_ap_tds_inv_taxes
393 where invoice_id = p_invoice_id
394 and section_type = p_section_type --rchandan for bug#4428980
395 and actual_section_code is not null
396 group by actual_section_code
397 having sum(tax_amount) <> 0;
398
399 cursor c_po_vendors(p_vendor_id number) is
400 select tds_vendor_type_lookup_code
401 from JAI_AP_TDS_VNDR_TYPE_V
402 where vendor_id = p_vendor_id;
403
404 cursor c_get_threshold
405 (p_vendor_id number, p_vendor_site_id number, p_tds_section_code varchar2,p_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
406 select threshold_hdr_id
407 from JAI_AP_TDS_TH_VSITE_V
408 where vendor_id = p_vendor_id
409 and vendor_site_id = p_vendor_site_id
410 and section_type = p_section_type --rchandan for bug#4428980
411 and section_code = p_tds_section_code;
412
413 cursor c_get_threshold_group
414 (p_vendor_id number, p_tan_no varchar2, p_pan_no varchar2, p_tds_section_code varchar2 , p_fin_year number,p_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
415 select threshold_grp_id
416 from jai_ap_tds_thhold_grps
417 where vendor_id = p_vendor_id
418 and section_type = p_section_type --rchandan for bug#4428980
419 and section_code = p_tds_section_code
420 and org_tan_num = p_tan_no
421 and vendor_pan_num = p_pan_no
422 and fin_year = p_fin_year;
423
424 cursor c_jai_ap_tds_thhold_grps(p_threshold_grp_id number) is
425 select (
426 nvl(total_invoice_amount, 0) -
427 nvl(total_invoice_cancel_amount, 0) -
428 nvl(total_invoice_apply_amount, 0) +
429 nvl(total_invoice_unapply_amount, 0)
430 )
431 total_invoice_amount,
432 total_tax_paid,
433 total_thhold_change_tax_paid,
434 current_threshold_slab_id
435 from jai_ap_tds_thhold_grps
436 where threshold_grp_id = p_threshold_grp_id;
437
438
439 cursor c_jai_ap_tds_thhold_slabs
440 ( p_threshold_hdr_id number, p_threshold_type varchar2, p_amount number) is
441 select threshold_slab_id, threshold_type_id, from_amount, to_amount
442 from jai_ap_tds_thhold_slabs
443 where threshold_hdr_id = p_threshold_hdr_id
444 and threshold_type_id in
445 ( select threshold_type_id
446 from jai_ap_tds_thhold_types
447 where threshold_hdr_id = p_threshold_hdr_id
448 and threshold_type = p_threshold_type
449 /* Bug 4522540. Added by Lakshmi Gopalsami
450 Added the following date condition */
451 and trunc(p_accounting_Date) between from_date
452 and nvl(to_date, p_accounting_date + 1)
453 )
454 and p_amount between from_amount and nvl(to_amount, p_amount)
455 order by from_amount asc;
456
457
458 /*following cursor added for FP bug 6345725 - need to check if there are any active slab(s) defined */
459 cursor c_check_slabs_end_dated (p_threshold_hdr_id number) is
460 select 1
461 from jai_ap_tds_thhold_types
462 where threshold_hdr_id = p_threshold_hdr_id
463 and trunc(p_accounting_Date) between from_date and nvl(to_date, p_accounting_date + 1);
464
465 ln_check_slab_exists NUMBER;
466
467 cursor c_get_taxes_to_generate_tds
468 (p_invoice_id number, p_tds_section_code varchar2, p_generate_all_invoices varchar2,
469 p_exchange_rate number, p_threshold_slab_id_single number,p_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
470 select actual_tax_id,
474 where invoice_id = p_invoice_id
471 sum(amount*p_exchange_rate) taxable_amount,
472 sum(tax_amount) tax_amount
473 from jai_ap_tds_inv_taxes
475 and section_type = p_section_type --rchandan for bug#4428980
476 and actual_section_code = p_tds_section_code
477 and (
478 (p_generate_all_invoices = 'Y' )
479 or
480 (p_threshold_slab_id_single > 0 )
481 )
482 group by actual_tax_id;
483
484 cursor c_get_vendor_pan_tan(p_vendor_id number , p_vendor_site_id number) is
485 select c.pan_no pan_no,
486 d.org_tan_num tan_no
487 from po_vendors a,
488 po_vendor_sites_all b,
489 JAI_AP_TDS_VENDOR_HDRS c,
490 jai_ap_tds_org_tan_v d --rchandan for bug#4323338
491 where a.vendor_id = b.vendor_id
492 and b.vendor_id = c.vendor_id
493 and b.vendor_site_id = c.vendor_site_id
494 and b.org_id = d.organization_id
495 and a.vendor_id = p_vendor_id
496 and b.vendor_site_id = p_vendor_site_id;
497
498
499 lv_attr_code VARCHAR2(25);
500 lv_attr_type_code VARCHAR2(25);
501 lv_tds_regime VARCHAR2(25);
502 lv_regn_type_others VARCHAR2(25);
503
504 cursor c_get_fin_year(p_accounting_date date, p_org_id number) is
505 select fin_year
506 from JAI_AP_TDS_YEARS
507 where tan_no in /* where clause and subquery added by ssumaith - bug# 4448789*/
508 (
509 SELECT attribute_value
510 FROM JAI_RGM_ORG_REGNS_V
511 WHERE regime_code = lv_tds_regime
512 AND registration_type = lv_regn_type_others
513 AND attribute_type_code = lv_attr_type_Code
514 AND attribute_code = lv_attr_code
515 AND organization_id = p_org_id
516 )
517 and p_accounting_date between start_date and end_date;
518
519 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
520 select currency_code
521 from gl_sets_of_books
522 where set_of_books_id = cp_set_of_books_id;
523
524 cursor c_get_non_tds_section_tax (p_invoice_id number, p_exchange_rate number,p_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
525 select section_type,
526 actual_tax_id,
527 sum(amount*p_exchange_rate) taxable_amount,
528 sum(tax_amount) tax_amount
529 from jai_ap_tds_inv_taxes
530 where invoice_id = p_invoice_id
531 and section_type <> p_section_type --rchandan for bug#4428980
532 and actual_tax_id is not null
533 group by section_type, actual_tax_id;
534
535
536 r_jai_ap_tds_thhold_grps c_jai_ap_tds_thhold_grps%rowtype;
537 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
538 r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
539
540 ln_count number:= 0;
541 ln_cnt_already_processed number:= 0;
542 ln_tax_id number;
543 ln_tax_amount number;
544 ln_threshold_grp_id number;
545 lv_vendor_type_lookup_code po_vendors.vendor_type_lookup_code%type;
546 ln_threshold_hdr_id number;
547 r_jai_ap_tds_thhold_slabs c_jai_ap_tds_thhold_slabs%rowtype;
548 ln_total_invoice_amount number;
549 ln_threshold_slab_id_before number;
550 ln_threshold_slab_id_after number;
551 ln_threshold_slab_id_single number;
552 lv_generate_all_invoices varchar2(1);
553 ln_threshold_trx_id number;
554 lv_tds_invoice_num ap_invoices_all.invoice_num%type;
555 lv_tds_cm_num ap_invoices_all.invoice_num%type;
556
557 lv_pan_no JAI_AP_TDS_VENDOR_HDRS.pan_no%type;
558 lv_tan_no jai_ap_tds_org_tan_v.org_tan_num %type; --rchandan for bug#4323338
559 ln_exchange_rate number;
560 ln_fin_year JAI_AP_TDS_YEARS.fin_year%type;
561 lv_slab_transition_tds_event jai_ap_tds_thhold_trxs.tds_event%type;
562
563 ln_no_of_tds_inv_generated number := 0;
564 lb_result boolean;
565 ln_req_id number;
566 ln_start_threshold_trx_id number;
567 ln_threshold_grp_audit_id number;
568 lv_tds_section_type CONSTANT varchar2(30) := 'TDS_SECTION'; --rchandan for bug#4428980
569 -- Bug 5722028. Added by Lakshmi Gopalsami
570 ln_tmp_tds_amt number;
571
572 sqlbuf VARCHAR2(1996);
573
574
575 begin
576
577 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_generation_pkg.process_tds_at_inv_validate', 'START'); /* 1 */
578 open c_check_if_exists(p_invoice_id);
579 fetch c_check_if_exists into ln_count;
580 close c_check_if_exists;
581
582 fnd_file.put_line(FND_FILE.LOG, '1. Check for tax count'|| ln_count);
583
584 if nvl(ln_count, 0) = 0 then
585 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
589 goto exit_from_procedure;
586 fnd_file.put_line(FND_FILE.LOG, '2. TDS tax is not applicable');
587 p_process_flag := 'X';
588 p_process_message := ' TDS tax is not applicable';
590 end if;
591
592 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
593 open c_check_if_processed(p_invoice_id,'P');
594 fetch c_check_if_processed into ln_cnt_already_processed;
595 close c_check_if_processed;
596
597 fnd_file.put_line(FND_FILE.LOG, '3. Check for processed already '|| ln_cnt_already_processed);
598
599 if nvl(ln_cnt_already_processed, 0) > 0 then
600 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
601 p_process_flag := 'X';
602 p_process_message := 'TDS invoices have already been processed for this invoice';
603 goto exit_from_procedure;
604 end if;
605
606
607 /* Update actual value from default value if actual is null for TDS section taxes only*/
608 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
609 update jai_ap_tds_inv_taxes
610 set actual_tax_id = default_tax_id
611 where invoice_id = p_invoice_id
612 and actual_tax_id is null
613 and user_deleted_tax_flag IS NOT NULL AND user_deleted_tax_flag <> 'Y' -- nvl(user_deleted_tax_flag, 'N') <> 'Y'
614 and section_type = lv_tds_section_type; --rchandan for bug#4428980
615
616
617 /* Update processed for those cases where NO TDS has to be deducted for TDS section taxes only */
618 update jai_ap_tds_inv_taxes
619 set process_status = 'P'
620 where invoice_id = p_invoice_id
621 and section_type = lv_tds_section_type --rchandan for bug#4428980
622 and nvl(user_deleted_tax_flag, 'N') = 'Y';
623
624 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
625
626 open c_gl_sets_of_books(p_set_of_books_id);
627 fetch c_gl_sets_of_books into r_gl_sets_of_books;
628 close c_gl_sets_of_books;
629
630 if r_gl_sets_of_books.currency_code <> p_invoice_currency_code then
631 /* Foreign currency invoice */
632 p_codepath := jai_general_pkg.plot_codepath(6.1, p_codepath); /* 6.1 */
633 ln_exchange_rate := p_exchange_rate;
634 end if;
635
636 ln_exchange_rate := nvl(ln_exchange_rate, 1);
637
638 /* start Loop through and calculate taxes for taxes of all sections */
639 for cur_rec in c_calculate_tax(p_invoice_id) loop
640
641 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
642
643 r_ja_in_tax_codes := null;
644 open c_ja_in_tax_codes(cur_rec.actual_tax_id);
645 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
646 close c_ja_in_tax_codes;
647
648 if trunc(r_ja_in_tax_codes.end_date) < p_accounting_date then --trunc(r_ja_in_tax_codes.sysdate) then --commented by Bgowrava for Bug#7389849
649 p_codepath := jai_general_pkg.plot_codepath(7.1, p_codepath); /* 7.1 */
650 p_process_flag := 'E';
651 p_process_message := r_ja_in_tax_codes.tax_end_dated_message;
652 goto exit_from_procedure;
653 end if;
654
655 ln_tax_amount := cur_rec.amount * (r_ja_in_tax_codes.tax_rate/100 );
656 ln_tax_amount := ln_tax_amount * ln_exchange_rate;
657 ln_tax_amount := round(ln_tax_amount, 2);
658
659 /* Bug 5722028. Added by Csahoo
660 * Called the rounding function as we need to round depending on the
661 * TDS rounding setup. We have a separate column calc_tax_amount
662 * which has non-rounded value.
663 */
664 /* Bug 7280925. Added by Lakshmi Gopalsami
665 Commented the following code as this is being handled in
666 generate_tds_invoicse and maintain_thhold_Grps
667 If r_gl_sets_of_books.currency_code = p_invoice_currency_code then
668 ln_tmp_tds_amt := round(ln_tax_amount,g_inr_currency_rounding);
669 else
670 ln_tmp_tds_amt := round(ln_tax_amount,g_fcy_currency_rounding);
671 end if ;
672 */
673 /* Bug 7280925. Commented by Lakshmi Gopalsami
674 * Rounding to 10 is applicable per invoice
675 * and not on each distribution
676
677 IF trunc(p_creation_date) >=
678 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
679 ln_tmp_tds_amt := get_rnded_value(ln_tmp_tds_amt);
680 END IF;
681 */
682 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 value of tax_amount before update'||ln_tax_amount);
683 -- End for bug 5722028.
684 /* bug 7280925. Added by Lakshmi Gopalsami
685 * changed from ln_tmp_tds_amt to ln_tax_amount
686 */
687
688 update jai_ap_tds_inv_taxes
689 set tax_amount = ln_tax_amount, -- ln_tmp_tds_amt, -- Bug 5722028
690 actual_section_code = r_ja_in_tax_codes.section_code
691 where tds_inv_tax_id = cur_rec.tds_inv_tax_id;
692
693 end loop;
694 /* End Loop through and calculate taxes */
695
696
697 /* Get vendor_type_lookup_code */
698 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
699 open c_po_vendors(p_vendor_id);
700 fetch c_po_vendors into lv_vendor_type_lookup_code;
701 close c_po_vendors;
702
703 fnd_file.put_line(FND_FILE.LOG,' 8. TDS Vendor type '|| lv_vendor_type_lookup_code);
707 close c_get_vendor_pan_tan;
704 /* Get Pan number and Tan number for the vendor */
705 open c_get_vendor_pan_tan(p_vendor_id, p_vendor_site_id);
706 fetch c_get_vendor_pan_tan into lv_pan_no, lv_tan_no;
708
709 lv_attr_code := 'TAN NO';
710 lv_attr_type_code := 'PRIMARY';
711 lv_tds_regime := 'TDS';
712 lv_regn_type_others := 'OTHERS';
713
714 fnd_file.put_line(FND_FILE.LOG,' 8.1 Pan number-> '|| lv_pan_no);
715
716 fnd_file.put_line(FND_FILE.LOG,' 8.1 Tan number-> '|| lv_tan_no);
717
718 /* Get the fin year */
719 open c_get_fin_year(p_accounting_date, p_org_id);
720 fetch c_get_fin_year into ln_fin_year;
721 close c_get_fin_year;
722
723 fnd_file.put_line(FND_FILE.LOG,' 8.2 Fin Year -> '|| ln_fin_year);
724
725 /* Start Loop through for each tds section and process for TDS section taxes only */
726 /* This section is meant for threshold, specific to TDS section taxes only */
727 for cur_rec_section in c_for_each_tds_section(p_invoice_id, ln_exchange_rate,'TDS_SECTION') LOOP --rchandan for bug#4428980
728
729 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
730 ln_threshold_grp_id := 0;
731 ln_threshold_hdr_id := 0;
732 ln_threshold_slab_id_before := null;
733 ln_threshold_slab_id_after:= null;
734 ln_threshold_slab_id_single := null;
735
736 open c_get_threshold
737 (p_vendor_id , p_vendor_site_id , cur_rec_section.actual_section_code,'TDS_SECTION'); --rchandan for bug#4428980
738 fetch c_get_threshold into ln_threshold_hdr_id;
739 close c_get_threshold;
740
741 fnd_file.put_line(FND_FILE.LOG,' 9. Threshold hdr id-> '|| ln_threshold_hdr_id);
742
743 if nvl(ln_threshold_hdr_id, 0) = 0 then
744 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
745 fnd_file.put_line(FND_FILE.LOG, '10. Threshold is not defined for the
746 applicable TDS section '||
747 cur_rec_section.actual_section_code||
748 '- Error');
749 p_process_flag := 'E';
750 p_process_message := 'Error - Threshold is not defined for the applicable TDS section :' ||
751 cur_rec_section.actual_section_code ;
752 goto exit_from_procedure;
753 end if;
754
755 /* Get the threshold group id */
756 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
757 open c_get_threshold_group(p_vendor_id, lv_tan_no, lv_pan_no, cur_rec_section.actual_section_code, ln_fin_year,'TDS_SECTION'); --rchandan for bug#4428980
758 fetch c_get_threshold_group into ln_threshold_grp_id;
759 close c_get_threshold_group;
760
761 fnd_file.put_line(FND_FILE.LOG, '11. Threshold grp id ->'||ln_threshold_grp_id);
762
763 /* Get the threshold group details */
764 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
765 if nvl(ln_threshold_grp_id, 0) <> 0 then
766 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
767
768 r_jai_ap_tds_thhold_grps := null;
769
770 open c_jai_ap_tds_thhold_grps(ln_threshold_grp_id);
771 fetch c_jai_ap_tds_thhold_grps into r_jai_ap_tds_thhold_grps;
772 close c_jai_ap_tds_thhold_grps;
773
774 ln_total_invoice_amount := r_jai_ap_tds_thhold_grps.total_invoice_amount;
775
776 else
777 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
778
779 ln_total_invoice_amount := 0;
780
781 end if;
782
783 fnd_file.put_line(FND_FILE.LOG, '12. Total invoice amount -> '||
784 ln_total_invoice_amount);
785
786 /* Get the threshold position before this invoice impact */
787 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
788 open c_jai_ap_tds_thhold_slabs(ln_threshold_hdr_id , 'CUMULATIVE' , ln_total_invoice_amount);
789 fetch c_jai_ap_tds_thhold_slabs into r_jai_ap_tds_thhold_slabs;
790 close c_jai_ap_tds_thhold_slabs;
791
792 ln_threshold_slab_id_before := nvl(r_jai_ap_tds_thhold_slabs.threshold_slab_id, 0);
793
794 fnd_file.put_line(FND_FILE.LOG, '15. Threshold slab id before '||
795 ln_threshold_slab_id_before);
796
797 /* Get the threshold position after this invoice impact */
798 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath); /* 16 */
799 /* FP Bug 6345725 - Removed the assignments for p_codepath without ja_in_general_pkg*/
800 r_jai_ap_tds_thhold_slabs := null;
801 open c_jai_ap_tds_thhold_slabs
802 (ln_threshold_hdr_id , 'CUMULATIVE' , ln_total_invoice_amount + cur_rec_section.invoice_amount);
803 fetch c_jai_ap_tds_thhold_slabs into r_jai_ap_tds_thhold_slabs;
804 close c_jai_ap_tds_thhold_slabs;
805
806 ln_threshold_slab_id_after := nvl(r_jai_ap_tds_thhold_slabs.threshold_slab_id, 0);
807
808 /*start addition for FP bug 6345725 - check for active slabs. if there are no active*/
809 /*slabs throw an error message*/
810 ln_check_slab_exists := NULL;
811 open c_check_slabs_end_dated(ln_threshold_hdr_id);
812 fetch c_check_slabs_end_dated into ln_check_slab_exists;
813 if ln_check_slab_exists IS NULL THEN
817 end if;
814 p_process_flag := 'E';
815 p_process_message := 'There are no active thresholds defined for this vendor';
816 goto exit_from_procedure;
818 close c_check_slabs_end_dated;
819 /*end addition for bug 6345725*/
820
821
822 fnd_file.put_line(FND_FILE.LOG, '16. Threshold slab id after ->'||
823 ln_threshold_slab_id_after);
824 p_codepath := p_codepath || to_char(ln_threshold_slab_id_after) || '**'; /*Bug 4667681*/
825 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath); /* 17 */
826 if ln_threshold_slab_id_after <> 0 then
827 /* Threshold has reached */
828 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath); /* 18 */
829 lv_generate_all_invoices := 'Y';
830 else
831 lv_generate_all_invoices := 'N';
832 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath); /* 19 */
833 end if; /* if ln_threshold_slab_id_after <> 0 */
834
835 fnd_file.put_line(FND_FILE.LOG, '19. Generate invoices -> ' ||
836 lv_generate_all_invoices);
837
838 /* Check for Single Invoice threshold if cumulative has not been reached */
839 if lv_generate_all_invoices = 'N' then
840 /* Cumulative threshold not reached */
841 r_jai_ap_tds_thhold_slabs := null;
842 open c_jai_ap_tds_thhold_slabs(ln_threshold_hdr_id , 'SINGLE' , cur_rec_section.invoice_amount);
843 fetch c_jai_ap_tds_thhold_slabs into r_jai_ap_tds_thhold_slabs;
844 close c_jai_ap_tds_thhold_slabs;
845 ln_threshold_slab_id_single := nvl(r_jai_ap_tds_thhold_slabs.threshold_slab_id, 0);
846 end if;
847
848 /* Loop and generate invoices */
849
850 if nvl(ln_threshold_grp_id, 0) = 0 then
851
852 p_codepath := jai_general_pkg.plot_codepath(19.1, p_codepath); /* 19.1 */
853
854 fnd_file.put_line(FND_FILE.LOG, '19.1 Call maintain thhold grps ');
855
856 jai_ap_tds_generation_pkg.maintain_thhold_grps
857 (
858 p_threshold_grp_id => ln_threshold_grp_id,
859 p_vendor_id => p_vendor_id,
860 p_org_tan_num => lv_tan_no,
861 p_vendor_pan_num => lv_pan_no,
862 p_section_type => 'TDS_SECTION',
863 p_section_code => cur_rec_section.actual_section_code,
864 p_fin_year => ln_fin_year,
865 p_org_id => p_org_id,
866 p_trx_invoice_amount => cur_rec_section.invoice_amount,
867 p_tds_event => 'INVOICE VALIDATE',
868 p_invoice_id => p_invoice_id,
869 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
870 -- Bug 5722028. Added by CSahoo
871 p_creation_Date => p_creation_date,
872 p_process_flag => p_process_flag,
873 P_process_message => P_process_message,
874 p_codepath => p_codepath
875 );
876
877 fnd_file.put_line(FND_FILE.LOG, '19.1 Process flag '|| p_process_flag);
878 fnd_file.put_line(FND_FILE.LOG, '19.1 Process message '|| p_process_message);
879
880 else
881
882 p_codepath := jai_general_pkg.plot_codepath(19.2, p_codepath); /* 19.2 */
883
884 jai_ap_tds_generation_pkg.maintain_thhold_grps
885 (
886 p_threshold_grp_id => ln_threshold_grp_id,
887 p_trx_invoice_amount => cur_rec_section.invoice_amount,
888 p_tds_event => 'INVOICE VALIDATE',
889 p_invoice_id => p_invoice_id,
890 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
891 p_process_flag => p_process_flag,
892 P_process_message => P_process_message,
893 p_codepath => p_codepath
894 );
895
896 fnd_file.put_line(FND_FILE.LOG, '19.2 Process flag '|| p_process_flag);
897 fnd_file.put_line(FND_FILE.LOG, '19.2 Process message '|| p_process_message);
898
899 end if;
900
901 --Added by Sanjikum for Bug#5131075(4722011)
902 IF p_process_flag = 'E' THEN
903 p_codepath := jai_general_pkg.plot_codepath(19.3, p_codepath); /* 19.3 */
904 goto exit_from_procedure;
905 END IF;
906
907 /* Generate TDS invoices by taxes under the section */
908 for cur_rec in
909 c_get_taxes_to_generate_tds
910 (p_invoice_id , cur_rec_section.actual_section_code, lv_generate_all_invoices,
911 ln_exchange_rate, ln_threshold_slab_id_single,'TDS_SECTION') LOOP --rchandan for bug#4428980
912
913 p_codepath := jai_general_pkg.plot_codepath(20, p_codepath); /* 20 */
914
915
916 ln_threshold_trx_id := 0;
917 lv_tds_invoice_num := null;
918 lv_tds_cm_num := null;
919 p_process_flag := null;
920
921 fnd_file.put_line(FND_FILE.LOG, '20. Call generate tds invoices' );
922
926 (
923 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - tax amount while calling generate tds invoices '|| cur_rec.tax_amount);
924
925 jai_ap_tds_generation_pkg.generate_tds_invoices
927 pn_invoice_id => p_invoice_id ,
928 pn_threshold_hdr_id => ln_threshold_hdr_id ,
929 pn_taxable_amount => cur_rec.taxable_amount ,
930 pn_tax_amount => cur_rec.tax_amount ,
931 pn_tax_id => cur_rec.actual_tax_id ,
932 pd_accounting_date => p_accounting_date ,
933 pv_tds_event => 'INVOICE VALIDATE' ,
934 pn_threshold_grp_id => ln_threshold_grp_id ,
935 pv_tds_invoice_num => lv_tds_invoice_num ,
936 pv_cm_invoice_num => lv_tds_cm_num ,
937 pn_threshold_trx_id => ln_threshold_trx_id ,
938 -- Bug 5722028. Added by CSahoo
939 pd_creation_date => p_creation_date ,
940 p_process_flag => p_process_flag ,
941 p_process_message => p_process_message
942 );
943
944
945 if p_process_flag = 'E' then
946 fnd_file.put_line(FND_FILE.LOG, '20 Process flag '|| p_process_flag);
947 fnd_file.put_line(FND_FILE.LOG, '20 Process message '|| p_process_message);
948 p_codepath := jai_general_pkg.plot_codepath(21, p_codepath); /* 21 */
949 goto exit_from_procedure;
950 end if;
951
952 p_codepath := jai_general_pkg.plot_codepath(22, p_codepath); /* 22 */
953
954 if ln_start_threshold_trx_id is null then
955 ln_start_threshold_trx_id := ln_threshold_trx_id;
956 end if;
957
958 fnd_file.put_line(FND_FILE.LOG,' 22. start thhold trx id '||
959 ln_start_threshold_trx_id);
960
961 /* Bug 7280925. Added by Lakshmi Gopalsami -- can be removed
962 * Need to round the value before calling maintain_thhold_grps
963
964 IF trunc(p_creation_date) >=
965 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
966 ln_tmp_tds_amt := get_rnded_value(cur_rec.tax_amount);
967 END IF;
968 */
969
970 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value of tmp tds before maintain thhold grps'||ln_tax_amount);
971
972 /* Update the total tax amount for which invoice was raised */
973
974 /* bug 7280925. Added by Lakshmi Gopalsami
975 * changed from ln_tmp_tds_amt to ln_tax_amount
976 */
977 p_codepath := jai_general_pkg.plot_codepath(23, p_codepath); /* 23 */
978
979 jai_ap_tds_generation_pkg.maintain_thhold_grps
980 (
981 p_threshold_grp_id => ln_threshold_grp_id,
982 p_trx_tax_paid => ln_tax_amount,
983 p_tds_event => 'INVOICE VALIDATE',
984 p_invoice_id => p_invoice_id,
985 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
986 -- Bug 5722028. Added by Lakshmi Gopalsami
987 p_creation_date => p_creation_date,
988 p_process_flag => p_process_flag,
989 P_process_message => P_process_message,
990 p_codepath => p_codepath
991 );
992
993 fnd_file.put_line(FND_FILE.LOG, '23 Process flag '|| p_process_flag);
994 fnd_file.put_line(FND_FILE.LOG, '23 Process message '|| p_process_message);
995
996 --Added by Sanjikum for Bug#5131075(4722011)
997 IF p_process_flag = 'E' THEN
998 p_codepath := jai_general_pkg.plot_codepath(23.1, p_codepath); /* 23.1 */
999 goto exit_from_procedure;
1000 END IF;
1001
1002
1003 /* Punch threshold_trx_id in jai_ap_tds_inv_taxes */
1004 update jai_ap_tds_inv_taxes
1005 set threshold_trx_id = ln_threshold_trx_id,
1006 threshold_slab_id_single = ln_threshold_slab_id_single
1007 where invoice_id = p_invoice_id
1008 and section_type = lv_tds_section_type --rchandan for bug#4428980
1009 and actual_section_code = cur_rec_section.actual_section_code
1010 and actual_tax_id = cur_rec.actual_tax_id
1011 and (
1012 (lv_generate_all_invoices = 'Y' )
1013 or
1014 (ln_threshold_slab_id_single > 0)
1015 );
1016
1017 ln_no_of_tds_inv_generated := ln_no_of_tds_inv_generated + 2;
1018 /* TDS invoices are always generated in pair */
1019
1020 p_codepath := jai_general_pkg.plot_codepath(24, p_codepath); /* 24 */
1021 end loop;
1022 /* Loop and generate invoices */
1023
1024 p_codepath := jai_general_pkg.plot_codepath(25, p_codepath); /* 25 */
1025 update jai_ap_tds_inv_taxes
1026 set threshold_grp_id = ln_threshold_grp_id,
1027 threshold_hdr_id = ln_threshold_hdr_id,
1028 threshold_slab_id = ln_threshold_slab_id_after,
1029 process_status = 'P'
1030 where invoice_id = p_invoice_id
1031 and section_type = lv_tds_section_type --rchandan for bug#4428980
1035 /* Transition in threshold has happened */
1032 and actual_section_code = cur_rec_section.actual_section_code;
1033
1034 if ln_threshold_slab_id_before <> ln_threshold_slab_id_after then
1036 p_codepath := jai_general_pkg.plot_codepath(26, p_codepath); /* 26 */
1037
1038 --4407184
1039 lv_slab_transition_tds_event := 'THRESHOLD TRANSITION(from slab id -' || ln_threshold_slab_id_before ||
1040 'to slab id - ' || ln_threshold_slab_id_after || ')';
1041
1042 fnd_file.put_line(FND_FILE.LOG, '26. Call process transition ');
1043 fnd_file.put_line(FND_FILE.LOG, '26. Event is '|| lv_slab_transition_tds_event);
1044
1045 process_threshold_transition
1046 (
1047 p_threshold_grp_id => ln_threshold_grp_id,
1048 p_threshold_slab_id => ln_threshold_slab_id_after,
1049 p_invoice_id => p_invoice_id,
1050 p_vendor_id => p_vendor_id,
1051 p_vendor_site_id => p_vendor_site_id,
1052 p_accounting_date => p_accounting_date,
1053 p_tds_event => lv_slab_transition_tds_event,
1054 p_org_id => p_org_id,
1055 pv_tds_invoice_num => lv_tds_invoice_num,
1056 pv_cm_invoice_num => lv_tds_cm_num,
1057 p_process_flag => p_process_flag,
1058 p_process_message => p_process_message
1059 );
1060
1061 if p_process_flag = 'E' then
1062 fnd_file.put_line(FND_FILE.LOG, '27 Process flag '|| p_process_flag);
1063 fnd_file.put_line(FND_FILE.LOG, '27 Process message '|| p_process_message);
1064
1065 p_codepath := jai_general_pkg.plot_codepath(27, p_codepath); /* 27 */
1066 goto exit_from_procedure;
1067 end if;
1068 ln_no_of_tds_inv_generated := ln_no_of_tds_inv_generated + 2;
1069 p_codepath := jai_general_pkg.plot_codepath(28, p_codepath); /* 28 */
1070 end if;
1071
1072 p_codepath := jai_general_pkg.plot_codepath(29, p_codepath); /* 29 */
1073 end loop;
1074
1075 /* End Loop through for each tds section and process */
1076
1077 p_codepath := jai_general_pkg.plot_codepath(30, p_codepath); /* 30 */
1078 /* Check if any non-TDS Section taxes are applicable and generate invoices if required. */
1079 for cur_non_tds_rec in c_get_non_tds_section_tax(p_invoice_id, ln_exchange_rate,'TDS_SECTION') LOOP --rchandan for bug#4428980
1080
1081 p_codepath := jai_general_pkg.plot_codepath(31, p_codepath); /* 31 */
1082 ln_threshold_trx_id := null;
1083 lv_tds_invoice_num := null;
1084 lv_tds_cm_num := null;
1085 p_process_flag := null;
1086
1087 fnd_file.put_line(FND_FILE.LOG, '31 Call generate tds invoices ');
1088
1089 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - WCT Tax amt '||cur_non_tds_rec.tax_amount);
1090
1091 jai_ap_tds_generation_pkg.generate_tds_invoices
1092 (
1093 pn_invoice_id => p_invoice_id ,
1094 pn_threshold_hdr_id => ln_threshold_hdr_id ,
1095 pn_taxable_amount => cur_non_tds_rec.taxable_amount ,
1096 pn_tax_amount => cur_non_tds_rec.tax_amount ,
1097 pn_tax_id => cur_non_tds_rec.actual_tax_id ,
1098 pd_accounting_date => p_accounting_date ,
1099 pv_tds_event => 'INVOICE VALIDATE' ,
1100 pn_threshold_grp_id => null ,
1101 pv_tds_invoice_num => lv_tds_invoice_num ,
1102 pv_cm_invoice_num => lv_tds_cm_num ,
1103 pn_threshold_trx_id => ln_threshold_trx_id ,
1104 -- Bug 5722028. Added by csahoo
1105 pd_creation_date => p_creation_date ,
1106 p_process_flag => p_process_flag ,
1107 p_process_message => p_process_message
1108 );
1109
1110 if p_process_flag = 'E' then
1111 fnd_file.put_line(FND_FILE.LOG, '31 Process flag '|| p_process_flag);
1112 fnd_file.put_line(FND_FILE.LOG, '31 Process message '|| p_process_message);
1113
1114 p_codepath := jai_general_pkg.plot_codepath(32, p_codepath); /* 32 */
1115 goto exit_from_procedure;
1116 end if;
1117
1118 p_codepath := jai_general_pkg.plot_codepath(33, p_codepath); /* 33 */
1119
1120 if ln_start_threshold_trx_id is null then
1121 p_codepath := jai_general_pkg.plot_codepath(34, p_codepath); /* 34 */
1122 ln_start_threshold_trx_id := ln_threshold_trx_id;
1123 end if;
1124
1125 fnd_file.put_line(FND_FILE.LOG, '34. Start thhold trx id '|| ln_start_threshold_trx_id);
1126
1127 /* Punch threshold_trx_id in jai_ap_tds_inv_taxes */
1128 update jai_ap_tds_inv_taxes
1129 set threshold_trx_id = ln_threshold_trx_id,
1130 process_status = 'P' /*Bug 4667681*/
1131 where invoice_id = p_invoice_id
1132 and section_type = cur_non_tds_rec.section_type
1133 and actual_tax_id = cur_non_tds_rec.actual_tax_id;
1134
1135 end loop; /* cur_non_tds_rec */
1136
1137
1138 p_codepath := jai_general_pkg.plot_codepath(35, p_codepath); /* 35 */
1139
1140
1141 /* If the process is called from batch do not fire import request */
1145 if p_call_from <> 'BATCH' then
1142
1143 fnd_file.put_line(FND_FILE.LOG, '35. called from '|| p_call_from);
1144
1146 /* Not Called from Batch */
1147
1148 p_codepath := jai_general_pkg.plot_codepath(36, p_codepath); /* 36 */
1149
1150 if ln_start_threshold_trx_id is not null then
1151
1152 fnd_file.put_line(FND_FILE.LOG, '36 start thhold trx id '||
1153 ln_start_threshold_trx_id);
1154
1155 p_codepath := jai_general_pkg.plot_codepath(37, p_codepath); /* 37 */
1156 import_and_approve
1157 (
1158 p_invoice_id => p_invoice_id,
1159 p_start_thhold_trx_id => ln_start_threshold_trx_id,
1160 p_tds_event => 'INVOICE VALIDATE',
1161 p_process_flag => p_process_flag,
1162 p_process_message => p_process_message
1163 );
1164
1165 fnd_file.put_line(FND_FILE.LOG, '37 Process flag '|| p_process_flag);
1166 fnd_file.put_line(FND_FILE.LOG, '37 Process message '|| p_process_message);
1167
1168 --Added by Sanjikum for Bug#5131075(4722011)
1169 IF p_process_flag = 'E' THEN
1170 p_codepath := jai_general_pkg.plot_codepath(37.1, p_codepath); /* 37.1 */
1171 goto exit_from_procedure;
1172 END IF;
1173
1174 end if; /* if ln_no_of_tds_inv_generated > 0 then */
1175
1176 end if; /* p_call_from <> 'BATCH' then */
1177
1178 <<exit_from_procedure>>
1179 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath); /* 100 */
1180 return;
1181
1182 exception
1183 when others then
1184 null;
1185 end process_tds_at_inv_validate;
1186 /* ************************************* process_tds_at_inv_validate ************************************ */
1187
1188 /* *********************************** procedure generate_tds_invoices ********************************** */
1189
1190 procedure generate_tds_invoices
1191 (
1192 pn_invoice_id in number,
1193 pn_invoice_line_number in number default null, /* AP lines */
1194 pn_invoice_distribution_id in number default null, /* Prepayment apply / unapply scenario */
1195 pv_invoice_num_prepay_apply in varchar2 default null, /* Prepayment application secanrio */
1196 pv_invoice_num_to_tds_apply in varchar2 default null, /* Prepayment unapplication secanrio */
1197 pv_invoice_num_to_vendor_apply in varchar2 default null, /* Prepayment unapplication secanrio */
1198 pv_invoice_num_to_vendor_can in varchar2 default null, /* Invoice Cancel Secnario */
1199 pn_threshold_hdr_id in number default null, /* For validate scenario only */
1200 pn_taxable_amount in number,
1201 pn_tax_amount in number,
1202 pn_tax_id in number,
1203 pd_accounting_date in date,
1204 pv_tds_event in varchar2,
1205 pn_threshold_grp_id in number,
1206 pv_tds_invoice_num out nocopy varchar2,
1207 pv_cm_invoice_num out nocopy varchar2,
1208 pn_threshold_trx_id out nocopy number,
1209 -- Bug 5722028. Added by csahoo
1210 pd_creation_date in date,
1211 p_process_flag out nocopy varchar2,
1212 p_process_message out nocopy varchar2
1213 )
1214 is
1215
1216 cursor c_ap_invoices_all(cp_invoice_id number) is
1217 select invoice_num,
1218 vendor_id,
1219 vendor_site_id,
1220 invoice_currency_code,
1221 exchange_rate_type,
1222 exchange_date,
1223 terms_id,
1224 payment_method_lookup_code,
1225 pay_group_lookup_code,
1226 invoice_date,
1227 goods_received_date,
1228 invoice_received_date,
1229 org_id,
1230 nvl(exchange_rate, 1) exchange_rate,
1231 set_of_books_id,
1232 payment_method_code -- Bug 7109056
1233 from ap_invoices_all
1234 where invoice_id = cp_invoice_id;
1235
1236 cursor c_po_vendor_sites_all(cp_vendor_id number, cp_vendor_site_id number) is
1237 select terms_id,
1238 --payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
1239 pay_group_lookup_code
1240 from po_vendor_sites_all
1241 where vendor_id = cp_vendor_id
1242 and vendor_site_id = cp_vendor_site_id;
1243
1244 cursor c_po_vendors(cp_vendor_id number) is
1245 select terms_id,
1246 --payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
1247 pay_group_lookup_code
1248 from po_vendors
1249 where vendor_id = cp_vendor_id;
1250
1251
1252 cursor c_ja_in_tax_codes (pn_tax_id number) is
1253 select section_code,
1254 vendor_id,
1255 vendor_site_id,
1256 tax_rate,
1260 from JAI_CMN_TAXES_ALL
1257 stform_type,
1258 tax_account_id,
1259 section_type
1261 where tax_id = pn_tax_id;
1262
1263
1264 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
1265 select currency_code
1266 from gl_sets_of_books
1267 where set_of_books_id = cp_set_of_books_id;
1268
1269 cursor c_get_ja_in_ap_inv_id is
1270 select to_char(JAI_AP_TDS_THHOLD_TRXS_S1.nextval)--to_char(JAI_AP_TDS_INVOICE_NUM_S.nextval)commented by rchandan for bug#4487676
1271 from dual;
1272
1273 cursor c_ap_payment_schedules_all(p_invoice_id number) is
1274 select payment_priority
1275 from ap_payment_schedules_all
1276 where invoice_id = p_invoice_id;
1277
1278 r_ap_invoices_all c_ap_invoices_all%rowtype;
1279 r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
1280 r_po_vendor_sites_all c_po_vendor_sites_all%rowtype;
1281 r_po_vendors c_po_vendors%rowtype;
1282 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
1283 r_ap_payment_schedules_all c_ap_payment_schedules_all%rowtype;
1284
1285
1286 lv_source varchar2(30); --File.Sql.35 Cbabu := 'TDS';
1287
1288 lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
1289 lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
1290
1291 lv_invoice_to_tds_type ap_invoices_all.invoice_type_lookup_code%type;
1292 lv_invoice_to_vendor_type ap_invoices_all.invoice_type_lookup_code%type;
1293
1294 ln_invoice_to_tds_id ap_invoices_all.invoice_id%type;
1295 ln_invoice_to_vendor_id ap_invoices_all.invoice_id%type;
1296
1297 ln_invoice_to_tds_line_id ap_invoice_lines_interface.invoice_line_id%type;
1298 ln_invoice_to_vendor_line_id ap_invoice_lines_interface.invoice_line_id%type;
1299
1300 lv_invoice_to_tds_line_type ap_invoice_distributions_all.line_type_lookup_code%type; --File.Sql.35 Cbabu := 'ITEM';
1301 lv_invoice_to_vendor_line_type ap_invoice_distributions_all.line_type_lookup_code%type; --File.Sql.35 Cbabu := 'ITEM';
1302
1303 ln_invoice_to_tds_amount number;
1304 ln_invoice_to_vendor_amount number;
1305
1306 ln_exchange_rate number;
1307 lv_this_procedure varchar2(50); --File.Sql.35 Cbabu := 'jaiap.generate_tds_invoice';
1308
1309 ln_terms_id po_vendors.terms_id%type;
1310 -- lv_payment_method_lookup_code po_vendors.payment_method_lookup_code%type; --commented by Sanjikum for Bug#4482462
1311 lv_pay_group_lookup_code po_vendors.pay_group_lookup_code%type;
1312
1313 lv_ja_in_ap_inv_id varchar2(15);
1314 ld_accounting_date date;
1315 lv_open_period ap_invoice_distributions_all.period_name%type;
1316 ln_tax_amount number;
1317
1318 lv_invoice_num ap_invoices_all.invoice_num%type;
1319 lv_source_attribute jai_ap_tds_invoices.source_attribute%TYPE ; --rchandan for bug#4428980
1320
1321 ln_invoice_amount ap_invoices_all.invoice_amount%TYPE; --Added by Ramananda for Bug#4562801
1322
1323 lv_group_id VARCHAR2(80); --Added by Sanjikum for Bug#5131075(4722011)
1324
1325 /* Bug 5722028. Added by Lakshmi Gopalsami
1326 * Added following variables
1327 */
1328 ln_tds_rnded_amt NUMBER;
1329 ln_tds_mod_value NUMBER;
1330 ln_tds_rnding_factor NUMBER;
1331
1332
1333 begin
1334
1335 lv_source := 'INDIA TDS'; /* --:= 'TDS'; --Ramanand for bug#4388958 */
1336 lv_invoice_to_tds_line_type := 'ITEM';
1337 lv_invoice_to_vendor_line_type := 'ITEM';
1338 lv_this_procedure := 'jaiap.generate_tds_invoice';
1339
1340
1341 /* Amount to be paid to TDS Authority should always be +ve */
1342 /* In case of prepayment application, this is still passed as +ve amount */
1343
1344 /* Bug 4513458. added by Lakshmi Gopalsami
1345 * Removed the rounding and assigned the exact amount
1346 * and the rounding is handled at later point to
1347 * accommodate the currency code
1348 */
1349 --ln_tax_amount := round(pn_tax_amount, 2);
1350 ln_tax_amount := pn_tax_amount;
1351
1352 /* Bug 4522507. Added by Lakshmi Gopalsami
1353 Checked whether round(ln_tax_amount) is less than
1354 zero instead of ln_tax_amount */
1355 if round(ln_tax_amount,2) <= 0 then
1356 p_process_flag := 'X';
1357 p_process_message := 'TDS amount must be greater than 0 ';
1358 goto exit_from_procedure;
1359 end if;
1360
1361 open c_ap_invoices_all(pn_invoice_id);
1362 fetch c_ap_invoices_all into r_ap_invoices_all;
1363 close c_ap_invoices_all;
1364
1365 /*
1366 || moved this up from the under the cursor - c_po_vendor_sites_all by Ramananda for Bug#4562793
1367 */
1368 open c_ja_in_tax_codes(pn_tax_id);
1369 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
1370 close c_ja_in_tax_codes;
1371
1372 /*
1373 || open c_po_vendors(r_ap_invoices_all.vendor_id);
1374 || Commented the above and added the below by Ramananda for Bug#4562793
1375 */
1376 open c_po_vendors(r_ja_in_tax_codes.vendor_id);
1377 fetch c_po_vendors into r_po_vendors;
1378 close c_po_vendors;
1379
1380 /*
1381 || open c_po_vendor_sites_all(r_ap_invoices_all.vendor_id, r_ap_invoices_all.vendor_site_id);
1385 fetch c_po_vendor_sites_all into r_po_vendor_sites_all;
1382 || Commented the above and added the below by sanjikum for Bug#4562793
1383 */
1384 open c_po_vendor_sites_all(r_ja_in_tax_codes.vendor_id, r_ja_in_tax_codes.vendor_site_id);
1386 close c_po_vendor_sites_all;
1387
1388 open c_gl_sets_of_books(r_ap_invoices_all.set_of_books_id);
1389 fetch c_gl_sets_of_books into r_gl_sets_of_books;
1390 close c_gl_sets_of_books;
1391
1392
1393 /* Get the payment details from the vendor site */
1394 ln_terms_id := r_po_vendor_sites_all.terms_id;
1395 -- lv_payment_method_lookup_code := r_po_vendor_sites_all.payment_method_lookup_code;--commented by Sanjikum for Bug#4482462
1396 lv_pay_group_lookup_code := r_po_vendor_sites_all.pay_group_lookup_code;
1397
1398
1399 if (
1400 ln_terms_id is null or
1401 -- lv_payment_method_lookup_code is null or --commented by Sanjikum for Bug#4482462
1402 lv_pay_group_lookup_code is null
1403 )
1404 then
1405
1406 /* Get the payment details from the vendor as it has been not defined for the site */
1407 ln_terms_id := r_po_vendors.terms_id;
1408 -- lv_payment_method_lookup_code := r_po_vendors.payment_method_lookup_code; --commented by Sanjikum for Bug#4482462
1409 lv_pay_group_lookup_code := r_po_vendors.pay_group_lookup_code;
1410
1411 end if;
1412
1413
1414 /* Get the unique number to suffix the tds invoices with */
1415 open c_get_ja_in_ap_inv_id;
1416 fetch c_get_ja_in_ap_inv_id into lv_ja_in_ap_inv_id;
1417 close c_get_ja_in_ap_inv_id;
1418
1419 lv_invoice_num := substr(r_ap_invoices_all.invoice_num, 1, 30);
1420
1421 /* Invoice Numbers, type for the invoice pair that is being created */
1422 if ( (pv_tds_event = 'INVOICE VALIDATE') or (pv_tds_event like 'THRESHOLD TRANSITION%') ) then
1423
1424 /* Standard invoice to TDS authority, Credit memo to supplier */
1425
1426 lv_invoice_to_tds_type := 'STANDARD';
1427 lv_invoice_to_vendor_type := 'CREDIT';
1428
1429 lv_invoice_to_tds_num := lv_invoice_num ||'-TDS-SI-'||lv_ja_in_ap_inv_id;
1430 lv_invoice_to_vendor_num := lv_invoice_num ||'-TDS-CM-'||lv_ja_in_ap_inv_id;
1431
1432 ln_invoice_to_tds_amount := ln_tax_amount;
1433 ln_invoice_to_vendor_amount := (-1) * ln_tax_amount;
1434
1435 elsif pv_tds_event = 'PREPAYMENT APPLICATION' OR pv_tds_event like 'THRESHOLD ROLLBACK%' then --Added by Sanjikum for Bug#5131075(4718907)
1436
1437 /* Credit memo to TDS authority, Standard invoice to supplier */
1438 if pv_invoice_num_prepay_apply is not null then
1439 lv_invoice_num := substr(pv_invoice_num_prepay_apply, 1, 30);
1440 end if;
1441
1442 lv_invoice_to_tds_type := 'CREDIT';
1443 lv_invoice_to_vendor_type := 'STANDARD';
1444
1445 lv_invoice_to_tds_num := lv_invoice_num ||'-RTN-TDS-CM-'||lv_ja_in_ap_inv_id;
1446 lv_invoice_to_vendor_num := lv_invoice_num ||'-RTN-TDS-SI-'||lv_ja_in_ap_inv_id;
1447
1448 ln_invoice_to_tds_amount := -1 * ln_tax_amount;
1449 ln_invoice_to_vendor_amount := ln_tax_amount;
1450
1451 elsif pv_tds_event = 'PREPAYMENT UNAPPLICATION' then
1452
1453 /* Standard invoice to TDS authority, Credit memo to supplier */
1454 lv_invoice_to_tds_type := 'STANDARD';
1455 lv_invoice_to_vendor_type := 'CREDIT';
1456
1457 if pv_invoice_num_to_tds_apply is not null then
1458 lv_invoice_to_tds_num := 'CAN/' || substr(pv_invoice_num_to_tds_apply, 1, 45);
1459 else
1460 lv_invoice_to_tds_num := lv_invoice_num ||'-RTN-TDS-SI-'||lv_ja_in_ap_inv_id;
1461 end if;
1462
1463 if pv_invoice_num_to_vendor_apply is not null then
1464 lv_invoice_to_vendor_num := 'CAN/' || substr(pv_invoice_num_to_vendor_apply, 1, 45);
1465 else
1466 lv_invoice_to_vendor_num := lv_invoice_num ||'-RTN-TDS-CM-'||lv_ja_in_ap_inv_id;
1467 end if;
1468
1469 ln_invoice_to_tds_amount := ln_tax_amount;
1470 ln_invoice_to_vendor_amount := (-1) * ln_tax_amount;
1471
1472 elsif pv_tds_event = 'INVOICE CANCEL' then
1473
1474 /* No invoice to TDS authority, Standard invoice to supplier */
1475
1476 lv_invoice_to_tds_num := null;
1477
1478 if pv_invoice_num_to_vendor_can is not null then
1479 lv_invoice_to_vendor_num := 'CAN/' || substr(pv_invoice_num_to_vendor_can, 1, 45);
1480 else
1481 lv_invoice_to_vendor_num := lv_invoice_num||'-CAN-TDS-SI-'||lv_ja_in_ap_inv_id;
1482 end if;
1483
1484 lv_invoice_to_tds_type := null;
1485 lv_invoice_to_vendor_type := 'STANDARD';
1486
1487 ln_invoice_to_tds_amount := null;
1488 ln_invoice_to_vendor_amount := ln_tax_amount;
1489
1490
1491 end if; /* TDS event type */
1492
1493 pv_tds_invoice_num := lv_invoice_to_tds_num;
1494 pv_cm_invoice_num := lv_invoice_to_vendor_num;
1495
1496 /* Check if the given date is in current open period */
1497
1498 /* Bug 4559756. Added by Lakshmi Gopalsami
1499 Added org_id to ap_utilities_pkg
1500 */
1501 lv_open_period:= ap_utilities_pkg.get_current_gl_date
1502 (pd_accounting_date,
1503 r_ap_invoices_all.org_id
1504 );
1505
1506 /* Bug 4559756. Added by Lakshmi Gopalsami
1507 Added org_id to ap_utilities_pkg
1508 */
1509
1513 (
1510 if lv_open_period is null then
1511
1512 ap_utilities_pkg.get_open_gl_date
1514 pd_accounting_date,
1515 lv_open_period,
1516 ld_accounting_date,
1517 r_ap_invoices_all.org_id
1518 );
1519
1520 if lv_open_period is null then
1521 p_process_flag := 'E';
1522 p_process_message := 'No open accounting Period after : ' || pd_accounting_date ;
1523 goto exit_from_procedure;
1524 end if;
1525
1526 else
1527 ld_accounting_date := pd_accounting_date;
1528 end if; /* ld_accounting_date */
1529
1530 --Added by Sanjikum for Bug#5131075(4722011)
1531 IF pv_tds_event = 'PREPAYMENT APPLICATION' OR pv_tds_event = 'PREPAYMENT UNAPPLICATION' THEN
1532 lv_group_id := to_char(pn_invoice_id)||pv_tds_event;
1533 ELSE
1534 lv_group_id := to_char(pn_invoice_id);
1535 END IF;
1536
1537
1538 /* Invoice to TDS Authority */
1539 /* Bug 4513458. Added by Lakshmi Gopalsami
1540 * Rounded the amount to zero as the TDS invoice amount should
1541 * be in INR currency */
1542
1543 -- ln_invoice_to_tds_amount := ROUND(ln_invoice_to_tds_amount,0);
1544 /* Bug 5722028. Added by csahoo
1545 * Rounded depending on the setup.
1546 */
1547 IF pv_tds_event NOT IN
1548 -- Bug 7280925. Commented by Lakshmi Gopalsami ('INVOICE CANCEL',
1549 ('PREPAYMENT UNAPPLICATION')
1550 THEN
1551 ln_invoice_to_tds_amount := ROUND(ln_invoice_to_tds_amount,g_inr_currency_rounding);
1552 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value before round '||ln_invoice_to_tds_amount);
1553 IF trunc(pd_creation_date) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)
1554 /* Bug 7280925. Added by Lakshmi Gopalsami
1555 * we should not round for WCT and ESSI. For those threshold_grp_id
1556 * will be null
1557 */
1558 and pn_threshold_grp_id is not null
1559 THEN
1560 ln_invoice_to_tds_amount := get_rnded_value(ln_invoice_to_tds_amount);
1561 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value after round per setup TDS auth inv'||ln_invoice_to_tds_amount);
1562 END IF;
1563 END IF ; -- pv_tds_event not in
1564
1565 -- End for Bug 5722028
1566
1567 --if lv_invoice_to_tds_num is not null and lv_invoice_to_tds_type is not null then
1568 --commented the above and added the below by bgowrava for bug#4549019
1569 if lv_invoice_to_tds_num is not null and lv_invoice_to_tds_type is not null and NVL(ln_invoice_to_tds_amount,0) <> 0 then
1570
1571 /* Generate the Invoice for the TDS authority - always in functional currency - INR */
1572
1573 jai_ap_utils_pkg.insert_ap_inv_interface
1574 (
1575 p_jai_source => lv_this_procedure,
1576 p_invoice_id => ln_invoice_to_tds_id,
1577 p_invoice_num => lv_invoice_to_tds_num,
1578 p_invoice_type_lookup_code => lv_invoice_to_tds_type,
1579 p_invoice_date => ld_accounting_date,
1580 p_vendor_id => r_ja_in_tax_codes.vendor_id,
1581 p_vendor_site_id => r_ja_in_tax_codes.vendor_site_id,
1582 p_invoice_amount => ln_invoice_to_tds_amount,
1583 p_invoice_currency_code => r_gl_sets_of_books.currency_code,
1584 p_exchange_rate => null,
1585 p_exchange_rate_type => null,
1586 p_exchange_date => null,
1587 p_terms_id => ln_terms_id,
1588 p_description => lv_invoice_to_tds_num,
1589 p_last_update_date => sysdate,
1590 p_last_updated_by => fnd_global.user_id,
1591 p_last_update_login => fnd_global.login_id,
1592 p_creation_date => sysdate,
1593 p_created_by => fnd_global.user_id,
1594 p_source => lv_source,
1595 p_voucher_num => lv_invoice_to_tds_num,
1596 --p_payment_method_lookup_code => lv_payment_method_lookup_code,
1597 --commented by Sanjikum for Bug#4482462
1598 p_pay_group_lookup_code => lv_pay_group_lookup_code,
1599 p_org_id => r_ap_invoices_all.org_id,
1600 p_attribute_category => 'India Original Invoice for TDS',
1601 p_attribute1 => pn_invoice_id,
1602 --added the below by Sanjikum for Bug#5131075(4722011)
1603 p_group_id => lv_group_id -- Bug# 6119216, changed to lv_group_id instead of to_char(p_invoice_id)
1604 );
1605
1606 /* Lines Interface */
1607 jai_ap_utils_pkg.insert_ap_inv_lines_interface
1608 (
1609 p_jai_source => lv_this_procedure,
1610 p_invoice_id => ln_invoice_to_tds_id,
1611 p_invoice_line_id => ln_invoice_to_tds_line_id,
1612 p_line_number => 1,
1613 p_line_type_lookup_code => lv_invoice_to_tds_line_type,
1614 p_amount => ln_invoice_to_tds_amount,
1615 p_accounting_date => ld_accounting_date,
1616 p_description => lv_invoice_to_tds_num,
1620 p_last_update_login => fnd_global.login_id,
1617 p_dist_code_combination_id => r_ja_in_tax_codes.tax_account_id,
1618 p_last_update_date => sysdate,
1619 p_last_updated_by => fnd_global.user_id,
1621 p_creation_date => sysdate,
1622 p_created_by => fnd_global.user_id
1623 );
1624
1625 end if; /* Invoice to TDS authority */
1626
1627
1628
1629 /* Invoice to Supplier */
1630
1631 if lv_invoice_to_vendor_num is not null and lv_invoice_to_vendor_type is not null then
1632
1633 /* Generate the TDS invoice for the supplier in supplier invoice currency */
1634
1635 /* Bug 5722028. Added by csahoo
1636 * Rounded depending on the setup.
1637 */
1638 IF pv_tds_event NOT IN
1639 -- Bug 7280925. Commented by Lakshmi Gopalsami ('INVOICE CANCEL',
1640 ('PREPAYMENT UNAPPLICATION') THEN
1641 if r_ap_invoices_all.invoice_currency_code <> r_gl_sets_of_books.currency_code then
1642 ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount / r_ap_invoices_all.exchange_rate, g_fcy_currency_rounding);
1643 ELSE
1644 ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount, g_inr_currency_rounding);
1645 end if;
1646 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value before round '||ln_invoice_to_vendor_amount);
1647 IF trunc(pd_creation_date) >=
1648 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)
1649 /* Bug 7280925. Added by Lakshmi Gopalsami
1650 * we should not round for WCT and ESSI. For those threshold_grp_id
1651 * will be null
1652 */
1653 and pn_threshold_grp_id is not null
1654 THEN
1655 ln_invoice_to_vendor_amount := get_rnded_value(ln_invoice_to_vendor_amount);
1656 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value after round per setup - vendor invoice'||ln_invoice_to_vendor_amount);
1657 END IF;
1658 END IF;
1659
1660 if upper(r_ap_invoices_all.exchange_rate_type) = 'USER' then
1661 ln_exchange_rate := r_ap_invoices_all.exchange_rate;
1662 else
1663 ln_exchange_rate := null;
1664 end if;
1665
1666 fnd_file.put_line(FND_FILE.LOG, ' CALL utils for inserting interface lines');
1667
1668 IF NVL(ln_invoice_to_vendor_amount,0) <> 0 THEN --condition added by bgowrava for bug#4549019
1669 /* Invoices Interface */
1670 jai_ap_utils_pkg.insert_ap_inv_interface
1671 (
1672 p_jai_source => lv_this_procedure,
1673 p_invoice_id => ln_invoice_to_vendor_id,
1674 p_invoice_num => lv_invoice_to_vendor_num,
1675 p_invoice_type_lookup_code => lv_invoice_to_vendor_type,
1676 p_invoice_date => r_ap_invoices_all.invoice_date,
1677 p_gl_date => ld_accounting_date,
1678 p_vendor_id => r_ap_invoices_all.vendor_id,
1679 p_vendor_site_id => r_ap_invoices_all.vendor_site_id,
1680 p_invoice_amount => ln_invoice_to_vendor_amount,
1681 p_invoice_currency_code => r_ap_invoices_all.invoice_currency_code,
1682 p_exchange_rate => ln_exchange_rate,
1683 p_exchange_rate_type => r_ap_invoices_all.exchange_rate_type,
1684 p_exchange_date => r_ap_invoices_all.exchange_date,
1685 p_terms_id => r_ap_invoices_all.terms_id,
1686 p_description => lv_invoice_to_vendor_num,
1687 p_last_update_date => sysdate,
1688 p_last_updated_by => fnd_global.user_id,
1689 p_last_update_login => fnd_global.login_id,
1690 p_creation_date => sysdate,
1691 p_created_by => fnd_global.user_id,
1692 p_source => lv_source,
1693 p_voucher_num => lv_invoice_to_vendor_num,
1694 -- Bug 7109056. Added by Lakshmi Gopalsami
1695 p_payment_method_code => r_ap_invoices_all.payment_method_code,
1696 --commented by Sanjikum for Bug#4482462
1697 p_pay_group_lookup_code => r_ap_invoices_all.pay_group_lookup_code,
1698 p_goods_received_date => r_ap_invoices_all.goods_received_date,
1699 p_invoice_received_date => r_ap_invoices_all.invoice_received_date,
1700 p_org_id => r_ap_invoices_all.org_id,
1701 p_attribute_category => 'India Original Invoice for TDS',
1702 p_attribute1 => pn_invoice_id,
1703 --commented the above and added the below by Sanjikum for Bug#5131075(4722011)
1704 p_group_id => lv_group_id
1705 );
1706
1707 /* Lines Interface */
1708 jai_ap_utils_pkg.insert_ap_inv_lines_interface
1709 (
1710 p_jai_source => lv_this_procedure,
1711 p_invoice_id => ln_invoice_to_vendor_id,
1712 p_invoice_line_id => ln_invoice_to_vendor_line_id,
1713 p_line_number => 1,
1714 p_line_type_lookup_code => lv_invoice_to_vendor_line_type,
1715 p_amount => ln_invoice_to_vendor_amount,
1719 p_last_update_date => sysdate,
1716 p_accounting_date => ld_accounting_date,
1717 p_description => lv_invoice_to_vendor_num,
1718 p_dist_code_combination_id => r_ja_in_tax_codes.tax_account_id,
1720 p_last_updated_by => fnd_global.user_id,
1721 p_last_update_login => fnd_global.login_id,
1722 p_creation_date => sysdate,
1723 p_created_by => fnd_global.user_id
1724 );
1725
1726 end if;
1727 end if; /* Invoice to Supplier */
1728
1729 /* Store the parent invoices payment priority as this is to be used in the credit memo generated for the supplier */
1730 open c_ap_payment_schedules_all(pn_invoice_id);
1731 fetch c_ap_payment_schedules_all into r_ap_payment_schedules_all;
1732 close c_ap_payment_schedules_all;
1733
1734 /* For downward compatibility with the pre-cleanup code */
1735 if ( (pv_tds_event = 'INVOICE VALIDATE') or (pv_tds_event like 'THRESHOLD TRANSITION%') ) then
1736
1737 /*
1738 || Added the IF-ELSE-ENDIF block by Ramananda for Bug#4562801
1739 */
1740 Fnd_File.put_line(Fnd_File.LOG, 'pn_taxable_amount '||pn_taxable_amount);
1741 IF pn_taxable_amount IS NOT NULL THEN
1742 ln_invoice_amount := pn_taxable_amount;
1743 ELSE
1744 ln_invoice_amount := ABS(round(pn_tax_amount * ( 100 / r_ja_in_tax_codes.tax_rate), 2));
1745 END IF;
1746
1747 IF r_ja_in_tax_codes.section_type = 'TDS_SECTION' THEN --rchandan for bug#4428980
1748 lv_source_attribute := 'ATTRIBUTE1';
1749 ELSIF r_ja_in_tax_codes.section_type = 'WCT_SECTION' THEN
1750 lv_source_attribute := 'ATTRIBUTE2';
1751 ELSIF r_ja_in_tax_codes.section_type = 'ESSI_SECTION' THEN
1752 lv_source_attribute := 'ATTRIBUTE3';
1753 END IF;
1754
1755 fnd_file.put_line(FND_FILE.LOG, ' invoice id '|| pn_invoice_id);
1756 fnd_file.put_line(FND_FILE.LOG, ' invoice amount'|| ln_invoice_amount);
1757 fnd_file.put_line(FND_FILE.LOG, 'invoice to tds inv num'|| lv_invoice_to_tds_num);
1758 fnd_file.put_line(FND_FILE.LOG, 'vendor num' ||lv_invoice_to_vendor_num);
1759 fnd_file.put_line(FND_FILE.LOG, 'tax id '||pn_tax_id);
1760 fnd_file.put_line(FND_FILE.LOG, 'tax rate'|| r_ja_in_tax_codes.tax_rate);
1761 fnd_file.put_line(FND_FILE.LOG, 'tds amt'||ln_invoice_to_tds_amount);
1762 fnd_file.put_line(FND_FILE.LOG, 'sec code '||r_ja_in_tax_codes.section_code);
1763 fnd_file.put_line(FND_FILE.LOG, 'stformtype '||r_ja_in_tax_codes.stform_type);
1764 fnd_file.put_line(FND_FILE.LOG, 'org id '|| r_ap_invoices_all.org_id);
1765 fnd_file.put_line(FND_FILE.LOG, 'src att'||lv_source_attribute);
1766
1767 IF NVL(ln_invoice_amount,0) <> 0 THEN --Added the condition by bgowrava for Bug#4549019
1768 insert into JAI_AP_TDS_INVOICES
1769 (TDS_INVOICE_ID,
1770 invoice_id,
1771 invoice_amount,
1772 tds_invoice_num,
1773 dm_invoice_num,
1774 tds_tax_id,
1775 tds_tax_rate,
1776 tds_amount,
1777 tds_section,
1778 certificate_number,
1779 --org_id,
1780 organization_id,
1781 source_attribute,
1782 /* Ramananda for bug#4560109 during R12 Sanity Testing. Added the WHO columns */
1783 created_by ,
1784 creation_date ,
1785 last_updated_by ,
1786 last_update_date ,
1787 last_update_login
1788 )
1789 values
1790 ( JAI_AP_TDS_INVOICES_S.nextval,
1791 pn_invoice_id,
1792 --round(ln_invoice_to_tds_amount * ( 100 / r_ja_in_tax_codes.tax_rate), 2),
1793 --commented the above and added the below by Ramananda for Bug#4562801
1794 ln_invoice_amount,
1795 lv_invoice_to_tds_num,
1796 lv_invoice_to_vendor_num,
1797 pn_tax_id,
1798 r_ja_in_tax_codes.tax_rate,
1799 ln_invoice_to_tds_amount,
1800 r_ja_in_tax_codes.section_code,
1801 r_ja_in_tax_codes.stform_type,
1802 --r_ap_invoices_all.org_id,
1803 r_ap_invoices_all.org_id,
1804 lv_source_attribute, --rchandan for bug#4428980
1805 /* Ramananda for bug#4560109 during R12 Sanity Testing. Added the WHO columns */
1806 fnd_global.user_id ,
1807 sysdate ,
1808 fnd_global.user_id ,
1809 sysdate ,
1810 fnd_global.login_id
1811 );
1812 end if;
1813 end if; /* Only for validate event as done in earlier regime */
1814
1815 -- Bug 5722028. Added by csahoo
1816 ln_tds_rnding_factor := 0;
1817 ln_tds_rnded_amt := pn_tax_amount;
1818 IF pv_tds_event NOT IN
1819 -- Bug 7280925. commented by Lakshmi Gopalsami ('INVOICE CANCEL',
1820 ('PREPAYMENT UNAPPLICATION') THEN
1821 IF r_ap_invoices_all.invoice_currency_code = r_gl_sets_of_books.currency_code THEN
1822 ln_tds_rnded_amt := ROUND(pn_tax_amount , g_inr_currency_rounding);
1823 ELSE
1824 ln_tds_rnded_amt := ROUND(pn_tax_amount / r_ap_invoices_all.exchange_rate, g_fcy_currency_rounding);
1825 END IF ;
1826 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value before round '||ln_tds_rnded_amt);
1827 IF (trunc(pd_creation_date) >=
1828 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)
1829 /* Bug 7280925. Added by Lakshmi Gopalsami
1833 and pn_threshold_grp_id is not null )
1830 * we should not round for WCT and ESSI. For those threshold_grp_id
1831 * will be null
1832 */
1834 THEN
1835 ln_tds_rnding_factor := jai_ap_tds_generation_pkg.gn_tds_rounding_factor;
1836 ln_tds_rnded_amt := get_rnded_value(ln_tds_rnded_amt);
1837 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value after round before insert into trxs'||ln_tds_rnded_amt);
1838 END IF;
1839 END IF;
1840 -- End for bug 5722028.
1841
1842
1843 insert into jai_ap_tds_thhold_trxs
1844 (
1845 threshold_trx_id ,
1846 invoice_id ,
1847 invoice_line_number ,
1848 invoice_distribution_id ,
1849 threshold_grp_id ,
1850 threshold_hdr_id ,
1851 tds_event ,
1852 tax_id ,
1853 tax_rate ,
1854 taxable_amount ,
1855 tax_amount ,
1856 tds_authority_vendor_id ,
1857 tds_authority_vendor_site_id ,
1858 invoice_to_tds_authority_num ,
1859 invoice_to_tds_authority_type ,
1860 invoice_to_tds_authority_curr ,
1861 invoice_to_tds_authority_amt ,
1862 vendor_id ,
1863 vendor_site_id ,
1864 invoice_to_vendor_num ,
1865 invoice_to_vendor_type ,
1866 invoice_to_vendor_curr ,
1867 invoice_to_vendor_amt ,
1868 parent_inv_payment_priority ,
1869 parent_inv_exchange_rate ,
1870 created_by ,
1871 creation_date ,
1872 last_updated_by ,
1873 last_update_date ,
1874 last_update_login ,
1875 tds_rounding_factor -- Bug 5722028. Added by csahoo
1876 )
1877 values
1878 (
1879 jai_ap_tds_thhold_trxs_s.nextval ,
1880 pn_invoice_id ,
1881 pn_invoice_line_number ,
1882 pn_invoice_distribution_id ,
1883 pn_threshold_grp_id ,
1884 pn_threshold_hdr_id ,
1885 pv_tds_event ,
1886 pn_tax_id ,
1887 r_ja_in_tax_codes.tax_rate ,
1888 pn_taxable_amount ,
1889 ln_tds_rnded_amt, --Bug 5722028. Added by csahoo
1890 r_ja_in_tax_codes.vendor_id ,
1891 r_ja_in_tax_codes.vendor_site_id ,
1892 lv_invoice_to_tds_num ,
1893 lv_invoice_to_tds_type ,
1894 r_gl_sets_of_books.currency_code ,
1895 ln_invoice_to_tds_amount ,
1896 r_ap_invoices_all.vendor_id ,
1897 r_ap_invoices_all.vendor_site_id ,
1898 lv_invoice_to_vendor_num ,
1899 lv_invoice_to_vendor_type ,
1900 r_ap_invoices_all.invoice_currency_code ,
1901 ln_invoice_to_vendor_amount ,
1902 r_ap_payment_schedules_all.payment_priority ,
1903 r_ap_invoices_all.exchange_rate ,
1904 fnd_global.user_id ,
1905 sysdate ,
1906 fnd_global.user_id ,
1907 sysdate ,
1908 fnd_global.login_id ,
1909 ln_tds_rnding_factor -- Bug 5722028. Added by csahoo
1910 )
1911 returning threshold_trx_id into pn_threshold_trx_id;
1912
1913 <<exit_from_procedure>>
1914 return;
1915
1916 exception
1917 when others then
1918 p_process_flag := 'E';
1919 p_process_message := 'Error from jai_ap_tds_generation_pkg.generate_tds_invoices :' || sqlerrm;
1920
1921 end generate_tds_invoices;
1922
1923 /* *********************************** procedure generate_tds_invoices ********************************** */
1924
1925 procedure process_threshold_transition
1926 (
1927 p_threshold_grp_id in number,
1928 p_threshold_slab_id in number,
1929 p_invoice_id in number,
1930 p_vendor_id in number,
1931 p_vendor_site_id in number,
1932 p_accounting_date in date,
1933 p_tds_event in varchar2,
1934 p_org_id in number,
1935 pv_tds_invoice_num out nocopy varchar2,
1936 pv_cm_invoice_num out nocopy varchar2,
1937 p_process_flag out nocopy varchar2,
1941
1938 p_process_message out nocopy varchar2
1939 )
1940 is
1942 cursor c_jai_ap_tds_thhold_taxes(p_threshold_slab_id number, p_org_id number) is
1943 select tax_id
1944 from jai_ap_tds_thhold_taxes
1945 where threshold_slab_id = p_threshold_slab_id
1946 and operating_unit_id = p_org_id;
1947
1948
1949 cursor c_jai_ap_tds_thhold_grps(cp_threshold_grp_id number) is
1950 select (
1951 nvl(total_invoice_amount, 0) -
1952 nvl(total_invoice_cancel_amount, 0) -
1953 nvl(total_invoice_apply_amount, 0) +
1954 nvl(total_invoice_unapply_amount, 0)
1955 )
1956 total_invoice_amount,
1957 total_tax_paid
1958 from jai_ap_tds_thhold_grps
1959 where threshold_grp_id = cp_threshold_grp_id;
1960
1961 cursor c_ja_in_tax_codes(cp_tax_id number) is
1962 select tax_rate
1963 from JAI_CMN_TAXES_ALL
1964 where tax_id = cp_tax_id;
1965
1966 CURSOR get_thhold_rollbk (cp_threshold_grp_id IN NUMBER )
1967 IS
1968 SELECT SUM(NVL(jattt.taxable_amount,0))
1969 FROM jai_ap_tds_thhold_trxs jattt
1970 WHERE jattt.threshold_grp_id = cp_threshold_grp_id
1971 AND (jattt.tds_event like 'THRESHOLD ROLLBACK%' OR
1972 -- Bug 5722028. Added by csahoo
1973 -- added the following condition
1974 jattt.tds_event like 'THRESHOLD TRANSITION%' );
1975
1976 lv_codepath jai_ap_tds_inv_taxes.codepath%type;
1977
1978
1979 r_jai_ap_tds_thhold_taxes c_jai_ap_tds_thhold_taxes%rowtype;
1980 r_jai_ap_tds_thhold_grps c_jai_ap_tds_thhold_grps%rowtype;
1981 r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
1982
1983 ln_thhold_transition_tax_amt number;
1984 lv_tds_invoice_num ap_invoices_all.invoice_num%type;
1985 lv_tds_cm_num ap_invoices_all.invoice_num%type;
1986 ln_threshold_trx_id number;
1987 ln_threshold_grp_audit_id number;
1988 ln_threshold_grp_id number;
1989
1990 begin
1991
1992 open c_jai_ap_tds_thhold_taxes(p_threshold_slab_id, p_org_id);
1993 fetch c_jai_ap_tds_thhold_taxes into r_jai_ap_tds_thhold_taxes;
1994 close c_jai_ap_tds_thhold_taxes;
1995
1996 open c_jai_ap_tds_thhold_grps(p_threshold_grp_id);
1997 fetch c_jai_ap_tds_thhold_grps into r_jai_ap_tds_thhold_grps;
1998 close c_jai_ap_tds_thhold_grps;
1999
2000 open c_ja_in_tax_codes(r_jai_ap_tds_thhold_taxes.tax_id);
2001 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
2002 close c_ja_in_tax_codes;
2003
2004 -- Bug 5722028. Added by csahoo
2005 -- Rounded depending on the TDS setup rounding.
2006
2007 ln_thhold_transition_tax_amt :=
2008 ROUND(r_jai_ap_tds_thhold_grps.total_invoice_amount * (r_ja_in_tax_codes.tax_rate/100),
2009 g_inr_currency_rounding);
2010
2011 IF trunc(sysdate) >=
2012 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
2013 ln_thhold_transition_tax_amt := get_rnded_value(ln_thhold_transition_tax_amt);
2014 END IF;
2015
2016 ln_thhold_transition_tax_amt := ln_thhold_transition_tax_amt - r_jai_ap_tds_thhold_grps.total_tax_paid;
2017
2018
2019 if ln_thhold_transition_tax_amt > 0 then
2020
2021
2022 jai_ap_tds_generation_pkg.generate_tds_invoices
2023 (
2024 pn_invoice_id => p_invoice_id ,
2025 pn_taxable_amount => null ,
2026 /* No taxable amount in case of threshold transition invoice */
2027 pn_tax_amount => ln_thhold_transition_tax_amt ,
2028 pn_tax_id => r_jai_ap_tds_thhold_taxes.tax_id ,
2029 pd_accounting_date => p_accounting_date ,
2030 pv_tds_event => p_tds_event ,
2031 pn_threshold_grp_id => p_threshold_grp_id ,
2032 pv_tds_invoice_num => lv_tds_invoice_num ,
2033 pv_cm_invoice_num => lv_tds_cm_num ,
2034 pn_threshold_trx_id => ln_threshold_trx_id ,
2035 pd_creation_date => sysdate, -- Bug 5722028. Added by csahoo
2036 p_process_flag => p_process_flag ,
2037 p_process_message => p_process_message
2038 );
2039
2040 if p_process_flag = 'E' then
2041 goto exit_from_procedure;
2042 end if;
2043
2044 /* Update the total tax amount for which invoice was raised */
2045 ln_threshold_grp_id:= p_threshold_grp_id;
2046 maintain_thhold_grps
2047 (
2048 p_threshold_grp_id => ln_threshold_grp_id,
2049 p_trx_tax_paid => ln_thhold_transition_tax_amt,
2050 p_trx_thhold_change_tax_paid => ln_thhold_transition_tax_amt,
2051 p_trx_threshold_slab_id => p_threshold_slab_id,
2052 p_tds_event => p_tds_event,
2053 p_invoice_id => p_invoice_id,
2054 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
2055 -- Bug 5722028. Added by Lakshmi Gopalsami
2056 p_creation_date => sysdate,
2057 p_process_flag => p_process_flag,
2058 P_process_message => P_process_message,
2059 p_codepath => lv_codepath
2060 );
2061
2065 END IF;
2062 --Added by Sanjikum for Bug#5131075(4722011)
2063 IF p_process_flag = 'E' THEN
2064 goto exit_from_procedure;
2066
2067 end if; /* ln_thhold_transition_tax_amt > 0 */
2068
2069
2070 <<exit_from_procedure>>
2071 return;
2072
2073 exception
2074 when others then
2075 p_process_flag := 'E';
2076 p_process_message := 'Error from jai_ap_tds_generation_pkg.process_threshold_transition :' || sqlerrm;
2077 end process_threshold_transition;
2078
2079 /* *********************************** procedure import_and_approve ********************************** */
2080
2081 procedure import_and_approve
2082 (
2083 p_invoice_id in number,
2084 p_start_thhold_trx_id in number,
2085 p_tds_event in varchar2,
2086 p_process_flag out nocopy varchar2,
2087 p_process_message out nocopy varchar2
2088 )
2089 is
2090
2091 cursor c_ap_invoices_all(p_invoice_id number) is
2092 select vendor_id,
2093 vendor_site_id
2094 from ap_invoices_all
2095 where invoice_id = p_invoice_id;
2096
2097 cursor c_ja_in_po_vendor_sites(p_vendor_id number, p_vendor_site_id number) is
2098 select nvl( approved_invoice_flag, 'N' ) approved_invoice_flag
2099 from JAI_CMN_VENDOR_SITES
2100 where vendor_id = p_vendor_id
2101 and vendor_site_id = p_vendor_site_id;
2102
2103
2104 r_ap_invoices_all c_ap_invoices_all%rowtype;
2105
2106 lb_result boolean;
2107 ln_import_request_id number;
2108 ln_approve_request_id number;
2109 lv_approved_invoice_flag JAI_CMN_VENDOR_SITES.approved_invoice_flag%type;
2110 lv_batch_name ap_batches_all.batch_name%TYPE; --added by Ramananda for Bug#4584221
2111 lv_group_id VARCHAR2(80); --Added by Sanjikum for Bug#5131075(4722011)
2112
2113 begin
2114 fnd_file.put_line (fnd_file.log, 'p_tds_event='||p_tds_event);
2115 --Added by Sanjikum for Bug#5131075(4722011)
2116 IF p_tds_event = 'PREPAYMENT APPLICATION' OR p_tds_event = 'PREPAYMENT UNAPPLICATION' THEN
2117 lv_group_id := to_char(p_invoice_id)||p_tds_event;
2118 ELSE
2119 lv_group_id := to_char(p_invoice_id);
2120 END IF;
2121
2122
2123 /* Invoke payables open interface */
2124
2125 lb_result := fnd_request.set_mode(true);
2126
2127 lv_batch_name := jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id); --Ramananda for bug#4584221
2128
2129 ln_import_request_id :=
2130 fnd_request.submit_request
2131 (
2132 'SQLAP',
2133 'APXIIMPT',
2134 'Import TDS invoices - ' || lower(p_tds_event),
2135 '',
2136 false,
2137 /* Bug 4774647. Added by Lakshmi Gopalsami
2138 Passed operating unit also as this parameter has been
2139 added by base .
2140 */
2141 '',
2142 'INDIA TDS', /*--'TDS', --Ramanand for bug#4388958*/
2143 --'',
2144 --Commented the above and added the below by Sanjikum for Bug#5131075(4722011)
2145 lv_group_id, -- Chaged from to_char(p_invoice_id) for bug# 6119216
2146 --'TDS'||TO_CHAR(TRUNC(SYSDATE)),
2147 --commented the above and added the below by Ramananda for Bug#4584221
2148 lv_batch_name,
2149 '',
2150 '',
2151 '',
2152 'Y',
2153 'N',
2154 'N',
2155 'N',
2156 1000,
2157 fnd_global.user_id,
2158 fnd_global.login_id
2159 );
2160
2161 /* Get vendor and site for the invoice */
2162 open c_ap_invoices_all(p_invoice_id);
2163 fetch c_ap_invoices_all into r_ap_invoices_all;
2164 close c_ap_invoices_all;
2165
2166 /* Check if Pre-approved TDS invoices setup has been set for the vendor */
2167
2168 /* Check for vendor and site */
2169 open c_ja_in_po_vendor_sites(r_ap_invoices_all.vendor_id, r_ap_invoices_all.vendor_site_id);
2170 fetch c_ja_in_po_vendor_sites into lv_approved_invoice_flag;
2171 close c_ja_in_po_vendor_sites;
2172
2173 if nvl(lv_approved_invoice_flag, 'N') <> 'Y' then
2174 /* Pre-approved TDS invoice is not set for vendor and site, Check for vendor and null site */
2175 open c_ja_in_po_vendor_sites(r_ap_invoices_all.vendor_id, 0);
2176 fetch c_ja_in_po_vendor_sites into lv_approved_invoice_flag;
2177 close c_ja_in_po_vendor_sites;
2178 end if;
2179
2180 if nvl(lv_approved_invoice_flag, 'N') <> 'Y' then
2181 /* Setup for pre-approved TDS invoice is not there for the vendor for site or null site. */
2182 goto exit_from_procedure;
2183 end if;
2184
2185
2186 /* Control comes here only when Pre-approved TDS invoice is setup for the vendor,
2187 we need to invoke the request for approval */
2188 lb_result := fnd_request.set_mode(true);
2189
2190 ln_approve_request_id :=
2191 fnd_request.submit_request
2192 (
2193 'JA',
2194 'JAITDSA',
2195 'Approval Of TDS Invoices ',
2196 sysdate,
2197 false,
2198 ln_import_request_id,
2199 p_invoice_id,
2200 r_ap_invoices_all.vendor_id,
2204
2201 r_ap_invoices_all.vendor_site_id,
2202 p_start_thhold_trx_id
2203 );
2205
2206 <<exit_from_procedure>>
2207 return;
2208
2209 exception
2210 when others then
2211 p_process_flag := 'E';
2212 p_process_message := 'Error from jai_ap_tds_generation_pkg.import_and_approve :' || sqlerrm;
2213 end import_and_approve;
2214
2215 /* *********************************** procedure import_and_approve ********************************** */
2216
2217 /* *********************************** procedure approve_tds_invoices ********************************** */
2218
2219 procedure approve_tds_invoices
2220 (
2221 errbuf out nocopy varchar2,
2222 retcode out nocopy varchar2,
2223 p_parent_request_id in number,
2224 p_invoice_id in number,
2225 p_vendor_id in number,
2226 p_vendor_site_id in number,
2227 p_start_thhold_trx_id in number
2228 )
2229 is
2230
2231 cursor c_jai_ap_tds_thhold_trxs
2232 (p_invoice_id number, p_start_thhold_trx_id number, p_vendor_id number, p_vendor_site_id number) is
2233 select invoice_to_tds_authority_id,
2234 invoice_to_vendor_id,
2235 invoice_to_tds_authority_num,
2236 invoice_to_vendor_num
2237 from jai_ap_tds_thhold_trxs
2238 where threshold_trx_id >= p_start_thhold_trx_id
2239 and invoice_id = p_invoice_id
2240 and vendor_id = p_vendor_id
2241 and vendor_site_id = p_vendor_site_id;
2242
2243 CURSOR c_jai_chk_tds_inv (p_invoice_id number) IS
2244 SELECT invoice_id, org_id,
2245 set_of_books_id -- bug 6819855. Added by Lakshmi Gopalsami
2246 FROM ap_invoices_all
2247 WHERE invoice_id = p_invoice_id;
2248
2249 lb_request_status boolean;
2250 lv_phase varchar2(100);
2251 lv_status varchar2(100);
2252 lv_dev_phase varchar2(100);
2253 lv_dev_status varchar2(100);
2254 lv_message varchar2(100);
2255
2256 ln_holds_count number;
2257 lv_approval_status varchar2(100);
2258 lv_conc_flag varchar2(10);
2259
2260 /* Bug 4872659. Added by Lakshmi Gopalsami */
2261 ln_tds_invoice_id NUMBER;
2262 ln_vendor_invoice_id NUMBER;
2263 ln_org_id NUMBER;
2264
2265 /* Bug 4943949. Added by Lakshmi gopalsami */
2266 lv_funds_ret_code varchar2(5);
2267
2268 /* Bug 6819855. Added by Lakshmi Gopalsami */
2269 ln_sob_id NUMBER;
2270 ln_holds_count1 NUMBER;
2271
2272 begin
2273
2274 /* Check for the status of the import request */
2275 Fnd_File.put_line(Fnd_File.LOG, 'jai_ap_tds_generation_pkg.approve_tds_invoices');
2276 Fnd_File.put_line(Fnd_File.LOG, 'p_parent_request_id =>' || p_parent_request_id);
2277 Fnd_File.put_line(Fnd_File.LOG, 'p_invoice_id=> ' || p_invoice_id );
2278 Fnd_File.put_line(Fnd_File.LOG, 'p_vendor_id=> ' || p_vendor_id);
2279 Fnd_File.put_line(Fnd_File.LOG, 'p_vendor_site_id=> ' || p_vendor_site_id);
2280 Fnd_File.put_line(Fnd_File.LOG, 'p_start_thhold_trx_id=> ' || p_start_thhold_trx_id);
2281
2282 lb_request_status :=
2283 fnd_concurrent.wait_for_request
2284 (
2285 request_id => p_parent_request_id,
2286 interval => 60, /* default value - sleep time in secs */
2287 max_wait => 0, /* default value - max wait in secs */
2288 phase => lv_phase,
2289 status => lv_status,
2290 dev_phase => lv_dev_phase,
2291 dev_status => lv_dev_status,
2292 message => lv_message
2293 );
2294
2295
2296 if not ( lv_dev_phase = 'COMPLETE' and lv_dev_status = 'NORMAL' ) then
2297
2298 Fnd_File.put_line(Fnd_File.LOG, 'Exiting with warning as parent request not completed with normal status');
2299 Fnd_File.put_line(Fnd_File.LOG, 'Message from parent request :' || lv_message);
2300 retcode := 1;
2301 errbuf := 'Exiting with warnings as parent request not completed with normal status';
2302 goto exit_from_procedure;
2303
2304 end if;
2305
2306 /* Control comes here only when the concurrent request has completed with Normal Status */
2307 Fnd_File.put_line(Fnd_File.LOG, 'Before Loop ');
2308
2309 /* Get all the tds invoices that have been created and call the base API to approve it */
2310 for cur_rec in
2311 c_jai_ap_tds_thhold_trxs(p_invoice_id , p_start_thhold_trx_id , p_vendor_id , p_vendor_site_id)
2312 loop
2313
2314
2315 /* Get the status of both the invoices and call approval API, if it is not already approved */
2316 ln_holds_count := 0;
2317 lv_approval_status := null;
2318
2319 /* Bug 4872659. Added by Lakshmi Gopalsami
2320 There is a possibility that the invoice gets rejected via Interface and
2321 the invoice is not existing. Base requires the org_id from the
2322 invoice_id we pass. Ensure that the invoice_id exists before calling approval
2323 */
2324
2325 fnd_file.put_line(FND_FILE.LOG, ' Check for the TDS authority invoice ');
2329 lv_conc_flag := 'N';
2326
2327 /*Added by nprashar for bug # 6720018*/
2328 IF (FND_GLOBAL.CONC_REQUEST_ID is NULL) THEN
2330 ELSE
2331 lv_conc_flag := 'Y';
2332 END IF;
2333
2334 If cur_rec.invoice_to_tds_authority_id is not null Then
2335
2336 OPEN c_jai_chk_tds_inv(cur_rec.invoice_to_tds_authority_id);
2337 FETCH c_jai_chk_tds_inv INTO ln_tds_invoice_id,
2338 ln_org_id,
2339 --Bug 6819855. Added by Lakshmi Gopalsami
2340 ln_sob_id;
2341 CLOSE c_jai_chk_tds_inv;
2342
2343 fnd_file.put_line(FND_FILE.LOG, ' Org id ' || ln_org_id);
2344
2345
2346 mo_global.set_policy_context('S', ln_org_id);
2347
2348 fnd_file.put_line(FND_FILE.LOG,' TDS authority invoice id '
2349 || cur_rec.invoice_to_tds_authority_id);
2350
2351 /* Invoice to TDS Authority */
2352 /* Bug 6819855. Added by Lakshmi Gopalsami
2353 Commented the following code and added a call to function batch_approval
2354 ap_approval_pkg.approve
2355 (
2356 p_run_option => null,
2357 p_invoice_batch_id => null,
2358 p_begin_invoice_date => null,
2359 p_end_invoice_date => null,
2360 p_vendor_id => null,
2361 p_pay_group => null,
2362 p_invoice_id => cur_rec.invoice_to_tds_authority_id,
2363 p_entered_by => null,
2364 p_set_of_books_id => null,
2365 p_trace_option => null,
2366 p_conc_flag => lv_conc_flag 'N', /*Changed by nprashar for bug # 6720018
2367 p_holds_count => ln_holds_count,
2368 p_approval_status => lv_approval_status,
2369 /* Bug 4943949. Added by Lakshmi Gopalsami
2370 p_funds_return_code => lv_funds_ret_code,
2371 p_calling_sequence => 'jai_ap_tds_generation_pkg.approve_tds_invoices'
2372 ) ;
2373 */
2374 BEGIN
2375 IF ap_approval_pkg.batch_approval(
2376 p_run_option => 'New',
2377 p_sob_id => ln_sob_id,
2378 p_inv_start_date => NULL,
2379 p_inv_end_date => NULL,
2380 p_inv_batch_id => NULL,
2381 p_vendor_id => NULL,
2382 p_pay_group => NULL,
2383 p_invoice_id => cur_rec.invoice_to_tds_authority_id,
2384 p_entered_by => NULL,
2385 p_debug_switch => 'N',
2386 p_conc_request_id => FND_GLOBAL.CONC_REQUEST_ID,
2387 p_commit_size => 1000,
2388 p_org_id => ln_org_id,
2389 p_report_holds_count => ln_holds_count
2390 ) THEN
2391
2392 Fnd_File.put_line(Fnd_File.LOG, 'Invoice to TDS Authority ' ||
2393 cur_rec.invoice_to_tds_authority_num ||
2394 '(' || cur_rec.invoice_to_tds_authority_id ||
2395 ') Was submitted for Approval.
2396 Holds count ' || ln_holds_count);
2397 END IF ;
2398 EXCEPTION
2399 WHEN OTHERS THEN
2400 retcode := 'E';
2401 errbuf := 'Error from jai_ap_tds_generation_pkg.approve_tds_invoices-> :
2402 during call to batch_approval for TDS invoice' || sqlerrm;
2403 END;
2404
2405 End if;
2406
2407 /* Invoice to Supplier */
2408 ln_holds_count1 := 0;
2409 lv_approval_status := null;
2410
2411 If cur_rec.invoice_to_vendor_id is not null Then
2412
2413 OPEN c_jai_chk_tds_inv(cur_rec.invoice_to_vendor_id);
2414 FETCH c_jai_chk_tds_inv INTO ln_vendor_invoice_id,
2415 ln_org_id,
2416 --Bug 6819855. Added by Lakshmi Gopalsami
2417 ln_sob_id;
2418 CLOSE c_jai_chk_tds_inv;
2419
2420 mo_global.set_policy_context('S', ln_org_id);
2421
2422 fnd_file.put_line(FND_FILE.LOG,' Supplier credit invoice id '
2423 || cur_rec.invoice_to_vendor_id);
2424 /* Bug 6819855. Added by Lakshmi Gopalsami
2425 Commented the following code and added a call to function batch_approval
2426 ap_approval_pkg.approve
2427 (
2428 p_run_option => null,
2429 p_invoice_batch_id => null,
2430 p_begin_invoice_date => null,
2431 p_end_invoice_date => null,
2432 p_vendor_id => null,
2433 p_pay_group => null,
2434 p_invoice_id => cur_rec.invoice_to_vendor_id,
2435 p_entered_by => null,
2436 p_set_of_books_id => null,
2437 p_trace_option => null,
2438 p_conc_flag => lv_conc_flag /*'N', /*Changed by nprashar for bug # 6720018
2439 p_holds_count => ln_holds_count,
2440 p_approval_status => lv_approval_status,
2441 /* Bug 4943949. Added by Lakshmi Gopalsami
2442 p_funds_return_code => lv_funds_ret_code,
2443 p_calling_sequence => 'jai_ap_tds_generation_pkg.approve_tds_invoices'
2444 ) ;
2445 */
2446
2447 BEGIN
2448 IF ap_approval_pkg.batch_approval(
2449 p_run_option => 'New',
2450 p_sob_id => ln_sob_id,
2454 p_vendor_id => NULL,
2451 p_inv_start_date => NULL,
2452 p_inv_end_date => NULL,
2453 p_inv_batch_id => NULL,
2455 p_pay_group => NULL,
2456 p_invoice_id => cur_rec.invoice_to_vendor_id,
2457 p_entered_by => NULL,
2458 p_debug_switch => 'N',
2459 p_conc_request_id => FND_GLOBAL.CONC_REQUEST_ID,
2460 p_commit_size => 1000,
2461 p_org_id => ln_org_id,
2462 p_report_holds_count => ln_holds_count1
2463 ) THEN
2464
2465 Fnd_File.put_line(Fnd_File.LOG, 'Invoice to Supplier for TDS' ||
2466 cur_rec.invoice_to_vendor_num ||
2467 '(' || cur_rec.invoice_to_vendor_id ||
2468 ') Was submitted for Approval.
2469 Holds count ' || ln_holds_count1);
2470 END IF ;
2471 EXCEPTION
2472 WHEN OTHERS THEN
2473 retcode := 'E';
2474 errbuf := 'Error from jai_ap_tds_generation_pkg.approve_tds_invoices-> :
2475 during call to batch_approval for TDS invoice' || sqlerrm;
2476 END;
2477
2478
2479 End if;
2480 end loop;
2481
2482
2483 <<exit_from_procedure>>
2484
2485 return;
2486
2487 exception
2488 when others then
2489 retcode := 2;
2490 errbuf := 'Error from jai_ap_tds_generation_pkg.approve_tds_invoices : ' || sqlerrm;
2491 end approve_tds_invoices;
2492
2493 /* ********************************* populate_tds_invoice_id **************************************** */
2494
2495 procedure populate_tds_invoice_id
2496 (
2497 p_invoice_id in number,
2498 p_invoice_num in varchar2,
2499 p_vendor_id in number,
2500 p_vendor_site_id in number,
2501 p_process_flag out nocopy varchar2,
2502 p_process_message out nocopy varchar2
2503 )
2504 is
2505
2506 cursor c_check_inv_to_tds_authority (p_invoice_num varchar2, p_vendor_id number, p_vendor_site_id number) is
2507 select threshold_trx_id,
2508 invoice_id
2509 from jai_ap_tds_thhold_trxs
2510 where invoice_to_tds_authority_num = p_invoice_num
2511 and tds_authority_vendor_id = p_vendor_id
2512 and tds_authority_vendor_site_id = p_vendor_site_id
2513 and invoice_to_tds_authority_id is null;
2514
2515
2516 cursor c_check_inv_to_vendor (p_invoice_num varchar2, p_vendor_id number, p_vendor_site_id number) is
2517 select threshold_trx_id
2518 from jai_ap_tds_thhold_trxs
2519 where invoice_to_vendor_num = p_invoice_num
2520 and vendor_id = p_vendor_id
2521 and vendor_site_id = p_vendor_site_id
2522 and invoice_to_vendor_id is null;
2523
2524 ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%type;
2525 ln_invoice_id ap_invoices_all.invoice_id%type;
2526
2527
2528 begin
2529
2530 open c_check_inv_to_tds_authority(p_invoice_num, p_vendor_id, p_vendor_site_id);
2531 fetch c_check_inv_to_tds_authority into ln_threshold_trx_id, ln_invoice_id;
2532 close c_check_inv_to_tds_authority;
2533
2534 if ln_threshold_trx_id is not null then
2535 /* Invoice being created is the invoice to TDS authority */
2536
2537 update jai_ap_tds_thhold_trxs
2538 set invoice_to_tds_authority_id = p_invoice_id
2539 where threshold_trx_id = ln_threshold_trx_id;
2540
2541 else
2542
2543 /* Invoice being created is not the invoice to TDS authority */
2544 /* check if it is the invoice to vendor for TDS */
2545 open c_check_inv_to_vendor(p_invoice_num, p_vendor_id, p_vendor_site_id);
2546 fetch c_check_inv_to_vendor into ln_threshold_trx_id;
2547 close c_check_inv_to_vendor;
2548
2549 if ln_threshold_trx_id is not null then
2550
2551 /* Invoice being created is teh invoice to TDS authority */
2552 update jai_ap_tds_thhold_trxs
2553 set invoice_to_vendor_id = p_invoice_id
2554 where threshold_trx_id = ln_threshold_trx_id;
2555
2556 end if; /* TDS invoice to vendor */
2557
2558 end if; /* TDS invoice to TDS authority */
2559
2560
2561 <<exit_from_procedure>>
2562 return;
2563
2564 exception
2565 when others then
2566 p_process_flag := 'E';
2567 p_process_message := 'Error from jai_ap_tds_generation_pkg.populate_tds_invoice_id :' || sqlerrm;
2568 end populate_tds_invoice_id;
2569
2570 /* ********************************* populate_tds_invoice_id **************************************** */
2571
2572 /* ******************************** maintain_thhold_grps ******************************************* */
2573
2574 procedure maintain_thhold_grps
2575 (
2576 p_threshold_grp_id in out nocopy number ,
2577 p_vendor_id in number default null,
2578 p_org_tan_num in varchar2 default null,
2579 p_vendor_pan_num in varchar2 default null,
2580 p_section_type in varchar2 default null,
2584 p_trx_invoice_amount in number default null,
2581 p_section_code in varchar2 default null,
2582 p_fin_year in number default null,
2583 p_org_id in number default null,
2585 p_trx_invoice_cancel_amount in number default null,
2586 p_trx_invoice_apply_amount in number default null,
2587 p_trx_invoice_unapply_amount in number default null,
2588 p_trx_tax_paid in number default null,
2589 p_trx_thhold_change_tax_paid in number default null,
2590 p_trx_threshold_slab_id in number default null,
2591 p_tds_event in varchar2,
2592 p_invoice_id in number default null,
2593 p_invoice_line_number in number default null, /* AP lines Uptake */
2594 p_invoice_distribution_id in number default null,
2595 p_remarks in varchar2 default null,
2596 -- bug 5722028. Added by csahoo
2597 p_creation_date in date default sysdate,
2598 p_threshold_grp_audit_id out nocopy number,
2599 p_process_flag out nocopy varchar2,
2600 P_process_message out nocopy varchar2,
2601 p_codepath in out nocopy varchar2
2602 )
2603 is
2604
2605
2606 cursor c_jai_ap_tds_thhold_grps(p_threshold_grp_id number) is
2607 select total_invoice_amount ,
2608 total_invoice_cancel_amount ,
2609 total_invoice_apply_amount ,
2610 total_invoice_unapply_amount ,
2611 total_tax_paid ,
2612 total_thhold_change_tax_paid ,
2613 current_threshold_slab_id
2614 from jai_ap_tds_thhold_grps
2615 where threshold_grp_id = p_threshold_grp_id;
2616
2617 cursor c_get_threshold_grp_id
2618 ( p_vendor_id number, p_org_tan_num varchar2, p_vendor_pan_num varchar2,
2619 p_section_type varchar2, p_section_code varchar2, p_fin_year number) is
2620 select threshold_grp_id
2621 from jai_ap_tds_thhold_grps
2622 where vendor_id = p_vendor_id and
2623 org_tan_num = p_org_tan_num and
2624 vendor_pan_num = p_vendor_pan_num and
2625 section_type = p_section_type and
2626 section_code = p_section_code and
2627 fin_year = p_fin_year;
2628
2629
2630 r_jai_ap_tds_thhold_grps c_jai_ap_tds_thhold_grps%rowtype;
2631
2632 ln_threshold_grp_id jai_ap_tds_thgrp_audits.threshold_grp_id%type;
2633 ln_old_invoice_amount jai_ap_tds_thgrp_audits.old_invoice_amount%type;
2634 ln_old_invoice_cancel_amount jai_ap_tds_thgrp_audits.old_invoice_cancel_amount%type;
2635 ln_old_invoice_apply_amount jai_ap_tds_thgrp_audits.old_invoice_apply_amount%type;
2636 ln_old_invoice_unapply_amount jai_ap_tds_thgrp_audits.old_invoice_unapply_amount%type;
2637 ln_old_tax_paid jai_ap_tds_thgrp_audits.old_tax_paid%type;
2638 ln_old_thhold_change_tax_paid jai_ap_tds_thgrp_audits.old_thhold_change_tax_paid%type;
2639 ln_old_threshold_slab_id jai_ap_tds_thgrp_audits.old_threshold_slab_id%type;
2640
2641 ln_new_invoice_amount jai_ap_tds_thgrp_audits.old_invoice_amount%type;
2642 ln_new_invoice_cancel_amount jai_ap_tds_thgrp_audits.old_invoice_cancel_amount%type;
2643 ln_new_invoice_apply_amount jai_ap_tds_thgrp_audits.old_invoice_apply_amount%type;
2644 ln_new_invoice_unapply_amount jai_ap_tds_thgrp_audits.old_invoice_unapply_amount%type;
2645 ln_new_tax_paid jai_ap_tds_thgrp_audits.old_tax_paid%type;
2646 ln_new_thhold_change_tax_paid jai_ap_tds_thgrp_audits.old_thhold_change_tax_paid%type;
2647 ln_new_threshold_slab_id jai_ap_tds_thgrp_audits.old_threshold_slab_id%type;
2648 ln_effective_threshold_amount number;
2649 ln_effective_tax_paid number;
2650
2651 -- bug 5722028. Added by csahoo
2652 ln_tmp_tds_amt number;
2653 ln_tmp_tds_change number;
2654
2655
2656 begin
2657
2658 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_generation_pkg.maintain_thhold_grps', 'START'); /* 1 */
2659
2660
2661 /* Validate the input */
2662 ln_threshold_grp_id := nvl(p_threshold_grp_id, 0);
2663
2664 if ln_threshold_grp_id = 0 then
2665
2666 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /*2*/
2667
2668 if p_vendor_id is null then
2669 p_process_flag := 'E';
2670 P_process_message := 'Vendor must be specified as threshold group identifier is null(jai_ap_tds_generation_pkg.maintain_thhold_grps) ';
2671 goto exit_from_procedure;
2672 end if;
2673
2674 if p_org_tan_num is null then
2675 p_process_flag := 'E';
2676 P_process_message := 'Organization TAN number must be specified as threshold group identifier is null(jai_ap_tds_generation_pkg.maintain_thhold_grps) ';
2677 goto exit_from_procedure;
2678 end if;
2679
2683 goto exit_from_procedure;
2680 if p_vendor_pan_num is null then
2681 p_process_flag := 'E';
2682 P_process_message := 'Vendor PAN number must be specified as threshold group identifier is null(jai_ap_tds_generation_pkg.maintain_thhold_grps) ';
2684 end if;
2685
2686 if p_section_type is null then
2687 p_process_flag := 'E';
2688 P_process_message := 'Section Type must be specified as threshold group identifier is null(jai_ap_tds_generation_pkg.maintain_thhold_grps) ';
2689 goto exit_from_procedure;
2690 end if;
2691
2692 if p_section_code is null then
2693 p_process_flag := 'E';
2694 P_process_message := 'Section Code must be specified as threshold group identifier is null(jai_ap_tds_generation_pkg.maintain_thhold_grps) ';
2695 goto exit_from_procedure;
2696 end if;
2697
2698 if p_fin_year is null then
2699 p_process_flag := 'E';
2700 P_process_message := 'Fin Year must be specified as threshold group identifier is null(jai_ap_tds_generation_pkg.maintain_thhold_grps) ';
2701 goto exit_from_procedure;
2702 end if;
2703
2704 end if; /* Validate the input */
2705
2706 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /*3*/
2707
2708 if ln_threshold_grp_id = 0 then
2709
2710 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
2711 /* Threshold has not been given as an input, check if exists */
2712 open c_get_threshold_grp_id
2713 (p_vendor_id, p_org_tan_num, p_vendor_pan_num, p_section_type, p_section_code, p_fin_year);
2714 fetch c_get_threshold_grp_id into ln_threshold_grp_id;
2715 close c_get_threshold_grp_id;
2716
2717 if nvl(ln_threshold_grp_id, 0) = 0 then
2718
2719 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
2720
2721 insert into jai_ap_tds_thhold_grps
2722 (
2723 threshold_grp_id ,
2724 vendor_id ,
2725 org_tan_num ,
2726 vendor_pan_num ,
2727 section_type ,
2728 section_code ,
2729 fin_year ,
2730 created_by ,
2731 creation_date ,
2732 last_updated_by ,
2733 last_update_date ,
2734 last_update_login
2735 )
2736 values
2737 (
2738 jai_ap_tds_thhold_grps_s.nextval ,
2739 p_vendor_id ,
2740 p_org_tan_num ,
2741 p_vendor_pan_num ,
2742 p_section_type ,
2743 p_section_code ,
2744 p_fin_year ,
2745 fnd_global.user_id ,
2746 sysdate ,
2747 fnd_global.user_id ,
2748 sysdate ,
2749 fnd_global.login_id
2750 )
2751 returning threshold_grp_id into ln_threshold_grp_id;
2752
2753 p_threshold_grp_id := ln_threshold_grp_id;
2754
2755 end if; /* ln_threshold_grp_id does not exist */
2756
2757 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
2758
2759 end if; /* ln_threshold_grp_id is not given as an input */
2760
2761 /* Get the old value of teh threshold group */
2762 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
2763 open c_jai_ap_tds_thhold_grps(ln_threshold_grp_id);
2764 fetch c_jai_ap_tds_thhold_grps into r_jai_ap_tds_thhold_grps;
2765 close c_jai_ap_tds_thhold_grps;
2766
2767 ln_old_invoice_amount := r_jai_ap_tds_thhold_grps.total_invoice_amount;
2768 ln_old_invoice_cancel_amount := r_jai_ap_tds_thhold_grps.total_invoice_cancel_amount;
2769 ln_old_invoice_apply_amount := r_jai_ap_tds_thhold_grps.total_invoice_apply_amount;
2770 ln_old_invoice_unapply_amount := r_jai_ap_tds_thhold_grps.total_invoice_unapply_amount;
2771 ln_old_tax_paid := r_jai_ap_tds_thhold_grps.total_tax_paid;
2772 ln_old_thhold_change_tax_paid := r_jai_ap_tds_thhold_grps.total_thhold_change_tax_paid;
2773 ln_old_threshold_slab_id := r_jai_ap_tds_thhold_grps.current_threshold_slab_id;
2774
2775 /* Check that threshold should not become negative */
2776 ln_effective_threshold_amount :=
2777 ( nvl(ln_old_invoice_amount, 0) + nvl(p_trx_invoice_amount, 0) ) -
2778 ( nvl(ln_old_invoice_cancel_amount, 0) + nvl(p_trx_invoice_cancel_amount, 0) ) -
2779 ( nvl(ln_old_invoice_apply_amount, 0) + nvl(p_trx_invoice_apply_amount, 0) ) +
2780 (nvl(ln_old_invoice_unapply_amount, 0) + nvl(p_trx_invoice_unapply_amount, 0) );
2781
2782 if ln_effective_threshold_amount < 0 then
2783 p_process_flag := 'E';
2784 p_process_message := 'Effective Total invoice amount for threshold cannot be negative.(Total Invoice - Cancel - apply + Unapply )' ;
2785 goto exit_from_procedure;
2786 end if;
2787
2788 /* Check that total tax paid should not become negative */
2789 ln_effective_tax_paid := nvl(ln_old_tax_paid, 0) + nvl(p_trx_tax_paid, 0);
2790 if ln_effective_tax_paid < 0 then
2791 p_process_flag := 'E';
2795
2792 p_process_message := 'Effective Tax Paid amount cannot be negative.' ;
2793 goto exit_from_procedure;
2794 end if;
2796 -- Bug 5722028. Added by Lakshmi Gopalsami
2797 ln_tmp_tds_amt := ROUND(nvl(p_trx_tax_paid,0),g_inr_currency_rounding);
2798 ln_tmp_tds_change := ROUND(nvl(p_trx_thhold_change_tax_paid,0), g_inr_currency_rounding);
2799
2800 IF p_tds_event NOT IN
2801 -- Bug 7280925. Commented by Lakshmi Gopalsami ('INVOICE CANCEL',
2802 ('PREPAYMENT UNAPPLICATION') THEN
2803 IF trunc(p_creation_date) >=
2804 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
2805 ln_tmp_tds_amt := get_rnded_value(ln_tmp_tds_amt);
2806 END IF;
2807 END IF;
2808 -- End if ;
2809
2810 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
2811 update jai_ap_tds_thhold_grps
2812 set
2813 total_invoice_amount = nvl(total_invoice_amount, 0) + nvl(p_trx_invoice_amount, 0),
2814 total_invoice_cancel_amount = nvl(total_invoice_cancel_amount, 0) + nvl(p_trx_invoice_cancel_amount, 0),
2815 total_invoice_apply_amount = nvl(total_invoice_apply_amount, 0) + nvl(p_trx_invoice_apply_amount, 0),
2816 total_invoice_unapply_amount = nvl(total_invoice_unapply_amount, 0) + nvl(p_trx_invoice_unapply_amount, 0),
2817 total_tax_paid = nvl(total_tax_paid, 0) + nvl(p_trx_tax_paid, 0),
2818 total_thhold_change_tax_paid = nvl(total_thhold_change_tax_paid, 0) + nvl(p_trx_thhold_change_tax_paid, 0),
2819 --current_threshold_slab_id = nvl( p_trx_threshold_slab_id, current_threshold_slab_id)
2820 --commented the above and added the below by Ramananda for Bug#4562793
2821 current_threshold_slab_id = nvl( p_trx_threshold_slab_id, 0)
2822 where threshold_grp_id = ln_threshold_grp_id;
2823
2824 /* Get the new value */
2825 r_jai_ap_tds_thhold_grps := null;
2826 open c_jai_ap_tds_thhold_grps(ln_threshold_grp_id);
2827 fetch c_jai_ap_tds_thhold_grps into r_jai_ap_tds_thhold_grps;
2828 close c_jai_ap_tds_thhold_grps;
2829
2830 ln_new_invoice_amount := r_jai_ap_tds_thhold_grps.total_invoice_amount;
2831 ln_new_invoice_cancel_amount := r_jai_ap_tds_thhold_grps.total_invoice_cancel_amount;
2832 ln_new_invoice_apply_amount := r_jai_ap_tds_thhold_grps.total_invoice_apply_amount;
2833 ln_new_invoice_unapply_amount := r_jai_ap_tds_thhold_grps.total_invoice_unapply_amount;
2834 ln_new_tax_paid := r_jai_ap_tds_thhold_grps.total_tax_paid;
2835 ln_new_thhold_change_tax_paid := r_jai_ap_tds_thhold_grps.total_thhold_change_tax_paid;
2836 ln_new_threshold_slab_id := r_jai_ap_tds_thhold_grps.current_threshold_slab_id;
2837
2838 /* Insert into the audite table */
2839 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
2840 insert into jai_ap_tds_thgrp_audits
2841 (
2842 threshold_grp_audit_id ,
2843 threshold_grp_id ,
2844 old_invoice_amount ,
2845 old_invoice_cancel_amount ,
2846 old_invoice_apply_amount ,
2847 old_invoice_unapply_amount ,
2848 old_tax_paid ,
2849 old_thhold_change_tax_paid ,
2850 old_threshold_slab_id ,
2851 trx_invoice_amount ,
2852 trx_invoice_cancel_amount ,
2853 trx_invoice_apply_amount ,
2854 trx_invoice_unapply_amount ,
2855 trx_tax_paid ,
2856 trx_thhold_change_tax_paid ,
2857 trx_threshold_slab_id ,
2858 new_invoice_amount ,
2859 new_invoice_cancel_amount ,
2860 new_invoice_apply_amount ,
2861 new_invoice_unapply_amount ,
2862 new_tax_paid ,
2863 new_thhold_change_tax_paid ,
2864 new_threshold_slab_id ,
2865 tds_event ,
2866 invoice_id ,
2867 invoice_line_number ,
2868 invoice_distribution_id ,
2869 remarks ,
2870 created_by ,
2871 creation_date ,
2872 last_updated_by ,
2873 last_update_date ,
2874 last_update_login
2875 )
2876 values
2877 (
2878 jai_ap_tds_thgrp_audits_s.nextval ,
2879 ln_threshold_grp_id ,
2880 ln_old_invoice_amount ,
2881 ln_old_invoice_cancel_amount ,
2882 ln_old_invoice_apply_amount ,
2883 ln_old_invoice_unapply_amount ,
2884 ln_old_tax_paid ,
2885 ln_old_thhold_change_tax_paid ,
2886 ln_old_threshold_slab_id ,
2887 p_trx_invoice_amount ,
2888 p_trx_invoice_cancel_amount ,
2889 p_trx_invoice_apply_amount ,
2890 p_trx_invoice_unapply_amount ,
2891 ln_tmp_tds_amt, --added for bug#5722028 csahoo
2892 p_trx_thhold_change_tax_paid ,
2893 p_trx_threshold_slab_id ,
2894 ln_new_invoice_amount ,
2895 ln_new_invoice_cancel_amount ,
2896 ln_new_invoice_apply_amount ,
2897 ln_new_invoice_unapply_amount ,
2901 p_tds_event ,
2898 ln_new_tax_paid ,
2899 ln_new_thhold_change_tax_paid ,
2900 ln_new_threshold_slab_id ,
2902 p_invoice_id ,
2903 p_invoice_line_number ,
2904 p_invoice_distribution_id ,
2905 p_remarks ,
2906 fnd_global.user_id ,
2907 sysdate ,
2908 fnd_global.user_id ,
2909 sysdate ,
2910 fnd_global.login_id
2911 )
2912 returning threshold_grp_audit_id into p_threshold_grp_audit_id;
2913 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
2914 <<exit_from_procedure>>
2915
2916 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 1 */
2917 return;
2918
2919 exception
2920 when others then
2921 fnd_file.put_line(FND_FILE.LOG,' Error in maintain thhold grps '|| substr(SQLERRM,1,200));
2922 p_process_flag := 'E';
2923 p_process_message := 'Error from jai_ap_tds_generation_pkg.maintain_thhold_grps :' || sqlerrm;
2924 end maintain_thhold_grps;
2925
2926 /* ******************************** maintain_thhold_grps ******************************************* */
2927 /* ******************************** insert_tds_thhold_trxs ******************************************* */
2928 --for bug#4333449
2929 procedure insert_tds_thhold_trxs
2930 (
2931 p_invoice_id in number,
2932 p_tds_event in varchar2,
2933 p_tax_id in number default null,
2934 p_tax_rate in number default null,
2935 p_taxable_amount in number default null,
2936 p_tax_amount in number default null,
2937 p_tds_authority_vendor_id in number default null,
2938 p_tds_authority_vendor_site_id in number default null,
2939 p_invoice_tds_authority_num in varchar2 default null,
2940 p_invoice_tds_authority_type in varchar2 default null,
2941 p_invoice_tds_authority_curr in varchar2 default null,
2942 p_invoice_tds_authority_amt in number default null,
2943 p_invoice_tds_authority_id in number default null,
2944 p_vendor_id in number default null,
2945 p_vendor_site_id in number default null,
2946 p_invoice_vendor_num in varchar2 default null,
2947 p_invoice_vendor_type in varchar2 default null,
2948 p_invoice_vendor_curr in varchar2 default null,
2949 p_invoice_vendor_amt in number default null,
2950 p_invoice_vendor_id in number default null,
2951 p_parent_inv_payment_priority in number default null,
2952 p_parent_inv_exchange_rate in number default null
2953 )
2954 is
2955 begin
2956
2957 fnd_file.put_line(FND_FILE.LOG, ' Insert -> insert_tds_thhold_trxs ');
2958
2959 insert into jai_ap_tds_thhold_trxs
2960 (
2961 threshold_trx_id ,
2962 invoice_id ,
2963 tds_event ,
2964 tax_id ,
2965 tax_rate ,
2966 taxable_amount ,
2967 tax_amount ,
2968 tds_authority_vendor_id ,
2969 tds_authority_vendor_site_id ,
2970 invoice_to_tds_authority_num ,
2971 invoice_to_tds_authority_type ,
2972 invoice_to_tds_authority_curr ,
2973 invoice_to_tds_authority_amt ,
2974 invoice_to_tds_authority_id ,
2975 vendor_id ,
2976 vendor_site_id ,
2977 invoice_to_vendor_num ,
2978 invoice_to_vendor_type ,
2979 invoice_to_vendor_curr ,
2980 invoice_to_vendor_amt ,
2981 invoice_to_vendor_id ,
2982 parent_inv_payment_priority ,
2983 parent_inv_exchange_rate ,
2984 created_by ,
2985 creation_date ,
2986 last_updated_by ,
2987 last_update_date ,
2988 last_update_login
2989 )
2990 values
2991 (
2992 jai_ap_tds_thhold_trxs_s.nextval ,
2993 p_invoice_id ,
2994 p_tds_event ,
2995 p_tax_id ,
2996 p_tax_rate ,
2997 p_taxable_amount ,
2998 p_tax_amount ,
2999 p_tds_authority_vendor_id ,
3000 p_tds_authority_vendor_site_id ,
3001 p_invoice_tds_authority_num ,
3005 p_invoice_tds_authority_id ,
3002 p_invoice_tds_authority_type ,
3003 p_invoice_tds_authority_curr ,
3004 p_invoice_tds_authority_amt ,
3006 p_vendor_id ,
3007 p_vendor_site_id ,
3008 p_invoice_vendor_num ,
3009 p_invoice_vendor_type ,
3010 p_invoice_vendor_curr ,
3011 p_invoice_vendor_amt ,
3012 p_invoice_vendor_id ,
3013 p_parent_inv_payment_priority ,
3014 p_parent_inv_exchange_rate ,
3015 fnd_global.user_id ,
3016 sysdate ,
3017 fnd_global.user_id ,
3018 sysdate ,
3019 fnd_global.login_id
3020 );
3021
3022 fnd_file.put_line(FND_FILE.LOG, ' Done Insert -> insert_tds_thhold_trxs ');
3023 end insert_tds_thhold_trxs;
3024
3025 /* ******************************** create_tds_after_holds_rel ******************************************* */
3026
3027 -- Bug#5131075(4685754). Added by Lakshmi Gopalsami
3028 -- Added for holds release
3029
3030 Procedure create_tds_after_holds_release
3031 (
3032 errbuf out nocopy varchar2,
3033 retcode out nocopy varchar2,
3034 p_invoice_id IN number,
3035 p_invoice_amount IN number,
3036 p_payment_status_flag IN varchar2,
3037 p_invoice_type_lookup_code IN varchar2,
3038 p_vendor_id IN number,
3039 p_vendor_site_id IN number,
3040 p_accounting_date IN DATE,
3041 p_invoice_currency_code IN varchar2,
3042 p_exchange_rate IN number,
3043 p_set_of_books_id IN number,
3044 p_org_id IN number,
3045 p_call_from IN varchar2,
3046 p_process_flag IN varchar2,
3047 p_process_message IN varchar2,
3048 p_codepath IN varchar2,
3049 p_request_id IN number default null-- added, Harshita for Bug#5131075(5346558)
3050 ) IS
3051
3052 lv_is_invoice_validated varchar2(1);
3053 lv_invoice_validation_status varchar2(25);
3054
3055 lv_process_flag varchar2(1);
3056 lv_process_message varchar2(200);
3057 lv_codepath varchar2(2000);
3058
3059 --Start addition by sanjikum for Bug#5131075(4722011)
3060 lv_new_transaction_si VARCHAR2(1);
3061 lv_new_transaction_pp VARCHAR2(1);
3062 lv_prepay_flag VARCHAR2(1);
3063
3064 -- added, Harshita for Bug#5131075(5346558)
3065 ln_req_status BOOLEAN ;
3066 lv_phase VARCHAR2(80) ;
3067 lv_status VARCHAR2(80) ;
3068 lv_dev_phase VARCHAR2(80) ;
3069 lv_dev_status VARCHAR2(80) ;
3070 lv_message VARCHAR2(80) ;
3071
3072 CURSOR c_check_prepayment_apply(p_invoice_distribution_id NUMBER)
3073 IS
3074 SELECT '1'
3075 FROM jai_ap_tds_prepayments
3076 WHERE invoice_distribution_id_prepay = p_invoice_distribution_id;
3077
3078 CURSOR c_check_prepayment_unapply(p_invoice_distribution_id_pp NUMBER)
3079 IS
3080 SELECT '1'
3081 FROM jai_ap_tds_prepayments
3082 WHERE invoice_distribution_id_prepay = p_invoice_distribution_id_pp
3083 AND unapply_flag = 'Y';
3084 --End addition by sanjikum for Bug#5131075(4722011)
3085
3086 lv_debug char(1) :='N'; -- Harshita, changed debug to 'N' for 5367640
3087
3088 -- Bug 5722028. Added by Lakshmi Gopalsami
3089 cursor get_creation_date is
3090 select creation_date
3091 from ap_invoices_all
3092 where invoice_id = p_invoice_id;
3093 ld_creation_date DATE;
3094 -- End for bug 5722028.
3095
3096 Begin
3097
3098 lv_codepath := p_codepath;
3099
3100 -- Harshita for Bug#5131075(5346558)
3101 BEGIN
3102 IF p_request_id is not null THEN
3103 ln_req_status := fnd_concurrent.wait_for_request
3104 (request_id => p_request_id,
3105 interval => 1,
3106 max_wait => 0,
3107 phase => lv_phase,
3108 status => lv_status,
3109 dev_phase => lv_dev_phase,
3110 dev_status => lv_dev_status,
3111 message => lv_message) ;
3112
3113 IF not ln_req_status THEN
3114 FND_FILE.put_line(FND_FILE.log, 'Phase : ' || lv_phase || 'Status : ' || lv_status || 'Dev Phase : ' || lv_dev_phase ||
3115 ' Dev Status : ' || lv_dev_status || ' Message : ' || lv_message );
3116 FND_FILE.put_line(FND_FILE.log, 'Status of Completion of previous Concurrent Create TDS Invoice After Holds Release - Request Id ' || p_request_id || ' ' || SQLERRM );
3117 END IF ;
3118
3119 END IF ;
3120
3121 EXCEPTION
3122 WHEN OTHERS THEN
3123 FND_FILE.put_line(FND_FILE.log, 'Phase : ' || lv_phase || 'Status : ' || lv_status || 'Dev Phase : ' || lv_dev_phase ||
3124 ' Dev Status : ' || lv_dev_status || ' Message : ' || lv_message );
3125 FND_FILE.put_line(FND_FILE.log, 'Status of Completion of previous Concurrent Create TDS Invoice After Holds Release - Request Id ' || p_request_id || ' ' || SQLERRM );
3126 END;
3127
3128
3132 l_invoice_amount => p_invoice_amount,
3129 lv_invoice_validation_status :=
3130 AP_INVOICES_UTILITY_PKG.get_approval_status(
3131 l_invoice_id => p_invoice_id,
3133 l_payment_status_flag => p_payment_status_flag,
3134 l_invoice_type_lookup_code => p_invoice_type_lookup_code);
3135
3136 if lv_invoice_validation_status not in ('APPROVED', 'AVAILABLE', 'UNPAID') then
3137 lv_is_invoice_validated := 'N';
3138 Else
3139 lv_is_invoice_validated := 'Y';
3140 end if;
3141
3142 if lv_debug='Y' then
3143 fnd_file.put_line(FND_FILE.LOG, ' value of validate'||lv_is_invoice_validated);
3144 end if ;
3145
3146 if lv_is_invoice_validated = 'Y' then
3147
3148 jai_ap_tds_generation_pkg.process_tds_at_inv_validate
3149 (
3150 p_invoice_id => p_invoice_id,
3151 p_vendor_id => p_vendor_id,
3152 p_vendor_site_id => p_vendor_site_id,
3153 p_accounting_date => p_accounting_date,
3154 p_invoice_currency_code => p_invoice_currency_code,
3155 p_exchange_rate => p_exchange_rate,
3156 p_set_of_books_id => p_set_of_books_id,
3157 p_org_id => p_org_id,
3158 p_call_from => p_call_from,
3159 p_creation_date => ld_creation_date, -- Bug 5722028. Added by csahoo
3160 p_process_flag => lv_process_flag,
3161 p_process_message => lv_process_message,
3162 p_codepath => lv_codepath
3163 );
3164
3165 --Moved this from below to here by Sanjikum for Bug#5131075(4722011)
3166 if nvl(lv_process_flag, 'N') = 'E' then
3167 fnd_file.put_line(FND_FILE.LOG, ' Error in the concurrent program '|| lv_process_message);
3168 goto exit_from_procedure;
3169 END IF;
3170
3171 --Start Addition by Sanjikum for Bug#5131075(4722011)
3172 FOR i IN(SELECT a.invoice_id,
3173 a.amount,
3174 a.invoice_distribution_id,
3175 a.parent_reversal_id,
3176 a.prepay_distribution_id,
3177 a.accounting_date,
3178 a.org_id,
3179 a.last_updated_by,
3180 a.last_update_date,
3181 a.created_by,
3182 a.creation_date,
3183 b.vendor_id,
3184 b.vendor_site_id,
3185 b.invoice_currency_code,
3186 b.exchange_rate,
3187 b.set_of_books_id
3188 FROM ap_invoice_distributions_all a,
3189 ap_invoices_all b
3190 WHERE a.invoice_id = b.invoice_id
3191 AND b.invoice_id = p_invoice_id
3192 AND a.line_type_lookup_code = 'PREPAY'
3193 AND b.source <> 'TDS'
3194 AND b.cancelled_date is null
3195 AND invoice_type_lookup_code NOT IN ('CREDIT', 'DEBIT'))
3196 LOOP
3197
3198 lv_prepay_flag := NULL;
3199
3200 --Apply Scenario
3201 IF NVL(i.amount,0) < 0 THEN
3202
3203 OPEN c_check_prepayment_apply(i.invoice_distribution_id);
3204 FETCH c_check_prepayment_apply INTO lv_prepay_flag;
3205 CLOSE c_check_prepayment_apply;
3206
3207 --Unapply Scenario
3208 ELSIF NVL(i.amount,0) > 0 THEN
3209
3210 OPEN c_check_prepayment_unapply(i.parent_reversal_id);
3211 FETCH c_check_prepayment_unapply INTO lv_prepay_flag;
3212 CLOSE c_check_prepayment_unapply;
3213
3214 END IF;
3215
3216 --should be run, only if prepayment application/unapplication is not already processed
3217 IF lv_prepay_flag IS NULL THEN
3218
3219
3220 jai_ap_tds_tax_defaultation.check_old_transaction
3221 (
3222 p_invoice_id => i.invoice_id,
3223 p_new_transaction => lv_new_transaction_si
3224 );
3225
3226 --Check for Pprepayment
3227 jai_ap_tds_tax_defaultation.check_old_transaction
3228 (
3229 p_invoice_distribution_id => i.prepay_distribution_id,
3230 p_new_transaction => lv_new_transaction_pp
3231 );
3232
3233 if lv_new_transaction_si = 'Y' and lv_new_transaction_pp = 'Y' then
3234
3235 lv_codepath := null;
3236
3237 jai_ap_tds_prepayments_pkg.process_prepayment
3238 (
3239 p_invoice_id => i.invoice_id,
3240 p_invoice_distribution_id => i.invoice_distribution_id,
3241 p_prepay_distribution_id => i.prepay_distribution_id,
3242 p_parent_reversal_id => i.parent_reversal_id,
3243 p_prepay_amount => i.amount,
3244 p_vendor_id => i.vendor_id,
3245 p_vendor_site_id => i.vendor_site_id,
3246 p_accounting_date => i.accounting_date,
3247 p_invoice_currency_code => i.invoice_currency_code,
3248 p_exchange_rate => i.exchange_rate,
3249 p_set_of_books_id => i.set_of_books_id,
3250 p_org_id => i.org_id,
3251 p_creation_date => i.creation_date, -- Bug 5722028
3252 p_process_flag => lv_process_flag,
3253 p_process_message => lv_process_message,
3254 p_codepath => lv_codepath
3255 );
3259 'Error - procedure jai_ap_tds_generation_pkg.create_tds_after_holds_release : ' || lv_process_message);
3256
3257 if nvl(lv_process_flag, 'N') = 'E' then
3258 raise_application_error(-20007,
3260 end if;
3261
3262 else
3263 --Invoke the old regime functionality
3264 jai_ap_tds_prepayments_pkg.process_old_transaction
3265 (
3266 p_invoice_id => i.invoice_id,
3267 p_invoice_distribution_id => i.invoice_distribution_id,
3268 p_prepay_distribution_id => i.prepay_distribution_id,
3269 p_amount => i.amount,
3270 p_last_updated_by => i.last_updated_by,
3271 p_last_update_date => i.last_update_date,
3272 p_created_by => i.created_by,
3273 p_creation_date => i.creation_date,
3274 p_org_id => i.org_id,
3275 p_process_flag => lv_process_flag,
3276 p_process_message => lv_process_message
3277 );
3278
3279 if nvl(lv_process_flag, 'N') = 'E' then
3280 raise_application_error(-20008,
3281 'Error - procedure jai_ap_tds_generation_pkg.create_tds_after_holds_release : ' || lv_process_message);
3282 end if;
3283 end if; --Transactions in new regime
3284
3285 END IF;
3286
3287 END LOOP;
3288
3289 << exit_from_procedure >>
3290
3291 NULL;
3292
3293 --End Addition by Sanjikum for Bug#5131075(4722011)
3294
3295 Else
3296 fnd_file.put_line(FND_FILE.LOG,' Not generating the TDS invoice
3297 as the parent invoice is not yet validated');
3298 retcode := 1;
3299 End if; /* lv_is_invoice_validated = 'Y' */
3300
3301 End create_tds_after_holds_release;
3302 -- End for bug#5131075(4685754)
3303
3304 /* ******************************** create_tds_after_holds_rel ******************************************* */
3305
3306 --new procedure created by sanjikum for bug#5131075(4718907)
3307 --This procedure gives the current threshold slab
3308 PROCEDURE get_tds_threshold_slab( p_prepay_distribution_id IN NUMBER,
3309 p_threshold_grp_id IN OUT NOCOPY NUMBER,
3310 p_threshold_hdr_id IN OUT NOCOPY NUMBER,
3311 p_threshold_slab_id OUT NOCOPY NUMBER,
3312 p_threshold_type OUT NOCOPY VARCHAR2,
3313 p_process_flag OUT NOCOPY VARCHAR2,
3314 p_process_message OUT NOCOPY VARCHAR2,
3315 p_codepath IN OUT NOCOPY VARCHAR2)
3316 IS
3317 CURSOR c_get_threshold_grp_id(p_prepay_distribution_id NUMBER)
3318 IS
3319 SELECT threshold_grp_id
3320 FROM jai_ap_tds_inv_taxes
3321 WHERE invoice_distribution_id = p_prepay_distribution_id
3322 AND section_type = 'TDS_SECTION';
3323
3324 CURSOR c_get_threshold_grp_dtl(p_threshold_grp_id NUMBER)
3325 IS
3326 SELECT *
3327 FROM jai_ap_tds_thhold_grps
3328 WHERE threshold_grp_id = p_threshold_grp_id;
3329
3330 CURSOR c_get_threshold_hdr(p_vendor_id NUMBER,
3331 p_org_tan_num VARCHAR2,
3332 p_pan_num VARCHAR2,
3333 p_section_type VARCHAR2,
3334 p_section_code VARCHAR2)
3335 IS
3336 SELECT threshold_hdr_id
3337 FROM jai_ap_tds_th_vsite_v
3338 WHERE vendor_id = p_vendor_id
3339 AND tan_no = p_org_tan_num
3340 AND pan_no = p_pan_num
3341 AND section_type = p_section_type
3342 AND section_code = p_section_code;
3343
3344 CURSOR c_jai_ap_tds_thhold_slabs( p_threshold_hdr_id NUMBER,
3345 p_threshold_type VARCHAR2,
3346 p_amount NUMBER)
3347 IS
3348 SELECT threshold_slab_id, threshold_type_id, from_amount, to_amount, tax_rate
3349 FROM jai_ap_tds_thhold_slabs
3350 WHERE threshold_hdr_id = p_threshold_hdr_id
3351 AND threshold_type_id in
3352 ( SELECT threshold_type_id
3353 FROM jai_ap_tds_thhold_types
3354 WHERE threshold_hdr_id = p_threshold_hdr_id
3355 AND threshold_type = p_threshold_type
3356 AND trunc(sysdate) between from_date and nvl(to_date, sysdate + 1)
3357 )
3358 AND from_amount <= p_amount
3359 AND NVL(to_amount, p_amount) >= p_amount
3360 ORDER BY from_amount asc;
3361
3362 r_get_threshold_grp_dtl c_get_threshold_grp_dtl%ROWTYPE;
3363 ln_effective_invoice_amt NUMBER;
3364 r_jai_ap_tds_thhold_slabs c_jai_ap_tds_thhold_slabs%ROWTYPE;
3365 lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
3366
3367 BEGIN
3368 IF p_threshold_grp_id IS NULL THEN
3369 OPEN c_get_threshold_grp_id(p_prepay_distribution_id);
3370 FETCH c_get_threshold_grp_id INTO p_threshold_grp_id;
3371 CLOSE c_get_threshold_grp_id;
3372 END IF;
3373
3374 OPEN c_get_threshold_grp_dtl(p_threshold_grp_id);
3378 IF p_threshold_hdr_id IS NULL THEN
3375 FETCH c_get_threshold_grp_dtl INTO r_get_threshold_grp_dtl;
3376 CLOSE c_get_threshold_grp_dtl;
3377
3379 OPEN c_get_threshold_hdr(r_get_threshold_grp_dtl.vendor_id,
3380 r_get_threshold_grp_dtl.org_tan_num,
3381 r_get_threshold_grp_dtl.vendor_pan_num,
3382 r_get_threshold_grp_dtl.section_type,
3383 r_get_threshold_grp_dtl.section_code);
3384 FETCH c_get_threshold_hdr INTO p_threshold_hdr_id;
3385 CLOSE c_get_threshold_hdr;
3386 END IF;
3387
3388 ln_effective_invoice_amt := r_get_threshold_grp_dtl.total_invoice_amount -
3389 r_get_threshold_grp_dtl.total_invoice_cancel_amount -
3390 r_get_threshold_grp_dtl.total_invoice_apply_amount +
3391 r_get_threshold_grp_dtl.total_invoice_unapply_amount;
3392
3393 lv_threshold_type := 'CUMULATIVE';
3394
3395 --check if the current amount falls in the cumulative threshold
3396 OPEN c_jai_ap_tds_thhold_slabs(p_threshold_hdr_id,
3397 lv_threshold_type,
3398 ln_effective_invoice_amt);
3399 FETCH c_jai_ap_tds_thhold_slabs INTO r_jai_ap_tds_thhold_slabs;
3400 CLOSE c_jai_ap_tds_thhold_slabs;
3401
3402 IF r_jai_ap_tds_thhold_slabs.threshold_slab_id IS NULL THEN
3403
3404 lv_threshold_type := 'SINGLE';
3405
3406 --check if the current amount falls in the single threshold
3407 OPEN c_jai_ap_tds_thhold_slabs(p_threshold_hdr_id,
3408 lv_threshold_type,
3409 99999999999999);
3410 FETCH c_jai_ap_tds_thhold_slabs INTO r_jai_ap_tds_thhold_slabs;
3411 CLOSE c_jai_ap_tds_thhold_slabs;
3412 END IF;
3413
3414 p_threshold_slab_id := r_jai_ap_tds_thhold_slabs.threshold_slab_id;
3415 p_threshold_type := lv_threshold_type;
3416
3417 EXCEPTION
3418 WHEN OTHERS THEN
3419 p_process_flag := 'E';
3420 p_process_message := SUBSTR(SQLERRM,1,200);
3421 END get_tds_threshold_slab;
3422
3423
3424 --new procedure created by sanjikum for bug#5131075(4718907)
3425 --This procedure takes as input the old and new threshold and checks if any type of Threshold Rollback processing is required
3426
3427 PROCEDURE process_threshold_rollback( p_invoice_id IN VARCHAR2,
3428 p_before_threshold_type IN VARCHAR2,
3429 p_after_threshold_type IN VARCHAR2,
3430 p_before_threshold_slab_id IN NUMBER,
3431 p_after_threshold_slab_id IN NUMBER,
3432 p_threshold_grp_id IN NUMBER,
3433 p_org_id IN NUMBER,
3434 p_accounting_date IN DATE,
3435 p_invoice_distribution_id IN NUMBER DEFAULT NULL,
3436 p_prepay_distribution_id IN NUMBER DEFAULT NULL,
3437 p_process_flag OUT NOCOPY VARCHAR2,
3438 p_process_message OUT NOCOPY VARCHAR2,
3439 p_codepath IN OUT NOCOPY VARCHAR2)
3440 IS
3441
3442 CURSOR c_threshold_slab(p_threshold_slab_id NUMBER,
3443 p_org_id NUMBER)
3444 IS
3445 SELECT b.tax_rate,
3446 b.from_amount,
3447 a.tax_id
3448 FROM jai_ap_tds_thhold_taxes a,
3449 jai_ap_tds_thhold_slabs b
3450 WHERE a.threshold_slab_id = b.threshold_slab_id
3451 AND a.operating_unit_id = p_org_id
3452 AND b.threshold_slab_id = p_threshold_slab_id;
3453
3454 CURSOR c_threshold_grp(p_threshold_grp_id NUMBER)
3455 IS
3456 SELECT *
3457 FROM jai_ap_tds_thhold_grps
3458 WHERE threshold_grp_id = p_threshold_grp_id;
3459
3460 CURSOR c_taxable_amount(c_threshold_grp_id NUMBER,
3461 c_single_threshold_amt NUMBER)
3462 IS
3463 SELECT NVL(SUM(a.taxable_amount),0) taxable_amount
3464 FROM jai_ap_tds_thhold_trxs a
3465 WHERE a.threshold_grp_id = c_threshold_grp_id
3466 AND a.tds_event = 'INVOICE VALIDATE'
3467 AND a.taxable_amount >= c_single_threshold_amt
3468 AND NOT EXISTS (SELECT '1'
3469 FROM jai_ap_tds_inv_cancels b
3470 WHERE a.invoice_id = b.invoice_id);
3471
3472 CURSOR c_prepayments(c_threshold_grp_id NUMBER)
3473 IS
3474 SELECT *
3475 FROM jai_ap_tds_prepayments
3476 WHERE tds_threshold_grp_id = c_threshold_grp_id
3477 AND NVL(unapply_flag,'N') <> 'Y';
3478
3479 CURSOR c_thhold_trxs(p_invoice_distribution_id NUMBER,
3480 p_single_threshold_amt NUMBER)
3481 IS
3482 SELECT 'Y'
3483 FROM jai_ap_tds_thhold_trxs a,
3484 jai_ap_tds_inv_taxes b
3485 WHERE a.invoice_id = b.invoice_id
3489
3486 AND b.invoice_distribution_id = p_invoice_distribution_id
3487 AND a.tds_event = 'INVOICE VALIDATE'
3488 AND a.taxable_amount >= p_single_threshold_amt;
3490 r_threshold_slab c_threshold_slab%ROWTYPE;
3491 r_before_threshold_slab c_threshold_slab%ROWTYPE;
3492 ln_effective_invoice_amt NUMBER;
3493 ln_effective_tds_amt NUMBER;
3494 ln_diff_tds_amount NUMBER;
3495 r_threshold_grp c_threshold_grp%ROWTYPE;
3496 v_si_flag VARCHAR2(1);
3497 v_pp_flag VARCHAR2(1);
3498 lv_tds_event jai_ap_tds_thhold_trxs.tds_event%TYPE;
3499 lv_tds_invoice_num ap_invoices_all.invoice_num%type;
3500 lv_tds_cm_num ap_invoices_all.invoice_num%type;
3501 ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%TYPE;
3502 ln_threshold_grp_audit_id jai_ap_tds_thgrp_audits.threshold_grp_audit_id%TYPE;
3503 ln_threshold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
3504
3505 CURSOR get_thhold_transn (cp_threshold_grp_id IN NUMBER )
3506 IS
3507 SELECT SUM(NVL(jattt.taxable_amount,0))
3508 FROM jai_ap_tds_thhold_trxs jattt
3509 WHERE jattt.threshold_grp_id = cp_threshold_grp_id
3510 AND ( jattt.tds_event like 'THRESHOLD TRANSITION%' OR
3511 -- Bug 5722028. Added by csahoo
3512 -- added the following condition
3513 jattt.tds_event like 'THRESHOLD ROLLBACK%'
3514 );
3515
3516 -- bug 5722028. Added by csahoo
3517 ln_taxable_thhold_change NUMBER;
3518
3519 FUNCTION get_pp_threshold(p_invoice_distribution_id IN NUMBER,
3520 p_single_threshold_amt IN NUMBER)
3521 RETURN VARCHAR2
3522 IS
3523 PRAGMA AUTONOMOUS_TRANSACTION;
3524
3525 CURSOR cur_thhold_trxs IS
3526 SELECT 'Y'
3527 FROM jai_ap_tds_thhold_trxs a
3528 WHERE a.tds_event = 'INVOICE VALIDATE'
3529 AND a.taxable_amount >= p_single_threshold_amt
3530 AND a.invoice_id IN (SELECT invoice_id
3531 FROM ap_invoice_distributions_all
3532 WHERE invoice_distribution_id = (SELECT prepay_distribution_id
3533 FROM ap_invoice_distributions_all
3534 WHERE invoice_distribution_id = p_invoice_distribution_id));
3535 lv_pp_flag VARCHAR2(1);
3536
3537 BEGIN
3538 OPEN cur_thhold_trxs;
3539 FETCH cur_thhold_trxs INTO lv_pp_flag;
3540 CLOSE cur_thhold_trxs;
3541
3542 RETURN lv_pp_flag;
3543
3544 END get_pp_threshold;
3545
3546 BEGIN
3547
3548 /*
3549 This functionality is required only if Threshold changes from any of the cumulative threshold slabs
3550 to either Single or any other cumulative threshold slab
3551 We need to check, only if the earlier threshold type is cumulative
3552 if the earlier type is single, it can't change to cumulative
3553 */
3554
3555 IF p_before_threshold_type = 'CUMULATIVE' THEN
3556
3557 --There is no change in the threshold slab. Means it is still in the same cumulative slab
3558 IF p_before_threshold_slab_id = p_after_threshold_slab_id THEN
3559
3560 NULL; --Nothing is required to be done, as there is no slab change
3561
3562 --There is a change in the slab. New slab is either cumulative or single
3563 ELSE
3564
3565 OPEN c_threshold_grp(p_threshold_grp_id);
3566 FETCH c_threshold_grp INTO r_threshold_grp;
3567 CLOSE c_threshold_grp;
3568
3569 OPEN c_threshold_slab(p_threshold_slab_id => p_after_threshold_slab_id,
3570 p_org_id => p_org_id);
3571 FETCH c_threshold_slab INTO r_threshold_slab;
3572 CLOSE c_threshold_slab;
3573
3574 --This is required, if there is no setup for the current threshold
3575 OPEN c_threshold_slab(p_threshold_slab_id => p_before_threshold_slab_id,
3576 p_org_id => p_org_id);
3577 FETCH c_threshold_slab INTO r_before_threshold_slab;
3578 CLOSE c_threshold_slab;
3579
3580 --If the new threshold type/slab is cumulative
3581 IF p_after_threshold_type = 'CUMULATIVE' THEN
3582
3583 ln_effective_invoice_amt := r_threshold_grp.total_invoice_amount -
3584 r_threshold_grp.total_invoice_cancel_amount -
3585 r_threshold_grp.total_invoice_apply_amount +
3586 r_threshold_grp.total_invoice_unapply_amount;
3587
3588 --If the new threshold type/slab is single
3589 ELSE
3590
3591 --If there is no single threshold setup done
3592 IF p_after_threshold_slab_id IS NULL THEN
3593 ln_effective_invoice_amt := 0;
3594 ELSE
3595
3596 --Calculate the TDS, based on the single threshold and pass the entry for the TDS amount
3597
3598 --Get all the invoice validations, where invoice amount is > single threshold amount
3599 OPEN c_taxable_amount(c_threshold_grp_id => p_threshold_grp_id,
3600 c_single_threshold_amt => r_threshold_slab.from_amount);
3601 FETCH c_taxable_amount INTO ln_effective_invoice_amt;
3602 CLOSE c_taxable_amount;
3603
3604 /* Bug 5722028. Added by Lakshmi Gopalsami
3608 */
3605 * We need to fetch the sum of taxable as part of threshold
3606 * transition or rollback as this would have been populated
3607 * with the amount of invoice on which TDS is not deducted.
3609
3610 OPEN get_thhold_transn( cp_threshold_grp_id => p_threshold_grp_id );
3611 FETCH get_thhold_transn INTO ln_taxable_thhold_change;
3612 CLOSE get_thhold_transn;
3613
3614 ln_effective_invoice_amt := ln_effective_invoice_amt + nvl(ln_taxable_thhold_change,0);
3615
3616 --If there are any invoices more than Single threshold, only then need to progress
3617 IF ln_effective_invoice_amt > 0 THEN
3618
3619 --Get all the prepayments applied in the current threshold group
3620 FOR i IN c_prepayments(p_threshold_grp_id) LOOP
3621
3622 v_si_flag := NULL;
3623
3624 --For SI. Check if the invoice amount of SI is more than Single threshold
3625 OPEN c_thhold_trxs(i.invoice_distribution_id,
3626 r_threshold_slab.from_amount);
3627 FETCH c_thhold_trxs INTO v_si_flag;
3628 CLOSE c_thhold_trxs;
3629
3630 v_pp_flag := NULL;
3631
3632 --For PP. Check if the invoice amount of SI is more than Single threshold
3633 --If the current transaction is PP application. As in the else part the autonomous function is
3634 --being used, which wouldn't be able to see the current transaction...means the PP application
3635 IF p_invoice_distribution_id = i.invoice_distribution_id_prepay THEN
3636
3637 OPEN c_thhold_trxs(p_prepay_distribution_id,
3638 r_threshold_slab.from_amount);
3639 FETCH c_thhold_trxs INTO v_pp_flag;
3640 CLOSE c_thhold_trxs;
3641 ELSE
3642 --Here the autonomous function is used, as it is required to select from ap_invoice_distributions table.
3643 --If this function is not used, this shall give the mutating error
3644 v_pp_flag := get_pp_threshold(i.invoice_distribution_id_prepay, r_threshold_slab.from_amount);
3645 END IF;
3646
3647 --If both the SI and PP have invoice amount > Single threshold, then adjustment amount need to be calculated
3648 IF NVL(v_si_flag,'N') = 'Y' AND NVL(v_pp_flag,'N') = 'Y' THEN
3649 ln_effective_invoice_amt := ln_effective_invoice_amt - NVL(i.application_amount,0);
3650 END IF;
3651
3652 END LOOP; --c_prepayments
3653
3654 END IF; --ln_effective_invoice_amt > 0
3655
3656 END IF;
3657
3658 END IF;
3659
3660 IF NVL(ln_effective_invoice_amt,0) = 0 THEN
3661 ln_effective_tds_amt := 0;
3662 ELSE
3663 ln_effective_tds_amt := ROUND(ln_effective_invoice_amt * (r_threshold_slab.tax_rate/100),0);
3664 END IF;
3665
3666 ln_diff_tds_amount := r_threshold_grp.total_tax_paid - ln_effective_tds_amt;
3667
3668
3669 IF ln_diff_tds_amount > 0 THEN
3670
3671 --There is an excess TDS payment/deduction. So need to create RTN invoice for the TDS Authority and SI for Vendor for ln_diff_tds_amount
3672
3673 lv_tds_event := 'THRESHOLD ROLLBACK( from slab id - '||p_before_threshold_slab_id||' to slab id - '||p_after_threshold_slab_id||')';
3674
3675 jai_ap_tds_generation_pkg.generate_tds_invoices
3676 (
3677 pn_invoice_id => p_invoice_id ,
3678 pn_taxable_amount => null ,
3679 --No taxable amount in case of threshold rollback invoice
3680 pn_tax_amount => ln_diff_tds_amount ,
3681 pn_tax_id => NVL(r_threshold_slab.tax_id, r_before_threshold_slab.tax_id) ,
3682 pd_accounting_date => p_accounting_date ,
3683 pv_tds_event => lv_tds_event ,
3684 pn_threshold_grp_id => p_threshold_grp_id ,
3685 pv_tds_invoice_num => lv_tds_invoice_num ,
3686 pv_cm_invoice_num => lv_tds_cm_num ,
3687 pn_threshold_trx_id => ln_threshold_trx_id ,
3688 pd_creation_date => sysdate, -- Bug 5722028. Added by csahoo
3689 p_process_flag => p_process_flag ,
3690 p_process_message => p_process_message
3691 );
3692
3693 if p_process_flag = 'E' then
3694 goto exit_from_procedure;
3695 end if;
3696
3697 IF ln_threshold_trx_id IS NOT NULL THEN
3698 jai_ap_tds_generation_pkg.import_and_approve
3699 (
3700 p_invoice_id => p_invoice_id,
3701 p_start_thhold_trx_id => ln_threshold_trx_id,
3702 p_tds_event => lv_tds_event,
3703 p_process_flag => p_process_flag,
3704 p_process_message => p_process_message
3705 );
3706 END IF;
3707
3708 --Update the total tax amount for which invoice was raised
3709 ln_threshold_grp_id := p_threshold_grp_id;
3710
3711 maintain_thhold_grps
3712 (
3713 p_threshold_grp_id => ln_threshold_grp_id,
3714 p_trx_tax_paid => ln_diff_tds_amount*-1, --Multiplied by -1, as this should reduce the total tax amount
3715 p_trx_thhold_change_tax_paid => ln_diff_tds_amount*-1,
3716 p_trx_threshold_slab_id => p_after_threshold_slab_id,
3717 p_tds_event => lv_tds_event,
3718 p_invoice_id => p_invoice_id,
3719 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
3720 -- Bug 5722028. Added by Lakshmi Gopalsami
3721 p_creation_date => sysdate,
3722 p_process_flag => p_process_flag,
3723 P_process_message => P_process_message,
3724 p_codepath => p_codepath
3725 );
3726
3727 IF p_process_flag = 'E' THEN
3728 goto exit_from_procedure;
3729 END IF;
3730
3731 END IF;
3732
3733 END IF;
3734
3735 END IF;
3736
3737 <<exit_from_procedure>>
3738
3739 NULL;
3740
3741 EXCEPTION
3742 WHEN OTHERS THEN
3743 p_process_flag := 'E';
3744 p_process_message := SUBSTR(SQLERRM,1,200);
3745 END process_threshold_rollback;
3746
3747 -- Bug 5722028. Added by csahoo
3748 FUNCTION get_rnded_value (p_tax_amount in number)
3749 RETURN NUMBER AS
3750 ln_tmp_tax_amt number ;
3751 ln_tds_mod_value number ;
3752 ln_tds_sign number;
3753 BEGIN
3754 ln_tds_sign := sign(p_tax_amount);
3755 ln_tmp_tax_amt := abs(p_tax_amount);
3756
3757 IF jai_ap_tds_generation_pkg.gn_tds_rounding_factor = -1 then
3758 ln_tds_mod_value := 0;
3759 ln_tds_mod_value := MOD(ROUND(ln_tmp_tax_amt,
3760 g_inr_currency_rounding),10);
3761 IF ln_tds_mod_value >= 5 THEN
3762 ln_tmp_tax_amt := ln_tmp_tax_amt + (10-ln_tds_mod_value);
3763 ELSE -- < 5
3764 ln_tmp_tax_amt := ln_tmp_tax_amt - ln_tds_mod_value;
3765 END IF ;
3766 END IF ; -- jai_ap_tds_generation_pkg.gn_tds_rounding_factor = -1
3767 return (ln_tmp_tax_amt* ln_tds_sign );
3768 END get_rnded_value;
3769 -- End for bug 5722028.
3770
3771
3772 END jai_ap_tds_generation_pkg;