1 PACKAGE BODY pay_au_dlec AS
2 /* $Header: pyaudlec.pkb 120.0 2005/05/29 03:04 appldev noship $ */
3
4 ----------------------------------------------------------------------------
5 -- --
6 -- Name : DISPLAY_LE_CHANGE --
7 -- Type : PROCEDURE --
8 -- Access : Private --
9 -- Description : Procedure to display the employees who have had a --
10 -- : change in legal employer in a specified financial --
11 -- year for AU. --
12 -- p_financial_year_end is in format YYYY where YYYY --
13 -- is the ending financial year. --
14 -- Eg. enter 2005 for Financial Year 2004/2005 --
15 -- --
16 -- Parameters : --
17 -- IN : p_business_group_id NUMBER --
18 -- p_financial_year_end VARCHAR2 --
19 -- OUT : --
20
21 PROCEDURE display_le_change ( errbuf OUT NOCOPY VARCHAR2
22 ,retcode OUT NOCOPY NUMBER
23 ,p_business_group_id IN NUMBER
24 ,p_financial_year_end IN NUMBER
25 )
26 IS
27
28 -- Determine all assignments which have had a change of legal employer.
29 -- Note : we're not checking effective date of the assignments because
30 -- we want to check all date-tracked changes for the assignment.
31
32 cursor c_select_assignments
33 (
34 c_business_group_id per_all_assignments_f.business_group_id%type,
35 c_start_date per_all_assignments_f.effective_start_date%type,
36 c_end_date per_all_assignments_f.effective_end_date%type
37 )
38 is
39 select asg.assignment_id,
40 asg.assignment_number,
41 asg.effective_start_date,
42 asg.effective_end_date,
43 asg.person_id,
44 scl.segment1
45 from per_all_assignments_f asg
46 ,hr_soft_coding_keyflex scl
47 where scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
48 and asg.business_group_id = c_business_group_id
49 and asg.effective_start_date <= c_end_date
50 and asg.effective_end_date >= c_start_date
51 order by asg.assignment_id,
52 asg.effective_start_date;
53
54 l_prev_assignment_id number := 0;
55 l_prev_legal_emp number := 0;
56 l_count number := 0;
57
58 l_year number := 0;
59 l_fin_year_start date;
60 l_fin_year_end date;
61 l_today date;
62 l_business_group_name hr_all_organization_units.name%type;
63
64 type l_asg_rec is record
65 (assignment_id per_all_assignments_f.assignment_id%type,
66 assignment_number per_all_assignments_f.assignment_number%type,
67 effective_start_date per_all_assignments_f.effective_start_date%type,
68 effective_end_date per_all_assignments_f.effective_end_date%type,
69 segment1 hr_soft_coding_keyflex.segment1%type,
70 prev_segment1 hr_soft_coding_keyflex.segment1%type,
71 seg1_name hr_organization_units.name%type,
72 seg1_prev_name hr_organization_units.name%type,
73 person_id per_all_assignments_f.person_id%type,
74 full_name per_all_people_f.full_name%type
75 );
76
77 type l_asg_table_def is table of l_asg_rec
78 index by binary_integer;
79
80 l_asg_tab l_asg_table_def;
81
82 l_proc_name VARCHAR2(150);
83
84 BEGIN
85
86 -- Clear out assignment table.
87
88 select to_char(sysdate,'DD-MON-YYYY')
89 into l_today
90 from dual;
91
92 -- Get the business group name.
93
94 select hou.name
95 into l_business_group_name
96 from hr_all_organization_units hou,
97 hr_organization_information hoi
98 where hou.organization_id = hou.business_group_id
99 and hou.organization_id = hoi.organization_id
100 and hoi.org_information_context = 'Business Group Information'
101 and hou.business_group_id = p_business_group_id;
102
103 -- Set up the start and end financial year dates.
104
105 l_year := to_number(p_financial_year_end) - 1;
106
107 l_fin_year_start := to_date('01-07-' || l_year, 'dd-mm-yyyy');
108
109 l_fin_year_end := to_date('30-06-' || p_financial_year_end, 'dd-mm-yyyy');
110
111 l_asg_tab.delete;
112
113 fnd_file.put_line(fnd_file.output, ' Listing of Assignments who have changed Legal Employer in Financial Year');
114 fnd_file.put_line(fnd_file.output, '---------------------------------------------------------------------------- ');
115 fnd_file.put_line(fnd_file.output, ' ');
116 fnd_file.put_line(fnd_file.output, 'Report Date : ' ||
117 to_char(l_today, 'dd-MON-yyyy' ));
118 fnd_file.put_line(fnd_file.output, ' ');
119 fnd_file.put_line(fnd_file.output, 'Parameters : ');
120 fnd_file.put_line(fnd_file.output, ' Business Group Id : ' ||
121 l_business_group_name );
122 fnd_file.put_line(fnd_file.output, ' Financial Year : ' ||
123 to_char(l_fin_year_start, 'dd-MON-yyyy') || ' to ' ||
124 to_char(l_fin_year_end, 'dd-MON-yyyy') );
125 fnd_file.put_line(fnd_file.output, ' ');
126 fnd_file.put_line(fnd_file.output, '---------------------------------------------------------------------------- ');
127
128 for asg_rec in c_select_assignments(p_business_group_id,
129 l_fin_year_start,
130 l_fin_year_end)
131 loop
132
133 -- For each assignment check with previous legal employer if it's the
134 -- same assignment and it started in the fin year we require.
135
136 if asg_rec.assignment_id = l_prev_assignment_id and
137 asg_rec.effective_start_date between l_fin_year_start
138 and l_fin_year_end then
139
140 if l_prev_legal_emp <> asg_rec.segment1 then
141 l_count := l_count + 1;
142 l_asg_tab(l_count).assignment_id := asg_rec.assignment_id;
143 l_asg_tab(l_count).assignment_number := asg_rec.assignment_number;
144 l_asg_tab(l_count).effective_start_date := asg_rec.effective_start_date;
145 l_asg_tab(l_count).effective_end_date := asg_rec.effective_end_date;
146 l_asg_tab(l_count).person_id := asg_rec.person_id;
147 l_asg_tab(l_count).segment1 := asg_rec.segment1;
148 l_asg_tab(l_count).prev_segment1 := l_prev_legal_emp;
149 end if;
150
151 end if;
152
153 l_prev_assignment_id := asg_rec.assignment_id;
154 l_prev_legal_emp := asg_rec.segment1;
155
156 end loop;
157
158 fnd_file.put_line(fnd_file.output, ' ');
159 fnd_file.put_line(fnd_file.output, 'Assignments that have changed legal employer in ' ||
160 'financial year ' ||
161 to_char(l_fin_year_start, 'dd-MON-yyyy') || ' to ' ||
162 to_char(l_fin_year_end, 'dd-MON-yyyy') );
163 fnd_file.put_line(fnd_file.output, ' ');
164
165 if l_asg_tab.count > 0 then
166 fnd_file.put_line(fnd_file.output, ' Full Name Assignment Number Date of New LE New Legal Employer Previous Legal Employer');
167 fnd_file.put_line(fnd_file.output, ' ');
168 end if;
169
170 if l_asg_tab.count > 0 then
171 for i in 1..l_asg_tab.last
172 loop
173
174 -- Get the legal employer name
175
176 select hou.name
177 into l_asg_tab(i).seg1_name
178 from hr_organization_units hou
179 where l_asg_tab(i).segment1 = hou.organization_id;
180
181 -- Get the previous legal employer name
182
183 select hou.name
184 into l_asg_tab(i).seg1_prev_name
185 from hr_organization_units hou
186 where l_asg_tab(i).prev_segment1 = hou.organization_id;
187
188 -- Get the persons full name.
189
190 select per.full_name
191 into l_asg_tab(i).full_name
192 from per_all_people_f per
193 where per.person_id = l_asg_tab(i).person_id
194 and l_fin_year_end between per.effective_start_date and per.effective_end_date;
195
196 -- Output the line.
197
198 fnd_file.put_line(fnd_file.output,
199 ' ' || rpad(l_asg_tab(i).full_name, 30, ' ') ||
200 ' ' || rpad(l_asg_tab(i).assignment_number, 15, ' ') ||
201 ' ' || to_char(l_asg_tab(i).effective_start_date, 'dd-MON-yyyy') ||
202 ' ' || rpad(l_asg_tab(i).seg1_name, 30, ' ') ||
203 ' ' || rpad(l_asg_tab(i).seg1_prev_name, 30, ' ')
204 );
205 end loop;
206 else
207 fnd_file.put_line(fnd_file.output, 'No assignments have had a legal employer change in the specified financial year');
208 end if;
209
210 fnd_file.put_line(fnd_file.output, ' ');
211 fnd_file.put_line(fnd_file.output, ' ');
212 fnd_file.put_line(fnd_file.output, ' ');
213 fnd_file.put_line(fnd_file.output, ' ');
214 fnd_file.put_line(fnd_file.output, ' E N D O F R E P O R T ');
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 hr_utility.trace('AU : Exception, Leaving: '|| l_proc_name);
219 RAISE;
220
221 END display_le_change;
222
223 END pay_au_dlec;
[Home] [Help]