1 PACKAGE PAY_CORE_FF_UDFS AUTHID CURRENT_USER as
2 /* $Header: paycoreffudfs.pkh 120.8.12020000.1 2012/06/28 16:24:54 appldev ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1994 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : pay_core_ff_udfs
11 Filename : paycoreffudfs.sql
12 Change List
13 -----------
14 Date Name Vers Bug No Description
15 ---- ---- ---- ------ -----------
16 01-May-2005 sodhingr 115.0 Defines user defined function
17 used by international payroll
18 14-JUN-2005 sodhingr 115.1 Added the function
19 get_hourly_rate that returns
20 the hourly rate based on Salary
21 Basis.also added the function
22 calculate_actual_hours_worked
23 that calculates the hours
24 worked based on ATG/
25 workschedule/Std hrs
26 16-JUN-2005 pganguly 115.2 Fixed the GSCC issue.
27 07-OCT-2005 sodhingr 115.3 removed convert_period_type and added
28 p_days_or_hours to calculate_actual_hours_worked
29
30 07-FEB-2005 sodhingr 115.4 added convert_period_type and calculate_period_earnings
31 that can be used by the localization team and take care
32 of proration if core proration is not enabled.
33 08-Jan-2008 sudedas 115.4 6718164 Added new Function term_skip_rule_rwage
34 25-Aug-2008 sudedas 115.5 5895804 Added Functions hours_between, calc_reduced_reg
35 3556204 calc_vacation_pay, calc_sick_pay
36 ER#3855241
37 22-Jun-2009 sudedas 115.7 Added function get_asg_status_typ.
38 06-Jul-2009 sudedas 115.8 8637053 Added context element_type_id to function
39 get_num_period_curr_year.
40 */
41
42
43 FUNCTION get_hourly_rate(
44 p_bg IN NUMBER -- context
45 ,p_assignment_id IN NUMBER -- context
46 ,p_payroll_id IN NUMBER -- context
47 ,p_element_entry_id IN NUMBER -- context
48 ,p_date_earned IN DATE -- context
49 ,p_assignment_action_id IN NUMBER)
50 RETURN NUMBER ;
51
52 FUNCTION calculate_actual_hours_worked
53 (assignment_action_id IN number --Context
54 ,assignment_id IN number --Context
55 ,business_group_id IN number --Context
56 ,element_entry_id IN number --Context
57 ,date_earned IN date --Context
58 ,p_period_start_date IN date
59 ,p_period_end_date IN date
60 ,p_schedule_category IN varchar2 default 'WORK'-- 'WORK'/'PAGER'
61 ,p_include_exceptions IN varchar2 default ''
62 ,p_busy_tentative_as IN varchar2 default 'FREE'-- 'BUSY'/FREE/NULL
63 ,p_legislation_code IN varchar2 default ''
64 ,p_schedule_source IN OUT nocopy varchar2 -- 'PER_ASG' for asg
65 ,p_schedule IN OUT nocopy varchar2 -- schedule
66 ,p_return_status OUT nocopy number
67 ,p_return_message OUT nocopy varchar2
68 ,p_days_or_hours IN VARCHAR2 default 'H' -- 'D' for days, 'H' for hours
69 )
70 RETURN NUMBER;
71
72 FUNCTION standard_hours_worked(
73 p_std_hrs in NUMBER,
74 p_range_start in DATE,
75 p_range_end in DATE,
76 p_std_freq in VARCHAR2) RETURN NUMBER;
77
78 FUNCTION Calculate_Period_Earnings (
79 p_bus_grp_id in NUMBER,
80 p_asst_id in NUMBER,
81 p_payroll_id in NUMBER,
82 p_ele_entry_id in NUMBER,
83 p_tax_unit_id in NUMBER,
84 p_date_earned in DATE,
85 p_assignment_action_id in NUMBER,
86 p_pay_basis in VARCHAR2 default NULL,
87 p_inpval_name in VARCHAR2 default NULL,
88 p_ass_hrly_figure in NUMBER,
89 p_period_start in DATE,
90 p_period_end in DATE,
91 p_actual_hours_worked in out nocopy NUMBER,
92 p_vac_hours_worked in out nocopy NUMBER,
93 p_vac_pay in out nocopy NUMBER,
94 p_sick_hours_worked in out nocopy NUMBER,
95 p_sick_pay in out nocopy NUMBER,
96 p_prorate in VARCHAR2 default 'Y',
97 p_asst_std_freq in VARCHAR2 default NULL)
98 RETURN NUMBER;
99 --
100
101 FUNCTION Convert_Period_Type(
102 p_bg in NUMBER -- context
103 ,p_assignment_id in NUMBER -- context
104 ,p_payroll_id in NUMBER -- context
105 ,p_element_entry_id in NUMBER -- context
106 ,p_date_earned in DATE -- context
107 ,p_assignment_action_id in NUMBER -- context
108 ,p_period_start_date IN DATE
109 ,p_period_end_date IN DATE
110 /*,p_schedule_category IN varchar2 --Optional
111 ,p_include_exceptions IN varchar2 --Optional
112 ,p_busy_tentative_as IN varchar2 --Optional
113 ,p_schedule_source IN varchar2
114 ,p_schedule IN varchar2*/
115 ,p_figure in NUMBER
116 ,p_from_freq in VARCHAR2
117 ,p_to_freq in VARCHAR2
118 ,p_asst_std_freq in VARCHAR2 default NULL
119 ,p_rate_calc_override in VARCHAR2 default 'NOT ENTERED')
120 RETURN NUMBER;
121 --
122 -- Added For Skip Rule for "Regular Wages" Element, "REGULAR_PAY"
123
124 FUNCTION term_skip_rule_rwage(ctx_payroll_id NUMBER
125 ,ctx_assignment_id NUMBER
126 ,ctx_date_earned DATE
127 ,p_user_entered_time VARCHAR2
128 ,p_final_pay_processed VARCHAR2
129 ,p_lspd_pay_processed VARCHAR2
130 ,p_payroll_termination_type VARCHAR2
131 ,p_bg_termination_type VARCHAR2
132 ,p_already_processed VARCHAR2)
133 RETURN VARCHAR2;
134
135 -- Following Functions have been added as part of Enabling Core Proration
136 -- for "Regular Salary" and "Regular Wages" elements.
137 -- hours_between will be called by 'REGULAR_SALARY', 'REGULAR_WAGES'
138 -- Fast Formulas by Formula Function HOURS_BETWEEN
139 -- Similarly calc_reduced_reg is by REDUCED_REGULAR_CALC
140 -- calc_vacation_pay by CALC_VAC_PAY
141 -- calc_sick_pay by CALC_SICK_PAY Formula Functions respectively.
142
143 Function hours_between( business_group_id IN number --Context
144 ,assignment_id IN number --Context
145 ,assignment_action_id IN number --Context
146 ,date_earned IN date --Context
147 ,element_entry_id IN number --Context
148 ,p_period_start_date IN date
149 ,p_period_end_date IN date
150 ,p_schedule_category IN varchar2 default 'WORK'-- 'WORK'/'PAGER'
151 ,p_include_exceptions IN varchar2 default ''
152 ,p_busy_tentative_as IN varchar2 default 'FREE'-- 'BUSY'/FREE/NULL
153 ,p_legislation_code IN varchar2 default ''
154 ,p_schedule_source IN OUT nocopy varchar2 -- 'PER_ASG' for asg
155 ,p_schedule IN OUT nocopy varchar2 -- schedule
156 ,p_return_status OUT nocopy number
157 ,p_return_message OUT nocopy varchar2
158 ,p_days_or_hours IN VARCHAR2 default 'H' -- 'D' for days, 'H' for hours
159 ) RETURN NUMBER;
160
161 FUNCTION calc_reduced_reg(ctx_assignment_id IN NUMBER
162 ,ctx_assignment_action_id IN NUMBER
163 ,p_period_end_dt IN DATE
164 ,p_prorate_start_dt IN DATE
165 ,p_prorate_end_dt IN DATE
166 ,p_red_reg_earn IN OUT NOCOPY NUMBER
167 ,p_red_reg_hrs IN OUT NOCOPY NUMBER
168 )
169 RETURN VARCHAR2;
170
171 FUNCTION calc_vacation_pay (ctx_asg_id IN NUMBER
172 ,p_period_end_dt IN DATE
173 ,p_prorate_start_dt IN DATE
174 ,p_prorate_end_dt IN DATE
175 ,p_curr_rate IN NUMBER
176 ,p_vac_hours IN OUT NOCOPY NUMBER)
177 RETURN NUMBER;
178
179 FUNCTION calc_sick_pay (ctx_asg_id IN NUMBER
180 ,p_period_end_dt IN DATE
181 ,p_prorate_start_dt IN DATE
182 ,p_prorate_end_dt IN DATE
183 ,p_curr_rate IN NUMBER
184 ,p_sick_hours IN OUT NOCOPY NUMBER)
185 RETURN NUMBER;
186
187 FUNCTION get_upgrade_flag(ctx_ele_typ_id IN NUMBER)
188 RETURN VARCHAR2;
189
190 FUNCTION get_num_period_curr_year(ctx_bg_id in NUMBER
191 ,ctx_payroll_id in NUMBER
192 ,ctx_ele_type_id in NUMBER
193 ,period_end_date in DATE)
194 RETURN NUMBER;
195
196 -- Following function is called from
197 -- Formula Function FF IS_ASSIGNMENT_ACTIVE
198
199 FUNCTION get_asg_status_typ(ctx_asg_id IN NUMBER
200 ,prorate_end_dt IN DATE)
201 RETURN VARCHAR2;
202
203 g_normal_hours NUMBER DEFAULT 0;
204
205 END pay_core_ff_udfs ;
[Home] [Help]