1 PACKAGE pay_us_sqwl_udf AUTHID CURRENT_USER as
2 /* $Header: pyussqut.pkh 120.4.12020000.7 2013/03/26 06:42:28 schowl ship $ */
3 /* +======================================================================+
4 REM | Copyright (c) 1997 Oracle Corporation |
5 REM | Redwood Shores, California, USA |
6 REM | All rights reserved. |
7 REM +======================================================================+
8 REM SQL Script File Name : pyussqut.pkh
9 REM Description : Package and procedure to build sql for payroll
10 REM processes.
11 REM Package Name : pay_us_sqwl_udf
12 REM Purpose : Sets up the data to provide SQWL Ulitity
13 REM Processing.
14 REM
15 REM Notes : This data is US specific.
16 REM
17 REM Change List:
18 REM ------------
19 REM
20 REM Name Date Version Bug Text
21 REM ------------ ---------- ------- ------- ------------------------------
22 REM M Doody 16-FEB-2001 115.0 Initial Version
23 REM
24 REM
25 REM tmehra 17-AUG-2001 115.1 Added 'get_gre_wage_plan_code'
26 REM
27 REM tmehra 15-OCT-2001 115.2 Added 'get_asg_wage_plan_code'
28 REM tmehra 07-MAY-2003 115.4 Added new validation for
29 REM california sqwl as a new
30 REM new segment has been introduced
31 REM for the info type.
32 REM tmehra 26-AUG-2003 115.6 2219097 Added two new functions for the
33 REM US W2 enhancements for Govt
34 REM employer.
35 REM - get_employment_code
36 REM - chk_govt_employer
37 REM Added the following function
38 REM for US W2 enhancements for Govt
39 REM employer.
40 REM - get_archived_emp_code
41 REM emunisek 05-Mar-2010 115.7 9356178 Added get_out_of_state_code
42 REM function
43 REM emunisek 30-Mar-2010 115.8 9356178 Modified the parameter type of
44 REM p_out_of_state_taxable parameter
45 REM in function get_out_of_state_code
46 REM emunisek 30-Mar-2010 115.9 9356178 Made file GSCC Compliant
47 REM nvelaga 26-Jul-2012 115.10 14368821 Added get_ar_out_of_state_code
48 REM function.
49 REM ybudamal 14-Sep-2012 115.12 14008507 Added Formula Functions Get_Employer_Count,
50 REM Get_Employer_Total_Wages and Get_Excess_Wages
51 REM for Q32012 MI SQWL changes.
52 REM schowl 22-Jan-2013 115.20 14456648 Added new formula function 'Get_SMWL_SUI_Wages'
53 REM to calculate Monthly SUI wages for the report 'SMWL'
54 REM schowl 11-Mar-2013 115.21 14345202 Added new formula function 'get_ca_sqwl_wages'
55 REM to get required Employer wages for CA_XML_SQWL Format
56 REM schowl 14-Mar-2013 115.22 14345202 Added new formula function 'spl_char_check'
57 REM to remove special characters from the string
58 REM schowl 26-Mar-2013 115.23 14345202 Added an extra input parameter 'p_wage_plan_code'
59 REM for the function 'get_ca_xml_wages'
60 REM ========================================================================
61
62 CREATE OR REPLACE PACKAGE pay_us_sqwl_udf as
63 */
64
65 FUNCTION get_qtr_hire_flag(p_emp_per_hire_date DATE, p_transfer_date DATE)
66 RETURN VARCHAR2;
67
68
69 FUNCTION get_gre_wage_plan_code
70 (
71 p_tax_unit_id in number,
72 p_transfer_state in varchar
73 )
74 RETURN VARCHAR2;
75
76
77 FUNCTION get_asg_wage_plan_code
78 (
79 p_assignment_id in number,
80 p_transfer_state in varchar
81 )
82 RETURN VARCHAR2;
83
84
85 PROCEDURE chk_for_default_wp ( p_organization_id number,
86 p_org_information_context varchar2,
87 p_org_information1 varchar2
88 );
89
90
91 FUNCTION chk_govt_employer ( p_tax_unit_id number DEFAULT NULL,
92 p_assignment_action_id number DEFAULT NULL
93 ) RETURN BOOLEAN;
94
95 FUNCTION get_employment_code ( p_medicare_wh number DEFAULT NULL,
96 p_ss_wh number DEFAULT NULL
97 ) RETURN varchar2;
98
99 FUNCTION get_archived_emp_code ( p_assignment_action_id number DEFAULT NULL
100 ) RETURN varchar2;
101
102 /*Added for Bug#9356178*/
103 FUNCTION get_out_of_state_code ( p_assignment_action_id number,
104 p_assignment_id number,
105 p_tax_unit_id number,
106 p_reporting_date date,
107 p_out_of_state_taxable IN OUT nocopy number
108 ) RETURN varchar2;
109
110 /* Added for Bug 9356247 */
111 FUNCTION get_reporting_unit_no ( p_assignment_action_id number
112 ) RETURN varchar2;
113
114 /* Added for Bug 14368821 */
115 FUNCTION get_ar_out_of_state_code ( p_assignment_action_id number
116 ) RETURN varchar2;
117
118 /* Bug 14008507 start */
119 /* Function Name : Get_Employer_Count
120 Purpose : Purpose of this function is to get the Number of Employers for the
121 State. Created for getting data in SQWL Output.
122
123 Special Note :
124
125 */
126
127 FUNCTION get_employer_count( p_payroll_action_id number, --context
128 p_state varchar2 default null
129 ) RETURN number ;
130
131
132 /* Function Name : Get_Employer_Total_Wages
133 Purpose : Purpose of this function is to get the Total Wages (State Specific)
134 to be reported in SQWL Magtape.
135
136 Special Note :
137
138 */
139
140 FUNCTION get_employer_total_wages( p_payroll_action_id number, --context
141 p_state varchar2
142 ) RETURN number ;
143
144 /* Function Name : Get_Excess_Wages
145 Purpose : Purpose of this function is to get the Excess Wages (State Specific)
146 to be reported in SQWL Magtape.
147
148 Special Note :
149
150 */
151
152 FUNCTION get_excess_wages( p_payroll_action_id number, --context
153 p_tax_unit_id number, --context
154 p_state varchar2
155 ) RETURN number ;
156 /* Bug 14008507 end */
157
158 /* Function Name : get_smwl_sui_wages
159 Purpose : this will return the SUI wages per month
160 Added for Bug 14456648
161 */
162
163 function get_smwl_sui_wages(p_payroll_action_id number, --context
164 p_tax_unit_id number, --context
165 p_state varchar2,
166 p_sui_gross out nocopy number,
167 p_sui_subj out nocopy number,
168 p_sui_pre_tax out nocopy number,
169 p_sui_taxable out nocopy number
170 )return number;
171
172
173 /* Function Name : get_ca_sqwl_wages
174 Purpose : This Function will return Employer Wages for the State CA
175 Added for Bug 14345202
176 */
177
178 function get_ca_sqwl_wages(p_payroll_action_id number, --context
179 p_tax_unit_id number, --context
180 p_wage_plan_code in varchar2,
181 p_sui_subj out nocopy number,
182 p_sui_pre_tax out nocopy number,
183 p_sui_taxable out nocopy number,
184 p_sui_gross out nocopy number,
185 p_sit_subj_nwhable out nocopy number,
186 p_sit_subj_whable out nocopy number,
187 p_sit_pre_tax out nocopy number,
188 p_sit_withheld out nocopy number,
189 p_sdi_pre_tax out nocopy number,
190 p_sdi_subj_whable out nocopy number,
191 p_sdi_taxable out nocopy number
192 )return number;
193
194
195 /* Function Name : spl_char_check
196 Purpose : This Function will remove special characters from the string
197 Added for Bug 14345202
198 */
199 FUNCTION spl_char_check(p_value IN varchar2,
200 p_state IN varchar2,
201 p_report_format IN varchar2)
202 return varchar2;
203
204 END pay_us_sqwl_udf;
205
206
207
208
[Home] [Help]