4 /* ----------------------------------------------------------------------------
1 PACKAGE BODY jai_ap_tds_generation_pkg
2 /* $Header: jai_ap_tds_gen.plb 120.77.12020000.14 2013/03/12 12:04:51 mmurtuza ship $ */
3 AS
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 -------------------------------------------------------------------------------
25
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.
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 31. 02-Mar-2009 Bgowrava for Bug#8254510, file Version 120.16.12000000.15, 120.26.12010000.7, 120.34
230 Modified the code in the procedure process_threshold_transition to avoid calculating the TDS for
231 difference in the rate between the rate at which an invoice has suffered earlier and the rate applicable
232 at the current threshold transition. Also Added code to this procedure to calculate surcharge amount for
233 all previous invoices in the threshold group when the threshold with hte surcharge applicability is
234 breached. A TDS invoice with the namin convention of -SUR- is created for the surcharge invoice.
235
236 32. 19-May-2009 Bgowrava for Bug#8459564, File Version 120.16.12000000.17, 120.26.12010000.9, 120.36
237 Issue: INDIA - TDS CERTIFICATES REPORT DOES NOT DISPLAY CORRECT AMOUNT
238 Fix: Modified the code in the process_threshold_transition procedure to pass the calculated taxable amount
239 for the additional TDS invoice pair created post threshold transition to the generate_tds_invoices procedure instead of just passing null.
240
241 33. 20-Aug-2009 Bgowrava for Bug#8716477, File Version 120.16.12000000.21, 120.26.12010000.13, 120.40
242 Issue: TDS CR MEMO and INVOICE NOT GENERATED FOR INVOICE HAVING MORE THAN 1000 LINES
243 Fix: The issue was occuring due to the length of the p_codepath exceeding the maximum length of 1996 and hence the procedure failing due to
244 numeric or value error. Hence commented the line where codepath was assigned with data without calling the funtion jai_general_pkg.plot_codepath.
245 also added exception details in the exception block of process_tds_at_inv_validate procedure.
246
247 34. 14-Oct-2009 Bgowrava for Bug# 8995604 , File version 120.16.12000000.22, 120.26.12010000.14, 120.41
248 Issue: CREDIT MEMO INCORRECTLY TRANSLATED FOR FOREIGN CURRENCY TDS INV
249 Fix : Avoided calling the get_rnded_value function for the vendor credit memo in case of foreign currency. also took care to convert hte INR value of tax to
250 foreign currency only after the rounding is completed for the INR tax amount by calling the get_rnded_value function.
251
252 35. 09-Dec-2009 Bgowrava for Bug#9186263, File version 120.16.12000000.24, 120.26.12010000.16, 120.43
253 Issue : INVOICE DATE ON RTN STANDARD INVOICE SHOULD BE GL DATE OF PREPAYMENT APPLICATION
254 Fix : Modified the value passed to the parameter invoice_date to ld_acoounting_date instead of the invoice date of the base invoice.
255
256
257 36. 13-Jan-2010 Xiao for Bug#7154864
258 the following changes were made for this issue.
259 1) The cursor c_calculate_tax is modified to retreive default_tax_id also.
260 2) The cursor c_check_slabs_end_dated is included again. this was earlier moved
261 to the trigger through the bug5925513.
262 3) modified c_get_taxes_to_generate_tds to retrieve default tax id when actual_tax_id
263 null.
264 4) modified cursor c_get_taxes_to_generate_tds to consider invoices which have a
265 actual_tax_id defined for generating TDS.
266 5) Uncommented the code responsible for throwing error when the invoice with an
267 enddated tax attached is validated
268 37. 13-Jan-2010 Xiao for Bug#6596019
269 The following changes are done as per this bug
270 1) modified c_for_each_tds_section to consider the invoice amount after deducting the
271 prepayment amount.
272 2) modified c_get_taxes_to_generate_tds to consider the taxable_amount after
273 deducting the prepayment amount.
274 3) Added new cursor to obtain the prepayment amount applied for a particular invoice.
275 4) Added new cursors c_jai_ap_no_tds_trx and c_jai_no_tds_trx_amt to calculate the
279 38. 13-Jan-2010 Xiao for Bug#6596019
276 TDS only on invoices which have not suffered TDS and ignore those which have already
277 suffered TDS, on reaching cumulative limit.
278
280 the code to throw an error when there is no active threshold defined for a
281 section code and the code to throw error when there are no active slabs available
282 for a particular threshold, are moved to the begining of the procedure process_tds_at_inv_validate
283 since it was not getting executed when iside the loop, due to certain conditions not getting satisfied.
284
285
286 40. 13-Jan-2010 Xiao for Bug#6596019
287 Added code for surcharge in the procedure process_threshold_transition
288 also added code for creating surcharge invoices when even is SURCHARGE_CALCULATE
289 in generate_tds_invoices procedure. also resolved the regression created due to earlier
290 fixes.
291
292 41. 13-Jan-2010 Xiao Lv for Bug#8345080, related 11i bug#8333898
293 Issue: TDS DEDUCTING TWICE
294 Fix: Added a new procedure get_prepay_appln_amt with PRAGMA AUTONOMOUS TRANSACTION to calculate
295 the application amount for invoices within the current group which are eligible for TDS
296 deduction at threshold transition. and this application amount is deducted from the total
297 amount while calculating the threshold transition amount.
298
299 42. 13-Jan-2010 Xiao Lv For Bug#8485691, related 11i bug#8439217
300 Issue: TDS CALCULATION AT APPLICABLE RATES DURING THRESHOLD TRANSITION
301 Fix: Modified the code in the procedure process_threshold_transition in the file jai_ap_tds_gen.plb,
302 to calculate the tax based on the rate applicable on the invoice which had not suffered TDS
303 earlier.
304
305 43. 13-Jan-2010 Xiao Lv For Bug#8513550, related 11i bug#8439276
306 Issue: ADDITIONAL TDS INV GETTING GENERATED FOR APPLIED PREPAY INV AT THRESHOLD
307 Fix: Modified the code in the process_threshold_transition procedure in jai_ap_tds_gen.plb. Here
308 added code to call the get_prepay_appln_amt function even for the invoice which is causing
309 the threshold transition to detect if any prepayment is attached to this invoice which should
310 be ignored while calculation of the transition TDS.
311
312
313 44. 14-Jan-2010 Jia For Bug#7431371, related 11i bug#7419533
314 Issue: FINANCIALS FOR INDIA -TDS NOT WORKING IN CASE OF MULTIPLE DISTRIBUTIONS
315 Fixed:
316 1) Modified the code in process get_prepay_invoice_amt.
317 Handled the case where a prepayment that is getting applied on the standard invoice had a 0% TDS
318 tax atttached, in which case the TDS on the standard invoice should deduct TDS on difference of
319 std invoice amount and prepayment invoice amount.
320 2) Modified the code in process process_tds_at_inv_validate.
321 Added code in process_tds_at_inv_validate to apply the prepayment amount increamently
322 in all the distribution lines, till there is sufficient amount left in prepayment to be applied.
323 Once the prepayment amount is completely applied, TDS or WCt or ESSI invoices get created on the
324 remaining standard invoice amount based on the TDS, WCT or ESSi tax respectively attached.
325
326 45. 14-Jan-2010 Jia For FP Bug#7312295
327 Issue: This is a forward port bug for the Bug#7252683.
328 Cancellation of the invoice breaching the surcharge threhsold does not cancel the surcharge invoice
329 that got created while the transition. this results in wrong surcharge calculation.
330
331 Fixed: Modified the code in procedure process_threshold_transition.
332 Added the nvl conditions to the various tax rates used in the surcharge rate calculation formula.
333 without this a null value in any one of either cess, or sh cess was leading to no surcharge invoice
334 getting created, even though surcharge was applicable.
335
336 46. 14-Jan-2010 Jia For FP Bug#7368735
337 Issue: This is a forward port bug for the Bug#7347096.
338 On attaching a wct tax alone, the error 'Error - Threshold is not defined for the applicable TDS section'
339 was getting thrown,this is due to the entry that gets created for TDS in jai_ap_tds_inv_taxes also with
340 no section codes, even though there is no distribution created which has a TDS tax.
341
342 Fixed: Modified the cursor c_check_valid_tax in procedure process_tds_at_inv_validate to only loop through
343 the distributions which have either a default_section_code or actual_section_code defined.
344
345 47. 14-Jan-2010 Jia For FP Bug#8278439
346 Issue: This is a forward port bug for the Bug#8269891.
347 The Threshold_hdr_id value in jai_ap_tds_thhold_trxs was not being populated, which was leading to
348 the record missing from the 'India - TDS PAyment Review' report, because the main query of this report
349 is dependent on the value of threshold_hdr_id column of jai_ap_tds_thhold_trxs.
350
351 Fixed: In cases where no default_section_code was specified in the vendor additional information,
352 the value for default_section_code in jai_ap_tds_inv_taxes was populated as null.
353 Hence during the execution of the cursor c_check_valid_tax it would fetch no records as the
357 48. 25-Jan-2010 Bug 5751783 (Forward Port of 5721614)
354 value for actual_section_code is also null at this point of time. Hence modified this query to
355 be based on actual_tax_id and default_tax_id rather than the section code.
356
358 -------------------------------------
359 Issues
360 + Amount in certificates is wrong. All calculations are made based on rounded values
361 + Certificates are generated with Taxable Basis as 0 but non zero tax amount
362 + Certificates are generated with negative amounts.
363 + During Prepayment Un-application if Threshold Transition occurs then there are no TDS Invoices generated.
364 + Taxable Basis is wrong for Threshold Rollback.
365 + Applying Prepayment with different rates results in negative RTN
366
367 Bug 8431516 (Forward Port of 7626202)
368 -------------------------------------
369 RTN invoice would be generated to negate the effect of TDS invoice created for a prepayment, when the prepayment
370 is applied to a standard invoice.
371
372 49. 30-Jun-2010 Bug 9759709
373 Issue: Wrong Liability Account is fetched for the TDS CM. If the Liability account is different from the
374 Liability account from Supplier Setup, then the account from Setup is fetched.
375 Fix: Fetched the Liability Account from Standard Invoice.
376
377 50. 22-Nov-2010 Bug# 10315928 by amandali
378 Description:R12 : ETDS FVU CHANGES AS NOTIFIED ON 09-OCT-2010 WRT NEW VERSION OF VALIDATION
379 Fix: Invoice Validation should fail if PAN is any of 'PANAPPLIED' or 'PANNOTAVBL' or 'PANINVALID'
380 and rate is not 20% (Higher Rate)
381
382 51. 20-jan-11 Bug #11070443 by amandali
383 Description:WCT IS NOT CALCULATED CORRECTLY WHEN INVOICE IS APPLIED TO PREPAYMENT BEFORE VALIDATION OF SI
384
385 52. 01-feb-2011 Bug #11671779 by amandali
386 Description:THRESHOLD TRANSITION TDS INVOICE IS MISSING IN TDS PAYMENT REVIEW REPORT
387 Fix:In a call to generate_tds_invoice for Threshold Transition invoices, added the parameter ln_threshold_hdr_id.
388 Previously it is taking null by default and hence the query in report 'India TDS Payment Review report' is failing for threshold transition invoices.
389
390 53. 15-Feb-2011 Bug #9562876 by amandali
391 Issue: TDS IS NOT GETTING DEDUCTED FOR PROPER AMOUNT
392 Fix: modified the code in process_threshold_transition procedure. Added new cursors to check if there was a threshold rollback that occured to the old
393 slab and if ocurred, the threshold transition should consider the rollback amount to create the transition invoice. The cursors added for this purpose are
394 c_thhold_rollback_occurred and c_get_rollback_amt.
395 Ported the fix of 9539694
396 54. 17-Mar-2011 Bug # 11803135 by amandali
397 Issue:TDS THRESHOLD IS NOT CONSIDERED FOR MANUALLY ATTACHED TAX CODES
398 Fix:Commented the actual_tax_id not null condition in the cursor c_get_taxes_to_generate_tds.
399 This would check for the threshold limit for both manually attached and defaulted tax codes.
400 55. 26-May-2011 Bug 11880998
401 Description: In an Invoice is canceled without validation prior to Threshold Breach it results in negative lines
402 in jai_ap_tds_inv_taxes and the same corrupts the Threshold Transition Invoice
403 Fix: Since the Invoice that is canceled without validation never hits the Threshold the same
404 needs to be skipped when picking invoices for Threshold Transition.
405 Modified cursor c_jai_ap_no_tds_trx to skip invoices based on AUDIT entries.
406 When an Invoice is canceled without validation the Transaction amounts in Audit tables are zero.
407
408 56. 23-Aug-2011 mmurtuza for bug12858951
409 Description: TDS CALCULATED FOR SERVICE TAX LINES WHEN THRESHOLD CROSSED
410 Fix: Added the condtion nvl(consider_amt_for_tds, 'Y') = 'Y' in al the statements involving jai_ap_tds_inv_taxes table
411 in process_tds_at_inv_validate procedure to pick invoices for tds invoice generation.
412
413 57. 14-Oct-2011 amandali for bug 13070779
414 Description:Wrong taxable amount shown for prepayment application on unvalidated standard invoice.
415 Fix:Commented out the assignment of pn_taxable_amount to ln_invoice_Amount as it is having the total invoice amount and not the net amount.
416
417 58. 02-FEB-2012 mmurtuza for bug 13561970
418 Description: Threshold rollback functionality is not working properly.
419 Fix: Modified cursor c_threshold_passed in procedure process_threshold_transition
420 Commented code for cursors c_thhold_rollback_occurred and c_get_rollback_amt in procedure process_threshold_transition
421 Commended code for cursor get_thhold_transn as Threshold Transition amount need not be subtracted to calculate the amount for which Threshold Rollback needs to be generated
422
423 59. 20-Feb-2012 amandali for bug 13715894
424 Description:No validation for TDS fin year setup during validation
425 Fix:Added org_id condition in cursor c_get_fin_year
426
427 60. 29-Mar-2012 amandali for bug 13840176
428 Description: WRONG TDS CALCULATION IS HAPPENING FOR THE PO MATCHED INVOICE WHICH ARE HAVING IPV line
429 Fix: As the IPV line would have a negative amount, ln_application_amount is not updated and so the application amount
430 of previous distribution is deducted for this line leading to wrong calculation of TDS.So, initialised application_amount to zero for every distribution_id
431
432 61 27-Jul-2012 amandali for bug 14369937
433 Description:Threshold TDS amount is wrongly calculated
437 Description:TDS threhsold transition amount is calculated wrongly
434 Fix:Added few conditions to the cursor c_jai_no_tds_trx_amt in procedure process_threshold_transition to calculate the correct taxable amount
435
436 62. 16-Nov-2012 amandali for bug 14709400
438 Fix:Modified the cursor c_jai_ap_tds_thhold_taxes in procedure process_threshold_transition
439
440 63. 29-Nov-2012 amandali for Bug:15918300
441 Description:Wrong threshold transition amount when an invoice has both TDS and WCT taxes attached.
442 Fix: Added section type condition in cursor c_default_tax_id in procedure process_threshold_transition
443
444 64. 28-Feb-2013 mmurtuza for bug 16016037
445 Description: UNABLE TO APPLY PREPAYMENTS TO THE STANDARD INVOICES
446 Fix: Commented the condition of p_match_status_flag before checking for Holds in procedure status_update_chk_validate
447
448 65. 12-Mar-2013 mmurtuza for bug 16435622
449 Description: INDIA - TDS PAYMENT REVIEW DOES NOT RESTRICT THE DATA BASED ON SECTION CODE
450 Fix: Uncommented the use of cursor c_get_threshold in cursor for loop c_for_each_tds_section. Also assigned null to r_jai_ap_tds_thhold_slabs before opening cursor c_jai_ap_tds_thhold_slabs
451
452 ---------------------------------------------------------------------------- */
453 /*Added Procedure below for Xiao for Bug#7154864*/
454 /*Added pn_section_type parameter to get_prepay_invoice_amt - Bug 11070443*/
455 procedure get_prepay_invoice_amt(pn_invoice_id NUMBER, pn_section_type VARCHAR2, pn_prepay_amt OUT NOCOPY NUMBER)
456 is
457 PRAGMA AUTONOMOUS_TRANSACTION;
458 cursor c_get_dist_prepay(p_invoice_id number) is
459 --select prepay_distribution_id, amount --Comments by Jia for FP Bug#7431371
460 select prepay_distribution_id, sum(amount) amount --Modified by Jia for FP Bug#7431371
461 from ap_invoice_distributions_all
462 where invoice_id = p_invoice_id
463 --Added the following clause to select the prepayment amounts with the same section type as the distribution - Bug 11070443
464 and
465 ((pn_section_type = 'TDS_SECTION' and global_attribute1 is not null)
466 or (pn_section_type = 'WCT_SECTION' and global_attribute2 is not null)
467 or (pn_section_type = 'ESSI_SECTION' and global_attribute3 is not null))
468 and prepay_distribution_id is not null
469 group by prepay_distribution_id; --Addec by Jia for FP Bug#7431371
470
471 cursor c_get_prepay_inv(p_prepay_dist_id number) is
472 select invoice_id
473 from ap_invoice_distributions_all
474 where invoice_distribution_id = p_prepay_dist_id;
475
476 cursor c_prepay_tds_cal(p_invoice_id number) is --Xiao for Bug#6767347
477 select 1 from
478 jai_ap_tds_thhold_trxs where
479 invoice_id = p_invoice_id;
480
481 --Addec by Jia for FP Bug#7431371, Begin
482 -------------------------------------------------------------------------------
483 cursor c_get_tax_code(p_invoice_id number, p_invoice_distribution_id number) is
484 select nvl(actual_tax_id, default_tax_id) tax_id
485 from jai_ap_tds_inv_taxes
486 where invoice_id = p_invoice_id
487 and invoice_distribution_id = p_invoice_distribution_id;
488
489 cursor c_get_tax_rate(p_tax_id number) is
490 select tax_rate
491 from jai_cmn_taxes_all
492 where tax_id = p_tax_id;
493
494 ln_tax_id number;
495 ln_tax_rate number;
496 -------------------------------------------------------------------------------
497 --Addec by Jia for FP Bug#7431371, End
498
499 ln_invoice_id number;
500 ln_prepay_tds_exists number := 0;
501 BEGIN
502 BEGIN
503 pn_prepay_amt := 0;
504 for r_get_dist_prepay in c_get_dist_prepay(pn_invoice_id)
505 loop
506 open c_get_prepay_inv(r_get_dist_prepay.prepay_distribution_id);
507 fetch c_get_prepay_inv into ln_invoice_id;
508 close c_get_prepay_inv;
509
510 open c_prepay_tds_cal(ln_invoice_id);
511 fetch c_prepay_tds_cal into ln_prepay_tds_exists;
512 close c_prepay_tds_cal;
513
514 if ln_prepay_tds_exists = 1 then
515 pn_prepay_amt := pn_prepay_amt + abs(r_get_dist_prepay.amount);
516
517 --Addec by Jia for FP Bug#7431371, Begin
518 -------------------------------------------------------------------------------
519 ELSE
520 open c_get_tax_code(ln_invoice_id, r_get_dist_prepay.prepay_distribution_id);
521 fetch c_get_tax_code into ln_tax_id;
522 close c_get_tax_code;
523
524 open c_get_tax_rate(ln_tax_id);
525 fetch c_get_tax_rate into ln_tax_rate;
526 close c_get_tax_rate;
527
528 if ln_tax_rate = 0 then
529 pn_prepay_amt := pn_prepay_amt + abs(r_get_dist_prepay.amount);
530 end if;
531 -------------------------------------------------------------------------------
532 --Addec by Jia for FP Bug#7431371, End
533 end if;
534 end loop;
535
536 EXCEPTION
537 WHEN NO_DATA_FOUND THEN
538 pn_prepay_amt:=0;
539 END;
540 END get_prepay_invoice_amt;
541
542 --Added by Xiao Lv for bug#8345080 on 13-Jan-10, begin
543 procedure get_prepay_appln_amt(pn_invoice_id NUMBER,
544 pn_threshold_grp_id NUMBER,
545 pn_curr_inv_flag VARCHAR2 DEFAULT 'N', --Added by Xiao Lv for Bug#8513550, related 11i bug#8439276
546 pn_apply_amt OUT NOCOPY NUMBER)
547 is
548 PRAGMA AUTONOMOUS_TRANSACTION;
549 cursor c_jai_apply_amount(cp_invoice_id number) is
550 select sum(a.application_amount) application_amount, d.threshold_grp_id, d.invoice_id
551 from jai_ap_tds_prepayments a,
552 ap_invoice_distributions_all b,
553 ap_invoice_distributions_all c,
554 jai_ap_tds_inv_taxes d
555 where a.invoice_distribution_id_prepay = b.invoice_distribution_id
556 and b.prepay_distribution_id = c.invoice_distribution_id
557 and nvl(a.unapply_flag, 'N') <> 'Y'
561
558 and c.invoice_id = cp_invoice_id --modified by Xiao Lv for Bug#8513550, related 11i bug#8439276
559 and c.invoice_id= d.invoice_id
560 group by d.threshold_grp_id, d.invoice_id;
562 /*Bug 12671504 - Start*/
563 /*If a Prepayment which suffers TDS due to SINGLE Threshold then the same is not deducted
564 during calculation of Effective Amount during Threshold Transition. This is resulting in
565 incorrect TDS deducted during Threshold Transition. Derive the Prepayments(which suffered TDS)
566 applied and subtract the same before calculating Effective amount for Threshold Transition*/
567 cursor c_pp_single_apply_amount(cp_invoice_id number) is
568 select sum(application_amount) amount
569 from jai_ap_tds_prepayments a,
570 ap_invoice_distributions_all b
571 where a.invoice_id = cp_invoice_id
572 and a.invoice_id = b.invoice_id
573 and a.invoice_distribution_id_prepay = b.invoice_distribution_id
574 and prepay_distribution_id is not null
575 and nvl(a.unapply_flag, 'N') <> 'Y'
576 and exists (select 1
577 from jai_ap_tds_inv_taxes jatit, ap_invoice_distributions_all aida
578 where aida.invoice_distribution_id = b.prepay_distribution_id
579 and aida.invoice_id = jatit.invoice_id
580 and aida.invoice_distribution_id = jatit.invoice_distribution_id
581 and jatit.threshold_slab_id_single is not null
582 and jatit.threshold_trx_id is not null
583 );
584 /*Bug 12671504 - End*/
585 --Added by Xiao Lv for Bug#8513550, related 11i bug#8439276, begin
586 cursor c_prepay_apply_amt(cp_invoice_id number) is
587 select abs(amount) amount, invoice_distribution_id, prepay_distribution_id
588 from ap_invoice_distributions_all
589 where invoice_id = cp_invoice_id
590 and line_type_lookup_code = 'PREPAY';
591
592 cursor c_get_thhold_grp(cp_invoice_id number, cp_invoice_dist_id number) is
593 select threshold_grp_id
594 from jai_ap_tds_inv_taxes
595 where invoice_distribution_id = cp_invoice_dist_id;
596
597 r_prepay_apply_amt c_prepay_apply_amt%rowtype;
598 lv_thhold_grp_id number;
599 --Added by Xiao Lv for Bug#8513550, related 11i bug#8439276, end
600 r_jai_apply_amount c_jai_apply_amount%rowtype;
601 r_pp_single_apply_amount c_pp_single_apply_amount%rowtype;
602
603 BEGIN
604 BEGIN
605 pn_apply_amt := 0;
606 --Added by Xiao Lv for Bug#8513550, related 11i bug#8439276, begin
607 if pn_curr_inv_flag = 'Y' then
608 for r_prepay_apply_amt in c_prepay_apply_amt(pn_invoice_id) loop
609 open c_get_thhold_grp(pn_invoice_id, r_prepay_apply_amt.prepay_distribution_id);
610 fetch c_get_thhold_grp into lv_thhold_grp_id;
611 close c_get_thhold_grp;
612
613 if lv_thhold_grp_id = pn_threshold_grp_id then
614 pn_apply_amt := pn_apply_amt + r_prepay_apply_amt.amount;
615 end if;
616 end loop;
617 else
618 --Added by Xiao Lv for Bug#8513550, related 11i bug#8439276, end
619 for r_jai_apply_amount in c_jai_apply_amount(pn_invoice_id) loop
620 if r_jai_apply_amount.threshold_grp_id = pn_threshold_grp_id then
621 pn_apply_amt := pn_apply_amt + nvl(r_jai_apply_amount.application_amount,0);
622 end if;
623 end loop;
624
625 /*Bug 12671504 - Start*/
626 OPEN c_pp_single_apply_amount(pn_invoice_id);
627 FETCH c_pp_single_apply_amount INTO r_pp_single_apply_amount;
628 CLOSE c_pp_single_apply_amount;
629 pn_apply_amt := pn_apply_amt + nvl(r_pp_single_apply_amount.amount, 0);
630 /*Bug 12671504 - End*/
631 end if; --Added by Xiao Lv for Bug#8513550
632
633 EXCEPTION
634 WHEN NO_DATA_FOUND THEN
635 pn_apply_amt:=0;
636 END;
637 END get_prepay_appln_amt;
638 --Added by Xiao Lv for bug#8345080 on 13-Jan-10, end
639
640 /*Added for Bug 8641199 - Start*/
641 procedure get_org_id(p_invoice_id IN NUMBER, p_org_id OUT NOCOPY NUMBER)
642 is
643 PRAGMA AUTONOMOUS_TRANSACTION;
644 BEGIN
645 select org_id into p_org_id
646 from ap_invoices_all
647 where invoice_id = p_invoice_id;
648 EXCEPTION
649 WHEN NO_DATA_FOUND THEN
650 p_org_id := NULL;
651 END;
652 /*Added for Bug 8641199 - End*/
653
654 /*Modified for Bug 8641199 - Start*/
655 procedure status_update_chk_validate
656 (
657 p_invoice_id in number,
658 p_invoice_line_number in number default null, /* AP lines uptake */
659 p_invoice_distribution_id in number default null,
660 p_match_status_flag in varchar2 default null,
661 p_is_invoice_validated out nocopy varchar2,
662 p_process_flag out nocopy varchar2,
663 p_process_message out nocopy varchar2,
664 p_codepath in out nocopy varchar2
665 )
666 is
667
668 lv_section_type VARCHAR2(15) ;
669
670
671 cursor c_check_not_validate(p_invoice_id number, p_section_type VARCHAR2 ) is
672 select count(tds_inv_tax_id) total_count, sum(decode(match_status_flag, 'A', 1, 0)) validated_a_count,
673 sum(decode(match_status_flag, 'T', 1, 0)) validated_t_count
674 from jai_ap_tds_inv_taxes
675 where invoice_id = p_invoice_id
676 -- Harshita for Bug 4870243
677 and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, invoice_line_number)
678 and invoice_distribution_id = nvl(p_invoice_distribution_id, invoice_distribution_id) -- Bug 6119216
679 and section_type = p_section_type ;
680
681 cursor c_fetch_po_encum(p_org_id number) is
682 select nvl(purch_encumbrance_flag, 'N')
683 from FINANCIALS_SYSTEM_PARAMS_ALL
684 where org_id = p_org_id;
688 from jai_ap_tds_inv_taxes
685
686
687 /*select tds_inv_tax_id
689 where invoice_id = p_invoice_id
690 and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
691 and nvl(invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
692 and section_type = p_section_type; */
693
694
695
696 cursor c_ap_holds_all(p_invoice_id number) is
697 select count(invoice_id)
698 from ap_holds_all
699 where invoice_id = p_invoice_id
700 and release_reason is null;
701
702
703 ln_total_count number;
704 ln_validated_a_cnt number;
705 ln_validated_t_cnt number;
706 ln_no_of_holds number;
707 lp_org_id number;
708 l_po_encum_flag VARCHAR2(1);
709
710 begin
711
712 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_generation_pkg.status_update_chk_validate', 'START'); /* 1 */
713
714 get_org_id(p_invoice_id, lp_org_id);
715 l_po_encum_flag := 'N';
716 if lp_org_id is NOT NULL THEN
717 open c_fetch_po_encum(lp_org_id);
718 fetch c_fetch_po_encum into l_po_encum_flag;
719 close c_fetch_po_encum;
720 end if;
721
722 if p_invoice_distribution_id is not null and p_match_status_flag is not null then
723 update jai_ap_tds_inv_taxes
724 set match_status_flag = p_match_status_flag
725 where invoice_id = p_invoice_id
726 and invoice_distribution_id = p_invoice_distribution_id;
727 end if;
728
729 ln_total_count := 0;
730 ln_validated_a_cnt := 0;
731 ln_validated_t_cnt := 0;
732
733 lv_section_type := 'TDS_SECTION' ; -- Harshita for Bug 4870243
734
735 open c_check_not_validate(p_invoice_id, lv_section_type); -- Harshita, added lv_section_type for Bug 4870243
736 fetch c_check_not_validate into ln_total_count, ln_validated_a_cnt, ln_validated_t_cnt;
737 close c_check_not_validate;
738
739 fnd_file.put_line(FND_FILE.LOG, ' Value of total cnt '|| ln_total_count);
740
741 fnd_file.put_line(FND_FILE.LOG, ' Value of validated A cnt '|| ln_validated_a_cnt);
742 fnd_file.put_line(FND_FILE.LOG, ' Value of validated T cnt '|| ln_validated_t_cnt);
743
744 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
745
746 if ln_total_count = (ln_validated_a_cnt + ln_validated_t_cnt) then
747 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
748
749 p_is_invoice_validated := 'Y';
750 else
751 p_is_invoice_validated := 'N';
752 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
753 end if;
754
755 if l_po_encum_flag = 'Y' and ln_validated_t_cnt > 0 then
756 p_is_invoice_validated := 'N';
757 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
758 end if;
759
760 -- if p_match_status_flag is not null then /*commented by mmurtuza for bug 16016037 */
761 /* Scenarios other than holds release */
762 open c_ap_holds_all(p_invoice_id);
763 fetch c_ap_holds_all into ln_no_of_holds;
764 close c_ap_holds_all;
765
766 if nvl(ln_no_of_holds, 0) > 0 then
767 p_is_invoice_validated := 'N';
768 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
769 end if;
770
771 -- end if; /*commented by mmurtuza for bug 16016037 */
772
773 fnd_file.put_line(FND_FILE.LOG, 'Status_update_chk_validate - Status of parent invoice '|| p_is_invoice_validated);
774
775 << exit_from_procedure >>
776 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath, null, 'END'); /* 7 */
777 return;
778
779 exception
780 when others then
781 p_process_flag := 'E';
782 P_process_message := 'jai_ap_tds_generation_pkg.status_update_chk_validate :' || sqlerrm;
783 return;
784 end status_update_chk_validate;
785
786 /*Modified for Bug 8641199 - End*/
787
788 /* ************************************* process_invoice ************************************ */
789
790
791 /* ************************************* process_tds_at_inv_validate ************************************ */
792
793 procedure process_tds_at_inv_validate
794 (
795 p_invoice_id in number,
796 p_vendor_id in number,
797 p_vendor_site_id in number,
798 p_accounting_date in date,
799 p_invoice_currency_code in varchar2,
800 p_exchange_rate in number,
801 p_set_of_books_id in number,
802 p_org_id in number,
803 p_call_from in varchar2,
804 -- Bug 5722028. Added by Lakshmi Gopalsami
805 p_creation_date in date,
806 p_process_flag out nocopy varchar2,
807 p_process_message out nocopy varchar2,
808 p_codepath in out nocopy varchar2
809 )
810 is
811
812 cursor c_check_if_exists(p_invoice_id number) is
813 select count(tds_inv_tax_id)
814 from jai_ap_tds_inv_taxes
815 where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
816 and invoice_id = p_invoice_id
817 and (actual_tax_id is not null or default_tax_id is not null);
818
822 where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
819 cursor c_check_if_processed(p_invoice_id number,p_process_status jai_ap_tds_inv_taxes.process_status%type) is
820 select count(tds_inv_tax_id)
821 from jai_ap_tds_inv_taxes
823 and invoice_id = p_invoice_id
824 and process_status = p_process_status;
825
826 cursor c_calculate_tax(p_invoice_id number) is
827 select tds_inv_tax_id, actual_tax_id,default_tax_id, amount, invoice_distribution_id,section_type --Xiao for Bug#7154864
828 /*Added Section Type - Bug 11070443*/
829 from jai_ap_tds_inv_taxes
830 where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
831 and invoice_id = p_invoice_id
832 and (default_tax_id is not null or actual_tax_id is not null); --Xiao for Bug#7154864
833
834 cursor c_ja_in_tax_codes(p_tax_id number) is
835 select tax_rate,
836 section_code,
837 end_date,
838 sysdate,
839 'Tax : ' || tax_name || ' is end dated as on ' || to_char(end_date, 'dd-mon-yyyy') ||
840 '. Setup needs modification.' tax_end_dated_message
841 from JAI_CMN_TAXES_ALL
842 where tax_id = p_tax_id;
843 /*Bug 5751783 - Selected non-rounded value for calculation*/
844 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, p_prepay_amt number) is--rchandan for bug#4428980 --add by xiao for bug#6596019
845 select actual_section_code, (sum(amount*p_exchange_rate)-p_prepay_amt) invoice_amount, sum(calc_tax_amount) section_amount,
846 sum(tax_amount) tax_amount_orig
847 from jai_ap_tds_inv_taxes
848 where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
849 and invoice_id = p_invoice_id
850 and section_type = p_section_type --rchandan for bug#4428980
851 and actual_section_code is not null
852 group by actual_section_code;
853 --having sum(tax_amount) <> 0; --Commented by Bgowrava for Bug#8254510
854
855 cursor c_po_vendors(p_vendor_id number) is
856 select tds_vendor_type_lookup_code
857 from JAI_AP_TDS_VNDR_TYPE_V
858 where vendor_id = p_vendor_id;
859
860 cursor c_get_threshold
861 (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
862 select threshold_hdr_id
863 from JAI_AP_TDS_TH_VSITE_V
864 where vendor_id = p_vendor_id
865 and vendor_site_id = p_vendor_site_id
866 and section_type = p_section_type --rchandan for bug#4428980
867 and section_code = p_tds_section_code;
868
869 cursor c_get_threshold_group
870 (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
871 select threshold_grp_id
872 from jai_ap_tds_thhold_grps
873 where vendor_id = p_vendor_id
874 and section_type = p_section_type --rchandan for bug#4428980
875 and section_code = p_tds_section_code
876 and org_tan_num = p_tan_no
877 and vendor_pan_num = p_pan_no
878 and fin_year = p_fin_year;
879
880 cursor c_jai_ap_tds_thhold_grps(p_threshold_grp_id number) is
881 select (
882 nvl(total_invoice_amount, 0) -
883 nvl(total_invoice_cancel_amount, 0) -
884 nvl(total_invoice_apply_amount, 0) +
885 nvl(total_invoice_unapply_amount, 0)
886 )
887 total_invoice_amount,
888 total_tax_paid,
889 total_thhold_change_tax_paid,
890 current_threshold_slab_id,
891 /*Bug 5751783. Selected non-rounded value for calculation*/
892 total_calc_tax_paid
893 from jai_ap_tds_thhold_grps
894 where threshold_grp_id = p_threshold_grp_id;
895
896
897 cursor c_jai_ap_tds_thhold_slabs
898 ( p_threshold_hdr_id number, p_threshold_type varchar2, p_amount number) is
899 select threshold_slab_id, threshold_type_id, from_amount, to_amount
900 from jai_ap_tds_thhold_slabs
901 where threshold_hdr_id = p_threshold_hdr_id
902 and threshold_type_id in
903 ( select threshold_type_id
904 from jai_ap_tds_thhold_types
905 where threshold_hdr_id = p_threshold_hdr_id
906 and threshold_type = p_threshold_type
907 /* Bug 4522540. Added by Lakshmi Gopalsami
908 Added the following date condition */
909 and trunc(p_accounting_Date) between from_date
910 and nvl(to_date, p_accounting_date + 1)
911 )
912 and p_amount between from_amount and nvl(to_amount, p_amount)
913 order by from_amount asc;
914
915
916 /*following cursor added for FP bug 6345725 - need to check if there are any active slab(s) defined */
917 cursor c_check_slabs_end_dated (p_threshold_hdr_id number) is
918 select 1
919 from jai_ap_tds_thhold_types
920 where threshold_hdr_id = p_threshold_hdr_id
921 and trunc(p_accounting_Date) between from_date and nvl(to_date, p_accounting_date + 1);
922
923 ln_check_slab_exists NUMBER;
924
925 /*Bug 5751783. Selected non-rounded value for calculation*/
926
927 cursor c_get_taxes_to_generate_tds
928 (p_invoice_id number, p_tds_section_code varchar2, p_generate_all_invoices varchar2,
929 p_exchange_rate number, p_threshold_slab_id_single number,p_section_type jai_ap_tds_inv_taxes.section_type%type, p_prepay_amt number) IS --rchandan for bug#4428980--add by xiao for bug#6596019
933 sum(tax_amount) tax_amount_orig
930 select nvl(actual_tax_id,default_tax_id) actual_tax_id, --added nvl by Xiao for Bug#7154864
931 ( sum(amount*p_exchange_rate)-p_prepay_amt) taxable_amount, --Xiao for bug#6596019
932 sum(calc_tax_amount) tax_amount,
934 from jai_ap_tds_inv_taxes
935 where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
936 and invoice_id = p_invoice_id
937 and section_type = p_section_type --rchandan for bug#4428980
938 and actual_section_code = p_tds_section_code
939 and (
940 (p_generate_all_invoices = 'Y' )
941 or
942 (p_threshold_slab_id_single > 0 )
943 -- or/*Commented the below condition for bug 11803135*/
944 -- (actual_tax_id is NOT NULL) --added by Xiao for Bug#7154864
945 /*FP of QA Bug 12431997 - Included in 11896260 - Tax to be deducted irrespective of Threshold if PAN is not available*/
946 OR
947 EXISTS (SELECT 1
948 FROM jai_ap_tds_vendor_hdrs
949 WHERE (pan_no IN ('PANAPPLIED','PANNOTAVBL','PANINVALID')
950 OR (pan_no IS NOT NULL AND confirm_pan_flag IS NULL))
951 AND vendor_site_id = 0
952 AND vendor_id = (SELECT vendor_id
953 FROM ap_invoices_all
954 WHERE invoice_id = p_invoice_id
955 )
956 )
957 )
958 group by nvl(actual_tax_id, default_tax_id); --added nvl by Xiao for Bug#7154864
959
960 cursor c_get_vendor_pan_tan(p_vendor_id number , p_vendor_site_id number) is
961 select c.pan_no pan_no,
962 d.org_tan_num tan_no,
963 c.confirm_pan_flag confirm_pan_flag /*Fixing QA Bug 12431997 - Included in 11896260*/
964 from po_vendors a,
965 po_vendor_sites_all b,
966 JAI_AP_TDS_VENDOR_HDRS c,
967 jai_ap_tds_org_tan_v d --rchandan for bug#4323338
968 where a.vendor_id = b.vendor_id
969 and b.vendor_id = c.vendor_id
970 and b.vendor_site_id = c.vendor_site_id
971 and b.org_id = d.organization_id
972 and a.vendor_id = p_vendor_id
973 and b.vendor_site_id = p_vendor_site_id;
974
975
976 lv_attr_code VARCHAR2(25);
977 lv_attr_type_code VARCHAR2(25);
978 lv_tds_regime VARCHAR2(25);
979 lv_regn_type_others VARCHAR2(25);
980
981 cursor c_get_fin_year(p_accounting_date date, p_org_id number) is
982 select fin_year
983 from JAI_AP_TDS_YEARS
984 where tan_no in /* where clause and subquery added by ssumaith - bug# 4448789*/
985 (
986 SELECT attribute_value
987 FROM JAI_RGM_ORG_REGNS_V
988 WHERE regime_code = lv_tds_regime
989 AND registration_type = lv_regn_type_others
990 AND attribute_type_code = lv_attr_type_Code
991 AND attribute_code = lv_attr_code
992 AND organization_id = p_org_id
993 )
994 and legal_entity_id = p_org_id /* Added the condition for bug 13715894 */
995 and p_accounting_date between start_date and end_date;
996
997 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
998 select currency_code
999 from gl_sets_of_books
1000 where set_of_books_id = cp_set_of_books_id;
1001
1002 /*Bug 5751783. Selected non-rounded value for calculation*/
1003
1004 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
1005 select section_type,
1006 actual_tax_id,
1007 sum(amount*p_exchange_rate) taxable_amount,
1008 sum(calc_tax_amount) tax_amount,
1009 sum(tax_amount) tax_amount_orig
1010 from jai_ap_tds_inv_taxes
1011 where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1012 and invoice_id = p_invoice_id
1013 and section_type <> p_section_type --rchandan for bug#4428980
1014 and actual_tax_id is not null
1015 group by section_type, actual_tax_id;
1016
1017 /*START, Bgowrava for Bug#8254510*/
1018 cursor c_jai_slab_start_amt(p_threshold_slab_id number) is
1019 select jatts.from_amount from_amount,
1020 jatts.tax_rate tax_rate,
1021 jattt.tax_id tax_id,
1022 (jitc.tax_rate-(nvl(jitc.surcharge_rate,0) + nvl(jitc.cess_rate,0) + nvl(jitc.sh_cess_rate,0))) tax_rate_orig
1023 from jai_ap_tds_thhold_slabs jatts,
1024 jai_ap_tds_thhold_taxes jattt,
1025 jai_cmn_taxes_all jitc
1026 where jatts.threshold_slab_id = jattt.threshold_slab_id
1027 and jattt.tax_id = jitc.tax_id
1028 and jatts.threshold_slab_id = p_threshold_slab_id;
1029
1030 r_jai_slab_start_amt_after c_jai_slab_start_amt%rowtype;
1031 r_jai_slab_start_amt_before c_jai_slab_start_amt%rowtype;
1032 ln_tds_amt_before number;
1033 ln_tds_amt_after number;
1034 ln_tds_tax_amount number;
1035 /*END, Bgowrava for Bug#8254510*/
1036
1037 /*START, Added by xiao for Bug#6596019*/
1038 cursor c_get_prepayment_amt(p_invoice_id number) is
1039 select amount_paid
1040 from ap_invoices_all
1041 where invoice_id = p_invoice_id;
1042
1043 -- Modified by Jia for FP Bug#7368735, Begin
1044 ---------------------------------------------------------
1045 /*
1046 cursor c_check_valid_tax(p_invoice_id number) is
1047 select actual_tax_id, default_tax_id
1048 from jai_ap_tds_inv_taxes
1049 where invoice_id = p_invoice_id
1050 and section_type = 'TDS_SECTION' ;
1054 ------------------------------------------------------------------------
1051 */ --Commented by Jia for FP Bug#7368735
1052
1053 -- Modified by Jia for FP Bug#8278439, Begin
1055 cursor c_check_valid_tax(p_invoice_id number) is
1056 /*
1057 select nvl(actual_section_code, default_section_code) section_code
1058 from jai_ap_tds_inv_taxes
1059 where section_type = 'TDS_SECTION'
1060 and invoice_id = p_invoice_id
1061 and (actual_section_code is not null or default_section_code is not null);
1062 */ --Commented by Jiaf or FP Bug#8278439
1063 select jitc.section_code section_code
1064 from jai_cmn_taxes_all jitc, jai_ap_tds_inv_taxes jatit
1065 where jitc.tax_id = nvl(jatit.actual_tax_id, jatit.default_tax_id)
1066 and jatit.section_type = 'TDS_SECTION'
1067 and jatit.invoice_id = p_invoice_id
1068 and (actual_tax_id is not null or default_tax_id is not null);
1069 ------------------------------------------------------------------------
1070 -- Modified by Jia for FP Bug#8278439, End
1071
1072 ---------------------------------------------------------
1073 -- Modified by Jia for FP Bug#7368735, End
1074
1075 /*END, xiao for Bug#6596019*/
1076
1077 --Addec by Jia for FP Bug#7431371, Begin
1078 -------------------------------------------------------------------------------
1079 /*Added parameter section_type fro bug 11070443*/
1080 cursor c_get_amount_already_applied(p_invoice_distribution_id number, p_section_type varchar2) is
1081 select sum(application_amount)
1082 from jai_ap_tds_prepayments
1083 where invoice_distribution_id = p_invoice_distribution_id
1084 and (
1085 (p_section_type = 'TDS_SECTION' and nvl(tds_applicable_flag, '*') = 'Y') or
1086 (p_section_type = 'WCT_SECTION' and nvl(wct_applicable_flag, '*') = 'Y') or
1087 (p_section_type = 'ESSI_SECTION' and nvl(essi_applicable_flag, '*') = 'Y')
1088 )
1089 and nvl(unapply_flag, 'N') <> 'Y';
1090 ln_remaining_tds_prepay_amt number;
1091 ln_remaining_wct_prepay_amt number;
1092 ln_remaining_ess_prepay_amt number;
1093 ln_remaining_prepayment_amount number;
1094 ln_effective_available_amount number;
1095 ln_already_applied_amount number;
1096 ln_application_amount number;
1097 ln_prev_section_type varchar2(20) := NULL;
1098 ln_prev_dist_id number := 0;
1099 -------------------------------------------------------------------------------
1100 --Addec by Jia for FP Bug#7431371, End
1101
1102 ln_prepayment_app_amt number :=0; --xiao for Bug#6596019
1103 r_jai_ap_tds_thhold_grps c_jai_ap_tds_thhold_grps%rowtype;
1104 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
1105 r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
1106
1107 ln_count number:= 0;
1108 ln_cnt_already_processed number:= 0;
1109 ln_tax_id number;
1110 ln_tax_amount number;
1111 ln_threshold_grp_id number;
1112 lv_vendor_type_lookup_code po_vendors.vendor_type_lookup_code%type;
1113 ln_threshold_hdr_id number;
1114 r_jai_ap_tds_thhold_slabs c_jai_ap_tds_thhold_slabs%rowtype;
1115 ln_total_invoice_amount number;
1116 ln_threshold_slab_id_before number;
1117 ln_threshold_slab_id_after number;
1118 ln_threshold_slab_id_single number;
1119 lv_generate_all_invoices varchar2(1);
1120 ln_threshold_trx_id number;
1121 lv_tds_invoice_num ap_invoices_all.invoice_num%type;
1122 lv_tds_cm_num ap_invoices_all.invoice_num%type;
1123
1124 lv_pan_no JAI_AP_TDS_VENDOR_HDRS.pan_no%type;
1125 lv_tan_no jai_ap_tds_org_tan_v.org_tan_num %type; --rchandan for bug#4323338
1126 lv_confirm_pan_flag JAI_AP_TDS_VENDOR_HDRS.confirm_pan_flag%type; /*Bug 11896260*/
1127 ln_exchange_rate number;
1128 ln_fin_year JAI_AP_TDS_YEARS.fin_year%type;
1129 lv_slab_transition_tds_event jai_ap_tds_thhold_trxs.tds_event%type;
1130
1131 ln_no_of_tds_inv_generated number := 0;
1132 lb_result boolean;
1133 ln_req_id number;
1134 ln_start_threshold_trx_id number;
1135 ln_threshold_grp_audit_id number;
1136 lv_tds_section_type CONSTANT varchar2(30) := 'TDS_SECTION'; --rchandan for bug#4428980
1137 -- Bug 5722028. Added by Lakshmi Gopalsami
1138 ln_tmp_tds_amt number;
1139
1140 sqlbuf VARCHAR2(1996);
1141
1142
1143 begin
1144
1145 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_generation_pkg.process_tds_at_inv_validate', 'START'); /* 1 */
1146 open c_check_if_exists(p_invoice_id);
1147 fetch c_check_if_exists into ln_count;
1148 close c_check_if_exists;
1149
1150 fnd_file.put_line(FND_FILE.LOG, '1. Check for tax count'|| ln_count);
1151
1152 if nvl(ln_count, 0) = 0 then
1153 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1154 fnd_file.put_line(FND_FILE.LOG, '2. TDS tax is not applicable');
1155 p_process_flag := 'X';
1156 p_process_message := ' TDS tax is not applicable';
1157 goto exit_from_procedure;
1158 end if;
1159
1160 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1161 open c_check_if_processed(p_invoice_id,'P');
1162 fetch c_check_if_processed into ln_cnt_already_processed;
1163 close c_check_if_processed;
1164
1168 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1165 fnd_file.put_line(FND_FILE.LOG, '3. Check for processed already '|| ln_cnt_already_processed);
1166
1167 if nvl(ln_cnt_already_processed, 0) > 0 then
1169 p_process_flag := 'X';
1170 p_process_message := 'TDS invoices have already been processed for this invoice';
1171 goto exit_from_procedure;
1172 end if;
1173
1174
1175 /* Update actual value from default value if actual is null for TDS section taxes only*/
1176 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1177 update jai_ap_tds_inv_taxes
1178 set actual_tax_id = default_tax_id
1179 where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1180 and invoice_id = p_invoice_id
1181 and actual_tax_id is null
1182 and user_deleted_tax_flag IS NOT NULL AND user_deleted_tax_flag <> 'Y' -- nvl(user_deleted_tax_flag, 'N') <> 'Y'
1183 and section_type = lv_tds_section_type; --rchandan for bug#4428980
1184
1185
1186 /* Update processed for those cases where NO TDS has to be deducted for TDS section taxes only */
1187 update jai_ap_tds_inv_taxes
1188 set process_status = 'P'
1189 where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1190 and invoice_id = p_invoice_id
1191 and section_type = lv_tds_section_type --rchandan for bug#4428980
1192 and nvl(user_deleted_tax_flag, 'N') = 'Y';
1193
1194 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1195
1196 open c_gl_sets_of_books(p_set_of_books_id);
1197 fetch c_gl_sets_of_books into r_gl_sets_of_books;
1198 close c_gl_sets_of_books;
1199
1200 if r_gl_sets_of_books.currency_code <> p_invoice_currency_code then
1201 /* Foreign currency invoice */
1202 p_codepath := jai_general_pkg.plot_codepath(6.1, p_codepath); /* 6.1 */
1203 ln_exchange_rate := p_exchange_rate;
1204 end if;
1205
1206 ln_exchange_rate := nvl(ln_exchange_rate, 1);
1207
1208 /* Get Pan number and Tan number for the vendor */
1209 open c_get_vendor_pan_tan(p_vendor_id, p_vendor_site_id);
1210 fetch c_get_vendor_pan_tan into lv_pan_no, lv_tan_no, lv_confirm_pan_flag;
1211 close c_get_vendor_pan_tan;
1212
1213 /*START, xiao for Bug#6596019*/
1214 for c_rec1 in c_check_valid_tax(p_invoice_id) loop
1215 r_ja_in_tax_codes := null;
1216
1217 --Commented by Jia for FP Bug#7368735, Begin
1218 --------------------------------------------------------------------------
1219 /*
1220 open c_ja_in_tax_codes(nvl(c_rec1.actual_tax_id, c_rec1.default_tax_id));
1221 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
1222 close c_ja_in_tax_codes;
1223 */
1224 --------------------------------------------------------------------------
1225 --Commented by Jia for FP Bug#7368735, End
1226
1227 ln_threshold_hdr_id := 0;
1228 --open c_get_threshold(p_vendor_id ,p_vendor_site_id, r_ja_in_tax_codes.section_code,'TDS_SECTION'); --Commented by Jia for FP Bug#7368735
1229 open c_get_threshold(p_vendor_id ,p_vendor_site_id, c_rec1.section_code,'TDS_SECTION');
1230 fetch c_get_threshold into ln_threshold_hdr_id;
1231 close c_get_threshold;
1232
1233 if nvl(ln_threshold_hdr_id, 0) = 0 then
1234 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /*10 */
1235 p_process_flag := 'E';
1236 p_process_message := 'Error - Threshold is not defined for the applicable TDS section :' ||
1237 r_ja_in_tax_codes.section_code ;
1238 goto exit_from_procedure;
1239 end if;
1240
1241 ln_check_slab_exists := NULL;
1242 open c_check_slabs_end_dated(ln_threshold_hdr_id);
1243 fetch c_check_slabs_end_dated into ln_check_slab_exists;
1244 if ln_check_slab_exists IS NULL THEN
1245 p_process_flag := 'E';
1246 p_process_message := 'There are no active thresholds defined for this vendor';
1247 goto exit_from_procedure;
1248 end if;
1249 close c_check_slabs_end_dated;
1250 end loop;
1251 /*END, xiao for Bug#6596019*/
1252
1253 --Addec by Jia for FP Bug#7431371, Begin
1254 -------------------------------------------------------------------------------
1255 /*get_prepay_invoice_amt(p_invoice_id,ln_prepayment_app_amt);
1256 ln_remaining_prepayment_amount := ln_prepayment_app_amt;*/
1257 get_prepay_invoice_amt(p_invoice_id,'TDS_SECTION', ln_prepayment_app_amt); --Added for Bgowrava for Bug#6767347
1258 ln_remaining_tds_prepay_amt := ln_prepayment_app_amt;
1259 get_prepay_invoice_amt(p_invoice_id,'WCT_SECTION', ln_prepayment_app_amt); --Added for Bgowrava for Bug#6767347
1260 ln_remaining_wct_prepay_amt := ln_prepayment_app_amt;
1261 get_prepay_invoice_amt(p_invoice_id,'ESSI_SECTION', ln_prepayment_app_amt); --Added for Bgowrava for Bug#6767347
1262 ln_remaining_ess_prepay_amt := ln_prepayment_app_amt;
1263 ln_application_amount := 0;
1264 -------------------------------------------------------------------------------
1265 --Addec by Jia for FP Bug#7431371, End
1266
1267 /* start Loop through and calculate taxes for taxes of all sections */
1268 for cur_rec in c_calculate_tax(p_invoice_id) loop
1269
1270 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
1271
1272 r_ja_in_tax_codes := null;
1273 open c_ja_in_tax_codes(nvl(cur_rec.actual_tax_id,cur_rec.default_tax_id)); --Added nvl by Xiao for Bug#7154864
1274 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
1275 close c_ja_in_tax_codes;
1276
1277 /*Start-bug 10315928*/
1278 /*Fixing QA Bug 12431997 - Included in 11896260*/
1279 /*If PAN is not avaliable or if PAN Reference Number is provided tax must be deducted either at the rate given in the table or
1283 OR (lv_pan_no is NOT NULL and lv_confirm_pan_flag IS NULL))
1280 at 20%, whichever is higher. Modified the below IF clause to ensure that Tax Rate is 20% and above incase PAN is not available
1281 or PAN Reference is provided*/
1282 if((lv_pan_no in ('PANAPPLIED','PANNOTAVBL','PANINVALID')
1284 and(r_ja_in_tax_codes.tax_rate < 20)
1285 and cur_rec.section_type = 'TDS_SECTION') then
1286 p_process_flag := 'E';
1287 p_process_message := 'Invalid PAN, deduction at higher rate is not selected';
1288 goto exit_from_procedure;
1289 end if;
1290 /*End-bug 10315928*/
1291
1292 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
1293 p_codepath := jai_general_pkg.plot_codepath(7.1, p_codepath); /* 7.1 */
1294 p_process_flag := 'E';
1295 p_process_message := r_ja_in_tax_codes.tax_end_dated_message;
1296 goto exit_from_procedure;
1297 end if;
1298
1299 /* ln_tax_amount := cur_rec.amount * (r_ja_in_tax_codes.tax_rate/100 );
1300 ln_tax_amount := ln_tax_amount * ln_exchange_rate;
1301 ln_tax_amount := round(ln_tax_amount, 2);
1302 get_prepay_invoice_amt(p_invoice_id,ln_prepayment_app_amt); */ --commented by bgowrava for bug#7431371
1303
1304 --Addec by Jia for FP Bug#7431371, Begin
1305 -------------------------------------------------------------------------------
1306 ln_already_applied_amount:= 0;
1307 ln_effective_available_amount := 0;
1308 ln_application_amount := 0; /* Added for Bug 13840176 */
1309 /*Added section type as a parameter - Bug 11070443*/
1310 open c_get_amount_already_applied(cur_rec.invoice_distribution_id, cur_rec.section_type);
1311 fetch c_get_amount_already_applied into ln_already_applied_amount;
1312 close c_get_amount_already_applied;
1313
1314 ln_already_applied_amount := nvl(ln_already_applied_amount, 0);
1315 ln_effective_available_amount := cur_rec.amount - ln_already_applied_amount;
1316
1317 /*Bug 11070443 - Added section type also*/
1318 if (ln_prev_dist_id <> cur_rec.invoice_distribution_id or nvl(ln_prev_section_type, '*') <> cur_rec.section_type)and cur_rec.amount > 0 then
1319 /*ln_application_amount := least(ln_remaining_prepayment_amount, ln_effective_available_amount);*/
1320 if (cur_rec.section_type = 'TDS_SECTION') then
1321 ln_application_amount := least(ln_remaining_tds_prepay_amt, ln_effective_available_amount);
1322 elsif (cur_rec.section_type = 'WCT_SECTION') then
1323 ln_application_amount := least(ln_remaining_wct_prepay_amt, ln_effective_available_amount);
1324 elsif (cur_rec.section_type = 'ESSI_SECTION') then
1325 ln_application_amount := least(ln_remaining_ess_prepay_amt, ln_effective_available_amount);
1326 end if;
1327 end if;
1328 -------------------------------------------------------------------------------
1329 --Addec by Jia for FP Bug#7431371, End
1330
1331 ln_tax_amount := (cur_rec.amount-nvl(ln_application_amount,0)) * ln_exchange_rate * --modified by Bgowrava for bug#7431371
1332 (r_ja_in_tax_codes.tax_rate/100 ); -- xiao for Bug#6596019
1333
1334 --Addec by Jia for FP Bug#7431371, Begin
1335 -------------------------------------------------------------------------------
1336 /*Bug 11070443 - Added section type also*/
1337 if (ln_prev_dist_id <> cur_rec.invoice_distribution_id or nvl(ln_prev_section_type, '*') <> cur_rec.section_type) and cur_rec.amount > 0 then
1338 /*ln_remaining_prepayment_amount := ln_remaining_prepayment_amount - ln_application_amount; */--Bgowrava for Bug#7419533
1339 if (cur_rec.section_type = 'TDS_SECTION') then
1340 ln_remaining_tds_prepay_amt := ln_remaining_tds_prepay_amt - ln_application_amount;
1341 elsif (cur_rec.section_type = 'WCT_SECTION') then
1342 ln_remaining_wct_prepay_amt := ln_remaining_wct_prepay_amt - ln_application_amount;
1343 elsif (cur_rec.section_type = 'ESSI_SECTION') then
1344 ln_remaining_ess_prepay_amt := ln_remaining_ess_prepay_amt - ln_application_amount;
1345 end if;
1346 end if;
1347 ln_prev_section_type := cur_rec.section_type;
1348 ln_prev_dist_id := cur_rec.invoice_distribution_id;
1349 -------------------------------------------------------------------------------
1350 --Addec by Jia for FP Bug#7431371, End
1351
1352
1353 /* Bug 5722028. Added by Csahoo
1354 * Called the rounding function as we need to round depending on the
1355 * TDS rounding setup. We have a separate column calc_tax_amount
1356 * which has non-rounded value.
1357 */
1358 /* Bug 7280925. Added by Lakshmi Gopalsami
1359 Commented the following code as this is being handled in
1360 generate_tds_invoicse and maintain_thhold_Grps
1361 If r_gl_sets_of_books.currency_code = p_invoice_currency_code then
1362 ln_tmp_tds_amt := round(ln_tax_amount,g_inr_currency_rounding);
1363 else
1364 ln_tmp_tds_amt := round(ln_tax_amount,g_fcy_currency_rounding);
1365 end if ;
1366 */
1367 /* Bug 7280925. Commented by Lakshmi Gopalsami
1368 * Rounding to 10 is applicable per invoice
1369 * and not on each distribution
1370
1371 IF trunc(p_creation_date) >=
1372 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
1373 ln_tmp_tds_amt := get_rnded_value(ln_tmp_tds_amt);
1374 END IF;
1375 */
1376 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 value of tax_amount before update'||ln_tax_amount);
1377 -- End for bug 5722028.
1378 /* bug 7280925. Added by Lakshmi Gopalsami
1379 * changed from ln_tmp_tds_amt to ln_tax_amount
1380 */
1381
1382 update jai_ap_tds_inv_taxes
1383 set tax_amount = ln_tax_amount, -- ln_tmp_tds_amt, -- Bug 5722028
1387 and tds_inv_tax_id = cur_rec.tds_inv_tax_id;
1384 actual_section_code = r_ja_in_tax_codes.section_code,
1385 calc_tax_amount = ln_tax_amount --Added by Bgowrava for bug#7154864
1386 where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1388
1389 end loop;
1390 /* End Loop through and calculate taxes */
1391
1392
1393 /* Get vendor_type_lookup_code */
1394 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
1395 open c_po_vendors(p_vendor_id);
1396 fetch c_po_vendors into lv_vendor_type_lookup_code;
1397 close c_po_vendors;
1398
1399 fnd_file.put_line(FND_FILE.LOG,' 8. TDS Vendor type '|| lv_vendor_type_lookup_code);
1400
1401
1402 lv_attr_code := 'TAN NO';
1403 lv_attr_type_code := 'PRIMARY';
1404 lv_tds_regime := 'TDS';
1405 lv_regn_type_others := 'OTHERS';
1406
1407 fnd_file.put_line(FND_FILE.LOG,' 8.1 Pan number-> '|| lv_pan_no);
1408
1409 fnd_file.put_line(FND_FILE.LOG,' 8.1 Tan number-> '|| lv_tan_no);
1410
1411 /* Get the fin year */
1412 open c_get_fin_year(p_accounting_date, p_org_id);
1413 fetch c_get_fin_year into ln_fin_year;
1414 close c_get_fin_year;
1415
1416 fnd_file.put_line(FND_FILE.LOG,' 8.2 Fin Year -> '|| ln_fin_year);
1417
1418 /* Start Loop through for each tds section and process for TDS section taxes only */
1419 /* This section is meant for threshold, specific to TDS section taxes only */
1420 /*Bug 12671504 - Replaced ln_prepayment_app_amt with ln_application_amount as it is the correct Prepayment applied amount
1421 ln_prepayment_app_amt is overwritten once for each Section Type. Hence the correct application amount is lost*/
1422 for cur_rec_section in c_for_each_tds_section(p_invoice_id, ln_exchange_rate,'TDS_SECTION', nvl(ln_application_amount,0)) LOOP --rchandan for bug#4428980--add by xiao for bug#6596019
1423
1424 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1425 ln_threshold_grp_id := 0;
1426 --ln_threshold_hdr_id := 0; --commented by xiao for bug#6596019
1427 ln_threshold_slab_id_before := null;
1428 ln_threshold_slab_id_after:= null;
1429 ln_threshold_slab_id_single := null;
1430
1431 /*Uncommented by mmurtuza for bug 16435622*/
1432 open c_get_threshold
1433 (p_vendor_id , p_vendor_site_id , cur_rec_section.actual_section_code,'TDS_SECTION'); --rchandan for bug#4428980
1434 fetch c_get_threshold into ln_threshold_hdr_id;
1435 close c_get_threshold; --Commented by xiao for bug#6596019
1436
1437 fnd_file.put_line(FND_FILE.LOG,' 9. Threshold hdr id-> '|| ln_threshold_hdr_id);
1438
1439 /* if nvl(ln_threshold_hdr_id, 0) = 0 then
1440 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); -- 10
1441 fnd_file.put_line(FND_FILE.LOG, '10. Threshold is not defined for the
1442 applicable TDS section '||
1443 cur_rec_section.actual_section_code||
1444 '- Error');
1445 p_process_flag := 'E';
1446 p_process_message := 'Error - Threshold is not defined for the applicable TDS section :' ||
1447 cur_rec_section.actual_section_code ;
1448 goto exit_from_procedure;
1449 end if; */ --commented by xiao for bug#6596019
1450
1451 /* Get the threshold group id */
1452 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
1453 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
1454 fetch c_get_threshold_group into ln_threshold_grp_id;
1455 close c_get_threshold_group;
1456
1457 fnd_file.put_line(FND_FILE.LOG, '11. Threshold grp id ->'||ln_threshold_grp_id);
1458
1459 /* Get the threshold group details */
1460 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
1461 if nvl(ln_threshold_grp_id, 0) <> 0 then
1462 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
1463
1464 r_jai_ap_tds_thhold_grps := null;
1465
1466 open c_jai_ap_tds_thhold_grps(ln_threshold_grp_id);
1467 fetch c_jai_ap_tds_thhold_grps into r_jai_ap_tds_thhold_grps;
1468 close c_jai_ap_tds_thhold_grps;
1469
1470 ln_total_invoice_amount := r_jai_ap_tds_thhold_grps.total_invoice_amount;
1471
1472 else
1473 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
1474
1475 ln_total_invoice_amount := 0;
1476
1477 end if;
1478
1479 fnd_file.put_line(FND_FILE.LOG, '12. Total invoice amount -> '||
1480 ln_total_invoice_amount);
1481
1482 /* Get the threshold position before this invoice impact */
1483 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
1484
1485 r_jai_ap_tds_thhold_slabs := null; /*Added by mmurtuza for bug 16435622*/
1486
1487 open c_jai_ap_tds_thhold_slabs(ln_threshold_hdr_id , 'CUMULATIVE' , ln_total_invoice_amount);
1488 fetch c_jai_ap_tds_thhold_slabs into r_jai_ap_tds_thhold_slabs;
1489 close c_jai_ap_tds_thhold_slabs;
1490
1491 ln_threshold_slab_id_before := nvl(r_jai_ap_tds_thhold_slabs.threshold_slab_id, 0);
1492
1493 fnd_file.put_line(FND_FILE.LOG, '15. Threshold slab id before '||
1494 ln_threshold_slab_id_before);
1495
1496 /* Get the threshold position after this invoice impact */
1497 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath); /* 16 */
1498 /* FP Bug 6345725 - Removed the assignments for p_codepath without ja_in_general_pkg*/
1499 r_jai_ap_tds_thhold_slabs := null;
1500 open c_jai_ap_tds_thhold_slabs
1501 (ln_threshold_hdr_id , 'CUMULATIVE' , ln_total_invoice_amount + cur_rec_section.invoice_amount);
1502 fetch c_jai_ap_tds_thhold_slabs into r_jai_ap_tds_thhold_slabs;
1503 close c_jai_ap_tds_thhold_slabs;
1504
1505 ln_threshold_slab_id_after := nvl(r_jai_ap_tds_thhold_slabs.threshold_slab_id, 0);
1506
1507 /*start addition for FP bug 6345725 - check for active slabs. if there are no active*/
1508 /*slabs throw an error message*/
1509 /*ln_check_slab_exists := NULL;
1510 open c_check_slabs_end_dated(ln_threshold_hdr_id);
1511 fetch c_check_slabs_end_dated into ln_check_slab_exists;
1512 if ln_check_slab_exists IS NULL THEN
1513 p_process_flag := 'E';
1514 p_process_message := 'There are no active thresholds defined for this vendor';
1515 goto exit_from_procedure;
1516 end if;
1517 close c_check_slabs_end_dated; */ --commented by bgowrava for bug#6596019
1518 /*end addition for bug 6345725*/
1519
1520
1521 fnd_file.put_line(FND_FILE.LOG, '16. Threshold slab id after ->'||
1522 ln_threshold_slab_id_after);
1523 --p_codepath := p_codepath || to_char(ln_threshold_slab_id_after) || '**'; --commented by Bgowrava for Bug#8716477
1524 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath); /* 17 */
1525 if ln_threshold_slab_id_after <> 0 then
1526 /* Threshold has reached */
1527 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath); /* 18 */
1528 lv_generate_all_invoices := 'Y';
1529 else
1530 lv_generate_all_invoices := 'N';
1531 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath); /* 19 */
1532 end if; /* if ln_threshold_slab_id_after <> 0 */
1533
1534 fnd_file.put_line(FND_FILE.LOG, '19. Generate invoices -> ' ||
1535 lv_generate_all_invoices);
1536
1537 /* Check for Single Invoice threshold if cumulative has not been reached */
1538 if lv_generate_all_invoices = 'N' then
1539 /* Cumulative threshold not reached */
1540 r_jai_ap_tds_thhold_slabs := null;
1541 open c_jai_ap_tds_thhold_slabs(ln_threshold_hdr_id , 'SINGLE' , cur_rec_section.invoice_amount);
1542 fetch c_jai_ap_tds_thhold_slabs into r_jai_ap_tds_thhold_slabs;
1543 close c_jai_ap_tds_thhold_slabs;
1544 ln_threshold_slab_id_single := nvl(r_jai_ap_tds_thhold_slabs.threshold_slab_id, 0);
1545 end if;
1546
1547 /* Loop and generate invoices */
1548
1549 if nvl(ln_threshold_grp_id, 0) = 0 then
1550
1551 p_codepath := jai_general_pkg.plot_codepath(19.1, p_codepath); /* 19.1 */
1552
1553 fnd_file.put_line(FND_FILE.LOG, '19.1 Call maintain thhold grps ');
1554
1555 jai_ap_tds_generation_pkg.maintain_thhold_grps
1556 (
1557 p_threshold_grp_id => ln_threshold_grp_id,
1558 p_vendor_id => p_vendor_id,
1559 p_org_tan_num => lv_tan_no,
1560 p_vendor_pan_num => lv_pan_no,
1561 p_section_type => 'TDS_SECTION',
1562 p_section_code => cur_rec_section.actual_section_code,
1563 p_fin_year => ln_fin_year,
1564 p_org_id => p_org_id,
1565 p_trx_invoice_amount => cur_rec_section.invoice_amount,
1566 p_tds_event => 'INVOICE VALIDATE',
1567 p_invoice_id => p_invoice_id,
1568 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
1569 -- Bug 5722028. Added by CSahoo
1570 p_creation_Date => p_creation_date,
1571 p_process_flag => p_process_flag,
1572 P_process_message => P_process_message,
1573 p_codepath => p_codepath
1574 );
1575
1576 fnd_file.put_line(FND_FILE.LOG, '19.1 Process flag '|| p_process_flag);
1577 fnd_file.put_line(FND_FILE.LOG, '19.1 Process message '|| p_process_message);
1578
1579 else
1580
1581 p_codepath := jai_general_pkg.plot_codepath(19.2, p_codepath); /* 19.2 */
1582
1583 jai_ap_tds_generation_pkg.maintain_thhold_grps
1584 (
1585 p_threshold_grp_id => ln_threshold_grp_id,
1586 p_trx_invoice_amount => cur_rec_section.invoice_amount,
1587 p_tds_event => 'INVOICE VALIDATE',
1588 p_invoice_id => p_invoice_id,
1589 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
1593 );
1590 p_process_flag => p_process_flag,
1591 P_process_message => P_process_message,
1592 p_codepath => p_codepath
1594
1595 fnd_file.put_line(FND_FILE.LOG, '19.2 Process flag '|| p_process_flag);
1596 fnd_file.put_line(FND_FILE.LOG, '19.2 Process message '|| p_process_message);
1597
1598 end if;
1599
1600 --Added by Sanjikum for Bug#5131075(4722011)
1601 IF p_process_flag = 'E' THEN
1602 p_codepath := jai_general_pkg.plot_codepath(19.3, p_codepath); /* 19.3 */
1603 goto exit_from_procedure;
1604 END IF;
1605
1606 /* Generate TDS invoices by taxes under the section */
1607 /*Bug 12671504 - Replaced ln_prepayment_app_amt with ln_application_amount*/
1608 for cur_rec in
1609 c_get_taxes_to_generate_tds
1610 (p_invoice_id , cur_rec_section.actual_section_code, lv_generate_all_invoices,
1611 ln_exchange_rate, ln_threshold_slab_id_single,'TDS_SECTION', nvl(ln_application_amount,0)) LOOP --rchandan for bug#4428980
1612
1613 p_codepath := jai_general_pkg.plot_codepath(20, p_codepath); /* 20 */
1614
1615 /*START, Bgowrava for Bug# 8254510*/
1616 ln_tds_tax_amount := cur_rec.tax_amount;
1617 if ln_threshold_slab_id_before <> ln_threshold_slab_id_after then
1618
1619 open c_jai_slab_start_amt(ln_threshold_slab_id_after);
1620 fetch c_jai_slab_start_amt into r_jai_slab_start_amt_after;
1621 close c_jai_slab_start_amt;
1622
1623 open c_jai_slab_start_amt(ln_threshold_slab_id_before);
1624 fetch c_jai_slab_start_amt into r_jai_slab_start_amt_before;
1625 close c_jai_slab_start_amt;
1626
1627 if ln_threshold_slab_id_before <> 0 and r_jai_slab_start_amt_before.tax_rate_orig <> r_jai_slab_start_amt_after.tax_rate_orig then
1628 ln_tds_amt_before := r_jai_slab_start_amt_after.from_amount - ln_total_invoice_amount;
1629 ln_tds_amt_after := cur_rec.taxable_amount - ln_tds_amt_before;
1630
1631 ln_tds_tax_amount := (ln_tds_amt_before*(nvl(r_jai_slab_start_amt_before.tax_rate, 0)/100))
1632 + (ln_tds_amt_after*(nvl(r_jai_slab_start_amt_after.tax_rate, 0)/100));
1633 end if;
1634 end if;
1635 /*END, Bgowrava for Bug#8254510 */
1636
1637 ln_threshold_trx_id := 0;
1638 lv_tds_invoice_num := null;
1639 lv_tds_cm_num := null;
1640 p_process_flag := null;
1641
1642 fnd_file.put_line(FND_FILE.LOG, '20. Call generate tds invoices' );
1643
1644 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - tax amount while calling generate tds invoices '|| cur_rec.tax_amount);
1645
1646 jai_ap_tds_generation_pkg.generate_tds_invoices
1647 (
1648 pn_invoice_id => p_invoice_id ,
1649 pn_threshold_hdr_id => ln_threshold_hdr_id ,
1650 pn_taxable_amount => cur_rec.taxable_amount ,
1651 pn_tax_amount => ln_tds_tax_amount , --Added by Bgowrava for Bug#8254510
1652 pn_tax_id => cur_rec.actual_tax_id ,
1653 pd_accounting_date => p_accounting_date ,
1654 pv_tds_event => 'INVOICE VALIDATE' ,
1655 pn_threshold_grp_id => ln_threshold_grp_id ,
1656 pv_tds_invoice_num => lv_tds_invoice_num ,
1657 pv_cm_invoice_num => lv_tds_cm_num ,
1658 pn_threshold_trx_id => ln_threshold_trx_id ,
1659 -- Bug 5722028. Added by CSahoo
1660 pd_creation_date => p_creation_date ,
1661 pn_calc_tax_amount => 0, /* Added for bug 12965614 */
1662 p_process_flag => p_process_flag ,
1663 p_process_message => p_process_message
1664 );
1665
1666
1667 if p_process_flag = 'E' then
1668 fnd_file.put_line(FND_FILE.LOG, '20 Process flag '|| p_process_flag);
1669 fnd_file.put_line(FND_FILE.LOG, '20 Process message '|| p_process_message);
1670 p_codepath := jai_general_pkg.plot_codepath(21, p_codepath); /* 21 */
1671 goto exit_from_procedure;
1672 end if;
1673
1674 p_codepath := jai_general_pkg.plot_codepath(22, p_codepath); /* 22 */
1675
1676 if ln_start_threshold_trx_id is null then
1677 ln_start_threshold_trx_id := ln_threshold_trx_id;
1678 end if;
1679
1680 fnd_file.put_line(FND_FILE.LOG,' 22. start thhold trx id '||
1681 ln_start_threshold_trx_id);
1682
1683 /* Bug 7280925. Added by Lakshmi Gopalsami -- can be removed
1684 * Need to round the value before calling maintain_thhold_grps
1685
1686 IF trunc(p_creation_date) >=
1687 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
1688 ln_tmp_tds_amt := get_rnded_value(cur_rec.tax_amount);
1689 END IF;
1690 */
1691
1692 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value of tmp tds before maintain thhold grps'||ln_tax_amount);
1693
1694 /* Update the total tax amount for which invoice was raised */
1695
1696 /* bug 7280925. Added by Lakshmi Gopalsami
1697 * changed from ln_tmp_tds_amt to ln_tax_amount
1698 */
1699 p_codepath := jai_general_pkg.plot_codepath(23, p_codepath); /* 23 */
1700
1701 jai_ap_tds_generation_pkg.maintain_thhold_grps
1702 (
1703 p_threshold_grp_id => ln_threshold_grp_id,
1704 p_trx_tax_paid => ln_tds_tax_amount,
1705 p_tds_event => 'INVOICE VALIDATE', --Added by Bgowrava for Bug#8254510
1706 p_invoice_id => p_invoice_id,
1710 p_process_flag => p_process_flag,
1707 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
1708 -- Bug 5722028. Added by Lakshmi Gopalsami
1709 p_creation_date => p_creation_date,
1711 P_process_message => P_process_message,
1712 p_codepath => p_codepath
1713 );
1714
1715 fnd_file.put_line(FND_FILE.LOG, '23 Process flag '|| p_process_flag);
1716 fnd_file.put_line(FND_FILE.LOG, '23 Process message '|| p_process_message);
1717
1718 --Added by Sanjikum for Bug#5131075(4722011)
1719 IF p_process_flag = 'E' THEN
1720 p_codepath := jai_general_pkg.plot_codepath(23.1, p_codepath); /* 23.1 */
1721 goto exit_from_procedure;
1722 END IF;
1723
1724
1725 /* Punch threshold_trx_id in jai_ap_tds_inv_taxes */
1726 update jai_ap_tds_inv_taxes
1727 set threshold_trx_id = ln_threshold_trx_id,
1728 threshold_slab_id_single = ln_threshold_slab_id_single
1729 where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1730 and invoice_id = p_invoice_id
1731 and section_type = lv_tds_section_type --rchandan for bug#4428980
1732 and actual_section_code = cur_rec_section.actual_section_code
1733 and nvl(actual_tax_id, default_tax_id) = cur_rec.actual_tax_id --Added nvl by Xiao for bug#7154864
1734 and (
1735 (lv_generate_all_invoices = 'Y' )
1736 or
1737 (ln_threshold_slab_id_single > 0)
1738 or
1739 (actual_tax_id is NOT NULL) --added by Xiao for bug#7154864
1740 );
1741
1742 ln_no_of_tds_inv_generated := ln_no_of_tds_inv_generated + 2;
1743 /* TDS invoices are always generated in pair */
1744
1745 p_codepath := jai_general_pkg.plot_codepath(24, p_codepath); /* 24 */
1746 end loop;
1747 /* Loop and generate invoices */
1748
1749 p_codepath := jai_general_pkg.plot_codepath(25, p_codepath); /* 25 */
1750 update jai_ap_tds_inv_taxes
1751 set threshold_grp_id = ln_threshold_grp_id,
1752 threshold_hdr_id = ln_threshold_hdr_id,
1753 threshold_slab_id = ln_threshold_slab_id_after,
1754 process_status = 'P'
1755 where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1756 and invoice_id = p_invoice_id
1757 and section_type = lv_tds_section_type --rchandan for bug#4428980
1758 and actual_section_code = cur_rec_section.actual_section_code;
1759
1760 if ln_threshold_slab_id_before <> ln_threshold_slab_id_after then
1761 /* Transition in threshold has happened */
1762 p_codepath := jai_general_pkg.plot_codepath(26, p_codepath); /* 26 */
1763
1764 --4407184
1765 lv_slab_transition_tds_event := 'THRESHOLD TRANSITION(from slab id -' || ln_threshold_slab_id_before ||
1766 'to slab id - ' || ln_threshold_slab_id_after || ')';
1767
1768 fnd_file.put_line(FND_FILE.LOG, '26. Call process transition ');
1769 fnd_file.put_line(FND_FILE.LOG, '26. Event is '|| lv_slab_transition_tds_event);
1770
1771 process_threshold_transition
1772 (
1773 p_threshold_grp_id => ln_threshold_grp_id,
1774 p_threshold_slab_id => ln_threshold_slab_id_after,
1775 p_invoice_id => p_invoice_id,
1776 p_vendor_id => p_vendor_id,
1777 p_vendor_site_id => p_vendor_site_id,
1778 p_accounting_date => p_accounting_date,
1779 p_tds_event => lv_slab_transition_tds_event,
1780 p_org_id => p_org_id,
1781 pv_tds_invoice_num => lv_tds_invoice_num,
1782 pv_cm_invoice_num => lv_tds_cm_num,
1783 p_process_flag => p_process_flag,
1784 p_process_message => p_process_message
1785 );
1786
1787 if p_process_flag = 'E' then
1788 fnd_file.put_line(FND_FILE.LOG, '27 Process flag '|| p_process_flag);
1789 fnd_file.put_line(FND_FILE.LOG, '27 Process message '|| p_process_message);
1790
1791 p_codepath := jai_general_pkg.plot_codepath(27, p_codepath); /* 27 */
1792 goto exit_from_procedure;
1793 end if;
1794 ln_no_of_tds_inv_generated := ln_no_of_tds_inv_generated + 2;
1795 p_codepath := jai_general_pkg.plot_codepath(28, p_codepath); /* 28 */
1796 end if;
1797
1798 p_codepath := jai_general_pkg.plot_codepath(29, p_codepath); /* 29 */
1799 end loop;
1800
1801 /* End Loop through for each tds section and process */
1802
1803 p_codepath := jai_general_pkg.plot_codepath(30, p_codepath); /* 30 */
1804 /* Check if any non-TDS Section taxes are applicable and generate invoices if required. */
1805 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
1806
1807 p_codepath := jai_general_pkg.plot_codepath(31, p_codepath); /* 31 */
1808 ln_threshold_trx_id := null;
1809 lv_tds_invoice_num := null;
1810 lv_tds_cm_num := null;
1811 p_process_flag := null;
1812
1813 fnd_file.put_line(FND_FILE.LOG, '31 Call generate tds invoices ');
1814
1815 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - WCT Tax amt '||cur_non_tds_rec.tax_amount);
1816
1817 jai_ap_tds_generation_pkg.generate_tds_invoices
1818 (
1819 pn_invoice_id => p_invoice_id ,
1820 pn_threshold_hdr_id => ln_threshold_hdr_id ,
1824 pd_accounting_date => p_accounting_date ,
1821 pn_taxable_amount => cur_non_tds_rec.taxable_amount ,
1822 pn_tax_amount => cur_non_tds_rec.tax_amount ,
1823 pn_tax_id => cur_non_tds_rec.actual_tax_id ,
1825 pv_tds_event => 'INVOICE VALIDATE' ,
1826 pn_threshold_grp_id => null ,
1827 pv_tds_invoice_num => lv_tds_invoice_num ,
1828 pv_cm_invoice_num => lv_tds_cm_num ,
1829 pn_threshold_trx_id => ln_threshold_trx_id ,
1830 -- Bug 5722028. Added by csahoo
1831 pd_creation_date => p_creation_date ,
1832 pn_calc_tax_amount => 0, /* Added for bug 12965614 */
1833 p_process_flag => p_process_flag ,
1834 p_process_message => p_process_message
1835 );
1836
1837 if p_process_flag = 'E' then
1838 fnd_file.put_line(FND_FILE.LOG, '31 Process flag '|| p_process_flag);
1839 fnd_file.put_line(FND_FILE.LOG, '31 Process message '|| p_process_message);
1840
1841 p_codepath := jai_general_pkg.plot_codepath(32, p_codepath); /* 32 */
1842 goto exit_from_procedure;
1843 end if;
1844
1845 p_codepath := jai_general_pkg.plot_codepath(33, p_codepath); /* 33 */
1846
1847 if ln_start_threshold_trx_id is null then
1848 p_codepath := jai_general_pkg.plot_codepath(34, p_codepath); /* 34 */
1849 ln_start_threshold_trx_id := ln_threshold_trx_id;
1850 end if;
1851
1852 fnd_file.put_line(FND_FILE.LOG, '34. Start thhold trx id '|| ln_start_threshold_trx_id);
1853
1854 /* Punch threshold_trx_id in jai_ap_tds_inv_taxes */
1855 update jai_ap_tds_inv_taxes
1856 set threshold_trx_id = ln_threshold_trx_id,
1857 process_status = 'P' /*Bug 4667681*/
1858 where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
1859 and invoice_id = p_invoice_id
1860 and section_type = cur_non_tds_rec.section_type
1861 and actual_tax_id = cur_non_tds_rec.actual_tax_id;
1862
1863 end loop; /* cur_non_tds_rec */
1864
1865
1866 p_codepath := jai_general_pkg.plot_codepath(35, p_codepath); /* 35 */
1867
1868
1869 /* If the process is called from batch do not fire import request */
1870
1871 fnd_file.put_line(FND_FILE.LOG, '35. called from '|| p_call_from);
1872
1873 if p_call_from <> 'BATCH' then
1874 /* Not Called from Batch */
1875
1876 p_codepath := jai_general_pkg.plot_codepath(36, p_codepath); /* 36 */
1877
1878 if ln_start_threshold_trx_id is not null then
1879
1880 fnd_file.put_line(FND_FILE.LOG, '36 start thhold trx id '||
1881 ln_start_threshold_trx_id);
1882
1883 p_codepath := jai_general_pkg.plot_codepath(37, p_codepath); /* 37 */
1884 import_and_approve
1885 (
1886 p_invoice_id => p_invoice_id,
1887 p_start_thhold_trx_id => ln_start_threshold_trx_id,
1888 p_tds_event => 'INVOICE VALIDATE',
1889 p_process_flag => p_process_flag,
1890 p_process_message => p_process_message
1891 );
1892
1893 fnd_file.put_line(FND_FILE.LOG, '37 Process flag '|| p_process_flag);
1894 fnd_file.put_line(FND_FILE.LOG, '37 Process message '|| p_process_message);
1895
1896 --Added by Sanjikum for Bug#5131075(4722011)
1897 IF p_process_flag = 'E' THEN
1898 p_codepath := jai_general_pkg.plot_codepath(37.1, p_codepath); /* 37.1 */
1899 goto exit_from_procedure;
1900 END IF;
1901
1902 end if; /* if ln_no_of_tds_inv_generated > 0 then */
1903
1904 end if; /* p_call_from <> 'BATCH' then */
1905
1906 <<exit_from_procedure>>
1907 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath); /* 100 */
1908 return;
1909
1910 exception
1911 when others then
1912 /*Added below by Bgowrava for Bug#8716477 */
1913 p_process_flag := 'E';
1914 P_process_message := 'jai_ap_tds_generation_pkg.process_tds_at_inv_validate :' || sqlerrm;
1915 return;
1916 end process_tds_at_inv_validate;
1917 /* ************************************* process_tds_at_inv_validate ************************************ */
1918
1919 /* *********************************** procedure generate_tds_invoices ********************************** */
1920
1921 procedure generate_tds_invoices
1922 (
1923 pn_invoice_id in number,
1924 pn_invoice_line_number in number default null, /* AP lines */
1925 pn_invoice_distribution_id in number default null, /* Prepayment apply / unapply scenario */
1926 pv_invoice_num_prepay_apply in varchar2 default null, /* Prepayment application secanrio */
1927 pv_invoice_num_to_tds_apply in varchar2 default null, /* Prepayment unapplication secanrio */
1928 pv_invoice_num_to_vendor_apply in varchar2 default null, /* Prepayment unapplication secanrio */
1929 pv_invoice_num_to_vendor_can in varchar2 default null, /* Invoice Cancel Secnario */
1930 pn_threshold_hdr_id in number default null, /* For validate scenario only */
1931 pn_taxable_amount in number,
1932 pn_tax_amount in number,
1933 pn_tax_id in number,
1934 pd_accounting_date in date,
1938 pv_cm_invoice_num out nocopy varchar2,
1935 pv_tds_event in varchar2,
1936 pn_threshold_grp_id in number,
1937 pv_tds_invoice_num out nocopy varchar2,
1939 pn_threshold_trx_id out nocopy number,
1940 -- Bug 5722028. Added by csahoo
1941 pd_creation_date in date,
1942 pn_calc_tax_amount in number default 0, /* Added for bug 12965614 */
1943 p_process_flag out nocopy varchar2,
1944 p_process_message out nocopy varchar2
1945 )
1946 is
1947
1948 cursor c_ap_invoices_all(cp_invoice_id number) is
1949 select invoice_num,
1950 vendor_id,
1951 vendor_site_id,
1952 invoice_currency_code,
1953 exchange_rate_type,
1954 exchange_date,
1955 terms_id,
1956 payment_method_lookup_code,
1957 pay_group_lookup_code,
1958 invoice_date,
1959 goods_received_date,
1960 invoice_received_date,
1961 org_id,
1962 nvl(exchange_rate, 1) exchange_rate,
1963 set_of_books_id,
1964 payment_method_code, -- Bug 7109056
1965 accts_pay_code_combination_id -- Bug 9759709
1966 from ap_invoices_all
1967 where invoice_id = cp_invoice_id;
1968
1969 cursor c_po_vendor_sites_all(cp_vendor_id number, cp_vendor_site_id number) is
1970 select terms_id,
1971 --payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
1972 pay_group_lookup_code
1973 from po_vendor_sites_all
1974 where vendor_id = cp_vendor_id
1975 and vendor_site_id = cp_vendor_site_id;
1976
1977 cursor c_po_vendors(cp_vendor_id number) is
1978 select terms_id,
1979 --payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
1980 pay_group_lookup_code
1981 from po_vendors
1982 where vendor_id = cp_vendor_id;
1983
1984
1985 cursor c_ja_in_tax_codes (pn_tax_id number) is
1986 select section_code,
1987 vendor_id,
1988 vendor_site_id,
1989 tax_rate,
1990 stform_type,
1991 tax_account_id,
1992 section_type
1993 from JAI_CMN_TAXES_ALL
1994 where tax_id = pn_tax_id;
1995
1996
1997 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
1998 select currency_code
1999 from gl_sets_of_books
2000 where set_of_books_id = cp_set_of_books_id;
2001
2002 cursor c_get_ja_in_ap_inv_id is
2003 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
2004 from dual;
2005
2006 cursor c_ap_payment_schedules_all(p_invoice_id number) is
2007 select payment_priority
2008 from ap_payment_schedules_all
2009 where invoice_id = p_invoice_id;
2010
2011 r_ap_invoices_all c_ap_invoices_all%rowtype;
2012 r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
2013 r_po_vendor_sites_all c_po_vendor_sites_all%rowtype;
2014 r_po_vendors c_po_vendors%rowtype;
2015 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
2016 r_ap_payment_schedules_all c_ap_payment_schedules_all%rowtype;
2017
2018
2019 lv_source varchar2(30); --File.Sql.35 Cbabu := 'TDS';
2020
2021 lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
2022 lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
2023
2024 lv_invoice_to_tds_type ap_invoices_all.invoice_type_lookup_code%type;
2025 lv_invoice_to_vendor_type ap_invoices_all.invoice_type_lookup_code%type;
2026
2027 ln_invoice_to_tds_id ap_invoices_all.invoice_id%type;
2028 ln_invoice_to_vendor_id ap_invoices_all.invoice_id%type;
2029
2030 ln_invoice_to_tds_line_id ap_invoice_lines_interface.invoice_line_id%type;
2031 ln_invoice_to_vendor_line_id ap_invoice_lines_interface.invoice_line_id%type;
2032
2033 lv_invoice_to_tds_line_type ap_invoice_distributions_all.line_type_lookup_code%type; --File.Sql.35 Cbabu := 'ITEM';
2034 lv_invoice_to_vendor_line_type ap_invoice_distributions_all.line_type_lookup_code%type; --File.Sql.35 Cbabu := 'ITEM';
2035
2036 ln_invoice_to_tds_amount number;
2037 ln_invoice_to_vendor_amount number;
2038
2039 ln_exchange_rate number;
2040 lv_this_procedure varchar2(50); --File.Sql.35 Cbabu := 'jaiap.generate_tds_invoice';
2041
2042 ln_terms_id po_vendors.terms_id%type;
2043 -- lv_payment_method_lookup_code po_vendors.payment_method_lookup_code%type; --commented by Sanjikum for Bug#4482462
2044 lv_pay_group_lookup_code po_vendors.pay_group_lookup_code%type;
2045
2046 lv_ja_in_ap_inv_id varchar2(15);
2047 ld_accounting_date date;
2048 lv_open_period ap_invoice_distributions_all.period_name%type;
2049 ln_tax_amount number;
2050
2051 lv_invoice_num ap_invoices_all.invoice_num%type;
2052 lv_source_attribute jai_ap_tds_invoices.source_attribute%TYPE ; --rchandan for bug#4428980
2053
2054 ln_invoice_amount ap_invoices_all.invoice_amount%TYPE; --Added by Ramananda for Bug#4562801
2055
2056 lv_group_id VARCHAR2(80); --Added by Sanjikum for Bug#5131075(4722011)
2057
2058 /* Bug 5722028. Added by Lakshmi Gopalsami
2059 * Added following variables
2060 */
2061 ln_tds_rnded_amt NUMBER;
2062 ln_tds_mod_value NUMBER;
2063 ln_tds_rnding_factor NUMBER;
2064
2068
2065 lv_section_name varchar2(10); --Added for Bug# 7410219
2066 pv_invoice_date DATE; --Added by Bgowrava for Bug#9186263
2067 ln_accts_pay_ccid NUMBER; -- Bug 9759709
2069 begin
2070
2071 lv_source := 'INDIA TDS'; /* --:= 'TDS'; --Ramanand for bug#4388958 */
2072 lv_invoice_to_tds_line_type := 'ITEM';
2073 lv_invoice_to_vendor_line_type := 'ITEM';
2074 lv_this_procedure := 'jaiap.generate_tds_invoice';
2075
2076
2077 /* Amount to be paid to TDS Authority should always be +ve */
2078 /* In case of prepayment application, this is still passed as +ve amount */
2079
2080 /* Bug 4513458. added by Lakshmi Gopalsami
2081 * Removed the rounding and assigned the exact amount
2082 * and the rounding is handled at later point to
2083 * accommodate the currency code
2084 */
2085 --ln_tax_amount := round(pn_tax_amount, 2);
2086 ln_tax_amount := pn_tax_amount;
2087
2088 /* Bug 4522507. Added by Lakshmi Gopalsami
2089 Checked whether round(ln_tax_amount) is less than
2090 zero instead of ln_tax_amount */
2091 if round(ln_tax_amount,2) <= 0 then
2092 p_process_flag := 'X';
2093 p_process_message := 'TDS amount must be greater than 0 ';
2094 goto exit_from_procedure;
2095 end if;
2096
2097 open c_ap_invoices_all(pn_invoice_id);
2098 fetch c_ap_invoices_all into r_ap_invoices_all;
2099 close c_ap_invoices_all;
2100
2101 /*
2102 || moved this up from the under the cursor - c_po_vendor_sites_all by Ramananda for Bug#4562793
2103 */
2104 open c_ja_in_tax_codes(pn_tax_id);
2105 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
2106 close c_ja_in_tax_codes;
2107
2108 /*
2109 || open c_po_vendors(r_ap_invoices_all.vendor_id);
2110 || Commented the above and added the below by Ramananda for Bug#4562793
2111 */
2112 open c_po_vendors(r_ja_in_tax_codes.vendor_id);
2113 fetch c_po_vendors into r_po_vendors;
2114 close c_po_vendors;
2115
2116 /*
2117 || open c_po_vendor_sites_all(r_ap_invoices_all.vendor_id, r_ap_invoices_all.vendor_site_id);
2118 || Commented the above and added the below by sanjikum for Bug#4562793
2119 */
2120 open c_po_vendor_sites_all(r_ja_in_tax_codes.vendor_id, r_ja_in_tax_codes.vendor_site_id);
2121 fetch c_po_vendor_sites_all into r_po_vendor_sites_all;
2122 close c_po_vendor_sites_all;
2123
2124 open c_gl_sets_of_books(r_ap_invoices_all.set_of_books_id);
2125 fetch c_gl_sets_of_books into r_gl_sets_of_books;
2126 close c_gl_sets_of_books;
2127
2128 /*Bug # 7410219 - Derive the Section Name*/
2129 if (r_ja_in_tax_codes.section_type = 'TDS_SECTION') then
2130 lv_section_name := 'TDS';
2131 elsif (r_ja_in_tax_codes.section_type = 'WCT_SECTION') then
2132 lv_section_name := 'WCT';
2133 elsif (r_ja_in_tax_codes.section_type = 'ESSI_SECTION') then
2134 lv_section_name := 'ESSI';
2135 end if;
2136 /*Bug # 7410219 - End*/
2137
2138 /* Get the payment details from the vendor site */
2139 ln_terms_id := r_po_vendor_sites_all.terms_id;
2140 -- lv_payment_method_lookup_code := r_po_vendor_sites_all.payment_method_lookup_code;--commented by Sanjikum for Bug#4482462
2141 lv_pay_group_lookup_code := r_po_vendor_sites_all.pay_group_lookup_code;
2142
2143
2144 if (
2145 ln_terms_id is null or
2146 -- lv_payment_method_lookup_code is null or --commented by Sanjikum for Bug#4482462
2147 lv_pay_group_lookup_code is null
2148 )
2149 then
2150
2151 /* Get the payment details from the vendor as it has been not defined for the site */
2152 ln_terms_id := r_po_vendors.terms_id;
2153 -- lv_payment_method_lookup_code := r_po_vendors.payment_method_lookup_code; --commented by Sanjikum for Bug#4482462
2154 lv_pay_group_lookup_code := r_po_vendors.pay_group_lookup_code;
2155
2156 end if;
2157
2158
2159 /* Get the unique number to suffix the tds invoices with */
2160 open c_get_ja_in_ap_inv_id;
2161 fetch c_get_ja_in_ap_inv_id into lv_ja_in_ap_inv_id;
2162 close c_get_ja_in_ap_inv_id;
2163
2164 lv_invoice_num := substr(r_ap_invoices_all.invoice_num, 1, 30);
2165
2166 /* Invoice Numbers, type for the invoice pair that is being created */
2167 if ( (pv_tds_event = 'INVOICE VALIDATE') or (pv_tds_event like 'THRESHOLD TRANSITION%') ) then
2168
2169 /* Standard invoice to TDS authority, Credit memo to supplier */
2170
2171 lv_invoice_to_tds_type := 'STANDARD';
2172 lv_invoice_to_vendor_type := 'CREDIT';
2173 /*Bug 7410219 - Modified Invoice Number as per Section*/
2174 lv_invoice_to_tds_num := lv_invoice_num ||'-'||lv_section_name||'-SI-'||lv_ja_in_ap_inv_id;
2175 lv_invoice_to_vendor_num := lv_invoice_num ||'-'||lv_section_name||'-CM-'||lv_ja_in_ap_inv_id;
2176
2177 ln_invoice_to_tds_amount := ln_tax_amount;
2178 ln_invoice_to_vendor_amount := (-1) * ln_tax_amount;
2179
2180 elsif pv_tds_event = 'PREPAYMENT APPLICATION' OR pv_tds_event like 'THRESHOLD ROLLBACK%' then --Added by Sanjikum for Bug#5131075(4718907)
2181
2182 /* Credit memo to TDS authority, Standard invoice to supplier */
2183 if pv_invoice_num_prepay_apply is not null then
2184 lv_invoice_num := substr(pv_invoice_num_prepay_apply, 1, 30);
2185 end if;
2186
2187 lv_invoice_to_tds_type := 'CREDIT';
2188 lv_invoice_to_vendor_type := 'STANDARD';
2189 /*Bug 7410219 - Modified Invoice Number as per Section*/
2190 lv_invoice_to_tds_num := lv_invoice_num ||'-RTN-'||lv_section_name||'-CM-'||lv_ja_in_ap_inv_id;
2191 lv_invoice_to_vendor_num := lv_invoice_num ||'-RTN-'||lv_section_name||'-SI-'||lv_ja_in_ap_inv_id;
2192
2193 ln_invoice_to_tds_amount := -1 * ln_tax_amount;
2197
2194 ln_invoice_to_vendor_amount := ln_tax_amount;
2195
2196 elsif pv_tds_event = 'PREPAYMENT UNAPPLICATION' then
2198 /* Standard invoice to TDS authority, Credit memo to supplier */
2199 lv_invoice_to_tds_type := 'STANDARD';
2200 lv_invoice_to_vendor_type := 'CREDIT';
2201
2202 if pv_invoice_num_to_tds_apply is not null then
2203 lv_invoice_to_tds_num := 'CAN/' || substr(pv_invoice_num_to_tds_apply, 1, 45);
2204 else
2205 /*Bug 7410219 - Modified Invoice Number as per Section*/
2206 lv_invoice_to_tds_num := lv_invoice_num ||'-RTN-'||lv_section_name||'-SI-'||lv_ja_in_ap_inv_id;
2207 end if;
2208
2209 if pv_invoice_num_to_vendor_apply is not null then
2210 lv_invoice_to_vendor_num := 'CAN/' || substr(pv_invoice_num_to_vendor_apply, 1, 45);
2211 else
2212 /*Bug 7410219 - Modified Invoice Number as per Section*/
2213 lv_invoice_to_vendor_num := lv_invoice_num ||'-RTN-'||lv_section_name||'-CM-'||lv_ja_in_ap_inv_id;
2214 end if;
2215
2216 ln_invoice_to_tds_amount := ln_tax_amount;
2217 ln_invoice_to_vendor_amount := (-1) * ln_tax_amount;
2218
2219 elsif pv_tds_event = 'INVOICE CANCEL' then
2220
2221 /* No invoice to TDS authority, Standard invoice to supplier */
2222
2223 lv_invoice_to_tds_num := null;
2224
2225 if pv_invoice_num_to_vendor_can is not null then
2226 lv_invoice_to_vendor_num := 'CAN/' || substr(pv_invoice_num_to_vendor_can, 1, 45);
2227 else
2228 /*Bug 7410219 - Modified Invoice Number as per Section*/
2229 lv_invoice_to_vendor_num := lv_invoice_num||'-CAN-'||lv_section_name||'-SI-'||lv_ja_in_ap_inv_id;
2230 end if;
2231
2232 lv_invoice_to_tds_type := null;
2233 lv_invoice_to_vendor_type := 'STANDARD';
2234
2235 ln_invoice_to_tds_amount := null;
2236 ln_invoice_to_vendor_amount := ln_tax_amount;
2237
2238 /*START, Bgowrava for Bug#8254510*/
2239 elsif ( pv_tds_event = 'SURCHARGE_CALCULATE') then
2240
2241 /* Standard invoice to TDS authority, Credit memo to supplier */
2242
2243 lv_invoice_to_tds_type := 'STANDARD';
2244 lv_invoice_to_vendor_type := 'CREDIT';
2245
2246 lv_invoice_to_tds_num := lv_invoice_num ||'-SUR-SI-'||lv_ja_in_ap_inv_id;
2247 lv_invoice_to_vendor_num := lv_invoice_num ||'-SUR-CM-'||lv_ja_in_ap_inv_id;
2248
2249 ln_invoice_to_tds_amount := ln_tax_amount;
2250 ln_invoice_to_vendor_amount := (-1) * ln_tax_amount;
2251 /*END, Bgowrava for Bug#8254510*/
2252
2253 end if; /* TDS event type */
2254
2255 pv_tds_invoice_num := lv_invoice_to_tds_num;
2256 pv_cm_invoice_num := lv_invoice_to_vendor_num;
2257
2258 /* Check if the given date is in current open period */
2259
2260 /* Bug 4559756. Added by Lakshmi Gopalsami
2261 Added org_id to ap_utilities_pkg
2262 */
2263 lv_open_period:= ap_utilities_pkg.get_current_gl_date
2264 (pd_accounting_date,
2265 r_ap_invoices_all.org_id
2266 );
2267
2268 /* Bug 4559756. Added by Lakshmi Gopalsami
2269 Added org_id to ap_utilities_pkg
2270 */
2271
2272 if lv_open_period is null then
2273
2274 ap_utilities_pkg.get_open_gl_date
2275 (
2276 pd_accounting_date,
2277 lv_open_period,
2278 ld_accounting_date,
2279 r_ap_invoices_all.org_id
2280 );
2281
2282 if lv_open_period is null then
2283 p_process_flag := 'E';
2284 p_process_message := 'No open accounting Period after : ' || pd_accounting_date ;
2285 goto exit_from_procedure;
2286 end if;
2287
2288 else
2289 ld_accounting_date := pd_accounting_date;
2290 end if; /* ld_accounting_date */
2291
2292 --Added by Sanjikum for Bug#5131075(4722011)
2293 IF pv_tds_event = 'PREPAYMENT APPLICATION' OR pv_tds_event = 'PREPAYMENT UNAPPLICATION' THEN
2294 lv_group_id := to_char(pn_invoice_id)||pv_tds_event;
2295 pv_invoice_date := ld_accounting_date; --Added by Bgowrava for Bug#9186263
2296 ELSE
2297 lv_group_id := to_char(pn_invoice_id);
2298 pv_invoice_date := r_ap_invoices_all.invoice_date; --Added by Bgowrava for Bug#9186263
2299 END IF;
2300
2301
2302 /* Invoice to TDS Authority */
2303 /* Bug 4513458. Added by Lakshmi Gopalsami
2304 * Rounded the amount to zero as the TDS invoice amount should
2305 * be in INR currency */
2306
2307 -- ln_invoice_to_tds_amount := ROUND(ln_invoice_to_tds_amount,0);
2308 /* Bug 5722028. Added by csahoo
2309 * Rounded depending on the setup.
2310 */
2311 IF pv_tds_event NOT IN
2312 -- Bug 7280925. Commented by Lakshmi Gopalsami ('INVOICE CANCEL',
2313 ('PREPAYMENT UNAPPLICATION')
2314 THEN
2315 ln_invoice_to_tds_amount := ROUND(ln_invoice_to_tds_amount,g_inr_currency_rounding);
2316 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value before round '||ln_invoice_to_tds_amount);
2317 IF trunc(pd_creation_date) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)
2318 /* Bug 7280925. Added by Lakshmi Gopalsami
2319 * we should not round for WCT and ESSI. For those threshold_grp_id
2320 * will be null
2321 */
2322 and pn_threshold_grp_id is not null
2323 THEN
2324 ln_invoice_to_tds_amount := get_rnded_value(ln_invoice_to_tds_amount);
2325 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value after round per setup TDS auth inv'||ln_invoice_to_tds_amount);
2326 END IF;
2327 END IF ; -- pv_tds_event not in
2328
2329 -- End for Bug 5722028
2330
2331 --if lv_invoice_to_tds_num is not null and lv_invoice_to_tds_type is not null then
2332 --commented the above and added the below by bgowrava for bug#4549019
2333 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
2334
2335 /* Generate the Invoice for the TDS authority - always in functional currency - INR */
2336
2337 jai_ap_utils_pkg.insert_ap_inv_interface
2338 (
2339 p_jai_source => lv_this_procedure,
2340 p_invoice_id => ln_invoice_to_tds_id,
2341 p_invoice_num => lv_invoice_to_tds_num,
2342 p_invoice_type_lookup_code => lv_invoice_to_tds_type,
2343 p_invoice_date => ld_accounting_date, --Bug 10020606 - Replaced Base Supplier Invoice Date with GL Date
2344 p_gl_date => ld_accounting_date, --Added by Bgowrava for Bug#9186263
2345 p_vendor_id => r_ja_in_tax_codes.vendor_id,
2346 p_vendor_site_id => r_ja_in_tax_codes.vendor_site_id,
2347 p_invoice_amount => ln_invoice_to_tds_amount,
2348 p_invoice_currency_code => r_gl_sets_of_books.currency_code,
2349 p_exchange_rate => null,
2350 p_exchange_rate_type => null,
2351 p_exchange_date => null,
2352 p_terms_id => ln_terms_id,
2353 p_description => lv_invoice_to_tds_num,
2354 p_last_update_date => sysdate,
2355 p_last_updated_by => fnd_global.user_id,
2356 p_last_update_login => fnd_global.login_id,
2357 p_creation_date => sysdate,
2358 p_created_by => fnd_global.user_id,
2359 p_source => lv_source,
2360 p_voucher_num => lv_invoice_to_tds_num,
2361 --p_payment_method_lookup_code => lv_payment_method_lookup_code,
2362 --commented by Sanjikum for Bug#4482462
2363 p_pay_group_lookup_code => lv_pay_group_lookup_code,
2364 p_org_id => r_ap_invoices_all.org_id,
2365 p_attribute_category => 'India Original Invoice for TDS',
2366 p_attribute1 => pn_invoice_id,
2367 --added the below by Sanjikum for Bug#5131075(4722011)
2368 p_group_id => lv_group_id -- Bug# 6119216, changed to lv_group_id instead of to_char(p_invoice_id)
2369 );
2370
2371 /* Lines Interface */
2372 jai_ap_utils_pkg.insert_ap_inv_lines_interface
2373 (
2374 p_jai_source => lv_this_procedure,
2375 p_invoice_id => ln_invoice_to_tds_id,
2376 p_invoice_line_id => ln_invoice_to_tds_line_id,
2377 p_line_number => 1,
2378 p_line_type_lookup_code => lv_invoice_to_tds_line_type,
2379 p_amount => ln_invoice_to_tds_amount,
2380 p_accounting_date => ld_accounting_date,
2381 p_description => lv_invoice_to_tds_num,
2382 p_dist_code_combination_id => r_ja_in_tax_codes.tax_account_id,
2383 p_last_update_date => sysdate,
2384 p_last_updated_by => fnd_global.user_id,
2385 p_last_update_login => fnd_global.login_id,
2386 p_creation_date => sysdate,
2387 p_created_by => fnd_global.user_id
2388 );
2389
2390 end if; /* Invoice to TDS authority */
2391
2392
2393
2394 /* Invoice to Supplier */
2395
2396 if lv_invoice_to_vendor_num is not null and lv_invoice_to_vendor_type is not null then
2397
2401 * Rounded depending on the setup.
2398 /* Generate the TDS invoice for the supplier in supplier invoice currency */
2399
2400 /* Bug 5722028. Added by csahoo
2402 */
2403 /* IF pv_tds_event NOT IN
2404 -- Bug 7280925. Commented by Lakshmi Gopalsami ('INVOICE CANCEL',
2405 ('PREPAYMENT UNAPPLICATION') THEN */
2406 -- Bug 7280925. Commented by Lakshmi Gopalsami
2407 /* Bug 12965614. Added by Avanija
2408 * Added the following condition to handle for prepayment unapplication for foreign currency invoices.
2409 */
2410 IF pv_tds_event = 'PREPAYMENT UNAPPLICATION' THEN --2
2411 IF r_ap_invoices_all.invoice_currency_code <> r_gl_sets_of_books.currency_code THEN --3
2412 ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount, g_inr_currency_rounding);
2413 IF trunc(pd_creation_date) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
2414 ln_invoice_to_vendor_amount := get_rnded_value(ln_invoice_to_vendor_amount);
2415 END IF;
2416 ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount / r_ap_invoices_all.exchange_rate, g_fcy_currency_rounding);
2417 END IF; --3
2418
2419 ELSE --2
2420
2421 if r_ap_invoices_all.invoice_currency_code <> r_gl_sets_of_books.currency_code then
2422 /*START, Bgowrava for Bug#8995604 , Adding below IF statement to round the INR TDS CM invoice to
2423 the TDS rounding factor before converting it to a foreign currency CM*/
2424 ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount, g_inr_currency_rounding);
2425 IF trunc(pd_creation_date) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)
2426 and pn_threshold_grp_id is not null THEN
2427 ln_invoice_to_vendor_amount := get_rnded_value(ln_invoice_to_vendor_amount);
2428 END IF;
2429 /*END, Bgowrava for Bug#8995604 */
2430 ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount / r_ap_invoices_all.exchange_rate, g_fcy_currency_rounding);
2431 ELSE
2432 ln_invoice_to_vendor_amount := round( ln_invoice_to_vendor_amount, g_inr_currency_rounding);
2433 /*START, Bgowrava for Bug#8995604 , Adding below IF statement to round the INR TDS CM invoice to the TDS rounding factor*/
2434 IF trunc(pd_creation_date) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)
2435 and pn_threshold_grp_id is not null THEN
2436 ln_invoice_to_vendor_amount := get_rnded_value(ln_invoice_to_vendor_amount);
2437 END IF;
2438 /*END, Bgowrava for Bug#8995604 */
2439
2440 END IF; --2
2441 END IF;
2442
2443
2444 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value before round '||ln_invoice_to_vendor_amount);
2445 /*IF trunc(pd_creation_date) >=
2446 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)
2447 and pn_threshold_grp_id is not null
2448 THEN
2449 ln_invoice_to_vendor_amount := get_rnded_value(ln_invoice_to_vendor_amount);
2450 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value after round per setup - vendor invoice'||ln_invoice_to_vendor_amount);
2451 END IF; */ --commented by bgowrava for Bug#8995604
2452
2453
2454 if upper(r_ap_invoices_all.exchange_rate_type) = 'USER' then
2455 ln_exchange_rate := r_ap_invoices_all.exchange_rate;
2456 else
2457 ln_exchange_rate := null;
2458 end if;
2459
2460 fnd_file.put_line(FND_FILE.LOG, ' CALL utils for inserting interface lines');
2461
2462 /*Bug 9759709 - Liability Account for the Credit Note must be fetched from the Standard Invoice
2463 If it is not present, then it needs to be defaulted from Vendor Site Setup. Passing NULL to
2464 parameter p_accts_pay_ccid in jai_ap_utils_pkg.insert_ap_inv_interface would cause it to default
2465 from setup*/
2466 if (r_ap_invoices_all.accts_pay_code_combination_id is not NULL) then
2467 ln_accts_pay_ccid := r_ap_invoices_all.accts_pay_code_combination_id;
2468 else
2469 ln_accts_pay_ccid := NULL;
2470 end if;
2471
2472 IF NVL(ln_invoice_to_vendor_amount,0) <> 0 THEN --condition added by bgowrava for bug#4549019
2473 /* Invoices Interface */
2474 jai_ap_utils_pkg.insert_ap_inv_interface
2475 (
2476 p_jai_source => lv_this_procedure,
2477 p_invoice_id => ln_invoice_to_vendor_id,
2478 p_invoice_num => lv_invoice_to_vendor_num,
2479 p_invoice_type_lookup_code => lv_invoice_to_vendor_type,
2480 p_invoice_date => pv_invoice_date,--r_ap_invoices_all.invoice_date, --Modified to ld_accounting_date for Bug#9186263
2481 p_gl_date => ld_accounting_date,
2482 p_vendor_id => r_ap_invoices_all.vendor_id,
2483 p_vendor_site_id => r_ap_invoices_all.vendor_site_id,
2484 p_invoice_amount => ln_invoice_to_vendor_amount,
2485 p_invoice_currency_code => r_ap_invoices_all.invoice_currency_code,
2486 p_exchange_rate => ln_exchange_rate,
2487 p_exchange_rate_type => r_ap_invoices_all.exchange_rate_type,
2488 p_exchange_date => r_ap_invoices_all.exchange_date,
2489 p_terms_id => r_ap_invoices_all.terms_id,
2490 p_description => lv_invoice_to_vendor_num,
2491 p_last_update_date => sysdate,
2492 p_last_updated_by => fnd_global.user_id,
2493 p_last_update_login => fnd_global.login_id,
2494 p_creation_date => sysdate,
2495 p_created_by => fnd_global.user_id,
2496 p_source => lv_source,
2500 --commented by Sanjikum for Bug#4482462
2497 p_voucher_num => lv_invoice_to_vendor_num,
2498 -- Bug 7109056. Added by Lakshmi Gopalsami
2499 p_payment_method_code => r_ap_invoices_all.payment_method_code,
2501 p_pay_group_lookup_code => r_ap_invoices_all.pay_group_lookup_code,
2502 p_goods_received_date => r_ap_invoices_all.goods_received_date,
2503 p_invoice_received_date => r_ap_invoices_all.invoice_received_date,
2504 p_org_id => r_ap_invoices_all.org_id,
2505 p_attribute_category => 'India Original Invoice for TDS',
2506 p_attribute1 => pn_invoice_id,
2507 --commented the above and added the below by Sanjikum for Bug#5131075(4722011)
2508 p_group_id => lv_group_id,
2509 p_accts_pay_ccid => ln_accts_pay_ccid /*Bug 9759709*/
2510 );
2511
2512 /* Lines Interface */
2513 jai_ap_utils_pkg.insert_ap_inv_lines_interface
2514 (
2515 p_jai_source => lv_this_procedure,
2516 p_invoice_id => ln_invoice_to_vendor_id,
2517 p_invoice_line_id => ln_invoice_to_vendor_line_id,
2518 p_line_number => 1,
2519 p_line_type_lookup_code => lv_invoice_to_vendor_line_type,
2520 p_amount => ln_invoice_to_vendor_amount,
2521 p_accounting_date => ld_accounting_date,
2522 p_description => lv_invoice_to_vendor_num,
2523 p_dist_code_combination_id => r_ja_in_tax_codes.tax_account_id,
2524 p_last_update_date => sysdate,
2525 p_last_updated_by => fnd_global.user_id,
2526 p_last_update_login => fnd_global.login_id,
2527 p_creation_date => sysdate,
2528 p_created_by => fnd_global.user_id
2529 );
2530
2531 end if;
2532 end if; /* Invoice to Supplier */
2533
2534 /* Store the parent invoices payment priority as this is to be used in the credit memo generated for the supplier */
2535 open c_ap_payment_schedules_all(pn_invoice_id);
2536 fetch c_ap_payment_schedules_all into r_ap_payment_schedules_all;
2537 close c_ap_payment_schedules_all;
2538
2539 /* Bug 5751783
2540 * Moved the assignment of ln_invoice_amount outside IF as this is used
2541 * in the insert to jai_ap_tds_thhold_trxs. This has to be derived irrespective
2542 * of the tds event.
2543 */
2544
2545 Fnd_File.put_line(Fnd_File.LOG, 'pn_taxable_amount '||pn_taxable_amount);
2546 /*IF pn_taxable_amount IS NOT NULL THEN
2547 ln_invoice_amount := pn_taxable_amount;
2548 ELSE*/ /*Commented the above if condition for bug 13070779 */
2549 ln_invoice_amount := pn_tax_amount * ( 100 / r_ja_in_tax_codes.tax_rate);
2550 -- END IF;
2551 fnd_file.put_line(FND_FILE.LOG, ' invoice amount'|| ln_invoice_amount);
2552
2553 /* For downward compatibility with the pre-cleanup code */
2554 if ( (pv_tds_event = 'INVOICE VALIDATE') or (pv_tds_event like 'THRESHOLD TRANSITION%') ) then
2555
2556 /*
2557 || Added the IF-ELSE-ENDIF block by Ramananda for Bug#4562801
2558 */
2559
2560
2561 IF r_ja_in_tax_codes.section_type = 'TDS_SECTION' THEN --rchandan for bug#4428980
2562 lv_source_attribute := 'ATTRIBUTE1';
2563 ELSIF r_ja_in_tax_codes.section_type = 'WCT_SECTION' THEN
2564 lv_source_attribute := 'ATTRIBUTE2';
2565 ELSIF r_ja_in_tax_codes.section_type = 'ESSI_SECTION' THEN
2566 lv_source_attribute := 'ATTRIBUTE3';
2567 END IF;
2568
2569 fnd_file.put_line(FND_FILE.LOG, ' invoice id '|| pn_invoice_id);
2570 fnd_file.put_line(FND_FILE.LOG, ' invoice amount'|| ln_invoice_amount);
2571 fnd_file.put_line(FND_FILE.LOG, 'invoice to tds inv num'|| lv_invoice_to_tds_num);
2572 fnd_file.put_line(FND_FILE.LOG, 'vendor num' ||lv_invoice_to_vendor_num);
2573 fnd_file.put_line(FND_FILE.LOG, 'tax id '||pn_tax_id);
2574 fnd_file.put_line(FND_FILE.LOG, 'tax rate'|| r_ja_in_tax_codes.tax_rate);
2575 fnd_file.put_line(FND_FILE.LOG, 'tds amt'||ln_invoice_to_tds_amount);
2576 fnd_file.put_line(FND_FILE.LOG, 'sec code '||r_ja_in_tax_codes.section_code);
2577 fnd_file.put_line(FND_FILE.LOG, 'stformtype '||r_ja_in_tax_codes.stform_type);
2578 fnd_file.put_line(FND_FILE.LOG, 'org id '|| r_ap_invoices_all.org_id);
2579 fnd_file.put_line(FND_FILE.LOG, 'src att'||lv_source_attribute);
2580
2581 IF NVL(ln_invoice_amount,0) <> 0 THEN --Added the condition by bgowrava for Bug#4549019
2582 insert into JAI_AP_TDS_INVOICES
2583 (TDS_INVOICE_ID,
2584 invoice_id,
2585 invoice_amount,
2586 tds_invoice_num,
2587 dm_invoice_num,
2588 tds_tax_id,
2589 tds_tax_rate,
2590 tds_amount,
2591 tds_section,
2592 certificate_number,
2593 --org_id,
2594 organization_id,
2595 source_attribute,
2596 /* Ramananda for bug#4560109 during R12 Sanity Testing. Added the WHO columns */
2597 created_by ,
2598 creation_date ,
2599 last_updated_by ,
2600 last_update_date ,
2601 last_update_login
2602 )
2603 values
2604 ( JAI_AP_TDS_INVOICES_S.nextval,
2605 pn_invoice_id,
2606 --round(ln_invoice_to_tds_amount * ( 100 / r_ja_in_tax_codes.tax_rate), 2),
2607 --commented the above and added the below by Ramananda for Bug#4562801
2608 ln_invoice_amount,
2609 lv_invoice_to_tds_num,
2610 lv_invoice_to_vendor_num,
2611 pn_tax_id,
2612 r_ja_in_tax_codes.tax_rate,
2613 ln_invoice_to_tds_amount,
2614 r_ja_in_tax_codes.section_code,
2615 r_ja_in_tax_codes.stform_type,
2616 --r_ap_invoices_all.org_id,
2617 r_ap_invoices_all.org_id,
2618 lv_source_attribute, --rchandan for bug#4428980
2619 /* Ramananda for bug#4560109 during R12 Sanity Testing. Added the WHO columns */
2620 fnd_global.user_id ,
2621 sysdate ,
2622 fnd_global.user_id ,
2623 sysdate ,
2624 fnd_global.login_id
2625 );
2626 end if;
2627 end if; /* Only for validate event as done in earlier regime */
2628
2629 -- Bug 5722028. Added by csahoo
2630 ln_tds_rnding_factor := 0;
2631 ln_tds_rnded_amt := pn_tax_amount;
2632 /* Added the below if condition for bug 12965614 */
2633 IF pv_tds_event = 'PREPAYMENT UNAPPLICATION' THEN --2
2634 IF r_ap_invoices_all.invoice_currency_code <> r_gl_sets_of_books.currency_code THEN --3
2635 ln_tds_rnded_amt := ROUND(pn_calc_tax_amount , g_inr_currency_rounding);
2636 IF (trunc(pd_creation_date) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)) THEN --4
2637 ln_tds_rnding_factor := jai_ap_tds_generation_pkg.gn_tds_rounding_factor;
2638 ln_tds_rnded_amt := get_rnded_value(ln_tds_rnded_amt);
2639 END IF; --4
2640 ln_tds_rnded_amt := ROUND(pn_calc_tax_amount / r_ap_invoices_all.exchange_rate, g_fcy_currency_rounding);
2641 END IF; --3
2642 ELSE --2
2643 /*IF pv_tds_event NOT IN
2644 -- Bug 7280925. commented by Lakshmi Gopalsami ('INVOICE CANCEL',
2645 ('PREPAYMENT UNAPPLICATION') THEN */
2646 IF r_ap_invoices_all.invoice_currency_code = r_gl_sets_of_books.currency_code THEN
2647 ln_tds_rnded_amt := ROUND(pn_tax_amount , g_inr_currency_rounding);
2648 --moved this if-endif block from below to here for bug#8995604
2649 ELSE
2650 ln_tds_rnded_amt := ROUND(pn_tax_amount , g_inr_currency_rounding);
2651 IF (trunc(pd_creation_date) >=
2652 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date)
2653 /* Bug 7280925. Added by Lakshmi Gopalsami
2654 * we should not round for WCT and ESSI. For those threshold_grp_id
2655 * will be null
2656 */
2657 and pn_threshold_grp_id is not null )
2658 THEN
2659 ln_tds_rnding_factor := jai_ap_tds_generation_pkg.gn_tds_rounding_factor;
2660 ln_tds_rnded_amt := get_rnded_value(ln_tds_rnded_amt);
2661 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value after round before insert into trxs'||ln_tds_rnded_amt);
2662 END IF;
2663 ln_tds_rnded_amt := ROUND(pn_tax_amount / r_ap_invoices_all.exchange_rate, g_fcy_currency_rounding);
2664 END IF ;
2665 fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value before round '||ln_tds_rnded_amt);
2666
2667 END IF;
2668 -- End for bug 5722028.
2669
2670
2671 insert into jai_ap_tds_thhold_trxs
2672 (
2673 threshold_trx_id ,
2674 invoice_id ,
2675 invoice_line_number ,
2676 invoice_distribution_id ,
2677 threshold_grp_id ,
2678 threshold_hdr_id ,
2679 tds_event ,
2680 tax_id ,
2681 tax_rate ,
2682 taxable_amount ,
2683 tax_amount ,
2684 tds_authority_vendor_id ,
2685 tds_authority_vendor_site_id ,
2686 invoice_to_tds_authority_num ,
2687 invoice_to_tds_authority_type ,
2688 invoice_to_tds_authority_curr ,
2689 invoice_to_tds_authority_amt ,
2690 vendor_id ,
2691 vendor_site_id ,
2692 invoice_to_vendor_num ,
2693 invoice_to_vendor_type ,
2694 invoice_to_vendor_curr ,
2695 invoice_to_vendor_amt ,
2696 parent_inv_payment_priority ,
2697 parent_inv_exchange_rate ,
2698 created_by ,
2699 creation_date ,
2700 last_updated_by ,
2701 last_update_date ,
2702 last_update_login ,
2703 calc_tax_amount , /*Bug 5751783*/
2704 tds_rounding_factor -- Bug 5722028. Added by csahoo
2705 )
2706 values
2707 (
2708 jai_ap_tds_thhold_trxs_s.nextval ,
2709 pn_invoice_id ,
2710 pn_invoice_line_number ,
2711 pn_invoice_distribution_id ,
2712 pn_threshold_grp_id ,
2713 pn_threshold_hdr_id ,
2714 pv_tds_event ,
2715 pn_tax_id ,
2716 r_ja_in_tax_codes.tax_rate ,
2717 /* Bug 5751783. Changed to ln_invoice_amount instead of pn_taxable_amount
2718 * This is done as now pn_taxable_amount will always be populated irrespective
2719 * of tds_event. Added rounding for pn_tax_amount.
2720 */
2721 ln_invoice_amount ,
2722 ln_tds_rnded_amt, --Bug 5722028. Added by csahoo
2723 r_ja_in_tax_codes.vendor_id ,
2724 r_ja_in_tax_codes.vendor_site_id ,
2725 lv_invoice_to_tds_num ,
2726 lv_invoice_to_tds_type ,
2727 r_gl_sets_of_books.currency_code ,
2728 ln_invoice_to_tds_amount ,
2729 r_ap_invoices_all.vendor_id ,
2730 r_ap_invoices_all.vendor_site_id ,
2731 lv_invoice_to_vendor_num ,
2732 lv_invoice_to_vendor_type ,
2733 r_ap_invoices_all.invoice_currency_code ,
2734 ln_invoice_to_vendor_amount ,
2735 r_ap_payment_schedules_all.payment_priority ,
2736 r_ap_invoices_all.exchange_rate ,
2737 fnd_global.user_id ,
2738 sysdate ,
2739 fnd_global.user_id ,
2740 sysdate ,
2741 fnd_global.login_id ,
2742 decode ( pv_tds_event, 'PREPAYMENT UNAPPLICATION', pn_calc_tax_amount, pn_tax_amount), /*Added decode condition for bug 12965614 */ /*Bug 5751783*/
2743 ln_tds_rnding_factor -- Bug 5722028. Added by csahoo
2744 )
2745 returning threshold_trx_id into pn_threshold_trx_id;
2746
2747 <<exit_from_procedure>>
2748 return;
2749
2750 exception
2751 when others then
2752 p_process_flag := 'E';
2753 p_process_message := 'Error from jai_ap_tds_generation_pkg.generate_tds_invoices :' || sqlerrm;
2754
2755 end generate_tds_invoices;
2756
2757 /* *********************************** procedure generate_tds_invoices ********************************** */
2758
2759 procedure process_threshold_transition
2760 (
2761 p_threshold_grp_id in number,
2762 p_threshold_slab_id in number,
2763 p_invoice_id in number,
2764 p_vendor_id in number,
2765 p_vendor_site_id in number,
2766 p_accounting_date in date,
2767 p_tds_event in varchar2,
2768 p_org_id in number,
2769 pv_tds_invoice_num out nocopy varchar2,
2770 pv_cm_invoice_num out nocopy varchar2,
2771 p_process_flag out nocopy varchar2,
2772 p_process_message out nocopy varchar2
2773 )
2774 is
2775
2776 cursor c_jai_ap_tds_thhold_taxes(p_threshold_slab_id number, p_org_id number) is
2777 select tax_id
2778 from jai_ap_tds_thhold_taxes
2779 where threshold_slab_id = p_threshold_slab_id
2780 and operating_unit_id = p_org_id;
2781
2782 /* START, Added below cursors for Bgowrava for Bug#8254510*/
2783 cursor c_jai_ap_no_tds_trx(cp_threshold_grp_id number) is
2784 select jatit.invoice_id from jai_ap_tds_inv_taxes jatit
2785 where jatit.threshold_grp_id = cp_threshold_grp_id
2786 and jatit.tax_amount <> 0
2787 /*Bug 11880998 - Skip Invoices that are canceled without validation
2788 Such Invoices do not update Threshold Audits*/
2789 and NOT EXISTS (SELECT 1
2790 FROM jai_ap_tds_thgrp_audits jatta
2791 WHERE jatit.invoice_id = jatta.invoice_id
2792 AND (jatta.trx_invoice_amount = 0 OR jatta.trx_invoice_cancel_amount = 0)
2793 AND EXISTS (SELECT 1
2794 FROM ap_invoices_all aia
2795 WHERE aia.invoice_id = jatit.invoice_id
2796 AND aia.cancelled_date IS NOT NULL)
2797 )
2798 minus
2799 /* select invoice_id from jai_ap_tds_thhold_trxs
2800 where threshold_grp_id = cp_threshold_grp_id; */ /* Commented and added below select for bug 14709400 */
2801 select invoice_id from jai_ap_tds_inv_taxes where invoice_id=p_invoice_id
2802 or (threshold_grp_id = cp_threshold_grp_id and nvl(threshold_slab_id_single,-99)<>-99 and threshold_trx_id is not null);
2803
2804 /*Bug 13561970 - Modified cursor to get the number of records with THRESHOLD%. This would fetch both THRESHOLD TRANSITION and THRESHOLD ROLLBACK. Earlir it was THRESHOLD TRANSITION%*/
2805
2806 cursor c_threshold_passed(cp_threshold_grp_id number) is
2807 select count(*) from jai_ap_tds_thhold_trxs /*Bug 9562876*/
2808 where threshold_grp_id = cp_threshold_grp_id
2809 and tds_event like 'THRESHOLD%';
2810
2811 cursor c_jai_no_tds_trx_amt(cp_invoice_id number) is
2812 select sum(amount) amount, sum(tax_amount) tax_amount
2813 from jai_ap_tds_inv_taxes
2814 where invoice_id = cp_invoice_id
2815 and section_type='TDS_SECTION' /* Added for bug 14369937 */
2816 and actual_section_code is not null /* Added for bug 14369937 */
2817 group by invoice_id;
2818
2819 cursor c_thhold_grps_inv(cp_threshold_grp_id number) is
2820 select invoice_id, tds_event, tax_id, tax_rate, taxable_amount, tax_amount
2821 from jai_ap_tds_thhold_trxs
2822 where threshold_grp_id = cp_threshold_grp_id;
2823
2824 cursor c_jai_cancelled_amount(cp_invoice_id number) is
2825 select nvl(cancelled_amount,0)
2826 from ap_invoices_all
2827 where invoice_id=cp_invoice_id;
2828
2829 cursor c_get_threshold(p_vendor_id number, p_vendor_site_id number, p_tds_section_code varchar2) is
2830 select threshold_hdr_id
2831 from JAI_AP_TDS_TH_VSITE_V
2832 where vendor_id = p_vendor_id
2833 and vendor_site_id = p_vendor_site_id
2834 and section_type = 'TDS_SECTION'
2835 and section_code = p_tds_section_code;
2836
2837 cursor c_sur_already_calc(cp_threshold_grp_id number) is
2838 select 1
2839 from jai_ap_tds_thhold_trxs
2840 where threshold_grp_id = cp_threshold_grp_id
2841 and tds_event like 'SURCHARGE_CALCULATE';
2842 /* END, Bgowrava for Bug#8254510*/
2843
2844
2845 cursor c_jai_ap_tds_thhold_grps(cp_threshold_grp_id number) is
2846 select (
2847 nvl(total_invoice_amount, 0) -
2848 nvl(total_invoice_cancel_amount, 0) -
2849 nvl(total_invoice_apply_amount, 0) +
2850 nvl(total_invoice_unapply_amount, 0)
2851 )
2852 total_invoice_amount,
2853 total_tax_paid,
2854 /*Bug 5751783. Selected non-rounded value for calculation*/
2855 total_calc_tax_paid
2856 from jai_ap_tds_thhold_grps
2857 where threshold_grp_id = cp_threshold_grp_id;
2858
2859 cursor c_ja_in_tax_codes(cp_tax_id number) is
2860 select tax_rate, surcharge_rate, cess_rate, sh_cess_rate, section_code --Added surcharge_rate, cess_rate, sh_cess_rate, section_code by Bgowrava for bug#8254510
2861 from JAI_CMN_TAXES_ALL
2862 where tax_id = cp_tax_id;
2863
2864 /* Bug 5751783. Get the sum of invoice amount for which TDS is not calculated*/
2865 CURSOR get_tds_not_deducted ( cp_threshold_grp_id IN NUMBER )
2866 IS
2867 SELECT SUM (NVL (jatit.amount , 0 ) )
2868 FROM jai_ap_tds_inv_taxes jatit
2869 WHERE jatit.threshold_grp_id = cp_threshold_grp_id
2870 AND match_status_flag = 'A'
2871 AND jatit.process_status = 'P'
2872 AND jatit.tax_amount IS NOT NULL
2873 AND jatit.threshold_trx_id IS NULL
2874 AND jatit.threshold_slab_id = 0
2875 AND ( jatit.actual_tax_id IS NOT NULL OR
2876 ( jatit.actual_taX_id IS NULL
2877 AND jatit.default_tax_id IS NOT NULL
2878 )
2879 )
2880 AND EXISTS /* check whether iinvoice is not cancelled*/
2881 ( SELECT invoice_id
2882 FROM ap_invoices_all ai
2883 WHERE ai.invoice_id = jatit.invoice_id
2884 AND ai.cancelled_Date IS NULL
2885 AND ai.cancelled_amount IS NULL
2886 );
2887
2888
2889 CURSOR get_thhold_rollbk (cp_threshold_grp_id IN NUMBER )
2890 IS
2891 SELECT SUM(NVL(jattt.taxable_amount,0))
2892 FROM jai_ap_tds_thhold_trxs jattt
2893 WHERE jattt.threshold_grp_id = cp_threshold_grp_id
2894 AND (jattt.tds_event like 'THRESHOLD ROLLBACK%');
2895 -- Bug 5722028. Added by csahoo
2896 -- added the following condition
2897 --jattt.tds_event like 'THRESHOLD TRANSITION%' ); --commented by Bgowrava for Bug#8254510
2898
2899 /* START, by Bgowrava for Bug#8459564 */
2900 /* Get the prepayment amount which is applied and RTN invoice is not yet generated. */
2901 CURSOR get_ppau_tds_not_deducted (cp_threshold_grp_id IN NUMBER )
2902 IS
2903 SELECT SUM (NVL (jatp.application_amount, 0 ))
2904 FROM jai_ap_tds_prepayments jatp
2905 WHERE jatp.tds_threshold_grp_id = cp_threshold_grp_id
2906 AND jatp.tds_applicable_flag = 'Y'
2907 AND jatp.tds_threshold_trx_id_apply IS NULL
2908 AND (jatp.unapply_flag IS NULL OR jatp.unapply_flag = 'N') ;
2909 /* END, by Bgowrava for Bug#8459564 */
2910
2911 --Added by Xiao Lv for bug#8485691, related 11i bug#8439217, begin
2912 /*Bug 12434843(Patched in 11896260) - Fetch actual tax id for Threshold Transition. Use default only if actual tax id does not exist*/
2913 cursor c_default_tax_id(cp_invoice_id number) is
2914 select nvl(actual_tax_id, default_tax_id)
2915 from jai_ap_tds_inv_taxes
2916 where invoice_id = cp_invoice_id
2917 and section_type = 'TDS_SECTION'; /* Bug 15918300 */
2918 --and default_type = 'TAX'
2919 --and default_tax_id <> cp_tax_id;
2920 --Added by Xiao Lv for bug#8485691, related 11i bug#8439217, end
2921 /*Start-Bug 9562876*/
2922 CURSOR c_thhold_rollback_occurred(cp_threshold_grp_id NUMBER, cp_new_thhold_slab NUMBER)
2923 IS
2924 SELECT 1
2925 FROM jai_ap_tds_thgrp_audits
2926 WHERE threshold_grp_id = cp_threshold_grp_id
2927 AND tds_Event like 'THRESHOLD ROLLBACK%'
2928 AND new_threshold_slab_id = cp_new_thhold_slab;
2929
2930 CURSOR c_get_rollback_amt(cp_threshold_grp_id NUMBER, cp_new_thhold_slab NUMBER)
2931 IS
2932 select ((tax_amount/tax_rate)*100) taxable_amount, tax_amount
2933 from jai_Ap_tds_thhold_trxs
2934 where invoice_id = (select invoice_id
2935 from jai_ap_tds_thgrp_audits
2936 where threshold_grp_audit_id = (select max(threshold_grp_audit_id)
2937 from jai_Ap_tds_thgrp_Audits
2938 where tds_event like 'THRESHOLD ROLLBACK%'
2939 and new_threshold_slab_id = cp_new_thhold_slab
2943
2940 and threshold_grp_id = cp_threshold_grp_id)
2941 )
2942 and tds_event like 'THRESHOLD ROLLBACK%';
2944 ln_rollback_available NUMBER;
2945 /*Ebd-Bug 9562876*/
2946 lv_codepath jai_ap_tds_inv_taxes.codepath%type;
2947
2948
2949 r_jai_ap_tds_thhold_taxes c_jai_ap_tds_thhold_taxes%rowtype;
2950 r_jai_ap_tds_thhold_grps c_jai_ap_tds_thhold_grps%rowtype;
2951 r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
2952 ln_thhold_transition_tax_amt number;
2953 lv_tds_invoice_num ap_invoices_all.invoice_num%type;
2954 lv_tds_cm_num ap_invoices_all.invoice_num%type;
2955 ln_threshold_trx_id number;
2956 ln_threshold_grp_audit_id number;
2957 ln_threshold_grp_id number;
2958
2959 /*Added below variables by bgowrava for Bug#8254510*/
2960 ln_thhold_total_tax_amt number;
2961 ln_sur_applicable_amt number;
2962 ln_inv_surcharge_rate number;
2963 ln_trx_surcharge_amount number;
2964 ln_thhold_total_sur_amt number;
2965 ln_surcharge_rate number;
2966 ln_thhold_passed number;
2967 ln_threshold_hdr_id number;
2968 ln_cancelled_amount number;
2969 ln_prepay_applied_amt number; --Added by Xiao Lv for bug#8345080 on 13-Jan-10
2970 r_ja_in_tax_codes_sur c_ja_in_tax_codes%rowtype;
2971 r_jai_no_tds_trx_amt c_jai_no_tds_trx_amt%rowtype;
2972 r_sur_already_calc number;
2973 /*END, bgowrava for Bug#8254510*/
2974 /* START, by Bgowrava for Bug#8459564 */
2975 ln_threshold_slab_id_before jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
2976 ln_threshold_slab_id_temp VARCHAR2 (1000);
2977 ln_threshold_slab_id_after jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
2978 ln_taxable_amount jai_ap_tds_thhold_trxs.taxable_amount%TYPE;
2979 ln_pp_tds_not_deducted NUMBER ;
2980 ln_thhold_trxn_roll NUMBER ;
2981 /* END, by Bgowrava for Bug#8459564 */
2982 ln_thhold_total_inv_amt number := 0; --Added by Xiao For Bug#8485691, related 11i bug#8439217
2983 r_tax_id number; --Added by Xiao Bug#8485691, related 11i bug#8439217
2984 r_ja_in_tax_codes_inv c_ja_in_tax_codes%rowtype; --Added by Xiao For Bug#8485691, related 11i bug#8439217
2985 ln_curr_prepay_amt number; --Added by Xiao Lv for Bug#8513550, related 11i bug#8439276
2986
2987 begin
2988
2989 open c_jai_ap_tds_thhold_taxes(p_threshold_slab_id, p_org_id);
2990 fetch c_jai_ap_tds_thhold_taxes into r_jai_ap_tds_thhold_taxes;
2991 close c_jai_ap_tds_thhold_taxes;
2992
2993 open c_jai_ap_tds_thhold_grps(p_threshold_grp_id);
2994 fetch c_jai_ap_tds_thhold_grps into r_jai_ap_tds_thhold_grps;
2995 close c_jai_ap_tds_thhold_grps;
2996
2997 open c_ja_in_tax_codes(r_jai_ap_tds_thhold_taxes.tax_id);
2998 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
2999 close c_ja_in_tax_codes;
3000
3001 -- Bug 5722028. Added by csahoo
3002 -- Rounded depending on the TDS setup rounding.
3003
3004 /*ln_thhold_transition_tax_amt :=
3005 ROUND(r_jai_ap_tds_thhold_grps.total_invoice_amount * (r_ja_in_tax_codes.tax_rate/100),
3006 g_inr_currency_rounding);
3007
3008 IF trunc(sysdate) >=
3009 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
3010 ln_thhold_transition_tax_amt := get_rnded_value(ln_thhold_transition_tax_amt);
3011 END IF;
3012
3013 ln_thhold_transition_tax_amt := ln_thhold_transition_tax_amt - r_jai_ap_tds_thhold_grps.total_tax_paid;*/ --Commented by Bgowrava for Bug#8254510
3014 /*Moved below lines from below to here by amandali for Bug#9562876*/
3015 ln_threshold_slab_id_after := p_threshold_slab_id;
3016 ln_threshold_slab_id_temp := substr(p_tds_event, instr(p_tds_event,'from slab id -')+14 );
3017 ln_threshold_slab_id_before := to_number(substr(ln_threshold_slab_id_temp,1, instr(ln_threshold_slab_id_temp,'to slab id -')-1));
3018 /*END, Bug#9562876*/
3019
3020 /* START, Bgowrava for Bug#8254510*/
3021 ln_threshold_hdr_id := 0;
3022 open c_get_threshold(p_vendor_id ,p_vendor_site_id, r_ja_in_tax_codes.section_code);
3023 fetch c_get_threshold into ln_threshold_hdr_id;
3024 close c_get_threshold;
3025
3026 ln_thhold_passed := 0;
3027 open c_threshold_passed(p_threshold_grp_id);
3028 fetch c_threshold_passed into ln_thhold_passed;
3029 close c_threshold_passed;
3030 /*START, amandali for Bug#9562876*/
3031 --if ln_thhold_passed <> 1 then
3032 if mod(ln_thhold_passed, 2) <> 1 then
3033 /* Bug 13561970 Start Commenting*/
3034
3035 /*OPEN c_thhold_rollback_occurred(p_threshold_grp_id, ln_threshold_slab_id_before);
3036 FETCH c_thhold_rollback_occurred INTO ln_rollback_available;
3037 CLOSE c_thhold_rollback_occurred;
3038
3039 IF ln_rollback_available = 1 THEN
3040 OPEN c_get_rollback_amt(p_threshold_grp_id, ln_threshold_slab_id_before);
3041 FETCH c_get_rollback_amt INTO ln_taxable_amount, ln_thhold_transition_tax_amt;
3042 CLOSE c_get_rollback_amt;
3043 ELSE*/
3044 /* Bug 13561970 End Commenting*/
3045 /*END, amandali for Bug#9562876*/
3046 get_prepay_appln_amt(p_invoice_id, p_threshold_grp_id, 'Y', ln_curr_prepay_amt); --Added by Xiao for Bug#8513550, related 11i bug#8439276
3047
3048 ln_thhold_total_tax_amt := 0;
3049 for c_rec_no_tds in c_jai_ap_no_tds_trx(p_threshold_grp_id)
3050 loop
3051 open c_jai_no_tds_trx_amt(c_rec_no_tds.invoice_id);
3052 fetch c_jai_no_tds_trx_amt into r_jai_no_tds_trx_amt;
3053 close c_jai_no_tds_trx_amt;
3054
3055 get_prepay_appln_amt(c_rec_no_tds.invoice_id, p_threshold_grp_id, 'N', ln_prepay_applied_amt); --Added by Xiao Lv for bug#8345080
3056 ln_cancelled_amount := 0;
3057 open c_jai_cancelled_amount(c_rec_no_tds.invoice_id);
3058 fetch c_jai_cancelled_amount into ln_cancelled_amount;
3059 close c_jai_cancelled_amount;
3060 --Added by Xiao for Bug#8485691, related 11i Bug#8439217, begin
3061 r_tax_id := null;
3062 /*Removed parameter r_jai_ap_tds_thhold_taxes.tax_id. Tax Rate for Threshold Transition must be fetched from the Invoice
3063 There is no need to fetch the Threshold Setup tax rate as Defaultation logic should have populated the same in default_tax_id*/
3064 open c_default_tax_id(c_rec_no_tds.invoice_id);
3065 fetch c_default_tax_id into r_tax_id;
3066 close c_default_tax_id;
3067
3068 open c_ja_in_tax_codes(nvl(r_tax_id, r_jai_ap_tds_thhold_taxes.tax_id));
3069 fetch c_ja_in_tax_codes into r_ja_in_tax_codes_inv;
3070 close c_ja_in_tax_codes;
3071
3072 ln_thhold_total_inv_amt := ln_thhold_total_inv_amt + (r_jai_no_tds_trx_amt.amount - ln_cancelled_amount - ln_prepay_applied_amt - ln_curr_prepay_amt); --Added ln_curr_prepay_amt by xiao for Bug#8513550
3073
3074
3075 --ln_thhold_total_tax_amt := ln_thhold_total_tax_amt + (r_jai_no_tds_trx_amt.amount - ln_cancelled_amount);
3076 --Added by Xiao for Bug#8485691, related 11i Bug#8439217, end
3077 ln_thhold_total_tax_amt := ln_thhold_total_tax_amt
3078 + ((r_jai_no_tds_trx_amt.amount - ln_cancelled_amount - ln_prepay_applied_amt - ln_curr_prepay_amt)
3079 *(r_ja_in_tax_codes_inv.tax_rate/100)); --Added ln_prepay_applied_amt by Xiao Lv for bug#8345080 --Added ln_curr_prepay_amt by Xiao for Bug#8513550, related 11i bug#8439276
3080 end loop;
3081
3082 ln_thhold_transition_tax_amt := ROUND(ln_thhold_total_tax_amt, g_inr_currency_rounding);
3083
3084 IF trunc(sysdate) >= trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
3085 ln_thhold_transition_tax_amt := get_rnded_value(ln_thhold_transition_tax_amt);
3086 END IF;
3087 /* END, Bgowrava for Bug#8254510*/
3088 --END IF; /*ln_rollback_available = 1*/ /* Commented Bug 13561970*/
3089
3090 if ln_thhold_transition_tax_amt > 0 then
3091 /* START, by Bgowrava for Bug#8459564 */
3092 IF ln_rollback_available <> 1 THEN --Added by amandali for Bug#9562876
3093
3094 IF ln_threshold_slab_id_before = 0 THEN
3095 OPEN get_ppau_tds_not_deducted(p_threshold_grp_id);
3096 FETCH get_ppau_tds_not_deducted INTO ln_pp_tds_not_deducted;
3097 CLOSE get_ppau_tds_not_deducted;
3098 -- Get the rollback taxable amount
3099 OPEN get_thhold_rollbk(p_threshold_grp_id);
3100 FETCH get_thhold_rollbk INTO ln_thhold_trxn_roll;
3101 CLOSE get_thhold_rollbk;
3102 ln_taxable_amount := ln_thhold_total_inv_amt - NVL(ln_pp_tds_not_deducted,0)- NVL(ln_thhold_trxn_roll,0);
3103 ELSE
3104 -- This case arrives when the transition happens within cumulative.
3105 ln_taxable_amount := 0;
3106 END IF;
3107 END IF; --Added by amandali for Bug#9562876
3108 /* END, by Bgowrava for Bug#8459564 */
3109
3110 jai_ap_tds_generation_pkg.generate_tds_invoices
3111 (
3112 pn_invoice_id => p_invoice_id ,
3113 pn_threshold_hdr_id => ln_threshold_hdr_id , /*amandali for bug 11671779*/
3114 pn_taxable_amount => ln_taxable_amount , --Modified by Bgowrava for Bug#8459564
3115 /* No taxable amount in case of threshold transition invoice */
3116 pn_tax_amount => ln_thhold_transition_tax_amt ,
3117 pn_tax_id => r_jai_ap_tds_thhold_taxes.tax_id ,
3118 pd_accounting_date => p_accounting_date ,
3119 pv_tds_event => p_tds_event ,
3120 pn_threshold_grp_id => p_threshold_grp_id ,
3121 pv_tds_invoice_num => lv_tds_invoice_num ,
3122 pv_cm_invoice_num => lv_tds_cm_num ,
3123 pn_threshold_trx_id => ln_threshold_trx_id ,
3124 pd_creation_date => sysdate, -- Bug 5722028. Added by csahoo
3125 pn_calc_tax_amount => 0, /* Added for bug 12965614 */
3126 p_process_flag => p_process_flag ,
3127 p_process_message => p_process_message
3128 );
3129
3130 if p_process_flag = 'E' then
3131 goto exit_from_procedure;
3132 end if;
3133
3134 /* START, by Bgowrava for Bug#8459564
3135 * Call the import and approve for threshold transition
3136 * occurred when prepayment unapplication has happened as
3137 * we already call import_and_approve for validation
3138 * in procedure process_tds_at_inv_validate
3139 * Threshold transition can again re-appear during prepayment
3140 * unapplication
3141 */
3142
3146
3143 IF ln_threshold_trx_id IS NOT NULL AND
3144 p_tds_event like 'THRESHOLD TRANSITION-PPUA%'
3145 THEN
3147 import_and_approve
3148 (
3149 p_invoice_id => p_invoice_id,
3150 p_start_thhold_trx_id => ln_threshold_trx_id,
3151 p_tds_event => p_tds_event,
3152 p_process_flag => p_process_flag,
3153 p_process_message => p_process_message
3154 );
3155
3156 IF p_process_flag = 'E' THEN
3157 goto exit_from_procedure;
3158 END IF;
3159
3160 END IF;
3161 /* END, by Bgowrava for Bug#8459564 */
3162
3163 /* Update the total tax amount for which invoice was raised */
3164 ln_threshold_grp_id:= p_threshold_grp_id;
3165 maintain_thhold_grps
3166 (
3167 p_threshold_grp_id => ln_threshold_grp_id,
3168 p_trx_tax_paid => ln_thhold_transition_tax_amt,
3169 p_trx_thhold_change_tax_paid => ln_thhold_transition_tax_amt,
3170 p_trx_threshold_slab_id => p_threshold_slab_id,
3171 p_tds_event => p_tds_event,
3172 p_invoice_id => p_invoice_id,
3173 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
3174 -- Bug 5722028. Added by Lakshmi Gopalsami
3175 p_creation_date => sysdate,
3176 p_process_flag => p_process_flag,
3177 P_process_message => P_process_message,
3178 p_codepath => lv_codepath
3179 );
3180
3181 --Added by Sanjikum for Bug#5131075(4722011)
3182 IF p_process_flag = 'E' THEN
3183 goto exit_from_procedure;
3184 END IF;
3185
3186 end if; /* ln_thhold_transition_tax_amt > 0 */
3187 end if; --Bgowrava for Bug#8254510
3188
3189 /* START, Bgowrava for bug#8254510*/
3190 ln_surcharge_rate := 0;
3191 open c_sur_already_calc(p_threshold_grp_id);
3192 fetch c_sur_already_calc into r_sur_already_calc;
3193 close c_sur_already_calc;
3194
3195 if nvl(r_ja_in_tax_codes.surcharge_rate, 0) > 0 and nvl(r_sur_already_calc, 0) <> 1 then
3196
3197 /* added nvl condition in the surcharge calculation formula below for Bug 7312295*/
3198 ln_surcharge_rate := (nvl(r_ja_in_tax_codes.surcharge_rate,0)/(r_ja_in_tax_codes.tax_rate-(nvl(r_ja_in_tax_codes.surcharge_rate, 0)
3199 + nvl(r_ja_in_tax_codes.cess_rate, 0)
3200 + nvl(r_ja_in_tax_codes.sh_cess_rate, 0)))) * 100;
3201
3202 ln_sur_applicable_amt := 0;
3203 ln_inv_surcharge_rate := 0;
3204 ln_trx_surcharge_amount := 0;
3205 ln_thhold_total_sur_amt := 0;
3206 for c_rec_grps_inv in c_thhold_grps_inv(p_threshold_grp_id)
3207 loop
3208 r_ja_in_tax_codes_sur := null;
3209 open c_ja_in_tax_codes(c_rec_grps_inv.tax_id);
3210 fetch c_ja_in_tax_codes into r_ja_in_tax_codes_sur;
3211 close c_ja_in_tax_codes;
3212
3213 if nvl(r_ja_in_tax_codes_sur.surcharge_rate, 0) = 0 and c_rec_grps_inv.taxable_amount<>0 then
3214 ln_sur_applicable_amt := ln_sur_applicable_amt + c_rec_grps_inv.taxable_amount;
3215 ln_inv_surcharge_rate := nvl(r_ja_in_tax_codes_sur.tax_rate, 0) * (ln_surcharge_rate/100); /*Bug 7312295 - Removed CESS and SHECESS*/
3216
3217 --ln_trx_surcharge_amount := c_rec_grps_inv.taxable_amount*(ln_inv_surcharge_rate/100); /*commented out by xin on 21-Nov-2012 for bug 14806969 */
3218 ln_trx_surcharge_amount := c_rec_grps_inv.taxable_amount*(r_ja_in_tax_codes.surcharge_rate/100); -- added by xin on 21-Nov-2012 for bug 14806969
3219 ln_thhold_total_sur_amt := ln_thhold_total_sur_amt + ln_trx_surcharge_amount;
3220 end if;
3221 end loop;
3222
3223 jai_ap_tds_generation_pkg.generate_tds_invoices
3224 (
3225 pn_invoice_id => p_invoice_id ,
3226 pn_threshold_hdr_id => ln_threshold_hdr_id ,
3227 pn_taxable_amount => ln_sur_applicable_amt,
3228 pn_tax_amount => ln_thhold_total_sur_amt ,
3229 pn_tax_id => r_jai_ap_tds_thhold_taxes.tax_id ,
3230 pd_accounting_date => p_accounting_date ,
3231 pv_tds_event => 'SURCHARGE_CALCULATE' ,
3232 pn_threshold_grp_id => p_threshold_grp_id ,
3233 pv_tds_invoice_num => lv_tds_invoice_num ,
3234 pv_cm_invoice_num => lv_tds_cm_num ,
3235 pn_threshold_trx_id => ln_threshold_trx_id ,
3236 pd_creation_date => sysdate,
3237 pn_calc_tax_amount => 0, /* Added for bug 12965614 */
3238 p_process_flag => p_process_flag ,
3239 p_process_message => p_process_message
3240 );
3241
3242 if p_process_flag = 'E' then
3243 goto exit_from_procedure;
3244 end if;
3245
3246 /* Update the total tax amount for which invoice was raised */
3247 ln_threshold_grp_id:= p_threshold_grp_id;
3248 maintain_thhold_grps
3249 (
3250 p_threshold_grp_id => ln_threshold_grp_id,
3251 p_trx_tax_paid => ln_thhold_total_sur_amt,
3252 p_trx_thhold_change_tax_paid => ln_thhold_total_sur_amt,
3253 p_trx_threshold_slab_id => p_threshold_slab_id,
3254 p_tds_event => 'SURCHARGE_CALCULATE',
3255 p_invoice_id => p_invoice_id,
3256 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
3257 p_creation_date => sysdate,
3258 p_process_flag => p_process_flag,
3259 P_process_message => P_process_message,
3260 p_codepath => lv_codepath
3264 /* END, Bgowrava for bug#8254510*/
3261 );
3262
3263 end if;
3265
3266 <<exit_from_procedure>>
3267 return;
3268
3269 exception
3270 when others then
3271 p_process_flag := 'E';
3272 p_process_message := 'Error from jai_ap_tds_generation_pkg.process_threshold_transition :' || sqlerrm;
3273 end process_threshold_transition;
3274
3275 /* *********************************** procedure import_and_approve ********************************** */
3276
3277 procedure import_and_approve
3278 (
3279 p_invoice_id in number,
3280 p_start_thhold_trx_id in number,
3281 p_tds_event in varchar2,
3282 p_process_flag out nocopy varchar2,
3283 p_process_message out nocopy varchar2
3284 )
3285 is
3286
3287 cursor c_ap_invoices_all(p_invoice_id number) is
3288 select vendor_id,
3289 vendor_site_id,
3290 org_id
3291 from ap_invoices_all
3292 where invoice_id = p_invoice_id;
3293
3294 cursor c_ja_in_po_vendor_sites(p_vendor_id number, p_vendor_site_id number) is
3295 select nvl( approved_invoice_flag, 'N' ) approved_invoice_flag
3296 from JAI_CMN_VENDOR_SITES
3297 where vendor_id = p_vendor_id
3298 and vendor_site_id = p_vendor_site_id;
3299
3300
3301 r_ap_invoices_all c_ap_invoices_all%rowtype;
3302
3303 lb_result boolean;
3304 ln_import_request_id number;
3305 ln_approve_request_id number;
3306 lv_approved_invoice_flag JAI_CMN_VENDOR_SITES.approved_invoice_flag%type;
3307 lv_batch_name ap_batches_all.batch_name%TYPE; --added by Ramananda for Bug#4584221
3308 lv_group_id VARCHAR2(80); --Added by Sanjikum for Bug#5131075(4722011)
3309
3310 begin
3311 fnd_file.put_line (fnd_file.log, 'p_tds_event='||p_tds_event);
3312 --Added by Sanjikum for Bug#5131075(4722011)
3313 IF p_tds_event = 'PREPAYMENT APPLICATION' OR p_tds_event = 'PREPAYMENT UNAPPLICATION' THEN
3314 lv_group_id := to_char(p_invoice_id)||p_tds_event;
3315 ELSE
3316 lv_group_id := to_char(p_invoice_id);
3317 END IF;
3318
3319
3320 /* Invoke payables open interface */
3321
3322 lb_result := fnd_request.set_mode(true);
3323
3324 open c_ap_invoices_all(p_invoice_id);
3325 fetch c_ap_invoices_all into r_ap_invoices_all;
3326 close c_ap_invoices_all;
3327
3328 lv_batch_name := jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id, r_ap_invoices_all.org_id); --Ramananda for bug#4584221 --added org_id parameter for Bug#9149941
3329
3330 ln_import_request_id :=
3331 fnd_request.submit_request
3332 (
3333 'SQLAP',
3334 'APXIIMPT',
3335 'Import TDS invoices - ' || lower(p_tds_event),
3336 '',
3337 false,
3338 /* Bug 4774647. Added by Lakshmi Gopalsami
3339 Passed operating unit also as this parameter has been
3340 added by base .
3341 */
3342 '',
3343 'INDIA TDS', /*--'TDS', --Ramanand for bug#4388958*/
3344 --'',
3345 --Commented the above and added the below by Sanjikum for Bug#5131075(4722011)
3346 lv_group_id, -- Chaged from to_char(p_invoice_id) for bug# 6119216
3347 --'TDS'||TO_CHAR(TRUNC(SYSDATE)),
3348 --commented the above and added the below by Ramananda for Bug#4584221
3349 lv_batch_name,
3350 '',
3351 '',
3352 '',
3353 'Y',
3354 'N',
3355 'N',
3356 'N',
3357 1000,
3358 fnd_global.user_id,
3359 fnd_global.login_id
3360 );
3361
3362 /* Get vendor and site for the invoice */
3363 /* open c_ap_invoices_all(p_invoice_id);
3364 fetch c_ap_invoices_all into r_ap_invoices_all;
3365 close c_ap_invoices_all;*/ --moved this code to the start of the procedure for Bug#9149941
3366
3367 /* Check if Pre-approved TDS invoices setup has been set for the vendor */
3368
3369 /* Check for vendor and site */
3370 open c_ja_in_po_vendor_sites(r_ap_invoices_all.vendor_id, r_ap_invoices_all.vendor_site_id);
3371 fetch c_ja_in_po_vendor_sites into lv_approved_invoice_flag;
3372 close c_ja_in_po_vendor_sites;
3373
3374 if nvl(lv_approved_invoice_flag, 'N') <> 'Y' then
3375 /* Pre-approved TDS invoice is not set for vendor and site, Check for vendor and null site */
3376 open c_ja_in_po_vendor_sites(r_ap_invoices_all.vendor_id, 0);
3377 fetch c_ja_in_po_vendor_sites into lv_approved_invoice_flag;
3378 close c_ja_in_po_vendor_sites;
3379 end if;
3380
3381 if nvl(lv_approved_invoice_flag, 'N') <> 'Y' then
3382 /* Setup for pre-approved TDS invoice is not there for the vendor for site or null site. */
3383 goto exit_from_procedure;
3384 end if;
3385
3386
3387 /* Control comes here only when Pre-approved TDS invoice is setup for the vendor,
3388 we need to invoke the request for approval */
3389 lb_result := fnd_request.set_mode(true);
3390
3391 ln_approve_request_id :=
3392 fnd_request.submit_request
3393 (
3394 'JA',
3395 'JAITDSA',
3396 'Approval Of TDS Invoices ',
3397 sysdate,
3398 false,
3399 ln_import_request_id,
3400 p_invoice_id,
3401 r_ap_invoices_all.vendor_id,
3402 r_ap_invoices_all.vendor_site_id,
3403 p_start_thhold_trx_id
3404 );
3405
3406
3407 <<exit_from_procedure>>
3408 return;
3409
3410 exception
3414 end import_and_approve;
3411 when others then
3412 p_process_flag := 'E';
3413 p_process_message := 'Error from jai_ap_tds_generation_pkg.import_and_approve :' || sqlerrm;
3415
3416 /* *********************************** procedure import_and_approve ********************************** */
3417
3418 /* *********************************** procedure approve_tds_invoices ********************************** */
3419
3420 procedure approve_tds_invoices
3421 (
3422 errbuf out nocopy varchar2,
3423 retcode out nocopy varchar2,
3424 p_parent_request_id in number,
3425 p_invoice_id in number,
3426 p_vendor_id in number,
3427 p_vendor_site_id in number,
3428 p_start_thhold_trx_id in number
3429 )
3430 is
3431
3432 cursor c_jai_ap_tds_thhold_trxs
3433 (p_invoice_id number, p_start_thhold_trx_id number, p_vendor_id number, p_vendor_site_id number) is
3434 select invoice_to_tds_authority_id,
3435 invoice_to_vendor_id,
3436 invoice_to_tds_authority_num,
3437 invoice_to_vendor_num
3438 from jai_ap_tds_thhold_trxs
3439 where threshold_trx_id >= p_start_thhold_trx_id
3440 and invoice_id = p_invoice_id
3441 and vendor_id = p_vendor_id
3442 and vendor_site_id = p_vendor_site_id;
3443
3444 CURSOR c_jai_chk_tds_inv (p_invoice_id number) IS
3445 SELECT invoice_id, org_id,
3446 set_of_books_id -- bug 6819855. Added by Lakshmi Gopalsami
3447 FROM ap_invoices_all
3448 WHERE invoice_id = p_invoice_id;
3449
3450 lb_request_status boolean;
3451 lv_phase varchar2(100);
3452 lv_status varchar2(100);
3453 lv_dev_phase varchar2(100);
3454 lv_dev_status varchar2(100);
3455 lv_message varchar2(100);
3456
3457 ln_holds_count number;
3458 lv_approval_status varchar2(100);
3459 lv_conc_flag varchar2(10);
3460
3461 /* Bug 4872659. Added by Lakshmi Gopalsami */
3462 ln_tds_invoice_id NUMBER;
3463 ln_vendor_invoice_id NUMBER;
3464 ln_org_id NUMBER;
3465
3466 /* Bug 4943949. Added by Lakshmi gopalsami */
3467 lv_funds_ret_code varchar2(5);
3468
3469 /* Bug 6819855. Added by Lakshmi Gopalsami */
3470 ln_sob_id NUMBER;
3471 ln_holds_count1 NUMBER;
3472
3473 begin
3474
3475 /* Check for the status of the import request */
3476 Fnd_File.put_line(Fnd_File.LOG, 'jai_ap_tds_generation_pkg.approve_tds_invoices');
3477 Fnd_File.put_line(Fnd_File.LOG, 'p_parent_request_id =>' || p_parent_request_id);
3478 Fnd_File.put_line(Fnd_File.LOG, 'p_invoice_id=> ' || p_invoice_id );
3479 Fnd_File.put_line(Fnd_File.LOG, 'p_vendor_id=> ' || p_vendor_id);
3480 Fnd_File.put_line(Fnd_File.LOG, 'p_vendor_site_id=> ' || p_vendor_site_id);
3481 Fnd_File.put_line(Fnd_File.LOG, 'p_start_thhold_trx_id=> ' || p_start_thhold_trx_id);
3482
3483 lb_request_status :=
3484 fnd_concurrent.wait_for_request
3485 (
3486 request_id => p_parent_request_id,
3487 interval => 60, /* default value - sleep time in secs */
3488 max_wait => 0, /* default value - max wait in secs */
3489 phase => lv_phase,
3490 status => lv_status,
3491 dev_phase => lv_dev_phase,
3492 dev_status => lv_dev_status,
3493 message => lv_message
3494 );
3495
3496
3497 if not ( lv_dev_phase = 'COMPLETE' and lv_dev_status = 'NORMAL' ) then
3498
3499 Fnd_File.put_line(Fnd_File.LOG, 'Exiting with warning as parent request not completed with normal status');
3500 Fnd_File.put_line(Fnd_File.LOG, 'Message from parent request :' || lv_message);
3501 retcode := 1;
3502 errbuf := 'Exiting with warnings as parent request not completed with normal status';
3503 goto exit_from_procedure;
3504
3505 end if;
3506
3507 /* Control comes here only when the concurrent request has completed with Normal Status */
3508 Fnd_File.put_line(Fnd_File.LOG, 'Before Loop ');
3509
3510 /* Get all the tds invoices that have been created and call the base API to approve it */
3511 for cur_rec in
3512 c_jai_ap_tds_thhold_trxs(p_invoice_id , p_start_thhold_trx_id , p_vendor_id , p_vendor_site_id)
3513 loop
3514
3515
3516 /* Get the status of both the invoices and call approval API, if it is not already approved */
3517 ln_holds_count := 0;
3518 lv_approval_status := null;
3519
3520 /* Bug 4872659. Added by Lakshmi Gopalsami
3521 There is a possibility that the invoice gets rejected via Interface and
3522 the invoice is not existing. Base requires the org_id from the
3523 invoice_id we pass. Ensure that the invoice_id exists before calling approval
3524 */
3525
3526 fnd_file.put_line(FND_FILE.LOG, ' Check for the TDS authority invoice ');
3527
3528 /*Added by nprashar for bug # 6720018*/
3529 IF (FND_GLOBAL.CONC_REQUEST_ID is NULL) THEN
3530 lv_conc_flag := 'N';
3531 ELSE
3532 lv_conc_flag := 'Y';
3533 END IF;
3534
3535 If cur_rec.invoice_to_tds_authority_id is not null Then
3536
3537 OPEN c_jai_chk_tds_inv(cur_rec.invoice_to_tds_authority_id);
3538 FETCH c_jai_chk_tds_inv INTO ln_tds_invoice_id,
3539 ln_org_id,
3540 --Bug 6819855. Added by Lakshmi Gopalsami
3541 ln_sob_id;
3542 CLOSE c_jai_chk_tds_inv;
3543
3547 mo_global.set_policy_context('S', ln_org_id);
3544 fnd_file.put_line(FND_FILE.LOG, ' Org id ' || ln_org_id);
3545
3546
3548
3549 fnd_file.put_line(FND_FILE.LOG,' TDS authority invoice id '
3550 || cur_rec.invoice_to_tds_authority_id);
3551
3552 /* Invoice to TDS Authority */
3553 /* Bug 6819855. Added by Lakshmi Gopalsami
3554 Commented the following code and added a call to function batch_approval
3555 ap_approval_pkg.approve
3556 (
3557 p_run_option => null,
3558 p_invoice_batch_id => null,
3559 p_begin_invoice_date => null,
3560 p_end_invoice_date => null,
3561 p_vendor_id => null,
3562 p_pay_group => null,
3563 p_invoice_id => cur_rec.invoice_to_tds_authority_id,
3564 p_entered_by => null,
3565 p_set_of_books_id => null,
3566 p_trace_option => null,
3567 p_conc_flag => lv_conc_flag 'N', /*Changed by nprashar for bug # 6720018
3568 p_holds_count => ln_holds_count,
3569 p_approval_status => lv_approval_status,
3570 /* Bug 4943949. Added by Lakshmi Gopalsami
3571 p_funds_return_code => lv_funds_ret_code,
3572 p_calling_sequence => 'jai_ap_tds_generation_pkg.approve_tds_invoices'
3573 ) ;
3574 */
3575 BEGIN
3576 IF ap_approval_pkg.batch_approval(
3577 p_run_option => 'New',
3578 p_sob_id => ln_sob_id,
3579 p_inv_start_date => NULL,
3580 p_inv_end_date => NULL,
3581 p_inv_batch_id => NULL,
3582 p_vendor_id => NULL,
3583 p_pay_group => NULL,
3584 p_invoice_id => cur_rec.invoice_to_tds_authority_id,
3585 p_entered_by => NULL,
3586 p_debug_switch => 'N',
3587 p_conc_request_id => FND_GLOBAL.CONC_REQUEST_ID,
3588 p_commit_size => 1000,
3589 p_org_id => ln_org_id,
3590 p_report_holds_count => ln_holds_count
3591 ) THEN
3592
3593 Fnd_File.put_line(Fnd_File.LOG, 'Invoice to TDS Authority ' ||
3594 cur_rec.invoice_to_tds_authority_num ||
3595 '(' || cur_rec.invoice_to_tds_authority_id ||
3596 ') Was submitted for Approval.
3597 Holds count ' || ln_holds_count);
3598 END IF ;
3599 EXCEPTION
3600 WHEN OTHERS THEN
3601 retcode := 'E';
3602 errbuf := 'Error from jai_ap_tds_generation_pkg.approve_tds_invoices-> :
3603 during call to batch_approval for TDS invoice' || sqlerrm;
3604 END;
3605
3606 End if;
3607
3608 /* Invoice to Supplier */
3609 ln_holds_count1 := 0;
3610 lv_approval_status := null;
3611
3612 If cur_rec.invoice_to_vendor_id is not null Then
3613
3614 OPEN c_jai_chk_tds_inv(cur_rec.invoice_to_vendor_id);
3615 FETCH c_jai_chk_tds_inv INTO ln_vendor_invoice_id,
3616 ln_org_id,
3617 --Bug 6819855. Added by Lakshmi Gopalsami
3618 ln_sob_id;
3619 CLOSE c_jai_chk_tds_inv;
3620
3621 mo_global.set_policy_context('S', ln_org_id);
3622
3623 fnd_file.put_line(FND_FILE.LOG,' Supplier credit invoice id '
3624 || cur_rec.invoice_to_vendor_id);
3625 /* Bug 6819855. Added by Lakshmi Gopalsami
3626 Commented the following code and added a call to function batch_approval
3627 ap_approval_pkg.approve
3628 (
3629 p_run_option => null,
3630 p_invoice_batch_id => null,
3631 p_begin_invoice_date => null,
3632 p_end_invoice_date => null,
3633 p_vendor_id => null,
3634 p_pay_group => null,
3635 p_invoice_id => cur_rec.invoice_to_vendor_id,
3636 p_entered_by => null,
3637 p_set_of_books_id => null,
3638 p_trace_option => null,
3639 p_conc_flag => lv_conc_flag /*'N', /*Changed by nprashar for bug # 6720018
3640 p_holds_count => ln_holds_count,
3641 p_approval_status => lv_approval_status,
3642 /* Bug 4943949. Added by Lakshmi Gopalsami
3643 p_funds_return_code => lv_funds_ret_code,
3644 p_calling_sequence => 'jai_ap_tds_generation_pkg.approve_tds_invoices'
3645 ) ;
3646 */
3647
3648 BEGIN
3649 IF ap_approval_pkg.batch_approval(
3650 p_run_option => 'New',
3651 p_sob_id => ln_sob_id,
3652 p_inv_start_date => NULL,
3653 p_inv_end_date => NULL,
3654 p_inv_batch_id => NULL,
3655 p_vendor_id => NULL,
3656 p_pay_group => NULL,
3657 p_invoice_id => cur_rec.invoice_to_vendor_id,
3658 p_entered_by => NULL,
3659 p_debug_switch => 'N',
3660 p_conc_request_id => FND_GLOBAL.CONC_REQUEST_ID,
3661 p_commit_size => 1000,
3662 p_org_id => ln_org_id,
3663 p_report_holds_count => ln_holds_count1
3664 ) THEN
3665
3666 Fnd_File.put_line(Fnd_File.LOG, 'Invoice to Supplier for TDS' ||
3667 cur_rec.invoice_to_vendor_num ||
3668 '(' || cur_rec.invoice_to_vendor_id ||
3669 ') Was submitted for Approval.
3670 Holds count ' || ln_holds_count1);
3671 END IF ;
3672 EXCEPTION
3673 WHEN OTHERS THEN
3674 retcode := 'E';
3675 errbuf := 'Error from jai_ap_tds_generation_pkg.approve_tds_invoices-> :
3679
3676 during call to batch_approval for TDS invoice' || sqlerrm;
3677 END;
3678
3680 End if;
3681 end loop;
3682
3683
3684 <<exit_from_procedure>>
3685
3686 return;
3687
3688 exception
3689 when others then
3690 retcode := 2;
3691 errbuf := 'Error from jai_ap_tds_generation_pkg.approve_tds_invoices : ' || sqlerrm;
3692 end approve_tds_invoices;
3693
3694 /* ********************************* populate_tds_invoice_id **************************************** */
3695
3696 procedure populate_tds_invoice_id
3697 (
3698 p_invoice_id in number,
3699 p_invoice_num in varchar2,
3700 p_vendor_id in number,
3701 p_vendor_site_id in number,
3702 p_process_flag out nocopy varchar2,
3703 p_process_message out nocopy varchar2
3704 )
3705 is
3706
3707 cursor c_check_inv_to_tds_authority (p_invoice_num varchar2, p_vendor_id number, p_vendor_site_id number) is
3708 select threshold_trx_id,
3709 invoice_id
3710 from jai_ap_tds_thhold_trxs
3711 where invoice_to_tds_authority_num = p_invoice_num
3712 and tds_authority_vendor_id = p_vendor_id
3713 and tds_authority_vendor_site_id = p_vendor_site_id
3714 and invoice_to_tds_authority_id is null;
3715
3716
3717 cursor c_check_inv_to_vendor (p_invoice_num varchar2, p_vendor_id number, p_vendor_site_id number) is
3718 select threshold_trx_id
3719 from jai_ap_tds_thhold_trxs
3720 where invoice_to_vendor_num = p_invoice_num
3721 and vendor_id = p_vendor_id
3722 and vendor_site_id = p_vendor_site_id
3723 and invoice_to_vendor_id is null;
3724
3725 ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%type;
3726 ln_invoice_id ap_invoices_all.invoice_id%type;
3727
3728
3729 begin
3730
3731 open c_check_inv_to_tds_authority(p_invoice_num, p_vendor_id, p_vendor_site_id);
3732 fetch c_check_inv_to_tds_authority into ln_threshold_trx_id, ln_invoice_id;
3733 close c_check_inv_to_tds_authority;
3734
3735 if ln_threshold_trx_id is not null then
3736 /* Invoice being created is the invoice to TDS authority */
3737
3738 update jai_ap_tds_thhold_trxs
3739 set invoice_to_tds_authority_id = p_invoice_id
3740 where threshold_trx_id = ln_threshold_trx_id;
3741
3742 else
3743
3744 /* Invoice being created is not the invoice to TDS authority */
3745 /* check if it is the invoice to vendor for TDS */
3746 open c_check_inv_to_vendor(p_invoice_num, p_vendor_id, p_vendor_site_id);
3747 fetch c_check_inv_to_vendor into ln_threshold_trx_id;
3748 close c_check_inv_to_vendor;
3749
3750 if ln_threshold_trx_id is not null then
3751
3752 /* Invoice being created is teh invoice to TDS authority */
3753 update jai_ap_tds_thhold_trxs
3754 set invoice_to_vendor_id = p_invoice_id
3755 where threshold_trx_id = ln_threshold_trx_id;
3756
3757 end if; /* TDS invoice to vendor */
3758
3759 end if; /* TDS invoice to TDS authority */
3760
3761
3762 <<exit_from_procedure>>
3763 return;
3764
3765 exception
3766 when others then
3767 p_process_flag := 'E';
3768 p_process_message := 'Error from jai_ap_tds_generation_pkg.populate_tds_invoice_id :' || sqlerrm;
3769 end populate_tds_invoice_id;
3770
3771 /* ********************************* populate_tds_invoice_id **************************************** */
3772
3773 /* ******************************** maintain_thhold_grps ******************************************* */
3774
3775 procedure maintain_thhold_grps
3776 (
3777 p_threshold_grp_id in out nocopy number ,
3778 p_vendor_id in number default null,
3779 p_org_tan_num in varchar2 default null,
3780 p_vendor_pan_num in varchar2 default null,
3781 p_section_type in varchar2 default null,
3782 p_section_code in varchar2 default null,
3783 p_fin_year in number default null,
3784 p_org_id in number default null,
3785 p_trx_invoice_amount in number default null,
3786 p_trx_invoice_cancel_amount in number default null,
3787 p_trx_invoice_apply_amount in number default null,
3788 p_trx_invoice_unapply_amount in number default null,
3789 p_trx_tax_paid in number default null,
3790 p_trx_thhold_change_tax_paid in number default null,
3791 p_trx_threshold_slab_id in number default null,
3792 p_tds_event in varchar2,
3793 p_invoice_id in number default null,
3794 p_invoice_line_number in number default null, /* AP lines Uptake */
3795 p_invoice_distribution_id in number default null,
3796 p_remarks in varchar2 default null,
3797 -- bug 5722028. Added by csahoo
3798 p_creation_date in date default sysdate,
3799 p_threshold_grp_audit_id out nocopy number,
3800 p_process_flag out nocopy varchar2,
3804 is
3801 P_process_message out nocopy varchar2,
3802 p_codepath in out nocopy varchar2
3803 )
3805
3806
3807 cursor c_jai_ap_tds_thhold_grps(p_threshold_grp_id number) is
3808 select total_invoice_amount ,
3809 total_invoice_cancel_amount ,
3810 total_invoice_apply_amount ,
3811 total_invoice_unapply_amount ,
3812 total_tax_paid ,
3813 total_thhold_change_tax_paid ,
3814 current_threshold_slab_id ,
3815 total_calc_tax_paid -- Bug 5751783
3816 from jai_ap_tds_thhold_grps
3817 where threshold_grp_id = p_threshold_grp_id;
3818
3819 cursor c_get_threshold_grp_id
3820 ( p_vendor_id number, p_org_tan_num varchar2, p_vendor_pan_num varchar2,
3821 p_section_type varchar2, p_section_code varchar2, p_fin_year number) is
3822 select threshold_grp_id
3823 from jai_ap_tds_thhold_grps
3824 where vendor_id = p_vendor_id and
3825 org_tan_num = p_org_tan_num and
3826 vendor_pan_num = p_vendor_pan_num and
3827 section_type = p_section_type and
3828 section_code = p_section_code and
3829 fin_year = p_fin_year;
3830
3831
3832 r_jai_ap_tds_thhold_grps c_jai_ap_tds_thhold_grps%rowtype;
3833
3834 ln_threshold_grp_id jai_ap_tds_thgrp_audits.threshold_grp_id%type;
3835 ln_old_invoice_amount jai_ap_tds_thgrp_audits.old_invoice_amount%type;
3836 ln_old_invoice_cancel_amount jai_ap_tds_thgrp_audits.old_invoice_cancel_amount%type;
3837 ln_old_invoice_apply_amount jai_ap_tds_thgrp_audits.old_invoice_apply_amount%type;
3838 ln_old_invoice_unapply_amount jai_ap_tds_thgrp_audits.old_invoice_unapply_amount%type;
3839 ln_old_tax_paid jai_ap_tds_thgrp_audits.old_tax_paid%type;
3840 ln_old_thhold_change_tax_paid jai_ap_tds_thgrp_audits.old_thhold_change_tax_paid%type;
3841 ln_old_threshold_slab_id jai_ap_tds_thgrp_audits.old_threshold_slab_id%type;
3842
3843 ln_new_invoice_amount jai_ap_tds_thgrp_audits.old_invoice_amount%type;
3844 ln_new_invoice_cancel_amount jai_ap_tds_thgrp_audits.old_invoice_cancel_amount%type;
3845 ln_new_invoice_apply_amount jai_ap_tds_thgrp_audits.old_invoice_apply_amount%type;
3846 ln_new_invoice_unapply_amount jai_ap_tds_thgrp_audits.old_invoice_unapply_amount%type;
3847 ln_new_tax_paid jai_ap_tds_thgrp_audits.old_tax_paid%type;
3848 ln_new_thhold_change_tax_paid jai_ap_tds_thgrp_audits.old_thhold_change_tax_paid%type;
3849 ln_new_threshold_slab_id jai_ap_tds_thgrp_audits.old_threshold_slab_id%type;
3850 ln_effective_threshold_amount number;
3851 ln_effective_tax_paid number;
3852
3853 /*Bug 5751783. Added following variables.*/
3854 ln_calc_old_tax_paid jai_ap_tds_thgrp_audits.calc_old_tax_paid%type;
3855 ln_calc_trx_tax_paid jai_ap_tds_thgrp_audits.calc_trx_tax_paid%type;
3856 ln_calc_new_tax_paid jai_ap_tds_thgrp_audits.calc_new_tax_paid%type;
3857
3858 -- bug 5722028. Added by csahoo
3859 ln_tmp_tds_amt number;
3860 ln_tmp_tds_change number;
3861
3862
3863 begin
3864
3865 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_generation_pkg.maintain_thhold_grps', 'START'); /* 1 */
3866
3867
3868 /* Validate the input */
3869 ln_threshold_grp_id := nvl(p_threshold_grp_id, 0);
3870
3871 if ln_threshold_grp_id = 0 then
3872
3873 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /*2*/
3874
3875 if p_vendor_id is null then
3876 p_process_flag := 'E';
3877 P_process_message := 'Vendor must be specified as threshold group identifier is null(jai_ap_tds_generation_pkg.maintain_thhold_grps) ';
3878 goto exit_from_procedure;
3879 end if;
3880
3881 if p_org_tan_num is null then
3882 p_process_flag := 'E';
3883 P_process_message := 'Organization TAN number must be specified as threshold group identifier is null(jai_ap_tds_generation_pkg.maintain_thhold_grps) ';
3884 goto exit_from_procedure;
3885 end if;
3886
3887 if p_vendor_pan_num is null then
3888 p_process_flag := 'E';
3889 P_process_message := 'Vendor PAN number must be specified as threshold group identifier is null(jai_ap_tds_generation_pkg.maintain_thhold_grps) ';
3890 goto exit_from_procedure;
3891 end if;
3892
3893 if p_section_type is null then
3894 p_process_flag := 'E';
3895 P_process_message := 'Section Type must be specified as threshold group identifier is null(jai_ap_tds_generation_pkg.maintain_thhold_grps) ';
3896 goto exit_from_procedure;
3897 end if;
3898
3899 if p_section_code is null then
3900 p_process_flag := 'E';
3901 P_process_message := 'Section Code must be specified as threshold group identifier is null(jai_ap_tds_generation_pkg.maintain_thhold_grps) ';
3902 goto exit_from_procedure;
3903 end if;
3904
3905 if p_fin_year is null then
3906 p_process_flag := 'E';
3907 P_process_message := 'Fin Year must be specified as threshold group identifier is null(jai_ap_tds_generation_pkg.maintain_thhold_grps) ';
3908 goto exit_from_procedure;
3909 end if;
3910
3911 end if; /* Validate the input */
3912
3913 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /*3*/
3914
3915 if ln_threshold_grp_id = 0 then
3919 open c_get_threshold_grp_id
3916
3917 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
3918 /* Threshold has not been given as an input, check if exists */
3920 (p_vendor_id, p_org_tan_num, p_vendor_pan_num, p_section_type, p_section_code, p_fin_year);
3921 fetch c_get_threshold_grp_id into ln_threshold_grp_id;
3922 close c_get_threshold_grp_id;
3923
3924 if nvl(ln_threshold_grp_id, 0) = 0 then
3925
3926 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
3927
3928 insert into jai_ap_tds_thhold_grps
3929 (
3930 threshold_grp_id ,
3931 vendor_id ,
3932 org_tan_num ,
3933 vendor_pan_num ,
3934 section_type ,
3935 section_code ,
3936 fin_year ,
3937 created_by ,
3938 creation_date ,
3939 last_updated_by ,
3940 last_update_date ,
3941 last_update_login
3942 )
3943 values
3944 (
3945 jai_ap_tds_thhold_grps_s.nextval ,
3946 p_vendor_id ,
3947 p_org_tan_num ,
3948 p_vendor_pan_num ,
3949 p_section_type ,
3950 p_section_code ,
3951 p_fin_year ,
3952 fnd_global.user_id ,
3953 sysdate ,
3954 fnd_global.user_id ,
3955 sysdate ,
3956 fnd_global.login_id
3957 )
3958 returning threshold_grp_id into ln_threshold_grp_id;
3959
3960 p_threshold_grp_id := ln_threshold_grp_id;
3961
3962 end if; /* ln_threshold_grp_id does not exist */
3963
3964 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
3965
3966 end if; /* ln_threshold_grp_id is not given as an input */
3967
3968 /* Get the old value of teh threshold group */
3969 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
3970 open c_jai_ap_tds_thhold_grps(ln_threshold_grp_id);
3971 fetch c_jai_ap_tds_thhold_grps into r_jai_ap_tds_thhold_grps;
3972 close c_jai_ap_tds_thhold_grps;
3973
3974 ln_old_invoice_amount := r_jai_ap_tds_thhold_grps.total_invoice_amount;
3975 ln_old_invoice_cancel_amount := r_jai_ap_tds_thhold_grps.total_invoice_cancel_amount;
3976 ln_old_invoice_apply_amount := r_jai_ap_tds_thhold_grps.total_invoice_apply_amount;
3977 ln_old_invoice_unapply_amount := r_jai_ap_tds_thhold_grps.total_invoice_unapply_amount;
3978 ln_old_tax_paid := r_jai_ap_tds_thhold_grps.total_tax_paid;
3979 ln_old_thhold_change_tax_paid := r_jai_ap_tds_thhold_grps.total_thhold_change_tax_paid;
3980 ln_old_threshold_slab_id := r_jai_ap_tds_thhold_grps.current_threshold_slab_id;
3981
3982 /*Bug 5751783*/
3983 ln_calc_old_tax_paid := r_jai_ap_tds_thhold_grps.total_calc_tax_paid;
3984
3985 /* Check that threshold should not become negative */
3986 ln_effective_threshold_amount :=
3987 ( nvl(ln_old_invoice_amount, 0) + nvl(p_trx_invoice_amount, 0) ) -
3988 ( nvl(ln_old_invoice_cancel_amount, 0) + nvl(p_trx_invoice_cancel_amount, 0) ) -
3989 ( nvl(ln_old_invoice_apply_amount, 0) + nvl(p_trx_invoice_apply_amount, 0) ) +
3990 (nvl(ln_old_invoice_unapply_amount, 0) + nvl(p_trx_invoice_unapply_amount, 0) );
3991
3992 if ln_effective_threshold_amount < 0 then
3993 p_process_flag := 'E';
3994 p_process_message := 'Effective Total invoice amount for threshold cannot be negative.(Total Invoice - Cancel - apply + Unapply )' ;
3995 goto exit_from_procedure;
3996 end if;
3997
3998 /* Check that total tax paid should not become negative */
3999 ln_effective_tax_paid := nvl(ln_old_tax_paid, 0) + nvl(p_trx_tax_paid, 0);
4000 if ln_effective_tax_paid < 0 then
4001 p_process_flag := 'E';
4002 p_process_message := 'Effective Tax Paid amount cannot be negative.' ;
4003 goto exit_from_procedure;
4004 end if;
4005
4006 -- Bug 5722028. Added by Lakshmi Gopalsami
4007 ln_tmp_tds_amt := ROUND(nvl(p_trx_tax_paid,0),g_inr_currency_rounding);
4008 ln_tmp_tds_change := ROUND(nvl(p_trx_thhold_change_tax_paid,0), g_inr_currency_rounding);
4009
4010 IF p_tds_event NOT IN
4011 -- Bug 7280925. Commented by Lakshmi Gopalsami ('INVOICE CANCEL',
4012 ('PREPAYMENT UNAPPLICATION') THEN
4013 IF trunc(p_creation_date) >=
4014 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
4015 ln_tmp_tds_amt := get_rnded_value(ln_tmp_tds_amt);
4016 END IF;
4017 END IF;
4018 -- End if ;
4019
4020 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
4021 update jai_ap_tds_thhold_grps
4022 set
4023 total_invoice_amount = nvl(total_invoice_amount, 0) + nvl(p_trx_invoice_amount, 0),
4024 total_invoice_cancel_amount = nvl(total_invoice_cancel_amount, 0) + nvl(p_trx_invoice_cancel_amount, 0),
4025 total_invoice_apply_amount = nvl(total_invoice_apply_amount, 0) + nvl(p_trx_invoice_apply_amount, 0),
4026 total_invoice_unapply_amount = nvl(total_invoice_unapply_amount, 0) + nvl(p_trx_invoice_unapply_amount, 0),
4027 total_tax_paid = nvl(total_tax_paid, 0) + nvl(p_trx_tax_paid, 0),
4028 total_thhold_change_tax_paid = nvl(total_thhold_change_tax_paid, 0) + nvl(p_trx_thhold_change_tax_paid, 0),
4032 /*Bug 5751783. Updated non-rounded value*/
4029 --current_threshold_slab_id = nvl( p_trx_threshold_slab_id, current_threshold_slab_id)
4030 --commented the above and added the below by Ramananda for Bug#4562793
4031 current_threshold_slab_id = nvl( p_trx_threshold_slab_id, 0),
4033 total_calc_tax_paid = nvl(total_calc_tax_paid,0) + nvl(p_trx_tax_paid,0)
4034
4035 where threshold_grp_id = ln_threshold_grp_id;
4036
4037 /* Get the new value */
4038 r_jai_ap_tds_thhold_grps := null;
4039 open c_jai_ap_tds_thhold_grps(ln_threshold_grp_id);
4040 fetch c_jai_ap_tds_thhold_grps into r_jai_ap_tds_thhold_grps;
4041 close c_jai_ap_tds_thhold_grps;
4042
4043 ln_new_invoice_amount := r_jai_ap_tds_thhold_grps.total_invoice_amount;
4044 ln_new_invoice_cancel_amount := r_jai_ap_tds_thhold_grps.total_invoice_cancel_amount;
4045 ln_new_invoice_apply_amount := r_jai_ap_tds_thhold_grps.total_invoice_apply_amount;
4046 ln_new_invoice_unapply_amount := r_jai_ap_tds_thhold_grps.total_invoice_unapply_amount;
4047 ln_new_tax_paid := r_jai_ap_tds_thhold_grps.total_tax_paid;
4048 ln_new_thhold_change_tax_paid := r_jai_ap_tds_thhold_grps.total_thhold_change_tax_paid;
4049 ln_new_threshold_slab_id := r_jai_ap_tds_thhold_grps.current_threshold_slab_id;
4050
4051 /*Bug 5751783*/
4052 ln_calc_new_tax_paid := r_jai_ap_tds_thhold_grps.total_calc_tax_paid;
4053
4054 /* Insert into the audite table */
4055 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
4056 insert into jai_ap_tds_thgrp_audits
4057 (
4058 threshold_grp_audit_id ,
4059 threshold_grp_id ,
4060 old_invoice_amount ,
4061 old_invoice_cancel_amount ,
4062 old_invoice_apply_amount ,
4063 old_invoice_unapply_amount ,
4064 old_tax_paid ,
4065 old_thhold_change_tax_paid ,
4066 old_threshold_slab_id ,
4067 trx_invoice_amount ,
4068 trx_invoice_cancel_amount ,
4069 trx_invoice_apply_amount ,
4070 trx_invoice_unapply_amount ,
4071 trx_tax_paid ,
4072 trx_thhold_change_tax_paid ,
4073 trx_threshold_slab_id ,
4074 new_invoice_amount ,
4075 new_invoice_cancel_amount ,
4076 new_invoice_apply_amount ,
4077 new_invoice_unapply_amount ,
4078 new_tax_paid ,
4079 new_thhold_change_tax_paid ,
4080 new_threshold_slab_id ,
4081 tds_event ,
4082 invoice_id ,
4083 invoice_line_number ,
4084 invoice_distribution_id ,
4085 remarks ,
4086 created_by ,
4087 creation_date ,
4088 last_updated_by ,
4089 last_update_date ,
4090 last_update_login ,
4091 /*Bug 5751783. Inserted non-rounded values also*/
4092 calc_old_tax_paid ,
4093 calc_trx_tax_paid ,
4094 calc_new_tax_paid
4095 )
4096 values
4097 (
4098 jai_ap_tds_thgrp_audits_s.nextval ,
4099 ln_threshold_grp_id ,
4100 ln_old_invoice_amount ,
4101 ln_old_invoice_cancel_amount ,
4102 ln_old_invoice_apply_amount ,
4103 ln_old_invoice_unapply_amount ,
4104 ln_old_tax_paid ,
4105 ln_old_thhold_change_tax_paid ,
4106 ln_old_threshold_slab_id ,
4107 p_trx_invoice_amount ,
4108 p_trx_invoice_cancel_amount ,
4109 p_trx_invoice_apply_amount ,
4110 p_trx_invoice_unapply_amount ,
4111 ln_tmp_tds_amt, --added for bug#5722028 csahoo
4112 p_trx_thhold_change_tax_paid ,
4113 p_trx_threshold_slab_id ,
4114 ln_new_invoice_amount ,
4115 ln_new_invoice_cancel_amount ,
4116 ln_new_invoice_apply_amount ,
4117 ln_new_invoice_unapply_amount ,
4118 ln_new_tax_paid ,
4119 ln_new_thhold_change_tax_paid ,
4120 ln_new_threshold_slab_id ,
4121 p_tds_event ,
4122 p_invoice_id ,
4123 p_invoice_line_number ,
4124 p_invoice_distribution_id ,
4125 p_remarks ,
4126 fnd_global.user_id ,
4127 sysdate ,
4128 fnd_global.user_id ,
4129 sysdate ,
4130 fnd_global.login_id ,
4131 /*Bug 5751783*/
4132 ln_calc_old_tax_paid ,
4133 ln_calc_trx_tax_paid ,
4134 ln_calc_new_tax_paid
4135 )
4136 returning threshold_grp_audit_id into p_threshold_grp_audit_id;
4137 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
4138 <<exit_from_procedure>>
4139
4140 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 1 */
4141 return;
4142
4143 exception
4144 when others then
4145 fnd_file.put_line(FND_FILE.LOG,' Error in maintain thhold grps '|| substr(SQLERRM,1,200));
4146 p_process_flag := 'E';
4147 p_process_message := 'Error from jai_ap_tds_generation_pkg.maintain_thhold_grps :' || sqlerrm;
4148 end maintain_thhold_grps;
4149
4150 /* ******************************** maintain_thhold_grps ******************************************* */
4151 /* ******************************** insert_tds_thhold_trxs ******************************************* */
4152 --for bug#4333449
4153 procedure insert_tds_thhold_trxs
4154 (
4155 p_invoice_id in number,
4156 p_tds_event in varchar2,
4157 p_tax_id in number default null,
4158 p_tax_rate in number default null,
4159 p_taxable_amount in number default null,
4160 p_tax_amount in number default null,
4161 p_tds_authority_vendor_id in number default null,
4162 p_tds_authority_vendor_site_id in number default null,
4163 p_invoice_tds_authority_num in varchar2 default null,
4164 p_invoice_tds_authority_type in varchar2 default null,
4165 p_invoice_tds_authority_curr in varchar2 default null,
4166 p_invoice_tds_authority_amt in number default null,
4167 p_invoice_tds_authority_id in number default null,
4168 p_vendor_id in number default null,
4169 p_vendor_site_id in number default null,
4170 p_invoice_vendor_num in varchar2 default null,
4171 p_invoice_vendor_type in varchar2 default null,
4172 p_invoice_vendor_curr in varchar2 default null,
4173 p_invoice_vendor_amt in number default null,
4174 p_invoice_vendor_id in number default null,
4175 p_parent_inv_payment_priority in number default null,
4176 p_parent_inv_exchange_rate in number default null
4177 )
4178 is
4179 begin
4180
4181 fnd_file.put_line(FND_FILE.LOG, ' Insert -> insert_tds_thhold_trxs ');
4182
4183 insert into jai_ap_tds_thhold_trxs
4184 (
4185 threshold_trx_id ,
4186 invoice_id ,
4187 tds_event ,
4188 tax_id ,
4189 tax_rate ,
4190 taxable_amount ,
4191 tax_amount ,
4192 tds_authority_vendor_id ,
4193 tds_authority_vendor_site_id ,
4194 invoice_to_tds_authority_num ,
4195 invoice_to_tds_authority_type ,
4196 invoice_to_tds_authority_curr ,
4197 invoice_to_tds_authority_amt ,
4198 invoice_to_tds_authority_id ,
4199 vendor_id ,
4200 vendor_site_id ,
4201 invoice_to_vendor_num ,
4202 invoice_to_vendor_type ,
4203 invoice_to_vendor_curr ,
4204 invoice_to_vendor_amt ,
4205 invoice_to_vendor_id ,
4206 parent_inv_payment_priority ,
4207 parent_inv_exchange_rate ,
4208 created_by ,
4209 creation_date ,
4210 last_updated_by ,
4211 last_update_date ,
4212 last_update_login
4213 )
4214 values
4215 (
4216 jai_ap_tds_thhold_trxs_s.nextval ,
4217 p_invoice_id ,
4218 p_tds_event ,
4219 p_tax_id ,
4220 p_tax_rate ,
4221 p_taxable_amount ,
4222 p_tax_amount ,
4223 p_tds_authority_vendor_id ,
4224 p_tds_authority_vendor_site_id ,
4225 p_invoice_tds_authority_num ,
4226 p_invoice_tds_authority_type ,
4227 p_invoice_tds_authority_curr ,
4228 p_invoice_tds_authority_amt ,
4229 p_invoice_tds_authority_id ,
4230 p_vendor_id ,
4231 p_vendor_site_id ,
4232 p_invoice_vendor_num ,
4233 p_invoice_vendor_type ,
4234 p_invoice_vendor_curr ,
4235 p_invoice_vendor_amt ,
4236 p_invoice_vendor_id ,
4237 p_parent_inv_payment_priority ,
4238 p_parent_inv_exchange_rate ,
4239 fnd_global.user_id ,
4240 sysdate ,
4241 fnd_global.user_id ,
4242 sysdate ,
4243 fnd_global.login_id
4244 );
4245
4246 fnd_file.put_line(FND_FILE.LOG, ' Done Insert -> insert_tds_thhold_trxs ');
4247 end insert_tds_thhold_trxs;
4248
4249 /* ******************************** create_tds_after_holds_rel ******************************************* */
4250
4251 -- Bug#5131075(4685754). Added by Lakshmi Gopalsami
4252 -- Added for holds release
4253
4254 Procedure create_tds_after_holds_release
4255 (
4256 errbuf out nocopy varchar2,
4257 retcode out nocopy varchar2,
4258 p_invoice_id IN number,
4259 p_invoice_amount IN number,
4260 p_payment_status_flag IN varchar2,
4261 p_invoice_type_lookup_code IN varchar2,
4262 p_vendor_id IN number,
4263 p_vendor_site_id IN number,
4264 p_accounting_date IN DATE,
4265 p_invoice_currency_code IN varchar2,
4266 p_exchange_rate IN number,
4267 p_set_of_books_id IN number,
4268 p_org_id IN number,
4269 p_call_from IN varchar2,
4270 p_process_flag IN varchar2,
4271 p_process_message IN varchar2,
4272 p_codepath IN varchar2,
4273 p_request_id IN number default null-- added, Harshita for Bug#5131075(5346558)
4274 ) IS
4275
4276 lv_is_invoice_validated varchar2(1);
4277 lv_invoice_validation_status varchar2(25);
4278
4279 lv_process_flag varchar2(1);
4280 lv_process_message varchar2(200);
4281 lv_codepath varchar2(2000);
4282
4283 --Start addition by sanjikum for Bug#5131075(4722011)
4284 lv_new_transaction_si VARCHAR2(1);
4285 lv_new_transaction_pp VARCHAR2(1);
4286 lv_prepay_flag VARCHAR2(1);
4287
4288 -- added, Harshita for Bug#5131075(5346558)
4289 ln_req_status BOOLEAN ;
4290 lv_phase VARCHAR2(80) ;
4291 lv_status VARCHAR2(80) ;
4292 lv_dev_phase VARCHAR2(80) ;
4293 lv_dev_status VARCHAR2(80) ;
4294 lv_message VARCHAR2(80) ;
4295
4296 CURSOR c_check_prepayment_apply(p_invoice_distribution_id NUMBER)
4297 IS
4298 SELECT '1'
4299 FROM jai_ap_tds_prepayments
4300 WHERE invoice_distribution_id_prepay = p_invoice_distribution_id;
4301
4302 CURSOR c_check_prepayment_unapply(p_invoice_distribution_id_pp NUMBER)
4303 IS
4304 SELECT '1'
4305 FROM jai_ap_tds_prepayments
4306 WHERE invoice_distribution_id_prepay = p_invoice_distribution_id_pp
4307 AND unapply_flag = 'Y';
4308 --End addition by sanjikum for Bug#5131075(4722011)
4309
4310 /*START, Added by Bgowrava for Bug#9214036*/
4311 CURSOR c_get_rnd_factor (p_org_id IN NUMBER, p_inv_date in date ) IS
4312 SELECT nvl(tds_rounding_factor,0), tds_rounding_start_date
4313 FROM jai_ap_tds_years
4314 WHERE legal_entity_id = p_org_id
4315 AND trunc (p_inv_date) between start_date and end_date ;
4316 /*END, Added by Bgowrava for Bug#9214036*/
4317
4318 lv_debug char(1) :='N'; -- Harshita, changed debug to 'N' for 5367640
4319
4320 -- Bug 5722028. Added by Lakshmi Gopalsami
4321 cursor get_creation_date is
4322 select creation_date
4323 from ap_invoices_all
4324 where invoice_id = p_invoice_id;
4325 ld_creation_date DATE;
4326 -- End for bug 5722028.
4327
4328 Begin
4329
4330 lv_codepath := p_codepath;
4331
4332 /*START, Added by Bgowrava for Bug#9214036*/
4333 OPEN c_get_rnd_factor (p_org_id,p_accounting_date);
4334 FETCH c_get_rnd_factor into JAI_AP_TDS_GENERATION_pkg.gn_tds_rounding_factor, JAI_AP_TDS_GENERATION_pkg.gd_tds_rounding_effective_date;
4335 CLOSE c_get_rnd_factor ;
4336 /*END, Added by Bgowrava for Bug#9214036*/
4337
4338 -- Harshita for Bug#5131075(5346558)
4339 BEGIN
4340 IF p_request_id is not null THEN
4341 ln_req_status := fnd_concurrent.wait_for_request
4342 (request_id => p_request_id,
4343 interval => 1,
4344 max_wait => 0,
4345 phase => lv_phase,
4346 status => lv_status,
4347 dev_phase => lv_dev_phase,
4348 dev_status => lv_dev_status,
4349 message => lv_message) ;
4350
4354 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 );
4351 IF not ln_req_status THEN
4352 FND_FILE.put_line(FND_FILE.log, 'Phase : ' || lv_phase || 'Status : ' || lv_status || 'Dev Phase : ' || lv_dev_phase ||
4353 ' Dev Status : ' || lv_dev_status || ' Message : ' || lv_message );
4355 END IF ;
4356
4357 END IF ;
4358
4359 EXCEPTION
4360 WHEN OTHERS THEN
4361 FND_FILE.put_line(FND_FILE.log, 'Phase : ' || lv_phase || 'Status : ' || lv_status || 'Dev Phase : ' || lv_dev_phase ||
4362 ' Dev Status : ' || lv_dev_status || ' Message : ' || lv_message );
4363 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 );
4364 END;
4365
4366
4367 lv_invoice_validation_status :=
4368 AP_INVOICES_UTILITY_PKG.get_approval_status(
4369 l_invoice_id => p_invoice_id,
4370 l_invoice_amount => p_invoice_amount,
4371 l_payment_status_flag => p_payment_status_flag,
4372 l_invoice_type_lookup_code => p_invoice_type_lookup_code);
4373
4374 if lv_invoice_validation_status not in ('APPROVED', 'AVAILABLE', 'UNPAID') then
4375 lv_is_invoice_validated := 'N';
4376 Else
4377 lv_is_invoice_validated := 'Y';
4378 end if;
4379
4380 if lv_debug='Y' then
4381 fnd_file.put_line(FND_FILE.LOG, ' value of validate'||lv_is_invoice_validated);
4382 end if ;
4383
4384 if lv_is_invoice_validated = 'Y' then
4385
4386 /*START, Added by Bgowrava for Bug#9214036*/
4387 OPEN get_creation_date;
4388 FETCH get_creation_date INTO ld_creation_date;
4389 CLOSE get_creation_date;
4390 /*END, Added by Bgowrava for Bug#9214036*/
4391
4392 jai_ap_tds_generation_pkg.process_tds_at_inv_validate
4393 (
4394 p_invoice_id => p_invoice_id,
4395 p_vendor_id => p_vendor_id,
4396 p_vendor_site_id => p_vendor_site_id,
4397 p_accounting_date => p_accounting_date,
4398 p_invoice_currency_code => p_invoice_currency_code,
4399 p_exchange_rate => p_exchange_rate,
4400 p_set_of_books_id => p_set_of_books_id,
4401 p_org_id => p_org_id,
4402 p_call_from => p_call_from,
4403 p_creation_date => ld_creation_date, -- Bug 5722028. Added by csahoo
4404 p_process_flag => lv_process_flag,
4405 p_process_message => lv_process_message,
4406 p_codepath => lv_codepath
4407 );
4408
4409 --Moved this from below to here by Sanjikum for Bug#5131075(4722011)
4410 if nvl(lv_process_flag, 'N') = 'E' then
4411 fnd_file.put_line(FND_FILE.LOG, ' Error in the concurrent program '|| lv_process_message);
4412 goto exit_from_procedure;
4413 END IF;
4414
4415 --Start Addition by Sanjikum for Bug#5131075(4722011)
4416 FOR i IN(SELECT a.invoice_id,
4417 a.amount,
4418 a.invoice_distribution_id,
4419 a.parent_reversal_id,
4420 a.prepay_distribution_id,
4421 a.accounting_date,
4422 a.org_id,
4423 a.last_updated_by,
4424 a.last_update_date,
4425 a.created_by,
4426 a.creation_date,
4427 b.vendor_id,
4428 b.vendor_site_id,
4429 b.invoice_currency_code,
4430 b.exchange_rate,
4431 b.set_of_books_id
4432 FROM ap_invoice_distributions_all a,
4433 ap_invoices_all b
4434 WHERE a.invoice_id = b.invoice_id
4435 AND b.invoice_id = p_invoice_id
4436 AND a.line_type_lookup_code = 'PREPAY'
4437 AND b.source <> 'TDS'
4438 AND b.cancelled_date is null
4439 AND invoice_type_lookup_code NOT IN ('CREDIT', 'DEBIT'))
4440 LOOP
4441
4442 lv_prepay_flag := NULL;
4443
4444 --Apply Scenario
4445 IF NVL(i.amount,0) < 0 THEN
4446
4447 OPEN c_check_prepayment_apply(i.invoice_distribution_id);
4448 FETCH c_check_prepayment_apply INTO lv_prepay_flag;
4449 CLOSE c_check_prepayment_apply;
4450
4451 --Unapply Scenario
4452 ELSIF NVL(i.amount,0) > 0 THEN
4453
4454 OPEN c_check_prepayment_unapply(i.parent_reversal_id);
4455 FETCH c_check_prepayment_unapply INTO lv_prepay_flag;
4456 CLOSE c_check_prepayment_unapply;
4457
4458 END IF;
4459
4460 --should be run, only if prepayment application/unapplication is not already processed
4461 IF lv_prepay_flag IS NULL THEN
4462
4463
4464 jai_ap_tds_tax_defaultation.check_old_transaction
4465 (
4466 p_invoice_id => i.invoice_id,
4467 p_new_transaction => lv_new_transaction_si
4468 );
4469
4470 --Check for Pprepayment
4471 jai_ap_tds_tax_defaultation.check_old_transaction
4472 (
4473 p_invoice_distribution_id => i.prepay_distribution_id,
4474 p_new_transaction => lv_new_transaction_pp
4475 );
4476
4477 if lv_new_transaction_si = 'Y' and lv_new_transaction_pp = 'Y' then
4478
4479 lv_codepath := null;
4480
4481 jai_ap_tds_prepayments_pkg.process_prepayment
4482 (
4483 p_event => 'INSERT', --Added for Bug 8431516
4484 p_invoice_id => i.invoice_id,
4485 p_invoice_distribution_id => i.invoice_distribution_id,
4486 p_prepay_distribution_id => i.prepay_distribution_id,
4487 p_parent_reversal_id => i.parent_reversal_id,
4488 p_prepay_amount => i.amount,
4489 p_vendor_id => i.vendor_id,
4490 p_vendor_site_id => i.vendor_site_id,
4491 p_accounting_date => i.accounting_date,
4492 p_invoice_currency_code => i.invoice_currency_code,
4493 p_exchange_rate => i.exchange_rate,
4494 p_set_of_books_id => i.set_of_books_id,
4495 p_org_id => i.org_id,
4496 p_creation_date => i.creation_date, -- Bug 5722028
4497 p_process_flag => lv_process_flag,
4498 p_process_message => lv_process_message,
4499 p_codepath => lv_codepath
4500 );
4501
4502 if nvl(lv_process_flag, 'N') = 'E' then
4503 raise_application_error(-20007,
4504 'Error - procedure jai_ap_tds_generation_pkg.create_tds_after_holds_release : ' || lv_process_message);
4505 end if;
4506
4507 else
4508 --Invoke the old regime functionality
4509 jai_ap_tds_prepayments_pkg.process_old_transaction
4510 (
4511 p_invoice_id => i.invoice_id,
4512 p_invoice_distribution_id => i.invoice_distribution_id,
4513 p_prepay_distribution_id => i.prepay_distribution_id,
4514 p_amount => i.amount,
4515 p_last_updated_by => i.last_updated_by,
4516 p_last_update_date => i.last_update_date,
4517 p_created_by => i.created_by,
4518 p_creation_date => i.creation_date,
4519 p_org_id => i.org_id,
4520 p_process_flag => lv_process_flag,
4521 p_process_message => lv_process_message
4522 );
4523
4524 if nvl(lv_process_flag, 'N') = 'E' then
4525 raise_application_error(-20008,
4526 'Error - procedure jai_ap_tds_generation_pkg.create_tds_after_holds_release : ' || lv_process_message);
4527 end if;
4528 end if; --Transactions in new regime
4529
4530 END IF;
4531
4532 END LOOP;
4533
4534 << exit_from_procedure >>
4535
4536 NULL;
4537
4538 --End Addition by Sanjikum for Bug#5131075(4722011)
4539
4540 Else
4541 fnd_file.put_line(FND_FILE.LOG,' Not generating the TDS invoice
4542 as the parent invoice is not yet validated');
4543 retcode := 1;
4544 End if; /* lv_is_invoice_validated = 'Y' */
4545
4546 End create_tds_after_holds_release;
4547 -- End for bug#5131075(4685754)
4548
4549 /* ******************************** create_tds_after_holds_rel ******************************************* */
4550
4551 --new procedure created by sanjikum for bug#5131075(4718907)
4552 --This procedure gives the current threshold slab
4553 PROCEDURE get_tds_threshold_slab( p_prepay_distribution_id IN NUMBER,
4554 p_threshold_grp_id IN OUT NOCOPY NUMBER,
4555 p_threshold_hdr_id IN OUT NOCOPY NUMBER,
4556 p_threshold_slab_id OUT NOCOPY NUMBER,
4557 p_threshold_type OUT NOCOPY VARCHAR2,
4558 p_process_flag OUT NOCOPY VARCHAR2,
4559 p_process_message OUT NOCOPY VARCHAR2,
4560 p_codepath IN OUT NOCOPY VARCHAR2)
4561 IS
4562 CURSOR c_get_threshold_grp_id(p_prepay_distribution_id NUMBER)
4563 IS
4564 SELECT threshold_grp_id
4565 FROM jai_ap_tds_inv_taxes
4566 WHERE invoice_distribution_id = p_prepay_distribution_id
4567 AND section_type = 'TDS_SECTION';
4568
4569 CURSOR c_get_threshold_grp_dtl(p_threshold_grp_id NUMBER)
4570 IS
4571 SELECT *
4572 FROM jai_ap_tds_thhold_grps
4573 WHERE threshold_grp_id = p_threshold_grp_id;
4574
4575 CURSOR c_get_threshold_hdr(p_vendor_id NUMBER,
4576 p_org_tan_num VARCHAR2,
4577 p_pan_num VARCHAR2,
4578 p_section_type VARCHAR2,
4579 p_section_code VARCHAR2)
4580 IS
4581 SELECT threshold_hdr_id
4582 FROM jai_ap_tds_th_vsite_v
4583 WHERE vendor_id = p_vendor_id
4584 AND tan_no = p_org_tan_num
4585 AND pan_no = p_pan_num
4586 AND section_type = p_section_type
4587 AND section_code = p_section_code;
4588
4589 CURSOR c_jai_ap_tds_thhold_slabs( p_threshold_hdr_id NUMBER,
4590 p_threshold_type VARCHAR2,
4591 p_amount NUMBER)
4592 IS
4593 SELECT threshold_slab_id, threshold_type_id, from_amount, to_amount, tax_rate
4594 FROM jai_ap_tds_thhold_slabs
4595 WHERE threshold_hdr_id = p_threshold_hdr_id
4596 AND threshold_type_id in
4597 ( SELECT threshold_type_id
4598 FROM jai_ap_tds_thhold_types
4599 WHERE threshold_hdr_id = p_threshold_hdr_id
4600 AND threshold_type = p_threshold_type
4601 AND trunc(sysdate) between from_date and nvl(to_date, sysdate + 1)
4602 )
4603 AND from_amount <= p_amount
4604 AND NVL(to_amount, p_amount) >= p_amount
4605 ORDER BY from_amount asc;
4606
4607 r_get_threshold_grp_dtl c_get_threshold_grp_dtl%ROWTYPE;
4608 ln_effective_invoice_amt NUMBER;
4609 r_jai_ap_tds_thhold_slabs c_jai_ap_tds_thhold_slabs%ROWTYPE;
4610 lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
4611
4612 BEGIN
4613 IF p_threshold_grp_id IS NULL THEN
4614 OPEN c_get_threshold_grp_id(p_prepay_distribution_id);
4615 FETCH c_get_threshold_grp_id INTO p_threshold_grp_id;
4616 CLOSE c_get_threshold_grp_id;
4617 END IF;
4618
4619 OPEN c_get_threshold_grp_dtl(p_threshold_grp_id);
4620 FETCH c_get_threshold_grp_dtl INTO r_get_threshold_grp_dtl;
4621 CLOSE c_get_threshold_grp_dtl;
4622
4623 IF p_threshold_hdr_id IS NULL THEN
4624 OPEN c_get_threshold_hdr(r_get_threshold_grp_dtl.vendor_id,
4625 r_get_threshold_grp_dtl.org_tan_num,
4626 r_get_threshold_grp_dtl.vendor_pan_num,
4627 r_get_threshold_grp_dtl.section_type,
4628 r_get_threshold_grp_dtl.section_code);
4629 FETCH c_get_threshold_hdr INTO p_threshold_hdr_id;
4630 CLOSE c_get_threshold_hdr;
4631 END IF;
4632
4633 ln_effective_invoice_amt := r_get_threshold_grp_dtl.total_invoice_amount -
4634 r_get_threshold_grp_dtl.total_invoice_cancel_amount -
4635 r_get_threshold_grp_dtl.total_invoice_apply_amount +
4636 r_get_threshold_grp_dtl.total_invoice_unapply_amount;
4637
4638 lv_threshold_type := 'CUMULATIVE';
4639
4640 --check if the current amount falls in the cumulative threshold
4641 OPEN c_jai_ap_tds_thhold_slabs(p_threshold_hdr_id,
4642 lv_threshold_type,
4643 ln_effective_invoice_amt);
4644 FETCH c_jai_ap_tds_thhold_slabs INTO r_jai_ap_tds_thhold_slabs;
4645 CLOSE c_jai_ap_tds_thhold_slabs;
4646
4647 IF r_jai_ap_tds_thhold_slabs.threshold_slab_id IS NULL THEN
4648
4649 lv_threshold_type := 'SINGLE';
4650
4651 --check if the current amount falls in the single threshold
4652 OPEN c_jai_ap_tds_thhold_slabs(p_threshold_hdr_id,
4653 lv_threshold_type,
4654 99999999999999);
4655 FETCH c_jai_ap_tds_thhold_slabs INTO r_jai_ap_tds_thhold_slabs;
4656 CLOSE c_jai_ap_tds_thhold_slabs;
4657 END IF;
4658
4659 p_threshold_slab_id := r_jai_ap_tds_thhold_slabs.threshold_slab_id;
4660 p_threshold_type := lv_threshold_type;
4661
4662 EXCEPTION
4663 WHEN OTHERS THEN
4664 p_process_flag := 'E';
4665 p_process_message := SUBSTR(SQLERRM,1,200);
4666 END get_tds_threshold_slab;
4667
4668
4669 --new procedure created by sanjikum for bug#5131075(4718907)
4670 --This procedure takes as input the old and new threshold and checks if any type of Threshold Rollback processing is required
4671
4672 PROCEDURE process_threshold_rollback( p_invoice_id IN VARCHAR2,
4673 p_before_threshold_type IN VARCHAR2,
4674 p_after_threshold_type IN VARCHAR2,
4675 p_before_threshold_slab_id IN NUMBER,
4676 p_after_threshold_slab_id IN NUMBER,
4677 p_threshold_grp_id IN NUMBER,
4678 p_org_id IN NUMBER,
4679 p_accounting_date IN DATE,
4680 p_invoice_distribution_id IN NUMBER DEFAULT NULL,
4681 p_prepay_distribution_id IN NUMBER DEFAULT NULL,
4682 p_process_flag OUT NOCOPY VARCHAR2,
4683 p_process_message OUT NOCOPY VARCHAR2,
4684 p_codepath IN OUT NOCOPY VARCHAR2)
4685 IS
4686
4687 CURSOR c_threshold_slab(p_threshold_slab_id NUMBER,
4688 p_org_id NUMBER)
4689 IS
4690 SELECT b.tax_rate,
4691 b.from_amount,
4692 a.tax_id
4693 FROM jai_ap_tds_thhold_taxes a,
4694 jai_ap_tds_thhold_slabs b
4695 WHERE a.threshold_slab_id = b.threshold_slab_id
4696 AND a.operating_unit_id = p_org_id
4697 AND b.threshold_slab_id = p_threshold_slab_id;
4698
4699 CURSOR c_threshold_grp(p_threshold_grp_id NUMBER)
4700 IS
4701 SELECT *
4702 FROM jai_ap_tds_thhold_grps
4703 WHERE threshold_grp_id = p_threshold_grp_id;
4704
4705 CURSOR c_taxable_amount(c_threshold_grp_id NUMBER,
4706 c_single_threshold_amt NUMBER)
4707 IS
4708 SELECT NVL(SUM(a.taxable_amount),0) taxable_amount
4709 FROM jai_ap_tds_thhold_trxs a
4710 WHERE a.threshold_grp_id = c_threshold_grp_id
4711 AND a.tds_event = 'INVOICE VALIDATE'
4712 AND a.taxable_amount >= c_single_threshold_amt
4713 AND NOT EXISTS (SELECT '1'
4714 FROM jai_ap_tds_inv_cancels b
4715 WHERE a.invoice_id = b.invoice_id);
4716
4717 CURSOR c_prepayments(c_threshold_grp_id NUMBER)
4718 IS
4719 SELECT *
4720 FROM jai_ap_tds_prepayments
4721 WHERE tds_threshold_grp_id = c_threshold_grp_id
4722 AND NVL(unapply_flag,'N') <> 'Y';
4723
4724 CURSOR c_thhold_trxs(p_invoice_distribution_id NUMBER,
4725 p_single_threshold_amt NUMBER)
4726 IS
4727 SELECT 'Y'
4728 FROM jai_ap_tds_thhold_trxs a,
4729 jai_ap_tds_inv_taxes b
4730 WHERE a.invoice_id = b.invoice_id
4731 AND b.invoice_distribution_id = p_invoice_distribution_id
4732 AND a.tds_event = 'INVOICE VALIDATE'
4733 AND a.taxable_amount >= p_single_threshold_amt;
4734
4735 r_threshold_slab c_threshold_slab%ROWTYPE;
4736 r_before_threshold_slab c_threshold_slab%ROWTYPE;
4737 ln_effective_invoice_amt NUMBER;
4738 ln_effective_tds_amt NUMBER;
4739 ln_diff_tds_amount NUMBER;
4740 r_threshold_grp c_threshold_grp%ROWTYPE;
4741 v_si_flag VARCHAR2(1);
4742 v_pp_flag VARCHAR2(1);
4743 lv_tds_event jai_ap_tds_thhold_trxs.tds_event%TYPE;
4744 lv_tds_invoice_num ap_invoices_all.invoice_num%type;
4745 lv_tds_cm_num ap_invoices_all.invoice_num%type;
4746 ln_threshold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%TYPE;
4747 ln_threshold_grp_audit_id jai_ap_tds_thgrp_audits.threshold_grp_audit_id%TYPE;
4748 ln_threshold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
4749
4750 /* Bug 5751783.
4751 * Get the sum of invoice amount for which TDS is not calculated
4752 */
4753 CURSOR get_tds_not_deducted ( cp_threshold_grp_id IN NUMBER )
4754 IS
4755 SELECT SUM (NVL (jatit.amount , 0 ) )
4756 FROM jai_ap_tds_inv_taxes jatit
4757 WHERE jatit.threshold_grp_id = cp_threshold_grp_id
4758 AND match_status_flag = 'A'
4759 AND jatit.process_status = 'P'
4760 AND jatit.tax_amount IS NOT NULL
4761 AND jatit.threshold_trx_id IS NULL
4762 AND jatit.threshold_slab_id = 0
4763 AND ( jatit.actual_tax_id IS NOT NULL OR
4764 ( jatit.actual_taX_id IS NULL
4765 AND jatit.default_tax_id IS NOT NULL
4766 )
4767 )
4768 AND EXISTS /* check whether iinvoice is not cancelled*/
4769 ( SELECT invoice_id
4770 FROM ap_invoices_all ai
4771 WHERE ai.invoice_id = jatit.invoice_id
4772 AND ai.cancelled_Date IS NULL
4773 AND ai.cancelled_amount IS NULL
4774 );
4775
4776 /* Get the prepayment amount which is applied and RTN invoice
4777 * is not yet generated.
4778 */
4779 CURSOR get_ppau_tds_not_deducted (cp_threshold_grp_id IN NUMBER )
4780 IS
4781 SELECT SUM (NVL (jatp.application_amount, 0 ))
4782 FROM jai_ap_tds_prepayments jatp
4783 WHERE jatp.tds_threshold_grp_id = cp_threshold_grp_id
4784 AND jatp.tds_applicable_flag = 'Y'
4785 AND jatp.tds_threshold_trx_id_apply IS NULL
4786 AND jatp.unapply_flag IS NULL OR jatp.unapply_flag = 'N' ;
4787
4788 ln_tds_not_deducted NUMBER ;
4789 ln_pp_tds_not_deducted NUMBER ;
4790 ln_thhold_trxn_trsn NUMBER ;
4791 ln_taxable_amount NUMBER ;
4792
4793 /*Bug 5751783 - End*/
4794 CURSOR get_thhold_transn (cp_threshold_grp_id IN NUMBER )
4795 IS
4796 SELECT SUM(NVL(jattt.taxable_amount,0))
4797 FROM jai_ap_tds_thhold_trxs jattt
4798 WHERE jattt.threshold_grp_id = cp_threshold_grp_id
4799 AND ( jattt.tds_event like 'THRESHOLD TRANSITION%' OR
4800 -- Bug 5722028. Added by csahoo
4801 -- added the following condition
4802 jattt.tds_event like 'THRESHOLD ROLLBACK%'
4803 );
4804
4805 -- bug 5722028. Added by csahoo
4806 ln_taxable_thhold_change NUMBER;
4807
4808 FUNCTION get_pp_threshold(p_invoice_distribution_id IN NUMBER,
4809 p_single_threshold_amt IN NUMBER)
4810 RETURN VARCHAR2
4811 IS
4812 PRAGMA AUTONOMOUS_TRANSACTION;
4813
4814 CURSOR cur_thhold_trxs IS
4815 SELECT 'Y'
4816 FROM jai_ap_tds_thhold_trxs a
4817 WHERE a.tds_event = 'INVOICE VALIDATE'
4818 AND a.taxable_amount >= p_single_threshold_amt
4819 AND a.invoice_id IN (SELECT invoice_id
4820 FROM ap_invoice_distributions_all
4821 WHERE invoice_distribution_id = (SELECT prepay_distribution_id
4822 FROM ap_invoice_distributions_all
4823 WHERE invoice_distribution_id = p_invoice_distribution_id));
4824 lv_pp_flag VARCHAR2(1);
4825
4826 BEGIN
4827 OPEN cur_thhold_trxs;
4828 FETCH cur_thhold_trxs INTO lv_pp_flag;
4829 CLOSE cur_thhold_trxs;
4830
4831 RETURN lv_pp_flag;
4832
4833 END get_pp_threshold;
4834
4835 BEGIN
4836
4837 /*
4838 This functionality is required only if Threshold changes from any of the cumulative threshold slabs
4839 to either Single or any other cumulative threshold slab
4840 We need to check, only if the earlier threshold type is cumulative
4841 if the earlier type is single, it can't change to cumulative
4842 */
4843
4844 IF p_before_threshold_type = 'CUMULATIVE' THEN
4845
4846 --There is no change in the threshold slab. Means it is still in the same cumulative slab
4847 IF p_before_threshold_slab_id = p_after_threshold_slab_id THEN
4848
4849 NULL; --Nothing is required to be done, as there is no slab change
4850
4851 --There is a change in the slab. New slab is either cumulative or single
4852 ELSE
4853
4854 OPEN c_threshold_grp(p_threshold_grp_id);
4855 FETCH c_threshold_grp INTO r_threshold_grp;
4856 CLOSE c_threshold_grp;
4857
4858 OPEN c_threshold_slab(p_threshold_slab_id => p_after_threshold_slab_id,
4859 p_org_id => p_org_id);
4860 FETCH c_threshold_slab INTO r_threshold_slab;
4861 CLOSE c_threshold_slab;
4862
4863 --This is required, if there is no setup for the current threshold
4864 OPEN c_threshold_slab(p_threshold_slab_id => p_before_threshold_slab_id,
4865 p_org_id => p_org_id);
4866 FETCH c_threshold_slab INTO r_before_threshold_slab;
4867 CLOSE c_threshold_slab;
4868
4869 --If the new threshold type/slab is cumulative
4870 IF p_after_threshold_type = 'CUMULATIVE' THEN
4871
4872 ln_effective_invoice_amt := r_threshold_grp.total_invoice_amount -
4873 r_threshold_grp.total_invoice_cancel_amount -
4874 r_threshold_grp.total_invoice_apply_amount +
4875 r_threshold_grp.total_invoice_unapply_amount;
4876
4877 --If the new threshold type/slab is single
4878 ELSE
4879
4880 --If there is no single threshold setup done
4881 IF p_after_threshold_slab_id IS NULL THEN
4882 ln_effective_invoice_amt := 0;
4883 ELSE
4884
4885 --Calculate the TDS, based on the single threshold and pass the entry for the TDS amount
4886
4887 --Get all the invoice validations, where invoice amount is > single threshold amount
4888 OPEN c_taxable_amount(c_threshold_grp_id => p_threshold_grp_id,
4889 c_single_threshold_amt => r_threshold_slab.from_amount);
4890 FETCH c_taxable_amount INTO ln_effective_invoice_amt;
4891 CLOSE c_taxable_amount;
4892
4893 /* Bug 5722028. Added by Lakshmi Gopalsami
4894 * We need to fetch the sum of taxable as part of threshold
4895 * transition or rollback as this would have been populated
4896 * with the amount of invoice on which TDS is not deducted.
4897 */
4898
4899 /*Start commenting by mmurtuza for bug 13561970*/
4900
4901 /* Bug 13561970 - Threshold Transition amount need not be subtracted
4902 to calculate the amount for which Threshold Rollback needs to be generated
4903 */
4904
4905 /*OPEN get_thhold_transn( cp_threshold_grp_id => p_threshold_grp_id );
4906 FETCH get_thhold_transn INTO ln_taxable_thhold_change;
4907 CLOSE get_thhold_transn;
4908
4909 ln_effective_invoice_amt := ln_effective_invoice_amt + nvl(ln_taxable_thhold_change,0);*/
4910 /*End commenting by mmurtuza for bug 13561970*/
4911
4912 --If there are any invoices more than Single threshold, only then need to progress
4913 IF ln_effective_invoice_amt > 0 THEN
4914
4915 --Get all the prepayments applied in the current threshold group
4916 FOR i IN c_prepayments(p_threshold_grp_id) LOOP
4917
4918 v_si_flag := NULL;
4919
4920 --For SI. Check if the invoice amount of SI is more than Single threshold
4921 OPEN c_thhold_trxs(i.invoice_distribution_id,
4922 r_threshold_slab.from_amount);
4923 FETCH c_thhold_trxs INTO v_si_flag;
4924 CLOSE c_thhold_trxs;
4925
4926 v_pp_flag := NULL;
4927
4928 --For PP. Check if the invoice amount of SI is more than Single threshold
4929 --If the current transaction is PP application. As in the else part the autonomous function is
4930 --being used, which wouldn't be able to see the current transaction...means the PP application
4931 IF p_invoice_distribution_id = i.invoice_distribution_id_prepay THEN
4932
4933 OPEN c_thhold_trxs(p_prepay_distribution_id,
4934 r_threshold_slab.from_amount);
4935 FETCH c_thhold_trxs INTO v_pp_flag;
4936 CLOSE c_thhold_trxs;
4937 ELSE
4938 --Here the autonomous function is used, as it is required to select from ap_invoice_distributions table.
4939 --If this function is not used, this shall give the mutating error
4940 v_pp_flag := get_pp_threshold(i.invoice_distribution_id_prepay, r_threshold_slab.from_amount);
4941 END IF;
4942
4943 --If both the SI and PP have invoice amount > Single threshold, then adjustment amount need to be calculated
4944 IF NVL(v_si_flag,'N') = 'Y' AND NVL(v_pp_flag,'N') = 'Y' THEN
4945 ln_effective_invoice_amt := ln_effective_invoice_amt - NVL(i.application_amount,0);
4946 END IF;
4947
4948 END LOOP; --c_prepayments
4949
4950 END IF; --ln_effective_invoice_amt > 0
4951
4952 /* Bug 5751783
4953 * Get the taxable basis for threshold rollback.
4954 * Calculate the taxable amount.
4955 */
4956
4957 fnd_file.put_line(FND_FILE.LOG,' inside rollback ');
4958 OPEN get_tds_not_deducted ( p_threshold_grp_id );
4959 FETCH get_tds_not_deducted INTO ln_tds_not_deducted;
4960 CLOSE get_tds_not_deducted;
4961 -- TDS not deducted for Prepayment application
4962 OPEN get_ppau_tds_not_deducted(p_threshold_grp_id);
4963 FETCH get_ppau_tds_not_deducted INTO ln_pp_tds_not_deducted;
4964 CLOSE get_ppau_tds_not_deducted;
4965 -- Get the transitioned taxable amount
4966
4967 /*Start commenting by mmurtuza for bug 13561970*/
4968 /*
4969 Bug 13561970 - Threshold Transition amount need not be subtracted
4970 to calculate the amount for which Threshold Rollback needs to be generated.
4971 */
4972
4973 /*OPEN get_thhold_transn(p_threshold_grp_id);
4974 FETCH get_thhold_transn INTO ln_thhold_trxn_trsn;
4975 CLOSE get_thhold_transn;*/
4976
4977 /*End commenting by mmurtuza for bug 13561970*/
4978
4979 fnd_file.put_line(FND_FILE.LOG,'SI not deducted ' || ln_tds_not_deducted);
4980 fnd_file.put_line(FND_FILE.LOG,'PPA/U not deducted ' || ln_pp_tds_not_deducted);
4981 fnd_file.put_line(FND_FILE.LOG,'Transitioned taxable amount ' || ln_thhold_trxn_trsn);
4982 ln_taxable_amount := NVL(ln_tds_not_deducted,0) -
4983 NVL(ln_pp_tds_not_deducted,0);
4984 /* -NVL(ln_thhold_trxn_trsn,0);*/ /*Bug 13561970*/
4985 fnd_file.put_line(FND_FILE.LOG,'Remaining taxable amount' || ln_taxable_amount);
4986
4987
4988 END IF;
4989
4990 END IF;
4991
4992 IF NVL(ln_effective_invoice_amt,0) = 0 THEN
4993 ln_effective_tds_amt := 0;
4994 ELSE
4995 /*Bug 5751783. Removed the rounding as this will be used for reverse calculation of invoice amount*/
4996 ln_effective_tds_amt := ROUND(ln_effective_invoice_amt * (r_threshold_slab.tax_rate/100), g_inr_currency_rounding);
4997 END IF;
4998
4999 IF trunc(sysdate) >=
5000 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
5001 ln_effective_tds_amt := get_rnded_value(ln_effective_tds_amt);
5002 END IF;
5003
5004 /*Bug 5721614. Used the non-rounded value for calculation*/
5005 ln_diff_tds_amount := r_threshold_grp.total_tax_paid - ln_effective_tds_amt;
5006
5007
5008 IF ln_diff_tds_amount > 0 THEN
5009
5010 --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
5011
5012 lv_tds_event := 'THRESHOLD ROLLBACK( from slab id - '||p_before_threshold_slab_id||' to slab id - '||p_after_threshold_slab_id||')';
5013
5014 jai_ap_tds_generation_pkg.generate_tds_invoices
5015 (
5016 pn_invoice_id => p_invoice_id ,
5017 /* Bug 5751783. Changed null to calculated value for taxable amount.
5018 * This value is not rounded as we are performing one more rounding in
5019 * procedure generate_Tds_invoices.
5020 * Removed (ln_diff_tds_amount/ r_threshold_slab.tax_rate ) * 100
5021 * and added ln_taxable_amount
5022 */
5023 pn_taxable_amount => NVL(ln_taxable_amount,0),
5024 --No taxable amount in case of threshold rollback invoice
5025 pn_tax_amount => ln_diff_tds_amount ,
5026 pn_tax_id => NVL(r_threshold_slab.tax_id, r_before_threshold_slab.tax_id) ,
5027 pd_accounting_date => p_accounting_date ,
5028 pv_tds_event => lv_tds_event ,
5029 pn_threshold_grp_id => p_threshold_grp_id ,
5030 pv_tds_invoice_num => lv_tds_invoice_num ,
5031 pv_cm_invoice_num => lv_tds_cm_num ,
5032 pn_threshold_trx_id => ln_threshold_trx_id ,
5033 pd_creation_date => sysdate, -- Bug 5722028. Added by csahoo
5034 pn_calc_tax_amount => 0, /* Added for bug 12965614 */
5035 p_process_flag => p_process_flag ,
5036 p_process_message => p_process_message
5037 );
5038
5039 if p_process_flag = 'E' then
5040 goto exit_from_procedure;
5041 end if;
5042
5043 IF ln_threshold_trx_id IS NOT NULL THEN
5044 jai_ap_tds_generation_pkg.import_and_approve
5045 (
5046 p_invoice_id => p_invoice_id,
5047 p_start_thhold_trx_id => ln_threshold_trx_id,
5048 p_tds_event => lv_tds_event,
5049 p_process_flag => p_process_flag,
5050 p_process_message => p_process_message
5051 );
5052 END IF;
5053
5054 --Update the total tax amount for which invoice was raised
5055 ln_threshold_grp_id := p_threshold_grp_id;
5056
5057 maintain_thhold_grps
5058 (
5059 p_threshold_grp_id => ln_threshold_grp_id,
5060 p_trx_tax_paid => ln_diff_tds_amount*-1, --Multiplied by -1, as this should reduce the total tax amount
5061 p_trx_thhold_change_tax_paid => ln_diff_tds_amount*-1,
5062 p_trx_threshold_slab_id => p_after_threshold_slab_id,
5063 p_tds_event => lv_tds_event,
5064 p_invoice_id => p_invoice_id,
5065 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
5066 -- Bug 5722028. Added by Lakshmi Gopalsami
5067 p_creation_date => sysdate,
5068 p_process_flag => p_process_flag,
5069 P_process_message => P_process_message,
5070 p_codepath => p_codepath
5071 );
5072
5073 IF p_process_flag = 'E' THEN
5074 goto exit_from_procedure;
5075 END IF;
5076
5077 END IF;
5078
5079 END IF;
5080
5081 END IF;
5082
5083 <<exit_from_procedure>>
5084
5085 NULL;
5086
5087 EXCEPTION
5088 WHEN OTHERS THEN
5089 p_process_flag := 'E';
5090 p_process_message := SUBSTR(SQLERRM,1,200);
5091 END process_threshold_rollback;
5092
5093 -- Bug 5722028. Added by csahoo
5094 FUNCTION get_rnded_value (p_tax_amount in number)
5095 RETURN NUMBER AS
5096 ln_tmp_tax_amt number ;
5097 ln_tds_mod_value number ;
5098 ln_tds_sign number;
5099 BEGIN
5100 ln_tds_sign := sign(p_tax_amount);
5101 ln_tmp_tax_amt := abs(p_tax_amount);
5102
5103 IF jai_ap_tds_generation_pkg.gn_tds_rounding_factor = -1 then
5104 ln_tds_mod_value := 0;
5105 ln_tds_mod_value := MOD(ROUND(ln_tmp_tax_amt,
5106 g_inr_currency_rounding),10);
5107 IF ln_tds_mod_value >= 5 THEN
5108 ln_tmp_tax_amt := ln_tmp_tax_amt + (10-ln_tds_mod_value);
5109 ELSE -- < 5
5110 ln_tmp_tax_amt := ln_tmp_tax_amt - ln_tds_mod_value;
5111 END IF ;
5112 END IF ; -- jai_ap_tds_generation_pkg.gn_tds_rounding_factor = -1
5113 return (ln_tmp_tax_amt* ln_tds_sign );
5114 END get_rnded_value;
5115 -- End for bug 5722028.
5116
5117
5118 END jai_ap_tds_generation_pkg;
[Home] [Help]