[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_PERSON_TITLE_UPDATE
Source
1 PACKAGE BODY pay_gb_person_title_update AS
2 /* $Header: pygbupdt.pkb 120.0.12010000.1 2008/11/07 12:48:05 smeduri noship $ */
3 --
4 -- Global variables.
5 --
6 g_package VARCHAR2(31) := 'pay_gb_person_title_update.';
7 --
8 -- -------------------------------------------------------------------------------------------
9 -- The main update.
10 -- -------------------------------------------------------------------------------------------
11 --
12 PROCEDURE run(errbuf OUT NOCOPY VARCHAR2
13 ,retcode OUT NOCOPY NUMBER
14 ,p_bg_id IN NUMBER
15 ,p_title IN VARCHAR2
16 ) IS
17
18 -- To get business_group name
19 CURSOR csr_business_group is
20 SELECT name
21 FROM per_business_groups
22 WHERE business_group_id =p_bg_id;
23
24 -- To get person details
25 CURSOR csr_person_det is
26 SELECT ppf.person_id person_id,
27 ppf.title title,
28 ppf.national_identifier national_identifier,
29 ppf.employee_number employee_number,
30 ppf.object_version_number object_version_number,
31 ppf.effective_start_date effective_start_date,
32 ppf.effective_end_date effective_end_date,
33 ppf.full_name full_name
34 FROM per_all_people_f ppf
35 WHERE ppf.business_group_id = p_bg_id
36 and ppf.title='HU_PROF'
37 order by ppf.person_id;
38
39 -- Local variables.
40 l_new_title varchar2(15);
41 l_business_group_name per_business_groups.name%type;
42 l_no_data_found varchar2(1);
43 l_full_name varchar2(100);
44 l_comment_id number;
45 l_person_id per_people_f.person_id%type;
46 l_ovn number;
47 l_emp_no VARCHAR2(20);
48 l_eff_sdt DATE;
49 l_eff_edt DATE;
50 l_name_combination_warning BOOLEAN := FALSE;
51 l_assign_payroll_warning BOOLEAN := FALSE;
52 l_orig_hire_warning BOOLEAN := FALSE;
53
54
55 BEGIN
56 --
57 l_no_data_found := 'Y';
58 -- hr_utility.trace_on(null,'title');
59 hr_utility.set_location('Entering: ' || g_package, 10);
60 OPEN csr_business_group;
61 FETCH csr_business_group into l_business_group_name;
62 CLOSE csr_business_group;
63
64 hr_utility.set_location('Leaving: ' || g_package, 20);
65 fnd_file.put_line(FND_FILE.OUTPUT,'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------');
66 fnd_file.put_line(FND_FILE.OUTPUT,' Title Changes For Employees in : '||rpad(l_business_group_name,30));
67 fnd_file.put_line(FND_FILE.OUTPUT,' ');
68 fnd_file.put_line(FND_FILE.OUTPUT,' Title '||rpad(p_title,30)||'updated for the following records');
69 fnd_file.put_line(FND_FILE.OUTPUT,'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------');
70 fnd_file.put_line(FND_FILE.OUTPUT,' ');
71 fnd_file.put_line(FND_FILE.OUTPUT, rpad('Person ID',20)||' '||rpad('National Identifier',20)||' '||rpad('Effective Start Date',20)||' '||
72 rpad('Effective End Date ',20)||' '||rpad('Initial Employee Name',75)||' '||rpad('Final Employee name',75));
73 fnd_file.put_line(FND_FILE.OUTPUT,'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------');
74
75
76 for v_csr_person in csr_person_det
77 loop
78 if l_no_data_found = 'Y' then
79 l_no_data_found := 'N';
80 end if;
81 l_person_id :=v_csr_person.person_id;
82 l_ovn := v_csr_person.object_version_number;
83 l_emp_no :=v_csr_person.employee_number;
84
85 HR_PERSON_API.update_gb_person(
86 p_effective_date => v_csr_person.effective_start_date
87 ,p_datetrack_update_mode => 'CORRECTION'
88 ,p_person_id => l_person_id
89 ,p_object_version_number => l_ovn
90 ,p_employee_number => l_emp_no
91 ,p_effective_start_date => l_eff_sdt
92 ,p_effective_end_date => l_eff_edt
93 ,p_full_name =>l_full_name
94 ,p_title => p_title
95 ,p_comment_id => l_comment_id
96 ,p_name_combination_warning =>l_name_combination_warning
97 ,p_assign_payroll_warning => l_assign_payroll_warning
98 ,p_orig_hire_warning => l_orig_hire_warning);
99
100
101 fnd_file.put_line(FND_FILE.OUTPUT,rpad(v_csr_person.person_id,20)||' '||rpad(v_csr_person.national_identifier,20)||' '||rpad(v_csr_person.effective_start_date,20)||' '||
102 rpad(v_csr_person.effective_end_date,20)||' '||rpad(v_csr_person.full_name,75)||' '||rpad(l_full_name,75));
103
104 end loop;
105
106 if l_no_data_found = 'Y' THEN
107 fnd_file.put_line(FND_FILE.OUTPUT,'--------------------------------No Person record with HU_PROF title, So no records updated---------------------------------');
108 end if;
109
110 --
111 EXCEPTION
112 WHEN OTHERS THEN
113 ROLLBACK;
114 fnd_file.put_line(FND_FILE.OUTPUT,SQLERRM);
115 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
116
117 END run;
118 --
119 END pay_gb_person_title_update;