4 -- Bug fix : 1580103 -- initializing the pa_res_denorm_rec casuses the
1 PACKAGE pa_hr_update_pa_entities AUTHID CURRENT_USER as
2 /* $Header: PAHRUPDS.pls 120.1.12000000.2 2007/04/05 14:27:34 kjai ship $ */
3
5 -- pl/sql numeric error and fails to complete the work flow process
6 -- hence commented out
7 -- pa_res_denorm_rec PA_RESOURCE_PVT.Resource_Denorm_Rec_Type ;
8
9
10 -- Start of comments
11 -- API name : Update_Project_Entities
12 -- Type : Private
13 -- Pre-reqs : None
14 -- Purpose : This procedure will be called from the database triggers
15 -- created on the following HR and PA entities.
16 -- 1>. PER_ALL_ASSIGNMENTS_F
17 -- 2>. PER_ALL_PEOPLE_F
18 -- 3>. PER_JOBS_EXTRA_INFO
19 -- 4>. HR_ORGANIZATION_INFORMATION
20 -- 5>. PER_VALID_GRADES
21 -- 6>. PER_GRADES
22 -- 7>. PER_ADDRESSES
23 -- 8>. PER_ORG_STRUCTURE_ELEMENTS
24 -- 9>. PA_ALL_ORGANIZATIONS
25 -- 10>. PA_JOB_RELATIONSHIPS
26 --
27 -- Based on the values of the parameters it invokes a worklow
28 -- and sets the appropriate attribute values in the workflow
29 -- and starts a process based on the entity that is updated/
30 -- inserted.
31 --
32 -- Parameters
33 -- p_calling_mode IN VARCHAR2
34 -- This parameter will indicate the mode in which the table is changed
35 -- i.e. INSERT or UPDATE or DELETE.
36 -- p_table_name IN VARCHAR2
37 -- This parameter will have the table name for which this api is called.
38 -- p_person_id IN NUMBER
39 -- This parameter will have the person identifier for which the record is
43 -- This parameter will have the old value of the assignment start date
40 -- inserted or updated. This parameter value is set only if the api is
41 -- called from PER_ALL_ASSIGNMENTS, PER_ADDRESSES and PER_ALL_PEOPLE_F.
42 -- p_start_date_old IN DATE
44 -- if the start date of an assignment is modified. It will be populated
45 -- only for table PER_ALL_ASSIGNMENTS_F.
46 -- p_start_date_new IN DATE
47 -- This parameter will have the new value of the asignment start date if
48 -- the start date of an assignment is modified or if a new assignment is
49 -- created. It will be populated only for table PER_ALL_ASSIGNMENTS_F.
50 -- p_end_date_old IN DATE
51 -- This parameter will have the old value of the assignment end date
52 -- if the end date of an assignment is modified. It will be populated
53 -- only for table PER_ALL_ASSIGNMENTS_F.
54 -- p_end_date_new IN DATE
55 -- This parameter will have the new value of the asignment end date if
56 -- the end date of an assignment is modified or if a new assignment is
57 -- created. It will be populated only for table PER_ALL_ASSIGNMENTS_F.
58 -- p_org_id_old IN NUMBER
59 -- This parameter will have the old value of organization identifier if
60 -- the organization is changed. It will be populated only for table
61 -- PER_ALL_ASSIGNMENTS_F.
62 -- p_org_id_new IN NUMBER
63 -- This parameter will have the new value of the organization if the
64 -- organization identifier is changed. It will be populated for tables
65 -- PER_ALL_ASSIGNMENTS_F and HR_ORGANIZATION_INFORMATION.
66 -- p_job_id_old IN NUMBER
67 -- This parameter will have the old value job identifier if it is changed.
68 -- It will be populated for PER_VALID_GRADES, PER_ALL_ASSIGNMENTS_F,
69 -- PA_JOB_MAPPINGS
70 -- p_job_id_new IN NUMBER
71 -- This parameter will have the new value of ob identifier if it is changed.
72 -- It will be populated for PER_VALID_GRADES, PER_ALL_ASSIGNMENTS_F,
73 -- PA_JOB_MAPPINGS.
74 -- p_from_job_group_id IN NUMBER
75 -- This parameter will have the value of the from job group identifier when
76 -- a job mapping is changed. It will be populated only if PA_JOB_MAPPINGS
77 -- is changed.
78 -- p_to_job_group_id IN NUMBER
79 -- This parameter will have the value of the to job group identifier when
80 -- a job mapping is changed. It will be populated only if PA_JOB_MAPPINGS
81 -- is changed.
82 -- p_job_level_old IN NUMBER
83 -- This parameter will have the old value of the job level when PER_GRADES
84 -- table is changed.
85 -- p_job_level_new IN NUMBER
86 -- This parameter will have the new value of the job level when PER_GRADES
87 -- table is changed.
88 -- p_supervisor_old IN NUMBER
89 -- This parameter will have the old value of the supervisor identifier when
90 -- PER_ASSIGNMENTS_F is changed.
91 -- p_supervisor_new IN NUMBER
92 -- This parameter will have the new value of the supervisor identifier when
93 -- PER_ASSIGNMENTS_F is changed.
94 -- p_primary_flag_old IN VARCHAR2
95 -- This parameter will have the old value of the primary flag when an
96 -- assignment is changed.
97 -- p_primary_flag_new IN VARCHAR2
98 -- This parameter will have the new value of the primary flag when an
99 -- assignment is changed.
100 -- p_org_info1_old IN VARCHAR2
101 -- This parameter will have the old value of the operating unit when its
102 -- value is changed in HR_ORGANIZATION_INFORMATION.
103 -- p_org_info1_new IN VARCHAR2
104 -- This parameter will have the new value of the operating unit when its
105 -- value is changed in HR_ORGANIZATION_INFORMATION.
106 -- p_jei_information2_old IN VARCHAR2
107 -- This parameter will have the old value of job billability when its
108 -- value is changed in PER_JOBS_EXTRA_INFO.
109 -- p_jei_information2_new IN VARCHAR2
110 -- This parameter will have the new value of job billability when its
111 -- value is changed in PER_JOBS_EXTRA_INFO.
112 -- p_jei_information3_old IN VARCHAR2
113 -- This parameter will have the old value of job utilization when its
114 -- value is changed in PER_JOBS_EXTRA_INFO.
115 -- p_jei_information3_new IN VARCHAR2
116 -- This parameter will have the new value of job utilization when its
117 -- value is changed in PER_JOBS_EXTRA_INFO.
118 -- p_jei_information4_old IN VARCHAR2
119 -- This parameter will have the old value of project job level when its
120 -- value is changed in PER_JOBS_EXTRA_INFO.
121 -- p_jei_information4_new IN VARCHAR2
122 -- This parameter will have the new value of project job level when its
123 -- value is changed in PER_JOBS_EXTRA_INFO.
124 -- p_jei_information6_old IN VARCHAR2
125 -- This parameter will have the old value of scheduable flag when its
126 -- value is changed in PER_JOBS_EXTRA_INFO.
127 -- p_jei_informatio6_new IN VARCHAR2
128 -- This parameter will have the new value of scheduable flag when its
129 -- value is changed in PER_JOBS_EXTRA_INFO.
130 -- p_grade_id_old
134 -- This parameter will have the new value of the grade identifier when
131 -- This parameter will have the old value of the grade identifier when
132 -- its value is changed in PER_VALID_GRADES.
133 -- p_grade_id_new
135 -- its value is changed in PER_VALID_GRADES. Also when a job level is
136 -- changed in PER_GRADES its value is set.
137 -- p_full_name_old IN VARCHAR2
138 -- When the full_name of a person is changed the old value of the
139 -- person's full name is passed through this parameter. So it value is
140 -- set only for PER_ALL_PEOPLE_F.
141 -- p_full_name_new IN VARCHAR2
142 -- When the full_name of a person is changed the old value of the
143 -- person's full name is passed through this parameter. So it value is
144 -- set only for PER_ALL_PEOPLE_F.
145 -- p_country_old IN VARCHAR2
146 -- This parameter will have the old value of the country code when the country
147 -- of an employee is changed. It is passed only for table PER_ADDRESSES.
148 -- p_country_new IN VARCHAR2
149 -- This parameter will have the new value of the country code when the country
150 -- of an employee is changed. It is passed only for table PER_ADDRESSES.
151 -- p_city_old IN VARCHAR2
152 -- This parameter will have the old value of the city when the city
153 -- of an employee is changed. It is passed only for table PER_ADDRESSES.
154 -- p_city_new IN VARCHAR2
155 -- This parameter will have the new value of the city when the city
156 -- of an employee is changed. It is passed only for table PER_ADDRESSES.
157 -- p_region2_old IN VARCHAR2
158 -- This parameter will have the old value of the state when the state
159 -- of an employee is changed. It is passed only for table PER_ADDRESSES.
160 -- p_region2_new IN VARCHAR2
161 -- This parameter will have the new value of the state when the state
162 -- of an employee is changed. It is passed only for table PER_ADDRESSES.
163 -- p_org_struct_element_id IN NUMBER
164 -- This parameter will have the value of organization structure element
165 -- identifier when you change a hierarchy. It is populated only for
166 -- PER_ORG_STRUCTURE_ELEMENTS.
167 -- p_organization_id_parent IN NUMBER
168 -- This parameter will have the value of the parent organization identifier
169 -- when you change a hierarchy. It is populated only for
170 -- PER_ORG_STRUCTURE_ELEMENTS.
171 -- p_organization_id_child IN NUMBER
172 -- This parameter will have the value of the child organization identifier
173 -- when you change a hierarchy. It is populated only for
174 -- PER_ORG_STRUCTURE_ELEMENTS.
175 -- p_org_structure_version_id IN NUMBER
176 -- This parameter will have the version identifier of the structure when
177 -- you change a hierarchy. It is populated only for
178 -- PER_ORG_STRUCTURE_ELEMENTS.
179 -- p_inactive_date_old IN DATE
180 -- This parameter will have the old inactive date date of a project
181 -- organization.
182 -- p_inactive_date_new IN DATE
183 -- This parameter will have the new inactive date date of a project
184 -- organization.
185 -- p_from_job_id_old IN NUMBER
186 -- This parameter will have the old value of the from job identifier. It is
187 -- populated only for PA_JOB_MAPPINGS.
188 -- p_from_job_id_new IN NUMBER
189 -- This parameter will have the new value of the from job identifier. It is
190 -- populated only for PA_JOB_MAPPINGS.
191 -- p_to_job_id_old IN NUMBER
192 -- This parameter will have the old value of the to job identifier. It is
193 -- populated only for PA_JOB_MAPPINGS.
194 -- p_to_job_id_new IN NUMBER
195 -- This parameter will have the new value of the to job identifier. It is
196 -- populated only for PA_JOB_MAPPINGS.
197 -- p_org_info_context in VARCHAR2
198 -- This parameter will have the value of the organization information
199 -- context. This applied to table HR_ORGANIZATION_INFORMATION.
200 -- x_return_status OUT VARCHAR2
201 -- x_error_message_code OUT VARCHAR2
202
203 --
204 -- p_end_date_new IN DATE
205 -- This parameter will have the new value of the organization
206 -- if the end date of an assignment is modified or if a new assignment
207 -- is created. It will be populated only for table PER_ALL_ASSIGNMENTS_F
208 -- and HR_ORGANIZATION_INFORMATION.
209
210 PROCEDURE update_project_entities ( p_calling_mode in varchar2,
211 p_table_name in varchar2,
212 p_person_id in number DEFAULT NULL,
213 p_start_date_old in date DEFAULT NULL,
214 p_start_date_new in date DEFAULT NULL,
215 p_end_date_old in date DEFAULT NULL,
216 p_end_date_new in date DEFAULT NULL,
217 p_org_id_old in number DEFAULT NULL,
218 p_org_id_new in number DEFAULT NULL,
219 p_job_id_old in number DEFAULT NULL,
223 p_job_level_old in number DEFAULT NULL,
220 p_job_id_new in number DEFAULT NULL,
221 p_from_job_group_id in number DEFAULT NULL,
222 p_to_job_group_id in number DEFAULT NULL,
224 p_job_level_new in number DEFAULT NULL,
225 p_supervisor_old in number DEFAULT NULL,
226 p_supervisor_new in number DEFAULT NULL,
227 p_primary_flag_old in varchar2 DEFAULT NULL,
228 p_primary_flag_new in varchar2 DEFAULT NULL,
229 p_org_info1_old in varchar2 DEFAULT NULL,
230 p_org_info1_new in varchar2 DEFAULT NULL,
231 p_jei_information2_old in varchar2 DEFAULT NULL,
232 p_jei_information2_new in varchar2 DEFAULT NULL,
233 p_jei_information3_old in varchar2 DEFAULT NULL,
234 p_jei_information3_new in varchar2 DEFAULT NULL,
235 p_jei_information4_old in varchar2 DEFAULT NULL,
236 p_jei_information4_new in varchar2 DEFAULT NULL,
237 p_jei_information6_old in varchar2 DEFAULT NULL,
238 p_jei_information6_new in varchar2 DEFAULT NULL,
239 p_grade_id_old in number DEFAULT NULL,
240 p_grade_id_new in number DEFAULT NULL,
241 p_full_name_old in varchar2 DEFAULT NULL,
242 p_full_name_new in varchar2 DEFAULT NULL,
243 p_country_old in varchar2 DEFAULT NULL,
244 p_country_new in varchar2 DEFAULT NULL,
245 p_city_old in varchar2 DEFAULT NULL,
246 p_city_new in varchar2 DEFAULT NULL,
247 p_region2_old in varchar2 DEFAULT NULL,
248 p_region2_new in varchar2 DEFAULT NULL,
249 p_org_struct_element_id in number DEFAULT NULL,
250 p_organization_id_parent in number DEFAULT NULL,
251 p_organization_id_child in number DEFAULT NULL,
252 p_org_structure_version_id in number DEFAULT NULL,
253 p_inactive_date_old in date DEFAULT NULL,
254 p_inactive_date_new in date DEFAULT NULL,
255 p_from_job_id_old in number DEFAULT NULL,
256 p_from_job_id_new in number DEFAULT NULL,
257 p_to_job_id_old in number DEFAULT NULL,
258 p_to_job_id_new in number DEFAULT NULL,
259 p_org_info_context in varchar2 DEFAULT NULL,
260 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
261 x_error_message_code out NOCOPY varchar2); --File.Sql.39 bug 4440895
262
263
264 PROCEDURE org_struct_element_change
265 (itemtype IN VARCHAR2
266 , itemkey IN VARCHAR2
267 , actid IN NUMBER
268 , funcmode IN VARCHAR2
269 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
270 ) ;
271
272 PROCEDURE Job_Bill_Change
273 (itemtype IN VARCHAR2
274 , itemkey IN VARCHAR2
275 , actid IN NUMBER
276 , funcmode IN VARCHAR2
277 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
278 ) ;
279
280 PROCEDURE Full_Name_Change
281 (itemtype IN VARCHAR2
282 , itemkey IN VARCHAR2
283 , actid IN NUMBER
284 , funcmode IN VARCHAR2
285 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
286 ) ;
287
288
289 PROCEDURE Default_OU_Change
290 (itemtype IN VARCHAR2
291 , itemkey IN VARCHAR2
292 , actid IN NUMBER
293 , funcmode IN VARCHAR2
294 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
295 ) ;
296
297
298 PROCEDURE Valid_Grade_Change
299 (itemtype IN VARCHAR2
300 , itemkey IN VARCHAR2
301 , actid IN NUMBER
302 , funcmode IN VARCHAR2
303 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
304 ) ;
305
306 PROCEDURE Job_Level_Change
307 (itemtype IN VARCHAR2
308 , itemkey IN VARCHAR2
309 , actid IN NUMBER
310 , funcmode IN VARCHAR2
314 PROCEDURE Address_Change
311 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
312 ) ;
313
315 (itemtype IN VARCHAR2
316 , itemkey IN VARCHAR2
317 , actid IN NUMBER
318 , funcmode IN VARCHAR2
319 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
320 ) ;
321
322 PROCEDURE Project_Organization_Change
323 (itemtype IN VARCHAR2
324 , itemkey IN VARCHAR2
325 , actid IN NUMBER
326 , funcmode IN VARCHAR2
327 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
328 ) ;
329
330 PROCEDURE Job_Rel_Change
331 (itemtype IN VARCHAR2
332 , itemkey IN VARCHAR2
333 , actid IN NUMBER
334 , funcmode IN VARCHAR2
335 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
336 ) ;
337
338 PROCEDURE assignment_change
339 (itemtype IN VARCHAR2
340 , itemkey IN VARCHAR2
341 , actid IN NUMBER
342 , funcmode IN VARCHAR2
343 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
344 ) ;
345
346
347 PROCEDURE Set_Nf_Error_Msg_Attr (p_item_type IN VARCHAR2,
348 p_item_key IN VARCHAR2,
349 p_msg_count IN NUMBER,
350 p_msg_data IN VARCHAR2 ) ;
351
352 --
353 -- PROCEDURE
354 -- create_fte_sync_wf
355 -- PURPOSE
356 -- This procedure creates a wf process for termination of employee/contingent worker
357 --
358 -- HISTORY
359 -- 27-MAR-207 kjai Created for Bug 5683340
360 PROCEDURE create_fte_sync_wf
361 (p_person_id IN PA_EMPLOYEES.PERSON_ID%TYPE
362 ,p_wait_days IN NUMBER
363 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
364 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
365 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
366 ) ;
367
368 --
369 -- PROCEDURE
370 -- start_fte_sync_wf
371 -- PURPOSE
372 -- This procedure starts wf process for termination of employee/contingent worker
373 --
374 -- HISTORY
375 -- 27-MAR-207 kjai Created for Bug 5683340
376 PROCEDURE start_fte_sync_wf
377 (itemtype IN VARCHAR2
378 , itemkey IN VARCHAR2
379 , actid IN NUMBER
380 , funcmode IN VARCHAR2
381 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
382 ) ;
383
384 END pa_hr_update_pa_entities ;