1 package body pay_bf_expc as
2 /* $Header: paybfexc.pkb 115.0 1999/11/24 02:18:02 pkm ship $ */
3 /* Copyright (c) Oracle Corporation 1994. All rights reserved. */
4
5 /*
6 PRODUCT
7 Oracle*Payroll
8 NAME
9 pytbfexc.pkb - PaYroll Test BF legislation EXpiry Checking code.
10 DESCRIPTION
11 Contains the expiry checking code that was contained as part
12 of the dimensions created by pyautogn. Following the change
13 to latest balance functionality, these need to be contained
14 as packaged procedures.
15 PUBLIC FUNCTIONS
16 <none>
17 PRIVATE FUNCTIONS
18 <none>
19 NOTES
20 <none>
21 MODIFIED (DD/MM/YY)
22 dsaxby 13/11/96 - Added always_expires procedure.
23 nbristow 25/01/96 - Year to Date expiry checking was incorrect.
24 dsaxby 18/01/95 - Added arcs revision header.
25 dsaxby 31/08/94 - first created.
26 */
27
28 /*------------------------------ pytd_ec ------------------------------------*/
29 /*
30 NAME
31 pytd_ec - Person Tax Year To Date expiry check.
32 DESCRIPTION
33 Expiry checking code for the following:
34 BF Person-level Tax Year to Date Balance Dimension
35 NOTES
36 <none>
37 */
38 procedure pytd_ec
39 (
40 p_owner_payroll_action_id in number, -- run created balance.
41 p_user_payroll_action_id in number, -- current run.
42 p_owner_assignment_action_id in number, -- assact created balance.
43 p_user_assignment_action_id in number, -- current assact..
44 p_owner_effective_date in date, -- eff date of balance.
45 p_user_effective_date in date, -- eff date of current run.
46 p_dimension_name in varchar2, -- balance dimension name.
47 p_expiry_information out number -- dimension expired flag.
48 ) is
49 l_tax_year_start date;
50 begin
51 /*
52 * Select the start of the current tax year.
53 * Follow this by comparing the tax year
54 * start with effective date of the run
55 * that created the balance.
56 * The dimension has expired if the owner
57 * date is less than the tax year start date,
58 * (i.e. we have passed a tax year boundary).
59 */
60 /*
64 || to_char(p_user_effective_date,'YYYY'),'DD-MM-YYYY')),
61 select to_date('06-04-' || to_char( to_number(
62 to_char( p_owner_effective_date,'YYYY'))
63 + decode(sign( p_user_effective_date - to_date('06-04-'
65 -1,-1,0)),'DD-MM-YYYY')
66 into l_tax_year_start
67 from dual;
68 */
69 Select to_date('06-04-' || to_char( to_number(
70 to_char( PTP.regular_payment_date,'YYYY'))
71 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
72 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
73 -1,-1,0)),'DD-MM-YYYY') finyear
74 into l_tax_year_start
75 from per_time_periods PTP,
76 pay_payroll_actions BACT
77 where BACT.payroll_action_id = p_user_payroll_action_id
78 and PTP.time_period_id = BACT.time_period_id;
79
80 if p_owner_effective_date >= l_tax_year_start then
81 p_expiry_information := 0;
82 else
83 p_expiry_information := 1;
84 end if;
85 end pytd_ec;
86
87 /*------------------------------ aytd_ec ------------------------------------*/
88 /*
89 NAME
90 aytd_ec - Assignment Tax Year To Date expiry check.
91 DESCRIPTION
92 Expiry checking code for the following:
93 BF Assignment-level Tax Year to Date Balance Dimension
94 NOTES
95 The associtated dimension is expiry checked at
96 Payroll Action level.
97 */
98 procedure aytd_ec
99 (
100 p_owner_payroll_action_id in number, -- run created balance.
101 p_user_payroll_action_id in number, -- current run.
102 p_owner_assignment_action_id in number, -- assact created balance.
103 p_user_assignment_action_id in number, -- current assact..
104 p_owner_effective_date in date, -- eff date of balance.
105 p_user_effective_date in date, -- eff date of current run.
106 p_dimension_name in varchar2, -- balance dimension name.
107 p_expiry_information out number -- dimension expired flag.
108 ) is
109 l_tax_year_start date;
110 begin
111 /*
112 * Select the start of the current tax year.
113 * Follow this by comparing the tax year
114 * start with effective date of the run
115 * that created the balance.
116 * The dimension has expired if the owner
117 * date is less than the tax year start date,
118 * (i.e. we have passed a tax year boundary).
119 */
120 /*
121 select to_date('06-04-' || to_char( to_number(
122 to_char( p_user_effective_date,'YYYY'))
123 + decode(sign( p_user_effective_date - to_date('06-04-'
124 || to_char(p_user_effective_date,'YYYY'),'DD-MM-YYYY')),
125 -1,-1,0)),'DD-MM-YYYY')
126 into l_tax_year_start
127 from dual;
128 */
129
130 Select to_date('06-04-' || to_char( to_number(
131 to_char( PTP.regular_payment_date,'YYYY'))
132 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
133 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
134 -1,-1,0)),'DD-MM-YYYY') finyear
135 into l_tax_year_start
136 from per_time_periods PTP,
137 pay_payroll_actions BACT
138 where BACT.payroll_action_id = p_user_payroll_action_id
139 and PTP.time_period_id = BACT.time_period_id;
140
141 if p_owner_effective_date >= l_tax_year_start then
142 p_expiry_information := 0;
143 else
144 p_expiry_information := 1;
145 end if;
146 end aytd_ec;
147
148 /*------------------------------ pptd_ec ------------------------------------*/
149 /*
150 NAME
151 pptd_ec - Person Period To Date Expiry Check.
152 DESCRIPTION
153 Expiry checking code for the following:
154 BF Person-level Period to Date Balance Dimension
155 NOTES
156 Associated dimension is expiry checked at
157 Payroll Action level.
158 */
159 procedure pptd_ec
160 (
161 p_owner_payroll_action_id in number, -- run created balance.
162 p_user_payroll_action_id in number, -- current run.
163 p_owner_assignment_action_id in number, -- assact created balance.
164 p_user_assignment_action_id in number, -- current assact..
165 p_owner_effective_date in date, -- eff date of balance.
166 p_user_effective_date in date, -- eff date of current run.
167 p_dimension_name in varchar2, -- balance dimension name.
168 p_expiry_information out number -- dimension expired flag.
169 ) is
170 l_period_start_date date;
171 begin
172 select TP.start_date
173 into l_period_start_date
174 from per_time_periods TP,
175 pay_payroll_actions PACT
176 where PACT.payroll_action_id = p_user_payroll_action_id
177 and PACT.payroll_id = TP.payroll_id
178 and p_user_effective_date between TP.start_date and TP.end_date;
179
180 if p_owner_effective_date >= l_period_start_date then
181 p_expiry_information := 0;
182 else
183 p_expiry_information := 1;
184 end if;
185 end pptd_ec;
186
187 /*------------------------------ aptd_ec ------------------------------------*/
188 /*
189 NAME
190 aptd_ec - Assignment Period To Date Expiry Check.
191 DESCRIPTION
192 Expiry checking code for the following:
193 BF Assignment-level Period to Date Balance Dimension
194 NOTES
195 Associated dimension is expiry checked at
196 Payroll Action level.
197 */
201 p_user_payroll_action_id in number, -- current run.
198 procedure aptd_ec
199 (
200 p_owner_payroll_action_id in number, -- run created balance.
202 p_owner_assignment_action_id in number, -- assact created balance.
203 p_user_assignment_action_id in number, -- current assact..
204 p_owner_effective_date in date, -- eff date of balance.
205 p_user_effective_date in date, -- eff date of current run.
206 p_dimension_name in varchar2, -- balance dimension name.
207 p_expiry_information out number -- dimension expired flag.
208 ) is
209 l_period_start_date date;
210 begin
211 select TP.start_date
212 into l_period_start_date
213 from per_time_periods TP,
214 pay_payroll_actions PACT
215 where PACT.payroll_action_id = p_user_payroll_action_id
216 and PACT.payroll_id = TP.payroll_id
217 and p_user_effective_date between TP.start_date and TP.end_date;
218 if p_owner_effective_date >= l_period_start_date then
219 p_expiry_information := 0;
220 else
221 p_expiry_information := 1;
222 end if;
223 end aptd_ec;
224
225 /*---------------------------- pptd_alc_ec ---------------------------------*/
226 /*
227 NAME
228 aptd_ec - Person Period To Date Assact Level Expiry Check.
229 DESCRIPTION
230 Expiry checking code for the following:
231 BF Person-level Period to Date Balance Dimension (test)
232 NOTES
233 The associated dimension is expiry checked at
234 Assignment Action ID level.
235
236 This expiry checking code does access the list of
237 balance context values.
238 */
239 procedure pptd_alc_ec
240 (
241 p_owner_payroll_action_id in number, -- run created balance.
242 p_user_payroll_action_id in number, -- current run.
243 p_owner_assignment_action_id in number, -- assact created balance.
244 p_user_assignment_action_id in number, -- current assact..
245 p_owner_effective_date in date, -- eff date of balance.
246 p_user_effective_date in date, -- eff date of current run.
247 p_dimension_name in varchar2, -- balance dimension name.
248 p_balance_context_values in varchar2, -- list of context values.
249 p_expiry_information out number -- dimension expired flag.
250 ) is
251 l_period_start_date date;
252 begin
253 select TP.start_date
254 into l_period_start_date
255 from per_time_periods TP,
256 pay_assignment_actions ACT,
257 pay_payroll_actions PACT
258 where ACT.assignment_action_id = p_user_assignment_action_id
259 and ACT.payroll_action_id = PACT.payroll_action_id
260 and PACT.payroll_id = TP.payroll_id
261 and p_user_effective_date between TP.start_date and TP.end_date;
262
263 if p_owner_effective_date >= l_period_start_date then
264 p_expiry_information := 0;
265 else
266 p_expiry_information := 1;
267 end if;
268
269 end pptd_alc_ec;
270
271 /*-------------------------- never_expires --------------------------------*/
272 /*
273 NAME
274 never_expires - Never expires procedure.
275 DESCRIPTION
276 When called, always returns a value that will not cause expiry.
277 NOTES
278 Although this expiry check could be replaced in reality with
279 the 'never expires' expiry checking level, this is left here
280 to reproduce the functionality of the original tests.
281 */
282 procedure never_expires
283 (
284 p_owner_payroll_action_id in number, -- run created balance.
285 p_user_payroll_action_id in number, -- current run.
286 p_owner_assignment_action_id in number, -- assact created balance.
287 p_user_assignment_action_id in number, -- current assact..
288 p_owner_effective_date in date, -- eff date of balance.
289 p_user_effective_date in date, -- eff date of current run.
290 p_dimension_name in varchar2, -- balance dimension name.
291 p_expiry_information out number -- dimension expired flag.
292 ) is
293 begin
294 p_expiry_information := 0; -- never expires.
295 end never_expires;
296
297 /*-------------------------- always_expires --------------------------------*/
298 /*
299 NAME
300 always_expires - Always expires procedure.
301 DESCRIPTION
302 Returns value that will cause expiry.
303 NOTES
304 This is useful for where we wish to create a balance on
305 the database that is really a run level balance, but happens
306 to have a dimension type of 'A' or 'P'.
307 */
308 procedure always_expires
309 (
310 p_owner_payroll_action_id in number, -- run created balance.
311 p_user_payroll_action_id in number, -- current run.
312 p_owner_assignment_action_id in number, -- assact created balance.
313 p_user_assignment_action_id in number, -- current assact..
314 p_owner_effective_date in date, -- eff date of balance.
315 p_user_effective_date in date, -- eff date of current run.
316 p_dimension_name in varchar2, -- balance dimension name.
317 p_expiry_information out number -- dimension expired flag.
318 ) is
319 begin
320 p_expiry_information := 1; -- never expires.
321 end always_expires;
325 NAME
322
323 /*------------------------------ pcon_ec -----------------------------------*/
324 /*
326 pcon_ec - Person CONtracted in Expiry Check.
327 DESCRIPTION
328 Expiry checking code for the following:
329 BF Person-level Contracted In YTD Balance Dimension
330 NOTES
331 The associated dimension is expiry checked at
332 Payroll Action level.
333
334 The associated dimension never expires, thus the
335 expiry_information flag always returns (FALSE).
336 */
337 procedure pcon_ec
338 (
339 p_owner_payroll_action_id in number, -- run created balance.
340 p_user_payroll_action_id in number, -- current run.
341 p_owner_assignment_action_id in number, -- assact created balance.
342 p_user_assignment_action_id in number, -- current assact..
343 p_owner_effective_date in date, -- eff date of balance.
344 p_user_effective_date in date, -- eff date of current run.
345 p_dimension_name in varchar2, -- balance dimension name.
346 p_expiry_information out number -- dimension expired flag.
347 ) is
348 l_tax_year_start date;
349 begin
350 select to_date('06-04-' || to_char( to_number(
351 to_char( p_user_effective_date,'YYYY'))
352 + decode(sign( p_user_effective_date - to_date('06-04-'
353 || to_char(p_user_effective_date,'YYYY'),'DD-MM-YYYY')),
354 -1,-1,0)),'DD-MM-YYYY')
355 into l_tax_year_start
356 from dual;
357 if p_owner_effective_date >= l_tax_year_start then
358 p_expiry_information := 0;
359 else
360 p_expiry_information := 1;
361 end if;
362 end pcon_ec;
363
364 /*------------------------------ pcon_fc -----------------------------------*/
365 /*
366 NAME
367 pcon_fc - Person CONtracted in Feed Check.
368 DESCRIPTION
369 Feed checking code for the following:
370 BF Person-level Contracted In YTD Balance Dimension
371 NOTES
372 <none>
373 */
374 procedure pcon_fc
375 (
376 p_payroll_action_id in number,
377 p_assignment_action_id in number,
378 p_assignment_id in number,
379 p_effective_date in date,
380 p_dimension_name in varchar2,
381 p_balance_contexts in varchar2,
382 p_feed_flag in out number
383 ) is
384 ni_status varchar2(30);
385 begin
386 select nvl(ass_attribute1, 'CO')
387 into ni_status
388 from per_assignments_f
389 where assignment_id = p_assignment_id
390 and p_effective_date between
391 effective_start_date and effective_end_date;
392
393 if ni_status = 'CI' then
394 p_feed_flag := 1;
395 else
396 p_feed_flag := 0;
397 end if;
398 end pcon_fc;
399
400 end pay_bf_expc;
[Home] [Help]