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