[Home] [Help]
PACKAGE BODY: APPS.JAI_AR_EER1_PKG
Source
1 PACKAGE BODY jai_ar_eer1_pkg AS
2 /*$Header: jai_ar_eer1_pkg.plb 120.2.12010000.2 2008/11/19 11:41:35 mbremkum ship $*/
3 /*------------------------------------------------------------------------------------------
4 FILENAME: jai_ar_eer1_pkg.plb
5 CHANGE HISTORY:
6
7 Sl. YYYY/MM/DD Author and Details
8 ------------------------------------------------------------------------------------------
9 1 2007/01/04 CSahoo BUG#5745509 . File Version 120.2
10 Forward porting of BUG#5637136. The table and view names were changed.
11 ---------------------------------------------------------------------------------------------*/
12
13
14 lv_ecc varchar2(15);
15
16 PROCEDURE generate_flat_file
17 (
18 p_err_buf OUT NOCOPY VARCHAR2,
19 p_ret_code OUT NOCOPY NUMBER,
20 p_organization_id IN NUMBER,
21 p_location_id IN NUMBER,
22 pv_start_date IN VARCHAR2,
23 pv_end_date IN VARCHAR2,
24 p_registration_number IN VARCHAR2,
25 p_operating_unit IN NUMBER,
26 p_auth_sign IN VARCHAR2,
27 p_place IN VARCHAR2,
28 p_file_path IN VARCHAR2,
29 p_filename IN VARCHAR2,
30 p_action_flg IN VARCHAR2 DEFAULT 'N'
31 )
32 IS
33 p_start_date DATE ;
34 p_end_date DATE ;
35
36 CURSOR cur_ecc(p_organization_id IN NUMBER, p_location_id IN NUMBER)
37 IS
38 SELECT substr(ec_code,1,15) FROM JAI_CMN_INVENTORY_ORGS
39 WHERE organization_id = p_organization_id
40 AND location_id = p_location_id;
41
42 BEGIN
43
44 p_start_date := fnd_date.canonical_to_date(pv_start_date);
45 p_end_date := fnd_date.canonical_to_date(pv_end_date);
46
47 jai_ar_eer1_pkg.p_action := p_action_flg;
48 openFile(p_file_path,p_filename);
49
50 OPEN cur_ecc( p_organization_id, p_location_id);
51 FETCH cur_ecc into lv_ecc;
52 CLOSE cur_ecc; -- for registration number
53
54 IF p_action_flg = 'Y' THEN
55 FND_FILE.put_line(FND_FILE.log, 'Cetsh Header') ;
56 create_ceth_header;
57 END IF;
58
59 FND_FILE.put_line(FND_FILE.log, 'Cetsh Dtl') ;
60
61 populate_ceth_wise_details(
62 p_organization_id => p_organization_id,
63 p_location_id => p_location_id,
64 p_start_date => p_start_date,
65 p_end_date => p_end_date
66 );
67
68 IF p_action_flg = 'Y' THEN
69 FND_FILE.put_line(FND_FILE.log, 'Duty Header') ;
70 create_duty_header;
71 END IF;
72
73 FND_FILE.put_line(FND_FILE.log, 'Duty Detail') ;
74 populate_duty_paid_details(
75 p_end_date => p_end_date,
76 p_location_id => p_location_id,
77 p_organization_id => p_organization_id,
78 p_start_date => p_start_date);
79
80
81 IF p_action_flg = 'Y' THEN
82 FND_FILE.put_line(FND_FILE.log, 'Input Header') ;
83 create_input_header;
84 END IF;
85
86 FND_FILE.put_line(FND_FILE.log, 'Input Detail') ;
87 populate_input_details
88 ( p_end_date => p_end_date,
89 p_location_id => p_location_id,
90 p_organization_id => p_organization_id,
91 p_start_date => p_start_date);
92
93 IF p_action_flg = 'Y' THEN
94 FND_FILE.put_line(FND_FILE.log, 'Cenvat Header') ;
98 FND_FILE.put_line(FND_FILE.log, 'Cenavt Detail') ;
95 create_cenvat_header;
96 END IF;
97
99 populate_cenvat_credit_details (
100 p_end_date => p_end_date,
101 p_location_id => p_location_id,
102 p_operating_unit => p_operating_unit,
103 p_organization_id => p_organization_id,
104 p_registration_number => p_registration_number,
105 p_start_date => p_start_date);
106
107
108 IF p_action_flg = 'Y' THEN
109 FND_FILE.put_line(FND_FILE.log, 'Payment Header') ;
110 create_payment_header;
111 END IF;
112
113 FND_FILE.put_line(FND_FILE.log, 'Payment Detail') ;
114 populate_payment_details
115 (p_start_date => p_start_date) ;
116
117 IF p_action_flg = 'Y' THEN
118 FND_FILE.put_line(FND_FILE.log, 'SAM Header') ;
119 create_sam_header;
120 END IF;
121
122 FND_FILE.put_line(FND_FILE.log, 'SAM Detail') ;
123 populate_sam_details
124 (
125 p_organization_id => p_organization_id,
126 p_location_id => p_location_id,
127 p_start_date => p_start_date ,
128 p_end_date => p_end_date,
129 p_auth_sign => p_auth_sign ,
130 p_place => p_place
131 ) ;
132
133 FND_FILE.put_line(FND_FILE.log, 'Close File') ;
134
135 closeFile;
136 FND_FILE.put_line(FND_FILE.log, 'End ') ;
137
138 -- Body for Generate Returns
139
140 END generate_flat_file;
141
142
143 -- Procedure to open a file for writing
144
145 PROCEDURE openFile(
146 p_directory IN VARCHAR2,
147 p_filename IN VARCHAR2
148 )
149 IS
150
151 BEGIN
152
153 v_filehandle := UTL_FILE.fopen(p_directory, p_filename, 'W',32767);
154
155 v_utl_file_dir := p_directory;
156 v_utl_file_name := p_filename;
157
158 END openFile;
159
160 -- Procedure to close the file
161
162 PROCEDURE closeFile
163 IS
164 BEGIN
165 UTL_FILE.fclose(v_filehandle);
166 END closeFile;
167
168
169 -- Procedure to Create the CETH header in the file
170
171 PROCEDURE create_ceth_header
172 IS
173 BEGIN
174 UTL_FILE.PUT_LINE(v_filehandle, ' ' ) ;
175
176 UTL_FILE.PUT_LINE(v_filehandle,
177 LPAD('Record Header', sq_len_50, v_quart_pad) || v_pad_char ||
178 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
179 LPAD('Reg No', sq_len_15, v_quart_pad) || v_pad_char ||
180 LPAD('YM', sq_len_6, v_quart_pad) || v_pad_char ||
181 LPAD('RN', sq_len_3, v_quart_pad) || v_pad_char ||
182 LPAD('DP', sq_len_2, v_quart_pad) || v_pad_char ||
183 LPAD('CETH', sq_len_8, v_quart_pad) || v_pad_char ||
184 LPAD('CTSH', sq_len_8, v_quart_pad) || v_pad_char ||
185 LPAD('UQC', sq_len_8, v_quart_pad) || v_pad_char ||
186 LPAD('QTY MNF', sq_len_15, v_quart_pad) || v_pad_char ||
187 LPAD('QTY CLR TYPE', sq_len_15, v_quart_pad) || v_pad_char ||
188 LPAD('QTY CLR', sq_len_15, v_quart_pad) || v_pad_char ||
189 LPAD('ASSESS VAL', sq_len_15, v_quart_pad) || v_pad_char ||
190 LPAD('NOTF1', sq_len_8, v_quart_pad) || v_pad_char ||
191 LPAD('NOTF SNO1', sq_len_10, v_quart_pad) || v_pad_char ||
192 LPAD('NOTF2', sq_len_8, v_quart_pad) || v_pad_char ||
193 LPAD('NOTF SNO2', sq_len_10, v_quart_pad) || v_pad_char ||
194 LPAD('NOTF3', sq_len_8, v_quart_pad) || v_pad_char ||
195 LPAD('NOTF SNO3', sq_len_10, v_quart_pad) || v_pad_char ||
196 LPAD('NOTF4', sq_len_8, v_quart_pad) || v_pad_char ||
197 LPAD('NOTF SNO4', sq_len_10, v_quart_pad) || v_pad_char ||
198 LPAD('NOTF5', sq_len_8, v_quart_pad) || v_pad_char ||
199 LPAD('NOTF SNO5', sq_len_10, v_quart_pad) || v_pad_char ||
200 LPAD('NOTF6', sq_len_8, v_quart_pad) || v_pad_char ||
201 LPAD('NOTF SNO6', sq_len_10, v_quart_pad) || v_pad_char ||
202 LPAD('ADV CENVAT', sq_len_11, v_quart_pad) || v_pad_char ||
203 LPAD('SP CENVAT', sq_len_11, v_quart_pad) || v_pad_char ||
204 LPAD('DP CENVAT', sq_len_15, v_quart_pad) || v_pad_char ||
205 LPAD('PA CEN', sq_len_7, v_quart_pad) || v_pad_char ||
206 LPAD('ADV SED', sq_len_11, v_quart_pad) || v_pad_char ||
207 LPAD('SP SED', sq_len_11, v_quart_pad) || v_pad_char ||
208 LPAD('DP SED', sq_len_15, v_quart_pad) || v_pad_char ||
209 LPAD('PA SED', sq_len_7, v_quart_pad) || v_pad_char ||
210 LPAD('ADV AED GSI', sq_len_11, v_quart_pad) || v_pad_char ||
211 LPAD('SP AED GSI', sq_len_11, v_quart_pad) || v_pad_char ||
212 LPAD('DP AED GSI', sq_len_15, v_quart_pad) || v_pad_char ||
213 LPAD('PA GSI', sq_len_7, v_quart_pad) || v_pad_char ||
214 LPAD('ADV NCCD', sq_len_11, v_quart_pad) || v_pad_char ||
215 LPAD('SP NCCD', sq_len_11, v_quart_pad) || v_pad_char ||
216 LPAD('DP NCCD', sq_len_15, v_quart_pad) || v_pad_char ||
217 LPAD('PA NCCD', sq_len_7, v_quart_pad) || v_pad_char ||
218 LPAD('ADV AED TTA', sq_len_11, v_quart_pad) || v_pad_char ||
219 LPAD('SP AED TTA', sq_len_11, v_quart_pad) || v_pad_char ||
220 LPAD('DP AED TTA', sq_len_15, v_quart_pad) || v_pad_char ||
221 LPAD('PA TTA', sq_len_7, v_quart_pad) || v_pad_char ||
222 LPAD('ADV AED PMT', sq_len_11, v_quart_pad) || v_pad_char ||
223 LPAD('SP AED PMT', sq_len_11, v_quart_pad) || v_pad_char ||
224 LPAD('DP AED PMT', sq_len_15, v_quart_pad) || v_pad_char ||
228 LPAD('DP SAED', sq_len_15, v_quart_pad) || v_pad_char ||
225 LPAD('PA PMT', sq_len_7, v_quart_pad) || v_pad_char ||
226 LPAD('ADV SAED', sq_len_11, v_quart_pad) || v_pad_char ||
227 LPAD('SP SAED', sq_len_11, v_quart_pad) || v_pad_char ||
229 LPAD('PA SAED', sq_len_7, v_quart_pad) || v_pad_char ||
230 LPAD('ADV ADE', sq_len_11, v_quart_pad) || v_pad_char ||
231 LPAD('SP ADE', sq_len_11, v_quart_pad) || v_pad_char ||
232 LPAD('DP ADE', sq_len_15, v_quart_pad) || v_pad_char ||
233 LPAD('PA ADE', sq_len_7, v_quart_pad) || v_pad_char ||
234 LPAD('ADV ADET', sq_len_11, v_quart_pad) || v_pad_char ||
235 LPAD('SP ADET', sq_len_11, v_quart_pad) || v_pad_char ||
236 LPAD('DP ADET', sq_len_15, v_quart_pad) || v_pad_char ||
237 LPAD('PA ADET', sq_len_7, v_quart_pad) || v_pad_char ||
238 LPAD('ADV CESS', sq_len_11, v_quart_pad) || v_pad_char ||
239 LPAD('SP CESS', sq_len_11, v_quart_pad) || v_pad_char ||
240 LPAD('DP CESS', sq_len_15, v_quart_pad) || v_pad_char ||
241 LPAD('PA CESS', sq_len_7, v_quart_pad) || v_pad_char ||
242 LPAD('ADV EDUCES', sq_len_11, v_quart_pad) || v_pad_char ||
243 LPAD('SP EDUCES', sq_len_11, v_quart_pad) || v_pad_char ||
244 LPAD('DP EDUCES', sq_len_15, v_quart_pad) || v_pad_char ||
245 LPAD('PA EC', sq_len_7, v_quart_pad)
246 );
247
248 UTL_FILE.PUT_LINE(v_filehandle,
249 LPAD(v_underline_char, sq_len_50, 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_15, v_underline_char) || v_pad_char ||
252 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
253 LPAD(v_underline_char, sq_len_3, v_underline_char) || v_pad_char ||
254 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
255 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
256 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
257 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
258 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
259 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
260 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
261 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
262 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
263 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
264 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
265 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
266 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
267 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
268 LPAD(v_underline_char, sq_len_8, 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_8, v_underline_char) || v_pad_char ||
271 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
272 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
273 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
274 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
275 LPAD(v_underline_char, sq_len_11, 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_7, v_underline_char) || v_pad_char ||
278 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
279 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
280 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
281 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
282 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
283 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
284 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
285 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
286 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
287 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
288 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
289 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
290 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
291 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
292 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
293 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
294 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
295 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
296 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
297 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
298 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
299 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
300 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
301 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
302 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
303 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
304 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
305 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
306 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
307 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
308 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
312 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
309 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
310 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
311 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
313 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
314 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
315 LPAD(v_underline_char, sq_len_11, v_underline_char) || v_pad_char ||
316 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
317 LPAD(v_underline_char, sq_len_7, v_underline_char)
318 );
319
320 END create_ceth_header;
321
322 -- Procedure to create DUTY PAID headers
323
324 PROCEDURE create_duty_header IS
325 BEGIN
326 UTL_FILE.PUT_LINE(v_filehandle, ' ' ) ;
327
328 UTL_FILE.PUT_LINE(v_filehandle,
329 LPAD('Record Header', sq_len_50, v_quart_pad) || v_pad_char ||
330 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
331 LPAD('Reg No', sq_len_15, v_quart_pad) || v_pad_char ||
332 LPAD('YM', sq_len_6, v_quart_pad) || v_pad_char ||
333 LPAD('RN', sq_len_3, v_quart_pad) || v_pad_char ||
334 LPAD('DP', sq_len_2, v_quart_pad) || v_pad_char ||
335 LPAD('DP CR CEN', sq_len_15, v_quart_pad) || v_pad_char ||
336 LPAD('DP CU CEN', sq_len_15, v_quart_pad) || v_pad_char ||
337 LPAD('CN CEN', sq_len_10, v_quart_pad) || v_pad_char ||
338 LPAD('CD CEN', sq_len_10, v_quart_pad) || v_pad_char ||
339 LPAD('BC CEN', sq_len_7, v_quart_pad) || v_pad_char ||
340 LPAD('DP TOT CEN', sq_len_15, v_quart_pad) || v_pad_char ||
341 LPAD('DP CR SED', sq_len_15, v_quart_pad) || v_pad_char ||
342 LPAD('DP CU SED', sq_len_15, v_quart_pad) || v_pad_char ||
343 LPAD('CN SED', sq_len_10, v_quart_pad) || v_pad_char ||
344 LPAD('CD SED', sq_len_10, v_quart_pad) || v_pad_char ||
345 LPAD('BC SED', sq_len_7, v_quart_pad) || v_pad_char ||
346 LPAD('DP TOT SED', sq_len_15, v_quart_pad) || v_pad_char ||
347 LPAD('DP CR AEDGSI', sq_len_15, v_quart_pad) || v_pad_char ||
348 LPAD('DP CU AEDGSI', sq_len_15, v_quart_pad) || v_pad_char ||
349 LPAD('CN AEDGSI', sq_len_10, v_quart_pad) || v_pad_char ||
350 LPAD('CD AEDGSI', sq_len_10, v_quart_pad) || v_pad_char ||
351 LPAD('BC AGSI', sq_len_7, v_quart_pad) || v_pad_char ||
352 LPAD('DP TOT AEDGSI', sq_len_15, v_quart_pad) || v_pad_char ||
353 LPAD('DP CR NCCD', sq_len_15, v_quart_pad) || v_pad_char ||
354 LPAD('DP CU NCCD', sq_len_15, v_quart_pad) || v_pad_char ||
355 LPAD('CN NCCD', sq_len_10, v_quart_pad) || v_pad_char ||
356 LPAD('CD NCCD', sq_len_10, v_quart_pad) || v_pad_char ||
357 LPAD('BC NCCD', sq_len_7, v_quart_pad) || v_pad_char ||
358 LPAD('DP TOT NCCD', sq_len_15, v_quart_pad) || v_pad_char ||
359 LPAD('DP CR AEDTTA', sq_len_15, v_quart_pad) || v_pad_char ||
360 LPAD('DP CU AEDTTA', sq_len_15, v_quart_pad) || v_pad_char ||
361 LPAD('CN AEDTTA', sq_len_10, v_quart_pad) || v_pad_char ||
362 LPAD('CD AEDTTA', sq_len_10, v_quart_pad) || v_pad_char ||
363 LPAD('BC ATTA', sq_len_7, v_quart_pad) || v_pad_char ||
364 LPAD('DP TOT AEDTTA', sq_len_15, v_quart_pad) || v_pad_char ||
365 LPAD('DP CR AEDPMT', sq_len_15, v_quart_pad) || v_pad_char ||
366 LPAD('DP CU AEDPMT', sq_len_15, v_quart_pad) || v_pad_char ||
367 LPAD('CN AEDPMT', sq_len_10, v_quart_pad) || v_pad_char ||
368 LPAD('CD AEDPMT', sq_len_10, v_quart_pad) || v_pad_char ||
369 LPAD('BC APMT', sq_len_7, v_quart_pad) || v_pad_char ||
370 LPAD('DP TOT AEDPMT', sq_len_15, v_quart_pad) || v_pad_char ||
371 LPAD('DP CR SAED', sq_len_15, v_quart_pad) || v_pad_char ||
372 LPAD('DP CU SAED', sq_len_15, v_quart_pad) || v_pad_char ||
373 LPAD('CN SAED', sq_len_10, v_quart_pad) || v_pad_char ||
374 LPAD('CD SAED', sq_len_10, v_quart_pad) || v_pad_char ||
375 LPAD('BC SAED', sq_len_7, v_quart_pad) || v_pad_char ||
376 LPAD('DP TOT SAED', sq_len_15, v_quart_pad) || v_pad_char ||
377 LPAD('DP CR ADE', sq_len_15, v_quart_pad) || v_pad_char ||
378 LPAD('DP CU ADE', sq_len_15, v_quart_pad) || v_pad_char ||
379 LPAD('CN ADE', sq_len_10, v_quart_pad) || v_pad_char ||
380 LPAD('CD ADE', sq_len_10, v_quart_pad) || v_pad_char ||
381 LPAD('BC ADE', sq_len_7, v_quart_pad) || v_pad_char ||
382 LPAD('DP TOT ADE', sq_len_15, v_quart_pad) || v_pad_char ||
383 LPAD('DP CR ADET', sq_len_15, v_quart_pad) || v_pad_char ||
384 LPAD('DP CU ADET', sq_len_15, v_quart_pad)
385 );
386
387 UTL_FILE.PUT_LINE(v_filehandle,
388 LPAD(v_underline_char, sq_len_50, v_underline_char) || v_pad_char ||
389 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
390 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
391 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
392 LPAD(v_underline_char, sq_len_3, v_underline_char) || v_pad_char ||
393 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
394 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
395 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
396 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
397 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
398 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
399 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
400 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
401 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
405 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
402 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
403 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
404 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
406 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
407 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
408 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
409 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
410 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
411 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
412 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
413 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
414 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
415 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
416 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
417 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
418 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
419 LPAD(v_underline_char, sq_len_15, 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_10, v_underline_char) || v_pad_char ||
422 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
423 LPAD(v_underline_char, sq_len_15, 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_10, v_underline_char) || v_pad_char ||
427 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
428 LPAD(v_underline_char, sq_len_7, 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_15, 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_10, v_underline_char) || v_pad_char ||
433 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
434 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
435 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
436 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
437 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
438 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
439 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
440 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
441 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
442 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
443 LPAD(v_underline_char, sq_len_15, v_underline_char)
444 );
445
446 END create_duty_header;
447
448 -- Procedure to create the Input Details Header
449
450 PROCEDURE create_input_header
451 IS
452 BEGIN
453 UTL_FILE.PUT_LINE(v_filehandle, ' ' ) ;
454
455 UTL_FILE.PUT_LINE(v_filehandle,
456 LPAD('Record Header', sq_len_50, v_quart_pad) || v_pad_char ||
457 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
458 LPAD('Reg No', sq_len_15, v_quart_pad) || v_pad_char ||
459 LPAD('YM', sq_len_6, v_quart_pad) || v_pad_char ||
460 LPAD('RN', sq_len_3, v_quart_pad) || v_pad_char ||
461 LPAD('CETH', sq_len_8, v_quart_pad) || v_pad_char ||
462 LPAD('CTSH', sq_len_8, v_quart_pad) || v_pad_char ||
463 LPAD('UQC', sq_len_8, v_quart_pad) || v_pad_char ||
464 LPAD('TOT QTY RECD', sq_len_15, v_quart_pad) || v_pad_char ||
465 LPAD('VAL RECD', sq_len_15, v_quart_pad) || v_pad_char ||
466 LPAD('NOTF', sq_len_8, v_quart_pad) || v_pad_char ||
467 LPAD('NOTF SNO', sq_len_10, v_quart_pad)
468 );
469
470 UTL_FILE.PUT_LINE(v_filehandle,
471 LPAD(v_underline_char, sq_len_50, v_underline_char) || v_pad_char ||
472 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
473 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
474 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
475 LPAD(v_underline_char, sq_len_3, v_underline_char) || v_pad_char ||
476 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
477 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
478 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
479 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
480 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
481 LPAD(v_underline_char, sq_len_8, v_underline_char) || v_pad_char ||
482 LPAD(v_underline_char, sq_len_10, v_underline_char)
483 );
484
485 END create_input_header;
486
487 -- Procedure to create CENVAT credit headers
488
489 PROCEDURE create_cenvat_header
490 IS
491 BEGIN
492 UTL_FILE.PUT_LINE(v_filehandle, ' ' ) ;
493
494 UTL_FILE.PUT_LINE(v_filehandle,
495 LPAD('Record Header', sq_len_50, v_quart_pad) || v_pad_char ||
496 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
497 LPAD('Reg No', sq_len_15, v_quart_pad) || v_pad_char ||
498 LPAD('YM', sq_len_6, v_quart_pad) || v_pad_char ||
499 LPAD('RN', sq_len_3, v_quart_pad) || v_pad_char ||
500 LPAD('DP', sq_len_2, v_quart_pad) || v_pad_char ||
501 LPAD('OP BAL CEN', sq_len_13, v_quart_pad) || v_pad_char ||
502 LPAD('CR IN CEN', sq_len_13, v_quart_pad) || v_pad_char ||
503 LPAD('CR IND CEN', sq_len_13, v_quart_pad) || v_pad_char ||
504 LPAD('CR CA CEN', sq_len_13, v_quart_pad) || v_pad_char ||
505 LPAD('CR SE CEN', sq_len_13, v_quart_pad) || v_pad_char ||
506 LPAD('CR TOT CEN', sq_len_13, v_quart_pad) || v_pad_char ||
507 LPAD('CR UT CEN', sq_len_13, v_quart_pad) || v_pad_char ||
508 LPAD('CR UIC CEN', sq_len_13, v_quart_pad) || v_pad_char ||
509 LPAD('CR UDS CEN', sq_len_13, v_quart_pad) || v_pad_char ||
510 LPAD('CL BAL CEN', sq_len_13, v_quart_pad) || v_pad_char ||
511 LPAD('OP BAL ATTA', sq_len_12, v_quart_pad) || v_pad_char ||
512 LPAD('CR IN ATTA', sq_len_12, v_quart_pad) || v_pad_char ||
513 LPAD('CR IND ATTA', sq_len_12, v_quart_pad) || v_pad_char ||
514 LPAD('CR CA ATTA', sq_len_12, v_quart_pad) || v_pad_char ||
515 LPAD('CR SE ATTA', sq_len_12, v_quart_pad) || v_pad_char ||
516 LPAD('CR TOT ATTA', sq_len_12, v_quart_pad) || v_pad_char ||
517 LPAD('CR UT ATTA', sq_len_12, v_quart_pad) || v_pad_char ||
518 LPAD('CR UIC ATTA', sq_len_12, v_quart_pad) || v_pad_char ||
519 LPAD('CR UDS ATTA', sq_len_12, v_quart_pad) || v_pad_char ||
520 LPAD('CL BAL ATTA', sq_len_12, v_quart_pad) || v_pad_char ||
521 LPAD('OP BAL APMT', sq_len_12, v_quart_pad) || v_pad_char ||
522 LPAD('CR IN APMT', sq_len_12, v_quart_pad) || v_pad_char ||
523 LPAD('CR IND APMT', sq_len_12, v_quart_pad) || v_pad_char ||
524 LPAD('CR CA APMT', sq_len_12, v_quart_pad) || v_pad_char ||
525 LPAD('CR SE APMT', sq_len_12, v_quart_pad) || v_pad_char ||
526 LPAD('CR TOT APMT', sq_len_12, v_quart_pad) || v_pad_char ||
527 LPAD('CR UT APMT', sq_len_12, v_quart_pad) || v_pad_char ||
528 LPAD('CR UIC APMT', sq_len_12, v_quart_pad) || v_pad_char ||
529 LPAD('CR UDS APMT', sq_len_12, v_quart_pad) || v_pad_char ||
530 LPAD('CL BAL APMT', sq_len_12, v_quart_pad) || v_pad_char ||
531 LPAD('OP BAL NCCD', sq_len_12, v_quart_pad) || v_pad_char ||
532 LPAD('CR IN NCCD', sq_len_12, v_quart_pad) || v_pad_char ||
533 LPAD('CR IND NCCD', sq_len_12, v_quart_pad) || v_pad_char ||
534 LPAD('CR CA NCCD', sq_len_12, v_quart_pad) || v_pad_char ||
535 LPAD('CR SE NCCD', sq_len_12, v_quart_pad) || v_pad_char ||
536 LPAD('CR TOT NCCD', sq_len_12, v_quart_pad) || v_pad_char ||
537 LPAD('CR UT NCCD', sq_len_12, v_quart_pad) || v_pad_char ||
538 LPAD('CR UIC NCCD', sq_len_12, v_quart_pad) || v_pad_char ||
539 LPAD('CR UDS NCCD', sq_len_12, v_quart_pad) || v_pad_char ||
540 LPAD('CL BAL NCCD', sq_len_12, v_quart_pad) || v_pad_char ||
541 LPAD('OP BAL ADET', sq_len_12, v_quart_pad) || v_pad_char ||
542 LPAD('CR IN ADET', sq_len_12, v_quart_pad) || v_pad_char ||
543 LPAD('CR IND ADET', sq_len_12, v_quart_pad) || v_pad_char ||
544 LPAD('CR CA ADET', sq_len_12, v_quart_pad) || v_pad_char ||
545 LPAD('CR SE ADET', sq_len_12, v_quart_pad) || v_pad_char ||
546 LPAD('CR TOT ADET', sq_len_12, v_quart_pad) || v_pad_char ||
547 LPAD('CR UT ADET', sq_len_12, v_quart_pad) || v_pad_char ||
548 LPAD('CR UIC ADET', sq_len_12, v_quart_pad) || v_pad_char ||
549 LPAD('CR UDS ADET', sq_len_12, v_quart_pad) || v_pad_char ||
550 LPAD('CL BAL ADET', sq_len_12, v_quart_pad) || v_pad_char ||
551 LPAD('OP BAL ECES', sq_len_12, v_quart_pad) || v_pad_char ||
552 LPAD('CR IN ECES', sq_len_12, v_quart_pad) || v_pad_char ||
553 LPAD('CR IND ECES', sq_len_12, v_quart_pad) || v_pad_char ||
554 LPAD('CR CA ECES', sq_len_12, v_quart_pad) || v_pad_char ||
555 LPAD('CR SE ECES', sq_len_12, v_quart_pad) || v_pad_char ||
556 LPAD('CR TOT ECES', sq_len_12, v_quart_pad) || v_pad_char ||
557 LPAD('CR UT ECES', sq_len_12, v_quart_pad) || v_pad_char ||
558 LPAD('CR UIC ECES', sq_len_12, v_quart_pad) || v_pad_char ||
559 LPAD('CR UDS ECES', sq_len_12, v_quart_pad) || v_pad_char ||
560 LPAD('CL BAL ECES', sq_len_12, v_quart_pad) || v_pad_char ||
561 LPAD('OP BAL ST', sq_len_12, v_quart_pad) || v_pad_char ||
562 LPAD('CR IN ST', sq_len_12, v_quart_pad) || v_pad_char ||
563 LPAD('CR IND ST', sq_len_12, v_quart_pad) || v_pad_char ||
564 LPAD('CR CA ST', sq_len_12, v_quart_pad) || v_pad_char ||
565 LPAD('CR SE ST', sq_len_12, v_quart_pad) || v_pad_char ||
566 LPAD('CR TOT ST', sq_len_12, v_quart_pad) || v_pad_char ||
567 LPAD('CR UT ST', sq_len_12, v_quart_pad) || v_pad_char ||
568 LPAD('CR UIC ST', sq_len_12, v_quart_pad) || v_pad_char ||
569 LPAD('CR UDS ST', sq_len_12, v_quart_pad) || v_pad_char ||
573 LPAD('CR IND STEC', sq_len_12, v_quart_pad) || v_pad_char ||
570 LPAD('CL BAL ST', sq_len_12, v_quart_pad) || v_pad_char ||
571 LPAD('OP BAL STEC', sq_len_12, v_quart_pad) || v_pad_char ||
572 LPAD('CR IN STEC', sq_len_12, v_quart_pad) || v_pad_char ||
574 LPAD('CR CA STEC', sq_len_12, v_quart_pad) || v_pad_char ||
575 LPAD('CR SE STEC', sq_len_12, v_quart_pad) || v_pad_char ||
576 LPAD('CR TOT STEC', sq_len_12, v_quart_pad) || v_pad_char ||
577 LPAD('CR UT STEC', sq_len_12, v_quart_pad) || v_pad_char ||
578 LPAD('CR UIC STEC', sq_len_12, v_quart_pad) || v_pad_char ||
579 LPAD('CR UDS STEC', sq_len_12, v_quart_pad) || v_pad_char ||
580 LPAD('CL BAL STEC', sq_len_12, v_quart_pad)
581 );
582
583 UTL_FILE.PUT_LINE(v_filehandle,
584 LPAD(v_underline_char, sq_len_50, v_underline_char) || v_pad_char ||
585 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
586 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
587 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
588 LPAD(v_underline_char, sq_len_3, v_underline_char) || v_pad_char ||
589 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
590 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
591 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
592 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
593 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
594 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
595 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
596 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
597 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
598 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
599 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
600 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
601 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
602 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
603 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
604 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
605 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
606 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
607 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
608 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
609 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
610 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
611 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
612 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
613 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
614 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
615 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
616 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
617 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
618 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
619 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
620 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
621 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
622 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
623 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
624 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
625 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
626 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
627 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
628 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
629 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
630 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
631 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
632 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
633 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
634 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
635 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
636 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
637 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
638 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
639 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
640 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
641 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
642 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
643 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
644 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
645 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
646 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
647 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
648 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
649 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
650 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
651 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
652 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
653 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
654 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
655 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
656 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
657 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
658 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
659 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
660 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
661 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
662 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
663 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
664 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
665 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
666 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
667 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
668 LPAD(v_underline_char, sq_len_12, v_underline_char) || v_pad_char ||
669 LPAD(v_underline_char, sq_len_12, v_underline_char)
670 );
671
672 END create_cenvat_header ;
673
674 -- Procedure to create PAYMENT Header
675
676 PROCEDURE create_payment_header
677 IS
678 BEGIN
679 UTL_FILE.PUT_LINE(v_filehandle, ' ' ) ;
680
681 UTL_FILE.PUT_LINE(v_filehandle,
682 LPAD('Record Header', sq_len_50, v_quart_pad) || v_pad_char ||
683 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
684 LPAD('Reg No', sq_len_15, v_quart_pad) || v_pad_char ||
685 LPAD('YM', sq_len_6, v_quart_pad) || v_pad_char ||
686 LPAD('RN', sq_len_3, v_quart_pad) || v_pad_char ||
687 LPAD('ARR R8 CU', sq_len_13, v_quart_pad) || v_pad_char ||
688 LPAD('ARR R8 CR', sq_len_13, v_quart_pad) || v_pad_char ||
689 LPAD('ARR R8 CN', sq_len_10, v_quart_pad) || v_pad_char ||
690 LPAD('ARR R8 CD', sq_len_10, v_quart_pad) || v_pad_char ||
691 LPAD('AR R8BC', sq_len_7, v_quart_pad) || v_pad_char ||
692 LPAD('ARR R8 SRCNO', sq_len_40, v_quart_pad) || v_pad_char ||
693 LPAD('ARR R8 SDT', sq_len_10, v_quart_pad) || v_pad_char ||
694 LPAD('ARR CU', sq_len_13, v_quart_pad) || v_pad_char ||
695 LPAD('ARR CR', sq_len_13, v_quart_pad) || v_pad_char ||
696 LPAD('ARR CN', sq_len_10, v_quart_pad) || v_pad_char ||
697 LPAD('ARR CD', sq_len_10, v_quart_pad) || v_pad_char ||
698 LPAD('ARR BC', sq_len_7, v_quart_pad) || v_pad_char ||
699 LPAD('ARR SRCNO', sq_len_40, v_quart_pad) || v_pad_char ||
700 LPAD('ARR SDT', sq_len_10, v_quart_pad) || v_pad_char ||
701 LPAD('INT R8 CU', sq_len_13, v_quart_pad) || v_pad_char ||
702 LPAD('INT R8 CR', sq_len_13, v_quart_pad) || v_pad_char ||
703 LPAD('INT R8 CN', sq_len_10, v_quart_pad) || v_pad_char ||
704 LPAD('INT R8 CD', sq_len_10, v_quart_pad) || v_pad_char ||
705 LPAD('IN R8BC', sq_len_7, v_quart_pad) || v_pad_char ||
706 LPAD('INT R8 SRCNO', sq_len_40, v_quart_pad) || v_pad_char ||
707 LPAD('INT R8 SDT', sq_len_10, v_quart_pad) || v_pad_char ||
708 LPAD('INT CU', sq_len_13, v_quart_pad) || v_pad_char ||
709 LPAD('INT CR', sq_len_13, v_quart_pad) || v_pad_char ||
710 LPAD('INT CN', sq_len_10, v_quart_pad) || v_pad_char ||
711 LPAD('INT CD', sq_len_10, v_quart_pad) || v_pad_char ||
715 LPAD('MIS CU', sq_len_13, v_quart_pad) || v_pad_char ||
712 LPAD('IN BC', sq_len_7, v_quart_pad) || v_pad_char ||
713 LPAD('INT SRCNO', sq_len_40, v_quart_pad) || v_pad_char ||
714 LPAD('INT SDT', sq_len_10, v_quart_pad) || v_pad_char ||
716 LPAD('MIS CR', sq_len_13, v_quart_pad) || v_pad_char ||
717 LPAD('MIS CN', sq_len_10, v_quart_pad) || v_pad_char ||
718 LPAD('MIS CD', sq_len_10, v_quart_pad) || v_pad_char ||
719 LPAD('MI BC', sq_len_7, v_quart_pad) || v_pad_char ||
720 LPAD('MIS SRCNO', sq_len_40, v_quart_pad) || v_pad_char ||
721 LPAD('MIS SDT', sq_len_10, v_quart_pad)
722 );
723
724 UTL_FILE.PUT_LINE(v_filehandle,
725 LPAD(v_underline_char, sq_len_50, v_underline_char) || v_pad_char ||
726 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
727 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
728 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
729 LPAD(v_underline_char, sq_len_3, v_underline_char) || v_pad_char ||
730 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
731 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
732 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
733 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
734 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
735 LPAD(v_underline_char, sq_len_40, v_underline_char) || v_pad_char ||
736 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
737 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
738 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
739 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
740 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
741 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
742 LPAD(v_underline_char, sq_len_40, v_underline_char) || v_pad_char ||
743 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
744 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
745 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
746 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
747 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
748 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
749 LPAD(v_underline_char, sq_len_40, v_underline_char) || v_pad_char ||
750 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
751 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
752 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
753 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
754 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
755 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
756 LPAD(v_underline_char, sq_len_40, v_underline_char) || v_pad_char ||
757 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
758 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
759 LPAD(v_underline_char, sq_len_13, v_underline_char) || v_pad_char ||
760 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
761 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
762 LPAD(v_underline_char, sq_len_7, v_underline_char) || v_pad_char ||
763 LPAD(v_underline_char, sq_len_40, v_underline_char) || v_pad_char ||
764 LPAD(v_underline_char, sq_len_10, v_underline_char)
765 );
766
767 END create_payment_header;
768
769
770 -- Procedure to create SELF ASSESSMENT MEMORANDUM headers
771
772 PROCEDURE create_sam_header IS
773 BEGIN
774 UTL_FILE.PUT_LINE(v_filehandle, ' ' ) ;
775
776 UTL_FILE.PUT_LINE(v_filehandle,
777 LPAD('Record Header', sq_len_50, v_quart_pad) || v_pad_char ||
778 LPAD('RT', sq_len_2, v_quart_pad) || v_pad_char ||
779 LPAD('Reg No', sq_len_15, v_quart_pad) || v_pad_char ||
780 LPAD('YM', sq_len_6, v_quart_pad) || v_pad_char ||
781 LPAD('RN', sq_len_3, v_quart_pad) || v_pad_char ||
782 LPAD('TR6 TOT AMT', sq_len_15, v_quart_pad) || v_pad_char ||
783 LPAD('INV ISSF1', sq_len_10, v_quart_pad) || v_pad_char ||
784 LPAD('INV ISST1', sq_len_10, v_quart_pad) || v_pad_char ||
785 LPAD('INV ISSF2', sq_len_10, v_quart_pad) || v_pad_char ||
786 LPAD('INV ISST2', sq_len_10, v_quart_pad) || v_pad_char ||
787 LPAD('INV ISSF3', sq_len_10, v_quart_pad) || v_pad_char ||
788 LPAD('INV ISST3', sq_len_10, v_quart_pad) || v_pad_char ||
789 LPAD('INV ISSF4', sq_len_10, v_quart_pad) || v_pad_char ||
790 LPAD('INV ISST4', sq_len_10, v_quart_pad) || v_pad_char ||
791 LPAD('INV ISSF5', sq_len_10, v_quart_pad) || v_pad_char ||
792 LPAD('INV ISST5', sq_len_10, v_quart_pad) || v_pad_char ||
793 LPAD('INV ISSF6', sq_len_10, v_quart_pad) || v_pad_char ||
794 LPAD('INV ISST6', sq_len_10, v_quart_pad) || v_pad_char ||
795 LPAD('REMARKS', sq_len_255, v_quart_pad) || v_pad_char ||
796 LPAD('PLACE', sq_len_20, v_quart_pad) || v_pad_char ||
797 LPAD('DT FIL', sq_len_10, v_quart_pad) || v_pad_char ||
798 LPAD('NAME AUTH SIGN', sq_len_40, v_quart_pad)
799 );
800
801
802 UTL_FILE.PUT_LINE(v_filehandle,
803 LPAD(v_underline_char, sq_len_50, v_underline_char) || v_pad_char ||
804 LPAD(v_underline_char, sq_len_2, v_underline_char) || v_pad_char ||
805 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
806 LPAD(v_underline_char, sq_len_6, v_underline_char) || v_pad_char ||
807 LPAD(v_underline_char, sq_len_3, v_underline_char) || v_pad_char ||
811 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
808 LPAD(v_underline_char, sq_len_15, v_underline_char) || v_pad_char ||
809 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
810 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
812 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
813 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
814 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
815 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
816 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
817 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
818 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
819 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
820 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
821 LPAD(v_underline_char, sq_len_255,v_underline_char) || v_pad_char ||
822 LPAD(v_underline_char, sq_len_20, v_underline_char) || v_pad_char ||
823 LPAD(v_underline_char, sq_len_10, v_underline_char) || v_pad_char ||
824 LPAD(v_underline_char, sq_len_40, v_underline_char)
825 );
826
827 END create_sam_header;
828
829
830 -- Function to create a string with the data for appropriate formatting
831
832 FUNCTION format_action(f_field_name varchar2, f_len_field number, f_action varchar2, f_last_flag varchar2 DEFAULT 'N')
833 RETURN varchar2 IS
834 v_final_str varchar2(2000);
835 BEGIN
836 IF f_action = 'Y' THEN
837 v_final_str := LPAD(nvl(substr(f_field_name,1,f_len_field),v_pad_char), f_len_field, v_quart_pad) || v_pad_char;
838 ELSE
839 v_final_str := v_append || substr(f_field_name,1,f_len_field) || v_append ;
840
841 IF f_last_flag = 'N' THEN
842 v_final_str := v_final_str || v_delimeter;
843 END IF ;
844 END IF;
845 RETURN v_final_str;
846 END format_action;
847
848 -- The Records
849
850 PROCEDURE create_ceth_details (
851 p_record_header varchar2,
852 p_rt_type varchar2,
853 p_ecc varchar2,
854 p_yyyymm number,
855 p_return_no varchar2,
856 p_data_prd_type varchar2,
857 p_ceth varchar2,
858 p_ctsh varchar2,
859 p_uqc varchar2,
860 p_qty_mnf number,
861 p_qty_clr_type varchar2,
862 p_qty_clr number,
863 p_ass_val number,
864 p_notf_no_1 varchar2,
865 p_notf_sno_1 varchar2,
866 p_notf_no_2 varchar2,
867 p_notf_sno_2 varchar2,
868 p_notf_no_3 varchar2,
869 p_notf_sno_3 varchar2,
870 p_notf_no_4 varchar2,
871 p_notf_sno_4 varchar2,
872 p_notf_no_5 varchar2,
873 p_notf_sno_5 varchar2,
874 p_notf_no_6 varchar2,
875 p_notf_sno_6 varchar2,
876 p_duty_rate_adv_cenvat number,
877 p_duty_rate_sp_cenvat number,
878 p_duty_payable_cenvat number,
879 p_pa_no_cenvat number,
880 p_duty_rate_adv_sed number,
881 p_duty_rate_sp_sed number,
882 p_duty_payable_sed number,
883 p_pa_no_sed number,
884 p_duty_rate_adv_aed_gsi number,
885 p_duty_rate_sp_aed_gsi number,
886 p_duty_payable_aed_gsi number,
887 p_pa_no_aed_gsi number,
888 p_duty_rate_adv_nccd number,
889 p_duty_rate_sp_nccd number,
890 p_duty_payable_nccd number,
891 p_pa_no_nccd number,
892 p_duty_rate_adv_aed_tta number,
893 p_duty_rate_sp_aed_tta number,
894 p_duty_payable_aed_tta number,
895 p_pa_no_aed_tta number,
896 p_duty_rate_adv_aed_pmt number,
897 p_duty_rate_sp_aed_pmt number,
898 p_duty_payable_aed_pmt number,
899 p_pa_no_aed_pmt number,
900 p_duty_rate_adv_saed number,
901 p_duty_rate_sp_saed number,
902 p_duty_payable_saed number,
903 p_pa_no_saed number,
904 p_duty_rate_adv_ade number,
905 p_duty_rate_sp_ade number,
906 p_duty_payable_ade number,
907 p_pa_no_ade number,
908 p_duty_rate_adv_adet number,
909 p_duty_rate_sp_adet number,
910 p_duty_payable_adet number,
911 p_pa_no_adet number,
912 p_duty_rate_adv_cess number,
913 p_duty_rate_sp_cess number,
914 p_duty_payable_cess number,
915 p_pa_no_cess number,
916 p_duty_rate_adv_edu_cess number,
917 p_duty_rate_sp_edu_cess number,
918 p_duty_payable_edu_cess number,
919 p_pa_no_edu_cess number
920 )
921
922 IS
923 BEGIN
924
925 p_str := format_action( p_record_header ,sq_len_50, p_action);
926 p_str := p_str || format_action( p_rt_type ,sq_len_2, p_action);
927 p_str := p_str || format_action( p_ecc ,sq_len_15, p_action);
928 p_str := p_str || format_action( p_yyyymm ,sq_len_6, p_action);
929 p_str := p_str || format_action( p_return_no ,sq_len_3, p_action);
930 p_str := p_str || format_action( p_data_prd_type ,sq_len_2, p_action);
931 p_str := p_str || format_action( p_ceth ,sq_len_8, p_action);
932 p_str := p_str || format_action( p_ctsh ,sq_len_8, p_action);
933 p_str := p_str || format_action( p_uqc ,sq_len_8, p_action);
934 p_str := p_str || format_action( p_qty_mnf ,sq_len_15, p_action);
935 p_str := p_str || format_action( p_qty_clr_type ,sq_len_15, p_action);
936 p_str := p_str || format_action( p_qty_clr ,sq_len_15, p_action);
937 p_str := p_str || format_action( p_ass_val ,sq_len_15, p_action);
938 p_str := p_str || format_action( p_notf_no_1 ,sq_len_8, p_action);
939 p_str := p_str || format_action( p_notf_sno_1 ,sq_len_10, p_action);
940 p_str := p_str || format_action( p_notf_no_2 ,sq_len_8, p_action);
941 p_str := p_str || format_action( p_notf_sno_2 ,sq_len_10, p_action);
942 p_str := p_str || format_action( p_notf_no_3 ,sq_len_8, p_action);
943 p_str := p_str || format_action( p_notf_sno_3 ,sq_len_10, p_action);
944 p_str := p_str || format_action( p_notf_no_4 ,sq_len_8, p_action);
945 p_str := p_str || format_action( p_notf_sno_4 ,sq_len_10, p_action);
946 p_str := p_str || format_action( p_notf_no_5 ,sq_len_8, p_action);
947 p_str := p_str || format_action( p_notf_sno_5 ,sq_len_10, p_action);
948 p_str := p_str || format_action( p_notf_no_6 ,sq_len_8, p_action);
949 p_str := p_str || format_action( p_notf_sno_6 ,sq_len_10, p_action);
950 p_str := p_str || format_action( p_duty_rate_adv_cenvat ,sq_len_11, p_action);
951 p_str := p_str || format_action( p_duty_rate_sp_cenvat ,sq_len_11, p_action);
952 p_str := p_str || format_action( p_duty_payable_cenvat ,sq_len_15, p_action);
953 p_str := p_str || format_action( p_pa_no_cenvat ,sq_len_7, p_action);
954 p_str := p_str || format_action( p_duty_rate_adv_sed ,sq_len_11, p_action);
955 p_str := p_str || format_action( p_duty_rate_sp_sed ,sq_len_11, p_action);
956 p_str := p_str || format_action( p_duty_payable_sed ,sq_len_15, p_action);
957 p_str := p_str || format_action( p_pa_no_sed ,sq_len_7, p_action);
958 p_str := p_str || format_action( p_duty_rate_adv_aed_gsi ,sq_len_11, p_action);
959 p_str := p_str || format_action( p_duty_rate_sp_aed_gsi ,sq_len_11, p_action);
960 p_str := p_str || format_action( p_duty_payable_aed_gsi ,sq_len_15, p_action);
961 p_str := p_str || format_action( p_pa_no_aed_gsi ,sq_len_7, p_action);
962 p_str := p_str || format_action( p_duty_rate_adv_nccd ,sq_len_11, p_action);
963 p_str := p_str || format_action( p_duty_rate_sp_nccd ,sq_len_11, p_action);
964 p_str := p_str || format_action( p_duty_payable_nccd ,sq_len_15, p_action);
965 p_str := p_str || format_action( p_pa_no_nccd ,sq_len_7, p_action);
966 p_str := p_str || format_action( p_duty_rate_adv_aed_tta ,sq_len_11, p_action);
967 p_str := p_str || format_action( p_duty_rate_sp_aed_tta ,sq_len_11, p_action);
968 p_str := p_str || format_action( p_duty_payable_aed_tta ,sq_len_15, p_action);
969 p_str := p_str || format_action( p_pa_no_aed_tta ,sq_len_7, p_action);
970 p_str := p_str || format_action( p_duty_rate_adv_aed_pmt ,sq_len_11, p_action);
971 p_str := p_str || format_action( p_duty_rate_sp_aed_pmt ,sq_len_11, p_action);
972 p_str := p_str || format_action( p_duty_payable_aed_pmt ,sq_len_15, p_action);
973 p_str := p_str || format_action( p_pa_no_aed_pmt ,sq_len_7, p_action);
974 p_str := p_str || format_action( p_duty_rate_adv_saed ,sq_len_11, p_action);
975 p_str := p_str || format_action( p_duty_rate_sp_saed ,sq_len_11, p_action);
976 p_str := p_str || format_action( p_duty_payable_saed ,sq_len_15, p_action);
977 p_str := p_str || format_action( p_pa_no_saed ,sq_len_7, p_action);
978 p_str := p_str || format_action( p_duty_rate_adv_ade ,sq_len_11, p_action);
979 p_str := p_str || format_action( p_duty_rate_sp_ade ,sq_len_11, p_action);
980 p_str := p_str || format_action( p_duty_payable_ade ,sq_len_15, p_action);
981 p_str := p_str || format_action( p_pa_no_ade ,sq_len_7, p_action);
982 p_str := p_str || format_action( p_duty_rate_adv_adet ,sq_len_11, p_action);
983 p_str := p_str || format_action( p_duty_rate_sp_adet ,sq_len_11, p_action);
984 p_str := p_str || format_action( p_duty_payable_adet ,sq_len_15, p_action);
985 p_str := p_str || format_action( p_pa_no_adet ,sq_len_7, p_action);
986 p_str := p_str || format_action( p_duty_rate_adv_cess ,sq_len_11, p_action);
987 p_str := p_str || format_action( p_duty_rate_sp_cess ,sq_len_11, p_action);
988 p_str := p_str || format_action( p_duty_payable_cess ,sq_len_15, p_action);
989 p_str := p_str || format_action( p_pa_no_cess ,sq_len_7, p_action);
990 p_str := p_str || format_action( p_duty_rate_adv_edu_cess ,sq_len_11, p_action);
991 p_str := p_str || format_action( p_duty_rate_sp_edu_cess ,sq_len_11, p_action);
992 p_str := p_str || format_action( p_duty_payable_edu_cess ,sq_len_15, p_action);
993 p_str := p_str || format_action( p_pa_no_edu_cess ,sq_len_7, p_action, lv_last_flag);
994
995
996 UTL_FILE.PUT_LINE(v_filehandle,p_str);
997 p_str:= NULL;
998
999 END create_ceth_details;
1000
1001 PROCEDURE create_duty_paid_details(
1002 p_record_header varchar2,
1003 p_rt_type varchar2,
1004 p_ecc varchar2,
1005 p_yyyymm number,
1006 p_return_no varchar2,
1007 p_data_prd_type varchar2,
1008 p_duty_paid_credit_cenvat number,
1009 p_duty_paid_current_cenvat number,
1010 p_challan_no_cenvat varchar2,
1011 p_challan_date_cenvat date,
1012 p_bank_code_cenvat varchar2,
1013 p_duty_paid_total_cenvat number,
1014 p_duty_paid_credit_sed number,
1015 p_duty_paid_current_sed number,
1016 p_challan_no_sed varchar2,
1017 p_challan_date_sed date,
1018 p_bank_code_sed varchar2,
1019 p_duty_paid_total_sed number,
1020 p_duty_paid_credit_aed_gsi number,
1021 p_duty_paid_current_aed_gsi number,
1022 p_challan_no_aed_gsi varchar2,
1023 p_challan_date_aed_gsi date,
1024 p_bank_code_aed_gsi varchar2,
1025 p_duty_paid_total_aed_gsi number,
1026 p_duty_paid_credit_nccd number,
1027 p_duty_paid_current_nccd number,
1028 p_challan_no_nccd varchar2,
1029 p_challan_date_nccd date,
1030 p_bank_code_nccd varchar2,
1031 p_duty_paid_total_nccd number,
1032 p_duty_paid_credit_aed_tta number,
1033 p_duty_paid_current_aed_tta number,
1034 p_challan_no_aed_tta varchar2,
1035 p_challan_date_aed_tta date,
1036 p_bank_code_aed_tta varchar2,
1037 p_duty_paid_total_aed_tta number,
1038 p_duty_paid_credit_aed_pmt number,
1039 p_duty_paid_current_aed_pmt number,
1040 p_challan_no_aed_pmt varchar2,
1041 p_challan_date_aed_pmt date,
1042 p_bank_code_aed_pmt varchar2,
1043 p_duty_paid_total_aed_pmt number,
1044 p_duty_paid_credit_saed number,
1045 p_duty_paid_current_saed number,
1046 p_challan_no_saed varchar2,
1047 p_challan_date_saed date,
1048 p_bank_code_saed varchar2,
1049 p_duty_paid_total_saed number,
1050 p_duty_paid_credit_ade number,
1051 p_duty_paid_current_ade number,
1052 p_challan_no_ade varchar2,
1053 p_challan_date_ade date,
1054 p_bank_code_ade varchar2,
1055 p_duty_paid_total_ade number,
1056 p_duty_paid_credit_adet number,
1057 p_duty_paid_current_adet number
1058 )
1059
1060 IS
1061 BEGIN
1062
1063 p_str := format_action( p_record_header ,sq_len_50, p_action);
1064 p_str := p_str || format_action( p_rt_type ,sq_len_2, p_action);
1065 p_str := p_str || format_action( p_ecc ,sq_len_15, p_action);
1066 p_str := p_str || format_action( p_yyyymm ,sq_len_6, p_action);
1067 p_str := p_str || format_action( p_return_no ,sq_len_3, p_action);
1068 p_str := p_str || format_action( p_data_prd_type ,sq_len_2, p_action);
1069 p_str := p_str || format_action( p_duty_paid_credit_cenvat ,sq_len_15, p_action);
1070 p_str := p_str || format_action( p_duty_paid_current_cenvat ,sq_len_15, p_action);
1071 p_str := p_str || format_action( p_challan_no_cenvat ,sq_len_10, p_action);
1072 p_str := p_str || format_action( to_char(p_challan_date_cenvat,'DD/MM/YYYY') ,sq_len_10, p_action);
1073 p_str := p_str || format_action( p_bank_code_cenvat ,sq_len_7, p_action);
1074 p_str := p_str || format_action( p_duty_paid_total_cenvat ,sq_len_15, p_action);
1075 p_str := p_str || format_action( p_duty_paid_credit_sed ,sq_len_15, p_action);
1076 p_str := p_str || format_action( p_duty_paid_current_sed ,sq_len_15, p_action);
1077 p_str := p_str || format_action( p_challan_no_sed ,sq_len_10, p_action);
1078 p_str := p_str || format_action( to_char(p_challan_date_sed ,'DD/MM/YYYY') ,sq_len_10, p_action);
1079 p_str := p_str || format_action( p_bank_code_sed ,sq_len_7, p_action);
1080 p_str := p_str || format_action( p_duty_paid_total_sed ,sq_len_15, p_action);
1081 p_str := p_str || format_action( p_duty_paid_credit_aed_gsi ,sq_len_15, p_action);
1082 p_str := p_str || format_action( p_duty_paid_current_aed_gsi ,sq_len_15, p_action);
1083 p_str := p_str || format_action( p_challan_no_aed_gsi ,sq_len_10, p_action);
1084 p_str := p_str || format_action( to_char(p_challan_date_aed_gsi,'DD/MM/YYYY') ,sq_len_10, p_action);
1085 p_str := p_str || format_action( p_bank_code_aed_gsi ,sq_len_7, p_action);
1086 p_str := p_str || format_action( p_duty_paid_total_aed_gsi ,sq_len_15, p_action);
1087 p_str := p_str || format_action( p_duty_paid_credit_nccd ,sq_len_15, p_action);
1088 p_str := p_str || format_action( p_duty_paid_current_nccd ,sq_len_15, p_action);
1089 p_str := p_str || format_action( p_challan_no_nccd ,sq_len_10, p_action);
1090 p_str := p_str || format_action( to_char(p_challan_date_nccd,'DD/MM/YYYY') ,sq_len_10, p_action);
1091 p_str := p_str || format_action( p_bank_code_nccd ,sq_len_7, p_action);
1092 p_str := p_str || format_action( p_duty_paid_total_nccd ,sq_len_15, p_action);
1093 p_str := p_str || format_action( p_duty_paid_credit_aed_tta ,sq_len_15, p_action);
1094 p_str := p_str || format_action( p_duty_paid_current_aed_tta ,sq_len_15, p_action);
1095 p_str := p_str || format_action( p_challan_no_aed_tta ,sq_len_10, p_action);
1096 p_str := p_str || format_action( to_char(p_challan_date_aed_tta,'DD/MM/YYYY') ,sq_len_10, p_action);
1097 p_str := p_str || format_action( p_bank_code_aed_tta ,sq_len_7, p_action);
1098 p_str := p_str || format_action( p_duty_paid_total_aed_tta ,sq_len_15, p_action);
1099 p_str := p_str || format_action( p_duty_paid_credit_aed_pmt ,sq_len_15, p_action);
1100 p_str := p_str || format_action( p_duty_paid_current_aed_pmt ,sq_len_15, p_action);
1101 p_str := p_str || format_action( p_challan_no_aed_pmt ,sq_len_10, p_action);
1102 p_str := p_str || format_action( to_char(p_challan_date_aed_pmt,'DD/MM/YYYY') ,sq_len_10, p_action);
1103 p_str := p_str || format_action( p_bank_code_aed_pmt ,sq_len_7, p_action);
1104 p_str := p_str || format_action( p_duty_paid_total_aed_pmt ,sq_len_15, p_action);
1105 p_str := p_str || format_action( p_duty_paid_credit_saed ,sq_len_15, p_action);
1106 p_str := p_str || format_action( p_duty_paid_current_saed ,sq_len_15, p_action);
1107 p_str := p_str || format_action( p_challan_no_saed ,sq_len_10, p_action);
1108 p_str := p_str || format_action( to_char(p_challan_date_saed,'DD/MM/YYYY') ,sq_len_10, p_action);
1109 p_str := p_str || format_action( p_bank_code_saed ,sq_len_7, p_action);
1110 p_str := p_str || format_action( p_duty_paid_total_saed ,sq_len_15, p_action);
1111 p_str := p_str || format_action( p_duty_paid_credit_ade ,sq_len_15, p_action);
1112 p_str := p_str || format_action( p_duty_paid_current_ade ,sq_len_15, p_action);
1113 p_str := p_str || format_action( p_challan_no_ade ,sq_len_10, p_action);
1114 p_str := p_str || format_action( to_char(p_challan_date_ade,'DD/MM/YYYY') ,sq_len_10, p_action);
1115 p_str := p_str || format_action( p_bank_code_ade ,sq_len_7, p_action);
1116 p_str := p_str || format_action( p_duty_paid_total_ade ,sq_len_15, p_action);
1117 p_str := p_str || format_action( p_duty_paid_credit_adet ,sq_len_15, p_action);
1118 p_str := p_str || format_action( p_duty_paid_current_adet ,sq_len_15, p_action, lv_last_flag);
1119
1120 UTL_FILE.PUT_LINE(v_filehandle,p_str);
1121 p_str:= NULL;
1122
1123 END create_duty_paid_details;
1124
1125 PROCEDURE create_input_details(
1126 p_record_header varchar2,
1127 p_rt_type varchar2,
1128 p_ecc varchar2,
1129 p_yyyymm number,
1130 p_return_no varchar2,
1131 p_ceth varchar2,
1132 p_ctsh varchar2,
1133 p_uqc varchar2,
1134 p_ln_total_qty_recd number,
1135 p_value_good_recd number,
1136 p_notf_no varchar2,
1137 p_notf_sno varchar2)
1138
1139
1140 IS
1141 BEGIN
1142 p_str := format_action( p_record_header ,sq_len_50, p_action);
1143 p_str := p_str || format_action( p_rt_type ,sq_len_2, p_action);
1144 p_str := p_str || format_action( p_ecc ,sq_len_15, p_action);
1145 p_str := p_str || format_action( p_yyyymm ,sq_len_6, p_action);
1146 p_str := p_str || format_action( p_return_no ,sq_len_3, p_action);
1147 p_str := p_str || format_action( p_ceth ,sq_len_8, p_action);
1148 p_str := p_str || format_action( p_ctsh ,sq_len_8, p_action);
1149 p_str := p_str || format_action( p_uqc ,sq_len_8, p_action);
1150 p_str := p_str || format_action( p_ln_total_qty_recd,sq_len_15, p_action);
1151 p_str := p_str || format_action( p_value_good_recd ,sq_len_15, p_action);
1152 p_str := p_str || format_action( p_notf_no ,sq_len_8, p_action);
1153 p_str := p_str || format_action( p_notf_sno ,sq_len_10, p_action, lv_last_flag);
1154
1155
1156 UTL_FILE.PUT_LINE(v_filehandle,p_str);
1157 p_str:= NULL;
1158
1159 END create_input_details;
1160
1161 PROCEDURE create_cenvat_details(
1162 p_record_header varchar2,
1163 p_rt_type varchar2,
1164 p_ecc varchar2,
1165 p_yyyymm number,
1166 p_return_no varchar2,
1167 p_data_prd_type varchar2,
1168 p_op_bal_cenvat number,
1169 p_credit_input_cenvat number,
1170 p_credit_input_dlr_cenvat number,
1171 p_credit_capital_cenvat number,
1172 p_credit_service_cenvat number,
1173 p_credit_total_cenvat number,
1174 p_credit_utilised_cenvat number,
1175 p_credit_utilised_ic_cenvat number,
1176 p_credit_utilised_ds_cenvat number,
1177 p_clos_bal_cenvat number,
1178 p_op_bal_aed_tta number,
1179 p_credit_input_aed_tta number,
1180 p_credit_input_dlr_aed_tta number,
1181 p_credit_capital_aed_tta number,
1182 p_credit_service_aed_tta number,
1183 p_credit_total_aed_tta number,
1184 p_credit_utilised_aed_tta number,
1185 p_credit_utilised_ic_aed_tta number,
1186 p_credit_utilised_ds_aed_tta number,
1187 p_clos_bal_aed_tta number,
1188 p_op_bal_aed_pmt number,
1189 p_credit_input_aed_pmt number,
1190 p_credit_input_dlr_aed_pmt number,
1191 p_credit_capital_aed_pmt number,
1192 p_credit_service_aed_pmt number,
1193 p_credit_total_aed_pmt number,
1194 p_credit_utilised_aed_pmt number,
1195 p_credit_utilised_ic_aed_pmt number,
1196 p_credit_utilised_ds_aed_pmt number,
1197 p_clos_bal_aed_pmt number,
1198 p_op_bal_nccd number,
1199 p_credit_input_nccd number,
1200 p_credit_input_dlr_nccd number,
1201 p_credit_capital_nccd number,
1202 p_credit_service_nccd number,
1203 p_credit_total_nccd number,
1204 p_credit_utilised_nccd number,
1205 p_credit_utilised_ic_nccd number,
1206 p_credit_utilised_ds_nccd number,
1207 p_clos_bal_nccd number,
1208 p_op_bal_adet number,
1209 p_credit_input_adet number,
1210 p_credit_input_dlr_adet number,
1211 p_credit_capital_adet number,
1212 p_credit_service_adet number,
1213 p_credit_total_adet number,
1214 p_credit_utilised_adet number,
1215 p_credit_utilised_ic_adet number,
1216 p_credit_utilised_ds_adet number,
1217 p_clos_bal_adet number,
1218 p_op_bal_edu_cess number,
1219 p_credit_input_edu_cess number,
1220 p_credit_input_dlr_edu_cess number,
1221 p_credit_capital_edu_cess number,
1222 p_credit_service_edu_cess number,
1223 p_credit_total_edu_cess number,
1224 p_credit_utilised_edu_cess number,
1225 p_credit_utilised_ic_edu_cess number,
1226 p_credit_utilised_ds_edu_cess number,
1227 p_clos_bal_edu_cess number,
1228 p_op_bal_st number,
1229 p_credit_input_st number,
1230 p_credit_input_dlr_st number,
1231 p_credit_capital_st number,
1232 p_credit_service_st number,
1233 p_credit_total_st number,
1234 p_credit_utilised_st number,
1235 p_credit_utilised_ic_st number,
1236 p_credit_utilised_ds_st number,
1237 p_clos_bal_st number,
1238 p_op_bal_st_edu_cess number,
1239 p_credit_input_st_edu_cess number,
1240 p_cre_input_dlr_st_edu_cess number,
1241 p_credit_capital_st_edu_cess number,
1242 p_credit_service_st_edu_cess number,
1243 p_credit_total_st_edu_cess number,
1244 p_creln_dit_uti_st_edu_cess number,
1245 p_credit_uti_ic_st_edu_cess number,
1246 p_credit_uti_ds_st_edu_cess number,
1247 p_clos_bal_st_edu_cess number
1248 )
1249
1250 IS
1251 BEGIN
1252 p_str := format_action( p_record_header ,sq_len_50, p_action);
1253 p_str := p_str || format_action( p_rt_type ,sq_len_2, p_action);
1254 p_str := p_str || format_action( p_ecc ,sq_len_15, p_action);
1255 p_str := p_str || format_action( p_yyyymm ,sq_len_6, p_action);
1256 p_str := p_str || format_action( p_return_no ,sq_len_3, p_action);
1257 p_str := p_str || format_action( p_data_prd_type ,sq_len_2, p_action);
1258 p_str := p_str || format_action( p_op_bal_cenvat ,sq_len_13, p_action);
1259 p_str := p_str || format_action( p_credit_input_cenvat ,sq_len_13, p_action);
1260 p_str := p_str || format_action( p_credit_input_dlr_cenvat ,sq_len_13, p_action);
1261 p_str := p_str || format_action( p_credit_capital_cenvat ,sq_len_13, p_action);
1262 p_str := p_str || format_action( p_credit_service_cenvat ,sq_len_13, p_action);
1263 p_str := p_str || format_action( p_credit_total_cenvat ,sq_len_13, p_action);
1264 p_str := p_str || format_action( p_credit_utilised_cenvat ,sq_len_13, p_action);
1265 p_str := p_str || format_action( p_credit_utilised_ic_cenvat ,sq_len_13, p_action);
1266 p_str := p_str || format_action( p_credit_utilised_ds_cenvat ,sq_len_13, p_action);
1267 p_str := p_str || format_action( p_clos_bal_cenvat ,sq_len_13, p_action);
1268 p_str := p_str || format_action( p_op_bal_aed_tta ,sq_len_12, p_action);
1269 p_str := p_str || format_action( p_credit_input_aed_tta ,sq_len_12, p_action);
1270 p_str := p_str || format_action( p_credit_input_dlr_aed_tta ,sq_len_12, p_action);
1271 p_str := p_str || format_action( p_credit_capital_aed_tta ,sq_len_12, p_action);
1272 p_str := p_str || format_action( p_credit_service_aed_tta ,sq_len_12, p_action);
1273 p_str := p_str || format_action( p_credit_total_aed_tta ,sq_len_12, p_action);
1274 p_str := p_str || format_action( p_credit_utilised_aed_tta ,sq_len_12, p_action);
1275 p_str := p_str || format_action( p_credit_utilised_ic_aed_tta ,sq_len_12, p_action);
1276 p_str := p_str || format_action( p_credit_utilised_ds_aed_tta ,sq_len_12, p_action);
1277 p_str := p_str || format_action( p_clos_bal_aed_tta ,sq_len_12, p_action);
1278 p_str := p_str || format_action( p_op_bal_aed_pmt ,sq_len_12, p_action);
1279 p_str := p_str || format_action( p_credit_input_aed_pmt ,sq_len_12, p_action);
1280 p_str := p_str || format_action( p_credit_input_dlr_aed_pmt ,sq_len_12, p_action);
1281 p_str := p_str || format_action( p_credit_capital_aed_pmt ,sq_len_12, p_action);
1282 p_str := p_str || format_action( p_credit_service_aed_pmt ,sq_len_12, p_action);
1283 p_str := p_str || format_action( p_credit_total_aed_pmt ,sq_len_12, p_action);
1284 p_str := p_str || format_action( p_credit_utilised_aed_pmt ,sq_len_12, p_action);
1285 p_str := p_str || format_action( p_credit_utilised_ic_aed_pmt ,sq_len_12, p_action);
1286 p_str := p_str || format_action( p_credit_utilised_ds_aed_pmt ,sq_len_12, p_action);
1287 p_str := p_str || format_action( p_clos_bal_aed_pmt ,sq_len_12, p_action);
1291 p_str := p_str || format_action( p_credit_capital_nccd ,sq_len_12, p_action);
1288 p_str := p_str || format_action( p_op_bal_nccd ,sq_len_12, p_action);
1289 p_str := p_str || format_action( p_credit_input_nccd ,sq_len_12, p_action);
1290 p_str := p_str || format_action( p_credit_input_dlr_nccd ,sq_len_12, p_action);
1292 p_str := p_str || format_action( p_credit_service_nccd ,sq_len_12, p_action);
1293 p_str := p_str || format_action( p_credit_total_nccd ,sq_len_12, p_action);
1294 p_str := p_str || format_action( p_credit_utilised_nccd ,sq_len_12, p_action);
1295 p_str := p_str || format_action( p_credit_utilised_ic_nccd ,sq_len_12, p_action);
1296 p_str := p_str || format_action( p_credit_utilised_ds_nccd ,sq_len_12, p_action);
1297 p_str := p_str || format_action( p_clos_bal_nccd ,sq_len_12, p_action);
1298 p_str := p_str || format_action( p_op_bal_adet ,sq_len_12, p_action);
1299 p_str := p_str || format_action( p_credit_input_adet ,sq_len_12, p_action);
1300 p_str := p_str || format_action( p_credit_input_dlr_adet ,sq_len_12, p_action);
1301 p_str := p_str || format_action( p_credit_capital_adet ,sq_len_12, p_action);
1302 p_str := p_str || format_action( p_credit_service_adet ,sq_len_12, p_action);
1303 p_str := p_str || format_action( p_credit_total_adet ,sq_len_12, p_action);
1304 p_str := p_str || format_action( p_credit_utilised_adet ,sq_len_12, p_action);
1305 p_str := p_str || format_action( p_credit_utilised_ic_adet ,sq_len_12, p_action);
1306 p_str := p_str || format_action( p_credit_utilised_ds_adet ,sq_len_12, p_action);
1307 p_str := p_str || format_action( p_clos_bal_adet ,sq_len_12, p_action);
1308 p_str := p_str || format_action( p_op_bal_edu_cess ,sq_len_12, p_action);
1309 p_str := p_str || format_action( p_credit_input_edu_cess ,sq_len_12, p_action);
1310 p_str := p_str || format_action( p_credit_input_dlr_edu_cess ,sq_len_12, p_action);
1311 p_str := p_str || format_action( p_credit_capital_edu_cess ,sq_len_12, p_action);
1312 p_str := p_str || format_action( p_credit_service_edu_cess ,sq_len_12, p_action);
1313 p_str := p_str || format_action( p_credit_total_edu_cess ,sq_len_12, p_action);
1314 p_str := p_str || format_action( p_credit_utilised_edu_cess ,sq_len_12, p_action);
1315 p_str := p_str || format_action( p_credit_utilised_ic_edu_cess ,sq_len_12, p_action);
1316 p_str := p_str || format_action( p_credit_utilised_ds_edu_cess ,sq_len_12, p_action);
1317 p_str := p_str || format_action( p_clos_bal_edu_cess ,sq_len_12, p_action);
1318 p_str := p_str || format_action( p_op_bal_st ,sq_len_12, p_action);
1319 p_str := p_str || format_action( p_credit_input_st ,sq_len_12, p_action);
1320 p_str := p_str || format_action( p_credit_input_dlr_st ,sq_len_12, p_action);
1321 p_str := p_str || format_action( p_credit_capital_st ,sq_len_12, p_action);
1322 p_str := p_str || format_action( p_credit_service_st ,sq_len_12, p_action);
1323 p_str := p_str || format_action( p_credit_total_st ,sq_len_12, p_action);
1324 p_str := p_str || format_action( p_credit_utilised_st ,sq_len_12, p_action);
1325 p_str := p_str || format_action( p_credit_utilised_ic_st ,sq_len_12, p_action);
1326 p_str := p_str || format_action( p_credit_utilised_ds_st ,sq_len_12, p_action);
1327 p_str := p_str || format_action( p_clos_bal_st ,sq_len_12, p_action);
1328 p_str := p_str || format_action( p_op_bal_st_edu_cess ,sq_len_12, p_action);
1329 p_str := p_str || format_action( p_credit_input_st_edu_cess ,sq_len_12, p_action);
1330 p_str := p_str || format_action( p_cre_input_dlr_st_edu_cess ,sq_len_12, p_action);
1331 p_str := p_str || format_action( p_credit_capital_st_edu_cess ,sq_len_12, p_action);
1332 p_str := p_str || format_action( p_credit_service_st_edu_cess ,sq_len_12, p_action);
1333 p_str := p_str || format_action( p_credit_total_st_edu_cess ,sq_len_12, p_action);
1334 p_str := p_str || format_action( p_creln_dit_uti_st_edu_cess ,sq_len_12, p_action);
1335 p_str := p_str || format_action( p_credit_uti_ic_st_edu_cess ,sq_len_12, p_action);
1336 p_str := p_str || format_action( p_credit_uti_ds_st_edu_cess ,sq_len_12, p_action);
1337 p_str := p_str || format_action( p_clos_bal_st_edu_cess ,sq_len_12, p_action, lv_last_flag);
1338
1339
1340
1341 UTL_FILE.PUT_LINE(v_filehandle,p_str);
1342 p_str:= NULL;
1343
1344 END create_cenvat_details;
1345
1346 PROCEDURE create_payment_details(
1347 p_record_header varchar2,
1348 p_rt_type varchar2,
1349 p_ecc varchar2,
1350 p_yyyymm number,
1351 p_return_no varchar2,
1352 p_arrear_rule8_current number,
1353 p_arrear_rule8_credit number,
1354 p_arrear_rule8_challan_no varchar2,
1355 p_arrear_rule8_challan_date date,
1356 p_arrear_rule8_bank_code varchar2,
1357 p_arrear_rule8_source_no varchar2,
1358 p_arrear_rule8_source_date date,
1359 p_arrear_current number,
1360 p_arrear_credit number,
1361 p_arrear_challan_no varchar2,
1362 p_arrear_challan_date date,
1363 p_arrear_bank_code varchar2,
1364 p_arrear_source_no varchar2,
1365 p_arrear_source_date date,
1366 p_int_rule8_current number,
1367 p_int_rule8_credit number,
1368 p_int_rule8_challan_no varchar2,
1369 p_int_rule8_challan_date date,
1370 p_int_rule8_bank_code varchar2,
1371 p_int_rule8_source_no varchar2,
1372 p_int_rule8_source_date date,
1373 p_int_current number,
1374 p_int_credit number,
1375 p_int_challan_no varchar2,
1376 p_int_challan_date date,
1377 p_int_bank_code varchar2,
1378 p_int_source_no varchar2,
1379 p_int_source_date date,
1380 p_misc_current number,
1381 p_misc_credit number,
1382 p_misc_challan_no varchar2,
1383 p_misc_challan_date date,
1384 p_misc_bank_code varchar2,
1385 p_misc_source_no varchar2,
1386 p_misc_source_date date
1387 )
1388
1389 IS
1390 BEGIN
1391 p_str := format_action( p_record_header ,sq_len_50, p_action);
1392 p_str := p_str || format_action( p_rt_type ,sq_len_2, p_action);
1393 p_str := p_str || format_action( p_ecc ,sq_len_15, p_action);
1394 p_str := p_str || format_action( p_yyyymm ,sq_len_6, p_action);
1395 p_str := p_str || format_action( p_return_no ,sq_len_3, p_action);
1396 p_str := p_str || format_action( p_arrear_rule8_current ,sq_len_13, p_action);
1397 p_str := p_str || format_action( p_arrear_rule8_credit ,sq_len_13, p_action);
1398 p_str := p_str || format_action( p_arrear_rule8_challan_no ,sq_len_10, p_action);
1399 p_str := p_str || format_action( to_char(p_arrear_rule8_challan_date, 'DD/MM/YYYY'),sq_len_10, p_action);
1400 p_str := p_str || format_action( p_arrear_rule8_bank_code ,sq_len_7, p_action);
1401 p_str := p_str || format_action( p_arrear_rule8_source_no ,sq_len_40, p_action);
1402 p_str := p_str || format_action( to_char(p_arrear_rule8_source_date, 'DD/MM/YYYY') ,sq_len_10, p_action);
1403 p_str := p_str || format_action( p_arrear_current ,sq_len_13, p_action);
1404 p_str := p_str || format_action( p_arrear_credit ,sq_len_13, p_action);
1405 p_str := p_str || format_action( p_arrear_challan_no ,sq_len_10, p_action);
1406 p_str := p_str || format_action( to_char(p_arrear_challan_date, 'DD/MM/YYYY') ,sq_len_10, p_action);
1407 p_str := p_str || format_action( p_arrear_bank_code ,sq_len_7, p_action);
1408 p_str := p_str || format_action( p_arrear_source_no ,sq_len_40, p_action);
1409 p_str := p_str || format_action( to_char(p_arrear_source_date, 'DD/MM/YYYY') ,sq_len_10, p_action);
1410 p_str := p_str || format_action( p_int_rule8_current ,sq_len_13, p_action);
1411 p_str := p_str || format_action( p_int_rule8_credit ,sq_len_13, p_action);
1412 p_str := p_str || format_action( p_int_rule8_challan_no ,sq_len_10, p_action);
1413 p_str := p_str || format_action( to_char(p_int_rule8_challan_date, 'DD/MM/YYYY') ,sq_len_10, p_action);
1414 p_str := p_str || format_action( p_int_rule8_bank_code ,sq_len_7, p_action);
1415 p_str := p_str || format_action( p_int_rule8_source_no ,sq_len_40, p_action);
1416 p_str := p_str || format_action( to_char(p_int_rule8_source_date, 'DD/MM/YYYY') ,sq_len_10, p_action);
1417 p_str := p_str || format_action( p_int_current ,sq_len_13, p_action);
1418 p_str := p_str || format_action( p_int_credit ,sq_len_13, p_action);
1419 p_str := p_str || format_action( p_int_challan_no ,sq_len_10, p_action);
1420 p_str := p_str || format_action( to_char(p_int_challan_date, 'DD/MM/YYYY') ,sq_len_10, p_action);
1421 p_str := p_str || format_action( p_int_bank_code ,sq_len_7, p_action);
1422 p_str := p_str || format_action( p_int_source_no ,sq_len_40, p_action);
1423 p_str := p_str || format_action( to_char(p_int_source_date, 'DD/MM/YYYY') ,sq_len_10, p_action);
1424 p_str := p_str || format_action( p_int_rule8_source_date ,sq_len_10, p_action);
1425 p_str := p_str || format_action( p_misc_current ,sq_len_13, p_action);
1426 p_str := p_str || format_action( p_misc_credit ,sq_len_13, p_action);
1427 p_str := p_str || format_action( p_misc_challan_no ,sq_len_10, p_action);
1428 p_str := p_str || format_action( to_char(p_misc_challan_date, 'DD/MM/YYYY') ,sq_len_10, p_action);
1429 p_str := p_str || format_action( p_misc_bank_code ,sq_len_7, p_action);
1430 p_str := p_str || format_action( p_misc_source_no ,sq_len_40, p_action);
1431 p_str := p_str || format_action( to_char(p_misc_source_date, 'DD/MM/YYYY') ,sq_len_10, p_action, lv_last_flag);
1432
1433
1434 UTL_FILE.PUT_LINE(v_filehandle,p_str);
1435 p_str:= NULL;
1436
1437 END create_payment_details;
1438
1439
1440 PROCEDURE create_sam_details(
1441 p_record_header varchar2,
1442 p_rt_type varchar2,
1443 p_ecc varchar2,
1444 p_yyyymm number,
1445 p_return_no varchar2,
1446 p_tr6_total_amount number,
1447 p_inv_issue_from1 varchar2,
1448 p_inv_issue_to1 varchar2,
1449 p_inv_issue_from2 varchar2,
1450 p_inv_issue_to2 varchar2,
1451 p_inv_issue_from3 varchar2,
1452 p_inv_issue_to3 varchar2,
1453 p_inv_issue_from4 varchar2,
1454 p_inv_issue_to4 varchar2,
1455 p_inv_issue_from5 varchar2,
1456 p_inv_issue_to5 varchar2,
1457 p_inv_issue_from6 varchar2,
1458 p_inv_issue_to6 varchar2,
1459 p_remarks varchar2,
1460 p_place varchar2,
1461 p_date_filing date,
1462 p_name_auth_sign varchar2
1463 )
1464
1465 IS
1466 BEGIN
1467 p_str := format_action( p_record_header ,sq_len_50, p_action);
1468 p_str := p_str || format_action( p_rt_type ,sq_len_2, p_action);
1469 p_str := p_str || format_action( p_ecc ,sq_len_15, p_action);
1470 p_str := p_str || format_action( p_yyyymm ,sq_len_6, p_action);
1471 p_str := p_str || format_action( p_return_no ,sq_len_3, p_action);
1472 p_str := p_str || format_action( p_tr6_total_amount ,sq_len_15, p_action);
1473 p_str := p_str || format_action( p_inv_issue_from1 ,sq_len_10, p_action);
1474 p_str := p_str || format_action( p_inv_issue_to1 ,sq_len_10, p_action);
1475 p_str := p_str || format_action( p_inv_issue_from2 ,sq_len_10, p_action);
1476 p_str := p_str || format_action( p_inv_issue_to2 ,sq_len_10, p_action);
1477 p_str := p_str || format_action( p_inv_issue_from3 ,sq_len_10, p_action);
1478 p_str := p_str || format_action( p_inv_issue_to3 ,sq_len_10, p_action);
1479 p_str := p_str || format_action( p_inv_issue_from4 ,sq_len_10, p_action);
1480 p_str := p_str || format_action( p_inv_issue_to4 ,sq_len_10, p_action);
1481 p_str := p_str || format_action( p_inv_issue_from5 ,sq_len_10, p_action);
1482 p_str := p_str || format_action( p_inv_issue_to5 ,sq_len_10, p_action);
1483 p_str := p_str || format_action( p_inv_issue_from6 ,sq_len_10, p_action);
1484 p_str := p_str || format_action( p_inv_issue_to6 ,sq_len_10, p_action);
1485 p_str := p_str || format_action( p_remarks ,sq_len_255,p_action);
1486 p_str := p_str || format_action( p_place ,sq_len_20, p_action);
1487 p_str := p_str || format_action( to_char(p_date_filing,'DD/MM/YYYY') ,sq_len_10, p_action);
1488 p_str := p_str || format_action( p_name_auth_sign ,sq_len_40, p_action, lv_last_flag);
1489
1490
1491 UTL_FILE.PUT_LINE(v_filehandle,p_str);
1492 p_str:= NULL;
1493
1494 END create_sam_details;
1495
1496
1497 --procedure to populate the data for DUTY PAID DETAILS
1498
1499
1500 PROCEDURE populate_duty_paid_details (
1501 p_end_date IN DATE,
1502 p_location_id IN NUMBER,
1503 p_organization_id IN NUMBER,
1504 p_start_date IN DATE )
1505
1506 IS
1507
1508 lv_record_header varchar2(50);
1509 lv_rt_type varchar2(2);
1510 p_ecc varchar2(15);
1511 ln_yyyymm number;
1512 lv_return_no varchar2(3);
1513 lv_data_prd_type varchar2(2);
1514 ln_duty_paid_credit_cenvat number;
1515 ln_duty_paid_current_cenvat number;
1516 lv_challan_no_cenvat varchar2(10);
1517 ld_challan_date_cenvat date;
1518 lv_bank_code_cenvat varchar2(7);
1519 ln_duty_paid_total_cenvat number;
1520 ln_duty_paid_credit_sed number;
1521 ln_duty_paid_current_sed number;
1522 lv_challan_no_sed varchar2(10);
1523 ld_challan_date_sed date;
1524 lv_bank_code_sed varchar2(7);
1525 ln_duty_paid_total_sed number;
1526 ln_duty_paid_credit_aed_gsi number;
1527 ln_duty_paid_current_aed_gsi number;
1528 lv_challan_no_aed_gsi varchar2(10);
1529 ld_challan_date_aed_gsi date;
1530 lv_bank_code_aed_gsi varchar2(7);
1531 ln_duty_paid_total_aed_gsi number;
1532 ln_duty_paid_credit_nccd number;
1533 ln_duty_paid_current_nccd number;
1534 lv_challan_no_nccd varchar2(10);
1535 ld_challan_date_nccd date;
1536 lv_bank_code_nccd varchar2(7);
1537 ln_duty_paid_total_nccd number;
1538 ln_duty_paid_credit_aed_tta number;
1539 ln_duty_paid_current_aed_tta number;
1540 lv_challan_no_aed_tta varchar2(10);
1541 ld_challan_date_aed_tta date;
1542 lv_bank_code_aed_tta varchar2(7);
1543 ln_duty_paid_total_aed_tta number;
1544 ln_duty_paid_credit_aed_pmt number;
1545 ln_duty_paid_current_aed_pmt number;
1546 lv_challan_no_aed_pmt varchar2(10);
1547 ld_challan_date_aed_pmt date;
1548 lv_bank_code_aed_pmt varchar2(7);
1549 ln_duty_paid_total_aed_pmt number;
1550 ln_duty_paid_credit_saed number;
1551 ln_duty_paid_current_saed number;
1552 lv_challan_no_saed varchar2(10);
1553 ld_challan_date_saed date;
1554 lv_bank_code_saed varchar2(7);
1555 ln_duty_paid_total_saed number;
1556 ln_duty_paid_credit_ade number;
1557 ln_duty_paid_current_ade number;
1558 lv_challan_no_ade varchar2(10);
1559 ld_challan_date_ade date;
1560 lv_bank_code_ade varchar2(7);
1561 ln_duty_paid_total_ade number;
1562 ln_duty_paid_credit_adet number;
1563 ln_duty_paid_current_adet number;
1564
1565 -- Cursor for registration number
1566
1567 CURSOR get_pla_amount
1568 IS
1569 SELECT round(nvl(sum(nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)),0),0)
1570 FROM JAI_CMN_RG_PLA_TRXS
1571 WHERE organization_id = p_organization_id
1572 AND location_id = p_location_id
1573 AND trunc(creation_date) >= p_start_date
1574 AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
1575 AND TRANSACTION_SOURCE_NUM = 91
1576 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm ;
1577
1578 CURSOR cur_other_credit
1579 IS
1580 SELECT round(sum(nvl(debit,0)),0)
1581 FROM JAI_CMN_RG_OTHERS
1582 WHERE tax_type in ( jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess )
1583 AND source_type = 1
1584 AND source_register_id in
1585 ( SELECT register_id
1586 FROM JAI_CMN_RG_23AC_II_TRXS
1587 WHERE location_id = P_Location_id
1588 AND organization_id = p_Organization_id
1589 AND trunc(creation_date) >= p_start_date
1590 AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
1591 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
1592 );
1593
1594 CURSOR cur_other_current IS
1595 SELECT round(SUM(nvl(credit,0)),0)
1596 FROM JAI_CMN_RG_OTHERS
1597 WHERE source_type=2
1598 AND tax_type in ( jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess )
1599 AND source_register_id in ( SELECT register_id
1600 FROM JAI_CMN_RG_PLA_TRXS
1601 WHERE organization_id = p_organization_id
1602 AND location_id = p_location_id
1603 AND trunc(creation_date) >= p_start_date
1604 AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
1605 AND TRANSACTION_SOURCE_NUM = 91
1606 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
1607 );
1608
1609
1610 CURSOR cur_dtls(
1611 p_location_id IN NUMBER,
1612 p_organization_id IN NUMBER,
1613 p_start_date IN DATE,
1614 p_end_date IN DATE)
1615 IS
1616 SELECT ROUND(SUM(nvl(dr_basic_ed,0) + nvl(dr_additional_ed,0) + nvl(dr_other_ed,0)), 0) credit_utilized,
1617 to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') year_month
1618 FROM JAI_CMN_RG_23AC_II_TRXS
1619 WHERE location_id = p_location_id
1620 AND organization_id = p_organization_id
1621 AND trunc(creation_date) >= p_start_date
1622 AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
1623 group by
1624 to_char(creation_date, 'MM'),
1625 to_char(creation_date, 'YYYY')
1626 ORDER BY
1627 to_char(creation_date, 'YYYY'),
1628 to_char(creation_date, 'MM') ;
1629
1630 BEGIN
1631
1632 lv_record_header := 'DUTY_PAID_DETAIL' ;
1633 lv_rt_type := 1 ;
1634 lv_return_no := 1 ;
1635 lv_data_prd_type := 'M' ;
1636
1637 FOR dtl IN cur_dtls(p_location_id, p_organization_id, p_start_date, p_end_date)
1638
1639 LOOP
1640 ln_duty_paid_credit_cenvat := NULL;
1641 ln_duty_paid_current_cenvat := NULL;
1642 ln_duty_paid_total_cenvat := NULL;
1643 ln_yyyymm := NULL;
1644 ln_duty_paid_current_aed_gsi := NULL;
1645 ln_duty_paid_credit_aed_gsi := NULL;
1646 ln_duty_paid_total_aed_gsi := NULL;
1647
1648 ln_duty_paid_credit_cenvat := dtl.credit_utilized; -- for credit account (CENVAT)
1649 ln_yyyymm := dtl.year_month ;
1650
1651 OPEN get_pla_amount;
1652 FETCH get_pla_amount INTO ln_duty_paid_current_cenvat; -- for current account(CENVAT)
1653 CLOSE get_pla_amount;
1654
1655 ln_duty_paid_total_cenvat := round(nvl(ln_duty_paid_credit_cenvat,0) + nvl(ln_duty_paid_current_cenvat, 0)) ;
1656
1657 OPEN cur_other_credit;
1658 FETCH cur_other_credit INTO ln_duty_paid_credit_aed_gsi;
1659 CLOSE cur_other_credit;
1660
1661 OPEN cur_other_current;
1662 FETCH cur_other_current INTO ln_duty_paid_current_aed_gsi;
1663 CLOSE cur_other_current;
1664
1665 ln_duty_paid_total_aed_gsi := round(nvl(ln_duty_paid_credit_aed_gsi,0) + nvl(ln_duty_paid_current_aed_gsi, 0)) ;
1666
1667 create_duty_paid_details (
1668 p_record_header => lv_record_header,
1669 p_rt_type => lv_rt_type,
1670 p_ecc => lv_ecc ,
1671 p_yyyymm => ln_yyyymm,
1672 p_return_no => lv_return_no,
1673 p_data_prd_type => lv_data_prd_type,
1674 p_duty_paid_credit_cenvat => ln_duty_paid_credit_cenvat,
1675 p_duty_paid_current_cenvat => ln_duty_paid_current_cenvat,
1676 p_challan_no_cenvat => lv_challan_no_cenvat,
1677 p_challan_date_cenvat => ld_challan_date_cenvat,
1678 p_bank_code_cenvat => lv_bank_code_cenvat,
1679 p_duty_paid_total_cenvat => ln_duty_paid_total_cenvat,
1680 p_duty_paid_credit_sed => ln_duty_paid_credit_sed,
1681 p_duty_paid_current_sed => ln_duty_paid_current_sed,
1682 p_challan_no_sed => lv_challan_no_sed,
1683 p_challan_date_sed => ld_challan_date_sed,
1684 p_bank_code_sed => lv_bank_code_sed,
1685 p_duty_paid_total_sed => ln_duty_paid_total_sed,
1686 p_duty_paid_credit_aed_gsi => ln_duty_paid_credit_aed_gsi,
1687 p_duty_paid_current_aed_gsi => ln_duty_paid_current_aed_gsi,
1688 p_challan_no_aed_gsi => lv_challan_no_aed_gsi,
1689 p_challan_date_aed_gsi => ld_challan_date_aed_gsi,
1690 p_bank_code_aed_gsi => lv_bank_code_aed_gsi,
1691 p_duty_paid_total_aed_gsi => ln_duty_paid_total_aed_gsi,
1692 p_duty_paid_credit_nccd => ln_duty_paid_credit_nccd,
1693 p_duty_paid_current_nccd => ln_duty_paid_current_nccd,
1694 p_challan_no_nccd => lv_challan_no_nccd,
1695 p_challan_date_nccd => ld_challan_date_nccd,
1696 p_bank_code_nccd => lv_bank_code_nccd,
1697 p_duty_paid_total_nccd => ln_duty_paid_total_nccd,
1698 p_duty_paid_credit_aed_tta => ln_duty_paid_credit_aed_tta,
1699 p_duty_paid_current_aed_tta => ln_duty_paid_current_aed_tta,
1700 p_challan_no_aed_tta => lv_challan_no_aed_tta,
1701 p_challan_date_aed_tta => ld_challan_date_aed_tta,
1702 p_bank_code_aed_tta => lv_bank_code_aed_tta,
1703 p_duty_paid_total_aed_tta => ln_duty_paid_total_aed_tta,
1704 p_duty_paid_credit_aed_pmt => ln_duty_paid_credit_aed_pmt,
1705 p_duty_paid_current_aed_pmt => ln_duty_paid_current_aed_pmt,
1706 p_challan_no_aed_pmt => lv_challan_no_aed_pmt,
1707 p_challan_date_aed_pmt => ld_challan_date_aed_pmt,
1708 p_bank_code_aed_pmt => lv_bank_code_aed_pmt,
1709 p_duty_paid_total_aed_pmt => ln_duty_paid_total_aed_pmt,
1710 p_duty_paid_credit_saed => ln_duty_paid_credit_saed,
1711 p_duty_paid_current_saed => ln_duty_paid_current_saed,
1712 p_challan_no_saed => lv_challan_no_saed,
1713 p_challan_date_saed => ld_challan_date_saed,
1714 p_bank_code_saed => lv_bank_code_saed,
1715 p_duty_paid_total_saed => ln_duty_paid_total_saed,
1716 p_duty_paid_credit_ade => ln_duty_paid_credit_ade,
1717 p_duty_paid_current_ade => ln_duty_paid_current_ade,
1718 p_challan_no_ade => lv_challan_no_ade,
1719 p_challan_date_ade => ld_challan_date_ade,
1720 p_bank_code_ade => lv_bank_code_ade,
1721 p_duty_paid_total_ade => ln_duty_paid_total_ade,
1722 p_duty_paid_credit_adet => ln_duty_paid_credit_adet,
1723 p_duty_paid_current_adet => ln_duty_paid_current_adet); -- procedure for formatting and adding the value in flat file
1724
1725 END LOOP;
1726
1727 END populate_duty_paid_details;
1728
1729
1730
1731
1732 -- to populate ceth wise details
1733
1734 PROCEDURE populate_ceth_wise_details( p_organization_id IN NUMBER,
1735 p_location_id IN NUMBER,
1736 p_start_date IN DATE,
1737 p_end_date IN DATE
1738 )
1739
1740 IS
1741 lv_record_header varchar2(50);
1742 lv_rt_type varchar2(2);
1743 p_ecc varchar2(15);
1744 ln_yyyymm number;
1745 lv_return_no varchar2(3);
1746 lv_data_prd_type varchar2(2);
1747 lv_ceth varchar2(8);
1748 lv_ctsh varchar2(8);
1749 lv_uqc varchar2(8);
1750 ln_qty_mnf number;
1751 lv_qty_clr_type varchar2(15);
1752 ln_qty_clr number;
1753 ln_ass_val number;
1754 lv_notf_no_1 varchar2(8);
1755 lv_notf_sno_1 varchar2(10);
1756 lv_notf_no_2 varchar2(8);
1757 lv_notf_sno_2 varchar2(10);
1758 lv_notf_no_3 varchar2(8);
1759 lv_notf_sno_3 varchar2(10);
1760 lv_notf_no_4 varchar2(8);
1761 lv_notf_sno_4 varchar2(10);
1762 lv_notf_no_5 varchar2(8);
1763 lv_notf_sno_5 varchar2(10);
1764 lv_notf_no_6 varchar2(8);
1765 lv_notf_sno_6 varchar2(10);
1766 ln_duty_rate_adv_cenvat number ;
1767 ln_duty_rate_sp_cenvat number ;
1768 ln_duty_payable_cenvat number ;
1769 ln_pa_no_cenvat number ;
1770 ln_duty_rate_adv_sed number ;
1771 ln_duty_rate_sp_sed number ;
1772 ln_duty_payable_sed number ;
1773 ln_pa_no_sed number ;
1774 ln_duty_rate_adv_aed_gsi number ;
1775 ln_duty_rate_sp_aed_gsi number ;
1776 ln_duty_payable_aed_gsi number ;
1777 ln_pa_no_aed_gsi number ;
1778 ln_duty_rate_adv_nccd number ;
1779 ln_duty_rate_sp_nccd number ;
1780 ln_duty_payable_nccd number ;
1781 ln_pa_no_nccd number ;
1782 ln_duty_rate_adv_aed_tta number ;
1783 ln_duty_rate_sp_aed_tta number ;
1784 ln_duty_payable_aed_tta number ;
1785 ln_pa_no_aed_tta number ;
1786 ln_duty_rate_adv_aed_pmt number ;
1787 ln_duty_rate_sp_aed_pmt number ;
1788 ln_duty_payable_aed_pmt number ;
1789 ln_pa_no_aed_pmt number ;
1790 ln_duty_rate_adv_saed number ;
1791 ln_duty_rate_sp_saed number ;
1792 ln_duty_payable_saed number ;
1793 ln_pa_no_saed number ;
1794 ln_duty_rate_adv_ade number ;
1795 ln_duty_rate_sp_ade number ;
1796 ln_duty_payable_ade number ;
1797 ln_pa_no_ade number ;
1798 ln_duty_rate_adv_adet number ;
1799 ln_duty_rate_sp_adet number ;
1800 ln_duty_payable_adet number ;
1801 ln_pa_no_adet number ;
1802 ln_duty_rate_adv_cess number ;
1803 ln_duty_rate_sp_cess number ;
1804 ln_duty_payable_cess number ;
1805 ln_pa_no_cess number ;
1806 ln_duty_rate_adv_edu_cess number ;
1807 ln_duty_rate_sp_edu_cess number ;
1808 ln_duty_payable_edu_cess number ;
1809 ln_pa_no_edu_cess number ;
1810 ln_pla_duty NUMBER ;
1811 ln_rg23_duty NUMBER ;
1812
1813
1814 CURSOR Cur_item_desc( p_inventory_item_id IN JAI_CMN_RG_I_TRXS.inventory_item_id%type)
1815 IS
1816 SELECT MSI.description
1817 FROM mtl_system_items MSI
1818 WHERE MSI.inventory_item_id = p_inventory_item_id
1819 AND MSI.organization_id = p_organization_id;
1820
1821
1822 -- Cursor for quantity manufactured
1823 CURSOR cur_qty_mftrd(
1824 p_inventory_item_id IN JAI_CMN_RG_I_TRXS.inventory_item_id%type,
1825 p_excise_duty_rate IN NUMBER,
1826 p_cetsh IN JAI_INV_ITM_SETUPS.item_tariff%type,
1827 p_units IN JAI_CMN_RG_I_TRXS.primary_uom_code%type)
1828
1829 IS
1830 SELECT sum( NVL(MANUFACTURED_LOOSE_QTY,0)+
1831 NVL(FOR_HOME_USE_PAY_ED_QTY,0)+
1832 NVL(FOR_EXPORT_PAY_ED_QTY,0)+
1833 NVL(FOR_EXPORT_N_PAY_ED_QTY,0)+
1834 NVL(TO_OTHER_FACTORY_N_PAY_ED_QTY,0)+
1835 NVL(OTHER_PURPOSE_N_PAY_ED_QTY,0)+
1836 NVL(OTHER_PURPOSE_PAY_ED_QTY,0)) QTY_MANUFACTURED
1837 FROM JAI_CMN_RG_I_TRXS jrgi,
1838 JAI_INV_ITM_SETUPS items
1839 WHERE jrgi.transaction_type in ( 'R','PR','RA','IOR','CR')
1840 AND (jrgi.inventory_item_id = p_inventory_item_id
1841 OR nvl(items.item_tariff,'xyz') = nvl(p_cetsh,'xyz'))
1842 AND items.inventory_item_id = jrgi.inventory_item_id
1843 AND jrgi.organization_id = p_organization_id
1844 AND items.organization_id = jrgi.organization_id
1845 AND nvl(jrgi.primary_uom_code,'XYZ') = nvl(p_units,'XYZ')
1846 AND nvl(round(jrgi.excise_duty_rate,0),-999.95) = nvl(p_excise_duty_rate,-999.95)
1847 AND jrgi.location_id = p_location_id
1848 AND trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
1849 AND to_char(jrgi.creation_date, 'YYYY') || to_char(jrgi.creation_date, 'MM') = ln_yyyymm ;
1850
1851
1852 -- Cursor for quantity cleared
1853 CURSOR cur_qty_clrd(
1854 p_inventory_item_id IN JAI_CMN_RG_I_TRXS.inventory_item_id%type,
1855 p_excise_duty_rate IN NUMBER,
1856 p_cetsh IN JAI_INV_ITM_SETUPS.item_tariff%type,
1857 p_units IN JAI_CMN_RG_I_TRXS.primary_uom_code%type)
1858
1859 IS
1860 SELECT sum( NVL(MANUFACTURED_LOOSE_QTY,0)+
1861 NVL(FOR_HOME_USE_PAY_ED_QTY,0)+
1862 NVL(FOR_EXPORT_PAY_ED_QTY,0)+
1863 NVL(FOR_EXPORT_N_PAY_ED_QTY,0)+
1864 NVL(TO_OTHER_FACTORY_N_PAY_ED_QTY,0)+
1865 NVL(OTHER_PURPOSE_N_PAY_ED_QTY,0)+
1866 NVL(OTHER_PURPOSE_PAY_ED_QTY,0)) QTY_MANUFACTURED
1867 FROM JAI_CMN_RG_I_TRXS jrgi,JAI_INV_ITM_SETUPS items
1868 WHERE jrgi.transaction_type in ( 'I','IA','PI','IOI')
1869 AND ( jrgi.inventory_item_id = p_inventory_item_id
1870 OR nvl(items.item_tariff,'xyz') = nvl(p_cetsh,'xyz'))
1871 AND items.inventory_item_id = jrgi.inventory_item_id
1872 AND jrgi.organization_id = p_organization_id
1873 AND items.organization_id = jrgi.organization_id
1874 AND nvl(round(jrgi.excise_duty_rate,0),-999.95) = nvl(p_excise_duty_rate,-999.95)
1875 AND nvl(jrgi.primary_uom_code,'xyz') = nvl(p_units,'xyz')
1876 AND jrgi.location_id = p_location_id
1877 AND trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
1878 AND to_char(jrgi.creation_date, 'YYYY') || to_char(jrgi.creation_date, 'MM') = ln_yyyymm ;
1879
1880
1881 -- Cursor for cenvat duty payable
1882 CURSOR cur_duty_payable(
1883 p_inventory_item_id IN JAI_CMN_RG_I_TRXS.inventory_item_id%type,
1884 p_excise_duty_rate IN NUMBER,
1885 p_cetsh IN JAI_INV_ITM_SETUPS.item_tariff%type,
1886 p_units IN JAI_CMN_RG_I_TRXS.primary_uom_code%type)
1887 IS
1888 SELECT round(sum( NVL(jrgi.basic_ed,0 ) + NVL(jrgi.additional_ed,0) + NVL(jrgi.other_ed,0) ),0) Duty_payable
1889 FROM JAI_CMN_RG_I_TRXS jrgi,JAI_INV_ITM_SETUPS items
1890 WHERE jrgi.transaction_type in ( 'I','PI','IA','IOI')
1891 AND ( jrgi.inventory_item_id = p_inventory_item_id
1892 OR items.item_tariff = p_cetsh)
1893 AND items.inventory_item_id = jrgi.inventory_item_id
1894 AND jrgi.organization_id = p_organization_id
1895 AND items.organization_id = jrgi.organization_id
1896 AND nvl(jrgi.primary_uom_code,'XYZ') = nvl(p_units,'XYZ')
1897 AND nvl(round(jrgi.excise_duty_rate,0),-999.95) = nvl(p_excise_duty_rate,-999.95)
1898 AND jrgi.location_id = p_location_id
1899 AND trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
1900 AND to_char(jrgi.creation_date, 'YYYY') || to_char(jrgi.creation_date, 'MM') = ln_yyyymm ;
1901
1902 -- Cursor for Duty Payable(CESS) and Duty Payable(EDU.CESS)
1903 CURSOR cur_other_duties_PLA(
1904 p_inventory_item_id IN JAI_CMN_RG_I_TRXS.inventory_item_id%type,
1905 p_excise_duty_rate IN NUMBER,
1906 p_cetsh IN JAI_INV_ITM_SETUPS.item_tariff%type,
1907 p_units IN JAI_CMN_RG_I_TRXS.primary_uom_code%type
1908 )
1909 IS
1910 SELECT nvl(sum(debit),0) FROM JAI_CMN_RG_OTHERS
1911 WHERE source_register_id IN(
1912 SELECT register_id_part_ii
1913 FROM JAI_CMN_RG_I_TRXS jrgi,JAI_INV_ITM_SETUPS items
1914 WHERE ( jrgi.inventory_item_id = p_inventory_item_id
1915 OR items.item_tariff = p_cetsh)
1916 AND items.inventory_item_id = jrgi.inventory_item_id
1917 AND jrgi.organization_id = p_organization_id
1918 AND items.organization_id = jrgi.organization_id
1919 AND nvl(jrgi.primary_uom_code,'XYZ') = nvl(p_units,'XYZ')
1920 AND nvl(round(jrgi.excise_duty_rate,0),-999.95) = nvl(p_excise_duty_rate,-999.95)
1921 AND jrgi.location_id = p_location_id
1925 AND payment_register = 'PLA')
1922 AND trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
1923 AND to_char(jrgi.creation_date, 'YYYY') || to_char(jrgi.creation_date, 'MM') = ln_yyyymm
1924 AND jrgi.transaction_type in ( 'I','IA','PI','IOI')
1926 AND source_type = 2
1927 AND tax_type IN (jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess ) ;
1928
1929 CURSOR cur_other_duties_RG23(
1930 p_inventory_item_id IN JAI_CMN_RG_I_TRXS.inventory_item_id%type,
1931 p_excise_duty_rate IN NUMBER,
1932 p_cetsh IN JAI_INV_ITM_SETUPS.item_tariff%type,
1933 p_units IN JAI_CMN_RG_I_TRXS.primary_uom_code%type
1934 )
1935 IS
1936 SELECT nvl(sum(debit),0)FROM JAI_CMN_RG_OTHERS
1937 WHERE source_register_id IN(
1938 SELECT register_id_part_ii FROM JAI_CMN_RG_I_TRXS jrgi, JAI_INV_ITM_SETUPS items
1939 WHERE ( jrgi.inventory_item_id = p_inventory_item_id
1940 OR items.item_tariff = p_cetsh )
1941 AND items.inventory_item_id = jrgi.inventory_item_id
1942 AND jrgi.organization_id = p_organization_id
1943 AND items.organization_id = jrgi.organization_id
1944 AND nvl(jrgi.primary_uom_code,'XYZ') = nvl(p_units,'XYZ')
1945 AND nvl(round(jrgi.excise_duty_rate,0),-999.95) = nvl(p_excise_duty_rate,-999.95)
1946 AND jrgi.location_id = p_location_id
1947 AND trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
1948 AND to_char(jrgi.creation_date, 'YYYY') || to_char(jrgi.creation_date, 'MM') = ln_yyyymm
1949 AND jrgi.transaction_type in ( 'I','IA','PI','IOI')
1950 AND payment_register IN ('RG23A','RG23C') )
1951 AND source_type = 1
1952 AND tax_type IN (jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess ) ;
1953
1954 CURSOR cur_dtls (
1955 p_location_id IN NUMBER,
1956 p_organization_id IN NUMBER,
1957 p_start_date IN DATE,
1958 p_end_date IN DATE)
1959 IS
1960 SELECT
1961 a.primary_uom_code units,
1962 c.item_tariff cetsh ,
1963 substr(c.item_tariff,1,8) cetsh_sub,
1964 0 inventory_item_id,
1965 round(excise_duty_rate,0) excise_duty_rate ,
1966 to_char(a.creation_date, 'YYYY') || to_char(a.creation_date, 'MM') year_month,
1967 sum( nvl(a.basic_ed,0 ) + nvl(a.additional_ed,0) ) duty_payable,
1968 sum( nvl(a.other_ed,0)) other_duties,
1969 a.organization_id -- added, Harshita for Bug 5637136
1970 FROM
1971 JAI_CMN_RG_I_TRXS a ,
1972 mtl_system_items b ,
1973 JAI_INV_ITM_SETUPS c
1974 WHERE a.inventory_item_id = b.inventory_item_id
1975 AND c.inventory_item_id = b.inventory_item_id
1976 AND c.organization_id = b.organization_id
1977 AND a.organization_id = b.organization_id
1978 AND a.location_id = nvl(p_location_id, a.location_id)
1979 AND a.organization_id = nvl(p_organization_id, a.organization_id)
1980 AND trunc(a.creation_date) >= trunc(p_start_date )
1981 AND trunc(a.creation_date) <= trunc(nvl(p_end_date,sysdate))
1982 GROUP BY
1983 c.item_tariff ,
1984 a.primary_uom_code,
1985 round(excise_duty_rate,0),
1986 to_char(a.creation_date, 'MM'),
1987 to_char(a.creation_date, 'YYYY'),
1988 a.organization_id -- added, Harshita for Bug 5637136
1989 HAVING sum( nvl(manufactured_loose_qty,0)+
1990 nvl(for_home_use_pay_ed_qty,0)+
1991 nvl(for_export_pay_ed_qty,0)+
1992 nvl(for_export_n_pay_ed_qty,0)+
1993 nvl(to_other_factory_n_pay_ed_qty,0)+
1994 nvl(other_purpose_n_pay_ed_qty,0)+
1995 nvl(other_purpose_pay_ed_qty,0)) <> 0
1996 ORDER BY
1997 to_char(a.creation_date, 'YYYY'),
1998 to_char(a.creation_date, 'MM') ;
1999
2000 cursor c_other_rg23_ii
2001 is
2002 SELECT round(sum(nvl(debit,0)),0)
2003 FROM JAI_CMN_RG_OTHERS
2004 WHERE tax_type in ( jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess )
2005 AND source_type = 1
2006 AND source_register_id in
2007 ( SELECT register_id
2008 FROM JAI_CMN_RG_23AC_II_TRXS jrgi
2009 WHERE location_id = P_Location_id
2010 AND organization_id = p_Organization_id
2011 AND trunc(creation_date) >= p_start_date
2012 AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
2013 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
2014 AND register_id not in
2015 ( select NVL(register_id_part_ii,0)
2016 from JAI_CMN_RG_I_TRXS
2017 where payment_register IN ( 'RG23A','RG23C' )
2018 )
2019 );
2020
2021 CURSOR cur_other_pla IS
2022 SELECT round(SUM(nvl(credit,0)),0)
2023 FROM JAI_CMN_RG_OTHERS
2024 WHERE source_type=2
2025 AND tax_type in ( jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess )
2026 AND source_register_id in ( SELECT register_id
2027 FROM JAI_CMN_RG_PLA_TRXS
2028 WHERE organization_id = p_organization_id
2029 AND location_id = p_location_id
2030 AND trunc(creation_date) >= p_start_date
2031 AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
2032 AND TRANSACTION_SOURCE_NUM = 91
2033 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
2034 AND register_id not in
2035 ( select NVL(register_id_part_ii,0)
2036 from JAI_CMN_RG_I_TRXS
2037 where payment_register = 'PLA'
2038 )
2039 );
2040
2041 cursor c_excise_uom_code( cp_organization_id IN number, cp_primary_uom_code in varchar2)
2042 is
2043 select excise_uom_code
2044 from jai_ar_excise_uom
2045 where organization_id = cp_organization_id
2046 and primary_uom_code = cp_primary_uom_code ;
2047
2048 cursor c_duty_payable_part_i
2049 ( cp_inventory_item_id number,
2050 cp_cetsh varchar2,
2051 cp_primary_uom_code varchar2
2052 )
2053 is
2054 SELECT sum( NVL(jrgi.basic_ed,0 ) + NVL(jrgi.other_ed,0) ) Duty_payable,
2055 sum( NVL(jrgi.additional_ed,0)) aed_duty_payable
2056 FROM JAI_CMN_RG_23AC_I_TRXS jrgi,
2057 JAI_INV_ITM_SETUPS items
2058 WHERE jrgi.transaction_type in ( 'RTV', 'I', 'IA', 'IOI', 'PI')
2059 and ( jrgi.inventory_item_id = cp_inventory_item_id
2060 OR items.item_tariff = cp_cetsh
2061 )
2062 and items.inventory_item_id = jrgi.inventory_item_id
2063 and jrgi.organization_id = p_organization_id
2064 and items.organization_id = jrgi.organization_id
2065 and nvl(jrgi.primary_uom_code,'XYZ') = nvl(cp_primary_uom_code,'XYZ')
2066 and jrgi.location_id = p_location_id
2067 and trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date);
2068
2069 CURSOR cur_other_duties_PLA_part_i
2070 ( cp_cetsh varchar2,
2071 cp_primary_uom_code varchar2
2072 )
2073 IS
2074 SELECT nvl(sum(debit),0)
2075 FROM JAI_CMN_RG_OTHERS
2076 WHERE source_register_id IN
2077 ( SELECT register_id_part_ii
2078 FROM JAI_CMN_RG_23AC_I_TRXS jrgi,
2079 JAI_INV_ITM_SETUPS items
2080 WHERE ( items.item_tariff = cp_cetsh
2081 )
2082 and items.inventory_item_id = jrgi.inventory_item_id
2083 and jrgi.organization_id = p_organization_id
2084 and items.organization_id = jrgi.organization_id
2085 and nvl(jrgi.primary_uom_code,'XYZ') = nvl(cp_primary_uom_code,'XYZ')
2086 and jrgi.location_id = p_location_id
2087 and trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
2088 and jrgi.transaction_type in ( 'RTV', 'I', 'IA', 'IOI', 'PI')
2089 and register_type = 'PLA')
2090 AND source_type = 2
2091 AND tax_type in ('EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS');
2092
2093 CURSOR cur_other_duties_RG23_part_i
2094 ( cp_cetsh varchar2,
2095 cp_primary_uom_code varchar2
2096 )
2097 IS
2098 SELECT nvl(sum(debit),0)
2099 FROM JAI_CMN_RG_OTHERS
2100 WHERE source_register_id IN
2101 ( SELECT register_id_part_ii
2102 FROM JAI_CMN_RG_23AC_I_TRXS jrgi,
2103 JAI_INV_ITM_SETUPS items
2104 WHERE items.item_tariff = cp_cetsh
2105 and items.inventory_item_id = jrgi.inventory_item_id
2106 and jrgi.organization_id = p_organization_id
2107 and items.organization_id = jrgi.organization_id
2108 and nvl(jrgi.primary_uom_code,'XYZ') = nvl(cp_primary_uom_code,'XYZ')
2109 and jrgi.location_id = p_location_id
2110 and trunc(jrgi.creation_date) between trunc(p_start_date) and trunc(p_end_date)
2111 and jrgi.transaction_type in ( 'RTV', 'I', 'IA', 'IOI', 'PI')
2112 and register_type IN ('A','C') )
2113 AND source_type = 1
2114 AND tax_type in ('EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS');
2115
2116 BEGIN
2117
2118 lv_record_header := 'CETH_WISE_DETAIL' ;
2119 lv_rt_type := 1 ;
2120 lv_return_no := 1 ;
2121 lv_data_prd_type := 'M' ;
2122
2123 FOR dtl IN cur_dtls( p_location_id, p_organization_id, p_start_date, p_end_date)
2124 LOOP
2125 lv_uqc := NULL;
2126 ln_qty_mnf := NULL;
2127 ln_qty_clr := NULL;
2128 ln_duty_rate_adv_cenvat := NULL;
2129 ln_duty_payable_cenvat := NULL;
2130 ln_ass_val := NULL;
2131 ln_duty_payable_edu_cess := NULL;
2132 lv_ceth := NULL;
2133 ln_yyyymm := NULL;
2134 ln_pla_duty := NULL;
2135 ln_rg23_duty := NULL;
2136 ln_duty_rate_sp_cenvat := null ;
2137 ln_duty_rate_adv_edu_cess := null ;
2138 ln_duty_rate_sp_edu_cess := null ;
2139
2140
2141 lv_ceth := substr(dtl.cetsh_sub,1,8) ;
2142 ln_yyyymm := dtl.year_month ;
2143
2144 open c_excise_uom_code( dtl.organization_id, dtl.units) ;
2145 fetch c_excise_uom_code into lv_uqc ;
2146 close c_excise_uom_code ;
2147
2148 -- lv_uqc := dtl.UNITS; -- for Unit of Quantity Code
2149
2150 -- for quantity manufactured
2151 OPEN cur_qty_mftrd( dtl.inventory_item_id, dtl.excise_duty_rate, dtl.CETSH, dtl.UNITS);
2152 FETCH cur_qty_mftrd INTO ln_qty_mnf;
2153 CLOSE cur_qty_mftrd;
2154
2155 -- for quantity cleared
2156 OPEN cur_qty_clrd( dtl.inventory_item_id, dtl.excise_duty_rate, dtl.CETSH, dtl.UNITS);
2157 FETCH cur_qty_clrd INTO ln_qty_clr;
2158 CLOSE cur_qty_clrd;
2159
2160 -- for Advalorem Rate of Duty(CENVAT)
2161 IF ln_qty_clr IS NULL THEN
2162 ln_duty_rate_adv_cenvat := NULL;
2163 ELSE
2164 ln_duty_rate_adv_cenvat := dtl.excise_duty_rate;
2165 END IF;
2166
2167 -- for cenvat duty payable
2168 OPEN cur_duty_payable( dtl.inventory_item_id, dtl.excise_duty_rate, dtl.CETSH, dtl.UNITS);
2169 FETCH cur_duty_payable INTO ln_duty_payable_cenvat;
2170 CLOSE cur_duty_payable;
2171
2172 -- for Assesseble Value of Clearance
2173 IF nvl(ln_duty_rate_adv_cenvat,0) = 0 THEN
2174 ln_ass_val := ln_duty_rate_adv_cenvat;
2175 ELSE
2176 ln_ass_val := round((NVL(ln_duty_payable_cenvat,0)*100)/ln_duty_rate_adv_cenvat,0);
2177 END IF;
2178
2179 -- for Duty Payable(EDU.CESS)
2180 OPEN cur_other_duties_PLA (dtl.inventory_item_id, dtl.excise_duty_rate, dtl.CETSH, dtl.UNITS);
2181 FETCH cur_other_duties_PLA INTO ln_pla_duty;
2182 CLOSE cur_other_duties_PLA;
2183
2187
2184 OPEN cur_other_duties_RG23(dtl.inventory_item_id, dtl.excise_duty_rate, dtl.CETSH, dtl.UNITS);
2185 FETCH cur_other_duties_RG23 INTO ln_rg23_duty;
2186 CLOSE cur_other_duties_RG23;
2188 ln_duty_payable_edu_cess := round((nvl(ln_pla_duty,0) + nvl(ln_rg23_duty,0)),0);
2189
2190 ln_pla_duty := null ;
2191 ln_rg23_duty := null ;
2192
2193 IF ln_duty_payable_cenvat is not null THEN
2194 ln_duty_rate_sp_cenvat := 0;
2195 END IF ;
2196
2197 IF ln_duty_payable_edu_cess is not null THEN
2198 ln_duty_rate_adv_edu_cess := 0 ;
2199 ln_duty_rate_sp_edu_cess := 0 ;
2200 END IF ;
2201
2202
2203 create_ceth_details(
2204 p_record_header => lv_record_header,
2205 p_rt_type => lv_rt_type,
2206 p_ecc => lv_ecc,
2207 p_yyyymm => ln_yyyymm,
2208 p_return_no => lv_return_no,
2209 p_data_prd_type => lv_data_prd_type,
2210 p_ceth => lv_ceth,
2211 p_ctsh => lv_ctsh,
2212 p_uqc => lv_uqc,
2213 p_qty_mnf => nvl(ln_qty_mnf,0),
2214 p_qty_clr_type => lv_qty_clr_type,
2215 p_qty_clr => nvl(ln_qty_clr,0),
2216 p_ass_val => nvl(ln_ass_val,0),
2217 p_notf_no_1 => lv_notf_no_1,
2218 p_notf_sno_1 => lv_notf_sno_1,
2219 p_notf_no_2 => lv_notf_no_2,
2220 p_notf_sno_2 => lv_notf_sno_2,
2221 p_notf_no_3 => lv_notf_no_3,
2222 p_notf_sno_3 => lv_notf_sno_3,
2223 p_notf_no_4 => lv_notf_no_4,
2224 p_notf_sno_4 => lv_notf_sno_4,
2225 p_notf_no_5 => lv_notf_no_5,
2226 p_notf_sno_5 => lv_notf_sno_5,
2227 p_notf_no_6 => lv_notf_no_6,
2228 p_notf_sno_6 => lv_notf_sno_6,
2229 p_duty_rate_adv_cenvat => ln_duty_rate_adv_cenvat,
2230 p_duty_rate_sp_cenvat => ln_duty_rate_sp_cenvat,
2231 p_duty_payable_cenvat => ln_duty_payable_cenvat,
2232 p_pa_no_cenvat => ln_pa_no_cenvat,
2233 p_duty_rate_adv_sed => ln_duty_rate_adv_sed,
2234 p_duty_rate_sp_sed => ln_duty_rate_sp_sed,
2235 p_duty_payable_sed => ln_duty_payable_sed,
2236 p_pa_no_sed => ln_pa_no_sed,
2237 p_duty_rate_adv_aed_gsi => ln_duty_rate_adv_aed_gsi,
2238 p_duty_rate_sp_aed_gsi => ln_duty_rate_sp_aed_gsi,
2239 p_duty_payable_aed_gsi => ln_duty_payable_aed_gsi,
2240 p_pa_no_aed_gsi => ln_pa_no_aed_gsi,
2241 p_duty_rate_adv_nccd => ln_duty_rate_adv_nccd,
2242 p_duty_rate_sp_nccd => ln_duty_rate_sp_nccd,
2243 p_duty_payable_nccd => ln_duty_payable_nccd,
2244 p_pa_no_nccd => ln_pa_no_nccd,
2245 p_duty_rate_adv_aed_tta => ln_duty_rate_adv_aed_tta,
2246 p_duty_rate_sp_aed_tta => ln_duty_rate_sp_aed_tta,
2247 p_duty_payable_aed_tta => ln_duty_payable_aed_tta,
2248 p_pa_no_aed_tta => ln_pa_no_aed_tta,
2249 p_duty_rate_adv_aed_pmt => ln_duty_rate_adv_aed_pmt,
2250 p_duty_rate_sp_aed_pmt => ln_duty_rate_sp_aed_pmt,
2251 p_duty_payable_aed_pmt => ln_duty_payable_aed_pmt,
2252 p_pa_no_aed_pmt => ln_pa_no_aed_pmt,
2253 p_duty_rate_adv_saed => ln_duty_rate_adv_saed,
2254 p_duty_rate_sp_saed => ln_duty_rate_sp_saed,
2255 p_duty_payable_saed => ln_duty_payable_saed,
2256 p_pa_no_saed => ln_pa_no_saed,
2257 p_duty_rate_adv_ade => ln_duty_rate_adv_ade,
2258 p_duty_rate_sp_ade => ln_duty_rate_sp_ade,
2259 p_duty_payable_ade => ln_duty_payable_ade,
2260 p_pa_no_ade => ln_pa_no_ade,
2261 p_duty_rate_adv_adet => ln_duty_rate_adv_adet,
2262 p_duty_rate_sp_adet => ln_duty_rate_sp_adet,
2263 p_duty_payable_adet => ln_duty_payable_adet,
2264 p_pa_no_adet => ln_pa_no_adet,
2265 p_duty_rate_adv_cess => ln_duty_rate_adv_cess,
2266 p_duty_rate_sp_cess => ln_duty_rate_sp_cess,
2267 p_duty_payable_cess => ln_duty_payable_cess,
2268 p_pa_no_cess => ln_pa_no_cess,
2269 p_duty_rate_adv_edu_cess=> ln_duty_rate_adv_edu_cess,
2270 p_duty_rate_sp_edu_cess => ln_duty_rate_sp_edu_cess,
2271 p_duty_payable_edu_cess => ln_duty_payable_edu_cess,
2272 p_pa_no_edu_cess => ln_pa_no_edu_cess);
2273
2274 END LOOP;
2275
2276 FOR dtl in
2277 (
2278 SELECT
2279 a.primary_uom_code UNITS,
2280 c.item_tariff CETSH ,
2281 Substr(c.ITEM_TARIFF,1,15) CETSH_SUB,
2282 0 inventory_item_id,
2283 0 excise_duty_rate ,
2284 to_char(a.creation_date, 'YYYY') || to_char(a.creation_date, 'MM') year_month,
2285 sum( NVL(a.basic_ed,0 ) + NVL(a.additional_ed,0) ) Duty_payable,
2286 sum( NVL(a.other_ed,0)) Other_duties,
2287 a.organization_id
2288 FROM
2289 JAI_CMN_RG_23AC_I_TRXS A ,
2290 mtl_system_items b ,
2291 JAI_INV_ITM_SETUPS c
2292 where a.inventory_item_id = b.inventory_item_id
2293 and c.inventory_item_id = b.inventory_item_id
2294 and c.organization_id = b.organization_id
2295 and a.organization_id = b.organization_id
2296 and a.location_id = nvl(P_Location_id, a.location_id)
2297 and a.organization_id = nvl(p_Organization_id, a.organization_id)
2298 and trunc(a.creation_date) >= trunc(p_start_date )
2299 and trunc(a.creation_date) <= trunc(nvl(p_end_date,sysdate))
2300 GROUP BY
2301 c.item_tariff ,
2302 a.primary_uom_code,
2303 a.organization_id ,
2304 to_char(a.creation_date, 'MM'),
2305 to_char(a.creation_date, 'YYYY')
2306 ORDER BY
2307 to_char(a.creation_date, 'YYYY'),
2308 to_char(a.creation_date, 'MM')
2309 )
2310 LOOP
2311
2312 lv_uqc := NULL;
2313 ln_qty_mnf := NULL;
2314 ln_qty_clr := NULL;
2315 ln_duty_rate_adv_cenvat := NULL;
2316 ln_duty_payable_cenvat := NULL;
2317 ln_ass_val := NULL;
2318 ln_duty_payable_edu_cess := NULL;
2319 lv_ceth := NULL;
2320 ln_yyyymm := NULL;
2321 ln_pla_duty := NULL;
2322 ln_rg23_duty := NULL;
2323 ln_duty_rate_sp_cenvat := null ;
2324 ln_duty_rate_adv_edu_cess := null ;
2325 ln_duty_rate_sp_edu_cess := null ;
2326 lv_ceth := substr(dtl.cetsh_sub,1,8) ;
2327 ln_yyyymm := dtl.year_month ;
2328 lv_uqc := dtl.UNITS;
2329 ln_qty_mnf := 0 ;
2330 ln_qty_clr := 0 ;
2331 ln_duty_rate_adv_cenvat := 0 ;
2332
2333 open c_duty_payable_part_i(dtl.inventory_item_id, dtl.cetsh, dtl.units ) ;
2334 fetch c_duty_payable_part_i into ln_duty_payable_cenvat, ln_duty_rate_adv_cenvat ;
2335 close c_duty_payable_part_i ;
2336
2337 OPEN cur_other_duties_PLA_part_i(dtl.cetsh , dtl.units);
2338 FETCH cur_other_duties_PLA_part_i INTO ln_pla_duty;
2339 CLOSE cur_other_duties_PLA_part_i;
2340
2341 OPEN cur_other_duties_RG23_part_i(dtl.cetsh , dtl.units);
2342 FETCH cur_other_duties_RG23_part_i INTO ln_rg23_duty;
2343 CLOSE cur_other_duties_RG23_part_i;
2344
2345 ln_duty_payable_edu_cess := round((nvl(ln_pla_duty,0) + nvl(ln_rg23_duty,0)),0);
2346
2347 IF ln_duty_payable_cenvat is not null THEN
2348 ln_duty_rate_sp_cenvat := 0;
2349 END IF ;
2350
2351 IF ln_duty_payable_edu_cess is not null THEN
2352 ln_duty_rate_adv_edu_cess := 0 ;
2353 ln_duty_rate_sp_edu_cess := 0 ;
2354 END IF ;
2355
2356 create_ceth_details(
2357 p_record_header => lv_record_header,
2358 p_rt_type => lv_rt_type,
2359 p_ecc => lv_ecc,
2360 p_yyyymm => ln_yyyymm,
2361 p_return_no => lv_return_no,
2362 p_data_prd_type => lv_data_prd_type,
2363 p_ceth => lv_ceth,
2364 p_ctsh => lv_ctsh,
2365 p_uqc => lv_uqc,
2366 p_qty_mnf => nvl(ln_qty_mnf,0),
2367 p_qty_clr_type => lv_qty_clr_type,
2368 p_qty_clr => nvl(ln_qty_clr,0),
2369 p_ass_val => nvl(ln_ass_val,0),
2370 p_notf_no_1 => lv_notf_no_1,
2371 p_notf_sno_1 => lv_notf_sno_1,
2372 p_notf_no_2 => lv_notf_no_2,
2373 p_notf_sno_2 => lv_notf_sno_2,
2374 p_notf_no_3 => lv_notf_no_3,
2375 p_notf_sno_3 => lv_notf_sno_3,
2376 p_notf_no_4 => lv_notf_no_4,
2377 p_notf_sno_4 => lv_notf_sno_4,
2378 p_notf_no_5 => lv_notf_no_5,
2379 p_notf_sno_5 => lv_notf_sno_5,
2380 p_notf_no_6 => lv_notf_no_6,
2381 p_notf_sno_6 => lv_notf_sno_6,
2382 p_duty_rate_adv_cenvat => ln_duty_rate_adv_cenvat,
2383 p_duty_rate_sp_cenvat => ln_duty_rate_sp_cenvat,
2384 p_duty_payable_cenvat => ln_duty_payable_cenvat,
2385 p_pa_no_cenvat => ln_pa_no_cenvat,
2386 p_duty_rate_adv_sed => ln_duty_rate_adv_sed,
2387 p_duty_rate_sp_sed => ln_duty_rate_sp_sed,
2388 p_duty_payable_sed => ln_duty_payable_sed,
2389 p_pa_no_sed => ln_pa_no_sed,
2390 p_duty_rate_adv_aed_gsi => ln_duty_rate_adv_aed_gsi,
2391 p_duty_rate_sp_aed_gsi => ln_duty_rate_sp_aed_gsi,
2392 p_duty_payable_aed_gsi => ln_duty_payable_aed_gsi,
2393 p_pa_no_aed_gsi => ln_pa_no_aed_gsi,
2394 p_duty_rate_adv_nccd => ln_duty_rate_adv_nccd,
2395 p_duty_rate_sp_nccd => ln_duty_rate_sp_nccd,
2396 p_duty_payable_nccd => ln_duty_payable_nccd,
2397 p_pa_no_nccd => ln_pa_no_nccd,
2398 p_duty_rate_adv_aed_tta => ln_duty_rate_adv_aed_tta,
2399 p_duty_rate_sp_aed_tta => ln_duty_rate_sp_aed_tta,
2400 p_duty_payable_aed_tta => ln_duty_payable_aed_tta,
2401 p_pa_no_aed_tta => ln_pa_no_aed_tta,
2402 p_duty_rate_adv_aed_pmt => ln_duty_rate_adv_aed_pmt,
2403 p_duty_rate_sp_aed_pmt => ln_duty_rate_sp_aed_pmt,
2404 p_duty_payable_aed_pmt => ln_duty_payable_aed_pmt,
2405 p_pa_no_aed_pmt => ln_pa_no_aed_pmt,
2406 p_duty_rate_adv_saed => ln_duty_rate_adv_saed,
2407 p_duty_rate_sp_saed => ln_duty_rate_sp_saed,
2408 p_duty_payable_saed => ln_duty_payable_saed,
2409 p_pa_no_saed => ln_pa_no_saed,
2410 p_duty_rate_adv_ade => ln_duty_rate_adv_ade,
2411 p_duty_rate_sp_ade => ln_duty_rate_sp_ade,
2412 p_duty_payable_ade => ln_duty_payable_ade,
2413 p_pa_no_ade => ln_pa_no_ade,
2414 p_duty_rate_adv_adet => ln_duty_rate_adv_adet,
2415 p_duty_rate_sp_adet => ln_duty_rate_sp_adet,
2416 p_duty_payable_adet => ln_duty_payable_adet,
2417 p_pa_no_adet => ln_pa_no_adet,
2418 p_duty_rate_adv_cess => ln_duty_rate_adv_cess,
2419 p_duty_rate_sp_cess => ln_duty_rate_sp_cess,
2420 p_duty_payable_cess => ln_duty_payable_cess,
2421 p_pa_no_cess => ln_pa_no_cess,
2422 p_duty_rate_adv_edu_cess=> ln_duty_rate_adv_edu_cess,
2423 p_duty_rate_sp_edu_cess => ln_duty_rate_sp_edu_cess,
2424 p_duty_payable_edu_cess => ln_duty_payable_edu_cess,
2425 p_pa_no_edu_cess => ln_pa_no_edu_cess);
2426
2427 END LOOP ;
2428
2429 END populate_ceth_wise_details;
2430
2431
2432 -- to populate cenvat credit details
2433 PROCEDURE populate_cenvat_credit_details (
2434 p_end_date IN DATE,
2435 p_location_id IN NUMBER,
2436 p_operating_unit IN NUMBER,
2437 p_organization_id IN NUMBER,
2438 p_registration_number IN VARCHAR2,
2439 p_start_date IN DATE )
2440 IS
2441 lv_record_header varchar2(50);
2442 lv_rt_type varchar2(2);
2443 p_ecc varchar2(15);
2444 ln_yyyymm number;
2445 lv_return_no varchar2(3);
2446 lv_data_prd_type varchar2(2);
2447 ln_op_bal_cenvat number ;
2448 ln_credit_input_cenvat number ;
2449 ln_credit_input_dlr_cenvat number ;
2450 ln_credit_capital_cenvat number ;
2451 ln_credit_service_cenvat number ;
2452 ln_credit_total_cenvat number ;
2453 ln_credit_utilised_cenvat number ;
2454 ln_credit_utilised_ic_cenvat number ;
2455 ln_credit_utilised_ds_cenvat number ;
2456 ln_clos_bal_cenvat number ;
2457 ln_op_bal_aed_tta number ;
2458 ln_credit_input_aed_tta number ;
2459 ln_credit_input_dlr_aed_tta number ;
2460 ln_credit_capital_aed_tta number ;
2461 ln_credit_service_aed_tta number ;
2462 ln_credit_total_aed_tta number ;
2463 ln_credit_utilised_aed_tta number ;
2464 ln_credit_utilised_ic_aed_tta number ;
2465 ln_credit_utilised_ds_aed_tta number ;
2466 ln_clos_bal_aed_tta number ;
2467 ln_op_bal_aed_pmt number ;
2468 ln_credit_input_aed_pmt number ;
2469 ln_credit_input_dlr_aed_pmt number ;
2470 ln_credit_capital_aed_pmt number ;
2471 ln_credit_service_aed_pmt number ;
2472 ln_credit_total_aed_pmt number ;
2473 ln_credit_utilised_aed_pmt number ;
2474 ln_credit_utilised_ic_aed_pmt number ;
2475 ln_credit_utilised_ds_aed_pmt number ;
2476 ln_clos_bal_aed_pmt number ;
2477 ln_op_bal_nccd number ;
2478 ln_credit_input_nccd number ;
2479 ln_credit_input_dlr_nccd number ;
2480 ln_credit_capital_nccd number ;
2481 ln_credit_service_nccd number ;
2482 ln_credit_total_nccd number ;
2483 ln_credit_utilised_nccd number ;
2484 ln_credit_utilised_ic_nccd number ;
2485 ln_credit_utilised_ds_nccd number ;
2486 ln_clos_bal_nccd number ;
2487 ln_op_bal_adet number ;
2488 ln_credit_input_adet number ;
2489 ln_credit_input_dlr_adet number ;
2490 ln_credit_capital_adet number ;
2491 ln_credit_service_adet number ;
2492 ln_credit_total_adet number ;
2493 ln_credit_utilised_adet number ;
2494 ln_credit_utilised_ic_adet number ;
2495 ln_credit_utilised_ds_adet number ;
2496 ln_clos_bal_adet number ;
2497 ln_op_bal_edu_cess number ;
2498 ln_credit_input_edu_cess number ;
2499 ln_credit_input_dlr_edu_cess number ;
2500 ln_credit_capital_edu_cess number ;
2501 ln_credit_service_edu_cess number ;
2502 ln_credit_total_edu_cess number ;
2503 ln_credit_utilised_edu_cess number ;
2504 ln_credit_utilised_ic_edu_cess number ;
2505 ln_credit_utilised_ds_edu_cess number ;
2506 ln_clos_bal_edu_cess number ;
2507 ln_op_bal_st number ;
2508 ln_credit_input_st number ;
2509 ln_credit_input_dlr_st number ;
2510 ln_credit_capital_st number ;
2511 ln_credit_service_st number ;
2512 ln_credit_total_st number ;
2513 ln_credit_utilised_st number ;
2514 ln_credit_utilised_ic_st number ;
2515 ln_credit_utilised_ds_st number ;
2516 ln_clos_bal_st number ;
2517 ln_op_bal_st_edu_cess number ;
2518 ln_credit_input_st_edu_cess number ;
2519 ln_cre_input_dlr_st_edu_cess number ;
2520 ln_credit_capital_st_edu_cess number ;
2521 ln_credit_service_st_edu_cess number ;
2522 ln_credit_total_st_edu_cess number ;
2523 ln_creln_dit_uti_st_edu_cess number ;
2524 ln_credit_uti_ic_st_edu_cess number ;
2525 ln_credit_uti_ds_st_edu_cess number ;
2526 ln_clos_bal_st_edu_cess number ;
2527 ln_closed_input_manf NUMBER;
2528 ln_closed_input_manf_iso NUMBER;
2529 ln_closed_input_cust NUMBER;
2530 ln_closed_input_stg NUMBER;
2531 ln_closed_input_stg_iso NUMBER;
2532 ln_rtv_amount NUMBER;
2533 ln_cgin_sale_amt NUMBER;
2534 ln_edu_cess_excise_manf NUMBER;
2535 ln_edu_cess_excise_manf_iso NUMBER;
2536 ln_edu_cess_excise_cust NUMBER;
2537 ln_edu_cess_excise_stg NUMBER;
2538 ln_edu_cess_excise_stg_iso NUMBER;
2539 ln_edu_cess_excise NUMBER;
2540 ln_rtv_cess NUMBER;
2541 ln_cgin_sales_cess NUMBER;
2542 lv_inv_open_bal NUMBER;
2543 lv_open_dist_bal NUMBER;
2544 lv_ar_util_credit NUMBER;
2545 lv_ar_ser_dist_out_debit NUMBER;
2546 lv_manual_bal NUMBER;
2547 lv_manual_debit_bal NUMBER;
2548 lv_manual_payment NUMBER;
2549 lv_st_credit_avld NUMBER;
2550 lv_cess_credit_avld NUMBER;
2551 ln_ar_util_credit NUMBER;
2552 ln_ar_ser_dist_out_debit NUMBER;
2553 lv_manual_debit NUMBER;
2554 lv_payment NUMBER;
2555
2556
2557 -- Cursor for cenvat opening balance
2558 CURSOR cur_opening_balance_cenvat(cp_start_date IN DATE )
2559 IS
2560 SELECT round(NVL(SUM(NVL(cr_basic_ed,0)+ NVL(cr_additional_ed,0) + NVL(cr_other_ed,0) - NVL(dr_basic_ed,0)- NVL(dr_additional_ed,0) - NVL(dr_other_ed,0)),0),0)
2561 FROM JAI_CMN_RG_23AC_II_TRXS
2562 WHERE location_id = p_location_id
2563 AND organization_id = p_organization_id
2564 AND trunc(creation_date) < cp_start_date;
2565
2566
2567
2568 -- Cursors for Credit availed on Input on invoices issued by manufactureres
2569 CURSOR Cur_crdit_input_manf
2570 IS
2571 SELECT SUM(DECODE(register_type, 'A', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)) credit_availed_on_inputs_vend
2572 FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_VENDOR_SITES JIPV
2573 WHERE location_id = p_location_id
2574 AND organization_id = p_organization_id
2575 AND JIRP.vendor_id = JIPV.vendor_id
2576 AND JIRP.vendor_site_id = JIPV.vendor_site_id
2577 AND (
2578 JIPV.vendor_type IN ('Manufacturer', 'Importer')
2579 OR JIPV.vendor_type IS NULL)
2580 AND TRUNC(JIRP.creation_date) >= p_start_date
2581 AND TRUNC(JIRP.creation_date) <= trunc(nvl(p_end_date,SYSDATE))
2582 AND to_char(jirp.creation_date, 'YYYY') || to_char(jirp.creation_date, 'MM') = ln_yyyymm ;
2583
2584 CURSOR Cur_crdit_input_cust
2585 IS
2586 SELECT SUM(DECODE(register_type, 'A', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)) credit_availed_on_inputs_cust
2587 FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_CUS_ADDRESSES JICA,hz_cust_acct_sites_all HZCAS,hz_cust_site_uses_all HZCSU
2588 WHERE HZCAS.cust_acct_site_id = HZCSU.cust_acct_site_id
2589 AND JICA.address_id = HZCSU.cust_acct_site_id
2590 AND HZCSU.site_use_id = JIRP.customer_site_id
2591 AND JIRP.customer_id = JICA.customer_id
2592 AND JIRP.location_id = p_location_id
2593 AND JIRP.organization_id = p_organization_id
2594 AND TRUNC(JIRP.creation_date) >= p_start_date
2595 AND TRUNC(JIRP.creation_date) <= TRUNC(nvl(p_end_date,SYSDATE))
2596 AND to_char(jirp.creation_date, 'YYYY') || to_char(jirp.creation_date, 'MM') = ln_yyyymm ;
2597
2598 CURSOR Cur_crdit_input_manf_iso
2599 IS
2600 SELECT SUM(DECODE(register_type, 'A', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)) credit_availed_on_inputs
2601 FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_INVENTORY_ORGS JIHO
2602 WHERE JIRP.location_id = p_location_id
2603 AND JIRP.organization_id = p_organization_id
2604 AND ABS(jirp.vendor_id) = jiho.organization_id
2605 AND ABS(jirp.vendor_site_id) = jiho.location_id
2606 AND JIHO.manufacturing = 'Y'
2607 AND TRUNC(jirp.creation_date) >= p_start_date
2608 AND TRUNC(jirp.creation_date) <= trunc(nvl(p_end_date,SYSDATE))
2609 AND to_char(jirp.creation_date, 'YYYY') || to_char(jirp.creation_date, 'MM') = ln_yyyymm ;
2610
2611 -- Cursors for Credit availed on Input on invoices issued by I or II stage dealers
2612
2613 CURSOR Cur_crdit_input_stg
2614 IS
2615 SELECT SUM(DECODE(register_type, 'A', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)) credit_availed_on_inputs
2616 FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_VENDOR_SITES JIPV
2617 WHERE location_id = p_location_id
2618 AND organization_id = p_organization_id
2619 AND JIRP.vendor_id = JIPV.vendor_id
2620 AND JIRP.vendor_site_id = JIPV.vendor_site_id
2621 AND JIPV.vendor_type IN ('First Stage Dealer', 'Second Stage Dealer')
2622 AND TRUNC(JIRP.creation_date) >= p_start_date
2623 AND TRUNC(JIRP.creation_date) <= trunc(nvl(p_end_date,SYSDATE))
2624 AND to_char(jirp.creation_date, 'YYYY') || to_char(jirp.creation_date, 'MM') = ln_yyyymm ;
2625
2626 CURSOR Cur_crdit_input_stg_iso
2627 IS
2628 SELECT SUM(DECODE(register_type, 'A', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)) credit_availed_on_inputs
2629 FROM JAI_CMN_RG_23AC_II_TRXS JIRP,JAI_CMN_INVENTORY_ORGS JIHO
2630 WHERE JIRP.location_id = p_location_id
2631 AND JIRP.organization_id = p_organization_id
2632 AND ABS(JIRP.vendor_id) = JIHO.organization_id
2633 AND ABS(JIRP.vendor_site_id) = JIHO.location_id
2634 AND JIHO.trading = 'Y'
2635 AND TRUNC(JIRP.creation_date) >= p_start_date
2636 AND TRUNC(JIRP.creation_date) <= trunc(nvl(p_end_date,SYSDATE))
2637 AND to_char(jirp.creation_date, 'YYYY') || to_char(jirp.creation_date, 'MM') = ln_yyyymm ;
2638
2639 -- Cursors for Credit utilised (input/capital goods)
2640 CURSOR get_rtv_amount
2641 IS
2642 SELECT sum(nvl(jrg23_ii.DR_BASIC_ED,0) + nvl(jrg23_ii.DR_ADDITIONAL_ED,0) + nvl(jrg23_ii.DR_OTHER_ED,0))
2643 FROM JAI_CMN_RG_23AC_II_TRXS jrg23_ii ,JAI_CMN_RG_23AC_I_TRXS jrg23_i
2644 WHERE jrg23_ii.organization_id = p_organization_id
2645 AND jrg23_ii.location_id = p_location_id
2646 AND trunc(jrg23_ii.creation_date) >= p_start_date
2647 AND trunc(jrg23_ii.creation_date) <= trunc(nvl(p_end_date,sysdate))
2648 AND jrg23_i.transaction_type = 'RTV'
2649 AND jrg23_ii.organization_id = jrg23_i.organization_id
2650 AND jrg23_ii.location_id = jrg23_i.location_id
2651 AND jrg23_ii.register_id_part_i = jrg23_i.register_id
2652 AND to_char(jrg23_ii.creation_date, 'YYYY') || to_char(jrg23_ii.creation_date, 'MM') = ln_yyyymm ;
2653
2654 CURSOR get_cgin_sales
2655 IS
2656 SELECT sum(nvl(jrg23_ii.DR_BASIC_ED,0) + nvl(jrg23_ii.DR_ADDITIONAL_ED,0) + nvl(jrg23_ii.DR_OTHER_ED,0))
2657 FROM JAI_CMN_RG_23AC_II_TRXS jrg23_ii ,JAI_CMN_RG_23AC_I_TRXS jrg23_i,JAI_INV_ITM_SETUPS jmsi
2658 WHERE jrg23_ii.organization_id = jrg23_i.organization_id
2659 AND jrg23_ii.location_id = jrg23_i.location_id
2660 AND jrg23_ii.register_id_part_i = jrg23_i.register_id
2661 AND jmsi.organization_id = jrg23_ii.organization_id
2662 AND jmsi.item_class like 'CG%'
2663 AND jmsi.inventory_item_id = jrg23_ii.inventory_item_id
2664 AND jmsi.organization_id = p_organization_id
2665 AND jrg23_ii.organization_id = p_organization_id
2666 AND jrg23_ii.location_id = p_location_id
2667 AND trunc(jrg23_ii.creation_date) >= p_start_date
2668 AND trunc(jrg23_ii.creation_date) <= trunc(nvl(p_end_date,sysdate))
2669 AND jrg23_i.transaction_type <> 'RTV'
2670 AND to_char(jrg23_ii.creation_date, 'YYYY') || to_char(jrg23_ii.creation_date, 'MM') = ln_yyyymm ;
2671
2672 -- Cursor for opening balance(EDU CESS)
2673 CURSOR cur_opening_bal_edu_cess(cp_start_date IN DATE )
2674 IS
2675 SELECT round(sum(nvl(credit,0) - nvl(debit,0)),0)
2676 FROM JAI_CMN_RG_OTHERS
2677 WHERE source_type = 1
2678 AND source_register_id in (
2679 SELECT register_id
2680 FROM JAI_CMN_RG_23AC_II_TRXS
2681 WHERE location_id = p_location_id
2682 AND organization_id = p_organization_id
2683 AND trunc(creation_date) < cp_start_date)
2684 AND tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess);
2685
2686 -- Cursors for Credit availed on Input on invoices issued by manufactureres (EDU CESS)
2687 CURSOR Cur_cess_excise_input_manf
2688 IS
2689 SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_VENDOR_SITES JIPV
2690 WHERE jro.source_register_id = RG23.register_id
2691 AND RG23.vendor_id = JIPV.vendor_id
2692 AND RG23.vendor_site_id = JIPV.vendor_site_id
2693 AND ( JIPV.vendor_type IN ('Manufacturer', 'Importer')
2694 OR JIPV.vendor_type IS NULL)
2695 AND rg23.location_id = p_location_id
2696 AND rg23.organization_id = p_organization_id
2697 AND TRUNC(rg23.creation_date) >= p_start_date
2698 AND TRUNC(rg23.creation_date) <= TRUNC(NVL(p_end_date,sysdate))
2699 AND RG23.register_type = 'A'
2700 AND JRO.source_register = 'RG23A_P2'
2701 AND JRO.tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
2702 AND to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
2703
2704 CURSOR Cur_cess_excise_input_cust
2705 IS
2706 SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_CUS_ADDRESSES JICA,hz_cust_acct_sites_all HZCAS,hz_cust_site_uses_all HZCSU
2707 WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
2708 and jica.address_id = hzcsu.cust_acct_site_id
2709 and hzcsu.site_use_id = rg23.customer_site_id
2710 and rg23.customer_id = jica.customer_id
2711 and jro.source_register_id = rg23.register_id
2712 and rg23.location_id = p_location_id
2713 and rg23.organization_id = p_organization_id
2714 and trunc(rg23.creation_date) >= p_start_date
2715 and trunc(rg23.creation_date) <= trunc(nvl(p_end_date,sysdate))
2716 and rg23.register_type = 'A'
2717 and jro.source_register = 'RG23A_P2'
2718 and jro.tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
2719 and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
2720
2721 CURSOR Cur_cess_excise_input_manf_iso
2722 IS
2723 SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_INVENTORY_ORGS JIHO
2724 WHERE jro.source_register_id = RG23.register_id
2725 and abs(rg23.vendor_id) = jiho.organization_id
2726 and abs(rg23.vendor_site_id)= jiho.location_id
2727 and jiho.manufacturing = 'Y'
2728 and rg23.location_id = p_location_id
2729 and rg23.organization_id = p_organization_id
2730 and trunc(rg23.creation_date) >= p_start_date
2731 and trunc(rg23.creation_date) <= trunc(nvl(p_end_date,sysdate))
2732 and rg23.register_type = 'A'
2733 and jro.source_register = 'RG23A_P2'
2734 and jro.tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
2735 and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
2736
2737
2738 -- Cursor for Credit availed on Input on invoices issued by I or II stage dealers (EDU CESS)
2739 CURSOR Cur_cess_excise_input_stg
2740 IS
2741 SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_VENDOR_SITES JIPV
2742 WHERE jro.source_register_id = RG23.register_id
2743 and rg23.vendor_id = jipv.vendor_id(+)
2744 and rg23.vendor_site_id = jipv.vendor_site_id(+)
2745 AND JIPV.vendor_type IN ('First Stage Dealer', 'Second Stage Dealer')
2746 and rg23.location_id = p_location_id
2747 and rg23.organization_id = p_organization_id
2748 and trunc(rg23.creation_date) >= p_start_date
2749 and trunc(rg23.creation_date) <= trunc(nvl(p_end_date,sysdate))
2750 and rg23.register_type = 'A'
2751 and jro.source_register = 'RG23A_P2'
2752 and jro.tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
2756 IS
2753 and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
2754
2755 CURSOR Cur_cess_excise_input_stg_iso
2757 SELECT NVL(SUM(credit),0) FROM JAI_CMN_RG_OTHERS JRO,JAI_CMN_RG_23AC_II_TRXS RG23,JAI_CMN_INVENTORY_ORGS JIHO
2758 WHERE jro.source_register_id = RG23.register_id
2759 AND ABS(RG23.vendor_id) = JIHO.organization_id
2760 AND ABS(RG23.vendor_site_id)= JIHO.location_id
2761 AND JIHO.trading = 'Y'
2762 AND RG23.location_id = p_location_id
2763 AND RG23.organization_id = p_organization_id
2764 AND TRUNC(RG23.creation_date) >= p_start_date
2765 AND TRUNC(RG23.creation_date) <= TRUNC(NVL(p_end_date,sysdate))
2766 AND RG23.register_type = 'A'
2767 AND JRO.source_register = 'RG23A_P2'
2768 AND JRO.tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
2769 and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
2770
2771
2772 -- Cursor for Credit on capital (EDU CESS) AND Total Credit (EDU CESS)
2773 CURSOR cur_edu_cess_cap(p_register_type IN VARCHAR2,p_source_register IN VARCHAR2)
2774 IS
2775 SELECT round(nvl(sum(credit),0),0) FROM JAI_CMN_RG_OTHERS jro,JAI_CMN_RG_23AC_II_TRXS rg23
2776 WHERE jro.source_register_id = rg23.register_id
2777 AND rg23.location_id = p_location_id
2778 AND rg23.organization_id = p_organization_id
2779 AND trunc(rg23.creation_date) >= p_start_date
2780 AND trunc(rg23.creation_date) <= trunc(nvl(p_end_date,sysdate))
2781 AND rg23.register_type = p_register_type
2782 AND jro.source_register = p_source_register
2783 AND jro.tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
2784 and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
2785
2786 -- Cursor for Credit utilised(EDU CESS)
2787 CURSOR cur_edu_cess_excise
2788 IS
2789 SELECT nvl(sum(debit),0) FROM JAI_CMN_RG_OTHERS jro,JAI_CMN_RG_23AC_II_TRXS rg23
2790 WHERE jro.source_register_id = rg23.register_id
2791 AND rg23.location_id = p_location_id
2792 AND rg23.organization_id = p_organization_id
2793 AND trunc(rg23.creation_date) >= p_start_date
2794 AND trunc(rg23.creation_date) <= trunc(nvl(p_end_date,sysdate))
2795 AND rg23.register_type IN ('A','C')
2796 AND jro.source_register in ('RG23A_P2','RG23C_P2')
2797 AND jro.tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
2798 and to_char(rg23.creation_date, 'YYYY') || to_char(rg23.creation_date, 'MM') = ln_yyyymm ;
2799
2800 -- Cursor for Credit utilised (input/capital goods) (EDU CESS)
2801 CURSOR get_rtv_cess IS
2802 SELECT sum(nvl(debit,0))
2803 FROM JAI_CMN_RG_OTHERS
2804 WHERE source_type = 1
2805 AND tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
2806 AND source_register_id in (
2807 SELECT jrg23_ii.register_id FROM JAI_CMN_RG_23AC_II_TRXS jrg23_ii ,JAI_CMN_RG_23AC_I_TRXS jrg23_i
2808 WHERE jrg23_ii.organization_id = p_organization_id
2809 AND jrg23_ii.location_id = p_location_id
2810 AND trunc(jrg23_ii.creation_date) >= p_start_date
2811 AND trunc(jrg23_ii.creation_date) <= trunc(nvl(p_end_date,sysdate))
2812 AND jrg23_i.transaction_type = 'RTV'
2813 AND jrg23_ii.organization_id = jrg23_i.organization_id
2814 AND jrg23_ii.location_id = jrg23_i.location_id
2815 AND jrg23_ii.register_id_part_i = jrg23_i.register_id
2816 and to_char(jrg23_ii.creation_date, 'YYYY') || to_char(jrg23_ii.creation_date, 'MM') = ln_yyyymm
2817 );
2818
2819 CURSOR get_cgin_sales_cess IS
2820 SELECT sum(nvl(debit,0))
2821 FROM JAI_CMN_RG_OTHERS
2822 WHERE source_type = 1
2823 AND tax_type in ( jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess)
2824 AND source_register_id in (
2825 SELECT jrg23_ii.register_id FROM JAI_CMN_RG_23AC_II_TRXS jrg23_ii ,JAI_CMN_RG_23AC_I_TRXS jrg23_i,JAI_INV_ITM_SETUPS jmsi
2826 WHERE jrg23_ii.organization_id = jrg23_i.organization_id
2827 AND jrg23_ii.location_id = jrg23_i.location_id
2828 AND jrg23_ii.register_id_part_i = jrg23_i.register_id
2829 AND jmsi.organization_id = jrg23_ii.organization_id
2830 AND jmsi.item_class like 'CG%'
2831 AND jmsi.inventory_item_id = jrg23_ii.inventory_item_id
2832 AND jmsi.organization_id = p_organization_id
2833 AND jrg23_ii.organization_id = p_organization_id
2834 AND jrg23_ii.location_id = p_location_id
2835 AND trunc(jrg23_ii.creation_date) >= p_start_date
2836 AND trunc(jrg23_ii.creation_date) <= trunc(nvl(p_end_date,sysdate))
2837 AND jrg23_i.transaction_type <> 'RTV'
2838 and to_char(jrg23_ii.creation_date, 'YYYY') || to_char(jrg23_ii.creation_date, 'MM') = ln_yyyymm
2839 );
2840
2841 -- Cursors for opening Balance (Service Tax)
2842 CURSOR cur_invoice_open_bal(cp_start_date IN DATE) IS
2843 SELECT sum(recovered_amount)
2844 FROM jai_rgm_trx_refs
2845 WHERE source = 'AP'
2846 AND tax_type = 'Service'
2847 AND trunc(creation_date) < cp_start_date
2848 AND organization_id in
2849 (
2850 SELECT DISTINCT organization_id
2851 FROM jai_rgm_org_regns_v
2852 WHERE regime_code = 'SERVICE'
2853 AND registration_type = 'OTHERS'
2854 AND attribute_type_code = 'PRIMARY'
2858 );
2855 AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
2856 AND attribute_value = p_registration_number
2857 AND organization_id = nvl(p_operating_unit,organization_id)
2859
2860 CURSOR cur_dist_in(cp_start_date IN DATE) IS
2861 SELECT sum(credit_amount)
2862 FROM jai_rgm_trx_records
2863 WHERE source = 'SERVICE_DISTRIBUTE_IN'
2864 AND regime_code = 'SERVICE'
2865 AND tax_type = 'Service'
2866 AND regime_primary_regno = p_registration_number
2867 AND organization_id = nvl(p_operating_unit,organization_id)
2868 AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
2869
2870 CURSOR cur_manual_in(cp_start_date IN DATE) IS
2871 SELECT sum(credit_amount)
2872 FROM jai_rgm_trx_records
2873 WHERE source = 'MANUAL'
2874 AND regime_code = 'SERVICE'
2875 AND tax_type = 'Service'
2876 AND source_trx_type IN ('ADJUSTMENT-RECOVERY','RECOVERY')
2877 AND regime_primary_regno = p_registration_number
2878 AND organization_id = nvl(p_operating_unit,organization_id)
2879 AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
2880
2881
2882 CURSOR cur_ar_util_credit(cp_start_date IN DATE) IS
2883 SELECT SUM(recovered_amount)
2884 FROM jai_rgm_trx_refs
2885 WHERE source = 'AR'
2886 AND tax_type = 'Service'
2887 AND trunc(creation_date) < cp_start_date
2888 AND organization_id IN
2889 (
2890 SELECT DISTINCT organization_id
2891 FROM jai_rgm_org_regns_v
2892 WHERE regime_code = 'SERVICE'
2893 AND registration_type = 'OTHERS'
2894 AND attribute_type_code = 'PRIMARY'
2895 AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
2896 AND attribute_value = p_registration_number
2897 AND organization_id = nvl(p_operating_unit,organization_id)
2898 );
2899
2900 CURSOR cur_ar_ser_dist_out_debit(cp_start_date IN DATE) IS
2901 SELECT nvl(sum(debit_amount),0)
2902 FROM jai_rgm_trx_records
2903 WHERE source = 'SERVICE_DISTRIBUTE_OUT'
2904 AND regime_code = 'SERVICE'
2905 AND tax_type = 'Service'
2906 AND regime_primary_regno = p_registration_number
2907 AND organization_id = nvl(p_operating_unit,organization_id)
2908 AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
2909
2910 CURSOR cur_manual_debit(cp_start_date IN DATE) IS
2911 SELECT nvl(sum(debit_amount),0)
2912 FROM jai_rgm_trx_records
2913 WHERE source = 'MANUAL'
2914 AND regime_code = 'SERVICE'
2915 AND tax_type = 'Service'
2916 AND source_trx_type IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
2917 AND regime_primary_regno = p_registration_number
2918 AND organization_id = nvl(p_operating_unit,organization_id)
2919 AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
2920
2921 CURSOR cur_payment(cp_start_date IN DATE) IS
2922 SELECT nvl(sum(debit_amount),0)
2923 FROM jai_rgm_trx_records
2924 WHERE source = 'MANUAL'
2928 AND regime_primary_regno = p_registration_number
2925 AND regime_code = 'SERVICE'
2926 AND tax_type = ( 'Service' )
2927 AND source_trx_type = 'PAYMENT'
2929 AND organization_id = nvl(p_operating_unit,organization_id)
2930 AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
2931
2932
2933 -- Cursor for Credit availed on input services (SERVICE TAX)
2934 CURSOR cur_st_cess IS
2935 SELECT nvl(sum(service_credit),0),nvl(sum(edu_cess_credit),0)
2936 FROM (
2937 SELECT jrtf1.recovered_amount service_credit ,jrtf2.recovered_amount edu_cess_credit
2938 FROM jai_rgm_trx_refs jrtf1 ,jai_rgm_trx_refs jrtf2
2939 WHERE jrtf1.source = 'AP'
2940 AND jrtf1.invoice_id = jrtf2.invoice_id(+)
2941 AND jrtf1.tax_type = 'Service'
2942 AND jrtf2.tax_type(+) = 'SERVICE_EDUCATION_CESS'
2943 AND NVL(trunc(jrtf1.creation_date),trunc(SYSDATE)) BETWEEN p_start_date AND p_end_date
2944 AND to_char(jrtf1.creation_date, 'YYYY') || to_char(jrtf1.creation_date, 'MM') = ln_yyyymm
2945 AND jrtf1.organization_id IN
2946 (
2947 SELECT DISTINCT organization_id
2948 FROM jai_rgm_org_regns_v
2949 WHERE regime_code = 'SERVICE'
2950 AND registration_type = 'OTHERS'
2951 AND attribute_type_code = 'PRIMARY'
2952 AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
2953 AND attribute_value = p_registration_number
2954 AND organization_id = nvl(p_operating_unit,organization_id)
2955 )
2956 UNION ALL
2957 SELECT jrtr1.credit_amount service_credit ,jrtr2.credit_amount edu_cess_credit
2958 FROM jai_rgm_trx_records jrtr1,jai_rgm_trx_records jrtr2
2959 WHERE jrtr1.source = 'SERVICE_DISTRIBUTE_IN'
2960 AND jrtr1.regime_code = 'SERVICE'
2961 AND jrtr1.tax_type = 'Service'
2962 AND jrtr2.tax_type(+) = 'SERVICE_EDUCATION_CESS'
2963 AND jrtr1.organization_id = jrtr2.organization_id(+)
2964 AND jrtr1.source_document_id = jrtr2.source_document_id(+)
2965 AND jrtr1.regime_primary_regno = p_registration_number
2966 AND (NVL(trunc(jrtr1.creation_date),trunc(SYSDATE))) BETWEEN (NVL(p_start_date,trunc(jrtr1.creation_date))) AND (NVL(p_end_date,trunc(SYSDATE)))
2967 AND to_char(jrtr1.creation_date, 'YYYY') || to_char(jrtr1.creation_date, 'MM') = ln_yyyymm
2968 UNION ALL
2969 SELECT jrtr1.credit_amount service_credit ,jrtr2.credit_amount edu_cess_credit
2970 FROM jai_rgm_trx_records jrtr1,jai_rgm_trx_records jrtr2
2971 WHERE jrtr1.source = 'MANUAL'
2972 AND jrtr1.regime_code = 'SERVICE'
2973 AND jrtr1.tax_type = 'Service'
2974 AND jrtr2.tax_type(+) = 'SERVICE_EDUCATION_CESS'
2975 AND jrtr1.source_trx_type IN ('ADJUSTMENT-RECOVERY','RECOVERY')
2976 AND jrtr1.source_trx_type = jrtr2.source_trx_type(+)
2977 AND jrtr1.organization_id = jrtr2.organization_id(+)
2978 AND jrtr1.source_document_id = jrtr2.source_document_id(+)
2979 AND jrtr1.regime_primary_regno = p_registration_number
2980 AND (NVL(trunc(jrtr1.creation_date),trunc(SYSDATE))) BETWEEN p_start_date AND p_end_date
2981 AND to_char(jrtr1.creation_date, 'YYYY') || to_char(jrtr1.creation_date, 'MM') = ln_yyyymm
2982 )
2983 ;
2984
2985 -- Cursors for Credit utilised (services)
2986 CURSOR cur_ar_util_credt IS
2987 SELECT SUM(recovered_amount) FROM jai_rgm_trx_refs
2988 WHERE source = 'AR'
2989 AND tax_type = 'Service'
2990 AND organization_id IN
2991 (
2992 SELECT DISTINCT organization_id
2993 FROM jai_rgm_org_regns_v
2994 WHERE regime_code = 'SERVICE'
2995 AND registration_type = 'OTHERS'
2996 AND attribute_type_code = 'PRIMARY'
2997 AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
2998 AND attribute_value = p_registration_number
2999 AND organization_id = nvl(p_operating_unit,organization_id)
3000 )
3001 AND (NVL(TRUNC(creation_date),SYSDATE)) BETWEEN (NVL(p_start_date,SYSDATE)) AND (NVL(p_end_date,SYSDATE))
3002 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
3003 ;
3004
3005
3006 CURSOR cur_ar_ser_dist_out_debt IS
3007 SELECT nvl(sum(debit_amount),0) FROM jai_rgm_trx_records
3008 WHERE source = 'SERVICE_DISTRIBUTE_OUT'
3009 AND regime_code = 'SERVICE'
3010 AND tax_type = 'Service'
3011 AND regime_primary_regno = p_registration_number
3012 AND organization_id = nvl(p_operating_unit,organization_id)
3013 AND (NVL(TRUNC(creation_date),SYSDATE)) BETWEEN (NVL(p_start_date,SYSDATE)) AND (NVL(p_end_date,SYSDATE))
3014 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
3015 ;
3016
3017
3018 CURSOR cur_manual_debt IS
3019 SELECT nvl(sum(debit_amount),0) FROM jai_rgm_trx_records
3020 WHERE source = 'MANUAL'
3021 AND regime_code = 'SERVICE'
3022 AND tax_type = 'Service'
3023 AND source_trx_type IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
3024 AND regime_primary_regno = p_registration_number
3025 AND organization_id = nvl(p_operating_unit,organization_id)
3026 AND (NVL(TRUNC(creation_date),SYSDATE)) BETWEEN (NVL(p_start_date,SYSDATE)) AND (NVL(p_end_date,SYSDATE))
3027 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
3031
3028 ;
3029
3030
3032 CURSOR cur_paymnt IS
3033 SELECT nvl(sum(debit_amount),0)
3034 FROM jai_rgm_trx_records
3035 WHERE source = 'MANUAL'
3036 AND regime_code = 'SERVICE'
3037 AND tax_type = 'Service'
3038 AND source_trx_type = 'PAYMENT'
3039 AND regime_primary_regno = p_registration_number
3040 AND organization_id = nvl(p_operating_unit,organization_id)
3041 AND (NVL(TRUNC(creation_date),SYSDATE)) BETWEEN (NVL(p_start_date,SYSDATE)) AND (NVL(p_end_date,SYSDATE))
3042 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
3043 ;
3044
3045
3046 -- Cursors for opening balance
3047 CURSOR cur_invoice_open_bal1(cp_start_date IN DATE) IS
3048 SELECT sum(recovered_amount) FROM jai_rgm_trx_refs
3049 WHERE source = 'AP'
3050 AND tax_type = 'SERVICE_EDUCATION_CESS'
3051 AND trunc(creation_date) < cp_start_date
3052 AND organization_id in
3053 (
3054 SELECT DISTINCT organization_id
3055 FROM jai_rgm_org_regns_v
3056 WHERE regime_code = 'SERVICE'
3057 AND registration_type = 'OTHERS'
3058 AND attribute_type_code = 'PRIMARY'
3059 AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
3060 AND attribute_value = p_registration_number
3061 AND organization_id = nvl(p_operating_unit,organization_id)
3062 );
3063
3064 CURSOR cur_dist_in1(cp_start_date IN DATE) IS
3065 SELECT sum(credit_amount) FROM jai_rgm_trx_records
3066 WHERE source = 'SERVICE_DISTRIBUTE_IN'
3067 AND regime_code = 'SERVICE'
3068 AND tax_type = 'SERVICE_EDUCATION_CESS'
3069 AND regime_primary_regno = p_registration_number
3070 AND organization_id = nvl(p_operating_unit,organization_id)
3071 AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
3072
3073 CURSOR cur_manual_in1(cp_start_date IN DATE) IS
3074 SELECT sum(credit_amount)
3075 FROM jai_rgm_trx_records
3076 WHERE source = 'MANUAL'
3077 AND regime_code = 'SERVICE'
3078 AND tax_type = 'SERVICE_EDUCATION_CESS'
3079 AND source_trx_type IN ('ADJUSTMENT-RECOVERY','RECOVERY')
3080 AND regime_primary_regno = p_registration_number
3081 AND organization_id = nvl(p_operating_unit,organization_id)
3082 AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
3083
3084
3085 CURSOR cur_ar_util_credit1(cp_start_date IN DATE) IS
3086 SELECT SUM(recovered_amount)
3087 FROM jai_rgm_trx_refs
3088 WHERE source = 'AR'
3089 AND tax_type = 'SERVICE_EDUCATION_CESS'
3090 AND trunc(creation_date) < cp_start_date
3091 AND organization_id IN
3092 (
3093 SELECT DISTINCT organization_id
3094 FROM jai_rgm_org_regns_v
3095 WHERE regime_code = 'SERVICE'
3096 AND registration_type = 'OTHERS'
3097 AND attribute_type_code = 'PRIMARY'
3098 AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
3099 AND attribute_value = p_registration_number
3100 AND organization_id = nvl(p_operating_unit,organization_id)
3101 );
3102
3103
3104
3105 CURSOR cur_ar_ser_dist_out_debit1(cp_start_date IN DATE) IS
3106 SELECT nvl(sum(debit_amount),0)
3107 FROM jai_rgm_trx_records
3108 WHERE source = 'SERVICE_DISTRIBUTE_OUT'
3109 AND regime_code = 'SERVICE'
3110 AND tax_type = 'SERVICE_EDUCATION_CESS'
3111 AND regime_primary_regno = p_registration_number
3112 AND organization_id = nvl(p_operating_unit,organization_id)
3113 AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
3114
3115 CURSOR cur_manual_debit1(cp_start_date IN DATE) IS
3116 SELECT nvl(sum(debit_amount),0)
3117 FROM jai_rgm_trx_records
3118 WHERE source = 'MANUAL'
3119 AND regime_code = 'SERVICE'
3120 AND tax_type = 'SERVICE_EDUCATION_CESS'
3121 AND source_trx_type IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
3122 AND regime_primary_regno = p_registration_number
3123 AND organization_id = nvl(p_operating_unit,organization_id)
3124 AND (NVL(trunc(creation_date),trunc(SYSDATE))) <(NVL(cp_start_date,trunc(sysdate)));
3125
3126 CURSOR cur_payment1(cp_start_date IN DATE) IS
3127 SELECT nvl(sum(debit_amount),0)
3128 FROM jai_rgm_trx_records
3129 WHERE source = 'MANUAL'
3130 AND regime_code = 'SERVICE'
3131 AND tax_type = 'SERVICE_EDUCATION_CESS'
3132 AND source_trx_type = 'PAYMENT'
3133 AND regime_primary_regno = p_registration_number
3134 AND organization_id = nvl(p_operating_unit,organization_id)
3135 AND (NVL(trunc(creation_date),trunc(SYSDATE))) < (NVL(cp_start_date,trunc(sysdate)));
3136
3137 -- Cursors for the Credit utilised (services)
3138 CURSOR cur_ar_util_credt1 IS
3139 SELECT SUM(recovered_amount) FROM jai_rgm_trx_refs
3140 WHERE source = 'AR'
3141 AND tax_type = 'SERVICE_EDUCATION_CESS'
3142 AND organization_id IN
3143 (
3144 SELECT DISTINCT organization_id
3145 FROM jai_rgm_org_regns_v
3146 WHERE regime_code = 'SERVICE'
3150 AND attribute_value = p_registration_number
3147 AND registration_type = 'OTHERS'
3148 AND attribute_type_code = 'PRIMARY'
3149 AND attribute_code = 'SERVICE_TAX_REGISTRATION_NO'
3151 AND organization_id = nvl(p_operating_unit,organization_id)
3152 )
3153 AND (NVL(TRUNC(creation_date),SYSDATE)) BETWEEN (NVL(p_start_date,SYSDATE)) AND (NVL(p_end_date,SYSDATE))
3154 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm ;
3155
3156
3157 CURSOR cur_ar_ser_dist_out_debt1 IS
3158 SELECT nvl(sum(debit_amount),0) FROM jai_rgm_trx_records
3159 WHERE source = 'SERVICE_DISTRIBUTE_OUT'
3160 AND regime_code = 'SERVICE'
3161 AND tax_type = 'SERVICE_EDUCATION_CESS'
3162 AND regime_primary_regno = p_registration_number
3163 AND organization_id = nvl(p_operating_unit,organization_id)
3164 AND (NVL(TRUNC(creation_date),SYSDATE)) BETWEEN (NVL(p_start_date,SYSDATE)) AND (NVL(p_end_date,SYSDATE))
3165 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
3166 ;
3167
3168
3169 CURSOR cur_manual_debt1 IS
3170 SELECT nvl(sum(debit_amount),0)
3171 FROM jai_rgm_trx_records
3172 WHERE source = 'MANUAL'
3173 AND regime_code = 'SERVICE'
3174 AND tax_type = 'SERVICE_EDUCATION_CESS'
3175 AND source_trx_type IN ( 'ADJUSTMENT-LIABILITY' , 'LIABILITY' )
3176 AND regime_primary_regno = p_registration_number
3177 AND organization_id = nvl(p_operating_unit,organization_id)
3178 and (nvl(trunc(creation_date),sysdate)) between (nvl(p_start_date,sysdate)) and (nvl(p_end_date,sysdate))
3179 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
3180 ;
3181
3182
3183 CURSOR cur_paymnt1 IS
3184 SELECT nvl(sum(debit_amount),0) FROM jai_rgm_trx_records
3185 WHERE source = 'MANUAL'
3186 AND regime_code = 'SERVICE'
3187 AND tax_type = 'SERVICE_EDUCATION_CESS'
3188 AND source_trx_type = 'PAYMENT'
3189 AND regime_primary_regno = p_registration_number
3190 AND organization_id = nvl(p_operating_unit,organization_id)
3191 and (nvl(trunc(creation_date),sysdate)) between (nvl(p_start_date,sysdate)) and (nvl(p_end_date,sysdate))
3192 AND to_char(creation_date, 'YYYY') || to_char(creation_date, 'MM') = ln_yyyymm
3193 ;
3194
3195 CURSOR cur_dtls(
3196 p_location_id IN NUMBER,
3197 p_organization_id IN NUMBER,
3198 p_start_date IN DATE,
3199 p_end_date IN DATE)
3200 IS
3201 SELECT
3202 SUM(DECODE(register_type, 'A', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)) credit_availed_on_inputs,
3203 ROUND(SUM(DECODE(register_type, 'C', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)), 0) credit_availed_on_cap_goods,
3204 ROUND(SUM(NVL(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)), 0) total_credit_availed,
3205 ROUND(SUM(nvl(dr_basic_ed,0) + nvl(dr_additional_ed,0) + nvl(dr_other_ed,0)), 0) credit_utilized ,
3206 to_char(creation_date, 'YYYY') year,
3207 to_char(creation_date, 'MM') month
3208 FROM JAI_CMN_RG_23AC_II_TRXS
3209 WHERE location_id = p_location_id
3210 AND organization_id = p_organization_id
3211 AND trunc(creation_date) >= p_start_date
3212 AND trunc(creation_date) <= trunc(nvl(p_end_date,sysdate))
3213 group by
3214 to_char(creation_date, 'MM'),
3215 to_char(creation_date, 'YYYY')
3216 ORDER BY
3217 to_char(creation_date, 'YYYY'),
3218 to_char(creation_date, 'MM') ;
3219
3220 lv_date_format VARCHAR2(10) ;
3221 ld_start_date VARCHAR2(10) ;
3222
3223 BEGIN
3224
3225 lv_date_format := 'DD/MM/YYYY' ;
3226 lv_record_header := 'CENVAT_CREDIT_DETAIL' ;
3227 lv_rt_type := 1 ;
3228 lv_return_no := 1 ;
3229 lv_data_prd_type := 'M' ;
3230
3231 FOR dtls IN cur_dtls( p_location_id, p_organization_id,p_start_date,p_end_date)
3232
3233 LOOP
3234
3235 ln_op_bal_cenvat := NULL;
3236 ln_credit_input_cenvat := NULL;
3237 ln_credit_input_dlr_cenvat := NULL;
3238 ln_credit_capital_cenvat := NULL;
3239 ln_credit_total_cenvat := NULL;
3240 ln_credit_utilised_ic_cenvat := NULL;
3241 ln_credit_utilised_cenvat := NULL;
3242 ln_clos_bal_cenvat := NULL;
3243 ln_op_bal_edu_cess := NULL;
3244 ln_credit_input_edu_cess := NULL;
3245 ln_credit_input_dlr_edu_cess := NULL;
3246 ln_credit_capital_edu_cess := NULL;
3247 ln_credit_total_edu_cess := NULL;
3248 ln_credit_utilised_ic_edu_cess := NULL;
3249 ln_credit_utilised_edu_cess := NULL;
3250 ln_clos_bal_edu_cess := NULL;
3251 ln_op_bal_st := NULL;
3252 ln_credit_service_st := NULL;
3253 ln_credit_total_st := NULL;
3254 ln_credit_utilised_ds_st := NULL;
3255 ln_clos_bal_st := NULL;
3256 ln_op_bal_st_edu_cess := NULL;
3257 ln_credit_service_st_edu_cess := NULL;
3258 ln_credit_total_st_edu_cess := NULL;
3259 ln_credit_uti_ds_st_edu_cess := NULL;
3260 ln_clos_bal_st_edu_cess := NULL;
3261 -- ln_closed_input_manf := NULL;
3262 -- ln_closed_input_manf_iso := NULL;
3263 -- ln_closed_input_cust := NULL;
3264 ln_closed_input_stg := NULL;
3265 ln_closed_input_stg_iso := NULL;
3269 ln_edu_cess_excise_manf_iso := NULL;
3266 ln_rtv_amount := NULL;
3267 ln_cgin_sale_amt := NULL;
3268 ln_edu_cess_excise_manf := NULL;
3270 ln_edu_cess_excise_cust := NULL;
3271 ln_edu_cess_excise_stg := NULL;
3272 ln_edu_cess_excise_stg_iso := NULL;
3273 ln_edu_cess_excise := NULL;
3274 ln_rtv_cess := NULL;
3275 ln_cgin_sales_cess := NULL;
3276 lv_inv_open_bal := NULL;
3277 lv_open_dist_bal := NULL;
3278 lv_ar_util_credit := NULL;
3279 lv_ar_ser_dist_out_debit := NULL;
3280 lv_manual_bal := NULL;
3281 lv_manual_debit_bal := NULL;
3282 lv_manual_payment := NULL;
3283 lv_st_credit_avld := NULL;
3284 lv_cess_credit_avld := NULL;
3285 ln_ar_util_credit := NULL;
3286 ln_ar_ser_dist_out_debit := NULL;
3287 lv_manual_debit := NULL;
3288 lv_payment := NULL;
3289 ln_yyyymm := NULL;
3290 ld_start_date := NULL;
3291
3292
3293
3294 ln_yyyymm := dtls.year || dtls.month ;
3295 ld_start_date := to_date('01-'|| dtls.month || '-' || dtls.year,'DD/MM/YYYY') ;
3296
3297
3298 -- for cenvat opening balance
3299 OPEN cur_opening_balance_cenvat(ld_start_date) ;
3300 FETCH cur_opening_balance_cenvat INTO ln_op_bal_cenvat;
3301 CLOSE cur_opening_balance_cenvat;
3302
3303 -- for Credit availed on Input on invoices issued by manufactureres
3304 OPEN Cur_crdit_input_manf;
3305 FETCH Cur_crdit_input_manf INTO ln_closed_input_manf;
3306 CLOSE Cur_crdit_input_manf;
3307
3308 OPEN Cur_crdit_input_manf_iso;
3309 FETCH Cur_crdit_input_manf_iso INTO ln_closed_input_manf_iso;
3310 CLOSE Cur_crdit_input_manf_iso;
3311
3312 OPEN Cur_crdit_input_cust;
3313 FETCH Cur_crdit_input_cust INTO ln_closed_input_cust;
3314 CLOSE Cur_crdit_input_cust;
3315
3316 ln_credit_input_cenvat := ROUND(NVL(ln_closed_input_manf, 0) + NVL(ln_closed_input_manf_iso, 0) + NVL(ln_closed_input_cust, 0) ,0);
3317
3318 -- for Credit availed on Input on invoices issued by I or II stage dealers
3319 OPEN Cur_crdit_input_stg;
3320 FETCH Cur_crdit_input_stg INTO ln_closed_input_stg;
3321 CLOSE Cur_crdit_input_stg;
3322 OPEN Cur_crdit_input_stg_iso;
3323 FETCH Cur_crdit_input_stg_iso INTO ln_closed_input_stg_iso;
3324 CLOSE Cur_crdit_input_stg_iso;
3325 ln_credit_input_dlr_cenvat:= ROUND(NVL(ln_closed_input_stg, 0) + NVL(ln_closed_input_stg_iso, 0),0);
3326
3327 -- for Credit on Inputs
3328 --ln_credit_input_cenvat := dtls.credit_availed_on_inputs ;
3329
3330 -- for Credit on capital
3331 ln_credit_capital_cenvat := dtls.credit_availed_on_cap_goods;
3332
3333 -- for total Credit
3334 --ln_credit_total_cenvat := dtls.total_credit_availed;
3335
3336 ln_credit_total_cenvat := ln_credit_input_cenvat + ln_credit_input_dlr_cenvat + ln_credit_capital_cenvat ;
3337
3338 -- for Credit utilised (input/capital goods)
3339 OPEN get_rtv_amount;
3340 FETCH get_rtv_amount INTO ln_rtv_amount;
3341 CLOSE get_rtv_amount;
3342
3343 OPEN get_cgin_sales;
3344 FETCH get_cgin_sales INTO ln_cgin_sale_amt;
3345 CLOSE get_cgin_sales;
3346
3347 ln_credit_utilised_ic_cenvat:= ROUND( nvl(ln_rtv_amount,0) + nvl(ln_cgin_sale_amt,0));
3348
3349 -- for Credit utilised
3350 ln_credit_utilised_cenvat := round( nvl(dtls.credit_utilized,0) - nvl(ln_credit_utilised_ic_cenvat,0) );
3351
3352 --for closing balance
3353 ln_clos_bal_cenvat := round((nvl(ln_op_bal_cenvat,0) + nvl(ln_credit_total_cenvat,0) - nvl(ln_credit_utilised_cenvat,0) - nvl(ln_credit_utilised_ic_cenvat,0)),0);
3354
3355 -- for Opening Balance (EDU CESS)
3356 OPEN cur_opening_bal_edu_cess(ld_start_date);
3357 FETCH cur_opening_bal_edu_cess INTO ln_op_bal_edu_cess;
3358 CLOSE cur_opening_bal_edu_cess;
3359
3360 -- for Credit availed on Input on invoices issued by manufactureres (EDU CESS)
3361 OPEN Cur_cess_excise_input_manf;
3362 FETCH Cur_cess_excise_input_manf INTO ln_edu_cess_excise_manf;
3363 CLOSE Cur_cess_excise_input_manf;
3364 OPEN Cur_cess_excise_input_manf_iso;
3365 FETCH Cur_cess_excise_input_manf_iso INTO ln_edu_cess_excise_manf_iso;
3366 CLOSE Cur_cess_excise_input_manf_iso;
3367 OPEN Cur_cess_excise_input_cust;
3368 FETCH Cur_cess_excise_input_cust INTO ln_edu_cess_excise_cust;
3369 CLOSE Cur_cess_excise_input_cust;
3370 ln_credit_input_edu_cess := ROUND(NVL(ln_edu_cess_excise_manf, 0) + NVL(ln_edu_cess_excise_manf_iso, 0) + NVL(ln_edu_cess_excise_cust, 0), 0);
3371
3372 -- for Credit availed on Input on invoices issued by I or II stage dealers (EDU CESS)
3373 OPEN Cur_cess_excise_input_stg;
3374 FETCH Cur_cess_excise_input_stg INTO ln_edu_cess_excise_stg;
3375 CLOSE Cur_cess_excise_input_stg;
3376 OPEN Cur_cess_excise_input_stg_iso;
3377 FETCH Cur_cess_excise_input_stg_iso INTO ln_edu_cess_excise_stg_iso;
3378 CLOSE Cur_cess_excise_input_stg_iso;
3379 ln_credit_input_dlr_edu_cess := ROUND(NVL(ln_edu_cess_excise_stg, 0) + NVL(ln_edu_cess_excise_stg_iso, 0),0);
3380
3381 -- for Credit on capital (EDU CESS)
3382 OPEN cur_edu_cess_cap('C','RG23C_P2');
3383 FETCH cur_edu_cess_cap INTO ln_credit_capital_edu_cess;
3384 CLOSE cur_edu_cess_cap;
3385
3386 -- for Total Credit (EDU CESS)
3387 OPEN cur_edu_cess_cap('A','RG23A_P2');
3388 FETCH cur_edu_cess_cap INTO ln_edu_cess_excise;
3392 -- for Credit utilised (input/capital goods) (EDU CESS)
3389 CLOSE cur_edu_cess_cap;
3390 ln_credit_total_edu_cess := round((nvl( ln_edu_cess_excise,0) + nvl( ln_credit_capital_edu_cess,0)),0);
3391
3393 OPEN get_rtv_cess;
3394 FETCH get_rtv_cess INTO ln_rtv_cess;
3395 CLOSE get_rtv_cess;
3396
3397 OPEN get_cgin_sales_cess;
3398 FETCH get_cgin_sales_cess INTO ln_cgin_sales_cess;
3399 CLOSE get_cgin_sales_cess;
3400
3401 ln_credit_utilised_ic_edu_cess := round ( nvl(ln_rtv_cess,0) + nvl(ln_cgin_sales_cess,0) );
3402
3403 -- for Credit utilised(EDU CESS)
3404 OPEN cur_edu_cess_excise;
3405 FETCH cur_edu_cess_excise INTO ln_edu_cess_excise;
3406 CLOSE cur_edu_cess_excise;
3407 ln_credit_utilised_edu_cess := round( nvl(ln_edu_cess_excise,0) - nvl(ln_credit_utilised_ic_edu_cess,0),0 );
3408
3409 -- for closing balance (EDU CESS)
3410
3411 ln_clos_bal_edu_cess := round(nvl (ln_op_bal_edu_cess,0) + nvl( ln_credit_total_edu_cess,0) - nvl(ln_credit_utilised_edu_cess,0) - nvl(ln_credit_utilised_ic_edu_cess,0) ,0);
3412
3413 -- for opening Balance (Service Tax)
3414
3415 OPEN cur_invoice_open_bal(ld_start_date) ;
3416 FETCH cur_invoice_open_bal INTO lv_inv_open_bal ;
3417 CLOSE cur_invoice_open_bal ;
3418
3419 OPEN cur_dist_in(ld_start_date) ;
3420 FETCH cur_dist_in INTO lv_open_dist_bal ;
3421 CLOSE cur_dist_in ;
3422
3423 OPEN cur_manual_in(ld_start_date) ;
3424 FETCH cur_manual_in INTO lv_manual_bal ;
3425 CLOSE cur_manual_in ;
3426
3427 OPEN cur_manual_debit(ld_start_date) ;
3428 FETCH cur_manual_debit INTO lv_manual_debit_bal ;
3429 CLOSE cur_manual_debit ;
3430
3431 OPEN cur_ar_util_credit(ld_start_date) ;
3432 FETCH cur_ar_util_credit INTO lv_ar_util_credit ;
3433 CLOSE cur_ar_util_credit ;
3434
3435 OPEN cur_ar_ser_dist_out_debit(ld_start_date) ;
3436 FETCH cur_ar_ser_dist_out_debit INTO lv_ar_ser_dist_out_debit ;
3437 CLOSE cur_ar_ser_dist_out_debit ;
3438
3439 OPEN cur_payment(ld_start_date) ;
3440 FETCH cur_payment INTO lv_manual_payment ;
3441 CLOSE cur_payment ;
3442
3443 ln_op_bal_st := round(( nvl(lv_open_dist_bal,0) + nvl(lv_inv_open_bal,0) + nvl(lv_manual_bal,0) - nvl(lv_ar_util_credit,0) - nvl(lv_ar_ser_dist_out_debit,0) - nvl(lv_manual_debit_bal,0) + nvl(lv_manual_payment,0)),0) ;
3444
3445 -- for Credit availed on input services (SERVICE TAX)
3446 OPEN cur_st_cess;
3447 FETCH cur_st_cess INTO lv_st_credit_avld,lv_cess_credit_avld;
3448 CLOSE cur_st_cess;
3449 ln_credit_service_st := ROUND(lv_st_credit_avld, 0);
3450
3451 -- for total credit(SERVICE TAX)
3452 ln_credit_total_st := round((nvl( ln_op_bal_st,0 ) + nvl( ln_credit_service_st,0 )),0);
3453
3454 -- for Credit utilised (services)
3455 OPEN cur_ar_util_credt ;
3456 FETCH cur_ar_util_credt INTO ln_ar_util_credit ;
3457 CLOSE cur_ar_util_credt ;
3458
3459 OPEN cur_ar_ser_dist_out_debt ;
3460 FETCH cur_ar_ser_dist_out_debt INTO ln_ar_ser_dist_out_debit ;
3461 CLOSE cur_ar_ser_dist_out_debt ;
3462
3463 OPEN cur_manual_debt ;
3464 FETCH cur_manual_debt INTO lv_manual_debit;
3465 CLOSE cur_manual_debt ;
3466
3467 OPEN cur_paymnt ;
3468 FETCH cur_paymnt INTO lv_payment;
3469 CLOSE cur_paymnt ;
3470
3471 ln_credit_utilised_ds_st := ROUND(( nvl(ln_ar_util_credit,0) + nvl(ln_ar_ser_dist_out_debit,0) + nvl(lv_manual_debit,0) - nvl(lv_payment,0)),0);
3472
3473 -- for closing balance (SERVICE TAX)
3474
3475 ln_clos_bal_st := round((nvl(ln_op_bal_st, 0) + nvl(ln_credit_total_st,0 ) - nvl( ln_credit_utilised_ds_st,0 )),0) ;
3476
3477 -- for opening balance
3478 OPEN cur_invoice_open_bal1(ld_start_date) ;
3479 FETCH cur_invoice_open_bal1 INTO lv_inv_open_bal ;
3480 CLOSE cur_invoice_open_bal1 ;
3481
3482 OPEN cur_dist_in1(ld_start_date) ;
3483 FETCH cur_dist_in1 INTO lv_open_dist_bal ;
3484 CLOSE cur_dist_in1 ;
3485
3486 OPEN cur_manual_in1(ld_start_date) ;
3487 FETCH cur_manual_in1 INTO lv_manual_bal ;
3488 CLOSE cur_manual_in1 ;
3489
3490 OPEN cur_manual_debit1(ld_start_date) ;
3491 FETCH cur_manual_debit1 INTO lv_manual_debit_bal ;
3492 CLOSE cur_manual_debit1 ;
3493
3494 OPEN cur_ar_util_credit1(ld_start_date) ;
3495 FETCH cur_ar_util_credit1 INTO lv_ar_util_credit ;
3496 CLOSE cur_ar_util_credit1 ;
3497
3498 OPEN cur_ar_ser_dist_out_debit1(ld_start_date) ;
3499 FETCH cur_ar_ser_dist_out_debit1 INTO lv_ar_ser_dist_out_debit ;
3500 CLOSE cur_ar_ser_dist_out_debit1 ;
3501
3502 OPEN cur_payment1(ld_start_date) ;
3503 FETCH cur_payment1 INTO lv_manual_payment ;
3504 CLOSE cur_payment1 ;
3505
3506 ln_op_bal_st_edu_cess := round(( nvl(lv_open_dist_bal,0) + nvl(lv_inv_open_bal,0) + nvl(lv_manual_bal,0) - nvl(lv_ar_util_credit,0) - nvl(lv_ar_ser_dist_out_debit,0) - nvl(lv_manual_debit_bal,0) + nvl(lv_manual_payment,0)),0) ;
3507
3508 -- for Credit availed on input services
3509
3510 ln_credit_service_st_edu_cess := ROUND(lv_cess_credit_avld, 0);
3511
3512 -- for total credit
3513 ln_credit_total_st_edu_cess := round((nvl( ln_op_bal_st_edu_cess,0 ) + nvl( ln_credit_service_st_edu_cess ,0 )),0);
3514
3515 -- for the Credit utilised (services)
3516 OPEN cur_ar_util_credt1 ;
3517 FETCH cur_ar_util_credt1 INTO ln_ar_util_credit ;
3518 CLOSE cur_ar_util_credt1 ;
3519
3520 OPEN cur_ar_ser_dist_out_debt1 ;
3524 OPEN cur_manual_debt1 ;
3521 FETCH cur_ar_ser_dist_out_debt1 INTO ln_ar_ser_dist_out_debit ;
3522 CLOSE cur_ar_ser_dist_out_debt1 ;
3523
3525 FETCH cur_manual_debt1 INTO lv_manual_debit;
3526 CLOSE cur_manual_debt1 ;
3527
3528 OPEN cur_paymnt1 ;
3529 FETCH cur_paymnt1 INTO lv_payment;
3530 CLOSE cur_paymnt1 ;
3531
3532 ln_credit_uti_ds_st_edu_cess := ROUND(( nvl(ln_ar_util_credit,0) + nvl(ln_ar_ser_dist_out_debit,0) + nvl(lv_manual_debit,0) - nvl(lv_payment,0)), 0);
3533
3534 -- for closing balance
3535
3536 ln_clos_bal_st_edu_cess := round((nvl(ln_op_bal_st_edu_cess, 0) + nvl( ln_credit_total_st_edu_cess,0 ) - nvl( ln_credit_uti_ds_st_edu_cess,0 )),0) ;
3537
3538 create_cenvat_details(
3539 p_record_header => lv_record_header,
3540 p_rt_type => lv_rt_type,
3541 p_ecc => lv_ecc,
3542 p_yyyymm => ln_yyyymm,
3543 p_return_no => lv_return_no,
3544 p_data_prd_type => lv_data_prd_type,
3545 p_op_bal_cenvat => ln_op_bal_cenvat,
3546 p_credit_input_cenvat => ln_credit_input_cenvat,
3547 p_credit_input_dlr_cenvat => ln_credit_input_dlr_cenvat,
3548 p_credit_capital_cenvat => ln_credit_capital_cenvat,
3549 p_credit_service_cenvat => ln_credit_service_cenvat,
3550 p_credit_total_cenvat => ln_credit_total_cenvat,
3551 p_credit_utilised_cenvat => ln_credit_utilised_cenvat,
3552 p_credit_utilised_ic_cenvat => ln_credit_utilised_ic_cenvat,
3553 p_credit_utilised_ds_cenvat => ln_credit_utilised_ds_cenvat,
3554 p_clos_bal_cenvat => ln_clos_bal_cenvat,
3555 p_op_bal_aed_tta => ln_op_bal_aed_tta,
3556 p_credit_input_aed_tta => ln_credit_input_aed_tta,
3557 p_credit_input_dlr_aed_tta => ln_credit_input_dlr_aed_tta,
3558 p_credit_capital_aed_tta => ln_credit_capital_aed_tta,
3559 p_credit_service_aed_tta => ln_credit_service_aed_tta,
3560 p_credit_total_aed_tta => ln_credit_total_aed_tta,
3561 p_credit_utilised_aed_tta => ln_credit_utilised_aed_tta,
3562 p_credit_utilised_ic_aed_tta => ln_credit_utilised_ic_aed_tta,
3563 p_credit_utilised_ds_aed_tta => ln_credit_utilised_ds_aed_tta,
3564 p_clos_bal_aed_tta => ln_clos_bal_aed_tta,
3565 p_op_bal_aed_pmt => ln_op_bal_aed_pmt,
3566 p_credit_input_aed_pmt => ln_credit_input_aed_pmt,
3567 p_credit_input_dlr_aed_pmt => ln_credit_input_dlr_aed_pmt,
3568 p_credit_capital_aed_pmt => ln_credit_capital_aed_pmt,
3569 p_credit_service_aed_pmt => ln_credit_service_aed_pmt,
3570 p_credit_total_aed_pmt => ln_credit_total_aed_pmt,
3571 p_credit_utilised_aed_pmt => ln_credit_utilised_aed_pmt,
3572 p_credit_utilised_ic_aed_pmt => ln_credit_utilised_ic_aed_pmt,
3573 p_credit_utilised_ds_aed_pmt => ln_credit_utilised_ds_aed_pmt,
3574 p_clos_bal_aed_pmt => ln_clos_bal_aed_pmt,
3575 p_op_bal_nccd => ln_op_bal_nccd,
3576 p_credit_input_nccd => ln_credit_input_nccd,
3577 p_credit_input_dlr_nccd => ln_credit_input_dlr_nccd,
3578 p_credit_capital_nccd => ln_credit_capital_nccd,
3579 p_credit_service_nccd => ln_credit_service_nccd,
3580 p_credit_total_nccd => ln_credit_total_nccd,
3581 p_credit_utilised_nccd => ln_credit_utilised_nccd,
3582 p_credit_utilised_ic_nccd => ln_credit_utilised_ic_nccd,
3583 p_credit_utilised_ds_nccd => ln_credit_utilised_ds_nccd,
3584 p_clos_bal_nccd => ln_clos_bal_nccd,
3585 p_op_bal_adet => ln_op_bal_adet,
3586 p_credit_input_adet => ln_credit_input_adet,
3587 p_credit_input_dlr_adet => ln_credit_input_dlr_adet,
3588 p_credit_capital_adet => ln_credit_capital_adet,
3589 p_credit_service_adet => ln_credit_service_adet,
3590 p_credit_total_adet => ln_credit_total_adet,
3591 p_credit_utilised_adet => ln_credit_utilised_adet,
3592 p_credit_utilised_ic_adet => ln_credit_utilised_ic_adet,
3593 p_credit_utilised_ds_adet => ln_credit_utilised_ds_adet,
3594 p_clos_bal_adet => ln_clos_bal_adet,
3595 p_op_bal_edu_cess => ln_op_bal_edu_cess,
3596 p_credit_input_edu_cess => ln_credit_input_edu_cess,
3597 p_credit_input_dlr_edu_cess => ln_credit_input_dlr_edu_cess,
3598 p_credit_capital_edu_cess => ln_credit_capital_edu_cess,
3599 p_credit_service_edu_cess => ln_credit_service_edu_cess,
3600 p_credit_total_edu_cess => ln_credit_total_edu_cess,
3601 p_credit_utilised_edu_cess => ln_credit_utilised_edu_cess,
3602 p_credit_utilised_ic_edu_cess=> ln_credit_utilised_ic_edu_cess,
3603 p_credit_utilised_ds_edu_cess=> ln_credit_utilised_ds_edu_cess,
3604 p_clos_bal_edu_cess => ln_clos_bal_edu_cess,
3605 p_op_bal_st => ln_op_bal_st,
3606 p_credit_input_st => ln_credit_input_st,
3607 p_credit_input_dlr_st => ln_credit_input_dlr_st,
3608 p_credit_capital_st => ln_credit_capital_st,
3609 p_credit_service_st => ln_credit_service_st,
3610 p_credit_total_st => ln_credit_total_st,
3611 p_credit_utilised_st => ln_credit_utilised_st,
3612 p_credit_utilised_ic_st => ln_credit_utilised_ic_st,
3613 p_credit_utilised_ds_st => ln_credit_utilised_ds_st,
3614 p_clos_bal_st => ln_clos_bal_st,
3618 p_credit_capital_st_edu_cess => ln_credit_capital_st_edu_cess,
3615 p_op_bal_st_edu_cess => ln_op_bal_st_edu_cess,
3616 p_credit_input_st_edu_cess => ln_credit_input_st_edu_cess,
3617 p_cre_input_dlr_st_edu_cess => ln_cre_input_dlr_st_edu_cess,
3619 p_credit_service_st_edu_cess => ln_credit_service_st_edu_cess,
3620 p_credit_total_st_edu_cess => ln_credit_total_st_edu_cess,
3621 p_creln_dit_uti_st_edu_cess => ln_creln_dit_uti_st_edu_cess,
3622 p_credit_uti_ic_st_edu_cess => ln_credit_uti_ic_st_edu_cess,
3623 p_credit_uti_ds_st_edu_cess => ln_credit_uti_ds_st_edu_cess,
3624 p_clos_bal_st_edu_cess => ln_clos_bal_st_edu_cess); -- procedure for formatting and adding the value in flat file
3625
3626 END LOOP;
3627
3628 END populate_cenvat_credit_details;
3629
3630 -- to populate input details
3631 PROCEDURE populate_input_details
3632 ( p_end_date IN DATE,
3633 p_location_id IN NUMBER,
3634 p_organization_id IN NUMBER,
3635 p_start_date IN DATE
3636 )
3637 IS
3638 lv_record_header varchar2(50);
3639 lv_rt_type varchar2(2);
3640 p_ecc varchar2(15);
3641 ln_yyyymm number;
3642 lv_return_no varchar2(3);
3643 lv_ceth varchar2(8);
3644 lv_ctsh varchar2(8);
3645 lv_uqc varchar2(8);
3646 ln_ln_total_qty_recd number;
3647 ln_value_good_recd number;
3648 lv_notf_no varchar2(8);
3649 lv_notf_sno varchar2(10);
3650
3651 Cursor c_year_month
3652 is
3653 select to_char(p_start_date, 'YYYYMM') from dual ;
3654
3655 Cursor c_cur_dtls
3656 is
3657 select
3658 sum(nvl(basic_ed,0) + nvl(additional_ed,0) + nvl(other_ed,0)) total_value,
3659 sum(nvl(quantity_received,0)) total_quantity,
3660 msi.attribute4 item_tariff,
3661 msi.primary_uom_code
3662 from
3663 JAI_CMN_RG_23AC_I_TRXS jrp,
3664 mtl_system_items msi
3665 where
3666 jrp.organization_id = msi.organization_id
3667 and jrp.inventory_item_id = msi.inventory_item_id
3668 and jrp.location_id = p_location_id
3669 AND jrp.organization_id = p_organization_id
3670 AND trunc(jrp.creation_date) >= p_start_date
3671 AND trunc(jrp.creation_date) <= trunc(nvl(p_end_date,sysdate))
3672 group by
3673 to_char(jrp.creation_date, 'MM'),
3674 to_char(jrp.creation_date, 'YYYY'),
3675 msi.attribute4 , -- group by Item Tariff Head
3676 msi.primary_uom_code
3677 ORDER BY
3678 to_char(jrp.creation_date, 'YYYY'),
3679 to_char(jrp.creation_date, 'MM') ;
3680
3681 BEGIN
3682
3683 lv_record_header := 'INPUT_DETAIL' ;
3684 lv_rt_type := 1 ;
3685 lv_return_no := 1 ;
3686
3687 open c_year_month ;
3688 fetch c_year_month into ln_yyyymm ;
3689 close c_year_month ;
3690
3691 FOR rec in c_cur_dtls
3692 LOOP
3693 lv_ceth := null ;
3694 lv_uqc := null ;
3695 ln_ln_total_qty_recd := null ;
3696 ln_value_good_recd := null ;
3697
3698 FND_FILE.put_line(FND_FILE.log, ' rec.total_quantity : ' || rec.total_quantity || ' rec.total_value : ' || rec.total_value ) ;
3699
3700 lv_ceth := substr(rec.item_tariff,1,8) ;
3701 lv_uqc := substr(rec.primary_uom_code,1,8) ;
3702 FND_FILE.put_line(FND_FILE.log, '1' ) ;
3703 ln_ln_total_qty_recd := rec.total_quantity ;
3704 FND_FILE.put_line(FND_FILE.log, '2' ) ;
3705 ln_value_good_recd := rec.total_value ;
3706
3707 FND_FILE.put_line(FND_FILE.log, '3' ) ;
3708 create_input_details(
3709 p_record_header => lv_record_header,
3710 p_rt_type => lv_rt_type,
3711 p_ecc => lv_ecc,
3712 p_yyyymm => ln_yyyymm,
3713 p_return_no => lv_return_no,
3714 p_ceth => lv_ceth,
3715 p_ctsh => lv_ctsh,
3716 p_uqc => lv_uqc,
3717 p_ln_total_qty_recd => ln_ln_total_qty_recd,
3718 p_value_good_recd => ln_value_good_recd,
3719 p_notf_no => lv_notf_no,
3720 p_notf_sno => lv_notf_sno);
3721
3722 FND_FILE.put_line(FND_FILE.log, '4' ) ;
3723 END LOOP ;
3724
3725 END populate_input_details;
3726
3727 -- to populate payment details
3728 PROCEDURE populate_payment_details
3729 (p_start_date in DATE
3730 )
3731 IS
3732 lv_record_header varchar2(50);
3733 lv_rt_type varchar2(2);
3734 p_ecc varchar2(15);
3735 ln_yyyymm number;
3736 lv_return_no varchar2(3);
3737 ln_arrear_rule8_current number;
3738 ln_arrear_rule8_credit number;
3739 lv_arrear_rule8_challan_no varchar2(10);
3740 ld_arrear_rule8_challan_date date;
3741 lv_arrear_rule8_bank_code varchar2(7);
3742 lv_arrear_rule8_source_no varchar2(40);
3743 ld_arrear_rule8_source_date date;
3744 ln_arrear_current number;
3745 ln_arrear_credit number;
3746 lv_arrear_challan_no varchar2(10);
3747 ld_arrear_challan_date date;
3748 lv_arrear_bank_code varchar2(7);
3749 lv_arrear_source_no varchar2(40);
3750 ld_arrear_source_date date;
3751 ln_int_rule8_current number;
3755 lv_int_rule8_bank_code varchar2(7);
3752 ln_int_rule8_credit number;
3753 lv_int_rule8_challan_no varchar2(10);
3754 ld_int_rule8_challan_date date;
3756 lv_int_rule8_source_no varchar2(40);
3757 ld_int_rule8_source_date date;
3758 ln_int_current number;
3759 ln_int_credit number;
3760 lv_int_challan_no varchar2(10);
3761 ld_int_challan_date date;
3762 lv_int_bank_code varchar2(7);
3763 lv_int_source_no varchar2(40);
3764 ld_int_source_date date;
3765 ln_misc_current number;
3766 ln_misc_credit number;
3767 lv_misc_challan_no varchar2(10);
3768 ld_misc_challan_date date;
3769 lv_misc_bank_code varchar2(7);
3770 lv_misc_source_no varchar2(40);
3771 ld_misc_source_date date;
3772
3773 Cursor c_year_month
3774 is
3775 select to_char(p_start_date, 'YYYYMM') from dual ;
3776
3777 BEGIN
3778
3779 lv_record_header := 'PAYMENT_DETAIL' ;
3780 lv_rt_type := 1 ;
3781 lv_return_no := 1 ;
3782
3783 open c_year_month ;
3784 fetch c_year_month into ln_yyyymm ;
3785 close c_year_month ;
3786
3787 ln_arrear_rule8_current := 0 ;
3788 ln_arrear_rule8_credit := 0 ;
3789
3790 ln_arrear_current := 0 ;
3791 ln_arrear_credit := 0 ;
3792
3793 create_payment_details(
3794 p_record_header => lv_record_header,
3795 p_rt_type => lv_rt_type,
3796 p_ecc => lv_ecc,
3797 p_yyyymm => ln_yyyymm,
3798 p_return_no => lv_return_no,
3799 p_arrear_rule8_current => ln_arrear_rule8_current,
3800 p_arrear_rule8_credit => ln_arrear_rule8_credit,
3801 p_arrear_rule8_challan_no => lv_arrear_rule8_challan_no,
3802 p_arrear_rule8_challan_date => ld_arrear_rule8_challan_date,
3803 p_arrear_rule8_bank_code => lv_arrear_rule8_bank_code,
3804 p_arrear_rule8_source_no => lv_arrear_rule8_source_no,
3805 p_arrear_rule8_source_date => ld_arrear_rule8_source_date,
3806 p_arrear_current => ln_arrear_current,
3807 p_arrear_credit => ln_arrear_credit,
3808 p_arrear_challan_no => lv_arrear_challan_no,
3809 p_arrear_challan_date => ld_arrear_challan_date,
3810 p_arrear_bank_code => lv_arrear_bank_code,
3811 p_arrear_source_no => lv_arrear_source_no,
3812 p_arrear_source_date => ld_arrear_source_date,
3813 p_int_rule8_current => ln_int_rule8_current,
3814 p_int_rule8_credit => ln_int_rule8_credit,
3815 p_int_rule8_challan_no => lv_int_rule8_challan_no,
3816 p_int_rule8_challan_date => ld_int_rule8_challan_date,
3817 p_int_rule8_bank_code => lv_int_rule8_bank_code,
3818 p_int_rule8_source_no => lv_int_rule8_source_no,
3819 p_int_rule8_source_date => ld_int_rule8_source_date,
3820 p_int_current => ln_int_current,
3821 p_int_credit => ln_int_credit,
3822 p_int_challan_no => lv_int_challan_no,
3823 p_int_challan_date => ld_int_challan_date,
3824 p_int_bank_code => lv_int_bank_code,
3825 p_int_source_no => lv_int_source_no,
3826 p_int_source_date => ld_int_source_date,
3827 p_misc_current => ln_misc_current,
3828 p_misc_credit => ln_misc_credit,
3829 p_misc_challan_no => lv_misc_challan_no,
3830 p_misc_challan_date => ld_misc_challan_date,
3831 p_misc_bank_code => lv_misc_bank_code,
3832 p_misc_source_no => lv_misc_source_no,
3833 p_misc_source_date => ld_misc_source_date);
3834
3835 END populate_payment_details;
3836
3837 -- to populate self assesment memorandum details
3838 PROCEDURE populate_sam_details
3839 (
3840 p_organization_id IN NUMBER,
3841 p_location_id IN NUMBER,
3842 p_start_date IN DATE,
3843 p_end_date IN DATE,
3844 p_auth_sign IN VARCHAR2,
3845 p_place IN VARCHAR2
3846 )
3847 IS
3848 lv_record_header varchar2(50);
3849 lv_rt_type varchar2(2);
3850 p_ecc varchar2(15);
3851 ln_yyyymm number;
3852 lv_return_no varchar2(3);
3853 ln_tr6_total_amount number;
3854 lv_inv_issue_from1 varchar2(10);
3855 lv_inv_issue_to1 varchar2(10);
3856 lv_inv_issue_from2 varchar2(10);
3857 lv_inv_issue_to2 varchar2(10);
3858 lv_inv_issue_from3 varchar2(10);
3859 lv_inv_issue_to3 varchar2(10);
3860 lv_inv_issue_from4 varchar2(10);
3861 lv_inv_issue_to4 varchar2(10);
3862 lv_inv_issue_from5 varchar2(10);
3863 lv_inv_issue_to5 varchar2(10);
3864 lv_inv_issue_from6 varchar2(10);
3865 lv_inv_issue_to6 varchar2(10);
3866 lv_remarks varchar2(255);
3867 lv_place varchar2(20);
3868 ld_date_filing date;
3869 lv_name_auth_sign varchar2(40);
3870
3871 Cursor c_year_month
3872 is
3873 select to_char(p_start_date, 'YYYYMM') from dual ;
3874
3875 Cursor c_tr6_challan_amt
3876 is
3877 select NVL(SUM(pla_amount),0)
3881 and trunc(a.creation_date) >= p_start_date
3878 from JAI_CMN_RG_PLA_HDRS a
3879 where a.organization_id = p_organization_id
3880 and a.location_id = p_location_id
3882 and trunc(a.creation_date) <= p_end_date
3883 and a.ACK_RECVD_FLAG = 'Y';
3884
3885 BEGIN
3886
3887 lv_record_header := 'SELF_ASSESMENT_MEMO_DETAIL' ;
3888 lv_rt_type := 1 ;
3889 lv_return_no := 1 ;
3890
3891 open c_year_month ;
3892 fetch c_year_month into ln_yyyymm ;
3893 close c_year_month ;
3894
3895 open c_tr6_challan_amt ;
3896 fetch c_tr6_challan_amt into ln_tr6_total_amount ;
3897 close c_tr6_challan_amt ;
3898
3899 lv_name_auth_sign := substr(p_auth_sign,1,40) ;
3900 lv_place := substr(p_place,1,20) ;
3901
3902 create_sam_details(
3903 p_record_header => lv_record_header,
3904 p_rt_type => lv_rt_type,
3905 p_ecc => lv_ecc,
3906 p_yyyymm => ln_yyyymm,
3907 p_return_no => lv_return_no,
3908 p_tr6_total_amount => ln_tr6_total_amount,
3909 p_inv_issue_from1 => lv_inv_issue_from1,
3910 p_inv_issue_to1 => lv_inv_issue_to1,
3911 p_inv_issue_from2 => lv_inv_issue_from2,
3912 p_inv_issue_to2 => lv_inv_issue_to2,
3913 p_inv_issue_from3 => lv_inv_issue_from3,
3914 p_inv_issue_to3 => lv_inv_issue_to3,
3915 p_inv_issue_from4 => lv_inv_issue_from4,
3916 p_inv_issue_to4 => lv_inv_issue_to4,
3917 p_inv_issue_from5 => lv_inv_issue_from5,
3918 p_inv_issue_to5 => lv_inv_issue_to5,
3919 p_inv_issue_from6 => lv_inv_issue_from6,
3920 p_inv_issue_to6 => lv_inv_issue_to6,
3921 p_remarks => lv_remarks,
3922 p_place => lv_place,
3923 p_date_filing => ld_date_filing,
3924 p_name_auth_sign => lv_name_auth_sign);
3925
3926 END populate_sam_details;
3927
3928
3929
3930 END jai_ar_eer1_pkg;