[Home] [Help]
PACKAGE BODY: APPS.PQH_PTX_UTL
Source
1 Package Body pqh_ptx_utl as
2 /* $Header: pqptxutl.pkb 120.3 2008/02/25 10:47:04 brsinha noship $ */
3 --
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := ' pqh_ptx_utl.'; -- Global package name
10 --
11 -- ----------------------------------------------------------------------------
12 -- |------------------------< delete_attributes >------------------------|
13 -- ----------------------------------------------------------------------------
14
15 procedure delete_attributes
16 (
17 p_query_str in varchar2,
18 p_attrib_prv_tab out nocopy pqh_prvcalc.t_attname_priv
19 )
20 is
21
22 -- declare local variables
23
24 TYPE cur_typ is REF CURSOR;
25 attrib_cur cur_typ;
26 i binary_integer := 1;
27 l_form_column_name pqh_txn_category_attributes.form_column_name%TYPE;
28
29
30 begin
31
32 OPEN attrib_cur FOR p_query_str;
33 LOOP
34 FETCH attrib_cur into l_form_column_name;
35 EXIT WHEN attrib_cur%NOTFOUND;
36 p_attrib_prv_tab(i).form_column_name := l_form_column_name;
37 p_attrib_prv_tab(i).mode_flag := 'D';
38 p_attrib_prv_tab(i).reqd_flag := 'D';
39 i := i + 1;
40 END LOOP;
41
42 CLOSE attrib_cur;
43
44 end delete_attributes;
45
46 -- ----------------------------------------------------------------------------
47 -- |---------------------------< update_pos_tran >-----------------------------|
48 -- ----------------------------------------------------------------------------
49
50 procedure update_pos_tran(p_position_transaction_id in number,
51 p_position_id in number,
52 p_job_id in number,
53 p_organization_id in number,
54 p_effective_date in date) is
55 cursor c1 is select worksheet_detail_id,object_version_number
56 from pqh_worksheet_details
57 where position_id is null
58 and position_transaction_id = p_position_transaction_id
59 for update of position_id;
60 begin
61 for i in c1 loop
62 --
63 pqh_wdt_shd.lck(
64 p_worksheet_detail_id => i.worksheet_detail_id,
65 p_object_version_number => i.object_version_number);
66 --
67 pqh_worksheet_details_api.update_worksheet_detail(
68 p_worksheet_detail_id => i.worksheet_detail_id,
69 p_position_id => p_position_id,
70 p_job_id => p_job_id,
71 p_organization_id => p_organization_id,
72 p_effective_date => p_effective_date,
73 p_object_version_number => i.object_version_number);
74 --
75 end loop;
76 end update_pos_tran;
77
78 -- ----------------------------------------------------------------------------
79 -- |--------------------------< log_warnings >------------------------------|
80 -- ----------------------------------------------------------------------------
81 procedure log_warnings(p_transaction_id number) is
82 l_warn_tab pqh_utility.warnings_tab;
83 l_warn_no number;
84 begin
85 --
86 pqh_utility.get_all_warnings(l_warn_tab, l_warn_no);
87 --
88 if l_warn_no > 0 then
89 --
90 for l_rec_no in l_warn_tab.first .. l_warn_tab.last
91 loop
92 --
93 pqh_process_batch_log.set_context_level (
94 p_txn_id => l_rec_no,
95 p_txn_table_route_id => null,
96 p_level => 1,
97 p_log_context => 'WARNING');
98 --
99 pqh_process_batch_log.insert_log ( p_message_type_cd => 'WARNING',
100 p_message_text => l_warn_tab(l_rec_no).message_text);
101 end loop;
102 --
103 end if;
104 --
105 end;
106 --
107 procedure append_if_not_present(p_string in out nocopy varchar2, p_substring varchar2) is
108 l_index integer;
109 begin
110 if p_substring is not null then
111 if p_string is null then
112 p_string := p_substring;
113 else
114 l_index := instr(nvl(p_string,''), p_substring);
115 hr_utility.set_location('l_index:'||nvl(l_index,-111), 1000);
116 if l_index = 0 then
117 p_string := p_string || fnd_global.local_chr(10) || ' ' || p_substring;
118 end if;
119 end if;
120 end if;
121 end;
122 --
123 -- ----------------------------------------------------------------------------
124 -- |--------------------------< fetch_position >------------------------------|
125 -- ----------------------------------------------------------------------------
126
127 procedure fetch_position
128 (
129 p_position_transaction_id out nocopy pqh_position_transactions.position_transaction_id%TYPE,
130 p_position_id in pqh_position_transactions.position_id%TYPE,
131 p_action_date in date,
132 p_review_flag in pqh_position_transactions.review_flag%type
133 ) is
134 l_object_version_number number := 1;
135 v_posn_rec HR_POSITIONS%ROWTYPE;
136 l_pei_populated varchar2(5000);
137 l_jtr_populated varchar2(5000);
138 l_ptx_deployment_factor_id number;
139 --
140 cursor c_pqh_ptx_dpf_df_s is
141 select pqh_ptx_dpf_df_s.nextval
142 from dual;
143 --
144 CURSOR c1 IS
145 SELECT *
146 FROM hr_positions
147 WHERE position_id = p_position_id;
148 v_wc_rec PER_DEPLOYMENT_FACTORS%ROWTYPE;
149 --
150 CURSOR c2 IS
151 SELECT *
152 FROM per_deployment_factors
153 WHERE position_id = p_position_id;
154 begin
155 --
156 OPEN c1;
157 FETCH c1 INTO v_posn_rec;
158 if c1%notfound then
159 return;
160 else
161 OPEN c2;
162 FETCH c2 INTO v_wc_rec;
163 CLOSE c2;
164 end if;
165 CLOSE c1;
166 --
167 if (pqh_psf_bus.position_control_enabled(
168 v_posn_rec.organization_id, p_action_date) = 'N') then
169 fnd_message.set_name('PQH','PQH_ORG_NOT_POS_CONTROLLED');
170 fnd_message.raise_error;
171 end if;
172 --
173 if (v_posn_rec.review_flag = 'Y') then
174 fnd_message.set_name('PQH','PQH_POSITION_UNDER_REVIEW');
175 fnd_message.raise_error;
176 end if;
177 --
178 pqh_position_transactions_api.create_position_transaction
179 (
180 p_validate => false
181 ,p_position_transaction_id => p_position_transaction_id
182 ,p_action_date => p_action_date
183 ,p_position_id => p_position_id
184 ,p_availability_status_id => v_posn_rec.availability_status_id
185 ,p_business_group_id => v_posn_rec.business_group_id
186 ,p_entry_step_id => v_posn_rec.entry_step_id
187 ,p_entry_grade_rule_id => v_posn_rec.entry_grade_rule_id
188 ,p_job_id => v_posn_rec.job_id
189 ,p_location_id => v_posn_rec.location_id
190 ,p_organization_id => v_posn_rec.organization_id
191 ,p_pay_freq_payroll_id => v_posn_rec.pay_freq_payroll_id
192 ,p_position_definition_id => v_posn_rec.position_definition_id
193 ,p_prior_position_id => v_posn_rec.prior_position_id
194 ,p_relief_position_id => v_posn_rec.relief_position_id
195 ,p_entry_grade_id => v_posn_rec.entry_grade_id
196 ,p_successor_position_id => v_posn_rec.successor_position_id
197 ,p_supervisor_position_id => v_posn_rec.supervisor_position_id
198 ,p_amendment_date => v_posn_rec.amendment_date
199 ,p_amendment_recommendation => v_posn_rec.amendment_recommendation
200 ,p_amendment_ref_number => v_posn_rec.amendment_ref_number
201 ,p_avail_status_prop_end_date => v_posn_rec.avail_status_prop_end_date
202 ,p_bargaining_unit_cd => v_posn_rec.bargaining_unit_cd
203 ,p_comments => v_posn_rec.comments
204 ,p_country1 => v_wc_rec.country1
205 ,p_country2 => v_wc_rec.country2
206 ,p_country3 => v_wc_rec.country3
207 ,p_current_job_prop_end_date => v_posn_rec.current_job_prop_end_date
208 ,p_current_org_prop_end_date => v_posn_rec.current_org_prop_end_date
209 ,p_date_effective => v_posn_rec.date_effective
210 ,p_date_end => null --v_posn_rec.date_end
211 ,p_earliest_hire_date => v_posn_rec.earliest_hire_date
212 ,p_fill_by_date => v_posn_rec.fill_by_date
213 ,p_frequency => v_posn_rec.frequency
214 ,p_fte => v_posn_rec.fte
215 ,p_fte_capacity => v_wc_rec.fte_capacity
216 ,p_location1 => v_wc_rec.location1
217 ,p_location2 => v_wc_rec.location2
218 ,p_location3 => v_wc_rec.location3
219 ,p_max_persons => v_posn_rec.max_persons
220 ,p_name => v_posn_rec.name
221 ,p_other_requirements => v_wc_rec.other_requirements
222 ,p_overlap_period => v_posn_rec.overlap_period
223 ,p_overlap_unit_cd => v_posn_rec.overlap_unit_cd
224 ,p_passport_required => v_wc_rec.passport_required
225 ,p_pay_term_end_day_cd => v_posn_rec.pay_term_end_day_cd
226 ,p_pay_term_end_month_cd => v_posn_rec.pay_term_end_month_cd
227 ,p_permanent_temporary_flag => nvl(v_posn_rec.permanent_temporary_flag, 'Y')
228 ,p_permit_recruitment_flag => nvl(v_posn_rec.permit_recruitment_flag, 'N')
229 ,p_position_type => v_posn_rec.position_type
230 ,p_posting_description => v_posn_rec.posting_description
231 ,p_probation_period => v_posn_rec.probation_period
232 ,p_probation_period_unit_cd => v_posn_rec.probation_period_unit_cd
233 ,p_relocate_domestically => v_wc_rec.relocate_domestically
234 ,p_relocate_internationally => v_wc_rec.relocate_internationally
235 ,p_replacement_required_flag => nvl(v_posn_rec.replacement_required_flag, 'N')
236 ,p_review_flag => nvl(p_review_flag, 'N')
237 ,p_seasonal_flag => nvl(v_posn_rec.seasonal_flag, 'N')
238 ,p_security_requirements => v_posn_rec.security_requirements
239 ,p_service_minimum => v_wc_rec.service_minimum
240 ,p_term_start_day_cd => v_posn_rec.term_start_day_cd
241 ,p_term_start_month_cd => v_posn_rec.term_start_month_cd
242 ,p_time_normal_finish => v_posn_rec.time_normal_finish
243 ,p_time_normal_start => v_posn_rec.time_normal_start
244 ,p_transaction_status => 'PENDING' --:ptx.transaction_status
245 ,p_travel_required => v_wc_rec.travel_required
246 ,p_working_hours => v_posn_rec.working_hours
247 ,p_works_council_approval_flag => v_posn_rec.works_council_approval_flag
248 ,p_work_any_country => v_wc_rec.work_any_country
249 ,p_work_any_location => v_wc_rec.work_any_location
250 ,p_work_period_type_cd => v_posn_rec.work_period_type_cd
251 ,p_work_schedule => v_wc_rec.work_schedule
252 ,p_work_duration => v_wc_rec.work_duration
253 ,p_work_term_end_day_cd => v_posn_rec.work_term_end_day_cd
254 ,p_work_term_end_month_cd => v_posn_rec.work_term_end_month_cd
255 ,p_proposed_fte_for_layoff => v_posn_rec.proposed_fte_for_layoff
256 ,p_proposed_date_for_layoff => v_posn_rec.proposed_date_for_layoff
257 ,p_information1 => v_posn_rec.information1
258 ,p_information2 => v_posn_rec.information2
259 ,p_information3 => v_posn_rec.information3
260 ,p_information4 => v_posn_rec.information4
261 ,p_information5 => v_posn_rec.information5
262 ,p_information6 => v_posn_rec.information6
263 ,p_information7 => v_posn_rec.information7
264 ,p_information8 => v_posn_rec.information8
265 ,p_information9 => v_posn_rec.information9
266 ,p_information10 => v_posn_rec.information10
267 ,p_information11 => v_posn_rec.information11
268 ,p_information12 => v_posn_rec.information12
269 ,p_information13 => v_posn_rec.information13
270 ,p_information14 => v_posn_rec.information14
271 ,p_information15 => v_posn_rec.information15
272 ,p_information16 => v_posn_rec.information16
273 ,p_information17 => v_posn_rec.information17
274 ,p_information18 => v_posn_rec.information18
275 ,p_information19 => v_posn_rec.information19
276 ,p_information20 => v_posn_rec.information20
277 ,p_information21 => v_posn_rec.information21
278 ,p_information22 => v_posn_rec.information22
279 ,p_information23 => v_posn_rec.information23
280 ,p_information24 => v_posn_rec.information24
281 ,p_information25 => v_posn_rec.information25
282 ,p_information26 => v_posn_rec.information26
283 ,p_information27 => v_posn_rec.information27
284 ,p_information28 => v_posn_rec.information28
285 ,p_information29 => v_posn_rec.information29
286 ,p_information30 => v_posn_rec.information30
287 ,p_information_category => v_posn_rec.information_category
288 ,p_attribute1 => v_posn_rec.attribute1
289 ,p_attribute2 => v_posn_rec.attribute2
290 ,p_attribute3 => v_posn_rec.attribute3
291 ,p_attribute4 => v_posn_rec.attribute4
292 ,p_attribute5 => v_posn_rec.attribute5
293 ,p_attribute6 => v_posn_rec.attribute6
294 ,p_attribute7 => v_posn_rec.attribute7
295 ,p_attribute8 => v_posn_rec.attribute8
296 ,p_attribute9 => v_posn_rec.attribute9
297 ,p_attribute10 => v_posn_rec.attribute10
298 ,p_attribute11 => v_posn_rec.attribute11
299 ,p_attribute12 => v_posn_rec.attribute12
300 ,p_attribute13 => v_posn_rec.attribute13
301 ,p_attribute14 => v_posn_rec.attribute14
302 ,p_attribute15 => v_posn_rec.attribute15
303 ,p_attribute16 => v_posn_rec.attribute16
304 ,p_attribute17 => v_posn_rec.attribute17
305 ,p_attribute18 => v_posn_rec.attribute18
306 ,p_attribute19 => v_posn_rec.attribute19
307 ,p_attribute20 => v_posn_rec.attribute20
308 ,p_attribute21 => v_posn_rec.attribute21
309 ,p_attribute22 => v_posn_rec.attribute22
310 ,p_attribute23 => v_posn_rec.attribute23
311 ,p_attribute24 => v_posn_rec.attribute24
312 ,p_attribute25 => v_posn_rec.attribute25
313 ,p_attribute26 => v_posn_rec.attribute26
314 ,p_attribute27 => v_posn_rec.attribute27
315 ,p_attribute28 => v_posn_rec.attribute28
316 ,p_attribute29 => v_posn_rec.attribute29
317 ,p_attribute30 => v_posn_rec.attribute30
318 ,p_attribute_category => v_posn_rec.attribute_category
319 ,p_object_version_number => l_object_version_number --:ptx.object_version_number
320 ,p_effective_date => p_action_date
321 ,p_pay_basis_id => v_posn_rec.pay_basis_id
322 ,p_supervisor_id => v_posn_rec.supervisor_id
323 ,p_wf_transaction_category_id =>
324 pqh_workflow.get_txn_cat('POSITION_TRANSACTION',v_posn_rec.business_group_id)
325 );
326 if (v_posn_rec.position_id is not null) and (p_position_transaction_id is not null) then
327 --
328 open c_pqh_ptx_dpf_df_s;
329 fetch c_pqh_ptx_dpf_df_s into l_ptx_deployment_factor_id;
330 close c_pqh_ptx_dpf_df_s;
331 --
332 insert into pqh_ptx_dpf_df
333 (
334 ptx_deployment_factor_id, deployment_factor_id, position_transaction_id,
335 attribute_category,
336 attribute1, attribute2, attribute3, attribute4, attribute5,
337 attribute6, attribute7, attribute8, attribute9, attribute10,
338 attribute11, attribute12, attribute13, attribute14, attribute15,
339 attribute16, attribute17, attribute18, attribute19, attribute20,
340 object_version_number
341 )
342 values
343 (
344 l_ptx_deployment_factor_id, v_wc_rec.deployment_factor_id, p_position_transaction_id,
345 v_wc_rec.attribute_category,
346 v_wc_rec.attribute1, v_wc_rec.attribute2, v_wc_rec.attribute3, v_wc_rec.attribute4, v_wc_rec.attribute5,
347 v_wc_rec.attribute6, v_wc_rec.attribute7, v_wc_rec.attribute8, v_wc_rec.attribute9, v_wc_rec.attribute10,
348 v_wc_rec.attribute11, v_wc_rec.attribute12, v_wc_rec.attribute13, v_wc_rec.attribute14, v_wc_rec.attribute15,
349 v_wc_rec.attribute16, v_wc_rec.attribute17, v_wc_rec.attribute18, v_wc_rec.attribute19, v_wc_rec.attribute20,
350 1
351 );
352 --
353 --
354 insert into pqh_ptx_dpf_df_shadow
355 (
356 ptx_deployment_factor_id, deployment_factor_id, position_transaction_id,
357 attribute_category,
358 attribute1, attribute2, attribute3, attribute4, attribute5,
359 attribute6, attribute7, attribute8, attribute9, attribute10,
360 attribute11, attribute12, attribute13, attribute14, attribute15,
361 attribute16, attribute17, attribute18, attribute19, attribute20,
362 object_version_number
363 )
364 values
365 (
366 l_ptx_deployment_factor_id, v_wc_rec.deployment_factor_id, p_position_transaction_id,
367 v_wc_rec.attribute_category,
368 v_wc_rec.attribute1, v_wc_rec.attribute2, v_wc_rec.attribute3, v_wc_rec.attribute4, v_wc_rec.attribute5,
369 v_wc_rec.attribute6, v_wc_rec.attribute7, v_wc_rec.attribute8, v_wc_rec.attribute9, v_wc_rec.attribute10,
370 v_wc_rec.attribute11, v_wc_rec.attribute12, v_wc_rec.attribute13, v_wc_rec.attribute14, v_wc_rec.attribute15,
371 v_wc_rec.attribute16, v_wc_rec.attribute17, v_wc_rec.attribute18, v_wc_rec.attribute19, v_wc_rec.attribute20,
372 1
373 );
374 -- populate extra_info into postion transaction
375 pqh_ptx_utl.populate_pei
376 ( p_position_transaction_id => p_position_transaction_id,
377 p_position_id => p_position_id,
378 p_populated => l_pei_populated
379 );
380 pqh_ptx_utl.populate_job_requirements
381 ( p_position_transaction_id => p_position_transaction_id,
382 p_position_id => p_position_id,
383 p_populated => l_jtr_populated
384 );
385 create_ptx_shadow(p_position_transaction_id);
386 create_pte_shadow(p_position_transaction_id);
387 create_tjr_shadow(p_position_transaction_id);
388 end if;
389 exception when others then
390 p_position_transaction_id := null;
391 raise;
392 end;
393 --
394 /*
395 PROCEDURE create_shadow_record(p_position_transaction_id number,p_position_id number) IS
396
397 cursor c1 is
398 select * from hr_positions
399 where position_id=p_position_id;
400
401 cursor c2 is
402 select *
403 from per_deployment_factors
404 where position_id=p_position_id;
405
406 cursor c3 is
407 select *
408 from pqh_ptx_extra_info
409 where position_transaction_id=p_position_transaction_id;
410
411 rec1 c1%rowtype;
412 rec2 c2%rowtype;
413
414 BEGIN
415
416 open c1;
417 fetch c1 into rec1;
418 close c1;
419
420 open c2;
421 fetch c2 into rec2;
422 close c2;
423
424 insert into pqh_ptx_shadow
425 (
426 AMENDMENT_DATE,
427 AMENDMENT_RECOMMENDATION,
428 AMENDMENT_REF_NUMBER,
429 ATTRIBUTE1,
430 ATTRIBUTE10,
431 ATTRIBUTE11,
432 ATTRIBUTE12,
433 ATTRIBUTE13,
434 ATTRIBUTE14,
435 ATTRIBUTE15,
436 ATTRIBUTE16,
437 ATTRIBUTE17,
438 ATTRIBUTE18,
439 ATTRIBUTE19,
440 ATTRIBUTE2,
441 ATTRIBUTE20,
442 ATTRIBUTE21,
443 ATTRIBUTE22,
444 ATTRIBUTE23,
445 ATTRIBUTE24,
446 ATTRIBUTE25,
447 ATTRIBUTE26,
448 ATTRIBUTE27,
449 ATTRIBUTE28,
450 ATTRIBUTE29,
451 ATTRIBUTE3,
452 ATTRIBUTE30,
453 ATTRIBUTE4,
454 ATTRIBUTE5,
455 ATTRIBUTE6,
456 ATTRIBUTE7,
457 ATTRIBUTE8,
458 ATTRIBUTE9,
459 ATTRIBUTE_CATEGORY,
460 AVAILABILITY_STATUS_ID,
461 AVAIL_STATUS_PROP_END_DATE,
462 BARGAINING_UNIT_CD,
463 BUSINESS_GROUP_ID,
464 COMMENTS,
465 CURRENT_JOB_PROP_END_DATE,
466 CURRENT_ORG_PROP_END_DATE,
467 DATE_EFFECTIVE,
468 DATE_END,
469 EARLIEST_HIRE_DATE,
470 ENTRY_GRADE_ID,
471 ENTRY_GRADE_RULE_ID,
472 ENTRY_STEP_ID,
473 FILL_BY_DATE,
474 FREQUENCY,
475 FTE,
476 FTE_CAPACITY,
477 INFORMATION1,
478 INFORMATION10,
479 INFORMATION11,
480 INFORMATION12,
481 INFORMATION13,
482 INFORMATION14,
483 INFORMATION15,
484 INFORMATION16,
485 INFORMATION17,
486 INFORMATION18,
487 INFORMATION19,
488 INFORMATION2,
489 INFORMATION20,
490 INFORMATION21,
491 INFORMATION22,
492 INFORMATION23,
493 INFORMATION24,
494 INFORMATION25,
495 INFORMATION26,
496 INFORMATION27,
497 INFORMATION28,
498 INFORMATION29,
499 INFORMATION3,
500 INFORMATION30,
501 INFORMATION4,
502 INFORMATION5,
503 INFORMATION6,
504 INFORMATION7,
505 INFORMATION8,
506 INFORMATION9,
507 INFORMATION_CATEGORY,
508 JOB_ID,
509 LOCATION_ID,
510 MAX_PERSONS,
511 NAME,
512 ORGANIZATION_ID,
513 OVERLAP_PERIOD,
514 OVERLAP_UNIT_CD,
515 PASSPORT_REQUIRED,
516 PAY_BASIS_ID,
517 PAY_FREQ_PAYROLL_ID,
518 PAY_TERM_END_DAY_CD,
519 PAY_TERM_END_MONTH_CD,
520 PERMANENT_TEMPORARY_FLAG,
521 PERMIT_RECRUITMENT_FLAG,
522 POSITION_DEFINITION_ID,
523 POSITION_ID,
524 POSITION_TRANSACTION_ID,
525 POSITION_TYPE,
526 POSTING_DESCRIPTION,
527 PRIOR_POSITION_ID,
528 PROBATION_PERIOD,
529 PROBATION_PERIOD_UNIT_CD,
530 PROPOSED_DATE_FOR_LAYOFF,
531 PROPOSED_FTE_FOR_LAYOFF,
532 RELIEF_POSITION_ID,
533 RELOCATE_DOMESTICALLY,
534 RELOCATE_INTERNATIONALLY,
535 RELOCATION_REQUIRED,
536 REPLACEMENT_REQUIRED_FLAG,
537 REVIEW_FLAG,
538 SEASONAL_FLAG,
539 SECURITY_REQUIREMENTS,
540 SUCCESSOR_POSITION_ID,
541 SUPERVISOR_ID,
542 SUPERVISOR_POSITION_ID,
543 SERVICE_MINIMUM,
544 TERM_START_DAY_CD,
545 TERM_START_MONTH_CD,
546 TIME_NORMAL_FINISH,
547 TIME_NORMAL_START,
548 TRAVEL_REQUIRED,
549 VISIT_INTERNATIONALLY,
550 WORKING_HOURS,
551 WORKS_COUNCIL_APPROVAL_FLAG,
552 WORK_ANY_COUNTRY,
553 WORK_ANY_LOCATION,
554 WORK_DURATION,
555 WORK_PERIOD_TYPE_CD,
556 WORK_TERM_END_DAY_CD,
557 WORK_TERM_END_MONTH_CD,
558 object_version_number)
559 values
560 (
561 rec1.AMENDMENT_DATE,
562 rec1.AMENDMENT_RECOMMENDATION,
563 rec1.AMENDMENT_REF_NUMBER,
564 rec1.ATTRIBUTE1,
565 rec1.ATTRIBUTE10,
566 rec1.ATTRIBUTE11,
567 rec1.ATTRIBUTE12,
568 rec1.ATTRIBUTE13,
569 rec1.ATTRIBUTE14,
570 rec1.ATTRIBUTE15,
571 rec1.ATTRIBUTE16,
572 rec1.ATTRIBUTE17,
573 rec1.ATTRIBUTE18,
574 rec1.ATTRIBUTE19,
575 rec1.ATTRIBUTE2,
576 rec1.ATTRIBUTE20,
577 rec1.ATTRIBUTE21,
578 rec1.ATTRIBUTE22,
579 rec1.ATTRIBUTE23,
580 rec1.ATTRIBUTE24,
581 rec1.ATTRIBUTE25,
582 rec1.ATTRIBUTE26,
583 rec1.ATTRIBUTE27,
584 rec1.ATTRIBUTE28,
585 rec1.ATTRIBUTE29,
586 rec1.ATTRIBUTE3,
587 rec1.ATTRIBUTE30,
588 rec1.ATTRIBUTE4,
589 rec1.ATTRIBUTE5,
590 rec1.ATTRIBUTE6,
591 rec1.ATTRIBUTE7,
592 rec1.ATTRIBUTE8,
593 rec1.ATTRIBUTE9,
594 rec1.ATTRIBUTE_CATEGORY,
595 rec1.AVAILABILITY_STATUS_ID,
596 rec1.AVAIL_STATUS_PROP_END_DATE,
597 rec1.BARGAINING_UNIT_CD,
598 rec1.BUSINESS_GROUP_ID,
599 rec1.COMMENTS,
600 rec1.CURRENT_JOB_PROP_END_DATE,
601 rec1.CURRENT_ORG_PROP_END_DATE,
602 rec1.DATE_EFFECTIVE,
603 rec1.DATE_END,
604 rec1.EARLIEST_HIRE_DATE,
605 rec1.ENTRY_GRADE_ID,
606 rec1.ENTRY_GRADE_RULE_ID,
607 rec1.ENTRY_STEP_ID,
608 rec1.FILL_BY_DATE,
609 rec1.FREQUENCY,
610 rec1.FTE,
611 rec2.FTE_CAPACITY,
612 rec1.INFORMATION1,
613 rec1.INFORMATION10,
614 rec1.INFORMATION11,
615 rec1.INFORMATION12,
616 rec1.INFORMATION13,
617 rec1.INFORMATION14,
618 rec1.INFORMATION15,
619 rec1.INFORMATION16,
620 rec1.INFORMATION17,
621 rec1.INFORMATION18,
622 rec1.INFORMATION19,
623 rec1.INFORMATION2,
624 rec1.INFORMATION20,
625 rec1.INFORMATION21,
626 rec1.INFORMATION22,
627 rec1.INFORMATION23,
628 rec1.INFORMATION24,
629 rec1.INFORMATION25,
630 rec1.INFORMATION26,
631 rec1.INFORMATION27,
632 rec1.INFORMATION28,
633 rec1.INFORMATION29,
634 rec1.INFORMATION3,
635 rec1.INFORMATION30,
636 rec1.INFORMATION4,
637 rec1.INFORMATION5,
638 rec1.INFORMATION6,
639 rec1.INFORMATION7,
640 rec1.INFORMATION8,
641 rec1.INFORMATION9,
642 rec1.INFORMATION_CATEGORY,
643 rec1.JOB_ID,
644 rec1.LOCATION_ID,
645 rec1.MAX_PERSONS,
646 rec1.NAME,
647 rec1.ORGANIZATION_ID,
648 rec1.OVERLAP_PERIOD,
649 rec1.OVERLAP_UNIT_CD,
650 rec2.PASSPORT_REQUIRED,
651 rec1.PAY_BASIS_ID,
652 rec1.PAY_FREQ_PAYROLL_ID,
653 rec1.PAY_TERM_END_DAY_CD,
654 rec1.PAY_TERM_END_MONTH_CD,
655 rec1.PERMANENT_TEMPORARY_FLAG,
656 rec1.PERMIT_RECRUITMENT_FLAG,
657 rec1.POSITION_DEFINITION_ID,
658 p_POSITION_ID,
659 p_position_transaction_id,
660 rec1.POSITION_TYPE,
661 rec1.POSTING_DESCRIPTION,
662 rec1.PRIOR_POSITION_ID,
663 rec1.PROBATION_PERIOD,
664 rec1.PROBATION_PERIOD_UNIT_CD,
665 rec1.PROPOSED_DATE_FOR_LAYOFF,
666 rec1.PROPOSED_FTE_FOR_LAYOFF,
667 rec1.RELIEF_POSITION_ID,
668 rec2.RELOCATE_DOMESTICALLY,
669 rec2.RELOCATE_INTERNATIONALLY,
670 rec2.RELOCATION_REQUIRED,
671 rec1.REPLACEMENT_REQUIRED_FLAG,
672 rec1.REVIEW_FLAG,
673 rec1.SEASONAL_FLAG,
674 rec1.SECURITY_REQUIREMENTS,
675 rec1.SUCCESSOR_POSITION_ID,
676 rec1.SUPERVISOR_ID,
677 rec1.SUPERVISOR_POSITION_ID,
678 rec2.SERVICE_MINIMUM,
679 rec1.TERM_START_DAY_CD,
680 rec1.TERM_START_MONTH_CD,
681 rec1.TIME_NORMAL_FINISH,
682 rec1.TIME_NORMAL_START,
683 rec2.TRAVEL_REQUIRED,
684 rec2.VISIT_INTERNATIONALLY,
685 rec1.WORKING_HOURS,
686 rec1.WORKS_COUNCIL_APPROVAL_FLAG,
687 rec2.WORK_ANY_COUNTRY,
688 rec2.WORK_ANY_LOCATION,
689 rec2.WORK_DURATION,
690 rec1.WORK_PERIOD_TYPE_CD,
691 rec1.WORK_TERM_END_DAY_CD,
692 rec1.WORK_TERM_END_MONTH_CD,
693 1);
694 --
695 for rec3 in c3 loop
696 insert into pqh_pte_shadow
697 (ptx_extra_info_id, information_type, position_transaction_id, position_extra_info_id,
698 information_category, information1, information2, information3, information4, information5,
699 information6, information7, information8, information9, information10,
700 information11, information12, information13, information14, information15,
701 information16, information17, information18, information19, information20,
702 information21, information22, information23, information24, information25,
703 information26, information27, information28, information29, information30,
704 attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5,
705 attribute6, attribute7, attribute8, attribute9, attribute10,
706 attribute11, attribute12, attribute13, attribute14, attribute15,
707 attribute16, attribute17, attribute18, attribute19, attribute20,
708 object_version_number)
709 values
710 (rec3.ptx_extra_info_id, rec3.information_type, rec3.position_transaction_id, rec3.position_extra_info_id,
711 rec3.information_category,
712 rec3.information1, rec3.information2, rec3.information3, rec3.information4, rec3.information5,
713 rec3.information6, rec3.information7, rec3.information8, rec3.information9, rec3.information10,
714 rec3.information11, rec3.information12, rec3.information13, rec3.information14, rec3.information15,
715 rec3.information16, rec3.information17, rec3.information18, rec3.information19, rec3.information20,
716 rec3.information21, rec3.information22, rec3.information23, rec3.information24, rec3.information25,
717 rec3.information26, rec3.information27, rec3.information28, rec3.information29, rec3.information30,
718 rec3.attribute_category, rec3.attribute1, rec3.attribute2, rec3.attribute3, rec3.attribute4, rec3.attribute5,
719 rec3.attribute6, rec3.attribute7, rec3.attribute8, rec3.attribute9, rec3.attribute10,
720 rec3.attribute11, rec3.attribute12, rec3.attribute13, rec3.attribute14, rec3.attribute15,
721 rec3.attribute16, rec3.attribute17, rec3.attribute18, rec3.attribute19, rec3.attribute20,
722 1);
723 end loop;
724 --
725 END;
726 */
727 --
728 -- ----------------------------------------------------------------------------
729 -- |----------------------------< populate_pei >------------------------------|
730 -- ----------------------------------------------------------------------------
731
732 procedure populate_pei
733 (
734 p_position_transaction_id in pqh_position_transactions.position_transaction_id%TYPE,
735 p_position_id in pqh_position_transactions.position_id%TYPE,
736 p_populated in out nocopy varchar2
737 ) is
738
739 /*
740
741 This procedure will be called from the Position Transaction Form.
742 In the case of update template, the user selects an existing position.
743 we will fetch corresponding records from per_position_extra_info table.
744 With the position_transaction_id id and the position_id of current position,
745 we will insert records in the pqh_ptx_extra_info table.
746
747 */
748
749 -- declare variables
750 l_ptx_extra_info_id pqh_ptx_extra_info.ptx_extra_info_id%TYPE;
751 l_object_version_number pqh_ptx_extra_info.object_version_number%TYPE := 1;
752
753 CURSOR c1 IS
754 select *
755 from per_position_extra_info pei
756 where information_type <> 'PQH_POS_ROLE_ID'
757 and position_id = p_position_id
758 and not exists
759 (select null from pqh_pte_shadow pps where position_transaction_id = p_position_transaction_id
760 and pps.position_extra_info_id = pei.position_extra_info_id);
761 --
762 l_pte_context_desc varchar2(1000);
763
764 begin
765
766 --
767
768 FOR v_poei_rec in c1 loop
769 -- select the sequence number for pqh_ptx_extra_info
770 select pqh_ptx_extra_info_s.nextval
771 into l_ptx_extra_info_id
772 from dual;
773
774 -- insert record into pqh_ptx_extra_info
775
776 insert into pqh_ptx_extra_info
777 (
778 PTX_EXTRA_INFO_ID,
779 INFORMATION_TYPE,
780 POSITION_TRANSACTION_ID,
781 POSITION_EXTRA_INFO_ID,
782 INFORMATION_CATEGORY,
783 INFORMATION1,
784 INFORMATION2,
785 INFORMATION3,
786 INFORMATION4,
787 INFORMATION5,
788 INFORMATION6,
789 INFORMATION7,
790 INFORMATION8,
791 INFORMATION9,
792 INFORMATION10,
793 INFORMATION11,
794 INFORMATION12,
795 INFORMATION13,
796 INFORMATION14,
797 INFORMATION15,
798 INFORMATION16,
799 INFORMATION17,
800 INFORMATION18,
801 INFORMATION19,
802 INFORMATION20,
803 INFORMATION21,
804 INFORMATION22,
805 INFORMATION23,
806 INFORMATION24,
807 INFORMATION25,
808 INFORMATION26,
809 INFORMATION27,
810 INFORMATION28,
811 INFORMATION29,
812 INFORMATION30,
813 ATTRIBUTE_CATEGORY,
814 ATTRIBUTE1,
815 ATTRIBUTE2,
816 ATTRIBUTE3,
817 ATTRIBUTE4,
818 ATTRIBUTE5,
819 ATTRIBUTE6,
820 ATTRIBUTE7,
821 ATTRIBUTE8,
822 ATTRIBUTE9,
823 ATTRIBUTE10,
824 ATTRIBUTE11,
825 ATTRIBUTE12,
826 ATTRIBUTE13,
827 ATTRIBUTE14,
828 ATTRIBUTE15,
829 ATTRIBUTE16,
830 ATTRIBUTE17,
831 ATTRIBUTE18,
832 ATTRIBUTE19,
833 ATTRIBUTE20,
834 OBJECT_VERSION_NUMBER
835 )
836 values
837 (
838 l_ptx_extra_info_id,
839 v_poei_rec.information_type,
840 p_position_transaction_id,
841 v_poei_rec.POSITION_EXTRA_INFO_ID,
842 v_poei_rec.POEI_INFORMATION_CATEGORY,
843 v_poei_rec.POEI_INFORMATION1,
844 v_poei_rec.POEI_INFORMATION2,
845 v_poei_rec.POEI_INFORMATION3,
846 v_poei_rec.POEI_INFORMATION4,
847 v_poei_rec.POEI_INFORMATION5,
848 v_poei_rec.POEI_INFORMATION6,
849 v_poei_rec.POEI_INFORMATION7,
850 v_poei_rec.POEI_INFORMATION8,
851 v_poei_rec.POEI_INFORMATION9,
852 v_poei_rec.POEI_INFORMATION10,
853 v_poei_rec.POEI_INFORMATION11,
854 v_poei_rec.POEI_INFORMATION12,
855 v_poei_rec.POEI_INFORMATION13,
856 v_poei_rec.POEI_INFORMATION14,
857 v_poei_rec.POEI_INFORMATION15,
858 v_poei_rec.POEI_INFORMATION16,
859 v_poei_rec.POEI_INFORMATION17,
860 v_poei_rec.POEI_INFORMATION18,
861 v_poei_rec.POEI_INFORMATION19,
862 v_poei_rec.POEI_INFORMATION20,
863 v_poei_rec.POEI_INFORMATION21,
864 v_poei_rec.POEI_INFORMATION22,
865 v_poei_rec.POEI_INFORMATION23,
866 v_poei_rec.POEI_INFORMATION24,
867 v_poei_rec.POEI_INFORMATION25,
868 v_poei_rec.POEI_INFORMATION26,
869 v_poei_rec.POEI_INFORMATION27,
870 v_poei_rec.POEI_INFORMATION28,
871 v_poei_rec.POEI_INFORMATION29,
872 v_poei_rec.POEI_INFORMATION30,
873 v_poei_rec.POEI_ATTRIBUTE_CATEGORY,
874 v_poei_rec.POEI_ATTRIBUTE1,
875 v_poei_rec.POEI_ATTRIBUTE2,
876 v_poei_rec.POEI_ATTRIBUTE3,
877 v_poei_rec.POEI_ATTRIBUTE4,
878 v_poei_rec.POEI_ATTRIBUTE5,
879 v_poei_rec.POEI_ATTRIBUTE6,
880 v_poei_rec.POEI_ATTRIBUTE7,
881 v_poei_rec.POEI_ATTRIBUTE8,
882 v_poei_rec.POEI_ATTRIBUTE9,
883 v_poei_rec.POEI_ATTRIBUTE10,
884 v_poei_rec.POEI_ATTRIBUTE11,
885 v_poei_rec.POEI_ATTRIBUTE12,
886 v_poei_rec.POEI_ATTRIBUTE13,
887 v_poei_rec.POEI_ATTRIBUTE14,
888 v_poei_rec.POEI_ATTRIBUTE15,
889 v_poei_rec.POEI_ATTRIBUTE16,
890 v_poei_rec.POEI_ATTRIBUTE17,
891 v_poei_rec.POEI_ATTRIBUTE18,
892 v_poei_rec.POEI_ATTRIBUTE19,
893 v_poei_rec.POEI_ATTRIBUTE20,
894 l_object_version_number
895 );
896
897 l_pte_context_desc := pqh_utility.get_pte_context_desc(l_ptx_extra_info_id);
898 append_if_not_present(p_populated, l_pte_context_desc);
899 END LOOP;
900
901 end populate_pei;
902 --
903 -- ----------------------------------------------------------------------------
904 -- |------------------< populate_job_requirements >---------------------------|
905 -- ----------------------------------------------------------------------------
906
907 procedure populate_job_requirements
908 (
909 p_position_transaction_id in pqh_position_transactions.position_transaction_id%TYPE,
910 p_position_id in pqh_position_transactions.position_id%TYPE,
911 p_populated in out nocopy varchar2
912 ) is
913
914 /*
915
916 This procedure will be called from the Position Transaction Form.
917 In the case of update template, the user selects an existing position.
918 we will fetch corresponding records from per_job_requirements table.
919 With the position_transaction_id and the position_id of current position,
920 we will insert records in the pqh_txn_job_requirements table.
921
922 */
923
924 -- declare variables
925 l_txn_job_requirement_id pqh_txn_job_requirements.txn_job_requirement_id%TYPE;
926 l_object_version_number pqh_txn_job_requirements.object_version_number%TYPE := 1;
927
928 CURSOR c1 IS
929 select *
930 from per_job_requirements pjr
931 where pjr.position_id = p_position_id
932 and not exists
933 (select null from pqh_tjr_shadow pts
934 where pts.position_transaction_id = p_position_transaction_id
935 and pts.job_requirement_id = pjr.job_requirement_id);
936
937 --
938 l_tjr_classification varchar2(1000);
939
940 begin
941
942 --
943
944 FOR r_jre in c1 loop
945 -- select the sequence number for pqh_txn_job_requirements
946 select pqh_txn_job_requirements_s.nextval
947 into l_txn_job_requirement_id
948 from dual;
949
950 -- insert record into pqh_txn_job_requirements
951
952 insert into pqh_txn_job_requirements
953 (
954 txn_job_requirement_id,
955 position_transaction_id,
956 job_requirement_id,
957 business_group_id,
958 analysis_criteria_id,
959 date_from,
960 date_to,
961 essential,
962 job_id,
963 object_version_number,
964 attribute_category,
965 attribute1,
966 attribute2,
967 attribute3,
968 attribute4,
969 attribute5,
970 attribute6,
971 attribute7,
972 attribute8,
973 attribute9,
974 attribute10,
975 attribute11,
976 attribute12,
977 attribute13,
978 attribute14,
979 attribute15,
980 attribute16,
981 attribute17,
982 attribute18,
983 attribute19,
984 attribute20,
985 comments
986 )
987 values
988 (
989 l_txn_job_requirement_id,
990 p_position_transaction_id,
991 r_jre.job_requirement_id,
992 r_jre.business_group_id,
993 r_jre.analysis_criteria_id,
994 r_jre.date_from,
995 r_jre.date_to,
996 r_jre.essential,
997 r_jre.job_id,
998 l_object_version_number,
999 r_jre.attribute_category,
1000 r_jre.attribute1,
1001 r_jre.attribute2,
1002 r_jre.attribute3,
1003 r_jre.attribute4,
1004 r_jre.attribute5,
1005 r_jre.attribute6,
1006 r_jre.attribute7,
1007 r_jre.attribute8,
1008 r_jre.attribute9,
1009 r_jre.attribute10,
1010 r_jre.attribute11,
1011 r_jre.attribute12,
1012 r_jre.attribute13,
1013 r_jre.attribute14,
1014 r_jre.attribute15,
1015 r_jre.attribute16,
1016 r_jre.attribute17,
1017 r_jre.attribute18,
1018 r_jre.attribute19,
1019 r_jre.attribute20,
1020 r_jre.comments
1021 );
1022
1023 l_tjr_classification := pqh_utility.get_tjr_classification(l_txn_job_requirement_id);
1024 append_if_not_present(p_populated, l_tjr_classification);
1025 END LOOP;
1026
1027 end populate_job_requirements;
1028
1029 -- ----------------------------------------------------------------------------
1030 -- |------------------------< alter_session_push >------------------------|
1031 -- ----------------------------------------------------------------------------
1032
1033 procedure alter_session_push is
1034
1035 /*
1036 This is called thru the Position Txn Form as a work around for bug 934616.
1037 When we query the pqh_position_transactions_v the session hangs. So as a
1038 work around we have to alter session set "_push_join_predicate"=FALSE
1039 */
1040 --
1041 -- local variables
1042 --
1043 l_proc varchar2(72) := g_package||'alter_session_push';
1044
1045
1046 BEGIN
1047 hr_utility.set_location('Entering:'||l_proc, 5);
1048
1049 EXECUTE IMMEDIATE
1050 'alter session set "_push_join_predicate"=FALSE';
1051
1052 hr_utility.set_location('Leaving:'||l_proc, 1000);
1053
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 Raise ;
1057 END alter_session_push;
1058
1059
1060
1061
1062 -- ----------------------------------------------------------------------------
1063 -- |------------------------< apply_transaction >------------------------|
1064 -- ----------------------------------------------------------------------------
1065
1066 function apply_transaction
1067 ( p_transaction_id in pqh_position_transactions.position_transaction_id%TYPE,
1068 p_validate_only in varchar2 default 'NO'
1069 ) return varchar2 is
1070 /*
1071
1072 This procedure will be called from the PQHPCTXN Position Transaction form
1073 when the user selects Apply Transaction option in the routing window.
1074 This procedure will determine whether it is an update transaction or
1075 a new position transaction and will call the apis to populate the
1076 underlying master tables.
1077 It will poppulate the following tables
1078 hr_all_positions_f
1079 per_positions
1080 per_position_extra_info
1081 per_deployment_factors
1082
1083 */
1084
1085 --
1086 -- local variables
1087 --
1088 l_proc varchar2(72) := g_package||'apply_transaction';
1089 l_txn_type varchar2(1);
1090 l_dpf_type varchar2(1);
1091 l_pei_type varchar2(1);
1092 l_update_mode varchar2(20);
1093 l_dummy varchar2(10);
1094 l_ptx_rec pqh_position_transactions%ROWTYPE;
1095 l_pte_rec pqh_ptx_extra_info%ROWTYPE;
1096 l_position_id hr_all_positions_f.position_id%TYPE;
1097 l_effective_start_date hr_all_positions_f.effective_start_date%TYPE;
1098 l_effective_end_date hr_all_positions_f.effective_end_date%TYPE;
1099 l_position_definition_id hr_all_positions_f.position_definition_id%TYPE;
1100 l_name hr_all_positions_f.name%TYPE;
1101 l_object_version_number hr_all_positions_f.object_version_number%TYPE;
1102 l_dpf_deployment_factor_id per_deployment_factors.deployment_factor_id%type;
1103 l_dpf_object_version_number per_deployment_factors.object_version_number%type;
1104 l_warning boolean;
1105 l_deployment_factor_id per_deployment_factors.deployment_factor_id%TYPE;
1106 l_position_extra_info_id per_position_extra_info.position_extra_info_id%TYPE;
1107 p_position_id hr_all_positions_f.position_id%TYPE;
1108 p_effective_start_date hr_all_positions_f.effective_start_date%TYPE;
1109 l_pei_position_extra_info_id per_position_extra_info.position_extra_info_id%type;
1110 l_pei_object_version_number per_position_extra_info.object_version_number%type;
1111 l_transaction_category_id pqh_transaction_categories.transaction_category_id%type;
1112 l_return varchar2(30) := 'SUCCESS';
1113 l_seasonal_dates_present boolean := false;
1114 l_overlap_dates_present boolean := false;
1115 l_position_family_flag boolean := false;
1116 l_permit_extended_pay boolean := false;
1117 l_start_date date;
1118 l_availability_status_id number;
1119 l_res_position_id number;
1120 l_res_fte number;
1121 l_res_position_type varchar2(30);
1122 l_res_effective_date date;
1123 l_res_validation_start_date date;
1124 l_res_validation_end_date date;
1125
1126 CURSOR c1 IS
1127 select *
1128 from pqh_position_transactions
1129 where position_transaction_id = p_transaction_id;
1130
1131 CURSOR c2(p_position_id IN hr_all_positions_f.position_id%TYPE,
1132 p_effective_date IN hr_all_positions_f.effective_start_date%TYPE) IS
1133 select effective_start_date, effective_end_date, object_version_number, date_effective, availability_status_id --'X'
1134 from hr_all_positions_f
1135 where position_id = p_position_id
1136 and p_effective_date
1137 between effective_start_date and effective_end_date ;
1138
1139 CURSOR c3 IS
1140 select *
1141 from pqh_ptx_extra_info
1142 where position_transaction_id = p_transaction_id;
1143
1144 CURSOR c_del_pte(p_transaction_id number, p_position_id number) IS
1145 select pei.position_extra_info_id, pei.object_version_number
1146 from per_position_extra_info pei, pqh_pte_shadow pps
1147 where position_id = p_position_id
1148 and position_transaction_id = p_transaction_id
1149 and pps.position_extra_info_id = pei.position_extra_info_id
1150 and not exists
1151 (select null
1152 from pqh_ptx_extra_info ppei
1153 where position_transaction_id = p_transaction_id
1154 and ppei.position_extra_info_id = pps.position_extra_info_id);
1155
1156 cursor c4 (p_position_id in hr_all_positions_f.position_id%TYPE)is
1157 select deployment_factor_id,object_version_number
1158 from per_deployment_factors
1159 where position_id = p_position_id;
1160
1161 CURSOR c5 (p_position_id in hr_all_positions_f.position_id%TYPE,
1162 p_information_type in per_position_extra_info.information_type%type,
1163 p_position_extra_info_id in
1164 per_position_extra_info.position_extra_info_id%type) IS
1165 select position_extra_info_id,object_version_number
1166 from per_position_extra_info
1167 where position_id = p_position_id
1168 and information_type = p_information_type
1169 and position_extra_info_id = p_position_extra_info_id;
1170
1171 /*
1172 CURSOR c_transaction_category is
1173 select transaction_category_id
1174 from pqh_transaction_categories
1175 where short_name = 'POSITION_TRANSACTION';
1176 */
1177 --
1178 cursor c_pes(p_ptx_extra_info_id number) is
1179 select *
1180 from pqh_pte_shadow
1181 where ptx_extra_info_id = p_ptx_extra_info_id;
1182 --
1183 cursor c_dpf_df(p_position_transaction_id number) is
1184 select *
1185 from pqh_ptx_dpf_df
1186 where position_transaction_id = p_position_transaction_id;
1187 --
1188 l_pes_rec c_pes%rowtype;
1189 l_items_changed varchar2(10000);
1190 l_dpf_df c_dpf_df%rowtype;
1191 --
1192 BEGIN
1193 hr_utility.set_location('Entering:'||l_proc, 5);
1194 --
1195 savepoint apply_transaction;
1196 /*
1197 Determine the transaction type i.e whether it is update transaction or
1198 a new transaction .
1199 If it is an update transactio the position_id will not be
1200 NULL. Alternatively if the position_id is NULL , then it is a new position
1201 transaction.
1202 */
1203
1204 OPEN c1;
1205 FETCH c1 INTO l_ptx_rec;
1206 CLOSE c1;
1207
1208 l_position_id := l_ptx_rec.position_id;
1209 l_position_definition_id := l_ptx_rec.position_definition_id;
1210 l_name := l_ptx_rec.name;
1211 --
1212 -- Start Position Transaction Log
1213 --
1214 pqh_process_batch_log.start_log (
1215 p_batch_id => p_transaction_id,
1216 p_module_cd => 'POSITION_TRANSACTION' ,
1217 p_log_context => l_ptx_rec.name || ' - ' || l_ptx_rec.position_type ) ;
1218
1219 --
1220 -- Select transaction_category_id
1221 --
1222 l_transaction_category_id := l_ptx_rec.wf_transaction_category_id;
1223 --
1224 IF l_ptx_rec.position_id IS NULL THEN
1225 -- create transaction
1226 l_txn_type := 'C';
1227 l_dpf_type := 'C';
1228 hr_utility.set_location('Txn Type is Create '||l_proc, 6);
1229 --
1230 open c_dpf_df(p_transaction_id);
1231 fetch c_dpf_df into l_dpf_df;
1232 close c_dpf_df;
1233 --
1234 ELSE
1235 -- update transaction
1236 l_txn_type := 'U';
1237 hr_utility.set_location('Txn Type is Update '||l_proc, 7);
1238 -- call_refresh before applying Update Transaction;
1239 pqh_ptx_utl.refresh_ptx(
1240 p_transaction_category_id => l_transaction_category_id,
1241 p_position_transaction_id => p_transaction_id,
1242 p_items_changed => l_items_changed);
1243 open c4(l_ptx_rec.position_id);
1244 fetch c4 into l_dpf_deployment_factor_id, l_dpf_object_version_number;
1245 close c4;
1246 --
1247 open c_dpf_df(p_transaction_id);
1248 fetch c_dpf_df into l_dpf_df;
1249 close c_dpf_df;
1250 --
1251 if l_dpf_deployment_factor_id is null then
1252 l_dpf_type := 'C';
1253 else
1254 l_dpf_type := 'U';
1255 end if;
1256 l_dummy := null;
1257 END IF;
1258
1259 /*
1260 Determine if UPDATE mode or CORRECTION mode in case of Update txn type
1261 This is done as follows :
1262 If for the action_date in the PTX table there exists a row in
1263 hr_all_positions_f
1264 table for the same position_id and effective_start_date = action_date
1265 of PTX , it will
1266 be correction , else it will be update
1267 */
1268
1269 IF l_txn_type = 'U' THEN
1270
1271 --
1272 -- TABLE : per_position_extra_info
1273 --
1274 -- For update transaction delete the position extra info if deleted from ptx extra info
1275 if l_position_id is not null then
1276 for r_del_pte in c_del_pte(p_transaction_id , l_position_id) loop
1277 hr_position_extra_info_api.delete_position_extra_info
1278 (p_validate => false
1279 ,p_position_extra_info_id => r_del_pte.position_extra_info_id
1280 ,p_object_version_number => r_del_pte.object_version_number
1281 );
1282 end loop;
1283 end if;
1284
1285 OPEN c2(p_position_id => l_ptx_rec.position_id,
1286 p_effective_date => l_ptx_rec.action_date);
1287
1288 FETCH c2 INTO l_effective_start_date, l_effective_end_date,
1289 l_object_version_number, l_start_date, l_availability_status_id;
1290
1291 CLOSE c2;
1292
1293 IF (l_effective_start_date = l_ptx_rec.action_date) or (nvl(l_start_date,l_ptx_rec.date_effective) <> l_ptx_rec.date_effective) THEN
1294 -- record exists so its correction
1295 l_update_mode := 'CORRECTION';
1296 hr_utility.set_location(' Correction Mode '||l_proc, 10);
1297 ELSIF (l_effective_end_date = to_date('4712/12/31','RRRR/MM/DD')) THEN
1298 -- no record exists so its update
1299 l_update_mode := 'UPDATE';
1300 hr_utility.set_location(' Update Mode '||l_proc, 11);
1301 ELSE
1302 l_update_mode := 'UPDATE_CHANGE_INSERT';
1303 hr_utility.set_location(' Update Change Insert Mode '||l_proc, 11);
1304 END IF; -- for updt mode
1305
1306 END IF; -- for txn_type as update
1307
1308 /*
1309 Depending on the txn type call the respective APIs
1310 */
1311
1312 --
1313 -- TABLE : hr_all_positions_f AND per_positions ( Common API )
1314 --
1315
1316 IF l_txn_type = 'C' THEN
1317 hr_utility.set_location('Calling create_dt_position '||l_proc, 15);
1318 --
1319 -- call create API
1320 --
1321 hr_position_api.create_position
1322 (p_position_id => l_position_id
1323 ,p_effective_start_date => l_effective_start_date
1324 ,p_effective_end_date => l_effective_end_date
1325 ,p_position_definition_id => l_position_definition_id
1326 ,p_name => l_name
1327 ,p_object_version_number => l_object_version_number
1328 ,p_job_id => l_ptx_rec.job_id
1329 ,p_organization_id => l_ptx_rec.organization_id
1330 ,p_effective_date => l_ptx_rec.action_date
1331 ,p_date_effective => l_ptx_rec.date_effective
1332 ,p_availability_status_id => l_ptx_rec.availability_status_id
1333 ,p_business_group_id => l_ptx_rec.business_group_id
1334 ,p_entry_step_id => l_ptx_rec.entry_step_id
1335 ,p_entry_grade_rule_id => l_ptx_rec.entry_grade_rule_id
1336 ,p_location_id => l_ptx_rec.location_id
1337 ,p_pay_freq_payroll_id => l_ptx_rec.pay_freq_payroll_id
1338 ,p_position_transaction_id => l_ptx_rec.position_transaction_id
1339 ,p_prior_position_id => l_ptx_rec.prior_position_id
1340 ,p_relief_position_id => l_ptx_rec.relief_position_id
1341 ,p_entry_grade_id => l_ptx_rec.entry_grade_id
1342 ,p_successor_position_id => l_ptx_rec.successor_position_id
1343 ,p_supervisor_position_id => l_ptx_rec.supervisor_position_id
1344 ,p_amendment_date => l_ptx_rec.amendment_date
1345 ,p_amendment_recommendation => l_ptx_rec.amendment_recommendation
1346 ,p_amendment_ref_number => l_ptx_rec.amendment_ref_number
1347 ,p_bargaining_unit_cd => l_ptx_rec.bargaining_unit_cd
1348 ,p_comments => l_ptx_rec.comments
1349 ,p_current_job_prop_end_date => l_ptx_rec.current_job_prop_end_date
1350 ,p_current_org_prop_end_date => l_ptx_rec.current_org_prop_end_date
1351 ,p_avail_status_prop_end_date => l_ptx_rec.avail_status_prop_end_date
1352 ,p_date_end => l_ptx_rec.date_end
1353 ,p_earliest_hire_date => l_ptx_rec.earliest_hire_date
1354 ,p_fill_by_date => l_ptx_rec.fill_by_date
1355 ,p_frequency => l_ptx_rec.frequency
1356 ,p_fte => l_ptx_rec.fte
1357 ,p_max_persons => l_ptx_rec.max_persons
1358 ,p_overlap_period => l_ptx_rec.overlap_period
1359 ,p_overlap_unit_cd => l_ptx_rec.overlap_unit_cd
1360 ,p_pay_term_end_day_cd => l_ptx_rec.pay_term_end_day_cd
1361 ,p_pay_term_end_month_cd => l_ptx_rec.pay_term_end_month_cd
1362 ,p_permanent_temporary_flag => l_ptx_rec.permanent_temporary_flag
1363 ,p_permit_recruitment_flag => l_ptx_rec.permit_recruitment_flag
1364 ,p_position_type => l_ptx_rec.position_type
1365 ,p_posting_description => l_ptx_rec.posting_description
1366 ,p_probation_period => l_ptx_rec.probation_period
1367 ,p_probation_period_unit_cd => l_ptx_rec.probation_period_unit_cd
1368 ,p_replacement_required_flag => l_ptx_rec.replacement_required_flag
1369 ,p_review_flag => 'N' --l_ptx_rec.review_flag
1370 ,p_seasonal_flag => l_ptx_rec.seasonal_flag
1371 ,p_security_requirements => l_ptx_rec.security_requirements
1372 ,p_status => NULL
1373 ,p_term_start_day_cd => l_ptx_rec.term_start_day_cd
1374 ,p_term_start_month_cd => l_ptx_rec.term_start_month_cd
1375 ,p_time_normal_finish => l_ptx_rec.time_normal_finish
1376 ,p_time_normal_start => l_ptx_rec.time_normal_start
1377 ,p_update_source_cd => NULL
1378 ,p_working_hours => l_ptx_rec.working_hours
1379 ,p_works_council_approval_flag => l_ptx_rec.works_council_approval_flag
1380 ,p_work_period_type_cd => l_ptx_rec.work_period_type_cd
1381 ,p_work_term_end_day_cd => l_ptx_rec.work_term_end_day_cd
1382 ,p_work_term_end_month_cd => l_ptx_rec.work_term_end_month_cd
1383 ,p_concat_segments => l_ptx_rec.name
1384 ,p_proposed_fte_for_layoff => l_ptx_rec.proposed_fte_for_layoff
1385 ,p_proposed_date_for_layoff => l_ptx_rec.proposed_date_for_layoff
1386 ,p_pay_basis_id => l_ptx_rec.pay_basis_id
1387 ,p_supervisor_id => l_ptx_rec.supervisor_id
1388 ,p_information1 => l_ptx_rec.information1
1389 ,p_information2 => l_ptx_rec.information2
1390 ,p_information3 => l_ptx_rec.information3
1391 ,p_information4 => l_ptx_rec.information4
1392 ,p_information5 => l_ptx_rec.information5
1393 ,p_information6 => l_ptx_rec.information6
1394 ,p_information7 => l_ptx_rec.information7
1395 ,p_information8 => l_ptx_rec.information8
1396 ,p_information9 => l_ptx_rec.information9
1397 ,p_information10 => l_ptx_rec.information10
1398 ,p_information11 => l_ptx_rec.information11
1399 ,p_information12 => l_ptx_rec.information12
1400 ,p_information13 => l_ptx_rec.information13
1401 ,p_information14 => l_ptx_rec.information14
1402 ,p_information15 => l_ptx_rec.information15
1403 ,p_information16 => l_ptx_rec.information16
1404 ,p_information17 => l_ptx_rec.information17
1405 ,p_information18 => l_ptx_rec.information18
1406 ,p_information19 => l_ptx_rec.information19
1407 ,p_information20 => l_ptx_rec.information20
1408 ,p_information21 => l_ptx_rec.information21
1409 ,p_information22 => l_ptx_rec.information22
1410 ,p_information23 => l_ptx_rec.information24
1411 ,p_information24 => l_ptx_rec.information23
1412 ,p_information25 => l_ptx_rec.information25
1413 ,p_information26 => l_ptx_rec.information26
1414 ,p_information27 => l_ptx_rec.information27
1415 ,p_information28 => l_ptx_rec.information28
1416 ,p_information29 => l_ptx_rec.information29
1417 ,p_information30 => l_ptx_rec.information30
1418 ,p_information_category => l_ptx_rec.information_category
1419 ,p_attribute1 => l_ptx_rec.attribute1
1420 ,p_attribute2 => l_ptx_rec.attribute2
1421 ,p_attribute3 => l_ptx_rec.attribute3
1422 ,p_attribute4 => l_ptx_rec.attribute4
1423 ,p_attribute5 => l_ptx_rec.attribute5
1424 ,p_attribute6 => l_ptx_rec.attribute6
1425 ,p_attribute7 => l_ptx_rec.attribute7
1426 ,p_attribute8 => l_ptx_rec.attribute8
1427 ,p_attribute9 => l_ptx_rec.attribute9
1428 ,p_attribute10 => l_ptx_rec.attribute10
1429 ,p_attribute11 => l_ptx_rec.attribute11
1430 ,p_attribute12 => l_ptx_rec.attribute12
1431 ,p_attribute13 => l_ptx_rec.attribute13
1432 ,p_attribute14 => l_ptx_rec.attribute14
1433 ,p_attribute15 => l_ptx_rec.attribute15
1434 ,p_attribute16 => l_ptx_rec.attribute16
1435 ,p_attribute17 => l_ptx_rec.attribute17
1436 ,p_attribute18 => l_ptx_rec.attribute18
1437 ,p_attribute19 => l_ptx_rec.attribute19
1438 ,p_attribute20 => l_ptx_rec.attribute20
1439 ,p_attribute21 => l_ptx_rec.attribute21
1440 ,p_attribute22 => l_ptx_rec.attribute22
1441 ,p_attribute23 => l_ptx_rec.attribute23
1442 ,p_attribute24 => l_ptx_rec.attribute24
1443 ,p_attribute25 => l_ptx_rec.attribute25
1444 ,p_attribute26 => l_ptx_rec.attribute26
1445 ,p_attribute27 => l_ptx_rec.attribute27
1446 ,p_attribute28 => l_ptx_rec.attribute28
1447 ,p_attribute29 => l_ptx_rec.attribute29
1448 ,p_attribute30 => l_ptx_rec.attribute30
1449 ,p_attribute_category => l_ptx_rec.attribute_category
1450 ) ;
1451 --
1452 hr_utility.set_location('POSITION ID is : '||l_position_id, 19);
1453 --
1454 update_pos_tran(p_position_transaction_id => l_ptx_rec.position_transaction_id,
1455 p_position_id => l_position_id,
1456 p_job_id => l_ptx_rec.job_id,
1457 p_organization_id => l_ptx_rec.organization_id,
1458 p_effective_date => l_ptx_rec.action_date);
1459 --
1460 --
1461 --rpullare Bug#2349744
1462
1463 UPDATE FND_ATTACHED_DOCUMENTS
1464 SET PK1_VALUE = l_position_id,
1465 ENTITY_NAME = 'PER_POSITIONS'
1466 WHERE PK1_VALUE = p_transaction_id
1467 AND ENTITY_NAME = 'PQH_POSITION_TRANSACTIONS_V';
1468
1469
1470
1471 --rpullare
1472 --
1473 hr_utility.set_location('Update Budget Positions', 20);
1474 --
1475 ELSE
1476 hr_utility.set_location('Calling update_dt_position '||l_proc, 20);
1477 --
1478 -- call update API
1479 --
1480
1481 hr_position_api.update_position
1482 (p_validate => false
1483 ,p_position_id => l_ptx_rec.position_id
1484 ,p_effective_start_date => l_effective_start_date
1485 ,p_effective_end_date => l_effective_end_date
1486 ,p_position_definition_id => l_position_definition_id
1487 ,p_valid_grades_changed_warning => l_warning
1488 ,p_name => l_name
1489 -- ,p_availability_status_id => l_availability_status_id
1490 ,p_entry_step_id => l_ptx_rec.entry_step_id
1491 ,p_entry_grade_rule_id => l_ptx_rec.entry_grade_rule_id
1492 ,p_location_id => l_ptx_rec.location_id
1493 ,p_pay_freq_payroll_id => l_ptx_rec.pay_freq_payroll_id
1494 ,p_position_transaction_id => l_ptx_rec.position_transaction_id
1495 ,p_prior_position_id => l_ptx_rec.prior_position_id
1496 ,p_relief_position_id => l_ptx_rec.relief_position_id
1497 ,p_entry_grade_id => l_ptx_rec.entry_grade_id
1498 ,p_successor_position_id => l_ptx_rec.successor_position_id
1499 ,p_supervisor_position_id => l_ptx_rec.supervisor_position_id
1500 ,p_amendment_date => l_ptx_rec.amendment_date
1501 ,p_amendment_recommendation => l_ptx_rec.amendment_recommendation
1502 ,p_amendment_ref_number => l_ptx_rec.amendment_ref_number
1503 ,p_bargaining_unit_cd => l_ptx_rec.bargaining_unit_cd
1504 ,p_comments => l_ptx_rec.comments
1505 ,p_current_job_prop_end_date => l_ptx_rec.current_job_prop_end_date
1506 ,p_current_org_prop_end_date => l_ptx_rec.current_org_prop_end_date
1507 ,p_avail_status_prop_end_date => l_ptx_rec.avail_status_prop_end_date
1508 ,p_date_effective => l_ptx_rec.date_effective
1509 ,p_date_end => l_ptx_rec.date_end
1510 ,p_earliest_hire_date => l_ptx_rec.earliest_hire_date
1511 ,p_fill_by_date => l_ptx_rec.fill_by_date
1512 ,p_frequency => l_ptx_rec.frequency
1513 ,p_fte => l_ptx_rec.fte
1514 ,p_max_persons => l_ptx_rec.max_persons
1515 ,p_overlap_period => l_ptx_rec.overlap_period
1516 ,p_overlap_unit_cd => l_ptx_rec.overlap_unit_cd
1517 ,p_pay_term_end_day_cd => l_ptx_rec.pay_term_end_day_cd
1518 ,p_pay_term_end_month_cd => l_ptx_rec.pay_term_end_month_cd
1519 ,p_permanent_temporary_flag => l_ptx_rec.permanent_temporary_flag
1520 ,p_permit_recruitment_flag => l_ptx_rec.permit_recruitment_flag
1521 ,p_position_type => l_ptx_rec.position_type
1522 ,p_posting_description => l_ptx_rec.posting_description
1523 ,p_probation_period => l_ptx_rec.probation_period
1524 ,p_probation_period_unit_cd => l_ptx_rec.probation_period_unit_cd
1525 ,p_replacement_required_flag => l_ptx_rec.replacement_required_flag
1526 ,p_review_flag => 'N' --l_ptx_rec.review_flag
1527 ,p_seasonal_flag => l_ptx_rec.seasonal_flag
1528 ,p_security_requirements => l_ptx_rec.security_requirements
1529 ,p_status => NULL
1530 ,p_term_start_day_cd => l_ptx_rec.term_start_day_cd
1531 ,p_term_start_month_cd => l_ptx_rec.term_start_month_cd
1532 ,p_time_normal_finish => l_ptx_rec.time_normal_finish
1533 ,p_time_normal_start => l_ptx_rec.time_normal_start
1534 ,p_update_source_cd => NULL
1535 ,p_working_hours => l_ptx_rec.working_hours
1536 ,p_works_council_approval_flag => l_ptx_rec.works_council_approval_flag
1537 ,p_work_period_type_cd => l_ptx_rec.work_period_type_cd
1538 ,p_work_term_end_day_cd => l_ptx_rec.work_term_end_day_cd
1539 ,p_work_term_end_month_cd => l_ptx_rec.work_term_end_month_cd
1540 ,p_concat_segments => l_ptx_rec.name
1541 ,p_object_version_number => l_object_version_number
1542 ,p_effective_date => l_ptx_rec.action_date
1543 ,p_datetrack_mode => l_update_mode
1544 ,p_proposed_fte_for_layoff => l_ptx_rec.proposed_fte_for_layoff
1545 ,p_proposed_date_for_layoff => l_ptx_rec.proposed_date_for_layoff
1546 ,p_pay_basis_id => l_ptx_rec.pay_basis_id
1547 ,p_supervisor_id => l_ptx_rec.supervisor_id
1548 ,p_information1 => l_ptx_rec.information1
1549 ,p_information2 => l_ptx_rec.information2
1550 ,p_information3 => l_ptx_rec.information3
1551 ,p_information4 => l_ptx_rec.information4
1552 ,p_information5 => l_ptx_rec.information5
1553 ,p_information6 => l_ptx_rec.information6
1554 ,p_information7 => l_ptx_rec.information7
1555 ,p_information8 => l_ptx_rec.information8
1556 ,p_information9 => l_ptx_rec.information9
1557 ,p_information10 => l_ptx_rec.information10
1558 ,p_information11 => l_ptx_rec.information11
1559 ,p_information12 => l_ptx_rec.information12
1560 ,p_information13 => l_ptx_rec.information13
1561 ,p_information14 => l_ptx_rec.information14
1562 ,p_information15 => l_ptx_rec.information15
1563 ,p_information16 => l_ptx_rec.information16
1564 ,p_information17 => l_ptx_rec.information17
1565 ,p_information18 => l_ptx_rec.information18
1566 ,p_information19 => l_ptx_rec.information19
1567 ,p_information20 => l_ptx_rec.information20
1568 ,p_information21 => l_ptx_rec.information21
1569 ,p_information22 => l_ptx_rec.information22
1570 ,p_information23 => l_ptx_rec.information24
1571 ,p_information24 => l_ptx_rec.information23
1572 ,p_information25 => l_ptx_rec.information25
1573 ,p_information26 => l_ptx_rec.information26
1574 ,p_information27 => l_ptx_rec.information27
1575 ,p_information28 => l_ptx_rec.information28
1576 ,p_information29 => l_ptx_rec.information29
1577 ,p_information30 => l_ptx_rec.information30
1578 ,p_information_category => l_ptx_rec.information_category
1579 ,p_attribute1 => l_ptx_rec.attribute1
1580 ,p_attribute2 => l_ptx_rec.attribute2
1581 ,p_attribute3 => l_ptx_rec.attribute3
1582 ,p_attribute4 => l_ptx_rec.attribute4
1583 ,p_attribute5 => l_ptx_rec.attribute5
1584 ,p_attribute6 => l_ptx_rec.attribute6
1585 ,p_attribute7 => l_ptx_rec.attribute7
1586 ,p_attribute8 => l_ptx_rec.attribute8
1587 ,p_attribute9 => l_ptx_rec.attribute9
1588 ,p_attribute10 => l_ptx_rec.attribute10
1589 ,p_attribute11 => l_ptx_rec.attribute11
1590 ,p_attribute12 => l_ptx_rec.attribute12
1591 ,p_attribute13 => l_ptx_rec.attribute13
1592 ,p_attribute14 => l_ptx_rec.attribute14
1593 ,p_attribute15 => l_ptx_rec.attribute15
1594 ,p_attribute16 => l_ptx_rec.attribute16
1595 ,p_attribute17 => l_ptx_rec.attribute17
1596 ,p_attribute18 => l_ptx_rec.attribute18
1597 ,p_attribute19 => l_ptx_rec.attribute19
1598 ,p_attribute20 => l_ptx_rec.attribute20
1599 ,p_attribute21 => l_ptx_rec.attribute21
1600 ,p_attribute22 => l_ptx_rec.attribute22
1601 ,p_attribute23 => l_ptx_rec.attribute23
1602 ,p_attribute24 => l_ptx_rec.attribute24
1603 ,p_attribute25 => l_ptx_rec.attribute25
1604 ,p_attribute26 => l_ptx_rec.attribute26
1605 ,p_attribute27 => l_ptx_rec.attribute27
1606 ,p_attribute28 => l_ptx_rec.attribute28
1607 ,p_attribute29 => l_ptx_rec.attribute29
1608 ,p_attribute30 => l_ptx_rec.attribute30
1609 ,p_attribute_category => l_ptx_rec.attribute_category
1610 );
1611 --
1612 l_res_position_id := l_ptx_rec.position_id;
1613 l_res_fte := l_ptx_rec.fte;
1614 l_res_position_type := l_ptx_rec.position_type;
1615 l_res_effective_date := l_ptx_rec.action_date;
1616 l_res_validation_start_date := l_effective_start_date;
1617 l_res_validation_end_date := l_effective_end_date;
1618 --
1619 if l_availability_status_id <> l_ptx_rec.availability_status_id then
1620 IF (l_effective_start_date = l_ptx_rec.action_date) THEN
1621 -- record exists so its correction
1622 l_update_mode := 'CORRECTION';
1623 hr_utility.set_location(' Correction Mode '||l_proc, 10);
1624 ELSIF (l_effective_end_date = to_date('4712/12/31','RRRR/MM/DD')) THEN
1625 -- no record exists so its update
1626 l_update_mode := 'UPDATE';
1627 hr_utility.set_location(' Update Mode '||l_proc, 11);
1628 ELSE
1629 l_update_mode := 'UPDATE_CHANGE_INSERT';
1630 hr_utility.set_location(' Update Change Insert Mode '||l_proc, 11);
1631 END IF; -- for updt mode
1632
1633 hr_position_api.update_position
1634 (p_validate => false
1635 ,p_position_id => l_ptx_rec.position_id
1636 ,p_effective_start_date => l_effective_start_date
1637 ,p_effective_end_date => l_effective_end_date
1638 ,p_position_definition_id => l_position_definition_id
1639 ,p_valid_grades_changed_warning => l_warning
1640 ,p_name => l_name
1641 ,p_availability_status_id => l_ptx_rec.availability_status_id
1642 ,p_entry_step_id => l_ptx_rec.entry_step_id
1643 ,p_entry_grade_rule_id => l_ptx_rec.entry_grade_rule_id
1644 ,p_location_id => l_ptx_rec.location_id
1645 ,p_pay_freq_payroll_id => l_ptx_rec.pay_freq_payroll_id
1646 ,p_position_transaction_id => l_ptx_rec.position_transaction_id
1647 ,p_prior_position_id => l_ptx_rec.prior_position_id
1648 ,p_relief_position_id => l_ptx_rec.relief_position_id
1649 ,p_entry_grade_id => l_ptx_rec.entry_grade_id
1650 ,p_successor_position_id => l_ptx_rec.successor_position_id
1651 ,p_supervisor_position_id => l_ptx_rec.supervisor_position_id
1652 ,p_amendment_date => l_ptx_rec.amendment_date
1653 ,p_amendment_recommendation => l_ptx_rec.amendment_recommendation
1654 ,p_amendment_ref_number => l_ptx_rec.amendment_ref_number
1655 ,p_bargaining_unit_cd => l_ptx_rec.bargaining_unit_cd
1656 ,p_comments => l_ptx_rec.comments
1657 ,p_current_job_prop_end_date => l_ptx_rec.current_job_prop_end_date
1658 ,p_current_org_prop_end_date => l_ptx_rec.current_org_prop_end_date
1659 ,p_avail_status_prop_end_date => l_ptx_rec.avail_status_prop_end_date
1660 ,p_date_effective => l_ptx_rec.date_effective
1661 ,p_date_end => l_ptx_rec.date_end
1662 ,p_earliest_hire_date => l_ptx_rec.earliest_hire_date
1663 ,p_fill_by_date => l_ptx_rec.fill_by_date
1664 ,p_frequency => l_ptx_rec.frequency
1665 ,p_fte => l_ptx_rec.fte
1666 ,p_max_persons => l_ptx_rec.max_persons
1667 ,p_overlap_period => l_ptx_rec.overlap_period
1668 ,p_overlap_unit_cd => l_ptx_rec.overlap_unit_cd
1669 ,p_pay_term_end_day_cd => l_ptx_rec.pay_term_end_day_cd
1670 ,p_pay_term_end_month_cd => l_ptx_rec.pay_term_end_month_cd
1671 ,p_permanent_temporary_flag => l_ptx_rec.permanent_temporary_flag
1672 ,p_permit_recruitment_flag => l_ptx_rec.permit_recruitment_flag
1673 ,p_position_type => l_ptx_rec.position_type
1674 ,p_posting_description => l_ptx_rec.posting_description
1675 ,p_probation_period => l_ptx_rec.probation_period
1676 ,p_probation_period_unit_cd => l_ptx_rec.probation_period_unit_cd
1677 ,p_replacement_required_flag => l_ptx_rec.replacement_required_flag
1678 ,p_review_flag => 'N' --l_ptx_rec.review_flag
1679 ,p_seasonal_flag => l_ptx_rec.seasonal_flag
1680 ,p_security_requirements => l_ptx_rec.security_requirements
1681 ,p_status => NULL
1682 ,p_term_start_day_cd => l_ptx_rec.term_start_day_cd
1683 ,p_term_start_month_cd => l_ptx_rec.term_start_month_cd
1684 ,p_time_normal_finish => l_ptx_rec.time_normal_finish
1685 ,p_time_normal_start => l_ptx_rec.time_normal_start
1686 ,p_update_source_cd => NULL
1687 ,p_working_hours => l_ptx_rec.working_hours
1688 ,p_works_council_approval_flag => l_ptx_rec.works_council_approval_flag
1689 ,p_work_period_type_cd => l_ptx_rec.work_period_type_cd
1690 ,p_work_term_end_day_cd => l_ptx_rec.work_term_end_day_cd
1691 ,p_work_term_end_month_cd => l_ptx_rec.work_term_end_month_cd
1692 ,p_concat_segments => l_ptx_rec.name
1693 ,p_object_version_number => l_object_version_number
1694 ,p_effective_date => l_ptx_rec.action_date
1695 ,p_datetrack_mode => l_update_mode
1696 ,p_proposed_fte_for_layoff => l_ptx_rec.proposed_fte_for_layoff
1697 ,p_proposed_date_for_layoff => l_ptx_rec.proposed_date_for_layoff
1698 ,p_pay_basis_id => l_ptx_rec.pay_basis_id
1699 ,p_supervisor_id => l_ptx_rec.supervisor_id
1700 ,p_information1 => l_ptx_rec.information1
1701 ,p_information2 => l_ptx_rec.information2
1702 ,p_information3 => l_ptx_rec.information3
1703 ,p_information4 => l_ptx_rec.information4
1704 ,p_information5 => l_ptx_rec.information5
1705 ,p_information6 => l_ptx_rec.information6
1706 ,p_information7 => l_ptx_rec.information7
1707 ,p_information8 => l_ptx_rec.information8
1708 ,p_information9 => l_ptx_rec.information9
1709 ,p_information10 => l_ptx_rec.information10
1710 ,p_information11 => l_ptx_rec.information11
1711 ,p_information12 => l_ptx_rec.information12
1712 ,p_information13 => l_ptx_rec.information13
1713 ,p_information14 => l_ptx_rec.information14
1714 ,p_information15 => l_ptx_rec.information15
1715 ,p_information16 => l_ptx_rec.information16
1716 ,p_information17 => l_ptx_rec.information17
1717 ,p_information18 => l_ptx_rec.information18
1718 ,p_information19 => l_ptx_rec.information19
1719 ,p_information20 => l_ptx_rec.information20
1720 ,p_information21 => l_ptx_rec.information21
1721 ,p_information22 => l_ptx_rec.information22
1722 ,p_information23 => l_ptx_rec.information24
1723 ,p_information24 => l_ptx_rec.information23
1724 ,p_information25 => l_ptx_rec.information25
1725 ,p_information26 => l_ptx_rec.information26
1726 ,p_information27 => l_ptx_rec.information27
1727 ,p_information28 => l_ptx_rec.information28
1728 ,p_information29 => l_ptx_rec.information29
1729 ,p_information30 => l_ptx_rec.information30
1730 ,p_information_category => l_ptx_rec.information_category
1731 ,p_attribute1 => l_ptx_rec.attribute1
1732 ,p_attribute2 => l_ptx_rec.attribute2
1733 ,p_attribute3 => l_ptx_rec.attribute3
1734 ,p_attribute4 => l_ptx_rec.attribute4
1735 ,p_attribute5 => l_ptx_rec.attribute5
1736 ,p_attribute6 => l_ptx_rec.attribute6
1737 ,p_attribute7 => l_ptx_rec.attribute7
1738 ,p_attribute8 => l_ptx_rec.attribute8
1739 ,p_attribute9 => l_ptx_rec.attribute9
1740 ,p_attribute10 => l_ptx_rec.attribute10
1741 ,p_attribute11 => l_ptx_rec.attribute11
1742 ,p_attribute12 => l_ptx_rec.attribute12
1743 ,p_attribute13 => l_ptx_rec.attribute13
1744 ,p_attribute14 => l_ptx_rec.attribute14
1745 ,p_attribute15 => l_ptx_rec.attribute15
1746 ,p_attribute16 => l_ptx_rec.attribute16
1747 ,p_attribute17 => l_ptx_rec.attribute17
1748 ,p_attribute18 => l_ptx_rec.attribute18
1749 ,p_attribute19 => l_ptx_rec.attribute19
1750 ,p_attribute20 => l_ptx_rec.attribute20
1751 ,p_attribute21 => l_ptx_rec.attribute21
1752 ,p_attribute22 => l_ptx_rec.attribute22
1753 ,p_attribute23 => l_ptx_rec.attribute23
1754 ,p_attribute24 => l_ptx_rec.attribute24
1755 ,p_attribute25 => l_ptx_rec.attribute25
1756 ,p_attribute26 => l_ptx_rec.attribute26
1757 ,p_attribute27 => l_ptx_rec.attribute27
1758 ,p_attribute28 => l_ptx_rec.attribute28
1759 ,p_attribute29 => l_ptx_rec.attribute29
1760 ,p_attribute30 => l_ptx_rec.attribute30
1761 ,p_attribute_category => l_ptx_rec.attribute_category
1762 );
1763 end if;
1764 --
1765 -- assign position_id to local variable as it will be used in the next apis
1766 --
1767 l_position_id := l_ptx_rec.position_id;
1768 hr_utility.set_location('POSITION ID is : '||l_position_id, 21);
1769 --
1770 --rpullare Bug#2349744
1771
1772 UPDATE FND_ATTACHED_DOCUMENTS
1773 SET PK1_VALUE = l_ptx_rec.position_id,
1774 ENTITY_NAME = 'PER_POSITIONS'
1775 WHERE PK1_VALUE = p_transaction_id
1776 AND ENTITY_NAME = 'PQH_POSITION_TRANSACTIONS_V';
1777
1778
1779 --rpullare
1780 --
1781 END IF; -- api call positions table
1782
1783
1784 --
1785 -- TABLE : per_deployment_factors
1786 -- In deployment factors , we will pass only the position_id as deployment
1787 -- is for a position_id OR person_id OR job_id
1788 -- Business Rule in per_deployment_factors :
1789 -- For position_id following fileds are NULL
1790 -- VISIT_INTERNATIONALLY, COMMENTS , EARLIEST_AVAILABLE_DATE
1791 --
1792
1793
1794 IF l_dpf_type = 'C' THEN
1795 hr_utility.set_location('Calling per_dpf_ins.ins '||l_proc, 20);
1796
1797 if (hr_psf_shd.get_availability_status(l_ptx_rec.availability_status_id,
1798 l_ptx_rec.business_group_id) <> 'ELIMINATED') then
1799 --
1800 -- call create API
1801 --
1802
1803 if (l_ptx_rec.work_any_country is not null
1804 or l_ptx_rec.work_any_location is not null
1805 or l_ptx_rec.relocate_domestically is not null
1806 or l_ptx_rec.relocate_internationally is not null
1807 or l_ptx_rec.travel_required is not null
1808 or l_ptx_rec.country1 is not null
1809 or l_ptx_rec.country2 is not null
1810 or l_ptx_rec.country3 is not null
1811 or l_ptx_rec.work_duration is not null
1812 or l_ptx_rec.work_schedule is not null
1813 or l_ptx_rec.working_hours is not null
1814 or l_ptx_rec.fte_capacity is not null
1815 or l_ptx_rec.relocation_required is not null
1816 or l_ptx_rec.passport_required is not null
1817 or l_ptx_rec.location1 is not null
1818 or l_ptx_rec.location2 is not null
1819 or l_ptx_rec.other_requirements is not null
1820 or l_ptx_rec.service_minimum is not null
1821 ) then
1822
1823 per_dpf_ins.ins
1824 (
1825 p_deployment_factor_id => l_deployment_factor_id,
1826 p_position_id => l_position_id,
1827 p_business_group_id => l_ptx_rec.business_group_id,
1828 p_work_any_country => nvl(l_ptx_rec.work_any_country,'N'),
1829 p_work_any_location => nvl(l_ptx_rec.work_any_location,'N'),
1830 p_relocate_domestically => nvl(l_ptx_rec.relocate_domestically,'N'),
1831 p_relocate_internationally => nvl(l_ptx_rec.relocate_internationally,'N'),
1832 p_travel_required => nvl(l_ptx_rec.travel_required,'N'),
1833 p_country1 => l_ptx_rec.country1,
1834 p_country2 => l_ptx_rec.country2,
1835 p_country3 => l_ptx_rec.country3,
1836 p_work_duration => l_ptx_rec.work_duration,
1837 p_work_schedule => l_ptx_rec.work_schedule,
1838 -- p_work_hours => l_ptx_rec.working_hours,
1839 p_fte_capacity => l_ptx_rec.fte_capacity,
1840 p_relocation_required => nvl(l_ptx_rec.relocation_required,'N'),
1841 p_passport_required => nvl(l_ptx_rec.passport_required,'N'),
1842 p_location1 => l_ptx_rec.location1,
1843 p_location2 => l_ptx_rec.location2,
1844 p_location3 => l_ptx_rec.location3,
1845 p_other_requirements => l_ptx_rec.other_requirements,
1846 p_service_minimum => l_ptx_rec.service_minimum,
1847 p_object_version_number => l_object_version_number,
1848 p_effective_date => l_ptx_rec.action_date
1849 ,p_attribute1 => l_dpf_df.attribute1
1850 ,p_attribute2 => l_dpf_df.attribute2
1851 ,p_attribute3 => l_dpf_df.attribute3
1852 ,p_attribute4 => l_dpf_df.attribute4
1853 ,p_attribute5 => l_dpf_df.attribute5
1854 ,p_attribute6 => l_dpf_df.attribute6
1855 ,p_attribute7 => l_dpf_df.attribute7
1856 ,p_attribute8 => l_dpf_df.attribute8
1857 ,p_attribute9 => l_dpf_df.attribute9
1858 ,p_attribute10 => l_dpf_df.attribute10
1859 ,p_attribute11 => l_dpf_df.attribute11
1860 ,p_attribute12 => l_dpf_df.attribute12
1861 ,p_attribute13 => l_dpf_df.attribute13
1862 ,p_attribute14 => l_dpf_df.attribute14
1863 ,p_attribute15 => l_dpf_df.attribute15
1864 ,p_attribute16 => l_dpf_df.attribute16
1865 ,p_attribute17 => l_dpf_df.attribute17
1866 ,p_attribute18 => l_dpf_df.attribute18
1867 ,p_attribute19 => l_dpf_df.attribute19
1868 ,p_attribute20 => l_dpf_df.attribute20
1869 ,p_attribute_category => l_dpf_df.attribute_category
1870 );
1871 --
1872 end if;
1873 end if;
1874 ELSE
1875 hr_utility.set_location('Calling per_dpf_upd.upd '||l_proc, 25);
1876 --
1877 -- call update API
1878 --
1879
1880 per_dpf_upd.upd
1881 (
1882 p_deployment_factor_id => l_dpf_deployment_factor_id,
1883 p_position_id => l_position_id,
1884 p_business_group_id => l_ptx_rec.business_group_id,
1885 p_work_any_country => nvl(l_ptx_rec.work_any_country,'N'),
1886 p_work_any_location => nvl(l_ptx_rec.work_any_location,'N'),
1887 p_relocate_domestically => nvl(l_ptx_rec.relocate_domestically,'N'),
1888 p_relocate_internationally => nvl(l_ptx_rec.relocate_internationally,'N'),
1889 p_travel_required => nvl(l_ptx_rec.travel_required,'N'),
1890 p_country1 => l_ptx_rec.country1,
1891 p_country2 => l_ptx_rec.country2,
1892 p_country3 => l_ptx_rec.country3,
1893 p_work_duration => l_ptx_rec.work_duration,
1894 p_work_schedule => l_ptx_rec.work_schedule,
1895 -- p_work_hours => l_ptx_rec.working_hours,
1896 p_fte_capacity => l_ptx_rec.fte_capacity,
1897 p_relocation_required => nvl(l_ptx_rec.relocation_required,'N'),
1898 p_passport_required => nvl(l_ptx_rec.passport_required,'N'),
1899 p_location1 => l_ptx_rec.location1,
1900 p_location2 => l_ptx_rec.location2,
1901 p_location3 => l_ptx_rec.location3,
1902 p_other_requirements => l_ptx_rec.other_requirements,
1903 p_service_minimum => l_ptx_rec.service_minimum,
1904 p_object_version_number => l_dpf_object_version_number,
1905 p_effective_date => l_ptx_rec.action_date
1906 ,p_attribute1 => l_dpf_df.attribute1
1907 ,p_attribute2 => l_dpf_df.attribute2
1908 ,p_attribute3 => l_dpf_df.attribute3
1909 ,p_attribute4 => l_dpf_df.attribute4
1910 ,p_attribute5 => l_dpf_df.attribute5
1911 ,p_attribute6 => l_dpf_df.attribute6
1912 ,p_attribute7 => l_dpf_df.attribute7
1913 ,p_attribute8 => l_dpf_df.attribute8
1914 ,p_attribute9 => l_dpf_df.attribute9
1915 ,p_attribute10 => l_dpf_df.attribute10
1916 ,p_attribute11 => l_dpf_df.attribute11
1917 ,p_attribute12 => l_dpf_df.attribute12
1918 ,p_attribute13 => l_dpf_df.attribute13
1919 ,p_attribute14 => l_dpf_df.attribute14
1920 ,p_attribute15 => l_dpf_df.attribute15
1921 ,p_attribute16 => l_dpf_df.attribute16
1922 ,p_attribute17 => l_dpf_df.attribute17
1923 ,p_attribute18 => l_dpf_df.attribute18
1924 ,p_attribute19 => l_dpf_df.attribute19
1925 ,p_attribute20 => l_dpf_df.attribute20
1926 ,p_attribute_category => l_dpf_df.attribute_category
1927 );
1928
1929 --
1930 END IF; -- api call per_deployment_factors
1931
1932
1933 --
1934 -- create/update the per_position_extra_info
1935 --
1936 OPEN c3;
1937 LOOP
1938 FETCH c3 INTO l_pte_rec;
1939 EXIT WHEN c3%NOTFOUND;
1940 l_pei_type := null;
1941
1942 if l_pte_rec.position_extra_info_id is null then
1943 l_pei_type := 'C';
1944 elsif l_txn_type = 'U' then
1945 --
1946 open c_pes(l_pte_rec.ptx_extra_info_id);
1947 fetch c_pes into l_pes_rec;
1948 close c_pes;
1949 --
1950 if l_pte_rec.object_version_number > l_pes_rec.object_version_number then
1951 l_pei_position_extra_info_id := null;
1952 open c5(l_ptx_rec.position_id,l_pte_rec.information_type,l_pte_rec.position_extra_info_id);
1953 fetch c5 into l_pei_position_extra_info_id,l_pei_object_version_number;
1954 close c5;
1955 --
1956 if l_pei_position_extra_info_id is null then
1957 l_pei_type := 'I';
1958 else
1959 l_pei_type := 'U';
1960 end if;
1961 end if;
1962 end if;
1963
1964 IF l_pei_type = 'I' THEN
1965 --
1966 hr_utility.set_location('Insert for pei_id '|| l_pei_position_extra_info_id
1967 ||l_proc, 20);
1968 insert into per_position_extra_info
1969 (
1970 position_extra_info_id, position_id, information_type,
1971 poei_attribute_category,
1972 poei_attribute1, poei_attribute2, poei_attribute3, poei_attribute4, poei_attribute5,
1973 poei_attribute6, poei_attribute7, poei_attribute8, poei_attribute9, poei_attribute10,
1974 poei_attribute11, poei_attribute12, poei_attribute13, poei_attribute14, poei_attribute15,
1975 poei_attribute16, poei_attribute17, poei_attribute18, poei_attribute19, poei_attribute20,
1976 poei_information_category,
1977 poei_information1, poei_information2, poei_information3,
1978 poei_information4, poei_information5,
1979 poei_information6, poei_information7, poei_information8,
1980 poei_information9, poei_information10,
1981 poei_information11, poei_information12, poei_information13,
1982 poei_information14, poei_information15,
1983 poei_information16, poei_information17, poei_information18,
1984 poei_information19, poei_information20,
1985 poei_information21, poei_information22, poei_information23,
1986 poei_information24, poei_information25,
1987 poei_information26, poei_information27, poei_information28,
1988 poei_information29, poei_information30,
1989 object_version_number
1990 )
1991 values
1992 (
1993 l_pte_rec.position_extra_info_id, l_position_id, l_pte_rec.information_type,
1994 l_pte_rec.attribute_category,
1995 l_pte_rec.attribute1, l_pte_rec.attribute2, l_pte_rec.attribute3, l_pte_rec.attribute4, l_pte_rec.attribute5,
1996 l_pte_rec.attribute6, l_pte_rec.attribute7, l_pte_rec.attribute8, l_pte_rec.attribute9, l_pte_rec.attribute10,
1997 l_pte_rec.attribute11, l_pte_rec.attribute12, l_pte_rec.attribute13, l_pte_rec.attribute14, l_pte_rec.attribute15,
1998 l_pte_rec.attribute16, l_pte_rec.attribute17, l_pte_rec.attribute18, l_pte_rec.attribute19, l_pte_rec.attribute20,
1999 l_pte_rec.information_category,
2000 l_pte_rec.information1, l_pte_rec.information2, l_pte_rec.information3,
2001 l_pte_rec.information4, l_pte_rec.information5,
2002 l_pte_rec.information6, l_pte_rec.information7, l_pte_rec.information8,
2003 l_pte_rec.information9, l_pte_rec.information10,
2004 l_pte_rec.information11, l_pte_rec.information12, l_pte_rec.information13,
2005 l_pte_rec.information14, l_pte_rec.information15,
2006 l_pte_rec.information16, l_pte_rec.information17, l_pte_rec.information18,
2007 l_pte_rec.information19, l_pte_rec.information20,
2008 l_pte_rec.information21, l_pte_rec.information22, l_pte_rec.information23,
2009 l_pte_rec.information24, l_pte_rec.information25,
2010 l_pte_rec.information26, l_pte_rec.information27, l_pte_rec.information28,
2011 l_pte_rec.information29, l_pte_rec.information30,
2012 1
2013 );
2014 --
2015 hr_utility.set_location('After Insert for pei_id '|| l_pei_position_extra_info_id
2016 ||l_proc, 25);
2017 ELSIF l_pei_type = 'C' THEN
2018 hr_utility.set_location('Calling create_position_extra_info.ins '
2019 ||l_proc, 30);
2020 --
2021 -- call create API
2022 --
2023 hr_position_extra_info_api.create_position_extra_info
2024 (p_validate => false
2025 ,p_position_id => l_position_id
2026 ,p_information_type => l_pte_rec.information_type
2027 ,p_poei_attribute_category => l_pte_rec.attribute_category
2028 ,p_poei_attribute1 => l_pte_rec.attribute1
2029 ,p_poei_attribute2 => l_pte_rec.attribute2
2030 ,p_poei_attribute3 => l_pte_rec.attribute3
2031 ,p_poei_attribute4 => l_pte_rec.attribute4
2032 ,p_poei_attribute5 => l_pte_rec.attribute5
2033 ,p_poei_attribute6 => l_pte_rec.attribute6
2034 ,p_poei_attribute7 => l_pte_rec.attribute7
2035 ,p_poei_attribute8 => l_pte_rec.attribute8
2036 ,p_poei_attribute9 => l_pte_rec.attribute9
2037 ,p_poei_attribute10 => l_pte_rec.attribute10
2038 ,p_poei_attribute11 => l_pte_rec.attribute11
2039 ,p_poei_attribute12 => l_pte_rec.attribute12
2040 ,p_poei_attribute13 => l_pte_rec.attribute13
2041 ,p_poei_attribute14 => l_pte_rec.attribute14
2042 ,p_poei_attribute15 => l_pte_rec.attribute15
2043 ,p_poei_attribute16 => l_pte_rec.attribute16
2044 ,p_poei_attribute17 => l_pte_rec.attribute17
2045 ,p_poei_attribute18 => l_pte_rec.attribute18
2046 ,p_poei_attribute19 => l_pte_rec.attribute19
2047 ,p_poei_attribute20 => l_pte_rec.attribute20
2048 ,p_poei_information_category => l_pte_rec.information_category
2049 ,p_poei_information1 => l_pte_rec.information1
2050 ,p_poei_information2 => l_pte_rec.information2
2051 ,p_poei_information3 => l_pte_rec.information3
2052 ,p_poei_information4 => l_pte_rec.information4
2053 ,p_poei_information5 => l_pte_rec.information5
2054 ,p_poei_information6 => l_pte_rec.information6
2055 ,p_poei_information7 => l_pte_rec.information7
2056 ,p_poei_information8 => l_pte_rec.information8
2057 ,p_poei_information9 => l_pte_rec.information9
2058 ,p_poei_information10 => l_pte_rec.information10
2059 ,p_poei_information11 => l_pte_rec.information11
2060 ,p_poei_information12 => l_pte_rec.information12
2061 ,p_poei_information13 => l_pte_rec.information13
2062 ,p_poei_information14 => l_pte_rec.information14
2063 ,p_poei_information15 => l_pte_rec.information15
2064 ,p_poei_information16 => l_pte_rec.information16
2065 ,p_poei_information17 => l_pte_rec.information17
2066 ,p_poei_information18 => l_pte_rec.information18
2067 ,p_poei_information19 => l_pte_rec.information19
2068 ,p_poei_information20 => l_pte_rec.information20
2069 ,p_poei_information21 => l_pte_rec.information21
2070 ,p_poei_information22 => l_pte_rec.information22
2071 ,p_poei_information23 => l_pte_rec.information23
2072 ,p_poei_information24 => l_pte_rec.information24
2073 ,p_poei_information25 => l_pte_rec.information25
2074 ,p_poei_information26 => l_pte_rec.information26
2075 ,p_poei_information27 => l_pte_rec.information27
2076 ,p_poei_information28 => l_pte_rec.information28
2077 ,p_poei_information29 => l_pte_rec.information29
2078 ,p_poei_information30 => l_pte_rec.information30
2079 ,p_position_extra_info_id => l_position_extra_info_id
2080 ,p_object_version_number => l_object_version_number
2081 );
2082 ELSIF l_pei_type = 'U' then
2083 hr_utility.set_location('Calling update_position_extra_info.upd '
2084 ||l_proc, 35);
2085 --
2086 -- call update API
2087 --
2088 hr_position_extra_info_api.update_position_extra_info
2089 (p_validate => false
2090 ,p_position_extra_info_id => l_pei_position_extra_info_id
2091 ,p_object_version_number => l_pei_object_version_number
2092 ,p_poei_attribute_category => l_pte_rec.attribute_category
2093 ,p_poei_attribute1 => l_pte_rec.attribute1
2094 ,p_poei_attribute2 => l_pte_rec.attribute2
2095 ,p_poei_attribute3 => l_pte_rec.attribute3
2096 ,p_poei_attribute4 => l_pte_rec.attribute4
2097 ,p_poei_attribute5 => l_pte_rec.attribute5
2098 ,p_poei_attribute6 => l_pte_rec.attribute6
2099 ,p_poei_attribute7 => l_pte_rec.attribute7
2100 ,p_poei_attribute8 => l_pte_rec.attribute8
2101 ,p_poei_attribute9 => l_pte_rec.attribute9
2102 ,p_poei_attribute10 => l_pte_rec.attribute10
2103 ,p_poei_attribute11 => l_pte_rec.attribute11
2104 ,p_poei_attribute12 => l_pte_rec.attribute12
2105 ,p_poei_attribute13 => l_pte_rec.attribute13
2106 ,p_poei_attribute14 => l_pte_rec.attribute14
2107 ,p_poei_attribute15 => l_pte_rec.attribute15
2108 ,p_poei_attribute16 => l_pte_rec.attribute16
2109 ,p_poei_attribute17 => l_pte_rec.attribute17
2110 ,p_poei_attribute18 => l_pte_rec.attribute18
2111 ,p_poei_attribute19 => l_pte_rec.attribute19
2112 ,p_poei_attribute20 => l_pte_rec.attribute20
2113 ,p_poei_information_category => l_pte_rec.information_category
2114 ,p_poei_information1 => l_pte_rec.information1
2115 ,p_poei_information2 => l_pte_rec.information2
2116 ,p_poei_information3 => l_pte_rec.information3
2117 ,p_poei_information4 => l_pte_rec.information4
2118 ,p_poei_information5 => l_pte_rec.information5
2119 ,p_poei_information6 => l_pte_rec.information6
2120 ,p_poei_information7 => l_pte_rec.information7
2121 ,p_poei_information8 => l_pte_rec.information8
2122 ,p_poei_information9 => l_pte_rec.information9
2123 ,p_poei_information10 => l_pte_rec.information10
2124 ,p_poei_information11 => l_pte_rec.information11
2125 ,p_poei_information12 => l_pte_rec.information12
2126 ,p_poei_information13 => l_pte_rec.information13
2127 ,p_poei_information14 => l_pte_rec.information14
2128 ,p_poei_information15 => l_pte_rec.information15
2129 ,p_poei_information16 => l_pte_rec.information16
2130 ,p_poei_information17 => l_pte_rec.information17
2131 ,p_poei_information18 => l_pte_rec.information18
2132 ,p_poei_information19 => l_pte_rec.information19
2133 ,p_poei_information20 => l_pte_rec.information20
2134 ,p_poei_information21 => l_pte_rec.information21
2135 ,p_poei_information22 => l_pte_rec.information22
2136 ,p_poei_information23 => l_pte_rec.information23
2137 ,p_poei_information24 => l_pte_rec.information24
2138 ,p_poei_information25 => l_pte_rec.information25
2139 ,p_poei_information26 => l_pte_rec.information26
2140 ,p_poei_information27 => l_pte_rec.information27
2141 ,p_poei_information28 => l_pte_rec.information28
2142 ,p_poei_information29 => l_pte_rec.information29
2143 ,p_poei_information30 => l_pte_rec.information30
2144 );
2145 --
2146 END IF; -- api call per_position_extra_info
2147 --
2148 -- if l_ptx_rec.seasonal_flag = 'Y' then
2149 --
2150 if l_pte_rec.information_type = 'PER_SEASONAL' then
2151 l_seasonal_dates_present := true;
2152 end if;
2153 --
2154 -- end if;
2155 --
2156 --
2157 --
2158 -- if l_ptx_rec.overlap_period is not null then
2159 --
2160 if l_pte_rec.information_type = 'PER_OVERLAP' then
2161 l_overlap_dates_present := true;
2162 end if;
2163 --
2164 -- end if;
2165 --
2166 --
2167 if l_ptx_rec.work_period_type_cd is not null then
2168 --
2169 if l_pte_rec.information_type = 'PER_FAMILY'
2170 and l_pte_rec.information3 in ('ACADEMIC','FACULTY') then
2171 l_permit_extended_pay := true;
2172 end if;
2173 --
2174 end if;
2175 --
2176 END LOOP; -- for c3
2177 CLOSE c3;
2178 --
2179 -- Check if seasonal_flag = 'Y' then seasonal dates are entered.
2180 --
2181 if l_ptx_rec.seasonal_flag = 'Y' then
2182 if not l_seasonal_dates_present then
2183 hr_utility.set_message(8302,'PQH_ENTER_SEASONAL_DATES');
2184 hr_utility.raise_error;
2185 end if;
2186 else
2187 if l_seasonal_dates_present then
2188 hr_utility.set_message(800,'HR_INV_SEASONAL_FLAG');
2189 hr_utility.raise_error;
2190 end if;
2191 end if;
2192 --
2193 -- Check if overlap_period is not null then overlap dates are entered.
2194 --
2195 if l_ptx_rec.overlap_period is not null then
2196 if not l_overlap_dates_present then
2197 hr_utility.set_message(8302,'PQH_ENTER_OVERLAP_DATES');
2198 hr_utility.raise_error;
2199 end if;
2200 else
2201 if l_overlap_dates_present then
2202 hr_utility.set_message(800,'HR_INV_OVERLAP_PERIOD');
2203 hr_utility.raise_error;
2204 end if;
2205 end if;
2206 /*
2207 --
2208 -- Check whether to permit extended pay
2209 --
2210 if nvl(l_ptx_rec.work_period_type_cd,'N')='Y' then
2211 if not l_permit_extended_pay then
2212 hr_utility.set_message(8302,'PQH_ENTER_VALID_POS_FAMILY');
2213 hr_utility.raise_error;
2214 end if;
2215 end if;
2216 --
2217 */
2218 if ((l_res_position_id is not null or l_res_fte is not null)
2219 or (l_res_effective_date is not null
2220 or (l_res_validation_start_date is not null or l_res_validation_end_date is not null)))
2221 then
2222 if (l_res_position_type = 'SHARED' or l_res_position_type = 'SINGLE') then
2223 pqh_ptx_utl.chk_reserved_fte
2224 (p_position_id =>l_res_position_id
2225 ,p_fte =>l_res_fte
2226 ,p_position_type =>l_res_position_type
2227 ,p_effective_date =>l_res_effective_date
2228 ,p_validation_start_date =>l_res_validation_start_date
2229 ,p_validation_end_date =>l_res_validation_end_date
2230 );
2231 end if;
2232 end if;
2233 --
2234 pqh_ptx_utl.apply_sit(p_transaction_id, l_position_id, l_txn_type);
2235 --
2236 pqh_ptx_utl.apply_ptx_budgets(l_ptx_rec.position_transaction_id);
2237 --
2238 pqh_position_transactions_api.update_position_transaction
2239 (
2240 p_validate => false
2241 ,p_position_transaction_id => l_ptx_rec.position_transaction_id
2242 ,p_action_date => l_ptx_rec.action_date
2243 ,p_position_id => l_position_id
2244 ,p_availability_status_id => l_ptx_rec.availability_status_id
2245 ,p_business_group_id => l_ptx_rec.business_group_id
2246 ,p_entry_step_id => l_ptx_rec.entry_step_id
2247 ,p_entry_grade_rule_id => l_ptx_rec.entry_grade_rule_id
2248 ,p_job_id => l_ptx_rec.job_id
2249 ,p_location_id => l_ptx_rec.location_id
2250 ,p_organization_id => l_ptx_rec.organization_id
2251 ,p_pay_freq_payroll_id => l_ptx_rec.pay_freq_payroll_id
2252 ,p_position_definition_id => l_ptx_rec.position_definition_id
2253 ,p_prior_position_id => l_ptx_rec.prior_position_id
2254 ,p_relief_position_id => l_ptx_rec.relief_position_id
2255 ,p_entry_grade_id => l_ptx_rec.entry_grade_id
2256 ,p_successor_position_id => l_ptx_rec.successor_position_id
2257 ,p_supervisor_position_id => l_ptx_rec.supervisor_position_id
2258 ,p_amendment_date => l_ptx_rec.amendment_date
2259 ,p_amendment_recommendation => l_ptx_rec.amendment_recommendation
2260 ,p_amendment_ref_number => l_ptx_rec.amendment_ref_number
2261 ,p_avail_status_prop_end_date => l_ptx_rec.avail_status_prop_end_date
2262 ,p_bargaining_unit_cd => l_ptx_rec.bargaining_unit_cd
2263 ,p_comments => l_ptx_rec.comments
2264 ,p_country1 => l_ptx_rec.country1
2265 ,p_country2 => l_ptx_rec.country2
2266 ,p_country3 => l_ptx_rec.country3
2267 ,p_current_job_prop_end_date => l_ptx_rec.current_job_prop_end_date
2268 ,p_current_org_prop_end_date => l_ptx_rec.current_org_prop_end_date
2269 ,p_date_effective => l_ptx_rec.date_effective
2270 --,p_date_end => l_ptx_rec.date_end
2271 ,p_earliest_hire_date => l_ptx_rec.earliest_hire_date
2272 ,p_fill_by_date => l_ptx_rec.fill_by_date
2273 ,p_frequency => l_ptx_rec.frequency
2274 ,p_fte => l_ptx_rec.fte
2275 ,p_location1 => l_ptx_rec.location1
2276 ,p_location2 => l_ptx_rec.location2
2277 ,p_location3 => l_ptx_rec.location3
2278 ,p_max_persons => l_ptx_rec.max_persons
2279 ,p_name => l_ptx_rec.name
2280 ,p_other_requirements => l_ptx_rec.other_requirements
2281 ,p_overlap_period => l_ptx_rec.overlap_period
2282 ,p_overlap_unit_cd => l_ptx_rec.overlap_unit_cd
2283 ,p_passport_required => l_ptx_rec.passport_required
2284 ,p_pay_term_end_day_cd => l_ptx_rec.pay_term_end_day_cd
2285 ,p_pay_term_end_month_cd => l_ptx_rec.pay_term_end_month_cd
2286 ,p_permanent_temporary_flag => l_ptx_rec.permanent_temporary_flag
2287 ,p_permit_recruitment_flag => l_ptx_rec.permit_recruitment_flag
2288 ,p_position_type => l_ptx_rec.position_type
2289 ,p_posting_description => l_ptx_rec.posting_description
2290 ,p_probation_period => l_ptx_rec.probation_period
2291 ,p_probation_period_unit_cd => l_ptx_rec.probation_period_unit_cd
2292 ,p_relocate_domestically => l_ptx_rec.relocate_domestically
2293 ,p_relocate_internationally => l_ptx_rec.relocate_internationally
2294 ,p_replacement_required_flag => l_ptx_rec.replacement_required_flag
2295 ,p_review_flag => 'N' --l_ptx_rec.review_flag
2296 ,p_seasonal_flag => l_ptx_rec.seasonal_flag
2297 ,p_security_requirements => l_ptx_rec.security_requirements
2298 ,p_service_minimum => l_ptx_rec.service_minimum
2299 ,p_term_start_day_cd => l_ptx_rec.term_start_day_cd
2300 ,p_term_start_month_cd => l_ptx_rec.term_start_month_cd
2301 ,p_time_normal_finish => l_ptx_rec.time_normal_finish
2302 ,p_time_normal_start => l_ptx_rec.time_normal_start
2303 ,p_transaction_status => 'APPLIED'
2304 ,p_travel_required => l_ptx_rec.travel_required
2305 ,p_working_hours => l_ptx_rec.working_hours
2306 ,p_works_council_approval_flag => l_ptx_rec.works_council_approval_flag
2307 ,p_work_any_country => l_ptx_rec.work_any_country
2308 ,p_work_any_location => l_ptx_rec.work_any_location
2309 ,p_work_period_type_cd => l_ptx_rec.work_period_type_cd
2310 ,p_work_schedule => l_ptx_rec.work_schedule
2311 ,p_work_duration => l_ptx_rec.work_duration
2312 ,p_work_term_end_day_cd => l_ptx_rec.work_term_end_day_cd
2313 ,p_work_term_end_month_cd => l_ptx_rec.work_term_end_month_cd
2314 ,p_proposed_fte_for_layoff => l_ptx_rec.proposed_fte_for_layoff
2315 ,p_proposed_date_for_layoff => l_ptx_rec.proposed_date_for_layoff
2316 ,p_information1 => l_ptx_rec.information1
2317 ,p_information2 => l_ptx_rec.information2
2318 ,p_information3 => l_ptx_rec.information3
2319 ,p_information4 => l_ptx_rec.information4
2320 ,p_information5 => l_ptx_rec.information5
2321 ,p_information6 => l_ptx_rec.information6
2322 ,p_information7 => l_ptx_rec.information7
2323 ,p_information8 => l_ptx_rec.information8
2324 ,p_information9 => l_ptx_rec.information9
2325 ,p_information10 => l_ptx_rec.information10
2326 ,p_information11 => l_ptx_rec.information11
2327 ,p_information12 => l_ptx_rec.information12
2328 ,p_information13 => l_ptx_rec.information13
2329 ,p_information14 => l_ptx_rec.information14
2330 ,p_information15 => l_ptx_rec.information15
2331 ,p_information16 => l_ptx_rec.information16
2332 ,p_information17 => l_ptx_rec.information17
2333 ,p_information18 => l_ptx_rec.information18
2334 ,p_information19 => l_ptx_rec.information19
2335 ,p_information20 => l_ptx_rec.information20
2336 ,p_information21 => l_ptx_rec.information21
2337 ,p_information22 => l_ptx_rec.information22
2338 ,p_information23 => l_ptx_rec.information24
2339 ,p_information24 => l_ptx_rec.information23
2340 ,p_information25 => l_ptx_rec.information25
2341 ,p_information26 => l_ptx_rec.information26
2342 ,p_information27 => l_ptx_rec.information27
2343 ,p_information28 => l_ptx_rec.information28
2344 ,p_information29 => l_ptx_rec.information29
2345 ,p_information30 => l_ptx_rec.information30
2346 ,p_information_category => l_ptx_rec.information_category
2347 ,p_attribute1 => l_ptx_rec.attribute1
2348 ,p_attribute2 => l_ptx_rec.attribute2
2349 ,p_attribute3 => l_ptx_rec.attribute3
2350 ,p_attribute4 => l_ptx_rec.attribute4
2351 ,p_attribute5 => l_ptx_rec.attribute5
2352 ,p_attribute6 => l_ptx_rec.attribute6
2353 ,p_attribute7 => l_ptx_rec.attribute7
2354 ,p_attribute8 => l_ptx_rec.attribute8
2355 ,p_attribute9 => l_ptx_rec.attribute9
2356 ,p_attribute10 => l_ptx_rec.attribute10
2357 ,p_attribute11 => l_ptx_rec.attribute11
2358 ,p_attribute12 => l_ptx_rec.attribute12
2359 ,p_attribute13 => l_ptx_rec.attribute13
2360 ,p_attribute14 => l_ptx_rec.attribute14
2361 ,p_attribute15 => l_ptx_rec.attribute15
2362 ,p_attribute16 => l_ptx_rec.attribute16
2363 ,p_attribute17 => l_ptx_rec.attribute17
2364 ,p_attribute18 => l_ptx_rec.attribute18
2365 ,p_attribute19 => l_ptx_rec.attribute19
2366 ,p_attribute20 => l_ptx_rec.attribute20
2367 ,p_attribute21 => l_ptx_rec.attribute21
2368 ,p_attribute22 => l_ptx_rec.attribute22
2369 ,p_attribute23 => l_ptx_rec.attribute23
2370 ,p_attribute24 => l_ptx_rec.attribute24
2371 ,p_attribute25 => l_ptx_rec.attribute25
2372 ,p_attribute26 => l_ptx_rec.attribute26
2373 ,p_attribute27 => l_ptx_rec.attribute27
2374 ,p_attribute28 => l_ptx_rec.attribute28
2375 ,p_attribute29 => l_ptx_rec.attribute29
2376 ,p_attribute30 => l_ptx_rec.attribute30
2377 ,p_attribute_category => l_ptx_rec.attribute_category
2378 ,p_object_version_number => l_ptx_rec.object_version_number
2379 ,p_effective_date => l_ptx_rec.action_date
2380 ,p_pay_basis_id => l_ptx_rec.pay_basis_id
2381 ,p_supervisor_id => l_ptx_rec.supervisor_id
2382 ,p_wf_transaction_category_id => l_ptx_rec.wf_transaction_category_id
2383 );
2384 --
2385 log_warnings(p_transaction_id);
2386 --
2387 pqh_process_batch_log.end_log ;
2388 --
2389 hr_utility.set_location('Leaving:'||l_proc, 1000);
2390 return(l_return);
2391 --
2392 EXCEPTION
2393 WHEN OTHERS THEN
2394 begin
2395 --
2396 pqh_process_batch_log.set_context_level (
2397 p_txn_id => p_transaction_id,
2398 p_txn_table_route_id => null,
2399 p_level => 1,
2400 p_log_context => 'ERROR');
2401 --
2402 pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR',
2403 p_message_text => SQLERRM );
2404 --
2405 log_warnings(p_transaction_id);
2406 --
2407 pqh_process_batch_log.end_log ;
2408 end;
2409 --
2410 rollback to apply_transaction;
2411 --
2412 --
2413 if PQH_APPLY_BUDGET.get_txn_state (
2414 p_transaction_category_id => l_transaction_category_id,
2415 p_action_date => l_ptx_rec.action_date
2416 ) = 'D'
2417 then
2418 pqh_position_transactions_api.update_position_transaction
2419 (
2420 p_validate => false
2421 ,p_position_transaction_id => l_ptx_rec.position_transaction_id
2422 ,p_action_date => l_ptx_rec.action_date
2423 ,p_position_id => l_ptx_rec.position_id
2424 ,p_availability_status_id => l_ptx_rec.availability_status_id
2425 ,p_business_group_id => l_ptx_rec.business_group_id
2426 ,p_entry_step_id => l_ptx_rec.entry_step_id
2427 ,p_entry_grade_rule_id => l_ptx_rec.entry_grade_rule_id
2428 ,p_job_id => l_ptx_rec.job_id
2429 ,p_location_id => l_ptx_rec.location_id
2430 ,p_organization_id => l_ptx_rec.organization_id
2431 ,p_pay_freq_payroll_id => l_ptx_rec.pay_freq_payroll_id
2432 ,p_position_definition_id => l_ptx_rec.position_definition_id
2433 ,p_prior_position_id => l_ptx_rec.prior_position_id
2434 ,p_relief_position_id => l_ptx_rec.relief_position_id
2435 ,p_entry_grade_id => l_ptx_rec.entry_grade_id
2436 ,p_successor_position_id => l_ptx_rec.successor_position_id
2437 ,p_supervisor_position_id => l_ptx_rec.supervisor_position_id
2438 ,p_amendment_date => l_ptx_rec.amendment_date
2439 ,p_amendment_recommendation => l_ptx_rec.amendment_recommendation
2440 ,p_amendment_ref_number => l_ptx_rec.amendment_ref_number
2441 ,p_avail_status_prop_end_date => l_ptx_rec.avail_status_prop_end_date
2442 ,p_bargaining_unit_cd => l_ptx_rec.bargaining_unit_cd
2443 ,p_comments => l_ptx_rec.comments
2444 ,p_country1 => l_ptx_rec.country1
2445 ,p_country2 => l_ptx_rec.country2
2446 ,p_country3 => l_ptx_rec.country3
2447 ,p_current_job_prop_end_date => l_ptx_rec.current_job_prop_end_date
2448 ,p_current_org_prop_end_date => l_ptx_rec.current_org_prop_end_date
2449 ,p_date_effective => l_ptx_rec.date_effective
2450 ,p_date_end => l_ptx_rec.date_end
2451 ,p_earliest_hire_date => l_ptx_rec.earliest_hire_date
2452 ,p_fill_by_date => l_ptx_rec.fill_by_date
2453 ,p_frequency => l_ptx_rec.frequency
2454 ,p_fte => l_ptx_rec.fte
2455 ,p_location1 => l_ptx_rec.location1
2456 ,p_location2 => l_ptx_rec.location2
2457 ,p_location3 => l_ptx_rec.location3
2458 ,p_max_persons => l_ptx_rec.max_persons
2459 ,p_name => l_ptx_rec.name
2460 ,p_other_requirements => l_ptx_rec.other_requirements
2461 ,p_overlap_period => l_ptx_rec.overlap_period
2462 ,p_overlap_unit_cd => l_ptx_rec.overlap_unit_cd
2463 ,p_passport_required => l_ptx_rec.passport_required
2464 ,p_pay_term_end_day_cd => l_ptx_rec.pay_term_end_day_cd
2465 ,p_pay_term_end_month_cd => l_ptx_rec.pay_term_end_month_cd
2466 ,p_permanent_temporary_flag => l_ptx_rec.permanent_temporary_flag
2467 ,p_permit_recruitment_flag => l_ptx_rec.permit_recruitment_flag
2468 ,p_position_type => l_ptx_rec.position_type
2469 ,p_posting_description => l_ptx_rec.posting_description
2470 ,p_probation_period => l_ptx_rec.probation_period
2471 ,p_probation_period_unit_cd => l_ptx_rec.probation_period_unit_cd
2472 ,p_relocate_domestically => l_ptx_rec.relocate_domestically
2473 ,p_relocate_internationally => l_ptx_rec.relocate_internationally
2474 ,p_replacement_required_flag => l_ptx_rec.replacement_required_flag
2475 ,p_review_flag => 'N' --l_ptx_rec.review_flag
2476 ,p_seasonal_flag => l_ptx_rec.seasonal_flag
2477 ,p_security_requirements => l_ptx_rec.security_requirements
2478 ,p_service_minimum => l_ptx_rec.service_minimum
2479 ,p_term_start_day_cd => l_ptx_rec.term_start_day_cd
2480 ,p_term_start_month_cd => l_ptx_rec.term_start_month_cd
2481 ,p_time_normal_finish => l_ptx_rec.time_normal_finish
2482 ,p_time_normal_start => l_ptx_rec.time_normal_start
2483 ,p_transaction_status => 'APPROVED'
2484 ,p_travel_required => l_ptx_rec.travel_required
2485 ,p_working_hours => l_ptx_rec.working_hours
2486 ,p_works_council_approval_flag => l_ptx_rec.works_council_approval_flag
2487 ,p_work_any_country => l_ptx_rec.work_any_country
2488 ,p_work_any_location => l_ptx_rec.work_any_location
2489 ,p_work_period_type_cd => l_ptx_rec.work_period_type_cd
2490 ,p_work_schedule => l_ptx_rec.work_schedule
2491 ,p_work_duration => l_ptx_rec.work_duration
2492 ,p_work_term_end_day_cd => l_ptx_rec.work_term_end_day_cd
2493 ,p_work_term_end_month_cd => l_ptx_rec.work_term_end_month_cd
2494 ,p_proposed_fte_for_layoff => l_ptx_rec.proposed_fte_for_layoff
2495 ,p_proposed_date_for_layoff => l_ptx_rec.proposed_date_for_layoff
2496 ,p_information1 => l_ptx_rec.information1
2497 ,p_information2 => l_ptx_rec.information2
2498 ,p_information3 => l_ptx_rec.information3
2499 ,p_information4 => l_ptx_rec.information4
2500 ,p_information5 => l_ptx_rec.information5
2501 ,p_information6 => l_ptx_rec.information6
2502 ,p_information7 => l_ptx_rec.information7
2503 ,p_information8 => l_ptx_rec.information8
2504 ,p_information9 => l_ptx_rec.information9
2505 ,p_information10 => l_ptx_rec.information10
2506 ,p_information11 => l_ptx_rec.information11
2507 ,p_information12 => l_ptx_rec.information12
2508 ,p_information13 => l_ptx_rec.information13
2509 ,p_information14 => l_ptx_rec.information14
2510 ,p_information15 => l_ptx_rec.information15
2511 ,p_information16 => l_ptx_rec.information16
2512 ,p_information17 => l_ptx_rec.information17
2513 ,p_information18 => l_ptx_rec.information18
2514 ,p_information19 => l_ptx_rec.information19
2515 ,p_information20 => l_ptx_rec.information20
2516 ,p_information21 => l_ptx_rec.information21
2517 ,p_information22 => l_ptx_rec.information22
2518 ,p_information23 => l_ptx_rec.information24
2519 ,p_information24 => l_ptx_rec.information23
2520 ,p_information25 => l_ptx_rec.information25
2521 ,p_information26 => l_ptx_rec.information26
2522 ,p_information27 => l_ptx_rec.information27
2523 ,p_information28 => l_ptx_rec.information28
2524 ,p_information29 => l_ptx_rec.information29
2525 ,p_information30 => l_ptx_rec.information30
2526 ,p_information_category => l_ptx_rec.information_category
2527 ,p_attribute1 => l_ptx_rec.attribute1
2528 ,p_attribute2 => l_ptx_rec.attribute2
2529 ,p_attribute3 => l_ptx_rec.attribute3
2530 ,p_attribute4 => l_ptx_rec.attribute4
2531 ,p_attribute5 => l_ptx_rec.attribute5
2532 ,p_attribute6 => l_ptx_rec.attribute6
2533 ,p_attribute7 => l_ptx_rec.attribute7
2534 ,p_attribute8 => l_ptx_rec.attribute8
2535 ,p_attribute9 => l_ptx_rec.attribute9
2536 ,p_attribute10 => l_ptx_rec.attribute10
2537 ,p_attribute11 => l_ptx_rec.attribute11
2538 ,p_attribute12 => l_ptx_rec.attribute12
2539 ,p_attribute13 => l_ptx_rec.attribute13
2540 ,p_attribute14 => l_ptx_rec.attribute14
2541 ,p_attribute15 => l_ptx_rec.attribute15
2542 ,p_attribute16 => l_ptx_rec.attribute16
2543 ,p_attribute17 => l_ptx_rec.attribute17
2544 ,p_attribute18 => l_ptx_rec.attribute18
2545 ,p_attribute19 => l_ptx_rec.attribute19
2546 ,p_attribute20 => l_ptx_rec.attribute20
2547 ,p_attribute21 => l_ptx_rec.attribute21
2548 ,p_attribute22 => l_ptx_rec.attribute22
2549 ,p_attribute23 => l_ptx_rec.attribute23
2550 ,p_attribute24 => l_ptx_rec.attribute24
2551 ,p_attribute25 => l_ptx_rec.attribute25
2552 ,p_attribute26 => l_ptx_rec.attribute26
2553 ,p_attribute27 => l_ptx_rec.attribute27
2554 ,p_attribute28 => l_ptx_rec.attribute28
2555 ,p_attribute29 => l_ptx_rec.attribute29
2556 ,p_attribute30 => l_ptx_rec.attribute30
2557 ,p_attribute_category => l_ptx_rec.attribute_category
2558 ,p_object_version_number => l_ptx_rec.object_version_number
2559 ,p_effective_date => l_ptx_rec.action_date
2560 ,p_pay_basis_id => l_ptx_rec.pay_basis_id
2561 ,p_supervisor_id => l_ptx_rec.supervisor_id
2562 ,p_wf_transaction_category_id => l_ptx_rec.wf_transaction_category_id
2563 );
2564 end if;
2565 --
2566 if SQLERRM is not null then
2567 pqh_wf.set_apply_error(p_transaction_category_id
2568 => l_transaction_category_id,
2569 p_transaction_id => p_transaction_id,
2570 p_apply_error_mesg => SQLERRM,
2571 p_apply_error_num => SQLCODE);
2572 l_return := 'FAILURE';
2573 else
2574 l_return := 'SUCESS';
2575 end if;
2576 --
2577 return l_return;
2578 --
2579 END apply_transaction;
2580
2581 --------------------------------------------------------------------------
2582
2583 FUNCTION fyi_notification (p_transaction_id in number) RETURN varchar2
2584 is
2585 l_document varchar2(4000);
2586 l_proc varchar2(61) := g_package||'fyi_notification' ;
2587 l_position_name varchar2(2000);
2588 l_action_date date;
2589 l_organization_desc varchar2(2000);
2590 l_job_desc varchar2(2000);
2591 l_transaction_status varchar2(100);
2592 cursor c0 is select name, action_date,
2593 hr_general.decode_organization(organization_id),
2594 hr_general.decode_job(job_id),
2595 hr_general.decode_lookup('PQH_TRANSACTION_STATUS', transaction_status)
2596 from pqh_position_transactions
2597 where position_transaction_id = p_transaction_id;
2598 BEGIN
2599 hr_utility.set_location('inside fyi notification'||l_proc,10);
2600 open c0;
2601 fetch c0 into l_position_name, l_action_date, l_organization_desc,
2602 l_job_desc, l_transaction_status;
2603 close c0;
2604 hr_utility.set_location('position name, action date fetched '||l_proc,20);
2605 --
2606 hr_utility.set_message(8302,'PQH_PTX_WF_FYI_NOTICE');
2607 hr_utility.set_message_token('POSITION_NAME',l_position_name);
2608 hr_utility.set_message_token('ACTION_DATE',l_action_date);
2609 hr_utility.set_message_token('ORGANIZATION',l_organization_desc);
2610 hr_utility.set_message_token('JOB',l_job_desc);
2611 hr_utility.set_message_token('TRANSACTION_STATUS',l_transaction_status);
2612 l_document := hr_utility.get_message;
2613 return l_document;
2614 exception
2615 when others then
2616 hr_utility.set_message(8302,'PQH_PTX_WF_FYI_FAIL');
2617 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
2618 l_document := hr_utility.get_message;
2619 return l_document;
2620 END fyi_notification;
2621
2622 --------------------------------------------------------------------------
2623
2624 FUNCTION back_notification (p_transaction_id in number) RETURN varchar2
2625 is
2626 l_document varchar2(4000);
2627 l_proc varchar2(61) := g_package||'back_notification' ;
2628 l_position_name varchar2(2000);
2629 l_action_date date;
2630 l_organization_desc varchar2(2000);
2631 l_job_desc varchar2(2000);
2632 l_transaction_status varchar2(100);
2633 cursor c0 is select name, action_date,
2634 hr_general.decode_organization(organization_id),
2635 hr_general.decode_job(job_id),
2636 hr_general.decode_lookup('PQH_TRANSACTION_STATUS', transaction_status)
2637 from pqh_position_transactions
2638 where position_transaction_id = p_transaction_id;
2639 BEGIN
2640 hr_utility.set_location('inside back notification'||l_proc,10);
2641 open c0;
2642 fetch c0 into l_position_name, l_action_date, l_organization_desc,
2643 l_job_desc, l_transaction_status;
2644 close c0;
2645 hr_utility.set_location('position name, action date fetched '||l_proc,20);
2646 --
2647 hr_utility.set_message(8302,'PQH_PTX_WF_BACK_NOTICE');
2648 hr_utility.set_message_token('POSITION_NAME',l_position_name);
2649 hr_utility.set_message_token('ACTION_DATE',l_action_date);
2650 hr_utility.set_message_token('ORGANIZATION',l_organization_desc);
2651 hr_utility.set_message_token('JOB',l_job_desc);
2652 hr_utility.set_message_token('TRANSACTION_STATUS',l_transaction_status);
2653 l_document := hr_utility.get_message;
2654 return l_document;
2655 exception
2656 when others then
2657 hr_utility.set_message(8302,'PQH_PTX_WF_BACK_FAIL');
2658 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
2659 l_document := hr_utility.get_message;
2660 return l_document;
2661 END back_notification;
2662
2663 --------------------------------------------------------------------------
2664
2665 FUNCTION override_notification (p_transaction_id in number) RETURN varchar2
2666 is
2667 l_document varchar2(4000);
2668 l_proc varchar2(61) := g_package||'override_notification' ;
2669 l_position_name varchar2(2000);
2670 l_action_date date;
2671 l_organization_desc varchar2(2000);
2672 l_job_desc varchar2(2000);
2673 l_transaction_status varchar2(100);
2674 cursor c0 is select name, action_date,
2675 hr_general.decode_organization(organization_id),
2676 hr_general.decode_job(job_id),
2677 hr_general.decode_lookup('PQH_TRANSACTION_STATUS', transaction_status)
2678 from pqh_position_transactions
2679 where position_transaction_id = p_transaction_id;
2680 BEGIN
2681 hr_utility.set_location('inside override notification'||l_proc,10);
2682 open c0;
2683 fetch c0 into l_position_name, l_action_date, l_organization_desc,
2684 l_job_desc, l_transaction_status;
2685 close c0;
2686 hr_utility.set_location('position name, action date fetched '||l_proc,20);
2687 --
2688 hr_utility.set_message(8302,'PQH_PTX_WF_OVERRIDE_NOTICE');
2689 hr_utility.set_message_token('POSITION_NAME',l_position_name);
2690 hr_utility.set_message_token('ACTION_DATE',l_action_date);
2691 hr_utility.set_message_token('ORGANIZATION',l_organization_desc);
2692 hr_utility.set_message_token('JOB',l_job_desc);
2693 hr_utility.set_message_token('TRANSACTION_STATUS',l_transaction_status);
2694 l_document := hr_utility.get_message;
2695 return l_document;
2696 exception
2697 when others then
2698 hr_utility.set_message(8302,'PQH_PTX_WF_OVERRIDE_FAIL');
2699 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
2700 l_document := hr_utility.get_message;
2701 return l_document;
2702 END override_notification;
2703
2704 --------------------------------------------------------------------------
2705
2706 FUNCTION apply_notification (p_transaction_id in number) RETURN varchar2
2707 is
2708 l_document varchar2(4000);
2709 l_proc varchar2(61) := g_package||'apply_notification' ;
2710 l_position_name varchar2(2000);
2711 l_action_date date;
2712 l_organization_desc varchar2(2000);
2713 l_job_desc varchar2(2000);
2714 l_transaction_status varchar2(100);
2715 cursor c0 is select name, action_date,
2716 hr_general.decode_organization(organization_id),
2717 hr_general.decode_job(job_id),
2718 hr_general.decode_lookup('PQH_TRANSACTION_STATUS', transaction_status)
2719 from pqh_position_transactions
2720 where position_transaction_id = p_transaction_id;
2721 BEGIN
2722 hr_utility.set_location('inside apply notification'||l_proc,10);
2723 open c0;
2724 fetch c0 into l_position_name, l_action_date, l_organization_desc,
2725 l_job_desc, l_transaction_status;
2726 close c0;
2727 hr_utility.set_location('position name, action date fetched '||l_proc,20);
2728 --
2729 hr_utility.set_message(8302,'PQH_PTX_WF_APPLY_NOTICE');
2730 hr_utility.set_message_token('POSITION_NAME',l_position_name);
2731 hr_utility.set_message_token('ACTION_DATE',l_action_date);
2732 hr_utility.set_message_token('ORGANIZATION',l_organization_desc);
2733 hr_utility.set_message_token('JOB',l_job_desc);
2734 hr_utility.set_message_token('TRANSACTION_STATUS',l_transaction_status);
2735 l_document := hr_utility.get_message;
2736 return l_document;
2737 exception
2738 when others then
2739 hr_utility.set_message(8302,'PQH_PTX_WF_APPLY_FAIL');
2740 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
2741 l_document := hr_utility.get_message;
2742 return l_document;
2743 END apply_notification;
2744
2745 --------------------------------------------------------------------------
2746
2747 FUNCTION reject_notification (p_transaction_id in number) RETURN varchar2
2748 is
2749 l_document varchar2(4000);
2750 l_proc varchar2(61) := g_package||'reject_notification' ;
2751 l_position_name varchar2(2000);
2752 l_action_date date;
2753 l_organization_desc varchar2(2000);
2754 l_job_desc varchar2(2000);
2755 l_transaction_status varchar2(100);
2756 cursor c0 is select name, action_date,
2757 hr_general.decode_organization(organization_id),
2758 hr_general.decode_job(job_id),
2759 hr_general.decode_lookup('PQH_TRANSACTION_STATUS', transaction_status)
2760 from pqh_position_transactions
2761 where position_transaction_id = p_transaction_id;
2762 BEGIN
2763 hr_utility.set_location('inside reject notification'||l_proc,10);
2764 open c0;
2765 fetch c0 into l_position_name, l_action_date, l_organization_desc,
2766 l_job_desc, l_transaction_status;
2767 close c0;
2768 hr_utility.set_location('position name, action date fetched '||l_proc,20);
2769 --
2770 hr_utility.set_message(8302,'PQH_PTX_WF_REJECT_NOTICE');
2771 hr_utility.set_message_token('POSITION_NAME',l_position_name);
2772 hr_utility.set_message_token('ACTION_DATE',l_action_date);
2773 hr_utility.set_message_token('ORGANIZATION',l_organization_desc);
2774 hr_utility.set_message_token('JOB',l_job_desc);
2775 hr_utility.set_message_token('TRANSACTION_STATUS',l_transaction_status);
2776 l_document := hr_utility.get_message;
2777 return l_document;
2778 exception
2779 when others then
2780 hr_utility.set_message(8302,'PQH_PTX_WF_REJECT_FAIL');
2781 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
2782 l_document := hr_utility.get_message;
2783 return l_document;
2784 END reject_notification;
2785
2786 --------------------------------------------------------------------------
2787
2788 FUNCTION warning_notification (p_transaction_id in number) RETURN varchar2
2789 is
2790 l_document varchar2(4000);
2791 l_proc varchar2(61) := g_package||'warning_notification' ;
2792 l_position_name varchar2(2000);
2793 l_action_date date;
2794 l_organization_desc varchar2(2000);
2795 l_job_desc varchar2(2000);
2796 l_transaction_status varchar2(100);
2797 cursor c0 is select name, action_date,
2798 hr_general.decode_organization(organization_id),
2799 hr_general.decode_job(job_id),
2800 hr_general.decode_lookup('PQH_TRANSACTION_STATUS', transaction_status)
2801 from pqh_position_transactions
2802 where position_transaction_id = p_transaction_id;
2803 BEGIN
2804 hr_utility.set_location('inside warning notification'||l_proc,10);
2805 open c0;
2806 fetch c0 into l_position_name, l_action_date, l_organization_desc,
2807 l_job_desc, l_transaction_status;
2808 close c0;
2809 hr_utility.set_location('position name, action date fetched '||l_proc,20);
2810 --
2811 hr_utility.set_message(8302,'PQH_PTX_WF_WARNING_NOTICE');
2812 hr_utility.set_message_token('POSITION_NAME',l_position_name);
2813 hr_utility.set_message_token('ACTION_DATE',l_action_date);
2814 hr_utility.set_message_token('ORGANIZATION',l_organization_desc);
2815 hr_utility.set_message_token('JOB',l_job_desc);
2816 hr_utility.set_message_token('TRANSACTION_STATUS',l_transaction_status);
2817 l_document := hr_utility.get_message;
2818 return l_document;
2819 exception
2820 when others then
2821 hr_utility.set_message(8302,'PQH_PTX_WF_WARNING_FAIL');
2822 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
2823 l_document := hr_utility.get_message;
2824 return l_document;
2825 END warning_notification;
2826
2827 --------------------------------------------------------------------------
2828
2829 FUNCTION respond_notification (p_transaction_id in number) RETURN varchar2
2830 is
2831 l_document varchar2(4000);
2832 l_proc varchar2(61) := g_package||'respond_notification' ;
2833 l_position_name varchar2(2000);
2834 l_action_date date;
2835 l_organization_desc varchar2(2000);
2836 l_job_desc varchar2(2000);
2837 l_transaction_status varchar2(100);
2838 cursor c0 is select name, action_date,
2839 hr_general.decode_organization(organization_id),
2840 hr_general.decode_job(job_id),
2841 hr_general.decode_lookup('PQH_TRANSACTION_STATUS', transaction_status)
2842 from pqh_position_transactions
2843 where position_transaction_id = p_transaction_id;
2844 BEGIN
2845 hr_utility.set_location('inside respond notification'||l_proc,10);
2846 open c0;
2847 fetch c0 into l_position_name, l_action_date, l_organization_desc,
2848 l_job_desc, l_transaction_status;
2849 close c0;
2850 hr_utility.set_location('position name, action date fetched '||l_proc,20);
2851 --
2852 hr_utility.set_message(8302,'PQH_PTX_WF_RESPOND_NOTICE');
2853 hr_utility.set_message_token('POSITION_NAME',l_position_name);
2854 hr_utility.set_message_token('ACTION_DATE',l_action_date);
2855 hr_utility.set_message_token('ORGANIZATION',l_organization_desc);
2856 hr_utility.set_message_token('JOB',l_job_desc);
2857 hr_utility.set_message_token('TRANSACTION_STATUS',l_transaction_status);
2858 l_document := hr_utility.get_message;
2859 return l_document;
2860 exception
2861 when others then
2862 hr_utility.set_message(8302,'PQH_PTX_WF_RESPOND_FAIL');
2863 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
2864 l_document := hr_utility.get_message;
2865 return l_document;
2866 END respond_notification;
2867
2868 --------------------------------------------------------------------------------------------------------------
2869
2870 FUNCTION set_status
2871 (
2872 p_transaction_category_id IN pqh_transaction_categories.transaction_category_id%TYPE,
2873 p_transaction_id IN pqh_worksheets.worksheet_id%TYPE,
2874 p_status IN pqh_worksheets.transaction_status%TYPE
2875 ) RETURN varchar2 IS
2876 /*
2877 This procedure will update the txn status
2878 */
2879
2880 l_proc varchar2(72) := g_package||'set_status';
2881
2882
2883 CURSOR csr_ptx IS
2884 SELECT ptx.*
2885 FROM pqh_position_transactions ptx
2886 WHERE ptx.position_transaction_id = p_transaction_id;
2887
2888 l_ptx_rec pqh_position_transactions%ROWTYPE;
2889 l_object_version_number pqh_position_transactions.object_version_number%TYPE;
2890 l_review_flag pqh_position_transactions.review_flag%TYPE;
2891
2892 BEGIN
2893 hr_utility.set_location('Entering:'||l_proc, 5);
2894
2895 OPEN csr_ptx;
2896 FETCH csr_ptx INTO l_ptx_rec;
2897 CLOSE csr_ptx;
2898
2899 -- call the Abort process
2900 BEGIN
2901 -- call the Abort Process, if error then skip and go to next record after updating wdt status
2902 wf_engine.AbortProcess
2903 (itemtype => 'PQHGEN',
2904 itemkey => p_transaction_category_id || '-' || p_transaction_id,
2905 process => 'PQH_ROUTING',
2906 result => null
2907 );
2908
2909 EXCEPTION
2910 WHEN OTHERS THEN
2911 null;
2912 END; -- for Abort process
2913
2914 l_object_version_number := l_ptx_rec.object_version_number;
2915
2916 -- call the update API
2917 -- If condition added for bug 6112905/ Modified for bug 6524175
2918 hr_utility.set_location('Entering:'||l_proc||' with status: '||p_status||
2919 'and review_flag: '||l_review_flag, 15);
2920
2921 if p_status in ('REJECT','TERMINATE','SUBMITTED') then
2922 l_review_flag := 'N';
2923 end if;
2924
2925
2926 pqh_position_transactions_api.update_position_transaction
2927 (
2928 p_validate => false
2929 ,p_position_transaction_id => p_transaction_id
2930 ,p_object_version_number => l_object_version_number
2931 ,p_transaction_status => p_status
2932 ,p_effective_date => SYSDATE
2933 ,p_review_flag => l_review_flag -- bug 6112905
2934 );
2935
2936
2937 hr_utility.set_location('Leaving:'||l_proc, 1000);
2938
2939 RETURN 'SUCCESS';
2940
2941
2942
2943 EXCEPTION
2944 WHEN OTHERS THEN
2945 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2946 hr_utility.set_message_token('ROUTINE', l_proc);
2947 hr_utility.set_message_token('REASON', SQLERRM);
2948 hr_utility.raise_error;
2949 hr_utility.set_location('Leaving: EXCEPTION '||l_proc, 1000);
2950 RETURN 'FAILURE';
2951 END set_status;
2952
2953 --------------------------------------------------------------------------------
2954 procedure create_ptx_shadow(p_position_transaction_id number) is
2955 cursor c1 is
2956 select *
2957 from pqh_position_transactions
2958 where position_transaction_id = p_position_transaction_id
2959 and position_transaction_id not in
2960 (select position_transaction_id
2961 from pqh_ptx_shadow
2962 where position_transaction_id = p_position_transaction_id);
2963 rec1 c1%rowtype;
2964 begin
2965 open c1;
2966 fetch c1 into rec1;
2967 if c1%notfound then
2968 return;
2969 end if;
2970 insert into pqh_ptx_shadow
2971 (position_transaction_id, action_date, position_id, availability_status_id, business_group_id, entry_grade_id,
2972 entry_step_id, entry_grade_rule_id, job_id, location_id, organization_id, pay_basis_id, pay_freq_payroll_id,
2973 position_definition_id, prior_position_id, relief_position_id, successor_position_id, supervisor_id,
2974 supervisor_position_id, amendment_date, amendment_recommendation, amendment_ref_number, avail_status_prop_end_date,
2975 bargaining_unit_cd, comments, country1, country2, country3, current_job_prop_end_date, current_org_prop_end_date,
2976 date_effective, date_end, earliest_hire_date, fill_by_date, frequency, fte, fte_capacity, location1, location2,
2977 location3, max_persons, name, other_requirements, overlap_period, overlap_unit_cd, passport_required,
2978 pay_term_end_day_cd, pay_term_end_month_cd, permanent_temporary_flag, permit_recruitment_flag, position_type,
2979 posting_description, probation_period, probation_period_unit_cd, proposed_fte_for_layoff,
2980 proposed_date_for_layoff, relocate_domestically, relocate_internationally, relocation_required,
2981 replacement_required_flag, review_flag, seasonal_flag, security_requirements, service_minimum,
2982 term_start_day_cd, term_start_month_cd, time_normal_finish, time_normal_start,
2983 travel_required, visit_internationally, working_hours, works_council_approval_flag, work_any_country,
2984 work_any_location, work_duration, work_period_type_cd, work_schedule, work_term_end_day_cd, work_term_end_month_cd,
2985 information1, information2, information3, information4, information5,
2986 information6, information7, information8, information9, information10,
2987 information11, information12, information13, information14, information15,
2988 information16, information17, information18, information19, information20,
2989 information21, information22, information23, information24, information25,
2990 information26, information27, information28, information29, information30,
2991 information_category,
2992 attribute1, attribute2, attribute3, attribute4, attribute5,
2993 attribute6, attribute7, attribute8, attribute9, attribute10,
2994 attribute11, attribute12, attribute13, attribute14, attribute15,
2995 attribute16, attribute17, attribute18, attribute19, attribute20,
2996 attribute21, attribute22, attribute23, attribute24, attribute25,
2997 attribute26, attribute27, attribute28, attribute29, attribute30,
2998 attribute_category,
2999 created_by, creation_date, last_updated_by, last_update_date, last_update_login, object_version_number
3000 )
3001 values
3002 (rec1.position_transaction_id, rec1.action_date, rec1.position_id, rec1.availability_status_id,
3003 rec1.business_group_id, rec1.entry_grade_id,
3004 rec1.entry_step_id, rec1.entry_grade_rule_id, rec1.job_id, rec1.location_id,
3005 rec1.organization_id, rec1.pay_basis_id, rec1.pay_freq_payroll_id,
3006 rec1.position_definition_id, rec1.prior_position_id, rec1.relief_position_id,
3007 rec1.successor_position_id, rec1.supervisor_id,
3008 rec1.supervisor_position_id, rec1.amendment_date, rec1.amendment_recommendation,
3009 rec1.amendment_ref_number, rec1.avail_status_prop_end_date,
3010 rec1.bargaining_unit_cd, rec1.comments, rec1.country1, rec1.country2, rec1.country3,
3011 rec1.current_job_prop_end_date, rec1.current_org_prop_end_date,
3012 rec1.date_effective, rec1.date_end, rec1.earliest_hire_date, rec1.fill_by_date, rec1.frequency,
3013 rec1.fte, rec1.fte_capacity, rec1.location1, rec1.location2,
3014 rec1.location3, rec1.max_persons, rec1.name, rec1.other_requirements, rec1.overlap_period,
3015 rec1.overlap_unit_cd, rec1.passport_required,
3016 rec1.pay_term_end_day_cd, rec1.pay_term_end_month_cd, rec1.permanent_temporary_flag,
3017 rec1.permit_recruitment_flag, rec1.position_type,
3018 rec1.posting_description, rec1.probation_period, rec1.probation_period_unit_cd,
3019 rec1.proposed_fte_for_layoff,
3020 rec1.proposed_date_for_layoff, rec1.relocate_domestically, rec1.relocate_internationally,
3021 rec1.relocation_required,
3022 rec1.replacement_required_flag, rec1.review_flag, rec1.seasonal_flag, rec1.security_requirements,
3023 rec1.service_minimum,
3024 rec1.term_start_day_cd, rec1.term_start_month_cd, rec1.time_normal_finish, rec1.time_normal_start,
3025 rec1.travel_required, rec1.visit_internationally, rec1.working_hours, rec1.works_council_approval_flag,
3026 rec1.work_any_country,
3027 rec1.work_any_location, rec1.work_duration, rec1.work_period_type_cd, rec1.work_schedule,
3028 rec1.work_term_end_day_cd, rec1.work_term_end_month_cd,
3029 rec1.information1, rec1.information2, rec1.information3, rec1.information4, rec1.information5,
3030 rec1.information6, rec1.information7, rec1.information8, rec1.information9, rec1.information10,
3031 rec1.information11, rec1.information12, rec1.information13, rec1.information14, rec1.information15,
3032 rec1.information16, rec1.information17, rec1.information18, rec1.information19, rec1.information20,
3033 rec1.information21, rec1.information22, rec1.information23, rec1.information24, rec1.information25,
3034 rec1.information26, rec1.information27, rec1.information28, rec1.information29, rec1.information30,
3035 rec1.information_category,
3036 rec1.attribute1, rec1.attribute2, rec1.attribute3, rec1.attribute4, rec1.attribute5,
3037 rec1.attribute6, rec1.attribute7, rec1.attribute8, rec1.attribute9, rec1.attribute10,
3038 rec1.attribute11, rec1.attribute12, rec1.attribute13, rec1.attribute14, rec1.attribute15,
3039 rec1.attribute16, rec1.attribute17, rec1.attribute18, rec1.attribute19, rec1.attribute20,
3040 rec1.attribute21, rec1.attribute22, rec1.attribute23, rec1.attribute24, rec1.attribute25,
3041 rec1.attribute26, rec1.attribute27, rec1.attribute28, rec1.attribute29, rec1.attribute30,
3042 rec1.attribute_category,
3043 rec1.created_by, rec1.creation_date, rec1.last_updated_by, rec1.last_update_date, rec1.last_update_login, rec1.object_version_number
3044 );
3045
3046 end;
3047 --------------------------------------------------------------------------------
3048 procedure create_pte_shadow(p_position_transaction_id number) is
3049 cursor c3 is
3050 select *
3051 from pqh_ptx_extra_info
3052 where position_transaction_id = p_position_transaction_id
3053 and position_extra_info_id is not null
3054 and ptx_extra_info_id not in
3055 (select ptx_extra_info_id
3056 from pqh_pte_shadow
3057 where position_transaction_id = p_position_transaction_id);
3058 begin
3059 for rec3 in c3 loop
3060 insert into pqh_pte_shadow
3061 (ptx_extra_info_id, information_type, position_transaction_id, position_extra_info_id,
3062 information_category, information1, information2, information3, information4, information5,
3063 information6, information7, information8, information9, information10,
3064 information11, information12, information13, information14, information15,
3065 information16, information17, information18, information19, information20,
3066 information21, information22, information23, information24, information25,
3067 information26, information27, information28, information29, information30,
3068 attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5,
3069 attribute6, attribute7, attribute8, attribute9, attribute10,
3070 attribute11, attribute12, attribute13, attribute14, attribute15,
3071 attribute16, attribute17, attribute18, attribute19, attribute20,
3072 object_version_number)
3073 values
3074 (rec3.ptx_extra_info_id, rec3.information_type, rec3.position_transaction_id, rec3.position_extra_info_id,
3075 rec3.information_category,
3076 rec3.information1, rec3.information2, rec3.information3, rec3.information4, rec3.information5,
3077 rec3.information6, rec3.information7, rec3.information8, rec3.information9, rec3.information10,
3078 rec3.information11, rec3.information12, rec3.information13, rec3.information14, rec3.information15,
3079 rec3.information16, rec3.information17, rec3.information18, rec3.information19, rec3.information20,
3080 rec3.information21, rec3.information22, rec3.information23, rec3.information24, rec3.information25,
3081 rec3.information26, rec3.information27, rec3.information28, rec3.information29, rec3.information30,
3082 rec3.attribute_category, rec3.attribute1, rec3.attribute2, rec3.attribute3, rec3.attribute4, rec3.attribute5,
3083 rec3.attribute6, rec3.attribute7, rec3.attribute8, rec3.attribute9, rec3.attribute10,
3084 rec3.attribute11, rec3.attribute12, rec3.attribute13, rec3.attribute14, rec3.attribute15,
3085 rec3.attribute16, rec3.attribute17, rec3.attribute18, rec3.attribute19, rec3.attribute20,
3086 1);
3087 end loop;
3088 --
3089 end;
3090 --------------------------------------------------------------------------------
3091 procedure create_tjr_shadow(p_position_transaction_id number) is
3092 cursor c3 is
3093 select *
3094 from pqh_txn_job_requirements
3095 where position_transaction_id = p_position_transaction_id
3096 and job_requirement_id is not null
3097 and txn_job_requirement_id not in
3098 (select txn_job_requirement_id
3099 from pqh_tjr_shadow
3100 where position_transaction_id = p_position_transaction_id);
3101 begin
3102 for rec3 in c3 loop
3103 insert into pqh_tjr_shadow (
3104 txn_job_requirement_id, position_transaction_id, job_requirement_id,
3105 business_group_id, analysis_criteria_id, date_from, date_to,
3106 essential, job_id, object_version_number,
3107 attribute_category, attribute1, attribute2, attribute3,
3108 attribute4, attribute5, attribute6, attribute7, attribute8,
3109 attribute9, attribute10, attribute11, attribute12,
3110 attribute13, attribute14, attribute15, attribute16,
3111 attribute17, attribute18, attribute19, attribute20,
3112 comments
3113 )
3114 values (
3115 rec3.txn_job_requirement_id, rec3.position_transaction_id, rec3.job_requirement_id,
3116 rec3.business_group_id, rec3.analysis_criteria_id, rec3.date_from, rec3.date_to,
3117 rec3.essential, rec3.job_id, 1,
3118 rec3.attribute_category, rec3.attribute1, rec3.attribute2, rec3.attribute3,
3119 rec3.attribute4, rec3.attribute5, rec3.attribute6, rec3.attribute7, rec3.attribute8,
3120 rec3.attribute9, rec3.attribute10, rec3.attribute11, rec3.attribute12,
3121 rec3.attribute13, rec3.attribute14, rec3.attribute15, rec3.attribute16,
3122 rec3.attribute17, rec3.attribute18, rec3.attribute19, rec3.attribute20,
3123 rec3.comments
3124 );
3125 end loop;
3126 --
3127 end;
3128 --------------------------------------------------------------------------------
3129 procedure refresh_ptx(p_transaction_category_id number, p_position_transaction_id number, p_items_changed out nocopy varchar2) is
3130 l_position_id number;
3131 l_pf1_items_changed varchar2(10000);
3132 l_ptx_items_changed varchar2(8000);
3133 p_ptx_deployment_factor_id number;
3134
3135 cursor c_ptx(p_position_transaction_id number) is
3136 select position_id
3137 from pqh_position_transactions
3138 where position_transaction_id = p_position_transaction_id;
3139 --
3140 cursor c_dpf_df(p_position_transaction_id number) is
3141 select ptx_deployment_factor_id
3142 from pqh_ptx_dpf_df
3143 where position_transaction_id = p_position_transaction_id;
3144 --
3145 l_pf1_changed varchar2(10000);
3146 l_pei_changed varchar2(10000);
3147 l_tjr_changed varchar2(10000);
3148 --
3149 begin
3150 open c_ptx(p_position_transaction_id);
3151 fetch c_ptx into l_position_id;
3152 close c_ptx;
3153 -- call the refresh_data pkg for txn table alias PTX
3154 pqh_refresh_data.refresh_data
3155 (p_txn_category_id => p_transaction_category_id,
3156 p_txn_id => p_position_transaction_ID,
3157 p_refresh_criteria => 'PTX',
3158 p_items_changed => l_ptx_items_changed
3159 );
3160 --
3161 open c_dpf_df(p_position_transaction_id);
3162 fetch c_dpf_df into p_ptx_deployment_factor_id;
3163 close c_dpf_df;
3164 if p_ptx_deployment_factor_id is not null then
3165 -- call the refresh_data pkg for txn table alias PF1
3166 pqh_refresh_data.refresh_data
3167 (p_txn_category_id => p_transaction_category_id,
3168 p_txn_id => p_ptx_deployment_factor_ID,
3169 p_refresh_criteria => 'PF1',
3170 p_items_changed => l_pf1_items_changed
3171 );
3172 end if;
3173 --
3174 pqh_ptx_utl.refresh_pte
3175 (p_transaction_category_id => p_transaction_category_id,
3176 p_position_transaction_id => p_position_transaction_ID,
3177 p_position_id => l_position_id,
3178 p_pte_changed => l_pei_changed
3179 );
3180 --
3181 pqh_ptx_utl.refresh_tjr
3182 (p_transaction_category_id => p_transaction_category_id,
3183 p_position_transaction_id => p_position_transaction_ID,
3184 p_position_id => l_position_id,
3185 p_tjr_changed => l_tjr_changed
3186 );
3187 --
3188 if l_pf1_items_changed is not null then
3189 l_pf1_changed := pqh_utility.get_attribute_name('DF1','DEPLOYMENT_DF');
3190 end if;
3191 --
3192 if l_pf1_changed is not null then
3193 if l_ptx_items_changed is null then
3194 l_ptx_items_changed := l_pf1_changed;
3195 else
3196 l_ptx_items_changed := l_ptx_items_changed || fnd_global.local_chr(10) || l_pf1_changed;
3197 end if;
3198 end if;
3199 --
3200 if l_ptx_items_changed is null then
3201 p_items_changed := l_pei_changed;
3202 elsif l_pei_changed is null then
3203 p_items_changed := l_ptx_items_changed;
3204 else
3205 p_items_changed := l_ptx_items_changed || fnd_global.local_chr(10) || l_pei_changed;
3206 end if;
3207 --
3208 if p_items_changed is null then
3209 p_items_changed := l_tjr_changed;
3210 elsif l_tjr_changed is not null then
3211 p_items_changed := p_items_changed || fnd_global.local_chr(10) || l_tjr_changed;
3212 end if;
3213 --
3214 --
3215 end;
3216 --
3217 procedure apply_sit(p_position_transaction_id number, p_position_id number, p_txn_type varchar2) is
3218 l_proc varchar2(72) := g_package||'apply_sit';
3219 l_tjr_rec pqh_txn_job_requirements%ROWTYPE;
3220 l_sit_type varchar2(1);
3221 l_sit_job_requirement_id per_job_requirements.job_requirement_id%type;
3222 l_sit_object_version_number per_job_requirements.object_version_number%type;
3223 l_position_id number;
3224 l_position_extra_info_id number;
3225 l_object_version_number number;
3226
3227 CURSOR c_del_tjr(p_transaction_id number, p_position_id number) IS
3228 select jreq.job_requirement_id, jreq.object_version_number
3229 from per_job_requirements jreq,pqh_tjr_shadow pts
3230 where position_id = p_position_id
3231 and jreq.job_requirement_id = pts.job_requirement_id
3232 and pts.position_transaction_id = p_transaction_id
3233 and not exists (
3234 select null
3235 from pqh_txn_job_requirements ptjr
3236 where position_transaction_id = p_transaction_id
3237 and ptjr.job_requirement_id = pts.job_requirement_id);
3238
3239 CURSOR c3 IS
3240 select *
3241 from pqh_txn_job_requirements
3242 where position_transaction_id = p_position_transaction_id;
3243 cursor c_pes(p_txn_job_requirement_id number) is
3244 select *
3245 from pqh_tjr_shadow
3246 where txn_job_requirement_id = p_txn_job_requirement_id;
3247 CURSOR c5 (p_position_id in hr_all_positions_f.position_id%TYPE,
3248 -- p_information_type in per_position_extra_info.information_type%type,
3249 p_job_requirement_id in
3250 per_job_requirements.job_requirement_id%type) IS
3251 select job_requirement_id,object_version_number
3252 from per_job_requirements
3253 where position_id = p_position_id
3254 -- and information_type = p_information_type
3255 and job_requirement_id = p_job_requirement_id;
3256 --
3257 l_pes_rec c_pes%rowtype;
3258 begin
3259
3260 --
3261 -- TABLE : per_job_requirements
3262 --
3263 -- For update transaction delete the per_job_requirements if deleted from per_job_requirements
3264 if p_position_id is not null and p_txn_type = 'U' then
3265 for r_del_tjr in c_del_tjr(p_position_transaction_id , p_position_id) loop
3266 delete per_job_requirements
3267 where job_requirement_id = r_del_tjr.job_requirement_id;
3268 end loop;
3269 end if;
3270
3271 --
3272 -- create/update the per_job_requirements
3273 --
3274 OPEN c3;
3275 LOOP
3276 FETCH c3 INTO l_tjr_rec;
3277 EXIT WHEN c3%NOTFOUND;
3278 l_sit_type := null;
3279
3280 if l_tjr_rec.job_requirement_id is null then
3281 l_sit_type := 'C';
3282 elsif p_txn_type = 'U' then
3283 --
3284 open c_pes(l_tjr_rec.txn_job_requirement_id);
3285 fetch c_pes into l_pes_rec;
3286 close c_pes;
3287 --
3288 if l_tjr_rec.object_version_number > l_pes_rec.object_version_number then
3289 l_sit_job_requirement_id := null;
3290 open c5(p_position_id,l_tjr_rec.job_requirement_id);
3291 fetch c5 into l_sit_job_requirement_id,l_sit_object_version_number;
3292 close c5;
3293 --
3294 if l_sit_job_requirement_id is null then
3295 l_sit_type := 'I';
3296 else
3297 l_sit_type := 'U';
3298 end if;
3299 end if;
3300 end if;
3301
3302 IF l_sit_type = 'I' THEN
3303 --
3304 hr_utility.set_location('Insert for sit_id '|| l_sit_job_requirement_id
3305 ||l_proc, 20);
3306 insert into per_job_requirements
3307 (
3308 job_requirement_id, business_group_id, analysis_criteria_id,
3309 comments, date_from, date_to, essential, job_id,
3310 position_id,
3311 attribute_category, attribute1, attribute2,
3312 attribute3, attribute4,
3313 attribute5, attribute6, attribute7,
3314 attribute8, attribute9, attribute10,
3315 attribute11, attribute12, attribute13,
3316 attribute14, attribute15,
3317 attribute16, attribute17, attribute18,
3318 attribute19, attribute20,
3319 object_version_number
3320 )
3321 values
3322 (
3323 l_tjr_rec.job_requirement_id, l_tjr_rec.business_group_id, l_tjr_rec.analysis_criteria_id,
3324 l_tjr_rec.comments, l_tjr_rec.date_from, l_tjr_rec.date_to, l_tjr_rec.essential, l_tjr_rec.job_id,
3325 p_position_id,
3326 l_tjr_rec.attribute_category, l_tjr_rec.attribute1, l_tjr_rec.attribute2,
3327 l_tjr_rec.attribute3, l_tjr_rec.attribute4,
3328 l_tjr_rec.attribute5, l_tjr_rec.attribute6, l_tjr_rec.attribute7,
3329 l_tjr_rec.attribute8, l_tjr_rec.attribute9, l_tjr_rec.attribute10,
3330 l_tjr_rec.attribute11, l_tjr_rec.attribute12, l_tjr_rec.attribute13,
3331 l_tjr_rec.attribute14, l_tjr_rec.attribute15,
3332 l_tjr_rec.attribute16, l_tjr_rec.attribute17, l_tjr_rec.attribute18,
3333 l_tjr_rec.attribute19, l_tjr_rec.attribute20,
3334 1
3335 );
3336 --
3337 hr_utility.set_location('After Insert for sit_id '|| l_sit_job_requirement_id
3338 ||l_proc, 25);
3339 ELSIF l_sit_type = 'C' THEN
3340 hr_utility.set_location('Calling create PER_JOB_REQUIREMENTS '
3341 ||l_proc, 30);
3342 --
3343 -- call create API
3344 --
3345 declare
3346 l_job_requirement_id number;
3347 begin
3348 select per_job_requirements_s.nextval into l_job_requirement_id
3349 from dual;
3350 --
3351 insert into per_job_requirements
3352 (
3353 job_requirement_id, business_group_id, analysis_criteria_id,
3354 comments, date_from, date_to, essential, job_id,
3355 position_id,
3356 attribute_category, attribute1, attribute2,
3357 attribute3, attribute4,
3358 attribute5, attribute6, attribute7,
3359 attribute8, attribute9, attribute10,
3360 attribute11, attribute12, attribute13,
3361 attribute14, attribute15,
3362 attribute16, attribute17, attribute18,
3363 attribute19, attribute20,
3364 object_version_number
3365 )
3366 values
3367 (
3368 l_job_requirement_id, l_tjr_rec.business_group_id, l_tjr_rec.analysis_criteria_id,
3369 l_tjr_rec.comments, l_tjr_rec.date_from, l_tjr_rec.date_to, l_tjr_rec.essential, l_tjr_rec.job_id,
3370 p_position_id,
3371 l_tjr_rec.attribute_category, l_tjr_rec.attribute1, l_tjr_rec.attribute2,
3372 l_tjr_rec.attribute3, l_tjr_rec.attribute4,
3373 l_tjr_rec.attribute5, l_tjr_rec.attribute6, l_tjr_rec.attribute7,
3374 l_tjr_rec.attribute8, l_tjr_rec.attribute9, l_tjr_rec.attribute10,
3375 l_tjr_rec.attribute11, l_tjr_rec.attribute12, l_tjr_rec.attribute13,
3376 l_tjr_rec.attribute14, l_tjr_rec.attribute15,
3377 l_tjr_rec.attribute16, l_tjr_rec.attribute17, l_tjr_rec.attribute18,
3378 l_tjr_rec.attribute19, l_tjr_rec.attribute20,
3379 1
3380 );
3381 end;
3382 ELSIF l_sit_type = 'U' then
3383 hr_utility.set_location('Calling update PER_JOB_REQUIREMENTS '
3384 ||l_proc, 35);
3385 --
3386 -- call update API
3387 --
3388 declare
3389 l_job_requirement_id number;
3390 begin
3391 update per_job_requirements
3392 set
3393 business_group_id = l_tjr_rec.business_group_id,
3394 analysis_criteria_id = l_tjr_rec.analysis_criteria_id,
3395 comments = l_tjr_rec.comments,
3396 date_from = l_tjr_rec.date_from,
3397 date_to = l_tjr_rec.date_to,
3398 essential = l_tjr_rec.essential,
3399 job_id = l_tjr_rec.job_id,
3400 position_id = p_position_id,
3401 attribute_category = l_tjr_rec.attribute_category,
3402 attribute1 = l_tjr_rec.attribute1,
3403 attribute2 = l_tjr_rec.attribute2,
3404 attribute3 = l_tjr_rec.attribute3,
3405 attribute4 = l_tjr_rec.attribute4,
3406 attribute5 = l_tjr_rec.attribute5,
3407 attribute6 = l_tjr_rec.attribute6,
3408 attribute7 = l_tjr_rec.attribute7,
3409 attribute8 = l_tjr_rec.attribute8,
3410 attribute9 = l_tjr_rec.attribute9,
3411 attribute10 = l_tjr_rec.attribute10,
3412 attribute11 = l_tjr_rec.attribute11,
3413 attribute12 = l_tjr_rec.attribute12,
3414 attribute13 = l_tjr_rec.attribute13,
3415 attribute14 = l_tjr_rec.attribute14,
3416 attribute15 = l_tjr_rec.attribute15,
3417 attribute16 = l_tjr_rec.attribute16,
3418 attribute17 = l_tjr_rec.attribute17,
3419 attribute18 = l_tjr_rec.attribute18,
3420 attribute19 = l_tjr_rec.attribute19,
3421 attribute20 = l_tjr_rec.attribute20,
3422 object_version_number = object_version_number+1
3423 where job_requirement_id = l_tjr_rec.job_requirement_id;
3424 end; --
3425 END IF; -- api call per_job_requirements
3426 --
3427 END LOOP; -- for c3
3428 CLOSE c3;
3429 --
3430 end;
3431 --
3432
3433 procedure refresh_tjr(p_transaction_category_id number, p_position_transaction_id number, p_position_id number, p_tjr_changed out nocopy varchar2) is
3434 l_position_id number;
3435 l_tjr_items_changed varchar2(8000);
3436 --
3437 cursor c_tjr(p_position_transaction_id number, p_position_id number) is
3438 select txn_job_requirement_id
3439 from pqh_txn_job_requirements
3440 where position_transaction_id = p_position_transaction_id
3441 and job_requirement_id in
3442 (select job_requirement_id
3443 from per_job_requirements
3444 where position_id = p_position_id
3445 );
3446 --
3447 cursor c_prs_tjr(p_position_transaction_id number, p_position_id number) is
3448 select tjr.txn_job_requirement_id, tjr.object_version_number
3449 from pqh_txn_job_requirements tjr, pqh_tjr_shadow pts
3450 where tjr.txn_job_requirement_id = pts.txn_job_requirement_id
3451 and tjr.position_transaction_id = pts.position_transaction_id
3452 and tjr.position_transaction_id = p_position_transaction_id
3453 and not exists
3454 (select null
3455 from per_job_requirements pjr
3456 where position_id = p_position_id
3457 and pjr.job_requirement_id = pts.job_requirement_id);
3458 --
3459 l_tjr_classification varchar2(100);
3460 l_index number;
3461 l_tjr_changed varchar2(10000);
3462 --
3463 begin
3464 l_position_id := p_position_id;
3465 --
3466 for r_tjr in c_tjr(p_position_transaction_id, l_position_id)
3467 loop
3468 pqh_refresh_data.refresh_data
3469 (p_txn_category_id => p_transaction_category_id,
3470 p_txn_id => r_tjr.txn_job_requirement_id,
3471 p_refresh_criteria => 'TJR',
3472 p_items_changed => l_tjr_items_changed
3473 );
3474 hr_utility.set_location('l_tjr_items_changed:'||nvl(l_tjr_items_changed,'NULL'), 1000);
3475 if l_tjr_items_changed is not null then
3476 l_tjr_classification := pqh_utility.get_tjr_classification(r_tjr.txn_job_requirement_id);
3477 hr_utility.set_location('l_tjr_classification:'||nvl(l_tjr_classification,'NULL'), 1000);
3478 append_if_not_present(l_tjr_changed, l_tjr_classification);
3479 end if;
3480 end loop;
3481 --
3482 for r_prs_tjr in c_prs_tjr(p_position_transaction_id, l_position_id)
3483 loop
3484 --
3485 if r_prs_tjr.object_version_number = 1 then
3486 l_tjr_classification := pqh_utility.get_tjr_classification(r_prs_tjr.txn_job_requirement_id);
3487 hr_utility.set_location('l_tjr_classification:'||nvl(l_tjr_classification,'NULL'), 1000);
3488 append_if_not_present(l_tjr_changed, l_tjr_classification);
3489 --
3490 delete pqh_txn_job_requirements
3491 where txn_job_requirement_id = r_prs_tjr.txn_job_requirement_id;
3492 --
3493 delete pqh_tjr_shadow
3494 where txn_job_requirement_id = r_prs_tjr.txn_job_requirement_id;
3495 end if;
3496 end loop;
3497 --
3498 populate_job_requirements(p_position_transaction_id, l_position_id, l_tjr_changed);
3499 create_tjr_shadow(p_position_transaction_id);
3500 --
3501 if l_tjr_changed is not null then
3502 fnd_message.set_name('PQH', 'PQH_REQUIREMENTS_LIST');
3503 fnd_message.set_token('CLASSIFICATIONS', l_tjr_changed);
3504 l_tjr_changed := fnd_message.get;
3505 end if;
3506 --
3507 p_tjr_changed := l_tjr_changed;
3508 end;
3509 --
3510 --------------------------------------------------------------------------------
3511 procedure refresh_pte(p_transaction_category_id number, p_position_transaction_id number,
3512 p_position_id number, p_pte_changed out nocopy varchar2) is
3513 l_pte_items_changed varchar2(8000);
3514 l_pte_context_desc varchar2(100);
3515 l_index number;
3516 l_pei_changed varchar2(10000);
3517 --
3518 cursor c_pte(p_position_transaction_id number, p_position_id number) is
3519 select ptx_extra_info_id
3520 from pqh_ptx_extra_info
3521 where position_transaction_id = p_position_transaction_id
3522 and position_extra_info_id in
3523 (select position_extra_info_id
3524 from per_position_extra_info
3525 where position_id = p_position_id
3526 and information_type <> 'PQH_POS_ROLE_ID');
3527 --
3528 cursor c_pes_poe(p_position_transaction_id number, p_position_id number) is
3529 select tei.ptx_extra_info_id, tei.object_version_number
3530 from pqh_ptx_extra_info tei, pqh_pte_shadow pps
3531 where
3532 tei.information_type <> 'PQH_POS_ROLE_ID'
3533 and tei.ptx_extra_info_id = pps.ptx_extra_info_id
3534 and tei.position_transaction_id = pps.position_transaction_id
3535 and tei.position_transaction_id = p_position_transaction_id
3536 and not exists (
3537 select null
3538 from per_position_extra_info ppei
3539 where position_id = p_position_id
3540 and ppei.position_extra_info_id = pps.position_extra_info_id);
3541 --
3542 begin
3543 --
3544 for r_pte in c_pte(p_position_transaction_id, p_position_id)
3545 loop
3546 pqh_refresh_data.refresh_data
3547 (p_txn_category_id => p_transaction_category_id,
3548 p_txn_id => r_pte.ptx_extra_info_ID,
3549 p_refresh_criteria => 'PTE',
3550 p_items_changed => l_pte_items_changed
3551 );
3552 hr_utility.set_location('l_pte_items_changed:'||nvl(l_pte_items_changed,'NULL'), 1000);
3553 if l_pte_items_changed is not null then
3554 l_pte_context_desc := pqh_utility.get_pte_context_desc(r_pte.ptx_extra_info_ID);
3555 hr_utility.set_location('l_pte_context_desc:'||nvl(l_pte_context_desc,'NULL'), 1000);
3556 append_if_not_present(l_pei_changed, l_pte_context_desc);
3557 end if;
3558 end loop;
3559 --
3560 for r_pes_poe in c_pes_poe(p_position_transaction_id, p_position_id)
3561 loop
3562 --
3563 if r_pes_poe.object_version_number = 1 then
3564 l_pte_context_desc := pqh_utility.get_pte_context_desc(r_pes_poe.ptx_extra_info_ID);
3565 hr_utility.set_location('l_pte_context_desc:'||nvl(l_pte_context_desc,'NULL'), 1000);
3566 append_if_not_present(l_pei_changed, l_pte_context_desc);
3567 --
3568 delete pqh_ptx_extra_info
3569 where ptx_extra_info_id = r_pes_poe.ptx_extra_info_id;
3570 --
3571 delete pqh_pte_shadow
3572 where ptx_extra_info_id = r_pes_poe.ptx_extra_info_id;
3573 end if;
3574 end loop;
3575 --
3576 populate_pei(p_position_transaction_id, p_position_id, l_pei_changed);
3577 create_pte_shadow(p_position_transaction_id);
3578 --
3579 if l_pei_changed is not null then
3580 fnd_message.set_name('PQH', 'PQH_EXTRA_INFO_LIST');
3581 fnd_message.set_token('INFORMATION_TYPES', l_pei_changed);
3582 p_pte_changed := fnd_message.get;
3583 end if;
3584 --
3585 --
3586 end;
3587 --
3588 -- ---------------------------------------------------------------------------
3589 -- --------------------------< chk_resesrved_fte >----------------------------
3590 -- ---------------------------------------------------------------------------
3591
3592 Procedure chk_reserved_fte
3593 (p_position_id in number
3594 ,p_fte in number
3595 ,p_position_type in varchar2
3596 ,p_effective_date in date
3597 ,p_validation_start_date in date
3598 ,p_validation_end_date in date
3599 ) is
3600
3601 l_proc varchar2(72) := 'chk_reserved_fte';
3602 l_api_updating boolean;
3603 l_rsv_fte number;
3604
3605 cursor csr_valid_fte(p_position_id number, p_effective_date date) is
3606 select sum(poei_information6) fte
3607 from per_position_extra_info
3608 where position_id = p_position_id
3609 and information_type= 'PER_RESERVED'
3610 and p_effective_date
3611 between fnd_date.canonical_to_date(poei_information3)
3612 and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time);
3613 --
3614 cursor csr_valid_eff_date(p_position_id number, p_validation_start_date date, p_validation_end_date date) is
3615 select p_validation_start_date start_date
3616 from dual
3617 union
3618 select start_date
3619 from (select fnd_date.canonical_to_date(poei_information3) start_date
3620 from per_position_extra_info
3621 where position_id = p_position_id
3622 and information_type = 'PER_RESERVED') a
3623 where a.start_date between p_validation_start_date and p_validation_end_date;
3624 Begin
3625 --
3626 hr_utility.set_location('Entering:'||l_proc, 5);
3627 --
3628 --
3629 for r2 in csr_valid_eff_date(p_position_id, p_validation_start_date, p_validation_end_date) loop
3630 if p_position_type ='SHARED' or p_position_type ='SINGLE' then
3631 open csr_valid_fte(p_position_id, r2.start_date);
3632 fetch csr_valid_fte into l_rsv_fte;
3633 if (p_fte < l_rsv_fte) then
3634 hr_utility.set_message(800,'PER_FTE_LT_RSVD_FTE');
3635 hr_utility.set_message_token('POSITION_FTE',p_fte);
3636 hr_utility.set_message_token('RESERVED_FTE',l_rsv_fte);
3637 hr_utility.set_message_token('EFFECTIVE_DATE',r2.start_date);
3638 hr_utility.raise_error;
3639 else
3640 hr_utility.set_location(l_proc, 3);
3641 end if;
3642 --
3643 close csr_valid_fte;
3644 end if;
3645 end loop;
3646 --
3647 end chk_reserved_fte;
3648 --
3649 --
3650 --
3651 --
3652 procedure recalculate_bvr_avail(p_budget_version_id number) is
3653 --
3654 cursor c_bvr(p_budget_version_id number) is
3655 select bgt.budget_style_cd,
3656 budget_version_id, bvr.object_version_number,
3657 budget_unit1_value, budget_unit2_value, budget_unit3_value
3658 from pqh_budget_versions bvr, pqh_budgets bgt
3659 where budget_version_id = p_budget_version_id
3660 and bvr.budget_id = bgt.budget_id;
3661 --
3662 cursor c_total_bdt(p_budget_version_id number) is
3663 SELECT sum(nvl(BUDGET_UNIT1_VALUE,0)) ,
3664 sum(nvl(BUDGET_UNIT2_VALUE,0)) ,
3665 sum(nvl(BUDGET_UNIT3_VALUE,0))
3666 FROM pqh_budget_details
3667 WHERE budget_version_id = p_budget_version_id;
3668 --
3669 l_budget_unit1_available number;
3670 l_budget_unit2_available number;
3671 l_budget_unit3_available number;
3672 --
3673 l_bdt_unit1 number;
3674 l_bdt_unit2 number;
3675 l_bdt_unit3 number;
3676 --
3677 begin
3678 for r_bvr in c_bvr(p_budget_version_id)
3679 loop
3680 --
3681 open c_total_bdt(p_budget_version_id);
3682 fetch c_total_bdt into l_bdt_unit1, l_bdt_unit2, l_bdt_unit3;
3683 close c_total_bdt;
3684 --
3685 if r_bvr.budget_style_cd = 'BOTTOM' then
3686 --
3687 pqh_budget_versions_api.update_budget_version
3688 (
3689 p_validate => false
3690 ,p_budget_version_id => r_bvr.budget_version_id
3691 ,p_object_version_number => r_bvr.object_version_number
3692 ,p_budget_unit1_value => l_bdt_unit1
3693 ,p_budget_unit2_value => l_bdt_unit2
3694 ,p_budget_unit3_value => l_bdt_unit3
3695 ,p_budget_unit1_available => null
3696 ,p_budget_unit2_available => null
3697 ,p_budget_unit3_available => null
3698 ,p_effective_date => sysdate
3699 );
3700 else
3701 --
3702 if nvl(r_bvr.budget_unit1_value,0) >0 then
3703 l_budget_unit1_available := r_bvr.budget_unit1_value - l_bdt_unit1;
3704 else
3705 l_budget_unit1_available := null;
3706 end if;
3707 if nvl(r_bvr.budget_unit2_value,0) >0 then
3708 l_budget_unit2_available := r_bvr.budget_unit2_value - l_bdt_unit2;
3709 else
3710 l_budget_unit2_available := null;
3711 end if;
3712 if nvl(r_bvr.budget_unit3_value,0) >0 then
3713 l_budget_unit3_available := r_bvr.budget_unit3_value - l_bdt_unit3;
3714 else
3715 l_budget_unit3_available := null;
3716 end if;
3717 --
3718 pqh_budget_versions_api.update_budget_version
3719 (
3720 p_validate => false
3721 ,p_budget_version_id => r_bvr.budget_version_id
3722 ,p_object_version_number => r_bvr.object_version_number
3723 ,p_budget_unit1_available => l_budget_unit1_available
3724 ,p_budget_unit2_available => l_budget_unit2_available
3725 ,p_budget_unit3_available => l_budget_unit3_available
3726 ,p_effective_date => sysdate
3727 );
3728 end if;
3729 end loop;
3730 end;
3731 --
3732 --
3733 --
3734 --
3735 FUNCTION get_unit_precision(p_currency_code in varchar2) RETURN number IS
3736 l_ext_precision number;
3737 l_precision number;
3738 l_min_acct number;
3739 BEGIN
3740 fnd_currency.get_info(currency_code => p_currency_code,
3741 precision => l_precision,
3742 ext_precision => l_ext_precision,
3743 min_acct_unit => l_min_acct);
3744 return l_precision;
3745 END;
3746 --
3747 --
3748 procedure get_unit_precision(p_budget_id number,
3749 p_unit1_precision OUT NOCOPY varchar2,
3750 p_unit2_precision OUT NOCOPY varchar2,
3751 p_unit3_precision OUT NOCOPY varchar2) as
3752 --
3753 cursor c1 is select currency_code
3754 from per_business_groups
3755 where business_group_id = hr_general.get_business_group_id;
3756 --
3757 cursor b1(p_budget_id number) is
3758 select currency_code, budget_unit1_id, budget_unit2_id, budget_unit3_id,
3759 pqh_wks_budget.get_unit_type(budget_unit1_id),
3760 pqh_wks_budget.get_unit_type(budget_unit2_id),
3761 pqh_wks_budget.get_unit_type(budget_unit3_id)
3762 from pqh_budgets
3763 where budget_id = p_budget_id;
3764 --
3765 l_budget_currency_code varchar2(15);
3766 l_budget_unit1_id number;
3767 l_budget_unit2_id number;
3768 l_budget_unit3_id number;
3769 l_budget_unit1_type_cd varchar2(15);
3770 l_budget_unit2_type_cd varchar2(15);
3771 l_budget_unit3_type_cd varchar2(15);
3772 l_currency_code varchar2(15);
3773 begin
3774 open b1(p_budget_id);
3775 fetch b1 into l_budget_currency_code, l_budget_unit1_id,
3776 l_budget_unit2_id, l_budget_unit3_id,
3777 l_budget_unit1_type_cd, l_budget_unit2_type_cd,
3778 l_budget_unit3_type_cd;
3779 if l_budget_currency_code is null then
3780 open c1;
3781 fetch c1 into l_currency_code;
3782 close c1;
3783 else
3784 l_currency_code := l_budget_currency_code;
3785 end if;
3786 --
3787 if l_budget_unit1_id is not null then
3788 --
3789 if l_budget_unit1_type_cd ='MONEY' then
3790 --
3791 if l_currency_code is not null then
3792 p_unit1_precision := get_unit_precision(l_currency_code);
3793 else
3794 p_unit1_precision := 2;
3795 end if;
3796 --
3797 else
3798 p_unit1_precision := 2;
3799 end if;
3800 end if;
3801 if l_budget_unit2_id is not null then
3802 --
3803 if l_budget_unit2_type_cd ='MONEY' then
3804 --
3805 if l_budget_currency_code is not null then
3806 p_unit2_precision := get_unit_precision(l_currency_code);
3807 else
3808 p_unit2_precision := 2;
3809 end if;
3810 --
3811 else
3812 p_unit2_precision := 2;
3813 end if;
3814 end if;
3815 if l_budget_unit3_id is not null then
3816 --
3817 if l_budget_unit3_type_cd ='MONEY' then
3818 --
3819 if l_currency_code is not null then
3820 p_unit3_precision := get_unit_precision(l_currency_code);
3821 else
3822 p_unit3_precision := 2;
3823 end if;
3824 --
3825 else
3826 p_unit3_precision := 2;
3827 end if;
3828 end if;
3829 end;
3830 --
3831 --
3832 procedure default_budget_set_info(p_budget_period_id number, p_dflt_budget_set_id number,
3833 p_period_unit1_value number,
3834 p_period_unit2_value number,
3835 p_period_unit3_value number) is
3836 l_budget_set_id number(15);
3837 l_object_version_number number(15);
3838 begin
3839 if p_dflt_budget_set_id is not null then
3840 --
3841 -- new period is added for a posted budget
3842 -- budget_detail_chg_to_gl;
3843
3844 pqh_budget_sets_api.create_budget_set(
3845 p_validate => FALSE
3846 ,p_budget_set_id => l_budget_set_id
3847 ,p_dflt_budget_set_id => p_dflt_budget_set_id
3848 ,p_budget_period_id => p_budget_period_id
3849 ,p_budget_unit1_value => p_period_unit1_value
3850 ,p_budget_unit1_percent => 100
3851 ,p_budget_unit1_available => p_period_unit1_value
3852 ,p_budget_unit1_value_type_cd => 'V'
3853 ,p_budget_unit2_value => p_period_unit2_value
3854 ,p_budget_unit2_percent => 100
3855 ,p_budget_unit2_available => p_period_unit2_value
3856 ,p_budget_unit2_value_type_cd => 'V'
3857 ,p_budget_unit3_value => p_period_unit3_value
3858 ,p_budget_unit3_percent => 100
3859 ,p_budget_unit3_value_type_cd => 'V'
3860 ,p_budget_unit3_available => p_period_unit3_value
3861 ,p_object_version_number => l_object_version_number
3862 ,p_effective_date => trunc(sysdate)
3863 );
3864
3865 --
3866 -- The following call will pull the default element and fund sources from
3867 -- pqh_dflt_budget_elements and pqh_dflt_fund_srcs and insert in
3868 -- pqh_budget_elements and pqh_budget_fund_srcs.
3869 --
3870
3871 pqh_wks_budget.insert_budgetset
3872 (p_dflt_budget_set_id => p_dflt_budget_set_id,
3873 p_budget_set_id => l_budget_set_id
3874 );
3875 end if;
3876 end;
3877 --
3878 --
3879 --
3880 procedure apply_ptx_budgets(p_position_transaction_id number) is
3881 --
3882 cursor c_worksheets(p_position_transaction_id number) is
3883 select distinct wks.worksheet_id, wks.budget_version_id,
3884 wdt.worksheet_detail_id, wdt.budget_detail_id
3885 from pqh_worksheets wks, pqh_worksheet_details wdt
3886 where wks.worksheet_id = wdt.worksheet_id
3887 and wks.worksheet_mode_cd = 'O'
3888 and wdt.position_transaction_id = p_position_transaction_id;
3889 --
3890 cursor c_worksheet_details(p_worksheet_detail_id number) is
3891 select *
3892 from pqh_worksheet_details
3893 where worksheet_detail_id = p_worksheet_detail_id;
3894 --
3895 cursor c_worksheet_periods(p_worksheet_detail_id number) is
3896 select *
3897 from pqh_worksheet_periods
3898 where worksheet_detail_id = p_worksheet_detail_id;
3899 --
3900 CURSOR units_csr(p_budget_version_id number) IS
3901 SELECT nvl(BUDGET_UNIT1_VALUE,0) ,
3902 nvl(BUDGET_UNIT2_VALUE,0) ,
3903 nvl(BUDGET_UNIT3_VALUE,0),
3904 bgt.budget_style_cd,
3905 bgt.dflt_budget_set_id
3906 FROM pqh_budget_versions bvr, pqh_budgets bgt
3907 WHERE budget_version_id = p_budget_version_id
3908 and bvr.budget_id = bgt.budget_id;
3909 --
3910 CURSOR l_object_version_number_cur(p_budget_detail_id number) IS
3911 SELECT object_version_number
3912 FROM pqh_budget_details
3913 WHERE budget_detail_id = p_budget_detail_id;
3914 --
3915 CURSOR l_bpr_ovn_cur(p_budget_period_id number) IS
3916 SELECT object_version_number
3917 FROM pqh_budget_periods
3918 WHERE budget_period_id = p_budget_period_id;
3919 --
3920 l_proc varchar2(72) := g_package||'apply_ptx_budgets';
3921 l_budget_style_cd pqh_budgets.budget_style_cd%type;
3922 l_budget_detail_id number;
3923 l_budget_period_id number;
3924 l_version_unit1_value number;
3925 l_version_unit2_value number;
3926 l_version_unit3_value number;
3927 l_budget_unit1_percent number;
3928 l_budget_unit2_percent number;
3929 l_budget_unit3_percent number;
3930 l_object_version_number pqh_budget_details.object_version_number%TYPE;
3931 l_bpr_object_version_number pqh_budget_periods.object_version_number%TYPE;
3932 l_propagate varchar2(15);
3933 l_bpr_budget_unit1_available number;
3934 l_bpr_budget_unit2_available number;
3935 l_bpr_budget_unit3_available number;
3936 l_unit1_precision number;
3937 l_unit2_precision number;
3938 l_unit3_precision number;
3939 l_dflt_budget_set_id number;
3940 --
3941 begin
3942 for r_wst in c_worksheets(p_position_transaction_id)
3943 loop
3944 -- compute the unit values
3945 OPEN units_csr(r_wst.budget_version_id);
3946 FETCH units_csr INTO l_version_unit1_value, l_version_unit2_value,
3947 l_version_unit3_value,
3948 l_budget_style_cd, l_dflt_budget_set_id;
3949 CLOSE units_csr;
3950 --
3951 for p_worksheet_details_rec in c_worksheet_details(r_wst.worksheet_detail_id)
3952 loop
3953
3954 if l_budget_style_cd = 'BOTTOM' then
3955 l_budget_unit1_percent := null;
3956 l_budget_unit2_percent := null;
3957 l_budget_unit3_percent := null;
3958 else
3959 if nvl(l_version_unit1_value,0) >0 then
3960 l_budget_unit1_percent := (p_worksheet_details_rec.budget_unit1_value*100)/l_version_unit1_value ;
3961 else
3962 l_budget_unit1_percent := null;
3963 end if;
3964 if nvl(l_version_unit2_value,0) >0 then
3965 l_budget_unit2_percent := (p_worksheet_details_rec.budget_unit2_value*100)/l_version_unit2_value ;
3966 else
3967 l_budget_unit2_percent := null;
3968 end if;
3969 if nvl(l_version_unit3_value,0) >0 then
3970 l_budget_unit3_percent := (p_worksheet_details_rec.budget_unit3_value*100)/l_version_unit3_value ;
3971 else
3972 l_budget_unit3_percent := null;
3973 end if;
3974 end if;
3975 --
3976 if p_worksheet_details_rec.budget_detail_id is not null then
3977 l_budget_detail_id := p_worksheet_details_rec.budget_detail_id;
3978 -- update rows where p_worksheet_details_rec.budget_detail_id IS NOT NULL
3979 hr_utility.set_location('Budget Detail Id : '||p_worksheet_details_rec.budget_detail_id, 7);
3980
3981 -- get the object_version_number for this budget_detail_id and pass to update API
3982 OPEN l_object_version_number_cur(p_worksheet_details_rec.budget_detail_id);
3983 FETCH l_object_version_number_cur INTO l_object_version_number;
3984 CLOSE l_object_version_number_cur;
3985
3986 hr_utility.set_location('Update API OVN : '||l_object_version_number, 8);
3987
3988 pqh_budget_details_api.update_budget_detail
3989 (
3990 p_validate => false
3991 ,p_budget_detail_id => p_worksheet_details_rec.budget_detail_id
3992 ,p_organization_id => p_worksheet_details_rec.organization_id
3993 ,p_job_id => p_worksheet_details_rec.job_id
3994 ,p_position_id => p_worksheet_details_rec.position_id
3995 ,p_grade_id => p_worksheet_details_rec.grade_id
3996 ,p_budget_version_id => r_wst.budget_version_id
3997 ,p_budget_unit1_percent => l_budget_unit1_percent
3998 ,p_budget_unit1_value_type_cd => p_worksheet_details_rec.budget_unit1_value_type_cd
3999 ,p_budget_unit1_value => p_worksheet_details_rec.budget_unit1_value
4000 ,p_budget_unit1_available => p_worksheet_details_rec.budget_unit1_available
4001 ,p_budget_unit2_percent => l_budget_unit2_percent
4002 ,p_budget_unit2_value_type_cd => p_worksheet_details_rec.budget_unit2_value_type_cd
4003 ,p_budget_unit2_value => p_worksheet_details_rec.budget_unit2_value
4004 ,p_budget_unit2_available => p_worksheet_details_rec.budget_unit2_available
4005 ,p_budget_unit3_percent => l_budget_unit3_percent
4006 ,p_budget_unit3_value_type_cd => p_worksheet_details_rec.budget_unit3_value_type_cd
4007 ,p_budget_unit3_value => p_worksheet_details_rec.budget_unit3_value
4008 ,p_budget_unit3_available => p_worksheet_details_rec.budget_unit3_available
4009 ,p_object_version_number => l_object_version_number
4010 );
4011 l_budget_detail_id := p_worksheet_details_rec.budget_detail_id;
4012 else
4013 -- for others i.e new rows call the insert API
4014
4015 hr_utility.set_location('Create API in update mode : ', 9);
4016
4017 pqh_budget_details_api.create_budget_detail
4018 (
4019 p_validate => false
4020 ,p_budget_detail_id => l_budget_detail_id
4021 ,p_organization_id => p_worksheet_details_rec.organization_id
4022 ,p_job_id => p_worksheet_details_rec.job_id
4023 ,p_position_id => p_worksheet_details_rec.position_id
4024 ,p_grade_id => p_worksheet_details_rec.grade_id
4025 ,p_budget_version_id => r_wst.budget_version_id
4026 ,p_budget_unit1_percent => l_budget_unit1_percent
4027 ,p_budget_unit1_value_type_cd => p_worksheet_details_rec.budget_unit1_value_type_cd
4028 ,p_budget_unit1_value => p_worksheet_details_rec.budget_unit1_value
4029 ,p_budget_unit1_available => p_worksheet_details_rec.budget_unit1_available
4030 ,p_budget_unit2_percent => l_budget_unit2_percent
4031 ,p_budget_unit2_value_type_cd => p_worksheet_details_rec.budget_unit2_value_type_cd
4032 ,p_budget_unit2_value => p_worksheet_details_rec.budget_unit2_value
4033 ,p_budget_unit2_available => p_worksheet_details_rec.budget_unit2_available
4034 ,p_budget_unit3_percent => l_budget_unit3_percent
4035 ,p_budget_unit3_value_type_cd => p_worksheet_details_rec.budget_unit3_value_type_cd
4036 ,p_budget_unit3_value => p_worksheet_details_rec.budget_unit3_value
4037 ,p_budget_unit3_available => p_worksheet_details_rec.budget_unit3_available
4038 ,p_object_version_number => l_object_version_number
4039 );
4040 end if;
4041
4042 -- Apply Budget Periods
4043 for p_worksheet_periods_rec in c_worksheet_periods(r_wst.worksheet_detail_id)
4044 loop
4045 --
4046 if p_worksheet_periods_rec.budget_period_id is not null then
4047 l_budget_period_id := p_worksheet_periods_rec.budget_period_id;
4048 -- update rows where p_worksheet_periods_rec.budget_period_id IS NOT NULL
4049 hr_utility.set_location('Budget Period Id : '||p_worksheet_periods_rec.budget_period_id, 7);
4050
4051 -- Populate all other levels
4052 -- pqh_apply_budget.ptx_budget_periods(r_wst.worksheet_detail_id);
4053 if l_budget_period_id is not null then
4054 begin
4055 --l_propagate := check_budget_details.chk_period_propagate(p_budget_period_id => l_budget_period_id);
4056 l_propagate := 'TRUE';
4057 if l_propagate = 'TRUE' then
4058 BEGIN
4059 get_unit_precision(1 ,
4060 l_unit1_precision,
4061 l_unit2_precision,
4062 l_unit3_precision);
4063
4064 pqh_bdgt.propagate_period_changes(
4065 p_change_mode => 'UE',
4066 p_budget_period_id => l_budget_period_id,
4067 p_new_prd_unit1_value => p_worksheet_periods_rec.budget_unit1_value,
4068 p_new_prd_unit2_value => p_worksheet_periods_rec.budget_unit2_value,
4069 p_new_prd_unit3_value => p_worksheet_periods_rec.budget_unit3_value,
4070 p_unit1_precision => l_unit1_precision, --:budgets.unit1_precision,
4071 p_unit2_precision => l_unit2_precision, --:budgets.unit2_precision,
4072 p_unit3_precision => l_unit3_precision, --:budgets.unit3_precision,
4073 p_prd_unit1_available => l_bpr_budget_unit1_available,
4074 p_prd_unit2_available => l_bpr_budget_unit2_available,
4075 p_prd_unit3_available => l_bpr_budget_unit3_available);
4076 EXCEPTION
4077 when others then
4078 fnd_message.set_name('PQH','PQH_WKS_PERIOD_PROP_ERROR');
4079 fnd_message.raise_error;
4080 END;
4081 end if;
4082 end;
4083 end if;
4084
4085 -- get the object_version_number for this budget_detail_id and pass to update API
4086 OPEN l_bpr_ovn_cur(p_worksheet_periods_rec.budget_period_id);
4087 FETCH l_bpr_ovn_cur INTO l_bpr_object_version_number;
4088 CLOSE l_bpr_ovn_cur;
4089
4090 hr_utility.set_location('Update API OVN : '||l_bpr_object_version_number, 8);
4091
4092 pqh_budget_periods_api.update_budget_period
4093 (
4094 p_validate => false
4095 ,p_budget_period_id => l_budget_period_id
4096 ,p_budget_detail_id => l_budget_detail_id
4097 ,p_start_time_period_id => p_worksheet_periods_rec.start_time_period_id
4098 ,p_end_time_period_id => p_worksheet_periods_rec.end_time_period_id
4099 ,p_budget_unit1_percent => p_worksheet_periods_rec.budget_unit1_percent
4100 ,p_budget_unit2_percent => p_worksheet_periods_rec.budget_unit2_percent
4101 ,p_budget_unit3_percent => p_worksheet_periods_rec.budget_unit3_percent
4102 ,p_budget_unit1_value => p_worksheet_periods_rec.budget_unit1_value
4103 ,p_budget_unit2_value => p_worksheet_periods_rec.budget_unit2_value
4104 ,p_budget_unit3_value => p_worksheet_periods_rec.budget_unit3_value
4105 ,p_budget_unit1_value_type_cd => p_worksheet_periods_rec.budget_unit1_value_type_cd
4106 ,p_budget_unit2_value_type_cd => p_worksheet_periods_rec.budget_unit2_value_type_cd
4107 ,p_budget_unit3_value_type_cd => p_worksheet_periods_rec.budget_unit3_value_type_cd
4108 ,p_budget_unit1_available => l_bpr_budget_unit1_available --p_worksheet_periods_rec.budget_unit1_available
4109 ,p_budget_unit2_available => l_bpr_budget_unit2_available --p_worksheet_periods_rec.budget_unit2_available
4110 ,p_budget_unit3_available => l_bpr_budget_unit3_available --p_worksheet_periods_rec.budget_unit3_available
4111 ,p_object_version_number => l_bpr_object_version_number
4112 );
4113 else
4114 -- for others i.e new rows call the insert API
4115
4116 hr_utility.set_location('Create API in update mode : ', 9);
4117
4118 pqh_budget_periods_api.create_budget_period
4119 (
4120 p_validate => false
4121 ,p_budget_period_id => l_budget_period_id
4122 ,p_budget_detail_id => l_budget_detail_id
4123 ,p_start_time_period_id => p_worksheet_periods_rec.start_time_period_id
4124 ,p_end_time_period_id => p_worksheet_periods_rec.end_time_period_id
4125 ,p_budget_unit1_percent => p_worksheet_periods_rec.budget_unit1_percent
4126 ,p_budget_unit2_percent => p_worksheet_periods_rec.budget_unit2_percent
4127 ,p_budget_unit3_percent => p_worksheet_periods_rec.budget_unit3_percent
4128 ,p_budget_unit1_value => p_worksheet_periods_rec.budget_unit1_value
4129 ,p_budget_unit2_value => p_worksheet_periods_rec.budget_unit2_value
4130 ,p_budget_unit3_value => p_worksheet_periods_rec.budget_unit3_value
4131 ,p_budget_unit1_value_type_cd => p_worksheet_periods_rec.budget_unit1_value_type_cd
4132 ,p_budget_unit2_value_type_cd => p_worksheet_periods_rec.budget_unit2_value_type_cd
4133 ,p_budget_unit3_value_type_cd => p_worksheet_periods_rec.budget_unit3_value_type_cd
4134 ,p_budget_unit1_available => p_worksheet_periods_rec.budget_unit1_available
4135 ,p_budget_unit2_available => p_worksheet_periods_rec.budget_unit2_available
4136 ,p_budget_unit3_available => p_worksheet_periods_rec.budget_unit3_available
4137 ,p_object_version_number => l_bpr_object_version_number
4138 );
4139 --
4140 --
4141 --
4142 default_budget_set_info(
4143 p_budget_period_id => l_budget_period_id,
4144 p_dflt_budget_set_id => l_dflt_budget_set_id,
4145 p_period_unit1_value => p_worksheet_periods_rec.budget_unit1_value,
4146 p_period_unit2_value => p_worksheet_periods_rec.budget_unit2_value,
4147 p_period_unit3_value => p_worksheet_periods_rec.budget_unit3_value);
4148 --
4149 --
4150 --
4151 end if;
4152 --
4153 --
4154 end loop;
4155 --
4156 end loop;
4157 -- Re-calculate budget version available
4158 recalculate_bvr_avail(r_wst.budget_version_id);
4159 end loop;
4160 --
4161 end;
4162 --
4163 end pqh_ptx_utl;