[Home] [Help]
PACKAGE BODY: APPS.JAI_ETCS_PKG
Source
1 PACKAGE BODY jai_etcs_pkg AS
2 /* $Header: jai_ar_etcs_prc.plb 120.6.12000000.1 2007/07/24 06:55:31 rallamse noship $ */
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 *****************************************************************************************************/
67
68 PROCEDURE openFile(
69 p_directory IN VARCHAR2,
70 p_filename IN VARCHAR2
71 ) IS
72
73 BEGIN
74
75 jai_ap_tds_etds_pkg.v_filehandle := UTL_FILE.fopen(p_directory, p_filename, 'W');
76 jai_ap_tds_etds_pkg.v_utl_file_dir := p_directory;
77 jai_ap_tds_etds_pkg.v_utl_file_name := p_filename;
78
79 END openFile;
80
81 PROCEDURE closeFile IS
82 BEGIN
83 UTL_FILE.fclose(jai_ap_tds_etds_pkg.v_filehandle);
84 END closeFile;
85
86 -- Date Population procedures for ETCS Quarterly Returns
87 PROCEDURE create_quarterly_fh(
88 p_batch_id IN NUMBER,
89 p_period IN VARCHAR2,
90 p_RespPersAddress IN VARCHAR2,
91 p_RespPersState IN VARCHAR2,
92 p_RespPersPin IN NUMBER,
93 p_RespPersAddrChange IN VARCHAR2
94 ) IS
95 v_req JAI_AP_ETDS_REQUESTS%rowtype;
96 BEGIN
97
98 SELECT * INTO v_req FROM JAI_AP_ETDS_REQUESTS WHERE batch_id = p_batch_id;
99 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, 'Input Parameters to this Request:');
100 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, '-------------------------------------------------');
101 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
102 ' batch_id ->'||v_req.batch_id||fnd_global.local_chr(10)
103 ||' request_id ->'||v_req.request_id||fnd_global.local_chr(10)
104 ||' operating_unit_id ->'||v_req.operating_unit_id||fnd_global.local_chr(10)
105 ||' org_tan_number ->'||v_req.org_tan_number||fnd_global.local_chr(10)
106 ||' financial_year ->'||v_req.financial_year||fnd_global.local_chr(10)
107 ||' tax_authority_id ->'||v_req.tax_authority_id||fnd_global.local_chr(10)
108 ||' tax_authority_site_id ->'||v_req.tax_authority_site_id||fnd_global.local_chr(10)
109 ||' organization_id ->'||v_req.organization_id||fnd_global.local_chr(10)
110 ||' collector_name ->'||v_req.deductor_name||fnd_global.local_chr(10)
111 ||' collector_state ->'||v_req.deductor_state||fnd_global.local_chr(10)
112 ||' addr_changed_since_last_ret->'||v_req.addr_changed_since_last_ret||fnd_global.local_chr(10)
113 ||' collector_status ->'||v_req.deductor_status||fnd_global.local_chr(10)
114 ||' person_resp_for_collection ->'||v_req.person_resp_for_deduction||fnd_global.local_chr(10)
115 ||' designation_of_pers_resp ->'||v_req.designation_of_pers_resp||fnd_global.local_chr(10)
116 ||' challan_start_date ->'||v_req.challan_start_date||fnd_global.local_chr(10)
117 ||' challan_end_date ->'||v_req.challan_end_date||fnd_global.local_chr(10)
118 ||' file_path ->'||v_req.file_path||fnd_global.local_chr(10)
119 ||' filename ->'||v_req.filename||fnd_global.local_chr(10)
120 ||' Period ->'||p_period||fnd_global.local_chr(10)
121 ||' RespPerson''s Address ->'||p_RespPersAddress||fnd_global.local_chr(10)
122 ||' RespPerson''s State ->'||p_RespPersState||fnd_global.local_chr(10)
123 ||' RespPerson''s Pin ->'||p_RespPersPin||fnd_global.local_chr(10)
124 ||' RespPerson''s Addr Changed ->'||p_RespPersAddrChange||fnd_global.local_chr(10)
125 );
126
127 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
128 LPAD('Line No', sq_len_9, v_quart_pad) || v_pad_char ||
129 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
130 LPAD('FT', sq_len_4, v_quart_pad) || v_pad_char ||
131 LPAD('UT', sq_len_2, v_quart_pad) || v_pad_char ||
132 LPAD('FileDate', sq_len_8, v_quart_pad) || v_pad_char ||
133 LPAD('SeqNo', sq_len_9, v_quart_pad) || v_pad_char ||
134 LPAD('U', sq_len_1, v_quart_pad) || v_pad_char ||
135 LPAD('TAN', sq_len_10, v_quart_pad) || v_pad_char ||
136 LPAD('Batch Cnt', sq_len_9, v_quart_pad) || v_pad_char ||
137 LPAD('RH', sq_len_2, v_quart_pad) || v_pad_char ||
138 LPAD('FV', sq_len_2, v_quart_pad) || v_pad_char ||
139 LPAD('FH', sq_len_2, v_quart_pad) || v_pad_char ||
140 LPAD('SV', sq_len_2, v_quart_pad) || v_pad_char ||
141 LPAD('SH', sq_len_2, v_quart_pad) || v_pad_char ||
142 LPAD('SV', sq_len_2, v_quart_pad) || v_pad_char ||
143 LPAD('SH', sq_len_2, v_quart_pad) );
144
145 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
146 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
147 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
148 LPAD(v_underline_char, sq_len_4, v_underline_char) || v_pad_char ||
149 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
150 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
151 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
152 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
153 LPAD(v_underline_char, sq_len_10,v_underline_char) || v_pad_char ||
154 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
155 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
156 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
157 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
158 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
159 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
160 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
161 LPAD(v_underline_char, sq_len_2, v_underline_char) );
162
163 END create_quarterly_fh;
164
165 PROCEDURE create_quarterly_bh
166 IS
167 BEGIN
168 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, ' ' ) ;
169 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
170 LPAD('Line No', sq_len_9, v_quart_pad) || v_pad_char ||
171 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
172 LPAD('Batch No', sq_len_9, v_quart_pad) || v_pad_char ||
173 LPAD('ChallCnt', sq_len_9, v_quart_pad) || v_pad_char ||
174 LPAD('FN', sq_len_4, v_quart_pad) || v_pad_char ||
175 LPAD('TT', sq_len_2, v_quart_pad) || v_pad_char ||
176 LPAD('BI', sq_len_2, v_quart_pad) || v_pad_char ||
177 LPAD('OR', sq_len_2, v_quart_pad) || v_pad_char ||
178 LPAD('PR', sq_len_2, v_quart_pad) || v_pad_char ||
179 LPAD('RN', sq_len_2, v_quart_pad) || v_pad_char ||
180 LPAD('RD', sq_len_2, v_quart_pad) || v_pad_char ||
181 LPAD('LT', sq_len_2, v_quart_pad) || v_pad_char ||
182 LPAD('Col TAN', sq_len_10, v_quart_pad) || v_pad_char ||
183 LPAD('F1', sq_len_2, v_quart_pad) || v_pad_char ||
184 LPAD('Col PAN', sq_len_10, v_quart_pad) || v_pad_char ||
185 LPAD('Ass.Yr', sq_len_6, v_quart_pad) || v_pad_char ||
186 LPAD('Fin.Yr', sq_len_6, v_quart_pad) || v_pad_char ||
187 LPAD('PD', sq_len_2, v_quart_pad) || v_pad_char ||
188 LPAD('Collector Name', sq_len_75, v_quart_pad) || v_pad_char ||
189 LPAD('Collector Branch', sq_len_75, v_quart_pad) || v_pad_char ||
190 LPAD('Collector Addr1', sq_len_25, v_quart_pad) || v_pad_char ||
191 LPAD('Collector Addr2', sq_len_25, v_quart_pad) || v_pad_char ||
192 LPAD('Collector Addr3', sq_len_25, v_quart_pad) || v_pad_char ||
193 LPAD('Collector Addr4', sq_len_25, v_quart_pad) || v_pad_char ||
194 LPAD('Collector Addr5', sq_len_25, v_quart_pad) || v_pad_char ||
195 LPAD('CS', sq_len_2, v_quart_pad) || v_pad_char ||
196 LPAD('ColPIN', sq_len_6, v_quart_pad) || v_pad_char ||
197 LPAD('Collector Email', sq_len_75, v_quart_pad) || v_pad_char ||
198 LPAD('ColSTD', sq_len_5, v_quart_pad) || v_pad_char ||
199 LPAD('Col Phone', sq_len_10, v_quart_pad) || v_pad_char ||
200 LPAD('C', sq_len_1, v_quart_pad) || v_pad_char ||
201 LPAD('T', sq_len_1, v_quart_pad) || v_pad_char ||
202 LPAD('RespPerson Name', sq_len_75, v_quart_pad) || v_pad_char ||
203 LPAD('RespPerson Desg', sq_len_20, v_quart_pad) || v_pad_char ||
204 LPAD('RespPerson Addr1', sq_len_25, v_quart_pad) || v_pad_char ||
205 LPAD('RespPerson Addr2', sq_len_25, v_quart_pad) || v_pad_char ||
206 LPAD('RespPerson Addr3', sq_len_25, v_quart_pad) || v_pad_char ||
207 LPAD('RespPerson Addr4', sq_len_25, v_quart_pad) || v_pad_char ||
208 LPAD('RespPerson Addr5', sq_len_25, v_quart_pad) || v_pad_char ||
209 LPAD('RS', sq_len_2, v_quart_pad) || v_pad_char ||
210 LPAD('ResPIN', sq_len_6, v_quart_pad) || v_pad_char ||
211 LPAD('RespPerson Email', sq_len_75, v_quart_pad) || v_pad_char ||
212 LPAD('Remark', sq_len_75, v_quart_pad) || v_pad_char ||
213 LPAD('ResSTD', sq_len_5, v_quart_pad) || v_pad_char ||
214 LPAD('ResPhone', sq_len_10, v_quart_pad) || v_pad_char ||
215 LPAD('C', sq_len_1, v_quart_pad) || v_pad_char ||
216 LPAD('TotChallanTax', sq_len_15, v_quart_pad) || v_pad_char ||
217 LPAD('TC', sq_len_2, v_quart_pad) || v_pad_char ||
218 LPAD('SC', sq_len_2, v_quart_pad) || v_pad_char ||
219 LPAD('GT', sq_len_2, v_quart_pad) || v_pad_char ||
220 LPAD('A', sq_len_1, v_quart_pad) || v_pad_char ||
221 LPAD('AO Approval No', sq_len_15, v_quart_pad) || v_pad_char ||
222 LPAD('RH', sq_len_2, v_quart_pad) );
223
224 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
225 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
226 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
227 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
228 LPAD(v_underline_char, sq_len_9, v_underline_char) || v_pad_char ||
229 LPAD(v_underline_char, sq_len_4, v_underline_char) || v_pad_char ||
230 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
231 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
232 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
233 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
234 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
235 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
236 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
237 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
238 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
239 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
240 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
241 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
242 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
243 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
244 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
245 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
246 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
247 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
248 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
249 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
250 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
251 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
252 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
253 LPAD(v_underline_char, sq_len_5, v_underline_char) || v_pad_char ||
254 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
255 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
256 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
257 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
258 LPAD(v_underline_char, sq_len_20, v_underline_char) || v_pad_char ||
259 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
260 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
261 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
262 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
263 LPAD(v_underline_char, sq_len_25, v_underline_char) || v_pad_char ||
264 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
265 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
266 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
267 LPAD(v_underline_char, sq_len_75, v_underline_char) || v_pad_char ||
268 LPAD(v_underline_char, sq_len_5, v_underline_char) || v_pad_char ||
269 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
270 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
271 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
272 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
273 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
274 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
275 LPAD(v_underline_char, sq_len_1, v_underline_char) || v_pad_char ||
276 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
277 LPAD(v_underline_char, sq_len_2, v_underline_char) );
278
279 END create_quarterly_bh;
280
281 PROCEDURE create_quarterly_cd
282 IS
283 BEGIN
284 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, ' ' ) ;
285 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
286 LPAD('Line No', sq_len_9 , v_quart_pad) || v_pad_char ||
287 LPAD('RT', sq_len_2 , v_quart_pad) || v_pad_char ||
288 LPAD('Batch No', sq_len_9 , v_quart_pad) || v_pad_char ||
289 LPAD('Chall No', sq_len_9 , v_quart_pad) || v_pad_char ||
290 LPAD('Collect Cnt', sq_len_9 , v_quart_pad) || v_pad_char ||
291 LPAD('I', sq_len_1 , v_quart_pad) || v_pad_char ||
292 LPAD('U', sq_len_2 , v_quart_pad) || v_pad_char ||
293 LPAD('F2', sq_len_2 , v_quart_pad) || v_pad_char ||
294 LPAD('F3', sq_len_2 , v_quart_pad) || v_pad_char ||
295 LPAD('F4', sq_len_2 , v_quart_pad) || v_pad_char ||
296 LPAD('LC', sq_len_2 , v_quart_pad) || v_pad_char ||
297 LPAD('Ch', sq_len_5 , v_quart_pad) || v_pad_char ||
298 LPAD('LV', sq_len_2 , v_quart_pad) || v_pad_char ||
299 LPAD('TrnsVouch', sq_len_9 , v_quart_pad) || v_pad_char ||
300 LPAD('LB', sq_len_2 , v_quart_pad) || v_pad_char ||
301 LPAD('Bank Br', sq_len_7 , v_quart_pad) || v_pad_char ||
302 LPAD('LD', sq_len_2 , v_quart_pad) || v_pad_char ||
303 LPAD('CH. Date', sq_len_8 , v_quart_pad) || v_pad_char ||
304 LPAD('F5', sq_len_2 , v_quart_pad) || v_pad_char ||
305 LPAD('F6', sq_len_2 , v_quart_pad) || v_pad_char ||
306 LPAD('Sec', sq_len_3 , v_quart_pad) || v_pad_char ||
307 LPAD('Oltas Tax', sq_len_15 , v_quart_pad) || v_pad_char ||
308 LPAD('Oltas Sur', sq_len_15 , v_quart_pad) || v_pad_char ||
309 LPAD('Oltas Cess', sq_len_15 , v_quart_pad) || v_pad_char ||
310 LPAD('Oltas Interest', sq_len_15 , v_quart_pad) || v_pad_char ||
311 LPAD('Oltas OtherAmt', sq_len_15 , v_quart_pad) || v_pad_char ||
312 LPAD('Total Deposit', sq_len_15 , v_quart_pad) || v_pad_char ||
313 LPAD('LD', sq_len_2 , v_quart_pad) || v_pad_char ||
314 LPAD('TotTax Deposit', sq_len_15 , v_quart_pad) || v_pad_char ||
315 LPAD('TCS Income Tax', sq_len_15 , v_quart_pad) || v_pad_char ||
316 LPAD('TCS Surcharge', sq_len_15 , v_quart_pad) || v_pad_char ||
317 LPAD('TCS Cess', sq_len_15 , v_quart_pad) || v_pad_char ||
318 LPAD('Total TCS ', sq_len_15 , v_quart_pad) || v_pad_char ||
319 LPAD('TDS Interest', sq_len_15 , v_quart_pad) || v_pad_char ||
320 LPAD('TCS OtherAmt', sq_len_15 , v_quart_pad) || v_pad_char ||
321 LPAD('Cheque/DD', sq_len_15 , v_quart_pad) || v_pad_char ||
322 LPAD('B', sq_len_1 , v_quart_pad) || v_pad_char ||
323 LPAD('Remarks', sq_len_14 , v_quart_pad) || v_pad_char ||
324 LPAD('RH', sq_len_2 , v_quart_pad)
325 );
326
327 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
328 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
329 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
330 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
331 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
332 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
333 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
334 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
335 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
336 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
337 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
338 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
339 LPAD(v_underline_char , sq_len_5 , v_underline_char) || v_pad_char ||
340 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
341 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
342 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
343 LPAD(v_underline_char , sq_len_7 , v_underline_char) || v_pad_char ||
344 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
345 LPAD(v_underline_char , sq_len_8 , v_underline_char) || v_pad_char ||
346 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
347 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
348 LPAD(v_underline_char , sq_len_3 , v_underline_char) || v_pad_char ||
349 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
350 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
351 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
352 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
353 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
354 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
355 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
356 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
357 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
358 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
359 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
360 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
361 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
362 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
363 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
364 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
365 LPAD(v_underline_char , sq_len_14 , v_underline_char) || v_pad_char ||
366 LPAD(v_underline_char , sq_len_2 , v_underline_char)
367 );
368
369 END create_quarterly_cd;
370
371 PROCEDURE create_quarterly_dd IS
372 BEGIN
373 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, ' ' ) ;
374 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
375 LPAD('Line No', sq_len_9, v_quart_pad) || v_pad_char ||
376 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
377 LPAD('Batch No', sq_len_9, v_quart_pad) || v_pad_char ||
378 LPAD('ChallNo', sq_len_9, v_quart_pad) || v_pad_char ||
379 LPAD('ColRecNo', sq_len_9, v_quart_pad) || v_pad_char ||
380 LPAD('M', sq_len_1, v_quart_pad) || v_pad_char ||
381 LPAD('EN', sq_len_2, v_quart_pad) || v_pad_char ||
382 LPAD('C', sq_len_1, v_quart_pad) || v_pad_char ||
383 LPAD('LP', sq_len_2, v_quart_pad) || v_pad_char ||
384 LPAD('Prt Pan', sq_len_10, v_quart_pad) || v_pad_char ||
385 LPAD('LP', sq_len_2, v_quart_pad) || v_pad_char ||
386 LPAD('PAN Ref No', sq_len_10, v_quart_pad) || v_pad_char ||
387 LPAD('Party Name', sq_len_75, v_quart_pad) || v_pad_char ||
388 LPAD('TCS Income Tax ', sq_len_15, v_quart_pad) || v_pad_char ||
389 LPAD('TCS Surcharge', sq_len_15, v_quart_pad) || v_pad_char ||
390 LPAD('TCS Cess', sq_len_15, v_quart_pad) || v_pad_char ||
391 LPAD('TCS Total', sq_len_15, v_quart_pad) || v_pad_char ||
392 LPAD('LT', sq_len_2, v_quart_pad) || v_pad_char ||
393 LPAD('TotTax Deposit', sq_len_15, v_quart_pad) || v_pad_char ||
394 LPAD('LT', sq_len_2, v_quart_pad) || v_pad_char ||
395 LPAD('TP', sq_len_15, v_quart_pad) || v_pad_char ||
396 LPAD('Payment Amt', sq_len_15, v_quart_pad) || v_pad_char ||
397 LPAD('Pay Dt', sq_len_8, v_quart_pad) || v_pad_char ||
398 LPAD('TaxColDt', sq_len_8, v_quart_pad) || v_pad_char ||
399 LPAD('DD', sq_len_2, v_quart_pad) || v_pad_char ||
400 LPAD('Tax Rt', sq_len_7, v_quart_pad) || v_pad_char ||
401 LPAD('GI', sq_len_2, v_quart_pad) || v_pad_char ||
402 LPAD('B', sq_len_1, v_quart_pad) || v_pad_char ||
403 LPAD('TD', sq_len_2, v_quart_pad) || v_pad_char ||
404 LPAD('R', sq_len_75, v_quart_pad) || v_pad_char ||
405 LPAD('Remarks 2', sq_len_75, v_quart_pad) || v_pad_char ||
406 LPAD('Remarks 3', sq_len_14, v_quart_pad) || v_pad_char ||
407 LPAD('RH', sq_len_2, v_quart_pad)
408 );
409
410 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
411 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
412 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
413 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
414 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
415 LPAD(v_underline_char , sq_len_9 , v_underline_char) || v_pad_char ||
416 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
417 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
418 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
419 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
420 LPAD(v_underline_char , sq_len_10 , v_underline_char) || v_pad_char ||
421 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
422 LPAD(v_underline_char , sq_len_10 , v_underline_char) || v_pad_char ||
423 LPAD(v_underline_char , sq_len_75 , v_underline_char) || v_pad_char ||
424 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
425 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
426 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
427 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
428 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
429 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
430 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
431 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
432 LPAD(v_underline_char , sq_len_15 , v_underline_char) || v_pad_char ||
433 LPAD(v_underline_char , sq_len_8 , v_underline_char) || v_pad_char ||
434 LPAD(v_underline_char , sq_len_8 , v_underline_char) || v_pad_char ||
435 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
436 LPAD(v_underline_char , sq_len_7 , v_underline_char) || v_pad_char ||
437 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
438 LPAD(v_underline_char , sq_len_1 , v_underline_char) || v_pad_char ||
439 LPAD(v_underline_char , sq_len_2 , v_underline_char) || v_pad_char ||
440 LPAD(v_underline_char , sq_len_75 , v_underline_char) || v_pad_char ||
441 LPAD(v_underline_char , sq_len_75 , v_underline_char) || v_pad_char ||
442 LPAD(v_underline_char , sq_len_14 , v_underline_char) || v_pad_char ||
443 LPAD(v_underline_char , sq_len_2 , v_underline_char)
444 );
445
446 END create_quarterly_dd;
447
448 PROCEDURE validate_Party_detail
449 ( p_line_number IN NUMBER ,
450 p_record_type IN VARCHAR2,
451 p_batch_number IN NUMBER,
452 p_challan_line_num IN NUMBER,
453 p_party_slno IN NUMBER,
454 p_dh_mode IN VARCHAR2,
455 p_quart_party_code IN VARCHAR2,
456 p_party_pan IN VARCHAR2,
457 p_party_name IN VARCHAR2,
458 p_tcs_amt IN NUMBER,
459 p_surcharge_amt IN NUMBER ,
460 p_cess_amt IN NUMBER ,
461 p_party_total_tax_deducted IN NUMBER,
462 p_base_taxabale_amount IN NUMBER,
463 p_gl_date IN DATE ,
464 p_book_ent_oth IN VARCHAR2,
465 p_tcs_tax_rate IN NUMBER,
466 p_total_purchase IN NUMBER,
467 p_party_total_tax_deposit IN NUMBER,
468 p_return_code OUT NOCOPY VARCHAR2,
469 p_return_message OUT NOCOPY VARCHAR2
470 )
471 IS
472 BEGIN
473 IF p_line_number IS NULL THEN
474 p_return_message := p_return_message || ' Line Number should not be null. ' ;
475 IF lv_action <> 'V' THEN
476 goto end_of_procedure ;
477 END IF ;
478 END IF ;
479 IF p_record_type IS NULL THEN
480 p_return_message := p_return_message || ' Record Type is null. ' ;
481 IF lv_action <> 'V' THEN
482 goto end_of_procedure ;
483 END IF ;
484 END IF ;
485 IF p_batch_number IS NULL THEN
486 p_return_message := p_return_message || ' Batch Number is null. ' ;
487 IF lv_action <> 'V' THEN
488 goto end_of_procedure ;
489 END IF ;
490 END IF ;
491 IF p_challan_line_num IS NULL THEN
492 p_return_message := p_return_message || ' Challan Record Number is null. ' ;
493 IF lv_action <> 'V' THEN
494 goto end_of_procedure ;
495 END IF ;
496 END IF ;
497 IF p_party_slno IS NULL THEN
498 p_return_message := p_return_message || ' Party Detail Record Number is null. ' ;
499 IF lv_action <> 'V' THEN
500 goto end_of_procedure ;
501 END IF ;
502 END IF ;
503 IF p_dh_mode IS NULL THEN
504 p_return_message := p_return_message || ' Mode is null. ' ;
505 IF lv_action <> 'V' THEN
506 goto end_of_procedure ;
507 END IF ;
508 END IF ;
509 IF p_quart_Party_code IS NULL THEN
510 p_return_message := p_return_message || ' Deductee Party Code is null. ' ;
511 IF lv_action <> 'V' THEN
512 goto end_of_procedure ;
513 END IF ;
514 END IF ;
515 IF p_Party_pan IS NULL THEN
516 p_return_message := p_return_message || ' Deductee PAN is null. ' ;
517 IF lv_action <> 'V' THEN
518 goto end_of_procedure ;
519 END IF ;
520 END IF ;
521 IF p_Party_name IS NULL THEN
522 p_return_message := p_return_message || ' Party Name is null. ' ;
523 IF lv_action <> 'V' THEN
524 goto end_of_procedure ;
525 END IF ;
526 END IF ;
527 IF p_tcs_amt IS NULL THEN
528 p_return_message := p_return_message || ' TCS Income Tax for the Period is null. ' ;
529 IF lv_action <> 'V' THEN
530 goto end_of_procedure ;
531 END IF ;
532 END IF ;
533 IF p_surcharge_amt IS NULL THEN
534 p_return_message := p_return_message || ' TCS Surcharge is null. ' ;
535 IF lv_action <> 'V' THEN
536 goto end_of_procedure ;
537 END IF ;
538 END IF ;
539 IF p_cess_amt IS NULL THEN
540 p_return_message := p_return_message || ' TCS Cess is null. ' ;
541 IF lv_action <> 'V' THEN
542 goto end_of_procedure ;
543 END IF ;
544 END IF ;
545 IF p_party_total_tax_deducted IS NULL THEN
546 p_return_message := p_return_message || ' Total TCS is null. ' ;
547 IF lv_action <> 'V' THEN
548 goto end_of_procedure ;
549 END IF ;
550 END IF ;
551 IF p_base_taxabale_amount IS NULL THEN
552 p_return_message := p_return_message || ' Payment Amount is null. ' ;
553 IF lv_action <> 'V' THEN
554 goto end_of_procedure ;
555 END IF ;
556 END IF ;
557 IF p_gl_date IS NULL THEN
558 p_return_message := p_return_message || ' Date on which Amount Credited is null. ' ;
559 IF lv_action <> 'V' THEN
560 goto end_of_procedure ;
561 END IF ;
562 END IF ;
563 IF p_book_ent_oth IS NULL THEN
564 p_return_message := p_return_message || ' Book/Cash Entry is null. ' ;
565 IF lv_action <> 'V' THEN
566 goto end_of_procedure ;
567 END IF ;
568 END IF ;
569 IF p_tcs_tax_rate IS NULL THEN
570 p_return_message := p_return_message || ' Tcs Tax Rate Is Null. ' ;
571 IF lv_action <> 'V' THEN
572 goto end_of_procedure ;
573 END IF ;
574 END IF ;
575 IF p_total_purchase IS NULL THEN
576 p_return_message := p_return_message || ' Total Purchase Amount Is Null. ' ;
577 IF lv_action <> 'V' THEN
578 goto end_of_procedure ;
579 END IF ;
580 END IF ;
581 IF p_party_total_tax_deposit IS NULL THEN
582 p_return_message := p_return_message || ' Total Tax Deposited Is Null. ' ;
583 IF lv_action <> 'V' THEN
584 goto end_of_procedure ;
585 END IF ;
586 END IF ;
587 IF lv_action = 'V' THEN
588 goto end_of_procedure ;
589 END IF ;
590
591 <<end_of_procedure>>
592 IF p_return_message IS NOT NULL THEN
593 p_return_code := 'E';
594 p_return_message := 'Collectee Detail Error - ' || p_return_message ;
595 END IF;
596
597 END validate_Party_detail ;
598
599
600 PROCEDURE create_quart_party_dtl(
601 p_line_number IN NUMBER,
602 p_record_type IN VARCHAR2,
603 p_batch_number IN NUMBER,
604 p_dh_challan_recNo IN NUMBER,
605 p_party_slno IN NUMBER,
606 p_dh_mode IN VARCHAR2,
607 p_emp_serial_no IN VARCHAR2,
608 p_party_code IN VARCHAR2,
609 p_last_emp_pan IN VARCHAR2,
610 p_party_pan IN VARCHAR2,
611 p_last_emp_pan_refno IN VARCHAR2,
612 p_party_pan_refno IN VARCHAR2,
613 p_party_name IN VARCHAR2,
614 p_party_tcs_income_tax IN NUMBER,
615 p_party_tcs_surcharge IN NUMBER,
616 p_party_tcs_cess IN NUMBER,
617 p_party_total_tax_deducted IN NUMBER,
618 p_last_total_tax_deducted IN VARCHAR2,
619 p_party_total_tax_deposit IN NUMBER,
620 p_last_total_tax_deposit IN VARCHAR2,
621 p_total_purchase IN NUMBER,
622 p_base_taxabale_amount IN NUMBER,
623 p_gl_date IN DATE,
624 p_tcs_invoice_date IN DATE,
625 p_deposit_date IN VARCHAR2,
626 p_tcs_tax_rate IN NUMBER,
627 p_grossingUp_ind IN VARCHAR2,
628 p_book_ent_oth IN VARCHAR2,
629 p_certificate_issue_date IN VARCHAR2,
630 p_remarks1 IN VARCHAR2,
631 p_remarks2 IN VARCHAR2,
632 p_remarks3 IN VARCHAR2,
633 p_dh_recHash IN VARCHAR2,
634 p_generate_headers IN VARCHAR2
635 )
636 IS
637 BEGIN
638 IF p_generate_headers = 'N' THEN
639 UTL_FILE.PUT_LINE( jai_ap_tds_etds_pkg.v_filehandle,
640 p_line_number || v_delimeter ||
641 upper(p_record_type) || v_delimeter ||
642 p_batch_number || v_delimeter ||
643 p_dh_challan_recNo || v_delimeter ||
644 p_party_slno || v_delimeter ||
645 p_dh_mode || v_delimeter ||
646 p_emp_serial_no || v_delimeter ||
647 p_party_code || v_delimeter ||
648 p_last_emp_pan || v_delimeter ||
649 p_party_pan || v_delimeter ||
650 p_last_emp_pan_refno || v_delimeter ||
651 p_party_pan_refno || v_delimeter ||
652 p_party_name || v_delimeter ||
653 to_char( p_party_tcs_income_tax, v_format_amount) || v_delimeter ||
654 to_char( p_party_tcs_surcharge, v_format_amount) || v_delimeter ||
655 to_char( p_party_tcs_cess, v_format_amount) || v_delimeter ||
656 to_char( p_party_total_tax_deducted, v_format_amount) || v_delimeter ||
657 p_last_total_tax_deducted || v_delimeter ||
658 to_char( p_party_total_tax_deposit, v_format_amount) || v_delimeter ||
659 p_last_total_tax_deposit || v_delimeter ||
660 TO_CHAR(p_total_purchase,V_FORMAT_AMOUNT) || v_delimeter ||
661 to_char( p_base_taxabale_amount, v_format_amount) || v_delimeter ||
662 to_char(p_gl_date,'ddmmyyyy') || v_delimeter ||
663 to_char(p_tcs_invoice_date,'ddmmyyyy') || v_delimeter ||
664 p_deposit_date || v_delimeter ||
665 to_char(p_tcs_tax_rate,'FM99D0000') || v_delimeter ||
666 p_grossingUp_ind || v_delimeter ||
667 p_book_ent_oth || v_delimeter ||
668 p_certificate_issue_date || v_delimeter ||
669 p_remarks1 || v_delimeter ||
670 p_remarks2 || v_delimeter ||
671 p_remarks3 || v_delimeter ||
672 p_dh_recHash
673 );
674 ELSE
675 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
676 LPAD(p_line_number , sq_len_9, v_quart_pad) || v_pad_char ||
677 LPAD(upper(p_record_type) , sq_len_2, v_quart_pad) || v_pad_char ||
678 LPAD(p_batch_number , sq_len_9, v_quart_pad) || v_pad_char ||
679 LPAD(p_dh_challan_recNo , sq_len_9, v_quart_pad) || v_pad_char ||
680 LPAD(p_party_slno , sq_len_9, v_quart_pad) || v_pad_char ||
681 LPAD(p_dh_mode , sq_len_1, v_quart_pad) || v_pad_char ||
682 LPAD(NVL(p_emp_serial_no,v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
683 LPAD(p_party_code , sq_len_1, v_quart_pad) || v_pad_char ||
684 LPAD(NVL(p_last_emp_pan, v_q_noval_filler), sq_len_2, v_quart_pad) || v_pad_char ||
685 LPAD(p_party_pan , sq_len_10, v_quart_pad) || v_pad_char ||
686 LPAD(NVL(p_last_emp_pan_refno,v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
687 LPAD(NVL(p_party_pan_refno,v_q_null_filler) , sq_len_10, v_quart_pad) || v_pad_char ||
688 LPAD(NVL(p_party_name, v_q_null_filler) , sq_len_75, v_quart_pad) || v_pad_char ||
689 LPAD(to_char( p_party_tcs_income_tax, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
690 LPAD(to_char( p_party_tcs_surcharge, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
691 LPAD(to_char( p_party_tcs_cess, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
692 LPAD(to_char( p_party_total_tax_deducted, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
693 LPAD(NVL(p_last_total_tax_deducted, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
694 LPAD(to_char( p_party_total_tax_deposit, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
695 LPAD(NVL(p_last_total_tax_deposit, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
696 LPAD(TO_CHAR(p_total_purchase, v_FORMAT_AMOUNT) , sq_len_15, v_quart_pad) || v_pad_char ||
697 LPAD(to_char( p_base_taxabale_amount, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
698 LPAD(to_char(p_gl_date,'ddmmyyyy') , sq_len_8, v_quart_pad) || v_pad_char ||
699 LPAD(NVL(to_char(p_tcs_invoice_date,'ddmmyyyy'),G_DATE_DUMMY) , sq_len_8, v_quart_pad) || v_pad_char || -- change later
700 LPAD(NVL(p_deposit_date, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
701 LPAD( to_char(p_tcs_tax_rate,v_format_rate), sq_len_7, v_quart_pad) || v_pad_char ||
702 LPAD(NVL(p_grossingUp_ind, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
703 LPAD(p_book_ent_oth , sq_len_1, v_quart_pad) || v_pad_char ||
704 LPAD(NVL(p_certificate_issue_date,v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
705 LPAD(NVL(p_remarks1,v_q_null_filler) , sq_len_75, v_quart_pad) || v_pad_char ||
706 LPAD(NVL(p_remarks2,v_q_noval_filler) , sq_len_75, v_quart_pad) || v_pad_char ||
707 LPAD(NVL(p_remarks3,v_q_noval_filler) , sq_len_14, v_quart_pad) || v_pad_char ||
708 LPAD(NVL(p_dh_recHash,v_q_noval_filler) , sq_len_2, v_quart_pad)
709 );
710 END IF ;
711
712 END create_quart_party_dtl;
713
714 PROCEDURE validate_file_header
715 ( p_line_number IN NUMBER ,
716 p_record_type IN VARCHAR2,
717 p_quartfile_type IN VARCHAR2,
718 p_upload_type IN VARCHAR2,
719 p_file_creation_date IN DATE,
720 p_file_sequence_number IN NUMBER,
721 p_uploader_type IN VARCHAR2,
722 p_collector_tan IN VARCHAR2,
723 p_number_of_batches IN NUMBER,
724 p_period IN VARCHAR2,
725 p_start_date IN DATE,
726 p_end_date IN DATE,
727 p_fin_year IN NUMBER,
728 p_return_code OUT NOCOPY VARCHAR2,
729 p_return_message OUT NOCOPY VARCHAR2
730 )
731 IS
732
733 lv_q1_start_date VARCHAR2(11) ;
734 lv_q1_end_date VARCHAR2(11) ;
735 lv_q2_start_date VARCHAR2(11) ;
736 lv_q2_end_date VARCHAR2(11) ;
737 lv_q3_start_date VARCHAR2(11) ;
738 lv_q3_end_date VARCHAR2(11) ;
739 lv_q4_start_date VARCHAR2(11) ;
740 lv_q4_end_date VARCHAR2(11) ;
741 ln_fin_year NUMBER ;
742
743 BEGIN
744
745 IF p_line_number IS NULL THEN
746 p_return_message := p_return_message || ' Line Number should not be null. ' ;
747 IF lv_action <> 'V' THEN
748 goto end_of_procedure ;
749 END IF ;
750 END IF;
751 IF p_record_type IS NULL THEN
752 p_return_message := p_return_message || ' Record Type is null. ' ;
753 IF lv_action <> 'V' THEN
754 goto end_of_procedure ;
755 END IF ;
756 END IF;
757 IF p_quartfile_type IS NULL THEN
758 p_return_message := p_return_message || ' File Type is null. ' ;
759 IF lv_action <> 'V' THEN
760 goto end_of_procedure ;
761 END IF ;
762 END IF;
763 IF p_upload_type IS NULL THEN
764 p_return_message := p_return_message || ' Upload Type is null. ' ;
765 IF lv_action <> 'V' THEN
766 goto end_of_procedure ;
767 END IF ;
768 END IF;
769 IF p_file_creation_date IS NULL THEN
770 p_return_message := p_return_message || ' File Creation Date is null. ' ;
771 IF lv_action <> 'V' THEN
772 goto end_of_procedure ;
773 END IF ;
774 END IF;
775 IF p_file_sequence_number IS NULL THEN
776 p_return_message := p_return_message || ' File Sequence No is null. ' ;
777 IF lv_action <> 'V' THEN
778 goto end_of_procedure ;
779 END IF ;
780 END IF;
781 IF p_uploader_type IS NULL THEN
782 p_return_message := p_return_message || ' Upload Type is null. ' ;
783 IF lv_action <> 'V' THEN
784 goto end_of_procedure ;
785 END IF ;
786 END IF;
787 IF p_collector_tan IS NULL THEN
788 p_return_message := p_return_message || ' Collector TAN is null. ' ;
789 IF lv_action <> 'V' THEN
790 goto end_of_procedure ;
791 END IF ;
792 END IF;
793 IF p_number_of_batches IS NULL THEN
794 p_return_message := p_return_message || ' Batch Count is null. ' ;
795 IF lv_action <> 'V' THEN
796 goto end_of_procedure ;
797 END IF ;
798 END IF;
799
800 -- code to validate whether the given challan start and end dates fall under the specified period.
801 IF p_period = 'Q1' THEN
802 lv_q1_start_date := '01/04/' || p_fin_year ;
803 lv_q1_end_date := '30/06/' || p_fin_year ;
804
805 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
806 p_return_message := ' The given Challan Start and end dates do not fall under the specified Quarter and Year. ' ;
807 goto end_of_procedure ;
808 END IF ;
809 ELSIF p_period = 'Q2' THEN
810 lv_q2_start_date := '01/07/' || p_fin_year;
811 lv_q2_end_date := '30/09/' || p_fin_year;
812
813 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
814 p_return_message := ' The given Challan Start and end dates do not fall under the specified Quarter and Year. ' ;
815 goto end_of_procedure ;
816 END IF ;
817 ELSIF p_period = 'Q3' THEN
818 lv_q3_start_date := '01/10/' || p_fin_year;
819 lv_q3_end_date := '31/12/' || p_fin_year;
820
821 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
822 p_return_message := ' The given Challan Start and end dates do not fall under the specified Quarter and Year. ' ;
823 goto end_of_procedure ;
824 END IF ;
825 ELSIF p_period = 'Q4' THEN
826 ln_fin_year := p_fin_year + 1 ;
827 lv_q4_start_date := '01/01/' || ln_fin_year;
828 lv_q4_end_date := '31/03/' || ln_fin_year;
829
830 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
831 p_return_message := ' The given Challan Start and end dates do not fall under the specified Quarter and Year. ' ;
832 goto end_of_procedure ;
833 END IF ;
834 END IF ;
835
836 IF lv_action = 'V' THEN
837 goto end_of_procedure ;
838 END IF ;
839
840 <<end_of_procedure>>
841 IF p_return_message IS NOT NULL THEN
842 FND_FILE.put_line(FND_FILE.log,' p_return_message ' || p_return_message ) ;
843 p_return_code := 'E';
844 p_return_message := 'File Header Error - ' || 'Line No : ' || p_line_number || '. ' || p_return_message ;
845 END IF;
846
847 END validate_file_header;
848
849 PROCEDURE create_quarterly_file_header(
850 p_line_number IN NUMBER,
851 p_record_type IN VARCHAR2,
852 p_file_type IN VARCHAR2,
853 p_upload_type IN VARCHAR2,
854 p_file_creation_date IN DATE,
855 p_file_sequence_number IN NUMBER,
856 p_uploader_type IN VARCHAR2,
857 p_collector_tan IN VARCHAR2,
858 p_number_of_batches IN NUMBER,
859 p_fh_recordHash IN VARCHAR2,
860 p_fh_fvuVersion IN VARCHAR2,
861 p_fh_fileHash IN VARCHAR2,
862 p_fh_samVersion IN VARCHAR2,
863 p_fh_samHash IN VARCHAR2,
864 p_fh_scmVersion IN VARCHAR2,
865 p_fh_scmHash IN VARCHAR2,
866 p_generate_headers IN VARCHAR2
867 ) IS
868 BEGIN
869 IF p_generate_headers = 'N' THEN
870
871 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
872 p_line_number || v_delimeter||
873 upper(p_record_type) || v_delimeter||
874 upper(p_file_type) || v_delimeter||
875 upper(p_upload_type) || v_delimeter||
876 to_char(p_file_creation_date,'ddmmyyyy') || v_delimeter||
877 p_file_sequence_number || v_delimeter||
878 upper(p_uploader_type) || v_delimeter||
879 p_collector_tan || v_delimeter||
880 p_number_of_batches || v_delimeter||
881 p_fh_recordHash || v_delimeter||
882 p_fh_fvuVersion || v_delimeter||
883 p_fh_fileHash || v_delimeter||
884 p_fh_samVersion || v_delimeter||
885 p_fh_samHash || v_delimeter||
886 p_fh_scmVersion || v_delimeter||
887 p_fh_scmHash);
888 ELSE
889 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
890 LPAD(p_line_number , sq_len_9, v_quart_pad) || v_pad_char ||
891 LPAD( upper(p_record_type) , sq_len_2, v_quart_pad) || v_pad_char ||
892 LPAD(upper(p_file_type) , sq_len_4, v_quart_pad) || v_pad_char ||
893 LPAD(upper(p_upload_type) , sq_len_2, v_quart_pad) || v_pad_char ||
894 LPAD(to_char(p_file_creation_date,'ddmmyyyy') , sq_len_8, v_quart_pad) || v_pad_char ||
895 LPAD(p_file_sequence_number , sq_len_9, v_quart_pad) || v_pad_char ||
896 LPAD(upper(p_uploader_type), sq_len_1, v_quart_pad) || v_pad_char ||
897 LPAD(upper(p_collector_tan) , sq_len_10, v_quart_pad) || v_pad_char ||
898 LPAD(p_number_of_batches , sq_len_9, v_quart_pad) || v_pad_char ||
899 LPAD(NVL(p_fh_recordHash,v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
900 LPAD(NVL(p_fh_fvuVersion, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
901 LPAD(NVL(p_fh_fileHash, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
902 LPAD(NVL(p_fh_samVersion, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
903 LPAD(NVL(p_fh_samHash, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
904 LPAD(NVL(p_fh_scmVersion, v_q_noval_filler) , sq_len_2, v_quart_pad) || v_pad_char ||
905 LPAD(NVL(p_fh_scmHash, v_q_noval_filler) , sq_len_2, v_quart_pad)
906 ) ;
907
908 END IF ;
909
910
911 END create_quarterly_file_header ;
912
913 PROCEDURE validate_batch_header
914 ( p_line_number IN NUMBER,
915 p_record_type IN VARCHAR2,
916 p_batch_number IN NUMBER,
917 p_challan_cnt IN NUMBER,
918 p_quart_form_number IN VARCHAR2,
919 p_collector_tan IN VARCHAR2,
920 p_assessment_year IN NUMBER,
921 p_financial_year IN NUMBER,
922 p_collector_name IN VARCHAR2,
923 p_tan_address1 IN VARCHAR2,
924 p_tan_state_code IN NUMBER,
925 p_tan_pin IN NUMBER,
926 p_collector_status IN VARCHAR2,
927 p_addrChangedSinceLastReturn IN VARCHAR2,
928 p_personNameRespForCollection IN VARCHAR2,
929 p_personDesgnRespForCollection IN VARCHAR2,
930 p_RespPersAddress IN VARCHAR2,
931 p_RespPersState IN NUMBER,
932 p_RespPersPin IN NUMBER,
933 p_RespPersAddrChange IN VARCHAR2,
934 p_totTaxCollectedAsPerParty IN NUMBER,
935 p_ao_approval IN VARCHAR2,
936 p_return_code OUT NOCOPY VARCHAR2,
937 p_return_message OUT NOCOPY VARCHAR2
938 )
939 IS
940 BEGIN
941 IF p_line_number IS NULL THEN
942 p_return_message := p_return_message || ' Line Number should not be null. ' ;
943 IF lv_action <> 'V' THEN
944 goto end_of_procedure ;
945 END IF ;
946 END IF ;
947 IF p_record_type IS NULL THEN
948 p_return_message := p_return_message || ' Record Type is null. ' ;
949 IF lv_action <> 'V' THEN
950 goto end_of_procedure ;
951 END IF ;
952 END IF ;
953 IF p_batch_number IS NULL THEN
954 p_return_message := p_return_message || ' Batch Number is null. ' ;
955 IF lv_action <> 'V' THEN
956 goto end_of_procedure ;
957 END IF ;
958 END IF ;
959 IF p_challan_cnt IS NULL THEN
960 p_return_message := p_return_message || ' Record Count is null. ' ;
961 IF lv_action <> 'V' THEN
962 goto end_of_procedure ;
963 END IF ;
964 END IF ;
965 IF p_quart_form_number IS NULL THEN
966 p_return_message := p_return_message || ' Form Number is null. ' ;
967 IF lv_action <> 'V' THEN
968 goto end_of_procedure ;
969 END IF ;
970 END IF ;
971 IF p_collector_tan IS NULL THEN
972 p_return_message := p_return_message || ' Collector TAN is null. ' ;
973 IF lv_action <> 'V' THEN
974 goto end_of_procedure ;
975 END IF ;
976 END IF ;
977 IF p_assessment_year IS NULL THEN
978 p_return_message := p_return_message || ' Assessment Year is null. ' ;
979 IF lv_action <> 'V' THEN
980 goto end_of_procedure ;
981 END IF ;
982 END IF ;
983 IF p_financial_year IS NULL THEN
984 p_return_message := p_return_message || ' Financial Year is null. ' ;
985 IF lv_action <> 'V' THEN
986 goto end_of_procedure ;
987 END IF ;
988 END IF ;
989 IF p_collector_name IS NULL THEN
990 p_return_message := p_return_message || ' Collector Name is null. ' ;
991 IF lv_action <> 'V' THEN
992 goto end_of_procedure ;
993 END IF ;
994 END IF ;
995 IF p_tan_address1 IS NULL THEN
996 p_return_message := p_return_message || ' Collector Address is null. ' ;
997 IF lv_action <> 'V' THEN
998 goto end_of_procedure ;
999 END IF ;
1000 END IF ;
1001 IF p_tan_state_code IS NULL THEN
1002 p_return_message := p_return_message || ' Collector State is null. ' ;
1003 IF lv_action <> 'V' THEN
1004 goto end_of_procedure ;
1005 END IF ;
1006 END IF ;
1007 IF p_tan_pin IS NULL THEN
1008 p_return_message := p_return_message || ' Collector Pin is null. ' ;
1009 IF lv_action <> 'V' THEN
1010 goto end_of_procedure ;
1011 END IF ;
1012 END IF ;
1013 IF p_collector_status IS NULL THEN
1014 p_return_message := p_return_message || ' Collector Type is null. ' ;
1015 IF lv_action <> 'V' THEN
1016 goto end_of_procedure ;
1017 END IF ;
1018 END IF ;
1019 IF p_addrChangedSinceLastReturn IS NULL THEN
1020 p_return_message := p_return_message || ' Field Collector Address Changed Since last year is null. ' ;
1021 IF lv_action <> 'V' THEN
1022 goto end_of_procedure ;
1023 END IF ;
1024 END IF ;
1025 IF p_personNameRespForCollection IS NULL THEN
1026 p_return_message := p_return_message || ' Person Responsible For Collection is null. ' ;
1027 IF lv_action <> 'V' THEN
1028 goto end_of_procedure ;
1029 END IF ;
1030 END IF ;
1031 IF p_personDesgnRespForCollection IS NULL THEN
1032 p_return_message := p_return_message || ' Designation of Responsible Person is null. ' ;
1033 IF lv_action <> 'V' THEN
1034 goto end_of_procedure ;
1035 END IF ;
1036 END IF ;
1037 IF p_RespPersAddress IS NULL THEN
1038 p_return_message := p_return_message || ' Address of Responsible Person is null. ' ;
1039 IF lv_action <> 'V' THEN
1040 goto end_of_procedure ;
1041 END IF ;
1042 END IF ;
1043 IF p_RespPersState IS NULL THEN
1044 p_return_message := p_return_message || ' State of Responsible Person is null. ' ;
1045 IF lv_action <> 'V' THEN
1046 goto end_of_procedure ;
1047 END IF ;
1048 END IF ;
1049 IF p_RespPersPin IS NULL THEN
1050 p_return_message := p_return_message || ' Pin of Responsible Person is null. ' ;
1051 IF lv_action <> 'V' THEN
1052 goto end_of_procedure ;
1053 END IF ;
1054 END IF ;
1055 IF p_RespPersAddrChange IS NULL THEN
1056 p_return_message := p_return_message || ' Field ''Address of Responsible Person has Changed'' is null. ' ;
1057 IF lv_action <> 'V' THEN
1058 goto end_of_procedure ;
1059 END IF ;
1060 END IF ;
1061 /*
1062 IF p_totTaxCollectedAsPerParty IS NULL THEN
1063 p_return_message := p_return_message || ' Total Deposit Amount as per Challan is null. ' ;
1064 IF lv_action <> 'V' THEN
1065 goto end_of_procedure ;
1066 END IF ;
1067 END IF ;
1068 */
1069 IF p_ao_approval IS NULL THEN
1070 p_return_message := p_return_message || ' AO Approval is null. ' ;
1071 IF lv_action <> 'V' THEN
1072 goto end_of_procedure ;
1073 END IF ;
1074 END IF ;
1075
1076 IF lv_action = 'V' THEN
1077 goto end_of_procedure ;
1078 END IF ;
1079
1080 <<end_of_procedure>>
1081 IF p_return_message IS NOT NULL THEN
1082 p_return_code := 'E';
1083 p_return_message := 'Batch Header Error - ' || 'Line No : ' || p_line_number || '. ' || p_return_message ;
1084 END IF;
1085
1086 END validate_batch_header ;
1087
1088 PROCEDURE create_quarterly_batch_header(
1089 p_line_number IN NUMBER,
1090 p_record_type IN VARCHAR2,
1091 p_batch_number IN NUMBER,
1092 p_challan_count IN NUMBER,
1093 p_form_number IN CHAR,
1094 p_trn_type IN VARCHAR2,
1095 p_batchUpd IN VARCHAR2,
1096 p_org_RRRno IN VARCHAR2,
1097 p_prev_RRRno IN VARCHAR2,
1098 p_RRRno IN VARCHAR2 ,
1099 p_RRRdate IN VARCHAR2 ,
1100 p_collector_last_tan IN VARCHAR2,
1101 p_collector_tan IN VARCHAR2,
1102 p_filler1 IN VARCHAR2,
1103 p_collector_pan IN VARCHAR2,
1104 p_assessment_year IN NUMBER,
1105 p_financial_year IN NUMBER,
1106 p_period IN VARCHAR2,
1107 p_collector_name IN VARCHAR2,
1108 p_collector_branch IN VARCHAR2,
1109 p_tan_address1 IN VARCHAR2,
1110 p_tan_address2 IN VARCHAR2,
1111 p_tan_address3 IN VARCHAR2,
1112 p_tan_address4 IN VARCHAR2,
1113 p_tan_address5 IN VARCHAR2,
1114 p_tan_state_code IN NUMBER,
1115 p_tan_pin IN NUMBER,
1116 p_collector_email IN VARCHAR2,
1117 p_collector_stdCode IN NUMBER,
1118 p_collector_phoneNo IN NUMBER,
1119 p_addrChangedSinceLastReturn IN VARCHAR2,
1120 p_status_of_collector IN VARCHAR2,
1121 p_pers_resp_for_collection IN VARCHAR2,
1122 p_RespPerson_designation IN VARCHAR2,
1123 p_RespPerson_address1 IN VARCHAR2,
1124 p_RespPerson_address2 IN VARCHAR2,
1125 p_RespPerson_address3 IN VARCHAR2,
1126 p_RespPerson_address4 IN VARCHAR2,
1127 p_RespPerson_address5 IN VARCHAR2,
1128 p_RespPerson_state IN VARCHAR2,
1129 p_RespPerson_pin IN NUMBER,
1130 p_RespPerson_email IN VARCHAR2,
1131 p_RespPerson_remark IN VARCHAR2,
1132 p_RespPerson_stdCode IN NUMBER,
1133 p_RespPerson_phoneNo IN NUMBER,
1134 p_RespPerson_addressChange IN VARCHAR2,
1135 p_totTaxcollectedAsPerChallan IN NUMBER,
1136 p_tds_circle IN VARCHAR2,
1137 p_salaryRecords_count IN VARCHAR2,
1138 p_gross_total IN VARCHAR2,
1139 p_ao_approval IN VARCHAR2,
1140 p_ao_approval_number IN VARCHAR2,
1141 p_recHash IN VARCHAR2,
1142 p_generate_headers IN VARCHAR2)
1143 IS
1144 BEGIN
1145
1146 IF p_generate_headers = 'N' THEN
1147 UTL_FILE.PUT_LINE( jai_ap_tds_etds_pkg.v_filehandle, p_line_number || v_delimeter||
1148 upper(p_record_type) || v_delimeter||
1149 p_batch_number || v_delimeter||
1150 p_challan_count || v_delimeter||
1151 upper(p_form_number) || v_delimeter||
1152 p_trn_type || v_delimeter||
1153 p_batchUpd || v_delimeter||
1154 p_org_RRRno || v_delimeter||
1155 p_prev_RRRno || v_delimeter||
1156 p_RRRno || v_delimeter||
1157 p_RRRdate || v_delimeter||
1158 p_collector_last_tan || v_delimeter||
1159 upper(p_collector_tan) || v_delimeter||
1160 p_filler1 || v_delimeter||
1161 p_collector_pan || v_delimeter||
1162 p_assessment_year || v_delimeter||
1163 p_financial_year || v_delimeter||
1164 p_period || v_delimeter||
1165 p_collector_name || v_delimeter||
1166 p_collector_branch || v_delimeter||
1167 p_tan_address1 || v_delimeter||
1168 p_tan_address2 || v_delimeter||
1169 p_tan_address3 || v_delimeter||
1170 p_tan_address4 || v_delimeter||
1171 p_tan_address5 || v_delimeter||
1172 p_tan_state_code || v_delimeter||
1173 p_tan_pin || v_delimeter||
1174 p_collector_email || v_delimeter||
1175 p_collector_stdCode || v_delimeter||
1176 p_collector_phoneNo || v_delimeter||
1177 p_addrChangedSinceLastReturn || v_delimeter||
1178 p_status_of_collector || v_delimeter||
1179 p_pers_resp_for_collection || v_delimeter||
1180 p_RespPerson_designation || v_delimeter||
1181 p_RespPerson_address1 || v_delimeter||
1182 p_RespPerson_address2 || v_delimeter||
1183 p_RespPerson_address3 || v_delimeter||
1184 p_RespPerson_address4 || v_delimeter||
1185 p_RespPerson_address5 || v_delimeter||
1186 p_RespPerson_state || v_delimeter||
1187 p_RespPerson_pin || v_delimeter||
1188 p_RespPerson_email || v_delimeter||
1189 p_RespPerson_remark || v_delimeter||
1190 p_RespPerson_stdCode || v_delimeter||
1191 p_RespPerson_phoneNo || v_delimeter||
1192 p_RespPerson_addressChange || v_delimeter||
1193 to_char(p_totTaxcollectedAsPerChallan,v_format_amount) || v_delimeter||
1194 p_tds_circle || v_delimeter||
1195 p_salaryRecords_count || v_delimeter||
1196 p_gross_total || v_delimeter||
1197 upper(p_ao_approval) || v_delimeter||
1198 p_ao_approval_number || v_delimeter||
1199 p_recHash ) ;
1200 ELSE
1201 UTL_FILE.PUT_LINE( jai_ap_tds_etds_pkg.v_filehandle,
1202 LPAD(p_line_number, sq_len_9 , v_quart_pad) || v_pad_char ||
1203 LPAD(upper(p_record_type) , sq_len_2 , v_quart_pad) || v_pad_char ||
1204 LPAD(p_batch_number, sq_len_9 , v_quart_pad) || v_pad_char ||
1205 LPAD(p_challan_count, sq_len_9 , v_quart_pad) || v_pad_char ||
1206 LPAD(upper(p_form_number), sq_len_4 , v_quart_pad) || v_pad_char ||
1207 LPAD(NVL(p_trn_type,v_q_noval_filler ), sq_len_2 , v_quart_pad) || v_pad_char ||
1208 LPAD(NVL(p_batchUpd,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1209 LPAD(NVL(p_org_RRRno,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1210 LPAD(NVL(p_prev_RRRno,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1211 LPAD(NVL(p_RRRno,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1212 LPAD(NVL(p_RRRdate,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1213 LPAD(NVL(p_collector_last_tan,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1214 LPAD(upper(p_collector_tan), sq_len_10 , v_quart_pad) || v_pad_char ||
1215 LPAD(NVL(p_filler1,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1216 LPAD(NVL(p_collector_pan,v_q_null_filler ) , sq_len_10 , v_quart_pad) || v_pad_char ||
1217 LPAD(p_assessment_year , sq_len_6 , v_quart_pad) || v_pad_char ||
1218 LPAD(p_financial_year , sq_len_6 , v_quart_pad) || v_pad_char ||
1219 LPAD(p_period , sq_len_2 , v_quart_pad) || v_pad_char ||
1220 LPAD(p_collector_name, sq_len_75 , v_quart_pad) || v_pad_char ||
1221 LPAD(NVL(p_collector_branch ,v_q_null_filler ), sq_len_75 , v_quart_pad) || v_pad_char ||
1222 LPAD(p_tan_address1, sq_len_25 , v_quart_pad) || v_pad_char ||
1223 LPAD(NVL(p_tan_address2,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1224 LPAD(NVL(p_tan_address3,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1225 LPAD(NVL(p_tan_address4,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1226 LPAD(NVL(p_tan_address5,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1227 LPAD(p_tan_state_code, sq_len_2 , v_quart_pad) || v_pad_char ||
1228 LPAD(p_tan_pin, sq_len_6 , v_quart_pad) || v_pad_char ||
1229 LPAD(NVL(p_collector_email,v_q_null_filler ) , sq_len_75 , v_quart_pad) || v_pad_char ||
1230 LPAD(NVL(p_collector_stdCode,v_quart_numfill ) , sq_len_5 , v_quart_pad) || v_pad_char ||
1231 LPAD(NVL(p_collector_phoneNo,v_quart_numfill ) , sq_len_10 , v_quart_pad) || v_pad_char ||
1232 LPAD(p_addrChangedSinceLastReturn, sq_len_1 , v_quart_pad) || v_pad_char ||
1233 LPAD(p_status_of_collector, sq_len_1 , v_quart_pad) || v_pad_char ||
1234 LPAD(p_pers_resp_for_collection, sq_len_75 , v_quart_pad) || v_pad_char ||
1235 LPAD(p_RespPerson_designation, sq_len_20 , v_quart_pad) || v_pad_char ||
1236 LPAD(p_RespPerson_address1, sq_len_25 , v_quart_pad) || v_pad_char ||
1237 LPAD(NVL(p_RespPerson_address2,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1238 LPAD(NVL(p_RespPerson_address3,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1239 LPAD(NVL(p_RespPerson_address4,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1240 LPAD(NVL(p_RespPerson_address5,v_q_null_filler ) , sq_len_25 , v_quart_pad) || v_pad_char ||
1241 LPAD(p_RespPerson_state, sq_len_2 , v_quart_pad) || v_pad_char ||
1242 LPAD(p_RespPerson_pin, sq_len_6 , v_quart_pad) || v_pad_char ||
1243 LPAD(NVL(p_RespPerson_email,v_q_null_filler ) , sq_len_75 , v_quart_pad) || v_pad_char ||
1244 LPAD(NVL(p_RespPerson_remark,v_q_null_filler ) , sq_len_75 , v_quart_pad) || v_pad_char ||
1245 LPAD(NVL(p_RespPerson_stdCode,v_quart_numfill ) , sq_len_5 , v_quart_pad) || v_pad_char ||
1246 LPAD(NVL(p_RespPerson_phoneNo,v_quart_numfill ) , sq_len_10 , v_quart_pad) || v_pad_char ||
1247 LPAD(p_RespPerson_addressChange, sq_len_1 , v_quart_pad) || v_pad_char ||
1248 LPAD(to_char(p_totTaxCollectedAsPerChallan,v_format_amount), sq_len_15 , v_quart_pad) || v_pad_char ||
1249 LPAD(NVL(p_tds_circle,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1250 LPAD(NVL(p_salaryRecords_count,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1251 LPAD(NVL(p_gross_total,v_q_noval_filler ) , sq_len_2 , v_quart_pad) || v_pad_char ||
1252 LPAD(upper(p_ao_approval), sq_len_1 , v_quart_pad) || v_pad_char ||
1253 LPAD(NVL(p_ao_approval_number,v_q_noval_filler ), sq_len_15 , v_quart_pad) || v_pad_char ||
1254 LPAD(NVL(p_recHash,v_q_noval_filler ) , sq_len_2 , v_quart_pad)
1255 );
1256 END IF ;
1257
1258 END create_quarterly_batch_header;
1259
1260 PROCEDURE validate_challan_detail
1261 ( p_line_number IN NUMBER ,
1262 p_record_type IN VARCHAR2,
1263 p_batch_number IN NUMBER,
1264 p_challan_dtl_slno IN NUMBER,
1265 p_party_cnt IN NUMBER,
1266 p_nil_challan_indicat IN VARCHAR2,
1267 p_tcs_section IN VARCHAR2,
1268 p_tcs_amt IN NUMBER,
1269 p_surcharge_amt IN NUMBER,
1270 p_cess_amt IN NUMBER,
1271 p_amt_of_oth IN NUMBER,
1272 p_tcs_amount IN NUMBER,
1273 p_total_income_tcs IN NUMBER,
1274 p_challan_no IN VARCHAR2,
1275 p_bank_branch_code IN VARCHAR2,
1276 p_challan_Date IN DATE,
1277 p_check_number IN NUMBER,
1278 p_amt_of_int IN NUMBER ,
1279 p_total_deposit IN NUMBER ,
1280 p_tcs_income_tax IN NUMBER,
1281 p_tcs_surcharge IN NUMBER ,
1282 p_tcs_cess IN NUMBER ,
1283 p_tcs_interest_amt IN NUMBER ,
1284 p_tcs_other_amt IN NUMBER ,
1285 p_return_code OUT NOCOPY VARCHAR2,
1286 p_return_message OUT NOCOPY VARCHAR2
1287 )
1288 IS
1289 BEGIN
1290 IF p_line_number IS NULL THEN
1291 p_return_message := p_return_message || ' Line number should not be null. ' ;
1292 IF lv_action <> 'V' THEN
1293 goto end_of_procedure ;
1294 END IF ;
1295 END IF ;
1296 IF p_record_type IS NULL THEN
1297 p_return_message := p_return_message || ' Record Type is null. ' ;
1298 IF lv_action <> 'V' THEN
1299 goto end_of_procedure ;
1300 END IF ;
1301 END IF ;
1302 IF p_batch_number IS NULL THEN
1303 p_return_message := p_return_message || ' Batch Number is null. ' ;
1304 IF lv_action <> 'V' THEN
1305 goto end_of_procedure ;
1306 END IF ;
1307 END IF ;
1308 IF p_challan_dtl_slno IS NULL THEN
1309 p_return_message := p_return_message || ' Challan Record Number is null. ' ;
1310 IF lv_action <> 'V' THEN
1311 goto end_of_procedure ;
1312 END IF ;
1313 END IF ;
1314 IF p_party_cnt IS NULL THEN
1315 p_return_message := p_return_message || ' Party Count is null . ' ;
1316 IF lv_action <> 'V' THEN
1317 goto end_of_procedure ;
1318 END IF ;
1319 END IF ;
1320 IF p_nil_challan_indicat IS NULL THEN
1321 p_return_message := p_return_message || ' NIL Challan Indicator is null . ' ;
1322 IF lv_action <> 'V' THEN
1323 goto end_of_procedure ;
1324 END IF ;
1325 END IF ;
1326 IF p_tcs_section IS NULL THEN
1327 p_return_message := p_return_message || ' TCS Section is null . ' ;
1328 IF lv_action <> 'V' THEN
1329 goto end_of_procedure ;
1330 END IF ;
1331 END IF ;
1332 IF p_tcs_amt IS NULL THEN
1333 p_return_message := p_return_message || ' TCS Amount is null . ' ;
1334 IF lv_action <> 'V' THEN
1335 goto end_of_procedure ;
1336 END IF ;
1337 END IF ;
1338 IF p_surcharge_amt IS NULL THEN
1339 p_return_message := p_return_message || ' TCS Surcharge Amount is null . ' ;
1340 IF lv_action <> 'V' THEN
1341 goto end_of_procedure ;
1342 END IF ;
1343 END IF ;
1344 IF p_cess_amt IS NULL THEN
1345 p_return_message := p_return_message || ' TCS Cess Amount is null . ' ;
1346 IF lv_action <> 'V' THEN
1347 goto end_of_procedure ;
1348 END IF ;
1349 END IF ;
1350 IF p_amt_of_oth IS NULL THEN
1351 p_return_message := p_return_message || ' TCS Other Amount is null . ' ;
1352 IF lv_action <> 'V' THEN
1353 goto end_of_procedure ;
1354 END IF ;
1355 END IF ;
1356 IF p_tcs_amount IS NULL THEN
1357 p_return_message := p_return_message || ' Total TCS Amount is null . ' ;
1358 IF lv_action <> 'V' THEN
1359 goto end_of_procedure ;
1360 END IF ;
1361 END IF ;
1362 IF p_challan_no IS NULL THEN
1363 p_return_message := p_return_message || ' Challan No is null . ' ;
1364 IF lv_action <> 'V' THEN
1365 goto end_of_procedure ;
1366 END IF ;
1367 END IF ;
1368 IF p_challan_Date IS NULL THEN
1369 p_return_message := p_return_message || ' Challan Date is null . ' ;
1370 IF lv_action <> 'V' THEN
1371 goto end_of_procedure ;
1372 END IF ;
1373 END IF ;
1374
1375 IF p_total_income_tcs IS NULL THEN
1376 p_return_message := p_return_message || ' Total Tax Deposit Amount is null . ' ;
1377 IF lv_action <> 'V' THEN
1378 goto end_of_procedure ;
1379 END IF ;
1380 END IF ;
1381 IF p_amt_of_int IS NULL THEN
1382 p_return_message := p_return_message || ' TCS Interest Amount is null . ' ;
1383 IF lv_action <> 'V' THEN
1384 goto end_of_procedure ;
1385 END IF ;
1386 END IF ;
1387 IF p_total_deposit IS NULL THEN
1388 p_return_message := p_return_message || ' Amount As Per Party is null . ' ;
1389 IF lv_action <> 'V' THEN
1390 goto end_of_procedure ;
1391 END IF ;
1392 END IF ;
1393 IF p_tcs_income_tax IS NULL THEN
1394 p_return_message := p_return_message || ' Total Tax Deposit Amount As Per Party is null . ' ;
1395 IF lv_action <> 'V' THEN
1396 goto end_of_procedure ;
1397 END IF ;
1398 END IF ;
1399 IF p_tcs_surcharge IS NULL THEN
1400 p_return_message := p_return_message || ' Total TCS Surcharge is null . ' ;
1401 IF lv_action <> 'V' THEN
1402 goto end_of_procedure ;
1403 END IF ;
1404 END IF ;
1405 IF p_tcs_cess IS NULL THEN
1406 p_return_message := p_return_message || ' Total TCS Cess is null . ' ;
1407 IF lv_action <> 'V' THEN
1408 goto end_of_procedure ;
1409 END IF ;
1410 END IF ;
1411 IF p_tcs_interest_amt IS NULL THEN
1412 p_return_message := p_return_message || ' Total TCS Interest is null . ' ;
1413 IF lv_action <> 'V' THEN
1414 goto end_of_procedure ;
1415 END IF ;
1416 END IF ;
1417 IF p_tcs_other_amt IS NULL THEN
1418 p_return_message := p_return_message || ' Total TCS Other Amount is null . ' ;
1419 IF lv_action <> 'V' THEN
1420 goto end_of_procedure ;
1421 END IF ;
1422 END IF ;
1423
1424 jai_ap_tds_etds_pkg.check_numeric(p_challan_no, 'Check Number : ' || p_check_number || ' Challan Number is not a Numeric Value', lv_action);
1425 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);
1426
1427 IF lv_action = 'V' THEN
1428 goto end_of_procedure ;
1429 END IF ;
1430
1431 <<end_of_procedure>>
1432 IF p_return_message IS NOT NULL THEN
1433 p_return_code := 'E';
1434 p_return_message := 'Challan Detail Error - ' || 'Check Number : ' || p_check_number || '. ' || p_return_message ;
1435 END IF;
1436 END validate_challan_detail;
1437
1438 PROCEDURE create_quart_challan_dtl(
1439 p_line_number IN NUMBER ,
1440 p_record_type IN VARCHAR2 ,
1441 p_batch_number IN NUMBER ,
1442 p_challan_dtl_slno IN NUMBER ,
1443 p_collection_cnt IN NUMBER ,
1444 p_nil_challan_indicator IN VARCHAR2 ,
1445 p_ch_updIndicator IN VARCHAR2 ,
1446 p_filler2 IN VARCHAR2 ,
1447 p_filler3 IN VARCHAR2 ,
1448 p_filler4 IN VARCHAR2 ,
1449 p_last_bank_challan_no IN VARCHAR2 ,
1450 p_bank_challan_no IN VARCHAR2 ,
1451 p_last_transfer_voucher_no IN VARCHAR2 ,
1452 p_transfer_voucher_no IN NUMBER ,
1453 p_last_bank_branch_code IN VARCHAR2 ,
1454 p_bank_branch_code IN VARCHAR2 ,
1455 p_challan_lastDate IN VARCHAR2 ,
1456 p_challan_Date IN DATE ,
1457 p_filler5 IN VARCHAR2 ,
1458 p_filler6 IN VARCHAR2 ,
1459 p_tcs_section IN VARCHAR2 ,
1460 p_tcs_amt IN NUMBER ,
1461 p_surcharge_amt IN NUMBER ,
1462 p_cess_amt IN NUMBER ,
1463 p_amt_of_int IN NUMBER ,
1464 p_amt_of_oth IN NUMBER ,
1465 p_tcs_amount IN NUMBER ,
1466 p_last_total_depositAmt IN NUMBER ,
1467 p_total_deposit IN NUMBER ,
1468 p_tcs_income_tax IN NUMBER ,
1469 p_tcs_surcharge IN NUMBER ,
1470 p_tcs_cess IN NUMBER ,
1471 p_total_income_tcs IN NUMBER ,
1472 p_tcs_interest_amt IN NUMBER ,
1473 p_tcs_other_amt IN NUMBER ,
1474 p_check_number IN NUMBER ,
1475 p_book_entry IN VARCHAR2 ,
1476 p_remarks IN VARCHAR2 ,
1477 p_ch_recHash IN VARCHAR2,
1478 p_generate_headers IN VARCHAR2
1479 )
1480 IS
1481 BEGIN
1482 IF p_generate_headers = 'N' THEN
1483 UTL_FILE.PUT_LINE(
1484 jai_ap_tds_etds_pkg.v_filehandle,p_line_number || v_delimeter||
1485 upper(p_record_type) || v_delimeter||
1486 p_batch_number || v_delimeter||
1487 p_challan_dtl_slno || v_delimeter||
1488 p_collection_cnt || v_delimeter||
1489 p_nil_challan_indicator || v_delimeter||
1490 p_ch_updIndicator || v_delimeter||
1491 p_filler2 || v_delimeter||
1492 p_filler3 || v_delimeter||
1493 p_filler4 || v_delimeter||
1494 p_last_bank_challan_no || v_delimeter||
1495 substr(p_bank_challan_no, 1,5) || v_delimeter||
1496 p_last_transfer_voucher_no || v_delimeter||
1497 p_transfer_voucher_no || v_delimeter||
1498 p_last_bank_branch_code || v_delimeter||
1499 p_bank_branch_code || v_delimeter||
1500 p_challan_lastDate || v_delimeter||
1501 to_char(p_challan_Date,'ddmmyyyy') || v_delimeter||
1502 p_filler5 || v_delimeter||
1503 p_filler6 || v_delimeter||
1504 p_tcs_section || v_delimeter||
1505 to_char(p_tcs_amt,v_format_amount) || v_delimeter||
1506 to_char(p_surcharge_amt,v_format_amount) || v_delimeter||
1507 to_char(p_cess_amt,v_format_amount) || v_delimeter||
1508 to_char(p_amt_of_int,v_format_amount) || v_delimeter||
1509 to_char(p_amt_of_oth,v_format_amount) || v_delimeter||
1510 to_char(p_tcs_amount,v_format_amount) || v_delimeter||
1511 p_last_total_depositAmt || v_delimeter||
1512 to_char(p_total_deposit,v_format_amount) || v_delimeter||
1513 to_char(p_tcs_income_tax,v_format_amount) || v_delimeter||
1514 to_char(p_tcs_surcharge,v_format_amount) || v_delimeter||
1515 to_char(p_tcs_cess,v_format_amount) || v_delimeter||
1516 to_char(p_total_income_tcs,v_format_amount) || v_delimeter||
1517 to_char(p_tcs_interest_amt,v_format_amount) || v_delimeter||
1518 to_char(p_tcs_other_amt, v_format_amount) || v_delimeter||
1519 p_check_number || v_delimeter||
1520 p_book_entry || v_delimeter||
1521 p_remarks || v_delimeter||
1522 p_ch_recHash ) ;
1523 ELSE
1524 UTL_FILE.PUT_LINE( jai_ap_tds_etds_pkg.v_filehandle,
1525 LPAD(p_line_number , sq_len_9, v_quart_pad) || v_pad_char ||
1526 LPAD(upper(p_record_type) , sq_len_2, v_quart_pad) || v_pad_char ||
1527 LPAD(p_batch_number , sq_len_9, v_quart_pad) || v_pad_char ||
1528 LPAD(p_challan_dtl_slno , sq_len_9, v_quart_pad) || v_pad_char ||
1529 LPAD(p_collection_cnt , sq_len_9, v_quart_pad) || v_pad_char ||
1530 LPAD(p_nil_challan_indicator , sq_len_1, v_quart_pad) || v_pad_char ||
1531 LPAD(NVL(p_ch_updIndicator,v_q_noval_filler), sq_len_2, v_quart_pad) || v_pad_char ||
1532 LPAD(NVL(p_filler2,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1533 LPAD(NVL(p_filler3,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1534 LPAD(NVL(p_filler4,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1535 LPAD(NVL(p_last_bank_challan_no,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1536 LPAD(NVL(p_bank_challan_no,v_q_null_filler ) , sq_len_5, v_quart_pad) || v_pad_char ||
1537 LPAD(NVL(p_last_transfer_voucher_no,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1538 LPAD(NVL(p_transfer_voucher_no,v_quart_numfill ) , sq_len_9, v_quart_pad) || v_pad_char ||
1539 LPAD(NVL(p_last_bank_branch_code,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1540 LPAD(NVL(p_bank_branch_code,v_q_null_filler ) , sq_len_7, v_quart_pad) || v_pad_char ||
1541 LPAD(NVL(p_challan_lastDate,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1542 LPAD(to_char(p_challan_Date,'ddmmyyyy') , sq_len_8, v_quart_pad) || v_pad_char ||
1543 LPAD(NVL(p_filler5,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1544 LPAD(NVL(p_filler6,v_q_noval_filler ) , sq_len_2, v_quart_pad) || v_pad_char ||
1545 LPAD(p_tcs_section, sq_len_3, v_quart_pad) || v_pad_char ||
1546 LPAD(to_char(p_tcs_amt , v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1547 LPAD(to_char( p_surcharge_amt, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1548 LPAD(to_char( p_cess_amt, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1549 LPAD(to_char( p_amt_of_int, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1550 LPAD(to_char( p_amt_of_oth, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1551 LPAD(to_char( p_tcs_amount, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1552 LPAD(NVL(p_last_total_depositAmt, v_quart_numfill) , sq_len_2, v_quart_pad) || v_pad_char ||
1553 LPAD(to_char( p_total_deposit, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1554 LPAD(to_char( p_tcs_income_tax, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1555 LPAD(to_char( p_tcs_surcharge, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1556 LPAD(to_char( p_tcs_cess, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1557 LPAD(to_char( p_total_income_tcs, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1558 LPAD(to_char( p_tcs_interest_amt, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1559 LPAD(to_char( p_tcs_other_amt, v_format_amount) , sq_len_15, v_quart_pad) || v_pad_char ||
1560 LPAD(NVL(p_check_number,v_quart_numfill ) , sq_len_15, v_quart_pad) || v_pad_char ||
1561 LPAD(NVL(p_book_entry,v_q_null_filler ) , sq_len_1, v_quart_pad) || v_pad_char ||
1562 LPAD(NVL(p_remarks,v_q_null_filler ) , sq_len_14, v_quart_pad) || v_pad_char ||
1563 LPAD(NVL(p_ch_recHash,v_q_noval_filler ) , sq_len_2, v_quart_pad)
1564 );
1565 END IF ;
1566
1567 END create_quart_challan_dtl;
1568
1569 --Date Population procedures for ETCS Yearly Returns
1570
1571 PROCEDURE create_file_header(
1572 p_line_number IN NUMBER,
1573 p_record_type IN VARCHAR2,
1574 p_file_type IN VARCHAR2,
1575 p_upload_type IN VARCHAR2,
1576 p_file_creation_date IN DATE,
1577 p_file_sequence_number IN NUMBER,
1578 p_deductor_tan IN VARCHAR2,
1579 p_number_of_batches IN NUMBER
1580 ) IS
1581
1582 BEGIN
1583
1584 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
1585 LPAD(p_line_number, s_line_number, v_pad_number)
1586 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(p_record_type, s_record_type, v_pad_char)
1587 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(p_file_type, s_file_type, v_pad_char)
1588 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(p_upload_type, s_upload_type, v_pad_char)
1589 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(to_char(p_file_creation_date,'ddmmyyyy'), s_date, v_pad_date)
1590 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(p_file_sequence_number, s_file_sequence_number, v_pad_number)
1591 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductor_tan,' '), s_deductor_tan, v_pad_char)
1592 ||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)
1593 );
1594 END create_file_header ;
1595
1596 PROCEDURE create_dd IS
1597 BEGIN
1598
1599 -- Deductee Detail
1600 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, fnd_global.local_chr(10) );
1601 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
1602 LPAD('LineNo', s_line_number, v_pad_char)
1603 ||v_pad_char||RPAD('RT', s_record_type, v_pad_char)
1604 ||v_pad_char||LPAD('B.No.', s_batch_number, v_pad_char)
1605 ||v_pad_char||LPAD('DSlNo', s_deductee_slno, v_pad_char)
1606 ||v_pad_char||RPAD('Secn.', s_deductee_section, v_pad_char)
1607 ||v_pad_char||RPAD('BCode', s_deductee_code, v_pad_char)
1608 ||v_pad_char||RPAD('PrtyPan', s_deductee_pan, v_pad_char)
1609 ||v_pad_char||RPAD('Party Name', s_deductee_name, v_pad_char)
1610 ||v_pad_char||RPAD('Party Addr1', s_deductee_address1, v_pad_char)
1611 ||v_pad_char||RPAD('Party Addr2', s_deductee_address2, v_pad_char)
1612 ||v_pad_char||RPAD('Party Addr3', s_deductee_address3, v_pad_char)
1613 ||v_pad_char||RPAD('Party Addr4', s_deductee_address4, v_pad_char)
1614 ||v_pad_char||RPAD('Party Addr5', s_deductee_address5, v_pad_char)
1615 ||v_pad_char||LPAD('PState', s_deductee_state, v_pad_char)
1616 ||v_pad_char||LPAD('PtePin', s_deductee_pin, v_pad_char)
1617 ||v_pad_char||LPAD('Purch Amount', s_filler, v_pad_char)
1618 ||v_pad_char||LPAD('Pay. Amount', s_payment_amount, v_pad_char)
1619 ||v_pad_char||LPAD('Pay. Date', s_date, v_pad_char)
1620 ||v_pad_char||LPAD('PBE', s_book_ent_oth , v_pad_char)
1621 ||v_pad_char||LPAD('TxRt', s_tax_rate, v_pad_char)
1622 ||v_pad_char||LPAD('Filler4', s_filler6, v_pad_char)
1623 ||v_pad_char||LPAD('TxColected', s_tax_deducted, v_pad_char)
1624 ||v_pad_char||LPAD('TxCol.Dt', s_date, v_pad_date)
1625 ||v_pad_char||RPAD('BSRCode', s_bank_branch_code, v_pad_char)
1626 ||v_pad_char||LPAD('TxPay.Dt', s_date, v_pad_date)
1627 ||v_pad_char||RPAD('ChlnNo', s_challan_no, v_pad_char)
1628 ||v_pad_char||LPAD('TcsCrtDt', s_date, v_pad_char)
1629 ||v_pad_char||RPAD('R', s_reason_for_nDeduction, v_pad_char)
1630 ||v_pad_char||LPAD('Filler5', s_filler, v_pad_char)
1631 );
1632
1633
1634 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
1635 LPAD(v_underline_char, s_line_number, v_underline_char)
1636 ||v_pad_char||RPAD(v_underline_char, s_record_type, v_underline_char)
1637 ||v_pad_char||LPAD(v_underline_char, s_batch_number, v_underline_char)
1638 ||v_pad_char||LPAD(v_underline_char, s_deductee_slno, v_underline_char)
1639 ||v_pad_char||RPAD(v_underline_char, s_deductee_section, v_underline_char)
1640 ||v_pad_char||RPAD(v_underline_char, s_deductee_code, v_underline_char)
1641 ||v_pad_char||RPAD(v_underline_char, s_deductee_pan, v_underline_char)
1642 ||v_pad_char||RPAD(v_underline_char, s_deductee_name, v_underline_char)
1643 ||v_pad_char||RPAD(v_underline_char, s_deductee_address1, v_underline_char)
1644 ||v_pad_char||RPAD(v_underline_char, s_deductee_address2, v_underline_char)
1645 ||v_pad_char||RPAD(v_underline_char, s_deductee_address3, v_underline_char)
1646 ||v_pad_char||RPAD(v_underline_char, s_deductee_address4, v_underline_char)
1647 ||v_pad_char||RPAD(v_underline_char, s_deductee_address5, v_underline_char)
1648 ||v_pad_char||LPAD(v_underline_char, s_deductee_state, v_underline_char)
1649 ||v_pad_char||LPAD(v_underline_char, s_deductee_pin, v_underline_char)
1650 ||v_pad_char||LPAD(v_underline_char, s_filler, v_underline_char)
1651 ||v_pad_char||LPAD(v_underline_char, s_payment_amount, v_underline_char)
1652 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1653 ||v_pad_char||LPAD(v_underline_char, s_book_ent_oth , v_underline_char)
1654 ||v_pad_char||LPAD(v_underline_char, s_tax_rate, v_underline_char)
1655 ||v_pad_char||LPAD(v_underline_char, s_filler6, v_underline_char)
1656 ||v_pad_char||LPAD(v_underline_char, s_tax_deducted, v_underline_char)
1657 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1658 ||v_pad_char||RPAD(v_underline_char, s_bank_branch_code, v_underline_char)
1659 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1660 ||v_pad_char||RPAD(v_underline_char, s_challan_no, v_underline_char)
1661 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1662 ||v_pad_char||RPAD(v_underline_char, s_reason_for_nDeduction, v_underline_char)
1663 ||v_pad_char||LPAD(v_underline_char, s_filler, v_underline_char)
1664 );
1665
1666 END create_dd;
1667
1668
1669 PROCEDURE create_fh(p_batch_id IN NUMBER) IS
1670 v_req JAI_AP_ETDS_REQUESTS%rowtype;
1671 BEGIN
1672
1673 -- File Header
1674 SELECT * INTO v_req FROM JAI_AP_ETDS_REQUESTS WHERE batch_id = p_batch_id;
1675
1676 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, 'Input Parameters to this Request:');
1677 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, '-------------------------------------------------');
1678 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
1679 ' batch_id ->'||v_req.batch_id||fnd_global.local_chr(10)
1680 ||' request_id ->'||v_req.request_id||fnd_global.local_chr(10)
1681 ||' operating_unit_id ->'||v_req.operating_unit_id||fnd_global.local_chr(10)
1682 ||' org_tan_number ->'||v_req.org_tan_number||fnd_global.local_chr(10)
1683 ||' financial_year ->'||v_req.financial_year||fnd_global.local_chr(10)
1684 ||' tax_authority_id ->'||v_req.tax_authority_id||fnd_global.local_chr(10)
1685 ||' tax_authority_site_id ->'||v_req.tax_authority_site_id||fnd_global.local_chr(10)
1686 ||' organization_id ->'||v_req.organization_id||fnd_global.local_chr(10)
1687 ||' deductor_name ->'||v_req.deductor_name||fnd_global.local_chr(10)
1688 ||' deductor_state ->'||v_req.deductor_state||fnd_global.local_chr(10)
1689 ||' addr_changed_since_last_ret->'||v_req.addr_changed_since_last_ret||fnd_global.local_chr(10)
1690 ||' deductor_status ->'||v_req.deductor_status||fnd_global.local_chr(10)
1691 ||' person_resp_for_deduction ->'||v_req.person_resp_for_deduction||fnd_global.local_chr(10)
1692 ||' designation_of_pers_resp ->'||v_req.designation_of_pers_resp||fnd_global.local_chr(10)
1693 ||' challan_start_date ->'||v_req.challan_start_date||fnd_global.local_chr(10)
1694 ||' challan_end_date ->'||v_req.challan_end_date||fnd_global.local_chr(10)
1695 ||' file_path ->'||v_req.file_path||fnd_global.local_chr(10)
1696 ||' filename ->'||v_req.filename||fnd_global.local_chr(10)
1697 );
1698
1699
1700 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
1701 LPAD('LineNo', s_line_number, v_pad_char)
1702 ||v_pad_char||RPAD('RT', s_record_type, v_pad_char)
1703 ||v_pad_char||RPAD('FT', s_file_type, v_pad_char)
1704 ||v_pad_char||RPAD('UT', s_upload_type, v_pad_char)
1705 ||v_pad_char||LPAD('FileDate', s_date, v_pad_char)
1706 ||v_pad_char||LPAD('FSeqNo', s_file_sequence_number, v_pad_char)
1707 ||v_pad_char||RPAD('Org Tan', s_deductor_tan, v_pad_char)
1708 ||v_pad_char||LPAD('NoOfBatches', s_number_of_batches, v_pad_char)
1709 );
1710 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
1711 LPAD(v_underline_char, s_line_number, v_underline_char)
1712 ||v_pad_char||RPAD(v_underline_char, s_record_type, v_underline_char)
1713 ||v_pad_char||RPAD(v_underline_char, s_file_type, v_underline_char)
1714 ||v_pad_char||RPAD(v_underline_char, s_upload_type, v_underline_char)
1715 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1716 ||v_pad_char||LPAD(v_underline_char, s_file_sequence_number, v_underline_char)
1717 ||v_pad_char||RPAD(v_underline_char, s_deductor_tan, v_underline_char)
1718 ||v_pad_char||LPAD(v_underline_char, s_number_of_batches, v_underline_char)
1719 );
1720 END create_fh;
1721
1722 PROCEDURE create_cd IS
1723 BEGIN
1724
1725 -- Challan Detail
1726 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle, fnd_global.local_chr(10) );
1727 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
1728 LPAD('LineNo', s_line_number, v_pad_char)
1729 ||v_pad_char||RPAD('RT', s_record_type, v_pad_char)
1730 ||v_pad_char||LPAD('B.No', s_batch_number, v_pad_char)
1731 ||v_pad_char||LPAD('CSlNo', s_challan_slno, v_pad_char)
1732 ||v_pad_char||RPAD('Secn.', s_challan_section, v_pad_char)
1733 ||v_pad_char||LPAD('TCS amount', s_amount_tcs, v_pad_char)
1734 ||v_pad_char||LPAD('Surcharge amt',s_amount_sur , v_pad_char)
1735 ||v_pad_char||LPAD('CESS amount', s_amount_cess , v_pad_char)
1736 ||v_pad_char||LPAD('Amount of int', s_amount_cess , v_pad_char)
1737 ||v_pad_char||LPAD('Amount - others', s_amount_cess , v_pad_char)
1738 ||v_pad_char||LPAD('Total amount', s_amount_deducted , v_pad_char)
1739 ||v_pad_char||LPAD('Chq/DD.No', s_chq_dd_num , v_pad_char)
1740 ||v_pad_char||RPAD('BankBrCode', s_bank_branch_code, v_pad_char)
1741 ||v_pad_char||LPAD('TxdpDate', s_date, v_pad_char) --chq deposit date
1742 ||v_pad_char||RPAD('Chal.Num.', s_challan_no, v_pad_char)
1743 ||v_pad_char||RPAD('TBE ', s_tds_dep_book_ent , v_pad_char)
1744 ||v_pad_char||RPAD('C', s_filler4 , v_pad_char)
1745 );
1746 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
1747 LPAD(v_underline_char, s_line_number, v_underline_char)
1748 ||v_pad_char||RPAD(v_underline_char, s_record_type, v_underline_char)
1749 ||v_pad_char||LPAD(v_underline_char, s_batch_number, v_underline_char)
1750 ||v_pad_char||LPAD(v_underline_char, s_challan_slno, v_underline_char)
1751 ||v_pad_char||RPAD(v_underline_char, s_challan_section, v_underline_char)
1752 ||v_pad_char||LPAD(v_underline_char, s_amount_tcs , v_underline_char)
1753 ||v_pad_char||LPAD(v_underline_char, s_amount_sur , v_underline_char)
1754 ||v_pad_char||LPAD(v_underline_char, s_amount_CESS , v_underline_char)
1755 ||v_pad_char||LPAD(v_underline_char, s_amount_CESS , v_underline_char)
1756 ||v_pad_char||LPAD(v_underline_char, s_amount_CESS , v_underline_char)
1757 ||v_pad_char||LPAD(v_underline_char, s_amount_deducted , v_underline_char)
1758 ||v_pad_char||LPAD(v_underline_char, s_chq_dd_num , v_underline_char)
1759 ||v_pad_char||RPAD(v_underline_char, s_bank_branch_code, v_underline_char)
1760 ||v_pad_char||LPAD(v_underline_char, s_date, v_underline_char)
1761 ||v_pad_char||RPAD(v_underline_char, s_challan_no, v_underline_char)
1762 ||v_pad_char||RPAD(v_underline_char, s_tds_dep_book_ent , v_underline_char)
1763 ||v_pad_char||RPAD(v_underline_char, s_filler4 , v_underline_char)
1764 );
1765 END create_cd;
1766
1767 PROCEDURE create_deductee_detail(
1768 p_line_number IN NUMBER,
1769 p_record_type IN VARCHAR2,
1770 p_batch_number IN NUMBER,
1771 p_deductee_slno IN NUMBER,
1772 p_deductee_section IN VARCHAR2,
1773 p_deductee_code IN VARCHAR2,
1774 p_deductee_pan IN VARCHAR2,
1775 p_deductee_name IN VARCHAR2,
1776 p_deductee_address1 IN VARCHAR2,
1777 p_deductee_address2 IN VARCHAR2,
1778 p_deductee_address3 IN VARCHAR2,
1779 p_deductee_address4 IN VARCHAR2,
1780 p_deductee_address5 IN VARCHAR2,
1781 p_deductee_state IN VARCHAR2,
1782 p_deductee_pin IN NUMBER,
1783 p_purchase_amount IN NUMBER,
1784 p_payment_amount IN NUMBER,
1785 p_payment_date IN DATE,
1786 p_book_ent_oth IN VARCHAR2,
1787 p_tax_rate IN NUMBER,
1788 p_filler6 IN VARCHAR2,
1789 p_tax_deducted IN NUMBER,
1790 p_tax_deducted_date IN DATE,
1791 p_tax_payment_date IN DATE,
1792 p_bank_branch_code IN VARCHAR2,
1793 p_challan_no IN VARCHAR2,
1794 p_tds_certificate_date IN DATE,
1795 p_reason_for_nDeduction IN VARCHAR2,
1796 p_filler7 IN NUMBER
1797 ) IS
1798
1799 BEGIN
1800
1801 UTL_FILE.PUT_LINE(jai_ap_tds_etds_pkg.v_filehandle,
1802 LPAD(p_line_number, s_line_number, v_pad_number)
1803 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(p_record_type, s_record_type, v_pad_char)
1804 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(p_batch_number, s_batch_number, v_pad_number)
1805 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(p_deductee_slno,0), s_deductee_slno, v_pad_number)
1806 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(p_deductee_section, s_deductee_section, v_pad_char)
1807 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_code,' '), s_deductee_code, v_pad_char)
1808 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_pan,' '), s_deductee_pan, v_pad_char)
1809 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_name,' '), s_deductee_name, v_pad_char)
1810 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_address1,' '), s_deductee_address1, v_pad_char)
1811 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_address2,' '), s_deductee_address2, v_pad_char)
1812 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_address3,' '), s_deductee_address3, v_pad_char)
1813 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_address4,' '), s_deductee_address4, v_pad_char)
1814 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_deductee_address5,' '), s_deductee_address5, v_pad_char)
1815 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(p_deductee_state,'0'), s_deductee_state, v_pad_number)
1816 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(p_deductee_pin,0), s_deductee_pin, v_pad_number)
1817 ||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)
1818 ||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)
1819 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(to_char(p_payment_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
1820 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(p_book_ent_oth , s_book_ent_oth, v_pad_char)
1821 ||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)
1822 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_filler6,' '), s_filler6, v_pad_char)
1823 ||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)
1824 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(to_char(p_tax_deducted_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
1825 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_bank_branch_code,' '), s_bank_branch_code, v_pad_char)
1826 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(to_char(p_tax_payment_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
1827 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_challan_no,' '), s_challan_no, v_pad_char)
1828 ||jai_ap_tds_etds_pkg.v_debug_pad_char||LPAD(nvl(to_char(p_tds_certificate_date, 'ddmmyyyy'),' '), s_date, v_pad_date)
1829 ||jai_ap_tds_etds_pkg.v_debug_pad_char||RPAD(nvl(p_reason_for_nDeduction,' '), s_reason_for_nDeduction, v_pad_char)
1830 ||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)
1831 );
1832
1833 END create_deductee_detail;
1834
1835
1836 PROCEDURE populate_details(
1837 p_batch_id IN NUMBER,
1838 p_org_tan_num IN VARCHAR2,
1839 p_tax_authority_id IN NUMBER,
1840 p_tax_authority_site_id IN NUMBER,
1841 p_from_date IN DATE,
1842 p_to_date IN DATE,
1843 p_collection_code IN VARCHAR2
1844 )
1845 IS
1846
1847 cursor c_tax_amount(cp_tax_type in varchar2, cp_source_doc_id in number, cp_check_id in number)
1848 is
1849 select nvl(sum(tax_amt),0)
1850 from jai_rgm_taxes
1851 where tax_type = cp_tax_type
1852 and trx_ref_id in
1853 ( select trx_ref_id
1854 from jai_rgm_refs_all jra
1855 where
1856 source_ref_document_id = cp_source_doc_id and
1857 jra.source_document_date between p_from_date and p_to_date
1858 and settlement_id
1859 IN ( select settlement_id
1860 from jai_ap_rgm_payments
1861 where check_id = cp_check_id
1862 )
1863 ) ;
1864
1865
1866 cursor c_pan_no(cp_party_id in number)
1867 is
1868 select pan_no
1869 from JAI_CMN_CUS_ADDRESSES
1870 where customer_id = cp_party_id
1871 and confirm_pan = 'Y'
1872 and pan_no is not null
1873 and rownum = 1 ;
1874
1875 cursor c_buyer_code(cp_party_id in number)
1876 is
1877 select
1878 DECODE(jca.tcs_customer_type, 'COMPANIES', '01', 'OTHERS', '02') buyer_code
1879 from JAI_CMN_CUS_ADDRESSES jca
1880 where jca.customer_id = cp_party_id
1881 and jca.tcs_customer_type is not null
1882 and rownum = 1 ;
1883
1884 cursor c_get_recs
1885 is
1886 select *
1887 from jai_ar_etcs_t for update ;
1888
1889 cursor c_line_amt(cp_source_document_ref_id number, cp_check_id in number) is
1890 select nvl(sum(line_amt),0)
1891 from jai_rgm_refs_all jra
1892 where
1893 source_ref_document_id = cp_source_document_ref_id and
1894 jra.source_document_date between p_from_date and p_to_date
1895 and settlement_id
1896 IN ( select settlement_id
1897 from jai_ap_rgm_payments
1898 where check_id = cp_check_id
1899 ) ;
1900
1901
1902 ln_line_amt number ;
1903
1904 lv_customer_type varchar2(2) ;
1905 lv_pan_no JAI_CMN_CUS_ADDRESSES.pan_no%TYPE ;
1906 lv_item_classification jai_rgm_lookups.display_value%type ;
1907 ln_tcs_amount number ;
1908 ln_surcharge_amount number ;
1909 ln_cess_amount number ;
1910 ln_tcs_cess_amount number ;
1911 ln_sur_cess_amount number ;
1912 -- Date 03/07/2007 by sacsethi for bug 6157120
1913 ln_tcs_sh_cess_amount number ;
1914
1915
1916 Cursor c_rcpt_date(cp_receipt_id number)
1917 is
1918 select receipt_date
1919 from ar_cash_receipts
1920 where cash_receipt_id = cp_receipt_id ;
1921
1922 Cursor c_source_ref_type(cp_source_ref_id in number)
1923 is
1924 select source_document_type
1925 from jai_rgm_refs_all
1926 where source_document_id = cp_source_ref_id
1927 and source_document_type IN (jai_constants.ar_cash_tax_confirmed , jai_constants.trx_type_inv_comp)
1928 and rownum = 1 ;
1929
1930 Cursor c_inv_date(cp_inv_id number)
1931 is
1932 select trx_date
1933 from ra_customer_trx_all
1934 where customer_trx_id = cp_inv_id ;
1935 -- Date 28/06/2007 by sacsethi for bug 6157120
1936 /* CURSOR c_check_dtls(cpn_check_id IN NUMBER) IS
1937 SELECT INTERNAL_BANK_ACCOUNT_ID
1938 FROM IBY_PAYMENTS_ALL
1939 WHERE paper_document_number =cpn_check_id;*/
1940
1941 -- Date 28/06/2007 by sacsethi for bug 6157120
1942 /* CURSOR c_bank_branch_code(cp_bank_account_id IN NUMBER) IS
1943 select a.branch_number
1944 from ce_bank_branches_v a, ce_bank_accounts b
1945 where a.branch_party_id = b.bank_branch_id
1946 and b.bank_account_id = cp_bank_account_id ;*/
1947
1948 /*SELECT a.bank_num
1949 FROM ap_bank_branches a, ap_bank_accounts_all b
1950 WHERE a.bank_branch_id = b.bank_branch_id
1951 AND b.bank_account_id = cp_bank_account_id;*/
1952
1953 v_bank_account_id NUMBER(15);
1954 v_bank_branch_code ce_bank_branches_v.BRANCH_NUMBER%TYPE;
1955
1956 lv_source_ref_type jai_rgm_refs_all.source_document_type%TYPE ;
1957 lv_doc_date date ;
1958
1959 BEGIN
1960
1961 insert into jai_ar_etcs_t
1962 (
1963 Batch_id ,
1964 tcs_check_id ,
1965 check_number ,
1966 tcs_check_date ,
1967 challan_no ,
1968 challan_date ,
1969 bank_branch_code ,
1970 source_document_id ,
1971 party_id ,
1972 party_site_id ,
1973 collection_flag ,
1974 tcs_tax_rate ,
1975 exempted_flag ,
1976 certificate_issue_date ,
1977 created_by ,
1978 creation_date ,
1979 last_updated_by ,
1980 last_update_date ,
1981 last_update_login
1982 )
1983 select
1984 p_batch_id ,
1985 jrp.check_id ,
1986 jrp.check_number ,
1987 jrp. check_deposit_date ,
1988 jrp.challan_no ,
1989 jrp.check_date ,
1990 jrp.bsr_code ,
1991 jra.source_ref_document_id ,
1992 jra.party_id ,
1993 jra.party_site_id ,
1994 p_collection_code ,
1995 jrt.tax_rate ,
1996 jrt.exempted_flag ,
1997 jrc.issue_date ,
1998 fnd_global.user_id ,
1999 sysdate ,
2000 fnd_global.user_id ,
2001 sysdate ,
2002 fnd_global.login_id
2003 from
2004 jai_ap_rgm_payments jrp,
2005 jai_rgm_refs_all jra,
2006 jai_rgm_taxes jrt,
2007 jai_rgm_certificates jrc
2008 where
2009 jrp.settlement_id = jra.settlement_id and
2010 jrp.tax_authority_id = p_tax_authority_id and
2011 jrp.tax_authority_site_id = nvl(p_tax_authority_site_id,jrp.tax_authority_site_id) and
2012 jrp.org_tan_no = p_org_tan_num and
2013 jrt.tax_type = 'TCS' and
2014 jrt.trx_ref_id = jra.trx_ref_id and
2015 jrc.certificate_id = jra.certificate_id and
2016 jra.item_classification = p_collection_code and
2017 jra.source_document_date between p_from_date and p_to_date and
2018 jra.settlement_id is not null and
2019 jra.certificate_id is not null
2020 group by
2021 p_batch_id ,
2022 jrp.check_id ,
2023 jrp.check_number ,
2024 jrp.check_date ,
2025 jrp.challan_no ,
2026 jrp.check_deposit_date ,
2027 jrp.bsr_code ,
2028 jra.source_ref_document_id ,
2029 jra.party_id ,
2030 jra.party_site_id ,
2031 jra.item_classification ,
2032 jrt.tax_rate ,
2033 jrt.exempted_flag ,
2034 jrc.issue_date ;
2035
2036 FOR rec in c_get_recs
2037 LOOP
2038
2039 ln_line_amt := null ;
2040 ln_tcs_amount := null ;
2041 ln_surcharge_amount := null ;
2042 ln_tcs_cess_amount := null ;
2043 ln_sur_cess_amount := null ;
2044 ln_cess_amount := null ;
2045
2046 -- Date 03/07/2007 by sacsethi for bug 6157120
2047 ln_tcs_sh_cess_amount := null ;
2048
2049 open c_line_amt(rec.source_document_id, rec.tcs_check_id) ;
2050 fetch c_line_amt into ln_line_amt ;
2051 close c_line_amt ;
2052
2053 open c_tax_amount(jai_constants.tax_type_tcs,rec.source_document_id, rec.tcs_check_id ) ;
2054 fetch c_tax_amount into ln_tcs_amount ;
2055 close c_tax_amount ;
2056
2057 open c_tax_amount(jai_constants.tax_type_tcs_surcharge, rec.source_document_id, rec.tcs_check_id ) ;
2058 fetch c_tax_amount into ln_surcharge_amount ;
2059 close c_tax_amount ;
2060
2061 open c_tax_amount(jai_constants.tax_type_tcs_cess, rec.source_document_id, rec.tcs_check_id ) ;
2062 fetch c_tax_amount into ln_tcs_cess_amount ;
2063 close c_tax_amount ;
2064
2065 open c_tax_amount(jai_constants.tax_type_tcs_surcharge_cess, rec.source_document_id, rec.tcs_check_id ) ;
2066 fetch c_tax_amount into ln_sur_cess_amount ;
2067 close c_tax_amount ;
2068
2069 -- Date 03/07/2007 by sacsethi for bug 6157120
2070
2071 open c_tax_amount(jai_constants.tax_type_sh_tcs_edu_cess, rec.source_document_id, rec.tcs_check_id ) ;
2072 fetch c_tax_amount into ln_tcs_sh_cess_amount ;
2073 close c_tax_amount ;
2074
2075 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
2076
2077 open c_pan_no(rec.party_id) ;
2078 fetch c_pan_no into lv_pan_no ;
2079 close c_pan_no ;
2080
2081 lv_pan_no := substr(lv_pan_no, 1,10);
2082
2083 open c_buyer_code(rec.party_id) ;
2084 fetch c_buyer_code into lv_customer_type ;
2085 close c_buyer_code ;
2086
2087 open c_source_ref_type(rec.source_document_id);
2088 fetch c_source_ref_type into lv_source_ref_type ;
2089 close c_source_ref_type ;
2090
2091 lv_source_ref_type := substr(lv_source_ref_type, 1,50);
2092
2093 IF lv_source_ref_type = jai_constants.trx_type_inv_comp THEN
2094 open c_inv_date(rec.source_document_id) ;
2095 fetch c_inv_date into lv_doc_date ;
2096 close c_inv_date ;
2097 ELSIF lv_source_ref_type = jai_constants.ar_cash_tax_confirmed THEN
2098 open c_rcpt_date(rec.source_document_id) ;
2099 fetch c_rcpt_date into lv_doc_date ;
2100 close c_rcpt_date ;
2101 END IF ;
2102
2103
2104 -- Date 28/06/2007 by sacsethi for bug 6157120
2105 -- Commenting this code because of no use
2106 /*
2107 OPEN c_check_dtls(rec.check_number);
2108 FETCH c_check_dtls INTO v_bank_account_id ;
2109 CLOSE c_check_dtls;
2110
2111 OPEN c_bank_branch_code(v_bank_account_id);
2112 FETCH c_bank_branch_code INTO v_bank_branch_code;
2113 CLOSE c_bank_branch_code;
2114
2115 v_bank_branch_code := substr(v_bank_branch_code, 1,10);
2116 */
2117
2118 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 ||
2119 ' v_bank_branch_code : ' || v_bank_branch_code ) ;
2120
2121 update jai_ar_etcs_t
2122 set
2123 line_amt = ln_line_amt ,
2124 tcs_amt = ln_tcs_amount ,
2125 surcharge_amt = ln_surcharge_amount ,
2126 cess_amt = ln_cess_amount ,
2127 party_code = lv_customer_type ,
2128 party_pan = lv_pan_no ,
2129 source_document_date = lv_doc_date ,
2130 source_document_type = lv_source_ref_type -- ,
2131 --bank_branch_code = v_bank_branch_code
2132 where current of c_get_recs;
2133
2134 END LOOP ;
2135
2136 END populate_details;
2137
2138 PROCEDURE generate_etcs_returns(
2139 p_err_buf OUT NOCOPY VARCHAR2,
2140 p_ret_code OUT NOCOPY NUMBER,
2141 p_tan_number IN VARCHAR2,
2142 p_organization_id IN NUMBER,
2143 p_fin_year IN NUMBER,
2144 p_tax_authority_id IN NUMBER,
2145 p_tax_authority_site_id IN NUMBER,
2146 p_seller_name IN VARCHAR2,
2147 p_seller_state IN VARCHAR2,
2148 p_addrChangedSinceLastRet IN VARCHAR2,
2149 p_persRespForCollection IN VARCHAR2,
2150 p_desgOfPersResponsible IN VARCHAR2,
2151 p_Start_Date IN DATE,
2152 p_End_Date IN DATE,
2153 p_pro_rcpt_num_org_ret IN NUMBER,
2154 p_file_path IN VARCHAR2,
2155 p_filename IN VARCHAR2,
2156 p_collection_code IN VARCHAR2,
2157 p_generate_headers IN VARCHAR2 DEFAULT NULL,
2158 p_period IN VARCHAR2 DEFAULT NULL,
2159 p_RespPersAddress IN VARCHAR2 DEFAULT NULL,
2160 p_RespPersState IN VARCHAR2 DEFAULT NULL,
2161 p_RespPersPin IN NUMBER DEFAULT NULL,
2162 p_RespPersAddrChange IN VARCHAR2 DEFAULT NULL,
2163 p_action IN VARCHAR2 DEFAULT NULL,
2164 p_collector_status IN VARCHAR2 DEFAULT NULL
2165 )
2166 IS
2167
2168 -- Date 28/06/2007 by sacsethi for bug 6157120
2169
2170 CURSOR c_pan_number(p_organization_id IN NUMBER) IS
2171 Select ATTRIBUTE_VALUE
2172 from jai_rgm_org_regns_v jrorv ,HR_ORGANIZATION_INFORMATION hrou
2173 where jrorv.attribute_code = jai_constants.pan_no AND
2174 hrou.org_information_context= jai_constants.accounting_information and
2175 hrou.organization_id = p_organization_id and
2176 jrorv.ORGANIZATION_ID = hrou.org_information3 AND
2177 jrorv.REGIME_CODE = jai_constants.tds_regime ;
2178 /*
2179 SELECT attribute2
2180 FROM hr_all_organization_units
2181 WHERE organization_id = p_organization_id;*/
2182
2183 -- to get financial and assessment years
2184 CURSOR c_fin_year(p_tan_number IN varchar2, p_fin_year IN NUMBER) IS
2185 SELECT start_date, end_date
2186 FROM JAI_AP_TDS_YEARS
2187 WHERE tan_no = p_tan_number --Date 26/05/2007 by sacsethi for bug 6153881
2188 AND fin_year = p_fin_year;
2189
2190 -- gives Location_id linked to Organization
2191 CURSOR c_location_linked_to_org(p_organization_id IN NUMBER) IS
2192 SELECT location_id
2193 FROM hr_all_organization_units
2194 WHERE organization_id = p_organization_id;
2195
2196 v_location_id hr_all_organization_units.location_id%TYPE ;
2197
2198 -- to get address details of location linked to given organization
2199 CURSOR c_address_details(p_location_id IN NUMBER) IS
2200 SELECT location_code, address_line_1, address_line_2, address_line_3, null, null,
2201 replace(postal_code, ' ') postal_code
2202 FROM hr_locations_all
2203 WHERE location_id = p_location_id;
2204
2205 CURSOR c_get_errors(cp_batch_id JAI_AP_ETDS_T.batch_id%TYPE ) IS
2206 Select Error_Message from jai_ap_etds_errors_t
2207 where batch_id = cp_batch_id ;
2208
2209 ln_errors_exist number ;
2210
2211 v_location_code HR_LOCATIONS_ALL.location_code%TYPE;
2212 v_tan_address1 HR_LOCATIONS_ALL.address_line_1%TYPE;
2213 v_tan_address2 HR_LOCATIONS_ALL.address_line_2%TYPE;
2214 v_tan_address3 HR_LOCATIONS_ALL.address_line_3%TYPE;
2215 v_tan_address4 VARCHAR2(75);
2216 v_tan_address5 VARCHAR2(75);
2217 v_postal_code HR_LOCATIONS_ALL.postal_code%TYPE;
2218 v_tan_pin NUMBER(6);
2219
2220 ln_batch_id number ;
2221 lv_etcs_yearly_returns varchar2(1) ;
2222 v_conc_request_id NUMBER(15) ;
2223 v_deductor_pan VARCHAR2(200);
2224 v_start_date JAI_AP_TDS_YEARS.start_date%type ;
2225 v_end_date JAI_AP_TDS_YEARS.end_date%type;
2226
2227 -- File Header variables
2228 v_line_number NUMBER(9);
2229 v_record_type CHAR(2);
2230 v_file_type CHAR(3) ;
2231 v_quartfile_type CHAR(3);
2232 v_upload_type CHAR(1);
2233 v_file_creation_date date ;
2234 v_file_sequence_number NUMBER(9);
2235 v_seller_tan VARCHAR2(10);
2236 v_number_of_batches NUMBER(9) ;
2237
2238
2239 -- Quarterly File Header Variables
2240 v_fh_recordHash varchar2(1);
2241 v_fh_fvuVersion varchar2(1);
2242 v_fh_fileHash varchar2(1);
2243 v_fh_samVersion varchar2(1);
2244 v_fh_samHash varchar2(1);
2245 v_fh_scmVersion varchar2(1);
2246 v_fh_scmHash varchar2(1);
2247 p_return_code VARCHAR2(1) ;
2248 p_return_message VARCHAR2(2000) ;
2249 lv_generate_headers VARCHAR2(1) ;
2250 v_uploader_type varchar2(1);
2251
2252 -- Batch Header
2253
2254 v_totTaxDeductedAsPerChallan NUMBER;
2255 v_totTaxDeductedAsPerDeductee NUMBER;
2256 v_challan_cnt NUMBER(9) := 0;
2257 v_deductee_cnt NUMBER(9) := 0;
2258 v_batch_number NUMBER(9);
2259 v_form_number CHAR(4) ;
2260 v_financial_year VARCHAR2(6);
2261 v_assessment_year VARCHAR2(6);
2262 v_ack_num_tan_app NUMBER(14);
2263 v_pro_rcpt_num_org_ret NUMBER(14);
2264 v_filler1 VARCHAR2(1) ;
2265 v_filler2 VARCHAR2(1) ;
2266 v_filler3 VARCHAR2(1) ;
2267 v_filler4 VARCHAR2(1) ;
2268 v_seller_name VARCHAR2(75);
2269 v_quarterlyOrYearly VARCHAR2(2) ;
2270 v_addrChangedSinceLastReturn VARCHAR2(1);
2271 v_seller_status VARCHAR2(1);
2272 v_personNameRespForDedection VARCHAR2(75);
2273 v_personDesgnRespForDedection VARCHAR2(20);
2274 v_tan_state_code NUMBER(2);
2275 lv_dummy_date date;
2276
2277 -- Quarterly Batch Header variables
2278 v_bh_trnType VARCHAR2(1);
2279 v_bh_batchUpd VARCHAR2(1);
2280 v_bh_org_RRRno VARCHAR2(1);
2281 v_bh_prev_RRRno VARCHAR2(1);
2282 v_bh_RRRno VARCHAR2(1);
2283 v_bh_RRRdate VARCHAR2(1);
2284 v_bh_deductor_last_tan VARCHAR2(1);
2285 v_deductor_branch VARCHAR2(75);
2286 v_deductor_email VARCHAR2(75);
2287 v_deductor_stdCode NUMBER(5);
2288 v_deductor_phoneNo NUMBER(10);
2289 v_RespPerson_address2 VARCHAR2(25);
2290 v_RespPerson_address3 VARCHAR2(25);
2291 v_RespPerson_address4 VARCHAR2(25);
2292 v_RespPerson_address5 VARCHAR2(25);
2293 v_RespPerson_email VARCHAR2(75);
2294 v_RespPerson_remark VARCHAR2(75);
2295 v_RespPerson_stdCode NUMBER(5);
2296 v_RespPerson_phoneNo NUMBER(10);
2297 v_bh_tds_circle CHAR(1);
2298 v_bh_salaryRecords_count CHAR(1);
2299 v_bh_gross_total CHAR(1);
2300 v_ao_approval varchar2(1);
2301 v_ao_approval_number VARCHAR2(15);
2302 v_quart_form_number varchar2(4);
2303 v_bh_recHash varchar2(1);
2304
2305 cursor c_deductee_cnt
2306 is
2307 select count(1), nvl(sum(tcs_amt + surcharge_amt + cess_amt),0)
2308 from
2309 jai_ar_etcs_t
2310 where
2311 batch_id = ln_batch_id ;
2312
2313 CURSOR c_quart_deductee_cnt(cp_batch_id IN NUMBER , cp_check_number IN NUMBER ) IS
2314 select sum ( count( distinct tcs_tax_rate ) )
2315 from jai_ar_etcs_t
2316 WHERE batch_id = cp_batch_id
2317 and check_number = cp_check_number
2318 group by source_document_id, tcs_tax_rate, exempted_flag ;
2319
2320
2321 cursor c_challan_cnt
2322 is
2323 select count(1)
2324 from
2325 ( select 1
2326 from
2327 jai_ar_etcs_t
2328 where
2329 batch_id = ln_batch_id
2330 group by
2331 NVL(challan_no, 'No Challan Number'),
2332 NVL(challan_date,lv_dummy_date),
2333 NVL(bank_branch_code,'No Bank Branch'),
2334 NVL(tcs_check_id , -1 )
2335 ) ;
2336
2337
2338 --Challan Detail
2339
2340 v_challan_dtl_slno number ;
2341 v_collection_code varchar2(1) ;
2342 ln_amt_of_oth number(14);
2343 v_tcs_section varchar2(5) ;
2344
2345 -- Quarterly Challan Detail
2346 v_nil_challan_indicator char(1);
2347 v_q_deductee_cnt number(9);
2348 v_last_bank_challan_no varchar2(1);
2349 v_last_transfer_voucher_no varchar2(1);
2350 v_transfer_voucher_no number(9);
2351 v_last_bank_branch_code varchar2(1);
2352 v_challan_lastDate varchar2(1);
2353 v_filler5 varchar2(1);
2354 v_last_total_depositAmt varchar2(1);
2355 v_remarks varchar2(14);
2356 V_ch_recHash varchar2(1);
2357 v_total_deposit number(15);
2358 v_bank_branch_code varchar2(7);
2359 v_ch_updIndicator varchar2(1);
2360 ln_amt_of_tds number(15);
2361
2362 CURSOR c_challan_records(p_batch_id IN NUMBER) IS
2363 select NVL(bank_branch_code,'No Bank Branch') bank_branch_code,
2364 NVL(challan_no,'No Challan Number') challan_no,
2365 NVL(challan_date,lv_dummy_date) challan_date,
2366 check_number check_number,
2367 tcs_check_id ,
2368 sum(tcs_amt + surcharge_amt + cess_amt ) total_tcs_amount,
2369 sum(tcs_amt) tcs_amt,
2370 sum(surcharge_amt) surcharge_amt,
2371 sum(cess_amt) cess_amt
2372 from jai_ar_etcs_t a
2373 where a.batch_id = p_batch_id
2374 group by NVL(bank_branch_code,'No Bank Branch'),
2375 NVL(challan_no,'No Challan Number'), NVL(challan_date,lv_dummy_date),
2376 check_number, tcs_check_id;
2377
2378 cd c_challan_records%ROWTYPE ;
2379
2380 cursor c_book_entry(cp_check_id number)
2381 is
2382 select nvl(book_entry_deposited,'N')
2383 from jai_ap_rgm_payments
2384 where check_id = cp_check_id ;
2385
2386 v_book_entry VARCHAR2(1) ;
2387
2388 -- Deductee Detail
2389 v_challan_line_num number ;
2390 v_party_name hz_parties.party_name%type ;
2391 v_deductee_state_code number;
2392 v_reason_for_nDeduction varchar2(1);
2393 ln_diff_rate number ;
2394 v_deductee_slno number ;
2395 v_book_ent_oth varchar2(1);
2396 v_filler6 varchar2(1) ;
2397 v_filler number(14);
2398 v_section_code varchar2(5) ;
2399
2400 -- Quarterly Deductee detail
2401 v_dh_mode varchar2(1);
2402 v_emp_serial_no varchar2(1);
2403 v_last_emp_pan varchar2(1);
2404 v_last_emp_pan_refno varchar2(1);
2405 v_party_pan_ref_no varchar2(10);
2406 v_last_total_tax_deducted varchar2(1);
2407 v_last_total_tax_deposit varchar2(1);
2408 v_deposit_date varchar2(1);
2409 v_grossingUp_ind varchar2(1);
2410 v_certificate_issue_date varchar2(1);
2411 v_remarks2 varchar2(1);
2412 v_remarks3 varchar2(1);
2413 v_dh_recHash varchar2(1);
2414 v_quart_deductee_code varchar2(1);
2415
2416
2417
2418
2419 cursor c_cust_name(cp_customer_id in number)
2420 is
2421 select party_name
2422 from hz_parties
2423 where party_id = cp_customer_id ;
2424
2425 cursor c_cust_site_dtls (cp_party_site_id number)
2426 is
2427 select
2428 address1,
2429 address2,
2430 address3,
2431 address4,
2432 city,
2433 state,
2434 postal_code
2435 from
2436 ar_addresses_v
2437 where
2438 address_id = cp_party_site_id ;
2439
2440 v_site_dtls c_cust_site_dtls%rowtype;
2441
2442 cursor c_state_code(p_state_name in varchar2) is
2443 select meaning
2444 from fnd_common_lookups
2445 where lookup_type = 'IN_STATE'
2446 and lookup_code = p_state_name;
2447
2448 cursor c_deductee_records(p_batch_id in number, p_challan_line_num in number) is
2449 select
2450 party_id,challan_line_num, party_site_id,exempted_flag,
2451 party_code, party_pan,source_document_id,
2452 NVL(bank_branch_code,'No Bank Branch') bank_branch_code,
2453 NVL(challan_no,'No Challan Number') challan_no,
2454 NVL(challan_date,lv_dummy_date) challan_date,
2455 check_number,
2456 tcs_tax_rate,
2457 sum(line_amt) line_amount,
2458 max(certificate_issue_date) certificate_issue_date,
2459 max(source_document_date) transaction_date ,
2460 max(tcs_check_date) tcs_check_date,
2461 sum(tcs_amt) tcs_amt,
2462 sum(surcharge_amt) surcharge_amt,
2463 sum(cess_amt) cess_amt,
2464 sum(tcs_amt + surcharge_amt + cess_amt ) total_tcs_amount
2465 from jai_ar_etcs_t a
2466 where a.batch_id = p_batch_id and
2467 challan_line_num = NVL(p_challan_line_num, challan_line_num)
2468 group by
2469 challan_line_num, party_id, party_site_id,exempted_flag, tcs_tax_rate,
2470 check_number,party_code, party_pan,
2471 NVL(bank_branch_code,'No Bank Branch'),
2472 NVL(challan_no,'No Challan Number') ,
2473 NVL(challan_date,lv_dummy_date) ,
2474 source_document_id ;
2475
2476 dd c_deductee_records%ROWTYPE ;
2477
2478 PROCEDURE process_deductee_records
2479 IS
2480 v_deductee_total_tax_deducted number(15);
2481 v_quart_book_ent_oth varchar2(1);
2482 BEGIN
2483 OPEN c_deductee_records(ln_batch_id, v_challan_line_num) ;
2484 LOOP
2485 FETCH c_deductee_records INTO dd ;
2486 EXIT WHEN
2487 c_deductee_records%NOTFOUND ;
2488
2489 v_party_name := null;
2490 v_site_dtls := null;
2491 v_reason_for_nDeduction := null;
2492 v_filler := null;
2493 v_deductee_state_code := null;
2494 v_reason_for_nDeduction := null;
2495 ln_diff_rate :=null;
2496
2497 v_line_number := v_line_number + 1;
2498 v_deductee_slno := v_deductee_slno + 1;
2499
2500 OPEN c_cust_name(dd.party_id);
2501 FETCH c_cust_name INTO v_party_name;
2502 CLOSE c_cust_name;
2503
2504 OPEN c_cust_site_dtls(dd.party_site_id);
2505 FETCH c_cust_site_dtls INTO v_site_dtls;
2506 CLOSE c_cust_site_dtls;
2507
2508 OPEN c_state_code(v_site_dtls.state);
2509 FETCH c_state_code INTO v_deductee_state_code;
2510 CLOSE c_state_code;
2511
2512 IF v_deductee_state_code IS NULL THEN
2513 v_deductee_state_code := 99;
2514 END IF;
2515
2516 v_book_ent_oth := ' ';
2517 v_batch_number := '000000001' ;
2518 v_section_code := '206C ' ;
2519
2520 IF lv_etcs_yearly_returns = 'Y' THEN
2521
2522 IF dd.exempted_flag = 'SR' THEN
2523 v_reason_for_nDeduction := 'Y';
2524 ELSE
2525 v_reason_for_nDeduction := 'X';
2526 END IF;
2527
2528 create_deductee_detail
2529 (
2530 p_line_number => v_line_number,
2531 p_record_type => v_record_type,
2532 p_batch_number => v_batch_number,
2533 p_deductee_slno => v_deductee_slno,
2534 p_deductee_section => v_section_code,
2535 p_deductee_code => dd.party_code,
2536 p_deductee_pan => dd.party_pan,
2537 p_deductee_name => v_party_name,
2538 p_deductee_address1 => v_site_dtls.address1,
2539 p_deductee_address2 => v_site_dtls.address2,
2540 p_deductee_address3 => v_site_dtls.address3,
2541 p_deductee_address4 => v_site_dtls.address4,
2542 p_deductee_address5 => v_site_dtls.city,
2543 p_deductee_state => v_deductee_state_code,
2544 p_deductee_pin => v_site_dtls.postal_code,
2545 p_purchase_amount => dd.line_amount,
2546 p_payment_amount => dd.line_amount,
2547 p_payment_date => dd.challan_date,
2548 p_book_ent_oth => v_book_ent_oth,
2549 p_tax_rate => dd.tcs_tax_rate,
2550 p_filler6 => v_filler6,
2551 p_tax_deducted => dd.total_tcs_amount,
2552 p_tax_deducted_date => dd.transaction_date,
2553 p_tax_payment_date => dd.tcs_check_date,
2554 p_bank_branch_code => dd.bank_branch_code,
2555 p_challan_no => dd.challan_no,
2556 p_tds_certificate_date => dd.certificate_issue_date,
2557 p_reason_for_nDeduction => v_reason_for_nDeduction,
2558 p_filler7 => v_filler
2559 );
2560 ELSE
2561
2562 IF dd.exempted_flag = 'SR' THEN
2563 v_reason_for_nDeduction := 'B';
2564 ELSE
2565 v_reason_for_nDeduction := 'A';
2566 END IF;
2567
2568 IF dd.party_code = '01' THEN
2569 v_quart_deductee_code := '1' ;
2570 ELSIF dd.party_code = '02' THEN
2571 v_quart_deductee_code := '2' ;
2572 END IF ;
2573
2574 v_deductee_total_tax_deducted := dd.tcs_amt+dd.surcharge_amt + dd.cess_amt ;
2575 v_quart_book_ent_oth := 'N' ;
2576
2577 p_return_code := null ;
2578 p_return_message := null ;
2579 v_dh_mode :='O';
2580
2581 jai_etcs_pkg.validate_party_detail
2582 ( p_line_number => v_line_number ,
2583 p_record_type => v_record_type ,
2584 p_batch_number => v_batch_number ,
2585 p_challan_line_num => dd.challan_line_num ,
2586 p_party_slno => v_deductee_slno ,
2587 p_dh_mode => v_dh_mode ,
2588 p_quart_party_code => v_quart_deductee_code ,
2589 p_party_pan => dd.party_pan ,
2590 p_party_name => v_party_name ,
2591 p_tcs_amt => dd.tcs_amt,
2592 p_surcharge_amt => dd.surcharge_amt ,
2593 p_cess_amt => dd.cess_amt ,
2594 p_party_total_tax_deducted => v_deductee_total_tax_deducted,
2595 p_base_taxabale_amount => dd.tcs_amt ,
2596 p_gl_date => dd.challan_date ,
2597 p_book_ent_oth => v_book_ent_oth,
2598 p_tcs_tax_rate => dd.tcs_tax_rate,
2599 p_total_purchase => dd.tcs_amt,
2600 p_party_total_tax_deposit =>v_deductee_total_tax_deducted,
2601 p_return_code => p_return_code,
2602 p_return_message => p_return_message
2603 );
2604
2605 IF p_return_code = 'E' THEN
2606 IF lv_action = 'V' THEN
2607 INSERT INTO jai_ap_etds_errors_t
2608 (batch_id, record_type, reference_id, error_message) VALUES
2609 ( ln_batch_id,'DD', v_line_number, p_return_message ) ;
2610 ELSE
2611 p_ret_code := jai_constants.request_error ;
2612 p_err_buf := p_return_message ;
2613 RETURN ;
2614 END IF ;
2615 END IF ;
2616
2617 lv_generate_headers := null ;
2618 IF p_action <> 'V' THEN
2619 IF p_action = 'F' THEN
2620 lv_generate_headers := 'N' ;
2621 ELSIF p_action = 'H' THEN
2622 lv_generate_headers := 'Y' ;
2623 END IF ;
2624
2625 jai_etcs_pkg.create_quart_party_dtl
2626 (
2627 p_line_number => v_line_number,
2628 p_record_type => v_record_type,
2629 p_batch_number => v_batch_number,
2630 p_dh_challan_recNo => v_challan_dtl_slno,
2631 p_party_slno => v_deductee_slno,
2632 p_dh_mode => v_dh_mode,
2633 p_emp_serial_no => v_emp_serial_no,
2634 p_party_code => v_quart_deductee_code,
2635 p_last_emp_pan => v_last_emp_pan,
2636 p_party_pan => dd.party_pan,
2637 p_last_emp_pan_refno => v_last_emp_pan_refno,
2638 p_party_pan_refno => v_party_pan_ref_no,
2639 p_party_name => v_party_name,
2640 p_party_tcs_income_tax => dd.tcs_amt ,
2641 p_party_tcs_surcharge => dd.surcharge_amt,
2642 p_party_tcs_cess => dd.cess_amt,
2643 p_party_total_tax_deducted => v_deductee_total_tax_deducted,
2644 p_last_total_tax_deducted => v_last_total_tax_deducted,
2645 p_party_total_tax_deposit => v_deductee_total_tax_deducted,
2646 p_last_total_tax_deposit => v_last_total_tax_deposit,
2647 p_total_purchase => dd.tcs_amt,
2648 p_base_taxabale_amount => dd.tcs_amt,
2649 p_gl_date => dd.challan_date ,
2650 p_tcs_invoice_date => dd.challan_date,
2651 p_deposit_date => v_deposit_date,
2652 p_tcs_tax_rate => dd.tcs_tax_rate,
2653 p_grossingUp_ind => v_grossingUp_ind,
2654 p_book_ent_oth => v_quart_book_ent_oth,
2655 p_certificate_issue_date => v_certificate_issue_date,
2656 p_remarks1 => v_reason_for_nDeduction,
2657 p_remarks2 => v_remarks2,
2658 p_remarks3 => v_remarks3,
2659 p_dh_recHash => v_dh_recHash,
2660 p_generate_headers => lv_generate_headers
2661 );
2662
2663 END IF ;
2664
2665 END IF ;
2666
2667 UPDATE jai_ar_etcs_t
2668 SET deductee_line_num = v_line_number
2669 WHERE batch_id = ln_batch_id
2670 and challan_line_num = dd.challan_line_num
2671 and party_id = dd.party_id
2672 and party_site_id = dd.party_site_id
2673 and exempted_flag = dd.exempted_flag
2674 and NVL(bank_branch_code,'No Bank Branch') = NVL(dd.bank_branch_code,'No Bank Branch')
2675 and NVL(challan_no,'No Challan Number') = NVL(dd.challan_no,'No Challan Number')
2676 and NVL(challan_date,lv_dummy_date) = NVL(dd.challan_date,lv_dummy_date)
2677 and check_number = dd.check_number
2678 and tcs_tax_rate = dd.tcs_tax_rate
2679 and source_document_id = dd.source_document_id ;
2680
2681 END LOOP;
2682
2683 CLOSE c_deductee_records ;
2684 END process_deductee_records;
2685
2686 BEGIN
2687 lv_dummy_date := TO_DATE('01/01/1600', 'DD/MM/RRRR');
2688
2689 v_conc_request_id := FND_PROFILE.value('CONC_REQUEST_ID');
2690 SELECT JAI_AP_ETDS_T_S.nextval INTO ln_batch_id FROM DUAL;
2691 v_line_number := 0;
2692
2693
2694 IF NVL(p_period,'XX') = 'XX' THEN
2695 lv_etcs_yearly_returns := 'Y' ;
2696 FND_FILE.put_line(FND_FILE.log, '~~~~Ver:115.0~~~~ Start of eTCS File Creation for Yearly Returns
2697 Batch_id->'||ln_batch_id ||', Creation Date->'||to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss')||' ~~~~~~~~~~~~~~~~~~');
2698 ELSE
2699 lv_etcs_yearly_returns := 'N' ;
2700 FND_FILE.put_line(FND_FILE.log, '~~~~Ver:115.0~~~~ Start of eTCS File Creation for Quarterly returns
2701 Batch_id->'||ln_batch_id || 'Period : ' || p_period ||', Creation Date->' ||
2702 to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss')||' ~~~~~~~~~~~~~~~~~~');
2703 END IF ;
2704
2705 IF NVL(p_action,'X') <> 'V' THEN
2706 IF NVL(p_generate_headers,'X') = 'Y' or NVL(p_action,'X') = 'H' THEN
2707 jai_ap_tds_etds_pkg.v_debug_pad_char := ' ';
2708 jai_ap_tds_etds_pkg.v_generate_headers := TRUE;
2709 ELSE
2710 jai_ap_tds_etds_pkg.v_debug_pad_char := '';
2711 jai_ap_tds_etds_pkg.v_generate_headers := FALSE;
2712 END IF;
2713 END IF ;
2714
2715 IF length(p_tan_number) > 10 THEN
2716 FND_FILE.put_line(FND_FILE.log, 'Tan Number length is greater than 10 characters');
2717 RAISE_APPLICATION_ERROR(-20014, 'Tan Number length is greater than 10 characters', true);
2718 END IF;
2719
2720
2721 -- Date 26/05/2007 by sacsethi for bug 6153881
2722 -- Mark Legal_Entity_id as null
2723
2724 INSERT INTO JAI_AP_ETDS_REQUESTS(
2725 batch_id, request_id, legal_entity_id , org_tan_number, financial_year,
2726 tax_authority_id, tax_authority_site_id, organization_id,
2727 deductor_name, deductor_state, addr_changed_since_last_ret, deductor_status,
2728 person_resp_for_deduction, designation_of_pers_resp, challan_start_date,
2729 challan_end_date, file_path, filename ,
2730 created_by ,creation_date , last_updated_by , last_update_date , last_update_login -- Date 28/06/2007 by sacsethi for bug 6157120
2731 ) VALUES (
2732 ln_batch_id, v_conc_request_id, null , p_tan_number, p_fin_year,
2733 p_tax_authority_id, p_tax_authority_site_id, p_organization_id,
2734 p_seller_name, p_seller_state, p_addrChangedSinceLastRet, p_collector_status,
2735 p_persRespForCollection, p_desgOfPersResponsible, p_Start_Date,
2736 p_End_Date, p_file_path, p_filename ,
2737 fnd_global.user_id , sysdate , fnd_global.user_id , sysdate ,fnd_global.login_id -- Date 28/06/2007 by sacsethi for bug 6157120
2738 );
2739
2740 -- Fetching the Pan Number based on TAN
2741 OPEN c_pan_number(p_organization_id);
2742 FETCH c_pan_number INTO v_deductor_pan;
2743 CLOSE c_pan_number;
2744
2745 IF v_deductor_pan IS NULL THEN
2746 FND_FILE.put_line(FND_FILE.log, 'Pan Number cannot be retreived based on given TAN Number');
2747 RAISE_APPLICATION_ERROR(-20015, 'Pan Number cannot be retreived based on given TAN Number', true);
2748 END IF;
2749
2750 -- Fetching Start Date and End date of given Financial Year
2751 OPEN c_fin_year(p_tan_number, p_fin_year);
2752 FETCH c_fin_year INTO v_start_date, v_end_date;
2753 CLOSE c_fin_year;
2754
2755 IF v_start_date IS NULL OR v_end_date IS NULL THEN
2756 FND_FILE.put_line(FND_FILE.log, 'Cannot get values for Financial Year and Assessment Year');
2757 RAISE_APPLICATION_ERROR( -20016, 'Cannot get values for Financial Year and Assessment Year');
2758 END IF;
2759
2760 -- Fetching Location linked to Input Organization from where address details are captured
2761 OPEN c_location_linked_to_org(p_organization_id);
2762 FETCH c_location_linked_to_org INTO v_location_id;
2763 CLOSE c_location_linked_to_org;
2764
2765 -- Shall Populate the Address Details of Batch Header
2766 OPEN c_address_details(v_location_id);
2767 FETCH c_address_details INTO v_location_code, v_tan_address1, v_tan_address2, v_tan_address3, v_tan_address4,
2768 v_tan_address5, v_postal_code;
2769 CLOSE c_address_details;
2770
2771 -- checks for Pincode related to Address location
2772 IF length(v_postal_code) > 6 THEN
2773 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||')');
2774 END IF;
2775
2776 BEGIN
2777 v_tan_pin := to_number(v_postal_code);
2778 EXCEPTION
2779 WHEN VALUE_ERROR THEN
2780 RAISE_APPLICATION_ERROR(-20010, 'Postal Code of Location should be a 6 digit number. Location Code (id):'||v_location_code||' ('||v_location_id||')');
2781 END;
2782
2783 populate_details
2784 (
2785 p_batch_id => ln_batch_id ,
2786 p_org_tan_num => p_tan_number ,
2787 p_tax_authority_id => p_tax_authority_id ,
2788 p_tax_authority_site_id => p_tax_authority_site_id ,
2789 p_from_date => p_Start_Date ,
2790 p_to_date => p_end_Date ,
2791 p_collection_code => p_collection_code
2792 );
2793
2794
2795 BEGIN
2796 jai_etcs_pkg.openFile(p_file_path, p_filename);
2797 EXCEPTION
2798 WHEN OTHERS THEN
2799 FND_FILE.put_line(FND_FILE.log, 'Error Occured during opening of file(1):'||SQLERRM);
2800 RAISE_APPLICATION_ERROR(-20016, 'Error Occured(1):'||SQLERRM, true);
2801 END;
2802
2803 IF p_action <> 'V' THEN
2804 FND_FILE.put_line(FND_FILE.log, 'Start File Header');
2805 END IF ;
2806
2807 -- File Header (42 Chars)
2808 v_line_number := v_line_number + 1;
2809 v_record_type := 'FH';
2810 v_file_type := 'NS3' ;
2811 v_upload_type := 'R' ;
2812 v_file_sequence_number := 1;
2813 v_seller_tan := p_tan_number;
2814 v_file_creation_date := sysdate;
2815 v_number_of_batches := 1;
2816 v_quartfile_type :='TC1';
2817 v_uploader_type :='D';
2818
2819 IF lv_etcs_yearly_returns = 'Y' THEN
2820 IF p_generate_headers = 'Y' THEN
2821 jai_etcs_pkg.create_fh(ln_batch_id);
2822 END IF;
2823
2824 create_file_header
2825 (
2826 p_line_number => v_line_number,
2827 p_record_type => v_record_type,
2828 p_file_type => v_file_type,
2829 p_upload_type => v_upload_type,
2830 p_file_creation_date => v_file_creation_date,
2831 p_file_sequence_number => v_file_sequence_number,
2832 p_deductor_tan => v_seller_tan,
2833 p_number_of_batches => v_number_of_batches
2834 );
2835
2836 ELSE
2837 IF p_action = 'H' THEN
2838 jai_etcs_pkg.create_quarterly_fh(ln_batch_id, p_period,p_RespPersAddress, p_RespPersState, p_RespPersPin, p_RespPersAddrChange );
2839 END IF;
2840
2841 p_return_code := null ;
2842 p_return_message := null ;
2843
2844 jai_etcs_pkg.validate_file_header
2845 ( p_line_number => v_line_number ,
2846 p_record_type => v_record_type ,
2847 p_quartfile_type => v_quartfile_type,
2848 p_upload_type => v_upload_type,
2849 p_file_creation_date => v_file_creation_date,
2850 p_file_sequence_number => v_file_sequence_number,
2851 p_uploader_type => v_uploader_type ,
2852 p_collector_tan => v_seller_tan ,
2853 p_number_of_batches => v_number_of_batches,
2854 p_period => p_period,
2855 p_start_date => p_start_date,
2856 p_end_date => p_end_date,
2857 p_fin_year => to_char(v_start_date,'YYYY'),
2858 p_return_code => p_return_code,
2859 p_return_message => p_return_message
2860 );
2861
2862 IF p_return_code = 'E' THEN
2863 IF lv_action = 'V' THEN
2864 INSERT INTO jai_ap_etds_errors_t
2865 (batch_id, record_type, error_message) values
2866 ( ln_batch_id, 'FH', p_return_message ) ;
2867 ELSE
2868 p_ret_code := jai_constants.request_error ;
2869 p_err_buf := p_return_message ;
2870 RETURN ;
2871 END IF ;
2872 END IF ;
2873
2874 lv_generate_headers := null ;
2875 IF p_action <> 'V' THEN
2876 IF p_action = 'F' THEN
2877 lv_generate_headers := 'N' ;
2878 ELSIF p_action = 'H' THEN
2879 lv_generate_headers := 'Y' ;
2880 END IF ;
2881
2882 jai_etcs_pkg.create_quarterly_file_header
2883 (
2884 p_line_number => v_line_number,
2885 p_record_type => v_record_type,
2886 p_file_type => v_quartfile_type,
2887 p_upload_type => v_upload_type,
2888 p_file_creation_date => v_file_creation_date,
2889 p_file_sequence_number => v_file_sequence_number,
2890 p_uploader_type => v_uploader_type,
2891 p_collector_tan => v_seller_tan,
2892 p_number_of_batches => v_number_of_batches,
2893 p_fh_recordHash => v_fh_recordHash,
2894 p_fh_fvuVersion => v_fh_fvuVersion,
2895 p_fh_fileHash => v_fh_fileHash,
2896 p_fh_samVersion => v_fh_samVersion,
2897 p_fh_samHash => v_fh_samHash,
2898 p_fh_scmVersion => v_fh_scmVersion,
2899 p_fh_scmHash => v_fh_scmHash,
2900 p_generate_headers => lv_generate_headers
2901 ) ;
2902
2903 END IF ;
2904
2905 END IF ;
2906
2907 -- Batch Header (411 Chars)
2908 v_line_number := v_line_number + 1;
2909 v_record_type := 'BH';
2910 v_batch_number := 1;
2911 v_form_number := '27E ';
2912 v_financial_year := to_char(v_start_date, 'YYYY')||to_char(v_end_date, 'YY');
2913 v_assessment_year := to_char(add_months(v_start_date,12), 'YYYY')||to_char(add_months(v_end_date,12), 'YY');
2914 v_seller_name := p_seller_name;
2915 v_addrChangedSinceLastReturn := p_addrChangedSinceLastRet;
2916 v_seller_status := 'O';
2917 v_quarterlyOrYearly := 'Y';
2918 v_personNameRespForDedection := p_persRespForCollection;
2919 v_personDesgnRespForDedection := p_desgOfPersResponsible;
2920 v_tan_state_code := to_number(p_seller_state);
2921 v_ao_approval :='N';
2922 v_quart_form_number :='27EQ';
2923
2924
2925 open c_deductee_cnt;
2926 fetch c_deductee_cnt into v_deductee_cnt, v_totTaxDeductedAsPerDeductee ;
2927 close c_deductee_cnt ;
2928
2929 open c_challan_cnt ;
2930 fetch c_challan_cnt into v_challan_cnt ;
2931 close c_challan_cnt ;
2932
2933 v_totTaxDeductedAsPerChallan := v_totTaxDeductedAsPerDeductee ;
2934
2935 IF p_action <> 'V' THEN
2936 FND_FILE.put_line(FND_FILE.log, 'Batch Header');
2937 END IF ;
2938
2939 v_ack_num_tan_app := NULL;
2940 v_pro_rcpt_num_org_ret := nvl(p_pro_rcpt_num_org_ret,0);
2941
2942 IF lv_etcs_yearly_returns = 'Y' THEN
2943
2944 IF p_generate_headers = 'Y' THEN
2945 jai_ap_tds_etds_pkg.create_bh;
2946 END IF;
2947
2948 jai_ap_tds_etds_pkg.create_batch_header(
2949 p_line_number => v_line_number,
2950 p_record_type => v_record_type,
2951 p_batch_number => v_batch_number,
2952 p_challan_count => v_challan_cnt,
2953 p_deductee_count => v_deductee_cnt,
2954 p_form_number => v_form_number,
2955 p_filler1 => v_filler1,
2956 p_deductor_tan => v_seller_tan,
2957 p_pan_of_tan => v_deductor_pan,
2958 p_assessment_year => v_assessment_year,
2959 p_financial_year => v_financial_year,
2960 p_deductor_name => v_seller_name,
2961 p_tan_address1 => v_tan_address1,
2962 p_tan_address2 => v_tan_address2,
2963 p_tan_address3 => v_tan_address3,
2964 p_tan_address4 => v_tan_address4,
2965 p_tan_address5 => v_tan_address5,
2966 p_tan_state => v_tan_state_code,
2967 p_tan_pin => v_tan_pin,
2968 p_chng_addr_since_last_return => v_addrChangedSinceLastReturn,
2969 p_status_of_deductor => v_seller_status,
2970 p_quart_year_return => v_quarterlyOrYearly,
2971 p_pers_resp_for_deduction => v_personNameRespForDedection,
2972 p_pers_designation => v_personDesgnRespForDedection,
2973 p_tot_tax_dedected_challan => v_totTaxDeductedAsPerChallan,
2974 p_tot_tax_dedected_deductee => v_totTaxDeductedAsPerDeductee,
2975 p_filler2 => v_filler2,
2976 p_filler3 => v_filler3,
2977 p_ack_num_tan_app => v_ack_num_tan_app,
2978 p_pro_rcpt_num_org_ret => v_pro_rcpt_num_org_ret,
2979 p_rrr_number => v_bh_RRRno,
2980 p_rrr_date => v_bh_RRRdate
2981 );
2982
2983 ELSE
2984 IF p_action = 'H' THEN
2985 jai_etcs_pkg.create_quarterly_bh;
2986 END IF;
2987
2988 p_return_code := null ;
2989 p_return_message := null ;
2990
2991 validate_batch_header
2992 ( p_line_number => v_line_number ,
2993 p_record_type => v_record_type ,
2994 p_batch_number => v_batch_number ,
2995 p_challan_cnt => v_challan_cnt ,
2996 p_quart_form_number => v_quart_form_number ,
2997 p_collector_tan => v_seller_tan ,
2998 p_assessment_year => v_assessment_year,
2999 p_financial_year => v_financial_year ,
3000 p_collector_name => v_seller_name ,
3001 p_tan_address1 => v_tan_address1 ,
3002 p_tan_state_code => v_tan_state_code ,
3003 p_tan_pin => v_tan_pin ,
3004 p_collector_status => p_collector_status ,
3005 p_addrChangedSinceLastReturn => v_addrChangedSinceLastReturn,
3006 p_personNameRespForCollection => v_personNameRespForDedection,
3007 p_personDesgnRespForCollection => v_personDesgnRespForDedection,
3008 p_RespPersAddress => p_RespPersAddress ,
3009 p_RespPersState => p_RespPersState ,
3010 p_RespPersPin => p_RespPersPin ,
3011 p_RespPersAddrChange => p_RespPersAddrChange ,
3012 p_totTaxCollectedAsPerParty => v_totTaxDeductedAsPerDeductee,
3013 p_ao_approval => v_ao_approval,
3014 p_return_code => p_return_code,
3015 p_return_message => p_return_message
3016 );
3017
3018
3019 IF p_return_code = 'E' THEN
3020 IF lv_action = 'V' THEN
3021 insert into jai_ap_etds_errors_t(batch_id, record_type, error_message) values
3022 ( ln_batch_id, 'BH', p_return_message ) ;
3023 ELSE
3024 p_ret_code := jai_constants.request_error ;
3025 p_err_buf := p_return_message ;
3026 RETURN ;
3027 END IF ;
3028 END IF ;
3029
3030
3031 lv_generate_headers := null ;
3032 IF p_action <> 'V' THEN
3033 IF p_action = 'F' THEN
3034 lv_generate_headers := 'N' ;
3035 ELSIF p_action = 'H' THEN
3036 lv_generate_headers := 'Y' ;
3037 END IF ;
3038
3039 jai_etcs_pkg.create_quarterly_batch_header
3040 (
3041 p_line_number => v_line_number,
3042 p_record_type => v_record_type,
3043 p_batch_number => v_batch_number,
3044 p_challan_count => v_challan_cnt,
3045 p_form_number => v_quart_form_number,
3046 p_trn_type => v_bh_trnType,
3047 p_batchUpd => v_bh_batchUpd,
3048 p_org_RRRno => v_bh_org_RRRno,
3049 p_prev_RRRno => v_bh_prev_RRRno,
3050 p_RRRno => v_bh_RRRno,
3051 p_RRRdate => v_bh_RRRdate,
3052 p_collector_last_tan => v_bh_deductor_last_tan,
3053 p_collector_tan => v_seller_tan,
3054 p_filler1 => v_filler1,
3055 p_collector_pan => v_deductor_pan,
3056 p_assessment_year => v_assessment_year,
3057 p_financial_year => v_financial_year,
3058 p_period => p_period,
3059 p_collector_name => v_seller_name,
3060 p_collector_branch => v_deductor_branch,
3061 p_tan_address1 => v_tan_address1,
3062 p_tan_address2 => v_tan_address2,
3063 p_tan_address3 => v_tan_address3,
3064 p_tan_address4 => v_tan_address4,
3065 p_tan_address5 => v_tan_address5,
3066 p_tan_state_code => v_tan_state_code,
3067 p_tan_pin => v_tan_pin,
3068 p_collector_email => v_deductor_email,
3069 p_collector_stdCode => v_deductor_stdCode,
3070 p_collector_phoneNo => v_deductor_phoneNo,
3071 p_addrChangedSinceLastReturn => v_addrChangedSinceLastReturn,
3072 p_status_of_collector => v_seller_status,
3073 p_pers_resp_for_collection => v_personNameRespForDedection,
3074 p_RespPerson_designation => v_personDesgnRespForDedection,
3075 p_RespPerson_address1 => p_RespPersAddress,
3076 p_RespPerson_address2 => v_RespPerson_address2,
3077 p_RespPerson_address3 => v_RespPerson_address3,
3078 p_RespPerson_address4 => v_RespPerson_address4,
3079 p_RespPerson_address5 => v_RespPerson_address5,
3080 p_RespPerson_state => p_RespPersState,
3081 p_RespPerson_pin => p_RespPersPin,
3082 p_RespPerson_email => v_RespPerson_email,
3083 p_RespPerson_remark => v_RespPerson_remark,
3084 p_RespPerson_stdCode => v_RespPerson_stdCode,
3085 p_RespPerson_phoneNo => v_RespPerson_phoneNo,
3086 p_RespPerson_addressChange => p_RespPersAddrChange,
3087 p_totTaxCollectedAsPerChallan => round(v_totTaxDeductedAsPerDeductee), -- decimal should be .00
3088 p_tds_circle => v_bh_tds_circle,
3089 p_salaryRecords_count => v_bh_salaryRecords_count,
3090 p_gross_total => v_bh_gross_total,
3091 p_ao_approval => v_ao_approval ,
3092 p_ao_approval_number => v_ao_approval_number,
3093 p_recHash => v_bh_recHash,
3094 p_generate_headers => lv_generate_headers
3095 ) ;
3096 END IF ;
3097 END IF ;
3098
3099 IF p_action <> 'V' THEN
3100 FND_FILE.put_line(FND_FILE.log, 'Challan Detail');
3101 END IF ;
3102
3103 v_record_type := 'CD';
3104
3105 IF lv_etcs_yearly_returns = 'Y' THEN
3106 IF p_generate_headers = 'Y' THEN
3107 create_cd;
3108 END IF ;
3109 END IF;
3110
3111 v_challan_dtl_slno := 0;
3112
3113 OPEN c_challan_records(ln_batch_id) ;
3114 LOOP
3115 FETCH c_challan_records INTO cd ;
3116 EXIT WHEN
3117 c_challan_records%NOTFOUND ;
3118
3119 v_line_number := v_line_number + 1;
3120 v_challan_dtl_slno := v_challan_dtl_slno + 1;
3121 ln_amt_of_oth := 0;
3122 v_record_type := 'CD';
3123 v_tcs_section := '206C' ;
3124 v_collection_code := p_collection_code ;
3125
3126 IF cd.challan_date = lv_dummy_date THEN
3127 cd.challan_date := to_date(null) ;
3128 END IF ;
3129
3130 IF lv_etcs_yearly_returns = 'Y' THEN
3131
3132 jai_ap_tds_etds_pkg.create_challan_detail
3133 (
3134 p_line_number => v_line_number,
3135 p_record_type => v_record_type,
3136 p_batch_number => v_batch_number,
3137 p_challan_slno => v_challan_dtl_slno,
3138 p_challan_section => v_tcs_section,
3139 p_amount_of_tds => cd.tcs_amt,
3140 p_amount_of_surcharge => cd.surcharge_amt,
3141 p_amount_of_cess => cd.cess_amt,
3142 p_amount_of_int => ln_amt_of_oth,
3143 p_amount_of_oth => ln_amt_of_oth,
3144 p_amount_deducted => cd.total_tcs_amount,
3145 p_challan_num => cd.challan_no,
3146 p_challan_date => cd.challan_date,
3147 p_bank_branch_code => cd.bank_branch_code,
3148 p_check_number => cd.check_number,
3149 p_tds_dep_by_book => 'N' ,
3150 p_filler4 => v_collection_code
3151 ) ;
3152
3153 ELSE
3154
3155 IF cd.challan_no = 'No Challan Number' THEN
3156 cd.challan_no := null ;
3157 END IF ;
3158
3159 OPEN c_quart_deductee_cnt(ln_batch_id, cd.check_number) ;
3160 FETCH c_quart_deductee_cnt INTO v_q_deductee_cnt;
3161 CLOSE c_quart_deductee_cnt ;
3162
3163 open c_book_entry(cd.tcs_check_id) ;
3164 fetch c_book_entry into v_book_entry ;
3165 close c_book_entry ;
3166
3167 v_total_deposit := cd.tcs_amt + cd.surcharge_amt + cd.cess_amt;
3168 v_nil_challan_indicator := 'N' ;
3169
3170 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);
3171
3172 FND_FILE.put_line(FND_FILE.log, 'create challan quarterly' );
3173
3174 IF p_action = 'H' THEN
3175 jai_etcs_pkg.create_quarterly_cd;
3176 END IF ;
3177
3178 IF cd.challan_no IS NULL THEN
3179 v_bank_branch_code := null ;
3180 ELSE
3181 v_bank_branch_code := substr(cd.bank_branch_code,1,7);
3182 END IF ;
3183
3184
3185 p_return_code := null ;
3186 p_return_message := null ;
3187
3188 FND_FILE.put_line(FND_FILE.log, 'Validate Challan Detail' );
3189
3190 validate_challan_detail
3191 (
3192 p_line_number => v_line_number ,
3193 p_record_type => v_record_type ,
3194 p_batch_number => v_batch_number ,
3195 p_challan_dtl_slno => v_challan_dtl_slno ,
3196 p_party_cnt => v_q_deductee_cnt ,
3197 p_nil_challan_indicat => v_nil_challan_indicator,
3198 p_tcs_section => v_collection_code,
3199 p_tcs_amt => cd.tcs_amt ,
3200 p_surcharge_amt => cd.surcharge_amt ,
3201 p_cess_amt => cd.cess_amt ,
3202 p_amt_of_oth => ln_amt_of_oth ,
3203 p_tcs_amount => cd.total_tcs_amount ,
3204 p_total_income_tcs => v_total_deposit ,
3205 p_challan_no => cd.challan_no,
3206 p_bank_branch_code => cd.bank_branch_code,
3207 p_challan_Date => cd.challan_date,
3208 p_check_number => cd.check_number,
3209 p_amt_of_int => round(ln_amt_of_oth),
3210 p_total_deposit => v_total_deposit,
3211 p_tcs_income_tax => cd.tcs_amt,
3212 p_tcs_surcharge => cd.surcharge_amt,
3213 p_tcs_cess => cd.cess_amt,
3214 p_tcs_interest_amt => 0,
3215 p_tcs_other_amt => 0,
3216 p_return_code => p_return_code,
3217 p_return_message => p_return_message
3218 );
3219
3220
3221 IF p_return_code = 'E' THEN
3222 IF lv_action = 'V' THEN
3223 insert into jai_ap_etds_errors_t
3224 (batch_id, record_type, reference_id, error_message) values
3225 ( ln_batch_id, 'CD', v_line_number, p_return_message ) ;
3226 ELSE
3227 p_ret_code := jai_constants.request_error ;
3228 p_err_buf := p_return_message ;
3229 RETURN ;
3230 END IF ;
3231 END IF ;
3232
3233
3234 lv_generate_headers := null ;
3235 IF p_action <> 'V' THEN
3236 IF p_action = 'F' THEN
3237 lv_generate_headers := 'N' ;
3238 ELSIF p_action = 'H' THEN
3239 lv_generate_headers := 'Y' ;
3240 END IF ;
3241
3242 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) ;
3243
3244 create_quart_challan_dtl
3245 (
3246 p_line_number => v_line_number,
3247 p_record_type => v_record_type,
3248 p_batch_number => v_batch_number,
3249 p_challan_dtl_slno => v_challan_dtl_slno,
3250 p_collection_cnt => v_q_deductee_cnt,
3251 p_nil_challan_indicator => v_nil_challan_indicator,
3252 p_ch_updIndicator => v_ch_updIndicator,
3253 p_filler2 => v_filler2,
3254 p_filler3 => v_filler3,
3255 p_filler4 => v_filler4,
3256 p_last_bank_challan_no => v_last_bank_challan_no,
3257 p_bank_challan_no => cd.challan_no,
3258 p_last_transfer_voucher_no => v_last_transfer_voucher_no,
3259 p_transfer_voucher_no => v_transfer_voucher_no,
3260 p_last_bank_branch_code => v_last_bank_branch_code,
3261 p_bank_branch_code => v_bank_branch_code ,
3262 p_challan_lastDate => v_challan_lastDate,
3263 p_challan_Date => cd.challan_date,
3264 p_filler5 => v_filler5,
3265 p_filler6 => v_filler6,
3266 p_tcs_section => v_collection_code,
3267 p_tcs_amt => ln_amt_of_tds,
3268 p_surcharge_amt => round(cd.surcharge_amt),
3269 p_cess_amt => round(cd.cess_amt),
3270 p_amt_of_int => round(ln_amt_of_oth),
3271 p_amt_of_oth => round(ln_amt_of_oth),
3272 p_tcs_amount => cd.total_tcs_amount,
3273 p_last_total_depositAmt => v_last_total_depositAmt,
3274 p_total_deposit => v_total_deposit,
3275 p_tcs_income_tax => cd.tcs_amt,
3276 p_tcs_surcharge => cd.surcharge_amt,
3277 p_tcs_cess => cd.cess_amt,
3278 p_total_income_tcs => v_total_deposit,
3279 p_tcs_interest_amt => 0,
3280 p_tcs_other_amt => 0,
3281 p_check_number => cd.check_number,
3282 p_book_entry => v_book_entry,
3283 p_remarks => v_remarks,
3284 p_ch_recHash => v_ch_recHash,
3285 p_generate_headers => lv_generate_headers
3286 ) ;
3287 END IF ;
3288 END IF ;
3289
3290 UPDATE jai_ar_etcs_t
3291 SET challan_line_num = v_line_number
3292 WHERE batch_id = ln_batch_id
3293 and nvl(challan_no,'No Challan Number') = nvl(cd.challan_no, 'No Challan Number')
3294 and nvl(challan_date, lv_dummy_date) = nvl(cd.challan_date, lv_dummy_date )
3295 and nvl(bank_branch_code, 'No Bank Branch') = nvl(cd.bank_branch_code, 'No Bank Branch')
3296 and check_number = cd.check_number;
3297
3298 IF p_action <> 'V' THEN
3299 FND_FILE.put_line(FND_FILE.log, 'Challan Line:'||v_line_number
3300 || ', ChlNum:' || cd.challan_no ||', ChlDate:'||cd.challan_date||', bankBr:'||cd.bank_branch_code
3301 );
3302 END IF ;
3303
3304 IF lv_etcs_yearly_returns= 'N' THEN
3305 v_record_type := 'DD';
3306 v_challan_line_num := v_line_number ;
3307
3308 IF p_action = 'H' THEN
3309 create_quarterly_dd;
3310 END IF;
3311
3312 v_deductee_slno := 0 ;
3313 process_deductee_records ;
3314 v_challan_line_num := null ;
3315 END IF;
3316
3317 END LOOP;
3318
3319 CLOSE c_challan_records ;
3320
3321 IF lv_etcs_yearly_returns= 'Y' THEN
3322
3323 v_record_type := 'DD';
3324
3325 IF p_action <> 'V' THEN
3326 FND_FILE.put_line(FND_FILE.log, 'Deductee Detail');
3327 END IF ;
3328
3329 IF p_generate_headers = 'Y' THEN
3330 create_dd;
3331 END IF;
3332
3333 v_challan_line_num := null ;
3334 v_deductee_slno := 0 ;
3335 process_deductee_records ; -- internal procedure call
3336
3337 END IF ;
3338
3339 IF p_action = 'V' THEN
3340
3341 FND_FILE.put_line(FND_FILE.log,' LISTING THE ERRORS IN THIS BATCH ' );
3342 FND_FILE.put_line(FND_FILE.log,'-------------------------------------------------------------------- ' );
3343
3344 ln_errors_exist := 0;
3345
3346 FOR rec_get_errors IN c_get_errors(ln_batch_id)
3347 LOOP
3348 ln_errors_exist := 1 ;
3349 FND_FILE.put_line(FND_FILE.log, rec_get_errors.Error_Message );
3350 END LOOP ;
3351
3352 IF ln_errors_exist = 0 THEN
3353 FND_FILE.put_line(FND_FILE.log,' File Validation Successful. No Errors Found !! ' );
3354 END IF ;
3355
3356 FND_FILE.put_line(FND_FILE.log,'-------------------------------------------------------------------- ' );
3357 FND_FILE.put_line(FND_FILE.log,' END OF ERRORS IN THIS BATCH ' );
3358
3359 END IF ;
3360
3361 jai_etcs_pkg.closeFile;
3362
3363 IF p_action <> 'V' THEN
3364 FND_FILE.put_line(FND_FILE.log, '~~~~~~~~~~~~~~~ End of eTCS File Creation ~~~~~~~~~~~~~~~~~~');
3365 END IF ;
3366
3367 END generate_etcs_returns;
3368
3369 PROCEDURE yearly_returns
3370 (
3371 p_err_buf OUT NOCOPY VARCHAR2,
3372 p_ret_code OUT NOCOPY NUMBER,
3373 p_tan_number IN VARCHAR2,
3374 p_organization_id IN NUMBER,
3375 p_fin_year IN NUMBER,
3376 p_collection_code IN VARCHAR2,
3377 p_tax_authority_id IN NUMBER,
3378 p_tax_authority_site_id IN NUMBER,
3379 p_seller_name IN VARCHAR2,
3380 p_seller_state IN VARCHAR2,
3381 p_addrChangedSinceLastRet IN VARCHAR2,
3382 p_persRespForCollection IN VARCHAR2,
3383 p_desgOfPersResponsible IN VARCHAR2,
3384 p_start_date IN VARCHAR2,
3385 p_end_date IN VARCHAR2,
3386 p_pro_rcpt_num_org_ret IN NUMBER,
3387 p_file_path IN VARCHAR2,
3388 p_filename IN VARCHAR2,
3389 p_generate_headers IN VARCHAR2 DEFAULT 'N'
3390 )
3391 IS
3392 pv_start_date DATE DEFAULT fnd_date.canonical_to_date(p_start_date);
3393 pv_end_date DATE DEFAULT fnd_date.canonical_to_date(p_end_date);
3394
3395 BEGIN
3396
3397 FND_FILE.put_line( FND_FILE.log, 'Parameters : ' || fnd_global.local_chr(10)
3398 ||' org_tan_number ->'||p_tan_number||fnd_global.local_chr(10)
3399 ||' Organization_id ->'||p_organization_id || fnd_global.local_chr(10)
3400 ||' financial_year ->'||p_fin_year||fnd_global.local_chr(10)
3401 ||' Collection Code ->'||p_collection_code||fnd_global.local_chr(10)
3402 ||' tax_authority_id ->'||p_tax_authority_id||fnd_global.local_chr(10)
3403 ||' tax_authority_site_id ->'||p_tax_authority_site_id||fnd_global.local_chr(10)
3404 ||' seller ->'||p_seller_name||fnd_global.local_chr(10)
3405 ||' seller state ->'||p_seller_state||fnd_global.local_chr(10)
3406 ||' addr_changed_since_last_ret->'||p_addrChangedSinceLastRet||fnd_global.local_chr(10)
3407 ||' person_resp_for_collectio ->'||p_persRespForCollection||fnd_global.local_chr(10)
3408 ||' designation_of_pers_resp ->'||p_desgOfPersResponsible||fnd_global.local_chr(10)
3409 ||' Start_date ->'||p_start_date||fnd_global.local_chr(10)
3410 ||' End_date ->'||p_end_date||fnd_global.local_chr(10)
3411 ||' Provvisional Rcpt No ->'||p_pro_rcpt_num_org_ret || fnd_global.local_chr(10)
3412 ||' file_path ->'||p_file_path||fnd_global.local_chr(10)
3413 ||' filename ->'||p_filename||fnd_global.local_chr(10)
3414 ||' Generate_headers ->'||p_generate_headers||fnd_global.local_chr(10)
3415 ) ;
3416
3417
3418 generate_etcs_returns
3419 (
3420 p_err_buf => p_err_buf ,
3421 p_ret_code => p_ret_code ,
3422 p_tan_number => p_tan_number ,
3423 p_organization_id => p_organization_id ,
3424 p_fin_year => p_fin_year ,
3425 p_tax_authority_id => p_tax_authority_id ,
3426 p_tax_authority_site_id => p_tax_authority_site_id ,
3427 p_seller_name => p_seller_name ,
3428 p_seller_state => p_seller_state ,
3429 p_addrChangedSinceLastRet => p_addrChangedSinceLastRet ,
3430 p_persRespForCollection => p_persRespForCollection ,
3431 p_desgOfPersResponsible => p_desgOfPersResponsible ,
3432 p_Start_Date => pv_start_date ,
3433 p_End_Date => pv_end_date ,
3434 p_pro_rcpt_num_org_ret => p_pro_rcpt_num_org_ret ,
3435 p_file_path => p_file_path ,
3436 p_filename => p_filename ,
3437 p_collection_code => p_collection_code ,
3438 p_generate_headers => p_generate_headers
3439 ) ;
3440 END yearly_returns;
3441
3442 PROCEDURE quarterly_returns
3443 (
3444 p_err_buf OUT NOCOPY VARCHAR2,
3445 p_ret_code OUT NOCOPY NUMBER,
3446 p_tan_number IN VARCHAR2,
3447 p_organization_id IN NUMBER,
3448 p_fin_year IN NUMBER,
3449 p_period IN VARCHAR2 ,
3450 p_collection_code IN VARCHAR2,
3451 p_tax_authority_id IN NUMBER,
3452 p_tax_authority_site_id IN NUMBER,
3453 p_seller_name IN VARCHAR2,
3454 p_seller_state IN VARCHAR2,
3455 p_addrChangedSinceLastRet IN VARCHAR2,
3456 p_collector_status IN VARCHAR2,
3457 p_persRespForCollection IN VARCHAR2,
3458 p_desgOfPersResponsible IN VARCHAR2,
3459 p_RespPersAddress IN VARCHAR2 ,
3460 p_RespPersState IN VARCHAR2 ,
3461 p_RespPersPin IN VARCHAR2 ,
3462 p_RespPersAddrChange IN VARCHAR2,
3463 p_start_date IN VARCHAR2,
3464 p_end_date IN VARCHAR2,
3465 p_pro_rcpt_num_org_ret IN NUMBER,
3466 p_file_path IN VARCHAR2,
3467 p_filename IN VARCHAR2,
3468 p_action IN VARCHAR2
3469 )
3470 IS
3471 pv_start_date DATE DEFAULT fnd_date.canonical_to_date(p_start_date);
3472 pv_end_date DATE DEFAULT fnd_date.canonical_to_date(p_end_date);
3473
3474 BEGIN
3475
3476 FND_FILE.put_line( FND_FILE.log, 'Parameters : ' || fnd_global.local_chr(10)
3477 ||' org_tan_number ->'||p_tan_number||fnd_global.local_chr(10)
3478 ||' Organization_id ->'||p_organization_id || fnd_global.local_chr(10)
3479 ||' financial_year ->'||p_fin_year||fnd_global.local_chr(10)
3480 ||' Period ->'||p_period||fnd_global.local_chr(10)
3481 ||' Collection Code ->'||p_collection_code||fnd_global.local_chr(10)
3482 ||' tax_authority_id ->'||p_tax_authority_id||fnd_global.local_chr(10)
3483 ||' tax_authority_site_id ->'||p_tax_authority_site_id||fnd_global.local_chr(10)
3484 ||' seller ->'||p_seller_name||fnd_global.local_chr(10)
3485 ||' seller state ->'||p_seller_state||fnd_global.local_chr(10)
3486 ||' addr_changed_since_last_ret->'||p_addrChangedSinceLastRet||fnd_global.local_chr(10)
3487 ||' collector_status ->'||p_collector_status||fnd_global.local_chr(10)
3488 ||' person_resp_for_collectio ->'||p_persRespForCollection||fnd_global.local_chr(10)
3489 ||' designation_of_pers_resp ->'||p_desgOfPersResponsible||fnd_global.local_chr(10)
3490 ||' RespPerson''s Address ->'||p_RespPersAddress||fnd_global.local_chr(10)
3491 ||' RespPerson''s State ->'||p_RespPersState||fnd_global.local_chr(10)
3492 ||' RespPerson''s Pin ->'||p_RespPersPin||fnd_global.local_chr(10)
3493 ||' RespPerson''s Addr Changed ->'||p_RespPersAddrChange||fnd_global.local_chr(10)
3494 ||' Start_date ->'||p_start_date||fnd_global.local_chr(10)
3495 ||' End_date ->'||p_end_date||fnd_global.local_chr(10)
3496 ||' Provvisional Rcpt No ->'||p_pro_rcpt_num_org_ret || fnd_global.local_chr(10)
3497 ||' file_path ->'||p_file_path||fnd_global.local_chr(10)
3498 ||' filename ->'||p_filename||fnd_global.local_chr(10)
3499 ||' Action ->'||p_action||fnd_global.local_chr(10)
3500 ) ;
3501
3502
3503 generate_etcs_returns
3504 (
3505 p_err_buf => p_err_buf ,
3506 p_ret_code => p_ret_code ,
3507 p_organization_id => p_organization_id ,
3508 p_tan_number => p_tan_number ,
3509 p_fin_year => p_fin_year ,
3510 p_tax_authority_id => p_tax_authority_id ,
3511 p_tax_authority_site_id => p_tax_authority_site_id ,
3512 p_seller_name => p_seller_name ,
3513 p_seller_state => p_seller_state ,
3514 p_addrChangedSinceLastRet => p_addrChangedSinceLastRet ,
3515 p_persRespForCollection => p_persRespForCollection ,
3516 p_desgOfPersResponsible => p_desgOfPersResponsible ,
3517 p_Start_Date => pv_start_date ,
3518 p_End_Date => pv_end_date ,
3519 p_pro_rcpt_num_org_ret => p_pro_rcpt_num_org_ret ,
3520 p_file_path => p_file_path ,
3521 p_filename => p_filename ,
3522 p_collection_code => p_collection_code ,
3523 p_period => p_period ,
3524 p_RespPersAddress => p_RespPersAddress ,
3525 p_RespPersState => p_RespPersState ,
3526 p_RespPersPin => p_RespPersPin ,
3527 p_RespPersAddrChange => p_RespPersAddrChange ,
3528 p_action => p_action ,
3529 p_collector_status => p_collector_status
3530 ) ;
3531 END quarterly_returns;
3532
3533
3534 END jai_etcs_pkg;