[Home] [Help]
PACKAGE BODY: APPS.OTA_TSR_SHD
Source
1 Package Body ota_tsr_shd as
2 /* $Header: ottsr01t.pkb 120.2 2005/08/08 23:27:40 ssur noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tsr_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 varchar2) 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 = 'OTA_SUPPLIABLE_RESOURCES_FK1') 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 ElsIf (p_constraint_name = 'OTA_SUPPLIABLE_RESOURCES_FK2') Then
42 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
43 hr_utility.set_message_token('PROCEDURE', l_proc);
44 hr_utility.set_message_token('STEP','10');
45 hr_utility.raise_error;
46 ElsIf (p_constraint_name = 'OTA_SUPPLIABLE_RESOURCES_PK') Then
47 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
48 hr_utility.set_message_token('PROCEDURE', l_proc);
49 hr_utility.set_message_token('STEP','15');
50 hr_utility.raise_error;
51 -- ElsIf (p_constraint_name = 'OTA_TSR_ADDRESS_LINE_NULL') Then
52 --hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
53 --hr_utility.set_message_token('PROCEDURE', l_proc);
54 --hr_utility.set_message_token('STEP','20');
55 --hr_utility.raise_error;
56 ElsIf (p_constraint_name = 'OTA_TSR_CHECK_DATES_ORDER') Then
57 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
58 hr_utility.set_message_token('PROCEDURE', l_proc);
59 hr_utility.set_message_token('STEP','25');
60 hr_utility.raise_error;
61 ElsIf (p_constraint_name = 'OTA_TSR_CONSUMABLE_FLAG_CHK') Then
62 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
63 hr_utility.set_message_token('PROCEDURE', l_proc);
64 hr_utility.set_message_token('STEP','30');
65 hr_utility.raise_error;
66 ElsIf (p_constraint_name = 'OTA_TSR_CURRENCY_NOTNULL') Then
67 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
68 hr_utility.set_message_token('PROCEDURE', l_proc);
69 hr_utility.set_message_token('STEP','35');
70 hr_utility.raise_error;
71 ElsIf (p_constraint_name = 'OTA_TSR_CURRENCY_NULL') Then
72 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
73 hr_utility.set_message_token('PROCEDURE', l_proc);
74 hr_utility.set_message_token('STEP','40');
75 hr_utility.raise_error;
76 --ElsIf (p_constraint_name = 'OTA_TSR_DELEGATES_NULL') Then
77 --hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
78 --hr_utility.set_message_token('PROCEDURE', l_proc);
79 --hr_utility.set_message_token('STEP','45');
80 --hr_utility.raise_error;
81 ElsIf (p_constraint_name = 'OTA_TSR_INTERNAL_FLAG_CHK') Then
82 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
83 hr_utility.set_message_token('PROCEDURE', l_proc);
84 hr_utility.set_message_token('STEP','50');
85 hr_utility.raise_error;
86 --ElsIf (p_constraint_name = 'OTA_TSR_LOCATION_NULL') Then
87 --hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
88 --hr_utility.set_message_token('PROCEDURE', l_proc);
89 --hr_utility.set_message_token('STEP','55');
90 --hr_utility.raise_error;
91 ElsIf (p_constraint_name = 'OTA_TSR_NAME_PERSON_NOTNULL') Then
92 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
93 hr_utility.set_message_token('PROCEDURE', l_proc);
94 hr_utility.set_message_token('STEP','60');
95 hr_utility.raise_error;
96 --ElsIf (p_constraint_name = 'OTA_TSR_NON_CONSUMABLE') Then
97 --hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
98 --hr_utility.set_message_token('PROCEDURE', l_proc);
99 --hr_utility.set_message_token('STEP','65');
100 --hr_utility.raise_error;
101 --ElsIf (p_constraint_name = 'OTA_TSR_PERSON_NULL') Then
102 --hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
103 --hr_utility.set_message_token('PROCEDURE', l_proc);
104 --hr_utility.set_message_token('STEP','70');
105 --hr_utility.raise_error;
106 --ElsIf (p_constraint_name = 'OTA_TSR_STOCKDATE_NOTNULL') Then
107 --hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
108 --hr_utility.set_message_token('PROCEDURE', l_proc);
109 --hr_utility.set_message_token('STEP','75');
110 --hr_utility.raise_error;
111 --ElsIf (p_constraint_name = 'OTA_TSR_STOCKDATE_NULL1') Then
112 --hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
113 --hr_utility.set_message_token('PROCEDURE', l_proc);
114 --hr_utility.set_message_token('STEP','80');
115 --hr_utility.raise_error;
116 --ElsIf (p_constraint_name = 'OTA_TSR_STOCKDATE_NULL2') Then
117 --hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
118 --hr_utility.set_message_token('PROCEDURE', l_proc);
119 --hr_utility.set_message_token('STEP','85');
120 --hr_utility.raise_error;
121 --ElsIf (p_constraint_name = 'OTA_TSR_STOCKDATE_NULL3') Then
122 --hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
123 --hr_utility.set_message_token('PROCEDURE', l_proc);
124 --hr_utility.set_message_token('STEP','90');
125 --hr_utility.raise_error;
126 --ElsIf (p_constraint_name = 'OTA_TSR_STOCK_NULL1') Then
127 --hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
128 --hr_utility.set_message_token('PROCEDURE', l_proc);
129 --hr_utility.set_message_token('STEP','95');
130 --hr_utility.raise_error;
131 --ElsIf (p_constraint_name = 'OTA_TSR_STOCK_NULL2') Then
132 --hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
133 --hr_utility.set_message_token('PROCEDURE', l_proc);
134 --hr_utility.set_message_token('STEP','96');
135 --hr_utility.raise_error;
136 Else
137 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
138 hr_utility.set_message_token('PROCEDURE', l_proc);
139 hr_utility.set_message_token('STEP','105');
140 hr_utility.raise_error;
141 End If;
142 --
143 hr_utility.set_location(' Leaving:'||l_proc, 10);
144 End constraint_error;
145 --
146 -- ----------------------------------------------------------------------------
147 -- |-----------------------------< api_updating >-----------------------------|
148 -- ----------------------------------------------------------------------------
149 Function api_updating
150 (
151 p_supplied_resource_id in number,
152 p_object_version_number in number
153 ) Return Boolean Is
154 --
155 --
156 -- Cursor selects the 'current' row from the HR Schema
157 --
158 Cursor C_Sel1 is
159 select
160 supplied_resource_id,
161 vendor_id,
162 business_group_id,
163 resource_definition_id,
164 consumable_flag,
165 object_version_number,
166 resource_type,
167 start_date,
168 comments,
169 cost,
170 cost_unit,
171 currency_code,
172 end_date,
173 internal_address_line,
174 lead_time,
175 name,
176 supplier_reference,
177 tsr_information_category,
178 tsr_information1,
179 tsr_information2,
180 tsr_information3,
181 tsr_information4,
182 tsr_information5,
183 tsr_information6,
184 tsr_information7,
185 tsr_information8,
186 tsr_information9,
187 tsr_information10,
188 tsr_information11,
189 tsr_information12,
190 tsr_information13,
191 tsr_information14,
192 tsr_information15,
193 tsr_information16,
194 tsr_information17,
195 tsr_information18,
196 tsr_information19,
197 tsr_information20,
198 training_center_id ,
199 location_id ,
200 trainer_id ,
201 special_instruction
202 from ota_suppliable_resources
203 where supplied_resource_id = p_supplied_resource_id;
204 --
205 l_proc varchar2(72) := g_package||'api_updating';
206 l_fct_ret boolean;
207 --
208 Begin
209 hr_utility.set_location('Entering:'||l_proc, 5);
210 --
211 If (
212 p_supplied_resource_id is null and
213 p_object_version_number is null
214 ) Then
215 --
216 -- One of the primary key arguments is null therefore we must
217 -- set the returning function value to false
218 --
219 l_fct_ret := false;
220 Else
221 If (
222 p_supplied_resource_id = g_old_rec.supplied_resource_id and
223 p_object_version_number = g_old_rec.object_version_number
224 ) Then
225 hr_utility.set_location(l_proc, 10);
226 --
227 -- The g_old_rec is current therefore we must
228 -- set the returning function to true
229 --
230 l_fct_ret := true;
231 Else
232 --
233 -- Select the current row into g_old_rec
234 --
235 Open C_Sel1;
236 Fetch C_Sel1 Into g_old_rec;
237 If C_Sel1%notfound Then
238 Close C_Sel1;
239 --
240 -- The primary key is invalid therefore we must error
241 --
242 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
243 hr_utility.raise_error;
244 End If;
245 Close C_Sel1;
246 If (p_object_version_number <> g_old_rec.object_version_number) Then
247 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
248 hr_utility.raise_error;
249 End If;
250 hr_utility.set_location(l_proc, 15);
251 l_fct_ret := true;
252 End If;
253 End If;
254 hr_utility.set_location(' Leaving:'||l_proc, 20);
255 Return (l_fct_ret);
256 --
257 End api_updating;
258 --
259 -- ----------------------------------------------------------------------------
260 -- |---------------------------------< lck >----------------------------------|
261 -- ----------------------------------------------------------------------------
262 Procedure lck
263 (
264 p_supplied_resource_id in number,
265 p_object_version_number in number
266 ) is
267 --
268 -- Cursor selects the 'current' row from the HR Schema
269 --
270 Cursor C_Sel1 is
271 select supplied_resource_id,
272 vendor_id,
273 business_group_id,
274 resource_definition_id,
275 consumable_flag,
276 object_version_number,
277 resource_type,
278 start_date,
279 comments,
280 cost,
281 cost_unit,
282 currency_code,
283 end_date,
284 internal_address_line,
285 lead_time,
286 name,
287 supplier_reference,
288 tsr_information_category,
289 tsr_information1,
290 tsr_information2,
291 tsr_information3,
292 tsr_information4,
293 tsr_information5,
294 tsr_information6,
295 tsr_information7,
296 tsr_information8,
297 tsr_information9,
298 tsr_information10,
299 tsr_information11,
300 tsr_information12,
301 tsr_information13,
302 tsr_information14,
303 tsr_information15,
304 tsr_information16,
305 tsr_information17,
306 tsr_information18,
307 tsr_information19,
308 tsr_information20,
309 training_center_id,
310 location_id,
311 trainer_id,
312 special_instruction
313 from ota_suppliable_resources
314 where supplied_resource_id = p_supplied_resource_id
315 for update nowait;
316 --
317 l_proc varchar2(72) := g_package||'lck';
318 --
319 Begin
320 hr_utility.set_location('Entering:'||l_proc, 5);
321 --
322 -- Add any mandatory argument checking here:
323 -- Example:
324 -- hr_api.mandatory_arg_error
325 -- (p_api_name => l_proc,
326 -- p_argument => 'object_version_number',
327 -- p_argument_value => p_object_version_number);
328 --
329 Open C_Sel1;
330 Fetch C_Sel1 Into g_old_rec;
331 If C_Sel1%notfound then
332 Close C_Sel1;
333 --
334 -- The primary key is invalid therefore we must error
335 --
336 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
337 hr_utility.raise_error;
338 End If;
339 Close C_Sel1;
340 If (p_object_version_number <> g_old_rec.object_version_number) Then
341 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
342 hr_utility.raise_error;
343 End If;
344 --
345 hr_utility.set_location(' Leaving:'||l_proc, 10);
346 --
347 -- We need to trap the ORA LOCK exception
348 --
349 Exception
350 When HR_Api.Object_Locked then
351 --
352 -- The object is locked therefore we need to supply a meaningful
353 -- error message.
354 --
355 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
356 hr_utility.set_message_token('TABLE_NAME', 'ota_suppliable_resources');
357 hr_utility.raise_error;
358 End lck;
359 --
360 -- ----------------------------------------------------------------------------
361 -- |-----------------------------< convert_args >-----------------------------|
362 -- ----------------------------------------------------------------------------
363 Function convert_args
364 (
365 p_supplied_resource_id in number,
366 p_vendor_id in number,
367 p_business_group_id in number,
368 p_resource_definition_id in number,
369 p_consumable_flag in varchar2,
370 p_object_version_number in number,
371 p_resource_type in varchar2,
372 p_start_date in date,
373 p_comments in varchar2,
374 p_cost in number,
375 p_cost_unit in varchar2,
376 p_currency_code in varchar2,
377 p_end_date in date,
378 p_internal_address_line in varchar2,
379 p_lead_time in number,
380 p_name in varchar2,
381 p_supplier_reference in varchar2,
382 p_tsr_information_category in varchar2,
383 p_tsr_information1 in varchar2,
384 p_tsr_information2 in varchar2,
385 p_tsr_information3 in varchar2,
386 p_tsr_information4 in varchar2,
387 p_tsr_information5 in varchar2,
388 p_tsr_information6 in varchar2,
389 p_tsr_information7 in varchar2,
390 p_tsr_information8 in varchar2,
391 p_tsr_information9 in varchar2,
392 p_tsr_information10 in varchar2,
393 p_tsr_information11 in varchar2,
394 p_tsr_information12 in varchar2,
395 p_tsr_information13 in varchar2,
396 p_tsr_information14 in varchar2,
397 p_tsr_information15 in varchar2,
398 p_tsr_information16 in varchar2,
399 p_tsr_information17 in varchar2,
400 p_tsr_information18 in varchar2,
401 p_tsr_information19 in varchar2,
405 p_trainer_id in number,
402 p_tsr_information20 in varchar2,
403 p_training_center_id in number,
404 p_location_id in number,
406 p_special_instruction in varchar2
407 )
408 Return g_rec_type is
409 --
410 l_rec g_rec_type;
411 l_proc varchar2(72) := g_package||'convert_args';
412 --
413 Begin
414 --
415 hr_utility.set_location('Entering:'||l_proc, 5);
416 --
417 -- Convert arguments into local l_rec structure.
418 --
419 l_rec.supplied_resource_id := p_supplied_resource_id;
420 l_rec.vendor_id := p_vendor_id;
421 l_rec.business_group_id := p_business_group_id;
422 l_rec.resource_definition_id := p_resource_definition_id;
423 l_rec.consumable_flag := p_consumable_flag;
424 l_rec.object_version_number := p_object_version_number;
425 l_rec.resource_type := p_resource_type;
426 l_rec.start_date := p_start_date;
427 l_rec.comments := p_comments;
428 l_rec.cost := p_cost;
429 l_rec.cost_unit := p_cost_unit;
430 l_rec.currency_code := p_currency_code;
431 l_rec.end_date := p_end_date;
432 l_rec.internal_address_line := p_internal_address_line;
433 l_rec.lead_time := p_lead_time;
434 l_rec.name := p_name;
435 l_rec.supplier_reference := p_supplier_reference;
436 l_rec.tsr_information_category := p_tsr_information_category;
437 l_rec.tsr_information1 := p_tsr_information1;
438 l_rec.tsr_information2 := p_tsr_information2;
439 l_rec.tsr_information3 := p_tsr_information3;
440 l_rec.tsr_information4 := p_tsr_information4;
441 l_rec.tsr_information5 := p_tsr_information5;
442 l_rec.tsr_information6 := p_tsr_information6;
443 l_rec.tsr_information7 := p_tsr_information7;
444 l_rec.tsr_information8 := p_tsr_information8;
445 l_rec.tsr_information9 := p_tsr_information9;
446 l_rec.tsr_information10 := p_tsr_information10;
447 l_rec.tsr_information11 := p_tsr_information11;
448 l_rec.tsr_information12 := p_tsr_information12;
449 l_rec.tsr_information13 := p_tsr_information13;
450 l_rec.tsr_information14 := p_tsr_information14;
451 l_rec.tsr_information15 := p_tsr_information15;
452 l_rec.tsr_information16 := p_tsr_information16;
453 l_rec.tsr_information17 := p_tsr_information17;
454 l_rec.tsr_information18 := p_tsr_information18;
455 l_rec.tsr_information19 := p_tsr_information19;
456 l_rec.tsr_information20 := p_tsr_information20;
457 l_rec.training_center_id := p_training_center_id;
458 l_rec.location_id := p_location_id;
459 l_rec.trainer_id := p_trainer_id;
460 l_rec.special_instruction := p_special_instruction;
461 --
462 -- Return the plsql record structure.
463 --
464 hr_utility.set_location(' Leaving:'||l_proc, 10);
465 Return(l_rec);
466 --
467 End convert_args;
468 --
469 end ota_tsr_shd;