[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_ACRR
Source
1 PACKAGE BODY PAY_FI_ACRR AS
2 /* $Header: pyfiacrr.pkb 120.5 2006/04/03 05:44:25 dbehera 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
17 AND fcrs.priority_request_id = fcrs1.priority_request_id
18 AND ppa.request_id BETWEEN fcrs1.request_id
19 AND fcrs.request_id
20 AND ppa.request_id = fcrs1.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 CURSOR csr_Element_data (csr_v_pa_id IN VARCHAR2)
44 IS
45 SELECT pai.action_information2 Legal_Employer,
46 pai.action_information10 Start_Date,
47 pai.action_information11 End_Date,
48 pai2.action_information4 Local_Unit,
49 pai1.action_information5 ||'-' || pai2.action_information6 Company_Number,
50 pai4.action_information4 Element_name,
51 pai4.action_information6 Account_number,
52 nvl(pai4.action_information7,0) Credit,
53 nvl(pai4.action_information8,0) Debit
54 FROM pay_action_information pai,
55 pay_payroll_actions ppa,
56 pay_action_information pai1,
57 pay_action_information pai2,
58 pay_action_information pai4
59 WHERE pai.action_context_id = ppa.payroll_action_id
60 AND ppa.payroll_action_id = csr_v_pa_id
61 AND pai.action_context_id = pai1.action_context_id
62 AND pai1.action_context_id= pai2.action_context_id
63 AND pai2.action_context_id=pai4.action_context_id
64 AND pai4.action_context_id=pai.action_context_id
65 AND pai1.action_information3=pai2.action_information5
66 AND pai2.action_information3=pai4.action_information5
67 AND pai1.action_context_type='PA'
68 AND pai1.action_information2 = 'LE'
69 AND pai1.action_information1 = 'PYFIACRA'
70 AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
71 AND pai.action_context_type = 'PA'
72 AND pai.action_information1 = 'PYFIACRA'
73 AND pai.action_information_category = 'EMEA REPORT DETAILS'
74 AND pai2.action_context_type = 'PA'
75 AND pai2.action_information1 = 'PYFIACRA'
76 AND pai2.action_information_category = 'EMEA REPORT INFORMATION'
77 AND pai2.action_information2 = 'LU'
78 AND pai4.action_context_type = 'PA'
79 AND pai4.action_information1 = 'PYFIACRA'
80 AND pai4.action_information_category = 'EMEA REPORT INFORMATION'
81 AND pai4.action_information2 = 'EL';
82
83 element_data csr_Element_data%ROWTYPE;
84 BEGIN
85 l_payroll_action_id :=
86 get_archive_payroll_action_id (p_payroll_action_id);
87 FOR element_data IN csr_Element_data (l_payroll_action_id)
88 LOOP
89 gplsqltable (l_counter).tagname := 'START';
90 gplsqltable (l_counter).tagvalue := 'START';
91 l_counter := l_counter
92 + 1;
93 gplsqltable (l_counter).tagname := 'LEGAL_EMPLOYER';
94 gplsqltable (l_counter).tagvalue :=
95 TO_CHAR (element_data.Legal_Employer);
96 l_counter := l_counter
97 + 1;
98 gplsqltable (l_counter).tagname := 'START_DATE';
99 gplsqltable (l_counter).tagvalue :=
100 (element_data.Start_Date);
101 l_counter := l_counter
102 + 1;
103 gplsqltable (l_counter).tagname := 'END_DATE';
104 gplsqltable (l_counter).tagvalue :=
105 (element_data.End_Date);
106 l_counter := l_counter
107 + 1;
108 gplsqltable (l_counter).tagname := 'CURRENCY';
109 gplsqltable (l_counter).tagvalue := TO_CHAR('EUR');
110 l_counter := l_counter
111 + 1;
112 gplsqltable (l_counter).tagname := 'LOCAL_UNIT';
113 gplsqltable (l_counter).tagvalue := TO_CHAR (element_data.Local_Unit);
114 l_counter := l_counter
115 + 1;
116 gplsqltable (l_counter).tagname := 'COMPANY_NUMBER';
117 gplsqltable (l_counter).tagvalue :=
118 TO_CHAR (element_data.Company_Number);
119 l_counter := l_counter
120 + 1;
121 gplsqltable (l_counter).tagname := 'ELEMENT_NAME';
122 gplsqltable (l_counter).tagvalue := TO_CHAR (element_data.Element_Name);
123 l_counter := l_counter
124 + 1;
125 gplsqltable (l_counter).tagname := 'ACCOUNT_NUMBER';
126 gplsqltable (l_counter).tagvalue :=
127 TO_CHAR (element_data.Account_Number);
128 l_counter := l_counter
129 + 1;
130 gplsqltable (l_counter).tagname := 'CREDIT';
131 gplsqltable (l_counter).tagvalue :=TO_CHAR(NVL(FND_NUMBER.canonical_to_number
132 (element_data.Credit),0) ,'999G999G990D99' );
133 l_counter := l_counter
134 + 1;
135 gplsqltable (l_counter).tagname := 'DEBIT';
136 gplsqltable (l_counter).tagvalue :=
137 TO_CHAR(NVL(FND_NUMBER.canonical_to_number
138 (element_data.Debit),0) ,'999G999G990D99' );
139 l_counter := l_counter
140 + 1;
141 gplsqltable (l_counter).tagname := 'END';
142 gplsqltable (l_counter).tagvalue := 'END';
143 l_counter := l_counter
144 + 1;
145 END LOOP;
146 writetoclob (p_xml);
147 COMMIT;
148 END get_data;
149 -----------------------------------------------------------------------------------------------------------------
150 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
151 IS
152 l_xfdf_string CLOB;
153 l_str1 VARCHAR2 (1000);
154 l_str2 VARCHAR2 (20);
155 l_str3 VARCHAR2 (20);
156 l_str4 VARCHAR2 (20);
157 l_str5 VARCHAR2 (20);
158 l_str6 VARCHAR2 (30);
159 l_str7 VARCHAR2 (1000);
160 l_str8 VARCHAR2 (240);
161 l_str9 VARCHAR2 (240);
162 l_str10 VARCHAR2 (20);
163 l_str11 VARCHAR2 (20);
164 current_index PLS_INTEGER;
165 l_counter PLS_INTEGER;
166 l_IANA_charset VARCHAR2 (50);
167 BEGIN
168 l_IANA_charset :=hr_fi_utility.get_IANA_charset ;
169 hr_utility.set_location ('Entering WritetoCLOB ', 70);
170 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT>';
171 l_str2 := '<';
172 l_str3 := '>';
173 l_str4 := '</';
174 l_str5 := '>';
175 l_str6 := '</ROOT>';
176 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
180 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
177 l_str10 := '<ACCR>';
178 l_str11 := '</ACCR>';
179 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
181 current_index := 0;
182 IF gplsqltable.COUNT > 0
183 THEN
184 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
185 FOR table_counter IN gplsqltable.FIRST .. gplsqltable.LAST
186 LOOP
187 l_str8 := gplsqltable (table_counter).tagname;
188 l_str9 := gplsqltable (table_counter).tagvalue;
189 IF l_str9 = 'END'
190 THEN
191 DBMS_LOB.writeappend (
192 l_xfdf_string,
193 LENGTH (l_str11),
194 l_str11
195 );
196 ELSIF l_str9 = 'START'
197 THEN
198 DBMS_LOB.writeappend (
199 l_xfdf_string,
200 LENGTH (l_str10),
201 l_str10
202 );
203 ELSIF l_str9 IS NOT NULL
204 THEN
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_str9), l_str9);
209 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
210 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
211 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
212 ELSE
213 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
214 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
215 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
216 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
217 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
218 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
219 END IF;
220 END LOOP;
221 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
222 ELSE
223 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
224 END IF;
225 p_xfdf_clob := l_xfdf_string;
226 COMMIT;
227 EXCEPTION
228 WHEN OTHERS
229 THEN
230 hr_utility.TRACE ( 'sqlerrm '
231 || SQLERRM);
232 hr_utility.raise_error;
233 END writetoclob;
234 -------------------------------------------------------------------------------------------------------------------------
235 END PAY_FI_ACRR;