[Home] [Help]
PACKAGE BODY: APPS.PAY_SA_GOSI_REPORTS
Source
1 PACKAGE BODY PAY_SA_GOSI_REPORTS AS
2 /* $Header: pysagosi.pkb 120.14 2010/12/13 06:59:54 bkeshary ship $ */
3 --
4 lg_format_mask varchar2(50);
5 PROCEDURE set_currency_mask(p_business_group_id IN NUMBER) IS
6 /* Cursor to retrieve Currency */
7 CURSOR csr_currency IS
8 SELECT org_information10
9 FROM hr_organization_information
10 WHERE organization_id = p_business_group_id
11 AND org_information_context = 'Business Group Information';
12 l_currency VARCHAR2(40);
13 BEGIN
14 OPEN csr_currency;
15 FETCH csr_currency into l_currency;
16 CLOSE csr_currency;
17 lg_format_mask := FND_CURRENCY.GET_FORMAT_MASK(l_currency,40);
18 END set_currency_mask;
19 PROCEDURE populate_sum(
20 p_request_id IN NUMBER
21 ,p_from_date IN varchar2
22 ,p_to_date IN varchar2
23 ,p_output_fname OUT NOCOPY VARCHAR2)
24 IS
25 l_file_name varchar2(50);
26 l_audit_log_dir varchar2(500);
27 l_from_date date;
28 l_to_date date;
29 l_report varchar2(50);
30 BEGIN
31 /*Msg in the temorary table*/
32 -- To clear the PL/SQL Table values.
33 vXMLTable.DELETE;
34 vCtr := 1;
35 l_from_date := fnd_date.canonical_to_date(p_from_date);
36 l_to_date := fnd_date.canonical_to_date(p_to_date);
37 -- Changing the date parameters from canonical format to date format.
38 --l_from_date:= fnd_date.canonical_to_date(p_from_date);
39 --l_to_date := fnd_date.canonical_to_date(p_to_date);
40 -- Populate the Part 1 of 462 Report
41 fnd_file.put_line(fnd_file.log,'Calling Procedure to Populate New and Terminated Report');
42 /*Write message in temporary table*/
43 /* Hardcode The values for the call to report5*/
44 --populate_new_and_term_wrks(2821,'JAN',2003);
45 /*End of call to report5*/
46 /*Write message in temporary table*/
47 -- Write the values to XML File
48 fnd_file.put_line(fnd_file.log,'Calling Procedure to write into XML File');
49 /* WritetoXML(
50 p_request_id,
51 l_report,
52 l_file_name);*/
53 p_output_fname := l_file_name;
54 fnd_file.put_line(fnd_file.log,'------------Output XML File----------------');
55 fnd_file.put_line(fnd_file.log,'File' || l_file_name );
56 fnd_file.put_line(fnd_file.log,'-------------------------------------------');
57 EXCEPTION
58 WHEN utl_file.invalid_path then
59 hr_utility.set_message(8301, 'GHR_38830_INVALID_UTL_FILE_PATH');
60 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
61 hr_utility.raise_error;
62 --
63 WHEN utl_file.invalid_mode then
64 hr_utility.set_message(8301, 'GHR_38831_INVALID_FILE_MODE');
65 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
66 hr_utility.raise_error;
67 --
68 WHEN utl_file.invalid_filehandle then
69 hr_utility.set_message(8301, 'GHR_38832_INVALID_FILE_HANDLE');
70 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
71 hr_utility.raise_error;
72 --
73 WHEN utl_file.invalid_operation then
74 hr_utility.set_message(8301, 'GHR_38833_INVALID_OPER');
75 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
76 hr_utility.raise_error;
77 --
78 WHEN utl_file.read_error then
79 hr_utility.set_message(8301, 'GHR_38834_FILE_READ_ERROR');
80 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
81 hr_utility.raise_error;
82 --
83 WHEN others THEN
84 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
85 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
86 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
87 hr_utility.raise_error;
88 END populate_sum;
89 --------------------------------------------
90 -- Procedure to populate the new and terminated report
91 PROCEDURE populate_new_and_term_wrks
92 (p_request_id number
93 ,p_report varchar2
94 ,p_business_group_id number
95 ,p_org_structure_version_id number DEFAULT NULL
96 ,p_organisation_id number
97 ,p_effective_month varchar2
98 ,p_effective_year varchar2
99 ,l_xfdf_blob OUT NOCOPY BLOB)
100 IS
101 l_test_new number;
102 l_test_term number;
103 l_defined_balance_id number;
104 l_person_id number;
105 l_person_id_term number;
106 l_assact_id_new number;
107 l_assact_id_term number;
108 l_assign_id number;
109 l_temp number;
110 l_temp_term number;
111 l_sum_term number := 0;
112 l_sum number:= 0;
113 l_count_new number;
114 l_count_term number;
115 l_job varchar2(240);
116 l_join_date date;
117 l_name_new varchar2(240);
118 l_birth_date date;
119 l_nationality varchar2(240);
120 l_nationality_mn varchar2(240);
121 l_passport_number varchar2(240);
122 l_civil_id varchar2(240);
123 l_GOSI_number_new varchar2(240);
124 l_term_date date;
125 l_term_reason varchar2(240);
126 l_term_reason_mn varchar2(240);
127 l_name_term varchar2(240);
128 l_GOSI_number_term varchar2(240);
129 new_count number := 0;
130 term_count number := 0;
131 l_new_count number := 1;
132 l_term_count number := 1;
133 l_employer_GOSI_office varchar2(240);
134 l_employer_name varchar2(240);
135 l_employer_GOSI_code varchar2(240);
136 l_n number :=0;
137 l_t number :=0;
138 l_org_id number;
139 l_effective_date1 date;
140 l_effective_date varchar2(40);
141 l_effective_date_p date;
142 l_effective_month CONSTANT number(2) := p_effective_month;
143 l_effective_year CONSTANT number(4) := p_effective_year ;
144 l_parent_id number;
145 TYPE t_rec_gre IS RECORD(GRE_NAME varchar2(80), GRE_ID number);
146 TYPE t_tab_gre IS TABLE OF t_rec_gre INDEX BY BINARY_INTEGER;
147 t_legal_entity t_tab_gre;
148 l_gre_present number := 0;
149 i number := 0;
150 l_gre_name varchar2(80);
151 l_gre_id number := 0;
152 l_err number := 0;
153 l_tax_unit_id number := 0;
154 l_gosi_office_id number;
155 l_gosi_office varchar2(260);
156 l_gosi_office_code varchar2(20);
157 l_employer_gosi_number varchar2(30);
158 l_leaver_this_month_flag varchar2(1) := 'Y' ;
159 l_joiner_this_month_flag varchar2(1) := 'Y';
160 l_nj_term_date date;
161 l_test_new_assact_id number;
162 l_test_term_assact_id number;
163 l_prev_date date;
164 l_test_prev_month_date varchar2(40);
165 l_test_curr_month_date varchar2(40);
166 l_prev_month varchar2(40);
167 l_last_prev_month_date varchar2(40);
168 l_last_curr_month_date varchar2(40);
169 l_last_prev_month_date1 date;
170 l_input_date varchar2(40);
171 l_temp_new_assgt_id number;
172 l_temp_term_assgt_id number;
173 l_assgt_id_new number;
174 l_assgt_id_term number;
175 l_employer_GOSI_office_name varchar2(240);
176 l_lower_base VARCHAR2(30);
177 l_upper_base VARCHAR2(30);
178 /*Cursor for fetching organizations in the hierarchy*/
179 cursor csr_org_hierarchy is
180 select pose.organization_id_child org
181 from per_org_structure_elements pose
182 connect by pose.organization_id_parent = prior pose.organization_id_child
183 and pose.org_structure_version_id = p_org_structure_version_id
184 start with pose.organization_id_parent = p_organisation_id
185 and pose.org_structure_version_id = p_org_structure_version_id
186 union
187 select p_organisation_id org
188 from dual;
189 rec_org_id csr_org_hierarchy%rowtype;
190 l_file_name varchar2(250);
191 l_audit_log_dir varchar2(500);
192 /*Cursor for fetching gosi office code*/
193 cursor csr_gosi_code(p_GOSI_office_id number) is
194 select org_information1
195 from hr_organization_information
196 where organization_id = p_GOSI_office_id
197 and org_information_context = 'SA_GOSI_OFFICE_DETAILS';
198 -- Cursor to populate Part G5-A-01,G5-A-05
199 cursor get_employer_GOSI (p_org_id number) is
200 select org_information1,org_information2
201 from hr_organization_information
202 where organization_id = p_org_id
203 and org_information_context = 'SA_EMPLOYER_GOSI_DETAILS';
204 --Cursor to get the name of the organization.
205 cursor get_org_name (p_org_id number) is
206 select name
207 from hr_all_organization_units
208 where organization_id = p_org_id;
209 /********************-- Cursor to get person ids of the newly hired employees
210 cursor get_pid_new (p_org_id number,p_date date) is
211 select distinct asg.person_id,paa.assignment_action_id
212 from per_all_assignments_f asg
213 ,pay_assignment_actions paa
214 ,pay_payroll_actions ppa
215 ,hr_soft_coding_keyflex hscl
216 ,per_periods_of_service pos
217 where asg.assignment_id = paa.assignment_id
218 and paa.payroll_action_id = ppa.payroll_action_id
219 and pos.period_of_service_id = asg.period_of_service_id
220 and ppa.action_type in ('R','Q')
221 and ppa.action_status = 'C'
222 and paa.action_status = 'C'
223 and trunc(ppa.date_earned,'MM') = TRUNC(to_date(l_effective_date,'DD-MM-YYYY'), 'MM')
224 and NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY'))
225 not between to_date(l_effective_date,'DD-MM-YYYY') and to_date(l_test_curr_month_date,'DD-MM-YYYY')
226 and trunc(pos.date_start, 'MM') = trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM')
227 and trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM') between asg.effective_start_date and asg.effective_end_date
228 and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
229 and hscl.segment1 = to_char(p_org_id)
230 ORDER BY asg.person_id;
231 -- Cursor to get person ids of terminated employees
232 cursor get_pid_term (p_org_id number,p_date date) is
233 --and trunc(pps.actual_termination_date,'MM') = p_date;
234 select distinct asg.person_id, paa.assignment_action_id
235 from per_all_assignments_f asg
236 ,pay_assignment_actions paa
237 ,pay_payroll_actions ppa
238 ,hr_soft_coding_keyflex hscl
239 ,per_periods_of_service pos
240 where asg.assignment_id = paa.assignment_id
241 and paa.payroll_action_id = ppa.payroll_action_id
242 and pos.period_of_service_id = asg.period_of_service_id
243 and ppa.action_type in ('R','Q')
244 and ppa.action_status = 'C'
245 and paa.action_status = 'C'
246 and trunc(ppa.date_earned,'MM') = TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'), 'MM')
247 and (trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')))
248 between to_date(l_test_prev_month_date,'DD-MM-YYYY') and to_date(l_last_prev_month_date,'DD-MM-YYYY')
249 or
250 trunc(NVL(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))
251 between p_date and to_date(l_test_curr_month_date,'DD-MM-YYYY')
252 )
253 -- and trunc(pos.date_start, 'MM') <> trunc(to_date(l_test_prev_month_date,'DD-MM-YYYY'), 'MM')
254 and
255 (trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM') between asg.effective_start_date and asg.effective_end_date
256 or
257 trunc(to_date(l_test_prev_month_date,'DD-MM-YYYY'),'MM') between asg.effective_start_date and asg.effective_end_date
258 )
259 and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
260 and hscl.segment1 = to_char(p_org_id)
261 ORDER BY asg.person_id;********************/
262 /***Start of new code***/
263 -- Cursor to get person_ids,assignment_ids of newly joined emps
264 cursor get_pid_new (p_org_id number,p_date date) is
265 select distinct asg.person_id,asg.assignment_id from per_all_assignments_f asg
266 ,pay_assignment_actions paa
267 ,pay_payroll_actions ppa
268 ,hr_soft_coding_keyflex hscl
269 ,per_periods_of_service pos
270 where asg.assignment_id = paa.assignment_id
271 and paa.payroll_action_id = ppa.payroll_action_id
272 and pos.period_of_service_id = asg.period_of_service_id
273 and ppa.action_type in ('R','Q')
274 and ppa.action_status = 'C'
275 and paa.action_status IN ('C','S')
276 and trunc(ppa.date_earned,'MM') = trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM')
277 and trunc(pos.date_start, 'MM') = trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM')
278 and trunc(to_date(l_effective_date,'DD-MM-YYYY'),'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
279 and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
280 and hscl.segment1 = to_char(p_org_id)
281 order by asg.person_id;
282 --Cursor to get assignment_action_id of new emps
283 cursor get_assact_id_new (p_org_id number,p_date date,p_person_id number) is
284 select paa.assignment_action_id from per_all_assignments_f asg
285 ,pay_assignment_actions paa
286 ,pay_payroll_actions ppa
287 ,hr_soft_coding_keyflex hscl
288 ,per_periods_of_service pos
289 where rownum < 2
290 and asg.assignment_id = paa.assignment_id
291 and asg.person_id = p_person_id
292 and paa.payroll_action_id = ppa.payroll_action_id
293 and pos.period_of_service_id = asg.period_of_service_id
294 and ppa.action_type in ('R','Q')
295 and ppa.action_status = 'C'
296 and paa.action_status IN ('C','S')
297 and trunc(ppa.date_earned,'MM') = trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM')
298 and trunc(pos.date_start, 'MM') = trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM')
299 and trunc(to_date(l_effective_date,'DD-MM-YYYY'),'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
300 and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
301 and hscl.segment1 = to_char(p_org_id)
302 order by asg.person_id;
303
304
305 -- Cursor to get person_ids,assignment_ids of terminated emps
306 cursor get_pid_term (p_org_id number,p_date date) is
307 --and trunc(pps.actual_termination_date,'MM') = p_date;
308 select /*+ INDEX(hscl, HR_SOFT_CODING_KEYFLEX_PK) */ distinct asg.person_id, asg.assignment_id
309 from per_all_assignments_f asg
310 ,pay_assignment_actions paa
311 ,pay_payroll_actions ppa
312 ,hr_soft_coding_keyflex hscl
313 ,per_periods_of_service pos
314 where asg.assignment_id = paa.assignment_id
315 and paa.payroll_action_id = ppa.payroll_action_id
316 and pos.period_of_service_id = asg.period_of_service_id
317 and ppa.action_type in ('R','Q')
318 and ppa.action_status = 'C'
319 and paa.action_status IN ('C','S')
320 and trunc(ppa.date_earned,'MM') = TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'), 'MM')
321 and (
322 ( trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')))
323 between to_date(l_test_prev_month_date,'DD-MM-YYYY') and to_date(l_last_prev_month_date,'DD-MM-YYYY')
324 )
325 or
326 trunc(NVL(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))
327 between p_date and to_date(l_test_curr_month_date,'DD-MM-YYYY')
328 or
329 (
330 trunc(NVL(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))
331 between TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'),'MM') and to_date(l_last_prev_month_date,'DD-MM-YYYY')
332 AND
333 trunc(pos.date_start, 'MM') = TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'), 'MM')
334 )
335 )
336 and trunc(pos.date_start, 'MM') <> trunc(to_date(l_test_curr_month_date,'DD-MM-YYYY'), 'MM')
337 and
338 (
339 trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
340 or
341 trunc(to_date(l_test_prev_month_date,'DD-MM-YYYY'),'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
342 )
343 and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
344 and hscl.segment1 = to_char(p_org_id)
345 and hscl.id_flex_num = 20
346 ORDER BY asg.person_id;
347
348 -- Cursor to get assignment_action_ids of terminated emps
349 cursor get_assact_id_term (p_org_id number,p_date date,p_person_id number) is
350 select paa.assignment_action_id
351 from per_all_assignments_f asg
352 ,pay_assignment_actions paa
353 ,pay_payroll_actions ppa
354 ,hr_soft_coding_keyflex hscl
355 ,per_periods_of_service pos
356 where rownum < 2
357 and asg.assignment_id = paa.assignment_id
358 and asg.person_id = p_person_id
359 and paa.payroll_action_id = ppa.payroll_action_id
360 and pos.period_of_service_id = asg.period_of_service_id
361 and ppa.action_type in ('R','Q')
362 and ppa.action_status = 'C'
363 and paa.action_status IN ('C','S')
364 and trunc(ppa.date_earned,'MM') = TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'), 'MM')
365 and (
366 ( trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')))
367 between to_date(l_test_prev_month_date,'DD-MM-YYYY') and to_date(l_last_prev_month_date,'DD-MM-YYYY')
368 )
369 or
370 trunc(NVL(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))
371 between p_date and to_date(l_test_curr_month_date,'DD-MM-YYYY')
372 or
373 (
374 trunc(NVL(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))
375 between TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'),'MM') and to_date(l_last_prev_month_date,'DD-MM-YYYY')
376 AND
377 trunc(pos.date_start, 'MM') = TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'), 'MM')
378 )
379 )
380 and trunc(pos.date_start, 'MM') <> trunc(to_date(l_test_curr_month_date,'DD-MM-YYYY'), 'MM')
381 and
382 (
383 trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
384 or
385 trunc(to_date(l_test_prev_month_date,'DD-MM-YYYY'),'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
386 )
387 and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
388 and hscl.segment1 = to_char(p_org_id)
389 ORDER BY asg.person_id;
390 /*****end of new code*******/
391 --cursor to get job name for the newly hired employees
392 cursor get_job(p_assignment_id number,p_date date) is
393 select name
394 from per_jobs pj, per_all_assignments_f paf
395 where pj.job_id = paf.job_id
396 and paf.assignment_id = p_assignment_id
397 and trunc(p_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date;
398 --cursor to get the passport number of the newly hired employees
399 cursor get_passport_number (pn_person_id number,p_date date) is
400 SELECT pei.pei_information1 FROM per_people_extra_info pei
401 WHERE pei.person_id = pn_person_id
402 AND pei.information_type = 'SA_PASSPORT' AND pei.pei_information_category = 'SA_PASSPORT'
403 AND p_date between trunc(fnd_date.canonical_to_date(pei.pei_information3),'MM') and fnd_date.canonical_to_date(pei.pei_information4);
404 --cursor to get the Civil ID
405 cursor get_civil_id (p_person_id number,p_date date) is
406 SELECT NATIONAL_IDENTIFIER from per_all_people_f pap WHERE pap.person_id = p_person_id
407 and trunc(p_date,'MM') between trunc(pap.effective_start_date,'MM') and pap.effective_end_date;
408 --cursor to get the joining date of the newly hired employees
409 cursor get_start_date(p_person_id number) is
410 select date_start from per_periods_of_service where person_id = p_person_id and trunc(date_start,'MM') = to_date(l_effective_date,'DD-MM-YYYY');
411 --cursor to get leaving date for new joinee
412 cursor get_term_date_new (p_person_id number) is
413 select actual_termination_date from per_periods_of_service where person_id = p_person_id;
414 --cursor to get the details of the newly hired employees
415 cursor get_details_new (p_person_id number,p_date date) is
416 select full_name,nationality,date_of_birth from per_all_people_f where person_id = p_person_id
417 and trunc(p_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
418 --cursor to get GOSI number of the employees
419 cursor get_GOSI_number_new (p_person_id number) is
420 select segment2
421 from hr_soft_coding_keyflex hsc, per_all_assignments_f paf
422 where hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
423 and paf.person_id = p_person_id;
424 --cursor to get names of the terminated employees
425 cursor get_name_term (p_person_id number,p_date date) is
426 select full_name from per_all_people_f where person_id = p_person_id
427 and trunc(p_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
428 --cursor to get GOSI numbers of the terminated employees
429 cursor get_GOSI_number_term(p_person_id number) is
430 select segment2
431 from hr_soft_coding_keyflex hsc, per_all_assignments_f paf
432 where hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
433 and paf.person_id = p_person_id;
434 --cursor to get the termination details of the terminated employees
435 cursor get_details_term (p_person_id number) is
436 select actual_termination_date,leaving_reason from per_periods_of_service where person_id = p_person_id;
437 /* Cursor to fetch lower limit of gosi base*/
438 CURSOR get_lower_base(l_effective_date DATE) IS
439 SELECT global_value
440 FROM ff_globals_f
441 WHERE global_name = 'SA_GOSI_BASE_LOWER_LIMIT'
442 AND legislation_code = 'SA'
443 AND business_group_id IS NULL
444 AND l_effective_date BETWEEN effective_start_date
445 AND effective_end_date;
446 /* Cursor to fetch upper limit of gosi base*/
447 CURSOR get_upper_base(l_effective_date DATE) IS
448 SELECT global_value
449 FROM ff_globals_f
450 WHERE global_name = 'SA_GOSI_BASE_UPPER_LIMIT'
451 AND legislation_code = 'SA'
452 AND business_group_id IS NULL
453 AND l_effective_date BETWEEN effective_start_date
454 AND effective_end_date;
455 l_fm_temp varchar2(250);
456 l_fm_sum varchar2(250);
457 l_fm_temp_term varchar2(250);
458 l_fm_sum_term varchar2(250);
459 BEGIN
460 set_currency_mask(p_business_group_id);
461 l_input_date := '01-'||l_effective_month||'-'||p_effective_year;
462 l_effective_date1 := last_day(to_date(l_input_date,'DD-MM-YYYY'));
463 l_effective_date := '01-'||to_char(l_effective_date1,'MM-YYYY');
464 l_effective_date_p := to_date(l_effective_date,'DD-MM-YYYY');
465 --l_effective_date := '01-01-2000';
466 l_prev_date:=add_months(to_date(l_effective_date,'DD-MM-YYYY'),-1);
467 /*Following two lines changed for enhancement 5283457
468 l_test_curr_month_date := '27-'||to_char(l_effective_date1,'MM-YYYY');
469 l_test_prev_month_date := '28-'||to_char(l_prev_date,'MM')||to_char(l_prev_date,'YYYY');*/
470 l_test_curr_month_date := TO_CHAR(l_effective_date1 - 1,'DD-MM-YYYY');
471 l_test_prev_month_date := TO_CHAR(last_day(l_prev_date),'DD-MM-YYYY');
472 --l_test_prev_month_date := '28-01-2000';
473 l_last_prev_month_date1 := (last_day(to_date(l_test_prev_month_date,'DD-MM-YYYY')));
474 l_last_prev_month_date := to_char(l_last_prev_month_date1,'DD-MM-YYYY');
475 --l_last_prev_month_date := '27-12-1999';
476
477 insert into fnd_sessions(session_id,effective_date) values(userenv('sessionid'),to_date(l_effective_date,'DD-MM-YYYY'));
478
479 /*Commented the hierarchy part on 15-Jan-2004 */
480 /*if p_org_structure_version_id is not null then
481 open csr_org_hierarchy;
482 loop
483 fetch csr_org_hierarchy into rec_org_id;
484 exit when csr_org_hierarchy%notfound;
485 hr_sa_org_info.get_employer_name(rec_org_id.org,l_gre_name,p_business_group_id,p_org_structure_version_id);
486 hr_utility.set_location('Before Hierarchy logic',10);
487 begin
488 select organization_id
489 into l_gre_id
490 from hr_all_organization_units
491 where name = l_gre_name
492 and business_group_id = p_business_group_id;
493 exception
494 when others then
495 l_err := 1;
496 end;
497 i := 0;
498 IF t_legal_entity.count <> 0 then
499 --WHILE t_legal_entity.last
500 l_gre_present := 0;
501 FOR i in t_legal_entity.first..t_legal_entity.last
502 LOOP
503 IF t_legal_entity(i).gre_id = l_gre_id THEN
504 l_gre_present := 1;
505 EXIT;
506 END IF;
507 END LOOP;
508 IF l_gre_present = 0 THEN
509 i := t_legal_entity.count;
510 t_legal_entity(i + 1).gre_id := l_gre_id;
511 t_legal_entity(i + 1).gre_name := l_gre_name;
512 END IF;
513 ELSE
514 i := t_legal_entity.count;
515 t_legal_entity(i + 1).gre_id := l_gre_id;
516 t_legal_entity(i + 1).gre_name := l_gre_name;
517 END IF;
518 END LOOP;
519 CLOSE csr_org_hierarchy;
520 end if; --if p_org_structure_version_id is not null then
521 */
522 /*Commented the hierarchy part on 15-Jan-2004 */
523 --Fetch defined_balance_id's
524 if p_org_structure_version_id is null then
525 /*Commented the hierarchy part on 15-Jan-2004 */
526 /*l_gre_id := null;
527 l_gre_name := null;
528 hr_sa_org_info.get_employer_name(p_organisation_id,l_gre_name,p_business_group_id);
529 begin
530 select organization_id
531 into l_gre_id
532 from hr_all_organization_units
533 where name = l_gre_name
534 and business_group_id =p_business_group_id;
535 exception
536 when others then
537 l_err := 1;
538 end;
539 t_legal_entity(1).gre_id := l_gre_id;
540 t_legal_entity(1).gre_name := l_gre_name;*/
541 /*Commented the hierarchy part on 15-Jan-2004 */
542 -- New code begins
543 l_gre_id := p_organisation_id;
544 begin
545 select name
546 into l_gre_name
547 from hr_all_organization_units
548 where organization_id= l_gre_id
549 and business_group_id =p_business_group_id;
550 exception
551 when others then
552 l_err := 1;
553 end;
554 t_legal_entity(1).gre_id := l_gre_id;
555 t_legal_entity(1).gre_name := l_gre_name;
556 -- New code ends
557 end if;
558 vXMLTable.DELETE;
559 vCtr := 1;
560 hr_utility.set_location('Calling Procedure to Populate New and Terminated Report',20);
561 FOR i in t_legal_entity.first..t_legal_entity.last
562 LOOP
563 l_org_id := t_legal_entity(i).gre_id;
564 OPEN get_lower_base(l_effective_date_p);
565 FETCH get_lower_base INTO l_lower_base;
566 CLOSE get_lower_base;
567 OPEN get_upper_base(l_effective_date_p);
568 FETCH get_upper_base INTO l_upper_base;
569 CLOSE get_upper_base;
570 --Get the defined Balance id
571 select u.creator_id
572 into l_defined_balance_id
573 from ff_user_entities u,
574 ff_database_items d
575 where d.user_name = 'GOSI_REFERENCE_EARNINGS_ASG_YTD'
576 and u.user_entity_id = d.user_entity_id
577 and u.legislation_code = 'SA'
578 and u.business_group_id is null
579 and u.creator_type = 'B';
580 --Get the First date of the current month.
581 open get_pid_new(l_org_id,l_effective_date_p);
582 fetch get_pid_new into l_test_new,l_temp_new_assgt_id;
583 open get_pid_term(l_org_id,l_effective_date_p);
584 fetch get_pid_term into l_test_term,l_temp_term_assgt_id;
585 if ((get_pid_new%notfound) and (get_pid_term%notfound)) then
586 close get_pid_term;
587 close get_pid_new;
588 open get_pid_new(l_org_id,l_effective_date_p);
589 open get_pid_term(l_org_id,l_effective_date_p);
590 else
591 close get_pid_new;
592 close get_pid_term;
593 open get_pid_new(l_org_id,l_effective_date_p);
594 open get_pid_term(l_org_id,l_effective_date_p);
595 loop
596 open get_employer_GOSI(l_org_id);
597 fetch get_employer_GOSI into l_employer_GOSI_number,l_employer_GOSI_office;
598 close get_employer_GOSI;
599 begin
600 select name
601 into l_employer_gosi_office_name
602 from hr_organization_units
603 where organization_id = l_employer_gosi_office;
604 exception
605 when others then
606 l_employer_gosi_office_name := null;
607 end;
608 open csr_GOSI_code(l_employer_GOSI_office);
609 fetch csr_GOSI_code into l_employer_GOSI_code;
610 close csr_GOSI_code;
611 vXMLTable(vCtr).TagName := 'INTO LOOP ORG';
612 vXMLTable(vCtr).TagValue := null;
613 vCtr := vCtr + 1;
614 vXMLTable(vCtr).TagName := 'G5-A-01';
615 vXMLTable(vCtr).TagValue := (l_employer_GOSI_office_name);
616 vCtr := vCtr + 1;
617 vXMLTable(vCtr).TagName := 'G5-A-01-1';
618 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_code,1,1);
619 vCtr := vCtr + 1;
620 vXMLTable(vCtr).TagName := 'G5-A-01-2';
621 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_code,2,1);
622 vCtr := vCtr + 1;
623 vXMLTable(vCtr).TagName := 'G5-A-05-1';
624 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,9,1);
625 vCtr := vCtr + 1;
626 vXMLTable(vCtr).TagName := 'G5-A-05-2';
627 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,8,1);
628 vCtr := vCtr + 1;
629 vXMLTable(vCtr).TagName := 'G5-A-05-3';
630 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,7,1);
631 vCtr := vCtr + 1;
632 vXMLTable(vCtr).TagName := 'G5-A-05-4';
633 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,6,1);
634 vCtr := vCtr + 1;
635 vXMLTable(vCtr).TagName := 'G5-A-05-5';
636 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,5,1);
637 vCtr := vCtr + 1;
638 vXMLTable(vCtr).TagName := 'G5-A-05-6';
639 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,4,1);
640 vCtr := vCtr + 1;
641 vXMLTable(vCtr).TagName := 'G5-A-05-7';
642 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,3,1);
643 vCtr := vCtr + 1;
644 vXMLTable(vCtr).TagName := 'G5-A-05-8';
645 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,2,1);
646 vCtr := vCtr + 1;
647 vXMLTable(vCtr).TagName := 'G5-A-05-9';
648 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,1,1);
649 vCtr := vCtr + 1;
650 open get_org_name(l_org_id);
651 fetch get_org_name into l_employer_name;
652 close get_org_name;
653 vXMLTable(vCtr).TagName := 'G5-A-04';
654 vXMLTable(vCtr).TagValue := l_employer_name;
655 vCtr := vCtr + 1;
656 vXMLTable(vCtr).TagName := 'G5-A-02';
657 --vXMLTable(vCtr).TagValue := l_effective_month;
658 vXMLTable(vCtr).TagValue := p_effective_month;
659 vCtr := vCtr + 1;
660 vXMLTable(vCtr).TagName := 'G5-A-03';
661 vXMLTable(vCtr).TagValue := l_effective_year;
662 vCtr := vCtr + 1;
663 vXMLTable(vCtr).TagName := 'start_of_record';
664 vXMLTable(vCtr).TagValue :=null;
665 vCtr := vCtr + 1;
666 loop
667 /*Get person_id and assignment_action_id for new employees*/
668 fetch get_pid_new into l_person_id,l_assgt_id_new;
669 if (get_pid_new %notfound) then
670 l_person_id := null;
671 l_assgt_id_new := null;
672 end if;
673 vXMLTable(vCtr).TagName := 'start_of_B';
674 vXMLTable(vCtr).TagValue := null;
675 vCtr := vCtr + 1;
676 if (get_pid_new%found and l_joiner_this_month_flag = 'Y' ) then
677
678 open get_assact_id_new(l_org_id,l_effective_date_p,l_person_id);
679 fetch get_assact_id_new into l_assact_id_new;
680 close get_assact_id_new;
681 /*Call the package to get the monthly contribution*/
682 if l_assact_id_new is not null then
683
684 l_temp := pay_balance_pkg.get_value(l_defined_balance_id, l_assact_id_new);
685 IF(l_temp > to_number(l_upper_base)) THEN
686 l_temp := to_number(l_upper_base);
687 ELSIF(l_temp < to_number(l_lower_base)) THEN
688 l_temp := to_number(l_lower_base);
689 END IF;
690 end if;
691 if (l_temp >0 ) then
692 l_n := l_n + 1;
693 end if;
694 l_sum := l_sum + l_temp;
695 end if;
696 IF (l_temp > 0) THEN
697
698 vXMLTable(vCtr).TagName := 'G5-B-13'||'-'||l_new_count||'x10';
699 if (get_pid_new %notfound or l_joiner_this_month_flag = 'N') then
700 vXMLTable(vCtr).TagValue := null;
701 else
702 l_fm_temp := null;
703 l_fm_temp := to_char(l_temp,lg_format_mask);
704 --vXMLTable(vCtr).TagValue := trunc(l_temp);
705 vXMLTable(vCtr).TagValue := substr(l_fm_temp,1,length(l_fm_temp)-3);
706 end if;
707 vCtr := vCtr + 1;
708 vXMLTable(vCtr).TagName := 'G5-B-12'||'-'||l_new_count||'x10';
709 if (get_pid_new %notfound or l_joiner_this_month_flag = 'N') then
710 vXMLTable(vCtr).TagValue := null;
711 else
712 --vXMLTable(vCtr).TagValue := l_temp - trunc(l_temp);
713 vXMLTable(vCtr).TagValue := substr(l_fm_temp,length(l_fm_temp)-1);
714 end if;
715 /*vXMLTable(vCtr).TagValue := l_temp - trunc(l_temp);*/
716 vCtr := vCtr + 1;
717 /* Details of the new hired employee*/
718 open get_job (l_assgt_id_new,l_effective_date_p);
719 fetch get_job into l_job;
720 close get_job;
721 vXMLTable(vCtr).TagName := 'G5-B-08'||'-'||l_new_count||'x10';
722 if (get_pid_new %notfound or l_joiner_this_month_flag = 'N') then
723 vXMLTable(vCtr).TagValue := null;
724 else
725 vXMLTable(vCtr).TagValue := l_job;
726 end if;
727 vCtr := vCtr + 1;
728 open get_details_new(l_person_id,l_effective_date_p);
729 fetch get_details_new into l_name_new,l_nationality,l_birth_date;
730 close get_details_new;
731 vXMLTable(vCtr).TagName := 'G5-B-01'||'-'||l_new_count||'x10';
732 if (get_pid_new %notfound or l_joiner_this_month_flag = 'N') then
733 vXMLTable(vCtr).TagValue := null;
734 else
735 vXMLTable(vCtr).TagValue := l_name_new;
736 end if;
737 vCtr := vCtr + 1;
738 vXMLTable(vCtr).TagName := 'G5-B-03'||'-'||l_new_count||'x10';
739 if (get_pid_new %notfound or l_joiner_this_month_flag = 'N') then
740 vXMLTable(vCtr).TagValue := null;
741 else
742 begin
743 SELECT meaning INTO l_nationality_mn
744 FROM HR_LOOKUPS H, FND_SESSIONS S
745 WHERE LOOKUP_TYPE = 'NATIONALITY'
746 AND ENABLED_FLAG = 'Y'
747 AND LOOKUP_CODE = l_nationality
748 AND SESSION_ID = USERENV('SESSIONID')
749 AND S.EFFECTIVE_DATE BETWEEN NVL(H.START_DATE_ACTIVE, S.EFFECTIVE_DATE)
750 AND NVL(END_DATE_ACTIVE, S.EFFECTIVE_DATE)
751 ORDER BY MEANING;
752 exception
753 when others then
754 null;
755 end;
756 vXMLTable(vCtr).TagValue := l_nationality_mn;
757 end if;
758 vCtr := vCtr + 1;
759 vXMLTable(vCtr).TagName := 'G5-B-07'||'-'||l_new_count||'x10';
760 if (get_pid_new %notfound or l_joiner_this_month_flag = 'N') then
761 vXMLTable(vCtr).TagValue := null;
762 else
763 vXMLTable(vCtr).TagValue :=substr(to_char(l_birth_date,'DD-MM-YYYY'),1,2);
764 end if;
765 vCtr := vCtr + 1;
766 vXMLTable(vCtr).TagName := 'G5-B-06'||'-'||l_new_count||'x10';
767 if (get_pid_new %notfound or l_joiner_this_month_flag = 'N') then
768 vXMLTable(vCtr).TagValue := null;
769 else
770 vXMLTable(vCtr).TagValue :=substr(to_char(l_birth_date,'DD-MM-YYYY'),4,2);
771 end if;
772 vCtr := vCtr + 1;
773 vXMLTable(vCtr).TagName := 'G5-B-05'||'-'||l_new_count||'x10';
774 if (get_pid_new %notfound or l_joiner_this_month_flag = 'N') then
775 vXMLTable(vCtr).TagValue := null;
776 else
777 vXMLTable(vCtr).TagValue :=substr(to_char(l_birth_date,'DD-MM-YYYY'),7,4);
778 end if;
779 vCtr := vCtr + 1;
780 vXMLTable(vCtr).TagName := 'G5-B-04'||'-'||l_new_count||'x10';
781 /*Check for Civil Id for Saudi nationals and Passport Number for Non Saudis.*/
782 if (get_pid_new%found) then
783 if (upper(l_nationality) = UPPER(FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY'))) then
784 l_civil_id := null;
785 open get_civil_id(l_person_id,l_effective_date_p);
786 fetch get_civil_id into l_civil_id;
787 close get_civil_id;
788 if (l_civil_id is not null) then
789 vXMLTable(vCtr).TagValue := l_civil_id;
790 else
791 vXMLTable(vCtr).TagValue := null;
792 end if;
793 else
794 l_passport_number := null;
795 open get_passport_number(l_person_id,l_effective_date_p);
796 fetch get_passport_number into l_passport_number;
797 close get_passport_number;
798 if (l_passport_number is not null) then
799 vXMLTable(vCtr).TagValue := l_passport_number;
800 else
801 vXMLTable(vCtr).TagValue := null;
802 end if;
803 end if;
804 end if;
805 /*ENd of Check for Civil Id for Saudi nationals and Passport Number for Non Saudis.*/
806 vCtr := vCtr + 1;
807 open get_start_date(l_person_id);
808 fetch get_start_date into l_join_date;
809 close get_start_date;
810 vXMLTable(vCtr).TagName := 'G5-B-11'||'-'||l_new_count||'x10';
811 if (get_pid_new %notfound or l_joiner_this_month_flag = 'N') then
812 vXMLTable(vCtr).TagValue := null;
813 else
814 vXMLTable(vCtr).TagValue := substr(to_char(l_join_date,'DD-MM-YYYY'),1,2);
815 end if;
816 vCtr := vCtr + 1;
817 vXMLTable(vCtr).TagName := 'G5-B-10'||'-'||l_new_count||'x10';
818 if (get_pid_new %notfound or l_joiner_this_month_flag = 'N') then
819 vXMLTable(vCtr).TagValue := null;
820 else
821 vXMLTable(vCtr).TagValue := substr(to_char(l_join_date,'DD-MM-YYYY'),4,2);
822 end if;
823 vCtr := vCtr + 1;
824 vXMLTable(vCtr).TagName := 'G5-B-09'||'-'||l_new_count||'x10';
825 if (get_pid_new %notfound or l_joiner_this_month_flag = 'N') then
826 vXMLTable(vCtr).TagValue := null;
827 else
828 vXMLTable(vCtr).TagValue := substr(to_char(l_join_date,'DD-MM-YYYY'),7,4);
829 end if;
830 vCtr := vCtr + 1;
831 open get_GOSI_number_new(l_person_id);
832 fetch get_GOSI_number_new into l_GOSI_number_new;
833 close get_GOSI_number_new;
834 vXMLTable(vCtr).TagName := 'G5-B-02'||'-'||l_new_count||'x10';
835 if (get_pid_new %notfound or l_joiner_this_month_flag = 'N') then
836 vXMLTable(vCtr).TagValue := null;
837 else
838 vXMLTable(vCtr).TagValue := l_GOSI_number_new;
839 end if;
840 vCtr := vCtr + 1;
841 vXMLTable(vCtr).TagName := 'end_of_B';
842 vXMLTable(vCtr).TagValue := null;
843 vCtr := vCtr + 1;
844 l_new_count := l_new_count + 1;
845 new_count := new_count + 1;
846 END IF;
847 if (l_new_count = 11 or get_pid_new % notfound) then
848 vXMLTable(vCtr).TagName := 'G5-B-10-11x11';
849 if (l_n <>0) then
850 vXMLTable(vCtr).TagValue := l_n;
851 else
852 vXMLTable(vCtr).TagValue := null;
853 end if;
854 vCtr := vCtr + 1;
855 if (l_sum <>0 ) then
856 l_fm_sum := null;
857 l_fm_sum := to_char(l_sum,lg_format_mask);
858 vXMLTable(vCtr).TagName := 'G5-B-13-11x11';
859 --vXMLTable(vCtr).TagValue := trunc(l_sum);
860 vXMLTable(vCtr).TagValue := substr(l_fm_sum,1,length(l_fm_sum)-3);
861 vCtr := vCtr + 1;
862 vXMLTable(vCtr).TagName := 'G5-B-12-11x11';
863 --vXMLTable(vCtr).TagValue := l_sum-trunc(l_sum);
864 vXMLTable(vCtr).TagValue := substr(l_fm_sum,length(l_fm_sum)-1);
865 vCtr := vCtr + 1;
866 end if;
867 end if;
868 exit when (l_new_count = 11 or get_pid_new % notfound);
869 end loop;
870 loop
871 fetch get_pid_term into l_person_id_term,l_assgt_id_term;
872 if (get_pid_term %notfound) then
873 l_person_id_term := null;
874 l_assgt_id_term := null;
875 end if;
876
877 vXMLTable(vCtr).TagName := 'start_of_C';
878 vXMLTable(vCtr).TagValue := null;
879 vCtr := vCtr + 1;
880 if(get_pid_term%found) then
881 open get_assact_id_term(l_org_id,to_date(l_effective_date,'DD-MM-YYYY'),l_person_id_term);
882 fetch get_assact_id_term into l_assact_id_term;
883 close get_assact_id_term;
884 if l_assact_id_term is not null then
885
886 l_temp_term := pay_balance_pkg.get_value(l_defined_balance_id, l_assact_id_term);
887 IF(l_temp_term > to_number(l_upper_base)) THEN
888 l_temp_term := to_number(l_upper_base);
889 ELSIF(l_temp_term < to_number(l_lower_base)) THEN
890 l_temp_term := to_number(l_lower_base);
891 END IF;
892 end if;
893 l_sum_term := l_sum_term + l_temp_term;
894 end if;
895 IF (l_temp_term >0 ) THEN
896
897 open get_details_term(l_person_id_term);
898 fetch get_details_term into l_term_date,l_term_reason;
899 close get_details_term;
900 vXMLTable(vCtr).TagName := 'G5-C-06'||'-'||l_term_count||'x10';
901 if (get_pid_term %found and l_leaver_this_month_flag = 'Y' ) then
902 l_t := l_t + 1;
903 end if;
904 if (get_pid_term %notfound or l_leaver_this_month_flag = 'N' ) then
905 vXMLTable(vCtr).TagValue := null;
906 else
907 vXMLTable(vCtr).TagValue := substr(to_char(l_term_date,'DD-MM-YYYY'),1,2);
908 end if;
909 vCtr := vCtr + 1;
910 vXMLTable(vCtr).TagName := 'G5-C-05'||'-'||l_term_count||'x10';
911 if (get_pid_term %notfound or l_leaver_this_month_flag = 'N' ) then
912 vXMLTable(vCtr).TagValue := null;
913 else
914 vXMLTable(vCtr).TagValue := substr(to_char(l_term_date,'DD-MM-YYYY'),4,2);
915 end if;
916 vCtr := vCtr + 1;
917 vXMLTable(vCtr).TagName := 'G5-C-04'||'-'||l_term_count||'x10';
918 if (get_pid_term %notfound or l_leaver_this_month_flag = 'N' ) then
919 vXMLTable(vCtr).TagValue := null;
920 else
921 vXMLTable(vCtr).TagValue := substr(to_char(l_term_date,'DD-MM-YYYY'),7,4);
922 end if;
923 vCtr := vCtr + 1;
924 /*call to the package to get monthly contribution*/
925 vXMLTable(vCtr).TagName := 'G5-C-08'||'-'||l_term_count||'x10';
926 if (get_pid_term %notfound or l_leaver_this_month_flag = 'N' ) then
927 vXMLTable(vCtr).TagValue := null;
928 else
929 l_fm_temp_term := null;
930 l_fm_temp_term := to_char(l_temp_term,lg_format_mask);
931 --vXMLTable(vCtr).TagValue := l_temp_term;
932 vXMLTable(vCtr).TagValue := substr(l_fm_temp_term,1,length(l_fm_temp_term)-3);
933 end if;
934 vCtr := vCtr + 1;
935 vXMLTable(vCtr).TagName := 'G5-C-07'||'-'||l_term_count||'x10';
936 if (get_pid_term %notfound or l_leaver_this_month_flag = 'N' ) then
937 vXMLTable(vCtr).TagValue := null;
938 else
939 --vXMLTable(vCtr).TagValue := l_temp_term - trunc(l_temp_term);
940 vXMLTable(vCtr).TagValue := substr(l_fm_temp_term,length(l_fm_temp_term)-1);
941 end if;
942 vCtr := vCtr + 1;
943 open get_name_term(l_person_id_term,to_date(l_effective_date,'DD-MM-YYYY'));
944 fetch get_name_term into l_name_term;
945 close get_name_term;
946 vXMLTable(vCtr).TagName := 'G5-C-01'||'-'||l_term_count||'x10';
947 if (get_pid_term %notfound or l_leaver_this_month_flag = 'N' ) then
948 vXMLTable(vCtr).TagValue := null;
949 else
950 vXMLTable(vCtr).TagValue := l_name_term;
951 end if;
952 vCtr := vCtr + 1;
953 open get_GOSI_number_term (l_person_id_term);
954 fetch get_GOSI_number_term into l_GOSI_number_term;
955 close get_GOSI_number_term;
956 vXMLTable(vCtr).TagName := 'G5-C-02'||'-'||l_term_count||'x10';
957 if (get_pid_term %notfound or l_leaver_this_month_flag = 'N' ) then
958 vXMLTable(vCtr).TagValue := null;
959 else
960 vXMLTable(vCtr).TagValue := l_GOSI_number_term;
961 end if;
962 vCtr := vCtr + 1;
963 vXMLTable(vCtr).TagName := 'G5-C-03'||'-'||l_term_count||'x10';
964 if (get_pid_term %notfound or l_leaver_this_month_flag = 'N' ) then
965 vXMLTable(vCtr).TagValue := null;
966 else
967 /*SELECT meaning INTO l_term_reason_mn
968 FROM hr_lookups hl
969 WHERE hl.lookup_type = 'LEAV_REAS'
970 AND to_date(l_effective_date'DD-MM-YYYY')
971 between nvl(hl.start_date_active,to_date( and hl.end_date_active
972 AND hl.lookup_code = l_term_reason;*/
973 BEGIN
974 SELECT meaning INTO l_term_reason_mn
975 FROM HR_LOOKUPS H, FND_SESSIONS S
976 WHERE LOOKUP_TYPE = 'LEAV_REAS'
977 AND ENABLED_FLAG = 'Y'
978 AND LOOKUP_CODE = l_term_reason
979 AND SESSION_ID = USERENV('SESSIONID')
980 AND S.EFFECTIVE_DATE BETWEEN NVL(H.START_DATE_ACTIVE, S.EFFECTIVE_DATE)
981 AND NVL(END_DATE_ACTIVE, S.EFFECTIVE_DATE)
982 ORDER BY MEANING;
983 EXCEPTION
984 WHEN OTHERS THEN
985 l_term_reason_mn := NULL;
986 END;
987 vXMLTable(vCtr).TagValue := l_term_reason_mn;
988 end if;
989 vCtr := vCtr + 1;
990 vXMLTable(vCtr).TagName := 'end_of_C';
991 vXMLTable(vCtr).TagValue := null;
992 vCtr := vCtr + 1;
993 l_term_count := l_term_count + 1;
994 term_count := term_count+1;
995 END IF;
996 if (l_term_count = 11 or get_pid_term % notfound or l_leaver_this_month_flag = 'N' ) then
997 vXMLTable(vCtr).TagName := 'G5-C-09-11x11';
998 if(l_t <>0) then
999 vXMLTable(vCtr).TagValue := l_t;
1000 else
1001 vXMLTable(vCtr).TagValue := null;
1002 end if;
1003 vCtr := vCtr + 1;
1004 if (l_sum_term <> 0) then
1005 l_fm_sum_term := null;
1006 l_fm_sum_term := to_char(l_sum_term,lg_format_mask);
1007 vXMLTable(vCtr).TagName := 'G5-C-08-11x11';
1008 --vXMLTable(vCtr).TagValue := trunc(l_sum_term);
1009 vXMLTable(vCtr).TagValue := substr(l_fm_sum_term,1,length(l_fm_sum_term)-3);
1010 vCtr := vCtr + 1;
1011 vXMLTable(vCtr).TagName := 'G5-C-07-11x11';
1012 --vXMLTable(vCtr).TagValue := l_sum_term-trunc(l_sum_term);
1013 vXMLTable(vCtr).TagValue := substr(l_fm_sum_term,length(l_fm_sum_term)-1);
1014 vCtr := vCtr + 1;
1015 end if;
1016 end if;
1017 exit when (l_term_count = 11 or get_pid_term % notfound);
1018 end loop;
1019 l_n := 0;
1020 l_t := 0;
1021 l_sum :=0;
1022 l_sum_term:=0;
1023 hr_utility.set_location('Finished populating New and Terminated Report ',30);
1024 new_count := 0;
1025 term_count :=0;
1026 l_new_count := 1;
1027 l_term_count := 1;
1028 vXMLTable(vCtr).TagName := 'end_of_record';
1029 vXMLTable(vCtr).TagValue :=null;
1030 vCtr := vCtr + 1;
1031 exit when (get_pid_new%NOTFOUND and get_pid_term%NOTFOUND);
1032 end loop;
1033 end if;
1034 close get_pid_new;
1035 close get_pid_term;
1036 END LOOP;
1037 /*Msg in the temorary table*/
1038 WritetoCLOB ( l_xfdf_blob );
1039 -- Write the values to XML File
1040 /* fnd_file.put_line(fnd_file.log,'Calling Procedure to write into XML File');
1041 WritetoXML(
1042 p_request_id,
1043 p_report,
1044 p_output_fname);
1045 --p_output_fname := l_file_name;
1046 fnd_file.put_line(fnd_file.log,'------------Output XML File----------------');
1047 fnd_file.put_line(fnd_file.log,'File' || p_output_fname );
1048 fnd_file.put_line(fnd_file.log,'-------------------------------------------');*/
1049 /*
1050 EXCEPTION
1051 WHEN utl_file.invalid_path then
1052 hr_utility.set_message(8301, 'GHR_38830_INVALID_UTL_FILE_PATH');
1053 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1054 hr_utility.raise_error;
1055 --
1056 WHEN utl_file.invalid_mode then
1057 hr_utility.set_message(8301, 'GHR_38831_INVALID_FILE_MODE');
1058 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1059 hr_utility.raise_error;
1060 --
1061 WHEN utl_file.invalid_filehandle then
1062 hr_utility.set_message(8301, 'GHR_38832_INVALID_FILE_HANDLE');
1063 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1064 hr_utility.raise_error;
1065 --
1066 WHEN utl_file.invalid_operation then
1067 hr_utility.set_message(8301, 'GHR_38833_INVALID_OPER');
1068 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1069 hr_utility.raise_error;
1070 --
1071 WHEN utl_file.read_error then
1072 hr_utility.set_message(8301, 'GHR_38834_FILE_READ_ERROR');
1073 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1074 hr_utility.raise_error;
1075 --
1076 WHEN others THEN
1077 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
1078 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
1079 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
1080 hr_utility.raise_error;
1081 */
1082 END populate_new_and_term_wrks;
1083 -------------------------------------------
1084 ----------------------------------------------
1085 procedure populate_monthly_contributions
1086 (p_request_id number
1087 ,p_report varchar2
1088 ,p_business_group_id number
1089 ,p_org_structure_version_id number default null
1090 ,p_organisation_id number
1091 ,p_effective_month varchar2
1092 ,p_effective_year varchar2
1093 ,p_arrears number default 0
1094 ,p_penalty_charge number default 0
1095 ,p_discount number default 0
1096 ,p_payment_method varchar2 default null
1097 ,l_xfdf_blob OUT NOCOPY BLOB) as
1098 l_input_date varchar2(30);
1099 l_parent_id number;
1100 l_payroll_id number;
1101 l_payroll_name number;
1102 l_effective_date date;
1103 l_eff_term_date date;
1104 l_prev_mon_date date;
1105 l_prev_term_date date;
1106 l_prev2_mon_date date;
1107 l_prev2_term_date date;
1108 --l_payroll_action_id number;
1109 --l_assignment_action_id number;
1110 --l_saudi_national varchar2(1) := 'N';
1111 l_def_nationality_cd VARCHAR2(80);
1112 l_def_nationality VARCHAR2(80);
1113 --l_per_nationality VARCHAR2(80);
1114 l_haz_month_db number;
1115 l_ann_month_db number;
1116 --l_g_annu_db number;
1117 --l_g_haz_db number;
1118 --l_hazards number := 0;
1119 l_hazards_new number := 0;
1120 l_hazards_ter number := 0;
1121 --l_non_hazards number := 0;
1122 --l_non_hazards_new number := 0;
1123 --l_non_hazards_ter number := 0;
1124 l_hazards_new_all number := 0;
1125 l_hazards_ter_all number := 0;
1126 --l_annuities_saudi number := 0;
1127 --l_hazards_saudi number := 0;
1128 l_annuities_saudi_new number := 0;
1129 l_hazards_saudi_new number := 0;
1130 l_annuities_saudi_ter number := 0;
1131 l_hazards_saudi_ter number := 0;
1132 --l_haz_ann_saudi number := 0;
1133 l_haz_ann_saudi_new number := 0;
1134 l_haz_ann_saudi_ter number := 0;
1135 l_hc_haz number := 0;
1136 l_hc_haz_new number := 0;
1137 l_hc_haz_ter number := 0;
1138 l_hc_ann_saudi number := 0;
1139 l_hc_haz_saudi number := 0;
1140 l_hc_ann_saudi_new number := 0;
1141 l_hc_haz_saudi_new number := 0;
1142 l_hc_ann_saudi_ter number := 0;
1143 l_hc_haz_saudi_ter number := 0;
1144 l_hc_haz_ann_saudi number := 0;
1145 l_hc_haz_ann_saudi_new number := 0;
1146 l_hc_haz_ann_saudi_ter number := 0;
1147 l_hc_haz_non number := 0;
1148 l_hc_haz_non_new number := 0;
1149 l_hc_haz_non_ter number := 0;
1150 --l_curr_ann number := 0;
1151 l_curr_ann_saudi number := 0;
1152 l_curr_ann_saudi_new number := 0;
1153 l_curr_ann_saudi_new_ter number := 0;
1154 l_curr_ann_saudi_ter number := 0;
1155 l_curr_haz number := 0;
1156 l_curr_haz_saudi number := 0;
1157 l_curr_haz_saudi_new number := 0;
1158 l_curr_haz_new number := 0;
1159 l_curr_haz_new_all number := 0;
1160 l_curr_haz_saudi_ter number := 0;
1161 l_curr_haz_saudi_new_ter number := 0;
1162 l_curr_haz_ter number := 0;
1163 l_curr_haz_ter_all number := 0;
1164 l_curr_haz_new_ter number := 0;
1165 l_curr_haz_new_ter_all number := 0;
1166 l_curr_haz_all number := 0;
1167 l_curr_annuities number := 0;
1168 l_curr_hazards number := 0;
1169 l_curr_haz_ann number := 0;
1170 l_curr_haz_ann_saudi number := 0;
1171 l_curr_haz_ann_saudi_new number := 0;
1172 l_curr_haz_ann_saudi_ter number := 0;
1173 l_curr_haz_ann_saudi_new_ter number := 0;
1174 l_tot_curr_ann_saudi number := 0;
1175 l_tot_curr_haz_all number := 0;
1176 l_tot_curr_haz_ann_saudi number := 0;
1177 l_tot_prev_ann_saudi number := 0;
1178 l_tot_prev_haz_all number := 0;
1179 l_tot_prev_haz_ann_saudi number := 0;
1180 l_prev_ann_saudi number := 0;
1181 l_prev_ann_saudi_new number := 0;
1182 l_prev_ann_saudi_ter number := 0;
1183 l_prev_haz_all number := 0;
1184 l_prev_haz_new_all number := 0;
1185 l_prev_haz_ter_all number := 0;
1186 l_prev_haz_ann_saudi number := 0;
1187 l_prev_haz_ann_saudi_new number := 0;
1188 l_prev_haz_ann_saudi_ter number := 0;
1189 l_prev_haz_saudi number := 0;
1190 l_prev_haz number := 0;
1191 l_prev_haz_saudi_new number := 0;
1192 l_prev_haz_new number := 0;
1193 l_prev_haz_saudi_ter number := 0;
1194 l_prev_haz_ter number := 0;
1195 l_prev_ann_saudi_new_ter number := 0;
1196 l_prev_haz_saudi_new_ter number := 0;
1197 l_prev_haz_ann_saudi_new_ter number := 0;
1198 l_prev_haz_new_ter number := 0;
1199 l_prev_haz_new_ter_all number := 0;
1200 l_annuities_saudi_new_ter number := 0;
1201 l_hazards_saudi_new_ter number := 0;
1202 l_hazards_new_ter number := 0;
1203 --l_hazards_saudi_new_ter number := 0;
1204 l_tot_ann_saudi number := 0;
1205 l_tot_haz_all number := 0;
1206 l_tot_haz_ann_saudi number := 0;
1207 l_hc_prev_ann_saudi_nn number := 0;
1208 l_hc_prev_ann_saudi_ny number := 0;
1209 l_hc_prev_ann_saudi_yn number := 0;
1210 l_hc_prev_ann_saudi_yy number := 0;
1211 l_hc_prev_haz_saudi_nn number := 0;
1212 l_hc_prev_haz_saudi_ny number := 0;
1213 l_hc_prev_haz_saudi_yn number := 0;
1214 l_hc_prev_haz_saudi_yy number := 0;
1215 l_hc_prev_haz_non_nn number := 0;
1216 l_hc_prev_haz_non_ny number := 0;
1217 l_hc_prev_haz_non_yn number := 0;
1218 l_hc_prev_haz_non_yy number := 0;
1219 l_hc_prev_haz_ann_saudi_nn number := 0;
1220 l_hc_prev_haz_ann_saudi_yn number := 0;
1221 l_hc_prev_haz_ann_saudi_yy number := 0;
1222 l_hc_prev_haz_ann_saudi_ny number := 0;
1223 l_hc_curr_ann_saudi_nn number := 0;
1224 l_hc_curr_ann_saudi_ny number := 0;
1225 l_hc_curr_ann_saudi_yn number := 0;
1226 l_hc_curr_ann_saudi_yy number := 0;
1227 l_hc_curr_haz_saudi_nn number := 0;
1228 l_hc_curr_haz_saudi_ny number := 0;
1229 l_hc_curr_haz_saudi_yn number := 0;
1230 l_hc_curr_haz_saudi_yy number := 0;
1231 l_hc_curr_haz_non_nn number := 0;
1232 l_hc_curr_haz_non_ny number := 0;
1233 l_hc_curr_haz_non_yn number := 0;
1234 l_hc_curr_haz_non_yy number := 0;
1235 l_hc_curr_haz_ann_saudi_nn number := 0;
1236 l_hc_curr_haz_ann_saudi_yn number := 0;
1237 l_hc_curr_haz_ann_saudi_yy number := 0;
1238 l_hc_curr_haz_ann_saudi_ny number := 0;
1239 l_hc_haz_new_all number := 0;
1240 l_hc_haz_ter_all number := 0;
1241 l_hc_tot_ann_saudi number := 0;
1242 l_hc_tot_haz number := 0;
1243 l_hc_tot_haz_ann_saudi number := 0;
1244 TYPE t_rec_gre IS RECORD(GRE_NAME varchar2(80), GRE_ID number);
1245 /*Table type for variable for storing legal entities within the hierarchy*/
1246 TYPE t_tab_gre IS TABLE OF t_rec_gre
1247 INDEX BY BINARY_INTEGER;
1248 t_legal_entity t_tab_gre;
1249 l_gre_present number := 0;
1250 i number := 0;
1251 l_gre_name varchar2(80);
1252 l_gre_id number := 0;
1253 l_err number := 0;
1254 l_tax_unit_id number := 0;
1255 l_gosi_office_id number;
1256 l_gosi_office varchar2(260);
1257 l_gosi_office_code varchar2(30);
1258 l_employer_gosi_number varchar2(30);
1259 /*p_l_fp1 UTL_FILE.FILE_TYPE;
1260 l_audit_log_dir1 varchar2(500) := '/sqlcom/outbound';
1261 l_file_name1 varchar2(50);
1262 l_check_flag1 number;
1263 l_file_created number := 0;*/
1264 /*Cursor for fetching organizations in the hierarchy*/
1265 cursor csr_org_hierarchy is
1266 select pose.organization_id_child org
1267 from per_org_structure_elements pose
1268 connect by pose.organization_id_parent = prior pose.organization_id_child
1269 and pose.org_structure_version_id = p_org_structure_version_id
1270 start with pose.organization_id_parent = (nvl(p_organisation_id,l_parent_id))
1271 and pose.org_structure_version_id = p_org_structure_version_id
1272 union
1273 select (nvl(p_organisation_id,l_parent_id)) org
1274 from dual;
1275 rec_org_id csr_org_hierarchy%rowtype;
1276 /*Cursor for fetching gosi office details*/
1277 cursor csr_gosi_office_details is
1278 select org_information1
1279 ,org_information2
1280 from hr_organization_information
1281 where organization_id = l_tax_unit_id
1282 and org_information_context = 'SA_EMPLOYER_GOSI_DETAILS';
1283 rec_gosi_office_details csr_gosi_office_details%rowtype;
1284 /*Cursor for fetching gosi office code*/
1285 cursor csr_gosi_code is
1286 select org_information1
1287 from hr_organization_information
1288 where organization_id = l_gosi_office_id
1289 and org_information_context = 'SA_GOSI_OFFICE_DETAILS';
1290 rec_gosi_code csr_gosi_code%rowtype;
1291 l_file_name varchar2(50);
1292 l_audit_log_dir varchar2(500);
1293 /*Variables required for amount formatting*/
1294 l_fm_tot_prev_ann_saudi varchar2(50) := null;
1295 l_fm_tot_prev_haz_all varchar2(50) := null;
1296 l_fm_tot_prev_haz_ann_saudi varchar2(50) := null;
1297 l_fm_annuities_saudi_new varchar2(50) := null;
1298 l_fm_hazards_new_all varchar2(50) := null;
1299 l_fm_haz_ann_saudi_new varchar2(50) := null;
1300 l_fm_annuities_saudi_ter varchar2(50) := null;
1301 l_fm_hazards_ter_all varchar2(50) := null;
1302 l_fm_haz_ann_saudi_ter varchar2(50) := null;
1303 l_fm_tot_curr_ann_saudi varchar2(50) := null;
1304 l_fm_tot_curr_haz_all varchar2(50) := null;
1305 l_fm_tot_curr_haz_ann_saudi varchar2(50) := null;
1306 l_fm_curr_annuities varchar2(50) := null;
1307 l_fm_curr_hazards varchar2(50) := null;
1308 l_fm_curr_haz_ann varchar2(50) := null;
1309 l_fm_arrears varchar2(50) := null;
1310 l_fm_penalty_charge varchar2(50) := null;
1311 l_fm_discount varchar2(50) := null;
1312 l_fm_total varchar2(50) := null;
1313 l_p_saudi_ann number := 0;
1314 l_p_saudi_ann_haz number := 0;
1315 l_p_haz number := 0;
1316 l_p_joiner_saudi_ann number := 0;
1317 l_p_joiner_saudi_ann_haz number := 0;
1318 l_p_joiner_haz number := 0;
1319 l_p_leaver_saudi_ann number := 0;
1320 l_p_leaver_saudi_ann_haz number := 0;
1321 l_p_leaver_haz number := 0;
1322 l_c_saudi_ann number := 0;
1323 l_c_saudi_ann_haz number := 0;
1324 l_c_haz number := 0;
1325 l_c_joiner_saudi_ann number := 0;
1326 l_c_joiner_saudi_ann_haz number := 0;
1327 l_c_joiner_haz number := 0;
1328 l_c_leaver_saudi_ann number := 0;
1329 l_c_leaver_saudi_ann_haz number := 0;
1330 l_c_leaver_haz number := 0;
1331 l_gosi_id number := null;
1332 l_employer_gosi_haz_id number := null;
1333 l_employee_gosi_ann_id number := null;
1334 l_p_joiner_nonsaudi_haz number := 0; /*Added for enhancement*/
1335 l_c_joiner_nonsaudi_haz number := 0; /*Added for enhancement*/
1336 l_p_leaver_nonsaudi_haz number := 0; /*Added for enhancement*/
1337 l_c_leaver_nonsaudi_haz number := 0; /*Added for enhancement*/
1338 l_p_nonsaudi_haz number := 0; /*Added for enhancement*/
1339 l_c_nonsaudi_haz number := 0; /*Added for enhancement*/
1340 l_hc_haz_nonsaudi_ter number := 0; /*Added for enhancement*/
1341 l_hazards_pct number := 0; /*Added for enhancement*/
1342 l_ee_annuities_pct number := 0; /*Added for enhancement*/
1343 l_er_annuities_pct number := 0; /*Added for enhancement*/
1344 l_gosi_ref_saudi_new number := 0; /*Added for enhancement*/
1345 l_gosi_ref_nonsaudi_new number := 0; /*Added for enhancement*/
1346 l_gosi_ref_saudi_ter number := 0; /*Added for enhancement*/
1347 l_gosi_ref_nonsaudi_ter number := 0; /*Added for enhancement*/
1348 l_sum_saudi_hazards_t number := 0; /*Copied for enhancement*/
1349 l_fm_gosi_ref_saudi_new varchar2(50) := null; /*Added for enhancement*/
1350 l_fm_gosi_ref_nonsaudi_new varchar2(50) := null; /*Added for enhancement*/
1351 l_fm_gosi_ref_saudi_ter varchar2(50) := null; /*Added for enhancement*/
1352 l_fm_gosi_ref_nonsaudi_ter varchar2(50) := null; /*Added for enhancement*/
1353 l_emp_annuity number := 0; /*Added for enhancement*/
1354 l_tot_l_emp_annuity number := 0; /*Added for enhancement*/
1355 l_tot_j_emp_annuity number := 0; /*Added for enhancement*/
1356 begin
1357 set_currency_mask(p_business_group_id);
1358 l_input_date := '01-'||p_effective_month||'-'||p_effective_year;
1359 l_effective_date := last_day(to_date(l_input_date,'DD-MM-YYYY'));
1360 /*Following 1 line changed for enhancement 5283457
1361 l_eff_term_date := to_date('28-'||to_char(l_effective_date,'MM-YYYY'),'DD-MM-YYYY');*/
1362 l_eff_term_date := l_effective_date;
1363 insert into fnd_sessions(session_id,effective_date) values(userenv('sessionid'),l_effective_date);
1364 -- To clear the PL/SQL Table values.
1365 vXMLTable.DELETE;
1366 vCtr := 1;
1367 hr_utility.set_location('Before hierarchy logic ',40);
1368 /*Commented the hierarchy part on 15-Jan-2004 */
1369 /*
1370 if p_org_structure_version_id is not null then
1371 if p_organisation_id is null then
1372 begin
1373 select distinct pose.organization_id_parent
1374 into l_parent_id
1375 from per_org_structure_elements pose
1376 where pose.org_structure_version_id = p_org_structure_version_id
1377 and pose.organization_id_parent not in (select pose1.organization_id_child
1378 from per_org_structure_elements pose1
1379 where pose1.org_structure_version_id = p_org_structure_version_id);
1380 exception
1381 when others then
1382 l_err := 1;
1383 end;
1384 end if;
1385 if l_err = 0 then
1386 open csr_org_hierarchy;
1387 loop
1388 fetch csr_org_hierarchy into rec_org_id;
1389 exit when csr_org_hierarchy%notfound;
1390 hr_sa_org_info.get_employer_name(rec_org_id.org,l_gre_name,p_business_group_id,p_org_structure_version_id);
1391 begin
1392 select organization_id
1393 into l_gre_id
1394 from hr_all_organization_units
1395 where name = l_gre_name
1396 and business_group_id = p_business_group_id;
1397 exception
1398 when others then
1399 l_err := 1;
1400 end;
1401 i := 0;
1402 IF t_legal_entity.count <> 0 then
1403 --WHILE t_legal_entity.last
1404 l_gre_present := 0;
1405 FOR i in t_legal_entity.first..t_legal_entity.last
1406 LOOP
1407 IF t_legal_entity(i).gre_id = l_gre_id THEN
1408 l_gre_present := 1;
1409 EXIT;
1410 END IF;
1411 --i := i + 1;
1412 END LOOP;
1413 IF l_gre_present = 0 THEN
1414 i := t_legal_entity.count;
1415 t_legal_entity(i + 1).gre_id := l_gre_id;
1416 t_legal_entity(i + 1).gre_name := l_gre_name;
1417 END IF;
1418 ELSE
1419 i := t_legal_entity.count;
1420 t_legal_entity(i + 1).gre_id := l_gre_id;
1421 t_legal_entity(i + 1).gre_name := l_gre_name;
1422 END IF;
1423 END LOOP;
1424 CLOSE csr_org_hierarchy;
1425 end if;
1426 end if;*/
1427 select add_months(l_effective_date,-1)
1428 into l_prev_mon_date
1429 from dual;
1430 /* Following one line changed for enhancement 5283457
1431 l_prev_term_date := to_date('28-'||to_char(l_prev_mon_date,'MM-YYYY'),'DD-MM-YYYY');*/
1432 l_prev_term_date := last_day(l_prev_mon_date);
1433 select add_months(l_prev_mon_date,-1)
1434 into l_prev2_mon_date
1435 from dual;
1436 /* Following one line changed for enhancement 5283457
1437 l_prev2_term_date := to_date('28-'||to_char(l_prev2_mon_date,'MM-YYYY'),'DD-MM-YYYY');*/
1438 l_prev2_term_date := last_day(l_prev2_mon_date);
1439 l_def_nationality_cd := UPPER(FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY')); --'AM'; -- UPPER(FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY'));
1440 BEGIN
1441 SELECT meaning INTO l_def_nationality
1442 FROM hr_lookups
1443 WHERE lookup_type = 'NATIONALITY'
1444 AND lookup_code = l_def_nationality_cd;
1445 EXCEPTION
1446 WHEN NO_DATA_FOUND THEN
1447 NULL;
1448 END;
1449 --l_def_nationality :='American';
1450 --Fetch defined_balance_id's
1451 select u.creator_id
1452 into l_ann_month_db
1453 from ff_user_entities u,
1454 ff_database_items d
1455 where d.user_name = 'GOSI_ANNUITIES_NAT_JOINER_LEAVER_TU_MONTH'
1456 and u.user_entity_id = d.user_entity_id
1457 and u.legislation_code = 'SA'
1458 and u.business_group_id is null
1459 and u.creator_type = 'B';
1460 select u.creator_id
1461 into l_haz_month_db
1462 from ff_user_entities u,
1463 ff_database_items d
1464 where d.user_name = 'GOSI_HAZARDS_NAT_JOINER_LEAVER_TU_MONTH'
1465 and u.user_entity_id = d.user_entity_id
1466 and u.legislation_code = 'SA'
1467 and u.business_group_id is null
1468 and u.creator_type = 'B';
1469 if p_org_structure_version_id is null then
1470 /*Commented the hierarchy part on 15-Jan-2004 */
1471 /*
1472 l_gre_id := null;
1473 l_gre_name := null;
1474 hr_sa_org_info.get_employer_name(p_organisation_id,l_gre_name,p_business_group_id);
1475 begin
1476 select organization_id
1477 into l_gre_id
1478 from hr_all_organization_units
1479 where name = l_gre_name
1480 and business_group_id = p_business_group_id;
1481 exception
1482 when others then
1483 l_err := 1;
1484 end;
1485 t_legal_entity(1).gre_id := l_gre_id;
1486 t_legal_entity(1).gre_name := l_gre_name;*/
1487 /*Commented the hierarchy part on 15-Jan-2004 */
1488 -- New code begins
1489 l_gre_id := p_organisation_id;
1490 begin
1491 select name
1492 into l_gre_name
1493 from hr_all_organization_units
1494 where organization_id= l_gre_id
1495 and business_group_id =p_business_group_id;
1496 exception
1497 when others then
1498 l_err := 1;
1499 end;
1500 t_legal_entity(1).gre_id := l_gre_id;
1501 t_legal_entity(1).gre_name := l_gre_name;
1502 -- New code ends
1503 end if;
1504 hr_utility.set_location('Calling Procedure to Populate Monthly Contribution Report ',50);
1505 FOR i in t_legal_entity.first..t_legal_entity.last
1506 LOOP
1507 l_tax_unit_id := t_legal_entity(i).gre_id;
1508 /*Get details for section A*/
1509 open csr_gosi_office_details;
1510 fetch csr_gosi_office_details into rec_gosi_office_details;
1511 l_employer_gosi_number := rec_gosi_office_details.org_information1;
1512 l_gosi_office_id := rec_gosi_office_details.org_information2;
1513 close csr_gosi_office_details;
1514 /*Fetch gosi_office name and gosi office code*/
1515 open csr_gosi_code;
1516 fetch csr_gosi_code into rec_gosi_code;
1517 l_gosi_office_code := rec_gosi_code.org_information1;
1518 close csr_gosi_code;
1519 begin
1520 select name
1521 into l_gosi_office
1522 from hr_all_organization_units
1523 where organization_id = l_gosi_office_id;
1524 exception
1525 when others then
1526 l_err := 1;
1527 end;
1528 --vXMLTable.DELETE;
1529 --vCtr := 1;
1530 vXMLTable(vCtr).TagName := 'G4-A-01-1';
1531 vXMLTable(vCtr).TagValue := l_gosi_office_code||' '||l_gosi_office;
1532 vctr := vctr + 1;
1533 /*vXMLTable(vCtr).TagName := 'G4-A-01-2-1';
1534 vXMLTable(vCtr).TagValue := SUBSTR(l_gosi_office_code,2,1);
1535 vctr := vctr + 1;
1536 vXMLTable(vCtr).TagName := 'G4-A-01-2-2';
1537 vXMLTable(vCtr).TagValue := SUBSTR(l_gosi_office_code,1,1);
1538 vctr := vctr + 1;*/
1539 vXMLTable(vCtr).TagName := 'G4-A-02';
1540 vXMLTable(vCtr).TagValue := p_effective_month;
1541 vctr := vctr + 1;
1542 vXMLTable(vCtr).TagName := 'G4-A-03';
1543 vXMLTable(vCtr).TagValue := p_effective_year;
1544 vctr := vctr + 1;
1545 vXMLTable(vCtr).TagName := 'G4-A-04';
1546 vXMLTable(vCtr).TagValue := t_legal_entity(i).gre_name;
1547 vctr := vctr + 1;
1548 vXMLTable(vCtr).TagName := 'G4-A-05-1';
1549 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_gosi_number,9,1);
1550 vctr := vctr + 1;
1551 vXMLTable(vCtr).TagName := 'G4-A-05-2';
1552 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_gosi_number,8,1);
1553 vctr := vctr + 1;
1554 vXMLTable(vCtr).TagName := 'G4-A-05-3';
1555 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_gosi_number,7,1);
1556 vctr := vctr + 1;
1557 vXMLTable(vCtr).TagName := 'G4-A-05-4';
1558 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_gosi_number,6,1);
1559 vctr := vctr + 1;
1560 vXMLTable(vCtr).TagName := 'G4-A-05-5';
1561 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_gosi_number,5,1);
1562 vctr := vctr + 1;
1563 vXMLTable(vCtr).TagName := 'G4-A-05-6';
1564 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_gosi_number,4,1);
1565 vctr := vctr + 1;
1566 vXMLTable(vCtr).TagName := 'G4-A-05-7';
1567 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_gosi_number,3,1);
1568 vctr := vctr + 1;
1569 vXMLTable(vCtr).TagName := 'G4-A-05-8';
1570 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_gosi_number,2,1);
1571 vctr := vctr + 1;
1572 vXMLTable(vCtr).TagName := 'G4-A-05-9';
1573 vXMLTable(vCtr).TagValue := SUBSTR(l_employer_gosi_number,1,1);
1574 vctr := vctr + 1;
1575 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
1576 pay_balance_pkg.set_context('DATE_EARNED',FND_DATE.DATE_TO_CANONICAL(l_prev_mon_date));
1577 /***To fetch for pprevious months, using existing emp + new + terminated after 28th *****/
1578 pay_balance_pkg.set_context('SOURCE_NUMBER',1);
1579 pay_balance_pkg.set_context('SOURCE_TEXT','N');
1580 pay_balance_pkg.set_context('SOURCE_TEXT2','N');
1581 l_prev_ann_saudi := pay_balance_pkg.get_value(l_ann_month_db,null);
1582 l_prev_haz_saudi := pay_balance_pkg.get_value(l_haz_month_db,null);
1583 l_prev_haz_ann_saudi := l_prev_ann_saudi + l_prev_haz_saudi;
1584 pay_balance_pkg.set_context('SOURCE_NUMBER',2);
1585 pay_balance_pkg.set_context('SOURCE_TEXT','N');
1586 pay_balance_pkg.set_context('SOURCE_TEXT2','N');
1587 l_prev_haz := pay_balance_pkg.get_value(l_haz_month_db,null);
1588 l_prev_haz_all := l_prev_haz_saudi + l_prev_haz;
1589 pay_balance_pkg.set_context('SOURCE_NUMBER',1);
1590 pay_balance_pkg.set_context('SOURCE_TEXT','Y');
1591 pay_balance_pkg.set_context('SOURCE_TEXT2','N');
1592 l_prev_ann_saudi_new := pay_balance_pkg.get_value(l_ann_month_db,null);
1593 l_prev_haz_saudi_new := pay_balance_pkg.get_value(l_haz_month_db,null);
1594 l_prev_haz_ann_saudi_new := l_prev_ann_saudi_new + l_prev_haz_saudi_new;
1595 pay_balance_pkg.set_context('SOURCE_NUMBER',2);
1596 pay_balance_pkg.set_context('SOURCE_TEXT','Y');
1597 pay_balance_pkg.set_context('SOURCE_TEXT2','N');
1598 l_prev_haz_new := pay_balance_pkg.get_value(l_haz_month_db,null);
1599 l_prev_haz_new_all := l_prev_haz_saudi_new + l_prev_haz_new;
1600 pay_balance_pkg.set_context('SOURCE_NUMBER',1);
1601 pay_balance_pkg.set_context('SOURCE_TEXT','N');
1602 pay_balance_pkg.set_context('SOURCE_TEXT2','Y');
1603 l_prev_ann_saudi_ter := pay_balance_pkg.get_value(l_ann_month_db,null);
1604 l_prev_haz_saudi_ter := pay_balance_pkg.get_value(l_haz_month_db,null);
1605 l_prev_haz_ann_saudi_ter := l_prev_ann_saudi_ter + l_prev_haz_saudi_ter;
1606 pay_balance_pkg.set_context('SOURCE_NUMBER',2);
1607 pay_balance_pkg.set_context('SOURCE_TEXT','N');
1608 pay_balance_pkg.set_context('SOURCE_TEXT2','Y');
1609 l_prev_haz_ter := pay_balance_pkg.get_value(l_haz_month_db,null);
1610 l_prev_haz_ter_all := l_prev_haz_saudi_ter + l_prev_haz_ter;
1611 /*Following code for joiner-leaver (after 28th) in same month*/
1612 pay_balance_pkg.set_context('SOURCE_NUMBER',1);
1613 pay_balance_pkg.set_context('SOURCE_TEXT','Y');
1614 pay_balance_pkg.set_context('SOURCE_TEXT2','Y');
1615 l_prev_ann_saudi_new_ter := pay_balance_pkg.get_value(l_ann_month_db,null);
1616 l_prev_haz_saudi_new_ter := pay_balance_pkg.get_value(l_haz_month_db,null);
1617 l_prev_haz_ann_saudi_new_ter := l_prev_ann_saudi_new_ter + l_prev_haz_saudi_new_ter;
1618 pay_balance_pkg.set_context('SOURCE_NUMBER',2);
1619 pay_balance_pkg.set_context('SOURCE_TEXT','Y');
1620 pay_balance_pkg.set_context('SOURCE_TEXT2','Y');
1621 l_prev_haz_new_ter := pay_balance_pkg.get_value(l_haz_month_db,null);
1622 l_prev_haz_new_ter_all := l_prev_haz_saudi_new_ter + l_prev_haz_new_ter;
1623 l_tot_prev_ann_saudi := l_prev_ann_saudi + l_prev_ann_saudi_new + l_prev_ann_saudi_ter + l_prev_ann_saudi_new_ter;
1624 l_tot_prev_haz_all := l_prev_haz_all + l_prev_haz_new_all + l_prev_haz_ter_all + l_prev_haz_new_ter_all;
1625 l_tot_prev_haz_ann_saudi := l_prev_haz_ann_saudi + l_prev_haz_ann_saudi_new + l_prev_haz_ann_saudi_ter + l_prev_haz_ann_saudi_new_ter;
1626 /*******************************************************/
1627 pay_balance_pkg.set_context('DATE_EARNED',FND_DATE.DATE_TO_CANONICAL(l_effective_date));
1628 pay_balance_pkg.set_context('SOURCE_NUMBER',1);
1629 pay_balance_pkg.set_context('SOURCE_TEXT','N');
1630 pay_balance_pkg.set_context('SOURCE_TEXT2','N');
1631 l_curr_ann_saudi := pay_balance_pkg.get_value(l_ann_month_db,null);
1632 l_curr_haz_saudi := pay_balance_pkg.get_value(l_haz_month_db,null);
1633 l_curr_haz_ann_saudi := l_curr_ann_saudi + l_curr_haz_saudi;
1634 pay_balance_pkg.set_context('SOURCE_NUMBER',2);
1635 pay_balance_pkg.set_context('SOURCE_TEXT','N');
1636 pay_balance_pkg.set_context('SOURCE_TEXT2','N');
1637 l_curr_haz := pay_balance_pkg.get_value(l_haz_month_db,null);
1638 l_curr_haz_all := l_curr_haz_saudi + l_curr_haz;
1639 pay_balance_pkg.set_context('SOURCE_NUMBER',1);
1640 pay_balance_pkg.set_context('SOURCE_TEXT','Y');
1641 pay_balance_pkg.set_context('SOURCE_TEXT2','N');
1642 l_curr_ann_saudi_new := pay_balance_pkg.get_value(l_ann_month_db,null);
1643 l_curr_haz_saudi_new := pay_balance_pkg.get_value(l_haz_month_db,null);
1644 l_curr_haz_ann_saudi_new := l_curr_ann_saudi_new + l_curr_haz_saudi_new;
1645 pay_balance_pkg.set_context('SOURCE_NUMBER',2);
1646 pay_balance_pkg.set_context('SOURCE_TEXT','Y');
1647 pay_balance_pkg.set_context('SOURCE_TEXT2','N');
1648 l_curr_haz_new := pay_balance_pkg.get_value(l_haz_month_db,null);
1649 l_curr_haz_new_all := l_curr_haz_saudi_new + l_curr_haz_new;
1650 pay_balance_pkg.set_context('SOURCE_NUMBER',1);
1651 pay_balance_pkg.set_context('SOURCE_TEXT','N');
1652 pay_balance_pkg.set_context('SOURCE_TEXT2','Y');
1653 l_curr_ann_saudi_ter := pay_balance_pkg.get_value(l_ann_month_db,null);
1654 l_curr_haz_saudi_ter := pay_balance_pkg.get_value(l_haz_month_db,null);
1655 l_curr_haz_ann_saudi_ter := l_curr_ann_saudi_ter + l_curr_haz_saudi_ter;
1656 pay_balance_pkg.set_context('SOURCE_NUMBER',2);
1657 pay_balance_pkg.set_context('SOURCE_TEXT','N');
1658 pay_balance_pkg.set_context('SOURCE_TEXT2','Y');
1659 l_curr_haz_ter := pay_balance_pkg.get_value(l_haz_month_db,null);
1660 l_curr_haz_ter_all := l_curr_haz_saudi_ter + l_curr_haz_ter;
1661 pay_balance_pkg.set_context('SOURCE_NUMBER',1);
1662 pay_balance_pkg.set_context('SOURCE_TEXT','Y');
1663 pay_balance_pkg.set_context('SOURCE_TEXT2','Y');
1664 l_curr_ann_saudi_new_ter := pay_balance_pkg.get_value(l_ann_month_db,null);
1665 l_curr_haz_saudi_new_ter := pay_balance_pkg.get_value(l_haz_month_db,null);
1666 l_curr_haz_ann_saudi_new_ter := l_curr_ann_saudi_new_ter + l_curr_haz_saudi_new_ter;
1667 pay_balance_pkg.set_context('SOURCE_NUMBER',2);
1668 pay_balance_pkg.set_context('SOURCE_TEXT','Y');
1669 pay_balance_pkg.set_context('SOURCE_TEXT2','Y');
1670 l_curr_haz_new_ter := pay_balance_pkg.get_value(l_haz_month_db,null);
1671 l_curr_haz_new_ter_all := l_curr_haz_saudi_new_ter + l_curr_haz_new_ter;
1672 l_tot_curr_ann_saudi := l_curr_ann_saudi + l_curr_ann_saudi_new + l_curr_ann_saudi_ter + l_curr_ann_saudi_new_ter;
1673 l_tot_curr_haz_all := l_curr_haz_all + l_curr_haz_new_all + l_curr_haz_ter_all + l_curr_haz_new_ter_all;
1674 l_tot_curr_haz_ann_saudi := l_curr_haz_ann_saudi + l_curr_haz_ann_saudi_new + l_curr_haz_ann_saudi_ter + l_curr_haz_ann_saudi_new_ter;
1675 /********************************************************/
1676 --New Joiners
1677 --for Saudi Annuities
1678 l_annuities_saudi_new := l_curr_ann_saudi_new + l_curr_ann_saudi_new_ter;
1679 --for all hazards
1680 l_hazards_new_all := l_curr_haz_new_all + l_curr_haz_new_ter_all;
1681 --for saudi annuities and hazards
1682 l_haz_ann_saudi_new := l_annuities_saudi_new + l_curr_haz_saudi_new + l_curr_haz_saudi_new_ter;
1683 /*Computation of leavers data */
1684 /******** Computation done after calculating leavers headcout
1685 l_annuities_saudi_ter := (l_tot_prev_ann_saudi) + (l_annuities_saudi_new) - (l_tot_curr_ann_saudi);
1686 l_hazards_ter_all := (l_tot_prev_haz_all) + (l_hazards_new_all) - (l_tot_curr_haz_all);
1687 l_haz_ann_saudi_ter := (l_tot_prev_haz_ann_saudi) + (l_haz_ann_saudi_new) - (l_tot_curr_haz_ann_saudi);********/
1688 /*For Section C*/
1689 l_curr_annuities := l_tot_curr_ann_saudi;
1690 l_curr_hazards := l_tot_curr_haz_all;
1691 l_curr_haz_ann := l_curr_annuities + l_curr_hazards;
1692 /****Fetch headcounts****/
1693 /*Fetch element_type_id for elements GOSI, Employee GOSI Annuities, Employee GOSI Hazards*/
1694 SELECT element_type_id
1695 INTO l_gosi_id
1696 FROM pay_element_types_f
1697 WHERE element_name = 'GOSI'
1698 AND legislation_code = 'SA'
1699 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
1700 SELECT element_type_id
1701 INTO l_employer_gosi_haz_id
1702 FROM pay_element_types_f
1703 WHERE element_name = 'Employer GOSI Hazards'
1704 AND legislation_code = 'SA'
1705 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
1706 SELECT element_type_id
1707 INTO l_employee_gosi_ann_id
1708 FROM pay_element_types_f
1709 WHERE element_name = 'Employee GOSI Annuities'
1710 AND legislation_code = 'SA'
1711 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
1712 /*Query for fetching headcount modified after the enhacement*/
1713 /*Head counts for previous month*/
1714 SELECT SUM(DECODE(ac1.context_value
1715 ,'1' ,DECODE(rr3.run_result_id
1716 ,NULL, 0
1717 ,1)
1718 ,0)) SAUDI_ANNUITIES
1719 ,SUM(DECODE(ac1.context_value
1720 ,'1', DECODE(rr3.run_result_id
1721 ,NULL, DECODE(rr2.run_result_id
1722 ,NULL, 0
1723 ,1)
1724 ,1)
1725 ,0)) SAUDI_ANNUITIES_HAZARDS
1726 ,SUM(DECODE(rr2.run_result_id
1727 ,NULL, 0
1728 ,1)) HAZARDS
1729 ,SUM(DECODE(ac2.context_value
1730 ,'Y', DECODE(ac1.context_value
1731 ,'1' ,DECODE(rr3.run_result_id
1732 ,NULL, 0
1733 ,1)
1734 ,0)
1735 ,0)) JOINER_SAUDI_ANNUITIES
1736 ,SUM(DECODE(ac2.context_value
1737 ,'Y', DECODE(ac1.context_value
1738 ,'1', DECODE(rr3.run_result_id
1739 ,NULL, DECODE(rr2.run_result_id
1740 ,NULL, 0
1741 ,1)
1742 ,1)
1743 ,0)
1744 ,0)) JOINER_SAUDI_ANNUITIES_HAZARDS
1745 ,SUM(DECODE(ac2.context_value
1746 ,'Y', DECODE(ac1.context_value
1747 ,'2' ,DECODE(rr2.run_result_id
1748 ,NULL, 0
1749 ,1)
1750 ,0)
1751 ,0)) JOINER_NONSAUDI_HAZARDS
1752 ,SUM(DECODE(ac3.context_value
1753 ,'Y', DECODE(ac1.context_value
1754 ,'2' ,DECODE(rr2.run_result_id
1755 ,NULL, 0
1756 ,1)
1757 ,0)
1758 ,0)) LEAVER_NONSAUDI_HAZARDS
1759 ,SUM(DECODE(ac1.context_value
1760 ,'2' ,DECODE(rr2.run_result_id
1761 ,NULL, 0
1762 ,1)
1763 ,0)) NONSAUDI_HAZARDS
1764 ,SUM(DECODE(ac2.context_value
1765 ,'Y', DECODE(rr2.run_result_id
1766 ,NULL, 0
1767 ,1)
1768 ,0)) JOINER_HAZARDS
1769 ,SUM(DECODE(ac3.context_value
1770 ,'Y', DECODE(ac1.context_value
1771 ,'1' ,DECODE(rr3.run_result_id
1772 ,NULL, 0
1773 ,1)
1774 ,0)
1775 ,0)) LEAVER_SAUDI_ANNUITIES
1776 ,SUM(DECODE(ac3.context_value
1777 ,'Y', DECODE(ac1.context_value
1778 ,'1', DECODE(rr3.run_result_id
1779 ,NULL, DECODE(rr2.run_result_id
1780 ,NULL, 0
1781 ,1)
1782 ,1)
1783 ,0)
1784 ,0)) LEAVER_SAUDI_ANNUITIES_HAZARDS
1785 ,SUM(DECODE(ac3.context_value
1786 ,'Y', DECODE(rr2.run_result_id
1787 ,NULL, 0
1788 ,1)
1789 ,0)) LEAVER_HAZARDS
1790 INTO l_p_saudi_ann
1791 ,l_p_saudi_ann_haz
1792 ,l_p_haz
1793 ,l_p_joiner_saudi_ann
1794 ,l_p_joiner_saudi_ann_haz
1795 ,l_p_joiner_nonsaudi_haz /*Added for enhancement*/
1796 ,l_p_leaver_nonsaudi_haz /*Added for enhancement*/
1797 ,l_p_nonsaudi_haz /*Added for enhancement*/
1798 ,l_p_joiner_haz
1799 ,l_p_leaver_saudi_ann
1800 ,l_p_leaver_saudi_ann_haz
1801 ,l_p_leaver_haz
1802 FROM pay_assignment_actions paa
1803 ,pay_action_contexts ac1
1804 ,ff_contexts ct1
1805 ,pay_action_contexts ac2
1806 ,ff_contexts ct2
1807 ,pay_action_contexts ac3
1808 ,ff_contexts ct3
1809 ,pay_payroll_actions ppa
1810 ,pay_run_results rr1
1811 ,pay_run_results rr2
1812 ,pay_run_results rr3
1813 WHERE ppa.business_group_id = p_business_group_id
1814 AND ppa.action_type IN ('R','Q')
1815 AND ppa.action_status = 'C'
1816 AND ppa.date_earned BETWEEN TRUNC(l_prev_mon_date,'MM')
1817 AND l_prev_mon_date
1818 AND paa.payroll_action_id = ppa.payroll_action_id
1819 AND paa.tax_unit_id = l_tax_unit_id -- Employer
1820 AND ct1.context_name = 'SOURCE_NUMBER'
1821 AND ac1.context_id = ct1.context_id
1822 AND ac1.assignment_action_id = paa.assignment_action_id
1823 AND ct2.context_name = 'SOURCE_TEXT'
1824 AND ac2.context_id = ct2.context_id
1825 AND ac2.assignment_action_id = paa.assignment_action_id
1826 AND ct3.context_name = 'SOURCE_TEXT2'
1827 AND ac3.context_id = ct3.context_id
1828 AND ac3.assignment_action_id = paa.assignment_action_id
1829 AND rr1.assignment_action_id = paa.assignment_action_id
1830 AND rr1.element_type_id = l_gosi_id
1831 AND rr2.assignment_action_id (+) = rr1.assignment_action_id
1832 AND rr2.source_id (+) = rr1.element_entry_id
1833 AND rr2.source_type (+) = 'I'
1834 AND rr2.element_type_id (+) = l_employer_gosi_haz_id
1835 AND rr3.assignment_action_id (+) = rr1.assignment_action_id
1836 AND rr3.source_id (+) = rr1.element_entry_id
1837 AND rr3.source_type (+) = 'I'
1838 AND rr3.element_type_id (+) = l_employee_gosi_ann_id;
1839 /*Head counts for current month*/
1840 SELECT SUM(DECODE(ac1.context_value
1841 ,'1' ,DECODE(rr3.run_result_id
1842 ,NULL, 0
1843 ,1)
1844 ,0)) SAUDI_ANNUITIES
1845 ,SUM(DECODE(ac1.context_value
1846 ,'1', DECODE(rr3.run_result_id
1847 ,NULL, DECODE(rr2.run_result_id
1848 ,NULL, 0
1849 ,1)
1850 ,1)
1851 ,0)) SAUDI_ANNUITIES_HAZARDS
1852 ,SUM(DECODE(rr2.run_result_id
1853 ,NULL, 0
1854 ,1)) HAZARDS
1855 ,SUM(DECODE(ac2.context_value
1856 ,'Y', DECODE(ac1.context_value
1857 ,'1' ,DECODE(rr3.run_result_id
1858 ,NULL, 0
1859 ,1)
1860 ,0)
1861 ,0)) JOINER_SAUDI_ANNUITIES
1862 ,SUM(DECODE(ac2.context_value
1863 ,'Y', DECODE(ac1.context_value
1864 ,'1', DECODE(rr3.run_result_id
1865 ,NULL, DECODE(rr2.run_result_id
1866 ,NULL, 0
1867 ,1)
1868 ,1)
1869 ,0)
1870 ,0)) JOINER_SAUDI_ANNUITIES_HAZARDS
1871 ,SUM(DECODE(ac2.context_value
1872 ,'Y', DECODE(ac1.context_value
1873 ,'2' ,DECODE(rr2.run_result_id
1874 ,NULL, 0
1875 ,1)
1876 ,0)
1877 ,0)) JOINER_NONSAUDI_HAZARDS
1878 ,SUM(DECODE(ac3.context_value
1879 ,'Y', DECODE(ac1.context_value
1880 ,'2' ,DECODE(rr2.run_result_id
1881 ,NULL, 0
1882 ,1)
1883 ,0)
1884 ,0)) LEAVER_NONSAUDI_HAZARDS
1885 ,SUM(DECODE(ac1.context_value
1886 ,'2' ,DECODE(rr2.run_result_id
1887 ,NULL, 0
1888 ,1)
1889 ,0)) NONSAUDI_HAZARDS
1890 ,SUM(DECODE(ac2.context_value
1891 ,'Y', DECODE(rr2.run_result_id
1892 ,NULL, 0
1893 ,1)
1894 ,0)) JOINER_HAZARDS
1895 ,SUM(DECODE(ac3.context_value
1896 ,'Y', DECODE(ac1.context_value
1897 ,'1' ,DECODE(rr3.run_result_id
1898 ,NULL, 0
1899 ,1)
1900 ,0)
1901 ,0)) LEAVER_SAUDI_ANNUITIES
1902 ,SUM(DECODE(ac3.context_value
1903 ,'Y', DECODE(ac1.context_value
1904 ,'1', DECODE(rr3.run_result_id
1905 ,NULL, DECODE(rr2.run_result_id
1906 ,NULL, 0
1907 ,1)
1908 ,1)
1909 ,0)
1910 ,0)) LEAVER_SAUDI_ANNUITIES_HAZARDS
1911 ,SUM(DECODE(ac3.context_value
1912 ,'Y', DECODE(rr2.run_result_id
1913 ,NULL, 0
1914 ,1)
1915 ,0)) LEAVER_HAZARDS
1916 INTO l_c_saudi_ann
1917 ,l_c_saudi_ann_haz
1918 ,l_c_haz
1919 ,l_c_joiner_saudi_ann
1920 ,l_c_joiner_saudi_ann_haz
1921 ,l_c_joiner_nonsaudi_haz /*Added for enhancement*/
1922 ,l_c_leaver_nonsaudi_haz /*Added for enhancement*/
1923 ,l_c_nonsaudi_haz /*Added for enhancement*/
1924 ,l_c_joiner_haz
1925 ,l_c_leaver_saudi_ann
1926 ,l_c_leaver_saudi_ann_haz
1927 ,l_c_leaver_haz
1928 FROM pay_assignment_actions paa
1929 ,pay_action_contexts ac1
1930 ,ff_contexts ct1
1931 ,pay_action_contexts ac2
1932 ,ff_contexts ct2
1933 ,pay_action_contexts ac3
1934 ,ff_contexts ct3
1935 ,pay_payroll_actions ppa
1936 ,pay_run_results rr1
1937 ,pay_run_results rr2
1938 ,pay_run_results rr3
1939 WHERE ppa.business_group_id = p_business_group_id
1940 AND ppa.action_type IN ('R','Q')
1941 AND ppa.action_status = 'C'
1942 AND ppa.date_earned BETWEEN TRUNC(l_effective_date,'MM')
1943 AND l_effective_date
1944 AND paa.payroll_action_id = ppa.payroll_action_id
1945 AND paa.tax_unit_id = l_tax_unit_id -- Employer
1946 AND ct1.context_name = 'SOURCE_NUMBER'
1947 AND ac1.context_id = ct1.context_id
1948 AND ac1.assignment_action_id = paa.assignment_action_id
1949 AND ct2.context_name = 'SOURCE_TEXT'
1950 AND ac2.context_id = ct2.context_id
1951 AND ac2.assignment_action_id = paa.assignment_action_id
1952 AND ct3.context_name = 'SOURCE_TEXT2'
1953 AND ac3.context_id = ct3.context_id
1954 AND ac3.assignment_action_id = paa.assignment_action_id
1955 AND rr1.assignment_action_id = paa.assignment_action_id
1956 AND rr1.element_type_id = l_gosi_id
1957 AND rr2.assignment_action_id (+) = rr1.assignment_action_id
1958 AND rr2.source_id (+) = rr1.element_entry_id
1959 AND rr2.source_type (+) = 'I'
1960 AND rr2.element_type_id (+) = l_employer_gosi_haz_id
1961 AND rr3.assignment_action_id (+) = rr1.assignment_action_id
1962 AND rr3.source_id (+) = rr1.element_entry_id
1963 AND rr3.source_type (+) = 'I'
1964 AND rr3.element_type_id (+) = l_employee_gosi_ann_id;
1965 l_hc_ann_saudi_ter := l_p_leaver_saudi_ann + (l_p_saudi_ann + l_c_joiner_saudi_ann - l_p_leaver_saudi_ann - l_c_saudi_ann);
1966 l_hc_haz_ter_all := l_p_leaver_haz + (l_p_haz + l_c_joiner_haz - l_p_leaver_haz - l_c_haz);
1967 l_hc_haz_ann_saudi_ter := l_p_leaver_saudi_ann_haz + (l_p_saudi_ann_haz + l_c_joiner_saudi_ann_haz
1968 - l_p_leaver_saudi_ann_haz - l_c_saudi_ann_haz);
1969 l_hc_haz_nonsaudi_ter := l_p_leaver_nonsaudi_haz + (l_p_nonsaudi_haz + l_c_joiner_nonsaudi_haz
1970 - l_p_leaver_nonsaudi_haz - l_c_nonsaudi_haz);
1971 l_sum_saudi_hazards_t := 0;
1972 declare
1973 /******Code for fetching contribution of leavers******/
1974 cursor csr_get_leav_assact is
1975 select distinct paa.assignment_action_id, paf.person_id
1976 from pay_assignment_actions paa
1977 ,pay_payroll_actions ppa
1978 ,per_all_assignments_f paf
1979 ,per_periods_of_service pps
1980 ,hr_soft_coding_keyflex hscl
1981 ,per_time_periods ptp
1982 ,pay_run_results prr
1983 where paf.period_of_service_id = pps.period_of_service_id
1984 and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'))
1985 between trunc(l_effective_date,'MM') and trunc(l_eff_term_date-1)
1986 /*****between trunc(l_effective_date,'MM') and trunc(l_eff_term_date-1)********* CHECK THIS*/
1987 --between l_prev_term_date and trunc(l_eff_term_date-1)
1988 and paf.assignment_id = paa.assignment_id
1989 and paa.payroll_action_id = ppa.payroll_action_id
1990 and ppa.action_type in ('R','Q')
1991 and ppa.action_status = 'C'
1992 and paa.action_status = 'C'
1993 and prr.assignment_action_id = paa.assignment_action_id
1994 and prr.element_type_id = l_gosi_id
1995 and paf.soft_coding_keyflex_id = hscl.soft_coding_keyflex_id
1996 and ppa.time_period_id = ptp.time_period_id
1997 and ptp.end_date = l_prev_mon_date
1998 and trunc(l_prev_mon_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date
1999 and hscl.segment1 = to_char(l_tax_unit_id);
2000 rec_leav_assact csr_get_leav_assact%ROWTYPE;
2001 /******Code for fetching contribution of saudi leaver employees paying only annuities*****/
2002 cursor csr_saudi_l_ann_assact is
2003 select distinct paa.assignment_action_id, paf.person_id
2004 from pay_assignment_actions paa
2005 ,pay_payroll_actions ppa
2006 ,per_all_assignments_f paf
2007 ,per_periods_of_service pps
2008 ,hr_soft_coding_keyflex hscl
2009 ,per_time_periods ptp
2010 ,pay_run_results prr
2011 where paf.period_of_service_id = pps.period_of_service_id
2012 and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'))
2013 between l_prev_term_date and trunc(l_eff_term_date-1)
2014 and paf.assignment_id = paa.assignment_id
2015 and paa.payroll_action_id = ppa.payroll_action_id
2016 and ppa.action_type in ('R','Q')
2017 and ppa.action_status = 'C'
2018 and paa.action_status = 'C'
2019 and prr.assignment_action_id = paa.assignment_action_id
2020 and prr.element_type_id = l_gosi_id
2021 and paf.soft_coding_keyflex_id = hscl.soft_coding_keyflex_id
2022 and ppa.time_period_id = ptp.time_period_id
2023 and ptp.end_date = l_prev_mon_date
2024 and trunc(l_prev_mon_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date
2025 and hscl.segment1 = to_char(l_tax_unit_id)
2026 and hscl.segment3 = 'Y'
2027 and hscl.segment5 = 'N';
2028 rec_l_ann_assact csr_saudi_l_ann_assact%ROWTYPE;
2029 /******Code for fetching contribution of saudi joiner employees paying only annuities*****/
2030 cursor csr_saudi_j_ann_assact is
2031 select distinct paa.assignment_action_id, paf.person_id
2032 from pay_assignment_actions paa
2033 ,pay_payroll_actions ppa
2034 ,per_all_assignments_f paf
2035 ,per_periods_of_service pps
2036 ,hr_soft_coding_keyflex hscl
2037 ,per_time_periods ptp
2038 ,pay_run_results prr
2039 where paf.period_of_service_id = pps.period_of_service_id
2040 and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'))
2041 not between trunc(l_effective_date,'MM') and trunc(l_eff_term_date-1)
2042 and trunc(pps.date_start, 'MM') = trunc(l_effective_date,'MM')
2043 and paf.assignment_id = paa.assignment_id
2044 and paa.payroll_action_id = ppa.payroll_action_id
2045 and ppa.action_type in ('R','Q')
2046 and ppa.action_status = 'C'
2047 and paa.action_status = 'C'
2048 and prr.assignment_action_id = paa.assignment_action_id
2049 and prr.element_type_id = l_gosi_id
2050 and paf.soft_coding_keyflex_id = hscl.soft_coding_keyflex_id
2051 and ppa.time_period_id = ptp.time_period_id
2052 and ptp.end_date = l_effective_date
2053 and trunc(l_effective_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date
2054 and hscl.segment1 = to_char(l_tax_unit_id)
2055 and hscl.segment3 = 'Y'
2056 and hscl.segment5 = 'N';
2057 rec_j_ann_assact csr_saudi_j_ann_assact%ROWTYPE;
2058 l_assact_haz number := 0;
2059 l_sum_hazards_t number := 0;
2060 l_sum_saudi_annuities_t number := 0;
2061 ----l_sum_saudi_hazards_t number := 0;*** THIS VARIABLE MOVED OUTSIDE THE BLOCK*********
2062 l_loc_nat number := 0;
2063 l_gosi_haz_asg_tu_mth_db number := 0;
2064 l_emp_gosi_ann_asg_ptd_db number := 0;
2065 begin
2066 select u.creator_id
2067 into l_gosi_haz_asg_tu_mth_db
2068 from ff_user_entities u,
2069 ff_database_items d
2070 where d.user_name = 'GOSI_HAZARDS_ASG_TU_MONTH'
2071 and u.user_entity_id = d.user_entity_id
2072 and u.legislation_code = 'SA'
2073 and u.business_group_id is null
2074 and u.creator_type = 'B';
2075 select u.creator_id
2076 into l_emp_gosi_ann_asg_ptd_db
2077 from ff_user_entities u,
2078 ff_database_items d
2079 where d.user_name = 'GOSI_ANNUITIES_ASG_TU_MONTH'
2080 and u.user_entity_id = d.user_entity_id
2081 and u.legislation_code = 'SA'
2082 and u.business_group_id is null
2083 and u.creator_type = 'B';
2084 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2085 pay_balance_pkg.set_context('DATE_EARNED',FND_DATE.DATE_TO_CANONICAL(l_prev_mon_date));
2086 /*Fetch contribution of employees who got terminated after 28th of previous month*/
2087 open csr_get_leav_assact;
2088 loop
2089 fetch csr_get_leav_assact into rec_leav_assact;
2090 exit when csr_get_leav_assact%notfound;
2091 l_assact_haz := 0;
2092 if nvl(l_hc_haz_ter_all,0) > 0 then
2093 l_assact_haz := pay_balance_pkg.get_value(l_gosi_haz_asg_tu_mth_db,rec_leav_assact.assignment_action_id);
2094 end if;
2095 l_sum_hazards_t := l_sum_hazards_t + l_assact_haz;
2096 if nvl(l_hc_ann_saudi_ter,0) > 0 then
2097 l_sum_saudi_annuities_t := l_sum_saudi_annuities_t +
2098 (pay_balance_pkg.get_value(l_emp_gosi_ann_asg_ptd_db,rec_leav_assact.assignment_action_id));
2099 end if;
2100 select count(*)
2101 into l_loc_nat
2102 from per_all_people_f
2103 where person_id = rec_leav_assact.person_id
2104 and upper(nationality) = FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY')
2105 and trunc(l_prev_mon_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
2106 if l_loc_nat > 0 then
2107 l_sum_saudi_hazards_t := l_sum_saudi_hazards_t + l_assact_haz;
2108 end if;
2109 end loop;
2110 close csr_get_leav_assact;
2111 if nvl(l_hc_ann_saudi_ter,0) = 0 then
2112 l_annuities_saudi_ter := 0;
2113 else
2114 l_annuities_saudi_ter := l_prev_ann_saudi_ter + l_prev_ann_saudi_new_ter + l_sum_saudi_annuities_t;
2115 end if;
2116 if nvl(l_hc_haz_ter_all,0) = 0 then
2117 l_hazards_ter_all := 0;
2118 else
2119 l_hazards_ter_all := l_prev_haz_ter_all + l_prev_haz_new_ter_all + l_sum_hazards_t;
2120 end if;
2121 if nvl(l_hc_haz_ann_saudi_ter,0) = 0 then
2122 l_haz_ann_saudi_ter := 0;
2123 else
2124 l_haz_ann_saudi_ter := l_prev_haz_ann_saudi_ter + l_prev_haz_ann_saudi_new_ter +
2125 l_sum_saudi_annuities_t + l_sum_saudi_hazards_t;
2126 end if;
2127 --Fetch the total amount paid by employees who pay only annuities
2128 l_emp_annuity := 0;
2129 l_tot_l_emp_annuity := 0;
2130 open csr_saudi_l_ann_assact;
2131 loop
2132 fetch csr_saudi_l_ann_assact into rec_l_ann_assact;
2133 exit when csr_saudi_l_ann_assact%notfound;
2134 l_emp_annuity := pay_balance_pkg.get_value(l_emp_gosi_ann_asg_ptd_db,rec_l_ann_assact.assignment_action_id);
2135 l_tot_l_emp_annuity := l_tot_l_emp_annuity + l_emp_annuity;
2136 end loop;
2137 close csr_saudi_l_ann_assact;
2138 l_emp_annuity := 0;
2139 l_tot_j_emp_annuity := 0;
2140 open csr_saudi_j_ann_assact;
2141 loop
2142 fetch csr_saudi_j_ann_assact into rec_j_ann_assact;
2143 exit when csr_saudi_j_ann_assact%notfound;
2144 l_emp_annuity := pay_balance_pkg.get_value(l_emp_gosi_ann_asg_ptd_db,rec_j_ann_assact.assignment_action_id);
2145 l_tot_j_emp_annuity := l_tot_j_emp_annuity + l_emp_annuity;
2146 end loop;
2147 close csr_saudi_j_ann_assact;
2148 end;
2149 /***************************************************Removed as part of enhancement
2150 vXMLTable(vCtr).TagName := 'G4-B-01-1';
2151 --vXMLTable(vCtr).TagValue := l_hc_ann_saudi;
2152 vXMLTable(vCtr).TagValue := nvl(l_p_saudi_ann,0);
2153 vctr := vctr + 1;
2154 vXMLTable(vCtr).TagName := 'G4-B-01-2';
2155 --vXMLTable(vCtr).TagValue := l_hc_haz;
2156 vXMLTable(vCtr).TagValue := nvl(l_p_haz,0);
2157 vctr := vctr + 1;
2158 vXMLTable(vCtr).TagName := 'G4-B-01-3';
2159 --vXMLTable(vCtr).TagValue := l_hc_haz_ann_saudi;
2160 vXMLTable(vCtr).TagValue := nvl(l_p_saudi_ann_haz,0);
2161 vctr := vctr + 1;
2162 l_fm_tot_prev_ann_saudi := null;
2163 l_fm_tot_prev_ann_saudi := to_char(l_tot_prev_ann_saudi,lg_format_mask);
2164 vXMLTable(vCtr).TagName := 'G4-B-01-4';
2165 --vXMLTable(vCtr).TagValue := l_tot_prev_ann_saudi - trunc(l_tot_prev_ann_saudi);
2166 vXMLTable(vCtr).TagValue := substr(l_fm_tot_prev_ann_saudi,length(l_fm_tot_prev_ann_saudi)-1);
2167 vctr := vctr + 1;
2168 vXMLTable(vCtr).TagName := 'G4-B-01-5';
2169 --vXMLTable(vCtr).TagValue := trunc(l_tot_prev_ann_saudi);
2170 vXMLTable(vCtr).TagValue := substr(l_fm_tot_prev_ann_saudi,1,length(l_fm_tot_prev_ann_saudi)-3);
2171 vctr := vctr + 1;
2172 l_fm_tot_prev_haz_all := null;
2173 l_fm_tot_prev_haz_all := to_char(l_tot_prev_haz_all,lg_format_mask);
2174 vXMLTable(vCtr).TagName := 'G4-B-01-6';
2175 --vXMLTable(vCtr).TagValue := l_tot_prev_haz_all - trunc(l_tot_prev_haz_all);
2176 vXMLTable(vCtr).TagValue := substr(l_fm_tot_prev_haz_all,length(l_fm_tot_prev_haz_all)-1);
2177 vctr := vctr + 1;
2178 vXMLTable(vCtr).TagName := 'G4-B-01-7';
2179 --vXMLTable(vCtr).TagValue := trunc(l_tot_prev_haz_all);
2180 vXMLTable(vCtr).TagValue := substr(l_fm_tot_prev_haz_all,1,length(l_fm_tot_prev_haz_all)-3);
2181 vctr := vctr + 1;
2182 l_fm_tot_prev_haz_ann_saudi := null;
2183 l_fm_tot_prev_haz_ann_saudi := to_char(l_tot_prev_haz_ann_saudi,lg_format_mask);
2184 vXMLTable(vCtr).TagName := 'G4-B-01-8';
2185 --vXMLTable(vCtr).TagValue := l_tot_prev_haz_ann_saudi - trunc(l_tot_prev_haz_ann_saudi);
2186 vXMLTable(vCtr).TagValue := substr(l_fm_tot_prev_haz_ann_saudi,length(l_fm_tot_prev_haz_ann_saudi)-1);
2187 vctr := vctr + 1;
2188 vXMLTable(vCtr).TagName := 'G4-B-01-9';
2189 --vXMLTable(vCtr).TagValue := trunc(l_tot_prev_haz_ann_saudi);
2190 vXMLTable(vCtr).TagValue := substr(l_fm_tot_prev_haz_ann_saudi,1,length(l_fm_tot_prev_haz_ann_saudi)-3);
2191 vctr := vctr + 1;
2192 ************************************************************/
2193 --For determining the GOSI reference salary use hazards value and apply the hazards percent
2194 BEGIN
2195 SELECT global_value
2196 INTO l_hazards_pct
2197 FROM ff_globals_f
2198 WHERE legislation_code='SA'
2199 AND business_group_id IS NULL
2200 AND global_name = 'SA_ER_HAZARDS_PCT';
2201 EXCEPTION
2202 WHEN OTHERS THEN
2203 l_hazards_pct := 0;
2204 END;
2205 --Annuities percent is used for determining reference salaries of employees who have only annuities flag enabled
2206 BEGIN
2207 SELECT global_value
2208 INTO l_ee_annuities_pct
2209 FROM ff_globals_f
2210 WHERE legislation_code='SA'
2211 AND business_group_id IS NULL
2212 AND global_name = 'SA_EE_ANNUITIES_PCT';
2213 EXCEPTION
2214 WHEN OTHERS THEN
2215 l_ee_annuities_pct := 0;
2216 END;
2217 BEGIN
2218 SELECT global_value
2219 INTO l_er_annuities_pct
2220 FROM ff_globals_f
2221 WHERE legislation_code='SA'
2222 AND business_group_id IS NULL
2223 AND global_name = 'SA_ER_ANNUITIES_PCT';
2224 EXCEPTION
2225 WHEN OTHERS THEN
2226 l_er_annuities_pct := 0;
2227 END;
2228 vXMLTable(vCtr).TagName := 'G4-B-02-1';
2229 vXMLTable(vCtr).TagValue := nvl(l_c_joiner_saudi_ann_haz,0);
2230 vctr := vctr + 1;
2231 /**********************************
2232 vXMLTable(vCtr).TagName := 'G4-B-02-2';
2233 vXMLTable(vCtr).TagValue := nvl(l_c_joiner_haz,0);
2234 vctr := vctr + 1;
2235 vXMLTable(vCtr).TagName := 'G4-B-02-3';
2236 --vXMLTable(vCtr).TagValue := l_hc_haz_ann_saudi_new;
2237 vXMLTable(vCtr).TagValue := nvl(l_c_joiner_saudi_ann_haz,0);
2238 vctr := vctr + 1;
2239 ***********************************/
2240 l_gosi_ref_saudi_new := (((l_curr_haz_saudi_new + l_curr_haz_saudi_new_ter)* 100)/l_hazards_pct) +
2241 ((l_tot_j_emp_annuity * 100)/(l_ee_annuities_pct + l_er_annuities_pct)) ;
2242 l_fm_gosi_ref_saudi_new := to_char(l_gosi_ref_saudi_new,lg_format_mask);
2243 vXMLTable(vCtr).TagName := 'G4-B-02-2';
2244 vXMLTable(vCtr).TagValue := substr(l_fm_gosi_ref_saudi_new,length(l_fm_gosi_ref_saudi_new)-1);
2245 vctr := vctr + 1;
2246 vXMLTable(vCtr).TagName := 'G4-B-02-3';
2247 vXMLTable(vCtr).TagValue := substr(l_fm_gosi_ref_saudi_new,1,length(l_fm_gosi_ref_saudi_new)-3);
2248 vctr := vctr + 1;
2249 vXMLTable(vCtr).TagName := 'G4-B-02-4';
2250 vXMLTable(vCtr).TagValue := nvl(l_c_joiner_nonsaudi_haz ,0);
2251 vctr := vctr + 1;
2252 l_gosi_ref_nonsaudi_new := ((l_curr_haz_new + l_curr_haz_new_ter) * 100)/l_hazards_pct;
2253 l_fm_gosi_ref_nonsaudi_new := to_char(l_gosi_ref_nonsaudi_new,lg_format_mask);
2254 vXMLTable(vCtr).TagName := 'G4-B-02-5';
2255 vXMLTable(vCtr).TagValue := substr(l_fm_gosi_ref_nonsaudi_new,length(l_fm_gosi_ref_nonsaudi_new)-1);
2256 vctr := vctr + 1;
2257 vXMLTable(vCtr).TagName := 'G4-B-02-6';
2258 vXMLTable(vCtr).TagValue := substr(l_fm_gosi_ref_nonsaudi_new,1,length(l_fm_gosi_ref_nonsaudi_new)-3);
2259 vctr := vctr + 1;
2260 vXMLTable(vCtr).TagName := 'G4-B-03-1';
2261 vXMLTable(vCtr).TagValue := nvl(l_hc_haz_ann_saudi_ter,0);
2262 vctr := vctr + 1;
2263 l_gosi_ref_saudi_ter := ((l_sum_saudi_hazards_t + l_prev_haz_saudi_ter + l_prev_haz_saudi_new_ter) * 100)/l_hazards_pct +
2264 ((l_tot_l_emp_annuity * 100)/(l_ee_annuities_pct + l_er_annuities_pct));
2265 l_fm_gosi_ref_saudi_ter := to_char(l_gosi_ref_saudi_ter ,lg_format_mask);
2266 vXMLTable(vCtr).TagName := 'G4-B-03-2';
2267 vXMLTable(vCtr).TagValue := substr(l_fm_gosi_ref_saudi_ter,length(l_fm_gosi_ref_saudi_ter)-1);
2268 vctr := vctr + 1;
2269 vXMLTable(vCtr).TagName := 'G4-B-03-3';
2270 vXMLTable(vCtr).TagValue := substr(l_fm_gosi_ref_saudi_ter,1,length(l_fm_gosi_ref_saudi_ter)-3);
2271 vctr := vctr + 1;
2272 vXMLTable(vCtr).TagName := 'G4-B-03-4';
2273 vXMLTable(vCtr).TagValue :=nvl(l_hc_haz_nonsaudi_ter ,0);
2274 vctr := vctr + 1;
2275 l_gosi_ref_nonsaudi_ter := ((l_hazards_ter_all - (l_sum_saudi_hazards_t + l_prev_haz_saudi_ter + l_prev_haz_saudi_new_ter)) * 100)/l_hazards_pct;
2276 l_fm_gosi_ref_nonsaudi_ter := to_char(l_gosi_ref_nonsaudi_ter,lg_format_mask);
2277 vXMLTable(vCtr).TagName := 'G4-B-03-5';
2278 vXMLTable(vCtr).TagValue := substr(l_fm_gosi_ref_nonsaudi_ter,length(l_fm_gosi_ref_nonsaudi_ter)-1);
2279 vctr := vctr + 1;
2280 vXMLTable(vCtr).TagName := 'G4-B-03-6';
2281 vXMLTable(vCtr).TagValue := substr(l_fm_gosi_ref_nonsaudi_ter,1,length(l_fm_gosi_ref_nonsaudi_ter)-3);
2282 vctr := vctr + 1;
2283 l_fm_total := to_char((l_curr_haz_ann + nvl(p_arrears,0) + nvl(p_penalty_charge,0) - nvl(p_discount,0)),lg_format_mask);
2284 vXMLTable(vCtr).TagName := 'G4-C-05-1';
2285 vXMLTable(vCtr).TagValue := substr(l_fm_total,length(l_fm_total)-1);
2286 vctr := vctr + 1;
2287 vXMLTable(vCtr).TagName := 'G4-C-05-2';
2288 vXMLTable(vCtr).TagValue := substr(l_fm_total,1,length(l_fm_total)-3);
2289 vctr := vctr + 1;
2290 IF p_payment_method IS NOT NULL THEN
2291 IF p_payment_method = '1' THEN
2292 vXMLTable(vCtr).TagName := 'G4-D-01';
2293 vXMLTable(vCtr).TagValue := 'X';
2294 vctr := vctr + 1;
2295 ELSIF p_payment_method = '2' THEN
2296 vXMLTable(vCtr).TagName := 'G4-D-02';
2297 vXMLTable(vCtr).TagValue := 'X';
2298 vctr := vctr + 1;
2299 ELSIF p_payment_method = '3' THEN
2300 vXMLTable(vCtr).TagName := 'G4-D-03';
2301 vXMLTable(vCtr).TagValue := 'X';
2302 vctr := vctr + 1;
2303 ELSIF p_payment_method = '4' THEN
2304 vXMLTable(vCtr).TagName := 'G4-D-04';
2305 vXMLTable(vCtr).TagValue := 'X';
2306 vctr := vctr + 1;
2307 END IF;
2308 END IF;
2309 /***********************************************************************
2310 l_fm_annuities_saudi_new := null;
2311 l_fm_annuities_saudi_new := to_char(l_annuities_saudi_new,lg_format_mask);
2312 vXMLTable(vCtr).TagName := 'G4-B-02-4';
2313 vXMLTable(vCtr).TagValue := substr(l_fm_annuities_saudi_new,length(l_fm_annuities_saudi_new)-1);
2314 vctr := vctr + 1;
2315 vXMLTable(vCtr).TagName := 'G4-B-02-5';
2316 vXMLTable(vCtr).TagValue := substr(l_fm_annuities_saudi_new,1,length(l_fm_annuities_saudi_new)-3);
2317 vctr := vctr + 1;
2318 l_fm_hazards_new_all := null;
2319 l_fm_hazards_new_all := to_char(l_hazards_new_all,lg_format_mask);
2320 vXMLTable(vCtr).TagName := 'G4-B-02-6';
2321 vXMLTable(vCtr).TagValue := substr(l_fm_hazards_new_all,length(l_fm_hazards_new_all)-1);
2322 vctr := vctr + 1;
2323 vXMLTable(vCtr).TagName := 'G4-B-02-7';
2324 vXMLTable(vCtr).TagValue := substr(l_fm_hazards_new_all,1,length(l_fm_hazards_new_all)-3);
2325 vctr := vctr + 1;
2326 l_fm_haz_ann_saudi_new := null;
2327 l_fm_haz_ann_saudi_new := to_char(l_haz_ann_saudi_new,lg_format_mask);
2328 vXMLTable(vCtr).TagName := 'G4-B-02-8';
2329 vXMLTable(vCtr).TagValue := substr(l_fm_haz_ann_saudi_new,length(l_fm_haz_ann_saudi_new)-1);
2330 vctr := vctr + 1;
2331 vXMLTable(vCtr).TagName := 'G4-B-02-9';
2332 vXMLTable(vCtr).TagValue := substr(l_fm_haz_ann_saudi_new,1,length(l_fm_haz_ann_saudi_new)-3);
2333 vctr := vctr + 1;
2334 vXMLTable(vCtr).TagName := 'G4-B-03-1';
2335 vXMLTable(vCtr).TagValue := nvl(l_hc_ann_saudi_ter,0);
2336 vctr := vctr + 1;
2337 vXMLTable(vCtr).TagName := 'G4-B-03-2';
2338 vXMLTable(vCtr).TagValue := nvl(l_hc_haz_ter_all,0);
2339 vctr := vctr + 1;
2340 vXMLTable(vCtr).TagName := 'G4-B-03-3';
2341 vXMLTable(vCtr).TagValue := nvl(l_hc_haz_ann_saudi_ter,0);
2342 vctr := vctr + 1;
2343 l_fm_annuities_saudi_ter := null;
2344 l_fm_annuities_saudi_ter := to_char(l_annuities_saudi_ter,lg_format_mask);
2345 vXMLTable(vCtr).TagName := 'G4-B-03-4';
2346 vXMLTable(vCtr).TagValue := substr(l_fm_annuities_saudi_ter,length(l_fm_annuities_saudi_ter)-1);
2347 vctr := vctr + 1;
2348 vXMLTable(vCtr).TagName := 'G4-B-03-5';
2349 vXMLTable(vCtr).TagValue := substr(l_fm_annuities_saudi_ter,1,length(l_fm_annuities_saudi_ter)-3);
2350 vctr := vctr + 1;
2351 l_fm_hazards_ter_all := null;
2352 l_fm_hazards_ter_all := to_char(l_hazards_ter_all,lg_format_mask);
2353 vXMLTable(vCtr).TagName := 'G4-B-03-6';
2354 vXMLTable(vCtr).TagValue := substr(l_fm_hazards_ter_all,length(l_fm_hazards_ter_all)-1);
2355 vctr := vctr + 1;
2356 vXMLTable(vCtr).TagName := 'G4-B-03-7';
2357 vXMLTable(vCtr).TagValue := substr(l_fm_hazards_ter_all,1,length(l_fm_hazards_ter_all)-3);
2358 vctr := vctr + 1;
2359 l_fm_haz_ann_saudi_ter := null;
2360 l_fm_haz_ann_saudi_ter := to_char(l_haz_ann_saudi_ter,lg_format_mask);
2361 vXMLTable(vCtr).TagName := 'G4-B-03-8';
2362 vXMLTable(vCtr).TagValue := substr(l_fm_haz_ann_saudi_ter,length(l_fm_haz_ann_saudi_ter)-1);
2363 vctr := vctr + 1;
2364 vXMLTable(vCtr).TagName := 'G4-B-03-9';
2365 vXMLTable(vCtr).TagValue := substr(l_fm_haz_ann_saudi_ter,1,length(l_fm_haz_ann_saudi_ter)-3);
2366 vctr := vctr + 1;
2367 vXMLTable(vCtr).TagName := 'G4-B-04-1';
2368 vXMLTable(vCtr).TagValue := nvl(l_c_saudi_ann,0);
2369 vctr := vctr + 1;
2370 vXMLTable(vCtr).TagName := 'G4-B-04-2';
2371 vXMLTable(vCtr).TagValue := nvl(l_c_haz,0);
2372 vctr := vctr + 1;
2373 vXMLTable(vCtr).TagName := 'G4-B-04-3';
2374 vXMLTable(vCtr).TagValue := nvl(l_c_saudi_ann_haz,0);
2375 vctr := vctr + 1;
2376 l_fm_tot_curr_ann_saudi := null;
2377 l_fm_tot_curr_ann_saudi := to_char(l_tot_curr_ann_saudi,lg_format_mask);
2378 vXMLTable(vCtr).TagName := 'G4-B-04-4';
2379 vXMLTable(vCtr).TagValue := substr(l_fm_tot_curr_ann_saudi,length(l_fm_tot_curr_ann_saudi)-1);
2380 vctr := vctr + 1;
2381 vXMLTable(vCtr).TagName := 'G4-B-04-5';
2382 vXMLTable(vCtr).TagValue := substr(l_fm_tot_curr_ann_saudi,1,length(l_fm_tot_curr_ann_saudi)-3);
2383 vctr := vctr + 1;
2384 l_fm_tot_curr_haz_all := null;
2385 l_fm_tot_curr_haz_all := to_char(l_tot_curr_haz_all,lg_format_mask);
2386 vXMLTable(vCtr).TagName := 'G4-B-04-6';
2387 vXMLTable(vCtr).TagValue := substr(l_fm_tot_curr_haz_all,length(l_fm_tot_curr_haz_all)-1);
2388 vctr := vctr + 1;
2389 vXMLTable(vCtr).TagName := 'G4-B-04-7';
2390 vXMLTable(vCtr).TagValue := substr(l_fm_tot_curr_haz_all,1,length(l_fm_tot_curr_haz_all)-3);
2391 l_fm_tot_curr_haz_ann_saudi := null;
2392 l_fm_tot_curr_haz_ann_saudi := to_char(l_tot_curr_haz_ann_saudi,lg_format_mask);
2393 vctr := vctr + 1;
2394 vXMLTable(vCtr).TagName := 'G4-B-04-8';
2395 vXMLTable(vCtr).TagValue := substr(l_fm_tot_curr_haz_ann_saudi,length(l_fm_tot_curr_haz_ann_saudi)-1);
2396 vctr := vctr + 1;
2397 vXMLTable(vCtr).TagName := 'G4-B-04-9';
2398 vXMLTable(vCtr).TagValue := substr(l_fm_tot_curr_haz_ann_saudi,1,length(l_fm_tot_curr_haz_ann_saudi)-3);
2399 vctr := vctr + 1;
2400 -----************* Section C************
2401 l_fm_curr_annuities := null;
2402 l_fm_curr_annuities := to_char(l_curr_annuities,lg_format_mask);
2403 vXMLTable(vCtr).TagName := 'G4-C-01-1';
2404 vXMLTable(vCtr).TagValue := substr(l_fm_curr_annuities,length(l_fm_curr_annuities)-1);
2405 vctr := vctr + 1;
2406 vXMLTable(vCtr).TagName := 'G4-C-01-2';
2407 vXMLTable(vCtr).TagValue := substr(l_fm_curr_annuities,1,length(l_fm_curr_annuities)-3);
2408 vctr := vctr + 1;
2409 l_fm_curr_hazards := null;
2410 l_fm_curr_hazards := to_char(l_curr_hazards,lg_format_mask);
2411 vXMLTable(vCtr).TagName := 'G4-C-01-3';
2412 vXMLTable(vCtr).TagValue := substr(l_fm_curr_hazards,length(l_fm_curr_hazards)-1);
2413 vctr := vctr + 1;
2414 vXMLTable(vCtr).TagName := 'G4-C-01-4';
2415 vXMLTable(vCtr).TagValue := substr(l_fm_curr_hazards,1,length(l_fm_curr_hazards)-3);
2416 vctr := vctr + 1;
2417 l_fm_curr_haz_ann := null;
2418 l_fm_curr_haz_ann := to_char(l_curr_haz_ann,lg_format_mask);
2419 vXMLTable(vCtr).TagName := 'G4-C-01-5';
2420 vXMLTable(vCtr).TagValue := substr(l_fm_curr_haz_ann,length(l_fm_curr_haz_ann)-1);
2421 vctr := vctr + 1;
2422 vXMLTable(vCtr).TagName := 'G4-C-01-6';
2423 vXMLTable(vCtr).TagValue := substr(l_fm_curr_haz_ann,1,length(l_fm_curr_haz_ann)-3);
2424 vctr := vctr + 1;
2425 l_fm_arrears := null;
2426 l_fm_arrears := to_char(nvl(p_arrears,0),lg_format_mask);
2427 vXMLTable(vCtr).TagName := 'G4-C-02-1';
2428 vXMLTable(vCtr).TagValue := substr(l_fm_arrears,length(l_fm_arrears)-1);
2429 vctr := vctr + 1;
2430 vXMLTable(vCtr).TagName := 'G4-C-02-2';
2431 vXMLTable(vCtr).TagValue := substr(l_fm_arrears,1,length(l_fm_arrears)-3);
2432 vctr := vctr + 1;
2433 l_fm_penalty_charge := null;
2434 l_fm_penalty_charge := to_char(nvl(p_penalty_charge,0),lg_format_mask);
2435 vXMLTable(vCtr).TagName := 'G4-C-03-1';
2436 vXMLTable(vCtr).TagValue := substr(l_fm_penalty_charge,length(l_fm_penalty_charge)-1);
2437 vctr := vctr + 1;
2438 vXMLTable(vCtr).TagName := 'G4-C-03-2';
2439 vXMLTable(vCtr).TagValue := substr(l_fm_penalty_charge,1,length(l_fm_penalty_charge)-3);
2440 vctr := vctr + 1;
2441 l_fm_discount := null;
2442 l_fm_discount := to_char(nvl(p_discount,0),lg_format_mask);
2443 vXMLTable(vCtr).TagName := 'G4-C-04-1';
2444 vXMLTable(vCtr).TagValue := substr(l_fm_discount,length(l_fm_discount)-1);
2445 vctr := vctr + 1;
2446 vXMLTable(vCtr).TagName := 'G4-C-04-2';
2447 vXMLTable(vCtr).TagValue := substr(l_fm_discount,1,length(l_fm_discount)-3);
2448 vctr := vctr + 1;
2449 l_fm_total := null;
2450 l_fm_total := to_char((l_curr_haz_ann + nvl(p_arrears,0) + nvl(p_penalty_charge,0) - nvl(p_discount,0)),lg_format_mask);
2451 vXMLTable(vCtr).TagName := 'G4-C-05-1';
2452 vXMLTable(vCtr).TagValue := substr(l_fm_total,length(l_fm_total)-1);
2453 vctr := vctr + 1;
2454 vXMLTable(vCtr).TagName := 'G4-C-05-2';
2455 vXMLTable(vCtr).TagValue := substr(l_fm_total,1,length(l_fm_total)-3);
2456 vctr := vctr + 1;
2457 ***********************************************************************/
2458 /*Msg in the temorary table*/
2459 hr_utility.set_location('Finished Procedure Populate Monthly Contribution Report ',60);
2460 /* -----------------------------------------------------------------------------
2461 -- Writing into XML File
2462 -----------------------------------------------------------------------------
2463 -- Assigning the File name.
2464 l_file_name1 := to_char(p_request_id) || '.xml';
2465 -- Getting the Util file directory name.mostly it'll be /sqlcom/outbound )
2466 BEGIN
2467 SELECT value
2468 INTO l_audit_log_dir1
2469 FROM v$parameter
2470 WHERE LOWER(name) = 'utl_file_dir';
2471 -- Check whether more than one util file directory is found
2472 IF INSTR(l_audit_log_dir1,',') > 0 THEN
2473 l_audit_log_dir1 := substr(l_audit_log_dir1,1,instr(l_audit_log_dir1,',')-1);
2474 END IF;
2475 EXCEPTION
2476 when no_data_found then
2477 null;
2478 END;
2479 -- Find out whether the OS is MS or Unix based
2480 -- If it's greater than 0, it's unix based environment
2481 IF INSTR(l_audit_log_dir1,'/') > 0 THEN
2482 p_output_fname := l_audit_log_dir1 || '/' || l_file_name1;
2483 ELSE
2484 p_output_fname := l_audit_log_dir1 || '\' || l_file_name1;
2485 END IF;
2486 -- getting Agency name
2487 p_l_fp1 := utl_file.fopen(l_audit_log_dir1,l_file_name1,'A');
2488 IF L_FILE_CREATED = 0 THEN
2489 utl_file.put_line(p_l_fp1,'<?xml version="1.0" encoding="UTF-8"?>');
2490 utl_file.put_line(p_l_fp1,'<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">');
2491 END IF;
2492 -- Writing from and to dates
2493 utl_file.put_line(p_l_fp1,'<fields>');
2494 -- Write the header fields to XML File.
2495 --WriteXMLvalues(p_l_fp,'P0_from_date',to_char(p_from_date,'dd') || ' ' || trim(to_char(p_from_date,'Month')) || ' ' || to_char(p_from_date,'yyyy') );
2496 --WriteXMLvalues(p_l_fp,'P0_to_date',to_char(p_to_date,'dd') || ' ' ||to_char(p_to_date,'Month') || ' ' || to_char(p_to_date,'yyyy') );
2497 -- Loop through PL/SQL Table and write the values into the XML File.
2498 -- Need to try FORALL instead of FOR
2499 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
2500 WriteXMLvalues(p_l_fp1,vXMLTable(ctr_table).TagName ,vXMLTable(ctr_table).TagValue);
2501 END LOOP;
2502 -- Write the end tag and close the XML File.
2503 utl_file.put_line(p_l_fp1,'</fields>');
2504 --utl_file.put_line(p_l_fp1,'</xfdf>');
2505 utl_file.fclose(p_l_fp1);
2506 l_file_created := 1; */
2507 END LOOP;
2508 /*End of call to report5*/
2509 WritetoCLOB ( l_xfdf_blob );
2510 -- Write the values to XML File
2511 /* fnd_file.put_line(fnd_file.log,'Calling Procedure to write into XML File');
2512 WritetoXML(
2513 p_request_id,
2514 p_report,
2515 l_file_name);
2516 p_output_fname := l_file_name;
2517 fnd_file.put_line(fnd_file.log,'------------Output XML File----------------');
2518 fnd_file.put_line(fnd_file.log,'File' || l_file_name );
2519 fnd_file.put_line(fnd_file.log,'-------------------------------------------');
2520 p_output_fname := l_file_name;*/
2521 /*
2522 EXCEPTION
2523 WHEN utl_file.invalid_path then
2524 hr_utility.set_message(8301, 'GHR_38830_INVALID_UTL_FILE_PATH');
2525 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2526 hr_utility.raise_error;
2527 --
2528 WHEN utl_file.invalid_mode then
2529 hr_utility.set_message(8301, 'GHR_38831_INVALID_FILE_MODE');
2530 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2531 hr_utility.raise_error;
2532 --
2533 WHEN utl_file.invalid_filehandle then
2534 hr_utility.set_message(8301, 'GHR_38832_INVALID_FILE_HANDLE');
2535 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2536 hr_utility.raise_error;
2537 --
2538 WHEN utl_file.invalid_operation then
2539 hr_utility.set_message(8301, 'GHR_38833_INVALID_OPER');
2540 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2541 hr_utility.raise_error;
2542 --
2543 WHEN utl_file.read_error then
2544 hr_utility.set_message(8301, 'GHR_38834_FILE_READ_ERROR');
2545 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2546 hr_utility.raise_error;
2547 --
2548 WHEN others THEN
2549 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
2550 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
2551 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2552 hr_utility.raise_error;
2553 */
2554 end populate_monthly_contributions;
2555 ----------------------------------------------
2556 ----------------------------------------------
2557 /*SA Worker Movement Report*/
2558 procedure populate_workers_movement
2559 ( p_request_id in number,
2560 p_report in varchar2,
2561 p_business_group_id in number,
2562 p_org_structure_version_id in number DEFAULT NULL,
2563 p_organisation_id in number,
2564 p_form_type in varchar2 DEFAULT NULL,
2565 p_effective_date in varchar2,
2566 p_assignment_id in number DEFAULT NULL,
2567 p_assignment_set_id in number DEFAULT NULL,
2568 l_xfdf_blob OUT NOCOPY BLOB)
2569 is
2570 l_person_id number;
2571 l_employer_gosi_number varchar2(40);
2572 l_employee_gosi_number number;
2573 l_employee_monthly_cont number;
2574 l_post_box varchar2(250);
2575 l_defined_balance_id number;
2576 l_assignment_set_id number;
2577 l_assignment_action_id number;
2578 l_payroll_id number;
2579 l_first_name varchar2(250);
2580 l_father_name varchar2(250);
2581 l_grandfather_name varchar2(250);
2582 l_family_name varchar2(250);
2583 l_gosi_office varchar2(250);
2584 l_employer_name varchar2(250);
2585 l_city varchar2(250);
2586 l_city_mn varchar2(80);
2587 l_area varchar2(250);
2588 l_id_card_number varchar2(50);
2589 l_civ_id_num number;
2590 l_gender varchar2(50);
2591 l_gender_male varchar2(50);
2592 l_gender_female varchar2(50);
2593 l_marital_status varchar2(50);
2594 l_married varchar2(50);
2595 l_single varchar2(50);
2596 l_date_of_birth date;
2597 l_occupation varchar2(250);
2598 l_termination_reason varchar2(200);
2599 l_employee_joining_date date;
2600 l_annuities_join_date varchar2(100);
2601 l_hazards_join_date varchar2(100);
2602 l_termination_date date;
2603 l_effective_date date;
2604 l_day varchar2(10);
2605 l_month varchar2(10);
2606 l_year varchar2(10);
2607 l_employer_GOSI_code varchar2(240);
2608 l_employer_GOSI_office varchar2(240);
2609 l_employer_GOSI_office_name varchar2(240);
2610 l_org_id number;
2611 l_hz_date date;
2612 l_form_new_unreg varchar2(30);
2613 l_form_new_reg varchar2(30);
2614 l_form_term varchar2(30);
2615 type t_varchar is table of varchar2(20)
2616 index by binary_integer;
2617 t_form_type t_varchar;
2618 m number := 0;
2619 l_nationality varchar2(30);
2620 l_nationality_mn varchar2(80);
2621 l_hafiza_number varchar2(30);
2622 l_hafiza_date date;
2623 l_hafiza_place varchar2(260);
2624 l_passport_number varchar2(150);
2625 l_passport_issue_date date;
2626 l_passport_issue_place varchar2(250);
2627 l_qualification_type varchar2(150);
2628 l_employee_number varchar2(80);
2629 l_work_location varchar2(240);
2630 l_street varchar2(150);
2631 l_email_id varchar2(240);
2632 l_zip_code varchar2(80);
2633 /*Cursor to pick up GOSI Code for the GOSI Office */
2634 cursor csr_gosi_code (p_Gosi_Office_Id Number) is
2635 select org_information1
2636 from hr_organization_information
2637 where organization_id = p_Gosi_Office_Id
2638 and org_information_context = 'SA_GOSI_OFFICE_DETAILS';
2639 -- Cursor to populate Part G5-A-01,G5-A-05
2640 /*Cursor to pick up Gosi Number and Gosi office for the employer (GRE)*/
2641 cursor get_employer_GOSI (p_gre_id number) is
2642 select org_information1,org_information2
2643 from hr_organization_information
2644 where organization_id = p_gre_id
2645 and org_information_context = 'SA_EMPLOYER_GOSI_DETAILS';
2646 /*Cursor to select personal information for employee*/
2647 cursor get_info_per (l_assignment_id number, l_effective_date date) is
2648 select first_name,
2649 per_information1,
2650 per_information2,
2651 per_information10,
2652 last_name
2653 from per_all_people_f peo
2654 ,per_all_assignments_f paa
2655 where peo.person_id = paa.person_id
2656 and paa.assignment_id = l_assignment_id;
2657 --and l_effective_date between paa.effective_start_date and paa.effective_end_date
2658 --and l_effective_date between peo.effective_start_date and peo.effective_end_date;
2659 /*Cursor to select assignments from a given GRE*/
2660 cursor csr_get_gre_assignments (l_employer_id number, l_business_group_id number, l_effective_date date,l_form_type varchar2) is
2661 select /*+ INDEX(hsck, HR_SOFT_CODING_KEYFLEX_PK) */ distinct assignment_id
2662 from per_all_assignments_f paa,
2663 hr_soft_coding_keyflex hsck,
2664 per_periods_of_service pos
2665 where hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
2666 and (nvl(hsck.segment3,'N') = 'Y' OR nvl(hsck.segment5,'N') = 'Y')
2667 and paa.business_group_id = l_business_group_id
2668 --and l_effective_date between paa.effective_start_date and paa.effective_end_date
2669 and hsck.ID_FLEX_NUM = 20
2670 and hsck.segment1= to_char(l_employer_id)
2671 and paa.period_of_service_id = pos.period_of_service_id
2672 and ((l_form_type = 'NU'
2673 and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
2674 and trunc(pos.date_start) <= trunc(l_effective_date)
2675 and hsck.segment2 is null)
2676 or (l_form_type = 'NR'
2677 and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
2678 and trunc(pos.date_start) <= trunc(l_effective_date)
2679 and hsck.segment2 is not null)
2680 or (l_form_type = 'TM'
2681 and trunc(pos.actual_termination_date,'MM') = trunc(l_effective_date,'MM')
2682 and trunc(pos.actual_termination_date) <= trunc(l_effective_date)));
2683 /*Cursor to select assignments for a given assignment set*/
2684 cursor csr_get_assignment(l_assignment_set_id number, l_form_type varchar2) is
2685 select distinct has.assignment_id
2686 from hr_assignment_set_amendments has
2687 ,per_all_assignments_f paa
2688 ,per_periods_of_service pos
2689 ,hr_soft_coding_keyflex hsck
2690 where assignment_set_id = l_assignment_set_id
2691 and include_or_exclude = 'I'
2692 and has.assignment_id = paa.assignment_id
2693 --and p_effective_date between paa.effective_start_date and paa.effective_end_date
2694 and hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
2695 and hsck.ID_FLEX_NUM = 20
2696 and hsck.segment1= to_char(p_organisation_id)
2697 and (nvl(hsck.segment3,'N') = 'Y' OR nvl(hsck.segment5,'N') = 'Y')
2698 and paa.period_of_service_id = pos.period_of_service_id
2699 and ((l_form_type = 'NU'
2700 and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
2701 and trunc(pos.date_start) <= trunc(l_effective_date)
2702 and hsck.segment2 is null)
2703 or (l_form_type = 'NR'
2704 and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
2705 and trunc(pos.date_start) <= trunc(l_effective_date)
2706 and hsck.segment2 is not null)
2707 or (l_form_type = 'TM'
2708 and trunc(pos.actual_termination_date,'MM') = trunc(l_effective_date,'MM')
2709 and trunc(pos.actual_termination_date) <= trunc(l_effective_date)));
2710 /* Cursor to select Employee GOSI Number */
2711 cursor get_emp_gosi (l_assignment_id NUMBER, l_effective_date Date) is
2712 select hscl.segment2
2713 from hr_soft_coding_keyflex hscl
2714 ,per_all_assignments_f paa
2715 where hscl.SOFT_CODING_KEYFLEX_ID = paa.soft_CODING_KEYFLEX_ID
2716 and paa.assignment_id = l_assignment_id
2717 and hscl.id_flex_num = 20;
2718 --and l_effective_date between paa.effective_start_date and paa.effective_end_date;
2719 /* Cursor to pick up Employee's Organization address */
2720 cursor get_town_old (l_assignment_id NUMBER, l_effective_date Date) is
2721 select hla.town_or_city
2722 ,hla.region_2
2723 ,hla.region_3
2724 from hr_locations_all hla
2725 ,hr_organization_units hou
2726 ,per_all_assignments_f paa
2727 where paa.organization_id = hou.organization_id
2728 and hou.location_id = hla.location_id
2729 and paa.assignment_id = l_assignment_id
2730 --and l_effective_date between paa.effective_start_date and paa.effective_end_date
2731 and paa.business_group_id = hou.business_group_id;
2732 /* Cursor to get person_id*/
2733 CURSOR get_person_id (l_assignment_id NUMBER) IS
2734 SELECT person_id
2735 FROM per_all_assignments_f
2736 WHERE assignment_id = l_assignment_id;
2737 /*Cursor to pick up employee primary address*/
2738 CURSOR get_town(l_person_id NUMBER, l_effective_date DATE) IS
2739 SELECT pa.town_or_city city,
2740 pa.region_1 R1,
2741 pa.region_2 R2,
2742 pa.region_3 R3,
2743 pa.postal_code ZIP
2744 FROM per_addresses pa
2745 WHERE pa.primary_flag = 'Y'
2746 AND pa.person_id = l_person_id
2747 AND l_effective_date BETWEEN trunc(pa.date_from,'MM')
2748 AND nvl(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
2749 /*Cursor to pick up person details*/
2750 cursor get_infos (l_assignment_id NUMBER, l_effective_date Date) is
2751 select peo.national_identifier
2752 ,peo.sex
2753 ,peo.marital_status
2754 ,peo.date_of_birth
2755 from per_all_people_f peo
2756 ,per_all_assignments_f paa
2757 where peo.person_id = paa.person_id
2758 and paa.assignment_id = l_assignment_id;
2759 --and l_effective_date between paa.effective_start_date and paa.effective_end_date
2760 --and l_effective_date between peo.effective_start_date and peo.effective_end_date;
2761 /*Cursor to pick up Employee's Job Name*/
2762 cursor get_job (l_assignment_id NUMBER, l_effective_date Date) is
2763 select name
2764 from per_jobs pj,
2765 per_all_assignments_f paa
2766 where pj.job_id = paa.job_id
2767 and paa.business_group_id = pj.business_group_id
2768 and paa.assignment_id = l_assignment_id;
2769 --and l_effective_date between paa.effective_start_date and paa.effective_end_date;
2770 /*Cursor to pick up Employee's Work Location*/
2771 cursor get_work_location (l_assignment_id NUMBER) is
2772 select hlp.meaning --distinct TOWN_OR_CITY
2773 from hr_locations hl,
2774 hr_lookups hlp,
2775 per_all_assignments_f paa
2776 where hl.location_id = paa.location_id
2777 and paa.assignment_id = l_assignment_id
2778 and hlp.lookup_type = 'SA_CITY'
2779 and hlp.lookup_code = hl.TOWN_OR_CITY;
2780 /* Cursor to fetch nationality, employee_number , e-mail address*/
2781 CURSOR get_nationality (l_person_id NUMBER , l_date date) IS
2782 select nationality,employee_number,email_address
2783 from per_all_people_f ppf
2784 where ppf.person_id = l_person_id
2785 and l_date between ppf.effective_start_date and ppf.effective_end_date;
2786 --cursor to get the passport number and other details
2787 cursor get_passport_number (l_person_id number,l_date date) is
2788 SELECT pei.pei_information1 , fnd_date.canonical_to_date(pei.pei_information3) , pei.pei_information5
2789 FROM per_people_extra_info pei
2790 WHERE pei.person_id = l_person_id
2791 AND pei.information_type = 'SA_PASSPORT' AND pei.pei_information_category = 'SA_PASSPORT'
2792 AND l_date between trunc(fnd_date.canonical_to_date(pei.pei_information3),'MM') and fnd_date.canonical_to_date(pei.pei_information4);
2793 --cursor to get the hafiza number
2794 cursor get_hafiza_number (l_person_id number) is
2795 SELECT pei.pei_information1,fnd_date.canonical_to_date(pei.pei_information2),pei.pei_information3
2796 FROM per_people_extra_info pei
2797 WHERE pei.person_id = l_person_id
2798 AND pei.information_type = 'SA_HAFIZA' AND pei.pei_information_category = 'SA_HAFIZA'
2799 AND rowid = (select max(rowid) from per_people_extra_info where person_id = l_person_id ) ;
2800 --cursor to get employers town or city
2801 CURSOR csr_get_work_location (l_organization_id number) IS
2802 select town_or_city
2803 from hr_locations hl, hr_all_organization_units hau
2804 where hau.organization_id = l_organization_id
2805 and hau.location_id = hl.location_id;
2806 /*Cursor to get Start and Termination date of employee*/
2807 cursor get_start_date (l_assignment_id NUMBER, l_effective_date Date) is
2808 select pps.date_start
2809 ,pps.actual_termination_date
2810 ,pps.leaving_reason
2811 from per_periods_of_service pps
2812 ,per_all_assignments_f paa
2813 where /*pps.person_id = paa.person_id*/
2814 pps.period_of_service_id = paa.period_of_service_id
2815 and paa.business_group_id = pps.business_group_id
2816 and paa.assignment_id = l_assignment_id;
2817 --and l_effective_date between paa.effective_start_date and paa.effective_end_date;
2818 /*Cursor to pick up GOSI hazards date*/
2819 cursor get_hazards_date (l_assignment_id NUMBER, l_effective_date Date) is
2820 select hscl.segment4,
2821 hscl.segment6
2822 from hr_soft_coding_keyflex hscl
2823 ,per_all_assignments_f paa
2824 where hscl.SOFT_CODING_KEYFLEX_ID = paa.soft_CODING_KEYFLEX_ID
2825 and paa.assignment_id = l_assignment_id
2826 and hscl.id_flex_num = 20;
2827 --and l_effective_date between paa.effective_start_date and paa.effective_end_date;
2828 /*Cursor to get employer for the employee*/
2829 cursor get_employer (l_assignment_id NUMBER, l_effective_date Date) is
2830 select hscl.segment1
2831 from hr_soft_coding_keyflex hscl
2832 ,per_all_assignments_f paa
2833 where hscl.SOFT_CODING_KEYFLEX_ID = paa.soft_CODING_KEYFLEX_ID
2834 and paa.assignment_id = l_assignment_id
2835 and hscl.id_flex_num = 20;
2836 --and l_effective_date between paa.effective_start_date and paa.effective_end_date;
2837 --get employee monthly contribution
2838 --get defined_balance_id
2839 /*Cursor to pick up assignment actions*/
2840 cursor get_assact_id (l_assignment_id NUMBER, l_effective_date Date) is
2841 select paa.assignment_action_id
2842 from pay_assignment_actions paa
2843 ,per_all_assignments_f paf
2844 ,pay_payroll_actions ppa
2845 where paa.assignment_id = paf.assignment_id
2846 and paf.assignment_id = l_assignment_id
2847 and ppa.payroll_id = paf.payroll_id
2848 and ppa.payroll_action_id = paa.payroll_action_id
2849 and ppa.action_type in ('R','Q')
2850 and ppa.action_status = 'C'
2851 and paa.action_status IN ('C','S')
2852 and trunc(ppa.date_earned,'MM') = trunc(l_effective_date,'MM');
2853 --and l_effective_date between paf.effective_start_date and paf.effective_end_date;
2854 /*Cursor to get nodes from hierarchy*/
2855 cursor csr_org_hierarchy(p_organisation_id number) is
2856 select pose.organization_id_child org
2857 from per_org_structure_elements pose
2858 connect by pose.organization_id_parent = prior pose.organization_id_child
2859 and pose.org_structure_version_id = p_org_structure_version_id
2860 start with pose.organization_id_parent = p_organisation_id
2861 and pose.org_structure_version_id = p_org_structure_version_id
2862 union
2863 select p_organisation_id org
2864 from dual;
2865 rec_org_id csr_org_hierarchy%rowtype;
2866 /* Cursor to fetch lower limit of gosi base*/
2867 CURSOR get_lower_base(l_effective_date DATE) IS
2868 SELECT global_value
2869 FROM ff_globals_f
2870 WHERE global_name = 'SA_GOSI_BASE_LOWER_LIMIT'
2871 AND legislation_code = 'SA'
2872 AND business_group_id IS NULL
2873 AND l_effective_date BETWEEN effective_start_date
2874 AND effective_end_date;
2875 /* Cursor to fetch upper limit of gosi base*/
2876 CURSOR get_upper_base(l_effective_date DATE) IS
2877 SELECT global_value
2878 FROM ff_globals_f
2879 WHERE global_name = 'SA_GOSI_BASE_UPPER_LIMIT'
2880 AND legislation_code = 'SA'
2881 AND business_group_id IS NULL
2882 AND l_effective_date BETWEEN effective_start_date
2883 AND effective_end_date;
2884 /* Cursor to fetch Phone types for employee*/
2885 CURSOR get_phone_type (l_bus_grp_id NUMBER) IS
2886 SELECT hoi.org_information3 mob_type
2887 ,hoi.org_information4 tel_type
2888 FROM hr_organization_information hoi
2889 WHERE hoi.organization_id = l_bus_grp_id
2890 AND hoi.org_information_context = 'SA_HR_BG_INFO';
2891 /* Cursor to fetch phone numbers of employee */
2892 CURSOR get_phone_number (l_phone_type VARCHAR2, l_person_id NUMBER, l_effective_date DATE) IS
2893 SELECT phone_number
2894 FROM per_phones pp,
2895 per_all_people_f pap
2896 WHERE pp.parent_id = pap.person_id
2897 AND pp.phone_type = l_phone_type
2898 AND pap.person_id = l_person_id
2899 AND l_effective_date BETWEEN pp.date_from
2900 AND nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'));
2901 /*Table type for variable for storing legal entities within the hierarchy*/
2902 TYPE t_rec_gre IS RECORD(GRE_NAME varchar2(80), GRE_ID number);
2903 TYPE t_tab_gre IS TABLE OF t_rec_gre
2904 INDEX BY BINARY_INTEGER;
2905 t_legal_entity t_tab_gre;
2906 TYPE t_rec_emp IS RECORD(emp_id number);
2907 TYPE t_tab_emp is TABLE of t_rec_emp
2908 INDEX BY BINARY_INTEGER;
2909 t_emp t_tab_emp;
2910 i NUMBER(15);
2911 l_gre_name HR_ORGANIZATION_UNITS.NAME%TYPE;
2912 l_gre_id HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE;
2913 l_gre_present NUMBER;
2914 l_person_id_new number;
2915 l_lower_base VARCHAR2(10) := NULL;
2916 l_upper_base VARCHAR2(10) := NULL;
2917 l_form VARCHAR2(20):= NULL;
2918 l_fm_employee_monthly_cont varchar2(50) := null;
2919 l_mobile_number VARCHAR2(30) := null;
2920 l_tel_number VARCHAR2(30) := null;
2921 l_mob_type VARCHAR2(10);
2922 l_tel_type VARCHAR2(10);
2923 begin
2924 set_currency_mask(p_business_group_id);
2925 if p_form_type is not null then
2926 t_form_type(0) := p_form_type;
2927 else
2928 t_form_type(0) := 'NU';
2929 t_form_type(1) := 'NR';
2930 t_form_type(2) := 'TM';
2931 end if;
2932 vXMLTable.DELETE;
2933 vCtr := 1;
2934 --l_effective_date := last_day(fnd_date.canonical_to_date(p_effective_date));
2935 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
2936 insert into fnd_sessions(session_id,effective_date) values (userenv('sessionid'), l_effective_date);
2937 for m in t_form_type.first .. t_form_type.last
2938 loop
2939 l_form := t_form_type(m);
2940 T_EMP.DELETE;
2941 T_LEGAL_ENTITY.DELETE;
2942 hr_utility.set_location('Before hierarchy logic',70);
2943 If p_assignment_id is not null then
2944 if p_assignment_set_id is not null then
2945 begin
2946 select 1 into i
2947 from hr_assignment_set_amendments haa
2948 ,per_all_assignments_f paa
2949 ,hr_soft_coding_keyflex hscl
2950 ,per_periods_of_service pos
2951 where assignment_set_id = p_assignment_set_id
2952 and include_or_exclude = 'I'
2953 and hscl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
2954 and paa.assignment_id = haa.assignment_id
2955 and haa.assignment_id = p_assignment_id
2956 --and l_effective_date between paa.effective_start_date and paa.effective_end_date
2957 and hscl.segment1 = to_char(p_organisation_id)
2958 and hscl.id_flex_num = 20
2959 and (nvl(hscl.segment3,'N') = 'Y' OR nvl(hscl.segment5,'N') = 'Y')
2960 and paa.period_of_service_id = pos.period_of_service_id
2961 and ((l_form = 'NU'
2962 and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
2963 and trunc(pos.date_start) <= trunc(l_effective_date)
2964 and hscl.segment2 is null)
2965 or (l_form = 'NR'
2966 and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
2967 and trunc(pos.date_start) <= trunc(l_effective_date)
2968 and hscl.segment2 is not null)
2969 or (l_form = 'TM'
2970 and trunc(pos.actual_termination_date,'MM') = trunc(l_effective_date,'MM')
2971 and trunc(pos.actual_termination_date) <= trunc(l_effective_date)))
2972 and rownum < 2;
2973 t_emp(0).emp_id := p_assignment_id;
2974 exception
2975 when no_data_found then
2976 null;
2977 end;
2978 else
2979 begin
2980 select distinct paa.assignment_id
2981 into t_emp(0).emp_id
2982 from per_all_assignments_f paa,
2983 per_periods_of_service pos,
2984 hr_soft_coding_keyflex hsck
2985 where paa.assignment_id = p_assignment_id
2986 --and p_effective_date between paa.effective_start_date and paa.effective_end_date
2987 and hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
2988 and hsck.ID_FLEX_NUM = 20
2989 and hsck.segment1= to_char(p_organisation_id)
2990 and (nvl(hsck.segment3,'N') = 'Y' OR nvl(hsck.segment5,'N') = 'Y')
2991 and paa.period_of_service_id = pos.period_of_service_id
2992 and ((l_form = 'NU'
2993 and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
2994 and trunc(pos.date_start) <= trunc(l_effective_date)
2995 and hsck.segment2 is null)
2996 or (l_form = 'NR'
2997 and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
2998 and trunc(pos.date_start) <= trunc(l_effective_date)
2999 and hsck.segment2 is not null)
3000 or (l_form = 'TM'
3001 and trunc(pos.actual_termination_date,'MM') = trunc(l_effective_date,'MM')
3002 and trunc(pos.actual_termination_date) <= trunc(l_effective_date)));
3003 exception
3004 when others then
3005 null;
3006 end;
3007 --t_emp(0).emp_id := p_assignment_id;
3008 end if;
3009 elsif p_assignment_set_id is not null then
3010 i := 0;
3011 for valid_assignments in csr_get_assignment(p_assignment_set_id,l_form)loop
3012 t_emp(i).emp_id := valid_assignments.assignment_id;
3013 i := i + 1;
3014 end loop;
3015 else
3016 -- New code begins
3017 l_gre_id := p_organisation_id;
3018 -- New code ends
3019 i := 0;
3020 l_form := t_form_type(m);
3021 for valid_assignments in csr_get_gre_assignments(l_gre_id, p_business_group_id, l_effective_date,l_form) loop
3022 t_emp(i).emp_id := valid_assignments.assignment_id;
3023 i := i + 1;
3024 end loop;
3025 end if;
3026 hr_utility.set_location('Entering procedure workers movement Report ',80);
3027 if t_emp.count > 0 then
3028 for j in t_emp.first..t_emp.last loop
3029 /*Reset the local variables */
3030 l_employer_GOSI_office_name := null;
3031 l_employer_GOSI_code := null;
3032 l_employer_name := null;
3033 l_employer_GOSI_number := null;
3034 l_first_name := null;
3035 l_father_name := null;
3036 l_grandfather_name := null;
3037 l_family_name := null;
3038 l_employee_gosi_number := null;
3039 l_city := null;
3040 l_area := null;
3041 l_post_box := null;
3042 l_id_card_number := null;
3043 l_gender := null;
3044 l_gender_female := null;
3045 l_gender_male := null;
3046 l_marital_status := null;
3047 l_single := null;
3048 l_married := null;
3049 l_date_of_birth := null;
3050 l_year := null;
3051 l_month := null;
3052 l_day := null;
3053 l_occupation := null;
3054 l_employee_joining_date := null;
3055 l_termination_date := null;
3056 l_termination_reason := null;
3057 l_annuities_join_date := null;
3058 l_hazards_join_date := null;
3059 l_assignment_action_id := null;
3060 l_employee_monthly_cont := null;
3061 open get_employer (t_emp(j).emp_id, l_effective_date);
3062 fetch get_employer into l_org_id;
3063 close get_employer;
3064 begin
3065 select name
3066 into l_employer_name
3067 from hr_organization_units
3068 where organization_id = l_org_id;
3069 exception
3070 when others then
3071 l_employer_name := null;
3072 end;
3073 open get_employer_GOSI(l_org_id);
3074 fetch get_employer_GOSI into l_employer_GOSI_number,l_employer_GOSI_office;
3075 close get_employer_GOSI;
3076 begin
3077 select name
3078 into l_employer_gosi_office_name
3079 from hr_organization_units
3080 where organization_id = l_employer_gosi_office;
3081 exception
3082 when others then
3083 l_employer_gosi_office_name := null;
3084 end;
3085 open csr_GOSI_code(l_employer_GOSI_office);
3086 fetch csr_GOSI_code into l_employer_GOSI_code;
3087 close csr_GOSI_code;
3088 vXMLTable(vCtr).TagName := 'G3-A-01';
3089 vXMLTable(vCtr).TagValue := l_employer_gosi_code||' '||(l_employer_GOSI_office_name);
3090 vCtr := vCtr + 1;
3091 /*vXMLTable(vCtr).TagName := 'G3-A-01-1';
3092 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_code,1,1);
3093 vCtr := vCtr + 1;
3094 vXMLTable(vCtr).TagName := 'G3-A-01-2';
3095 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_code,2,1);
3096 vCtr := vCtr + 1;*/
3097 if t_form_type(m) = 'NU' then
3098 l_form_new_unreg := 'X';
3099 l_form_new_reg := null;
3100 l_form_term := null;
3101 elsif t_form_type(m) = 'NR' then
3102 l_form_new_unreg := null;
3103 l_form_new_reg := 'X';
3104 l_form_term := null;
3105 elsif t_form_type(m) = 'TM' then
3106 l_form_new_unreg := null;
3107 l_form_new_reg := null;
3108 l_form_term := 'X';
3109 end if;
3110 vXMLTable(vCtr).TagName := 'G3-A-02-1';
3111 vXMLTable(vCtr).TagValue := l_form_new_unreg;
3112 vCtr := vCtr + 1;
3113 vXMLTable(vCtr).TagName := 'G3-A-02-2';
3114 vXMLTable(vCtr).TagValue := l_form_new_reg;
3115 vCtr := vCtr + 1;
3116 vXMLTable(vCtr).TagName := 'G3-A-02-3';
3117 vXMLTable(vCtr).TagValue := l_form_term;
3118 vCtr := vCtr + 1;
3119 vXMLTable(vCtr).TagName := 'G3-A-03';
3120 vXMLTable(vCtr).TagValue := l_employer_name;
3121 vCtr := vCtr + 1;
3122 vXMLTable(vCtr).TagName := 'G3-A-04-9';
3123 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,9,1);
3124 vCtr := vCtr + 1;
3125 vXMLTable(vCtr).TagName := 'G3-A-04-8';
3126 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,8,1);
3127 vCtr := vCtr + 1;
3128 vXMLTable(vCtr).TagName := 'G3-A-04-7';
3129 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,7,1);
3130 vCtr := vCtr + 1;
3131 vXMLTable(vCtr).TagName := 'G3-A-04-6';
3132 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,6,1);
3133 vCtr := vCtr + 1;
3134 vXMLTable(vCtr).TagName := 'G3-A-04-5';
3135 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,5,1);
3136 vCtr := vCtr + 1;
3137 vXMLTable(vCtr).TagName := 'G3-A-04-4';
3138 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,4,1);
3139 vCtr := vCtr + 1;
3140 vXMLTable(vCtr).TagName := 'G3-A-04-3';
3141 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,3,1);
3142 vCtr := vCtr + 1;
3143 vXMLTable(vCtr).TagName := 'G3-A-04-2';
3144 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,2,1);
3145 vCtr := vCtr + 1;
3146 vXMLTable(vCtr).TagName := 'G3-A-04-1';
3147 vXMLTable(vCtr).TagValue := substr(l_employer_GOSI_number,1,1);
3148 vCtr := vCtr + 1;
3149 --get personal info - all names of person
3150 open get_info_per(t_emp(j).emp_id, l_effective_date);
3151 fetch get_info_per into l_first_name,l_father_name ,l_grandfather_name ,l_qualification_type , l_family_name;
3152 close get_info_per;
3153 --get employee gosi number
3154 open get_emp_gosi(t_emp(j).emp_id, l_effective_date);
3155 fetch get_emp_gosi into l_employee_gosi_number;
3156 close get_emp_gosi;
3157 --get city, area and post box of employee
3158 /* To get person_id from assignment_id*/
3159 OPEN get_person_id(t_emp(j).emp_id);
3160 FETCH get_person_id INTO l_person_id_new;
3161 CLOSE get_person_id;
3162 open get_town(l_person_id_new, l_effective_date);
3163 fetch get_town into l_city,l_street, l_area, l_post_box,l_zip_code;
3164 close get_town;
3165 If l_city is not null then
3166 BEGIN
3167 SELECT hl.meaning
3168 INTO l_city_mn
3169 FROM hr_lookups hl
3170 WHERE hl.lookup_type = 'SA_CITY'
3171 AND hl.lookup_code = l_city
3172 AND hl.enabled_flag = 'Y';
3173 END;
3174 End if;
3175 OPEN get_phone_type (p_business_group_id);
3176 FETCH get_phone_type INTO l_mob_type, l_tel_type;
3177 CLOSE get_phone_type;
3178 OPEN get_phone_number(l_mob_type,l_person_id_new,l_effective_date);
3179 FETCH get_phone_number INTO l_mobile_number;
3180 CLOSE get_phone_number;
3181 OPEN get_phone_number(l_tel_type,l_person_id_new,l_effective_date);
3182 FETCH get_phone_number INTO l_tel_number;
3183 CLOSE get_phone_number;
3184 vXMLTable(vCtr).TagName := 'G3-B-01';
3185 vXMLTable(vCtr).TagValue := l_first_name;
3186 vCtr := vCtr + 1;
3187 vXMLTable(vCtr).TagName := 'G3-B-02';
3188 vXMLTable(vCtr).TagValue := l_father_name;
3189 vCtr := vCtr + 1;
3190 vXMLTable(vCtr).TagName := 'G3-B-03';
3191 vXMLTable(vCtr).TagValue := l_grandfather_name;
3192 vCtr := vCtr + 1;
3193 vXMLTable(vCtr).TagName := 'G3-B-04';
3194 vXMLTable(vCtr).TagValue := l_family_name;
3195 vCtr := vCtr + 1;
3196 vXMLTable(vCtr).TagName := 'G3-B-05-1';
3197 vXMLTable(vCtr).TagValue := substr(l_employee_gosi_number,1,1);
3198 vCtr := vCtr + 1;
3199 vXMLTable(vCtr).TagName := 'G3-B-05-2';
3200 vXMLTable(vCtr).TagValue := substr(l_employee_gosi_number,2,1);
3201 vCtr := vCtr + 1;
3202 vXMLTable(vCtr).TagName := 'G3-B-05-3';
3203 vXMLTable(vCtr).TagValue := substr(l_employee_gosi_number,3,1);
3204 vCtr := vCtr + 1;
3205 vXMLTable(vCtr).TagName := 'G3-B-05-4';
3206 vXMLTable(vCtr).TagValue := substr(l_employee_gosi_number,4,1);
3207 vCtr := vCtr + 1;
3208 vXMLTable(vCtr).TagName := 'G3-B-05-5';
3209 vXMLTable(vCtr).TagValue := substr(l_employee_gosi_number,5,1);
3210 vCtr := vCtr + 1;
3211 vXMLTable(vCtr).TagName := 'G3-B-05-6';
3212 vXMLTable(vCtr).TagValue := substr(l_employee_gosi_number,6,1);
3213 vCtr := vCtr + 1;
3214 vXMLTable(vCtr).TagName := 'G3-B-05-7';
3215 vXMLTable(vCtr).TagValue :=substr(l_employee_gosi_number,7,1);
3216 vCtr := vCtr + 1;
3217 vXMLTable(vCtr).TagName := 'G3-B-05-8';
3218 vXMLTable(vCtr).TagValue := substr(l_employee_gosi_number,8,1);
3219 vCtr := vCtr + 1;
3220 vXMLTable(vCtr).TagName := 'G3-B-05-9';
3221 vXMLTable(vCtr).TagValue := substr(l_employee_gosi_number,9,1);
3222 vCtr := vCtr + 1;
3223 vXMLTable(vCtr).TagName := 'G3-B-06';
3224 vXMLTable(vCtr).TagValue := l_city_mn;
3225 vCtr := vCtr + 1;
3226 vXMLTable(vCtr).TagName := 'G3-B-07';
3227 vXMLTable(vCtr).TagValue := l_area;
3228 vCtr := vCtr + 1;
3229 vXMLTable(vCtr).TagName := 'G3-B-08';
3230 vXMLTable(vCtr).TagValue := l_post_box;
3231 vCtr := vCtr + 1;
3232 vXMLTable(vCtr).TagName := 'G3-B-09';
3233 vXMLTable(vCtr).TagValue := l_tel_number;
3234 vCtr := vCtr + 1;
3235 vXMLTable(vCtr).TagName := 'G3-B-10';
3236 vXMLTable(vCtr).TagValue := l_mobile_number;
3237 vCtr := vCtr + 1;
3238 --get id card num, gender, marital status, dob
3239 open get_infos(t_emp(j).emp_id, l_effective_date);
3240 fetch get_infos into l_id_card_number, l_gender, l_marital_status, l_date_of_birth;
3241 close get_infos ;
3242 l_civ_id_num := to_number(replace(l_id_card_number,'-',''));
3243 If length(l_civ_id_num) > 10 then
3244 vXMLTable(vCtr).TagName := 'G3-C-01-0';
3245 -- vXMLTable(vCtr).TagValue := substr(l_civ_id_num,11,1);
3246 vXMLTable(vCtr).TagValue := ' ';
3247 vCtr := vCtr + 1;
3248 end if;
3249 vXMLTable(vCtr).TagName := 'G3-C-01-1';
3250 vXMLTable(vCtr).TagValue := substr(l_civ_id_num,1,1);
3251 vCtr := vCtr + 1;
3252 vXMLTable(vCtr).TagName := 'G3-C-01-2';
3253 vXMLTable(vCtr).TagValue := substr(l_civ_id_num,2,1);
3254 vCtr := vCtr + 1;
3255 vXMLTable(vCtr).TagName := 'G3-C-01-3';
3256 vXMLTable(vCtr).TagValue := substr(l_civ_id_num,3,1);
3257 vCtr := vCtr + 1;
3258 vXMLTable(vCtr).TagName := 'G3-C-01-4';
3259 vXMLTable(vCtr).TagValue := substr(l_civ_id_num,4,1);
3260 vCtr := vCtr + 1;
3261 vXMLTable(vCtr).TagName := 'G3-C-01-5';
3262 vXMLTable(vCtr).TagValue := substr(l_civ_id_num,5,1);
3263 vCtr := vCtr + 1;
3264 vXMLTable(vCtr).TagName := 'G3-C-01-6';
3265 vXMLTable(vCtr).TagValue := substr(l_civ_id_num,6,1);
3266 vCtr := vCtr + 1;
3267 vXMLTable(vCtr).TagName := 'G3-C-01-7';
3268 vXMLTable(vCtr).TagValue := substr(l_civ_id_num,7,1);
3269 vCtr := vCtr + 1;
3270 vXMLTable(vCtr).TagName := 'G3-C-01-8';
3271 vXMLTable(vCtr).TagValue := substr(l_civ_id_num,8,1);
3272 vCtr := vCtr + 1;
3273 vXMLTable(vCtr).TagName := 'G3-C-01-9';
3274 vXMLTable(vCtr).TagValue := substr(l_civ_id_num,9,1);
3275 vCtr := vCtr + 1;
3276 vXMLTable(vCtr).TagName := 'G3-C-01-10';
3277 vXMLTable(vCtr).TagValue := substr(l_civ_id_num,10,1);
3278 vCtr := vCtr + 1;
3279 if(l_gender = 'M') then
3280 l_gender_male := 'X';
3281 l_gender_female := NULL;
3282 elsif l_gender ='F' then
3283 l_gender_male := NULL;
3284 l_gender_female := 'X';
3285 end if;
3286 vXMLTable(vCtr).TagName := 'G3-C-02-2';
3287 vXMLTable(vCtr).TagValue := l_gender_female;
3288 vCtr := vCtr + 1;
3289 vXMLTable(vCtr).TagName := 'G3-C-02-1';
3290 vXMLTable(vCtr).TagValue := l_gender_male;
3291 vCtr := vCtr + 1;
3292 if(l_marital_status = 'M') then
3293 l_married := 'X';
3294 l_single := NULL;
3295 else
3296 l_married := NULL;
3297 l_single := 'X';
3298 end if;
3299 vXMLTable(vCtr).TagName := 'G3-C-03-2';
3300 vXMLTable(vCtr).TagValue := l_single;
3301 vCtr := vCtr + 1;
3302 vXMLTable(vCtr).TagName := 'G3-C-03-1';
3303 vXMLTable(vCtr).TagValue := l_married;
3304 vCtr := vCtr + 1;
3305 l_day := to_char(l_date_of_birth,'DD');
3306 l_month := to_char(l_date_of_birth,'MM');
3307 l_year := to_char(l_date_of_birth,'YYYY');
3308 vXMLTable(vCtr).TagName := 'G3-C-04-1';
3309 vXMLTable(vCtr).TagValue := substr(to_char(l_date_of_birth,'DD-MM-YYYY'),7,1);
3310 vCtr := vCtr + 1;
3311 vXMLTable(vCtr).TagName := 'G3-C-04-2';
3312 vXMLTable(vCtr).TagValue := substr(to_char(l_date_of_birth,'DD-MM-YYYY'),8,1);
3313 vCtr := vCtr + 1;
3314 vXMLTable(vCtr).TagName := 'G3-C-04-3';
3315 vXMLTable(vCtr).TagValue := substr(to_char(l_date_of_birth,'DD-MM-YYYY'),9,1);
3316 vCtr := vCtr + 1;
3317 vXMLTable(vCtr).TagName := 'G3-C-04-4';
3318 vXMLTable(vCtr).TagValue := substr(to_char(l_date_of_birth,'DD-MM-YYYY'),10,1);
3319 vCtr := vCtr + 1;
3320 vXMLTable(vCtr).TagName := 'G3-C-04-5';
3321 vXMLTable(vCtr).TagValue := substr(to_char(l_date_of_birth,'DD-MM-YYYY'),4,1);
3322 vCtr := vCtr + 1;
3323 vXMLTable(vCtr).TagName := 'G3-C-04-6';
3324 vXMLTable(vCtr).TagValue := substr(to_char(l_date_of_birth,'DD-MM-YYYY'),5,1);
3325 vCtr := vCtr + 1;
3326 vXMLTable(vCtr).TagName := 'G3-C-04-7';
3327 vXMLTable(vCtr).TagValue := substr(to_char(l_date_of_birth,'DD-MM-YYYY'),1,1);
3328 vCtr := vCtr + 1;
3329 vXMLTable(vCtr).TagName := 'G3-C-04-8';
3330 vXMLTable(vCtr).TagValue := substr(to_char(l_date_of_birth,'DD-MM-YYYY'),2,1);
3331 vCtr := vCtr + 1;
3332 vXMLTable(vCtr).TagName := 'G3-C-04-9';
3333 vXMLTable(vCtr).TagValue := 'G';
3334 vCtr := vCtr + 1;
3335 --get occupation name
3336 open get_job(t_emp(j).emp_id, l_effective_date);
3337 fetch get_job into l_occupation;
3338 close get_job;
3339 vXMLTable(vCtr).TagName := 'G3-C-05';
3340 vXMLTable(vCtr).TagValue := l_occupation;
3341 vCtr := vCtr + 1;
3342 --get joining date, termination date, termination reason
3343 open get_start_date(t_emp(j).emp_id, l_effective_date);
3344 fetch get_start_date into l_employee_joining_date,l_termination_date,l_termination_reason;
3345 close get_start_date;
3346 if t_form_type(m) <> 'TM' then
3347 l_termination_date := null;
3348 l_termination_reason := null;
3349 end if;
3350 -- get anuuities join date and hazards join date
3351 open get_hazards_date(t_emp(j).emp_id, l_effective_date);
3352 fetch get_hazards_date into l_annuities_join_date,l_hazards_join_date;
3353 close get_hazards_date;
3354 --get employee monthly contribution
3355 OPEN get_lower_base(l_effective_date);
3356 FETCH get_lower_base INTO l_lower_base;
3357 CLOSE get_lower_base;
3358 OPEN get_upper_base(l_effective_date);
3359 FETCH get_upper_base INTO l_upper_base;
3360 CLOSE get_upper_base;
3361 --get defined_balance_id
3362 select u.creator_id
3363 into l_defined_balance_id
3364 from ff_user_entities u,
3365 ff_database_items d
3366 where d.user_name = 'GOSI_REFERENCE_EARNINGS_ASG_YTD'
3367 and u.user_entity_id = d.user_entity_id
3368 and u.legislation_code = 'SA'
3369 and u.business_group_id is null
3370 and u.creator_type = 'B';
3371 --get assignment_action_id
3372 open get_assact_id(t_emp(j).emp_id, l_effective_date);
3373 fetch get_assact_id into l_assignment_action_id;
3374 close get_assact_id;
3375 --get monthly contribution
3376 if l_assignment_action_id is not null then
3377 l_employee_monthly_cont := pay_balance_pkg.get_value(l_defined_balance_id, l_assignment_action_id);
3378 IF (l_employee_monthly_cont > to_number(l_upper_base)) THEN
3379 l_employee_monthly_cont := to_number(l_upper_base);
3380 ELSIF ( l_employee_monthly_cont < to_number(l_lower_base)) THEN
3381 l_employee_monthly_cont := to_number(l_lower_base);
3382 END IF;
3383 else
3384 l_employee_monthly_cont := null;
3385 end if;
3386 l_day := to_char(l_employee_joining_date,'DD');
3387 l_month := to_char(l_employee_joining_date,'MM');
3388 l_year := to_char(l_employee_joining_date,'YYYY');
3389 vXMLTable(vCtr).TagName := 'G3-C-06-1';
3390 vXMLTable(vCtr).TagValue := substr(to_char(l_employee_joining_date,'DD-MM-YYYY'),7,1);
3391 vCtr := vCtr + 1;
3392 vXMLTable(vCtr).TagName := 'G3-C-06-2';
3393 vXMLTable(vCtr).TagValue := substr(to_char(l_employee_joining_date,'DD-MM-YYYY'),8,1);
3394 vCtr := vCtr + 1;
3395 vXMLTable(vCtr).TagName := 'G3-C-06-3';
3396 vXMLTable(vCtr).TagValue := substr(to_char(l_employee_joining_date,'DD-MM-YYYY'),9,1);
3397 vCtr := vCtr + 1;
3398 vXMLTable(vCtr).TagName := 'G3-C-06-4';
3399 vXMLTable(vCtr).TagValue := substr(to_char(l_employee_joining_date,'DD-MM-YYYY'),10,1);
3400 vCtr := vCtr + 1;
3401 vXMLTable(vCtr).TagName := 'G3-C-06-5';
3402 vXMLTable(vCtr).TagValue := substr(to_char(l_employee_joining_date,'DD-MM-YYYY'),4,1);
3403 vCtr := vCtr + 1;
3404 vXMLTable(vCtr).TagName := 'G3-C-06-6';
3405 vXMLTable(vCtr).TagValue := substr(to_char(l_employee_joining_date,'DD-MM-YYYY'),5,1);
3406 vCtr := vCtr + 1;
3407 vXMLTable(vCtr).TagName := 'G3-C-06-7';
3408 vXMLTable(vCtr).TagValue := substr(to_char(l_employee_joining_date,'DD-MM-YYYY'),1,1);
3409 vCtr := vCtr + 1;
3410 vXMLTable(vCtr).TagName := 'G3-C-06-8';
3411 vXMLTable(vCtr).TagValue := substr(to_char(l_employee_joining_date,'DD-MM-YYYY'),2,1);
3412 vCtr := vCtr + 1;
3413 l_day := to_char(fnd_date.canonical_to_date(l_annuities_join_date),'DD');
3414 l_month := to_char(fnd_date.canonical_to_date(l_annuities_join_date),'MM');
3415 l_year := to_char(fnd_date.canonical_to_date(l_annuities_join_date),'YYYY');
3416 /** Added as per the GOSI Report 3+4 enhancement */
3417 vXMLTable(vCtr).TagName := 'G3-N-08';
3418 vXMLTable(vCtr).TagValue := l_street;
3419 vCtr := vCtr + 1;
3420 vXMLTable(vCtr).TagName := 'G3-N-10';
3421 vXMLTable(vCtr).TagValue := l_zip_code;
3422 vCtr := vCtr + 1;
3423 open get_work_location(t_emp(j).emp_id);
3424 fetch get_work_location into l_work_location;
3425 close get_work_location;
3426 If l_work_location is null then
3427 BEGIN
3428 select distinct TOWN_OR_CITY
3429 into l_work_location
3430 from hr_locations hl,
3431 per_all_assignments_f paa
3432 where hl.location_id = paa.location_id
3433 And paa.assignment_id = t_emp(j).emp_id;
3434 exception
3435 WHEN OTHERS THEN NULL;
3436 end ;
3437 end if;
3438 vXMLTable(vCtr).TagName := 'G3-N-07';
3439 vXMLTable(vCtr).TagValue := l_work_location;
3440 vCtr := vCtr + 1;
3441 open get_nationality(l_person_id_new,l_effective_date);
3442 fetch get_nationality into l_nationality,l_employee_number,l_email_id;
3443 close get_nationality;
3444 l_nationality_mn := null;
3445 If l_nationality is not null then
3446 SELECT hl.meaning
3447 INTO l_nationality_mn
3448 FROM hr_lookups hl
3449 WHERE hl.lookup_type = 'NATIONALITY'
3450 and hl.lookup_code = l_nationality
3451 and hl.enabled_flag = 'Y';
3452 End If;
3453 vXMLTable(vCtr).TagName := 'G3-N-01';
3454 vXMLTable(vCtr).TagValue := l_nationality_mn;
3455 vCtr := vCtr + 1;
3456 vXMLTable(vCtr).TagName := 'G3-N-06-1';
3457 vXMLTable(vCtr).TagValue := substr(l_employee_number,1,1);
3458 vCtr := vCtr + 1;
3459 vXMLTable(vCtr).TagName := 'G3-N-06-2';
3460 vXMLTable(vCtr).TagValue := nvl(substr(l_employee_number,2,1),' ');
3461 vCtr := vCtr + 1;
3462 vXMLTable(vCtr).TagName := 'G3-N-06-3';
3463 vXMLTable(vCtr).TagValue := nvl(substr(l_employee_number,3,1),' ');
3464 vCtr := vCtr + 1;
3465 vXMLTable(vCtr).TagName := 'G3-N-06-4';
3466 vXMLTable(vCtr).TagValue := nvl(substr(l_employee_number,4,1),' ');
3467 vCtr := vCtr + 1;
3468 vXMLTable(vCtr).TagName := 'G3-N-06-5';
3469 vXMLTable(vCtr).TagValue := nvl(substr(l_employee_number,5,1),' ');
3470 vCtr := vCtr + 1;
3471 vXMLTable(vCtr).TagName := 'G3-N-06-6';
3472 vXMLTable(vCtr).TagValue := nvl(substr(l_employee_number,6,1),' ');
3473 vCtr := vCtr + 1;
3474 vXMLTable(vCtr).TagName := 'G3-N-06-7';
3475 vXMLTable(vCtr).TagValue := nvl(substr(l_employee_number,7,1),' ');
3476 vCtr := vCtr + 1;
3477 vXMLTable(vCtr).TagName := 'G3-N-06-8';
3478 vXMLTable(vCtr).TagValue := nvl(substr(l_employee_number,8,1),' ');
3479 vCtr := vCtr + 1;
3480 vXMLTable(vCtr).TagName := 'G3-N-06-9';
3481 vXMLTable(vCtr).TagValue := nvl(substr(l_employee_number,9,1),' ');
3482 vCtr := vCtr + 1;
3483 vXMLTable(vCtr).TagName := 'G3-N-09';
3484 vXMLTable(vCtr).TagValue := l_email_id;
3485 vCtr := vCtr + 1;
3486 IF upper(l_nationality) <> FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY') then
3487 open get_passport_number(l_person_id_new,l_effective_date);
3488 fetch get_passport_number into l_passport_number,l_passport_issue_date,l_passport_issue_place;
3489 close get_passport_number;
3490 vXMLTable(vCtr).TagName := 'G3-N-02-1';
3491 vXMLTable(vCtr).TagValue := nvl(substr(l_passport_number,1,1),' ');
3492 vCtr := vCtr + 1;
3493 vXMLTable(vCtr).TagName := 'G3-N-02-2';
3494 vXMLTable(vCtr).TagValue := nvl(substr(l_passport_number,2,1),' ');
3495 vCtr := vCtr + 1;
3496 vXMLTable(vCtr).TagName := 'G3-N-02-3';
3497 vXMLTable(vCtr).TagValue := nvl(substr(l_passport_number,3,1),' ');
3498 vCtr := vCtr + 1;
3499 vXMLTable(vCtr).TagName := 'G3-N-02-4';
3500 vXMLTable(vCtr).TagValue := nvl(substr(l_passport_number,4,1),' ');
3501 vCtr := vCtr + 1;
3502 vXMLTable(vCtr).TagName := 'G3-N-02-5';
3503 vXMLTable(vCtr).TagValue := nvl(substr(l_passport_number,5,1),' ');
3504 vCtr := vCtr + 1;
3505 vXMLTable(vCtr).TagName := 'G3-N-02-6';
3506 vXMLTable(vCtr).TagValue := nvl(substr(l_passport_number,6,1),' ');
3507 vCtr := vCtr + 1;
3508 vXMLTable(vCtr).TagName := 'G3-N-02-7';
3509 vXMLTable(vCtr).TagValue := nvl(substr(l_passport_number,7,1),' ');
3510 vCtr := vCtr + 1;
3511 vXMLTable(vCtr).TagName := 'G3-N-02-8';
3512 vXMLTable(vCtr).TagValue := nvl(substr(l_passport_number,8,1),' ');
3513 vCtr := vCtr + 1;
3514 vXMLTable(vCtr).TagName := 'G3-N-02-9';
3515 vXMLTable(vCtr).TagValue := nvl(substr(l_passport_number,9,1),' ');
3516 vCtr := vCtr + 1;
3517 vXMLTable(vCtr).TagName := 'G3-N-02-10';
3518 vXMLTable(vCtr).TagValue := nvl(substr(l_passport_number,10,1),' ');
3519 vCtr := vCtr + 1;
3520 vXMLTable(vCtr).TagName := 'G3-N-03-1';
3521 vXMLTable(vCtr).TagValue := substr(to_char(l_passport_issue_date,'DD-MM-YYYY'),7,1);
3522 vCtr := vCtr + 1;
3523 vXMLTable(vCtr).TagName := 'G3-N-03-2';
3524 vXMLTable(vCtr).TagValue := substr(to_char(l_passport_issue_date,'DD-MM-YYYY'),8,1);
3525 vCtr := vCtr + 1;
3526 vXMLTable(vCtr).TagName := 'G3-N-03-3';
3527 vXMLTable(vCtr).TagValue := substr(to_char(l_passport_issue_date,'DD-MM-YYYY'),9,1);
3528 vCtr := vCtr + 1;
3529 vXMLTable(vCtr).TagName := 'G3-N-03-4';
3530 vXMLTable(vCtr).TagValue := substr(to_char(l_passport_issue_date,'DD-MM-YYYY'),10,1);
3531 vCtr := vCtr + 1;
3532 vXMLTable(vCtr).TagName := 'G3-N-03-5';
3533 vXMLTable(vCtr).TagValue := substr(to_char(l_passport_issue_date,'DD-MM-YYYY'),4,1);
3534 vCtr := vCtr + 1;
3535 vXMLTable(vCtr).TagName := 'G3-N-03-6';
3536 vXMLTable(vCtr).TagValue := substr(to_char(l_passport_issue_date,'DD-MM-YYYY'),5,1);
3537 vCtr := vCtr + 1;
3538 vXMLTable(vCtr).TagName := 'G3-N-03-7';
3539 vXMLTable(vCtr).TagValue := substr(to_char(l_passport_issue_date,'DD-MM-YYYY'),1,1);
3540 vCtr := vCtr + 1;
3541 vXMLTable(vCtr).TagName := 'G3-N-03-8';
3542 vXMLTable(vCtr).TagValue := substr(to_char(l_passport_issue_date,'DD-MM-YYYY'),2,1);
3543 vCtr := vCtr + 1;
3544 vXMLTable(vCtr).TagName := 'G3-N-04';
3545 vXMLTable(vCtr).TagValue := l_passport_issue_place;
3546 vCtr := vCtr + 1;
3547 Else
3548 open get_hafiza_number(l_person_id_new);
3549 fetch get_hafiza_number into l_hafiza_number,l_hafiza_date,l_hafiza_place;
3550 close get_hafiza_number;
3551 vXMLTable(vCtr).TagName := 'G3-N-02-1';
3552 vXMLTable(vCtr).TagValue := nvl(substr(l_hafiza_number,1,1),' ');
3553 vCtr := vCtr + 1;
3554 vXMLTable(vCtr).TagName := 'G3-N-02-2';
3555 vXMLTable(vCtr).TagValue := nvl(substr(l_hafiza_number,2,1),' ');
3556 vCtr := vCtr + 1;
3557 vXMLTable(vCtr).TagName := 'G3-N-02-3';
3558 vXMLTable(vCtr).TagValue := nvl(substr(l_hafiza_number,3,1),' ');
3559 vCtr := vCtr + 1;
3560 vXMLTable(vCtr).TagName := 'G3-N-02-4';
3561 vXMLTable(vCtr).TagValue := nvl(substr(l_hafiza_number,4,1),' ');
3562 vCtr := vCtr + 1;
3563 vXMLTable(vCtr).TagName := 'G3-N-02-5';
3564 vXMLTable(vCtr).TagValue := nvl(substr(l_hafiza_number,5,1),' ');
3565 vCtr := vCtr + 1;
3566 vXMLTable(vCtr).TagName := 'G3-N-02-6';
3567 vXMLTable(vCtr).TagValue := nvl(substr(l_hafiza_number,6,1),' ');
3568 vCtr := vCtr + 1;
3569 vXMLTable(vCtr).TagName := 'G3-N-02-7';
3570 vXMLTable(vCtr).TagValue := nvl(substr(l_hafiza_number,7,1),' ');
3571 vCtr := vCtr + 1;
3572 vXMLTable(vCtr).TagName := 'G3-N-02-8';
3573 vXMLTable(vCtr).TagValue := nvl(substr(l_hafiza_number,8,1),' ');
3574 vCtr := vCtr + 1;
3575 vXMLTable(vCtr).TagName := 'G3-N-02-9';
3576 vXMLTable(vCtr).TagValue := nvl(substr(l_hafiza_number,9,1),' ');
3577 vCtr := vCtr + 1;
3578 vXMLTable(vCtr).TagName := 'G3-N-02-10';
3579 vXMLTable(vCtr).TagValue := nvl(substr(l_hafiza_number,10,1),' ');
3580 vCtr := vCtr + 1;
3581 vXMLTable(vCtr).TagName := 'G3-N-03-1';
3582 vXMLTable(vCtr).TagValue := substr(to_char(l_hafiza_date,'DD-MM-YYYY'),7,1);
3583 vCtr := vCtr + 1;
3584 vXMLTable(vCtr).TagName := 'G3-N-03-2';
3585 vXMLTable(vCtr).TagValue := substr(to_char(l_hafiza_date,'DD-MM-YYYY'),8,1);
3586 vCtr := vCtr + 1;
3587 vXMLTable(vCtr).TagName := 'G3-N-03-3';
3588 vXMLTable(vCtr).TagValue := substr(to_char(l_hafiza_date,'DD-MM-YYYY'),9,1);
3589 vCtr := vCtr + 1;
3590 vXMLTable(vCtr).TagName := 'G3-N-03-4';
3591 vXMLTable(vCtr).TagValue := substr(to_char(l_hafiza_date,'DD-MM-YYYY'),10,1);
3592 vCtr := vCtr + 1;
3593 vXMLTable(vCtr).TagName := 'G3-N-03-5';
3594 vXMLTable(vCtr).TagValue := substr(to_char(l_hafiza_date,'DD-MM-YYYY'),4,1);
3595 vCtr := vCtr + 1;
3596 vXMLTable(vCtr).TagName := 'G3-N-03-6';
3597 vXMLTable(vCtr).TagValue := substr(to_char(l_hafiza_date,'DD-MM-YYYY'),5,1);
3598 vCtr := vCtr + 1;
3599 vXMLTable(vCtr).TagName := 'G3-N-03-7';
3600 vXMLTable(vCtr).TagValue := substr(to_char(l_hafiza_date,'DD-MM-YYYY'),1,1);
3601 vCtr := vCtr + 1;
3602 vXMLTable(vCtr).TagName := 'G3-N-03-8';
3603 vXMLTable(vCtr).TagValue := substr(to_char(l_hafiza_date,'DD-MM-YYYY'),2,1);
3604 vCtr := vCtr + 1;
3605 vXMLTable(vCtr).TagName := 'G3-N-04';
3606 vXMLTable(vCtr).TagValue := substr(l_hafiza_place,1,60);
3607 vCtr := vCtr + 1;
3608 END If;
3609 if l_qualification_type is not null then
3610 if l_qualification_type = '1' then
3611 vXMLTable(vCtr).TagName := 'G3-N-05-1';
3612 vXMLTable(vCtr).TagValue := 'X';
3613 vCtr := vCtr + 1;
3614 elsif l_qualification_type = '2' then
3615 vXMLTable(vCtr).TagName := 'G3-N-05-2';
3616 vXMLTable(vCtr).TagValue := 'X';
3617 vCtr := vCtr + 1;
3618 elsif l_qualification_type = '3' then
3619 vXMLTable(vCtr).TagName := 'G3-N-05-3';
3620 vXMLTable(vCtr).TagValue := 'X';
3621 vCtr := vCtr + 1;
3622 elsif l_qualification_type = '4' then
3623 vXMLTable(vCtr).TagName := 'G3-N-05-4';
3624 vXMLTable(vCtr).TagValue := 'X';
3625 vCtr := vCtr + 1;
3626 elsif l_qualification_type = '5' then
3627 vXMLTable(vCtr).TagName := 'G3-N-05-5';
3628 vXMLTable(vCtr).TagValue := 'X';
3629 vCtr := vCtr + 1;
3630 elsif l_qualification_type = '6' then
3631 vXMLTable(vCtr).TagName := 'G3-N-05-6';
3632 vXMLTable(vCtr).TagValue := 'X';
3633 vCtr := vCtr + 1;
3634 elsif l_qualification_type = '7' then
3635 vXMLTable(vCtr).TagName := 'G3-N-05-7';
3636 vXMLTable(vCtr).TagValue := 'X';
3637 vCtr := vCtr + 1;
3638 end if;
3639 end if;
3640 /* Added as per the GOSI Report 3+4 enhancement **/
3641 /* Cancelled as per the GOSI Report 3+4 enhancement.
3642 vXMLTable(vCtr).TagName := 'G3-C-07-1';
3643 vXMLTable(vCtr).TagValue := l_year;
3644 vCtr := vCtr + 1;
3645 vXMLTable(vCtr).TagName := 'G3-C-07-2';
3646 vXMLTable(vCtr).TagValue := l_month;
3647 vCtr := vCtr + 1;
3648 vXMLTable(vCtr).TagName := 'G3-C-07-3';
3649 vXMLTable(vCtr).TagValue := l_day;
3650 vCtr := vCtr + 1;
3651 */
3652 l_day := to_char(fnd_date.canonical_to_date(l_hazards_join_date),'DD');
3653 l_month := to_char(fnd_date.canonical_to_date(l_hazards_join_date),'MM');
3654 l_year := to_char(fnd_date.canonical_to_date(l_hazards_join_date),'YYYY');
3655 /* Cancelled as per the GOSI Report 3+4 enhancement.
3656 vXMLTable(vCtr).TagName := 'G3-C-08-1';
3657 vXMLTable(vCtr).TagValue := l_year;
3658 vCtr := vCtr + 1;
3659 vXMLTable(vCtr).TagName := 'G3-C-08-2';
3660 vXMLTable(vCtr).TagValue := l_month;
3661 vCtr := vCtr + 1;
3662 vXMLTable(vCtr).TagName := 'G3-C-08-3';
3663 vXMLTable(vCtr).TagValue := l_day;
3664 vCtr := vCtr + 1;
3665 */
3666 l_fm_employee_monthly_cont := null;
3667 l_fm_employee_monthly_cont := to_char(l_employee_monthly_cont,lg_format_mask);
3668 vXMLTable(vCtr).TagName := 'G3-C-09-6';
3669 vXMLTable(vCtr).TagValue := substr(l_fm_employee_monthly_cont,length(l_fm_employee_monthly_cont)-1,1);
3670 vCtr := vCtr + 1;
3671 vXMLTable(vCtr).TagName := 'G3-C-09-7';
3672 vXMLTable(vCtr).TagValue := substr(l_fm_employee_monthly_cont,length(l_fm_employee_monthly_cont),1);
3673 vCtr := vCtr + 1;
3674 If length(trunc(l_employee_monthly_cont)) = 3 then
3675 vXMLTable(vCtr).TagName := 'G3-C-09-3';
3676 vXMLTable(vCtr).TagValue := substr(l_employee_monthly_cont,1,1);
3677 vCtr := vCtr + 1;
3678 vXMLTable(vCtr).TagName := 'G3-C-09-4';
3679 vXMLTable(vCtr).TagValue := substr(l_employee_monthly_cont,2,1);
3680 vCtr := vCtr + 1;
3681 vXMLTable(vCtr).TagName := 'G3-C-09-5';
3682 vXMLTable(vCtr).TagValue := substr(l_employee_monthly_cont,3,1);
3683 vCtr := vCtr + 1;
3684 End If;
3685 If length(trunc(l_employee_monthly_cont)) = 4 then
3686 vXMLTable(vCtr).TagName := 'G3-C-09-5';
3687 vXMLTable(vCtr).TagValue := substr(l_employee_monthly_cont,4,1);
3688 vCtr := vCtr + 1;
3689 vXMLTable(vCtr).TagName := 'G3-C-09-4';
3690 vXMLTable(vCtr).TagValue := substr(l_employee_monthly_cont,3,1);
3691 vCtr := vCtr + 1;
3692 vXMLTable(vCtr).TagName := 'G3-C-09-3';
3693 vXMLTable(vCtr).TagValue := substr(l_employee_monthly_cont,2,1);
3694 vCtr := vCtr + 1;
3695 vXMLTable(vCtr).TagName := 'G3-C-09-2';
3696 vXMLTable(vCtr).TagValue := substr(l_employee_monthly_cont,1,1);
3697 vCtr := vCtr + 1;
3698 end if;
3699 If length(trunc(l_employee_monthly_cont)) = 5 then
3700 vXMLTable(vCtr).TagName := 'G3-C-09-5';
3701 vXMLTable(vCtr).TagValue := substr(l_employee_monthly_cont,5,1);
3702 vCtr := vCtr + 1;
3703 vXMLTable(vCtr).TagName := 'G3-C-09-4';
3704 vXMLTable(vCtr).TagValue := substr(l_employee_monthly_cont,4,1);
3705 vCtr := vCtr + 1;
3706 vXMLTable(vCtr).TagName := 'G3-C-09-3';
3707 vXMLTable(vCtr).TagValue := substr(l_employee_monthly_cont,3,1);
3708 vCtr := vCtr + 1;
3709 vXMLTable(vCtr).TagName := 'G3-C-09-2';
3710 vXMLTable(vCtr).TagValue := substr(l_employee_monthly_cont,2,1);
3711 vCtr := vCtr + 1;
3712 vXMLTable(vCtr).TagName := 'G3-C-09-1';
3713 vXMLTable(vCtr).TagValue := substr(l_employee_monthly_cont,1,1);
3714 vCtr := vCtr + 1;
3715 end if;
3716 l_day := to_char(l_termination_date,'DD');
3717 l_month := to_char(l_termination_date,'MM');
3718 l_year := to_char(l_termination_date,'YYYY');
3719 vXMLTable(vCtr).TagName := 'G3-D-01-1';
3720 vXMLTable(vCtr).TagValue := substr(to_char(l_termination_date,'DD-MM-YYYY'),7,1);
3721 vCtr := vCtr + 1;
3722 vXMLTable(vCtr).TagName := 'G3-D-01-2';
3723 vXMLTable(vCtr).TagValue := substr(to_char(l_termination_date,'DD-MM-YYYY'),8,1);
3724 vCtr := vCtr + 1;
3725 vXMLTable(vCtr).TagName := 'G3-D-01-3';
3726 vXMLTable(vCtr).TagValue := substr(to_char(l_termination_date,'DD-MM-YYYY'),9,1);
3727 vCtr := vCtr + 1;
3728 vXMLTable(vCtr).TagName := 'G3-D-01-4';
3729 vXMLTable(vCtr).TagValue := substr(to_char(l_termination_date,'DD-MM-YYYY'),10,1);
3730 vCtr := vCtr + 1;
3731 vXMLTable(vCtr).TagName := 'G3-D-01-5';
3732 vXMLTable(vCtr).TagValue := substr(to_char(l_termination_date,'DD-MM-YYYY'),4,1);
3733 vCtr := vCtr + 1;
3734 vXMLTable(vCtr).TagName := 'G3-D-01-6';
3735 vXMLTable(vCtr).TagValue := substr(to_char(l_termination_date,'DD-MM-YYYY'),5,1);
3736 vCtr := vCtr + 1;
3737 vXMLTable(vCtr).TagName := 'G3-D-01-7';
3738 vXMLTable(vCtr).TagValue := substr(to_char(l_termination_date,'DD-MM-YYYY'),1,1);
3739 vCtr := vCtr + 1;
3740 vXMLTable(vCtr).TagName := 'G3-D-01-8';
3741 vXMLTable(vCtr).TagValue := substr(to_char(l_termination_date,'DD-MM-YYYY'),2,1);
3742 vCtr := vCtr + 1;
3743 vXMLTable(vCtr).TagName := 'G3-D-02';
3744 vXMLTable(vCtr).TagValue := hr_general.decode_lookup('LEAV_REAS',l_termination_reason);
3745 vCtr := vCtr + 1;
3746 vXMLTable(vCtr).TagName := 'dummy';
3747 vXMLTable(vCtr).TagValue := ' ';
3748 vCtr := vCtr + 1;
3749 /*Write the values to xml file*/
3750 end loop; --t_emp
3751 end if;
3752 end loop; --(t_form_type);
3753 WritetoCLOB ( l_xfdf_blob );
3754 hr_utility.set_location('Finished Procedure Workers movement Report ',90);
3755 /*Write the values to xml file*/
3756 /*
3757 EXCEPTION
3758 WHEN utl_file.invalid_path then
3759 hr_utility.set_message(8301, 'GHR_38830_INVALID_UTL_FILE_PATH');
3760 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
3761 hr_utility.raise_error;
3762 --
3763 WHEN utl_file.invalid_mode then
3764 hr_utility.set_message(8301, 'GHR_38831_INVALID_FILE_MODE');
3765 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
3766 hr_utility.raise_error;
3767 --
3768 WHEN utl_file.invalid_filehandle then
3769 hr_utility.set_message(8301, 'GHR_38832_INVALID_FILE_HANDLE');
3770 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
3771 hr_utility.raise_error;
3772 --
3773 WHEN utl_file.invalid_operation then
3774 hr_utility.set_message(8301, 'GHR_38833_INVALID_OPER');
3775 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
3776 hr_utility.raise_error;
3777 --
3778 WHEN utl_file.read_error then
3779 hr_utility.set_message(8301, 'GHR_38834_FILE_READ_ERROR');
3780 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
3781 hr_utility.raise_error;
3782 --
3783 WHEN others THEN
3784 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
3785 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
3786 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
3787 hr_utility.raise_error;
3788 */
3789 end populate_workers_movement;
3790 /*End of SA Worker Movement Report*/
3791 ----------------------------------------------
3792 PROCEDURE WritetoCLOB (
3793 p_xfdf_blob out nocopy blob)
3794 IS
3795 l_xfdf_string clob;
3796 l_str1 varchar2(1000);
3797 l_str2 varchar2(20);
3798 l_str3 varchar2(20);
3799 l_str4 varchar2(20);
3800 l_str5 varchar2(20);
3801 l_str6 varchar2(30);
3802 l_str7 varchar2(1000);
3803 l_str8 varchar2(240);
3804 l_str9 varchar2(240);
3805 begin
3806 hr_utility.set_location('Entered Procedure Write to clob ',100);
3807 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
3808 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
3809 <fields> ' ;
3810 l_str2 := '<field name="';
3811 l_str3 := '">';
3812 l_str4 := '<value>' ;
3813 l_str5 := '</value> </field>' ;
3814 l_str6 := '</fields> </xfdf>';
3815 l_str7 := '<?xml version="1.0" encoding="UTF-8"?>
3816 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
3817 <fields>
3818 </fields> </xfdf>';
3819 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
3820 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
3821 if vXMLTable.count > 0 then
3822 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
3823 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
3824 l_str8 := vXMLTable(ctr_table).TagName;
3825 l_str9 := vXMLTable(ctr_table).TagValue;
3826 if (l_str9 is not null) then
3827 /* Added CDATA to handle special characters Bug No:8741752 */
3828 l_str9 := '<![CDATA['||l_str9||']]>';
3829 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
3830 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
3831 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
3832 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
3833 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
3834 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
3835 elsif (l_str9 is null and l_str8 is not null) then
3836 dbms_lob.writeAppend(l_xfdf_string,length(l_str2),l_str2);
3837 dbms_lob.writeAppend(l_xfdf_string,length(l_str8),l_str8);
3838 dbms_lob.writeAppend(l_xfdf_string,length(l_str3),l_str3);
3839 dbms_lob.writeAppend(l_xfdf_string,length(l_str4),l_str4);
3840 dbms_lob.writeAppend(l_xfdf_string,length(l_str5),l_str5);
3841 else
3842 null;
3843 end if;
3844 END LOOP;
3845 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
3846 else
3847 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
3848 end if;
3849 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
3850 clob_to_blob(l_xfdf_string,p_xfdf_blob);
3851 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
3852 --return p_xfdf_blob;
3853 EXCEPTION
3854 WHEN OTHERS then
3855 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
3856 HR_UTILITY.RAISE_ERROR;
3857 END WritetoCLOB;
3858 ----------------------------------------------------------------
3859 Procedure clob_to_blob(p_clob clob,
3860 p_blob IN OUT NOCOPY Blob)
3861 is
3862 l_length_clob number;
3863 l_offset pls_integer;
3864 l_varchar_buffer varchar2(32767);
3865 l_raw_buffer raw(32767);
3866 l_buffer_len number:= 20000;
3867 l_chunk_len number;
3868 l_blob blob;
3869 g_nls_db_char varchar2(60);
3870 l_raw_buffer_len pls_integer;
3871 l_blob_offset pls_integer := 1;
3872 begin
3873 hr_utility.set_location('Entered Procedure clob to blob',120);
3874 select userenv('LANGUAGE') into g_nls_db_char from dual;
3875 l_length_clob := dbms_lob.getlength(p_clob);
3876 l_offset := 1;
3877 while l_length_clob > 0 loop
3878 hr_utility.trace('l_length_clob '|| l_length_clob);
3879 if l_length_clob < l_buffer_len then
3880 l_chunk_len := l_length_clob;
3881 else
3882 l_chunk_len := l_buffer_len;
3883 end if;
3884 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
3885 --l_raw_buffer := utl_raw.cast_to_raw(l_varchar_buffer);
3886 l_raw_buffer := utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',g_nls_db_char);
3887 l_raw_buffer_len := utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',g_nls_db_char));
3888 hr_utility.trace('l_varchar_buffer '|| l_varchar_buffer);
3889 --dbms_lob.write(p_blob,l_chunk_len, l_offset, l_raw_buffer);
3890 dbms_lob.write(p_blob,l_raw_buffer_len, l_blob_offset, l_raw_buffer);
3891 l_blob_offset := l_blob_offset + l_raw_buffer_len;
3892 l_offset := l_offset + l_chunk_len;
3893 l_length_clob := l_length_clob - l_chunk_len;
3894 hr_utility.trace('l_length_blob '|| dbms_lob.getlength(p_blob));
3895 end loop;
3896 hr_utility.set_location('Finished Procedure clob to blob ',130);
3897 end;
3898 ------------------------------------------------------------------
3899 Procedure fetch_pdf_blob
3900 (p_report in varchar2,
3901 p_pdf_blob OUT NOCOPY blob)
3902 IS
3903 BEGIN
3904 IF (p_report='Form 3') THEN
3905 Select file_data
3906 Into p_pdf_blob
3907 From fnd_lobs
3908 Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_G32003_ar_SA.pdf');
3909 ELSIF (p_report = 'Form 4') THEN
3910 Select file_data
3911 Into p_pdf_blob
3912 From fnd_lobs
3913 Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_G42003_ar_SA.pdf');
3914 ELSIF (p_report ='Form 5') THEN
3915 Select file_data
3916 Into p_pdf_blob
3917 From fnd_lobs
3918 Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_G52003_ar_SA.pdf');
3919 END IF;
3920 EXCEPTION
3921 when no_data_found then
3922 null;
3923 END fetch_pdf_blob;
3924 -----------------------------------------------------------------
3925 END PAY_SA_GOSI_REPORTS;