[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_EMP_TRAINEE_REIMBURSE
Source
1 PACKAGE BODY PAY_DK_EMP_TRAINEE_REIMBURSE AS
2 /* $Header: pydkaerrpt.pkb 120.16.12000000.1 2007/01/17 18:24:26 appldev noship $ */
3 --
4 -- Global Data
5 --
6 TYPE t_xml_element_rec IS RECORD
7 (tagname VARCHAR2(100)
8 ,tagvalue VARCHAR2(500));
9 --
10 TYPE t_xml_element_table IS TABLE OF t_xml_element_rec INDEX BY BINARY_INTEGER;
11 --
12 g_xml_element_table t_xml_element_table;
13 --
14 -- -----------------------------------------------------------------------------
15 -- Get the correct characterset for XML generation
16 -- -----------------------------------------------------------------------------
17 --
18 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
19 CURSOR csr_get_iana_charset IS
20 SELECT tag
21 FROM fnd_lookup_values
22 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
23 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
24 INSTR(USERENV('LANGUAGE'), '.') + 1)
25 AND language = 'US';
26 --
27 lv_iana_charset fnd_lookup_values.tag%type;
28 BEGIN
29 OPEN csr_get_iana_charset;
30 FETCH csr_get_iana_charset INTO lv_iana_charset;
31 CLOSE csr_get_iana_charset;
32 RETURN (lv_iana_charset);
33 END get_IANA_charset;
34 --
35 --
36 -- -----------------------------------------------------------------------------
37 -- Takes XML element from a table and puts them into a CLOB.
38 -- -----------------------------------------------------------------------------
39 --
40 PROCEDURE write_to_clob
41 (p_clob OUT NOCOPY CLOB) IS
42 --
43 l_xml_element_template1 VARCHAR2(30) := '<TAG>VALUE</TAG>';
44 l_xml_element_template2 VARCHAR2(10) := '<TAG>';
45 l_xml_element_template3 VARCHAR2(10) := '</TAG>';
46 l_str1 VARCHAR2(80) ;
47 l_str2 VARCHAR2(20) := ' </ROOT>';
48 l_xml_element VARCHAR2(800);
49 l_clob CLOB;
50 --
51 BEGIN
52 --
53 -- l_str1 := '<?xml version="1.0" encoding="UTF-8"?> <ROOT>';
54 l_str1 := '<?xml version="1.0" encoding="' || get_IANA_charset || '"?> <ROOT>';
55
56 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
57 dbms_lob.open(l_clob, DBMS_LOB.LOB_READWRITE);
58 --
59 dbms_lob.writeappend(l_clob, LENGTH(l_str1), l_str1);
60 --
61 IF g_xml_element_table.COUNT > 0 THEN
62 FOR table_counter IN g_xml_element_table.FIRST .. g_xml_element_table.LAST LOOP
63 IF g_xml_element_table(table_counter).tagvalue = '_START_' THEN
64 l_xml_element := REPLACE(l_xml_element_template2, 'TAG', g_xml_element_table(table_counter).tagname);
65 ELSIF g_xml_element_table(table_counter).tagvalue = '_END_' THEN
66 l_xml_element := REPLACE(l_xml_element_template3, 'TAG', g_xml_element_table(table_counter).tagname);
67 ELSE
68 l_xml_element := REPLACE(
69 REPLACE (l_xml_element_template1, 'TAG', g_xml_element_table(table_counter).tagname)
70 ,'VALUE', g_xml_element_table(table_counter).tagvalue);
71 END IF;
72 --
73 dbms_lob.writeappend(l_clob, LENGTH(l_xml_element), l_xml_element);
74 END LOOP;
75 END IF;
76 --
77 dbms_lob.writeappend(l_clob, LENGTH(l_str2), l_str2);
78 --
79 p_clob := l_clob;
80 --
81 EXCEPTION
82 WHEN OTHERS THEN
83 hr_utility.set_location(sqlerrm(sqlcode),110);
84 --
85 END write_to_clob;
86 --
87 -------------------------------------------------------------------------------------------------------------------------
88 /* Populate details procedure to construct XML output */
89 -- NAME
90 -- populate_details
91 -- PURPOSE
92 -- To generate XML output for employer trainee reimbursement report.
93 -- ARGUMENTS
94 -- P_QUARTER - Quarter the report to run.
95 -- P_LEGAL_EMPLOYER_ID - Legal employer id.
96 -- P_BUSINESS_GROUP_ID - Business group id.
97 -- P_TEMPLATE_NAME - The name of the template.
98 -- P_XML - Output variable keeps the resulted xml.
99 -- USES
100 -- NOTES
101 -- This is used to generate XML output for Employer trainee reimbursement report.
102 -- This will fetch all person's available under the inputted business group and legal employer.
103 -- It calculates the sum of employer and employee's reimbursement for the given input quarter
104 -- to arrive the total AER contribution.
105 -------------------------------------------------------------------------------------------------------------------------
106
107 PROCEDURE POPULATE_DETAILS ( P_QUARTER IN VARCHAR2,
108 P_LEGAL_EMPLOYER_ID IN NUMBER,
109 P_BUSINESS_GROUP_ID IN NUMBER,
110 P_EFFECTIVE_DATE1 IN VARCHAR2, --Bug 4895163 fix
111 P_TEMPLATE_NAME IN VARCHAR2,
112 P_XML OUT NOCOPY CLOB
113 )
114 IS
115 -- XMLRESULT CLOB;
116 -- XMLIDENT DBMS_XMLQUERY.CTXTYPE;
117 -- SQLSTR VARCHAR2(4000);
118 L_QTR_START DATE;
119 L_QTR_END DATE;
120 L_EMPR_BAL NUMBER;
121 L_EMPE_BAL NUMBER;
122 L_TOTAL_ATP NUMBER := 0;
123 L_GLOBAL_RATE NUMBER(30,7);
124 L_GLOBAL_ATP NUMBER(30,7);
125 L_OLDEMP NUMBER := 0;
126 L_EMPR_BAL_ID NUMBER;
127 L_EMPE_BAL_ID NUMBER;
128
129 l_emp_count NUMBER := 0;
130 l_ded_1 NUMBER := 1;
131 l_ded_50 NUMBER := 0;
132 l_ded_trainee NUMBER := 0;
133 l_tot_emp_aer NUMBER := 0;
134 l_tot_qtr_aer NUMBER := 0;
135
136 P_EFFECTIVE_DATE DATE;
137 l_trainee_status CHAR ;
138 -- Introduced for character set conversion on XML generation
139 -- lv_clob CLOB;
140 -- l_iana_charset VARCHAR2(100);
141 -- lv_offset NUMBER;
142 l_xml_element_count NUMBER := 1;
143
144 -- Cursor to fetch the payroll details of all the persons with primary assignment for the inputted quarter.
145 /* CURSOR C1(QUARTER_START DATE, QUARTER_END DATE)
146 IS
147 SELECT PASG.PERSON_ID, PASG.ASSIGNMENT_ID, MAX(PAA.ASSIGNMENT_ACTION_ID) AS ASSIGNMENT_ACTION_ID,
148 PPA.PAYROLL_ID, MAX(PPA.DATE_EARNED) AS EFFECTIVE_DATE, PAP.PER_INFORMATION3
149 FROM PER_ALL_PEOPLE_F PAP
150 ,PER_ALL_ASSIGNMENTS_F ASG
151 ,PAY_PAYROLL_ACTIONS PPA
152 ,PAY_ASSIGNMENT_ACTIONS PAA
153 ,PAY_RUN_RESULTS PRR
154 ,PAY_ELEMENT_TYPES_F PET
155 ,PER_ALL_ASSIGNMENTS_F PASG
156 WHERE PAP.PERSON_ID = ASG.PERSON_ID
157 AND ASG.PAYROLL_ID = PPA.PAYROLL_ID
158 AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
159 AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
160 AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
161 AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
162 AND PASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
163 AND PET.LEGISLATION_CODE = 'DK'
164 AND PPA.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
165 AND PAA.TAX_UNIT_ID = P_LEGAL_EMPLOYER_ID
166 AND PET.ELEMENT_NAME IN ('Employee ATP', 'Employer ATP')
167 AND PPA.DATE_EARNED BETWEEN QUARTER_START AND QUARTER_END
168 AND QUARTER_END BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
169 AND PASG.ASSIGNMENT_STATUS_TYPE_ID = 1
170 AND PAP.CURRENT_EMPLOYEE_FLAG = 'Y'
171 AND PAP.EFFECTIVE_START_DATE <= QUARTER_END
172 AND PAP.EFFECTIVE_END_DATE >= QUARTER_START
173 AND PASG.EFFECTIVE_START_DATE <= QUARTER_END
174 AND PASG.EFFECTIVE_END_DATE >= QUARTER_START
175 AND ASG.EFFECTIVE_START_DATE <= QUARTER_END
176 AND ASG.EFFECTIVE_END_DATE >= QUARTER_START
177 GROUP BY PASG.PERSON_ID, PASG.ASSIGNMENT_ID, PPA.PAYROLL_ID, PAP.PER_INFORMATION3
178 ORDER BY PASG.PERSON_ID, PASG.ASSIGNMENT_ID;*/
179
180 -- Cursor to fetch the payroll details of all the persons with primary assignment for the inputted quarter.
181 /*Bug 4895163 fix- Modified the cursor to get the details for each month*/
182 CURSOR C1(QUARTER_START DATE, QUARTER_END DATE)
183 IS
184 SELECT
185 PERSON_ID ,
186 ASSIGNMENT_ID ,
187 ASSIGNMENT_ACTION_ID,
188 PAYROLL_ID,
189 EFFECTIVE_DATE,
190 PER_INFORMATION3,
191 EFFECTIVE_START_DATE,
192 EFFECTIVE_END_DATE,
193 LEAD (ASSIGNMENT_ID,1) OVER (ORDER BY PERSON_ID, ASSIGNMENT_ID,EFFECTIVE_DATE,EFFECTIVE_START_DATE,
194 EFFECTIVE_END_DATE) AS LEAD_ASSIGNMENT_ID,
195 LEAD (EFFECTIVE_DATE,1) OVER (ORDER BY PERSON_ID, ASSIGNMENT_ID,EFFECTIVE_DATE,EFFECTIVE_START_DATE,
196 EFFECTIVE_END_DATE) AS LEAD_EFFECTIVE_DATE
197 FROM (
198 SELECT ASG.PERSON_ID, ASG.ASSIGNMENT_ID, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID,
199 PPA.PAYROLL_ID, PPA.DATE_EARNED EFFECTIVE_DATE, PAP.PER_INFORMATION3,
200 PAP.EFFECTIVE_START_DATE,
201 PAP.EFFECTIVE_END_DATE
202 FROM PER_ALL_PEOPLE_F PAP
203 ,PER_ALL_ASSIGNMENTS_F ASG
204 ,PAY_PAYROLL_ACTIONS PPA
205 ,PAY_ASSIGNMENT_ACTIONS PAA
206 ,PAY_RUN_RESULTS PRR
207 ,PAY_ELEMENT_TYPES_F PET
208 WHERE PAP.PERSON_ID = ASG.PERSON_ID
209 AND ASG.PAYROLL_ID = PPA.PAYROLL_ID
210 AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
211 AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
212 AND PPA.ACTION_TYPE IN ('R','Q') -- Payroll Run or Quickpay Run
213 AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
214 AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
215 AND PET.LEGISLATION_CODE = 'DK'
216 AND PPA.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
217 AND PAA.TAX_UNIT_ID = P_LEGAL_EMPLOYER_ID
218 AND PET.ELEMENT_NAME IN ('Employee ATP', 'Employer ATP')
219 AND PPA.DATE_EARNED BETWEEN QUARTER_START AND QUARTER_END
220 AND ASG.ASSIGNMENT_STATUS_TYPE_ID = 1
221 AND PAP.CURRENT_EMPLOYEE_FLAG = 'Y'
222 /* Date check for the date tracked tables*/
223 AND TO_CHAR(PPA.DATE_EARNED,'MM/YYYY') BETWEEN to_char(PAP.EFFECTIVE_START_DATE,'MM/YYYY') AND TO_CHAR(PAP.EFFECTIVE_END_DATE,'MM/YYYY')
224 AND TO_CHAR (PPA.DATE_EARNED,'MM/YYYY') BETWEEN to_char(ASG.EFFECTIVE_START_DATE,'MM/YYYY') AND TO_CHAR(ASG.EFFECTIVE_END_DATE,'MM/YYYY')
225 AND PPA.DATE_EARNED BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
226 GROUP BY ASG.PERSON_ID, ASG.ASSIGNMENT_ID, PAA.ASSIGNMENT_ACTION_ID,
227 PPA.PAYROLL_ID, PPA.DATE_EARNED, PAP.PER_INFORMATION3,
228 PAP.EFFECTIVE_START_DATE,PAP.EFFECTIVE_END_DATE
229 ORDER BY ASG.PERSON_ID, ASG.ASSIGNMENT_ID,PPA.DATE_EARNED,PAP.EFFECTIVE_START_DATE,
230 PAP.EFFECTIVE_END_DATE);
231
232
233 -- Cursor to fetch the defined balance id for the given balance and dimension name.
234 CURSOR C2 (BAL_NAME VARCHAR2, DIM_NAME VARCHAR2)
235 IS
236 SELECT DEFINED_BALANCE_ID
237 FROM PAY_BALANCE_TYPES PBT
238 , PAY_DEFINED_BALANCES PDB
239 , PAY_BALANCE_DIMENSIONS PBD
240 WHERE PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
241 AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
242 AND PBT.BALANCE_NAME = BAL_NAME
243 AND PBD.DATABASE_ITEM_SUFFIX = DIM_NAME;
244
245 -- Cursor to fetch global values
246 CURSOR C3(GLB_NAME VARCHAR2, QUARTER_END DATE)
247 IS
248 SELECT TRIM(GLOBAL_VALUE)
249 FROM FF_GLOBALS_F GLB
250 WHERE QUARTER_END BETWEEN GLB.EFFECTIVE_START_DATE AND GLB.EFFECTIVE_END_DATE
251 AND GLB.GLOBAL_NAME = GLB_NAME
252 AND GLB.LEGISLATION_CODE = 'DK';
253
254 BEGIN
255 g_xml_element_table.DELETE;
256 -- Plsql block to select effective date from fnd_sessions table.
257 /* Bug 4895163 fix- Taking the effective date as input parameter*/
258 /* BEGIN
259 SELECT TRUNC(EFFECTIVE_DATE)
260 INTO P_EFFECTIVE_DATE
261 FROM FND_SESSIONS
262 WHERE SESSION_ID=USERENV('SESSIONID');
263 EXCEPTION
264 WHEN OTHERS THEN
265 P_EFFECTIVE_DATE := SYSDATE;
266 END;*/
267 --lv_offset := 23;
268 -- Create a temporary LOB to store the generated XML.
269 -- DBMS_LOB.CREATETEMPORARY (XMLRESULT, TRUE, DBMS_LOB.SESSION);
270 -- DBMS_LOB.CREATETEMPORARY (lv_clob, TRUE, DBMS_LOB.SESSION);
271 /*Converting the P_EFFECTIVE_DATE1 parameter to date and assigning it to P_EFFECTIVE_DATE*/
272 P_EFFECTIVE_DATE :=FND_DATE.CANONICAL_TO_DATE(P_EFFECTIVE_DATE1);
273 -- Control structure to identify which Quarter the report to be executed
274 IF P_QUARTER = 1 THEN
275 L_QTR_START := TO_DATE('01/01/'||TO_CHAR(P_EFFECTIVE_DATE,'YYYY'), 'DD/MM/YYYY');
276 L_QTR_END := TO_DATE('31/03/'||TO_CHAR(P_EFFECTIVE_DATE,'YYYY'), 'DD/MM/YYYY');
277 ELSIF P_QUARTER = 2 THEN
278 L_QTR_START := TO_DATE('01/04/'||TO_CHAR(P_EFFECTIVE_DATE,'YYYY'), 'DD/MM/YYYY');
279 L_QTR_END := TO_DATE('30/06/'||TO_CHAR(P_EFFECTIVE_DATE,'YYYY'), 'DD/MM/YYYY');
280
281 -- ELSIF P_QUARTER = 1 THEN
282 ELSIF P_QUARTER = 3 THEN --Bug 4895163 fix
283 L_QTR_START := TO_DATE('01/07/'||TO_CHAR(P_EFFECTIVE_DATE,'YYYY'), 'DD/MM/YYYY');
284 L_QTR_END := TO_DATE('30/09/'||TO_CHAR(P_EFFECTIVE_DATE,'YYYY'), 'DD/MM/YYYY');
285
286 -- ELSIF P_QUARTER = 1 THEN
287 ELSIF P_QUARTER = 4 THEN --Bug 4895163 fix
288 L_QTR_START := TO_DATE('01/10/'||TO_CHAR(P_EFFECTIVE_DATE,'YYYY'), 'DD/MM/YYYY');
289 L_QTR_END := TO_DATE('31/12/'||TO_CHAR(P_EFFECTIVE_DATE,'YYYY'), 'DD/MM/YYYY');
290 END IF;
291
292 -- To fetch employer defined balance id
293 -- OPEN C2('Employer ATP Deductions', '_ASG_LE_QTD');
294 OPEN C2('Employer ATP Deductions', '_ASG_RUN');
295 FETCH C2 INTO L_EMPR_BAL_ID;
296 CLOSE C2;
297
301 FETCH C2 INTO L_EMPE_BAL_ID;
298 -- To fetch employee defined balance id
299 -- OPEN C2('Employee ATP Deductions', '_ASG_LE_QTD');
300 OPEN C2('Employee ATP Deductions', '_ASG_RUN');
302 CLOSE C2;
303
304 -- To fetch GLOBAL VALUE of AER_ATPAMOUNT_QUARTER
305 OPEN C3 ('DK_AER_ATPAMOUNT_QUARTER', L_QTR_END);
306 FETCH C3 INTO L_GLOBAL_ATP;
307 CLOSE C3;
308
309 -- To fetch GLOBAL VALUE of AER_RATE
310 OPEN C3 ('DK_AER_RATE', L_QTR_END);
311 FETCH C3 INTO L_GLOBAL_RATE;
312 CLOSE C3;
313
314 FOR C1REC IN C1(l_qtr_start, l_qtr_end)
315 LOOP
316 /*To check if there are multiple rows for the same assignment in the same payroll run,if present then taking the last one*/
317 IF (C1REC.ASSIGNMENT_ID = NVL(C1REC.LEAD_ASSIGNMENT_ID,'-999')AND C1REC.EFFECTIVE_DATE = C1REC.LEAD_EFFECTIVE_DATE) THEN
318 NULL; -- if true then go to next record
319 ELSE
320 IF NVL(C1REC.PER_INFORMATION3,'N') = 'N' THEN
321 -- To get employer ATP contribution balance value
322 L_EMPR_BAL := PAY_BALANCE_PKG.GET_VALUE (L_EMPR_BAL_ID,
323 C1REC.ASSIGNMENT_ACTION_ID,
324 P_LEGAL_EMPLOYER_ID,
325 NULL,
326 NULL,
327 NULL,
328 C1REC.EFFECTIVE_DATE
329 );
330
331 -- To get employee ATP contribution balance value
332 L_EMPE_BAL := PAY_BALANCE_PKG.GET_VALUE (L_EMPE_BAL_ID,
333 C1REC.ASSIGNMENT_ACTION_ID,
334 P_LEGAL_EMPLOYER_ID,
335 NULL,
336 NULL,
337 NULL,
338 C1REC.EFFECTIVE_DATE
339 );
340 END IF ;
341 -- Total ATP contribution both employer and employee.
342 L_TOTAL_ATP := L_TOTAL_ATP + nvl(L_EMPR_BAL,0) + nvl(L_EMPE_BAL,0) ;
343 L_EMPR_BAL := 0;
344 L_EMPE_BAL := 0;
345 -- To fetch the no of trainees
346 /* IF L_OLDEMP <> C1REC.PERSON_ID THEN
347 IF C1REC.PER_INFORMATION3 = 'Y' THEN
348 L_DED_TRAINEE := L_DED_TRAINEE + 1;
349 END IF;
350 L_OLDEMP := C1REC.PERSON_ID;
351 END IF;*/
352 -- To fetch the no of trainees
353 /*Bug 4895163 fix- Chcking the previous employee trainee status in order to get the latest value*/
354 IF L_OLDEMP <> C1REC.PERSON_ID AND L_OLDEMP <> 0 THEN
355 IF l_trainee_status = 'Y' THEN
356 L_DED_TRAINEE := L_DED_TRAINEE + 1;
357 END IF;
358 END IF;
359 L_OLDEMP := C1REC.PERSON_ID;
360 l_trainee_status := NVL(C1REC.PER_INFORMATION3,'N');
361 END IF ;
362 END LOOP;
363 /*Bug 4895163 fix- Getting the trainee status for the last employee in the loop*/
364 IF l_trainee_status = 'Y' THEN
365 L_DED_TRAINEE := L_DED_TRAINEE + 1;
366 END IF;
367 l_emp_count := round((l_total_atp/l_global_atp),2);
368 l_ded_50 := FLOOR(l_emp_count/50);
369 l_tot_emp_aer := l_emp_count - (l_ded_1 + l_ded_50 + l_ded_trainee);
370
371 IF l_tot_emp_aer < 0 OR NVL(l_total_atp,0) = 0 THEN
372 l_total_atp := NULL;
373 l_emp_count := NULL;
374 l_ded_1 := NULL;
375 l_ded_50 := NULL;
376 l_ded_trainee := NULL;
377 l_tot_emp_aer := NULL;
378 l_tot_qtr_aer := NULL;
379
380 -- Set the message
381 hr_utility.set_message (801, 'PAY_377056_DK_NEGATIVE_ERR');
382 -- Put the meassage in the log file
383 ELSE
384 l_tot_qtr_aer := round((round(l_tot_emp_aer,2) * l_global_rate),2);
385 END IF;
386
387 -- Constructing a dynamic string to feed query to dbms_xmlquery which will generate XML output.
388 /* SQLSTR := 'SELECT '''|| TO_CHAR(NVL(FND_NUMBER.canonical_to_number(round(l_total_atp,2)),0) ,'999G999G990D99' ) || ''' as "TotalATP",'''
389 || TO_CHAR(NVL(FND_NUMBER.canonical_to_number(round(l_emp_count,2)),0) ,'999G999G990D99' ) || ''' as "FullTimeEmpCount",'''
390 || l_ded_1 ||''' as "Deduction1",'''
391 || l_ded_50 ||''' as "Deduction50",'''
392 || l_ded_trainee ||''' as "DeductionTrainee",'''
393 || TO_CHAR(NVL(FND_NUMBER.canonical_to_number(round(l_tot_emp_aer,2)),0) ,'999G999G990D99' ) ||''' as "TotalAER",'''
394 || TO_CHAR(NVL(FND_NUMBER.canonical_to_number(l_tot_qtr_aer),0) ,'999G999G990D99' ) ||''' as "TotalAERQuarter" from dual';
395 XMLIDENT := DBMS_XMLQUERY.NEWCONTEXT(SQLSTR);
396 DBMS_XMLQUERY.SETROWSETTAG (XMLIDENT, 'AERReport');
397 DBMS_XMLQUERY.SETROWTAG (XMLIDENT, 'Employee');
398 DBMS_XMLQUERY.GETXML(XMLIDENT, XMLRESULT);
399 DBMS_XMLQUERY.CLOSECONTEXT(XMLIDENT);
400 DBMS_LOB.ERASE(XMLRESULT, LV_OFFSET, 1 );
401 l_iana_charset := PAY_DK_GENERAL.get_IANA_charset();
402 LV_CLOB := '<?xml version="1.0" encoding="'||l_iana_charset||'"?> ' ;
403 DBMS_LOB.APPEND (LV_CLOB, XMLRESULT );
404 -- Assign the resulted XML into output variable
405 */
406 g_xml_element_table(l_xml_element_count).tagname := 'AERReport';
407 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
408 l_xml_element_count := l_xml_element_count + 1;
409 --
410 g_xml_element_table(l_xml_element_count).tagname := 'TotalATP';
411 g_xml_element_table(l_xml_element_count).tagvalue := TO_CHAR(NVL(FND_NUMBER.canonical_to_number(round(l_total_atp,2)),0) ,'999G999G990D99');
412 l_xml_element_count := l_xml_element_count + 1;
413 --
414 g_xml_element_table(l_xml_element_count).tagname := 'FullTimeEmpCount';
415 g_xml_element_table(l_xml_element_count).tagvalue := TO_CHAR(NVL(FND_NUMBER.canonical_to_number(round(l_emp_count,2)),0) ,'999G999G990D99');
416 l_xml_element_count := l_xml_element_count + 1;
417 --
418 g_xml_element_table(l_xml_element_count).tagname := 'Deduction1';
419 g_xml_element_table(l_xml_element_count).tagvalue := l_ded_1;
420 l_xml_element_count := l_xml_element_count + 1;
421 --
422 g_xml_element_table(l_xml_element_count).tagname := 'Deduction50';
423 g_xml_element_table(l_xml_element_count).tagvalue := l_ded_50;
424 l_xml_element_count := l_xml_element_count + 1;
425 --
426 g_xml_element_table(l_xml_element_count).tagname := 'DeductionTrainee';
427 g_xml_element_table(l_xml_element_count).tagvalue := l_ded_trainee;
428 l_xml_element_count := l_xml_element_count + 1;
429 --
430 g_xml_element_table(l_xml_element_count).tagname := 'TotalAER';
431 g_xml_element_table(l_xml_element_count).tagvalue := TO_CHAR(NVL(FND_NUMBER.canonical_to_number(round(l_tot_emp_aer,2)),0) ,'999G999G990D99' );
432 l_xml_element_count := l_xml_element_count + 1;
433 --
434 g_xml_element_table(l_xml_element_count).tagname := 'TotalAERQuarter';
435 g_xml_element_table(l_xml_element_count).tagvalue := TO_CHAR(NVL(FND_NUMBER.canonical_to_number(l_tot_qtr_aer),0) ,'999G999G990D99');
436 l_xml_element_count := l_xml_element_count + 1;
437 --
438 g_xml_element_table(l_xml_element_count).tagname := 'AERReport';
439 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
440 l_xml_element_count := l_xml_element_count + 1;
441 --
442 write_to_clob(P_XML);
443 --
444 -- DBMS_LOB.FREETEMPORARY(XMLRESULT);
445 -- DBMS_LOB.FREETEMPORARY(LV_CLOB);
446 --
447 END POPULATE_DETAILS;
448 --
449 END PAY_DK_EMP_TRAINEE_REIMBURSE;