1 PACKAGE HR_COMP_PROFILE_SS AS
2 /* $Header: hrcprwrs.pkh 120.0 2005/05/30 23:24:28 appldev noship $ */
3 --
4
5 API_NAME VARCHAR(50) := 'HR_COMP_PROFILE_SS.PROCESS_API';
6
7 COMPETENCES_CHANGED VARCHAR2(30) := 'COMPETENCES_CHANGED';
8 NEXT VARCHAR2(10) := 'NEXT';
9
10
11 TYPE transaction_row IS RECORD
12 (param_name VARCHAR2(200)
13 ,param_value LONG
14 ,param_data_type VARCHAR2(200));
15
16 TYPE transaction_table1 IS TABLE OF transaction_row INDEX BY BINARY_INTEGER;
17
18 g_api_name varchar2(50) := 'HR_COMP_PROFILE_SS.PROCESS_API';
19 g_date_format varchar2(15) := 'RRRR-MM-DD';
20
21 g_upd_mode constant varchar2(30) := 'CORRECT';
22 g_upgrade_proficiency_mode constant varchar2(30) := 'UPGRADE';
23
24 -- Exceptions
25 g_fatal_error exception;
26 g_data_err exception;
27 g_access_violation_err exception;
28
29 -- ---------------------------------------------------------------------------
30 -- ---------------------------- < process_api > ------------------------------
31 -- ---------------------------------------------------------------------------
32 -- Purpose: This procedure reads the data from transaction table and saves it
33 -- to the database.
34 -- This procedure is called after Workflow Approval or the user
35 -- chooses "Update" without approval in workflow.
36 -- ---------------------------------------------------------------------------
37 Procedure process_api(
38 p_validate in boolean default false
39 ,p_transaction_step_id in number
40 ,p_effective_date in varchar2 default null);
41 --
42 Procedure api_validate_competence_rec_ss(
43 p_item_type in varchar2
44 ,p_item_key in varchar2
45 ,p_activity_id in varchar2
46 ,p_pid in number
47 ,p_validate in varchar2
48 ,p_business_group_id in number default null
49 ,p_change_mode in varchar2 default null
50 ,p_competence_element_id in number default null
51 ,p_preupd_obj_vers_num in number default null
52 ,p_competence_id in number default null
53 ,p_proficiency_level_id in number default null
54 ,p_eff_date_from in varchar2 default null
55 ,p_comments in varchar2 default null
56 ,p_eff_date_to in varchar2 default null
57 ,p_proficy_lvl_source in varchar2 default null
58 ,p_certification_mthd in varchar2 default null
59 ,p_certification_date in varchar2 default null
60 ,p_next_certifctn_date in varchar2 default null
61 ,p_competence_status in varchar2 default NULL -- Competence Qualification Link enhancement
62 ,p_transaction_step_id in out nocopy number
63 ,p_error_message out nocopy long);
64 --
65 Procedure process_save_currentupdate(
66 p_item_type in wf_items.item_type%type
67 ,p_item_key in wf_items.item_key%type
68 ,p_actid in varchar2
69 ,p_person_id in number
70 ,p_change_mode in varchar2 default null
71 ,p_preupd_obj_vers_num in number default null
72 ,p_competence_id in number default null
73 ,p_competence_element_id in number default null
74 ,p_competence_name in varchar2 default null
75 ,p_competence_alias in varchar2 default null
76 ,p_proficiency_level_id in number default null
77 ,p_step_value in number default null
78 ,p_preupd_proficy_lvl_id in number default null
79 ,p_certification_mthd in varchar2 default null
80 ,p_proficy_lvl_source in varchar2 default null
81 ,p_eff_date_from in varchar2 default null
82 ,p_eff_date_to in varchar2 default null
83 ,p_certification_date in varchar2 default null
84 ,p_next_certifctn_date in varchar2 default null
85 ,p_comments in varchar2 default null
86 ,p_prev_step_value in number default null
87 ,p_prev_start_date in varchar2 default null
88 ,p_competence_status IN VARCHAR2 DEFAULT NULL -- Competence Qualification Link enhancement
89 ,transaction_step_id in number default null);
90 --
91
92 FUNCTION comp_not_exists
93 (p_item_type in varchar2
94 ,p_item_key in varchar2
95 ,p_person_id in number
96 ,p_competence_id in number
97 ) Return varchar2;
98
99 PROCEDURE save_transaction_step(
100 p_item_type IN VARCHAR2
101 ,p_item_key IN VARCHAR2
102 ,p_actid IN NUMBER
103 ,p_login_person_id IN NUMBER
104 ,p_transaction_step_id IN OUT NOCOPY NUMBER
105 ,p_api_name IN VARCHAR2 default null
106 ,p_api_display_name IN VARCHAR2 DEFAULT NULL
107 ,p_transaction_data IN TRANSACTION_TABLE1) ;
108 --
109 PROCEDURE delete_transaction_step_id(
110 p_transaction_step_id IN number);
111 --
112 PROCEDURE delete_all_ids(
113 p_item_type in varchar2
114 ,p_item_key in varchar2);
115 --
116 Procedure del_pen_currupd_ids(
117 p_item_type IN varchar2
118 ,p_item_key IN varchar2);
119 --
120 Procedure del_add_ids(
121 p_item_type IN varchar2
122 ,p_item_key IN varchar2) ;
123
124 -- to save data on update main page into tx table
125 PROCEDURE process_save_update_details(
126 p_item_type in wf_items.item_type%type
127 ,p_item_key in wf_items.item_key%type
128 ,p_actid in varchar2
129 ,p_person_id in number
130 ,p_proficiency_level_id in number default null
131 ,p_step_value in number default null
132 ,p_eff_date_from in varchar2 default null
133 ,p_prev_step_value in number default null
134 ,p_competence_status IN VARCHAR2 DEFAULT null
135 ,transaction_step_id in number );
136 -- to save the date on the update details page into tx table
137 procedure save_update_details(
138 p_item_type in varchar2
139 ,p_item_key in varchar2
140 ,p_activity_id in varchar2
141 ,p_pid in number
142 ,p_competence_id in number
143 ,p_competence_element_id in number default null
144 ,p_proficiency_level_id in number default null
145 ,p_eff_date_from in varchar2 default null
146 ,p_comments in varchar2 default null
147 ,p_eff_date_to in varchar2 default null
148 ,p_proficy_lvl_source in varchar2 default null
149 ,p_certification_mthd in varchar2 default null
150 ,p_certification_date in varchar2 default null
151 ,p_next_certifctn_date in varchar2 default null
152 ,p_preupd_obj_vers_num in number default null
153 ,p_transaction_step_id in number
154 ,p_prev_eff_date_from in varchar2 default null
155 ,p_pre_eff_date_to in varchar2 default null
156 ,p_competence_status in varchar2 default null -- Competence Qualification Enhancement
157 ,p_error_message out nocopy long);
158
159 --
160 procedure get_comp_name_alias(
161 p_competence_name in out nocopy varchar2
162 ,p_competence_alias in out nocopy varchar2
163 ,p_competence_id out nocopy varchar2
164 ,p_business_group_id in varchar2) ;
165 --
166 procedure set_name_alias(
167 p_competence_id in varchar2 default null
168 ,l_competence_name out nocopy varchar2
169 ,l_competence_alias out nocopy varchar2) ;
170 --
171 PROCEDURE write_add_transaction(
172 p_item_type in varchar2 default null
173 ,p_item_key in varchar2 default null
174 ,p_activity_id in varchar2 default null
175 ,p_pid in varchar2 default null
176 ,p_competence_id in varchar2 default null
177 ,p_competence_name in varchar2 default null
178 ,p_competence_alias in varchar2 default null
179 ,p_proficiency_level_id in varchar2 default null
180 ,p_step_value in varchar2 default null
181 ,p_eff_date_from in varchar2 default null
182 ,p_change_mode in varchar2 default null
183 ,p_row_index in number default null
184 ,p_transaction_step_id in out nocopy varchar2
185 ,p_competence_status IN VARCHAR2 DEFAULT NULL ) ;-- Competence Qualification link
186 --
187 Procedure get_pending_addition_ids(
188 p_item_type IN varchar2
189 ,p_item_key IN varchar2
190 ,p_step_values out nocopy varchar2
191 ,p_rows out nocopy number);
192
193 procedure validate_updated_row
194 (p_competence_id in varchar2
195 ,p_step_value in varchar2
196 ,p_person_id in varchar2
197 ,p_eff_date_from in varchar2 default null
198 ,r_step_value out nocopy varchar2
199 ,r_new_prof_level out nocopy varchar2
200 ,p_item_type in varchar2 default null
201 ,p_item_key in varchar2 default null
202 ,p_activity_id in varchar2 default null
203 ,p_error_message out nocopy varchar2);
204 --
205 procedure get_lov_comp_id(
206 p_competence_name in varchar2 default null
207 ,p_competence_alias in varchar2 default null
208 ,l_competence_id out nocopy varchar2);
209 --
210 procedure set_parameters(
211 p_competence_id in out nocopy varchar2
212 ,p_competence_name in out nocopy varchar2
213 ,p_competence_alias in out nocopy varchar2
214 ,p_step_value in out nocopy varchar2
215 ,p_prof_level_id in out nocopy varchar2
216 ,p_business_group_id in varchar2
217 -- bug 2946360 fix
218 ,p_item_type in varchar2 default null
219 ,p_item_key in varchar2 default null
220 ,p_person_id in number default null
221 ,p_dup_comp_not_exists out nocopy varchar2
222 -- bug 2946360 fix
223 -- bug fix 4136402
224 ,p_eff_date_from in varchar2 default null
225 ,p_eff_date_to in varchar2 default null
226 ,p_activity_id in varchar2 default null
227 -- bug fix 4136402
228 ,p_error_message out nocopy long);
229 --
230 procedure set_upd_parameters(
231 p_competence_id in varchar2 default null
232 ,p_step_value in varchar2 default null);
233 --
234 Procedure delete_add_page(
235 transaction_step_ids in varchar2);
236 --
237 procedure add_to_addition(p_item_type in varchar2
238 ,p_item_key in varchar2);
239 --
240 procedure update_date_validate(
241 p_person_id in varchar2 default null
242 ,p_competence_id in varchar2 default null
243 ,p_eff_date_from in varchar2 default null
244 ,p_error_message out nocopy varchar2) ;
245 --
246 procedure ex_comp_date_validation(
247 p_person_id in varchar2
248 ,p_competence_id in varchar2
249 ,p_eff_date_from in varchar2) ;
250 --
251 Procedure write_proc_actid(
252 p_item_type in varchar2
253 ,p_item_key in varchar2
254 ,p_activity_id in varchar2
255 ,p_person_id in varchar2
256 ,p_review_proc_call in varchar2);
257 --
258 Procedure get_correction_trans_values(
259 p_item_type in varchar2
260 ,p_item_key in varchar2
261 ,p_competence_element_id in number
262 ,p_proficiency_level_id out nocopy number
263 ,p_start_date out nocopy date
264 ,p_end_date out nocopy date
265 ,p_justification out nocopy varchar2
266 ,p_acquired_by out nocopy varchar2
267 ,p_measured_by out nocopy varchar2
268 ,p_ceritification_date out nocopy varchar2
269 ,p_next_review_date out nocopy varchar2);
270 --
271 PROCEDURE final_update_save(
272 p_item_type in varchar2
273 ,p_item_key in varchar2
274 ,p_activity_id in varchar2
275 ,p_competence_element_id in number default null
276 ,p_pid in number
277 ,p_proficiency_level_id in number default null
278 ,p_eff_date_from in varchar2 default null
279 ,p_step_value in number
280 ,p_transaction_step_id in number
281 ,p_competence_status IN varchar2);
282 --
283 Function get_preferred_prof_range(
284 p_person_id in varchar2
285 ,p_competence_id in number) Return VARCHAR2;
286 --
287 Function is_proficiency_required
288 (p_person_id in varchar2
289 ,p_competence_id in number) Return VARCHAR2;
290 --
291 End hr_comp_profile_ss;
[Home] [Help]