1 PACKAGE hr_gbbal AS
2 /* $Header: pygbbal.pkh 120.2.12000000.1 2007/01/17 19:59:41 appldev noship $ */
3 ----------------------------------------------------------------------
4 FUNCTION span_start(
5 p_input_date IN DATE,
6 p_frequency IN NUMBER DEFAULT 1,
7 p_start_dd_mm IN VARCHAR2 DEFAULT '06-04-')
8 RETURN date ;
9 PRAGMA RESTRICT_REFERENCES (span_start, WNDS, WNPS);
10 -----------------------------------------------------------------------
11 FUNCTION span_end(
12 p_input_date IN DATE,
13 p_frequency IN NUMBER DEFAULT 1,
14 p_start_dd_mm IN VARCHAR2 DEFAULT '06-04-')
15 RETURN date ;
16 PRAGMA RESTRICT_REFERENCES (span_end, WNDS, WNPS);
17 -----------------------------------------------------------------------
18 -- what is the latest reset date for a particular dimension
19 FUNCTION dimension_reset_date(
20 p_dimension_name IN VARCHAR2,
21 p_user_date IN DATE,
22 p_business_group_id IN NUMBER)
23 RETURN date;
24 PRAGMA RESTRICT_REFERENCES (dimension_reset_date, WNDS, WNPS);
25 -----------------------------------------------------------------------
26 FUNCTION start_director(
27 p_assignment_id NUMBER,
28 p_start_date DATE ,
29 p_end_date DATE )
30 RETURN DATE;
31 PRAGMA RESTRICT_REFERENCES (start_director, WNDS, WNPS);
32 -----------------------------------------------------------------------
33 FUNCTION balance (
34 p_assignment_action_id IN NUMBER,
35 p_defined_balance_id IN NUMBER,
36 p_effective_date IN DATE DEFAULT NULL) -- For D.M. Calls
37 RETURN number ;
38 -----------------------------------------------------------------------
39 FUNCTION calc_per_ptd(
40 p_assignment_action_id IN NUMBER,
41 p_balance_type_id IN NUMBER,
42 p_effective_date IN DATE DEFAULT NULL,
43 p_assignment_id IN NUMBER)
44 RETURN NUMBER;
45 -----------------------------------------------------------------------
46 FUNCTION calc_per_ptd_action(
47 p_assignment_action_id IN NUMBER,
48 p_balance_type_id IN NUMBER,
49 p_effective_date IN DATE DEFAULT NULL)
50 RETURN NUMBER;
51 -----------------------------------------------------------------------
52 FUNCTION calc_per_ptd_date(
53 p_assignment_id IN NUMBER,
54 p_balance_type_id IN NUMBER,
55 p_effective_date IN DATE)
56 RETURN NUMBER;
57 -----------------------------------------------------------------------
58 FUNCTION calc_asg_proc_ytd_action(
59 p_assignment_action_id IN NUMBER,
60 p_balance_type_id IN NUMBER,
61 p_effective_date IN DATE DEFAULT NULL)
62 RETURN NUMBER;
63 -----------------------------------------------------------------------
64 FUNCTION calc_asg_proc_ytd(
65 p_assignment_action_id IN NUMBER,
66 p_balance_type_id IN NUMBER,
67 p_effective_date IN DATE DEFAULT NULL,
68 p_assignment_id IN NUMBER)
69 RETURN NUMBER;
70 -----------------------------------------------------------------------
71 --date mode function
72 FUNCTION calc_asg_proc_ytd_date(
73 p_assignment_id IN NUMBER,
74 p_balance_type_id IN NUMBER,
75 p_effective_date IN DATE)
76 RETURN NUMBER;
77 -----------------------------------------------------------------------
78 FUNCTION calc_asg_qtd_action(
79 p_assignment_action_id IN NUMBER,
80 p_balance_type_id IN NUMBER,
81 p_effective_date IN DATE DEFAULT NULL)
82 RETURN NUMBER;
83 -----------------------------------------------------------------------
84 FUNCTION calc_asg_qtd(
85 p_assignment_action_id IN NUMBER,
86 p_balance_type_id IN NUMBER,
87 p_effective_date IN DATE DEFAULT NULL,
88 p_assignment_id IN NUMBER
89 )
90 RETURN NUMBER;
91 -----------------------------------------------------------------------
92 --date mode function
93 FUNCTION calc_asg_qtd_date(
94 p_assignment_id IN NUMBER,
95 p_balance_type_id IN NUMBER,
96 p_effective_date IN DATE)
97 RETURN NUMBER;
98 -----------------------------------------------------------------------
99 FUNCTION calc_all_balances(
100 p_assignment_action_id IN NUMBER,
101 p_defined_balance_id IN NUMBER)
102 RETURN NUMBER;
103 -----------------------------------------------------------------------
104 FUNCTION calc_all_balances(
105 p_effective_date IN DATE,
106 p_assignment_id IN NUMBER,
107 p_defined_balance_id IN NUMBER)
108 RETURN NUMBER;
109 -----------------------------------------------------------------------
110 FUNCTION calc_asg_ytd(
111 p_assignment_action_id IN NUMBER,
112 p_balance_type_id IN NUMBER,
113 p_effective_date IN DATE DEFAULT NULL,
114 p_assignment_id IN NUMBER)
115 RETURN NUMBER;
116 -----------------------------------------------------------------------
117 FUNCTION calc_asg_ytd_action(
118 p_assignment_action_id IN NUMBER,
119 p_balance_type_id IN NUMBER,
120 p_effective_date IN DATE DEFAULT NULL)
121 RETURN NUMBER;
122 -----------------------------------------------------------------------
123 --date mode function
124 FUNCTION calc_asg_ytd_date(
125 p_assignment_id IN NUMBER,
126 p_balance_type_id IN NUMBER,
127 p_effective_date IN DATE)
128 RETURN NUMBER;
129 -----------------------------------------------------------------------
130 FUNCTION calc_asg_stat_ytd(
131 p_assignment_action_id IN NUMBER,
132 p_balance_type_id IN NUMBER,
133 p_effective_date IN DATE DEFAULT NULL,
134 p_assignment_id IN NUMBER)
135 RETURN NUMBER;
136 -----------------------------------------------------------------------
137 FUNCTION calc_asg_stat_ytd_action(
138 p_assignment_action_id IN NUMBER,
139 p_balance_type_id IN NUMBER,
140 p_effective_date IN DATE DEFAULT NULL)
141 RETURN NUMBER;
142 -----------------------------------------------------------------------
143 --date mode function
144 FUNCTION calc_asg_stat_ytd_date(
145 p_assignment_id IN NUMBER,
146 p_balance_type_id IN NUMBER,
147 p_effective_date IN DATE)
148 RETURN NUMBER;
149 -----------------------------------------------------------------------
150 FUNCTION calc_asg_proc_ptd_action(
151 p_assignment_action_id IN NUMBER,
152 p_balance_type_id IN NUMBER,
153 p_effective_date IN DATE DEFAULT NULL)
154 RETURN NUMBER;
155 -----------------------------------------------------------------------
156 FUNCTION calc_asg_proc_ptd(
157 p_assignment_action_id IN NUMBER,
158 p_balance_type_id IN NUMBER,
159 p_effective_date IN DATE DEFAULT NULL,
160 p_assignment_id IN NUMBER)
161 RETURN NUMBER;
162 -----------------------------------------------------------------------
163 --date mode function
164 FUNCTION calc_asg_proc_ptd_date(
165 p_assignment_id IN NUMBER,
166 p_balance_type_id IN NUMBER,
167 p_effective_date IN DATE)
168 RETURN NUMBER;
169 -----------------------------------------------------------------------
170 FUNCTION calc_asg_run_action(
171 p_assignment_action_id IN NUMBER,
172 p_balance_type_id IN NUMBER,
173 p_effective_date IN DATE DEFAULT NULL)
174 RETURN NUMBER;
175 -----------------------------------------------------------------------
176 FUNCTION calc_asg_run(
177 p_assignment_action_id IN NUMBER,
178 p_balance_type_id IN NUMBER,
179 p_effective_date IN DATE DEFAULT NULL,
180 p_assignment_id IN NUMBER)
181 RETURN NUMBER;
182 -----------------------------------------------------------------------
183 --date mode function
184 FUNCTION calc_asg_run_date(
185 p_assignment_id IN NUMBER,
186 p_balance_type_id IN NUMBER,
187 p_effective_date IN DATE)
188 RETURN NUMBER;
189 -----------------------------------------------------------------------
190 FUNCTION calc_payment_action(
191 p_assignment_action_id IN NUMBER,
192 p_balance_type_id IN NUMBER,
193 p_effective_date IN DATE DEFAULT NULL)
194 RETURN NUMBER;
195 -----------------------------------------------------------------------
196 FUNCTION calc_payment(
197 p_assignment_action_id IN NUMBER,
198 p_balance_type_id IN NUMBER,
199 p_effective_date IN DATE DEFAULT NULL,
200 p_assignment_id IN NUMBER)
201 RETURN NUMBER;
202 -----------------------------------------------------------------------
203 --date mode function
204 FUNCTION calc_payment_date(
205 p_assignment_id IN NUMBER,
206 p_balance_type_id IN NUMBER,
207 p_effective_date IN DATE)
208 RETURN NUMBER;
209 -----------------------------------------------------------------------
210 FUNCTION calc_asg_itd_action(
211 p_assignment_action_id IN NUMBER,
212 p_balance_type_id IN NUMBER,
213 p_effective_date IN DATE DEFAULT NULL)
214 RETURN NUMBER;
215 -----------------------------------------------------------------------
216 FUNCTION calc_asg_itd(
217 p_assignment_id IN NUMBER,
218 p_assignment_action_id IN NUMBER,
219 p_balance_type_id IN NUMBER,
220 p_effective_date IN DATE DEFAULT NULL)
221 RETURN NUMBER;
222 -----------------------------------------------------------------------
223 --date mode function
224 FUNCTION calc_asg_itd_date(
225 p_assignment_id IN NUMBER,
226 p_balance_type_id IN NUMBER,
227 p_effective_date IN DATE)
228 RETURN NUMBER;
229 -----------------------------------------------------------------------
230 FUNCTION calc_asg_td_itd_action(
231 p_assignment_action_id IN NUMBER,
232 p_balance_type_id IN NUMBER,
233 p_effective_date IN DATE DEFAULT NULL)
234 RETURN NUMBER;
235 -----------------------------------------------------------------------
236 FUNCTION calc_asg_td_itd(
237 p_assignment_id IN NUMBER,
238 p_assignment_action_id IN NUMBER,
239 p_balance_type_id IN NUMBER,
240 p_effective_date IN DATE DEFAULT NULL)
241 RETURN NUMBER;
242 -----------------------------------------------------------------------
243 --date mode function
244 FUNCTION calc_asg_td_itd_date(
245 p_assignment_id IN NUMBER,
246 p_balance_type_id IN NUMBER,
247 p_effective_date IN DATE)
248 RETURN NUMBER;
249 -----------------------------------------------------------------------
250 FUNCTION calc_asg_tfr_ptd_action(
251 p_assignment_action_id IN NUMBER,
252 p_balance_type_id IN NUMBER,
253 p_effective_date IN DATE DEFAULT NULL)
254 RETURN NUMBER;
255 -----------------------------------------------------------------------
256 FUNCTION calc_asg_tfr_ptd(
257 p_assignment_action_id IN NUMBER,
258 p_balance_type_id IN NUMBER,
259 p_effective_date IN DATE DEFAULT NULL,
260 p_assignment_id IN NUMBER)
261 RETURN NUMBER;
262 -----------------------------------------------------------------------
263 --date mode function
264 FUNCTION calc_asg_tfr_ptd_date(
265 p_assignment_id IN NUMBER,
266 p_balance_type_id IN NUMBER,
267 p_effective_date IN DATE)
268 RETURN NUMBER;
269 -----------------------------------------------------------------------
270 FUNCTION calc_asg_td_ytd_action(
271 p_assignment_action_id IN NUMBER,
272 p_balance_type_id IN NUMBER,
273 p_effective_date IN DATE DEFAULT NULL)
274 RETURN NUMBER;
275 -----------------------------------------------------------------------
276 FUNCTION calc_asg_td_ytd(
277 p_assignment_action_id IN NUMBER,
278 p_balance_type_id IN NUMBER,
279 p_effective_date IN DATE DEFAULT NULL,
280 p_assignment_id IN NUMBER)
281 RETURN NUMBER;
282 -----------------------------------------------------------------------
283 --date mode function
284 FUNCTION calc_asg_td_ytd_date(
285 p_assignment_id IN NUMBER,
286 p_balance_type_id IN NUMBER,
287 p_effective_date IN DATE)
288 RETURN NUMBER;
289 -----------------------------------------------------------------------
290 -- added by skutteti
291 --
292 FUNCTION calc_asg_td_odd_two_ytd_action(
293 p_assignment_action_id IN NUMBER,
294 p_balance_type_id IN NUMBER,
295 p_effective_date IN DATE DEFAULT NULL)
296 RETURN NUMBER;
297 -----------------------------------------------------------------------
298 FUNCTION calc_asg_td_odd_two_ytd_date(
299 p_assignment_id IN NUMBER,
300 p_balance_type_id IN NUMBER,
301 p_effective_date IN DATE)
302 RETURN NUMBER;
303 -----------------------------------------------------------------------
304 FUNCTION calc_asg_td_odd_two_ytd(
305 p_assignment_action_id IN NUMBER,
306 p_balance_type_id IN NUMBER,
307 p_effective_date IN DATE DEFAULT NULL,
308 p_assignment_id IN NUMBER)
309 RETURN NUMBER;
310 -----------------------------------------------------------------------
311 --
312 FUNCTION calc_asg_td_even_two_ytd_actio(
313 p_assignment_action_id IN NUMBER,
314 p_balance_type_id IN NUMBER,
315 p_effective_date IN DATE DEFAULT NULL)
316 RETURN NUMBER;
317 -----------------------------------------------------------------------
318 FUNCTION calc_asg_td_even_two_ytd_date(
319 p_assignment_id IN NUMBER,
320 p_balance_type_id IN NUMBER,
321 p_effective_date IN DATE)
322 RETURN NUMBER;
323 -----------------------------------------------------------------------
324 FUNCTION calc_asg_td_even_two_ytd(
325 p_assignment_action_id IN NUMBER,
326 p_balance_type_id IN NUMBER,
327 p_effective_date IN DATE DEFAULT NULL,
328 p_assignment_id IN NUMBER)
329 RETURN NUMBER;
330 -----------------------------------------------------------------------
331 --
332 -----------------------------------------------------------------------
333 FUNCTION calc_balance(
334 p_assignment_id IN NUMBER,
335 p_balance_type_id IN NUMBER, -- balance
336 p_period_from_date IN DATE, -- since regular pay date of period
337 p_event_from_date IN DATE, -- since effective date of
338 p_to_date IN DATE, -- sum up to this date
339 p_action_sequence IN NUMBER) -- sum up to this sequence
340 RETURN NUMBER;
341 PRAGMA RESTRICT_REFERENCES (calc_balance, WNDS, WNPS);
342 -----------------------------------------------------------------------
343 FUNCTION calc_element_itd_bal(p_assignment_action_id IN NUMBER,
344 p_balance_type_id IN NUMBER,
345 p_source_id IN NUMBER)
346 RETURN NUMBER;
347 -----------------------------------------------------------------------
348 FUNCTION calc_element_co_itd_bal(p_assignment_action_id IN NUMBER,
349 p_balance_type_id IN NUMBER,
350 p_source_id IN NUMBER,
351 p_source_text IN VARCHAR2)
352 RETURN NUMBER;
353 -----------------------------------------------------------------------
354 FUNCTION calc_element_ptd_bal(p_assignment_action_id IN NUMBER,
355 p_balance_type_id IN NUMBER,
356 p_source_id IN NUMBER)
357 RETURN NUMBER;
358 -----------------------------------------------------------------------
359 FUNCTION get_element_reference(p_run_result_id IN NUMBER,
360 p_database_item_suffix IN VARCHAR2)
361 RETURN VARCHAR2;
362 -----------------------------------------------------------------------
363 FUNCTION get_context_references(p_context_value IN VARCHAR2,
364 p_database_item_suffix IN VARCHAR2)
368 errbuf OUT NOCOPY VARCHAR2,
365 RETURN VARCHAR2;
366 -----------------------------------------------------------------------
367 PROCEDURE create_dimension(
369 retcode OUT NOCOPY NUMBER,
370 p_business_group_id IN NUMBER,
371 p_suffix IN VARCHAR2,
372 p_level IN VARCHAR2,
373 p_start_dd_mm IN VARCHAR2,
374 p_frequency IN NUMBER,
375 p_global_name IN VARCHAR2 DEFAULT NULL);
376 -----------------------------------------------------------------------
377 PROCEDURE check_expiry(
378 p_owner_payroll_action_id IN NUMBER,
379 p_user_payroll_action_id IN NUMBER,
380 p_owner_assignment_action_id IN NUMBER,
381 p_user_assignment_action_id IN NUMBER,
382 p_owner_effective_date IN DATE,
383 p_user_effective_date IN DATE,
384 p_dimension_name IN VARCHAR2,
385 p_expiry_information OUT NOCOPY NUMBER);
386 -----------------------------------------------------------------------
387 function ni_category_exists_in_year (p_assignment_action_id in number,
388 p_category in varchar2)
389 RETURN number;
390 PRAGMA RESTRICT_REFERENCES(ni_category_exists_in_year, WNDS);
391 -----------------------------------------------------------------------
392 FUNCTION get_master_action_id(p_action_type IN VARCHAR2,
393 p_action_id IN NUMBER)
394 RETURN NUMBER;
395 PRAGMA RESTRICT_REFERENCES(get_master_action_id, WNDS);
396 -----------------------------------------------------------------------
397 END hr_gbbal;