[Home] [Help]
PACKAGE: APPS.PAY_IN_EOY_ER_RETURNS
Source
1 PACKAGE pay_in_eoy_er_returns AS
2 /* $Header: pyinerit.pkh 120.3 2006/04/17 04:01 vgsriniv noship $ */
3
4
5 level_cnt NUMBER;
6
7 --------------------------------------------------------------------------
8 -- --
9 -- Name : CHALLAN_REC_COUNT --
10 -- Type : FUNCTION --
11 -- Access : Public --
12 -- Description : This function returns the Total number of records --
13 -- in the Challan Details of the Magtape --
14 -- Parameters : --
15 -- IN : p_gre_org_id VARCHAR2 --
16 -- p_assess_year VARCHAR2 --
17 --------------------------------------------------------------------------
18 FUNCTION challan_rec_count (p_gre_org_id IN VARCHAR2
19 ,p_assess_year IN VARCHAR2)
20 RETURN VARCHAR2;
21
22 --------------------------------------------------------------------------
23 -- --
24 -- Name : DEDUCTEE_REC_COUNT --
25 -- Type : FUNCTION --
26 -- Access : Public --
27 -- Description : This function returns the Total number of records --
28 -- in the Deductee Details of the Magtape --
29 -- Parameters : --
30 -- IN : p_gre_org_id VARCHAR2 --
31 -- p_assess_year VARCHAR2 --
32 --------------------------------------------------------------------------
33 FUNCTION deductee_rec_count (p_gre_org_id IN VARCHAR2
34 ,p_assess_year IN VARCHAR2)
35 RETURN VARCHAR2;
36
37 --------------------------------------------------------------------------
38 -- --
39 -- Name : PERQ_REC_COUNT --
40 -- Type : FUNCTION --
41 -- Access : Public --
42 -- Description : This function returns the Total number of records --
43 -- in the Perquisite Details of the Magtape --
44 -- Parameters : --
45 -- IN : p_gre_org_id VARCHAR2 --
46 -- p_assess_year VARCHAR2 --
47 --------------------------------------------------------------------------
48 FUNCTION perq_rec_count (p_gre_org_id IN VARCHAR2
49 ,p_assess_year IN VARCHAR2)
50 RETURN VARCHAR2;
51
52 --------------------------------------------------------------------------
53 -- --
54 -- Name : GROSS_TOT_TDS_CHALLAN --
55 -- Type : FUNCTION --
56 -- Access : Public --
57 -- Description : This function returns the Gross Total of TDS --
58 -- deducted as per Challan details annexure --
59 -- Parameters : --
60 -- IN : p_gre_org_id VARCHAR2 --
61 -- p_assess_year VARCHAR2 --
62 --------------------------------------------------------------------------
63 FUNCTION gross_tot_tds_challan (p_gre_org_id IN VARCHAR2
64 ,p_assess_year IN VARCHAR2)
65 RETURN VARCHAR2;
66
67 --------------------------------------------------------------------------
68 -- --
69 -- Name : GROSS_TOT_TDS_DEDUCTEE --
70 -- Type : FUNCTION --
71 -- Access : Public --
72 -- Description : This function returns the Gross Total of TDS --
73 -- deducted as per Deductee details annexure --
74 -- Parameters : --
75 -- IN : p_gre_org_id VARCHAR2 --
76 -- p_assess_year VARCHAR2 --
77 --------------------------------------------------------------------------
78 FUNCTION gross_tot_tds_deductee (p_gre_org_id IN VARCHAR2
79 ,p_assess_year IN VARCHAR2)
80 RETURN VARCHAR2;
81
82 --------------------------------------------------------------------------
83 -- --
84 -- Name : GET_EOY_VALUES --
85 -- Type : FUNCTION --
86 -- Access : Public --
87 -- Description : This function returns the values corresponding to --
88 -- the F16 Balances --
89 -- Parameters : --
90 -- IN : p_category VARCHAR2 --
91 -- p_component_name VARCHAR2 --
92 -- p_context_id NUMBER --
93 -- p_segment_num NUMBER --
94 --------------------------------------------------------------------------
95 FUNCTION get_eoy_values (p_category IN VARCHAR2
96 ,p_component_name IN VARCHAR2
97 ,p_context_id IN NUMBER
98 ,p_source_id IN NUMBER
99 ,p_segment_num IN NUMBER)
100 RETURN VARCHAR2;
101
102 --------------------------------------------------------------------------
103 -- --
104 -- Name : GET_TDE_REMARKS --
105 -- Type : FUNCTION --
106 -- Access : Public --
107 -- Description : This function returns the remarks entered at the --
108 -- assignment extra Information --
109 -- Parameters : --
110 -- IN : p_person_id VARCHAR2 --
111 -- p_assess_year VARCHAR2 --
112 --------------------------------------------------------------------------
113 FUNCTION get_tde_remarks (p_person_id IN VARCHAR2
114 ,p_assess_year IN VARCHAR2
115 ,p_date IN VARCHAR2)
116 RETURN VARCHAR2;
117
118 --------------------------------------------------------------------------
119 -- --
120 -- Name : GET_EMPLOYER_CLASS --
121 -- Type : FUNCTION --
122 -- Access : Public --
123 -- Description : This function returns the employer classfication --
124 -- --
125 -- Parameters : --
126 -- IN : p_gre_org_id VARCHAR2 --
127 --------------------------------------------------------------------------
128 FUNCTION get_emplr_class (p_gre_org_id IN VARCHAR2)
129 RETURN VARCHAR2;
130
131 --------------------------------------------------------------------------
132 -- --
133 -- Name : GET_LOCATION_DETAILS --
134 -- Type : FUNCTION --
135 -- Access : Public --
136 -- Description : This function gets the gre location details --
137 -- --
138 -- Parameters : --
139 -- IN : p_location_id hr_locations.location_id --
140 -- : p_concatenate VARCHAR2 --
141 -- p_field VARCHAR2 --
142 --------------------------------------------------------------------------
143 FUNCTION get_location_details (p_location_id IN hr_locations.location_id%TYPE)
144 RETURN VARCHAR2;
145
146
147 /* FILE HEADER RECORD */
148 CURSOR c_f24_file_header IS
149 SELECT DISTINCT 'UPLOAD_TYPE=P'
150 , pay_magtape_generic.get_parameter_value('UPLOAD_TYPE_PARAM')
151 ,'TAN_OF_DED=P'
152 , LPAD(NVL(pai.action_information4, ' '),10,' ')
153 , 'MAX_ACTION_CONTEXT_ID=P'
154 , pai.action_context_id
155 , 'SUBMIT_DATE=P'
156 , TO_CHAR (SYSDATE,'DDMMYYYY')
157 FROM pay_action_information pai
158 , pay_payroll_actions ppa
159 WHERE pai.action_information_category = 'IN_EOY_ORG'
160 AND pai.action_context_type = 'PA'
161 AND pai.action_information1 = pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM')
162 AND pai.action_information3 = pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM')
163 AND ppa.action_type='X'
164 AND ppa.action_status = 'C'
165 AND ppa.report_type='IN_EOY_ARCHIVE'
166 AND ppa.report_qualifier = 'IN'
167 AND ppa.payroll_action_id = pai.action_context_id
168 AND pai.action_context_id = ( SELECT MAX(action_context_id)
169 FROM pay_action_information
170 WHERE action_information1 = pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM')
171 AND action_information3 = pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM')
172 AND action_context_type = 'PA'
173 AND action_information_category = 'IN_EOY_ORG');
174
175 /*BATCH HEADER RECORD*/
176 CURSOR c_f24_batch_header IS
177 SELECT 'TOT_CHALLAN_REC=P'
178 , pay_in_eoy_er_returns.challan_rec_count(pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM'),pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'))
179 , 'TOT_DEDUCTEE_REC=P'
180 , pay_in_eoy_er_returns.deductee_rec_count(pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM'),pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'))
181 , 'TOT_PERQ_REC=P'
182 , pay_in_eoy_er_returns.perq_rec_count(pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM'),pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'))
183 , 'TAN_OF_DED=P'
184 , LPAD(NVL(pai.action_information4,' '),10, ' ')
185 , 'PAN_OF_TAN=P'
186 , LPAD(NVL(pai.action_information2,' '),10,' ')
187 , 'ASSESS_YEAR=P'
188 , SUBSTR(pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'),1,4)||SUBSTR(pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'),8,2)
189 , 'FIN_YEAR=P'
190 , SUBSTR(pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'),1,4)-1||LPAD (SUBSTR(pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'),8,2)-1,2,'0')
191 , 'LEGAL_NAME=P'
192 , NVL(pai.action_information8,' ')
193 , 'EMPLOYER_CLASS=P'
194 , pay_in_eoy_er_returns.get_emplr_class(pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM'))
195 , 'EMPLOYER_ADD1=P'
196 , SUBSTR(pay_in_eoy_er_returns.get_location_details(pai.action_information7),1,75)
197 , 'EMPLOYER_ADD2=P'
198 , SUBSTR(pay_in_eoy_er_returns.get_location_details(pai.action_information7),76)
199 , 'EMP_ADD_CHG=P'
200 , pay_magtape_generic.get_parameter_value('EMP_ADD_CHG')
201 , 'REP_NAME=P'
202 , NVL(pai.action_information11,' ')
203 , 'REP_DESIG=P'
204 , NVL(pai.action_information13,' ')
205 , 'REP_ADD1=P'
206 , SUBSTR(pay_in_eoy_er_returns.get_location_details(pai.action_information16),1,75)
207 , 'REP_ADD2=P'
208 , SUBSTR(pay_in_eoy_er_returns.get_location_details(pai.action_information16),76)
209 , 'REP_ADD_CHG=P'
210 , pay_magtape_generic.get_parameter_value('REP_ADD_CHG')
211 , 'GROSS_TDS_CHALLAN=P'
212 , pay_in_eoy_er_returns.gross_tot_tds_challan(pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM'),pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'))
213 , 'GROSS_TDS_DED=P'
214 , pay_in_eoy_er_returns.gross_tot_tds_deductee(pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM'),pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'))
215 , 'TAN_ACK_NUM=P'
216 , NVL(pai.action_information5,'00000000000000')
217 , 'PRN=P'
218 , pay_magtape_generic.get_parameter_value('PRN')
219 FROM pay_action_information pai
220 WHERE pai.action_information_category = 'IN_EOY_ORG'
221 AND pai.action_context_type = 'PA'
222 AND pai.action_information1 = pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM')
223 AND pai.action_information3 = pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM')
224 AND pai.action_context_id = pay_magtape_generic.get_parameter_value('MAX_ACTION_CONTEXT_ID')
225 AND ROWNUM = 1;
226
227
228 /* CHALLAN DETAIL RECORD */
229 CURSOR c_f24_challan_det_rec IS
230 SELECT 'TDS_DEP=P'
231 , (NVL(it_ch.org_information4,'0')||'00')
232 , 'SURCHARGE=P'
233 , (NVL(it_ch.org_information7,'0')||'00')
234 , 'EDU_CESS=P'
235 , (NVL(it_ch.org_information8,'0')||'00')
236 , 'INTEREST=P'
237 , (NVL(it_ch.org_information9,'0')||'00')
238 , 'OTHERS=P'
239 , (NVL(it_ch.org_information10,'0')||'00')
240 , 'CHQ_DD_NUM=P'
241 , it_ch.org_information11
242 , 'BOOK_ENTRY=P'
243 , it_ch.org_information12
244 , 'CHALLAN_NUM=P'
245 , it_ch.org_information3
246 , 'CHALLAN_DATE=P'
247 , TO_CHAR(fnd_date.canonical_to_date(it_ch.org_information2),'DDMMYYYY')
248 , 'BANK_BRANCH_CODE=P'
249 , hr_general.decode_lookup('IN_BANK_BRANCH_CODES',ch_b.org_information4)
250 , fnd_date.canonical_to_date(it_ch.org_information2)
251 FROM hr_organization_information ch_b
252 , hr_organization_information it_ch
253 WHERE it_ch.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
254 AND ch_b.org_information_context = 'PER_IN_CHALLAN_BANK'
255 AND it_ch.organization_id = pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM')
259 UNION
256 AND it_ch.org_information1 = TO_CHAR((TO_NUMBER(SUBSTR(pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'),1,4)) - 1)||'-'||SUBSTR(pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'),1,4))
257 AND it_ch.organization_id = ch_b.organization_id
258 AND TO_NUMBER(it_ch.org_information5) = ch_b.org_information_id
260 SELECT 'TDS_DEP=P'
261 , '0'
262 , 'SURCHARGE=P'
263 , '0'
264 , 'EDU_CESS=P'
265 , '0'
266 , 'INTEREST=P'
267 , '0'
268 , 'OTHERS=P'
269 , '0'
270 , 'CHQ_DD_NUM=P'
271 , '0'
272 , 'BOOK_ENTRY=P'
273 , ' '
274 , 'CHALLAN_NUM=P'
275 , ' '
276 , 'CHALLAN_DATE=P'
277 , '00011900'
278 , 'BANK_BRANCH_CODE=P'
279 , ' '
280 , SYSDATE
281 FROM DUAL
282 WHERE NOT EXISTS (
283 SELECT 'EXISTS'
284 FROM hr_organization_information ch_b
285 ,hr_organization_information it_ch
286 WHERE it_ch.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
287 AND ch_b.org_information_context = 'PER_IN_CHALLAN_BANK'
288 AND it_ch.organization_id = pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM')
289 AND it_ch.org_information1 = TO_CHAR((TO_NUMBER(SUBSTR(pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'),1,4)) - 1)||'-'||SUBSTR(pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'),1,4))
290 AND it_ch.organization_id = ch_b.organization_id
291 AND TO_NUMBER(it_ch.org_information5) = ch_b.org_information_id
292 )
293 ORDER BY 21;
294
295
296 /* DEDUCTEE DETAIL RECORD */
297
298 CURSOR c_f24_ded_det_rec IS
299 SELECT 'EMP_PERSON_ID=P'
300 , action_information13
301 , 'EMP_PAN=P'
302 , NVL(DECODE (action_information4,'Y','APPLIEDFOR','N',' ',action_information4),' ')
303 , 'EMP_FULL_NAME=P'
304 , SUBSTR(action_information6||action_information5,1,80)
305 , 'EMP_START_DATE=P'
306 , TO_CHAR(fnd_date.CHARDATE_TO_DATE(action_information17),'DDMMYYYY')
307 , 'EMP_END_DATE=P'
308 , TO_CHAR(fnd_date.CHARDATE_TO_DATE(action_information18),'DDMMYYYY')
309 , 'F16_SEC17_SAL=P'
310 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Salary Under Section 17',action_context_id,source_id,2)
311 , 'F16_PROFIT_LIEU=P'
312 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Profit in lieu of Salary',action_context_id,source_id,2)
313 , 'EXCESS_INTEREST=P'
314 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','Excess Interest Amount',action_context_id,source_id,2)
315 , 'EXCESS_PF=P'
316 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','Excess PF Amount',action_context_id,source_id,2)
317 , 'F16_ALW_EXEM=P'
318 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Allowances Exempt',action_context_id,source_id,2)
319 , 'F16_DEC_SEC16=P'
320 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Deductions under Sec 16',action_context_id,source_id,2)
321 , 'F16_INC_HEAD_SAL=P'
322 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Income Chargeable Under head Salaries',action_context_id,source_id,2)
323 , 'F16_OTHER_INC=P'
324 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Other Income',action_context_id,source_id,2)
325 , 'F16_GROSS_TOT_INC=P'
326 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Gross Total Income',action_context_id,source_id,2)
327 , 'F16_TOT_INC=P'
328 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Total Income',action_context_id,source_id,2)
329 , 'F16_TAX_ON_TOT_INC=P'
330 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Tax on Total Income',action_context_id,source_id,2)
331 , 'F16_TOTAL_TAX_PAY=P'
332 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Total Tax payable',action_context_id,source_id,2)
333 , 'F16_RELIEF_89=P'
334 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Relief under Sec 89',action_context_id,source_id,2)
335 , 'F16_IT_TD=P'
336 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Income Tax till Date',action_context_id,source_id,2)
337 , 'F16_SUR_TD=P'
338 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Surcharge till Date',action_context_id,source_id,2)
339 , 'F16_EDUCESS_TD=P'
340 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Education Cess till Date',action_context_id,source_id,2)
341 , 'F16_SURCHARGE=P'
342 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Surcharge',action_context_id,source_id,2)
343 , 'F16_EDU_CESS=P'
344 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Education Cess',action_context_id,source_id,2)
345 , 'F16_BALANCE_TAX=P'
346 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Balance Tax',action_context_id,source_id,2)
347 , 'F16_TAX_REFUND=P'
348 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','F16 Tax Refundable',action_context_id,source_id,2)
349 , 'F16_DED_SEC_80G=P'
350 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_VIA','F16 Deductions Sec 80G',action_context_id,source_id,2)
351 , 'F16_DED_SEC_80GG=P'
352 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_VIA','F16 Deductions Sec 80GG',action_context_id,source_id,2)
353 , 'F16_TOT_CHAP_VIA=P'
354 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_VIA','F16 Total Chapter VI A Deductions',action_context_id,source_id,2)
355 , 'TAXABLE_ALW=P'
356 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ALLOW','Taxable Allowances',action_context_id,source_id,2)
357 , 'TAXABLE_PERQ=P'
361 , 'TAXABLE_HRA=P'
358 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Taxable Perquisites',action_context_id,source_id,2)
359 , 'REMARKS=P'
360 , pay_in_eoy_er_returns.get_tde_remarks(action_information13,pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM'),action_information18)
362 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ALLOW','House Rent Allowance',action_context_id,source_id,5)
363 FROM pay_action_information
364 WHERE action_information_category = 'IN_EOY_PERSON'
365 AND action_context_type = 'AAP'
366 AND action_information2 = pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM')
367 AND action_information3 = pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM')
368 AND action_context_id IN ( SELECT MAX(action_context_id)
369 FROM pay_action_information
370 ,pay_assignment_actions
371 WHERE action_information_category = 'IN_EOY_PERSON'
372 AND action_context_type = 'AAP'
373 AND action_information2 = pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM')
374 AND action_information3 = pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM')
375 AND source_id = assignment_action_id
376 GROUP BY action_information1,action_information17 )
377 ORDER BY LENGTH(action_information1),action_information1,source_id;
378
379
380
381 /* PERQUISITE DETAIL RECORD */
382
383 CURSOR c_f24_perq_det_rec IS
384 SELECT 'EMP_FULL_NAME=P'
385 , SUBSTR(action_information6||action_information5,1,80)
386 , 'EMP_PERSON_ID=P'
387 , action_information13
388 , 'COMP_ACC_TAX=P'
389 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Company Accommodation',action_context_id,source_id,2)
390 , 'COMP_ACC_EMP=P'
391 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Company Accommodation',action_context_id,source_id,3)
392 , 'COST_RENT_FUR=P'
393 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Cost and Rent of Furniture',action_context_id,source_id,2)
394 , 'FUR_PERQ=P'
395 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Furniture Perquisite',action_context_id,source_id,2)
396 , 'MONTHLY_FUR_CP=P'
397 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Monthly Furniture Cost',action_context_id,source_id,2)
398 , 'MEDICAL_PERQ=P'
399 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Medical',action_context_id,source_id,2)
400 , 'DOMESTIC_TAX=P'
401 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Domestic Servant',action_context_id,source_id,2)
402 , 'GWE_TAX=P'
403 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Gas / Water / Electricity',action_context_id,source_id,2)
404 , 'EDU_TAX=P'
405 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Free Education',action_context_id,source_id,2)
406 , 'LTC_TAX=P'
407 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Leave Travel Concession',action_context_id,source_id,2)
408 , 'SHARE_TAX=P'
409 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Shares',action_context_id,source_id,2)
410 , 'LCR_TAX=P'
411 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Loan at Concessional Rate',action_context_id,source_id,2)
412 , 'MOV_ASSET_TAX=P'
413 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Company Movable Assets',action_context_id,source_id,2)
414 , 'TRAN_ASSET_TAX=P'
415 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Transfer of Company Assets',action_context_id,source_id,2)
416 , 'TOT_TAXABLE_PERQ=P'
417 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_PERQ','Taxable Perquisites',action_context_id,source_id,2)
418 , 'PF_EXCESS_AMT=P'
419 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','Excess PF Amount',action_context_id,source_id,2)
420 , 'PF_EXCESS_INT=P'
421 , pay_in_eoy_er_returns.get_eoy_values('IN_EOY_ASG_SAL','Excess Interest Amount',action_context_id,source_id,2)
422 FROM pay_action_information
423 WHERE action_information_category = 'IN_EOY_PERSON'
424 AND action_context_type = 'AAP'
425 AND action_information2 = pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM')
426 AND action_information3 = pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM')
427 AND action_context_id IN ( SELECT MAX(action_context_id)
428 FROM pay_action_information
429 ,pay_assignment_actions
430 WHERE action_information_category = 'IN_EOY_PERSON'
431 AND action_context_type = 'AAP'
432 AND action_information2 = pay_magtape_generic.get_parameter_value('ASSESSMENT_YEAR_PARAM')
433 AND action_information3 = pay_magtape_generic.get_parameter_value('GRE_ORGANIZATION_PARAM')
434 AND source_id = assignment_action_id
435 GROUP BY action_information1,action_information17 )
436 ORDER BY LENGTH(action_information1),action_information1,source_id;
437
438
439
440
441 END pay_in_eoy_er_returns;