[Home] [Help]
PACKAGE BODY: APPS.POR_LOAD_EMPLOYEE_ASSIGNMENT
Source
1 PACKAGE BODY POR_LOAD_EMPLOYEE_ASSIGNMENT as
2 /* $Header: PORLEMAB.pls 115.6 2002/11/19 00:36:52 jjessup ship $ */
3
4 PROCEDURE insert_update_employee_asg (
5 x_person_id IN NUMBER,
6 x_business_group_id IN NUMBER,
7 x_location_name IN VARCHAR2,
8 x_assignment_number IN OUT NOCOPY VARCHAR2,
9 x_default_employee_account IN VARCHAR2,
10 x_set_of_books_name IN VARCHAR2,
11 x_job_name IN VARCHAR2,
12 x_supervisor_emp_number IN VARCHAR2,
13 x_effective_start_date IN DATE,
14 x_effective_end_date IN DATE)
15
16 IS
17
18 l_person_id NUMBER;
19 l_assignment_id NUMBER;
20 l_object_version_number NUMBER;
21 l_effective_start_date DATE;
22 l_effective_end_date DATE;
23 l_comment_id NUMBER;
24 l_location_id NUMBER;
25 l_assignment_sequence NUMBER;
26 l_name_combination_warning BOOLEAN;
27 l_assign_payroll_warning BOOLEAN;
28 l_business_group_id NUMBER;
29 l_employee_number NUMBER;
30 l_set_of_books_id NUMBER;
31 l_concatenated_segments VARCHAR2(20);
32 l_cagr_grade_def_id NUMBER;
33 l_cagr_concatenated_segments VARCHAR2(20);
34 l_soft_coding_keyflex_id NUMBER;
35 l_people_group_id NUMBER;
36 l_other_manager_warning BOOLEAN;
37 l_no_managers_warning BOOLEAN;
38 l_chart_of_accounts_id NUMBER;
39 l_ccid NUMBER;
40 l_job_id NUMBER;
41 l_supervisor_id NUMBER;
42 l_group_name VARCHAR2(10);
43 l_address_id NUMBER;
44 l_employment_category VARCHAR2(20);
45 l_spp_delete_warning BOOLEAN;
46 l_entries_changed_warning VARCHAR2(20);
47 l_tax_district_changed_warning BOOLEAN;
48 l_special_ceiling_step_id NUMBER;
49
50 BEGIN
51
52 l_set_of_books_id := get_set_of_books_id(x_set_of_books_name);
53
54 l_chart_of_accounts_id := get_chart_of_accounts_id(l_set_of_books_id);
55
56 l_ccid := get_ccid(l_chart_of_accounts_id,x_default_employee_account);
57
58 if (x_job_name IS NOT NULL) THEN
59
60 l_job_id := get_job_id(x_job_name,x_business_group_id);
61
62 END IF;
63
64 IF (x_supervisor_emp_number IS NOT NULL) THEN
65
66 l_supervisor_id := get_supervisor_id(x_supervisor_emp_number);
67
68 END IF;
69
70 IF (x_location_name IS NOT NULL) THEN
71
72 l_location_id := get_location_id(x_location_name);
73
74 END IF;
75
76 get_assignment_exists(x_person_id,x_effective_start_date,x_effective_end_date,l_assignment_id,l_object_version_number);
77
78 hr_assignment_api.update_emp_asg (
79 p_validate => FALSE
80 ,p_datetrack_update_mode => 'CORRECTION'
81 ,p_effective_date => x_effective_start_date
82 ,p_concatenated_segments => l_concatenated_segments
83 ,p_cagr_grade_def_id => l_cagr_grade_def_id
84 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
85 ,p_assignment_id => l_assignment_id
86 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
87 ,p_object_version_number => l_object_version_number
88 ,p_assignment_number => x_assignment_number
89 ,p_effective_start_date => l_effective_start_date
90 ,p_effective_end_date => l_effective_end_date
91 ,p_comment_id => l_comment_id
92 ,p_other_manager_warning => l_other_manager_warning
93 ,p_no_managers_warning => l_no_managers_warning
94 ,p_set_of_books_id => l_set_of_books_id
95 ,p_default_code_comb_id => l_ccid
96 ,p_supervisor_id => l_supervisor_id
97 );
98
99
100 hr_assignment_api.update_emp_asg_criteria (
101 p_validate => FALSE
102 ,p_datetrack_update_mode => 'CORRECTION'
103 ,p_assignment_id => l_assignment_id
104 ,p_object_version_number => l_object_version_number
105 ,p_special_ceiling_step_id => l_special_ceiling_step_id
106 ,p_effective_date => x_effective_start_date
107 ,p_job_id => l_job_id
108 ,p_location_id => l_location_id
109 ,p_group_name => l_group_name
110 ,p_employment_category => l_employment_category
111 ,p_effective_start_date => l_effective_start_date
112 ,p_effective_end_date => l_effective_end_date
113 ,p_people_group_id => l_people_group_id
114 ,p_org_now_no_manager_warning => l_no_managers_warning
115 ,p_other_manager_warning => l_other_manager_warning
116 ,p_spp_delete_warning => l_spp_delete_warning
117 ,p_entries_changed_warning => l_entries_changed_warning
118 ,p_tax_district_changed_warning => l_tax_district_changed_warning
119 );
120
121
122 commit;
123
124 EXCEPTION
125
126 WHEN NO_DATA_FOUND THEN
127 RETURN;
128
129 WHEN OTHERS THEN
130 RAISE;
131
132
133 END insert_update_employee_asg;
134
135
136 FUNCTION get_set_of_books_id (p_set_of_books_name IN VARCHAR2) RETURN NUMBER IS
137 l_set_of_books_id NUMBER;
138 BEGIN
139
140 SELECT set_of_books_id INTO l_set_of_books_id
141 FROM gl_sets_of_books
142 WHERE name = p_set_of_books_name;
143
144 RETURN l_set_of_books_id;
145 EXCEPTION
146 WHEN NO_DATA_FOUND THEN
147 RETURN NULL;
148
149 END get_set_of_books_id;
150
151
152 FUNCTION get_location_id (p_location_name IN VARCHAR2) RETURN NUMBER IS
153 l_location_id NUMBER;
154 BEGIN
155
156 SELECT location_id INTO l_location_id
157 FROM hr_locations_all
158 WHERE location_code = p_location_name;
159
160 RETURN l_location_id;
161
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164 RETURN -1;
165
166 END get_location_id;
167
168
169 FUNCTION get_chart_of_accounts_id (p_set_of_books_id IN NUMBER) RETURN NUMBER IS
170 l_chart_of_accounts_id NUMBER;
171 BEGIN
172
173 SELECT chart_of_accounts_id INTO l_chart_of_accounts_id
174 FROM gl_sets_of_books
175 WHERE set_of_books_id = p_set_of_books_id;
176
177 RETURN l_chart_of_accounts_id;
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 RETURN NULL;
181
182 END get_chart_of_accounts_id;
183
184
185 FUNCTION get_ccid (p_chart_of_accounts_id IN NUMBER,p_concatenated_segs IN VARCHAR2) RETURN NUMBER IS
186 l_ccid NUMBER;
187 BEGIN
188
189
190
191 l_ccid := fnd_flex_ext.get_ccid('SQLGL','GL#',p_chart_of_accounts_id, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),p_concatenated_segs);
192
193
194 IF (l_ccid = 0) THEN
195 RETURN NULL;
196 ELSE
197 RETURN l_ccid;
198
199 END IF;
200
201 EXCEPTION
202 WHEN NO_DATA_FOUND THEN
203 RETURN NULL;
204
205 END get_ccid;
206
207 PROCEDURE get_assignment_exists(p_person_id IN NUMBER,p_effective_start_date IN DATE, p_effective_end_date IN DATE,l_assignment_id OUT NOCOPY NUMBER,l_object_version_number OUT NOCOPY NUMBER)
208 IS
209
210 BEGIN
211
212 SELECT assignment_id, object_version_number INTO l_assignment_id, l_object_version_number FROM per_all_assignments_f
213 WHERE person_id = p_person_id and trunc(effective_start_date) <= trunc(p_effective_start_date);
214
215 EXCEPTION
216 WHEN NO_DATA_FOUND THEN
217 RETURN;
218
219 END get_assignment_exists;
220
221 FUNCTION get_job_id (p_job_name IN VARCHAR2, p_business_group_id IN NUMBER) RETURN NUMBER IS
222 l_job_id NUMBER;
223 BEGIN
224
225 SELECT job_id INTO l_job_id FROM per_jobs job
226 WHERE job.name = p_job_name
227 AND job.business_group_id = p_business_group_id;
228
229 RETURN l_job_id;
230
231 EXCEPTION
232 WHEN NO_DATA_FOUND THEN
233 RETURN -1;
234
235 END get_job_id;
236
237
238 FUNCTION get_supervisor_id(x_supervisor_emp_num IN VARCHAR2) RETURN NUMBER IS
239 l_supervisor_id NUMBER;
240 BEGIN
241
242 SELECT person_id INTO l_supervisor_id FROM per_all_people_f
243 WHERE employee_number = x_supervisor_emp_num;
244
245 RETURN l_supervisor_id;
246
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 RETURN -1;
250
251 END get_supervisor_id;
252
253 FUNCTION get_address_exists(p_person_id IN NUMBER) RETURN NUMBER IS
254 l_address_id NUMBER;
255 BEGIN
256
257 SELECT address_id INTO l_address_id
258 FROM per_addresses
259 WHERE person_id = p_person_id
260 AND primary_flag = 'Y';
261
262 RETURN l_address_id;
263
264 EXCEPTION
265 WHEN NO_DATA_FOUND THEN
266 RETURN NULL;
267
268 END get_address_exists;
269
270 END POR_LOAD_EMPLOYEE_ASSIGNMENT;