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