1 package pqh_gsp_hr_to_stage AUTHID CURRENT_USER as
2 /* $Header: pqgsphrs.pkh 120.1.12010000.1 2008/07/28 12:57:43 appldev ship $ */
3 g_master_txn_id number;
4 g_txn_id number;
5 procedure update_crset_type(p_copy_entity_txn_id in number,
6 p_crset_id in number,
7 p_crset_type in varchar2);
8 function get_grd_start_date(p_grade_cer_id in number) return date;
9 function get_abr_cer(p_copy_entity_txn_id in number,
10 p_pl_cer_id in number default null,
11 p_opt_cer_id in number default null,
12 p_effective_date in date) return number;
13 procedure update_crset(p_crset_id in number,
14 p_effective_date in date,
15 p_crset_name in varchar2,
16 p_copy_entity_txn_id in number,
17 p_datetrack_mode in varchar2,
18 p_bu_cd in varchar2 default null,
19 p_bu_name in varchar2 default null,
20 p_fp_cd in varchar2 default null,
21 p_fp_name in varchar2 default null,
22 p_job_id in number default null,
23 p_job_name in varchar2 default null,
24 p_org_id in number default null,
25 p_org_name in varchar2 default null,
26 p_pt_id in number default null,
27 p_pt_name in varchar2 default null,
28 p_loc_id in number default null,
29 p_loc_name in varchar2 default null,
30 p_perf_rtng_cd in varchar2 default null,
31 p_perf_rtng_name in varchar2 default null,
32 p_event_type in varchar2 default null,
33 p_event_name in varchar2 default null,
34 p_sa_id in number default null,
35 p_sa_name in varchar2 default null,
36 p_ff_id in number default null,
37 p_ff_name in varchar2 default null);
38 procedure create_crset_row(p_crset_id out nocopy number,
39 p_effective_date in date,
40 p_copy_entity_txn_id in number,
41 p_bu_cd in varchar2 default null,
42 p_bu_name in varchar2 default null,
43 p_fp_cd in varchar2 default null,
44 p_fp_name in varchar2 default null,
45 p_job_id in number default null,
46 p_job_name in varchar2 default null,
47 p_org_id in number default null,
48 p_org_name in varchar2 default null,
49 p_pt_id in number default null,
50 p_pt_name in varchar2 default null,
51 p_loc_id in number default null,
52 p_loc_name in varchar2 default null,
53 p_perf_rtng_cd in varchar2 default null,
54 p_event_type in varchar2 default null,
55 p_perf_rtng_name in varchar2 default null,
56 p_event_name in varchar2 default null,
57 p_sa_id in number default null,
58 p_sa_name in varchar2 default null,
59 p_ff_id in number default null,
60 p_ff_name in varchar2 default null,
61 p_validate in varchar2 default 'TRUE',
62 p_crset_type in varchar2,
63 p_name in varchar2);
64 procedure pull_elp_for_crset(p_elp_id in number,
65 p_copy_entity_txn_id in number,
66 p_crset_type in varchar2,
67 p_effective_date in date,
68 p_business_group_id in number,
69 p_crset_id out nocopy number,
70 p_dup_crset out nocopy varchar2);
71 function is_crrate_there(p_oipl_cer_id in number default null,
72 p_plip_cer_id in number default null,
73 p_pl_cer_id in number default null,
74 p_point_cer_id in number default null,
75 p_copy_entity_txn_id in number,
76 p_effective_date in date) return varchar2;
77 procedure create_crrate_row(p_vpf_cer_id in number default null,
78 p_abr_cer_id in number ,
79 p_vpf_id in number default null,
80 p_vpf_name in varchar2 default null,
81 p_vpf_ovn in number default null,
82 p_grade_cer_id in number default null,
83 p_point_cer_id in number default null,
84 p_copy_entity_txn_id in number,
85 p_business_group_id in number,
86 p_effective_date in date,
87 p_vpf_esd in date,
88 p_vpf_eed in date,
89 p_vpf_value in number,
90 p_crset_id in number,
91 p_dml_operation in varchar2 default 'INSERT',
92 p_datetrack_mode in varchar2 default 'INSERT',
93 p_elp_id in number default null,
94 p_crr_cer_id out nocopy number);
95 procedure create_crrate_row(p_grade_cer_id in number default null,
96 p_point_cer_id in number default null,
97 p_copy_entity_txn_id in number,
98 p_business_group_id in number,
99 p_effective_date in date,
100 p_vpf_value in number,
101 p_crset_id in number,
102 p_crr_cer_id out nocopy number);
103 procedure update_txn_table_route(p_copy_entity_txn_id in number);
104 procedure get_table_route_details(p_table_alias in varchar2,
105 p_table_route_id out nocopy number ,
106 p_table_name out nocopy varchar2 );
107 Procedure grade_to_pl_stage(p_grade_id in number,
108 p_pl_cer_id in number,
109 p_effective_date in date);
110 Procedure scale_to_stage(p_scale_id in number,
111 p_business_group_id in number,
112 p_copy_entity_txn_id in number,
113 p_effective_date in date,
114 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
115 p_scale_cer_id out nocopy number);
116 procedure get_point_rate_values(p_effective_date in date,
117 p_opt_id in number,
118 p_point_id in number,
119 p_point_value out nocopy number);
120 procedure step_to_oipl_stage(p_copy_entity_txn_id in number,
121 p_oipl_id in number,
122 p_step_id in number,
123 p_oipl_cer_id in number,
124 p_parent_cer_id in number,
125 p_effective_date in date,
126 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST');
127 procedure populate_pgm_hierarchy(p_copy_entity_txn_id in number,
128 p_effective_date in date,
129 p_business_group_id in number,
130 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
131 p_pgm_id in number);
132 procedure hrate_to_stage(p_abr_cer_id in number,
133 p_copy_entity_txn_id in number,
134 p_effective_date in date,
135 p_abr_id in number,
136 p_parent_cer_id in number);
137 procedure point_to_opt_stage(p_copy_entity_txn_id in number,
138 p_option_id in number,
139 p_opt_cer_id in number,
140 p_effective_date in date,
141 p_business_group_id in number,
142 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST');
143 Procedure grade_to_plip_stage(p_grade_id in number,
144 p_plip_cer_id in number,
145 p_pl_cer_id in number,
146 p_parent_cer_id in number,
147 p_mirror_ser_id in number,
148 p_effective_date in date,
149 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST');
150 procedure create_gsp_control_rec(p_copy_entity_txn_id in number,
151 p_effective_date in date,
152 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST');
153 procedure create_gsp_control_rec(p_copy_entity_txn_id in number,
154 p_effective_date in date,
155 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
156 p_gl_exists in varchar2,
157 p_sal_exists in varchar2,
158 p_grd_exists in varchar2,
159 p_step_exists in varchar2,
160 p_rate_exists in varchar2,
161 p_rule_exists in varchar2);
162 procedure hr_to_stage(p_copy_entity_txn_id in number,
163 p_start_cer_id in number default null,
164 p_effective_date in date,
165 p_business_group_id in number,
166 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST');
167 procedure start_gsp_txn(p_copy_entity_txn_id out nocopy number
168 ,p_business_group_id in number
169 ,p_name in varchar2
170 ,p_effective_date in date
171 ,p_status in varchar2
172 ,p_business_area in varchar2 default 'PQH_GSP_TASK_LIST'
173 ,p_object_version_number out nocopy number) ;
174 procedure update_gsp_control_rec(p_copy_entity_txn_id in number,
175 p_effective_date in date,
176 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST');
177 procedure update_GL(p_pgm_id in number,
178 p_action_type in varchar2 default 'REFRESH',
179 p_pgm_name in varchar2,
180 p_effective_date in date,
181 p_business_group_id in number,
182 p_user_id in number,
183 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
184 p_copy_entity_txn_id out nocopy number );
185 procedure update_or_view_GL(p_calling_mode in varchar2,
186 p_action_type in varchar2 default 'REFRESH',
187 p_pgm_id in number,
188 p_pgm_name in varchar2,
189 p_effective_date in date,
190 p_business_group_id in number,
191 p_user_id in number,
192 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
193 p_copy_entity_txn_id out nocopy number);
194 procedure get_step_all_details(p_point_id in number,
195 p_grade_id in number,
196 p_option_id in number,
197 p_effective_date in date,
198 p_point_name out nocopy varchar2,
199 p_step_name out nocopy varchar2,
200 p_step_id out nocopy number,
201 p_step_ovn out nocopy number,
202 p_grade_spine_id out nocopy number,
203 p_ceiling_flag out nocopy varchar2,
204 p_point_value out nocopy number,
205 p_scale_id out nocopy number);
206 procedure populate_pl_hierarchy(p_copy_entity_txn_id in number,
207 p_effective_date in date,
208 p_business_group_id in number,
209 p_plip_cer_id in number,
210 p_pl_id in number,
211 p_mode in varchar2 default 'COMPLETE',
212 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
213 p_pl_cer_id out nocopy number);
214 procedure populate_ep_hierarchy(p_copy_entity_txn_id in number,
215 p_effective_date in date,
216 p_business_group_id in number,
217 p_ep_id in number,
218 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
219 p_ep_cer_id out nocopy number);
220 procedure populate_opt_hierarchy(p_copy_entity_txn_id in number,
221 p_effective_date in date,
222 p_business_group_id in number,
223 p_opt_id in number,
224 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
225 p_opt_cer_id out nocopy number);
226 procedure populate_scale_hierarchy(p_copy_entity_txn_id in number,
227 p_effective_date in date,
228 p_business_group_id in number,
229 p_scale_id in number,
230 p_mode in varchar2 default 'COMPLETE',
231 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
232 p_scale_cer_id out nocopy number);
233 procedure get_grade_scale_details(p_grade_id in number,
234 p_scale_id in number,
235 p_effective_date in date,
236 p_ceiling_step_id out nocopy number,
237 p_grade_spine_id out nocopy number,
238 p_starting_step out nocopy number);
239 procedure get_grd_scale_details(p_grade_id in number,
240 p_effective_date in date,
241 p_scale_id out nocopy number,
242 p_ceiling_step_id out nocopy number,
243 p_grade_spine_ovn out nocopy number,
244 p_grade_spine_id out nocopy number,
245 p_scale_ovn out nocopy number,
246 p_scale_name out nocopy varchar2,
247 p_starting_step out nocopy number);
248 procedure vpf_to_stage(p_vpf_cer_id in number,
249 p_copy_entity_txn_id in number,
250 p_effective_date in date,
251 p_result_type_cd in varchar2);
252 function get_plan_for_grade(p_grade_id in number,
253 p_effective_date in date) return number;
254 function get_grade_for_plan(p_plan_id in number,
255 p_effective_date in date) return number;
256 procedure create_option_row(p_copy_entity_txn_id in number,
260 p_scale_cer_id in number,
257 p_effective_date in date,
258 p_business_group_id in number,
259 p_scale_id in number,
261 p_point_id in number,
262 p_dml_operation in varchar2 default 'INSERT',
263 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
264 p_opt_cer_id out nocopy number,
265 p_opt_cer_ovn out nocopy number);
266 function is_scale_exists_in_txn(p_copy_entity_txn_id in number,
267 p_scale_id in number) return number;
268 function is_option_exists_in_txn(p_copy_entity_txn_id in number,
269 p_opt_id in number) return number;
270 function is_hrrate_for_abr_exists(p_copy_entity_txn_id in number,
271 p_abr_id in number) return boolean;
272 function is_grd_exists_in_txn(p_copy_entity_txn_id in number,
273 p_grd_id in number) return number;
274 function is_pl_exists_in_txn(p_copy_entity_txn_id in number,
275 p_pl_id in number) return number;
276 function is_ep_exists_in_txn(p_copy_entity_txn_id in number,
277 p_start_cer in number,
278 p_ep_id in number) return number;
279 function is_point_exists_in_txn(p_copy_entity_txn_id in number,
280 p_point_id in number) return number;
281 function get_point_for_step(p_step_id in number,
282 p_effective_date in date) return number;
283 function get_point_for_opt(p_option_id in number,
284 p_effective_date in date) return number;
285 function get_opt_for_point(p_point_id in number,
286 p_effective_date in date) return number;
287 function get_step_for_oipl(p_oipl_id in number,
288 p_effective_date in date) return number;
289 function get_oipl_for_step(p_step_id in number,
290 p_effective_date in date) return number;
291 procedure get_grade_for_plip(p_plip_id in number,
292 p_effective_date in date,
293 p_plan_id out nocopy number,
294 p_grade_id out nocopy number);
295 procedure create_oipl_row(p_copy_entity_txn_id in number,
296 p_effective_date in date,
297 p_business_group_id in number,
298 p_grade_id in number,
299 p_plip_cer_id in number,
300 p_point_id in number,
301 p_point_cer_id in number,
302 p_option_id in number,
303 p_scale_cer_id in number,
304 p_dml_operation in varchar2 default 'INSERT',
305 p_oipl_cer_id out nocopy number);
306 procedure populate_grd_hierarchy(p_copy_entity_txn_id in number,
307 p_effective_date in date,
308 p_business_group_id in number,
309 p_grade_id in number,
310 p_grade_name in varchar2,
311 p_pgm_cer_id in number,
312 p_in_pl_cer_id in number,
313 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
314 p_out_pl_cer_id out nocopy number,
315 p_plip_cer_id out nocopy number,
316 p_scale_cer_id out nocopy number);
317 procedure create_plan_row(p_copy_entity_txn_id in number,
318 p_effective_date in date,
319 p_business_group_id in number,
320 p_grade_id in number,
321 p_plip_cer_id in number,
322 p_dml_operation in varchar2 default 'INSERT',
323 p_pl_cer_id out nocopy number,
324 p_pl_cer_ovn out nocopy number);
325 procedure create_abr_row(p_copy_entity_txn_id in number,
326 p_start_date in date,
327 p_pl_cer_id in number default null,
328 p_opt_cer_id in number default null,
329 p_business_group_id in number,
330 p_effective_date in date,
331 p_abr_cer_id out nocopy number,
332 p_create_hrr in varchar2 default 'N',
333 p_dml_oper in varchar2);
334 procedure create_hrrate_row(p_copy_entity_txn_id in number,
335 p_effective_date in date,
336 p_start_date in date,
337 p_business_group_id in number,
338 p_abr_cer_id in number,
339 p_grade_cer_id in number,
340 p_grd_value in number,
341 p_grd_min_value in number,
342 p_grd_mid_value in number,
343 p_grd_max_value in number,
344 p_dml_oper in varchar2,
345 p_hrrate_cer_id out nocopy number);
346 procedure create_hrrate_row(p_copy_entity_txn_id in number,
347 p_effective_date in date,
348 p_start_date in date,
352 p_point_value in number,
349 p_business_group_id in number,
350 p_abr_cer_id in number,
351 p_point_cer_id in number,
353 p_dml_oper in varchar2,
354 p_hrrate_cer_id out nocopy number);
355 function is_step_exists_in_txn(p_copy_entity_txn_id in number,
356 p_step_id in number,
357 p_option_id in number,
358 p_pl_id in number) return number;
359 procedure pull_payrate(p_copy_entity_txn_id in number,
360 p_payrate_id in number,
361 p_effective_date in date);
362 procedure update_frps_point_rate(p_point_cer_id in number,
363 p_copy_entity_txn_id in number,
364 p_business_group_id in number,
365 p_point_value in number,
366 p_effective_date in date);
367 function get_co_std_rate(p_plan_id in number default null,
368 p_opt_id in number default null,
369 p_effective_date in date,
370 p_pay_rule_id out nocopy number) return number;
371 procedure create_payrate(p_copy_entity_txn_id in number,
372 p_effective_date in date,
373 p_business_group_id in number);
374 function get_plip_for_pgm_plan(p_pgm_id in number,
375 p_plan_id in number,
376 p_effective_date in date
377 ) return number;
378 end pqh_gsp_hr_to_stage;
[Home] [Help]