1 PACKAGE pay_us_web_w4
2 /* $Header: pyuswbw4.pkh 120.8.12020000.1 2012/06/28 19:38:31 appldev ship $ *
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2000 Oracle Corporation. *
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_us_web_w4
21
22 Description : Contains utility and back end procedures for the W4.
23
24 Uses :
25
26 Change List
27 -----------
28 Date Name Vers Description
29 ---- ---- ---- -----------
30 3-MAR-2000 dscully 110.0 Created.
31
32 24-MAR-2000 dscully 115.0 Created.
33 10-APR-2000 dscully 115.1 Added process and itemtype parameters
34 11-APR-2001 meshah 115.2 Added procedure get_transaction_values
35 Added procedure check_update_status.
36 New procedure update_w4_info.
37 New parameters in procedure
38 validate_submission and update_tax_records.
39 11-APR-2001 meshah 115.3 Removed item_type and added transaction_id
40 iin update_tax_record.
41 25-MAY-2001 meshah 115.4 Added one more parameter to validate_submission.
42 20-AUG-2001 meshah 115.5 Added two more parameter to validate_submission.
43 Removed procedure check_update_status.
44 04-SEP-2001 meshah 115.1 adding p_original_exempt in validate_submission
45 and p_org_filing_status_code ,p_org_allowances
46 and p_org_exempt_status_code to update_tax_records.
47 15-OCT-2001 meshah 115.2 Defined a new global variable g_state_list.
48 20-DEC-2002 meshah 115.3 added nocopy and dbdrv.
49 28-OCT-2003 meshah 115.4 p_exempt_state_list parameters has been
50 added to validate_submission and
51 g_state_exempt_list a new global variable.
52 09-APR-2004 meshah 115.5 p_original_aa parameter has been
53 added to validate_submission.
54 23-MAY-2005 rsethupa 115.6 Bug 4070034 - Added new parameter p_last_name_diff
55 to procedures validate_submission and
56 update_tax_records
57 26-sep-2005 jgoswami 115.7 Bug 4599982 - Added update_alien_tax_records
58 to support pqp calls to old w4 packages.
59 17-jan-2006 jgoswami 115.8 Bug 4956850 - added new parameter
60 p_transaction_type and p_source_name to
61 procedure update_tax_records
62 11-aug-2006 jgoswami 115.9 Bug 5198005 - Supress W4 Notifications for the
63 W4 forms that are exempt or at a level above 10 allowances as IRS does not require Employer to
64 Send it. Based on the value of the DFF the
65 Notification will be sent or suppressed.Default the Notification will be Suppressed.
66 created function get_org_context
67 06-sep-2006 jgoswami 115.10 Bug 3852021 - Modified validate_submission
68 changed data type for p_additional_amount,
69 p_original_aa from varchar2 to Number .
70 13-AUG-2007 vaprakas 115.10 Bug 6200677 modified
71 17-NOV-2007 sudedas 115.12 Added new Function Fed_State_Filing_Status_Match
72 AND Global Variables g_not_matching_state_list
73 AND g_nonmatch_cntr.
74 03-Aug-2009 kagangul 115.13 Added one global variable 'g_NRA_flag' for tracking
75 the FIT_EXEMPT change of a NRA employee.
76 *******************************************************************/
77 AS
78
79 /* global variables */
80
81 g_state_list varchar2(10000);
82 g_state_exempt_list varchar2(10000);
83 g_not_matching_state_list VARCHAR2(10000);
84 g_nonmatch_cntr NUMBER;
85 g_NRA_flag CHAR(1);
86
87 PROCEDURE validate_submission(p_filing_status_code IN VARCHAR2 DEFAULT null,
88 p_additional_amount IN NUMBER DEFAULT null,
89 p_allowances IN VARCHAR2 DEFAULT null,
90 p_exempt_status_code IN VARCHAR2 DEFAULT null,
91 p_agreement IN VARCHAR2 DEFAULT 'N',
92 p_person_id IN VARCHAR2,
93 p_error OUT nocopy VARCHAR2,
94 p_errorcnt OUT nocopy INTEGER,
95 p_itemtype IN VARCHAR2,
96 p_itemkey IN VARCHAR2,
97 p_activity_id IN NUMBER,
98 p_state_list OUT nocopy VARCHAR2,
99 p_over_allowance OUT nocopy VARCHAR2,
100 p_exempt_exception OUT nocopy VARCHAR2,
101 p_original_fs IN VARCHAR2,
102 p_original_wa IN VARCHAR2,
103 p_original_exempt IN VARCHAR2,
104 p_exempt_state_list OUT nocopy VARCHAR2,
105 p_original_aa IN NUMBER,
106 p_last_name_diff IN VARCHAR2 DEFAULT 'N',
107 p_fit_exempt OUT nocopy VARCHAR2
108 );
109 /******************************************************************
110 **
111 ** Description:
112 ** validates the submitted information and then displays either
113 ** the update page with errors or the review page.
114 **
115 ** It uses the chk_ procedures of the FED api to validate. It
116 ** does not pass a tax id to the chk_ procedures since it may be updating
117 ** multiple fed tax rows and the chk_ procedures don't really care.
118 ** The submitted values are more rigorously validated upon updating.
119 **
120 ** Access Status:
121 ** Public
122 **
123 ******************************************************************/
124
125 FUNCTION check_update_status(p_person_id IN per_people_f.person_id%TYPE)
126 RETURN VARCHAR2;
127 /******************************************************************
128 **
129 ** Description:
130 ** Checks that employee meets these conditions:
131 ** Update Method profile option not set to NONE
132 ** No allowance reject dates or overrides for current recs
133 ** No future dated changes
134 ** Primary assignment is not a retiree asg
135 ** If it fails a test, it returns the appropriate error msg
136 ** Otherwise, it returns null.
137 ** Access Status:
138 ** Public
139 **
140 ******************************************************************/
141
142 PROCEDURE update_alien_tax_records(
143 p_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
144 ,p_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
145 ,p_additional_amount pay_us_emp_fed_tax_rules_f.fit_additional_tax%TYPE
146 ,p_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE
147 ,p_process VARCHAR2
148 ,p_itemtype VARCHAR2
149 ,p_person_id per_people_f.person_id%TYPE default null
150 ,p_effective_date date default null
151 ,p_source_name VARCHAR2 default null
152 );
153 /******************************************************************
154 **
155 ** Description: OTF Fed W4 update procedure for alien changes
156 ** 1. locks all applicable rows
157 ** 2. update each fed row using fed api
158 ** 3. update each state row using state api
159 ** 4. archive the submission
160 **
161 ** Access Status:
162 ** Public
163 **
164 ******************************************************************/
165
166
167 PROCEDURE update_tax_records(
168 p_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
169 ,p_org_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
170 ,p_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
171 ,p_org_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
172 ,p_additional_amount pay_us_emp_fed_tax_rules_f.fit_additional_tax%TYPE
173 ,p_last_name_diff VARCHAR2 DEFAULT 'N'
174 ,p_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE
175 ,p_org_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE
176 ,p_transaction_id hr_api_transactions.transaction_id%type
177 ,p_person_id VARCHAR2
178 ,p_transaction_type VARCHAR2
179 ,p_source_name VARCHAR2
180 ,p_validate boolean default false
181 );
182 /******************************************************************
183 **
184 ** Description: OTF Fed W4 update procedure
185 ** 1. locks all applicable rows
186 ** 2. update each fed row using fed api
187 ** 3. update each state row using state api
188 ** 4. archive the submission
189 **
190 ** Access Status:
191 ** Public
192 **
193 ******************************************************************/
194
195 PROCEDURE get_transaction_values(
196 p_trans_id IN VARCHAR2 default null,
197 p_step_id IN VARCHAR2 default null,
198 p_out_values OUT nocopy VARCHAR2 );
199
200 /******************************************************************
201 **
202 ** Description: This procedure gets the transaction values that
203 ** were inserted while transiting from update page
204 ** to review page to be displayed on the review page.
205 ** Access Status:
206 ** Public
207 **
208 ******************************************************************/
209
210 PROCEDURE update_w4_info(
211 p_validate in boolean default false ,
212 p_transaction_step_id in number);
213
214 FUNCTION GET_STATE_LIST(p_person_id IN per_people_f.person_id%TYPE,
215 p_primary_flag IN varchar2 )
216 RETURN VARCHAR2;
217
218 FUNCTION GET_ORG_CONTEXT(p_person_id IN per_people_f.person_id%TYPE,
219 p_context hr_organization_information.org_information_context%TYPE,
220 p_level IN VARCHAR2)
221 RETURN VARCHAR2;
222
223 -- The following Function has been Added to correct inconsistent behaviour of State W-4
224 -- For the States that should follow Federal W-4, Filing Status, Allowances etc. should be
225 -- defaulted from Federal Information for them. Whereas for States that May OR May NOT
226 -- follow Federal W-4, if Filing Status does not match with State W-4 the information will
227 -- NOT be copied and an Informational Message will be displayed to Customer.
228 --
229 --
230 FUNCTION Fed_State_Filing_Status_Match(
231 p_state_code IN pay_us_states.state_code%TYPE
232 ,p_state_org_filing_status_code IN pay_us_emp_state_tax_rules_f.filing_status_code%TYPE
233 ,p_fed_org_filing_status_code IN pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
234 ,p_fed_org_wa IN pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
235 ,p_state_org_wa IN pay_us_emp_state_tax_rules_f.withholding_allowances%TYPE
236 ,p_fed_exmpt_cnt IN NUMBER
237 ,p_state_empt_cnt IN NUMBER
238 ,p_new_filing_status_code IN OUT NOCOPY pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
239 )
240 RETURN BOOLEAN;
241 --
242 --
243
244 END pay_us_web_w4;
[Home] [Help]