[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.5.12010000.2 2008/09/15 12:51:25 lgopalsa 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 --------------------------------------------------------------------------- */
90
91 procedure process_prepayment
92 (
93 p_invoice_id in number,
94 p_invoice_distribution_id in number,
95 p_prepay_distribution_id in number,
96 p_parent_reversal_id in number,
97 p_prepay_amount in number,
98 p_vendor_id in number,
99 p_vendor_site_id in number,
100 p_accounting_date in date,
101 p_invoice_currency_code in varchar2,
102 p_exchange_rate in number,
103 p_set_of_books_id in number,
104 p_org_id in number,
105 -- Bug 5722028. Added by CSahoo
106 p_creation_date in date,
107 p_process_flag out nocopy varchar2,
108 p_process_message out nocopy varchar2,
109 p_codepath in out nocopy varchar2
110 )
111 is
112 begin
113
114 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.process_prepayment', 'START'); /* 1 */
115
116 if p_prepay_amount < 0 then
117
118 /* Event is APPLY of prepayment */
119
120 jai_ap_tds_prepayments_pkg.allocate_prepayment
121 (
122 p_invoice_id => p_invoice_id ,
123 p_invoice_distribution_id => p_invoice_distribution_id ,
124 p_prepay_amount => p_prepay_amount ,
125 p_process_flag => p_process_flag ,
126 p_process_message => p_process_message ,
127 p_codepath => p_codepath
128 );
129
130 if p_process_flag = 'E' then
131 goto exit_from_procedure;
132 end if;
133
134 jai_ap_tds_prepayments_pkg.populate_section_tax
135 (
136 p_invoice_id => p_invoice_id ,
137 p_invoice_distribution_id => p_invoice_distribution_id ,
138 p_prepay_distribution_id => p_prepay_distribution_id ,
139 p_process_flag => p_process_flag ,
140 p_process_message => p_process_message ,
141 p_codepath => p_codepath
142 );
143
144 if p_process_flag = 'E' then
145 goto exit_from_procedure;
146 end if;
147
148
149 jai_ap_tds_prepayments_pkg.process_tds_invoices
150 (
151 p_invoice_id => p_invoice_id ,
152 p_invoice_distribution_id => p_invoice_distribution_id ,
153 p_prepay_distribution_id => p_prepay_distribution_id ,
154 p_prepay_amount => p_prepay_amount ,
155 p_vendor_id => p_vendor_id ,
156 p_vendor_site_id => p_vendor_site_id ,
157 p_accounting_date => p_accounting_date ,
158 p_invoice_currency_code => p_invoice_currency_code ,
159 p_exchange_rate => p_exchange_rate ,
160 p_set_of_books_id => p_set_of_books_id ,
161 p_org_id => p_org_id ,
162 -- Bug 5722028. Added by Lakshmi Gopalsami
163 p_creation_date => p_creation_date,
164 p_process_flag => p_process_flag ,
165 p_process_message => p_process_message ,
166 p_codepath => p_codepath
167 );
168
169 if p_process_flag = 'E' then
170 goto exit_from_procedure;
171 end if;
172
173
174 elsif p_prepay_amount > 0 then
175
176 /* Event is UNAPPLY of prepayment */
177
178 jai_ap_tds_prepayments_pkg.process_unapply
179 (
180 p_invoice_id => p_invoice_id ,
181 p_invoice_distribution_id => p_invoice_distribution_id ,
182 p_parent_distribution_id => p_parent_reversal_id ,
183 p_prepay_amount => p_prepay_amount ,
184 p_vendor_id => p_vendor_id ,
185 p_vendor_site_id => p_vendor_site_id ,
186 p_accounting_date => p_accounting_date ,
187 p_invoice_currency_code => p_invoice_currency_code ,
188 p_exchange_rate => p_exchange_rate ,
189 p_set_of_books_id => p_set_of_books_id ,
190 p_org_id => p_org_id ,
191 -- Bug 5722028. Added by CSahoo
192 p_creation_date => p_creation_date,
193 p_process_flag => p_process_flag ,
194 p_process_message => p_process_message ,
195 p_codepath => p_codepath
196 );
197
198 --Added by Sanjikum for Bug#5131075(4722011)
199 IF p_process_flag = 'E' THEN
200 goto exit_from_procedure;
201 END IF;
202
203 end if;
204
205
206 << exit_from_procedure >>
207 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
208 return;
209
210 exception
211 when others then
212 p_process_flag := 'E';
213 P_process_message := 'jai_ap_tds_prepayemnts_pkg.process_prepayment :' || sqlerrm;
214 return;
215 end process_prepayment;
216
217
218
219 /***********************************************************************************************/
220 procedure process_unapply
221 (
222 p_invoice_id in number,
223 p_invoice_distribution_id in number, /* PREPAY UNAPPLY distribution */
224 p_parent_distribution_id in number, /* parent PREPAY APPLY distribution */
225 p_prepay_amount in number,
226 p_vendor_id in number,
227 p_vendor_site_id in number,
228 p_accounting_date in date,
229 p_invoice_currency_code in varchar2,
230 p_exchange_rate in number,
231 p_set_of_books_id in number,
232 p_org_id in number,
233 -- Bug 5722028. Added by CSahoo
234 p_creation_date in date,
235 p_process_flag out nocopy varchar2,
236 p_process_message out nocopy varchar2,
237 p_codepath in out nocopy varchar2
238 )
239 is
240
241 cursor c_get_total_prepayment_tax
242 (p_invoice_id number, p_invoice_distribution_id number, p_exchange_rate number) is
243 select sum( decode(tds_applicable_flag , 'Y', application_amount*p_exchange_rate, 0) ) tds_taxable_basis,
244 sum( decode(tds_applicable_flag , 'Y', tds_application_amount, 0) ) tds_amount,
245 sum( decode(wct_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) wct_taxable_basis,
246 sum( decode(wct_applicable_flag, 'Y', wct_application_amount, 0) ) wct_amount,
247 sum( decode(essi_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) essi_taxable_basis,
248 sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount
249 from jai_ap_tds_prepayments
250 where invoice_id = p_invoice_id
251 and invoice_distribution_id_prepay = p_invoice_distribution_id;
252
253 cursor c_tds_details_apply(p_invoice_id number, p_invoice_distribution_id number) is
254 select tds_threshold_grp_id,
255 tds_threshold_trx_id_apply
256 from jai_ap_tds_prepayments
257 where invoice_id = p_invoice_id
258 and invoice_distribution_id_prepay = p_invoice_distribution_id
259 and tds_threshold_grp_id is not null;
260
261 cursor c_wct_details_apply(p_invoice_id number, p_invoice_distribution_id number) is
262 select wct_threshold_trx_id_apply
263 from jai_ap_tds_prepayments
264 where invoice_id = p_invoice_id
265 and invoice_distribution_id_prepay = p_invoice_distribution_id
266 and wct_threshold_trx_id_apply is not null;
267
268 cursor c_essi_details_apply(p_invoice_id number, p_invoice_distribution_id number) is
269 select essi_threshold_trx_id_apply
270 from jai_ap_tds_prepayments
271 where invoice_id = p_invoice_id
272 and invoice_distribution_id_prepay = p_invoice_distribution_id
273 and essi_threshold_trx_id_apply is not null;
274
275 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
276 select currency_code
277 from gl_sets_of_books
278 where set_of_books_id = cp_set_of_books_id;
279
280 cursor c_get_tds_tax_id(p_invoice_id number, p_prepay_distribution_id number) is
281 select tds_tax_id_prepay
282 from jai_ap_tds_prepayments
283 where invoice_id = p_invoice_id
284 and invoice_distribution_id_prepay = p_prepay_distribution_id
285 and tds_tax_id_prepay is not null
286 and tds_applicable_flag = 'Y';
287
288 cursor c_get_wct_tax_id(p_invoice_id number, p_prepay_distribution_id number) is
289 select wct_tax_id_prepay
290 from jai_ap_tds_prepayments
291 where invoice_id = p_invoice_id
292 and invoice_distribution_id_prepay = p_prepay_distribution_id
293 and wct_tax_id_prepay is not null
294 and wct_applicable_flag = 'Y';
295
296 cursor c_get_essi_tax_id(p_invoice_id number, p_prepay_distribution_id number) is
297 select essi_tax_id_prepay
298 from jai_ap_tds_prepayments
299 where invoice_id = p_invoice_id
300 and invoice_distribution_id_prepay = p_prepay_distribution_id
301 and essi_tax_id_prepay is not null
302 and essi_applicable_flag = 'Y';
303
304 cursor c_get_invoice_num_of_apply(p_threshold_trx_id number) is
305 select invoice_to_tds_authority_num,
306 invoice_to_vendor_num
307 from jai_ap_tds_thhold_trxs
308 where threshold_trx_id = p_threshold_trx_id;
309
310
311 r_get_total_prepayment_tax c_get_total_prepayment_tax%rowtype;
312 r_tds_details_apply c_tds_details_apply%rowtype;
313 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
314
315 lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
316 lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
317 ln_threshold_trx_id_apply number;
318 ln_threshold_trx_id_tds number;
319 ln_threshold_trx_id_wct number;
320 ln_threshold_trx_id_essi number;
321 ln_start_threshold_trx_id number;
322 ln_exchange_rate number;
323 ln_tax_id number;
324 ln_threshold_grp_id number;
325 ln_threshold_grp_audit_id number;
326 lv_invoice_num_to_tds_apply ap_invoices_all.invoice_num%type;
327 lv_invoice_num_to_vendor_apply ap_invoices_all.invoice_num%type;
328
329 begin
330 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.process_unapply', 'START'); /* 1 */
331
332 open c_gl_sets_of_books(p_set_of_books_id);
333 fetch c_gl_sets_of_books into r_gl_sets_of_books;
334 close c_gl_sets_of_books;
335
336 if r_gl_sets_of_books.currency_code <> p_invoice_currency_code then
337 /* Foreign currency invoice */
338 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
339 ln_exchange_rate := p_exchange_rate;
340 end if;
341
342 ln_exchange_rate := nvl(ln_exchange_rate, 1);
343
344 open c_get_total_prepayment_tax(p_invoice_id, p_parent_distribution_id, ln_exchange_rate);
345 fetch c_get_total_prepayment_tax into r_get_total_prepayment_tax;
346 close c_get_total_prepayment_tax;
347
348 /* Unapply TDS */
349 if r_get_total_prepayment_tax.tds_amount > 0 then
350
351 open c_tds_details_apply(p_invoice_id, p_parent_distribution_id);
352 fetch c_tds_details_apply into r_tds_details_apply;
353 close c_tds_details_apply;
354
355 ln_threshold_grp_id:= r_tds_details_apply.tds_threshold_grp_id;
356 jai_ap_tds_generation_pkg.maintain_thhold_grps
357 (
358 p_threshold_grp_id => ln_threshold_grp_id,
359 p_trx_invoice_unapply_amount => r_get_total_prepayment_tax.tds_taxable_basis,
360 p_tds_event => 'PREPAYMENT UNAPPLICATION',
361 p_invoice_id => p_invoice_id,
362 p_invoice_distribution_id => p_invoice_distribution_id,
363 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
364 p_process_flag => p_process_flag,
365 P_process_message => p_process_message,
366 p_codepath => p_codepath
367 );
368
369 --Added by Sanjikum for Bug#5131075(4722011)
370 IF p_process_flag = 'E' THEN
371 goto exit_from_procedure;
372 END IF;
373
374
375 if r_tds_details_apply.tds_threshold_trx_id_apply is not null then
376
377 lv_invoice_to_tds_num := null;
378 lv_invoice_to_vendor_num := null;
379
380 /* get the tds invoice numbers at apply */
381 open c_get_invoice_num_of_apply(r_tds_details_apply.tds_threshold_trx_id_apply);
382 fetch c_get_invoice_num_of_apply into lv_invoice_num_to_tds_apply, lv_invoice_num_to_vendor_apply;
383 close c_get_invoice_num_of_apply ;
384
385 /* get the tax id */
386 ln_tax_id := null;
387 open c_get_tds_tax_id(p_invoice_id, p_parent_distribution_id);
388 fetch c_get_tds_tax_id into ln_tax_id;
389 close c_get_tds_tax_id;
390
391 jai_ap_tds_generation_pkg.generate_tds_invoices
392 (
393 pn_invoice_id => p_invoice_id ,
394 pn_invoice_distribution_id => p_invoice_distribution_id ,
395 pv_invoice_num_to_tds_apply => lv_invoice_num_to_tds_apply ,
396 pv_invoice_num_to_vendor_apply => lv_invoice_num_to_vendor_apply ,
397 pn_taxable_amount => r_get_total_prepayment_tax.tds_taxable_basis ,
398 pn_tax_amount => r_get_total_prepayment_tax.tds_amount ,
399 pn_tax_id => ln_tax_id ,
400 pd_accounting_date => p_accounting_date ,
401 pv_tds_event => 'PREPAYMENT UNAPPLICATION' ,
402 pn_threshold_grp_id => r_tds_details_apply.tds_threshold_grp_id ,
403 pv_tds_invoice_num => lv_invoice_to_tds_num ,
404 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
405 pn_threshold_trx_id => ln_threshold_trx_id_tds ,
406 pd_creation_date => p_creation_date, -- Bug 5722028. Added by CSahoo
407 p_process_flag => p_process_flag ,
408 p_process_message => p_process_message
409 );
410
411 if p_process_flag = 'E' then
412 goto exit_from_procedure;
413 end if;
414
415 /* prepayment apply scenario for backward compatibility*/
416 update JAI_AP_TDS_INVOICES
417 set amt_reversed = nvl(amt_reversed, 0) - r_get_total_prepayment_tax.tds_amount,
418 amt_applied = nvl(amt_applied, 0) - abs(p_prepay_amount)
419 where invoice_id = p_invoice_id;
420 /* prepayment apply scenario for backward compatibility*/
421
422 /* Update the threshold group */
423
424 ln_threshold_grp_id:= r_tds_details_apply.tds_threshold_grp_id;
425 jai_ap_tds_generation_pkg.maintain_thhold_grps
426 (
427 p_threshold_grp_id => ln_threshold_grp_id,
428 p_trx_tax_paid => r_get_total_prepayment_tax.tds_amount,
429 p_tds_event => 'PREPAYMENT UNAPPLICATION',
430 p_invoice_id => p_invoice_id,
431 p_invoice_distribution_id => p_invoice_distribution_id,
432 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
433 p_process_flag => p_process_flag,
434 P_process_message => p_process_message,
435 p_codepath => p_codepath
436 );
437
438 --Added by Sanjikum for Bug#5131075(4722011)
439 IF p_process_flag = 'E' THEN
440 goto exit_from_procedure;
441 END IF;
442
443 update jai_ap_tds_prepayments
444 set tds_threshold_trx_id_unapply = ln_threshold_trx_id_tds
445 where invoice_id = p_invoice_id
446 and invoice_distribution_id_prepay = p_parent_distribution_id
447 and tds_threshold_trx_id_apply is not null
448 and tds_applicable_flag = 'Y';
449
450 if ln_start_threshold_trx_id is null then
451 ln_start_threshold_trx_id := ln_threshold_trx_id_tds;
452 end if;
453
454 end if; /* r_tds_details_apply.tds_threshold_trx_id_apply is not null */
455
456 end if; /* r_get_total_prepayment_tax.tds_amount > 0 */
457 /* Unapply TDS */
458
459
460 /* Unapply WCT */
461 if r_get_total_prepayment_tax.wct_amount > 0 then
462
463 ln_threshold_trx_id_apply := null;
464 open c_wct_details_apply(p_invoice_id, p_parent_distribution_id);
465 fetch c_wct_details_apply into ln_threshold_trx_id_apply;
466 close c_wct_details_apply;
467
468 if ln_threshold_trx_id_apply is not null then
469
470 lv_invoice_to_tds_num := null;
471 lv_invoice_to_vendor_num := null;
472
473 /* get the tds invoice numbers at apply */
474 open c_get_invoice_num_of_apply(ln_threshold_trx_id_apply);
475 fetch c_get_invoice_num_of_apply into lv_invoice_num_to_tds_apply, lv_invoice_num_to_vendor_apply;
476 close c_get_invoice_num_of_apply ;
477
478
479 /* get the tax id */
480 ln_tax_id := null;
481 open c_get_wct_tax_id(p_invoice_id, p_parent_distribution_id);
482 fetch c_get_wct_tax_id into ln_tax_id;
483 close c_get_wct_tax_id;
484
485 jai_ap_tds_generation_pkg.generate_tds_invoices
486 (
487 pn_invoice_id => p_invoice_id ,
488 pn_invoice_distribution_id => p_invoice_distribution_id ,
489 pv_invoice_num_to_tds_apply => lv_invoice_num_to_tds_apply ,
490 pv_invoice_num_to_vendor_apply => lv_invoice_num_to_vendor_apply ,
491 pn_taxable_amount => r_get_total_prepayment_tax.wct_taxable_basis ,
492 pn_tax_amount => r_get_total_prepayment_tax.wct_amount ,
493 pn_tax_id => ln_tax_id ,
494 pd_accounting_date => p_accounting_date ,
495 pv_tds_event => 'PREPAYMENT UNAPPLICATION' ,
496 pn_threshold_grp_id => null ,
497 pv_tds_invoice_num => lv_invoice_to_tds_num ,
498 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
499 pn_threshold_trx_id => ln_threshold_trx_id_wct ,
500 pd_creation_date => p_creation_date, -- Bug 5722028. Added by csahoo
501 p_process_flag => p_process_flag ,
502 p_process_message => p_process_message
503 );
504
505 if p_process_flag = 'E' then
506 goto exit_from_procedure;
507 end if;
508
509 update jai_ap_tds_prepayments
510 set wct_threshold_trx_id_unapply = ln_threshold_trx_id_wct
511 where invoice_id = p_invoice_id
512 and invoice_distribution_id_prepay = p_parent_distribution_id
513 and wct_threshold_trx_id_apply is not null
514 and wct_applicable_flag = 'Y';
515
516 if ln_start_threshold_trx_id is null then
517 ln_start_threshold_trx_id := ln_threshold_trx_id_wct;
518 end if;
519
520 end if; /* ln_threshold_trx_id_apply.tds_threshold_trx_id_apply is not null */
521
522 end if;
523 /* Unapply WCT */
524
525 /* Unapply ESSI */
526 if r_get_total_prepayment_tax.wct_amount > 0 then
527
528 ln_threshold_trx_id_apply := null;
529 open c_essi_details_apply(p_invoice_id, p_parent_distribution_id);
530 fetch c_essi_details_apply into ln_threshold_trx_id_apply;
531 close c_essi_details_apply;
532
533 if ln_threshold_trx_id_apply is not null then
534
535 lv_invoice_to_tds_num := null;
536 lv_invoice_to_vendor_num := null;
537
538 /* get the tds invoice numbers at apply */
539 open c_get_invoice_num_of_apply(ln_threshold_trx_id_apply);
540 fetch c_get_invoice_num_of_apply into lv_invoice_num_to_tds_apply, lv_invoice_num_to_vendor_apply;
541 close c_get_invoice_num_of_apply ;
542
543 /* get the tax id */
544 ln_tax_id := null;
545 open c_get_essi_tax_id(p_invoice_id, p_parent_distribution_id);
546 fetch c_get_essi_tax_id into ln_tax_id;
547 close c_get_essi_tax_id;
548
549 jai_ap_tds_generation_pkg.generate_tds_invoices
550 (
551 pn_invoice_id => p_invoice_id ,
552 pn_invoice_distribution_id => p_invoice_distribution_id ,
553 pv_invoice_num_to_tds_apply => lv_invoice_num_to_tds_apply ,
554 pv_invoice_num_to_vendor_apply => lv_invoice_num_to_vendor_apply ,
555 pn_taxable_amount => r_get_total_prepayment_tax.essi_taxable_basis ,
556 pn_tax_amount => r_get_total_prepayment_tax.essi_amount ,
557 pn_tax_id => ln_tax_id ,
558 pd_accounting_date => p_accounting_date ,
559 pv_tds_event => 'PREPAYMENT UNAPPLICATION' ,
560 pn_threshold_grp_id => null ,
561 pv_tds_invoice_num => lv_invoice_to_tds_num ,
562 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
563 pn_threshold_trx_id => ln_threshold_trx_id_essi ,
564 pd_creation_date => p_creation_date, -- Bug 5722028. Added by CSahoo
565 p_process_flag => p_process_flag ,
566 p_process_message => p_process_message
567 );
568
569 if p_process_flag = 'E' then
570 goto exit_from_procedure;
571 end if;
572
573 update jai_ap_tds_prepayments
574 set essi_threshold_trx_id_unapply = ln_threshold_trx_id_essi
575 where invoice_id = p_invoice_id
576 and invoice_distribution_id_prepay = p_parent_distribution_id
577 and essi_threshold_trx_id_apply is not null
578 and essi_applicable_flag = 'Y';
579
580
581 if ln_start_threshold_trx_id is null then
582 ln_start_threshold_trx_id := ln_threshold_trx_id_essi;
583 end if;
584
585 end if; /* ln_threshold_trx_id_apply.tds_threshold_trx_id_apply is not null */
586
587 end if;
588 /* Unapply ESSI */
589
590 /* update the unapply flag for all */
591 update jai_ap_tds_prepayments
592 set unapply_flag = 'Y'
593 where invoice_id = p_invoice_id
594 and invoice_distribution_id_prepay = p_parent_distribution_id;
595
596 if ln_start_threshold_trx_id is not null then
597
598 jai_ap_tds_generation_pkg.import_and_approve
599 (
600 p_invoice_id => p_invoice_id,
601 p_start_thhold_trx_id => ln_start_threshold_trx_id,
602 p_tds_event => 'PREPAYMENT UNAPPLICATION',
603 p_process_flag => p_process_flag,
604 p_process_message => p_process_message
605 );
606
607 --Added by Sanjikum for Bug#5131075(4722011)
608 IF p_process_flag = 'E' THEN
609 goto exit_from_procedure;
610 END IF;
611
612 end if;
613
614 << exit_from_procedure >>
615 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
616 return;
617
618 exception
619 when others then
620 p_process_flag := 'E';
621 P_process_message := 'jai_ap_tds_prepayemnts_pkg.process_unapply :' || sqlerrm;
622 return;
623 end process_unapply;
624
625 /***********************************************************************************************/
626
627 procedure allocate_prepayment
628 (
629 p_invoice_id in number,
630 p_invoice_distribution_id in number, /* Of the PREPAY line */
631 p_prepay_amount in number,
632 p_process_flag out nocopy varchar2,
633 p_process_message out nocopy varchar2,
634 p_codepath in out nocopy varchar2
635 )
636 is
637
638 cursor c_jai_ap_tds_inv_taxes(p_invoice_id number, p_prepay_distribution_id number,cp_section_type jai_ap_tds_inv_taxes.section_type%type) is
639 select invoice_distribution_id, amount
640 from jai_ap_tds_inv_taxes
641 where invoice_id = p_invoice_id
642 and invoice_distribution_id <> p_prepay_distribution_id
643 and section_type = cp_section_type;
644
645 cursor c_get_amount_already_applied(p_invoice_distribution_id number) is
646 select sum(application_amount)
647 from jai_ap_tds_prepayments
648 where invoice_distribution_id = p_invoice_distribution_id
649 and nvl(unapply_flag, 'N') <> 'Y';
650
651 ln_remaining_prepayment_amount number;
652 ln_effective_available_amount number;
653 ln_already_applied_amount number;
654 ln_application_amount number;
655
656
657 begin
658
659 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.allocate_prepayment', 'START'); /* 1 */
660
661 ln_remaining_prepayment_amount := abs(p_prepay_amount); /* Apply amount is negative */
662
663 /* Loop through to get the taxable basis for each line in jai_ap_tds_inv_taxes */
664 /* It is ok to loop through section_type = 'TDS_SECTION as considering any one section type
665 is ok and tds section will always be there */
666
667 -- Bug 4754213. Added by Lakshmi Gopalsami
668 for cur_si_distributions_rec in c_jai_ap_tds_inv_taxes(p_invoice_id, p_invoice_distribution_id,'TDS_SECTION')
669 loop
670
671 ln_already_applied_amount:= 0;
672 ln_effective_available_amount := 0;
673 ln_application_amount := 0;
674
675 open c_get_amount_already_applied(cur_si_distributions_rec.invoice_distribution_id);
676 fetch c_get_amount_already_applied into ln_already_applied_amount;
677 close c_get_amount_already_applied;
678
679 ln_already_applied_amount := nvl(ln_already_applied_amount, 0);
680
681 ln_effective_available_amount := cur_si_distributions_rec.amount - ln_already_applied_amount;
682
683 ln_application_amount := least(ln_remaining_prepayment_amount, ln_effective_available_amount);
684
685 if ln_application_amount > 0 then
686
687 /* Insert into jai_ap_tds_prepayments */
688 insert into jai_ap_tds_prepayments
689 (
690 tds_prepayment_id ,
691 invoice_id ,
692 invoice_distribution_id_prepay ,
693 invoice_distribution_id ,
694 application_amount ,
695 created_by ,
696 creation_date ,
697 last_updated_by ,
698 last_update_date ,
699 last_update_login
700 )
701 values
702 (
703 jai_ap_tds_prepayments_s.nextval ,
704 p_invoice_id ,
705 p_invoice_distribution_id ,
706 cur_si_distributions_rec.invoice_distribution_id ,
707 ln_application_amount ,
708 fnd_global.user_id ,
709 sysdate ,
710 fnd_global.user_id ,
711 sysdate ,
712 fnd_global.login_id
713 );
714
715 end if;
716
717 ln_remaining_prepayment_amount := ln_remaining_prepayment_amount - ln_application_amount;
718
719 if ln_remaining_prepayment_amount <= 0 then
720 goto exit_from_procedure;
721 end if;
722
723 end loop; /* cur_si_distributions_rec in c_jai_ap_tds_inv_taxes */
724
725
726 << exit_from_procedure >>
727 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
728 return;
729
730 exception
731 when others then
732 p_process_flag := 'E';
733 P_process_message := 'jai_ap_tds_prepayemnts_pkg.allocate_prepayment :' || sqlerrm;
734 return;
735 end allocate_prepayment;
736
737 /***********************************************************************************************/
738
739 procedure populate_section_tax
740 (
741 p_invoice_id in number,
742 p_invoice_distribution_id in number, /* Of the PREPAY line in the SI*/
743 p_prepay_distribution_id in number, /*Distribution id of the PP invoice */
744 p_process_flag out nocopy varchar2,
745 p_process_message out nocopy varchar2,
746 p_codepath in out nocopy varchar2
747 )
748 is
749
750 cursor c_get_tax_details_pp_inv_dist(p_prepay_distribution_id number) is
751 select section_type,
752 actual_section_code section_code,
753 actual_tax_id tax_id
754 from jai_ap_tds_inv_taxes
755 where invoice_distribution_id = p_prepay_distribution_id
756 and actual_tax_id is not null;
757
758 cursor c_get_tax_details_si_inv_dist(p_invoice_id number, p_invoice_distribution_id number) is
759 select section_type,
760 nvl(actual_section_code, default_section_code) section_code,
761 nvl(actual_tax_id, default_tax_id) tax_id
762 from jai_ap_tds_inv_taxes
763 where invoice_id = p_invoice_id
764 and invoice_distribution_id = p_invoice_distribution_id;
765
766
767 cursor c_jai_ap_tds_prepayments(p_invoice_id number, p_invoice_distribution_id number) is
768 select tds_prepayment_id,
769 invoice_distribution_id
770 from jai_ap_tds_prepayments
771 where invoice_id = p_invoice_id
772 and invoice_distribution_id_prepay = p_invoice_distribution_id;
773
774
775
776 cursor c_get_tds_application_basis(p_invoice_id number) is
777 select 'N'
778 from jai_ap_tds_inv_taxes
779 where invoice_id = p_invoice_id
780 and nvl(match_status_flag, 'N') <> 'A';
781
782
783 lv_applicable_flag varchar2(1);
784 lv_is_si_validated_flag varchar2(1);
785
786 lv_tds_section_code_prepay jai_ap_tds_prepayments.tds_section_code_prepay%type;
787 ln_tds_tax_id_prepay jai_ap_tds_prepayments.tds_tax_id_prepay%type;
788 ln_wct_tax_id_prepay jai_ap_tds_prepayments.wct_tax_id_prepay%type;
789 ln_essi_tax_id_prepay jai_ap_tds_prepayments.essi_tax_id_prepay%type;
790 lv_application_basis jai_ap_tds_prepayments.application_basis%type;
791
792
793 lv_tds_section_code_other jai_ap_tds_prepayments.tds_section_code_other%type;
794 ln_tds_tax_id_other jai_ap_tds_prepayments.tds_tax_id_other%type;
795 lv_tds_applicable_flag jai_ap_tds_prepayments.tds_applicable_flag%type;
796 ln_wct_tax_id_other jai_ap_tds_prepayments.wct_tax_id_other%type;
797 lv_wct_applicable_flag jai_ap_tds_prepayments.wct_applicable_flag%type;
798 ln_essi_tax_id_other jai_ap_tds_prepayments.essi_tax_id_other%type;
799 lv_essi_applicable_flag jai_ap_tds_prepayments.essi_applicable_flag%type;
800
801
802
803
804 begin
805
806 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.populate_section_tax', 'START'); /* 1 */
807
808 /* Get the tds_application_basis :
809 This will be STANDARD INVOICE if the SI has already been validated, else it will be PREPAYMENT */
810 open c_get_tds_application_basis(p_invoice_id);
811 fetch c_get_tds_application_basis into lv_is_si_validated_flag;
812 close c_get_tds_application_basis;
813
814 if nvl(lv_is_si_validated_flag, 'Y') = 'Y' then
815 lv_application_basis := 'STANDARD INVOICE';
816 else
817 lv_application_basis := 'PREPAYMENT';
818 end if;
819
820 /* Get the details of the taxes of all sections that was applicable on the distribution line as in the Prepayment */
821 for cur_rec_pp_tax_details in c_get_tax_details_pp_inv_dist(p_prepay_distribution_id) loop
822 -- Bug 4754213. Added by Lakshmi Gopalsami
823 if cur_rec_pp_tax_details.section_type = 'TDS_SECTION' then
824 lv_tds_section_code_prepay := cur_rec_pp_tax_details.section_code;
825 ln_tds_tax_id_prepay := cur_rec_pp_tax_details.tax_id;
826 elsif cur_rec_pp_tax_details.section_type = 'WCT_SECTION' then
827 ln_wct_tax_id_prepay := cur_rec_pp_tax_details.tax_id;
828 elsif cur_rec_pp_tax_details.section_type = 'ESSI_SECTION' then
829 ln_essi_tax_id_prepay := cur_rec_pp_tax_details.tax_id;
830 end if;
831
832 end loop; /* cur_rec_pp_tax_details */
833
834
835 /* Loop and get all the distribution is that has been been allocated for this prepayment and
836 get the tax details that is applicable on the allocated line */
837 for cur_rec_pp_allocations in c_jai_ap_tds_prepayments(p_invoice_id, p_invoice_distribution_id) loop
838
839 for cur_rec in c_get_tax_details_si_inv_dist(p_invoice_id, cur_rec_pp_allocations.invoice_distribution_id) loop
840 -- Bug 4754213. Added by Lakshmi Gopalsami
841 if cur_rec.section_type = 'TDS_SECTION' then
842
843 lv_tds_section_code_other := cur_rec.section_code;
844 ln_tds_tax_id_other := cur_rec.tax_id;
845
846 if lv_tds_section_code_other = lv_tds_section_code_prepay and
847 lv_tds_section_code_other is not null and
848 lv_tds_section_code_prepay is not null
849 then
850 lv_tds_applicable_flag := 'Y';
851 else
852 lv_tds_applicable_flag := 'N';
853 end if;
854
855 elsif cur_rec.section_type = 'WCT_SECTION' then
856
857 ln_wct_tax_id_other := cur_rec.tax_id;
858
859 if ln_wct_tax_id_prepay is not null and ln_wct_tax_id_other is not null then
860 lv_wct_applicable_flag := 'Y';
861 else
862 lv_wct_applicable_flag := 'N';
863 end if;
864
865 elsif cur_rec.section_type = 'ESSI_SECTION' then
866
867 ln_essi_tax_id_other := cur_rec.tax_id;
868
869 if ln_essi_tax_id_prepay is not null and ln_essi_tax_id_other is not null then
870 lv_essi_applicable_flag := 'Y';
871 else
872 lv_essi_applicable_flag := 'N';
873 end if;
874
875 end if; /* Section type of the SI distributions */
876
877 end loop; /* Cur rec */
878
879
880 /* Update jai_ap_tds_prepayments */
881 update jai_ap_tds_prepayments
882 set application_basis = lv_application_basis ,
883 tds_section_code_prepay = lv_tds_section_code_prepay ,
884 tds_section_code_other = lv_tds_section_code_other ,
885 tds_tax_id_prepay = ln_tds_tax_id_prepay ,
886 tds_tax_id_other = ln_tds_tax_id_other ,
887 tds_applicable_flag = lv_tds_applicable_flag ,
888 wct_tax_id_prepay = ln_wct_tax_id_prepay ,
889 wct_tax_id_other = ln_wct_tax_id_other ,
890 wct_applicable_flag = lv_wct_applicable_flag ,
891 essi_tax_id_prepay = ln_essi_tax_id_prepay ,
892 essi_tax_id_other = ln_essi_tax_id_other ,
893 essi_applicable_flag = lv_essi_applicable_flag
894 where tds_prepayment_id = cur_rec_pp_allocations.tds_prepayment_id;
895
896
897 end loop; /* cur_rec_pp_allocations */
898
899
900 << exit_from_procedure >>
901 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
902 return;
903
904 exception
905 when others then
906 p_process_flag := 'E';
907 P_process_message := 'jai_ap_tds_prepayemnts_pkg.populate_section_tax :' || sqlerrm;
908 return;
909 end populate_section_tax;
910
911 /***********************************************************************************************/
912 procedure process_tds_invoices
913 (
914 p_invoice_id in number,
915 p_invoice_distribution_id in number,
916 p_prepay_distribution_id in number,
917 p_prepay_amount in number,
918 p_vendor_id in number,
919 p_vendor_site_id in number,
920 p_accounting_date in date,
921 p_invoice_currency_code in varchar2,
922 p_exchange_rate in number,
923 p_set_of_books_id in number,
924 p_org_id in number,
925 -- Bug 5722028. Added by CSahoo
926 p_creation_date in date,
927 p_process_flag out nocopy varchar2,
928 p_process_message out nocopy varchar2,
929 p_codepath in out nocopy varchar2
930 )
931 is
932
933 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
934 select currency_code
935 from gl_sets_of_books
936 where set_of_books_id = cp_set_of_books_id;
937
938 cursor c_jai_ap_tds_prepayments(p_invoice_id number, p_invoice_distribution_id number) is
939 select tds_prepayment_id,
940 application_amount,
941 application_basis,
942 decode(tds_applicable_flag, 'Y',
943 decode(application_basis, 'STANDARD INVOICE', tds_tax_id_other, tds_tax_id_prepay),
944 null) tds_tax_id,
945 decode(wct_applicable_flag, 'Y',
946 decode(application_basis, 'STANDARD INVOICE', wct_tax_id_other, wct_tax_id_prepay),
947 null) wct_tax_id,
948 decode(essi_applicable_flag, 'Y',
949 decode(application_basis, 'STANDARD INVOICE', essi_tax_id_other, essi_tax_id_prepay),
950 null) essi_tax_id
951 from jai_ap_tds_prepayments
952 where invoice_id = p_invoice_id
953 and invoice_distribution_id_prepay = p_invoice_distribution_id;
954
955
956 cursor c_ja_in_tax_codes(p_tax_id number) is
957 select nvl(tax_rate, 0) tax_rate
958 from JAI_CMN_TAXES_ALL
959 where tax_id = p_tax_id;
960
961 cursor c_get_prepayment_thgroup(p_prepay_distribution_id number,cp_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
962 select threshold_grp_id,
963 actual_tax_id
964 from jai_ap_tds_inv_taxes
965 where invoice_distribution_id = p_prepay_distribution_id
966 and section_type = cp_section_type; --rchandan for bug#4428980
967
968 cursor c_get_pp_section_tax_id(p_prepay_distribution_id number, p_section_type varchar2) is
969 select actual_tax_id
970 from jai_ap_tds_inv_taxes
971 where invoice_distribution_id = p_prepay_distribution_id
972 and section_type = p_section_type;
973
974
975 cursor c_jai_ap_tds_thhold_grps(p_threshold_grp_id number) is
976 select nvl(current_threshold_slab_id, 0) current_threshold_slab_id
977 from jai_ap_tds_thhold_grps
978 where threshold_grp_id = p_threshold_grp_id;
979
980 cursor c_ap_invoices_all (p_invoice_distribution_id number) is
981 select invoice_num
982 from ap_invoices_all
983 where invoice_id in
984 ( select invoice_id
985 from jai_ap_tds_inv_taxes /* ap_invoice_distributions not used for mutation problem */
986 where invoice_distribution_id = p_invoice_distribution_id);
987
988
989 cursor c_get_total_prepayment_tax
990 (p_invoice_id number, p_invoice_distribution_id number, p_exchange_rate number) is
991 select sum( decode(tds_applicable_flag , 'Y', application_amount*p_exchange_rate, 0) ) tds_taxable_basis,
992 sum( decode(tds_applicable_flag , 'Y', tds_application_amount, 0) ) tds_amount,
993 sum( decode(wct_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) wct_taxable_basis,
994 sum( decode(wct_applicable_flag, 'Y', wct_application_amount, 0) ) wct_amount,
995 sum( decode(essi_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) essi_taxable_basis,
996 sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount
997 from jai_ap_tds_prepayments
998 where invoice_id = p_invoice_id
999 and invoice_distribution_id_prepay = p_invoice_distribution_id;
1000
1001 /* Bug 4522507. Added by Lakshmi Gopalsami */
1002
1003 cursor c_get_if_tds_inv_generated_pp(p_prepay_distribution_id number) is
1004 select threshold_trx_id
1005 from jai_ap_tds_inv_taxes
1006 where invoice_distribution_id = p_prepay_distribution_id
1007 -- Bug 4754213. Added by Lakshmi Gopalsami
1008 and section_type = 'TDS_SECTION';
1009
1010 cursor c_get_amt_tds_inv_generated_si(p_invoice_id number, p_invoice_distribution_id number) is
1011 select sum(tds_application_amount)
1012 from jai_ap_tds_prepayments jatp
1013 where invoice_id = p_invoice_id
1014 and invoice_distribution_id_prepay = p_invoice_distribution_id
1015 and tds_applicable_flag = 'Y'
1016 and exists (select '1'
1017 from jai_ap_tds_inv_taxes
1018 where invoice_distribution_id = jatp.invoice_distribution_id
1019 -- Bug 4754213. Added by Lakshmi Gopalsami
1020 and section_type = 'TDS_SECTION'
1021 and threshold_trx_id is not null
1022 );
1023
1024
1025
1026 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
1027 r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
1028 r_get_total_prepayment_tax c_get_total_prepayment_tax%rowtype;
1029
1030 ln_exchange_rate number;
1031 ln_threshold_grp_id number;
1032 ln_total_tds_amount number;
1033 ln_current_threshold_slab_id jai_ap_tds_thhold_grps.current_threshold_slab_id%type;
1034 ln_prepay_tax_id number;
1035
1036 lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
1037 lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
1038 lv_invoice_num_prepay_apply ap_invoices_all.invoice_num%type;
1039 ln_threshold_trx_id_tds number;
1040 ln_threshold_trx_id_wct number;
1041 ln_threshold_trx_id_essi number;
1042 ln_start_threshold_trx_id number;
1043 ln_prepayment_amount number;
1044
1045 lb_result boolean;
1046 ln_req_id number;
1047 ln_pp_section_tax_id number;
1048 ln_threshold_grp_audit_id number;
1049 lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1050 /* Bug 4522507. Added by Lakshmi Gopalsami */
1051 ln_threshold_trx_id_prepay jai_ap_tds_inv_taxes.threshold_trx_id%type;
1052 ln_amt_tds_inv_generated_si number;
1053 --Added the below 6 variables by Sanjikum for Bug#5131075(4718907)
1054 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
1055 lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
1056 ln_after_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
1057 lv_after_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
1058 ln_temp_threshold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
1059 ln_temp_threshold_hdr_id jai_ap_tds_thhold_hdrs.threshold_hdr_id%TYPE;
1060
1061 -- Bug 5722028. Added by CSahoo
1062 ln_tds_tmp_amt number;
1063
1064 begin
1065
1066 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.process_tds_invoices', 'START'); /* 1 */
1067
1068 open c_gl_sets_of_books(p_set_of_books_id);
1069 fetch c_gl_sets_of_books into r_gl_sets_of_books;
1070 close c_gl_sets_of_books;
1071
1072 if r_gl_sets_of_books.currency_code <> p_invoice_currency_code then
1073 /* Foreign currency invoice */
1074 p_codepath := jai_general_pkg.plot_codepath(6.1, p_codepath); /* 6.1 */
1075 ln_exchange_rate := p_exchange_rate;
1076 end if;
1077
1078 ln_exchange_rate := nvl(ln_exchange_rate, 1);
1079
1080 ln_prepayment_amount := -1 * p_prepay_amount * ln_exchange_rate;
1081
1082 /* update the tax amount for the prepayements */
1083 for cur_rec in c_jai_ap_tds_prepayments(p_invoice_id, p_invoice_distribution_id)
1084 loop
1085
1086 if lv_application_basis is null then
1087 lv_application_basis := cur_rec.application_basis;
1088 end if;
1089
1090 /* TDS application amount */
1091 if cur_rec.tds_tax_id is not null then
1092
1093 r_ja_in_tax_codes := null;
1094 open c_ja_in_tax_codes(cur_rec.tds_tax_id);
1095 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
1096 close c_ja_in_tax_codes;
1097
1098 /* Bug 5722028. Addd by CSahoo
1099 * Need to round the value as per the setup.
1100 */
1101 ln_tds_tmp_amt := 0;
1102 if r_gl_sets_of_books.currency_code = p_invoice_currency_code then
1103 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1104 * (r_ja_in_tax_codes.tax_rate/100),
1105 jai_ap_tds_generation_pkg.g_inr_currency_rounding);
1106 else
1107 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1108 * (r_ja_in_tax_codes.tax_rate/100),
1109 jai_ap_tds_generation_pkg.g_fcy_currency_rounding);
1110 end if;
1111 IF trunc(p_creation_date) >=
1112 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
1113 ln_tds_tmp_amt := jai_ap_tds_generation_pkg.get_rnded_value(ln_tds_tmp_amt);
1114 END IF;
1115 -- End for bug 5722028
1116
1117
1118 update jai_ap_tds_prepayments
1119 set tds_application_amount = ln_tds_tmp_amt, -- Bug 5722028
1120 calc_tds_appln_amt = cur_rec.application_amount * ln_exchange_rate * (r_ja_in_tax_codes.tax_rate/100)
1121 where tds_prepayment_id = cur_rec.tds_prepayment_id;
1122
1123 end if; /* TDS */
1124
1125 /* WCT application amount */
1126 if cur_rec.wct_tax_id is not null then
1127
1128 r_ja_in_tax_codes := null;
1129 open c_ja_in_tax_codes(cur_rec.wct_tax_id);
1130 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
1131 close c_ja_in_tax_codes;
1132
1133 /* Bug 5722028. Addd by CSahoo
1134 * Need to round the value as per the setup.
1135 */
1136 ln_tds_tmp_amt := 0;
1137 if r_gl_sets_of_books.currency_code = p_invoice_currency_code then
1138 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1139 * (r_ja_in_tax_codes.tax_rate/100),
1140 jai_ap_tds_generation_pkg.g_inr_currency_rounding);
1141 else
1142 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1143 * (r_ja_in_tax_codes.tax_rate/100),
1144 jai_ap_tds_generation_pkg.g_fcy_currency_rounding);
1145 end if;
1146
1147 /* Bug 7280925. Commented by Lakshmi Gopalsami
1148 * Rounding to 10 is applicable only for TDS.
1149 * WCT and ESSI should be rounded to Re. 1
1150 IF trunc(p_creation_date) >=
1151 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
1152 ln_tds_tmp_amt := jai_ap_tds_generation_pkg.get_rnded_value(ln_tds_tmp_amt);
1153 END IF;
1154 */
1155 -- End for bug 5722028
1156
1157 update jai_ap_tds_prepayments
1158 set wct_application_amount = ln_tds_tmp_amt, -- Bug 5722028
1159 calc_wct_appln_amt = cur_rec.application_amount * ln_exchange_rate * (r_ja_in_tax_codes.tax_rate/100)
1160 where tds_prepayment_id = cur_rec.tds_prepayment_id;
1161
1162 end if; /* WCT */
1163
1164 /* ESSI application amount */
1165 if cur_rec.essi_tax_id is not null then
1166
1167 r_ja_in_tax_codes := null;
1168 open c_ja_in_tax_codes(cur_rec.essi_tax_id);
1169 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
1170 close c_ja_in_tax_codes;
1171
1172 /* Bug 5722028. Addd by Lakshmi Gopalsami
1173 * Need to round the value as per the setup.
1174 */
1175 ln_tds_tmp_amt := 0;
1176 if r_gl_sets_of_books.currency_code = p_invoice_currency_code then
1177 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1178 * (r_ja_in_tax_codes.tax_rate/100),
1179 jai_ap_tds_generation_pkg.g_inr_currency_rounding);
1180 else
1181 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1182 * (r_ja_in_tax_codes.tax_rate/100),
1183 jai_ap_tds_generation_pkg.g_fcy_currency_rounding);
1184 end if;
1185 /* Bug 7280925. Commented by Lakshmi Gopalsami
1186 * Rounding to 10 is applicable only for TDS.
1187 * WCT and ESSI should be rounded to Re. 1
1188 IF trunc(p_creation_date) >=
1189 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
1190 ln_tds_tmp_amt := jai_ap_tds_generation_pkg.get_rnded_value(ln_tds_tmp_amt);
1191 END IF;
1192 */
1193 -- End for bug 5722028
1194
1195 update jai_ap_tds_prepayments
1196 set essi_application_amount = ln_tds_tmp_amt, --Bug 5722028
1197 calc_essi_appln_amt = cur_rec.application_amount * ln_exchange_rate * (r_ja_in_tax_codes.tax_rate/100)
1198 where tds_prepayment_id = cur_rec.tds_prepayment_id;
1199
1200 end if; /* ESSI */
1201
1202 end loop;
1203
1204 open c_get_total_prepayment_tax(p_invoice_id, p_invoice_distribution_id, ln_exchange_rate);
1205 fetch c_get_total_prepayment_tax into r_get_total_prepayment_tax;
1206 close c_get_total_prepayment_tax;
1207
1208 --Call to procedure - get_tds_threshold_slab, added by Sanjikum for Bug#5131075(4718907)
1209 --Store the current Threshold slab and type before PP application
1210 jai_ap_tds_generation_pkg.get_tds_threshold_slab(
1211 p_prepay_distribution_id => p_prepay_distribution_id,
1212 p_threshold_grp_id => ln_temp_threshold_grp_id,
1213 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
1214 p_threshold_slab_id => ln_threshold_slab_id,
1215 p_threshold_type => lv_threshold_type,
1216 p_process_flag => p_process_flag,
1217 p_process_message => p_process_message,
1218 p_codepath => p_codepath);
1219
1220 IF p_process_flag = 'E' THEN
1221 goto exit_from_procedure;
1222 END IF;
1223 --End by Sanjikum for Bug#5131075(4718907)
1224
1225 if r_get_total_prepayment_tax.tds_amount > 0 then
1226
1227 /* Get the group id as per the section of the prepay line
1228 This has to be same as the SI lines as they have to be of the same section */
1229 -- Bug 4754213. Added by Lakshmi Gopalsami
1230 open c_get_prepayment_thgroup(p_prepay_distribution_id,'TDS_SECTION'); --rchandan for bug#4428980
1231 fetch c_get_prepayment_thgroup into ln_threshold_grp_id, ln_prepay_tax_id;
1232 close c_get_prepayment_thgroup;
1233
1234 if ln_threshold_grp_id is null then
1235 p_process_flag := 'E';
1236 P_process_message := 'Threshold group identifier is not found against the prepayment invoice TDS tax, cannot proceed.';
1237 goto exit_from_procedure;
1238 end if;
1239
1240 /* update the threshold with the tds amount that will be impacted because of this application */
1241 jai_ap_tds_generation_pkg.maintain_thhold_grps
1242 (
1243 p_threshold_grp_id => ln_threshold_grp_id,
1244 p_trx_invoice_apply_amount => r_get_total_prepayment_tax.tds_taxable_basis,
1245 p_tds_event => 'PREPAYMENT APPLICATION',
1246 p_invoice_id => p_invoice_id,
1247 p_invoice_distribution_id => p_invoice_distribution_id,
1248 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
1249 -- Bug 5722028. Added by Lakshmi Gopalsami
1250 p_creation_Date => p_creation_date,
1251 p_process_flag => p_process_flag,
1252 P_process_message => p_process_message,
1253 p_codepath => p_codepath
1254 );
1255
1256 --Added by Sanjikum for Bug#5131075(4722011)
1257 IF p_process_flag = 'E' THEN
1258 goto exit_from_procedure;
1259 END IF;
1260
1261 /* Check if reversed tds invoices_need to be generated. */
1262
1263 /* Bug 4522507. Added by Lakshmi Gopalsami
1264 Commented the following cursor
1265
1266
1267 open c_jai_ap_tds_thhold_grps(ln_threshold_grp_id);
1268 fetch c_jai_ap_tds_thhold_grps into ln_current_threshold_slab_id;
1269 close c_jai_ap_tds_thhold_grps;
1270 */
1271
1272 update jai_ap_tds_prepayments
1273 set tds_threshold_grp_id = ln_threshold_grp_id
1274 where invoice_id = p_invoice_id
1275 and invoice_distribution_id_prepay = p_invoice_distribution_id
1276 and tds_applicable_flag = 'Y';
1277
1278 /* Start Bug#4522507. Added by Lakshmi Gopalsami */
1279
1280 /* Reverse invoice needs to be generated only if TDS invoice was generated against the
1281 PP and also the SI */
1282
1283 /* Check if TDS invoice was generated against the PP */
1284 open c_get_if_tds_inv_generated_pp(p_prepay_distribution_id);
1285 fetch c_get_if_tds_inv_generated_pp into ln_threshold_trx_id_prepay;
1286 close c_get_if_tds_inv_generated_pp;
1287
1288 if ln_threshold_trx_id_prepay is not null then
1289
1290 /* TDS invoice was generated against the Prepayment,
1291 check for what amount of the SI, TDS invoice was generated */
1292
1293 open c_get_amt_tds_inv_generated_si(p_invoice_id, p_invoice_distribution_id);
1294 fetch c_get_amt_tds_inv_generated_si into ln_amt_tds_inv_generated_si;
1295 close c_get_amt_tds_inv_generated_si;
1296
1297 if ln_amt_tds_inv_generated_si > 0 then
1298
1299 --if ln_current_threshold_slab_id <> 0 then
1300
1301 /* End Bug#4522507. Commented the above condition */
1302
1303 /* Generate the Retrun invoice for the amount for which TDS was generated against the SI */
1304
1305 /* Get the invoice number against which the tds invoices are to be generated */
1306
1307 /* if application_basis is prepayment it shd be generated against the prepayment
1308 or else against the standard invoice */
1309
1310 if lv_application_basis = 'STANDARD INVOICE' then
1311 /* get the standard invoice number */
1312 open c_ap_invoices_all(p_invoice_distribution_id);
1313 fetch c_ap_invoices_all into lv_invoice_num_prepay_apply;
1314 close c_ap_invoices_all;
1315 else
1316 /* Get the prepayment number */
1317 open c_ap_invoices_all(p_prepay_distribution_id);
1318 fetch c_ap_invoices_all into lv_invoice_num_prepay_apply;
1319 close c_ap_invoices_all;
1320 end if;
1321
1322 jai_ap_tds_generation_pkg.generate_tds_invoices
1323 (
1324 pn_invoice_id => p_invoice_id ,
1325 pn_invoice_distribution_id => p_invoice_distribution_id ,
1326 pv_invoice_num_prepay_apply => lv_invoice_num_prepay_apply ,
1327 pn_taxable_amount => r_get_total_prepayment_tax.tds_taxable_basis ,
1328 /*Bug 4522507. Added by Lakshmi Gopalsami
1329 Commented the below parameter value and added different assignment
1330 pn_tax_amount => r_get_total_prepayment_tax.tds_amount ,*/
1331 pn_tax_amount => ln_amt_tds_inv_generated_si ,
1332 pn_tax_id => ln_prepay_tax_id ,
1333 pd_accounting_date => p_accounting_date ,
1334 pv_tds_event => 'PREPAYMENT APPLICATION' ,
1335 pn_threshold_grp_id => ln_threshold_grp_id ,
1336 pv_tds_invoice_num => lv_invoice_to_tds_num ,
1337 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
1338 pn_threshold_trx_id => ln_threshold_trx_id_tds ,
1339 pd_creation_date => p_creation_date, -- Bug 5722028. Added by CSahoo
1340 p_process_flag => p_process_flag ,
1341 p_process_message => p_process_message
1342 );
1343 if p_process_flag = 'E' then
1344 goto exit_from_procedure;
1345 end if;
1346
1347 /* prepayment apply scenario for backward compatibility*/
1348 update JAI_AP_TDS_INVOICES
1349 set amt_reversed = nvl(amt_reversed, 0) + r_get_total_prepayment_tax.tds_amount,
1350 amt_applied = nvl(amt_applied, 0) + abs(p_prepay_amount)
1351 where invoice_id = p_invoice_id;
1352 /* prepayment apply scenario for backward compatibility*/
1353
1354 if ln_start_threshold_trx_id is null then
1355 ln_start_threshold_trx_id := ln_threshold_trx_id_tds;
1356 end if;
1357
1358 /* Update the threshold group */
1359 jai_ap_tds_generation_pkg.maintain_thhold_grps
1360 (
1361 p_threshold_grp_id => ln_threshold_grp_id,
1362 /* Bug 4522507. Added by Lakshmi Gopalsami
1363 Commented the following and added different assginment
1364 p_trx_tax_paid => (-1 * r_get_total_prepayment_tax.tds_amount), */
1365 p_trx_tax_paid => (-1 * ln_amt_tds_inv_generated_si),
1366 p_tds_event => 'PREPAYMENT APPLICATION',
1367 p_invoice_id => p_invoice_id,
1368 p_invoice_distribution_id => p_invoice_distribution_id,
1369 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
1370 -- Bug 5722028. Added by CSahoo
1371 p_creation_date => p_creation_date,
1372 p_process_flag => p_process_flag,
1373 P_process_message => p_process_message,
1374 p_codepath => p_codepath
1375 );
1376
1377 --Added by Sanjikum for Bug#5131075(4722011)
1378 IF p_process_flag = 'E' THEN
1379 goto exit_from_procedure;
1380 END IF;
1381
1382 /* Update jai_ap_tds_prepayments with threshold_trx_id_apply*/
1383
1384 update jai_ap_tds_prepayments
1385 set tds_threshold_trx_id_apply = ln_threshold_trx_id_tds
1386 where invoice_id = p_invoice_id
1387 and invoice_distribution_id_prepay = p_invoice_distribution_id
1388 and tds_applicable_flag = 'Y';
1389
1390 end if; /* If ln_amt_tds_inv_generated_si > 0 then */
1391
1392 end if; /* if ln_threshold_trx_id_prepay is not null then */
1393
1394 end if; /* TDS */
1395
1396 if r_get_total_prepayment_tax.wct_amount > 0 then
1397
1398 /* get the tax_id */
1399 ln_pp_section_tax_id := null;
1400 open c_get_pp_section_tax_id(p_prepay_distribution_id, 'WCT_SECTION');
1401 fetch c_get_pp_section_tax_id into ln_pp_section_tax_id;
1402 close c_get_pp_section_tax_id;
1403
1404 jai_ap_tds_generation_pkg.generate_tds_invoices
1405 (
1406 pn_invoice_id => p_invoice_id ,
1407 pn_invoice_distribution_id => p_invoice_distribution_id ,
1408 pn_taxable_amount => r_get_total_prepayment_tax.wct_taxable_basis ,
1409 pn_tax_amount => r_get_total_prepayment_tax.wct_amount ,
1410 pn_tax_id => ln_pp_section_tax_id ,
1411 pd_accounting_date => p_accounting_date ,
1412 pv_tds_event => 'PREPAYMENT APPLICATION' ,
1413 pn_threshold_grp_id => null ,
1414 pv_tds_invoice_num => lv_invoice_to_tds_num ,
1415 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
1416 pn_threshold_trx_id => ln_threshold_trx_id_wct ,
1417 pd_creation_date => p_creation_date, -- Bug 5722028. Added by CSahoo
1418 p_process_flag => p_process_flag ,
1419 p_process_message => p_process_message
1420 );
1421
1422 if p_process_flag = 'E' then
1423 goto exit_from_procedure;
1424 end if;
1425
1426 update jai_ap_tds_prepayments
1427 set wct_threshold_trx_id_apply = ln_threshold_trx_id_wct
1428 where invoice_id = p_invoice_id
1429 and invoice_distribution_id_prepay = p_invoice_distribution_id
1430 and wct_applicable_flag = 'Y';
1431
1432 if ln_start_threshold_trx_id is null then
1433 ln_start_threshold_trx_id := ln_threshold_trx_id_wct;
1434 end if;
1435
1436 /* Generate the return invoices */
1437 end if; /* WCT */
1438
1439
1440 if r_get_total_prepayment_tax.essi_amount > 0 then
1441
1442 /* get the tax_id */
1443 ln_pp_section_tax_id := null;
1444 open c_get_pp_section_tax_id(p_prepay_distribution_id, 'ESSI_SECTION');
1445 fetch c_get_pp_section_tax_id into ln_pp_section_tax_id;
1446 close c_get_pp_section_tax_id;
1447
1448 jai_ap_tds_generation_pkg.generate_tds_invoices
1449 (
1450 pn_invoice_id => p_invoice_id ,
1451 pn_invoice_distribution_id => p_invoice_distribution_id ,
1452 pn_taxable_amount => r_get_total_prepayment_tax.essi_taxable_basis ,
1453 pn_tax_amount => r_get_total_prepayment_tax.essi_amount ,
1454 pn_tax_id => ln_pp_section_tax_id ,
1455 pd_accounting_date => p_accounting_date ,
1456 pv_tds_event => 'PREPAYMENT APPLICATION' ,
1457 pn_threshold_grp_id => null ,
1458 pv_tds_invoice_num => lv_invoice_to_tds_num ,
1459 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
1460 pn_threshold_trx_id => ln_threshold_trx_id_essi ,
1461 pd_creation_date => p_creation_date, -- Bug 5722028. Added by CSahoo
1462 p_process_flag => p_process_flag ,
1463 p_process_message => p_process_message
1464 );
1465
1466 if p_process_flag = 'E' then
1467 goto exit_from_procedure;
1468 end if;
1469
1470 update jai_ap_tds_prepayments
1471 set essi_threshold_trx_id_apply = ln_threshold_trx_id_essi
1472 where invoice_id = p_invoice_id
1473 and invoice_distribution_id_prepay = p_invoice_distribution_id
1474 and essi_applicable_flag = 'Y';
1475
1476 if ln_start_threshold_trx_id is null then
1477 ln_start_threshold_trx_id := ln_threshold_trx_id_essi;
1478 end if;
1479
1480 end if; /* ESSI */
1481
1482 --Call to procedure - get_tds_threshold_slab, added by Sanjikum for Bug#5131075(4718907)
1483 --Store the current Threshold slab and type After PP application
1484 jai_ap_tds_generation_pkg.get_tds_threshold_slab(
1485 p_prepay_distribution_id => p_prepay_distribution_id,
1486 p_threshold_grp_id => ln_temp_threshold_grp_id,
1487 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
1488 p_threshold_slab_id => ln_after_threshold_slab_id,
1489 p_threshold_type => lv_after_threshold_type,
1490 p_process_flag => p_process_flag,
1491 p_process_message => p_process_message,
1492 p_codepath => p_codepath);
1493
1494 IF p_process_flag = 'E' THEN
1495 goto exit_from_procedure;
1496 END IF;
1497
1498 --Call below made to check, if there is any threshold rollback processing required
1499 jai_ap_tds_generation_pkg.process_threshold_rollback(
1500 p_invoice_id => p_invoice_id,
1501 p_before_threshold_type => lv_threshold_type,
1502 p_after_threshold_type => lv_after_threshold_type,
1503 p_before_threshold_slab_id => ln_threshold_slab_id,
1504 p_after_threshold_slab_id => ln_after_threshold_slab_id,
1505 p_threshold_grp_id => ln_temp_threshold_grp_id,
1506 p_org_id => p_org_id,
1507 p_accounting_date => p_accounting_date,
1508 p_invoice_distribution_id => p_invoice_distribution_id,
1509 p_prepay_distribution_id => p_prepay_distribution_id,
1510 p_process_flag => p_process_flag,
1511 p_process_message => p_process_message,
1512 p_codepath => p_codepath);
1513
1514 IF p_process_flag = 'E' THEN
1515 goto exit_from_procedure;
1516 END IF;
1517
1518 --End by Sanjikum for Bug#5131075(4718907)
1519
1520 if ln_start_threshold_trx_id is not null then
1521
1522 jai_ap_tds_generation_pkg.import_and_approve
1523 (
1524 p_invoice_id => p_invoice_id,
1525 p_start_thhold_trx_id => ln_start_threshold_trx_id,
1526 p_tds_event => 'PREPAYMENT APPLICATION',
1527 p_process_flag => p_process_flag,
1528 p_process_message => p_process_message
1529 );
1530
1531 end if;
1532
1533 << exit_from_procedure >>
1534 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
1535 return;
1536
1537 exception
1538 when others then
1539 p_process_flag := 'E';
1540 P_process_message := 'jai_ap_tds_prepayemnts_pkg.process_tds_invoices :' || sqlerrm;
1541 return;
1542 end process_tds_invoices;
1543
1544 /***********************************************************************************************/
1545
1546 /* **************************************** process_old_transaction **************************************** */
1547
1548 procedure process_old_transaction
1549 (
1550 p_invoice_id in number,
1551 p_invoice_distribution_id in number,
1552 p_prepay_distribution_id in number,
1553 p_amount in number,
1554 p_last_updated_by in number,
1555 p_last_update_date in date,
1556 p_created_by in number,
1557 p_creation_date in date,
1558 p_org_id in number,
1559 p_process_flag out nocopy varchar2,
1560 p_process_message out nocopy varchar2
1561 )
1562 is
1563
1564 cursor c_tds_count(p_invoice_id number, p_source_attribute varchar2) is
1565 select count(1)
1566 from JAI_AP_TDS_INVOICES
1567 where invoice_id = p_invoice_id
1568 and source_attribute = p_source_attribute;
1569
1570 /* Following cursor definition has been changed to cater for the obsoletion of table ja_in_ap_tds_inv_temp */
1571 cursor c_tds_count_unapp(p_invoice_id number, p_section_type varchar2) IS --rchandan for bug#4428980
1572 select count(1)
1573 from jai_ap_tds_inv_taxes
1574 where invoice_id = p_invoice_id
1575 and section_type = p_section_type; --rchandan for bug#4428980
1576
1577 ln_tds_count_attribute1 number;
1578 ln_tds_count_attribute2 number;
1579 ln_tds_count_attribute3 number;
1580 lb_result boolean;
1581 ln_req_id number;
1582
1583 begin
1584 /* This code is to replace the following three triggers of the old tds regime
1585 1. ja_in_prepay_insert_trg
1586 2. ja_in_prepay_insert_wct_trg
1587 3. ja_in_prepay_insert_wct1_trg
1588 */
1589
1590 open c_tds_count(p_invoice_id, 'ATTRIBUTE1');
1591 fetch c_tds_count into ln_tds_count_attribute1;
1592 close c_tds_count;
1593
1594 if ln_tds_count_attribute1 = 0 then
1595 -- the standard invoice does not have tds attached to it.
1596 -- check if there is TDS record in temp table, this would happen when the invoice is unapproved.
1597 -- Bug 4754213. Added by Lakshmi Gopalsami
1598 open c_tds_count_unapp(p_invoice_id, 'TDS_SECTION'); --rchandan for bug#4428980
1599 fetch c_tds_count_unapp into ln_tds_count_attribute1;
1600 close c_tds_count_unapp;
1601
1602 ln_tds_count_attribute1 := nvl(ln_tds_count_attribute1, 0);
1603
1604 if ln_tds_count_attribute1 = 0 then
1605 goto attribut2_processing;
1606 end if;
1607
1608 end if;
1609
1610
1611 if p_amount < 0 then /* Case of Apply */
1612
1613 lb_result := fnd_request.set_mode(TRUE);
1614 ln_req_id :=
1615 fnd_request.submit_request
1616 (
1617 'JA',
1618 'JAINPREP',
1619 'To Insert Prepayment Distributions',
1620 '',
1621 FALSE,
1622 p_invoice_id,
1623 p_invoice_distribution_id,
1624 abs(p_amount),
1625 p_last_updated_by,
1626 p_last_update_date,
1627 p_created_by ,
1628 p_creation_date,
1629 p_org_id,
1630 p_prepay_distribution_id,
1631 'I',
1632 'ATTRIBUTE1'
1633 );
1634
1635 elsif p_amount > 0 then
1636
1637 lb_result := fnd_request.set_mode(TRUE);
1638 ln_req_id :=
1639 fnd_request.submit_request
1640 (
1641 'JA',
1642 'JAINUNPR',
1643 'To Unapply Prepayment Distributions',
1644 '',
1645 FALSE,
1646 p_invoice_id,
1647 p_last_updated_by,
1648 p_last_update_date,
1649 p_created_by ,
1650 p_creation_date,
1651 p_org_id,
1652 p_prepay_distribution_id,
1653 p_invoice_distribution_id,
1654 'ATTRIBUTE1'
1655 );
1656
1657 end if;
1658
1659 /* Check for WCT tax */
1660 << attribut2_processing >>
1661 open c_tds_count(p_invoice_id, 'ATTRIBUTE2');
1662 fetch c_tds_count into ln_tds_count_attribute2;
1663 close c_tds_count;
1664
1665 if ln_tds_count_attribute2 = 0 then
1666 -- the standard invoice does not have tds attached to it.
1667 -- check if there is TDS record in temp table, this would happen when the invoice is unapproved.
1668 open c_tds_count_unapp(p_invoice_id, 'WCT_SECTION');
1669 fetch c_tds_count_unapp into ln_tds_count_attribute2;
1670 close c_tds_count_unapp;
1671
1672 ln_tds_count_attribute2 := nvl(ln_tds_count_attribute2, 0);
1673
1674 if ln_tds_count_attribute2 = 0 then
1675 goto attribut3_processing;
1676 end if;
1677
1678 end if;
1679
1680
1681 if p_amount < 0 then /* Case of Apply */
1682
1683 lb_result := fnd_request.set_mode(TRUE);
1684 ln_req_id :=
1685 fnd_request.submit_request
1686 (
1687 'JA',
1688 'JAINPREP',
1689 'To Insert Prepayment Distributions',
1690 '',
1691 FALSE,
1692 p_invoice_id,
1693 p_invoice_distribution_id,
1694 abs(p_amount),
1695 p_last_updated_by,
1696 p_last_update_date,
1697 p_created_by ,
1698 p_creation_date,
1699 p_org_id,
1700 p_prepay_distribution_id,
1701 'I',
1702 'ATTRIBUTE2'
1703 );
1704
1705 elsif p_amount > 0 then
1706
1707 lb_result := fnd_request.set_mode(TRUE);
1708 ln_req_id :=
1709 fnd_request.submit_request
1710 (
1711 'JA',
1712 'JAINUNPR',
1713 'To Unapply Prepayment Distributions',
1714 '',
1715 FALSE,
1716 p_invoice_id,
1717 p_last_updated_by,
1718 p_last_update_date,
1719 p_created_by ,
1720 p_creation_date,
1721 p_org_id,
1722 p_prepay_distribution_id,
1723 p_invoice_distribution_id,
1724 'ATTRIBUTE2'
1725 );
1726
1727 end if;
1728
1729 /* Check for ESSI Tax */
1730 << attribut3_processing >>
1731 open c_tds_count(p_invoice_id, 'ATTRIBUTE3');
1732 fetch c_tds_count into ln_tds_count_attribute3;
1733 close c_tds_count;
1734
1735 if ln_tds_count_attribute3 = 0 then
1736 -- the standard invoice does not have tds attached to it.
1737 -- check if there is TDS record in temp table, this would happen when the invoice is unapproved.
1738 open c_tds_count_unapp(p_invoice_id, 'ESSI_SECTION');
1739 fetch c_tds_count_unapp into ln_tds_count_attribute3;
1740 close c_tds_count_unapp;
1741
1742 ln_tds_count_attribute3 := nvl(ln_tds_count_attribute3, 0);
1743
1744 if ln_tds_count_attribute3 = 0 then
1745 goto exit_from_procedure;
1746 end if;
1747
1748 end if;
1749
1750
1751 if p_amount < 0 then /* Case of Apply */
1752
1753 lb_result := fnd_request.set_mode(TRUE);
1754 ln_req_id :=
1755 fnd_request.submit_request
1756 (
1757 'JA',
1758 'JAINPREP',
1759 'To Insert Prepayment Distributions',
1760 '',
1761 FALSE,
1762 p_invoice_id,
1763 p_invoice_distribution_id,
1764 abs(p_amount),
1765 p_last_updated_by,
1766 p_last_update_date,
1767 p_created_by ,
1768 p_creation_date,
1769 p_org_id,
1770 p_prepay_distribution_id,
1771 'I',
1772 'ATTRIBUTE3'
1773 );
1774
1775 elsif p_amount > 0 then
1776
1777 lb_result := fnd_request.set_mode(TRUE);
1778 ln_req_id :=
1779 fnd_request.submit_request
1780 (
1781 'JA',
1782 'JAINUNPR',
1783 'To Unapply Prepayment Distributions',
1784 '',
1785 FALSE,
1786 p_invoice_id,
1787 p_last_updated_by,
1788 p_last_update_date,
1789 p_created_by ,
1790 p_creation_date,
1791 p_org_id,
1792 p_prepay_distribution_id,
1793 p_invoice_distribution_id,
1794 'ATTRIBUTE3'
1795 );
1796
1797 end if;
1798
1799 << exit_from_procedure >>
1800 return;
1801
1802 exception
1803 when others then
1804 p_process_flag := 'E';
1805 P_process_message := 'jai_ap_tds_prepayemnts_pkg.process_old_transaction :' || sqlerrm;
1806 return;
1807 end process_old_transaction;
1808
1809 /* **************************************** process_old_transaction **************************************** */
1810
1811 end jai_ap_tds_prepayments_pkg;