1 package pay_kr_sep_form_pkg as
2 /* $Header: pykrsepf.pkh 120.1 2006/09/29 11:54:04 vaisriva noship $ */
3 --
4 -- Exception Handlers
5 --
6 zero_req_id exception;
7 pragma exception_init(zero_req_id, -9999);
8 --------------------------------------------------------------------------------
9 function get_run_type_name(p_run_type_id in number,
10 p_effective_date in date) return varchar2;
11 --------------------------------------------------------------------------------
12 function get_kr_d_address_line1(p_address_line1 in varchar2) return varchar2;
13 --------------------------------------------------------------------------------
14 procedure process_run(p_payroll_id in number,
15 p_consolidation_set_id in number,
16 p_earned_date in varchar2,
17 p_date_paid in varchar2,
18 p_ele_set_id in number,
19 p_assignment_set_id in number,
20 p_run_type_id in number,
21 p_leg_params in varchar2,
22 p_payout_date in varchar2,
23 p_req_id in out NOCOPY number,
24 p_success out NOCOPY boolean,
25 errbuf out NOCOPY varchar2);
26 --------------------------------------------------------------------------------
27 procedure archive_run(p_business_group_id in number,
28 p_start_date in varchar2,
29 p_effective_date in varchar2,
30 p_payroll_id in number,
31 p_payroll_id_hd in varchar2,
32 p_req_id in out NOCOPY number,
33 p_success out NOCOPY boolean,
34 errbuf out NOCOPY varchar2);
35 --------------------------------------------------------------------------------
36 procedure delete_action(p_source_action_id in number,
37 p_dml_mode in varchar2); /* NO_COMMIT, NONE, FULL */
38 --------------------------------------------------------------------------------
39 procedure lock_action(p_source_action_id in number);
40 --------------------------------------------------------------------------------
41 procedure find_dt_upd_modes(
42 p_effective_date in date,
43 p_base_key_value in number,
44 p_correction out NOCOPY boolean,
45 p_update out NOCOPY boolean,
46 p_update_override out NOCOPY boolean,
47 p_update_change_insert out NOCOPY boolean);
48 --------------------------------------------------------------------------------
49 procedure find_dt_del_modes(
50 p_effective_date in date,
51 p_base_key_value in number,
52 p_zap out NOCOPY boolean,
53 p_delete out NOCOPY boolean,
54 p_future_change out NOCOPY boolean,
55 p_delete_next_change out NOCOPY boolean);
56 --------------------------------------------------------------------------------
57 procedure lock_element_entry(
58 p_effective_date in date,
59 p_datetrack_mode in varchar2,
60 p_element_entry_id in number,
61 p_object_version_number in number,
62 p_validation_start_date out NOCOPY date,
63 p_validation_end_date out NOCOPY date);
64 --------------------------------------------------------------------------------
65 procedure insert_element_entry(
66 p_validate in boolean default false,
67 p_assignment_id in number,
68 p_business_group_id in number,
69 p_effective_date in date,
70 p_element_link_id in number,
71 p_input_value_id1 in number default null,
72 p_input_value_id2 in number default null,
73 p_input_value_id3 in number default null,
74 p_input_value_id4 in number default null,
75 p_input_value_id5 in number default null,
76 p_input_value_id6 in number default null,
77 p_input_value_id7 in number default null,
78 p_input_value_id8 in number default null,
79 p_input_value_id9 in number default null,
80 p_input_value_id10 in number default null,
81 p_input_value_id11 in number default null,
82 p_input_value_id12 in number default null,
83 p_input_value_id13 in number default null,
84 p_input_value_id14 in number default null,
85 p_input_value_id15 in number default null,
86 p_entry_value1 in varchar2 default null,
87 p_entry_value2 in varchar2 default null,
88 p_entry_value3 in varchar2 default null,
89 p_entry_value4 in varchar2 default null,
90 p_entry_value5 in varchar2 default null,
91 p_entry_value6 in varchar2 default null,
92 p_entry_value7 in varchar2 default null,
93 p_entry_value8 in varchar2 default null,
94 p_entry_value9 in varchar2 default null,
95 p_entry_value10 in varchar2 default null,
96 p_entry_value11 in varchar2 default null,
97 p_entry_value12 in varchar2 default null,
98 p_entry_value13 in varchar2 default null,
99 p_entry_value14 in varchar2 default null,
100 p_entry_value15 in varchar2 default null,
101 p_element_entry_id out NOCOPY number,
102 p_effective_start_date out NOCOPY date,
103 p_effective_end_date out NOCOPY date,
104 p_object_version_number out NOCOPY number);
105 --------------------------------------------------------------------------------
106 procedure update_element_entry(
107 p_validate in boolean default false,
108 p_dt_update_mode in varchar2, /* UPDATE,UPDATE_CHANGE_INSERT,UPDATE_OVERRIDE,CORRECTION */
109 p_effective_date in date,
110 p_business_group_id in number,
111 p_element_entry_id in number,
112 p_object_version_number in out NOCOPY number,
113 p_input_value_id1 in number default null,
114 p_input_value_id2 in number default null,
115 p_input_value_id3 in number default null,
116 p_input_value_id4 in number default null,
117 p_input_value_id5 in number default null,
118 p_input_value_id6 in number default null,
119 p_input_value_id7 in number default null,
120 p_input_value_id8 in number default null,
121 p_input_value_id9 in number default null,
122 p_input_value_id10 in number default null,
123 p_input_value_id11 in number default null,
124 p_input_value_id12 in number default null,
125 p_input_value_id13 in number default null,
126 p_input_value_id14 in number default null,
127 p_input_value_id15 in number default null,
128 p_entry_value1 in varchar2 default null,
129 p_entry_value2 in varchar2 default null,
130 p_entry_value3 in varchar2 default null,
131 p_entry_value4 in varchar2 default null,
132 p_entry_value5 in varchar2 default null,
133 p_entry_value6 in varchar2 default null,
134 p_entry_value7 in varchar2 default null,
135 p_entry_value8 in varchar2 default null,
136 p_entry_value9 in varchar2 default null,
137 p_entry_value10 in varchar2 default null,
138 p_entry_value11 in varchar2 default null,
139 p_entry_value12 in varchar2 default null,
140 p_entry_value13 in varchar2 default null,
141 p_entry_value14 in varchar2 default null,
142 p_entry_value15 in varchar2 default null,
143 p_effective_start_date out NOCOPY date,
144 p_effective_end_date out NOCOPY date);
145 ------------------------------------------------------------------------------
146 procedure delete_element_entry(
147 p_validate in boolean default false,
148 p_dt_delete_mode in varchar2, /* DELETE,ZAP,DELETE_NEXT_CHANGE,FUTURE_CHANGE */
149 p_effective_date in date,
150 p_element_entry_id in number,
151 p_object_version_number in out NOCOPY number,
152 p_effective_start_date out NOCOPY date,
153 p_effective_end_date out NOCOPY date);
154 --------------------------------------------------------------------------------
155 procedure chk_entry(
156 p_element_entry_id in number,
157 p_assignment_id in number,
158 p_element_link_id in number,
159 p_entry_type in varchar2,
160 p_original_entry_id in number default null,
161 p_target_entry_id in number default null,
162 p_effective_date in date,
163 p_validation_start_date in date,
164 p_validation_end_date in date,
165 p_effective_start_date in out NOCOPY date,
166 p_effective_end_date in out NOCOPY date,
167 p_usage in varchar2,
168 p_dt_update_mode in varchar2,
169 p_dt_delete_mode in varchar2);
170 --------------------------------------------------------------------------------
171 procedure chk_formula(
172 p_formula_id in number,
173 p_entry_value in varchar2,
174 p_business_group_id in number,
175 p_assignment_id in number,
176 p_date_earned in date,
177 p_formula_status out NOCOPY varchar2,
178 p_formula_message out NOCOPY varchar2);
179 --------------------------------------------------------------------------------
180 procedure validate_entry_value(
181 p_element_link_id in number,
182 p_input_value_id in number,
183 p_effective_date in date,
184 p_business_group_id in number,
185 p_assignment_id in number,
186 p_user_value in out NOCOPY varchar2,
187 p_canonical_value out NOCOPY varchar2,
188 p_hot_defaulted out NOCOPY boolean,
189 p_min_max_warning out NOCOPY boolean,
190 p_user_min_value out NOCOPY varchar2,
191 p_user_max_value out NOCOPY varchar2,
192 p_formula_warning out NOCOPY boolean,
193 p_formula_message out NOCOPY varchar2);
194 --------------------------------------------------------------------------------
195 function get_session_date return date;
196 --------------------------------------------------------------------------------
197 function get_element_type_id(
198 p_element_name in varchar2,
199 p_business_group_id in number,
200 p_effective_date in date) return number;
201 --------------------------------------------------------------------------------
202 function get_input_value_id(
203 p_element_type_id in number,
204 p_sequence in number,
205 p_business_group_id in number,
206 p_effective_date in date default null) return number;
207 --------------------------------------------------------------------------------
208 function get_input_value_name(
209 p_element_type_id in number,
210 p_sequence in number,
211 p_business_group_id in number,
212 p_effective_date in date default null) return varchar2;
213 --------------------------------------------------------------------------------
214 function get_input_value_d_sequence(
215 p_element_type_id in number,
216 p_sequence in number,
217 p_business_group_id in number,
218 p_effective_date in date default null) return number;
219 --------------------------------------------------------------------------------
220 function get_input_value_lookup_type(
221 p_element_type_id in number,
222 p_sequence in number,
223 p_business_group_id in number,
224 p_effective_date in date default null) return varchar2;
225 --------------------------------------------------------------------------------
226 function get_input_value_mandatory(
227 p_element_type_id in number,
228 p_sequence in number,
229 p_business_group_id in number,
230 p_effective_date in date default null) return varchar2;
231 --------------------------------------------------------------------------------
232 procedure get_default_value(
233 p_assignment_id in number,
234 p_element_type_id in number,
235 p_business_group_id in varchar2,
236 p_entry_type in varchar2 default 'E',
237 p_effective_date in date,
238 p_element_link_id out NOCOPY number,
239 p_input_value_id1 out NOCOPY number,
240 p_input_value_id2 out NOCOPY number,
241 p_input_value_id3 out NOCOPY number,
242 p_input_value_id4 out NOCOPY number,
243 p_input_value_id5 out NOCOPY number,
244 p_input_value_id6 out NOCOPY number,
245 p_input_value_id7 out NOCOPY number,
246 p_input_value_id8 out NOCOPY number,
247 p_input_value_id9 out NOCOPY number,
248 p_input_value_id10 out NOCOPY number,
249 p_input_value_id11 out NOCOPY number,
250 p_input_value_id12 out NOCOPY number,
251 p_input_value_id13 out NOCOPY number,
252 p_input_value_id14 out NOCOPY number,
253 p_input_value_id15 out NOCOPY number,
254 p_default_value1 out NOCOPY varchar2,
255 p_default_value2 out NOCOPY varchar2,
256 p_default_value3 out NOCOPY varchar2,
257 p_default_value4 out NOCOPY varchar2,
258 p_default_value5 out NOCOPY varchar2,
259 p_default_value6 out NOCOPY varchar2,
260 p_default_value7 out NOCOPY varchar2,
261 p_default_value8 out NOCOPY varchar2,
262 p_default_value9 out NOCOPY varchar2,
263 p_default_value10 out NOCOPY varchar2,
264 p_default_value11 out NOCOPY varchar2,
265 p_default_value12 out NOCOPY varchar2,
266 p_default_value13 out NOCOPY varchar2,
267 p_default_value14 out NOCOPY varchar2,
268 p_default_value15 out NOCOPY varchar2,
269 p_b_default_value1 out NOCOPY varchar2,
270 p_b_default_value2 out NOCOPY varchar2,
271 p_b_default_value3 out NOCOPY varchar2,
272 p_b_default_value4 out NOCOPY varchar2,
273 p_b_default_value5 out NOCOPY varchar2,
274 p_b_default_value6 out NOCOPY varchar2,
275 p_b_default_value7 out NOCOPY varchar2,
276 p_b_default_value8 out NOCOPY varchar2,
277 p_b_default_value9 out NOCOPY varchar2,
278 p_b_default_value10 out NOCOPY varchar2,
279 p_b_default_value11 out NOCOPY varchar2,
280 p_b_default_value12 out NOCOPY varchar2,
281 p_b_default_value13 out NOCOPY varchar2,
282 p_b_default_value14 out NOCOPY varchar2,
283 p_b_default_value15 out NOCOPY varchar2,
284 p_effective_start_date in out NOCOPY date,
285 p_effective_end_date in out NOCOPY date);
286 --------------------------------------------------------------------------------
287 function get_screen_entry_value(
288 p_element_type_id in number,
289 p_sequence in number,
290 p_business_group_id in number,
291 p_ee_element_entry_id in number,
292 p_ee_effective_start_date in date,
293 p_ee_effective_end_date in date) return varchar2;
294 --------------------------------------------------------------------------------
295 function get_entry_value(
299 p_ee_element_entry_id in number,
296 p_element_type_id in number,
297 p_sequence in number,
298 p_business_group_id in number,
300 p_ee_effective_start_date in date,
301 p_ee_effective_end_date in date,
302 p_el_element_link_id in number) return varchar2;
303 -------------------------------------------------------------------------------------------------------------+
304 --
305 -- Bug# 2425705
306 -- Added function get_employee_status,procedures create_entries and create_entry_for_assignment
307 -- to enhance the function of PAYKRSEP.fmb.
308 -- Added a pl/sql table to hold assignment_id's.
309 -------------------------------------------------------------------------------------------------------------+
310 type assignment_id_tbl is table of pay_assignment_actions.assignment_id%type index by binary_integer;
311 g_assignment_id_tbl assignment_id_tbl;
312 -------------------------------------------------------------------------------------------------------------+
313 procedure create_entries(
314 p_assignment_id_tbl in g_assignment_id_tbl%type,
315 p_element_set_id in pay_element_type_rules.element_set_id%type,
316 p_run_type_id in pay_run_types.run_type_id%type,
317 p_business_group_id in hr_assignment_sets.business_group_id%type,
318 p_session_date in date);
319 -------------------------------------------------------------------------------------------------------------+
320 procedure create_entries(
321 p_assignment_set_id in hr_assignment_sets.assignment_set_id%type,
322 p_element_set_id in pay_element_type_rules.element_set_id%type,
323 p_run_type_id in pay_run_types.run_type_id%type,
324 p_business_group_id in hr_assignment_sets.business_group_id%type,
325 p_payroll_id in hr_assignment_sets.payroll_id%type,
326 p_session_date in date);
327 -------------------------------------------------------------------------------------------------------------+
328 procedure create_entry_for_assignment(
329 p_assignment_id in pay_assignment_actions.assignment_id%type,
330 p_element_type_id in pay_element_types.element_type_id%type,
331 p_business_group_id in pay_element_types.business_group_id%type,
332 p_entry_type in pay_element_entries_f.entry_type%type,
333 p_effective_date in date,
334 p_effective_start_date in out NOCOPY date,
335 p_effective_end_date in out NOCOPY date,
336 p_element_entry_id out NOCOPY pay_element_entries_f.element_entry_id%type,
337 p_object_version_number out NOCOPY number);
338 -------------------------------------------------------------------------------------------------------------+
339 function get_employee_status(
340 p_assignment_id in pay_assignment_actions.assignment_id%type,
341 p_run_type_name in pay_run_types.run_type_name%type,
342 p_date_earned in date) return varchar2;
343 -------------------------------------------------------------------------------------------------------------+
344 -- Bug# 2425705
345 -------------------------------------------------------------------------------------------------------------+
346 end pay_kr_sep_form_pkg;