[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_TDS_PREPAYMENTS_PKG
Source
1 PACKAGE BODY jai_ap_tds_prepayments_pkg AS
2 /* $Header: jai_ap_tds_ppay.plb 120.20.12020000.7 2013/04/08 09:14:53 amandali ship $ */
3
4 /* ----------------------------------------------------------------------------
5 FILENAME : jai_ap_tds_prepayemnts_pkg_b.sql
6
7 Created By : Aparajita
8
9 Created Date : 03-mar-2005
10
11 Bug :
12
13 Purpose : Implementation of prepayment functionality for TDS.
14
15 Called from : Trigger ja_in_ap_aia_after_trg
16 Trigger ja_in_ap_aida_after_trg
17
18 CHANGE HISTORY:
19 -------------------------------------------------------------------------------
20 S.No Date Author and Details
21 -------------------------------------------------------------------------------
22 1. 03/03/2005 Aparajita for bug#4088186. version#115.0. TDS Clean Up.
23
24 Created this package for implementing the TDS prepayemnts
25 functionality onto AP invoice.
26
27 2. 08-Jun-2005 Version 116.1 jai_ap_tds_ppay -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
28 as required for CASE COMPLAINCE.
29
30 3. 14-Jun-2005 rchandan for bug#4428980, Version 116.2
31 Modified the object to remove literals from DML statements and CURSORS.
32 4. 28-Jul-2005 Bug 4522507. Added by Lakshmi Gopalsami version 120.2
33 - Made the changes in Procedure process_tds_invoices
34 a) Added 2 new cursors c_get_if_tds_inv_generated_pp,
35 c_get_amt_tds_inv_generated_si.
36 b) Code Added to open and Fetch the details from the
37 above 2 cursors
38 c) Changed the value of parameter pn_tax_amount,
39 while calling
40 jai_ap_tds_generation_pkg.generate_tds_invoices
41 d) Changed the value of parameter p_trx_tax_paid,
42 while calling
43 jai_ap_tds_generation_pkg.maintain_thhold_grps
44
45 Dependencies (Functional)
46 -------------------------
47 jai_ap_tds_gen.plb Version 120.3
48
49 6. 22-nov-2005 Bug 47541213. Added by Lakshmi Gopalsami
50 Changed JAI_TDS_SECTION to TDS_SECTION
51
52 7. 03/11/2006 Sanjikum for Bug#5131075, File Version 120.4
53 1) Changes are done for forward porting of bugs - 4722011, 4718907
54
55 Dependency Due to this Bug
56 --------------------------
57 Yes, as Package spec is changed and there are multiple files changed as part of current
58
59 8. 14/03/2007 Bug 5722028. Added by CSahoo 120.5
60 Forward Porting to R12
61 Added parameter p_creation_date for the follownig procedures
62 process_tds_at_inv_validate
63 maintain_thhold_grps
64 and pd_creation_date in generate_tds_invoices.
65 Added global variables
66 gn_tds_rounding_factor
67 gd_tds_rounding_effective_date and function get_rnded_value
68 is created.
69
70 updated jai_ap_tdS_inv_taxes and jai_ap_tds_thhold_grps
71 withe the rounded values. This is done in procedure
72 process_tds_at_inv_validate and maintain_thhold_grps.
73 In generate_tds_invoices derived the logic for rounding.
74 Added conditions in queries for fetching the taxable
75 amount in procedure process_threshold_transition and
76 process_threshold_rollback. Added the parameters p_creation_date
77 or pd_creation_date wherever required.
78 Search for bug number for complete fix.
79
80 Depedencies:
81 =============
82 jai_ap_tds_gen.pls - 120.5
83 jai_ap_tds_gen.plb - 120.19
84 jai_ap_tds_ppay.pls - 120.2
85 jai_ap_tds_ppay.plb - 120.5
86 jai_ap_tds_can.plb - 120.6
87
88
89 9. 6/01/2010 Added by Jia for FP bug6929483
90 Issue: This is a forward port bug for the bug6911776
91 Applying prepayment to an invoice takes longer time
92
93 Fixed: 1) Add new procedure get_prepay_invoice_id to get invoice_id
94 2) Modified the procedure populate_section_tax and procedure process_tds_invoices.
95 The queries referring to the table jai_ap_tds_inv_taxes,
96 included invoice_id in their where clause to make use of existing index
97
98 10. 07/01/2010 Added by Xiao Lv for FP bug#8345080
99 Issue: This is a forward port bug for the bug8333898
100 TDS DEDUCTING TWICE
101
102 Fixed: 1) Add new cursor c_get_grp_details_si_inv_dist, c_get_tax_sec_det.
103
104 11. 13-Jan-2010 Xiao for Bug#6596019
105 Commented the code related to creation of TDS invoices for RTN generation.
106 This is implemented because on application of prepayment the TDS calculated is
107 on the net amount of standard invoice and hence the RTN need not be created.
108
109 12. 14/01/2010 Added by Jia for FP Bug#7431371
110 Issue: This is a forward port bug for the 11i Bug#7419533
111 FINANCIALS FOR INDIA -TDS NOT WORKING IN CASE OF MULTIPLE DISTRIBUTIONS
112 Fixed: Commented the code in procdeure process_tds_invoices for calling the procedure maintain_thhold_grps.
113
114 13. 25-Jan-2010 Bug 5751783 (Forward Port of 5721614)
115 -------------------------------------
116 Issues
117 + Amount in certificates is wrong. All calculations are made based on rounded values
118 + Certificates are generated with Taxable Basis as 0 but non zero tax amount
119 + Certificates are generated with negative amounts.
120 + During Prepayment Un-application if Threshold Transition occurs then there are no TDS Invoices generated.
121 + Taxable Basis is wrong for Threshold Rollback.
122 + Applying Prepayment with different rates results in negative RTN
123
124 Bug 8679964 (Forward Port of 8639011)
125 -------------------------------------
126 When attempting to unapply prepayment error message pops up 'Cannot unapply the prepayment as it was applied
127 before validating the standard invoice' even though the prepayment was applied after validation of std invoice.
128
129 Bug 6363056 (Forward Port of 6031679)
130 -------------------------------------
131 When prepayment from the previous year is applied on to the Standard Invoice of the current year,
132 it results in 'Effective Tax Amount cannot be negative'.
133 This issue is fixed by Invoice ID of the latest document in jai_ap_tds_thhold_trxs when inserting records
134 for TDS Event 'PREPAYMENT APPLICATION'. Apart from this, the threshold group which belongs latest
135 GL Date in the Distribution is used.
136
137 Bug 6972230 (Forward Port of 6742977)
138 -------------------------------------
139 RTN not generated for the correct amount when Prepayment Tax Rate is different from the Standard Invoice
140 Tax Rate
141
142 Bug 6929483 (Forward Port of 6911776)
143 -------------------------------------
144 Pending fix which was dependent in 5751783 is done here
145
146 Bug 8431516 (Forward Port of 7626202)
147 -------------------------------------
148 RTN invoice would be generated to negate the effect of TDS invoice created for a prepayment, when the prepayment
149 is applied to a standard invoice.
150
151 14. 21-Jun-2010 Bug - 9826422
152 Description: Records are inserted into AP Interface tables using Standard Invoice, but import_and_approve
153 was called using the Prepayment Invoice ID. Hence wrong group_id was getting passed and no
154 Invoices were getting improved
155 Fix: Replaced p_invoice_id by ln_parent_invoice_id
156
157 15. 09-Jun-2011 Bug 12392890
158 Description:UNAPP OF PREPAYMENT SHOULD NOT BE ALLOWED ON VALIDATED STANDARD INVOICE AFTER AP
159 Fix:Modified the cursor c_get_prepay_apply
160 Changed the parameter from p_invoice_distribution_id to p_parent_reversal_id
161
162 16. 24-Jan-2012 mmurtuza for bug 13620923
163 Description: PREPAYMENT UNAPPLICATION HAS NOT HAPPENING WHERE INVOICE BEFORE BREACHING THRESHOLD are applied
164 Fix: Arcs'ed out the version 120.3.12000000.17 to revert the changes done for DTC ER.
165 Added cursor c_chk_tds_deducted to check if TDS is deducted for this bug
166
167 17. 15-Jun-2012 amandali for bug 13929793
168 Description:PREPAYMENT ALLOWED TO BE APPLIED ON INVOICE EVEN IF IT HAS NO TDS
169 Fix:In populate_section_tax procedure, added a condition to check the TDS sections on both the documents.
170
171 18. 23-Jul-12 amandali for bug 14183670
172 Description:RTN TDS invoice not generated when a prepayment is applied to a standard invoice
173 Fix: Added a new cursor c_applied_amount to calculate the applied amount to an invoice and modified the if condition for checking the application_mode
174
175 19. 08-Nov-2012 amandali for bug 14811018
176 Description:RTN not generated for standard invoice having a negative line without TDS
177 Fix:Added AND clause in cursor c_get_effective_available_amt in procedure allocate_prepayment to find the effective amount for the lines having TDS alone.
178
179 20. 20-Nov-2012 amandali for bug 15848719
180 Description:RTN NOT GENERATED FOR A SI HAVING A NEGATIVE LINE HAVING TDS
181 Fix:Made modifications to procedure allocate_prepayment
182
183 21. 09-Jan-2012 amandali for bug 14657086
184 Description:RTN generated for prepayment with no TDS applied to SI belonging to Single slab
185 Fix:Added tds applicability condition in c_get_prepay_apply and also modified c_get_tax_details_si_inv_dist and
186 added if condition in populate_Section_tax procedure
187 --------------------------------------------------------------------------- */
188
189 -- Added by Jia for FP bug6929483, Begin
190 -----------------------------------------------------------------------------
191 PROCEDURE get_prepay_invoice_id
192 (
193 p_prepay_inv_dist_id NUMBER,
194 p_prepay_inv_id OUT NOCOPY NUMBER
195 )
196 IS
197 PRAGMA AUTONOMOUS_TRANSACTION;
198 BEGIN
199 BEGIN
200 SELECT invoice_id
201 INTO p_prepay_inv_id
202 FROM ap_invoice_distributions_all
203 WHERE invoice_distribution_id = p_prepay_inv_dist_id;
204 EXCEPTION
205 WHEN NO_DATA_FOUND THEN
206 p_prepay_inv_id := null;
207 END;
208 END get_prepay_invoice_id;
209
210 /*Bug 8431516 - Start*/
211 FUNCTION get_reversal_flag(pn_invoice_dist_id NUMBER) RETURN VARCHAR2
212 IS
213 PRAGMA AUTONOMOUS_TRANSACTION;
214 CURSOR c_get_reversal_flag(p_inv_dist_id NUMBER) is
215 SELECT reversal_flag
216 FROM ap_invoice_distributions_all
217 WHERE invoice_distribution_id = p_inv_dist_id;
218 v_reversal_flag VARCHAR2(1);
219 BEGIN
220 OPEN c_get_reversal_flag(pn_invoice_dist_id);
221 FETCH c_get_reversal_flag INTO v_reversal_flag;
222 CLOSE c_get_reversal_flag;
223 v_reversal_flag := NVL(v_reversal_flag,'N');
224 RETURN v_reversal_flag;
225 END get_reversal_flag;
226 /*Bug 8431516 - End*/
227
228 -----------------------------------------------------------------------------
229 -- Added by Jia for FP bug6929483, End
230
231
232 procedure process_prepayment
233 (
234 p_event in varchar2, --Added for Bug 8431516
235 p_invoice_id in number,
236 p_invoice_distribution_id in number,
237 p_prepay_distribution_id in number,
238 p_parent_reversal_id in number,
239 p_prepay_amount in number,
240 p_vendor_id in number,
241 p_vendor_site_id in number,
242 p_accounting_date in date,
243 p_invoice_currency_code in varchar2,
244 p_exchange_rate in number,
245 p_set_of_books_id in number,
246 p_org_id in number,
247 -- Bug 5722028. Added by CSahoo
248 p_creation_date in date,
249 p_process_flag out nocopy varchar2,
250 p_process_message out nocopy varchar2,
251 p_codepath in out nocopy varchar2
252 )
253 is
254 /*Bug 5751783 - Start*/
255 cursor c_get_prepay_apply(cp_invoice_id number, cp_inv_dist_id number) is
256 select tds_threshold_trx_id_apply, count(1)
257 from jai_ap_tds_prepayments
258 where invoice_id = cp_invoice_id
259 and invoice_distribution_id_prepay = cp_inv_dist_id
260 and tds_applicable_flag='Y' /* Bug 14657086 */
261 group by tds_threshold_trx_id_apply;
262
263 /*Start Additions by mmurtuza for bug 13620923*/
264 cursor c_chk_tds_deducted(cp_invoice_id number) is
265 select count(1) from jai_ap_tds_thhold_trxs
266 where invoice_id = cp_invoice_id
267 and tds_event = 'INVOICE VALIDATE';
268
269 ln_cnt_thrshold number := 0;
270
271 /*End Additions by mmurtuza for bug 13620923*/
272
273 ln_prepay_apply number;
274 ln_prepay_apply_trx_id number;
275 /*Bug 5751783 - End*/
276 begin
277
278 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.process_prepayment', 'START'); /* 1 */
279 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', '============jai_ap_tds_prepayemnts_pkg.process_prepayment=======');
280 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', 'p_invoice_id'||p_invoice_id ||'p_invoice_distribution_id :'||p_invoice_distribution_id );
281 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','p_prepay_distribution_id: '||p_prepay_distribution_id ||'p_parent_reversal_id: '||p_parent_reversal_id);
282 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', 'p_prepay_amount: '||p_prepay_amount);
283 if p_prepay_amount < 0 then
284
285 /* Event is APPLY of prepayment */
286
287 jai_ap_tds_prepayments_pkg.allocate_prepayment
288 (
289 p_invoice_id => p_invoice_id ,
290 p_invoice_distribution_id => p_invoice_distribution_id ,
291 p_prepay_amount => p_prepay_amount ,
292 p_process_flag => p_process_flag ,
293 p_process_message => p_process_message ,
294 p_codepath => p_codepath
295 );
296
297 if p_process_flag = 'E' then
298 goto exit_from_procedure;
299 end if;
300
301 jai_ap_tds_prepayments_pkg.populate_section_tax
302 (
303 p_invoice_id => p_invoice_id ,
304 p_invoice_distribution_id => p_invoice_distribution_id ,
305 p_prepay_distribution_id => p_prepay_distribution_id ,
306 p_process_flag => p_process_flag ,
307 p_process_message => p_process_message ,
308 p_codepath => p_codepath
309 );
310
311 if p_process_flag = 'E' then
312 goto exit_from_procedure;
313 end if;
314
315
316 jai_ap_tds_prepayments_pkg.process_tds_invoices
317 (
318 p_event => p_event , --Added for Bug 8431516
319 p_invoice_id => p_invoice_id ,
320 p_invoice_distribution_id => p_invoice_distribution_id ,
321 p_prepay_distribution_id => p_prepay_distribution_id ,
322 p_prepay_amount => p_prepay_amount ,
323 p_vendor_id => p_vendor_id ,
324 p_vendor_site_id => p_vendor_site_id ,
325 p_accounting_date => p_accounting_date ,
326 p_invoice_currency_code => p_invoice_currency_code ,
327 p_exchange_rate => p_exchange_rate ,
328 p_set_of_books_id => p_set_of_books_id ,
329 p_org_id => p_org_id ,
330 -- Bug 5722028. Added by Lakshmi Gopalsami
331 p_creation_date => p_creation_date,
332 p_process_flag => p_process_flag ,
333 p_process_message => p_process_message ,
334 p_codepath => p_codepath
335 );
336
337 if p_process_flag = 'E' then
338 goto exit_from_procedure;
339 end if;
340
341
342 elsif p_prepay_amount > 0 then
343
344 /* Event is UNAPPLY of prepayment */
345 /* Bug 5721614. Added by Lakshmi Gopalsami
346 * Included parameter p_prepay_distribution_id
347 */
348 -- Bug 12392890. Modified the cursor c_get_prepay_apply
349 -- Changed from p_invoice_distribution_id to p_parent_reversal_id
350 open c_get_prepay_apply(p_invoice_id, p_parent_reversal_id);
351 fetch c_get_prepay_apply into ln_prepay_apply_trx_id, ln_prepay_apply;
352 close c_get_prepay_apply;
353
354 /*Start Additions by mmurtuza for bug 13620923*/
355 open c_chk_tds_deducted(p_invoice_id);
356 fetch c_chk_tds_deducted into ln_cnt_thrshold;
357 close c_chk_tds_deducted;
358 /*End Additions by mmurtuza for bug 13620923*/
359
360 if p_event = 'INSERT' and nvl(ln_prepay_apply,0) > 0 and nvl(ln_prepay_apply_trx_id, 0) = 0 and ln_cnt_thrshold <> 0 then
361 /*added condition of ln_cnt_thrshold by mmurtuza for bug 13620923*/
362 p_process_flag := 'E';
363 P_process_message := 'Error - Cannot Unapply prepayment as it was Applied before Validating the Standard invoice';
364 goto exit_from_procedure;
365 end if;
366
367
368 jai_ap_tds_prepayments_pkg.process_unapply
369 (
370 p_event => p_event , --Added for Bug 8431516
371 p_invoice_id => p_invoice_id ,
372 p_invoice_distribution_id => p_invoice_distribution_id ,
373 p_parent_distribution_id => p_parent_reversal_id ,
374 p_prepay_distribution_id => p_prepay_distribution_id , /*Bug 5751783*/
375 p_prepay_amount => p_prepay_amount ,
376 p_vendor_id => p_vendor_id ,
377 p_vendor_site_id => p_vendor_site_id ,
378 p_accounting_date => p_accounting_date ,
379 p_invoice_currency_code => p_invoice_currency_code ,
380 p_exchange_rate => p_exchange_rate ,
381 p_set_of_books_id => p_set_of_books_id ,
382 p_org_id => p_org_id ,
383 -- Bug 5722028. Added by CSahoo
384 p_creation_date => p_creation_date,
385 p_process_flag => p_process_flag ,
386 p_process_message => p_process_message ,
387 p_codepath => p_codepath
388 );
389
390 --Added by Sanjikum for Bug#5131075(4722011)
391 IF p_process_flag = 'E' THEN
392 goto exit_from_procedure;
393 END IF;
394
395 end if;
396
397
398 << exit_from_procedure >>
399 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
400 return;
401
402 exception
403 when others then
404 p_process_flag := 'E';
405 P_process_message := 'jai_ap_tds_prepayemnts_pkg.process_prepayment :' || sqlerrm;
406 return;
407 end process_prepayment;
408
409
410
411 /***********************************************************************************************/
412 procedure process_unapply
413 (
414 p_event in varchar2, --Added for Bug 8431516
415 p_invoice_id in number,
416 p_invoice_distribution_id in number, /* PREPAY UNAPPLY distribution */
417 p_parent_distribution_id in number, /* parent PREPAY APPLY distribution */
418 p_prepay_distribution_id in number, /* Distribution id of the prepay line - Bug 5751783*/
419 p_prepay_amount in number,
420 p_vendor_id in number,
421 p_vendor_site_id in number,
422 p_accounting_date in date,
423 p_invoice_currency_code in varchar2,
424 p_exchange_rate in number,
425 p_set_of_books_id in number,
426 p_org_id in number,
427 -- Bug 5722028. Added by CSahoo
428 p_creation_date in date,
429 p_process_flag out nocopy varchar2,
430 p_process_message out nocopy varchar2,
431 p_codepath in out nocopy varchar2
432 )
433 is
434
435 /* Bug 5751783
436 * Fetched the non-rounded value of the tds paid in order to avoid
437 * any rounding issues.
438 */
439 /* bug 12965614. Added by Avanija
440 * We should not convert the application amount back to functional currency as prepayment application is in
441 * invoice currency and prepayment unapplication should also be in same currency
442 * Removed the p_exchange_rate for taxable basis
443 */
444 cursor c_get_total_prepayment_tax
445 (p_invoice_id number, p_invoice_distribution_id number, p_exchange_rate number) is
446 select sum( decode(tds_applicable_flag , 'Y', application_amount, 0) ) tds_taxable_basis,
447 sum( decode(tds_applicable_flag , 'Y', calc_tds_appln_amt, 0) ) tds_amount,
448 sum( decode(tds_applicable_flag , 'Y', tds_application_amount, 0) ) tds_amount_orig,
449 sum( decode(wct_applicable_flag, 'Y', application_amount, 0) ) wct_taxable_basis,
450 sum( decode(wct_applicable_flag, 'Y', calc_wct_appln_amt, 0) ) wct_amount,
451 sum( decode(wct_applicable_flag , 'Y', wct_application_amount, 0) ) wct_amount_orig,
452 sum( decode(essi_applicable_flag, 'Y', application_amount, 0) ) essi_taxable_basis,
453 sum( decode(essi_applicable_flag, 'Y', calc_essi_appln_amt, 0) ) essi_amount,
454 sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount_orig
455 from jai_ap_tds_prepayments
456 where invoice_id = p_invoice_id
457 and invoice_distribution_id_prepay = p_invoice_distribution_id;
458
459 /* bug 12965614. Added by Avanija
460 * We should not convert the application amount back to functional currency as prepayment application is in
461 * invoice currency and prepayment unapplication should also be in same currency
462 * Removed the p_exchange_rate for taxable basis
463 */
464 cursor c_tds_details_apply(p_invoice_id number, p_invoice_distribution_id number, p_exchange_rate in number) is
465 select tds_threshold_grp_id,
466 tds_threshold_trx_id_apply,
467 -- Bug 6363056
468 sum(decode(tds_applicable_flag , 'Y', application_amount, 0)) tds_taxable_basis,
469 sum(decode(tds_applicable_flag , 'Y', calc_tds_appln_amt, 0)) tds_amount,
470 sum(decode(tds_applicable_flag , 'Y', tds_application_amount, 0)) tds_amount_orig,
471 sum(decode(wct_applicable_flag, 'Y', application_amount, 0)) wct_taxable_basis,
472 sum(decode(wct_applicable_flag, 'Y', calc_wct_appln_amt, 0)) wct_amount,
473 sum(decode(wct_applicable_flag , 'Y', wct_application_amount, 0)) wct_amount_orig,
474 sum(decode(essi_applicable_flag, 'Y', application_amount, 0)) essi_taxable_basis,
475 sum(decode(essi_applicable_flag, 'Y', calc_essi_appln_amt, 0)) essi_amount,
476 sum(decode(essi_applicable_flag , 'Y', essi_application_amount, 0)) essi_amount_orig
477 from jai_ap_tds_prepayments
478 where invoice_id = p_invoice_id
479 and invoice_distribution_id_prepay = p_invoice_distribution_id
480 and tds_threshold_grp_id is not null
481 and nvl(unapply_flag, 'N') <> 'Y' -- Bug 6363056
482 group by
483 tds_threshold_grp_id,
484 tds_threshold_trx_id_apply; /*Bug 9132694 - Added Group By clause to sum the tax amounts and create a single RTN reversal entry on unapplication*/
485
486 cursor c_wct_details_apply(p_invoice_id number, p_invoice_distribution_id number) is
487 select wct_threshold_trx_id_apply, invoice_distribution_id -- Bug 6363056
488 from jai_ap_tds_prepayments
489 where invoice_id = p_invoice_id
490 and invoice_distribution_id_prepay = p_invoice_distribution_id
491 and wct_threshold_trx_id_apply is not null;
492
493 cursor c_essi_details_apply(p_invoice_id number, p_invoice_distribution_id number) is
494 select essi_threshold_trx_id_apply, invoice_distribution_id -- Bug 6363056
495 from jai_ap_tds_prepayments
496 where invoice_id = p_invoice_id
497 and invoice_distribution_id_prepay = p_invoice_distribution_id
498 and essi_threshold_trx_id_apply is not null;
499
500 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
501 select currency_code
502 from gl_sets_of_books
503 where set_of_books_id = cp_set_of_books_id;
504
505 cursor c_get_tds_tax_id(p_invoice_id number, p_prepay_distribution_id number) is
506 select tds_tax_id_prepay
507 from jai_ap_tds_prepayments
508 where invoice_id = p_invoice_id
509 and invoice_distribution_id_prepay = p_prepay_distribution_id
510 and tds_tax_id_prepay is not null
511 and tds_applicable_flag = 'Y';
512
513 cursor c_get_wct_tax_id(p_invoice_id number, p_prepay_distribution_id number) is
514 select wct_tax_id_prepay
515 from jai_ap_tds_prepayments
516 where invoice_id = p_invoice_id
517 and invoice_distribution_id_prepay = p_prepay_distribution_id
518 and wct_tax_id_prepay is not null
519 and wct_applicable_flag = 'Y';
520
521 cursor c_get_essi_tax_id(p_invoice_id number, p_prepay_distribution_id number) is
522 select essi_tax_id_prepay
523 from jai_ap_tds_prepayments
524 where invoice_id = p_invoice_id
525 and invoice_distribution_id_prepay = p_prepay_distribution_id
526 and essi_tax_id_prepay is not null
527 and essi_applicable_flag = 'Y';
528
529 cursor c_get_invoice_num_of_apply(p_threshold_trx_id number) is
530 select invoice_to_tds_authority_num,
531 invoice_to_vendor_num,
532 /* Bug 5751783
533 * Pass the Prepayment application invoice_id for generating the
534 * prepayment unapplication
535 */
536 invoice_id,
537 tax_id
538 from jai_ap_tds_thhold_trxs
539 where threshold_trx_id = p_threshold_trx_id;
540
541
542 r_get_total_prepayment_tax c_get_total_prepayment_tax%rowtype;
543 r_tds_details_apply c_tds_details_apply%rowtype;
544 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
545
546 lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
547 lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
548 ln_threshold_trx_id_apply number;
549 ln_threshold_trx_id_tds number;
550 ln_threshold_trx_id_wct number;
551 ln_threshold_trx_id_essi number;
552 ln_start_threshold_trx_id number;
553 ln_exchange_rate number;
554 ln_tax_id number;
555 ln_threshold_grp_id number;
556 ln_threshold_grp_audit_id number;
557 lv_invoice_num_to_tds_apply ap_invoices_all.invoice_num%type;
558 lv_invoice_num_to_vendor_apply ap_invoices_all.invoice_num%type;
559 /*Bug 5751783 - Start*/
560 ln_parent_pp_invoice_id NUMBER ;
561 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
562 lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
563 ln_after_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
564 lv_after_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
565 ln_temp_threshold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
566 ln_temp_threshold_hdr_id jai_ap_tds_thhold_hdrs.threshold_hdr_id%TYPE;
567 lv_slab_transition_tds_event jai_ap_tds_thhold_trxs.tds_event%type;
568 lv_ppu_tds_inv_num ap_invoices_all.invoice_num%type;
569 lv_ppu_tds_cm_num ap_invoices_all.invoice_num%type;
570 /*Bug 5751783 - End*/
571 -- Bug 6031679. Added by Lakshmi Gopalsami
572 ln_inv_dist_id_apply ap_invoice_distributions_all.invoice_distribution_id%TYPE ;
573
574
575 begin
576 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.process_unapply', 'START'); /* 1 */
577
578 open c_gl_sets_of_books(p_set_of_books_id);
579 fetch c_gl_sets_of_books into r_gl_sets_of_books;
580 close c_gl_sets_of_books;
581
582 if r_gl_sets_of_books.currency_code <> p_invoice_currency_code then
583 /* Foreign currency invoice */
584 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
585 ln_exchange_rate := p_exchange_rate;
586 end if;
587
588 ln_exchange_rate := nvl(ln_exchange_rate, 1);
589
590 open c_get_total_prepayment_tax(p_invoice_id, p_parent_distribution_id, ln_exchange_rate);
591 fetch c_get_total_prepayment_tax into r_get_total_prepayment_tax;
592 close c_get_total_prepayment_tax;
593
594 /* Bug 5751783
595 * Call to procedure - get_tds_threshold_slab,
596 * Store the current Threshold slab and type
597 * before PP Unapplication
598 */
599 /* Unapply TDS */
600 if r_get_total_prepayment_tax.tds_amount > 0 then
601
602 OPEN c_tds_details_apply(p_invoice_id, p_parent_distribution_id, ln_exchange_rate);
603 LOOP
604 FETCH c_tds_details_apply INTO r_tds_details_apply;
605 EXIT WHEN c_tds_details_apply%NOTFOUND ;
606
607 ln_temp_threshold_grp_id := r_tds_details_apply.tds_threshold_grp_id;
608 jai_ap_tds_generation_pkg.get_tds_threshold_slab(
609 p_prepay_distribution_id => p_prepay_distribution_id,
610 p_threshold_grp_id => ln_temp_threshold_grp_id,
611 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
612 p_threshold_slab_id => ln_threshold_slab_id,
613 p_threshold_type => lv_threshold_type,
614 p_process_flag => p_process_flag,
615 p_process_message => p_process_message,
616 p_codepath => p_codepath);
617
618 IF p_process_flag = 'E' THEN
619 goto exit_from_procedure;
620 END IF;
621
622
623 ln_threshold_grp_id:= r_tds_details_apply.tds_threshold_grp_id;
624 jai_ap_tds_generation_pkg.maintain_thhold_grps
625 (
626 p_threshold_grp_id => ln_threshold_grp_id,
627 p_trx_invoice_unapply_amount => r_tds_details_apply.tds_taxable_basis,/*5751783*/
628 p_tds_event => 'PREPAYMENT UNAPPLICATION',
629 p_invoice_id => p_invoice_id,
630 p_invoice_distribution_id => p_invoice_distribution_id,
631 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
632 p_process_flag => p_process_flag,
633 P_process_message => p_process_message,
634 p_codepath => p_codepath
635 );
636
637 --Added by Sanjikum for Bug#5131075(4722011)
638 IF p_process_flag = 'E' THEN
639 goto exit_from_procedure;
640 END IF;
641
642
643 if r_tds_details_apply.tds_threshold_trx_id_apply is not null then
644
645 lv_invoice_to_tds_num := null;
646 lv_invoice_to_vendor_num := null;
647 ln_tax_id := null;
648
649 /* get the tds invoice numbers at apply */
650 /* Bug 5721614. Added by Lakshmi Gopalsami
651 * Fetched the invoice_id to be passed for generating the TDS invoice
652 * for prepayment unapplication.
653 */
654 open c_get_invoice_num_of_apply(r_tds_details_apply.tds_threshold_trx_id_apply);
655 fetch c_get_invoice_num_of_apply into
656 lv_invoice_num_to_tds_apply,
657 lv_invoice_num_to_vendor_apply,
658 ln_parent_pp_invoice_id,
659 ln_tax_id; -- bug 6031679
660 close c_get_invoice_num_of_apply ;
661
662 /* Bug 5751783
663 * Changed from p_invoice_id to ln_parent_pp_invoice_id ie,
664 * invoice_id of the prepayment application.
665 */
666 jai_ap_tds_generation_pkg.generate_tds_invoices
667 (
668 pn_invoice_id => ln_parent_pp_invoice_id ,
669 pn_invoice_distribution_id => p_invoice_distribution_id ,
670 pv_invoice_num_to_tds_apply => lv_invoice_num_to_tds_apply ,
671 pv_invoice_num_to_vendor_apply => lv_invoice_num_to_vendor_apply ,
672 pn_taxable_amount => r_tds_details_apply.tds_taxable_basis ,/*5751783*/
673 pn_tax_amount => r_tds_details_apply.tds_amount_orig ,/*5751783*/
674 pn_tax_id => ln_tax_id ,
675 pd_accounting_date => p_accounting_date ,
676 pv_tds_event => 'PREPAYMENT UNAPPLICATION' ,
677 pn_threshold_grp_id => r_tds_details_apply.tds_threshold_grp_id ,
678 pv_tds_invoice_num => lv_invoice_to_tds_num ,
679 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
680 pn_threshold_trx_id => ln_threshold_trx_id_tds ,
681 pd_creation_date => p_creation_date, -- Bug 5722028. Added by CSahoo
682 pn_calc_tax_amount => r_tds_details_apply.tds_amount, /*Added for bug 12965614 */
683 p_process_flag => p_process_flag ,
684 p_process_message => p_process_message
685 );
686
687 if p_process_flag = 'E' then
688 goto exit_from_procedure;
689 end if;
690
691 /* prepayment apply scenario for backward compatibility*/
692 update JAI_AP_TDS_INVOICES
693 set amt_reversed = nvl(amt_reversed, 0) - r_get_total_prepayment_tax.tds_amount,
694 amt_applied = nvl(amt_applied, 0) - abs(p_prepay_amount)
695 where invoice_id = p_invoice_id;
696 /* prepayment apply scenario for backward compatibility*/
697
698 /* Update the threshold group */
699
700 ln_threshold_grp_id:= r_tds_details_apply.tds_threshold_grp_id;
701 if p_event = 'INSERT' then /*Added for Bug 8431516*/
702 jai_ap_tds_generation_pkg.maintain_thhold_grps
703 (
704 p_threshold_grp_id => ln_threshold_grp_id,
705 p_trx_tax_paid => r_get_total_prepayment_tax.tds_amount,
706 p_tds_event => 'PREPAYMENT UNAPPLICATION',
707 p_invoice_id => p_invoice_id,
708 p_invoice_distribution_id => p_invoice_distribution_id,
709 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
710 p_process_flag => p_process_flag,
711 P_process_message => p_process_message,
712 p_codepath => p_codepath
713 );
714
715 --Added by Sanjikum for Bug#5131075(4722011)
716 IF p_process_flag = 'E' THEN
717 goto exit_from_procedure;
718 END IF;
719 END IF; /*if p_event = 'INSERT' then*/
720
721 update jai_ap_tds_prepayments
722 set tds_threshold_trx_id_unapply = ln_threshold_trx_id_tds
723 where invoice_id = p_invoice_id
724 and invoice_distribution_id_prepay = p_parent_distribution_id
725 and tds_threshold_trx_id_apply is not null
726 and tds_applicable_flag = 'Y';
727
728 if ln_start_threshold_trx_id is null then
729 ln_start_threshold_trx_id := ln_threshold_trx_id_tds;
730 end if;
731
732 end if; /* r_tds_details_apply.tds_threshold_trx_id_apply is not null */
733
734 /* update the unapply flag for invoice distribution */
735 update jai_ap_tds_prepayments
736 set unapply_flag = 'Y'
737 where invoice_id = p_invoice_id
738 and invoice_distribution_id_prepay = p_parent_distribution_id;
739
740 /*Bug 9132694 - Only one Unapplication entry would be created in jai_ap_tds_thhold_trxs for one unapplication*/
741
742 /* Bug 5751783
743 * Call to procedure - get_tds_threshold_slab,
744 * Store the current Threshold slab and type
745 * After PP Unapplication
746 */
747
748 jai_ap_tds_generation_pkg.get_tds_threshold_slab(
749 p_prepay_distribution_id => p_prepay_distribution_id,
750 p_threshold_grp_id => ln_temp_threshold_grp_id,
751 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
752 p_threshold_slab_id => ln_after_threshold_slab_id,
753 p_threshold_type => lv_after_threshold_type,
754 p_process_flag => p_process_flag,
755 p_process_message => p_process_message,
756 p_codepath => p_codepath);
757
758 IF p_process_flag = 'E' THEN
759 goto exit_from_procedure;
760 END IF;
761
762 IF ln_threshold_slab_id <> ln_after_threshold_slab_id THEN
763 lv_slab_transition_tds_event :=
764 'THRESHOLD TRANSITION-PPUA(from slab id -' || ln_threshold_slab_id ||
765 'to slab id - ' || ln_after_threshold_slab_id || ')';
766 jai_ap_tds_generation_pkg.process_threshold_transition
767 (
768 p_threshold_grp_id => ln_temp_threshold_grp_id,
769 p_threshold_slab_id => ln_after_threshold_slab_id,
770 p_invoice_id => ln_parent_pp_invoice_id,
771 p_vendor_id => p_vendor_id,
772 p_vendor_site_id => p_vendor_site_id,
773 p_accounting_date => p_accounting_date,
774 p_tds_event => lv_slab_transition_tds_event,
775 p_org_id => p_org_id,
776 pv_tds_invoice_num => lv_ppu_tds_inv_num,
777 pv_cm_invoice_num => lv_ppu_tds_cm_num,
778 p_process_flag => p_process_flag,
779 p_process_message => p_process_message
780 );
781
782 IF p_process_flag = 'E' THEN
783 goto exit_from_procedure;
784 END IF;
785 END IF ; /* ln_threshold_slab_id <> ln_after_threshold_slab_id */
786 ln_threshold_trx_id_tds := NULL ;
787 /*Bug 5751783*/
788 END LOOP ;
789 CLOSE c_tds_details_apply;
790
791
792 end if; /* r_get_total_prepayment_tax.tds_amount > 0*/
793 /* Unapply TDS */
794
795
796 /* Unapply WCT */
797 ln_threshold_trx_id_apply := null;
798 if r_get_total_prepayment_tax.wct_amount > 0 then
799
800 OPEN c_wct_details_apply(p_invoice_id, p_parent_distribution_id);
801 LOOP
802 FETCH c_wct_details_apply into ln_threshold_trx_id_apply, ln_inv_dist_id_apply ;
803 EXIT WHEN c_wct_details_apply%NOTFOUND ;
804
805 if ln_threshold_trx_id_apply is not null then
806
807 lv_invoice_to_tds_num := null;
808 lv_invoice_to_vendor_num := null;
809 ln_tax_id := null;
810
811 /* get the tds invoice numbers at apply */
812 /* Bug 5751783
813 * Fetched the invoice_id to be passed for generating the TDS invoice
814 * for prepayment unapplication.
815 */
816 open c_get_invoice_num_of_apply(ln_threshold_trx_id_apply);
817 fetch c_get_invoice_num_of_apply into
818 lv_invoice_num_to_tds_apply,
819 lv_invoice_num_to_vendor_apply,
820 ln_parent_pp_invoice_id,
821 ln_tax_id ;
822 close c_get_invoice_num_of_apply ;
823
824 /* Bug 5751783
825 * Changed from p_invoice_id to ln_parent_pp_invoice_id ie,
826 * invoice_id of the prepayment application.
827 */
828
829 jai_ap_tds_generation_pkg.generate_tds_invoices
830 (
831 pn_invoice_id => ln_parent_pp_invoice_id ,
832 pn_invoice_distribution_id => p_invoice_distribution_id ,
833 pv_invoice_num_to_tds_apply => lv_invoice_num_to_tds_apply ,
834 pv_invoice_num_to_vendor_apply => lv_invoice_num_to_vendor_apply ,
835 pn_taxable_amount => r_get_total_prepayment_tax.wct_taxable_basis ,
836 pn_tax_amount => r_get_total_prepayment_tax.wct_amount_orig ,
837 pn_tax_id => ln_tax_id ,
838 pd_accounting_date => p_accounting_date ,
839 pv_tds_event => 'PREPAYMENT UNAPPLICATION' ,
840 pn_threshold_grp_id => null ,
841 pv_tds_invoice_num => lv_invoice_to_tds_num ,
842 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
843 pn_threshold_trx_id => ln_threshold_trx_id_wct ,
844 pd_creation_date => p_creation_date, -- Bug 5722028. Added by csahoo
845 pn_calc_tax_amount => r_tds_details_apply.wct_amount, /*Added for bug 12965614 */
846 p_process_flag => p_process_flag ,
847 p_process_message => p_process_message
848 );
849
850 if p_process_flag = 'E' then
851 goto exit_from_procedure;
852 end if;
853
854 update jai_ap_tds_prepayments
855 set wct_threshold_trx_id_unapply = ln_threshold_trx_id_wct
856 where invoice_id = p_invoice_id
857 and invoice_distribution_id_prepay = p_parent_distribution_id
858 and wct_threshold_trx_id_apply is not null
859 and wct_applicable_flag = 'Y';
860
861 if ln_start_threshold_trx_id is null then
862 ln_start_threshold_trx_id := ln_threshold_trx_id_wct;
863 end if;
864
865 end if; /* ln_threshold_trx_id_apply.tds_threshold_trx_id_apply is not null */
866 ln_threshold_trx_id_apply := null;
867 ln_threshold_trx_id_wct := null;
868 END LOOP ;
869 CLOSE c_wct_details_apply;
870 end if;
871 /* Unapply WCT */
872
873 /* Unapply ESSI */
874 ln_threshold_trx_id_apply := null;
875 /*Bug 5751783. Changed to ESSI instead of wct_amount*/
876 if r_get_total_prepayment_tax.essi_amount > 0 then
877
878 OPEN c_essi_details_apply(p_invoice_id, p_parent_distribution_id);
879 LOOP
880 FETCH c_essi_details_apply into ln_threshold_trx_id_apply,ln_inv_dist_id_apply;
881 EXIT WHEN c_essi_details_apply%NOTFOUND ;
882
883 if ln_threshold_trx_id_apply is not null then
884
885 lv_invoice_to_tds_num := null;
886 lv_invoice_to_vendor_num := null;
887 ln_tax_id := null;
888
889 /* get the tds invoice numbers at apply */
890 /* Bug 5751783
891 * Fetched the invoice_id to be passed for generating the TDS invoice
892 * for prepayment unapplication.
893 */
894 open c_get_invoice_num_of_apply(ln_threshold_trx_id_apply);
895 fetch c_get_invoice_num_of_apply into
896 lv_invoice_num_to_tds_apply,
897 lv_invoice_num_to_vendor_apply,
898 ln_parent_pp_invoice_id,
899 ln_tax_id;
900 close c_get_invoice_num_of_apply ;
901
902 /* Bug 5721614. Added by Lakshmi Gopalsami
903 * Changed from p_invoice_id to ln_parent_pp_invoice_id ie,
904 * invoice_id of the prepayment application.
905 */
906 jai_ap_tds_generation_pkg.generate_tds_invoices
907 (
908 pn_invoice_id => ln_parent_pp_invoice_id ,
909 pn_invoice_distribution_id => p_invoice_distribution_id ,
910 pv_invoice_num_to_tds_apply => lv_invoice_num_to_tds_apply ,
911 pv_invoice_num_to_vendor_apply => lv_invoice_num_to_vendor_apply ,
912 pn_taxable_amount => r_get_total_prepayment_tax.essi_taxable_basis ,
913 pn_tax_amount => r_get_total_prepayment_tax.essi_amount_orig ,
914 pn_tax_id => ln_tax_id ,
915 pd_accounting_date => p_accounting_date ,
916 pv_tds_event => 'PREPAYMENT UNAPPLICATION' ,
917 pn_threshold_grp_id => null ,
918 pv_tds_invoice_num => lv_invoice_to_tds_num ,
919 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
920 pn_threshold_trx_id => ln_threshold_trx_id_essi ,
921 pd_creation_date => p_creation_date, -- Bug 5722028. Added by CSahoo
922 pn_calc_tax_amount => r_tds_details_apply.essi_amount, /*Added for bug 12965614 */
923 p_process_flag => p_process_flag ,
924 p_process_message => p_process_message
925 );
926
927 if p_process_flag = 'E' then
928 goto exit_from_procedure;
929 end if;
930
931 update jai_ap_tds_prepayments
932 set essi_threshold_trx_id_unapply = ln_threshold_trx_id_essi
933 where invoice_id = p_invoice_id
934 and invoice_distribution_id_prepay = p_parent_distribution_id
935 and essi_threshold_trx_id_apply is not null
936 and essi_applicable_flag = 'Y';
937
938
939 if ln_start_threshold_trx_id is null then
940 ln_start_threshold_trx_id := ln_threshold_trx_id_essi;
941 end if;
942
943 end if; /* ln_threshold_trx_id_apply.tds_threshold_trx_id_apply is not null */
944 ln_threshold_trx_id_apply := null;
945 ln_threshold_trx_id_essi := null;
946 END LOOP ;
947 CLOSE c_essi_details_apply;
948 end if;
949 /* Unapply ESSI */
950
951 /* update the unapply flag for all */
952 update jai_ap_tds_prepayments
953 set unapply_flag = 'Y'
954 where invoice_id = p_invoice_id
955 and invoice_distribution_id_prepay = p_parent_distribution_id;
956
957 /* prepayment apply scenario for backward compatibility*/
958 update JAI_AP_TDS_INVOICES
959 set amt_reversed = nvl(amt_reversed, 0) - r_get_total_prepayment_tax.tds_amount_orig,
960 amt_applied = nvl(amt_applied, 0) - abs(p_prepay_amount)
961 where invoice_id = p_invoice_id;
962
963 if ln_start_threshold_trx_id is not null then
964
965 jai_ap_tds_generation_pkg.import_and_approve
966 (
967 p_invoice_id => ln_parent_pp_invoice_id, /*Bug 5751783*/
968 p_start_thhold_trx_id => ln_start_threshold_trx_id,
969 p_tds_event => 'PREPAYMENT UNAPPLICATION',
970 p_process_flag => p_process_flag,
971 p_process_message => p_process_message
972 );
973
974 --Added by Sanjikum for Bug#5131075(4722011)
975 IF p_process_flag = 'E' THEN
976 goto exit_from_procedure;
977 END IF;
978
979 end if;
980
981 << exit_from_procedure >>
982 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
983 return;
984
985 exception
986 when others then
987 p_process_flag := 'E';
988 P_process_message := 'jai_ap_tds_prepayemnts_pkg.process_unapply :' || sqlerrm;
989 return;
990 end process_unapply;
991
992 /***********************************************************************************************/
993
994 procedure allocate_prepayment
995 (
996 p_invoice_id in number,
997 p_invoice_distribution_id in number, /* Of the PREPAY line */
998 p_prepay_amount in number,
999 p_process_flag out nocopy varchar2,
1000 p_process_message out nocopy varchar2,
1001 p_codepath in out nocopy varchar2
1002 )
1003 is
1004 /*Bug 9494469 - Removed parameter cp_section_type from c_jai_ap_tds_inv_taxes*/
1005 cursor c_jai_ap_tds_inv_taxes(p_invoice_id number, p_prepay_distribution_id number) is
1006 select invoice_distribution_id, amount, invoice_line_number, invoice_id
1007 from jai_ap_tds_inv_taxes
1008 where invoice_id = p_invoice_id
1009 and invoice_distribution_id <> p_prepay_distribution_id
1010 --and section_type = cp_section_type /*Commented for Bug 9494469*/
1011 and nvl(actual_tax_id, default_tax_id) is not null ;/*Bug 8431516*/
1012 --and amount > 0; --Added by bgowrava for bug#9214036 /* Commented for bug 15848719 */
1013
1014 cursor c_get_amount_already_applied(p_invoice_distribution_id number) is
1015 select sum(application_amount)
1016 from jai_ap_tds_prepayments
1017 where invoice_distribution_id = p_invoice_distribution_id
1018 and nvl(unapply_flag, 'N') <> 'Y';
1019
1020 /*START, Added by bgowrava for bug#9214036*/
1021 cursor c_get_effective_available_amt(p_invoice_id number, p_invoice_line_num number) is
1022 select sum(amount) amount
1023 from jai_ap_tds_inv_taxes
1024 where invoice_id = p_invoice_id
1025 and invoice_line_number = p_invoice_line_num
1026 and nvl(actual_tax_id, default_tax_id) is not null /* Added and condition for bug 14811018 */
1027 and amount < 0;
1028 /*END, Added by bgowrava for bug#9214036*/
1029
1030 ln_remaining_prepayment_amount number;
1031 ln_effective_available_amount number;
1032 ln_already_applied_amount number;
1033 ln_application_amount number;
1034 --ln_less_amount number; --Added by bgowrava for bug#9214036 /* Commented for bug 15848719 */
1035 lv_reversal_flag varchar2(1); /*Bug 8431516*/
1036
1037 begin
1038
1039 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.allocate_prepayment', 'START'); /* 1 */
1040 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', '=============jai_ap_tds_prepayemnts_pkg.allocate_prepayment=============');
1041 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', 'p_invoice_id:'||p_invoice_id );
1042 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', 'p_invoice_distribution_id:'||p_invoice_distribution_id );
1043 ln_remaining_prepayment_amount := abs(p_prepay_amount); /* Apply amount is negative */
1044 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', 'ln_remaining_prepayment_amount:'||ln_remaining_prepayment_amount );
1045 /* Loop through to get the taxable basis for each line in jai_ap_tds_inv_taxes */
1046 /* It is ok to loop through section_type = 'TDS_SECTION as considering any one section type
1047 is ok and tds section will always be there */
1048
1049 -- Bug 4754213. Added by Lakshmi Gopalsami
1050 for cur_si_distributions_rec in c_jai_ap_tds_inv_taxes(p_invoice_id, p_invoice_distribution_id) /*Bug 9494469 - Removed parameter cp_section_type*/
1051 loop
1052 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', 'cur_si_distributions_rec.invoice_distribution_id:'||cur_si_distributions_rec.invoice_distribution_id );
1053 lv_reversal_flag := get_reversal_flag(cur_si_distributions_rec.invoice_distribution_id); /*Bug 8431516*/
1054 if lv_reversal_flag = 'N' then /*Bug 8431516*/
1055
1056 ln_already_applied_amount:= 0;
1057 ln_effective_available_amount := 0;
1058 ln_application_amount := 0;
1059
1060 open c_get_amount_already_applied(cur_si_distributions_rec.invoice_distribution_id);
1061 fetch c_get_amount_already_applied into ln_already_applied_amount;
1062 close c_get_amount_already_applied;
1063
1064 ln_already_applied_amount := nvl(ln_already_applied_amount, 0);
1065 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', 'ln_already_applied_amount:'||ln_already_applied_amount );
1066 /*START, Added by bgowrava for bug#9214036*/
1067 /* open c_get_effective_available_amt(cur_si_distributions_rec.invoice_id, cur_si_distributions_rec.invoice_line_number);
1068 fetch c_get_effective_available_amt into ln_less_amount;
1069 close c_get_effective_available_amt;
1070 ln_less_amount := nvl(ln_less_amount, 0); */ /* Commented for bug 15848719 */
1071 /*END, Added by bgowrava for bug#9214036*/
1072 --jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', 'ln_less_amount:'||ln_less_amount );
1073 if cur_si_distributions_rec.amount>0 then /* Added for bug 15848719 */
1074 ln_effective_available_amount := cur_si_distributions_rec.amount - ln_already_applied_amount; /*- abs(ln_less_amount); - Commented for bug 15848719 */ --Added abs(ln_less_amount) by Bgowrava for Bug#9214036
1075 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', 'ln_effective_available_amount:'||ln_effective_available_amount );
1076 ln_application_amount := least(ln_remaining_prepayment_amount, ln_effective_available_amount);
1077 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', 'ln_application_amount:'||ln_application_amount );
1078 end if; /* Added for bug 15848719 */
1079 if ln_application_amount > 0 then
1080
1081 /* Insert into jai_ap_tds_prepayments */
1082 insert into jai_ap_tds_prepayments
1083 (
1084 tds_prepayment_id ,
1085 invoice_id ,
1086 invoice_distribution_id_prepay ,
1087 invoice_distribution_id ,
1088 application_amount ,
1089 created_by ,
1090 creation_date ,
1091 last_updated_by ,
1092 last_update_date ,
1093 last_update_login
1094 )
1095 values
1096 (
1097 jai_ap_tds_prepayments_s.nextval ,
1098 p_invoice_id ,
1099 p_invoice_distribution_id ,
1100 cur_si_distributions_rec.invoice_distribution_id ,
1101 ln_application_amount ,
1102 fnd_global.user_id ,
1103 sysdate ,
1104 fnd_global.user_id ,
1105 sysdate ,
1106 fnd_global.login_id
1107 );
1108
1109 end if;
1110
1111 ln_remaining_prepayment_amount := ln_remaining_prepayment_amount - ln_application_amount;
1112
1113 if ln_remaining_prepayment_amount <= 0 then
1114 goto exit_from_procedure;
1115 end if;
1116
1117 end if; /*if lv_reversal_flag = 'N' then*/
1118
1119 end loop; /* cur_si_distributions_rec in c_jai_ap_tds_inv_taxes */
1120
1121
1122 << exit_from_procedure >>
1123 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
1124 return;
1125
1126 exception
1127 when others then
1128 p_process_flag := 'E';
1129 P_process_message := 'jai_ap_tds_prepayemnts_pkg.allocate_prepayment :' || sqlerrm;
1130 return;
1131 end allocate_prepayment;
1132
1133 /***********************************************************************************************/
1134
1135 procedure populate_section_tax
1136 (
1137 p_invoice_id in number,
1138 p_invoice_distribution_id in number, /* Of the PREPAY line in the SI*/
1139 p_prepay_distribution_id in number, /*Distribution id of the PP invoice */
1140 p_process_flag out nocopy varchar2,
1141 p_process_message out nocopy varchar2,
1142 p_codepath in out nocopy varchar2
1143 )
1144 is
1145
1146 cursor c_get_tax_details_pp_inv_dist(p_pre_pay_inv_id number, p_prepay_distribution_id number) is -- Added parameter p_pre_pay_inv_id by Jia for FP bug6929483
1147 select section_type,
1148 nvl(actual_section_code, default_section_code) section_code, --Added NVL condition for Bug 8431516
1149 nvl(actual_tax_id, default_tax_id) tax_id --Added NVL condition for Bug 8431516
1150 from jai_ap_tds_inv_taxes
1151 where invoice_id = p_pre_pay_inv_id -- Added where clause p_pre_pay_inv_id by Jia for FP bug6929483
1152 and invoice_distribution_id = p_prepay_distribution_id
1153 and nvl(actual_tax_id, default_tax_id) is not null; --Added NVL condition for Bug 8431516
1154
1155 cursor c_get_tax_details_si_inv_dist(p_invoice_id number, p_invoice_distribution_id number) is
1156 select section_type,
1157 nvl(actual_section_code, default_section_code) section_code,
1158 nvl(actual_tax_id, default_tax_id) tax_id,
1159 threshold_slab_id_single /* Bug 14657086 */
1160 from jai_ap_tds_inv_taxes
1161 where invoice_id = p_invoice_id
1162 and invoice_distribution_id = p_invoice_distribution_id;
1163
1164
1165 cursor c_jai_ap_tds_prepayments(p_invoice_id number, p_invoice_distribution_id number) is
1166 select tds_prepayment_id,
1167 invoice_distribution_id
1168 from jai_ap_tds_prepayments
1169 where invoice_id = p_invoice_id
1170 and invoice_distribution_id_prepay = p_invoice_distribution_id;
1171
1172
1173
1174 cursor c_get_tds_application_basis(p_invoice_id number) is
1175 select 'N'
1176 from jai_ap_tds_inv_taxes
1177 where invoice_id = p_invoice_id
1178 and nvl(match_status_flag, 'N') <> 'A';
1179
1180 /* Bug 5751783 - Start*/
1181 /* added parameter p_pre_pay_inv_id to cursor for bug 6929483*/
1182 CURSOR get_threshold_trx_id (p_pre_pay_inv_id number, p_invoice_distribution_id IN NUMBER )
1183 IS
1184 SELECT threshold_trx_id
1185 FROM jai_ap_tds_inv_taxes
1186 WHERE invoice_id = p_pre_pay_inv_id
1187 AND invoice_distribution_id = p_invoice_distribution_id ;
1188
1189 lv_si_thhold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%TYPE;
1190 lv_pp_thhold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%TYPE;
1191 /* Bug 5751783 - End*/
1192
1193 lv_applicable_flag varchar2(1);
1194 lv_is_si_validated_flag varchar2(1);
1195
1196 lv_tds_section_code_prepay jai_ap_tds_prepayments.tds_section_code_prepay%type;
1197 ln_tds_tax_id_prepay jai_ap_tds_prepayments.tds_tax_id_prepay%type;
1198 ln_wct_tax_id_prepay jai_ap_tds_prepayments.wct_tax_id_prepay%type;
1199 ln_essi_tax_id_prepay jai_ap_tds_prepayments.essi_tax_id_prepay%type;
1200 lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1201
1202
1203 lv_tds_section_code_other jai_ap_tds_prepayments.tds_section_code_other%type;
1204 ln_tds_tax_id_other jai_ap_tds_prepayments.tds_tax_id_other%type;
1205 lv_tds_applicable_flag jai_ap_tds_prepayments.tds_applicable_flag%type;
1206 ln_wct_tax_id_other jai_ap_tds_prepayments.wct_tax_id_other%type;
1207 lv_wct_applicable_flag jai_ap_tds_prepayments.wct_applicable_flag%type;
1208 ln_essi_tax_id_other jai_ap_tds_prepayments.essi_tax_id_other%type;
1209 lv_essi_applicable_flag jai_ap_tds_prepayments.essi_applicable_flag%type;
1210
1211 pre_pay_inv_id ap_invoice_distributions_all.invoice_id%TYPE; -- Added by Jia for FP bug6929483
1212
1213
1214 begin
1215
1216 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.populate_section_tax', 'START'); /* 1 */
1217
1218 get_prepay_invoice_id(p_prepay_distribution_id,pre_pay_inv_id); -- Added by Jia for FP bug6929483
1219 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','==========jai_ap_tds_prepayemnts_pkg.populate_section_tax===========');
1220 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' pre_pay_inv_id: '||pre_pay_inv_id);
1221 /*
1222 open c_get_tds_application_basis(p_invoice_id);
1223 fetch c_get_tds_application_basis into lv_is_si_validated_flag;
1224 close c_get_tds_application_basis;
1225
1226 if nvl(lv_is_si_validated_flag, 'Y') = 'Y' then
1227 lv_application_basis := 'STANDARD INVOICE';
1228 else
1229 lv_application_basis := 'PREPAYMENT';
1230 end if;
1231 */
1232
1233 /* Bug 5751783
1234 * Commented the above logic as the above is obsoleted and the logic
1235 * for the deriving the basis is changed.
1236 * We should get the details of the invoice which is created latest in the
1237 * system. i.e., whichever is validated later in the system. We can get
1238 * these details by getting the value of threshold_trx_id from
1239 * jai_ap_tds_inv_taxes.
1240 */
1241
1242 -- Get the tds_threshold_trx_id of the prepay invoice.
1243 OPEN get_threshold_trx_id (pre_pay_inv_id,p_prepay_distribution_id );
1244 FETCH get_threshold_trx_id INTO lv_pp_thhold_trx_id ;
1245 CLOSE get_threshold_trx_id;
1246 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','lv_pp_thhold_trx_id: '||lv_pp_thhold_trx_id);
1247 -- Get the threshold_trx_id of the standard invoice.
1248 SELECT max(nvl(threshold_trx_id, 0))
1249 INTO lv_si_thhold_trx_id
1250 FROM jai_ap_tds_inv_taxes
1251 WHERE invoice_id = p_invoice_id ;
1252 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' lv_si_thhold_trx_id: '||lv_si_thhold_trx_id);
1253 IF (lv_si_thhold_trx_id > NVL (lv_pp_thhold_trx_id,0 )) THEN
1254 lv_application_basis := 'STANDARD INVOICE';
1255 ELSIF ( NVL (lv_pp_thhold_trx_id,0 ) <> 0 ) THEN
1256 lv_application_basis := 'PREPAYMENT';
1257 END IF ;
1258 /*Bug 5751783 - End*/
1259 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','lv_application_basis: '||lv_application_basis);
1260 /* Get the details of the taxes of all sections that was applicable on the distribution line as in the Prepayment */
1261 for cur_rec_pp_tax_details in c_get_tax_details_pp_inv_dist(pre_pay_inv_id,p_prepay_distribution_id) loop -- Added parameter pre_pay_inv_id by Jia for FP bug6929483
1262 -- Bug 4754213. Added by Lakshmi Gopalsami
1263 if cur_rec_pp_tax_details.section_type = 'TDS_SECTION' then
1264 lv_tds_section_code_prepay := cur_rec_pp_tax_details.section_code;
1265 ln_tds_tax_id_prepay := cur_rec_pp_tax_details.tax_id;
1266 elsif cur_rec_pp_tax_details.section_type = 'WCT_SECTION' then
1267 ln_wct_tax_id_prepay := cur_rec_pp_tax_details.tax_id;
1268 elsif cur_rec_pp_tax_details.section_type = 'ESSI_SECTION' then
1269 ln_essi_tax_id_prepay := cur_rec_pp_tax_details.tax_id;
1270 end if;
1271
1272 end loop; /* cur_rec_pp_tax_details */
1273
1274
1275 /* Loop and get all the distribution is that has been been allocated for this prepayment and
1276 get the tax details that is applicable on the allocated line */
1277 for cur_rec_pp_allocations in c_jai_ap_tds_prepayments(p_invoice_id, p_invoice_distribution_id) loop
1278
1279 for cur_rec in c_get_tax_details_si_inv_dist(p_invoice_id, cur_rec_pp_allocations.invoice_distribution_id) loop
1280 -- Bug 4754213. Added by Lakshmi Gopalsami
1281 if cur_rec.section_type = 'TDS_SECTION' then
1282 /* Start- Bug 13929793 --Validate the section codes of standard and prepayment invoices during application */
1283 if nvl(cur_rec.section_code, 'XYZ') <> nvl(lv_tds_section_code_prepay,'XYZ')
1284 and nvl(jai_populate_attribute.is_legacy_invoice(pre_pay_inv_id), 'N') = 'N' then
1285 /* Added above AND condition for bug 16606774 -
1286 This validation for legacy invoice is checked only if the Client Extension in jai_populate_attribute.is_legacy_invoice function enable_customization is set to Y */
1287 p_process_flag := 'E';
1288 P_process_message := 'Application cannot be done as the section code is different in both the documents';
1289 goto exit_from_procedure;
1290 end if;
1291 /* end- Bug 13929793 */
1292 lv_tds_section_code_other := cur_rec.section_code;
1293 ln_tds_tax_id_other := cur_rec.tax_id;
1294
1295 if lv_tds_section_code_other = lv_tds_section_code_prepay and
1296 lv_tds_section_code_other is not null and
1297 lv_tds_section_code_prepay is not null
1298 then
1299 /* Bug 14657086 -Added the below if condition. RTN should not be generated when a prepayment does not have TDS deducted
1300 and if the standard invoice belongs to a single slab */
1301 if nvl(lv_pp_thhold_trx_id,0 ) = 0 and cur_rec.threshold_slab_id_single is not null
1302 then lv_tds_applicable_flag := 'N';
1303 else
1304 lv_tds_applicable_flag := 'Y';
1305 end if;
1306 else
1307 lv_tds_applicable_flag := 'N';
1308 end if;
1309
1310 elsif cur_rec.section_type = 'WCT_SECTION' then
1311
1312 ln_wct_tax_id_other := cur_rec.tax_id;
1313
1314 if ln_wct_tax_id_prepay is not null and ln_wct_tax_id_other is not null then
1315 lv_wct_applicable_flag := 'Y';
1316 else
1317 lv_wct_applicable_flag := 'N';
1318 end if;
1319
1320 elsif cur_rec.section_type = 'ESSI_SECTION' then
1321
1322 ln_essi_tax_id_other := cur_rec.tax_id;
1323
1324 if ln_essi_tax_id_prepay is not null and ln_essi_tax_id_other is not null then
1325 lv_essi_applicable_flag := 'Y';
1326 else
1327 lv_essi_applicable_flag := 'N';
1328 end if;
1329
1330 end if; /* Section type of the SI distributions */
1331
1332 end loop; /* Cur rec */
1333
1334
1335 /* Update jai_ap_tds_prepayments */
1336 update jai_ap_tds_prepayments
1337 set application_basis = lv_application_basis ,
1338 tds_section_code_prepay = lv_tds_section_code_prepay ,
1339 tds_section_code_other = lv_tds_section_code_other ,
1340 tds_tax_id_prepay = ln_tds_tax_id_prepay ,
1341 tds_tax_id_other = ln_tds_tax_id_other ,
1342 tds_applicable_flag = lv_tds_applicable_flag ,
1343 wct_tax_id_prepay = ln_wct_tax_id_prepay ,
1344 wct_tax_id_other = ln_wct_tax_id_other ,
1345 wct_applicable_flag = lv_wct_applicable_flag ,
1346 essi_tax_id_prepay = ln_essi_tax_id_prepay ,
1347 essi_tax_id_other = ln_essi_tax_id_other ,
1348 essi_applicable_flag = lv_essi_applicable_flag
1349 where tds_prepayment_id = cur_rec_pp_allocations.tds_prepayment_id;
1350
1351
1352 end loop; /* cur_rec_pp_allocations */
1353
1354
1355 << exit_from_procedure >>
1356 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
1357 return;
1358
1359 exception
1360 when others then
1361 p_process_flag := 'E';
1362 P_process_message := 'jai_ap_tds_prepayemnts_pkg.populate_section_tax :' || sqlerrm;
1363 return;
1364 end populate_section_tax;
1365
1366 /***********************************************************************************************/
1367 procedure process_tds_invoices
1368 (
1369 p_event in varchar2, /*Bug 8431516*/
1370 p_invoice_id in number,
1371 p_invoice_distribution_id in number,
1372 p_prepay_distribution_id in number,
1373 p_prepay_amount in number,
1374 p_vendor_id in number,
1375 p_vendor_site_id in number,
1376 p_accounting_date in date,
1377 p_invoice_currency_code in varchar2,
1378 p_exchange_rate in number,
1379 p_set_of_books_id in number,
1380 p_org_id in number,
1381 -- Bug 5722028. Added by CSahoo
1382 p_creation_date in date,
1383 p_process_flag out nocopy varchar2,
1384 p_process_message out nocopy varchar2,
1385 p_codepath in out nocopy varchar2
1386 )
1387 is
1388
1389 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
1390 select currency_code
1391 from gl_sets_of_books
1392 where set_of_books_id = cp_set_of_books_id;
1393
1394 cursor c_jai_ap_tds_prepayments(p_invoice_id number, p_invoice_distribution_id number) is
1395 select tds_prepayment_id,
1396 application_amount,
1397 application_basis,
1398 /*
1399 decode(tds_applicable_flag, 'Y',
1400 decode(application_basis, 'STANDARD INVOICE', tds_tax_id_other, tds_tax_id_prepay),
1401 null) tds_tax_id,
1402 decode(wct_applicable_flag, 'Y',
1403 decode(application_basis, 'STANDARD INVOICE', wct_tax_id_other, wct_tax_id_prepay),
1404 null) wct_tax_id,
1405 decode(essi_applicable_flag, 'Y',
1406 decode(application_basis, 'STANDARD INVOICE', essi_tax_id_other, essi_tax_id_prepay),
1407 null) essi_tax_id
1408 */
1409 /* Bug 6363056. Commented the above
1410 * and added the following. Need to selected the lowest rate between
1411 * SI and PP
1412 */
1413 tds_applicable_flag, tds_tax_id_other, tds_tax_id_prepay,
1414 wct_applicable_flag, wct_tax_id_other, wct_tax_id_prepay,
1415 essi_applicable_flag, essi_tax_id_other, essi_tax_id_prepay
1416 from jai_ap_tds_prepayments
1417 where invoice_id = p_invoice_id
1418 and invoice_distribution_id_prepay = p_invoice_distribution_id;
1419
1420
1421 cursor c_ja_in_tax_codes(p_tax_id number) is
1422 select nvl(tax_rate, 0) tax_rate
1423 from JAI_CMN_TAXES_ALL
1424 where tax_id = p_tax_id;
1425
1426 --Add parameter p_pre_pay_inv_id in cursor c_get_prepayment_throup by Jia for FP bug6929483, Begin
1427 cursor c_get_prepayment_thgroup(p_pre_pay_inv_id number, p_prepay_distribution_id number,cp_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
1428 select threshold_grp_id,
1429 actual_tax_id,
1430 threshold_trx_id /*Bug 6363056*/
1431 from jai_ap_tds_inv_taxes
1432 where invoice_id = p_pre_pay_inv_id -- Added by Jia for FP bug6929483
1433 and invoice_distribution_id = p_prepay_distribution_id
1434 and section_type = cp_section_type; --rchandan for bug#4428980
1435
1436 cursor c_get_pp_section_tax_id(p_prepay_distribution_id number, p_section_type varchar2) is
1437 select actual_tax_id, invoice_id /*Bug 5751783*/
1438 from jai_ap_tds_inv_taxes
1439 where invoice_distribution_id = p_prepay_distribution_id
1440 and section_type = p_section_type;
1441
1442
1443 cursor c_jai_ap_tds_thhold_grps(p_threshold_grp_id number) is
1444 select nvl(current_threshold_slab_id, 0) current_threshold_slab_id
1445 from jai_ap_tds_thhold_grps
1446 where threshold_grp_id = p_threshold_grp_id;
1447
1448 cursor c_ap_invoices_all (p_invoice_distribution_id number) is
1449 select invoice_num, invoice_id /*Bug 5751783*/
1450 from ap_invoices_all
1451 where invoice_id in
1452 ( select invoice_id
1453 from jai_ap_tds_inv_taxes /* ap_invoice_distributions not used for mutation problem */
1454 where invoice_distribution_id = p_invoice_distribution_id);
1455
1456
1457 cursor c_get_total_prepayment_tax
1458 (p_invoice_id number, p_invoice_distribution_id number, p_exchange_rate number) is
1459 select sum( decode(tds_applicable_flag , 'Y', application_amount*p_exchange_rate, 0) ) tds_taxable_basis,
1460 sum( decode(tds_applicable_flag , 'Y', tds_application_amount, 0) ) tds_amount,
1461 sum( decode(wct_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) wct_taxable_basis,
1462 sum( decode(wct_applicable_flag, 'Y', wct_application_amount, 0) ) wct_amount,
1463 sum( decode(essi_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) essi_taxable_basis,
1464 sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount
1465 from jai_ap_tds_prepayments
1466 where invoice_id = p_invoice_id
1467 and invoice_distribution_id_prepay = p_invoice_distribution_id;
1468
1469 /* Bug 4522507. Added by Lakshmi Gopalsami */
1470
1471 cursor c_get_if_tds_inv_generated_pp(p_prepay_distribution_id number) is
1472 select threshold_trx_id
1473 from jai_ap_tds_inv_taxes
1474 where invoice_distribution_id = p_prepay_distribution_id
1475 -- Bug 4754213. Added by Lakshmi Gopalsami
1476 and section_type = 'TDS_SECTION';
1477
1478 /*Bug 6363056 - Replaced p_invoice_distribution_id with p_item_distribution_id*/
1479 cursor c_get_amt_tds_inv_generated_si(p_invoice_id number, p_item_distribution_id number) is
1480 select sum(calc_tds_appln_amt) , sum(application_amount)
1481 from jai_ap_tds_prepayments jatp
1482 where invoice_id = p_invoice_id
1483 and invoice_distribution_id_prepay = p_invoice_distribution_id
1484 /*Bug 6363056. Added invoice_distribution_id condition also*/
1485 and invoice_distribution_id = p_item_distribution_id
1486 and tds_applicable_flag = 'Y'
1487 and exists (select '1'
1488 from jai_ap_tds_inv_taxes
1489 where invoice_distribution_id = jatp.invoice_distribution_id
1490 -- Bug 4754213. Added by Lakshmi Gopalsami
1491 and section_type = 'TDS_SECTION'
1492 and threshold_trx_id is not null
1493 );
1494
1495 /*Bug 6363056 Start*/
1496 cursor c_si_ap_invoices_all (p_invoice_id number) is
1497 select invoice_num, invoice_id
1498 from ap_invoices_all
1499 where invoice_id = p_invoice_id;
1500
1501 CURSOR c_get_thgrp_det ( p_threshold_grp_id NUMBER ) IS
1502 SELECT *
1503 FROM jai_ap_tds_thhold_grps
1504 WHERE threshold_grp_id = p_threshold_grp_id;
1505 /*Bug 6363056 End*/
1506
1507
1508 --Added by Xiao Lv for Bug#8345080 on 7-Jan-10, begin
1509
1510 cursor c_get_grp_details_si_inv_dist(p_invoice_id number, p_invoice_distribution_id number)
1511 is
1512 select threshold_grp_id
1513 from jai_ap_tds_inv_taxes
1514 where invoice_id = p_invoice_id
1515 and invoice_distribution_id = p_invoice_distribution_id
1516 and section_type = 'TDS_SECTION'; --Added for bug#8855650 by JMEENA
1517
1518 cursor c_get_tax_sec_det(p_invoice_id number, p_invoice_distribution_id number)
1519 is
1520 select tds_section_code_other, tds_tax_id_other, application_amount, invoice_distribution_id
1521 from jai_ap_tds_prepayments
1522 where invoice_id = p_invoice_id
1523 and invoice_distribution_id_prepay = p_invoice_distribution_id;
1524
1525 ln_si_thgrp_id number;
1526 r_get_tax_sec_det c_get_tax_sec_det%rowtype;
1527
1528 --Added by Xiao Lv for Bug#8345080 on 7-Jan-10, end
1529 /*Bug 12671504 - Start*/
1530 /*Fetch the taxable amount for which TDS is generated*/
1531 CURSOR c_taxable_amount(p_invoice_id NUMBER)
1532 IS
1533 SELECT nvl(sum(taxable_amount), 0)
1534 FROM jai_ap_tds_thhold_trxs
1535 WHERE invoice_id = p_invoice_id
1536 AND tds_event = 'INVOICE VALIDATE'
1537 group by invoice_id;
1538
1539 /*Fetch the taxable basis for which TDS would be generated*/
1540 CURSOR c_available_amount (p_invoice_id number, p_exchange_rate NUMBER)
1541 IS
1542 SELECT nvl(sum(amount*nvl(p_exchange_rate, 1)), 0)
1543 FROM jai_ap_tds_inv_taxes
1544 WHERE invoice_id = p_invoice_id
1545 AND nvl(actual_tax_id, default_tax_id) is not null
1546 AND section_type = 'TDS_SECTION'
1547 AND actual_section_code IS NOT NULL
1548 AND threshold_trx_id IS NOT NULL
1549 group by invoice_id;
1550 /*Bug 12671504 - End*/
1551 /* Bug 14183670 -Start */
1552 cursor c_applied_amount(p_invoice_id number)
1553 is
1554 SELECT nvl(SUM(application_amount), 0)
1555 FROM jai_ap_tds_prepayments
1556 WHERE invoice_id = p_invoice_id
1557 AND tds_threshold_trx_id_unapply IS NULL;
1558 ln_application_amount number;
1559 /* Bug 14183670 -end*/
1560
1561 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
1562 r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
1563 r_get_total_prepayment_tax c_get_total_prepayment_tax%rowtype;
1564
1565 ln_exchange_rate number;
1566 ln_threshold_grp_id number;
1567 ln_total_tds_amount number;
1568 ln_current_threshold_slab_id jai_ap_tds_thhold_grps.current_threshold_slab_id%type;
1569 ln_prepay_tax_id number;
1570
1571 lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
1572 lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
1573 lv_invoice_num_prepay_apply ap_invoices_all.invoice_num%type;
1574 ln_threshold_trx_id_tds number;
1575 ln_threshold_trx_id_wct number;
1576 ln_threshold_trx_id_essi number;
1577 ln_start_threshold_trx_id number;
1578 ln_prepayment_amount number;
1579
1580 lb_result boolean;
1581 ln_req_id number;
1582 ln_pp_section_tax_id number;
1583 ln_threshold_grp_audit_id number;
1584 lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1585 /* Bug 4522507. Added by Lakshmi Gopalsami */
1586 ln_threshold_trx_id_prepay jai_ap_tds_inv_taxes.threshold_trx_id%type;
1587 ln_amt_tds_inv_generated_si number;
1588 --Added the below 6 variables by Sanjikum for Bug#5131075(4718907)
1589 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
1590 lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
1591 ln_after_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
1592 lv_after_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
1593 ln_temp_threshold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
1594 ln_temp_threshold_hdr_id jai_ap_tds_thhold_hdrs.threshold_hdr_id%TYPE;
1595
1596 -- Bug 5722028. Added by CSahoo
1597 ln_tds_tmp_amt number;
1598 /*Bug 5751783 - Start*/
1599 ln_si_tax_id NUMBER ;
1600 ln_parent_invoice_id NUMBER ;
1601 ln_pp_section_invoice_id NUMBER ;
1602 /*Bug 5751783 - End*/
1603 /*Bug 6363056 - Start*/
1604 r_ja_in_tax_codes_prepay c_ja_in_tax_codes%rowtype;
1605 ln_tax_rate_basis JAI_CMN_TAXES_ALL.tax_rate%TYPE ;
1606 ln_si_wct_tax_id JAI_CMN_TAXES_ALL.tax_id%TYPE ;
1607 ln_si_essi_tax_id JAI_CMN_TAXES_ALL.tax_id%TYPE ;
1608 ln_si_thhold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
1609 ln_pp_thhold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
1610 ln_parent_tax_id JAI_CMN_TAXES_ALL.tax_id%TYPE ;
1611 ln_tds_application_amt jai_ap_tds_prepayments.application_amount%TYPE ;
1612 r_pp_jai_ap_tds_thhold_grps c_get_thgrp_det%ROWTYPE ;
1613 r_si_jai_ap_tds_thhold_grps c_get_thgrp_det%ROWTYPE ;
1614 /*Bug 6363056 - End*/
1615 pre_pay_inv_id ap_invoice_distributions_all.invoice_id%TYPE; -- Added by Jia for FP bug6929483
1616 /*START, Bgowrava for Bug#7626202*/
1617 ln_tot_tds_amt number := 0;
1618 ln_tot_appln_amt number := 0;
1619 /*END, Bgowrava for Bug#7626202*/
1620 ln_application_mode VARCHAR2(1); /*Bug 12671504*/
1621 ln_taxable_amount NUMBER; /*Bug 12671504*/
1622 ln_available_amount NUMBER; /*Bug 12671504*/
1623
1624
1625 begin
1626
1627 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.process_tds_invoices', 'START'); /* 1 */
1628 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', '=============jai_ap_tds_prepayemnts_pkg.process_tds_invoices=============');
1629 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' p_invoice_id :'||p_invoice_id);
1630 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' p_invoice_distribution_id :'||p_invoice_distribution_id);
1631 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' p_prepay_distribution_id :'||p_prepay_distribution_id);
1632 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','p_prepay_amount :'||p_prepay_amount);
1633 get_prepay_invoice_id(p_prepay_distribution_id,pre_pay_inv_id); -- Added by Jia for FP bug6929483
1634 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' pre_pay_inv_id :'||pre_pay_inv_id);
1635 open c_gl_sets_of_books(p_set_of_books_id);
1636 fetch c_gl_sets_of_books into r_gl_sets_of_books;
1637 close c_gl_sets_of_books;
1638
1639 if r_gl_sets_of_books.currency_code <> p_invoice_currency_code then
1640 /* Foreign currency invoice */
1641 p_codepath := jai_general_pkg.plot_codepath(6.1, p_codepath); /* 6.1 */
1642 ln_exchange_rate := p_exchange_rate;
1643 end if;
1644
1645 ln_exchange_rate := nvl(ln_exchange_rate, 1);
1646
1647 ln_prepayment_amount := -1 * p_prepay_amount * ln_exchange_rate;
1648
1649 /* update the tax amount for the prepayements */
1650 for cur_rec in c_jai_ap_tds_prepayments(p_invoice_id, p_invoice_distribution_id)
1651 loop
1652
1653 if lv_application_basis is null then
1654 lv_application_basis := cur_rec.application_basis;
1655 end if;
1656 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','lv_application_basis :'||lv_application_basis);
1657 /* TDS application amount */
1658 if cur_rec.tds_tax_id_other is not null AND
1659 cur_rec.tds_tax_id_prepay IS NOT NULL AND -- Bug 6363056
1660 cur_rec.tds_applicable_flag = 'Y' -- Bug 6363056
1661 THEN
1662 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','Inside if');
1663 r_ja_in_tax_codes := null;
1664 open c_ja_in_tax_codes(cur_rec.tds_tax_id_other); -- Bug 6363056
1665 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
1666 close c_ja_in_tax_codes;
1667
1668 ln_tax_rate_basis := r_ja_in_tax_codes.tax_rate; -- bug 6363056
1669 ln_si_tax_id := cur_rec.tds_tax_id_other; -- bug 6363056
1670
1671 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_tax_rate_basis :'||ln_tax_rate_basis);
1672 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_si_tax_id :'||ln_si_tax_id);
1673 /* Bug 5722028. Addd by CSahoo
1674 * Need to round the value as per the setup.
1675 */
1676 ln_tds_tmp_amt := 0;
1677 if r_gl_sets_of_books.currency_code = p_invoice_currency_code then
1678 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1679 * (ln_tax_rate_basis/100), /*Bug 6363056*/
1680 jai_ap_tds_generation_pkg.g_inr_currency_rounding);
1681 else
1682 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1683 * (ln_tax_rate_basis/100), /*Bug 6363056*/
1684 jai_ap_tds_generation_pkg.g_fcy_currency_rounding);
1685 end if;
1686 IF trunc(p_creation_date) >=
1687 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
1688 ln_tds_tmp_amt := jai_ap_tds_generation_pkg.get_rnded_value(ln_tds_tmp_amt);
1689 END IF;
1690 -- End for bug 5722028
1691
1692
1693 update jai_ap_tds_prepayments
1694 set tds_application_amount = ln_tds_tmp_amt, -- Bug 5722028
1695 /*Bug 5751783. Added the update for non-rounded value also*/
1696 calc_tds_appln_amt = cur_rec.application_amount * ln_exchange_rate * (ln_tax_rate_basis/100)
1697 where tds_prepayment_id = cur_rec.tds_prepayment_id;
1698
1699 end if; /* TDS */
1700
1701 /* WCT application amount */
1702 if cur_rec.wct_tax_id_other is not null AND
1703 cur_rec.wct_tax_id_prepay IS NOT NULL AND -- Bug 6363056
1704 cur_rec.wct_applicable_flag = 'Y' -- Bug 6363056
1705 THEN
1706
1707 r_ja_in_tax_codes := null;
1708 open c_ja_in_tax_codes(cur_rec.wct_tax_id_other); -- Bug 6363056
1709 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
1710 close c_ja_in_tax_codes;
1711
1712 /*Bug 6363056 - Start*/
1713 ln_tax_rate_basis := r_ja_in_tax_codes.tax_rate;
1714 ln_si_wct_tax_id := cur_rec.wct_tax_id_other;
1715
1716 IF cur_rec.wct_tax_id_other <> cur_rec.wct_tax_id_prepay THEN
1717 r_ja_in_tax_codes_prepay := NULL ;
1718 OPEN c_ja_in_tax_codes(cur_rec.wct_tax_id_prepay);
1719 FETCH c_ja_in_tax_codes INTO r_ja_in_tax_codes_prepay;
1720 CLOSE c_ja_in_tax_codes;
1721 IF ln_tax_rate_basis > r_ja_in_tax_codes_prepay.tax_rate THEN
1722 ln_tax_rate_basis := r_ja_in_tax_codes_prepay.tax_rate; /* Modified r_ja_in_tax_codes to r_ja_in_tax_codes_prepay for Bug 6972230 */
1723 ln_si_wct_tax_id := cur_rec.wct_tax_id_prepay; /* Modified wct_tax_id_other to wct_tax_id_prepay for Bug 6972230 */
1724 END IF ;
1725 END IF ;
1726 /*Bug 6363056 - End*/
1727
1728
1729 /* Bug 5722028. Addd by CSahoo
1730 * Need to round the value as per the setup.
1731 */
1732 ln_tds_tmp_amt := 0;
1733 if r_gl_sets_of_books.currency_code = p_invoice_currency_code then
1734 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1735 * (ln_tax_rate_basis/100), /*Bug 6363056*/
1736 jai_ap_tds_generation_pkg.g_inr_currency_rounding);
1737 else
1738 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1739 * (ln_tax_rate_basis/100), /*Bug 6363056*/
1740 jai_ap_tds_generation_pkg.g_fcy_currency_rounding);
1741 end if;
1742
1743 /* Bug 7280925. Commented by Lakshmi Gopalsami
1744 * Rounding to 10 is applicable only for TDS.
1745 * WCT and ESSI should be rounded to Re. 1
1746 IF trunc(p_creation_date) >=
1747 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
1748 ln_tds_tmp_amt := jai_ap_tds_generation_pkg.get_rnded_value(ln_tds_tmp_amt);
1749 END IF;
1750 */
1751 -- End for bug 5722028
1752
1753 update jai_ap_tds_prepayments
1754 set wct_application_amount = ln_tds_tmp_amt, -- Bug 5722028
1755 /*Bug 5751783. Added the update for non-rounded value also*/
1756 calc_wct_appln_amt = cur_rec.application_amount * ln_exchange_rate * (ln_tax_rate_basis/100)
1757 where tds_prepayment_id = cur_rec.tds_prepayment_id;
1758
1759 end if; /* WCT */
1760
1761 /* ESSI application amount */
1762 if cur_rec.essi_tax_id_other is not null AND
1763 cur_rec.essi_tax_id_prepay IS NOT NULL AND -- Bug 6363056
1764 cur_rec.essi_applicable_flag = 'Y' -- Bug 6363056
1765 THEN
1766
1767 r_ja_in_tax_codes := null;
1768 open c_ja_in_tax_codes(cur_rec.essi_tax_id_other); --Bug 6363056
1769 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
1770 close c_ja_in_tax_codes;
1771
1772 /*Bug 6363056 - Start*/
1773 ln_tax_rate_basis := r_ja_in_tax_codes.tax_rate;
1774 ln_si_essi_tax_id := cur_rec.essi_tax_id_other;
1775
1776 IF cur_rec.essi_tax_id_other <> cur_rec.essi_tax_id_prepay THEN
1777 r_ja_in_tax_codes_prepay := NULL ;
1778 OPEN c_ja_in_tax_codes(cur_rec.essi_tax_id_prepay);
1779 FETCH c_ja_in_tax_codes INTO r_ja_in_tax_codes_prepay;
1780 CLOSE c_ja_in_tax_codes;
1781 IF ln_tax_rate_basis > r_ja_in_tax_codes_prepay.tax_rate THEN
1782 ln_tax_rate_basis := r_ja_in_tax_codes.tax_rate;
1783 ln_si_essi_tax_id := cur_rec.wct_tax_id_prepay;
1784 END IF ;
1785 END IF ;
1786 /*Bug 6363056 - End*/
1787
1788 /* Bug 5722028. Addd by Lakshmi Gopalsami
1789 * Need to round the value as per the setup.
1790 */
1791 ln_tds_tmp_amt := 0;
1792 if r_gl_sets_of_books.currency_code = p_invoice_currency_code then
1793 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1794 * (ln_tax_rate_basis/100),
1795 jai_ap_tds_generation_pkg.g_inr_currency_rounding);
1796 else
1797 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1798 * (ln_tax_rate_basis/100),
1799 jai_ap_tds_generation_pkg.g_fcy_currency_rounding);
1800 end if;
1801 /* Bug 7280925. Commented by Lakshmi Gopalsami
1802 * Rounding to 10 is applicable only for TDS.
1803 * WCT and ESSI should be rounded to Re. 1
1804 IF trunc(p_creation_date) >=
1805 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
1806 ln_tds_tmp_amt := jai_ap_tds_generation_pkg.get_rnded_value(ln_tds_tmp_amt);
1807 END IF;
1808 */
1809 -- End for bug 5722028
1810
1811 update jai_ap_tds_prepayments
1812 set essi_application_amount = ln_tds_tmp_amt, --Bug 5722028
1813 /*Bug 5751783. Added the update for non-rounded value also*/
1814 calc_essi_appln_amt = cur_rec.application_amount * ln_exchange_rate * (ln_tax_rate_basis/100)
1815 where tds_prepayment_id = cur_rec.tds_prepayment_id;
1816
1817 end if; /* ESSI */
1818
1819 end loop;
1820
1821 open c_get_total_prepayment_tax(p_invoice_id, p_invoice_distribution_id, ln_exchange_rate);
1822 fetch c_get_total_prepayment_tax into r_get_total_prepayment_tax;
1823 close c_get_total_prepayment_tax;
1824
1825 --Added by Xiao Lv for bug#8345080 on 07-Jan-10, begin
1826
1827 open c_get_tax_sec_det(p_invoice_id, p_invoice_distribution_id);
1828 fetch c_get_tax_sec_det into r_get_tax_sec_det;
1829 close c_get_tax_sec_det;
1830
1831 /*Bug 12671504 - Start*/
1832 /*Compare the Taxable amount for which TDS is deducted with the amount for which TDS would be deducted.
1833 If the Taxable amount is less than the Available amount then Prepayment was applied prior to validation.
1834 Taxable amount for Invoice validated after Prepayment application would be effective amount and hence
1835 Prepayment application mode would be 'B' indicating prepayment application happened prior to validation.
1836 Threshold Groups need not be updated with the Prepayment application amount as only Effective amount i.e. Invoice - Prepay
1837 was passed to Threshold Group during validation*/
1838 ln_application_mode := NULL;
1839 OPEN c_taxable_amount(p_invoice_id);
1840 FETCH c_taxable_amount INTO ln_taxable_amount;
1841 CLOSE c_taxable_amount;
1842 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_taxable_amount :'||ln_taxable_amount);
1843 IF ln_taxable_amount > 0 THEN
1844 OPEN c_available_amount(p_invoice_id, p_exchange_rate);
1845 FETCH c_available_amount INTO ln_available_amount;
1846 CLOSE c_available_amount;
1847 /* Bug 14183670 -start */
1848 OPEN c_applied_amount(p_invoice_id);
1849 FETCH c_applied_amount INTO ln_application_amount;
1850 CLOSE c_applied_amount;
1851 /* Bug 14183670 - end */
1852 IF ln_taxable_amount+ln_application_amount <= ln_available_amount THEN
1853 /* commented the below if and added the above if condition for bug 14183670 */
1854 -- IF ln_taxable_amount < ln_available_amount THEN
1855 ln_application_mode := 'B';
1856 ELSE
1857 ln_application_mode := 'A';
1858 END IF;
1859 ELSE
1860 ln_application_mode := 'A';
1861 END IF;
1862 /*Bug 12671504 - End*/
1863 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_application_amount :'||ln_application_amount);
1864 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_available_amount :'||ln_available_amount);
1865 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_application_mode :'||ln_application_mode);
1866 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','r_get_tax_sec_det.application_amount :'||r_get_tax_sec_det.application_amount);
1867 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' r_get_total_prepayment_tax.tds_amount :'||r_get_total_prepayment_tax.tds_amount);
1868 if r_get_tax_sec_det.application_amount > 0
1869 and (r_get_tax_sec_det.tds_section_code_other is not null or r_get_tax_sec_det.tds_tax_id_other is not null)
1870 and r_get_total_prepayment_tax.tds_amount = 0
1871 and ln_application_mode = 'A'
1872 then
1873 open c_get_grp_details_si_inv_dist(p_invoice_id, r_get_tax_sec_det.invoice_distribution_id);
1874 fetch c_get_grp_details_si_inv_dist into ln_si_thgrp_id;
1875 close c_get_grp_details_si_inv_dist;
1876
1877 jai_ap_tds_generation_pkg.maintain_thhold_grps(
1878 p_threshold_grp_id => ln_si_thgrp_id,
1879 p_trx_invoice_apply_amount => r_get_tax_sec_det.application_amount,
1880 p_tds_event => 'PREPAYMENT APPLICATION',
1881 p_invoice_id => p_invoice_id,
1882 p_invoice_distribution_id => p_invoice_distribution_id,
1883 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
1884 p_creation_Date => p_creation_date,
1885 p_process_flag => p_process_flag,
1886 P_process_message => p_process_message,
1887 p_codepath => p_codepath
1888 );
1889 end if; --r_get_tax_sec_det.application_amount > 0
1890
1891 --Added by Xiao Lv for bug#8345080 on 07-Jan-10, end
1892
1893 if r_get_total_prepayment_tax.tds_amount > 0 AND ln_application_mode = 'A' then
1894
1895 /* Bug 6363056
1896 * Get the details of threshold grp for prepay and invoice.
1897 * This determines which group needs to be hit.
1898 */
1899
1900 -- Added parameter pre_pay_inv_id by Jia for FP bug6929483,Begin
1901 ----------------------------------------------------------------------
1902 --open c_get_prepayment_thgroup(p_prepay_distribution_id,'TDS_SECTION'); --rchandan for bug#4428980
1903 open c_get_prepayment_thgroup(pre_pay_inv_id,p_prepay_distribution_id,'TDS_SECTION');
1904 ----------------------------------------------------------------------
1905 -- Added parameter pre_pay_inv_id by Jia for FP bug6929483,End
1906 fetch c_get_prepayment_thgroup into ln_pp_thhold_grp_id, ln_prepay_tax_id, ln_threshold_trx_id_prepay;
1907 close c_get_prepayment_thgroup;
1908 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_pp_thhold_grp_id :'||ln_pp_thhold_grp_id);
1909 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_prepay_tax_id :'||ln_prepay_tax_id);
1910 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_threshold_trx_id_prepay :'||ln_threshold_trx_id_prepay);
1911 IF ln_pp_thhold_grp_id IS NULL
1912 AND (r_get_total_prepayment_tax.tds_amount > 0 OR
1913 r_get_total_prepayment_tax.wct_amount > 0 OR
1914 r_get_total_prepayment_tax.essi_amount > 0) THEN
1915 p_process_flag := 'E';
1916 P_process_message := 'Threshold group identifier is not found against the prepayment invoice TDS tax, cannot proceed.';
1917 goto exit_from_procedure;
1918 end if;
1919
1920 OPEN c_get_thgrp_det(ln_pp_thhold_grp_id);
1921 FETCH c_get_thgrp_det INTO r_pp_jai_ap_tds_thhold_grps;
1922 CLOSE c_get_thgrp_det;
1923
1924 FOR get_si_det IN (SELECT jattt.*,
1925 jatp.tds_prepayment_id tds_prepayment_id,
1926 jatp.application_amount tds_taxable_basis,
1927 jatp.invoice_distribution_id tax_dist
1928 FROM jai_ap_tds_thhold_trxs jattt,
1929 jai_ap_tds_prepayments jatp
1930 WHERE jattt.invoice_id = jatp.invoice_id
1931 AND jattt.tds_event = 'INVOICE VALIDATE'
1932 AND jatp.tds_applicable_flag ='Y'
1933 AND invoice_distribution_id_prepay = p_invoice_distribution_id
1934 AND jattt.invoice_id = p_invoice_id
1935 AND jatp.invoice_distribution_id in
1936 (select invoice_distribution_id
1937 from jai_ap_tdS_inv_taxes
1938 where threshold_trx_id = jattt.threshold_trx_id
1939 and invoice_id = p_invoice_id
1940 and section_type ='TDS_SECTION'
1941 )
1942 )
1943 LOOP
1944
1945 ln_temp_threshold_grp_id := get_si_det.threshold_grp_id;
1946 ln_parent_tax_id := get_si_det.tax_id ;
1947
1948 IF NVL (ln_pp_thhold_grp_id, 0) <> 0 AND
1949 NVL (ln_temp_threshold_grp_id, 0) <> 0 AND
1950 NVL (ln_temp_threshold_grp_id,0 ) <> NVL (ln_pp_thhold_grp_id, 0)
1951 THEN
1952 OPEN c_get_thgrp_det(ln_temp_threshold_grp_id);
1953 FETCH c_get_thgrp_det INTO r_si_jai_ap_tds_thhold_grps;
1954 CLOSE c_get_thgrp_det;
1955 IF r_pp_jai_ap_tds_thhold_grps.fin_year > r_si_jai_ap_tds_thhold_grps.fin_year THEN
1956 ln_temp_threshold_grp_id := ln_pp_thhold_grp_id;
1957 END IF ;
1958 END IF ;
1959
1960 ln_threshold_grp_id := ln_temp_threshold_grp_id;
1961 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','ln_threshold_grp_id :'||ln_threshold_grp_id);
1962 --Call to procedure - get_tds_threshold_slab, Store the current Threshold slab and type before PP application
1963 jai_ap_tds_generation_pkg.get_tds_threshold_slab(
1964 p_prepay_distribution_id => p_prepay_distribution_id,
1965 p_threshold_grp_id => ln_temp_threshold_grp_id,
1966 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
1967 p_threshold_slab_id => ln_threshold_slab_id,
1968 p_threshold_type => lv_threshold_type,
1969 p_process_flag => p_process_flag,
1970 p_process_message => p_process_message,
1971 p_codepath => p_codepath);
1972
1973 IF p_process_flag = 'E' THEN
1974 goto exit_from_procedure;
1975 END IF;
1976 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' r_get_total_prepayment_tax.tds_amount :'||r_get_total_prepayment_tax.tds_amount);
1977 if r_get_total_prepayment_tax.tds_amount > 0 THEN
1978 /* update the threshold with the tds amount that will be impacted because of this application */
1979 jai_ap_tds_generation_pkg.maintain_thhold_grps
1980 (
1981 p_threshold_grp_id => ln_threshold_grp_id,
1982 p_trx_invoice_apply_amount => get_si_det.tds_taxable_basis,
1983 p_tds_event => 'PREPAYMENT APPLICATION',
1984 p_invoice_id => p_invoice_id,
1985 p_invoice_distribution_id => p_invoice_distribution_id,
1986 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
1987 p_creation_Date => p_creation_date,
1988 p_process_flag => p_process_flag,
1989 P_process_message => p_process_message,
1990 p_codepath => p_codepath
1991 );
1992
1993 IF p_process_flag = 'E' THEN
1994 GOTO exit_from_procedure;
1995 END IF;
1996
1997 -- Update each distribution with the threshold grp id as
1998 -- it may vary depending on the date and the group.
1999 update jai_ap_tds_prepayments
2000 set tds_threshold_grp_id = ln_threshold_grp_id
2001 where tds_prepayment_id = get_si_det.tds_prepayment_id; -- Bug 6363056
2002
2003 /* TDS invoice was generated against the Prepayment,
2004 check for what amount of the SI, TDS invoice was generated */
2005
2006 /* Bug 6363056
2007 * Changed from p_invoice_distribution_id to get_si_det.invoice_distribution_id
2008 * as we need to generate for each line in jai_ap_tds_thhold_thhold_trxs
2009 */
2010 open c_get_amt_tds_inv_generated_si(p_invoice_id, get_si_det.tax_dist);
2011 fetch c_get_amt_tds_inv_generated_si INTO ln_amt_tds_inv_generated_si, ln_tds_application_amt;
2012 close c_get_amt_tds_inv_generated_si;
2013 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','p_invoice_id :' || p_invoice_id);
2014 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','get_si_det.tax_dist :' || get_si_det.tax_dist);
2015 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','ln_amt_tds_inv_generated_si :' || ln_amt_tds_inv_generated_si);
2016 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','ln_tds_application_amt :' || ln_tds_application_amt);
2017 /*Bug 8431516 - Start*/
2018 ln_tot_tds_amt := ln_tot_tds_amt + ln_amt_tds_inv_generated_si;
2019 ln_tot_appln_amt := ln_tot_appln_amt + ln_tds_application_amt;
2020 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','ln_tot_tds_amt :' || ln_tot_tds_amt);
2021 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg','ln_tot_appln_amt :' || ln_tot_appln_amt);
2022 if p_event = 'INSERT' then
2023 update jai_ap_tds_prepayments
2024 set tds_threshold_trx_id_apply = -999
2025 where tds_prepayment_id = get_si_det.tds_prepayment_id; --Bug 6031679
2026 end if;
2027 /*Bug 8431516 - End*/
2028 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_amt_tds_inv_generated_si :'||ln_amt_tds_inv_generated_si);
2029 IF ln_amt_tds_inv_generated_si > 0 THEN
2030 IF lv_application_basis = 'STANDARD INVOICE' THEN
2031 /* get the standard invoice number */
2032 OPEN c_si_ap_invoices_all(p_invoice_id);
2033 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
2034 CLOSE c_si_ap_invoices_all;
2035 ELSE
2036 /*Bug 8606302 - Start*/
2037 /*cursor c_ap_invoices_all would not fetch the Invoice ID if the Prepayment did not
2038 suffer TDS when it was validated initially, but only when Threshold was breached
2039 In the above case there would be no records in jai_ap_tds_inv_taxes with the distribution
2040 ID of the Prepayment Invoice*/
2041 get_prepay_invoice_id(p_prepay_distribution_id, ln_parent_invoice_id);
2042 OPEN c_si_ap_invoices_all (ln_parent_invoice_id);
2043 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
2044 CLOSE c_si_ap_invoices_all;
2045 /*Bug 8606302 - End*/
2046 END IF ; /* lv_application_basis*/
2047 end if; /* if ln_amt_tds_inv_generated_si > 0 then */
2048 end if ;
2049 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' lv_invoice_num_prepay_apply :'||lv_invoice_num_prepay_apply);
2050 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_parent_invoice_id :'||ln_parent_invoice_id);
2051 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' get_tds_threshold_slab :p_prepay_distribution_id :'||p_prepay_distribution_id);
2052
2053 --Call to procedure - get_tds_threshold_slab. Store the current Threshold slab and type After PP application
2054 jai_ap_tds_generation_pkg.get_tds_threshold_slab(
2055 p_prepay_distribution_id => p_prepay_distribution_id,
2056 p_threshold_grp_id => ln_temp_threshold_grp_id,
2057 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
2058 p_threshold_slab_id => ln_after_threshold_slab_id,
2059 p_threshold_type => lv_after_threshold_type,
2060 p_process_flag => p_process_flag,
2061 p_process_message => p_process_message,
2062 p_codepath => p_codepath);
2063 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_temp_threshold_grp_id :'||ln_temp_threshold_grp_id);
2064 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_temp_threshold_hdr_id :'||ln_temp_threshold_hdr_id);
2065 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' ln_after_threshold_slab_id :'||ln_after_threshold_slab_id);
2066 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg',' lv_after_threshold_type :'||lv_after_threshold_type);
2067 IF p_process_flag = 'E' THEN
2068 goto exit_from_procedure;
2069 END IF;
2070
2071 END LOOP ; /* get_si_det */
2072 end if; /* if r_get_total_prepayment_tax.tds_amount > 0 then */ --moved this statement from above to here for Bug 6972230
2073
2074 /*Bug 8431516 - Start*/
2075 IF ln_tot_tds_amt > 0 THEN
2076 IF lv_application_basis = 'STANDARD INVOICE' THEN
2077 /* get the standard invoice number */
2078 OPEN c_si_ap_invoices_all(p_invoice_id);
2079 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
2080 CLOSE c_si_ap_invoices_all;
2081 ELSE
2082 /*Bug 8606302 - Start*/
2083 /*cursor c_ap_invoices_all would not fetch the Invoice ID if the Prepayment did not
2084 suffer TDS when it was validated initially, but only when Threshold was breached
2085 In the above case there would be no records in jai_ap_tds_inv_taxes with the distribution
2086 ID of the Prepayment Invoice*/
2087 get_prepay_invoice_id(p_prepay_distribution_id, ln_parent_invoice_id);
2088 OPEN c_si_ap_invoices_all (ln_parent_invoice_id);
2089 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
2090 CLOSE c_si_ap_invoices_all;
2091 /*Bug 8606302 - End*/
2092 END IF ; /* lv_application_basis*/
2093
2094 fnd_file.put_line(FND_FILE.log, ' value of dist id '|| p_invoice_distribution_id);
2095 fnd_file.put_line(FND_FILE.log, ' value of prepay dist id '|| p_prepay_distribution_id);
2096 fnd_file.put_line(FND_FILE.log, ' value of invoice id '|| ln_parent_invoice_id);
2097 fnd_file.put_line(FND_FILE.log, ' value of invoice num '||lv_invoice_num_prepay_apply);
2098 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', ' value of dist id '|| p_invoice_distribution_id);
2099 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', ' value of prepay dist id '|| p_prepay_distribution_id);
2100 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', ' value of invoice id '|| ln_parent_invoice_id);
2101 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', ' value of invoice num '||lv_invoice_num_prepay_apply);
2102
2103 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', ' value of before threshold type '||lv_threshold_type);
2104 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', ' value of after threshold type '||lv_after_threshold_type);
2105 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', ' value of before slab '||ln_threshold_slab_id);
2106 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', ' value of after slab '||ln_after_threshold_slab_id);
2107 if p_event = 'INSERT' then /*Bug 8431516*/
2108 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', ' ln_tot_appln_amt:'||ln_tot_appln_amt);
2109 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', 'ln_threshold_grp_id:'||ln_threshold_grp_id);
2110 /*Bug 5751783. Changed from invoice_id to ln_parent_invoice_id*/
2111 jai_ap_tds_generation_pkg.generate_tds_invoices
2112 (
2113 pn_invoice_id => ln_parent_invoice_id,
2114 pn_invoice_distribution_id => p_invoice_distribution_id,
2115 pv_invoice_num_prepay_apply => lv_invoice_num_prepay_apply,
2116 pn_taxable_amount => ln_tot_appln_amt, /*Bug 6363056*/
2117 pn_tax_amount => ln_tot_tds_amt,
2118 pn_tax_id => ln_parent_tax_id,
2119 pd_accounting_date => p_accounting_date,
2120 pv_tds_event => 'PREPAYMENT APPLICATION',
2121 pn_threshold_grp_id => ln_threshold_grp_id,
2122 pv_tds_invoice_num => lv_invoice_to_tds_num,
2123 pv_cm_invoice_num => lv_invoice_to_vendor_num,
2124 pn_threshold_trx_id => ln_threshold_trx_id_tds,
2125 pd_creation_date => p_creation_date, -- Bug 5722028. Added by Lakshmi Gopalsami
2126 pn_calc_tax_amount => 0,/* Added for bug 12965614 */
2127 p_process_flag => p_process_flag,
2128 p_process_message => p_process_message
2129 );
2130
2131 IF p_process_flag = 'E' THEN
2132 GOTO exit_from_procedure;
2133 END IF ;
2134
2135 /* prepayment apply scenario for backward compatibility*/
2136
2137 IF ln_start_threshold_trx_id is null THEN
2138 ln_start_threshold_trx_id := ln_threshold_trx_id_tds;
2139 END IF ;
2140
2141 /* Update the threshold group */
2142 jai_ap_tds_generation_pkg.maintain_thhold_grps
2143 ( p_threshold_grp_id => ln_threshold_grp_id,
2144 p_trx_tax_paid => (-1 * ln_tot_tds_amt),
2145 p_tds_event => 'PREPAYMENT APPLICATION',
2146 p_invoice_id => p_invoice_id,
2147 p_invoice_distribution_id => p_invoice_distribution_id,
2148 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
2149 p_creation_date => p_creation_date,
2150 p_process_flag => p_process_flag,
2151 P_process_message => p_process_message,
2152 p_codepath => p_codepath
2153 );
2154
2155 --Added by Sanjikum for Bug#4722011
2156 IF p_process_flag = 'E' THEN
2157 GOTO exit_from_procedure;
2158 END IF;
2159
2160 /* Update jai_ap_tds_prepayments with threshold_trx_id_apply*/
2161 -- Update each distribution with the threshold grp id as
2162 -- it may vary depending on the date and the group.
2163 -- changed invoice_distribution_id_prepay to invoice_distribution_id.
2164 update jai_ap_tds_prepayments
2165 set tds_threshold_trx_id_apply = ln_threshold_trx_id_tds
2166 where tds_threshold_trx_id_apply = -999
2167 and invoice_id = p_invoice_id
2168 and invoice_distribution_id_prepay = p_invoice_distribution_id; /*Bug 6363056*/
2169
2170 if p_event = 'INSERT' then --Added for Bug 8431516
2171 /* Bug 5751783
2172 * Changed from p_invoice_id to ln_parent_invoice_id
2173 * Parent invoice_id should be depending on the TDS invoice
2174 * created.
2175 */
2176 jai_ap_tds_generation_pkg.process_threshold_rollback
2177 ( p_invoice_id => ln_parent_invoice_id,
2178 p_before_threshold_type => lv_threshold_type,
2179 p_after_threshold_type => lv_after_threshold_type,
2180 p_before_threshold_slab_id => ln_threshold_slab_id,
2181 p_after_threshold_slab_id => ln_after_threshold_slab_id,
2182 p_threshold_grp_id => ln_temp_threshold_grp_id,
2183 p_org_id => p_org_id,
2184 p_accounting_date => p_accounting_date,
2185 p_invoice_distribution_id => p_invoice_distribution_id,
2186 p_prepay_distribution_id => p_prepay_distribution_id,
2187 p_process_flag => p_process_flag,
2188 p_process_message => p_process_message,
2189 p_codepath => p_codepath);
2190
2191 IF p_process_flag = 'E' THEN
2192 goto exit_from_procedure;
2193 END IF;
2194 end if; /*if p_event = 'INSERT' then*/ --Added for Bug 8431516
2195
2196 end if; /*if p_event = 'INSERT' then*/ --Added for Bug 8431516
2197 end if ; /* IF ln_tot_tds_amt > 0 THEN */
2198 /*Bug 8431516 - End*/
2199
2200 /* prepayment apply scenario for backward compatibility*/
2201 update JAI_AP_TDS_INVOICES
2202 set amt_reversed = nvl(amt_reversed, 0) + r_get_total_prepayment_tax.tds_amount,
2203 amt_applied = nvl(amt_applied, 0) + abs(p_prepay_amount)
2204 where invoice_id = p_invoice_id;
2205
2206 -- End for bug 6363056.
2207
2208 if r_get_total_prepayment_tax.wct_amount > 0 then
2209 /* get the tax_id */
2210 ln_pp_section_tax_id := null;
2211 ln_parent_invoice_id := null;
2212
2213 /*Bug 6363056*/
2214 if lv_application_basis = 'STANDARD INVOICE' then
2215 /* get the standard invoice number */
2216 ln_parent_invoice_id := p_invoice_id;
2217 else
2218 /* Get the prepayment number */
2219 open c_get_pp_section_tax_id(p_prepay_distribution_id, 'WCT_SECTION');
2220 fetch c_get_pp_section_tax_id into ln_pp_section_tax_id, ln_parent_invoice_id;
2221 close c_get_pp_section_tax_id;
2222 end if;
2223
2224 /*Bug 6363056*/
2225 IF nvl(ln_pp_section_tax_id,-1) <> ln_si_wct_tax_id THEN
2226 ln_pp_section_tax_id := ln_si_wct_tax_id;
2227 END IF ;
2228
2229 if p_event = 'INSERT' then --Added for Bug 8431516
2230 /*Bug 5751783 - Changed from p_invoice_id to ln_pp_section_invoice_id*/
2231 jai_ap_tds_generation_pkg.generate_tds_invoices
2232 (
2233 pn_invoice_id => ln_parent_invoice_id ,
2234 pn_invoice_distribution_id => p_invoice_distribution_id ,
2235 pn_taxable_amount => r_get_total_prepayment_tax.wct_taxable_basis ,
2236 pn_tax_amount => r_get_total_prepayment_tax.wct_amount ,
2237 pn_tax_id => ln_pp_section_tax_id ,
2238 pd_accounting_date => p_accounting_date ,
2239 pv_tds_event => 'PREPAYMENT APPLICATION' ,
2240 pn_threshold_grp_id => null ,
2241 pv_tds_invoice_num => lv_invoice_to_tds_num ,
2242 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
2243 pn_threshold_trx_id => ln_threshold_trx_id_wct ,
2244 pd_creation_date => p_creation_date ,
2245 pn_calc_tax_amount => 0, /*Added for bug 12965614 */
2246 p_process_flag => p_process_flag ,
2247 p_process_message => p_process_message
2248 );
2249
2250 if p_process_flag = 'E' then
2251 goto exit_from_procedure;
2252 end if;
2253
2254 update jai_ap_tds_prepayments
2255 set wct_threshold_trx_id_apply = ln_threshold_trx_id_wct
2256 where invoice_id = p_invoice_id
2257 and invoice_distribution_id_prepay = p_invoice_distribution_id
2258 and wct_applicable_flag = 'Y';
2259
2260 if ln_start_threshold_trx_id is null then
2261 ln_start_threshold_trx_id := ln_threshold_trx_id_wct;
2262 end if;
2263 end if; /*if p_event = 'INSERT' then*/ --Added for Bug 8431516
2264 /* Generate the return invoices */
2265 end if; /* if r_get_total_prepayment_tax.wct_amount > 0 then */
2266
2267
2268 if r_get_total_prepayment_tax.essi_amount > 0 then
2269 /* get the tax_id */
2270 ln_pp_section_tax_id := null;
2271 ln_parent_invoice_id := null;
2272 /*Bug 6363056*/
2273 if lv_application_basis = 'STANDARD INVOICE' then
2274 /* get the standard invoice number */
2275 ln_parent_invoice_id := p_invoice_id;
2276 else
2277 /* Get the prepayment number */
2278 open c_get_pp_section_tax_id(p_prepay_distribution_id, 'WCT_SECTION');
2279 fetch c_get_pp_section_tax_id into ln_pp_section_tax_id, ln_parent_invoice_id;
2280 close c_get_pp_section_tax_id;
2281 end if;
2282
2283 /*Bug 6363056*/
2284 IF nvl(ln_pp_section_tax_id,-1) <> ln_si_essi_tax_id THEN
2285 ln_pp_section_tax_id := ln_si_essi_tax_id;
2286 END IF ;
2287
2288 IF p_event = 'INSERT' then --Added for Bug 8431516
2289 /*Bug 5751783 - Changed from p_invoice_id to ln_pp_section_invoice_id*/
2290 jai_ap_tds_generation_pkg.generate_tds_invoices
2291 (
2292 pn_invoice_id => ln_parent_invoice_id ,
2293 pn_invoice_distribution_id => p_invoice_distribution_id ,
2294 pn_taxable_amount => r_get_total_prepayment_tax.essi_taxable_basis ,
2295 pn_tax_amount => r_get_total_prepayment_tax.essi_amount ,
2296 pn_tax_id => ln_pp_section_tax_id ,
2297 pd_accounting_date => p_accounting_date ,
2298 pv_tds_event => 'PREPAYMENT APPLICATION' ,
2299 pn_threshold_grp_id => null ,
2300 pv_tds_invoice_num => lv_invoice_to_tds_num ,
2301 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
2302 pn_threshold_trx_id => ln_threshold_trx_id_essi ,
2303 pd_creation_date => p_creation_date ,
2304 pn_calc_tax_amount => 0, /*Added for bug 12965614 */
2305 p_process_flag => p_process_flag ,
2306 p_process_message => p_process_message
2307 );
2308
2309 if p_process_flag = 'E' then
2310 goto exit_from_procedure;
2311 end if;
2312
2313 update jai_ap_tds_prepayments
2314 set essi_threshold_trx_id_apply = ln_threshold_trx_id_essi
2315 where invoice_id = p_invoice_id
2316 and invoice_distribution_id_prepay = p_invoice_distribution_id
2317 and essi_applicable_flag = 'Y';
2318
2319 if ln_start_threshold_trx_id is null then
2320 ln_start_threshold_trx_id := ln_threshold_trx_id_essi;
2321 end if;
2322 end if; /*IF p_event = 'INSERT' then*/ --Added for Bug 8431516
2323 end if; /* if r_get_total_prepayment_tax.essi_amount > 0 then */
2324
2325 if ln_start_threshold_trx_id is not null then
2326 /*Bug - 9826422
2327 Records are inserted into AP Interface tables using Standard Invoice, but import_and_approve
2328 was called using the Prepayment Invoice ID. Hence wrong group_id was getting passed and no
2329 Invoices were getting improved*/
2330 jai_ap_tds_generation_pkg.import_and_approve
2331 (
2332 p_invoice_id => ln_parent_invoice_id,
2333 p_start_thhold_trx_id => ln_start_threshold_trx_id,
2334 p_tds_event => 'PREPAYMENT APPLICATION',
2335 p_process_flag => p_process_flag,
2336 p_process_message => p_process_message
2337 );
2338
2339 end if;
2340
2341 << exit_from_procedure >>
2342 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
2343 return;
2344
2345 exception
2346 when others then
2347 p_process_flag := 'E';
2348 P_process_message := 'jai_ap_tds_prepayemnts_pkg.process_tds_invoices :' || sqlerrm;
2349 return;
2350 end process_tds_invoices;
2351
2352 /***********************************************************************************************/
2353
2354 /* **************************************** process_old_transaction **************************************** */
2355
2356 procedure process_old_transaction
2357 (
2358 p_invoice_id in number,
2359 p_invoice_distribution_id in number,
2360 p_prepay_distribution_id in number,
2361 p_amount in number,
2362 p_last_updated_by in number,
2363 p_last_update_date in date,
2364 p_created_by in number,
2365 p_creation_date in date,
2366 p_org_id in number,
2367 p_process_flag out nocopy varchar2,
2368 p_process_message out nocopy varchar2
2369 )
2370 is
2371
2372 cursor c_tds_count(p_invoice_id number, p_source_attribute varchar2) is
2373 select count(1)
2374 from JAI_AP_TDS_INVOICES
2375 where invoice_id = p_invoice_id
2376 and source_attribute = p_source_attribute;
2377
2378 /* Following cursor definition has been changed to cater for the obsoletion of table ja_in_ap_tds_inv_temp */
2379 cursor c_tds_count_unapp(p_invoice_id number, p_section_type varchar2) IS --rchandan for bug#4428980
2380 select count(1)
2381 from jai_ap_tds_inv_taxes
2382 where invoice_id = p_invoice_id
2383 and section_type = p_section_type; --rchandan for bug#4428980
2384
2385 ln_tds_count_attribute1 number;
2386 ln_tds_count_attribute2 number;
2387 ln_tds_count_attribute3 number;
2388 lb_result boolean;
2389 ln_req_id number;
2390
2391 begin
2392 /* This code is to replace the following three triggers of the old tds regime
2393 1. ja_in_prepay_insert_trg
2394 2. ja_in_prepay_insert_wct_trg
2395 3. ja_in_prepay_insert_wct1_trg
2396 */
2397
2398 open c_tds_count(p_invoice_id, 'ATTRIBUTE1');
2399 fetch c_tds_count into ln_tds_count_attribute1;
2400 close c_tds_count;
2401
2402 if ln_tds_count_attribute1 = 0 then
2403 -- the standard invoice does not have tds attached to it.
2404 -- check if there is TDS record in temp table, this would happen when the invoice is unapproved.
2405 -- Bug 4754213. Added by Lakshmi Gopalsami
2406 open c_tds_count_unapp(p_invoice_id, 'TDS_SECTION'); --rchandan for bug#4428980
2407 fetch c_tds_count_unapp into ln_tds_count_attribute1;
2408 close c_tds_count_unapp;
2409
2410 ln_tds_count_attribute1 := nvl(ln_tds_count_attribute1, 0);
2411
2412 if ln_tds_count_attribute1 = 0 then
2413 goto attribut2_processing;
2414 end if;
2415
2416 end if;
2417
2418
2419 if p_amount < 0 then /* Case of Apply */
2420
2421 lb_result := fnd_request.set_mode(TRUE);
2422 ln_req_id :=
2423 fnd_request.submit_request
2424 (
2425 'JA',
2426 'JAINPREP',
2427 'To Insert Prepayment Distributions',
2428 '',
2429 FALSE,
2430 p_invoice_id,
2431 p_invoice_distribution_id,
2432 abs(p_amount),
2433 p_last_updated_by,
2434 p_last_update_date,
2435 p_created_by ,
2436 p_creation_date,
2437 p_org_id,
2438 p_prepay_distribution_id,
2439 'I',
2440 'ATTRIBUTE1'
2441 );
2442
2443 elsif p_amount > 0 then
2444
2445 lb_result := fnd_request.set_mode(TRUE);
2446 ln_req_id :=
2447 fnd_request.submit_request
2448 (
2449 'JA',
2450 'JAINUNPR',
2451 'To Unapply Prepayment Distributions',
2452 '',
2453 FALSE,
2454 p_invoice_id,
2455 p_last_updated_by,
2456 p_last_update_date,
2457 p_created_by ,
2458 p_creation_date,
2459 p_org_id,
2460 p_prepay_distribution_id,
2461 p_invoice_distribution_id,
2462 'ATTRIBUTE1'
2463 );
2464
2465 end if;
2466
2467 /* Check for WCT tax */
2468 << attribut2_processing >>
2469 open c_tds_count(p_invoice_id, 'ATTRIBUTE2');
2470 fetch c_tds_count into ln_tds_count_attribute2;
2471 close c_tds_count;
2472
2473 if ln_tds_count_attribute2 = 0 then
2474 -- the standard invoice does not have tds attached to it.
2475 -- check if there is TDS record in temp table, this would happen when the invoice is unapproved.
2476 open c_tds_count_unapp(p_invoice_id, 'WCT_SECTION');
2477 fetch c_tds_count_unapp into ln_tds_count_attribute2;
2478 close c_tds_count_unapp;
2479
2480 ln_tds_count_attribute2 := nvl(ln_tds_count_attribute2, 0);
2481
2482 if ln_tds_count_attribute2 = 0 then
2483 goto attribut3_processing;
2484 end if;
2485
2486 end if;
2487
2488
2489 if p_amount < 0 then /* Case of Apply */
2490
2491 lb_result := fnd_request.set_mode(TRUE);
2492 ln_req_id :=
2493 fnd_request.submit_request
2494 (
2495 'JA',
2496 'JAINPREP',
2497 'To Insert Prepayment Distributions',
2498 '',
2499 FALSE,
2500 p_invoice_id,
2501 p_invoice_distribution_id,
2502 abs(p_amount),
2503 p_last_updated_by,
2504 p_last_update_date,
2505 p_created_by ,
2506 p_creation_date,
2507 p_org_id,
2508 p_prepay_distribution_id,
2509 'I',
2510 'ATTRIBUTE2'
2511 );
2512
2513 elsif p_amount > 0 then
2514
2515 lb_result := fnd_request.set_mode(TRUE);
2516 ln_req_id :=
2517 fnd_request.submit_request
2518 (
2519 'JA',
2520 'JAINUNPR',
2521 'To Unapply Prepayment Distributions',
2522 '',
2523 FALSE,
2524 p_invoice_id,
2525 p_last_updated_by,
2526 p_last_update_date,
2527 p_created_by ,
2528 p_creation_date,
2529 p_org_id,
2530 p_prepay_distribution_id,
2531 p_invoice_distribution_id,
2532 'ATTRIBUTE2'
2533 );
2534
2535 end if;
2536
2537 /* Check for ESSI Tax */
2538 << attribut3_processing >>
2539 open c_tds_count(p_invoice_id, 'ATTRIBUTE3');
2540 fetch c_tds_count into ln_tds_count_attribute3;
2541 close c_tds_count;
2542
2543 if ln_tds_count_attribute3 = 0 then
2544 -- the standard invoice does not have tds attached to it.
2545 -- check if there is TDS record in temp table, this would happen when the invoice is unapproved.
2546 open c_tds_count_unapp(p_invoice_id, 'ESSI_SECTION');
2547 fetch c_tds_count_unapp into ln_tds_count_attribute3;
2548 close c_tds_count_unapp;
2549
2550 ln_tds_count_attribute3 := nvl(ln_tds_count_attribute3, 0);
2551
2552 if ln_tds_count_attribute3 = 0 then
2553 goto exit_from_procedure;
2554 end if;
2555
2556 end if;
2557
2558
2559 if p_amount < 0 then /* Case of Apply */
2560
2561 lb_result := fnd_request.set_mode(TRUE);
2562 ln_req_id :=
2563 fnd_request.submit_request
2564 (
2565 'JA',
2566 'JAINPREP',
2567 'To Insert Prepayment Distributions',
2568 '',
2569 FALSE,
2570 p_invoice_id,
2571 p_invoice_distribution_id,
2572 abs(p_amount),
2573 p_last_updated_by,
2574 p_last_update_date,
2575 p_created_by ,
2576 p_creation_date,
2577 p_org_id,
2578 p_prepay_distribution_id,
2579 'I',
2580 'ATTRIBUTE3'
2581 );
2582
2583 elsif p_amount > 0 then
2584
2585 lb_result := fnd_request.set_mode(TRUE);
2586 ln_req_id :=
2587 fnd_request.submit_request
2588 (
2589 'JA',
2590 'JAINUNPR',
2591 'To Unapply Prepayment Distributions',
2592 '',
2593 FALSE,
2594 p_invoice_id,
2595 p_last_updated_by,
2596 p_last_update_date,
2597 p_created_by ,
2598 p_creation_date,
2599 p_org_id,
2600 p_prepay_distribution_id,
2601 p_invoice_distribution_id,
2602 'ATTRIBUTE3'
2603 );
2604
2605 end if;
2606
2607 << exit_from_procedure >>
2608 return;
2609
2610 exception
2611 when others then
2612 p_process_flag := 'E';
2613 P_process_message := 'jai_ap_tds_prepayemnts_pkg.process_old_transaction :' || sqlerrm;
2614 return;
2615 end process_old_transaction;
2616
2617 /* **************************************** process_old_transaction **************************************** */
2618
2619 end jai_ap_tds_prepayments_pkg;