[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_YEA_MED_EFILE_CONC_PKG
Source
1 package body pay_kr_yea_med_efile_conc_pkg as
2 /*$Header: pykrymcon.pkb 120.1 2006/10/20 08:44:01 vaisriva noship $ */
3
4 /*************************************************************************
5 * Procedure to submit e-file request indirectly
6 *************************************************************************/
7
8 procedure submit_efile (errbuf out nocopy varchar2,
9 retcode out nocopy varchar2,
10 p_business_place in varchar2,
11 p_REPORT_FOR in varchar2, --5069923
12 p_magnetic_file_name in varchar2,
13 p_report_file_name in varchar2,
14 p_effective_date in varchar2,
15 p_PAYROLL_ACTION in varchar2,
16 p_ASSIGNMENT_SET in varchar2,
17 p_REPORT_TYPE in varchar2,
18 p_reported_date in varchar2,
19 p_TARGET_YEAR in varchar2,
20 p_CHARACTERSET in varchar2,
21 p_HOME_TAX_ID in varchar2,
22 p_ORG_STRUC_VERSION_ID in varchar2 --5069923
23 )
24
25 is
26
27 l_req_id number;
28 l_message varchar2(2000);
29 l_phase varchar2(100);
30 l_status varchar2(100);
31 l_action_completed boolean;
32 l_bg_id number;
33
34 begin
35 get_bg_id(p_business_place,l_bg_id);
36 l_req_id := fnd_request.submit_request (
37 APPLICATION => 'PAY'
38 ,PROGRAM => 'PYKRYEAM_MED_A'
39 ,DESCRIPTION => 'KR Detailed Medical Expense EFile - (Mag Tape)'
40 ,ARGUMENT1 => 'pay_magtape_generic.new_formula'
41 ,ARGUMENT2 => p_magnetic_file_name
42 ,ARGUMENT3 => p_report_file_name
43 ,ARGUMENT4 => p_effective_date
44 ,ARGUMENT5 => 'MAGTAPE_REPORT_ID=KR_YEA_MED_EFILE'
45 ,ARGUMENT6 => 'PRIMARY_BP_ID=' || p_business_place
46 ,ARGUMENT7 => 'REPORTED_DATE=' || p_reported_date
47 ,ARGUMENT8 => 'PAYROLL_ACTION_ID=' || p_PAYROLL_ACTION
48 ,ARGUMENT9 => 'ASSIGNMENT_SET_ID=' || p_ASSIGNMENT_SET
49 ,ARGUMENT10 => 'REPORT_TYPE=' || p_REPORT_TYPE
50 ,ARGUMENT11 => 'REPORT_DATE=' || p_reported_date
51 ,ARGUMENT12 => 'TARGET_YEAR=' || p_TARGET_YEAR
52 ,ARGUMENT13 => 'CHARACTER_SET=' || p_CHARACTERSET
53 ,ARGUMENT14 => 'HOME_TAX_ID=' || p_HOME_TAX_ID
54 ,ARGUMENT15 => 'BG_ID=' || (l_bg_id)
55 ,ARGUMENT16 => 'REPORT_FOR=' || p_REPORT_FOR
56 ,ARGUMENT17 => 'ORG_STRUC_VERSION_ID=' || p_ORG_STRUC_VERSION_ID
57 );
58
59 if (l_req_id = 0) then
60 retcode := 2;
61 fnd_message.retrieve(errbuf);
62 else
63 commit;
64 end if;
65 end submit_efile;
66
67 procedure get_bg_id
68 ( p_business_place in varchar2,
69 l_bg_id out nocopy number)
70 is
71
72 cursor csr_a is
73 select hou.business_group_id
74 from hr_organization_units hou
75 where hou.organization_id = p_business_place;
76 begin
77 open csr_a;
78 fetch csr_a into l_bg_id;
79 close csr_a;
80 end get_bg_id;
81
82 function validate_det_medical_rec
83 ( p_assignment_id in number,
84 p_yea_date in date
85 )
86 return varchar2
87 is
88 l_inv_relationship number;
89 l_inv_aged_disabled number;
90 l_inv_med_ser_prov_name number;
91
92 cursor csr_inv_relationship is
93 select count(*) from
94 (
95 select res_reg_no, count(*)
96 from
97 (
98 select
99 aei_information8 res_reg_no
100 ,aei_information7 relationship
101 ,count(*) cnt
102 from
103 per_assignment_extra_info where assignment_id = p_assignment_id
104 and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
105 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
106 trunc(p_yea_date, 'yyyy')
107 group by
108 aei_information8
109 ,aei_information7
110 )
111 group by res_reg_no
112 having count(*) > 1
113 );
114
115 cursor csr_inv_aged_disabled is
116 select count(*) from
117 (
118 select res_reg_no, count(*)
119 from
120 (
121 select
122 aei_information8 res_reg_no
123 ,aei_information9 aged_disabled
124 ,count(*) cnt
125 from
126 per_assignment_extra_info where assignment_id = p_assignment_id
127 and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
128 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
129 trunc(p_yea_date, 'yyyy')
130 group by
131 aei_information8
132 ,aei_information9
133 )
134 group by res_reg_no
135 having count(*) > 1
136 );
137
138 cursor csr_inv_med_ser_prov_name is
139 select count(*) from
140 (
141 select med_ser_prov_reg_no, count(*)
142 from
143 (
144 select
145 aei_information5 med_ser_prov_reg_no
146 ,aei_information6 med_ser_prov_name
147 ,count(*) cnt
148 from
149 per_assignment_extra_info where assignment_id = p_assignment_id
150 and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
151 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
152 trunc(p_yea_date, 'yyyy')
153 group by
154 aei_information5
155 ,aei_information6
156 )
157 group by med_ser_prov_reg_no
158 having count(*) > 1
159 );
160 --
161 begin
162 --
163 open csr_inv_relationship;
164 fetch csr_inv_relationship into l_inv_relationship;
165 close csr_inv_relationship;
166
167 if l_inv_relationship > 0 then
168 --
169 return 'MULTI_RELATIONSHIP';
170 --
171 end if;
172
173 open csr_inv_aged_disabled;
174 fetch csr_inv_aged_disabled into l_inv_aged_disabled;
175 close csr_inv_aged_disabled;
176
177 if l_inv_aged_disabled > 0 then
178 --
179 return 'MULTI_DIS_AGED';
180 --
181 end if;
182
183 open csr_inv_med_ser_prov_name;
184 fetch csr_inv_med_ser_prov_name into l_inv_med_ser_prov_name;
185 close csr_inv_med_ser_prov_name;
186
187 if l_inv_med_ser_prov_name > 0 then
188 --
189 return 'MULTI_MED_SRV_PRVD_NAME';
190 --
191 end if;
192
193 return null;
194 --
195 end validate_det_medical_rec;
196
197 function get_medical_reg_no
198 ( p_assignment_id in number,
199 p_yea_date in date ,
200 p_medical_reg_no in varchar2
201 )
202 return varchar2
203 is
204 --
205 l_validation varchar2(50);
206 l_medical_reg_no varchar2(50);
207
208 cursor csr_inv_med_ser_prov_no is
209 select med_ser_prov_reg_no
210 from
211 (
212 select
213 aei_information5 med_ser_prov_reg_no
214 ,aei_information6 med_ser_prov_name
215 ,count(*) cnt
216 from
217 per_assignment_extra_info where assignment_id = p_assignment_id
218 and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
219 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
220 trunc(p_yea_date, 'yyyy')
221 group by
222 aei_information5
223 ,aei_information6
224 )
225 group by med_ser_prov_reg_no
226 having count(*) > 1;
227 --
228 Begin
229 --
230 l_validation := pay_magtape_generic.get_parameter_value('VALIDATION');
231 --
232 if l_validation = 'MULTI_MED_SRV_PRVD_NAME' then
233 --
234 open csr_inv_med_ser_prov_no;
235 fetch csr_inv_med_ser_prov_no into l_medical_reg_no;
236 close csr_inv_med_ser_prov_no;
237 --
238 else
239 --
240 l_medical_reg_no := p_medical_reg_no;
241 --
242 end if;
243 --
244 return l_medical_reg_no;
245 --
246 end get_medical_reg_no;
247 --
248 --
249
250 function get_resident_reg_no
251 ( p_assignment_id in number,
252 p_yea_date in date ,
253 p_resident_reg_no in varchar2
254 )
255 return varchar2
256 is
257 --
258 l_validation varchar2(50);
259 l_resident_reg_no varchar2(50);
260
261 cursor csr_inv_relationship is
262 select res_reg_no
263 from
264 (
265 select
266 aei_information8 res_reg_no
267 ,aei_information7 relationship
268 ,count(*) cnt
269 from
270 per_assignment_extra_info where assignment_id = p_assignment_id
271 and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
272 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
273 trunc(p_yea_date, 'yyyy')
274 group by
275 aei_information8
276 ,aei_information7
277 )
278 group by res_reg_no
279 having count(*) > 1;
280
281 cursor csr_inv_aged_disabled is
282 select res_reg_no
283 from
284 (
285 select
286 aei_information8 res_reg_no
287 ,aei_information9 aged_disabled
288 ,count(*) cnt
289 from
290 per_assignment_extra_info where assignment_id = p_assignment_id
291 and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
292 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
293 trunc(p_yea_date, 'yyyy')
294 group by
295 aei_information8
296 ,aei_information9
297 )
298 group by res_reg_no
299 having count(*) > 1;
300 --
301 Begin
302 --
303 l_validation := pay_magtape_generic.get_parameter_value('VALIDATION');
304 --
305 if l_validation = 'MULTI_RELATIONSHIP' then
306 --
307 open csr_inv_relationship;
308 fetch csr_inv_relationship into l_resident_reg_no;
309 close csr_inv_relationship;
310 --
311 elsif l_validation = 'MULTI_DIS_AGED' then
312 --
313 open csr_inv_aged_disabled;
314 fetch csr_inv_aged_disabled into l_resident_reg_no;
315 close csr_inv_aged_disabled;
316 --
317 else
318 --
319 l_resident_reg_no := p_resident_reg_no;
320 --
321 end if;
322 --
323 return l_resident_reg_no;
324 --
325 end get_resident_reg_no;
326 --
327
328 end pay_kr_yea_med_efile_conc_pkg;