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