[Home] [Help]
PACKAGE: APPS.JAI_AP_TDS_ETDS_PKG
Source
1 PACKAGE jai_ap_tds_etds_pkg AS
2 /* $Header: jai_ap_tds_etds.pls 120.16.12020000.3 2013/04/08 07:15:55 cholei ship $ */
3
4 v_filehandle UTL_FILE.FILE_TYPE;
5 v_utl_file_dir VARCHAR2(512);
6 v_utl_file_name VARCHAR2(50);
7 v_generate_headers BOOLEAN := FALSE;
8
9 -- Padding Variables
10 v_pad_char CONSTANT VARCHAR2(1) := ' ';
11 v_pad_date CONSTANT VARCHAR2(1) := ' ';
12 v_pad_number CONSTANT VARCHAR2(1) := '0';
13
14 v_underline_char CONSTANT VARCHAR2(1) := '-';
15 v_debug_pad_char VARCHAR2(1);
16
17 s_date NUMBER(1) := 8;
18
19 -- Size and Format Related Variables (Updated on 25th Oct 2006)
20
21 v_delimeter VARCHAR2(1) := '^' ;
22 v_quart_len NUMBER := 15 ;
23 v_chr13 VARCHAR2(7) := 'CHR(13)';
24 v_chr10 VARCHAR2(7) := 'CHR(10)';
25 sq_len_1 NUMBER := 1 ;
26 sq_len_2 NUMBER :=2 ;
27 sq_len_3 NUMBER :=3 ;
28 sq_len_4 NUMBER :=4 ;
29 sq_len_5 NUMBER :=5 ;
30 sq_len_6 NUMBER :=6 ;
31 sq_len_7 NUMBER :=7 ;
32 sq_len_8 NUMBER :=8 ;
33 sq_len_9 NUMBER :=9 ;
34 sq_len_10 NUMBER :=10 ;
35 sq_len_12 NUMBER :=12 ;
36 sq_len_14 NUMBER :=14 ;
37 sq_len_15 NUMBER :=15 ;
38 sq_len_20 NUMBER :=20 ;
39 sq_len_25 NUMBER :=25 ;
40 sq_len_75 NUMBER :=75 ;
41 sq_len_150 NUMBER :=150 ;
42
43 v_quart_pad VARCHAR2(1) := ' ';
44 v_q_noval_filler VARCHAR2(1) := '-';
45 v_q_null_filler VARCHAR2(1) := '*';
46 v_quart_numfill NUMBER := 0 ;
47 v_format_amount VARCHAR2(17) := 'FM999999999990D00' ;
48 v_format_rate VARCHAR2(9) := 'FM90D0000';
49 ln_batch_id NUMBER ;
50 lv_action VARCHAR2(1) ;
51 G_DATE_DUMMY CONSTANT VARCHAR2(1) := '-';
52
53 -- File Header Size Variables
54 s_line_number CONSTANT NUMBER(2) := 6;
55 s_record_type CONSTANT NUMBER(2) := 2;
56 s_file_type CONSTANT NUMBER(2) := 3;
57 s_upload_type CONSTANT NUMBER(2) := 1;
58 s_file_sequence_number CONSTANT NUMBER(2) := 8;
59 s_deductor_tan CONSTANT NUMBER(2) := 10;
60 s_number_of_batches CONSTANT NUMBER(2) := 4;
61 s_return_prep CONSTANT NUMBER(2) := 75; /*Bug 8880543 - Added for Return Preperation Utility*/
62
63 -- Batch Header Size Variables
64 s_batch_number CONSTANT NUMBER(2) := 4;
65 s_challan_count CONSTANT NUMBER(2) := 5;
66 s_deductee_count CONSTANT NUMBER(2) := 5;
67 s_form_number CONSTANT NUMBER(2) := 4;
68 s_rrr_number CONSTANT NUMBER(2) := 10;
69 s_rrr_date CONSTANT NUMBER(2) := 8;
70 s_pan_of_tan CONSTANT NUMBER(2) := 10;
71 s_assessment_year CONSTANT NUMBER(2) := 6;
72 s_financial_year CONSTANT NUMBER(2) := 6;
73 s_deductor_name CONSTANT NUMBER(2) := 75;
74 s_tan_address1 CONSTANT NUMBER(2) := 25;
75 s_tan_address2 CONSTANT NUMBER(2) := 25;
76 s_tan_address3 CONSTANT NUMBER(2) := 25;
77 s_tan_address4 CONSTANT NUMBER(2) := 25;
78 s_tan_address5 CONSTANT NUMBER(2) := 25;
79 s_tan_state CONSTANT NUMBER(2) := 2;
80 s_tan_pin CONSTANT NUMBER(2) := 6;
81 s_chng_addr_since_last_return CONSTANT NUMBER(2) := 1;
82 s_status_of_deductor CONSTANT NUMBER(2) := 1;
83 s_quart_year_return CONSTANT NUMBER(2) := 2;
84 s_pers_resp_for_deduction CONSTANT NUMBER(2) := 75;
85 s_pers_designation CONSTANT NUMBER(2) := 20;
86 s_tot_tax_dedected_challan CONSTANT NUMBER(2) := 14;
87 s_tot_tax_dedected_deductee CONSTANT NUMBER(2) := 14;
88
89 -- Challan Detail Size Variables
90 s_challan_slno CONSTANT NUMBER(2) := 5;
91 s_challan_section CONSTANT NUMBER(2) := 5;
92 s_amount_deducted CONSTANT NUMBER(2) := 14;
93 s_challan_num CONSTANT NUMBER(2) := 9;
94 s_bank_branch_code CONSTANT NUMBER(2) := 7;
95
96 -- Deductee Detail Size Variables
97 s_deductee_slno CONSTANT NUMBER(2) := 5;
98 s_deductee_section CONSTANT NUMBER(2) := 5;
99 s_deductee_code CONSTANT NUMBER(2) := 2;
100 s_deductee_pan CONSTANT NUMBER(2) := 10;
101 s_deductee_name CONSTANT NUMBER(2) := 75;
102 s_deductee_address1 CONSTANT NUMBER(2) := 25;
103 s_deductee_address2 CONSTANT NUMBER(2) := 25;
104 s_deductee_address3 CONSTANT NUMBER(2) := 25;
105 s_deductee_address4 CONSTANT NUMBER(2) := 25;
106 s_deductee_address5 CONSTANT NUMBER(2) := 25;
107 s_deductee_state CONSTANT NUMBER(2) := 2;
108 s_deductee_pin CONSTANT NUMBER(2) := 6;
109 s_payment_amount CONSTANT NUMBER(2) := 14;
110 s_tax_rate CONSTANT NUMBER(2) := 4;
111 s_grossing_up_factor CONSTANT NUMBER(2) := 1;
112 s_tax_deducted CONSTANT NUMBER(2) := 14;
113 s_challan_no CONSTANT NUMBER(2) := 9;
114 s_reason_for_nDeduction CONSTANT NUMBER(2) := 1;
115 s_filler CONSTANT NUMBER(2) := 14;
116 s_book_ent_oth NUMBER(2) := 1; -- updated on 26th
117 s_filler6 NUMBER(2) := 1; -- updated on 26th
118
119 FUNCTION formatAmount( p_amount IN NUMBER) RETURN VARCHAR2;
120
121 FUNCTION getSectionCode( p_section IN VARCHAR2, p_string IN VARCHAR2 DEFAULT NULL ) RETURN VARCHAR2;
122
123 PROCEDURE openFile(p_directory IN VARCHAR2, p_filename IN VARCHAR2);
124 PROCEDURE closeFile;
125
126 -- added, Harshita for Bug 5096787
127 PROCEDURE populate_details(
128 p_batch_id IN NUMBER,
129 p_org_tan_num IN VARCHAR2,
130 p_tds_vendor_id IN NUMBER,
131 p_tds_vendor_site_id IN NUMBER,
132 p_tds_inv_from_date IN DATE,
133 p_tds_inv_to_date IN DATE,
134 --p_etds_yearly_returns VARCHAR2 , -- updated on 25th october --Comment out Yearly return by Chong for eTDS ER bug#16414088
135 p_include_list IN VARCHAR2, --Date 11-05-2007 by Sacsethi for bug 5647248
136 p_exclude_list IN VARCHAR2,
137 p_organization_id IN NUMBER /* Bug#10315928 */
138 );
139
140 -- ended, Harshita for Bug 5096787
141
142 PROCEDURE create_file_header(
143 p_line_number IN NUMBER,
144 p_record_type IN VARCHAR2,
145 p_file_type IN VARCHAR2,
146 p_upload_type IN VARCHAR2,
147 p_file_creation_date IN DATE,
148 p_file_sequence_number IN NUMBER,
149 p_deductor_tan IN VARCHAR2,
150 p_number_of_batches IN NUMBER
151 );
152
153 PROCEDURE create_batch_header(
154 p_line_number IN NUMBER,
155 p_record_type IN VARCHAR2,
156 p_batch_number IN NUMBER,
157 p_challan_count IN NUMBER,
158 p_deductee_count IN NUMBER,
159 p_form_number IN CHAR,
160 p_rrr_number IN NUMBER,
161 p_rrr_date IN DATE,
162 p_deductor_tan IN VARCHAR2,
163 p_pan_of_tan IN VARCHAR2,
164 p_assessment_year IN NUMBER,
165 p_financial_year IN NUMBER,
166 p_deductor_name IN VARCHAR2,
167 p_tan_address1 IN VARCHAR2,
168 p_tan_address2 IN VARCHAR2,
169 p_tan_address3 IN VARCHAR2,
170 p_tan_address4 IN VARCHAR2,
171 p_tan_address5 IN VARCHAR2,
172 p_tan_state IN NUMBER,
173 p_tan_pin IN NUMBER,
174 p_chng_addr_since_last_return IN VARCHAR2,
175 p_type_of_deductor IN VARCHAR2,
176 p_quart_year_return IN VARCHAR,
177 p_pers_resp_for_deduction IN VARCHAR2,
178 p_pers_designation IN VARCHAR2,
179 p_tot_tax_dedected_challan IN NUMBER,
180 p_tot_tax_dedected_deductee IN NUMBER,
181 -- added. Harshita for Bug 5096787
182 p_filler1 IN DATE DEFAULT NULL,
183 p_filler2 IN NUMBER DEFAULT NULL,
184 p_filler3 IN VARCHAR2 DEFAULT NULL,
185 p_ack_num_tan_app IN NUMBER DEFAULT NULL,
186 p_pro_rcpt_num_org_ret IN NUMBER DEFAULT NULL
187 -- ended. Harshita for Bug 5096787
188 );
189
190 PROCEDURE create_challan_detail(
191 p_line_number IN NUMBER, -- 6
192 p_record_type IN VARCHAR2, -- 2
193 p_batch_number IN NUMBER, -- 4
194 p_challan_slno IN NUMBER, -- 5
195 p_challan_section IN VARCHAR2, -- 5
196 p_amount_deducted IN NUMBER, -- 14
197 p_challan_num IN VARCHAR2, -- 9
198 p_challan_date IN DATE, -- 8
199 p_bank_branch_code IN VARCHAR2, -- 7
200 -- added. Harshita for Bug 5096787
201 p_amount_of_tds IN NUMBER DEFAULT NULL,
202 p_amount_of_surcharge IN NUMBER DEFAULT NULL,
203 p_amount_of_cess IN NUMBER DEFAULT NULL,
204 p_amount_of_int IN NUMBER DEFAULT NULL,
205 p_amount_of_oth IN NUMBER DEFAULT NULL,
206 p_check_number IN NUMBER DEFAULT NULL,
207 p_tds_dep_by_book IN VARCHAR2 DEFAULT NULL,
208 p_filler4 IN VARCHAR2 DEFAULT NULL
209 -- added. Harshita for Bug 5096787
210 );
211
212 PROCEDURE create_deductee_detail(
213 p_line_number IN NUMBER, -- 9
214 p_record_type IN VARCHAR2, -- 2
215 p_batch_number IN NUMBER, -- 9
216 p_deductee_slno IN NUMBER, -- 5
217 p_deductee_section IN VARCHAR2, -- 5
218 p_deductee_code IN VARCHAR2, -- 2 01 for Companies and 02 for other than companies
219 p_deductee_pan IN VARCHAR2, -- 10
220 p_deductee_name IN VARCHAR2, -- 75
221 p_deductee_address1 IN VARCHAR2, -- 25
222 p_deductee_address2 IN VARCHAR2, -- 25
223 p_deductee_address3 IN VARCHAR2, -- 25
224 p_deductee_address4 IN VARCHAR2, -- 25
225 p_deductee_address5 IN VARCHAR2, -- 25
226 p_deductee_state IN VARCHAR2, -- 2
227 p_deductee_pin IN VARCHAR2, -- 6 /*Changed to VARCHAR2 - Bug7494473*/
228 p_filler5 IN NUMBER, -- 14 Added for bug#4353842
229 p_payment_amount IN NUMBER, -- 14 (12+2), DECIMAL
230 p_payment_date IN DATE, -- 8
231 p_book_ent_oth IN VARCHAR2, -- 1 Added for bug#4353842
232 p_tax_rate IN NUMBER, -- 4(2+2), DECIMAL
233 p_filler6 IN VARCHAR2, -- 1 Added for bug#4353842
234 --p_grossing_up_factor IN VARCHAR2, -- 1 -- Obsoleted via bug # 4353842
235 p_tax_deducted IN NUMBER, -- 14(12+2), DECIMAL
236 p_tax_deducted_date IN DATE, -- 8
237 p_tax_payment_date IN DATE, -- 8
238 p_bank_branch_code IN VARCHAR2, -- 7
239 p_challan_no IN VARCHAR2, -- 9
240 p_tds_certificate_date IN DATE, -- 8
241 p_reason_for_nDeduction IN VARCHAR2, -- 1
242 p_filler7 IN NUMBER -- 14, DECIMAL
243 );
244
245
246 PROCEDURE create_fh(p_batch_id IN NUMBER);
247 PROCEDURE create_bh;
248 PROCEDURE create_cd;
249 PROCEDURE create_dd;
250
251
252
253 -- added, Harshita for Bug 5096787
254
255 -- eTDS Quarterly Data Generation Procedues
256
257 PROCEDURE create_quarterly_file_header(
258 p_line_number IN NUMBER,
259 p_record_type IN VARCHAR2,
260 p_file_type IN VARCHAR2,
261 p_upload_type IN VARCHAR2,
262 p_file_creation_date IN DATE,
263 p_file_sequence_number IN NUMBER,
264 p_uploader_type IN VARCHAR2,
265 p_deductor_tan IN VARCHAR2,
266 p_number_of_batches IN NUMBER,
267 p_return_prep_util IN VARCHAR2, /*Bug 8880543 - Added Return Preperation Utility*/
268 p_fh_recordHash IN VARCHAR2,
269 p_fh_fvuVersion IN VARCHAR2,
270 p_fh_fileHash IN VARCHAR2,
271 p_fh_samVersion IN VARCHAR2,
272 p_fh_samHash IN VARCHAR2,
273 p_fh_scmVersion IN VARCHAR2,
274 p_fh_scmHash IN VARCHAR2,
275 p_fh_consFileHash IN VARCHAR2, /* ETDS FVU3.3 -Bug 13702172 */
276 p_generate_headers IN VARCHAR2
277 );
278
279
280 PROCEDURE create_quarterly_batch_header(
281 p_line_number IN NUMBER,
282 p_record_type IN VARCHAR2,
283 p_batch_number IN NUMBER,
284 p_challan_count IN NUMBER,
285 p_form_number IN CHAR,
286 p_trn_type IN VARCHAR2,
287 p_batchUpd IN VARCHAR2,
288 /* Bug#10315928 - Start */
289 p_bh_orig_token IN VARCHAR2,
290 p_bh_prev_token IN VARCHAR2,
291 p_bh_token_number IN VARCHAR2,
292 p_bh_token_date IN VARCHAR2,
293 /* Bug#10315928 - End */
294 p_deductor_last_tan IN VARCHAR2,
295 p_deductor_tan IN VARCHAR2,
299 p_financial_year IN NUMBER,
296 p_bh_receipt_no IN VARCHAR2, /* Bug#10315928 */
297 p_deductor_pan IN VARCHAR2,
298 p_assessment_year IN NUMBER,
300 p_period IN VARCHAR2,
301 p_deductor_name IN VARCHAR2,
302 p_deductor_branch IN VARCHAR2,
303 p_tan_address1 IN VARCHAR2,
304 p_tan_address2 IN VARCHAR2,
305 p_tan_address3 IN VARCHAR2,
306 p_tan_address4 IN VARCHAR2,
307 p_tan_address5 IN VARCHAR2,
308 p_tan_state_code IN NUMBER,
309 p_tan_pin IN NUMBER,
310 p_deductor_email IN VARCHAR2,
311 p_deductor_stdCode IN NUMBER,
312 p_deductor_phoneNo IN NUMBER,
313 p_addrChangedSinceLastReturn IN VARCHAR2,
314 p_type_of_deductor IN VARCHAR2, /*Bug 8880543 - Modified Deductor Status to Deductor Type*/
315 p_pers_resp_for_deduction IN VARCHAR2,
316 p_PespPerson_designation IN VARCHAR2,
317 p_RespPerson_address1 IN VARCHAR2,
318 p_RespPerson_address2 IN VARCHAR2,
319 p_RespPerson_address3 IN VARCHAR2,
320 p_RespPerson_address4 IN VARCHAR2,
321 p_RespPerson_address5 IN VARCHAR2,
322 p_RespPerson_state IN VARCHAR2,
323 p_RespPerson_pin IN NUMBER,
324 p_RespPerson_email IN VARCHAR2,
325 p_RespPerson_mobile IN VARCHAR2, /* Bug#10315928 */
326 p_RespPerson_stdCode IN NUMBER,
327 p_RespPerson_phoneNo IN NUMBER,
328 p_RespPerson_addressChange IN VARCHAR2,
329 p_totTaxDeductedAsPerChallan IN NUMBER,
330 p_bh_unmatch_challan_cnt IN VARCHAR2, /* Bug#10315928 */
331 p_salaryRecords_count IN VARCHAR2,
332 p_gross_total IN VARCHAR2,
333 p_ao_approval IN VARCHAR2,
334 p_ao_approval_number IN VARCHAR2,
335 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - Start*/
336 p_last_deductor_type IN VARCHAR2,
337 p_state_name IN VARCHAR2,
338 p_pao_code IN VARCHAR2,
339 p_ddo_code IN VARCHAR2,
340 p_ministry_name IN VARCHAR2,
341 p_ministry_name_other IN VARCHAR2,
342 p_filler2 IN VARCHAR2,
343 p_pao_registration_no IN NUMBER,
344 p_ddo_registration_no IN VARCHAR2,
345 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - End*/
346 p_recHash IN VARCHAR2,
347 p_generate_headers IN VARCHAR2
348 );
349
350
351 PROCEDURE create_quart_challan_dtl(
352 p_line_number IN NUMBER ,
353 p_record_type IN VARCHAR2 ,
354 p_batch_number IN NUMBER ,
355 p_challan_dtl_slno IN NUMBER ,
356 p_deductee_cnt IN NUMBER ,
357 p_nil_challan_indicator IN VARCHAR2 ,
358 p_ch_updIndicator IN VARCHAR2 ,
359 p_filler2 IN VARCHAR2 ,
360 p_filler3 IN VARCHAR2 ,
361 p_filler4 IN VARCHAR2 ,
362 p_last_bank_challan_no IN VARCHAR2 ,
363 p_bank_challan_no IN VARCHAR2 ,
364 p_last_transfer_voucher_no IN VARCHAR2 ,
365 p_transfer_voucher_no IN NUMBER ,
366 p_last_bank_branch_code IN VARCHAR2 ,
367 p_bank_branch_code IN VARCHAR2 ,
368 p_challan_lastDate IN VARCHAR2 ,
369 p_challan_Date IN DATE ,
370 p_filler5 IN VARCHAR2 ,
371 p_filler6 IN VARCHAR2 ,
372 p_tds_section IN VARCHAR2 ,
373 p_amt_of_tds IN NUMBER ,
374 p_amt_of_surcharge IN NUMBER ,
375 p_amt_of_cess IN NUMBER ,
376 p_amt_of_int IN NUMBER ,
377 p_amt_of_oth IN NUMBER ,
378 p_tds_amount IN NUMBER ,
379 p_last_total_depositAmt IN NUMBER ,
380 p_total_deposit IN NUMBER ,
381 p_tds_income_tax IN NUMBER ,
382 p_tds_surcharge IN NUMBER ,
383 p_tds_cess IN NUMBER ,
384 p_total_income_tds IN NUMBER ,
385 p_tds_interest_amt IN NUMBER ,
386 p_tds_other_amt IN NUMBER ,
387 p_check_number IN NUMBER ,
388 p_book_entry IN VARCHAR2 ,
389 p_remarks IN VARCHAR2 ,
390 p_ch_recHash IN VARCHAR2,
391 p_generate_headers IN VARCHAR2,
392 /* Bug 6796765. Added by Lakshmi Gopalsami
393 * Added p_form_name as this is required to print the
394 * section code depending on the section
395 */
396 p_form_name IN VARCHAR2
397 ) ;
398
399
400 PROCEDURE create_quart_deductee_dtl(
401 p_line_number IN NUMBER,
402 p_record_type IN VARCHAR2,
403 p_batch_number IN NUMBER,
404 p_dh_challan_recNo IN NUMBER,
405 p_deductee_slno IN NUMBER,
406 p_dh_mode IN VARCHAR2,
407 p_emp_serial_no IN VARCHAR2,
408 p_deductee_code IN VARCHAR2,
409 p_last_emp_pan IN VARCHAR2,
410 p_deductee_pan IN VARCHAR2,
411 p_last_emp_pan_refno IN VARCHAR2,
412 p_deductee_pan_refno IN VARCHAR2,
413 p_vendor_name IN VARCHAR2,
414 p_deductee_tds_income_tax IN NUMBER,
415 p_deductee_tds_surcharge IN NUMBER,
416 p_deductee_tds_cess IN NUMBER,
417 p_deductee_total_tax_deducted IN NUMBER,
418 p_last_total_tax_deducted IN VARCHAR2,
419 p_deductee_total_tax_deposit IN NUMBER,
420 p_last_total_tax_deposit IN VARCHAR2,
421 p_total_purchase IN VARCHAR2,
422 p_base_taxabale_amount IN NUMBER,
426 p_tds_tax_rate IN NUMBER,
423 p_gl_date IN DATE,
424 p_tds_invoice_date IN DATE,
425 p_deposit_date IN VARCHAR2,
427 p_grossingUp_ind IN VARCHAR2,
428 p_book_ent_oth IN VARCHAR2,
429 p_certificate_issue_date IN VARCHAR2,
430 p_remarks1 IN VARCHAR2,
431 p_remarks2 IN VARCHAR2,
432 p_remarks3 IN VARCHAR2,
433 p_dh_recHash IN VARCHAR2,
434 p_generate_headers IN VARCHAR2
435 ) ;
436
437 -- Validation related Procedures.
438
439 PROCEDURE validate_file_header
440 ( p_line_number IN NUMBER ,
441 p_record_type IN VARCHAR2,
442 p_quartfile_type IN VARCHAR2,
443 p_upload_type IN VARCHAR2,
444 p_file_creation_date IN DATE,
445 p_file_sequence_number IN NUMBER,
446 p_uploader_type IN VARCHAR2,
447 p_deductor_tan IN VARCHAR2,
448 p_number_of_batches IN NUMBER,
449 p_ret_prep_util IN VARCHAR2, /*Bug 8880543 - Added Return Preperation Utility*/
450 p_period IN VARCHAR2,
451 p_challan_start_date IN DATE,
452 p_challan_end_date IN DATE,
453 p_fin_year IN NUMBER,
454 p_return_code OUT NOCOPY VARCHAR2,
455 p_return_message OUT NOCOPY VARCHAR2
456 ) ;
457
458 PROCEDURE validate_batch_header
459 ( p_line_number IN NUMBER,
460 p_record_type IN VARCHAR2,
461 p_batch_number IN NUMBER,
462 p_challan_cnt IN NUMBER,
463 p_quart_form_number IN VARCHAR2,
464 p_deductor_tan IN VARCHAR2,
465 p_assessment_year IN NUMBER,
466 p_financial_year IN NUMBER,
467 p_deductor_name IN VARCHAR2,
468 p_deductor_pan IN VARCHAR2, /*Bug 8880543 - Added for Validating PAN Number*/
469 p_tan_address1 IN VARCHAR2,
470 p_tan_state_code IN NUMBER,
471 p_tan_pin IN NUMBER,
472 p_deductor_type IN VARCHAR2, /*Bug 8880543 - Modified Deductor Status to Deductor Type*/
473 p_addrChangedSinceLastReturn IN VARCHAR2,
474 p_personNameRespForDedection IN VARCHAR2,
475 p_personDesgnRespForDedection IN VARCHAR2,
476 p_RespPers_flat_no IN VARCHAR2 , -- Bug 6007891
477 p_RespPers_prem_bldg IN VARCHAR2 , -- Bug 6007891
478 p_RespPers_rd_st_lane IN VARCHAR2 , -- Bug 6007891
479 p_RespPers_area_loc IN VARCHAR2 , -- Bug 6007891
480 p_RespPers_tn_cty_dt IN VARCHAR2 , -- Bug 6007891
481 p_RespPersState IN NUMBER,
482 p_RespPersPin IN NUMBER,
483 p_RespPers_tel_no IN VARCHAR2 , -- Bug 6007891
484 p_RespPers_email IN VARCHAR2 , -- Bug 6007891
485 p_RespPersAddrChange IN VARCHAR2,
486 p_totTaxDeductedAsPerDeductee IN NUMBER,
487 p_ao_approval IN VARCHAR2,
488 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - Start*/
489 p_state_name IN VARCHAR2,
490 p_pao_code IN VARCHAR2,
491 p_ddo_code IN VARCHAR2,
492 p_ministry_name IN VARCHAR2,
493 p_pao_registration_no IN NUMBER,
494 p_ddo_registration_no IN VARCHAR2,
495 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - End*/
496 p_RespPerson_mobile IN VARCHAR2,
497 p_deductor_phone_no IN VARCHAR2,
498 p_resp_person_phone_no IN VARCHAR2,/*ETDS FVU3.1*/
499 p_return_code OUT NOCOPY VARCHAR2,
500 p_return_message OUT NOCOPY VARCHAR2
501 ) ;
502
503 PROCEDURE validate_challan_detail
504 (p_line_number IN NUMBER ,
505 p_record_type IN VARCHAR2,
506 p_batch_number IN NUMBER,
507 p_challan_dtl_slno IN NUMBER,
508 p_deductee_cnt IN NUMBER,
509 p_nil_challan_indicat IN VARCHAR2,
510 p_tds_section IN VARCHAR2,
511 p_amt_of_tds IN NUMBER,
512 p_amt_of_surcharge IN NUMBER,
513 p_amt_of_cess IN NUMBER,
514 p_amt_of_oth IN NUMBER,
515 p_tds_amount IN NUMBER,
516 p_total_income_tds IN NUMBER,
517 p_challan_num IN VARCHAR2,
518 p_bank_branch_code IN VARCHAR2,
519 p_challan_no IN VARCHAR2,
520 p_challan_Date IN DATE,
521 p_check_number IN NUMBER,
522 /* p_RespPerson_mobile IN VARCHAR2,-- Bug 10315928
523 p_deductor_phone_no IN VARCHAR2,-- Bug 10315928
524 p_resp_person_phone_no IN VARCHAR2,--Bug 10315928*//*ETDS FVU3.1*/
525 p_tds_vendor_classification IN VARCHAR2, /* Bug 10315928*/
526 p_return_code OUT NOCOPY VARCHAR2,
527 p_return_message OUT NOCOPY VARCHAR2
528 ) ;
529
530 PROCEDURE validate_deductee_detail
531 ( p_line_number IN NUMBER ,
532 p_record_type IN VARCHAR2,
533 p_batch_number IN NUMBER,
534 p_challan_line_num IN NUMBER,
535 p_deductee_slno IN NUMBER,
536 p_dh_mode IN VARCHAR2,
540 p_amt_of_tds IN NUMBER,
537 p_quart_deductee_code IN VARCHAR2,
538 p_deductee_pan IN VARCHAR2,
539 p_vendor_name IN VARCHAR2,
541 p_amt_of_surcharge IN NUMBER ,
542 p_amt_of_cess IN NUMBER ,
543 p_deductee_total_tax_deducted IN NUMBER,
544 p_base_taxabale_amount IN NUMBER,
545 p_gl_date IN DATE ,
546 p_book_ent_oth IN VARCHAR2,
547 p_return_code OUT NOCOPY VARCHAR2,
548 p_return_message OUT NOCOPY VARCHAR2
549 ) ;
550
551 PROCEDURE check_numeric
552 (p_variable IN VARCHAR2 ,
553 p_err IN VARCHAR2 ,
554 p_action IN VARCHAR2
555 ) ;
556
557 /* Functional Related Procedures */
558
559 PROCEDURE quarterly_returns(
560 p_err_buf OUT NOCOPY VARCHAR2,
561 p_ret_code OUT NOCOPY NUMBER,
562 -- p_legal_entity_id IN NUMBER, --commented by csahoo for bug#6158875
563 -- p_profile_org_id IN NUMBER, --commented by csahoo for bug#6158875
564 p_tan_number IN VARCHAR2,
565 p_fin_year IN NUMBER,
566 p_period IN VARCHAR2,
567 p_tax_authority_id IN NUMBER,
568 p_tax_authority_site_id IN NUMBER,
569 p_organization_id IN NUMBER,
570 p_deductor_addr_org_id IN NUMBER, /*ETDS FVU3.1 Bug#11896244*/
571 p_deductor_name IN VARCHAR2,
572 p_deductor_state IN VARCHAR2,
573 p_addrChangedSinceLastRet IN VARCHAR2,
574 --p_deductor_status IN VARCHAR2, /*Bug 8880543 - Coomented for eTDS/eTCS FVU Changes*/
575 p_persRespForDeduction IN VARCHAR2,
576 p_desgOfPersResponsible IN VARCHAR2,
577 p_RespPers_flat_no IN VARCHAR2 , -- Bug 6007891
578 p_RespPers_prem_bldg IN VARCHAR2 , -- Bug 6007891
579 p_RespPers_rd_st_lane IN VARCHAR2 , -- Bug 6007891
580 p_RespPers_area_loc IN VARCHAR2 , -- Bug 6007891
581 p_RespPers_tn_cty_dt IN VARCHAR2 , -- Bug 6007891
582 p_RespPersState IN VARCHAR2 ,
583 p_RespPersPin IN NUMBER ,
584 --p_RespPers_tel_no IN VARCHAR2 , -- Bug 6007891
585 --p_RespPers_email IN VARCHAR2 , -- Bug 6007891
586 p_RespPersAddrChange IN VARCHAR2,
587 p_challan_Start_Date IN VARCHAR2, --changed the datatype by csahoo for bug#6158875
588 p_challan_End_Date IN VARCHAR2, --changed the datatype by csahoo for bug#6158875
589 p_pro_rcpt_num_org_ret IN NUMBER,
590 p_file_path IN VARCHAR2,
591 p_filename IN VARCHAR2,
592 p_action IN VARCHAR2 DEFAULT NULL ,
593 p_include_list IN VARCHAR2, --Date 11-05-2007 by Sacsethi for bug 5647248
594 p_exclude_list IN VARCHAR2
595
596 ) ;
597
598 /* Comment out Yearly return program by Chong for eTDS ER bug#16414088
599 PROCEDURE yearly_returns(
600 p_err_buf OUT NOCOPY VARCHAR2,
601 p_ret_code OUT NOCOPY NUMBER,
602 -- p_legal_entity_id IN NUMBER, --commented by csahoo for bug#6158875
603 --p_profile_org_id IN NUMBER, --commented by csahoo for bug#6158875
604 p_tan_number IN VARCHAR2,
605 p_fin_year IN NUMBER,
606 p_organization_id IN NUMBER,
607 p_tax_authority_id IN NUMBER,
608 p_tax_authority_site_id IN NUMBER,
609 p_deductor_name IN VARCHAR2,
610 p_deductor_state IN VARCHAR2,
611 p_addrChangedSinceLastRet IN VARCHAR2,
612 p_deductor_status IN VARCHAR2,
613 p_persRespForDeduction IN VARCHAR2,
614 p_desgOfPersResponsible IN VARCHAR2,
615 p_challan_Start_Date IN VARCHAR2, --changed the datatype by csahoo for bug#6158875
616 p_challan_End_Date IN VARCHAR2, --changed the datatype by csahoo for bug#6158875
617 --p_pro_rcpt_num_org_ret IN NUMBER, --commented by csahoo for bug#6158875
618 p_file_path IN VARCHAR2,
619 p_filename IN VARCHAR2,
620 p_generate_headers IN VARCHAR2 DEFAULT NULL
621 );
622 -- ended, Harshita for Bug 4525089
623 */
624
625 PROCEDURE generate_etds_returns
626 (
627 p_err_buf OUT NOCOPY VARCHAR2,
628 p_ret_code OUT NOCOPY NUMBER,
629 p_tan_number IN VARCHAR2,
630 p_fin_year IN NUMBER,
631 p_organization_id IN NUMBER, -- Harshita for Bug 4889272
632 p_deductor_addr_org_id IN NUMBER DEFAULT NULL,/*ETDS FVU3.1 Bug#11896244*/
633 p_tax_authority_id IN NUMBER,
634 p_tax_authority_site_id IN NUMBER,
635 p_deductor_name IN VARCHAR2,
636 p_deductor_state IN VARCHAR2,
637 p_addrChangedSinceLastRet IN VARCHAR2,
638 --p_deductor_status IN VARCHAR2, /*Bug 8880543 - Coomented for eTDS/eTCS FVU Changes*/
639 p_persRespForDeduction IN VARCHAR2,
640 p_desgOfPersResponsible IN VARCHAR2,
641 pv_challan_Start_Date IN VARCHAR2, /* rallamse for bu# 4334682 changed to varchar2 from date */
642 pv_challan_End_Date IN VARCHAR2, /* rallamse for bu# 4334682 changed to varchar2 from date */
643 p_pro_rcpt_num_org_ret IN NUMBER,
644 p_file_path IN VARCHAR2,
645 p_filename IN VARCHAR2,
646 p_generate_headers IN VARCHAR2 DEFAULT NULL,
647 p_period IN VARCHAR2 DEFAULT NULL,
648 p_RespPers_flat_no IN VARCHAR2 DEFAULT NULL, -- Bug 6007891
649 p_RespPers_prem_bldg IN VARCHAR2 DEFAULT NULL, -- Bug 6007891
650 p_RespPers_rd_st_lane IN VARCHAR2 DEFAULT NULL, -- Bug 6007891
651 p_RespPers_area_loc IN VARCHAR2 DEFAULT NULL, -- Bug 6007891
652 p_RespPers_tn_cty_dt IN VARCHAR2 DEFAULT NULL, -- Bug 6007891
653 p_RespPersState IN VARCHAR2 DEFAULT NULL,
654 p_RespPersPin IN NUMBER DEFAULT NULL,
658 p_action IN VARCHAR2 DEFAULT NULL,
655 -- p_RespPers_tel_no IN VARCHAR2 DEFAULT NULL, -- Bug 6007891
656 -- p_RespPers_email IN VARCHAR2 DEFAULT NULL, -- Bug 6007891
657 p_RespPersAddrChange IN VARCHAR2 DEFAULT NULL,
659 p_form_number IN VARCHAR2 DEFAULT NULL, --Date 11-05-2007 by Sacsethi for bug 5647248
660 p_include_list IN VARCHAR2 DEFAULT NULL,
661 p_exclude_list IN VARCHAR2 DEFAULT NULL
662
663 ) ;
664
665
666 END jai_ap_tds_etds_pkg;