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

Source


1 PACKAGE BODY pay_nl_retro_setup_report AS
2 /* $Header: pynlersr.pkb 120.1 2008/02/19 11:57:33 abhgangu noship $ */
3 
4 -------------------------------------------------------------------------------
5 -- get_IANA_charset
6 -------------------------------------------------------------------------------
7 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
8     CURSOR csr_get_iana_charset IS
9         SELECT tag
10           FROM fnd_lookup_values
11          WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
12            AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
13                                     INSTR(USERENV('LANGUAGE'), '.') + 1)
14            AND language = 'US';
15 
16     lv_iana_charset fnd_lookup_values.tag%type;
17 BEGIN
18     OPEN csr_get_iana_charset;
19         FETCH csr_get_iana_charset INTO lv_iana_charset;
20     CLOSE csr_get_iana_charset;
21 
22     hr_utility.trace('IANA Charset = '||lv_iana_charset);
23     RETURN (lv_iana_charset);
24 END get_IANA_charset;
25 
26 -------------------------------------------------------------------------------
27 -- Procedure to Generate XML Data
28 -------------------------------------------------------------------------------
29 
30 PROCEDURE generate
31 	 		( 	p_business_group_id IN NUMBER,
32 	 			p_eff_date IN VARCHAR2,
33 	 			p_ele_records IN VARCHAR2,
34 	 			p_template_name IN VARCHAR2,
35 	 			p_xml OUT NOCOPY CLOB
36 	 		)
37 	 		IS
38 
39 -- Cursor to get classifications of the elements.
40 CURSOR csr_get_ele_class(c_bg_id NUMBER,c_ele_rec VARCHAR2) IS
41 
42 SELECT DISTINCT
43 		pec.classification_id,
44 		pec1.classification_name
45 FROM
46 		pay_element_classifications pec ,
47 		pay_element_classifications_tl pec1 ,
48 		pay_element_types_f pat
49 WHERE
50 		pec.legislation_code = 'NL'
51 AND  		pec.parent_classification_id IS NULL
52 AND		pec1.language = USERENV('LANG') -- Bug ref. 5837256
53 AND 		pat.classification_id = pec.classification_id
54 AND 		pat.business_group_id = c_bg_id
55 AND 		pec1.classification_id = pat.classification_id
56 AND		(EXISTS (SELECT NULL FROM pay_retro_component_usages prcu WHERE
57 			    pat.element_type_id = prcu.creator_id
58 		AND         prcu.creator_type   = 'ET') OR  c_ele_rec = 'A');
59 
60 -- Cursor for getting element retro setup records
61 CURSOR csr_get_element_record(c_bg_id NUMBER,c_eff_date DATE, c_class_id NUMBER) IS
62 
63 SELECT
64             pat.element_name,
65             prc.component_name,
66             hr_general.decode_lookup('HR_NL_YES_NO',NVL(prcu.default_component,'N')) default_component,
67             hr_general.decode_lookup('RETRO_REPROCESS_TYPE',prcu.reprocess_type) reprocess_type,
68             hr_general.decode_lookup('HR_NL_YES_NO',NVL(prcu.replace_run_flag,'N')) replace_run_flag,
69             hr_general.decode_lookup('HR_NL_YES_NO',NVL(prcu.use_override_dates,'N')) use_override_dates,
70             pat1.element_name  Retro_Element,
71             ptd1.definition_name Time_from ,
72             ptd2.definition_name  Time_To ,
73             pec.classification_name
74 
75 FROM
76             pay_element_types_f pat,
77             pay_retro_component_usages prcu,
78             pay_element_span_usages pesu,
79             pay_time_spans pts,
80             pay_time_definitions ptd1,
81             pay_time_definitions ptd2,
82             pay_element_types_f pat1,
83             pay_retro_components prc,
84             pay_element_classifications_tl pec
85 
86 WHERE
87             pat.business_group_id = c_bg_id
88 AND	    c_eff_date between pat.effective_start_date AND pat.effective_end_date
89 AND	    pat.classification_id = c_class_id
90 AND         pat.element_type_id = prcu.creator_id
91 AND         prcu.creator_type   = 'ET'
92 AND		pec.language = USERENV('LANG') -- Bug ref. 5837256
93 AND         prcu.retro_component_usage_id = pesu.retro_component_usage_id(+)
94 AND         pesu.time_span_id = pts.time_span_id(+)
95 AND         pts.start_time_def_id = ptd1.time_definition_id(+)
96 AND         pts.end_time_def_id  = ptd2.time_definition_id(+)
97 AND         pesu.retro_element_type_id = pat1.element_type_id(+)
98 AND         prcu.retro_component_id = prc.retro_component_id
99 AND 	    pat1.classification_id = pec.classification_id(+)
100 ORDER BY    pat.element_name, PRC.component_name	;
101 
102 -- Cursor for getting elements without retro components
103 
104 CURSOR csr_get_ele_without_ret(c_bg_id NUMBER,c_eff_date DATE, c_class_id NUMBER) IS
105 SELECT DISTINCT
106 	    pat.element_name
107 FROM
108             pay_element_types_f pat,
109             pay_retro_component_usages prcu
110 WHERE
111             pat.business_group_id = c_bg_id
112 AND	    c_eff_date between pat.effective_start_date AND pat.effective_end_date
113 AND	    pat.classification_id = c_class_id
114 AND         pat.element_type_id = prcu.creator_id (+)
115 AND         prcu.creator_type (+)  = 'ET'
116 AND	    prcu.creator_id IS NULL;
117 
118 
119 
120 -- Getting BG name
121 CURSOR csr_bg_name(c_bg_id per_business_groups.name%TYPE, c_eff_date DATE) IS
122 
123 SELECT name FROM per_business_groups
124 WHERE business_group_id = c_bg_id
125 AND   c_eff_date BETWEEN date_from AND NVL(date_to,hr_general.end_of_time);
126 
127 -- Local Variables
128 vCtr NUMBER := 0;
129 v_get_ele_class csr_get_ele_class%ROWTYPE;
130 v_get_ele_without_ret csr_get_ele_without_ret%ROWTYPE;
131 v_get_element_record csr_get_element_record%ROWTYPE;
132 l_element_name pay_element_types_f.element_name%TYPE := ' ';
133 l_show_element pay_element_types_f.element_name%TYPE := ' ';
134 l_component pay_retro_components.component_name%TYPE := ' ';
135 l_show_component pay_retro_components.component_name%TYPE := ' ';
136 l_default_comp pay_retro_component_usages.default_component%TYPE := ' ';
137 l_replace_run pay_retro_component_usages.replace_run_flag%TYPE := ' ';
138 l_override_dates pay_retro_component_usages.use_override_dates%TYPE := ' ';
139 l_reprocess_type pay_retro_component_usages.reprocess_type%TYPE := ' ';
140 l_bg_name csr_bg_name%ROWTYPE;
141 l_str VARCHAR2(7500);
142 l_str1 varchar2(1000);
143 l_str2 varchar2(20);
144 l_str3 varchar2(20);
145 l_str4 varchar2(20);
146 l_str5 varchar2(20);
147 l_str6 varchar2(30);
148 l_str7 varchar2(1000);
149 l_str8 varchar2(1000);
150 l_str9 varchar2(1000);
151 l_str10 varchar2(1000);
152 l_str11 varchar2(100);
153 l_effec_date DATE;
154 l_xml CLOB;
155 
156 
157 BEGIN
158 
159 --hr_utility.trace_on(NULL,'ERSR');
160 hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : Parameters',100);
161 hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : p_eff_date'||p_eff_date,140);
162 hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : p_ele_records'||p_ele_records,160);
163 hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : p_business_group_id'||p_business_group_id,180);
164 
165 
166 l_effec_date := fnd_date.canonical_to_date(p_eff_date);
167 
168 OPEN csr_bg_name(p_business_group_id,l_effec_date);
169 FETCH csr_bg_name INTO l_bg_name;
170 CLOSE csr_bg_name;
171 
172 -- Setting PL/SQL table for tags and values of data be reported once.
173 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EFF_DATE';
174 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_effec_date);
175 vCtr := vCtr + 1;
176 
177 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'BG_NAME';
178 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_bg_name.name;
179 vCtr := vCtr + 1;
180 
181 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ELE_RECORDS';
182 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := hr_general.decode_lookup('HR_NL_ESR_ELE_REC',p_ele_records);
183 vCtr := vCtr + 1;
184 
185 hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : <EFF_DATE>'||to_char(l_effec_date),600);
186 hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : <BG_NAME>'||l_bg_name.name,620);
187 hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : <ELE_RECORDS>'||hr_general.decode_lookup('HR_NL_ESR_ELE_REC',p_ele_records),640);
188 
189 -- Setting PL/SQL table for tags and values of the xml data for all the element records
190 
191 FOR v_get_ele_class IN csr_get_ele_class(p_business_group_id,p_ele_records)
192 LOOP
193 
194 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_CLASS';
195 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
196 	vCtr := vCtr + 1;
197 
198 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ELE_CLASS';
199 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_get_ele_class.classification_name ;
200 	vCtr := vCtr + 1;
201 
202 	hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : <G_CONTAINER_CLASS>',660);
203 	hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : <ELE_CLASS>'||NVL(v_get_ele_class.classification_name,'NULL'),680);
204 	FOR v_get_element_record
205 	IN csr_get_element_record(p_business_group_id,l_effec_date,v_get_ele_class.classification_id)
206 	LOOP
207 		-- Donot repeat the element name if same.
208 		IF l_element_name <> v_get_element_record.element_name THEN
209 			l_element_name := v_get_element_record.element_name;
210 			l_show_element := v_get_element_record.element_name;
211 			l_show_component := v_get_element_record.component_name;
212 			l_component := v_get_element_record.component_name;
213 			l_default_comp := v_get_element_record.default_component;
214 			l_replace_run := v_get_element_record.replace_run_flag;
215 			l_override_dates := v_get_element_record.use_override_dates ;
216 			l_reprocess_type := v_get_element_record.reprocess_type;
217 		ELSE
218 			l_show_element := ' ';
219 			-- Donot repeat the component data if same
220 			IF l_component <> v_get_element_record.component_name THEN
221 				l_component := v_get_element_record.component_name;
222 				l_show_component := v_get_element_record.component_name;
223 				l_default_comp := v_get_element_record.default_component;
224 				l_replace_run := v_get_element_record.replace_run_flag;
225 				l_override_dates := v_get_element_record.use_override_dates ;
226 				l_reprocess_type := v_get_element_record.reprocess_type;
227 			ELSE
228 				l_show_component := ' ';
229 				l_default_comp := ' ';
230 				l_replace_run := ' ';
231 				l_override_dates := ' ';
232 				l_reprocess_type := ' ';
233 
234 			END IF;
235 		END IF;
236 
237 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_ELEMENT';
238 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
239 		vCtr := vCtr + 1;
240 
241 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ELEMENT_NAME';
242 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_show_element ;
243 		vCtr := vCtr + 1;
244 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'COMPONENT';
245 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_show_component;
246 		vCtr := vCtr + 1;
247 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REPROCESS_TYPE';
248 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_reprocess_type;
249 		vCtr := vCtr + 1;
250 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'DEFAULT_COMP';
251 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_default_comp;
252 		vCtr := vCtr + 1;
253 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REPLACE_RUN';
254 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_replace_run;
255 		vCtr := vCtr + 1;
256 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'OVERRIDE_DATE';
257 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_override_dates;
258 		vCtr := vCtr + 1;
259 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TIME_FROM';
260 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_get_element_record.Time_From;
261 		vCtr := vCtr + 1;
262 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TIME_TO';
263 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_get_element_record.Time_To;
264 		vCtr := vCtr + 1;
265 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'RETRO_ELEMENT';
266 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_get_element_record.Retro_Element;
267 		vCtr := vCtr + 1;
268 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'RETRO_ELE_CLASS';
269 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_get_element_record.classification_name;
270 		vCtr := vCtr + 1;
271 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_ELEMENT';
272 		PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
273 		vCtr := vCtr + 1;
274 
275 		hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : <G_CONTAINER_ELEMENT>',700);
276 		hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : <ELEMENT_NAME>'||NVL(v_get_element_record.element_name,'NULL'),720);
277 
278 	END LOOP;
279 
280 	IF p_ele_records = 'A' THEN
281 		FOR v_get_ele_without_ret
282 		IN csr_get_ele_without_ret(p_business_group_id,l_effec_date,v_get_ele_class.classification_id)
283 		LOOP
284 			PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_ELEMENT';
285 			PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
286 			vCtr := vCtr + 1;
287 
288 			PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ELEMENT_NAME';
289 			PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_get_ele_without_ret.element_name ;
290 			vCtr := vCtr + 1;
291 
292 			PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_ELEMENT';
293 			PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
294 			vCtr := vCtr + 1;
295 
296 			hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : <G_CONTAINER_ELEMENT>',740);
297 			hr_utility.set_location('Inside pay_nl_retro_setup_report.generate : <ELEMENT_NAME>'||NVL(v_get_ele_without_ret.element_name,'NULL'),760);
298 		END LOOP;
299 	END IF;
300 
301 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_CLASS';
302 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
303 	vCtr := vCtr + 1;
304 
305 END LOOP;
306 
307 -- Generate XML data using the PL/SQL table.
308 hr_utility.set_location('Entered Procedure Write to clob ',100);
309 	l_str1 := '<fields>' ;
310 	l_str2 := '<';
311 	l_str3 := '>';
312 	l_str4 := '<value>' ;
313 	l_str5 := '</value> </' ;
314 	l_str6 := '</fields>';
315 	l_str7 := '<fields></fields>';
316 	l_str10 := '</';
317 	l_str11 := '<?xml version="1.0" encoding="' || get_IANA_charset ||'"?>';
318 	dbms_lob.createtemporary(l_xml,FALSE,DBMS_LOB.CALL);
319 
320 	dbms_lob.open(l_xml,dbms_lob.lob_readwrite);
321 	dbms_lob.writeAppend( l_xml, length(l_str11), l_str11 );
322 
323 	if PAY_NL_XDO_REPORT.vXMLTable.count > 0 then
324 		dbms_lob.writeAppend( l_xml, length(l_str1), l_str1 );
325                 hr_utility.set_location('Before Procedure Write to clob: before loop',102);
326         	FOR ctr_table IN PAY_NL_XDO_REPORT.vXMLTable.FIRST .. PAY_NL_XDO_REPORT.vXMLTable.LAST LOOP
327         		hr_utility.set_location('Before Procedure Write to clob: Inside loop'||PAY_NL_XDO_REPORT.vXMLTable(ctr_table).TagName,104);
328         		l_str8 := PAY_NL_XDO_REPORT.vXMLTable(ctr_table).TagName;
329         		l_str9 := PAY_NL_XDO_REPORT.vXMLTable(ctr_table).TagValue;
330         		if (substr(l_str8,1,11) = 'G_CONTAINER') then
331         		        if (l_str9 is null) then
332 					l_str :=  l_str2||l_str8||l_str3;
333 
334 					/*dbms_lob.writeAppend( l_xml, length(l_str2), l_str2 );
335 					dbms_lob.writeAppend( l_xml, length(l_str8),l_str8);
336 					dbms_lob.writeAppend( l_xml, length(l_str3), l_str3 ); 	*/
337 				else
338 				if (l_str9 = 'END') then
339 					 l_str :=  l_str10||l_str8||l_str3;
340 					/* dbms_lob.writeAppend( l_xml, length(l_str10), l_str10 );
341 					 dbms_lob.writeAppend( l_xml, length(l_str8),l_str8);
342 					 dbms_lob.writeAppend( l_xml, length(l_str3), l_str3 ); */
343 				end if;
344 				end if;
345 		        else
346         		if (l_str9 is not null) then
347 
348         		l_str :=  l_str2||l_str8||l_str3||l_str4||l_str9||l_str5||l_str8||l_str3;
349 			/*	dbms_lob.writeAppend( l_xml, length(l_str2), l_str2 );
350 				dbms_lob.writeAppend( l_xml, length(l_str8),l_str8);
351 				dbms_lob.writeAppend( l_xml, length(l_str3), l_str3 );
352 				dbms_lob.writeAppend( l_xml, length(l_str4), l_str4 );
353 				dbms_lob.writeAppend( l_xml, length(l_str9), l_str9);
354 				dbms_lob.writeAppend( l_xml, length(l_str5), l_str5 );
355 				dbms_lob.writeAppend( l_xml, length(l_str8),l_str8);
356 				dbms_lob.writeAppend( l_xml, length(l_str3),l_str3); */
357 			elsif (l_str9 is null and l_str8 is not null) then
358 
359 			l_str :=  l_str2||l_str8||l_str3||l_str4||l_str5||l_str8||l_str3;
360 			/*	dbms_lob.writeAppend(l_xml,length(l_str2),l_str2);
361 				dbms_lob.writeAppend(l_xml,length(l_str8),l_str8);
362 				dbms_lob.writeAppend(l_xml,length(l_str3),l_str3);
363 				dbms_lob.writeAppend(l_xml,length(l_str4),l_str4);
364 				dbms_lob.writeAppend(l_xml,length(l_str5),l_str5);
365 				dbms_lob.writeAppend( l_xml, length(l_str8),l_str8);
366 				dbms_lob.writeAppend( l_xml, length(l_str3),l_str3); */
367 			else
368 			null;
369 			end if;
370 			end if;
371 			dbms_lob.writeAppend( l_xml, length(l_str),l_str);
372 			l_str := '';
373 
374 		END LOOP;
375 		dbms_lob.writeAppend( l_xml, length(l_str6), l_str6 );
376 	else
377 		dbms_lob.writeAppend( l_xml, length(l_str7), l_str7 );
378 	end if;
379 --set return output variable to CLOB xml file
380 p_xml := l_xml;
381 
382 /*begin
383 insert into my_table15 values(l_xml);
384 end;*/
385 EXCEPTION
386 	WHEN OTHERS then
387 	HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
388 	HR_UTILITY.RAISE_ERROR;
389 
390 END generate;
391 
392 END pay_nl_retro_setup_report;