[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_RT_OPER_RESOURCE_PVT
Source
1 PACKAGE BODY AHL_RM_RT_OPER_RESOURCE_PVT AS
2 /* $Header: AHLVRORB.pls 120.2.12010000.3 2008/12/29 14:21:25 bachandr ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_RM_RT_OPER_RESOURCE_PVT';
5 G_API_NAME1 VARCHAR2(30) := 'PROCESS_RT_OPER_RESOURCE';
6 G_API_NAME2 VARCHAR2(30) := 'DEFINE_COST_PARAMETER';
7 G_API_NAME3 VARCHAR2(30) := 'PROCESS_ALTERNATE_RESOURCE';
8 G_DEBUG VARCHAR2(1) := NVL(AHL_DEBUG_PUB.is_log_enabled,'N');
9
10 -- constants for WHO Columns
11 -- Added by balaji as a part of Public API cleanup
12 G_LAST_UPDATE_DATE DATE := SYSDATE;
13 G_LAST_UPDATED_BY NUMBER(15) := FND_GLOBAL.user_id;
14 G_LAST_UPDATE_LOGIN NUMBER(15) := FND_GLOBAL.login_id;
15 G_CREATION_DATE DATE := SYSDATE;
16 G_CREATED_BY NUMBER(15) := FND_GLOBAL.user_id;
17
18 -- Function to get the Record Identifier for Error Messages
19 FUNCTION get_record_identifier
20 (
21 p_rt_oper_resource_rec IN rt_oper_resource_rec_type
22 ) RETURN VARCHAR2
23 IS
24
25 l_record_identifier VARCHAR2(2000) := '';
26
27 BEGIN
28
29 IF ( p_rt_oper_resource_rec.resource_type IS NOT NULL AND
30 p_rt_oper_resource_rec.resource_type <> FND_API.G_MISS_CHAR ) THEN
31 l_record_identifier := l_record_identifier || p_rt_oper_resource_rec.resource_type;
32 END IF;
33
34 l_record_identifier := l_record_identifier || ' - ';
35
36 IF ( p_rt_oper_resource_rec.aso_resource_name IS NOT NULL AND
37 p_rt_oper_resource_rec.asO_resource_name <> FND_API.G_MISS_CHAR ) THEN
38 l_record_identifier := l_record_identifier || p_rt_oper_resource_rec.aso_resource_name;
39 END IF;
40
41 RETURN l_record_identifier;
42
43 END get_record_identifier;
44
45 -- Procedure to validate the all the inputs except the table structure of the API
46 PROCEDURE validate_api_inputs
47 (
48 p_rt_oper_resource_tbl IN rt_oper_resource_tbl_type,
49 p_association_type_code IN VARCHAR2,
50 p_object_id IN NUMBER,
51 x_return_status OUT NOCOPY VARCHAR2
52 )
53 IS
54
55 l_return_status VARCHAR2(1);
56 l_msg_data VARCHAR2(2000);
57
58 BEGIN
59 x_return_status := FND_API.G_RET_STS_SUCCESS;
60
61 -- Check if a valid value is passed in p_association_type_code
62 IF ( p_association_type_code = FND_API.G_MISS_CHAR OR
63 p_association_type_code IS NULL ) THEN
64 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ASSOC_TYPE_NULL' );
65 FND_MSG_PUB.add;
66 x_return_status := FND_API.G_RET_STS_ERROR;
67 RETURN;
68 ELSIF ( p_association_type_code <> 'OPERATION' AND
69 p_association_type_code <> 'ROUTE' ) THEN
70 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ASSOC_TYPE_INVALID' );
71 FND_MESSAGE.set_token( 'FIELD', p_association_type_code );
72 FND_MSG_PUB.add;
73 x_return_status := FND_API.G_RET_STS_ERROR;
74 RETURN;
75 END IF;
76
77 -- Check if a valid value is passed in p_object_id
78 IF (p_association_type_code = 'OPERATION') THEN
79 AHL_RM_ROUTE_UTIL.validate_operation_status
80 (
81 p_operation_id => p_object_id,
82 x_return_status => l_return_status,
83 x_msg_data => l_msg_data
84 );
85 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
86 FND_MESSAGE.set_name( 'AHL', l_msg_data );
87 FND_MSG_PUB.add;
88 x_return_status := FND_API.G_RET_STS_ERROR;
89 RETURN;
90 END IF;
91 ELSIF (p_association_type_code = 'ROUTE') THEN
92 AHL_RM_ROUTE_UTIL.validate_route_status
93 (
94 p_route_id => p_object_id,
95 x_return_status => l_return_status,
96 x_msg_data => l_msg_data
97 );
98 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
99 FND_MESSAGE.set_name( 'AHL', l_msg_data );
100 FND_MSG_PUB.add;
101 x_return_status := FND_API.G_RET_STS_ERROR;
102 RETURN;
103 END IF;
104 END IF;
105
106 -- Check if at least one record is passed in p_rt_oper_resource_tbl
107 IF ( p_rt_oper_resource_tbl.count < 1 ) THEN
108 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
109 FND_MESSAGE.set_token( 'PROCEDURE', G_PKG_NAME || '.' || G_API_NAME1 );
110 FND_MSG_PUB.add;
111 x_return_status := FND_API.G_RET_STS_ERROR;
112 RETURN;
113 END IF;
114
115 -- Validate DML Operation
116 FOR i IN 1..p_rt_oper_resource_tbl.count LOOP
117 IF ( p_rt_oper_resource_tbl(i).dml_operation IS NULL OR
118 (
119 p_rt_oper_resource_tbl(i).dml_operation <> 'C' AND
120 p_rt_oper_resource_tbl(i).dml_operation <> 'U' AND
121 p_rt_oper_resource_tbl(i).dml_operation <> 'D'
122 )
123 )
124 THEN
125 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML' );
126 FND_MESSAGE.set_token( 'FIELD', p_rt_oper_resource_tbl(i).dml_operation );
127 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_tbl(i) ) );
128 FND_MSG_PUB.add;
129 x_return_status := FND_API.G_RET_STS_ERROR;
130 RETURN;
131 END IF;
132 END LOOP;
133
134 END validate_api_inputs;
135
136 -- Procedure to Default NULL / G_MISS Values for LOV attributes
137 PROCEDURE clear_lov_attribute_ids
138 (
139 p_x_rt_oper_resource_rec IN OUT NOCOPY rt_oper_resource_rec_type
140 )
141 IS
142
143 BEGIN
144 IF ( p_x_rt_oper_resource_rec.resource_type IS NULL ) THEN
145 p_x_rt_oper_resource_rec.resource_type_id := NULL;
146 ELSIF ( p_x_rt_oper_resource_rec.resource_type = FND_API.G_MISS_CHAR ) THEN
147 p_x_rt_oper_resource_rec.resource_type_id := FND_API.G_MISS_NUM;
148 END IF;
149
150 IF ( p_x_rt_oper_resource_rec.aso_resource_name IS NULL ) THEN
151 p_x_rt_oper_resource_rec.aso_resource_id := NULL;
152 ELSIF ( p_x_rt_oper_resource_rec.aso_resource_name = FND_API.G_MISS_CHAR ) THEN
153 p_x_rt_oper_resource_rec.aso_resource_id := FND_API.G_MISS_NUM;
154 END IF;
155
156 END clear_lov_attribute_ids;
157
158 -- Procedure to perform Value to ID conversion and validation for LOV attributes
159 PROCEDURE convert_values_to_ids
160 (
161 p_x_rt_oper_resource_rec IN OUT NOCOPY rt_oper_resource_rec_type,
162 x_return_status OUT NOCOPY VARCHAR2
163 )
164 IS
165
166 l_return_status VARCHAR2(1);
167 l_msg_data VARCHAR2(2000);
168
169 BEGIN
170 x_return_status := FND_API.G_RET_STS_SUCCESS;
171
172 -- Convert / Validate resource_type_id
173 IF ( ( p_x_rt_oper_resource_rec.resource_type_id IS NOT NULL AND
174 p_x_rt_oper_resource_rec.resource_type_id <> FND_API.G_MISS_NUM ) OR
175 ( p_x_rt_oper_resource_rec.resource_type IS NOT NULL AND
176 p_x_rt_oper_resource_rec.resource_type <> FND_API.G_MISS_CHAR ) ) THEN
177
178 AHL_RM_ROUTE_UTIL.validate_mfg_lookup
179 (
180 x_return_status => l_return_status,
181 x_msg_data => l_msg_data,
182 p_lookup_type => 'BOM_RESOURCE_TYPE',
183 p_lookup_meaning => p_x_rt_oper_resource_rec.resource_type,
184 p_x_lookup_code => p_x_rt_oper_resource_rec.resource_type_id
185 );
186
187 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
188 IF ( l_msg_data = 'AHL_COM_INVALID_MFG_LOOKUP' ) THEN
189 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_RESOURCE_TYPE' );
190 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_MFG_LOOKUPS' ) THEN
191 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_RESOURCE_TYPES' );
192 ELSE
193 FND_MESSAGE.set_name( 'AHL', l_msg_data );
194 END IF;
195
196 IF ( p_x_rt_oper_resource_rec.resource_type IS NULL OR
197 p_x_rt_oper_resource_rec.resource_type = FND_API.G_MISS_CHAR ) THEN
198 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_resource_rec.resource_type_id ) );
199 ELSE
200 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_resource_rec.resource_type );
201 END IF;
202
203 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_rt_oper_resource_rec ) );
204 FND_MSG_PUB.add;
205 END IF;
206
207 END IF;
208
209 -- Convert / Validate ASO_resource_id
210 IF ( ( p_x_rt_oper_resource_rec.aso_resource_id IS NOT NULL AND
211 p_x_rt_oper_resource_rec.aso_resource_id <> FND_API.G_MISS_NUM ) OR
212 ( p_x_rt_oper_resource_rec.aso_resource_name IS NOT NULL AND
213 p_x_rt_oper_resource_rec.aso_resource_name <> FND_API.G_MISS_CHAR ) )
214 THEN
215
216 AHL_RM_ROUTE_UTIL.validate_aso_resource
217 (
218 x_return_status => l_return_status,
219 x_msg_data => l_msg_data,
220 p_aso_resource_name => p_x_rt_oper_resource_rec.aso_resource_name,
221 p_x_aso_resource_id => p_x_rt_oper_resource_rec.aso_resource_id
222 );
223
224 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
225 FND_MESSAGE.set_name( 'AHL', l_msg_data );
226
227 IF ( p_x_rt_oper_resource_rec.aso_resource_name IS NULL OR
228 p_x_rt_oper_resource_rec.aso_resource_name = FND_API.G_MISS_CHAR ) THEN
229 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_resource_rec.aso_resource_id ) );
230 ELSE
231 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_resource_rec.aso_resource_name );
232 END IF;
233
234 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_rt_oper_resource_rec ) );
235 FND_MSG_PUB.add;
236 END IF;
237
238 END IF;
239
240 -- Added for bug fix 6512803. Honor input scheduled_type_id if passed.
241 -- Convert / Validate scheduled_type_id
242 IF ( ( p_x_rt_oper_resource_rec.scheduled_type_id IS NOT NULL AND
243 p_x_rt_oper_resource_rec.scheduled_type_id <> FND_API.G_MISS_NUM ) OR
244 ( p_x_rt_oper_resource_rec.scheduled_type IS NOT NULL AND
245 p_x_rt_oper_resource_rec.scheduled_type <> FND_API.G_MISS_CHAR ) )
246 THEN
247
248 AHL_RM_ROUTE_UTIL.validate_mfg_lookup
249 (
250 x_return_status => l_return_status,
251 x_msg_data => l_msg_data,
252 p_lookup_type => 'BOM_RESOURCE_SCHEDULE_TYPE',
253 p_lookup_meaning => p_x_rt_oper_resource_rec.scheduled_type,
254 p_x_lookup_code => p_x_rt_oper_resource_rec.scheduled_type_id
255 );
256
257 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
258 IF ( l_msg_data = 'AHL_RM_INVALID_MFG_LOOKUP' ) THEN
259 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_SCHEDULE_TYPE' );
260 ELSIF ( l_msg_data = 'AHL_RM_TOO_MANY_MFG_LOOKUPS' ) THEN
261 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_SCHEDULE_TYPES' );
262 ELSE
263 FND_MESSAGE.set_name( 'AHL', l_msg_data );
264 END IF;
265
266 IF ( p_x_rt_oper_resource_rec.scheduled_type IS NULL OR
267 p_x_rt_oper_resource_rec.scheduled_type = FND_API.G_MISS_CHAR ) THEN
268 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_resource_rec.scheduled_type_id ) );
269 ELSE
270 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_resource_rec.scheduled_type);
271 END IF;
272
273 FND_MSG_PUB.add;
274 END IF;
275
276 END IF;
277
278 END convert_values_to_ids;
279 /* Removed as a part of public API cleanup in 11510+.
280 -- Procedure to add Default values for rt_oper_resource attributes
281 PROCEDURE default_attributes
282 (
283 p_x_rt_oper_resource_rec IN OUT NOCOPY rt_oper_resource_rec_type
284 )
285 IS
286
287 BEGIN
288
289 p_x_rt_oper_resource_rec.last_update_date := SYSDATE;
290 p_x_rt_oper_resource_rec.last_updated_by := FND_GLOBAL.user_id;
291 p_x_rt_oper_resource_rec.last_update_login := FND_GLOBAL.login_id;
292
293 IF ( p_x_rt_oper_resource_rec.dml_operation = 'C' ) THEN
294 p_x_rt_oper_resource_rec.object_version_number := 1;
295 p_x_rt_oper_resource_rec.creation_date := SYSDATE;
296 p_x_rt_oper_resource_rec.created_by := FND_GLOBAL.user_id;
297 END IF;
298
299 END default_attributes;
300 */
301 -- Procedure to add Default values for missing attributes (CREATE)
302 PROCEDURE default_missing_attributes
303 (
304 p_x_rt_oper_resource_rec IN OUT NOCOPY rt_oper_resource_rec_type
305 )
306 IS
307
308 BEGIN
309
310 -- Convert G_MISS values to NULL
311 IF ( p_x_rt_oper_resource_rec.resource_type_id = FND_API.G_MISS_NUM ) THEN
312 p_x_rt_oper_resource_rec.resource_type_id := null;
313 END IF;
314
315 IF ( p_x_rt_oper_resource_rec.resource_type = FND_API.G_MISS_CHAR ) THEN
316 p_x_rt_oper_resource_rec.resource_type := null;
317 END IF;
318
319 IF ( p_x_rt_oper_resource_rec.aso_resource_id = FND_API.G_MISS_NUM ) THEN
320 p_x_rt_oper_resource_rec.aso_resource_id := null;
321 END IF;
322
323 IF ( p_x_rt_oper_resource_rec.aso_resource_name = FND_API.G_MISS_CHAR ) THEN
324 p_x_rt_oper_resource_rec.aso_resource_name := null;
325 END IF;
326
327 IF ( p_x_rt_oper_resource_rec.quantity = FND_API.G_MISS_NUM ) THEN
328 p_x_rt_oper_resource_rec.quantity := null;
329 END IF;
330
331 IF ( p_x_rt_oper_resource_rec.duration = FND_API.G_MISS_NUM ) THEN
332 p_x_rt_oper_resource_rec.duration := null;
333 END IF;
334
335 IF ( p_x_rt_oper_resource_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
336 p_x_rt_oper_resource_rec.attribute_category := null;
337 END IF;
338
339 IF ( p_x_rt_oper_resource_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
340 p_x_rt_oper_resource_rec.attribute1 := null;
341 END IF;
342
343 IF ( p_x_rt_oper_resource_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
344 p_x_rt_oper_resource_rec.attribute2 := null;
345 END IF;
346
347 IF ( p_x_rt_oper_resource_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
348 p_x_rt_oper_resource_rec.attribute3 := null;
349 END IF;
350
351 IF ( p_x_rt_oper_resource_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
352 p_x_rt_oper_resource_rec.attribute4 := null;
353 END IF;
354
355 IF ( p_x_rt_oper_resource_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
356 p_x_rt_oper_resource_rec.attribute5 := null;
357 END IF;
358
359 IF ( p_x_rt_oper_resource_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
360 p_x_rt_oper_resource_rec.attribute6 := null;
361 END IF;
362
363 IF ( p_x_rt_oper_resource_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
364 p_x_rt_oper_resource_rec.attribute7 := null;
365 END IF;
366
367 IF ( p_x_rt_oper_resource_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
368 p_x_rt_oper_resource_rec.attribute8 := null;
369 END IF;
370
371 IF ( p_x_rt_oper_resource_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
372 p_x_rt_oper_resource_rec.attribute9 := null;
373 END IF;
374
375 IF ( p_x_rt_oper_resource_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
376 p_x_rt_oper_resource_rec.attribute10 := null;
377 END IF;
378
379 IF ( p_x_rt_oper_resource_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
380 p_x_rt_oper_resource_rec.attribute11 := null;
381 END IF;
382
383 IF ( p_x_rt_oper_resource_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
384 p_x_rt_oper_resource_rec.attribute12 := null;
385 END IF;
386
387 IF ( p_x_rt_oper_resource_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
388 p_x_rt_oper_resource_rec.attribute13 := null;
389 END IF;
390
391 IF ( p_x_rt_oper_resource_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
392 p_x_rt_oper_resource_rec.attribute14 := null;
393 END IF;
394
395 IF ( p_x_rt_oper_resource_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
396 p_x_rt_oper_resource_rec.attribute15 := null;
397 END IF;
398
399 -- Added for bug fix# 6512803.
400 -- honor the input value if passed (from public api).
401 IF (p_x_rt_oper_resource_rec.scheduled_type_id IS NULL OR
402 p_x_rt_oper_resource_rec.scheduled_type_id = FND_API.G_MISS_NUM) THEN
403 IF (p_x_rt_oper_resource_rec.resource_type_id IN (1,2)) THEN
404 p_x_rt_oper_resource_rec.scheduled_type_id := 1;
405 ELSE
406 p_x_rt_oper_resource_rec.scheduled_type_id := 2;
407 END IF;
408 END IF;
409
410 -- Bug # 7644260 (FP for ER # 6998882) -- start
411 IF ( p_x_rt_oper_resource_rec.schedule_seq = FND_API.G_MISS_NUM ) THEN
412 p_x_rt_oper_resource_rec.schedule_seq := null;
413 END IF;
414 -- Bug # 7644260 (FP for ER # 6998882) -- end
415
416 END default_missing_attributes;
417
418 -- Procedure to add Default values for unchanged attributes (UPDATE)
419 PROCEDURE default_unchanged_attributes
420 (
421 p_x_rt_oper_resource_rec IN OUT NOCOPY rt_oper_resource_rec_type
422 )
423 IS
424
425 l_old_rt_oper_resource_rec rt_oper_resource_rec_type;
426
427 CURSOR get_old_rec ( c_rt_oper_resource_id NUMBER )
428 IS
429 SELECT aso_resource_id,
430 quantity,
431 duration,
432 attribute_category,
433 attribute1,
434 attribute2,
435 attribute3,
436 attribute4,
437 attribute5,
438 attribute6,
439 attribute7,
440 attribute8,
441 attribute9,
442 attribute10,
443 attribute11,
444 attribute12,
445 attribute13,
446 attribute14,
447 attribute15,
448 scheduled_type_id, -- added for bug fix 6512803.
449 -- Bug # 7644260 (FP for ER # 6998882) -- start
450 schedule_seq
451 -- Bug # 7644260 (FP for ER # 6998882) -- end
452 FROM AHL_RT_OPER_RESOURCES
453 WHERE rt_oper_resource_id = c_rt_oper_resource_id;
454
455 -- Added for bug# 6512803.
456 CURSOR get_res_type_id( c_resource_id In NUMBER)
457 IS
458 SELECT resource_type_id
459 FROM AHL_RESOURCES
460 WHERE resource_id = c_resource_id;
461
462 l_resource_type_id NUMBER;
463
464
465 BEGIN
466
467 -- Get the old record from AHL_RT_OPER_RESOURCES.
468 OPEN get_old_rec( p_x_rt_oper_resource_rec.rt_oper_resource_id );
469
470 FETCH get_old_rec INTO
471 l_old_rt_oper_resource_rec.aso_resource_id,
472 l_old_rt_oper_resource_rec.quantity,
473 l_old_rt_oper_resource_rec.duration,
474 l_old_rt_oper_resource_rec.attribute_category,
475 l_old_rt_oper_resource_rec.attribute1,
476 l_old_rt_oper_resource_rec.attribute2,
477 l_old_rt_oper_resource_rec.attribute3,
478 l_old_rt_oper_resource_rec.attribute4,
479 l_old_rt_oper_resource_rec.attribute5,
480 l_old_rt_oper_resource_rec.attribute6,
481 l_old_rt_oper_resource_rec.attribute7,
482 l_old_rt_oper_resource_rec.attribute8,
483 l_old_rt_oper_resource_rec.attribute9,
484 l_old_rt_oper_resource_rec.attribute10,
485 l_old_rt_oper_resource_rec.attribute11,
486 l_old_rt_oper_resource_rec.attribute12,
487 l_old_rt_oper_resource_rec.attribute13,
488 l_old_rt_oper_resource_rec.attribute14,
489 l_old_rt_oper_resource_rec.attribute15,
490 l_old_rt_oper_resource_rec.scheduled_type_id,
491 -- Bug # 7644260 (FP for ER # 6998882) -- start
492 l_old_rt_oper_resource_rec.schedule_seq ;
493 -- Bug # 7644260 (FP for ER # 6998882) -- end
494
495 IF get_old_rec%NOTFOUND THEN
496 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_RES_REC' );
497 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_rt_oper_resource_rec ) );
498 FND_MSG_PUB.add;
499 CLOSE get_old_rec;
500 RAISE FND_API.G_EXC_ERROR;
501 END IF;
502
503 CLOSE get_old_rec;
504
505 -- Convert G_MISS values to NULL and NULL values to Old values
506 IF ( p_x_rt_oper_resource_rec.resource_type_id = FND_API.G_MISS_NUM ) THEN
507 p_x_rt_oper_resource_rec.resource_type_id := null;
508 ELSIF ( p_x_rt_oper_resource_rec.resource_type_id IS NULL ) THEN
509 p_x_rt_oper_resource_rec.resource_type_id := l_old_rt_oper_resource_rec.resource_type_id;
510 END IF;
511
512 IF ( p_x_rt_oper_resource_rec.resource_type = FND_API.G_MISS_CHAR ) THEN
513 p_x_rt_oper_resource_rec.resource_type := null;
514 ELSIF ( p_x_rt_oper_resource_rec.resource_type IS NULL ) THEN
515 p_x_rt_oper_resource_rec.resource_type := l_old_rt_oper_resource_rec.resource_type;
516 END IF;
517
518 IF ( p_x_rt_oper_resource_rec.aso_resource_id = FND_API.G_MISS_NUM ) THEN
519 p_x_rt_oper_resource_rec.aso_resource_id := null;
520 ELSIF ( p_x_rt_oper_resource_rec.aso_resource_id IS NULL ) THEN
521 p_x_rt_oper_resource_rec.aso_resource_id := l_old_rt_oper_resource_rec.aso_resource_id;
522 END IF;
523
524 IF ( p_x_rt_oper_resource_rec.aso_resource_name = FND_API.G_MISS_CHAR ) THEN
525 p_x_rt_oper_resource_rec.aso_resource_name := null;
526 ELSIF ( p_x_rt_oper_resource_rec.aso_resource_name IS NULL ) THEN
527 p_x_rt_oper_resource_rec.aso_resource_name := l_old_rt_oper_resource_rec.aso_resource_name;
528 END IF;
529
530 IF ( p_x_rt_oper_resource_rec.quantity = FND_API.G_MISS_NUM ) THEN
531 p_x_rt_oper_resource_rec.quantity := null;
532 ELSIF ( p_x_rt_oper_resource_rec.quantity IS NULL ) THEN
533 p_x_rt_oper_resource_rec.quantity := l_old_rt_oper_resource_rec.quantity;
534 END IF;
535
536 IF ( p_x_rt_oper_resource_rec.duration = FND_API.G_MISS_NUM ) THEN
537 p_x_rt_oper_resource_rec.duration := null;
538 ELSIF ( p_x_rt_oper_resource_rec.duration IS NULL ) THEN
539 p_x_rt_oper_resource_rec.duration := l_old_rt_oper_resource_rec.duration;
540 END IF;
541
542 IF ( p_x_rt_oper_resource_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
543 p_x_rt_oper_resource_rec.attribute_category := null;
544 ELSIF ( p_x_rt_oper_resource_rec.attribute_category IS NULL ) THEN
545 p_x_rt_oper_resource_rec.attribute_category := l_old_rt_oper_resource_rec.attribute_category;
546 END IF;
547
548 IF ( p_x_rt_oper_resource_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
549 p_x_rt_oper_resource_rec.attribute1 := null;
550 ELSIF ( p_x_rt_oper_resource_rec.attribute1 IS NULL ) THEN
551 p_x_rt_oper_resource_rec.attribute1 := l_old_rt_oper_resource_rec.attribute1;
552 END IF;
553
554 IF ( p_x_rt_oper_resource_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
555 p_x_rt_oper_resource_rec.attribute2 := null;
556 ELSIF ( p_x_rt_oper_resource_rec.attribute2 IS NULL ) THEN
557 p_x_rt_oper_resource_rec.attribute2 := l_old_rt_oper_resource_rec.attribute2;
558 END IF;
559
560 IF ( p_x_rt_oper_resource_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
561 p_x_rt_oper_resource_rec.attribute3 := null;
562 ELSIF ( p_x_rt_oper_resource_rec.attribute3 IS NULL ) THEN
563 p_x_rt_oper_resource_rec.attribute3 := l_old_rt_oper_resource_rec.attribute3;
564 END IF;
565
566 IF ( p_x_rt_oper_resource_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
567 p_x_rt_oper_resource_rec.attribute4 := null;
568 ELSIF ( p_x_rt_oper_resource_rec.attribute4 IS NULL ) THEN
569 p_x_rt_oper_resource_rec.attribute4 := l_old_rt_oper_resource_rec.attribute4;
570 END IF;
571
572 IF ( p_x_rt_oper_resource_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
573 p_x_rt_oper_resource_rec.attribute5 := null;
574 ELSIF ( p_x_rt_oper_resource_rec.attribute5 IS NULL ) THEN
575 p_x_rt_oper_resource_rec.attribute5 := l_old_rt_oper_resource_rec.attribute5;
576 END IF;
577
578 IF ( p_x_rt_oper_resource_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
579 p_x_rt_oper_resource_rec.attribute6 := null;
580 ELSIF ( p_x_rt_oper_resource_rec.attribute6 IS NULL ) THEN
581 p_x_rt_oper_resource_rec.attribute6 := l_old_rt_oper_resource_rec.attribute6;
582 END IF;
583
584 IF ( p_x_rt_oper_resource_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
585 p_x_rt_oper_resource_rec.attribute7 := null;
586 ELSIF ( p_x_rt_oper_resource_rec.attribute7 IS NULL ) THEN
587 p_x_rt_oper_resource_rec.attribute7 := l_old_rt_oper_resource_rec.attribute7;
588 END IF;
589
590 IF ( p_x_rt_oper_resource_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
591 p_x_rt_oper_resource_rec.attribute8 := null;
592 ELSIF ( p_x_rt_oper_resource_rec.attribute8 IS NULL ) THEN
593 p_x_rt_oper_resource_rec.attribute8 := l_old_rt_oper_resource_rec.attribute8;
594 END IF;
595
596 IF ( p_x_rt_oper_resource_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
597 p_x_rt_oper_resource_rec.attribute9 := null;
598 ELSIF ( p_x_rt_oper_resource_rec.attribute9 IS NULL ) THEN
599 p_x_rt_oper_resource_rec.attribute9 := l_old_rt_oper_resource_rec.attribute9;
600 END IF;
601
602 IF ( p_x_rt_oper_resource_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
603 p_x_rt_oper_resource_rec.attribute10 := null;
604 ELSIF ( p_x_rt_oper_resource_rec.attribute10 IS NULL ) THEN
605 p_x_rt_oper_resource_rec.attribute10 := l_old_rt_oper_resource_rec.attribute10;
606 END IF;
607
608 IF ( p_x_rt_oper_resource_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
609 p_x_rt_oper_resource_rec.attribute11 := null;
610 ELSIF ( p_x_rt_oper_resource_rec.attribute11 IS NULL ) THEN
611 p_x_rt_oper_resource_rec.attribute11 := l_old_rt_oper_resource_rec.attribute11;
612 END IF;
613
614 IF ( p_x_rt_oper_resource_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
615 p_x_rt_oper_resource_rec.attribute12 := null;
616 ELSIF ( p_x_rt_oper_resource_rec.attribute12 IS NULL ) THEN
617 p_x_rt_oper_resource_rec.attribute12 := l_old_rt_oper_resource_rec.attribute12;
618 END IF;
619
620 IF ( p_x_rt_oper_resource_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
621 p_x_rt_oper_resource_rec.attribute13 := null;
622 ELSIF ( p_x_rt_oper_resource_rec.attribute13 IS NULL ) THEN
623 p_x_rt_oper_resource_rec.attribute13 := l_old_rt_oper_resource_rec.attribute13;
624 END IF;
625
626 IF ( p_x_rt_oper_resource_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
627 p_x_rt_oper_resource_rec.attribute14 := null;
628 ELSIF ( p_x_rt_oper_resource_rec.attribute14 IS NULL ) THEN
629 p_x_rt_oper_resource_rec.attribute14 := l_old_rt_oper_resource_rec.attribute14;
630 END IF;
631
632 IF ( p_x_rt_oper_resource_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
633 p_x_rt_oper_resource_rec.attribute15 := null;
634 ELSIF ( p_x_rt_oper_resource_rec.attribute15 IS NULL ) THEN
635 p_x_rt_oper_resource_rec.attribute15 := l_old_rt_oper_resource_rec.attribute15;
636 END IF;
637
638 -- Fix bug# 6512803. Default schedule_type_id based on resource_type_id.
639 IF (p_x_rt_oper_resource_rec.scheduled_type_id = FND_API.G_MISS_NUM OR
640 p_x_rt_oper_resource_rec.scheduled_type_id IS NULL) THEN
641 IF (p_x_rt_oper_resource_rec.aso_resource_id <> l_old_rt_oper_resource_rec.aso_resource_id) THEN
642 OPEN get_res_type_id(p_x_rt_oper_resource_rec.aso_resource_id);
643 FETCH get_res_type_id INTO l_resource_type_id;
644 CLOSE get_res_type_id;
645
646 IF (l_resource_type_id IN (1,2)) THEN
647 p_x_rt_oper_resource_rec.scheduled_type_id := 1;
648 ELSE
649 p_x_rt_oper_resource_rec.scheduled_type_id := 2;
650 END IF;
651
652 ELSE
653 p_x_rt_oper_resource_rec.scheduled_type_id := l_old_rt_oper_resource_rec.scheduled_type_id;
654 END IF;
655 END IF;
656
657 -- Bug # 7644260 (FP for ER # 6998882) -- start
658 IF ( p_x_rt_oper_resource_rec.schedule_seq = FND_API.G_MISS_NUM ) THEN
659 p_x_rt_oper_resource_rec.schedule_seq := null;
660 ELSIF ( p_x_rt_oper_resource_rec.schedule_seq IS NULL ) THEN
661 p_x_rt_oper_resource_rec.schedule_seq := l_old_rt_oper_resource_rec.schedule_seq;
662 END IF;
663 -- Bug # 7644260 (FP for ER # 6998882) -- end
664
665 END default_unchanged_attributes;
666
667 -- Procedure to validate individual rt_oper_resource attributes
668 PROCEDURE validate_attributes
669 (
670 p_object_id IN NUMBER,
671 p_association_type_code IN VARCHAR2,
672 p_rt_oper_resource_rec IN rt_oper_resource_rec_type,
673 x_return_status OUT NOCOPY VARCHAR2
674 )
675 IS
676
677 l_return_status VARCHAR2(1);
678 l_msg_count NUMBER;
679 l_msg_data VARCHAR2(2000);
680 l_max_rt_time_span NUMBER;
681 l_dummy VARCHAR2(1);
682
683 CURSOR check_alternate_resource (c_rt_oper_resource_id number, c_aso_resource_id number) IS
684 SELECT 'X'
685 FROM ahl_alternate_resources
686 WHERE rt_oper_resource_id = c_rt_oper_resource_id
687 AND aso_resource_id = c_aso_resource_id;
688
689 -- Cursor added for the bug 3354746(Resource type is not editable when alternate resources are defined)
690 CURSOR get_old_rec ( c_rt_oper_resource_id NUMBER )
691 IS
692 SELECT resource_type_id,
693 resource_type,
694 aso_resource_name
695 FROM AHL_RT_OPER_RESOURCES_V
696 WHERE rt_oper_resource_id = c_rt_oper_resource_id;
697 l_old_rt_oper_resource_rec rt_oper_resource_rec_type;
698
699 CURSOR alternate_resource_csr( c_rt_oper_resource_id NUMBER)
700 IS
701 SELECT alternate_resource_id FROM AHL_ALTERNATE_RESOURCES
702 WHERE rt_oper_resource_id = c_rt_oper_resource_id;
703 l_alternate_resource_id NUMBER;
704
705 BEGIN
706 x_return_status := FND_API.G_RET_STS_SUCCESS;
707 -- Check if the mandatory column aso_resource_id contains a value.
708 IF ( ( p_rt_oper_resource_rec.dml_operation = 'C' AND
709 p_rt_oper_resource_rec.aso_resource_id IS NULL ) OR
710 ( p_rt_oper_resource_rec.dml_operation <> 'D' AND
711 p_rt_oper_resource_rec.aso_resource_id = FND_API.G_MISS_NUM ) ) THEN
712 FND_MESSAGE.set_name( 'AHL','AHL_RM_ASO_RES_NULL' );
713 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
714 FND_MSG_PUB.add;
715 END IF;
716
717 -- Check if the mandatory quantity column contains a positive value.
718 IF ( ( p_rt_oper_resource_rec.dml_operation = 'C' AND
719 p_rt_oper_resource_rec.quantity IS NULL ) OR
720 ( p_rt_oper_resource_rec.dml_operation <> 'D' AND
721 p_rt_oper_resource_rec.quantity = FND_API.G_MISS_NUM ) ) THEN
722 FND_MESSAGE.set_name( 'AHL','AHL_RM_RES_QTY_NULL' );
723 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
724 FND_MSG_PUB.add;
725 ELSIF ( p_rt_oper_resource_rec.dml_operation <> 'D' AND
726 p_rt_oper_resource_rec.quantity <= 0 ) THEN
727 FND_MESSAGE.set_name( 'AHL','AHL_RM_RES_QTY_LESS_ZERO' );
728 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
729 FND_MSG_PUB.add;
730 END IF;
731
732 -- Check if the mandatory duration column contains a positive value.
733 IF ( ( p_rt_oper_resource_rec.dml_operation = 'C' AND
734 p_rt_oper_resource_rec.duration IS NULL ) OR
735 ( p_rt_oper_resource_rec.dml_operation <> 'D' AND
736 p_rt_oper_resource_rec.duration = FND_API.G_MISS_NUM ) ) THEN
737 FND_MESSAGE.set_name( 'AHL','AHL_RM_DURATION_NULL' );
738 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
739 FND_MSG_PUB.add;
740 ELSIF ( p_rt_oper_resource_rec.dml_operation <> 'D' AND
741 p_rt_oper_resource_rec.duration <= 0 ) THEN
742 FND_MESSAGE.set_name( 'AHL','AHL_RM_DURATION_LESS_ZERO' );
743 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
744 FND_MSG_PUB.add;
745 END IF;
746
747 -- Bug # 7644260 (FP for ER # 6998882) -- start
748 --Check if the schedule sequence column contains a positive value.
749 IF ( p_rt_oper_resource_rec.dml_operation <> 'D' AND
750 p_rt_oper_resource_rec.schedule_seq IS NOT NULL AND
751 p_rt_oper_resource_rec.schedule_seq <> FND_API.G_MISS_NUM AND
752 p_rt_oper_resource_rec.schedule_seq <= 0 ) THEN
753 FND_MESSAGE.set_name( 'AHL','AHL_RM_RES_SEQ_LESS_ZERO' );
754 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
755 FND_MSG_PUB.add;
756 --Check if the schedule sequence column contains a whole number.
757 ELSIF ( p_rt_oper_resource_rec.dml_operation <> 'D' AND
758 p_rt_oper_resource_rec.schedule_seq IS NOT NULL AND
759 p_rt_oper_resource_rec.schedule_seq <> FND_API.G_MISS_NUM AND
760 TRUNC(p_rt_oper_resource_rec.schedule_seq) <> p_rt_oper_resource_rec.schedule_seq ) THEN
761 FND_MESSAGE.set_name( 'AHL','AHL_COM_SCHED_SEQ_INV' );
762 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
763 FND_MSG_PUB.add;
764 END IF;
765 -- Bug # 7644260 (FP for ER # 6998882) -- end
766
767 -- Check if the mandatory Resource Type column does not contain a NULL value.
768 IF ( p_rt_oper_resource_rec.dml_operation = 'C' AND
769 ( p_rt_oper_resource_rec.resource_type IS NULL OR
770 p_rt_oper_resource_rec.resource_type = FND_API.G_MISS_CHAR ) AND
771 ( p_rt_oper_resource_rec.resource_type_id IS NULL OR
772 p_rt_oper_resource_rec.resource_type_id = FND_API.G_MISS_NUM ) ) THEN
773 FND_MESSAGE.set_name( 'AHL','AHL_RM_RES_TYPE_NULL' );
774 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
775 FND_MSG_PUB.add;
776 ELSIF ( p_rt_oper_resource_rec.dml_operation = 'U' AND
777 p_rt_oper_resource_rec.resource_type = FND_API.G_MISS_CHAR AND
778 p_rt_oper_resource_rec.resource_type_id = FND_API.G_MISS_NUM ) THEN
779 FND_MESSAGE.set_name( 'AHL','AHL_RM_RES_TYPE_NULL' );
780 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
781 FND_MSG_PUB.add;
782 END IF;
783 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
784 l_msg_count := FND_MSG_PUB.count_msg;
785 IF l_msg_count > 0 THEN
786 RAISE FND_API.G_EXC_ERROR;
787 END IF;
788
789 /* as part of fix 6512803 moving this validation to validate_record procedure
790 * due to dependency validation between resource_type_id and
791 * scheduled_type_id.
792 -- Validate whether the Duration specified for the Route / Operation Resource is longer than The Route Time Span.
793 IF ( p_rt_oper_resource_rec.duration IS NOT NULL AND
794 p_rt_oper_resource_rec.duration <> FND_API.G_MISS_NUM AND
795 p_rt_oper_resource_rec.duration > 0 ) THEN
796
797 AHL_RM_ROUTE_UTIL.validate_resource_duration
798 (
799 x_return_status => l_return_status,
800 x_msg_data => l_msg_data,
801 p_object_id => p_object_id,
802 p_association_type_code=> p_association_type_code,
803 p_duration => p_rt_oper_resource_rec.duration,
804 x_max_rt_time_span => l_max_rt_time_span
805 );
806
807 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
808 FND_MESSAGE.set_name( 'AHL', l_msg_data );
809 FND_MESSAGE.set_token( 'FIELD1', p_rt_oper_resource_rec.duration );
810 FND_MESSAGE.set_token( 'FIELD2', l_max_rt_time_span );
811 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
812 FND_MSG_PUB.add;
813 END IF;
814 END IF;
815 -- end changes for fix 6512803. */
816
817 IF ( p_rt_oper_resource_rec.dml_operation = 'C' ) THEN
818 RETURN;
819 END IF;
820
821 -- Check if the ASO resource already existing as an alternate resource when updating the primary ASO resource.
822 OPEN check_alternate_resource(p_rt_oper_resource_rec.rt_oper_resource_id, p_rt_oper_resource_rec.aso_resource_id);
823 FETCH check_alternate_resource into l_dummy;
824 IF check_alternate_resource%FOUND THEN
825 FND_MESSAGE.set_name( 'AHL','AHL_RM_ALTERNATE_RES_EXISTS' );
826 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
827 FND_MSG_PUB.add;
828 END IF;
829 CLOSE check_alternate_resource;
830
831 -- Check if the mandatory Object Version Number column contains a null value.
832 IF ( p_rt_oper_resource_rec.object_version_number IS NULL OR
833 p_rt_oper_resource_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
834 FND_MESSAGE.set_name( 'AHL','AHL_RM_RES_OBJ_VERSION_NULL' );
835 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
836 FND_MSG_PUB.add;
837 END IF;
838
839 -- Check if the mandatory rt_oper_resource ID column contains a null value.
840 IF ( p_rt_oper_resource_rec.rt_oper_resource_id IS NULL OR
841 p_rt_oper_resource_rec.rt_oper_resource_id = FND_API.G_MISS_NUM ) THEN
842 FND_MESSAGE.set_name( 'AHL','AHL_RM_RT_OPER_RES_ID_NULL' );
843 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
844 FND_MSG_PUB.add;
845 END IF;
846
847 -- Get the old record from AHL_RT_OPER_RESOURCES.
848 OPEN get_old_rec( p_rt_oper_resource_rec.rt_oper_resource_id );
849
850 FETCH get_old_rec INTO
851 l_old_rt_oper_resource_rec.resource_type_id,
852 l_old_rt_oper_resource_rec.resource_type,
853 l_old_rt_oper_resource_rec.aso_resource_name;
854
855 -- Check added by balaji for the bug 3354746
856 IF l_old_rt_oper_resource_rec.resource_type_id <> p_rt_oper_resource_rec.resource_type_id
857 OR l_old_rt_oper_resource_rec.resource_type <> p_rt_oper_resource_rec.resource_type
858 THEN
859 OPEN alternate_resource_csr(p_rt_oper_resource_rec.rt_oper_resource_id);
860 FETCH alternate_resource_csr INTO l_alternate_resource_id;
861 -- Check if alternate resources are defined for the primary resource, if so throw error that
862 -- "Resource type cannot be changed if alternate resources are defined".
863 IF alternate_resource_csr%FOUND THEN
864 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RES_TYPE_NO_CHG' );
865 -- get_record_identifier can't be used here as it takes old record as parameter.
866 FND_MESSAGE.set_token( 'RECORD', l_old_rt_oper_resource_rec.resource_type
867 || ' - '
868 || l_old_rt_oper_resource_rec.aso_resource_name);
869 FND_MSG_PUB.add;
870 RAISE FND_API.G_EXC_ERROR;
871 END IF;
872 CLOSE alternate_resource_csr;
873 END IF;
874
875 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
876 l_msg_count := FND_MSG_PUB.count_msg;
877 IF l_msg_count > 0 THEN
878 RAISE FND_API.G_EXC_ERROR;
879 END IF;
880
881
882 END validate_attributes;
883
884 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
885 -- Added p_object_id and p_association_type_code to fix bug# 6512803.
886 PROCEDURE validate_record
887 (
888 p_rt_oper_resource_rec IN rt_oper_resource_rec_type,
889 p_object_id IN NUMBER,
890 p_association_type_code IN VARCHAR2,
891 x_return_status OUT NOCOPY VARCHAR2
892 )
893 IS
894
895 l_dummy VARCHAR2(1);
896
897 CURSOR check_resource_type( c_aso_resource_id NUMBER, c_resource_type_id NUMBER )
898 IS
899 SELECT 'X'
900 FROM AHL_RESOURCES
901 WHERE resource_id = c_aso_resource_id
902 AND resource_type_id = c_resource_type_id;
903
904 -- Added to fix bug# 6512803.
905 l_return_status VARCHAR2(1);
906 l_msg_count NUMBER;
907 l_msg_data VARCHAR2(2000);
908 l_max_rt_time_span NUMBER;
909
910 BEGIN
911 x_return_status := FND_API.G_RET_STS_SUCCESS;
912
913 -- Ensure that ASO Resource and it's Resource Type Match
914 IF ( p_rt_oper_resource_rec.resource_type_id IS NOT NULL AND
915 p_rt_oper_resource_rec.aso_resource_id IS NOT NULL ) THEN
916
917 OPEN check_resource_type( p_rt_oper_resource_rec.aso_resource_id,
918 p_rt_oper_resource_rec.resource_type_id );
919
920 FETCH check_resource_type INTO
921 l_dummy;
922
923 IF ( check_resource_type%NOTFOUND ) THEN
924
925 x_return_status := FND_API.G_RET_STS_ERROR ;
926 FND_MESSAGE.set_name( 'AHL','AHL_RM_INVALID_TYPE_ASO_RES' );
927
928 IF ( p_rt_oper_resource_rec.aso_resource_name IS NULL ) THEN
929 FND_MESSAGE.set_token( 'FIELD1', TO_CHAR( p_rt_oper_resource_rec.aso_resource_id ) );
930 ELSE
931 FND_MESSAGE.set_token( 'FIELD1', p_rt_oper_resource_rec.aso_resource_name );
932 END IF;
933
934 IF ( p_rt_oper_resource_rec.resource_type IS NULL ) THEN
935 FND_MESSAGE.set_token( 'FIELD2', TO_CHAR( p_rt_oper_resource_rec.resource_type_id ) );
936 ELSE
937 FND_MESSAGE.set_token( 'FIELD2', p_rt_oper_resource_rec.resource_type );
938 END IF;
939
940 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
941 FND_MSG_PUB.add;
942 END IF;
943 END IF;
944
945 -- Validate whether the Duration specified for the Route / Operation Resource is longer than The Route Time Span.
946 IF ( p_rt_oper_resource_rec.duration IS NOT NULL AND
947 p_rt_oper_resource_rec.duration <> FND_API.G_MISS_NUM AND
948 p_rt_oper_resource_rec.duration > 0 ) AND
949 -- Added to fix bug# 6512803. Validate only for scheduled person and machine type resources.
950 ( p_rt_oper_resource_rec.resource_type_id IS NOT NULL AND
951 p_rt_oper_resource_rec.resource_type_id <> FND_API.G_MISS_NUM AND
952 p_rt_oper_resource_rec.resource_type_id IN (1,2) ) AND
953 ( p_rt_oper_resource_rec.scheduled_type_id = 1 )
954 THEN
955
956 AHL_RM_ROUTE_UTIL.validate_resource_duration
957 (
958 x_return_status => l_return_status,
959 x_msg_data => l_msg_data,
960 p_object_id => p_object_id,
961 p_association_type_code=> p_association_type_code,
962 p_duration => p_rt_oper_resource_rec.duration,
963 x_max_rt_time_span => l_max_rt_time_span
964 );
965
966 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
967 x_return_status := FND_API.G_RET_STS_ERROR ;
968 FND_MESSAGE.set_name( 'AHL', l_msg_data );
969 FND_MESSAGE.set_token( 'FIELD1', p_rt_oper_resource_rec.duration );
970 FND_MESSAGE.set_token( 'FIELD2', l_max_rt_time_span );
971 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_rt_oper_resource_rec ) );
972 FND_MSG_PUB.add;
973 --dbms_output.put_line('validate_resource_duration error');
974 END IF;
975 END IF;
976
977 END validate_record;
978
979 -- Procedure to Perform cross records validation and duplicate checks
980 PROCEDURE validate_records
981 (
982 p_object_id IN NUMBER,
983 p_association_type_code IN VARCHAR2,
984 x_return_status OUT NOCOPY VARCHAR2
985 )
986 IS
987
988 CURSOR get_dup_rec ( c_object_id NUMBER, c_association_type_code VARCHAR2 )
989 IS
990 SELECT resource_type_id,
991 resource_type,
992 aso_resource_id,
993 aso_resource_name
994 FROM AHL_RT_OPER_RESOURCES_V
995 WHERE object_id = c_object_id
996 AND association_type_code = c_association_type_code
997 GROUP BY resource_type_id,
998 resource_type,
999 aso_resource_id,
1000 aso_resource_name
1001 HAVING count(*) > 1;
1002
1003 --pdoki ER 7436910 Begin.
1004 --checking dept conflicts among primary resources
1005 CURSOR get_dept_conflicts ( c_object_id NUMBER, c_association_type_code VARCHAR2 )
1006 IS
1007 SELECT 'X'
1008 FROM ahl_resource_mappings
1009 WHERE DEPARTMENT_ID IS NOT NULL
1010 AND aso_resource_id in
1011 (SELECT ASO_RESOURCE_ID
1012 FROM ahl_rt_oper_resources
1013 WHERE object_id = c_object_id
1014 AND ASSOCIATION_TYPE_CODE = c_association_type_code
1015 )
1016 GROUP BY bom_org_id
1017 HAVING count(DISTINCT DEPARTMENT_ID) > 1;
1018
1019 --checking dept conflicts b/w the primary resource and alt resources of OTHER primary resources
1020 CURSOR get_dept_conflicts_alt_res ( c_object_id NUMBER, c_association_type_code VARCHAR2, c_rt_oper_res_id NUMBER)
1021 IS
1022 SELECT 'X'
1023 FROM ahl_resource_mappings
1024 WHERE DEPARTMENT_ID IS NOT NULL
1025 AND aso_resource_id in
1026 (SELECT ALTR.ASO_RESOURCE_ID
1027 FROM ahl_alternate_resources ALTR,
1028 (SELECT RT_OPER_RESOURCE_ID
1029 FROM ahl_rt_oper_resources
1030 WHERE RT_OPER_RESOURCE_ID <> c_rt_oper_res_id
1031 AND ASSOCIATION_TYPE_CODE = c_association_type_code
1032 AND OBJECT_ID = c_object_id
1033 )
1034 ROR
1035 WHERE ROR.RT_OPER_RESOURCE_ID = ALTR.rt_oper_resource_id
1036 )
1037 OR aso_resource_id =
1038 (SELECT aso_resource_id
1039 FROM ahl_rt_oper_resources
1040 WHERE RT_OPER_RESOURCE_ID = c_rt_oper_res_id
1041 )
1042 GROUP BY bom_org_id
1043 HAVING count(DISTINCT DEPARTMENT_ID) > 1;
1044
1045 CURSOR get_rt_oper_res_ids ( c_object_id NUMBER, c_association_type_code VARCHAR2)
1046 IS
1047 SELECT RT_OPER_RESOURCE_ID
1048 FROM AHL_RT_OPER_RESOURCES
1049 WHERE ASSOCIATION_TYPE_CODE = c_association_type_code
1050 AND OBJECT_ID = c_object_id;
1051 --pdoki ER 7436910 End.
1052
1053 l_rt_oper_resource_rec rt_oper_resource_rec_type;
1054 l_dummy VARCHAR(1);
1055 l_rt_oper_res_id NUMBER;
1056
1057 BEGIN
1058
1059 x_return_status := FND_API.G_RET_STS_SUCCESS;
1060 -- Check whether any duplicate rt_oper_resource records for the given object_ID
1061 OPEN get_dup_rec( p_object_id, p_association_type_code );
1062
1063 LOOP
1064 FETCH get_dup_rec INTO
1065 l_rt_oper_resource_rec.resource_type_id,
1066 l_rt_oper_resource_rec.resource_type,
1067 l_rt_oper_resource_rec.aso_resource_id,
1068 l_rt_oper_resource_rec.aso_resource_name;
1069
1070 EXIT WHEN get_dup_rec%NOTFOUND;
1071 END LOOP;
1072
1073 IF ( get_dup_rec%ROWCOUNT > 0 ) THEN
1074 CLOSE get_dup_rec;
1075 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RT_OPER_RESOURCE_DUP' );
1076 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_rt_oper_resource_rec ) );
1077 FND_MSG_PUB.add;
1078 x_return_status := FND_API.G_RET_STS_ERROR;
1079 RETURN;
1080 END IF;
1081
1082 CLOSE get_dup_rec;
1083
1084 --pdoki ER 7436910 Begin.
1085 --checking dept conflicts among primary resources
1086 OPEN get_dept_conflicts( p_object_id, p_association_type_code );
1087
1088 FETCH get_dept_conflicts INTO l_dummy;
1089
1090 IF ( get_dept_conflicts%FOUND ) THEN
1091 CLOSE get_dept_conflicts;
1092 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_DEP_CONFLICT_RES' );
1093 FND_MSG_PUB.add;
1094 x_return_status := FND_API.G_RET_STS_ERROR;
1095 RETURN;
1096 END IF;
1097
1098 CLOSE get_dept_conflicts;
1099
1100 --checking dept conflicts b/w the primary resource and alt resources of OTHER primary resources
1101 OPEN get_rt_oper_res_ids( p_object_id, p_association_type_code );
1102
1103 LOOP
1104 FETCH get_rt_oper_res_ids INTO l_rt_oper_res_id;
1105
1106 EXIT WHEN get_rt_oper_res_ids%NOTFOUND;
1107
1108 OPEN get_dept_conflicts_alt_res( p_object_id, p_association_type_code, l_rt_oper_res_id);
1109
1110 FETCH get_dept_conflicts_alt_res INTO l_dummy;
1111
1112 IF ( get_dept_conflicts_alt_res%FOUND ) THEN
1113 CLOSE get_dept_conflicts_alt_res;
1114 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_DEP_CONFLICT_RES' );
1115 FND_MSG_PUB.add;
1116 x_return_status := FND_API.G_RET_STS_ERROR;
1117 RAISE FND_API.G_EXC_ERROR;
1118 END IF;
1119
1120 CLOSE get_dept_conflicts_alt_res;
1121
1122 END LOOP;
1123
1124 CLOSE get_rt_oper_res_ids;
1125 --pdoki ER 7436910 End.
1126
1127 END validate_records;
1128
1129 PROCEDURE process_rt_oper_resource
1130 (
1131 p_api_version IN NUMBER := 1.0,
1132 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1133 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1134 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1135 p_default IN VARCHAR2 := FND_API.G_FALSE,
1136 p_module_type IN VARCHAR2 := NULL,
1137 x_return_status OUT NOCOPY VARCHAR2,
1138 x_msg_count OUT NOCOPY NUMBER,
1139 x_msg_data OUT NOCOPY VARCHAR2,
1140 p_x_rt_oper_resource_tbl IN OUT NOCOPY rt_oper_resource_tbl_type,
1141 p_association_type_code IN VARCHAR2,
1142 p_object_id IN NUMBER
1143 )
1144 IS
1145
1146 cursor get_route_status (p_route_id in number)
1147 is
1148 select revision_status_code
1149 from ahl_routes_app_v
1150 where route_id = p_route_id;
1151
1152 l_obj_status VARCHAR2(30);
1153 -- Bug # 7644260 (FP for ER # 6998882) -- start
1154 l_min_sch_seq NUMBER ;
1155 -- Bug # 7644260 (FP for ER # 6998882) -- end
1156
1157 cursor get_oper_status (p_operation_id in number)
1158 is
1159 select revision_status_code
1160 from ahl_operations_b
1161 where operation_id = p_operation_id;
1162
1163 -- Bug # 7644260 (FP for ER # 6998882) -- start
1164 cursor get_min_sch_seq ( c_object_id NUMBER, c_association_type_code VARCHAR2 )
1165 is
1166 select min(schedule_seq)
1167 from ahl_rt_oper_resources
1168 where object_id = c_object_id
1169 and association_type_code = c_association_type_code
1170 and schedule_seq IS NOT NULL ;
1171 -- Bug # 7644260 (FP for ER # 6998882) -- end
1172
1173 l_api_version CONSTANT NUMBER := 1.0;
1174 l_return_status VARCHAR2(1);
1175 l_msg_count NUMBER;
1176 l_msg_data VARCHAR2(2000);
1177 l_rt_oper_resource_id NUMBER;
1178 l_x_operation_rec AHL_RM_OPERATION_PVT.operation_rec_type ;
1179 l_x_route_rec AHL_RM_ROUTE_PVT.route_rec_type ;
1180 BEGIN
1181 -- Initialize API return status to success
1182 x_return_status := FND_API.G_RET_STS_SUCCESS;
1183
1184 -- Standard Start of API savepoint
1185 SAVEPOINT process_rt_oper_resource_pvt;
1186
1187 -- Standard call to check for call compatibility.
1188 IF NOT FND_API.compatible_api_call
1189 (
1190 l_api_version,
1191 p_api_version,
1192 G_API_NAME1,
1193 G_PKG_NAME
1194 )
1195 THEN
1196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1197 END IF;
1198
1199 -- Initialize message list if p_init_msg_list is set to TRUE.
1200 IF FND_API.to_boolean( p_init_msg_list ) THEN
1201 FND_MSG_PUB.initialize;
1202 END IF;
1203
1204 IF G_DEBUG = 'Y' THEN
1205 AHL_DEBUG_PUB.enable_debug;
1206 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : Begin API' );
1207 END IF;
1208
1209
1210 --This is to be added before calling validate_api_inputs()
1211 -- Validate Application Usage
1212 IF (p_association_type_code = 'ROUTE')
1213 THEN
1214 AHL_RM_ROUTE_UTIL.validate_ApplnUsage
1215 (
1216 p_object_id => p_object_id,
1217 p_association_type => p_association_type_code ,
1218 x_return_status => x_return_status,
1219 x_msg_data => x_msg_data
1220 );
1221
1222 -- If any severe error occurs, then, abort API.
1223 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1224 RAISE FND_API.G_EXC_ERROR;
1225 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1227 END IF;
1228 END IF ;
1229
1230
1231 -- Validate all the inputs of the API
1232 validate_api_inputs
1233 (
1234 p_x_rt_oper_resource_tbl,
1235 p_association_type_code,
1236 p_object_id,
1237 l_return_status
1238 );
1239
1240 -- If any severe error occurs, then, abort API.
1241 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1242 RAISE FND_API.G_EXC_ERROR;
1243 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1245 END IF;
1246
1247 -- If the module type is JSP, then default values for ID columns of LOV attributes
1248 IF ( p_module_type = 'JSP' ) THEN
1249 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1250 IF ( p_x_rt_oper_resource_tbl(i).dml_operation <> 'D' ) THEN
1251 clear_lov_attribute_ids
1252 (
1253 p_x_rt_oper_resource_tbl(i) -- IN OUT Record with Values and Ids
1254 );
1255 END IF;
1256 END LOOP;
1257 END IF;
1258
1259 -- Convert Values into Ids.
1260 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
1261 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1262 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1263 IF ( p_x_rt_oper_resource_tbl(i).dml_operation <> 'D' ) THEN
1264 convert_values_to_ids
1265 (
1266 p_x_rt_oper_resource_tbl(i) , -- IN OUT Record with Values and Ids
1267 l_return_status -- OUT
1268 );
1269
1270 -- If any severe error occurs, then, abort API.
1271 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1272 RAISE FND_API.G_EXC_ERROR;
1273 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1275 END IF;
1276 END IF;
1277 END LOOP;
1278 --END IF;
1279
1280 IF G_DEBUG = 'Y' THEN
1281 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after convert_values_to_ids' );
1282 END IF;
1283
1284 -- Default rt_oper_resource attributes.
1285 /* Removed as a part of public API cleanup in 11510+.
1286 IF FND_API.to_boolean( p_default ) THEN
1287 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1288 IF ( p_x_rt_oper_resource_tbl(i).dml_operation <> 'D' ) THEN
1289 default_attributes
1290 (
1291 p_x_rt_oper_resource_tbl(i) -- IN OUT
1292 );
1293 END IF;
1294 END LOOP;
1295 END IF;
1296 */
1297
1298 IF G_DEBUG = 'Y' THEN
1299 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after default_attributes' );
1300 END IF;
1301
1302 -- Validate all attributes (Item level validation)
1303 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
1304 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1305 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1306 validate_attributes
1307 (
1308 p_object_id, -- IN
1309 p_association_type_code, -- IN
1310 p_x_rt_oper_resource_tbl(i), -- IN
1311 l_return_status -- OUT
1312 );
1313
1314 -- If any severe error occurs, then, abort API.
1315 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1316 RAISE FND_API.G_EXC_ERROR;
1317 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1319 END IF;
1320 END LOOP;
1321 --END IF;
1322
1323 IF G_DEBUG = 'Y' THEN
1324 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after validate_attributes' );
1325 END IF;
1326
1327 -- Default missing and unchanged attributes.
1328 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1329 IF ( p_x_rt_oper_resource_tbl(i).dml_operation = 'U' ) THEN
1330 default_unchanged_attributes
1331 (
1332 p_x_rt_oper_resource_tbl(i) -- IN OUT
1333 );
1334 ELSIF ( p_x_rt_oper_resource_tbl(i).dml_operation = 'C' ) THEN
1335 default_missing_attributes
1336 (
1337 p_x_rt_oper_resource_tbl(i) -- IN OUT
1338 );
1339 END IF;
1340 END LOOP;
1341
1342 IF G_DEBUG = 'Y' THEN
1343 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after default_unchanged_attributes / default_missing_attributes' );
1344 END IF;
1345
1346 -- Perform cross attribute validation and missing attribute checks (Record level validation)
1347 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
1348 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1349 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1350 IF ( p_x_rt_oper_resource_tbl(i).dml_operation <> 'D' ) THEN
1351 validate_record
1352 (
1353 p_x_rt_oper_resource_tbl(i), -- IN
1354 p_object_id,
1355 p_association_type_code,
1356 l_return_status -- OUT
1357 );
1358
1359 -- If any severe error occurs, then, abort API.
1360 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1361 RAISE FND_API.G_EXC_ERROR;
1362 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1364 END IF;
1365 END IF;
1366 END LOOP;
1367 --END IF;
1368
1369 IF G_DEBUG = 'Y' THEN
1370 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after validate_record' );
1371 END IF;
1372
1373 IF G_DEBUG='Y' THEN
1374 AHL_DEBUG_PUB.debug( 'Starting updating parent route/operation');
1375 END IF;
1376
1377 IF ( p_association_type_code = 'OPERATION')
1378 THEN
1379 IF G_DEBUG='Y' THEN
1380 AHL_DEBUG_PUB.debug( 'p_association_type_code = OPERATION');
1381 END IF;
1382
1383 AHL_RM_ROUTE_UTIL.validate_operation_status
1384 (
1385 p_object_id,
1386 l_msg_data,
1387 l_return_status
1388 );
1389
1390 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1391 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
1392 FND_MSG_PUB.ADD;
1393 x_return_status := l_return_status;
1394 RETURN;
1395 END IF;
1396
1397 -- Update route status from APPROVAL_REJECTED to DRAFT
1398 OPEN get_oper_status (p_object_id);
1399 FETCH get_oper_status INTO l_obj_status;
1400 IF (get_oper_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
1401 THEN
1402 UPDATE ahl_operations_b
1403 SET revision_status_code = 'DRAFT'
1404 WHERE operation_id = p_object_id;
1405 END IF;
1406 CLOSE get_oper_status;
1407
1408 ELSIF ( p_association_type_code = 'ROUTE')
1409 THEN
1410 IF G_DEBUG='Y' THEN
1411 AHL_DEBUG_PUB.debug( 'p_association_type_code = ROUTE');
1412 END IF;
1413 -- Check if the Route is existing and in Draft status
1414 AHL_RM_ROUTE_UTIL.validate_route_status
1415 (
1416 p_object_id,
1417 l_msg_data,
1418 l_return_status
1419 );
1420
1421 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1422 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
1423 FND_MSG_PUB.ADD;
1424 x_return_status := l_return_status;
1425 RETURN;
1426 END IF;
1427
1428 -- Update route status from APPROVAL_REJECTED to DRAFT
1429 OPEN get_route_status (p_object_id);
1430 FETCH get_route_status INTO l_obj_status;
1431 IF (get_route_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
1432 THEN
1433 UPDATE ahl_routes_b
1434 SET revision_status_code = 'DRAFT'
1435 WHERE route_id = p_object_id;
1436 END IF;
1437 CLOSE get_route_status;
1438
1439 END IF ;
1440
1441
1442 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1443 l_msg_count := FND_MSG_PUB.count_msg;
1444 IF l_msg_count > 0 THEN
1445 x_msg_count := l_msg_count;
1446 RAISE FND_API.G_EXC_ERROR;
1447 END IF;
1448
1449 -- Perform the DML statement directly.
1450 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1451 IF ( p_x_rt_oper_resource_tbl(i).dml_operation = 'C' ) THEN
1452
1453 BEGIN
1454 -- Insert the record
1455 INSERT INTO AHL_RT_OPER_RESOURCES
1456 (
1457 rt_oper_resource_ID,
1458 OBJECT_VERSION_NUMBER,
1459 LAST_UPDATE_DATE,
1460 LAST_UPDATED_BY,
1461 CREATION_DATE,
1462 CREATED_BY,
1463 LAST_UPDATE_LOGIN,
1464 association_type_code,
1465 object_ID,
1466 aso_resource_id,
1467 quantity,
1468 duration,
1469 ATTRIBUTE_CATEGORY,
1470 ATTRIBUTE1,
1471 ATTRIBUTE2,
1472 ATTRIBUTE3,
1473 ATTRIBUTE4,
1474 ATTRIBUTE5,
1475 ATTRIBUTE6,
1476 ATTRIBUTE7,
1477 ATTRIBUTE8,
1478 ATTRIBUTE9,
1479 ATTRIBUTE10,
1480 ATTRIBUTE11,
1481 ATTRIBUTE12,
1482 ATTRIBUTE13,
1483 ATTRIBUTE14,
1484 ATTRIBUTE15,
1485 scheduled_type_id, -- added for bug fix 6512803.
1486 -- Bug # 7644260 (FP for ER # 6998882) -- start
1487 schedule_seq
1488 -- Bug # 7644260 (FP for ER # 6998882) -- end
1489 ) VALUES
1490 (
1491 AHL_RT_OPER_RESOURCES_S.NEXTVAL,
1492 1,
1493 G_LAST_UPDATE_DATE,
1494 G_LAST_UPDATED_BY,
1495 G_CREATION_DATE,
1496 G_CREATED_BY,
1497 G_LAST_UPDATE_LOGIN,
1498 p_association_type_code,
1499 p_object_id,
1500 p_x_rt_oper_resource_tbl(i).aso_resource_id,
1501 p_x_rt_oper_resource_tbl(i).quantity,
1502 p_x_rt_oper_resource_tbl(i).duration,
1503 p_x_rt_oper_resource_tbl(i).attribute_category,
1504 p_x_rt_oper_resource_tbl(i).attribute1,
1505 p_x_rt_oper_resource_tbl(i).attribute2,
1506 p_x_rt_oper_resource_tbl(i).attribute3,
1507 p_x_rt_oper_resource_tbl(i).attribute4,
1508 p_x_rt_oper_resource_tbl(i).attribute5,
1509 p_x_rt_oper_resource_tbl(i).attribute6,
1510 p_x_rt_oper_resource_tbl(i).attribute7,
1511 p_x_rt_oper_resource_tbl(i).attribute8,
1512 p_x_rt_oper_resource_tbl(i).attribute9,
1513 p_x_rt_oper_resource_tbl(i).attribute10,
1514 p_x_rt_oper_resource_tbl(i).attribute11,
1515 p_x_rt_oper_resource_tbl(i).attribute12,
1516 p_x_rt_oper_resource_tbl(i).attribute13,
1517 p_x_rt_oper_resource_tbl(i).attribute14,
1518 p_x_rt_oper_resource_tbl(i).attribute15,
1519 p_x_rt_oper_resource_tbl(i).scheduled_type_id,
1520 -- Bug # 7644260 (FP for ER # 6998882) -- start
1521 p_x_rt_oper_resource_tbl(i).schedule_seq
1522 -- Bug # 7644260 (FP for ER # 6998882) -- end
1523 ) RETURNING rt_oper_resource_id INTO l_rt_oper_resource_id;
1524
1525 -- Set OUT values
1526 p_x_rt_oper_resource_tbl(i).rt_oper_resource_id := l_rt_oper_resource_id;
1527
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 IF ( SQLCODE = -1 ) THEN
1531 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RT_OPER_RESOURCE_DUP' );
1532 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_x_rt_oper_resource_tbl(i) ) );
1533 FND_MSG_PUB.add;
1534 ELSE
1535 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1536 fnd_log.string
1537 (
1538 fnd_log.level_unexpected,
1539 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME1,
1540 'AHL_RT_OPER_RESOURCES insert error = ['||SQLERRM||']'
1541 );
1542 END IF;
1543 END IF;
1544 END;
1545
1546 ELSIF ( p_x_rt_oper_resource_tbl(i).dml_operation = 'U' ) THEN
1547
1548 BEGIN
1549 -- Update the record
1550 UPDATE AHL_RT_OPER_RESOURCES SET
1551 object_version_number = object_version_number + 1,
1552 last_update_date = G_LAST_UPDATE_DATE,
1553 last_updated_by = G_LAST_UPDATED_BY,
1554 last_update_login = G_LAST_UPDATE_LOGIN,
1555 aso_resource_id = p_x_rt_oper_resource_tbl(i).aso_resource_id,
1556 quantity = p_x_rt_oper_resource_tbl(i).quantity,
1557 duration = p_x_rt_oper_resource_tbl(i).duration,
1558 attribute_category = p_x_rt_oper_resource_tbl(i).attribute_category,
1559 attribute1 = p_x_rt_oper_resource_tbl(i).attribute1,
1560 attribute2 = p_x_rt_oper_resource_tbl(i).attribute2,
1561 attribute3 = p_x_rt_oper_resource_tbl(i).attribute3,
1562 attribute4 = p_x_rt_oper_resource_tbl(i).attribute4,
1563 attribute5 = p_x_rt_oper_resource_tbl(i).attribute5,
1564 attribute6 = p_x_rt_oper_resource_tbl(i).attribute6,
1565 attribute7 = p_x_rt_oper_resource_tbl(i).attribute7,
1566 attribute8 = p_x_rt_oper_resource_tbl(i).attribute8,
1567 attribute9 = p_x_rt_oper_resource_tbl(i).attribute9,
1568 attribute10 = p_x_rt_oper_resource_tbl(i).attribute10,
1569 attribute11 = p_x_rt_oper_resource_tbl(i).attribute11,
1570 attribute12 = p_x_rt_oper_resource_tbl(i).attribute12,
1571 attribute13 = p_x_rt_oper_resource_tbl(i).attribute13,
1572 attribute14 = p_x_rt_oper_resource_tbl(i).attribute14,
1573 attribute15 = p_x_rt_oper_resource_tbl(i).attribute15,
1574 -- added for bug fix# 6512803.
1575 scheduled_type_id = p_x_rt_oper_resource_tbl(i).scheduled_type_id,
1576 -- Bug # 7644260 (FP for ER # 6998882) -- start
1577 schedule_seq = p_x_rt_oper_resource_tbl(i).schedule_seq
1578 -- Bug # 7644260 (FP for ER # 6998882) -- end
1579 WHERE rt_oper_resource_id = p_x_rt_oper_resource_tbl(i).rt_oper_resource_id
1580 AND object_version_number = p_x_rt_oper_resource_tbl(i).object_version_number;
1581
1582 -- If the record does not exist, then, abort API.
1583 IF ( SQL%ROWCOUNT = 0 ) THEN
1584 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1585 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_rt_oper_resource_tbl(i) ) );
1586 FND_MSG_PUB.add;
1587 END IF;
1588
1589 -- Set OUT values
1590 p_x_rt_oper_resource_tbl(i).object_version_number := p_x_rt_oper_resource_tbl(i).object_version_number + 1;
1591
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 IF ( SQLCODE = -1 ) THEN
1595 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RT_OPER_RESOURCE_DUP' );
1596 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_rt_oper_resource_tbl(i) ) );
1597 FND_MSG_PUB.add;
1598 ELSE
1599 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1600 fnd_log.string
1601 (
1602 fnd_log.level_unexpected,
1603 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME1,
1604 'AHL_RT_OPER_RESOURCES update error = ['||SQLERRM||']'
1605 );
1606 END IF;
1607 END IF;
1608 END;
1609
1610 ELSIF ( p_x_rt_oper_resource_tbl(i).dml_operation = 'D' ) THEN
1611 --pdoki ER 7436910
1612 --Deleting alternate_resource mappings for deleted resource.
1613 DELETE FROM ahl_alternate_resources
1614 WHERE rt_oper_resource_id = p_x_rt_oper_resource_tbl(i).rt_oper_resource_id;
1615 --pdoki ER 7436910
1616
1617 -- Delete the record
1618 DELETE FROM AHL_RT_OPER_RESOURCES
1619 WHERE rt_oper_resource_id = p_x_rt_oper_resource_tbl(i).rt_oper_resource_id
1620 AND object_version_number = p_x_rt_oper_resource_tbl(i).object_version_number;
1621
1622 -- If the record does not exist, then, abort API.
1623 IF ( SQL%ROWCOUNT = 0 ) THEN
1624 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1625 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
1626 FND_MSG_PUB.add;
1627 END IF;
1628 END IF;
1629 END LOOP;
1630
1631 IF G_DEBUG = 'Y' THEN
1632 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after DML operation' );
1633 END IF;
1634
1635 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1636 l_msg_count := FND_MSG_PUB.count_msg;
1637 IF l_msg_count > 0 THEN
1638 x_msg_count := l_msg_count;
1639 RAISE FND_API.G_EXC_ERROR;
1640 END IF;
1641
1642 -- Perform cross records validations and duplicate records check
1643 validate_records
1644 (
1645 p_object_id, -- IN
1646 p_association_type_code,
1647 l_return_status -- OUT
1648 );
1649
1650 -- If any severe error occurs, then, abort API.
1651 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1652 RAISE FND_API.G_EXC_ERROR;
1653 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1655 END IF;
1656
1657 IF G_DEBUG = 'Y' THEN
1658 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after validate_records' );
1659 END IF;
1660
1661 -- Bug # 7644260 (FP for ER # 6998882) -- start
1662 OPEN get_min_sch_seq( p_object_id, p_association_type_code );
1663 FETCH get_min_sch_seq INTO l_min_sch_seq ;
1664 IF get_min_sch_seq%FOUND THEN
1665 UPDATE ahl_rt_oper_resources
1666 SET schedule_seq = l_min_sch_seq
1667 WHERE object_id = p_object_id
1668 AND association_type_code = p_association_type_code
1669 AND schedule_seq IS NULL ;
1670 END IF;
1671 CLOSE get_min_sch_seq;
1672 -- Bug # 7644260 (FP for ER # 6998882) -- end
1673
1674 -- Perform the Commit (if requested)
1675 IF FND_API.to_boolean( p_commit ) THEN
1676 COMMIT WORK;
1677 END IF;
1678
1679 -- Count and Get messages (optional)
1680 FND_MSG_PUB.count_and_get
1681 (
1682 p_encoded => FND_API.G_FALSE,
1683 p_count => x_msg_count,
1684 p_data => x_msg_data
1685 );
1686
1687 -- Disable debug (if enabled)
1688 IF G_DEBUG = 'Y' THEN
1689 AHL_DEBUG_PUB.disable_debug;
1690 END IF;
1691 EXCEPTION
1692 WHEN FND_API.G_EXC_ERROR THEN
1693 ROLLBACK TO process_rt_oper_resource_PVT;
1694 x_return_status := FND_API.G_RET_STS_ERROR ;
1695 FND_MSG_PUB.count_and_get
1696 (
1697 p_encoded => FND_API.G_FALSE,
1698 p_count => x_msg_count,
1699 p_data => x_msg_data
1700 );
1701
1702 -- Disable debug (if enabled)
1703 IF G_DEBUG = 'Y' THEN
1704 AHL_DEBUG_PUB.disable_debug;
1705 END IF;
1706
1707 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1708 ROLLBACK TO process_rt_oper_resource_PVT;
1709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1710 FND_MSG_PUB.count_and_get
1711 (
1712 p_encoded => FND_API.G_FALSE,
1713 p_count => x_msg_count,
1714 p_data => x_msg_data
1715 );
1716
1717 -- Disable debug (if enabled)
1718 IF G_DEBUG = 'Y' THEN
1719 AHL_DEBUG_PUB.disable_debug;
1720 END IF;
1721
1722 WHEN OTHERS THEN
1723 ROLLBACK TO process_rt_oper_resource_PVT;
1724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1725 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1726 THEN
1727 FND_MSG_PUB.add_exc_msg
1728 (
1729 p_pkg_name => G_PKG_NAME,
1730 p_procedure_name => G_API_NAME1,
1731 p_error_text => SUBSTRB(SQLERRM,1,240)
1732 );
1733 END IF;
1734 FND_MSG_PUB.count_and_get
1735 (
1736 p_encoded => FND_API.G_FALSE,
1737 p_count => x_msg_count,
1738 p_data => x_msg_data
1739 );
1740
1741 -- Disable debug (if enabled)
1742 IF G_DEBUG = 'Y' THEN
1743 AHL_DEBUG_PUB.disable_debug;
1744 END IF;
1745
1746 END process_rt_oper_resource;
1747
1748 -- The following local procedures are for another publiced API define_cost_parameter
1749 -- Procedure to perform Value to ID conversion for appropriate attributes
1750 PROCEDURE convert_costing_values_to_ids
1751 (
1752 p_x_rt_oper_cost_rec IN OUT NOCOPY rt_oper_cost_rec_type,
1753 x_return_status OUT NOCOPY VARCHAR2
1754 )
1755 IS
1756
1757 l_return_status VARCHAR2(1);
1758 l_msg_data VARCHAR2(2000);
1759
1760 BEGIN
1761 x_return_status := FND_API.G_RET_STS_SUCCESS;
1762
1763 -- Convert / Validate cost_basis_id
1764 IF ( ( p_x_rt_oper_cost_rec.cost_basis_id IS NOT NULL AND
1765 p_x_rt_oper_cost_rec.cost_basis_id <> FND_API.G_MISS_NUM ) OR
1766 ( p_x_rt_oper_cost_rec.cost_basis IS NOT NULL AND
1767 p_x_rt_oper_cost_rec.cost_basis <> FND_API.G_MISS_CHAR ) ) THEN
1768
1769 AHL_RM_ROUTE_UTIL.validate_mfg_lookup
1770 (
1771 x_return_status => l_return_status,
1772 x_msg_data => l_msg_data,
1773 p_lookup_type => 'CST_BASIS',
1774 p_lookup_meaning => p_x_rt_oper_cost_rec.cost_basis,
1775 p_x_lookup_code => p_x_rt_oper_cost_rec.cost_basis_id
1776 );
1777
1778 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1779 IF ( l_msg_data = 'AHL_RM_INVALID_MFG_LOOKUP' ) THEN
1780 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_COST_BASIS' );
1781 ELSIF ( l_msg_data = 'AHL_RM_TOO_MANY_MFG_LOOKUPS' ) THEN
1782 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_COST_BASIS' );
1783 ELSE
1784 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1785 END IF;
1786
1787 IF ( p_x_rt_oper_cost_rec.cost_basis IS NULL OR
1788 p_x_rt_oper_cost_rec.cost_basis = FND_API.G_MISS_CHAR ) THEN
1789 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_cost_rec.cost_basis_id ) );
1790 ELSE
1791 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_cost_rec.cost_basis );
1792 END IF;
1793
1794 FND_MSG_PUB.add;
1795 END IF;
1796
1797 END IF;
1798 /* activity look up obsoleted
1799 -- Convert / Validate activity_id
1800 IF ( ( p_x_rt_oper_cost_rec.activity_id IS NOT NULL AND
1801 p_x_rt_oper_cost_rec.activity_id <> FND_API.G_MISS_NUM ) OR
1802 ( p_x_rt_oper_cost_rec.activity IS NOT NULL AND
1803 p_x_rt_oper_cost_rec.activity <> FND_API.G_MISS_CHAR ) )
1804 THEN
1805
1806 AHL_RM_ROUTE_UTIL.validate_activity
1807 (
1808 x_return_status => l_return_status,
1809 x_msg_data => l_msg_data,
1810 p_activity => p_x_rt_oper_cost_rec.activity,
1811 p_x_activity_id => p_x_rt_oper_cost_rec.activity_id
1812 );
1813
1814 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1815 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1816
1817 IF ( p_x_rt_oper_cost_rec.activity IS NULL OR
1818 p_x_rt_oper_cost_rec.activity = FND_API.G_MISS_CHAR ) THEN
1819 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_cost_rec.activity_id ) );
1820 ELSE
1821 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_cost_rec.activity );
1822 END IF;
1823
1824 FND_MSG_PUB.add;
1825 END IF;
1826
1827 END IF;
1828 */
1829 -- Convert / Validate scheduled_type_id
1830 IF ( ( p_x_rt_oper_cost_rec.scheduled_type_id IS NOT NULL AND
1831 p_x_rt_oper_cost_rec.scheduled_type_id <> FND_API.G_MISS_NUM ) OR
1832 ( p_x_rt_oper_cost_rec.scheduled_type IS NOT NULL AND
1833 p_x_rt_oper_cost_rec.scheduled_type <> FND_API.G_MISS_CHAR ) )
1834 THEN
1835
1836 AHL_RM_ROUTE_UTIL.validate_mfg_lookup
1837 (
1838 x_return_status => l_return_status,
1839 x_msg_data => l_msg_data,
1840 p_lookup_type => 'BOM_RESOURCE_SCHEDULE_TYPE',
1841 p_lookup_meaning => p_x_rt_oper_cost_rec.scheduled_type,
1842 p_x_lookup_code => p_x_rt_oper_cost_rec.scheduled_type_id
1843 );
1844
1845 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1846 IF ( l_msg_data = 'AHL_RM_INVALID_MFG_LOOKUP' ) THEN
1847 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_SCHEDULE_TYPE' );
1848 ELSIF ( l_msg_data = 'AHL_RM_TOO_MANY_MFG_LOOKUPS' ) THEN
1849 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_SCHEDULE_TYPES' );
1850 ELSE
1851 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1852 END IF;
1853
1854 IF ( p_x_rt_oper_cost_rec.scheduled_type IS NULL OR
1855 p_x_rt_oper_cost_rec.scheduled_type = FND_API.G_MISS_CHAR ) THEN
1856 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_cost_rec.scheduled_type_id ) );
1857 ELSE
1858 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_cost_rec.scheduled_type );
1859 END IF;
1860
1861 FND_MSG_PUB.add;
1862 END IF;
1863
1864 END IF;
1865
1866 -- Convert / Validate autocharge_type_id
1867 IF ( ( p_x_rt_oper_cost_rec.autocharge_type_id IS NOT NULL AND
1868 p_x_rt_oper_cost_rec.autocharge_type_id <> FND_API.G_MISS_NUM ) OR
1869 ( p_x_rt_oper_cost_rec.autocharge_type IS NOT NULL AND
1870 p_x_rt_oper_cost_rec.autocharge_type <> FND_API.G_MISS_CHAR ) )
1871 THEN
1872
1873 AHL_RM_ROUTE_UTIL.validate_mfg_lookup
1874 (
1875 x_return_status => l_return_status,
1876 x_msg_data => l_msg_data,
1877 p_lookup_type => 'BOM_AUTOCHARGE_TYPE',
1878 p_lookup_meaning => p_x_rt_oper_cost_rec.autocharge_type,
1879 p_x_lookup_code => p_x_rt_oper_cost_rec.autocharge_type_id
1880 );
1881
1882 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1883 IF ( l_msg_data = 'AHL_RM_INVALID_MFG_LOOKUP' ) THEN
1884 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_ACHARGE_TYPE' );
1885 ELSIF ( l_msg_data = 'AHL_RM_TOO_MANY_MFG_LOOKUPS' ) THEN
1886 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_ACHARGE_TYPES' );
1887 ELSE
1888 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1889 END IF;
1890
1891 IF ( p_x_rt_oper_cost_rec.autocharge_type IS NULL OR
1892 p_x_rt_oper_cost_rec.autocharge_type = FND_API.G_MISS_CHAR ) THEN
1893 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_cost_rec.autocharge_type_id ) );
1894 ELSE
1895 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_cost_rec.autocharge_type );
1896 END IF;
1897
1898 FND_MSG_PUB.add;
1899 END IF;
1900
1901 END IF;
1902
1903 -- Convert / Validate standard_rate_flag
1904 IF ( ( p_x_rt_oper_cost_rec.standard_rate_flag IS NOT NULL AND
1905 p_x_rt_oper_cost_rec.standard_rate_flag <> FND_API.G_MISS_NUM ) OR
1906 ( p_x_rt_oper_cost_rec.standard_rate IS NOT NULL AND
1907 p_x_rt_oper_cost_rec.standard_rate <> FND_API.G_MISS_CHAR ) )
1908 THEN
1909
1910 AHL_RM_ROUTE_UTIL.validate_mfg_lookup
1911 (
1912 x_return_status => l_return_status,
1913 x_msg_data => l_msg_data,
1914 p_lookup_type => 'SYS_YES_NO',
1915 p_lookup_meaning => p_x_rt_oper_cost_rec.standard_rate,
1916 p_x_lookup_code => p_x_rt_oper_cost_rec.standard_rate_flag
1917 );
1918
1919 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1920 IF ( l_msg_data = 'AHL_RM_INVALID_MFG_LOOKUP' ) THEN
1921 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_STD_RATE' );
1922 ELSIF ( l_msg_data = 'AHL_RM_TOO_MANY_MFG_LOOKUPS' ) THEN
1923 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_STD_RATES' );
1924 ELSE
1925 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1926 END IF;
1927
1928 IF ( p_x_rt_oper_cost_rec.standard_rate IS NULL OR
1929 p_x_rt_oper_cost_rec.standard_rate = FND_API.G_MISS_CHAR ) THEN
1930 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_cost_rec.standard_rate_flag ) );
1931 ELSE
1932 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_cost_rec.standard_rate );
1933 END IF;
1934
1935 FND_MSG_PUB.add;
1936 END IF;
1937
1938 END IF;
1939 END convert_costing_values_to_ids;
1940
1941 -- Procedure to add Default values for unchanged attributes (UPDATE)
1942 PROCEDURE default_costing_unchanged
1943 (
1944 p_x_rt_oper_cost_rec IN OUT NOCOPY rt_oper_cost_rec_type
1945 )
1946 IS
1947
1948 l_old_rt_oper_cost_rec rt_oper_cost_rec_type;
1949
1950 CURSOR get_old_rec ( c_rt_oper_resource_id NUMBER )
1951 IS
1952 SELECT activity_id,
1953 activity,
1954 cost_basis_id,
1955 cost_basis,
1956 scheduled_type_id,
1957 scheduled_type,
1958 autocharge_type_id,
1959 autocharge_type,
1960 standard_rate_flag,
1961 standard_rate
1962 FROM AHL_RT_OPER_RESOURCES_V
1963 WHERE rt_oper_resource_id = c_rt_oper_resource_id;
1964
1965 BEGIN
1966
1967 -- Get the old record from AHL_MR_EFFECTIVITIES.
1968 OPEN get_old_rec( p_x_rt_oper_cost_rec.rt_oper_resource_id );
1969
1970 FETCH get_old_rec INTO
1971 l_old_rt_oper_cost_rec.activity_id,
1972 l_old_rt_oper_cost_rec.activity,
1973 l_old_rt_oper_cost_rec.cost_basis_id,
1974 l_old_rt_oper_cost_rec.cost_basis,
1975 l_old_rt_oper_cost_rec.scheduled_type_id,
1976 l_old_rt_oper_cost_rec.scheduled_type,
1977 l_old_rt_oper_cost_rec.autocharge_type_id,
1978 l_old_rt_oper_cost_rec.autocharge_type,
1979 l_old_rt_oper_cost_rec.standard_rate_flag,
1980 l_old_rt_oper_cost_rec.standard_rate;
1981
1982 IF get_old_rec%NOTFOUND THEN
1983 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_RES' );
1984 FND_MSG_PUB.add;
1985 CLOSE get_old_rec;
1986 RAISE FND_API.G_EXC_ERROR;
1987 END IF;
1988
1989 CLOSE get_old_rec;
1990
1991 -- Convert G_MISS values to NULL and NULL values to Old values
1992 IF ( p_x_rt_oper_cost_rec.activity_id = FND_API.G_MISS_NUM ) THEN
1993 p_x_rt_oper_cost_rec.activity_id := null;
1994 ELSIF ( p_x_rt_oper_cost_rec.activity_id IS NULL ) THEN
1995 p_x_rt_oper_cost_rec.activity_id := l_old_rt_oper_cost_rec.activity_id;
1996 END IF;
1997
1998 IF ( p_x_rt_oper_cost_rec.activity = FND_API.G_MISS_CHAR ) THEN
1999 p_x_rt_oper_cost_rec.activity := null;
2000 ELSIF ( p_x_rt_oper_cost_rec.activity IS NULL ) THEN
2001 p_x_rt_oper_cost_rec.activity := l_old_rt_oper_cost_rec.activity;
2002 END IF;
2003
2004 IF ( p_x_rt_oper_cost_rec.cost_basis_id = FND_API.G_MISS_NUM ) THEN
2005 p_x_rt_oper_cost_rec.cost_basis_id := null;
2006 ELSIF ( p_x_rt_oper_cost_rec.cost_basis_id IS NULL ) THEN
2007 p_x_rt_oper_cost_rec.cost_basis_id := l_old_rt_oper_cost_rec.cost_basis_id;
2008 END IF;
2009
2010 IF ( p_x_rt_oper_cost_rec.cost_basis = FND_API.G_MISS_CHAR ) THEN
2011 p_x_rt_oper_cost_rec.cost_basis := null;
2012 ELSIF ( p_x_rt_oper_cost_rec.cost_basis IS NULL ) THEN
2013 p_x_rt_oper_cost_rec.cost_basis := l_old_rt_oper_cost_rec.cost_basis;
2014 END IF;
2015
2016 IF ( p_x_rt_oper_cost_rec.scheduled_type_id = FND_API.G_MISS_NUM ) THEN
2017 p_x_rt_oper_cost_rec.scheduled_type_id := null;
2018 ELSIF ( p_x_rt_oper_cost_rec.scheduled_type_id IS NULL ) THEN
2019 p_x_rt_oper_cost_rec.scheduled_type_id := l_old_rt_oper_cost_rec.scheduled_type_id;
2020 END IF;
2021
2022 IF ( p_x_rt_oper_cost_rec.scheduled_type = FND_API.G_MISS_CHAR ) THEN
2023 p_x_rt_oper_cost_rec.scheduled_type := null;
2024 ELSIF ( p_x_rt_oper_cost_rec.scheduled_type IS NULL ) THEN
2025 p_x_rt_oper_cost_rec.scheduled_type := l_old_rt_oper_cost_rec.scheduled_type;
2026 END IF;
2027
2028 IF ( p_x_rt_oper_cost_rec.autocharge_type_id = FND_API.G_MISS_NUM ) THEN
2029 p_x_rt_oper_cost_rec.autocharge_type_id := null;
2030 ELSIF ( p_x_rt_oper_cost_rec.autocharge_type_id IS NULL ) THEN
2031 p_x_rt_oper_cost_rec.autocharge_type_id := l_old_rt_oper_cost_rec.autocharge_type_id;
2032 END IF;
2033
2034 IF ( p_x_rt_oper_cost_rec.autocharge_type = FND_API.G_MISS_CHAR ) THEN
2035 p_x_rt_oper_cost_rec.autocharge_type := null;
2036 ELSIF ( p_x_rt_oper_cost_rec.autocharge_type IS NULL ) THEN
2037 p_x_rt_oper_cost_rec.autocharge_type := l_old_rt_oper_cost_rec.autocharge_type;
2038 END IF;
2039
2040 IF ( p_x_rt_oper_cost_rec.standard_rate_flag = FND_API.G_MISS_NUM ) THEN
2041 p_x_rt_oper_cost_rec.standard_rate_flag := null;
2042 ELSIF ( p_x_rt_oper_cost_rec.standard_rate_flag IS NULL ) THEN
2043 p_x_rt_oper_cost_rec.standard_rate_flag := l_old_rt_oper_cost_rec.standard_rate_flag;
2044 END IF;
2045
2046 IF ( p_x_rt_oper_cost_rec.standard_rate = FND_API.G_MISS_CHAR ) THEN
2047 p_x_rt_oper_cost_rec.standard_rate := null;
2048 ELSIF ( p_x_rt_oper_cost_rec.standard_rate IS NULL ) THEN
2049 p_x_rt_oper_cost_rec.standard_rate := l_old_rt_oper_cost_rec.standard_rate;
2050 END IF;
2051
2052 END default_costing_unchanged;
2053
2054 PROCEDURE define_cost_parameter
2055 (
2056 p_api_version IN NUMBER := 1.0,
2057 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2058 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2059 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2060 p_default IN VARCHAR2 := FND_API.G_FALSE,
2061 p_module_type IN VARCHAR2 := NULL,
2062 x_return_status OUT NOCOPY VARCHAR2,
2063 x_msg_count OUT NOCOPY NUMBER,
2064 x_msg_data OUT NOCOPY VARCHAR2,
2065 p_x_rt_oper_cost_rec IN OUT NOCOPY rt_oper_cost_rec_type
2066 ) IS
2067
2068
2069
2070 -- added AR.resource_type_id and duration to fix bug# 6512803.
2071 CURSOR get_object_rec(C_RT_OPER_RESOURCE_ID NUMBER)
2072 IS
2073 SELECT --DISTINCT
2074 RES.OBJECT_ID,
2075 RES.ASSOCIATION_TYPE_CODE,
2076 AR.resource_type_id,
2077 RES.duration,
2078 AR.NAME
2079 FROM AHL_RT_OPER_RESOURCES RES, AHL_RESOURCES AR
2080 WHERE RES.aso_resource_id = AR.resource_id
2081 AND RES.RT_OPER_RESOURCE_ID = C_RT_OPER_RESOURCE_ID;
2082
2083 cursor get_route_status (p_route_id in number)
2084 is
2085 select revision_status_code
2086 from ahl_routes_app_v
2087 where route_id = p_route_id;
2088
2089 l_obj_status VARCHAR2(30);
2090
2091 cursor get_oper_status (p_operation_id in number)
2092 is
2093 select revision_status_code
2094 from ahl_operations_b
2095 where operation_id = p_operation_id;
2096
2097 l_api_version CONSTANT NUMBER := 1.0;
2098 l_api_name CONSTANT VARCHAR2(30) := 'DEFINE_COST_PARAMETER';
2099 l_return_status VARCHAR2(1);
2100 l_msg_count NUMBER;
2101 l_msg_data VARCHAR2(2000);
2102 l_object_id NUMBER;
2103 l_association_type_code VARCHAR2(30);
2104 l_x_operation_rec AHL_RM_OPERATION_PVT.operation_rec_type ;
2105 l_x_route_rec AHL_RM_ROUTE_PVT.route_rec_type ;
2106
2107 -- Added for bug fix# 6512803.
2108 l_resource_type_id NUMBER;
2109 l_max_rt_time_span NUMBER;
2110 l_duration NUMBER;
2111 l_name ahl_resources.name%TYPE;
2112
2113 BEGIN
2114 -- Initialize API return status to success
2115 x_return_status := FND_API.G_RET_STS_SUCCESS;
2116
2117 -- Standard Start of API savepoint
2118 SAVEPOINT define_cost_parameter_pvt;
2119
2120 -- Standard call to check for call compatibility.
2121 IF NOT FND_API.compatible_api_call
2122 (
2123 l_api_version,
2124 p_api_version,
2125 G_API_NAME2,
2126 G_PKG_NAME
2127 )
2128 THEN
2129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2130 END IF;
2131
2132 -- Initialize message list if p_init_msg_list is set to TRUE.
2133 IF FND_API.to_boolean( p_init_msg_list ) THEN
2134 FND_MSG_PUB.initialize;
2135 END IF;
2136
2137 IF G_DEBUG = 'Y' THEN
2138 AHL_DEBUG_PUB.enable_debug;
2139 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.'||l_api_name||': Begin API' );
2140 END IF;
2141
2142
2143 -- Convert Values into Ids.
2144 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
2145 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
2146 convert_costing_values_to_ids
2147 (
2148 p_x_rt_oper_cost_rec , -- IN OUT Record with Values and Ids
2149 l_return_status -- OUT
2150 );
2151
2152 -- If any severe error occurs, then, abort API.
2153 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2154 RAISE FND_API.G_EXC_ERROR;
2155 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2157 END IF;
2158 --END IF;
2159
2160 IF G_DEBUG = 'Y' THEN
2161 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after convert_costing_values_to_ids' );
2162 END IF;
2163
2164 OPEN get_object_rec ( p_x_rt_oper_cost_rec.RT_OPER_RESOURCE_ID ) ;
2165 FETCH get_object_rec INTO
2166 l_object_id ,
2167 l_association_type_code ,
2168 l_resource_type_id , -- added for 6512803.
2169 l_duration ,
2170 l_name ;
2171 IF get_object_rec%NOTFOUND THEN
2172 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_OBJECT' );
2173 FND_MSG_PUB.add;
2174 RAISE FND_API.G_EXC_ERROR;
2175 END IF;
2176 CLOSE get_object_rec;
2177
2178 -- Fix for bug# 6512803. Schedule flag can be set to Yes(1) only for person and
2179 -- machine resources.
2180
2181 --Bug 6625880. AMSRINIV. Doing away with below validation as misc resources can be scheduled.
2182
2183 IF (p_x_rt_oper_cost_rec.scheduled_type_id IS NOT NULL) AND
2184 (p_x_rt_oper_cost_rec.scheduled_type_id <> FND_API.G_MISS_NUM) THEN
2185 /*
2186 IF (p_x_rt_oper_cost_rec.scheduled_type_id = 1) AND (l_resource_type_id NOT IN (1,2)) THEN
2187 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_RES_SCHEDULE_TY' );
2188 IF ( p_x_rt_oper_cost_rec.scheduled_type IS NULL OR
2189 p_x_rt_oper_cost_rec.scheduled_type = FND_API.G_MISS_CHAR ) THEN
2190 SELECT meaning
2191 INTO p_x_rt_oper_cost_rec.scheduled_type
2192 FROM fnd_lookup_values_vl
2193 WHERE lookup_type = 'BOM_RESOURCE_SCHEDULE_TYPE'
2194 AND lookup_code = p_x_rt_oper_cost_rec.scheduled_type_id;
2195
2196 END IF;
2197 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_cost_rec.scheduled_type );
2198 FND_MSG_PUB.add;
2199 RAISE FND_API.G_EXC_ERROR;
2200 END IF; -- p_x_rt_oper_cost_rec.scheduled_type_id = 1
2201 */
2202 -- validate time span based on scheduled_type_id.
2203 IF (p_x_rt_oper_cost_rec.scheduled_type_id = 1 AND l_resource_type_id IN (1,2)) THEN
2204 AHL_RM_ROUTE_UTIL.validate_resource_duration
2205 (
2206 x_return_status => l_return_status,
2207 x_msg_data => l_msg_data,
2208 p_object_id => l_object_id,
2209 p_association_type_code=> l_association_type_code,
2210 p_duration => l_duration,
2211 x_max_rt_time_span => l_max_rt_time_span
2212 );
2213
2214 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
2215 FND_MESSAGE.set_name( 'AHL', l_msg_data );
2216 FND_MESSAGE.set_token( 'FIELD1', l_duration );
2217 FND_MESSAGE.set_token( 'FIELD2', l_max_rt_time_span );
2218 FND_MESSAGE.set_token( 'RECORD', l_name);
2219 FND_MSG_PUB.add;
2220 --dbms_output.put_line('Cost: validate_resource_duration error');
2221 END IF;
2222 END IF;
2223
2224 ELSIF (p_x_rt_oper_cost_rec.scheduled_type_id = FND_API.G_MISS_NUM) THEN
2225 -- default here to avoid query on ahl_resources.
2226 IF (l_resource_type_id IN (1,2)) THEN
2227 p_x_rt_oper_cost_rec.scheduled_type_id := 1;
2228 ELSE
2229 p_x_rt_oper_cost_rec.scheduled_type_id := 2;
2230 END IF;
2231
2232 END IF; -- p_x_rt_oper_cost_rec.scheduled_type_id IS NOT NULL
2233
2234 -- moved this procedure after validation on Schedule flag.
2235 -- Default missing and unchanged attributes.
2236 default_costing_unchanged
2237 (
2238 p_x_rt_oper_cost_rec -- IN OUT
2239 );
2240
2241 IF G_DEBUG = 'Y' THEN
2242 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after default_costing_unchanged' );
2243 END IF;
2244
2245 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2246 l_msg_count := FND_MSG_PUB.count_msg;
2247 IF l_msg_count > 0 THEN
2248 x_msg_count := l_msg_count;
2249 RAISE FND_API.G_EXC_ERROR;
2250 END IF;
2251
2252 /*
2253 -- to get the association object type code and the object id
2254 OPEN get_object_rec ( p_x_rt_oper_cost_rec.RT_OPER_RESOURCE_ID ) ;
2255 FETCH get_object_rec INTO
2256 l_object_id ,
2257 l_association_type_code ;
2258 IF get_object_rec%NOTFOUND THEN
2259 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_OBJECT' );
2260 FND_MSG_PUB.add;
2261 RAISE FND_API.G_EXC_ERROR;
2262 END IF;
2263 CLOSE get_object_rec;
2264 */
2265
2266
2267 --to change the status of Approval rejected Routes/perations to Draft if costing parameters are updated.
2268 IF ( l_association_type_code = 'OPERATION')
2269 THEN
2270 IF G_DEBUG='Y' THEN
2271 AHL_DEBUG_PUB.debug( 'l_association_type_code = OPERATION');
2272 END IF;
2273
2274 -- Check if the Route is existing and in Draft status
2275 AHL_RM_ROUTE_UTIL.validate_operation_status
2276 (
2277 l_object_id,
2278 l_msg_data,
2279 l_return_status
2280 );
2281
2282 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2283 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
2284 FND_MSG_PUB.ADD;
2285 x_return_status := l_return_status;
2286 RETURN;
2287 END IF;
2288
2289 -- Update route status from APPROVAL_REJECTED to DRAFT
2290 OPEN get_oper_status (l_object_id);
2291 FETCH get_oper_status INTO l_obj_status;
2292 IF (get_oper_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
2293 THEN
2294 UPDATE ahl_operations_b
2295 SET revision_status_code = 'DRAFT'
2296 WHERE operation_id = l_object_id;
2297 END IF;
2298 CLOSE get_oper_status;
2299
2300
2301 ELSIF ( l_association_type_code = 'ROUTE')
2302 THEN
2303 IF G_DEBUG='Y' THEN
2304 AHL_DEBUG_PUB.debug( 'l_association_type_code = ROUTE');
2305 END IF;
2306
2307 AHL_RM_ROUTE_UTIL.validate_ApplnUsage
2308 (
2309 p_object_id => l_object_id,
2310 p_association_type => l_association_type_code ,
2311 x_return_status => x_return_status,
2312 x_msg_data => x_msg_data
2313 );
2314
2315 -- If any severe error occurs, then, abort API.
2316 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2317 RAISE FND_API.G_EXC_ERROR;
2318 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2320 END IF ;
2321
2322 -- Check if the Route is existing and in Draft status
2323 AHL_RM_ROUTE_UTIL.validate_route_status
2324 (
2325 l_object_id,
2326 l_msg_data,
2327 l_return_status
2328 );
2329
2330 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2331 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
2332 FND_MSG_PUB.ADD;
2333 x_return_status := l_return_status;
2334 RETURN;
2335 END IF;
2336
2337 -- Update route status from APPROVAL_REJECTED to DRAFT
2338 OPEN get_route_status (l_object_id);
2339 FETCH get_route_status INTO l_obj_status;
2340 IF (get_route_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
2341 THEN
2342 UPDATE ahl_routes_b
2343 SET revision_status_code = 'DRAFT'
2344 WHERE route_id = l_object_id;
2345 END IF;
2346 CLOSE get_route_status;
2347
2348 END IF ;
2349
2350 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2351 l_msg_count := FND_MSG_PUB.count_msg;
2352 IF l_msg_count > 0 THEN
2353 x_msg_count := l_msg_count;
2354 RAISE FND_API.G_EXC_ERROR;
2355 END IF;
2356
2357 -- Update the cost parameters
2358 UPDATE AHL_RT_OPER_RESOURCES SET
2359 object_version_number = object_version_number + 1,
2360 activity_id = p_x_rt_oper_cost_rec.activity_id,
2361 cost_basis_id = p_x_rt_oper_cost_rec.cost_basis_id,
2362 scheduled_type_id = p_x_rt_oper_cost_rec.scheduled_type_id,
2363 autocharge_type_id = p_x_rt_oper_cost_rec.autocharge_type_id,
2364 standard_rate_flag = p_x_rt_oper_cost_rec.standard_rate_flag,
2365 last_update_date = SYSDATE,
2366 last_updated_by = FND_GLOBAL.user_id,
2367 last_update_login = FND_GLOBAL.login_id
2368 WHERE rt_oper_resource_id = p_x_rt_oper_cost_rec.rt_oper_resource_id
2369 AND object_version_number = p_x_rt_oper_cost_rec.object_version_number;
2370
2371 -- If the record does not exist, then, abort API.
2372 IF ( SQL%ROWCOUNT = 0 ) THEN
2373 FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_CHANGED');
2374 FND_MSG_PUB.add;
2375 END IF;
2376
2377 -- Set OUT values
2378 p_x_rt_oper_cost_rec.object_version_number := p_x_rt_oper_cost_rec.object_version_number + 1;
2379
2380 IF G_DEBUG = 'Y' THEN
2381 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after DML operation' );
2382 END IF;
2383
2384 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2385 l_msg_count := FND_MSG_PUB.count_msg;
2386 IF l_msg_count > 0 THEN
2387 x_msg_count := l_msg_count;
2388 RAISE FND_API.G_EXC_ERROR;
2389 END IF;
2390
2391 -- Perform the Commit (if requested)
2392 IF FND_API.to_boolean( p_commit ) THEN
2393 COMMIT WORK;
2394 END IF;
2395
2396 -- Count and Get messages (optional)
2397 FND_MSG_PUB.count_and_get
2398 (
2399 p_encoded => FND_API.G_FALSE,
2400 p_count => x_msg_count,
2401 p_data => x_msg_data
2402 );
2403
2404 -- Disable debug (if enabled)
2405 IF G_DEBUG = 'Y' THEN
2406 AHL_DEBUG_PUB.disable_debug;
2407 END IF;
2408
2409 EXCEPTION
2410 WHEN FND_API.G_EXC_ERROR THEN
2411 ROLLBACK TO define_cost_parameter_pvt;
2412 x_return_status := FND_API.G_RET_STS_ERROR ;
2413 FND_MSG_PUB.count_and_get
2414 (
2415 p_encoded => FND_API.G_FALSE,
2416 p_count => x_msg_count,
2417 p_data => x_msg_data
2418 );
2419
2420 -- Disable debug (if enabled)
2421 IF G_DEBUG = 'Y' THEN
2422 AHL_DEBUG_PUB.disable_debug;
2423 END IF;
2424
2425 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2426 ROLLBACK TO define_cost_parameter_pvt;
2427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2428 FND_MSG_PUB.count_and_get
2429 (
2430 p_encoded => FND_API.G_FALSE,
2431 p_count => x_msg_count,
2432 p_data => x_msg_data
2433 );
2434
2435 -- Disable debug (if enabled)
2436 IF G_DEBUG = 'Y' THEN
2437 AHL_DEBUG_PUB.disable_debug;
2438 END IF;
2439
2440 WHEN OTHERS THEN
2441 ROLLBACK TO define_cost_parameter_pvt;
2442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2443 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2444 THEN
2445 FND_MSG_PUB.add_exc_msg
2446 (
2447 p_pkg_name => G_PKG_NAME,
2448 p_procedure_name => G_API_NAME2,
2449 p_error_text => SUBSTRB(SQLERRM,1,240)
2450 );
2451 END IF;
2452 FND_MSG_PUB.count_and_get
2453 (
2454 p_encoded => FND_API.G_FALSE,
2455 p_count => x_msg_count,
2456 p_data => x_msg_data
2457 );
2458
2459 -- Disable debug (if enabled)
2460 IF G_DEBUG = 'Y' THEN
2461 AHL_DEBUG_PUB.disable_debug;
2462 END IF;
2463
2464 END define_cost_parameter;
2465
2466 -- Procedure to validate the all the inputs except the table structure of the API
2467 PROCEDURE validate_alt_api_inputs
2468 (
2469 p_rt_oper_resource_id IN NUMBER,
2470 p_alt_resource_tbl IN alt_resource_tbl_type,
2471 x_return_status OUT NOCOPY VARCHAR2
2472 )
2473 IS
2474
2475 l_return_status VARCHAR2(1);
2476 l_msg_data VARCHAR2(2000);
2477
2478 BEGIN
2479 x_return_status := FND_API.G_RET_STS_SUCCESS;
2480
2481 -- Check if a valid value is passed in p_rt_oper_resource_id
2482 IF ( p_rt_oper_resource_id = FND_API.G_MISS_NUM OR
2483 p_rt_oper_resource_id IS NULL ) THEN
2484 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RT_OPER_RES_NULL' );
2485 FND_MSG_PUB.add;
2486 x_return_status := FND_API.G_RET_STS_ERROR;
2487 RAISE FND_API.G_EXC_ERROR;
2488 END IF;
2489
2490 -- Check if at least one record is passed in p_rt_oper_resource_tbl
2491 IF ( p_alt_resource_tbl.count < 1 ) THEN
2492 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
2493 FND_MESSAGE.set_token( 'PROCEDURE', G_PKG_NAME || '.' || G_API_NAME3 );
2494 FND_MSG_PUB.add;
2495 x_return_status := FND_API.G_RET_STS_ERROR;
2496 RAISE FND_API.G_EXC_ERROR;
2497 END IF;
2498
2499 -- Validate DML Operation
2500 FOR i IN 1..p_alt_resource_tbl.count LOOP
2501 IF ( p_alt_resource_tbl(i).dml_operation IS NULL OR
2502 (
2503 p_alt_resource_tbl(i).dml_operation <> 'C' AND
2504 p_alt_resource_tbl(i).dml_operation <> 'U' AND
2505 p_alt_resource_tbl(i).dml_operation <> 'D'
2506 )
2507 )
2508 THEN
2509 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML' );
2510 FND_MESSAGE.set_token( 'FIELD', p_alt_resource_tbl(i).dml_operation );
2511 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_tbl(i).aso_resource_name );
2512 FND_MSG_PUB.add;
2513 x_return_status := FND_API.G_RET_STS_ERROR;
2514 RAISE FND_API.G_EXC_ERROR;
2515 END IF;
2516 END LOOP;
2517
2518 END validate_alt_api_inputs;
2519
2520 -- Procedure to Default NULL / G_MISS Values for LOV attributes
2521 PROCEDURE clear_alt_lov_attribute_ids
2522 (
2523 p_x_alt_resource_rec IN OUT NOCOPY alt_resource_rec_type
2524 )
2525 IS
2526
2527 BEGIN
2528 IF ( p_x_alt_resource_rec.aso_resource_name IS NULL ) THEN
2529 p_x_alt_resource_rec.aso_resource_id := NULL;
2530 ELSIF ( p_x_alt_resource_rec.aso_resource_name = FND_API.G_MISS_CHAR ) THEN
2531 p_x_alt_resource_rec.aso_resource_id := FND_API.G_MISS_NUM;
2532 END IF;
2533 END clear_alt_lov_attribute_ids;
2534
2535 -- Procedure to perform Value to ID conversion and validation for LOV attributes
2536 PROCEDURE convert_alt_values_to_ids
2537 (
2538 p_x_alt_resource_rec IN OUT NOCOPY alt_resource_rec_type,
2539 x_return_status OUT NOCOPY VARCHAR2
2540 )
2541 IS
2542
2543 l_return_status VARCHAR2(1);
2544 l_msg_data VARCHAR2(2000);
2545
2546 BEGIN
2547 x_return_status := FND_API.G_RET_STS_SUCCESS;
2548
2549 -- Convert / Validate ASO_resource_id
2550 IF ( ( p_x_alt_resource_rec.aso_resource_id IS NOT NULL AND
2551 p_x_alt_resource_rec.aso_resource_id <> FND_API.G_MISS_NUM ) OR
2552 ( p_x_alt_resource_rec.aso_resource_name IS NOT NULL AND
2553 p_x_alt_resource_rec.aso_resource_name <> FND_API.G_MISS_CHAR ) )
2554 THEN
2555
2556 AHL_RM_ROUTE_UTIL.validate_aso_resource
2557 (
2558 x_return_status => l_return_status,
2559 x_msg_data => l_msg_data,
2560 p_aso_resource_name => p_x_alt_resource_rec.aso_resource_name,
2561 p_x_aso_resource_id => p_x_alt_resource_rec.aso_resource_id
2562 );
2563
2564 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2565 FND_MESSAGE.set_name( 'AHL', l_msg_data );
2566
2567 IF ( p_x_alt_resource_rec.aso_resource_name IS NULL OR
2568 p_x_alt_resource_rec.aso_resource_name = FND_API.G_MISS_CHAR ) THEN
2569 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_alt_resource_rec.aso_resource_id ) );
2570 ELSE
2571 FND_MESSAGE.set_token( 'FIELD', p_x_alt_resource_rec.aso_resource_name );
2572 END IF;
2573
2574 FND_MESSAGE.set_token( 'RECORD', p_x_alt_resource_rec.aso_resource_name );
2575 FND_MSG_PUB.add;
2576 END IF;
2577 x_return_status := l_return_status;
2578 END IF;
2579
2580 END convert_alt_values_to_ids;
2581
2582 /* Removing as a part of Public API cleanup in 11510+.
2583 -- Procedure to add Default values for rt_oper_resource attributes
2584 PROCEDURE default_alt_attributes
2585 (
2586 p_x_alt_resource_rec IN OUT NOCOPY alt_resource_rec_type
2587 )
2588 IS
2589
2590 BEGIN
2591
2592 p_x_alt_resource_rec.last_update_date := SYSDATE;
2593 p_x_alt_resource_rec.last_updated_by := FND_GLOBAL.user_id;
2594 p_x_alt_resource_rec.last_update_login := FND_GLOBAL.login_id;
2595
2596 IF ( p_x_alt_resource_rec.dml_operation = 'C' ) THEN
2597 p_x_alt_resource_rec.object_version_number := 1;
2598 p_x_alt_resource_rec.creation_date := SYSDATE;
2599 p_x_alt_resource_rec.created_by := FND_GLOBAL.user_id;
2600 END IF;
2601
2602 END default_alt_attributes;
2603 */
2604 -- Procedure to add Default values for missing attributes (CREATE)
2605 PROCEDURE default_alt_miss_attributes
2606 (
2607 p_x_alt_resource_rec IN OUT NOCOPY alt_resource_rec_type
2608 )
2609 IS
2610
2611 BEGIN
2612
2613 -- Convert G_MISS values to NULL
2614
2615 IF ( p_x_alt_resource_rec.aso_resource_id = FND_API.G_MISS_NUM ) THEN
2616 p_x_alt_resource_rec.aso_resource_id := null;
2617 END IF;
2618
2619 IF ( p_x_alt_resource_rec.aso_resource_name = FND_API.G_MISS_CHAR ) THEN
2620 p_x_alt_resource_rec.aso_resource_name := null;
2621 END IF;
2622
2623 IF ( p_x_alt_resource_rec.priority = FND_API.G_MISS_NUM ) THEN
2624 p_x_alt_resource_rec.priority := null;
2625 END IF;
2626
2627 IF ( p_x_alt_resource_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
2628 p_x_alt_resource_rec.attribute_category := null;
2629 END IF;
2630
2631 IF ( p_x_alt_resource_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
2632 p_x_alt_resource_rec.attribute1 := null;
2633 END IF;
2634
2635 IF ( p_x_alt_resource_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
2636 p_x_alt_resource_rec.attribute2 := null;
2637 END IF;
2638
2639 IF ( p_x_alt_resource_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
2640 p_x_alt_resource_rec.attribute3 := null;
2641 END IF;
2642
2643 IF ( p_x_alt_resource_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
2644 p_x_alt_resource_rec.attribute4 := null;
2645 END IF;
2646
2647 IF ( p_x_alt_resource_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
2648 p_x_alt_resource_rec.attribute5 := null;
2649 END IF;
2650
2651 IF ( p_x_alt_resource_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
2652 p_x_alt_resource_rec.attribute6 := null;
2653 END IF;
2654
2655 IF ( p_x_alt_resource_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
2656 p_x_alt_resource_rec.attribute7 := null;
2657 END IF;
2658
2659 IF ( p_x_alt_resource_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
2660 p_x_alt_resource_rec.attribute8 := null;
2661 END IF;
2662
2663 IF ( p_x_alt_resource_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
2664 p_x_alt_resource_rec.attribute9 := null;
2665 END IF;
2666
2667 IF ( p_x_alt_resource_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
2668 p_x_alt_resource_rec.attribute10 := null;
2669 END IF;
2670
2671 IF ( p_x_alt_resource_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
2672 p_x_alt_resource_rec.attribute11 := null;
2673 END IF;
2674
2675 IF ( p_x_alt_resource_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
2676 p_x_alt_resource_rec.attribute12 := null;
2677 END IF;
2678
2679 IF ( p_x_alt_resource_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
2680 p_x_alt_resource_rec.attribute13 := null;
2681 END IF;
2682
2683 IF ( p_x_alt_resource_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
2684 p_x_alt_resource_rec.attribute14 := null;
2685 END IF;
2686
2687 IF ( p_x_alt_resource_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
2688 p_x_alt_resource_rec.attribute15 := null;
2689 END IF;
2690
2691 END default_alt_miss_attributes;
2692
2693 -- Procedure to add Default values for unchanged attributes (UPDATE)
2694 PROCEDURE default_alt_unchang_attributes
2695 (
2696 p_x_alt_resource_rec IN OUT NOCOPY alt_resource_rec_type
2697 )
2698 IS
2699
2700 l_old_alt_resource_rec alt_resource_rec_type;
2701
2702 CURSOR get_old_rec ( c_alt_resource_id NUMBER )
2703 IS
2704 SELECT alternate_resource_id,
2705 aso_resource_id,
2706 priority,
2707 attribute_category,
2708 attribute1,
2709 attribute2,
2710 attribute3,
2711 attribute4,
2712 attribute5,
2713 attribute6,
2714 attribute7,
2715 attribute8,
2716 attribute9,
2717 attribute10,
2718 attribute11,
2719 attribute12,
2720 attribute13,
2721 attribute14,
2722 attribute15
2723 FROM AHL_ALTERNATE_RESOURCES
2724 WHERE alternate_resource_id = c_alt_resource_id;
2725
2726 BEGIN
2727
2728 -- Get the old record from AHL_alt_RESOURCES.
2729 OPEN get_old_rec( p_x_alt_resource_rec.alternate_resource_id );
2730
2731 FETCH get_old_rec INTO
2732 l_old_alt_resource_rec.alternate_resource_id,
2733 l_old_alt_resource_rec.aso_resource_id,
2734 l_old_alt_resource_rec.priority,
2735 l_old_alt_resource_rec.attribute_category,
2736 l_old_alt_resource_rec.attribute1,
2737 l_old_alt_resource_rec.attribute2,
2738 l_old_alt_resource_rec.attribute3,
2739 l_old_alt_resource_rec.attribute4,
2740 l_old_alt_resource_rec.attribute5,
2741 l_old_alt_resource_rec.attribute6,
2742 l_old_alt_resource_rec.attribute7,
2743 l_old_alt_resource_rec.attribute8,
2744 l_old_alt_resource_rec.attribute9,
2745 l_old_alt_resource_rec.attribute10,
2746 l_old_alt_resource_rec.attribute11,
2747 l_old_alt_resource_rec.attribute12,
2748 l_old_alt_resource_rec.attribute13,
2749 l_old_alt_resource_rec.attribute14,
2750 l_old_alt_resource_rec.attribute15;
2751
2752 IF get_old_rec%NOTFOUND THEN
2753 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_ALT_RES_REC' );
2754 FND_MESSAGE.set_token( 'RECORD', p_x_alt_resource_rec.aso_resource_name );
2755 FND_MSG_PUB.add;
2756 CLOSE get_old_rec;
2757 RAISE FND_API.G_EXC_ERROR;
2758 END IF;
2759
2760 CLOSE get_old_rec;
2761
2762 -- Convert G_MISS values to NULL and NULL values to Old values
2763
2764 IF ( p_x_alt_resource_rec.aso_resource_id = FND_API.G_MISS_NUM ) THEN
2765 p_x_alt_resource_rec.aso_resource_id := null;
2766 ELSIF ( p_x_alt_resource_rec.aso_resource_id IS NULL ) THEN
2767 p_x_alt_resource_rec.aso_resource_id := l_old_alt_resource_rec.aso_resource_id;
2768 END IF;
2769
2770 IF ( p_x_alt_resource_rec.aso_resource_name = FND_API.G_MISS_CHAR ) THEN
2771 p_x_alt_resource_rec.aso_resource_name := null;
2772 ELSIF ( p_x_alt_resource_rec.aso_resource_name IS NULL ) THEN
2773 p_x_alt_resource_rec.aso_resource_name := l_old_alt_resource_rec.aso_resource_name;
2774 END IF;
2775
2776 IF ( p_x_alt_resource_rec.priority = FND_API.G_MISS_NUM ) THEN
2777 p_x_alt_resource_rec.priority := null;
2778 ELSIF ( p_x_alt_resource_rec.priority IS NULL ) THEN
2779 p_x_alt_resource_rec.priority := l_old_alt_resource_rec.priority;
2780 END IF;
2781
2782 IF ( p_x_alt_resource_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
2783 p_x_alt_resource_rec.attribute_category := null;
2784 ELSIF ( p_x_alt_resource_rec.attribute_category IS NULL ) THEN
2785 p_x_alt_resource_rec.attribute_category := l_old_alt_resource_rec.attribute_category;
2786 END IF;
2787
2788 IF ( p_x_alt_resource_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
2789 p_x_alt_resource_rec.attribute1 := null;
2790 ELSIF ( p_x_alt_resource_rec.attribute1 IS NULL ) THEN
2791 p_x_alt_resource_rec.attribute1 := l_old_alt_resource_rec.attribute1;
2792 END IF;
2793
2794 IF ( p_x_alt_resource_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
2795 p_x_alt_resource_rec.attribute2 := null;
2796 ELSIF ( p_x_alt_resource_rec.attribute2 IS NULL ) THEN
2797 p_x_alt_resource_rec.attribute2 := l_old_alt_resource_rec.attribute2;
2798 END IF;
2799
2800 IF ( p_x_alt_resource_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
2801 p_x_alt_resource_rec.attribute3 := null;
2802 ELSIF ( p_x_alt_resource_rec.attribute3 IS NULL ) THEN
2803 p_x_alt_resource_rec.attribute3 := l_old_alt_resource_rec.attribute3;
2804 END IF;
2805
2806 IF ( p_x_alt_resource_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
2807 p_x_alt_resource_rec.attribute4 := null;
2808 ELSIF ( p_x_alt_resource_rec.attribute4 IS NULL ) THEN
2809 p_x_alt_resource_rec.attribute4 := l_old_alt_resource_rec.attribute4;
2810 END IF;
2811
2812 IF ( p_x_alt_resource_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
2813 p_x_alt_resource_rec.attribute5 := null;
2814 ELSIF ( p_x_alt_resource_rec.attribute5 IS NULL ) THEN
2815 p_x_alt_resource_rec.attribute5 := l_old_alt_resource_rec.attribute5;
2816 END IF;
2817
2818 IF ( p_x_alt_resource_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
2819 p_x_alt_resource_rec.attribute6 := null;
2820 ELSIF ( p_x_alt_resource_rec.attribute6 IS NULL ) THEN
2821 p_x_alt_resource_rec.attribute6 := l_old_alt_resource_rec.attribute6;
2822 END IF;
2823
2824 IF ( p_x_alt_resource_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
2825 p_x_alt_resource_rec.attribute7 := null;
2826 ELSIF ( p_x_alt_resource_rec.attribute7 IS NULL ) THEN
2827 p_x_alt_resource_rec.attribute7 := l_old_alt_resource_rec.attribute7;
2828 END IF;
2829
2830 IF ( p_x_alt_resource_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
2831 p_x_alt_resource_rec.attribute8 := null;
2832 ELSIF ( p_x_alt_resource_rec.attribute8 IS NULL ) THEN
2833 p_x_alt_resource_rec.attribute8 := l_old_alt_resource_rec.attribute8;
2834 END IF;
2835
2836 IF ( p_x_alt_resource_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
2837 p_x_alt_resource_rec.attribute9 := null;
2838 ELSIF ( p_x_alt_resource_rec.attribute9 IS NULL ) THEN
2839 p_x_alt_resource_rec.attribute9 := l_old_alt_resource_rec.attribute9;
2840 END IF;
2841
2842 IF ( p_x_alt_resource_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
2843 p_x_alt_resource_rec.attribute10 := null;
2844 ELSIF ( p_x_alt_resource_rec.attribute10 IS NULL ) THEN
2845 p_x_alt_resource_rec.attribute10 := l_old_alt_resource_rec.attribute10;
2846 END IF;
2847
2848 IF ( p_x_alt_resource_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
2849 p_x_alt_resource_rec.attribute11 := null;
2850 ELSIF ( p_x_alt_resource_rec.attribute11 IS NULL ) THEN
2851 p_x_alt_resource_rec.attribute11 := l_old_alt_resource_rec.attribute11;
2852 END IF;
2853
2854 IF ( p_x_alt_resource_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
2855 p_x_alt_resource_rec.attribute12 := null;
2856 ELSIF ( p_x_alt_resource_rec.attribute12 IS NULL ) THEN
2857 p_x_alt_resource_rec.attribute12 := l_old_alt_resource_rec.attribute12;
2858 END IF;
2859
2860 IF ( p_x_alt_resource_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
2861 p_x_alt_resource_rec.attribute13 := null;
2862 ELSIF ( p_x_alt_resource_rec.attribute13 IS NULL ) THEN
2863 p_x_alt_resource_rec.attribute13 := l_old_alt_resource_rec.attribute13;
2864 END IF;
2865
2866 IF ( p_x_alt_resource_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
2867 p_x_alt_resource_rec.attribute14 := null;
2868 ELSIF ( p_x_alt_resource_rec.attribute14 IS NULL ) THEN
2869 p_x_alt_resource_rec.attribute14 := l_old_alt_resource_rec.attribute14;
2870 END IF;
2871
2872 IF ( p_x_alt_resource_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
2873 p_x_alt_resource_rec.attribute15 := null;
2874 ELSIF ( p_x_alt_resource_rec.attribute15 IS NULL ) THEN
2875 p_x_alt_resource_rec.attribute15 := l_old_alt_resource_rec.attribute15;
2876 END IF;
2877
2878 END default_alt_unchang_attributes;
2879
2880 -- Procedure to validate individual rt_oper_resource attributes
2881 PROCEDURE validate_alt_attributes
2882 (
2883 p_alt_resource_rec IN alt_resource_rec_type,
2884 x_return_status OUT NOCOPY VARCHAR2
2885 )
2886 IS
2887
2888 l_return_status VARCHAR2(1);
2889 l_msg_data VARCHAR2(2000);
2890 l_max_rt_time_span NUMBER;
2891 l_dummy NUMBER;
2892
2893
2894 BEGIN
2895 x_return_status := FND_API.G_RET_STS_SUCCESS;
2896
2897 -- Check if the mandatory quantity column contains a positive value.
2898 IF ( ( p_alt_resource_rec.dml_operation = 'C' AND
2899 p_alt_resource_rec.priority IS NULL ) OR
2900 ( p_alt_resource_rec.dml_operation <> 'D' AND
2901 p_alt_resource_rec.priority = FND_API.G_MISS_NUM ) ) THEN
2902 FND_MESSAGE.set_name( 'AHL','AHL_RM_PRIORITY_NULL' );
2903 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
2904 FND_MSG_PUB.add;
2905 ELSIF ( p_alt_resource_rec.dml_operation <> 'D' AND
2906 p_alt_resource_rec.priority <= 0 ) THEN
2907 FND_MESSAGE.set_name( 'AHL','AHL_RM_PRIORITY_LESS_ZERO' );
2908 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
2909 FND_MSG_PUB.add;
2910 ELSIF ( p_alt_resource_rec.dml_operation <> 'D' AND
2911 p_alt_resource_rec.priority > 0 ) THEN
2912 BEGIN
2913 l_dummy := TO_NUMBER(TO_CHAR(p_alt_resource_rec.priority), '999999');
2914 EXCEPTION
2915 WHEN OTHERS THEN
2916 FND_MESSAGE.set_name( 'AHL','AHL_RM_PRIORITY_NOT_INTEGER' );
2917 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
2918 FND_MSG_PUB.add;
2919 END;
2920 END IF;
2921
2922 IF ( p_alt_resource_rec.dml_operation = 'C' ) THEN
2923 RETURN;
2924 END IF;
2925
2926 -- Check if the mandatory Object Version Number column contains a null value.
2927 IF ( p_alt_resource_rec.dml_operation <> 'D' AND (p_alt_resource_rec.object_version_number IS NULL OR
2928 p_alt_resource_rec.object_version_number = FND_API.G_MISS_NUM) ) THEN
2929 FND_MESSAGE.set_name( 'AHL','AHL_RM_ALT_OBJ_VER_NUM_NULL' );
2930 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
2931 FND_MSG_PUB.add;
2932 END IF;
2933
2934 -- Check if the mandatory rt_oper_resource ID column contains a null value.
2935 IF ( p_alt_resource_rec.dml_operation <> 'D' AND (p_alt_resource_rec.alternate_resource_id IS NULL OR
2936 p_alt_resource_rec.alternate_resource_id = FND_API.G_MISS_NUM) ) THEN
2937 FND_MESSAGE.set_name( 'AHL','AHL_RM_ALT_RES_ID_NULL' );
2938 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
2939 FND_MSG_PUB.add;
2940 END IF;
2941
2942 END validate_alt_attributes;
2943
2944 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
2945 PROCEDURE validate_alt_record
2946 (
2947 p_rt_oper_resource_id IN NUMBER,
2948 p_alt_resource_rec IN alt_resource_rec_type,
2949 x_return_status OUT NOCOPY VARCHAR2
2950 )
2951 IS
2952
2953 l_res_type1 NUMBER;
2954 l_res_type2 NUMBER;
2955
2956 CURSOR check_resource_type1( c_rt_oper_resource_id NUMBER)
2957 IS
2958 SELECT resource_type_id
2959 FROM AHL_RT_OPER_RESOURCES_V
2960 WHERE rt_oper_resource_id = c_rt_oper_resource_id;
2961
2962 CURSOR check_resource_type2( c_aso_resource_id NUMBER)
2963 IS
2964 SELECT resource_type_id
2965 FROM AHL_RESOURCES
2966 WHERE resource_id = c_aso_resource_id;
2967 BEGIN
2968 x_return_status := FND_API.G_RET_STS_SUCCESS;
2969 OPEN check_resource_type1( p_rt_oper_resource_id);
2970 FETCH check_resource_type1 INTO l_res_type1;
2971 IF ( check_resource_type1%NOTFOUND ) THEN
2972 FND_MESSAGE.set_name( 'AHL','AHL_RM_INVALID_RT_OPER_RES_ID' );
2973 FND_MSG_PUB.add;
2974 x_return_status := FND_API.G_RET_STS_ERROR;
2975 ELSE
2976 OPEN check_resource_type2( p_alt_resource_rec.aso_resource_id);
2977 FETCH check_resource_type2 INTO l_res_type2;
2978 IF ( check_resource_type2%NOTFOUND ) THEN
2979 FND_MESSAGE.set_name( 'AHL','AHL_RM_INVALID_ASO_RES_ID' );
2980 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
2981 FND_MSG_PUB.add;
2982 x_return_status := FND_API.G_RET_STS_ERROR;
2983 ELSIF l_res_type1 <> l_res_type2 THEN
2984 FND_MESSAGE.set_name( 'AHL','AHL_RM_RES_TYPE_DIFF' );
2985 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
2986 FND_MSG_PUB.add;
2987 x_return_status := FND_API.G_RET_STS_ERROR;
2988 END IF;
2989 CLOSE check_resource_type2;
2990 END IF;
2991 CLOSE check_resource_type1;
2992 END validate_alt_record;
2993
2994 -- Procedure to Perform cross records validation and duplicate checks
2995 PROCEDURE validate_alt_records
2996 (
2997 p_rt_oper_resource_id IN NUMBER,
2998 x_return_status OUT NOCOPY VARCHAR2
2999 )
3000 IS
3001
3002 CURSOR get_dup_rec1 (c_rt_oper_resource_id number)
3003 IS
3004 SELECT name
3005 FROM AHL_ALTERNATE_RESOURCES_V
3006 WHERE rt_oper_resource_id = c_rt_oper_resource_id
3007 GROUP BY NAME
3008 HAVING count(*) > 1;
3009
3010 l_res_name VARCHAR2(30);
3011 l_primary_name varchar2(30);
3012
3013 CURSOR get_dup_rec2 (c_rt_oper_resource_id number)
3014 IS
3015 SELECT priority
3016 FROM AHL_ALTERNATE_RESOURCES
3017 WHERE rt_oper_resource_id = c_rt_oper_resource_id
3018 GROUP BY priority
3019 HAVING count(*) > 1;
3020
3021 l_priority NUMBER;
3022
3023 CURSOR get_primary_res_name (c_rt_oper_resource_id number)
3024 IS
3025 SELECT aso_resource_name
3026 from ahl_rt_oper_resources_v
3027 where rt_oper_resource_id = c_rt_oper_resource_id;
3028
3029 CURSOR get_alt_res_name (c_rt_oper_resource_id number, c_aso_resource_name varchar2)
3030 IS
3031 select name
3032 from AHL_alternate_resources_v
3033 where rt_oper_resource_id = c_rt_oper_resource_id
3034 and name = c_aso_resource_name;
3035
3036 --pdoki ER 7436910 Begin.
3037 CURSOR get_dept_conflicts ( c_object_id NUMBER, c_association_type_code VARCHAR2, c_aso_res_id NUMBER, c_alt_res_id NUMBER)
3038 IS
3039 SELECT 'X'
3040 FROM ahl_resource_mappings
3041 WHERE DEPARTMENT_ID IS NOT NULL
3042 AND aso_resource_id in
3043 (
3044 SELECT ASO_RESOURCE_ID
3045 FROM ahl_rt_oper_resources
3046 WHERE object_id = c_object_id
3047 AND ASSOCIATION_TYPE_CODE = c_association_type_code
3048 AND ASO_RESOURCE_ID <> c_aso_res_id
3049 )
3050 OR aso_resource_id = c_alt_res_id
3051 GROUP BY bom_org_id
3052 HAVING count(DISTINCT DEPARTMENT_ID) > 1;
3053
3054 CURSOR get_rt_oper_res_det (c_rt_oper_resource_id number)
3055 IS
3056 SELECT OBJECT_ID,ASSOCIATION_TYPE_CODE, ASO_RESOURCE_ID
3057 FROM AHL_RT_OPER_RESOURCES
3058 WHERE RT_OPER_RESOURCE_ID= c_rt_oper_resource_id;
3059
3060 CURSOR get_aso_res_ids(c_rt_oper_resource_id number)
3061 IS
3062 SELECT ASO_RESOURCE_ID
3063 FROM AHL_ALTERNATE_RESOURCES
3064 WHERE RT_OPER_RESOURCE_ID= c_rt_oper_resource_id;
3065
3066 l_dummy VARCHAR2(1);
3067 l_object_id NUMBER;
3068 l_association_type_code VARCHAR2(30);
3069 l_aso_res_id NUMBER;
3070 l_alt_res_id NUMBER;
3071 --pdoki ER 7436910 End.
3072
3073 BEGIN
3074
3075 x_return_status := FND_API.G_RET_STS_SUCCESS;
3076 -- Check whether any duplicate rt_oper_resource records for the given object_ID
3077 OPEN get_primary_res_name (p_rt_oper_resource_id);
3078 FETCH get_primary_res_name INTO l_primary_name;
3079 IF get_primary_res_name%NOTFOUND THEN
3080 CLOSE get_primary_res_name;
3081 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_RT_OPER_RES_ID' );
3082 FND_MSG_PUB.add;
3083 x_return_status := FND_API.G_RET_STS_ERROR;
3084 RAISE FND_API.G_EXC_ERROR;
3085 ELSE
3086 CLOSE get_primary_res_name;
3087 OPEN get_alt_res_name (p_rt_oper_resource_id, l_primary_name);
3088 FETCH get_alt_res_name INTO l_res_name;
3089 IF get_alt_res_name%FOUND THEN
3090 CLOSE get_alt_res_name;
3091 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_PRIMARY_RESOURCE_NAME' );
3092 FND_MESSAGE.set_token( 'RECORD', l_primary_name);
3093 FND_MSG_PUB.add;
3094 x_return_status := FND_API.G_RET_STS_ERROR;
3095 RAISE FND_API.G_EXC_ERROR;
3096 ELSE
3097 CLOSE get_alt_res_name;
3098 END IF;
3099 END IF;
3100
3101 OPEN get_dup_rec1(p_rt_oper_resource_id);
3102 LOOP
3103 FETCH get_dup_rec1 INTO l_res_name;
3104 EXIT WHEN get_dup_rec1%NOTFOUND;
3105 END LOOP;
3106 IF ( get_dup_rec1%ROWCOUNT > 0 ) THEN
3107 CLOSE get_dup_rec1;
3108 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ALT_RESOURCE_DUP' );
3109 FND_MESSAGE.set_token( 'RECORD', l_res_name );
3110 FND_MSG_PUB.add;
3111 x_return_status := FND_API.G_RET_STS_ERROR;
3112 RAISE FND_API.G_EXC_ERROR;
3113 END IF;
3114 CLOSE get_dup_rec1;
3115
3116 OPEN get_dup_rec2(p_rt_oper_resource_id);
3117 LOOP
3118 FETCH get_dup_rec2 INTO l_priority;
3119 EXIT WHEN get_dup_rec2%NOTFOUND;
3120 END LOOP;
3121 IF ( get_dup_rec2%ROWCOUNT > 0 ) THEN
3122 CLOSE get_dup_rec2;
3123 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ALT_RES_PRIORITY_DUP' );
3124 FND_MESSAGE.set_token( 'RECORD', l_priority );
3125 FND_MSG_PUB.add;
3126 x_return_status := FND_API.G_RET_STS_ERROR;
3127 RAISE FND_API.G_EXC_ERROR;
3128 END IF;
3129 CLOSE get_dup_rec2;
3130
3131 --pdoki ER 7436910 Begin.
3132 OPEN get_rt_oper_res_det( p_rt_oper_resource_id );
3133 FETCH get_rt_oper_res_det INTO
3134 l_object_id,
3135 l_association_type_code,
3136 l_aso_res_id;
3137 CLOSE get_rt_oper_res_det;
3138
3139 OPEN get_aso_res_ids( p_rt_oper_resource_id );
3140 LOOP
3141 FETCH get_aso_res_ids INTO l_alt_res_id;
3142 EXIT WHEN get_aso_res_ids%NOTFOUND;
3143 OPEN get_dept_conflicts( l_object_id, l_association_type_code, l_aso_res_id, l_alt_res_id);
3144 FETCH get_dept_conflicts INTO l_dummy;
3145 IF ( get_dept_conflicts%FOUND ) THEN
3146 CLOSE get_dept_conflicts;
3147 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_DEP_CONFLICT_RES' );
3148 FND_MSG_PUB.add;
3149 x_return_status := FND_API.G_RET_STS_ERROR;
3150 RAISE FND_API.G_EXC_ERROR;
3151 END IF;
3152 CLOSE get_dept_conflicts;
3153 END LOOP;
3154 CLOSE get_aso_res_ids;
3155 --pdoki ER 7436910 End.
3156
3157 END validate_alt_records;
3158
3159 PROCEDURE process_alternate_resource
3160 (
3161 p_api_version IN NUMBER := 1.0,
3162 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3163 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3164 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3165 p_default IN VARCHAR2 := FND_API.G_FALSE,
3166 p_module_type IN VARCHAR2 := NULL,
3167 x_return_status OUT NOCOPY VARCHAR2,
3168 x_msg_count OUT NOCOPY NUMBER,
3169 x_msg_data OUT NOCOPY VARCHAR2,
3170 p_rt_oper_resource_id IN NUMBER,
3171 p_x_alt_resource_tbl IN OUT NOCOPY alt_resource_tbl_type
3172 ) IS
3173
3174 l_api_version CONSTANT NUMBER := 1.0;
3175 l_return_status VARCHAR2(1);
3176 l_msg_count NUMBER;
3177 l_alt_resource_id NUMBER;
3178
3179 BEGIN
3180 -- Initialize API return status to success
3181 x_return_status := FND_API.G_RET_STS_SUCCESS;
3182
3183 -- Standard Start of API savepoint
3184 SAVEPOINT process_alternate_resource_pvt;
3185
3186 -- Standard call to check for call compatibility.
3187 IF NOT FND_API.compatible_api_call
3188 (
3189 l_api_version,
3190 p_api_version,
3191 G_API_NAME3,
3192 G_PKG_NAME
3193 )
3194 THEN
3195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3196 END IF;
3197
3198 -- Initialize message list if p_init_msg_list is set to TRUE.
3199 IF FND_API.to_boolean( p_init_msg_list ) THEN
3200 FND_MSG_PUB.initialize;
3201 END IF;
3202
3203 IF G_DEBUG = 'Y' THEN
3204 AHL_DEBUG_PUB.enable_debug;
3205 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : Begin API' );
3206 END IF;
3207
3208 -- Validate all the inputs of the API
3209 validate_alt_api_inputs
3210 (
3211 p_rt_oper_resource_id,
3212 p_x_alt_resource_tbl,
3213 l_return_status
3214 );
3215
3216 -- If the module type is JSP, then default values for ID columns of LOV attributes
3217 IF ( p_module_type = 'JSP' ) THEN
3218 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3219 IF ( p_x_alt_resource_tbl(i).dml_operation <> 'D' ) THEN
3220 clear_alt_lov_attribute_ids
3221 (
3222 p_x_alt_resource_tbl(i)
3223 );
3224 END IF;
3225 END LOOP;
3226 END IF;
3227
3228 -- Convert Values into Ids.
3229 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
3230 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
3231 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3232 IF ( p_x_alt_resource_tbl(i).dml_operation <> 'D' ) THEN
3233 convert_alt_values_to_ids
3234 (
3235 p_x_alt_resource_tbl(i) ,
3236 l_return_status
3237 );
3238
3239 -- If any severe error occurs, then, abort API.
3240 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3241 RAISE FND_API.G_EXC_ERROR;
3242 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3244 END IF;
3245 END IF;
3246 END LOOP;
3247 --END IF;
3248
3249 IF G_DEBUG = 'Y' THEN
3250 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after convert_values_to_ids' );
3251 END IF;
3252
3253 /* Removing the procedure call as a part of public API cleanup in 11510+
3254 -- Default rt_oper_resource attributes.
3255 IF FND_API.to_boolean( p_default ) THEN
3256 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3257 IF ( p_x_alt_resource_tbl(i).dml_operation <> 'D' ) THEN
3258 default_alt_attributes
3259 (
3260 p_x_alt_resource_tbl(i) -- IN OUT
3261 );
3262 END IF;
3263 END LOOP;
3264 END IF;
3265 */
3266
3267 IF G_DEBUG = 'Y' THEN
3268 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after default_attributes' );
3269 END IF;
3270
3271 -- Validate all attributes (Item level validation)
3272 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
3273 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
3274 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3275 validate_alt_attributes
3276 (
3277 p_x_alt_resource_tbl(i),
3278 l_return_status
3279 );
3280
3281 -- If any severe error occurs, then, abort API.
3282 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3283 RAISE FND_API.G_EXC_ERROR;
3284 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3286 END IF;
3287 END LOOP;
3288 --END IF;
3289
3290 IF G_DEBUG = 'Y' THEN
3291 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after validate_attributes' );
3292 END IF;
3293
3294 -- Default missing and unchanged attributes.
3295 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3296 IF ( p_x_alt_resource_tbl(i).dml_operation = 'U' ) THEN
3297 default_alt_unchang_attributes
3298 (
3299 p_x_alt_resource_tbl(i) -- IN OUT
3300 );
3301 ELSIF ( p_x_alt_resource_tbl(i).dml_operation = 'C' ) THEN
3302 default_alt_miss_attributes
3303 (
3304 p_x_alt_resource_tbl(i) -- IN OUT
3305 );
3306 END IF;
3307 END LOOP;
3308
3309 IF G_DEBUG = 'Y' THEN
3310 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after default_unchanged_attributes / default_missing_attributes' );
3311 END IF;
3312
3313 -- Perform cross attribute validation and missing attribute checks (Record level validation)
3314 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
3315 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
3316 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3317 IF ( p_x_alt_resource_tbl(i).dml_operation <> 'D' ) THEN
3318 validate_alt_record
3319 (
3320 p_rt_oper_resource_id,
3321 p_x_alt_resource_tbl(i),
3322 l_return_status
3323 );
3324
3325 -- If any severe error occurs, then, abort API.
3326 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3327 RAISE FND_API.G_EXC_ERROR;
3328 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3330 END IF;
3331 END IF;
3332 END LOOP;
3333 --END IF;
3334
3335 IF G_DEBUG = 'Y' THEN
3336 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after validate_record' );
3337 END IF;
3338
3339 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
3340 l_msg_count := FND_MSG_PUB.count_msg;
3341 IF l_msg_count > 0 THEN
3342 x_msg_count := l_msg_count;
3343 RAISE FND_API.G_EXC_ERROR;
3344 END IF;
3345
3346 -- Perform the DML statement directly.
3347 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3348 IF ( p_x_alt_resource_tbl(i).dml_operation = 'C' ) THEN
3349
3350 BEGIN
3351 -- Insert the record
3352 INSERT INTO AHL_ALTERNATE_RESOURCES
3353 (
3354 ALTERNATE_RESOURCE_ID,
3355 OBJECT_VERSION_NUMBER,
3356 LAST_UPDATE_DATE,
3357 LAST_UPDATED_BY,
3358 CREATION_DATE,
3359 CREATED_BY,
3360 LAST_UPDATE_LOGIN,
3361 RT_OPER_RESOURCE_ID,
3362 ASO_RESOURCE_ID,
3363 PRIORITY,
3364 ATTRIBUTE_CATEGORY,
3365 ATTRIBUTE1,
3366 ATTRIBUTE2,
3367 ATTRIBUTE3,
3368 ATTRIBUTE4,
3369 ATTRIBUTE5,
3370 ATTRIBUTE6,
3371 ATTRIBUTE7,
3372 ATTRIBUTE8,
3373 ATTRIBUTE9,
3374 ATTRIBUTE10,
3375 ATTRIBUTE11,
3376 ATTRIBUTE12,
3377 ATTRIBUTE13,
3378 ATTRIBUTE14,
3379 ATTRIBUTE15
3380 ) VALUES
3381 (
3382 AHL_ALTERNATE_RESOURCES_S.NEXTVAL,
3383 1,
3384 G_LAST_UPDATE_DATE,
3385 G_LAST_UPDATED_BY,
3386 G_CREATION_DATE,
3387 G_CREATED_BY,
3388 G_LAST_UPDATE_LOGIN,
3389 p_rt_oper_resource_id,
3390 p_x_alt_resource_tbl(i).aso_resource_id,
3391 p_x_alt_resource_tbl(i).priority,
3392 p_x_alt_resource_tbl(i).attribute_category,
3393 p_x_alt_resource_tbl(i).attribute1,
3394 p_x_alt_resource_tbl(i).attribute2,
3395 p_x_alt_resource_tbl(i).attribute3,
3396 p_x_alt_resource_tbl(i).attribute4,
3397 p_x_alt_resource_tbl(i).attribute5,
3398 p_x_alt_resource_tbl(i).attribute6,
3399 p_x_alt_resource_tbl(i).attribute7,
3400 p_x_alt_resource_tbl(i).attribute8,
3401 p_x_alt_resource_tbl(i).attribute9,
3402 p_x_alt_resource_tbl(i).attribute10,
3403 p_x_alt_resource_tbl(i).attribute11,
3404 p_x_alt_resource_tbl(i).attribute12,
3405 p_x_alt_resource_tbl(i).attribute13,
3406 p_x_alt_resource_tbl(i).attribute14,
3407 p_x_alt_resource_tbl(i).attribute15
3408 ) RETURNING alternate_resource_id INTO l_alt_resource_id ;
3409
3410 -- Set OUT values
3411 p_x_alt_resource_tbl(i).alternate_resource_id := l_alt_resource_id;
3412
3413
3414 EXCEPTION
3415 WHEN OTHERS THEN
3416 IF ( SQLCODE = -1 ) THEN
3417 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ALT_RESOURCE_DUP' );
3418 FND_MESSAGE.set_token( 'RECORD', p_x_alt_resource_tbl(i).aso_resource_name );
3419 FND_MSG_PUB.add;
3420 ELSE
3421 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
3422 fnd_log.string
3423 (
3424 fnd_log.level_unexpected,
3425 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME3,
3426 'AHL_ALTERNATE_RESOURCES insert error = ['||SQLERRM||']'
3427 );
3428 END IF;
3429 END IF;
3430 END;
3431
3432 ELSIF ( p_x_alt_resource_tbl(i).dml_operation = 'U' ) THEN
3433
3434 BEGIN
3435 -- Update the record
3436 UPDATE AHL_ALTERNATE_RESOURCES SET
3437 object_version_number = object_version_number + 1,
3438 last_update_date = G_LAST_UPDATE_DATE,
3439 last_updated_by = G_LAST_UPDATED_BY,
3440 last_update_login = G_LAST_UPDATE_LOGIN,
3441 aso_resource_id = p_x_alt_resource_tbl(i).aso_resource_id,
3442 priority = p_x_alt_resource_tbl(i).priority,
3443 attribute_category = p_x_alt_resource_tbl(i).attribute_category,
3444 attribute1 = p_x_alt_resource_tbl(i).attribute1,
3445 attribute2 = p_x_alt_resource_tbl(i).attribute2,
3446 attribute3 = p_x_alt_resource_tbl(i).attribute3,
3447 attribute4 = p_x_alt_resource_tbl(i).attribute4,
3448 attribute5 = p_x_alt_resource_tbl(i).attribute5,
3449 attribute6 = p_x_alt_resource_tbl(i).attribute6,
3450 attribute7 = p_x_alt_resource_tbl(i).attribute7,
3451 attribute8 = p_x_alt_resource_tbl(i).attribute8,
3452 attribute9 = p_x_alt_resource_tbl(i).attribute9,
3453 attribute10 = p_x_alt_resource_tbl(i).attribute10,
3454 attribute11 = p_x_alt_resource_tbl(i).attribute11,
3455 attribute12 = p_x_alt_resource_tbl(i).attribute12,
3456 attribute13 = p_x_alt_resource_tbl(i).attribute13,
3457 attribute14 = p_x_alt_resource_tbl(i).attribute14,
3458 attribute15 = p_x_alt_resource_tbl(i).attribute15
3459 WHERE alternate_resource_id = p_x_alt_resource_tbl(i).alternate_resource_id
3460 AND object_version_number = p_x_alt_resource_tbl(i).object_version_number;
3461
3462 -- If the record does not exist, then, abort API.
3463 IF ( SQL%ROWCOUNT = 0 ) THEN
3464 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
3465 FND_MESSAGE.set_token( 'RECORD', p_x_alt_resource_tbl(i).aso_resource_name );
3466 FND_MSG_PUB.add;
3467 END IF;
3468
3469 -- Set OUT values
3470 p_x_alt_resource_tbl(i).object_version_number := p_x_alt_resource_tbl(i).object_version_number + 1;
3471
3472 EXCEPTION
3473 WHEN OTHERS THEN
3474 IF ( SQLCODE = -1 ) THEN
3475 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ALT_RESOURCE_DUP' );
3476 FND_MESSAGE.set_token( 'RECORD', p_x_alt_resource_tbl(i).aso_resource_name );
3477 FND_MSG_PUB.add;
3478 ELSE
3479 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
3480 fnd_log.string
3481 (
3482 fnd_log.level_unexpected,
3483 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME3,
3484 'AHL_ALTERNATE_RESOURCES update error = ['||SQLERRM||']'
3485 );
3486 END IF;
3487 END IF;
3488 END;
3489
3490 ELSIF ( p_x_alt_resource_tbl(i).dml_operation = 'D' ) THEN
3491
3492 -- Delete the record
3493 DELETE FROM AHL_ALTERNATE_RESOURCES
3494 WHERE alternate_resource_id = p_x_alt_resource_tbl(i).alternate_resource_id
3495 AND object_version_number = p_x_alt_resource_tbl(i).object_version_number;
3496
3497 -- If the record does not exist, then, abort API.
3498 IF ( SQL%ROWCOUNT = 0 ) THEN
3499 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
3500 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
3501 FND_MSG_PUB.add;
3502 END IF;
3503 END IF;
3504 END LOOP;
3505
3506 IF G_DEBUG = 'Y' THEN
3507 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after DML operation' );
3508 END IF;
3509
3510 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
3511 l_msg_count := FND_MSG_PUB.count_msg;
3512 IF l_msg_count > 0 THEN
3513 x_msg_count := l_msg_count;
3514 RAISE FND_API.G_EXC_ERROR;
3515 END IF;
3516
3517 -- Perform cross records validations and duplicate records check
3518 validate_alt_records
3519 (
3520 p_rt_oper_resource_id,
3521 l_return_status
3522 );
3523
3524 -- If any severe error occurs, then, abort API.
3525 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3526 RAISE FND_API.G_EXC_ERROR;
3527 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3529 END IF;
3530
3531 IF G_DEBUG = 'Y' THEN
3532 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after validate_records' );
3533 END IF;
3534
3535 -- Perform the Commit (if requested)
3536 IF FND_API.to_boolean( p_commit ) THEN
3537 COMMIT WORK;
3538 END IF;
3539
3540 -- Count and Get messages (optional)
3541 FND_MSG_PUB.count_and_get
3542 (
3543 p_encoded => FND_API.G_FALSE,
3544 p_count => x_msg_count,
3545 p_data => x_msg_data
3546 );
3547
3548 -- Disable debug (if enabled)
3549 IF G_DEBUG = 'Y' THEN
3550 AHL_DEBUG_PUB.disable_debug;
3551 END IF;
3552
3553 EXCEPTION
3554 WHEN FND_API.G_EXC_ERROR THEN
3555 ROLLBACK TO PROCESS_ALTERNATE_RESOURCE_PVT;
3556 x_return_status := FND_API.G_RET_STS_ERROR ;
3557 FND_MSG_PUB.count_and_get
3558 (
3559 p_encoded => FND_API.G_FALSE,
3560 p_count => x_msg_count,
3561 p_data => x_msg_data
3562 );
3563
3564 -- Disable debug (if enabled)
3565 IF G_DEBUG = 'Y' THEN
3566 AHL_DEBUG_PUB.disable_debug;
3567 END IF;
3568
3569 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3570 ROLLBACK TO PROCESS_ALTERNATE_RESOURCE_PVT;
3571 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3572 FND_MSG_PUB.count_and_get
3573 (
3574 p_encoded => FND_API.G_FALSE,
3575 p_count => x_msg_count,
3576 p_data => x_msg_data
3577 );
3578
3579 -- Disable debug (if enabled)
3580 IF G_DEBUG = 'Y' THEN
3581 AHL_DEBUG_PUB.disable_debug;
3582 END IF;
3583
3584 WHEN OTHERS THEN
3585 ROLLBACK TO PROCESS_ALTERNATE_RESOURCE_PVT;
3586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3587 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3588 THEN
3589 FND_MSG_PUB.add_exc_msg
3590 (
3591 p_pkg_name => G_PKG_NAME,
3592 p_procedure_name => G_API_NAME3,
3593 p_error_text => SUBSTRB(SQLERRM,1,240)
3594 );
3595 END IF;
3596 FND_MSG_PUB.count_and_get
3597 (
3598 p_encoded => FND_API.G_FALSE,
3599 p_count => x_msg_count,
3600 p_data => x_msg_data
3601 );
3602
3603 -- Disable debug (if enabled)
3604 IF G_DEBUG = 'Y' THEN
3605 AHL_DEBUG_PUB.disable_debug;
3606 END IF;
3607
3608 END PROCESS_ALTERNATE_RESOURCE;
3609
3610 END AHL_RM_RT_OPER_RESOURCE_PVT;