[Home] [Help]
PACKAGE: APPS.PAY_1099R_PKG
Source
1 package pay_1099R_pkg as
2 /* $Header: pyus109r.pkh 120.1 2006/11/08 13:15:40 alikhar noship $*/
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ****************************************************************** */
22 /*
23 rem Name :PYUS109R.PKH
24 rem Description :This package defines the cursors needed to run 1099R Information Return
25 rem Multi-Thread.
26
27 rem Change List
28 -----------
29 rem Date Name Vers Description
30 ----- ---- ----- -----------
31 rem 08-SEP-2000 Fusman 115.0 Created
32 rem 19-JAN-2002 meshah 115.1 dbdrv.
33 rem 22-JAN-2002 meshah 115.2 dbdrv, checkfile syntax.
34 rem 09-JAN-2003 asasthan 115.4 nocopy changes
35 rem 08-NOV-2006 alikhar 115.5 Modified for 1099R PDF conversion
36 */
37
38 level_cnt number;
39
40 procedure range_cursor ( pactid in number,
41 sqlstr out nocopy varchar2
42 );
43 procedure action_creation ( pactid in number,
44 stperson in number,
45 endperson in number,
46 chunk in number
47 );
48 procedure sort_action ( payactid in varchar2,
49 sqlstr in out nocopy varchar2,
50 len out nocopy number
51 );
52 function get_parameter(name in varchar2,
53 parameter_list varchar2) return varchar2;
54 pragma restrict_references(get_parameter, WNDS, WNPS);
55
56 PROCEDURE generate_detail_xml;
57
58 PROCEDURE generate_header_xml;
59
60 PROCEDURE generate_footer_xml;
61
62 CURSOR c_1099R_detail IS
63 SELECT 'TRANSFER_ACT_ID=P',
64 paa1.assignment_action_id
65 FROM pay_assignment_actions paa1,
66 pay_payroll_actions ppa1,
67 pay_assignment_actions paa
68 WHERE ppa1.payroll_action_id = pay_magtape_generic.get_parameter_value
69 ('TRANSFER_PAYROLL_ACTION_ID')
70 AND paa1.payroll_action_id = ppa1.payroll_action_id
71 AND paa.assignment_action_id = paa1.serial_number
72 ORDER BY DECODE
73 (pay_1099r_pkg.get_parameter ('SORT_1',
74 ppa1.legislative_parameters
75 ),
76 'Employee_Name', hr_us_w2_rep.get_per_item
77 (paa.assignment_action_id,
78 'A_PER_LAST_NAME'
79 )
80 || hr_us_w2_rep.get_per_item (paa.assignment_action_id,
81 'A_PER_FIRST_NAME'
82 )
83 || DECODE
84 (hr_us_w2_rep.get_per_item (paa.assignment_action_id,
85 'A_PER_MIDDLE_NAMES'
86 ),
87 NULL, NULL,
88 SUBSTR
89 (hr_us_w2_rep.get_per_item (paa.assignment_action_id,
90 'A_PER_MIDDLE_NAMES'
91 ),
92 1,
93 1
94 )
95 ),
96 'Social_Security_Number', NVL
97 (hr_us_w2_rep.get_per_item (paa.assignment_action_id,
98 'A_PER_NATIONAL_IDENTIFIER'
99 ),
100 'Applied For'
101 ),
102 'Zip_Code', hr_us_w2_rep.get_w2_postal_code
103 (TO_NUMBER (paa.serial_number),
104 ppa1.effective_date
105 ),
106 hr_us_w2_rep.get_per_item (paa.assignment_action_id,
107 'A_PER_LAST_NAME'
108 )
109 || hr_us_w2_rep.get_per_item (paa.assignment_action_id,
110 'A_PER_FIRST_NAME'
111 )
112 || DECODE
113 (hr_us_w2_rep.get_per_item (paa.assignment_action_id,
114 'A_PER_MIDDLE_NAMES'
115 ),
116 NULL, NULL,
117 SUBSTR
118 (hr_us_w2_rep.get_per_item (paa.assignment_action_id,
119 'A_PER_MIDDLE_NAMES'
120 ),
121 1,
122 1
123 )
124 )
125 ),
126 DECODE
127 (pay_1099r_pkg.get_parameter ('SORT_2',
128 ppa1.legislative_parameters
129 ),
130 'Employee_Name', hr_us_w2_rep.get_per_item
131 (paa.assignment_action_id,
132 'A_PER_LAST_NAME'
133 )
134 || hr_us_w2_rep.get_per_item (paa.assignment_action_id,
135 'A_PER_FIRST_NAME'
136 )
137 || DECODE
138 (hr_us_w2_rep.get_per_item (paa.assignment_action_id,
139 'A_PER_MIDDLE_NAMES'
140 ),
141 NULL, NULL,
142 SUBSTR
143 (hr_us_w2_rep.get_per_item (paa.assignment_action_id,
144 'A_PER_MIDDLE_NAMES'
145 ),
146 1,
147 1
148 )
149 ),
150 'Social_Security_Number', NVL
151 (hr_us_w2_rep.get_per_item (paa.assignment_action_id,
152 'A_PER_NATIONAL_IDENTIFIER'
153 ),
154 'Applied For'
155 ),
156 'Zip_Code', hr_us_w2_rep.get_w2_postal_code
157 (TO_NUMBER (paa.serial_number),
158 ppa1.effective_date
159 ),
160 NULL
161 ),
162 DECODE
163 (pay_1099r_pkg.get_parameter ('SORT_3',
164 ppa1.legislative_parameters
165 ),
166 'Employee_Name', hr_us_w2_rep.get_per_item
167 (paa.assignment_action_id,
168 'A_PER_LAST_NAME'
169 )
170 || hr_us_w2_rep.get_per_item (paa.assignment_action_id,
171 'A_PER_FIRST_NAME'
172 )
173 || DECODE
174 (hr_us_w2_rep.get_per_item (paa.assignment_action_id,
175 'A_PER_MIDDLE_NAMES'
176 ),
177 NULL, NULL,
178 SUBSTR
179 (hr_us_w2_rep.get_per_item (paa.assignment_action_id,
180 'A_PER_MIDDLE_NAMES'
181 ),
182 1,
183 1
184 )
185 ),
186 'Social_Security_Number', NVL
187 (hr_us_w2_rep.get_per_item (paa.assignment_action_id,
188 'A_PER_NATIONAL_IDENTIFIER'
189 ),
190 'Applied For'
191 ),
192 'Zip_Code', hr_us_w2_rep.get_w2_postal_code
193 (TO_NUMBER (paa.serial_number),
194 ppa1.effective_date
195 ),
196 NULL
197 );
198
199
200 CURSOR c_1099R_hf IS
201 SELECT 'PAYROLL_ACTION_ID=P'
202 ,pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
203 FROM dual;
204
205 CURSOR c_1099R_asg_actions IS
206 SELECT 'TRANSFER_ACT_ID=P'
207 ,pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
208 FROM dual;
209 --
210 end pay_1099R_pkg;