1 PACKAGE BODY PAY_FR_SETTLEMENT AS
2 /* $Header: pyfrsett.pkb 115.6 2003/07/08 16:16:51 sfmorris noship $ */
3 --
4 -- Cursor To Retrieve Employee Payment Information
5 --
6 Cursor c_get_payment(p_assignment_id number
7 ,p_last_day_paid date) is
8
9 Select sum(actinfo.action_information12) total_pay
10 from pay_action_information actinfo
11 , pay_assignment_actions aa
12 , pay_payroll_actions pa
13 where actinfo.action_information_category = 'FR_SOE_EE_TOTALS'
14 and actinfo.action_context_id = aa.assignment_action_id
15 and aa.assignment_id = p_assignment_id
16 and aa.payroll_action_id = pa.payroll_action_id
17 and pa.effective_date
18 between trunc(p_last_day_paid,'MM') and last_day(p_last_day_paid)
19 and pa.payroll_action_id =
20 (select max(pa1.payroll_action_id)
21 from pay_payroll_actions pa1
22 , pay_assignment_actions aa1
23 , pay_action_information actinfo1
24 where actinfo1.action_information_category = 'FR_SOE_EE_TOTALS'
25 and actinfo1.action_context_id = aa.assignment_action_id
26 and aa1.assignment_id = p_assignment_id
27 and aa1.payroll_action_id = pa1.payroll_action_id
28 and pa1.effective_date
29 between trunc(p_last_day_paid,'MM') and last_day(p_last_day_paid)
30 )
31 ;
32 --
33 -- Function To Return Employee Payment
34 --
35 Function get_payment(p_assignment_id number
36 ,p_effective_date date) return number is
37 --
38 l_payment number;
39 --
40 begin
41 --
42 Open c_get_payment(p_assignment_id, p_effective_date);
43 Fetch c_get_payment into l_payment;
44 Close c_get_payment;
45 --
46 return l_payment;
47 --
48 end get_payment;
49 --
50 -- Procedure to Concatenate Addresses
51 --
52 procedure concat(p_concat1 IN OUT NOCOPY varchar2
53 ,p_concat2 IN varchar2) is
54 --
55 begin
56 if p_concat1 is not null and p_concat2 is not null then
57 p_concat1:= p_concat1 || ' ' || p_concat2;
58 else
59 p_concat1:= p_concat1 || p_concat2;
60 end if;
61 end concat;
62 --
63 -- Function To Return Formatted Address
64 --
65 Function format_address(p_complement varchar2
66 ,p_road varchar2
67 ,p_small_town varchar2
68 ,p_postal_code varchar2
69 ,p_town_or_city varchar2) return varchar2 is
70 --
71 l_address varchar2(2000);
72 --
73 begin
74 concat(l_address, p_complement);
75 concat(l_address, p_road);
76 concat(l_address, p_small_town);
77 concat(l_address, p_postal_code);
78 concat(l_address, p_town_or_city);
79 --
80 return l_address;
81 --
82 end format_address;
83 --
84 -- Function To Return Formatted Full Name
85 --
86 Function format_full_name(p_title varchar2
87 ,p_first_name varchar2
88 ,p_last_name varchar2 ) return varchar2 is
89 --
90 l_full_name varchar2(2000);
91 --
92 begin
93 l_full_name := p_title || ' ' || p_first_name || ' ' || p_last_name;
94 --
95 return l_full_name;
96 --
97 end format_full_name;
98 --
99 -- The procedure process has been commented out as it is no longer used
100 -- now that the report uses Web ADI and not a concurrent program.
101 --
102 /*
103 Procedure process(errbuf OUT NOCOPY VARCHAR2,
104 retcode OUT NOCOPY NUMBER,
105 p_start_date IN DATE,
106 p_end_date IN DATE,
107 p_set_or_asg IN VARCHAR2,
108 p_dummy IN VARCHAR2,
109 p_dummy1 IN VARCHAR2,
110 p_assignment_set_id IN NUMBER,
111 p_assignment_id IN NUMBER,
112 p_separator IN VARCHAR2) is
113
114 --
115 -- Cursor To Get Employee Details
116 --
117 cursor c_get_employee(l_assignment_id number) is
118 Select a.assignment_id assignment_id
119 , p.first_name first_name
120 , p.last_name last_name
121 , estorg.name establishment_name
122 , estinfo.org_information2 SIRET
123 , estinfo.org_information3 NAF
124 , comporg.name company_name
125 --
126 -- Employee Address
127 --
128 , addr.address_line1 addr_road
129 , addr.address_line2 addr_complement
130 , addr.region_3 addr_small_town
131 , addr.postal_code addr_postal_code
132 , addr.town_or_city addr_town_or_city
133 --
134 -- Company Address
135 --
136 , comploc.address_line_1 compaddr_road
137 , comploc.address_line_2 compaddr_complement
138 , comploc.region_3 compaddr_small_town
139 , comploc.postal_code compaddr_postal_code
140 , comploc.town_or_city compaddr_town_or_city
141 --
142 -- Establishment Address
143 --
144 , estloc.address_line_1 estaddr_road
145 , estloc.address_line_2 estaddr_complement
146 , estloc.region_3 estaddr_small_town
147 , estloc.postal_code estaddr_postal_code
148 , estloc.town_or_city estaddr_town_or_city
149 --
150 --
151 --
152 , decode(pds.pds_information11,'LAST_DAY_WORKED'
153 , fnd_date.canonical_to_date(pds.pds_information10)
154 , pds.actual_termination_date) last_day_paid
155 from per_all_people_f p
156 , per_all_assignments_f a
157 , per_periods_of_service pds
158 , hr_organization_information estinfo
159 , hr_all_organization_units estorg
160 , hr_all_organization_units comporg
161 , per_addresses addr
162 , hr_locations comploc
163 , hr_locations estloc
164 where (l_assignment_id is not null and
165 a.assignment_id = l_assignment_id)
166 --
167 -- Ensure that the last day paid is between the start and end dates
168 -- entered as parameters
169 -- N.B. Last Day Paid is taken as Last Day Worked if Final Payment Schedule
170 -- is LAST_DAY_WORKED, otherwise Actual Termination Date.
171 --
172 and decode(pds.pds_information11,'LAST_DAY_WORKED'
173 , fnd_date.canonical_to_date(pds.pds_information10)
174 , pds.actual_termination_date)
175 between p_start_date and p_end_date
176 --
177 -- Use the Last Day Paid (Last Day Worked or ATD) to determine the date
178 -- effectivity of the Person and Assignment records
179 --
180 and decode(pds.pds_information11,'LAST_DAY_WORKED'
181 , fnd_date.canonical_to_date(pds.pds_information10)
182 , pds.actual_termination_date)
183 between p.effective_start_date and p.effective_end_date
184 --
185 and p.person_id = a.person_id
186 and decode(pds.pds_information11,'LAST_DAY_WORKED'
187 , fnd_date.canonical_to_date(pds.pds_information10)
188 , pds.actual_termination_date)
189 between a.effective_start_date and a.effective_end_date
190 and a.period_of_service_id = pds.period_of_service_id
191 --
192 -- Get Establishment Details
193 --
194 and a.establishment_id = estorg.organization_id
195 and estinfo.org_information_context = 'FR_ESTAB_INFO'
196 and estorg.organization_id = estinfo.organization_id
197 --
198 -- Get Company Details
199 --
200 and to_number(estinfo.org_information1) = comporg.organization_id
201 --
202 -- Get Person Address Details
203 --
204 and p.person_id = addr.person_id(+)
205 and addr.primary_flag(+) = 'Y'
206 --
207 -- Get Company Address Details
208 --
209 and comporg.location_id = comploc.location_id(+)
210 --
211 -- Get Establishment Address Details
212 --
213 and estorg.location_id = estloc.location_id(+)
214 order by p.last_name;
215 --
216 -- Cursor To Retrieve Employee Payment Information
217 --
218 cursor c_get_payment(l_assignment_id number
219 ,p_last_day_paid date) is
220 Select sum(actinfo.action_information12) total_pay
221 from pay_action_information actinfo
222 , pay_assignment_actions aa
223 , pay_payroll_actions pa
224 where actinfo.action_information_category = 'FR_SOE_EE_TOTALS'
225 and actinfo.action_context_id = aa.assignment_action_id
226 and aa.assignment_id = l_assignment_id
227 and aa.payroll_action_id = pa.payroll_action_id
228 and pa.effective_date
229 between trunc(p_last_day_paid,'MM') and last_day(p_last_day_paid)
230 and pa.effective_date =
231 (select max(pa1.effective_date)
232 from pay_payroll_actions pa1
233 , pay_assignment_actions aa1
234 where aa1.assignment_id = l_assignment_id
235 and aa1.payroll_action_id = pa1.payroll_action_id
236 and pa1.effective_date
237 between trunc(p_last_day_paid,'MM') and last_day(p_last_day_paid)
238 );
239 --
240 -- Cursor To Get All Employees In An Assignment Set
241 --
242 cursor c_get_assignment_set_employees(p_assignment_set_id number) is
243 select amend.assignment_id
244 from hr_assignment_set_amendments amend
245 where amend.assignment_set_id = p_assignment_set_id
246 and amend.include_or_exclude = 'I';
247 --
248 -- Declaration Of Local Variables Used In This Package
249 --
250 emp c_get_employee%ROWTYPE;
251 l_emp varchar2(1000);
252 payment number;
253 l_header varchar2(1000);
254 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
255 --
256 -- Procedure to Concatenate Addresses
257 --
258 procedure concat(p_concat1 IN OUT NOCOPY varchar2
259 ,p_concat2 IN varchar2) is
260 begin
261 if p_concat1 is not null and p_concat2 is not null then
262 p_concat1:= p_concat1 || ' ' || p_concat2;
263 else
264 p_concat1:= p_concat1 || p_concat2;
265 end if;
266 end concat;
267 --
268 -- Function to Format Address Into Required Style For Report
269 --
270 Function format_address(p_complement varchar2
271 ,p_road varchar2
272 ,p_small_town varchar2
273 ,p_postal_code varchar2
274 ,p_town_or_city varchar2) return varchar2 is
275 l_address varchar2(2000);
276 --
277 begin
278 concat(l_address, p_complement);
279 concat(l_address, p_road);
280 concat(l_address, p_small_town);
281 concat(l_address, p_postal_code);
282 concat(l_address, p_town_or_city);
283 --
284 return l_address;
285 end format_address;
286 --
287 -- Procedure To Get And Print Employee Details To The File
288 --
289 procedure print_employee_details(l_assignment_id number) is
290 begin
291 --
292 -- Get Employee Info
293 --
294 Open c_get_employee(l_assignment_id);
295 Fetch c_get_employee into emp;
296 Close c_get_employee;
297 --
298 -- Concatenate the values with the spearator
299 --
300 l_emp := emp.first_name||p_separator||
301 emp.last_name||p_separator||
302 format_address(emp.addr_complement
303 ,emp.addr_road
304 ,emp.addr_small_town
305 ,emp.addr_postal_code
306 ,emp.addr_town_or_city)||p_separator||
307 emp.company_name||p_separator||
308 format_address(emp.compaddr_complement
309 ,emp.compaddr_road
310 ,emp.compaddr_small_town
311 ,emp.compaddr_postal_code
312 ,emp.compaddr_town_or_city)||p_separator||
313 emp.establishment_name||p_separator||
314 format_address(emp.estaddr_complement
315 ,emp.estaddr_road
316 ,emp.estaddr_small_town
317 ,emp.estaddr_postal_code
318 ,emp.estaddr_town_or_city)||p_separator||
319 emp.SIRET||p_separator||
320 emp.NAF;
321 --
322 --
323 -- Retrieve Employee Payment Info
324 --
325 Open c_get_payment(l_assignment_id, emp.last_day_paid);
326 Fetch c_get_payment into payment;
327 Close c_get_payment;
328 --
329 -- Concatenate the values with the separator
330 --
331 l_emp := l_emp ||p_separator||
332 payment||p_separator||
333 pay_ca_amt_in_words.pay_amount_in_words(payment
334 ,userenv('LANG'));
335 --
336 -- Output Employee Record To The File
337 --
338 Fnd_file.put_line(FND_FILE.OUTPUT,l_emp);
339 end print_employee_details;
340
341 begin
342 --
343 -- Build Up Report Header Line
344 --
345 l_header :=
346 hr_general.decode_lookup('NAME_TRANSLATIONS','FR_FIRST_NAME') ||
347 p_separator ||
348 hr_general.decode_lookup('NAME_TRANSLATIONS','FR_LAST_NAME') ||
349 p_separator ||
350 hr_general.decode_lookup('NAME_TRANSLATIONS','FR_EMPLOYEE_ADDRESS') ||
351 p_separator ||
352 hr_general.decode_lookup('NAME_TRANSLATIONS','FR_COMPANY_NAME') ||
353 p_separator ||
354 hr_general.decode_lookup('NAME_TRANSLATIONS','FR_COMPANY_ADDRESS') ||
355 p_separator ||
356 hr_general.decode_lookup('NAME_TRANSLATIONS','FR_ESTABLISHMENT_NAME') ||
357 p_separator ||
358 hr_general.decode_lookup('NAME_TRANSLATIONS','FR_ESTABLISHMENT_ADDRESS') ||
359 p_separator ||
360 'SIRET' ||
361 p_separator ||
362 'NAF' ||
363 p_separator ||
364 hr_general.decode_lookup('NAME_TRANSLATIONS','FR_PAYMENT') ||
365 p_separator ||
366 hr_general.decode_lookup('NAME_TRANSLATIONS','FR_PAYMENT_IN_WORDS');
367 --
368 -- Output Header To File
369 --
370 Fnd_file.put_line(FND_FILE.OUTPUT,l_header);
371 --
372 -- If an assignment id was passed in, then the details of that single employee
373 -- must be output to the file. If an assignment set id was passed in, then a
374 -- loop is entered to print details for every employee in the assignment set.
375 -- If neither is entered then a message is output to the log.
376 --
377 IF p_assignment_id is not null THEN
378 l_assignment_id := p_assignment_id;
379 print_employee_details(l_assignment_id);
380 ELSIF p_assignment_set_id is not null THEN
381 Open c_get_assignment_set_employees(p_assignment_set_id);
382 LOOP
383 fetch c_get_assignment_set_employees into l_assignment_id;
384 exit when c_get_assignment_set_employees%NOTFOUND;
385 print_employee_details(l_assignment_id);
386 END LOOP;
387 close c_get_assignment_set_employees;
388 ELSE fnd_file.put_line(FND_FILE.LOG, 'No assignment or assignment set was entered');
389 END IF;
390 end process;
391 */
392 end PAY_FR_SETTLEMENT;
[Home] [Help]