1 package PAY_NL_GENERAL AUTHID CURRENT_USER as
2 /* $Header: pynlgenr.pkh 120.10.12020000.2 2012/11/29 14:04:52 mkuppuch ship $ */
3
4
5 -- +********************************************************************+
6 -- | PUBLIC FUNCTIONS |
7 -- +********************************************************************+
8
9 ------------------------------------------------------------------------
10 -- Function GET_POSTAL_CODE
11 ------------------------------------------------------------------------
12 function get_postal_code
13 (p_postal_code in varchar2)
14 return varchar2;
15
16 ------------------------------------------------------------------------
17 -- Function GET_POSTAL_CODE_NEW
18 ------------------------------------------------------------------------
19 function get_postal_code_new
20 (p_postal_code in varchar2)
21 return varchar2;
22
23
24 ------------------------------------------------------------------------
25 -- Function GET_MESSAGE
26 ------------------------------------------------------------------------
27 function get_message
28 (p_product in varchar2
29 ,p_message_name in varchar2
30 ,p_token1 in varchar2 default null
31 ,p_token2 in varchar2 default null
32 ,p_token3 in varchar2 default null)
33 return varchar2;
34
35 ---------------------------------------------------------------------------
36 -- Function: PAY_PERIOD_ASG_DATES
37 -- Description: Function returns pay period assignment dates
38 ---------------------------------------------------------------------------
39
40 function get_period_asg_dates (p_assignment_id in number
41 ,p_period_start_date in date
42 ,p_period_end_date in date
43 ,p_asg_start_date out nocopy date
44 ,p_asg_end_date out nocopy date
45 ) return number ;
46
47 ---------------------------------------------------------------------------
48 -- Function : get_run_result_value
49 -- Function returns the run result value given the assignment_action_id,
50 -- element_type_id, input_value_id,run_result_id
51 ---------------------------------------------------------------------------
52
53 function get_run_result_value(p_assignment_action_id number
54 ,p_element_type_id number
55 ,p_input_value_id number
56 ,p_run_result_id number
57 ,p_UOM varchar2)return varchar2;
58
59
60 ------------------------------------------------------------------
61 -- Function : get_run_result_value
62 -- This is a generic function that returns the run result value
63 -- given the assignment_action_id , element_Type_id,
64 -- input_value_id
65 ------------------------------------------------------------------
66
67 function get_run_result_value(p_assignment_action_id number,
68 p_element_type_id number,
69 p_input_value_id number)return number;
70
71
72
73 ---------------------------------------------------------------------------
74 -- Function : get_retro_period
75 -- Function returns the retro period for the given element_entry_id and
76 -- date_earned
77 ---------------------------------------------------------------------------
78
79 function get_retro_period
80 (
81 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
82 p_date_earned in pay_payroll_actions.date_earned%TYPE
83 ) return date;
84
85 ---------------------------------------------------------------------------
86 -- Function : get_defined_balance_id
87 -- Function returns the defined balance id
88 ---------------------------------------------------------------------------
89 FUNCTION get_defined_balance_id(p_user_name IN VARCHAR2) RETURN NUMBER;
90
91 ---------------------------------------------------------------------------
92 -- Function : get_iv_run_result
93 -- Function returns the input value run_result for the specified element
94 -- input value name
95 ---------------------------------------------------------------------------
96 FUNCTION get_iv_run_result(p_run_result_id IN NUMBER
97 ,p_element_type_id IN NUMBER
98 ,p_input_value_name IN VARCHAR2) RETURN VARCHAR2;
99 ---------------------------------------------------------------------------
100 -- Function : get_sit_type_name
101 -- Function returns the Si Type Name for specified Context Balance
102 ---------------------------------------------------------------------------
103 FUNCTION get_sit_type_name(p_balance_type_id pay_balance_types.balance_type_id%TYPE
104 ,p_assgn_action_id NUMBER
105 ,p_date_earned DATE
106 ,p_si_type VARCHAR2) RETURN VARCHAR2;
107
108
109
110
111 ---------------------------------------------------------------------------
112 -- Procedure : insert_leg_rule
113 -- Creates a Row in Pay_Legislation_Rules
114 ---------------------------------------------------------------------------
115 PROCEDURE insert_leg_rule(errbuf out nocopy varchar2, retcode out nocopy varchar2,p_retropay_method IN number) ;
116
117 ---------------------------------------------------------------------------
118 -- Function : get_default_retro_definition
119 -- Function returns the Default Retro Definition ID
120 ---------------------------------------------------------------------------
121 FUNCTION get_default_retro_definition(p_business_group_id IN number) RETURN number;
122
123 Function get_global_value(l_date_earned date,l_global_name varchar2) return varchar2;
124 Function get_global_value(l_date_earned date,l_payroll_action_id number,l_global_name varchar2) RETURN varchar2;
125
126
127
128 --
129 ------------------------- create_scl_flex_dict -------------------------
130 --
131 -- create the SCL key flexfield database items for a given id flex number.
132 --
133 procedure create_scl_flex_dict
134 (
135 p_id_flex_num in number
136 );
137 --
138
139 ---------------------------------------------------------------------------
140 -- Procedure : cache_formula
141 -- Procedure checks if a given Fast Formula at all exists and if so
142 -- then is it in a compiled state.
143 -- Four globals should be declared in the package where this procedure is
144 -- being called. The globals to be declared are as follows:
145 -- g_<function>_formula_exists BOOLEAN := TRUE
146 -- g_<function>_formula_cached BOOLEAN := FALSE
147 -- g_<function>_formula_id ff_formulas_f.formula_id%TYPE
148 -- g_<function>_formula_name ff_formulas_f.formula_name%TYPE
149 -- The usage of this function with the above declared globals can be
150 -- understood from the function get_part_time_perc in the package pay_nl_si_pkg
151 ---------------------------------------------------------------------------
152 PROCEDURE cache_formula(p_formula_name IN VARCHAR2
153 ,p_business_group_id IN NUMBER
154 ,p_effective_date IN DATE
155 ,p_formula_id IN OUT NOCOPY NUMBER
156 ,p_formula_exists IN OUT NOCOPY BOOLEAN
157 ,p_formula_cached IN OUT NOCOPY BOOLEAN
158 );
159
160 ---------------------------------------------------------------------------
161 -- Procedure : run_formula
162 -- Procedure runs a fast formula and returns the result in the p_output
163 -- parameter.
164 ---------------------------------------------------------------------------
165
166 PROCEDURE run_formula(p_formula_id IN NUMBER
167 ,p_effective_date IN DATE
168 ,p_formula_name IN VARCHAR2
169 ,p_inputs IN ff_exec.inputs_t
170 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t);
171
172
173
174 FUNCTION get_element_type_id(p_element_name VARCHAR2,
175 p_effective_date DATE)
176 RETURN number;
177
178 FUNCTION get_input_value_id(p_element_type_id NUMBER,
179 p_input_value_name VARCHAR2,
180 p_effective_date DATE)
181 RETURN number;
182
183 ---------------------------------------------------------------------------
184 -- Function : get_employee_address
185 -- Function returns the Employee Address with no spaces in the postal code
186 ---------------------------------------------------------------------------
187 FUNCTION get_employee_address(p_person_id IN NUMBER
188 ,p_effective_date IN DATE
189 ,p_house_number IN OUT NOCOPY VARCHAR2
190 ,p_house_no_add IN OUT NOCOPY VARCHAR2
191 ,p_street_name IN OUT NOCOPY VARCHAR2
192 ,p_line1 IN OUT NOCOPY VARCHAR2
193 ,p_line2 IN OUT NOCOPY VARCHAR2
194 ,p_line3 IN OUT NOCOPY VARCHAR2
195 ,p_city IN OUT NOCOPY VARCHAR2
196 ,p_country IN OUT NOCOPY VARCHAR2
197 ,p_postal_code IN OUT NOCOPY VARCHAR2
198 ,p_address_type IN VARCHAR2 DEFAULT NULL
199 )
200 RETURN NUMBER;
201 ---------------------------------------------------------------------------
202 -- Function : get_emp_address
203 -- Function returns the Employee Address with postal code in the correct format
204 ---------------------------------------------------------------------------
205 FUNCTION get_emp_address(p_person_id IN NUMBER
206 ,p_effective_date IN DATE
207 ,p_house_number IN OUT NOCOPY VARCHAR2
208 ,p_house_no_add IN OUT NOCOPY VARCHAR2
209 ,p_street_name IN OUT NOCOPY VARCHAR2
210 ,p_line1 IN OUT NOCOPY VARCHAR2
211 ,p_line2 IN OUT NOCOPY VARCHAR2
212 ,p_line3 IN OUT NOCOPY VARCHAR2
213 ,p_city IN OUT NOCOPY VARCHAR2
214 ,p_country IN OUT NOCOPY VARCHAR2
215 ,p_postal_code IN OUT NOCOPY VARCHAR2
216 ,p_address_type IN VARCHAR2 DEFAULT NULL
217 )
218 RETURN NUMBER;
219 ---------------------------------------------------------------------------
220 -- Function : get_organization_address
221 -- Function returns the Organization Address with no spaces in the postal code
222 ---------------------------------------------------------------------------
223 FUNCTION get_organization_address(p_org_id IN NUMBER
224 ,p_bg_id IN NUMBER
225 ,p_house_number IN OUT NOCOPY VARCHAR2
226 ,p_house_no_add IN OUT NOCOPY VARCHAR2
227 ,p_street_name IN OUT NOCOPY VARCHAR2
228 ,p_line1 IN OUT NOCOPY VARCHAR2
229 ,p_line2 IN OUT NOCOPY VARCHAR2
230 ,p_line3 IN OUT NOCOPY VARCHAR2
231 ,p_city IN OUT NOCOPY VARCHAR2
232 ,p_country IN OUT NOCOPY VARCHAR2
233 ,p_postal_code IN OUT NOCOPY VARCHAR2
234 )
235 RETURN NUMBER;
236 ---------------------------------------------------------------------------
237 -- Function : get_org_address
238 -- Function returns the Organization Address with postal code in the correct format
239 ---------------------------------------------------------------------------
240 FUNCTION get_org_address(p_org_id IN NUMBER
241 ,p_bg_id IN NUMBER
242 ,p_house_number IN OUT NOCOPY VARCHAR2
243 ,p_house_no_add IN OUT NOCOPY VARCHAR2
244 ,p_street_name IN OUT NOCOPY VARCHAR2
245 ,p_line1 IN OUT NOCOPY VARCHAR2
246 ,p_line2 IN OUT NOCOPY VARCHAR2
247 ,p_line3 IN OUT NOCOPY VARCHAR2
248 ,p_city IN OUT NOCOPY VARCHAR2
249 ,p_country IN OUT NOCOPY VARCHAR2
250 ,p_postal_code IN OUT NOCOPY VARCHAR2
251 )
252 RETURN NUMBER;
253
254 ---------------------------------------------------------------------------
255 -- Function : get_country_name
256 -- Function returns the Contry name withe country_code as input parameter
257 ---------------------------------------------------------------------------
258 FUNCTION get_country_name(p_territory_code IN VARCHAR2)
259 RETURN VARCHAR2;
260
261 -------------------------------------------------------------------------------
262 -- Function : get_retro_sum_sec_class
263 -- Function returns the sum of retrospective values for a sub classification
264 -- for a period.
265 -------------------------------------------------------------------------------
266 FUNCTION get_retro_sum_sec_class(p_retro_period IN DATE,
267 p_sec_class_name IN VARCHAR2,
268 p_assact_id IN NUMBER)
269 RETURN NUMBER;
270 -------------------------------------------------------------------------------
271 -- Function : get_retro_sum_pri_class
272 -- Function returns the sum of retrospective values for a sub classification
273 -- for a period.
274 -------------------------------------------------------------------------------
275 FUNCTION get_retro_sum_pri_class(p_retro_period IN DATE,
276 p_pri_class_name IN VARCHAR2,
277 p_assact_id IN NUMBER)
278 RETURN NUMBER;
279 -------------------------------------------------------------------------------
280 -- Function : get_retro_sum_element
281 -- Function returns the sum of retrospective values values for an element
282 -------------------------------------------------------------------------------
283 FUNCTION get_retro_sum_element(p_retro_period IN DATE,
284 p_input_value_id IN NUMBER,
285 p_element_type_id IN NUMBER,
286 p_context IN VARCHAR2,
287 p_end_of_year IN VARCHAR2,
288 p_assact_id IN NUMBER)
289 RETURN NUMBER;
290 ------------------------------------------------------------------------------
291 --Function :get_sum_element_pri_class
292 --Function returns the sum of non retrospective values for an element
293 --Classification
294 -----------------------------------------------------------------------------
295 FUNCTION get_sum_element_pri_class(p_effective_date IN DATE,
296 p_pri_class_name IN VARCHAR2,
297 p_assact_id IN NUMBER)
298 RETURN NUMBER;
299 -----------------------------------------------------------------------------
300 --Function :format_number_field
301 --Function returns formatted string for a number with decimal
302 -----------------------------------------------------------------------------
303 function format_number_field(p_number number,
304 p_mpy_factor number,
305 p_field_length number)
306 return varchar2;
307 -----------------------------------------------------------------------------
308 -----------------------------------------------------------------------------
309 -- Function :get_file_id
310 -- Function returns file id on the effective date
311 -------------------------------------------------------------------------------
312 FUNCTION get_file_id(p_effective_date DATE) RETURN VARCHAR2;
313 -----------------------------------------------------------------------------
314 -- Function :get_parameter
315 -- Function returns the parameter to be passed
316 -------------------------------------------------------------------------------
317 FUNCTION get_parameter (
318 p_parameter_string in varchar2
319 ,p_token in varchar2
320 ,p_segment_number in number default null) RETURN varchar2;
321
322 -----------------------------------------------------------------------------
323 -- Function :chk_multiple_assignments
324 -- Function to determine the existance of multiple assignments for an employee
325 -------------------------------------------------------------------------------
326 FUNCTION chk_multiple_assignments(p_effective_date IN DATE
327 ,p_person_id IN NUMBER) RETURN VARCHAR2;
328
329 ------------------------------------------------------------------------------
330 --Function :get_sum_element_sec_class
331 --Function returns the sum of non retrospective values for an element
332 --with the given Secondary Classification
333 -----------------------------------------------------------------------------
334 FUNCTION get_sum_element_sec_class(p_effective_date IN DATE,
335 p_sec_class_name IN VARCHAR2,
336 p_assact_id IN NUMBER)
337 RETURN NUMBER;
338
339 -----------------------------------------------------------------------------
340 -- Function :get_retro_status
341 -- Function to determine whether replacement retropay method is running
342 -------------------------------------------------------------------------------
343 FUNCTION get_retro_status(p_date_earned date,p_payroll_action_id number)
344 RETURN VARCHAR2;
345
346 -----------------------------------------------------------------------------
347 -- Function :get_num_payroll_periods
348 -- Function to get number of payroll periods in a year
349 -------------------------------------------------------------------------------
350 FUNCTION get_num_payroll_periods(p_payroll_action_id IN NUMBER)
351 RETURN NUMBER;
352 -----------------------------------------------------------------------------
353 -- Function :check_de_dp_dimension
354 -- Function to check whether Date paid or balance date dimenions to be used.
355 -------------------------------------------------------------------------------
356 FUNCTION check_de_dp_dimension(p_pay_act_id NUMBER
357 ,p_ass_id NUMBER
358 ,p_ass_act_id NUMBER) RETURN VARCHAR2;
359
360 -----------------------------------------------------------------------------
361 -- Function :check_de_dp_dimension_old
362 -------------------------------------------------------------------------------
363 FUNCTION check_de_dp_dimension_old(p_pay_act_id NUMBER
364 ,p_ass_id NUMBER
365 ,p_ass_act_id NUMBER) RETURN VARCHAR2;
366
367 -----------------------------------------------------------------------------
368 -- Function :check_de_dp_dimension
369 -- Function to check whether Date paid or balance date dimenions to be used.
370 -------------------------------------------------------------------------------
371 FUNCTION check_de_dp_dimension_qtd(p_pay_act_id NUMBER
372 ,p_ass_id NUMBER
373 ,p_ass_act_id NUMBER
374 ,p_type VARCHAR2) RETURN VARCHAR2;
375 --
376 -----------------------------------------------------------------------------
377 -- Globals to be used in CHECK_DE_DP_DIMENSION.
378 -------------------------------------------------------------------------------
379 g_result VARCHAR2(5);
380 g_qtd_result VARCHAR2(5);
381 g_period_type VARCHAR2(30);
382 g_parent_id NUMBER;
383 g_payroll_action_id NUMBER;
384 g_assignment_id NUMBER;
385 g_Late_Hire_Indicator VARCHAR2(10);
386 --
387 --9453856
388 -----------------------------------------------------------------------------
389 -- New procedure to modify the Adjustment mode to Correction for ABP, PGGM.
390 -------------------------------------------------------------------------------
391 PROCEDURE NL_ADJUST_TO_CORRECTION ( errbuf OUT NOCOPY VARCHAR2
392 , retcode OUT NOCOPY NUMBER
393 , p_bg_id IN NUMBER
394 --, p_reprocess_date IN VARCHAR2 --9453856
395 --, p_payroll_id IN NUMBER --9453856
396 , p_adj_to_corr IN VARCHAR2
397 );
398 --9453856
399
400 --13350181
401 FUNCTION get_run_type(p_payroll_action_id IN NUMBER
402 ,p_assignment_action_id IN NUMBER) RETURN VARCHAR2;
403
404 END PAY_NL_GENERAL;
[Home] [Help]