[Home] [Help]
PACKAGE BODY: APPS.IRC_IOF_SHD
Source
1 Package Body irc_iof_shd as
2 /* $Header: iriofrhi.pkb 120.20 2011/04/08 12:04:09 amikukum ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_iof_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE
15 ) Is
16 --
17 l_proc varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20 --
21 If (p_constraint_name = 'IRC_OFFERS_FK1') Then
22 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
23 fnd_message.set_token('PROCEDURE', l_proc);
24 fnd_message.set_token('STEP','5');
25 fnd_message.raise_error;
26 ElsIf (p_constraint_name = 'IRC_OFFERS_FK2') Then
27 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
28 fnd_message.set_token('PROCEDURE', l_proc);
29 fnd_message.set_token('STEP','10');
30 fnd_message.raise_error;
31 ElsIf (p_constraint_name = 'IRC_OFFERS_U1') Then
32 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
33 fnd_message.set_token('PROCEDURE', l_proc);
34 fnd_message.set_token('STEP','15');
35 fnd_message.raise_error;
36 ElsIf (p_constraint_name = 'IRC_OFFERS_PK') Then
37 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
38 fnd_message.set_token('PROCEDURE', l_proc);
39 fnd_message.set_token('STEP','20');
40 fnd_message.raise_error;
41 ElsIf (p_constraint_name = 'SYS_C00196102') Then
42 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
43 fnd_message.set_token('PROCEDURE', l_proc);
44 fnd_message.set_token('STEP','25');
45 fnd_message.raise_error;
46 ElsIf (p_constraint_name = 'SYS_C00196104') Then
47 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
48 fnd_message.set_token('PROCEDURE', l_proc);
49 fnd_message.set_token('STEP','30');
50 fnd_message.raise_error;
51 Else
52 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
53 fnd_message.set_token('PROCEDURE', l_proc);
54 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
55 fnd_message.raise_error;
56 End If;
57 --
58 End constraint_error;
59 --
60 -- ----------------------------------------------------------------------------
61 -- |-----------------------------< api_updating >-----------------------------|
62 -- ----------------------------------------------------------------------------
63 Function api_updating
64 (p_offer_id in number
65 ,p_object_version_number in number
66 )
67 Return Boolean Is
68 --
69 --
70 -- Cursor selects the 'current' row from the HR Schema
71 --
72 Cursor C_Sel1 is
73 select
74 offer_id
75 ,offer_version
76 ,latest_offer
77 ,offer_status
78 ,discretionary_job_title
79 ,offer_extended_method
80 ,respondent_id
81 ,expiry_date
82 ,proposed_start_date
83 ,offer_letter_tracking_code
84 ,offer_postal_service
85 ,offer_shipping_date
86 ,vacancy_id
87 ,applicant_assignment_id
88 ,offer_assignment_id
89 ,address_id
90 ,template_id
91 ,offer_letter_file_type
92 ,offer_letter_file_name
93 ,attribute_category
94 ,attribute1
95 ,attribute2
96 ,attribute3
97 ,attribute4
98 ,attribute5
99 ,attribute6
100 ,attribute7
101 ,attribute8
102 ,attribute9
103 ,attribute10
104 ,attribute11
108 ,attribute15
105 ,attribute12
106 ,attribute13
107 ,attribute14
109 ,attribute16
110 ,attribute17
111 ,attribute18
112 ,attribute19
113 ,attribute20
114 ,attribute21
115 ,attribute22
116 ,attribute23
117 ,attribute24
118 ,attribute25
119 ,attribute26
120 ,attribute27
121 ,attribute28
122 ,attribute29
123 ,attribute30
124 ,object_version_number
125 from irc_offers
126 where offer_id = p_offer_id;
127 --
128 l_fct_ret boolean;
129 --
130 Begin
131 --
132 If (p_offer_id is null and
133 p_object_version_number is null
134 ) Then
135 --
136 -- One of the primary key arguments is null therefore we must
137 -- set the returning function value to false
138 --
139 l_fct_ret := false;
140 Else
141 If (p_offer_id
142 = irc_iof_shd.g_old_rec.offer_id and
143 p_object_version_number
144 = irc_iof_shd.g_old_rec.object_version_number
145 ) Then
146 --
147 -- The g_old_rec is current therefore we must
148 -- set the returning function to true
149 --
150 l_fct_ret := true;
151 Else
152 --
153 -- Select the current row into g_old_rec
154 --
155 Open C_Sel1;
156 Fetch C_Sel1 Into irc_iof_shd.g_old_rec;
157 If C_Sel1%notfound Then
158 Close C_Sel1;
159 --
160 -- The primary key is invalid therefore we must error
161 --
162 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
163 fnd_message.raise_error;
164 End If;
165 Close C_Sel1;
166 If (p_object_version_number
167 <> irc_iof_shd.g_old_rec.object_version_number) Then
168 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
169 fnd_message.raise_error;
170 End If;
171 l_fct_ret := true;
172 End If;
173 End If;
174 Return (l_fct_ret);
175 --
176 End api_updating;
177 --
178 -- ----------------------------------------------------------------------------
179 -- |---------------------------------< lck >----------------------------------|
180 -- ----------------------------------------------------------------------------
181 Procedure lck
182 (p_offer_id in number
183 ,p_object_version_number in number
184 ) is
185 --
186 -- Cursor selects the 'current' row from the HR Schema
187 --
188 Cursor C_Sel1 is
189 select
190 offer_id
191 ,offer_version
192 ,latest_offer
193 ,offer_status
194 ,discretionary_job_title
195 ,offer_extended_method
196 ,respondent_id
197 ,expiry_date
198 ,proposed_start_date
199 ,offer_letter_tracking_code
200 ,offer_postal_service
201 ,offer_shipping_date
202 ,vacancy_id
203 ,applicant_assignment_id
204 ,offer_assignment_id
205 ,address_id
206 ,template_id
207 ,offer_letter_file_type
208 ,offer_letter_file_name
209 ,attribute_category
210 ,attribute1
211 ,attribute2
212 ,attribute3
213 ,attribute4
214 ,attribute5
215 ,attribute6
216 ,attribute7
217 ,attribute8
218 ,attribute9
219 ,attribute10
220 ,attribute11
221 ,attribute12
222 ,attribute13
223 ,attribute14
224 ,attribute15
225 ,attribute16
226 ,attribute17
227 ,attribute18
228 ,attribute19
229 ,attribute20
230 ,attribute21
231 ,attribute22
232 ,attribute23
233 ,attribute24
234 ,attribute25
235 ,attribute26
236 ,attribute27
237 ,attribute28
238 ,attribute29
239 ,attribute30
240 ,object_version_number
241 from irc_offers
242 where offer_id = p_offer_id
243 for update nowait;
244 --
245 l_proc varchar2(72) := g_package||'lck';
246 --
247 Begin
248 hr_utility.set_location('Entering:'||l_proc, 5);
249 --
250 hr_api.mandatory_arg_error
251 (p_api_name => l_proc
252 ,p_argument => 'OFFER_ID'
253 ,p_argument_value => p_offer_id
254 );
255 hr_utility.set_location(l_proc,10);
256 hr_api.mandatory_arg_error
257 (p_api_name => l_proc
258 ,p_argument => 'OBJECT_VERSION_NUMBER'
259 ,p_argument_value => p_object_version_number
260 );
261 --
262 Open C_Sel1;
263 Fetch C_Sel1 Into irc_iof_shd.g_old_rec;
264 If C_Sel1%notfound then
265 Close C_Sel1;
266 --
267 -- The primary key is invalid therefore we must error
268 --
269 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
270 fnd_message.raise_error;
271 End If;
272 Close C_Sel1;
273 If (p_object_version_number
274 <> irc_iof_shd.g_old_rec.object_version_number) Then
275 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
276 fnd_message.raise_error;
277 End If;
278 --
279 hr_utility.set_location(' Leaving:'||l_proc, 20);
280 --
281 -- We need to trap the ORA LOCK exception
282 --
283 Exception
284 When HR_Api.Object_Locked then
285 --
289 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
286 -- The object is locked therefore we need to supply a meaningful
287 -- error message.
288 --
290 fnd_message.set_token('TABLE_NAME', 'irc_offers');
291 fnd_message.raise_error;
292 End lck;
293 --
294 -- ----------------------------------------------------------------------------
295 -- |-----------------------------< convert_args >-----------------------------|
296 -- ----------------------------------------------------------------------------
297 Function convert_args
298 (p_offer_id in number
299 ,p_offer_version in number
300 ,p_latest_offer in varchar2
301 ,p_offer_status in varchar2
302 ,p_discretionary_job_title in varchar2
303 ,p_offer_extended_method in varchar2
304 ,p_respondent_id in number
305 ,p_expiry_date in date
306 ,p_proposed_start_date in date
307 ,p_offer_letter_tracking_code in varchar2
308 ,p_offer_postal_service in varchar2
309 ,p_offer_shipping_date in date
310 ,p_vacancy_id in number
311 ,p_applicant_assignment_id in number
312 ,p_offer_assignment_id in number
313 ,p_address_id in number
314 ,p_template_id in number
315 ,p_offer_letter_file_type in varchar2
316 ,p_offer_letter_file_name in varchar2
317 ,p_attribute_category in varchar2
318 ,p_attribute1 in varchar2
319 ,p_attribute2 in varchar2
320 ,p_attribute3 in varchar2
321 ,p_attribute4 in varchar2
322 ,p_attribute5 in varchar2
323 ,p_attribute6 in varchar2
324 ,p_attribute7 in varchar2
325 ,p_attribute8 in varchar2
326 ,p_attribute9 in varchar2
327 ,p_attribute10 in varchar2
328 ,p_attribute11 in varchar2
329 ,p_attribute12 in varchar2
330 ,p_attribute13 in varchar2
331 ,p_attribute14 in varchar2
332 ,p_attribute15 in varchar2
333 ,p_attribute16 in varchar2
334 ,p_attribute17 in varchar2
335 ,p_attribute18 in varchar2
336 ,p_attribute19 in varchar2
337 ,p_attribute20 in varchar2
338 ,p_attribute21 in varchar2
339 ,p_attribute22 in varchar2
340 ,p_attribute23 in varchar2
341 ,p_attribute24 in varchar2
342 ,p_attribute25 in varchar2
343 ,p_attribute26 in varchar2
344 ,p_attribute27 in varchar2
345 ,p_attribute28 in varchar2
346 ,p_attribute29 in varchar2
347 ,p_attribute30 in varchar2
348 ,p_object_version_number in number
349 )
350 Return g_rec_type is
351 --
352 l_rec g_rec_type;
353 --
354 Begin
355 --
356 -- Convert arguments into local l_rec structure.
357 --
358 l_rec.offer_id := p_offer_id;
359 l_rec.offer_version := p_offer_version;
360 l_rec.latest_offer := p_latest_offer;
361 l_rec.offer_status := p_offer_status;
362 l_rec.discretionary_job_title := p_discretionary_job_title;
363 l_rec.offer_extended_method := p_offer_extended_method;
364 l_rec.respondent_id := p_respondent_id;
365 l_rec.expiry_date := p_expiry_date;
366 l_rec.proposed_start_date := p_proposed_start_date;
367 l_rec.offer_letter_tracking_code := p_offer_letter_tracking_code;
368 l_rec.offer_postal_service := p_offer_postal_service;
369 l_rec.offer_shipping_date := p_offer_shipping_date;
370 l_rec.vacancy_id := p_vacancy_id;
371 l_rec.applicant_assignment_id := p_applicant_assignment_id;
372 l_rec.offer_assignment_id := p_offer_assignment_id;
373 l_rec.address_id := p_address_id;
374 l_rec.template_id := p_template_id;
375 l_rec.offer_letter_file_type := p_offer_letter_file_type;
376 l_rec.offer_letter_file_name := p_offer_letter_file_name;
377 l_rec.attribute_category := p_attribute_category;
378 l_rec.attribute1 := p_attribute1;
379 l_rec.attribute2 := p_attribute2;
380 l_rec.attribute3 := p_attribute3;
381 l_rec.attribute4 := p_attribute4;
382 l_rec.attribute5 := p_attribute5;
383 l_rec.attribute6 := p_attribute6;
384 l_rec.attribute7 := p_attribute7;
385 l_rec.attribute8 := p_attribute8;
386 l_rec.attribute9 := p_attribute9;
387 l_rec.attribute10 := p_attribute10;
388 l_rec.attribute11 := p_attribute11;
389 l_rec.attribute12 := p_attribute12;
390 l_rec.attribute13 := p_attribute13;
391 l_rec.attribute14 := p_attribute14;
392 l_rec.attribute15 := p_attribute15;
393 l_rec.attribute16 := p_attribute16;
394 l_rec.attribute17 := p_attribute17;
395 l_rec.attribute18 := p_attribute18;
396 l_rec.attribute19 := p_attribute19;
397 l_rec.attribute20 := p_attribute20;
398 l_rec.attribute21 := p_attribute21;
399 l_rec.attribute22 := p_attribute22;
400 l_rec.attribute23 := p_attribute23;
401 l_rec.attribute24 := p_attribute24;
402 l_rec.attribute25 := p_attribute25;
403 l_rec.attribute26 := p_attribute26;
404 l_rec.attribute27 := p_attribute27;
405 l_rec.attribute28 := p_attribute28;
406 l_rec.attribute29 := p_attribute29;
407 l_rec.attribute30 := p_attribute30;
408 l_rec.object_version_number := p_object_version_number;
409 --
410 -- Return the plsql record structure.
411 --
412 Return(l_rec);
413 --
414 End convert_args;
415 --
416 -- ----------------------------------------------------------------------------
417 -- |------------------------------< blob_dml >--------------------------------|
418 -- ----------------------------------------------------------------------------
419 --
420 Procedure blob_dml
421 (p_offer_letter in irc_offers.offer_letter%TYPE
422 ,p_offer_id in irc_offers.offer_id%TYPE
423 ,p_object_version_number in irc_offers.object_version_number%TYPE
424 ) is
425 --
426 l_proc varchar2(72) := g_package||'blob_dml';
427 --
428 Begin
429 --
430 hr_utility.set_location(' Entering:'||l_proc, 10);
431 --
432 -- We must lock the row which we need to update.
433 --
434 irc_iof_shd.lck
435 (p_offer_id
436 ,p_object_version_number
437 );
438 --
439 -- Upload the bolb to the table.
440 --
441 update irc_offers
442 set offer_letter = p_offer_letter
443 where offer_id = p_offer_id;
444 --
445 hr_utility.set_location(' Leaving:'||l_proc, 20);
446 --
447 End blob_dml;
448 --
449 end irc_iof_shd;