[Home] [Help]
PACKAGE BODY: APPS.PER_SP_STAGE
Source
1 PACKAGE BODY PER_SP_STAGE AS
2 /* $Header: perspstage.pkb 120.3 2011/04/19 06:13:55 vkodedal noship $ */
3
4 g_package varchar2(30) := ' per_sp_stage.';
5 g_debug boolean ;
6 g_no_transaction_id exception;
7 g_wf_not_initialzed exception;
8 g_wf_error_state exception;
9 g_invalid_responsibility exception;
10 g_transaction_status_invalid exception;
11
12
13
14 --
15 -- ---------------------------------------------------------------------------
16 -- |----------------------< createHRApiTransaction >--------------------------|
17 -- ---------------------------------------------------------------------------
18 --
19
20 procedure createHRApiTransaction(p_person_id in number,
21 p_copy_entity_txn_id in number,
22 p_hr_api_txn_id out nocopy number) is
23
24 PRAGMA AUTONOMOUS_TRANSACTION;
25
26 cursor csrHrApiTxnId is
27 select TRANSACTION_ID
28 from HR_API_TRANSACTIONS
29 where TRANSACTION_REF_ID=p_copy_entity_txn_id
30 and TRANSACTION_REF_TABLE='PER_SP_PLAN'
31 and PROCESS_NAME='HR_SP_APPROVAL_PRC';
32
33 begin
34
35 hr_utility.set_location('Creating createHRApiTransaction',10);
36
37 open csrHrApiTxnId;
38 fetch csrHrApiTxnId into p_hr_api_txn_id;
39 close csrHrApiTxnId;
40
41 hr_utility.set_location('p_hr_api_txn_id:'||p_hr_api_txn_id,15);
42
43 if p_hr_api_txn_id is null then
44 p_hr_api_txn_id := HR_API_TRANSACTIONS_S.NEXTVAL;
45
46 INSERT INTO HR_API_TRANSACTIONS (TRANSACTION_ID, CREATOR_PERSON_ID, STATUS, TRANSACTION_REF_ID, TRANSACTION_REF_TABLE, TRANSACTION_TYPE, PROCESS_NAME,TRANSACTION_PRIVILEGE,ITEM_TYPE,ITEM_KEY)
47 VALUES(p_hr_api_txn_id,p_person_id,'W', p_copy_entity_txn_id,'PER_SP_PLAN','NWF','HR_SP_APPROVAL_PRC','PRIVATE','HRSSA',hr_workflow_item_key_s.nextval);
48
49 else
50
51 update HR_API_TRANSACTIONS
52 set item_key=hr_workflow_item_key_s.nextval
53 where item_key is null
54 and TRANSACTION_ID = p_hr_api_txn_id
55 and PROCESS_NAME='HR_SP_APPROVAL_PRC';
56
57 end if;
58
59 commit;
60
61 hr_utility.set_location('Leaving createHRApiTransaction',10);
62
63 exception
64 when others then
65 hr_utility.set_location('issues in createHRApiTransaction',99);
66 raise;
67
68 end createHRApiTransaction;
69
70 --
71 -- ---------------------------------------------------------------------------
72 -- |----------------------< create_sp_txn >--------------------------|
73 -- ---------------------------------------------------------------------------
74 --
75
76 procedure create_sp_txn(p_mode in varchar2,
77 p_business_group_id in number,
78 p_copy_entity_txn_id out nocopy number) is
79 l_sp_txn_cat number;
80 l_ovn number;
81 begin
82 hr_utility.set_location('Creating cet row',10);
83 begin
84
85 select transaction_category_id
86 into l_sp_txn_cat
87 from pqh_transaction_categories
88 where short_name ='SP'
89 and business_group_id is null;
90
91 hr_utility.set_location('txn_cat is'||l_sp_txn_cat,100);
92 exception
93 when others then
94 hr_utility.set_location('txn_cat doesnot exist',100);
95 raise;
96 end;
97 if l_sp_txn_cat is not null then
98 pqh_copy_entity_txns_api.create_COPY_ENTITY_TXN
99 (p_copy_entity_txn_id => p_copy_entity_txn_id
100 ,p_transaction_category_id => l_sp_txn_cat
101 ,p_context_business_group_id => p_business_group_id
102 ,p_context => 'SP'
103 ,p_action_date => trunc(sysdate)
104 ,p_number_of_copies => 1
105 ,p_display_name => p_mode||' - SP - '||to_char(sysdate,'ddmmyyyyhhmiss')
106 ,p_replacement_type_cd => 'NONE'
107 ,p_start_with => 'Succession Plan'
108 ,p_status => p_mode
109 ,p_object_version_number => l_ovn
110 ,p_effective_date => trunc(sysdate));
111 end if;
112 exception
113 when others then
114 hr_utility.set_location('issues in creating CET row',100);
115 raise;
116 end create_sp_txn;
117
118 --
119 -- ---------------------------------------------------------------------------
120 -- |----------------------< create_sp >--------------------------|
121 -- ---------------------------------------------------------------------------
122 --
123
124 procedure create_sp(p_business_group_id in number,
125 p_copy_entity_txn_id out nocopy number) is
126
127 l_cet_id number;
128 l_cer_id number;
129 l_cer_ovn number;
130 begin
131
132 hr_utility.set_location('creating cet row',10);
133
134 create_sp_txn(p_mode => 'CREATE',
135 p_business_group_id => p_business_group_id,
136 p_copy_entity_txn_id => l_cet_id);
137
138 if l_cet_id is not null then
139 hr_utility.set_location('populate out params',10);
140 p_copy_entity_txn_id := l_cet_id;
141 else
142 hr_utility.set_location('cet row is not there',10);
143 end if;
144
145
146 end create_sp;
147
148 --
149 -- ---------------------------------------------------------------------------
150 -- |----------------------< stage_to_sp >--------------------------|
151 -- ---------------------------------------------------------------------------
152 --
153
154 procedure stage_to_sp(p_effective_date in date
155 ,p_copy_entity_txn_id in number
156 ,p_validate in varchar2) IS
157
158 CURSOR csr_spp_stage (p_copy_entity_txn_id IN number) IS
159 SELECT copy_entity_result_id
160 ,information1 "PLAN_ID"
161 ,information11 "PLAN_TYPE"
162 ,information151 "PLAN_NAME"
163 ,information4 "SUCCESSEE_ID"
164 ,information152 "DESCRIPTION"
165 ,information160 "PLAN_OWNER"
166 ,information2 "START_DATE"
167 ,information3 "END_DATE"
168 ,information161 "FILLED_BY"
169 ,information166 "FILLED_ON"
170 ,information8 "STATUS"
171 ,information167 "NEXT_REVIEW_DATE"
172 ,information15 "FILLED_FROM_PLAN"
173 ,information169 "CRITERIA_SET_ID"
174 ,information219 "COMMENTS" -- not there in table
175 ,information221 "BUSINESS_GROUP_ID"
176 ,information265 "OBJECT_VERSION_NUMBER"
177 ,information266 "CREATED_BY"
178 ,information306 "CREATION_DATE"
179 ,information267 "LAST_UPDATED_BY"
180 ,information307 "LAST_UPDATE_DATE"
181 ,Information110 "ATTRIBUTE_CATEGORY"
182 ,Information111 "ATTRIBUTE1"
183 ,Information112 "ATTRIBUTE2"
184 ,Information113 "ATTRIBUTE3"
185 ,Information114 "ATTRIBUTE4"
186 ,Information115 "ATTRIBUTE5"
187 ,Information116 "ATTRIBUTE6"
188 ,Information117 "ATTRIBUTE7"
189 ,Information118 "ATTRIBUTE8"
190 ,Information119 "ATTRIBUTE9"
191 ,Information120 "ATTRIBUTE10"
192 ,Information121 "ATTRIBUTE11"
193 ,Information122 "ATTRIBUTE12"
194 ,Information123 "ATTRIBUTE13"
195 ,Information124 "ATTRIBUTE14"
196 ,Information125 "ATTRIBUTE15"
197 ,Information126 "ATTRIBUTE16"
198 ,Information127 "ATTRIBUTE17"
199 ,Information128 "ATTRIBUTE18"
200 ,Information129 "ATTRIBUTE19"
201 ,Information130 "ATTRIBUTE20"
202 ,dml_operation
203 ,table_alias
204 FROM ben_copy_entity_results
205 WHERE copy_entity_txn_id = p_copy_entity_txn_id
206 AND table_alias = 'SP';
207
208
209 CURSOR csr_sip_stage(p_copy_entity_txn_id in number
210 ,p_parent_entity_result_id in number) IS
211 SELECT copy_entity_result_id
212 ,information160 "SUCCESSOR_IN_PLAN_ID"
213 ,information1 "PLAN_ID"
214 ,information4 "SUCCESSOR_ID"
215 ,information11 "POTENTIAL"
216 ,information12 "PERFORMANCE_RATING"
217 ,information13 "RISK_OF_LOSS"
218 ,information14 "IMPACT_OF_LOSS"
219 ,information15 "KEY_PERSON" -- not there in table
220 ,information300 "READINESS_PCT"
221 ,information161 "RANK"
222 ,information8 "STATUS"
223 ,information2 "EARLIEST_SUCCESSION_DATE"
224 ,information3 "LATEST_SUCCESSION_DATE"
225 ,information15 "ELIGIBLE_FOR_PROMOTION"
226 ,information221 "BUSINESS_GROUP_ID"
227 ,information219 "COMMENTS"
228 ,information110 "ATTRIBUTE_CATEGORY" -- not mapped
229 ,information111 "ATTRIBUTE1"
230 ,information112 "ATTRIBUTE2"
231 ,information113 "ATTRIBUTE3"
232 ,information114 "ATTRIBUTE4"
233 ,information115 "ATTRIBUTE5"
234 ,information116 "ATTRIBUTE6"
235 ,information117 "ATTRIBUTE7"
236 ,information118 "ATTRIBUTE8"
237 ,information119 "ATTRIBUTE9"
238 ,information120 "ATTRIBUTE10"
239 ,information121 "ATTRIBUTE11"
240 ,information122 "ATTRIBUTE12"
241 ,information123 "ATTRIBUTE13"
242 ,information124 "ATTRIBUTE14"
243 ,information125 "ATTRIBUTE15"
244 ,information126 "ATTRIBUTE16"
245 ,information127 "ATTRIBUTE17"
246 ,information128 "ATTRIBUTE18"
247 ,information129 "ATTRIBUTE19"
248 ,information130 "ATTRIBUTE20"
249 ,information166 "PLANNED_DATE"
250 ,information230 "ASSIGNMENT_ID"
251 ,information231 "SUPERVISOR_ID"
252 ,information232 "GRADE_ID"
253 ,information233 "GRADE_LADDER_ID"
254 ,information234 "GRADE_STEP_ID"
255 ,information235 "GRADE_POINT_ID"
256 ,information236 "POSITION_ID"
257 ,information237 "JOB_ID"
258 ,information238 "ASSIGNMENT_STATUS_TYPE_ID"
259 ,information239 "ORGANIZATION_ID"
260 ,information240 "PEOPLE_GROUP_ID"
261 ,information241 "PAY_BASIS_ID"
262 ,information242 "COLLECTIVE_AGREEMENT_ID"
263 ,information243 "LOCATION_ID"
264 ,information244 "CONTRACT_ID" --- ???
265 ,information101 "EMPLOYEE_CATEGORY"
266 ,information102 "BARGAINING_UNIT_CODE"
267 ,information103 "LABOUR_UNION_MEMBER_FLAG"
268 ,information104 "ASSIGNMENT_CATEGORY"
269 ,information265 "OBJECT_VERSION_NUMBER"
270 ,information266 "CREATED_BY"
271 ,information306 "CREATION_DATE"
272 ,information267 "LAST_UPDATED_BY"
273 ,information307 "LAST_UPDATE_DATE"
274 ,dml_operation
275 ,table_alias
276 FROM ben_copy_entity_results
277 WHERE copy_entity_txn_id = p_copy_entity_txn_id
278 AND table_alias = 'SCSR'
279 AND parent_entity_result_id = p_parent_entity_result_id
280 ORDER BY information160;
281 l_proc varchar2(80) := g_package||'stage_to_sp';
282 l_spp_ovn number(10);
283 l_sip_ovn number(10);
284 l_spp_id number(15);
285 l_sip_id number(15);
286 l_effective_date DATE;
287
288
289 begin
290 hr_utility.set_location('Entering: '||l_proc,10);
291 l_effective_date := TRUNC(p_effective_date);
292
293
294 --
295 -- Issue a savepoint
296 --
297 savepoint stage_to_sp;
298
299 FND_MSG_PUB.INITIALIZE;
300
301 FOR i IN csr_spp_stage(p_copy_entity_txn_id)
302 LOOP
303 IF i.dml_operation = 'CREATE' THEN
304 hr_utility.trace('Inserting the new Succession Plan: '||i.plan_name);
305 hr_utility.set_location(l_proc,15);
306 per_sp_plan_api.create_sp_plan(p_validate => false
307 ,p_effective_date => l_effective_date
308 ,p_plan_type => i.plan_type
309 ,p_plan_name => i.plan_name
310 ,p_successee_id => i.successee_id
311 ,p_business_group_id => i.business_group_id
312 ,p_status => 'A'
313 ,p_plan_owner => i.plan_owner
314 ,p_start_date => i.start_date
315 ,p_end_date => i.end_date
316 ,p_description => i.description
317 ,p_filled_from_plan => i.filled_from_plan
318 ,p_filled_by => i.filled_by
319 ,p_filled_on => i.filled_on
320 ,p_next_review_date => i.next_review_date
321 ,p_criteria_set_id => i.criteria_set_id
322 ,p_attribute_category => i.attribute_category
323 ,p_attribute1 => i.attribute1
324 ,p_attribute2 => i.attribute2
325 ,p_attribute3 => i.attribute3
326 ,p_attribute4 => i.attribute4
327 ,p_attribute5 => i.attribute5
328 ,p_attribute6 => i.attribute6
329 ,p_attribute7 => i.attribute7
330 ,p_attribute8 => i.attribute8
331 ,p_attribute9 => i.attribute9
332 ,p_attribute10 => i.attribute10
333 ,p_attribute11 => i.attribute11
334 ,p_attribute12 => i.attribute12
335 ,p_attribute13 => i.attribute13
336 ,p_attribute14 => i.attribute14
337 ,p_attribute15 => i.attribute15
338 ,p_attribute16 => i.attribute16
339 ,p_attribute17 => i.attribute17
340 ,p_attribute18 => i.attribute18
341 ,p_attribute19 => i.attribute19
342 ,p_attribute20 => i.attribute20
343 ,p_plan_id => l_spp_id
344 ,p_object_version_number => l_spp_ovn);
345 hr_utility.set_location(l_proc,20);
346 ELSIF i.dml_operation = 'UPDATE' THEN
347 hr_utility.trace('Updating the new Succession Plan: '||i.plan_name);
348 l_spp_id := i.plan_id;
349 l_spp_ovn := i.object_version_number;
350 hr_utility.set_location(l_proc,25);
351 per_sp_plan_api.update_sp_plan(p_validate => false
352 ,p_effective_date => l_effective_date
353 ,p_plan_type => i.plan_type
354 ,p_plan_name => i.plan_name
355 ,p_successee_id => i.successee_id
356 ,p_business_group_id => i.business_group_id
357 ,p_status => i.status
358 ,p_plan_owner => i.plan_owner
359 ,p_start_date => i.start_date
360 ,p_end_date => i.end_date
361 ,p_description => i.description
362 ,p_filled_from_plan => i.filled_from_plan
363 ,p_filled_by => i.filled_by
364 ,p_filled_on => i.filled_on
365 ,p_next_review_date => i.next_review_date
366 ,p_criteria_set_id => i.criteria_set_id
367 ,p_attribute_category => i.attribute_category
368 ,p_attribute1 => i.attribute1
369 ,p_attribute2 => i.attribute2
370 ,p_attribute3 => i.attribute3
371 ,p_attribute4 => i.attribute4
372 ,p_attribute5 => i.attribute5
373 ,p_attribute6 => i.attribute6
374 ,p_attribute7 => i.attribute7
375 ,p_attribute8 => i.attribute8
376 ,p_attribute9 => i.attribute9
377 ,p_attribute10 => i.attribute10
378 ,p_attribute11 => i.attribute11
379 ,p_attribute12 => i.attribute12
380 ,p_attribute13 => i.attribute13
381 ,p_attribute14 => i.attribute14
382 ,p_attribute15 => i.attribute15
383 ,p_attribute16 => i.attribute16
384 ,p_attribute17 => i.attribute17
385 ,p_attribute18 => i.attribute18
386 ,p_attribute19 => i.attribute19
387 ,p_attribute20 => i.attribute20
388 ,p_plan_id => l_spp_id
389 ,p_object_version_number => l_spp_ovn);
390 hr_utility.set_location(l_proc,30);
391 ELSIF i.dml_operation = 'DELETE' THEN
392 hr_utility.trace('Deleting the new Succession Plan: '||i.plan_name);
393 l_spp_id := i.plan_id;
394 l_spp_ovn := i.object_version_number;
395 hr_utility.set_location(l_proc,35);
396 FOR j IN csr_sip_stage(p_copy_entity_txn_id, i.copy_entity_result_id)
397 LOOP
398 hr_utility.set_location(l_proc,38);
399 IF j.successor_in_plan_id IS NOT NULL AND j.object_version_number IS NOT NULL THEN
400 per_successor_in_plan_api.delete_successor_in_plan(p_validate => false
401 ,p_successor_in_plan_id => j.successor_in_plan_id
402 ,p_object_version_number => j.object_version_number);
403 END IF;
404 hr_utility.set_location(l_proc,39);
405 END LOOP;
406 per_sp_plan_api.delete_sp_plan(p_validate => false
407 ,p_plan_id => l_spp_id
408 ,p_object_version_number => l_spp_ovn);
409 hr_utility.set_location(l_proc,40);
410
411 ELSIF i.dml_operation = 'COPIED' THEN
412 l_spp_id := i.plan_id;
413
414 ELSE
415 hr_utility.trace('Invaid SPP dml_operation:'||i.dml_operation||'-'||l_proc);
416 END IF;
417 IF i.dml_operation IN ('CREATE','UPDATE') THEN
418 FOR j IN csr_sip_stage(p_copy_entity_txn_id, i.copy_entity_result_id)
419 LOOP
420 IF j.dml_operation = 'CREATE' THEN
421 hr_utility.set_location(l_proc,45);
422 per_successor_in_plan_api.create_successor_in_plan
423 (p_validate => false
424 ,p_effective_date => l_effective_date
425 ,p_plan_id => l_spp_id
426 ,p_successor_id => j.successor_id
427 ,p_business_group_id => j.business_group_id
428 ,p_status => j.status
429 ,p_rank => j.rank
430 ,p_potential => j.potential
431 ,p_performance_rating => j.performance_rating
432 ,p_risk_of_loss => j.risk_of_loss
433 ,p_impact_of_loss => j.impact_of_loss
434 ,p_readiness_pct => j.readiness_pct
435 ,p_eligible_for_promotion => j.eligible_for_promotion
436 ,p_earliest_succession_date => j.earliest_succession_date
437 ,p_latest_succession_date => j.latest_succession_date
438 ,p_comments => j.comments
439 ,p_attribute_category => j.attribute_category
440 ,p_attribute1 => j.attribute1
441 ,p_attribute2 => j.attribute2
442 ,p_attribute3 => j.attribute3
443 ,p_attribute4 => j.attribute4
444 ,p_attribute5 => j.attribute5
445 ,p_attribute6 => j.attribute6
446 ,p_attribute7 => j.attribute7
447 ,p_attribute8 => j.attribute8
448 ,p_attribute9 => j.attribute9
449 ,p_attribute10 => j.attribute10
450 ,p_attribute11 => j.attribute11
451 ,p_attribute12 => j.attribute12
452 ,p_attribute13 => j.attribute13
453 ,p_attribute14 => j.attribute14
454 ,p_attribute15 => j.attribute15
455 ,p_attribute16 => j.attribute16
456 ,p_attribute17 => j.attribute17
457 ,p_attribute18 => j.attribute18
458 ,p_attribute19 => j.attribute19
459 ,p_attribute20 => j.attribute20
460 ,p_planned_date => j.planned_date
461 ,p_assignment_id => j.assignment_id
462 ,p_supervisor_id => j.supervisor_id
463 ,p_grade_id => j.grade_id
464 ,p_grade_ladder_id => j.grade_ladder_id
465 ,p_grade_step_id => j.grade_step_id
466 ,p_grade_point_id => j.grade_point_id
467 ,p_position_id => j.position_id
468 ,p_job_id => j.job_id
469 ,p_assignment_status_type_id => j.assignment_status_type_id
470 ,p_organization_id => j.organization_id
471 ,p_people_group_id => j.people_group_id
472 ,p_pay_basis_id => j.pay_basis_id
473 ,p_employee_category => j.employee_category
474 ,p_bargaining_unit_code => j.bargaining_unit_code
475 ,p_labour_union_member_flag => j.labour_union_member_flag
476 ,p_collective_agreement_id => j.collective_agreement_id
477 ,p_assignment_category => j.assignment_category
478 ,p_location_id => j.location_id
479 ,p_contract_id => j.contract_id
480 ,p_successor_in_plan_id => l_sip_id
481 ,p_object_version_number => l_sip_ovn);
482 hr_utility.set_location(l_proc,50);
483
484
485 ELSIF j.dml_operation = 'UPDATE' THEN
486 hr_utility.set_location(l_proc,55);
487 l_sip_id := j.successor_in_plan_id;
488 l_sip_ovn := j.object_version_number;
489 per_successor_in_plan_api.update_successor_in_plan
490 (p_validate => false
491 ,p_effective_date => l_effective_date
492 ,p_plan_id => j.plan_id
493 ,p_successor_id => j.successor_id
494 ,p_business_group_id => j.business_group_id
495 ,p_status => j.status
496 ,p_rank => j.rank
497 ,p_potential => j.potential
498 ,p_performance_rating => j.performance_rating
499 ,p_risk_of_loss => j.risk_of_loss
500 ,p_impact_of_loss => j.impact_of_loss
501 ,p_readiness_pct => j.readiness_pct
502 ,p_eligible_for_promotion => j.eligible_for_promotion
503 ,p_earliest_succession_date => j.earliest_succession_date
504 ,p_latest_succession_date => j.latest_succession_date
505 ,p_comments => j.comments
506 ,p_attribute_category => j.attribute_category
507 ,p_attribute1 => j.attribute1
508 ,p_attribute2 => j.attribute2
509 ,p_attribute3 => j.attribute3
510 ,p_attribute4 => j.attribute4
511 ,p_attribute5 => j.attribute5
512 ,p_attribute6 => j.attribute6
513 ,p_attribute7 => j.attribute7
514 ,p_attribute8 => j.attribute8
515 ,p_attribute9 => j.attribute9
516 ,p_attribute10 => j.attribute10
517 ,p_attribute11 => j.attribute11
518 ,p_attribute12 => j.attribute12
519 ,p_attribute13 => j.attribute13
520 ,p_attribute14 => j.attribute14
521 ,p_attribute15 => j.attribute15
522 ,p_attribute16 => j.attribute16
523 ,p_attribute17 => j.attribute17
524 ,p_attribute18 => j.attribute18
525 ,p_attribute19 => j.attribute19
526 ,p_attribute20 => j.attribute20
527 ,p_planned_date => j.planned_date
528 ,p_assignment_id => j.assignment_id
529 ,p_supervisor_id => j.supervisor_id
530 ,p_grade_id => j.grade_id
531 ,p_grade_ladder_id => j.grade_ladder_id
532 ,p_grade_step_id => j.grade_step_id
533 ,p_grade_point_id => j.grade_point_id
534 ,p_position_id => j.position_id
535 ,p_job_id => j.job_id
536 ,p_assignment_status_type_id => j.assignment_status_type_id
537 ,p_organization_id => j.organization_id
538 ,p_people_group_id => j.people_group_id
539 ,p_pay_basis_id => j.pay_basis_id
540 ,p_employee_category => j.employee_category
541 ,p_bargaining_unit_code => j.bargaining_unit_code
542 ,p_labour_union_member_flag => j.labour_union_member_flag
543 ,p_collective_agreement_id => j.collective_agreement_id
544 ,p_assignment_category => j.assignment_category
545 ,p_location_id => j.location_id
546 ,p_contract_id => j.contract_id
547 ,p_successor_in_plan_id => l_sip_id
548 ,p_object_version_number => l_sip_ovn);
549 hr_utility.set_location(l_proc,50);
550
551 ELSIF j.dml_operation = 'DELETE' THEN
552 per_successor_in_plan_api.delete_successor_in_plan(p_validate => false
553 ,p_successor_in_plan_id => j.successor_in_plan_id
554 ,p_object_version_number => j.object_version_number);
555 ELSIF j.dml_operation = 'COPIED' THEN
556 l_sip_id := j.successor_in_plan_id;
557 ELSE
558 hr_utility.trace('Invaid SIP dml_operation:'||j.dml_operation||'-'||l_proc);
559 END IF;
560
561 if j.dml_operation <> 'DELETE' then
562 --
563 -- CHANGE THE PK1_VALUE FOR ATTACHMENTS
564 --
565
566 UPDATE fnd_attached_documents
567 SET PK1_VALUE=l_sip_id,
568 ENTITY_NAME='PER_SP_SUCCESSOR_IN_PLAN'
569 WHERE ENTITY_NAME='BEN_COPY_ENTITY_RESULTS_SCSR'
570 AND PK1_VALUE=j.copy_entity_result_id;
571 end if;
572
573 END LOOP;
574 END IF;
575
576 if i.dml_operation <> 'DELETE' then
577 --
578 -- CHANGE THE PK1_VALUE FOR ATTACHMENTS
579 --
580
581 UPDATE fnd_attached_documents
582 SET PK1_VALUE=l_spp_id,
583 ENTITY_NAME='PER_SP_PLAN'
584 WHERE ENTITY_NAME='BEN_COPY_ENTITY_RESULTS_PL'
585 AND PK1_VALUE=i.copy_entity_result_id;
586 end if;
587 END LOOP;
588
589
590 --
591 -- When in validation only mode raise the Validate_Enabled exception
592 --
593 if p_validate = 'Y' then
594 raise hr_api.validate_enabled;
595 end if;
596
597 exception
598 when hr_api.validate_enabled then
599 --
600 -- As the Validate_Enabled exception has been raised
601 -- we must rollback to the savepoint
602 --
603 rollback to stage_to_sp;
604
605 when others then
606 --
607 -- A validation or unexpected error has occured
608 rollback to stage_to_sp;
609 raise;
610 hr_utility.set_location('Leaving: '||l_proc,80);
611 end stage_to_sp;
612
613 --
614 -- ---------------------------------------------------------------------------
615 -- |----------------------< sp_to_stage >--------------------------|
616 -- ---------------------------------------------------------------------------
617 --
618 procedure sp_to_stage(p_effective_date in date
619 ,p_plan_id in number
620 ,p_business_group_id in number
621 ,p_mode in varchar2
622 ,p_copy_entity_txn_id out nocopy number) IS
623
624 l_proc varchar2(80) := g_package||'sp_to_stage';
625 l_copy_entity_txn_id NUMBER;
626 l_business_group_id NUMBER;
627 l_pl_cer_id NUMBER;
628
629
630 CURSOR csr_sp_rec(p_plan_id IN number) IS
631 SELECT plan_id
632 ,plan_type
633 ,plan_name
634 ,successee_id
635 ,description
636 ,plan_owner
637 ,start_date
638 ,end_date
639 ,filled_by
640 ,filled_on
641 ,status
642 ,next_review_date
643 ,filled_from_plan
644 ,NULL comments
645 ,business_group_id
646 ,object_version_number
647 ,criteria_set_id
648 ,created_by
649 ,creation_date
650 ,last_updated_by
651 ,last_update_date
652 ,attribute_category
653 ,attribute1
654 ,attribute2
655 ,attribute3
656 ,attribute4
657 ,attribute5
658 ,attribute6
659 ,attribute7
660 ,attribute8
661 ,attribute9
662 ,attribute10
663 ,attribute11
664 ,attribute12
665 ,attribute13
666 ,attribute14
667 ,attribute15
668 ,attribute16
669 ,attribute17
670 ,attribute18
671 ,attribute19
672 ,attribute20
673 FROM per_sp_plan
674 WHERE plan_id = p_plan_id;
675
676 CURSOR csr_successors(p_plan_id IN number) IS
677 SELECT plan_id
678 ,successor_id
679 ,business_group_id
680 ,status
681 ,rank
682 ,potential
683 ,performance_rating
684 ,risk_of_loss
685 ,impact_of_loss
686 ,readiness_pct
687 ,eligible_for_promotion
688 ,earliest_succession_date
689 ,latest_succession_date
690 ,comments
691 ,attribute_category
692 ,attribute1
693 ,attribute2
694 ,attribute3
695 ,attribute4
696 ,attribute5
697 ,attribute6
698 ,attribute7
699 ,attribute8
700 ,attribute9
701 ,attribute10
702 ,attribute11
703 ,attribute12
704 ,attribute13
705 ,attribute14
706 ,attribute15
707 ,attribute16
708 ,attribute17
709 ,attribute18
710 ,attribute19
711 ,attribute20
712 ,planned_date
713 ,assignment_id
714 ,supervisor_id
715 ,grade_id
716 ,grade_ladder_id
717 ,grade_step_id
718 ,grade_point_id
719 ,position_id
720 ,job_id
721 ,assignment_status_type_id
722 ,organization_id
723 ,people_group_id
724 ,pay_basis_id
725 ,employee_category
726 ,bargaining_unit_code
727 ,labour_union_member_flag
728 ,collective_agreement_id
729 ,assignment_category
730 ,location_id
731 ,contract_id
732 ,successor_in_plan_id
733 ,object_version_number
734 ,creation_date
735 ,created_by
736 ,last_updated_by
737 ,last_update_date
738 FROM per_sp_successor_in_plan
739 WHERE plan_id = p_plan_id
740 ORDER BY rank;
741
742
743 begin
744
745 hr_utility.set_location('Entering:'||l_proc,10);
746 hr_utility.trace('p_plan_id : '||p_plan_id);
747 hr_utility.trace('p_business_group_id: '||p_business_group_id);
748 hr_utility.trace('p_mode:'||p_mode);
749
750 l_business_group_id :=p_business_group_id;
751
752 IF p_business_group_id IS NULL
753 THEN
754 SELECT business_group_id INTO l_business_group_id
755 FROM PER_SP_PLAN WHERE PLAN_ID=p_plan_id;
756 END IF;
757
758 create_sp(p_business_group_id => l_business_group_id,
759 p_copy_entity_txn_id => l_copy_entity_txn_id);
760
761 hr_utility.trace('Copy Entity Txn Id: '||l_copy_entity_txn_id);
762
763
764
765 FOR i IN csr_sp_rec(p_plan_id)
766 LOOP
767
768
769
770 INSERT INTO ben_copy_entity_results (copy_entity_result_id
771 ,information1
772 ,information11
773 ,information151
774 ,information4
775 ,information152
776 ,information160
777 ,information2
778 ,information3
779 ,information161
780 ,information166
781 ,information8
782 ,information167
783 ,information15
784 ,information169
785 ,information219
786 ,information221
787 ,information265
788 ,information266
789 ,information306
790 ,information267
791 ,information307
792 ,information110
793 ,information111
794 ,information112
795 ,information113
796 ,information114
797 ,information115
798 ,information116
799 ,information117
800 ,information118
801 ,information119
802 ,information120
803 ,information121
804 ,information122
805 ,information123
806 ,information124
807 ,information125
808 ,information126
809 ,information127
810 ,information128
811 ,information129
812 ,information130
813 ,dml_operation
814 ,table_alias
815 ,copy_entity_txn_id
816 ,RESULT_TYPE_CD
817 ,OBJECT_VERSION_NUMBER
818 ,STATUS)
819 VALUES
820 (ben_copy_entity_results_s.nextval
821 ,i.plan_id
822 ,i.plan_type
823 ,i.plan_name
824 ,i.successee_id
825 ,i.description
826 ,i.plan_owner
827 ,i.start_date
828 ,i.end_date
829 ,i.filled_by
830 ,i.filled_on
831 ,i.status
832 ,i.next_review_date
833 ,i.filled_from_plan
834 ,i.criteria_set_id
835 ,null --- comments
836 ,i.business_group_id
837 ,i.object_version_number
838 ,i.created_by
839 ,i.creation_date
840 ,i.last_updated_by
841 ,i.last_update_date
842 ,i.attribute_category
843 ,i.attribute1
844 ,i.attribute2
845 ,i.attribute3
846 ,i.attribute4
847 ,i.attribute5
848 ,i.attribute6
849 ,i.attribute7
850 ,i.attribute8
851 ,i.attribute9
852 ,i.attribute10
853 ,i.attribute11
854 ,i.attribute12
855 ,i.attribute13
856 ,i.attribute14
857 ,i.attribute15
858 ,i.attribute16
859 ,i.attribute17
860 ,i.attribute18
861 ,i.attribute19
862 ,i.attribute20
863 ,'COPIED'
864 ,'SP'
865 ,l_copy_entity_txn_id
866 ,'DISPLAY'
867 ,1
868 ,'W');
869
870 select ben_copy_entity_results_s.currval into l_pl_cer_id from dual;
871 --- Now insert the SPP records
872 FOR j IN csr_successors(p_plan_id)
873 LOOP
874 INSERT INTO ben_copy_entity_results
875 ( copy_entity_result_id
876 ,information160
877 ,information1
878 ,information4
879 ,information11
880 ,information12
881 ,information13
882 ,information14
883 -- ,information15 -- key person not there in table
884 ,information300
885 ,information161
886 ,information8
887 ,information2
888 ,information3
889 ,information15
890 ,information221
891 ,information219
892 ,information110
893 ,information111
894 ,information112
895 ,information113
896 ,information114
897 ,information115
898 ,information116
899 ,information117
900 ,information118
901 ,information119
902 ,information120
903 ,information121
904 ,information122
905 ,information123
906 ,information124
907 ,information125
908 ,information126
909 ,information127
910 ,information128
911 ,information129
912 ,information130
913 ,information166
914 ,information230
915 ,information231
916 ,information232
917 ,information233
918 ,information234
919 ,information235
920 ,information236
921 ,information237
922 ,information238
923 ,information239
924 ,information240
925 ,information241
926 ,information242
927 ,information243
928 ,information244
929 ,information101
930 ,information102
931 ,information103
932 ,information104
933 ,information265
934 ,information266
935 ,information306
936 ,information267
937 ,information307
938 ,dml_operation
939 ,table_alias
940 ,copy_entity_txn_id
941 ,RESULT_TYPE_CD
942 ,OBJECT_VERSION_NUMBER
943 ,PARENT_ENTITY_RESULT_ID
944 )
945 VALUES
946 ( ben_copy_entity_results_s.nextval
947 ,j.successor_in_plan_id
948 ,j.plan_id
949 ,j.successor_id
950 ,j.potential
951 ,j.performance_rating
952 ,j.risk_of_loss
953 ,j.impact_of_loss
954 ,j.readiness_pct
955 ,j.rank
956 ,j.status
957 ,j.earliest_succession_date
958 ,j.latest_succession_date
959 ,j.eligible_for_promotion
960 ,j.business_group_id
961 ,j.comments
962 ,j.attribute_category
963 ,j.attribute1
964 ,j.attribute2
965 ,j.attribute3
966 ,j.attribute4
967 ,j.attribute5
968 ,j.attribute6
969 ,j.attribute7
970 ,j.attribute8
971 ,j.attribute9
972 ,j.attribute10
973 ,j.attribute11
974 ,j.attribute12
975 ,j.attribute13
976 ,j.attribute14
977 ,j.attribute15
978 ,j.attribute16
979 ,j.attribute17
980 ,j.attribute18
981 ,j.attribute19
982 ,j.attribute20
983 ,j.planned_date
984 ,j.assignment_id
985 ,j.supervisor_id
986 ,j.grade_id
987 ,j.grade_ladder_id
988 ,j.grade_step_id
989 ,j.grade_point_id
990 ,j.position_id
991 ,j.job_id
992 ,j.assignment_status_type_id
993 ,j.organization_id
994 ,j.people_group_id
995 ,j.pay_basis_id
996 ,j.collective_agreement_id
997 ,j.location_id
998 ,j.contract_id
999 ,j.employee_category
1000 ,j.bargaining_unit_code
1001 ,j.labour_union_member_flag
1002 ,j.assignment_category
1003 ,j.object_version_number
1004 ,j.created_by
1005 ,j.creation_date
1006 ,j.last_updated_by
1007 ,j.last_update_date
1008 ,'COPIED'
1009 ,'SCSR'
1010 ,l_copy_entity_txn_id
1011 ,'DISPLAY'
1012 ,1
1013 ,l_pl_cer_id);
1014
1015
1016 END LOOP;
1017 END LOOP;
1018
1019 p_copy_entity_txn_id := l_copy_entity_txn_id;
1020 hr_utility.set_location('Leaving: '||l_proc,30);
1021 EXCEPTION
1022 WHEN Others THEN
1023 p_copy_entity_txn_id := NULL;
1024 hr_utility.trace('error in sp_to_stage');
1025 hr_utility.trace(SqlErrm);
1026 hr_utility.set_location('Leaving: '||l_proc,40);
1027 Raise ;
1028 end sp_to_stage;
1029
1030
1031 --
1032 -- ---------------------------------------------------------------------------
1033 -- |----------------------< reject_transaction >--------------------------|
1034 -- ---------------------------------------------------------------------------
1035 --
1036
1037 PROCEDURE reject_transaction( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
1038 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
1039 actid IN NUMBER,
1040 funcmode IN VARCHAR2,
1041 resultout OUT nocopy VARCHAR2 ) IS
1042 l_copy_entity_txn_id NUMBER;
1043 response varchar2(4000);
1044 l_hrApiTxnId number;
1045 BEGIN
1046
1047 l_hrApiTxnId := wf_engine.GetItemAttrNumber(itemtype=> itemtype,itemkey=> itemkey,aname => 'TRANSACTION_ID' );
1048
1049 select transaction_ref_id into l_copy_entity_txn_id
1050 from hr_api_transactions
1051 where transaction_id=l_hrApiTxnId
1052 and process_name='HR_SP_APPROVAL_PRC';
1053
1054 response := wf_engine.getItemAttrText(itemtype=> itemtype,itemkey=> itemkey,aname => 'WF_NOTE' );
1055 WF_ENGINE.setitemattrtext(itemtype, itemkey, 'APPROVAL_COMMENT', response);
1056
1057 -- reset the status to work in progress
1058 update ben_copy_entity_results
1059 set status='W', information220=response
1060 where copy_entity_txn_id=l_copy_entity_txn_id
1061 and table_alias='SP'
1062 and status='Y';
1063
1064 update hr_api_transactions
1065 set item_key=null
1066 where transaction_id=l_hrApiTxnId
1067 and process_name='HR_SP_APPROVAL_PRC';
1068
1069 resultout:='COMPLETE:SUCCESS';
1070
1071 hr_utility.set_location('Leaving commit_transaction', 90);
1072
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 resultout:='COMPLETE:FAILURE';
1076 RAISE;
1077
1078 END;
1079
1080 --
1081 -- ---------------------------------------------------------------------------
1082 -- |----------------------< commit_transaction >--------------------------|
1083 -- ---------------------------------------------------------------------------
1084 --
1085
1086 PROCEDURE commit_transaction ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
1087 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
1088 actid IN NUMBER,
1089 funcmode IN VARCHAR2,
1090 resultout OUT nocopy VARCHAR2 )
1091 is
1092
1093 l_copy_entity_txn_id NUMBER;
1094 l_hrApiTxnId number;
1095 response varchar2(4000);
1096 begin
1097
1098 hr_utility.set_location('ENTERING commit_transaction', 10);
1099
1100 l_hrApiTxnId := wf_engine.GetItemAttrNumber(itemtype=> itemtype,itemkey=> itemkey,aname => 'TRANSACTION_ID' );
1101
1102 response := wf_engine.getItemAttrText(itemtype=> itemtype,itemkey=> itemkey,aname => 'WF_NOTE' );
1103 WF_ENGINE.setitemattrtext(itemtype, itemkey, 'APPROVAL_COMMENT', response);
1104
1105 select transaction_ref_id into l_copy_entity_txn_id
1106 from hr_api_transactions
1107 where transaction_id=l_hrApiTxnId
1108 and process_name='HR_SP_APPROVAL_PRC';
1109
1110 hr_utility.set_location('l_copy_entity_txn_id:'||l_copy_entity_txn_id, 40);
1111
1112 commit_txn(l_copy_entity_txn_id);
1113
1114 hr_utility.set_location('delete from hr_api_transactions..', 50);
1115
1116 delete from hr_api_transactions
1117 where item_type=itemtype and item_key=itemkey;
1118
1119 resultout:='COMPLETE:SUCCESS';
1120
1121 hr_utility.set_location('Leaving commit_transaction', 90);
1122
1123 EXCEPTION
1124 WHEN OTHERS THEN
1125 resultout:='COMPLETE:FAILURE';
1126 RAISE;
1127
1128 end commit_transaction;
1129
1130 --
1131 -- ---------------------------------------------------------------------------
1132 -- |----------------------< refresh_txn >--------------------------|
1133 -- ---------------------------------------------------------------------------
1134 --
1135 PROCEDURE refresh_txn ( p_copy_entity_txn_id in Number)
1136 is
1137
1138
1139 begin
1140
1141 hr_utility.set_location('ENTERING refresh_txn', 10);
1142
1143
1144 --
1145 -- Purge data from transaction tables.
1146 --
1147
1148 Delete from BEN_COPY_ENTITY_RESULTS
1149 where copy_entity_txn_id = p_copy_entity_txn_id
1150 and table_alias in ('SP','SCSR');
1151
1152 delete from pqh_copy_entity_txns
1153 where copy_entity_txn_id = p_copy_entity_txn_id
1154 and context='SP';
1155
1156 delete from hr_api_transactions
1157 where transaction_ref_id = p_copy_entity_txn_id
1158 and process_name='HR_SP_APPROVAL_PRC';
1159
1160
1161 hr_utility.set_location('leaving refresh_txn', 90);
1162 end refresh_txn;
1163
1164 --
1165 -- ---------------------------------------------------------------------------
1166 -- |----------------------< commit >--------------------------|
1167 -- ---------------------------------------------------------------------------
1168 --
1169 PROCEDURE commit_txn ( p_copy_entity_txn_id in Number)
1170 is
1171
1172 plan_id number;
1173 begin
1174
1175 hr_utility.set_location('ENTERING commit_txn', 10);
1176
1177 stage_to_sp(trunc(sysdate), p_copy_entity_txn_id,'N');
1178
1179 hr_utility.set_location('getting the plan Id....', 50);
1180
1181 select distinct information1 into plan_id
1182 from ben_copy_entity_results
1183 where copy_entity_txn_id = p_copy_entity_txn_id
1184 and table_alias='SP';
1185
1186 hr_utility.set_location('deleting from stage..PLAN:'||plan_id, 50);
1187
1188 if plan_id is not null
1189 then
1190 --
1191 -- Purge all data from transaction tables with the above plan_id.
1192 --
1193 Delete from BEN_COPY_ENTITY_RESULTS
1194 where information1 = plan_id
1195 and table_alias in ('SP','SCSR');
1196 else
1197
1198 --
1199 -- Purge data from transaction tables with the given txn id
1200 --
1201 Delete from BEN_COPY_ENTITY_RESULTS
1202 where copy_entity_txn_id = p_copy_entity_txn_id
1203 and table_alias in ('SP','SCSR');
1204 end if;
1205
1206 --
1207 --
1208 delete from pqh_copy_entity_txns
1209 where copy_entity_txn_id = p_copy_entity_txn_id;
1210
1211 delete from hr_api_transactions
1212 where transaction_ref_id = p_copy_entity_txn_id
1213 and process_name='HR_SP_APPROVAL_PRC';
1214
1215
1216
1217 hr_utility.set_location('leaving commit_txn', 90);
1218 end commit_txn;
1219
1220 --
1221 -- ---------------------------------------------------------------------------
1222 -- |----------------------< get_approval_req >--------------------------|
1223 -- ---------------------------------------------------------------------------
1224 --
1225 PROCEDURE get_approval_req ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
1226 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
1227 actid IN NUMBER,
1228 funcmode IN VARCHAR2,
1229 resultout OUT nocopy VARCHAR2 )
1230 IS
1231
1232 l_item_value varchar2(200);
1233 l_ntf_url varchar2(4000);
1234
1235
1236
1237 BEGIN
1238 hr_utility.set_location('ENTERING get_approval_req', 10);
1239 IF (funcmode='RUN') THEN
1240
1241
1242
1243
1244 l_item_value := wf_engine.getItemAttrText(itemtype => itemtype
1245 ,itemkey => itemkey
1246 , aname => 'HR_RUNTIME_APPROVAL_REQ_FLAG');
1247
1248
1249
1250 if l_item_value = 'NO' then
1251
1252 resultout:='COMPLETE:N';
1253
1254
1255 else
1256
1257 resultout:='COMPLETE:Y';
1258
1259
1260 end if;
1261 hr_utility.trace('l_resultout' || resultout);
1262
1263 RETURN;
1264 END IF; --RUN
1265
1266 IF (funcmode='CANCEL') THEN
1267 resultout:='COMPLETE';
1268 RETURN;
1269 END IF;
1270 Exception
1271
1272 when others then
1273 hr_utility.set_location('ENTERING exception get_approval_req', 10);
1274
1275
1276
1277 end get_approval_req;
1278
1279
1280 --
1281 -- ---------------------------------------------------------------------------
1282 -- |----------------------< launch_wf >--------------------------|
1283 -- ---------------------------------------------------------------------------
1284 --
1285
1286 PROCEDURE launch_wf(p_process in wf_process_activities.process_name%type,
1287 p_item_type in wf_items.item_type%type,
1288 p_person_id in number,
1289 p_copy_entity_txn_id in number,
1290 p_approval_reqd in varchar2)
1291
1292 is
1293
1294 l_proc varchar2(72) := 'launch_wf';
1295 l_process wf_activities.name%type := upper(p_process);
1296 l_item_type wf_items.item_type%type := upper(p_item_type);
1297 l_item_key wf_items.item_key%type;
1298 l_return_status varchar2(1000);
1299 l_process_display_name varchar2(240);
1300 l_role_name wf_roles.name%type;
1301 l_role_displayname wf_roles.display_name%type;
1302 l_plan_name per_sp_plan.plan_name%type;
1303 l_hrApiTxnId number;
1304
1305 lv_status varchar2(8);
1306 l_status varchar2(8);
1307 lv_result varchar2(30);
1308 lv_errorActid wf_item_activity_statuses.process_activity%type;
1309 lv_errname VARCHAR2(4000);
1310 l_error_message varchar2(240);
1311 l_errstack varchar2(4000);
1312
1313 Cursor get_display_name is
1314 SELECT wrpv.display_name displayName
1315 FROM wf_runnable_processes_v wrpv
1316 WHERE wrpv.item_type = p_item_type
1317 AND wrpv.process_name = p_process;
1318
1319 cursor get_plan_name is
1320 select Information151 plan_name
1321 from ben_copy_entity_results
1322 where copy_entity_txn_id=p_copy_entity_txn_id
1323 and table_alias='SP';
1324
1325 begin
1326
1327 hr_utility.set_location('Entering:'||l_proc, 10);
1328
1329 select transaction_id, item_key into l_hrApiTxnId, l_item_key
1330 from hr_api_transactions
1331 where TRANSACTION_REF_ID=p_copy_entity_txn_id
1332 and TRANSACTION_REF_TABLE='PER_SP_PLAN'
1333 and PROCESS_NAME='HR_SP_APPROVAL_PRC';
1334
1335 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
1336
1337 -- check the process status before setting
1338 -- other mandatory attributes
1339 -- check the state of the workflow
1340 -- we need to check if the flow is in error state or not
1341 wf_engine.iteminfo(l_item_type,
1342 l_item_key,
1343 l_status,
1344 lv_result,
1345 lv_errorActid,
1346 lv_errname,
1347 l_error_message,
1348 l_errstack);
1349
1350 if(lv_status = 'ERROR') then
1351 raise g_wf_error_state;
1352 end if;
1353
1354 OPEN get_display_name;
1355 FETCH get_display_name INTO l_process_display_name;
1356 CLOSE get_display_name;
1357
1358 open get_plan_name;
1359 fetch get_plan_name into l_plan_name;
1360 close get_plan_name;
1361
1362 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'CREATOR_PERSON_ID', p_person_id);
1363 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'CURRENT_PERSON_ID', p_person_id);
1364 HR_APPROVAL_WF.initialize_item_attributes(p_item_type,l_item_key,null,wf_engine.eng_run,lv_result);
1365
1366 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'PROCESS_DISPLAY_NAME', l_process_display_name);
1367 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'PROCESS_NAME',l_process );
1368 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_AME_TRAN_TYPE_ATTR','SSHRMS');
1369 WF_ENGINE.setitemattrNumber(l_item_type, l_item_key, 'HR_AME_APP_ID_ATTR', 800);
1370 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_RUNTIME_APPROVAL_REQ_FLAG', p_approval_reqd);
1371 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'TRANSACTION_ID', l_hrApiTxnId);
1372 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'PLAN_NAME', l_plan_name);
1373
1374
1375
1376 --
1377 -- ---------------------------------
1378 -- Get the Role for the Owner
1379 -- ---------------------------------
1380 wf_directory.getRoleName
1381 (p_orig_system => 'PER'
1382 ,p_orig_system_id => p_person_id
1383 ,p_name => l_role_name
1384 ,p_display_name => l_role_displayname);
1385
1386 IF l_role_name = '' OR l_role_name IS NULL THEN
1387 RAISE g_invalid_responsibility;
1388 END IF;
1389 -- ---------------------------------------------------
1390 -- Set the Item Owner
1391 -- ---------------------------------------------------
1392 wf_engine.setItemOwner
1393 (itemtype => l_item_type
1394 ,itemkey => l_item_key
1395 ,owner => l_role_name);
1396 hr_utility.set_location(' l_role_name'||l_role_name,10);
1397
1398
1399
1400 -- set 'CURRENT_EFFECTIVE_DATE'
1401 hr_utility.set_location(' lr_transaction_rec.item_type'||l_item_type,50);
1402 hr_approval_ss.create_item_attrib_if_notexist(itemtype => l_item_type
1403 ,itemkey => l_item_key
1404 ,aname => 'CURRENT_EFFECTIVE_DATE'
1405 ,text_value=>null
1406 ,number_value=>null,
1407 date_value=>trunc(sysdate)
1408 );
1409 -- set HR_OAF_EDIT_URL_ATTR
1410
1411 hr_approval_ss.create_item_attrib_if_notexist(itemtype => l_item_type
1412 ,itemkey => l_item_key
1413 ,aname => 'HR_OAF_EDIT_URL_ATTR'
1414 ,text_value=>null
1415 ,number_value=>null,
1416 date_value=>null
1417 );
1418
1419 -- set HR_OAF_NAVIGATION_ATTR
1420 hr_approval_ss.create_item_attrib_if_notexist(itemtype => l_item_type
1421 ,itemkey => l_item_key
1422 ,aname => 'HR_OAF_NAVIGATION_ATTR'
1423 ,text_value=>'N'
1424 ,number_value=>null,
1425 date_value=>null
1426 );
1427
1428
1429
1430 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
1431
1432 hr_utility.set_location('started process:'||l_proc, 20);
1433
1434
1435 -- check the wf status before returning the status back to caller
1436 -- we need to check if the flow is in error state or not
1437 wf_engine.iteminfo(l_item_type,
1438 l_item_key,
1439 l_status,
1440 lv_result,
1441 lv_errorActid,
1442 lv_errname,
1443 l_error_message,
1444 l_errstack);
1445
1446 if(lv_status = 'ERROR') then
1447 raise g_wf_error_state;
1448 end if;
1449
1450
1451
1452 update ben_copy_entity_results
1453 set status='Y'
1454 where copy_entity_txn_id=p_copy_entity_txn_id
1455 and table_alias='SP'
1456 and status='W';
1457
1458 hr_utility.set_location('leaving:'||l_proc, 90);
1459 EXCEPTION
1460 WHEN OTHERS THEN
1461
1462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1463
1464 end launch_wf;
1465
1466
1467 end PER_SP_STAGE;