[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_ENROLL_INFO_GENE
Source
4
1 PACKAGE body pay_gb_enroll_info_gene as
2 /* $Header: pygbpaeig.pkb 120.0.12020000.9 2013/02/18 04:49:00 rsadhana noship $ */
3
5 l_set_layout boolean;
6 p_request_id number;
7 l_product_release varchar2(30);
8 counter number;
9 -----------------------
10 l_prp_date_paid date;
11 l_prp_start_date date;
12 l_prp_end_date date;
13
14 l_pay_date_paid date;
15 l_pay_start_date date;
16 l_pay_end_date date;
17 l_tab_asg_set_amnds pqp_budget_maintenance.t_asg_set_amnds;
18 l_formula_id Number :=0;
19 l_flag varchar2(1);
20
21
22 -----------------------
23 CURSOR c_payroll_details(p_period number) IS
24 SELECT start_date,
25 end_date,
26 regular_payment_date
27 FROM per_time_periods ptp
28 WHERE ptp.time_period_id = p_period;
29
30 CURSOR c_prp_details(p_payroll number) IS
31 SELECT start_date,
32 end_date,
33 regular_payment_date
34 FROM per_time_periods ptp
35 WHERE trunc(l_pay_date_paid) BETWEEN start_date AND end_date
36 AND payroll_id=p_payroll;
37
38 CURSOR c_comm_details(P_PAYROLL_ID number)
39 IS
40 SELECT PCV.PCV_INFORMATION1,
41 PCV.PCV_INFORMATION2,PCV.PCV_INFORMATION3,
42 PCV.PCV_INFORMATION4
43 FROM PQP_CONFIGURATION_VALUES PCV,
44 PAY_PAYROLLS_F PPF
45 WHERE PCV.PCV_INFORMATION_CATEGORY like 'PAY_GB_ENROLMENT_COMM_DETAILS'
46 AND PPF.business_group_id = PCV.business_group_id
47 AND PPF.PAYROLL_ID = P_PAYROLL_ID ;
48
49
50 CURSOR c_comm_details_qual(P_PAYROLL_ID number,P_QUALIFYING_SCHEME VARCHAR) -- Comm Details from Qualifying Scheme
51 IS
52 SELECT PCV.PCV_INFORMATION14,
53 PCV.PCV_INFORMATION15,PCV.PCV_INFORMATION16,PCV.PCV_INFORMATION17
54 FROM PQP_CONFIGURATION_VALUES PCV,
55 PAY_PAYROLLS_F PPF
56 WHERE PCV.PCV_INFORMATION_CATEGORY like 'PAY_GB_QUALIFIED_PENSIONS_INFO'
57 AND PPF.business_group_id = PCV.business_group_id
58 AND PCV.PCV_INFORMATION1 = P_QUALIFYING_SCHEME
59 AND PPF.PAYROLL_ID = P_PAYROLL_ID ;
60
61
62
63 cursor c_tax_ref_details(p_payroll number) IS
64 SELECT trim (org_information3) employer
65 , ppf.period_type period
66 FROM pay_payrolls_f ppf
67 , hr_soft_coding_keyflex hscf
68 , hr_organization_information hoi
69 WHERE ppf.payroll_id = p_payroll
70 AND ppf.business_group_id = hoi.organization_id
71 AND hoi.org_information_context = 'Tax Details References'
72 AND nvl (hoi.org_information10, 'UK') = 'UK'
73 AND ppf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id
74 AND hscf.segment1 = hoi.org_information1
75 AND l_prp_end_date
76 BETWEEN ppf.effective_start_date
77 AND ppf.effective_end_date;
78
79 CURSOR c_get_global_value
80 IS
81 SELECT
82 max(decode(global_name,'AUTO_ENROL_TRIG_WEEKLY', global_value ,null)) auto_enrol_trig_weekly ,
83 max(decode(global_name,'AUTO_ENROL_TRIG_MONTHLY', global_value ,null)) auto_enrol_trig_monthly ,
84 max(decode(global_name,'AUTO_ENROL_TRIG_ANNUAL', global_value ,null)) auto_enrol_trig_annual,
85 max(decode(global_name,'AUTO_ENROL_TRIG_BIWEEK', global_value ,null)) auto_enrol_trig_biweek,
86 max(decode(global_name,'AUTO_ENROL_TRIG_LUNAR', global_value ,null)) auto_enrol_trig_lunar,
87 max(decode(global_name,'AUTO_ENROL_TRIG_QUARTERLY', global_value ,null)) auto_enrol_trig_quarterly,
88 max(decode(global_name,'AUTO_ENROL_TRIG_SEMIYEAR', global_value ,null)) auto_enrol_trig_semiyear,
89 max(decode(global_name,'QE_ANNUAL_LT', global_value ,null)) QE_ANNUAL_LT
90 ,max(decode(global_name,'QE_BIWEEK_LT', global_value ,null)) QE_BIWEEK_LT
91 ,max(decode(global_name,'QE_LUNAR_LT', global_value ,null)) QE_LUNAR_LT
92 ,max(decode(global_name,'QE_MONTHLY_LT', global_value ,null)) QE_MONTHLY_LT
93 ,max(decode(global_name,'QE_QUARTERLY_LT', global_value ,null)) QE_QUARTERLY_LT
94 ,max(decode(global_name,'QE_SEMIYEAR_LT', global_value ,null)) QE_SEMIYEAR_LT
95 ,max(decode(global_name,'QE_WEEKLY_LT', global_value ,null)) QE_WEEKLY_LT
96 FROM FF_GLOBALS_F FGF
97 WHERE global_name IN ( 'AUTO_ENROL_TRIG_ANNUAL', 'AUTO_ENROL_TRIG_WEEKLY', 'AUTO_ENROL_TRIG_MONTHLY',
98 'AUTO_ENROL_TRIG_BIWEEK','AUTO_ENROL_TRIG_LUNAR','AUTO_ENROL_TRIG_QUARTERLY',
99 'AUTO_ENROL_TRIG_SEMIYEAR','QE_ANNUAL_LT','QE_BIWEEK_LT','QE_LUNAR_LT','QE_MONTHLY_LT'
100 ,'QE_QUARTERLY_LT','QE_SEMIYEAR_LT','QE_WEEKLY_LT')
101 AND l_prp_end_date BETWEEN fgf.effective_start_date and fgf.effective_end_date;
102
103 -- Input value Ids of the 13 input values
104 CURSOR cur_input_id IS
105 -- Aggregation is not considered , Info is generated for every assignment
106 SELECT max (decode (name, 'Qualifying Scheme Name', input_value_id)) qualifying_scheme_name
107 , max (decode (name, 'Auto Enrollment Date', input_value_id)) auto_enrollment_date
108 , max (decode (name, 'Pension Classification', input_value_id)) pension_classification
109 , max (decode (name, 'Opt Out Period End Date', input_value_id)) opt_out_period_end_date
110 , max (decode (name, 'Total Earning PRP' , input_value_id)) total_earning_prp
111 , max (decode (name, 'Postponement Type' , input_value_id)) postponement_type
112 , max (decode (name, 'Postponement End Date', input_value_id)) postponement_end_date
113 , max (decode (name, 'Qualifying scheme exists' , input_value_id)) qualifying_scheme_exists
114 , max (decode (name, 'Opt in Date', input_value_id)) opt_in_date
115 , max (decode (name, 'Eligible Job Holder Date', input_value_id)) elig_jh_date
116 , max (decode (name, 'Opt Out Date' , input_value_id)) opt_out_date
120 FROM (
117 , max (decode (name, 'Aggregate Earnings' , input_value_id)) agg_earnings_pensions_id
118 , max (decode (name, 'Main Entry for Aggregation' , input_value_id)) main_entry_id
119
121 SELECT input_value_id
122 , name
123 FROM pay_input_values_f iv,pay_element_types_f et where
124 et.element_type_id = iv.element_type_id
125 and et.element_name = 'Pensions Information'
126 );
127
128 l_input_value_ids cur_input_id%rowtype;
129 l_person_id number;
130 l_address csr_address%rowtype;
131
132
133 CURSOR c_person_details(p_assignment_id number) IS
134 SELECT papf.full_name, papf.email_address, papf.person_id FROM
135 per_all_people_f papf,
136 per_all_assignments_f paaf
137 WHERE
138 papf.person_id=paaf.person_id
139 AND paaf.assignment_id = p_assignment_id
140 AND l_prp_end_date BETWEEN
144
141 paaf.effective_start_date and paaf.effective_end_date
142 AND l_prp_end_date BETWEEN
143 papf.effective_start_date and papf.effective_end_date;
145 cursor c_pension_info_check(p_assignment_id number) is
146 select 1 from pay_element_entries_f peef,pay_element_types_f petf where
147 peef.element_type_id = petf.element_type_id
148 and petf.element_name = 'Pensions Information'
149 and p_assignment_id = peef.assignment_id
150 AND l_prp_end_date BETWEEN
151 peef.effective_start_date and peef.effective_end_date
152 AND l_prp_end_date BETWEEN
153 petf.effective_start_date and petf.effective_end_date;
154 l_temp number;
155
156
157 cursor c_archive_details_check(p_assignment_id number,p_letter_type pay_action_information.action_information30%type) is
158 select * from pay_action_information where
159 action_context_id in
160 (
161 SELECT assignment_action_id
162 FROM fnd_concurrent_programs fcp
163 , fnd_concurrent_requests fcr
164 , pay_payroll_actions ppa
165 , pay_assignment_actions paa
166 where fcr.REQUEST_ID = ppa.REQUEST_ID
167 AND ppa.payroll_action_id = paa.payroll_action_id
168 AND concurrent_program_name = 'PENAUTOENR'
169 AND fcp.concurrent_program_id = fcr.concurrent_program_id
170 AND paa.assignment_id = p_assignment_id
171 )
172 AND ACTION_INFORMATION_CATEGORY='GB_ENROLL_PENSIONS'
173 AND action_information30=p_letter_type
174 AND ACTION_CONTEXT_TYPE='AAP';
175
176 cursor c_archive_details_update (p_assignment_id number,p_period_id number) is
177 select * from pay_action_information where
178 action_context_id in
179 (
180 SELECT assignment_action_id
181 FROM fnd_concurrent_programs fcp
182 , fnd_concurrent_requests fcr
183 , pay_payroll_actions ppa
184 , pay_assignment_actions paa
185 where fcr.REQUEST_ID = ppa.REQUEST_ID
186 AND ppa.payroll_action_id = paa.payroll_action_id
187 AND concurrent_program_name = 'PENAUTOENR'
188 AND fcp.concurrent_program_id = fcr.concurrent_program_id
189 AND paa.assignment_id = p_assignment_id
190 AND (substr(substr(LEGISLATIVE_PARAMETERS,(instr(LEGISLATIVE_PARAMETERS,' ',1,4)+1))
191 ,1,length(substr(substr(LEGISLATIVE_PARAMETERS,(instr(LEGISLATIVE_PARAMETERS,' ',1,4)+1)),1,
192 (instr(substr(LEGISLATIVE_PARAMETERS,(instr(LEGISLATIVE_PARAMETERS,' ',1,4)+1)),' ',1,1)-1))))) ='GB_VALIDATE_COMMIT'
193 AND substr(substr(LEGISLATIVE_PARAMETERS,(instr(LEGISLATIVE_PARAMETERS,' ',1,2)+1))
194 ,1,length(substr(substr(LEGISLATIVE_PARAMETERS,(instr(LEGISLATIVE_PARAMETERS,' ',1,2)+1)),1,
195 (instr(substr(LEGISLATIVE_PARAMETERS,(instr(LEGISLATIVE_PARAMETERS,' ',1,2)+1)),' ',1,1)-1)))) = p_period_id
196 )
197 AND ACTION_INFORMATION_CATEGORY='GB_ENROLL_PENSIONS'
198 AND ACTION_CONTEXT_TYPE='AAP'
199 order by action_context_id desc;-- LAtest archive data in that period
200
201 --Summary Info
202 cursor c_summary_details(p_assignment_id number) is
203 select assignment_number,full_name from per_all_people_f papf,per_all_assignments_f paaf
204 where papf.person_id = paaf.person_id
205 and paaf.assignment_id = p_assignment_id
206 and l_prp_end_date between papf.effective_start_date and papf.effective_end_date
207 and l_prp_end_date between paaf.effective_start_date and paaf.effective_end_date;
208
209 --Element Entry values for Pensions information Element
210 CURSOR c_pension_entry_values(l_assignment_id number)
211 IS
212 SELECT max(decode(trim(peevf.input_value_id),l_input_value_ids.qualifying_scheme_name,screen_entry_value, null)) qualifying_scheme_name,
213 max(decode(trim(peevf.input_value_id),l_input_value_ids.agg_earnings_pensions_id,screen_entry_value, null)) agg_earnings,
217 max(decode(trim(peevf.input_value_id),l_input_value_ids.total_earning_prp,screen_entry_value, null)) total_earning_prp,
214 fnd_date.canonical_to_date(max(decode(trim(peevf.input_value_id),l_input_value_ids.auto_enrollment_date,screen_entry_value, null))) auto_enrollment_date ,
215 max(decode(trim(peevf.input_value_id),l_input_value_ids.pension_classification,hr_general.decode_lookup('GB_PENSION_WORKER_TYPES',screen_entry_value), null)) pension_classification,
216 fnd_date.canonical_to_date(max(decode(trim(peevf.input_value_id),l_input_value_ids.opt_out_period_end_date,screen_entry_value, null))) opt_out_period_end_date,
218 max(decode(trim(peevf.input_value_id),l_input_value_ids.postponement_type,hr_general.decode_lookup('GB_PENSION_POSTPONEMENT_TYPES',screen_entry_value), null)) postponement_type ,
219 fnd_date.canonical_to_date(max(decode(trim(peevf.input_value_id),l_input_value_ids.postponement_end_date,screen_entry_value, null))) postponement_end_date,
220 max(decode(trim(peevf.input_value_id),l_input_value_ids.qualifying_scheme_exists,screen_entry_value, null)) qualifying_scheme_exists,
221 fnd_date.canonical_to_date(max(decode(trim(peevf.input_value_id),l_input_value_ids.opt_in_date,screen_entry_value, null))) opt_in_date,
222 max(decode(trim(peevf.input_value_id),l_input_value_ids.main_entry_id,screen_entry_value, null)) main_entry,
223 fnd_date.canonical_to_date(max(decode(trim(peevf.input_value_id),l_input_value_ids.elig_jh_date,screen_entry_value, null))) elig_jh_date,
224 fnd_date.canonical_to_date(max(decode(trim(peevf.input_value_id),l_input_value_ids.opt_out_date,screen_entry_value, null))) opt_out_date
225 from pay_element_entry_values_f peevf,
226 pay_element_entries_f peef,
227 pay_element_types_f petf
228 where peevf.input_value_id in (l_input_value_ids.qualifying_scheme_name ,
229 --l_input_value_ids.g_agg_earnings_pensions_id ,
230 l_input_value_ids.auto_enrollment_date ,
231 l_input_value_ids.pension_classification ,
232 l_input_value_ids.opt_out_period_end_date ,
233 l_input_value_ids.total_earning_prp ,
234 l_input_value_ids.postponement_type ,
235 l_input_value_ids.postponement_end_date ,
236 l_input_value_ids.qualifying_scheme_exists ,
237 l_input_value_ids.opt_in_date,
238 l_input_value_ids.elig_jh_date,
239 --l_input_value_ids.g_main_entry_id,
240 l_input_value_ids.opt_out_date)
241 and peevf.element_entry_id = peef.element_entry_id
242 and peef.element_type_id = petf.element_type_id
243 and petf.element_name = 'Pensions Information'
244 and peef.assignment_id = l_assignment_id
245 and l_prp_end_date -- Review again against the bug 14813352
246 between peevf.effective_start_date and peevf.effective_end_date
247 and l_prp_end_date -- Review again against the bug 14813352
248 between peef.effective_start_date and peef.effective_end_date
249 and l_prp_end_date -- Not Needed for pay_element_types_f -Review again against the bug 14813352
250 between petf.effective_start_date and petf.effective_end_date
251 group by peevf.element_entry_id;
252
253 l_global_value c_get_global_value%rowtype;
254
258 l_pension_age number := 65;
255 l_string VARCHAR2(32767) := NULL;
256 l_xml CLOB;
257 EOL VARCHAR2(5) := fnd_global.local_chr(10);
259 l_not varchar2(3) :='not';
260 --------------------------------------
261 l_employer_name VARCHAR2(150):= NULL;
262 l_employee_name per_all_people_f.full_name%type:= NULL;
263 l_email_address per_all_people_f.email_address%type:= NULL;
264 l_period pay_payrolls_f.period_type%type:= NULL;
265 l_earning ff_globals_f.global_value%type:= NULL;
266 l_earning_lower ff_globals_f.global_value%type:= NULL;
267
268 l_contact_mail_org VARCHAR2(150) := NULL;
269 l_more_info VARCHAR2(150) := NULL;
270 l_instruction VARCHAR2(150) := NULL;
271 l_signatory_name VARCHAR2(150) := NULL;
272
273 l_contact_mail_pqs VARCHAR2(150) := NULL;
274 l_more_info_pqs VARCHAR2(150) := NULL;
275 l_instruction_pqs VARCHAR2(150) := NULL;
276 l_signatory_name_pqs VARCHAR2(150) := NULL;
277
278 procedure update_archive(p_archive_details_update IN c_archive_details_update%rowtype
279 ,p_period IN number
280 ,p_issue_date IN date
281 ,p_postponement_date IN date default null
282 ,p_postponement_type IN varchar2
283 ,p_letter IN varchar2)
284 is
285 /*
286 ACTION_INFORMATION26 - Time Period
287 ACTION_INFORMATION27 - Issue Date
288 ACTION_INFORMATION28 - Postponement Date
289 ACTION_INFORMATION29 - Postponement Type
290 ACTION_INFORMATION30 - Letter Type
291 */
292 l_ovn number;
293 begin
294 fnd_file.put_line(fnd_file.LOG,'Entering update_archive');
295 l_ovn := p_archive_details_update.object_version_number;
296
297 pay_action_information_api.update_action_information
298 ( p_action_information_id =>p_archive_details_update.action_information_id
299 ,p_object_version_number =>l_ovn
300 ,p_action_information1 =>p_archive_details_update.action_information1
301 ,p_action_information2 =>p_archive_details_update.action_information2
302 ,p_action_information3 =>p_archive_details_update.action_information3
303 ,p_action_information4 =>p_archive_details_update.action_information4
304 ,p_action_information5 =>p_archive_details_update.action_information5
305 ,p_action_information6 =>p_archive_details_update.action_information6
306 ,p_action_information7 =>p_archive_details_update.action_information7
307 ,p_action_information8 =>p_archive_details_update.action_information8
308 ,p_action_information9 =>p_archive_details_update.action_information9
309 ,p_action_information10 =>p_archive_details_update.action_information10
310 ,p_action_information11 =>p_archive_details_update.action_information11
311 ,p_action_information12 =>p_archive_details_update.action_information12
312 ,p_action_information13 =>p_archive_details_update.action_information13
313 ,p_action_information14 =>p_archive_details_update.action_information14
314 ,p_action_information15 =>p_archive_details_update.action_information15
315 ,p_action_information16 =>p_archive_details_update.action_information16
316 ,p_action_information17 =>p_archive_details_update.action_information17
317 ,p_action_information18 =>p_archive_details_update.action_information18
318 ,p_action_information19 =>p_archive_details_update.action_information19
319 ,p_action_information20 =>p_archive_details_update.action_information20
320 ,p_action_information21 =>p_archive_details_update.action_information21
321 ,p_action_information22 =>p_archive_details_update.action_information22
322 ,p_action_information23 =>p_archive_details_update.action_information23
323 ,p_action_information24 =>p_archive_details_update.action_information24
324 ,p_action_information25 =>p_archive_details_update.action_information25
325 ,p_action_information26 =>p_period
326 ,p_action_information27 =>fnd_date.date_to_canonical(p_issue_date)
327 ,p_action_information28 =>fnd_date.date_to_canonical(p_postponement_date)
328 ,p_action_information29 =>p_postponement_type
329 ,p_action_information30 => P_LETTER);
330 end update_archive;
331
332 procedure generate_xml(p_letter IN varchar2,
333 p_assignment_id IN number,
334 p_pension_entry_values IN c_pension_entry_values%rowtype,
335 P_PERIOD IN number,
336 p_payroll IN NUmber,
337 p_issue_date IN DATE,
338 p_string IN OUT NOCOPY varchar2
339 )
340 is
341
342 l_string varchar2(32767);
343 l_archive_details_check c_archive_details_check%rowtype;
344 l_archive_details_update c_archive_details_update%rowtype;
345 l_flag varchar2(1):='N';
346 l_postponement_date date;
347 l_postponement_type varchar2(50);
348 l_opt_out_new date;
349 l_scheme_exists varchar2(3);
350 l_main_entry varchar2(3);
351 l_agg_earnings varchar2(3);
352
353 CURSOR C_ELEMENT_ENTRIES
354 IS
355 SELECT ELEMENT_ENTRY_ID, GREATEST(EFFECTIVE_START_DATE,l_PRP_START_DATE) START_DATE
356 FROM PAY_ELEMENT_ENTRIES_F PEEF
357 WHERE ASSIGNMENT_ID =P_ASSIGNMENT_ID
358 AND PEEF.ELEMENT_TYPE_ID in (select ELEMENT_TYPE_ID from pay_element_types_f where
359 element_name ='Pensions Information' and legislation_code ='GB')
360 AND l_PRP_START_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
361 -- OR EFFECTIVE_START_DATE > l_PRP_START_DATE);
362
363 begin
364 p_string := ' ';
365 l_string := ' ';
366 OPEN c_payroll_details(p_period);
367 FETCH c_payroll_details INTO l_pay_start_date,l_pay_end_date,l_pay_date_paid;
368 CLOSE c_payroll_details;
369
370 OPEN c_prp_details(p_payroll);
371 FETCH c_prp_details INTO l_prp_start_date,l_prp_end_date,l_prp_date_paid;
372 CLOSE c_prp_details;
373
374 OPEN c_comm_details(P_PAYROLL);
375 fetch c_comm_details into l_signatory_name,l_contact_mail_org,l_instruction,l_more_info;
376 close c_comm_details;
377
381
378 OPEN c_tax_ref_details(p_payroll);
379 FETCH c_tax_ref_details into l_employer_name,l_period;
380 CLOSE c_tax_ref_details;
382 OPEN c_get_global_value;
383 FETCH c_get_global_value into l_global_value;
384 CLOSE c_get_global_value;
385
386 IF l_period = 'Week' THEN
387 l_earning_lower := l_GLOBAL_VALUE.QE_WEEKLY_LT;
388 l_earning := l_GLOBAL_VALUE.AUTO_ENROL_TRIG_WEEKLY;
389 ELSIF l_period = 'Calendar Month' THEN
390 l_earning_lower := l_GLOBAL_VALUE.QE_MONTHLY_LT ;
391 l_earning := l_GLOBAL_VALUE.AUTO_ENROL_TRIG_MONTHLY ;
392 ELSIF l_period = 'Year' THEN
393 l_earning_lower := l_GLOBAL_VALUE.QE_ANNUAL_LT;
394 l_earning := l_GLOBAL_VALUE.AUTO_ENROL_TRIG_ANNUAL;
395 ELSIF l_period = 'Quarter' THEN
396 l_earning_lower := l_GLOBAL_VALUE.QE_QUARTERLY_LT ;
397 l_earning := l_GLOBAL_VALUE.AUTO_ENROL_TRIG_QUARTERLY ;
398 ELSIF l_period = 'Lunar Month' THEN
399 l_earning_lower := l_GLOBAL_VALUE.QE_LUNAR_LT ;
400 l_earning := l_GLOBAL_VALUE.AUTO_ENROL_TRIG_LUNAR ;
401 ELSIF l_period = 'Bi-Week' THEN
402 l_earning_lower := l_GLOBAL_VALUE.QE_BIWEEK_LT;
403 l_earning := l_GLOBAL_VALUE.AUTO_ENROL_TRIG_BIWEEK;
404 ELSIF l_period = 'Semi-Year' THEN
405 l_earning_lower := l_GLOBAL_VALUE.QE_SEMIYEAR_LT;
406 l_earning := l_GLOBAL_VALUE.AUTO_ENROL_TRIG_SEMIYEAR;
407 ELSIF l_period = 'Bi-Month' THEN -- Monthly's Threshold multiplied by 2
408 l_earning_lower := l_GLOBAL_VALUE.QE_MONTHLY_LT * 2;
409 l_earning := l_GLOBAL_VALUE.AUTO_ENROL_TRIG_MONTHLY * 2;
410 END IF;
411
412 OPEN c_person_details(p_assignment_id);
413 FETCH c_person_details INTO l_employee_name,l_email_address,l_person_id;
414 CLOSE c_person_details;
415 fnd_file.put_line(fnd_file.LOG,'Inside Generate XML');
416 open c_archive_details_check (p_assignment_id,P_LETTER);
417 fetch c_archive_details_check into l_archive_details_check;
418 IF c_archive_details_check%notfound then
419 l_flag :='Y';
420 fnd_file.put_line(fnd_file.LOG,'No Matching Archive data found.');
421 END IF;
422 close c_archive_details_check;
423
424 Open csr_address(l_person_id,l_PRP_START_DATE);
425 fetch csr_address into l_address;
426 close csr_address;
427
428 IF l_flag ='Y' THEN --Update Archive Table
429 OPEN c_archive_details_update(p_assignment_id,P_PERIOD) ;
430 FETCH c_archive_details_update into l_archive_details_update;
431 IF c_archive_details_update%notfound then
432 fnd_file.put_line(fnd_file.LOG,'Nothing to Update in the Archive.');
433 fnd_file.put_line(fnd_file.LOG,'Hence, skipping this assignment as there is no archive data for this assignment for this period.');
434 l_flag := 'N';
435 end if;
436 --l_ovn := l_archive_details_update.object_version_number;
437 CLOSE c_archive_details_update;
438 END IF;
439
440 l_postponement_date :=fnd_date.canonical_to_date(p_pension_entry_values.postponement_end_date);
441 l_postponement_type :=p_pension_entry_values.postponement_type;
442 ----------------------------------------------------------------------------------------------------
443 IF P_LETTER = 'PYGBPAEIG1' and l_flag ='Y' THEN
444 --Letter for the Automatically Enrolled
445 fnd_file.put_line(fnd_file.LOG,'Letter for the Automatically Enrolled');
446 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.qualifying_scheme_Name: '||p_pension_entry_values.qualifying_scheme_Name);
447 fnd_file.put_line(fnd_file.LOG,'P_PAYROLL.: '||P_PAYROLL);
448
449
450 OPEN c_comm_details_qual(P_PAYROLL,p_pension_entry_values.qualifying_scheme_name);
451 fetch c_comm_details_qual into l_signatory_name_pqs,l_contact_mail_pqs,l_instruction_pqs,l_more_info_pqs;
452 close c_comm_details_qual;
453
454 if l_signatory_name_pqs is not null then
455 l_signatory_name := l_signatory_name_pqs;
456 end if;
457 if l_contact_mail_pqs is not null then
458 l_contact_mail_org := l_contact_mail_pqs;
459 end if;
460 if l_instruction_pqs is not null then
461 l_instruction := l_instruction_pqs;
462 end if;
463 if l_more_info_pqs is not null then
464 l_more_info := l_more_info_pqs;
465 end if;
466
467 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.auto_enrollment_date::'||p_pension_entry_values.auto_enrollment_date);
468 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.qualifying_scheme_exists::'||p_pension_entry_values.qualifying_scheme_exists);
469 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.pension_classification::'||p_pension_entry_values.pension_classification);
470 fnd_file.put_line(fnd_file.LOG,'l_prp_start_date::'||l_prp_start_date);
471 fnd_file.put_line(fnd_file.LOG,'l_prp_end_date::'||l_prp_end_date);
472
473 IF p_pension_entry_values.auto_enrollment_date between l_prp_start_date and l_prp_end_date and
474 NVL(p_pension_entry_values.qualifying_scheme_exists,'N') ='Y'
475 and p_pension_entry_values.pension_classification = 'Eligible Job Holder' THEN
476 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||p_assignment_id);
477
478 fnd_file.put_line(fnd_file.LOG,'Printing XML for the assignment id - '||p_assignment_id);
479 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.qualifying_scheme_name : '||l_input_value_ids.qualifying_scheme_name);
480 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.auto_enrollment_date : '||l_input_value_ids.auto_enrollment_date);
481 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.pension_classification : '||l_input_value_ids.pension_classification);
482 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.opt_out_period_end_date : '||l_input_value_ids.opt_out_period_end_date);
483 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.total_earning_prp : '||l_input_value_ids.total_earning_prp);
484 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.postponement_type : '||l_input_value_ids.postponement_type);
488 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.elig_jh_date : '||l_input_value_ids.elig_jh_date);
485 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.postponement_end_date : '||l_input_value_ids.postponement_end_date);
486 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.qualifying_scheme_exists : '||l_input_value_ids.qualifying_scheme_exists);
487 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.opt_in_date : '||l_input_value_ids.opt_in_date);
489 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.opt_out_date : '||l_input_value_ids.opt_out_date);
490
491 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.qualifying_scheme_name : '||p_pension_entry_values.qualifying_scheme_name);
492 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.auto_enrollment_date : '||p_pension_entry_values.auto_enrollment_date);
493 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.pension_classification : '||p_pension_entry_values.pension_classification);
494 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.opt_out_period_end_date : '||p_pension_entry_values.opt_out_period_end_date);
495 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.total_earning_prp : '||p_pension_entry_values.total_earning_prp);
496 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.postponement_type : '||p_pension_entry_values.postponement_type);
497 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.postponement_end_date : '||p_pension_entry_values.postponement_end_date);
498 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.qualifying_scheme_exists : '||p_pension_entry_values.qualifying_scheme_exists);
499 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.opt_in_date : '||p_pension_entry_values.opt_in_date);
500 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.elig_jh_date : '||p_pension_entry_values.elig_jh_date);
501 fnd_file.put_line(fnd_file.LOG,'p_pension_entry_values.opt_out_date : '||p_pension_entry_values.opt_out_date);
502
503
504
505 l_string := l_string ||'<LETTERS>'||EOL;
506 l_string := l_string ||'<ADDRESS>'||EOL;
507 l_string := l_string ||l_employee_name||EOL||EOL;
508 l_string := l_string ||l_address.addr1||EOL;
509 l_string := l_string ||l_address.addr2||EOL;
510 l_string := l_string ||l_address.addr3||EOL;
511 l_string := l_string ||l_address.post_code||EOL;
512 l_string := l_string ||l_address.addr4||EOL;
513 l_string := l_string ||l_address.country||EOL;
514 l_string := l_string ||'</ADDRESS>'||EOL;
515 l_string := l_string ||'<LETTER>1</LETTER>'||EOL;
516 l_string := l_string || '<EMPLOYER_NAME>'|| l_employer_name||'</EMPLOYER_NAME>' ||EOL;
517 l_string := l_string || '<EMPLOYEE_NAME>'|| l_employee_name||'</EMPLOYEE_NAME>' ||EOL;
518 l_string := l_string || '<EARNING>'|| l_earning||'</EARNING>' ||EOL;
519 l_string := l_string || '<EARNING_LOWER>'|| l_earning_lower||'</EARNING_LOWER>' ||EOL;
520 l_string := l_string || '<EMAIL_ADDR>'|| l_not||'</EMAIL_ADDR>' ||EOL;
521 l_string := l_string || '<NOT>'|| l_not||'</NOT>' ||EOL;
522 l_string := l_string || '<EMP_AGE>'|| l_pension_age||'</EMP_AGE>' ||EOL;
523 l_string := l_string || '<PERIOD>'|| l_period||'</PERIOD>' ||EOL;
524 l_string := l_string || '<DEFERRAL_DATE>'|| p_pension_entry_values.postponement_end_date||'</DEFERRAL_DATE>' ||EOL;
525 l_string := l_string || '<AUTO_ENROL_DATE>'|| l_prp_start_date||'</AUTO_ENROL_DATE>' ||EOL;
526 l_string := l_string || '<MORE_INFO>'|| l_more_info||'</MORE_INFO>' ||EOL;
527 l_string := l_string || '<CONTACT_MAIL_ORG>'|| l_contact_mail_org||'</CONTACT_MAIL_ORG>' ||EOL;
528 l_string := l_string || '<INSTRUCTION_DETAILS>'|| l_instruction||'</INSTRUCTION_DETAILS>' ||EOL;
529 l_string := l_string || '<SIGNATORY_NAME>'|| l_signatory_name||'</SIGNATORY_NAME>' ||EOL;
530 l_string := l_string ||'</LETTERS>'||EOL;
531 p_string := p_string || l_string;
532
533 update_archive (l_archive_details_update,p_period,p_issue_date
534 ,l_postponement_date
535 ,l_postponement_type
536 ,p_letter);
537
538 FOR entries IN C_ELEMENT_ENTRIES
539 LOOP
540 IF (p_pension_entry_values.opt_out_period_end_date) IS NOT NULL THEN
541 l_opt_out_new := greatest ((p_issue_date+30), fnd_date.canonical_to_date(p_pension_entry_values.opt_out_period_end_date));
542 ELSE
543 fnd_file.put_line(fnd_file.LOG,'No Opt-Out End Date found for this assignment');
544 END IF;
545 IF p_pension_entry_values.qualifying_scheme_exists = 'Y'
546 then l_scheme_exists := 'Yes';
547 ELSE l_scheme_exists := 'No';
548 END IF;
549 IF p_pension_entry_values.main_entry = 'Y'
550 then l_main_entry := 'Yes';
551 ELSE l_main_entry := 'No';
552 END IF;
553 IF p_pension_entry_values.agg_earnings = 'Y'
554 then l_agg_earnings := 'Yes';
555 ELSE l_agg_earnings := 'No';
556 END IF;
557
558
559 HR_ENTRY_API.UPDATE_ELEMENT_ENTRY (P_DT_UPDATE_MODE =>'UPDATE', P_SESSION_DATE => entries.START_DATE,
560 P_CHECK_FOR_UPDATE =>'N' , P_CREATOR_TYPE => 'F', P_ELEMENT_ENTRY_ID => entries.ELEMENT_ENTRY_ID ,
561 P_INPUT_VALUE_ID1 => l_input_value_ids.qualifying_scheme_name, P_ENTRY_VALUE1 => p_pension_entry_values.qualifying_scheme_name ,
562 P_INPUT_VALUE_ID2 => l_input_value_ids.auto_enrollment_date, P_ENTRY_VALUE2 => p_pension_entry_values.auto_enrollment_date,--fnd_date.canonical_to_date(p_pension_entry_values.auto_enrollment_date ),
563 P_INPUT_VALUE_ID3 => l_input_value_ids.pension_classification, P_ENTRY_VALUE3 => p_pension_entry_values.pension_classification ,
564 P_INPUT_VALUE_ID4 => l_input_value_ids.opt_out_period_end_date, P_ENTRY_VALUE4 => l_opt_out_new,--Date
565 P_INPUT_VALUE_ID5 => l_input_value_ids.total_earning_prp, P_ENTRY_VALUE5 => p_pension_entry_values.total_earning_prp,
566 P_INPUT_VALUE_ID6 => l_input_value_ids.postponement_type, P_ENTRY_VALUE6 => p_pension_entry_values.postponement_type,
567 P_INPUT_VALUE_ID7 => l_input_value_ids.postponement_end_date, P_ENTRY_VALUE7 => p_pension_entry_values.postponement_end_date,--fnd_date.canonical_to_date(p_pension_entry_values.postponement_end_date),
568 P_INPUT_VALUE_ID8 => l_input_value_ids.qualifying_scheme_exists, P_ENTRY_VALUE8 => l_scheme_exists,
572 P_INPUT_VALUE_ID12 => l_input_value_ids.main_entry_id, P_ENTRY_VALUE12 => l_main_entry,
569 P_INPUT_VALUE_ID9 => l_input_value_ids.opt_in_date, P_ENTRY_VALUE9 => p_pension_entry_values.opt_in_date,--fnd_date.canonical_to_date(p_pension_entry_values.opt_in_date),
570 P_INPUT_VALUE_ID10 => l_input_value_ids.elig_jh_date, P_ENTRY_VALUE10 => p_pension_entry_values.elig_jh_date,--fnd_date.canonical_to_date(p_pension_entry_values.elig_jh_date),
571 P_INPUT_VALUE_ID11 => l_input_value_ids.opt_out_date, P_ENTRY_VALUE11 => fnd_date.canonical_to_date(p_pension_entry_values.opt_out_date),
573 P_INPUT_VALUE_ID13 => l_input_value_ids.agg_earnings_pensions_id,P_ENTRY_VALUE13 => l_agg_earnings);
574 END LOOP;
575
576 END IF;
577 END IF;
578 ----------------------------------------------------------------------------------------------------
579 ----------------------------------------------------------------------------------------------------
580 IF P_LETTER = 'PYGBPAEIG2' and l_flag ='Y' THEN
581 --Letter for the Non Eligible Job Holders
582 fnd_file.put_line(fnd_file.LOG,'Letter for the Non Eligible Job Holders');
583
584 OPEN c_comm_details_qual(P_PAYROLL,p_pension_entry_values.qualifying_scheme_name);
585 fetch c_comm_details_qual into l_signatory_name_pqs,l_contact_mail_pqs,l_instruction_pqs,l_more_info_pqs;
586 close c_comm_details_qual;
587
588 if l_signatory_name_pqs is not null then
589 l_signatory_name := l_signatory_name_pqs;
590 end if;
591 if l_contact_mail_pqs is not null then
592 l_contact_mail_org := l_contact_mail_pqs;
593 end if;
594 if l_instruction_pqs is not null then
595 l_instruction := l_instruction_pqs;
596 end if;
597 if l_more_info_pqs is not null then
598 l_more_info := l_more_info_pqs;
599 end if;
600
601 IF NVL(p_pension_entry_values.postponement_end_date,l_prp_start_date) <l_prp_end_date and NVL(p_pension_entry_values.qualifying_scheme_exists,'N') ='N'
602 and p_pension_entry_values.pension_classification = 'Non Eligible Job Holder' THEN
603 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||p_assignment_id);
604
605 fnd_file.put_line(fnd_file.LOG,'Printing XML for the assignment id - '||p_assignment_id);
606
607 l_string := l_string ||'<LETTERS>'||EOL;
608 l_string := l_string ||'<ADDRESS>'||EOL;
609 l_string := l_string ||l_employee_name||EOL||EOL;
610 l_string := l_string ||l_address.addr1||EOL;
611 l_string := l_string ||l_address.addr2||EOL;
612 l_string := l_string ||l_address.addr3||EOL;
613 l_string := l_string ||l_address.post_code||EOL;
614 l_string := l_string ||l_address.addr4||EOL;
615 l_string := l_string ||l_address.country||EOL;
616 l_string := l_string ||'</ADDRESS>'||EOL;
617 l_string := l_string ||'<LETTER>2</LETTER>'||EOL;
618 l_string := l_string || '<EMPLOYER_NAME>'|| l_employer_name||'</EMPLOYER_NAME>' ||EOL;
619 l_string := l_string || '<EMPLOYEE_NAME>'|| l_employee_name||'</EMPLOYEE_NAME>' ||EOL;
620 l_string := l_string || '<EARNING>'|| l_earning||'</EARNING>' ||EOL;
621 l_string := l_string || '<EARNING_LOWER>'|| l_earning_lower||'</EARNING_LOWER>' ||EOL;
622 l_string := l_string || '<EMAIL_ADDR>'|| l_not||'</EMAIL_ADDR>' ||EOL;
623 l_string := l_string || '<NOT>'|| l_not||'</NOT>' ||EOL;
624 l_string := l_string || '<EMP_AGE>'|| l_pension_age||'</EMP_AGE>' ||EOL;
625 l_string := l_string || '<PERIOD>'|| l_period||'</PERIOD>' ||EOL;
626 l_string := l_string || '<DEFERRAL_DATE>'|| p_pension_entry_values.postponement_end_date||'</DEFERRAL_DATE>' ||EOL;
627 l_string := l_string || '<AUTO_ENROL_DATE>'|| l_prp_start_date||'</AUTO_ENROL_DATE>' ||EOL;
628 l_string := l_string || '<MORE_INFO>'|| l_more_info||'</MORE_INFO>' ||EOL;
629 l_string := l_string || '<CONTACT_MAIL_ORG>'|| l_contact_mail_org||'</CONTACT_MAIL_ORG>' ||EOL;
630 l_string := l_string || '<INSTRUCTION_DETAILS>'|| l_instruction||'</INSTRUCTION_DETAILS>' ||EOL;
631 l_string := l_string || '<SIGNATORY_NAME>'|| l_signatory_name||'</SIGNATORY_NAME>' ||EOL;
632 l_string := l_string ||'</LETTERS>'||EOL;
633
634 p_string := p_string || l_string;
635
636 update_archive (l_archive_details_update,p_period,p_issue_date
637 ,l_postponement_date
638 ,l_postponement_type
639 ,p_letter);
640
641 END IF;
642 END IF;
643 ----------------------------------------------------------------------------------------------------
644 ----------------------------------------------------------------------------------------------------
645 IF P_LETTER = 'PYGBPAEIG3' and l_flag ='Y' THEN
646 --Letter for the Workers
647 fnd_file.put_line(fnd_file.LOG,'Letter for the Workers');
648
649
650 OPEN c_comm_details_qual(P_PAYROLL,p_pension_entry_values.qualifying_scheme_name);
651 fetch c_comm_details_qual into l_signatory_name_pqs,l_contact_mail_pqs,l_instruction_pqs,l_more_info_pqs;
652 close c_comm_details_qual;
653
654 if l_signatory_name_pqs is not null then
655 l_signatory_name := l_signatory_name_pqs;
656 end if;
657 if l_contact_mail_pqs is not null then
658 l_contact_mail_org := l_contact_mail_pqs;
659 end if;
660 if l_instruction_pqs is not null then
661 l_instruction := l_instruction_pqs;
662 end if;
663 if l_more_info_pqs is not null then
664 l_more_info := l_more_info_pqs;
665 end if;
666 IF NVL(p_pension_entry_values.postponement_end_date,l_prp_start_date) <l_prp_end_date
667 and NVL(p_pension_entry_values.qualifying_scheme_exists,'N') ='N'
668 and p_pension_entry_values.pension_classification = 'Worker' THEN
669 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||p_assignment_id);
670
671 fnd_file.put_line(fnd_file.LOG,'Printing XML for the assignment id - '||p_assignment_id);
672
673 l_string := l_string ||'<LETTERS>'||EOL;
674 l_string := l_string ||'<ADDRESS>'||EOL;
675 l_string := l_string ||l_employee_name||EOL||EOL;
679 l_string := l_string ||l_address.post_code||EOL;
676 l_string := l_string ||l_address.addr1||EOL;
677 l_string := l_string ||l_address.addr2||EOL;
678 l_string := l_string ||l_address.addr3||EOL;
680 l_string := l_string ||l_address.addr4||EOL;
681 l_string := l_string ||l_address.country||EOL;
682 l_string := l_string ||'</ADDRESS>'||EOL;
683 l_string := l_string ||'<LETTER>3</LETTER>'||EOL;
684 l_string := l_string || '<EMPLOYER_NAME>'|| l_employer_name||'</EMPLOYER_NAME>' ||EOL;
685 l_string := l_string || '<EMPLOYEE_NAME>'|| l_employee_name||'</EMPLOYEE_NAME>' ||EOL;
686 l_string := l_string || '<EARNING>'|| l_earning||'</EARNING>' ||EOL;
687 l_string := l_string || '<EARNING_LOWER>'|| l_earning_lower||'</EARNING_LOWER>' ||EOL;
688 l_string := l_string || '<EMAIL_ADDR>'|| l_not||'</EMAIL_ADDR>' ||EOL;
689 l_string := l_string || '<NOT>'|| l_not||'</NOT>' ||EOL;
690 l_string := l_string || '<EMP_AGE>'|| l_pension_age||'</EMP_AGE>' ||EOL;
691 l_string := l_string || '<PERIOD>'|| l_period||'</PERIOD>' ||EOL;
692 l_string := l_string || '<DEFERRAL_DATE>'|| p_pension_entry_values.postponement_end_date||'</DEFERRAL_DATE>' ||EOL;
693 l_string := l_string || '<AUTO_ENROL_DATE>'|| l_prp_start_date||'</AUTO_ENROL_DATE>' ||EOL;
694 l_string := l_string || '<MORE_INFO>'|| l_more_info||'</MORE_INFO>' ||EOL;
695 l_string := l_string || '<CONTACT_MAIL_ORG>'|| l_contact_mail_org||'</CONTACT_MAIL_ORG>' ||EOL;
696 l_string := l_string || '<INSTRUCTION_DETAILS>'|| l_instruction||'</INSTRUCTION_DETAILS>' ||EOL;
697 l_string := l_string || '<SIGNATORY_NAME>'|| l_signatory_name||'</SIGNATORY_NAME>' ||EOL;
698 l_string := l_string ||'</LETTERS>'||EOL;
699 p_string := p_string || l_string;
700
701 update_archive (l_archive_details_update,p_period,p_issue_date
702 ,l_postponement_date
703 ,l_postponement_type
704 ,p_letter);
705
706 END IF;
707 END IF;
708 ----------------------------------------------------------------------------------------------------
709 ----------------------------------------------------------------------------------------------------
710 IF P_LETTER = 'PYGBPAEIG4' and l_flag ='Y' THEN
711 --Letter for who are already in Qualifying Pension Scheme
712 fnd_file.put_line(fnd_file.LOG,'Letter for who are already in Qualifying Pension Scheme');
713
714 OPEN c_comm_details_qual(P_PAYROLL,p_pension_entry_values.qualifying_scheme_name);
715 fetch c_comm_details_qual into l_signatory_name_pqs,l_contact_mail_pqs,l_instruction_pqs,l_more_info_pqs;
716 close c_comm_details_qual;
717
718 if l_signatory_name_pqs is not null then
719 l_signatory_name := l_signatory_name_pqs;
720 end if;
721 if l_contact_mail_pqs is not null then
722 l_contact_mail_org := l_contact_mail_pqs;
723 end if;
724 if l_instruction_pqs is not null then
725 l_instruction := l_instruction_pqs;
726 end if;
727 if l_more_info_pqs is not null then
728 l_more_info := l_more_info_pqs;
729 end if;
730
731 IF p_pension_entry_values.auto_enrollment_date IS NULL
732 and NVL(p_pension_entry_values.qualifying_scheme_exists,'N') ='Y' THEN
733 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||p_assignment_id);
734
735 fnd_file.put_line(fnd_file.LOG,'Printing XML for the assignment id - '||p_assignment_id);
736
737 l_string := l_string ||'<LETTERS>'||EOL;
738 l_string := l_string ||'<ADDRESS>'||EOL;
739 l_string := l_string ||l_employee_name||EOL||EOL;
740 l_string := l_string ||l_address.addr1||EOL;
741 l_string := l_string ||l_address.addr2||EOL;
742 l_string := l_string ||l_address.addr3||EOL;
743 l_string := l_string ||l_address.post_code||EOL;
744 l_string := l_string ||l_address.addr4||EOL;
745 l_string := l_string ||l_address.country||EOL;
746 l_string := l_string ||'</ADDRESS>'||EOL;
747 l_string := l_string ||'<LETTER>4</LETTER>'||EOL;
748 l_string := l_string || '<EMPLOYER_NAME>'|| l_employer_name||'</EMPLOYER_NAME>' ||EOL;
749 l_string := l_string || '<EMPLOYEE_NAME>'|| l_employee_name||'</EMPLOYEE_NAME>' ||EOL;
750 l_string := l_string || '<EARNING>'|| l_earning||'</EARNING>' ||EOL;
751 l_string := l_string || '<EARNING_LOWER>'|| l_earning_lower||'</EARNING_LOWER>' ||EOL;
752 l_string := l_string || '<EMAIL_ADDR>'|| l_not||'</EMAIL_ADDR>' ||EOL;
753 l_string := l_string || '<NOT>'|| l_not||'</NOT>' ||EOL;
754 l_string := l_string || '<EMP_AGE>'|| l_pension_age||'</EMP_AGE>' ||EOL;
755 l_string := l_string || '<PERIOD>'|| l_period||'</PERIOD>' ||EOL;
756 l_string := l_string || '<DEFERRAL_DATE>'|| p_pension_entry_values.postponement_end_date||'</DEFERRAL_DATE>' ||EOL;
757 l_string := l_string || '<AUTO_ENROL_DATE>'|| l_prp_start_date||'</AUTO_ENROL_DATE>' ||EOL;
758 l_string := l_string || '<MORE_INFO>'|| l_more_info||'</MORE_INFO>' ||EOL;
759 l_string := l_string || '<CONTACT_MAIL_ORG>'|| l_contact_mail_org||'</CONTACT_MAIL_ORG>' ||EOL;
760 l_string := l_string || '<INSTRUCTION_DETAILS>'|| l_instruction||'</INSTRUCTION_DETAILS>' ||EOL;
761 l_string := l_string || '<SIGNATORY_NAME>'|| l_signatory_name||'</SIGNATORY_NAME>' ||EOL;
762 l_string := l_string ||'</LETTERS>'||EOL;
763
764 p_string := p_string || l_string;
765
766 update_archive (l_archive_details_update,p_period,p_issue_date
767 ,l_postponement_date
768 ,l_postponement_type
769 ,p_letter);
770
771 END IF;
772 END IF;
773 ----------------------------------------------------------------------------------------------------
774 ----------------------------------------------------------------------------------------------------
775 IF P_LETTER = 'PYGBPAEIG5' and l_flag ='Y' THEN
776 --General Letter of Postponement Redundant
777 fnd_file.put_line(fnd_file.LOG,'General Letter of Postponement (Redundant)');
778
782
779 OPEN c_comm_details_qual(P_PAYROLL,p_pension_entry_values.qualifying_scheme_name);
780 fetch c_comm_details_qual into l_signatory_name_pqs,l_contact_mail_pqs,l_instruction_pqs,l_more_info_pqs;
781 close c_comm_details_qual;
783 if l_signatory_name_pqs is not null then
784 l_signatory_name := l_signatory_name_pqs;
785 end if;
786 if l_contact_mail_pqs is not null then
787 l_contact_mail_org := l_contact_mail_pqs;
788 end if;
789 if l_instruction_pqs is not null then
790 l_instruction := l_instruction_pqs;
791 end if;
792 if l_more_info_pqs is not null then
793 l_more_info := l_more_info_pqs;
794 end if;
795
796
797 IF p_pension_entry_values.postponement_end_date >=l_prp_end_date
798 and p_pension_entry_values.auto_enrollment_date IS NULL THEN
799 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||p_assignment_id);
800
801 fnd_file.put_line(fnd_file.LOG,'Printing XML for the assignment id - '||p_assignment_id);
802 l_string := l_string ||'<LETTERS>'||EOL;
803 l_string := l_string ||'<ADDRESS>'||EOL;
804 l_string := l_string ||l_employee_name||EOL||EOL;
805 l_string := l_string ||l_address.addr1||EOL;
806 l_string := l_string ||l_address.addr2||EOL;
807 l_string := l_string ||l_address.addr3||EOL;
808 l_string := l_string ||l_address.post_code||EOL;
809 l_string := l_string ||l_address.addr4||EOL;
810 l_string := l_string ||l_address.country||EOL;
811 l_string := l_string ||'</ADDRESS>'||EOL;
812 l_string := l_string ||'<LETTER>5</LETTER>'||EOL;
813 l_string := l_string || '<EMPLOYER_NAME>'|| l_employer_name||'</EMPLOYER_NAME>' ||EOL;
814 l_string := l_string || '<EMPLOYEE_NAME>'|| l_employee_name||'</EMPLOYEE_NAME>' ||EOL;
815 l_string := l_string || '<EARNING>'|| l_earning||'</EARNING>' ||EOL;
816 l_string := l_string || '<EARNING_LOWER>'|| l_earning_lower||'</EARNING_LOWER>' ||EOL;
817 l_string := l_string || '<EMAIL_ADDR>'|| l_not||'</EMAIL_ADDR>' ||EOL;
818 l_string := l_string || '<NOT>'|| l_not||'</NOT>' ||EOL;
819 l_string := l_string || '<EMP_AGE>'|| l_pension_age||'</EMP_AGE>' ||EOL;
820 l_string := l_string || '<PERIOD>'|| l_period||'</PERIOD>' ||EOL;
821 l_string := l_string || '<DEFERRAL_DATE>'|| p_pension_entry_values.postponement_end_date||'</DEFERRAL_DATE>' ||EOL;
822 l_string := l_string || '<AUTO_ENROL_DATE>'|| l_prp_start_date||'</AUTO_ENROL_DATE>' ||EOL;
823 l_string := l_string || '<MORE_INFO>'|| l_more_info||'</MORE_INFO>' ||EOL;
824 l_string := l_string || '<CONTACT_MAIL_ORG>'|| l_contact_mail_org||'</CONTACT_MAIL_ORG>' ||EOL;
825 l_string := l_string || '<INSTRUCTION_DETAILS>'|| l_instruction||'</INSTRUCTION_DETAILS>' ||EOL;
826 l_string := l_string || '<SIGNATORY_NAME>'|| l_signatory_name||'</SIGNATORY_NAME>' ||EOL;
827 l_string := l_string ||'</LETTERS>'||EOL;
828
829 p_string := p_string || l_string;
830
831 update_archive (l_archive_details_update,p_period,p_issue_date
832 ,l_postponement_date
833 ,l_postponement_type
834 ,p_letter);
835
836 END IF;
837 END IF;
838 ----------------------------------------------------------------------------------------------------
839 ----------------------------------------------------------------------------------------------------
840 IF P_LETTER = 'PYGBPAEIG6' and l_flag ='Y' THEN
841 --General Letter of Postponement
842 fnd_file.put_line(fnd_file.LOG,'General Letter of Postponement');
843
844 OPEN c_comm_details_qual(P_PAYROLL,p_pension_entry_values.qualifying_scheme_name);
845 fetch c_comm_details_qual into l_signatory_name_pqs,l_contact_mail_pqs,l_instruction_pqs,l_more_info_pqs;
846 close c_comm_details_qual;
847
848 if l_signatory_name_pqs is not null then
849 l_signatory_name := l_signatory_name_pqs;
850 end if;
851 if l_contact_mail_pqs is not null then
852 l_contact_mail_org := l_contact_mail_pqs;
853 end if;
854 if l_instruction_pqs is not null then
855 l_instruction := l_instruction_pqs;
856 end if;
857 if l_more_info_pqs is not null then
858 l_more_info := l_more_info_pqs;
859 end if;
860
861 IF p_pension_entry_values.postponement_end_date >=l_prp_end_date
862 and p_pension_entry_values.auto_enrollment_date IS NULL THEN
863 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||p_assignment_id);
864
865 fnd_file.put_line(fnd_file.LOG,'Printing XML for the assignment id - '||p_assignment_id);
866 l_string := l_string ||'<LETTERS>'||EOL;
867 l_string := l_string ||'<ADDRESS>'||EOL;
868 l_string := l_string ||l_employee_name||EOL||EOL;
874 l_string := l_string ||l_address.country||EOL;
869 l_string := l_string ||l_address.addr1||EOL;
870 l_string := l_string ||l_address.addr2||EOL;
871 l_string := l_string ||l_address.addr3||EOL;
872 l_string := l_string ||l_address.post_code||EOL;
873 l_string := l_string ||l_address.addr4||EOL;
875 l_string := l_string ||'</ADDRESS>'||EOL;
876 l_string := l_string ||'<LETTER>6</LETTER>'||EOL;
877 l_string := l_string || '<EMPLOYER_NAME>'|| l_employer_name||'</EMPLOYER_NAME>' ||EOL;
878 l_string := l_string || '<EMPLOYEE_NAME>'|| l_employee_name||'</EMPLOYEE_NAME>' ||EOL;
879 l_string := l_string || '<EARNING>'|| l_earning||'</EARNING>' ||EOL;
880 l_string := l_string || '<EARNING_LOWER>'|| l_earning_lower||'</EARNING_LOWER>' ||EOL;
881 l_string := l_string || '<EMAIL_ADDR>'|| l_not||'</EMAIL_ADDR>' ||EOL;
882 l_string := l_string || '<NOT>'|| l_not||'</NOT>' ||EOL;
883 l_string := l_string || '<EMP_AGE>'|| l_pension_age||'</EMP_AGE>' ||EOL;
884 l_string := l_string || '<PERIOD>'|| l_period||'</PERIOD>' ||EOL;
885 l_string := l_string || '<DEFERRAL_DATE>'|| p_pension_entry_values.postponement_end_date||'</DEFERRAL_DATE>' ||EOL;
886 l_string := l_string || '<AUTO_ENROL_DATE>'|| l_prp_start_date||'</AUTO_ENROL_DATE>' ||EOL;
887 l_string := l_string || '<MORE_INFO>'|| l_more_info||'</MORE_INFO>' ||EOL;
888 l_string := l_string || '<CONTACT_MAIL_ORG>'|| l_contact_mail_org||'</CONTACT_MAIL_ORG>' ||EOL;
889 l_string := l_string || '<INSTRUCTION_DETAILS>'|| l_instruction||'</INSTRUCTION_DETAILS>' ||EOL;
890 l_string := l_string || '<SIGNATORY_NAME>'|| l_signatory_name||'</SIGNATORY_NAME>' ||EOL;
891 l_string := l_string ||'</LETTERS>'||EOL;
892
893 p_string := p_string || l_string;
894
895 update_archive (l_archive_details_update,p_period,p_issue_date
896 ,l_postponement_date
897 ,l_postponement_type
898 ,p_letter);
899
900 END IF;
901 END IF;
902 ----------------------------------------------------------------------------------------------------
903 ----------------------------------------------------------------------------------------------------
904 IF P_LETTER = 'PYGBPAEIG7' and l_flag ='Y' THEN
905 --Letter of DB Scheme Postponement
906 fnd_file.put_line(fnd_file.LOG,'Letter of DB Scheme Postponement');
907
908
909 OPEN c_comm_details_qual(P_PAYROLL,p_pension_entry_values.qualifying_scheme_name);
910 fetch c_comm_details_qual into l_signatory_name_pqs,l_contact_mail_pqs,l_instruction_pqs,l_more_info_pqs;
911 close c_comm_details_qual;
912
913 if l_signatory_name_pqs is not null then
914 l_signatory_name := l_signatory_name_pqs;
915 end if;
916 if l_contact_mail_pqs is not null then
917 l_contact_mail_org := l_contact_mail_pqs;
918 end if;
919 if l_instruction_pqs is not null then
920 l_instruction := l_instruction_pqs;
921 end if;
922 if l_more_info_pqs is not null then
923 l_more_info := l_more_info_pqs;
924 end if;
925 IF p_pension_entry_values.postponement_end_date >= l_prp_end_date
926 and p_pension_entry_values.postponement_type = 'DB Scheme Postponement' THEN
927 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||p_assignment_id);
928
929 fnd_file.put_line(fnd_file.LOG,'Printing XML for the assignment id - '||p_assignment_id);
930
931 l_string := l_string ||'<LETTERS>'||EOL;
932 l_string := l_string ||'<ADDRESS>'||EOL;
933 l_string := l_string ||l_employee_name||EOL||EOL;
934 l_string := l_string ||l_address.addr1||EOL;
935 l_string := l_string ||l_address.addr2||EOL;
936 l_string := l_string ||l_address.addr3||EOL;
937 l_string := l_string ||l_address.post_code||EOL;
938 l_string := l_string ||l_address.addr4||EOL;
939 l_string := l_string ||l_address.country||EOL;
940 l_string := l_string ||'</ADDRESS>'||EOL;
941 l_string := l_string ||'<LETTER>7</LETTER>'||EOL;
942 l_string := l_string || '<EMPLOYER_NAME>'|| l_employer_name||'</EMPLOYER_NAME>' ||EOL;
943 l_string := l_string || '<EMPLOYEE_NAME>'|| l_employee_name||'</EMPLOYEE_NAME>' ||EOL;
944 l_string := l_string || '<EARNING>'|| l_earning||'</EARNING>' ||EOL;
945 l_string := l_string || '<EARNING_LOWER>'|| l_earning_lower||'</EARNING_LOWER>' ||EOL;
946 l_string := l_string || '<EMAIL_ADDR>'|| l_not||'</EMAIL_ADDR>' ||EOL;
947 l_string := l_string || '<NOT>'|| l_not||'</NOT>' ||EOL;
948 l_string := l_string || '<EMP_AGE>'|| l_pension_age||'</EMP_AGE>' ||EOL;
949 l_string := l_string || '<PERIOD>'|| l_period||'</PERIOD>' ||EOL;
950 l_string := l_string || '<DEFERRAL_DATE>'|| p_pension_entry_values.postponement_end_date||'</DEFERRAL_DATE>' ||EOL;
951 l_string := l_string || '<AUTO_ENROL_DATE>'|| l_prp_start_date||'</AUTO_ENROL_DATE>' ||EOL;
952 l_string := l_string || '<MORE_INFO>'|| l_more_info||'</MORE_INFO>' ||EOL;
953 l_string := l_string || '<CONTACT_MAIL_ORG>'|| l_contact_mail_org||'</CONTACT_MAIL_ORG>' ||EOL;
954 l_string := l_string || '<INSTRUCTION_DETAILS>'|| l_instruction||'</INSTRUCTION_DETAILS>' ||EOL;
955 l_string := l_string || '<SIGNATORY_NAME>'|| l_signatory_name||'</SIGNATORY_NAME>' ||EOL;
956 l_string := l_string ||'</LETTERS>'||EOL;
957
958 p_string := p_string || l_string;
959
960 update_archive (l_archive_details_update,p_period,p_issue_date
961 ,l_postponement_date
962 ,l_postponement_type
963 ,p_letter);
964
965 END IF;
966 END IF;
967 ----------------------------------------------------------------------------------------------------
968 ----------------------------------------------------------------------------------------------------
969 IF P_LETTER = 'PYGBPAEIG8' and l_flag ='Y' THEN
970 --Letter of Postponement for the Non Eligible Job Holders
971 fnd_file.put_line(fnd_file.LOG,'Letter of Postponement for the Non Eligible Job Holders');
972
973
974 OPEN c_comm_details_qual(P_PAYROLL,p_pension_entry_values.qualifying_scheme_name);
975 fetch c_comm_details_qual into l_signatory_name_pqs,l_contact_mail_pqs,l_instruction_pqs,l_more_info_pqs;
979 l_signatory_name := l_signatory_name_pqs;
976 close c_comm_details_qual;
977
978 if l_signatory_name_pqs is not null then
980 end if;
981 if l_contact_mail_pqs is not null then
982 l_contact_mail_org := l_contact_mail_pqs;
983 end if;
984 if l_instruction_pqs is not null then
985 l_instruction := l_instruction_pqs;
986 end if;
987 if l_more_info_pqs is not null then
988 l_more_info := l_more_info_pqs;
989 end if;
990 IF p_pension_entry_values.postponement_end_date >= l_prp_end_date
991 and NVL(p_pension_entry_values.postponement_type,'DB Scheme Postponement') <>'DB Scheme Postponement'
992 and p_pension_entry_values.pension_classification = 'Non Eligible Job Holder' THEN
996 l_string := l_string ||'<LETTERS>'||EOL;
993 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||p_assignment_id);
994
995 fnd_file.put_line(fnd_file.LOG,'Printing XML for the assignment id - '||p_assignment_id);
997 l_string := l_string ||'<ADDRESS>'||EOL;
998 l_string := l_string ||l_employee_name||EOL||EOL;
999 l_string := l_string ||l_address.addr1||EOL;
1000 l_string := l_string ||l_address.addr2||EOL;
1001 l_string := l_string ||l_address.addr3||EOL;
1002 l_string := l_string ||l_address.post_code||EOL;
1003 l_string := l_string ||l_address.addr4||EOL;
1004 l_string := l_string ||l_address.country||EOL;
1005 l_string := l_string ||'</ADDRESS>'||EOL;
1006 l_string := l_string ||'<LETTER>8</LETTER>'||EOL;
1007 l_string := l_string || '<EMPLOYER_NAME>'|| l_employer_name||'</EMPLOYER_NAME>' ||EOL;
1008 l_string := l_string || '<EMPLOYEE_NAME>'|| l_employee_name||'</EMPLOYEE_NAME>' ||EOL;
1009 l_string := l_string || '<EARNING>'|| l_earning||'</EARNING>' ||EOL;
1010 l_string := l_string || '<EARNING_LOWER>'|| l_earning_lower||'</EARNING_LOWER>' ||EOL;
1011 l_string := l_string || '<EMAIL_ADDR>'|| l_not||'</EMAIL_ADDR>' ||EOL;
1012 l_string := l_string || '<NOT>'|| l_not||'</NOT>' ||EOL;
1013 l_string := l_string || '<EMP_AGE>'|| l_pension_age||'</EMP_AGE>' ||EOL;
1014 l_string := l_string || '<PERIOD>'|| l_period||'</PERIOD>' ||EOL;
1015 l_string := l_string || '<DEFERRAL_DATE>'|| p_pension_entry_values.postponement_end_date||'</DEFERRAL_DATE>' ||EOL;
1016 l_string := l_string || '<AUTO_ENROL_DATE>'|| l_prp_start_date||'</AUTO_ENROL_DATE>' ||EOL;
1017 l_string := l_string || '<MORE_INFO>'|| l_more_info||'</MORE_INFO>' ||EOL;
1018 l_string := l_string || '<CONTACT_MAIL_ORG>'|| l_contact_mail_org||'</CONTACT_MAIL_ORG>' ||EOL;
1019 l_string := l_string || '<INSTRUCTION_DETAILS>'|| l_instruction||'</INSTRUCTION_DETAILS>' ||EOL;
1020 l_string := l_string || '<SIGNATORY_NAME>'|| l_signatory_name||'</SIGNATORY_NAME>' ||EOL;
1021 l_string := l_string ||'</LETTERS>'||EOL;
1022
1023 p_string := p_string || l_string;
1024
1025 update_archive (l_archive_details_update,p_period,p_issue_date
1026 ,l_postponement_date
1027 ,l_postponement_type
1028 ,p_letter);
1029
1030 END IF;
1031 END IF;
1032 ----------------------------------------------------------------------------------------------------
1033 ----------------------------------------------------------------------------------------------------
1034 IF P_LETTER = 'PYGBPAEIG9' and l_flag ='Y' THEN
1035 --Letter of Postponement for the Workers
1036 fnd_file.put_line(fnd_file.LOG,'Letter of Postponement for the Workers');
1037
1038
1039 OPEN c_comm_details_qual(P_PAYROLL,p_pension_entry_values.qualifying_scheme_name);
1040 fetch c_comm_details_qual into l_signatory_name_pqs,l_contact_mail_pqs,l_instruction_pqs,l_more_info_pqs;
1041 close c_comm_details_qual;
1042
1043 if l_signatory_name_pqs is not null then
1044 l_signatory_name := l_signatory_name_pqs;
1045 end if;
1046 if l_contact_mail_pqs is not null then
1047 l_contact_mail_org := l_contact_mail_pqs;
1048 end if;
1049 if l_instruction_pqs is not null then
1050 l_instruction := l_instruction_pqs;
1051 end if;
1052 if l_more_info_pqs is not null then
1053 l_more_info := l_more_info_pqs;
1054 end if;
1055 IF p_pension_entry_values.postponement_end_date >= l_prp_end_date
1056 and NVL(p_pension_entry_values.postponement_type,'DB Scheme Postponement') <>'DB Scheme Postponement'
1057 and p_pension_entry_values.pension_classification = 'Worker' THEN
1058 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||p_assignment_id);
1059
1060 fnd_file.put_line(fnd_file.LOG,'Printing XML for the assignment id - '||p_assignment_id);
1061
1062 l_string := l_string ||'<LETTERS>'||EOL;
1063 l_string := l_string ||'<ADDRESS>'||EOL;
1064 l_string := l_string ||l_employee_name||EOL||EOL;
1065 l_string := l_string ||l_address.addr1||EOL;
1066 l_string := l_string ||l_address.addr2||EOL;
1067 l_string := l_string ||l_address.addr3||EOL;
1068 l_string := l_string ||l_address.post_code||EOL;
1069 l_string := l_string ||l_address.addr4||EOL;
1070 l_string := l_string ||l_address.country||EOL;
1071 l_string := l_string ||'</ADDRESS>'||EOL;
1072 l_string := l_string ||'<LETTER>9</LETTER>'||EOL;
1073 l_string := l_string || '<EMPLOYER_NAME>'|| l_employer_name||'</EMPLOYER_NAME>' ||EOL;
1074 l_string := l_string || '<EMPLOYEE_NAME>'|| l_employee_name||'</EMPLOYEE_NAME>' ||EOL;
1075 l_string := l_string || '<EARNING>'|| l_earning||'</EARNING>' ||EOL;
1076 l_string := l_string || '<EARNING_LOWER>'|| l_earning_lower||'</EARNING_LOWER>' ||EOL;
1077 l_string := l_string || '<EMAIL_ADDR>'|| l_not||'</EMAIL_ADDR>' ||EOL;
1078 l_string := l_string || '<NOT>'|| l_not||'</NOT>' ||EOL;
1079 l_string := l_string || '<EMP_AGE>'|| l_pension_age||'</EMP_AGE>' ||EOL;
1080 l_string := l_string || '<PERIOD>'|| l_period||'</PERIOD>' ||EOL;
1081 l_string := l_string || '<DEFERRAL_DATE>'|| p_pension_entry_values.postponement_end_date||'</DEFERRAL_DATE>' ||EOL;
1082 l_string := l_string || '<AUTO_ENROL_DATE>'|| l_prp_start_date||'</AUTO_ENROL_DATE>' ||EOL;
1083 l_string := l_string || '<MORE_INFO>'|| l_more_info||'</MORE_INFO>' ||EOL;
1084 l_string := l_string || '<CONTACT_MAIL_ORG>'|| l_contact_mail_org||'</CONTACT_MAIL_ORG>' ||EOL;
1085 l_string := l_string || '<INSTRUCTION_DETAILS>'|| l_instruction||'</INSTRUCTION_DETAILS>' ||EOL;
1086 l_string := l_string || '<SIGNATORY_NAME>'|| l_signatory_name||'</SIGNATORY_NAME>' ||EOL;
1087 l_string := l_string ||'</LETTERS>'||EOL;
1088
1089 p_string := p_string || l_string;
1090
1091 update_archive (l_archive_details_update,p_period,p_issue_date
1092 ,l_postponement_date
1093 ,l_postponement_type
1094 ,p_letter);
1095
1096 END IF;
1100
1097 END IF;
1098 ----------------------------------------------------------------------------------------------------
1099 END generate_xml;
1101 PROCEDURE generate
1102 (
1103 ERRBUG OUT NOCOPY VARCHAR2
1104 ,RETCODE OUT NOCOPY VARCHAR2
1105 ,P_EMPLOYER IN number
1106 ,P_PAYROLL IN number
1107 ,P_PERIOD IN number
1108 ,P_LETTER IN VARCHAR2
1109 --,p_xml OUT NOCOPY CLOB
1110 ,P_DEBUG_FLAG IN varchar2
1111 ,P_MODE IN varchar2
1112 ,P_ASSIGNMENT_SET IN number
1113 ,P_ISSUE_DATE IN VARCHAR2
1114 ) IS
1115
1116
1117 /*-------------------------------------------------------------
1118 PYGBPAEIG1 - Letter for the Automatically Enrolled
1119 PYGBPAEIG2 - Letter for the Non Eligible Job Holders
1120 PYGBPAEIG3 - Letter for the Workers
1121 PYGBPAEIG4 - Letter for who are already in Qualifying Pension Scheme
1122 PYGBPAEIG5 - General Letter of Postponement (Redundant)
1123 PYGBPAEIG6 - General Letter of Postponement
1124 PYGBPAEIG7 - Letter of DB Scheme Postponement
1125 PYGBPAEIG8 - Letter of Postponement for the Non Eligible Job Holders
1126 PYGBPAEIG9 - Letter of Postponement for the Workers
1127 --------------------------------------------------------------*/
1128
1129 /*-------------------------------------------------------------
1130 Letter1 - Letter for the Automatically Enrolled
1131 (To all the Employees who with the Automatic Enrolment Date within that period)
1132 Letter2 - Letter for the Non Eligible Job Holders
1133 (To all the Non-Eligible Job Holders (No Postponement) in the period for the first time)
1134 Letter3 - Letter for the Workers
1135 (To all the Workers (No Postponement) in the period for the first time)
1136 Letter4 - Letter for who are already in Qualifying Pension Scheme
1137 (To all the people who have a QPS already attached and which does not have an Automatic Enrolment Date)
1138 Letter5 - General Letter of Postponement (Redundant) --- Will not be used as Letter 6 can be sent for this purpose too.
1139 Letter6 - General Letter of Postponement
1140 (For every assignment with Postponement (EJH Postponement, Worker Postponement or DB Scheme Postponement))
1141 Letter1T - Letter of DB Scheme Postponement
1142 (To assignments under DB Scheme Postponement in the period)
1143 Letter2P - Letter of Postponement for the Non Eligible Job Holders
1144 (To all the Non-Eligible Job Holders under Postponement in the period)
1145 Letter3P - Letter of Postponement for the Workers
1146 (To all the Workers under Postponement in the period)
1147 --------------------------------------------------------------*/
1148
1149 l_set_layout boolean;
1150 p_request_id number;
1151 l_product_release varchar2(30);
1152 -----------------------
1153 /*
1154 l_tab_asg_set_amnds pqp_budget_maintenance.t_asg_set_amnds;
1155 l_formula_id Number :=0;
1156 l_flag varchar2(1);
1157 */
1158
1159
1160 -----------------------
1161
1162
1163
1164 CURSOR c_all_assignments_payroll IS
1165 SELECT DISTINCT
1166 paaf.assignment_id
1167 FROM per_all_assignments_f paaf
1168 , per_assignment_status_types past
1169 WHERE paaf.payroll_id = p_payroll
1170 AND paaf.assignment_status_type_id = past.assignment_status_type_id
1171 AND past.per_system_status = 'ACTIVE_ASSIGN'
1172 AND paaf.assignment_type = 'E'
1173 AND l_prp_end_date -- Review again against the bug 14813352
1174 BETWEEN paaf.effective_start_date
1175 AND paaf.effective_end_date
1176 ORDER BY assignment_id;
1177
1178
1179 l_pension_entry_values c_pension_entry_values%rowtype;
1180 l_string VARCHAR2(32767) := NULL;
1181 t_string VARCHAR2(32767) := NULL;
1182 l_xml CLOB;
1183 EOL VARCHAR2(5) := fnd_global.local_chr(10);
1184 l_req_id varchar2(20);
1185 l_assignment_number per_all_assignments_f.assignment_number%type;
1186 l_employee_name per_all_people_f.full_name%type;
1187 /*
1188
1189 t_string VARCHAR2(32767) := NULL;
1190
1191 l_archive_details_check c_archive_details_check%rowtype;
1192 l_archive_details_update c_archive_details_update%rowtype;
1193 l_pension_entry_values c_pension_entry_values%rowtype;
1194
1195 l_global_value c_get_global_value%rowtype;
1196
1197 l_string VARCHAR2(32767) := NULL;
1198 l_xml CLOB;
1199 EOL VARCHAR2(5) := fnd_global.local_chr(10);
1200 l_pension_age number := 65;
1201 l_not varchar2(3) :='not';
1202
1203 --------------------------------------
1204 l_employer_name VARCHAR2(150):= NULL;
1205 l_employee_name per_all_people_f.full_name%type:= NULL;
1206 l_period pay_payrolls_f.period_type%type:= NULL;
1207 l_earning ff_globals_f.global_value%type:= NULL;
1208 l_earning_lower ff_globals_f.global_value%type:= NULL;
1209
1210 l_contact_mail_org VARCHAR2(32767) := NULL;
1211 l_more_info VARCHAR2(32767) := NULL;
1212 l_instruction VARCHAR2(32767) := NULL;
1213 l_signatory_name VARCHAR2(32767) := NULL;
1214
1215 l_ovn number;
1216 */
1217
1218 --------------------------------------
1219
1220 BEGIN
1221
1222 fnd_file.put_line(fnd_file.LOG,'Entering GENERATE');
1223 IF P_MODE = 'SUMMARY' THEN --Mode = SUMMARY
1224 IF P_LETTER = 'PYGBPAEIG1' THEN
1225 --Letter for the Automatically Enrolled
1226 fnd_file.put_line(fnd_file.OUTPUT,'Letter for the Automatically Enrolled');
1227
1228 END IF;
1232 --Letter for the Non Eligible Job Holders
1229 ----------------------------------------------------------------------------------------------------
1230 ----------------------------------------------------------------------------------------------------
1231 IF P_LETTER = 'PYGBPAEIG2' THEN
1233 fnd_file.put_line(fnd_file.OUTPUT,'Letter for the Non Eligible Job Holders');
1234
1235 END IF;
1236 ----------------------------------------------------------------------------------------------------
1237 ----------------------------------------------------------------------------------------------------
1238 IF P_LETTER = 'PYGBPAEIG3' THEN
1239 --Letter for the Workers
1240 fnd_file.put_line(fnd_file.OUTPUT,'Letter for the Workers');
1241
1242 END IF;
1243 ----------------------------------------------------------------------------------------------------
1244 ----------------------------------------------------------------------------------------------------
1245 IF P_LETTER = 'PYGBPAEIG4' THEN
1246 --Letter for who are already in Qualifying Pension Scheme
1247 fnd_file.put_line(fnd_file.OUTPUT,'Letter for who are already in Qualifying Pension Scheme');
1248
1249 END IF;
1250 ----------------------------------------------------------------------------------------------------
1251 ----------------------------------------------------------------------------------------------------
1252 IF P_LETTER = 'PYGBPAEIG5' THEN
1253 --General Letter of Postponement Redundant
1254 fnd_file.put_line(fnd_file.OUTPUT,'General Letter of Postponement (Redundant)');
1255
1256
1257 END IF;
1258 ----------------------------------------------------------------------------------------------------
1259 ----------------------------------------------------------------------------------------------------
1260 IF P_LETTER = 'PYGBPAEIG6' THEN
1261 --General Letter of Postponement
1262 fnd_file.put_line(fnd_file.OUTPUT,'General Letter of Postponement');
1263
1264
1265
1266 END IF;
1267 ----------------------------------------------------------------------------------------------------
1268 ----------------------------------------------------------------------------------------------------
1269 IF P_LETTER = 'PYGBPAEIG7' THEN
1270 --Letter of DB Scheme Postponement
1271 fnd_file.put_line(fnd_file.OUTPUT,'Letter of DB Scheme Postponement');
1272
1273 END IF;
1274 ----------------------------------------------------------------------------------------------------
1275 ----------------------------------------------------------------------------------------------------
1276 IF P_LETTER = 'PYGBPAEIG8' THEN
1277 --Letter of Postponement for the Non Eligible Job Holders
1278 fnd_file.put_line(fnd_file.OUTPUT,'Letter of Postponement for the Non Eligible Job Holders');
1279
1280
1281 END IF;
1282 ----------------------------------------------------------------------------------------------------
1283 ----------------------------------------------------------------------------------------------------
1284 IF P_LETTER = 'PYGBPAEIG9' THEN
1285 --Letter of Postponement for the Workers
1286 fnd_file.put_line(fnd_file.OUTPUT,'Letter of Postponement for the Workers');
1287
1288 END IF;
1289 ----------------------------------------------------------------------------------------------------
1290 fnd_file.put_line(fnd_file.output,rpad('-',86,'-')) ;
1291 fnd_file.put_line(fnd_file.output,rpad(' ',86)) ;
1292 fnd_file.put_line(fnd_file.output,rpad('Assignment Number',35)||rpad('Employee Name',50)) ;
1293 fnd_file.put_line(fnd_file.output,rpad('-',86,'-')) ;
1294
1295 END IF; --Mode = SUMMARY
1296
1297 IF P_MODE = 'GENMAIL' OR P_MODE = 'SUMMARY' THEN --Mode = SUMMARY THEN --Mode = Generate and Mail
1298 fnd_file.put_line(fnd_file.LOG,'P_EMPLOYER ::'||P_EMPLOYER);
1299 fnd_file.put_line(fnd_file.LOG,'P_PAYROLL ::'||P_PAYROLL);
1300 fnd_file.put_line(fnd_file.LOG,'P_PERIOD ::'||P_PERIOD);
1301 fnd_file.put_line(fnd_file.LOG,'P_LETTER ::'||P_LETTER);
1302
1303 OPEN c_payroll_details(p_period);
1304 FETCH c_payroll_details INTO l_pay_start_date,l_pay_end_date,l_pay_date_paid;
1305 CLOSE c_payroll_details;
1306 fnd_file.put_line(fnd_file.LOG,'l_pay_start_date : '||fnd_date.date_to_canonical(l_pay_start_date));
1307 fnd_file.put_line(fnd_file.LOG,'l_pay_end_date : '||fnd_date.date_to_canonical(l_pay_end_date));
1308 fnd_file.put_line(fnd_file.LOG,'l_pay_date_paid : '||fnd_date.date_to_canonical(l_pay_date_paid));
1309
1310 OPEN cur_input_id;
1311 FETCH cur_input_id INTO l_input_value_ids;
1312 CLOSE cur_input_id;
1313 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.qualifying_scheme_name : '||l_input_value_ids.qualifying_scheme_name);
1314 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.auto_enrollment_date : '||l_input_value_ids.auto_enrollment_date);
1315 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.pension_classification : '||l_input_value_ids.pension_classification);
1316 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.opt_out_period_end_date : '||l_input_value_ids.opt_out_period_end_date);
1317 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.total_earning_prp : '||l_input_value_ids.total_earning_prp);
1318 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.postponement_type : '||l_input_value_ids.postponement_type);
1319 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.postponement_end_date : '||l_input_value_ids.postponement_end_date);
1320 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.qualifying_scheme_exists : '||l_input_value_ids.qualifying_scheme_exists);
1321 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.opt_in_date : '||l_input_value_ids.opt_in_date);
1322 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.elig_jh_date : '||l_input_value_ids.elig_jh_date);
1323 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.opt_out_date : '||l_input_value_ids.opt_out_date);
1324
1325 fnd_file.put_line(fnd_file.LOG,'Before l_pay_date_paid : '||fnd_date.date_to_canonical(l_pay_date_paid));
1326 fnd_file.put_line(fnd_file.LOG,'Before P_PAYROLL ::'||P_PAYROLL);
1327 OPEN c_prp_details(p_payroll);
1328 FETCH c_prp_details INTO l_prp_start_date,l_prp_end_date,l_prp_date_paid;
1332 fnd_file.put_line(fnd_file.LOG,'l_prp_date_paid : '||l_prp_date_paid);
1329 CLOSE c_prp_details;
1330 fnd_file.put_line(fnd_file.LOG,'l_prp_start_date : '||l_prp_start_date);
1331 fnd_file.put_line(fnd_file.LOG,'l_prp_end_date : '||l_prp_end_date);
1333
1334 l_string := l_string || '<PYGBPAEIG>'||EOL;
1335
1336 FOR assignment in c_all_assignments_payroll
1337 LOOP -- Loop every assignment
1338 fnd_file.put_line(fnd_file.LOG,assignment.assignment_id);
1339 l_pension_entry_values := NULL;
1340 l_employee_name:= NULL;
1341 l_formula_id :=NULL;
1342 fnd_file.put_line(fnd_file.LOG,'Counter::'||counter);
1343 --l_tab_asg_set_amnds := NULL;
1344 l_flag :='Y';
1345 IF P_ASSIGNMENT_SET is not null then
1346 l_flag :='N';
1347 pqp_budget_maintenance.get_asg_set_details(p_assignment_set_id => P_ASSIGNMENT_SET
1348 ,p_formula_id => l_formula_id
1349 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
1350 );
1351
1352 l_flag :=pqp_budget_maintenance.chk_is_asg_in_asg_set(p_assignment_id => assignment.assignment_id
1353 ,p_formula_id => l_formula_id
1354 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
1355 ,p_effective_date => l_prp_end_date
1356 );
1357 END IF;
1358 IF l_flag ='Y' OR P_ASSIGNMENT_SET IS NULL THEN -- Assignment Set Check
1359 --continue;
1360 --END IF;-- Assignment Set Check
1361
1362 OPEN c_pension_info_check(assignment.assignment_id);
1363 FETCH c_pension_info_check into l_temp;
1364 close c_pension_info_check;
1365
1366 IF l_temp =1 then -- Element Attached Check
1367 fnd_file.put_line(fnd_file.LOG,'Pensions Information Element Attached');
1368
1369 OPEN c_pension_entry_values(assignment.assignment_id);
1370 FETCH c_pension_entry_values into l_pension_entry_values;
1371 CLOSE c_pension_entry_values;
1372 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.qualifying_scheme_name : '||l_pension_entry_values.qualifying_scheme_name);
1373 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.auto_enrollment_date : '||l_pension_entry_values.auto_enrollment_date);
1374 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.pension_classification : '||l_pension_entry_values.pension_classification);
1375 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.opt_out_period_end_date : '||l_pension_entry_values.opt_out_period_end_date);
1376 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.total_earning_prp : '||l_pension_entry_values.total_earning_prp);
1377 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.postponement_type : '||l_pension_entry_values.postponement_type);
1378 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.postponement_end_date : '||l_pension_entry_values.postponement_end_date);
1379 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.qualifying_scheme_exists : '||l_pension_entry_values.qualifying_scheme_exists);
1380 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.opt_in_date : '||l_pension_entry_values.opt_in_date);
1381 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.elig_jh_date : '||l_pension_entry_values.elig_jh_date);
1382 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.opt_out_date : '||l_pension_entry_values.opt_out_date);
1383
1384
1385 fnd_file.put_line(fnd_file.LOG,'Before Loop End');
1386 IF P_MODE = 'SUMMARY' THEN --Mode = SUMMARY
1387
1388 open c_summary_details(assignment.assignment_id);
1389 fetch c_summary_details into l_assignment_number,l_employee_name;
1390 close c_summary_details;
1391
1392
1393 ----------------------------------------------------------------------------------------------------
1394 IF P_LETTER = 'PYGBPAEIG1' and l_flag ='Y' THEN
1395 --Letter for the Automatically Enrolled
1396 fnd_file.put_line(fnd_file.LOG,'Letter for the Automatically Enrolled');
1397
1398
1399 IF l_pension_entry_values.auto_enrollment_date between l_prp_start_date and l_prp_end_date and
1400 NVL(l_pension_entry_values.qualifying_scheme_exists,'N') ='Y'
1401 and l_pension_entry_values.pension_classification = 'Eligible Job Holder' THEN
1402 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||assignment.assignment_id);
1403 fnd_file.put_line(fnd_file.LOG,'Assignment_Number : '||l_assignment_number);
1404 fnd_file.put_line(fnd_file.LOG,'l_employee_name : '||l_employee_name);
1405 fnd_file.put_line(fnd_file.output,rpad(l_assignment_number,35)||rpad(l_employee_name,50)) ;
1406
1407
1408 END IF;
1409 END IF;
1410 ----------------------------------------------------------------------------------------------------
1411 ----------------------------------------------------------------------------------------------------
1412 IF P_LETTER = 'PYGBPAEIG2' and l_flag ='Y' THEN
1413 --Letter for the Non Eligible Job Holders
1414 fnd_file.put_line(fnd_file.LOG,'Letter for the Non Eligible Job Holders');
1415
1416
1417 IF NVL(l_pension_entry_values.postponement_end_date,l_prp_start_date) <l_prp_end_date and NVL(l_pension_entry_values.qualifying_scheme_exists,'N') ='N'
1418 and l_pension_entry_values.pension_classification = 'Non Eligible Job Holder' THEN
1419 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||assignment.assignment_id);
1420 fnd_file.put_line(fnd_file.LOG,'Assignment_Number : '||l_assignment_number);
1421 fnd_file.put_line(fnd_file.LOG,'l_employee_name : '||l_employee_name);
1422 fnd_file.put_line(fnd_file.output,rpad(l_assignment_number,35)||rpad(l_employee_name,50)) ;
1423
1424
1425
1426 END IF;
1427 END IF;
1428 ----------------------------------------------------------------------------------------------------
1429 ----------------------------------------------------------------------------------------------------
1430 IF P_LETTER = 'PYGBPAEIG3' and l_flag ='Y' THEN
1431 --Letter for the Workers
1432 fnd_file.put_line(fnd_file.LOG,'Letter for the Workers');
1433
1434
1438 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||assignment.assignment_id);
1435 IF NVL(l_pension_entry_values.postponement_end_date,l_prp_start_date) <l_prp_end_date
1436 and NVL(l_pension_entry_values.qualifying_scheme_exists,'N') ='N'
1437 and l_pension_entry_values.pension_classification = 'Worker' THEN
1439 fnd_file.put_line(fnd_file.LOG,'Assignment_Number : '||l_assignment_number);
1440 fnd_file.put_line(fnd_file.LOG,'l_employee_name : '||l_employee_name);
1441 fnd_file.put_line(fnd_file.output,rpad(l_assignment_number,35)||rpad(l_employee_name,50)) ;
1442
1443
1444
1445 END IF;
1446 END IF;
1447 ----------------------------------------------------------------------------------------------------
1448 ----------------------------------------------------------------------------------------------------
1449 IF P_LETTER = 'PYGBPAEIG4' and l_flag ='Y' THEN
1450 --Letter for who are already in Qualifying Pension Scheme
1451 fnd_file.put_line(fnd_file.LOG,'Letter for who are already in Qualifying Pension Scheme');
1452
1453
1454 IF l_pension_entry_values.auto_enrollment_date IS NULL
1455 and NVL(l_pension_entry_values.qualifying_scheme_exists,'N') ='Y' THEN
1456 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||assignment.assignment_id);
1457 fnd_file.put_line(fnd_file.LOG,'Assignment_Number : '||l_assignment_number);
1458 fnd_file.put_line(fnd_file.LOG,'l_employee_name : '||l_employee_name);
1459 fnd_file.put_line(fnd_file.output,rpad(l_assignment_number,35)||rpad(l_employee_name,50)) ;
1460
1461
1462
1463 END IF;
1464 END IF;
1465 ----------------------------------------------------------------------------------------------------
1466 ----------------------------------------------------------------------------------------------------
1467 IF P_LETTER = 'PYGBPAEIG5' and l_flag ='Y' THEN
1468 --General Letter of Postponement Redundant
1469 fnd_file.put_line(fnd_file.LOG,'General Letter of Postponement (Redundant)');
1470
1471
1472
1473 IF l_pension_entry_values.postponement_end_date >=l_prp_end_date
1474 and l_pension_entry_values.auto_enrollment_date IS NULL THEN
1475 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||assignment.assignment_id);
1476 fnd_file.put_line(fnd_file.LOG,'Assignment_Number : '||l_assignment_number);
1477 fnd_file.put_line(fnd_file.LOG,'l_employee_name : '||l_employee_name);
1478 fnd_file.put_line(fnd_file.output,rpad(l_assignment_number,35)||rpad(l_employee_name,50)) ;
1479
1480
1481
1482 END IF;
1483 END IF;
1484 ----------------------------------------------------------------------------------------------------
1485 ----------------------------------------------------------------------------------------------------
1486 IF P_LETTER = 'PYGBPAEIG6' and l_flag ='Y' THEN
1487 --General Letter of Postponement
1488 fnd_file.put_line(fnd_file.LOG,'General Letter of Postponement');
1489
1490
1491 IF l_pension_entry_values.postponement_end_date >=l_prp_end_date
1492 and l_pension_entry_values.auto_enrollment_date IS NULL THEN
1493 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||assignment.assignment_id);
1494 fnd_file.put_line(fnd_file.LOG,'Assignment_Number : '||l_assignment_number);
1495 fnd_file.put_line(fnd_file.LOG,'l_employee_name : '||l_employee_name);
1496 fnd_file.put_line(fnd_file.output,rpad(l_assignment_number,35)||rpad(l_employee_name,50)) ;
1497
1498
1499
1500 END IF;
1501 END IF;
1502 ----------------------------------------------------------------------------------------------------
1503 ----------------------------------------------------------------------------------------------------
1504 IF P_LETTER = 'PYGBPAEIG7' and l_flag ='Y' THEN
1505 --Letter of DB Scheme Postponement
1506 fnd_file.put_line(fnd_file.LOG,'Letter of DB Scheme Postponement');
1507
1508
1509 IF l_pension_entry_values.postponement_end_date >= l_prp_end_date
1510 and l_pension_entry_values.postponement_type = 'DB Scheme Postponement' THEN
1511 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||assignment.assignment_id);
1512 fnd_file.put_line(fnd_file.LOG,'Assignment_Number : '||l_assignment_number);
1513 fnd_file.put_line(fnd_file.LOG,'l_employee_name : '||l_employee_name);
1514 fnd_file.put_line(fnd_file.output,rpad(l_assignment_number,35)||rpad(l_employee_name,50)) ;
1515
1516
1517
1518 END IF;
1519 END IF;
1520 ----------------------------------------------------------------------------------------------------
1521 ----------------------------------------------------------------------------------------------------
1522 IF P_LETTER = 'PYGBPAEIG8' and l_flag ='Y' THEN
1523 --Letter of Postponement for the Non Eligible Job Holders
1524 fnd_file.put_line(fnd_file.LOG,'Letter of Postponement for the Non Eligible Job Holders');
1525
1526
1527 IF l_pension_entry_values.postponement_end_date >= l_prp_end_date
1528 and NVL(l_pension_entry_values.postponement_type,'DB Scheme Postponement') <>'DB Scheme Postponement'
1529 and l_pension_entry_values.pension_classification = 'Non Eligible Job Holder' THEN
1530 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||assignment.assignment_id);
1531 fnd_file.put_line(fnd_file.LOG,'Assignment_Number : '||l_assignment_number);
1532 fnd_file.put_line(fnd_file.LOG,'l_employee_name : '||l_employee_name);
1533 fnd_file.put_line(fnd_file.output,rpad(l_assignment_number,35)||rpad(l_employee_name,50)) ;
1534
1535
1536
1537 END IF;
1538 END IF;
1539 ----------------------------------------------------------------------------------------------------
1540 ----------------------------------------------------------------------------------------------------
1541 IF P_LETTER = 'PYGBPAEIG9' and l_flag ='Y' THEN
1542 --Letter of Postponement for the Workers
1543 fnd_file.put_line(fnd_file.LOG,'Letter of Postponement for the Workers');
1547 and NVL(l_pension_entry_values.postponement_type,'DB Scheme Postponement') <>'DB Scheme Postponement'
1544
1545
1546 IF l_pension_entry_values.postponement_end_date >= l_prp_end_date
1548 and l_pension_entry_values.pension_classification = 'Worker' THEN
1549 fnd_file.put_line(fnd_file.LOG,'Assignment_id : '||assignment.assignment_id);
1550 fnd_file.put_line(fnd_file.LOG,'Assignment_Number : '||l_assignment_number);
1551 fnd_file.put_line(fnd_file.LOG,'l_employee_name : '||l_employee_name);
1552 fnd_file.put_line(fnd_file.output,rpad(l_assignment_number,35)||rpad(l_employee_name,50)) ;
1553
1554
1555 END IF;
1556 END IF;
1557 ----------------------------------------------------------------------------------------------------
1558
1559 END IF; --Mode = SUMMARY
1560
1561
1562 IF P_MODE = 'GENMAIL' THEN --Mode = Generate and Mail Inner
1563 generate_xml(p_letter ,assignment.assignment_id ,l_pension_entry_values ,P_PERIOD,p_payroll ,
1564 fnd_date.canonical_to_date(p_issue_date),t_string );
1565
1566 l_string := l_string || t_string;
1567
1568 fnd_file.put_line(fnd_file.LOG,'GENERATE_XML xml: '||l_string);
1569 END IF;--Mode = Generate and Mail Inner
1570 ELSE--Element Attached Check
1571 fnd_file.put_line(fnd_file.LOG,'Pensions Information Element Not Attached');
1572 --continue;
1573 END IF;--Element Attached Check
1574 END IF;-- Assignment Set Check
1575 END LOOP;--Loop Every Assignment
1576 IF P_MODE = 'GENMAIL' THEN --Mode = Generate and Mail Inner
1577 l_req_id := fnd_global.conc_request_id ;
1578 l_string := l_string || '</PYGBPAEIG>'||EOL;
1579
1580 fnd_file.put_line(fnd_file.LOG,'Request_id'||l_req_id);
1581 fnd_file.put_line(fnd_file.LOG,'XML '||l_string);
1582
1583 l_string := '<?xml version="1.0" encoding="UTF-8"?>' ||EOL ;--||'<LETTERS>'||EOL|| l_string||'</LETTERS>';
1584
1585 ---------------------------------------------------------------------------------------
1586 fnd_file.put_line(fnd_file.OUTPUT,l_string);
1587 -- Working Section For XML BUrsting
1588 p_request_id :=fnd_global.conc_request_id;
1589 UPDATE fnd_concurrent_requests
1590 SET output_file_type = 'XML'
1591 WHERE request_id = p_request_id;
1592
1593 COMMIT ;
1594 fnd_file.put_line(fnd_file.LOG,'Committed');
1595 SELECT substr(p.product_version,1,2) INTO l_product_release
1596 FROM fnd_application a, fnd_application_tl t, fnd_product_installations p
1597 WHERE a.application_id = p.application_id
1598 AND a.application_id = t.application_id
1599 AND t.language = Userenv ('LANG')
1600 AND Substr (a.application_short_name, 1, 5) = 'PAY';
1601 fnd_file.put_line(fnd_file.LOG,'Check 1');
1602 l_set_layout := fnd_request.add_layout('XDO','BURST_STATUS_REPORT','en','US','PDF');
1603 IF l_set_layout THEN
1604 fnd_file.put_line(fnd_file.LOG,'Check 2');
1605 ELSE
1606 fnd_file.put_line(fnd_file.LOG,'Check 5');
1607 END IF;
1608
1609 IF TO_NUMBER(l_product_release) = 11 THEN
1610 p_request_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOBURSTREP',NULL,NULL,FALSE,p_request_id,'N');
1611 fnd_file.put_line(fnd_file.LOG,'Check 3');
1612 ELSE
1613 p_request_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOBURSTREP',NULL,NULL,FALSE,'Y',p_request_id,'Y');
1614 fnd_file.put_line(fnd_file.LOG,'Check 4');
1615 END IF;
1616
1617 -- Working Section For XML Bursting
1618 End IF;--Mode = Generate and Mail Inner
1619 END IF;--Mode = Generate and Mail
1620
1621 IF P_MODE = 'GENERATE' THEN --Mode = Generate
1622 --Testing For PDF
1623 p_request_id := fnd_request.submit_request (application => 'PAY' ,program => 'PYGBPAEIGP' ,argument1 => P_EMPLOYER ,argument2 =>P_PAYROLL ,
1624 argument3 => p_period , argument4 =>'PYGBPAEIGP' ,
1625 argument5 => 'GB' ,
1626 argument6 => fnd_date.date_to_canonical(sysdate) ,argument7 => 'PYGBPAEIGP' ,argument8 => 'PAY'
1627 --Template Name
1628 ,argument9 => 'PYGBPAEIG', argument10 =>P_ASSIGNMENT_SET ,
1629 argument11=>p_letter,argument12=>P_ISSUE_DATE, argument13 => 'N');
1630 --,argument9 => 'PYGBPAEIG', argument10 =>P_ASSIGNMENT_SET ,argument11 => 'N');
1631
1632
1633 END IF; --Mode = Generate
1634
1635 END generate;
1636
1637
1638 procedure generate_pdf(
1639
1640 P_EMPLOYER IN number
1641 ,P_PAYROLL IN number
1642 ,P_PERIOD IN number
1643 ,P_TEMPLATE_NAME IN VARCHAR2
1644 --,P_LETTER IN VARCHAR2
1645 ,P_ASSIGNMENT_SET IN number
1646 ,P_LETTER IN VARCHAR2
1647 ,P_ISSUE_DATE IN VARCHAR2
1648 ,p_xml OUT NOCOPY CLOB
1649 ) IS
1650
1651
1652 /*-------------------------------------------------------------
1653 PYGBPAEIG1 - Letter for the Automatically Enrolled
1654 PYGBPAEIG2 - Letter for the Non Eligible Job Holders
1655 PYGBPAEIG3 - Letter for the Workers
1656 PYGBPAEIG4 - Letter for who are already in Qualifying Pension Scheme
1657 PYGBPAEIG5 - General Letter of Postponement (Redundant)
1658 PYGBPAEIG6 - General Letter of Postponement
1659 PYGBPAEIG7 - Letter of DB Scheme Postponement
1660 PYGBPAEIG8 - Letter of Postponement for the Non Eligible Job Holders
1661 PYGBPAEIG9 - Letter of Postponement for the Workers
1662 --------------------------------------------------------------*/
1663
1664 /*-------------------------------------------------------------
1665 Letter1 - Letter for the Automatically Enrolled
1666 (To all the Employees who with the Automatic Enrolment Date within that period)
1667 Letter2 - Letter for the Non Eligible Job Holders
1671 Letter4 - Letter for who are already in Qualifying Pension Scheme
1668 (To all the Non-Eligible Job Holders (No Postponement) in the period for the first time)
1669 Letter3 - Letter for the Workers
1670 (To all the Workers (No Postponement) in the period for the first time)
1672 (To all the people who have a QPS already attached and which does not have an Automatic Enrolment Date)
1673 Letter5 - General Letter of Postponement (Redundant) --- Will not be used as Letter 6 can be sent for this purpose too.
1674 Letter6 - General Letter of Postponement
1675 (For every assignment with Postponement (EJH Postponement, Worker Postponement or DB Scheme Postponement))
1676 Letter1T - Letter of DB Scheme Postponement
1677 (To assignments under DB Scheme Postponement in the period)
1678 Letter2P - Letter of Postponement for the Non Eligible Job Holders
1679 (To all the Non-Eligible Job Holders under Postponement in the period)
1680 Letter3P - Letter of Postponement for the Workers
1681 (To all the Workers under Postponement in the period)
1682 --------------------------------------------------------------*/
1683
1684
1685 CURSOR c_all_assignments_payroll IS
1686 SELECT DISTINCT
1687 paaf.assignment_id
1688 FROM per_all_assignments_f paaf
1689 , per_assignment_status_types past
1690 WHERE paaf.payroll_id = p_payroll
1691 AND paaf.assignment_status_type_id = past.assignment_status_type_id
1692 AND past.per_system_status = 'ACTIVE_ASSIGN'
1693 AND paaf.assignment_type = 'E'
1694 AND l_prp_end_date -- Review again against the bug 14813352
1695 BETWEEN paaf.effective_start_date
1696 AND paaf.effective_end_date
1697 ORDER BY assignment_id;
1698
1699
1700 l_pension_entry_values c_pension_entry_values%rowtype;
1701 l_string VARCHAR2(32767) := NULL;
1702 t_string VARCHAR2(32767) := NULL;
1703 l_xml CLOB;
1704 EOL VARCHAR2(5) := fnd_global.local_chr(10);
1705
1706
1707
1708
1709 BEGIN
1710
1711 fnd_file.put_line(fnd_file.LOG,'Entering generate_pdf');
1712 fnd_file.put_line(fnd_file.LOG,'P_EMPLOYER ::'||P_EMPLOYER);
1713 fnd_file.put_line(fnd_file.LOG,'P_PAYROLL ::'||P_PAYROLL);
1714 fnd_file.put_line(fnd_file.LOG,'P_PERIOD ::'||P_PERIOD);
1715 fnd_file.put_line(fnd_file.LOG,'P_TEMPLATE_NAME ::'||P_TEMPLATE_NAME);
1716 fnd_file.put_line(fnd_file.LOG,'P_LETTER ::'||P_LETTER);
1717
1718 OPEN c_payroll_details(p_period);
1719 FETCH c_payroll_details INTO l_pay_start_date,l_pay_end_date,l_pay_date_paid;
1720 CLOSE c_payroll_details;
1721 fnd_file.put_line(fnd_file.LOG,'l_pay_start_date : '||l_pay_start_date);
1722 fnd_file.put_line(fnd_file.LOG,'l_pay_end_date : '||l_pay_end_date);
1723 fnd_file.put_line(fnd_file.LOG,'l_pay_date_paid : '||l_pay_date_paid);
1724
1725 OPEN cur_input_id;
1726 FETCH cur_input_id INTO l_input_value_ids;
1727 CLOSE cur_input_id;
1728 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.qualifying_scheme_name : '||l_input_value_ids.qualifying_scheme_name);
1729 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.auto_enrollment_date : '||l_input_value_ids.auto_enrollment_date);
1730 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.pension_classification : '||l_input_value_ids.pension_classification);
1731 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.opt_out_period_end_date : '||l_input_value_ids.opt_out_period_end_date);
1732 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.total_earning_prp : '||l_input_value_ids.total_earning_prp);
1733 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.postponement_type : '||l_input_value_ids.postponement_type);
1734 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.postponement_end_date : '||l_input_value_ids.postponement_end_date);
1735 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.qualifying_scheme_exists : '||l_input_value_ids.qualifying_scheme_exists);
1736 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.opt_in_date : '||l_input_value_ids.opt_in_date);
1737 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.elig_jh_date : '||l_input_value_ids.elig_jh_date);
1738 fnd_file.put_line(fnd_file.LOG,'l_input_value_ids.opt_out_date : '||l_input_value_ids.opt_out_date);
1739
1740 OPEN c_prp_details(p_payroll);
1741 FETCH c_prp_details INTO l_prp_start_date,l_prp_end_date,l_prp_date_paid;
1742 CLOSE c_prp_details;
1743 fnd_file.put_line(fnd_file.LOG,'l_prp_start_date : '||l_prp_start_date);
1744 fnd_file.put_line(fnd_file.LOG,'l_prp_end_date : '||l_prp_end_date);
1745 fnd_file.put_line(fnd_file.LOG,'l_prp_date_paid : '||l_prp_date_paid);
1746
1747 l_string := l_string || '<PYGBPAEIG>'||EOL;
1748
1749 FOR assignment in c_all_assignments_payroll
1750 LOOP -- Loop every assignment
1751 fnd_file.put_line(fnd_file.LOG,assignment.assignment_id);
1752 l_pension_entry_values := NULL;
1753 l_employee_name:= NULL;
1754 l_formula_id :=NULL;
1755 fnd_file.put_line(fnd_file.LOG,'Counter::'||counter);
1756 --l_tab_asg_set_amnds := NULL;
1757 IF P_ASSIGNMENT_SET is not null then
1758 pqp_budget_maintenance.get_asg_set_details(p_assignment_set_id => P_ASSIGNMENT_SET
1759 ,p_formula_id => l_formula_id
1760 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
1761 );
1762
1763 l_flag :=pqp_budget_maintenance.chk_is_asg_in_asg_set(p_assignment_id => assignment.assignment_id
1764 ,p_formula_id => l_formula_id
1765 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
1766 ,p_effective_date => l_prp_end_date
1767 );
1768 END IF;
1769 IF l_flag ='Y' OR P_ASSIGNMENT_SET IS NULL THEN -- Assignment Set Check
1770 --continue;
1774
1771 --END IF;-- Assignment Set Check
1772
1773
1775 OPEN c_pension_info_check(assignment.assignment_id);
1776 FETCH c_pension_info_check into l_temp;
1777 close c_pension_info_check;
1778
1779 IF l_temp =1 then--Element attached Check
1780 fnd_file.put_line(fnd_file.LOG,'Pensions Information Element Attached');
1781
1782
1783 OPEN c_pension_entry_values(assignment.assignment_id);
1784 FETCH c_pension_entry_values into l_pension_entry_values;
1785 CLOSE c_pension_entry_values;
1786 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.qualifying_scheme_name : '||l_pension_entry_values.qualifying_scheme_name);
1787 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.auto_enrollment_date : '||l_pension_entry_values.auto_enrollment_date);
1788 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.pension_classification : '||l_pension_entry_values.pension_classification);
1789 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.opt_out_period_end_date : '||l_pension_entry_values.opt_out_period_end_date);
1790 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.total_earning_prp : '||l_pension_entry_values.total_earning_prp);
1791 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.postponement_type : '||l_pension_entry_values.postponement_type);
1792 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.postponement_end_date : '||l_pension_entry_values.postponement_end_date);
1793 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.qualifying_scheme_exists : '||l_pension_entry_values.qualifying_scheme_exists);
1794 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.opt_in_date : '||l_pension_entry_values.opt_in_date);
1795 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.elig_jh_date : '||l_pension_entry_values.elig_jh_date);
1796 fnd_file.put_line(fnd_file.LOG,'l_pension_entry_values.opt_out_date : '||l_pension_entry_values.opt_out_date);
1797
1798
1799 fnd_file.put_line(fnd_file.LOG,'Before GENERATE_XML');
1800 generate_xml(p_letter ,assignment.assignment_id ,l_pension_entry_values ,P_PERIOD,p_payroll ,
1801 fnd_date.canonical_to_date(p_issue_date),t_string );
1802
1803 l_string := l_string || t_string;
1804
1805 fnd_file.put_line(fnd_file.LOG,'GENERATE_XML xml: '||l_string);
1806
1807
1808 fnd_file.put_line(fnd_file.LOG,'Before Loop End');
1809 ELSE-- Element Attached Check
1810 fnd_file.put_line(fnd_file.LOG,'Pensions Information Element NOT Attached');
1811 --continue;
1812 END IF;
1813 END IF;-- Assignment Set Check
1814
1815 END LOOP;--Loop Every Assignment
1816 /*
1817 IF counter >=1 THEN
1818 l_string := l_string || '</PYGBPAEIG>'||EOL;
1819 END IF;
1820 */
1821 l_string := l_string || '</PYGBPAEIG>'||EOL;
1822 --l_req_id := fnd_global.conc_request_id ;
1823
1824 --fnd_file.put_line(fnd_file.LOG,'Request_id'||l_req_id);
1825 fnd_file.put_line(fnd_file.LOG,'XML '||l_string);
1826 /*
1827 IF l_string is null then
1828 l_string := l_string || '<PYGBPAEIG></PYGBPAEIG>'||EOL;
1829 end if;*/
1830 dbms_lob.createtemporary(l_xml,FALSE,DBMS_LOB.CALL);
1831 dbms_lob.open(l_xml,dbms_lob.lob_readwrite);
1832 dbms_lob.writeAppend( l_xml, length(l_string), l_string);
1833 p_xml := l_xml;
1834 dbms_lob.freeTemporary(l_xml);
1835 END generate_pdf;
1836
1837 END pay_gb_enroll_info_gene;