1 PACKAGE PAY_PAYROLL_XML_EXTRACT_PKG as
2 /* $Header: pyxmlxtr.pkh 120.6.12010000.2 2008/08/22 10:30:51 ckesanap ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2004, Oracle India Pvt. Ltd., Hyderabad *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_payroll_xml_extract_pkg
21
22 Description : This package handles generation of XML from data archived
23 in pay_action_information. Calling applications can invoke
24 one of the overloaded versions of GENERATE procedure with
25 appropriate parameters to obtain the XML. This package has
26 other public procedures which GENERATE uses for processing.
27 They might not be of much use if invoked directly by calling
28 applications.
29
30 Change List
31 -----------
32 Date Name Vers Bug No Description
33 ----------- ---------- ------ ------- -------------------------------
34 23-NOV-2004 sdahiya 115.0 Created.
35 22-DEC-2004 sdahiya 115.1 Modified csr_get_archived_info to
36 retrieve records archived at payroll
37 action level too.
38 20-FEB-2005 sdahiya 115.2 Modified parameters of GENERATE
39 procedure.
40 11-JUL-2005 sdahiya 115.3 Added overloaded versions of
41 GENERATE procedure so that it can be
42 driven off action_information_id
43 too.
44 15-JUL-2005 sdahiya 115.4 Modified signature of GENERATE
45 overloaded procedure to handle
46 custom XML tags.
47 01-AUG-2005 sdahiya 115.5 Added support for localization
48 package and removed
49 DocumentProcessor XML tags.
50 18-AUG-2005 sdahiya 115.6 Added LOAD_XML_DATA procedure and
51 global variable g_xml_table.
52 20-NOV-2005 vmehta 115.7 Added overloaded version of LOAD_XML
53 which accepts flexfield name.
54 21-NOV-2005 sdahiya 115.8 4773967 Modified procedure signatures to
55 return XML as BLOB instead of CLOB.
56 21-Aug-2008 jalin 115.9 6522667 Fixed performance issue, changed to
57 use UNION_ALL function in csr_get_
58 archived_info cursor
59 *****************************************************************************/
60
61 TYPE int_tab_type IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
62 TYPE char_tab_type IS TABLE OF pay_action_information.action_information1%type
63 INDEX BY BINARY_INTEGER;
64 TYPE param_rec IS RECORD (
65 parameter_name varchar2(50),
66 parameter_value varchar2(500)
67 );
68 TYPE param_tab_type IS TABLE OF param_rec INDEX BY BINARY_INTEGER;
69
70 /****************************************************************************
71 Name : LOAD_XML
72 Description : This procedure loads the global XML cache.
73 Parameters : P_NODE_TYPE This parameter can take one of these values: -
74 1. CS - This signifies that string contained in
75 P_NODE parameter is start of container
76 node. P_DATA parameter is ignored in this
77 mode.
78 2. CE - This signifies that string contained in
79 P_NODE parameter is end of container
80 node. P_DATA parameter is ignored in this
81 mode.
82 3. D - This signifies that string contained in
83 P_NODE parameter is data node and P_DATA
84 carries actual data to be contained by
85 tag specified by P_NODE parameter.
86
87 P_CONTEXT_CODE Context code of descriptive flexfield.
88
89 P_NODE Name of XML tag, or, application column name of flex segment.
90
91 P_DATA Data to be contained by tag specified by P_NODE parameter.
92 P_DATA is not used unless P_NODE_TYPE = D.
93 *****************************************************************************/
94 PROCEDURE LOAD_XML (
95 P_NODE_TYPE varchar2,
96 P_FLEXFIELD_NAME varchar2,
97 P_CONTEXT_CODE varchar2,
98 P_NODE varchar2,
99 P_DATA varchar2
100 );
101
102
103 /****************************************************************************
104 Name : LOAD_XML
105 Description : This procedure obtains segment titles from the Action
106 Information DF. This is temporary, and is created only to
107 provide backward compatibility for payslip code. Once the
108 payslip processes are changed to pass the flexfield name,
109 this procedure can be removed.
110 *****************************************************************************/
111 PROCEDURE LOAD_XML (
112 P_NODE_TYPE varchar2,
113 P_CONTEXT_CODE varchar2,
114 P_NODE varchar2,
115 P_DATA varchar2
116 );
117
118 /****************************************************************************
119 Name : LOAD_XML
120 Description : This procedure obtains segment title from the bank key
121 flexfield to be used as XML tag.
122 *****************************************************************************/
123 PROCEDURE LOAD_XML (
124 P_NODE_TYPE varchar2,
125 P_NODE varchar2,
126 P_DATA varchar2
127 );
128
129
130 /****************************************************************************
131 Name : LOAD_XML
132 Description : This procedure accepts a well-formed XML and loads it into
133 global XML cache. Note that this procedure does not perform
134 any syntactical validations over passed XML data.
135 LOAD_XML_DATA should be used if such validations are required
136 to be performed implicitly.
137 *****************************************************************************/
138 PROCEDURE LOAD_XML (
139 P_XML pay_action_information.action_information1%type
140 );
141
142
143 /****************************************************************************
144 Name : LOAD_XML_DATA
145 Description : This procedure accepts meta-data along with actual XML data
146 and loads the global XML cache. This is a public procedure
147 which performs basic validations to check well-formedness of
148 XML data before loading the cache. Please see parameter
149 description of public version of LOAD_XML to find what each
150 parameter signifies.
151 *****************************************************************************/
152 PROCEDURE LOAD_XML_DATA (
153 P_NODE_TYPE varchar2,
154 P_NODE varchar2,
155 P_DATA varchar2
156 );
157
158 /****************************************************************************
159 Name : GENERATE
160 Description : This procedure interprets archived information, converts it to
161 XML and prints it out to a BLOB. This is a public procedure
162 and is based on action_context_IDs passed by the calling
163 process. All archived records belonging to passed
164 action_context_id will be converted to XML. Currently, online
165 payslip and MX Pay Advice invoke this overloaded version.
166 *****************************************************************************/
167 PROCEDURE GENERATE (
168 P_ACTION_CONTEXT_ID number,
169 P_CUSTOM_XML_PROCEDURE varchar2,
170 P_GENERATE_HEADER_FLAG varchar2, -- {Y/N}
171 P_ROOT_TAG varchar2,
172 P_DOCUMENT_TYPE varchar2,
173 P_XML OUT NOCOPY BLOB
174 );
175
176
177 /****************************************************************************
178 Name : GENERATE
179 Description : This procedure interprets archived information, converts it to
180 XML and prints it out to a BLOB. This is a public procedure
181 and is driven off action_information_IDs set by the calling
182 process.
183
184 It also accepts a custom XML tag parameter, which if passed a
185 non-null value, will be used as parent enclosing tag of each
186 action_information_id irrespective of the actual action
187 information category.
188
192 PROCEDURE GENERATE (
189 Currently, MX SUA process invokes this overloaded version.
190 *****************************************************************************/
191
193 P_ACTION_INF_ID_TAB int_tab_type,
194 P_CUSTOM_ACTION_INFO_CAT varchar2,
195 P_DOCUMENT_TYPE varchar2,
196 P_XML OUT NOCOPY BLOB
197 );
198
199 /*---- Global declarations ---*/
200 g_leg_code fnd_territories.territory_code%type;
201 g_xml_table char_tab_type;
202 g_custom_xml char_tab_type;
203 g_custom_params param_tab_type;
204
205 CURSOR csr_get_archived_info (p_action_context_id number,
206 p_category varchar2,
207 p_category_filter varchar2,
208 p_action_information_id number) IS
209 SELECT effective_date,
210 action_information1,
211 action_information2,
212 action_information3,
213 action_information4,
214 action_information5,
215 action_information6,
216 action_information7,
217 action_information8,
218 action_information9,
219 action_information10,
220 action_information11,
221 action_information12,
222 action_information13,
223 action_information14,
224 action_information15,
225 action_information16,
226 action_information17,
227 action_information18,
228 action_information19,
229 action_information20,
230 action_information21,
231 action_information22,
232 action_information23,
233 action_information24,
234 action_information25,
235 action_information26,
236 action_information27,
237 action_information28,
238 action_information29,
239 action_information30
240 FROM pay_action_information
241 WHERE (action_information_id = p_action_information_id AND p_action_information_id IS NOT NULL)
242 AND action_information_category = p_category
243 UNION ALL /* Bug 6522667, added UNION ALL */
244 SELECT effective_date,
245 action_information1,
246 action_information2,
247 action_information3,
248 action_information4,
249 action_information5,
250 action_information6,
251 action_information7,
252 action_information8,
253 action_information9,
254 action_information10,
255 action_information11,
256 action_information12,
257 action_information13,
258 action_information14,
259 action_information15,
260 action_information16,
261 action_information17,
262 action_information18,
263 action_information19,
264 action_information20,
265 action_information21,
266 action_information22,
267 action_information23,
268 action_information24,
269 action_information25,
270 action_information26,
271 action_information27,
272 action_information28,
273 action_information29,
274 action_information30
275 FROM pay_action_information
276 WHERE ((action_context_id = p_action_context_id AND action_context_type = 'AAP') OR
277 (action_context_id = (SELECT payroll_action_id
278 FROM pay_assignment_actions
279 WHERE assignment_action_id = p_action_context_id)
280 AND action_context_type = 'PA'
281 AND ((action_information1 = p_category_filter AND
282 p_category IN ('ADDRESS DETAILS', pay_payroll_xml_extract_pkg.g_leg_code || ' EMPLOYER DETAILS')) OR
283 (p_category NOT IN ('ADDRESS DETAILS', pay_payroll_xml_extract_pkg.g_leg_code || ' EMPLOYER DETAILS')))))
284 AND action_information_category = p_category;
285
286 CURSOR csr_payroll_details (p_time_period_id number) IS
287 SELECT ppf.payroll_name,
288 ptp.period_type,
289 substr(fnd_date.date_to_canonical(ptp.start_date),1,10) start_date,
290 substr(fnd_date.date_to_canonical(ptp.end_date),1,10) end_date
291 FROM pay_payrolls_f ppf,
292 per_time_periods ptp
293 WHERE ppf.payroll_id = ptp.payroll_id
294 AND ptp.time_period_id = p_time_period_id;
295
296 END PAY_PAYROLL_XML_EXTRACT_PKG;
297
[Home] [Help]