1 PACKAGE pay_nl_si_pkg AS
2 /* $Header: pynlsoci.pkh 120.7.12000000.2 2007/02/19 06:16:02 shmittal noship $ */
3 --
4 -- Returns the SI Status
5 -- are entered for a employee
6 FUNCTION get_si_status
7 ( p_assignment_id in number,
8 p_date_earned in date,
9 p_si_class in varchar2
10 ) return number;
11 FUNCTION is_ami
12 ( p_assignment_id in number,
13 p_date_earned in date
14 ) return number;
15 FUNCTION get_payroll_type
16 (p_payroll_id in number
17 ) return varchar2;
18
19 --
20 -- Determines the Number if Week Days(Monday to Friday )
21 -- between two dates
22 FUNCTION Get_Week_Days( P_Start_Date Date,
23 P_End_Date Date) return NUMBER;
24
25 --
26 -- Determines the Maximum SI Days between two dates
27 -- based on the method of 5 days per week
28 FUNCTION Get_Max_SI_Days(P_Assignment_Id Number,
29 P_Start_Date Date,
30 P_End_Date Date) return NUMBER;
31
32 --
33 -- Determines the Number of Unpaid absence Days that reduce
34 -- SI Days indicated by the segment on the Absence
35 FUNCTION Get_Non_SI_Days(P_Assignment_Id Number,
36 P_Start_Date Date,
37 P_End_Date Date) return NUMBER;
38
39 --
40 -- Determines the Total Number of days a Work pattern has been
41 -- setup for regardless of the work pattern start date on
42 -- employee assignment or dates of payroll period.
43
44 FUNCTION Get_Total_Work_Pattern_days(P_Assignment_Id Number) return NUMBER;
45 --
46 -- Determines the Total Number of days markd as Working Days in a
47 -- work pattern regardless of the work pattern start date on
48 -- employee assignment or dates of payroll period.
49
50 FUNCTION Get_Working_Work_Pattern_days(P_Assignment_Id Number) return NUMBER;
51
52 FUNCTION get_part_time_perc (p_assignment_id IN NUMBER
53 ,p_date_earned IN DATE
54 ,p_business_group_id IN NUMBER
55 ,p_assignment_action_id IN NUMBER) RETURN NUMBER;
56
57 FUNCTION get_day_of_week(p_date date) return number;
58
59
60 -------------------------------------------------------------------------------
61 -- Function : get_standard_si_part_time_perc
62 -- To get the Standard SI Part time Percentage using
63 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
64 ---------------------------------------------------------------------------------
65 FUNCTION get_standard_si_part_time_perc (p_assignment_id IN NUMBER
66 ,p_date_earned IN DATE
67 ,p_business_group_id IN NUMBER
68 ,p_assignment_action_id IN NUMBER) RETURN number;
69 -------------------------------------------------------------------------------
70 -- Function : get_pseudo_si_part_time_perc
71 -- To get the Standard SI Part time Percentage using
72 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
73 ---------------------------------------------------------------------------------
74 FUNCTION get_pseudo_si_part_time_perc (p_assignment_id IN NUMBER
75 ,p_date_earned IN DATE
76 ,p_business_group_id IN NUMBER
77 ,p_assignment_action_id IN NUMBER) RETURN number;
78 -------------------------------------------------------------------------------
79 -- Function : get_std_si_rep_part_time_perc
80 -- To get the Standard SI Part time Percentage for Reporting using
81 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
82 ---------------------------------------------------------------------------------
83 FUNCTION get_std_si_rep_part_time_perc (p_assignment_id IN NUMBER
84 ,p_date_earned IN DATE
85 ,p_business_group_id IN NUMBER
86 ,p_assignment_action_id IN NUMBER) RETURN number;
87 -------------------------------------------------------------------------------
88 -- Function : get_pse_si_rep_part_time_perc
89 -- To get the Pseudo SI Part time Percentage for Reporting using
90 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
91 ---------------------------------------------------------------------------------
92 FUNCTION get_pse_si_rep_part_time_perc (p_assignment_id IN NUMBER
93 ,p_date_earned IN DATE
94 ,p_business_group_id IN NUMBER
95 ,p_assignment_action_id IN NUMBER) RETURN number ;
96
97 -----------------------------------------------------------------------------------------------
98 -- Function : get_avg_part_time_percentage
99 -- To get the average Pseudo SI Part time Percentage
100 -- Assignment Id,Period Start Date,Period End Date
101 -----------------------------------------------------------------------------------------------
102 FUNCTION get_avg_part_time_percentage
103 ( p_assignment_id IN per_all_assignments_f.assignment_id%type ,
104 p_period_start_date IN DATE ,
105 p_period_end_date IN DATE
106 )
107 RETURN NUMBER;
108
109 -----------------------------------------------------------------------------------------------
110 -- Function : get_real_si_days
111 -- To get the override for Real SI Days
112 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
113 -----------------------------------------------------------------------------------------------
114 FUNCTION get_real_si_days
115 ( p_assignment_id IN NUMBER
116 ,p_date_earned IN DATE
117 ,p_business_group_id IN NUMBER
118 ,p_assignment_action_id IN NUMBER
119 ,p_payroll_action_id IN NUMBER
120 )
121 RETURN number ;
122
123
124 ---------------------------------------------------------------------------------------
125 -- Function : get_asg_ind_work_hours
126 -- To get the total individual work hours
127 -- Assignment Id,Period Start Date,Period End Date
128 ---------------------------------------------------------------------------------------
129 FUNCTION get_asg_ind_work_hours
130 ( p_assignment_id IN per_all_assignments_f.assignment_id%type ,
131 p_period_start_date IN DATE ,
132 p_period_end_date IN DATE
133 )
134 RETURN NUMBER ;
135
136 ---------------------------------------------------------------------------------------
137 -- Function : get_period_si_days
138 -- To get the period si days
139 ---------------------------------------------------------------------------------------
140 FUNCTION get_period_si_days( p_assignment_id NUMBER
141 ,p_payroll_id NUMBER
142 ,p_effective_date DATE
143 ,p_source_text VARCHAR2
144 ,p_override_day_method VARCHAR2
145 ,p_override_day_value VARCHAR2
146 ,p_avg_ws_si_days NUMBER
147 ,p_override_si_days NUMBER
148 ,p_real_si_days NUMBER
149 ,p_si_day_method VARCHAR2
150 ,p_max_si_method VARCHAR2
151 ,p_multi_asg_si_days NUMBER
152 ,p_year_calc VARCHAR2
153 ,p_override_real_si_days NUMBER
154 ,p_override OUT NOCOPY VARCHAR2
155 ,p_period_si_days_year_calc NUMBER
156 )
157 RETURN NUMBER;
158
159 ---------------------------------------------------------------------------------------
160 -- Function : get_ret_real_si_days
161 -- To get return real si days
162 ---------------------------------------------------------------------------------------
163 FUNCTION get_ret_real_si_days( p_assignment_id NUMBER
164 ,p_payroll_id NUMBER
165 ,p_effective_date DATE
166 ,p_source_text VARCHAR2
167 ,p_source_text2 VARCHAR2
168 ,p_real_si_days NUMBER
169 ,p_override_real_si_days NUMBER
170 ,p_max_si_method VARCHAR2
171 ,p_real_si_sit_ytd NUMBER
172 ,p_real_si_sit_ptd NUMBER
173 ,p_ret_real_si_sit_ytd NUMBER
174 ,p_real_si_per_pay_sitp_ptd NUMBER
175 )
176 RETURN NUMBER ;
177
178 ---------------------------------------------------------------------------------------
179 -- Function : get_thres_or_max_si
180 -- To calculate threshold and max si days
181 ---------------------------------------------------------------------------------------
182 FUNCTION get_thres_or_max_si ( p_assignment_id NUMBER
183 ,p_payroll_id NUMBER
184 ,p_effective_date DATE
185 ,p_calc_code NUMBER
186 ,p_part_time_perc NUMBER
187 ,p_si_days NUMBER
188 ,p_thre_max_si NUMBER
189 )
190 RETURN NUMBER;
191
192
193 ---------------------------------------------------------------------------------------
194 -- Function : get_si_proration_days
195 -- To return number of SI Days for proration
196 ---------------------------------------------------------------------------------------
197
198 FUNCTION get_si_proration_days (p_assignment_id NUMBER
199 ,p_period_start_date DATE
200 ,p_period_end_date DATE
201 ,p_proration_start_date DATE
202 ,p_proration_end_date DATE
203 ,p_period_si_days NUMBER
204 )
205 RETURN NUMBER;
206
207 -----------------------------------------------------------------------------------------------
208 -- Function : get_override_si_days
209 -- To get the override for SI Days
210 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
211 -----------------------------------------------------------------------------------------------
212 FUNCTION get_override_si_days
213 ( p_assignment_id IN NUMBER
214 ,p_date_earned IN DATE
215 ,p_business_group_id IN NUMBER
216 ,p_assignment_action_id IN NUMBER
217 ,p_payroll_action_id IN NUMBER
218 )
219 RETURN number ;
220
221 -----------------------------------------------------------------------------------------------
222 -- Function : get_tax_proration_days
223 -- To get the number of days for tax proration by executing user formula
224 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
225 -----------------------------------------------------------------------------------------------
226 Function get_tax_proration_days
227 ( p_assignment_id IN NUMBER
228 ,p_date_earned IN DATE
229 ,p_assignment_action_id IN NUMBER
230 ,p_payroll_action_id IN NUMBER
231 ,p_business_group_id IN NUMBER
232 )
233 RETURN number ;
234
235 -----------------------------------------------------------------------------------------------
236 -- Function : get_tax_proration_flag
237 -- To return the flag to determine whether proration is required or not by executing user formula
238 -- Assignment Id,Date Earned, Business Group Id and Assignment action Id.
239 -----------------------------------------------------------------------------------------------
240 Function get_tax_proration_flag
241 ( p_assignment_id IN NUMBER
242 ,p_date_earned IN DATE
243 ,p_assignment_action_id IN NUMBER
244 ,p_payroll_action_id IN NUMBER
245 ,p_business_group_id IN NUMBER
246 )
247 RETURN varchar2 ;
248
249 ---------------------------------------------------------------------------------------
250 -- Function : get_tax_proration_cal_days
251 -- To return number of Tax Days for proration based on calender days
252 ---------------------------------------------------------------------------------------
253
254 FUNCTION get_tax_proration_cal_days
255 ( p_assignment_id IN NUMBER
256 ,p_period_start_date IN DATE
257 ,p_period_end_date IN DATE
258 ,p_proration_start_date IN DATE
259 ,p_proration_end_date IN DATE
260 )
261 RETURN NUMBER;
262
263 END pay_nl_si_pkg;