[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_TDS_PROCESSING_PKG
Source
1 package body jai_ap_tds_processing_pkg as
2 /* $Header: jai_ap_tds_prc.plb 120.11.12010000.3 2008/11/24 09:37:20 mbremkum ship $ */
3 /* ----------------------------------------------------------------------------
4 FILENAME : jai_ap_tds_prc.plb
5
6 Created By : Aparajita
7
8 Created Date : 21-jul-2005
9
10 Bug :
11
12 Purpose : Revamp of TDS certificate and eTDS reporting.
13
14 Called from : Concurrents,
15 JAIATDSP - India - Process TDS Payments
16 JAIATDSC - India - Generate TDS Certificates
17
18 CHANGE HISTORY:
19 -------------------------------------------------------------------------------
20 S.No Date Author and Details
21 -------------------------------------------------------------------------------
22 1. 21/7/2005 Created by Aparajita for bug#4448293. Version#115.0.
23
24 Cleanup of TDS certificate and eTDS reporting.
25
26 2. 25/10/2005 Harshita for Bug # 4643633/4640996, File Version 115.2
27 Issue :
28 In case of an insert into jai_ap_tds_payments for invoices created
29 prior to TDS clean up, vendor_id and vendor_site_id are passed as null.
30
31 Fix :
32 Called cursor c_ap_invoices_all to generate the vendor_id and vendor_site_id.
33 Inserted these values into the jai_ap_tds_payments table.
34
35 Dependency due to this Bug :
36 Yes.
37
38 3. 26/10/2005 Harshita for Bug 4692310/4640996, File Version 115.4
39 Issue :
40 In the cursors c_process_old_tds_payments, c_process_tds_payments, c_tds_invoice_paid_by_prepay,
41 and during deletion from jai_ap_tds_payments during regeneration,
42 The join < jiaot.organization_id = hou.legal_entity_id > is failing and the
43 Fix :
44 Suggested code change is as follows ..
45 to_char(jiaot.organization_id) = hou.DEFAULT_LEGAL_CONTEXT_ID
46
47 Dependency due to this Bug :
48 Yes.
49
50 4. 26/06/2006 Sanjikum for Bug#5219225, File version 115.5
51 1) Changes are done in procedure - process_tds_payments. Here changed the fnd log text at one place
52
53 5. 26/09/2006 rchandan for bug#4742259, File Version 115.7
54 Purpose: Impact due to TCS solution.
55 Fix : A new column by name regime_code is added in jai_ap_tds_certificate_nums
56 so that the same table can be used for TCS. Changes are made in this
57 package accordingly
58 6. 25/1/2007 CSahoo for BUG#5631784, File Version 120.1
59 Forward Porting of BUG#4742259
60 A new column by name regime_code is added in jai_ap_tds_certificate_nums
61 so that the same table can be used for TCS. Changes are made in this
62 package accordingly
63 7. 29/03/2007 bduvarag for bug#5647725,File version 120.2
64 Forward porting the changes done in 11i bug#5647215
65
66 8.14-may-07 kunkumar made changes for Budget and ST by IO and Build issues resolved8.14-may-07 kunkumar made changes for Budget and ST by IO and Build issues resolved8.14-may-07
67
68 9. 12-06-2007 sacsethi for bug 6119195 file version 120.6
69
70 R12RUP03-ST1: INDIA - PROCESS TDS PAYMENTS GIVES ERROR MESSAGE WHILE SUBMITTING
71
72 Probelem - After execution of Concurrent India TDS Payments , some concurrent execution
73 error was coming - FDPSTP failed due to ORA-01861: literal does not match format string
74
75 Solution - This problem was due to procedure process_tds_payments , Argument pd_tds_payment_from_date ,
76 pd_tds_payment_to_date parameter was of date type , whcih we made it as varchar2 and
77 create two variable with name ld_tds_payment_to_date ,ld_tds_payment_from_date
78
79 replae all pd_tds_payment_from_date , pd_tds_payment_to_date with
80 ld_tds_payment_from_date , ld_tds_payment_to_date with
81
82 10. 14-JUN-2007 Bgowrava for Bug#6129650, File Version 120.7
83 Removed the cursor c_hr_operating_units. changed the parameter of the cursor c_ja_in_tds_year_info
84 from r_hr_operating_units.default_legal_context_id to cur_ou.operating_unit_id.
85 Also removed the union codes in the cursors c_process_old_tds_payments, c_tds_invoice_paid_by_prepay,
86 c_process_tds_payments
87
88 11. 18-jan-2008 ssumaith - bug#6761239
89 prepayment applied to tds invoices was snot showing in the TDS
90 certificates report.
91
92 12. 21-FEB-2008 Changes done by nprashar for Bug # 6774129. Added a condition in cursor c_tds_invoice_paid_by_prepay,in order to avoid the problem of
93 TDS CERTIFICATE NOT GETTING GENERATED FOR PARTIAL PREPAYMENTS.
94 13. 7-March-2008. Changes by nprashar for Bug # 6774129. Change in cursor c_group_for_no_certificate, along with cursor
95 c_group_for_certificate.
96 14. 6-june-2008 Changes by nprashar for bug # 6195566. Forward port 11i bug # 6124751.
97
98 15. 20-Oct-2008 Bgowrava for Bug 6069891. File Version 120.7.12000000.8, 120.11.12010000.2
99 Created cursor c_tds_multiple_payments and
100 its related variables. Implemented logic for multiple
101 payments for single TDS invoice in procedure
102 process_tds_payments.
103
104 Future Dependencies For the release Of this Object:-
105 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
106 A datamodel change )
107
108 ----------------------------------------------------------------------------------------------------------------------------------------------------
109 Current Version Current Bug Dependent Files Version Author Date Remarks
110 Of File On Bug/Patchset Dependent On
111 ----------------------------------------------------------------------------------------------------------------------------------------------------
112
113 115.2,115.4 4640996 4601658 Pls refer BCT for the list
114 of dependent files.
115
116 ---------------------------------------------------------------------------- */
117
118
119 ---------------------------------------------------------------------------- */
120
121 /* ******************************** process_tds_payments ******************************************* */
122 procedure process_tds_payments
123 (
124 errbuf out nocopy varchar2 ,
125 retcode out nocopy varchar2 ,
126 pd_tds_payment_from_date in varchar2 ,
127 pd_tds_payment_to_date in varchar2 ,
128 pv_org_tan_num in varchar2 ,
129 p_section_type in varchar2,/*bduvarag for Bug#5647725*/
130 pv_tds_section in varchar2 default null ,
131 pn_tds_authority_id in number default null ,
132 pn_tds_authority_site_id in number default null ,
133 pn_vendor_id in number default null ,
134 pn_vendor_site_id in number default null ,
135 pv_regenerate_flag in varchar2 default 'N'
136 )
137 is
138
139 ld_tds_payment_from_date date ; --Date 12-jun-2007 sacsethi for bug 6119195
140 ld_tds_payment_to_date date; --Date 12-jun-2007 sacsethi for bug 6119195
141 lv_sts_lookup_code_argument1 constant varchar2(10) := 'VOIDED' ;
142 lv_sts_lookup_code_argument2 constant varchar2(20) := 'STOP INITIATED' ; --Date 12-jun-2007 sacsethi for bug 6119195 Length Increases
143 lv_payment_status_flag constant varchar2(1) := 'Y' ;
144 lv_pv_regenerate_flag constant varchar2(1) := 'Y' ;
145 lv_source_attribute constant varchar2(12) := 'ATTRIBUTE1';
146 lv_attribute_category constant varchar2(30) := 'India Original Invoice for TDS';
147 lv_source constant varchar2(10):= 'INDIA TDS' ; /* 6761239 */
148 lv_line_type_lookup_code constant varchar2(6) := 'PREPAY' ;
149 lv_tds_event constant varchar2(25) := 'PREPAYMENT APPLICATION' ; --Date 12-jun-2007 sacsethi for bug 6119195 Length Increases
150
151
152 cursor c_process_tds_payments
153 (
154 pd_tds_payment_from_date date ,
155 pd_tds_payment_to_date date ,
156 pv_org_tan_num varchar2 ,
157 pv_tds_section varchar2 ,
158 pn_tds_authority_id number ,
159 pn_tds_authority_site_id number ,
160 pn_vendor_id number ,
161 pn_vendor_site_id number
162 )
163 is
164 select
165 aca.org_id org_id ,
166 aca.check_id check_id ,
167 aca.check_number check_number ,
168 aca.check_date check_date ,
169 aca.amount check_amount ,
170 aipa.invoice_payment_id invoice_payment_id ,
171 aipa.invoice_id invoice_id ,
172 aia.invoice_num invoice_num ,
173 aia.invoice_date invoice_date ,
174 aipa.amount payment_amount ,
175 jitc.section_code section_code ,
176 jattt.tax_id tax_id ,
177 jattt.tax_rate tax_rate ,
178 jattt.threshold_trx_id threshold_trx_id ,
179 jattt.invoice_id parent_invoice_id ,
180 jattt.tds_event tds_event ,
181 jattt.taxable_amount taxable_basis ,
182 jattt.invoice_to_tds_authority_amt tax_amount ,
183 jattt.tds_authority_vendor_id tax_authority_id ,
184 jattt.tds_authority_vendor_site_id tax_authority_site_id ,
185 jattt.vendor_id vendor_id ,
186 jattt.vendor_site_id vendor_site_id
187 from
188 ap_checks_all aca ,
189 ap_invoice_payments_all aipa ,
190 ap_invoices_all aia ,
191 jai_ap_tds_thhold_trxs jattt,
192 JAI_CMN_TAXES_ALL jitc
193 where
194 aca.check_id = aipa.check_id
195 and aipa.invoice_id = jattt.invoice_to_tds_authority_id
196 and aipa.invoice_id = aia.invoice_id
197 and jattt.tax_id = jitc.tax_id
198 and aia.invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
199 and aca.status_lookup_code NOT IN (lv_sts_lookup_code_argument1, lv_sts_lookup_code_argument2)
200 and ( (aia.payment_status_flag = lv_payment_status_flag)
201 or
202 ( nvl( aia.invoice_amount, 0 ) = nvl(aia.amount_paid, 0 ) )
203 )
204 and aca.org_id in
205 (
206 select organization_id org_id
207 from JAI_AP_TDS_ORG_TAN_V
208 where org_tan_num = pv_org_tan_num
209 --Removed the union code by Bgowrava for Bug#6129650
210 )
211 and jattt.tds_authority_vendor_id = nvl(pn_tds_authority_id, jattt.tds_authority_vendor_id)
212 and jattt.tds_authority_vendor_site_id = nvl(pn_tds_authority_site_id, jattt.tds_authority_vendor_site_id)
213 and jattt.vendor_id = nvl(pn_vendor_id, jattt.vendor_id)
214 and jattt.vendor_site_id = nvl(pn_vendor_site_id, jattt.vendor_site_id)
215 and jitc.section_type = p_section_type -- 5647725, 6109941 brathod
216 and nvl(jitc.section_code,'XYZ') = nvl(pv_tds_section,nvl(jitc.section_code,'XYZ')) /*bduvarag for Bug#5647725*/
217 and nvl(jitc.section_code,'XYZ') = nvl(pv_tds_section, section_code)
218 /*bduvarag for Bug#5647725*/
219 and not exists (
220 select '1'
221 from JAI_AP_TDS_INV_PAYMENTS
222 where check_id = aca.check_id
223 and vendor_id = jattt.vendor_id /*Added by nprashar for bug # 6195566*/
224 and invoice_id = aipa.invoice_id /*Added by nprashar for bug # 6195566*/
225 and tds_tax_id in /*bduvarag for Bug#5647725*/
226 (
227 select tax_id from JAI_CMN_TAXES_ALL where tax_type = 'TDS'
228 and section_type = p_section_type)
229 )
230 ;
231
232 cursor c_process_old_tds_payments
233 (
234 pd_tds_payment_from_date date ,
235 pd_tds_payment_to_date date ,
236 pv_org_tan_num varchar2 ,
237 pn_tds_authority_id number ,
238 pn_tds_authority_site_id number
239 )
240 is
241 select
242 aca.org_id org_id ,
243 aca.check_id check_id ,
244 aca.check_number check_number ,
245 aca.amount check_amount ,
246 aca.check_date check_date ,
247 aipa.invoice_payment_id invoice_payment_id ,
248 aipa.amount payment_amount ,
249 aia.invoice_id invoice_id ,
250 aia.invoice_num invoice_num ,
251 aia.invoice_date invoice_date ,
255 nvl(aia.attribute_category,
252 aia.invoice_amount tax_amount ,
253 aia.vendor_id tax_authority_id ,
254 aia.vendor_site_id tax_authority_site_id ,
256 lv_attribute_category) context ,
257 aia.attribute1 parent_invoice_id
258 from
259 ap_checks_all aca,
260 ap_invoice_payments_all aipa,
261 ap_invoices_all aia
262 where aca.check_id = aipa.check_id
263 and aipa.invoice_id = aia.invoice_id
264 and aia.source = lv_source
265 and aia.invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
266 and aca.status_lookup_code NOT IN (lv_sts_lookup_code_argument1, lv_sts_lookup_code_argument2)
267 and ( (aia.payment_status_flag = lv_payment_status_flag)
268 or
269 ( nvl( aia.invoice_amount, 0 ) = nvl(aia.amount_paid, 0 ) )
270 )
271 and aia.vendor_id = nvl(pn_tds_authority_id, aia.vendor_id)
272 and aia.vendor_site_id = nvl(pn_tds_authority_site_id, aia.vendor_site_id)
273 /*Added by nprashar for bug # 6195566*/
274 and EXISTS ( SELECT 'Y'
275 FROM po_vendors pv
276 WHERE pv.vendor_id = aia.vendor_id
277 AND pv.vendor_type_lookup_code = 'INDIA TDS AUTHORITY'
278 )
279 and aca.org_id in
280 (
281 select organization_id org_id
282 from JAI_AP_TDS_ORG_TAN_V
283 where org_tan_num = pv_org_tan_num
284 --Removed the union code by Bgowrava for Bug#6129650
285 )
286 and not exists (
287 select '1'
288 from JAI_AP_TDS_INV_PAYMENTS
289 where invoice_id = aia.invoice_id
290 )
291 and not exists (
292 SELECT 1
293 FROM jai_ap_tds_thhold_trxs
294 WHERE invoice_to_tds_authority_id = aia.invoice_id
295 )/*bduvarag for Bug#5647725*/
296
297 ;
298
299 cursor c_ap_invoices_all(pn_invoice_id number) is
300 select
301 vendor_id,
302 vendor_site_id,
303 cancelled_date
304 from
305 ap_invoices_all
306 where invoice_id = pn_invoice_id;
307
308 cursor c_JAI_AP_TDS_INVOICES(pn_parent_invoice_id number, pv_tds_invoice_num varchar2) is
309 select
310 invoice_id parent_invoice_id ,
311 invoice_amount taxable_basis ,
312 tds_tax_id tds_tax_id ,
313 tds_section tds_section ,
314 tds_tax_rate tds_tax_rate ,
315 tds_amount tax_amount
316 from
317 JAI_AP_TDS_INVOICES
318 where invoice_id = nvl(pn_parent_invoice_id, invoice_id)
319 and tds_invoice_num = pv_tds_invoice_num
320 and source_attribute = lv_source_attribute;
321
322 /* identifies parent on basis of invoice number */
323 cursor c_JAI_AP_TDS_INVOICES_1(pv_tds_invoice_num varchar2) is
324 select
325 invoice_id parent_invoice_id ,
326 invoice_amount taxable_basis ,
327 tds_tax_id tds_tax_id ,
328 tds_section tds_section ,
329 tds_tax_rate tds_tax_rate ,
330 tds_amount tax_amount
331 from
332 JAI_AP_TDS_INVOICES
333 where tds_invoice_num = pv_tds_invoice_num
334 and source_attribute = lv_source_attribute;
335
336
337
338 cursor c_get_section_if_one(pn_invoice_id number) is
339 select jiati_1.tds_section
340 from JAI_AP_TDS_INVOICES jiati_1
341 where jiati_1.invoice_id = pn_invoice_id
342 and source_attribute = lv_source_attribute
343 and not exists
344 (
345 select '1'
346 from JAI_AP_TDS_INVOICES jiati_2
347 where jiati_1.rowid <> jiati_2.rowid
348 and source_attribute = lv_source_attribute
349 and jiati_1.invoice_id = jiati_2.invoice_id
350 and jiati_1.tds_section <> jiati_2.tds_section
351 );
352
353
354 cursor c_get_tax_if_one(pn_invoice_id number) is
355 select
356 jiati_1.tds_tax_id ,
357 jiati_1.tds_tax_rate
358 from JAI_AP_TDS_INVOICES jiati_1
359 where jiati_1.invoice_id = pn_invoice_id
360 and source_attribute = lv_source_attribute
361 and not exists
362 (
363 select '1'
364 from JAI_AP_TDS_INVOICES jiati_2
365 where jiati_1.rowid <> jiati_2.rowid
366 and source_attribute = lv_source_attribute
367 and jiati_1.invoice_id = jiati_2.invoice_id
368 and jiati_1.tds_tax_id <> jiati_2.tds_tax_id
372 cursor c_tds_invoice_paid_by_prepay
369 );
370
371
373 (
374 pd_tds_payment_from_date date,
375 pd_tds_payment_to_date date,
376 pv_org_tan_num varchar2,
377 pn_tds_authority_id number,
378 pn_tds_authority_site_id number
379 )
380 is
381 select
382 aia.org_id org_id ,
383 aia.invoice_id invoice_id ,
384 aia.invoice_num invoice_num ,
385 aia.invoice_date invoice_date ,
386 aia.invoice_amount tax_amount ,
387 aia.vendor_id tax_authority_id ,
388 aia.vendor_site_id tax_authority_site_id ,
389 nvl(aia.attribute_category,
390 lv_attribute_category) context ,
391 aia.attribute1 parent_invoice_id ,
392 aida_prepayment.invoice_id prepay_invoice_id ,
393 -1 * sum(aida.amount) prepaid_amount
394 from
395 ap_invoices_all aia,
396 ap_invoice_distributions_all aida,
397 ap_invoice_distributions_all aida_prepayment
398 where aia.invoice_id = aida.invoice_id
399 and aida.prepay_distribution_id = aida_prepayment.invoice_distribution_id
400 and aida.line_type_lookup_code = lv_line_type_lookup_code
401 and aia.source = lv_source
402 and aia.invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
403 and ( (aia.payment_status_flag = lv_payment_status_flag)
404 or
405 ( nvl( aia.invoice_amount, 0 ) = nvl(aia.amount_paid, 0 ) )
406 )
407 and aia.vendor_id = nvl(pn_tds_authority_id, aia.vendor_id)
408 and aia.vendor_site_id = nvl(pn_tds_authority_site_id, aia.vendor_site_id)
409 and aia.org_id in
410 (
411 select organization_id org_id
412 from JAI_AP_TDS_ORG_TAN_V
413 where org_tan_num = pv_org_tan_num
414 --Removed the union code by Bgowrava for Bug#6129650
415 )
416 and not exists (
417 select '1'
418 from JAI_AP_TDS_INV_PAYMENTS jatip
419 where jatip.invoice_id = aia.invoice_id
420 and jatip.prepay_invoice_id = aida_prepayment.invoice_id ) --Added by nprashar for Bug # 6774129
421 having sum(aida.amount) <> 0 -- Added by nprashar for Bug # 6774129
422 group by
423 aia.org_id ,
424 aia.invoice_id ,
425 aia.invoice_num ,
426 aia.invoice_date ,
427 aia.invoice_amount ,
428 aia.vendor_id ,
429 aia.vendor_site_id ,
430 nvl(aia.attribute_category, lv_attribute_category) ,
431 aia.attribute1 ,
432 aida_prepayment.invoice_id
433 ;
434
435 cursor c_jai_ap_tds_thhold_trxs(pn_invoice_to_tds_authority_id number) is
436 select
437 jatt.threshold_trx_id,
438 jatt.invoice_id,
439 jatc.section_code tds_section,
440 jatt.tax_id,
441 jatt.tax_rate,
442 jatt.taxable_amount,
443 jatt.tax_amount,
444 jatt.vendor_id,
445 jatt.vendor_site_id
446 from
447 jai_ap_tds_thhold_trxs jatt,
448 JAI_CMN_TAXES_ALL jatc
449 where
450 jatt.invoice_to_tds_authority_id = pn_invoice_to_tds_authority_id
451 and jatc.tax_id = jatt.tax_id
452 and jatc.section_type = p_section_type /*bduvarag for Bug#5647725*/ ;
453
454 cursor c_get_payment_details(pn_invoice_id number) is
455 select
456 aca.check_id check_id,
457 aca.check_date check_date,
458 aca.amount check_amount,
459 aipa.invoice_payment_id invoice_payment_id
460 from
461 ap_checks_all aca,
462 ap_invoice_payments_all aipa
463 where aca.check_id = aipa.check_id
464 and aipa.invoice_id = pn_invoice_id;
465
466 cursor c_get_total_tax_basis ( cp_invoice_id number) is /*Added by nprashar for Bug # 6774129*/
467 select sum(nvl(taxable_basis,0))
468 from jai_ap_tds_inv_payments
469 where invoice_id = cp_invoice_id;
470
471 /* START, Bgowrava for Bug#6069891*/
472
473 CURSOR c_tds_multiple_payments IS
474 SELECT jatp.*
475 FROM jai_ap_tds_inv_payments jatp
476 WHERE (jatp.invoice_id , jatp.taxable_basis,
477 jatp.tax_amount, jatp.tds_tax_id ) IN
478 (SELECT invoice_id, taxable_basis, tax_amount , tds_tax_id
479 FROM jai_ap_tds_inv_payments
480 GROUP BY invoice_id, taxable_basis, tax_amount , tds_tax_id
481 having count(*) > 1
482 )
486 WHERE ac.check_id = jatp.check_id
483 AND jatp.check_id NOT IN /* Filter out all voided and stop initiated checks*/
484 (SELECT check_id
485 FROM ap_checks_all ac
487 AND status_lookup_code in ('VOIDED', 'STOP INITIATED')
488 )
489 AND TRUNC(jatp.creation_date) = TRUNC (sysdate)
490 AND jatp.form16_hdr_id IS NULL /*Pick up payments for which certificates are not generated */
491 ORDER BY tds_payment_id DESC ;
492
493 r_ap_tds_payments c_tds_multiple_payments%ROWTYPE ;
494 TYPE get_tds_inv_details IS RECORD
495 ( tds_payment_id NUMBER ,
496 taxable_basis NUMBER ,
497 invoice_id NUMBER
498 );
499 TYPE get_tds_inv_details_tab IS TABLE OF get_tds_inv_details
500 INDEX BY BINARY_INTEGER ;
501 r_get_tds_inv_details get_tds_inv_details_tab;
502 tab_index NUMBER;
503 ln_temp_invoice_id NUMBER ;
504 /* END, Bgowrava for Bug#6069891*/
505
506 ln_program_id number;
507 ln_program_login_id number;
508 ln_program_application_id number;
509 ln_request_id number;
510 ln_user_id number(15);
511 ln_last_update_login number(15);
512 ln_taxable_basis number;
513 lv_parent_invoice_cancel_flag varchar2(1);
514 ln_parent_invoice_id number(15);
515 lv_section_code varchar2(30);
516 ln_tax_id number(15);
517 ln_tax_rate number;
518 ln_tax_amount number;
519 ln_vendor_id number(15);
520 ln_vendor_site_id number(15);
521
522
523 r_ap_invoices_all c_ap_invoices_all%rowtype;
524 r_JAI_AP_TDS_INVOICES c_JAI_AP_TDS_INVOICES%rowtype;
525 ln_record_count number;
526 ln_threshold_trx_id number;
527 r_get_payment_details c_get_payment_details%rowtype;
528
529 -- Bug 6774129. Added by Lakshmi Gopalsami
530 -- Observation as part of QA.
531 ln_inv_tax_basis NUMBER;
532 begin
533
534
535 /* Get the statis fnd values for populating into the table */
536 Fnd_File.put_line(Fnd_File.LOG, '** Start of procedure jai_ap_tds_processing_pkg.process_tds_payments **');
537 ln_record_count := 0;
538 ln_user_id := fnd_global.user_id;
539 ln_last_update_login := fnd_global.login_id ;
540 ln_program_id := fnd_global.conc_program_id ;
541 ln_program_login_id := fnd_global.conc_login_id ;
542 ln_program_application_id := fnd_global.prog_appl_id ;
543 ln_request_id := fnd_global.conc_request_id ;
544
545
546 ld_tds_payment_from_date := fnd_date.canonical_to_date(pd_tds_payment_from_date);--Date 12-jun-2007 sacsethi for bug 6119195
547 ld_tds_payment_to_date := fnd_date.canonical_to_date(pd_tds_payment_to_date);--Date 12-jun-2007 sacsethi for bug 6119195
548 /* Check regenerate option */
549 if pv_regenerate_flag = lv_pv_regenerate_flag then
550
551 /* Flush the check records that have been processed earlier but are not paid */
552 Fnd_File.put_line(Fnd_File.LOG, ' Flushing the data as regenration option is set to Yes');
553 delete JAI_AP_TDS_INV_PAYMENTS
554 where check_id in
555 (
556 select
557 aca.check_id check_id
558 from
559 ap_checks_all aca ,
560 ap_invoice_payments_all aipa ,
561 ap_invoices_all aia ,
562 jai_ap_tds_thhold_trxs jattt,
563 JAI_CMN_TAXES_ALL jitc
564 where
565 aca.check_id = aipa.check_id
566 and aipa.invoice_id = jattt.invoice_to_tds_authority_id
567 and aipa.invoice_id = aia.invoice_id
568 and jattt.tax_id = jitc.tax_id
569 and aia.invoice_date between ld_tds_payment_from_date and ld_tds_payment_to_date
570 and aca.status_lookup_code NOT IN (lv_sts_lookup_code_argument1, lv_sts_lookup_code_argument2)
571 and aca.org_id in
572 (
573 select organization_id org_id
574 from JAI_AP_TDS_ORG_TAN_V
575 where org_tan_num = pv_org_tan_num
576 --Removed the union code by Bgowrava for Bug#6129650
577 )
578 and jattt.tds_authority_vendor_id = nvl(pn_tds_authority_id, jattt.tds_authority_vendor_id)
579 and jattt.tds_authority_vendor_site_id = nvl(pn_tds_authority_site_id, jattt.tds_authority_vendor_site_id)
580 and jattt.vendor_id = nvl(pn_vendor_id, jattt.vendor_id)
581 and jattt.vendor_site_id = nvl(pn_vendor_site_id, jattt.vendor_site_id)
582 and nvl(jitc.section_code,'XYZ') = nvl(pv_tds_section,nvl(jitc.section_code,'XYZ')) /*bduvarag for Bug#5647725*/
583 )
584 and form16_hdr_id is null;
585
586 Fnd_File.put_line(Fnd_File.LOG, ' No of records flushed : ' || to_char(sql%rowcount) );
587
588 end if; /*if pv_regenerate_flag = 'Y' */
589
593 for cur_rec in
590
591 /* Get all payments from ap_checks_all */
592 Fnd_File.put_line(Fnd_File.LOG, 'Start Processing Payment **');
594 c_process_tds_payments
595 (
596 ld_tds_payment_from_date ,
597 ld_tds_payment_to_date ,
598 pv_org_tan_num ,
599 pv_tds_section ,
600 pn_tds_authority_id ,
601 pn_tds_authority_site_id ,
602 pn_vendor_id ,
603 pn_vendor_site_id
604 )
605 loop
606
607 Fnd_File.put_line(Fnd_File.LOG, ' Processing Invoice / Check : ' || cur_rec.invoice_num || ' / ' || cur_rec.check_number );
608
609 ln_taxable_basis := cur_rec.taxable_basis;
610 lv_parent_invoice_cancel_flag := null;
611
612 if ln_taxable_basis is null then
613 ln_taxable_basis := cur_rec.tax_amount * (100/cur_rec.tax_rate);
614 end if;
615
616 if cur_rec.tds_event in (lv_tds_event) then
617 /* For prepayment application, taxable basis should be negative */
618 ln_taxable_basis := -1 * ln_taxable_basis;
619 end if;
620
621 open c_ap_invoices_all(cur_rec.parent_invoice_id);
622 fetch c_ap_invoices_all into r_ap_invoices_all;
623 close c_ap_invoices_all;
624
625 if r_ap_invoices_all.cancelled_date is not null then
626 lv_parent_invoice_cancel_flag := 'Y';
627 end if;
628
629 insert into JAI_AP_TDS_INV_PAYMENTS
630 (
631 tds_payment_id ,
632 check_id ,
633 check_amount ,
634 check_date ,
635 invoice_payment_id ,
636 payment_amount ,
637 invoice_id ,
638 invoice_date ,
639 parent_invoice_id ,
640 parent_invoice_cancel_flag ,
641 threshold_trx_id ,
642 tds_section ,
643 tds_tax_id ,
644 tds_tax_rate ,
645 taxable_basis ,
646 tax_amount ,
647 tax_authority_id ,
648 tax_authority_site_id ,
649 vendor_id ,
650 vendor_site_id ,
651 org_tan_num ,
652 operating_unit_id ,
653 created_by ,
654 creation_date ,
655 last_updated_by ,
656 last_update_date ,
657 last_update_login ,
658 program_id ,
659 program_login_id ,
660 program_application_id ,
661 request_id
662 )
663 values
664 (
665 jai_ap_tds_inv_payments_s.nextval ,
666 cur_rec.check_id ,
667 cur_rec.check_amount ,
668 cur_rec.check_date ,
669 cur_rec.invoice_payment_id ,
670 cur_rec.payment_amount ,
671 cur_rec.invoice_id ,
672 cur_rec.invoice_date ,
673 cur_rec.parent_invoice_id ,
674 lv_parent_invoice_cancel_flag ,
675 cur_rec.threshold_trx_id ,
676 cur_rec.section_code ,
677 cur_rec.tax_id ,
678 cur_rec.tax_rate ,
679 ln_taxable_basis ,
680 cur_rec.tax_amount ,
681 cur_rec.tax_authority_id ,
682 cur_rec.tax_authority_site_id ,
683 cur_rec.vendor_id ,
684 cur_rec.vendor_site_id ,
685 pv_org_tan_num ,
686 cur_rec.org_id ,
687 ln_user_id ,
688 sysdate ,
689 ln_user_id ,
690 sysdate ,
691 ln_last_update_login ,
692 ln_program_id ,
693 ln_program_login_id ,
694 ln_program_application_id ,
695 ln_request_id
696 );
697
698 ln_record_count := ln_record_count + 1;
699 end loop; /* process tds payments */
700
701
702 /* Check for invoices generated prior to TDS threshold patch */
703 --Fnd_File.put_line(Fnd_File.LOG, 'Start Processing Invoices created prior to TDS clean up if any **');
704 --commented the above and added the below by Sanjikum for Bug#5219225
705 Fnd_File.put_line(Fnd_File.LOG, 'Start Processing Invoices created prior to TDS Threshold if any **');
706
707 for cur_rec in
708 c_process_old_tds_payments
709 (
710 ld_tds_payment_from_date ,
711 ld_tds_payment_to_date ,
712 pv_org_tan_num ,
713 pn_tds_authority_id ,
714 pn_tds_authority_site_id
715 )
716 loop
717
721 lv_section_code := null;
718 Fnd_File.put_line(Fnd_File.LOG, ' Processing Invoice / Check : ' || cur_rec.invoice_num || ' / ' || cur_rec.check_number );
719 ln_parent_invoice_id := null;
720 lv_parent_invoice_cancel_flag := null;
722 ln_tax_id := null;
723 ln_tax_rate := null;
724 ln_taxable_basis := null;
725 ln_vendor_id := null;
726 ln_vendor_site_id := null;
727
728 r_ap_invoices_all := null;
729 r_JAI_AP_TDS_INVOICES := null;
730
731 ln_tax_amount := cur_rec.tax_amount;
732
733
734 if cur_rec.context = lv_attribute_category and cur_rec.parent_invoice_id is not null then
735 ln_parent_invoice_id := cur_rec.parent_invoice_id;
736 end if;
737
738 if ln_parent_invoice_id is not null then
739 open c_JAI_AP_TDS_INVOICES(ln_parent_invoice_id, cur_rec.invoice_num);
740 fetch c_JAI_AP_TDS_INVOICES into r_JAI_AP_TDS_INVOICES;
741 close c_JAI_AP_TDS_INVOICES;
742 else
743 /* try n find the parent based on invoice number */
744 open c_JAI_AP_TDS_INVOICES_1(cur_rec.invoice_num);
745 fetch c_JAI_AP_TDS_INVOICES_1 into r_JAI_AP_TDS_INVOICES;
746 close c_JAI_AP_TDS_INVOICES_1;
747 end if;
748
749 if ln_parent_invoice_id is null and r_JAI_AP_TDS_INVOICES.parent_invoice_id is null then
750 /* parent is not found in ap_invoices_all or JAI_AP_TDS_INVOICES,
751 no other details can be found */
752 goto populate_old_invoice_details;
753 end if;
754
755 /* A parent invoice has been traced, check if it passes filtering condition of vendor and site if given */
756 if pn_vendor_id is not null or pn_vendor_site_id is not null then
757 open c_ap_invoices_all( nvl(ln_parent_invoice_id, r_JAI_AP_TDS_INVOICES.parent_invoice_id) );
758 fetch c_ap_invoices_all into r_ap_invoices_all;
759 close c_ap_invoices_all;
760
761 if r_ap_invoices_all.vendor_id <> nvl(pn_vendor_id, r_ap_invoices_all.vendor_id) or
762 r_ap_invoices_all.vendor_site_id <> nvl(pn_vendor_site_id, r_ap_invoices_all.vendor_site_id)
763 then
764 goto continue_with_next_record;
765 end if;
766
767 end if; /* checking parent vendor or site */
768
769
770 if ln_parent_invoice_id is not null and r_JAI_AP_TDS_INVOICES.parent_invoice_id is null then
771 /* parent invoice has been found, but details not captured in JAI_AP_TDS_INVOICES.
772 could be a return invoice, check if only one section was applicable against the parent and populate if so */
773 open c_get_section_if_one(ln_parent_invoice_id);
774 fetch c_get_section_if_one into lv_section_code;
775 close c_get_section_if_one;
776
777 if lv_section_code <> nvl(pv_tds_section, lv_section_code) then
778 goto continue_with_next_record;
779 end if;
780
781 if lv_section_code is not null then
782 open c_get_tax_if_one(ln_parent_invoice_id);
783 fetch c_get_tax_if_one into ln_tax_id, ln_tax_rate;
784 close c_get_tax_if_one;
785
786 ln_taxable_basis := ln_tax_amount * (100/ln_tax_rate);
787 end if;
788
789 elsif r_JAI_AP_TDS_INVOICES.parent_invoice_id is not null then
790 /* A record in ja_in_ap_tds_invoice has been identified */
791
792 if r_JAI_AP_TDS_INVOICES.tds_section <> nvl(pv_tds_section, r_JAI_AP_TDS_INVOICES.tds_section) then
793 goto continue_with_next_record;
794 end if;
795
796 ln_parent_invoice_id := r_JAI_AP_TDS_INVOICES.parent_invoice_id;
797 lv_section_code := r_JAI_AP_TDS_INVOICES.tds_section;
798 ln_tax_id := r_JAI_AP_TDS_INVOICES.tds_tax_id;
799 ln_tax_rate := r_JAI_AP_TDS_INVOICES.tds_tax_rate;
800 ln_taxable_basis := r_JAI_AP_TDS_INVOICES.taxable_basis;
801 ln_tax_amount := r_JAI_AP_TDS_INVOICES.tax_amount;
802
803 end if;
804
805 -- added, Harshita for Bug 4643633
806 open c_ap_invoices_all(ln_parent_invoice_id );
807 fetch c_ap_invoices_all into r_ap_invoices_all;
808 close c_ap_invoices_all;
809 -- ended, Harshita for Bug 4643633
810
811 << populate_old_invoice_details >>
812 insert into JAI_AP_TDS_INV_PAYMENTS
813 (
814 tds_payment_id ,
815 check_id ,
816 check_amount ,
817 check_date ,
818 invoice_payment_id ,
819 payment_amount ,
820 invoice_id ,
821 invoice_date ,
822 parent_invoice_id ,
823 parent_invoice_cancel_flag ,
824 threshold_trx_id ,
825 tds_section ,
826 tds_tax_id ,
827 tds_tax_rate ,
828 taxable_basis ,
829 tax_amount ,
833 vendor_site_id ,
830 tax_authority_id ,
831 tax_authority_site_id ,
832 vendor_id ,
834 org_tan_num ,
835 operating_unit_id ,
836 source ,
837 created_by ,
838 creation_date ,
839 last_updated_by ,
840 last_update_date ,
841 last_update_login ,
842 program_id ,
843 program_login_id ,
844 program_application_id ,
845 request_id
846 )
847 values
848 (
849 jai_ap_tds_inv_payments_s.nextval ,
850 cur_rec.check_id ,
851 cur_rec.check_amount ,
852 cur_rec.check_date ,
853 cur_rec.invoice_payment_id ,
854 cur_rec.payment_amount ,
855 cur_rec.invoice_id ,
856 cur_rec.invoice_date ,
857 ln_parent_invoice_id ,
858 lv_parent_invoice_cancel_flag ,
859 null ,
860 lv_section_code ,
861 ln_tax_id ,
862 ln_tax_rate ,
863 ln_taxable_basis ,
864 ln_tax_amount ,
865 cur_rec.tax_authority_id ,
866 cur_rec.tax_authority_site_id ,
867 r_ap_invoices_all.vendor_id, --ln_vendor_id , Harshita for Bug 4643633
868 r_ap_invoices_all.vendor_site_id , --ln_vendor_site_id , Harshita for Bug 4643633
869 pv_org_tan_num ,
870 cur_rec.org_id ,
871 'Invoice prior to threshold' ,
872 ln_user_id ,
873 sysdate ,
874 ln_user_id ,
875 sysdate ,
876 ln_last_update_login ,
877 ln_program_id ,
878 ln_program_login_id ,
879 ln_program_application_id ,
880 ln_request_id
881 );
882
883 ln_record_count := ln_record_count + 1;
884
885 << continue_with_next_record >>
886 null;
887
888 end loop; /* c_process_old_tds_payments */
889
890 /* Payemnt by Prepayments */
891 Fnd_File.put_line(Fnd_File.LOG, 'Processing Prepayment if any ');
892
893 for cur_rec in
894 c_tds_invoice_paid_by_prepay
895 (
896 ld_tds_payment_from_date ,
897 ld_tds_payment_to_date ,
898 pv_org_tan_num ,
899 pn_tds_authority_id ,
900 pn_tds_authority_site_id
901 )
902 loop
903
904 Fnd_File.put_line(Fnd_File.LOG, ' Processing Invoice / Prepayment invoice id : ' || cur_rec.invoice_num || ' / ' || cur_rec.prepay_invoice_id );
905
906 ln_threshold_trx_id := null;
907 ln_parent_invoice_id := null;
908 lv_parent_invoice_cancel_flag := null;
909 lv_section_code := null;
910 ln_tax_id := null;
911 ln_tax_rate := null;
912 ln_taxable_basis := null;
913 ln_vendor_id := null;
914 ln_vendor_site_id := null;
915
916 r_ap_invoices_all := null;
917 r_JAI_AP_TDS_INVOICES := null;
918
919
920 /* Get payment information against the prepayment */
921 r_get_payment_details := null;
922 open c_get_payment_details(cur_rec.prepay_invoice_id);
923 fetch c_get_payment_details into r_get_payment_details;
924 close c_get_payment_details;
925
926 /* Check if the TDS invoice is created post clean up then get all info from there */
927 open c_jai_ap_tds_thhold_trxs(cur_rec.invoice_id);
928 fetch c_jai_ap_tds_thhold_trxs into
929 ln_threshold_trx_id,
930 ln_parent_invoice_id,
931 lv_section_code ,
932 ln_tax_id,
933 ln_tax_rate,
934 ln_taxable_basis,
935 ln_tax_amount,
936 ln_vendor_id,
937 ln_vendor_site_id;
938 close c_jai_ap_tds_thhold_trxs;
939
940 if ln_threshold_trx_id is not null then
941 goto populate_invoice_details;
942 end if;
943
944
945 ln_tax_amount := cur_rec.tax_amount;
946
947 if cur_rec.context = lv_attribute_category and cur_rec.parent_invoice_id is not null then
948 ln_parent_invoice_id := cur_rec.parent_invoice_id;
949 end if;
950
951 if ln_parent_invoice_id is not null then
952 open c_JAI_AP_TDS_INVOICES(ln_parent_invoice_id, cur_rec.invoice_num);
953 fetch c_JAI_AP_TDS_INVOICES into r_JAI_AP_TDS_INVOICES;
954 close c_JAI_AP_TDS_INVOICES;
955 else
956 /* try n find the parent based on invoice number */
960 end if;
957 open c_JAI_AP_TDS_INVOICES_1(cur_rec.invoice_num);
958 fetch c_JAI_AP_TDS_INVOICES_1 into r_JAI_AP_TDS_INVOICES;
959 close c_JAI_AP_TDS_INVOICES_1;
961
962 if ln_parent_invoice_id is null and r_JAI_AP_TDS_INVOICES.parent_invoice_id is null then
963 /* parent is not found in ap_invoices_all or JAI_AP_TDS_INVOICES,
964 no other details can be found */
965 goto populate_invoice_details;
966 end if;
967
968 /* A parent invoice has been traced, check if it passes filtering condition of vendor and site if given */
969 if pn_vendor_id is not null or pn_vendor_site_id is not null then
970 open c_ap_invoices_all( nvl(ln_parent_invoice_id, r_JAI_AP_TDS_INVOICES.parent_invoice_id) );
971 fetch c_ap_invoices_all into r_ap_invoices_all;
972 close c_ap_invoices_all;
973
974 if r_ap_invoices_all.vendor_id <> nvl(pn_vendor_id, r_ap_invoices_all.vendor_id) or
975 r_ap_invoices_all.vendor_site_id <> nvl(pn_vendor_site_id, r_ap_invoices_all.vendor_site_id)
976 then
977 goto continue_with_next_record;
978 end if;
979
980 end if; /* checking parent vendor or site */
981
982
983 if ln_parent_invoice_id is not null and r_JAI_AP_TDS_INVOICES.parent_invoice_id is null then
984 /* parent invoice has been found, but details not captured in JAI_AP_TDS_INVOICES.
985 could be a return invoice, check if only one section was applicable against the parent and populate if so */
986 open c_get_section_if_one(ln_parent_invoice_id);
987 fetch c_get_section_if_one into lv_section_code;
988 close c_get_section_if_one;
989
990 if lv_section_code <> nvl(pv_tds_section, lv_section_code) then
991 goto continue_with_next_record;
992 end if;
993
994 if lv_section_code is not null then
995 open c_get_tax_if_one(ln_parent_invoice_id);
996 fetch c_get_tax_if_one into ln_tax_id, ln_tax_rate;
997 close c_get_tax_if_one;
998
999 ln_taxable_basis := ln_tax_amount * (100/ln_tax_rate);
1000 end if;
1001
1002 elsif r_JAI_AP_TDS_INVOICES.parent_invoice_id is not null then
1003 /* A record in ja_in_ap_tds_invoice has been identified */
1004
1005 if r_JAI_AP_TDS_INVOICES.tds_section <> nvl(pv_tds_section, r_JAI_AP_TDS_INVOICES.tds_section) then
1006 goto continue_with_next_record;
1007 end if;
1008
1009 ln_parent_invoice_id := r_JAI_AP_TDS_INVOICES.parent_invoice_id;
1010 lv_section_code := r_JAI_AP_TDS_INVOICES.tds_section;
1011 ln_tax_id := r_JAI_AP_TDS_INVOICES.tds_tax_id;
1012 ln_tax_rate := r_JAI_AP_TDS_INVOICES.tds_tax_rate;
1013 ln_taxable_basis := r_JAI_AP_TDS_INVOICES.taxable_basis;
1014 ln_tax_amount := r_JAI_AP_TDS_INVOICES.tax_amount;
1015
1016 end if;
1017
1018 << populate_invoice_details >>
1019 -- bug 6774129. Added by Lakshmi Gopalsami
1020 -- Observation as part of QA.
1021 -- Get the sum of taxable basis for the already existing line
1022 -- so that the difference will be updated for prepay lines.
1023
1024
1025 Open c_get_total_tax_basis(cur_rec.invoice_id);
1026 fetch c_get_total_tax_basis into ln_inv_tax_basis;
1027 Close c_get_total_tax_basis;
1028
1029 ln_tax_amount := cur_rec.prepaid_amount;
1030 ln_taxable_basis := ln_inv_tax_basis - ln_taxable_basis;
1031 -- End for bug 6774129
1032
1033 insert into JAI_AP_TDS_INV_PAYMENTS
1034 (
1035 tds_payment_id ,
1036 check_id ,
1037 check_amount ,
1038 check_date ,
1039 invoice_payment_id ,
1040 prepay_invoice_id ,
1041 payment_amount ,
1042 invoice_id ,
1043 invoice_date ,
1044 parent_invoice_id ,
1045 parent_invoice_cancel_flag ,
1046 threshold_trx_id ,
1047 tds_section ,
1048 tds_tax_id ,
1049 tds_tax_rate ,
1050 taxable_basis ,
1051 tax_amount ,
1052 tax_authority_id ,
1053 tax_authority_site_id ,
1054 vendor_id ,
1055 vendor_site_id ,
1056 org_tan_num ,
1057 operating_unit_id ,
1058 source ,
1059 created_by ,
1060 creation_date ,
1061 last_updated_by ,
1062 last_update_date ,
1063 last_update_login ,
1064 program_id ,
1065 program_login_id ,
1066 program_application_id ,
1067 request_id
1068 )
1069 values
1070 (
1071 jai_ap_tds_inv_payments_s.nextval ,
1072 r_get_payment_details.check_id ,
1073 r_get_payment_details.check_amount ,
1077 cur_rec.prepaid_amount ,
1074 r_get_payment_details.check_date ,
1075 r_get_payment_details.invoice_payment_id ,
1076 cur_rec.prepay_invoice_id ,
1078 cur_rec.invoice_id ,
1079 cur_rec.invoice_date ,
1080 ln_parent_invoice_id ,
1081 lv_parent_invoice_cancel_flag ,
1082 ln_threshold_trx_id ,
1083 lv_section_code ,
1084 ln_tax_id ,
1085 ln_tax_rate ,
1086 ln_taxable_basis ,
1087 ln_tax_amount ,
1088 cur_rec.tax_authority_id ,
1089 cur_rec.tax_authority_site_id ,
1090 ln_vendor_id ,
1091 ln_vendor_site_id ,
1092 pv_org_tan_num ,
1093 cur_rec.org_id ,
1094 'Invoice paid by prepayment' ,
1095 ln_user_id ,
1096 sysdate ,
1097 ln_user_id ,
1098 sysdate ,
1099 ln_last_update_login ,
1100 ln_program_id ,
1101 ln_program_login_id ,
1102 ln_program_application_id ,
1103 ln_request_id
1104 );
1105
1106 ln_record_count := ln_record_count + 1;
1107 -- bug 6774129. Added by Lakshmi Gopalsami
1108 -- Observation as part of QA.
1109 -- this will update the tax amount with the payment amount for
1110 -- all lines which has been paid by check.
1111 update jai_ap_tds_inv_payments
1112 set tax_amount = payment_amount
1113 where invoice_id = cur_rec.invoice_id
1114 and prepay_invoice_id is null
1115 and nvl(source,'ABC') <> 'Invoice paid by prepayment';
1116 << continue_with_next_record >>
1117 null;
1118
1119 end loop; /* c_tds_invoice_paid_by_prepay */
1120
1121 /* START, Bgowrava for Bug#6069891*/
1122 /* Following logic is introduced to handle multiple payments made for a single TDS invoice. */
1123 tab_index := 1; ln_temp_invoice_id := 0;
1124 FOR c_get_multiple_payments IN c_tds_multiple_payments
1125 LOOP
1126 IF ln_temp_invoice_id <> c_get_multiple_payments.invoice_id THEN
1127 r_get_tds_inv_details(tab_index).tds_payment_id := c_get_multiple_payments.tds_payment_id;
1128 r_get_tds_inv_details(tab_index).taxable_basis := c_get_multiple_payments.taxable_basis;
1129 r_get_tds_inv_details(tab_index).invoice_id := c_get_multiple_payments.invoice_id;
1130 tab_index := tab_index + 1;
1131 END IF ;
1132 UPDATE jai_ap_tds_inv_payments jatp
1133 SET jatp.taxable_basis = round(jatp.taxable_basis * jatp.payment_amount / jatp.tax_amount,2)
1134 WHERE jatp.tds_payment_id = c_get_multiple_payments.tds_payment_id ;
1135 UPDATE jai_ap_tds_inv_payments jatp
1136 SET jatp.tax_amount = jatp.payment_amount
1137 WHERE jatp.tds_payment_id = c_get_multiple_payments.tds_payment_id ;
1138 END LOOP ;
1139
1140 /* Round the taxable basis correct if not rounded properly. */
1141 FOR ind IN 1..tab_index - 1
1142 LOOP
1143 UPDATE jai_ap_tds_inv_payments jatp
1144 SET jatp.taxable_basis = jatp.taxable_basis +
1145 ( r_get_tds_inv_details(ind).taxable_basis -
1146 (SELECT sum(jatp1.taxable_basis)
1147 FROM jai_ap_tds_inv_payments jatp1
1148 WHERE jatp1.invoice_id = r_get_tds_inv_details(ind).invoice_id
1149 AND jatp1.check_id NOT IN
1150 (SELECT check_id
1151 FROM ap_checks_all ac
1152 WHERE ac.check_id = jatp.check_id
1153 AND status_lookup_code in ('VOIDED', 'STOP INITIATED')
1154 )
1155 )
1156 )
1157 WHERE jatp.tds_payment_id = r_get_tds_inv_details(ind).tds_payment_id
1158 AND jatp.form16_hdr_id IS NULL ;
1159 END LOOP ;
1160 /* END, Bgowrava for Bug#6069891*/
1161
1162 <<exit_from_procedure>>
1163 Fnd_File.put_line(Fnd_File.LOG, 'No of records inserted into JAI_AP_TDS_INV_PAYMENTS : ' || to_char(ln_record_count));
1164 Fnd_File.put_line(Fnd_File.LOG, '** Successful End of procedure jai_ap_tds_processing_pkg.process_tds_payments **');
1165
1166 return;
1167
1168 exception
1169 when others then
1170 retcode := 2;
1171 errbuf := 'Error from jai_ap_tds_processing_pkg.process_tds_payments : ' || sqlerrm;
1172 Fnd_File.put_line(Fnd_File.LOG, 'Error End of procedure jai_ap_tds_processing_pkg.process_tds_payments : ' || sqlerrm);
1173 Fnd_File.put_line(Fnd_File.LOG, '** Error End of procedure jai_ap_tds_processing_pkg.process_tds_payments **');
1174
1175 end process_tds_payments;
1176 /* ******************************** process_tds_payments ******************************************* */
1177
1178 /* ****************************** process_tds_certificates ***************************************** */
1179 procedure process_tds_certificates
1180 (
1181 errbuf out nocopy varchar2,
1182 retcode out nocopy varchar2,
1183 pd_tds_payment_from_date in varchar2,
1184 pd_tds_payment_to_date in varchar2,
1185 pv_org_tan_num in varchar2,
1189 pn_tds_authority_site_id in number default null,
1186 p_section_type in varchar2,/*bduvarag for Bug#5647725*/
1187 pv_tds_section in varchar2 ,
1188 pn_tds_authority_id in number ,
1190 pn_vendor_id in number default null,
1191 pn_vendor_site_id in number default null
1192 )
1193 is
1194 ld_tds_payment_from_date date ; --Date 12-jun-2007 sacsethi for bug 6119195
1195 ld_tds_payment_to_date date; --Date 12-jun-2007 sacsethi for bug 6119195
1196
1197 cursor c_get_distinct_ou
1198 (
1199 pd_tds_payment_from_date date,
1200 pd_tds_payment_to_date date,
1201 pv_org_tan_num varchar2,
1202 pv_tds_section varchar2,
1203 pn_tds_authority_id number,
1204 pn_tds_authority_site_id number,
1205 pn_vendor_id number,
1206 pn_vendor_site_id number
1207 )
1208 is
1209 select distinct operating_unit_id operating_unit_id
1210 from jai_ap_tds_inv_payments
1211 where parent_invoice_id is not null
1212 and tds_tax_id is not null
1213 and tds_tax_rate is not null
1214 and invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
1215 and form16_hdr_id is null
1216 and nvl(tds_section,'XYZ') = nvl(pv_tds_section,nvl(tds_section,'XYZ')) /*rchandan for bug#4936956. Added nvl on left hand side and nvl within nvl on right side*/
1217 and tax_authority_id = pn_tds_authority_id
1218 and tax_authority_site_id = nvl(pn_tds_authority_site_id, tax_authority_site_id)
1219 and vendor_id = nvl(pn_vendor_id, vendor_id)
1220 and vendor_site_id = nvl(pn_vendor_site_id, vendor_site_id)
1221 and org_tan_num = pv_org_tan_num
1222 and tds_tax_id in ( SELECT tax_id
1223 FROM JAI_CMN_TAXES_ALL
1224 WHERE section_type = p_section_type
1225 );/*bduvarag for Bug#5647725*/
1226
1227
1228 cursor c_get_distinct_invoice_date
1229 (
1230 pd_tds_payment_from_date date,
1231 pd_tds_payment_to_date date,
1232 pv_org_tan_num varchar2,
1233 pv_tds_section varchar2,
1234 pn_tds_authority_id number,
1235 pn_tds_authority_site_id number,
1236 pn_vendor_id number,
1237 pn_vendor_site_id number,
1238 pn_operating_unit_id number
1239 )
1240 is
1241 select distinct invoice_date invoice_date
1242 from jai_ap_tds_inv_payments
1243 where parent_invoice_id is not null
1244 and tds_tax_id is not null
1245 and tds_tax_rate is not null
1246 and invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
1247 and form16_hdr_id is null
1248 and nvl(tds_section,'XYZ') = nvl(pv_tds_section,nvl(tds_section,'XYZ')) /*rchandan for bug#4936956. Added nvl on left hand side and nvl within nvl on right side*/
1249 and tax_authority_id = pn_tds_authority_id
1250 and tax_authority_site_id = nvl(pn_tds_authority_site_id, tax_authority_site_id)
1251 and vendor_id = nvl(pn_vendor_id, vendor_id)
1252 and vendor_site_id = nvl(pn_vendor_site_id, vendor_site_id)
1253 and org_tan_num = pv_org_tan_num
1254 and operating_unit_id = pn_operating_unit_id
1255 and tds_tax_id in ( SELECT tax_id
1256 FROM JAI_CMN_TAXES_ALL
1257 WHERE section_type = p_section_type
1258 );/*bduvarag for Bug#5647725*/
1259 --Commented below by Bgowrava for Bug#6129650
1260 /*
1261 cursor c_hr_operating_units(pn_organization_id number) is
1262 select default_legal_context_id
1263 from hr_operating_units
1264 where organization_id = pn_organization_id;*/
1265
1266 cursor c_ja_in_tds_year_info(pn_legal_entity_id number, pd_invoice_date date) is
1267 select fin_year
1268 from jai_ap_tds_years
1269 where legal_entity_id = pn_legal_entity_id
1270 and pd_invoice_date between start_date and end_date;
1271
1272 cursor c_group_for_no_certificate/*Bug 5647725 start bduvarag*/
1273 (
1274 pd_tds_payment_from_date date,
1275 pd_tds_payment_to_date date,
1276 pv_org_tan_num varchar2,
1277 pv_tds_section varchar2,
1278 pn_tds_authority_id number,
1279 pn_tds_authority_site_id number,
1280 pn_vendor_id number,
1281 pn_vendor_site_id number
1282 )
1283 is
1284 select
1285 fin_year,
1286 org_tan_num,
1287 operating_unit_id,
1288 vendor_id,
1289 vendor_site_id,
1290 --tds_tax_id,
1291 tds_section,
1292 tax_authority_id,
1293 parent_invoice_id
1294 from jai_ap_tds_inv_payments /*Added by nprashar for bug 6774129*/
1295 where parent_invoice_id is not null
1296 and tds_tax_id is not null
1297 and tds_tax_rate is not null
1301 and fin_year is not null
1298 and invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
1299 and form16_hdr_id is null
1300 and tds_section = pv_tds_section
1302 and tax_authority_id = pn_tds_authority_id
1303 and tax_authority_site_id = nvl(pn_tds_authority_site_id, tax_authority_site_id)
1304 and vendor_id = nvl(pn_vendor_id, vendor_id)
1305 and vendor_site_id = nvl(pn_vendor_site_id, vendor_site_id)
1306 and org_tan_num = pv_org_tan_num
1307 and tds_tax_id in ( SELECT tax_id
1308 FROM JAI_CMN_TAXES_ALL
1309 WHERE section_type = p_section_type
1310 )
1311 group by
1312 fin_year,
1313 org_tan_num,
1314 operating_unit_id,
1315 vendor_id,
1316 vendor_site_id,
1317 --tds_tax_id,
1318 tds_section,
1319 tax_authority_id,
1320 parent_invoice_id
1321 having sum(TAX_AMOUNT) = 0;
1322 /*Bug 5647725 end bduvarag*/
1323 cursor c_group_for_certificate
1324 (
1325 pd_tds_payment_from_date date,
1326 pd_tds_payment_to_date date,
1327 pv_org_tan_num varchar2,
1328 pv_tds_section varchar2,
1329 pn_tds_authority_id number,
1330 pn_tds_authority_site_id number,
1331 pn_vendor_id number,
1332 pn_vendor_site_id number
1333 )
1334 is
1335 select distinct
1336 fin_year,
1337 org_tan_num,
1338 operating_unit_id,
1339 vendor_id,
1340 vendor_site_id,
1341 /*tds_tax_id, commented by nprashar for Bug : 6774129*/
1342 tds_section,
1343 tax_authority_id
1344 from jai_ap_tds_inv_payments
1345 where parent_invoice_id is not null
1346 and tds_tax_id is not null
1347 and tds_tax_rate is not null
1348 and invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
1349 and form16_hdr_id is null
1350 and nvl(tds_section,'XYZ') = nvl(pv_tds_section,nvl(tds_section,'XYZ'))/*bduvarag for Bug#5647725*/
1351 and fin_year is not null
1352 and tax_authority_id = pn_tds_authority_id
1353 and tax_authority_site_id = nvl(pn_tds_authority_site_id, tax_authority_site_id)
1354 and vendor_id = nvl(pn_vendor_id, vendor_id)
1355 and vendor_site_id = nvl(pn_vendor_site_id, vendor_site_id)
1356 and org_tan_num = pv_org_tan_num
1357 and tds_tax_id in ( SELECT tax_id
1358 FROM JAI_CMN_TAXES_ALL
1359 WHERE section_type = p_section_type
1360 );/*bduvarag for Bug#5647725*/
1361
1362
1363 cursor c_jai_ap_tds_cert_nums(pv_org_tan_num varchar2, pn_fin_year number, pv_regime_code VARCHAR2)
1364 is
1365 select nvl(certificate_num, 0) + 1
1366 from jai_ap_tds_cert_nums
1367 where org_tan_num = pv_org_tan_num
1368 and fin_yr = pn_fin_year
1369 and regime_code = pv_regime_code/*CSahoo for Bug#5631784*/
1370 ;
1371
1372
1373 cursor c_form16_cert_lines(cp_form16_hdr_id number) is
1374 select rowid row_id, parent_invoice_id, threshold_trx_id
1375 from jai_ap_tds_inv_payments
1376 where form16_hdr_id = cp_form16_hdr_id
1377 order by parent_invoice_id, invoice_id
1378 for update of certificate_line_num;
1379
1380 cursor c_tds_thhold_event(cp_threshold_trx_id number) is
1381 select tds_event
1382 from jai_ap_tds_thhold_trxs
1383 where threshold_trx_id = cp_threshold_trx_id;
1384
1385 cursor c_get_form16_hdr_id is
1386 select jai_ap_tds_f16_hdrs_all_s.nextval from dual;
1387 /*bduvarag for Bug#5647725*/
1388 CURSOR cur_get_tds_section IS
1389 SELECT decode(p_section_type,'TDS_SECTION','TDS','WCT_SECTION','WCT','ESSI_SECTION','ESI')
1390 FROM dual;
1391
1392
1393 ln_cert_line_num NUMBER(15);
1394 ln_prev_parent_invoice_id ap_invoices_all.invoice_id%TYPE;
1395 -- lv_prev_tds_event jai_ap_tds_thhold_trxs.tds_event%TYPE;
1396 /*Bug 5647725 bduvarag*/
1397 lv_tds_event jai_ap_tds_thhold_trxs.tds_event%TYPE;
1398
1399
1400 --r_hr_operating_units c_hr_operating_units%rowtype;
1401 r_ja_in_tds_year_info c_ja_in_tds_year_info%rowtype;
1402 ln_certificate_num jai_ap_tds_cert_nums.certificate_num%type;
1403 ln_form16_hdr_id number;
1404
1405 ln_program_id number;
1406 ln_program_login_id number;
1407 ln_program_application_id number;
1408 ln_request_id number;
1409 ln_user_id number(15);
1410 ln_last_update_login number(15);
1411 ln_certificate_count number;
1412 lv_tds_section VARCHAR2(30);/*bduvarag for Bug#5647725*/
1413
1414 begin
1415
1416 /* Get the statis fnd values for populating into the table */
1417 Fnd_File.put_line(Fnd_File.LOG, '** Start of procedure jai_ap_tds_processing_pkg.process_tds_certificates **');
1418
1419 ln_user_id := fnd_global.user_id ;
1420 ln_last_update_login := fnd_global.login_id ;
1421 ln_program_id := fnd_global.conc_program_id ;
1422 ln_program_login_id := fnd_global.conc_login_id ;
1426 ln_certificate_count := 0 ;
1423 ln_program_application_id := fnd_global.prog_appl_id ;
1424 ln_request_id := fnd_global.conc_request_id ;
1425
1427
1428 ld_tds_payment_from_date := fnd_date.canonical_to_date(pd_tds_payment_from_date);--Date 12-jun-2007 sacsethi for bug 6119195
1429 ld_tds_payment_to_date := fnd_date.canonical_to_date(pd_tds_payment_to_date);--Date 12-jun-2007 sacsethi for bug 6119195
1430
1431 /*bduvarag for Bug#5647725 start*/
1432
1433 OPEN cur_get_tds_section;
1434 FETCH cur_get_tds_section INTO lv_tds_section;
1435 CLOSE cur_get_tds_section;
1436
1437 IF lv_tds_section = 'TDS' and pv_tds_section IS NULL THEN
1438
1439 raise_application_error(-20120,' Section Code is mandatory for TDS Section');
1440
1441 END IF;
1442 /*bduvarag for Bug#5647725 End*/
1443
1444 for cur_ou in
1445 c_get_distinct_ou
1446 (
1447 ld_tds_payment_from_date ,
1448 ld_tds_payment_to_date ,
1449 pv_org_tan_num ,
1450 pv_tds_section ,
1451 pn_tds_authority_id ,
1452 pn_tds_authority_site_id ,
1453 pn_vendor_id ,
1454 pn_vendor_site_id
1455 )
1456 loop
1457
1458
1459 Fnd_File.put_line(Fnd_File.LOG, 'Processing operating unit : ' || cur_ou.operating_unit_id);
1460
1461 --Commented below by Bgowrava for Bug#6129650
1462 /*
1463 open c_hr_operating_units(cur_ou.operating_unit_id);
1464 fetch c_hr_operating_units into r_hr_operating_units;
1465 close c_hr_operating_units;*/
1466
1467 for cur_invoice_date in
1468 c_get_distinct_invoice_date
1469 (
1470 ld_tds_payment_from_date ,
1471 ld_tds_payment_to_date ,
1472 pv_org_tan_num ,
1473 pv_tds_section ,
1474 pn_tds_authority_id ,
1475 pn_tds_authority_site_id ,
1476 pn_vendor_id ,
1477 pn_vendor_site_id ,
1478 cur_ou.operating_unit_id
1479 )
1480 loop
1481 Fnd_File.put_line(Fnd_File.LOG, 'Processing cur_invoice_date : ' || cur_invoice_date.invoice_date);
1482
1483 open c_ja_in_tds_year_info(cur_ou.operating_unit_id, cur_invoice_date.invoice_date); --changed r_hr_operating_units.default_legal_context_id to cur_ou.operating_unit_id for Bug#6129650
1484 fetch c_ja_in_tds_year_info into r_ja_in_tds_year_info;
1485 close c_ja_in_tds_year_info;
1486
1487 Fnd_File.put_line(Fnd_File.LOG, 'Updating ' || r_ja_in_tds_year_info.fin_year);
1488
1489 update jai_ap_tds_inv_payments
1490 set fin_year = r_ja_in_tds_year_info.fin_year
1491 where parent_invoice_id is not null
1492 and tds_tax_id is not null
1493 and tds_tax_rate is not null
1494 and invoice_date between ld_tds_payment_from_date and ld_tds_payment_to_date
1495 and form16_hdr_id is null
1496 and nvl(tds_section,'XYZ') = nvl(pv_tds_section,nvl(tds_section,'XYZ'))/*bduvarag for Bug#5647725*/
1497 and tax_authority_id = pn_tds_authority_id
1498 and tax_authority_site_id = nvl(pn_tds_authority_site_id, tax_authority_site_id)
1499 and vendor_id = nvl(pn_vendor_id, vendor_id)
1500 and vendor_site_id = nvl(pn_vendor_site_id, vendor_site_id)
1501 and org_tan_num = pv_org_tan_num
1502 and operating_unit_id = cur_ou.operating_unit_id
1503 and invoice_date = cur_invoice_date.invoice_date
1504 and tds_tax_id in ( SELECT tax_id
1505 FROM JAI_CMN_TAXES_ALL
1506 WHERE section_type = p_section_type
1507 );/*bduvarag for Bug#5647725*/
1508
1509
1510
1511 Fnd_File.put_line(Fnd_File.LOG, ' No of records updated with Fin year : ' || to_char(sql%rowcount) );
1512
1513 end loop; /*c_get_distinct_invoice_date */
1514
1515
1516 end loop; /* c_get_distinct_ou */
1517
1518 /* Fin year update complete */
1519
1520 FOR cur_rec IN /*Bug 5647725 start bduvarag*/
1521 c_group_for_no_certificate
1522 (
1523 ld_tds_payment_from_date ,
1524 ld_tds_payment_to_date ,
1525 pv_org_tan_num ,
1526 pv_tds_section ,
1527 pn_tds_authority_id ,
1528 pn_tds_authority_site_id ,
1529 pn_vendor_id ,
1530 pn_vendor_site_id
1531 )
1532 LOOP
1533
1534 ln_form16_hdr_id := null;
1535
1536 open c_get_form16_hdr_id;
1537 fetch c_get_form16_hdr_id into ln_form16_hdr_id;
1538 close c_get_form16_hdr_id;
1539
1540 update jai_ap_tds_inv_payments /*changed by nprashar for bug 6774129 */
1541 set form16_hdr_id = -1 * ln_form16_hdr_id
1542 , last_update_date = sysdate
1543 , last_update_login = ln_last_update_login
1544 where parent_invoice_id = cur_rec.parent_invoice_id
1545 --and tds_tax_id = cur_rec.tds_tax_id --tds_tax_id,
1546 and tds_tax_rate is not null
1547 and invoice_date between ld_tds_payment_from_date and ld_tds_payment_to_date
1548 and form16_hdr_id is null
1549 and tds_section = cur_rec.tds_section
1550 and fin_year = cur_rec.fin_year
1551 and tax_authority_id = cur_rec.tax_authority_id
1555 and operating_unit_id = cur_rec.operating_unit_id
1552 and vendor_id = cur_rec.vendor_id
1553 and vendor_site_id = cur_rec.vendor_site_id
1554 and org_tan_num = pv_org_tan_num
1556 and tds_tax_id in ( SELECT tax_id
1557 FROM JAI_CMN_TAXES_ALL
1558 WHERE section_type = p_section_type
1559 );
1560
1561 END LOOP; /*c_group_for_no_certificate*/
1562 /*Bug 5647725 end bduvarag*/
1563 /* Group for TDS Certificates */
1564
1565 Fnd_File.put_line(Fnd_File.LOG, ' Generating Certificates ' );
1566
1567 for cur_rec in
1568 c_group_for_certificate
1569 (
1570 ld_tds_payment_from_date ,
1571 ld_tds_payment_to_date ,
1572 pv_org_tan_num ,
1573 pv_tds_section ,
1574 pn_tds_authority_id ,
1575 pn_tds_authority_site_id ,
1576 pn_vendor_id ,
1577 pn_vendor_site_id
1578 )
1579 loop
1580
1581 /* Get certificate number */
1582 ln_certificate_num := null;
1583 open c_jai_ap_tds_cert_nums(pv_org_tan_num, cur_rec.fin_year, lv_tds_section); /*bduvarag for Bug#5647725*/ /*CSahoo for Bug#5631784*/
1584 fetch c_jai_ap_tds_cert_nums into ln_certificate_num;
1585 close c_jai_ap_tds_cert_nums;
1586
1587 if ln_certificate_num is null then
1588 ln_certificate_num := 1;
1589 end if;
1590
1591 ln_form16_hdr_id := null;
1592
1593 open c_get_form16_hdr_id;
1594 fetch c_get_form16_hdr_id into ln_form16_hdr_id;
1595 close c_get_form16_hdr_id;
1596
1597 update jai_ap_tds_inv_payments
1598 set form16_hdr_id = ln_form16_hdr_id
1599 , certificate_num = ln_certificate_num
1600 , last_update_date = sysdate
1601 , last_update_login = ln_last_update_login
1602 where parent_invoice_id is not null
1603 /*and tds_tax_id = cur_rec.tds_tax_id commented by nprashar for bug # 6774129*/
1604 and tds_tax_rate is not null
1605 and invoice_date between ld_tds_payment_from_date and ld_tds_payment_to_date
1606 and form16_hdr_id is null
1607 and nvl(tds_section,'XYZ') = nvl(cur_rec.tds_section,'XYZ') /*bduvarag for Bug#5647725*/
1608 and fin_year = cur_rec.fin_year
1609 and tax_authority_id = cur_rec.tax_authority_id
1610 and vendor_id = cur_rec.vendor_id
1611 and vendor_site_id = cur_rec.vendor_site_id
1612 and org_tan_num = pv_org_tan_num
1613 and operating_unit_id = cur_rec.operating_unit_id
1614 and tds_tax_id in ( SELECT tax_id
1615 FROM JAI_CMN_TAXES_ALL
1616 WHERE section_type = p_section_type
1617 );/*bduvarag for Bug#5647725*/
1618
1619 if sql%rowcount = 0 then
1620 goto continue_with_next_certificate;
1621 end if;
1622
1623 Fnd_File.put_line(Fnd_File.LOG, 'Certificate Number : ' || ln_certificate_num);
1624 Fnd_File.put_line(Fnd_File.LOG, ' No of Records for the Certificate : ' || to_char(sql%rowcount) );
1625 ln_certificate_count := ln_certificate_count + 1;
1626
1627 if ln_certificate_num = 1 then
1628 Fnd_File.put_line(Fnd_File.LOG, 'Created a certificate record in jai_ap_tds_cert_nums');
1629 insert into jai_ap_tds_cert_nums
1630 (
1631 fin_yr_cert_id ,
1632 regime_code ,
1633 org_tan_num ,
1634 fin_yr ,
1635 certificate_num ,
1636 created_by ,
1637 creation_date ,
1638 last_updated_by ,
1639 last_update_date ,
1640 last_update_login
1641 )
1642 values
1643 (
1644 jai_ap_tds_cert_nums_s.nextval, /*Bgowrava for Bug#6129650*/
1645 lv_tds_section ,/*bduvarag for Bug#5647725*/ /*CSahoo for BUG#5631784*/
1646 pv_org_tan_num ,
1647 cur_rec.fin_year ,
1648 1 ,
1649 ln_user_id ,
1650 sysdate ,
1651 ln_user_id ,
1652 sysdate ,
1653 ln_last_update_login
1654 );
1655
1656 else
1657
1658 Fnd_File.put_line(Fnd_File.LOG, 'Updated certificate number in jai_ap_tds_cert_nums');
1659 update jai_ap_tds_cert_nums
1660 set certificate_num = ln_certificate_num
1661 where org_tan_num = pv_org_tan_num
1662 and fin_yr = cur_rec.fin_year
1663 and regime_code = lv_tds_section/*bduvarag for Bug#5647725*/ /*CSahoo for BUG#5631784*/
1664 ;
1665 end if;
1666
1667 /* insert into jai_ap_tds_f16_hdrs_all */
1668 IF lv_tds_section = 'TDS' THEN/*bduvarag for Bug#5647725*/
1669 Fnd_File.put_line(Fnd_File.LOG, 'Inserting record in jai_ap_tds_f16_hdrs_all with form16_hdr_id : ' || to_char(ln_form16_hdr_id));
1670 insert into jai_ap_tds_f16_hdrs_all
1671 (
1672 form16_hdr_id ,
1673 fin_yr ,
1674 org_tan_num ,
1675 certificate_num ,
1676 certificate_date ,
1677 vendor_id ,
1678 vendor_site_id ,
1679 --tds_tax_id ,/*Commented by nprashar for bug # 6774129*/
1680 tax_authority_id ,
1681 from_date ,
1682 to_date ,
1683 print_flag ,
1684 org_id ,
1685 tds_tax_section ,
1686 created_by ,
1687 creation_date ,
1688 last_updated_by ,
1689 last_update_date ,
1690 last_update_login ,
1691 program_id ,
1692 program_login_id ,
1693 program_application_id ,
1694 request_id
1695 )
1696 values
1697 (
1698 ln_form16_hdr_id ,
1699 cur_rec.fin_year ,
1700 pv_org_tan_num ,
1701 ln_certificate_num ,
1702 trunc(sysdate) ,
1703 cur_rec.vendor_id ,
1704 cur_rec.vendor_site_id ,
1705 --cur_rec.tds_tax_id ,/*Commented by nprashar for bug # 6774129*/
1706 cur_rec.tax_authority_id ,
1707 ld_tds_payment_from_date ,
1708 ld_tds_payment_to_date ,
1709 'N' ,
1710 cur_rec.operating_unit_id ,
1711 cur_rec.tds_section ,
1712 ln_user_id ,
1713 sysdate ,
1714 ln_user_id ,
1715 sysdate ,
1716 ln_last_update_login ,
1717 ln_program_id ,
1718 ln_program_login_id ,
1719 ln_program_application_id ,
1720 ln_request_id
1721 )
1722 ;
1723 END IF;/*bduvarag for Bug#5647725*/
1724
1725 /* logic to punch the certificate line number */
1726 /* All tds invoices will be grouped by parent invoice provided tds invoice is not against a threshold transition */
1727 ln_cert_line_num := 0;
1728 ln_prev_parent_invoice_id := -9999;
1729 -- lv_prev_tds_event := 'INITIAL';
1730 /*Bug 5647725 bduvarag*/
1731
1732 Fnd_File.put_line(Fnd_File.LOG, 'Puching certificate line numbers ');
1733
1734 for tds_payment in c_form16_cert_lines(ln_form16_hdr_id)
1735 loop
1736
1737 lv_tds_event := null;
1738 open c_tds_thhold_event(tds_payment.threshold_trx_id);
1739 fetch c_tds_thhold_event into lv_tds_event;
1740 close c_tds_thhold_event;
1741
1742 lv_tds_event := nvl(lv_tds_event, 'NO EVENT');
1743 if ln_prev_parent_invoice_id <> tds_payment.parent_invoice_id
1744 -- or (lv_prev_tds_event <> lv_tds_event and lv_tds_event like 'THRESHOLD TRANSITION%')
1745 /*Bug 5647725 bduvarag*/
1746 then
1747 ln_cert_line_num := ln_cert_line_num + 1;
1748 ln_prev_parent_invoice_id := tds_payment.parent_invoice_id;
1749 -- lv_prev_tds_event := lv_tds_event;
1750 /*bduvarag for Bug#5647725*/
1751 end if;
1752
1753 update jai_ap_tds_inv_payments
1754 set certificate_line_num = ln_cert_line_num
1755 where current of c_form16_cert_lines;
1756
1757 Fnd_File.put_line(Fnd_File.LOG, 'Line number / No of records for the line :'
1758 || to_char(ln_cert_line_num) || ' / ' || to_char(sql%rowcount) );
1759 end loop;
1760
1761
1762 << continue_with_next_certificate >>
1763 null;
1764
1765 end loop; /* c_group_for_certificate */
1766
1767
1768 <<exit_from_procedure>>
1769 Fnd_File.put_line(Fnd_File.LOG, 'No of Certificates Generated : ' || to_char(ln_certificate_count));
1770 Fnd_File.put_line(Fnd_File.LOG, '** Successful End of procedure jai_ap_tds_processing_pkg.process_tds_certificates **');
1771
1772 return;
1773
1774 exception
1775 when others then
1776 retcode := 2;
1777 errbuf := 'Error from jai_ap_tds_processing_pkg.process_tds_certificates : ' || sqlerrm;
1778 Fnd_File.put_line(Fnd_File.LOG, 'Error End of procedure jai_ap_tds_processing_pkg.process_tds_payments : ' || sqlerrm);
1779
1780 end process_tds_certificates;
1781 /* ****************************** process_tds_certificates ***************************************** */
1782 /* End added for bug#4448293 */
1783
1784 END jai_ap_tds_processing_pkg;