1 PACKAGE hr_pay_rate_ss AUTHID CURRENT_USER as
2 /* $Header: hrpaywrs.pkh 120.3.12010000.4 2009/08/18 09:01:28 gpurohit ship $*/
3
4
5 -- 05/14/2002 - Bug 2374140 Fix Begins
6 -- ------------------------------------------------------------------------
7 -- |------------------ < check_mid_pay_period_change > --------------------|
8 -- ------------------------------------------------------------------------
9 --
10 -- Description
11 --
12 -- Determine if a mid pay period change was performed when a salary basis
13 -- was changed. If yes, we need to set the WF item attribute
14 -- HR_MID_PAY_PERIOD_CHANGE ='Y' so that a notification will be sent to the
15 -- Payroll Contact.
16 --
17 -- This procedure is invoked by the WF HR_CHK_SAL_BASIS_MID_PAY_PERIOD process.
18 -- ------------------------------------------------------------------------
19 --
20 procedure check_mid_pay_period_change
21 (p_item_type in varchar2,
22 p_item_key in varchar2,
23 p_act_id in number,
24 funmode in varchar2,
25 result out nocopy varchar2 );
26
27 -- 05/14/2002 - Bug 2374140 Fix Ends
28
29 /*===============================================================
30 | Procedure: check_asg_txn_data
31 | Function: This is a cover routine invoked by Java.
32 |================================================================
33 */
34 PROCEDURE check_asg_txn_data
35 (p_item_type in varchar2
36 ,p_item_key in varchar2
37 ,p_act_id in number
38 ,p_effective_date in date
39 ,p_assignment_id in number
40 ,p_asg_txn_step_id in out nocopy number
41 ,p_get_defaults_date in out nocopy date
42 ,p_business_group_id out nocopy number
43 ,p_currency out nocopy varchar2
44 ,p_format_string out nocopy varchar2
45 ,p_salary_basis_name out nocopy varchar2
46 ,p_pay_basis_name out nocopy varchar2
47 ,p_pay_basis out nocopy varchar2
48 ,p_grade_basis out nocopy varchar2
49 ,p_pay_annualization_factor out nocopy number
50 ,p_fte_factor out nocopy number
51 ,p_grade out nocopy varchar2
52 ,p_grade_annualization_factor out nocopy number
53 ,p_minimum_salary out nocopy number
54 ,p_maximum_salary out nocopy number
55 ,p_midpoint_salary out nocopy number
56 ,p_prev_salary out nocopy number
57 ,p_last_change_date out nocopy date
58 ,p_element_entry_id out nocopy number
59 ,p_basis_changed out nocopy number
60 ,p_uom out nocopy varchar2
61 ,p_grade_uom out nocopy varchar2
62 ,p_change_amount out nocopy number
63 ,p_change_percent out nocopy number
64 ,p_quartile out nocopy number
65 ,p_comparatio out nocopy number
66 ,p_last_pay_change out nocopy varchar2
67 ,p_flsa_status out nocopy varchar2
68 ,p_currency_symbol out nocopy varchar2
69 ,p_precision out nocopy number
70 ,p_excep_message out nocopy varchar2
71 ,p_pay_proposal_id out nocopy number
72 ,p_current_salary out nocopy number
73 ,p_proposal_ovn out nocopy number
74 ,p_api_mode out nocopy varchar2
75 ,p_warning_message out nocopy varchar2
76 ,p_new_pay_basis_id out nocopy number
77 ,p_old_pay_basis_id out nocopy number
78 ,p_old_pay_annualization_factor out nocopy number
79 ,p_old_fte_factor out nocopy number
80 ,p_old_salary_basis_name out nocopy varchar2
81 ,p_salary_basis_change_type out nocopy varchar2
82 ,p_flow_mode in out nocopy varchar2 -- 2355929
83 ,p_element_type_id_changed out nocopy varchar2
84 ,p_old_currency_code out nocopy varchar2
85 ,p_old_currency_symbol out nocopy varchar2
86 ,p_old_pay_basis out nocopy varchar2 --4002387
87 ,p_old_to_new_currency_rate out nocopy number --4002387
88 ,p_offered_salary out nocopy number
89 ,p_proc_sel_txn in varchar2 default null
90 );
91
92 -- GSP changes
93 /*===============================================================
94 | Procedure: check_asg_txn_data_gsp
95 | Function: This is a cover routine invoked by Java.
96 |================================================================
97 */
98 PROCEDURE check_gsp_asg_txn_data
99 (p_item_type in varchar2
100 ,p_item_key in varchar2
101 ,p_act_id in number
102 ,p_effective_date in date
103 ,p_assignment_id in number
104
105 ,p_asg_txn_step_id in number
106 ,p_get_defaults_date in date
107 ,p_excep_message out nocopy varchar2
108 ,p_flow_mode in varchar2 -- 2355929
109 );
110
111 PROCEDURE MY_GET_DEFAULTS(p_assignment_id IN NUMBER
112 ,p_job_id IN NUMBER
113 ,p_date IN OUT NOCOPY DATE
114 ,p_business_group_id OUT NOCOPY NUMBER
115 ,p_currency OUT NOCOPY VARCHAR2
116 ,p_format_string OUT NOCOPY VARCHAR2
117 ,p_salary_basis_name OUT NOCOPY VARCHAR2
118 ,p_pay_basis_name OUT NOCOPY VARCHAR2
119 ,p_pay_basis OUT NOCOPY VARCHAR2
120 ,p_grade_basis OUT NOCOPY VARCHAR2
121 ,p_pay_annualization_factor OUT NOCOPY NUMBER
122 ,p_fte_factor OUT NOCOPY NUMBER
123 ,p_grade OUT NOCOPY VARCHAR2
124 ,p_grade_annualization_factor OUT NOCOPY NUMBER
125 ,p_minimum_salary OUT NOCOPY NUMBER
126 ,p_maximum_salary OUT NOCOPY NUMBER
127 ,p_midpoint_salary OUT NOCOPY NUMBER
128 ,p_prev_salary OUT NOCOPY NUMBER
129 ,p_last_change_date OUT NOCOPY DATE
130 ,p_element_entry_id OUT NOCOPY NUMBER
131 ,p_basis_changed OUT NOCOPY number
132 ,p_uom OUT NOCOPY VARCHAR2
133 ,p_grade_uom OUT NOCOPY VARCHAR2
134 ,p_change_amount out nocopy number
135 ,p_change_percent out nocopy number
136 , p_quartile out nocopy number
137 , p_comparatio out nocopy number
138 , p_last_pay_change out nocopy varchar2
139 , p_flsa_status out nocopy varchar2
140 , p_currency_symbol out nocopy varchar2
141 , p_precision out nocopy number);
142
143 -------------------------------------------------
144 -- Function
145 -- get_rate_type
146 --
147 --
148 -- Purpose
149 --
150 -- Returns the rate type given the business group, effective date and
151 -- processing type
152 --
153 -- Returns NULL if no type found
154 --
155 -- Current processing types are:-
156 -- P - Payroll Processing
157 -- R - General HRMS reporting
158 -- I - Business Intelligence System
159 --
160 -- History
161 -- 22/01/99 wkerr.uk Created
162 --
163 -- Argumnents
164 -- p_business_group_id The business group
165 -- p_conversion_date The date for which to return the rate type
166 -- p_processing_type The processing type of which to return the rate
167 --
168 FUNCTION get_rate_type (
169 p_business_group_id NUMBER,
170 p_conversion_date DATE,
171 p_processing_type VARCHAR2 ) RETURN VARCHAR2;
172 --
173 --PRAGMA RESTRICT_REFERENCES(get_rate_type,WNDS);
174 --
175 ------------------------------------------------------------------------
176
177
178 -- Function
179 -- get_rate
180 --
181 -- Purpose
182 -- Returns the rate between the two currencies for a given conversion
183 -- date and rate type.
184 --
185 -- History
186 -- 22-Apr-98 wkerr Created
187 --
188 -- Arguments
189 -- p_from_currency From currency
190 -- p_to_currency To currency
191 -- p_conversion_date Conversion date
192 -- p_rate_type Rate Type
193 --
194 FUNCTION get_rate (
195 p_from_currency VARCHAR2,
196 p_to_currency VARCHAR2,
197 p_conversion_date DATE,
198 p_rate_type VARCHAR2) RETURN NUMBER;
199 PRAGMA RESTRICT_REFERENCES(get_rate,WNDS,WNPS);
200
201
202
203
204 -- Function
205 -- convert_amount
206 --
207 -- Purpose
208 -- Returns the amount converted from the from currency into the
209 -- to currency for a given conversion date and rate type.
210 -- The amount returned is rounded to the precision and minimum
211 -- account unit of the to currency.
212 --
213 -- History
214 -- 22-Apr-98 wkerr Created
215 --
216 -- Arguments
217 -- p_from_currency From currency
218 -- p_to_currency To currency
219 -- p_conversion_date Conversion date
220 -- p_amount Amount to be converted from the from currency
221 -- into the to currency
222 -- p_rate_type Rate Type
223 --
224 FUNCTION convert_amount (
225 p_from_currency VARCHAR2,
226 p_to_currency VARCHAR2,
227 p_conversion_date DATE,
228 p_amount NUMBER,
229 p_rate_type VARCHAR2) RETURN NUMBER;
230 PRAGMA RESTRICT_REFERENCES(convert_amount,WNDS,WNPS);
231
232 -- define a type for salary proposal record
233 TYPE lrt_salary_proposal is RECORD (
234 pay_proposal_id NUMBER,
235 assignment_id NUMBER,
236 business_group_id NUMBER,
237 effective_date DATE,
238 comments LONG,
239 next_sal_review_date DATE,
240 salary_change_amount NUMBER ,
241 salary_change_percent NUMBER ,
242 annual_change NUMBER ,
243 proposed_salary NUMBER ,
244 proposed_percent NUMBER ,
245 proposal_reason VARCHAR2(30),
246 ranking NUMBER,
247 current_salary NUMBER,
248 performance_review_id NUMBER,
249 multiple_components VARCHAR2(1),
250 element_entry_id NUMBER ,
251 selection_mode VARCHAR2(1),
252 ovn NUMBER,
253 currency VARCHAR2(15),
254 pay_basis_name VARCHAR2(80),
255 annual_equivalent NUMBER ,
256 total_percent NUMBER ,
257 quartile NUMBER ,
258 comparatio NUMBER ,
259 lv_selection_mode VARCHAR2(1),
260 attribute_category VARCHAR2(150),
261 attribute1 VARCHAR2(150),
262 attribute2 VARCHAR2(150),
263 attribute3 VARCHAR2(150),
264 attribute4 VARCHAR2(150),
265 attribute5 VARCHAR2(150),
266 attribute6 VARCHAR2(150),
267 attribute7 VARCHAR2(150),
268 attribute8 VARCHAR2(150),
269 attribute9 VARCHAR2(150),
270 attribute10 VARCHAR2(150),
271 attribute11 VARCHAR2(150),
272 attribute12 VARCHAR2(150),
273 attribute13 VARCHAR2(150),
274 attribute14 VARCHAR2(150),
275 attribute15 VARCHAR2(150),
276 attribute16 VARCHAR2(150),
277 attribute17 VARCHAR2(150),
278 attribute18 VARCHAR2(150),
279 attribute19 VARCHAR2(150),
280 attribute20 VARCHAR2(150),
281 no_of_components NUMBER,
282 salary_basis_change_type varchar2(30));
283
284
285 -- define a type for salary component records
286 TYPE lrt_salary_component is RECORD (
287 component_id NUMBER ,
288 pay_proposal_id NUMBER ,
289 approved VARCHAR2(30),
290 component_reason VARCHAR2(30),
291 reason_meaning VARCHAR2(80),
292 change_amount VARCHAR2(30) ,
293 change_percent NUMBER ,
294 change_annual NUMBER ,
295 comments VARCHAR2(2000),
296 ovn NUMBER ,
297 attribute_category VARCHAR2(30),
298 attribute1 VARCHAR2(150),
299 attribute2 VARCHAR2(150),
300 attribute3 VARCHAR2(150),
301 attribute4 VARCHAR2(150),
302 attribute5 VARCHAR2(150),
303 attribute6 VARCHAR2(150),
304 attribute7 VARCHAR2(150),
305 attribute8 VARCHAR2(150),
306 attribute9 VARCHAR2(150),
307 attribute10 VARCHAR2(150),
308 attribute11 VARCHAR2(150),
309 attribute12 VARCHAR2(150),
310 attribute13 VARCHAR2(150),
311 attribute14 VARCHAR2(150),
312 attribute15 VARCHAR2(150),
313 attribute16 VARCHAR2(150),
314 attribute17 VARCHAR2(150),
315 attribute18 VARCHAR2(150),
316 attribute19 VARCHAR2(150),
317 attribute20 VARCHAR2(150),
318 object_version_number NUMBER
319 ) ;
320
321 TYPE ltt_salary is table of lrt_salary_proposal INDEX BY BINARY_INTEGER ;
322 TYPE ltt_components is table of lrt_salary_component
323 INDEX BY BINARY_INTEGER ;
324
325
326 USER_DATE_FORMAT VARCHAR2(20) := 'RRRR-MM-DD';
327
328 -- declare a table for storing txn steps
329 gtt_transaction_steps hr_transaction_ss.transaction_table ;
330
331 --gtt_trans_steps hr_transaction_ss.transaction_table ;
332
333 gv_package_name VARCHAR2(30) := 'HR_PAY_RATE_SS' ;
334
335 gv_activity_name wf_item_activity_statuses_v.activity_name%TYPE
336 :='HR_MAINTAIN_SALARY' ;
337 gv_process_name wf_process_activities.process_name%TYPE
338 := 'HR_SALARY_PRC' ;
339 Procedure start_transaction(itemtype in varchar2
340 ,itemkey in varchar2
341 ,actid in number
342 ,funmode in varchar2
343 ,p_creator_person_id in number
344 ,result out nocopy varchar2 );
345
346
347 /********************************************************/
348 /**** Implementation change using Oracle Object Types ***/
349 /********************************************************/
350 PROCEDURE validate_salary_details (
351 p_assignment_id IN VARCHAR2,
352 p_bg_id IN VARCHAR2,
353 p_effective_date IN VARCHAR2,
354 p_payroll_id IN VARCHAR2,
355 p_old_pay_basis_id in number default null,
356 p_new_pay_basis_id in number default null,
357 excep_message OUT NOCOPY VARCHAR2,
358 p_pay_proposal_id OUT NOCOPY NUMBER,
359 p_current_salary OUT NOCOPY NUMBER,
360 p_ovn OUT NOCOPY NUMBER,
361 p_api_mode OUT NOCOPY VARCHAR2,
362 p_warning_message OUT NOCOPY VARCHAR2,
363 p_asg_type in varchar2 default 'E'
364 ) ;
365
366
367
368 PROCEDURE validate_salary_details (
369 p_assignment_id IN VARCHAR2,
370 p_effective_date IN date DEFAULT NULL,
371 p_item_type IN VARCHAR2 DEFAULT NULL,
372 p_item_key IN VARCHAR2 DEFAULT NULL,
373 excep_message OUT NOCOPY VARCHAR2,
374 p_pay_proposal_id OUT NOCOPY NUMBER,
375 p_current_salary OUT NOCOPY NUMBER,
376 p_ovn OUT NOCOPY NUMBER,
377 p_api_mode OUT NOCOPY VARCHAR2,
378 p_proposal_change_date OUT NOCOPY DATE
379 );
380
381 PROCEDURE is_transaction_exists(p_item_type IN VARCHAR2,
382 p_item_key IN VARCHAR2,
383 p_act_id IN VARCHAR2,
384 trans_exists OUT NOCOPY VARCHAR2,
385 no_of_components OUT NOCOPY NUMBER,
386 is_multiple_payrate OUT NOCOPY VARCHAR2 );
387
388
389 PROCEDURE get_transaction_step_details(p_item_type IN VARCHAR2,
390 p_item_key IN VARCHAR2,
391 p_transaction_step_id IN VARCHAR2,
392 trans_exists OUT NOCOPY VARCHAR2,
393 no_of_components OUT NOCOPY NUMBER,
394 is_multiple_payrate OUT NOCOPY VARCHAR2 );
395
396 PROCEDURE process_salary_java (
397 p_item_type IN VARCHAR ,
398 p_item_key IN VARCHAR2 ,
399 p_act_id IN VARCHAR2 ,
400 ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ,
401 ltt_component IN OUT NOCOPY sshr_sal_comp_tab_typ,
402 p_api_mode IN VARCHAR2,
403 p_review_proc_call IN VARCHAR2,
404 p_save_mode IN VARCHAR2,
405 p_flow_mode in out nocopy varchar2, -- 2355929
406 p_step_id OUT NOCOPY NUMBER,
407 p_warning_msg_name IN OUT NOCOPY varchar2,
408 p_error_msg_text IN OUT NOCOPY varchar2,
409 p_rptg_grp_id IN varchar2 default null,
410 p_plan_id IN varchar2 default null,
411 p_effective_date_option IN varchar2 default null
412 );
413
414
415 -- GSP change
416 PROCEDURE get_transaction_details (
417 p_item_type IN wf_items.item_type%type ,
418 p_item_key IN wf_items.item_key%TYPE ,
419 p_Act_id IN VARCHAR2,
420 p_ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ,
421 p_ltt_component IN OUT NOCOPY sshr_sal_comp_tab_typ );
422
423
424 PROCEDURE get_txn_details_for_review (
425 p_item_type IN wf_items.item_type%type ,
426 p_item_key IN wf_items.item_key%TYPE ,
427 p_transaction_step_id IN VARCHAR2,
428 p_ltt_salary_data IN OUT NOCOPY sshr_sal_prop_tab_typ,
429 p_ltt_component IN OUT NOCOPY sshr_sal_comp_tab_typ );
430
431 -- End of GSP change
432
433
434 procedure delete_transaction_step
435 (p_transaction_id in number,
436 p_login_person_id in number );
437
438
439 --PROCEDURE process_api_java (
440 /*
441 PROCEDURE PROCESS_API (
442 p_transaction_step_id IN hr_api_transaction_steps.transaction_step_id%type,
443 p_validate IN boolean default false,
444 p_effective_date in varchar2 default null
445 );
446 */
447
448 PROCEDURE PROCESS_API (
449 p_transaction_step_id IN hr_api_transaction_steps.transaction_step_id%type,
450 p_effective_date in varchar2 default null,
451 p_validate IN boolean default false
452 );
453
454 procedure prate_applicant_hire
455 (p_person_id in number,
456 p_bg_id in number,
457 p_org_id in number,
458 p_effective_date in date default sysdate,
459 p_salaray_basis_id out nocopy varchar,
460 p_offered_salary out nocopy varchar,
461 p_offered_salary_basis out nocopy varchar
462 );
463
464
465 API_NAME VARCHAR(50) := 'HR_SALARY_WEB.PROCESS_API';
466
467 PACKAGE_NAME VARCHAR2(30) := 'HR_PAY_RATE_SS';
468
469 END hr_pay_rate_ss;