[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_ASO_RESOURCE_PVT
Source
1 PACKAGE BODY AHL_RM_ASO_RESOURCE_PVT AS
2 /* $Header: AHLVASRB.pls 120.0.12010000.2 2008/10/24 07:21:06 pdoki ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_RM_ASO_RESOURCE_PVT';
5 G_API_NAME VARCHAR2(30) := 'PROCESS_ASO_RESOURCE';
6 G_DEBUG VARCHAR2(1) := NVL(AHL_DEBUG_PUB.is_log_enabled,'N');
7
8 -- Procedure to validate the Inputs of the API
9
10 PROCEDURE validate_api_inputs
11 (
12 p_aso_resource_rec IN aso_resource_rec_type,
13 p_bom_resource_tbl IN bom_resource_tbl_type
14 )
15 IS
16
17 BEGIN
18
19 -- Validate DML Operation
20 IF ( p_aso_resource_rec.dml_operation <> 'D' AND
21 p_aso_resource_rec.dml_operation <> 'U' AND
22 p_aso_resource_rec.dml_operation <> 'C' ) THEN
23 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML' );
24 FND_MESSAGE.set_token( 'FIELD', p_aso_resource_rec.dml_operation );
25 FND_MSG_PUB.add;
26 RAISE FND_API.G_EXC_ERROR;
27 END IF;
28 IF p_bom_resource_tbl.count > 0 THEN
29 FOR i IN p_bom_resource_tbl.FIRST..p_bom_resource_tbl.LAST LOOP
30 IF ( p_bom_resource_tbl(i).dml_operation <> 'D' AND
31 p_bom_resource_tbl(i).dml_operation <> 'U' AND
32 p_bom_resource_tbl(i).dml_operation <> 'C' ) THEN
33 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML' );
34 FND_MESSAGE.set_token( 'FIELD', p_bom_resource_tbl(i).dml_operation );
35 FND_MESSAGE.set_token( 'RECORD', p_bom_resource_tbl(i).bom_resource_code ) ;
36 FND_MSG_PUB.add;
37 RAISE FND_API.G_EXC_ERROR;
38 END IF;
39 END LOOP;
40 END IF;
41 IF ( p_aso_resource_rec.dml_operation = 'C' and p_bom_resource_tbl.count >0 ) THEN
42 FOR i IN p_bom_resource_tbl.FIRST..p_bom_resource_tbl.LAST LOOP
43 IF p_bom_resource_tbl(i).dml_operation <> 'C' THEN
44 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML' );
45 FND_MESSAGE.set_token( 'FIELD', p_bom_resource_tbl(i).dml_operation );
46 FND_MESSAGE.set_token( 'RECORD', p_bom_resource_tbl(i).bom_resource_code ) ;
47 FND_MSG_PUB.add;
48 RAISE FND_API.G_EXC_ERROR;
49 END IF;
50 END LOOP;
51 END IF;
52
53 END validate_api_inputs;
54
55 -- Procedure to Default NULL / G_MISS Values for LOV attributes
56 PROCEDURE clear_lov_attribute_ids
57 (
58 p_x_aso_resource_rec IN OUT NOCOPY aso_resource_rec_type,
59 p_x_bom_resource_tbl IN OUT NOCOPY bom_resource_tbl_type
60 )
61 IS
62 BEGIN
63 IF (p_x_aso_resource_rec.dml_operation <> 'D') THEN
64 IF ( p_x_aso_resource_rec.resource_type IS NULL ) THEN
65 p_x_aso_resource_rec.resource_type_id := NULL;
66 ELSIF ( p_x_aso_resource_rec.resource_type = FND_API.G_MISS_CHAR ) THEN
67 p_x_aso_resource_rec.resource_type_id := FND_API.G_MISS_NUM;
68 END IF;
69 END IF;
70 IF p_x_bom_resource_tbl.count > 0 THEN
71 FOR i IN p_x_bom_resource_tbl.FIRST..p_x_bom_resource_tbl.LAST LOOP
72 IF (p_x_bom_resource_tbl(i).dml_operation <> 'D') THEN
73 IF ( p_x_bom_resource_tbl(i).bom_resource_code IS NULL ) THEN
74 p_x_bom_resource_tbl(i).bom_resource_id := NULL;
75 ELSIF ( p_x_bom_resource_tbl(i).bom_resource_code = FND_API.G_MISS_CHAR ) THEN
76 p_x_bom_resource_tbl(i).bom_resource_id := FND_API.G_MISS_NUM;
77 END IF;
78 END IF;
79 END LOOP;
80 END IF;
81 END clear_lov_attribute_ids;
82
83 -- Procedure to perform Value to ID conversion for appropriate attributes
84 PROCEDURE convert_values_to_ids
85 (
86 p_x_aso_resource_rec IN OUT NOCOPY aso_resource_rec_type,
87 p_x_bom_resource_tbl IN OUT NOCOPY bom_resource_tbl_type
88 )
89 IS
90
91 l_return_status VARCHAR2(1);
92 l_total_return_status VARCHAR2(1);
93 l_msg_data VARCHAR2(2000);
94
95 BEGIN
96 l_total_return_status := FND_API.G_RET_STS_SUCCESS;
97
98 -- Convert / Validate ASO Resource Type
99 IF ( p_x_aso_resource_rec.dml_operation <> 'D') THEN
100 IF ( ( p_x_aso_resource_rec.resource_type_id IS NOT NULL AND
101 p_x_aso_resource_rec.resource_type_id <> FND_API.G_MISS_NUM ) OR
102 ( p_x_aso_resource_rec.resource_type IS NOT NULL AND
103 p_x_aso_resource_rec.resource_type <> FND_API.G_MISS_CHAR ) ) THEN
104
105 AHL_RM_ROUTE_UTIL.validate_mfg_lookup
106 (
107 x_return_status => l_return_status,
108 x_msg_data => l_msg_data,
109 p_lookup_type => 'BOM_RESOURCE_TYPE',
110 p_lookup_meaning => p_x_aso_resource_rec.resource_type,
111 p_x_lookup_code => p_x_aso_resource_rec.resource_type_id
112 );
113
114 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
115 IF ( l_msg_data = 'AHL_COM_INVALID_MFG_LOOKUP' ) THEN
116 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_RES_TYPE' );
117 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
118 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_RES_TYPES' );
119 ELSE
120 FND_MESSAGE.set_name( 'AHL', l_msg_data );
121 END IF;
122
123 IF ( p_x_aso_resource_rec.resource_type IS NULL OR
124 p_x_aso_resource_rec.resource_type = FND_API.G_MISS_CHAR ) THEN
125 FND_MESSAGE.set_token( 'FIELD', p_x_aso_resource_rec.resource_type_id );
126 ELSE
127 FND_MESSAGE.set_token( 'FIELD', p_x_aso_resource_rec.resource_type );
128 END IF;
129
130 FND_MSG_PUB.add;
131 l_total_return_status := FND_API.G_RET_STS_ERROR;
132 END IF;
133 END IF;
134 END IF;
135
136 -- Convert / Validate BOM Resource Code
137 IF p_x_bom_resource_tbl.count > 0 THEN
138 FOR i IN p_x_bom_resource_tbl.FIRST..p_x_bom_resource_tbl.LAST LOOP
139 IF (p_x_bom_resource_tbl(i).dml_operation <> 'D' ) THEN
140 IF ( ( p_x_bom_resource_tbl(i).bom_resource_code IS NOT NULL AND
141 p_x_bom_resource_tbl(i).bom_resource_code <> FND_API.G_MISS_CHAR ) OR
142 ( p_x_bom_resource_tbl(i).bom_resource_id IS NOT NULL AND
143 p_x_bom_resource_tbl(i).bom_resource_id <> FND_API.G_MISS_NUM ) ) THEN
144
145 AHL_RM_ROUTE_UTIL.validate_bom_resource
146 (
147 x_return_status => l_return_status,
148 x_msg_data => l_msg_data,
149 p_bom_resource_code => p_x_bom_resource_tbl(i).bom_resource_code,
150 p_x_bom_resource_id => p_x_bom_resource_tbl(i).bom_resource_id,
151 p_x_bom_org_id => p_x_bom_resource_tbl(i).bom_org_id
152 );
153
154 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
155 FND_MESSAGE.set_name( 'AHL', l_msg_data );
156 IF ( p_x_bom_resource_tbl(i).bom_resource_code IS NULL OR
157 p_x_bom_resource_tbl(i).bom_resource_code = FND_API.G_MISS_CHAR ) THEN
158 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_bom_resource_tbl(i).bom_resource_id ) );
159 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( p_x_bom_resource_tbl(i).bom_resource_id ) );
160 ELSE
161 FND_MESSAGE.set_token( 'FIELD', p_x_bom_resource_tbl(i).bom_resource_code );
162 FND_MESSAGE.set_token( 'RECORD', p_x_bom_resource_tbl(i).bom_resource_code );
163 END IF;
164 FND_MSG_PUB.add;
165 l_total_return_status := FND_API.G_RET_STS_ERROR;
166 END IF;
167 END IF;
168
169 -- pdoki ER 7436910 Begin.
170 -- Convert / Validate BOM resource's department
171 IF ( ( p_x_bom_resource_tbl(i).department_name IS NOT NULL AND
172 p_x_bom_resource_tbl(i).department_name <> FND_API.G_MISS_CHAR ) OR
173 ( p_x_bom_resource_tbl(i).department_id IS NOT NULL AND
174 p_x_bom_resource_tbl(i).department_id <> FND_API.G_MISS_NUM ) ) THEN
175
176 IF (p_x_bom_resource_tbl(i).bom_resource_id IS NULL OR p_x_bom_resource_tbl(i).bom_resource_id = FND_API.G_MISS_NUM)
177 THEN
178 FND_MESSAGE.set_name( 'AHL','AHL_RM_BOM_RES_ID_NULL' );
179 FND_MESSAGE.set_token( 'RECORD',TO_CHAR(i));
180 FND_MSG_PUB.add;
181 l_total_return_status := FND_API.G_RET_STS_ERROR;
182 ELSE
183 AHL_RM_ROUTE_UTIL.validate_bom_res_dep
184 (
185 x_return_status => l_return_status,
186 x_msg_data => l_msg_data,
187 p_bom_resource_id => p_x_bom_resource_tbl(i).bom_resource_id,
188 p_bom_org_id => p_x_bom_resource_tbl(i).bom_org_id,
189 p_bom_department_name => p_x_bom_resource_tbl(i).department_name,
190 p_x_bom_department_id => p_x_bom_resource_tbl(i).department_id
191 );
192
193 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
194 FND_MESSAGE.set_name( 'AHL', l_msg_data );
195 IF ( p_x_bom_resource_tbl(i).department_name IS NULL OR
196 p_x_bom_resource_tbl(i).department_name = FND_API.G_MISS_CHAR ) THEN
197 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( p_x_bom_resource_tbl(i).department_id ) );
198 ELSE
199 FND_MESSAGE.set_token( 'RECORD', p_x_bom_resource_tbl(i).department_name );
200 END IF;
201 FND_MSG_PUB.add;
202 l_total_return_status := FND_API.G_RET_STS_ERROR;
203 END IF;
204 END IF;
205 END IF;
206 -- pdoki ER 7436910 End.
207
208 END IF;
209 END LOOP;
210 IF (l_total_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
211 RAISE FND_API.G_EXC_ERROR;
212 END IF;
213 END IF;
214 END convert_values_to_ids;
215
216 -- Procedure to add Default values for aso_resource attributes
217 PROCEDURE default_attributes
218 (
219 p_x_aso_resource_rec IN OUT NOCOPY aso_resource_rec_type,
220 p_x_bom_resource_tbl IN OUT NOCOPY bom_resource_tbl_type
221 )
222 IS
223
224 BEGIN
225 IF p_x_aso_resource_rec.dml_operation <> 'D' THEN
226 p_x_aso_resource_rec.last_update_date := SYSDATE;
227 p_x_aso_resource_rec.last_updated_by := FND_GLOBAL.user_id;
228 p_x_aso_resource_rec.last_update_login := FND_GLOBAL.login_id;
229 END IF;
230
231 IF ( p_x_aso_resource_rec.dml_operation = 'C' ) THEN
232 p_x_aso_resource_rec.object_version_number := 1;
233 p_x_aso_resource_rec.creation_date := SYSDATE;
234 p_x_aso_resource_rec.created_by := FND_GLOBAL.user_id;
235 END IF;
236 IF p_x_bom_resource_tbl.count > 0 THEN
237 FOR i IN p_x_bom_resource_tbl.FIRST..p_x_bom_resource_tbl.LAST LOOP
238 IF ( p_x_bom_resource_tbl(i).dml_operation <> 'D' ) THEN
239 p_x_bom_resource_tbl(i).last_update_date := SYSDATE;
240 p_x_bom_resource_tbl(i).last_updated_by := FND_GLOBAL.user_id;
241 p_x_bom_resource_tbl(i).last_update_login := FND_GLOBAL.login_id;
242 END IF;
243
244 IF ( p_x_bom_resource_tbl(i).dml_operation = 'C' ) THEN
245 p_x_bom_resource_tbl(i).object_version_number := 1;
246 p_x_bom_resource_tbl(i).creation_date := SYSDATE;
247 p_x_bom_resource_tbl(i).created_by := FND_GLOBAL.user_id;
248 END IF;
249 END LOOP;
250 END IF;
251 END default_attributes;
252
253 -- Procedure to validate individual aso_resource attributes
254 PROCEDURE validate_attributes
255 (
256 p_aso_resource_rec IN aso_resource_rec_type,
257 p_bom_resource_tbl IN bom_resource_tbl_type
258 )
259 IS
260
261 BEGIN
262
263 IF ( p_aso_resource_rec.dml_operation = 'C' OR
264 p_aso_resource_rec.dml_operation = 'U' ) THEN
265 -- Check if the mandatory resource_type column contains a value.
266 IF ( (p_aso_resource_rec.resource_type_id IS NULL OR
267 p_aso_resource_rec.resource_type_id = FND_API.G_MISS_NUM) AND
268 p_aso_resource_rec.dml_operation <> 'U' ) THEN
269 FND_MESSAGE.set_name( 'AHL','AHL_RM_ASO_RES_TYPE_NULL' );
270 FND_MSG_PUB.add;
271 RAISE FND_API.G_EXC_ERROR;
272 END IF;
273
274 -- Check if the mandatory name column contains a value.
275 IF ( p_aso_resource_rec.name IS NULL OR
276 p_aso_resource_rec.name = FND_API.G_MISS_CHAR ) THEN
277 FND_MESSAGE.set_name( 'AHL','AHL_RM_ASO_RES_NAME_NULL' );
278 FND_MSG_PUB.add;
279 RAISE FND_API.G_EXC_ERROR;
280 END IF;
281
282 -- Check if the mandatory description column contains a value.
283 IF ( p_aso_resource_rec.description IS NULL OR
284 p_aso_resource_rec.description = FND_API.G_MISS_CHAR ) THEN
285 FND_MESSAGE.set_name( 'AHL','AHL_RM_ASO_RES_DESC_NULL' );
286 FND_MSG_PUB.add;
287 RAISE FND_API.G_EXC_ERROR;
288 END IF;
289 END IF;
290
291 -- Check if the mandatory bom_resource_id column in details table contains a value.
292 IF p_bom_resource_tbl.count > 0 THEN
293 FOR i IN p_bom_resource_tbl.FIRST..p_bom_resource_tbl.LAST LOOP
294 IF ( p_bom_resource_tbl(i).dml_operation = 'C' OR
295 p_bom_resource_tbl(i).dml_operation = 'U' ) THEN
296
297 IF (p_bom_resource_tbl(i).bom_resource_id IS NULL OR
298 p_bom_resource_tbl(i).bom_resource_id = FND_API.G_MISS_NUM) THEN
299 FND_MESSAGE.set_name( 'AHL','AHL_RM_BOM_RES_ID_NULL' );
300 FND_MESSAGE.set_token( 'RECORD',TO_CHAR(i));
301 FND_MSG_PUB.add;
302 RAISE FND_API.G_EXC_ERROR;
303 END IF;
304 IF (p_bom_resource_tbl(i).bom_org_id IS NULL OR
305 p_bom_resource_tbl(i).bom_org_id = FND_API.G_MISS_NUM) THEN
306 FND_MESSAGE.set_name( 'AHL','AHL_RM_BOM_ORG_ID_NULL' );
307 FND_MESSAGE.set_token( 'RECORD',p_bom_resource_tbl(i).bom_resource_code);
308 FND_MSG_PUB.add;
309 RAISE FND_API.G_EXC_ERROR;
310 END IF;
311 END IF;
312 END LOOP;
313 END IF;
314 END validate_attributes;
315
316 -- Procedure to add Default values for missing attributes (CREATE)
317 PROCEDURE default_miss_aso_attributes
318 (
319 p_x_aso_resource_rec IN OUT NOCOPY aso_resource_rec_type
320 )
321 IS
322
323 BEGIN
324
325 -- Convert G_MISS values to NULL
326 IF p_x_aso_resource_rec.dml_operation = 'C' THEN
327 IF ( p_x_aso_resource_rec.resource_type_id = FND_API.G_MISS_NUM ) THEN
328 p_x_aso_resource_rec.resource_type_id := null;
329 END IF;
330
331 IF ( p_x_aso_resource_rec.name = FND_API.G_MISS_CHAR ) THEN
332 p_x_aso_resource_rec.name := null;
333 END IF;
334
338
335 IF ( p_x_aso_resource_rec.description = FND_API.G_MISS_CHAR ) THEN
336 p_x_aso_resource_rec.description := null;
337 END IF;
339 IF ( p_x_aso_resource_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
340 p_x_aso_resource_rec.attribute_category := null;
341 END IF;
342
343 IF ( p_x_aso_resource_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
344 p_x_aso_resource_rec.attribute1 := null;
345 END IF;
346
347 IF ( p_x_aso_resource_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
348 p_x_aso_resource_rec.attribute2 := null;
349 END IF;
350
351 IF ( p_x_aso_resource_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
352 p_x_aso_resource_rec.attribute3 := null;
353 END IF;
354
355 IF ( p_x_aso_resource_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
356 p_x_aso_resource_rec.attribute4 := null;
357 END IF;
358
359 IF ( p_x_aso_resource_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
360 p_x_aso_resource_rec.attribute5 := null;
361 END IF;
362
363 IF ( p_x_aso_resource_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
364 p_x_aso_resource_rec.attribute6 := null;
365 END IF;
366
367 IF ( p_x_aso_resource_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
368 p_x_aso_resource_rec.attribute7 := null;
369 END IF;
370
371 IF ( p_x_aso_resource_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
372 p_x_aso_resource_rec.attribute8 := null;
373 END IF;
374
375 IF ( p_x_aso_resource_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
376 p_x_aso_resource_rec.attribute9 := null;
377 END IF;
378
379 IF ( p_x_aso_resource_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
380 p_x_aso_resource_rec.attribute10 := null;
381 END IF;
382
383 IF ( p_x_aso_resource_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
384 p_x_aso_resource_rec.attribute11 := null;
385 END IF;
386
387 IF ( p_x_aso_resource_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
388 p_x_aso_resource_rec.attribute12 := null;
389 END IF;
390
391 IF ( p_x_aso_resource_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
392 p_x_aso_resource_rec.attribute13 := null;
393 END IF;
394
395 IF ( p_x_aso_resource_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
396 p_x_aso_resource_rec.attribute14 := null;
397 END IF;
398
399 IF ( p_x_aso_resource_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
400 p_x_aso_resource_rec.attribute15 := null;
401 END IF;
402 END IF;
403 END default_miss_aso_attributes;
404
405 -- Procedure to add Default values for missing attributes (CREATE)
406 PROCEDURE default_miss_bom_attributes
407 (
408 p_x_bom_resource_rec IN OUT NOCOPY bom_resource_rec_type
409 )
410 IS
411
412 BEGIN
413 IF ( p_x_bom_resource_rec.dml_operation = 'C') THEN
414 IF ( p_x_bom_resource_rec.bom_resource_id = FND_API.G_MISS_NUM ) THEN
415 p_x_bom_resource_rec.bom_resource_id := null;
416 END IF;
417
418 IF ( p_x_bom_resource_rec.bom_org_id = FND_API.G_MISS_NUM ) THEN
419 p_x_bom_resource_rec.bom_org_id := null;
420 END IF;
421
422 --pdoki ER 7436910 Begin.
423 IF ( p_x_bom_resource_rec.department_id = FND_API.G_MISS_NUM ) THEN
424 p_x_bom_resource_rec.department_id := null;
425 END IF;
426 --pdoki ER 7436910 End.
427
428 IF ( p_x_bom_resource_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
429 p_x_bom_resource_rec.attribute_category := null;
430 END IF;
431
432 IF ( p_x_bom_resource_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
433 p_x_bom_resource_rec.attribute1 := null;
434 END IF;
435
436 IF ( p_x_bom_resource_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
437 p_x_bom_resource_rec.attribute2 := null;
438 END IF;
439
440 IF ( p_x_bom_resource_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
441 p_x_bom_resource_rec.attribute3 := null;
442 END IF;
443
444 IF ( p_x_bom_resource_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
445 p_x_bom_resource_rec.attribute4 := null;
446 END IF;
447
448 IF ( p_x_bom_resource_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
449 p_x_bom_resource_rec.attribute5 := null;
450 END IF;
451
452 IF ( p_x_bom_resource_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
453 p_x_bom_resource_rec.attribute6 := null;
454 END IF;
455
456 IF ( p_x_bom_resource_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
457 p_x_bom_resource_rec.attribute7 := null;
458 END IF;
459
460 IF ( p_x_bom_resource_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
461 p_x_bom_resource_rec.attribute8 := null;
462 END IF;
463
464 IF ( p_x_bom_resource_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
465 p_x_bom_resource_rec.attribute9 := null;
466 END IF;
467
468 IF ( p_x_bom_resource_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
469 p_x_bom_resource_rec.attribute10 := null;
470 END IF;
471
472 IF ( p_x_bom_resource_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
473 p_x_bom_resource_rec.attribute11 := null;
474 END IF;
475
476 IF ( p_x_bom_resource_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
477 p_x_bom_resource_rec.attribute12 := null;
481 p_x_bom_resource_rec.attribute13 := null;
478 END IF;
479
480 IF ( p_x_bom_resource_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
482 END IF;
483
484 IF ( p_x_bom_resource_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
485 p_x_bom_resource_rec.attribute14 := null;
486 END IF;
487
488 IF ( p_x_bom_resource_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
489 p_x_bom_resource_rec.attribute15 := null;
490 END IF;
491 END IF;
492 END default_miss_bom_attributes;
493
494 -- Procedure to add Default values for unchanged attributes (UPDATE)
495 PROCEDURE default_unc_aso_attributes
496 (
497 p_x_aso_resource_rec IN OUT NOCOPY aso_resource_rec_type
498 )
499 IS
500
501 l_old_aso_resource_rec aso_resource_rec_type;
502
503 CURSOR get_old_aso_rec ( c_resource_id NUMBER )
504 IS
505 SELECT resource_id,
506 resource_type_id,
507 name,
508 description,
509 attribute_category,
510 attribute1,
511 attribute2,
512 attribute3,
513 attribute4,
514 attribute5,
515 attribute6,
516 attribute7,
517 attribute8,
518 attribute9,
519 attribute10,
520 attribute11,
521 attribute12,
522 attribute13,
523 attribute14,
524 attribute15
525 FROM AHL_RESOURCES
526 WHERE resource_id = c_resource_id;
527
528 BEGIN
529
530 -- Get the old record from AHL_RESOURCES.
531 OPEN get_old_aso_rec( p_x_aso_resource_rec.resource_id );
532
533 FETCH get_old_aso_rec INTO
534 l_old_aso_resource_rec.resource_id,
535 l_old_aso_resource_rec.resource_type_id,
536 l_old_aso_resource_rec.name,
537 l_old_aso_resource_rec.description,
538 l_old_aso_resource_rec.attribute_category,
539 l_old_aso_resource_rec.attribute1,
540 l_old_aso_resource_rec.attribute2,
541 l_old_aso_resource_rec.attribute3,
542 l_old_aso_resource_rec.attribute4,
543 l_old_aso_resource_rec.attribute5,
544 l_old_aso_resource_rec.attribute6,
545 l_old_aso_resource_rec.attribute7,
546 l_old_aso_resource_rec.attribute8,
547 l_old_aso_resource_rec.attribute9,
548 l_old_aso_resource_rec.attribute10,
549 l_old_aso_resource_rec.attribute11,
550 l_old_aso_resource_rec.attribute12,
551 l_old_aso_resource_rec.attribute13,
552 l_old_aso_resource_rec.attribute14,
553 l_old_aso_resource_rec.attribute15;
554
555 IF get_old_aso_rec%NOTFOUND THEN
556 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_ASO_REC' );
557 FND_MSG_PUB.add;
558 CLOSE get_old_aso_rec;
559 RAISE FND_API.G_EXC_ERROR;
560 END IF;
561
562 CLOSE get_old_aso_rec;
563
564 -- Convert G_MISS values to NULL and NULL values to Old values
565
566 IF ( p_x_aso_resource_rec.name = FND_API.G_MISS_CHAR ) THEN
567 p_x_aso_resource_rec.name := null;
568 ELSIF ( p_x_aso_resource_rec.name IS NULL ) THEN
569 p_x_aso_resource_rec.name := l_old_aso_resource_rec.name;
570 END IF;
571
572 IF ( p_x_aso_resource_rec.description = FND_API.G_MISS_CHAR ) THEN
573 p_x_aso_resource_rec.description := null;
574 ELSIF ( p_x_aso_resource_rec.description IS NULL ) THEN
575 p_x_aso_resource_rec.description := l_old_aso_resource_rec.description;
576 END IF;
577
578 IF ( p_x_aso_resource_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
579 p_x_aso_resource_rec.attribute_category := null;
580 ELSIF ( p_x_aso_resource_rec.attribute_category IS NULL ) THEN
581 p_x_aso_resource_rec.attribute_category := l_old_aso_resource_rec.attribute_category;
582 END IF;
583
584 IF ( p_x_aso_resource_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
585 p_x_aso_resource_rec.attribute1 := null;
586 ELSIF ( p_x_aso_resource_rec.attribute1 IS NULL ) THEN
587 p_x_aso_resource_rec.attribute1 := l_old_aso_resource_rec.attribute1;
588 END IF;
589
590 IF ( p_x_aso_resource_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
591 p_x_aso_resource_rec.attribute2 := null;
592 ELSIF ( p_x_aso_resource_rec.attribute2 IS NULL ) THEN
593 p_x_aso_resource_rec.attribute2 := l_old_aso_resource_rec.attribute2;
594 END IF;
595
596 IF ( p_x_aso_resource_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
597 p_x_aso_resource_rec.attribute3 := null;
598 ELSIF ( p_x_aso_resource_rec.attribute3 IS NULL ) THEN
599 p_x_aso_resource_rec.attribute3 := l_old_aso_resource_rec.attribute3;
600 END IF;
601
602 IF ( p_x_aso_resource_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
603 p_x_aso_resource_rec.attribute4 := null;
604 ELSIF ( p_x_aso_resource_rec.attribute4 IS NULL ) THEN
605 p_x_aso_resource_rec.attribute4 := l_old_aso_resource_rec.attribute4;
606 END IF;
607
608 IF ( p_x_aso_resource_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
609 p_x_aso_resource_rec.attribute5 := null;
610 ELSIF ( p_x_aso_resource_rec.attribute5 IS NULL ) THEN
611 p_x_aso_resource_rec.attribute5 := l_old_aso_resource_rec.attribute5;
612 END IF;
613
614 IF ( p_x_aso_resource_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
618 END IF;
615 p_x_aso_resource_rec.attribute6 := null;
616 ELSIF ( p_x_aso_resource_rec.attribute6 IS NULL ) THEN
617 p_x_aso_resource_rec.attribute6 := l_old_aso_resource_rec.attribute6;
619
620 IF ( p_x_aso_resource_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
621 p_x_aso_resource_rec.attribute7 := null;
622 ELSIF ( p_x_aso_resource_rec.attribute7 IS NULL ) THEN
623 p_x_aso_resource_rec.attribute7 := l_old_aso_resource_rec.attribute7;
624 END IF;
625
626 IF ( p_x_aso_resource_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
627 p_x_aso_resource_rec.attribute8 := null;
628 ELSIF ( p_x_aso_resource_rec.attribute8 IS NULL ) THEN
629 p_x_aso_resource_rec.attribute8 := l_old_aso_resource_rec.attribute8;
630 END IF;
631
632 IF ( p_x_aso_resource_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
633 p_x_aso_resource_rec.attribute9 := null;
634 ELSIF ( p_x_aso_resource_rec.attribute9 IS NULL ) THEN
635 p_x_aso_resource_rec.attribute9 := l_old_aso_resource_rec.attribute9;
636 END IF;
637
638 IF ( p_x_aso_resource_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
639 p_x_aso_resource_rec.attribute10 := null;
640 ELSIF ( p_x_aso_resource_rec.attribute10 IS NULL ) THEN
641 p_x_aso_resource_rec.attribute10 := l_old_aso_resource_rec.attribute10;
642 END IF;
643
644 IF ( p_x_aso_resource_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
645 p_x_aso_resource_rec.attribute11 := null;
646 ELSIF ( p_x_aso_resource_rec.attribute11 IS NULL ) THEN
647 p_x_aso_resource_rec.attribute11 := l_old_aso_resource_rec.attribute11;
648 END IF;
649
650 IF ( p_x_aso_resource_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
651 p_x_aso_resource_rec.attribute12 := null;
652 ELSIF ( p_x_aso_resource_rec.attribute12 IS NULL ) THEN
653 p_x_aso_resource_rec.attribute12 := l_old_aso_resource_rec.attribute12;
654 END IF;
655
656 IF ( p_x_aso_resource_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
657 p_x_aso_resource_rec.attribute13 := null;
658 ELSIF ( p_x_aso_resource_rec.attribute13 IS NULL ) THEN
659 p_x_aso_resource_rec.attribute13 := l_old_aso_resource_rec.attribute13;
660 END IF;
661
662 IF ( p_x_aso_resource_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
663 p_x_aso_resource_rec.attribute14 := null;
664 ELSIF ( p_x_aso_resource_rec.attribute14 IS NULL ) THEN
665 p_x_aso_resource_rec.attribute14 := l_old_aso_resource_rec.attribute14;
666 END IF;
667
668 IF ( p_x_aso_resource_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
669 p_x_aso_resource_rec.attribute15 := null;
670 ELSIF ( p_x_aso_resource_rec.attribute15 IS NULL ) THEN
671 p_x_aso_resource_rec.attribute15 := l_old_aso_resource_rec.attribute15;
672 END IF;
673
674 END default_unc_aso_attributes;
675
676 -- Procedure to add Default values for unchanged attributes (UPDATE)
677 PROCEDURE default_unc_bom_attributes
678 (p_x_bom_resource_rec IN OUT NOCOPY bom_resource_rec_type)
679 IS
680 l_old_bom_resource_rec bom_resource_rec_type;
681 CURSOR get_old_bom_rec ( c_resource_mapping_id NUMBER )
682 IS
683 SELECT bom_resource_id,
684 bom_org_id,
685 department_id, --pdoki ER 7436910
686 attribute_category,
687 attribute1,
688 attribute2,
689 attribute3,
690 attribute4,
691 attribute5,
692 attribute6,
693 attribute7,
694 attribute8,
695 attribute9,
696 attribute10,
697 attribute11,
698 attribute12,
699 attribute13,
700 attribute14,
701 attribute15
702 FROM AHL_RESOURCE_MAPPINGS
703 WHERE resource_mapping_id = c_resource_mapping_id;
704
705 BEGIN
706
707 -- Get the old record from AHL_RESOURCES.
708 OPEN get_old_bom_rec( p_x_bom_resource_rec.resource_mapping_id );
709
710 FETCH get_old_bom_rec INTO
711 l_old_bom_resource_rec.bom_resource_id,
712 l_old_bom_resource_rec.bom_org_id,
713 l_old_bom_resource_rec.department_id,--pdoki ER 7436910
714 l_old_bom_resource_rec.attribute_category,
715 l_old_bom_resource_rec.attribute1,
716 l_old_bom_resource_rec.attribute2,
717 l_old_bom_resource_rec.attribute3,
718 l_old_bom_resource_rec.attribute4,
719 l_old_bom_resource_rec.attribute5,
720 l_old_bom_resource_rec.attribute6,
721 l_old_bom_resource_rec.attribute7,
722 l_old_bom_resource_rec.attribute8,
723 l_old_bom_resource_rec.attribute9,
724 l_old_bom_resource_rec.attribute10,
725 l_old_bom_resource_rec.attribute11,
726 l_old_bom_resource_rec.attribute12,
727 l_old_bom_resource_rec.attribute13,
728 l_old_bom_resource_rec.attribute14,
729 l_old_bom_resource_rec.attribute15;
730
731 IF get_old_bom_rec%NOTFOUND THEN
732 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_MAPPING_REC' );
733 FND_MESSAGE.set_token( 'RECORD', p_x_bom_resource_rec.bom_resource_code);
734 FND_MSG_PUB.add;
735 CLOSE get_old_bom_rec;
736 RAISE FND_API.G_EXC_ERROR;
737 END IF;
738
739 CLOSE get_old_bom_rec;
740
741 -- Convert G_MISS values to NULL and NULL values to Old values
742 IF ( p_x_bom_resource_rec.bom_resource_id = FND_API.G_MISS_NUM ) THEN
746 END IF;
743 p_x_bom_resource_rec.bom_resource_id := null;
744 ELSIF ( p_x_bom_resource_rec.bom_resource_id IS NULL ) THEN
745 p_x_bom_resource_rec.bom_resource_id := l_old_bom_resource_rec.bom_resource_id;
747
748 IF ( p_x_bom_resource_rec.bom_org_id = FND_API.G_MISS_NUM ) THEN
749 p_x_bom_resource_rec.bom_org_id := null;
750 ELSIF ( p_x_bom_resource_rec.bom_org_id IS NULL ) THEN
751 p_x_bom_resource_rec.bom_org_id := l_old_bom_resource_rec.bom_org_id;
752 END IF;
753
754 IF ( p_x_bom_resource_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
755 p_x_bom_resource_rec.attribute_category := null;
756 ELSIF ( p_x_bom_resource_rec.attribute_category IS NULL ) THEN
757 p_x_bom_resource_rec.attribute_category := l_old_bom_resource_rec.attribute_category;
758 END IF;
759
760 IF ( p_x_bom_resource_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
761 p_x_bom_resource_rec.attribute1 := null;
762 ELSIF ( p_x_bom_resource_rec.attribute1 IS NULL ) THEN
763 p_x_bom_resource_rec.attribute1 := l_old_bom_resource_rec.attribute1;
764 END IF;
765
766 IF ( p_x_bom_resource_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
767 p_x_bom_resource_rec.attribute2 := null;
768 ELSIF ( p_x_bom_resource_rec.attribute2 IS NULL ) THEN
769 p_x_bom_resource_rec.attribute2 := l_old_bom_resource_rec.attribute2;
770 END IF;
771
772 IF ( p_x_bom_resource_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
773 p_x_bom_resource_rec.attribute3 := null;
774 ELSIF ( p_x_bom_resource_rec.attribute3 IS NULL ) THEN
775 p_x_bom_resource_rec.attribute3 := l_old_bom_resource_rec.attribute3;
776 END IF;
777
778 IF ( p_x_bom_resource_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
779 p_x_bom_resource_rec.attribute4 := null;
780 ELSIF ( p_x_bom_resource_rec.attribute4 IS NULL ) THEN
781 p_x_bom_resource_rec.attribute4 := l_old_bom_resource_rec.attribute4;
782 END IF;
783
784 IF ( p_x_bom_resource_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
785 p_x_bom_resource_rec.attribute5 := null;
786 ELSIF ( p_x_bom_resource_rec.attribute5 IS NULL ) THEN
787 p_x_bom_resource_rec.attribute5 := l_old_bom_resource_rec.attribute5;
788 END IF;
789
790 IF ( p_x_bom_resource_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
791 p_x_bom_resource_rec.attribute6 := null;
792 ELSIF ( p_x_bom_resource_rec.attribute6 IS NULL ) THEN
793 p_x_bom_resource_rec.attribute6 := l_old_bom_resource_rec.attribute6;
794 END IF;
795
796 IF ( p_x_bom_resource_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
797 p_x_bom_resource_rec.attribute7 := null;
798 ELSIF ( p_x_bom_resource_rec.attribute7 IS NULL ) THEN
799 p_x_bom_resource_rec.attribute7 := l_old_bom_resource_rec.attribute7;
800 END IF;
801
802 IF ( p_x_bom_resource_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
803 p_x_bom_resource_rec.attribute8 := null;
804 ELSIF ( p_x_bom_resource_rec.attribute8 IS NULL ) THEN
805 p_x_bom_resource_rec.attribute8 := l_old_bom_resource_rec.attribute8;
806 END IF;
807
808 IF ( p_x_bom_resource_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
809 p_x_bom_resource_rec.attribute9 := null;
810 ELSIF ( p_x_bom_resource_rec.attribute9 IS NULL ) THEN
811 p_x_bom_resource_rec.attribute9 := l_old_bom_resource_rec.attribute9;
812 END IF;
813
814 IF ( p_x_bom_resource_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
815 p_x_bom_resource_rec.attribute10 := null;
816 ELSIF ( p_x_bom_resource_rec.attribute10 IS NULL ) THEN
817 p_x_bom_resource_rec.attribute10 := l_old_bom_resource_rec.attribute10;
818 END IF;
819
820 IF ( p_x_bom_resource_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
821 p_x_bom_resource_rec.attribute11 := null;
822 ELSIF ( p_x_bom_resource_rec.attribute11 IS NULL ) THEN
823 p_x_bom_resource_rec.attribute11 := l_old_bom_resource_rec.attribute11;
824 END IF;
825
826 IF ( p_x_bom_resource_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
827 p_x_bom_resource_rec.attribute12 := null;
828 ELSIF ( p_x_bom_resource_rec.attribute12 IS NULL ) THEN
829 p_x_bom_resource_rec.attribute12 := l_old_bom_resource_rec.attribute12;
830 END IF;
831
832 IF ( p_x_bom_resource_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
833 p_x_bom_resource_rec.attribute13 := null;
834 ELSIF ( p_x_bom_resource_rec.attribute13 IS NULL ) THEN
835 p_x_bom_resource_rec.attribute13 := l_old_bom_resource_rec.attribute13;
836 END IF;
837
838 IF ( p_x_bom_resource_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
839 p_x_bom_resource_rec.attribute14 := null;
840 ELSIF ( p_x_bom_resource_rec.attribute14 IS NULL ) THEN
841 p_x_bom_resource_rec.attribute14 := l_old_bom_resource_rec.attribute14;
842 END IF;
843
844 IF ( p_x_bom_resource_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
845 p_x_bom_resource_rec.attribute15 := null;
846 ELSIF ( p_x_bom_resource_rec.attribute15 IS NULL ) THEN
847 p_x_bom_resource_rec.attribute15 := l_old_bom_resource_rec.attribute15;
848 END IF;
849
850 END default_unc_bom_attributes;
851
852 -- Procedure to cross fields record validation in details table
853 PROCEDURE validate_bom_record
854 (
855 p_aso_resource_rec IN aso_resource_rec_type,
856 p_bom_resource_tbl IN bom_resource_tbl_type
857 )
858 IS
859
860 CURSOR get_bom_rec ( c_bom_resource_id NUMBER)
861 IS
862 SELECT resource_type, disable_date
863 FROM BOM_RESOURCES
867 l_res_type NUMBER;
864 WHERE resource_id = c_bom_resource_id;
865
866 l_get_bom_rec get_bom_rec%ROWTYPE;
868 l_return_status VARCHAR2(1);
869
870 BEGIN
871 l_return_status := FND_API.G_RET_STS_SUCCESS;
872 IF p_bom_resource_tbl.count > 0 THEN
873 FOR i IN p_bom_resource_tbl.FIRST..p_bom_resource_tbl.LAST LOOP
874 IF p_bom_resource_tbl(i).dml_operation <> 'D' THEN
875 OPEN get_bom_rec(p_bom_resource_tbl(i).bom_resource_id);
876 FETCH get_bom_rec INTO l_get_bom_rec;
877 IF get_bom_rec%NOTFOUND THEN
878 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_BOM_RES_REC' );
879 FND_MESSAGE.set_token( 'RECORD', p_bom_resource_tbl(i).bom_resource_code);
880 FND_MSG_PUB.add;
881 l_return_status := FND_API.G_RET_STS_ERROR;
882 ELSIF trunc(nvl(l_get_bom_rec.disable_date,sysdate))<trunc(sysdate) THEN
883 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_BOM_RES_OUTDATED' );
884 FND_MESSAGE.set_token( 'RECORD', p_bom_resource_tbl(i).bom_resource_code);
885 FND_MSG_PUB.add;
886 l_return_status := FND_API.G_RET_STS_ERROR;
887 ELSIF (l_get_bom_rec.resource_type <> p_aso_resource_rec.resource_type_id
888 OR l_get_bom_rec.resource_type IS NULL) THEN
889 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_BOM_RES_TYPE_NOT_MATCH' );
890 FND_MESSAGE.set_token( 'RECORD', p_bom_resource_tbl(i).bom_resource_code);
891 FND_MSG_PUB.add;
892 l_return_status := FND_API.G_RET_STS_ERROR;
893 END IF;
894 CLOSE get_bom_rec;
895 END IF;
896 END LOOP;
897 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
898 RAISE FND_API.G_EXC_ERROR;
899 END IF;
900 END IF;
901 END validate_bom_record;
902
903 -- Procedure to perform cross records validation and duplicate checks in master table
904 PROCEDURE validate_aso_records
905 (
906 p_aso_resource_rec IN aso_resource_rec_type
907 )
908 IS
909
910 CURSOR get_dup_rec ( c_resource_id NUMBER, c_name VARCHAR2)
911 IS
912 SELECT name
913 FROM AHL_RESOURCES
914 WHERE UPPER(TRIM(NAME)) = UPPER(TRIM(c_name))
915 AND (resource_id <> c_resource_id
916 OR c_resource_id IS NULL);
917
918 l_name get_dup_rec%ROWTYPE;
919
920 BEGIN
921
922 -- Check whether any duplicate aso_resource records for the given object_ID
923 OPEN get_dup_rec( p_aso_resource_rec.resource_id, p_aso_resource_rec.NAME);
924 FETCH get_dup_rec INTO l_name;
925 IF ( get_dup_rec%FOUND ) THEN
926 CLOSE get_dup_rec;
927 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ASO_RESOURCE_DUP' );
928 FND_MSG_PUB.add;
929 RAISE FND_API.G_EXC_ERROR;
930 ELSE
931 CLOSE get_dup_rec;
932 END IF;
933
934 END validate_aso_records;
935
936 -- Procedure to perform cross records validation in details table
937 PROCEDURE validate_records
938 (
939 p_aso_resource_rec IN aso_resource_rec_type,
940 p_bom_resource_tbl IN bom_resource_tbl_type
941 )
942 IS
943
944 BEGIN
945 IF (p_aso_resource_rec.dml_operation = 'C' AND
946 p_bom_resource_tbl.COUNT = 0) THEN
947 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_BOM_RES_COUNT_ZERO' );
948 FND_MSG_PUB.add;
949 IF G_DEBUG = 'Y' THEN
950 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Inside validate_records procedure');
951 END IF;
952 RAISE FND_API.G_EXC_ERROR;
953 END IF;
954 END validate_records;
955
956 -- Procedure to perform cross records validation in details table after DML Operations
957 PROCEDURE validate_bom_records
958 (
959 p_aso_resource_id IN NUMBER
960 )
961 IS
962 CURSOR get_bom_res_num (c_aso_resource_id number) IS
963 SELECT count(bom_resource_id)
964 FROM ahl_resource_mappings
965 WHERE aso_resource_id = c_aso_resource_id;
966 CURSOR get_bom_res_org (c_aso_resource_id number) IS
967 SELECT count(bom_resource_id)
968 FROM ahl_resource_mappings
969 WHERE aso_resource_id = c_aso_resource_id
970 GROUP BY aso_resource_id, bom_org_id
971 HAVING count(bom_resource_id) > 1;
972 CURSOR get_bom_res_dup (c_aso_resource_id number) IS
973 SELECT bom_resource_id
974 FROM ahl_resource_mappings
975 WHERE aso_resource_id = c_aso_resource_id
976 GROUP BY aso_resource_id, bom_resource_id, bom_org_id
977 HAVING count(resource_mapping_id) >1;
978
979 --pdoki ER 7436910 Begin.
980 CURSOR get_rt_oper_res_obj (c_aso_resource_id number) IS
981 SELECT DISTINCT object_id, association_type_code
982 FROM ahl_rt_oper_resources
983 WHERE aso_resource_id = c_aso_resource_id;
984 --checking dept conflicts among primary resources
985 CURSOR get_dept_conflicts ( c_object_id NUMBER, c_association_type_code VARCHAR2 )
986 IS
987 SELECT 'X'
988 FROM ahl_resource_mappings
989 WHERE DEPARTMENT_ID IS NOT NULL
990 AND aso_resource_id in
991 (SELECT ASO_RESOURCE_ID
992 FROM ahl_rt_oper_resources
993 WHERE object_id = c_object_id
994 AND ASSOCIATION_TYPE_CODE = c_association_type_code
995 )
996 GROUP BY bom_org_id
997 HAVING count(DISTINCT DEPARTMENT_ID) > 1;
998
1002 SELECT 'X'
999 --checking dept conflicts b/w the primary resource and alt resources of OTHER primary resources.here the resource is a primary resource(in rt_oper_resources table)
1000 CURSOR get_dept_conflicts_alt_res ( c_object_id NUMBER, c_association_type_code VARCHAR2, c_rt_oper_res_id NUMBER)
1001 IS
1003 FROM ahl_resource_mappings
1004 WHERE DEPARTMENT_ID IS NOT NULL
1005 AND aso_resource_id in
1006 (SELECT ALTR.ASO_RESOURCE_ID
1007 FROM ahl_alternate_resources ALTR,
1008 (SELECT RT_OPER_RESOURCE_ID
1009 FROM ahl_rt_oper_resources
1010 WHERE RT_OPER_RESOURCE_ID <> c_rt_oper_res_id
1011 AND ASSOCIATION_TYPE_CODE = c_association_type_code
1012 AND OBJECT_ID = c_object_id
1013 )
1014 ROR
1015 WHERE ROR.RT_OPER_RESOURCE_ID = ALTR.rt_oper_resource_id
1016 )
1017 OR aso_resource_id =
1018 (SELECT aso_resource_id
1019 FROM ahl_rt_oper_resources
1020 WHERE RT_OPER_RESOURCE_ID = c_rt_oper_res_id
1021 )
1022 GROUP BY bom_org_id
1023 HAVING count(DISTINCT DEPARTMENT_ID) > 1;
1024
1025 CURSOR get_rt_oper_res_ids ( c_object_id NUMBER, c_association_type_code VARCHAR2)
1026 IS
1027 SELECT RT_OPER_RESOURCE_ID
1028 FROM AHL_RT_OPER_RESOURCES
1029 WHERE ASSOCIATION_TYPE_CODE = c_association_type_code
1030 AND OBJECT_ID = c_object_id;
1031
1032 --checks for conflicts b/w the alternate resource and other primary resources. here the resource is an alternate resource(in alternate_resources table)
1033 CURSOR get_dept_conflicts_alt_pri ( c_object_id NUMBER, c_association_type_code VARCHAR2, c_aso_res_id NUMBER, c_alt_res_id NUMBER)
1034 IS
1035 SELECT 'X'
1036 FROM ahl_resource_mappings
1037 WHERE DEPARTMENT_ID IS NOT NULL
1038 AND aso_resource_id in
1039 (
1040 SELECT ASO_RESOURCE_ID
1041 FROM ahl_rt_oper_resources
1042 WHERE object_id = c_object_id
1043 AND ASSOCIATION_TYPE_CODE = c_association_type_code
1044 AND ASO_RESOURCE_ID <> c_aso_res_id
1045 )
1046 OR aso_resource_id = c_alt_res_id
1047 GROUP BY bom_org_id
1048 HAVING count(DISTINCT DEPARTMENT_ID) > 1;
1049
1050 CURSOR get_rt_oper_res_det (c_rt_oper_resource_id number)
1051 IS
1052 SELECT OBJECT_ID,ASSOCIATION_TYPE_CODE, ASO_RESOURCE_ID
1053 FROM AHL_RT_OPER_RESOURCES
1054 WHERE RT_OPER_RESOURCE_ID= c_rt_oper_resource_id;
1055
1056 CURSOR get_alt_res_rt_oper_ids(c_aso_resource_id number)
1057 IS
1058 select RT_OPER_RESOURCE_ID
1059 from ahl_alternate_resources
1060 where ASO_RESOURCE_ID= c_aso_resource_id;
1061
1062 l_aso_res_id NUMBER;
1063 l_alt_res_rt_oper_id NUMBER;
1064 l_dummy VARCHAR2(1);
1065 l_object_id NUMBER;
1066 l_association_type_code VARCHAR2(30);
1067 l_rt_oper_res_id NUMBER;
1068
1069 --pdoki ER 7436910 End.
1070
1071 l_dummy_num NUMBER;
1072
1073 BEGIN
1074
1075 OPEN get_bom_res_num (p_aso_resource_id);
1076 FETCH get_bom_res_num INTO l_dummy_num;
1077 IF l_dummy_num < 1 THEN
1078 CLOSE get_bom_res_num;
1079 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_BOM_RES_COUNT_ZERO' );
1080 FND_MSG_PUB.add;
1081 RAISE FND_API.G_EXC_ERROR;
1082 END IF;
1083 CLOSE get_bom_res_num;
1084
1085 OPEN get_bom_res_org (p_aso_resource_id);
1086 FETCH get_bom_res_org INTO l_dummy_num;
1087 IF get_bom_res_org%FOUND THEN
1088 CLOSE get_bom_res_org;
1089 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_MUL_BOM_RES_PER_ORG' );
1090 FND_MSG_PUB.add;
1091 RAISE FND_API.G_EXC_ERROR;
1092 END IF;
1093 CLOSE get_bom_res_org;
1094
1095 OPEN get_bom_res_dup (p_aso_resource_id);
1096 FETCH get_bom_res_dup INTO l_dummy_num;
1097 IF get_bom_res_dup%FOUND THEN
1098 CLOSE get_bom_res_dup;
1099 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_BOM_RESOURCE_DUP' );
1100 FND_MSG_PUB.add;
1101 RAISE FND_API.G_EXC_ERROR;
1102 END IF;
1103 CLOSE get_bom_res_dup;
1104
1105 --pdoki ER 7436910 Begin.
1106 OPEN get_rt_oper_res_obj( p_aso_resource_id );
1107
1108 LOOP
1109 FETCH get_rt_oper_res_obj INTO
1110 l_object_id,
1111 l_association_type_code;
1112
1113 EXIT WHEN get_rt_oper_res_obj%NOTFOUND;
1114
1115 --checking dept conflicts among primary resources
1116 OPEN get_dept_conflicts( l_object_id, l_association_type_code );
1117 FETCH get_dept_conflicts INTO l_dummy;
1118 IF ( get_dept_conflicts%FOUND ) THEN
1119 CLOSE get_dept_conflicts;
1120 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_UPD_RES_CONFLICT' );
1121 FND_MSG_PUB.add;
1122 RAISE FND_API.G_EXC_ERROR;
1123 END IF;
1124 CLOSE get_dept_conflicts;
1125
1126 --checking dept conflicts b/w the primary resource and alt resources of OTHER primary resources
1127 OPEN get_rt_oper_res_ids( l_object_id, l_association_type_code );
1128
1129 LOOP
1130 FETCH get_rt_oper_res_ids INTO l_rt_oper_res_id;
1131 EXIT WHEN get_rt_oper_res_ids%NOTFOUND;
1132 OPEN get_dept_conflicts_alt_res( l_object_id, l_association_type_code, l_rt_oper_res_id);
1136 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_UPD_RES_CONFLICT' );
1133 FETCH get_dept_conflicts_alt_res INTO l_dummy;
1134 IF ( get_dept_conflicts_alt_res%FOUND ) THEN
1135 CLOSE get_dept_conflicts_alt_res;
1137 FND_MSG_PUB.add;
1138 RAISE FND_API.G_EXC_ERROR;
1139 END IF;
1140 CLOSE get_dept_conflicts_alt_res;
1141 END LOOP;
1142 CLOSE get_rt_oper_res_ids;
1143
1144 END LOOP;
1145
1146 CLOSE get_rt_oper_res_obj;
1147
1148 --checks for conflicts b/w the alternate resource and other primary resources. here the resource is an alternate resource(in alternate_resources table)
1149 OPEN get_alt_res_rt_oper_ids(p_aso_resource_id);
1150 LOOP
1151 FETCH get_alt_res_rt_oper_ids INTO l_alt_res_rt_oper_id;
1152 EXIT WHEN get_alt_res_rt_oper_ids%NOTFOUND;
1153
1154 OPEN get_rt_oper_res_det( l_alt_res_rt_oper_id );
1155 FETCH get_rt_oper_res_det INTO
1156 l_object_id,
1157 l_association_type_code,
1158 l_aso_res_id;
1159 CLOSE get_rt_oper_res_det;
1160
1161 OPEN get_dept_conflicts_alt_pri( l_object_id, l_association_type_code, l_aso_res_id, p_aso_resource_id);
1162 FETCH get_dept_conflicts_alt_pri INTO l_dummy;
1163 IF ( get_dept_conflicts_alt_pri%FOUND ) THEN
1164 CLOSE get_dept_conflicts_alt_pri;
1165 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_UPD_RES_CONFLICT' );
1166 FND_MSG_PUB.add;
1167 RAISE FND_API.G_EXC_ERROR;
1168 END IF;
1169 CLOSE get_dept_conflicts_alt_pri;
1170
1171 END LOOP;
1172 CLOSE get_alt_res_rt_oper_ids;
1173 --pdoki ER 7436910 End.
1174
1175 END validate_bom_records;
1176
1177 PROCEDURE process_aso_resource
1178 (
1179 p_api_version IN NUMBER := 1.0,
1180 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1181 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1182 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1183 p_default IN VARCHAR2 := FND_API.G_FALSE,
1184 p_module_type IN VARCHAR2 := NULL,
1185 x_return_status OUT NOCOPY VARCHAR2,
1186 x_msg_count OUT NOCOPY NUMBER,
1187 x_msg_data OUT NOCOPY VARCHAR2,
1188 p_x_aso_resource_rec IN OUT NOCOPY aso_resource_rec_type,
1189 p_x_bom_resource_tbl IN OUT NOCOPY bom_resource_tbl_type
1190 )
1191 IS
1192 l_api_version CONSTANT NUMBER := 1.0;
1193 l_return_status VARCHAR2(1);
1194 l_msg_count NUMBER;
1195 l_resource_id NUMBER;
1196 l_resource_mapping_id NUMBER;
1197
1198 CURSOR check_aso_resource_used(c_resource_id NUMBER) IS
1199 SELECT aso_resource_id
1200 FROM ahl_rt_oper_resources
1201 WHERE aso_resource_id = c_resource_id;
1202
1203 BEGIN
1204 -- Initialize API return status to success
1205 x_return_status := FND_API.G_RET_STS_SUCCESS;
1206
1207 -- Standard Start of API savepoint
1208 SAVEPOINT process_aso_resource_pvt;
1209
1210 -- Standard call to check for call compatibility.
1211 IF NOT FND_API.compatible_api_call
1212 (
1213 l_api_version,
1214 p_api_version,
1215 G_API_NAME,
1216 G_PKG_NAME
1217 )
1218 THEN
1219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1220 END IF;
1221
1222 -- Initialize message list if p_init_msg_list is set to TRUE.
1223 IF FND_API.to_boolean( p_init_msg_list ) THEN
1224 FND_MSG_PUB.initialize;
1225 END IF;
1226
1227 IF G_DEBUG = 'Y' THEN
1228 AHL_DEBUG_PUB.enable_debug;
1229 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Begin API' );
1230 END IF;
1231
1232 -- Validate all the inputs of the API
1233 validate_api_inputs
1234 (
1235 p_aso_resource_rec => p_x_aso_resource_rec,
1236 p_bom_resource_tbl => p_x_bom_resource_tbl
1237 );
1238
1239 IF G_DEBUG = 'Y' THEN
1240 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_api_inputs' );
1241 END IF;
1242
1243 -- If the module type is JSP, then default values for ID columns of LOV attributes
1244 IF ( p_module_type = 'JSP' ) THEN
1245 clear_lov_attribute_ids
1246 (
1247 p_x_aso_resource_rec => p_x_aso_resource_rec,
1248 p_x_bom_resource_tbl => p_x_bom_resource_tbl
1249 );
1250 END IF;
1251
1252 -- Convert Values into Ids.
1253 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1254 convert_values_to_ids
1255 (
1256 p_x_aso_resource_rec => p_x_aso_resource_rec,
1257 p_x_bom_resource_tbl => p_x_bom_resource_tbl
1258 );
1259 END IF;
1260
1261 IF G_DEBUG = 'Y' THEN
1262 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after convert_values_to_ids' );
1263 END IF;
1264
1265 -- Default aso_resource attributes.
1266 IF FND_API.to_boolean( p_default ) THEN
1267 IF ( p_x_aso_resource_rec.dml_operation <> 'D' ) THEN
1268 default_attributes
1269 (
1270 p_x_aso_resource_rec,
1271 p_x_bom_resource_tbl
1272 );
1273 END IF;
1274 END IF;
1275
1276 IF G_DEBUG = 'Y' THEN
1277 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_attributes' );
1281 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1278 END IF;
1279
1280 -- Validate all attributes (Item level validation)
1282 validate_attributes
1283 (
1284 p_x_aso_resource_rec,
1285 p_x_bom_resource_tbl
1286 );
1287 END IF;
1288
1289 IF G_DEBUG = 'Y' THEN
1290 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_attributes' );
1291 END IF;
1292
1293 -- Default missing and unchanged attributes.
1294 IF ( p_x_aso_resource_rec.dml_operation = 'C' ) THEN
1295 default_miss_aso_attributes( p_x_aso_resource_rec );
1296 IF p_x_bom_resource_tbl.count > 0 THEN
1297 FOR i IN p_x_bom_resource_tbl.FIRST..p_x_bom_resource_tbl.LAST LOOP
1298 default_miss_bom_attributes( p_x_bom_resource_tbl(i) );
1299 END LOOP;
1300 END IF;
1301 ELSIF ( p_x_aso_resource_rec.dml_operation = 'U' ) THEN
1302 default_unc_aso_attributes( p_x_aso_resource_rec);
1303 IF p_x_bom_resource_tbl.count > 0 THEN
1304 FOR i IN p_x_bom_resource_tbl.FIRST..p_x_bom_resource_tbl.LAST LOOP
1305 IF p_x_bom_resource_tbl(i).dml_operation = 'C' THEN
1306 default_miss_bom_attributes( p_x_bom_resource_tbl(i));
1307 ELSIF p_x_bom_resource_tbl(i).dml_operation = 'U' THEN
1308 default_unc_bom_attributes( p_x_bom_resource_tbl(i));
1309 END IF;
1310 END LOOP;
1311 END IF;
1312 ELSIF ( p_x_aso_resource_rec.dml_operation IS NULL) THEN
1313 IF p_x_bom_resource_tbl.count > 0 THEN
1314 FOR i IN p_x_bom_resource_tbl.FIRST..p_x_bom_resource_tbl.LAST LOOP
1315 IF p_x_bom_resource_tbl(i).dml_operation = 'C' THEN
1316 default_miss_bom_attributes( p_x_bom_resource_tbl(i));
1317 ELSIF p_x_bom_resource_tbl(i).dml_operation = 'U' THEN
1318 default_unc_bom_attributes( p_x_bom_resource_tbl(i));
1319 END IF;
1320 END LOOP;
1321 END IF;
1322 END IF;
1323
1324 IF G_DEBUG = 'Y' THEN
1325 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_unchanged_attributes / default_missing_attributes' );
1326 END IF;
1327
1328 -- Validate records (Across records validation)
1329 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
1330 IF p_x_bom_resource_tbl.count > 0 THEN
1331 validate_bom_record
1332 (
1333 p_x_aso_resource_rec,
1334 p_x_bom_resource_tbl
1335 );
1336 END IF;
1337 validate_aso_records
1338 (
1339 p_x_aso_resource_rec
1340 );
1341 validate_records
1342 (
1343 p_x_aso_resource_rec,
1344 p_x_bom_resource_tbl
1345 );
1346 END IF;
1347
1348 IF G_DEBUG = 'Y' THEN
1349 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_records' );
1350 END IF;
1351
1352 -- Perform the DML statement directly.
1353 IF ( p_x_aso_resource_rec.dml_operation = 'C' ) THEN
1354 -- Insert the record into the master table AHL_RESOURCES
1355 INSERT INTO AHL_RESOURCES
1356 (
1357 RESOURCE_ID,
1358 OBJECT_VERSION_NUMBER,
1359 LAST_UPDATE_DATE,
1360 LAST_UPDATED_BY,
1361 CREATION_DATE,
1362 CREATED_BY,
1363 LAST_UPDATE_LOGIN,
1364 RESOURCE_TYPE_ID,
1365 NAME,
1366 DESCRIPTION,
1367 ATTRIBUTE_CATEGORY,
1368 ATTRIBUTE1,
1369 ATTRIBUTE2,
1370 ATTRIBUTE3,
1371 ATTRIBUTE4,
1372 ATTRIBUTE5,
1373 ATTRIBUTE6,
1374 ATTRIBUTE7,
1375 ATTRIBUTE8,
1376 ATTRIBUTE9,
1377 ATTRIBUTE10,
1378 ATTRIBUTE11,
1379 ATTRIBUTE12,
1380 ATTRIBUTE13,
1381 ATTRIBUTE14,
1382 ATTRIBUTE15
1383 ) VALUES
1384 (
1385 ahl_resources_s.nextval,
1386 p_x_aso_resource_rec.object_version_number,
1387 p_x_aso_resource_rec.last_update_date,
1388 p_x_aso_resource_rec.last_updated_by,
1389 p_x_aso_resource_rec.creation_date,
1390 p_x_aso_resource_rec.created_by,
1391 p_x_aso_resource_rec.last_update_login,
1392 p_x_aso_resource_rec.resource_type_id,
1393 p_x_aso_resource_rec.name,
1394 p_x_aso_resource_rec.description,
1395 p_x_aso_resource_rec.attribute_category,
1396 p_x_aso_resource_rec.attribute1,
1397 p_x_aso_resource_rec.attribute2,
1398 p_x_aso_resource_rec.attribute3,
1399 p_x_aso_resource_rec.attribute4,
1400 p_x_aso_resource_rec.attribute5,
1401 p_x_aso_resource_rec.attribute6,
1402 p_x_aso_resource_rec.attribute7,
1403 p_x_aso_resource_rec.attribute8,
1404 p_x_aso_resource_rec.attribute9,
1405 p_x_aso_resource_rec.attribute10,
1406 p_x_aso_resource_rec.attribute11,
1407 p_x_aso_resource_rec.attribute12,
1408 p_x_aso_resource_rec.attribute13,
1409 p_x_aso_resource_rec.attribute14,
1410 p_x_aso_resource_rec.attribute15
1411 ) RETURNING resource_id INTO l_resource_id;
1412
1413 -- Set OUT values
1414 p_x_aso_resource_rec.resource_id := l_resource_id;
1415
1416 -- Insert the records into the details table AHL_RESOURCE_MAPPINGS
1420 RESOURCE_MAPPING_ID,
1417 FOR i IN p_x_bom_resource_tbl.FIRST..p_x_bom_resource_tbl.LAST LOOP
1418 INSERT INTO AHL_RESOURCE_MAPPINGS
1419 (
1421 OBJECT_VERSION_NUMBER,
1422 LAST_UPDATE_DATE,
1423 LAST_UPDATED_BY,
1424 CREATION_DATE,
1425 CREATED_BY,
1426 LAST_UPDATE_LOGIN,
1427 ASO_RESOURCE_ID,
1428 BOM_RESOURCE_ID,
1429 BOM_ORG_ID,
1430 DEPARTMENT_ID,--pdoki ER 7436910
1431 ATTRIBUTE_CATEGORY,
1432 ATTRIBUTE1,
1433 ATTRIBUTE2,
1434 ATTRIBUTE3,
1435 ATTRIBUTE4,
1436 ATTRIBUTE5,
1437 ATTRIBUTE6,
1438 ATTRIBUTE7,
1439 ATTRIBUTE8,
1440 ATTRIBUTE9,
1441 ATTRIBUTE10,
1442 ATTRIBUTE11,
1443 ATTRIBUTE12,
1444 ATTRIBUTE13,
1445 ATTRIBUTE14,
1446 ATTRIBUTE15
1447 ) VALUES
1448 (
1449 AHL_RESOURCE_MAPPINGS_S.NEXTVAL,
1450 p_x_bom_resource_tbl(i).object_version_number,
1451 p_x_bom_resource_tbl(i).last_update_date,
1452 p_x_bom_resource_tbl(i).last_updated_by,
1453 p_x_bom_resource_tbl(i).creation_date,
1454 p_x_bom_resource_tbl(i).created_by,
1455 p_x_bom_resource_tbl(i).last_update_login,
1456 p_x_aso_resource_rec.resource_id,
1457 p_x_bom_resource_tbl(i).bom_resource_id,
1458 p_x_bom_resource_tbl(i).bom_org_id,
1459 p_x_bom_resource_tbl(i).department_id,--pdoki ER 7436910
1460 p_x_bom_resource_tbl(i).attribute_category,
1461 p_x_bom_resource_tbl(i).attribute1,
1462 p_x_bom_resource_tbl(i).attribute2,
1463 p_x_bom_resource_tbl(i).attribute3,
1464 p_x_bom_resource_tbl(i).attribute4,
1465 p_x_bom_resource_tbl(i).attribute5,
1466 p_x_bom_resource_tbl(i).attribute6,
1467 p_x_bom_resource_tbl(i).attribute7,
1468 p_x_bom_resource_tbl(i).attribute8,
1469 p_x_bom_resource_tbl(i).attribute9,
1470 p_x_bom_resource_tbl(i).attribute10,
1471 p_x_bom_resource_tbl(i).attribute11,
1472 p_x_bom_resource_tbl(i).attribute12,
1473 p_x_bom_resource_tbl(i).attribute13,
1474 p_x_bom_resource_tbl(i).attribute14,
1475 p_x_bom_resource_tbl(i).attribute15
1476 ) RETURNING resource_mapping_id INTO l_resource_mapping_id;
1477 p_x_bom_resource_tbl(i).resource_mapping_id := l_resource_mapping_id;
1478 END LOOP;
1479
1480 ELSIF ( p_x_aso_resource_rec.dml_operation = 'U' OR p_x_aso_resource_rec.dml_operation IS NULL) THEN
1481 -- Update the record
1482 UPDATE AHL_RESOURCES SET
1483 object_version_number = object_version_number + 1,
1484 last_update_date = p_x_aso_resource_rec.last_update_date,
1485 last_updated_by = p_x_aso_resource_rec.last_updated_by,
1486 last_update_login = p_x_aso_resource_rec.last_update_login,
1487 name = p_x_aso_resource_rec.name,
1488 description = p_x_aso_resource_rec.description,
1489 attribute_category = p_x_aso_resource_rec.attribute_category,
1490 attribute1 = p_x_aso_resource_rec.attribute1,
1491 attribute2 = p_x_aso_resource_rec.attribute2,
1492 attribute3 = p_x_aso_resource_rec.attribute3,
1493 attribute4 = p_x_aso_resource_rec.attribute4,
1494 attribute5 = p_x_aso_resource_rec.attribute5,
1495 attribute6 = p_x_aso_resource_rec.attribute6,
1496 attribute7 = p_x_aso_resource_rec.attribute7,
1497 attribute8 = p_x_aso_resource_rec.attribute8,
1498 attribute9 = p_x_aso_resource_rec.attribute9,
1499 attribute10 = p_x_aso_resource_rec.attribute10,
1500 attribute11 = p_x_aso_resource_rec.attribute11,
1501 attribute12 = p_x_aso_resource_rec.attribute12,
1502 attribute13 = p_x_aso_resource_rec.attribute13,
1503 attribute14 = p_x_aso_resource_rec.attribute14,
1504 attribute15 = p_x_aso_resource_rec.attribute15
1505 WHERE resource_id = p_x_aso_resource_rec.resource_id
1506 AND object_version_number = p_x_aso_resource_rec.object_version_number;
1507
1508 -- If the record does not exist, then, abort API.
1509 IF ( SQL%ROWCOUNT = 0 ) THEN
1510 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1511 FND_MSG_PUB.add;
1512 ELSE
1513 -- Update(Insert or Delete) the records in details table
1514 IF p_x_bom_resource_tbl.count > 0 THEN
1515 FOR i IN p_x_bom_resource_tbl.FIRST..p_x_bom_resource_tbl.LAST LOOP
1516 IF p_x_bom_resource_tbl(i).dml_operation = 'C' THEN
1517 INSERT INTO AHL_RESOURCE_MAPPINGS(
1518 RESOURCE_MAPPING_ID,
1519 OBJECT_VERSION_NUMBER,
1520 LAST_UPDATE_DATE,
1521 LAST_UPDATED_BY,
1522 CREATION_DATE,
1523 CREATED_BY,
1524 LAST_UPDATE_LOGIN,
1525 ASO_RESOURCE_ID,
1526 BOM_RESOURCE_ID,
1527 BOM_ORG_ID,
1528 DEPARTMENT_ID,--pdoki ER 7436910
1529 ATTRIBUTE_CATEGORY,
1530 ATTRIBUTE1,
1531 ATTRIBUTE2,
1532 ATTRIBUTE3,
1533 ATTRIBUTE4,
1534 ATTRIBUTE5,
1535 ATTRIBUTE6,
1536 ATTRIBUTE7,
1537 ATTRIBUTE8,
1538 ATTRIBUTE9,
1539 ATTRIBUTE10,
1540 ATTRIBUTE11,
1541 ATTRIBUTE12,
1542 ATTRIBUTE13,
1543 ATTRIBUTE14,
1544 ATTRIBUTE15)
1545 VALUES(
1546 AHL_RESOURCE_MAPPINGS_S.NEXTVAL,
1547 p_x_bom_resource_tbl(i).object_version_number,
1548 p_x_bom_resource_tbl(i).last_update_date,
1549 p_x_bom_resource_tbl(i).last_updated_by,
1550 p_x_bom_resource_tbl(i).creation_date,
1551 p_x_bom_resource_tbl(i).created_by,
1552 p_x_bom_resource_tbl(i).last_update_login,
1553 p_x_aso_resource_rec.resource_id,
1554 p_x_bom_resource_tbl(i).bom_resource_id,
1555 p_x_bom_resource_tbl(i).bom_org_id,
1556 p_x_bom_resource_tbl(i).department_id,--pdoki ER 7436910
1557 p_x_bom_resource_tbl(i).attribute_category,
1558 p_x_bom_resource_tbl(i).attribute1,
1559 p_x_bom_resource_tbl(i).attribute2,
1560 p_x_bom_resource_tbl(i).attribute3,
1561 p_x_bom_resource_tbl(i).attribute4,
1562 p_x_bom_resource_tbl(i).attribute5,
1563 p_x_bom_resource_tbl(i).attribute6,
1564 p_x_bom_resource_tbl(i).attribute7,
1565 p_x_bom_resource_tbl(i).attribute8,
1566 p_x_bom_resource_tbl(i).attribute9,
1567 p_x_bom_resource_tbl(i).attribute10,
1568 p_x_bom_resource_tbl(i).attribute11,
1569 p_x_bom_resource_tbl(i).attribute12,
1570 p_x_bom_resource_tbl(i).attribute13,
1571 p_x_bom_resource_tbl(i).attribute14,
1572 p_x_bom_resource_tbl(i).attribute15)
1573 RETURNING resource_mapping_id INTO l_resource_mapping_id;
1574 p_x_bom_resource_tbl(i).resource_mapping_id := l_resource_mapping_id;
1575 ELSIF p_x_bom_resource_tbl(i).dml_operation = 'U' THEN
1576 UPDATE AHL_RESOURCE_MAPPINGS SET
1577 object_version_number = object_version_number + 1,
1578 last_update_date = p_x_bom_resource_tbl(i).last_update_date,
1579 last_updated_by = p_x_bom_resource_tbl(i).last_updated_by,
1580 last_update_login = p_x_bom_resource_tbl(i).last_update_login,
1581 bom_resource_id = p_x_bom_resource_tbl(i).bom_resource_id,
1582 bom_org_id = p_x_bom_resource_tbl(i).bom_org_id,
1583 department_id = p_x_bom_resource_tbl(i).department_id,--pdoki ER 7436910
1584 attribute_category = p_x_bom_resource_tbl(i).attribute_category,
1585 attribute1 = p_x_bom_resource_tbl(i).attribute1,
1586 attribute2 = p_x_bom_resource_tbl(i).attribute2,
1587 attribute3 = p_x_bom_resource_tbl(i).attribute3,
1588 attribute4 = p_x_bom_resource_tbl(i).attribute4,
1589 attribute5 = p_x_bom_resource_tbl(i).attribute5,
1590 attribute6 = p_x_bom_resource_tbl(i).attribute6,
1591 attribute7 = p_x_bom_resource_tbl(i).attribute7,
1592 attribute8 = p_x_bom_resource_tbl(i).attribute8,
1593 attribute9 = p_x_bom_resource_tbl(i).attribute9,
1594 attribute10 = p_x_bom_resource_tbl(i).attribute10,
1595 attribute11 = p_x_bom_resource_tbl(i).attribute11,
1596 attribute12 = p_x_bom_resource_tbl(i).attribute12,
1597 attribute13 = p_x_bom_resource_tbl(i).attribute13,
1598 attribute14 = p_x_bom_resource_tbl(i).attribute14,
1599 attribute15 = p_x_bom_resource_tbl(i).attribute15
1600 WHERE resource_mapping_id = p_x_bom_resource_tbl(i).resource_mapping_id
1601 AND object_version_number = p_x_bom_resource_tbl(i).object_version_number;
1602 IF ( SQL%ROWCOUNT = 0 ) THEN
1603 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1604 FND_MSG_PUB.add;
1605 ELSE
1606 p_x_bom_resource_tbl(i).object_version_number := p_x_bom_resource_tbl(i).object_version_number+1;
1607 END IF;
1608 ELSIF p_x_bom_resource_tbl(i).dml_operation = 'D' THEN
1609 DELETE FROM AHL_RESOURCE_MAPPINGS
1610 WHERE resource_mapping_id = p_x_bom_resource_tbl(i).resource_mapping_id
1611 AND object_version_number = p_x_bom_resource_tbl(i).object_version_number;
1612 IF ( SQL%ROWCOUNT = 0 ) THEN
1613 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1614 FND_MSG_PUB.add;
1615 END IF;
1616 END IF;
1617 END LOOP;
1618 END IF;
1619
1620 -- Set OUT values
1621 p_x_aso_resource_rec.object_version_number := p_x_aso_resource_rec.object_version_number + 1;
1622 END IF;
1623
1624 ELSIF ( p_x_aso_resource_rec.dml_operation = 'D' ) THEN
1625 -- Before deleting the record, check whether it is used in the resources of
1626 -- a route or an operation
1627 OPEN check_aso_resource_used(p_x_aso_resource_rec.resource_id);
1628 FETCH check_aso_resource_used INTO l_resource_id;
1629 IF check_aso_resource_used%FOUND THEN
1630 CLOSE check_aso_resource_used;
1631 FND_MESSAGE.set_name('AHL','AHL_RM_ASO_RES_BEING_USED');
1632 FND_MSG_PUB.add;
1633 ELSE
1634 CLOSE check_aso_resource_used;
1635 /*
1636 DELETE FROM AHL_RESOURCE_SKILLS
1637 WHERE AHL_RESOURCE_ID = p_x_aso_resource_rec.resource_id;
1638 */
1639 DELETE FROM AHL_RESOURCES
1640 WHERE resource_id = p_x_aso_resource_rec.resource_id
1641 AND object_version_number = p_x_aso_resource_rec.object_version_number;
1642
1643 -- If the record does not exist, then, abort API.
1644 IF ( SQL%ROWCOUNT = 0 ) THEN
1645 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1646 FND_MSG_PUB.add;
1647 -- Delete the detailed records in ahl_resource_skills
1648 ELSE
1649 DELETE FROM AHL_RESOURCE_MAPPINGS
1650 WHERE aso_resource_id = p_x_aso_resource_rec.resource_id;
1651 END IF;
1652 END IF;
1653 END IF;
1654
1655 IF G_DEBUG = 'Y' THEN
1656 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after DML operation' );
1657 END IF;
1658
1659 -- Perform the cross records validation after DML operation
1660 IF p_x_aso_resource_rec.DML_OPERATION <> 'D' THEN
1661 IF G_DEBUG = 'Y' THEN
1662 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : just before validate_bom_records.' );
1663 END IF;
1664 validate_bom_records (p_x_aso_resource_rec.RESOURCE_ID);
1665 END IF;
1666
1667 IF G_DEBUG = 'Y' THEN
1668 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : The last cross records validation was processed after DML operation' );
1669 END IF;
1670
1671 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1672 l_msg_count := FND_MSG_PUB.count_msg;
1673 IF l_msg_count > 0 THEN
1674 x_msg_count := l_msg_count;
1675 RAISE FND_API.G_EXC_ERROR;
1676 END IF;
1677
1678 -- Perform the Commit (if requested)
1679 IF FND_API.to_boolean( p_commit ) THEN
1680 COMMIT WORK;
1681 END IF;
1682
1683 -- Count and Get messages (optional)
1684 FND_MSG_PUB.count_and_get
1685 (
1686 p_encoded => FND_API.G_FALSE,
1687 p_count => x_msg_count,
1688 p_data => x_msg_data
1689 );
1690
1691 -- Disable debug (if enabled)
1692 IF G_DEBUG = 'Y' THEN
1693 AHL_DEBUG_PUB.disable_debug;
1694 END IF;
1695 EXCEPTION
1696 WHEN FND_API.G_EXC_ERROR THEN
1697 ROLLBACK TO PROCESS_ASO_RESOURCE_PVT;
1698 x_return_status := FND_API.G_RET_STS_ERROR ;
1699 FND_MSG_PUB.count_and_get
1700 (
1701 p_encoded => FND_API.G_FALSE,
1702 p_count => x_msg_count,
1703 p_data => x_msg_data
1704 );
1705 -- Disable debug (if enabled)
1706 IF G_DEBUG = 'Y' THEN
1707 AHL_DEBUG_PUB.disable_debug;
1708 END IF;
1709
1710 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1711 ROLLBACK TO process_aso_resource_PVT;
1712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1713 FND_MSG_PUB.count_and_get
1714 (
1715 p_encoded => FND_API.G_FALSE,
1716 p_count => x_msg_count,
1717 p_data => x_msg_data
1718 );
1719
1720 -- Disable debug (if enabled)
1721 IF G_DEBUG = 'Y' THEN
1722 AHL_DEBUG_PUB.disable_debug;
1723 END IF;
1724
1725 WHEN OTHERS THEN
1726 ROLLBACK TO process_aso_resource_PVT;
1727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1728 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1729 THEN
1730 FND_MSG_PUB.add_exc_msg
1731 (
1732 p_pkg_name => G_PKG_NAME,
1733 p_procedure_name => G_API_NAME,
1734 p_error_text => SUBSTRB(SQLERRM,1,240)
1735 );
1736 END IF;
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 END process_aso_resource;
1750
1751 END AHL_RM_ASO_RESOURCE_PVT;