[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.7.12020000.2 2012/12/07 13:44:25 sareepar 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 --MANESING::ER 9014609, added following cursor
1054 CURSOR get_aso_res_ids( c_object_id NUMBER, c_association_type_code VARCHAR2 )
1055 IS
1056 SELECT aso_resource_id
1057 FROM AHL_RT_OPER_RESOURCES
1058 WHERE association_type_code = c_association_type_code
1059 and object_id = c_object_id;
1060
1061 l_rt_oper_resource_rec rt_oper_resource_rec_type;
1062 l_dummy VARCHAR(1);
1063 l_rt_oper_res_id NUMBER;
1064 l_bom_asso_type VARCHAR(1) := null;
1065 l_aso_res_id NUMBER;
1066
1067 BEGIN
1068
1069 x_return_status := FND_API.G_RET_STS_SUCCESS;
1070 -- Check whether any duplicate rt_oper_resource records for the given object_ID
1071 OPEN get_dup_rec( p_object_id, p_association_type_code );
1072
1073 LOOP
1074 FETCH get_dup_rec INTO
1075 l_rt_oper_resource_rec.resource_type_id,
1076 l_rt_oper_resource_rec.resource_type,
1077 l_rt_oper_resource_rec.aso_resource_id,
1078 l_rt_oper_resource_rec.aso_resource_name;
1079
1080 EXIT WHEN get_dup_rec%NOTFOUND;
1081 END LOOP;
1082
1083 IF ( get_dup_rec%ROWCOUNT > 0 ) THEN
1084 CLOSE get_dup_rec;
1085 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RT_OPER_RESOURCE_DUP' );
1086 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_rt_oper_resource_rec ) );
1087 FND_MSG_PUB.add;
1088 x_return_status := FND_API.G_RET_STS_ERROR;
1089 RETURN;
1090 END IF;
1091
1092 CLOSE get_dup_rec;
1093
1094 -- MANESING::ER 9014609, 28-Jun-2010
1095 -- Check whether all the cmro resources to be associated to a route/operation have the same bom association type,
1096 -- i.e. either 1 or multiple BOM resources per org associated to a cmro resource
1097 OPEN get_aso_res_ids( p_object_id, p_association_type_code );
1098 LOOP
1099 FETCH get_aso_res_ids INTO l_aso_res_id;
1100 EXIT WHEN get_aso_res_ids%NOTFOUND;
1101
1102 -- Find how bom resources are associated to this cmro resource
1103 SELECT NVL(one_bomres_org, 'Y')
1104 INTO l_dummy
1105 FROM ahl_resources
1106 WHERE resource_id = l_aso_res_id;
1107
1108 -- Check whether all other cmro resources are of the same bom association type as the first cmro resource
1109 IF l_bom_asso_type IS null THEN
1110 l_bom_asso_type := l_dummy;
1111
1112 ELSIF l_bom_asso_type <> l_dummy THEN
1113 FND_MESSAGE.set_name('AHL', 'AHL_RM_DIFF_BOM_ASSO_TYPES');
1114 FND_MSG_PUB.add;
1115 x_return_status := FND_API.G_RET_STS_ERROR;
1116 RETURN;
1117 END IF;
1118 END LOOP;
1119 CLOSE get_aso_res_ids;
1120
1121 --pdoki ER 7436910 Begin.
1122 --checking dept conflicts among primary resources
1123 -- MANESING::ER 9014609, 28-Jun-2010
1124 -- Following department level validations are done only if cmro resources associated have only 1 BOM resource per org
1125 IF l_bom_asso_type = 'Y' THEN
1126 OPEN get_dept_conflicts( p_object_id, p_association_type_code );
1127
1128 FETCH get_dept_conflicts INTO l_dummy;
1129
1130 IF ( get_dept_conflicts%FOUND ) THEN
1131 CLOSE get_dept_conflicts;
1132 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_DEP_CONFLICT_RES' );
1133 FND_MSG_PUB.add;
1134 x_return_status := FND_API.G_RET_STS_ERROR;
1135 RETURN;
1136 END IF;
1137
1138 CLOSE get_dept_conflicts;
1139 END IF;
1140
1141 --checking dept conflicts b/w the primary resource and alt resources of OTHER primary resources
1142 IF l_bom_asso_type = 'Y' THEN
1143 OPEN get_rt_oper_res_ids( p_object_id, p_association_type_code );
1144
1145 LOOP
1146 FETCH get_rt_oper_res_ids INTO l_rt_oper_res_id;
1147
1148 EXIT WHEN get_rt_oper_res_ids%NOTFOUND;
1149
1150 OPEN get_dept_conflicts_alt_res( p_object_id, p_association_type_code, l_rt_oper_res_id);
1151
1152 FETCH get_dept_conflicts_alt_res INTO l_dummy;
1153
1154 IF ( get_dept_conflicts_alt_res%FOUND ) THEN
1155 CLOSE get_dept_conflicts_alt_res;
1156 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_DEP_CONFLICT_RES' );
1157 FND_MSG_PUB.add;
1158 x_return_status := FND_API.G_RET_STS_ERROR;
1159 RAISE FND_API.G_EXC_ERROR;
1160 END IF;
1161
1162 CLOSE get_dept_conflicts_alt_res;
1163
1164 END LOOP;
1165 CLOSE get_rt_oper_res_ids;
1166 END IF;
1167 --pdoki ER 7436910 End.
1168
1169 END validate_records;
1170
1171 PROCEDURE process_rt_oper_resource
1172 (
1173 p_api_version IN NUMBER := 1.0,
1174 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1175 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1176 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1177 p_default IN VARCHAR2 := FND_API.G_FALSE,
1178 p_module_type IN VARCHAR2 := NULL,
1179 x_return_status OUT NOCOPY VARCHAR2,
1180 x_msg_count OUT NOCOPY NUMBER,
1181 x_msg_data OUT NOCOPY VARCHAR2,
1182 p_x_rt_oper_resource_tbl IN OUT NOCOPY rt_oper_resource_tbl_type,
1183 p_association_type_code IN VARCHAR2,
1184 p_object_id IN NUMBER
1185 )
1186 IS
1187
1188 cursor get_route_status (p_route_id in number)
1189 is
1190 select revision_status_code
1191 from ahl_routes_app_v
1192 where route_id = p_route_id;
1193
1194 l_obj_status VARCHAR2(30);
1195 -- Bug # 7644260 (FP for ER # 6998882) -- start
1196 l_min_sch_seq NUMBER ;
1197 -- Bug # 7644260 (FP for ER # 6998882) -- end
1198
1199 cursor get_oper_status (p_operation_id in number)
1200 is
1201 select revision_status_code
1202 from ahl_operations_b
1203 where operation_id = p_operation_id;
1204
1205 -- Bug # 7644260 (FP for ER # 6998882) -- start
1206 cursor get_min_sch_seq ( c_object_id NUMBER, c_association_type_code VARCHAR2 )
1207 is
1208 select min(schedule_seq)
1209 from ahl_rt_oper_resources
1210 where object_id = c_object_id
1211 and association_type_code = c_association_type_code
1212 and schedule_seq IS NOT NULL ;
1213 -- Bug # 7644260 (FP for ER # 6998882) -- end
1214
1215 l_api_version CONSTANT NUMBER := 1.0;
1216 l_return_status VARCHAR2(1);
1217 l_msg_count NUMBER;
1218 l_msg_data VARCHAR2(2000);
1219 l_rt_oper_resource_id NUMBER;
1220 l_x_operation_rec AHL_RM_OPERATION_PVT.operation_rec_type ;
1221 l_x_route_rec AHL_RM_ROUTE_PVT.route_rec_type ;
1222 BEGIN
1223 -- Initialize API return status to success
1224 x_return_status := FND_API.G_RET_STS_SUCCESS;
1225
1226 -- Standard Start of API savepoint
1227 SAVEPOINT process_rt_oper_resource_pvt;
1228
1229 -- Standard call to check for call compatibility.
1230 IF NOT FND_API.compatible_api_call
1231 (
1232 l_api_version,
1233 p_api_version,
1234 G_API_NAME1,
1235 G_PKG_NAME
1236 )
1237 THEN
1238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1239 END IF;
1240
1241 -- Initialize message list if p_init_msg_list is set to TRUE.
1242 IF FND_API.to_boolean( p_init_msg_list ) THEN
1243 FND_MSG_PUB.initialize;
1244 END IF;
1245
1246 IF G_DEBUG = 'Y' THEN
1247 AHL_DEBUG_PUB.enable_debug;
1248 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : Begin API' );
1249 END IF;
1250
1251
1252 --This is to be added before calling validate_api_inputs()
1253 -- Validate Application Usage
1254 IF (p_association_type_code = 'ROUTE')
1255 THEN
1256 AHL_RM_ROUTE_UTIL.validate_ApplnUsage
1257 (
1258 p_object_id => p_object_id,
1259 p_association_type => p_association_type_code ,
1260 x_return_status => x_return_status,
1261 x_msg_data => x_msg_data
1262 );
1263
1264 -- If any severe error occurs, then, abort API.
1265 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1266 RAISE FND_API.G_EXC_ERROR;
1267 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1269 END IF;
1270 END IF ;
1271
1272
1273 -- Validate all the inputs of the API
1274 validate_api_inputs
1275 (
1276 p_x_rt_oper_resource_tbl,
1277 p_association_type_code,
1278 p_object_id,
1279 l_return_status
1280 );
1281
1282 -- If any severe error occurs, then, abort API.
1283 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1284 RAISE FND_API.G_EXC_ERROR;
1285 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1287 END IF;
1288
1289 -- If the module type is JSP, then default values for ID columns of LOV attributes
1290 IF ( p_module_type = 'JSP' ) THEN
1291 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1292 IF ( p_x_rt_oper_resource_tbl(i).dml_operation <> 'D' ) THEN
1293 clear_lov_attribute_ids
1294 (
1295 p_x_rt_oper_resource_tbl(i) -- IN OUT Record with Values and Ids
1296 );
1297 END IF;
1298 END LOOP;
1299 END IF;
1300
1301 -- Convert Values into Ids.
1302 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
1303 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1304 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1305 IF ( p_x_rt_oper_resource_tbl(i).dml_operation <> 'D' ) THEN
1306 convert_values_to_ids
1307 (
1308 p_x_rt_oper_resource_tbl(i) , -- IN OUT Record with Values and Ids
1309 l_return_status -- OUT
1310 );
1311
1312 -- If any severe error occurs, then, abort API.
1313 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1314 RAISE FND_API.G_EXC_ERROR;
1315 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1317 END IF;
1318 END IF;
1319 END LOOP;
1320 --END IF;
1321
1322 IF G_DEBUG = 'Y' THEN
1323 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after convert_values_to_ids' );
1324 END IF;
1325
1326 -- Default rt_oper_resource attributes.
1327 /* Removed as a part of public API cleanup in 11510+.
1328 IF FND_API.to_boolean( p_default ) THEN
1329 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1330 IF ( p_x_rt_oper_resource_tbl(i).dml_operation <> 'D' ) THEN
1331 default_attributes
1332 (
1333 p_x_rt_oper_resource_tbl(i) -- IN OUT
1334 );
1335 END IF;
1336 END LOOP;
1337 END IF;
1338 */
1339
1340 IF G_DEBUG = 'Y' THEN
1341 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after default_attributes' );
1342 END IF;
1343
1344 -- Validate all attributes (Item level validation)
1345 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
1346 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1347 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1348 validate_attributes
1349 (
1350 p_object_id, -- IN
1351 p_association_type_code, -- IN
1352 p_x_rt_oper_resource_tbl(i), -- IN
1353 l_return_status -- OUT
1354 );
1355
1356 -- If any severe error occurs, then, abort API.
1357 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1358 RAISE FND_API.G_EXC_ERROR;
1359 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1361 END IF;
1362 END LOOP;
1363 --END IF;
1364
1365 IF G_DEBUG = 'Y' THEN
1366 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after validate_attributes' );
1367 END IF;
1368
1369 -- Default missing and unchanged attributes.
1370 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1371 IF ( p_x_rt_oper_resource_tbl(i).dml_operation = 'U' ) THEN
1372 default_unchanged_attributes
1373 (
1374 p_x_rt_oper_resource_tbl(i) -- IN OUT
1375 );
1376 ELSIF ( p_x_rt_oper_resource_tbl(i).dml_operation = 'C' ) THEN
1377 default_missing_attributes
1378 (
1379 p_x_rt_oper_resource_tbl(i) -- IN OUT
1380 );
1381 END IF;
1382 END LOOP;
1383
1384 IF G_DEBUG = 'Y' THEN
1385 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after default_unchanged_attributes / default_missing_attributes' );
1386 END IF;
1387
1388 -- Perform cross attribute validation and missing attribute checks (Record level validation)
1389 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
1390 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1391 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1392 IF ( p_x_rt_oper_resource_tbl(i).dml_operation <> 'D' ) THEN
1393 validate_record
1394 (
1395 p_x_rt_oper_resource_tbl(i), -- IN
1396 p_object_id,
1397 p_association_type_code,
1398 l_return_status -- OUT
1399 );
1400
1401 -- If any severe error occurs, then, abort API.
1402 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1403 RAISE FND_API.G_EXC_ERROR;
1404 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1406 END IF;
1407 END IF;
1408 END LOOP;
1409 --END IF;
1410
1411 IF G_DEBUG = 'Y' THEN
1412 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after validate_record' );
1413 END IF;
1414
1415 IF G_DEBUG='Y' THEN
1416 AHL_DEBUG_PUB.debug( 'Starting updating parent route/operation');
1417 END IF;
1418
1419 IF ( p_association_type_code = 'OPERATION')
1420 THEN
1421 IF G_DEBUG='Y' THEN
1422 AHL_DEBUG_PUB.debug( 'p_association_type_code = OPERATION');
1423 END IF;
1424
1425 AHL_RM_ROUTE_UTIL.validate_operation_status
1426 (
1427 p_object_id,
1428 l_msg_data,
1429 l_return_status
1430 );
1431
1432 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1433 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
1434 FND_MSG_PUB.ADD;
1435 x_return_status := l_return_status;
1436 RETURN;
1437 END IF;
1438
1439 -- Update route status from APPROVAL_REJECTED to DRAFT
1440 OPEN get_oper_status (p_object_id);
1441 FETCH get_oper_status INTO l_obj_status;
1442 IF (get_oper_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
1443 THEN
1444 UPDATE ahl_operations_b
1445 SET revision_status_code = 'DRAFT'
1446 WHERE operation_id = p_object_id;
1447 END IF;
1448 CLOSE get_oper_status;
1449
1450 ELSIF ( p_association_type_code = 'ROUTE')
1451 THEN
1452 IF G_DEBUG='Y' THEN
1453 AHL_DEBUG_PUB.debug( 'p_association_type_code = ROUTE');
1454 END IF;
1455 -- Check if the Route is existing and in Draft status
1456 AHL_RM_ROUTE_UTIL.validate_route_status
1457 (
1458 p_object_id,
1459 l_msg_data,
1460 l_return_status
1461 );
1462
1463 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1464 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
1465 FND_MSG_PUB.ADD;
1466 x_return_status := l_return_status;
1467 RETURN;
1468 END IF;
1469
1470 -- Update route status from APPROVAL_REJECTED to DRAFT
1471 OPEN get_route_status (p_object_id);
1472 FETCH get_route_status INTO l_obj_status;
1473 IF (get_route_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
1474 THEN
1475 UPDATE ahl_routes_b
1476 SET revision_status_code = 'DRAFT'
1477 WHERE route_id = p_object_id;
1478 END IF;
1479 CLOSE get_route_status;
1480
1481 END IF ;
1482
1483 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1484 l_msg_count := FND_MSG_PUB.count_msg;
1485 IF l_msg_count > 0 THEN
1486 x_msg_count := l_msg_count;
1487 RAISE FND_API.G_EXC_ERROR;
1488 END IF;
1489
1490 -- Perform the DML statement directly.
1491 FOR i IN 1..p_x_rt_oper_resource_tbl.count LOOP
1492 IF ( p_x_rt_oper_resource_tbl(i).dml_operation = 'C' ) THEN
1493
1494 BEGIN
1495 -- Insert the record
1496 INSERT INTO AHL_RT_OPER_RESOURCES
1497 (
1498 rt_oper_resource_ID,
1499 OBJECT_VERSION_NUMBER,
1500 LAST_UPDATE_DATE,
1501 LAST_UPDATED_BY,
1502 CREATION_DATE,
1503 CREATED_BY,
1504 LAST_UPDATE_LOGIN,
1505 association_type_code,
1506 object_ID,
1507 aso_resource_id,
1508 quantity,
1509 duration,
1510 ATTRIBUTE_CATEGORY,
1511 ATTRIBUTE1,
1512 ATTRIBUTE2,
1513 ATTRIBUTE3,
1514 ATTRIBUTE4,
1515 ATTRIBUTE5,
1516 ATTRIBUTE6,
1517 ATTRIBUTE7,
1518 ATTRIBUTE8,
1519 ATTRIBUTE9,
1520 ATTRIBUTE10,
1521 ATTRIBUTE11,
1522 ATTRIBUTE12,
1523 ATTRIBUTE13,
1524 ATTRIBUTE14,
1525 ATTRIBUTE15,
1526 scheduled_type_id, -- added for bug fix 6512803.
1527 -- Bug # 7644260 (FP for ER # 6998882) -- start
1528 schedule_seq
1529 -- Bug # 7644260 (FP for ER # 6998882) -- end
1530 ) VALUES
1531 (
1532 AHL_RT_OPER_RESOURCES_S.NEXTVAL,
1533 1,
1534 G_LAST_UPDATE_DATE,
1535 G_LAST_UPDATED_BY,
1536 G_CREATION_DATE,
1537 G_CREATED_BY,
1538 G_LAST_UPDATE_LOGIN,
1539 p_association_type_code,
1540 p_object_id,
1541 p_x_rt_oper_resource_tbl(i).aso_resource_id,
1542 p_x_rt_oper_resource_tbl(i).quantity,
1543 p_x_rt_oper_resource_tbl(i).duration,
1544 p_x_rt_oper_resource_tbl(i).attribute_category,
1545 p_x_rt_oper_resource_tbl(i).attribute1,
1546 p_x_rt_oper_resource_tbl(i).attribute2,
1547 p_x_rt_oper_resource_tbl(i).attribute3,
1548 p_x_rt_oper_resource_tbl(i).attribute4,
1549 p_x_rt_oper_resource_tbl(i).attribute5,
1550 p_x_rt_oper_resource_tbl(i).attribute6,
1551 p_x_rt_oper_resource_tbl(i).attribute7,
1552 p_x_rt_oper_resource_tbl(i).attribute8,
1553 p_x_rt_oper_resource_tbl(i).attribute9,
1554 p_x_rt_oper_resource_tbl(i).attribute10,
1555 p_x_rt_oper_resource_tbl(i).attribute11,
1556 p_x_rt_oper_resource_tbl(i).attribute12,
1557 p_x_rt_oper_resource_tbl(i).attribute13,
1558 p_x_rt_oper_resource_tbl(i).attribute14,
1559 p_x_rt_oper_resource_tbl(i).attribute15,
1560 p_x_rt_oper_resource_tbl(i).scheduled_type_id,
1561 -- Bug # 7644260 (FP for ER # 6998882) -- start
1562 p_x_rt_oper_resource_tbl(i).schedule_seq
1563 -- Bug # 7644260 (FP for ER # 6998882) -- end
1564 ) RETURNING rt_oper_resource_id INTO l_rt_oper_resource_id;
1565
1566 -- Set OUT values
1567 p_x_rt_oper_resource_tbl(i).rt_oper_resource_id := l_rt_oper_resource_id;
1568 p_x_rt_oper_resource_tbl(i).object_version_number := 1;
1569
1570 EXCEPTION
1571 WHEN OTHERS THEN
1572 IF ( SQLCODE = -1 ) THEN
1573 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RT_OPER_RESOURCE_DUP' );
1574 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_x_rt_oper_resource_tbl(i) ) );
1575 FND_MSG_PUB.add;
1576 ELSE
1577 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1578 fnd_log.string
1579 (
1580 fnd_log.level_unexpected,
1581 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME1,
1582 'AHL_RT_OPER_RESOURCES insert error = ['||SQLERRM||']'
1583 );
1584 END IF;
1585 END IF;
1586 END;
1587
1588 ELSIF ( p_x_rt_oper_resource_tbl(i).dml_operation = 'U' ) THEN
1589
1590 BEGIN
1591 -- Update the record
1592 UPDATE AHL_RT_OPER_RESOURCES SET
1593 object_version_number = object_version_number + 1,
1594 last_update_date = G_LAST_UPDATE_DATE,
1595 last_updated_by = G_LAST_UPDATED_BY,
1596 last_update_login = G_LAST_UPDATE_LOGIN,
1597 aso_resource_id = p_x_rt_oper_resource_tbl(i).aso_resource_id,
1598 quantity = p_x_rt_oper_resource_tbl(i).quantity,
1599 duration = p_x_rt_oper_resource_tbl(i).duration,
1600 attribute_category = p_x_rt_oper_resource_tbl(i).attribute_category,
1601 attribute1 = p_x_rt_oper_resource_tbl(i).attribute1,
1602 attribute2 = p_x_rt_oper_resource_tbl(i).attribute2,
1603 attribute3 = p_x_rt_oper_resource_tbl(i).attribute3,
1604 attribute4 = p_x_rt_oper_resource_tbl(i).attribute4,
1605 attribute5 = p_x_rt_oper_resource_tbl(i).attribute5,
1606 attribute6 = p_x_rt_oper_resource_tbl(i).attribute6,
1607 attribute7 = p_x_rt_oper_resource_tbl(i).attribute7,
1608 attribute8 = p_x_rt_oper_resource_tbl(i).attribute8,
1609 attribute9 = p_x_rt_oper_resource_tbl(i).attribute9,
1610 attribute10 = p_x_rt_oper_resource_tbl(i).attribute10,
1611 attribute11 = p_x_rt_oper_resource_tbl(i).attribute11,
1612 attribute12 = p_x_rt_oper_resource_tbl(i).attribute12,
1613 attribute13 = p_x_rt_oper_resource_tbl(i).attribute13,
1614 attribute14 = p_x_rt_oper_resource_tbl(i).attribute14,
1615 attribute15 = p_x_rt_oper_resource_tbl(i).attribute15,
1616 -- added for bug fix# 6512803.
1617 scheduled_type_id = p_x_rt_oper_resource_tbl(i).scheduled_type_id,
1618 -- Bug # 7644260 (FP for ER # 6998882) -- start
1619 schedule_seq = p_x_rt_oper_resource_tbl(i).schedule_seq
1620 -- Bug # 7644260 (FP for ER # 6998882) -- end
1621 WHERE rt_oper_resource_id = p_x_rt_oper_resource_tbl(i).rt_oper_resource_id
1622 AND object_version_number = p_x_rt_oper_resource_tbl(i).object_version_number;
1623
1624 -- If the record does not exist, then, abort API.
1625 IF ( SQL%ROWCOUNT = 0 ) THEN
1626 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1627 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_rt_oper_resource_tbl(i) ) );
1628 FND_MSG_PUB.add;
1629 END IF;
1630
1631 -- Set OUT values
1632 p_x_rt_oper_resource_tbl(i).object_version_number := p_x_rt_oper_resource_tbl(i).object_version_number + 1;
1633
1634 EXCEPTION
1635 WHEN OTHERS THEN
1636 IF ( SQLCODE = -1 ) THEN
1637 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RT_OPER_RESOURCE_DUP' );
1638 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_rt_oper_resource_tbl(i) ) );
1639 FND_MSG_PUB.add;
1640 ELSE
1641 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
1642 fnd_log.string
1643 (
1644 fnd_log.level_unexpected,
1645 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME1,
1646 'AHL_RT_OPER_RESOURCES update error = ['||SQLERRM||']'
1647 );
1648 END IF;
1649 END IF;
1650 END;
1651
1652 ELSIF ( p_x_rt_oper_resource_tbl(i).dml_operation = 'D' ) THEN
1653 --pdoki ER 7436910
1654 --Deleting alternate_resource mappings for deleted resource.
1655 DELETE FROM ahl_alternate_resources
1656 WHERE rt_oper_resource_id = p_x_rt_oper_resource_tbl(i).rt_oper_resource_id;
1657 --pdoki ER 7436910
1658
1659 -- Delete the record
1660 DELETE FROM AHL_RT_OPER_RESOURCES
1661 WHERE rt_oper_resource_id = p_x_rt_oper_resource_tbl(i).rt_oper_resource_id
1662 AND object_version_number = p_x_rt_oper_resource_tbl(i).object_version_number;
1663
1664 -- If the record does not exist, then, abort API.
1665 IF ( SQL%ROWCOUNT = 0 ) THEN
1666 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1667 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
1668 FND_MSG_PUB.add;
1669 END IF;
1670 END IF;
1671 END LOOP;
1672
1673 IF G_DEBUG = 'Y' THEN
1674 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after DML operation' );
1675 END IF;
1676
1677 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1678 l_msg_count := FND_MSG_PUB.count_msg;
1679 IF l_msg_count > 0 THEN
1680 x_msg_count := l_msg_count;
1681 RAISE FND_API.G_EXC_ERROR;
1682 END IF;
1683
1684 -- Perform cross records validations and duplicate records check
1685 validate_records
1686 (
1687 p_object_id, -- IN
1688 p_association_type_code,
1689 l_return_status -- OUT
1690 );
1691
1692 -- If any severe error occurs, then, abort API.
1693 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1694 RAISE FND_API.G_EXC_ERROR;
1695 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1697 END IF;
1698
1699 IF G_DEBUG = 'Y' THEN
1700 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after validate_records' );
1701 END IF;
1702
1703 -- Bug # 7644260 (FP for ER # 6998882) -- start
1704 OPEN get_min_sch_seq( p_object_id, p_association_type_code );
1705 FETCH get_min_sch_seq INTO l_min_sch_seq ;
1706 IF get_min_sch_seq%FOUND THEN
1707 UPDATE ahl_rt_oper_resources
1708 SET schedule_seq = l_min_sch_seq
1709 WHERE object_id = p_object_id
1710 AND association_type_code = p_association_type_code
1711 AND schedule_seq IS NULL ;
1712 END IF;
1713 CLOSE get_min_sch_seq;
1714 -- Bug # 7644260 (FP for ER # 6998882) -- end
1715
1716 -- Perform the Commit (if requested)
1717 IF FND_API.to_boolean( p_commit ) THEN
1718 COMMIT WORK;
1719 END IF;
1720
1721 -- Count and Get messages (optional)
1722 FND_MSG_PUB.count_and_get
1723 (
1724 p_encoded => FND_API.G_FALSE,
1725 p_count => x_msg_count,
1726 p_data => x_msg_data
1727 );
1728
1729 -- Disable debug (if enabled)
1730 IF G_DEBUG = 'Y' THEN
1731 AHL_DEBUG_PUB.disable_debug;
1732 END IF;
1733 EXCEPTION
1734 WHEN FND_API.G_EXC_ERROR THEN
1735 ROLLBACK TO process_rt_oper_resource_PVT;
1736 x_return_status := FND_API.G_RET_STS_ERROR ;
1737 FND_MSG_PUB.count_and_get
1738 (
1739 p_encoded => FND_API.G_FALSE,
1740 p_count => x_msg_count,
1741 p_data => x_msg_data
1742 );
1743
1744 -- Disable debug (if enabled)
1745 IF G_DEBUG = 'Y' THEN
1746 AHL_DEBUG_PUB.disable_debug;
1747 END IF;
1748
1749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1750 ROLLBACK TO process_rt_oper_resource_PVT;
1751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1752 FND_MSG_PUB.count_and_get
1753 (
1754 p_encoded => FND_API.G_FALSE,
1755 p_count => x_msg_count,
1756 p_data => x_msg_data
1757 );
1758
1759 -- Disable debug (if enabled)
1760 IF G_DEBUG = 'Y' THEN
1761 AHL_DEBUG_PUB.disable_debug;
1762 END IF;
1763
1764 WHEN OTHERS THEN
1765 ROLLBACK TO process_rt_oper_resource_PVT;
1766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1767 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1768 THEN
1769 FND_MSG_PUB.add_exc_msg
1770 (
1771 p_pkg_name => G_PKG_NAME,
1772 p_procedure_name => G_API_NAME1,
1773 p_error_text => SUBSTRB(SQLERRM,1,240)
1774 );
1775 END IF;
1776 FND_MSG_PUB.count_and_get
1777 (
1778 p_encoded => FND_API.G_FALSE,
1779 p_count => x_msg_count,
1780 p_data => x_msg_data
1781 );
1782
1783 -- Disable debug (if enabled)
1784 IF G_DEBUG = 'Y' THEN
1785 AHL_DEBUG_PUB.disable_debug;
1786 END IF;
1787
1788 END process_rt_oper_resource;
1789
1790 -- The following local procedures are for another publiced API define_cost_parameter
1791 -- Procedure to perform Value to ID conversion for appropriate attributes
1792 PROCEDURE convert_costing_values_to_ids
1793 (
1794 p_x_rt_oper_cost_rec IN OUT NOCOPY rt_oper_cost_rec_type,
1795 x_return_status OUT NOCOPY VARCHAR2
1796 )
1797 IS
1798
1799 l_return_status VARCHAR2(1);
1800 l_msg_data VARCHAR2(2000);
1801
1802 BEGIN
1803 x_return_status := FND_API.G_RET_STS_SUCCESS;
1804
1805 -- Convert / Validate cost_basis_id
1806 IF ( ( p_x_rt_oper_cost_rec.cost_basis_id IS NOT NULL AND
1807 p_x_rt_oper_cost_rec.cost_basis_id <> FND_API.G_MISS_NUM ) OR
1808 ( p_x_rt_oper_cost_rec.cost_basis IS NOT NULL AND
1809 p_x_rt_oper_cost_rec.cost_basis <> FND_API.G_MISS_CHAR ) ) THEN
1810
1811 AHL_RM_ROUTE_UTIL.validate_mfg_lookup
1812 (
1813 x_return_status => l_return_status,
1814 x_msg_data => l_msg_data,
1815 p_lookup_type => 'CST_BASIS',
1816 p_lookup_meaning => p_x_rt_oper_cost_rec.cost_basis,
1817 p_x_lookup_code => p_x_rt_oper_cost_rec.cost_basis_id
1818 );
1819
1820 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1821 IF ( l_msg_data = 'AHL_RM_INVALID_MFG_LOOKUP' ) THEN
1822 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_COST_BASIS' );
1823 ELSIF ( l_msg_data = 'AHL_RM_TOO_MANY_MFG_LOOKUPS' ) THEN
1824 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_COST_BASIS' );
1825 ELSE
1826 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1827 END IF;
1828
1829 IF ( p_x_rt_oper_cost_rec.cost_basis IS NULL OR
1830 p_x_rt_oper_cost_rec.cost_basis = FND_API.G_MISS_CHAR ) THEN
1831 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_cost_rec.cost_basis_id ) );
1832 ELSE
1833 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_cost_rec.cost_basis );
1834 END IF;
1835
1836 FND_MSG_PUB.add;
1837 END IF;
1838
1839 END IF;
1840 /* activity look up obsoleted
1841 -- Convert / Validate activity_id
1842 IF ( ( p_x_rt_oper_cost_rec.activity_id IS NOT NULL AND
1843 p_x_rt_oper_cost_rec.activity_id <> FND_API.G_MISS_NUM ) OR
1844 ( p_x_rt_oper_cost_rec.activity IS NOT NULL AND
1845 p_x_rt_oper_cost_rec.activity <> FND_API.G_MISS_CHAR ) )
1846 THEN
1847
1848 AHL_RM_ROUTE_UTIL.validate_activity
1849 (
1850 x_return_status => l_return_status,
1851 x_msg_data => l_msg_data,
1852 p_activity => p_x_rt_oper_cost_rec.activity,
1853 p_x_activity_id => p_x_rt_oper_cost_rec.activity_id
1854 );
1855
1856 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1857 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1858
1859 IF ( p_x_rt_oper_cost_rec.activity IS NULL OR
1860 p_x_rt_oper_cost_rec.activity = FND_API.G_MISS_CHAR ) THEN
1861 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_cost_rec.activity_id ) );
1862 ELSE
1863 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_cost_rec.activity );
1864 END IF;
1865
1866 FND_MSG_PUB.add;
1867 END IF;
1868
1869 END IF;
1870 */
1871 -- Convert / Validate scheduled_type_id
1872 IF ( ( p_x_rt_oper_cost_rec.scheduled_type_id IS NOT NULL AND
1873 p_x_rt_oper_cost_rec.scheduled_type_id <> FND_API.G_MISS_NUM ) OR
1874 ( p_x_rt_oper_cost_rec.scheduled_type IS NOT NULL AND
1875 p_x_rt_oper_cost_rec.scheduled_type <> FND_API.G_MISS_CHAR ) )
1876 THEN
1877
1878 AHL_RM_ROUTE_UTIL.validate_mfg_lookup
1879 (
1880 x_return_status => l_return_status,
1881 x_msg_data => l_msg_data,
1882 p_lookup_type => 'BOM_RESOURCE_SCHEDULE_TYPE',
1883 p_lookup_meaning => p_x_rt_oper_cost_rec.scheduled_type,
1884 p_x_lookup_code => p_x_rt_oper_cost_rec.scheduled_type_id
1885 );
1886
1887 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1888 IF ( l_msg_data = 'AHL_RM_INVALID_MFG_LOOKUP' ) THEN
1889 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_SCHEDULE_TYPE' );
1890 ELSIF ( l_msg_data = 'AHL_RM_TOO_MANY_MFG_LOOKUPS' ) THEN
1891 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_SCHEDULE_TYPES' );
1892 ELSE
1893 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1894 END IF;
1895
1896 IF ( p_x_rt_oper_cost_rec.scheduled_type IS NULL OR
1897 p_x_rt_oper_cost_rec.scheduled_type = FND_API.G_MISS_CHAR ) THEN
1898 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_cost_rec.scheduled_type_id ) );
1899 ELSE
1900 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_cost_rec.scheduled_type );
1901 END IF;
1902
1903 FND_MSG_PUB.add;
1904 END IF;
1905
1906 END IF;
1907
1908 -- Convert / Validate autocharge_type_id
1909 IF ( ( p_x_rt_oper_cost_rec.autocharge_type_id IS NOT NULL AND
1910 p_x_rt_oper_cost_rec.autocharge_type_id <> FND_API.G_MISS_NUM ) OR
1911 ( p_x_rt_oper_cost_rec.autocharge_type IS NOT NULL AND
1912 p_x_rt_oper_cost_rec.autocharge_type <> FND_API.G_MISS_CHAR ) )
1913 THEN
1914
1915 AHL_RM_ROUTE_UTIL.validate_mfg_lookup
1916 (
1917 x_return_status => l_return_status,
1918 x_msg_data => l_msg_data,
1919 p_lookup_type => 'BOM_AUTOCHARGE_TYPE',
1920 p_lookup_meaning => p_x_rt_oper_cost_rec.autocharge_type,
1921 p_x_lookup_code => p_x_rt_oper_cost_rec.autocharge_type_id
1922 );
1923
1924 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1925 IF ( l_msg_data = 'AHL_RM_INVALID_MFG_LOOKUP' ) THEN
1926 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_ACHARGE_TYPE' );
1927 ELSIF ( l_msg_data = 'AHL_RM_TOO_MANY_MFG_LOOKUPS' ) THEN
1928 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_ACHARGE_TYPES' );
1929 ELSE
1930 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1931 END IF;
1932
1933 IF ( p_x_rt_oper_cost_rec.autocharge_type IS NULL OR
1934 p_x_rt_oper_cost_rec.autocharge_type = FND_API.G_MISS_CHAR ) THEN
1935 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_cost_rec.autocharge_type_id ) );
1936 ELSE
1937 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_cost_rec.autocharge_type );
1938 END IF;
1939
1940 FND_MSG_PUB.add;
1941 END IF;
1942
1943 END IF;
1944
1945 -- Convert / Validate standard_rate_flag
1946 IF ( ( p_x_rt_oper_cost_rec.standard_rate_flag IS NOT NULL AND
1947 p_x_rt_oper_cost_rec.standard_rate_flag <> FND_API.G_MISS_NUM ) OR
1948 ( p_x_rt_oper_cost_rec.standard_rate IS NOT NULL AND
1949 p_x_rt_oper_cost_rec.standard_rate <> FND_API.G_MISS_CHAR ) )
1950 THEN
1951
1952 AHL_RM_ROUTE_UTIL.validate_mfg_lookup
1953 (
1954 x_return_status => l_return_status,
1955 x_msg_data => l_msg_data,
1956 p_lookup_type => 'SYS_YES_NO',
1957 p_lookup_meaning => p_x_rt_oper_cost_rec.standard_rate,
1958 p_x_lookup_code => p_x_rt_oper_cost_rec.standard_rate_flag
1959 );
1960
1961 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1962 IF ( l_msg_data = 'AHL_RM_INVALID_MFG_LOOKUP' ) THEN
1963 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_STD_RATE' );
1964 ELSIF ( l_msg_data = 'AHL_RM_TOO_MANY_MFG_LOOKUPS' ) THEN
1965 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_STD_RATES' );
1966 ELSE
1967 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1968 END IF;
1969
1970 IF ( p_x_rt_oper_cost_rec.standard_rate IS NULL OR
1971 p_x_rt_oper_cost_rec.standard_rate = FND_API.G_MISS_CHAR ) THEN
1972 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_rt_oper_cost_rec.standard_rate_flag ) );
1973 ELSE
1974 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_cost_rec.standard_rate );
1975 END IF;
1976
1977 FND_MSG_PUB.add;
1978 END IF;
1979
1980 END IF;
1981 END convert_costing_values_to_ids;
1982
1983 -- Procedure to add Default values for unchanged attributes (UPDATE)
1984 PROCEDURE default_costing_unchanged
1985 (
1986 p_x_rt_oper_cost_rec IN OUT NOCOPY rt_oper_cost_rec_type
1987 )
1988 IS
1989
1990 l_old_rt_oper_cost_rec rt_oper_cost_rec_type;
1991
1992 CURSOR get_old_rec ( c_rt_oper_resource_id NUMBER )
1993 IS
1994 SELECT activity_id,
1995 activity,
1996 cost_basis_id,
1997 cost_basis,
1998 scheduled_type_id,
1999 scheduled_type,
2000 autocharge_type_id,
2001 autocharge_type,
2002 standard_rate_flag,
2003 standard_rate
2004 FROM AHL_RT_OPER_RESOURCES_V
2005 WHERE rt_oper_resource_id = c_rt_oper_resource_id;
2006
2007 BEGIN
2008
2009 -- Get the old record from AHL_MR_EFFECTIVITIES.
2010 OPEN get_old_rec( p_x_rt_oper_cost_rec.rt_oper_resource_id );
2011
2012 FETCH get_old_rec INTO
2013 l_old_rt_oper_cost_rec.activity_id,
2014 l_old_rt_oper_cost_rec.activity,
2015 l_old_rt_oper_cost_rec.cost_basis_id,
2016 l_old_rt_oper_cost_rec.cost_basis,
2017 l_old_rt_oper_cost_rec.scheduled_type_id,
2018 l_old_rt_oper_cost_rec.scheduled_type,
2019 l_old_rt_oper_cost_rec.autocharge_type_id,
2020 l_old_rt_oper_cost_rec.autocharge_type,
2021 l_old_rt_oper_cost_rec.standard_rate_flag,
2022 l_old_rt_oper_cost_rec.standard_rate;
2023
2024 IF get_old_rec%NOTFOUND THEN
2025 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_RES' );
2026 FND_MSG_PUB.add;
2027 CLOSE get_old_rec;
2028 RAISE FND_API.G_EXC_ERROR;
2029 END IF;
2030
2031 CLOSE get_old_rec;
2032
2033 -- Convert G_MISS values to NULL and NULL values to Old values
2034 IF ( p_x_rt_oper_cost_rec.activity_id = FND_API.G_MISS_NUM ) THEN
2035 p_x_rt_oper_cost_rec.activity_id := null;
2036 ELSIF ( p_x_rt_oper_cost_rec.activity_id IS NULL ) THEN
2037 p_x_rt_oper_cost_rec.activity_id := l_old_rt_oper_cost_rec.activity_id;
2038 END IF;
2039
2040 IF ( p_x_rt_oper_cost_rec.activity = FND_API.G_MISS_CHAR ) THEN
2041 p_x_rt_oper_cost_rec.activity := null;
2042 ELSIF ( p_x_rt_oper_cost_rec.activity IS NULL ) THEN
2043 p_x_rt_oper_cost_rec.activity := l_old_rt_oper_cost_rec.activity;
2044 END IF;
2045
2046 IF ( p_x_rt_oper_cost_rec.cost_basis_id = FND_API.G_MISS_NUM ) THEN
2047 p_x_rt_oper_cost_rec.cost_basis_id := null;
2048 ELSIF ( p_x_rt_oper_cost_rec.cost_basis_id IS NULL ) THEN
2049 p_x_rt_oper_cost_rec.cost_basis_id := l_old_rt_oper_cost_rec.cost_basis_id;
2050 END IF;
2051
2052 IF ( p_x_rt_oper_cost_rec.cost_basis = FND_API.G_MISS_CHAR ) THEN
2053 p_x_rt_oper_cost_rec.cost_basis := null;
2054 ELSIF ( p_x_rt_oper_cost_rec.cost_basis IS NULL ) THEN
2055 p_x_rt_oper_cost_rec.cost_basis := l_old_rt_oper_cost_rec.cost_basis;
2056 END IF;
2057
2058 IF ( p_x_rt_oper_cost_rec.scheduled_type_id = FND_API.G_MISS_NUM ) THEN
2059 p_x_rt_oper_cost_rec.scheduled_type_id := null;
2060 ELSIF ( p_x_rt_oper_cost_rec.scheduled_type_id IS NULL ) THEN
2061 p_x_rt_oper_cost_rec.scheduled_type_id := l_old_rt_oper_cost_rec.scheduled_type_id;
2062 END IF;
2063
2064 IF ( p_x_rt_oper_cost_rec.scheduled_type = FND_API.G_MISS_CHAR ) THEN
2065 p_x_rt_oper_cost_rec.scheduled_type := null;
2066 ELSIF ( p_x_rt_oper_cost_rec.scheduled_type IS NULL ) THEN
2067 p_x_rt_oper_cost_rec.scheduled_type := l_old_rt_oper_cost_rec.scheduled_type;
2068 END IF;
2069
2070 IF ( p_x_rt_oper_cost_rec.autocharge_type_id = FND_API.G_MISS_NUM ) THEN
2071 p_x_rt_oper_cost_rec.autocharge_type_id := null;
2072 ELSIF ( p_x_rt_oper_cost_rec.autocharge_type_id IS NULL ) THEN
2073 p_x_rt_oper_cost_rec.autocharge_type_id := l_old_rt_oper_cost_rec.autocharge_type_id;
2074 END IF;
2075
2076 IF ( p_x_rt_oper_cost_rec.autocharge_type = FND_API.G_MISS_CHAR ) THEN
2077 p_x_rt_oper_cost_rec.autocharge_type := null;
2078 ELSIF ( p_x_rt_oper_cost_rec.autocharge_type IS NULL ) THEN
2079 p_x_rt_oper_cost_rec.autocharge_type := l_old_rt_oper_cost_rec.autocharge_type;
2080 END IF;
2081
2082 IF ( p_x_rt_oper_cost_rec.standard_rate_flag = FND_API.G_MISS_NUM ) THEN
2083 p_x_rt_oper_cost_rec.standard_rate_flag := null;
2084 ELSIF ( p_x_rt_oper_cost_rec.standard_rate_flag IS NULL ) THEN
2085 p_x_rt_oper_cost_rec.standard_rate_flag := l_old_rt_oper_cost_rec.standard_rate_flag;
2086 END IF;
2087
2088 IF ( p_x_rt_oper_cost_rec.standard_rate = FND_API.G_MISS_CHAR ) THEN
2089 p_x_rt_oper_cost_rec.standard_rate := null;
2090 ELSIF ( p_x_rt_oper_cost_rec.standard_rate IS NULL ) THEN
2091 p_x_rt_oper_cost_rec.standard_rate := l_old_rt_oper_cost_rec.standard_rate;
2092 END IF;
2093
2094 END default_costing_unchanged;
2095
2096 PROCEDURE define_cost_parameter
2097 (
2098 p_api_version IN NUMBER := 1.0,
2099 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2100 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2101 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2102 p_default IN VARCHAR2 := FND_API.G_FALSE,
2103 p_module_type IN VARCHAR2 := NULL,
2104 x_return_status OUT NOCOPY VARCHAR2,
2105 x_msg_count OUT NOCOPY NUMBER,
2106 x_msg_data OUT NOCOPY VARCHAR2,
2107 p_x_rt_oper_cost_rec IN OUT NOCOPY rt_oper_cost_rec_type
2108 ) IS
2109
2110
2111
2112 -- added AR.resource_type_id and duration to fix bug# 6512803.
2113 CURSOR get_object_rec(C_RT_OPER_RESOURCE_ID NUMBER)
2114 IS
2115 SELECT --DISTINCT
2116 RES.OBJECT_ID,
2117 RES.ASSOCIATION_TYPE_CODE,
2118 AR.resource_type_id,
2119 RES.duration,
2120 AR.NAME
2121 FROM AHL_RT_OPER_RESOURCES RES, AHL_RESOURCES AR
2122 WHERE RES.aso_resource_id = AR.resource_id
2123 AND RES.RT_OPER_RESOURCE_ID = C_RT_OPER_RESOURCE_ID;
2124
2125 cursor get_route_status (p_route_id in number)
2126 is
2127 select revision_status_code
2128 from ahl_routes_app_v
2129 where route_id = p_route_id;
2130
2131 l_obj_status VARCHAR2(30);
2132
2133 cursor get_oper_status (p_operation_id in number)
2134 is
2135 select revision_status_code
2136 from ahl_operations_b
2137 where operation_id = p_operation_id;
2138
2139 l_api_version CONSTANT NUMBER := 1.0;
2140 l_api_name CONSTANT VARCHAR2(30) := 'DEFINE_COST_PARAMETER';
2141 l_return_status VARCHAR2(1);
2142 l_msg_count NUMBER;
2143 l_msg_data VARCHAR2(2000);
2144 l_object_id NUMBER;
2145 l_association_type_code VARCHAR2(30);
2146 l_x_operation_rec AHL_RM_OPERATION_PVT.operation_rec_type ;
2147 l_x_route_rec AHL_RM_ROUTE_PVT.route_rec_type ;
2148
2149 -- Added for bug fix# 6512803.
2150 l_resource_type_id NUMBER;
2151 l_max_rt_time_span NUMBER;
2152 l_duration NUMBER;
2153 l_name ahl_resources.name%TYPE;
2154
2155 BEGIN
2156 -- Initialize API return status to success
2157 x_return_status := FND_API.G_RET_STS_SUCCESS;
2158
2159 -- Standard Start of API savepoint
2160 SAVEPOINT define_cost_parameter_pvt;
2161
2162 -- Standard call to check for call compatibility.
2163 IF NOT FND_API.compatible_api_call
2164 (
2165 l_api_version,
2166 p_api_version,
2167 G_API_NAME2,
2168 G_PKG_NAME
2169 )
2170 THEN
2171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2172 END IF;
2173
2174 -- Initialize message list if p_init_msg_list is set to TRUE.
2175 IF FND_API.to_boolean( p_init_msg_list ) THEN
2176 FND_MSG_PUB.initialize;
2177 END IF;
2178
2179 IF G_DEBUG = 'Y' THEN
2180 AHL_DEBUG_PUB.enable_debug;
2181 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.'||l_api_name||': Begin API' );
2182 END IF;
2183
2184
2185 -- Convert Values into Ids.
2186 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
2187 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
2188 convert_costing_values_to_ids
2189 (
2190 p_x_rt_oper_cost_rec , -- IN OUT Record with Values and Ids
2191 l_return_status -- OUT
2192 );
2193
2194 -- If any severe error occurs, then, abort API.
2195 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2196 RAISE FND_API.G_EXC_ERROR;
2197 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2199 END IF;
2200 --END IF;
2201
2202 IF G_DEBUG = 'Y' THEN
2203 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after convert_costing_values_to_ids' );
2204 END IF;
2205
2206 OPEN get_object_rec ( p_x_rt_oper_cost_rec.RT_OPER_RESOURCE_ID ) ;
2207 FETCH get_object_rec INTO
2208 l_object_id ,
2209 l_association_type_code ,
2210 l_resource_type_id , -- added for 6512803.
2211 l_duration ,
2212 l_name ;
2213 IF get_object_rec%NOTFOUND THEN
2214 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_OBJECT' );
2215 FND_MSG_PUB.add;
2216 RAISE FND_API.G_EXC_ERROR;
2217 END IF;
2218 CLOSE get_object_rec;
2219
2220 -- Fix for bug# 6512803. Schedule flag can be set to Yes(1) only for person and
2221 -- machine resources.
2222
2223 --Bug 6625880. AMSRINIV. Doing away with below validation as misc resources can be scheduled.
2224
2225 IF (p_x_rt_oper_cost_rec.scheduled_type_id IS NOT NULL) AND
2226 (p_x_rt_oper_cost_rec.scheduled_type_id <> FND_API.G_MISS_NUM) THEN
2227 /*
2228 IF (p_x_rt_oper_cost_rec.scheduled_type_id = 1) AND (l_resource_type_id NOT IN (1,2)) THEN
2229 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_RES_SCHEDULE_TY' );
2230 IF ( p_x_rt_oper_cost_rec.scheduled_type IS NULL OR
2231 p_x_rt_oper_cost_rec.scheduled_type = FND_API.G_MISS_CHAR ) THEN
2232 SELECT meaning
2233 INTO p_x_rt_oper_cost_rec.scheduled_type
2234 FROM fnd_lookup_values_vl
2235 WHERE lookup_type = 'BOM_RESOURCE_SCHEDULE_TYPE'
2236 AND lookup_code = p_x_rt_oper_cost_rec.scheduled_type_id;
2237
2238 END IF;
2239 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_cost_rec.scheduled_type );
2240 FND_MSG_PUB.add;
2241 RAISE FND_API.G_EXC_ERROR;
2242 END IF; -- p_x_rt_oper_cost_rec.scheduled_type_id = 1
2243 */
2244 -- validate time span based on scheduled_type_id.
2245 IF (p_x_rt_oper_cost_rec.scheduled_type_id = 1 AND l_resource_type_id IN (1,2)) THEN
2246 AHL_RM_ROUTE_UTIL.validate_resource_duration
2247 (
2248 x_return_status => l_return_status,
2249 x_msg_data => l_msg_data,
2250 p_object_id => l_object_id,
2251 p_association_type_code=> l_association_type_code,
2252 p_duration => l_duration,
2253 x_max_rt_time_span => l_max_rt_time_span
2254 );
2255
2256 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
2257 FND_MESSAGE.set_name( 'AHL', l_msg_data );
2258 FND_MESSAGE.set_token( 'FIELD1', l_duration );
2259 FND_MESSAGE.set_token( 'FIELD2', l_max_rt_time_span );
2260 FND_MESSAGE.set_token( 'RECORD', l_name);
2261 FND_MSG_PUB.add;
2262 --dbms_output.put_line('Cost: validate_resource_duration error');
2263 END IF;
2264 END IF;
2265
2266 ELSIF (p_x_rt_oper_cost_rec.scheduled_type_id = FND_API.G_MISS_NUM) THEN
2267 -- default here to avoid query on ahl_resources.
2268 IF (l_resource_type_id IN (1,2)) THEN
2269 p_x_rt_oper_cost_rec.scheduled_type_id := 1;
2270 ELSE
2271 p_x_rt_oper_cost_rec.scheduled_type_id := 2;
2272 END IF;
2273
2274 END IF; -- p_x_rt_oper_cost_rec.scheduled_type_id IS NOT NULL
2275
2276 -- moved this procedure after validation on Schedule flag.
2277 -- Default missing and unchanged attributes.
2278 default_costing_unchanged
2279 (
2280 p_x_rt_oper_cost_rec -- IN OUT
2281 );
2282
2283 IF G_DEBUG = 'Y' THEN
2284 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after default_costing_unchanged' );
2285 END IF;
2286
2287 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2288 l_msg_count := FND_MSG_PUB.count_msg;
2289 IF l_msg_count > 0 THEN
2290 x_msg_count := l_msg_count;
2291 RAISE FND_API.G_EXC_ERROR;
2292 END IF;
2293
2294 /*
2295 -- to get the association object type code and the object id
2296 OPEN get_object_rec ( p_x_rt_oper_cost_rec.RT_OPER_RESOURCE_ID ) ;
2297 FETCH get_object_rec INTO
2298 l_object_id ,
2299 l_association_type_code ;
2300 IF get_object_rec%NOTFOUND THEN
2301 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_OBJECT' );
2302 FND_MSG_PUB.add;
2303 RAISE FND_API.G_EXC_ERROR;
2304 END IF;
2305 CLOSE get_object_rec;
2306 */
2307
2308
2309 --to change the status of Approval rejected Routes/perations to Draft if costing parameters are updated.
2310 IF ( l_association_type_code = 'OPERATION')
2311 THEN
2312 IF G_DEBUG='Y' THEN
2313 AHL_DEBUG_PUB.debug( 'l_association_type_code = OPERATION');
2314 END IF;
2315
2316 -- Check if the Route is existing and in Draft status
2317 AHL_RM_ROUTE_UTIL.validate_operation_status
2318 (
2319 l_object_id,
2320 l_msg_data,
2321 l_return_status
2322 );
2323
2324 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2325 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
2326 FND_MSG_PUB.ADD;
2327 x_return_status := l_return_status;
2328 RETURN;
2329 END IF;
2330
2331 -- Update route status from APPROVAL_REJECTED to DRAFT
2332 OPEN get_oper_status (l_object_id);
2333 FETCH get_oper_status INTO l_obj_status;
2334 IF (get_oper_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
2335 THEN
2336 UPDATE ahl_operations_b
2337 SET revision_status_code = 'DRAFT'
2338 WHERE operation_id = l_object_id;
2339 END IF;
2340 CLOSE get_oper_status;
2341
2342
2343 ELSIF ( l_association_type_code = 'ROUTE')
2344 THEN
2345 IF G_DEBUG='Y' THEN
2346 AHL_DEBUG_PUB.debug( 'l_association_type_code = ROUTE');
2347 END IF;
2348
2349 AHL_RM_ROUTE_UTIL.validate_ApplnUsage
2350 (
2351 p_object_id => l_object_id,
2352 p_association_type => l_association_type_code ,
2353 x_return_status => x_return_status,
2354 x_msg_data => x_msg_data
2355 );
2356
2357 -- If any severe error occurs, then, abort API.
2358 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2359 RAISE FND_API.G_EXC_ERROR;
2360 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2362 END IF ;
2363
2364 -- Check if the Route is existing and in Draft status
2365 AHL_RM_ROUTE_UTIL.validate_route_status
2366 (
2367 l_object_id,
2368 l_msg_data,
2369 l_return_status
2370 );
2371
2372 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2373 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
2374 FND_MSG_PUB.ADD;
2375 x_return_status := l_return_status;
2376 RETURN;
2377 END IF;
2378
2379 -- Update route status from APPROVAL_REJECTED to DRAFT
2380 OPEN get_route_status (l_object_id);
2381 FETCH get_route_status INTO l_obj_status;
2382 IF (get_route_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
2383 THEN
2384 UPDATE ahl_routes_b
2385 SET revision_status_code = 'DRAFT'
2386 WHERE route_id = l_object_id;
2387 END IF;
2388 CLOSE get_route_status;
2389
2390 END IF ;
2391
2392 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2393 l_msg_count := FND_MSG_PUB.count_msg;
2394 IF l_msg_count > 0 THEN
2395 x_msg_count := l_msg_count;
2396 RAISE FND_API.G_EXC_ERROR;
2397 END IF;
2398
2399 -- Update the cost parameters
2400 UPDATE AHL_RT_OPER_RESOURCES SET
2401 object_version_number = object_version_number + 1,
2402 activity_id = p_x_rt_oper_cost_rec.activity_id,
2403 cost_basis_id = p_x_rt_oper_cost_rec.cost_basis_id,
2404 scheduled_type_id = p_x_rt_oper_cost_rec.scheduled_type_id,
2405 autocharge_type_id = p_x_rt_oper_cost_rec.autocharge_type_id,
2406 standard_rate_flag = p_x_rt_oper_cost_rec.standard_rate_flag,
2407 last_update_date = SYSDATE,
2408 last_updated_by = FND_GLOBAL.user_id,
2409 last_update_login = FND_GLOBAL.login_id
2410 WHERE rt_oper_resource_id = p_x_rt_oper_cost_rec.rt_oper_resource_id
2411 AND object_version_number = p_x_rt_oper_cost_rec.object_version_number;
2412
2413 -- If the record does not exist, then, abort API.
2414 IF ( SQL%ROWCOUNT = 0 ) THEN
2415 FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_CHANGED');
2416 FND_MSG_PUB.add;
2417 END IF;
2418
2419 -- Set OUT values
2420 p_x_rt_oper_cost_rec.object_version_number := p_x_rt_oper_cost_rec.object_version_number + 1;
2421
2422 IF G_DEBUG = 'Y' THEN
2423 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after DML operation' );
2424 END IF;
2425
2426 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2427 l_msg_count := FND_MSG_PUB.count_msg;
2428 IF l_msg_count > 0 THEN
2429 x_msg_count := l_msg_count;
2430 RAISE FND_API.G_EXC_ERROR;
2431 END IF;
2432
2433 -- Perform the Commit (if requested)
2434 IF FND_API.to_boolean( p_commit ) THEN
2435 COMMIT WORK;
2436 END IF;
2437
2438 -- Count and Get messages (optional)
2439 FND_MSG_PUB.count_and_get
2440 (
2441 p_encoded => FND_API.G_FALSE,
2442 p_count => x_msg_count,
2443 p_data => x_msg_data
2444 );
2445
2446 -- Disable debug (if enabled)
2447 IF G_DEBUG = 'Y' THEN
2448 AHL_DEBUG_PUB.disable_debug;
2449 END IF;
2450
2451 EXCEPTION
2452 WHEN FND_API.G_EXC_ERROR THEN
2453 ROLLBACK TO define_cost_parameter_pvt;
2454 x_return_status := FND_API.G_RET_STS_ERROR ;
2455 FND_MSG_PUB.count_and_get
2456 (
2457 p_encoded => FND_API.G_FALSE,
2458 p_count => x_msg_count,
2459 p_data => x_msg_data
2460 );
2461
2462 -- Disable debug (if enabled)
2463 IF G_DEBUG = 'Y' THEN
2464 AHL_DEBUG_PUB.disable_debug;
2465 END IF;
2466
2467 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2468 ROLLBACK TO define_cost_parameter_pvt;
2469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2470 FND_MSG_PUB.count_and_get
2471 (
2472 p_encoded => FND_API.G_FALSE,
2473 p_count => x_msg_count,
2474 p_data => x_msg_data
2475 );
2476
2477 -- Disable debug (if enabled)
2478 IF G_DEBUG = 'Y' THEN
2479 AHL_DEBUG_PUB.disable_debug;
2480 END IF;
2481
2482 WHEN OTHERS THEN
2483 ROLLBACK TO define_cost_parameter_pvt;
2484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2485 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2486 THEN
2487 FND_MSG_PUB.add_exc_msg
2488 (
2489 p_pkg_name => G_PKG_NAME,
2490 p_procedure_name => G_API_NAME2,
2491 p_error_text => SUBSTRB(SQLERRM,1,240)
2492 );
2493 END IF;
2494 FND_MSG_PUB.count_and_get
2495 (
2496 p_encoded => FND_API.G_FALSE,
2497 p_count => x_msg_count,
2498 p_data => x_msg_data
2499 );
2500
2501 -- Disable debug (if enabled)
2502 IF G_DEBUG = 'Y' THEN
2503 AHL_DEBUG_PUB.disable_debug;
2504 END IF;
2505
2506 END define_cost_parameter;
2507
2508 -- Procedure to validate the all the inputs except the table structure of the API
2509 PROCEDURE validate_alt_api_inputs
2510 (
2511 p_rt_oper_resource_id IN NUMBER,
2512 p_alt_resource_tbl IN alt_resource_tbl_type,
2513 x_return_status OUT NOCOPY VARCHAR2
2514 )
2515 IS
2516
2517 l_return_status VARCHAR2(1);
2518 l_msg_data VARCHAR2(2000);
2519
2520 BEGIN
2521 x_return_status := FND_API.G_RET_STS_SUCCESS;
2522
2523 -- Check if a valid value is passed in p_rt_oper_resource_id
2524 IF ( p_rt_oper_resource_id = FND_API.G_MISS_NUM OR
2525 p_rt_oper_resource_id IS NULL ) THEN
2526 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RT_OPER_RES_NULL' );
2527 FND_MSG_PUB.add;
2528 x_return_status := FND_API.G_RET_STS_ERROR;
2529 RAISE FND_API.G_EXC_ERROR;
2530 END IF;
2531
2532 -- Check if at least one record is passed in p_rt_oper_resource_tbl
2533 IF ( p_alt_resource_tbl.count < 1 ) THEN
2534 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
2535 FND_MESSAGE.set_token( 'PROCEDURE', G_PKG_NAME || '.' || G_API_NAME3 );
2536 FND_MSG_PUB.add;
2537 x_return_status := FND_API.G_RET_STS_ERROR;
2538 RAISE FND_API.G_EXC_ERROR;
2539 END IF;
2540
2541 -- Validate DML Operation
2542 FOR i IN 1..p_alt_resource_tbl.count LOOP
2543 IF ( p_alt_resource_tbl(i).dml_operation IS NULL OR
2544 (
2545 p_alt_resource_tbl(i).dml_operation <> 'C' AND
2546 p_alt_resource_tbl(i).dml_operation <> 'U' AND
2547 p_alt_resource_tbl(i).dml_operation <> 'D'
2548 )
2549 )
2550 THEN
2551 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML' );
2552 FND_MESSAGE.set_token( 'FIELD', p_alt_resource_tbl(i).dml_operation );
2553 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_tbl(i).aso_resource_name );
2554 FND_MSG_PUB.add;
2555 x_return_status := FND_API.G_RET_STS_ERROR;
2556 RAISE FND_API.G_EXC_ERROR;
2557 END IF;
2558 END LOOP;
2559
2560 END validate_alt_api_inputs;
2561
2562 -- Procedure to Default NULL / G_MISS Values for LOV attributes
2563 PROCEDURE clear_alt_lov_attribute_ids
2564 (
2565 p_x_alt_resource_rec IN OUT NOCOPY alt_resource_rec_type
2566 )
2567 IS
2568
2569 BEGIN
2570 IF ( p_x_alt_resource_rec.aso_resource_name IS NULL ) THEN
2571 p_x_alt_resource_rec.aso_resource_id := NULL;
2572 ELSIF ( p_x_alt_resource_rec.aso_resource_name = FND_API.G_MISS_CHAR ) THEN
2573 p_x_alt_resource_rec.aso_resource_id := FND_API.G_MISS_NUM;
2574 END IF;
2575 END clear_alt_lov_attribute_ids;
2576
2577 -- Procedure to perform Value to ID conversion and validation for LOV attributes
2578 PROCEDURE convert_alt_values_to_ids
2579 (
2580 p_x_alt_resource_rec IN OUT NOCOPY alt_resource_rec_type,
2581 x_return_status OUT NOCOPY VARCHAR2
2582 )
2583 IS
2584
2585 l_return_status VARCHAR2(1);
2586 l_msg_data VARCHAR2(2000);
2587
2588 BEGIN
2589 x_return_status := FND_API.G_RET_STS_SUCCESS;
2590
2591 -- Convert / Validate ASO_resource_id
2592 IF ( ( p_x_alt_resource_rec.aso_resource_id IS NOT NULL AND
2593 p_x_alt_resource_rec.aso_resource_id <> FND_API.G_MISS_NUM ) OR
2594 ( p_x_alt_resource_rec.aso_resource_name IS NOT NULL AND
2595 p_x_alt_resource_rec.aso_resource_name <> FND_API.G_MISS_CHAR ) )
2596 THEN
2597
2598 AHL_RM_ROUTE_UTIL.validate_aso_resource
2599 (
2600 x_return_status => l_return_status,
2601 x_msg_data => l_msg_data,
2602 p_aso_resource_name => p_x_alt_resource_rec.aso_resource_name,
2603 p_x_aso_resource_id => p_x_alt_resource_rec.aso_resource_id
2604 );
2605
2606 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2607 FND_MESSAGE.set_name( 'AHL', l_msg_data );
2608
2609 IF ( p_x_alt_resource_rec.aso_resource_name IS NULL OR
2610 p_x_alt_resource_rec.aso_resource_name = FND_API.G_MISS_CHAR ) THEN
2611 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_alt_resource_rec.aso_resource_id ) );
2612 ELSE
2613 FND_MESSAGE.set_token( 'FIELD', p_x_alt_resource_rec.aso_resource_name );
2614 END IF;
2615
2616 FND_MESSAGE.set_token( 'RECORD', p_x_alt_resource_rec.aso_resource_name );
2617 FND_MSG_PUB.add;
2618 END IF;
2619 x_return_status := l_return_status;
2620 END IF;
2621
2622 END convert_alt_values_to_ids;
2623
2624 /* Removing as a part of Public API cleanup in 11510+.
2625 -- Procedure to add Default values for rt_oper_resource attributes
2626 PROCEDURE default_alt_attributes
2627 (
2628 p_x_alt_resource_rec IN OUT NOCOPY alt_resource_rec_type
2629 )
2630 IS
2631
2632 BEGIN
2633
2634 p_x_alt_resource_rec.last_update_date := SYSDATE;
2635 p_x_alt_resource_rec.last_updated_by := FND_GLOBAL.user_id;
2636 p_x_alt_resource_rec.last_update_login := FND_GLOBAL.login_id;
2637
2638 IF ( p_x_alt_resource_rec.dml_operation = 'C' ) THEN
2639 p_x_alt_resource_rec.object_version_number := 1;
2640 p_x_alt_resource_rec.creation_date := SYSDATE;
2641 p_x_alt_resource_rec.created_by := FND_GLOBAL.user_id;
2642 END IF;
2643
2644 END default_alt_attributes;
2645 */
2646 -- Procedure to add Default values for missing attributes (CREATE)
2647 PROCEDURE default_alt_miss_attributes
2648 (
2649 p_x_alt_resource_rec IN OUT NOCOPY alt_resource_rec_type
2650 )
2651 IS
2652
2653 BEGIN
2654
2655 -- Convert G_MISS values to NULL
2656
2657 IF ( p_x_alt_resource_rec.aso_resource_id = FND_API.G_MISS_NUM ) THEN
2658 p_x_alt_resource_rec.aso_resource_id := null;
2659 END IF;
2660
2661 IF ( p_x_alt_resource_rec.aso_resource_name = FND_API.G_MISS_CHAR ) THEN
2662 p_x_alt_resource_rec.aso_resource_name := null;
2663 END IF;
2664
2665 IF ( p_x_alt_resource_rec.priority = FND_API.G_MISS_NUM ) THEN
2666 p_x_alt_resource_rec.priority := null;
2667 END IF;
2668
2669 IF ( p_x_alt_resource_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
2670 p_x_alt_resource_rec.attribute_category := null;
2671 END IF;
2672
2673 IF ( p_x_alt_resource_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
2674 p_x_alt_resource_rec.attribute1 := null;
2675 END IF;
2676
2677 IF ( p_x_alt_resource_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
2678 p_x_alt_resource_rec.attribute2 := null;
2679 END IF;
2680
2681 IF ( p_x_alt_resource_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
2682 p_x_alt_resource_rec.attribute3 := null;
2683 END IF;
2684
2685 IF ( p_x_alt_resource_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
2686 p_x_alt_resource_rec.attribute4 := null;
2687 END IF;
2688
2689 IF ( p_x_alt_resource_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
2690 p_x_alt_resource_rec.attribute5 := null;
2691 END IF;
2692
2693 IF ( p_x_alt_resource_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
2694 p_x_alt_resource_rec.attribute6 := null;
2695 END IF;
2696
2697 IF ( p_x_alt_resource_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
2698 p_x_alt_resource_rec.attribute7 := null;
2699 END IF;
2700
2701 IF ( p_x_alt_resource_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
2702 p_x_alt_resource_rec.attribute8 := null;
2703 END IF;
2704
2705 IF ( p_x_alt_resource_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
2706 p_x_alt_resource_rec.attribute9 := null;
2707 END IF;
2708
2709 IF ( p_x_alt_resource_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
2710 p_x_alt_resource_rec.attribute10 := null;
2711 END IF;
2712
2713 IF ( p_x_alt_resource_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
2714 p_x_alt_resource_rec.attribute11 := null;
2715 END IF;
2716
2717 IF ( p_x_alt_resource_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
2718 p_x_alt_resource_rec.attribute12 := null;
2719 END IF;
2720
2721 IF ( p_x_alt_resource_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
2722 p_x_alt_resource_rec.attribute13 := null;
2723 END IF;
2724
2725 IF ( p_x_alt_resource_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
2726 p_x_alt_resource_rec.attribute14 := null;
2727 END IF;
2728
2729 IF ( p_x_alt_resource_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
2730 p_x_alt_resource_rec.attribute15 := null;
2731 END IF;
2732
2733 END default_alt_miss_attributes;
2734
2735 -- Procedure to add Default values for unchanged attributes (UPDATE)
2736 PROCEDURE default_alt_unchang_attributes
2737 (
2738 p_x_alt_resource_rec IN OUT NOCOPY alt_resource_rec_type
2739 )
2740 IS
2741
2742 l_old_alt_resource_rec alt_resource_rec_type;
2743
2744 CURSOR get_old_rec ( c_alt_resource_id NUMBER )
2745 IS
2746 SELECT alternate_resource_id,
2747 aso_resource_id,
2748 priority,
2749 attribute_category,
2750 attribute1,
2751 attribute2,
2752 attribute3,
2753 attribute4,
2754 attribute5,
2755 attribute6,
2756 attribute7,
2757 attribute8,
2758 attribute9,
2759 attribute10,
2760 attribute11,
2761 attribute12,
2762 attribute13,
2763 attribute14,
2764 attribute15
2765 FROM AHL_ALTERNATE_RESOURCES
2766 WHERE alternate_resource_id = c_alt_resource_id;
2767
2768 BEGIN
2769
2770 -- Get the old record from AHL_alt_RESOURCES.
2771 OPEN get_old_rec( p_x_alt_resource_rec.alternate_resource_id );
2772
2773 FETCH get_old_rec INTO
2774 l_old_alt_resource_rec.alternate_resource_id,
2775 l_old_alt_resource_rec.aso_resource_id,
2776 l_old_alt_resource_rec.priority,
2777 l_old_alt_resource_rec.attribute_category,
2778 l_old_alt_resource_rec.attribute1,
2779 l_old_alt_resource_rec.attribute2,
2780 l_old_alt_resource_rec.attribute3,
2781 l_old_alt_resource_rec.attribute4,
2782 l_old_alt_resource_rec.attribute5,
2783 l_old_alt_resource_rec.attribute6,
2784 l_old_alt_resource_rec.attribute7,
2785 l_old_alt_resource_rec.attribute8,
2786 l_old_alt_resource_rec.attribute9,
2787 l_old_alt_resource_rec.attribute10,
2788 l_old_alt_resource_rec.attribute11,
2789 l_old_alt_resource_rec.attribute12,
2790 l_old_alt_resource_rec.attribute13,
2791 l_old_alt_resource_rec.attribute14,
2792 l_old_alt_resource_rec.attribute15;
2793
2794 IF get_old_rec%NOTFOUND THEN
2795 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_ALT_RES_REC' );
2796 FND_MESSAGE.set_token( 'RECORD', p_x_alt_resource_rec.aso_resource_name );
2797 FND_MSG_PUB.add;
2798 CLOSE get_old_rec;
2799 RAISE FND_API.G_EXC_ERROR;
2800 END IF;
2801
2802 CLOSE get_old_rec;
2803
2804 -- Convert G_MISS values to NULL and NULL values to Old values
2805
2806 IF ( p_x_alt_resource_rec.aso_resource_id = FND_API.G_MISS_NUM ) THEN
2807 p_x_alt_resource_rec.aso_resource_id := null;
2808 ELSIF ( p_x_alt_resource_rec.aso_resource_id IS NULL ) THEN
2809 p_x_alt_resource_rec.aso_resource_id := l_old_alt_resource_rec.aso_resource_id;
2810 END IF;
2811
2812 IF ( p_x_alt_resource_rec.aso_resource_name = FND_API.G_MISS_CHAR ) THEN
2813 p_x_alt_resource_rec.aso_resource_name := null;
2814 ELSIF ( p_x_alt_resource_rec.aso_resource_name IS NULL ) THEN
2815 p_x_alt_resource_rec.aso_resource_name := l_old_alt_resource_rec.aso_resource_name;
2816 END IF;
2817
2818 IF ( p_x_alt_resource_rec.priority = FND_API.G_MISS_NUM ) THEN
2819 p_x_alt_resource_rec.priority := null;
2820 ELSIF ( p_x_alt_resource_rec.priority IS NULL ) THEN
2821 p_x_alt_resource_rec.priority := l_old_alt_resource_rec.priority;
2822 END IF;
2823
2824 IF ( p_x_alt_resource_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
2825 p_x_alt_resource_rec.attribute_category := null;
2826 ELSIF ( p_x_alt_resource_rec.attribute_category IS NULL ) THEN
2827 p_x_alt_resource_rec.attribute_category := l_old_alt_resource_rec.attribute_category;
2828 END IF;
2829
2830 IF ( p_x_alt_resource_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
2831 p_x_alt_resource_rec.attribute1 := null;
2832 ELSIF ( p_x_alt_resource_rec.attribute1 IS NULL ) THEN
2833 p_x_alt_resource_rec.attribute1 := l_old_alt_resource_rec.attribute1;
2834 END IF;
2835
2836 IF ( p_x_alt_resource_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
2837 p_x_alt_resource_rec.attribute2 := null;
2838 ELSIF ( p_x_alt_resource_rec.attribute2 IS NULL ) THEN
2839 p_x_alt_resource_rec.attribute2 := l_old_alt_resource_rec.attribute2;
2840 END IF;
2841
2842 IF ( p_x_alt_resource_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
2843 p_x_alt_resource_rec.attribute3 := null;
2844 ELSIF ( p_x_alt_resource_rec.attribute3 IS NULL ) THEN
2845 p_x_alt_resource_rec.attribute3 := l_old_alt_resource_rec.attribute3;
2846 END IF;
2847
2848 IF ( p_x_alt_resource_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
2849 p_x_alt_resource_rec.attribute4 := null;
2850 ELSIF ( p_x_alt_resource_rec.attribute4 IS NULL ) THEN
2851 p_x_alt_resource_rec.attribute4 := l_old_alt_resource_rec.attribute4;
2852 END IF;
2853
2854 IF ( p_x_alt_resource_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
2855 p_x_alt_resource_rec.attribute5 := null;
2856 ELSIF ( p_x_alt_resource_rec.attribute5 IS NULL ) THEN
2857 p_x_alt_resource_rec.attribute5 := l_old_alt_resource_rec.attribute5;
2858 END IF;
2859
2860 IF ( p_x_alt_resource_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
2861 p_x_alt_resource_rec.attribute6 := null;
2862 ELSIF ( p_x_alt_resource_rec.attribute6 IS NULL ) THEN
2863 p_x_alt_resource_rec.attribute6 := l_old_alt_resource_rec.attribute6;
2864 END IF;
2865
2866 IF ( p_x_alt_resource_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
2867 p_x_alt_resource_rec.attribute7 := null;
2868 ELSIF ( p_x_alt_resource_rec.attribute7 IS NULL ) THEN
2869 p_x_alt_resource_rec.attribute7 := l_old_alt_resource_rec.attribute7;
2870 END IF;
2871
2872 IF ( p_x_alt_resource_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
2873 p_x_alt_resource_rec.attribute8 := null;
2874 ELSIF ( p_x_alt_resource_rec.attribute8 IS NULL ) THEN
2875 p_x_alt_resource_rec.attribute8 := l_old_alt_resource_rec.attribute8;
2876 END IF;
2877
2878 IF ( p_x_alt_resource_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
2879 p_x_alt_resource_rec.attribute9 := null;
2880 ELSIF ( p_x_alt_resource_rec.attribute9 IS NULL ) THEN
2881 p_x_alt_resource_rec.attribute9 := l_old_alt_resource_rec.attribute9;
2882 END IF;
2883
2884 IF ( p_x_alt_resource_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
2885 p_x_alt_resource_rec.attribute10 := null;
2886 ELSIF ( p_x_alt_resource_rec.attribute10 IS NULL ) THEN
2887 p_x_alt_resource_rec.attribute10 := l_old_alt_resource_rec.attribute10;
2888 END IF;
2889
2890 IF ( p_x_alt_resource_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
2891 p_x_alt_resource_rec.attribute11 := null;
2892 ELSIF ( p_x_alt_resource_rec.attribute11 IS NULL ) THEN
2893 p_x_alt_resource_rec.attribute11 := l_old_alt_resource_rec.attribute11;
2894 END IF;
2895
2896 IF ( p_x_alt_resource_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
2897 p_x_alt_resource_rec.attribute12 := null;
2898 ELSIF ( p_x_alt_resource_rec.attribute12 IS NULL ) THEN
2899 p_x_alt_resource_rec.attribute12 := l_old_alt_resource_rec.attribute12;
2900 END IF;
2901
2902 IF ( p_x_alt_resource_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
2903 p_x_alt_resource_rec.attribute13 := null;
2904 ELSIF ( p_x_alt_resource_rec.attribute13 IS NULL ) THEN
2905 p_x_alt_resource_rec.attribute13 := l_old_alt_resource_rec.attribute13;
2906 END IF;
2907
2908 IF ( p_x_alt_resource_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
2909 p_x_alt_resource_rec.attribute14 := null;
2910 ELSIF ( p_x_alt_resource_rec.attribute14 IS NULL ) THEN
2911 p_x_alt_resource_rec.attribute14 := l_old_alt_resource_rec.attribute14;
2912 END IF;
2913
2914 IF ( p_x_alt_resource_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
2915 p_x_alt_resource_rec.attribute15 := null;
2916 ELSIF ( p_x_alt_resource_rec.attribute15 IS NULL ) THEN
2917 p_x_alt_resource_rec.attribute15 := l_old_alt_resource_rec.attribute15;
2918 END IF;
2919
2920 END default_alt_unchang_attributes;
2921
2922 -- Procedure to validate individual rt_oper_resource attributes
2923 PROCEDURE validate_alt_attributes
2924 (
2925 p_alt_resource_rec IN alt_resource_rec_type,
2926 x_return_status OUT NOCOPY VARCHAR2
2927 )
2928 IS
2929
2930 l_return_status VARCHAR2(1);
2931 l_msg_data VARCHAR2(2000);
2932 l_max_rt_time_span NUMBER;
2933 l_dummy NUMBER;
2934
2935
2936 BEGIN
2937 x_return_status := FND_API.G_RET_STS_SUCCESS;
2938
2939 -- Check if the mandatory quantity column contains a positive value.
2940 IF ( ( p_alt_resource_rec.dml_operation = 'C' AND
2941 p_alt_resource_rec.priority IS NULL ) OR
2942 ( p_alt_resource_rec.dml_operation <> 'D' AND
2943 p_alt_resource_rec.priority = FND_API.G_MISS_NUM ) ) THEN
2944 FND_MESSAGE.set_name( 'AHL','AHL_RM_PRIORITY_NULL' );
2945 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
2946 FND_MSG_PUB.add;
2947 ELSIF ( p_alt_resource_rec.dml_operation <> 'D' AND
2948 p_alt_resource_rec.priority <= 0 ) THEN
2949 FND_MESSAGE.set_name( 'AHL','AHL_RM_PRIORITY_LESS_ZERO' );
2950 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
2951 FND_MSG_PUB.add;
2952 ELSIF ( p_alt_resource_rec.dml_operation <> 'D' AND
2953 p_alt_resource_rec.priority > 0 ) THEN
2954 BEGIN
2955 l_dummy := TO_NUMBER(TO_CHAR(p_alt_resource_rec.priority), '999999');
2956 EXCEPTION
2957 WHEN OTHERS THEN
2958 FND_MESSAGE.set_name( 'AHL','AHL_RM_PRIORITY_NOT_INTEGER' );
2959 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
2960 FND_MSG_PUB.add;
2961 END;
2962 END IF;
2963
2964 IF ( p_alt_resource_rec.dml_operation = 'C' ) THEN
2965 RETURN;
2966 END IF;
2967
2968 -- Check if the mandatory Object Version Number column contains a null value.
2969 IF ( p_alt_resource_rec.dml_operation <> 'D' AND (p_alt_resource_rec.object_version_number IS NULL OR
2970 p_alt_resource_rec.object_version_number = FND_API.G_MISS_NUM) ) THEN
2971 FND_MESSAGE.set_name( 'AHL','AHL_RM_ALT_OBJ_VER_NUM_NULL' );
2972 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
2973 FND_MSG_PUB.add;
2974 END IF;
2975
2976 -- Check if the mandatory rt_oper_resource ID column contains a null value.
2977 IF ( p_alt_resource_rec.dml_operation <> 'D' AND (p_alt_resource_rec.alternate_resource_id IS NULL OR
2978 p_alt_resource_rec.alternate_resource_id = FND_API.G_MISS_NUM) ) THEN
2979 FND_MESSAGE.set_name( 'AHL','AHL_RM_ALT_RES_ID_NULL' );
2980 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
2981 FND_MSG_PUB.add;
2982 END IF;
2983
2984 END validate_alt_attributes;
2985
2986 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
2987 PROCEDURE validate_alt_record
2988 (
2989 p_rt_oper_resource_id IN NUMBER,
2990 p_alt_resource_rec IN alt_resource_rec_type,
2991 x_return_status OUT NOCOPY VARCHAR2
2992 )
2993 IS
2994
2995 l_res_type1 NUMBER;
2996 l_res_type2 NUMBER;
2997
2998 CURSOR check_resource_type1( c_rt_oper_resource_id NUMBER)
2999 IS
3000 SELECT resource_type_id
3001 FROM AHL_RT_OPER_RESOURCES_V
3002 WHERE rt_oper_resource_id = c_rt_oper_resource_id;
3003
3004 CURSOR check_resource_type2( c_aso_resource_id NUMBER)
3005 IS
3006 SELECT resource_type_id
3007 FROM AHL_RESOURCES
3008 WHERE resource_id = c_aso_resource_id;
3009 BEGIN
3010 x_return_status := FND_API.G_RET_STS_SUCCESS;
3011 OPEN check_resource_type1( p_rt_oper_resource_id);
3012 FETCH check_resource_type1 INTO l_res_type1;
3013 IF ( check_resource_type1%NOTFOUND ) THEN
3014 FND_MESSAGE.set_name( 'AHL','AHL_RM_INVALID_RT_OPER_RES_ID' );
3015 FND_MSG_PUB.add;
3016 x_return_status := FND_API.G_RET_STS_ERROR;
3017 ELSE
3018 OPEN check_resource_type2( p_alt_resource_rec.aso_resource_id);
3019 FETCH check_resource_type2 INTO l_res_type2;
3020 IF ( check_resource_type2%NOTFOUND ) THEN
3021 FND_MESSAGE.set_name( 'AHL','AHL_RM_INVALID_ASO_RES_ID' );
3022 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
3023 FND_MSG_PUB.add;
3024 x_return_status := FND_API.G_RET_STS_ERROR;
3025 ELSIF l_res_type1 <> l_res_type2 THEN
3026 FND_MESSAGE.set_name( 'AHL','AHL_RM_RES_TYPE_DIFF' );
3027 FND_MESSAGE.set_token( 'RECORD', p_alt_resource_rec.aso_resource_name );
3028 FND_MSG_PUB.add;
3029 x_return_status := FND_API.G_RET_STS_ERROR;
3030 END IF;
3031 CLOSE check_resource_type2;
3032 END IF;
3033 CLOSE check_resource_type1;
3034 END validate_alt_record;
3035
3036 -- Procedure to Perform cross records validation and duplicate checks
3037 PROCEDURE validate_alt_records
3038 (
3039 p_rt_oper_resource_id IN NUMBER,
3040 x_return_status OUT NOCOPY VARCHAR2
3041 )
3042 IS
3043
3044 CURSOR get_dup_rec1 (c_rt_oper_resource_id number)
3045 IS
3046 SELECT name
3047 FROM AHL_ALTERNATE_RESOURCES_V
3048 WHERE rt_oper_resource_id = c_rt_oper_resource_id
3049 GROUP BY NAME
3050 HAVING count(*) > 1;
3051
3052 l_res_name VARCHAR2(30);
3053 l_primary_name varchar2(30);
3054
3055 CURSOR get_dup_rec2 (c_rt_oper_resource_id number)
3056 IS
3057 SELECT priority
3058 FROM AHL_ALTERNATE_RESOURCES
3059 WHERE rt_oper_resource_id = c_rt_oper_resource_id
3060 GROUP BY priority
3061 HAVING count(*) > 1;
3062
3063 l_priority NUMBER;
3064
3065 CURSOR get_primary_res_name (c_rt_oper_resource_id number)
3066 IS
3067 SELECT aso_resource_name
3068 from ahl_rt_oper_resources_v
3069 where rt_oper_resource_id = c_rt_oper_resource_id;
3070
3071 CURSOR get_alt_res_name (c_rt_oper_resource_id number, c_aso_resource_name varchar2)
3072 IS
3073 select name
3074 from AHL_alternate_resources_v
3075 where rt_oper_resource_id = c_rt_oper_resource_id
3076 and name = c_aso_resource_name;
3077
3078 --pdoki ER 7436910 Begin.
3079 CURSOR get_dept_conflicts ( c_object_id NUMBER, c_association_type_code VARCHAR2, c_aso_res_id NUMBER, c_alt_res_id NUMBER)
3080 IS
3081 SELECT 'X'
3082 FROM ahl_resource_mappings
3083 WHERE DEPARTMENT_ID IS NOT NULL
3084 AND aso_resource_id in
3085 (
3086 SELECT ASO_RESOURCE_ID
3087 FROM ahl_rt_oper_resources
3088 WHERE object_id = c_object_id
3089 AND ASSOCIATION_TYPE_CODE = c_association_type_code
3090 AND ASO_RESOURCE_ID <> c_aso_res_id
3091 )
3092 OR aso_resource_id = c_alt_res_id
3093 GROUP BY bom_org_id
3094 HAVING count(DISTINCT DEPARTMENT_ID) > 1;
3095
3096 CURSOR get_rt_oper_res_det (c_rt_oper_resource_id number)
3097 IS
3098 SELECT OBJECT_ID,ASSOCIATION_TYPE_CODE, ASO_RESOURCE_ID
3099 FROM AHL_RT_OPER_RESOURCES
3100 WHERE RT_OPER_RESOURCE_ID= c_rt_oper_resource_id;
3101
3102 CURSOR get_aso_res_ids(c_rt_oper_resource_id number)
3103 IS
3104 SELECT ASO_RESOURCE_ID
3105 FROM AHL_ALTERNATE_RESOURCES
3106 WHERE RT_OPER_RESOURCE_ID= c_rt_oper_resource_id;
3107
3108 l_dummy VARCHAR2(1);
3109 l_object_id NUMBER;
3110 l_association_type_code VARCHAR2(30);
3111 l_aso_res_id NUMBER;
3112 l_alt_res_id NUMBER;
3113 --pdoki ER 7436910 End.
3114 l_bom_asso_type VARCHAR(1);
3115
3116 BEGIN
3117
3118 x_return_status := FND_API.G_RET_STS_SUCCESS;
3119 -- Check whether any duplicate rt_oper_resource records for the given object_ID
3120 OPEN get_primary_res_name (p_rt_oper_resource_id);
3121 FETCH get_primary_res_name INTO l_primary_name;
3122 IF get_primary_res_name%NOTFOUND THEN
3123 CLOSE get_primary_res_name;
3124 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_RT_OPER_RES_ID' );
3125 FND_MSG_PUB.add;
3126 x_return_status := FND_API.G_RET_STS_ERROR;
3127 RAISE FND_API.G_EXC_ERROR;
3128 ELSE
3129 CLOSE get_primary_res_name;
3130 OPEN get_alt_res_name (p_rt_oper_resource_id, l_primary_name);
3131 FETCH get_alt_res_name INTO l_res_name;
3132 IF get_alt_res_name%FOUND THEN
3133 CLOSE get_alt_res_name;
3134 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_PRIMARY_RESOURCE_NAME' );
3135 FND_MESSAGE.set_token( 'RECORD', l_primary_name);
3136 FND_MSG_PUB.add;
3137 x_return_status := FND_API.G_RET_STS_ERROR;
3138 RAISE FND_API.G_EXC_ERROR;
3139 ELSE
3140 CLOSE get_alt_res_name;
3141 END IF;
3142 END IF;
3143
3144 OPEN get_dup_rec1(p_rt_oper_resource_id);
3145 LOOP
3146 FETCH get_dup_rec1 INTO l_res_name;
3147 EXIT WHEN get_dup_rec1%NOTFOUND;
3148 END LOOP;
3149 IF ( get_dup_rec1%ROWCOUNT > 0 ) THEN
3150 CLOSE get_dup_rec1;
3151 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ALT_RESOURCE_DUP' );
3152 FND_MESSAGE.set_token( 'RECORD', l_res_name );
3153 FND_MSG_PUB.add;
3154 x_return_status := FND_API.G_RET_STS_ERROR;
3155 RAISE FND_API.G_EXC_ERROR;
3156 END IF;
3157 CLOSE get_dup_rec1;
3158
3159 OPEN get_dup_rec2(p_rt_oper_resource_id);
3160 LOOP
3161 FETCH get_dup_rec2 INTO l_priority;
3162 EXIT WHEN get_dup_rec2%NOTFOUND;
3163 END LOOP;
3164 IF ( get_dup_rec2%ROWCOUNT > 0 ) THEN
3165 CLOSE get_dup_rec2;
3166 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ALT_RES_PRIORITY_DUP' );
3167 FND_MESSAGE.set_token( 'RECORD', l_priority );
3168 FND_MSG_PUB.add;
3169 x_return_status := FND_API.G_RET_STS_ERROR;
3170 RAISE FND_API.G_EXC_ERROR;
3171 END IF;
3172 CLOSE get_dup_rec2;
3173
3174 --pdoki ER 7436910 Begin.
3175 OPEN get_rt_oper_res_det( p_rt_oper_resource_id );
3176 FETCH get_rt_oper_res_det INTO
3177 l_object_id,
3178 l_association_type_code,
3179 l_aso_res_id;
3180 CLOSE get_rt_oper_res_det;
3181
3182 -- MANESING::ER 9014609, 28-Jun-2010, find how bom resources are associated to cmro resource
3183 SELECT NVL(one_bomres_org, 'Y')
3184 INTO l_bom_asso_type
3185 FROM ahl_resources
3186 WHERE resource_id = l_aso_res_id;
3187
3188 -- MANESING::ER 9014609, 28-Jun-2010
3189 -- Following validation is done only if cmro resources associated have only 1 BOM resource per org
3190 IF l_bom_asso_type = 'Y' THEN
3191 OPEN get_aso_res_ids( p_rt_oper_resource_id );
3192 LOOP
3193 FETCH get_aso_res_ids INTO l_alt_res_id;
3194 EXIT WHEN get_aso_res_ids%NOTFOUND;
3195
3196 OPEN get_dept_conflicts( l_object_id, l_association_type_code, l_aso_res_id, l_alt_res_id);
3197 FETCH get_dept_conflicts INTO l_dummy;
3198 IF ( get_dept_conflicts%FOUND ) THEN
3199 CLOSE get_dept_conflicts;
3200 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_DEP_CONFLICT_RES' );
3201 FND_MSG_PUB.add;
3202 x_return_status := FND_API.G_RET_STS_ERROR;
3203 RAISE FND_API.G_EXC_ERROR;
3204 END IF;
3205 CLOSE get_dept_conflicts;
3206 END LOOP;
3207 CLOSE get_aso_res_ids;
3208 END IF;
3209 --pdoki ER 7436910 End.
3210
3211 END validate_alt_records;
3212
3213 PROCEDURE process_alternate_resource
3214 (
3215 p_api_version IN NUMBER := 1.0,
3216 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3217 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3218 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3219 p_default IN VARCHAR2 := FND_API.G_FALSE,
3220 p_module_type IN VARCHAR2 := NULL,
3221 x_return_status OUT NOCOPY VARCHAR2,
3222 x_msg_count OUT NOCOPY NUMBER,
3223 x_msg_data OUT NOCOPY VARCHAR2,
3224 p_rt_oper_resource_id IN NUMBER,
3225 p_x_alt_resource_tbl IN OUT NOCOPY alt_resource_tbl_type
3226 ) IS
3227
3228 l_api_version CONSTANT NUMBER := 1.0;
3229 l_return_status VARCHAR2(1);
3230 l_msg_count NUMBER;
3231 l_alt_resource_id NUMBER;
3232
3233 BEGIN
3234 -- Initialize API return status to success
3235 x_return_status := FND_API.G_RET_STS_SUCCESS;
3236
3237 -- Standard Start of API savepoint
3238 SAVEPOINT process_alternate_resource_pvt;
3239
3240 -- Standard call to check for call compatibility.
3241 IF NOT FND_API.compatible_api_call
3242 (
3243 l_api_version,
3244 p_api_version,
3245 G_API_NAME3,
3246 G_PKG_NAME
3247 )
3248 THEN
3249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3250 END IF;
3251
3252 -- Initialize message list if p_init_msg_list is set to TRUE.
3253 IF FND_API.to_boolean( p_init_msg_list ) THEN
3254 FND_MSG_PUB.initialize;
3255 END IF;
3256
3257 IF G_DEBUG = 'Y' THEN
3258 AHL_DEBUG_PUB.enable_debug;
3259 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : Begin API' );
3260 END IF;
3261
3262 -- Validate all the inputs of the API
3263 validate_alt_api_inputs
3264 (
3265 p_rt_oper_resource_id,
3266 p_x_alt_resource_tbl,
3267 l_return_status
3268 );
3269
3270 -- If the module type is JSP, then default values for ID columns of LOV attributes
3271 IF ( p_module_type = 'JSP' ) THEN
3272 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3273 IF ( p_x_alt_resource_tbl(i).dml_operation <> 'D' ) THEN
3274 clear_alt_lov_attribute_ids
3275 (
3276 p_x_alt_resource_tbl(i)
3277 );
3278 END IF;
3279 END LOOP;
3280 END IF;
3281
3282 -- Convert Values into Ids.
3283 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
3284 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
3285 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3286 IF ( p_x_alt_resource_tbl(i).dml_operation <> 'D' ) THEN
3287 convert_alt_values_to_ids
3288 (
3289 p_x_alt_resource_tbl(i) ,
3290 l_return_status
3291 );
3292
3293 -- If any severe error occurs, then, abort API.
3294 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3295 RAISE FND_API.G_EXC_ERROR;
3296 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3298 END IF;
3299 END IF;
3300 END LOOP;
3301 --END IF;
3302
3303 IF G_DEBUG = 'Y' THEN
3304 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after convert_values_to_ids' );
3305 END IF;
3306
3307 /* Removing the procedure call as a part of public API cleanup in 11510+
3308 -- Default rt_oper_resource attributes.
3309 IF FND_API.to_boolean( p_default ) THEN
3310 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3311 IF ( p_x_alt_resource_tbl(i).dml_operation <> 'D' ) THEN
3312 default_alt_attributes
3313 (
3314 p_x_alt_resource_tbl(i) -- IN OUT
3315 );
3316 END IF;
3317 END LOOP;
3318 END IF;
3319 */
3320
3321 IF G_DEBUG = 'Y' THEN
3322 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after default_attributes' );
3323 END IF;
3324
3325 -- Validate all attributes (Item level validation)
3326 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
3327 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
3328 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3329 validate_alt_attributes
3330 (
3331 p_x_alt_resource_tbl(i),
3332 l_return_status
3333 );
3334
3335 -- If any severe error occurs, then, abort API.
3336 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3337 RAISE FND_API.G_EXC_ERROR;
3338 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3340 END IF;
3341 END LOOP;
3342 --END IF;
3343
3344 IF G_DEBUG = 'Y' THEN
3345 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after validate_attributes' );
3346 END IF;
3347
3348 -- Default missing and unchanged attributes.
3349 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3350 IF ( p_x_alt_resource_tbl(i).dml_operation = 'U' ) THEN
3351 default_alt_unchang_attributes
3352 (
3353 p_x_alt_resource_tbl(i) -- IN OUT
3354 );
3355 ELSIF ( p_x_alt_resource_tbl(i).dml_operation = 'C' ) THEN
3356 default_alt_miss_attributes
3357 (
3358 p_x_alt_resource_tbl(i) -- IN OUT
3359 );
3360 END IF;
3361 END LOOP;
3362
3363 IF G_DEBUG = 'Y' THEN
3364 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after default_unchanged_attributes / default_missing_attributes' );
3365 END IF;
3366
3367 -- Perform cross attribute validation and missing attribute checks (Record level validation)
3368 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
3369 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
3370 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3371 IF ( p_x_alt_resource_tbl(i).dml_operation <> 'D' ) THEN
3372 validate_alt_record
3373 (
3374 p_rt_oper_resource_id,
3375 p_x_alt_resource_tbl(i),
3376 l_return_status
3377 );
3378
3379 -- If any severe error occurs, then, abort API.
3380 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3381 RAISE FND_API.G_EXC_ERROR;
3382 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3384 END IF;
3385 END IF;
3386 END LOOP;
3387 --END IF;
3388
3389 IF G_DEBUG = 'Y' THEN
3390 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after validate_record' );
3391 END IF;
3392
3393 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
3394 l_msg_count := FND_MSG_PUB.count_msg;
3395 IF l_msg_count > 0 THEN
3396 x_msg_count := l_msg_count;
3397 RAISE FND_API.G_EXC_ERROR;
3398 END IF;
3399
3400 -- Perform the DML statement directly.
3401 FOR i IN 1..p_x_alt_resource_tbl.count LOOP
3402 IF ( p_x_alt_resource_tbl(i).dml_operation = 'C' ) THEN
3403
3404 BEGIN
3405 -- Insert the record
3406 INSERT INTO AHL_ALTERNATE_RESOURCES
3407 (
3408 ALTERNATE_RESOURCE_ID,
3409 OBJECT_VERSION_NUMBER,
3410 LAST_UPDATE_DATE,
3411 LAST_UPDATED_BY,
3412 CREATION_DATE,
3413 CREATED_BY,
3414 LAST_UPDATE_LOGIN,
3415 RT_OPER_RESOURCE_ID,
3416 ASO_RESOURCE_ID,
3417 PRIORITY,
3418 ATTRIBUTE_CATEGORY,
3419 ATTRIBUTE1,
3420 ATTRIBUTE2,
3421 ATTRIBUTE3,
3422 ATTRIBUTE4,
3423 ATTRIBUTE5,
3424 ATTRIBUTE6,
3425 ATTRIBUTE7,
3426 ATTRIBUTE8,
3427 ATTRIBUTE9,
3428 ATTRIBUTE10,
3429 ATTRIBUTE11,
3430 ATTRIBUTE12,
3431 ATTRIBUTE13,
3432 ATTRIBUTE14,
3433 ATTRIBUTE15
3434 ) VALUES
3435 (
3436 AHL_ALTERNATE_RESOURCES_S.NEXTVAL,
3437 1,
3438 G_LAST_UPDATE_DATE,
3439 G_LAST_UPDATED_BY,
3440 G_CREATION_DATE,
3441 G_CREATED_BY,
3442 G_LAST_UPDATE_LOGIN,
3443 p_rt_oper_resource_id,
3444 p_x_alt_resource_tbl(i).aso_resource_id,
3445 p_x_alt_resource_tbl(i).priority,
3446 p_x_alt_resource_tbl(i).attribute_category,
3447 p_x_alt_resource_tbl(i).attribute1,
3448 p_x_alt_resource_tbl(i).attribute2,
3449 p_x_alt_resource_tbl(i).attribute3,
3450 p_x_alt_resource_tbl(i).attribute4,
3451 p_x_alt_resource_tbl(i).attribute5,
3452 p_x_alt_resource_tbl(i).attribute6,
3453 p_x_alt_resource_tbl(i).attribute7,
3454 p_x_alt_resource_tbl(i).attribute8,
3455 p_x_alt_resource_tbl(i).attribute9,
3456 p_x_alt_resource_tbl(i).attribute10,
3457 p_x_alt_resource_tbl(i).attribute11,
3458 p_x_alt_resource_tbl(i).attribute12,
3459 p_x_alt_resource_tbl(i).attribute13,
3460 p_x_alt_resource_tbl(i).attribute14,
3461 p_x_alt_resource_tbl(i).attribute15
3462 ) RETURNING alternate_resource_id INTO l_alt_resource_id ;
3463
3464 -- Set OUT values
3465 p_x_alt_resource_tbl(i).alternate_resource_id := l_alt_resource_id;
3466 p_x_alt_resource_tbl(i).object_version_number := 1;
3467
3468
3469 EXCEPTION
3470 WHEN OTHERS THEN
3471 IF ( SQLCODE = -1 ) THEN
3472 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ALT_RESOURCE_DUP' );
3473 FND_MESSAGE.set_token( 'RECORD', p_x_alt_resource_tbl(i).aso_resource_name );
3474 FND_MSG_PUB.add;
3475 ELSE
3476 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
3477 fnd_log.string
3478 (
3479 fnd_log.level_unexpected,
3480 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME3,
3481 'AHL_ALTERNATE_RESOURCES insert error = ['||SQLERRM||']'
3482 );
3483 END IF;
3484 END IF;
3485 END;
3486
3487 ELSIF ( p_x_alt_resource_tbl(i).dml_operation = 'U' ) THEN
3488
3489 BEGIN
3490 -- Update the record
3491 UPDATE AHL_ALTERNATE_RESOURCES SET
3492 object_version_number = object_version_number + 1,
3493 last_update_date = G_LAST_UPDATE_DATE,
3494 last_updated_by = G_LAST_UPDATED_BY,
3495 last_update_login = G_LAST_UPDATE_LOGIN,
3496 aso_resource_id = p_x_alt_resource_tbl(i).aso_resource_id,
3497 priority = p_x_alt_resource_tbl(i).priority,
3498 attribute_category = p_x_alt_resource_tbl(i).attribute_category,
3499 attribute1 = p_x_alt_resource_tbl(i).attribute1,
3500 attribute2 = p_x_alt_resource_tbl(i).attribute2,
3501 attribute3 = p_x_alt_resource_tbl(i).attribute3,
3502 attribute4 = p_x_alt_resource_tbl(i).attribute4,
3503 attribute5 = p_x_alt_resource_tbl(i).attribute5,
3504 attribute6 = p_x_alt_resource_tbl(i).attribute6,
3505 attribute7 = p_x_alt_resource_tbl(i).attribute7,
3506 attribute8 = p_x_alt_resource_tbl(i).attribute8,
3507 attribute9 = p_x_alt_resource_tbl(i).attribute9,
3508 attribute10 = p_x_alt_resource_tbl(i).attribute10,
3509 attribute11 = p_x_alt_resource_tbl(i).attribute11,
3510 attribute12 = p_x_alt_resource_tbl(i).attribute12,
3511 attribute13 = p_x_alt_resource_tbl(i).attribute13,
3512 attribute14 = p_x_alt_resource_tbl(i).attribute14,
3513 attribute15 = p_x_alt_resource_tbl(i).attribute15
3514 WHERE alternate_resource_id = p_x_alt_resource_tbl(i).alternate_resource_id
3515 AND object_version_number = p_x_alt_resource_tbl(i).object_version_number;
3516
3517 -- If the record does not exist, then, abort API.
3518 IF ( SQL%ROWCOUNT = 0 ) THEN
3519 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
3520 FND_MESSAGE.set_token( 'RECORD', p_x_alt_resource_tbl(i).aso_resource_name );
3521 FND_MSG_PUB.add;
3522 END IF;
3523
3524 -- Set OUT values
3525 p_x_alt_resource_tbl(i).object_version_number := p_x_alt_resource_tbl(i).object_version_number + 1;
3526
3527 EXCEPTION
3528 WHEN OTHERS THEN
3529 IF ( SQLCODE = -1 ) THEN
3530 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ALT_RESOURCE_DUP' );
3531 FND_MESSAGE.set_token( 'RECORD', p_x_alt_resource_tbl(i).aso_resource_name );
3532 FND_MSG_PUB.add;
3533 ELSE
3534 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
3535 fnd_log.string
3536 (
3537 fnd_log.level_unexpected,
3538 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME3,
3539 'AHL_ALTERNATE_RESOURCES update error = ['||SQLERRM||']'
3540 );
3541 END IF;
3542 END IF;
3543 END;
3544
3545 ELSIF ( p_x_alt_resource_tbl(i).dml_operation = 'D' ) THEN
3546
3547 -- Delete the record
3548 DELETE FROM AHL_ALTERNATE_RESOURCES
3549 WHERE alternate_resource_id = p_x_alt_resource_tbl(i).alternate_resource_id
3550 AND object_version_number = p_x_alt_resource_tbl(i).object_version_number;
3551
3552 -- If the record does not exist, then, abort API.
3553 IF ( SQL%ROWCOUNT = 0 ) THEN
3554 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
3555 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
3556 FND_MSG_PUB.add;
3557 END IF;
3558 END IF;
3559 END LOOP;
3560
3561 IF G_DEBUG = 'Y' THEN
3562 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after DML operation' );
3563 END IF;
3564
3565 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
3566 l_msg_count := FND_MSG_PUB.count_msg;
3567 IF l_msg_count > 0 THEN
3568 x_msg_count := l_msg_count;
3569 RAISE FND_API.G_EXC_ERROR;
3570 END IF;
3571
3572 -- Perform cross records validations and duplicate records check
3573 validate_alt_records
3574 (
3575 p_rt_oper_resource_id,
3576 l_return_status
3577 );
3578
3579 -- If any severe error occurs, then, abort API.
3580 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3581 RAISE FND_API.G_EXC_ERROR;
3582 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3583 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3584 END IF;
3585
3586 IF G_DEBUG = 'Y' THEN
3587 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME3 || ' : after validate_records' );
3588 END IF;
3589
3590 -- Perform the Commit (if requested)
3591 IF FND_API.to_boolean( p_commit ) THEN
3592 COMMIT WORK;
3593 END IF;
3594
3595 -- Count and Get messages (optional)
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 EXCEPTION
3609 WHEN FND_API.G_EXC_ERROR THEN
3610 ROLLBACK TO PROCESS_ALTERNATE_RESOURCE_PVT;
3611 x_return_status := FND_API.G_RET_STS_ERROR ;
3612 FND_MSG_PUB.count_and_get
3613 (
3614 p_encoded => FND_API.G_FALSE,
3615 p_count => x_msg_count,
3616 p_data => x_msg_data
3617 );
3618
3619 -- Disable debug (if enabled)
3620 IF G_DEBUG = 'Y' THEN
3621 AHL_DEBUG_PUB.disable_debug;
3622 END IF;
3623
3624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3625 ROLLBACK TO PROCESS_ALTERNATE_RESOURCE_PVT;
3626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3627 FND_MSG_PUB.count_and_get
3628 (
3629 p_encoded => FND_API.G_FALSE,
3630 p_count => x_msg_count,
3631 p_data => x_msg_data
3632 );
3633
3634 -- Disable debug (if enabled)
3635 IF G_DEBUG = 'Y' THEN
3636 AHL_DEBUG_PUB.disable_debug;
3637 END IF;
3638
3639 WHEN OTHERS THEN
3640 ROLLBACK TO PROCESS_ALTERNATE_RESOURCE_PVT;
3641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3642 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3643 THEN
3644 FND_MSG_PUB.add_exc_msg
3645 (
3646 p_pkg_name => G_PKG_NAME,
3647 p_procedure_name => G_API_NAME3,
3648 p_error_text => SUBSTRB(SQLERRM,1,240)
3649 );
3650 END IF;
3651 FND_MSG_PUB.count_and_get
3652 (
3653 p_encoded => FND_API.G_FALSE,
3654 p_count => x_msg_count,
3655 p_data => x_msg_data
3656 );
3657
3658 -- Disable debug (if enabled)
3659 IF G_DEBUG = 'Y' THEN
3660 AHL_DEBUG_PUB.disable_debug;
3661 END IF;
3662
3663 END PROCESS_ALTERNATE_RESOURCE;
3664
3665 END AHL_RM_RT_OPER_RESOURCE_PVT;