1 PACKAGE pqp_alien_expat_taxation_pkg AUTHID DEFINER AS
2 /* $Header: pqalnexp.pkh 120.0.12010000.1 2008/07/28 11:07:48 appldev ship $ */
3 TYPE t_people_rec_type IS RECORD
4 (
5 person_id per_people_f.person_id%TYPE ,
6 last_name per_people_f.last_name%TYPE ,
7 first_name per_people_f.first_name%TYPE ,
8 middle_names per_people_f.middle_names%TYPE ,
9 national_identifier per_people_f.national_identifier%TYPE ,
10 employee_number per_people_f.employee_number%TYPE ,
11 date_of_birth per_people_f.date_of_birth%TYPE ,
12 title per_people_f.title%TYPE ,
13 suffix per_people_f.suffix%TYPE ,
14 marital_status per_people_f.marital_status%TYPE ,
15 spouse_here VARCHAR2(30) ,
16 dependents NUMBER ,
17 address_line1 hr_locations.address_line_1%TYPE ,
18 address_line2 hr_locations.address_line_2%TYPE ,
19 address_line3 hr_locations.address_line_3%TYPE ,
20 city hr_locations.loc_information17%TYPE ,
21 state hr_locations.loc_information18%TYPE ,
22 telephone_number_1 hr_locations.telephone_number_1%TYPE ,
23 telephone_number_2 hr_locations.telephone_number_2%TYPE ,
24 telephone_number_3 hr_locations.telephone_number_3%TYPE ,
25 postal_code hr_locations.postal_code%TYPE ,
26 non_us_address_line1 per_addresses.address_line1%TYPE ,
27 non_us_address_line2 per_addresses.address_line2%TYPE ,
28 non_us_address_line3 per_addresses.address_line3%TYPE ,
29 non_us_city_postal_cd per_addresses.postal_code%TYPE ,
30 non_us_city per_addresses.town_or_city%TYPE ,
31 non_us_region per_addresses.region_1%TYPE ,
32 non_us_region_postal_cd per_addresses.region_2%TYPE ,
33 non_us_country_code VARCHAR2(100) ,
34 non_us_country_name VARCHAR2(100) ,
35 citizenship_c_code VARCHAR2(100) ,
36 citizenship_c_name VARCHAR2(100) ,
37 passport_number VARCHAR2(100) ,
38 date_first_entered_us DATE ,
39 date_employment_us DATE ,
40 validation_flag VARCHAR2(2) ,
41 error_mesg VARCHAR2(240)
42 );
43
44 /*****
45 The above Record Type t_people_rec_type stores the personal info data
46 *****/
47
48 TYPE t_balance_rec_type IS RECORD
49 (
50 person_id per_people_f.person_id%TYPE ,
51 last_name per_people_f.last_name%TYPE ,
52 first_name per_people_f.first_name%TYPE ,
53 middle_names per_people_f.middle_names%TYPE ,
54 national_identifier per_people_f.national_identifier%TYPE ,
55 date_of_birth per_people_f.date_of_birth%TYPE ,
56 income_code pqp_analyzed_alien_details.income_code%TYPE,
57 exemption_code pqp_analyzed_alien_details.exemption_code%TYPE,
58 gross_amount NUMBER ,
59 withholding_allowance
60 pqp_analyzed_alien_data.withldg_allow_eligible_flag%TYPE,
61 withholding_rate pqp_analyzed_alien_details.withholding_rate%TYPE,
62 withheld_amount NUMBER ,
63 income_code_sub_type pqp_analyzed_alien_details.income_code_sub_type%TYPE,
64 country_code VARCHAR2(100) ,
65 cycle_date VARCHAR2(100) ,
66 tax_year pqp_analyzed_alien_data.tax_year%TYPE ,
67 state_withheld_amount NUMBER ,
68 state_code VARCHAR2(100) ,
69 record_source VARCHAR2(100) ,
70 no_of_days_in_cycle NUMBER ,
71 payment_type VARCHAR2(100) ,
72 last_date_of_earnings DATE ,
73 constant_addl_tax pqp_analyzed_alien_details.constant_addl_tax%TYPE,
74 record_status VARCHAR2(100) ,
75 prev_er_treaty_benefit_amount NUMBER
76 );
77
78 /*****
79 The above Record Type t_balance_rec_type stores the data the balance
80 information
81 *****/
82
83 TYPE t_visa_rec_type IS RECORD
84 (
85 person_id per_people_f.person_id%TYPE ,
86 last_name per_people_f.last_name%TYPE ,
87 first_name per_people_f.first_name%TYPE ,
88 middle_names per_people_f.middle_names%TYPE ,
89 national_identifier per_people_f.national_identifier%TYPE ,
90 date_of_birth per_people_f.date_of_birth%TYPE ,
91 visa_type VARCHAR2(100) ,
92 j_category_code VARCHAR2(100) ,
93 primary_activity_code VARCHAR2(100) ,
94 visa_start_date DATE ,
95 visa_end_date DATE ,
96 visa_number VARCHAR2(100) ,
97 tax_residence_country_code VARCHAR2(100)
98 );
99
100 /*****
101 The above Record Type stores the data selected of the visa information
102 *****/
103 TYPE t_payment_export_type IS RECORD
104 ( id per_people_f.person_id%TYPE ,
105 last_name per_people_f.last_name%TYPE ,
106 first_name per_people_f.first_name%TYPE ,
107 middle_names per_people_f.middle_names%TYPE ,
108 system_id_number NUMBER ,
109 social_security_number VARCHAR2(11) ,
110 institution_indiv_id VARCHAR2(15) ,
111 date_of_birth DATE ,
112 taxyear NUMBER ,
113 income_code VARCHAR2(2) ,
114 withholding_rate VARCHAR2(3) ,
115 scholarship_type VARCHAR2(1) ,
116 exemption_code VARCHAR2(1) ,
117 maximum_benefit NUMBER ,
118 retro_lose_on_amount NUMBER ,
119 date_benefit_ends DATE ,
120 retro_lose_on_date NUMBER ,
121 residency_status VARCHAR2(1) ,
122 date_becomes_ra DATE ,
123 target_departure_date DATE ,
124 date_record_created DATE ,
125 tax_residence_country_code VARCHAR2(2) ,
126 date_treaty_updated DATE ,
127 exempt_fica NUMBER ,
128 exempt_student_fica NUMBER ,
129 add_wh_for_nra_whennotreaty NUMBER ,
130 amount_of_addl_withholding NUMBER ,
131 personal_exemption VARCHAR2(1) ,
132 additional_exemptions_allowed NUMBER ,
133 days_in_usa NUMBER ,
134 eligible_for_whallowance NUMBER ,
135 treatybenefits_allowed NUMBER ,
136 treatybenefit_startdate DATE ,
137 ra_effective_date DATE ,
138 state_code VARCHAR2(2) ,
139 state_honours_treaty NUMBER ,
140 ytd_payments NUMBER ,
141 ytd_w2payments NUMBER ,
142 ytd_withholding NUMBER ,
143 ytd_whallowance NUMBER ,
144 ytd_treaty_payments NUMBER ,
145 ytd_treaty_withheld_amts NUMBER ,
146 record_source VARCHAR2(3) ,
147 visa_type VARCHAR2(4) ,
148 jsub_type VARCHAR2(2) ,
149 primary_activity VARCHAR2(2) ,
150 nus_countrycode VARCHAR2(2) ,
151 citizenship VARCHAR2(2) ,
152 constant_additional_tax NUMBER ,
153 out_of_system_treaty NUMBER ,
154 amount_of_addl_wh_type VARCHAR2(1) ,
155 error_indicator VARCHAR2(30) ,
156 error_text VARCHAR2(4000),
157 date_w4_signed DATE ,
158 date_8233_signed DATE
159 );
160
161 SUBTYPE out_mesg_type IS VARCHAR2(240);
162 /*****
163 The above Record Type stores the message for error processing
164 *****/
165 TYPE t_error_rec_type IS RECORD
166 (
167 person_id per_people_f.person_id%TYPE ,
168 process_event_id pay_process_events.process_event_id%TYPE
169 );
170
171 TYPE t_error_tab_type IS TABLE OF t_error_rec_type INDEX BY BINARY_INTEGER;
172
173 TYPE t_people_tab_type IS TABLE OF t_people_rec_type INDEX BY BINARY_INTEGER;
174 /*****
175 This is the definition of the table of the t_people_rec_type record type
176 *****/
177
178 TYPE t_balance_tab_type IS TABLE OF t_balance_rec_type INDEX BY BINARY_INTEGER;
179 /*****
180 This is the definition of the table of the t_per_balance_rec_type record type
181 *****/
182 TYPE t_visa_tab_type IS TABLE OF t_visa_rec_type INDEX BY BINARY_INTEGER ;
183
184 /*****
185 This is the definition of the table of the t_per_visa_rec_type record type
186 *****/
187 PROCEDURE pqp_read_public
188 (
189 p_selection_criterion IN VARCHAR2 DEFAULT NULL ,
190 p_effective_date IN DATE ,
191 p_batch_size OUT NOCOPY NUMBER ,
192 t_people_tab OUT NOCOPY t_people_tab_type ,
193 t_balance_tab OUT NOCOPY t_balance_tab_type ,
194 t_visa_tab OUT NOCOPY t_visa_tab_type ,
195 p_person_read_count OUT NOCOPY NUMBER ,
196 p_person_err_count OUT NOCOPY NUMBER
197 );
198 /*****
199 The above procedure pqp_windstar_read is a public procedure and is called
200 from the wrapper script.
201 *****/
202 PROCEDURE update_pay_process_events
203 (
204 p_person_id IN NUMBER ,
205 p_effective_date IN DATE ,
206 p_source_type IN VARCHAR2 ,
207 p_status IN VARCHAR2 ,
208 p_desc IN VARCHAR2
209 );
210
211 PROCEDURE pqp_write_public
212 (
213 p_id IN NUMBER ,
214 p_last_name IN VARCHAR2 ,
215 p_first_name IN VARCHAR2 ,
216 p_middle_names IN VARCHAR2 ,
217 p_system_id_number IN NUMBER ,
218 p_social_security_number IN VARCHAR2 ,
219 p_institution_indiv_id IN VARCHAR2 ,
220 p_date_of_birth IN DATE ,
221 p_taxyear IN NUMBER ,
222 p_income_code IN VARCHAR2 ,
223 p_withholding_rate IN VARCHAR2 ,
224 p_scholarship_type IN VARCHAR2 ,
225 p_exemption_code IN VARCHAR2 ,
226 p_maximum_benefit IN NUMBER ,
227 p_retro_lose_on_amount IN NUMBER ,
228 p_date_benefit_ends IN DATE ,
229 p_retro_lose_on_date IN NUMBER ,
230 p_residency_status IN VARCHAR2 ,
231 p_date_becomes_ra IN DATE ,
232 p_target_departure_date IN DATE ,
233 p_date_record_created IN DATE ,
234 p_tax_residence_country_code IN VARCHAR2 ,
235 p_date_treaty_updated IN DATE ,
236 p_exempt_fica IN NUMBER ,
237 p_exempt_student_fica IN NUMBER ,
238 p_add_wh_for_nra_whennotreaty IN NUMBER ,
239 p_amount_of_addl_withholding IN NUMBER ,
240 p_personal_exemption IN VARCHAR2 ,
241 p_add_exemptions_allowed IN NUMBER ,
242 p_days_in_usa IN NUMBER ,
243 p_eligible_for_whallowance IN NUMBER ,
244 p_treatybenefits_allowed IN NUMBER ,
245 p_treatybenefit_startdate IN DATE ,
246 p_ra_effective_date IN DATE ,
247 p_state_code IN VARCHAR2 ,
248 p_state_honours_treaty IN NUMBER ,
249 p_ytd_payments IN NUMBER ,
250 p_ytd_w2payments IN NUMBER ,
251 p_ytd_withholding IN NUMBER ,
252 p_ytd_whallowance IN NUMBER ,
253 p_ytd_treaty_payments IN NUMBER ,
254 p_ytd_treaty_withheld_amts IN NUMBER ,
255 p_record_source IN VARCHAR2 ,
256 p_visa_type IN VARCHAR2 ,
257 p_jsub_type IN VARCHAR2 ,
258 p_primary_activity IN VARCHAR2 ,
259 p_nus_countrycode IN VARCHAR2 ,
260 p_citizenship IN VARCHAR2 ,
261 p_constant_additional_tax IN NUMBER ,
262 p_out_of_system_treaty IN NUMBER ,
263 p_amount_of_addl_wh_type IN VARCHAR2 ,
264 p_error_indicator IN VARCHAR2 ,
265 p_error_text IN VARCHAR2 ,
266 p_date_w4_signed IN DATE ,
267 p_date_8233_signed IN DATE ,
268 p_reconcile IN BOOLEAN ,
269 p_effective_date IN DATE ,
270 p_current_analysis IN NUMBER ,
271 p_forecast_income_code IN VARCHAR2 ,
272 p_error_message OUT NOCOPY VARCHAR2
273 );
274
275 PROCEDURE pqp_batch_size(p_batch_size OUT NOCOPY NUMBER);
276
277 /*****
278 The above procedure pqp_windstar_write is a public procedure and is called
279 from the wrapper script. The p_payment_export_id is NOT a Mandatory parameter.
280 If a null value is passed, then all the records with a rec_read_by_external_sys = 0 in payment_export table are selected. Otherwise, if the p_payment_export_id
281 is specified, then only the record with payment_export.id = one that is
282 specified is selected.
283 *****/
284 PROCEDURE ResetForReadAPI(p_process_event_id IN NUMBER);
285 PROCEDURE AbortReadAPI(p_process_event_id IN NUMBER);
286 END pqp_alien_expat_taxation_pkg;
[Home] [Help]