[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.12010000.2 2009/03/02 05:32:20 pnethaga ship $ */
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,
13 p_report_file_name in varchar2,
10 p_business_place in varchar2,
11 p_REPORT_FOR in varchar2, --5069923
12 p_magnetic_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 and ( aei_information13 <> '1' or (aei_information5 is not null and aei_information6 is not null)) -- Bug 8280944
154 group by
155 aei_information5
156 ,aei_information6
157 )
161 --
158 group by med_ser_prov_reg_no
159 having count(*) > 1
160 );
162 begin
163 --
164 open csr_inv_relationship;
165 fetch csr_inv_relationship into l_inv_relationship;
166 close csr_inv_relationship;
167
168 if l_inv_relationship > 0 then
169 --
170 return 'MULTI_RELATIONSHIP';
171 --
172 end if;
173
174 open csr_inv_aged_disabled;
175 fetch csr_inv_aged_disabled into l_inv_aged_disabled;
176 close csr_inv_aged_disabled;
177
178 if l_inv_aged_disabled > 0 then
179 --
180 return 'MULTI_DIS_AGED';
181 --
182 end if;
183
184 open csr_inv_med_ser_prov_name;
185 fetch csr_inv_med_ser_prov_name into l_inv_med_ser_prov_name;
186 close csr_inv_med_ser_prov_name;
187
188 if l_inv_med_ser_prov_name > 0 then
189 --
190 return 'MULTI_MED_SRV_PRVD_NAME';
191 --
192 end if;
193
194 return null;
195 --
196 end validate_det_medical_rec;
197
198 function get_medical_reg_no
199 ( p_assignment_id in number,
200 p_yea_date in date ,
201 p_medical_reg_no in varchar2
202 )
203 return varchar2
204 is
205 --
206 l_validation varchar2(50);
207 l_medical_reg_no varchar2(50);
208
209 cursor csr_inv_med_ser_prov_no is
210 select med_ser_prov_reg_no
211 from
212 (
213 select
214 aei_information5 med_ser_prov_reg_no
215 ,aei_information6 med_ser_prov_name
216 ,count(*) cnt
217 from
218 per_assignment_extra_info where assignment_id = p_assignment_id
219 and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
220 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
221 trunc(p_yea_date, 'yyyy')
222 and ( aei_information13 <> '1' or (aei_information5 is not null and aei_information6 is not null)) -- Bug 8280944
223 group by
224 aei_information5
225 ,aei_information6
226 )
227 group by med_ser_prov_reg_no
228 having count(*) > 1;
229 --
230 Begin
231 --
232 l_validation := pay_magtape_generic.get_parameter_value('VALIDATION');
233 --
234 if l_validation = 'MULTI_MED_SRV_PRVD_NAME' then
235 --
236 open csr_inv_med_ser_prov_no;
237 fetch csr_inv_med_ser_prov_no into l_medical_reg_no;
238 close csr_inv_med_ser_prov_no;
239 --
240 else
241 --
242 l_medical_reg_no := p_medical_reg_no;
243 --
244 end if;
245 --
246 return l_medical_reg_no;
247 --
248 end get_medical_reg_no;
249 --
250 --
251
252 function get_resident_reg_no
253 ( p_assignment_id in number,
254 p_yea_date in date ,
255 p_resident_reg_no in varchar2
256 )
257 return varchar2
258 is
259 --
260 l_validation varchar2(50);
261 l_resident_reg_no varchar2(50);
262
263 cursor csr_inv_relationship is
264 select res_reg_no
265 from
266 (
267 select
268 aei_information8 res_reg_no
269 ,aei_information7 relationship
270 ,count(*) cnt
271 from
272 per_assignment_extra_info where assignment_id = p_assignment_id
273 and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
274 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
275 trunc(p_yea_date, 'yyyy')
276 group by
277 aei_information8
278 ,aei_information7
279 )
280 group by res_reg_no
281 having count(*) > 1;
282
283 cursor csr_inv_aged_disabled is
284 select res_reg_no
285 from
286 (
287 select
288 aei_information8 res_reg_no
289 ,aei_information9 aged_disabled
290 ,count(*) cnt
291 from
292 per_assignment_extra_info where assignment_id = p_assignment_id
293 and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
294 and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') =
295 trunc(p_yea_date, 'yyyy')
296 group by
297 aei_information8
298 ,aei_information9
299 )
300 group by res_reg_no
301 having count(*) > 1;
302 --
303 Begin
304 --
305 l_validation := pay_magtape_generic.get_parameter_value('VALIDATION');
306 --
307 if l_validation = 'MULTI_RELATIONSHIP' then
308 --
309 open csr_inv_relationship;
310 fetch csr_inv_relationship into l_resident_reg_no;
311 close csr_inv_relationship;
312 --
313 elsif l_validation = 'MULTI_DIS_AGED' then
314 --
315 open csr_inv_aged_disabled;
316 fetch csr_inv_aged_disabled into l_resident_reg_no;
317 close csr_inv_aged_disabled;
318 --
319 else
320 --
321 l_resident_reg_no := p_resident_reg_no;
322 --
323 end if;
324 --
325 return l_resident_reg_no;
326 --
327 end get_resident_reg_no;
328 --
329
330 end pay_kr_yea_med_efile_conc_pkg;