1 package hr_us_ff_udfs AUTHID CURRENT_USER as
2 /* $Header: pyusudfs.pkh 120.3.12020000.2 2012/07/05 03:55:07 amnaraya ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : chained_element_exists
11 Filename : pychaind.sql
12 Change List
13 -----------
14 Date Name Vers Bug No Description
15 ---- ---- ---- ------ -----------
16 16-NOV-93 hparicha 1.0 Created
17 07-JAN-94 jmychale 40.7 G491 Removed actual hours worked
18 param from Calculate_Period_
19 Earnings.
20 13-JAN-94 hparicha 40.8 G497 Reverted to version 40.6
21 NEED actual hours worked for
22 Statement of Earnings report.
23 02-FEB-94 hparicha 40.9 G542 Added Dedn_Freq_Factor.
24 06-JUN-94 hparicha 40.10 G815 Added Arrearage function.
25 15-JUL-94 hparicha 40.2 G907 Beta I freeze (new version
26 numbers b/c now in UK arcs)
27 26-OCT-94 hparicha 40.3 G1342 Included as part of this STU
28 bug. Added "addr_val" for
29 use by VERTEX formulae and
30 "get_geocode".
31 04-JAN-95 hparicha 40.4 G1565 Vacation/Sick correlation
32 to Regular Pay - changes to
33 Calc Period Earns.
34 40.5 ???
35 40.6 ???
36 40.7 ???
37
38 12-JUL-96 hparicha 40.8 366215 Removed consumed_entry fn
39 because package body too large...
40 moved into pay_consumed_entry pckg
41 pyconsum.pkh, pkb.
42 --
43 08-Dec-97 kmundair 40.9(110.1) 509120 overloaded addr_val function.
44 11-Aug-99 djsohi 115.2 added the function pay_us_country to get the country when country code
45 is given. This function is used
46 in the fast formula for Kentucky
47 diskette
48 06-Mar-01 ssarma 115.3 Added a new parameter to override
49 payroll level setting for
50 convert_period_type function.
51 24-Jun-02 rsirigir 115.6 As per bug 2429333
52 modified the thre parameters in
53 FUNCTION Convert_Period_Type from
54 p_asst_work_schedule in VARCHAR2 default NULL,
55 p_from_freq in VARCHAR2 default NULL
56 p_to_freq in VARCHAR2 default NULL,
57 to
58 p_asst_work_schedule in VARCHAR2
59 default 'NOT ENTERED',
60 p_from_freq in VARCHAR2
61 default 'NOT ENTERED',
62 p_to_freq in VARCHAR2
63 default 'NOT ENTERED',
64 09-Jan-2003 ekim 115.8 GSCC warnings fix for nocopy.
65 10-Aug-2011 emunisek 115.10 12588037 Added function catchup_type_details, which
66 will be used to store the catchup_type selected
67 for 403/457 Catchup Elements. The stored value
68 will be used in Roth Formulas.
69 10-Aug-2011 emunisek 115.11 12588037 Corrected GSCC Errors
70 */
71 --
72 FUNCTION Calculate_Period_Earnings (
73 p_bus_grp_id in NUMBER,
74 p_asst_id in NUMBER,
75 p_payroll_id in NUMBER,
76 p_ele_entry_id in NUMBER,
77 p_tax_unit_id in NUMBER,
78 p_date_earned in DATE,
79 p_pay_basis in VARCHAR2 default NULL,
80 p_inpval_name in VARCHAR2 default NULL,
81 p_ass_hrly_figure in NUMBER,
82 p_period_start in DATE,
83 p_period_end in DATE,
84 p_work_schedule in VARCHAR2 default NULL,
85 p_asst_std_hrs in NUMBER default NULL,
86 p_actual_hours_worked in out nocopy NUMBER,
87 p_vac_hours_worked in out nocopy NUMBER,
88 p_vac_pay in out nocopy NUMBER,
89 p_sick_hours_worked in out nocopy NUMBER,
90 p_sick_pay in out nocopy NUMBER,
91 p_prorate in VARCHAR2 default 'Y',
92 p_asst_std_freq in VARCHAR2 default NULL)
93 RETURN NUMBER;
94 --
95 FUNCTION standard_hours_worked(
96 p_std_hrs in NUMBER,
97 p_range_start in DATE,
98 p_range_end in DATE,
99 p_std_freq in VARCHAR2) RETURN NUMBER;
100 --
101 FUNCTION Convert_Period_Type(
102 p_bus_grp_id in NUMBER,
103 p_payroll_id in NUMBER,
104 p_asst_work_schedule in VARCHAR2 default 'NOT ENTERED',
105 --p_asst_work_schedule in VARCHAR2 default NULL,
106 p_asst_std_hours in NUMBER default NULL,
107 p_figure in NUMBER,
108 p_from_freq in VARCHAR2 default 'NOT ENTERED',
109 p_to_freq in VARCHAR2 default 'NOT ENTERED',
110 --p_from_freq in VARCHAR2 default NULL,
111 --p_to_freq in VARCHAR2 default NULL,
112 p_period_start_date in DATE default NULL,
113 p_period_end_date in DATE default NULL,
114 p_asst_std_freq in VARCHAR2 default NULL,
115 p_rate_calc_override in VARCHAR2 default 'NOT ENTERED')
116 RETURN NUMBER;
117 --
118 FUNCTION work_schedule_total_hours(
119 p_bg_id in NUMBER,
120 p_ws_name in VARCHAR2,
121 p_range_start in DATE default NULL,
122 p_range_end in DATE default NULL) RETURN NUMBER;
123 --
124 FUNCTION chained_element_exists(p_bg_id in NUMBER,
125 p_asst_id in NUMBER,
126 p_payroll_id in NUMBER,
127 p_date_earned in DATE,
128 p_ele_name IN VARCHAR2) RETURN VARCHAR2;
129 --
130 FUNCTION us_jurisdiction_val (p_jurisdiction_code in VARCHAR2) RETURN VARCHAR2;
131 --
132 FUNCTION get_process_run_flag ( p_date_earned IN DATE,
133 p_ele_type_id IN NUMBER) RETURN VARCHAR2;
134 --
135 FUNCTION check_dedn_freq ( p_payroll_id IN NUMBER,
136 p_bg_id IN NUMBER,
137 p_pay_action_id IN NUMBER,
138 p_date_earned IN DATE,
139 p_ele_type_id IN NUMBER) RETURN VARCHAR2;
140 --
141 FUNCTION Separate_Check_Skip (
142 p_bg_id in NUMBER,
143 p_element_type_id in NUMBER,
144 p_assact_id in NUMBER,
145 p_payroll_id in NUMBER,
146 p_date_earned in DATE) RETURN VARCHAR2;
147 --
148 FUNCTION Other_Non_Separate_Check (
149 p_date_earned IN DATE,
150 p_ass_id IN NUMBER) RETURN VARCHAR2;
151 --
152 FUNCTION OT_Base_Rate ( p_bg_id in NUMBER,
153 p_pay_id in NUMBER,
154 p_ass_id in NUMBER,
155 p_ass_action_id in NUMBER,
156 p_date_earned in DATE,
157 p_work_sched in VARCHAR2 default NULL,
158 p_std_hours in NUMBER default NULL,
159 p_ass_salary in NUMBER,
160 p_ass_sal_basis in VARCHAR2,
161 p_std_freq in VARCHAR2 default NULL)
162 RETURN NUMBER;
163 --
164 FUNCTION Dedn_Freq_Factor (
165 p_payroll_id in NUMBER,
166 p_element_type_id in NUMBER,
167 p_date_earned in DATE,
168 p_ele_period_type in VARCHAR2 default NULL)
169 RETURN NUMBER;
170 --
171 FUNCTION Arrearage ( p_eletype_id IN NUMBER,
172 p_date_earned IN DATE,
173 p_assignment_id IN NUMBER,
174 p_ele_entry_id IN NUMBER,
175 p_partial_flag IN VARCHAR2 DEFAULT 'N',
176 p_net_asg_run IN NUMBER,
177 p_arrears_itd IN NUMBER,
178 p_guaranteed_net IN NUMBER,
179 p_dedn_amt IN NUMBER,
180 p_to_arrears IN OUT nocopy NUMBER,
181 p_not_taken IN OUT nocopy NUMBER)
182 RETURN NUMBER;
183 --
184 FUNCTION addr_val ( p_state_abbrev IN VARCHAR2 DEFAULT NULL,
185 p_county_name IN VARCHAR2 DEFAULT NULL,
186 p_city_name IN VARCHAR2 DEFAULT NULL,
187 p_zip_code IN VARCHAR2 DEFAULT NULL)
188 RETURN VARCHAR2;
189 --
190 FUNCTION addr_val ( p_state_abbrev IN VARCHAR2 DEFAULT NULL,
191 p_county_name IN VARCHAR2 DEFAULT NULL,
192 p_city_name IN VARCHAR2 DEFAULT NULL,
193 p_zip_code IN VARCHAR2 DEFAULT NULL,
194 p_skip_rule IN VARCHAR2 )
195 RETURN VARCHAR2;
196
197
198 -- function to return short coutry name for given coutry_code
199 -- us is united state
200
201 FUNCTION pay_us_country(p_territory_code IN VARCHAR2) RETURN VARCHAR2;
202
203 /*Added for Bug#12588037 */
204
205 TYPE catchup_type_rec IS RECORD
206 ( catchup_403 varchar2(1),
207 base_element_403 varchar2(100),
208 catchup_type_403 varchar2(100),
209 catchup_457 varchar2(1),
210 base_element_457 varchar2(100),
211 catchup_type_457 varchar2(100)
212 );
213
214 TYPE catchup_type_tab IS TABLE OF catchup_type_rec
215 INDEX BY BINARY_INTEGER;
216
217 catchup_type_table catchup_type_tab;
218
219 -- function to store the Catchup Type selection made in Catchup Type element for 403/457 Pre-Tax Element.
220 -- the stored value will be used in corresponding Roth Calculation.
221
222 FUNCTION catchup_type_details ( p_assignment_action_id IN NUMBER,
223 p_plan IN NUMBER,
224 p_base_element_name IN VARCHAR2,
225 p_catchup_type IN OUT NOCOPY VARCHAR2,
226 p_mode IN VARCHAR2
227 )
228 RETURN VARCHAR2;
229
230 /*End of changes for Bug#12588037 */
231
232 /*Function created for Bug#13614766*/
233
234 FUNCTION direct_fed_data_archived ( p_reporting_year IN VARCHAR2 )
235 RETURN VARCHAR2;
236
237 /*End of changes for Bug#13614766* */
238 --
239 END hr_us_ff_udfs;