[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_P60XML
Source
1 PACKAGE BODY PAY_IE_P60XML as
2 /* $Header: pyiep60p.pkb 120.15.12010000.3 2008/08/06 07:25:05 ubhat ship $ */
3 vCtr NUMBER;
4
5 /*6876894*/
6 TYPE t_asg_set_amnds IS TABLE OF hr_assignment_set_amendments.include_or_exclude%TYPE
7 INDEX BY BINARY_INTEGER;
8 l_tab_asg_set_amnds t_asg_set_amnds;
9
10
11 --
12 /*6876894*/
13 /*
14 This procedure returns formulae id for the assignment set criteria and
15 if its assignment set ammendents, then it retrives and stores those assignments
16 in p_tab_asg_set_amnds
17 */
18 PROCEDURE get_asg_set_details(
19 p_assignment_set_id IN NUMBER
20 ,p_formula_id OUT NOCOPY NUMBER
21 ,p_tab_asg_set_amnds OUT NOCOPY t_asg_set_amnds
22 )
23 IS
24 --
25 -- Cursor to get information about assignment set
26 CURSOR csr_get_asg_set_info(c_asg_set_id NUMBER)
27 IS
28 SELECT formula_id
29 FROM hr_assignment_sets ags
30 WHERE assignment_set_id = c_asg_set_id
31 AND EXISTS(SELECT 1
32 FROM hr_assignment_set_criteria agsc
33 WHERE agsc.assignment_set_id = ags.assignment_set_id);
34 -- Cursor to get assignment ids from asg set amendments
35 CURSOR csr_get_asg_amnd(c_asg_set_id NUMBER)
36 IS
37 SELECT assignment_id, NVL(include_or_exclude
38 ,'I') include_or_exclude
39 FROM hr_assignment_set_amendments
40 WHERE assignment_set_id = c_asg_set_id;
41 l_proc_step NUMBER(38, 10) := 0;
42 l_asg_set_amnds csr_get_asg_amnd%ROWTYPE;
43 l_tab_asg_set_amnds t_asg_set_amnds;
44 l_formula_id NUMBER;
45
46 --
47 BEGIN
48 --
49 fnd_file.put_line(fnd_file.LOG,'Entering get_asg_set_details');
50 -- Check whether the assignment set id has a criteria
51 -- if a formula id is attached or check whether this
52 -- is an amendments only
53 l_formula_id := NULL;
54 OPEN csr_get_asg_set_info(p_assignment_set_id);
55 FETCH csr_get_asg_set_info INTO l_formula_id;
56 fnd_file.put_line(fnd_file.LOG,' after csr_get_asg_set_info ');
57 fnd_file.put_line(fnd_file.LOG,' l_formula_id '|| l_formula_id);
58 IF csr_get_asg_set_info%FOUND
59 THEN
60 -- Criteria exists check for formula id
61 IF l_formula_id IS NULL
62 THEN
63 -- Raise error as the criteria is not generated
64 CLOSE csr_get_asg_set_info;
65 hr_utility.raise_error;
66 END IF; -- End if of formula id is null check ...
67 END IF; -- End if of asg criteria row found check ...
68 CLOSE csr_get_asg_set_info;
69 fnd_file.put_line(fnd_file.LOG,' before csr_get_asg_amd ');
70 OPEN csr_get_asg_amnd(p_assignment_set_id);
71 LOOP
72 FETCH csr_get_asg_amnd INTO l_asg_set_amnds;
73 EXIT WHEN csr_get_asg_amnd%NOTFOUND;
74 l_tab_asg_set_amnds(l_asg_set_amnds.assignment_id) :=
75 l_asg_set_amnds.include_or_exclude;
76 fnd_file.put_line(fnd_file.LOG,' l_asg_set_amnds.assignment_id '|| l_asg_set_amnds.assignment_id);
77 fnd_file.put_line(fnd_file.LOG,' l_asg_set_amnds.include_or_exclude '|| l_asg_set_amnds.include_or_exclude);
78 END LOOP;
79 CLOSE csr_get_asg_amnd;
80 p_formula_id := l_formula_id;
81 p_tab_asg_set_amnds := l_tab_asg_set_amnds;
82 EXCEPTION
83 WHEN OTHERS
84 THEN
85 fnd_file.put_line(fnd_file.LOG,'..'||'SQL-ERRM :'||SQLERRM);
86 END get_asg_set_details;
87
88
89
90 /*6876894*/
91 /*
92 firstly it checks whether the assignment is present in assinment set ammendments else
93 it executes the formulae if its not null for a particular assignment , returns whether
94 included or not.
95
96 */
97
98 FUNCTION chk_is_asg_in_asg_set(
99 p_assignment_id IN NUMBER
100 ,p_formula_id IN NUMBER
101 ,p_tab_asg_set_amnds IN t_asg_set_amnds
102 ,p_effective_date IN DATE
103 )
104 RETURN VARCHAR2
105 IS
106 l_session_date DATE;
107 l_include_flag VARCHAR2(10);
108 l_tab_asg_set_amnds t_asg_set_amnds;
109 l_inputs ff_exec.inputs_t;
110 l_outputs ff_exec.outputs_t;
111 --
112 BEGIN
113 --
114 fnd_file.put_line(fnd_file.LOG,'Entering chk_is_asg_in_asg_set');
115 l_include_flag := 'N';
116 l_tab_asg_set_amnds := p_tab_asg_set_amnds;
117 -- Check whether the assignment exists in the collection
118 -- first as the static assignment set overrides the
119 -- criteria one
120 IF l_tab_asg_set_amnds.EXISTS(p_assignment_id)
121 THEN
122 fnd_file.put_line(fnd_file.LOG,'Entered assignment ammendents if block');
123 -- Check whether to include or exclude
124 IF l_tab_asg_set_amnds(p_assignment_id) = 'I'
125 THEN
126 l_include_flag := 'Y';
127 ELSIF l_tab_asg_set_amnds(p_assignment_id) = 'E'
128 THEN
129 l_include_flag := 'N';
130 END IF; -- End if of include or exclude flag check ...
131 ELSIF p_formula_id IS NOT NULL
132 THEN
133 -- assignment does not exist in assignment set amendments
134 -- check whether a formula criteria exists for this
135 -- assignment set
136 -- Initialize the formula
137 fnd_file.put_line(fnd_file.LOG,'Entered assignment criteria block');
138 ff_exec.init_formula(p_formula_id => p_formula_id
139 ,p_effective_date => p_effective_date
140 ,p_inputs => l_inputs
141 ,p_outputs => l_outputs
142 );
143 fnd_file.put_line(fnd_file.LOG,'formula initialized');
144 -- Set the inputs first
145 -- Loop through them to set the contexts
146 FOR i IN l_inputs.FIRST .. l_inputs.LAST
147 LOOP
148 IF l_inputs(i).NAME = 'ASSIGNMENT_ID'
149 THEN
150 l_inputs(i).VALUE := p_assignment_id;
151 ELSIF l_inputs(i).NAME = 'DATE_EARNED'
152 THEN
153 l_inputs(i).VALUE := fnd_date.date_to_canonical(p_effective_date);
154 END IF;
155 END LOOP;
156 -- Run the formula
157 fnd_file.put_line(fnd_file.LOG,' before formaula run');
158
159
160 ff_exec.run_formula(l_inputs, l_outputs);
161
162
163 fnd_file.put_line(fnd_file.LOG,' aftre formaula run');
164 -- Check whether the assignment has to be included
165 -- by checking the output flag
166
167
168 fnd_file.put_line(fnd_file.LOG,' before outputs for run');
169 FOR i IN l_outputs.FIRST .. l_outputs.LAST
170 LOOP
171 IF l_outputs(i).NAME = 'INCLUDE_FLAG'
172 THEN
173 IF l_outputs(i).VALUE = 'Y'
174 THEN
175 l_include_flag := 'Y';
176 ELSIF l_outputs(i).VALUE = 'N'
177 THEN
178 l_include_flag := 'N';
179 END IF;
180 fnd_file.put_line(fnd_file.LOG,'p_assignment_id'||p_assignment_id);
181 fnd_file.put_line(fnd_file.LOG,'l_include_flag'||l_include_flag);
182 EXIT;
183 END IF;
184
185 END LOOP;
186 END IF; -- End if of assignment exists in amendments check ...
187
188 RETURN l_include_flag;
189 EXCEPTION
190 WHEN OTHERS
191 THEN
192 fnd_file.put_line(fnd_file.LOG,'..'||'SQL-ERRM :'||SQLERRM);
193 END chk_is_asg_in_asg_set;
194
195
196
197
198 procedure get_p60_details(p_53_indicator in varchar2,
199 cp_start_date in date,
200 cp_effective_date in date,
201 cp_end_date in date,
202 p_business_group_id in number,
203 p_assignment_set_id in number,
204 p_payroll_id in number,
205 p_consolidation_set_id in number,
206 p_sort_order in varchar2)
207 is
208 cursor c_p60_records(p_53_indicator varchar2,
209 cp_start_date date,
210 cp_effective_date date,
211 cp_end_date date,
212 p_business_group_id number,
213 p_assignment_set_id number,
214 p_payroll_id number,
215 p_consolidation_set_id number,
216 p_sort_order varchar2) IS
217 select
218 SUBSTR(trim(pai.action_information18||','|| pai.action_information19),1,30) Q1_Employee
219 ,substr(trim(pai.action_information21),1,30) Q1_Address1
220 ,substr(trim(pai.action_information22),1,30) Q1_Address2
221 ,rpad(substr(trim(pai.action_information23),1,30) ,30,' ') Q1_County
222 , to_char(cp_end_date,'YYYY') Q1_YEAR /*bug 3595646*/
223 ,nvl(pai.action_information1,' ') Q1_PPSN
224 ,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.WEEKLY_TAX_CREDIT*52),2),
225 decode(instr(papf.period_type,'Week'),0,round((payef.MONTHLY_TAX_CREDIT*12),2),round((payef.WEEKLY_TAX_CREDIT*52),2))),0),'999990.00') Q1_Tax_credits
226 ,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.WEEKLY_STD_RATE_CUT_OFF*52),2),
227 decode(instr(papf.period_type,'Week'),0,round((payef.MONTHLY_STD_RATE_CUT_OFF*12),2),round((payef.WEEKLY_STD_RATE_CUT_OFF*52),2))),0),'999990.00') Q1_Std_Rate
228 ,decode(payef.tax_basis,'IE_WEEK1_MONTH1','W', 'IE_EXEMPT_WEEK_MONTH', 'W') Q1_WM_Indicator
229 ,decode(payef.TAX_BASIS,
230 'IE_WEEK1_MONTH1' , '1' ,
231 'IE_EXEMPT_WEEK_MONTH' , '1',
232 'IE_EMERGENCY','2',
233 decode(payef.INFO_SOURCE,'IE_P45','1')) Q1_TB_Indicator /* 6982274 */
234 -- ,decode(payef.TAX_BASIS,'IE_EMERGENCY','2',decode(payef.INFO_SOURCE,'IE_P45','1')) Q1_TB_Indicator
235 ,decode(p_53_indicator,'Y','X') Q1_53_Indicator
236 ,decode(prsif.director_flag,'Y','D') Q1_Director_Indicator
237 /*4130512 Total Pay must be sum of. ,nvl(round(to_number(trim(pai.action_information16)),2),0) Q1_Total_Pay*/
238 -- changes made for bug 5435931
239 ,to_char(nvl(round(to_number(substr(trim(pai.action_information28),1,instr(pai.action_information28,'|',1,1)-1)),2),0) +
240 nvl(round(to_number(substr(trim(pai.action_information28),instr(pai.action_information28,'|',1,1)+1)),2),0),'999990.00') Q1_Total_Pay
241 -- bug 5435931
242 ,to_char(nvl(round(to_number(substr(trim(pai.action_information28),1,instr(pai.action_information28,'|',1,1)-1)),2),0),'999990.00') Q1_Previous_Emp_Pay
243 -- bug 5435931
244 ,to_char(nvl(round(to_number(substr(trim(pai.action_information28),instr(pai.action_information28,'|',1,1)+1)),2),0),'999990.00') Q1_Present_pay
245 -- bug 5435931
246 ,to_char(nvl(round(to_number(substr(trim(pai.action_information29),1,instr(pai.action_information29,'|',1,1)-1)),2),0) +
247 nvl(round(to_number(substr(trim(pai.action_information29),instr(pai.action_information29,'|',1,1)+1)),2),0),'999990.00') Q1_Total_Tax
248 -- bug 5435931
249 ,to_char(nvl(round(to_number(substr(trim(pai.action_information29),1,instr(pai.action_information29,'|',1,1)-1)),2),0),'999990.00') Q1_Previous_Emp_Tax
250 ,pai.action_information30 Q1_PR_Indicator
251 -- bug 5435931
252 ,to_char(nvl(round(to_number(substr(trim(pai.action_information29),instr(pai.action_information29,'|',1,1)+1)),2),0),'999990.00') Q1_Present_tax
253 -- Modified for bug 5657992
254 ,to_char(nvl(round(to_number(trim(pai_prsi.action_information11)),2),0),'999990.00') Q1_EmployeePRSI
255 ,to_char(nvl(round(to_number(nvl(trim(pai_prsi.action_information12),0)),2),0),'999990.00') Q1_TotalPRSI
256 ,to_number(trim(pai_prsi.action_information13)) Q1_Total_Weeks_Insurable
257 ,pai_prsi.action_information14 Q1_Initial_Contribution_Class
258 ,rpad(pai_prsi.action_information15,2) Q1_Sub_Contribution_Class
259 ,nvl(to_number(trim(pai_prsi.action_information16)),0) Q1_Weeks_In_Later_CC
260 -- end bug 5657992
261 ,decode(sign(to_date(pai.action_information24,'DD-MM-YYYY')- cp_start_date),-1,Null,to_char(to_date(pai.action_information24,'DD-MM-YYYY'),'DD-MON-YYYY')) Q1_Date_Of_Hire
262 ,nvl(rtrim(pact_ade.action_information26),'') Q1_Employer
263 ,nvl(rtrim(pact_ade.action_information1),'') Q1_Employer_RegNo
264 --Bug No: 6474486 : Employer contact no. is added
265 ,nvl(rtrim(pact_ade.action_information28),'') Q1_Employer_PhoneNo
266 ,to_char(cp_effective_date,'DD-MON-RR') Q1_Report_date /* bug 3595646*/
267 ,paf.assignment_number Q1_Assignment_Number
268 ,paf.person_id Q1_Person_Id
269 ,paf.assignment_id assignment_id /*6876894*/
270 FROM pay_action_information pai /*Employee Details Info*/
271 ,pay_action_information pai_prsi /* prsi Details 5657992 */
272 ,pay_action_information pact_ade /*Address Details - for Employer Name -IE Employer Tax Address*/
273 ,pay_payroll_actions ppa35
274 ,pay_assignment_actions paa
275 ,per_assignments_f paf
276 ,per_periods_of_service pps
277 ,pay_ie_paye_details_f payef
278 ,pay_ie_prsi_details_f prsif
279 ,pay_all_payrolls_f PAPF
280 WHERE
281 NVl('N','N') = 'N'
282 and to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD') between cp_start_date and cp_end_date
283 -- and cp_start_date <= to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD') /*4641756*/
284 and ppa35.report_type = 'IEP35'
285 and ppa35.business_group_id = p_business_group_id /* p_business_group_id */
286 and paa.payroll_action_id = ppa35.payroll_action_id
287 and paa.assignment_id = paf.assignment_id
288 and paa.action_status = 'C'
289 and paa.assignment_action_id = pai.action_context_id
290 and paf.period_of_service_id = pps.period_of_service_id
291 and paf.person_id= pps.person_id
292 and paf.business_group_id + 0 = p_business_group_id /*4483028*/
293 -- Bug 3446744 Checking if the employee has been terminated before issuing the P60
294 and (pps.actual_termination_date is null or pps.actual_termination_date > cp_end_date)
295 and paf.effective_start_date = (select max(asg2.effective_start_date)
296 from per_all_assignments_f asg2
297 where asg2.assignment_id = paf.assignment_id
298 and asg2.effective_start_date <= cp_end_date
299 and nvl(asg2.effective_end_date, to_date('31-12-4712','DD-MM-RRRR')) >= cp_start_date)
300 /*bug 3595646*/
301 and payef.assignment_id(+)= paa.assignment_id
302 -- For SR 5108858.993
303 -- 6774415 Changed eff dates to cert dates
304 and payef.certificate_start_date(+) <= cp_end_date
305 and (payef.certificate_end_date IS NULL OR payef.certificate_end_date >= cp_start_date)
306 --
307 and (payef.effective_end_date = (select max(paye.effective_end_date)
308 from pay_ie_paye_details_f paye
309 where paye.assignment_id = paa.assignment_id
310 --6774415 Changed eff dates to cert dates
311 and paye.certificate_start_date <= cp_end_date
312 and nvl(paye.certificate_end_date,to_date('31/12/4712','DD/MM/YYYY')) >= cp_start_date
313 )
314 or
315 payef.effective_end_date IS NULL
316 )
317 and prsif.assignment_id(+)= paa.assignment_id
318 -- For SR - 5108858.993, similar changes were made to PRSI as
319 -- made for PAYE
320 and prsif.effective_start_date(+) <= cp_end_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD')*/
321 and prsif.effective_end_date(+) >= cp_start_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'START_DATE'),'YYYY/MM/DD')*/
322 --
323 and (prsif.effective_end_date = (select max(prsi.effective_end_date)
324 from pay_ie_prsi_details_f prsi
325 where prsi.assignment_id = paa.assignment_id
326 and prsi.effective_start_date <= cp_end_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD')*/
327 and prsi.effective_end_date >= cp_start_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'START_DATE'),'YYYY/MM/DD')*/
328 )
329 or
330 prsif.effective_end_date IS NULL
331 )
332 -- Bug 3446744 Removed the check of a P45 existence
333 /* and not exists (select 1 from pay_assignment_actions paax
334 ,pay_payroll_actions ppax
335 WHERE
336 paax.assignment_id = paa.assignment_id
337 and ppax.payroll_action_id = paax.payroll_action_id
338 and ppax.report_type = 'P45'
339 and ppax.business_group_id = ppa35.business_group_id
340 and ppax.action_status = 'C') */
341 /*6876894*/
342 /* removing the check with the assignment set ammendments and checking later for both ammendment set criteria
343 and ammendments for a particular assignment set id*/
344 /* AND (p_assignment_set_id IS NULL OR EXISTS (SELECT ' '
345 FROM HR_ASSIGNMENT_SET_AMENDMENTS HR_ASG
346 WHERE HR_ASG.ASSIGNMENT_SET_ID=NVL(p_assignment_set_id, HR_ASG.ASSIGNMENT_SET_ID)
347 AND HR_ASG.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID ))
348 */
349 and PAPF.payroll_id = paf.payroll_id
350 and PAPF.business_group_id + 0 = p_business_group_id /*4483028*/
351 and PAPF.payroll_id = nvl(p_payroll_id,papf.payroll_id)
352 and papf.consolidation_set_id =nvl(p_consolidation_set_id,PAPF.consolidation_set_id)
353 and PAPF.effective_end_date = (select max(PAPF1.effective_end_date)
354 from pay_all_payrolls_f PAPF1
355 where PAPF1.payroll_id = PAPF.payroll_id
356 and PAPF1.effective_start_date <= cp_end_date --to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD')
357 and PAPF1.effective_end_date >= cp_start_date --to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'START_DATE'),'YYYY/MM/DD')
358 )
359 AND pact_ade.action_information_category = 'ADDRESS DETAILS'
360 AND pact_ade.action_context_type = 'PA'
361 AND pai.action_information_category = 'IE P35 DETAIL'
362 -- added for PRSI section changes 5657992
363 AND pai_prsi.action_information_category = 'IE P35 ADDITIONAL DETAILS'
364 AND pai.action_context_id = pai_prsi.action_context_id
365 -- end 5657992
366 AND pact_ade.ACTION_CONTEXT_ID = paa.payroll_action_id
367 and paf.period_of_service_id = pps.period_of_service_id
368 and paf.person_id= pps.person_id
369 order by decode(p_sort_order,'Last Name',SUBSTR(trim(pai.action_information18||','|| pai.action_information19),1,30),
370 'Address Line1',substr(trim(pai.action_information21),1,30),
371 'Address Line2',substr(trim(pai.action_information22),1,30),
372 'County',rpad(substr(trim(pai.action_information23),1,30) ,30,' '),
373 'Assignment Number',paf.assignment_number,
374 'National Identifier',nvl(pai.action_information1,' '),
375 SUBSTR(trim(pai.action_information18||','|| pai.action_information19),1,30));
376
377
378 /*6876894*/
379 l_formula_id NUMBER;
380 l_include_flag VARCHAR2(10);
381 skip_assignment Exception;
382 l_flag VARCHAR2(2);
383 begin
384 hr_utility.set_location('Entering get_p60_details',10);
385 vCtr := 0;
386 vXMLTable(vCtr).xmlstring := '<?xml version="1.0" encoding="UTF-8"?>';
387 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<start>';
388 vCtr := vCtr +1;
389
390
391 /*6876894*/
392 get_asg_set_details(p_assignment_set_id => p_assignment_set_id
393 ,p_formula_id => l_formula_id
394 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
395
396 );
397 fnd_file.put_line(fnd_file.LOG,'after get_asg_set_details' );
398
399
400 for p60 in c_p60_records(p_53_indicator,
401 cp_start_date,
402 cp_effective_date,
403 cp_end_date,
404 p_business_group_id,
405 p_assignment_set_id,
406 p_payroll_id,
407 p_consolidation_set_id,
408 p_sort_order) LOOP
409
410
411 /*6876894*/
412 fnd_file.put_line(fnd_file.LOG,'assignment_id'||p60.assignment_id);
413 l_flag:='Y';
414 If p_assignment_set_id is not null then
415 l_include_flag := chk_is_asg_in_asg_set(p_assignment_id => p60.assignment_id
416 ,p_formula_id => l_formula_id
417 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
418 ,p_effective_date => cp_effective_date
419 );
420 fnd_file.put_line(fnd_file.LOG,'l_include_flag'||l_include_flag);
421 if l_include_flag = 'N' then
422 l_flag:='N';
423 end if;
424 end if;
425 IF(l_flag='Y') THEN
426
427 vXMLTable(vCtr).xmlstring := '<G_Q1_YEAR>';
428 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring ||'<Q1_YEAR>'||p60.Q1_YEAR||'</Q1_YEAR>';
429 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employee>'||'<![CDATA[ '||p60.Q1_Employee||' ]]>'||'</Q1_Employee>';
430 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Tax_credits>'||p60.Q1_Tax_credits||'</Q1_Tax_credits>';
431 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Std_Rate>'||p60.Q1_Std_Rate||'</Q1_Std_Rate>';
432 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Address1>'||'<![CDATA[ '||p60.Q1_Address1||' ]]>'|| '</Q1_Address1>';
433 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Address2>'||'<![CDATA[ '||p60.Q1_Address2||' ]]>'||'</Q1_Address2>';
434 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_County>'||p60.Q1_County||'</Q1_County>';
435 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_TB_Indicator>'||p60.Q1_TB_Indicator||'</Q1_TB_Indicator>';
436 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_PPSN>'||p60.Q1_PPSN||'</Q1_PPSN>';
437 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_53_Indicator>'||p60.Q1_53_Indicator||'</Q1_53_Indicator>';
438 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_WM_Indicator>'||p60.Q1_WM_Indicator||'</Q1_WM_Indicator>';
439 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Director_Indicator>'||p60.Q1_Director_Indicator||'</Q1_Director_Indicator>';
440 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Total_Pay>'||p60.Q1_Total_Pay||'</Q1_Total_Pay>';
441 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_EmployeePRSI>'||p60.Q1_EmployeePRSI||'</Q1_EmployeePRSI>';
442 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Previous_Emp_Pay>'||p60.Q1_Previous_Emp_Pay||'</Q1_Previous_Emp_Pay>';
443 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_TotalPRSI>'||p60.Q1_TotalPRSI||'</Q1_TotalPRSI>';
444 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Present_pay>'||p60.Q1_Present_pay||'</Q1_Present_pay>';
445 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Total_Weeks_Insurable>'||p60.Q1_Total_Weeks_Insurable||'</Q1_Total_Weeks_Insurable>';
446 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Initial_Contribution_Class>'||p60.Q1_Initial_Contribution_Class||'</Q1_Initial_Contribution_Class>';
447 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Total_Tax>'||p60.Q1_Total_Tax||'</Q1_Total_Tax>';
448 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Sub_Contribution_Class>'||p60.Q1_Sub_Contribution_Class||'</Q1_Sub_Contribution_Class>';
449 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Previous_Emp_Tax>'||p60.Q1_Previous_Emp_Tax||'</Q1_Previous_Emp_Tax>';
450 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Weeks_In_Later_CC>'||p60.Q1_Weeks_In_Later_CC||'</Q1_Weeks_In_Later_CC>';
451 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_PR_Indicator>'||p60.Q1_PR_Indicator||'</Q1_PR_Indicator>';
452 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Present_tax>'||p60.Q1_Present_tax||'</Q1_Present_tax>';
453 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Date_Of_Hire>'||p60.Q1_Date_Of_Hire||'</Q1_Date_Of_Hire>';
454 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employer>'||'<![CDATA[ '||p60.Q1_Employer||' ]]>'||'</Q1_Employer>';
455 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employer_RegNo>'||p60.Q1_Employer_RegNo||'</Q1_Employer_RegNo>';
456 -- Bug No: 6474486 : New tag Q1_Employer_PhoneNo is added to the RTF template.
457 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employer_PhoneNo>'||p60.Q1_Employer_PhoneNo||'</Q1_Employer_PhoneNo>';
458 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Report_date>'||p60.Q1_Report_date||'</Q1_Report_date>';
459 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Assignment_Number>'||p60.Q1_Assignment_Number||'</Q1_Assignment_Number>'; -- 5467291
460 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring ||'</G_Q1_YEAR>';
461 vCtr := vCtr + 1;
462 END IF;
463 END LOOP;
464 vXMLTable(vCtr).xmlstring := '</start>';
465 end get_p60_details;
466 procedure populate_p60_details(P_53_INDICATOR IN VARCHAR2 DEFAULT NULL
467 ,P_START_DATE IN VARCHAR2 DEFAULT NULL
468 ,CP_EFFECTIVE_DATE IN VARCHAR2 DEFAULT NULL
469 ,P_END_DATE IN VARCHAR2 DEFAULT NULL
470 ,P_BUSINESS_GROUP_ID IN VARCHAR2 DEFAULT NULL
471 ,P_ASSIGNMENT_SET_ID IN VARCHAR2 DEFAULT NULL
472 ,P_PAYROLL_ID IN VARCHAR2 DEFAULT NULL
473 ,P_CONSOLIDATION_SET_ID IN VARCHAR2 DEFAULT NULL
474 ,P_SORT_ORDER IN VARCHAR2 DEFAULT NULL
475 ,P_TEMPLATE_NAME IN VARCHAR2
476 ,P_XML OUT NOCOPY CLOB
477 ) IS
478 cp_start_date date;
479 p_effective_date date;
480 cp_end_date date;
481 cp_business_group_id number := to_number(p_business_group_id);
482 cp_assignment_set_id number := to_number(p_assignment_set_id);
483 cp_payroll_id number := to_number(p_payroll_id);
484 cp_consolidation_set_id number := to_number(p_consolidation_set_id);
485
486 begin
487
488 cp_start_date := fnd_date.canonical_to_date(p_start_date);
489 p_effective_date := fnd_date.canonical_to_date(cp_effective_date);
490 cp_end_date := fnd_date.canonical_to_date(p_end_date);
491 get_p60_details(p_53_indicator,cp_start_date,p_effective_date,cp_end_date,
492 cp_business_group_id,cp_assignment_set_id,cp_payroll_id,
493 cp_consolidation_set_id,p_sort_order);
494 WritetoCLOB(p_xml);
495 end populate_p60_details;
496
497 -- Fucntion to Convert to Local Caharacter set
498 -- Bug 4705094
499 FUNCTION TO_UTF8(str in varchar2 )RETURN VARCHAR2
500 AS
501 db_charset varchar2(30);
502 BEGIN
503 select value into db_charset
504 from nls_database_parameters
505 where parameter = 'NLS_CHARACTERSET';
506 return convert(str,'UTF8',db_charset);
507 END;
508
509 PROCEDURE WritetoCLOB (p_xml out nocopy clob) IS
510 l_xfdf_string clob;
511 l_str1 varchar2(6000);
512 begin
513 hr_utility.set_location('Entered Procedure Write to clob ',100);
514 dbms_lob.createtemporary(p_xml,FALSE,DBMS_LOB.CALL);
515 dbms_lob.open(p_xml,dbms_lob.lob_readwrite);
516 if vXMLTable.count > 0 then
517 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
518 -- Bug 4705094
519 l_str1 := TO_UTF8(vXMLTable(ctr_table).xmlString);
520 dbms_lob.writeAppend( p_xml, length(l_str1), l_str1 );
521 END LOOP;
522 end if;
523 --DBMS_LOB.CREATETEMPORARY(p_xml,TRUE);
524 --clob_to_blob(l_xfdf_string,p_xml);
525 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
526 EXCEPTION
527 WHEN OTHERS then
528 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
529 HR_UTILITY.RAISE_ERROR;
530 END WritetoCLOB;
531
532 Procedure clob_to_blob(p_clob clob
533 ,p_blob IN OUT NOCOPY blob)
534 is
535 l_length_clob number;
536 l_offset integer;
537 l_varchar_buffer varchar2(32000);
538 l_raw_buffer raw(32000);
539 l_buffer_len number:= 32000;
540 l_chunk_len number;
541 l_blob blob;
542 begin
543 hr_utility.set_location('Entered Procedure clob to blob',120);
544 l_length_clob := dbms_lob.getlength(p_clob);
545 l_offset := 1;
546 while l_length_clob > 0 loop
547 hr_utility.trace('l_length_clob '|| l_length_clob);
548 if l_length_clob < l_buffer_len then
549 l_chunk_len := l_length_clob;
550 else
551 l_chunk_len := l_buffer_len;
552 end if;
553 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
554 l_raw_buffer := utl_raw.cast_to_raw(l_varchar_buffer);
555 hr_utility.trace('l_varchar_buffer '|| l_varchar_buffer);
556 dbms_lob.writeappend(p_blob,l_chunk_len,l_raw_buffer);
557 l_offset := l_offset + l_chunk_len;
558 l_length_clob := l_length_clob - l_chunk_len;
559 hr_utility.trace('l_length_blob '|| dbms_lob.getlength(p_blob));
560 end loop;
561 hr_utility.set_location('Finished Procedure clob to blob ',130);
562 end clob_to_blob;
563
564 Procedure fetch_rtf_blob (p_template_id number
565 ,p_rtf_blob OUT NOCOPY blob) IS
566 BEGIN
567 Select file_data Into p_rtf_blob
568 From fnd_lobs
569 Where file_id = p_template_id;
570 EXCEPTION
571 when no_data_found then
572 null;
573 END fetch_rtf_blob;
574 end;