1 PACKAGE BODY pay_group_event_pkg
2 /* $Header: pygrpevn.pkb 120.0.12000000.1 2007/04/10 09:57:07 ckesanap noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_group_event_pkg
21
22 Description : Delivery of event qulaifier for group level events
23 criteria , for retro notification.
24 This package must be customized by the customer to
25 enable the event qualifier for FF_GLOBALS_F and
26 PAY_USER_COLUMN_INSTANCES_F.
27 There are few examples given for the customer to
28 base their code on.
29 Uses :
30
31 Change List
32 -----------
33 Date Name Vers Bug No Description
34 ---- ---- ---- ------ -----------
35 09-APR-2007 SuSivasu 120.0 5562866 Created.
36
37 *******************************************************************/
38 AS
39 --
40 -- *******************************************************************
41 -- Function to check the global is attached ot the given assignment.
42 --
43 -- Parameters:
44 -- p_assignment_id : Assignment Id in question.
45 -- p_surrogate_key : Global_id in question.
46 -- *******************************************************************
47 function ff_global_check(p_assignment_id in number,
48 p_surrogate_key in number) return varchar2
49 is
50 --
51 --
52 begin
53 --
54 --
55 -- Place your code here to check whether the given global impacts
56 -- the given assignment. If so then return 'Y'.
57 --
58 return 'N';
59 --
60 end;
61 --
62 -- *******************************************************************
63 -- Function to check whether the event is attached to the given entry.
64 -- *******************************************************************
65 function ff_global_qualifier return varchar2
66 is
67 --
68 begin
69 --
70 --
71 -- Place your code here to check whether the given global (i.e.
72 -- pay_interpreter_pkg.g_object_key) impacts the given element entry
73 -- (i.e. pay_interpreter_pkg.g_ee_id). If so then return 'Y'.
74 --
75 return 'N';
76 --
77 end;
78 --
79 -- *******************************************************************
80 -- Function to check the global is attached ot the given assignment.
81 --
82 -- Parameters:
83 -- p_assignment_id : Assignment Id in question.
84 -- p_surrogate_key : Global_id in question.
85 -- *******************************************************************
86 function pay_user_table_check(p_assignment_id in number,
87 p_surrogate_key in number) return varchar2
88 is
89 --
90 begin
91 --
92 --
93 -- Place your code here to check whether the given user column
94 -- instance impacts the given assignment. If so then return 'Y'.
95 --
96 return 'N';
97 --
98 end;
99 --
100 -- *******************************************************************
101 -- Function to check whether the event is attached to the given entry.
102 -- *******************************************************************
103 function pay_user_table_qualifier return varchar2
104 is
105 --
106 begin
107 --
108 --
109 -- Place your code here to check whether the given user column instance (i.e.
110 -- pay_interpreter_pkg.g_object_key) impacts the given element entry
111 -- (i.e. pay_interpreter_pkg.g_ee_id). If so then return 'Y'.
112 --
113 return 'N';
114 --
115 end;
116 --
117 /*
118 --
119 --
120 -- *******************************************************************
121 -- EXAMPLE 1: FF_GLOBALS_F
122 -- This takes the following path, which can casue performance issue:
123 --
124 -- Global > Formula > Formula Processing Rule > Formula Result Rule
125 -- > Element > Entry > Assignment
126 --
127 -- *******************************************************************
128 --
129 -- Function to check whether the event is in fact tied to the
130 -- assignment in question.
131 --
132 function ff_global_check(p_assignment_id in number,
133 p_surrogate_key in number) return varchar2
134 is
135 --
136 cursor validate_event(p_assignment_id in number,
137 p_surrogate_key in number)
138 is
139 select '' chk
140 from dual
141 where exists (
142 select pee.assignment_id
143 from ff_fdi_usages_f fdi
144 , ff_globals_f glb
145 , ff_formulas_f ff
146 , PAY_STATUS_PROCESSING_RULES_F psp
147 , PAY_FORMULA_RESULT_RULES_F pfr
148 , pay_element_entries_f pee
149 where glb.global_id = p_surrogate_key
150 and pee.assignment_id = p_assignment_id
151 and fdi.item_name = glb.global_name
152 and ff.formula_id = fdi.formula_id
153 and ((glb.legislation_code is null and glb.business_group_id is null) or
154 (glb.legislation_code = ff.legislation_code) or
155 (glb.business_group_id = ff.business_group_id) or
156 (glb.legislation_code =
157 (select bg.legislation_code
158 from per_business_groups_perf bg
159 where bg.business_group_id = ff.business_group_id))
160 )
161 and psp.formula_id = ff.formula_id
162 and pfr.STATUS_PROCESSING_RULE_ID = psp.STATUS_PROCESSING_RULE_ID
163 );
164 --
165 l_valid_event VARCHAR2(1);
166 --
167 begin
168 l_valid_event := 'N';
169 --
170 for grrec in validate_event(p_assignment_id,p_surrogate_key) loop
171 l_valid_event := 'Y';
172 end loop;
173 --
174 return l_valid_event;
175 --
176 end;
177 --
178 --
179 -- Function used by the qualifier.
180 --
181 function ff_global_qualifier return varchar2
182 is
183 --
184 cursor global_affected is
185 select '' chk
186 from dual
187 where exists (
188 select pee.assignment_id
189 from ff_fdi_usages_f fdi
190 , ff_globals_f glb
191 , ff_formulas_f ff
192 , PAY_STATUS_PROCESSING_RULES_F psp
193 , PAY_FORMULA_RESULT_RULES_F pfr
194 , pay_element_entries_f pee
195 where glb.global_id = pay_interpreter_pkg.g_object_key
196 and pee.element_entry_id = pay_interpreter_pkg.g_ee_id
197 and pee.effective_end_date >= fdi.effective_start_date
198 and pee.effective_start_date <= fdi.effective_end_date
199 and fdi.item_name = glb.global_name
200 and ff.formula_id = fdi.formula_id
201 and ff.effective_start_date = fdi.effective_start_date
202 and ff.effective_end_date = fdi.effective_end_date
203 and ((glb.legislation_code is null and glb.business_group_id is null) or
204 (glb.legislation_code = ff.legislation_code) or
205 (glb.business_group_id = ff.business_group_id) or
206 (glb.legislation_code =
207 (select bg.legislation_code
208 from per_business_groups_perf bg
209 where bg.business_group_id = ff.business_group_id))
210 )
211 and psp.formula_id = ff.formula_id
212 and pee.effective_end_date >= psp.effective_start_date
213 and pee.effective_start_date <= psp.effective_end_date
214 and pfr.STATUS_PROCESSING_RULE_ID = psp.STATUS_PROCESSING_RULE_ID
215 and pee.effective_end_date >= pfr.effective_start_date
216 and pee.effective_start_date <= pfr.effective_end_date
217 );
218 --
219 l_exists varchar2(1);
220 --
221 begin
222 --
223 open global_effected;
224 fetch global_effected into l_exists;
225 --
226 if global_effected%notfound then
227 close global_effected;
228 return 'N';
229 else
230 close global_effected;
231 return 'Y';
232 end if;
233 --
234 end;
235 --
236 --
237 -- *******************************************************************
238 -- END OF EXAMPLE 1
239 -- *******************************************************************
240 --
241 -- *******************************************************************
242 -- EXAMPLE 2: FF_GLOBALS_F
243 --
244 -- This assumes the global which the customer wants to track are
245 -- "STANDARD_RATE" and "OVERTIME_RATE", where by these will be used by
246 -- elements "Time Card" and "Overtime".
247 --
248 -- *******************************************************************
249 --
250 -- Function to check whether the event is in fact tied to the
251 -- assignment in question.
252 --
253 function ff_global_check(p_assignment_id in number,
254 p_surrogate_key in number) return varchar2
255 is
256 --
257 cursor validate_event(p_assignment_id in number,
258 p_surrogate_key in number)
259 is
260 select '' chk
261 from dual
262 where exists (
263 select pee.assignment_id
264 from ff_globals_f glb
265 , pay_element_entries_f pee
266 , pay_element_types_f pet
267 where glb.global_id = p_surrogate_key
268 and pee.assignment_id = p_assignment_id
269 and glb.global_name in ('STANDARD_RATE','OVERTIME_RATE')
270 and pee.element_type_id = pet.element_type_id
271 and pet.element_name in ('Time Card','Overtime')
272 );
273 --
274 l_valid_event VARCHAR2(1);
275 --
276 begin
277 l_valid_event := 'N';
278 --
279 for grrec in validate_event(p_assignment_id,p_surrogate_key) loop
280 l_valid_event := 'Y';
281 end loop;
282 --
283 return l_valid_event;
284 --
285 end;
286 --
287 --
288 -- Function used by the qualifier.
289 --
290 function ff_global_qualifier return varchar2
291 is
292 --
293 cursor global_affected is
294 select '' chk
295 from dual
296 where exists (
297 select pee.assignment_id
298 from ff_globals_f glb
299 , pay_element_entries_f pee
300 , pay_element_types_f pet
301 where glb.global_id = p_surrogate_key
302 and pee.assignment_id = p_assignment_id
303 and glb.global_name in ('STANDARD_RATE','OVERTIME_RATE')
304 and pee.element_type_id = pet.element_type_id
305 and pet.element_name in ('Time Card','Overtime')
306 and pee.effective_end_date >= pet.effective_start_date
307 and pee.effective_start_date <= pet.effective_end_date
308 );
309 --
310 l_exists varchar2(1);
311 --
312 begin
313 --
314 open global_effected;
315 fetch global_effected into l_exists;
316 --
317 if global_effected%notfound then
318 close global_effected;
319 return 'N';
320 else
321 close global_effected;
322 return 'Y';
323 end if;
324 --
325 end;
326 --
327 -- *******************************************************************
328 -- END OF EXAMPLE 2
329 -- *******************************************************************
330 */
331
332 end pay_group_event_pkg;
[Home] [Help]