[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_P60XML
Source
1 PACKAGE BODY PAY_IE_P60XML as
2 /* $Header: pyiep60p.pkb 120.30.12020000.6 2013/02/12 11:36:15 rsahai 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 /* 10277535 start */
98 FUNCTION test_XML(P_STRING VARCHAR2) RETURN VARCHAR2 AS
99 l_string varchar2(1000);
100
101 FUNCTION replace_xml_symbols(pp_string IN VARCHAR2)
102 RETURN VARCHAR2
103 AS
104
105 ll_string VARCHAR2(1000);
106
107 BEGIN
108
109
110 ll_string := pp_string;
111
112 ll_string := replace(ll_string, '&', '&');
113 ll_string := replace(ll_string, '<', '<');
114 ll_string := replace(ll_string, '>', '>');
115 ll_string := replace(ll_string, '''',''');
116 ll_string := replace(ll_string, '"', '"');
117
118 RETURN ll_string;
119 EXCEPTION when no_data_found then
120 null;
121 END replace_xml_symbols;
122
123 begin
124 l_string := p_string;
125 l_string := replace_xml_symbols(l_string);
126
127 l_string := pay_ie_p35_magtape.test_XML(l_string);
128
129 RETURN l_string;
130 END ;
131 /* 10277535 end */
132 FUNCTION chk_is_asg_in_asg_set(
133 p_assignment_id IN NUMBER
134 ,p_formula_id IN NUMBER
135 ,p_tab_asg_set_amnds IN t_asg_set_amnds
136 ,p_effective_date IN DATE
137 )
138 RETURN VARCHAR2
139 IS
140 l_session_date DATE;
141 l_include_flag VARCHAR2(10);
142 l_tab_asg_set_amnds t_asg_set_amnds;
143 l_inputs ff_exec.inputs_t;
144 l_outputs ff_exec.outputs_t;
145 --
146 BEGIN
147 --
148 fnd_file.put_line(fnd_file.LOG,'Entering chk_is_asg_in_asg_set');
149 l_include_flag := 'N';
150 l_tab_asg_set_amnds := p_tab_asg_set_amnds;
151 -- Check whether the assignment exists in the collection
152 -- first as the static assignment set overrides the
153 -- criteria one
154 IF l_tab_asg_set_amnds.EXISTS(p_assignment_id)
155 THEN
156 fnd_file.put_line(fnd_file.LOG,'Entered assignment ammendents if block');
157 -- Check whether to include or exclude
158 IF l_tab_asg_set_amnds(p_assignment_id) = 'I'
159 THEN
160 l_include_flag := 'Y';
161 ELSIF l_tab_asg_set_amnds(p_assignment_id) = 'E'
162 THEN
163 l_include_flag := 'N';
164 END IF; -- End if of include or exclude flag check ...
165 ELSIF p_formula_id IS NOT NULL
166 THEN
167 -- assignment does not exist in assignment set amendments
168 -- check whether a formula criteria exists for this
169 -- assignment set
170 -- Initialize the formula
171 fnd_file.put_line(fnd_file.LOG,'Entered assignment criteria block');
172 ff_exec.init_formula(p_formula_id => p_formula_id
173 ,p_effective_date => p_effective_date
174 ,p_inputs => l_inputs
175 ,p_outputs => l_outputs
176 );
177 fnd_file.put_line(fnd_file.LOG,'formula initialized');
178 -- Set the inputs first
179 -- Loop through them to set the contexts
180 FOR i IN l_inputs.FIRST .. l_inputs.LAST
181 LOOP
182 IF l_inputs(i).NAME = 'ASSIGNMENT_ID'
183 THEN
184 l_inputs(i).VALUE := p_assignment_id;
185 ELSIF l_inputs(i).NAME = 'DATE_EARNED'
186 THEN
187 l_inputs(i).VALUE := fnd_date.date_to_canonical(p_effective_date);
188 END IF;
189 END LOOP;
190 -- Run the formula
191 fnd_file.put_line(fnd_file.LOG,' before formaula run');
192
193
194 ff_exec.run_formula(l_inputs, l_outputs);
195
196
197 fnd_file.put_line(fnd_file.LOG,' aftre formaula run');
198 -- Check whether the assignment has to be included
199 -- by checking the output flag
200
201
202 fnd_file.put_line(fnd_file.LOG,' before outputs for run');
203 FOR i IN l_outputs.FIRST .. l_outputs.LAST
204 LOOP
205 IF l_outputs(i).NAME = 'INCLUDE_FLAG'
206 THEN
207 IF l_outputs(i).VALUE = 'Y'
208 THEN
209 l_include_flag := 'Y';
210 ELSIF l_outputs(i).VALUE = 'N'
211 THEN
212 l_include_flag := 'N';
213 END IF;
214 fnd_file.put_line(fnd_file.LOG,'p_assignment_id'||p_assignment_id);
215 fnd_file.put_line(fnd_file.LOG,'l_include_flag'||l_include_flag);
216 EXIT;
217 END IF;
218
219 END LOOP;
220 END IF; -- End if of assignment exists in amendments check ...
221
222 RETURN l_include_flag;
223 EXCEPTION
224 WHEN OTHERS
225 THEN
226 fnd_file.put_line(fnd_file.LOG,'..'||'SQL-ERRM :'||SQLERRM);
227 END chk_is_asg_in_asg_set;
228
229
230
231
232 procedure get_p60_details(p_53_indicator in varchar2,
233 cp_start_date in date,
234 cp_effective_date in date,
235 cp_end_date in date,
236 p_business_group_id in number,
237 p_assignment_set_id in number,
238 p_payroll_id in number,
239 p_consolidation_set_id in number,
240 p_sort_order in varchar2)
241 is
242 cursor c_p60_records(p_53_indicator varchar2,
243 cp_start_date date,
244 cp_effective_date date,
245 cp_end_date date,
246 p_business_group_id number,
247 p_assignment_set_id number,
248 p_payroll_id number,
249 p_consolidation_set_id number,
250 p_sort_order varchar2) IS
251 select
252 SUBSTR(trim(pai.action_information18||','|| pai.action_information19),1,30) Q1_Employee
253 ,substr(trim(pai.action_information21),1,30) Q1_Address1
254 ,substr(trim(pai.action_information22),1,30) Q1_Address2
255 ,rpad(substr(trim(pai.action_information23),1,30) ,30,' ') Q1_County
256 , to_char(cp_end_date,'YYYY') Q1_YEAR /*bug 3595646*/
257 ,nvl(pai.action_information1,' ') Q1_PPSN
258 ,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.WEEKLY_TAX_CREDIT*52),2),
259 decode(instr(papf.period_type,'Week'),0,round((payef.MONTHLY_TAX_CREDIT*12),2),round((payef.WEEKLY_TAX_CREDIT*52),2))),0),'99999990.00') Q1_Tax_credits --13637469
260 ,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.WEEKLY_STD_RATE_CUT_OFF*52),2),
261 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),'99999990.00') Q1_Std_Rate --13637469
262 ,decode(payef.tax_basis,'IE_WEEK1_MONTH1','W', 'IE_EXEMPT_WEEK_MONTH', 'W') Q1_WM_Indicator
263 ,decode(payef.TAX_BASIS,
264 'IE_WEEK1_MONTH1' , '1' ,
265 'IE_EXEMPT_WEEK_MONTH' , '1',
266 'IE_EMERGENCY','2',
267 NULL, '2', --7710479
268 decode(payef.INFO_SOURCE,'IE_P45','1')) Q1_TB_Indicator /* 6982274 */
269 -- ,decode(payef.TAX_BASIS,'IE_EMERGENCY','2',decode(payef.INFO_SOURCE,'IE_P45','1')) Q1_TB_Indicator
270 ,decode(p_53_indicator,'Y','X') Q1_53_Indicator
271 ,decode(prsif.director_flag,'Y','D') Q1_Director_Indicator
272 /*4130512 Total Pay must be sum of. ,nvl(round(to_number(trim(pai.action_information16)),2),0) Q1_Total_Pay*/
273 -- changes made for bug 5435931
274 ,to_char(nvl(round(to_number(substr(trim(pai.action_information28),1,instr(pai.action_information28,'|',1,1)-1)),2),0) +
275 nvl(round(to_number(substr(trim(pai.action_information28),instr(pai.action_information28,'|',1,1)+1)),2),0)
276 -- + nvl(round(to_number(trim(pai_prsi.action_information22)),2),0) --14659262 --15843742
277 ,'99999990.00') Q1_Total_Pay --13637469
278 -- bug 5435931
279 ,to_char(nvl(round(to_number(substr(trim(pai.action_information28),1,instr(pai.action_information28,'|',1,1)-1)),2),0),'99999990.00') Q1_Previous_Emp_Pay --13637469
280 -- bug 5435931
281 ,to_char(nvl(round(to_number(substr(trim(pai.action_information28),instr(pai.action_information28,'|',1,1)+1)),2),0)
282 -- + nvl(round(to_number(trim(pai_prsi.action_information22)),2),0) --14659262 --15843742
283 ,'99999990.00') Q1_Present_pay --13637469
284 -- bug 5435931
285 ,to_char(nvl(round(to_number(substr(trim(pai.action_information29),1,instr(pai.action_information29,'|',1,1)-1)),2),0) +
286 nvl(round(to_number(substr(trim(pai.action_information29),instr(pai.action_information29,'|',1,1)+1)),2),0),'99999990.00') Q1_Total_Tax --13637469
287 -- bug 5435931
288 ,to_char(nvl(round(to_number(substr(trim(pai.action_information29),1,instr(pai.action_information29,'|',1,1)-1)),2),0),'99999990.00') Q1_Previous_Emp_Tax --13637469
289 ,pai.action_information30 Q1_PR_Indicator
290 -- bug 5435931
291 ,to_char(nvl(round(to_number(substr(trim(pai.action_information29),instr(pai.action_information29,'|',1,1)+1)),2),0),'99999990.00') Q1_Present_tax --13637469
292 -- Modified for bug 5657992
293 ,to_char(nvl(round(to_number(trim(pai_prsi.action_information11)),2),0),'99999990.00') Q1_EmployeePRSI --13637469
294 ,to_char(nvl(round(to_number(nvl(trim(pai_prsi.action_information12),0)),2),0),'99999990.00') Q1_TotalPRSI --13637469
295 ,to_number(trim(pai_prsi.action_information13)) Q1_Total_Weeks_Insurable
296 ,pai_prsi.action_information14 Q1_Initial_Contribution_Class
297 ,rpad(pai_prsi.action_information15,2) Q1_Sub_Contribution_Class
298 ,nvl(to_number(trim(pai_prsi.action_information16)),0) Q1_Weeks_In_Later_CC
299 -- end bug 5657992
300 ,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
301 ,nvl(rtrim(pact_ade.action_information26),'') Q1_Employer
302 ,nvl(rtrim(pact_ade.action_information1),'') Q1_Employer_RegNo
303 --Bug No: 6474486 : Employer contact no. is added
304 ,nvl(rtrim(pact_ade.action_information28),'') Q1_Employer_PhoneNo
305 ,to_char(cp_effective_date,'DD-MON-RR') Q1_Report_date /* bug 3595646*/
306 ,paf.assignment_number Q1_Assignment_Number
307 ,paf.person_id Q1_Person_Id
308 ,paf.assignment_id assignment_id /*6876894*/
309 /* 10277535 start */
310 --,pai_prsi.action_information26 asg_location_id --11674153
311 ,nvl(to_number(substr(trim(pai_prsi.action_information26),1,instr(pai_prsi.action_information26,'|',1,1)-1)),0) asg_location_id --11674153
312 ,nvl(to_number(substr(trim(pai_prsi.action_information26),instr(pai_prsi.action_information26,'|',1,1)+1)),0) asg_org_id --11674153
313 ,upper(substr(trim(pact_ade.action_information5),1,30)) address_line1
314 ,upper(substr(trim(pact_ade.action_information6),1,30)) address_line2
315 ,upper(substr(trim(pact_ade.action_information7),1,30)) address_line3
316 ,upper(substr(trim(pact_ade.action_information11),1,30)) Location_name -- 10277535
317 /* 10277535 end */
318 --11857084
319 ,nvl(pai_prsi.action_information29,0) Q1_grosspay_usc
320 ,nvl(pai_prsi.action_information30,0) Q1_usc_ded
321 --11857084
322 --12556436
323 ,nvl(rtrim(pact_ade.action_information29),'') Q1_Employer_Email
324 ,decode(sign(to_char(nvl(round(to_number(substr(trim(pai.action_information29),instr(pai.action_information29,'|',1,1)+1)),2),0),'99999990.00')) --13637469
325 ,1,'D',-1,'R',NULL
326 ) Q1_Refund_Deduction
327 --12556436
328 --14659262
329 --15843742
330 --,nvl(payef.USC_RATE_1,0) Q1_USC_Rate1
331 --,nvl(payef.USC_RATE_2,0) Q1_USC_Rate2
332 /*
333 ,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.USC_WKLY_CUTOFF_1*52),2),
334 decode(instr(papf.period_type,'Week'),0,round((payef.USC_MTHLY_CUTOFF_1*12),2),round((payef.USC_WKLY_CUTOFF_1*52),2))),0),'99999990.00') Q1_USC_Rate1
335 ,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.USC_WKLY_CUTOFF_2*52),2),
336 decode(instr(papf.period_type,'Week'),0,round((payef.USC_MTHLY_CUTOFF_2*12),2),round((payef.USC_WKLY_CUTOFF_2*52),2))),0),'99999990.00') Q1_USC_Rate2
337 */
338 --16249683, 16315861
339 /*,nvl(payef.USC_YRLY_CUTOFF_1,0) Q1_USC_Rate1
340 ,nvl(payef.USC_YRLY_CUTOFF_2,0) Q1_USC_Rate2*/
341 ,to_char(nvl(NULL,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.USC_WKLY_CUTOFF_1*52),2),
342 decode(instr(papf.period_type,'Week'),0,round((payef.USC_MTHLY_CUTOFF_1*12),2),round((payef.USC_WKLY_CUTOFF_1*52),2))),0),'99999990.00')),'99999990.00') Q1_USC_Rate1
343 ,to_char(nvl(NULL,to_char(nvl(decode(papf.period_type,'Lunar Month',round((payef.USC_WKLY_CUTOFF_2*52),2),
344 decode(instr(papf.period_type,'Week'),0,round((payef.USC_MTHLY_CUTOFF_2*12),2),round((payef.USC_WKLY_CUTOFF_2*52),2))),0),'99999990.00')),'99999990.00') Q1_USC_Rate2
345 --16249683, 16315861
346 --15843742
347 ,to_char(nvl(round(to_number(trim(pai_prsi.action_information22)),2),0),'99999990.00') Q1_Illness_Benefit
348 ,to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information20),1,instr(pai_prsi.action_information20,'|',1,1)-1)),2),0) +
349 nvl(round(to_number(substr(trim(pai_prsi.action_information20),instr(pai_prsi.action_information20,'|',1,1)+1)),2),0),'99999990.00') Q1_Total_Pay_USC
350 ,to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information20),1,instr(pai_prsi.action_information20,'|',1,1)-1)),2),0),'99999990.00') Q1_Previous_Emp_Pay_USC
351 ,to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information20),instr(pai_prsi.action_information20,'|',1,1)+1)),2),0),'99999990.00') Q1_Present_pay_USC
352
353 ,to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information21),1,instr(pai_prsi.action_information21,'|',1,1)-1)),2),0) +
354 nvl(round(to_number(substr(trim(pai_prsi.action_information21),instr(pai_prsi.action_information21,'|',1,1)+1)),2),0),'99999990.00') Q1_Total_USC
355 ,to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information21),1,instr(pai_prsi.action_information21,'|',1,1)-1)),2),0),'99999990.00') Q1_Previous_Emp_USC
356 ,decode(sign(to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information21),instr(pai_prsi.action_information21,'|',1,1)+1)),2),0),'99999990.00'))
357 ,1,'D',-1,'R',NULL
358 ) Q1_USC_Refund_Deduction
359 ,to_char(nvl(round(to_number(substr(trim(pai_prsi.action_information21),instr(pai_prsi.action_information21,'|',1,1)+1)),2),0),'99999990.00') Q1_Present_USC
360 --14659262
361 FROM pay_action_information pai /*Employee Details Info*/
362 ,pay_action_information pai_prsi /* prsi Details 5657992 */
363 ,pay_action_information pact_ade /*Address Details - for Employer Name -IE Employer Tax Address*/
364 ,pay_payroll_actions ppa35
365 ,pay_assignment_actions paa
366 ,per_assignments_f paf
367 ,per_periods_of_service pps
368 ,pay_ie_paye_details_f payef
369 ,pay_ie_prsi_details_f prsif
370 ,pay_all_payrolls_f PAPF
371 WHERE
372 NVl('N','N') = 'N'
373 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
374 -- and cp_start_date <= to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD') /*4641756*/
375 and ppa35.report_type = 'IEP35'
376 and ppa35.business_group_id = p_business_group_id /* p_business_group_id */
377 and paa.payroll_action_id = ppa35.payroll_action_id
378 and paa.assignment_id = paf.assignment_id
379 and paa.action_status = 'C'
380 and paa.assignment_action_id = pai.action_context_id
381 and paf.period_of_service_id = pps.period_of_service_id
382 and paf.person_id= pps.person_id
383 and paf.business_group_id + 0 = p_business_group_id /*4483028*/
384 -- Bug 3446744 Checking if the employee has been terminated before issuing the P60
385 and (pps.actual_termination_date is null or pps.actual_termination_date >= cp_end_date)
386 and paf.effective_start_date = (select max(asg2.effective_start_date)
387 from per_all_assignments_f asg2
388 where asg2.assignment_id = paf.assignment_id
389 and asg2.effective_start_date <= cp_end_date
390 and nvl(asg2.effective_end_date, to_date('31-12-4712','DD-MM-RRRR')) >= cp_start_date)
391 /*bug 3595646*/
392 and payef.assignment_id(+)= paa.assignment_id
393 -- For SR 5108858.993
394 -- Bug#9503612 Fix commented the following and added code using effective date
395 /* -- 6774415 Changed eff dates to cert dates
396 and (payef.certificate_start_date is null or payef.certificate_start_date <= cp_end_date) --8229764
397 and (payef.certificate_end_date IS NULL OR payef.certificate_end_date >= cp_start_date) */
398 and payef.effective_start_date(+) <= cp_end_date
399 and payef.effective_end_date(+) >= cp_start_date
400
401 --
402 and (payef.effective_end_date = (select max(paye.effective_end_date)
403 from pay_ie_paye_details_f paye
404 where paye.assignment_id = paa.assignment_id
405 and paye.effective_start_date <= cp_end_date
406 and paye.effective_end_date >= cp_start_date
407 /* --6774415 Changed eff dates to cert dates, nvl for 8229764
408 and nvl(paye.certificate_start_date, to_date('01/01/0001','DD/MM/YYYY')) <= cp_end_date
409 and nvl(paye.certificate_end_date,to_date('31/12/4712','DD/MM/YYYY')) >= cp_start_date */
410 )
411 or
412 payef.effective_end_date IS NULL
413 )
414 and prsif.assignment_id(+)= paa.assignment_id
415 -- For SR - 5108858.993, similar changes were made to PRSI as
416 -- made for PAYE
417 and prsif.effective_start_date(+) <= cp_end_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD')*/
418 and prsif.effective_end_date(+) >= cp_start_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'START_DATE'),'YYYY/MM/DD')*/
419 --
420 and (prsif.effective_end_date = (select max(prsi.effective_end_date)
421 from pay_ie_prsi_details_f prsi
422 where prsi.assignment_id = paa.assignment_id
423 and prsi.effective_start_date <= cp_end_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD')*/
424 and prsi.effective_end_date >= cp_start_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'START_DATE'),'YYYY/MM/DD')*/
425 )
426 or
427 prsif.effective_end_date IS NULL
428 )
429 -- Bug 3446744 Removed the check of a P45 existence
430 /* and not exists (select 1 from pay_assignment_actions paax
431 ,pay_payroll_actions ppax
432 WHERE
433 paax.assignment_id = paa.assignment_id
434 and ppax.payroll_action_id = paax.payroll_action_id
435 and ppax.report_type = 'P45'
436 and ppax.business_group_id = ppa35.business_group_id
437 and ppax.action_status = 'C') */
438 /*6876894*/
439 /* removing the check with the assignment set ammendments and checking later for both ammendment set criteria
440 and ammendments for a particular assignment set id*/
441 /* AND (p_assignment_set_id IS NULL OR EXISTS (SELECT ' '
442 FROM HR_ASSIGNMENT_SET_AMENDMENTS HR_ASG
443 WHERE HR_ASG.ASSIGNMENT_SET_ID=NVL(p_assignment_set_id, HR_ASG.ASSIGNMENT_SET_ID)
444 AND HR_ASG.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID ))
445 */
446 and PAPF.payroll_id = paf.payroll_id
447 and PAPF.business_group_id + 0 = p_business_group_id /*4483028*/
448 and PAPF.payroll_id = nvl(p_payroll_id,papf.payroll_id)
449 and papf.consolidation_set_id =nvl(p_consolidation_set_id,PAPF.consolidation_set_id)
450 and PAPF.effective_end_date = (select max(PAPF1.effective_end_date)
451 from pay_all_payrolls_f PAPF1
452 where PAPF1.payroll_id = PAPF.payroll_id
453 and PAPF1.effective_start_date <= cp_end_date --to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD')
454 and PAPF1.effective_end_date >= cp_start_date --to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'START_DATE'),'YYYY/MM/DD')
455 )
456 AND pact_ade.action_information_category = 'ADDRESS DETAILS'
457 AND pact_ade.action_context_type = 'PA'
458 AND pai.action_information_category = 'IE P35 DETAIL'
459 -- added for PRSI section changes 5657992
460 AND pai_prsi.action_information_category = 'IE P35 ADDITIONAL DETAILS'
461 AND pai.action_context_id = pai_prsi.action_context_id
462 -- end 5657992
463 AND pact_ade.ACTION_CONTEXT_ID = paa.payroll_action_id
464 and paf.period_of_service_id = pps.period_of_service_id
465 and paf.person_id= pps.person_id
466 order by decode(p_sort_order,'Last Name',SUBSTR(trim(pai.action_information18||','|| pai.action_information19),1,30),
467 'Address Line1',substr(trim(pai.action_information21),1,30),
468 'Address Line2',substr(trim(pai.action_information22),1,30),
469 'County',rpad(substr(trim(pai.action_information23),1,30) ,30,' '),
470 'Assignment Number',paf.assignment_number,
471 'National Identifier',nvl(pai.action_information1,' '),
472 SUBSTR(trim(pai.action_information18||','|| pai.action_information19),1,30));
473
474
475 /*6876894*/
476 l_formula_id NUMBER;
477 l_include_flag VARCHAR2(10);
478 skip_assignment Exception;
479 l_flag VARCHAR2(2);
480
481 /* 10277535 start */
482 CURSOR csr_asg_empr_location(c_location_id hr_locations.location_id%type)
483 IS
484 SELECT NVL (TRIM (RPAD (hl.ADDRESS_LINE_1, 30)), ' ') address_line1,
485 NVL (TRIM (RPAD (hl.ADDRESS_LINE_2, 30)), ' ') address_line2,
486 NVL (TRIM (RPAD (hl.ADDRESS_LINE_3, 30)), ' ') address_line3,
487 NVL (TRIM (RPAD (hl.LOCATION_CODE, 30)), ' ') Location_name
488 FROM hr_locations hl
489 WHERE hl.location_id =c_location_id;
490 r_asg_empr_location csr_asg_empr_location%ROWTYPE;
491 l_er_address_line1 hr_locations.ADDRESS_LINE_1%TYPE;
492 l_er_address_line2 hr_locations.ADDRESS_LINE_2%TYPE;
493 l_er_address_line3 hr_locations.ADDRESS_LINE_3%TYPE;
494 l_er_loc_name hr_locations.LOCATION_CODE%TYPE;
495
496
497 /* 10277535 end */
498
499 --11674153
500 CURSOR csr_org_name(p_business_group_id number, p_asg_org_id number, cp_effective_date date)
501 IS
502 Select NVL (TRIM (RPAD (NAME, 30)), ' ') asg_org_name
503 FROM
504 hr_all_organization_units
505 where
506 business_group_id = p_business_group_id
507 and organization_id = p_asg_org_id
508 and cp_effective_date between DATE_FROM and NVL(DATE_TO,to_date('31-12-4712','dd-mm-rrrr'));
509
510 l_asg_org_name hr_all_organization_units.name%type;
511 --11674153
512
513 begin
514 hr_utility.set_location('Entering get_p60_details',10);
515 vCtr := 0;
516 vXMLTable(vCtr).xmlstring := '<?xml version="1.0" encoding="UTF-8"?>';
517 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<start>';
518 vCtr := vCtr +1;
519
520
521 /*6876894*/
522 get_asg_set_details(p_assignment_set_id => p_assignment_set_id
523 ,p_formula_id => l_formula_id
524 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
525
526 );
527 fnd_file.put_line(fnd_file.LOG,'after get_asg_set_details' );
528
529
530 for p60 in c_p60_records(p_53_indicator,
531 cp_start_date,
532 cp_effective_date,
533 cp_end_date,
534 p_business_group_id,
535 p_assignment_set_id,
536 p_payroll_id,
537 p_consolidation_set_id,
538 p_sort_order) LOOP
539
540
541 /*6876894*/
542 fnd_file.put_line(fnd_file.LOG,'assignment_id'||p60.assignment_id);
543 /* 10277535 start */
544 l_er_address_line1:='';
545 l_er_address_line2:='';
546 l_er_address_line3:='';
547 l_er_loc_name :='';
548 /* 10277535 end */
549 l_flag:='Y';
550 If p_assignment_set_id is not null then
551 l_include_flag := chk_is_asg_in_asg_set(p_assignment_id => p60.assignment_id
552 ,p_formula_id => l_formula_id
553 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
554 ,p_effective_date => cp_effective_date
555 );
556 fnd_file.put_line(fnd_file.LOG,'l_include_flag'||l_include_flag);
557 if l_include_flag = 'N' then
558 l_flag:='N';
559 end if;
560 end if;
561 IF(l_flag='Y') THEN
562 /* 10277535 start */
563 IF p60.asg_location_id IS NOT NULL THEN
564 fnd_file.put_line(fnd_file.LOG,'P60 Location id not null' || p60.asg_location_id);
565 OPEN csr_asg_empr_location(p60.asg_location_id);
566 FETCH csr_asg_empr_location INTO r_asg_empr_location;
567 CLOSE csr_asg_empr_location;
568
569 l_er_address_line1 := test_XML(r_asg_empr_location.address_line1);
570 l_er_address_line2 := test_XML(r_asg_empr_location.address_line2);
571 l_er_address_line3 := test_XML(r_asg_empr_location.address_line3);
572 l_er_loc_name := test_XML(r_asg_empr_location.Location_name);
573 fnd_file.put_line(fnd_file.LOG,'P60 Address lines set');
574 ELSE
575 l_er_address_line1 := test_XML(p60.address_line1);
576 l_er_address_line2 := test_XML(p60.address_line2);
577 l_er_address_line3 := test_XML(p60.address_line3);
578 l_er_loc_name := test_XML(p60.Location_name);
579 fnd_file.put_line(fnd_file.LOG,'P60 Location id is null');
580 END IF;
581 --11674153
582 OPEN csr_org_name(p_business_group_id, p60.asg_org_id, cp_effective_date);
583 FETCH csr_org_name INTO l_asg_org_name;
584 CLOSE csr_org_name;
585 --11674153
586
587 /* 10277535 end */
588
589 vXMLTable(vCtr).xmlstring := '<G_Q1_YEAR>';
590 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring ||'<Q1_YEAR>'||p60.Q1_YEAR||'</Q1_YEAR>';
591 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employee>'||'<![CDATA[ '||p60.Q1_Employee||' ]]>'||'</Q1_Employee>';
592 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Emp_Org_Name>'||'<![CDATA[ '||l_asg_org_name||' ]]>'||'</Q1_Emp_Org_Name>'; --11674153
593 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Tax_credits>'||trim(p60.Q1_Tax_credits)||'</Q1_Tax_credits>'; --14659262
594 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Std_Rate>'||trim(p60.Q1_Std_Rate)||'</Q1_Std_Rate>'; --14659262
595 --14659262
596 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_USC_Rate1>'||trim(p60.Q1_USC_Rate1)||'</Q1_USC_Rate1>';
597 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_USC_Rate2>'||trim(p60.Q1_USC_Rate2)||'</Q1_USC_Rate2>';
598 --14659262
599 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Address1>'||'<![CDATA['||p60.Q1_Address1||']]>'|| '</Q1_Address1>';
600 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Address2>'||'<![CDATA['||p60.Q1_Address2||']]>'||'</Q1_Address2>';
601 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_County>'|| p60.Q1_County ||'</Q1_County>'; /* knadhan */
602 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_TB_Indicator>'||p60.Q1_TB_Indicator||'</Q1_TB_Indicator>';
603 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_PPSN>'||p60.Q1_PPSN||'</Q1_PPSN>';
604 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_53_Indicator>'||p60.Q1_53_Indicator||'</Q1_53_Indicator>';
605 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_WM_Indicator>'||p60.Q1_WM_Indicator||'</Q1_WM_Indicator>';
606 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Director_Indicator>'||p60.Q1_Director_Indicator||'</Q1_Director_Indicator>';
607 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Total_Pay>'||p60.Q1_Total_Pay||'</Q1_Total_Pay>';
608 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_EmployeePRSI>'||p60.Q1_EmployeePRSI||'</Q1_EmployeePRSI>';
609 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Previous_Emp_Pay>'||p60.Q1_Previous_Emp_Pay||'</Q1_Previous_Emp_Pay>';
610 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_TotalPRSI>'||p60.Q1_TotalPRSI||'</Q1_TotalPRSI>';
611 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Present_pay>'||p60.Q1_Present_pay||'</Q1_Present_pay>';
612 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Total_Weeks_Insurable>'||p60.Q1_Total_Weeks_Insurable||'</Q1_Total_Weeks_Insurable>';
613 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Initial_Contribution_Class>'||p60.Q1_Initial_Contribution_Class||'</Q1_Initial_Contribution_Class>';
614 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Total_Tax>'||p60.Q1_Total_Tax||'</Q1_Total_Tax>';
615 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Sub_Contribution_Class>'||p60.Q1_Sub_Contribution_Class||'</Q1_Sub_Contribution_Class>';
616 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Previous_Emp_Tax>'||p60.Q1_Previous_Emp_Tax||'</Q1_Previous_Emp_Tax>';
617 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Weeks_In_Later_CC>'||p60.Q1_Weeks_In_Later_CC||'</Q1_Weeks_In_Later_CC>';
618 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_PR_Indicator>'||p60.Q1_PR_Indicator||'</Q1_PR_Indicator>';
619 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Present_tax>'||p60.Q1_Present_tax||'</Q1_Present_tax>';
620 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Refund_Deduction>'||p60.Q1_Refund_Deduction||'</Q1_Refund_Deduction>'; --12556436
621 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Date_Of_Hire>'||p60.Q1_Date_Of_Hire||'</Q1_Date_Of_Hire>';
622 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employer>'||'<![CDATA[ '||p60.Q1_Employer||' ]]>'||'</Q1_Employer>';
623 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employer_RegNo>'||p60.Q1_Employer_RegNo||'</Q1_Employer_RegNo>';
624 /* 10277535 start */
625 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employer_Add_Line1>'|| l_er_address_line1 ||'</Q1_Employer_Add_Line1>';
626 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employer_Add_Line2>'|| l_er_address_line2 ||'</Q1_Employer_Add_Line2>';
627 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employer_Add_Line3>'|| l_er_address_line3 ||'</Q1_Employer_Add_Line3>';
628 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employer_Loc_Name>'|| l_er_loc_name ||'</Q1_Employer_Loc_Name>';
629 /* 10277535 end */
630 --11857084
631 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_grosspay_usc>'||lpad(trim(to_char(fnd_number.canonical_to_number(nvl(p60.Q1_grosspay_usc,0)) ,'99999990.99')),10,' ')||'</Q1_grosspay_usc>';
632 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_usc_ded>'||lpad(trim(to_char(fnd_number.canonical_to_number(nvl( p60.Q1_usc_ded ,0)) ,'99999990.99')),10,' ')||'</Q1_usc_ded>';
633 --11857084
634 --14659262
635 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Illness_Benefit>'||lpad(trim(to_char(fnd_number.canonical_to_number(nvl( p60.Q1_Illness_Benefit ,0)) ,'99999990.99')),10,' ')||'</Q1_Illness_Benefit>';
636 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Total_Pay_USC>'||lpad(trim(to_char(fnd_number.canonical_to_number(nvl( p60.Q1_Total_Pay_USC ,0)) ,'99999990.99')),10,' ')||'</Q1_Total_Pay_USC>';
637 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Previous_Emp_Pay_USC>'||lpad(trim(to_char(fnd_number.canonical_to_number(nvl( p60.Q1_Previous_Emp_Pay_USC ,0)) ,'99999990.99')),10,' ')||'</Q1_Previous_Emp_Pay_USC>';
638 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Present_pay_USC>'||lpad(trim(to_char(fnd_number.canonical_to_number(nvl( p60.Q1_Present_pay_USC ,0)) ,'99999990.99')),10,' ')||'</Q1_Present_pay_USC>';
639 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Total_USC>'||lpad(trim(to_char(fnd_number.canonical_to_number(nvl( p60.Q1_Total_USC ,0)) ,'99999990.99')),10,' ')||'</Q1_Total_USC>';
640 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Previous_Emp_USC>'||lpad(trim(to_char(fnd_number.canonical_to_number(nvl( p60.Q1_Previous_Emp_USC ,0)) ,'99999990.99')),10,' ')||'</Q1_Previous_Emp_USC>';
641 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Present_USC>'||lpad(trim(to_char(fnd_number.canonical_to_number(nvl( p60.Q1_Present_USC ,0)) ,'99999990.99')),10,' ')||'</Q1_Present_USC>';
642 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_USC_Refund_Deduction>'||p60.Q1_USC_Refund_Deduction||'</Q1_USC_Refund_Deduction>';
643 --14659262
644 -- Bug No: 6474486 : New tag Q1_Employer_PhoneNo is added to the RTF template.
645 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employer_PhoneNo>'||p60.Q1_Employer_PhoneNo||'</Q1_Employer_PhoneNo>';
646 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Employer_Email>'||'<![CDATA[ '||p60.Q1_Employer_Email||' ]]>'||'</Q1_Employer_Email>'; --12556436
647 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Report_date>'||p60.Q1_Report_date||'</Q1_Report_date>';
648 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Q1_Assignment_Number>'||p60.Q1_Assignment_Number||'</Q1_Assignment_Number>'; -- 5467291
649 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring ||'</G_Q1_YEAR>';
650 vCtr := vCtr + 1;
651 END IF;
652 END LOOP;
653 vXMLTable(vCtr).xmlstring := '</start>';
654 end get_p60_details;
655 procedure populate_p60_details(P_53_INDICATOR IN VARCHAR2 DEFAULT NULL
656 ,P_START_DATE IN VARCHAR2 DEFAULT NULL
657 ,CP_EFFECTIVE_DATE IN VARCHAR2 DEFAULT NULL
658 ,P_END_DATE IN VARCHAR2 DEFAULT NULL
659 ,P_BUSINESS_GROUP_ID IN VARCHAR2 DEFAULT NULL
660 ,P_ASSIGNMENT_SET_ID IN VARCHAR2 DEFAULT NULL
661 ,P_PAYROLL_ID IN VARCHAR2 DEFAULT NULL
662 ,P_CONSOLIDATION_SET_ID IN VARCHAR2 DEFAULT NULL
663 ,P_SORT_ORDER IN VARCHAR2 DEFAULT NULL
664 ,P_TEMPLATE_NAME IN VARCHAR2
665 ,P_XML OUT NOCOPY CLOB
666 ) IS
667 cp_start_date date;
668 p_effective_date date;
669 cp_end_date date;
670 cp_business_group_id number := to_number(p_business_group_id);
671 cp_assignment_set_id number := to_number(p_assignment_set_id);
672 cp_payroll_id number := to_number(p_payroll_id);
673 cp_consolidation_set_id number := to_number(p_consolidation_set_id);
674
675 begin
676
677 cp_start_date := fnd_date.canonical_to_date(p_start_date);
678 p_effective_date := fnd_date.canonical_to_date(cp_effective_date);
679 cp_end_date := fnd_date.canonical_to_date(p_end_date);
680 get_p60_details(p_53_indicator,cp_start_date,p_effective_date,cp_end_date,
681 cp_business_group_id,cp_assignment_set_id,cp_payroll_id,
682 cp_consolidation_set_id,p_sort_order);
683 WritetoCLOB(p_xml);
684 end populate_p60_details;
685
686 -- Fucntion to Convert to Local Caharacter set
687 -- Bug 4705094
688 FUNCTION TO_UTF8(str in varchar2 )RETURN VARCHAR2
689 AS
690 db_charset varchar2(30);
691 BEGIN
692 select value into db_charset
693 from nls_database_parameters
694 where parameter = 'NLS_CHARACTERSET';
695 return convert(str,'UTF8',db_charset);
696 END;
697
698 PROCEDURE WritetoCLOB (p_xml out nocopy clob) IS
699 l_xfdf_string clob;
700 l_str1 varchar2(6000);
701 begin
702 hr_utility.set_location('Entered Procedure Write to clob ',100);
703 dbms_lob.createtemporary(p_xml,FALSE,DBMS_LOB.CALL);
704 dbms_lob.open(p_xml,dbms_lob.lob_readwrite);
705 if vXMLTable.count > 0 then
706 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
707 -- Bug 4705094
708 l_str1 := TO_UTF8(vXMLTable(ctr_table).xmlString);
709 dbms_lob.writeAppend( p_xml, length(l_str1), l_str1 );
710 END LOOP;
711 end if;
712 --DBMS_LOB.CREATETEMPORARY(p_xml,TRUE);
713 --clob_to_blob(l_xfdf_string,p_xml);
714 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
715 EXCEPTION
716 WHEN OTHERS then
717 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
718 HR_UTILITY.RAISE_ERROR;
719 END WritetoCLOB;
720
721 Procedure clob_to_blob(p_clob clob
722 ,p_blob IN OUT NOCOPY blob)
723 is
724 l_length_clob number;
725 l_offset integer;
726 l_varchar_buffer varchar2(32000);
727 l_raw_buffer raw(32000);
728 l_buffer_len number:= 32000;
729 l_chunk_len number;
730 l_blob blob;
731 begin
732 hr_utility.set_location('Entered Procedure clob to blob',120);
733 l_length_clob := dbms_lob.getlength(p_clob);
734 l_offset := 1;
735 while l_length_clob > 0 loop
736 hr_utility.trace('l_length_clob '|| l_length_clob);
737 if l_length_clob < l_buffer_len then
738 l_chunk_len := l_length_clob;
739 else
740 l_chunk_len := l_buffer_len;
741 end if;
742 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
743 l_raw_buffer := utl_raw.cast_to_raw(l_varchar_buffer);
744 hr_utility.trace('l_varchar_buffer '|| l_varchar_buffer);
745 dbms_lob.writeappend(p_blob,l_chunk_len,l_raw_buffer);
746 l_offset := l_offset + l_chunk_len;
747 l_length_clob := l_length_clob - l_chunk_len;
748 hr_utility.trace('l_length_blob '|| dbms_lob.getlength(p_blob));
749 end loop;
750 hr_utility.set_location('Finished Procedure clob to blob ',130);
751 end clob_to_blob;
752
753 Procedure fetch_rtf_blob (p_template_id number
754 ,p_rtf_blob OUT NOCOPY blob) IS
755 BEGIN
756 Select file_data Into p_rtf_blob
757 From fnd_lobs
758 Where file_id = p_template_id;
759 EXCEPTION
760 when no_data_found then
761 null;
762 END fetch_rtf_blob;
763 end;