[Home] [Help]
PACKAGE BODY: APPS.HR_AU_ASSIGNMENT_API
Source
1 PACKAGE BODY hr_au_assignment_api AS
2 /* $Header: hrauwrsa.pkb 120.1 2011/12/20 11:23:50 jmarupil ship $ */
3 /*
4 +==========================================================================================
5 | Copyright (c) 1999 Oracle Corporation Ltd
6 | All rights reserved.
7 +==========================================================================================
8 |SQL Script File Name : HR AU WR AS . PKB
9 | Name : hr_au_assignment_api
10 | Description : Assignment API Wrapper for AU
11 |
12 | Name Date Version Bug Text
13 | -------------- ---------- ------- ----- ----
14 | sgoggin 11-JUN-1999 110.0 Created for AU
15 | atopol 01-OCT-1999 115.0 Upgraded
16 | sclarke 09-FEB-2000 115.1 1177545 Added Group Certificate Issue date segment
17 | makelly 28-MAR-2000 115.2 Added extra params for overloading in core api
18 | rsinghal 23-JAN-2001 115.3 1560081 added hours worked per week segment
19 | apunekar 02-DEC-2002 115.5 2689173 Added Nocopy to out and in out parameters
20 |NOTES
21 +==========================================================================================
22 */
23
24 g_package VARCHAR2(33) := 'hr_au_assignment_api.';
25 --
26 -- ----------------------------------------------------------------------------
27 -- |---------------------< create_au_secondary_emp_asg >-----------------------|
28 -- ----------------------------------------------------------------------------
29 --
30 PROCEDURE create_au_secondary_emp_asg
31 (p_validate IN BOOLEAN DEFAULT FALSE
32 ,p_effective_date IN DATE
33 ,p_person_id IN NUMBER
34 ,p_organization_id IN NUMBER
35 ,p_grade_id IN NUMBER DEFAULT NULL
36 ,p_position_id IN NUMBER DEFAULT NULL
37 ,p_job_id IN NUMBER DEFAULT NULL
38 ,p_assignment_status_type_id IN NUMBER DEFAULT NULL
39 ,p_payroll_id IN NUMBER DEFAULT NULL
40 ,p_location_id IN NUMBER DEFAULT NULL
41 ,p_supervisor_id IN NUMBER DEFAULT NULL
42 ,p_special_ceiling_step_id IN NUMBER DEFAULT NULL
43 ,p_pay_basis_id IN NUMBER DEFAULT NULL
44 ,p_assignment_number IN OUT NOCOPY VARCHAR2
45 ,p_change_reason IN VARCHAR2 DEFAULT NULL
46 ,p_comments IN hr_comments.comment_text%TYPE DEFAULT NULL
47 ,p_date_probation_end IN DATE DEFAULT NULL
48 ,p_default_code_comb_id IN NUMBER DEFAULT NULL
49 ,p_employment_category IN VARCHAR2 DEFAULT NULL
50 ,p_frequency IN VARCHAR2 DEFAULT NULL
51 ,p_internal_address_line IN VARCHAR2 DEFAULT NULL
52 ,p_manager_flag IN VARCHAR2 DEFAULT NULL
53 ,p_normal_hours IN NUMBER DEFAULT NULL
54 ,p_perf_review_period IN NUMBER DEFAULT NULL
55 ,p_perf_review_period_frequency IN VARCHAR2 DEFAULT NULL
56 ,p_probation_period IN NUMBER DEFAULT NULL
57 ,p_probation_unit IN VARCHAR2 DEFAULT NULL
58 ,p_sal_review_period IN NUMBER DEFAULT NULL
59 ,p_sal_review_period_frequency IN VARCHAR2 DEFAULT NULL
60 ,p_set_of_books_id IN NUMBER DEFAULT NULL
61 ,p_source_type IN VARCHAR2 DEFAULT NULL
62 ,p_time_normal_finish IN VARCHAR2 DEFAULT NULL
63 ,p_time_normal_start IN VARCHAR2 DEFAULT NULL
64 ,p_bargaining_unit_code IN VARCHAR2 DEFAULT NULL
65 ,p_labour_union_member_flag in varchar2 default 'N'
66 ,p_hourly_salaried_code IN VARCHAR2 DEFAULT NULL
67 ,p_ass_attribute_category IN VARCHAR2 DEFAULT NULL
68 ,p_ass_attribute1 IN VARCHAR2 DEFAULT NULL
69 ,p_ass_attribute2 IN VARCHAR2 DEFAULT NULL
70 ,p_ass_attribute3 IN VARCHAR2 DEFAULT NULL
71 ,p_ass_attribute4 IN VARCHAR2 DEFAULT NULL
72 ,p_ass_attribute5 IN VARCHAR2 DEFAULT NULL
73 ,p_ass_attribute6 IN VARCHAR2 DEFAULT NULL
74 ,p_ass_attribute7 IN VARCHAR2 DEFAULT NULL
75 ,p_ass_attribute8 IN VARCHAR2 DEFAULT NULL
76 ,p_ass_attribute9 IN VARCHAR2 DEFAULT NULL
77 ,p_ass_attribute10 IN VARCHAR2 DEFAULT NULL
78 ,p_ass_attribute11 IN VARCHAR2 DEFAULT NULL
79 ,p_ass_attribute12 IN VARCHAR2 DEFAULT NULL
80 ,p_ass_attribute13 IN VARCHAR2 DEFAULT NULL
81 ,p_ass_attribute14 IN VARCHAR2 DEFAULT NULL
82 ,p_ass_attribute15 IN VARCHAR2 DEFAULT NULL
83 ,p_ass_attribute16 IN VARCHAR2 DEFAULT NULL
84 ,p_ass_attribute17 IN VARCHAR2 DEFAULT NULL
85 ,p_ass_attribute18 IN VARCHAR2 DEFAULT NULL
86 ,p_ass_attribute19 IN VARCHAR2 DEFAULT NULL
87 ,p_ass_attribute20 IN VARCHAR2 DEFAULT NULL
88 ,p_ass_attribute21 IN VARCHAR2 DEFAULT NULL
89 ,p_ass_attribute22 IN VARCHAR2 DEFAULT NULL
90 ,p_ass_attribute23 IN VARCHAR2 DEFAULT NULL
91 ,p_ass_attribute24 IN VARCHAR2 DEFAULT NULL
92 ,p_ass_attribute25 IN VARCHAR2 DEFAULT NULL
93 ,p_ass_attribute26 IN VARCHAR2 DEFAULT NULL
94 ,p_ass_attribute27 IN VARCHAR2 DEFAULT NULL
95 ,p_ass_attribute28 IN VARCHAR2 DEFAULT NULL
96 ,p_ass_attribute29 IN VARCHAR2 DEFAULT NULL
97 ,p_ass_attribute30 IN VARCHAR2 DEFAULT NULL
98 ,p_title IN VARCHAR2 DEFAULT NULL
99 ,p_legal_employer_id IN VARCHAR2 DEFAULT NULL
100 ,p_lev_lod_flg IN VARCHAR2 DEFAULT NULL
101 ,p_group_cert_issue_date IN VARCHAR2 DEFAULT NULL
102 ,p_hrs_worked_per_week IN VARCHAR2 DEFAULT NULL
103 ,p_pgp_segment1 IN VARCHAR2 DEFAULT NULL
104 ,p_pgp_segment2 IN VARCHAR2 DEFAULT NULL
105 ,p_pgp_segment3 IN VARCHAR2 DEFAULT NULL
106 ,p_pgp_segment4 IN VARCHAR2 DEFAULT NULL
107 ,p_pgp_segment5 IN VARCHAR2 DEFAULT NULL
108 ,p_pgp_segment6 IN VARCHAR2 DEFAULT NULL
109 ,p_pgp_segment7 IN VARCHAR2 DEFAULT NULL
110 ,p_pgp_segment8 IN VARCHAR2 DEFAULT NULL
111 ,p_pgp_segment9 IN VARCHAR2 DEFAULT NULL
112 ,p_pgp_segment10 IN VARCHAR2 DEFAULT NULL
113 ,p_pgp_segment11 IN VARCHAR2 DEFAULT NULL
114 ,p_pgp_segment12 IN VARCHAR2 DEFAULT NULL
115 ,p_pgp_segment13 IN VARCHAR2 DEFAULT NULL
116 ,p_pgp_segment14 IN VARCHAR2 DEFAULT NULL
117 ,p_pgp_segment15 IN VARCHAR2 DEFAULT NULL
118 ,p_pgp_segment16 IN VARCHAR2 DEFAULT NULL
119 ,p_pgp_segment17 IN VARCHAR2 DEFAULT NULL
120 ,p_pgp_segment18 IN VARCHAR2 DEFAULT NULL
121 ,p_pgp_segment19 IN VARCHAR2 DEFAULT NULL
122 ,p_pgp_segment20 IN VARCHAR2 DEFAULT NULL
123 ,p_pgp_segment21 IN VARCHAR2 DEFAULT NULL
124 ,p_pgp_segment22 IN VARCHAR2 DEFAULT NULL
125 ,p_pgp_segment23 IN VARCHAR2 DEFAULT NULL
126 ,p_pgp_segment24 IN VARCHAR2 DEFAULT NULL
127 ,p_pgp_segment25 IN VARCHAR2 DEFAULT NULL
128 ,p_pgp_segment26 IN VARCHAR2 DEFAULT NULL
129 ,p_pgp_segment27 IN VARCHAR2 DEFAULT NULL
130 ,p_pgp_segment28 IN VARCHAR2 DEFAULT NULL
131 ,p_pgp_segment29 IN VARCHAR2 DEFAULT NULL
132 ,p_pgp_segment30 IN VARCHAR2 DEFAULT NULL
133 ,p_pgp_concat_segments IN VARCHAR2 DEFAULT NULL
134 ,p_contract_id IN NUMBER DEFAULT NULL
135 ,p_establishment_id IN NUMBER DEFAULT NULL
136 ,p_collective_agreement_id IN NUMBER DEFAULT NULL
137 ,p_cagr_id_flex_num IN NUMBER DEFAULT NULL
138 ,p_cag_segment1 IN VARCHAR2 DEFAULT NULL
139 ,p_cag_segment2 IN VARCHAR2 DEFAULT NULL
140 ,p_cag_segment3 IN VARCHAR2 DEFAULT NULL
141 ,p_cag_segment4 IN VARCHAR2 DEFAULT NULL
142 ,p_cag_segment5 IN VARCHAR2 DEFAULT NULL
143 ,p_cag_segment6 IN VARCHAR2 DEFAULT NULL
144 ,p_cag_segment7 IN VARCHAR2 DEFAULT NULL
145 ,p_cag_segment8 IN VARCHAR2 DEFAULT NULL
146 ,p_cag_segment9 IN VARCHAR2 DEFAULT NULL
147 ,p_cag_segment10 IN VARCHAR2 DEFAULT NULL
148 ,p_cag_segment11 IN VARCHAR2 DEFAULT NULL
149 ,p_cag_segment12 IN VARCHAR2 DEFAULT NULL
150 ,p_cag_segment13 IN VARCHAR2 DEFAULT NULL
151 ,p_cag_segment14 IN VARCHAR2 DEFAULT NULL
152 ,p_cag_segment15 IN VARCHAR2 DEFAULT NULL
153 ,p_cag_segment16 IN VARCHAR2 DEFAULT NULL
154 ,p_cag_segment17 IN VARCHAR2 DEFAULT NULL
155 ,p_cag_segment18 IN VARCHAR2 DEFAULT NULL
156 ,p_cag_segment19 IN VARCHAR2 DEFAULT NULL
157 ,p_cag_segment20 IN VARCHAR2 DEFAULT NULL
158 ,p_cagr_grade_def_id OUT NOCOPY NUMBER
159 ,p_cagr_concatenated_segments OUT NOCOPY VARCHAR2
160 ,p_assignment_id OUT NOCOPY NUMBER
161 ,p_soft_coding_keyflex_id OUT NOCOPY NUMBER
162 ,p_people_group_id OUT NOCOPY NUMBER
163 ,p_object_version_number OUT NOCOPY NUMBER
164 ,p_effective_start_date OUT NOCOPY DATE
165 ,p_effective_end_date OUT NOCOPY DATE
166 ,p_assignment_sequence OUT NOCOPY NUMBER
167 ,p_comment_id OUT NOCOPY NUMBER
168 ,p_concatenated_segments OUT NOCOPY VARCHAR2
169 ,p_group_name OUT NOCOPY VARCHAR2
170 ,p_other_manager_warning OUT NOCOPY BOOLEAN
171 ) IS
172 --
173 -- Declare cursors AND local variables
174 --
175 -- Declare variables
176 --
177 l_assignment_number per_assignments_f.assignment_number%TYPE;
178 l_effective_date DATE;
179 --
180 l_business_group_id per_business_groups.business_group_id%TYPE;
181 l_legislation_code per_business_groups.legislation_code%TYPE;
182 l_proc VARCHAR2(72) := g_package||'create_au_secondary_emp_asg';
183 --
184 -- Declare cursors
185 --
186 CURSOR csr_legislation IS
187 SELECT NULL
188 FROM per_assignments_f paf,
189 per_business_groups pbg
190 WHERE paf.person_id = p_person_id
191 AND l_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
192 AND pbg.business_group_id = paf.business_group_id
193 AND pbg.legislation_code = 'AU';
194 --
195 --
196 BEGIN
197 hr_utility.set_location('Entering:'|| l_proc, 10);
198 --
199 -- Initialise local variable
200 --
201 l_effective_date := trunc(p_effective_date);
202 --
203 -- Validation IN addition to Table Handlers
204 --
205 -- Ensure that the employee IS within a au business group
206 --
207 OPEN csr_legislation;
208 FETCH csr_legislation
209 INTO l_legislation_code;
210 IF csr_legislation%notfound THEN
211 CLOSE csr_legislation;
212 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
213 hr_utility.set_message_token('LEG_CODE', 'AU');
214 hr_utility.raise_error;
215 END IF;
216 CLOSE csr_legislation;
217 --
218 --
219 -- Call create_secondary_emp_asg
220 --
221 hr_assignment_api.create_secondary_emp_asg
222 (p_validate => p_validate
223 ,p_effective_date => l_effective_date
224 ,p_person_id => p_person_id
225 ,p_organization_id => p_organization_id
226 ,p_grade_id => p_grade_id
227 ,p_position_id => p_position_id
228 ,p_job_id => p_job_id
229 ,p_assignment_status_type_id => p_assignment_status_type_id
230 ,p_payroll_id => p_payroll_id
231 ,p_location_id => p_location_id
232 ,p_supervisor_id => p_supervisor_id
233 ,p_special_ceiling_step_id => p_special_ceiling_step_id
234 ,p_pay_basis_id => p_pay_basis_id
235 ,p_assignment_number => l_assignment_number
236 ,p_change_reason => p_change_reason
237 ,p_comments => p_comments
238 ,p_date_probation_end => trunc(p_date_probation_end)
239 ,p_default_code_comb_id => p_default_code_comb_id
240 ,p_employment_category => p_employment_category
241 ,p_frequency => p_frequency
242 ,p_internal_address_line => p_internal_address_line
243 ,p_manager_flag => p_manager_flag
244 ,p_normal_hours => p_normal_hours
245 ,p_perf_review_period => p_perf_review_period
246 ,p_perf_review_period_frequency => p_perf_review_period_frequency
247 ,p_probation_period => p_probation_period
248 ,p_probation_unit => p_probation_unit
249 ,p_sal_review_period => p_sal_review_period
250 ,p_sal_review_period_frequency => p_sal_review_period_frequency
251 ,p_set_of_books_id => p_set_of_books_id
252 ,p_source_type => p_source_type
253 ,p_time_normal_finish => p_time_normal_finish
254 ,p_time_normal_start => p_time_normal_start
255 ,p_bargaining_unit_code => p_bargaining_unit_code
256 ,p_labour_union_member_flag => p_labour_union_member_flag
257 ,p_hourly_salaried_code => p_hourly_salaried_code
258 ,p_ass_attribute_category => p_ass_attribute_category
259 ,p_ass_attribute1 => p_ass_attribute1
260 ,p_ass_attribute2 => p_ass_attribute2
261 ,p_ass_attribute3 => p_ass_attribute3
262 ,p_ass_attribute4 => p_ass_attribute4
263 ,p_ass_attribute5 => p_ass_attribute5
264 ,p_ass_attribute6 => p_ass_attribute6
265 ,p_ass_attribute7 => p_ass_attribute7
266 ,p_ass_attribute8 => p_ass_attribute8
267 ,p_ass_attribute9 => p_ass_attribute9
268 ,p_ass_attribute10 => p_ass_attribute10
269 ,p_ass_attribute11 => p_ass_attribute11
270 ,p_ass_attribute12 => p_ass_attribute12
271 ,p_ass_attribute13 => p_ass_attribute13
272 ,p_ass_attribute14 => p_ass_attribute14
273 ,p_ass_attribute15 => p_ass_attribute15
274 ,p_ass_attribute16 => p_ass_attribute16
275 ,p_ass_attribute17 => p_ass_attribute17
276 ,p_ass_attribute18 => p_ass_attribute18
277 ,p_ass_attribute19 => p_ass_attribute19
278 ,p_ass_attribute20 => p_ass_attribute20
279 ,p_ass_attribute21 => p_ass_attribute21
280 ,p_ass_attribute22 => p_ass_attribute22
281 ,p_ass_attribute23 => p_ass_attribute23
282 ,p_ass_attribute24 => p_ass_attribute24
283 ,p_ass_attribute25 => p_ass_attribute25
284 ,p_ass_attribute26 => p_ass_attribute26
285 ,p_ass_attribute27 => p_ass_attribute27
286 ,p_ass_attribute28 => p_ass_attribute28
287 ,p_ass_attribute29 => p_ass_attribute29
288 ,p_ass_attribute30 => p_ass_attribute30
289 ,p_title => p_title
290 ,p_scl_segment1 => p_legal_employer_id
291 ,p_scl_segment2 => p_lev_lod_flg
292 ,p_scl_segment3 => p_group_cert_issue_date
293 ,p_scl_segment4 => p_hrs_worked_per_week
294 ,p_pgp_segment1 => p_pgp_segment1
295 ,p_pgp_segment2 => p_pgp_segment2
296 ,p_pgp_segment3 => p_pgp_segment3
297 ,p_pgp_segment4 => p_pgp_segment4
298 ,p_pgp_segment5 => p_pgp_segment5
299 ,p_pgp_segment6 => p_pgp_segment6
300 ,p_pgp_segment7 => p_pgp_segment7
301 ,p_pgp_segment8 => p_pgp_segment8
302 ,p_pgp_segment9 => p_pgp_segment9
303 ,p_pgp_segment10 => p_pgp_segment10
304 ,p_pgp_segment11 => p_pgp_segment11
305 ,p_pgp_segment12 => p_pgp_segment12
306 ,p_pgp_segment13 => p_pgp_segment13
307 ,p_pgp_segment14 => p_pgp_segment14
308 ,p_pgp_segment15 => p_pgp_segment15
309 ,p_pgp_segment16 => p_pgp_segment16
310 ,p_pgp_segment17 => p_pgp_segment17
311 ,p_pgp_segment18 => p_pgp_segment18
312 ,p_pgp_segment19 => p_pgp_segment19
313 ,p_pgp_segment20 => p_pgp_segment20
314 ,p_pgp_segment21 => p_pgp_segment21
315 ,p_pgp_segment22 => p_pgp_segment22
316 ,p_pgp_segment23 => p_pgp_segment23
317 ,p_pgp_segment24 => p_pgp_segment24
318 ,p_pgp_segment25 => p_pgp_segment25
319 ,p_pgp_segment26 => p_pgp_segment26
320 ,p_pgp_segment27 => p_pgp_segment27
321 ,p_pgp_segment28 => p_pgp_segment28
322 ,p_pgp_segment29 => p_pgp_segment29
323 ,p_pgp_segment30 => p_pgp_segment30
324 ,p_pgp_concat_segments => p_pgp_concat_segments
325 ,p_contract_id => p_contract_id
326 ,p_establishment_id => p_establishment_id
327 ,p_collective_agreement_id => p_collective_agreement_id
328 ,p_cagr_id_flex_num => p_cagr_id_flex_num
329 ,p_cag_segment1 => p_cag_segment1
330 ,p_cag_segment2 => p_cag_segment2
331 ,p_cag_segment3 => p_cag_segment3
332 ,p_cag_segment4 => p_cag_segment4
333 ,p_cag_segment5 => p_cag_segment5
334 ,p_cag_segment6 => p_cag_segment6
335 ,p_cag_segment7 => p_cag_segment7
336 ,p_cag_segment8 => p_cag_segment8
337 ,p_cag_segment9 => p_cag_segment9
338 ,p_cag_segment10 => p_cag_segment10
339 ,p_cag_segment11 => p_cag_segment11
340 ,p_cag_segment12 => p_cag_segment12
341 ,p_cag_segment13 => p_cag_segment13
342 ,p_cag_segment14 => p_cag_segment14
343 ,p_cag_segment15 => p_cag_segment15
344 ,p_cag_segment16 => p_cag_segment16
345 ,p_cag_segment17 => p_cag_segment17
346 ,p_cag_segment18 => p_cag_segment18
347 ,p_cag_segment19 => p_cag_segment19
348 ,p_cag_segment20 => p_cag_segment20
349 ,p_cagr_grade_def_id => p_cagr_grade_def_id
350 ,p_cagr_concatenated_segments => p_cagr_concatenated_segments
351 ,p_assignment_id => p_assignment_id
352 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
353 ,p_people_group_id => p_people_group_id
354 ,p_object_version_number => p_object_version_number
355 ,p_effective_start_date => p_effective_start_date
356 ,p_effective_end_date => p_effective_end_date
357 ,p_assignment_sequence => p_assignment_sequence
358 ,p_comment_id => p_comment_id
359 ,p_concatenated_segments => p_concatenated_segments
360 ,p_group_name => p_group_name
361 ,p_other_manager_warning => p_other_manager_warning
362 );
363 --
364 hr_utility.set_location(' Leaving:'||l_proc, 30);
365 --
366 END create_au_secondary_emp_asg;
367
368 -- ----------------------------------------------------------------------------
369 -- |--------------------------< update_au_emp_asg >---------------------------|
370 -- ----------------------------------------------------------------------------
371 --
372 PROCEDURE update_au_emp_asg
373 (p_validate IN BOOLEAN DEFAULT FALSE
374 ,p_effective_date IN DATE
375 ,p_datetrack_update_mode IN VARCHAR2
376 ,p_assignment_id IN NUMBER
377 ,p_object_version_number IN OUT NOCOPY NUMBER
378 ,p_supervisor_id IN NUMBER DEFAULT hr_api.g_number
379 ,p_assignment_number IN VARCHAR2 DEFAULT hr_api.g_varchar2
380 ,p_change_reason IN VARCHAR2 DEFAULT hr_api.g_varchar2
381 ,p_comments IN hr_comments.comment_text%TYPE DEFAULT hr_api.g_varchar2
382 ,p_date_probation_end IN DATE DEFAULT hr_api.g_date
383 ,p_default_code_comb_id IN NUMBER DEFAULT hr_api.g_number
384 ,p_frequency IN VARCHAR2 DEFAULT hr_api.g_varchar2
385 ,p_internal_address_line IN VARCHAR2 DEFAULT hr_api.g_varchar2
386 ,p_manager_flag IN VARCHAR2 DEFAULT hr_api.g_varchar2
387 ,p_normal_hours IN NUMBER DEFAULT hr_api.g_number
388 ,p_perf_review_period IN NUMBER DEFAULT hr_api.g_number
389 ,p_perf_review_period_frequency IN VARCHAR2 DEFAULT hr_api.g_varchar2
390 ,p_probation_period IN NUMBER DEFAULT hr_api.g_number
391 ,p_probation_unit IN VARCHAR2 DEFAULT hr_api.g_varchar2
392 ,p_sal_review_period IN NUMBER DEFAULT hr_api.g_number
393 ,p_sal_review_period_frequency IN VARCHAR2 DEFAULT hr_api.g_varchar2
394 ,p_set_of_books_id IN NUMBER DEFAULT hr_api.g_number
395 ,p_source_type IN VARCHAR2 DEFAULT hr_api.g_varchar2
396 ,p_time_normal_finish IN VARCHAR2 DEFAULT hr_api.g_varchar2
397 ,p_time_normal_start IN VARCHAR2 DEFAULT hr_api.g_varchar2
398 ,p_bargaining_unit_code IN VARCHAR2 DEFAULT hr_api.g_varchar2
399 ,p_labour_union_member_flag IN VARCHAR2 DEFAULT hr_api.g_varchar2
400 ,p_hourly_salaried_code IN VARCHAR2 DEFAULT hr_api.g_varchar2
401 ,p_ass_attribute_category IN VARCHAR2 DEFAULT hr_api.g_varchar2
402 ,p_ass_attribute1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
403 ,p_ass_attribute2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
404 ,p_ass_attribute3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
405 ,p_ass_attribute4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
406 ,p_ass_attribute5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
407 ,p_ass_attribute6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
408 ,p_ass_attribute7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
409 ,p_ass_attribute8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
410 ,p_ass_attribute9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
411 ,p_ass_attribute10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
412 ,p_ass_attribute11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
413 ,p_ass_attribute12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
414 ,p_ass_attribute13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
415 ,p_ass_attribute14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
416 ,p_ass_attribute15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
417 ,p_ass_attribute16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
418 ,p_ass_attribute17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
419 ,p_ass_attribute18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
420 ,p_ass_attribute19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
421 ,p_ass_attribute20 IN VARCHAR2 DEFAULT hr_api.g_varchar2
422 ,p_ass_attribute21 IN VARCHAR2 DEFAULT hr_api.g_varchar2
423 ,p_ass_attribute22 IN VARCHAR2 DEFAULT hr_api.g_varchar2
424 ,p_ass_attribute23 IN VARCHAR2 DEFAULT hr_api.g_varchar2
425 ,p_ass_attribute24 IN VARCHAR2 DEFAULT hr_api.g_varchar2
426 ,p_ass_attribute25 IN VARCHAR2 DEFAULT hr_api.g_varchar2
427 ,p_ass_attribute26 IN VARCHAR2 DEFAULT hr_api.g_varchar2
428 ,p_ass_attribute27 IN VARCHAR2 DEFAULT hr_api.g_varchar2
429 ,p_ass_attribute28 IN VARCHAR2 DEFAULT hr_api.g_varchar2
430 ,p_ass_attribute29 IN VARCHAR2 DEFAULT hr_api.g_varchar2
431 ,p_ass_attribute30 IN VARCHAR2 DEFAULT hr_api.g_varchar2
432 ,p_title IN VARCHAR2 DEFAULT hr_api.g_varchar2
433 ,p_legal_employer_id IN VARCHAR2 DEFAULT hr_api.g_varchar2
434 ,p_lev_lod_flg IN VARCHAR2 DEFAULT hr_api.g_varchar2
435 ,p_group_cert_issue_date IN VARCHAR2 DEFAULT hr_api.g_varchar2
436 ,p_hrs_worked_per_week IN VARCHAR2 DEFAULT hr_api.g_varchar2
437 ,p_contract_id IN NUMBER DEFAULT hr_api.g_number
438 ,p_establishment_id IN NUMBER DEFAULT hr_api.g_number
439 ,p_collective_agreement_id IN NUMBER DEFAULT hr_api.g_number
440 ,p_cagr_id_flex_num IN NUMBER DEFAULT hr_api.g_number
441 ,p_cag_segment1 IN VARCHAR2 DEFAULT hr_api.g_varchar2
442 ,p_cag_segment2 IN VARCHAR2 DEFAULT hr_api.g_varchar2
443 ,p_cag_segment3 IN VARCHAR2 DEFAULT hr_api.g_varchar2
444 ,p_cag_segment4 IN VARCHAR2 DEFAULT hr_api.g_varchar2
445 ,p_cag_segment5 IN VARCHAR2 DEFAULT hr_api.g_varchar2
446 ,p_cag_segment6 IN VARCHAR2 DEFAULT hr_api.g_varchar2
447 ,p_cag_segment7 IN VARCHAR2 DEFAULT hr_api.g_varchar2
448 ,p_cag_segment8 IN VARCHAR2 DEFAULT hr_api.g_varchar2
449 ,p_cag_segment9 IN VARCHAR2 DEFAULT hr_api.g_varchar2
450 ,p_cag_segment10 IN VARCHAR2 DEFAULT hr_api.g_varchar2
451 ,p_cag_segment11 IN VARCHAR2 DEFAULT hr_api.g_varchar2
452 ,p_cag_segment12 IN VARCHAR2 DEFAULT hr_api.g_varchar2
453 ,p_cag_segment13 IN VARCHAR2 DEFAULT hr_api.g_varchar2
454 ,p_cag_segment14 IN VARCHAR2 DEFAULT hr_api.g_varchar2
455 ,p_cag_segment15 IN VARCHAR2 DEFAULT hr_api.g_varchar2
456 ,p_cag_segment16 IN VARCHAR2 DEFAULT hr_api.g_varchar2
457 ,p_cag_segment17 IN VARCHAR2 DEFAULT hr_api.g_varchar2
458 ,p_cag_segment18 IN VARCHAR2 DEFAULT hr_api.g_varchar2
459 ,p_cag_segment19 IN VARCHAR2 DEFAULT hr_api.g_varchar2
460 ,p_cag_segment20 IN VARCHAR2 DEFAULT hr_api.g_varchar2
461 ,p_cagr_grade_def_id OUT NOCOPY NUMBER
462 ,p_cagr_concatenated_segments OUT NOCOPY VARCHAR2
463 ,p_comment_id OUT NOCOPY NUMBER
464 ,p_soft_coding_keyflex_id OUT NOCOPY NUMBER
465 ,p_effective_start_date OUT NOCOPY DATE
466 ,p_effective_end_date OUT NOCOPY DATE
467 ,p_concatenated_segments OUT NOCOPY VARCHAR2
468 ,p_no_managers_warning OUT NOCOPY BOOLEAN
469 ,p_other_manager_warning OUT NOCOPY BOOLEAN
470 )
471 IS
472 --
473 -- Declare cursors AND local variables
474 --
475 l_proc VARCHAR2(72) := g_package||'update_au_emp_asg';
476 l_effective_date DATE;
477 l_legislation_code per_business_groups.legislation_code%TYPE;
478 l_business_group_id per_business_groups.business_group_id%type;
479 a varchar2(100);
480
481 --
482 CURSOR check_legislation
483 (c_assignment_id per_assignments_f.assignment_id%TYPE,
484 c_effective_date DATE
485 )
486 IS
487 SELECT bgp.legislation_code
488 FROM per_assignments_f asg,
489 per_business_groups bgp
490 WHERE asg.business_group_id = bgp.business_group_id
491 AND asg.assignment_id = c_assignment_id
492 AND c_effective_date BETWEEN effective_start_date AND effective_end_date;
493 --
494
495
496 BEGIN
497 hr_utility.set_location('Entering:'|| l_proc, 10);
498
499 --
500 -- Truncate DATE variables
501 --
502 l_effective_date := trunc(p_effective_date);
503 --
504 -- Validate IN addition to Table Handlers
505 --
506 -- Check that the assignment exists.
507 --
508 OPEN check_legislation(p_assignment_id, l_effective_date);
509 FETCH check_legislation into l_legislation_code;
510 IF check_legislation%notfound THEN
511 CLOSE check_legislation;
512 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
513 hr_utility.raise_error;
514 END IF;
515 CLOSE check_legislation;
516 hr_utility.set_location(l_proc, 20);
517 --
518 -- Check that the legislation of the specified business group IS 'AU'.
519 --
520 IF l_legislation_code <> 'AU' THEN
521 hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
522 hr_utility.set_message_token('LEG_CODE','AU');
523 hr_utility.raise_error;
524 END IF;
525 hr_utility.set_location(l_proc, 30);
526 --
527 --
528 -- Call update_emp_asg business process
529 --
530 hr_assignment_api.update_emp_asg
531 (p_validate => p_validate
532 ,p_effective_date => p_effective_date
533 ,p_datetrack_update_mode => p_datetrack_update_mode
534 ,p_assignment_id => p_assignment_id
535 ,p_object_version_number => p_object_version_number
536 ,p_supervisor_id => p_supervisor_id
537 ,p_assignment_number => p_assignment_number
538 ,p_change_reason => p_change_reason
539 ,p_comments => p_comments
540 ,p_date_probation_end => p_date_probation_end
541 ,p_default_code_comb_id => p_default_code_comb_id
542 ,p_frequency => p_frequency
543 ,p_internal_address_line => p_internal_address_line
544 ,p_manager_flag => p_manager_flag
545 ,p_normal_hours => p_normal_hours
546 ,p_perf_review_period => p_perf_review_period
547 ,p_perf_review_period_frequency => p_perf_review_period_frequency
548 ,p_probation_period => p_probation_period
549 ,p_probation_unit => p_probation_unit
550 ,p_sal_review_period => p_sal_review_period
551 ,p_sal_review_period_frequency => p_sal_review_period_frequency
552 ,p_set_of_books_id => p_set_of_books_id
553 ,p_source_type => p_source_type
554 ,p_time_normal_finish => p_time_normal_finish
555 ,p_time_normal_start => p_time_normal_start
556 ,p_bargaining_unit_code => p_bargaining_unit_code
557 ,p_labour_union_member_flag => p_labour_union_member_flag
558 ,p_hourly_salaried_code => p_hourly_salaried_code
559 ,p_ass_attribute_category => p_ass_attribute_category
560 ,p_ass_attribute1 => p_ass_attribute1
561 ,p_ass_attribute2 => p_ass_attribute2
562 ,p_ass_attribute3 => p_ass_attribute3
563 ,p_ass_attribute4 => p_ass_attribute4
564 ,p_ass_attribute5 => p_ass_attribute5
565 ,p_ass_attribute6 => p_ass_attribute6
566 ,p_ass_attribute7 => p_ass_attribute7
567 ,p_ass_attribute8 => p_ass_attribute8
568 ,p_ass_attribute9 => p_ass_attribute9
569 ,p_ass_attribute10 => p_ass_attribute10
570 ,p_ass_attribute11 => p_ass_attribute11
571 ,p_ass_attribute12 => p_ass_attribute12
572 ,p_ass_attribute13 => p_ass_attribute13
573 ,p_ass_attribute14 => p_ass_attribute14
574 ,p_ass_attribute15 => p_ass_attribute15
575 ,p_ass_attribute16 => p_ass_attribute16
576 ,p_ass_attribute17 => p_ass_attribute17
577 ,p_ass_attribute18 => p_ass_attribute18
578 ,p_ass_attribute19 => p_ass_attribute19
579 ,p_ass_attribute20 => p_ass_attribute20
580 ,p_ass_attribute21 => p_ass_attribute21
581 ,p_ass_attribute22 => p_ass_attribute22
582 ,p_ass_attribute23 => p_ass_attribute23
583 ,p_ass_attribute24 => p_ass_attribute24
584 ,p_ass_attribute25 => p_ass_attribute25
585 ,p_ass_attribute26 => p_ass_attribute26
586 ,p_ass_attribute27 => p_ass_attribute27
587 ,p_ass_attribute28 => p_ass_attribute28
588 ,p_ass_attribute29 => p_ass_attribute29
589 ,p_ass_attribute30 => p_ass_attribute30
590 ,p_title => p_title
591 ,p_segment1 => p_legal_employer_id
592 ,p_segment2 => p_lev_lod_flg
593 ,p_segment3 => p_group_cert_issue_date
594 ,p_segment4 => p_hrs_worked_per_week
595 ,p_contract_id => p_contract_id
596 ,p_establishment_id => p_establishment_id
597 ,p_collective_agreement_id => p_collective_agreement_id
598 ,p_cagr_id_flex_num => p_cagr_id_flex_num
599 ,p_cag_segment1 => p_cag_segment1
600 ,p_cag_segment2 => p_cag_segment2
601 ,p_cag_segment3 => p_cag_segment3
602 ,p_cag_segment4 => p_cag_segment4
603 ,p_cag_segment5 => p_cag_segment5
604 ,p_cag_segment6 => p_cag_segment6
605 ,p_cag_segment7 => p_cag_segment7
606 ,p_cag_segment8 => p_cag_segment8
607 ,p_cag_segment9 => p_cag_segment9
608 ,p_cag_segment10 => p_cag_segment10
609 ,p_cag_segment11 => p_cag_segment11
610 ,p_cag_segment12 => p_cag_segment12
611 ,p_cag_segment13 => p_cag_segment13
612 ,p_cag_segment14 => p_cag_segment14
613 ,p_cag_segment15 => p_cag_segment15
614 ,p_cag_segment16 => p_cag_segment16
615 ,p_cag_segment17 => p_cag_segment17
616 ,p_cag_segment18 => p_cag_segment18
617 ,p_cag_segment19 => p_cag_segment19
618 ,p_cag_segment20 => p_cag_segment20
619 ,p_cagr_grade_def_id => p_cagr_grade_def_id
620 ,p_cagr_concatenated_segments => p_cagr_concatenated_segments
621 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
622 ,p_comment_id => p_comment_id
623 ,p_effective_start_date => p_effective_start_date
624 ,p_effective_END_date => p_effective_end_date
625 ,p_concatenated_segments => p_concatenated_segments
626 ,p_no_managers_warning => p_no_managers_warning
627 ,p_other_manager_warning => p_other_manager_warning
628 );
629
630
631 hr_utility.set_location(' Leaving:'||l_proc, 40);
632 END update_au_emp_asg;
633
634
635 END hr_au_assignment_api;