[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_TDS_ETDS_PKG
Source
1 PACKAGE BODY jai_ap_tds_etds_pkg AS
2 /* $Header: jai_ap_tds_etds.plb 120.15.12010000.8 2008/11/20 09:12:41 bgowrava ship $ */
3
4 /*---------------------------------------------------------------------------------------------------------
5 change history for jai_ap_tds_etds_pkg.sql
6
7 SlNo. DD/MM/YYYY Author and Details of Modifications
8 ----------------------------------------------------------------------------------------------------------
9 1 22/03/2004 Vijay Shankar for Bug# 3463974, Version: 619.1
10 This Package is created for enhancement to capture all the eTDS format, data Requirements in different procedures
11
12 2 13/04/2004 Vijay Shankar for Bug# 3603545 (also fixed 3567864), Version: 619.2
13 TDS and Base Taxable amounts are not populated correctly in
14 JAI_AP_ETDS_T table for Vendor Prepayment Invoices
15 TDS Amount is wrongly populated as tds_tax_rate is used instead of
16 tds_tax_rate/100 to calculated tds amount of prepayment applied amount.
17 Base amount is not at all reduced to the extent of prepayment applied which is resolved with this bug.
18
19 Previous Bug# 3463974 is Obsoleted with this bug
20
21 3. 21/06/2004 Aparajita for bug#3708878. Version#115.1
22 Section was not getting printed properly for deductee details. Call to getSectionCode was missing for deductee section.
23
24 4. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
25 Changed AP Lookup code from 'TDS' to 'INDIA TDS'
26
27 5. 08-Jun-2005 File Version 116.3. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
28 as required for CASE COMPLAINCE.
29
30 6. 14-Jun-2005 rchandan for bug#4428980, Version 116.4
31 Modified the object to remove literals from DML statements and CURSORS.
32 This activity is done as a part of R12 initiatives.
33
34 7. 14-Jun-2205 Brathod for Bug# 4428712, Version 116.5
35 Issue:
36 Impact of Bank Account Project on IL Objects
37 Solution:
38 In R12 Bank Account Project will eliminate the redudant information maintained
39 by each diffrent applications and improve the information sharing.
40 As per the documentation AP_BANK_ACCOUNTS and AP_BANK_BRANCHES tables will
41 hold information only for external bank accounts. For internal bank accounts
42 as per the new model HZ_PARTIES and CE_BANK_ACCOUNT, CE_BANK_ACCT_USES_ALL
43 will be used. The code in this procedure is modfied to refer to this new
44 model as part of R12 Initiative.
45
46 8. 29-Jun-2005 ssumaith - bug#4448789 - removal of hr_operating_units.legal_entity_id from this trigger.
47
48 9. 17-Aug-2005 Ramananda for bug#4555466 during R12 Sanity Testing. File Version 120.2
49 Instead of fnd_common_lookups table, ja_lookups table is being referred
50
51 10 07/12/2005 Hjujjuru for the bug 4866533 File version 120.3
52 added the who columns in the insert into tables JAI_AP_ETDS_REQUESTS and JAI_AP_ETDS_T.
53 Dependencies Due to this bug:-
54 None
55
56 11 23/12/2005 Hjujjuru for Bug 4889272, File Version 120.4
57 Removed the legal_entity_id parameter in the
58 procedures populate_details and generate_flat_file.
59 Removed the profile_org_id from the generate_flat_file procedure.
60 Modified the code to eliminate references to the
61 legal_entity_id and Operating Unit id.
62 Changed the position of the parameter p_organization_id in the
63 generate_flat_file procedure.
64
65 12 27/03/2006 Hjujjuru for Bug 5096787 , File Version 120.5
66 Spec changes have been made in this file as a part og Bug 5096787.
67 Now, the r12 Procedure/Function specs is in this file are in
68 sync with their corrsponding 11i counterparts
69
70
71
72 13 11/05/2007 sacsethi for bug 5647248 + 5658040
73
74 The following changes are done to make use of the same package for 26Q and 27Q
75 Modified the populate_details procedure:
76 1.added two parameters p_include_list,p_exclude_list in the call to populate_details,quarterly_returns
77 and generate_etds_returns.
78 2.Declared lv_list which is of varray type
79 3.added populate_include_exclude_list procedure in populate_details.
80 4.declared cursor c_prg_name which will fetch the concurrent program name in quarterly_returns procedure.
81 Based on this concurrent program name form_number is changed.
82
83 Report has been modified to generate Form 27A along with eTDS Quarterly Returns.
84 Added a call to the Concurrent JAINTDSA in the quarterly_returns procedure.
85
86
87 Added the update clause to modify the taxabale_amount of ja_in_ap_etds_temp
88 based on the taxable_amount of jai_ap_tds_thhold_trxs table.
89 In case of threshold transition and rollback transactions, the
90 taxable amount should be 0 for the differential invoice that has been created.
91 Hence, implemented this change.
92
93 14. 28-jun-2007 CSahoo for bug#6158875
94 modified the num of input parameters to yearly_returns procedure.
95 added the call to the function Fnd_date.canoncical_to_date.
96
97 15. 02-Jul-2007 CSahoo for bug#6158875, file version 120.10
98 modified the num of input parameters to querterly_returns procedure.
99 added the call to the function Fnd_date.canoncical_to_date.
100
101
102 16. 03/07/2007 CSAHOO FOR BUG#6158875, File Version 120.11
103 modified the following cursors c_tds_payment_check_id,c_base_payment_check_id,c_check_dtls.
104
105 17. 27/08/2007 CSahoo for bug#5975168, File Version 120.12
106 Added a check for status_lookup_code in cursor c_check_dtls
107
108 18. 27/08/2007 Csahoo for bug#6070014, File version 120.13
109 Changed the format mask from hardcoded value to
110 assigned value. This is done in procedure
111 create_quart_deductee_dtl
112
113 19. 13/02/2008 Lakshmi Gopalsami for bug# 6796765 File Version 120.5.12000000.4
114 1. Changed the function getSectionCode. Replaced the hardcoded value with p_string.
115 2. Changed in procedure create_challan_detail. Added upper for both p_challan_section and Sec.().
116 3. Changed in procedure create_deductee_detail. Added upper for both p_deductee_section and Sec.().
117 4. Changed in procedure create_quart_challan_dtl.
118 a) Added input parameter p_form_name to print the section code depending on the section.
119 b) Declared variables to select the section truncation depending on the form name.
120 c) Added logic to get the section code as 194C, 194D, 195 etc. from SEC. 194(C) and then check whether it can be converted to a
121 number. If so, the value will be printed in flat file else extract the value from 2nd character in flat file.
122 hardcoded the values of 194BB, 194EE, 194LA as per the details provided in NSDL as separate logic cannot be derived.
123 d) Changed the UTL_FILE.PUT_LINE parameter from getsectioncode to lv_output_string.
124
125 20. 15/02/2008 JMEENA for bug#6647362 File Version 120.5.12000000.4
126 Added 5000 UTL_FILE buffer size for bug#6647362
127
128 21. 20/02/2008 JMEENA for bug#4600778 File Version 120.5.12000000.5
129 Removed the cursor c_bank_branch_code and added column bsr_code in the cursor c_tds_payment_check_id and fetched the value in v_bank_branch_code.
130
131 22 08-Oct-2008 Bgowrava for Bug#6195027, File Version 120.5.12000000.5
132 Added sh_cess_rate in cursor c_tax_rates and defined related variables. Included ln_sh_cess_amt in ln_cess_amt.
133
134 23. 24-Oct-2008 Bgowrava for bug#7485031, File version 115.14.6017.12
135 Added code to use the include_flag and exclude_flag while determining the sections which need to be considered to be
136 inserted into the table JAI_AP_ETDS_T for the current execution. Also included code changes for bug 6281440
137
138 24. 06-Aug-2007 Forward Port Bug 6329774
139 Changed the challan date to base_invoice_date while printing deductee details.
140
141 25. 18-Oct-2008 Bgowrava for bug#6030953, File version 120.15.12010000.7, 120.21
142 Mandatory details to be printed in Form 27 A which is called from eTDS Quaterly. Following parameters are included in respective procedures
143 p_RespPers_flat_no
144 p_RespPers_prem_bldg
145 p_RespPers_rd_st_lane
146 p_RespPers_area_loc
147 p_RespPers_tn_cty_dt
148 p_RespPers_tel_no
149 p_RespPers_email
150 and parameter p_RespPersAddress is removed.
151 Affected procedures are :
152 create_quarterly_fh
153 validate_batch_header
154 quarterly_returns
155 generate_etds_returns
156 Also passed the parameters while submitting the request for Form 27A. Included p_deductor_status
157
158 ---------------------------------------------------------------------------------------------------------*/
159
160 FUNCTION formatAmount( p_amount IN NUMBER) RETURN VARCHAR2 IS
161 BEGIN
162
163 -- return (replace(to_char(ROUND(nvl(p_amount,0), 2), '999999999999D99'),'.'));
164 return ( to_char(ROUND(nvl(p_amount,0), 2)*100 ) );
165 END formatAmount;
166
167 FUNCTION getSectionCode( p_section IN VARCHAR2, p_string IN VARCHAR2 DEFAULT NULL ) RETURN VARCHAR2 IS
168 BEGIN
169
170 IF p_section IS NOT NULL THEN
171 -- Bug 6796765. Added by Lakshmi Gopalsami
172 -- Changed to p_string instead of hardcoded value
173 return replace(translate(p_section, p_string, ' '), ' ');
174 ELSE
175 return ' ';
176 END IF;
177
178 END getSectionCode;
179
180 PROCEDURE openFile(
181 p_directory IN VARCHAR2,
182 p_filename IN VARCHAR2
183 ) IS
184
185 BEGIN
186
187 v_filehandle := UTL_FILE.fopen(p_directory, p_filename, 'W', 5000); --Added 5000 buffer size for bug#6647362
188
189 v_utl_file_dir := p_directory;
190 v_utl_file_name := p_filename;
191
192 END openFile;
193
194 PROCEDURE closeFile IS
195 BEGIN
196 UTL_FILE.fclose(v_filehandle);
197 END closeFile;
198
199 PROCEDURE populate_details(
200 p_batch_id IN NUMBER,
201 -- p_legal_entity_id IN NUMBER, -- Harshita for Bug 4889272
202 p_org_tan_num IN VARCHAR2,
203 p_tds_vendor_id IN NUMBER,
204 p_tds_vendor_site_id IN NUMBER,
205 p_tds_inv_from_date IN DATE,
206 p_tds_inv_to_date IN DATE,
207 p_etds_yearly_returns VARCHAR2,
208 p_include_list IN VARCHAR2, --Date 11-05-2007 by Sacsethi for bug 5647248
209 p_exclude_list IN VARCHAR2
210 )
211 IS
212 v_tds_check_id NUMBER(15);
213 v_prepay_inv_id_of_tds NUMBER(15);
214 v_bank_account_id NUMBER(15);
215 v_bank_account_name VARCHAR2(50); -- added by csahoo for BUG#6158875
216
217 v_temp_challan_num VARCHAR2(50);
218 v_temp_challan_date DATE;
219 v_temp_bank NUMBER(15);
220 v_temp_bank_acc_name VARCHAR2(50); -- added by csahoo for BUG#6158875
221
222 v_statement_id VARCHAR2(3);
223 v_debug_base_invid NUMBER(15);
224 v_debug_tds_invid NUMBER(15);
225
226 v_base_check_date DATE;
227 v_tds_check_date DATE;
228 v_challan_num VARCHAR2(50);
229 v_challan_date DATE;
230 v_bank_branch_code HZ_ORGANIZATION_PROFILES.BANK_OR_BRANCH_NUMBER%TYPE;
231
232 v_challan_err VARCHAR2(100);
233 v_deductee_err VARCHAR2(100);
234
235 v_base_invoice_check_id NUMBER(15);
236 v_prepay_inv_id_of_base NUMBER(15);
237
238 v_payment_id NUMBER;
239 v_prepay_invoice_id NUMBER;
240 v_prepayment_amount_applied NUMBER;
241
242 /* Bug 4353842. Added by Lakshmi Gopalsami */
243 ln_check_number ap_checks_all.check_number%TYPE;
244 ln_tax_rate JAI_CMN_TAXES_ALL.tax_rate%TYPE;
245 ln_tds_rate JAI_CMN_TAXES_ALL.tax_rate%TYPE;
246 ln_surcharge_rate JAI_CMN_TAXES_ALL.surcharge_rate%TYPE;
247 ln_cess_rate JAI_CMN_TAXES_ALL.cess_rate%TYPE;
248 ln_inv_amt ap_invoices_all.invoice_amount%TYPE;
249
250 -- added, Harshita for Bug 4525089
251
252 lv_cert_issue_date DATE ;
253 ln_tds_amt JAI_AP_ETDS_T.amt_of_tds%TYPE;
254 ln_surcharge_amt JAI_AP_ETDS_T.amt_of_surcharge%TYPE;
255 ln_cess_amt JAI_AP_ETDS_T.amt_of_cess%TYPE;
256 -- ended, Harshita for Bug 4525089
257
258 --Date 11-05-2007 by Sacsethi for bug 5647248
259 -- start 5647248
260 lv_include_flag VARCHAR2(1);
261 lv_exclude_flag VARCHAR2(1);
262 TYPE lv_list IS VARRAY(10) OF VARCHAR2(30);
263 lv_include lv_list :=lv_list();
264 lv_exclude lv_list :=lv_list();
265 -- end 5647248
266
267
268 -- Harshita for Bug 4889272
269 --v_tanAtLegalEntityFlag CHAR(1); -- := 'N'; File.Sql.35 by Brathod
270
271 v_legalEntityTan VARCHAR2(50);
272 lv_voided CONSTANT VARCHAR2(30) := 'VOIDED'; --rchandan for bug#4428980
273 lv_stop_init CONSTANT VARCHAR2(30) := 'STOP INITIATED';--rchandan for bug#4428980
274 lv_india_tds_source CONSTANT VARCHAR2(30) := 'INDIA TDS';--rchandan for bug#4428980
275 lv_source_attribute VARCHAR2(30);--rchandan for bug#4428980
276
277 -- to get TAN of an organization
278 CURSOR c_tan_number(p_organization_id IN NUMBER) IS
279 SELECT attribute1
280 FROM hr_all_organization_units
281 WHERE organization_id = p_organization_id;
282
283 -- p_invoice_id contains tds_invoice_id incase if tds amount is directly paid to TDS Authorities using a c
284 -- p_invoice_id contains PREPAYMENT invoice_id to TDS Auth., if TDS invoice is applied to PREPAYMENT invo
285 /*CURSOR c_tds_payment_check_id(p_invoice_id IN NUMBER) IS
286 SELECT pay.check_id, apc.bank_account_id, apc.attribute3 challan_num, apc.attribute1 challan_date -- p
287 FROM ap_invoice_payments_all PAY, ap_checks_all APC
288 WHERE PAY.invoice_id = p_invoice_id
289 AND PAY.check_id = APC.check_id
290 AND APC.status_lookup_code NOT IN (lv_voided,lv_stop_init);--rchandan for bug#4428980
291
292 CURSOR c_base_payment_check_id(p_invoice_id IN NUMBER) IS
293 SELECT pay.check_id, apc.bank_account_id, check_date
294 FROM ap_invoice_payments_all PAY, ap_checks_all APC
295 WHERE PAY.invoice_id = p_invoice_id
296 AND PAY.check_id = APC.check_id
297 AND APC.status_lookup_code NOT IN (lv_voided, lv_stop_init);--rchandan for bug#4428980*/
298
299
300
301 -- Vijay Shankar for Bug#4448293
302 /*CURSOR c_check_dtls(cpn_check_id IN NUMBER) IS
303 SELECT apc.bank_account_id,
304 apc.attribute3 challan_num,
305 apc.attribute1 challan_date, */ -- pay.invoice_payment_id
306 /* Bug 4353842. Added by Lakshmi Gopalsami */
307 /* apc.check_number
308 FROM ap_checks_all APC
309 WHERE check_id = cpn_check_id;*/
310
311 /*CURSOR c_check_dtls(cp_check_id IN NUMBER) IS
312 SELECT apc.bank_account_id,
313 apc.attribute3 challan_num,
314 apc.attribute1 challan_date, -- pay.invoice_payment_id
315 apc.check_number
316 FROM ap_checks_all apc,
317 JAI_AP_TDS_INV_PAYMENTS jatp
318 WHERE
319 apc.check_id = jatp.check_id AND
320 jatp.check_id = cp_check_id;*/
321
322 /*added by csahoo for bug # 6158875, START*/
326 JATP.check_deposit_date challan_date, JATP.bsr_code branch_code
323 /*Modified the cursor c_tds_payment_check_id by JMEENA for bug#4600778 to select the bsr_code from JAI_AP_TDS_PAYMENTS table */
324 CURSOR c_tds_payment_check_id(p_invoice_id IN NUMBER) IS
325 SELECT pay.check_id, apc.current_bank_account_name, JATP.Challan_no challan_num,
327 FROM ap_invoice_payments_all PAY, ap_checks_v APC, JAI_AP_TDS_PAYMENTS JATP
328 WHERE PAY.invoice_id = p_invoice_id
329 AND PAY.check_id = APC.check_id
330 AND APC.check_id = JATP.check_id
331 AND APC.status_lookup_code NOT IN (lv_voided,lv_stop_init);
332
333 CURSOR c_base_payment_check_id(p_invoice_id IN NUMBER) IS
334 SELECT pay.check_id, apc.current_bank_account_name, check_date
335 FROM ap_invoice_payments_all PAY, ap_checks_v APC
336 WHERE PAY.invoice_id = p_invoice_id
337 AND PAY.check_id = APC.check_id
338 AND APC.status_lookup_code NOT IN (lv_voided, lv_stop_init);
339
340
341 CURSOR c_check_dtls(cp_check_id IN NUMBER) IS
342 SELECT ACV.CURRENT_BANK_ACCOUNT_NAME ,
343 JATP.Challan_no challan_num,
344 JATP.check_deposit_date challan_date,
345 JATP.check_number
346 FROM AP_CHECKS_V ACV, JAI_AP_TDS_PAYMENTS JATP
347 WHERE ACV.check_id = JATP.check_id
348 AND jatp.check_id = cp_check_id
349 -- Bug 5975168. Added by csahoo
350 AND ACV.status_lookup_code NOT IN
351 ('VOIDED', 'STOP INITIATED');
352
353 /*added by csahoo for bug # 6158875, END*/
354
355 CURSOR c_prepay_invoice_id(p_tds_invoice_id IN NUMBER) IS
356 SELECT b.invoice_id prepay_invoice_id
357 FROM ap_invoice_distributions_all a, ap_invoice_distributions_all b
358 WHERE a.invoice_id = p_tds_invoice_id
359 AND a.prepay_distribution_id IS NOT NULL
360 AND (a.reversal_flag IS NULL OR a.reversal_flag = 'N') --rchandan for bug#4428980
361 AND b.invoice_distribution_id = a.prepay_distribution_id;
362
363 /* Following cursor is modified by Brathod for Bug# 4428712 (R12 Initiative)
364 CURSOR c_bank_branch_code(p_bank_account_id IN NUMBER) IS
365 SELECT HZOP.BANK_OR_BRANCH_NUMBER
366 FROM HZ_ORGANIZATION_PROFILES HZOP
367 , CE_BANK_ACCOUNTS CEBA
368 WHERE HZOP.PARTY_ID = CEBA.BANK_BRANCH_ID
369 AND CEBA.BANK_ACCOUNT_ID = p_bank_account_id;*/
370
371 CURSOR c_cert_issue_date(p_tds_invoice_id IN NUMBER) IS
372 SELECT a.issue_date
373 FROM JAI_AP_TDS_F16_HDRS_ALL a, JAI_AP_TDS_INV_PAYMENTS b -- Bug#4517720 ja_in_ap_form16_dtl b
374 WHERE a.certificate_num = b.certificate_num
375 AND a.org_tan_num = b.org_tan_num
376 AND a.fin_yr = b.fin_year
377 AND b.invoice_id = p_tds_invoice_id;
378
379 CURSOR c_check_date(p_check_id IN NUMBER) IS
380 SELECT nvl(jatp.check_date, jatp.check_date) check_date
381 /* decode (attribute1, NULL,check_date,to_date(attribute1)) */
382 FROM JAI_AP_TDS_INV_PAYMENTS jatp
383 WHERE
384 check_id = p_check_id;
385 lv_tds_check_date DATE ;
386
387 CURSOR c_tax_rates(p_tax_id JAI_CMN_TAXES_ALL.tax_id%TYPE)
388 IS
389 SELECT
390 NVL(tax_rate,0),
391 (NVL(tax_rate,0) - (NVL(surcharge_rate,0) + NVL(cess_rate,0) + NVL(sh_cess_rate,0))) tds_rate, -- added NVL, Harshita for Bug 4639067 --Added sh_cess_rate by Bgowrava for bug #6195027
392 NVL(surcharge_rate,0) surcharge_rate,
393 NVL(cess_rate,0),
394 NVL(sh_cess_rate,0) --Added by Bgowrava for Bug#6195027
395 FROM
396 JAI_CMN_TAXES_ALL jtc
397 WHERE
398 tax_id = p_tax_id ;
399 -- ended, Harshita for Bug 4525089
400 --Date 11-05-2007 by Sacsethi for bug 5647248
401 -- start 5647248
402
403 -- Bug 5975168. Added by csahoo, start
404 ln_con_for_challan NUMBER ;
405 ln_con_for_deductee NUMBER ;
406 -- Bug 5975168. end
407
408 /*START, Bgowrava for Bug#6195027*/
409 ln_sh_cess_rate NUMBER ;
410 ln_sh_cess_amt NUMBER ;
411 /*END, Bgowrava for Bug#6195027*/
412
413 PROCEDURE populate_include_exclude_list(
414 p_include_exclude_list IN VARCHAR2,
415 p_include_exclude_arr OUT NOCOPY lv_list)
416 IS
417 ln_pos NUMBER;
418 ln_initial_pos NUMBER;
419 ln_length NUMBER;
420 lv_char VARCHAR2(1);
421 BEGIN
422 p_include_exclude_arr:=lv_list();
423 lv_char := ',';
424 ln_initial_pos := 1;
425 p_include_exclude_arr.extend(10);
426 FOR lv_count in 1..10
427 LOOP
428 p_include_exclude_arr(lv_count) := NULL;
429 END LOOP;
430 IF p_include_exclude_list IS NOT NULL THEN
431 FOR lv_count in 1..10
432 LOOP
433 ln_pos := instr (p_include_exclude_list, lv_char, 1, lv_count);
434 IF ln_pos <> 0 THEN
435 ln_length := ln_pos - ln_initial_pos ;
436 p_include_exclude_arr(lv_count) := substr (p_include_exclude_list, ln_initial_pos , ln_length);
437 ln_initial_pos := ln_pos + 1 ;
438 ELSE
439 p_include_exclude_arr(lv_count) := substr (p_include_exclude_list, ln_initial_pos);
440
441 FOR i in lv_count+1..10
442 LOOP
446 END IF;
443 p_include_exclude_arr(i) := p_include_exclude_arr(lv_count) ;
444 END LOOP ;
445 EXIT;
447 END LOOP;
448 END IF;
449 -- end 5647248
450
451 END populate_include_exclude_list;
452
453
454
455
456
457
458 BEGIN
459
460 v_statement_id := '1';
461
462 -- Harshita for Bug 4889272
463 -- v_tanAtLegalEntityFlag := 'N';
464 /*
465 OPEN c_tan_number(p_legal_entity_id);
466 FETCH c_tan_number INTO v_legalEntityTan;
467 CLOSE c_tan_number;
468
469
470 IF v_legalEntityTan IS NOT NULL THEN
471 v_tanAtLegalEntityFlag := 'Y';
472 END IF;
473 */
474
475 v_statement_id := '2';
476 lv_source_attribute := 'ATTRIBUTE1'; --rchandan for bug#4428980
477
478 lv_include.extend(10);
479 lv_exclude.extend(10);
480
481 --Date 11-05-2007 by Sacsethi for bug 5647248
482 -- start 5647248
483 IF p_include_list IS NOT NULL THEN
484 lv_include_flag := 'Y';
485 populate_include_exclude_list(p_include_list, lv_include);
486 ElSE
487 lv_include_flag := 'N';
488 END IF;
489
490 IF p_exclude_list IS NOT NULL THEN
491 lv_exclude_flag := 'Y';
492 populate_include_exclude_list(p_exclude_list, lv_exclude);
493 ElSE
494 lv_exclude_flag := 'N';
495 END IF;
496 -- end 5647248
497
498
499
500
501 INSERT INTO JAI_AP_ETDS_T (
502 batch_id,
503 -- line_number,
504 base_invoice_id,
505 base_taxabale_amount,
506 tds_invoice_id,
507 tds_invoice_num,
508 tds_invoice_date,
509 tds_section,
510 tds_tax_id,
511 tds_tax_rate,
512 tds_amount,
513 consider_for_challan,
514 consider_for_deductee,
515 tds_check_id, -- Vijay Shankar for Bug#4448293
516 base_invoice_date ,
517 -- added, Harshita for Bug 4866533
518 created_by,
519 creation_date,
520 last_updated_by,
521 last_update_date
522 ) SELECT p_batch_id,
523 -- rownum + 2, -- 2 is added to all records to take care of file and batch header record line numbers
524 base_invoices.invoice_id, -- base_invoice_id
525 base_invoices.invoice_amount, -- base taxable amount
526 tds_invoices.invoice_id, -- tds_invoice_id
527 tds_invoices.invoice_num, -- tds_invoice_num
528 tds_invoices.invoice_date, -- tds_invoice_date
529 a.tds_section,
530 a.tds_tax_id,
531 a.tds_tax_rate,
532 a.tax_amount,
533 1,
534 1,
535 a.check_id,
536 base_invoices.invoice_date,
537 -- added, Harshita for Bug 4866533
538 fnd_global.user_id,
539 sysdate,
540 fnd_global.user_id,
541 sysdate
542 from JAI_AP_TDS_INV_PAYMENTS a, ap_invoices_all base_invoices, ap_invoices_all tds_invoices
543 where a.parent_invoice_id = base_invoices.invoice_id
544 and a.invoice_id = tds_invoices.invoice_id
545 and a.invoice_date between p_tds_inv_from_date and p_tds_inv_to_date
546 and a.tax_authority_id = p_tds_vendor_id
547 and (p_tds_vendor_site_id is null or a.tax_authority_site_id = p_tds_vendor_site_id)
548 and a.payment_amount <> 0
549 and a.org_tan_num = p_org_tan_num
550 and
551 ( lv_include_flag = 'N' or ( lv_include_flag = 'Y'
552 and upper(a.tds_section) in ( lv_include(1),lv_include(2),lv_include(3),lv_include(4),lv_include(5),lv_include(6),lv_include(7),lv_include(8),lv_include(9),lv_include(10) ) ))
553 and
554 ( lv_exclude_flag = 'N' or ( lv_exclude_flag = 'Y'
555 and upper(a.tds_section) not in ( lv_exclude(1),lv_exclude(2),lv_exclude(3),lv_exclude(4),lv_exclude(5),lv_exclude(6),lv_exclude(7),lv_exclude(8),lv_exclude(9),lv_exclude(10)) ) )
556 ; --Added above two conditions by Bgowrava for bug#7485031
557
558
559 v_statement_id := '3';
560 /* -------- following fields of JAI_AP_ETDS_T needs to be populated now
561 Invoice Distributions - prepayment_amount_applied
562 Payment Details - tds_check_id, challan_num, challan_date, bank_branch_code
563 --------------------------------- */
564 FOR dtl IN (select a.rowid row_id, a.*,
565 b.vendor_id vendor_id, b.vendor_site_id vendor_site_id, b.invoice_type_lookup_code inv_type
566 from JAI_AP_ETDS_T a, ap_invoices_all b
567 where a.batch_id = p_batch_id and a.base_invoice_id = b.invoice_id)
568 LOOP
569 --added by csahoo for bug#5975168, start
570 ln_con_for_challan := 1;
571 ln_con_for_deductee := 1;
572 --bug#5975168, end
573
574 ln_tax_rate := NULL;
575 ln_tds_rate := NULL;
576 ln_surcharge_rate := NULL;
577 ln_cess_rate := NULL;
578 ln_tds_amt := 0 ;
579 ln_surcharge_amt := 0 ;
580 ln_cess_amt := 0 ;
581
582 /*START, Bgowrava for Bug#6195027*/
586
583 ln_sh_cess_rate := NULL;
584 ln_sh_cess_amt := 0;
585 /*END, Bgowrava for Bug#6195027*/
587 OPEN c_cert_issue_date(dtl.tds_invoice_id);
588 FETCH c_cert_issue_date INTO lv_cert_issue_date;
589 CLOSE c_cert_issue_date;
590
591 OPEN c_check_date(dtl.tds_check_id);
592 FETCH c_check_date INTO lv_tds_check_date;
593 CLOSE c_check_date;
594
595 OPEN c_tax_rates(dtl.tds_tax_id) ;
596 FETCH c_tax_rates INTO ln_tax_rate, ln_tds_rate, ln_surcharge_rate, ln_cess_rate, ln_sh_cess_rate ; --Added ln_sh_cess_rate by Bgowrava for bug#6195027
597 CLOSE c_tax_rates ;
598
599
600 v_challan_err := null;
601 v_deductee_err := null;
602 v_tds_check_id := null;
603 v_base_invoice_check_id := null;
604 v_challan_num := null;
605 v_challan_date := null;
606 v_bank_account_id := null;
607 v_bank_account_name := null; --added by csahoo for BUG#6158875
608 v_bank_branch_code := null;
609 v_prepay_inv_id_of_base := null;
610 v_prepay_inv_id_of_tds := null;
611 v_prepayment_amount_applied := null;
612 v_tds_check_date := null;
613 v_base_check_date := null;
614
615 v_temp_bank := null;
616 v_temp_bank_acc_name := null; --added by csahoo for BUG#6158875
617 v_temp_challan_num := null;
618 v_temp_challan_date := null;
619
620 v_debug_tds_invid := dtl.tds_invoice_id;
621 v_debug_base_invid := dtl.base_invoice_id;
622
623 ln_check_number := null;
624
625 v_statement_id := '3a';
626 /* Modified by JMEENA for bug# 4600778 to fetch the bsr_code in v_bank_branch_code */
627 OPEN c_tds_payment_check_id(dtl.tds_invoice_id);
628 FETCH c_tds_payment_check_id INTO v_tds_check_id, v_bank_account_name, v_challan_num, v_challan_date, v_bank_branch_code;
629 CLOSE c_tds_payment_check_id;
630
631 IF v_tds_check_id IS NULL THEN
632 v_statement_id := '3b';
633 OPEN c_prepay_invoice_id(dtl.tds_invoice_id);
634 FETCH c_prepay_invoice_id INTO v_prepay_inv_id_of_tds;
635 CLOSE c_prepay_invoice_id;
636
637 v_statement_id := '3b';
638 /* Modified by JMEENA for bug# 4600778 to fetch the bsr_code in v_bank_branch_code */
639 OPEN c_tds_payment_check_id(v_prepay_inv_id_of_tds);
640 FETCH c_tds_payment_check_id INTO v_tds_check_id, v_bank_account_name, v_challan_num, v_challan_date, v_bank_branch_code;
641 CLOSE c_tds_payment_check_id;
642 ELSE
643
644 OPEN c_check_dtls(v_tds_check_id);
645 FETCH c_check_dtls INTO v_bank_account_name, v_challan_num,
646 v_challan_date, ln_check_number;
647 -- Bug 5975168. Added by csahoo
648 IF c_check_dtls%NOTFOUND THEN
649 ln_con_for_challan := 0;
650 fnd_file.put_line(FND_FILE.LOG, ' consider for challan '|| ln_con_for_challan);
651 CLOSE c_check_dtls;
652 GOTO update_now;
653 END IF ;
654 -- End for bug 5975168
655 CLOSE c_check_dtls;
656 END IF;
657
658 IF v_tds_check_id IS NULL THEN
659 v_challan_err := 'Payment Information not available for TDS Inv';
660 GOTO update_now;
661 END IF;
662
663 IF v_bank_branch_code IS NULL THEN
664 v_challan_err := 'Bank Branch Code is not found for TDS Inv';
665 ELSIF v_challan_num IS NULL THEN
666 v_challan_err := 'Challan Number is not found for TDS Inv';
667 ELSIF v_challan_date IS NULL THEN
668 v_challan_err := 'Challan Date is not found for TDS Inv';
669 END IF;
670
671 v_statement_id := '3d';
672 OPEN c_base_payment_check_id(dtl.base_invoice_id);
673 FETCH c_base_payment_check_id INTO v_base_invoice_check_id, v_temp_bank_acc_name, v_base_check_date;
674 CLOSE c_base_payment_check_id;
675
676 IF v_base_invoice_check_id IS NULL THEN
677 v_statement_id := '3e';
678 OPEN c_prepay_invoice_id(dtl.base_invoice_id);
679 FETCH c_prepay_invoice_id INTO v_prepay_inv_id_of_base;
680 CLOSE c_prepay_invoice_id;
681
682 v_statement_id := '3f';
683 OPEN c_base_payment_check_id(v_prepay_inv_id_of_base);
684 FETCH c_base_payment_check_id INTO v_base_invoice_check_id, v_temp_bank_acc_name, v_base_check_date;
685 CLOSE c_base_payment_check_id;
686 END IF;
687
688 IF v_base_invoice_check_id IS NULL THEN
689 v_deductee_err := 'Payment Information not available for Base Invoice';
690 END IF;
691
692 <<update_now>>
693
694 IF dtl.inv_type = 'PREPAYMENT' THEN
695 v_statement_id := '3g';
696 select sum(amount) - sum( nvl(prepay_amount_remaining, amount)) INTO v_prepayment_amount_applied
697 from ap_invoice_distributions_all
698 where invoice_id = dtl.base_invoice_id
699 and attribute1 = dtl.tds_tax_id;
700 END IF;
701
702 v_prepayment_amount_applied := nvl(v_prepayment_amount_applied,0);
703
704 IF v_generate_headers THEN
705 IF NVL(lv_action,'X') <> 'V' THEN
706 FND_FILE.put_line(FND_FILE.log, 'lengths - inv_type:'||length(dtl.inv_type)
707 ||', ch_num:'||length(v_challan_num) ||', bank_brCode:'||length(v_bank_branch_code) ||', ch_err:'
708 ||', ded_err:'||length(v_deductee_err)
709 );
710 END IF ;
711 END IF;
715 v_statement_id := '3h';
712
713 v_challan_num := substr(v_challan_num, 1,25);
714
716 /* following lines of UPDATE commented by Vijay Shankar for Bug# 3567864 and modified as below UPDATE st
717 UPDATE JAI_AP_ETDS_T
718 SET tds_amount = round(tds_amount - v_prepayment_amount_applied*dtl.tds_tax_rate/100, 2),
719 */
720
721 -- added, Harshita for Bug 4525089
722 ln_surcharge_amt := round((dtl.tds_amount * ln_surcharge_rate /ln_tax_rate),2) ;
723 ln_sh_cess_amt := round((dtl.tds_amount * ln_sh_cess_rate/ln_tax_rate),2); --Added by Bgowrava for Bug#
724 ln_cess_amt := round((dtl.tds_amount * ln_cess_rate/ln_tax_rate),2) + ln_sh_cess_amt ; --Added ln_sh_cess_amt by bgowrava for bug#6195027
725 ln_tds_amt := dtl.tds_amount - NVL(ln_surcharge_amt,0) - NVL(ln_cess_amt,0) ; -- added NVL, Harshita
726 -- ended, Harshita for Bug 4525089
727
728 UPDATE JAI_AP_ETDS_T
729 SET tds_amount = round(tds_amount - v_prepayment_amount_applied*dtl.tds_tax_rate/100, 2),
730 base_taxabale_amount = base_taxabale_amount - v_prepayment_amount_applied,
731 base_vendor_id = dtl.vendor_id,
732 base_vendor_site_id = dtl.vendor_site_id,
733 base_invoice_type_lookup_code = dtl.inv_type,
734 tds_check_id = v_tds_check_id,
735 challan_num = v_challan_num,
736 challan_date = v_challan_date,
737 check_number = ln_check_number,
738 bank_branch_code = v_bank_branch_code,
739 base_invoice_check_id = v_base_invoice_check_id,
740 prepayment_amount_applied = v_prepayment_amount_applied,
741 challan_error = v_challan_err,
742 deductee_error = v_deductee_err,
743 amt_of_tds = ln_tds_amt,
744 amt_of_surcharge = ln_surcharge_amt,
745 amt_of_cess = ln_cess_amt,
746 certificate_issue_date = lv_cert_issue_date,
747 tds_check_date = lv_tds_check_date,
748 -- Bug 5975168. Added by csahoo
749 consider_for_challan = ln_con_for_challan,
750 consider_for_deductee = decode(ln_con_for_challan,0,0,ln_con_for_deductee)
751 WHERE rowid = dtl.row_id;
752
753 END LOOP;
754
755 --Date 11-05-2007 by Sacsethi for bug 5647248
756 -- start 5647248
757 update jai_ap_etds_t a
758 set base_taxabale_amount =
759 ( select nvl(taxable_amount,0)
760 from JAI_AP_TDS_THHOLD_TRXS b
761 where b.invoice_to_tds_authority_id = a.tds_invoice_id )
762 where a.batch_id = p_batch_id
763 and exists
764 ( select 1
765 from JAI_AP_TDS_THHOLD_TRXS c
766 where c.invoice_to_tds_authority_id = a.tds_invoice_id
767 and c.tds_event like '%THRESHOLD%'
768 ) ;
769 -- end 5647248
770
771
772
773
774 EXCEPTION
775 WHEN OTHERS THEN
776 FND_FILE.put_line(FND_FILE.log, 'Err->'||SQLERRM);
777 FND_FILE.put_line(FND_FILE.log, 'statement->'||v_statement_id
778 ||', tds_invid->'||v_debug_tds_invid||', base_invid->'||v_debug_base_invid);
779 RAISE;
780 END populate_details;
781
782 PROCEDURE create_file_header(
783 p_line_number IN NUMBER,
784 p_record_type IN VARCHAR2,
785 p_file_type IN VARCHAR2,
786 p_upload_type IN VARCHAR2,
787 p_file_creation_date IN DATE,
788 p_file_sequence_number IN NUMBER,
789 p_deductor_tan IN VARCHAR2,
790 p_number_of_batches IN NUMBER
791 ) IS
792
793 BEGIN
794
795 UTL_FILE.PUT_LINE(v_filehandle,
796 LPAD(p_line_number, s_line_number, v_pad_number)
797 ||v_debug_pad_char||RPAD(p_record_type, s_record_type, v_pad_char)
798 ||v_debug_pad_char||RPAD(p_file_type, s_file_type, v_pad_char)
799 ||v_debug_pad_char||RPAD(p_upload_type, s_upload_type, v_pad_char)
800 ||v_debug_pad_char||LPAD(to_char(p_file_creation_date, 'ddmmyyyy'), s_date, v_pad_date)
801 ||v_debug_pad_char||LPAD(p_file_sequence_number, s_file_sequence_number, v_pad_number)
802 ||v_debug_pad_char||RPAD(nvl(p_deductor_tan,' '), s_deductor_tan, v_pad_char)
803 ||v_debug_pad_char||LPAD(nvl(p_number_of_batches,0), s_number_of_batches, v_pad_number)
804 );
805
806 END create_file_header;
807
808 PROCEDURE create_batch_header(
809 p_line_number IN NUMBER, -- 6
810 p_record_type IN VARCHAR2, -- 2
811 p_batch_number IN NUMBER, -- 4
812 p_challan_count IN NUMBER, -- 5
813 p_deductee_count IN NUMBER, -- 5
814 p_form_number IN CHAR, -- 4
815 p_rrr_number IN NUMBER, -- 10
816 p_rrr_date IN DATE, -- 8
817 p_deductor_tan IN VARCHAR2, -- 10
818 p_pan_of_tan IN VARCHAR2, -- 10
819 p_assessment_year IN NUMBER,-- 6
820 p_financial_year IN NUMBER, -- 6
821 p_deductor_name IN VARCHAR2,-- 75
822 p_tan_address1 IN VARCHAR2, -- 25
823 p_tan_address2 IN VARCHAR2, -- 25
824 p_tan_address3 IN VARCHAR2, -- 25
825 p_tan_address4 IN VARCHAR2, -- 25
826 p_tan_address5 IN VARCHAR2, -- 25
827 p_tan_state IN NUMBER, -- 2
828 p_tan_pin IN NUMBER, -- 6
829 p_chng_addr_since_last_return IN VARCHAR2, -- 1 tells you whether address of deductor is changed since last return
830 p_status_of_deductor IN VARCHAR2, -- 1 'C' for Central Govt/ 'O' for others
831 p_quart_year_return IN VARCHAR, -- 2
832 p_pers_resp_for_deduction IN VARCHAR2, -- 75
833 p_pers_designation IN VARCHAR2, -- 20
834 p_tot_tax_dedected_challan IN NUMBER, -- 14, DECIMAL
835 p_tot_tax_dedected_deductee IN NUMBER, -- 14, DECIMAL
836 -- added. Harshita for Bug 5096787
840 p_ack_num_tan_app IN NUMBER DEFAULT NULL,
837 p_filler1 IN DATE DEFAULT NULL,
838 p_filler2 IN NUMBER DEFAULT NULL,
839 p_filler3 IN VARCHAR2 DEFAULT NULL,
841 p_pro_rcpt_num_org_ret IN NUMBER DEFAULT NULL
842 -- ended. Harshita for Bug 5096787
843 ) IS
844
845 BEGIN
846
847 UTL_FILE.PUT_LINE(v_filehandle,
848 LPAD(p_line_number, s_line_number, v_pad_number)
849 ||v_debug_pad_char||RPAD(p_record_type, s_record_type, v_pad_char)
850 ||v_debug_pad_char||LPAD(p_batch_number, s_batch_number, v_pad_number)
851 ||v_debug_pad_char||LPAD(p_challan_count, s_challan_count, v_pad_number)
852 ||v_debug_pad_char||LPAD(p_deductee_count, s_deductee_count, v_pad_number)
853 ||v_debug_pad_char||RPAD(p_form_number, s_form_number, v_pad_char)
854 ||v_debug_pad_char||LPAD(nvl(p_rrr_number,0), s_rrr_number, v_pad_number)
855 ||v_debug_pad_char||LPAD(nvl(to_char(p_rrr_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
856 ||v_debug_pad_char||RPAD(nvl(p_deductor_tan,' '), s_deductor_tan, v_pad_char)
857 ||v_debug_pad_char||RPAD(nvl(p_pan_of_tan,' '), s_pan_of_tan, v_pad_char)
858 ||v_debug_pad_char||LPAD(nvl(p_assessment_year,0), s_assessment_year, v_pad_number)
859 ||v_debug_pad_char||LPAD(nvl(p_financial_year,0), s_financial_year, v_pad_number)
860 ||v_debug_pad_char||RPAD(nvl(p_deductor_name,' '), s_deductor_name, v_pad_char)
861 ||v_debug_pad_char||RPAD(nvl(p_tan_address1,' '), s_tan_address1, v_pad_char)
862 ||v_debug_pad_char||RPAD(nvl(p_tan_address2,' '), s_tan_address2, v_pad_char)
863 ||v_debug_pad_char||RPAD(nvl(p_tan_address3,' '), s_tan_address3, v_pad_char)
864 ||v_debug_pad_char||RPAD(nvl(p_tan_address4,' '), s_tan_address4, v_pad_char)
865 ||v_debug_pad_char||RPAD(nvl(p_tan_address5,' '), s_tan_address5, v_pad_char)
866 ||v_debug_pad_char||LPAD(nvl(p_tan_state,0), s_tan_state, v_pad_number)
867 ||v_debug_pad_char||LPAD(nvl(p_tan_pin,0), s_tan_pin, v_pad_number)
868 ||v_debug_pad_char||RPAD(nvl(p_chng_addr_since_last_return,' '), s_chng_addr_since_last_return, v_pad_char)
869 ||v_debug_pad_char||RPAD(nvl(p_status_of_deductor,' '), s_status_of_deductor, v_pad_char)
870 ||v_debug_pad_char||RPAD(nvl(p_quart_year_return,' '), s_quart_year_return, v_pad_char)
871 ||v_debug_pad_char||RPAD(nvl(p_pers_resp_for_deduction,' '), s_pers_resp_for_deduction, v_pad_char)
872 ||v_debug_pad_char||RPAD(nvl(p_pers_designation,' '), s_pers_designation, v_pad_char)
873 ||v_debug_pad_char||LPAD(formatAmount(p_tot_tax_dedected_challan), s_tot_tax_dedected_challan, v_pad_number)
874 ||v_debug_pad_char||LPAD(formatAmount(p_tot_tax_dedected_deductee), s_tot_tax_dedected_deductee, v_pad_number)
875 );
876
877 END create_batch_header;
878
879 PROCEDURE create_challan_detail(
880 p_line_number IN NUMBER, -- 6
881 p_record_type IN VARCHAR2, -- 2
882 p_batch_number IN NUMBER, -- 4
883 p_challan_slno IN NUMBER, -- 5
884 p_challan_section IN VARCHAR2, -- 5
885 p_amount_deducted IN NUMBER, -- 14, DECIMAL
886 p_challan_num IN VARCHAR2, -- 9
887 p_challan_date IN DATE, -- 8
888 p_bank_branch_code IN VARCHAR2, -- 7,
889 -- added. Harshita for Bug 5096787
890 p_amount_of_tds IN NUMBER DEFAULT NULL,
891 p_amount_of_surcharge IN NUMBER DEFAULT NULL,
892 p_amount_of_cess IN NUMBER DEFAULT NULL,
893 p_amount_of_int IN NUMBER DEFAULT NULL,
894 p_amount_of_oth IN NUMBER DEFAULT NULL,
895 p_check_number IN NUMBER DEFAULT NULL,
896 p_tds_dep_by_book IN VARCHAR2 DEFAULT NULL,
897 p_filler4 IN VARCHAR2 DEFAULT NULL
898 -- added. Harshita for Bug 5096787
899 ) IS
900
901 BEGIN
902 -- Bug 6796765. Added by Lakshmi Gopalsami
903 -- Added upper for both p_challan_section and Sec.()
904 UTL_FILE.PUT_LINE(v_filehandle,
905 LPAD(p_line_number, s_line_number, v_pad_number)
906 ||v_debug_pad_char||RPAD(p_record_type, s_record_type, v_pad_char)
907 ||v_debug_pad_char||LPAD(p_batch_number, s_batch_number, v_pad_number)
908 ||v_debug_pad_char||LPAD(nvl(p_challan_slno,0), s_challan_slno, v_pad_number)
909 ||v_debug_pad_char||RPAD(getSectionCode(upper(p_challan_section), upper('Sec.()')), s_challan_section, v_pad_char)
910 ||v_debug_pad_char||LPAD(formatAmount(p_amount_deducted), s_amount_deducted, v_pad_number)
911 ||v_debug_pad_char||RPAD(nvl(p_challan_num,' '), s_challan_num, v_pad_char)
912 ||v_debug_pad_char||LPAD(nvl(to_char(p_challan_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
913 ||v_debug_pad_char||RPAD(nvl(p_bank_branch_code,' '), s_bank_branch_code, v_pad_char)
914 );
915
916 END create_challan_detail;
917
918 PROCEDURE create_deductee_detail(
919 p_line_number IN NUMBER, -- 9
920 p_record_type IN VARCHAR2, -- 2
921 p_batch_number IN NUMBER, -- 9
922 p_deductee_slno IN NUMBER, -- 5
923 p_deductee_section IN VARCHAR2, -- 5
924 p_deductee_code IN VARCHAR2, -- 2 01 for Companies and 02 for other than companies
925 p_deductee_pan IN VARCHAR2, -- 10
926 p_deductee_name IN VARCHAR2, -- 75
927 p_deductee_address1 IN VARCHAR2, -- 25
928 p_deductee_address2 IN VARCHAR2, -- 25
929 p_deductee_address3 IN VARCHAR2, -- 25
930 p_deductee_address4 IN VARCHAR2, -- 25
931 p_deductee_address5 IN VARCHAR2, -- 25
932 p_deductee_state IN VARCHAR2, -- 2
933 p_deductee_pin IN VARCHAR2, -- 6 /*Changed to VARCHAR2 - Bug 7494473*/
934 p_filler5 IN NUMBER, -- 14 Added for bug#4353842
938 p_tax_rate IN NUMBER, -- 4(2+2), DECIMAL
935 p_payment_amount IN NUMBER, -- 14 (12+2), DECIMAL
936 p_payment_date IN DATE, -- 8
937 p_book_ent_oth IN VARCHAR2, -- 1 Added for bug#4353842
939 p_filler6 IN VARCHAR2, -- 1 Added for bug#4353842
940 --p_grossing_up_factor IN VARCHAR2, -- 1 -- Obsoleted via bug # 4353842
941 p_tax_deducted IN NUMBER, -- 14(12+2), DECIMAL
942 p_tax_deducted_date IN DATE, -- 8
943 p_tax_payment_date IN DATE, -- 8
944 p_bank_branch_code IN VARCHAR2, -- 7
945 p_challan_no IN VARCHAR2, -- 9
946 p_tds_certificate_date IN DATE, -- 8
947 p_reason_for_nDeduction IN VARCHAR2, -- 1
948 p_filler7 IN NUMBER -- 14, DECIMAL
949 ) IS
950
951
952 BEGIN
953
954 -- Bug 6796765. Added by Lakshmi Gopalsami
955 -- Added upper for both p_deductee_section and Sec.()
956
957 UTL_FILE.PUT_LINE(v_filehandle,
958 LPAD(p_line_number, s_line_number, v_pad_number)
959 ||v_debug_pad_char||RPAD(p_record_type, s_record_type, v_pad_char)
960 ||v_debug_pad_char||LPAD(p_batch_number, s_batch_number, v_pad_number)
961 ||v_debug_pad_char||LPAD(nvl(p_deductee_slno,0), s_deductee_slno, v_pad_number)
962 ||v_debug_pad_char||RPAD(getSectionCode(upper(p_deductee_section),upper('Sec.()')), s_deductee_section, v_pad_char)-- bug#3708878
963 ||v_debug_pad_char||RPAD(nvl(p_deductee_code,' '), s_deductee_code, v_pad_char)
964 ||v_debug_pad_char||RPAD(nvl(p_deductee_pan,' '), s_deductee_pan, v_pad_char)
965 ||v_debug_pad_char||RPAD(nvl(p_deductee_name,' '), s_deductee_name, v_pad_char)
966 ||v_debug_pad_char||RPAD(nvl(p_deductee_address1,' '), s_deductee_address1, v_pad_char)
967 ||v_debug_pad_char||RPAD(nvl(p_deductee_address2,' '), s_deductee_address2, v_pad_char)
968 ||v_debug_pad_char||RPAD(nvl(p_deductee_address3,' '), s_deductee_address3, v_pad_char)
969 ||v_debug_pad_char||RPAD(nvl(p_deductee_address4,' '), s_deductee_address4, v_pad_char)
970 ||v_debug_pad_char||RPAD(nvl(p_deductee_address5,' '), s_deductee_address5, v_pad_char)
971 ||v_debug_pad_char||LPAD(nvl(p_deductee_state,'0'), s_deductee_state, v_pad_number)
972 ||v_debug_pad_char||LPAD(nvl(p_deductee_pin,0), s_deductee_pin, v_pad_number)
973 ||v_debug_pad_char||LPAD(formatAmount(p_filler5), s_filler, v_pad_number)
974 ||v_debug_pad_char||LPAD(formatAmount(p_payment_amount), s_payment_amount, v_pad_number)
975 ||v_debug_pad_char||LPAD(nvl(to_char(p_payment_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
976 ||v_debug_pad_char||RPAD(p_book_ent_oth , s_book_ent_oth, v_pad_char)
977 ||v_debug_pad_char||LPAD(formatAmount(p_tax_rate), s_tax_rate, v_pad_number)
978 ||v_debug_pad_char||RPAD(nvl(p_filler6,' '), s_filler6, v_pad_char)
979 ||v_debug_pad_char||LPAD(formatAmount(p_tax_deducted), s_tax_deducted, v_pad_number)
980 ||v_debug_pad_char||LPAD(nvl(to_char(p_tax_deducted_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
981 ||v_debug_pad_char||RPAD(nvl(p_bank_branch_code,' '), s_bank_branch_code, v_pad_char)
982 ||v_debug_pad_char||LPAD(nvl(to_char(p_tax_payment_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
983 ||v_debug_pad_char||RPAD(nvl(p_challan_no,' '), s_challan_no, v_pad_char)
984 ||v_debug_pad_char||LPAD(nvl(to_char(p_tds_certificate_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
985 ||v_debug_pad_char||RPAD(nvl(p_reason_for_nDeduction,' '), s_reason_for_nDeduction, v_pad_char)
986 ||v_debug_pad_char||LPAD(formatAmount(p_filler7), s_filler, v_pad_number) || v_chr13
987 );
988 END create_deductee_detail;
989
990 PROCEDURE create_fh(p_batch_id IN NUMBER) IS
991 v_req JAI_AP_ETDS_REQUESTS%rowtype;
992 BEGIN
993
994 -- File Header
995 SELECT * INTO v_req FROM JAI_AP_ETDS_REQUESTS WHERE batch_id = p_batch_id;
996
997 UTL_FILE.PUT_LINE(v_filehandle, 'Input Parameters to this Request:');
998 UTL_FILE.PUT_LINE(v_filehandle, '-------------------------------------------------');
999 UTL_FILE.PUT_LINE(v_filehandle,
1000 ' batch_id ->'||v_req.batch_id||fnd_global.local_chr(10)
1001 ||' request_id ->'||v_req.request_id||fnd_global.local_chr(10)
1002 ||' legal_entity_id ->'||v_req.legal_entity_id||fnd_global.local_chr(10)
1003 ||' operating_unit_id ->'||v_req.operating_unit_id||fnd_global.local_chr(10)
1004 ||' org_tan_number ->'||v_req.org_tan_number||fnd_global.local_chr(10)
1005 ||' financial_year ->'||v_req.financial_year||fnd_global.local_chr(10)
1006 ||' tax_authority_id ->'||v_req.tax_authority_id||fnd_global.local_chr(10)
1007 ||' tax_authority_site_id ->'||v_req.tax_authority_site_id||fnd_global.local_chr(10)
1008 ||' organization_id ->'||v_req.organization_id||fnd_global.local_chr(10)
1009 ||' deductor_name ->'||v_req.deductor_name||fnd_global.local_chr(10)
1010 ||' deductor_state ->'||v_req.deductor_state||fnd_global.local_chr(10)
1011 ||' addr_changed_since_last_ret->'||v_req.addr_changed_since_last_ret||fnd_global.local_chr(10)
1012 ||' deductor_status ->'||v_req.deductor_status||fnd_global.local_chr(10)
1013 ||' person_resp_for_deduction ->'||v_req.person_resp_for_deduction||fnd_global.local_chr(10)
1014 ||' designation_of_pers_resp ->'||v_req.designation_of_pers_resp||fnd_global.local_chr(10)
1015 ||' challan_start_date ->'||v_req.challan_start_date||fnd_global.local_chr(10)
1016 ||' challan_end_date ->'||v_req.challan_end_date||fnd_global.local_chr(10)
1017 ||' file_path ->'||v_req.file_path||fnd_global.local_chr(10)
1018 ||' filename ->'||v_req.filename||fnd_global.local_chr(10)
1019 );
1020
1021
1022 UTL_FILE.PUT_LINE(v_filehandle,
1023 LPAD('LineNo', s_line_number, v_pad_char)
1024 ||v_pad_char||RPAD('RT', s_record_type, v_pad_char)
1025 ||v_pad_char||RPAD('FT', s_file_type, v_pad_char)
1026 ||v_pad_char||RPAD('UT', s_upload_type, v_pad_char)
1027 ||v_pad_char||LPAD('FileDate', s_date, v_pad_char)
1028 ||v_pad_char||LPAD('FSeqNo', s_file_sequence_number, v_pad_char)
1029 ||v_pad_char||RPAD('Org Tan', s_deductor_tan, v_pad_char)
1030 ||v_pad_char||LPAD('NoOfBatches', s_number_of_batches, v_pad_char)
1031 );
1032 UTL_FILE.PUT_LINE(v_filehandle,
1033 LPAD(v_underline_char, s_line_number, v_underline_char)
1034 ||v_pad_char||RPAD(v_underline_char, s_record_type, v_underline_char)
1035 ||v_pad_char||RPAD(v_underline_char, s_file_type, v_underline_char)
1036 ||v_pad_char||RPAD(v_underline_char, s_upload_type, v_underline_char)
1037 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1038 ||v_pad_char||LPAD(v_underline_char, s_file_sequence_number, v_underline_char)
1039 ||v_pad_char||RPAD(v_underline_char, s_deductor_tan, v_underline_char)
1040 ||v_pad_char||LPAD(v_underline_char, s_number_of_batches, v_underline_char)
1041 );
1042 END create_fh;
1043
1044 PROCEDURE create_quarterly_fh
1045 ( p_batch_id IN NUMBER,
1046 p_period IN VARCHAR2,
1047 p_RespPers_flat_no IN VARCHAR2 , -- Bug 6030953
1048 p_RespPers_prem_bldg IN VARCHAR2 , -- Bug 6030953
1049 p_RespPers_rd_st_lane IN VARCHAR2 , -- Bug 6030953
1050 p_RespPers_area_loc IN VARCHAR2 , -- Bug 6030953
1051 p_RespPers_tn_cty_dt IN VARCHAR2 , -- Bug 6030953
1052 p_RespPersState IN VARCHAR2,
1053 p_RespPersPin IN NUMBER,
1054 p_RespPers_tel_no IN VARCHAR2 , -- Bug 6030953
1055 p_RespPers_email IN VARCHAR2 , -- Bug 6030953
1056 p_RespPersAddrChange IN VARCHAR2
1057 )
1058 IS
1059 v_req JAI_AP_ETDS_REQUESTS%rowtype;
1060 BEGIN
1061 SELECT * INTO v_req FROM JAI_AP_ETDS_REQUESTS WHERE batch_id = p_batch_id;
1062
1063 UTL_FILE.PUT_LINE(v_filehandle, 'Input Parameters to this Request:');
1064 UTL_FILE.PUT_LINE(v_filehandle, '-------------------------------------------------');
1065 UTL_FILE.PUT_LINE(v_filehandle,
1066 ' batch_id ->'||v_req.batch_id||v_chr10
1067 ||' request_id ->'||v_req.request_id||v_chr10
1068 ||' legal_entity_id ->'||v_req.legal_entity_id||v_chr10
1069 ||' operating_unit_id ->'||v_req.operating_unit_id||v_chr10
1070 ||' org_tan_number ->'||v_req.org_tan_number||v_chr10
1071 ||' financial_year ->'||v_req.financial_year||v_chr10
1072 ||' tax_authority_id ->'||v_req.tax_authority_id||v_chr10
1073 ||' tax_authority_site_id ->'||v_req.tax_authority_site_id||v_chr10
1074 ||' organization_id ->'||v_req.organization_id||v_chr10
1075 ||' deductor_name ->'||v_req.deductor_name||v_chr10
1076 ||' deductor_state ->'||v_req.deductor_state||v_chr10
1077 ||' addr_changed_since_last_ret->'||v_req.addr_changed_since_last_ret||v_chr10
1078 ||' deductor_status ->'||v_req.deductor_status||v_chr10
1079 ||' person_resp_for_deduction ->'||v_req.person_resp_for_deduction||v_chr10
1080 ||' designation_of_pers_resp ->'||v_req.designation_of_pers_resp||v_chr10
1081 ||' challan_start_date ->'||v_req.challan_start_date||v_chr10
1082 ||' challan_end_date ->'||v_req.challan_end_date||v_chr10
1083 ||' file_path ->'||v_req.file_path||v_chr10
1084 ||' filename ->'||v_req.filename||v_chr10
1085 ||' Period ->'||p_period||v_chr10
1086 ||' RespPerson''s Flat No ->'||p_RespPers_Flat_no||v_chr10
1087 ||' RespPerson''s Premises/Bldg ->'||p_RespPers_prem_bldg||v_chr10
1088 ||' RespPerson''s Rd/St/Lane ->'||p_RespPers_rd_st_lane||v_chr10
1089 ||' RespPerson''s Area/Loc ->'||p_RespPers_area_loc||v_chr10
1090 ||' RespPerson''s Tn/Cty/Dt ->'||p_RespPers_tn_cty_dt||v_chr10
1091 ||' RespPerson''s State ->'||p_RespPersState||v_chr10
1092 ||' RespPerson''s Pin ->'||p_RespPersPin||v_chr10
1093 ||' RespPerson''s Telephone no ->'||p_RespPers_tel_no||v_chr10
1094 ||' RespPerson''s Email ->'||p_RespPers_email||v_chr10
1095 ||' RespPerson''s Addr Changed ->'||p_RespPersAddrChange||v_chr10
1096 );
1097
1098
1099 UTL_FILE.PUT_LINE(v_filehandle,
1100 LPAD('Line No', sq_len_9, v_quart_pad) || v_pad_char ||
1101 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
1102 LPAD('FT', sq_len_4, v_quart_pad) || v_pad_char ||
1106 LPAD('U', sq_len_1, v_quart_pad) || v_pad_char ||
1103 LPAD('UT', sq_len_2, v_quart_pad) || v_pad_char ||
1104 LPAD('FileDate', sq_len_8, v_quart_pad) || v_pad_char ||
1105 LPAD('SeqNo', sq_len_5, v_quart_pad) || v_pad_char ||
1107 LPAD('TAN', sq_len_10, v_quart_pad) || v_pad_char ||
1108 LPAD('Batch Cnt', sq_len_9, v_quart_pad) || v_pad_char ||
1109 LPAD('RH', sq_len_2, v_quart_pad) || v_pad_char ||
1110 LPAD('FV', sq_len_2, v_quart_pad) || v_pad_char ||
1111 LPAD('FH', sq_len_2, v_quart_pad) || v_pad_char ||
1112 LPAD('SV', sq_len_2, v_quart_pad) || v_pad_char ||
1113 LPAD('SH', sq_len_2, v_quart_pad) || v_pad_char ||
1114 LPAD('SV', sq_len_2, v_quart_pad) || v_pad_char ||
1115 LPAD('SH', sq_len_2, v_quart_pad) );
1116
1117 UTL_FILE.PUT_LINE(v_filehandle,
1118 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
1119 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1120 LPAD(v_underline_char, sq_len_4, v_underline_char) || v_pad_char ||
1121 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1122 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
1123 LPAD(v_underline_char, sq_len_5, v_underline_char) || v_pad_char ||
1124 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
1125 LPAD(v_underline_char, sq_len_10,v_underline_char) || v_pad_char ||
1126 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
1127 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1128 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1129 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1130 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1131 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1132 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1133 LPAD(v_underline_char, sq_len_2, v_underline_char) );
1134
1135 END create_quarterly_fh;
1136
1137 PROCEDURE create_bh IS
1138 BEGIN
1139
1140 -- Batch Header
1141 UTL_FILE.PUT_LINE(v_filehandle, fnd_global.local_chr(10) );
1142 UTL_FILE.PUT_LINE(v_filehandle,
1143 LPAD('LineNo', s_line_number, v_pad_char)
1144 ||v_pad_char||RPAD('RT', s_record_type, v_pad_char)
1145 ||v_pad_char||LPAD('BNo', s_batch_number, v_pad_char)
1146 ||v_pad_char||LPAD('Ch.Cnt', s_challan_count, v_pad_char)
1147 ||v_pad_char||LPAD('Dd.Cnt', s_deductee_count, v_pad_char)
1148 ||v_pad_char||RPAD('FNo.', s_form_number, v_pad_char)
1149 ||v_pad_char||LPAD('RRR No', s_rrr_number, v_pad_char)
1150 ||v_pad_char||LPAD('RRR Date', s_date, v_pad_char)
1151 ||v_pad_char||RPAD('Org Tan', s_deductor_tan, v_pad_char)
1152 ||v_pad_char||RPAD('Org Pan', s_pan_of_tan, v_pad_char)
1153 ||v_pad_char||LPAD('Ass.Year', s_assessment_year, v_pad_char)
1154 ||v_pad_char||LPAD('FinYr.', s_financial_year, v_pad_char)
1155 ||v_pad_char||RPAD('Org Name', s_deductor_name, v_pad_char)
1156 ||v_pad_char||RPAD('Tan Addr1', s_tan_address1, v_pad_char)
1157 ||v_pad_char||RPAD('Tan Addr2', s_tan_address2, v_pad_char)
1158 ||v_pad_char||RPAD('Tan Addr3', s_tan_address3, v_pad_char)
1159 ||v_pad_char||RPAD('Tan Addr4', s_tan_address4, v_pad_char)
1160 ||v_pad_char||RPAD('Tan Addr5', s_tan_address5, v_pad_char)
1161 ||v_pad_char||LPAD('State', s_tan_state, v_pad_char)
1162 ||v_pad_char||LPAD('TanPin', s_tan_pin, v_pad_char)
1163 ||v_pad_char||RPAD('ChangeOfAddr', s_chng_addr_since_last_return, v_pad_char)
1164 ||v_pad_char||RPAD('DS', s_status_of_deductor, v_pad_char)
1165 ||v_pad_char||RPAD('QY', s_quart_year_return, v_pad_char)
1166 ||v_pad_char||RPAD('PersonResponsibleForDeduction', s_pers_resp_for_deduction, v_pad_char)
1167 ||v_pad_char||RPAD('PersonDesignation', s_pers_designation, v_pad_char)
1168 ||v_pad_char||LPAD('TotChallanTax', s_tot_tax_dedected_challan, v_pad_char)
1169 ||v_pad_char||LPAD('TotDeducteeTax', s_tot_tax_dedected_deductee, v_pad_char)
1170 );
1171 UTL_FILE.PUT_LINE(v_filehandle,
1172 LPAD(v_underline_char, s_line_number, v_underline_char)
1173 ||v_pad_char||RPAD(v_underline_char, s_record_type, v_underline_char)
1174 ||v_pad_char||LPAD(v_underline_char, s_batch_number, v_underline_char)
1175 ||v_pad_char||LPAD(v_underline_char, s_challan_count, v_underline_char)
1176 ||v_pad_char||LPAD(v_underline_char, s_deductee_count, v_underline_char)
1177 ||v_pad_char||RPAD(v_underline_char, s_form_number, v_underline_char)
1178 ||v_pad_char||LPAD(v_underline_char, s_rrr_number, v_underline_char)
1179 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1180 ||v_pad_char||RPAD(v_underline_char, s_deductor_tan, v_underline_char)
1181 ||v_pad_char||RPAD(v_underline_char, s_pan_of_tan, v_underline_char)
1182 ||v_pad_char||LPAD(v_underline_char, s_assessment_year, v_underline_char)
1183 ||v_pad_char||LPAD(v_underline_char, s_financial_year, v_underline_char)
1184 ||v_pad_char||RPAD(v_underline_char, s_deductor_name, v_underline_char)
1185 ||v_pad_char||RPAD(v_underline_char, s_tan_address1, v_underline_char)
1186 ||v_pad_char||RPAD(v_underline_char, s_tan_address2, v_underline_char)
1187 ||v_pad_char||RPAD(v_underline_char, s_tan_address3, v_underline_char)
1188 ||v_pad_char||RPAD(v_underline_char, s_tan_address4, v_underline_char)
1189 ||v_pad_char||RPAD(v_underline_char, s_tan_address5, v_underline_char)
1190 ||v_pad_char||LPAD(v_underline_char, s_tan_state, v_underline_char)
1191 ||v_pad_char||LPAD(v_underline_char, s_tan_pin, v_underline_char)
1192 ||v_pad_char||RPAD(v_underline_char, s_chng_addr_since_last_return, v_underline_char)
1193 ||v_pad_char||RPAD(v_underline_char, s_status_of_deductor, v_underline_char)
1194 ||v_pad_char||RPAD(v_underline_char, s_quart_year_return, v_underline_char)
1195 ||v_pad_char||RPAD(v_underline_char, s_pers_resp_for_deduction, v_underline_char)
1196 ||v_pad_char||RPAD(v_underline_char, s_pers_designation, v_underline_char)
1197 ||v_pad_char||LPAD(v_underline_char, s_tot_tax_dedected_challan, v_underline_char)
1198 ||v_pad_char||LPAD(v_underline_char, s_tot_tax_dedected_deductee, v_underline_char)
1199 );
1200 END create_bh;
1201
1202 PROCEDURE create_quarterly_bh IS
1203 BEGIN
1204 UTL_FILE.PUT_LINE(v_filehandle, ' ' ) ;
1205
1206 UTL_FILE.PUT_LINE(v_filehandle,
1207 LPAD('Line No', sq_len_9, v_quart_pad) || v_pad_char ||
1208 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
1209 LPAD('Batch No', sq_len_9, v_quart_pad) || v_pad_char ||
1210 LPAD('ChallCnt', sq_len_9, v_quart_pad) || v_pad_char ||
1211 LPAD('FN', sq_len_4, v_quart_pad) || v_pad_char ||
1212 LPAD('TT', sq_len_2, v_quart_pad) || v_pad_char ||
1213 LPAD('BI', sq_len_2, v_quart_pad) || v_pad_char ||
1214 LPAD('OR', sq_len_2, v_quart_pad) || v_pad_char ||
1215 LPAD('PR', sq_len_2, v_quart_pad) || v_pad_char ||
1216 LPAD('RN', sq_len_2, v_quart_pad) || v_pad_char ||
1217 LPAD('RD', sq_len_2, v_quart_pad) || v_pad_char ||
1218 LPAD('LT', sq_len_2, v_quart_pad) || v_pad_char ||
1219 LPAD('Ded TAN', sq_len_10, v_quart_pad) || v_pad_char ||
1220 LPAD('F1', sq_len_2, v_quart_pad) || v_pad_char ||
1221 LPAD('Ded PAN', sq_len_10, v_quart_pad) || v_pad_char ||
1222 LPAD('Ass.Yr', sq_len_6, v_quart_pad) || v_pad_char ||
1223 LPAD('Fin.Yr', sq_len_6, v_quart_pad) || v_pad_char ||
1224 LPAD('PD', sq_len_2, v_quart_pad) || v_pad_char ||
1225 LPAD('Deductor Name', sq_len_75, v_quart_pad) || v_pad_char ||
1226 LPAD('Deductor Branch', sq_len_75, v_quart_pad) || v_pad_char ||
1227 LPAD('Deductor Addr1', sq_len_25, v_quart_pad) || v_pad_char ||
1228 LPAD('Deductor Addr2', sq_len_25, v_quart_pad) || v_pad_char ||
1229 LPAD('Deductor Addr3', sq_len_25, v_quart_pad) || v_pad_char ||
1230 LPAD('Deductor Addr4', sq_len_25, v_quart_pad) || v_pad_char ||
1231 LPAD('Deductor Addr5', sq_len_25, v_quart_pad) || v_pad_char ||
1232 LPAD('DS', sq_len_2, v_quart_pad) || v_pad_char ||
1233 LPAD('DedPIN', sq_len_6, v_quart_pad) || v_pad_char ||
1234 LPAD('Deductor Email', sq_len_75, v_quart_pad) || v_pad_char ||
1235 LPAD('DedSTD', sq_len_5, v_quart_pad) || v_pad_char ||
1236 LPAD('Ded Phone', sq_len_10, v_quart_pad) || v_pad_char ||
1237 LPAD('C', sq_len_1, v_quart_pad) || v_pad_char ||
1238 LPAD('T', sq_len_1, v_quart_pad) || v_pad_char ||
1239 LPAD('RespPerson Name', sq_len_75, v_quart_pad) || v_pad_char ||
1240 LPAD('RespPerson Desg', sq_len_20, v_quart_pad) || v_pad_char ||
1241 LPAD('RespPerson Addr1', sq_len_25, v_quart_pad) || v_pad_char ||
1242 LPAD('RespPerson Addr2', sq_len_25, v_quart_pad) || v_pad_char ||
1243 LPAD('RespPerson Addr3', sq_len_25, v_quart_pad) || v_pad_char ||
1244 LPAD('RespPerson Addr4', sq_len_25, v_quart_pad) || v_pad_char ||
1245 LPAD('RespPerson Addr5', sq_len_25, v_quart_pad) || v_pad_char ||
1246 LPAD('RS', sq_len_2, v_quart_pad) || v_pad_char ||
1247 LPAD('ResPIN', sq_len_6, v_quart_pad) || v_pad_char ||
1248 LPAD('RespPerson Email', sq_len_75, v_quart_pad) || v_pad_char ||
1249 LPAD('Remark', sq_len_75, v_quart_pad) || v_pad_char ||
1250 LPAD('ResSTD', sq_len_5, v_quart_pad) || v_pad_char ||
1251 LPAD('ResPhone', sq_len_10, v_quart_pad) || v_pad_char ||
1252 LPAD('C', sq_len_1, v_quart_pad) || v_pad_char ||
1253 LPAD('TotChallanTax', sq_len_15, v_quart_pad) || v_pad_char ||
1254 LPAD('TC', sq_len_2, v_quart_pad) || v_pad_char ||
1255 LPAD('SC', sq_len_2, v_quart_pad) || v_pad_char ||
1256 LPAD('GT', sq_len_2, v_quart_pad) || v_pad_char ||
1257 LPAD('A', sq_len_1, v_quart_pad) || v_pad_char ||
1258 LPAD('AO Approval No', sq_len_15, v_quart_pad) || v_pad_char ||
1259 LPAD('RH', sq_len_2, v_quart_pad)
1260 );
1261
1262 UTL_FILE.PUT_LINE(v_filehandle,
1263 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
1264 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1265 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
1266 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
1267 LPAD(v_underline_char, sq_len_4, v_underline_char) || v_pad_char ||
1268 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1269 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1270 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1271 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1275 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
1272 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1273 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1274 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1276 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1277 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
1278 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
1279 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
1280 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1281 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
1282 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
1283 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
1284 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
1285 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
1286 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
1287 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
1288 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1289 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
1290 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
1291 LPAD(v_underline_char, sq_len_5, v_underline_char) || v_pad_char ||
1292 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
1293 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
1294 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
1295 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
1296 LPAD(v_underline_char, sq_len_20, v_underline_char) || v_pad_char ||
1297 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
1298 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
1299 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
1300 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
1301 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
1302 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1303 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
1304 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
1305 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
1306 LPAD(v_underline_char, sq_len_5, v_underline_char) || v_pad_char ||
1307 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
1308 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
1309 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
1310 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1311 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1312 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
1313 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
1314 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
1315 LPAD(v_underline_char, sq_len_2, v_underline_char)
1316 );
1317
1318 END create_quarterly_bh;
1319
1320 PROCEDURE create_quarterly_cd IS
1321 BEGIN
1322 UTL_FILE.PUT_LINE(v_filehandle, ' ' ) ;
1323 UTL_FILE.PUT_LINE(v_filehandle,
1324 LPAD('Line No', sq_len_9 , v_quart_pad) || v_pad_char ||
1325 LPAD('RT', sq_len_2 , v_quart_pad) || v_pad_char ||
1326 LPAD('Batch No', sq_len_9 , v_quart_pad) || v_pad_char ||
1327 LPAD('Chall No', sq_len_9 , v_quart_pad) || v_pad_char ||
1328 LPAD('DeductCnt', sq_len_9 , v_quart_pad) || v_pad_char ||
1329 LPAD('I', sq_len_1 , v_quart_pad) || v_pad_char ||
1330 LPAD('U', sq_len_2 , v_quart_pad) || v_pad_char ||
1331 LPAD('F2', sq_len_2 , v_quart_pad) || v_pad_char ||
1332 LPAD('F3', sq_len_2 , v_quart_pad) || v_pad_char ||
1333 LPAD('F4', sq_len_2 , v_quart_pad) || v_pad_char ||
1337 LPAD('TrnsVouch', sq_len_9 , v_quart_pad) || v_pad_char ||
1334 LPAD('LC', sq_len_2 , v_quart_pad) || v_pad_char ||
1335 LPAD('Ch', sq_len_5 , v_quart_pad) || v_pad_char ||
1336 LPAD('LV', sq_len_2 , v_quart_pad) || v_pad_char ||
1338 LPAD('LB', sq_len_2 , v_quart_pad) || v_pad_char ||
1339 LPAD('Bank Br', sq_len_7 , v_quart_pad) || v_pad_char ||
1340 LPAD('LD', sq_len_2 , v_quart_pad) || v_pad_char ||
1341 LPAD('CH. Date', sq_len_8 , v_quart_pad) || v_pad_char ||
1342 LPAD('F5', sq_len_2 , v_quart_pad) || v_pad_char ||
1343 LPAD('F6', sq_len_2 , v_quart_pad) || v_pad_char ||
1344 LPAD('Sec', sq_len_3 , v_quart_pad) || v_pad_char ||
1345 LPAD('Oltas Tax', sq_len_15 , v_quart_pad) || v_pad_char ||
1346 LPAD('Oltas Sur', sq_len_15 , v_quart_pad) || v_pad_char ||
1347 LPAD('Oltas Cess', sq_len_15 , v_quart_pad) || v_pad_char ||
1348 LPAD('Oltas Interest', sq_len_15 , v_quart_pad) || v_pad_char ||
1349 LPAD('Oltas OtherAmt', sq_len_15 , v_quart_pad) || v_pad_char ||
1350 LPAD('Total Deposit', sq_len_15 , v_quart_pad) || v_pad_char ||
1351 LPAD('LD', sq_len_2 , v_quart_pad) || v_pad_char ||
1352 LPAD('TotTax Deposit', sq_len_15 , v_quart_pad) || v_pad_char ||
1353 LPAD('TDS Income Tax', sq_len_15 , v_quart_pad) || v_pad_char ||
1354 LPAD('TDS Surcharge', sq_len_15 , v_quart_pad) || v_pad_char ||
1355 LPAD('TDS Cess', sq_len_15 , v_quart_pad) || v_pad_char ||
1356 LPAD('Total TDS ', sq_len_15 , v_quart_pad) || v_pad_char ||
1357 LPAD('TDS Interest', sq_len_15 , v_quart_pad) || v_pad_char ||
1358 LPAD('TDS OtherAmt', sq_len_15 , v_quart_pad) || v_pad_char ||
1359 LPAD('Cheque/DD', sq_len_15 , v_quart_pad) || v_pad_char ||
1360 LPAD('B', sq_len_1 , v_quart_pad) || v_pad_char ||
1361 LPAD('Remarks', sq_len_14 , v_quart_pad) || v_pad_char ||
1362 LPAD('RH', sq_len_2 , v_quart_pad)
1363 );
1364
1365 UTL_FILE.PUT_LINE(v_filehandle,
1366 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
1367 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1368 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
1369 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
1370 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
1371 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
1372 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1373 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1374 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1375 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1376 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1377 LPAD(v_underline_char , sq_len_5 , v_underline_char) || v_pad_char ||
1378 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1379 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
1380 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1381 LPAD(v_underline_char , sq_len_7 , v_underline_char) || v_pad_char ||
1382 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1383 LPAD(v_underline_char , sq_len_8 , v_underline_char) || v_pad_char ||
1384 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1385 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1386 LPAD(v_underline_char , sq_len_3 , v_underline_char) || v_pad_char ||
1387 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1388 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1389 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1390 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1391 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1392 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1393 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1394 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1395 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1396 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1397 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1398 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1399 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1400 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1401 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1405 );
1402 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
1403 LPAD(v_underline_char , sq_len_14 , v_underline_char) || v_pad_char ||
1404 LPAD(v_underline_char , sq_len_2 , v_underline_char)
1406
1407 END create_quarterly_cd;
1408
1409 PROCEDURE create_quarterly_dd IS
1410 BEGIN
1411 UTL_FILE.PUT_LINE(v_filehandle, ' ' ) ;
1412 UTL_FILE.PUT_LINE(v_filehandle,
1413 LPAD('Line No', sq_len_9, v_quart_pad) || v_pad_char ||
1414 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
1415 LPAD('Batch No', sq_len_9, v_quart_pad) || v_pad_char ||
1416 LPAD('ChallNo', sq_len_9, v_quart_pad) || v_pad_char ||
1417 LPAD('DedRecNo', sq_len_9, v_quart_pad) || v_pad_char ||
1418 LPAD('M', sq_len_1, v_quart_pad) || v_pad_char ||
1419 LPAD('EN', sq_len_2, v_quart_pad) || v_pad_char ||
1420 LPAD('C', sq_len_1, v_quart_pad) || v_pad_char ||
1421 LPAD('LP', sq_len_2, v_quart_pad) || v_pad_char ||
1422 LPAD('Emp Pan', sq_len_10, v_quart_pad) || v_pad_char ||
1423 LPAD('LP', sq_len_2, v_quart_pad) || v_pad_char ||
1424 LPAD('PAN Ref No', sq_len_10, v_quart_pad) || v_pad_char ||
1425 LPAD('Employee Name', sq_len_75, v_quart_pad) || v_pad_char ||
1426 LPAD('TDS Income Tax ', sq_len_15, v_quart_pad) || v_pad_char ||
1427 LPAD('TDS Surcharge', sq_len_15, v_quart_pad) || v_pad_char ||
1428 LPAD('TDS Cess', sq_len_15, v_quart_pad) || v_pad_char ||
1429 LPAD('TDS Total', sq_len_15, v_quart_pad) || v_pad_char ||
1430 LPAD('LT', sq_len_2, v_quart_pad) || v_pad_char ||
1431 LPAD('TotTax Deposit', sq_len_15, v_quart_pad) || v_pad_char ||
1432 LPAD('LT', sq_len_2, v_quart_pad) || v_pad_char ||
1433 LPAD('TP', sq_len_2, v_quart_pad) || v_pad_char ||
1434 LPAD('Payment Amt', sq_len_15, v_quart_pad) || v_pad_char ||
1435 LPAD('Pay Dt', sq_len_8, v_quart_pad) || v_pad_char ||
1436 LPAD('TaxDedDt', sq_len_8, v_quart_pad) || v_pad_char ||
1437 LPAD('DD', sq_len_2, v_quart_pad) || v_pad_char ||
1438 LPAD('Tax Rt', sq_len_7, v_quart_pad) || v_pad_char ||
1439 LPAD('GI', sq_len_2, v_quart_pad) || v_pad_char ||
1440 LPAD('B', sq_len_1, v_quart_pad) || v_pad_char ||
1441 LPAD('TD', sq_len_2, v_quart_pad) || v_pad_char ||
1442 LPAD('R', sq_len_1, v_quart_pad) || v_pad_char ||
1443 LPAD('Remarks 2', sq_len_75, v_quart_pad) || v_pad_char ||
1444 LPAD('Remarks 3', sq_len_14, v_quart_pad) || v_pad_char ||
1445 LPAD('RH', sq_len_2, v_quart_pad)
1446 );
1447
1448 UTL_FILE.PUT_LINE(v_filehandle,
1449 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
1450 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1451 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
1452 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
1453 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
1454 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
1455 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1456 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
1457 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1458 LPAD(v_underline_char , sq_len_10 , v_underline_char) || v_pad_char ||
1459 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1460 LPAD(v_underline_char , sq_len_10 , v_underline_char) || v_pad_char ||
1461 LPAD(v_underline_char , sq_len_75 , v_underline_char) || v_pad_char ||
1462 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1463 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1464 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1465 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1466 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1467 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1468 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1469 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1470 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
1471 LPAD(v_underline_char , sq_len_8 , v_underline_char) || v_pad_char ||
1472 LPAD(v_underline_char , sq_len_8 , v_underline_char) || v_pad_char ||
1473 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1474 LPAD(v_underline_char , sq_len_7 , v_underline_char) || v_pad_char ||
1475 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1479 LPAD(v_underline_char , sq_len_75 , v_underline_char) || v_pad_char ||
1476 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
1477 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
1478 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
1480 LPAD(v_underline_char , sq_len_14 , v_underline_char) || v_pad_char ||
1481 LPAD(v_underline_char , sq_len_2 , v_underline_char)
1482 );
1483
1484 END create_quarterly_dd;
1485
1486
1487 PROCEDURE create_cd IS
1488 BEGIN
1489
1490 -- Challan Detail
1491 UTL_FILE.PUT_LINE(v_filehandle, fnd_global.local_chr(10) );
1492 UTL_FILE.PUT_LINE(v_filehandle,
1493 LPAD('LineNo', s_line_number, v_pad_char)
1494 ||v_pad_char||RPAD('RT', s_record_type, v_pad_char)
1495 ||v_pad_char||LPAD('B.No', s_batch_number, v_pad_char)
1496 ||v_pad_char||LPAD('CSlNo', s_challan_slno, v_pad_char)
1497 ||v_pad_char||RPAD('Secn.', s_challan_section, v_pad_char)
1498 ||v_pad_char||LPAD('AmtDeducted', s_amount_deducted, v_pad_char)
1499 ||v_pad_char||RPAD('Ch.Num.', s_challan_num, v_pad_char)
1500 ||v_pad_char||LPAD('Ch.Date', s_date, v_pad_char)
1501 ||v_pad_char||RPAD('BankBrCode', s_bank_branch_code, v_pad_char)
1502 );
1503 UTL_FILE.PUT_LINE(v_filehandle,
1504 LPAD(v_underline_char, s_line_number, v_underline_char)
1505 ||v_pad_char||RPAD(v_underline_char, s_record_type, v_underline_char)
1506 ||v_pad_char||LPAD(v_underline_char, s_batch_number, v_underline_char)
1507 ||v_pad_char||LPAD(v_underline_char, s_challan_slno, v_underline_char)
1508 ||v_pad_char||RPAD(v_underline_char, s_challan_section, v_underline_char)
1509 ||v_pad_char||LPAD(v_underline_char, s_amount_deducted, v_underline_char)
1510 ||v_pad_char||RPAD(v_underline_char, s_challan_num, v_underline_char)
1511 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1512 ||v_pad_char||RPAD(v_underline_char, s_bank_branch_code, v_underline_char)
1513 );
1514 END create_cd;
1515
1516 PROCEDURE create_dd IS
1517 BEGIN
1518
1519 /* Bug 4353842. Added by Lakshmi Gopalsami
1520 p_grossing_up_factor Obsoleted via bug # 4353842
1521 */
1522
1523 -- Deductee Detail
1524 UTL_FILE.PUT_LINE(v_filehandle, v_chr10 );
1525 UTL_FILE.PUT_LINE(v_filehandle,
1526 LPAD('LineNo', s_line_number, v_pad_char)
1527 ||v_pad_char||RPAD('RT', s_record_type, v_pad_char)
1528 ||v_pad_char||LPAD('B.No.', s_batch_number, v_pad_char)
1529 ||v_pad_char||LPAD('DSlNo', s_deductee_slno, v_pad_char)
1530 ||v_pad_char||RPAD('Secn.', s_deductee_section, v_pad_char)
1531 ||v_pad_char||RPAD('DCode', s_deductee_code, v_pad_char)
1532 ||v_pad_char||RPAD('DteePan', s_deductee_pan, v_pad_char)
1533 ||v_pad_char||RPAD('Deductee Name', s_deductee_name, v_pad_char)
1534 ||v_pad_char||RPAD('Deductee Addr1', s_deductee_address1, v_pad_char)
1535 ||v_pad_char||RPAD('Deductee Addr2', s_deductee_address2, v_pad_char)
1536 ||v_pad_char||RPAD('Deductee Addr3', s_deductee_address3, v_pad_char)
1537 ||v_pad_char||RPAD('Deductee Addr4', s_deductee_address4, v_pad_char)
1538 ||v_pad_char||RPAD('Deductee Addr5', s_deductee_address5, v_pad_char)
1539 ||v_pad_char||LPAD('DState', s_deductee_state, v_pad_char)
1540 ||v_pad_char||LPAD('DtePin', s_deductee_pin, v_pad_char)
1541 ||v_pad_char||LPAD('Filler5', s_filler, v_pad_char)
1542 ||v_pad_char||LPAD('Pay. Amount', s_payment_amount, v_pad_char)
1543 ||v_pad_char||LPAD('Pay. Date', s_date, v_pad_char)
1544 ||v_pad_char||LPAD('PBE', s_book_ent_oth , v_pad_char)
1545 ||v_pad_char||LPAD('TxRt', s_tax_rate, v_pad_char)
1546 --||v_pad_char||RPAD('Gf', s_grossing_up_factor, v_pad_char)
1547 ||v_pad_char||LPAD('Filler6', s_filler6, v_pad_char)
1548 ||v_pad_char||LPAD('TxDeducted', s_tax_deducted, v_pad_char)
1549 ||v_pad_char||LPAD('TxDed.Dt', s_date, v_pad_date)
1550 ||v_pad_char||RPAD('BBRCode', s_bank_branch_code, v_pad_char)
1551 ||v_pad_char||LPAD('TxPay.Dt', s_date, v_pad_date)
1552 ||v_pad_char||RPAD('ChlnNo', s_challan_no, v_pad_char)
1553 ||v_pad_char||LPAD('TdsCrtDt', s_date, v_pad_char)
1554 ||v_pad_char||RPAD('R', s_reason_for_nDeduction, v_pad_char)
1555 ||v_pad_char||LPAD('Filler7', s_filler, v_pad_char)
1556 );
1557
1558 /* Bug 4353842. Added by Lakshmi Gopalsami
1559 p_grossing_up_factor Obsoleted via bug # 4353842
1560 */
1561
1562 UTL_FILE.PUT_LINE(v_filehandle,
1563 LPAD(v_underline_char, s_line_number, v_underline_char)
1564 ||v_pad_char||RPAD(v_underline_char, s_record_type, v_underline_char)
1565 ||v_pad_char||LPAD(v_underline_char, s_batch_number, v_underline_char)
1566 ||v_pad_char||LPAD(v_underline_char, s_deductee_slno, v_underline_char)
1567 ||v_pad_char||RPAD(v_underline_char, s_deductee_section, v_underline_char)
1568 ||v_pad_char||RPAD(v_underline_char, s_deductee_code, v_underline_char)
1572 ||v_pad_char||RPAD(v_underline_char, s_deductee_address2, v_underline_char)
1569 ||v_pad_char||RPAD(v_underline_char, s_deductee_pan, v_underline_char)
1570 ||v_pad_char||RPAD(v_underline_char, s_deductee_name, v_underline_char)
1571 ||v_pad_char||RPAD(v_underline_char, s_deductee_address1, v_underline_char)
1573 ||v_pad_char||RPAD(v_underline_char, s_deductee_address3, v_underline_char)
1574 ||v_pad_char||RPAD(v_underline_char, s_deductee_address4, v_underline_char)
1575 ||v_pad_char||RPAD(v_underline_char, s_deductee_address5, v_underline_char)
1576 ||v_pad_char||LPAD(v_underline_char, s_deductee_state, v_underline_char)
1577 ||v_pad_char||LPAD(v_underline_char, s_deductee_pin, v_underline_char)
1578 ||v_pad_char||LPAD(v_underline_char, s_filler, v_underline_char)
1579 ||v_pad_char||LPAD(v_underline_char, s_payment_amount, v_underline_char)
1580 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1581 ||v_pad_char||LPAD(v_underline_char, s_book_ent_oth , v_underline_char)
1582 ||v_pad_char||LPAD(v_underline_char, s_tax_rate, v_underline_char)
1583 ||v_pad_char||LPAD(v_underline_char, s_filler6, v_underline_char)
1584 --||v_pad_char||RPAD(v_underline_char, s_grossing_up_factor, v_underline_char)
1585 ||v_pad_char||LPAD(v_underline_char, s_tax_deducted, v_underline_char)
1586 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1587 ||v_pad_char||RPAD(v_underline_char, s_bank_branch_code, v_underline_char)
1588 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1589 ||v_pad_char||RPAD(v_underline_char, s_challan_no, v_underline_char)
1590 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1591 ||v_pad_char||RPAD(v_underline_char, s_reason_for_nDeduction, v_underline_char)
1592 ||v_pad_char||LPAD(v_underline_char, s_filler, v_underline_char)
1593 );
1594
1595 END create_dd;
1596
1597
1598 -- added, Harshita for Bug 5096787
1599
1600 -- eTDS Quarterly Data Generation Procedues
1601
1602 PROCEDURE create_quarterly_file_header(
1603 p_line_number IN NUMBER,
1604 p_record_type IN VARCHAR2,
1605 p_file_type IN VARCHAR2,
1606 p_upload_type IN VARCHAR2,
1607 p_file_creation_date IN DATE,
1608 p_file_sequence_number IN NUMBER,
1609 p_uploader_type IN VARCHAR2,
1610 p_deductor_tan IN VARCHAR2,
1611 p_number_of_batches IN NUMBER,
1612 p_fh_recordHash IN VARCHAR2,
1613 p_fh_fvuVersion IN VARCHAR2,
1614 p_fh_fileHash IN VARCHAR2,
1615 p_fh_samVersion IN VARCHAR2,
1616 p_fh_samHash IN VARCHAR2,
1617 p_fh_scmVersion IN VARCHAR2,
1618 p_fh_scmHash IN VARCHAR2,
1619 p_generate_headers IN VARCHAR2
1620 )
1621 IS
1622 BEGIN
1623 IF p_generate_headers = 'N' THEN
1624
1625 UTL_FILE.PUT_LINE(v_filehandle,
1626 p_line_number || v_delimeter||
1627 p_record_type || v_delimeter||
1628 p_file_type || v_delimeter||
1629 p_upload_type || v_delimeter||
1630 to_char(p_file_creation_date,'ddmmyyyy') || v_delimeter||
1631 p_file_sequence_number || v_delimeter||
1632 p_uploader_type || v_delimeter||
1633 upper(p_deductor_tan) || v_delimeter||
1634 p_number_of_batches || v_delimeter||
1635 p_fh_recordHash || v_delimeter||
1636 p_fh_fvuVersion || v_delimeter||
1637 p_fh_fileHash || v_delimeter||
1638 p_fh_samVersion || v_delimeter||
1639 p_fh_samHash || v_delimeter||
1640 p_fh_scmVersion || v_delimeter||
1641 p_fh_scmHash);
1642 ELSE
1643 UTL_FILE.PUT_LINE(v_filehandle,
1644 LPAD(p_line_number , sq_len_9, v_quart_pad) || v_pad_char ||
1645 LPAD(p_record_type , sq_len_2, v_quart_pad) || v_pad_char ||
1646 LPAD(p_file_type , sq_len_4, v_quart_pad) || v_pad_char ||
1647 LPAD(p_upload_type , sq_len_2, v_quart_pad) || v_pad_char ||
1648 LPAD(p_file_creation_date , sq_len_8, v_quart_pad) || v_pad_char ||
1649 LPAD(p_file_sequence_number , sq_len_5, v_quart_pad) || v_pad_char ||
1650 LPAD(p_uploader_type , sq_len_1, v_quart_pad) || v_pad_char ||
1651 LPAD(upper(p_deductor_tan) , sq_len_10, v_quart_pad) || v_pad_char ||
1652 LPAD(p_number_of_batches , sq_len_9, v_quart_pad) || v_pad_char ||
1653 LPAD(NVL(p_fh_recordHash,v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1654 LPAD(NVL(p_fh_fvuVersion, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1655 LPAD(NVL(p_fh_fileHash, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1656 LPAD(NVL(p_fh_samVersion, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1657 LPAD(NVL(p_fh_samHash, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1658 LPAD(NVL(p_fh_scmVersion, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1659 LPAD(NVL(p_fh_scmHash, v_q_noval_filler) , sq_len_2, v_quart_pad)
1660 ) ;
1661
1662 END IF ;
1663
1664 END create_quarterly_file_header;
1665
1666
1667 PROCEDURE create_quarterly_batch_header(
1668 p_line_number IN NUMBER,
1669 p_record_type IN VARCHAR2,
1670 p_batch_number IN NUMBER,
1671 p_challan_count IN NUMBER,
1672 p_form_number IN CHAR,
1673 p_trn_type IN VARCHAR2,
1674 p_batchUpd IN VARCHAR2,
1675 p_org_RRRno IN VARCHAR2,
1676 p_prev_RRRno IN VARCHAR2,
1677 p_RRRno IN VARCHAR2 ,
1678 p_RRRdate IN VARCHAR2 ,
1682 p_deductor_pan IN VARCHAR2,
1679 p_deductor_last_tan IN VARCHAR2,
1680 p_deductor_tan IN VARCHAR2,
1681 p_filler1 IN VARCHAR2,
1683 p_assessment_year IN NUMBER,
1684 p_financial_year IN NUMBER,
1685 p_period IN VARCHAR2,
1686 p_deductor_name IN VARCHAR2,
1687 p_deductor_branch IN VARCHAR2,
1688 p_tan_address1 IN VARCHAR2,
1689 p_tan_address2 IN VARCHAR2,
1690 p_tan_address3 IN VARCHAR2,
1691 p_tan_address4 IN VARCHAR2,
1692 p_tan_address5 IN VARCHAR2,
1693 p_tan_state_code IN NUMBER,
1694 p_tan_pin IN NUMBER,
1695 p_deductor_email IN VARCHAR2,
1696 p_deductor_stdCode IN NUMBER,
1697 p_deductor_phoneNo IN NUMBER,
1698 p_addrChangedSinceLastReturn IN VARCHAR2,
1699 p_status_of_deductor IN VARCHAR2,
1700 p_pers_resp_for_deduction IN VARCHAR2,
1701 p_PespPerson_designation IN VARCHAR2,
1702 p_RespPerson_address1 IN VARCHAR2,
1703 p_RespPerson_address2 IN VARCHAR2,
1704 p_RespPerson_address3 IN VARCHAR2,
1705 p_RespPerson_address4 IN VARCHAR2,
1706 p_RespPerson_address5 IN VARCHAR2,
1707 p_RespPerson_state IN VARCHAR2,
1708 p_RespPerson_pin IN NUMBER,
1709 p_RespPerson_email IN VARCHAR2,
1710 p_RespPerson_remark IN VARCHAR2,
1711 p_RespPerson_stdCode IN NUMBER,
1712 p_RespPerson_phoneNo IN NUMBER,
1713 p_RespPerson_addressChange IN VARCHAR2,
1714 p_totTaxDeductedAsPerChallan IN NUMBER,
1715 p_tds_circle IN VARCHAR2,
1716 p_salaryRecords_count IN VARCHAR2,
1717 p_gross_total IN VARCHAR2,
1718 p_ao_approval IN VARCHAR2,
1719 p_ao_approval_number IN VARCHAR2,
1720 p_recHash IN VARCHAR2,
1721 p_generate_headers IN VARCHAR2
1722 )
1723 IS
1724 BEGIN
1725 IF p_generate_headers = 'N' THEN
1726 UTL_FILE.PUT_LINE( v_filehandle, p_line_number || v_delimeter||
1727 p_record_type || v_delimeter||
1728 p_batch_number || v_delimeter||
1729 p_challan_count || v_delimeter||
1730 p_form_number || v_delimeter||
1731 p_trn_type || v_delimeter||
1732 p_batchUpd || v_delimeter||
1733 p_org_RRRno || v_delimeter||
1734 p_prev_RRRno || v_delimeter||
1735 p_RRRno || v_delimeter||
1736 p_RRRdate || v_delimeter||
1737 p_deductor_last_tan || v_delimeter||
1738 upper(p_deductor_tan) || v_delimeter||
1739 p_filler1 || v_delimeter||
1740 p_deductor_pan || v_delimeter||
1741 p_assessment_year || v_delimeter||
1742 p_financial_year || v_delimeter||
1743 p_period || v_delimeter||
1744 p_deductor_name || v_delimeter||
1745 p_deductor_branch || v_delimeter||
1746 p_tan_address1 || v_delimeter||
1747 p_tan_address2 || v_delimeter||
1748 p_tan_address3 || v_delimeter||
1749 p_tan_address4 || v_delimeter||
1750 p_tan_address5 || v_delimeter||
1751 p_tan_state_code || v_delimeter||
1752 p_tan_pin || v_delimeter||
1753 p_deductor_email || v_delimeter||
1754 p_deductor_stdCode || v_delimeter||
1755 p_deductor_phoneNo || v_delimeter||
1756 p_addrChangedSinceLastReturn || v_delimeter||
1757 p_status_of_deductor || v_delimeter||
1758 p_pers_resp_for_deduction || v_delimeter||
1759 p_PespPerson_designation || v_delimeter||
1760 p_RespPerson_address1 || v_delimeter||
1761 p_RespPerson_address2 || v_delimeter||
1762 p_RespPerson_address3 || v_delimeter||
1763 p_RespPerson_address4 || v_delimeter||
1764 p_RespPerson_address5 || v_delimeter||
1765 p_RespPerson_state || v_delimeter||
1766 p_RespPerson_pin || v_delimeter||
1767 p_RespPerson_email || v_delimeter||
1768 p_RespPerson_remark || v_delimeter||
1769 p_RespPerson_stdCode || v_delimeter||
1770 p_RespPerson_phoneNo || v_delimeter||
1771 p_RespPerson_addressChange || v_delimeter||
1772 to_char(p_totTaxDeductedAsPerChallan,v_format_amount) || v_delimeter||
1773 p_tds_circle || v_delimeter||
1774 p_salaryRecords_count || v_delimeter||
1775 p_gross_total || v_delimeter||
1776 p_ao_approval || v_delimeter||
1777 p_ao_approval_number || v_delimeter||
1778 p_recHash ) ;
1779
1780
1781 ELSE
1782 UTL_FILE.PUT_LINE( v_filehandle,
1783 LPAD(p_line_number, sq_len_9 , v_quart_pad) || v_pad_char ||
1784 LPAD(p_record_type , sq_len_2 , v_quart_pad) || v_pad_char ||
1785 LPAD(p_batch_number, sq_len_9 , v_quart_pad) || v_pad_char ||
1786 LPAD(p_challan_count, sq_len_9 , v_quart_pad) || v_pad_char ||
1787 LPAD(p_form_number, sq_len_4 , v_quart_pad) || v_pad_char ||
1788 LPAD(NVL(p_trn_type,v_q_noval_filler ), sq_len_2 , v_quart_pad) || v_pad_char ||
1789 LPAD(NVL(p_batchUpd,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1793 LPAD(NVL(p_RRRdate,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1790 LPAD(NVL(p_org_RRRno,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1791 LPAD(NVL(p_prev_RRRno,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1792 LPAD(NVL(p_RRRno,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1794 LPAD(NVL(p_deductor_last_tan,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1795 LPAD(upper(p_deductor_tan), sq_len_10 , v_quart_pad) || v_pad_char ||
1796 LPAD(NVL(p_filler1,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1797 LPAD(NVL(p_deductor_pan,v_q_null_filler ) , sq_len_10 , v_quart_pad) || v_pad_char ||
1798 LPAD(p_assessment_year , sq_len_6 , v_quart_pad) || v_pad_char ||
1799 LPAD(p_financial_year , sq_len_6 , v_quart_pad) || v_pad_char ||
1800 LPAD(p_period , sq_len_2 , v_quart_pad) || v_pad_char ||
1801 LPAD(p_deductor_name, sq_len_75 , v_quart_pad) || v_pad_char ||
1802 LPAD(NVL(p_deductor_branch ,v_q_null_filler ), sq_len_75 , v_quart_pad) || v_pad_char ||
1803 LPAD(p_tan_address1, sq_len_25 , v_quart_pad) || v_pad_char ||
1804 LPAD(NVL(p_tan_address2,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1805 LPAD(NVL(p_tan_address3,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1806 LPAD(NVL(p_tan_address4,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1807 LPAD(NVL(p_tan_address5,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1808 LPAD(p_tan_state_code, sq_len_2 , v_quart_pad) || v_pad_char ||
1809 LPAD(p_tan_pin, sq_len_6 , v_quart_pad) || v_pad_char ||
1810 LPAD(NVL(p_deductor_email,v_q_null_filler ) , sq_len_75 , v_quart_pad) || v_pad_char ||
1811 LPAD(NVL(p_deductor_stdCode,v_quart_numfill ) , sq_len_5 , v_quart_pad) || v_pad_char ||
1812 LPAD(NVL(p_deductor_phoneNo,v_quart_numfill ) , sq_len_10 , v_quart_pad) || v_pad_char ||
1813 LPAD(p_addrChangedSinceLastReturn, sq_len_1 , v_quart_pad) || v_pad_char ||
1814 LPAD(p_status_of_deductor, sq_len_1 , v_quart_pad) || v_pad_char ||
1815 LPAD(p_pers_resp_for_deduction, sq_len_75 , v_quart_pad) || v_pad_char ||
1816 LPAD(p_PespPerson_designation, sq_len_20 , v_quart_pad) || v_pad_char ||
1817 LPAD(p_RespPerson_address1, sq_len_25 , v_quart_pad) || v_pad_char ||
1818 LPAD(NVL(p_RespPerson_address2,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1819 LPAD(NVL(p_RespPerson_address3,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1820 LPAD(NVL(p_RespPerson_address4,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1821 LPAD(NVL(p_RespPerson_address5,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1822 LPAD(p_RespPerson_state, sq_len_2 , v_quart_pad) || v_pad_char ||
1823 LPAD(p_RespPerson_pin, sq_len_6 , v_quart_pad) || v_pad_char ||
1824 LPAD(NVL(p_RespPerson_email,v_q_null_filler ) , sq_len_75 , v_quart_pad) || v_pad_char ||
1825 LPAD(NVL(p_RespPerson_remark,v_q_null_filler ) , sq_len_75 , v_quart_pad) || v_pad_char ||
1826 LPAD(NVL(p_RespPerson_stdCode,v_quart_numfill ) , sq_len_5 , v_quart_pad) || v_pad_char ||
1827 LPAD(NVL(p_RespPerson_phoneNo,v_quart_numfill ) , sq_len_10 , v_quart_pad) || v_pad_char ||
1828 LPAD(p_RespPerson_addressChange, sq_len_1 , v_quart_pad) || v_pad_char ||
1829 LPAD(to_char(p_totTaxDeductedAsPerChallan,v_format_amount), sq_len_15 , v_quart_pad) || v_pad_char ||
1830 LPAD(NVL(p_tds_circle,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1831 LPAD(NVL(p_salaryRecords_count,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1832 LPAD(NVL(p_gross_total,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1833 LPAD(p_ao_approval, sq_len_1 , v_quart_pad) || v_pad_char ||
1837
1834 LPAD(NVL(p_ao_approval_number,v_q_noval_filler ) , sq_len_15 , v_quart_pad) || v_pad_char ||
1835 LPAD(NVL(p_recHash,v_q_noval_filler ) , sq_len_2 , v_quart_pad)
1836 );
1838 END IF ;
1839 END create_quarterly_batch_header;
1840
1841
1842 PROCEDURE create_quart_challan_dtl(
1843 p_line_number IN NUMBER ,
1844 p_record_type IN VARCHAR2 ,
1845 p_batch_number IN NUMBER ,
1846 p_challan_dtl_slno IN NUMBER ,
1847 p_deductee_cnt IN NUMBER ,
1848 p_nil_challan_indicator IN VARCHAR2 ,
1849 p_ch_updIndicator IN VARCHAR2 ,
1850 p_filler2 IN VARCHAR2 ,
1851 p_filler3 IN VARCHAR2 ,
1852 p_filler4 IN VARCHAR2 ,
1853 p_last_bank_challan_no IN VARCHAR2 ,
1854 p_bank_challan_no IN VARCHAR2 ,
1855 p_last_transfer_voucher_no IN VARCHAR2 ,
1856 p_transfer_voucher_no IN NUMBER ,
1857 p_last_bank_branch_code IN VARCHAR2 ,
1858 p_bank_branch_code IN VARCHAR2 ,
1859 p_challan_lastDate IN VARCHAR2 ,
1860 p_challan_Date IN DATE ,
1861 p_filler5 IN VARCHAR2 ,
1862 p_filler6 IN VARCHAR2 ,
1863 p_tds_section IN VARCHAR2 ,
1864 p_amt_of_tds IN NUMBER ,
1865 p_amt_of_surcharge IN NUMBER ,
1866 p_amt_of_cess IN NUMBER ,
1867 p_amt_of_int IN NUMBER ,
1868 p_amt_of_oth IN NUMBER ,
1869 p_tds_amount IN NUMBER ,
1870 p_last_total_depositAmt IN NUMBER ,
1871 p_total_deposit IN NUMBER ,
1872 p_tds_income_tax IN NUMBER ,
1873 p_tds_surcharge IN NUMBER ,
1874 p_tds_cess IN NUMBER ,
1875 p_total_income_tds IN NUMBER ,
1876 p_tds_interest_amt IN NUMBER ,
1877 p_tds_other_amt IN NUMBER ,
1878 p_check_number IN NUMBER ,
1879 p_book_entry IN VARCHAR2 ,
1880 p_remarks IN VARCHAR2 ,
1881 p_ch_recHash IN VARCHAR2,
1882 p_generate_headers IN VARCHAR2,
1883 /* Bug 6796765. Added by Lakshmi Gopalsami
1884 * Added p_form_name as this is required to print the
1885 * section code depending on the section
1886 */
1887 p_form_name IN VARCHAR2
1888 ) IS
1889 /* Bug 6796765. Added by Lakshmi Gopalsami
1890 * Declared variable to select the section truncation depending on
1891 * the form name
1892 */
1893 lv_sec_string VARCHAR2(50);
1894 lv_output_string VARCHAR2 (100);
1895 ln_tds_section NUMBER;
1896
1897 BEGIN
1898
1899 /* Bug 6796765. Added by Lakshmi Gopalsami
1900 * Hardcoding the value of tds_section to be printed in flat file
1901 * for following values 194BB, 194EE, 194LA
1902 * as per the details provided in NSDL as separate logic cannot be
1903 * derived.
1904 * Logic here is to get the section code as 194C, 194D, 195 etc.
1905 * from SEC. 194(C) and then check whether it can be converted to a
1906 * number. If so, the value will be printed in flat file else
1907 * extract the value from 2nd character in flat file except for the
1908 * above hardcoded sections
1909 */
1910
1911
1912 lv_sec_string := 'SEC. ()';
1913 lv_output_string := getSectionCode(upper(p_tds_section), lv_sec_string);
1914
1915 IF lv_output_string in ('194BB', '194EE') THEN
1916 lv_output_string := substr(lv_output_string,3,length(lv_output_string));
1917 ELSIF lv_output_string = '194LA' THEN
1918 lv_output_string := '94L';
1919 ELSE
1920 BEGIN
1921 ln_tds_section := to_number(lv_output_string);
1922 EXCEPTION
1923 WHEN OTHERS THEN
1924 IF SQLCODE = -6502 THEN
1925 lv_output_string := substr(lv_output_string,2,length(lv_output_string));
1926 END IF;
1927 END;
1928 END IF;
1929
1930 -- End for bug 6796765.
1931
1932 -- fnd_file.put_line(FND_FILE.LOG,' tds section ' || p_tds_section);
1933 -- fnd_file.put_line(FND_FILE.LOG,'SEction code with header '||
1934 -- LPAD(getSectionCode(upper(p_tds_section),upper(lv_sec_string)), sq_len_3, v_quart_pad));
1935 -- fnd_file.put_line(FND_FILE.LOG,'SEction code with header '|| getSectionCode(upper(p_tds_section),
1936 -- upper(lv_sec_string)) );
1937
1938 IF p_generate_headers = 'N' THEN
1939
1940 -- Bug 6796765. Added by Lakshmi Gopalsami
1941 -- Changed from getsectioncode to lv_output_string
1942
1943 UTL_FILE.PUT_LINE(
1944 v_filehandle,p_line_number || v_delimeter||
1945 p_record_type || v_delimeter||
1946 p_batch_number || v_delimeter||
1947 p_challan_dtl_slno || v_delimeter||
1948 p_deductee_cnt || v_delimeter||
1949 p_nil_challan_indicator || v_delimeter||
1950 p_ch_updIndicator || v_delimeter||
1951 p_filler2 || v_delimeter||
1952 p_filler3 || v_delimeter||
1953 p_filler4 || v_delimeter||
1954 p_last_bank_challan_no || v_delimeter||
1955 substr(p_bank_challan_no, 1,5) || v_delimeter||
1956 p_last_transfer_voucher_no || v_delimeter||
1957 p_transfer_voucher_no || v_delimeter||
1958 p_last_bank_branch_code || v_delimeter||
1959 p_bank_branch_code || v_delimeter||
1960 p_challan_lastDate || v_delimeter||
1961 to_char(p_challan_Date,'ddmmyyyy') || v_delimeter||
1962 p_filler5 || v_delimeter||
1963 p_filler6 || v_delimeter||
1967 to_char(p_amt_of_surcharge,v_format_amount) || v_delimeter||
1964 -- Bug 6796765. Added by Lakshmi Gopalsami
1965 lv_output_string || v_delimeter||
1966 to_char(p_amt_of_tds,v_format_amount) || v_delimeter||
1968 to_char(p_amt_of_cess,v_format_amount) || v_delimeter||
1969 to_char(p_amt_of_int,v_format_amount) || v_delimeter||
1970 to_char(p_amt_of_oth,v_format_amount) || v_delimeter||
1971 to_char(p_tds_amount,v_format_amount) || v_delimeter||
1972 p_last_total_depositAmt || v_delimeter||
1973 to_char(p_total_deposit,v_format_amount) || v_delimeter||
1974 to_char(p_tds_income_tax,v_format_amount) || v_delimeter||
1975 to_char(p_tds_surcharge,v_format_amount) || v_delimeter||
1976 to_char(p_tds_cess,v_format_amount) || v_delimeter||
1977 to_char(p_total_income_tds,v_format_amount) || v_delimeter||
1978 to_char(p_tds_interest_amt,v_format_amount) || v_delimeter||
1979 to_char(p_tds_other_amt, v_format_amount) || v_delimeter||
1980 p_check_number || v_delimeter||
1981 p_book_entry || v_delimeter||
1982 p_remarks || v_delimeter||
1983 p_ch_recHash ) ;
1984 ELSE
1985 -- Bug 6796765. Added by Lakshmi Gopalsami
1986 -- Changed from getsectioncode to lv_output_string
1987
1988 UTL_FILE.PUT_LINE( v_filehandle,
1989 LPAD(p_line_number , sq_len_9, v_quart_pad) || v_pad_char ||
1990 LPAD(p_record_type , sq_len_2, v_quart_pad) || v_pad_char ||
1991 LPAD(p_batch_number , sq_len_9, v_quart_pad) || v_pad_char ||
1992 LPAD(p_challan_dtl_slno , sq_len_9, v_quart_pad) || v_pad_char ||
1993 LPAD(p_deductee_cnt , sq_len_9, v_quart_pad) || v_pad_char ||
1994 LPAD(p_nil_challan_indicator , sq_len_1, v_quart_pad) || v_pad_char ||
1995 LPAD(NVL(p_ch_updIndicator,v_q_noval_filler), sq_len_2, v_quart_pad) || v_pad_char ||
1996 LPAD(NVL(p_filler2,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1997 LPAD(NVL(p_filler3,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1998 LPAD(NVL(p_filler4,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1999 LPAD(NVL(p_last_bank_challan_no,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
2000 LPAD(NVL(p_bank_challan_no,v_q_null_filler ) , sq_len_5, v_quart_pad) || v_pad_char ||
2001 LPAD(NVL(p_last_transfer_voucher_no,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
2002 LPAD(NVL(p_transfer_voucher_no,v_quart_numfill ) , sq_len_9, v_quart_pad) || v_pad_char ||
2003 LPAD(NVL(p_last_bank_branch_code,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
2004 LPAD(NVL(p_bank_branch_code,v_q_null_filler ) , sq_len_7, v_quart_pad) || v_pad_char ||
2005 LPAD(NVL(p_challan_lastDate,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
2006 LPAD(to_char(p_challan_Date,'ddmmyyyy') , sq_len_8, v_quart_pad) || v_pad_char ||
2007 LPAD(NVL(p_filler5,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
2008 LPAD(NVL(p_filler6,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
2009 -- Bug 6796765. Added by Lakshmi Gopalsami
2010 LPAD(lv_output_string, sq_len_3, v_quart_pad) || v_pad_char ||
2011 LPAD(to_char(p_amt_of_tds , v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2012 LPAD(to_char( p_amt_of_surcharge, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2013 LPAD(to_char( p_amt_of_cess, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2014 LPAD(to_char( p_amt_of_int, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2015 LPAD(to_char( p_amt_of_oth, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2016 LPAD(to_char( p_tds_amount, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2017 LPAD(NVL(p_last_total_depositAmt, v_quart_numfill) , sq_len_2, v_quart_pad) || v_pad_char ||
2018 LPAD(to_char( p_total_deposit, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2019 LPAD(to_char( p_tds_income_tax, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2020 LPAD(to_char( p_tds_surcharge, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2021 LPAD(to_char( p_tds_cess, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2022 LPAD(to_char( p_total_income_tds, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2023 LPAD(to_char( p_tds_interest_amt, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2024 LPAD(to_char( p_tds_other_amt, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2025 LPAD(NVL(p_check_number,v_quart_numfill ) , sq_len_15, v_quart_pad) || v_pad_char ||
2026 LPAD(NVL(p_book_entry,v_q_null_filler ) , sq_len_1, v_quart_pad) || v_pad_char ||
2027 LPAD(NVL(p_remarks,v_q_null_filler ) , sq_len_14, v_quart_pad) || v_pad_char ||
2028 LPAD(NVL(p_ch_recHash,v_q_noval_filler ) , sq_len_2, v_quart_pad)
2029 );
2030 END IF ;
2031 END create_quart_challan_dtl;
2032
2033
2034 PROCEDURE create_quart_deductee_dtl(
2035 p_line_number IN NUMBER,
2036 p_record_type IN VARCHAR2,
2037 p_batch_number IN NUMBER,
2038 p_dh_challan_recNo IN NUMBER,
2039 p_deductee_slno IN NUMBER,
2040 p_dh_mode IN VARCHAR2,
2041 p_emp_serial_no IN VARCHAR2,
2042 p_deductee_code IN VARCHAR2,
2043 p_last_emp_pan IN VARCHAR2,
2044 p_deductee_pan IN VARCHAR2,
2045 p_last_emp_pan_refno IN VARCHAR2,
2046 p_deductee_pan_refno IN VARCHAR2,
2047 p_vendor_name IN VARCHAR2,
2048 p_deductee_tds_income_tax IN NUMBER,
2049 p_deductee_tds_surcharge IN NUMBER,
2050 p_deductee_tds_cess IN NUMBER,
2054 p_last_total_tax_deposit IN VARCHAR2,
2051 p_deductee_total_tax_deducted IN NUMBER,
2052 p_last_total_tax_deducted IN VARCHAR2,
2053 p_deductee_total_tax_deposit IN NUMBER,
2055 p_total_purchase IN VARCHAR2,
2056 p_base_taxabale_amount IN NUMBER,
2057 p_gl_date IN DATE,
2058 p_tds_invoice_date IN DATE,
2059 p_deposit_date IN VARCHAR2,
2060 p_tds_tax_rate IN NUMBER,
2061 p_grossingUp_ind IN VARCHAR2,
2062 p_book_ent_oth IN VARCHAR2,
2063 p_certificate_issue_date IN VARCHAR2,
2064 p_remarks1 IN VARCHAR2,
2065 p_remarks2 IN VARCHAR2,
2066 p_remarks3 IN VARCHAR2,
2067 p_dh_recHash IN VARCHAR2,
2068 p_generate_headers IN VARCHAR2
2069 )
2070 IS
2071 BEGIN
2072 IF p_generate_headers = 'N' THEN
2073 UTL_FILE.PUT_LINE( v_filehandle,
2074 p_line_number || v_delimeter ||
2075 p_record_type || v_delimeter ||
2076 p_batch_number || v_delimeter ||
2077 p_dh_challan_recNo || v_delimeter ||
2078 p_deductee_slno || v_delimeter ||
2079 p_dh_mode || v_delimeter ||
2080 p_emp_serial_no || v_delimeter ||
2081 p_deductee_code || v_delimeter ||
2082 p_last_emp_pan || v_delimeter ||
2083 p_deductee_pan || v_delimeter ||
2084 p_last_emp_pan_refno || v_delimeter ||
2085 p_deductee_pan_refno || v_delimeter ||
2086 p_vendor_name || v_delimeter ||
2087 to_char( p_deductee_tds_income_tax, v_format_amount) || v_delimeter ||
2088 to_char( p_deductee_tds_surcharge, v_format_amount) || v_delimeter ||
2089 to_char( p_deductee_tds_cess, v_format_amount) || v_delimeter ||
2090 to_char( p_deductee_total_tax_deducted, v_format_amount) || v_delimeter ||
2091 p_last_total_tax_deducted || v_delimeter ||
2092 to_char( p_deductee_total_tax_deposit, v_format_amount) || v_delimeter ||
2093 p_last_total_tax_deposit || v_delimeter ||
2094 p_total_purchase || v_delimeter ||
2095 to_char( p_base_taxabale_amount, v_format_amount) || v_delimeter ||
2096 to_char(p_gl_date,'ddmmyyyy') || v_delimeter ||
2097 to_char(p_tds_invoice_date,'ddmmyyyy') || v_delimeter ||
2098 p_deposit_date || v_delimeter ||
2099 -- Bug 6070014. Added by csahoo.
2100 -- Changed the hardcoded value to assigned value.
2101 to_char(p_tds_tax_rate,v_format_rate) || v_delimeter ||
2102 p_grossingUp_ind || v_delimeter ||
2103 p_book_ent_oth || v_delimeter ||
2104 p_certificate_issue_date || v_delimeter ||
2105 p_remarks1 || v_delimeter ||
2106 p_remarks2 || v_delimeter ||
2107 p_remarks3 || v_delimeter ||
2108 p_dh_recHash
2109 );
2110 ELSE
2111 UTL_FILE.PUT_LINE( v_filehandle,
2112 LPAD(p_line_number , sq_len_9, v_quart_pad) || v_pad_char ||
2113 LPAD(p_record_type , sq_len_2, v_quart_pad) || v_pad_char ||
2114 LPAD(p_batch_number , sq_len_9, v_quart_pad) || v_pad_char ||
2115 LPAD(p_dh_challan_recNo , sq_len_9, v_quart_pad) || v_pad_char ||
2116 LPAD(p_deductee_slno , sq_len_9, v_quart_pad) || v_pad_char ||
2117 LPAD(p_dh_mode , sq_len_1, v_quart_pad) || v_pad_char ||
2118 LPAD(NVL(p_emp_serial_no,v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
2119 LPAD(p_deductee_code , sq_len_1, v_quart_pad) || v_pad_char ||
2120 LPAD(NVL(p_last_emp_pan, v_q_noval_filler), sq_len_2, v_quart_pad) || v_pad_char ||
2121 LPAD(p_deductee_pan , sq_len_10, v_quart_pad) || v_pad_char ||
2122 LPAD(NVL(p_last_emp_pan_refno,v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
2123 LPAD(NVL(p_deductee_pan_refno,v_q_null_filler) , sq_len_10, v_quart_pad) || v_pad_char ||
2124 LPAD(NVL(p_vendor_name, v_q_null_filler) , sq_len_75, v_quart_pad) || v_pad_char ||
2125 LPAD(to_char( p_deductee_tds_income_tax, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2126 LPAD(to_char( p_deductee_tds_surcharge, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2127 LPAD(to_char( p_deductee_tds_cess, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2128 LPAD(to_char( p_deductee_total_tax_deducted, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2129 LPAD(NVL(p_last_total_tax_deducted, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
2130 LPAD(to_char( p_deductee_total_tax_deposit, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2131 LPAD(NVL(p_last_total_tax_deposit, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
2132 LPAD(NVL(p_total_purchase, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
2133 LPAD(to_char( p_base_taxabale_amount, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
2134 LPAD(to_char(p_gl_date,'ddmmyyyy') , sq_len_8, v_quart_pad) || v_pad_char ||
2135 LPAD(NVL(to_char(p_tds_invoice_date,'ddmmyyyy'),G_DATE_DUMMY) , sq_len_8, v_quart_pad) || v_pad_char || -- change later
2139 LPAD(p_book_ent_oth , sq_len_1, v_quart_pad) || v_pad_char ||
2136 LPAD(NVL(p_deposit_date, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
2137 LPAD( to_char(p_tds_tax_rate,v_format_rate), sq_len_7, v_quart_pad) || v_pad_char ||
2138 LPAD(NVL(p_grossingUp_ind, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
2140 LPAD(NVL(p_certificate_issue_date,v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
2141 LPAD(NVL(p_remarks1,v_q_null_filler) , sq_len_1, v_quart_pad) || v_pad_char ||
2142 LPAD(NVL(p_remarks2,v_q_noval_filler) , sq_len_75, v_quart_pad) || v_pad_char ||
2143 LPAD(NVL(p_remarks3,v_q_noval_filler) , sq_len_14, v_quart_pad) || v_pad_char ||
2144 LPAD(NVL(p_dh_recHash,v_q_noval_filler) , sq_len_2, v_quart_pad)
2145 );
2146 END IF ;
2147 END create_quart_deductee_dtl;
2148
2149 -- Validation related Procedures.
2150
2151 PROCEDURE validate_file_header
2152 ( p_line_number IN NUMBER ,
2153 p_record_type IN VARCHAR2,
2154 p_quartfile_type IN VARCHAR2,
2155 p_upload_type IN VARCHAR2,
2156 p_file_creation_date IN DATE,
2157 p_file_sequence_number IN NUMBER,
2158 p_uploader_type IN VARCHAR2,
2159 p_deductor_tan IN VARCHAR2,
2160 p_number_of_batches IN NUMBER,
2161 p_period IN VARCHAR2,
2162 p_challan_start_date IN DATE,
2163 p_challan_end_date IN DATE,
2164 p_fin_year IN NUMBER,
2165 p_return_code OUT NOCOPY VARCHAR2,
2166 p_return_message OUT NOCOPY VARCHAR2
2167 )
2168 IS
2169 lv_q1_start_date VARCHAR2(11) ;
2170 lv_q1_end_date VARCHAR2(11) ;
2171 lv_q2_start_date VARCHAR2(11) ;
2172 lv_q2_end_date VARCHAR2(11) ;
2173 lv_q3_start_date VARCHAR2(11) ;
2174 lv_q3_end_date VARCHAR2(11) ;
2175 lv_q4_start_date VARCHAR2(11) ;
2176 lv_q4_end_date VARCHAR2(11) ;
2177 ln_fin_year NUMBER ;
2178 lv_date_format VARCHAR2(11);
2179 BEGIN
2180
2181 IF p_line_number IS NULL THEN
2182 p_return_message := p_return_message || ' Line Number should not be null. ' ;
2183 IF lv_action <> 'V' THEN
2184 goto end_of_procedure ;
2185 END IF ;
2186 END IF;
2187 IF p_record_type IS NULL THEN
2188 p_return_message := p_return_message || ' Record Type is null. ' ;
2189 IF lv_action <> 'V' THEN
2190 goto end_of_procedure ;
2191 END IF ;
2192 END IF;
2193 IF p_quartfile_type IS NULL THEN
2194 p_return_message := p_return_message || ' File Type is null. ' ;
2195 IF lv_action <> 'V' THEN
2196 goto end_of_procedure ;
2197 END IF ;
2198 END IF;
2199 IF p_upload_type IS NULL THEN
2200 p_return_message := p_return_message || ' Upload Type is null. ' ;
2201 IF lv_action <> 'V' THEN
2202 goto end_of_procedure ;
2203 END IF ;
2204 END IF;
2205 IF p_file_creation_date IS NULL THEN
2206 p_return_message := p_return_message || ' File Creation Date is null. ' ;
2207 IF lv_action <> 'V' THEN
2208 goto end_of_procedure ;
2209 END IF ;
2210 END IF;
2211 IF p_file_sequence_number IS NULL THEN
2212 p_return_message := p_return_message || ' File Sequence No is null. ' ;
2213 IF lv_action <> 'V' THEN
2214 goto end_of_procedure ;
2215 END IF ;
2216 END IF;
2217 IF p_uploader_type IS NULL THEN
2218 p_return_message := p_return_message || ' Upload Type is null. ' ;
2219 IF lv_action <> 'V' THEN
2220 goto end_of_procedure ;
2221 END IF ;
2222 END IF;
2223 IF p_deductor_tan IS NULL THEN
2224 p_return_message := p_return_message || ' Deductor TAN is null. ' ;
2225 IF lv_action <> 'V' THEN
2226 goto end_of_procedure ;
2227 END IF ;
2228 END IF;
2229 IF p_number_of_batches IS NULL THEN
2230 p_return_message := p_return_message || ' Batch Count is null. ' ;
2231 IF lv_action <> 'V' THEN
2232 goto end_of_procedure ;
2233 END IF ;
2234 END IF;
2235
2236 IF p_period = 'Q1' THEN
2237 lv_q1_start_date := '01-APR-' || p_fin_year ;
2238 lv_q1_end_date := '30-JUN-' || p_fin_year ;
2239 lv_date_format := 'DD-MON-YYYY' ;
2240
2241 IF not ( p_challan_start_date >= to_date(lv_q1_start_date,lv_date_format) and p_challan_end_date <= to_date(lv_q1_end_date,lv_date_format) ) THEN
2242 p_return_message := ' The given Challan Start and end dates do not fall under the specified Quarter and Year. ' ;
2243 goto end_of_procedure ;
2244 END IF ;
2245 ELSIF p_period = 'Q2' THEN
2246 lv_q2_start_date := '01-JUL-' || p_fin_year;
2247 lv_q2_end_date := '30-SEP-' || p_fin_year;
2248
2249 IF not ( p_challan_start_date >= to_date(lv_q2_start_date,lv_date_format) and p_challan_end_date <= to_date(lv_q2_end_date,lv_date_format) ) THEN
2250 p_return_message := ' The given Challan Start and end dates do not fall under the specified Quarter and Year. ' ;
2251 goto end_of_procedure ;
2252 END IF ;
2253 ELSIF p_period = 'Q3' THEN
2254 lv_q3_start_date := '01-OCT-' || p_fin_year;
2255 lv_q3_end_date := '31-DEC-' || p_fin_year;
2256
2260 END IF ;
2257 IF not ( p_challan_start_date >= to_date(lv_q3_start_date,lv_date_format) and p_challan_end_date <= to_date(lv_q3_end_date,lv_date_format) ) THEN
2258 p_return_message := ' The given Challan Start and end dates do not fall under the specified Quarter and Year. ' ;
2259 goto end_of_procedure ;
2261 ELSIF p_period = 'Q4' THEN
2262 ln_fin_year := p_fin_year + 1 ;
2263 lv_q4_start_date := '01-JAN-' || ln_fin_year;
2264 lv_q4_end_date := '31-MAR-' || ln_fin_year;
2265
2266 IF not ( p_challan_start_date >= to_date(lv_q4_start_date,lv_date_format) and p_challan_end_date <= to_date(lv_q4_end_date,lv_date_format) ) THEN
2267 p_return_message := ' The given Challan Start and end dates do not fall under the specified Quarter and Year. ' ;
2268 goto end_of_procedure ;
2269 END IF ;
2270 END IF ;
2271
2272 IF lv_action = 'V' THEN
2273 goto end_of_procedure ;
2274 END IF ;
2275
2276 <<end_of_procedure>>
2277 if p_return_message is not null then
2278 p_return_code := 'E';
2279 p_return_message := 'File Header Error - ' || 'Line No : ' || p_line_number || '. ' || p_return_message ;
2280 end if;
2281
2282 END validate_file_header;
2283
2284 PROCEDURE validate_batch_header
2285 ( p_line_number IN NUMBER,
2286 p_record_type IN VARCHAR2,
2287 p_batch_number IN NUMBER,
2288 p_challan_cnt IN NUMBER,
2289 p_quart_form_number IN VARCHAR2,
2290 p_deductor_tan IN VARCHAR2,
2291 p_assessment_year IN NUMBER,
2292 p_financial_year IN NUMBER,
2293 p_deductor_name IN VARCHAR2,
2294 p_tan_address1 IN VARCHAR2,
2295 p_tan_state_code IN NUMBER,
2296 p_tan_pin IN NUMBER,
2297 p_deductor_status IN VARCHAR2,
2298 p_addrChangedSinceLastReturn IN VARCHAR2,
2299 p_personNameRespForDedection IN VARCHAR2,
2300 p_personDesgnRespForDedection IN VARCHAR2,
2301 p_RespPers_flat_no IN VARCHAR2 , -- Bug 6030953
2302 p_RespPers_prem_bldg IN VARCHAR2 , -- Bug 6030953
2303 p_RespPers_rd_st_lane IN VARCHAR2 , -- Bug 6030953
2304 p_RespPers_area_loc IN VARCHAR2 , -- Bug 6030953
2305 p_RespPers_tn_cty_dt IN VARCHAR2 , -- Bug 6030953
2306 p_RespPersState IN NUMBER,
2307 p_RespPersPin IN NUMBER,
2308 p_RespPers_tel_no IN VARCHAR2 , -- Bug 6030953
2309 p_RespPers_email IN VARCHAR2 , -- Bug 6030953
2310 p_RespPersAddrChange IN VARCHAR2,
2311 p_totTaxDeductedAsPerDeductee IN NUMBER,
2312 p_ao_approval IN VARCHAR2,
2313 p_return_code OUT NOCOPY VARCHAR2,
2314 p_return_message OUT NOCOPY VARCHAR2
2315 )
2316 IS
2317 BEGIN
2318
2319 IF p_line_number IS NULL THEN
2320 p_return_message := p_return_message || ' Line Number should not be null. ' ;
2321 IF lv_action <> 'V' THEN
2322 goto end_of_procedure ;
2323 END IF ;
2324 END IF ;
2325 IF p_record_type IS NULL THEN
2326 p_return_message := p_return_message || ' Record Type is null. ' ;
2327 IF lv_action <> 'V' THEN
2328 goto end_of_procedure ;
2329 END IF ;
2330 END IF ;
2331 IF p_batch_number IS NULL THEN
2332 p_return_message := p_return_message || ' Batch Number is null. ' ;
2333 IF lv_action <> 'V' THEN
2334 goto end_of_procedure ;
2335 END IF ;
2336 END IF ;
2337 IF p_challan_cnt IS NULL THEN
2338 p_return_message := p_return_message || ' Record Count is null. ' ;
2339 IF lv_action <> 'V' THEN
2340 goto end_of_procedure ;
2341 END IF ;
2342 END IF ;
2343 IF p_quart_form_number IS NULL THEN
2344 p_return_message := p_return_message || ' Form Number is null. ' ;
2345 IF lv_action <> 'V' THEN
2346 goto end_of_procedure ;
2347 END IF ;
2348 END IF ;
2349 IF p_deductor_tan IS NULL THEN
2350 p_return_message := p_return_message || ' Deductor TAN is null. ' ;
2351 IF lv_action <> 'V' THEN
2352 goto end_of_procedure ;
2353 END IF ;
2354 END IF ;
2355 IF p_assessment_year IS NULL THEN
2356 p_return_message := p_return_message || ' Assessment Year is null. ' ;
2357 IF lv_action <> 'V' THEN
2358 goto end_of_procedure ;
2359 END IF ;
2360 END IF ;
2361 IF p_financial_year IS NULL THEN
2362 p_return_message := p_return_message || ' Financial Year is null. ' ;
2363 IF lv_action <> 'V' THEN
2364 goto end_of_procedure ;
2365 END IF ;
2366 END IF ;
2367 IF p_deductor_name IS NULL THEN
2368 p_return_message := p_return_message || ' Deductor Name is null. ' ;
2369 IF lv_action <> 'V' THEN
2370 goto end_of_procedure ;
2371 END IF ;
2372 END IF ;
2373 IF p_tan_address1 IS NULL THEN
2374 p_return_message := p_return_message || ' Deductor Address is null. ' ;
2375 IF lv_action <> 'V' THEN
2376 goto end_of_procedure ;
2377 END IF ;
2378 END IF ;
2379 IF p_tan_state_code IS NULL THEN
2380 p_return_message := p_return_message || ' Deductor State is null. ' ;
2381 IF lv_action <> 'V' THEN
2382 goto end_of_procedure ;
2383 END IF ;
2384 END IF ;
2385 IF p_tan_pin IS NULL THEN
2389 END IF ;
2386 p_return_message := p_return_message || ' Deductor Pin is null. ' ;
2387 IF lv_action <> 'V' THEN
2388 goto end_of_procedure ;
2390 END IF ;
2391 IF p_deductor_status IS NULL THEN
2392 p_return_message := p_return_message || ' Deductor Type is null. ' ;
2393 IF lv_action <> 'V' THEN
2394 goto end_of_procedure ;
2395 END IF ;
2396 END IF ;
2397 IF p_addrChangedSinceLastReturn IS NULL THEN
2398 p_return_message := p_return_message || ' Field Deductor Addredd Changed Since last year is null. ' ;
2399 IF lv_action <> 'V' THEN
2400 goto end_of_procedure ;
2401 END IF ;
2402 END IF ;
2403 IF p_personNameRespForDedection IS NULL THEN
2404 p_return_message := p_return_message || ' Person Responsible For Deduction is null. ' ;
2405 IF lv_action <> 'V' THEN
2406 goto end_of_procedure ;
2407 END IF ;
2408 END IF ;
2409 IF p_personDesgnRespForDedection IS NULL THEN
2410 p_return_message := p_return_message || ' Designation of Responsible Person is null. ' ;
2411 IF lv_action <> 'V' THEN
2412 goto end_of_procedure ;
2413 END IF ;
2414 END IF ;
2415 -- Bug 6030953. Added by Lakshmi Gopalsami
2416 IF p_RespPers_flat_no IS NULL THEN
2417 p_return_message := p_return_message || ' Flat No. of Responsible Person is null. ' ;
2418 IF lv_action <> 'V' THEN
2419 goto end_of_procedure ;
2420 END IF ;
2421 END IF ;
2422
2423 IF p_RespPers_prem_bldg IS NULL THEN
2424 p_return_message := p_return_message || ' Name of the premises/bldg of Responsible Person is null. ' ;
2425 IF lv_action <> 'V' THEN
2426 goto end_of_procedure ;
2427 END IF ;
2428 END IF ;
2429
2430 IF p_RespPers_rd_st_lane IS NULL THEN
2431 p_return_message := p_return_message || ' Road/Street/Lane of Responsible Person is null. ' ;
2432 IF lv_action <> 'V' THEN
2433 goto end_of_procedure ;
2434 END IF ;
2435 END IF ;
2436
2437 IF p_RespPers_area_loc IS NULL THEN
2438 p_return_message := p_return_message || ' Area/Location of Responsible Person is null. ' ;
2439 IF lv_action <> 'V' THEN
2440 goto end_of_procedure ;
2441 END IF ;
2442 END IF ;
2443
2444 IF p_RespPers_tn_cty_dt IS NULL THEN
2445 p_return_message := p_return_message || ' Town/City/District of Responsible Person is null. ' ;
2446 IF lv_action <> 'V' THEN
2447 goto end_of_procedure ;
2448 END IF ;
2449 END IF ;
2450 -- End for bug 6030953
2451
2452 IF p_RespPersState IS NULL THEN
2453 p_return_message := p_return_message || ' State of Responsible Person is null. ' ;
2454 IF lv_action <> 'V' THEN
2455 goto end_of_procedure ;
2456 END IF ;
2457 END IF ;
2458 IF p_RespPersPin IS NULL THEN
2459 p_return_message := p_return_message || ' Pin of Responsible Person is null. ' ;
2460 IF lv_action <> 'V' THEN
2461 goto end_of_procedure ;
2462 END IF ;
2463 END IF ;
2464 IF p_RespPersAddrChange IS NULL THEN
2465 p_return_message := p_return_message || ' Field ''Address of Responsible Person has Changed'' is null. ' ;
2466 IF lv_action <> 'V' THEN
2467 goto end_of_procedure ;
2468 END IF ;
2469 END IF ;
2470 IF p_totTaxDeductedAsPerDeductee IS NULL THEN
2471 p_return_message := p_return_message || ' Total Deposit Amount as per Challan is null. ' ;
2472 IF lv_action <> 'V' THEN
2473 goto end_of_procedure ;
2474 END IF ;
2475 END IF ;
2476 IF p_ao_approval IS NULL THEN
2477 p_return_message := p_return_message || ' AO Approval is null. ' ;
2478 IF lv_action <> 'V' THEN
2479 goto end_of_procedure ;
2480 END IF ;
2481 END IF ;
2482
2483 IF lv_action = 'V' THEN
2484 goto end_of_procedure ;
2485 END IF ;
2486
2487 <<end_of_procedure>>
2488 IF p_return_message IS NOT NULL THEN
2489 p_return_code := 'E';
2490 p_return_message := 'Batch Header Error - ' || 'Line No : ' || p_line_number || '. ' || p_return_message ;
2491 END IF;
2492
2493 END validate_batch_header;
2494
2495 PROCEDURE validate_challan_detail
2496 (p_line_number IN NUMBER ,
2497 p_record_type IN VARCHAR2,
2498 p_batch_number IN NUMBER,
2499 p_challan_dtl_slno IN NUMBER,
2500 p_deductee_cnt IN NUMBER,
2501 p_nil_challan_indicat IN VARCHAR2,
2502 p_tds_section IN VARCHAR2,
2503 p_amt_of_tds IN NUMBER,
2504 p_amt_of_surcharge IN NUMBER,
2505 p_amt_of_cess IN NUMBER,
2506 p_amt_of_oth IN NUMBER,
2507 p_tds_amount IN NUMBER,
2508 p_total_income_tds IN NUMBER,
2509 p_challan_num IN VARCHAR2,
2510 p_bank_branch_code IN VARCHAR2,
2511 p_challan_no IN VARCHAR2,
2512 p_challan_Date IN DATE,
2513 p_check_number IN NUMBER,
2514 p_return_code OUT NOCOPY VARCHAR2,
2515 p_return_message OUT NOCOPY VARCHAR2
2516 )
2517 IS
2518 BEGIN
2519 IF p_line_number IS NULL THEN
2523 END IF ;
2520 p_return_message := p_return_message || ' Line number should not be null. ' ;
2521 IF lv_action <> 'V' THEN
2522 goto end_of_procedure ;
2524 END IF ;
2525 IF p_record_type IS NULL THEN
2526 p_return_message := p_return_message || ' Record Type is null. ' ;
2527 IF lv_action <> 'V' THEN
2528 goto end_of_procedure ;
2529 END IF ;
2530 END IF ;
2531 IF p_batch_number IS NULL THEN
2532 p_return_message := p_return_message || ' Batch Number is null. ' ;
2533 IF lv_action <> 'V' THEN
2534 goto end_of_procedure ;
2535 END IF ;
2536 END IF ;
2537 IF p_challan_dtl_slno IS NULL THEN
2538 p_return_message := p_return_message || ' Challan Record Number is null. ' ;
2539 IF lv_action <> 'V' THEN
2540 goto end_of_procedure ;
2541 END IF ;
2542 END IF ;
2543 IF p_deductee_cnt IS NULL THEN
2544 p_return_message := p_return_message || ' Deductee Count is null . ' ;
2545 IF lv_action <> 'V' THEN
2546 goto end_of_procedure ;
2547 END IF ;
2548 END IF ;
2549 IF p_nil_challan_indicat IS NULL THEN
2550 p_return_message := p_return_message || ' NIL Challan Indicator is null . ' ;
2551 IF lv_action <> 'V' THEN
2552 goto end_of_procedure ;
2553 END IF ;
2554 END IF ;
2555 IF p_tds_section IS NULL THEN
2556 p_return_message := p_return_message || ' TDS Section is null . ' ;
2557 IF lv_action <> 'V' THEN
2558 goto end_of_procedure ;
2559 END IF ;
2560 END IF ;
2561 IF p_amt_of_tds IS NULL THEN
2562 p_return_message := p_return_message || ' TDS Amount is null . ' ;
2563 IF lv_action <> 'V' THEN
2564 goto end_of_procedure ;
2565 END IF ;
2566 END IF ;
2567 IF p_amt_of_surcharge IS NULL THEN
2568 p_return_message := p_return_message || ' TDS Surcharge Amount is null . ' ;
2569 IF lv_action <> 'V' THEN
2570 goto end_of_procedure ;
2571 END IF ;
2572 END IF ;
2573 IF p_amt_of_cess IS NULL THEN
2574 p_return_message := p_return_message || ' TDS Cess Amount is null . ' ;
2575 IF lv_action <> 'V' THEN
2576 goto end_of_procedure ;
2577 END IF ;
2578 END IF ;
2579 IF p_amt_of_oth IS NULL THEN
2580 p_return_message := p_return_message || ' TDS Other Amount is null . ' ;
2581 IF lv_action <> 'V' THEN
2582 goto end_of_procedure ;
2583 END IF ;
2584 END IF ;
2585 IF p_tds_amount IS NULL THEN
2586 p_return_message := p_return_message || ' Total TDS Amount is null . ' ;
2587 IF lv_action <> 'V' THEN
2588 goto end_of_procedure ;
2589 END IF ;
2590 END IF ;
2591 IF p_challan_no IS NULL THEN
2592 p_return_message := p_return_message || ' CHallan No is null . ' ;
2593 IF lv_action <> 'V' THEN
2594 goto end_of_procedure ;
2595 END IF ;
2596 END IF ;
2597 IF p_challan_Date IS NULL THEN
2598 p_return_message := p_return_message || ' CHallan Date is null . ' ;
2599 IF lv_action <> 'V' THEN
2600 goto end_of_procedure ;
2601 END IF ;
2602 END IF ;
2603
2604 IF p_total_income_tds IS NULL THEN
2605 p_return_message := p_return_message || ' Total Tax Deposit Amount is null . ' ;
2606 IF lv_action <> 'V' THEN
2607 goto end_of_procedure ;
2608 END IF ;
2609 END IF ;
2610
2611 -- Harshita for Bug 4640996
2612 check_numeric(p_challan_num, 'Check Number : ' || p_check_number || ' Challan Number is not a Numeric Value', lv_action);
2613 check_numeric(p_bank_branch_code, 'Check Number : ' || p_check_number || ' Bank Branch Code is not a Numeric Value ', lv_action);
2614
2615 IF lv_action = 'V' THEN
2616 goto end_of_procedure ;
2617 END IF ;
2618
2619 <<end_of_procedure>>
2620 IF p_return_message IS NOT NULL THEN
2621 p_return_code := 'E';
2622 p_return_message := 'Challan Detail Error - ' || 'Check Number : ' || p_check_number || '. ' || p_return_message ;
2623 END IF;
2624 END validate_challan_detail;
2625
2626 PROCEDURE validate_deductee_detail
2627 ( p_line_number IN NUMBER ,
2628 p_record_type IN VARCHAR2,
2629 p_batch_number IN NUMBER,
2630 p_challan_line_num IN NUMBER,
2631 p_deductee_slno IN NUMBER,
2632 p_dh_mode IN VARCHAR2,
2633 p_quart_deductee_code IN VARCHAR2,
2634 p_deductee_pan IN VARCHAR2,
2635 p_vendor_name IN VARCHAR2,
2636 p_amt_of_tds IN NUMBER,
2637 p_amt_of_surcharge IN NUMBER ,
2638 p_amt_of_cess IN NUMBER ,
2639 p_deductee_total_tax_deducted IN NUMBER,
2640 p_base_taxabale_amount IN NUMBER,
2641 p_gl_date IN DATE ,
2642 p_book_ent_oth IN VARCHAR2,
2643 p_return_code OUT NOCOPY VARCHAR2,
2644 p_return_message OUT NOCOPY VARCHAR2
2648 IF p_line_number IS NULL THEN
2645 )
2646 IS
2647 BEGIN
2649 p_return_message := p_return_message || ' Line Number should not be null. ' ;
2650 IF lv_action <> 'V' THEN
2651 goto end_of_procedure ;
2652 END IF ;
2653 END IF ;
2654 IF p_record_type IS NULL THEN
2655 p_return_message := p_return_message || ' Record Type is null. ' ;
2656 IF lv_action <> 'V' THEN
2657 goto end_of_procedure ;
2658 END IF ;
2659 END IF ;
2660 IF p_batch_number IS NULL THEN
2661 p_return_message := p_return_message || ' Batch Number is null. ' ;
2662 IF lv_action <> 'V' THEN
2663 goto end_of_procedure ;
2664 END IF ;
2665 END IF ;
2666 IF p_challan_line_num IS NULL THEN
2667 p_return_message := p_return_message || ' Challan Record Number is null. ' ;
2668 IF lv_action <> 'V' THEN
2669 goto end_of_procedure ;
2670 END IF ;
2671 END IF ;
2672 IF p_deductee_slno IS NULL THEN
2673 p_return_message := p_return_message || ' Party Detail Record Number is null. ' ;
2674 IF lv_action <> 'V' THEN
2675 goto end_of_procedure ;
2676 END IF ;
2677 END IF ;
2678 IF p_dh_mode IS NULL THEN
2679 p_return_message := p_return_message || ' Mode is null. ' ;
2680 IF lv_action <> 'V' THEN
2681 goto end_of_procedure ;
2682 END IF ;
2683 END IF ;
2684 IF p_quart_deductee_code IS NULL THEN
2685 p_return_message := p_return_message || ' Deductee Party Code is null. ' ;
2686 IF lv_action <> 'V' THEN
2687 goto end_of_procedure ;
2688 END IF ;
2689 END IF ;
2690 IF p_deductee_pan IS NULL THEN
2691 p_return_message := p_return_message || ' Deductee PAN is null. ' ;
2692 IF lv_action <> 'V' THEN
2693 goto end_of_procedure ;
2694 END IF ;
2695 END IF ;
2696 IF p_vendor_name IS NULL THEN
2697 p_return_message := p_return_message || ' Party Name is null. ' ;
2698 IF lv_action <> 'V' THEN
2699 goto end_of_procedure ;
2700 END IF ;
2701 END IF ;
2702 IF p_amt_of_tds IS NULL THEN
2703 p_return_message := p_return_message || ' TDS Income Tax for the Period is null. ' ;
2704 IF lv_action <> 'V' THEN
2705 goto end_of_procedure ;
2706 END IF ;
2707 END IF ;
2708 IF p_amt_of_surcharge IS NULL THEN
2709 p_return_message := p_return_message || ' TDS Surcharge is null. ' ;
2710 IF lv_action <> 'V' THEN
2711 goto end_of_procedure ;
2712 END IF ;
2713 END IF ;
2714 IF p_amt_of_cess IS NULL THEN
2715 p_return_message := p_return_message || ' TDS Cess is null. ' ;
2716 IF lv_action <> 'V' THEN
2717 goto end_of_procedure ;
2718 END IF ;
2719 END IF ;
2720 IF p_deductee_total_tax_deducted IS NULL THEN
2721 p_return_message := p_return_message || ' Total TDS is null. ' ;
2722 IF lv_action <> 'V' THEN
2723 goto end_of_procedure ;
2724 END IF ;
2725 END IF ;
2726 IF p_base_taxabale_amount IS NULL THEN
2727 p_return_message := p_return_message || ' Payment Amount is null. ' ;
2728 IF lv_action <> 'V' THEN
2729 goto end_of_procedure ;
2730 END IF ;
2731 END IF ;
2732 IF p_gl_date IS NULL THEN
2733 p_return_message := p_return_message || ' Date on which Amount Credited is null. ' ;
2734 IF lv_action <> 'V' THEN
2735 goto end_of_procedure ;
2736 END IF ;
2737 END IF ;
2738 IF p_book_ent_oth IS NULL THEN
2739 p_return_message := p_return_message || ' Book/Cash Entry is null. ' ;
2740 IF lv_action <> 'V' THEN
2741 goto end_of_procedure ;
2742 END IF ;
2743 END IF ;
2744
2745 IF lv_action = 'V' THEN
2746 goto end_of_procedure ;
2747 END IF ;
2748
2749 <<end_of_procedure>>
2750 if p_return_message is not null then
2751 p_return_code := 'E';
2752 p_return_message := 'Deductee Detail Error - ' || p_return_message ;
2753 end if;
2754 END validate_deductee_detail;
2755
2756 PROCEDURE check_numeric
2757 (p_variable IN VARCHAR2 ,
2758 p_err IN VARCHAR2 ,
2759 p_action IN VARCHAR2
2760 )
2761 IS
2762 ln_check_number NUMBER ;
2763 BEGIN
2764 ln_check_number := to_number( p_variable ) ;
2765 EXCEPTION
2766 WHEN OTHERS THEN
2767 FND_FILE.put_line(FND_FILE.log, 'sql code : ' || SQLCODE );
2768 FND_FILE.put_line(FND_FILE.log, 'Challan Detail Error - ' || '. ERROR : ' || p_err );
2769
2770 -- Harshita for Bug 4640996
2771 IF p_action <> 'V' THEN
2772 raise_application_error(-20023, 'Challan Detail Error - ' || '. ERROR : ' || p_err );
2773 END IF ;
2774 END check_numeric;
2775
2776 /* Functional Related Procedures */
2777
2778 PROCEDURE quarterly_returns(
2779 p_err_buf OUT NOCOPY VARCHAR2,
2780 p_ret_code OUT NOCOPY NUMBER,
2781 --p_legal_entity_id IN NUMBER, --commented by csahoo for bug#6158875
2782 --p_profile_org_id IN NUMBER, --commented by csahoo for bug#6158875
2783 p_tan_number IN VARCHAR2,
2784 p_fin_year IN NUMBER,
2785 p_period IN VARCHAR2,
2786 p_tax_authority_id IN NUMBER,
2790 p_deductor_state IN VARCHAR2,
2787 p_tax_authority_site_id IN NUMBER,
2788 p_organization_id IN NUMBER,
2789 p_deductor_name IN VARCHAR2,
2791 p_addrChangedSinceLastRet IN VARCHAR2,
2792 p_deductor_status IN VARCHAR2,
2793 p_persRespForDeduction IN VARCHAR2,
2794 p_desgOfPersResponsible IN VARCHAR2,
2795 p_RespPers_flat_no IN VARCHAR2 , -- Bug 6030953
2796 p_RespPers_prem_bldg IN VARCHAR2 , -- Bug 6030953
2797 p_RespPers_rd_st_lane IN VARCHAR2 , -- Bug 6030953
2798 p_RespPers_area_loc IN VARCHAR2 , -- Bug 6030953
2799 p_RespPers_tn_cty_dt IN VARCHAR2 , -- Bug 6030953
2800 p_RespPersState IN VARCHAR2 ,
2801 p_RespPersPin IN NUMBER ,
2802 p_RespPers_tel_no IN VARCHAR2 , -- Bug 6030953
2803 p_RespPers_email IN VARCHAR2 , -- Bug 6030953
2804 p_RespPersAddrChange IN VARCHAR2,
2805 p_challan_Start_Date IN VARCHAR2,
2806 p_challan_End_Date IN VARCHAR2,
2807 p_pro_rcpt_num_org_ret IN NUMBER,
2808 p_file_path IN VARCHAR2,
2809 p_filename IN VARCHAR2,
2810 p_action IN VARCHAR2 DEFAULT NULL,
2811 p_include_list IN VARCHAR2,
2812 p_exclude_list IN VARCHAR2 --Date 11-05-2007 by Sacsethi for bug 5647248
2813 )
2814 IS
2815
2816
2817 --Date 11-05-2007 by Sacsethi for bug 5647248
2818 -- start 5647248
2819
2820
2821 cursor c_prg_name(cp_conc_prg in number)
2822 is
2823 select concurrent_program_name
2824 from fnd_concurrent_programs_vl
2825 where concurrent_program_id= cp_conc_prg;
2826
2827 l_prg_name varchar2(50);
2828 l_prg_id number(15);
2829 l_form_number varchar2(5);
2830
2831 lv_req_id NUMBER;
2832 lv_result BOOLEAN;
2833 lv_request_desc VARCHAR2(200);
2834
2835 Cursor c_get_state_desc
2836 is
2837 select
2838 description
2839 from FND_FLEX_VALUES_VL a
2840 where flex_value_set_id =
2841 ( select flex_value_set_id
2842 from fnd_flex_value_sets
2843 where flex_value_set_name ='JA_IN_INDIAN_STATES'
2844 )
2845 and flex_value = to_number(p_RespPersState) ;
2846
2847 lv_state_desc fnd_flex_values_vl.description%TYPE ;
2848 ld_challan_start_date DATE; --added by csahoo for bug#6158875
2849 ld_challan_end_date DATE; --added by csahoo for bug#6158875
2850 BEGIN
2851
2852 l_prg_id:=FND_GLOBAL.CONC_PROGRAM_ID;
2853 ld_challan_start_date := fnd_date.canonical_to_date(p_challan_Start_Date); --added by csahoo for bug#6158875
2854 ld_challan_end_date := fnd_date.canonical_to_date(p_challan_End_Date); --added by csahoo for bug#6158875
2855
2856 open c_prg_name(l_prg_id);
2857 fetch c_prg_name into l_prg_name;
2858 close c_prg_name;
2859
2860 if l_prg_name='JAINETDSQ' then
2861 l_form_number:='26Q';
2862 elsif l_prg_name='JAINE27Q' then
2863 l_form_number:='27Q';
2864 end if;
2865
2866 generate_etds_returns
2867 (p_err_buf => p_err_buf ,
2868 p_ret_code => p_ret_code ,
2869 p_tan_number => p_tan_number ,
2870 p_fin_year => p_fin_year ,
2871 p_period => p_period ,
2872 p_tax_authority_id => p_tax_authority_id ,
2873 p_tax_authority_site_id => p_tax_authority_site_id ,
2874 p_organization_id => p_organization_id ,
2875 p_deductor_name => p_deductor_name ,
2876 p_deductor_state => p_deductor_state ,
2877 p_addrChangedSinceLastRet => p_addrChangedSinceLastRet ,
2878 p_deductor_status => p_deductor_status ,
2879 p_persRespForDeduction => p_persRespForDeduction ,
2880 p_desgOfPersResponsible => p_desgOfPersResponsible ,
2881 -- bug 6030953. Added by Lakshmi Gopalsami
2882 p_RespPers_flat_no => p_RespPers_flat_no ,
2883 p_RespPers_prem_bldg => p_RespPers_prem_bldg ,
2884 p_RespPers_rd_st_lane => p_RespPers_rd_st_lane ,
2885 p_RespPers_area_loc => p_RespPers_area_loc ,
2886 p_RespPers_tn_cty_dt => p_RespPers_tn_cty_dt ,
2887 -- end for bug 6030953
2888 p_RespPersState => p_RespPersState ,
2889 p_RespPersPin => p_RespPersPin ,
2890 -- bug 6030953. Added by Lakshmi Gopalsami
2891 p_RespPers_tel_no => p_RespPers_tel_no ,
2892 p_RespPers_email => p_RespPers_email ,
2893 -- end for bug 6030953
2894 p_RespPersAddrChange => p_RespPersAddrChange ,
2895 pv_challan_Start_Date => p_challan_Start_Date ,
2896 pv_challan_End_Date => p_challan_End_Date ,
2897 p_pro_rcpt_num_org_ret => p_pro_rcpt_num_org_ret ,
2898 p_file_path => p_file_path ,
2899 p_filename => p_filename ,
2900 p_action => p_action ,
2901 p_form_number => l_form_number ,
2902 p_include_list => p_include_list ,
2903 p_exclude_list => p_exclude_list
2904 ) ;
2905
2906
2907 open c_get_state_desc ;
2908 fetch c_get_state_desc into lv_state_desc ;
2909 close c_get_state_desc ;
2910
2911 lv_request_desc := 'India - TDS Form 27A for Batch Id '|| ln_batch_id;
2912
2916 --p_legal_entity_id, --Legal Entity Id --commented by csahoo for bug#6158875
2913 lv_result := FND_REQUEST.set_mode(true);
2914 lv_req_id := FND_REQUEST.submit_request(
2915 'JA', 'JAINTDSA', lv_request_desc, '', FALSE,
2917 --p_profile_org_id, --Operating Unit --commented by csahoo for bug#6158875
2918 p_tan_number, --Org Tan Num
2919 p_organization_id, --Organization Id
2920 p_fin_year, --Fin Year
2921 p_period, --Quarter
2922 p_tax_authority_id, --Tax Authority
2923 p_tax_authority_site_id, --Tax Authority Site
2924 l_form_number, --Form Number
2925 p_pro_rcpt_num_org_ret, --Previous Receipt Number
2926 -- Bug 6030953. Added by Lakshmi Gopalsami
2927 p_deductor_status, -- Type of deductor.
2928 p_persRespForDeduction, --Person Responsible
2929 p_desgOfPersResponsible, --Responsible person Designation
2930 -- bug 6030953. Added by Lakshmi Gopalsami
2931 p_RespPers_flat_no, -- Responsible person Flat No.
2932 p_RespPers_prem_bldg, -- Name of premises/Bldg
2933 p_RespPers_rd_st_lane, -- Road/Street/Lane
2934 p_RespPers_area_loc, -- Area/Location
2935 p_RespPers_tn_cty_dt, -- Town/city/District
2936 lv_state_desc, -- Responsible person State
2937 p_RespPersPin, -- Responsible Person Pin
2938 p_RespPers_tel_no, -- Telephone number
2939 p_RespPers_email, -- Email
2940 -- end for bug 6030953
2941 p_challan_Start_Date, --MODIFIED BY CSAHOO FOR BUG#6158875
2942 p_challan_End_Date, --MODIFIED BY CSAHOO FOR BUG#6158875
2943 CHR(0), '', '', '', '', '', '',
2944 '', '', '', '', '', '', '', '', '', '',
2945 '', '', '', '', '', '', '', '', '', '',
2946 '', '', '', '', '', '', '', '', '', '',
2947 '', '', '', '', '', '', '', '', '', '',
2948 '', '', '', '', '', '', '', '', '', '',
2949 '', '', '', '', '', '', '', '', '', '',
2950 '', '', '', '', '', '', '', ''
2951 );
2952
2953
2954
2955
2956 END quarterly_returns;
2957
2958 PROCEDURE yearly_returns(
2959 p_err_buf OUT NOCOPY VARCHAR2,
2960 p_ret_code OUT NOCOPY NUMBER,
2961 --p_legal_entity_id IN NUMBER, --commented by csahoo for bug#6158875
2962 --p_profile_org_id IN NUMBER, --commented by csahoo for bug#6158875
2963 p_tan_number IN VARCHAR2,
2964 p_fin_year IN NUMBER,
2965 p_organization_id IN NUMBER,
2966 p_tax_authority_id IN NUMBER,
2967 p_tax_authority_site_id IN NUMBER,
2968 p_deductor_name IN VARCHAR2,
2969 p_deductor_state IN VARCHAR2,
2970 p_addrChangedSinceLastRet IN VARCHAR2,
2971 p_deductor_status IN VARCHAR2,
2972 p_persRespForDeduction IN VARCHAR2,
2973 p_desgOfPersResponsible IN VARCHAR2,
2974 p_challan_Start_Date IN VARCHAR2,
2975 p_challan_End_Date IN VARCHAR2,
2976 --p_pro_rcpt_num_org_ret IN NUMBER, --commented by csahoo for bug#6158875
2977 p_file_path IN VARCHAR2,
2978 p_filename IN VARCHAR2,
2979 p_generate_headers IN VARCHAR2 DEFAULT NULL
2980 )
2981 IS
2982 ld_challan_start_date DATE;
2983 ld_challan_end_date DATE;
2984 BEGIN
2985
2986 ld_challan_start_date := fnd_date.canonical_to_date(p_challan_Start_Date); --added by csahoo for bug#6158875
2987 ld_challan_end_date := fnd_date.canonical_to_date(p_challan_End_Date); --added by csahoo for bug#6158875
2988 generate_etds_returns
2989 (
2990 p_err_buf => p_err_buf ,
2991 p_ret_code => p_ret_code ,
2992 p_tan_number => p_tan_number ,
2993 p_fin_year => p_fin_year ,
2994 p_tax_authority_id => p_tax_authority_id ,
2995 p_tax_authority_site_id => p_tax_authority_site_id ,
2996 p_organization_id => p_organization_id ,
2997 p_deductor_name => p_deductor_name ,
2998 p_deductor_state => p_deductor_state ,
2999 p_addrChangedSinceLastRet => p_addrChangedSinceLastRet ,
3000 p_deductor_status => p_deductor_status ,
3001 p_persRespForDeduction => p_persRespForDeduction ,
3002 p_desgOfPersResponsible => p_desgOfPersResponsible ,
3003 pv_challan_Start_Date => p_challan_Start_Date ,
3004 pv_challan_End_Date => p_challan_End_Date ,
3005 p_pro_rcpt_num_org_ret => NULL , --modified by csahoo for bug#6158875
3006 p_file_path => p_file_path ,
3007 p_filename => p_filename ,
3008 p_generate_headers => p_generate_headers
3009 ) ;
3010
3011
3012 END yearly_returns;
3013 -- ended, Harshita for Bug 4525089
3014
3015
3016
3017
3018 PROCEDURE generate_etds_returns
3019 (
3020 p_err_buf OUT NOCOPY VARCHAR2,
3021 p_ret_code OUT NOCOPY NUMBER,
3022 p_tan_number IN VARCHAR2,
3026 p_tax_authority_site_id IN NUMBER,
3023 p_fin_year IN NUMBER,
3024 p_organization_id IN NUMBER, -- Harshita for Bug 4889272
3025 p_tax_authority_id IN NUMBER,
3027 p_deductor_name IN VARCHAR2,
3028 p_deductor_state IN VARCHAR2,
3029 p_addrChangedSinceLastRet IN VARCHAR2,
3030 p_deductor_status IN VARCHAR2,
3031 p_persRespForDeduction IN VARCHAR2,
3032 p_desgOfPersResponsible IN VARCHAR2,
3033 pv_challan_Start_Date IN VARCHAR2, /* rallamse for bu# 4334682 changed to varchar2 from date */
3034 pv_challan_End_Date IN VARCHAR2, /* rallamse for bu# 4334682 changed to varchar2 from date */
3035 p_pro_rcpt_num_org_ret IN NUMBER,
3036 p_file_path IN VARCHAR2,
3037 p_filename IN VARCHAR2,
3038 p_generate_headers IN VARCHAR2 DEFAULT NULL,
3039 p_period IN VARCHAR2 DEFAULT NULL,
3040 p_RespPers_flat_no IN VARCHAR2 DEFAULT NULL, -- Bug 6030953
3041 p_RespPers_prem_bldg IN VARCHAR2 DEFAULT NULL, -- Bug 6030953
3042 p_RespPers_rd_st_lane IN VARCHAR2 DEFAULT NULL, -- Bug 6030953
3043 p_RespPers_area_loc IN VARCHAR2 DEFAULT NULL, -- Bug 6030953
3044 p_RespPers_tn_cty_dt IN VARCHAR2 DEFAULT NULL, -- Bug 6030953
3045 p_RespPersState IN VARCHAR2 DEFAULT NULL,
3046 p_RespPersPin IN NUMBER DEFAULT NULL,
3047 p_RespPers_tel_no IN VARCHAR2 DEFAULT NULL, -- Bug 6030953
3048 p_RespPers_email IN VARCHAR2 DEFAULT NULL, -- Bug 6030953
3049 p_RespPersAddrChange IN VARCHAR2 DEFAULT NULL,
3050 p_action IN VARCHAR2 DEFAULT NULL,
3051 p_form_number IN VARCHAR2 DEFAULT NULL, --Date 11-05-2007 by Sacsethi for bug 5647248
3052 p_include_list IN VARCHAR2 DEFAULT NULL,
3053 p_exclude_list IN VARCHAR2 DEFAULT NULL
3054
3055 )IS
3056
3057 lv_object_name VARCHAR2(61); -- := '<Package_name>.<procedure_name>'; /* Added by Ramananda for bug#4407165 */
3058 lv_oth_reg_type CONSTANT VARCHAR2(30) := 'OTHERS';
3059 lv_prim_att_type CONSTANT VARCHAR2(30) := 'OTHERS' ; -- 'PRIMARY'; -- Harshita for Bug 4889272
3060 lv_pan_att_code CONSTANT VARCHAR2(30) := 'PAN NO';
3061
3062 /* rallamse for bug# 4336482 */
3063 p_challan_Start_Date DATE; --File.Sql.35 Cbabu DEFAULT fnd_date.canonical_to_date(pv_challan_Start_Date);
3064 p_challan_End_Date DATE; --File.Sql.35 Cbabu DEFAULT fnd_date.canonical_to_date(pv_challan_End_Date);
3065
3066 -- to get financial and assessment years
3067 CURSOR c_fin_year(p_tan_number /*p_legal_entity_id*/ IN VARCHAR2, p_fin_year IN NUMBER, p_organization_id IN NUMBER ) IS
3068 SELECT start_date, end_date
3069 FROM JAI_AP_TDS_YEARS
3070 -- added, Harshita for Bug 4889272
3071 where TAN_NO = p_tan_number
3072 AND fin_year = p_fin_year
3073 and legal_entity_id = p_organization_id;
3074
3075 -- commented, Harshita for Bug 4889272
3076 /*
3077 WHERE legal_entity_id = p_legal_entity_id
3078 AND fin_year = p_fin_year;
3079 */
3080
3081 -- to get Organization related to TAN
3082 CURSOR c_organization_id(p_tan_number IN VARCHAR2) IS
3083 SELECT organization_id
3084 FROM jai_ap_tds_org_tan_v --- JAI_AP_TDS_ORG_TANS is changed to view jai_ap_tds_org_tan_v 4323338
3085 WHERE org_tan_num = p_tan_number;
3086
3087 -- to get PAN related to TAN
3088 CURSOR c_pan_number(p_organization_id IN NUMBER) IS --Harshita for bug#4889272
3089 /* SELECT jpr.attribute_value ORG_TAN_NUM
3090 FROM jai_rgm_parties jrp,
3091 JAI_RGM_REGISTRATIONS jrr,
3092 jai_rgm_party_regns jpr
3093 WHERE jrp.regime_org_id = jpr.regime_org_id
3094 AND jpr.registration_id = jrr.registration_id
3095 AND jrp.organization_id = p_organization_id
3096 AND jrr.registration_type = lv_oth_reg_type
3097 AND jrr.attribute_type_code = lv_prim_att_type
3098 AND jrr.attribute_code = lv_pan_att_code;*/
3099 SELECT NVL(jpr.attribute_value, jrr.attribute_value) ORG_TAN_NUM
3100 FROM
3101 jai_rgm_parties jrp,
3102 JAI_RGM_REGISTRATIONS jrr,
3103 jai_rgm_party_regns jpr
3104 WHERE
3105 jrp.regime_org_id = jpr.regime_org_id(+)
3106 AND ( jpr.registration_id is null or jpr.registration_id = jrr.registration_id )
3107 AND jrp.organization_id = p_organization_id
3108 AND jrr.registration_type = lv_oth_reg_type
3109 AND jrr.attribute_type_code = lv_prim_att_type
3110 AND jrr.attribute_code = lv_pan_att_code;
3111
3112
3113 -- Cursor to check whether the organization is an Operating Unit
3114 -- commented by ssumaith - bug# 4448789
3115 /*
3116 CURSOR c_ou_check(p_organization_id IN NUMBER) IS
3117 SELECT organization_id, to_number(legal_entity_id)
3118 FROM hr_operating_units
3119 WHERE organization_id = p_organization_id;
3120 */
3121 -- Cursor to check whether the organization is Legal Entity
3122 CURSOR c_le_check(p_organization_id IN NUMBER) IS
3123 SELECT 1
3124 FROM hr_legal_entities
3125 WHERE organization_id = p_organization_id;
3126
3127 -- gives Location_id linked to Organization
3128 CURSOR c_location_linked_to_org(p_organization_id IN NUMBER) IS
3129 SELECT location_id
3130 FROM hr_all_organization_units
3131 WHERE organization_id = p_organization_id;
3132
3133 -- to get address details of location linked to given organization
3134 CURSOR c_address_details(p_location_id IN NUMBER) IS
3138 WHERE location_id = p_location_id;
3135 SELECT location_code, address_line_1, address_line_2, address_line_3, null, null,
3136 REPLACE(postal_code, ' ') postal_code
3137 FROM hr_locations_all
3139
3140 -- gives the Deductee code based on Vendor Classification
3141 CURSOR c_deductee_dtls(p_vendor_id IN NUMBER) IS
3142 SELECT vendor_name,
3143 decode( UPPER(organization_type_lookup_code), 'COMPANY', '01', '02')
3144 FROM po_vendors
3145 WHERE vendor_id = p_vendor_id;
3146
3147 -- gives the Deductee code based on Vendor Classification
3148 CURSOR c_deductee_site_dtls(p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER) IS
3149 SELECT address_line1 addr1, address_line2 addr2, address_line3 addr3, address_line4 addr4, city,
3150 UPPER(state) state, REPLACE(zip,' ') zip --Removed to_number for Bug 6281440
3151 FROM po_vendor_sites_all
3152 WHERE vendor_id = p_vendor_id
3153 AND vendor_site_id = p_vendor_site_id;
3154
3155 v_deductee_state_code NUMBER;
3156 CURSOR c_state_code(p_state_name IN VARCHAR2,p_state_type IN VARCHAR2) IS
3157 SELECT meaning
3158 FROM ja_lookups --fnd_common_lookups /* Ramananda for bug#4555466 */
3159 WHERE lookup_type = p_state_type
3160 AND lookup_code = p_state_name;
3161
3162 CURSOR c_deductee_pan(p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER) IS
3163 SELECT pan_no
3164 FROM JAI_AP_TDS_VENDOR_HDRS
3165 WHERE vendor_id = p_vendor_id
3166 AND vendor_site_id = p_vendor_site_id;
3167
3168 CURSOR c_check_dtls(p_check_id IN NUMBER) IS
3169 SELECT check_date
3170 FROM ap_checks_all
3171 WHERE check_id = p_check_id;
3172
3173 CURSOR c_cert_issue_date(p_tds_invoice_id IN NUMBER) IS
3174 SELECT a.issue_date
3175 FROM JAI_AP_TDS_F16_HDRS_ALL a, JAI_AP_TDS_F16_DTLS_ALL b
3176 WHERE a.CERTIFICATE_NUM = b.CERTIFICATE_NUM
3177 AND a.org_tan_num = b.org_tan_num
3178 AND a.fin_yr = b.fin_yr
3179 AND b.tds_invoice_id = p_tds_invoice_id;
3180
3181
3182 /* CURSOR c_challan_records(p_batch_id IN NUMBER) IS
3183 select *
3184 from JAI_AP_ETDS_T a
3185 where a.batch_id = p_batch_id
3186 and a.consider_for_challan=1
3187 FOR UPDATE OF challan_line_num;
3188
3189 bug#3708878 above cursor definition replaced by the definition below
3190 */
3191
3192 lv_dummy_date DATE;
3193
3194 /* CURSOR c_challan_records(p_batch_id IN NUMBER) IS
3195 SELECT tds_section, bank_branch_code, challan_num, challan_date, sum(tds_amount) tds_amount
3196 FROM JAI_AP_ETDS_T a
3197 WHERE a.batch_id = p_batch_id
3198 AND a.consider_for_challan=1
3199 GROUP BY tds_section, bank_branch_code, challan_num, challan_date;*/
3200
3201 CURSOR c_challan_records(p_batch_id IN NUMBER) IS
3202 select NVL(tds_section,'No Section') tds_section,
3203 NVL(bank_branch_code,'No Bank Branch') bank_branch_code,
3204 NVL(challan_num,'No Challan Number') challan_num,
3205 NVL(challan_date,lv_dummy_date) challan_date,
3206 check_number check_number,
3207 sum(tds_amount) tds_amount,
3208 sum(amt_of_tds) amt_of_tds,
3209 sum(amt_of_surcharge) amt_of_surcharge,
3210 sum(amt_of_cess) amt_of_cess
3211 from JAI_AP_ETDS_T a
3212 where a.batch_id = p_batch_id
3213 and a.consider_for_challan=1
3214 group by NVL(tds_section,'No Section'), NVL(bank_branch_code,'No Bank Branch'),
3215 NVL(challan_num,'No Challan Number'), NVL(challan_date,lv_dummy_date),
3216 check_number;
3217 cd c_challan_records%ROWTYPE ;
3218
3219
3220 /*CURSOR c_deductee_records(p_batch_id IN NUMBER) IS
3221 SELECT *
3222 FROM JAI_AP_ETDS_T a
3223 WHERE a.batch_id = p_batch_id
3224 AND a.consider_for_challan=1
3225 FOR UPDATE OF deductee_line_num;*/
3226
3227 CURSOR c_deductee_records(p_batch_id IN NUMBER, p_challan_line_num IN NUMBER) IS
3228 select
3229 base_vendor_id,challan_line_num, base_vendor_site_id,tds_tax_id,
3230 NVL(tds_section,'No Section') tds_section,
3231 NVL(bank_branch_code,'No Bank Branch') bank_branch_code,
3232 NVL(challan_num,'No Challan Number') challan_num,
3233 NVL(challan_date,lv_dummy_date) challan_date,
3234 check_number,
3235 tds_tax_rate,
3236 max(certificate_issue_date) certificate_issue_date,
3237 max( base_invoice_date) base_invoice_date ,
3238 max(tds_check_date) tds_check_date,
3239 max(tds_invoice_date) tds_invoice_date,
3240 sum(amt_of_tds) amt_of_tds,
3241 sum(amt_of_surcharge) amt_of_surcharge,
3242 sum(amt_of_cess) amt_of_cess,
3243 sum(base_taxabale_amount) base_taxabale_amount,
3244 sum(tds_amount) tds_amount
3245 from JAI_AP_ETDS_T a
3246 where a.batch_id = p_batch_id
3247 and a.consider_for_challan=1
3248 and challan_line_num = NVL(p_challan_line_num, challan_line_num)
3249 group by challan_line_num, base_vendor_id, base_vendor_site_id,tds_tax_id,
3250 NVL(tds_section,'No Section'), NVL(bank_branch_code,'No Bank Branch'),
3251 NVL(challan_num,'No Challan Number'), NVL(challan_date,lv_dummy_date),
3252 check_number,tds_tax_rate;
3253 dd c_deductee_records%ROWTYPE ;
3254
3258 WHERE invoice_id = p_invoice_id
3255 CURSOR c_base_inv_gl_date(p_invoice_id IN NUMBER, p_tax_id IN NUMBER,p_line_type ap_invoice_distributions_all.line_type_lookup_code%type) IS
3256 SELECT accounting_date
3257 FROM ap_invoice_distributions_all
3259 AND line_type_lookup_code = p_line_type
3260 AND global_attribute1 = to_char(p_tax_id);--rchandan for bug#4333488
3261
3262 CURSOR c_stform_type(p_tax_id IN NUMBER) IS
3263 SELECT stform_type
3264 FROM JAI_CMN_TAXES_ALL
3265 WHERE tax_id = p_tax_id;
3266 /*
3267 CURSOR c_deductee_cnt(p_batch_id IN NUMBER , p_check_number IN NUMBER ) IS
3268 select sum ( count( base_vendor_id ) ) -- distinct removed the distinct
3269 from JAI_AP_ETDS_T
3270 WHERE batch_id = p_batch_id
3271 and nvl(tds_section, 'No Section') = nvl(cd.tds_section, 'No Section')
3272 and nvl(challan_num,'No Challan Number') = nvl(cd.challan_num, 'No Challan Number')
3273 and nvl(challan_date, lv_dummy_date) = nvl(cd.challan_date, lv_dummy_date )
3274 and nvl(bank_branch_code, 'No Bank Branch') = nvl(cd.bank_branch_code, 'No Bank Branch')
3275 and consider_for_challan=1
3276 and check_number = p_check_number
3277 group by base_vendor_id, base_vendor_site_id,tds_tax_id ;
3278 */
3279
3280 /*ADDED BY CSAHOO FOR BUG#6158875*/
3281 CURSOR c_deductee_cnt(p_batch_id IN NUMBER ,
3282 p_check_number IN NUMBER,
3283 p_tds_section IN VARCHAR2 ,
3284 p_challan_num IN varchar2,
3285 p_challan_date IN DATE,
3286 p_bank_branch_code IN VARCHAR2) IS
3287 select sum ( count( distinct base_vendor_id ) )
3288 from JAI_AP_ETDS_T
3289 WHERE batch_id = p_batch_id
3290 and nvl(tds_section, 'No Section') = nvl(p_tds_section, 'No Section')
3291 and nvl(challan_num,'No Challan Number') = nvl(p_challan_num, 'No Challan Number')
3292 and nvl(challan_date, lv_dummy_date) = nvl(p_challan_date, lv_dummy_date )
3293 and nvl(bank_branch_code, 'No Bank Branch') = nvl(p_bank_branch_code, 'No Bank Branch')
3294 and consider_for_challan=1
3295 and check_number = p_check_number
3296 group by base_vendor_id, base_vendor_site_id,tds_tax_id ;
3297
3298
3299 CURSOR c_get_errors(cp_batch_id JAI_AP_ETDS_T.batch_id%TYPE ) IS
3300 Select Error_Message from jai_ap_etds_errors_t
3301 where batch_id = cp_batch_id ;
3302
3303 -- File Header Variables
3304 v_line_number NUMBER(6); --File.Sql.35 Cbabu := 0;
3305 v_record_type VARCHAR2(2);
3306 v_file_type VARCHAR2(3) ; --:= 'TDS' File.Sql.35 by Brathod
3307 v_quartfile_type CHAR(3);
3308 v_upload_type VARCHAR2(1) ; --:= 'R' File.Sql.35 by Brathod
3309 v_file_creation_date Date; -- := SYSDATE File.Sql.35 by Brathod
3310 v_file_sequence_number NUMBER(8);
3311 v_deductor_tan VARCHAR2(20);
3312 v_number_of_batches NUMBER(4); --File.Sql.35 Cbabu := 1;
3313
3314 -- Batch Header Variables
3315 v_batch_number NUMBER(4);
3316 v_challan_cnt NUMBER(5); --File.Sql.35 Cbabu := 0;
3317 v_deductee_cnt NUMBER(5); --File.Sql.35 Cbabu := 0;
3318 v_form_number CHAR(4);
3319 v_rrr_number NUMBER(10);
3320 v_rrr_date CHAR(8);
3321 v_deductor_pan VARCHAR2(200);
3322
3323 v_quart_form_number VARCHAR2(3) ;
3324 v_deductor_branch VARCHAR2(75);
3325 v_deductor_email VARCHAR2(75);
3326 v_deductor_stdCode NUMBER(5);
3327 v_deductor_phoneNo NUMBER(10);
3328 v_RespPerson_address2 VARCHAR2(25);
3329 v_RespPerson_address3 VARCHAR2(25);
3330 v_RespPerson_address4 VARCHAR2(25);
3331 v_RespPerson_address5 VARCHAR2(25);
3332 v_RespPerson_email VARCHAR2(75);
3333 v_RespPerson_remark VARCHAR2(75);
3334 v_RespPerson_stdCode INTEGER(5) ;
3335 v_RespPerson_phoneNo INTEGER(10) ;
3336 v_RespPerson_addressChange VARCHAR2(1);
3337 v_bh_trnType VARCHAR2(1) ;
3338 v_bh_batchUpd VARCHAR2(1) ;
3339 v_bh_org_RRRno VARCHAR2(1) ;
3340 v_bh_prev_RRRno VARCHAR2(1) ;
3341 v_bh_RRRno VARCHAR2(1) ;
3342 v_bh_RRRdate VARCHAR2(1) ;
3343 v_bh_deductor_last_tan VARCHAR2(1) ;
3344 v_bh_tds_circle VARCHAR2(1) ;
3345 v_bh_salaryRecords_count VARCHAR2(1) ;
3346 v_bh_gross_total VARCHAR2(1) ;
3347 v_ao_approval VARCHAR2(1) ;
3348 v_ao_approval_number VARCHAR2(15);
3349 v_bh_recHash VARCHAR2(1) ;
3350
3351
3352 -- Input Variables
3353 v_tds_vendor_id NUMBER;
3354
3355 v_fin_year VARCHAR2(4);
3356 v_address_organization_id NUMBER;
3357 v_deductor_name VARCHAR2(75);
3358 v_tan_state_code NUMBER(2);
3359 v_addrChangedSinceLastReturn VARCHAR2(1);
3360 v_deductor_status VARCHAR2(1);
3361 v_personNameRespForDedection VARCHAR2(75);
3362 v_personDesgnRespForDedection VARCHAR2(20);
3363 v_challan_start_date DATE;
3364 v_challan_end_date DATE;
3365
3366 -- Derived variables
3367 -- v_tan_org_id NUMBER;
3368 v_org_id NUMBER;
3369 v_financial_year VARCHAR2(6);
3370 v_assessment_year VARCHAR2(6);
3371 --v_legal_entity_id NUMBER; -- Harshita for Bug 4889272
3372 v_location_id NUMBER;
3373 v_tan_pin NUMBER(6);
3377 v_location_code HR_LOCATIONS_ALL.location_code%TYPE;
3374 v_quarterlyOrYearly VARCHAR2(2); -- := 'Y'; -- as given by Vikram File.Sql.35 by Brathod
3375
3376 -- added location_code and modified the types of address variables to refer table column types by Vijay Shankar for Bug#4057192
3378 v_tan_address1 HR_LOCATIONS_ALL.address_line_1%TYPE;
3379 v_tan_address2 HR_LOCATIONS_ALL.address_line_2%TYPE;
3380 v_tan_address3 HR_LOCATIONS_ALL.address_line_3%TYPE;
3381 v_tan_address4 VARCHAR2(75);
3382 v_tan_address5 VARCHAR2(75);
3383 v_postal_code HR_LOCATIONS_ALL.postal_code%TYPE;
3384 v_totTaxDeductedAsPerChallan NUMBER;
3385 v_totTaxDeductedAsPerDeductee NUMBER;
3386
3387 v_challan_dtl_slno NUMBER; --File.Sql.35 Cbabu := 0;
3388 v_bank_branch_code VARCHAR2(7) ;
3389
3390 v_deductee_slno NUMBER; --File.Sql.35 Cbabu := 0;
3391 v_deductee_code VARCHAR2(2);
3392 v_deductee_pan VARCHAR2(20);
3393 v_stform_name VARCHAR2(25);
3394 v_vendor_name PO_VENDORS.vendor_name%TYPE;
3395 v_site_dtls c_deductee_site_dtls%ROWTYPE;
3396 v_base_inv_check_dtls c_check_dtls%ROWTYPE;
3397 v_tds_inv_check_dtls c_check_dtls%ROWTYPE;
3398 v_cert_issue_date DATE;
3399 v_grossing_up_factor VARCHAR2(1);
3400 v_reason_for_nDeduction VARCHAR2(1);
3401 v_filler NUMBER(14);
3402
3403 v_gl_date DATE;
3404 v_batch_id NUMBER;
3405
3406 v_ch_updIndicator VARCHAR2(1) ;
3407 v_filler1 VARCHAR2(1) ;
3408 v_filler2 VARCHAR2(1) ;
3409 v_filler3 VARCHAR2(1) ;
3410 v_filler4 VARCHAR2(1) ;
3411 v_last_bank_challan_no VARCHAR2(1) ;
3412 v_bank_challan_no NUMBER(5) ;
3413 v_last_transfer_voucher_no VARCHAR2(1) ;
3414 v_transfer_voucher_no NUMBER(9) ;
3415 v_last_bank_branch_code VARCHAR2(1) ;
3416 v_challan_lastDate VARCHAR2(1) ;
3417 v_challan_Date DATE ;
3418 v_filler5 VARCHAR2(1) ;
3419 v_filler6 VARCHAR2(1) ;
3420 v_last_total_depositAmt VARCHAR2(1) ;
3421
3422 v_total_deposit NUMBER(15,2) ;
3423 v_book_entry VARCHAR2(1) ;
3424 v_ch_recHash VARCHAR2(1) ;
3425 v_nil_challan_indicator VARCHAR2(1) ;
3426 v_remarks VARCHAR2(14) ;
3427 v_remarks2 VARCHAR2(75);
3428 v_remarks3 VARCHAR2(14);
3429
3430
3431 v_quart_deductee_code VARCHAR2(1) ;
3432 v_deductee_pan_refno NUMBER(10);
3433 v_dh_mode VARCHAR2(1) ;
3434 v_emp_serial_no VARCHAR2(1) ;
3435 v_last_emp_pan VARCHAR2(1) ;
3436 v_last_emp_pan_refno VARCHAR2(1) ;
3437 v_deductee_total_tax_deducted NUMBER(15,2) ;
3438 v_last_total_tax_deducted VARCHAR2(1) ;
3439 v_deductee_total_tax_deposit NUMBER(15);
3440 v_last_total_tax_deposit VARCHAR2(1) ;
3441 v_total_purchase VARCHAR2(1) ;
3442 v_deposit_date VARCHAR2(1) ;
3443 v_grossingUp_ind VARCHAR2(1) ;
3444 v_certificate_issue_date VARCHAR2(1) ; -- another declaration exists.
3445 v_dh_recHash VARCHAR2(1) ;
3446 lv_etds_yearly_returns VARCHAR2(1) ;
3447 v_challan_line_num NUMBER ;
3448 ln_amt_of_tds NUMBER(15,2) ;
3449 --v_tds_tax_rate NUMBER(7);
3450 -- ended, Harshita for Bug 4525089
3451
3452 v_ack_num_tan_app NUMBER(14);
3453 v_pro_rcpt_num_org_ret NUMBER(14);
3454 v_book_ent_oth VARCHAR2(1);
3455 v_quart_book_ent_oth VARCHAR2(1); -- Harshita for Bug 4525089,
3456 ln_amt_of_oth number(14);
3457
3458 -- used variables
3459 --v_vendor_name PO_VENDORS.vendor_name%TYPE;
3460 v_vendor_site_code PO_VENDOR_SITES_ALL.vendor_site_code%TYPE;
3461 v_error_message VARCHAR2(100);
3462 v_conc_request_id NUMBER(15); -- := FND_PROFILE.value('CONC_REQUEST_ID'); File.Sql.35 by Brathod
3463 v_statement_id VARCHAR2(3) ; --:= '0'; File.Sql.35 by Brathod
3464
3465 v_start_date DATE;
3466 v_end_date DATE;
3467
3468 v_uploader_type CHAR(1) ;
3469 p_return_code VARCHAR2(1) ;
3470 p_return_message VARCHAR2(2000) ;
3471 lv_generate_headers VARCHAR2(1) ;
3472 ln_errors_exist NUMBER ;
3473 DUMMY VARCHAR2(1) ;
3474 v_fh_recordHash VARCHAR2(1) ;
3475 v_fh_fvuVersion VARCHAR2(1) ;
3476 v_fh_fileHash VARCHAR2(1) ;
3477 v_fh_samVersion VARCHAR2(1) ;
3478 v_fh_samHash VARCHAR2(1) ;
3479 v_fh_scmVersion VARCHAR2(1) ;
3480 v_fh_scmHash VARCHAR2(1) ;
3481 v_q_deductee_cnt NUMBER(9) ;
3482
3483
3484 PROCEDURE process_deductee_records
3485 IS
3486 BEGIN
3487
3488 OPEN c_deductee_records(v_batch_id, v_challan_line_num) ;
3489 LOOP
3490 FETCH c_deductee_records INTO dd ;
3491 EXIT WHEN
3492 c_deductee_records%NOTFOUND ;
3493
3494 v_vendor_name := null;
3495 v_deductee_code := null;
3496 v_deductee_pan := null;
3497 v_site_dtls := null;
3498 --v_tds_inv_check_dtls := null;
3499 --v_base_inv_check_dtls := null;
3500 v_reason_for_nDeduction := null;
3501 v_filler := null;
3502 v_deductee_state_code := null;
3503 --v_gl_date := null;
3504 v_reason_for_nDeduction := null;
3508 v_line_number := v_line_number + 1;
3505 v_stform_name :=null;
3506 --v_tds_tax_rate := null;
3507
3509 v_deductee_slno := v_deductee_slno + 1;
3510
3511 OPEN c_deductee_dtls(dd.base_vendor_id);
3512 FETCH c_deductee_dtls INTO v_vendor_name, v_deductee_code;
3513 CLOSE c_deductee_dtls;
3514
3515 OPEN c_deductee_pan(dd.base_vendor_id, dd.base_vendor_site_id);
3516 FETCH c_deductee_pan INTO v_deductee_pan;
3517 CLOSE c_deductee_pan;
3518
3519 -- START code added for Bug#3841751 as PAN # was not printed, if it setup only at null site
3520 IF v_deductee_pan IS NULL THEN
3521 OPEN c_deductee_pan(dd.base_vendor_id, 0);
3522 FETCH c_deductee_pan INTO v_deductee_pan;
3523 CLOSE c_deductee_pan;
3524 END IF;
3525 -- END code added for Bug#3841751
3526
3527 OPEN c_deductee_site_dtls(dd.base_vendor_id, dd.base_vendor_site_id);
3528 FETCH c_deductee_site_dtls INTO v_site_dtls;
3529 CLOSE c_deductee_site_dtls;
3530
3531 OPEN c_state_code(v_site_dtls.state,'IN_STATE');
3532 FETCH c_state_code INTO v_deductee_state_code;
3533 CLOSE c_state_code;
3534
3535 IF v_deductee_state_code IS NULL THEN
3536 v_deductee_state_code := 99;
3537 END IF;
3538
3539 -- v_reason_for_nDeduction := 'A';
3540 OPEN c_stform_type(dd.tds_tax_id);
3541 FETCH c_stform_type INTO v_stform_name; --, v_tds_tax_rate;
3542 CLOSE c_stform_type;
3543
3544 IF v_stform_name = '197' THEN
3545 v_reason_for_nDeduction := 'A';
3546 ELSIF v_stform_name = '197A' THEN
3547 v_reason_for_nDeduction := 'B';
3548 ELSE
3549 v_reason_for_nDeduction := null;
3550 END IF;
3551
3552 v_book_ent_oth := 'C';
3553
3554 IF lv_etds_yearly_returns = 'Y' THEN -- Harshita for Bug 4525089
3555 jai_ap_tds_etds_pkg.create_deductee_detail
3556 (
3557 p_line_number => v_line_number,
3558 p_record_type => v_record_type,
3559 p_batch_number => v_batch_number,
3560 p_deductee_slno => v_deductee_slno,
3561 p_deductee_section => dd.tds_section,
3562 p_deductee_code => v_deductee_code,
3563 p_deductee_pan => v_deductee_pan,
3564 p_deductee_name => v_vendor_name,
3565 p_deductee_address1 => v_site_dtls.addr1,
3566 p_deductee_address2 => v_site_dtls.addr2,
3567 p_deductee_address3 => v_site_dtls.addr3,
3568 p_deductee_address4 => v_site_dtls.addr4,
3569 p_deductee_address5 => v_site_dtls.city,
3570 p_deductee_state => v_deductee_state_code,
3571 p_deductee_pin => v_site_dtls.zip,
3572 p_filler5 => v_filler,
3573 p_payment_amount => dd.base_taxabale_amount,
3574 p_payment_date => dd.base_invoice_date, /*Changed challan_date to base_invoice_date - Forward Port Bug 6329774*/
3575 p_book_ent_oth => v_book_ent_oth,
3576 p_tax_rate => dd.tds_tax_rate,
3577 p_filler6 => v_filler6,
3578 p_tax_deducted => dd.tds_amount,
3579 p_tax_deducted_date => dd.tds_invoice_date,
3580 p_tax_payment_date => dd.tds_check_date,
3581 p_bank_branch_code => dd.bank_branch_code,
3582 p_challan_no => dd.challan_num,
3583 p_tds_certificate_date => dd.certificate_issue_date,
3584 p_reason_for_nDeduction => v_reason_for_nDeduction,
3585 p_filler7 => v_filler
3586
3587 );
3588 ELSE
3589 IF v_deductee_code = '01' THEN
3590 v_quart_deductee_code := '1' ;
3591 ELSIF v_deductee_code = '02' THEN
3592 v_quart_deductee_code := '2' ;
3593 END IF ;
3594
3595 v_deductee_total_tax_deducted := dd.amt_of_tds + dd.amt_of_surcharge + dd.amt_of_cess ;
3596 v_quart_book_ent_oth := 'N' ;
3597
3598 p_return_code := null ;
3599 p_return_message := null ;
3600
3601 jai_ap_tds_etds_pkg.validate_deductee_detail
3602 ( p_line_number => v_line_number ,
3603 p_record_type => v_record_type ,
3604 p_batch_number => v_batch_number ,
3605 p_challan_line_num => dd.challan_line_num ,
3606 p_deductee_slno => v_deductee_slno ,
3607 p_dh_mode => v_dh_mode ,
3608 p_quart_deductee_code => v_quart_deductee_code ,
3609 p_deductee_pan => v_deductee_pan ,
3610 p_vendor_name => v_vendor_name ,
3611 p_amt_of_tds => dd.amt_of_tds ,
3612 p_amt_of_surcharge => dd.amt_of_surcharge ,
3613 p_amt_of_cess => dd.amt_of_cess ,
3614 p_deductee_total_tax_deducted => v_deductee_total_tax_deducted,
3615 p_base_taxabale_amount => dd.base_taxabale_amount ,
3616 p_gl_date => dd.base_invoice_date , /*Changed challan_date to base_invoice_date - Forward Port Bug 6329774*/
3617 p_book_ent_oth => v_book_ent_oth,
3618 p_return_code => p_return_code,
3619 p_return_message => p_return_message
3620 );
3621 IF p_return_code = 'E' THEN
3625 ( ln_batch_id,'DD', v_line_number, p_return_message ) ;
3622 IF lv_action = 'V' THEN
3623 insert into jai_ap_etds_errors_t
3624 (batch_id, record_type, reference_id, error_message) values
3626 ELSE
3627 p_ret_code := jai_constants.request_error ;
3628 p_err_buf := p_return_message ;
3629 RETURN ;
3630 END IF ;
3631 END IF ;
3632 lv_generate_headers := null ;
3633 IF p_action <> 'V' THEN
3634 IF p_action = 'F' THEN
3635 lv_generate_headers := 'N' ;
3636 ELSIF p_action = 'H' THEN
3637 lv_generate_headers := 'Y' ;
3638 END IF ;
3639
3640 jai_ap_tds_etds_pkg.create_quart_deductee_dtl
3641 (
3642 p_line_number => v_line_number,
3643 p_record_type => v_record_type,
3644 p_batch_number => v_batch_number,
3645 p_dh_challan_recNo => v_challan_dtl_slno,
3646 p_deductee_slno => v_deductee_slno,
3647 p_dh_mode => v_dh_mode,
3648 p_emp_serial_no => v_emp_serial_no,
3649 p_deductee_code => v_quart_deductee_code,
3650 p_last_emp_pan => v_last_emp_pan,
3651 p_deductee_pan => v_deductee_pan,
3652 p_last_emp_pan_refno => v_last_emp_pan_refno,
3653 p_deductee_pan_refno => v_deductee_pan_refno,
3654 p_vendor_name => v_vendor_name,
3655 p_deductee_tds_income_tax => dd.amt_of_tds ,
3656 p_deductee_tds_surcharge => dd.amt_of_surcharge,
3657 p_deductee_tds_cess => dd.amt_of_cess,
3658 p_deductee_total_tax_deducted => v_deductee_total_tax_deducted,
3659 p_last_total_tax_deducted => v_last_total_tax_deducted,
3660 p_deductee_total_tax_deposit => v_deductee_total_tax_deducted,
3661 p_last_total_tax_deposit => v_last_total_tax_deposit,
3662 p_total_purchase => v_total_purchase,
3663 p_base_taxabale_amount => dd.base_taxabale_amount,
3664 p_gl_date => dd.base_invoice_date, /*Changed challan_date to base_invoice_date - Forward Port Bug 6329774*/
3665 p_tds_invoice_date => dd.tds_invoice_date,
3666 p_deposit_date => v_deposit_date,
3667 p_tds_tax_rate => dd.tds_tax_rate, --v_tds_tax_rate,
3668 p_grossingUp_ind => v_grossingUp_ind,
3669 p_book_ent_oth => v_quart_book_ent_oth,
3670 p_certificate_issue_date => v_certificate_issue_date,
3671 p_remarks1 => v_reason_for_nDeduction, -- VIJAY REVIEW v_remarks1,
3672 p_remarks2 => v_remarks2,
3673 p_remarks3 => v_remarks3,
3674 p_dh_recHash => v_dh_recHash,
3675 p_generate_headers => lv_generate_headers
3676 );
3677
3678 END IF ;
3679
3680 END IF ;
3681
3682 UPDATE JAI_AP_ETDS_T
3683 SET deductee_line_num = v_line_number
3684 WHERE batch_id = v_batch_id
3685 and consider_for_challan=1
3686 and challan_line_num = dd.challan_line_num
3687 and base_vendor_id = dd.base_vendor_id
3688 and base_vendor_site_id = dd.base_vendor_site_id
3689 and tds_tax_id = dd.tds_tax_id
3690 and NVL(tds_section,'No Section') = NVL(dd.tds_section,'No Section')
3691 and NVL(bank_branch_code,'No Bank Branch') = NVL(dd.bank_branch_code,'No Bank Branch')
3692 and NVL(challan_num,'No Challan Number') = NVL(dd.challan_num,'No Challan Number')
3693 and NVL(challan_date,lv_dummy_date) = NVL(dd.challan_date,lv_dummy_date)
3694 and check_number = dd.check_number
3695 and tds_tax_rate = dd.tds_tax_rate ;
3696
3697 END LOOP;
3698
3699 CLOSE c_deductee_records ;
3700 END process_deductee_records;
3701
3702
3703 BEGIN
3704
3705 /*---------------------------------------------------------------------------------------------------------------------------------
3706 change history for ja_in_ap_generate_etds_p.sql
3707
3708 SlNo. DD/MM/YYYY Author and Details of Modifications
3709 ---------------------------------------------------------------------------------------------------------------------------------
3710 1 22/03/2004 Vijay Shankar for Bug# 3463974, Version: 619.1
3711 This procedure is created for enhancement to generate eTDS Flat file by making calls to jai_ap_tds_etds_pkg package
3712
3713 2 13/04/2004 Vijay Shankar for Bug# 3603545 (also fixed 3567864), Version: 619.2
3714 Fixed the issues
3715 deductee state code - Using lookup codes in FND_COMMON_LOOKUPS with lookup_type = 'IN_STATE'
3716 Deductee payment date - old date among GL_DATE of invoice and check_date
3717 reason for non deduction - populating A if stform_type of tds tax is 197 and B if 197A and null if others
3718 Also incorporated few error checks and raising errors with relevant message
3719
3720 3 21/06/2004 Aparajita for bug#3708878. Version 115.1.
3724
3721 If more than one TDS invoice has been paid by the same challan, then the challan
3722 details were getting repeated as many times as the number of invoices that has been
3723 grouped for payment. Changed the code to group by challan number and date.
3725 4 02/09/2004 Sanjikum for bug#3841751. Version 115.2.
3726 Issue:-
3727 ETDS CONCURRENT DOES NOT SHOW PAN # IF THE SAME IS SETUP ONLY AT NULL SITE.
3728
3729 Reason:-
3730 TDS Transactions can take place even if setup is maintained only at null
3731 site level without setup at Site level. So for such transactions, no pan# is displayed
3732 Fix:-
3733 In the cursor - c_deductee_pan added the check that if there is no row returned,
3734 then open the same cursor again for vendor_site_id = 0
3735
3736 5 12/12/2004 Vijay Shankar for Bug#4057192. Version 115.3
3737 When postal Code is not a number or number with more that 6 digits, then the application is erroring. which is
3738 rectified with this fix by showing proper message on Postal Code attached to Address Location
3739
3740
3741 6. 2/05/2005 rchandan for bug#4323338. Version 116.1
3742 India Org Info DFF is eliminated as a part of JA migration. A table by name ja_in_ap_tds_org_tan is dropped
3743 and a view jai_ap_tds_org_tan_v is created to capture the PAN No,TAN NO and WARD NO. The code changes are done
3744 to refer to the new view instead of the dropped table.
3745
3746 7. 8/05/2005 rchandan for bug#4333488. Version 116.1
3747 The Invoice Distribution DFF is eliminated and a new global DFF is used to
3748 maintain the functionality. From now the TDS tax, WCT tax and ESSI will not
3749 be populated in the attribute columns of ap_invoice_distributions_all table
3750 instead these will be populated in the global attribute columns. So the code changes are
3751 made accordingly.Both Payment and India distributions DFF elimintion is taken care of in
3752 the same version.
3753
3754 8. 19/05/2005 rallamse for Bug#4336482, Version 116.2
3755 For SEED there is a change in concurrent to use FND_STANDARD_DATE with STANDARD_DATE format
3756 Procedure signature modified by converting DATE datatype
3757 to varchar2 datatype. The varchar2 values are converted to DATE fromat
3758 using fnd_date.canonical_to_date function.
3759
3760 9. 13/07/2007 CSahoo for bug#6158875, File Version 120.12
3761 replaced the AND with OR in the update statement.
3762
3763
3764
3765
3766
3767 Future Dependencies For the release Of this Object:-
3768 (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/
3769 A datamodel change )
3770
3771 ----------------------------------------------------------------------------------------------------------------------------------------------------
3772 Current Version Current Bug Dependent Files Version Author Date Remarks
3773 Of File On Bug/Patchset Dependent On
3774 jai_ap_rpt_apcr_pkg.compute_credit_balance.sql
3775 ----------------------------------------------------------------------------------------------------------------------------------------------------
3776 115.1 3708878 IN60105D2+3603545 jai_ap_tds_etds_pkg.sql Apdas 25/06/2004
3777
3778 ---------------------------------------------------------------------------------------------------------------------------------------------------
3779
3780 ---------------------------------------------------------------------------------------------------------------------------------*/
3781 DUMMY := null ;
3782 v_fh_recordHash := DUMMY ;
3783 v_fh_fvuVersion := DUMMY ;
3784 v_fh_fileHash := DUMMY ;
3785 v_fh_samVersion := DUMMY ;
3786 v_fh_samHash := DUMMY ;
3787 v_fh_scmVersion := DUMMY ;
3788 v_fh_scmHash := DUMMY ;
3789 v_bh_trnType := DUMMY ;
3790 v_bh_batchUpd := DUMMY ;
3791 v_bh_org_RRRno := DUMMY ;
3792 v_bh_prev_RRRno := DUMMY ;
3793 v_bh_RRRno := DUMMY ;
3794 v_bh_RRRdate := DUMMY ;
3795 v_bh_deductor_last_tan := DUMMY ;
3796 v_bh_tds_circle := DUMMY ;
3797 v_bh_salaryRecords_count := DUMMY ;
3798 v_bh_gross_total := DUMMY ;
3799 v_bh_recHash := DUMMY ;
3800 v_ch_updIndicator := DUMMY ;
3801 v_filler1 := DUMMY ;
3802 v_filler2 := DUMMY ;
3803 v_filler3 := DUMMY ;
3804 v_filler4 := DUMMY ;
3805 v_last_bank_challan_no := DUMMY ;
3806 v_last_transfer_voucher_no := DUMMY ;
3807 v_last_bank_branch_code := DUMMY ;
3808 v_challan_lastDate := DUMMY ;
3809 v_filler5 := DUMMY ;
3810 v_filler6 := DUMMY ;
3811 v_last_total_depositAmt := DUMMY ;
3812 v_ch_recHash := DUMMY ;
3813 v_emp_serial_no := DUMMY ;
3817 v_last_total_tax_deposit := DUMMY ;
3814 v_last_emp_pan := DUMMY ;
3815 v_last_emp_pan_refno := DUMMY ;
3816 v_last_total_tax_deducted := DUMMY ;
3818 v_total_purchase := DUMMY ;
3819 v_deposit_date := DUMMY ;
3820 v_grossingUp_ind := DUMMY ;
3821 v_certificate_issue_date := DUMMY ;
3822 v_dh_recHash := DUMMY ;
3823 lv_action := p_action ;
3824
3825 lv_object_name := 'jai_ap_tds_etds_pkg.generate_flat_file'; /* Added by Ramananda for bug#4407165 */
3826
3827
3828 v_upload_type := 'R' ;
3829 v_file_creation_date := SYSDATE ;
3830 v_quarterlyOrYearly :='Y';
3831 v_conc_request_id := FND_PROFILE.value('CONC_REQUEST_ID') ;-- File.Sql.35 by Brathod
3832 p_challan_Start_Date := fnd_date.canonical_to_date(pv_challan_Start_Date);
3833 p_challan_End_Date := fnd_date.canonical_to_date(pv_challan_End_Date);
3834 v_line_number := 0;
3835 v_number_of_batches := 1;
3836 v_challan_cnt := 0;
3837 v_deductee_cnt := 0;
3838 v_challan_dtl_slno := 0;
3839 v_deductee_slno := 0;
3840
3841 lv_dummy_date := SYSDATE-1000;
3842 v_file_type := 'NS3' ;
3843 v_quartfile_type := 'NS1';
3844 v_upload_type := 'R';
3845 v_uploader_type := 'D';
3846
3847 v_ao_approval := 'N';
3848 v_nil_challan_indicator := 'N' ;
3849 v_dh_mode := 'O' ;
3850 v_statement_id := '0';
3851
3852
3853 -- v_quart_form_number := '26Q';
3854 v_quart_form_number := p_form_number; --Date 11-05-2007 by Sacsethi for bug 5647248
3855
3856 SELECT JAI_AP_ETDS_T_S.nextval INTO v_batch_id FROM DUAL;
3857
3858 IF NVL(p_action,'X') <> 'V' THEN
3859 IF NVL(p_generate_headers,'X') = 'Y' or NVL(p_action,'X') = 'H' THEN
3860 jai_ap_tds_etds_pkg.v_debug_pad_char := ' ';
3861 jai_ap_tds_etds_pkg.v_generate_headers := TRUE;
3862 ELSE
3863 jai_ap_tds_etds_pkg.v_debug_pad_char := '';
3864 jai_ap_tds_etds_pkg.v_generate_headers := FALSE;
3865 END IF;
3866 END IF;
3867
3868
3869 IF NVL(p_period,'XX') = 'XX' THEN
3870 lv_etds_yearly_returns := 'Y' ;
3871 FND_FILE.put_line(FND_FILE.log, '~~~~Ver:619.1~~~~ Start of eTDS File Creation for Yearly Returns Batch_id->'||v_batch_id
3872 ||', Creation Date->'||to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss')||' ~~~~~~~~~~~~~~~~~~');
3873 ELSE
3874 lv_etds_yearly_returns := 'N' ;
3875 FND_FILE.put_line(FND_FILE.log, '~~~~Ver:115.6~~~~ Start of eTDS File Creation for Quarterly returns
3876 Batch_id->'||v_batch_id || 'Period : ' || p_period
3877 ||', Creation Date->'||to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss')||' ~~~~~~~~~~~~~~~~~~');
3878 END IF ;
3879
3880 IF length(p_tan_number) > 10 THEN
3881 FND_FILE.put_line(FND_FILE.log, 'Tan Number length is greater than 10 characters');
3882 RAISE_APPLICATION_ERROR(-20014, 'Tan Number length is greater than 10 characters', true);
3883 END IF;
3884
3885 INSERT INTO JAI_AP_ETDS_REQUESTS(
3886 batch_id, request_id,/* legal_entity_id, operating_unit_id,*/ org_tan_number, financial_year,
3887 tax_authority_id, tax_authority_site_id, organization_id,
3888 deductor_name, deductor_state, addr_changed_since_last_ret, deductor_status,
3889 person_resp_for_deduction, designation_of_pers_resp, challan_start_date,
3890 challan_end_date, file_path, filename,
3891 program_application_id, program_id, program_login_id,
3892 -- added, Harshita for Bug 4866533
3893 created_by, creation_date, last_updated_by, last_update_date
3894 ) VALUES (
3895 v_batch_id, v_conc_request_id,/*p_legal_entity_id, p_profile_org_id,*/ p_tan_number, p_fin_year, -- Harshita for Bug 4889272
3896 p_tax_authority_id, p_tax_authority_site_id, p_organization_id,
3897 p_deductor_name, p_deductor_state, p_addrChangedSinceLastRet, p_deductor_status,
3898 p_persRespForDeduction, p_desgOfPersResponsible, p_challan_Start_Date,
3899 p_challan_End_Date, p_file_path, p_filename,
3900 fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'),
3901 -- added, Harshita for Bug 4866533
3902 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate
3903 );
3904
3905 FOR k IN c_organization_id(p_tan_number) LOOP
3906
3907 -- Fetching the Pan Number based on TAN
3908 OPEN c_pan_number(k.organization_id);
3909 FETCH c_pan_number INTO v_deductor_pan;
3910 CLOSE c_pan_number;
3911
3912 EXIT WHEN v_deductor_pan IS NOT NULL;
3913
3914 END LOOP;
3915
3916 IF v_deductor_pan IS NULL THEN
3917 FND_FILE.put_line(FND_FILE.log, 'Pan Number cannot be retreived based on given TAN Number');
3918 RAISE_APPLICATION_ERROR(-20015, 'Pan Number cannot be retreived based on given TAN Number', true);
3919 END IF;
3920
3921
3922 --v_legal_entity_id := p_legal_entity_id; -- Harshita for Bug 4889272
3923 v_financial_year := p_fin_year;
3924 v_deductor_tan := p_tan_number;
3925 v_tan_state_code := to_number(p_deductor_state);
3926 v_deductor_name := p_deductor_name;
3930 v_personDesgnRespForDedection := p_desgOfPersResponsible;
3927 v_deductor_status := p_deductor_status;
3928 v_addrChangedSinceLastReturn := p_addrChangedSinceLastRet;
3929 v_personNameRespForDedection := p_persRespForDeduction;
3931
3932 -- Fetching Start Date and End date of given Financial Year
3933 OPEN c_fin_year(v_deductor_tan, /* v_legal_entity_id*/ v_financial_year, p_organization_id); -- Harshita for Bug 4889272
3934 FETCH c_fin_year INTO v_start_date, v_end_date;
3935 CLOSE c_fin_year;
3936
3937 IF v_start_date IS NULL OR v_end_date IS NULL THEN
3938 FND_FILE.put_line(FND_FILE.log, 'Cannot get values for Financial Year and Assessment Year');
3939 RAISE_APPLICATION_ERROR( -20016, 'Cannot get values for Financial Year and Assessment Year');
3940 END IF;
3941
3942 -- Fetching Location linked to Input Organization from where address details are captured
3943 OPEN c_location_linked_to_org(p_organization_id);
3944 FETCH c_location_linked_to_org INTO v_location_id;
3945 CLOSE c_location_linked_to_org;
3946
3947 jai_ap_tds_etds_pkg.populate_details(
3948 v_batch_id,
3949 --p_legal_entity_id, -- Harshita for Bug 4889272
3950 p_tan_number,
3951 p_tax_authority_id,
3952 p_tax_authority_site_id,
3953 p_challan_Start_Date,
3954 p_challan_end_Date,
3955 lv_etds_yearly_returns,
3956 p_include_list, --Date 11-05-2007 by Sacsethi for bug 5647248
3957 p_exclude_list
3958 );
3959
3960 -- Shall Populate the Address Details of Batch Header
3961 OPEN c_address_details(v_location_id);
3962 FETCH c_address_details INTO v_location_code, v_tan_address1, v_tan_address2, v_tan_address3, v_tan_address4,
3963 v_tan_address5, v_postal_code;
3964 CLOSE c_address_details;
3965
3966 -- Start, Vijay Shankar for Bug#4057192
3967 -- checks for Pincode related to Address location
3968 IF length(v_postal_code) > 6 THEN
3969 RAISE_APPLICATION_ERROR(-20010, 'Postal Code of Location should not have more than 6 digit numbered value. Location Code (id):'||v_location_code||' ('||v_location_id||')');
3970 END IF;
3971
3972 BEGIN
3973 v_tan_pin := to_number(v_postal_code);
3974 EXCEPTION
3975 WHEN VALUE_ERROR THEN -- SQLCODE = '-6502', PL/SQL: numeric or value error: character to number conversion error
3976 RAISE_APPLICATION_ERROR(-20010, 'Postal Code of Location should be a 6 digit number. Location Code (id):'||v_location_code||' ('||v_location_id||')');
3977 END;
3978 -- End, Vijay Shankar for Bug#4057192
3979
3980 BEGIN
3981 jai_ap_tds_etds_pkg.openFile(p_file_path, p_filename);
3982 EXCEPTION
3983 WHEN OTHERS THEN
3984 FND_FILE.put_line(FND_FILE.log, 'Error Occured during opening of file(1):'||SQLERRM);
3985 RAISE_APPLICATION_ERROR(-20016, 'Error Occured(1):'||SQLERRM, true);
3986 END;
3987
3988 IF p_action <> 'V' THEN
3989 FND_FILE.put_line(FND_FILE.log, 'Start File Header');
3990 END IF ;
3991
3992 -- File Header (42 Chars)
3993 v_line_number := v_line_number + 1;
3994 v_record_type := 'FH';
3995 v_file_sequence_number := 1;
3996
3997 IF lv_etds_yearly_returns = 'Y' THEN
3998 IF p_generate_headers = 'Y' THEN
3999 jai_ap_tds_etds_pkg.create_fh(v_batch_id);
4000 END IF;
4001
4002 jai_ap_tds_etds_pkg.create_file_header(v_line_number, v_record_type, v_file_type, v_upload_type,
4003 v_file_creation_date, v_file_sequence_number, v_deductor_tan, v_number_of_batches);
4004 ELSE
4005 IF p_action = 'H' THEN
4006 jai_ap_tds_etds_pkg.create_quarterly_fh
4007 (v_batch_id,
4008 p_period,
4009 p_RespPers_flat_no, p_RespPers_prem_bldg, -- Bug 6030953
4010 p_RespPers_rd_st_lane, p_RespPers_area_loc, -- Bug 6030953
4011 p_RespPers_tn_cty_dt, -- Bug 6030953
4012 p_RespPersState, p_RespPersPin,
4013 p_RespPers_tel_no, p_RespPers_email, -- Bug 6030953
4014 p_RespPersAddrChange );
4015 END IF;
4016
4017 p_return_code := null ;
4018 p_return_message := null ;
4019
4020 jai_ap_tds_etds_pkg.validate_file_header
4021 ( p_line_number => v_line_number ,
4022 p_record_type => v_record_type ,
4023 p_quartfile_type => v_quartfile_type,
4024 p_upload_type => v_upload_type,
4025 p_file_creation_date => v_file_creation_date,
4026 p_file_sequence_number => v_file_sequence_number,
4027 p_uploader_type => v_uploader_type ,
4028 p_deductor_tan => v_deductor_tan ,
4029 p_number_of_batches => v_number_of_batches,
4030 p_period => p_period,
4031 p_challan_start_date => p_challan_Start_Date,
4032 p_challan_end_date => p_challan_End_Date,
4033 p_fin_year => to_char(v_start_date,'YYYY'),/*(p_fin_year has been replaced with to_char(v_start_date,'YYYY')
4034 by rchandan for bug#4640996*/
4035 p_return_code => p_return_code,
4036 p_return_message => p_return_message
4037 );
4038
4039 IF p_return_code = 'E' THEN
4040 IF lv_action = 'V' THEN
4041 insert into jai_ap_etds_errors_t
4042 (batch_id, record_type, error_message) values
4043 ( ln_batch_id, 'FH', p_return_message ) ;
4044 ELSE
4045 p_ret_code := jai_constants.request_error ;
4046 p_err_buf := p_return_message ;
4047 RETURN ;
4051 lv_generate_headers := null ;
4048 END IF ;
4049 END IF ;
4050
4052 IF p_action <> 'V' THEN
4053 IF p_action = 'F' THEN
4054 lv_generate_headers := 'N' ;
4055 ELSIF p_action = 'H' THEN
4056 lv_generate_headers := 'Y' ;
4057 END IF ;
4058
4059 jai_ap_tds_etds_pkg.create_quarterly_file_header
4060 (
4061 p_line_number => v_line_number,
4062 p_record_type => v_record_type,
4063 p_file_type => v_quartfile_type,
4064 p_upload_type => v_upload_type,
4065 p_file_creation_date => v_file_creation_date,
4066 p_file_sequence_number => v_file_sequence_number,
4067 p_uploader_type => v_uploader_type,
4068 p_deductor_tan => v_deductor_tan,
4069 p_number_of_batches => v_number_of_batches,
4070 p_fh_recordHash => v_fh_recordHash,
4071 p_fh_fvuVersion => v_fh_fvuVersion,
4072 p_fh_fileHash => v_fh_fileHash,
4073 p_fh_samVersion => v_fh_samVersion,
4074 p_fh_samHash => v_fh_samHash,
4075 p_fh_scmVersion => v_fh_scmVersion,
4076 p_fh_scmHash => v_fh_scmHash,
4077 p_generate_headers => lv_generate_headers
4078 ) ;
4079 END IF ;
4080 END IF ;
4081
4082 -- Batch Header (411 Chars)
4083 v_line_number := v_line_number + 1;
4084 v_record_type := 'BH';
4085 v_batch_number := 1;
4086 v_form_number := 26; -- as per Vikrams update
4087 v_financial_year := to_char(v_start_date, 'YYYY')||to_char(v_end_date, 'YY');
4088 v_assessment_year := to_char(add_months(v_start_date,12), 'YYYY')||to_char(add_months(v_end_date,12), 'YY');
4089
4090 /*SELECT count(1), sum(tds_amount)
4091 FROM JAI_AP_ETDS_T
4092 WHERE batch_id = v_batch_id AND consider_for_challan=1;*/
4093
4094 -- bug#3708878. Above select was changed to the select below.
4095 select count(1), sum(tds_amt)
4096 INTO v_challan_cnt, v_totTaxDeductedAsPerChallan
4097 from
4098 (
4099 select tds_section, bank_branch_code, challan_num, challan_date, sum(tds_amount) tds_amt
4100 from JAI_AP_ETDS_T
4101 WHERE batch_id = v_batch_id AND consider_for_challan=1
4102 group by tds_section, bank_branch_code, challan_num, challan_date
4103 );
4104
4105 SELECT count(1), sum(tds_amount) INTO v_deductee_cnt, v_totTaxDeductedAsPerDeductee
4106 FROM JAI_AP_ETDS_T
4107 WHERE batch_id = v_batch_id AND consider_for_deductee=1;
4108
4109 IF p_action <> 'V' THEN
4110 FND_FILE.put_line(FND_FILE.log, 'Batch Header');
4111 END IF ;
4112
4113 v_ack_num_tan_app := NULL;
4114 v_pro_rcpt_num_org_ret := nvl(p_pro_rcpt_num_org_ret,0);
4115
4116 IF lv_etds_yearly_returns = 'Y' THEN
4117
4118 IF p_generate_headers = 'Y' THEN
4119 jai_ap_tds_etds_pkg.create_bh;
4120 END IF;
4121
4122 jai_ap_tds_etds_pkg.create_batch_header(v_line_number, v_record_type, v_batch_number,
4123 v_challan_cnt, v_deductee_cnt, v_form_number, v_rrr_number, v_rrr_date,
4124 v_deductor_tan, v_deductor_pan, v_assessment_year, v_financial_year, v_deductor_name,
4125 v_tan_address1, v_tan_address2, v_tan_address3, v_tan_address4, v_tan_address5,
4126 v_tan_state_code, v_tan_pin, v_addrChangedSinceLastReturn, v_deductor_status,
4127 v_quarterlyOrYearly, v_personNameRespForDedection, v_personDesgnRespForDedection,
4128 v_totTaxDeductedAsPerChallan, v_totTaxDeductedAsPerDeductee
4129 );
4130
4131 ELSE
4132 IF p_action = 'H' THEN
4133 jai_ap_tds_etds_pkg.create_quarterly_bh;
4134 END IF;
4135
4136 p_return_code := null ;
4137 p_return_message := null ;
4138
4139 jai_ap_tds_etds_pkg.validate_batch_header
4140 ( p_line_number => v_line_number ,
4141 p_record_type => v_record_type ,
4142 p_batch_number => v_batch_number ,
4143 p_challan_cnt => v_challan_cnt ,
4144 p_quart_form_number => v_quart_form_number ,
4145 p_deductor_tan => v_deductor_tan ,
4146 p_assessment_year => v_assessment_year,
4147 p_financial_year => v_financial_year ,
4148 p_deductor_name => v_deductor_name ,
4149 p_tan_address1 => v_tan_address1 ,
4150 p_tan_state_code => v_tan_state_code ,
4151 p_tan_pin => v_tan_pin ,
4152 p_deductor_status => v_deductor_status ,
4153 p_addrChangedSinceLastReturn => v_addrChangedSinceLastReturn,
4154 p_personNameRespForDedection => v_personNameRespForDedection,
4155 p_personDesgnRespForDedection => v_personDesgnRespForDedection,
4156 -- bug 6030953. Added by Lakshmi Gopalsami
4157 p_RespPers_flat_no => p_RespPers_flat_no ,
4158 p_RespPers_prem_bldg => p_RespPers_prem_bldg ,
4159 p_RespPers_rd_st_lane => p_RespPers_rd_st_lane ,
4160 p_RespPers_area_loc => p_RespPers_area_loc ,
4161 p_RespPers_tn_cty_dt => p_RespPers_tn_cty_dt ,
4162 -- end for bug 6030953
4163 p_RespPersState => p_RespPersState ,
4167 p_RespPers_email => p_RespPers_email ,
4164 p_RespPersPin => p_RespPersPin ,
4165 -- bug 6030953. Added by Lakshmi Gopalsami
4166 p_RespPers_tel_no => p_RespPers_tel_no ,
4168 -- end for bug 6030953
4169 p_RespPersAddrChange => p_RespPersAddrChange ,
4170 p_totTaxDeductedAsPerDeductee => v_totTaxDeductedAsPerDeductee,
4171 p_ao_approval => v_ao_approval,
4172 p_return_code => p_return_code,
4173 p_return_message => p_return_message
4174 );
4175
4176 IF p_return_code = 'E' THEN
4177 IF lv_action = 'V' THEN
4178 insert into jai_ap_etds_errors_t(batch_id, record_type, error_message) values
4179 ( ln_batch_id, 'BH', p_return_message ) ;
4180 ELSE
4181 p_ret_code := jai_constants.request_error ;
4182 p_err_buf := p_return_message ;
4183
4184 RETURN ;
4185 END IF ;
4186 END IF ;
4187
4188
4189 lv_generate_headers := null ;
4190 IF p_action <> 'V' THEN
4191 IF p_action = 'F' THEN
4192 lv_generate_headers := 'N' ;
4193 ELSIF p_action = 'H' THEN
4194 lv_generate_headers := 'Y' ;
4195 END IF ;
4196
4197 jai_ap_tds_etds_pkg.create_quarterly_batch_header
4198 (
4199 p_line_number => v_line_number,
4200 p_record_type => v_record_type,
4201 p_batch_number => v_batch_number,
4202 p_challan_count => v_challan_cnt,
4203 p_form_number => v_quart_form_number,
4204 p_trn_type => v_bh_trnType,
4205 p_batchUpd => v_bh_batchUpd,
4206 p_org_RRRno => v_bh_org_RRRno,
4207 p_prev_RRRno => v_bh_prev_RRRno,
4208 p_RRRno => v_bh_RRRno,
4209 p_RRRdate => v_bh_RRRdate,
4210 p_deductor_last_tan => v_bh_deductor_last_tan,
4211 p_deductor_tan => v_deductor_tan,
4212 p_filler1 => v_filler1,
4213 p_deductor_pan => v_deductor_pan,
4214 p_assessment_year => v_assessment_year,
4215 p_financial_year => v_financial_year,
4216 p_period => p_period,
4217 p_deductor_name => v_deductor_name,
4218 p_deductor_branch => v_deductor_branch,
4219 p_tan_address1 => v_tan_address1,
4220 p_tan_address2 => v_tan_address2,
4221 p_tan_address3 => v_tan_address3,
4222 p_tan_address4 => v_tan_address4,
4223 p_tan_address5 => v_tan_address5,
4224 p_tan_state_code => v_tan_state_code,
4225 p_tan_pin => v_tan_pin,
4226 p_deductor_email => v_deductor_email,
4227 p_deductor_stdCode => v_deductor_stdCode,
4228 p_deductor_phoneNo => v_deductor_phoneNo,
4229 p_addrChangedSinceLastReturn => v_addrChangedSinceLastReturn,
4230 p_status_of_deductor => v_deductor_status,
4231 p_pers_resp_for_deduction => v_personNameRespForDedection,
4232 p_PespPerson_designation => v_personDesgnRespForDedection,
4233 p_RespPerson_address1 => p_RespPers_flat_no, -- bug 6030953
4234 p_RespPerson_address2 => p_RespPers_prem_bldg, -- bug 6030953
4235 p_RespPerson_address3 => p_RespPers_rd_st_lane, -- bug 6030953
4236 p_RespPerson_address4 => p_RespPers_area_loc, -- bug 6030953
4237 p_RespPerson_address5 => p_RespPers_tn_cty_dt, -- bug 6030953
4238 p_RespPerson_state => p_RespPersState,
4239 p_RespPerson_pin => p_RespPersPin,
4240 p_RespPerson_email => p_RespPers_email, -- bug 6030953
4241 p_RespPerson_remark => v_RespPerson_remark,
4242 p_RespPerson_stdCode => v_RespPerson_stdCode,
4243 p_RespPerson_phoneNo => p_RespPers_tel_no, -- bug 6030953
4244 p_RespPerson_addressChange => p_RespPersAddrChange,
4245 p_totTaxDeductedAsPerChallan => round(v_totTaxDeductedAsPerDeductee), -- decimal should be .00
4246 p_tds_circle => v_bh_tds_circle,
4247 p_salaryRecords_count => v_bh_salaryRecords_count,
4248 p_gross_total => v_bh_gross_total,
4249 p_ao_approval => v_ao_approval ,
4250 p_ao_approval_number => v_ao_approval_number,
4251 p_recHash => v_bh_recHash,
4252 p_generate_headers => lv_generate_headers
4253 ) ;
4254 END IF ;
4255 END IF ;
4256
4257
4258 IF p_action <> 'V' THEN
4259 FND_FILE.put_line(FND_FILE.log, 'Challan Detail');
4260 END IF ;
4261
4262 -- Challan Details (60 Chars)
4263 v_record_type := 'CD';
4264 IF lv_etds_yearly_returns = 'Y' THEN
4265 IF p_generate_headers = 'Y' THEN
4266 jai_ap_tds_etds_pkg.create_cd;
4267 END IF;
4268 END IF;
4269
4270 v_challan_dtl_slno := 0;
4271 FOR cd IN c_challan_records(v_batch_id)
4272 LOOP
4273 v_line_number := v_line_number + 1;
4274 v_challan_dtl_slno := v_challan_dtl_slno + 1;
4275 ln_amt_of_oth := 0;
4276 v_record_type := 'CD';
4277
4278 IF cd.challan_date = lv_dummy_date THEN
4279 cd.challan_date := to_date(null) ;
4280 END IF ;
4281
4282 IF lv_etds_yearly_returns = 'Y' THEN -- Harshita for Bug 4525089
4283 jai_ap_tds_etds_pkg.create_challan_detail(v_line_number, v_record_type, v_batch_number, v_challan_dtl_slno,
4284 cd.tds_section, cd.tds_amount, cd.challan_num, cd.challan_date, cd.bank_branch_code
4285 );
4286 ELSE
4287 IF cd.challan_num = 'No Challan Number' THEN
4288 cd.challan_num := null ;
4289 END IF ;
4290
4291 FND_FILE.put_line(FND_FILE.log, 'cd.tds_section: '||cd.tds_section||' cd.challan_num: '||cd.challan_num|| ' cd.challan_date :' || cd.challan_date || 'cd.bank_branch_code' || cd.bank_branch_code || 'cd.check_number' || cd.check_number);
4292
4293
4294 --MODIFIED BY CSAHOO FOR BUG#6158875
4295 OPEN c_deductee_cnt(v_batch_id, cd.check_number, cd.tds_section,
4296 cd.challan_num, cd.challan_date,cd.bank_branch_code);
4297 FETCH c_deductee_cnt INTO v_q_deductee_cnt ;
4298 CLOSE c_deductee_cnt ;
4299
4300 FND_FILE.put_line(FND_FILE.log, 'v_batch_id: '||v_batch_id||' v_q_deductee_cnt: '||v_q_deductee_cnt);
4301
4302 IF p_action = 'H' THEN
4303 jai_ap_tds_etds_pkg.create_quarterly_cd;
4304 END IF ;
4305
4306 v_total_deposit := cd.amt_of_tds + cd.amt_of_surcharge + cd.amt_of_cess;
4307
4308 IF cd.challan_num IS NULL THEN
4309 v_bank_branch_code := null ;
4310 ELSE
4311 v_bank_branch_code := substr(cd.bank_branch_code,1,7);
4312 END IF ;
4313
4314 p_return_code := null ;
4315 p_return_message := null ;
4316
4317 jai_ap_tds_etds_pkg.validate_challan_detail
4318 (
4319 p_line_number => v_line_number ,
4320 p_record_type => v_record_type ,
4321 p_batch_number => v_batch_number ,
4322 p_challan_dtl_slno => v_challan_dtl_slno ,
4323 p_deductee_cnt => v_q_deductee_cnt ,
4324 p_nil_challan_indicat => v_nil_challan_indicator,
4325 p_tds_section => cd.tds_section ,
4326 p_amt_of_tds => cd.amt_of_tds ,
4327 p_amt_of_surcharge => cd.amt_of_surcharge ,
4328 p_amt_of_cess => cd.amt_of_cess ,
4329 p_amt_of_oth => ln_amt_of_oth ,
4330 p_tds_amount => cd.tds_amount ,
4331 p_total_income_tds => v_total_deposit ,
4332 p_challan_num => cd.challan_num,
4333 p_bank_branch_code => cd.bank_branch_code,
4334 p_challan_no => cd.challan_num,
4335 p_challan_Date => cd.challan_date,
4336 p_check_number => cd.check_number,
4337 p_return_code => p_return_code,
4338 p_return_message => p_return_message
4339 );
4340 IF p_return_code = 'E' THEN
4341 IF lv_action = 'V' THEN
4342 insert into jai_ap_etds_errors_t
4343 (batch_id, record_type, reference_id, error_message) values
4344 ( ln_batch_id, 'CD', v_line_number, p_return_message ) ;
4345 ELSE
4346 p_ret_code := jai_constants.request_error ;
4347 p_err_buf := p_return_message ;
4348
4349 RETURN ;
4350 END IF ;
4351 END IF ;
4352
4353 FND_FILE.put_line(FND_FILE.log, 'after validate_challan_detail');
4354
4355 lv_generate_headers := null ;
4356 IF p_action <> 'V' THEN
4357 IF p_action = 'F' THEN
4358 lv_generate_headers := 'N' ;
4359 ELSIF p_action = 'H' THEN
4360 lv_generate_headers := 'Y' ;
4361 END IF ;
4362
4363 ln_amt_of_tds := cd.tds_amount - round(cd.amt_of_surcharge) - round(cd.amt_of_cess) - round(ln_amt_of_oth) - round(ln_amt_of_oth) ;
4364
4365 jai_ap_tds_etds_pkg.create_quart_challan_dtl
4366 (
4367 p_line_number => v_line_number,
4368 p_record_type => v_record_type,
4369 p_batch_number => v_batch_number,
4370 p_challan_dtl_slno => v_challan_dtl_slno,
4371 p_deductee_cnt => v_q_deductee_cnt,
4372 p_nil_challan_indicator => v_nil_challan_indicator,
4373 p_ch_updIndicator => v_ch_updIndicator,
4374 p_filler2 => v_filler2,
4375 p_filler3 => v_filler3,
4376 p_filler4 => v_filler4,
4377 p_last_bank_challan_no => v_last_bank_challan_no,
4378 p_bank_challan_no => cd.challan_num,
4379 p_last_transfer_voucher_no => v_last_transfer_voucher_no,
4380 p_transfer_voucher_no => v_transfer_voucher_no,
4381 p_last_bank_branch_code => v_last_bank_branch_code,
4382 p_bank_branch_code => v_bank_branch_code ,
4383 p_challan_lastDate => v_challan_lastDate,
4384 p_challan_Date => cd.challan_date,
4385 p_filler5 => v_filler5,
4386 p_filler6 => v_filler6,
4387 p_tds_section => cd.tds_section,
4388 p_amt_of_tds => ln_amt_of_tds,
4389 p_amt_of_surcharge => round(cd.amt_of_surcharge),
4390 p_amt_of_cess => round(cd.amt_of_cess),
4391 p_amt_of_int => round(ln_amt_of_oth),
4392 p_amt_of_oth => round(ln_amt_of_oth),
4393 p_tds_amount => cd.tds_amount,
4394 p_last_total_depositAmt => v_last_total_depositAmt,
4395 p_total_deposit => v_total_deposit,
4396 p_tds_income_tax => cd.amt_of_tds,
4397 p_tds_surcharge => cd.amt_of_surcharge,
4398 p_tds_cess => cd.amt_of_cess,
4399 p_total_income_tds => v_total_deposit,
4400 p_tds_interest_amt => 0,
4401 p_tds_other_amt => 0,
4402 p_check_number => cd.check_number,
4403 p_book_entry => v_book_entry,
4404 p_remarks => v_remarks,
4405 p_ch_recHash => v_ch_recHash,
4406 p_generate_headers => lv_generate_headers,
4407 /* Bug 6796765. Added by Lakshmi Gopalsami
4408 * Added p_form_name as this is required to print the
4409 * section code depending on the section
4410 */
4411 p_form_name => v_quart_form_number
4412 ) ;
4413 END IF ;
4414 END IF ;
4415
4416
4417
4418 -- UPDATE JAI_AP_ETDS_T SET challan_line_num = v_line_number WHERE CURRENT OF c_challan_records;
4419 -- bug#3708878. update needed to be changed as for update of cannot be used with group by
4420 UPDATE JAI_AP_ETDS_T
4421 SET challan_line_num = v_line_number
4422 WHERE batch_id = v_batch_id
4423 and ( ( tds_section IS NULL AND cd.tds_section IS NULL ) OR ( tds_section = cd.tds_section ) )--nvl(tds_section, 'No Section') = nvl(cd.tds_section, 'No Section')
4424 and ( (challan_num IS NULL AND cd.challan_num IS NULL ) OR ( challan_num = cd.challan_num ) ) --nvl(challan_num, 'No Challan Number') = nvl(cd.challan_num, 'No Challan Number')
4425 and nvl(challan_date, trunc(sysdate) ) = nvl(cd.challan_date, trunc(sysdate) )
4426 -- csahoo for bug 6158875, replaced the AND by OR below
4427 and (( bank_branch_code IS NULL AND cd.bank_branch_code IS NULL ) OR ( bank_branch_code = cd.bank_branch_code )) --nvl(bank_branch_code, 'No Bank Branch') = nvl(cd.bank_branch_code, 'No Bank Branch')
4428 and consider_for_challan=1;
4429
4430 IF p_action <> 'V' THEN
4431 FND_FILE.put_line(FND_FILE.log, 'Challan Line:'||v_line_number
4432 ||', tdsSec:' || cd.tds_section || ', ChlNum:' || cd.challan_num
4433 ||', ChlDate:'||cd.challan_date||', bankBr:'||cd.bank_branch_code
4434 );
4435 END IF ;
4436
4437 IF lv_etds_yearly_returns = 'N' THEN -- Harshita for Bug 4525089
4438
4439 v_record_type := 'DD';
4440 v_challan_line_num := v_line_number ;
4441
4442 IF p_action = 'H' THEN
4443 jai_ap_tds_etds_pkg.create_quarterly_dd;
4444 END IF;
4445
4446 v_deductee_slno := 0 ;
4447 process_deductee_records ; -- internal procedure call
4448 v_challan_line_num := null ;
4449
4450 END IF ;
4451
4452 END LOOP;
4453
4454 IF lv_etds_yearly_returns= 'Y' THEN
4455 v_record_type := 'DD';
4456 IF p_action <> 'V' THEN
4457 FND_FILE.put_line(FND_FILE.log, 'Deductee Detail');
4458 -- Dedectee Details (338 Chars)
4459 v_record_type := 'DD';
4460 v_deductee_slno := 0;
4461 END IF;
4462
4463 IF p_generate_headers = 'Y' THEN
4464 jai_ap_tds_etds_pkg.create_dd;
4465 END IF;
4466
4467 v_challan_line_num := null ;
4468 process_deductee_records ;
4469 END IF ;
4470
4471 IF p_action = 'V' THEN
4472
4473 FND_FILE.put_line(FND_FILE.log,' LISTING THE ERRORS IN THIS BATCH ' );
4474 FND_FILE.put_line(FND_FILE.log,'-------------------------------------------------------------------- ' );
4475
4476 ln_errors_exist := 0;
4477
4478 FOR rec_get_errors IN c_get_errors(ln_batch_id)
4479 LOOP
4480 ln_errors_exist := 1 ;
4481 FND_FILE.put_line(FND_FILE.log, rec_get_errors.Error_Message );
4482 END LOOP ;
4483
4484 IF ln_errors_exist = 0 THEN
4485 FND_FILE.put_line(FND_FILE.log,' File Validation Successful. No Errors Found !! ' );
4486 END IF ;
4487
4488 FND_FILE.put_line(FND_FILE.log,'-------------------------------------------------------------------- ' );
4489 FND_FILE.put_line(FND_FILE.log,' END OF ERRORS IN THIS BATCH ' );
4490
4491 END IF ;
4492
4493 jai_ap_tds_etds_pkg.closeFile;
4494
4495 IF p_action <> 'V' THEN
4496 FND_FILE.put_line(FND_FILE.log, '~~~~~~~~~~~~~~~ End of eTDS File Creation ~~~~~~~~~~~~~~~~~~');
4497 END IF ;
4498
4499 /* Added by Ramananda for bug#4407165 */
4500 EXCEPTION
4501 WHEN OTHERS THEN
4502 p_err_buf := null;
4503 p_ret_code := null;
4504 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
4505 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
4506 app_exception.raise_exception;
4507
4508 END generate_etds_returns;
4509
4510 -- ended, Harshita for 5096787
4511
4512
4513 END jai_ap_tds_etds_pkg;