1 Package pqp_atd_shd as
2 /* $Header: pqatdrhi.pkh 120.0.12010000.1 2008/07/28 11:08:00 appldev ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (
10 alien_transaction_id number(15),
11 person_id number(10), -- Added by Ashu Gupta
12 data_source_type varchar2(30),
13 tax_year number(9), -- Increased length
14 income_code varchar2(30),
15 withholding_rate number(11,2),
16 income_code_sub_type varchar2(30),
17 exemption_code varchar2(30),
18 maximum_benefit_amount number(11,2),
19 retro_lose_ben_amt_flag varchar2(30),
20 date_benefit_ends date,
21 retro_lose_ben_date_flag varchar2(30),
22 current_residency_status varchar2(30),
23 nra_to_ra_date date,
24 target_departure_date date,
25 tax_residence_country_code varchar2(30),
26 treaty_info_update_date date,
27 nra_exempt_from_fica varchar2(30),
28 student_exempt_from_fica varchar2(30),
29 addl_withholding_flag varchar2(30),
30 addl_withholding_amt number(11,2),
31 addl_wthldng_amt_period_type varchar2(30),
32 personal_exemption number(9), -- Increased length
33 addl_exemption_allowed number(9), -- Increased length
34 number_of_days_in_usa number(9), -- Increased length
35 wthldg_allow_eligible_flag varchar2(30),
36 treaty_ben_allowed_flag varchar2(30),
37 treaty_benefits_start_date date,
38 ra_effective_date date,
39 state_code varchar2(30),
40 state_honors_treaty_flag varchar2(30),
41 ytd_payments number(11,2),
42 ytd_w2_payments number(11,2),
43 ytd_w2_withholding number(11,2),
44 ytd_withholding_allowance number(11,2),
45 ytd_treaty_payments number(11,2),
46 ytd_treaty_withheld_amt number(11,2),
47 record_source varchar2(30),
48 visa_type varchar2(30),
49 j_sub_type varchar2(30),
50 primary_activity varchar2(30),
51 non_us_country_code varchar2(30),
52 citizenship_country_code varchar2(30),
53 constant_addl_tax number(11,2),
54 date_8233_signed date,
55 date_w4_signed date,
56 error_indicator varchar2(30),
57 prev_er_treaty_benefit_amt number(11,2),
58 error_text varchar2(4000),
59 object_version_number number(15),
60 current_analysis varchar2(30),
61 forecast_income_code varchar2(30)
62
63 );
64 --
65 -- ----------------------------------------------------------------------------
66 -- | Global Definitions - Internal Development Use Only |
67 -- ----------------------------------------------------------------------------
68 --
69 g_old_rec g_rec_type; -- Global record definition
70 g_api_dml boolean; -- Global api dml status
71 --
72 -- ----------------------------------------------------------------------------
73 -- |------------------------< return_api_dml_status >-------------------------|
74 -- ----------------------------------------------------------------------------
75 -- {Start Of Comments}
76 --
77 -- Description:
78 -- This function will return the current g_api_dml private global
79 -- boolean status.
80 -- The g_api_dml status determines if at the time of the function
81 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
82 -- is being issued from within an api.
83 -- If the status is TRUE then a dml statement is being issued from
84 -- within this entity api.
85 -- This function is primarily to support database triggers which
86 -- need to maintain the object_version_number for non-supported
87 -- dml statements (i.e. dml statement issued outside of the api layer).
88 --
89 -- Prerequisites:
90 -- None.
91 --
92 -- In Parameters:
93 -- None.
94 --
95 -- Post Success:
96 -- Processing continues.
97 -- If the function returns a TRUE value then, dml is being executed from
98 -- within this api.
99 --
100 -- Post Failure:
101 -- None.
102 --
103 -- Access Status:
104 -- Internal Row Handler Use Only.
105 --
106 -- {End Of Comments}
107 -- ----------------------------------------------------------------------------
108 Function return_api_dml_status Return Boolean;
109 --
110 -- ----------------------------------------------------------------------------
111 -- |---------------------------< constraint_error >---------------------------|
112 -- ----------------------------------------------------------------------------
113 -- {Start Of Comments}
114 --
115 -- Description:
116 -- This procedure is called when a constraint has been violated (i.e.
117 -- The exception hr_api.check_integrity_violated,
118 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
119 -- hr_api.unique_integrity_violated has been raised).
120 -- The exceptions can only be raised as follows:
121 -- 1) A check constraint can only be violated during an INSERT or UPDATE
122 -- dml operation.
123 -- 2) A parent integrity constraint can only be violated during an
124 -- INSERT or UPDATE dml operation.
125 -- 3) A child integrity constraint can only be violated during an
126 -- DELETE dml operation.
127 -- 4) A unique integrity constraint can only be violated during INSERT or
128 -- UPDATE dml operation.
129 --
130 -- Prerequisites:
131 -- 1) Either hr_api.check_integrity_violated,
132 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
133 -- hr_api.unique_integrity_violated has been raised with the subsequent
134 -- stripping of the constraint name from the generated error message
135 -- text.
136 -- 2) Standalone validation test which corresponds with a constraint error.
137 --
138 -- In Parameter:
139 -- p_constraint_name is in upper format and is just the constraint name
140 -- (e.g. not prefixed by brackets, schema owner etc).
141 --
142 -- Post Success:
143 -- Development dependant.
144 --
145 -- Post Failure:
146 -- Developement dependant.
147 --
148 -- Developer Implementation Notes:
149 -- For each constraint being checked the hr system package failure message
150 -- has been generated as a template only. These system error messages should
151 -- be modified as required (i.e. change the system failure message to a user
152 -- friendly defined error message).
153 --
154 -- Access Status:
155 -- Internal Development Use Only.
156 --
157 -- {End Of Comments}
158 -- ----------------------------------------------------------------------------
159 Procedure constraint_error
160 (p_constraint_name in all_constraints.constraint_name%TYPE);
161 --
162 -- ----------------------------------------------------------------------------
163 -- |-----------------------------< api_updating >-----------------------------|
164 -- ----------------------------------------------------------------------------
165 -- {Start Of Comments}
166 --
167 -- Description:
168 -- This function is used to populate the g_old_rec record with the
169 -- current row from the database for the specified primary key
170 -- provided that the primary key exists and is valid and does not
171 -- already match the current g_old_rec. The function will always return
172 -- a TRUE value if the g_old_rec is populated with the current row.
173 -- A FALSE value will be returned if all of the primary key arguments
174 -- are null.
175 --
176 -- Prerequisites:
177 -- None.
178 --
179 -- In Parameters:
180 --
181 -- Post Success:
182 -- A value of TRUE will be returned indiciating that the g_old_rec
183 -- is current.
184 -- A value of FALSE will be returned if all of the primary key arguments
185 -- have a null value (this indicates that the row has not be inserted into
186 -- the Schema), and therefore could never have a corresponding row.
187 --
188 -- Post Failure:
189 -- A failure can only occur under two circumstances:
190 -- 1) The primary key is invalid (i.e. a row does not exist for the
191 -- specified primary key values).
192 -- 2) If an object_version_number exists but is NOT the same as the current
193 -- g_old_rec value.
194 --
195 -- Developer Implementation Notes:
196 -- None.
197 --
198 -- Access Status:
199 -- Internal Development Use Only.
200 --
201 -- {End Of Comments}
202 -- ----------------------------------------------------------------------------
203 Function api_updating
204 (
205 p_alien_transaction_id in number,
206 p_object_version_number in number
207 ) Return Boolean;
208 --
209 -- ----------------------------------------------------------------------------
210 -- |---------------------------------< lck >----------------------------------|
211 -- ----------------------------------------------------------------------------
212 -- {Start Of Comments}
213 --
214 -- Description:
215 -- The Lck process has two main functions to perform. Firstly, the row to be
216 -- updated or deleted must be locked. The locking of the row will only be
217 -- successful if the row is not currently locked by another user.
218 -- Secondly, during the locking of the row, the row is selected into
219 -- the g_old_rec data structure which enables the current row values from the
220 -- server to be available to the api.
221 --
222 -- Prerequisites:
223 -- When attempting to call the lock the object version number (if defined)
224 -- is mandatory.
225 --
226 -- In Parameters:
227 -- The arguments to the Lck process are the primary key(s) which uniquely
228 -- identify the row and the object version number of row.
229 --
230 -- Post Success:
231 -- On successful completion of the Lck process the row to be updated or
232 -- deleted will be locked and selected into the global data structure
233 -- g_old_rec.
234 --
235 -- Post Failure:
236 -- The Lck process can fail for three reasons:
237 -- 1) When attempting to lock the row the row could already be locked by
238 -- another user. This will raise the HR_Api.Object_Locked exception.
239 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
240 -- This error is trapped and reported using the message name
241 -- 'HR_7220_INVALID_PRIMARY_KEY'.
242 -- 3) The row although existing in the HR Schema has a different object
243 -- version number than the object version number specified.
244 -- This error is trapped and reported using the message name
245 -- 'HR_7155_OBJECT_INVALID'.
246 --
247 -- Developer Implementation Notes:
248 -- For each primary key and the object version number arguments add a
249 -- call to hr_api.mandatory_arg_error procedure to ensure that these
250 -- argument values are not null.
251 --
252 -- Access Status:
253 -- Internal Development Use Only.
254 --
255 -- {End Of Comments}
256 -- ----------------------------------------------------------------------------
257 Procedure lck
258 (
259 p_alien_transaction_id in number,
260 p_object_version_number in number
261 );
262 --
263 -- ----------------------------------------------------------------------------
264 -- |-----------------------------< convert_args >-----------------------------|
265 -- ----------------------------------------------------------------------------
266 -- {Start Of Comments}
267 --
268 -- Description:
269 -- This function is used to turn attribute parameters into the record
270 -- structure parameter g_rec_type.
271 --
272 -- Prerequisites:
273 -- This is a private function and can only be called from the ins or upd
274 -- attribute processes.
275 --
276 -- In Parameters:
277 --
278 -- Post Success:
279 -- A returning record structure will be returned.
280 --
281 -- Post Failure:
282 -- No direct error handling is required within this function. Any possible
283 -- errors within this function will be a PL/SQL value error due to conversion
284 -- of datatypes or data lengths.
285 --
286 -- Developer Implementation Notes:
287 -- None.
288 --
289 -- Access Status:
290 -- Internal Row Handler Use Only.
291 --
292 -- {End Of Comments}
293 -- ----------------------------------------------------------------------------
294 Function convert_args
295 (
296 p_alien_transaction_id in number,
297 p_person_id in number, -- Added by Ashu Gupta
298 p_data_source_type in varchar2,
299 p_tax_year in number,
300 p_income_code in varchar2,
301 p_withholding_rate in number,
302 p_income_code_sub_type in varchar2,
303 p_exemption_code in varchar2,
304 p_maximum_benefit_amount in number,
305 p_retro_lose_ben_amt_flag in varchar2,
306 p_date_benefit_ends in date,
307 p_retro_lose_ben_date_flag in varchar2,
308 p_current_residency_status in varchar2,
309 p_nra_to_ra_date in date,
310 p_target_departure_date in date,
311 p_tax_residence_country_code in varchar2,
312 p_treaty_info_update_date in date,
313 p_nra_exempt_from_fica in varchar2,
314 p_student_exempt_from_fica in varchar2,
315 p_addl_withholding_flag in varchar2,
316 p_addl_withholding_amt in number,
317 p_addl_wthldng_amt_period_type in varchar2,
318 p_personal_exemption in number,
319 p_addl_exemption_allowed in number,
320 p_number_of_days_in_usa in number,
321 p_wthldg_allow_eligible_flag in varchar2,
322 p_treaty_ben_allowed_flag in varchar2,
323 p_treaty_benefits_start_date in date,
324 p_ra_effective_date in date,
325 p_state_code in varchar2,
326 p_state_honors_treaty_flag in varchar2,
327 p_ytd_payments in number,
328 p_ytd_w2_payments in number,
329 p_ytd_w2_withholding in number,
330 p_ytd_withholding_allowance in number,
331 p_ytd_treaty_payments in number,
332 p_ytd_treaty_withheld_amt in number,
333 p_record_source in varchar2,
334 p_visa_type in varchar2,
335 p_j_sub_type in varchar2,
336 p_primary_activity in varchar2,
337 p_non_us_country_code in varchar2,
338 p_citizenship_country_code in varchar2,
339 p_constant_addl_tax in number,
340 p_date_8233_signed in date,
341 p_date_w4_signed in date,
342 p_error_indicator in varchar2,
343 p_prev_er_treaty_benefit_amt in number,
344 p_error_text in varchar2,
345 p_object_version_number in number,
346 p_current_analysis in varchar2,
347 p_forecast_income_code in varchar2
348 )
349 Return g_rec_type;
350 --
351 end pqp_atd_shd;