1 PACKAGE PAY_PAYROLL_XML_EXTRACT_PKG AUTHID CURRENT_USER as
2 /* $Header: pyxmlxtr.pkh 120.9.12020000.7 2013/03/07 10:51:29 emunisek 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 24-Feb-2011 abellur 115.10 8606266 Updated the csr_get_archived_info
60 cursor to get the override date
61 if specified, in the override prog.
62 else show the same original date.
63 23-Feb-2012 abellur 115.11 13029999 Added the procedure
64 GENERATE_THIRD_PARTY and cursor for
65 the same csr_get_thd_pty_arch_info
66 28-Feb-2012 sjawid 115.12 13743223 Added global variable
67 g_install_leg_check
68 04-Feb-2013 emunisek 115.15 16082307 Added Procedure SIMULATION_GENERATE
69 to generate the XML Data necessary
70 for Payroll Simulation Output.
71 07-Mar-2013 emunisek 115.16 16281614 Moved the procedure SIMULATION_GENERATE
72 to package PAY_US_PAYSLIP_SIMULATION_MAIN
73 so that this package can have no dependancies
74 on Simulation Code.
75 *****************************************************************************/
76
77 TYPE int_tab_type IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
78 TYPE char_tab_type IS TABLE OF pay_action_information.action_information1%type
79 INDEX BY BINARY_INTEGER;
80 TYPE param_rec IS RECORD (
81 parameter_name varchar2(50),
82 parameter_value varchar2(500)
83 );
84 TYPE param_tab_type IS TABLE OF param_rec INDEX BY BINARY_INTEGER;
85
86 /****************************************************************************
87 Name : LOAD_XML
88 Description : This procedure loads the global XML cache.
89 Parameters : P_NODE_TYPE This parameter can take one of these values: -
90 1. CS - This signifies that string contained in
91 P_NODE parameter is start of container
92 node. P_DATA parameter is ignored in this
93 mode.
94 2. CE - This signifies that string contained in
95 P_NODE parameter is end of container
96 node. P_DATA parameter is ignored in this
97 mode.
98 3. D - This signifies that string contained in
99 P_NODE parameter is data node and P_DATA
100 carries actual data to be contained by
101 tag specified by P_NODE parameter.
102
103 P_CONTEXT_CODE Context code of descriptive flexfield.
104
105 P_NODE Name of XML tag, or, application column name of flex segment.
106
107 P_DATA Data to be contained by tag specified by P_NODE parameter.
108 P_DATA is not used unless P_NODE_TYPE = D.
109 *****************************************************************************/
110 PROCEDURE LOAD_XML (
111 P_NODE_TYPE varchar2,
112 P_FLEXFIELD_NAME varchar2,
113 P_CONTEXT_CODE varchar2,
114 P_NODE varchar2,
115 P_DATA varchar2
116 );
117
118
119 /****************************************************************************
120 Name : LOAD_XML
121 Description : This procedure obtains segment titles from the Action
122 Information DF. This is temporary, and is created only to
123 provide backward compatibility for payslip code. Once the
124 payslip processes are changed to pass the flexfield name,
125 this procedure can be removed.
126 *****************************************************************************/
127 PROCEDURE LOAD_XML (
128 P_NODE_TYPE varchar2,
129 P_CONTEXT_CODE varchar2,
130 P_NODE varchar2,
131 P_DATA varchar2
132 );
133
134 /****************************************************************************
135 Name : LOAD_XML
136 Description : This procedure obtains segment title from the bank key
137 flexfield to be used as XML tag.
138 *****************************************************************************/
139 PROCEDURE LOAD_XML (
140 P_NODE_TYPE varchar2,
141 P_NODE varchar2,
142 P_DATA varchar2
143 );
144
145
146 /****************************************************************************
147 Name : LOAD_XML
148 Description : This procedure accepts a well-formed XML and loads it into
149 global XML cache. Note that this procedure does not perform
150 any syntactical validations over passed XML data.
151 LOAD_XML_DATA should be used if such validations are required
152 to be performed implicitly.
153 *****************************************************************************/
154 PROCEDURE LOAD_XML (
155 P_XML pay_action_information.action_information1%type
156 );
157
158
159 /****************************************************************************
160 Name : LOAD_XML_DATA
161 Description : This procedure accepts meta-data along with actual XML data
162 and loads the global XML cache. This is a public procedure
163 which performs basic validations to check well-formedness of
164 XML data before loading the cache. Please see parameter
165 description of public version of LOAD_XML to find what each
166 parameter signifies.
167 *****************************************************************************/
168 PROCEDURE LOAD_XML_DATA (
169 P_NODE_TYPE varchar2,
170 P_NODE varchar2,
171 P_DATA varchar2
172 );
173
174 /****************************************************************************
175 Name : GENERATE
176 Description : This procedure interprets archived information, converts it to
177 XML and prints it out to a BLOB. This is a public procedure
178 and is based on action_context_IDs passed by the calling
179 process. All archived records belonging to passed
180 action_context_id will be converted to XML. Currently, online
181 payslip and MX Pay Advice invoke this overloaded version.
182 *****************************************************************************/
183 PROCEDURE GENERATE (
184 P_ACTION_CONTEXT_ID number,
185 P_CUSTOM_XML_PROCEDURE varchar2,
186 P_GENERATE_HEADER_FLAG varchar2, -- {Y/N}
187 P_ROOT_TAG varchar2,
188 P_DOCUMENT_TYPE varchar2,
189 P_XML OUT NOCOPY BLOB
190 );
191
192
193 /****************************************************************************
194 Name : GENERATE
195 Description : This procedure interprets archived information, converts it to
196 XML and prints it out to a BLOB. This is a public procedure
197 and is driven off action_information_IDs set by the calling
198 process.
199
200 It also accepts a custom XML tag parameter, which if passed a
201 non-null value, will be used as parent enclosing tag of each
202 action_information_id irrespective of the actual action
203 information category.
204
205 Currently, MX SUA process invokes this overloaded version.
206 *****************************************************************************/
207
208 PROCEDURE GENERATE (
209 P_ACTION_INF_ID_TAB int_tab_type,
210 P_CUSTOM_ACTION_INFO_CAT varchar2,
211 P_DOCUMENT_TYPE varchar2,
212 P_XML OUT NOCOPY BLOB
213 );
214
215 /*13029999 Added the below procedure to generate XML code
216 for Third Party Payment Cheque context*/
217 PROCEDURE GENERATE_THIRD_PARTY
218 (
219 P_ACTION_INFORMATION_ID number,
220 P_ACTION_CONTEXT_ID number,
221 P_PRE_PAYMENT_ID number,
222 P_CUSTOM_ACTION_INFO_CAT varchar2,
223 P_CUSTOM_XML_PROCEDURE varchar2,
224 P_GENERATE_HEADER_FLAG boolean,
225 P_ROOT_TAG varchar2,
226 P_DOCUMENT_TYPE varchar2,
227 P_XML OUT NOCOPY BLOB
228 );
229
230 /*---- Global declarations ---*/
231 g_leg_code fnd_territories.territory_code%type;
232 g_xml_table char_tab_type;
233 g_custom_xml char_tab_type;
234 g_custom_params param_tab_type;
235 g_install_leg_check char(1) := 'Y';
236
237 /* Updated the effective date criteria to pick up override date
238 if provided. Bug # 8606266*/
239 CURSOR csr_get_archived_info (p_action_context_id number,
240 p_category varchar2,
241 p_category_filter varchar2,
242 p_action_information_id number) IS
243 SELECT nvl((select overriding_dd_date from pay_payroll_actions where payroll_action_id=(select pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID') from dual)),effective_date) effective_date,
244 action_information1,
245 action_information2,
246 action_information3,
247 action_information4,
248 action_information5,
249 action_information6,
250 action_information7,
251 action_information8,
252 action_information9,
253 action_information10,
254 action_information11,
255 action_information12,
256 action_information13,
257 action_information14,
258 action_information15,
259 action_information16,
260 action_information17,
261 action_information18,
262 action_information19,
263 action_information20,
264 action_information21,
265 action_information22,
266 action_information23,
267 action_information24,
268 action_information25,
269 action_information26,
270 action_information27,
271 action_information28,
272 action_information29,
273 action_information30
274 FROM pay_action_information
275 WHERE (action_information_id = p_action_information_id AND p_action_information_id IS NOT NULL)
276 AND action_information_category = p_category
277 UNION ALL /* Bug 6522667, added UNION ALL */
278 SELECT nvl((select overriding_dd_date from pay_payroll_actions where payroll_action_id=(select pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID') from dual)),effective_date) effective_date,
279 action_information1,
280 action_information2,
281 action_information3,
282 action_information4,
283 action_information5,
284 action_information6,
285 action_information7,
286 action_information8,
287 action_information9,
288 action_information10,
289 action_information11,
290 action_information12,
291 action_information13,
292 action_information14,
293 action_information15,
294 action_information16,
295 action_information17,
296 action_information18,
297 action_information19,
298 action_information20,
299 action_information21,
300 action_information22,
301 action_information23,
302 action_information24,
303 action_information25,
304 action_information26,
305 action_information27,
306 action_information28,
307 action_information29,
308 action_information30
309 FROM pay_action_information
310 WHERE ((action_context_id = p_action_context_id AND action_context_type = 'AAP') OR
311 (action_context_id = (SELECT payroll_action_id
312 FROM pay_assignment_actions
313 WHERE assignment_action_id = p_action_context_id)
314 AND action_context_type = 'PA'
315 AND ((action_information1 = p_category_filter AND
316 p_category IN ('ADDRESS DETAILS', pay_payroll_xml_extract_pkg.g_leg_code || ' EMPLOYER DETAILS')) OR
317 (p_category NOT IN ('ADDRESS DETAILS', pay_payroll_xml_extract_pkg.g_leg_code || ' EMPLOYER DETAILS')))))
318 AND action_information_category = p_category;
319
320 CURSOR csr_payroll_details (p_time_period_id number) IS
321 SELECT ppf.payroll_name,
322 ptp.period_type,
323 substr(fnd_date.date_to_canonical(ptp.start_date),1,10) start_date,
324 substr(fnd_date.date_to_canonical(ptp.end_date),1,10) end_date
325 FROM pay_payrolls_f ppf,
326 per_time_periods ptp
327 WHERE ppf.payroll_id = ptp.payroll_id
328 AND ptp.time_period_id = p_time_period_id;
329
330
331 --13029999 Added the below cursor for Third Party Checks/Cheques. 13029999 changes start
332 CURSOR csr_get_thd_pty_arch_info (p_action_context_id number,
333 p_category varchar2,
334 p_pre_payment_id number) IS
335 SELECT effective_date,
336 action_information1,
337 action_information2,
338 action_information3,
339 action_information4,
340 action_information5,
341 action_information6,
342 action_information7,
343 action_information8,
344 action_information9,
345 action_information10,
346 action_information11,
347 action_information12,
348 action_information13,
349 action_information14,
350 action_information15,
351 action_information16,
352 action_information17,
353 action_information18,
354 action_information19,
355 action_information20,
356 action_information21,
357 action_information22,
358 action_information23,
359 action_information24,
360 action_information25,
361 action_information26,
362 action_information27,
363 action_information28,
364 action_information29,
365 action_information30
366 FROM pay_action_information
367 WHERE action_information_category = p_category
368 AND (action_information_category like '%THIRD PARTY CHECKS' or action_information_category like '%THIRD PARTY CHEQUES')
369 AND action_context_id = p_action_context_id
370 AND action_information2 = p_pre_payment_id;
371 --13029999 changes end
372
373 END PAY_PAYROLL_XML_EXTRACT_PKG;
374
[Home] [Help]