[Home] [Help]
PACKAGE BODY: APPS.PAY_FR_DADS_PKG
Source
1 package body PAY_FR_DADS_PKG as
2 /* $Header: pyfrdads.pkb 120.1 2006/03/16 10:31 aparkes noship $ */
3 --
4 -- Globals
5 --
6 g_package CONSTANT VARCHAR2(20):= 'pay_fr_dads_pkg.';
7 --
8 g_cache_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
9 g_param_issuing_estab_id hr_organization_information.organization_id%TYPE;
10 g_param_company_id hr_organization_information.organization_id%TYPE;
11 g_param_estab_id hr_organization_information.organization_id%TYPE;
12 g_param_business_group_id per_business_Groups.business_group_id%TYPE;
13 g_param_reference varchar2(50);
14 g_param_start_date date;
15 g_param_effective_date date;
16 --
17 --
18 g_cre_info_issue pay_fr_dads_estab_comp.g_cre_info_issue%TYPE;
19 --
20 -------------------------------------------------------------------------------
21 -- ARCHIVE HOOK POINTS
22 --
23 -------------------------------------------------------------------------------
24 -- RANGE CURSOR
25 -- DESCRIPTION : Single threaded.
26 -- Returns the Range Cursor String
27 -------------------------------------------------------------------------------
28 procedure range_cursor (
29 pactid in number
30 ,sqlstr out nocopy varchar) is
31 BAD EXCEPTION;
32 l_text fnd_lookup_values.meaning%TYPE;
33 l_proc VARCHAR2(40) := g_package||' range_cursor ';
34 BEGIN
35 --
36 hr_utility.set_location('Entering ' || l_proc,10);
37 --
38 -- Get the legislative parameters used in the call to prove the seed data
39 -- retrict the list of addresses
40 --
41 hr_utility.set_location('Step ' || l_proc,20);
42 --
43 -- Return the select string
44 --
45 sqlstr := 'SELECT DISTINCT person_id
46 FROM per_people_f ppf
47 ,pay_payroll_actions ppa
48 WHERE ppa.payroll_action_id = :payroll_action_id
49 AND ppa.business_group_id = ppf.business_group_id
50 ORDER BY ppf.person_id';
51 --
52 hr_utility.set_location('Leaving: '||l_proc,50);
53 EXCEPTION
54 WHEN OTHERS THEN
55 hr_utility.set_location(' Leaving with EXCEPTION: '||l_proc,50);
56 -- Return cursor that selects no rows
57 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
58 END range_cursor;
59 -------------------------------------------------------------------------------
60 -- ACTION CREATION --
61 -- DESCRIPTION : Creates new assignment actions under the (archive)
62 -- payroll action
63 -------------------------------------------------------------------------------
64 PROCEDURE action_creation (pactid IN NUMBER,
65 stperson IN NUMBER,
66 endperson IN NUMBER,
67 chunk IN NUMBER) IS
68 --
69 l_proc VARCHAR2(60):= g_package||' action_creation ';
70 --
71 cursor csr_qualifying_assignments is
72 select /*+leading(asg_run) index(asg_run PER_ASSIGNMENTS_F_N12)
73 index(org_est HR_ORGANIZATION_INFORMATIO_FK2)
74 index(org_coy HR_ORGANIZATION_INFORMATIO_FK2) */
75 ppa_run.payroll_id, asg_run.assignment_id,
76 max(nvl(paa_run.source_action_id
77 ,paa_run.assignment_action_id)) mst_action_id
78 from pay_assignment_Actions paa_run
79 ,pay_payroll_actions ppa_run
80 ,per_all_assignments_f asg_run
81 ,hr_organization_information org_est
82 ,hr_organization_information org_coy
83 where ppa_run.business_group_id = asg_run.business_group_id
84 and asg_run.business_group_id = g_param_business_group_id
85 and ppa_run.payroll_action_id = paa_run.payroll_Action_id
86 and ppa_run.action_type in ('R','Q')
87 and ppa_run.action_status = 'C'
88 and paa_run.action_status = 'C'
89 and ppa_run.effective_Date between g_param_start_date
90 and g_param_effective_date
91 and paa_run.assignment_id = asg_run.assignment_id
92 and ppa_run.effective_date BETWEEN asg_run.effective_start_date
93 AND asg_run.effective_end_date
94 and asg_run.person_id BETWEEN stperson
95 AND endperson
96 and (paa_run.source_action_id is null or paa_run.end_date is not null)
97 /* the estab parameter condition is met */
98 and paa_run.tax_unit_id = nvl(g_param_estab_id, paa_run.tax_unit_id)
99 /* the company parameter condition is met */
100 and org_est.organization_id = paa_run.tax_unit_id
101 and org_est.org_information_context = 'FR_ESTAB_INFO'
102 and org_coy.organization_id+0 = nvl(g_param_company_id /*comp param*/
103 ,org_coy.organization_id)
104 /* the estab's coy has the parameterized issueing estab */
105 and org_est.org_information1 = org_coy.organization_id
106 and org_coy.org_information_context = 'FR_COMP_INFO'
107 and org_coy.org_information4 = g_param_issuing_estab_id
108 group by ppa_run.payroll_id, asg_run.assignment_id;
109
110 --
111 l_qualify_asg_rec csr_qualifying_assignments%ROWTYPE;
112 --
113 l_actid pay_assignment_actions.assignment_action_id%TYPE;
114 --
115 BEGIN
116 hr_utility.set_location('Entering ' || l_proc,20);
117 --
118 if g_cache_payroll_action_id is null
119 or g_cache_payroll_action_id <> pactid then
120 pay_fr_dads_pkg.get_all_parameters (
121 p_payroll_action_id => pactid
122 ,p_issuing_estab_id => g_param_issuing_estab_id
123 ,p_company_id => g_param_company_id
124 ,p_estab_id => g_param_estab_id
125 ,p_business_Group_id => g_param_business_group_id
126 ,p_reference => g_param_reference
127 ,p_start_date => g_param_start_date
128 ,p_effective_date => g_param_effective_date);
129 g_cache_payroll_action_id := pactid;
130 end if;
131 --
132 --
133 -- get any qualifying assignments
134 --
135 open csr_qualifying_assignments;
136 LOOP
137 fetch csr_qualifying_assignments into l_qualify_asg_rec;
138 EXIT WHEN csr_qualifying_assignments%NOTFOUND;
139 --
140 -- get the latest master action in the period and lock it
141 --
142 -- create a new action and lock the fetched one
143 --
144 SELECT pay_assignment_actions_s.NEXTVAL
145 INTO l_actid
146 FROM dual;
147 --
148 hr_nonrun_asact.insact(l_actid
149 ,l_qualify_asg_rec.assignment_id
150 ,pactid
151 ,chunk);
152 --
153 hr_nonrun_asact.insint(
154 lockingactid => l_actid
155 ,lockedactid => l_qualify_asg_rec.mst_action_id);
156 --
157 END LOOP;
158
159 --
160 hr_utility.set_location('Leaving ' || l_proc, 100);
161 END action_creation;
162 -------------------------------------------------------------------------------
163 -- ARCHIVE_INIT --
164 -- DESCRIPTION : Caches the SRS Parameters
165 -------------------------------------------------------------------------------
166 procedure archive_init(
167 p_payroll_action_id in number) is
168 --
169 l_proc VARCHAR2(40):= g_package||' archinit ';
170 BEGIN
171 --
172 hr_utility.set_location('Entering ' || l_proc, 10);
173 if g_cache_payroll_action_id is null
174 or g_cache_payroll_action_id <> p_payroll_action_id then
175 pay_fr_dads_pkg.get_all_parameters (
176 p_payroll_action_id => p_payroll_action_id
177 ,p_issuing_estab_id => g_param_issuing_estab_id
178 ,p_company_id => g_param_company_id
179 ,p_estab_id => g_param_estab_id
180 ,p_business_Group_id => g_param_business_group_id
181 ,p_reference => g_param_reference
182 ,p_start_date => g_param_start_date
183 ,p_effective_date => g_param_effective_date);
184 g_cache_payroll_action_id := p_payroll_action_id;
185 end if;
186 hr_utility.set_location('Leaving ' || l_proc, 100);
187 --
188 END;
189 -------------------------------------------------------------------------------
190 -- ARCHIVE CODE
191 -- DESCRIPTION : Main routine for storing data against the assignment actions.
192 -------------------------------------------------------------------------------
193 procedure archive_code(
194 p_assactid in number
195 ,p_effective_date in date) is
196 --
197 l_proc VARCHAR2(40):= g_package||' Archive code ';
198 --
199 -- Cursor for getting companies
200 -- for the selected issuing establishment
201 cursor get_compid_cur is
202 select distinct hoi.organization_id organization_id
203 from hr_organization_information hoi,
204 hr_organization_information hoi_issue
205 where hoi.org_information_context = 'CLASS'
206 and hoi.org_information1 = 'FR_SOCIETE'
207 and hoi_issue.organization_id = hoi.organization_id
208 and hoi_issue.org_information_context = 'FR_COMP_INFO'
209 and hoi_issue.org_information4 = g_param_issuing_estab_id;
210 BEGIN
211 hr_utility.set_location('Entering ' || l_proc,10);
212
213 IF g_param_company_id IS NULL THEN
214 for get_compid_rec in get_compid_cur loop
215 -- Call procedure for retrieving S30 data
216 hr_utility.set_location('Calling S30 p_assact_id:'||p_assactid,101);
217 PAY_FR_DADS_EMP_PKG.execS30_G01_00(
218 p_assact_id =>p_assactid
219 ,p_issuing_estab_id => g_param_issuing_estab_id
220 ,p_org_id =>get_compid_rec.organization_id
221 ,p_estab_id =>g_param_estab_id
222 ,p_business_Group_id => g_param_business_group_id
223 ,p_reference => g_param_reference
224 ,p_start_date => g_param_start_date
225 ,p_effective_date => g_param_effective_date);
226 end loop;
227 ELSE
228 hr_utility.set_location('Calling S30-COmp p_assact_id:'||p_assactid,101);
229 PAY_FR_DADS_EMP_PKG.execS30_G01_00(
230 p_assact_id =>p_assactid
231 ,p_issuing_estab_id => g_param_issuing_estab_id
232 ,p_org_id =>g_param_company_id
233 ,p_estab_id =>g_param_estab_id
234 ,p_business_Group_id => g_param_business_group_id
235 ,p_reference => g_param_reference
236 ,p_start_date => g_param_start_date
237 ,p_effective_date => g_param_effective_date);
238
239 END IF;
240
241 hr_utility.set_location('Leaving ' || l_proc, 100);
242 --
243 END Archive_Code;
244
245 -------------------------------------------------------------------------------
246 -- GET_PARAMETER used in sql to decode legislative parameters
247 -- copied from uk code.
248 -------------------------------------------------------------------------------
249 function get_parameter(
250 p_parameter_string in varchar2
251 ,p_token in varchar2
252 ,p_segment_number in number default null ) RETURN varchar2
253 IS
254 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
255 l_start_pos NUMBER;
256 l_delimiter varchar2(1):=' ';
257 l_proc VARCHAR2(40):= g_package||' get parameter ';
258 BEGIN
259 hr_utility.set_location('Entering ' || l_proc, 20);
260 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
261 IF l_start_pos = 0 THEN
262 l_delimiter := '|';
263 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
264 end if;
265 IF l_start_pos <> 0 THEN
266 l_start_pos := l_start_pos + length(p_token||'=');
267 l_parameter := substr(p_parameter_string,
268 l_start_pos,
269 instr(p_parameter_string||' ',
270 l_delimiter,l_start_pos)
271 - l_start_pos);
272 IF p_segment_number IS NOT NULL THEN
273 l_parameter := ':'||l_parameter||':';
274 l_parameter := substr(l_parameter,
275 instr(l_parameter,':',1,p_segment_number)+1,
276 instr(l_parameter,':',1,p_segment_number+1) -1
277 - instr(l_parameter,':',1,p_segment_number));
278 END IF;
279 END IF;
280 hr_utility.set_location('Leaving ' || l_proc, 100);
281 RETURN l_parameter;
282 END get_parameter;
283 -------------------------------------------------------------------------------
284 -- GET_ALL_PARAMETERS gets all parameters for the payroll action
285 -------------------------------------------------------------------------------
286 procedure get_all_parameters (
287 p_payroll_action_id in number
288 ,p_issuing_estab_id out nocopy number
289 ,p_company_id out nocopy number
290 ,p_estab_id out nocopy number
291 ,p_business_group_id out nocopy number
292 ,p_reference out nocopy varchar2
293 ,p_start_date out nocopy date
294 ,p_effective_date out nocopy date) is
295 --
296 cursor csr_parameter_info(p_payroll_action_id NUMBER) IS
297 SELECT pay_fr_dads_pkg.get_parameter(legislative_parameters, 'ISSUING_ESTAB_ID')
298 ,pay_fr_dads_pkg.get_parameter(legislative_parameters, 'COMPANY_ID')
299 ,pay_fr_dads_pkg.get_parameter(legislative_parameters, 'ESTAB_ID')
300 ,business_group_id
301 ,pay_fr_dads_pkg.get_parameter(legislative_parameters, 'REFERENCE')
302 ,start_date
303 ,effective_date
304 FROM pay_payroll_actions
305 WHERE payroll_action_id = p_payroll_action_id;
306 --
307 l_proc VARCHAR2(40):= g_package||' get_all_parameters ';
308
309 BEGIN
310 hr_utility.set_location('Entering ' || l_proc, 20);
311 open csr_parameter_info (p_payroll_action_id);
312 fetch csr_parameter_info into p_issuing_estab_id, p_company_id, p_estab_id
313 ,p_business_group_id, p_reference, p_start_date
314 ,p_effective_date;
315 close csr_parameter_info;
316
317 hr_utility.set_location('Leaving ' || l_proc, 100);
318 END;
319 -------------------------------------------------------------------------------
320 -- DEINITIALIZE
321 -- DESCRIPTION : Called once per payroll action;
322 -------------------------------------------------------------------------------
323 PROCEDURE deinitialize_code(p_payroll_action_id in number) is
324 --
325 l_proc VARCHAR2(40) := g_package||' deinitialize ';
326 --
327 -- Cursor for getting companies
328 -- for the selected issuing establishment
329 cursor S20 is
330 select distinct hoi.organization_id
331 from hr_organization_information hoi,
332 hr_organization_information hoi_issue
333 where hoi.org_information_context ||'' = 'CLASS'
334 and hoi.org_information1 = 'FR_SOCIETE'
335 and hoi_issue.organization_id =hoi.organization_id
336 and hoi_issue.org_information_context = 'FR_COMP_INFO'
337 and hoi_issue.org_information4 = g_param_issuing_estab_id;
338 --
339 -- Cursor for getting establishment ids
340 -- from archived assignments
341 cursor S80 is
342 select distinct pacinfo.action_information7 estab_id
343 from pay_action_information pacinfo,
344 pay_assignment_actions pasac
345 where pacinfo.action_context_id = pasac.assignment_action_id
346 and pasac.payroll_action_id = p_payroll_action_id
347 and pacinfo.action_information_category = 'FR_DADS_FILE_DATA'
348 and pacinfo.action_information1 = 'S41.G01.00.005';
349 --
350 --
351 BEGIN
352 if g_cache_payroll_action_id is null
353 or g_cache_payroll_action_id <> p_payroll_action_id then
354 pay_fr_dads_pkg.get_all_parameters (
355 p_payroll_action_id => p_payroll_action_id
356 ,p_issuing_estab_id => g_param_issuing_estab_id
357 ,p_company_id => g_param_company_id
358 ,p_estab_id => g_param_estab_id
359 ,p_business_Group_id => g_param_business_group_id
360 ,p_reference => g_param_reference
361 ,p_start_date => g_param_start_date
362 ,p_effective_date => g_param_effective_date);
363 g_cache_payroll_action_id := p_payroll_action_id;
364 end if;
365 --
366 -- Retrieve S10 (issuing establishment) data
367 pay_fr_dads_estab_comp.S10_00_issue_estab(
368 P_PARAM_REFERENCE => g_param_reference,
369 P_PARAM_ISSUING_ESTAB_ID => g_param_issuing_estab_id,
370 P_PARAM_BUSINESS_GROUP_ID => g_param_business_group_id,
371 P_PAYROLL_ACTION_ID => p_payroll_action_id,
372 P_CRE_INFO_ISSUE => g_cre_info_issue);
373 --
374 pay_fr_dads_estab_comp.S10_01_issue_person(
375 P_ISSUING_ESTAB_ID => g_param_issuing_estab_id,
376 P_PAYROLL_ACTION_ID => p_payroll_action_id);
377 --
378 IF g_param_company_id IS NULL THEN
379 for s20_comp_rec in S20 loop
380 -- Call procedure for retrieving S20 data
381 pay_fr_dads_estab_comp.S20_comp_info(
382 P_COMPANY_ID => s20_comp_rec.organization_id,
383 P_CRE_INFO_ISSUE => g_cre_info_issue,
384 P_DADS_START_DATE => g_param_start_date,
385 P_DADS_END_DATE => g_param_effective_date,
386 P_PAYROLL_ACTION_ID => p_payroll_action_id);
387 end loop;
388 ELSE
389 pay_fr_dads_estab_comp.S20_comp_info(
390 P_COMPANY_ID => g_param_company_id,
391 P_CRE_INFO_ISSUE => g_cre_info_issue,
392 P_DADS_START_DATE => g_param_start_date,
393 P_DADS_END_DATE => g_param_effective_date,
394 P_PAYROLL_ACTION_ID => p_payroll_action_id);
395 END IF;
396 --
397 for estab_rec in S80 loop
398 pay_fr_dads_estab_comp.S80_insee_estab(
399 P_ESTAB_ID => estab_rec.estab_id,
400 P_PAYROLL_ACTION_ID => p_payroll_action_id,
401 P_DADS_END_DATE => g_param_effective_date);
402 end loop;
403 --
404 --
405 end deinitialize_code;
406 --
407 END pay_fr_dads_pkg;