[Home] [Help]
PACKAGE BODY: APPS.JAI_ETCS_PKG
Source
1 PACKAGE BODY jai_etcs_pkg AS
2 /* $Header: jai_ar_etcs_prc.plb 120.18.12020000.3 2013/03/18 06:00:42 zxin ship $ */
3
4
5
6 /***************************************************************************************************
7 CREATED BY : CSahoo
8 CREATED DATE : 01-FEB-2007
9 ENHANCEMENT BUG : 5631784
10 PURPOSE : NEW ENH: TAX COLLECTION AT SOURCE IN RECEIVABLES
11
12 -- #
13 -- # Change History -
14
15
16 1. 01/02/2007 CSahoo for bug#5631784. File Version 120.0
17 Forward Porting of 11i BUG#4742259 (TAX COLLECTION AT SOURCE IN RECEIVABLES)
18
19 Column name org_tan_num is renamed to org_tan_no. So the change is propogated into this
20 package.
21 File name changed to jai_ar_etcs_prc.plb
22 Removed the Hard Coded values and replaced them with the
23 constants jai_constants.ar_cash_tax_confirmed and jai_constants.trx_type_inv_comp
24 2. 14/05/2007 bduvarag for bug#5631784. File Version 120.1
25 Removed local_fnd_global
26
27 3. 26.06.2007 sacsethi for bug 6153881 file version 120.2
28
29 Problem - R12RUP03-ST1:UNABLE TO RUN TCS RETURN REPORTS
30
31 Solution - According to R12 Standard we should not use legal entity in out code , so
32 removing legal_entity_id from code ...
33
34 4. 28/06/2007 sacsethi for bug 6157120 File version 120.5
35
36 R12RUP03-ST1:ETCS REPORT RUNS INTO ERROR
37
38 Code Fix -
39
40 1. Table insertion JAI_AP_ETDS_REQUESTS , Missing Data related to WHO Columns
41 2. cursor c_pan_number is changed to get pan no .
42 3. Cursor c_fin_year , Tan_number datatype changed from number to varchar2
43 4. Cursor c_check_dtls ,c_bank_branch_code is changed
44
45 Problem - Table used under this cursor has been obsuleted in R12
46 we should not use ap_check_all , ap_bank_branches etc.
47
48 5. In File jai_constants.pls - Two variable is used pan_no , accounting_information to Avoid Hard coding of these information.
49
50 4. 03/07/2007 sacsethi for bug 6157120 File version 120.6
51
52 R12RUP03-ST1:ETCS REPORT RUNS INTO ERROR
53
54 Problem - SH Cess FP missing
55
56 Code Fix -
57
58 1. SHE Cess also added in CESS amount .
59
60 Previous Formula -
61 cess_amount := tcs_cess_amount + sur_cess_amount
62
63 New Formula -
64 cess_amount := tcs_cess_amount + sur_cess_amount + tcs_sh_cess_amount
65
66 5. 22-SEP-2009 Bug 8880543
67 Added for eTDS/eTCS FVU changes.
68
69 6. 09-OCT-2009 Added by Bgowrava for Bug#9005248
70 Replace the literal 'PANNOTREQD' to 'PANNOTAVBL' according to the latest notification.
71
72 7. 27-Oct-2010 Bug 10238421
73 Description: eTCS changes for FVU 3.0
74
75 8. 18-Aug-2011 Bug:12597773 by amandali
76 Description:ETCS Quarterly reports not triggering Form 27B
77 Fix:Added parameters p_RespPers_flat_no,p_RespPers_prem_bldg,p_RespPers_rd_st_lane,p_RespPers_area_loc,
78 p_RespPers_tn_cty_dt and commented p_RespPersAddress in the procedures generate_etcs_returns, quarterly_returns,
79 create_quarterly_batch_header, validate_batch_header, create_quarterly_fh
80 Also, added a call to trigger JAINTCSB concurrent in generate_etcs_returns procedure.
81 Added validations to the parameters added in validate_batch_header
82
83 9. March 29, 2012 FVU 3.4 - Added Consolidated file hash in File Header
84
85 *****************************************************************************************************/
86
87 /*Bug 8880543 - Changes for eTDS/eTCS FVU Changes - Start*/
88
89 FUNCTION VALIDATE_ALPHA_NUMERIC(p_str VARCHAR2, p_length NUMBER) RETURN VARCHAR2 IS
90 lv_resp VARCHAR2(10);
91 BEGIN
92 FOR i in
93 (SELECT TRANSLATE(UPPER(substr(p_str, 1, 5)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','AAAAAAAAAAAAAAAAAAAAAAAAAA') src_str1,
94 TRANSLATE(substr(p_str, 6, 4),'0123456789','0000000000') src_str2,
95 TRANSLATE(UPPER(substr(p_str, 10, 1)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','AAAAAAAAAAAAAAAAAAAAAAAAAA') src_str3,
96 'AAAAA0000A' dest_str
97 FROM dual) LOOP
98
99 IF (i.src_str1 || i.src_str2 || i.src_str3) = i.dest_str or p_str = 'PANNOTAVBL' then
100 lv_resp := 'VALID';
101 ELSE
102 lv_resp := 'INVALID';
103 END IF;
104
105 EXIT;
106 END LOOP;
107
108 RETURN lv_resp;
109 END;
110
111 PROCEDURE chk_err (p_err IN VARCHAR2,
112 p_message IN VARCHAR2) IS
113 BEGIN
114 IF (p_err = 'E') THEN
115 FND_FILE.put_line(FND_FILE.log, p_message);
116 RAISE_APPLICATION_ERROR(-20099, p_message, true);
117 ELSIF (p_err in ('N', 'S')) THEN
118 FND_FILE.put_line(FND_FILE.log, p_message);
119 END IF;
120 END chk_err;
121
122 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 begin
123
124 --Added by Chong for DTC ER 2012/09/11 Get TCS calendar from regime start
125
126 /*-------------------------------------------------------------------------------------------------------------------------------+
127 | Created By : Chong |
128 | Creation Date : 11/Sep/2012 |
129 | Bug Number/ER Name : ZX Dtc |
130 | SubProgram Name : get_start_end_date |
131 | Type : PROCEDURE |
132 | Purpose : In DTC ER, TCS and TDS financial year defined in regime lever |
133 | This funcation get start date and end date from regime for TDS and TCS module |
134 | TDD Reference : |
135 | Assumptions : |
136 | Called From : |
137 |--------------------------------------------------------------------------------------------------------------------------------|
138 | parameters IN/OUT Type Required Description and Purpose |
139 | ------------ -------- ------ ---------- ------------------------- |
140 | cp_attr_code IN varchar2 YES TDS_CALENDAR / TCS_CALENDAR |
141 | cp_rgm_code IN varchar2 YES TDS / TCS |
142 | cp_fin_year IN Number YES Given financial year |
143 | cp_start_date IN DATE YES start date of Given financial year |
144 | cp_end_date IN DATE YES End date of Given financial year |
145 ---------------------------------------------------------------------------------------------------------------------------------*/
146 PROCEDURE get_start_end_date(
147 cp_attr_code IN JAI_RGM_REGISTRATIONS.ATTRIBUTE_CODE%TYPE --TDS_CALENDAR / TCS_CALENDAR
148 , cp_rgm_code IN JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE --TDS / TCS
149 , cp_fin_year IN NUMBER
150 , cp_start_date OUT NOCOPY GL_PERIODS.START_DATE%TYPE
151 , cp_end_date OUT NOCOPY GL_PERIODS.END_DATE%TYPE
152 )
153 IS
154
155 cursor c_get_tcs_rgm_atrbt
156 is
157 SELECT jrr.ATTRIBUTE_VALUE
158 FROM JAI_RGM_DEFINITIONS jrd,
159 JAI_RGM_REGISTRATIONS jrr
160 WHERE jrd.REGIME_ID = jrr.REGIME_ID
161 AND jrr.ATTRIBUTE_CODE = cp_attr_code
162 AND jrd.REGIME_CODE = cp_rgm_code;
163
164 CURSOR c_get_start_end_date(cp_calendar_id NUMBER
165 ,cp_fin_year IN NUMBER)
166 IS
167 SELECT a.START_DATE
168 ,a.END_DATE
169 FROM GL_PERIODS a
170 , gl_period_sets b
171 WHERE a.PERIOD_SET_NAME = b.period_set_name
172 AND b.period_set_id = cp_calendar_id
173 AND a.PERIOD_YEAR = cp_fin_year;
174
175
176 lv_gl_calendar_id number;
177 lv_fin_year GL_PERIODS.PERIOD_YEAR%TYPE;
178
179 BEGIN
180
181 OPEN c_get_tcs_rgm_atrbt;
182 FETCH c_get_tcs_rgm_atrbt INTO lv_gl_calendar_id;
183 CLOSE c_get_tcs_rgm_atrbt;
184
185 OPEN c_get_start_end_date(lv_gl_calendar_id, cp_fin_year);
186 FETCH c_get_start_end_date INTO cp_start_date, cp_end_date;
187 CLOSE c_get_start_end_date;
188
189 jai_cmn_utils_pkg.WRITE_FND_LOG(FND_LOG.LEVEL_STATEMENT, 'JAI.PLSQL.jai_etcs_pkg.get_start_end_date'
190 ,'Get ' || cp_rgm_code || ' Finanical year Start:' || cp_start_date || ' End:' || cp_end_date);
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 jai_cmn_utils_pkg.WRITE_FND_LOG(FND_LOG.LEVEL_UNEXPECTED, 'JAI.PLSQL.jai_etcs_pkg.get_start_end_date.Other_Exception '
195 , SQLCODE || ':' || SQLERRM
196 );
197
198 END get_start_end_date;
199 --Added by Chong for DTC ER 2012/09/11 Get TCS calendar from regime end
200
201 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 end.
202
203 PROCEDURE get_attr_value (p_org_id IN NUMBER,
204 p_attr_code IN VARCHAR2,
205 p_attr_val OUT NOCOPY VARCHAR2,
206 p_err OUT NOCOPY VARCHAR2,
207 p_return_message OUT NOCOPY VARCHAR2
208 ) IS
209
210 CURSOR c_org_exists (p_org_id NUMBER)
211 IS
212 select '1'
213 from jai_rgm_definitions jrd,
214 jai_rgm_parties jrp
215 where jrd.regime_code = 'TCS'
216 and jrd.regime_id = jrp.regime_id
217 and jrp.organization_id = p_org_id;
218
219 CURSOR c_party_setup (p_attr_code VARCHAR2, p_org_id NUMBER)
220 IS
221 select jrpr.attribute_value
222 from jai_rgm_parties jrp,
223 jai_rgm_definitions jrd,
224 jai_rgm_party_regns jrpr,
225 jai_rgm_registrations jrr
226 where jrd.regime_code = 'TCS'
227 and jrd.regime_id = jrp.regime_id
228 and jrp.regime_org_id = jrpr.regime_org_id
229 and jrr.attribute_code = p_attr_code
230 and jrp.organization_id = p_org_id
231 and jrr.registration_id = jrpr.registration_id;
232
233 CURSOR c_regime_setup (p_attr_code VARCHAR2)
234 IS
235 select jrr.attribute_value
236 from jai_rgm_definitions jrd,
237 jai_rgm_registrations jrr
238 where jrd.regime_code = 'TCS'
239 and jrd.regime_id = jrr.regime_id
240 and jrr.attribute_code = p_attr_code;
241
242 l_org_exists NUMBER;
243 l_attr_val VARCHAR2(240);
244
245 BEGIN
246
247 l_org_exists := 0;
248 l_attr_val := NULL;
249
250 OPEN c_org_exists (p_org_id);
251 FETCH c_org_exists INTO l_org_exists;
252 CLOSE c_org_exists;
253
254 IF (l_org_exists = 0) THEN
255 p_return_message := 'Regime Registration Setup does not exist for the current Organization';
256 p_err := 'E';
257 p_attr_val := NULL;
258 return;
259 END IF;
260
261 OPEN c_party_setup (p_attr_code, p_org_id);
262 FETCH c_party_setup INTO l_attr_val;
263 CLOSE c_party_setup;
264
265 IF (l_attr_val IS NULL) THEN
266 OPEN c_regime_setup (p_attr_code);
267 FETCH c_regime_setup INTO l_attr_val;
268 CLOSE c_regime_setup;
269 END IF;
270
271 IF (l_attr_val IS NULL) THEN
272 p_return_message := 'Attribute ' || p_attr_code || ' is not defined';
273 p_err := 'N';
274 p_attr_val := NULL;
275 return;
276 ELSE
277 p_attr_val := l_attr_val;
278 p_return_message := 'Attribute ' || p_attr_code || ' = ' || p_attr_val;
279 p_err := 'S';
280 END IF;
281
282 END get_attr_value;
283
284 /*Bug 8880543 - Changes for eTDS/eTCS FVU Changes - End*/
285
286
287 PROCEDURE openFile(
288 p_directory IN VARCHAR2,
289 p_filename IN VARCHAR2
290 ) IS
291
292 BEGIN
293
294 jai_ap_tds_etds_pkg.v_filehandle := UTL_FILE.fopen(p_directory, p_filename, 'W', 2000);
295 jai_ap_tds_etds_pkg.v_utl_file_dir := p_directory;
296 jai_ap_tds_etds_pkg.v_utl_file_name := p_filename;
297
298 END openFile;
299
300 PROCEDURE closeFile IS
301 BEGIN
302 UTL_FILE.fclose(jai_ap_tds_etds_pkg.v_filehandle);
303 END closeFile;
304
305 -- Date Population procedures for ETCS Quarterly Returns
306 PROCEDURE create_quarterly_fh(
307 p_batch_id IN NUMBER,
308 p_period IN VARCHAR2,
309 p_RespPers_flat_no IN VARCHAR2 , -- Bug 12597773
310 p_RespPers_prem_bldg IN VARCHAR2 , -- Bug 12597773
311 p_RespPers_rd_st_lane IN VARCHAR2 , -- Bug 12597773
312 p_RespPers_area_loc IN VARCHAR2 , -- Bug 12597773
313 p_RespPers_tn_cty_dt IN VARCHAR2 , -- Bug 12597773
314 --p_RespPersAddress IN VARCHAR2,
315 p_RespPersState IN VARCHAR2,
316 p_RespPersPin IN NUMBER,
317 p_RespPersAddrChange IN VARCHAR2
318 ) IS
319 v_req JAI_AP_ETDS_REQUESTS%rowtype;
320 BEGIN
321
322 SELECT * INTO v_req FROM JAI_AP_ETDS_REQUESTS WHERE batch_id = p_batch_id;
323 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, 'Input Parameters to this Request:');
324 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, '-------------------------------------------------');
325 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
326 ' batch_id ->'||v_req.batch_id||fnd_global.local_chr(10)
327 ||' request_id ->'||v_req.request_id||fnd_global.local_chr(10)
328 ||' operating_unit_id ->'||v_req.operating_unit_id||fnd_global.local_chr(10)
329 ||' org_tan_number ->'||v_req.org_tan_number||fnd_global.local_chr(10)
330 ||' financial_year ->'||v_req.financial_year||fnd_global.local_chr(10)
331 ||' tax_authority_id ->'||v_req.tax_authority_id||fnd_global.local_chr(10)
332 ||' tax_authority_site_id ->'||v_req.tax_authority_site_id||fnd_global.local_chr(10)
333 ||' organization_id ->'||v_req.organization_id||fnd_global.local_chr(10)
334 ||' collector_name ->'||v_req.deductor_name||fnd_global.local_chr(10)
335 ||' collector_state ->'||v_req.deductor_state||fnd_global.local_chr(10)
336 ||' addr_changed_since_last_ret->'||v_req.addr_changed_since_last_ret||fnd_global.local_chr(10)
337 ||' collector_status ->'||v_req.deductor_status||fnd_global.local_chr(10)
338 ||' person_resp_for_collection ->'||v_req.person_resp_for_deduction||fnd_global.local_chr(10)
339 ||' designation_of_pers_resp ->'||v_req.designation_of_pers_resp||fnd_global.local_chr(10)
340 ||' challan_start_date ->'||v_req.challan_start_date||fnd_global.local_chr(10)
341 ||' challan_end_date ->'||v_req.challan_end_date||fnd_global.local_chr(10)
342 ||' file_path ->'||v_req.file_path||fnd_global.local_chr(10)
343 ||' filename ->'||v_req.filename||fnd_global.local_chr(10)
344 ||' Period ->'||p_period||fnd_global.local_chr(10)
345 --||' RespPerson''s Address ->'||p_RespPersAddress||fnd_global.local_chr(10) /* Bug 12597773*/
346 ||' RespPerson''s Flat No ->'||p_RespPers_Flat_no||fnd_global.local_chr(10) /* Bug 12597773*/
347 ||' RespPerson''s Premises/Bldg ->'||p_RespPers_prem_bldg||fnd_global.local_chr(10) /* Bug 12597773*/
348 ||' RespPerson''s Rd/St/Lane ->'||p_RespPers_rd_st_lane||fnd_global.local_chr(10) /* Bug 12597773*/
349 ||' RespPerson''s Area/Loc ->'||p_RespPers_area_loc||fnd_global.local_chr(10) /* Bug 12597773*/
350 ||' RespPerson''s Tn/Cty/Dt ->'||p_RespPers_tn_cty_dt||fnd_global.local_chr(10) /* Bug 12597773*/
351 ||' RespPerson''s State ->'||p_RespPersState||fnd_global.local_chr(10)
352 ||' RespPerson''s Pin ->'||p_RespPersPin||fnd_global.local_chr(10)
353 ||' RespPerson''s Addr Changed ->'||p_RespPersAddrChange||fnd_global.local_chr(10)
354 );
355
356 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
357 LPAD('Line No', sq_len_9, v_quart_pad) || v_pad_char ||
358 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
359 LPAD('FT', sq_len_4, v_quart_pad) || v_pad_char ||
360 LPAD('UT', sq_len_2, v_quart_pad) || v_pad_char ||
361 LPAD('FileDate', sq_len_8, v_quart_pad) || v_pad_char ||
362 LPAD('SeqNo', sq_len_9, v_quart_pad) || v_pad_char ||
363 LPAD('U', sq_len_1, v_quart_pad) || v_pad_char ||
364 LPAD('TAN', sq_len_10, v_quart_pad) || v_pad_char ||
365 LPAD('Batch Cnt', sq_len_9, v_quart_pad) || v_pad_char ||
366 LPAD('Ret Prep util', sq_len_75, v_quart_pad) || v_pad_char || /*Bug 8880543 - Added Return Preperation Utility*/
367 LPAD('RH', sq_len_2, v_quart_pad) || v_pad_char ||
368 LPAD('FV', sq_len_2, v_quart_pad) || v_pad_char ||
369 LPAD('FH', sq_len_2, v_quart_pad) || v_pad_char ||
370 LPAD('SV', sq_len_2, v_quart_pad) || v_pad_char ||
371 LPAD('SH', sq_len_2, v_quart_pad) || v_pad_char ||
372 LPAD('SV', sq_len_2, v_quart_pad) || v_pad_char ||
373 LPAD('SH', sq_len_2, v_quart_pad) );
374
375 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
376 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
377 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
378 LPAD(v_underline_char, sq_len_4, v_underline_char) || v_pad_char ||
379 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
380 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
381 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
382 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
383 LPAD(v_underline_char, sq_len_10,v_underline_char) || v_pad_char ||
384 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
385 LPAD(v_underline_char, sq_len_75,v_underline_char) || v_pad_char || /*Bug 8880543 - Added Return Preperation Utility*/
386 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
387 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
388 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
389 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
390 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
391 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
392 LPAD(v_underline_char, sq_len_2, v_underline_char) );
393
394 END create_quarterly_fh;
395
396 PROCEDURE create_quarterly_bh
397 IS
398 BEGIN
399 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, ' ' ) ;
400 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
401 LPAD('Line No', sq_len_9, v_quart_pad) || v_pad_char ||
402 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
403 LPAD('Batch No', sq_len_9, v_quart_pad) || v_pad_char ||
404 LPAD('ChallCnt', sq_len_9, v_quart_pad) || v_pad_char ||
405 LPAD('FN', sq_len_4, v_quart_pad) || v_pad_char ||
406 LPAD('TT', sq_len_2, v_quart_pad) || v_pad_char ||
407 LPAD('BI', sq_len_2, v_quart_pad) || v_pad_char ||
408 LPAD('OT', sq_len_2, v_quart_pad) || v_pad_char || /*Bug 10238421*/
409 LPAD('PT', sq_len_2, v_quart_pad) || v_pad_char || /*Bug 10238421*/
410 LPAD('TN', sq_len_2, v_quart_pad) || v_pad_char || /*Bug 10238421*/
411 LPAD('TD', sq_len_2, v_quart_pad) || v_pad_char || /*Bug 10238421*/
412 LPAD('LT', sq_len_2, v_quart_pad) || v_pad_char ||
413 LPAD('Col TAN', sq_len_10, v_quart_pad) || v_pad_char ||
414 LPAD('RN', sq_len_2, v_quart_pad) || v_pad_char || /*Bug 10238421*/
415 LPAD('Col PAN', sq_len_10, v_quart_pad) || v_pad_char ||
416 LPAD('Ass.Yr', sq_len_6, v_quart_pad) || v_pad_char ||
417 LPAD('Fin.Yr', sq_len_6, v_quart_pad) || v_pad_char ||
418 LPAD('PD', sq_len_2, v_quart_pad) || v_pad_char ||
419 LPAD('Collector Name', sq_len_75, v_quart_pad) || v_pad_char ||
420 LPAD('Collector Branch', sq_len_75, v_quart_pad) || v_pad_char ||
421 LPAD('Collector Addr1', sq_len_25, v_quart_pad) || v_pad_char ||
422 LPAD('Collector Addr2', sq_len_25, v_quart_pad) || v_pad_char ||
423 LPAD('Collector Addr3', sq_len_25, v_quart_pad) || v_pad_char ||
424 LPAD('Collector Addr4', sq_len_25, v_quart_pad) || v_pad_char ||
425 LPAD('Collector Addr5', sq_len_25, v_quart_pad) || v_pad_char ||
426 LPAD('CS', sq_len_2, v_quart_pad) || v_pad_char ||
427 LPAD('ColPIN', sq_len_6, v_quart_pad) || v_pad_char ||
428 LPAD('Collector Email', sq_len_75, v_quart_pad) || v_pad_char ||
429 LPAD('ColSTD', sq_len_5, v_quart_pad) || v_pad_char ||
430 LPAD('Col Phone', sq_len_10, v_quart_pad) || v_pad_char ||
431 LPAD('C', sq_len_1, v_quart_pad) || v_pad_char ||
432 LPAD('T', sq_len_1, v_quart_pad) || v_pad_char ||
433 LPAD('RespPerson Name', sq_len_75, v_quart_pad) || v_pad_char ||
434 LPAD('RespPerson Desg', sq_len_20, v_quart_pad) || v_pad_char ||
435 LPAD('RespPerson Addr1', sq_len_25, v_quart_pad) || v_pad_char ||
436 LPAD('RespPerson Addr2', sq_len_25, v_quart_pad) || v_pad_char ||
437 LPAD('RespPerson Addr3', sq_len_25, v_quart_pad) || v_pad_char ||
438 LPAD('RespPerson Addr4', sq_len_25, v_quart_pad) || v_pad_char ||
439 LPAD('RespPerson Addr5', sq_len_25, v_quart_pad) || v_pad_char ||
440 LPAD('RS', sq_len_2, v_quart_pad) || v_pad_char ||
441 LPAD('ResPIN', sq_len_6, v_quart_pad) || v_pad_char ||
442 LPAD('RespPerson Email', sq_len_75, v_quart_pad) || v_pad_char ||
443 LPAD('Mobile', sq_len_75, v_quart_pad) || v_pad_char || /*Bug 10238421*/
444 LPAD('ResSTD', sq_len_5, v_quart_pad) || v_pad_char ||
445 LPAD('ResPhone', sq_len_10, v_quart_pad) || v_pad_char ||
446 LPAD('C', sq_len_1, v_quart_pad) || v_pad_char ||
447 LPAD('TotChallanTax', sq_len_15, v_quart_pad) || v_pad_char ||
448 LPAD('TC', sq_len_2, v_quart_pad) || v_pad_char ||
449 LPAD('SC', sq_len_2, v_quart_pad) || v_pad_char ||
450 LPAD('GT', sq_len_2, v_quart_pad) || v_pad_char ||
451 LPAD('A', sq_len_1, v_quart_pad) || v_pad_char ||
452 LPAD('AO Approval No', sq_len_15, v_quart_pad) || v_pad_char ||
453 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - Start*/
454 LPAD('L', sq_len_1, v_quart_pad) || v_pad_char ||
455 LPAD('SN', sq_len_2, v_quart_pad) || v_pad_char ||
456 LPAD('PAO Code', sq_len_20, v_quart_pad) || v_pad_char ||
457 LPAD('DDO Code', sq_len_20, v_quart_pad) || v_pad_char ||
458 LPAD('MN', sq_len_3, v_quart_pad) || v_pad_char ||
459 LPAD('Ministry Name Other', sq_len_150, v_quart_pad) || v_pad_char ||
460 LPAD('F2', sq_len_12, v_quart_pad) || v_pad_char ||
461 LPAD('PAORgNo', sq_len_7, v_quart_pad) || v_pad_char ||
462 LPAD('DDORgNo', sq_len_10, v_quart_pad) || v_pad_char ||
463 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - End*/
464 LPAD('RH', sq_len_2, v_quart_pad) );
465
466 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
467 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
468 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
469 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
470 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
471 LPAD(v_underline_char, sq_len_4, v_underline_char) || v_pad_char ||
472 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
473 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
474 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
475 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
476 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
477 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
478 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
479 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
480 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
481 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
482 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
483 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
484 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
485 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
486 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
487 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
488 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
489 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
490 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
491 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
492 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
493 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
494 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
495 LPAD(v_underline_char, sq_len_5, v_underline_char) || v_pad_char ||
496 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
497 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
498 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
499 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
500 LPAD(v_underline_char, sq_len_20, v_underline_char) || v_pad_char ||
501 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
502 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
503 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
504 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
505 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
506 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
507 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
508 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
509 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
510 LPAD(v_underline_char, sq_len_5, v_underline_char) || v_pad_char ||
511 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
512 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
513 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
514 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
515 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
516 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
517 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
518 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
519 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - Start*/
520 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
521 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
522 LPAD(v_underline_char, sq_len_20, v_underline_char) || v_pad_char ||
523 LPAD(v_underline_char, sq_len_20, v_underline_char) || v_pad_char ||
524 LPAD(v_underline_char, sq_len_3, v_underline_char) || v_pad_char ||
525 LPAD(v_underline_char, sq_len_150, v_underline_char) || v_pad_char ||
526 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
527 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
528 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
529 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - End*/
530 LPAD(v_underline_char, sq_len_2, v_underline_char) );
531
532 END create_quarterly_bh;
533
534 PROCEDURE create_quarterly_cd
535 IS
536 BEGIN
537 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, ' ' ) ;
538 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
539 LPAD('Line No', sq_len_9 , v_quart_pad) || v_pad_char ||
540 LPAD('RT', sq_len_2 , v_quart_pad) || v_pad_char ||
541 LPAD('Batch No', sq_len_9 , v_quart_pad) || v_pad_char ||
542 LPAD('Chall No', sq_len_9 , v_quart_pad) || v_pad_char ||
543 LPAD('Collect Cnt', sq_len_9 , v_quart_pad) || v_pad_char ||
544 LPAD('I', sq_len_1 , v_quart_pad) || v_pad_char ||
545 LPAD('U', sq_len_2 , v_quart_pad) || v_pad_char ||
546 LPAD('F2', sq_len_2 , v_quart_pad) || v_pad_char ||
547 LPAD('F3', sq_len_2 , v_quart_pad) || v_pad_char ||
548 LPAD('F4', sq_len_2 , v_quart_pad) || v_pad_char ||
549 LPAD('LC', sq_len_2 , v_quart_pad) || v_pad_char ||
550 LPAD('Ch', sq_len_5 , v_quart_pad) || v_pad_char ||
551 LPAD('LV', sq_len_2 , v_quart_pad) || v_pad_char ||
552 LPAD('TrnsVouch', sq_len_9 , v_quart_pad) || v_pad_char ||
553 LPAD('LB', sq_len_2 , v_quart_pad) || v_pad_char ||
554 LPAD('Bank Br', sq_len_7 , v_quart_pad) || v_pad_char ||
555 LPAD('LD', sq_len_2 , v_quart_pad) || v_pad_char ||
556 LPAD('CH. Date', sq_len_8 , v_quart_pad) || v_pad_char ||
557 LPAD('F5', sq_len_2 , v_quart_pad) || v_pad_char ||
558 LPAD('F6', sq_len_2 , v_quart_pad) || v_pad_char ||
559 LPAD('Sec', sq_len_3 , v_quart_pad) || v_pad_char ||
560 LPAD('Oltas Tax', sq_len_15 , v_quart_pad) || v_pad_char ||
561 LPAD('Oltas Sur', sq_len_15 , v_quart_pad) || v_pad_char ||
562 LPAD('Oltas Cess', sq_len_15 , v_quart_pad) || v_pad_char ||
563 LPAD('Oltas Interest', sq_len_15 , v_quart_pad) || v_pad_char ||
564 LPAD('Oltas OtherAmt', sq_len_15 , v_quart_pad) || v_pad_char ||
565 LPAD('Total Deposit', sq_len_15 , v_quart_pad) || v_pad_char ||
566 LPAD('LD', sq_len_2 , v_quart_pad) || v_pad_char ||
567 LPAD('TotTax Deposit', sq_len_15 , v_quart_pad) || v_pad_char ||
568 LPAD('TCS Income Tax', sq_len_15 , v_quart_pad) || v_pad_char ||
569 LPAD('TCS Surcharge', sq_len_15 , v_quart_pad) || v_pad_char ||
570 LPAD('TCS Cess', sq_len_15 , v_quart_pad) || v_pad_char ||
571 LPAD('Total TCS ', sq_len_15 , v_quart_pad) || v_pad_char ||
572 LPAD('TDS Interest', sq_len_15 , v_quart_pad) || v_pad_char ||
573 LPAD('TCS OtherAmt', sq_len_15 , v_quart_pad) || v_pad_char ||
574 LPAD('Cheque/DD', sq_len_15 , v_quart_pad) || v_pad_char ||
575 LPAD('B', sq_len_1 , v_quart_pad) || v_pad_char ||
576 LPAD('Remarks', sq_len_14 , v_quart_pad) || v_pad_char ||
577 LPAD('RH', sq_len_2 , v_quart_pad)
578 );
579
580 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
581 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
582 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
583 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
584 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
585 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
586 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
587 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
588 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
589 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
590 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
591 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
592 LPAD(v_underline_char , sq_len_5 , v_underline_char) || v_pad_char ||
593 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
594 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
595 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
596 LPAD(v_underline_char , sq_len_7 , v_underline_char) || v_pad_char ||
597 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
598 LPAD(v_underline_char , sq_len_8 , v_underline_char) || v_pad_char ||
599 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
600 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
601 LPAD(v_underline_char , sq_len_3 , v_underline_char) || v_pad_char ||
602 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
603 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
604 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
605 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
606 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
607 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
608 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
609 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
610 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
611 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
612 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
613 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
614 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
615 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
616 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
617 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
618 LPAD(v_underline_char , sq_len_14 , v_underline_char) || v_pad_char ||
619 LPAD(v_underline_char , sq_len_2 , v_underline_char)
620 );
621
622 END create_quarterly_cd;
623
624 PROCEDURE create_quarterly_dd IS
625 BEGIN
626 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, ' ' ) ;
627 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
628 LPAD('Line No', sq_len_9, v_quart_pad) || v_pad_char ||
629 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
630 LPAD('Batch No', sq_len_9, v_quart_pad) || v_pad_char ||
631 LPAD('ChallNo', sq_len_9, v_quart_pad) || v_pad_char ||
632 LPAD('ColRecNo', sq_len_9, v_quart_pad) || v_pad_char ||
633 LPAD('M', sq_len_1, v_quart_pad) || v_pad_char ||
634 LPAD('EN', sq_len_2, v_quart_pad) || v_pad_char ||
635 LPAD('C', sq_len_1, v_quart_pad) || v_pad_char ||
636 LPAD('LP', sq_len_2, v_quart_pad) || v_pad_char ||
637 LPAD('Prt Pan', sq_len_10, v_quart_pad) || v_pad_char ||
638 LPAD('LP', sq_len_2, v_quart_pad) || v_pad_char ||
639 LPAD('PAN Ref No', sq_len_10, v_quart_pad) || v_pad_char ||
640 LPAD('Party Name', sq_len_75, v_quart_pad) || v_pad_char ||
641 LPAD('TCS Income Tax ', sq_len_15, v_quart_pad) || v_pad_char ||
642 LPAD('TCS Surcharge', sq_len_15, v_quart_pad) || v_pad_char ||
643 LPAD('TCS Cess', sq_len_15, v_quart_pad) || v_pad_char ||
644 LPAD('TCS Total', sq_len_15, v_quart_pad) || v_pad_char ||
645 LPAD('LT', sq_len_2, v_quart_pad) || v_pad_char ||
646 LPAD('TotTax Deposit', sq_len_15, v_quart_pad) || v_pad_char ||
647 LPAD('LT', sq_len_2, v_quart_pad) || v_pad_char ||
648 LPAD('TP', sq_len_15, v_quart_pad) || v_pad_char ||
649 LPAD('Payment Amt', sq_len_15, v_quart_pad) || v_pad_char ||
650 LPAD('Pay Dt', sq_len_8, v_quart_pad) || v_pad_char ||
651 LPAD('TaxColDt', sq_len_8, v_quart_pad) || v_pad_char ||
652 LPAD('DD', sq_len_2, v_quart_pad) || v_pad_char ||
653 LPAD('Tax Rt', sq_len_7, v_quart_pad) || v_pad_char ||
654 LPAD('GI', sq_len_2, v_quart_pad) || v_pad_char ||
655 LPAD('B', sq_len_1, v_quart_pad) || v_pad_char ||
656 LPAD('TD', sq_len_2, v_quart_pad) || v_pad_char ||
657 LPAD('R', sq_len_75, v_quart_pad) || v_pad_char ||
658 LPAD('Remarks 2', sq_len_75, v_quart_pad) || v_pad_char ||
659 LPAD('Remarks 3', sq_len_14, v_quart_pad) || v_pad_char ||
660 LPAD('RH', sq_len_2, v_quart_pad)
661 );
662
663 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
664 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
665 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
666 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
667 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
668 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
669 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
670 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
671 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
672 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
673 LPAD(v_underline_char , sq_len_10 , v_underline_char) || v_pad_char ||
674 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
675 LPAD(v_underline_char , sq_len_10 , v_underline_char) || v_pad_char ||
676 LPAD(v_underline_char , sq_len_75 , v_underline_char) || v_pad_char ||
677 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
678 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
679 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
680 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
681 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
682 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
683 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
684 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
685 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
686 LPAD(v_underline_char , sq_len_8 , v_underline_char) || v_pad_char ||
687 LPAD(v_underline_char , sq_len_8 , v_underline_char) || v_pad_char ||
688 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
689 LPAD(v_underline_char , sq_len_7 , v_underline_char) || v_pad_char ||
690 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
691 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
692 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
693 LPAD(v_underline_char , sq_len_75 , v_underline_char) || v_pad_char ||
694 LPAD(v_underline_char , sq_len_75 , v_underline_char) || v_pad_char ||
695 LPAD(v_underline_char , sq_len_14 , v_underline_char) || v_pad_char ||
696 LPAD(v_underline_char , sq_len_2 , v_underline_char)
697 );
698
699 END create_quarterly_dd;
700
701 PROCEDURE validate_Party_detail
702 ( p_line_number IN NUMBER ,
703 p_record_type IN VARCHAR2,
704 p_batch_number IN NUMBER,
705 p_challan_line_num IN NUMBER,
706 p_party_slno IN NUMBER,
707 p_dh_mode IN VARCHAR2,
708 p_quart_party_code IN VARCHAR2,
709 p_party_pan IN VARCHAR2,
710 p_party_name IN VARCHAR2,
711 p_tcs_amt IN NUMBER,
712 p_surcharge_amt IN NUMBER ,
713 p_cess_amt IN NUMBER ,
714 p_party_total_tax_deducted IN NUMBER,
715 p_base_taxabale_amount IN NUMBER,
716 p_gl_date IN DATE ,
717 p_book_ent_oth IN VARCHAR2,
718 p_tcs_tax_rate IN NUMBER,
719 p_total_purchase IN NUMBER,
720 p_party_total_tax_deposit IN NUMBER,
721 p_return_code OUT NOCOPY VARCHAR2,
722 p_return_message OUT NOCOPY VARCHAR2
723 )
724 IS
725 BEGIN
726 IF p_line_number IS NULL THEN
727 p_return_message := p_return_message || ' Line Number should not be null. ' ;
728 IF lv_action <> 'V' THEN
729 goto end_of_procedure ;
730 END IF ;
731 END IF ;
732 IF p_record_type IS NULL THEN
733 p_return_message := p_return_message || ' Record Type is null. ' ;
734 IF lv_action <> 'V' THEN
735 goto end_of_procedure ;
736 END IF ;
737 END IF ;
738 IF p_batch_number IS NULL THEN
739 p_return_message := p_return_message || ' Batch Number is null. ' ;
740 IF lv_action <> 'V' THEN
741 goto end_of_procedure ;
742 END IF ;
743 END IF ;
744 IF p_challan_line_num IS NULL THEN
745 p_return_message := p_return_message || ' Challan Record Number is null. ' ;
746 IF lv_action <> 'V' THEN
747 goto end_of_procedure ;
748 END IF ;
749 END IF ;
750 IF p_party_slno IS NULL THEN
751 p_return_message := p_return_message || ' Party Detail Record Number is null. ' ;
752 IF lv_action <> 'V' THEN
753 goto end_of_procedure ;
754 END IF ;
755 END IF ;
756 IF p_dh_mode IS NULL THEN
757 p_return_message := p_return_message || ' Mode is null. ' ;
758 IF lv_action <> 'V' THEN
759 goto end_of_procedure ;
760 END IF ;
761 END IF ;
762 IF p_quart_Party_code IS NULL THEN
763 p_return_message := p_return_message || ' Deductee Party Code is null. ' ;
764 IF lv_action <> 'V' THEN
765 goto end_of_procedure ;
766 END IF ;
767 END IF ;
768 IF p_Party_pan IS NULL THEN
769 p_return_message := p_return_message || ' Deductee PAN is null. ' ;
770 IF lv_action <> 'V' THEN
771 goto end_of_procedure ;
772 END IF ;
773 /*Bug 8880543 - Added validation for PAN - Start*/
774 ELSE
775 IF (validate_alpha_numeric(p_Party_pan, length(p_Party_pan)) = 'INVALID') THEN
776 p_return_message := p_return_message || ' PAN format incorrect. The first five must be alphabets, followed by four numbers, and then followed by an alphabet.' ;
777 IF lv_action <> 'V' THEN
778 goto end_of_procedure ;
779 END IF ;
780 END IF;
781 /*Bug 8880543 - Added validation for PAN - End*/
782 END IF ;
783 IF p_Party_name IS NULL THEN
784 p_return_message := p_return_message || ' Party Name is null. ' ;
785 IF lv_action <> 'V' THEN
786 goto end_of_procedure ;
787 END IF ;
788 END IF ;
789 IF p_tcs_amt IS NULL THEN
790 p_return_message := p_return_message || ' TCS Income Tax for the Period is null. ' ;
791 IF lv_action <> 'V' THEN
792 goto end_of_procedure ;
793 END IF ;
794 END IF ;
795 IF p_surcharge_amt IS NULL THEN
796 p_return_message := p_return_message || ' TCS Surcharge is null. ' ;
797 IF lv_action <> 'V' THEN
798 goto end_of_procedure ;
799 END IF ;
800 END IF ;
801 IF p_cess_amt IS NULL THEN
802 p_return_message := p_return_message || ' TCS Cess is null. ' ;
803 IF lv_action <> 'V' THEN
804 goto end_of_procedure ;
805 END IF ;
806 END IF ;
807 IF p_party_total_tax_deducted IS NULL THEN
808 p_return_message := p_return_message || ' Total TCS is null. ' ;
809 IF lv_action <> 'V' THEN
810 goto end_of_procedure ;
811 END IF ;
812 END IF ;
813 IF p_base_taxabale_amount IS NULL THEN
814 p_return_message := p_return_message || ' Payment Amount is null. ' ;
815 IF lv_action <> 'V' THEN
816 goto end_of_procedure ;
817 END IF ;
818 END IF ;
819 IF p_gl_date IS NULL THEN
820 p_return_message := p_return_message || ' Date on which Amount Credited is null. ' ;
821 IF lv_action <> 'V' THEN
822 goto end_of_procedure ;
823 END IF ;
824 END IF ;
825 IF p_book_ent_oth IS NULL THEN
826 p_return_message := p_return_message || ' Book/Cash Entry is null. ' ;
827 IF lv_action <> 'V' THEN
828 goto end_of_procedure ;
829 END IF ;
830 END IF ;
831 IF p_tcs_tax_rate IS NULL THEN
832 p_return_message := p_return_message || ' Tcs Tax Rate Is Null. ' ;
833 IF lv_action <> 'V' THEN
834 goto end_of_procedure ;
835 END IF ;
836 END IF ;
837 IF p_total_purchase IS NULL THEN
838 p_return_message := p_return_message || ' Total Purchase Amount Is Null. ' ;
839 IF lv_action <> 'V' THEN
840 goto end_of_procedure ;
841 END IF ;
842 END IF ;
843 IF p_party_total_tax_deposit IS NULL THEN
844 p_return_message := p_return_message || ' Total Tax Deposited Is Null. ' ;
845 IF lv_action <> 'V' THEN
846 goto end_of_procedure ;
847 END IF ;
848 END IF ;
849 IF lv_action = 'V' THEN
850 goto end_of_procedure ;
851 END IF ;
852
853 <<end_of_procedure>>
854 IF p_return_message IS NOT NULL THEN
855 p_return_code := 'E';
856 p_return_message := 'Collectee Detail Error - ' || p_return_message ;
857 END IF;
858
859 END validate_Party_detail ;
860
861
862 PROCEDURE create_quart_party_dtl(
863 p_line_number IN NUMBER,
864 p_record_type IN VARCHAR2,
865 p_batch_number IN NUMBER,
866 p_dh_challan_recNo IN NUMBER,
867 p_party_slno IN NUMBER,
868 p_dh_mode IN VARCHAR2,
869 p_emp_serial_no IN VARCHAR2,
870 p_party_code IN VARCHAR2,
871 p_last_emp_pan IN VARCHAR2,
872 p_party_pan IN VARCHAR2,
873 p_last_emp_pan_refno IN VARCHAR2,
874 p_party_pan_refno IN VARCHAR2,
875 p_party_name IN VARCHAR2,
876 p_party_tcs_income_tax IN NUMBER,
877 p_party_tcs_surcharge IN NUMBER,
878 p_party_tcs_cess IN NUMBER,
879 p_party_total_tax_deducted IN NUMBER,
880 p_last_total_tax_deducted IN VARCHAR2,
881 p_party_total_tax_deposit IN NUMBER,
882 p_last_total_tax_deposit IN VARCHAR2,
883 p_total_purchase IN NUMBER,
884 p_base_taxabale_amount IN NUMBER,
885 p_gl_date IN DATE,
886 p_tcs_invoice_date IN DATE,
887 p_deposit_date IN VARCHAR2,
888 p_tcs_tax_rate IN NUMBER,
889 p_grossingUp_ind IN VARCHAR2,
890 p_book_ent_oth IN VARCHAR2,
891 p_certificate_issue_date IN VARCHAR2,
892 p_remarks1 IN VARCHAR2,
893 p_remarks2 IN VARCHAR2,
894 p_remarks3 IN VARCHAR2,
895 p_dh_recHash IN VARCHAR2,
896 p_generate_headers IN VARCHAR2
897 )
898 IS
899 BEGIN
900 IF p_generate_headers = 'N' THEN
901 UTL_FILE.PUT_LINE( jai_ap_tds_etds_pkg.v_filehandle,
902 p_line_number || v_delimeter ||
903 upper(p_record_type) || v_delimeter ||
904 p_batch_number || v_delimeter ||
905 p_dh_challan_recNo || v_delimeter ||
906 p_party_slno || v_delimeter ||
907 p_dh_mode || v_delimeter ||
908 p_emp_serial_no || v_delimeter ||
909 p_party_code || v_delimeter ||
910 p_last_emp_pan || v_delimeter ||
911 p_party_pan || v_delimeter ||
912 p_last_emp_pan_refno || v_delimeter ||
913 p_party_pan_refno || v_delimeter ||
914 p_party_name || v_delimeter ||
915 to_char( p_party_tcs_income_tax, v_format_amount) || v_delimeter ||
916 to_char( p_party_tcs_surcharge, v_format_amount) || v_delimeter ||
917 to_char( p_party_tcs_cess, v_format_amount) || v_delimeter ||
918 to_char( p_party_total_tax_deducted, v_format_amount) || v_delimeter ||
919 p_last_total_tax_deducted || v_delimeter ||
920 to_char( p_party_total_tax_deposit, v_format_amount) || v_delimeter ||
921 p_last_total_tax_deposit || v_delimeter ||
922 TO_CHAR(p_total_purchase,V_FORMAT_AMOUNT) || v_delimeter ||
923 to_char( p_base_taxabale_amount, v_format_amount) || v_delimeter ||
924 to_char(p_gl_date,'ddmmyyyy') || v_delimeter ||
925 to_char(p_tcs_invoice_date,'ddmmyyyy') || v_delimeter ||
926 p_deposit_date || v_delimeter ||
927 to_char(p_tcs_tax_rate,'FM99D0000') || v_delimeter ||
928 p_grossingUp_ind || v_delimeter ||
929 p_book_ent_oth || v_delimeter ||
930 p_certificate_issue_date || v_delimeter ||
931 p_remarks1 || v_delimeter ||
932 p_remarks2 || v_delimeter ||
933 p_remarks3 || v_delimeter ||
934 p_dh_recHash
935 );
936 ELSE
937 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
938 LPAD(p_line_number , sq_len_9, v_quart_pad) || v_pad_char ||
939 LPAD(upper(p_record_type) , sq_len_2, v_quart_pad) || v_pad_char ||
940 LPAD(p_batch_number , sq_len_9, v_quart_pad) || v_pad_char ||
941 LPAD(p_dh_challan_recNo , sq_len_9, v_quart_pad) || v_pad_char ||
942 LPAD(p_party_slno , sq_len_9, v_quart_pad) || v_pad_char ||
943 LPAD(p_dh_mode , sq_len_1, v_quart_pad) || v_pad_char ||
944 LPAD(NVL(p_emp_serial_no,v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
945 LPAD(p_party_code , sq_len_1, v_quart_pad) || v_pad_char ||
946 LPAD(NVL(p_last_emp_pan, v_q_noval_filler), sq_len_2, v_quart_pad) || v_pad_char ||
947 LPAD(p_party_pan , sq_len_10, v_quart_pad) || v_pad_char ||
948 LPAD(NVL(p_last_emp_pan_refno,v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
949 LPAD(NVL(p_party_pan_refno,v_q_null_filler) , sq_len_10, v_quart_pad) || v_pad_char ||
950 LPAD(NVL(p_party_name, v_q_null_filler) , sq_len_75, v_quart_pad) || v_pad_char ||
951 LPAD(to_char( p_party_tcs_income_tax, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
952 LPAD(to_char( p_party_tcs_surcharge, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
953 LPAD(to_char( p_party_tcs_cess, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
954 LPAD(to_char( p_party_total_tax_deducted, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
955 LPAD(NVL(p_last_total_tax_deducted, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
956 LPAD(to_char( p_party_total_tax_deposit, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
957 LPAD(NVL(p_last_total_tax_deposit, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
958 LPAD(TO_CHAR(p_total_purchase, v_FORMAT_AMOUNT) , sq_len_15, v_quart_pad) || v_pad_char ||
959 LPAD(to_char( p_base_taxabale_amount, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
960 LPAD(to_char(p_gl_date,'ddmmyyyy') , sq_len_8, v_quart_pad) || v_pad_char ||
961 LPAD(NVL(to_char(p_tcs_invoice_date,'ddmmyyyy'),G_DATE_DUMMY) , sq_len_8, v_quart_pad) || v_pad_char || -- change later
962 LPAD(NVL(p_deposit_date, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
963 LPAD( to_char(p_tcs_tax_rate,v_format_rate), sq_len_7, v_quart_pad) || v_pad_char ||
964 LPAD(NVL(p_grossingUp_ind, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
965 LPAD(p_book_ent_oth , sq_len_1, v_quart_pad) || v_pad_char ||
966 LPAD(NVL(p_certificate_issue_date,v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
967 LPAD(NVL(p_remarks1,v_q_null_filler) , sq_len_75, v_quart_pad) || v_pad_char ||
968 LPAD(NVL(p_remarks2,v_q_noval_filler) , sq_len_75, v_quart_pad) || v_pad_char ||
969 LPAD(NVL(p_remarks3,v_q_noval_filler) , sq_len_14, v_quart_pad) || v_pad_char ||
970 LPAD(NVL(p_dh_recHash,v_q_noval_filler) , sq_len_2, v_quart_pad)
971 );
972 END IF ;
973
974 END create_quart_party_dtl;
975
976 PROCEDURE validate_file_header
977 ( p_line_number IN NUMBER ,
978 p_record_type IN VARCHAR2,
979 p_quartfile_type IN VARCHAR2,
980 p_upload_type IN VARCHAR2,
981 p_file_creation_date IN DATE,
982 p_file_sequence_number IN NUMBER,
983 p_uploader_type IN VARCHAR2,
984 p_collector_tan IN VARCHAR2,
985 p_number_of_batches IN NUMBER,
986 p_period IN VARCHAR2,
987 p_start_date IN DATE,
988 p_end_date IN DATE,
989 p_fin_year IN NUMBER,
990 p_return_prep_util IN VARCHAR2, /*Bug 8880543 - Added for eTCS/eTDS FVU Changes*/
991 p_return_code OUT NOCOPY VARCHAR2,
992 p_return_message OUT NOCOPY VARCHAR2
993 )
994 IS
995
996 lv_q1_start_date VARCHAR2(11) ;
997 lv_q1_end_date VARCHAR2(11) ;
998 lv_q2_start_date VARCHAR2(11) ;
999 lv_q2_end_date VARCHAR2(11) ;
1000 lv_q3_start_date VARCHAR2(11) ;
1001 lv_q3_end_date VARCHAR2(11) ;
1002 lv_q4_start_date VARCHAR2(11) ;
1003 lv_q4_end_date VARCHAR2(11) ;
1004 ln_fin_year NUMBER ;
1005
1006 BEGIN
1007
1008 IF p_line_number IS NULL THEN
1009 p_return_message := p_return_message || ' Line Number should not be null. ' ;
1010 IF lv_action <> 'V' THEN
1011 goto end_of_procedure ;
1012 END IF ;
1013 END IF;
1014 IF p_record_type IS NULL THEN
1015 p_return_message := p_return_message || ' Record Type is null. ' ;
1016 IF lv_action <> 'V' THEN
1017 goto end_of_procedure ;
1018 END IF ;
1019 END IF;
1020 IF p_quartfile_type IS NULL THEN
1021 p_return_message := p_return_message || ' File Type is null. ' ;
1022 IF lv_action <> 'V' THEN
1023 goto end_of_procedure ;
1024 END IF ;
1025 END IF;
1026 IF p_upload_type IS NULL THEN
1027 p_return_message := p_return_message || ' Upload Type is null. ' ;
1028 IF lv_action <> 'V' THEN
1029 goto end_of_procedure ;
1030 END IF ;
1031 END IF;
1032 IF p_file_creation_date IS NULL THEN
1033 p_return_message := p_return_message || ' File Creation Date is null. ' ;
1034 IF lv_action <> 'V' THEN
1035 goto end_of_procedure ;
1036 END IF ;
1037 END IF;
1038 IF p_file_sequence_number IS NULL THEN
1039 p_return_message := p_return_message || ' File Sequence No is null. ' ;
1040 IF lv_action <> 'V' THEN
1041 goto end_of_procedure ;
1042 END IF ;
1043 END IF;
1044 IF p_uploader_type IS NULL THEN
1045 p_return_message := p_return_message || ' Upload Type is null. ' ;
1046 IF lv_action <> 'V' THEN
1047 goto end_of_procedure ;
1048 END IF ;
1049 END IF;
1050 IF p_collector_tan IS NULL THEN
1051 p_return_message := p_return_message || ' Collector TAN is null. ' ;
1052 IF lv_action <> 'V' THEN
1053 goto end_of_procedure ;
1054 END IF ;
1055 END IF;
1056 IF p_number_of_batches IS NULL THEN
1057 p_return_message := p_return_message || ' Batch Count is null. ' ;
1058 IF lv_action <> 'V' THEN
1059 goto end_of_procedure ;
1060 END IF ;
1061 END IF;
1062 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - Start*/
1063 IF p_return_prep_util IS NULL THEN
1064 p_return_message := p_return_message || ' Return Preperation Utility is null. ' ;
1065 IF lv_action <> 'V' THEN
1066 goto end_of_procedure ;
1067 END IF ;
1068 END IF;
1069 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - End*/
1070
1071 -- code to validate whether the given challan start and end dates fall under the specified period.
1072 IF p_period = 'Q1' THEN
1073 lv_q1_start_date := '01/04/' || p_fin_year ;
1074 lv_q1_end_date := '30/06/' || p_fin_year ;
1075
1076 IF not ( p_start_date >= to_date(lv_q1_start_date,'DD/MM/YYYY') and p_end_date <= to_date(lv_q1_end_date,'DD/MM/YYYY') ) THEN
1077 p_return_message := ' The given Challan Start and end dates do not fall under the specified Quarter and Year. ' ;
1078 goto end_of_procedure ;
1079 END IF ;
1080 ELSIF p_period = 'Q2' THEN
1081 lv_q2_start_date := '01/07/' || p_fin_year;
1082 lv_q2_end_date := '30/09/' || p_fin_year;
1083
1084 IF not ( p_start_date >= to_date(lv_q2_start_date,'DD/MM/YYYY') and p_end_date <= to_date(lv_q2_end_date,'DD/MM/YYYY') ) THEN
1085 p_return_message := ' The given Challan Start and end dates do not fall under the specified Quarter and Year. ' ;
1086 goto end_of_procedure ;
1087 END IF ;
1088 ELSIF p_period = 'Q3' THEN
1089 lv_q3_start_date := '01/10/' || p_fin_year;
1090 lv_q3_end_date := '31/12/' || p_fin_year;
1091
1092 IF not ( p_start_date >= to_date(lv_q3_start_date,'DD/MM/YYYY') and p_end_date <= to_date(lv_q3_end_date,'DD/MM/YYYY') ) THEN
1093 p_return_message := ' The given Challan Start and end dates do not fall under the specified Quarter and Year. ' ;
1094 goto end_of_procedure ;
1095 END IF ;
1096 ELSIF p_period = 'Q4' THEN
1097 ln_fin_year := p_fin_year + 1 ;
1098 lv_q4_start_date := '01/01/' || ln_fin_year;
1099 lv_q4_end_date := '31/03/' || ln_fin_year;
1100
1101 IF not ( p_start_date >= to_date(lv_q4_start_date,'DD/MM/YYYY') and p_end_date <= to_date(lv_q4_end_date,'DD/MM/YYYY') ) THEN
1102 p_return_message := ' The given Challan Start and end dates do not fall under the specified Quarter and Year. ' ;
1103 goto end_of_procedure ;
1104 END IF ;
1105 END IF ;
1106
1107 IF lv_action = 'V' THEN
1108 goto end_of_procedure ;
1109 END IF ;
1110
1111 <<end_of_procedure>>
1112 IF p_return_message IS NOT NULL THEN
1113 FND_FILE.put_line(FND_FILE.log,' p_return_message ' || p_return_message ) ;
1114 p_return_code := 'E';
1115 p_return_message := 'File Header Error - ' || 'Line No : ' || p_line_number || '. ' || p_return_message ;
1116 END IF;
1117
1118 END validate_file_header;
1119
1120 PROCEDURE create_quarterly_file_header(
1121 p_line_number IN NUMBER,
1122 p_record_type IN VARCHAR2,
1123 p_file_type IN VARCHAR2,
1124 p_upload_type IN VARCHAR2,
1125 p_file_creation_date IN DATE,
1126 p_file_sequence_number IN NUMBER,
1127 p_uploader_type IN VARCHAR2,
1128 p_collector_tan IN VARCHAR2,
1129 p_number_of_batches IN NUMBER,
1130 p_return_prep_util IN VARCHAR2, /*Bug 8880543 - Added for eTDS/eTCS FVU Changes*/
1131 p_fh_recordHash IN VARCHAR2,
1132 p_fh_fvuVersion IN VARCHAR2,
1133 p_fh_fileHash IN VARCHAR2,
1134 p_fh_samVersion IN VARCHAR2,
1135 p_fh_samHash IN VARCHAR2,
1136 p_fh_scmVersion IN VARCHAR2,
1137 p_fh_scmHash IN VARCHAR2,
1138 p_fh_consFileHash IN VARCHAR2,
1139 p_generate_headers IN VARCHAR2
1140 ) IS
1141 BEGIN
1142 IF p_generate_headers = 'N' THEN
1143
1144 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
1145 p_line_number || v_delimeter||
1146 upper(p_record_type) || v_delimeter||
1147 upper(p_file_type) || v_delimeter||
1148 upper(p_upload_type) || v_delimeter||
1149 to_char(p_file_creation_date,'ddmmyyyy') || v_delimeter||
1150 p_file_sequence_number || v_delimeter||
1151 upper(p_uploader_type) || v_delimeter||
1152 p_collector_tan || v_delimeter||
1153 p_number_of_batches || v_delimeter||
1154 p_return_prep_util || v_delimeter|| /*Bug 8880543 - Added for eTDS/eTCS FVU Changes*/
1155 p_fh_recordHash || v_delimeter||
1156 p_fh_fvuVersion || v_delimeter||
1157 p_fh_fileHash || v_delimeter||
1158 p_fh_samVersion || v_delimeter||
1159 p_fh_samHash || v_delimeter||
1160 p_fh_scmVersion || v_delimeter||
1161 p_fh_scmHash || v_delimeter||
1162 p_fh_consFileHash); /*FVU 3.4*/
1163 ELSE
1164 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
1165 LPAD(p_line_number , sq_len_9, v_quart_pad) || v_pad_char ||
1166 LPAD( upper(p_record_type) , sq_len_2, v_quart_pad) || v_pad_char ||
1167 LPAD(upper(p_file_type) , sq_len_4, v_quart_pad) || v_pad_char ||
1168 LPAD(upper(p_upload_type) , sq_len_2, v_quart_pad) || v_pad_char ||
1169 LPAD(to_char(p_file_creation_date,'ddmmyyyy') , sq_len_8, v_quart_pad) || v_pad_char ||
1170 LPAD(p_file_sequence_number , sq_len_9, v_quart_pad) || v_pad_char ||
1171 LPAD(upper(p_uploader_type), sq_len_1, v_quart_pad) || v_pad_char ||
1172 LPAD(upper(p_collector_tan) , sq_len_10, v_quart_pad) || v_pad_char ||
1173 LPAD(p_number_of_batches , sq_len_9, v_quart_pad) || v_pad_char ||
1174 LPAD(p_return_prep_util , sq_len_75, v_quart_pad) || v_pad_char || /*Bug 8880543 - Added for eTDS/eTCS FVU Changes*/
1175 LPAD(NVL(p_fh_recordHash,v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1176 LPAD(NVL(p_fh_fvuVersion, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1177 LPAD(NVL(p_fh_fileHash, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1178 LPAD(NVL(p_fh_samVersion, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1179 LPAD(NVL(p_fh_samHash, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1180 LPAD(NVL(p_fh_scmVersion, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1181 LPAD(NVL(p_fh_scmHash, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
1182 LPAD(NVL(p_fh_consFileHash, v_q_noval_filler) , sq_len_2, v_quart_pad) /*FVU 3.4*/
1183 ) ;
1184
1185 END IF ;
1186
1187
1188 END create_quarterly_file_header ;
1189
1190 PROCEDURE validate_batch_header
1191 ( p_line_number IN NUMBER,
1192 p_record_type IN VARCHAR2,
1193 p_batch_number IN NUMBER,
1194 p_challan_cnt IN NUMBER,
1195 p_quart_form_number IN VARCHAR2,
1196 p_collector_tan IN VARCHAR2,
1197 p_pan_of_tan IN VARCHAR2, /*Bug 8880543 - Added for eTDS/eTCS FVU Changes*/
1198 p_assessment_year IN NUMBER,
1199 p_financial_year IN NUMBER,
1200 p_collector_name IN VARCHAR2,
1201 p_tan_address1 IN VARCHAR2,
1202 p_tan_state_code IN NUMBER,
1203 p_tan_pin IN NUMBER,
1204 p_collector_type IN VARCHAR2, /*Bug 8880543 - Modified Collector Status to Collector Type*/
1205 p_addrChangedSinceLastReturn IN VARCHAR2,
1206 p_personNameRespForCollection IN VARCHAR2,
1207 p_personDesgnRespForCollection IN VARCHAR2,
1208 --p_RespPersAddress IN VARCHAR2,
1209 p_RespPers_flat_no IN VARCHAR2 , -- Bug 12597773
1210 p_RespPers_prem_bldg IN VARCHAR2 , -- Bug 12597773
1211 p_RespPers_rd_st_lane IN VARCHAR2 , -- Bug 12597773
1212 p_RespPers_area_loc IN VARCHAR2 , -- Bug 12597773
1213 p_RespPers_tn_cty_dt IN VARCHAR2 , -- Bug 12597773
1214 p_RespPersState IN NUMBER,
1215 p_RespPersPin IN NUMBER,
1216 p_RespPersAddrChange IN VARCHAR2,
1217 p_totTaxCollectedAsPerParty IN NUMBER,
1218 p_ao_approval IN VARCHAR2,
1219 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - Start*/
1220 p_state_name IN VARCHAR2,
1221 p_pao_code IN VARCHAR2,
1222 p_ddo_code IN VARCHAR2,
1223 p_ministry_name IN VARCHAR2,
1224 p_pao_registration_no IN NUMBER,
1225 p_ddo_registration_no IN VARCHAR2,
1226 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - End*/
1227 /*Bug 10238421- Start*/
1228 p_RespPerson_mobile IN VARCHAR2,
1229 p_collector_phone_no IN VARCHAR2,
1230 p_resp_person_phone_no IN VARCHAR2,
1231 /*Bug 10238421- End*/
1232 p_return_code OUT NOCOPY VARCHAR2,
1233 p_return_message OUT NOCOPY VARCHAR2
1234 )
1235 IS
1236 BEGIN
1237 IF p_line_number IS NULL THEN
1238 p_return_message := p_return_message || ' Line Number should not be null. ' ;
1239 IF lv_action <> 'V' THEN
1240 goto end_of_procedure ;
1241 END IF ;
1242 END IF ;
1243 IF p_record_type IS NULL THEN
1244 p_return_message := p_return_message || ' Record Type is null. ' ;
1245 IF lv_action <> 'V' THEN
1246 goto end_of_procedure ;
1247 END IF ;
1248 END IF ;
1249 IF p_batch_number IS NULL THEN
1250 p_return_message := p_return_message || ' Batch Number is null. ' ;
1251 IF lv_action <> 'V' THEN
1252 goto end_of_procedure ;
1253 END IF ;
1254 END IF ;
1255 IF p_challan_cnt IS NULL THEN
1256 p_return_message := p_return_message || ' Record Count is null. ' ;
1257 IF lv_action <> 'V' THEN
1258 goto end_of_procedure ;
1259 END IF ;
1260 END IF ;
1261 IF p_quart_form_number IS NULL THEN
1262 p_return_message := p_return_message || ' Form Number is null. ' ;
1263 IF lv_action <> 'V' THEN
1264 goto end_of_procedure ;
1265 END IF ;
1266 END IF ;
1267 IF p_collector_tan IS NULL THEN
1268 p_return_message := p_return_message || ' Collector TAN is null. ' ;
1269 IF lv_action <> 'V' THEN
1270 goto end_of_procedure ;
1271 END IF ;
1272 END IF ;
1273 /*Bug 8880543 - Added for PAN Number Validation - Start*/
1274
1275 IF p_pan_of_tan IS NULL THEN
1276 p_return_message := p_return_message || ' Collector PAN is null. ' ;
1277 IF lv_action <> 'V' THEN
1278 goto end_of_procedure ;
1279 END IF ;
1280 ELSE
1281 IF (validate_alpha_numeric(p_pan_of_tan, length(p_pan_of_tan)) = 'INVALID') THEN
1282 p_return_message := p_return_message || ' PAN format incorrect. The first five must be alphabets, followed by four numbers, and then followed by an alphabet.' ;
1283 IF lv_action <> 'V' THEN
1284 goto end_of_procedure ;
1285 END IF ;
1286 END IF;
1287 END IF ;
1288
1289 /*Bug 8880543 - Added for PAN Number Validation - End*/
1290 IF p_assessment_year IS NULL THEN
1291 p_return_message := p_return_message || ' Assessment Year is null. ' ;
1292 IF lv_action <> 'V' THEN
1293 goto end_of_procedure ;
1294 END IF ;
1295 END IF ;
1296 IF p_financial_year IS NULL THEN
1297 p_return_message := p_return_message || ' Financial Year is null. ' ;
1298 IF lv_action <> 'V' THEN
1299 goto end_of_procedure ;
1300 END IF ;
1301 END IF ;
1302 IF p_collector_name IS NULL THEN
1303 p_return_message := p_return_message || ' Collector Name is null. ' ;
1304 IF lv_action <> 'V' THEN
1305 goto end_of_procedure ;
1306 END IF ;
1307 END IF ;
1308 IF p_tan_address1 IS NULL THEN
1309 p_return_message := p_return_message || ' Collector Address is null. ' ;
1310 IF lv_action <> 'V' THEN
1311 goto end_of_procedure ;
1312 END IF ;
1313 END IF ;
1314 IF p_tan_state_code IS NULL THEN
1315 p_return_message := p_return_message || ' Collector State is null. ' ;
1316 IF lv_action <> 'V' THEN
1317 goto end_of_procedure ;
1318 END IF ;
1319 END IF ;
1320 IF p_tan_pin IS NULL THEN
1321 p_return_message := p_return_message || ' Collector Pin is null. ' ;
1322 IF lv_action <> 'V' THEN
1323 goto end_of_procedure ;
1324 END IF ;
1325 END IF ;
1326 IF p_collector_type IS NULL THEN /*Bug 8880543 - Modified Collector Status to Collector Type*/
1327 p_return_message := p_return_message || ' Collector Type is null. ' ;
1328 IF lv_action <> 'V' THEN
1329 goto end_of_procedure ;
1330 END IF ;
1331 END IF ;
1332 IF p_addrChangedSinceLastReturn IS NULL THEN
1333 p_return_message := p_return_message || ' Field Collector Address Changed Since last year is null. ' ;
1334 IF lv_action <> 'V' THEN
1335 goto end_of_procedure ;
1336 END IF ;
1337 END IF ;
1338 IF p_personNameRespForCollection IS NULL THEN
1339 p_return_message := p_return_message || ' Person Responsible For Collection is null. ' ;
1340 IF lv_action <> 'V' THEN
1341 goto end_of_procedure ;
1342 END IF ;
1343 END IF ;
1344 IF p_personDesgnRespForCollection IS NULL THEN
1345 p_return_message := p_return_message || ' Designation of Responsible Person is null. ' ;
1346 IF lv_action <> 'V' THEN
1347 goto end_of_procedure ;
1348 END IF ;
1349 END IF ;
1350 /*IF p_RespPersAddress IS NULL THEN
1351 p_return_message := p_return_message || ' Address of Responsible Person is null. ' ;
1352 IF lv_action <> 'V' THEN
1353 goto end_of_procedure ;
1354 END IF ;
1355 END IF ;*/
1356 -- Bug 12597773. Added by Avanija
1357 IF p_RespPers_flat_no IS NULL THEN
1358 p_return_message := p_return_message || ' Flat No. of Responsible Person is null. ' ;
1359 IF lv_action <> 'V' THEN
1360 goto end_of_procedure ;
1361 END IF ;
1362 END IF ;
1363
1364 IF p_RespPers_prem_bldg IS NULL THEN
1365 p_return_message := p_return_message || ' Name of the premises/bldg of Responsible Person is null. ' ;
1366 IF lv_action <> 'V' THEN
1367 goto end_of_procedure ;
1368 END IF ;
1369 END IF ;
1370
1371 IF p_RespPers_rd_st_lane IS NULL THEN
1372 p_return_message := p_return_message || ' Road/Street/Lane of Responsible Person is null. ' ;
1373 IF lv_action <> 'V' THEN
1374 goto end_of_procedure ;
1375 END IF ;
1376 END IF ;
1377
1378 IF p_RespPers_area_loc IS NULL THEN
1379 p_return_message := p_return_message || ' Area/Location of Responsible Person is null. ' ;
1380 IF lv_action <> 'V' THEN
1381 goto end_of_procedure ;
1382 END IF ;
1383 END IF ;
1384
1385 IF p_RespPers_tn_cty_dt IS NULL THEN
1386 p_return_message := p_return_message || ' Town/City/District of Responsible Person is null. ' ;
1387 IF lv_action <> 'V' THEN
1388 goto end_of_procedure ;
1389 END IF ;
1390 END IF ;
1391 -- End for bug 12597773
1392 IF p_RespPersState IS NULL THEN
1393 p_return_message := p_return_message || ' State of Responsible Person is null. ' ;
1394 IF lv_action <> 'V' THEN
1395 goto end_of_procedure ;
1396 END IF ;
1397 END IF ;
1398 IF p_RespPersPin IS NULL THEN
1399 p_return_message := p_return_message || ' Pin of Responsible Person is null. ' ;
1400 IF lv_action <> 'V' THEN
1401 goto end_of_procedure ;
1402 END IF ;
1403 END IF ;
1404 IF p_RespPersAddrChange IS NULL THEN
1405 p_return_message := p_return_message || ' Field ''Address of Responsible Person has Changed'' is null. ' ;
1406 IF lv_action <> 'V' THEN
1407 goto end_of_procedure ;
1408 END IF ;
1409 END IF ;
1410 /*
1411 IF p_totTaxCollectedAsPerParty IS NULL THEN
1412 p_return_message := p_return_message || ' Total Deposit Amount as per Challan is null. ' ;
1413 IF lv_action <> 'V' THEN
1414 goto end_of_procedure ;
1415 END IF ;
1416 END IF ;
1417 */
1418 IF p_ao_approval IS NULL THEN
1419 p_return_message := p_return_message || ' AO Approval is null. ' ;
1420 IF lv_action <> 'V' THEN
1421 goto end_of_procedure ;
1422 END IF ;
1423 END IF ;
1424
1425 /*Bug 8880543 - Validation for eTDs/eTCS FVU changes - Start*/
1426
1427 IF p_collector_type in ('S', 'E', 'H', 'N') THEN
1428 IF p_state_name IS NULL THEN
1429 p_return_message := p_return_message || ' State is required when Deductor Type is S/E/H/N. ' ;
1430 IF lv_action <> 'V' THEN
1431 goto end_of_procedure ;
1432 END IF ;
1433 END IF ;
1434 END IF;
1435
1436 IF p_collector_type in ('A') THEN
1437 IF p_pao_code IS NULL THEN
1438 p_return_message := p_return_message || ' PAO Code is required when Deductor Type is A. ' ;
1439 IF lv_action <> 'V' THEN
1440 goto end_of_procedure ;
1441 END IF ;
1442 END IF ;
1443 IF p_ddo_code IS NULL THEN
1444 p_return_message := p_return_message || ' DDO Code is required when Deductor Type is A. ' ;
1445 IF lv_action <> 'V' THEN
1446 goto end_of_procedure ;
1447 END IF ;
1448 END IF ;
1449 END IF;
1450
1451 IF p_collector_type in ('A', 'D', 'G') THEN
1452 IF p_ministry_name IS NULL THEN
1453 p_return_message := p_return_message || ' Ministry Name is required when Deductor Type is A/D/G. ' ;
1454 IF lv_action <> 'V' THEN
1455 goto end_of_procedure ;
1456 END IF ;
1457 END IF ;
1458 END IF;
1459
1460 /*Bug 8880543 - Validation for eTDs/eTCS FVU changes - End*/
1461
1462 /*Bug 10238421 - Start*/
1463 IF p_RespPerson_mobile IS NULL AND
1464 p_collector_phone_no IS NULL AND
1465 p_resp_person_phone_no IS NULL
1466 THEN
1467 p_return_message := p_return_message || ' Either mobile no. should be provided or Telephone no. and STD code of collector or responsible person should be provided. ';
1468 IF lv_action <> 'V' THEN
1469 goto end_of_procedure ;
1470 END IF ;
1471 END IF;
1472 /*Bug 10238421 - End*/
1473
1474 IF lv_action = 'V' THEN
1475 goto end_of_procedure ;
1476 END IF ;
1477
1478 <<end_of_procedure>>
1479 IF p_return_message IS NOT NULL THEN
1480 p_return_code := 'E';
1481 p_return_message := 'Batch Header Error - ' || 'Line No : ' || p_line_number || '. ' || p_return_message ;
1482 END IF;
1483
1484 END validate_batch_header ;
1485
1486 PROCEDURE create_quarterly_batch_header(
1487 p_line_number IN NUMBER,
1488 p_record_type IN VARCHAR2,
1489 p_batch_number IN NUMBER,
1490 p_challan_count IN NUMBER,
1491 p_form_number IN CHAR,
1492 p_trn_type IN VARCHAR2,
1493 p_batchUpd IN VARCHAR2,
1494 p_org_token_no IN VARCHAR2, /*Bug 10238421*/
1495 p_prev_token_no IN VARCHAR2, /*Bug 10238421*/
1496 p_token_no IN VARCHAR2 , /*Bug 10238421*/
1497 p_token_date IN VARCHAR2 , /*Bug 10238421*/
1498 p_collector_last_tan IN VARCHAR2,
1499 p_collector_tan IN VARCHAR2,
1500 p_receipt_number IN VARCHAR2, /*Bug 10238421*/
1501 p_collector_pan IN VARCHAR2,
1502 p_assessment_year IN NUMBER,
1503 p_financial_year IN NUMBER,
1504 p_period IN VARCHAR2,
1505 p_collector_name IN VARCHAR2,
1506 p_collector_branch IN VARCHAR2,
1507 p_tan_address1 IN VARCHAR2,
1508 p_tan_address2 IN VARCHAR2,
1509 p_tan_address3 IN VARCHAR2,
1510 p_tan_address4 IN VARCHAR2,
1511 p_tan_address5 IN VARCHAR2,
1512 p_tan_state_code IN NUMBER,
1513 p_tan_pin IN NUMBER,
1514 p_collector_email IN VARCHAR2,
1515 p_collector_stdCode IN NUMBER,
1516 p_collector_phoneNo IN NUMBER,
1517 p_addrChangedSinceLastReturn IN VARCHAR2,
1518 p_type_of_collector IN VARCHAR2, /*Bug 8880543 - Modified Status of Collector to Type of Collector*/
1519 p_pers_resp_for_collection IN VARCHAR2,
1520 p_RespPerson_designation IN VARCHAR2,
1521 p_RespPerson_address1 IN VARCHAR2,
1522 p_RespPerson_address2 IN VARCHAR2,
1523 p_RespPerson_address3 IN VARCHAR2,
1524 p_RespPerson_address4 IN VARCHAR2,
1525 p_RespPerson_address5 IN VARCHAR2,
1526 p_RespPerson_state IN VARCHAR2,
1527 p_RespPerson_pin IN NUMBER,
1528 p_RespPerson_email IN VARCHAR2,
1529 p_RespPerson_mobile IN VARCHAR2, /*Bug 10238421*/
1530 p_RespPerson_stdCode IN NUMBER,
1531 p_RespPerson_phoneNo IN NUMBER,
1532 p_RespPerson_addressChange IN VARCHAR2,
1533 p_totTaxcollectedAsPerChallan IN NUMBER,
1534 p_unmatch_challan_cnt IN VARCHAR2, /*Bug 10238421*/
1535 p_salaryRecords_count IN VARCHAR2,
1536 p_gross_total IN VARCHAR2,
1537 p_ao_approval IN VARCHAR2,
1538 p_ao_approval_number IN VARCHAR2,
1539 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - Start*/
1540 p_last_collector_type IN VARCHAR2,
1541 p_state_name IN VARCHAR2,
1542 p_pao_code IN VARCHAR2,
1543 p_ddo_code IN VARCHAR2,
1544 p_ministry_name IN VARCHAR2,
1545 p_ministry_name_other IN VARCHAR2,
1546 p_filler2 IN VARCHAR2,
1547 p_pao_registration_no IN NUMBER,
1548 p_ddo_registration_no IN VARCHAR2,
1549 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - End*/
1550 p_recHash IN VARCHAR2,
1551 p_generate_headers IN VARCHAR2)
1552 IS
1553 BEGIN
1554
1555 IF p_generate_headers = 'N' THEN
1556 UTL_FILE.PUT_LINE( jai_ap_tds_etds_pkg.v_filehandle, p_line_number || v_delimeter||
1557 upper(p_record_type) || v_delimeter||
1558 p_batch_number || v_delimeter||
1559 p_challan_count || v_delimeter||
1560 upper(p_form_number) || v_delimeter||
1561 p_trn_type || v_delimeter||
1562 p_batchUpd || v_delimeter||
1563 p_org_token_no || v_delimeter|| /*Bug 10238421*/
1564 p_prev_token_no || v_delimeter|| /*Bug 10238421*/
1565 p_token_no || v_delimeter|| /*Bug 10238421*/
1566 p_token_date || v_delimeter|| /*Bug 10238421*/
1567 p_collector_last_tan || v_delimeter||
1568 upper(p_collector_tan) || v_delimeter||
1569 p_receipt_number || v_delimeter|| /*Bug 10238421*/
1570 p_collector_pan || v_delimeter||
1571 p_assessment_year || v_delimeter||
1572 p_financial_year || v_delimeter||
1573 p_period || v_delimeter||
1574 p_collector_name || v_delimeter||
1575 p_collector_branch || v_delimeter||
1576 p_tan_address1 || v_delimeter||
1577 p_tan_address2 || v_delimeter||
1578 p_tan_address3 || v_delimeter||
1579 p_tan_address4 || v_delimeter||
1580 p_tan_address5 || v_delimeter||
1581 p_tan_state_code || v_delimeter||
1582 p_tan_pin || v_delimeter||
1583 p_collector_email || v_delimeter||
1584 p_collector_stdCode || v_delimeter||
1585 p_collector_phoneNo || v_delimeter||
1586 p_addrChangedSinceLastReturn || v_delimeter||
1587 p_type_of_collector || v_delimeter|| /*Bug 8880543 - Changed Collector Status to Collector Type*/
1588 p_pers_resp_for_collection || v_delimeter||
1589 p_RespPerson_designation || v_delimeter||
1590 p_RespPerson_address1 || v_delimeter||
1591 p_RespPerson_address2 || v_delimeter||
1592 p_RespPerson_address3 || v_delimeter||
1593 p_RespPerson_address4 || v_delimeter||
1594 p_RespPerson_address5 || v_delimeter||
1595 p_RespPerson_state || v_delimeter||
1596 p_RespPerson_pin || v_delimeter||
1597 p_RespPerson_email || v_delimeter||
1598 p_RespPerson_mobile || v_delimeter|| /*Bug 10238421*/
1599 p_RespPerson_stdCode || v_delimeter||
1600 p_RespPerson_phoneNo || v_delimeter||
1601 p_RespPerson_addressChange || v_delimeter||
1602 to_char(p_totTaxcollectedAsPerChallan,v_format_amount) || v_delimeter||
1603 p_unmatch_challan_cnt || v_delimeter|| /*Bug 10238421*/
1604 p_salaryRecords_count || v_delimeter||
1605 p_gross_total || v_delimeter||
1606 upper(p_ao_approval) || v_delimeter||
1607 p_ao_approval_number || v_delimeter||
1608 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - Start*/
1609 p_last_collector_type || v_delimeter||
1610 p_state_name || v_delimeter||
1611 p_pao_code || v_delimeter||
1612 p_ddo_code || v_delimeter||
1613 p_ministry_name || v_delimeter||
1614 p_ministry_name_other || v_delimeter||
1615 p_filler2 || v_delimeter||
1616 to_char(p_pao_registration_no) || v_delimeter||
1617 p_ddo_registration_no || v_delimeter||
1618 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - End*/
1619 p_recHash ) ;
1620 ELSE
1621 UTL_FILE.PUT_LINE( jai_ap_tds_etds_pkg.v_filehandle,
1622 LPAD(p_line_number, sq_len_9 , v_quart_pad) || v_pad_char ||
1623 LPAD(upper(p_record_type) , sq_len_2 , v_quart_pad) || v_pad_char ||
1624 LPAD(p_batch_number, sq_len_9 , v_quart_pad) || v_pad_char ||
1625 LPAD(p_challan_count, sq_len_9 , v_quart_pad) || v_pad_char ||
1626 LPAD(upper(p_form_number), sq_len_4 , v_quart_pad) || v_pad_char ||
1627 LPAD(NVL(p_trn_type,v_q_noval_filler ), sq_len_2 , v_quart_pad) || v_pad_char ||
1628 LPAD(NVL(p_batchUpd,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1629 LPAD(NVL(p_org_token_no,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char || /*Bug 10238421*/
1630 LPAD(NVL(p_prev_token_no,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char || /*Bug 10238421*/
1631 LPAD(NVL(p_token_no,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char || /*Bug 10238421*/
1632 LPAD(NVL(p_token_date,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char || /*Bug 10238421*/
1633 LPAD(NVL(p_collector_last_tan,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1634 LPAD(upper(p_collector_tan), sq_len_10 , v_quart_pad) || v_pad_char ||
1635 LPAD(NVL(p_receipt_number,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char || /*Bug 10238421*/
1636 LPAD(NVL(p_collector_pan,v_q_null_filler ) , sq_len_10 , v_quart_pad) || v_pad_char ||
1637 LPAD(p_assessment_year , sq_len_6 , v_quart_pad) || v_pad_char ||
1638 LPAD(p_financial_year , sq_len_6 , v_quart_pad) || v_pad_char ||
1639 LPAD(p_period , sq_len_2 , v_quart_pad) || v_pad_char ||
1640 LPAD(p_collector_name, sq_len_75 , v_quart_pad) || v_pad_char ||
1641 LPAD(NVL(p_collector_branch ,v_q_null_filler ), sq_len_75 , v_quart_pad) || v_pad_char ||
1642 LPAD(p_tan_address1, sq_len_25 , v_quart_pad) || v_pad_char ||
1643 LPAD(NVL(p_tan_address2,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1644 LPAD(NVL(p_tan_address3,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1645 LPAD(NVL(p_tan_address4,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1646 LPAD(NVL(p_tan_address5,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1647 LPAD(p_tan_state_code, sq_len_2 , v_quart_pad) || v_pad_char ||
1648 LPAD(p_tan_pin, sq_len_6 , v_quart_pad) || v_pad_char ||
1649 LPAD(NVL(p_collector_email,v_q_null_filler ) , sq_len_75 , v_quart_pad) || v_pad_char ||
1650 LPAD(NVL(p_collector_stdCode,v_quart_numfill ) , sq_len_5 , v_quart_pad) || v_pad_char ||
1651 LPAD(NVL(p_collector_phoneNo,v_quart_numfill ) , sq_len_10 , v_quart_pad) || v_pad_char ||
1652 LPAD(p_addrChangedSinceLastReturn, sq_len_1 , v_quart_pad) || v_pad_char ||
1653 LPAD(p_type_of_collector, sq_len_1 , v_quart_pad) || v_pad_char || /*Bug 8880543 - Changed Collector Status to Collector Type*/
1654 LPAD(p_pers_resp_for_collection, sq_len_75 , v_quart_pad) || v_pad_char ||
1655 LPAD(p_RespPerson_designation, sq_len_20 , v_quart_pad) || v_pad_char ||
1656 LPAD(p_RespPerson_address1, sq_len_25 , v_quart_pad) || v_pad_char ||
1657 LPAD(NVL(p_RespPerson_address2,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1658 LPAD(NVL(p_RespPerson_address3,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1659 LPAD(NVL(p_RespPerson_address4,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1660 LPAD(NVL(p_RespPerson_address5,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1661 LPAD(p_RespPerson_state, sq_len_2 , v_quart_pad) || v_pad_char ||
1662 LPAD(p_RespPerson_pin, sq_len_6 , v_quart_pad) || v_pad_char ||
1663 LPAD(NVL(p_RespPerson_email,v_q_null_filler ) , sq_len_75 , v_quart_pad) || v_pad_char ||
1664 LPAD(NVL(p_RespPerson_mobile,v_q_null_filler ) , sq_len_75 , v_quart_pad) || v_pad_char || /*Bug 10238421*/
1665 LPAD(NVL(p_RespPerson_stdCode,v_quart_numfill ) , sq_len_5 , v_quart_pad) || v_pad_char ||
1666 LPAD(NVL(p_RespPerson_phoneNo,v_quart_numfill ) , sq_len_10 , v_quart_pad) || v_pad_char ||
1667 LPAD(p_RespPerson_addressChange, sq_len_1 , v_quart_pad) || v_pad_char ||
1668 LPAD(to_char(p_totTaxCollectedAsPerChallan,v_format_amount), sq_len_15 , v_quart_pad) || v_pad_char ||
1669 LPAD(NVL(p_unmatch_challan_cnt,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char || /*Bug 10238421*/
1670 LPAD(NVL(p_salaryRecords_count,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1671 LPAD(NVL(p_gross_total,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1672 LPAD(upper(p_ao_approval), sq_len_1 , v_quart_pad) || v_pad_char ||
1673 LPAD(NVL(p_ao_approval_number,v_q_noval_filler ), sq_len_15 , v_quart_pad) || v_pad_char ||
1674 /*Bug 8880543 - Modified for eTDS/eTCS FVU changes - Start*/
1675 LPAD(NVL(p_last_collector_type, v_q_null_filler ) , sq_len_1 , v_quart_pad) || v_pad_char ||
1676 LPAD(NVL(p_state_name, v_q_null_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1677 LPAD(NVL(p_pao_code, v_q_null_filler ) , sq_len_20 , v_quart_pad) || v_pad_char ||
1678 LPAD(NVL(p_ddo_code, v_q_null_filler ) , sq_len_20 , v_quart_pad) || v_pad_char ||
1679 LPAD(NVL(p_ministry_name, v_q_null_filler ) , sq_len_3 , v_quart_pad) || v_pad_char ||
1680 LPAD(NVL(p_ministry_name_other, v_q_null_filler ) , sq_len_150 , v_quart_pad) || v_pad_char ||
1681 LPAD(NVL(p_filler2, v_q_null_filler ) , sq_len_12 , v_quart_pad) || v_pad_char ||
1682 LPAD(NVL(to_char(p_pao_registration_no), v_q_null_filler ) , sq_len_7 , v_quart_pad) || v_pad_char ||
1683 LPAD(NVL(p_ddo_registration_no, v_q_null_filler ) , sq_len_10 , v_quart_pad) || v_pad_char ||
1684 /*Bug 8880543 - Modified for eTDS/eTCS FVU changes - End*/
1685 LPAD(NVL(p_recHash,v_q_noval_filler ) , sq_len_2 , v_quart_pad)
1686 );
1687 END IF ;
1688
1689 END create_quarterly_batch_header;
1690
1691 PROCEDURE validate_challan_detail
1692 ( p_line_number IN NUMBER ,
1693 p_record_type IN VARCHAR2,
1694 p_batch_number IN NUMBER,
1695 p_challan_dtl_slno IN NUMBER,
1696 p_party_cnt IN NUMBER,
1697 p_nil_challan_indicat IN VARCHAR2,
1698 p_tcs_section IN VARCHAR2,
1699 p_tcs_amt IN NUMBER,
1700 p_surcharge_amt IN NUMBER,
1701 p_cess_amt IN NUMBER,
1702 p_amt_of_oth IN NUMBER,
1703 p_tcs_amount IN NUMBER,
1704 p_total_income_tcs IN NUMBER,
1705 p_challan_no IN VARCHAR2,
1706 p_bank_branch_code IN VARCHAR2,
1707 p_challan_Date IN DATE,
1708 p_check_number IN NUMBER,
1709 p_amt_of_int IN NUMBER ,
1710 p_total_deposit IN NUMBER ,
1711 p_tcs_income_tax IN NUMBER,
1712 p_tcs_surcharge IN NUMBER ,
1713 p_tcs_cess IN NUMBER ,
1714 p_tcs_interest_amt IN NUMBER ,
1715 p_tcs_other_amt IN NUMBER ,
1716 p_return_code OUT NOCOPY VARCHAR2,
1717 p_return_message OUT NOCOPY VARCHAR2
1718 )
1719 IS
1720 BEGIN
1721 IF p_line_number IS NULL THEN
1722 p_return_message := p_return_message || ' Line number should not be null. ' ;
1723 IF lv_action <> 'V' THEN
1724 goto end_of_procedure ;
1725 END IF ;
1726 END IF ;
1727 IF p_record_type IS NULL THEN
1728 p_return_message := p_return_message || ' Record Type is null. ' ;
1729 IF lv_action <> 'V' THEN
1730 goto end_of_procedure ;
1731 END IF ;
1732 END IF ;
1733 IF p_batch_number IS NULL THEN
1734 p_return_message := p_return_message || ' Batch Number is null. ' ;
1735 IF lv_action <> 'V' THEN
1736 goto end_of_procedure ;
1737 END IF ;
1738 END IF ;
1739 IF p_challan_dtl_slno IS NULL THEN
1740 p_return_message := p_return_message || ' Challan Record Number is null. ' ;
1741 IF lv_action <> 'V' THEN
1742 goto end_of_procedure ;
1743 END IF ;
1744 END IF ;
1745 IF p_party_cnt IS NULL THEN
1746 p_return_message := p_return_message || ' Party Count is null . ' ;
1747 IF lv_action <> 'V' THEN
1748 goto end_of_procedure ;
1749 END IF ;
1750 END IF ;
1751 IF p_nil_challan_indicat IS NULL THEN
1752 p_return_message := p_return_message || ' NIL Challan Indicator is null . ' ;
1753 IF lv_action <> 'V' THEN
1754 goto end_of_procedure ;
1755 END IF ;
1756 END IF ;
1757 IF p_tcs_section IS NULL THEN
1758 p_return_message := p_return_message || ' TCS Section is null . ' ;
1759 IF lv_action <> 'V' THEN
1760 goto end_of_procedure ;
1761 END IF ;
1762 END IF ;
1763 IF p_tcs_amt IS NULL THEN
1764 p_return_message := p_return_message || ' TCS Amount is null . ' ;
1765 IF lv_action <> 'V' THEN
1766 goto end_of_procedure ;
1767 END IF ;
1768 END IF ;
1769 IF p_surcharge_amt IS NULL THEN
1770 p_return_message := p_return_message || ' TCS Surcharge Amount is null . ' ;
1771 IF lv_action <> 'V' THEN
1772 goto end_of_procedure ;
1773 END IF ;
1774 END IF ;
1775 IF p_cess_amt IS NULL THEN
1776 p_return_message := p_return_message || ' TCS Cess Amount is null . ' ;
1777 IF lv_action <> 'V' THEN
1778 goto end_of_procedure ;
1779 END IF ;
1780 END IF ;
1781 IF p_amt_of_oth IS NULL THEN
1782 p_return_message := p_return_message || ' TCS Other Amount is null . ' ;
1783 IF lv_action <> 'V' THEN
1784 goto end_of_procedure ;
1785 END IF ;
1786 END IF ;
1787 IF p_tcs_amount IS NULL THEN
1788 p_return_message := p_return_message || ' Total TCS Amount is null . ' ;
1789 IF lv_action <> 'V' THEN
1790 goto end_of_procedure ;
1791 END IF ;
1792 END IF ;
1793 IF p_challan_no IS NULL THEN
1794 p_return_message := p_return_message || ' Challan No is null . ' ;
1795 IF lv_action <> 'V' THEN
1796 goto end_of_procedure ;
1797 END IF ;
1798 END IF ;
1799 IF p_challan_Date IS NULL THEN
1800 p_return_message := p_return_message || ' Challan Date is null . ' ;
1801 IF lv_action <> 'V' THEN
1802 goto end_of_procedure ;
1803 END IF ;
1804 END IF ;
1805
1806 IF p_total_income_tcs IS NULL THEN
1807 p_return_message := p_return_message || ' Total Tax Deposit Amount is null . ' ;
1808 IF lv_action <> 'V' THEN
1809 goto end_of_procedure ;
1810 END IF ;
1811 END IF ;
1812 IF p_amt_of_int IS NULL THEN
1813 p_return_message := p_return_message || ' TCS Interest Amount is null . ' ;
1814 IF lv_action <> 'V' THEN
1815 goto end_of_procedure ;
1816 END IF ;
1817 END IF ;
1818 IF p_total_deposit IS NULL THEN
1819 p_return_message := p_return_message || ' Amount As Per Party is null . ' ;
1820 IF lv_action <> 'V' THEN
1821 goto end_of_procedure ;
1822 END IF ;
1823 END IF ;
1824 IF p_tcs_income_tax IS NULL THEN
1825 p_return_message := p_return_message || ' Total Tax Deposit Amount As Per Party is null . ' ;
1826 IF lv_action <> 'V' THEN
1827 goto end_of_procedure ;
1828 END IF ;
1829 END IF ;
1830 IF p_tcs_surcharge IS NULL THEN
1831 p_return_message := p_return_message || ' Total TCS Surcharge is null . ' ;
1832 IF lv_action <> 'V' THEN
1833 goto end_of_procedure ;
1834 END IF ;
1835 END IF ;
1836 IF p_tcs_cess IS NULL THEN
1837 p_return_message := p_return_message || ' Total TCS Cess is null . ' ;
1838 IF lv_action <> 'V' THEN
1839 goto end_of_procedure ;
1840 END IF ;
1841 END IF ;
1842 IF p_tcs_interest_amt IS NULL THEN
1843 p_return_message := p_return_message || ' Total TCS Interest is null . ' ;
1844 IF lv_action <> 'V' THEN
1845 goto end_of_procedure ;
1846 END IF ;
1847 END IF ;
1848 IF p_tcs_other_amt IS NULL THEN
1849 p_return_message := p_return_message || ' Total TCS Other Amount is null . ' ;
1850 IF lv_action <> 'V' THEN
1851 goto end_of_procedure ;
1852 END IF ;
1853 END IF ;
1854
1855 jai_ap_tds_etds_pkg.check_numeric(p_challan_no, 'Check Number : ' || p_check_number || ' Challan Number is not a Numeric Value', lv_action);
1856 jai_ap_tds_etds_pkg.check_numeric(p_bank_branch_code, 'Check Number : ' || p_check_number || ' Bank Branch Code is not a Numeric Value ', lv_action);
1857
1858 IF lv_action = 'V' THEN
1859 goto end_of_procedure ;
1860 END IF ;
1861
1862 <<end_of_procedure>>
1863 IF p_return_message IS NOT NULL THEN
1864 p_return_code := 'E';
1865 p_return_message := 'Challan Detail Error - ' || 'Check Number : ' || p_check_number || '. ' || p_return_message ;
1866 END IF;
1867 END validate_challan_detail;
1868
1869 PROCEDURE create_quart_challan_dtl(
1870 p_line_number IN NUMBER ,
1871 p_record_type IN VARCHAR2 ,
1872 p_batch_number IN NUMBER ,
1873 p_challan_dtl_slno IN NUMBER ,
1874 p_collection_cnt IN NUMBER ,
1875 p_nil_challan_indicator IN VARCHAR2 ,
1876 p_ch_updIndicator IN VARCHAR2 ,
1877 p_filler2 IN VARCHAR2 ,
1878 p_filler3 IN VARCHAR2 ,
1879 p_filler4 IN VARCHAR2 ,
1880 p_last_bank_challan_no IN VARCHAR2 ,
1881 p_bank_challan_no IN VARCHAR2 ,
1882 p_last_transfer_voucher_no IN VARCHAR2 ,
1883 p_transfer_voucher_no IN NUMBER ,
1884 p_last_bank_branch_code IN VARCHAR2 ,
1885 p_bank_branch_code IN VARCHAR2 ,
1886 p_challan_lastDate IN VARCHAR2 ,
1887 p_challan_Date IN DATE ,
1888 p_filler5 IN VARCHAR2 ,
1889 p_filler6 IN VARCHAR2 ,
1890 p_tcs_section IN VARCHAR2 ,
1891 p_tcs_amt IN NUMBER ,
1892 p_surcharge_amt IN NUMBER ,
1893 p_cess_amt IN NUMBER ,
1894 p_amt_of_int IN NUMBER ,
1895 p_amt_of_oth IN NUMBER ,
1896 p_tcs_amount IN NUMBER ,
1897 p_last_total_depositAmt IN NUMBER ,
1898 p_total_deposit IN NUMBER ,
1899 p_tcs_income_tax IN NUMBER ,
1900 p_tcs_surcharge IN NUMBER ,
1901 p_tcs_cess IN NUMBER ,
1902 p_total_income_tcs IN NUMBER ,
1903 p_tcs_interest_amt IN NUMBER ,
1904 p_tcs_other_amt IN NUMBER ,
1905 p_check_number IN NUMBER ,
1906 p_book_entry IN VARCHAR2 ,
1907 p_remarks IN VARCHAR2 ,
1908 p_ch_recHash IN VARCHAR2,
1909 p_generate_headers IN VARCHAR2
1910 )
1911 IS
1912 BEGIN
1913 IF p_generate_headers = 'N' THEN
1914 UTL_FILE.PUT_LINE(
1915 jai_ap_tds_etds_pkg.v_filehandle,p_line_number || v_delimeter||
1916 upper(p_record_type) || v_delimeter||
1917 p_batch_number || v_delimeter||
1918 p_challan_dtl_slno || v_delimeter||
1919 p_collection_cnt || v_delimeter||
1920 p_nil_challan_indicator || v_delimeter||
1921 p_ch_updIndicator || v_delimeter||
1922 p_filler2 || v_delimeter||
1923 p_filler3 || v_delimeter||
1924 p_filler4 || v_delimeter||
1925 p_last_bank_challan_no || v_delimeter||
1926 substr(p_bank_challan_no, 1,5) || v_delimeter||
1927 p_last_transfer_voucher_no || v_delimeter||
1928 p_transfer_voucher_no || v_delimeter||
1929 p_last_bank_branch_code || v_delimeter||
1930 p_bank_branch_code || v_delimeter||
1931 p_challan_lastDate || v_delimeter||
1932 to_char(p_challan_Date,'ddmmyyyy') || v_delimeter||
1933 p_filler5 || v_delimeter||
1934 p_filler6 || v_delimeter||
1935 p_tcs_section || v_delimeter||
1936 to_char(p_tcs_amt,v_format_amount) || v_delimeter||
1937 to_char(p_surcharge_amt,v_format_amount) || v_delimeter||
1938 to_char(p_cess_amt,v_format_amount) || v_delimeter||
1939 to_char(p_amt_of_int,v_format_amount) || v_delimeter||
1940 to_char(p_amt_of_oth,v_format_amount) || v_delimeter||
1941 to_char(p_tcs_amount,v_format_amount) || v_delimeter||
1942 p_last_total_depositAmt || v_delimeter||
1943 to_char(p_total_deposit,v_format_amount) || v_delimeter||
1944 to_char(p_tcs_income_tax,v_format_amount) || v_delimeter||
1945 to_char(p_tcs_surcharge,v_format_amount) || v_delimeter||
1946 to_char(p_tcs_cess,v_format_amount) || v_delimeter||
1947 to_char(p_total_income_tcs,v_format_amount) || v_delimeter||
1948 to_char(p_tcs_interest_amt,v_format_amount) || v_delimeter||
1949 to_char(p_tcs_other_amt, v_format_amount) || v_delimeter||
1950 p_check_number || v_delimeter||
1951 p_book_entry || v_delimeter||
1952 p_remarks || v_delimeter||
1953 p_ch_recHash ) ;
1954 ELSE
1955 UTL_FILE.PUT_LINE( jai_ap_tds_etds_pkg.v_filehandle,
1956 LPAD(p_line_number , sq_len_9, v_quart_pad) || v_pad_char ||
1957 LPAD(upper(p_record_type) , sq_len_2, v_quart_pad) || v_pad_char ||
1958 LPAD(p_batch_number , sq_len_9, v_quart_pad) || v_pad_char ||
1959 LPAD(p_challan_dtl_slno , sq_len_9, v_quart_pad) || v_pad_char ||
1960 LPAD(p_collection_cnt , sq_len_9, v_quart_pad) || v_pad_char ||
1961 LPAD(p_nil_challan_indicator , sq_len_1, v_quart_pad) || v_pad_char ||
1962 LPAD(NVL(p_ch_updIndicator,v_q_noval_filler), sq_len_2, v_quart_pad) || v_pad_char ||
1963 LPAD(NVL(p_filler2,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1964 LPAD(NVL(p_filler3,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1965 LPAD(NVL(p_filler4,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1966 LPAD(NVL(p_last_bank_challan_no,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1967 LPAD(NVL(p_bank_challan_no,v_q_null_filler ) , sq_len_5, v_quart_pad) || v_pad_char ||
1968 LPAD(NVL(p_last_transfer_voucher_no,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1969 LPAD(NVL(p_transfer_voucher_no,v_quart_numfill ) , sq_len_9, v_quart_pad) || v_pad_char ||
1970 LPAD(NVL(p_last_bank_branch_code,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1971 LPAD(NVL(p_bank_branch_code,v_q_null_filler ) , sq_len_7, v_quart_pad) || v_pad_char ||
1972 LPAD(NVL(p_challan_lastDate,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1973 LPAD(to_char(p_challan_Date,'ddmmyyyy') , sq_len_8, v_quart_pad) || v_pad_char ||
1974 LPAD(NVL(p_filler5,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1975 LPAD(NVL(p_filler6,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1976 LPAD(p_tcs_section, sq_len_3, v_quart_pad) || v_pad_char ||
1977 LPAD(to_char(p_tcs_amt , v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1978 LPAD(to_char( p_surcharge_amt, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1979 LPAD(to_char( p_cess_amt, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1980 LPAD(to_char( p_amt_of_int, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1981 LPAD(to_char( p_amt_of_oth, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1982 LPAD(to_char( p_tcs_amount, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1983 LPAD(NVL(p_last_total_depositAmt, v_quart_numfill) , sq_len_2, v_quart_pad) || v_pad_char ||
1984 LPAD(to_char( p_total_deposit, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1985 LPAD(to_char( p_tcs_income_tax, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1986 LPAD(to_char( p_tcs_surcharge, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1987 LPAD(to_char( p_tcs_cess, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1988 LPAD(to_char( p_total_income_tcs, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1989 LPAD(to_char( p_tcs_interest_amt, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1990 LPAD(to_char( p_tcs_other_amt, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1991 LPAD(NVL(p_check_number,v_quart_numfill ) , sq_len_15, v_quart_pad) || v_pad_char ||
1992 LPAD(NVL(p_book_entry,v_q_null_filler ) , sq_len_1, v_quart_pad) || v_pad_char ||
1993 LPAD(NVL(p_remarks,v_q_null_filler ) , sq_len_14, v_quart_pad) || v_pad_char ||
1994 LPAD(NVL(p_ch_recHash,v_q_noval_filler ) , sq_len_2, v_quart_pad)
1995 );
1996 END IF ;
1997
1998 END create_quart_challan_dtl;
1999
2000 --Date Population procedures for ETCS Yearly Returns
2001
2002 PROCEDURE create_file_header(
2003 p_line_number IN NUMBER,
2004 p_record_type IN VARCHAR2,
2005 p_file_type IN VARCHAR2,
2006 p_upload_type IN VARCHAR2,
2007 p_file_creation_date IN DATE,
2008 p_file_sequence_number IN NUMBER,
2009 p_deductor_tan IN VARCHAR2,
2010 p_number_of_batches IN NUMBER
2011 ) IS
2012
2013 BEGIN
2014
2015 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
2016 LPAD(p_line_number, s_line_number, v_pad_number)
2017 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(p_record_type, s_record_type, v_pad_char)
2018 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(p_file_type, s_file_type, v_pad_char)
2019 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(p_upload_type, s_upload_type, v_pad_char)
2020 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(to_char(p_file_creation_date,'ddmmyyyy'), s_date, v_pad_date)
2021 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(p_file_sequence_number, s_file_sequence_number, v_pad_number)
2022 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductor_tan,' '), s_deductor_tan, v_pad_char)
2023 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(p_number_of_batches,0), s_number_of_batches, v_pad_number)||fnd_global.local_chr(13)
2024 );
2025 END create_file_header ;
2026
2027 PROCEDURE create_dd IS
2028 BEGIN
2029
2030 -- Deductee Detail
2031 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, fnd_global.local_chr(10) );
2032 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
2033 LPAD('LineNo', s_line_number, v_pad_char)
2034 ||v_pad_char||RPAD('RT', s_record_type, v_pad_char)
2035 ||v_pad_char||LPAD('B.No.', s_batch_number, v_pad_char)
2036 ||v_pad_char||LPAD('DSlNo', s_deductee_slno, v_pad_char)
2037 ||v_pad_char||RPAD('Secn.', s_deductee_section, v_pad_char)
2038 ||v_pad_char||RPAD('BCode', s_deductee_code, v_pad_char)
2039 ||v_pad_char||RPAD('PrtyPan', s_deductee_pan, v_pad_char)
2040 ||v_pad_char||RPAD('Party Name', s_deductee_name, v_pad_char)
2041 ||v_pad_char||RPAD('Party Addr1', s_deductee_address1, v_pad_char)
2042 ||v_pad_char||RPAD('Party Addr2', s_deductee_address2, v_pad_char)
2043 ||v_pad_char||RPAD('Party Addr3', s_deductee_address3, v_pad_char)
2044 ||v_pad_char||RPAD('Party Addr4', s_deductee_address4, v_pad_char)
2045 ||v_pad_char||RPAD('Party Addr5', s_deductee_address5, v_pad_char)
2046 ||v_pad_char||LPAD('PState', s_deductee_state, v_pad_char)
2047 ||v_pad_char||LPAD('PtePin', s_deductee_pin, v_pad_char)
2048 ||v_pad_char||LPAD('Purch Amount', s_filler, v_pad_char)
2049 ||v_pad_char||LPAD('Pay. Amount', s_payment_amount, v_pad_char)
2050 ||v_pad_char||LPAD('Pay. Date', s_date, v_pad_char)
2051 ||v_pad_char||LPAD('PBE', s_book_ent_oth , v_pad_char)
2052 ||v_pad_char||LPAD('TxRt', s_tax_rate, v_pad_char)
2053 ||v_pad_char||LPAD('Filler4', s_filler6, v_pad_char)
2054 ||v_pad_char||LPAD('TxColected', s_tax_deducted, v_pad_char)
2055 ||v_pad_char||LPAD('TxCol.Dt', s_date, v_pad_date)
2056 ||v_pad_char||RPAD('BSRCode', s_bank_branch_code, v_pad_char)
2057 ||v_pad_char||LPAD('TxPay.Dt', s_date, v_pad_date)
2058 ||v_pad_char||RPAD('ChlnNo', s_challan_no, v_pad_char)
2059 ||v_pad_char||LPAD('TcsCrtDt', s_date, v_pad_char)
2060 ||v_pad_char||RPAD('R', s_reason_for_nDeduction, v_pad_char)
2061 ||v_pad_char||LPAD('Filler5', s_filler, v_pad_char)
2062 );
2063
2064
2065 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
2066 LPAD(v_underline_char, s_line_number, v_underline_char)
2067 ||v_pad_char||RPAD(v_underline_char, s_record_type, v_underline_char)
2068 ||v_pad_char||LPAD(v_underline_char, s_batch_number, v_underline_char)
2069 ||v_pad_char||LPAD(v_underline_char, s_deductee_slno, v_underline_char)
2070 ||v_pad_char||RPAD(v_underline_char, s_deductee_section, v_underline_char)
2071 ||v_pad_char||RPAD(v_underline_char, s_deductee_code, v_underline_char)
2072 ||v_pad_char||RPAD(v_underline_char, s_deductee_pan, v_underline_char)
2073 ||v_pad_char||RPAD(v_underline_char, s_deductee_name, v_underline_char)
2074 ||v_pad_char||RPAD(v_underline_char, s_deductee_address1, v_underline_char)
2075 ||v_pad_char||RPAD(v_underline_char, s_deductee_address2, v_underline_char)
2076 ||v_pad_char||RPAD(v_underline_char, s_deductee_address3, v_underline_char)
2077 ||v_pad_char||RPAD(v_underline_char, s_deductee_address4, v_underline_char)
2078 ||v_pad_char||RPAD(v_underline_char, s_deductee_address5, v_underline_char)
2079 ||v_pad_char||LPAD(v_underline_char, s_deductee_state, v_underline_char)
2080 ||v_pad_char||LPAD(v_underline_char, s_deductee_pin, v_underline_char)
2081 ||v_pad_char||LPAD(v_underline_char, s_filler, v_underline_char)
2082 ||v_pad_char||LPAD(v_underline_char, s_payment_amount, v_underline_char)
2083 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
2084 ||v_pad_char||LPAD(v_underline_char, s_book_ent_oth , v_underline_char)
2085 ||v_pad_char||LPAD(v_underline_char, s_tax_rate, v_underline_char)
2086 ||v_pad_char||LPAD(v_underline_char, s_filler6, v_underline_char)
2087 ||v_pad_char||LPAD(v_underline_char, s_tax_deducted, v_underline_char)
2088 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
2089 ||v_pad_char||RPAD(v_underline_char, s_bank_branch_code, v_underline_char)
2090 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
2091 ||v_pad_char||RPAD(v_underline_char, s_challan_no, v_underline_char)
2092 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
2093 ||v_pad_char||RPAD(v_underline_char, s_reason_for_nDeduction, v_underline_char)
2094 ||v_pad_char||LPAD(v_underline_char, s_filler, v_underline_char)
2095 );
2096
2097 END create_dd;
2098
2099
2100 PROCEDURE create_fh(p_batch_id IN NUMBER) IS
2101 v_req JAI_AP_ETDS_REQUESTS%rowtype;
2102 BEGIN
2103
2104 -- File Header
2105 SELECT * INTO v_req FROM JAI_AP_ETDS_REQUESTS WHERE batch_id = p_batch_id;
2106
2107 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, 'Input Parameters to this Request:');
2108 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, '-------------------------------------------------');
2109 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
2110 ' batch_id ->'||v_req.batch_id||fnd_global.local_chr(10)
2111 ||' request_id ->'||v_req.request_id||fnd_global.local_chr(10)
2112 ||' operating_unit_id ->'||v_req.operating_unit_id||fnd_global.local_chr(10)
2113 ||' org_tan_number ->'||v_req.org_tan_number||fnd_global.local_chr(10)
2114 ||' financial_year ->'||v_req.financial_year||fnd_global.local_chr(10)
2115 ||' tax_authority_id ->'||v_req.tax_authority_id||fnd_global.local_chr(10)
2116 ||' tax_authority_site_id ->'||v_req.tax_authority_site_id||fnd_global.local_chr(10)
2117 ||' organization_id ->'||v_req.organization_id||fnd_global.local_chr(10)
2118 ||' deductor_name ->'||v_req.deductor_name||fnd_global.local_chr(10)
2119 ||' deductor_state ->'||v_req.deductor_state||fnd_global.local_chr(10)
2120 ||' addr_changed_since_last_ret->'||v_req.addr_changed_since_last_ret||fnd_global.local_chr(10)
2121 ||' deductor_status ->'||v_req.deductor_status||fnd_global.local_chr(10)
2122 ||' person_resp_for_deduction ->'||v_req.person_resp_for_deduction||fnd_global.local_chr(10)
2123 ||' designation_of_pers_resp ->'||v_req.designation_of_pers_resp||fnd_global.local_chr(10)
2124 ||' challan_start_date ->'||v_req.challan_start_date||fnd_global.local_chr(10)
2125 ||' challan_end_date ->'||v_req.challan_end_date||fnd_global.local_chr(10)
2126 ||' file_path ->'||v_req.file_path||fnd_global.local_chr(10)
2127 ||' filename ->'||v_req.filename||fnd_global.local_chr(10)
2128 );
2129
2130
2131 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
2132 LPAD('LineNo', s_line_number, v_pad_char)
2133 ||v_pad_char||RPAD('RT', s_record_type, v_pad_char)
2134 ||v_pad_char||RPAD('FT', s_file_type, v_pad_char)
2135 ||v_pad_char||RPAD('UT', s_upload_type, v_pad_char)
2136 ||v_pad_char||LPAD('FileDate', s_date, v_pad_char)
2137 ||v_pad_char||LPAD('FSeqNo', s_file_sequence_number, v_pad_char)
2138 ||v_pad_char||RPAD('Org Tan', s_deductor_tan, v_pad_char)
2139 ||v_pad_char||LPAD('NoOfBatches', s_number_of_batches, v_pad_char)
2140 );
2141 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
2142 LPAD(v_underline_char, s_line_number, v_underline_char)
2143 ||v_pad_char||RPAD(v_underline_char, s_record_type, v_underline_char)
2144 ||v_pad_char||RPAD(v_underline_char, s_file_type, v_underline_char)
2145 ||v_pad_char||RPAD(v_underline_char, s_upload_type, v_underline_char)
2146 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
2147 ||v_pad_char||LPAD(v_underline_char, s_file_sequence_number, v_underline_char)
2148 ||v_pad_char||RPAD(v_underline_char, s_deductor_tan, v_underline_char)
2149 ||v_pad_char||LPAD(v_underline_char, s_number_of_batches, v_underline_char)
2150 );
2151 END create_fh;
2152
2153 PROCEDURE create_cd IS
2154 BEGIN
2155
2156 -- Challan Detail
2157 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, fnd_global.local_chr(10) );
2158 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
2159 LPAD('LineNo', s_line_number, v_pad_char)
2160 ||v_pad_char||RPAD('RT', s_record_type, v_pad_char)
2161 ||v_pad_char||LPAD('B.No', s_batch_number, v_pad_char)
2162 ||v_pad_char||LPAD('CSlNo', s_challan_slno, v_pad_char)
2163 ||v_pad_char||RPAD('Secn.', s_challan_section, v_pad_char)
2164 ||v_pad_char||LPAD('TCS amount', s_amount_tcs, v_pad_char)
2165 ||v_pad_char||LPAD('Surcharge amt',s_amount_sur , v_pad_char)
2166 ||v_pad_char||LPAD('CESS amount', s_amount_cess , v_pad_char)
2167 ||v_pad_char||LPAD('Amount of int', s_amount_cess , v_pad_char)
2168 ||v_pad_char||LPAD('Amount - others', s_amount_cess , v_pad_char)
2169 ||v_pad_char||LPAD('Total amount', s_amount_deducted , v_pad_char)
2170 ||v_pad_char||LPAD('Chq/DD.No', s_chq_dd_num , v_pad_char)
2171 ||v_pad_char||RPAD('BankBrCode', s_bank_branch_code, v_pad_char)
2172 ||v_pad_char||LPAD('TxdpDate', s_date, v_pad_char) --chq deposit date
2173 ||v_pad_char||RPAD('Chal.Num.', s_challan_no, v_pad_char)
2174 ||v_pad_char||RPAD('TBE ', s_tds_dep_book_ent , v_pad_char)
2175 ||v_pad_char||RPAD('C', s_filler4 , v_pad_char)
2176 );
2177 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
2178 LPAD(v_underline_char, s_line_number, v_underline_char)
2179 ||v_pad_char||RPAD(v_underline_char, s_record_type, v_underline_char)
2180 ||v_pad_char||LPAD(v_underline_char, s_batch_number, v_underline_char)
2181 ||v_pad_char||LPAD(v_underline_char, s_challan_slno, v_underline_char)
2182 ||v_pad_char||RPAD(v_underline_char, s_challan_section, v_underline_char)
2183 ||v_pad_char||LPAD(v_underline_char, s_amount_tcs , v_underline_char)
2184 ||v_pad_char||LPAD(v_underline_char, s_amount_sur , v_underline_char)
2185 ||v_pad_char||LPAD(v_underline_char, s_amount_CESS , v_underline_char)
2186 ||v_pad_char||LPAD(v_underline_char, s_amount_CESS , v_underline_char)
2187 ||v_pad_char||LPAD(v_underline_char, s_amount_CESS , v_underline_char)
2188 ||v_pad_char||LPAD(v_underline_char, s_amount_deducted , v_underline_char)
2189 ||v_pad_char||LPAD(v_underline_char, s_chq_dd_num , v_underline_char)
2190 ||v_pad_char||RPAD(v_underline_char, s_bank_branch_code, v_underline_char)
2191 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
2192 ||v_pad_char||RPAD(v_underline_char, s_challan_no, v_underline_char)
2193 ||v_pad_char||RPAD(v_underline_char, s_tds_dep_book_ent , v_underline_char)
2194 ||v_pad_char||RPAD(v_underline_char, s_filler4 , v_underline_char)
2195 );
2196 END create_cd;
2197
2198 PROCEDURE create_deductee_detail(
2199 p_line_number IN NUMBER,
2200 p_record_type IN VARCHAR2,
2201 p_batch_number IN NUMBER,
2202 p_deductee_slno IN NUMBER,
2203 p_deductee_section IN VARCHAR2,
2204 p_deductee_code IN VARCHAR2,
2205 p_deductee_pan IN VARCHAR2,
2206 p_deductee_name IN VARCHAR2,
2207 p_deductee_address1 IN VARCHAR2,
2208 p_deductee_address2 IN VARCHAR2,
2209 p_deductee_address3 IN VARCHAR2,
2210 p_deductee_address4 IN VARCHAR2,
2211 p_deductee_address5 IN VARCHAR2,
2212 p_deductee_state IN VARCHAR2,
2213 p_deductee_pin IN NUMBER,
2214 p_purchase_amount IN NUMBER,
2215 p_payment_amount IN NUMBER,
2216 p_payment_date IN DATE,
2217 p_book_ent_oth IN VARCHAR2,
2218 p_tax_rate IN NUMBER,
2219 p_filler6 IN VARCHAR2,
2220 p_tax_deducted IN NUMBER,
2221 p_tax_deducted_date IN DATE,
2222 p_tax_payment_date IN DATE,
2223 p_bank_branch_code IN VARCHAR2,
2224 p_challan_no IN VARCHAR2,
2225 p_tds_certificate_date IN DATE,
2226 p_reason_for_nDeduction IN VARCHAR2,
2227 p_filler7 IN NUMBER
2228 ) IS
2229
2230 BEGIN
2231
2232 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
2233 LPAD(p_line_number, s_line_number, v_pad_number)
2234 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(p_record_type, s_record_type, v_pad_char)
2235 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(p_batch_number, s_batch_number, v_pad_number)
2236 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(p_deductee_slno,0), s_deductee_slno, v_pad_number)
2237 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(p_deductee_section, s_deductee_section, v_pad_char)
2238 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_code,' '), s_deductee_code, v_pad_char)
2239 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_pan,' '), s_deductee_pan, v_pad_char)
2240 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_name,' '), s_deductee_name, v_pad_char)
2241 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_address1,' '), s_deductee_address1, v_pad_char)
2242 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_address2,' '), s_deductee_address2, v_pad_char)
2243 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_address3,' '), s_deductee_address3, v_pad_char)
2244 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_address4,' '), s_deductee_address4, v_pad_char)
2245 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_address5,' '), s_deductee_address5, v_pad_char)
2246 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(p_deductee_state,'0'), s_deductee_state, v_pad_number)
2247 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(p_deductee_pin,0), s_deductee_pin, v_pad_number)
2248 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(jai_ap_tds_etds_pkg.formatAmount(p_purchase_amount), s_payment_amount, v_pad_number)
2249 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(jai_ap_tds_etds_pkg.formatAmount(p_payment_amount), s_payment_amount, v_pad_number)
2250 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(to_char(p_payment_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
2251 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(p_book_ent_oth , s_book_ent_oth, v_pad_char)
2252 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(jai_ap_tds_etds_pkg.formatAmount(p_tax_rate), s_tax_rate, v_pad_number)
2253 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_filler6,' '), s_filler6, v_pad_char)
2254 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(jai_ap_tds_etds_pkg.formatAmount(p_tax_deducted), s_tax_deducted, v_pad_number)
2255 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(to_char(p_tax_deducted_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
2256 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_bank_branch_code,' '), s_bank_branch_code, v_pad_char)
2257 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(to_char(p_tax_payment_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
2258 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_challan_no,' '), s_challan_no, v_pad_char)
2259 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(to_char(p_tds_certificate_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
2260 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_reason_for_nDeduction,' '), s_reason_for_nDeduction, v_pad_char)
2261 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(jai_ap_tds_etds_pkg.formatAmount(p_filler7), s_filler, v_pad_number) || fnd_global.local_chr(13)
2262 );
2263
2264 END create_deductee_detail;
2265
2266
2267 PROCEDURE populate_details(
2268 p_batch_id IN NUMBER,
2269 p_org_tan_num IN VARCHAR2,
2270 p_tax_authority_id IN NUMBER,
2271 p_tax_authority_site_id IN NUMBER,
2272 p_from_date IN DATE,
2273 p_to_date IN DATE,
2274 p_collection_code IN VARCHAR2
2275 )
2276 IS
2277
2278 cursor c_tax_amount(cp_tax_type in varchar2, cp_source_doc_id in number, cp_check_id in number)
2279 is
2280 select nvl(sum(tax_amt),0)
2281 from jai_rgm_taxes
2282 where tax_type = cp_tax_type
2283 and trx_ref_id in
2284 ( select trx_ref_id
2285 from jai_rgm_refs_all jra
2286 where
2287 source_ref_document_id = cp_source_doc_id and
2288 jra.source_document_date between p_from_date and p_to_date
2289 and settlement_id
2290 IN ( select settlement_id
2291 from jai_ap_rgm_payments
2292 where check_id = cp_check_id
2293 )
2294 ) ;
2295
2296
2297 cursor c_pan_no(cp_party_id in number)
2298 is
2299 select pan_no
2300 from JAI_CMN_CUS_ADDRESSES
2301 where customer_id = cp_party_id
2302 and confirm_pan = 'Y'
2303 and pan_no is not null
2304 and rownum = 1 ;
2305
2306 cursor c_buyer_code(cp_party_id in number)
2307 is
2308 select
2309 DECODE(jca.tcs_customer_type, 'COMPANIES', '01', 'OTHERS', '02') buyer_code
2310 from JAI_CMN_CUS_ADDRESSES jca
2311 where jca.customer_id = cp_party_id
2312 and jca.tcs_customer_type is not null
2313 and rownum = 1 ;
2314
2315 cursor c_get_recs
2316 is
2317 select *
2318 from jai_ar_etcs_t for update ;
2319
2320 cursor c_line_amt(cp_source_document_ref_id number, cp_check_id in number) is
2321 select nvl(sum(line_amt),0)
2322 from jai_rgm_refs_all jra
2323 where
2324 source_ref_document_id = cp_source_document_ref_id and
2325 jra.source_document_date between p_from_date and p_to_date
2326 and settlement_id
2327 IN ( select settlement_id
2328 from jai_ap_rgm_payments
2329 where check_id = cp_check_id
2330 ) ;
2331
2332
2333 ln_line_amt number ;
2334
2335 lv_customer_type varchar2(2) ;
2336 lv_pan_no JAI_CMN_CUS_ADDRESSES.pan_no%TYPE ;
2337 lv_item_classification jai_rgm_lookups.display_value%type ;
2338 ln_tcs_amount number ;
2339 ln_surcharge_amount number ;
2340 ln_cess_amount number ;
2341 ln_tcs_cess_amount number ;
2342 ln_sur_cess_amount number ;
2343 -- Date 03/07/2007 by sacsethi for bug 6157120
2344 ln_tcs_sh_cess_amount number ;
2345
2346
2347 Cursor c_rcpt_date(cp_receipt_id number)
2348 is
2349 select receipt_date
2350 from ar_cash_receipts
2351 where cash_receipt_id = cp_receipt_id ;
2352
2353 Cursor c_source_ref_type(cp_source_ref_id in number)
2354 is
2355 select source_document_type
2356 from jai_rgm_refs_all
2357 where source_document_id = cp_source_ref_id
2358 and source_document_type IN (jai_constants.ar_cash_tax_confirmed , jai_constants.trx_type_inv_comp)
2359 and rownum = 1 ;
2360
2361 Cursor c_inv_date(cp_inv_id number)
2362 is
2363 select trx_date
2364 from ra_customer_trx_all
2365 where customer_trx_id = cp_inv_id ;
2366 -- Date 28/06/2007 by sacsethi for bug 6157120
2367 /* CURSOR c_check_dtls(cpn_check_id IN NUMBER) IS
2368 SELECT INTERNAL_BANK_ACCOUNT_ID
2369 FROM IBY_PAYMENTS_ALL
2370 WHERE paper_document_number =cpn_check_id;*/
2371
2372 -- Date 28/06/2007 by sacsethi for bug 6157120
2373 /* CURSOR c_bank_branch_code(cp_bank_account_id IN NUMBER) IS
2374 select a.branch_number
2375 from ce_bank_branches_v a, ce_bank_accounts b
2376 where a.branch_party_id = b.bank_branch_id
2377 and b.bank_account_id = cp_bank_account_id ;*/
2378
2379 /*SELECT a.bank_num
2380 FROM ap_bank_branches a, ap_bank_accounts_all b
2381 WHERE a.bank_branch_id = b.bank_branch_id
2382 AND b.bank_account_id = cp_bank_account_id;*/
2383
2384 v_bank_account_id NUMBER(15);
2385 v_bank_branch_code ce_bank_branches_v.BRANCH_NUMBER%TYPE;
2386
2387 lv_source_ref_type jai_rgm_refs_all.source_document_type%TYPE ;
2388 lv_doc_date date ;
2389
2390 BEGIN
2391
2392 insert into jai_ar_etcs_t
2393 (
2394 Batch_id ,
2395 tcs_check_id ,
2396 check_number ,
2397 tcs_check_date ,
2398 challan_no ,
2399 challan_date ,
2400 bank_branch_code ,
2401 source_document_id ,
2402 party_id ,
2403 party_site_id ,
2404 collection_flag ,
2405 tcs_tax_rate ,
2406 exempted_flag ,
2407 certificate_issue_date ,
2408 created_by ,
2409 creation_date ,
2410 last_updated_by ,
2411 last_update_date ,
2412 last_update_login
2413 )
2414 select
2415 p_batch_id ,
2416 jrp.check_id ,
2417 jrp.check_number ,
2418 jrp. check_deposit_date ,
2419 jrp.challan_no ,
2420 jrp.check_date ,
2421 jrp.bsr_code ,
2422 jra.source_ref_document_id ,
2423 jra.party_id ,
2424 jra.party_site_id ,
2425 p_collection_code ,
2426 jrt.tax_rate ,
2427 jrt.exempted_flag ,
2428 jrc.issue_date ,
2429 fnd_global.user_id ,
2430 sysdate ,
2431 fnd_global.user_id ,
2432 sysdate ,
2433 fnd_global.login_id
2434 from
2435 jai_ap_rgm_payments jrp,
2436 jai_rgm_refs_all jra,
2437 jai_rgm_taxes jrt,
2438 jai_rgm_certificates jrc
2439 where
2440 jrp.settlement_id = jra.settlement_id and
2441 jrp.tax_authority_id = p_tax_authority_id and
2442 jrp.tax_authority_site_id = nvl(p_tax_authority_site_id,jrp.tax_authority_site_id) and
2443 jrp.org_tan_no = p_org_tan_num and
2444 jrt.tax_type = 'TCS' and
2445 jrt.trx_ref_id = jra.trx_ref_id and
2446 jrc.certificate_id = jra.certificate_id and
2447 jra.item_classification = p_collection_code and
2448 jra.source_document_date between p_from_date and p_to_date and
2449 jra.settlement_id is not null and
2450 jra.certificate_id is not null
2451 group by
2452 p_batch_id ,
2453 jrp.check_id ,
2454 jrp.check_number ,
2455 jrp.check_date ,
2456 jrp.challan_no ,
2457 jrp.check_deposit_date ,
2458 jrp.bsr_code ,
2459 jra.source_ref_document_id ,
2460 jra.party_id ,
2461 jra.party_site_id ,
2462 jra.item_classification ,
2463 jrt.tax_rate ,
2464 jrt.exempted_flag ,
2465 jrc.issue_date
2466 having sum(jra.total_tax_amt) > 0;
2467
2468 FOR rec in c_get_recs
2469 LOOP
2470
2471 ln_line_amt := null ;
2472 ln_tcs_amount := null ;
2473 ln_surcharge_amount := null ;
2474 ln_tcs_cess_amount := null ;
2475 ln_sur_cess_amount := null ;
2476 ln_cess_amount := null ;
2477
2478 -- Date 03/07/2007 by sacsethi for bug 6157120
2479 ln_tcs_sh_cess_amount := null ;
2480
2481 open c_line_amt(rec.source_document_id, rec.tcs_check_id) ;
2482 fetch c_line_amt into ln_line_amt ;
2483 close c_line_amt ;
2484
2485 open c_tax_amount(jai_constants.tax_type_tcs,rec.source_document_id, rec.tcs_check_id ) ;
2486 fetch c_tax_amount into ln_tcs_amount ;
2487 close c_tax_amount ;
2488
2489 open c_tax_amount(jai_constants.tax_type_tcs_surcharge, rec.source_document_id, rec.tcs_check_id ) ;
2490 fetch c_tax_amount into ln_surcharge_amount ;
2491 close c_tax_amount ;
2492
2493 open c_tax_amount(jai_constants.tax_type_tcs_cess, rec.source_document_id, rec.tcs_check_id ) ;
2494 fetch c_tax_amount into ln_tcs_cess_amount ;
2495 close c_tax_amount ;
2496
2497 open c_tax_amount(jai_constants.tax_type_tcs_surcharge_cess, rec.source_document_id, rec.tcs_check_id ) ;
2498 fetch c_tax_amount into ln_sur_cess_amount ;
2499 close c_tax_amount ;
2500
2501 -- Date 03/07/2007 by sacsethi for bug 6157120
2502
2503 open c_tax_amount(jai_constants.tax_type_sh_tcs_edu_cess, rec.source_document_id, rec.tcs_check_id ) ;
2504 fetch c_tax_amount into ln_tcs_sh_cess_amount ;
2505 close c_tax_amount ;
2506
2507 ln_cess_amount := ln_tcs_cess_amount + ln_sur_cess_amount + ln_tcs_sh_cess_amount ; -- Date 03/07/2007 by sacsethi for bug 6157120
2508
2509 open c_pan_no(rec.party_id) ;
2510 fetch c_pan_no into lv_pan_no ;
2511 close c_pan_no ;
2512
2513 lv_pan_no := substr(lv_pan_no, 1,10);
2514
2515 open c_buyer_code(rec.party_id) ;
2516 fetch c_buyer_code into lv_customer_type ;
2517 close c_buyer_code ;
2518
2519 open c_source_ref_type(rec.source_document_id);
2520 fetch c_source_ref_type into lv_source_ref_type ;
2521 close c_source_ref_type ;
2522
2523 lv_source_ref_type := substr(lv_source_ref_type, 1,50);
2524
2525 IF lv_source_ref_type = jai_constants.trx_type_inv_comp THEN
2526 open c_inv_date(rec.source_document_id) ;
2527 fetch c_inv_date into lv_doc_date ;
2528 close c_inv_date ;
2529 ELSIF lv_source_ref_type = jai_constants.ar_cash_tax_confirmed THEN
2530 open c_rcpt_date(rec.source_document_id) ;
2531 fetch c_rcpt_date into lv_doc_date ;
2532 close c_rcpt_date ;
2533 END IF ;
2534
2535
2536 -- Date 28/06/2007 by sacsethi for bug 6157120
2537 -- Commenting this code because of no use
2538 /*
2539 OPEN c_check_dtls(rec.check_number);
2540 FETCH c_check_dtls INTO v_bank_account_id ;
2541 CLOSE c_check_dtls;
2542
2543 OPEN c_bank_branch_code(v_bank_account_id);
2544 FETCH c_bank_branch_code INTO v_bank_branch_code;
2545 CLOSE c_bank_branch_code;
2546
2547 v_bank_branch_code := substr(v_bank_branch_code, 1,10);
2548 */
2549
2550 FND_FILE.put_line(FND_FILE.log, ' lv_customer_type : ' || lv_customer_type || 'lv_pan_no : ' || lv_pan_no || ' lv_doc_date : ' || lv_doc_date ||
2551 ' v_bank_branch_code : ' || v_bank_branch_code ) ;
2552
2553 update jai_ar_etcs_t
2554 set
2555 line_amt = ln_line_amt ,
2556 tcs_amt = ln_tcs_amount ,
2557 surcharge_amt = ln_surcharge_amount ,
2558 cess_amt = ln_cess_amount ,
2559 party_code = lv_customer_type ,
2560 party_pan = lv_pan_no ,
2561 source_document_date = lv_doc_date ,
2562 source_document_type = lv_source_ref_type -- ,
2563 --bank_branch_code = v_bank_branch_code
2564 where current of c_get_recs;
2565
2566 END LOOP ;
2567
2568 END populate_details;
2569
2570 PROCEDURE generate_etcs_returns(
2571 p_err_buf OUT NOCOPY VARCHAR2,
2572 p_ret_code OUT NOCOPY NUMBER,
2573 p_tan_number IN VARCHAR2,
2574 p_organization_id IN NUMBER,
2575 p_fin_year IN NUMBER,
2576 p_tax_authority_id IN NUMBER,
2577 p_tax_authority_site_id IN NUMBER,
2578 p_seller_name IN VARCHAR2,
2579 p_seller_state IN VARCHAR2,
2580 p_addrChangedSinceLastRet IN VARCHAR2,
2581 p_persRespForCollection IN VARCHAR2,
2582 p_desgOfPersResponsible IN VARCHAR2,
2583 p_Start_Date IN DATE,
2584 p_End_Date IN DATE,
2585 p_pro_rcpt_num_org_ret IN NUMBER,
2586 p_file_path IN VARCHAR2,
2587 p_filename IN VARCHAR2,
2588 p_collection_code IN VARCHAR2,
2589 p_generate_headers IN VARCHAR2 DEFAULT NULL,
2590 p_period IN VARCHAR2 DEFAULT NULL,
2591 /*Bug 12597773 -Start*/
2592 p_RespPers_flat_no IN VARCHAR2 DEFAULT NULL,
2593 p_RespPers_prem_bldg IN VARCHAR2 DEFAULT NULL,
2594 p_RespPers_rd_st_lane IN VARCHAR2 DEFAULT NULL,
2595 p_RespPers_area_loc IN VARCHAR2 DEFAULT NULL,
2596 p_RespPers_tn_cty_dt IN VARCHAR2 DEFAULT NULL,
2597 --p_RespPersAddress IN VARCHAR2 DEFAULT NULL,
2598 /*Bug 12597773 -End*/
2599 p_RespPersState IN VARCHAR2 DEFAULT NULL,
2600 p_RespPersPin IN NUMBER DEFAULT NULL,
2601 p_RespPersAddrChange IN VARCHAR2 DEFAULT NULL,
2602 p_action IN VARCHAR2 DEFAULT NULL
2603 --p_collector_status IN VARCHAR2 DEFAULT NULL /*Bug 8880543 - Commeted for eTDS/eTCS FVU Changes*/
2604 )
2605 IS
2606
2607 -- Date 28/06/2007 by sacsethi for bug 6157120
2608 /* Bug 12597773 -Start*/
2609 Cursor c_get_state_desc
2610 is
2611 select
2612 description
2613 from FND_FLEX_VALUES_VL a
2614 where flex_value_set_id =
2615 ( select flex_value_set_id
2616 from fnd_flex_value_sets
2617 where flex_value_set_name ='JA_IN_INDIAN_STATES'
2618 )
2619 and flex_value = to_number(p_RespPersState) ;
2620
2621 lv_state_desc fnd_flex_values_vl.description%TYPE ;
2622 lv_request_desc VARCHAR2(200);
2623 lv_req_id NUMBER;
2624 lv_result BOOLEAN;
2625 /* Bug 12597773 -End*/
2626 CURSOR c_pan_number(p_organization_id IN NUMBER) IS
2627 Select ATTRIBUTE_VALUE
2628 from jai_rgm_org_regns_v jrorv ,HR_ORGANIZATION_INFORMATION hrou
2629 where jrorv.attribute_code = jai_constants.pan_no AND
2630 hrou.org_information_context= jai_constants.accounting_information and
2631 hrou.organization_id = p_organization_id and
2632 jrorv.ORGANIZATION_ID = hrou.org_information3 AND
2633 jrorv.REGIME_CODE = jai_constants.tds_regime ;
2634 /*
2635 SELECT attribute2
2636 FROM hr_all_organization_units
2637 WHERE organization_id = p_organization_id;*/
2638 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 begin
2639 --Commented by Chong for DTC ER 2012/09/11 Get TCS calendar from regime start
2640 /*
2641 -- to get financial and assessment years
2642 CURSOR c_fin_year(p_tan_number IN varchar2, p_fin_year IN NUMBER) IS
2643 SELECT start_date, end_date
2644 FROM JAI_AP_TDS_YEARS
2645 WHERE tan_no = p_tan_number --Date 26/05/2007 by sacsethi for bug 6153881
2646 AND fin_year = p_fin_year;
2647 */
2648 --Commented by Chong for DTC ER 2012/09/11 Get TCS calendar from regime end
2649 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 end.
2650 -- gives Location_id linked to Organization
2651 CURSOR c_location_linked_to_org(p_organization_id IN NUMBER) IS
2652 SELECT location_id
2653 FROM hr_all_organization_units
2654 WHERE organization_id = p_organization_id;
2655
2656 v_location_id hr_all_organization_units.location_id%TYPE ;
2657
2658 -- to get address details of location linked to given organization
2659 CURSOR c_address_details(p_location_id IN NUMBER) IS
2660 SELECT location_code, address_line_1, address_line_2, address_line_3, null, null,
2661 replace(postal_code, ' ') postal_code
2662 FROM hr_locations_all
2663 WHERE location_id = p_location_id;
2664
2665 CURSOR c_get_errors(cp_batch_id JAI_AP_ETDS_T.batch_id%TYPE ) IS
2666 Select Error_Message from jai_ap_etds_errors_t
2667 where batch_id = cp_batch_id ;
2668
2669 ln_errors_exist number ;
2670
2671 v_location_code HR_LOCATIONS_ALL.location_code%TYPE;
2672 v_tan_address1 HR_LOCATIONS_ALL.address_line_1%TYPE;
2673 v_tan_address2 HR_LOCATIONS_ALL.address_line_2%TYPE;
2674 v_tan_address3 HR_LOCATIONS_ALL.address_line_3%TYPE;
2675 v_tan_address4 VARCHAR2(75);
2676 v_tan_address5 VARCHAR2(75);
2677 v_postal_code HR_LOCATIONS_ALL.postal_code%TYPE;
2678 v_tan_pin NUMBER(6);
2679
2680 ln_batch_id number ;
2681 lv_etcs_yearly_returns varchar2(1) ;
2682 v_conc_request_id NUMBER(15) ;
2683 v_deductor_pan VARCHAR2(200);
2684 -- code ported from BL12.1.3 on 15-MAR-2013 begin
2685 /*--Commented by Chong for DTC ER 2012/09/11 Get TCS calendar from regime
2686 v_start_date JAI_AP_TDS_YEARS.start_date%type ;
2687 v_end_date JAI_AP_TDS_YEARS.end_date%type;
2688 */
2689 v_start_date GL_PERIODS.START_DATE%TYPE; --Added by Chong for DTC ER 2012/09/11 Get TCS calendar from regime
2690 v_end_date GL_PERIODS.END_DATE%TYPE; --Added by Chong for DTC ER 2012/09/11 Get TCS calendar from regime
2691 -- code ported from Bl12.1.3 on 15-MAR-2013 end.
2692
2693 -- File Header variables
2694 v_line_number NUMBER(9);
2695 v_record_type CHAR(2);
2696 v_file_type CHAR(3) ;
2697 v_quartfile_type CHAR(3);
2698 v_upload_type CHAR(1);
2699 v_file_creation_date date ;
2700 v_file_sequence_number NUMBER(9);
2701 v_seller_tan VARCHAR2(10);
2702 v_number_of_batches NUMBER(9) ;
2703 v_return_prep_util VARCHAR2(75); /*Bug 8880543 - Added for eTDS/eTCS FVU*/
2704
2705 -- Quarterly File Header Variables
2706 v_fh_recordHash varchar2(1);
2707 v_fh_fvuVersion varchar2(1);
2708 v_fh_fileHash varchar2(1);
2709 v_fh_samVersion varchar2(1);
2710 v_fh_samHash varchar2(1);
2711 v_fh_scmVersion varchar2(1);
2712 v_fh_scmHash varchar2(1);
2713 v_fh_consFileHash VARCHAR2(1) ; /*FVU 3.4*/
2714 p_return_code VARCHAR2(1) ;
2715 p_return_message VARCHAR2(2000) ;
2716 lv_generate_headers VARCHAR2(1) ;
2717 v_uploader_type varchar2(1);
2718
2719 -- Batch Header
2720
2721 v_totTaxDeductedAsPerChallan NUMBER;
2722 v_totTaxDeductedAsPerDeductee NUMBER;
2723 v_challan_cnt NUMBER(9) := 0;
2724 v_deductee_cnt NUMBER(9) := 0;
2725 v_batch_number NUMBER(9);
2726 v_form_number CHAR(4) ;
2727 v_financial_year VARCHAR2(6);
2728 v_assessment_year VARCHAR2(6);
2729 v_ack_num_tan_app NUMBER(14);
2730 v_pro_rcpt_num_org_ret NUMBER(14);
2731 v_receipt_number VARCHAR2(1) ; /*10238421*/
2732 v_filler1 VARCHAR2(1) ;
2733 v_filler2 VARCHAR2(1) ;
2734 v_filler3 VARCHAR2(1) ;
2735 v_filler4 VARCHAR2(1) ;
2736 v_seller_name VARCHAR2(75);
2737 v_quarterlyOrYearly VARCHAR2(2) ;
2738 v_addrChangedSinceLastReturn VARCHAR2(1);
2739 v_seller_type VARCHAR2(1); /*Bug 8880543 - Chnaged Seller Status to Seller Type*/
2740 v_personNameRespForDedection VARCHAR2(75);
2741 v_personDesgnRespForDedection VARCHAR2(20);
2742 v_tan_state_code NUMBER(2);
2743 lv_dummy_date date;
2744
2745 -- Quarterly Batch Header variables
2746 v_bh_trnType VARCHAR2(1);
2747 v_bh_batchUpd VARCHAR2(1);
2748 v_bh_org_RRRno VARCHAR2(1);
2749 v_bh_prev_RRRno VARCHAR2(1);
2750 v_bh_RRRno VARCHAR2(1);
2751 v_bh_RRRdate VARCHAR2(1);
2752 /*Bug 10238421 - Start*/
2753 v_bh_org_token_no VARCHAR2(1);
2754 v_bh_prev_token_no VARCHAR2(1);
2755 v_bh_token_no VARCHAR2(1);
2756 v_bh_token_date VARCHAR2(1);
2757 /*Bug 10238421 - End*/
2758 v_bh_deductor_last_tan VARCHAR2(1);
2759 v_deductor_branch VARCHAR2(75);
2760 v_collector_std_code NUMBER(5); /*Bug 10238421*/
2761 v_collector_ph_no NUMBER(10); /*Bug 10238421*/
2762 v_RespPerson_address2 VARCHAR2(25);
2763 v_RespPerson_address3 VARCHAR2(25);
2764 v_RespPerson_address4 VARCHAR2(25);
2765 v_RespPerson_address5 VARCHAR2(25);
2766 v_RespPerson_email VARCHAR2(75);
2767 v_RespPerson_mobile VARCHAR2(75); /*10238421*/
2768 v_unmatch_challan_cnt CHAR(1);
2769 v_bh_salaryRecords_count CHAR(1);
2770 v_bh_gross_total CHAR(1);
2771 v_ao_approval varchar2(1);
2772 v_ao_approval_number VARCHAR2(15);
2773 /*Bug 8880543 - Start*/
2774 v_last_collector_type VARCHAR2(1) ;
2775 v_state_name VARCHAR2(2) ;
2776 v_pao_code VARCHAR2(20) ;
2777 v_ddo_code VARCHAR2(20) ;
2778 v_ministry_name VARCHAR2(3) ;
2779 v_ministry_name_other VARCHAR2(150);
2780 v_pao_registration_no NUMBER(10) ;
2781 v_ddo_registration_no VARCHAR2(10) ;
2782 /*Bug 10238421 - Start*/
2783 v_collector_email VARCHAR2(75);
2784 v_collector_phone_no VARCHAR2(15);
2785 v_resp_person_phone_no VARCHAR2(15);
2786 v_resp_person_std_code VARCHAR2(5);
2787 v_resp_person_ph_no VARCHAR2(10);
2788 /*Bug 10238421 - End*/
2789 v_challan_number VARCHAR2(10) ;
2790 v_return_message VARCHAR2(240);
2791 v_err VARCHAR2(1) ;
2792 /*Bug 8880543 - End*/
2793 v_quart_form_number varchar2(4);
2794 v_bh_recHash varchar2(1);
2795
2796 /*Bug 10238421 - Start*/
2797 cursor c_split_phone_no(p_phone_no IN VARCHAR2)
2798 IS
2799 select
2800 substr(p_phone_no,0,(select instr(p_phone_no,'-')from dual)-1),
2801 substr(p_phone_no,(select instr(p_phone_no,'-')from dual)+1)
2802 from dual;
2803 /*Bug 10238421 - End*/
2804
2805 cursor c_deductee_cnt
2806 is
2807 select count(1), nvl(sum(tcs_amt + surcharge_amt + cess_amt),0)
2808 from
2809 jai_ar_etcs_t
2810 where
2811 batch_id = ln_batch_id ;
2812
2813 CURSOR c_quart_deductee_cnt(cp_batch_id IN NUMBER , cp_check_number IN NUMBER ) IS
2814 select sum ( count( distinct tcs_tax_rate ) )
2815 from jai_ar_etcs_t
2816 WHERE batch_id = cp_batch_id
2817 and check_number = cp_check_number
2818 group by source_document_id, tcs_tax_rate, exempted_flag ;
2819
2820
2821 cursor c_challan_cnt
2822 is
2823 select count(1)
2824 from
2825 ( select 1
2826 from
2827 jai_ar_etcs_t
2828 where
2829 batch_id = ln_batch_id
2830 group by
2831 NVL(challan_no, 'No Challan Number'),
2832 NVL(challan_date,lv_dummy_date),
2833 NVL(bank_branch_code,'No Bank Branch'),
2834 NVL(tcs_check_id , -1 )
2835 ) ;
2836
2837
2838 --Challan Detail
2839
2840 v_challan_dtl_slno number ;
2841 v_collection_code varchar2(1) ;
2842 ln_amt_of_oth number(14);
2843 v_tcs_section varchar2(5) ;
2844
2845 -- Quarterly Challan Detail
2846 v_nil_challan_indicator char(1);
2847 v_q_deductee_cnt number(9);
2848 v_last_bank_challan_no varchar2(1);
2849 v_last_transfer_voucher_no varchar2(1);
2850 v_transfer_voucher_no number(9);
2851 v_last_bank_branch_code varchar2(1);
2852 v_challan_lastDate varchar2(1);
2853 v_filler5 varchar2(1);
2854 v_last_total_depositAmt varchar2(1);
2855 v_remarks varchar2(14);
2856 V_ch_recHash varchar2(1);
2857 v_total_deposit number(15);
2858 v_bank_branch_code varchar2(7);
2859 v_ch_updIndicator varchar2(1);
2860 ln_amt_of_tds number(15);
2861
2862 v_check_number NUMBER;
2863
2864 CURSOR c_challan_records(p_batch_id IN NUMBER) IS
2865 select NVL(bank_branch_code,'No Bank Branch') bank_branch_code,
2866 NVL(challan_no,'No Challan Number') challan_no,
2867 NVL(challan_date,lv_dummy_date) challan_date,
2868 check_number check_number,
2869 tcs_check_id ,
2870 sum(tcs_amt + surcharge_amt + cess_amt ) total_tcs_amount,
2871 sum(tcs_amt) tcs_amt,
2872 sum(surcharge_amt) surcharge_amt,
2873 sum(cess_amt) cess_amt
2874 from jai_ar_etcs_t a
2875 where a.batch_id = p_batch_id
2876 group by NVL(bank_branch_code,'No Bank Branch'),
2877 NVL(challan_no,'No Challan Number'), NVL(challan_date,lv_dummy_date),
2878 check_number, tcs_check_id;
2879
2880 cd c_challan_records%ROWTYPE ;
2881
2882 cursor c_book_entry(cp_check_id number)
2883 is
2884 select nvl(book_entry_deposited,'N')
2885 from jai_ap_rgm_payments
2886 where check_id = cp_check_id ;
2887
2888 v_book_entry VARCHAR2(1) ;
2889
2890 -- Deductee Detail
2891 v_challan_line_num number ;
2892 v_party_name hz_parties.party_name%type ;
2893 v_deductee_state_code number;
2894 v_reason_for_nDeduction varchar2(1);
2895 ln_diff_rate number ;
2896 v_deductee_slno number ;
2897 v_book_ent_oth varchar2(1);
2898 v_filler6 varchar2(1) ;
2899 v_filler number(14);
2900 v_section_code varchar2(5) ;
2901
2902 -- Quarterly Deductee detail
2903 v_dh_mode varchar2(1);
2904 v_emp_serial_no varchar2(1);
2905 v_last_emp_pan varchar2(1);
2906 v_last_emp_pan_refno varchar2(1);
2907 v_party_pan_ref_no varchar2(10);
2908 v_last_total_tax_deducted varchar2(1);
2909 v_last_total_tax_deposit varchar2(1);
2910 v_deposit_date varchar2(1);
2911 v_grossingUp_ind varchar2(1);
2912 v_certificate_issue_date varchar2(1);
2913 v_remarks2 varchar2(1);
2914 v_remarks3 varchar2(1);
2915 v_dh_recHash varchar2(1);
2916 v_quart_deductee_code varchar2(1);
2917
2918
2919 /*
2920 Bug 8429168 - Customer Account ID was passed as parameter
2921 Hence joined hz_parties and hz_cust_accounts to get Party Name
2922 */
2923 cursor c_cust_name(cp_customer_id in number)
2924 is
2925 select hp.party_name
2926 from hz_parties hp, hz_cust_accounts hca
2927 where hca.cust_account_id = cp_customer_id
2928 and hca.party_id = hp.party_id;
2929
2930
2931 cursor c_cust_site_dtls (cp_party_site_id number)
2932 is
2933 select
2934 address1,
2935 address2,
2936 address3,
2937 address4,
2938 city,
2939 state,
2940 postal_code
2941 from
2942 ar_addresses_v
2943 where
2944 address_id = cp_party_site_id ;
2945
2946 v_site_dtls c_cust_site_dtls%rowtype;
2947
2948 cursor c_state_code(p_state_name in varchar2) is
2949 select meaning
2950 from fnd_common_lookups
2951 where lookup_type = 'IN_STATE'
2952 and lookup_code = p_state_name;
2953
2954 cursor c_deductee_records(p_batch_id in number, p_challan_line_num in number) is
2955 select
2956 party_id,challan_line_num, party_site_id,exempted_flag,
2957 party_code, party_pan,source_document_id,
2958 NVL(bank_branch_code,'No Bank Branch') bank_branch_code,
2959 NVL(challan_no,'No Challan Number') challan_no,
2960 NVL(challan_date,lv_dummy_date) challan_date,
2961 check_number,
2962 tcs_tax_rate,
2963 sum(line_amt) line_amount,
2964 max(certificate_issue_date) certificate_issue_date,
2965 max(source_document_date) transaction_date ,
2966 max(tcs_check_date) tcs_check_date,
2967 sum(tcs_amt) tcs_amt,
2968 sum(surcharge_amt) surcharge_amt,
2969 sum(cess_amt) cess_amt,
2970 sum(tcs_amt + surcharge_amt + cess_amt ) total_tcs_amount
2971 from jai_ar_etcs_t a
2972 where a.batch_id = p_batch_id and
2973 challan_line_num = NVL(p_challan_line_num, challan_line_num)
2974 group by
2975 challan_line_num, party_id, party_site_id,exempted_flag, tcs_tax_rate,
2976 check_number,party_code, party_pan,
2977 NVL(bank_branch_code,'No Bank Branch'),
2978 NVL(challan_no,'No Challan Number') ,
2979 NVL(challan_date,lv_dummy_date) ,
2980 source_document_id ;
2981
2982 dd c_deductee_records%ROWTYPE ;
2983
2984 PROCEDURE process_deductee_records
2985 IS
2986 v_deductee_total_tax_deducted number(15);
2987 v_quart_book_ent_oth varchar2(1);
2988 BEGIN
2989 OPEN c_deductee_records(ln_batch_id, v_challan_line_num) ;
2990 LOOP
2991 FETCH c_deductee_records INTO dd ;
2992 EXIT WHEN
2993 c_deductee_records%NOTFOUND ;
2994
2995 v_party_name := null;
2996 v_site_dtls := null;
2997 v_reason_for_nDeduction := null;
2998 v_filler := null;
2999 v_deductee_state_code := null;
3000 v_reason_for_nDeduction := null;
3001 ln_diff_rate :=null;
3002
3003 v_line_number := v_line_number + 1;
3004 v_deductee_slno := v_deductee_slno + 1;
3005
3006 OPEN c_cust_name(dd.party_id);
3007 FETCH c_cust_name INTO v_party_name;
3008 CLOSE c_cust_name;
3009
3010 OPEN c_cust_site_dtls(dd.party_site_id);
3011 FETCH c_cust_site_dtls INTO v_site_dtls;
3012 CLOSE c_cust_site_dtls;
3013
3014 OPEN c_state_code(v_site_dtls.state);
3015 FETCH c_state_code INTO v_deductee_state_code;
3016 CLOSE c_state_code;
3017
3018 IF v_deductee_state_code IS NULL THEN
3019 v_deductee_state_code := 99;
3020 END IF;
3021
3022 v_book_ent_oth := ' ';
3023 v_batch_number := '000000001' ;
3024 v_section_code := '206C ' ;
3025
3026 IF lv_etcs_yearly_returns = 'Y' THEN
3027
3028 IF dd.exempted_flag = 'SR' THEN
3029 v_reason_for_nDeduction := 'Y';
3030 ELSE
3031 v_reason_for_nDeduction := 'X';
3032 END IF;
3033
3034 create_deductee_detail
3035 (
3036 p_line_number => v_line_number,
3037 p_record_type => v_record_type,
3038 p_batch_number => v_batch_number,
3039 p_deductee_slno => v_deductee_slno,
3040 p_deductee_section => v_section_code,
3041 p_deductee_code => dd.party_code,
3042 p_deductee_pan => dd.party_pan,
3043 p_deductee_name => v_party_name,
3044 p_deductee_address1 => v_site_dtls.address1,
3045 p_deductee_address2 => v_site_dtls.address2,
3046 p_deductee_address3 => v_site_dtls.address3,
3047 p_deductee_address4 => v_site_dtls.address4,
3048 p_deductee_address5 => v_site_dtls.city,
3049 p_deductee_state => v_deductee_state_code,
3050 p_deductee_pin => v_site_dtls.postal_code,
3051 p_purchase_amount => dd.line_amount,
3052 p_payment_amount => dd.line_amount,
3053 p_payment_date => dd.challan_date,
3054 p_book_ent_oth => v_book_ent_oth,
3055 p_tax_rate => dd.tcs_tax_rate,
3056 p_filler6 => v_filler6,
3057 p_tax_deducted => dd.total_tcs_amount,
3058 p_tax_deducted_date => dd.transaction_date,
3059 p_tax_payment_date => dd.tcs_check_date,
3060 p_bank_branch_code => dd.bank_branch_code,
3061 p_challan_no => dd.challan_no,
3062 p_tds_certificate_date => dd.certificate_issue_date,
3063 p_reason_for_nDeduction => v_reason_for_nDeduction,
3064 p_filler7 => v_filler
3065 );
3066 ELSE
3067
3068 IF dd.exempted_flag = 'SR' THEN
3069 v_reason_for_nDeduction := 'B';
3070 ELSE
3071 v_reason_for_nDeduction := 'A';
3072 END IF;
3073
3074 IF dd.party_code = '01' THEN
3075 v_quart_deductee_code := '1' ;
3076 ELSIF dd.party_code = '02' THEN
3077 v_quart_deductee_code := '2' ;
3078 END IF ;
3079
3080 v_deductee_total_tax_deducted := dd.tcs_amt+dd.surcharge_amt + dd.cess_amt ;
3081 v_quart_book_ent_oth := 'N' ;
3082
3083 p_return_code := null ;
3084 p_return_message := null ;
3085 v_dh_mode :='O';
3086
3087 jai_etcs_pkg.validate_party_detail
3088 ( p_line_number => v_line_number ,
3089 p_record_type => v_record_type ,
3090 p_batch_number => v_batch_number ,
3091 p_challan_line_num => dd.challan_line_num ,
3092 p_party_slno => v_deductee_slno ,
3093 p_dh_mode => v_dh_mode ,
3094 p_quart_party_code => v_quart_deductee_code ,
3095 p_party_pan => dd.party_pan ,
3096 p_party_name => v_party_name ,
3097 p_tcs_amt => dd.tcs_amt,
3098 p_surcharge_amt => dd.surcharge_amt ,
3099 p_cess_amt => dd.cess_amt ,
3100 p_party_total_tax_deducted => v_deductee_total_tax_deducted,
3101 p_base_taxabale_amount => dd.tcs_amt ,
3102 p_gl_date => dd.challan_date ,
3103 p_book_ent_oth => v_book_ent_oth,
3104 p_tcs_tax_rate => dd.tcs_tax_rate,
3105 p_total_purchase => dd.tcs_amt,
3106 p_party_total_tax_deposit =>v_deductee_total_tax_deducted,
3107 p_return_code => p_return_code,
3108 p_return_message => p_return_message
3109 );
3110
3111 IF p_return_code = 'E' THEN
3112 IF lv_action = 'V' THEN
3113 INSERT INTO jai_ap_etds_errors_t
3114 (batch_id, record_type, reference_id, error_message) VALUES
3115 ( ln_batch_id,'DD', v_line_number, p_return_message ) ;
3116 ELSE
3117 p_ret_code := jai_constants.request_error ;
3118 p_err_buf := p_return_message ;
3119 RETURN ;
3120 END IF ;
3121 END IF ;
3122
3123 lv_generate_headers := null ;
3124 IF p_action <> 'V' THEN
3125 IF p_action = 'F' THEN
3126 lv_generate_headers := 'N' ;
3127 ELSIF p_action = 'H' THEN
3128 lv_generate_headers := 'Y' ;
3129 END IF ;
3130
3131 jai_etcs_pkg.create_quart_party_dtl
3132 (
3133 p_line_number => v_line_number,
3134 p_record_type => v_record_type,
3135 p_batch_number => v_batch_number,
3136 p_dh_challan_recNo => v_challan_dtl_slno,
3137 p_party_slno => v_deductee_slno,
3138 p_dh_mode => v_dh_mode,
3139 p_emp_serial_no => v_emp_serial_no,
3140 p_party_code => v_quart_deductee_code,
3141 p_last_emp_pan => v_last_emp_pan,
3142 p_party_pan => dd.party_pan,
3143 p_last_emp_pan_refno => v_last_emp_pan_refno,
3144 p_party_pan_refno => v_party_pan_ref_no,
3145 p_party_name => v_party_name,
3146 p_party_tcs_income_tax => dd.tcs_amt ,
3147 p_party_tcs_surcharge => dd.surcharge_amt,
3148 p_party_tcs_cess => dd.cess_amt,
3149 p_party_total_tax_deducted => v_deductee_total_tax_deducted,
3150 p_last_total_tax_deducted => v_last_total_tax_deducted,
3151 p_party_total_tax_deposit => v_deductee_total_tax_deducted,
3152 p_last_total_tax_deposit => v_last_total_tax_deposit,
3153 p_total_purchase => dd.tcs_amt,
3154 p_base_taxabale_amount => nvl(dd.tcs_amt,0) + nvl(dd.surcharge_amt,0) + nvl(dd.cess_amt,0),
3155 p_gl_date => dd.challan_date ,
3156 p_tcs_invoice_date => dd.challan_date,
3157 p_deposit_date => v_deposit_date,
3158 p_tcs_tax_rate => dd.tcs_tax_rate,
3159 p_grossingUp_ind => v_grossingUp_ind,
3160 p_book_ent_oth => v_quart_book_ent_oth,
3161 p_certificate_issue_date => v_certificate_issue_date,
3162 p_remarks1 => v_reason_for_nDeduction,
3163 p_remarks2 => v_remarks2,
3164 p_remarks3 => v_remarks3,
3165 p_dh_recHash => v_dh_recHash,
3166 p_generate_headers => lv_generate_headers
3167 );
3168
3169 END IF ;
3170
3171 END IF ;
3172
3173 UPDATE jai_ar_etcs_t
3174 SET deductee_line_num = v_line_number
3175 WHERE batch_id = ln_batch_id
3176 and challan_line_num = dd.challan_line_num
3177 and party_id = dd.party_id
3178 and party_site_id = dd.party_site_id
3179 and exempted_flag = dd.exempted_flag
3180 and NVL(bank_branch_code,'No Bank Branch') = NVL(dd.bank_branch_code,'No Bank Branch')
3181 and NVL(challan_no,'No Challan Number') = NVL(dd.challan_no,'No Challan Number')
3182 and NVL(challan_date,lv_dummy_date) = NVL(dd.challan_date,lv_dummy_date)
3183 and check_number = dd.check_number
3184 and tcs_tax_rate = dd.tcs_tax_rate
3185 and source_document_id = dd.source_document_id ;
3186
3187 END LOOP;
3188
3189 CLOSE c_deductee_records ;
3190 END process_deductee_records;
3191
3192 BEGIN
3193 lv_dummy_date := TO_DATE('01/01/1600', 'DD/MM/RRRR');
3194
3195 v_conc_request_id := FND_PROFILE.value('CONC_REQUEST_ID');
3196 SELECT JAI_AP_ETDS_T_S.nextval INTO ln_batch_id FROM DUAL;
3197 v_line_number := 0;
3198
3199
3200 IF NVL(p_period,'XX') = 'XX' THEN
3201 lv_etcs_yearly_returns := 'Y' ;
3202 FND_FILE.put_line(FND_FILE.log, '~~~~Ver:115.0~~~~ Start of eTCS File Creation for Yearly Returns
3203 Batch_id->'||ln_batch_id ||', Creation Date->'||to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss')||' ~~~~~~~~~~~~~~~~~~');
3204 ELSE
3205 lv_etcs_yearly_returns := 'N' ;
3206 FND_FILE.put_line(FND_FILE.log, '~~~~Ver:115.0~~~~ Start of eTCS File Creation for Quarterly returns
3207 Batch_id->'||ln_batch_id || 'Period : ' || p_period ||', Creation Date->' ||
3208 to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss')||' ~~~~~~~~~~~~~~~~~~');
3209 END IF ;
3210
3211 IF NVL(p_action,'X') <> 'V' THEN
3212 IF NVL(p_generate_headers,'X') = 'Y' or NVL(p_action,'X') = 'H' THEN
3213 jai_ap_tds_etds_pkg.v_debug_pad_char := ' ';
3214 jai_ap_tds_etds_pkg.v_generate_headers := TRUE;
3215 ELSE
3216 jai_ap_tds_etds_pkg.v_debug_pad_char := '';
3217 jai_ap_tds_etds_pkg.v_generate_headers := FALSE;
3218 END IF;
3219 END IF ;
3220
3221 IF length(p_tan_number) > 10 THEN
3222 FND_FILE.put_line(FND_FILE.log, 'Tan Number length is greater than 10 characters');
3223 RAISE_APPLICATION_ERROR(-20014, 'Tan Number length is greater than 10 characters', true);
3224 END IF;
3225
3226
3227 -- Date 26/05/2007 by sacsethi for bug 6153881
3228 -- Mark Legal_Entity_id as null
3229
3230 INSERT INTO JAI_AP_ETDS_REQUESTS(
3231 batch_id, request_id, legal_entity_id , org_tan_number, financial_year,
3232 tax_authority_id, tax_authority_site_id, organization_id,
3233 deductor_name, deductor_state, addr_changed_since_last_ret,
3234 --deductor_status, /*Bug 8880543 - Commented Deductor Status for eTDS/eTCS FVU Changes*/
3235 person_resp_for_deduction, designation_of_pers_resp, challan_start_date,
3236 challan_end_date, file_path, filename ,
3237 created_by ,creation_date , last_updated_by , last_update_date , last_update_login -- Date 28/06/2007 by sacsethi for bug 6157120
3238 ) VALUES (
3239 ln_batch_id, v_conc_request_id, null , p_tan_number, p_fin_year,
3240 p_tax_authority_id, p_tax_authority_site_id, p_organization_id,
3241 p_seller_name, p_seller_state, p_addrChangedSinceLastRet,
3242 --p_collector_status, /*Bug 8880543 - Commented Deductor Status for eTDS/eTCS FVU Changes*/
3243 p_persRespForCollection, p_desgOfPersResponsible, p_Start_Date,
3244 p_End_Date, p_file_path, p_filename ,
3245 fnd_global.user_id , sysdate , fnd_global.user_id , sysdate ,fnd_global.login_id -- Date 28/06/2007 by sacsethi for bug 6157120
3246 );
3247
3248 -- Fetching the Pan Number based on TAN
3249 OPEN c_pan_number(p_organization_id);
3250 FETCH c_pan_number INTO v_deductor_pan;
3251 CLOSE c_pan_number;
3252
3253 IF v_deductor_pan IS NULL THEN
3254 FND_FILE.put_line(FND_FILE.log, 'Pan Number cannot be retreived based on given TAN Number');
3255 RAISE_APPLICATION_ERROR(-20015, 'Pan Number cannot be retreived based on given TAN Number', true);
3256 END IF;
3257
3258 -- code ported from BL12.1.3 on 15-MAR-2013 by zhiwei.xin begin
3259 -- Fetching Start Date and End date of given Financial Year
3260 /*OPEN c_fin_year(p_tan_number, p_fin_year);
3261 FETCH c_fin_year INTO v_start_date, v_end_date;
3262 CLOSE c_fin_year;*/ --Commented by Chong for DTC ER 2012/09/11 Get TCS calendar from regime
3263 --Added by Chong for DTC ER 2012/09/11 Get TCS calendar from regime Start
3264 get_start_end_date(cp_attr_code => 'TCS_CALENDAR'
3265 ,cp_rgm_code => 'TCS'
3266 ,cp_fin_year => p_fin_year
3267 ,cp_start_date => v_start_date
3268 ,cp_end_date => v_end_date);
3269 --Added by Chong for DTC ER 2012/09/11 Get TCS calendar from regime End
3270
3271 -- code ported from BL12.1.3 on 15-MAR-2013 by zhiwei.xin end.
3272
3273 IF v_start_date IS NULL OR v_end_date IS NULL THEN
3274 FND_FILE.put_line(FND_FILE.log, 'Cannot get values for Financial Year and Assessment Year');
3275 RAISE_APPLICATION_ERROR( -20016, 'Cannot get values for Financial Year and Assessment Year');
3276 END IF;
3277
3278 -- Fetching Location linked to Input Organization from where address details are captured
3279 OPEN c_location_linked_to_org(p_organization_id);
3280 FETCH c_location_linked_to_org INTO v_location_id;
3281 CLOSE c_location_linked_to_org;
3282
3283 -- Shall Populate the Address Details of Batch Header
3284 OPEN c_address_details(v_location_id);
3285 FETCH c_address_details INTO v_location_code, v_tan_address1, v_tan_address2, v_tan_address3, v_tan_address4,
3286 v_tan_address5, v_postal_code;
3287 CLOSE c_address_details;
3288
3289 -- checks for Pincode related to Address location
3290 IF length(v_postal_code) > 6 THEN
3291 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||')');
3292 END IF;
3293
3294 BEGIN
3295 v_tan_pin := to_number(v_postal_code);
3296 EXCEPTION
3297 WHEN VALUE_ERROR THEN
3298 RAISE_APPLICATION_ERROR(-20010, 'Postal Code of Location should be a 6 digit number. Location Code (id):'||v_location_code||' ('||v_location_id||')');
3299 END;
3300
3301 populate_details
3302 (
3303 p_batch_id => ln_batch_id ,
3304 p_org_tan_num => p_tan_number ,
3305 p_tax_authority_id => p_tax_authority_id ,
3306 p_tax_authority_site_id => p_tax_authority_site_id ,
3307 p_from_date => p_Start_Date ,
3308 p_to_date => p_end_Date ,
3309 p_collection_code => p_collection_code
3310 );
3311
3312
3313 BEGIN
3314 jai_etcs_pkg.openFile(p_file_path, p_filename);
3315 EXCEPTION
3316 WHEN OTHERS THEN
3317 FND_FILE.put_line(FND_FILE.log, 'Error Occured during opening of file(1):'||SQLERRM);
3318 RAISE_APPLICATION_ERROR(-20016, 'Error Occured(1):'||SQLERRM, true);
3319 END;
3320
3321 IF p_action <> 'V' THEN
3322 FND_FILE.put_line(FND_FILE.log, 'Start File Header');
3323 END IF ;
3324
3325 -- File Header (42 Chars)
3326 v_line_number := v_line_number + 1;
3327 v_record_type := 'FH';
3328 v_file_type := 'NS3' ;
3329 v_upload_type := 'R' ;
3330 v_file_sequence_number := 1;
3331 v_seller_tan := p_tan_number;
3332 v_file_creation_date := sysdate;
3333 v_number_of_batches := 1;
3334 v_quartfile_type :='TC1';
3335 v_uploader_type :='D';
3336
3337 get_attr_value (p_organization_id, 'RETURN_PREP_UTILITY', v_return_prep_util, v_err, v_return_message);
3338 chk_err(v_err, v_return_message);
3339
3340 IF lv_etcs_yearly_returns = 'Y' THEN
3341 IF p_generate_headers = 'Y' THEN
3342 jai_etcs_pkg.create_fh(ln_batch_id);
3343 END IF;
3344
3345 create_file_header
3346 (
3347 p_line_number => v_line_number,
3348 p_record_type => v_record_type,
3349 p_file_type => v_file_type,
3350 p_upload_type => v_upload_type,
3351 p_file_creation_date => v_file_creation_date,
3352 p_file_sequence_number => v_file_sequence_number,
3353 p_deductor_tan => v_seller_tan,
3354 p_number_of_batches => v_number_of_batches
3355 );
3356
3357 ELSE
3358 IF p_action = 'H' THEN
3359 jai_etcs_pkg.create_quarterly_fh(ln_batch_id, p_period,/* Added the below 5 parameters for bug 12597773*/p_RespPers_flat_no, p_RespPers_prem_bldg,
3360 p_RespPers_rd_st_lane, p_RespPers_area_loc,
3361 p_RespPers_tn_cty_dt, /*p_RespPersAddress,*/ p_RespPersState, p_RespPersPin, p_RespPersAddrChange );
3362 END IF;
3363
3364 p_return_code := null ;
3365 p_return_message := null ;
3366
3367 jai_etcs_pkg.validate_file_header
3368 ( p_line_number => v_line_number ,
3369 p_record_type => v_record_type ,
3370 p_quartfile_type => v_quartfile_type,
3371 p_upload_type => v_upload_type,
3372 p_file_creation_date => v_file_creation_date,
3373 p_file_sequence_number => v_file_sequence_number,
3374 p_uploader_type => v_uploader_type ,
3375 p_collector_tan => v_seller_tan ,
3376 p_number_of_batches => v_number_of_batches,
3377 p_period => p_period,
3378 p_start_date => p_start_date,
3379 p_end_date => p_end_date,
3380 p_fin_year => to_char(v_start_date,'YYYY'),
3381 p_return_prep_util => v_return_prep_util, /*Bug 8880543 - Added for eTCS/eTCS FVU Changes*/
3382 p_return_code => p_return_code,
3383 p_return_message => p_return_message
3384 );
3385
3386 IF p_return_code = 'E' THEN
3387 IF lv_action = 'V' THEN
3388 INSERT INTO jai_ap_etds_errors_t
3389 (batch_id, record_type, error_message) values
3390 ( ln_batch_id, 'FH', p_return_message ) ;
3391 ELSE
3392 p_ret_code := jai_constants.request_error ;
3393 p_err_buf := p_return_message ;
3394 RETURN ;
3395 END IF ;
3396 END IF ;
3397
3398 lv_generate_headers := null ;
3399 IF p_action <> 'V' THEN
3400 IF p_action = 'F' THEN
3401 lv_generate_headers := 'N' ;
3402 ELSIF p_action = 'H' THEN
3403 lv_generate_headers := 'Y' ;
3404 END IF ;
3405
3406 jai_etcs_pkg.create_quarterly_file_header
3407 (
3408 p_line_number => v_line_number,
3409 p_record_type => v_record_type,
3410 p_file_type => v_quartfile_type,
3411 p_upload_type => v_upload_type,
3412 p_file_creation_date => v_file_creation_date,
3413 p_file_sequence_number => v_file_sequence_number,
3414 p_uploader_type => v_uploader_type,
3415 p_collector_tan => v_seller_tan,
3416 p_number_of_batches => v_number_of_batches,
3417 p_return_prep_util => v_return_prep_util, /*Bug 8880543 - Added for eTDS/eTCS FVU Changes*/
3418 p_fh_recordHash => v_fh_recordHash,
3419 p_fh_fvuVersion => v_fh_fvuVersion,
3420 p_fh_fileHash => v_fh_fileHash,
3421 p_fh_samVersion => v_fh_samVersion,
3422 p_fh_samHash => v_fh_samHash,
3423 p_fh_scmVersion => v_fh_scmVersion,
3424 p_fh_scmHash => v_fh_scmHash,
3425 p_fh_consFileHash => v_fh_consFileHash, /*FVU 3.4*/
3426 p_generate_headers => lv_generate_headers
3427 ) ;
3428
3429 END IF ;
3430
3431 END IF ;
3432
3433 -- Batch Header (411 Chars)
3434 v_line_number := v_line_number + 1;
3435 v_record_type := 'BH';
3436 v_batch_number := 1;
3437 v_form_number := '27E ';
3438 v_financial_year := to_char(v_start_date, 'YYYY')||to_char(v_end_date, 'YY');
3439 v_assessment_year := to_char(add_months(v_start_date,12), 'YYYY')||to_char(add_months(v_end_date,12), 'YY');
3440 v_seller_name := p_seller_name;
3441 v_addrChangedSinceLastReturn := p_addrChangedSinceLastRet;
3442 --v_seller_status := 'O'; /*Bug 8880543 - To be fetched from Regime Registration Setup*/
3443 v_quarterlyOrYearly := 'Y';
3444 v_personNameRespForDedection := p_persRespForCollection;
3445 v_personDesgnRespForDedection := p_desgOfPersResponsible;
3446 v_tan_state_code := to_number(p_seller_state);
3447 v_ao_approval :='N';
3448 v_quart_form_number :='27EQ';
3449
3450
3451 open c_deductee_cnt;
3452 fetch c_deductee_cnt into v_deductee_cnt, v_totTaxDeductedAsPerDeductee ;
3453 close c_deductee_cnt ;
3454
3455 open c_challan_cnt ;
3456 fetch c_challan_cnt into v_challan_cnt ;
3457 close c_challan_cnt ;
3458
3459 v_totTaxDeductedAsPerChallan := v_totTaxDeductedAsPerDeductee ;
3460
3461 IF p_action <> 'V' THEN
3462 FND_FILE.put_line(FND_FILE.log, 'Batch Header');
3463 END IF ;
3464
3465 v_ack_num_tan_app := NULL;
3466 v_pro_rcpt_num_org_ret := nvl(p_pro_rcpt_num_org_ret,0);
3467
3468 /*Bug 8880543 - Fetch Attribute values for eTDS/eTCS FVU Changes - Start*/
3469 get_attr_value (p_organization_id, 'COLLECTOR_TYPE', v_seller_type, v_err, v_return_message);
3470 chk_err(v_err, v_return_message);
3471 get_attr_value (p_organization_id, 'STATE', v_state_name, v_err, v_return_message);
3472 chk_err(v_err, v_return_message);
3473 get_attr_value (p_organization_id, 'MINISTRY_NAME', v_ministry_name, v_err, v_return_message);
3474 chk_err(v_err, v_return_message);
3475 get_attr_value (p_organization_id, 'PAO_CODE', v_pao_code, v_err, v_return_message);
3476 chk_err(v_err, v_return_message);
3477 get_attr_value (p_organization_id, 'DDO_CODE', v_ddo_code, v_err, v_return_message);
3478 chk_err(v_err, v_return_message);
3479 get_attr_value (p_organization_id, 'PAO_REGISTRATION_NO', v_pao_registration_no, v_err, v_return_message);
3480 chk_err(v_err, v_return_message);
3481 get_attr_value (p_organization_id, 'DDO_REGISTRATION_NO', v_ddo_registration_no, v_err, v_return_message);
3482 chk_err(v_err, v_return_message);
3483 /*Bug 10238421 - Start*/
3484 get_attr_value (p_organization_id, 'COLLECTOR_EMAIL', v_collector_email, v_err, v_return_message);
3485 chk_err(v_err, v_return_message);
3486 get_attr_value (p_organization_id, 'COLLECTOR_PHONE_NO', v_collector_phone_no, v_err, v_return_message);
3487 chk_err(v_err, v_return_message);
3488 get_attr_value (p_organization_id, 'RESP_PERSON_EMAIL', v_RespPerson_email, v_err, v_return_message);
3489 chk_err(v_err, v_return_message);
3490 get_attr_value (p_organization_id, 'RESP_PERSON_PHONE_NO', v_resp_person_phone_no, v_err, v_return_message);
3491 chk_err(v_err, v_return_message);
3492 get_attr_value (p_organization_id, 'RESP_PERSON_MOBILE_NO', v_RespPerson_mobile, v_err, v_return_message);
3493 chk_err(v_err, v_return_message);
3494
3495 OPEN c_split_phone_no(v_collector_phone_no);
3496 FETCH c_split_phone_no INTO v_collector_std_code, v_collector_ph_no;
3497 CLOSE c_split_phone_no;
3498
3499 OPEN c_split_phone_no(v_resp_person_phone_no);
3500 FETCH c_split_phone_no INTO v_resp_person_std_code, v_resp_person_ph_no;
3501 CLOSE c_split_phone_no;
3502 /*Bug 10238421 - End*/
3503
3504 IF v_ministry_name = '99' THEN
3505 select meaning into v_ministry_name_other
3506 from ja_lookups lkup
3507 where lkup.lookup_type = 'JAI_MIN_NAME_VALUES'
3508 and lkup.lookup_code = '99';
3509 else
3510 v_ministry_name_other := NULL;
3511 END IF;
3512
3513 IF lv_etcs_yearly_returns = 'Y' THEN
3514
3515 IF p_generate_headers = 'Y' THEN
3516 jai_ap_tds_etds_pkg.create_bh;
3517 END IF;
3518
3519 jai_ap_tds_etds_pkg.create_batch_header(
3520 p_line_number => v_line_number,
3521 p_record_type => v_record_type,
3522 p_batch_number => v_batch_number,
3523 p_challan_count => v_challan_cnt,
3524 p_deductee_count => v_deductee_cnt,
3525 p_form_number => v_form_number,
3526 p_filler1 => v_filler1,
3527 p_deductor_tan => v_seller_tan,
3528 p_pan_of_tan => v_deductor_pan,
3529 p_assessment_year => v_assessment_year,
3530 p_financial_year => v_financial_year,
3531 p_deductor_name => v_seller_name,
3532 p_tan_address1 => v_tan_address1,
3533 p_tan_address2 => v_tan_address2,
3534 p_tan_address3 => v_tan_address3,
3535 p_tan_address4 => v_tan_address4,
3536 p_tan_address5 => v_tan_address5,
3537 p_tan_state => v_tan_state_code,
3538 p_tan_pin => v_tan_pin,
3539 p_chng_addr_since_last_return => v_addrChangedSinceLastReturn,
3540 p_type_of_deductor => v_seller_type, /*Modified the parameter Name - Bug 8880543*/
3541 p_quart_year_return => v_quarterlyOrYearly,
3542 p_pers_resp_for_deduction => v_personNameRespForDedection,
3543 p_pers_designation => v_personDesgnRespForDedection,
3544 p_tot_tax_dedected_challan => v_totTaxDeductedAsPerChallan,
3545 p_tot_tax_dedected_deductee => v_totTaxDeductedAsPerDeductee,
3546 p_filler2 => v_filler2,
3547 p_filler3 => v_filler3,
3548 p_ack_num_tan_app => v_ack_num_tan_app,
3549 p_pro_rcpt_num_org_ret => v_pro_rcpt_num_org_ret,
3550 p_rrr_number => v_bh_RRRno,
3551 p_rrr_date => v_bh_RRRdate
3552 );
3553
3554 ELSE
3555 IF p_action = 'H' THEN
3556 jai_etcs_pkg.create_quarterly_bh;
3557 END IF;
3558
3559 p_return_code := null ;
3560 p_return_message := null ;
3561
3562 validate_batch_header
3563 ( p_line_number => v_line_number ,
3564 p_record_type => v_record_type ,
3565 p_batch_number => v_batch_number ,
3566 p_challan_cnt => v_challan_cnt ,
3567 p_quart_form_number => v_quart_form_number ,
3568 p_collector_tan => v_seller_tan ,
3569 p_pan_of_tan => v_deductor_pan, /*Bug 8880543 - Added for PAN Number Validation for eTDS/eTCS FVU Changes*/
3570 p_assessment_year => v_assessment_year,
3571 p_financial_year => v_financial_year ,
3572 p_collector_name => v_seller_name ,
3573 p_tan_address1 => v_tan_address1 ,
3574 p_tan_state_code => v_tan_state_code ,
3575 p_tan_pin => v_tan_pin ,
3576 p_collector_type => v_seller_type , /*Modified the parameter Name - Bug 8880543*/
3577 p_addrChangedSinceLastReturn => v_addrChangedSinceLastReturn,
3578 p_personNameRespForCollection => v_personNameRespForDedection,
3579 p_personDesgnRespForCollection => v_personDesgnRespForDedection,
3580 /* Bug 12597773- Start*/
3581 --p_RespPersAddress => p_RespPersAddress ,
3582 p_RespPers_flat_no => p_RespPers_flat_no ,
3583 p_RespPers_prem_bldg => p_RespPers_prem_bldg ,
3584 p_RespPers_rd_st_lane => p_RespPers_rd_st_lane ,
3585 p_RespPers_area_loc => p_RespPers_area_loc ,
3586 p_RespPers_tn_cty_dt => p_RespPers_tn_cty_dt ,
3587 /* Bug 12597773 -End */
3588 p_RespPersState => p_RespPersState ,
3589 p_RespPersPin => p_RespPersPin ,
3590 p_RespPersAddrChange => p_RespPersAddrChange ,
3591 p_totTaxCollectedAsPerParty => v_totTaxDeductedAsPerDeductee,
3592 p_ao_approval => v_ao_approval,
3593 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - Start*/
3594 p_state_name => v_state_name,
3595 p_pao_code => v_pao_code,
3596 p_ddo_code => v_ddo_code,
3597 p_ministry_name => v_ministry_name,
3598 p_pao_registration_no => v_pao_registration_no,
3599 p_ddo_registration_no => v_ddo_registration_no,
3600 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - End*/
3601 /*Bug 10238421 - Start*/
3602 p_RespPerson_mobile => v_RespPerson_mobile,
3603 p_collector_phone_no => v_collector_phone_no,
3604 p_resp_person_phone_no => v_resp_person_phone_no,
3605 /*Bug 10238421 - End*/
3606 p_return_code => p_return_code,
3607 p_return_message => p_return_message
3608 );
3609
3610
3611 IF p_return_code = 'E' THEN
3612 IF lv_action = 'V' THEN
3613 insert into jai_ap_etds_errors_t(batch_id, record_type, error_message) values
3614 ( ln_batch_id, 'BH', p_return_message ) ;
3615 ELSE
3616 p_ret_code := jai_constants.request_error ;
3617 p_err_buf := p_return_message ;
3618 RETURN ;
3619 END IF ;
3620 END IF ;
3621
3622
3623 lv_generate_headers := null ;
3624 IF p_action <> 'V' THEN
3625 IF p_action = 'F' THEN
3626 lv_generate_headers := 'N' ;
3627 ELSIF p_action = 'H' THEN
3628 lv_generate_headers := 'Y' ;
3629 END IF ;
3630
3631 jai_etcs_pkg.create_quarterly_batch_header
3632 (
3633 p_line_number => v_line_number,
3634 p_record_type => v_record_type,
3635 p_batch_number => v_batch_number,
3636 p_challan_count => v_challan_cnt,
3637 p_form_number => v_quart_form_number,
3638 p_trn_type => v_bh_trnType,
3639 p_batchUpd => v_bh_batchUpd,
3640 p_org_token_no => v_bh_org_token_no, /*Bug 10238421*/
3641 p_prev_token_no => v_bh_prev_token_no, /*Bug 10238421*/
3642 p_token_no => v_bh_token_no, /*Bug 10238421*/
3643 p_token_date => v_bh_token_date, /*Bug 10238421*/
3644 p_collector_last_tan => v_bh_deductor_last_tan,
3645 p_collector_tan => v_seller_tan,
3646 p_receipt_number => v_receipt_number, /*Bug 10238421*/
3647 p_collector_pan => v_deductor_pan,
3648 p_assessment_year => v_assessment_year,
3649 p_financial_year => v_financial_year,
3650 p_period => p_period,
3651 p_collector_name => v_seller_name,
3652 p_collector_branch => v_deductor_branch,
3653 p_tan_address1 => v_tan_address1,
3654 p_tan_address2 => v_tan_address2,
3655 p_tan_address3 => v_tan_address3,
3656 p_tan_address4 => v_tan_address4,
3657 p_tan_address5 => v_tan_address5,
3658 p_tan_state_code => v_tan_state_code,
3659 p_tan_pin => v_tan_pin,
3660 p_collector_email => v_collector_email, /*Bug 10238421*/
3661 p_collector_stdCode => v_collector_std_code, /*Bug 10238421*/
3662 p_collector_phoneNo => v_collector_ph_no, /*Bug 10238421*/
3663 p_addrChangedSinceLastReturn => v_addrChangedSinceLastReturn,
3664 p_type_of_collector => v_seller_type, /*Modified Seller Status to Seller Type - Bug 8880543*/
3665 p_pers_resp_for_collection => v_personNameRespForDedection,
3666 p_RespPerson_designation => v_personDesgnRespForDedection,
3667 p_RespPerson_address1 => p_RespPers_flat_no, -- bug 12597773
3668 p_RespPerson_address2 => p_RespPers_prem_bldg, -- bug 12597773
3669 p_RespPerson_address3 => p_RespPers_rd_st_lane, -- bug 12597773
3670 p_RespPerson_address4 => p_RespPers_area_loc, -- bug 12597773
3671 p_RespPerson_address5 => p_RespPers_tn_cty_dt, -- bug 12597773
3672 p_RespPerson_state => p_RespPersState,
3673 p_RespPerson_pin => p_RespPersPin,
3674 p_RespPerson_email => v_RespPerson_email,
3675 p_RespPerson_mobile => v_RespPerson_mobile, /*Bug 10238421*/
3676 p_RespPerson_stdCode => v_resp_person_std_code, /*Bug 10238421*/
3677 p_RespPerson_phoneNo => v_resp_person_ph_no, /*Bug 10238421*/
3678 p_RespPerson_addressChange => p_RespPersAddrChange,
3679 p_totTaxCollectedAsPerChallan => round(v_totTaxDeductedAsPerDeductee), -- decimal should be .00
3680 p_unmatch_challan_cnt => v_unmatch_challan_cnt,
3681 p_salaryRecords_count => v_bh_salaryRecords_count,
3682 p_gross_total => v_bh_gross_total,
3683 p_ao_approval => v_ao_approval ,
3684 p_ao_approval_number => v_ao_approval_number,
3685 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - Start*/
3686 p_last_collector_type => v_last_collector_type,
3687 p_state_name => v_state_name,
3688 p_pao_code => v_pao_code,
3689 p_ddo_code => v_ddo_code,
3690 p_ministry_name => v_ministry_name,
3691 p_ministry_name_other => v_ministry_name_other,
3692 p_filler2 => v_filler2,
3693 p_pao_registration_no => v_pao_registration_no,
3694 p_ddo_registration_no => v_ddo_registration_no,
3695 /*Bug 8880543 - Added for eTDS/eTCS FVU Changes - End*/
3696 p_recHash => v_bh_recHash,
3697 p_generate_headers => lv_generate_headers
3698 ) ;
3699 END IF ;
3700 END IF ;
3701
3702 IF p_action <> 'V' THEN
3703 FND_FILE.put_line(FND_FILE.log, 'Challan Detail');
3704 END IF ;
3705
3706 v_record_type := 'CD';
3707
3708 IF lv_etcs_yearly_returns = 'Y' THEN
3709 IF p_generate_headers = 'Y' THEN
3710 create_cd;
3711 END IF ;
3712 END IF;
3713
3714 v_challan_dtl_slno := 0;
3715
3716 OPEN c_challan_records(ln_batch_id) ;
3717 LOOP
3718 FETCH c_challan_records INTO cd ;
3719 EXIT WHEN
3720 c_challan_records%NOTFOUND ;
3721
3722 v_line_number := v_line_number + 1;
3723 v_challan_dtl_slno := v_challan_dtl_slno + 1;
3724 ln_amt_of_oth := 0;
3725 v_record_type := 'CD';
3726 v_tcs_section := '206C' ;
3727 v_collection_code := p_collection_code ;
3728 v_check_number := cd.check_number;
3729
3730 IF cd.challan_date = lv_dummy_date THEN
3731 cd.challan_date := to_date(null) ;
3732 END IF ;
3733
3734 IF lv_etcs_yearly_returns = 'Y' THEN
3735
3736 jai_ap_tds_etds_pkg.create_challan_detail
3737 (
3738 p_line_number => v_line_number,
3739 p_record_type => v_record_type,
3740 p_batch_number => v_batch_number,
3741 p_challan_slno => v_challan_dtl_slno,
3742 p_challan_section => v_tcs_section,
3743 p_amount_of_tds => cd.tcs_amt,
3744 p_amount_of_surcharge => cd.surcharge_amt,
3745 p_amount_of_cess => cd.cess_amt,
3746 p_amount_of_int => ln_amt_of_oth,
3747 p_amount_of_oth => ln_amt_of_oth,
3748 p_amount_deducted => cd.total_tcs_amount,
3749 p_challan_num => cd.challan_no,
3750 p_challan_date => cd.challan_date,
3751 p_bank_branch_code => cd.bank_branch_code,
3752 p_check_number => cd.check_number,
3753 p_tds_dep_by_book => 'N' ,
3754 p_filler4 => v_collection_code
3755 ) ;
3756
3757 ELSE
3758
3759 IF cd.challan_no = 'No Challan Number' THEN
3760 cd.challan_no := null ;
3761 END IF ;
3762
3763 OPEN c_quart_deductee_cnt(ln_batch_id, cd.check_number) ;
3764 FETCH c_quart_deductee_cnt INTO v_q_deductee_cnt;
3765 CLOSE c_quart_deductee_cnt ;
3766
3767 open c_book_entry(cd.tcs_check_id) ;
3768 fetch c_book_entry into v_book_entry ;
3769 close c_book_entry ;
3770
3771 v_total_deposit := cd.tcs_amt + cd.surcharge_amt + cd.cess_amt;
3772 v_nil_challan_indicator := 'N' ;
3773
3774 jai_ap_tds_etds_pkg.check_numeric(v_bank_branch_code, 'Check Number : ' || cd.check_number || ' Bank Branch Code is not a Numeric Value ', p_action);
3775
3776 FND_FILE.put_line(FND_FILE.log, 'create challan quarterly' );
3777
3778 IF p_action = 'H' THEN
3779 jai_etcs_pkg.create_quarterly_cd;
3780 END IF ;
3781
3782 IF cd.challan_no IS NULL THEN
3783 v_bank_branch_code := null ;
3784 ELSE
3785 v_bank_branch_code := substr(cd.bank_branch_code,1,7);
3786 END IF ;
3787
3788
3789 p_return_code := null ;
3790 p_return_message := null ;
3791
3792 FND_FILE.put_line(FND_FILE.log, 'Validate Challan Detail' );
3793
3794 validate_challan_detail
3795 (
3796 p_line_number => v_line_number ,
3797 p_record_type => v_record_type ,
3798 p_batch_number => v_batch_number ,
3799 p_challan_dtl_slno => v_challan_dtl_slno ,
3800 p_party_cnt => v_q_deductee_cnt ,
3801 p_nil_challan_indicat => v_nil_challan_indicator,
3802 p_tcs_section => v_collection_code,
3803 p_tcs_amt => cd.tcs_amt ,
3804 p_surcharge_amt => cd.surcharge_amt ,
3805 p_cess_amt => cd.cess_amt ,
3806 p_amt_of_oth => ln_amt_of_oth ,
3807 p_tcs_amount => cd.total_tcs_amount ,
3808 p_total_income_tcs => v_total_deposit ,
3809 p_challan_no => cd.challan_no,
3810 p_bank_branch_code => cd.bank_branch_code,
3811 p_challan_Date => cd.challan_date,
3812 p_check_number => cd.check_number,
3813 p_amt_of_int => round(ln_amt_of_oth),
3814 p_total_deposit => v_total_deposit,
3815 p_tcs_income_tax => cd.tcs_amt,
3816 p_tcs_surcharge => cd.surcharge_amt,
3817 p_tcs_cess => cd.cess_amt,
3818 p_tcs_interest_amt => 0,
3819 p_tcs_other_amt => 0,
3820 p_return_code => p_return_code,
3821 p_return_message => p_return_message
3822 );
3823
3824
3825 IF p_return_code = 'E' THEN
3826 IF lv_action = 'V' THEN
3827 insert into jai_ap_etds_errors_t
3828 (batch_id, record_type, reference_id, error_message) values
3829 ( ln_batch_id, 'CD', v_line_number, p_return_message ) ;
3830 ELSE
3831 p_ret_code := jai_constants.request_error ;
3832 p_err_buf := p_return_message ;
3833 RETURN ;
3834 END IF ;
3835 END IF ;
3836
3837
3838 lv_generate_headers := null ;
3839 IF p_action <> 'V' THEN
3840 IF p_action = 'F' THEN
3841 lv_generate_headers := 'N' ;
3842 ELSIF p_action = 'H' THEN
3843 lv_generate_headers := 'Y' ;
3844 END IF ;
3845
3846 ln_amt_of_tds := cd.total_tcs_amount - round(cd.surcharge_amt) - round(cd.cess_amt) - round(ln_amt_of_oth) - round(ln_amt_of_oth) ;
3847
3848 /*Bug 8880543 - Challan Number to be used only if deductor type is not A or S - Start*/
3849 IF (v_seller_type in ('A', 'S')) THEN
3850 v_transfer_voucher_no := to_number(cd.challan_no);
3851 v_challan_number := NULL;
3852 v_bank_branch_code := NULL;
3853 v_check_number := NULL;
3854 v_book_entry := 'Y';
3855 else
3856 v_challan_number := to_number(cd.challan_no);
3857 v_transfer_voucher_no := NULL;
3858 v_book_entry := 'N';
3859 END IF;
3860 /*Bug 8880543 - Challan Number to be used only if deductor type is not A or S - End*/
3861
3862 create_quart_challan_dtl
3863 (
3864 p_line_number => v_line_number,
3865 p_record_type => v_record_type,
3866 p_batch_number => v_batch_number,
3867 p_challan_dtl_slno => v_challan_dtl_slno,
3868 p_collection_cnt => v_q_deductee_cnt,
3869 p_nil_challan_indicator => v_nil_challan_indicator,
3870 p_ch_updIndicator => v_ch_updIndicator,
3871 p_filler2 => v_filler2,
3872 p_filler3 => v_filler3,
3873 p_filler4 => v_filler4,
3874 p_last_bank_challan_no => v_last_bank_challan_no,
3875 p_bank_challan_no => v_challan_number,
3876 /*Bug 8880543 - Challan Number to be used only if deductory type is not A or S*/
3877 p_last_transfer_voucher_no => v_last_transfer_voucher_no,
3878 p_transfer_voucher_no => v_transfer_voucher_no,
3879 /*Bug 8880543 - Replaced v_transfer_voucher_no with Challan Number if Deductor Type is A or S*/
3880 p_last_bank_branch_code => v_last_bank_branch_code,
3881 p_bank_branch_code => v_bank_branch_code ,
3882 p_challan_lastDate => v_challan_lastDate,
3883 p_challan_Date => cd.challan_date,
3884 p_filler5 => v_filler5,
3885 p_filler6 => v_filler6,
3886 p_tcs_section => v_collection_code,
3887 p_tcs_amt => ln_amt_of_tds,
3888 p_surcharge_amt => round(cd.surcharge_amt),
3889 p_cess_amt => round(cd.cess_amt),
3890 p_amt_of_int => round(ln_amt_of_oth),
3891 p_amt_of_oth => round(ln_amt_of_oth),
3892 p_tcs_amount => cd.total_tcs_amount,
3893 p_last_total_depositAmt => v_last_total_depositAmt,
3894 p_total_deposit => v_total_deposit,
3895 p_tcs_income_tax => cd.tcs_amt,
3896 p_tcs_surcharge => cd.surcharge_amt,
3897 p_tcs_cess => cd.cess_amt,
3898 p_total_income_tcs => v_total_deposit,
3899 p_tcs_interest_amt => 0,
3900 p_tcs_other_amt => 0,
3901 p_check_number => v_check_number,
3902 p_book_entry => v_book_entry,
3903 p_remarks => v_remarks,
3904 p_ch_recHash => v_ch_recHash,
3905 p_generate_headers => lv_generate_headers
3906 ) ;
3907 END IF ;
3908 END IF ;
3909
3910 UPDATE jai_ar_etcs_t
3911 SET challan_line_num = v_line_number
3912 WHERE batch_id = ln_batch_id
3913 and nvl(challan_no,'No Challan Number') = nvl(cd.challan_no, 'No Challan Number')
3914 and nvl(challan_date, lv_dummy_date) = nvl(cd.challan_date, lv_dummy_date )
3915 and nvl(bank_branch_code, 'No Bank Branch') = nvl(cd.bank_branch_code, 'No Bank Branch')
3916 and check_number = cd.check_number;
3917
3918 IF p_action <> 'V' THEN
3919 FND_FILE.put_line(FND_FILE.log, 'Challan Line:'||v_line_number
3920 || ', ChlNum:' || cd.challan_no ||', ChlDate:'||cd.challan_date||', bankBr:'||cd.bank_branch_code
3921 );
3922 END IF ;
3923
3924 IF lv_etcs_yearly_returns= 'N' THEN
3925 v_record_type := 'DD';
3926 v_challan_line_num := v_line_number ;
3927
3928 IF p_action = 'H' THEN
3929 create_quarterly_dd;
3930 END IF;
3931
3932 v_deductee_slno := 0 ;
3933 process_deductee_records ;
3934 v_challan_line_num := null ;
3935 END IF;
3936
3937 END LOOP;
3938
3939 CLOSE c_challan_records ;
3940
3941 IF lv_etcs_yearly_returns= 'Y' THEN
3942
3943 v_record_type := 'DD';
3944
3945 IF p_action <> 'V' THEN
3946 FND_FILE.put_line(FND_FILE.log, 'Deductee Detail');
3947 END IF ;
3948
3949 IF p_generate_headers = 'Y' THEN
3950 create_dd;
3951 END IF;
3952
3953 v_challan_line_num := null ;
3954 v_deductee_slno := 0 ;
3955 process_deductee_records ; -- internal procedure call
3956
3957 END IF ;
3958
3959 IF p_action = 'V' THEN
3960
3961 FND_FILE.put_line(FND_FILE.log,' LISTING THE ERRORS IN THIS BATCH ' );
3962 FND_FILE.put_line(FND_FILE.log,'-------------------------------------------------------------------- ' );
3963
3964 ln_errors_exist := 0;
3965
3966 FOR rec_get_errors IN c_get_errors(ln_batch_id)
3967 LOOP
3968 ln_errors_exist := 1 ;
3969 FND_FILE.put_line(FND_FILE.log, rec_get_errors.Error_Message );
3970 END LOOP ;
3971
3972 IF ln_errors_exist = 0 THEN
3973 FND_FILE.put_line(FND_FILE.log,' File Validation Successful. No Errors Found !! ' );
3974 END IF ;
3975
3976 FND_FILE.put_line(FND_FILE.log,'-------------------------------------------------------------------- ' );
3977 FND_FILE.put_line(FND_FILE.log,' END OF ERRORS IN THIS BATCH ' );
3978
3979 END IF ;
3980
3981 jai_etcs_pkg.closeFile;
3982
3983 IF p_action <> 'V' THEN
3984 FND_FILE.put_line(FND_FILE.log, '~~~~~~~~~~~~~~~ End of eTCS File Creation ~~~~~~~~~~~~~~~~~~');
3985 END IF ;
3986 /*Bug 12597773 Start*/
3987 if (lv_etcs_yearly_returns='N') then
3988 open c_get_state_desc ;
3989 fetch c_get_state_desc into lv_state_desc ;
3990 close c_get_state_desc ;
3991 lv_request_desc := 'India - TCS Form 27B for Batch Id '|| ln_batch_id; /*ln_batch_id*/
3992
3993 lv_result := FND_REQUEST.set_mode(true);
3994 lv_req_id := FND_REQUEST.submit_request(
3995 'JA', 'JAINTCSB', lv_request_desc, '', FALSE,
3996 p_tan_number, --Org Tan Num
3997 p_organization_id, --Organization Id
3998 p_fin_year, --Fin Year
3999 p_period, --Quarter
4000 p_pro_rcpt_num_org_ret, --Previous Receipt Number
4001 p_collection_code, --Collection code
4002 p_persRespForCollection, --Person Responsible
4003 p_desgOfPersResponsible, --Responsible person Designation
4004 p_RespPers_flat_no, -- Responsible person Flat No.
4005 p_RespPers_prem_bldg, -- Name of premises/Bldg
4006 p_RespPers_rd_st_lane, -- Road/Street/Lane
4007 p_RespPers_area_loc, -- Area/Location
4008 p_RespPers_tn_cty_dt, -- Town/city/District
4009 lv_state_desc, -- Responsible person State
4010 p_RespPersPin, -- Responsible Person Pin
4011 v_resp_person_phone_no, -- Telephone number
4012 v_RespPerson_email, -- Email
4013 p_Start_Date,
4014 p_End_Date,
4015 CHR(0), '', '', '', '', '', '',
4016 '', '', '', '', '', '', '', '', '', '',
4017 '', '', '', '', '', '', '', '', '', '',
4018 '', '', '', '', '', '', '', '', '', '',
4019 '', '', '', '', '', '', '', '', '', '',
4020 '', '', '', '', '', '', '', '', '', '',
4021 '', '', '', '', '', '', '', '', '', '',
4022 '', '', '', '', '', '', '', ''
4023 );
4024 end if;
4025 /*Bug 12597773 -End*/
4026 END generate_etcs_returns;
4027
4028 PROCEDURE yearly_returns
4029 (
4030 p_err_buf OUT NOCOPY VARCHAR2,
4031 p_ret_code OUT NOCOPY NUMBER,
4032 p_tan_number IN VARCHAR2,
4033 p_organization_id IN NUMBER,
4034 p_fin_year IN NUMBER,
4035 p_collection_code IN VARCHAR2,
4036 p_tax_authority_id IN NUMBER,
4037 p_tax_authority_site_id IN NUMBER,
4038 p_seller_name IN VARCHAR2,
4039 p_seller_state IN VARCHAR2,
4040 p_addrChangedSinceLastRet IN VARCHAR2,
4041 p_persRespForCollection IN VARCHAR2,
4042 p_desgOfPersResponsible IN VARCHAR2,
4043 p_start_date IN VARCHAR2,
4044 p_end_date IN VARCHAR2,
4045 p_pro_rcpt_num_org_ret IN NUMBER,
4046 p_file_path IN VARCHAR2,
4047 p_filename IN VARCHAR2,
4048 p_generate_headers IN VARCHAR2 DEFAULT 'N'
4049 )
4050 IS
4051 pv_start_date DATE DEFAULT fnd_date.canonical_to_date(p_start_date);
4052 pv_end_date DATE DEFAULT fnd_date.canonical_to_date(p_end_date);
4053
4054 BEGIN
4055
4056 FND_FILE.put_line( FND_FILE.log, 'Parameters : ' || fnd_global.local_chr(10)
4057 ||' org_tan_number ->'||p_tan_number||fnd_global.local_chr(10)
4058 ||' Organization_id ->'||p_organization_id || fnd_global.local_chr(10)
4059 ||' financial_year ->'||p_fin_year||fnd_global.local_chr(10)
4060 ||' Collection Code ->'||p_collection_code||fnd_global.local_chr(10)
4061 ||' tax_authority_id ->'||p_tax_authority_id||fnd_global.local_chr(10)
4062 ||' tax_authority_site_id ->'||p_tax_authority_site_id||fnd_global.local_chr(10)
4063 ||' seller ->'||p_seller_name||fnd_global.local_chr(10)
4064 ||' seller state ->'||p_seller_state||fnd_global.local_chr(10)
4065 ||' addr_changed_since_last_ret->'||p_addrChangedSinceLastRet||fnd_global.local_chr(10)
4066 ||' person_resp_for_collectio ->'||p_persRespForCollection||fnd_global.local_chr(10)
4067 ||' designation_of_pers_resp ->'||p_desgOfPersResponsible||fnd_global.local_chr(10)
4068 ||' Start_date ->'||p_start_date||fnd_global.local_chr(10)
4069 ||' End_date ->'||p_end_date||fnd_global.local_chr(10)
4070 ||' Provvisional Rcpt No ->'||p_pro_rcpt_num_org_ret || fnd_global.local_chr(10)
4071 ||' file_path ->'||p_file_path||fnd_global.local_chr(10)
4072 ||' filename ->'||p_filename||fnd_global.local_chr(10)
4073 ||' Generate_headers ->'||p_generate_headers||fnd_global.local_chr(10)
4074 ) ;
4075
4076
4077 generate_etcs_returns
4078 (
4079 p_err_buf => p_err_buf ,
4080 p_ret_code => p_ret_code ,
4081 p_tan_number => p_tan_number ,
4082 p_organization_id => p_organization_id ,
4083 p_fin_year => p_fin_year ,
4084 p_tax_authority_id => p_tax_authority_id ,
4085 p_tax_authority_site_id => p_tax_authority_site_id ,
4086 p_seller_name => p_seller_name ,
4087 p_seller_state => p_seller_state ,
4088 p_addrChangedSinceLastRet => p_addrChangedSinceLastRet ,
4089 p_persRespForCollection => p_persRespForCollection ,
4090 p_desgOfPersResponsible => p_desgOfPersResponsible ,
4091 p_Start_Date => pv_start_date ,
4092 p_End_Date => pv_end_date ,
4093 p_pro_rcpt_num_org_ret => p_pro_rcpt_num_org_ret ,
4094 p_file_path => p_file_path ,
4095 p_filename => p_filename ,
4096 p_collection_code => p_collection_code ,
4097 p_generate_headers => p_generate_headers
4098 ) ;
4099 END yearly_returns;
4100
4101 PROCEDURE quarterly_returns
4102 (
4103 p_err_buf OUT NOCOPY VARCHAR2,
4104 p_ret_code OUT NOCOPY NUMBER,
4105 p_tan_number IN VARCHAR2,
4106 p_organization_id IN NUMBER,
4107 p_fin_year IN NUMBER,
4108 p_period IN VARCHAR2 ,
4109 p_collection_code IN VARCHAR2,
4110 p_tax_authority_id IN NUMBER,
4111 p_tax_authority_site_id IN NUMBER,
4112 p_seller_name IN VARCHAR2,
4113 p_seller_state IN VARCHAR2,
4114 p_addrChangedSinceLastRet IN VARCHAR2,
4115 --p_collector_status IN VARCHAR2, /*Bug 8880543 - Commented for eTDS/eTCS FVU Changes*/
4116 p_persRespForCollection IN VARCHAR2,
4117 p_desgOfPersResponsible IN VARCHAR2,
4118 /*Bug 12597773 -Start*/
4119 -- p_RespPersAddress IN VARCHAR2 ,
4120 p_RespPers_flat_no IN VARCHAR2 ,
4121 p_RespPers_prem_bldg IN VARCHAR2 ,
4122 p_RespPers_rd_st_lane IN VARCHAR2 ,
4123 p_RespPers_area_loc IN VARCHAR2 ,
4124 p_RespPers_tn_cty_dt IN VARCHAR2 ,
4125 /*Bug 12597773 -End*/
4126 p_RespPersState IN VARCHAR2 ,
4127 p_RespPersPin IN VARCHAR2 ,
4128 p_RespPersAddrChange IN VARCHAR2,
4129 p_start_date IN VARCHAR2,
4130 p_end_date IN VARCHAR2,
4131 p_pro_rcpt_num_org_ret IN NUMBER,
4132 p_file_path IN VARCHAR2,
4133 p_filename IN VARCHAR2,
4134 p_action IN VARCHAR2
4135 )
4136 IS
4137 pv_start_date DATE DEFAULT fnd_date.canonical_to_date(p_start_date);
4138 pv_end_date DATE DEFAULT fnd_date.canonical_to_date(p_end_date);
4139
4140 BEGIN
4141
4142 FND_FILE.put_line( FND_FILE.log, 'Parameters : ' || fnd_global.local_chr(10)
4143 ||' org_tan_number ->'||p_tan_number||fnd_global.local_chr(10)
4144 ||' Organization_id ->'||p_organization_id || fnd_global.local_chr(10)
4145 ||' financial_year ->'||p_fin_year||fnd_global.local_chr(10)
4146 ||' Period ->'||p_period||fnd_global.local_chr(10)
4147 ||' Collection Code ->'||p_collection_code||fnd_global.local_chr(10)
4148 ||' tax_authority_id ->'||p_tax_authority_id||fnd_global.local_chr(10)
4149 ||' tax_authority_site_id ->'||p_tax_authority_site_id||fnd_global.local_chr(10)
4150 ||' seller ->'||p_seller_name||fnd_global.local_chr(10)
4151 ||' seller state ->'||p_seller_state||fnd_global.local_chr(10)
4152 ||' addr_changed_since_last_ret->'||p_addrChangedSinceLastRet||fnd_global.local_chr(10)
4153 --||' collector_status ->'||p_collector_status||fnd_global.local_chr(10) /*Bug 8880543 - Commented for eTDS/eTCS FVU Changes*/
4154 ||' person_resp_for_collectio ->'||p_persRespForCollection||fnd_global.local_chr(10)
4155 ||' designation_of_pers_resp ->'||p_desgOfPersResponsible||fnd_global.local_chr(10)
4156 --||' RespPerson''s Address ->'||p_RespPersAddress||fnd_global.local_chr(10)
4157 ||' RespPerson''s Flat No ->'||p_RespPers_Flat_no||fnd_global.local_chr(10) /* Bug 12597773*/
4158 ||' RespPerson''s Premises/Bldg ->'||p_RespPers_prem_bldg||fnd_global.local_chr(10) /* Bug 12597773*/
4159 ||' RespPerson''s Rd/St/Lane ->'||p_RespPers_rd_st_lane||fnd_global.local_chr(10) /* Bug 12597773*/
4160 ||' RespPerson''s Area/Loc ->'||p_RespPers_area_loc||fnd_global.local_chr(10) /* Bug 12597773*/
4161 ||' RespPerson''s Tn/Cty/Dt ->'||p_RespPers_tn_cty_dt||fnd_global.local_chr(10) /* Bug 12597773*/
4162 ||' RespPerson''s State ->'||p_RespPersState||fnd_global.local_chr(10)
4163 ||' RespPerson''s Pin ->'||p_RespPersPin||fnd_global.local_chr(10)
4164 ||' RespPerson''s Addr Changed ->'||p_RespPersAddrChange||fnd_global.local_chr(10)
4165 ||' Start_date ->'||p_start_date||fnd_global.local_chr(10)
4166 ||' End_date ->'||p_end_date||fnd_global.local_chr(10)
4167 ||' Provvisional Rcpt No ->'||p_pro_rcpt_num_org_ret || fnd_global.local_chr(10)
4168 ||' file_path ->'||p_file_path||fnd_global.local_chr(10)
4169 ||' filename ->'||p_filename||fnd_global.local_chr(10)
4170 ||' Action ->'||p_action||fnd_global.local_chr(10)
4171 ) ;
4172
4173
4174 generate_etcs_returns
4175 (
4176 p_err_buf => p_err_buf ,
4177 p_ret_code => p_ret_code ,
4178 p_organization_id => p_organization_id ,
4179 p_tan_number => p_tan_number ,
4180 p_fin_year => p_fin_year ,
4181 p_tax_authority_id => p_tax_authority_id ,
4182 p_tax_authority_site_id => p_tax_authority_site_id ,
4183 p_seller_name => p_seller_name ,
4184 p_seller_state => p_seller_state ,
4185 p_addrChangedSinceLastRet => p_addrChangedSinceLastRet ,
4186 p_persRespForCollection => p_persRespForCollection ,
4187 p_desgOfPersResponsible => p_desgOfPersResponsible ,
4188 p_Start_Date => pv_start_date ,
4189 p_End_Date => pv_end_date ,
4190 p_pro_rcpt_num_org_ret => p_pro_rcpt_num_org_ret ,
4191 p_file_path => p_file_path ,
4192 p_filename => p_filename ,
4193 p_collection_code => p_collection_code ,
4194 p_period => p_period ,
4195 /*Bug 12597773 -Start*/
4196 p_RespPers_flat_no => p_RespPers_flat_no ,
4197 p_RespPers_prem_bldg => p_RespPers_prem_bldg ,
4198 p_RespPers_rd_st_lane => p_RespPers_rd_st_lane ,
4199 p_RespPers_area_loc => p_RespPers_area_loc ,
4200 p_RespPers_tn_cty_dt => p_RespPers_tn_cty_dt ,
4201 -- p_RespPersAddress => p_RespPersAddress ,
4202 /*Bug 12597773 -End*/
4203 p_RespPersState => p_RespPersState ,
4204 p_RespPersPin => p_RespPersPin ,
4205 p_RespPersAddrChange => p_RespPersAddrChange ,
4206 p_action => p_action
4207 --p_collector_status => p_collector_status /*Bug 8880543 - Commeneted for eTDS/eTCS FVU Changes*/
4208 ) ;
4209 END quarterly_returns;
4210
4211
4212 END jai_etcs_pkg;