1 package body pay_action_contexts_pkg as
2 /* $Header: pyactx.pkb 120.0 2005/05/29 01:51:32 appldev noship $ */
3 --
4 -- private global variables
5 --
6 type g_context_ids_type is record (
7 original_entry_id ff_contexts.context_id%type,
8 jurisdiction_code ff_contexts.context_id%type,
9 tax_group ff_contexts.context_id%type);
10
11 g_context_ids g_context_ids_type;
12 g_legislation_code pay_legislation_rules.legislation_code%type;
13
14
15
16 procedure archinit(p_pay_act_id in number)
17 is
18 begin
19 hr_utility.trace('> archinit()');
20
21 --
22 -- lookup up context id values for contexts to be populated
23 --
24 begin
25 SELECT c1.context_id original_entry_id,
26 c2.context_id jurisdiction_code,
27 c3.context_id tax_group
28 INTO g_context_ids
29 FROM FF_CONTEXTS c3,
30 FF_CONTEXTS c2,
31 FF_CONTEXTS c1
32 WHERE c1.context_name = 'ORIGINAL_ENTRY_ID'
33 and c2.context_name = 'JURISDICTION_CODE'
34 and c3.context_name = 'TAX_GROUP';
35 exception
36 when NO_DATA_FOUND then
37 ff_utils.assert(false, 'pay_action_contexts_pkg:archinit:10');
38 end;
39
40 --
41 -- lookup up legislation code for business group
42 --
43 begin
44 SELECT ou.org_information9
45 INTO g_legislation_code
46 FROM HR_ORGANIZATION_INFORMATION ou,
47 HR_ORGANIZATION_UNITS o,
48 PAY_PAYROLL_ACTIONS pa
49 WHERE pa.payroll_action_id = p_pay_act_id
50 and o.business_group_id = pa.business_group_id
51 and ou.organization_id = o.organization_id
52 and ou.org_information_context = 'Business Group Information';
53 exception
54 when NO_DATA_FOUND then
55 ff_utils.assert(false, 'pay_action_contexts_pkg:archinit:20');
56 end;
57
58 hr_utility.trace('< archinit()');
59 end archinit;
60
61
62
63 procedure range_cursor(p_pay_act_id in number
64 ,p_sqlstr out nocopy varchar2)
65 is
66 begin
67 hr_utility.trace('> range_cursor()');
68
69 p_sqlstr :=
70 'SELECT DISTINCT
71 asg.person_id
72 FROM PER_ASSIGNMENTS_F asg,
73 PAY_PAYROLL_ACTIONS pa
74 WHERE pa.payroll_action_id = :payroll_action_id
75 and asg.business_group_id = pa.business_group_id
76 ORDER BY
77 asg.person_id';
78
79 hr_utility.trace('< range_cursor()');
80 end range_cursor;
81
82
83
84 procedure action_creation(p_pay_act_id in number,
85 p_stperson in number,
86 p_endperson in number,
87 p_chunk in number)
88 is
89 cursor csr_asg_acts(b_pay_act_id number, b_stperson number,
90 b_endperson number) is
91 SELECT DISTINCT
92 asg.assignment_id
93 FROM PAY_PAYROLL_ACTIONS pa,
94 PER_ALL_ASSIGNMENTS_F asg,
95 PER_PERIODS_OF_SERVICE pos
96 WHERE pa.payroll_action_id = b_pay_act_id
97 and asg.business_group_id = pa.business_group_id
98 and pa.effective_date between
99 asg.effective_start_date and asg.effective_end_date
100 and pos.period_of_service_id = asg.period_of_service_id
101 and pos.person_id between b_stperson and b_endperson;
102
103 l_locking_asg_act_id number;
104 begin
105 hr_utility.trace('> action_creation()');
106
107 for rec_asg_act in csr_asg_acts(p_pay_act_id, p_stperson, p_endperson) loop
108 SELECT pay_assignment_actions_s.nextval
109 INTO l_locking_asg_act_id
110 FROM DUAL;
111
112 --
113 -- insert assignment action record
114 --
115 hr_nonrun_asact.insact(l_locking_asg_act_id, rec_asg_act.assignment_id,
116 p_pay_act_id, p_chunk, null);
117 end loop;
118
119 hr_utility.trace('< action_creation()');
120 end action_creation;
121
122
123
124 procedure ins_action_context(p_asg_act_id in number,
125 p_asg_id in number,
126 p_context_id in number,
127 p_context_value in varchar2)
128 is
132 b_context_value varchar2) is
129 cursor csr_chk_dup(b_asg_act_id number,
130 b_asg_id number,
131 b_context_id number,
133 SELECT 1
134 FROM PAY_ACTION_CONTEXTS ac
135 WHERE ac.assignment_action_id = b_asg_act_id
136 and ac.assignment_id = b_asg_id
137 and ac.context_id = b_context_id
138 and ac.context_value = b_context_value;
139
140 rec_dummy csr_chk_dup%rowtype;
141 begin
142 hr_utility.trace('> ins_action_context()');
143
144 --
145 -- before doing insert,
146 -- check if context value already exists,
147 -- if it does then don't bother inserting again,
148 -- nb. need to use this approach as csr_context_values may select out
149 -- duplicate values
150 --
151 open csr_chk_dup(p_asg_act_id, p_asg_id, p_context_id, p_context_value);
152 fetch csr_chk_dup into rec_dummy;
153
154 if csr_chk_dup%notfound then
155 hr_utility.trace('AA_ID>' || p_asg_act_id || '< ' ||
156 'ASG_ID>' || p_asg_id || '< ' ||
157 'CONTEXT_ID>' || p_context_id || '< ' ||
158 'CONTEXT_VALUE>' || p_context_value || '<');
159 INSERT INTO pay_action_contexts (
160 assignment_action_id,
161 assignment_id,
162 context_id,
163 context_value)
164 VALUES (
165 p_asg_act_id,
166 p_asg_id,
167 p_context_id,
168 p_context_value);
169 end if;
170
171 close csr_chk_dup;
172
173 hr_utility.trace('< ins_action_context()');
174 exception
175 when others then
176 close csr_chk_dup;
177 raise;
178 end ins_action_context;
179
180
181
182 procedure process_asg_act(p_asg_id in number,
183 p_asg_act_id in number,
184 p_tax_unit_id in number,
185 p_effective_date in date,
186 p_action_type in varchar2)
187 is
188 --
189 -- may select out duplicate context values
190 -- eg. sor_ jur_ sor_ entry_
191 -- id code type type
192 -- RR1 299035 null E E
193 -- RR2 299035 null I E
194 --
195 -- nb. selecting out distinct values is not possible because the
196 -- source type and entry type are required
197 --
198 cursor csr_context_values(b_asg_act_id number) is
199 SELECT distinct
200 et.element_type_id,
201 et.processing_type,
202 rr.source_id,
203 rr.jurisdiction_code
204 FROM PAY_RUN_RESULTS rr,
205 PAY_ELEMENT_TYPES_F et
206 WHERE rr.assignment_action_id = b_asg_act_id
207 AND et.element_type_id = rr.element_type_id
208 AND p_effective_date between
209 et.effective_start_date and et.effective_end_date;
210
211 --
212 -- Cursor to check if the specified source entry of an adjustment
213 -- run result is an original entry (a normal entry).
214 --
215 cursor csr_is_adj_orig_entry(p_entry_id in number)
216 is
217 select 1
218 from pay_element_entries_f pee
219 where pee.element_entry_id = p_entry_id
220 and pee.entry_type = 'E';
221 --
222 l_dummy number;
223
224 l_tax_group pay_action_contexts.context_value%type;
225 begin
226 hr_utility.trace('> process_asg_act()');
227
228 --
229 -- if in US legislation then also look up tax group
230 --
231 if g_legislation_code = 'US' then
232 begin
233 SELECT oi.org_information5
234 INTO l_tax_group
235 FROM HR_ORGANIZATION_INFORMATION oi
236 WHERE upper(oi.org_information_context) = 'FEDERAL TAX RULES'
237 and oi.organization_id = p_tax_unit_id;
238 exception
239 when NO_DATA_FOUND then
240 null;
241 end;
242
243 --
244 -- if tax group does not exist set or is null then
245 -- set context value to 'No Tax Group'
246 --
247 if l_tax_group is null then
248 l_tax_group := 'No Tax Group';
249 end if;
250
251 ins_action_context(p_asg_act_id, p_asg_id,
252 g_context_ids.tax_group, l_tax_group);
253 end if;
254
255 --
256 -- get distinct context values for current assignment action
257 --
258 for rec_context_value in csr_context_values(p_asg_act_id) loop
259
260 --
261 -- Check for ORIGINAL_ENTRY_ID context.
262 --
263 if p_action_type in ('B', 'I') then
264 --
265 -- For the adjustment actions, check to see if the originated
266 -- entry is a normal entry (entry_type='E').
267 --
268 open csr_is_adj_orig_entry(rec_context_value.source_id);
269 fetch csr_is_adj_orig_entry into l_dummy;
270 if csr_is_adj_orig_entry%found then
271
272 ins_action_context(p_asg_act_id, p_asg_id,
273 g_context_ids.original_entry_id,
274 to_char(rec_context_value.source_id));
275 end if;
276 close csr_is_adj_orig_entry;
277
278 else
279 if rec_context_value.processing_type = 'R' then
280
281 -- Insert an action context for all RECURRING entries
282 -- that have a formula associated with them that
283 -- uses ORIGINAL_ENTRY_ID.
284 -- This does not match exactly with the Payroll processes
285 -- but it's difficult to do that after the fact.
286 declare
287 is_oeid number;
288 begin
289 select 1
290 into is_oeid
291 from pay_status_processing_rules_f spr,
292 ff_fdi_usages_f fdi
293 where spr.element_type_id = rec_context_value.element_type_id
294 and p_effective_date between
295 spr.effective_start_date and spr.effective_end_date
296 and fdi.formula_id = spr.formula_id
297 and p_effective_date between
298 fdi.effective_start_date and fdi.effective_end_date
299 and fdi.item_name = 'ORIGINAL_ENTRY_ID'
300 and fdi.usage = 'U'
301 and rownum = 1;
302
303 ins_action_context(p_asg_act_id, p_asg_id,
304 g_context_ids.original_entry_id,
305 to_char(rec_context_value.source_id));
306
307 exception when no_data_found then
308 null;
309 end;
310
311 end if;
312 end if;
313
314 --
315 -- insert jurisdiction code into action contexts
316 --
317 if rec_context_value.jurisdiction_code is not null then
318 ins_action_context(p_asg_act_id, p_asg_id,
319 g_context_ids.jurisdiction_code,
320 rec_context_value.jurisdiction_code);
321 end if;
322 end loop;
323
324 hr_utility.trace('< process_asg_act()');
325 end process_asg_act;
326
327
328
329 procedure archive_data(p_asg_act_id in number, p_effective_date in date)
330 is
331 --
332 -- all assigment actions associated with marker assignment action,
333 -- only process assignment actions associated with runs of type:
334 -- - 'R' (payroll run)
335 -- - 'Q' (quick pay)
336 -- - 'B' (balance adjustment)
337 -- - 'V' (reversal)
338 -- - 'I' (balance initialisation)
339 --
340 -- also excludes assignment actions associated with a run of
341 -- type 'X' (archiver)
342 --
343 cursor csr_asg_acts(b_asg_act_id number) is
344 SELECT pa.effective_date,
345 pa.action_type,
346 aat.assignment_id,
347 aat.assignment_action_id,
348 aat.tax_unit_id
349 FROM PAY_PAYROLL_ACTIONS pa,
350 PAY_ASSIGNMENT_ACTIONS aat,
351 PAY_ASSIGNMENT_ACTIONS aam
352 WHERE aam.assignment_action_id = b_asg_act_id
353 and aat.assignment_id = aam.assignment_id
354 and pa.payroll_action_id = aat.payroll_action_id
355 and pa.action_type in ('R', 'Q', 'B', 'V', 'I')
356 and pa.effective_date <= p_effective_date
357 and NOT EXISTS
358 (SELECT 1
359 FROM PAY_ACTION_CONTEXTS ac
360 WHERE ac.assignment_action_id = aat.assignment_action_id)
361 ORDER BY
362 aat.assignment_action_id;
363 begin
364 hr_utility.trace('> ***** archive_data() *****');
365 hr_utility.trace(' p_asg_act_id>' || p_asg_act_id || '<');
366 hr_utility.trace(' p_effective_date>' || p_effective_date || '<');
367
368 --
369 -- loop through all assignment actions associated with an assignment,
370 -- do context processing for each assignment action individually
371 --
372 for rec_asg_act in csr_asg_acts(p_asg_act_id) loop
373 process_asg_act(rec_asg_act.assignment_id,
374 rec_asg_act.assignment_action_id,
375 rec_asg_act.tax_unit_id,
376 rec_asg_act.effective_date,
377 rec_asg_act.action_type);
378 end loop;
379
380 hr_utility.trace('< ***** archive_data() *****');
381 exception
382 when others then
383 hr_utility.trace('***** others');
384 hr_utility.trace('***** sqlcode>' || sqlcode || '<');
385 hr_utility.trace('***** sqlerrm>' || sqlerrm || '<');
386 raise;
387 end archive_data;
388
389
390
391 procedure deinitialise(p_pay_act_id in number)
392 is
393 begin
394 hr_utility.trace('> deinitialise()');
395 hr_utility.trace('< deinitialise()');
396 end deinitialise;
397
398 end pay_action_contexts_pkg;
[Home] [Help]