1 PACKAGE pay_us_web_w4
2 /* $Header: pyuswbw4.pkh 120.7.12010000.1 2008/07/28 00:00:12 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 *******************************************************************/
75 AS
76
77 /* global variables */
78
79 g_state_list varchar2(10000);
80 g_state_exempt_list varchar2(10000);
81 g_not_matching_state_list VARCHAR2(10000);
82 g_nonmatch_cntr NUMBER;
83
84 PROCEDURE validate_submission(p_filing_status_code IN VARCHAR2 DEFAULT null,
85 p_additional_amount IN NUMBER DEFAULT null,
86 p_allowances IN VARCHAR2 DEFAULT null,
87 p_exempt_status_code IN VARCHAR2 DEFAULT null,
88 p_agreement IN VARCHAR2 DEFAULT 'N',
89 p_person_id IN VARCHAR2,
90 p_error OUT nocopy VARCHAR2,
91 p_errorcnt OUT nocopy INTEGER,
92 p_itemtype IN VARCHAR2,
93 p_itemkey IN VARCHAR2,
94 p_activity_id IN NUMBER,
95 p_state_list OUT nocopy VARCHAR2,
96 p_over_allowance OUT nocopy VARCHAR2,
97 p_exempt_exception OUT nocopy VARCHAR2,
98 p_original_fs IN VARCHAR2,
99 p_original_wa IN VARCHAR2,
100 p_original_exempt IN VARCHAR2,
101 p_exempt_state_list OUT nocopy VARCHAR2,
102 p_original_aa IN NUMBER,
103 p_last_name_diff IN VARCHAR2 DEFAULT 'N',
104 p_fit_exempt OUT nocopy VARCHAR2
105 );
106 /******************************************************************
107 **
108 ** Description:
109 ** validates the submitted information and then displays either
110 ** the update page with errors or the review page.
111 **
112 ** It uses the chk_ procedures of the FED api to validate. It
113 ** does not pass a tax id to the chk_ procedures since it may be updating
114 ** multiple fed tax rows and the chk_ procedures don't really care.
115 ** The submitted values are more rigorously validated upon updating.
116 **
117 ** Access Status:
118 ** Public
119 **
120 ******************************************************************/
121
122 FUNCTION check_update_status(p_person_id IN per_people_f.person_id%TYPE)
123 RETURN VARCHAR2;
124 /******************************************************************
125 **
126 ** Description:
127 ** Checks that employee meets these conditions:
128 ** Update Method profile option not set to NONE
129 ** No allowance reject dates or overrides for current recs
130 ** No future dated changes
131 ** Primary assignment is not a retiree asg
132 ** If it fails a test, it returns the appropriate error msg
133 ** Otherwise, it returns null.
134 ** Access Status:
135 ** Public
136 **
137 ******************************************************************/
138
139 PROCEDURE update_alien_tax_records(
140 p_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
141 ,p_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
142 ,p_additional_amount pay_us_emp_fed_tax_rules_f.fit_additional_tax%TYPE
143 ,p_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE
144 ,p_process VARCHAR2
145 ,p_itemtype VARCHAR2
146 ,p_person_id per_people_f.person_id%TYPE default null
147 ,p_effective_date date default null
148 ,p_source_name VARCHAR2 default null
149 );
150 /******************************************************************
151 **
152 ** Description: OTF Fed W4 update procedure for alien changes
153 ** 1. locks all applicable rows
154 ** 2. update each fed row using fed api
155 ** 3. update each state row using state api
156 ** 4. archive the submission
157 **
158 ** Access Status:
159 ** Public
160 **
161 ******************************************************************/
162
163
164 PROCEDURE update_tax_records(
165 p_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
166 ,p_org_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
167 ,p_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
168 ,p_org_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
169 ,p_additional_amount pay_us_emp_fed_tax_rules_f.fit_additional_tax%TYPE
170 ,p_last_name_diff VARCHAR2 DEFAULT 'N'
171 ,p_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE
172 ,p_org_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE
173 ,p_transaction_id hr_api_transactions.transaction_id%type
174 ,p_person_id VARCHAR2
175 ,p_transaction_type VARCHAR2
176 ,p_source_name VARCHAR2
177 ,p_validate boolean default false
178 );
179 /******************************************************************
180 **
181 ** Description: OTF Fed W4 update procedure
182 ** 1. locks all applicable rows
183 ** 2. update each fed row using fed api
184 ** 3. update each state row using state api
185 ** 4. archive the submission
186 **
187 ** Access Status:
188 ** Public
189 **
190 ******************************************************************/
191
192 PROCEDURE get_transaction_values(
193 p_trans_id IN VARCHAR2 default null,
194 p_step_id IN VARCHAR2 default null,
195 p_out_values OUT nocopy VARCHAR2 );
196
197 /******************************************************************
198 **
199 ** Description: This procedure gets the transaction values that
200 ** were inserted while transiting from update page
201 ** to review page to be displayed on the review page.
202 ** Access Status:
203 ** Public
204 **
205 ******************************************************************/
206
207 PROCEDURE update_w4_info(
208 p_validate in boolean default false ,
209 p_transaction_step_id in number);
210
211 FUNCTION GET_STATE_LIST(p_person_id IN per_people_f.person_id%TYPE,
212 p_primary_flag IN varchar2 )
213 RETURN VARCHAR2;
214
215 FUNCTION GET_ORG_CONTEXT(p_person_id IN per_people_f.person_id%TYPE,
216 p_context hr_organization_information.org_information_context%TYPE,
217 p_level IN VARCHAR2)
218 RETURN VARCHAR2;
219
220 -- The following Function has been Added to correct inconsistent behaviour of State W-4
221 -- For the States that should follow Federal W-4, Filing Status, Allowances etc. should be
222 -- defaulted from Federal Information for them. Whereas for States that May OR May NOT
223 -- follow Federal W-4, if Filing Status does not match with State W-4 the information will
224 -- NOT be copied and an Informational Message will be displayed to Customer.
225 --
226 --
227 FUNCTION Fed_State_Filing_Status_Match(
228 p_state_code IN pay_us_states.state_code%TYPE
229 ,p_state_org_filing_status_code IN pay_us_emp_state_tax_rules_f.filing_status_code%TYPE
230 ,p_fed_org_filing_status_code IN pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
231 ,p_fed_org_wa IN pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
232 ,p_state_org_wa IN pay_us_emp_state_tax_rules_f.withholding_allowances%TYPE
233 ,p_fed_exmpt_cnt IN NUMBER
234 ,p_state_empt_cnt IN NUMBER
235 ,p_new_filing_status_code IN OUT NOCOPY pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
236 )
237 RETURN BOOLEAN;
238 --
239 --
240
241 END pay_us_web_w4;
[Home] [Help]