1 Package pqh_gsp_utility AUTHID CURRENT_USER as
2 /* $Header: pqgsputl.pkh 120.8 2010/12/15 10:08:08 lbodired ship $ */
3
4
5 function get_gsp_plntyp_str_date (p_business_group_id in number
6 ,p_copy_entity_txn_id in number default null )
7 return date;
8
9 function gsp_plan_type_exists (p_business_group_id in number)
10 return varchar2;
11 --
12 ---------------------------CHK_GRADE_EXIST_IN_GL-----------------------------
13 --
14 -- mvankada
15 Function chk_grade_exist_in_gl
16 (p_copy_entity_txn_id IN ben_copy_entity_results.copy_entity_result_id%TYPE )
17 RETURN VARCHAR2;
18
19 --mvankada
20 Procedure remove_grade_from_grdldr
21 (
22 p_Grade_Result_Id IN ben_copy_entity_results.copy_entity_result_id%TYPE,
23 p_Copy_Entity_Txn_Id IN ben_copy_entity_results.copy_entity_txn_id%TYPE,
24 p_Business_Group_Id IN Number,
25 p_Effective_Date IN Date,
26 p_rec_exists OUT NOCOPY Varchar2
27 );
28
29
30 FUNCTION GET_PGM_TYP(p_cpy_enty_txn_id in number)
31 RETURN varchar2;
32 --
33 FUNCTION ENABLE_DISABLE_START_ICON(p_gsp_node in varchar2,
34 p_copy_enty_txn_id in number,
35 p_table_alias in varchar2)
36 RETURN varchar2;
37 --
38 FUNCTION GET_STATUS(p_gsp_node in varchar2,
39 p_copy_enty_txn_id in number,
40 p_table_alias in varchar2)
41 RETURN varchar2;
42 --
43 --mvankada
44 FUNCTION USE_POINT_OR_STEP(p_copy_entity_txn_id in number)
45 RETURN varchar2;
46 --
47 --mvankada
48 Procedure remove_step_from_grade
49 (
50 p_step_result_id IN ben_copy_entity_results.copy_entity_result_id%TYPE,
51 p_copy_entity_txn_id IN number,
52 p_effective_date IN Date,
53 p_use_points IN varchar2 ,
54 p_step_id IN ben_copy_entity_results.information1%TYPE default NULL,
55 p_celing_step_flag IN varchar2 default 'N',
56 p_rec_exists OUT NOCOPY Varchar2
57 );
58 --
59 --mvankada
60 FUNCTION CHK_PROFILE_EXISTS
61 ( p_copy_entity_result_id IN Ben_Copy_Entity_Results.Copy_Entity_Result_Id%Type,
62 p_copy_entity_txn_id IN Ben_Copy_Entity_Results.Copy_Entity_Txn_Id%Type
63 ) RETURN varchar2;
64
65
66 --mvankada
67 FUNCTION DISPLAY_ICON
68 (p_page IN Varchar2,
69 p_Table_Alias IN Ben_Copy_Entity_Results.Table_Alias%Type,
70 p_action IN Varchar2,
71 p_copy_entity_txn_id IN Ben_Copy_Entity_Results.Copy_Entity_Txn_Id%Type,
72 p_copy_entity_result_id IN Ben_Copy_Entity_Results.Copy_Entity_Result_Id%Type
73 ) RETURN varchar2;
74
75
76 procedure chk_grd_details
77 (
78 p_name IN per_grades.name%TYPE ,
79 p_short_name IN per_grades.short_name%TYPE,
80 p_business_group_id IN per_grades.business_group_id%TYPE,
81 p_grade_id IN per_grades.grade_id%TYPE default NULL,
82 p_copy_entity_result_id IN ben_copy_entity_results.copy_entity_result_id%TYPE default NULL,
83 p_copy_entity_txn_id IN ben_copy_entity_results.copy_entity_txn_id%TYPE,
84 p_status OUT NOCOPY VARCHAR
85 );
86
87 --mvankada
88 FUNCTION GET_STEP_PRG_RULE_HGRID_NAME( p_copy_entity_result_id in Number,
89 p_copy_entity_txn_id in Number,
90 p_Table_Alias in ben_copy_entity_results.Table_Alias%Type,
91 p_hgrid in Varchar Default NULL)
92 RETURN varchar2;
93 --
94
95 Function get_standard_rate(p_copy_entity_result_id in number,
96 p_effective_date in date)
97 RETURN number;
98 --
99 procedure delete_transaction
100 (p_pqh_copy_entity_txn_id IN pqh_copy_entity_txns.copy_entity_txn_id%TYPE);
101 --
102 procedure del_gl_details_from_stage
103 (p_pqh_copy_entity_txn_id IN pqh_copy_entity_txns.copy_entity_txn_id%TYPE);
104
105 procedure enddate_grade_ladder
106 (p_ben_pgm_id IN ben_pgm_f.pgm_id%TYPE,
107 p_effective_date_in IN ben_pgm_f.effective_start_date%TYPE);
108
109 Function Get_Step_Dtls
110 (P_Entity_id In Number,
111 P_Effective_Date In Date,
112 P_Id_name In Varchar2,
113 P_Curr_Prop In Varchar2)
114
115 RETURN Number;
116
117 Function Get_Cur_Sal
118 (P_Assignment_id In Per_All_Assignments_F.ASSIGNMENT_ID%TYPE,
119 P_Effective_Date In Date)
120 Return Number;
121
122 Function Get_CAGR_Name
123 (P_CAGR_Id IN Per_Collective_Agreements.Collective_Agreement_ID%TYPE)
124 return varchar2;
125
126 Function gen_txn_display_name
127 (p_program_name IN pqh_copy_entity_txns.display_name%TYPE,
128 p_mode IN varchar2)
129 Return Varchar2;
130
131 Function get_grade_ladder_name_from_txn
132 (p_pqh_copy_entity_txn_id IN pqh_copy_entity_txns.copy_entity_txn_id%TYPE)
133 Return Varchar2;
134
135 --mvankada
136 Procedure chk_default_ladder_exist
137 ( p_pgm_id in number,
138 p_business_group_id in number,
139 p_effective_date in Date);
140
141 --mvankada
142 Procedure chk_add_steps_in_all_grades
143 ( p_copy_entity_txn_id in number,
144 p_business_group_id in number);
145
146 --mvankada
147 Procedure chk_valid_grd_in_grdldr
148 ( p_copy_entity_txn_id in number,
149 p_effective_date in date,
150 p_business_group_id in Number);
151
152 --mvankada
153 Procedure chk_inactivate_grdldr
154 (p_pgm_id in Number,
155 p_effective_date in Date,
156 p_business_group_id in Number,
157 p_activate in Varchar Default 'A');
158
159 Function Get_Emp_Los
160 (P_Person_id In Per_All_PEOPLE_F.Person_Id%TYPE,
161 P_Effective_Date In Date)
162 Return Number;
163
164 Function Get_Currency
165 (P_Corrency_Code In Fnd_Currencies_Vl.Currency_Code%TYPE)
166 Return Varchar2;
167
168 Function Get_SpinalPoint_Name
169 (p_Point_id IN per_spinal_points.Spinal_Point_Id%TYPE)
170 Return Varchar2;
171
172 --mvankada
173 Procedure update_or_delete_grade
174 ( p_copy_entity_txn_id in number,
175 p_grade_result_id in number,
176 p_effective_date in Date);
177
178 --mvankada
179 Procedure update_or_delete_step
180 ( p_copy_entity_txn_id in Number,
181 p_step_result_id in Number,
182 p_step_id in Number,
183 p_point_result_id in Number,
184 p_effective_date in Date);
185
186
187 procedure set_step_name(p_copy_entity_txn_id in number,
188 p_effective_start_date in date,
189 p_grd_result_id in number);
190 --mvankada
191 Procedure chk_unlink_grd_from_grdldr
192 (p_pgm_id in Number
193 ,p_copy_entity_txn_id in Number
194 ,p_business_group_id in Number
195 ,p_effective_date in Date
196 ,p_status OUT NOCOPY Varchar2
197 );
198
199 --mvankada
200 Procedure chk_unlink_step_from_grdldr
201 (p_copy_entity_txn_id in Number
202 ,p_business_group_id in Number
203 ,p_effective_date in Date
204 );
205
206 --mvankada
207 Procedure chk_gl_sht_name_code_unique
208 ( p_pgm_id in Number
209 ,p_business_group_id in Number
210 ,p_short_name in varchar2 Default Null
211 ,p_short_code in varchar2 Default Null);
212
213 -- mvankada
214 Procedure chk_grdldr_name_unique
215 ( p_pgm_id in Number
216 ,p_business_group_id in Number
217 ,p_name in varchar2
218 );
219 --
220 -- The following procedure validates the grade ladder before it is saved.
221 --
222 Procedure validate_grade_ladder(
223 p_pgm_id in number
224 ,p_effective_start_date in date default null
225 ,p_effective_end_date in date default null
226 ,p_name in varchar2 default null
227 ,p_pgm_stat_cd in varchar2 default null
228 ,p_pgm_typ_cd in varchar2 default null
229 ,p_enrt_cvg_strt_dt_cd in varchar2 default null
230 ,p_enrt_cvg_strt_dt_rl in number default null
231 ,p_rt_strt_dt_cd in varchar2 default null
232 ,p_rt_strt_dt_rl in number default null
233 ,p_pgm_uom in varchar2 default null
234 ,p_enrt_cd in varchar2 default null
235 ,p_enrt_mthd_cd in varchar2 default null
236 ,p_enrt_rl in number default null
237 ,p_auto_enrt_mthd_rl in number default null
238 ,p_business_group_id in number default null
239 ,p_Dflt_pgm_flag in Varchar2 default null
240 ,p_Use_prog_points_flag in Varchar2 default null
241 ,p_Dflt_step_cd in Varchar2 default null
242 ,p_Dflt_step_rl in number default null
243 ,p_Update_salary_cd in Varchar2 default null
244 ,p_Use_multi_pay_rates_flag in Varchar2 default null
245 ,p_dflt_element_type_id in number default null
246 ,p_Dflt_input_value_id in number default null
247 ,p_Use_scores_cd in Varchar2 default null
248 ,p_Scores_calc_mthd_cd in Varchar2 default null
249 ,p_Scores_calc_rl in number default null
250 ,p_gsp_allow_override_flag in varchar2 default null
251 ,p_use_variable_rates_flag in varchar2 default null
252 ,p_salary_calc_mthd_cd in varchar2 default null
253 ,p_salary_calc_mthd_rl in number default null
254 ,p_effective_date in date
255 ,p_short_name in varchar2 default null
256 ,p_short_code in varchar2 default null
257 );
258 --
259
260 Procedure chk_grdldr_grd_curreny_rate
261 (p_copy_entity_txn_id In Number,
262 p_business_group_id In Number,
263 p_effective_date In Date);
264
265
266 --mvankada
267 Procedure chk_review_submit_val
268 (p_copy_entity_txn_id in Number,
269 p_effective_date in Date,
270 p_pgm_id in Number default null,
271 p_business_group_id in Number,
272 p_status OUT NOCOPY Varchar2,
273 p_prog_le_created_flag OUT NOCOPY Varchar2,
274 p_sync_le_created_flag OUT NOCOPY Varchar2,
275 p_plan_tp_created_flag OUT NOCOPY Varchar2
276 );
277 --
278
279
280 --
281 Function get_rates_icon_enabled
282 (p_copy_entity_txn_id in Number,
283 p_copy_entity_result_id in Number,
284 p_rate_hgrid_node in varchar2)
285 Return Varchar2;
286 --
287 --
288 -- Function to return the annualization factor for frequency codes used in Benefits.
289 --
290 Function pgm_freq_annual_factor
291 (p_ref_perd_cd in varchar2) return number;
292 --
293
294 Procedure create_pay_rate(p_business_group_id in number,
295 p_ldr_period_code in varchar2,
296 p_rate_id Out nocopy number,
297 p_ovn Out nocopy number);
298 --
299 Procedure create_pay_rate (p_business_group_id in number,
300 p_scale_id in number,
301 p_rate_name in varchar2,
302 p_rate_id Out nocopy number,
303 p_ovn Out nocopy number);
304 --
305 procedure step_exists_for_point(p_copy_entity_txn_id in number,
306 p_points_result_id in number,
307 p_status out nocopy varchar);
308 --
309 procedure chk_scale_name(p_copy_entity_txn_id in number,
310 p_business_group_id in number,
311 p_copy_entity_result_id in number,
312 p_parent_spine_id in number,
313 p_name in varchar,
314 p_status out nocopy varchar);
315 --
316 Function get_dflt_salary_rate
317 (p_copy_entity_txn_id in Number,
318 p_copy_entity_result_id in Number,
319 p_rate_hgrid_node in varchar2)
320 Return Number;
321 --
322 Function is_crrate_there_icon
323 (p_copy_entity_txn_id in Number,
324 p_copy_entity_result_id in Number,
325 p_effective_date_in in date,
326 p_rate_hgrid_node in varchar2)
327 Return Varchar2;
328 --
329 procedure chk_steps_in_grade(p_copy_entity_txn_id in number,
330 p_grade_result_id in number,
331 p_status out nocopy varchar2);
332
333 Function PGM_TO_BASIS_CONVERSION
334 (P_Pgm_ID IN Number
335 ,P_EFFECTIVE_DATE IN Date
336 ,P_AMOUNT IN Number
337 ,P_ASSIGNMENT_ID IN Number)
338
339 Return Number;
340
341 Function get_num_steps_in_grade(p_copy_entity_txn_id in number,
342 p_grade_cer_id in number)
343 Return Number;
344
345 --
346 --
347 Function get_dflt_point_rate (p_copy_entity_txn_id in number,
348 p_point_cer_id in number,
349 p_effective_date in date)
350 RETURN NUMBER;
351
352 Function Get_person_name (P_Person_id IN Number,
353 P_Effective_Date IN Date)
354
355 Return Varchar2;
356
357 Function Get_Assgt_Status (P_Assgt_Status_Id IN Number)
358 Return varchar2;
359 --
360
361 Procedure check_sal_basis_iv (p_input_value_id in number,
362 p_basis_id in number,
363 p_business_group_id in number,
364 p_exists_flag Out nocopy varchar2);
365 --
366 procedure update_oipl_records(
367 p_effective_date IN DATE,
368 p_copy_entity_result_id IN ben_copy_entity_results.copy_entity_result_id%TYPE,
369 p_point_name IN ben_copy_entity_results.information99%TYPE,
370 p_sequence IN ben_copy_entity_results.information263%TYPE,
371 p_copy_entity_txn_id IN ben_copy_entity_results.copy_entity_txn_id%TYPE
372 );
373 --
374 Procedure validate_crset_values(p_copy_entity_txn_id in number,
375 p_effective_date in date);
376 Procedure chk_duplicate_crset_exists(
377 p_copy_entity_txn_id in number,
378 p_effective_date in date,
379 p_cset_id in number default null,
380 p_location_id in number default null,
381 p_job_id in number default null,
382 p_org_id in number default null,
383 p_rule_id in number default null,
384 p_person_type_id in number default null,
385 p_service_area_id in number default null,
386 p_barg_unit_cd in varchar2 default null,
387 p_full_part_time_cd in varchar2 default null,
388 p_perf_type_cd in varchar2 default null,
389 p_rating_type_cd in varchar2 default null,
390 p_duplicate_exists out nocopy varchar2,
391 p_duplicate_cset_name out nocopy varchar2);
392 Procedure move_data_stage_to_hr
393 (p_copy_entity_txn_id in Number,
394 p_effective_date in Date,
395 p_business_area in varchar2 default 'PQH_GSP_TASK_LIST',
396 p_business_group_id in Number,
397 p_datetrack_mode in Varchar2,
398 p_error_msg out Nocopy Varchar2
399 );
400 procedure get_grade_name (
401 p_grade_definition_id IN NUMBER,
402 p_business_group_id IN NUMBER,
403 p_concatenated_segments OUT NOCOPY VARCHAR2);
404 FUNCTION GET_DML_OPERATION
405 (p_in_dml_operation in ben_copy_entity_results.dml_operation%TYPE)
406 RETURN VARCHAR2;
407 --
408 Procedure chk_no_asg_grd_ldr(p_asg_grade_ladder_id in number,
409 p_asg_grade_id in number,
410 p_asg_org_id in number,
411 p_asg_bg_id in number,
412 p_effective_date in date);
413 --
414 FUNCTION bus_area_pgm_entity_exist(p_bus_area_cd IN Varchar2,
415 P_pgm_id IN NUMBER)
416 RETURN varchar2 ;
417 PROCEDURE unlink_step_or_point (p_copy_entity_result_id IN NUMBER);
418
419 --ggnanagu
420 PROCEDURE chk_delete_option (
421 p_copy_entity_txn_id IN NUMBER,
422 p_opt_cer_id IN NUMBER,
423 p_point_id IN NUMBER,
424 p_opt_id IN NUMBER,
425 p_pspine_id IN NUMBER,
426 p_effective_date IN DATE
427
428 );
429 --
430 PROCEDURE chk_new_ceiling (
431 p_effective_date IN DATE,
432 p_grade_cer_id IN NUMBER,
433 p_new_ceiling IN NUMBER
434 );
435 --ggnanagu
436 PROCEDURE change_ceiling_step (
437 p_copy_entity_txn_id IN NUMBER,
438 p_effective_date IN DATE,
439 p_initial_ceiling_id IN NUMBER,
440 p_final_ceiling_id IN NUMBER,
441 p_grade_result_id IN NUMBER
442 );
443
444 ----ggnanagu
445
446 procedure update_frps_point_rate(p_point_cer_id in number,
447 p_copy_entity_txn_id in number,
448 p_business_group_id in number,
449 p_salary_rate in number,
450 p_gross_index in number,
451 p_effective_date in date
452 );
453 Function chk_from_steps(p_parent_spine_id IN per_parent_spines.parent_spine_id%TYPE)
454 RETURN VARCHAR2;
455 function check_crset(p_crset_type in VARCHAR2,p_crset_id IN NUMBER,p_copy_entity_txn_id IN NUMBER,p_scale_cer_id in number)
456 return varchar2;
457 procedure change_scale_name(p_copy_entity_txn_id in number,p_pl_cer_id in number,p_short_name in varchar2);
458 procedure remove_steps(p_copy_entity_txn_id IN NUMBER, p_grade_result_id IN NUMBER);
459 procedure change_rates_date(p_copy_entity_txn_id in number,p_pl_cer_id in number,p_start_date in DATE);
460 FUNCTION GET_CURRENCY_CODE(p_copy_entity_txn_id in number) RETURN varchar2;
461 FUNCTION get_grd_start_date(p_copy_entity_result_id in ben_copy_entity_results.copy_entity_result_id%TYPE)RETURN DATE;
462 procedure change_start_step(p_copy_entity_txn_id in number
463 ,p_init_start_step in number
464 ,p_final_start_step in number
465 ,p_grade_result_id in number
466 );
467
468 FUNCTION get_bg_currency(p_business_group_id in number) RETURN varchar2;
469 FUNCTION get_formula_name (p_formula_id IN NUMBER, p_effective_date IN DATE)
470 RETURN VARCHAR2;
471
472 FUNCTION get_element_name (p_element_type_id IN NUMBER)
473 RETURN VARCHAR2;
474
475 FUNCTION get_input_val_name (p_input_value_id IN NUMBER)
476 RETURN VARCHAR2;
477 PROCEDURE chk_grd_ldr_details (
478 p_business_group_id IN NUMBER,
479 p_name IN VARCHAR2,
480 p_dflt_pgm_flag IN VARCHAR2,
481 p_pgm_id IN NUMBER,
482 p_effective_date IN DATE
483 );
484
485 function get_gl_ann_factor(p_pgm_id in number) return varchar2;
486
487 --rlpatil
488
489 PROCEDURE upd_ceiling_info(p_grade_cer_id IN NUMBER, p_step_id IN number);
490
491 -- Start changes for bug 9328526
492 procedure chk_ps_for_upd_del(
493 p_parent_spine_id IN NUMBER
494 ,p_entity IN VARCHAR2
495 );
496
497 procedure chk_rate_for_upd_del(
498 p_rate_id IN NUMBER
499 ,p_entity IN VARCHAR2
500 );
501 -- End changes for bug 9328526
502 -- Start changes for bug 10324888
503 Function Get_current_rate_value
504 (P_Entity_id In Number,
505 P_Effective_Date In Date
506 ) RETURN Number ;
507
508 -- End changes for bug 10324888
509
510 End pqh_gsp_utility;