[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_HOLIDAY_ENT_REPORT
Source
1 PACKAGE BODY PAY_NO_HOLIDAY_ENT_REPORT AS
2 /* $Header: pynohler.pkb 120.0.12000000.1 2007/05/22 07:34:24 rajesrin noship $ */
3 FUNCTION get_archive_payroll_action_id (p_payroll_action_id IN NUMBER)
4 RETURN NUMBER
5 IS
6 l_payroll_action_id NUMBER;
7 BEGIN
8 IF p_payroll_action_id IS NULL
9 THEN
10 BEGIN
11 SELECT payroll_action_id
12 INTO l_payroll_action_id
13 FROM pay_payroll_actions ppa,
14 fnd_conc_req_summary_v fcrs,
15 fnd_conc_req_summary_v fcrs1
16 WHERE fcrs.request_id = fnd_global.conc_request_id
20 AND ppa.request_id = fcrs1.request_id;
17 AND fcrs.priority_request_id = fcrs1.priority_request_id
18 AND ppa.request_id BETWEEN fcrs1.request_id
19 AND fcrs.request_id
21 EXCEPTION
22 WHEN OTHERS
23 THEN
24 NULL;
25 END;
26 ELSE
27 l_payroll_action_id := p_payroll_action_id;
28 END IF;
29 RETURN l_payroll_action_id;
30 END;
31 PROCEDURE get_data (
32 p_business_group_id in varchar2,
33 p_payroll_action_id IN VARCHAR2,
34 p_template_name IN VARCHAR2,
35 p_xml OUT NOCOPY CLOB
36 )
37 IS
38 /* Start of declaration*/
39 -- Variables needed for the report
40 l_sum NUMBER;
41 l_counter NUMBER := 1;
42 l_payroll_action_id pay_action_information.action_information1%TYPE;
43 l_legal_employer_id number;
44 l_legal_employer HR_ORGANIZATION_UNITS.name%type;
45 l_eff_date date;
46 CURSOR csr_legal_employer is select o.name,fnd_date.canonical_to_date(pai.action_information4) from
47 HR_ORGANIZATION_UNITS o,
48 HR_ORGANIZATION_INFORMATION hoi1,
49 pay_action_information pai
50 where pai.action_context_id = l_payroll_action_id
51 and pai.action_information_category ='EMEA REPORT DETAILS'
52 and o.organization_id = pai.action_information2
53 and hoi1.organization_id = o.organization_id
54 and hoi1.org_information_context = 'CLASS'
55 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER';
56
57
58 CURSOR csr_holiday_ent_data (csr_v_pa_id IN VARCHAR2)
59 IS
60 SELECT pai.assignment_id,
61 pai.action_information1,
62 pai.action_information2 assignment_number,
63 pai.action_information3 full_name,
64 pai.action_information4 holiday_ent_days,
65 pai.action_information5 holiday_ent_60_days,
66 pai.action_information6 holiday_carried_fwd_days,
67 pai.action_information7 holiday_taken_days,
68 pai.action_information8 holiday_taken_60_days,
69 pai.action_information9 holiday_remaining_days,
70 pai.action_information10 holiday_remaining60_days
71 FROM pay_action_information pai
72 ,pay_assignment_actions paa
73 WHERE pai.action_context_id = paa.assignment_action_id
74 AND paa.payroll_action_id = csr_v_pa_id
75 AND action_information_category ='EMEA REPORT INFORMATION'
76 AND action_information1 = 'PYNOHLEA';
77
78
79
80 BEGIN
81 l_payroll_action_id := get_archive_payroll_action_id (p_payroll_action_id);
82
83 open csr_legal_employer;
84 fetch csr_legal_employer into l_legal_employer,l_eff_date;
85 close csr_legal_employer;
86
87 gplsqltable (0).tagname := 'LEGAL_EMPLOYER';
88 gplsqltable (0).tagvalue := l_legal_employer;
89 gplsqltable (l_counter).tagname := 'EFFECTIVE_DATE';
90 gplsqltable (l_counter).tagvalue := to_char(l_eff_date,'DD.Mon.YYYY');
91 l_counter := l_counter+ 1;
92
93 FOR csr_holiday_ent_datas IN csr_holiday_ent_data (l_payroll_action_id)
94 LOOP
95
96 gplsqltable (l_counter).tagname := 'START';
97 gplsqltable (l_counter).tagvalue := 'START';
98 l_counter := l_counter+ 1;
99
100 gplsqltable (l_counter).tagname := 'FULL_NAME';
101 gplsqltable (l_counter).tagvalue := csr_holiday_ent_datas.full_name||' ['||csr_holiday_ent_datas.assignment_number||']';
102 l_counter := l_counter+ 1;
103
104 gplsqltable (l_counter).tagname := 'HOLIDAY_ENT_DAYS';
105 gplsqltable (l_counter).tagvalue := csr_holiday_ent_datas.holiday_ent_days;
106 l_counter := l_counter+ 1;
107
108 gplsqltable (l_counter).tagname := 'HOLIDAY_ENT_60_DAYS';
109 gplsqltable (l_counter).tagvalue := csr_holiday_ent_datas.holiday_ent_60_days;
110 l_counter := l_counter + 1;
111
112 gplsqltable (l_counter).tagname := 'HOLIDAY_CARRIED_FWD_DAYS';
113 gplsqltable (l_counter).tagvalue := csr_holiday_ent_datas.holiday_carried_fwd_days;
114 l_counter := l_counter + 1;
115
116 gplsqltable (l_counter).tagname := 'HOLIDAY_TAKEN_DAYS';
117 gplsqltable (l_counter).tagvalue := csr_holiday_ent_datas.holiday_taken_days;
118 l_counter := l_counter + 1;
119
120 gplsqltable (l_counter).tagname := 'HOLIDAY_TAKEN_60_DAYS';
121 gplsqltable (l_counter).tagvalue := csr_holiday_ent_datas.holiday_taken_60_days;
122 l_counter := l_counter + 1;
123
124 gplsqltable (l_counter).tagname := 'HOLIDAY_REMAINING_DAYS';
125 gplsqltable (l_counter).tagvalue := csr_holiday_ent_datas.holiday_remaining_days;
126 l_counter := l_counter + 1;
127
128 gplsqltable (l_counter).tagname := 'HOLIDAY_REMAINING60_DAYS';
129 gplsqltable (l_counter).tagvalue := csr_holiday_ent_datas.holiday_remaining60_days;
130 l_counter := l_counter + 1;
131
132 gplsqltable (l_counter).tagname := 'END';
133 gplsqltable (l_counter).tagvalue := 'END';
134 l_counter := l_counter + 1;
135
136 END LOOP;
137
138 writetoclob (p_xml);
139
140 END get_data;
141 -----------------------------------------------------------------------------------------------------------------
142 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
143 IS
144 l_xfdf_string CLOB;
145 l_str1 VARCHAR2 (1000);
146 l_str2 VARCHAR2 (20);
147 l_str3 VARCHAR2 (20);
148 l_str4 VARCHAR2 (20);
149 l_str5 VARCHAR2 (20);
150 l_str6 VARCHAR2 (30);
151 l_str7 VARCHAR2 (1000);
152 l_str8 VARCHAR2 (240);
153 l_str9 VARCHAR2 (240);
154 l_str10 VARCHAR2 (20);
155 l_str11 VARCHAR2 (20);
156 current_index PLS_INTEGER;
157 l_counter PLS_INTEGER;
158 l_IANA_charset VARCHAR2 (50);
159 BEGIN
160 l_IANA_charset :=hr_no_utility.get_IANA_charset ;
161 hr_utility.set_location ('Entering WritetoCLOB ', 70);
162 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT>';
163 l_str2 := '<';
164 l_str3 := '>';
165 l_str4 := '</';
166 l_str5 := '>';
167 l_str6 := '</ROOT>';
168 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
169 l_str10 := '<HOLIDAY>';
170 l_str11 := '</HOLIDAY>';
171 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
172 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
173 current_index := 0;
174 IF gplsqltable.COUNT > 0
175 THEN
176 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
177 FOR table_counter IN gplsqltable.FIRST .. gplsqltable.LAST
178 LOOP
179 l_str8 := gplsqltable (table_counter).tagname;
180 l_str9 := gplsqltable (table_counter).tagvalue;
181 IF l_str9 = 'END'
182 THEN
183 DBMS_LOB.writeappend (
184 l_xfdf_string,
185 LENGTH (l_str11),
186 l_str11
187 );
188 ELSIF l_str9 = 'START'
189 THEN
190 DBMS_LOB.writeappend (
191 l_xfdf_string,
192 LENGTH (l_str10),
193 l_str10
194 );
195 ELSIF l_str9 IS NOT NULL
196 THEN
197 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
198 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
199 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
200 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9), l_str9);
201 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
202 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
203 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
204 ELSE
205 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
206 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
207 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
208 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
209 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
210 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
211 END IF;
212 END LOOP;
213 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
214 ELSE
215 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
216 END IF;
217 p_xfdf_clob := l_xfdf_string;
218
219 EXCEPTION
220 WHEN OTHERS
221 THEN
222 hr_utility.TRACE ( 'sqlerrm '
223 || SQLERRM);
224 hr_utility.raise_error;
225 END writetoclob;
226
227 END PAY_NO_HOLIDAY_ENT_REPORT;