eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_GB_ENROLL_PEN_REPORTING

Source


1 PACKAGE body PAY_GB_ENROLL_PEN_REPORTING
2 /* $Header: pygbpaer.pkb 120.0.12020000.6 2012/11/21 13:18:13 ssanjays noship $ */
3 AS
4 
5 
6 CURSOR cur_element_entry_assignment (p_end_date date,p_payroll_id pay_all_payrolls_f.payroll_id%type) IS
7 SELECT  pay_element_entries_f.element_entry_id element_entry_id, per_all_assignments_f.assignment_id assignment_id
8     FROM    pay_element_entries_f, pay_element_types_f , per_all_assignments_f,PER_ASSIGNMENT_STATUS_TYPES PAST
9     WHERE   pay_element_entries_f.element_type_id = pay_element_types_f.element_type_id
10     AND per_all_assignments_f.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
11     AND PAST.PER_SYSTEM_STATUS         ='ACTIVE_ASSIGN'
12 		and 		pay_element_entries_f.assignment_id = per_all_assignments_f.assignment_id
13 		and 		pay_element_types_f.ELEMENT_NAME like 'Pensions Information'
14 		and			per_all_assignments_f.payroll_id = p_payroll_id
15 		and 		p_end_date between pay_element_entries_f.effective_start_date and pay_element_entries_f.effective_end_date
16 		and 		p_end_date between pay_element_types_f.effective_start_date and pay_element_types_f.effective_end_date
17 		and 		p_end_date between per_all_assignments_f.effective_start_date and per_all_assignments_f.effective_end_date;
18 
19 cursor cur_input_id is
20 select
21 		MAX(DECODE ( name , 'Qualifying Scheme Name', INPUT_VALUE_ID )) qualifying_scheme_name,
22 		MAX(DECODE ( name , 'Auto Enrollment Date', INPUT_VALUE_ID )) auto_enrollment_date,
23 		MAX(DECODE ( name , 'Pension Classification', INPUT_VALUE_ID )) pension_classification,
24 		MAX(DECODE ( name , 'Opt Out Period End Date', INPUT_VALUE_ID )) opt_out_period_end_date,
25 		MAX(DECODE ( name , 'Total Earning PRP', INPUT_VALUE_ID )) total_earning_PRP,
26     MAX(DECODE ( name , 'Postponement Type', INPUT_VALUE_ID )) postponement_type,
27     MAX(DECODE ( name , 'Postponement End Date', INPUT_VALUE_ID )) postponement_end_date,
28     MAX(DECODE ( name , 'Qualifying scheme exists', INPUT_VALUE_ID )) qualifying_scheme_exists,
29     MAX(DECODE ( name , 'Opt in Date', INPUT_VALUE_ID )) opt_in_Date,
30     MAX(DECODE ( name , 'Eligible Job Holder Date', INPUT_VALUE_ID )) first_found_elig_date,
31     MAX(DECODE ( name , 'Postponement End Date', INPUT_VALUE_ID )) post_period_end_date
32 FROM
33  (SELECT  input_value_id, name from pay_input_values_f);
34 
35 
36 cursor cur_input_id_value(p_input_id cur_input_id%rowtype,p_elem_entry_id pay_element_entry_values_f.element_entry_id%type,p_end_date date) is
37 select
38 		MAX(DECODE ( input_value_id , p_input_id.qualifying_scheme_name, SCREEN_ENTRY_VALUE )) qualifying_scheme_name,
39 		MAX(DECODE ( input_value_id , p_input_id.auto_enrollment_date, SCREEN_ENTRY_VALUE )) auto_enrollment_date,
40 		MAX(DECODE ( input_value_id , p_input_id.pension_classification, SCREEN_ENTRY_VALUE )) pension_classification,
41 		MAX(DECODE ( input_value_id , p_input_id.opt_out_period_end_date, SCREEN_ENTRY_VALUE )) opt_out_period_end_date,
42 		MAX(DECODE ( input_value_id , p_input_id.total_earning_PRP, SCREEN_ENTRY_VALUE )) total_earning_PRP,
43     MAX(DECODE ( input_value_id , p_input_id.postponement_type, SCREEN_ENTRY_VALUE )) postponement_type,
44     MAX(DECODE ( input_value_id , p_input_id.postponement_end_date, SCREEN_ENTRY_VALUE )) postponement_end_date,
45     MAX(DECODE ( input_value_id , p_input_id.qualifying_scheme_exists, SCREEN_ENTRY_VALUE )) qualifying_scheme_exists,
46     MAX(DECODE ( input_value_id , p_input_id.first_found_elig_date, SCREEN_ENTRY_VALUE )) first_found_elig_date,
47 		MAX(DECODE ( input_value_id , p_input_id.opt_in_Date, SCREEN_ENTRY_VALUE ))opt_in_date,
48     MAX(DECODE ( input_value_id , p_input_id.post_period_end_date, SCREEN_ENTRY_VALUE ))post_period_end_date
49 
50 FROM
51  (SELECT  input_value_id,SCREEN_ENTRY_VALUE from pay_element_entry_values_f
52 where element_entry_id = p_elem_entry_id
53 and p_end_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
54  );
55 
56 cursor cur_person_assign_num(p_assignment_id per_all_assignments_f.assignment_id%type,p_end_date date) is
57 SELECT   assignment_number,person_id
58       FROM    per_all_assignments_f
59       WHERE   assignment_id = p_assignment_id
60       AND      p_end_date  between effective_start_date and effective_end_date;
61 
62 cursor cur_get_input_time(p_period number,p_payroll_id per_time_periods.payroll_id%type)  is
63 select start_date,end_date,period_name
64 from per_time_periods
65 where TIME_PERIOD_ID=p_period and payroll_id =p_payroll_id;
66 
67 cursor cur_payroll_name(p_payroll number,p_end_date date)  is
68 select payroll_name from pay_all_payrolls_f where payroll_id = p_payroll
69 AND   p_end_date between effective_start_date and effective_end_date;
70 
71 
72 procedure generate( p_employer         NUMBER
73                    ,p_payroll          NUMBER
74                    ,p_period           NUMBER
75                    ,p_template_name    VARCHAR2
76                    ,p_xml   OUT NOCOPY CLOB)  as
77 
78 l_string                      VARCHAR2(32767) := NULL;
79 l_xml                         CLOB;
80 EOL                   VARCHAR2(5) := fnd_global.local_chr(10);
81 --l_start_date date;-- default to_date ('2012-08-01', 'yyyy-mm-dd');
82 --l_end_date date;-- default to_date ('2012-08-31', 'yyyy-mm-dd');
83 
84 l_cur_element_entry_assignment cur_element_entry_assignment%ROWTYPE;
85 l_full_name per_all_people_f.full_name%TYPE;
86 l_age number;
87 l_auto_enrol_date/*auto_en_date*/ pay_element_entry_values_f.screen_entry_value%TYPE;
88 l_total_earning_prp pay_element_entry_values_f.screen_entry_value%TYPE;
89 l_opt_out_date pay_element_entry_values_f.screen_entry_value%TYPE;
90 l_emp_classification pay_element_entry_values_f.screen_entry_value%TYPE;
91 l_pension_scheme_name pay_element_entry_values_f.screen_entry_value%TYPE;
92 l_payroll_name cur_payroll_name%rowtype;
93 l_period/*p_name*/ cur_get_input_time%rowtype;
94 l_input_id_value cur_input_id_value%rowtype;
95 l_input_id cur_input_id%rowtype;
96 l_person_assign_num cur_person_assign_num%rowtype;
97 
98 
99 begin
100 
101 
102 dbms_lob.createtemporary(l_xml,FALSE,DBMS_LOB.CALL);
103 dbms_lob.open(l_xml,dbms_lob.lob_readwrite);
104 
105 open cur_get_input_time(p_period,p_payroll);
106 fetch cur_get_input_time into  l_period ;
107 close cur_get_input_time;
108 
109 open cur_payroll_name(p_payroll,l_period.end_date);
110 fetch cur_payroll_name into  l_payroll_name ;
111 close cur_payroll_name;
112 
113 
114 open cur_input_id;
115 fetch cur_input_id into l_input_id ;
116 close cur_input_id;
117 
118 hr_utility.trace('...No Postponement present......');
119 
120 if (p_template_name='PYGBPAERE') then
121 		l_string := l_string || '<PYGBPAERE>'||EOL;
122     l_string := l_string || '<P_PAYROLL_ACTION_ID>'||p_payroll||'</P_PAYROLL_ACTION_ID>'||EOL;
123     l_string := l_string || '<P_PAY_START_DATE>'||l_period.start_date||'</P_PAY_START_DATE>'||EOL;
124     l_string := l_string || '<P_PAY_END_DATE>'||l_period.end_date||'</P_PAY_END_DATE>'||EOL;
125     l_string := l_string || '<P_PRP_START_DATE>'||l_period.start_date||'</P_PRP_START_DATE>'||EOL;
126     l_string := l_string || '<P_PRP_END_DATE>'||l_period.end_date||'</P_PRP_END_DATE>'||EOL;
127     l_string := l_string || '<P_MODE>'||0||'</P_MODE>'||EOL;
128     l_string := l_string || '<P_PAYROLL_NAME>'||l_payroll_name.payroll_name||'</P_PAYROLL_NAME>'||EOL;
129     l_string := l_string || '<P_PERIOD_NAME>'||l_period.period_name||'</P_PERIOD_NAME>'||EOL;
130     dbms_lob.writeAppend( l_xml, length(l_string), l_string );
131     l_string := null;
132 
133 OPEN cur_element_entry_assignment(l_period.end_date,p_payroll);
134 
135   FETCH cur_element_entry_assignment
136     INTO    l_cur_element_entry_assignment;
137   CLOSE cur_element_entry_assignment;
138 
139 
140 
141   FOR l_cur_element_entry_assignment IN cur_element_entry_assignment(l_period.end_date,p_payroll) LOOP
142 
143 open cur_input_id_value(l_input_id,l_cur_element_entry_assignment.element_entry_id,l_period.end_date);
144 fetch cur_input_id_value into l_input_id_value;
145 close cur_input_id_value;
146 
147 
148     IF (l_input_id_value.qualifying_scheme_name IS NOT NULL) and
149 (fnd_date.canonical_to_date(l_input_id_value.auto_enrollment_date) between  l_period.start_date and l_period.end_date) THEN
150 
151 			open cur_person_assign_num(l_cur_element_entry_assignment.assignment_id,l_period.end_date);
152 			fetch cur_person_assign_num into l_person_assign_num;
153 			close cur_person_assign_num;
154 
155       SELECT
156               (full_name)
157             , extract (year FROM sysdate)  - extract (year FROM date_of_birth)
158       INTO    l_full_name
159             , l_age
160       FROM    per_all_people_f
161       WHERE   person_id = l_person_assign_num.person_id
162     	AND        l_period.end_date between effective_start_date and effective_end_date;
163 
164  		l_string := l_string || '<G_EMP>'||EOL;
165     l_string := l_string || '<ASSIGNMENT_NUMBER>'||l_person_assign_num.assignment_number||'</ASSIGNMENT_NUMBER>'||EOL;
166     l_string := l_string || '<FULL_NAME>'||l_full_name||'</FULL_NAME>'||EOL;
167     l_string := l_string || '<AGE>'||l_age||'</AGE>'||EOL;
168 		l_string := l_string || '<AUTO_ENROLLEMENT_DATE>'||fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_input_id_value.auto_enrollment_date))||'</AUTO_ENROLLEMENT_DATE>'||EOL;
169     l_string := l_string || '<TOTAL_EAR_PRP>'||l_input_id_value.total_earning_PRP||'</TOTAL_EAR_PRP>'||EOL;/*total_earning_prp*/
170 		l_string := l_string || '<EMPLOYEE_CLASSIFICATION>'||NVL(hr_general.decode_lookup('GB_PENSION_WORKER_TYPES',l_input_id_value.pension_classification),'N/A')||'</EMPLOYEE_CLASSIFICATION>'||EOL;
171 		l_string := l_string || '<OPT_OUT_END_DATE>'||fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_input_id_value.opt_out_period_end_date))||'</OPT_OUT_END_DATE>'||EOL;
172 		l_string := l_string || '<PENSION_SCHEME_NAME>'||l_input_id_value.qualifying_scheme_name||'</PENSION_SCHEME_NAME>'||EOL;
173     l_string := l_string || '</G_EMP>'||EOL;
174 
175     dbms_lob.writeAppend( l_xml, length(l_string), l_string );
176     l_string := null;
177     END IF;
178     END LOOP;
179   l_string := l_string || '</PYGBPAERE>'||EOL;
180 
181     dbms_lob.writeAppend( l_xml, length(l_string), l_string );
182     l_string := null;
183 
184 
185 elsif (p_template_name='PYGBPAEQE') then
186 
187 l_string := l_string || '<PYGBPAEQE>'||EOL;
188     l_string := l_string || '<P_PAYROLL_ACTION_ID>'||p_payroll||'</P_PAYROLL_ACTION_ID>'||EOL;
189     l_string := l_string || '<P_PAY_START_DATE>'||l_period.start_date||'</P_PAY_START_DATE>'||EOL;
190     l_string := l_string || '<P_PAY_END_DATE>'||l_period.end_date||'</P_PAY_END_DATE>'||EOL;
191     l_string := l_string || '<P_PRP_START_DATE>'||l_period.start_date||'</P_PRP_START_DATE>'||EOL;
192     l_string := l_string || '<P_PRP_END_DATE>'||l_period.end_date||'</P_PRP_END_DATE>'||EOL;
193     l_string := l_string || '<P_MODE>'||0||'</P_MODE>'||EOL;
194     l_string := l_string || '<P_PAYROLL_NAME>'||l_payroll_name.payroll_name||'</P_PAYROLL_NAME>'||EOL;
195     l_string := l_string || '<P_PERIOD_NAME>'||l_period.period_name||'</P_PERIOD_NAME>'||EOL;
196 
197     dbms_lob.writeAppend( l_xml, length(l_string), l_string );
198     l_string := null;
199 
200 OPEN cur_element_entry_assignment(l_period.end_date,p_payroll);
201 
202   FETCH cur_element_entry_assignment
203     INTO    l_cur_element_entry_assignment;
204   CLOSE cur_element_entry_assignment;
205 
206   FOR l_cur_element_entry_assignment IN cur_element_entry_assignment(l_period.end_date,p_payroll) LOOP
207 
208 open cur_input_id_value(l_input_id,l_cur_element_entry_assignment.element_entry_id,l_period.end_date);
209 fetch cur_input_id_value into l_input_id_value;
210 close cur_input_id_value;
211 
212 
213     IF ((l_input_id_value.qualifying_scheme_name IS NOT  NULL ) and (l_input_id_value.auto_enrollment_date is null )) THEN
214 
215 			open cur_person_assign_num(l_cur_element_entry_assignment.assignment_id,l_period.end_date);
216 			fetch cur_person_assign_num into l_person_assign_num;
217 			close cur_person_assign_num;
218 
219       SELECT
220               (full_name)
221             , extract (year FROM sysdate)  - extract (year FROM date_of_birth)
222       INTO    l_full_name
223             , l_age
224       FROM    per_all_people_f
225       WHERE   person_id = l_person_assign_num.person_id
226     AND        l_period.end_date between effective_start_date and effective_end_date;
227 
228  		l_string := l_string || '<G_EMP>'||EOL;
229     l_string := l_string || '<ASSIGNMENT_NUMBER>'||l_person_assign_num.assignment_number||'</ASSIGNMENT_NUMBER>'||EOL;
230     l_string := l_string || '<FULL_NAME>'||l_full_name||'</FULL_NAME>'||EOL;
231     l_string := l_string || '<AGE>'||l_age||'</AGE>'||EOL;
232 		l_string := l_string || '<EMPLOYEE_CLASSIFICATION>'||NVL(hr_general.decode_lookup('GB_PENSION_WORKER_TYPES',l_input_id_value.pension_classification),'N/A')||'</EMPLOYEE_CLASSIFICATION>'||EOL;
233 		l_string := l_string || '<PENSION_SCHEME_NAME>'||l_input_id_value.qualifying_scheme_name||'</PENSION_SCHEME_NAME>'||EOL;
234 		--l_string := l_string || '<AUTO_ENROLLEMENT_DATE>'||fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_input_id_value.auto_enrollment_date))||'</AUTO_ENROLLEMENT_DATE>'||EOL;
235     l_string := l_string || '</G_EMP>'||EOL;
236 
237     dbms_lob.writeAppend( l_xml, length(l_string), l_string );
238     l_string := null;
239     END IF;
240     END LOOP;
241   l_string := l_string || '</PYGBPAEQE>'||EOL;
242 
243     dbms_lob.writeAppend( l_xml, length(l_string), l_string );
244     l_string := null;
245 
246 
247 elsif (p_template_name='PYGBPAENRE') then
248 
249     l_string := l_string || '<PYGBPAENRE>'||EOL;
250     l_string := l_string || '<P_PAYROLL_ACTION_ID>'||p_payroll||'</P_PAYROLL_ACTION_ID>'||EOL;
251     l_string := l_string || '<P_PAY_START_DATE>'||l_period.start_date||'</P_PAY_START_DATE>'||EOL;
252     l_string := l_string || '<P_PAY_END_DATE>'||l_period.end_date||'</P_PAY_END_DATE>'||EOL;
253     l_string := l_string || '<P_PRP_START_DATE>'||l_period.start_date||'</P_PRP_START_DATE>'||EOL;
254     l_string := l_string || '<P_PRP_END_DATE>'||l_period.end_date||'</P_PRP_END_DATE>'||EOL;
255     l_string := l_string || '<P_MODE>'||0||'</P_MODE>'||EOL;
256     l_string := l_string || '<P_PAYROLL_NAME>'||l_payroll_name.payroll_name||'</P_PAYROLL_NAME>'||EOL;
257     l_string := l_string || '<P_PERIOD_NAME>'||l_period.period_name||'</P_PERIOD_NAME>'||EOL;
258 
259     dbms_lob.writeAppend( l_xml, length(l_string), l_string );
260     l_string := null;
261 
262 
263 	 OPEN cur_element_entry_assignment(l_period.end_date,p_payroll);
264 	  FETCH cur_element_entry_assignment
265 	    INTO    l_cur_element_entry_assignment;
266 	  CLOSE cur_element_entry_assignment;
267 
268 
269 
270 	  FOR l_cur_element_entry_assignment IN cur_element_entry_assignment(l_period.end_date,p_payroll) LOOP
271 
272 			open cur_input_id_value(l_input_id,l_cur_element_entry_assignment.element_entry_id,l_period.end_date);
273 		  fetch cur_input_id_value into l_input_id_value;
274 		  close cur_input_id_value;
275 
276   		open cur_person_assign_num(l_cur_element_entry_assignment.assignment_id,l_period.end_date);
277 			fetch cur_person_assign_num into l_person_assign_num;
278 			close cur_person_assign_num;
279 
280       SELECT
281               (full_name)
282             , extract (year FROM sysdate)  - extract (year FROM date_of_birth)
283       INTO    l_full_name
284             , l_age
285       FROM    per_all_people_f
286       WHERE   person_id = l_person_assign_num.person_id
287     	AND        l_period.end_date between effective_start_date and effective_end_date;
288 
289 
290 			if (
291 			(fnd_date.canonical_to_date(l_input_id_value.postponement_end_date)  >=l_period.end_date) and
292 			l_input_id_value.auto_enrollment_date is null and
293 			l_input_id_value.postponement_type ='ELIGIBLE JOB HOLDER DEFERMENT'
294 			) then
295 			    l_string := l_string || '<G_EMP1>'||EOL;
296 			    l_string := l_string || '<ASSIGNMENT_NUMBER>'||l_person_assign_num.assignment_number||'</ASSIGNMENT_NUMBER>'||EOL;
297 			    l_string := l_string || '<FULL_NAME>'||l_full_name||'</FULL_NAME>'||EOL;
298 			    l_string := l_string || '<EMPLOYEE_CLASSIFICATION>'||NVL(hr_general.decode_lookup('GB_PENSION_WORKER_TYPES',l_input_id_value.pension_classification),'N/A')||'</EMPLOYEE_CLASSIFICATION>'||EOL;
299 					l_string := l_string || '<FIRST_FOUND_ELIG_DATE>'||fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_input_id_value.first_found_elig_date))||'</FIRST_FOUND_ELIG_DATE>'||EOL;
300 			    l_string := l_string || '<POST_PERIOD_END_DATE>'||fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_input_id_value.post_period_end_date))||'</POST_PERIOD_END_DATE>'||EOL;
301 					l_string := l_string || '</G_EMP1>'||EOL;
302 
303 			    dbms_lob.writeAppend( l_xml, length(l_string), l_string );
304 			    l_string := null;
305 			end if ;
306 
307 			if   (l_input_id_value.pension_classification IN ('NON ELIGIBLE JOB HOLDER','WORKER'))
308 			then
309 			 		l_string := l_string || '<G_EMP2>'||EOL;
310 			    l_string := l_string || '<ASSIGNMENT_NUMBER>'||l_person_assign_num.assignment_number||'</ASSIGNMENT_NUMBER>'||EOL;
311 			    l_string := l_string || '<FULL_NAME>'||l_full_name||'</FULL_NAME>'||EOL;
312 			    l_string := l_string || '<AGE>'||l_age||'</AGE>'||EOL;
313 			    l_string := l_string || '<TOTAL_EAR_PRP>'||NVL(l_input_id_value.total_earning_PRP,'N/A')||'</TOTAL_EAR_PRP>'||EOL;
314 			    l_string := l_string || '<EMPLOYEE_CLASSIFICATION>'||NVL(hr_general.decode_lookup('GB_PENSION_WORKER_TYPES',l_input_id_value.pension_classification),'N/A')||'</EMPLOYEE_CLASSIFICATION>'||EOL;
315 					l_string := l_string || '</G_EMP2>'||EOL;
316 
317 			    dbms_lob.writeAppend( l_xml, length(l_string), l_string );
318 			    l_string := null;
319 			end if;
320 
321 			if (
322          (fnd_date.canonical_to_date(l_input_id_value.postponement_end_date)  >=l_period.end_date)
323 			     --AND l_input_id_value.pension_classification IS NULL
324 					 AND l_input_id_value.auto_enrollment_date IS NULL
325 					 AND l_input_id_value.postponement_type ='WORKER DEFERMENT'
326 			  ) then
327 			    l_string := l_string || '<G_EMP3>'||EOL;
328 			    l_string := l_string || '<ASSIGNMENT_NUMBER>'||l_person_assign_num.assignment_number||'</ASSIGNMENT_NUMBER>'||EOL;
329 			    l_string := l_string || '<FULL_NAME>'||l_full_name||'</FULL_NAME>'||EOL;
330 			    l_string := l_string || '<POST_PERIOD_END_DATE>'||fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_input_id_value.post_period_end_date))||'</POST_PERIOD_END_DATE>'||EOL;
331 			    l_string := l_string || '<EMPLOYEE_CLASSIFICATION>'||NVL(hr_general.decode_lookup('GB_PENSION_WORKER_TYPES',l_input_id_value.pension_classification),'N/A')||'</EMPLOYEE_CLASSIFICATION>'||EOL;
332 					l_string := l_string || '</G_EMP3>'||EOL;
333 
334 			    dbms_lob.writeAppend( l_xml, length(l_string), l_string );
335 			    l_string := null;
336 			end if;
337 
338 		END LOOP;
339   	l_string := l_string || '</PYGBPAENRE>'||EOL;
340 	dbms_lob.writeAppend( l_xml, length(l_string), l_string );
341 	l_string := null;
342 
343 
344 
345 elsif (p_template_name='PYGBPAOIE') then
346 
347     l_string := l_string || '<PYGBPAOIE>'||EOL;
348     l_string := l_string || '<P_PAYROLL_ACTION_ID>'||p_payroll||'</P_PAYROLL_ACTION_ID>'||EOL;
349     l_string := l_string || '<P_PAY_START_DATE>'||l_period.start_date||'</P_PAY_START_DATE>'||EOL;
350     l_string := l_string || '<P_PAY_END_DATE>'||l_period.end_date||'</P_PAY_END_DATE>'||EOL;
351     l_string := l_string || '<P_PRP_START_DATE>'||l_period.start_date||'</P_PRP_START_DATE>'||EOL;
352     l_string := l_string || '<P_PRP_END_DATE>'||l_period.end_date||'</P_PRP_END_DATE>'||EOL;
353     l_string := l_string || '<P_MODE>'||0||'</P_MODE>'||EOL;
354     l_string := l_string || '<P_PAYROLL_NAME>'||l_payroll_name.payroll_name||'</P_PAYROLL_NAME>'||EOL;
355     l_string := l_string || '<P_PERIOD_NAME>'||l_period.period_name||'</P_PERIOD_NAME>'||EOL;
356 
357     dbms_lob.writeAppend( l_xml, length(l_string), l_string );
358     l_string := null;
359 
360 OPEN cur_element_entry_assignment(l_period.end_date,p_payroll);
361 
362   FETCH cur_element_entry_assignment
363     INTO    l_cur_element_entry_assignment;
364   CLOSE cur_element_entry_assignment;
365 
366   FOR l_cur_element_entry_assignment IN cur_element_entry_assignment(l_period.end_date,p_payroll) LOOP
367 
368 			open cur_input_id_value(l_input_id,l_cur_element_entry_assignment.element_entry_id,l_period.end_date);
369 			fetch cur_input_id_value into l_input_id_value;
370 			close cur_input_id_value;
371 
372 
373 			open cur_person_assign_num(l_cur_element_entry_assignment.assignment_id,l_period.end_date);
374 			fetch cur_person_assign_num into l_person_assign_num;
375 			close cur_person_assign_num;
376 
377       SELECT
378               (full_name)
379             , extract (year FROM sysdate)  - extract (year FROM date_of_birth)
380       INTO    l_full_name
381             , l_age
382       FROM    per_all_people_f
383       WHERE   person_id = l_person_assign_num.person_id
384    		AND     l_period.end_date between effective_start_date and effective_end_date;
385 
386 		--	if ( l_input_id_value.pension_classification IN ('Non Eligible Job Holder')
387 					if ( fnd_date.canonical_to_date(l_input_id_value.opt_in_date)
388 					 BETWEEN l_period.start_date AND l_period.end_date) then
389 				l_string := l_string || '<G_EMP4>'||EOL;
390 		    l_string := l_string || '<ASSIGNMENT_NUMBER>'||l_person_assign_num.assignment_number||'</ASSIGNMENT_NUMBER>'||EOL;
391 		    l_string := l_string || '<FULL_NAME>'||l_full_name||'</FULL_NAME>'||EOL;
392 		    l_string := l_string || '<EMPLOYEE_CLASSIFICATION>'||NVL(hr_general.decode_lookup('GB_PENSION_WORKER_TYPES',l_input_id_value.pension_classification),'N/A')||'</EMPLOYEE_CLASSIFICATION>'||EOL;
393 		    l_string := l_string || '<OPT_IN_DATE>'||fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_input_id_value.opt_in_Date))||'</OPT_IN_DATE>'||EOL;
394 				l_string := l_string || '</G_EMP4>'||EOL;
395 
396     		dbms_lob.writeAppend( l_xml, length(l_string), l_string );
397     		l_string := null;
398 	    END IF;
399     END LOOP;
400   l_string := l_string || '</PYGBPAOIE>'||EOL;
401 
402     dbms_lob.writeAppend( l_xml, length(l_string), l_string );
403     l_string := null;
404 end if;
405 
406 
407 --  dbms_lob.createtemporary(l_xml,FALSE,DBMS_LOB.CALL);
408  -- dbms_lob.open(l_xml,dbms_lob.lob_readwrite);
409 --  dbms_lob.writeAppend( l_xml, length(l_string), l_string);
410   p_xml := l_xml;
411   dbms_lob.freeTemporary(l_xml);
412 
413 end generate;
414 END PAY_GB_ENROLL_PEN_REPORTING;