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