[Home] [Help]
PACKAGE BODY: APPS.PQP_ATD_SHD
Source
1 PACKAGE BODY pqp_atd_shd as
2 /* $Header: pqatdrhi.pkb 115.10 2003/02/17 22:13:56 tmehra ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package VARCHAR2(33) := ' pqp_atd_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 FUNCTION return_api_dml_status RETURN BOOLEAN IS
14 --
15 l_proc varchar2(72) := g_package||'return_api_dml_status';
16 --
17 BEGIN
18 hr_utility.set_location('Entering:'||l_proc, 5);
19 --
20 Return (nvl(g_api_dml, false));
21 --
22 hr_utility.set_location(' Leaving:'||l_proc, 10);
23 END return_api_dml_status;
24 --
25 -- ----------------------------------------------------------------------------
26 -- |---------------------------< constraint_error >---------------------------|
27 -- ----------------------------------------------------------------------------
28 PROCEDURE constraint_error
29 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
30 --
31 l_proc varchar2(72) := g_package||'constraint_error';
32 --
33 BEGIN
34 hr_utility.set_location('Entering:'||l_proc, 5);
35 --
36 IF (p_constraint_name = 'PQP_ALIEN_TRANSACTION_DATA_PK') Then
37 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
38 hr_utility.set_message_token('PROCEDURE', l_proc);
39 hr_utility.set_message_token('STEP','5');
40 hr_utility.raise_error;
41 ELSE
42 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
43 hr_utility.set_message_token('PROCEDURE', l_proc);
44 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
45 hr_utility.raise_error;
46 END IF;
47 --
48 hr_utility.set_location(' Leaving:'||l_proc, 10);
49 END constraint_error;
50 --
51 -- ----------------------------------------------------------------------------
52 -- |-----------------------------< api_updating >-----------------------------|
53 -- ----------------------------------------------------------------------------
54 FUNCTION api_updating
55 (
56 p_alien_transaction_id IN NUMBER,
57 p_object_version_number IN NUMBER
58 ) RETURN BOOLEAN IS
59 --
60 --
61 -- Cursor selects the 'current' row from the HR Schema
62 --
63 Cursor C_Sel1 is
64 select
65 alien_transaction_id,
66 person_id,
67 data_source_type,
68 tax_year,
69 income_code,
70 withholding_rate,
71 income_code_sub_type,
72 exemption_code,
73 maximum_benefit_amount,
74 retro_lose_ben_amt_flag,
75 date_benefit_ends,
76 retro_lose_ben_date_flag,
77 current_residency_status,
78 nra_to_ra_date,
79 target_departure_date,
80 tax_residence_country_code,
81 treaty_info_update_date,
82 nra_exempt_from_fica,
83 student_exempt_from_fica,
84 addl_withholding_flag,
85 addl_withholding_amt,
86 addl_wthldng_amt_period_type,
87 personal_exemption,
88 addl_exemption_allowed,
89 number_of_days_in_usa,
90 wthldg_allow_eligible_flag,
91 treaty_ben_allowed_flag,
92 treaty_benefits_start_date,
93 ra_effective_date,
94 state_code,
95 state_honors_treaty_flag,
96 ytd_payments,
97 ytd_w2_payments,
98 ytd_w2_withholding,
99 ytd_withholding_allowance,
100 ytd_treaty_payments,
101 ytd_treaty_withheld_amt,
102 record_source,
103 visa_type,
104 j_sub_type,
105 primary_activity,
106 non_us_country_code,
107 citizenship_country_code,
108 constant_addl_tax,
109 date_8233_signed,
110 date_w4_signed,
111 error_indicator,
112 prev_er_treaty_benefit_amt,
113 error_text,
114 object_version_number ,
115 current_analysis,
116 forecast_income_code
117 from pqp_alien_transaction_data
118 where alien_transaction_id = p_alien_transaction_id;
119 --
120 l_proc varchar2(72) := g_package||'api_updating';
121 l_fct_ret boolean;
122 --
123 Begin
124 hr_utility.set_location('Entering:'||l_proc, 5);
125 --
126 If (
127 p_alien_transaction_id is null and
128 p_object_version_number is null
129 ) Then
130 --
131 -- One of the primary key arguments is null therefore we must
132 -- set the returning function value to false
133 --
134 l_fct_ret := false;
135 Else
136 If (
137 p_alien_transaction_id = g_old_rec.alien_transaction_id and
138 p_object_version_number = g_old_rec.object_version_number
139 ) Then
140 hr_utility.set_location(l_proc, 10);
141 --
142 -- The g_old_rec is current therefore we must
143 -- set the returning function to true
144 --
145 l_fct_ret := true;
146 Else
147 --
148 -- Select the current row into g_old_rec
149 --
150 Open C_Sel1;
151 Fetch C_Sel1 Into g_old_rec;
152 If C_Sel1%notfound Then
153 Close C_Sel1;
154 --
155 -- The primary key is invalid therefore we must error
156 --
157 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
158 hr_utility.raise_error;
159 End If;
160 Close C_Sel1;
161 If (p_object_version_number <> g_old_rec.object_version_number) Then
162 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
163 hr_utility.raise_error;
164 End If;
165 hr_utility.set_location(l_proc, 15);
166 l_fct_ret := true;
167 End If;
168 End If;
169 hr_utility.set_location(' Leaving:'||l_proc, 20);
170 Return (l_fct_ret);
171 --
172 End api_updating;
173 --
174 -- ----------------------------------------------------------------------------
175 -- |---------------------------------< lck >----------------------------------|
176 -- ----------------------------------------------------------------------------
177 Procedure lck
178 (
179 p_alien_transaction_id in number,
180 p_object_version_number in number
181 ) is
182 --
183 -- Cursor selects the 'current' row from the HR Schema
184 --
185 Cursor C_Sel1 is
186 select alien_transaction_id,
187 person_id,
188 data_source_type,
189 tax_year,
190 income_code,
191 withholding_rate,
192 income_code_sub_type,
193 exemption_code,
194 maximum_benefit_amount,
195 retro_lose_ben_amt_flag,
196 date_benefit_ends,
197 retro_lose_ben_date_flag,
198 current_residency_status,
199 nra_to_ra_date,
200 target_departure_date,
201 tax_residence_country_code,
202 treaty_info_update_date,
203 nra_exempt_from_fica,
204 student_exempt_from_fica,
205 addl_withholding_flag,
206 addl_withholding_amt,
207 addl_wthldng_amt_period_type,
208 personal_exemption,
209 addl_exemption_allowed,
210 number_of_days_in_usa,
211 wthldg_allow_eligible_flag,
212 treaty_ben_allowed_flag,
213 treaty_benefits_start_date,
214 ra_effective_date,
215 state_code,
216 state_honors_treaty_flag,
217 ytd_payments,
218 ytd_w2_payments,
219 ytd_w2_withholding,
220 ytd_withholding_allowance,
221 ytd_treaty_payments,
222 ytd_treaty_withheld_amt,
223 record_source,
224 visa_type,
225 j_sub_type,
226 primary_activity,
227 non_us_country_code,
228 citizenship_country_code,
229 constant_addl_tax,
230 date_8233_signed,
231 date_w4_signed,
232 error_indicator,
233 prev_er_treaty_benefit_amt,
234 error_text,
235 object_version_number,
236 current_analysis,
237 forecast_income_code
238 from pqp_alien_transaction_data
239 where alien_transaction_id = p_alien_transaction_id
240 for update nowait;
241 --
242 l_proc varchar2(72) := g_package||'lck';
243 --
244 Begin
245 hr_utility.set_location('Entering:'||l_proc, 5);
246 --
247 -- Add any mandatory argument checking here:
248 -- Example:
249 -- hr_api.mandatory_arg_error
250 -- (p_api_name => l_proc,
251 -- p_argument => 'object_version_number',
252 -- p_argument_value => p_object_version_number);
253 --
254 Open C_Sel1;
255 Fetch C_Sel1 Into g_old_rec;
256 If C_Sel1%notfound then
257 Close C_Sel1;
258 --
259 -- The primary key is invalid therefore we must error
260 --
261 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
262 hr_utility.raise_error;
263 End If;
264 Close C_Sel1;
265 If (p_object_version_number <> g_old_rec.object_version_number) Then
266 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
267 hr_utility.raise_error;
268 End If;
269 --
270 hr_utility.set_location(' Leaving:'||l_proc, 10);
271 --
272 -- We need to trap the ORA LOCK exception
273 --
274 Exception
275 When HR_Api.Object_Locked then
276 --
277 -- The object is locked therefore we need to supply a meaningful
278 -- error message.
279 --
280 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
281 hr_utility.set_message_token('TABLE_NAME', 'pqp_alien_transaction_data');
282 hr_utility.raise_error;
283 End lck;
284 --
285 -- ----------------------------------------------------------------------------
286 -- |-----------------------------< convert_args >-----------------------------|
287 -- ----------------------------------------------------------------------------
288 Function convert_args
289 (
290 p_alien_transaction_id in number,
291 p_person_id in number,
292 p_data_source_type in varchar2,
293 p_tax_year in number,
294 p_income_code in varchar2,
295 p_withholding_rate in number,
296 p_income_code_sub_type in varchar2,
297 p_exemption_code in varchar2,
298 p_maximum_benefit_amount in number,
299 p_retro_lose_ben_amt_flag in varchar2,
300 p_date_benefit_ends in date,
301 p_retro_lose_ben_date_flag in varchar2,
302 p_current_residency_status in varchar2,
303 p_nra_to_ra_date in date,
304 p_target_departure_date in date,
305 p_tax_residence_country_code in varchar2,
306 p_treaty_info_update_date in date,
307 p_nra_exempt_from_fica in varchar2,
308 p_student_exempt_from_fica in varchar2,
309 p_addl_withholding_flag in varchar2,
310 p_addl_withholding_amt in number,
311 p_addl_wthldng_amt_period_type in varchar2,
312 p_personal_exemption in number,
313 p_addl_exemption_allowed in number,
314 p_number_of_days_in_usa in number,
315 p_wthldg_allow_eligible_flag in varchar2,
316 p_treaty_ben_allowed_flag in varchar2,
317 p_treaty_benefits_start_date in date,
318 p_ra_effective_date in date,
319 p_state_code in varchar2,
320 p_state_honors_treaty_flag in varchar2,
321 p_ytd_payments in number,
322 p_ytd_w2_payments in number,
323 p_ytd_w2_withholding in number,
324 p_ytd_withholding_allowance in number,
325 p_ytd_treaty_payments in number,
326 p_ytd_treaty_withheld_amt in number,
327 p_record_source in varchar2,
328 p_visa_type in varchar2,
329 p_j_sub_type in varchar2,
330 p_primary_activity in varchar2,
331 p_non_us_country_code in varchar2,
332 p_citizenship_country_code in varchar2,
333 p_constant_addl_tax in number,
334 p_date_8233_signed in date,
335 p_date_w4_signed in date,
336 p_error_indicator in varchar2,
337 p_prev_er_treaty_benefit_amt in number,
338 p_error_text in varchar2,
339 p_object_version_number in number,
340 p_current_analysis in varchar2,
341 p_forecast_income_code in varchar2
342 )
343 Return g_rec_type is
344 --
345 l_rec g_rec_type;
346 l_proc varchar2(72) := g_package||'convert_args';
347 --
348 Begin
349 --
350 hr_utility.set_location('Entering:'||l_proc, 5);
351 --
352 -- Convert arguments into local l_rec structure.
353 --
354 l_rec.alien_transaction_id := p_alien_transaction_id;
355 l_rec.person_id := p_person_id;
356 l_rec.data_source_type := p_data_source_type;
357 l_rec.tax_year := p_tax_year;
358 l_rec.income_code := p_income_code;
359 l_rec.withholding_rate := p_withholding_rate;
360 l_rec.income_code_sub_type := p_income_code_sub_type;
361 l_rec.exemption_code := p_exemption_code;
362 l_rec.maximum_benefit_amount := p_maximum_benefit_amount;
363 l_rec.retro_lose_ben_amt_flag := p_retro_lose_ben_amt_flag;
364 l_rec.date_benefit_ends := p_date_benefit_ends;
365 l_rec.retro_lose_ben_date_flag := p_retro_lose_ben_date_flag;
366 l_rec.current_residency_status := p_current_residency_status;
367 l_rec.nra_to_ra_date := p_nra_to_ra_date;
368 l_rec.target_departure_date := p_target_departure_date;
369 l_rec.tax_residence_country_code := p_tax_residence_country_code;
370 l_rec.treaty_info_update_date := p_treaty_info_update_date;
371 l_rec.nra_exempt_from_fica := p_nra_exempt_from_fica;
372 l_rec.student_exempt_from_fica := p_student_exempt_from_fica;
373 l_rec.addl_withholding_flag := p_addl_withholding_flag;
374 l_rec.addl_withholding_amt := p_addl_withholding_amt;
375 l_rec.addl_wthldng_amt_period_type := p_addl_wthldng_amt_period_type;
376 l_rec.personal_exemption := p_personal_exemption;
377 l_rec.addl_exemption_allowed := p_addl_exemption_allowed;
378 l_rec.number_of_days_in_usa := p_number_of_days_in_usa;
379 l_rec.wthldg_allow_eligible_flag := p_wthldg_allow_eligible_flag;
380 l_rec.treaty_ben_allowed_flag := p_treaty_ben_allowed_flag;
381 l_rec.treaty_benefits_start_date := p_treaty_benefits_start_date;
382 l_rec.ra_effective_date := p_ra_effective_date;
383 l_rec.state_code := p_state_code;
384 l_rec.state_honors_treaty_flag := p_state_honors_treaty_flag;
385 l_rec.ytd_payments := p_ytd_payments;
386 l_rec.ytd_w2_payments := p_ytd_w2_payments;
387 l_rec.ytd_w2_withholding := p_ytd_w2_withholding;
388 l_rec.ytd_withholding_allowance := p_ytd_withholding_allowance;
389 l_rec.ytd_treaty_payments := p_ytd_treaty_payments;
390 l_rec.ytd_treaty_withheld_amt := p_ytd_treaty_withheld_amt;
391 l_rec.record_source := p_record_source;
392 l_rec.visa_type := p_visa_type;
393 l_rec.j_sub_type := p_j_sub_type;
394 l_rec.primary_activity := p_primary_activity;
395 l_rec.non_us_country_code := p_non_us_country_code;
396 l_rec.citizenship_country_code := p_citizenship_country_code;
397 l_rec.constant_addl_tax := p_constant_addl_tax;
398 l_rec.date_8233_signed := p_date_8233_signed;
399 l_rec.date_w4_signed := p_date_w4_signed;
400 l_rec.error_indicator := p_error_indicator;
401 l_rec.prev_er_treaty_benefit_amt := p_prev_er_treaty_benefit_amt;
402 l_rec.error_text := p_error_text;
403 l_rec.object_version_number := p_object_version_number;
404 l_rec.current_analysis := p_current_analysis;
405 l_rec.forecast_income_code := p_forecast_income_code;
406 --
407 -- Return the plsql record structure.
408 --
409 hr_utility.set_location(' Leaving:'||l_proc, 10);
410 Return(l_rec);
411 --
412 End convert_args;
413 --
414 end pqp_atd_shd;