1 PACKAGE PSB_POSITIONS_PVT AS
2 /* $Header: PSBVPOSS.pls 120.8 2005/10/14 16:27:30 matthoma ship $ */
3
4
5 --
6 -- Global Variables for Views
7 --
8 g_worksheet_id NUMBER ;
9 g_start_date DATE ;
10 g_end_date DATE ;
11 g_select_date DATE ;
12 g_worksheet_flag VARCHAR2(1) := 'N';
13 g_distr_percent_total NUMBER; -- 1308558
14
15 --
16 -- Functions/Procedures to intialize and return global values
17 --
18
19 PROCEDURE Initialize_View ( p_worksheet_id in number,
20 p_start_date in date,
21 p_end_date in date,
22 p_select_date in date := fnd_api.g_miss_date);
23
24 PROCEDURE Define_Worksheet_Values (
25 p_api_version in number,
26 p_init_msg_list in varchar2 := fnd_api.g_false,
27 p_commit in varchar2 := fnd_api.g_false,
28 p_validation_level in number := fnd_api.g_valid_level_full,
29 p_return_status OUT NOCOPY varchar2,
30 p_msg_count OUT NOCOPY number,
31 p_msg_data OUT NOCOPY varchar2,
32 p_worksheet_id in number,
33 p_position_id in number,
34 p_pos_effective_start_date in date := FND_API.G_MISS_DATE,
35 p_pos_effective_end_date in date := FND_API.G_MISS_DATE,
36 p_budget_source in varchar2:= FND_API.G_MISS_CHAR,
37 p_out_worksheet_id OUT NOCOPY number,
38 p_out_start_date OUT NOCOPY date,
39 p_out_end_date OUT NOCOPY date);
40
41
42 FUNCTION Get_Start_Date RETURN DATE;
43 pragma RESTRICT_REFERENCES ( Get_START_DATE, WNDS, WNPS );
44
45 FUNCTION Get_End_Date RETURN DATE;
46 pragma RESTRICT_REFERENCES ( Get_END_DATE, WNDS, WNPS );
47
48 FUNCTION Get_Select_Date RETURN DATE;
49 pragma RESTRICT_REFERENCES ( Get_SELECT_DATE, WNDS, WNPS );
50
51 FUNCTION Get_Worksheet_Flag RETURN varchar2;
52 pragma RESTRICT_REFERENCES ( Get_WORKSHEET_FLAG, WNDS, WNPS );
53
54 FUNCTION Get_Worksheet_ID RETURN NUMBER;
55 pragma RESTRICT_REFERENCES ( Get_WORKSHEET_ID, WNDS, WNPS );
56
57 --
58 -- Table Handlers
59 --
60
61 PROCEDURE Insert_Row (
62 p_api_version in number,
63 p_init_msg_list in varchar2 := fnd_api.g_false,
64 p_commit in varchar2 := fnd_api.g_false,
65 p_validation_level in number := fnd_api.g_valid_level_full,
66 p_return_status OUT NOCOPY varchar2,
67 p_msg_count OUT NOCOPY number,
68 p_msg_data OUT NOCOPY varchar2,
69 p_rowid in OUT NOCOPY varchar2,
70 p_position_id in number,
71 -- de by org
72 p_organization_id in number := NULL,
73 p_data_extract_id in number,
74 p_position_definition_id in number,
75 p_hr_position_id in number,
76 p_hr_employee_id in number := fnd_api.g_miss_num ,
77 p_business_group_id in number,
78 p_budget_group_id in number := fnd_api.g_miss_num ,
79 p_effective_start_date in date,
80 p_effective_end_date in date,
81 p_set_of_books_id in number,
82 p_vacant_position_flag in varchar2,
83 p_availability_status in varchar2 := fnd_api.g_miss_char ,
84 p_transaction_id in number := fnd_api.g_miss_num ,
85 p_transaction_status in varchar2 := fnd_api.g_miss_char ,
86 p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
87 p_attribute1 in varchar2,
88 p_attribute2 in varchar2,
89 p_attribute3 in varchar2,
90 p_attribute4 in varchar2,
91 p_attribute5 in varchar2,
92 p_attribute6 in varchar2,
93 p_attribute7 in varchar2,
94 p_attribute8 in varchar2,
95 p_attribute9 in varchar2,
96 p_attribute10 in varchar2,
97 p_attribute11 in varchar2,
98 p_attribute12 in varchar2,
99 p_attribute13 in varchar2,
100 p_attribute14 in varchar2,
101 p_attribute15 in varchar2,
102 p_attribute16 in varchar2,
103 p_attribute17 in varchar2,
104 p_attribute18 in varchar2,
105 p_attribute19 in varchar2,
106 p_attribute20 in varchar2,
107 p_attribute_category in varchar2,
108 p_name in varchar2,
109 p_mode in varchar2 default 'R'
110 );
111 --
112 --
113 --
114
115 PROCEDURE LOCK_ROW (
116 p_api_version in number,
117 p_init_msg_list in varchar2 := fnd_api.g_false,
118 p_commit in varchar2 := fnd_api.g_false,
119 p_validation_level in number := fnd_api.g_valid_level_full,
120 p_return_status OUT NOCOPY varchar2,
121 p_msg_count OUT NOCOPY number,
122 p_msg_data OUT NOCOPY varchar2,
123 p_row_locked OUT NOCOPY varchar2,
124 p_position_id in number,
125 p_data_extract_id in number,
126 p_position_definition_id in number,
127 p_hr_position_id in number,
128 p_business_group_id in number,
129 p_effective_start_date in date,
130 p_effective_end_date in date,
131 p_set_of_books_id in number,
132 p_vacant_position_flag in varchar2,
133 p_attribute1 in varchar2,
134 p_attribute2 in varchar2,
135 p_attribute3 in varchar2,
136 p_attribute4 in varchar2,
137 p_attribute5 in varchar2,
138 p_attribute6 in varchar2,
139 p_attribute7 in varchar2,
140 p_attribute8 in varchar2,
141 p_attribute9 in varchar2,
142 p_attribute10 in varchar2,
143 p_attribute11 in varchar2,
144 p_attribute12 in varchar2,
145 p_attribute13 in varchar2,
146 p_attribute14 in varchar2,
147 p_attribute15 in varchar2,
148 p_attribute16 in varchar2,
149 p_attribute17 in varchar2,
150 p_attribute18 in varchar2,
151 p_attribute19 in varchar2,
152 p_attribute20 in varchar2,
153 p_attribute_category in varchar2,
154 p_name in varchar2
155 );
156
157 --
158 --
159 --
160 PROCEDURE Update_Row (
161 p_api_version in number,
162 p_init_msg_list in varchar2 := fnd_api.g_false,
163 p_commit in varchar2 := fnd_api.g_false,
164 p_validation_level in number := fnd_api.g_valid_level_full,
165 p_return_status OUT NOCOPY varchar2,
166 p_msg_count OUT NOCOPY number,
167 p_msg_data OUT NOCOPY varchar2,
168 p_position_id in number,
169 --de by org
170 p_organization_id in number := NULL,
171 p_data_extract_id in number,
172 p_position_definition_id in number,
173 p_hr_position_id in number,
174 p_hr_employee_id in number := fnd_api.g_miss_num ,
175 p_business_group_id in number,
176 p_budget_group_id in number := fnd_api.g_miss_num ,
177 p_effective_start_date in date,
178 p_effective_end_date in date,
179 p_set_of_books_id in number,
180 p_vacant_position_flag in varchar2,
181 p_availability_status in varchar2 := fnd_api.g_miss_char ,
182 p_transaction_id in number := fnd_api.g_miss_num ,
183 p_transaction_status in varchar2 := fnd_api.g_miss_char ,
184 p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
185 p_attribute1 in varchar2,
186 p_attribute2 in varchar2,
187 p_attribute3 in varchar2,
188 p_attribute4 in varchar2,
189 p_attribute5 in varchar2,
190 p_attribute6 in varchar2,
191 p_attribute7 in varchar2,
192 p_attribute8 in varchar2,
193 p_attribute9 in varchar2,
194 p_attribute10 in varchar2,
195 p_attribute11 in varchar2,
196 p_attribute12 in varchar2,
197 p_attribute13 in varchar2,
198 p_attribute14 in varchar2,
199 p_attribute15 in varchar2,
200 p_attribute16 in varchar2,
201 p_attribute17 in varchar2,
202 p_attribute18 in varchar2,
203 p_attribute19 in varchar2,
204 p_attribute20 in varchar2,
205 p_attribute_category in varchar2,
206 p_name in varchar2,
207 p_mode in varchar2 default 'R'
208
209 );
210 --
211 --
212 --
213 PROCEDURE ADD_ROW (
214 p_api_version in number,
215 p_init_msg_list in varchar2 := fnd_api.g_false,
216 p_commit in varchar2 := fnd_api.g_false,
217 p_validation_level in number := fnd_api.g_valid_level_full,
218 p_return_status OUT NOCOPY varchar2,
219 p_msg_count OUT NOCOPY number,
220 p_msg_data OUT NOCOPY varchar2,
221 p_rowid in OUT NOCOPY varchar2,
222 p_position_id in number,
223 p_organization_id in number,
224 p_data_extract_id in number,
225 p_position_definition_id in number,
226 p_hr_position_id in number,
227 p_business_group_id in number,
228 p_effective_start_date in date,
229 p_effective_end_date in date,
230 p_set_of_books_id in number,
231 p_vacant_position_flag in varchar2,
232 p_attribute1 in varchar2,
233 p_attribute2 in varchar2,
234 p_attribute3 in varchar2,
235 p_attribute4 in varchar2,
236 p_attribute5 in varchar2,
237 p_attribute6 in varchar2,
238 p_attribute7 in varchar2,
239 p_attribute8 in varchar2,
240 p_attribute9 in varchar2,
241 p_attribute10 in varchar2,
242 p_attribute11 in varchar2,
243 p_attribute12 in varchar2,
244 p_attribute13 in varchar2,
245 p_attribute14 in varchar2,
246 p_attribute15 in varchar2,
247 p_attribute16 in varchar2,
248 p_attribute17 in varchar2,
249 p_attribute18 in varchar2,
250 p_attribute19 in varchar2,
251 p_attribute20 in varchar2,
252 p_attribute_category in varchar2,
253 p_name in varchar2,
254 p_mode in varchar2 default 'R'
255
256 );
257 --
258 --
259 --
260 PROCEDURE Delete_Row (
261 p_api_version in number,
262 p_init_msg_list in varchar2 := fnd_api.g_false,
263 p_commit in varchar2 := fnd_api.g_false,
264 p_validation_level in number := fnd_api.g_valid_level_full,
265 p_return_status OUT NOCOPY varchar2,
266 p_msg_count OUT NOCOPY number,
267 p_msg_data OUT NOCOPY varchar2,
268 p_position_id in number
269 );
270 --
271 PROCEDURE Delete_Assignments (
272 p_api_version in number,
273 p_init_msg_list in varchar2 := fnd_api.g_false,
274 p_commit in varchar2 := fnd_api.g_false,
275 p_validation_level in number := fnd_api.g_valid_level_full,
276 p_return_status OUT NOCOPY varchar2,
277 p_msg_count OUT NOCOPY number,
278 p_msg_data OUT NOCOPY varchar2,
279 p_worksheet_id in number
280 );
281 --
282 --
283 PROCEDURE Delete_Assignment_Employees (
284 p_api_version in number,
285 p_init_msg_list in varchar2 := fnd_api.g_false,
286 p_commit in varchar2 := fnd_api.g_false,
287 p_validation_level in number := fnd_api.g_valid_level_full,
288 p_return_status OUT NOCOPY varchar2,
289 p_msg_count OUT NOCOPY number,
290 p_msg_data OUT NOCOPY varchar2,
291 p_data_extract_id in number
292 );
293 --
294 -- Modify_assignment used for insert/modify assignments
295 --
296 PROCEDURE Modify_Assignment
297 ( p_api_version in number,
298 p_init_msg_list in varchar2 := fnd_api.g_false,
299 p_commit in varchar2 := fnd_api.g_false,
300 p_validation_level in number := fnd_api.g_valid_level_full,
301 p_return_status OUT NOCOPY varchar2,
302 p_msg_count OUT NOCOPY number,
303 p_msg_data OUT NOCOPY varchar2,
304 p_position_assignment_id in OUT NOCOPY number,
305 p_data_extract_id in number,
306 p_worksheet_id in number,
307 p_position_id in number,
308 p_assignment_type in varchar2,
309 p_attribute_id in number,
310 p_attribute_value_id in number,
311 p_attribute_value in varchar2,
312 p_pay_element_id in number,
313 p_pay_element_option_id in number,
314 p_effective_start_date in date,
315 p_effective_end_date in date,
316 p_element_value_type in varchar2,
317 p_element_value in number,
318 p_currency_code in varchar2,
319 p_pay_basis in varchar2,
320 p_employee_id in number,
321 p_primary_employee_flag in varchar2,
322 p_global_default_flag in varchar2,
323 p_assignment_default_rule_id in number,
324 p_modify_flag in varchar2,
325 p_rowid in OUT NOCOPY varchar2,
326 p_mode in varchar2 default 'R'
327 );
328 --
329 --
330 --
331 PROCEDURE Validate_Salary
332 ( p_api_version in number,
333 p_init_msg_list in varchar2 := fnd_api.g_false,
334 p_commit in varchar2 := fnd_api.g_false,
335 p_validation_level in number := fnd_api.g_valid_level_full,
336 p_return_status OUT NOCOPY varchar2,
337 p_msg_count OUT NOCOPY number,
338 p_msg_data OUT NOCOPY varchar2,
339 p_worksheet_id in number,
340 p_position_id in number,
341 p_effective_start_date in date,
342 p_effective_end_date in date,
343 p_pay_element_id in number,
344 p_data_extract_id in number,
345 p_rowid in varchar2
346 );
347
348 --added the parameter p_validation_mode as part of bug fix 3247574
349
350 PROCEDURE Position_WS_Validation
351 ( p_api_version in number,
352 p_init_msg_list in varchar2 := fnd_api.g_false,
353 p_commit in varchar2 := fnd_api.g_false,
354 p_validation_level in number := fnd_api.g_valid_level_full,
355 p_return_status OUT NOCOPY varchar2,
356 p_msg_count OUT NOCOPY number,
357 p_msg_data OUT NOCOPY varchar2,
358 p_worksheet_id in number ,
359 p_validation_status OUT NOCOPY varchar2,
360 p_validation_mode IN VARCHAR2 DEFAULT NULL
361 );
362 --
363 --
364 -- 1308558 Mass Position Assignment Rules Enhancement
365 -- added the extra parameter p_ruleset_id for passing the
366 -- id for the default ruleset
367 PROCEDURE Create_Default_Assignments
368 ( p_api_version IN NUMBER,
369 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
370 p_commit IN VARCHAR2 := FND_API.G_FALSE,
371 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
372 p_return_status OUT NOCOPY VARCHAR2,
373 p_msg_count OUT NOCOPY NUMBER,
374 p_msg_data OUT NOCOPY VARCHAR2,
375 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
376 p_data_extract_id IN NUMBER,
377 p_position_id IN NUMBER := FND_API.G_MISS_NUM,
378 p_position_start_date IN DATE := FND_API.G_MISS_DATE,
379 p_position_end_date IN DATE := FND_API.G_MISS_DATE,
380 p_ruleset_id IN NUMBER := NULL
381 );
382 --
383 --
384 --
385 FUNCTION Rev_Check_Allowed
386 ( p_api_version IN NUMBER,
387 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
388 p_startdate_pp IN DATE,
389 p_enddate_cy IN DATE,
390 p_worksheet_id IN NUMBER,
391 p_position_budget_group_id IN NUMBER
392 ) RETURN VARCHAR2;
393
394
395
396 FUNCTION get_debug RETURN VARCHAR2;
397
398 /* Start Bug 3422919 */
399
400 FUNCTION get_employee_id
401 ( p_data_extract_id IN NUMBER := NULL,
402 p_worksheet_id IN NUMBER := NULL,
403 p_position_id IN NUMBER := NULL
404 ) RETURN NUMBER;
405 PRAGMA RESTRICT_REFERENCES(get_employee_id,WNDS,WNPS);
406
407 FUNCTION get_employee_number
408 ( p_data_extract_id IN NUMBER := NULL,
412 PRAGMA RESTRICT_REFERENCES(get_employee_number,WNDS,WNPS);
409 p_worksheet_id IN NUMBER := NULL,
410 p_position_id IN NUMBER := NULL
411 ) RETURN VARCHAR2;
413
414 FUNCTION get_employee_name
415 ( p_data_extract_id IN NUMBER := NULL,
416 p_worksheet_id IN NUMBER := NULL,
417 p_position_id IN NUMBER := NULL
418 ) RETURN VARCHAR2;
419 PRAGMA RESTRICT_REFERENCES(get_employee_name,WNDS,WNPS);
420
421 FUNCTION get_job_name
422 ( p_data_extract_id IN NUMBER := NULL,
423 p_worksheet_id IN NUMBER := NULL,
424 p_position_id IN NUMBER := NULL
425 ) RETURN VARCHAR2;
426 PRAGMA RESTRICT_REFERENCES(get_job_name,WNDS,WNPS);
427
428 /* End Bug 3422919 */
429
430 /* Bug 4273099 made the following api public */
431 -- Bug 4545909 added the parameter p_worksheet_id
432 PROCEDURE Apply_Position_Default_Rules
433 ( p_api_version IN NUMBER,
434 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
435 p_commit IN VARCHAR2 := FND_API.G_FALSE,
436 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_count OUT NOCOPY NUMBER,
439 x_msg_data OUT NOCOPY VARCHAR2,
440 p_position_assignment_id IN OUT NOCOPY NUMBER,
441 p_data_extract_id IN NUMBER,
442 p_position_id IN NUMBER,
443 p_assignment_type IN VARCHAR2,
444 p_attribute_id IN NUMBER,
445 p_attribute_value_id IN NUMBER,
446 p_attribute_value IN VARCHAR2,
447 p_pay_element_id IN NUMBER,
448 p_pay_element_option_id IN NUMBER,
449 p_effective_start_date IN DATE,
450 p_effective_end_date IN DATE,
451 p_element_value_type IN VARCHAR2,
452 p_element_value IN NUMBER,
453 p_currency_code IN VARCHAR2,
454 p_pay_basis IN VARCHAR2,
455 p_employee_id IN NUMBER,
456 p_primary_employee_flag IN VARCHAR2,
457 p_global_default_flag IN VARCHAR2,
458 p_assignment_default_rule_id IN NUMBER,
459 p_modify_flag IN VARCHAR2,
460 p_mode IN VARCHAR2 := 'R' ,
461 p_worksheet_id IN NUMBER DEFAULT NULL
462 );
463
464 END PSB_POSITIONS_PVT ;
[Home] [Help]