[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_MATERIAL_AS_PVT
Source
1 PACKAGE BODY AHL_RM_MATERIAL_AS_PVT AS
2 /* $Header: AHLVMTLB.pls 120.2 2008/01/30 05:37:54 pdoki ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_RM_MATERIAL_AS_PVT';
5 G_API_NAME VARCHAR2(30) := 'PROCESS_MATERIAL_REQ';
6 G_API_NAME1 VARCHAR2(30) := 'PROCESS_ROUTE_EFCTS';
7 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
8
9 -- Function to get the Record Identifier for Error Messages
10 FUNCTION get_record_identifier
11 (
12 p_material_req_rec IN material_req_rec_type
13 ) RETURN VARCHAR2
14 IS
15
16 l_record_identifier VARCHAR2(5000) := '';
17
18 l_put_comma VARCHAR2(5) :='N';
19
20 BEGIN
21
22
23 IF ( p_material_req_rec.item_group_name IS NOT NULL AND
24 p_material_req_rec.item_group_name <> FND_API.G_MISS_CHAR ) THEN
25 l_record_identifier := l_record_identifier || p_material_req_rec.item_group_name;
26 l_put_comma := 'Y';
27 ELSE
28 l_put_comma := 'N';
29 END IF;
30
31 l_record_identifier := l_record_identifier || ' ';
32
33 IF ( p_material_req_rec.item_number IS NOT NULL AND
34 p_material_req_rec.item_number <> FND_API.G_MISS_CHAR ) THEN
35
36 IF(l_put_comma = 'Y')
37 THEN
38 l_record_identifier := l_record_identifier || ',';
39 END IF;
40 l_record_identifier := l_record_identifier || p_material_req_rec.item_number;
41
42 l_put_comma := 'Y';
43 END IF;
44
45 l_record_identifier := l_record_identifier || ' ';
46
47 IF ( p_material_req_rec.POSITION_PATH IS NOT NULL AND
48 p_material_req_rec.POSITION_PATH <> FND_API.G_MISS_CHAR ) THEN
49
50 IF(l_put_comma = 'Y')
51 THEN
52 l_record_identifier := l_record_identifier || ',';
53 END IF;
54 l_record_identifier := l_record_identifier || p_material_req_rec.POSITION_PATH;
55 END IF;
56
57 RETURN l_record_identifier;
58
59 END get_record_identifier;
60
61
62
63 FUNCTION get_effct_identifier
64 (
65 p_route_req_rec IN route_efct_rec_type
66 ) RETURN VARCHAR2
67 IS
68
69 l_record_identifier VARCHAR2(2000) := '';
70
71 BEGIN
72 IF ( p_route_req_rec.ITEM_NUMBER IS NOT NULL AND
73 p_route_req_rec.ITEM_NUMBER <> FND_API.G_MISS_CHAR ) THEN
74 l_record_identifier := l_record_identifier || p_route_req_rec.ITEM_NUMBER ;
75 l_record_identifier := l_record_identifier || ' ';
76 END IF;
77
78
79
80
81 IF ( p_route_req_rec.ORGANIZATION_CODE IS NOT NULL AND
82 p_route_req_rec.ORGANIZATION_CODE <> FND_API.G_MISS_CHAR ) THEN
83
84 l_record_identifier := l_record_identifier || p_route_req_rec.ORGANIZATION_CODE;
85 l_record_identifier := l_record_identifier || ' ';
86 END IF;
87
88
89
90 IF ( p_route_req_rec.MC_NAME IS NOT NULL AND
91 p_route_req_rec.MC_NAME <> FND_API.G_MISS_CHAR ) THEN
92
93 l_record_identifier := l_record_identifier || p_route_req_rec.MC_NAME;
94 l_record_identifier := l_record_identifier || ' ';
95 END IF;
96
97
98
99 IF ( p_route_req_rec.MC_REVISION IS NOT NULL AND
100 p_route_req_rec.MC_REVISION <> FND_API.G_MISS_CHAR ) THEN
101
102 l_record_identifier := l_record_identifier || p_route_req_rec.MC_REVISION;
103 END IF;
104
105 RETURN l_record_identifier;
106
107 END get_effct_identifier;
108
109 -- Procedure to validate the Inputs of the API
110 PROCEDURE validate_api_inputs
111 (
112 p_material_req_tbl IN material_req_tbl_type,
113 p_object_id IN NUMBER,
114 p_association_type IN VARCHAR2,
115 x_return_status OUT NOCOPY VARCHAR2
116 )
117 IS
118
119 l_return_status VARCHAR2(1);
120 l_msg_data VARCHAR2(2000);
121
122 BEGIN
123 x_return_status := FND_API.G_RET_STS_SUCCESS;
124
125 -- Check if a value is passed in p_object_id
126 IF ( p_object_id = FND_API.G_MISS_NUM OR
127 p_object_id IS NULL ) THEN
128 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_OBJECT_ID_NULL' );
129 FND_MSG_PUB.add;
130 x_return_status := FND_API.G_RET_STS_ERROR;
131 RETURN;
132 END IF;
133
134 -- Check if a value is passed in p_association_type
135 IF ( p_association_type = FND_API.G_MISS_CHAR OR
136 p_association_type IS NULL ) THEN
137 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ASSOC_TYPE_NULL' );
138 FND_MSG_PUB.add;
139 x_return_status := FND_API.G_RET_STS_ERROR;
140 RETURN;
141 END IF;
142
143 -- Check if a valid value is passed in p_association_type
144 IF ( p_association_type <> 'ROUTE' AND
145 p_association_type <> 'OPERATION' AND
146 p_association_type <> 'DISPOSITION' ) THEN
147 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ASSOC_TYPE_INVALID' );
148 FND_MESSAGE.set_token( 'FIELD', p_association_type );
149 FND_MSG_PUB.add;
150 x_return_status := FND_API.G_RET_STS_ERROR;
151 RETURN;
152 END IF;
153
154 -- Check if the Route / Operation is in Updatable status
155 IF ( p_association_type = 'ROUTE' ) THEN
156 AHL_RM_ROUTE_UTIL.validate_route_status
157 (
158 x_return_status => l_return_status,
159 x_msg_data => l_msg_data,
160 p_route_id => p_object_id
161 );
162 ELSIF ( p_association_type = 'OPERATION' ) THEN
163 AHL_RM_ROUTE_UTIL.validate_operation_status
164 (
165 x_return_status => l_return_status,
166 x_msg_data => l_msg_data,
167 p_operation_id => p_object_id
168 );
169 ELSIF ( p_association_type = 'DISPOSITION' ) THEN
170 AHL_RM_ROUTE_UTIL.validate_efct_status
171 (
172 x_return_status => l_return_status,
173 x_msg_data => l_msg_data,
174 p_efct_id => p_object_id
175 );
176 END IF;
177
178 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
179 FND_MESSAGE.set_name( 'AHL', l_msg_data );
180 FND_MSG_PUB.add;
181 x_return_status := l_return_status;
182 RETURN;
183 END IF;
184
185 -- Check if atleast one record is passed in p_material_req_tbl
186 IF ( p_material_req_tbl.count < 1 ) THEN
187 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
188 FND_MESSAGE.set_token( 'PROCEDURE', G_PKG_NAME || '.' || G_API_NAME );
189 FND_MSG_PUB.add;
190 x_return_status := FND_API.G_RET_STS_ERROR;
191 RETURN;
192 END IF;
193
194 -- Validate DML Operation
195 FOR i IN 1..p_material_req_tbl.count LOOP
196 IF ( p_material_req_tbl(i).dml_operation <> 'D' AND
197 p_material_req_tbl(i).dml_operation <> 'U' AND
198 p_material_req_tbl(i).dml_operation <> 'C' ) THEN
199 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML' );
200 FND_MESSAGE.set_token( 'FIELD', p_material_req_tbl(i).dml_operation );
201 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_tbl(i) ) );
202 FND_MSG_PUB.add;
203 x_return_status := FND_API.G_RET_STS_ERROR;
204 RETURN;
205 END IF;
206 END LOOP;
207
208 END validate_api_inputs;
209
210 -- Procedure to validate the all the inputs except the table structure of the API
211 PROCEDURE validate_efct_api_inputs
212 (
213 p_route_efct_tbl IN route_efct_tbl_type,
214 p_object_id IN NUMBER,
215 x_return_status OUT NOCOPY VARCHAR2
216 )
217 IS
218
219 l_return_status VARCHAR2(1);
220 l_msg_data VARCHAR2(2000);
221
222 BEGIN
223 x_return_status := FND_API.G_RET_STS_SUCCESS;
224
225 -- Check if a valid value is passed in p_rt_oper_resource_id
226 IF ( p_object_id = FND_API.G_MISS_NUM OR
227 p_object_id IS NULL ) THEN
228 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_efct_OBJECT_NULL' );
229 FND_MSG_PUB.add;
230 x_return_status := FND_API.G_RET_STS_ERROR;
231 RAISE FND_API.G_EXC_ERROR;
232 END IF;
233
234 AHL_RM_ROUTE_UTIL.validate_route_status
235 (
236 p_route_id => p_object_id,
237 x_return_status => l_return_status,
238 x_msg_data => l_msg_data
239 );
240 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
241 FND_MESSAGE.set_name( 'AHL', l_msg_data );
242 FND_MSG_PUB.add;
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 RETURN;
245 END IF;
246
247
248 -- Check if at least one record is passed in p_rt_oper_resource_tbl
249 IF ( p_route_efct_tbl.count < 1 ) THEN
250 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
251 FND_MESSAGE.set_token( 'PROCEDURE', G_PKG_NAME || '.' || G_API_NAME1 );
252 FND_MSG_PUB.add;
253 x_return_status := FND_API.G_RET_STS_ERROR;
254 RETURN;
255 END IF;
256
257 -- Validate DML Operation
258 FOR i IN 1..p_route_efct_tbl.count LOOP
259 IF ( p_route_efct_tbl(i).dml_operation <> 'C' AND
260 p_route_efct_tbl(i).dml_operation <> 'U' AND
261 p_route_efct_tbl(i).dml_operation <> 'D' ) THEN
262 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML' );
263 FND_MESSAGE.set_token( 'FIELD', p_route_efct_tbl(i).dml_operation );
264 -- FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_efct_tbl(i) ) );
265 FND_MSG_PUB.add;
266 x_return_status := FND_API.G_RET_STS_ERROR;
267 RETURN;
268 END IF;
269 END LOOP;
270
271
272 END validate_efct_api_inputs;
273
274 -- Procedure to Default NULL / G_MISS Values for LOV attributes
275 PROCEDURE clear_lov_attribute_ids
276 (
277 p_x_material_req_rec IN OUT NOCOPY material_req_rec_type
278 )
279 IS
280
281 BEGIN
282
283
284 IF ( p_x_material_req_rec.position_path IS NULL ) THEN
285 p_x_material_req_rec.position_path_id := NULL;
286 ELSIF ( p_x_material_req_rec.position_path = FND_API.G_MISS_CHAR ) THEN
287 p_x_material_req_rec.position_path_id := FND_API.G_MISS_NUM;
288 END IF;
289
290 IF ( p_x_material_req_rec.item_group_name IS NULL ) THEN
291 p_x_material_req_rec.item_group_id := NULL;
292 ELSIF ( p_x_material_req_rec.item_group_name = FND_API.G_MISS_CHAR ) THEN
293 p_x_material_req_rec.item_group_id := FND_API.G_MISS_NUM;
294 END IF;
295
296 IF ( p_x_material_req_rec.item_number IS NULL ) THEN
297 p_x_material_req_rec.inventory_item_id := NULL;
298 p_x_material_req_rec.inventory_org_id := NULL;
299 ELSIF ( p_x_material_req_rec.item_number = FND_API.G_MISS_CHAR ) THEN
300 p_x_material_req_rec.inventory_item_id := FND_API.G_MISS_NUM;
301 p_x_material_req_rec.inventory_org_id := FND_API.G_MISS_NUM;
302 END IF;
303
304 IF ( p_x_material_req_rec.uom IS NULL ) THEN
305 p_x_material_req_rec.uom_code := NULL;
306 ELSIF ( p_x_material_req_rec.uom = FND_API.G_MISS_CHAR ) THEN
307 p_x_material_req_rec.uom_code := FND_API.G_MISS_CHAR;
308 END IF;
309
310 END clear_lov_attribute_ids;
311
312 -- Procedure to Default NULL / G_MISS Values for LOV attributes
313 PROCEDURE clear_efct_attribute_ids
314 (
315 p_x_route_efct_rec IN OUT NOCOPY route_efct_rec_type
316 )
317 IS
318
319 BEGIN
320
321 IF ( p_x_route_efct_rec.mc_name IS NULL ) THEN
322 p_x_route_efct_rec.mc_id := NULL;
323 p_x_route_efct_rec.mc_header_id := NULL;
324 ELSIF ( p_x_route_efct_rec.mc_name = FND_API.G_MISS_CHAR ) THEN
325 p_x_route_efct_rec.mc_header_id := FND_API.G_MISS_NUM;
326 p_x_route_efct_rec.mc_id := FND_API.G_MISS_NUM;
327 END IF;
328
329 IF ( p_x_route_efct_rec.MC_REVISION IS NULL ) THEN
330 p_x_route_efct_rec.mc_header_id := NULL;
331 ELSIF ( p_x_route_efct_rec.MC_REVISION = FND_API.G_MISS_CHAR ) THEN
332 p_x_route_efct_rec.mc_header_id := FND_API.G_MISS_NUM;
333 END IF;
334
335 IF ( p_x_route_efct_rec.item_number IS NULL OR p_x_route_efct_rec.ORGANIZATION_CODE IS NULL ) THEN
336 p_x_route_efct_rec.inventory_item_id := NULL;
337 p_x_route_efct_rec.inventory_master_org_id := NULL;
338 ELSIF ( p_x_route_efct_rec.item_number = FND_API.G_MISS_CHAR OR p_x_route_efct_rec.ORGANIZATION_CODE = FND_API.G_MISS_CHAR ) THEN
339 p_x_route_efct_rec.inventory_item_id := FND_API.G_MISS_NUM;
340 p_x_route_efct_rec.inventory_master_org_id := FND_API.G_MISS_NUM;
341 END IF;
342
343
344 END clear_efct_attribute_ids;
345
346
347 -- Procedure to perform Value to ID conversion for appropriate attributes
348 PROCEDURE convert_values_to_ids
349 (
350 p_x_material_req_rec IN OUT NOCOPY material_req_rec_type,
351 p_object_id IN NUMBER,
352 p_association_type IN VARCHAR2,
353 x_return_status OUT NOCOPY VARCHAR2
354 )
355 IS
356
357
358 -- Cursor for getting item_comp_detail_id when supplied with item_group_id and master_org_id
359
360 --AMSRINIV. Bug 4913141. Query for cursor below tuned.
361 CURSOR get_item_comp_detail_id( p_route_effecitivity_id IN NUMBER,
362 p_item_group_id IN NUMBER ) IS
363 SELECT ITEM_COMP_DETAIL_ID
364 FROM AHL_ITEM_COMP_DETAILS
365 WHERE ITEM_GROUP_ID = P_ITEM_GROUP_ID AND
366 ITEM_COMPOSITION_ID = (
367 SELECT IC.ITEM_COMPOSITION_ID FROM
368 AHL_ROUTE_EFFECTIVITIES RE, AHL_ITEM_COMPOSITIONS IC
369 WHERE RE.INVENTORY_ITEM_ID=IC.INVENTORY_ITEM_ID(+) AND
370 RE.INVENTORY_MASTER_ORG_ID=IC.INVENTORY_MASTER_ORG_ID(+) AND
371 IC.APPROVAL_STATUS_CODE(+)='COMPLETE' AND
372 ROUTE_EFFECTIVITY_ID = P_ROUTE_EFFECITIVITY_ID ) AND
373 EFFECTIVE_END_DATE IS NULL;
374
375 /*SELECT item_comp_detail_id
376 FROM AHL_ITEM_COMP_DETAILS
377 WHERE item_group_id = p_item_group_id AND
378 item_composition_id = (
379 SELECT item_composition_id FROM AHL_ROUTE_EFFECTIVITIES_V
380 WHERE route_effectivity_id = p_route_effecitivity_id
381 ) AND
382 effective_end_date IS NULL;*/
383
384
385
386
387 l_return_status VARCHAR2(1);
388 l_msg_data VARCHAR2(2000);
389 l_item_comp_detail_id NUMBER;
390
391 BEGIN
392 x_return_status := FND_API.G_RET_STS_SUCCESS;
393
394 -- Convert / Validate Item Composition
395 IF ( p_association_type = 'DISPOSITION' AND
396 p_x_material_req_rec.item_comp_detail_id IS NOT NULL AND
397 p_x_material_req_rec.item_comp_detail_id <> FND_API.G_MISS_NUM ) THEN
398
399 AHL_RM_ROUTE_UTIL.validate_item_comp
400 (
401 x_return_status => l_return_status,
402 x_msg_data => l_msg_data,
403 p_x_item_comp_detail_id => p_x_material_req_rec.item_comp_detail_id
404 );
405
406
407 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
408 FND_MESSAGE.set_name( 'AHL', l_msg_data );
409 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_material_req_rec.item_comp_detail_id ) );
410 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_material_req_rec ) );
411 FND_MSG_PUB.add;
412 x_return_status := l_return_status;
413 END IF;
414
415 END IF;
416
417 -- Convert / Validate Position path
418 IF ( p_association_type = 'DISPOSITION' AND
419 ( p_x_material_req_rec.position_path_id IS NOT NULL AND
420 p_x_material_req_rec.position_path_id <> FND_API.G_MISS_NUM ) OR
421 ( p_x_material_req_rec.position_path IS NOT NULL AND
422 p_x_material_req_rec.position_path <> FND_API.G_MISS_CHAR ) ) THEN
423
424 AHL_RM_ROUTE_UTIL.validate_position_path
425 (
426 x_return_status => l_return_status,
427 x_msg_data => l_msg_data,
428 p_position_path => p_x_material_req_rec.position_path,
429 p_x_position_path_id => p_x_material_req_rec.position_path_id
430 );
431
432 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
433 FND_MESSAGE.set_name( 'AHL', l_msg_data );
434
435 IF ( p_x_material_req_rec.position_path IS NULL OR
436 p_x_material_req_rec.position_path = FND_API.G_MISS_CHAR ) THEN
437 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_material_req_rec.position_path_id ) );
438 ELSE
439 FND_MESSAGE.set_token( 'FIELD', p_x_material_req_rec.position_path );
440 END IF;
441
442 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_material_req_rec ) );
443 FND_MSG_PUB.add;
444 x_return_status := l_return_status;
445 END IF;
446
447 END IF;
448
449 -- Convert / Validate Item Group
450 IF ( ( p_x_material_req_rec.item_group_id IS NOT NULL AND
451 p_x_material_req_rec.item_group_id <> FND_API.G_MISS_NUM ) OR
452 ( p_x_material_req_rec.item_group_name IS NOT NULL AND
453 p_x_material_req_rec.item_group_name <> FND_API.G_MISS_CHAR ) ) THEN
454
455
456 AHL_RM_ROUTE_UTIL.validate_item_group
457 (
458 x_return_status => l_return_status,
459 x_msg_data => l_msg_data,
460 p_association_type => p_association_type,
461 p_item_group_name => p_x_material_req_rec.item_group_name,
462 p_x_item_group_id => p_x_material_req_rec.item_group_id
463 );
464
465 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
466 FND_MESSAGE.set_name( 'AHL', l_msg_data );
467
468 IF ( p_x_material_req_rec.item_group_name IS NULL OR
469 p_x_material_req_rec.item_group_name = FND_API.G_MISS_CHAR ) THEN
470 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_material_req_rec.item_group_id ) );
471 ELSE
472 FND_MESSAGE.set_token( 'FIELD', p_x_material_req_rec.item_group_name );
473 END IF;
474
475 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_material_req_rec ) );
476 FND_MSG_PUB.add;
477 x_return_status := l_return_status;
478 END IF;
479
480 --Check if the item group is from composition and is a valid composition element if
481 -- comp material flag is set.
482 IF p_x_material_req_rec.COMP_MATERIAL_FLAG = 'Y' OR p_x_material_req_rec.COMP_MATERIAL_FLAG = 'y'
483 THEN
484 -- Find if it a MC-Route Association or Item-Route Association
485 IF ( p_x_material_req_rec.position_path_id IS NULL OR
486 p_x_material_req_rec.position_path_id = FND_API.G_MISS_NUM ) AND
487 ( p_x_material_req_rec.item_comp_detail_id IS NULL OR
488 p_x_material_req_rec.item_comp_detail_id = FND_API.G_MISS_NUM )
489 THEN
490 OPEN get_item_comp_detail_id(p_object_id,
491 p_x_material_req_rec.item_group_id);
492 FETCH get_item_comp_detail_id INTO l_item_comp_detail_id;
493 CLOSE get_item_comp_detail_id;
494
495 IF l_item_comp_detail_id IS NOT NULL
496 THEN
497 p_x_material_req_rec.item_comp_detail_id := l_item_comp_detail_id;
498 ELSE
499 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INV_ITEM_COMP_MAT' );
500 FND_MESSAGE.set_token( 'FIELD',p_x_material_req_rec.item_group_name);
501 FND_MSG_PUB.add;
502 x_return_status := FND_API.G_RET_STS_ERROR;
503 END IF; -- l_item_comp_detail_id is null
504 END IF; -- which association if
505 END IF;-- comp_material_flag check if
506
507 END IF; -- Item group id check
508
509 -- Convert / Validate Item
510
511 IF ( ( p_x_material_req_rec.inventory_item_id IS NOT NULL AND
512 p_x_material_req_rec.inventory_item_id <> FND_API.G_MISS_NUM AND
513 p_x_material_req_rec.inventory_org_id IS NOT NULL AND
514 p_x_material_req_rec.inventory_org_id <> FND_API.G_MISS_NUM ) OR
515 ( p_x_material_req_rec.item_number IS NOT NULL AND
516 p_x_material_req_rec.item_number <> FND_API.G_MISS_CHAR ) ) THEN
517 IF ( p_association_type <> 'DISPOSITION' )
518 THEN
519 AHL_RM_ROUTE_UTIL.validate_item
520 (
521 x_return_status => l_return_status,
522 x_msg_data => l_msg_data,
523 p_item_number => p_x_material_req_rec.item_number,
524 p_x_inventory_item_id => p_x_material_req_rec.inventory_item_id,
525 p_x_inventory_org_id => p_x_material_req_rec.inventory_org_id
526 );
527 ELSE
528 AHL_RM_ROUTE_UTIL.validate_adt_item
529 (
530 x_return_status => l_return_status,
531 x_msg_data => l_msg_data,
532 p_item_number => p_x_material_req_rec.item_number,
533 p_x_inventory_item_id => p_x_material_req_rec.inventory_item_id,
534 p_x_inventory_org_id => p_x_material_req_rec.inventory_org_id
535 );
536 END IF;
537
538 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
539 IF ( l_msg_data = 'AHL_COM_INVALID_ITEM' ) THEN
540 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_ITEM' );
541 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_ITEMS' ) THEN
542 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_ITEMS' );
543 ELSIF ( l_msg_data = 'AHL_COM_SERVICE_ITEM' ) THEN
544 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_SERVICE_ITEM' );
545 ELSE
546 FND_MESSAGE.set_name( 'AHL', l_msg_data );
547 END IF;
548
549 IF ( p_x_material_req_rec.item_number IS NULL OR
550 p_x_material_req_rec.item_number = FND_API.G_MISS_CHAR ) THEN
551 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_material_req_rec.inventory_item_id ) || '-' || TO_CHAR( p_x_material_req_rec.inventory_org_id ) );
552 ELSE
553 FND_MESSAGE.set_token( 'FIELD', p_x_material_req_rec.item_number );
554 END IF;
555
556 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_material_req_rec ) );
557 FND_MSG_PUB.add;
558 x_return_status := l_return_status;
559 END IF;
560
561 END IF;
562
563 -- Convert / Validate UOM
564 IF ( ( p_x_material_req_rec.uom_code IS NOT NULL AND
565 p_x_material_req_rec.uom_code <> FND_API.G_MISS_CHAR ) OR
566 ( p_x_material_req_rec.uom IS NOT NULL AND
567 p_x_material_req_rec.uom <> FND_API.G_MISS_CHAR ) )
568 THEN
569 AHL_RM_ROUTE_UTIL.validate_uom
570 (
571 x_return_status => l_return_status,
572 x_msg_data => l_msg_data,
573 p_uom => p_x_material_req_rec.uom,
574 p_x_uom_code => p_x_material_req_rec.uom_code
575 );
576
577 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
578 FND_MESSAGE.set_name( 'AHL', l_msg_data );
579
580 IF ( p_x_material_req_rec.uom IS NULL OR
581 p_x_material_req_rec.uom = FND_API.G_MISS_CHAR ) THEN
582 FND_MESSAGE.set_token( 'FIELD', p_x_material_req_rec.uom_code );
583 ELSE
584 FND_MESSAGE.set_token( 'FIELD', p_x_material_req_rec.uom );
585 END IF;
586
587 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_material_req_rec ) );
588 FND_MSG_PUB.add;
589 x_return_status := l_return_status;
590 END IF;
591
592 END IF;
593
594 END convert_values_to_ids;
595
596
597 -- Procedure to perform Value to ID conversion for appropriate attributes
598 PROCEDURE convert_efct_values_to_ids
599 (
600 p_x_route_efct_rec IN OUT NOCOPY route_efct_rec_type,
601 x_return_status OUT NOCOPY VARCHAR2
602 )
603 IS
604
605 l_return_status VARCHAR2(1);
606 l_msg_data VARCHAR2(2000);
607
608 BEGIN
609 x_return_status := FND_API.G_RET_STS_SUCCESS;
610
611 -- Convert / Validate MC
612 IF ( ( p_x_route_efct_rec.MC_ID IS NOT NULL AND
613 p_x_route_efct_rec.MC_ID <> FND_API.G_MISS_NUM ) OR
614 ( p_x_route_efct_rec.MC_NAME IS NOT NULL AND
615 p_x_route_efct_rec.MC_NAME <> FND_API.G_MISS_CHAR ) ) THEN
616
617 AHL_RM_ROUTE_UTIL.validate_master_configuration
618 (
619 x_return_status => l_return_status,
620 x_msg_data => l_msg_data,
621 p_mc_name => p_x_route_efct_rec.MC_NAME,
622 p_x_mc_id => p_x_route_efct_rec.MC_ID,
623 p_mc_revision_number => p_x_route_efct_rec.MC_REVISION,
624 p_x_mc_header_id => p_x_route_efct_rec.MC_HEADER_ID
625 );
626
627 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
628 FND_MESSAGE.set_name( 'AHL', l_msg_data );
629
630 IF ( p_x_route_efct_rec.MC_NAME IS NULL OR
631 p_x_route_efct_rec.MC_NAME = FND_API.G_MISS_CHAR ) THEN
632 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_route_efct_rec.MC_HEADER_ID ) );
633 ELSE
634 FND_MESSAGE.set_token( 'FIELD', p_x_route_efct_rec.MC_NAME );
635 END IF;
636
637 -- FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_material_req_rec(i) ) );
638 FND_MSG_PUB.add;
639 x_return_status := l_return_status;
640 END IF;
641
642 END IF;
643
644 -- Convert / Validate Item
645 IF ( ( p_x_route_efct_rec.inventory_item_id IS NOT NULL AND
646 p_x_route_efct_rec.inventory_item_id <> FND_API.G_MISS_NUM AND
647 p_x_route_efct_rec.inventory_master_org_id IS NOT NULL AND
648 p_x_route_efct_rec.inventory_master_org_id <> FND_API.G_MISS_NUM ) OR
649 ( (p_x_route_efct_rec.ORGANIZATION_CODE IS NOT NULL AND p_x_route_efct_rec.ORGANIZATION_CODE <> FND_API.G_MISS_CHAR )
650 AND(p_x_route_efct_rec.item_number IS NOT NULL AND p_x_route_efct_rec.item_number <> FND_API.G_MISS_CHAR )
651 )
652 )THEN
653
654 AHL_RM_ROUTE_UTIL.validate_effectivity_item
655 (
656 x_return_status => l_return_status,
657 x_msg_data => l_msg_data,
658 p_item_number => p_x_route_efct_rec.item_number,
659 p_org_code => p_x_route_efct_rec.ORGANIZATION_CODE,
660 p_x_inventory_item_id => p_x_route_efct_rec.inventory_item_id,
661 p_x_inventory_org_id => p_x_route_efct_rec.inventory_master_org_id
662 );
663
664 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
665 IF ( l_msg_data = 'AHL_COM_INVALID_ITEM' ) THEN
666 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_ITEM' );
667 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_ITEMS' ) THEN
668 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_ITEMS' );
669 ELSIF ( l_msg_data = 'AHL_COM_effectivity_ITEM' ) THEN
670 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_effectivity_ITEM' );
671 ELSE
672 FND_MESSAGE.set_name( 'AHL', l_msg_data );
673 END IF;
674
675 IF ( p_x_route_efct_rec.item_number IS NULL OR
676 p_x_route_efct_rec.item_number = FND_API.G_MISS_CHAR ) THEN
677 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_route_efct_rec.inventory_item_id ) || '-' || TO_CHAR( p_x_route_efct_rec.inventory_master_org_id ) );
678 ELSE
679 FND_MESSAGE.set_token( 'FIELD', p_x_route_efct_rec.item_number );
680 END IF;
681
682 -- FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_material_req_rec ) );
683 FND_MSG_PUB.add;
684 x_return_status := l_return_status;
685 END IF;
686
687 END IF;
688
689
690 END convert_efct_values_to_ids;
691
692 -- Procedure to add Default values for missing attributes (CREATE)
693 PROCEDURE default_missing_attributes
694 (
695 p_x_material_req_rec IN OUT NOCOPY material_req_rec_type
696 )
697 IS
698
699 BEGIN
700
701 -- Convert G_MISS values to NULL
702
703 IF ( p_x_material_req_rec.item_comp_detail_id = FND_API.G_MISS_NUM ) THEN
704 p_x_material_req_rec.item_comp_detail_id := null;
705 END IF;
706
707 IF ( p_x_material_req_rec.position_path_id = FND_API.G_MISS_NUM ) THEN
708 p_x_material_req_rec.position_path_id := null;
709 END IF;
710
711 IF ( p_x_material_req_rec.position_path = FND_API.G_MISS_CHAR ) THEN
712 p_x_material_req_rec.position_path := null;
713 END IF;
714
715 IF ( p_x_material_req_rec.item_group_id = FND_API.G_MISS_NUM ) THEN
716 p_x_material_req_rec.item_group_id := null;
717 END IF;
718
719 IF ( p_x_material_req_rec.item_group_name = FND_API.G_MISS_CHAR ) THEN
720 p_x_material_req_rec.item_group_name := null;
721 END IF;
722
723 IF ( p_x_material_req_rec.inventory_item_id = FND_API.G_MISS_NUM ) THEN
724 p_x_material_req_rec.inventory_item_id := null;
725 END IF;
726
727 IF ( p_x_material_req_rec.inventory_org_id = FND_API.G_MISS_NUM ) THEN
728 p_x_material_req_rec.inventory_org_id := null;
729 END IF;
730
731 IF ( p_x_material_req_rec.item_number = FND_API.G_MISS_CHAR ) THEN
732 p_x_material_req_rec.item_number := null;
733 END IF;
734
735 IF ( p_x_material_req_rec.uom_code = FND_API.G_MISS_CHAR ) THEN
736 p_x_material_req_rec.uom_code := null;
737 END IF;
738
739 IF ( p_x_material_req_rec.uom = FND_API.G_MISS_CHAR ) THEN
740 p_x_material_req_rec.uom := null;
741 END IF;
742
743 IF ( p_x_material_req_rec.quantity = FND_API.G_MISS_NUM ) THEN
744 p_x_material_req_rec.quantity := null;
745 END IF;
746
747 IF ( p_x_material_req_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
748 p_x_material_req_rec.attribute_category := null;
749 END IF;
750
751 IF ( p_x_material_req_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
752 p_x_material_req_rec.attribute1 := null;
753 END IF;
754
755 IF ( p_x_material_req_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
756 p_x_material_req_rec.attribute2 := null;
757 END IF;
758
759 IF ( p_x_material_req_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
760 p_x_material_req_rec.attribute3 := null;
761 END IF;
762
763 IF ( p_x_material_req_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
764 p_x_material_req_rec.attribute4 := null;
765 END IF;
766
767 IF ( p_x_material_req_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
768 p_x_material_req_rec.attribute5 := null;
769 END IF;
770
771 IF ( p_x_material_req_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
772 p_x_material_req_rec.attribute6 := null;
773 END IF;
774
775 IF ( p_x_material_req_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
776 p_x_material_req_rec.attribute7 := null;
777 END IF;
778
779 IF ( p_x_material_req_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
780 p_x_material_req_rec.attribute8 := null;
781 END IF;
782
783 IF ( p_x_material_req_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
784 p_x_material_req_rec.attribute9 := null;
785 END IF;
786
787 IF ( p_x_material_req_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
788 p_x_material_req_rec.attribute10 := null;
789 END IF;
790
791 IF ( p_x_material_req_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
792 p_x_material_req_rec.attribute11 := null;
793 END IF;
794
795 IF ( p_x_material_req_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
796 p_x_material_req_rec.attribute12 := null;
797 END IF;
798
799 IF ( p_x_material_req_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
800 p_x_material_req_rec.attribute13 := null;
801 END IF;
802
803 IF ( p_x_material_req_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
804 p_x_material_req_rec.attribute14 := null;
805 END IF;
806
807 IF ( p_x_material_req_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
808 p_x_material_req_rec.attribute15 := null;
809 END IF;
810
811 IF ( p_x_material_req_rec.replace_percent = FND_API.G_MISS_NUM ) THEN
812 p_x_material_req_rec.replace_percent := null;
813 END IF;
814
815 IF ( p_x_material_req_rec.rework_percent = FND_API.G_MISS_NUM ) THEN
816 p_x_material_req_rec.rework_percent := null;
817 END IF;
818
819 IF ( p_x_material_req_rec.exclude_flag = FND_API.G_MISS_CHAR ) THEN
820 p_x_material_req_rec.exclude_flag := null;
821 END IF;
822
823 --pdoki added for OGMA 105 issue
824 IF ( p_x_material_req_rec.in_service = FND_API.G_MISS_CHAR ) THEN
825 p_x_material_req_rec.in_service := null;
826 END IF;
827
828 END default_missing_attributes;
829
830
831 -- Procedure to add Default values for missing attributes (CREATE)
832 PROCEDURE default_efct_miss_attributes
833 (
834 p_x_route_efct_rec IN OUT NOCOPY route_efct_rec_type
835 )
836 IS
837
838 BEGIN
839
840 -- Convert G_MISS values to NULL
841 IF ( p_x_route_efct_rec.mc_id = FND_API.G_MISS_NUM ) THEN
842 p_x_route_efct_rec.mc_id := null;
843 END IF;
844
845 IF ( p_x_route_efct_rec.mc_header_id = FND_API.G_MISS_NUM ) THEN
846 p_x_route_efct_rec.mc_header_id := null;
847 END IF;
848
849 IF ( p_x_route_efct_rec.mc_name = FND_API.G_MISS_CHAR ) THEN
850 p_x_route_efct_rec.mc_name := null;
851 END IF;
852
853 IF ( p_x_route_efct_rec.inventory_item_id = FND_API.G_MISS_NUM ) THEN
854 p_x_route_efct_rec.inventory_item_id := null;
855 END IF;
856
857 IF ( p_x_route_efct_rec.inventory_master_org_id = FND_API.G_MISS_NUM ) THEN
858 p_x_route_efct_rec.inventory_master_org_id := null;
859 END IF;
860
861 IF ( p_x_route_efct_rec.item_number = FND_API.G_MISS_CHAR ) THEN
862 p_x_route_efct_rec.item_number := null;
863 END IF;
864
865 IF ( p_x_route_efct_rec.ORGANIZATION_CODE = FND_API.G_MISS_CHAR ) THEN
866 p_x_route_efct_rec.ORGANIZATION_CODE := null;
867 END IF;
868
869 IF ( p_x_route_efct_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
870 p_x_route_efct_rec.attribute_category := null;
871 END IF;
872
873 IF ( p_x_route_efct_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
874 p_x_route_efct_rec.attribute1 := null;
875 END IF;
876
877 IF ( p_x_route_efct_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
878 p_x_route_efct_rec.attribute2 := null;
879 END IF;
880
881 IF ( p_x_route_efct_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
882 p_x_route_efct_rec.attribute3 := null;
883 END IF;
884
885 IF ( p_x_route_efct_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
886 p_x_route_efct_rec.attribute4 := null;
887 END IF;
888
889 IF ( p_x_route_efct_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
890 p_x_route_efct_rec.attribute5 := null;
891 END IF;
892
893 IF ( p_x_route_efct_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
894 p_x_route_efct_rec.attribute6 := null;
895 END IF;
896
897 IF ( p_x_route_efct_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
898 p_x_route_efct_rec.attribute7 := null;
899 END IF;
900
901 IF ( p_x_route_efct_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
902 p_x_route_efct_rec.attribute8 := null;
903 END IF;
904
905 IF ( p_x_route_efct_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
906 p_x_route_efct_rec.attribute9 := null;
907 END IF;
908
909 IF ( p_x_route_efct_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
910 p_x_route_efct_rec.attribute10 := null;
911 END IF;
912
913 IF ( p_x_route_efct_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
914 p_x_route_efct_rec.attribute11 := null;
915 END IF;
916
917 IF ( p_x_route_efct_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
918 p_x_route_efct_rec.attribute12 := null;
919 END IF;
920
921 IF ( p_x_route_efct_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
922 p_x_route_efct_rec.attribute13 := null;
923 END IF;
924
925 IF ( p_x_route_efct_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
926 p_x_route_efct_rec.attribute14 := null;
927 END IF;
928
929 IF ( p_x_route_efct_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
930 p_x_route_efct_rec.attribute15 := null;
931 END IF;
932
933 END default_efct_miss_attributes;
934
935
936 -- Procedure to add Default values for unchanged attributes (UPDATE)
937 PROCEDURE default_unchanged_attributes
938 (
939 p_x_material_req_rec IN OUT NOCOPY material_req_rec_type
940 )
941 IS
942
943 l_old_material_req_rec material_req_rec_type;
944
945 CURSOR get_old_rec ( c_rt_oper_material_id NUMBER )
946 IS
947 SELECT item_group_id,
948 item_group_name,
949 inventory_item_id,
950 inventory_org_id,
951 item_number,
952 item_comp_detail_id,
953 position_path_id,
954 position_path,
955 uom_code,
956 uom,
957 quantity,
958 attribute_category,
959 attribute1,
960 attribute2,
961 attribute3,
962 attribute4,
963 attribute5,
964 attribute6,
965 attribute7,
966 attribute8,
967 attribute9,
968 attribute10,
969 attribute11,
970 attribute12,
971 attribute13,
972 attribute14,
973 attribute15,
974 exclude_flag,
975 rework_percent,
976 replace_percent,
977 in_service --pdoki added for OGMA 105 issue
978 FROM AHL_RT_OPER_MATERIALS_V
979 WHERE rt_oper_material_id = c_rt_oper_material_id;
980
981 BEGIN
982
983 -- Get the old record from AHL_RT_OPER_MATERIALS.
984 OPEN get_old_rec( p_x_material_req_rec.rt_oper_material_id );
985
986 FETCH get_old_rec INTO
987 l_old_material_req_rec.item_group_id,
988 l_old_material_req_rec.item_group_name,
989 l_old_material_req_rec.inventory_item_id,
990 l_old_material_req_rec.inventory_org_id,
991 l_old_material_req_rec.item_number,
992 l_old_material_req_rec.item_comp_detail_id,
993 l_old_material_req_rec.position_path_id,
994 l_old_material_req_rec.position_path,
995 l_old_material_req_rec.uom_code,
996 l_old_material_req_rec.uom,
997 l_old_material_req_rec.quantity,
998 l_old_material_req_rec.attribute_category,
999 l_old_material_req_rec.attribute1,
1000 l_old_material_req_rec.attribute2,
1001 l_old_material_req_rec.attribute3,
1002 l_old_material_req_rec.attribute4,
1003 l_old_material_req_rec.attribute5,
1004 l_old_material_req_rec.attribute6,
1005 l_old_material_req_rec.attribute7,
1006 l_old_material_req_rec.attribute8,
1007 l_old_material_req_rec.attribute9,
1008 l_old_material_req_rec.attribute10,
1009 l_old_material_req_rec.attribute11,
1010 l_old_material_req_rec.attribute12,
1011 l_old_material_req_rec.attribute13,
1012 l_old_material_req_rec.attribute14,
1013 l_old_material_req_rec.attribute15,
1014 l_old_material_req_rec.exclude_flag,
1015 l_old_material_req_rec.rework_percent,
1016 l_old_material_req_rec.replace_percent,
1017 l_old_material_req_rec.in_service --pdoki added for OGMA 105 issue
1018 ;
1019
1020 IF get_old_rec%NOTFOUND THEN
1021 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_MTL_REC' );
1022 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_material_req_rec ) );
1023 FND_MSG_PUB.add;
1024 CLOSE get_old_rec;
1025 RAISE FND_API.G_EXC_ERROR;
1026 END IF;
1027
1028 CLOSE get_old_rec;
1029
1030 -- Convert G_MISS values to NULL and NULL values to Old values
1031
1032 IF ( p_x_material_req_rec.item_comp_detail_id = FND_API.G_MISS_NUM ) THEN
1033 p_x_material_req_rec.item_comp_detail_id := null;
1034 ELSIF ( p_x_material_req_rec.item_comp_detail_id IS NULL ) THEN
1035 p_x_material_req_rec.item_comp_detail_id := l_old_material_req_rec.item_comp_detail_id;
1036 END IF;
1037
1038 IF ( p_x_material_req_rec.position_path_id = FND_API.G_MISS_NUM ) THEN
1039 p_x_material_req_rec.position_path_id := null;
1040 ELSIF ( p_x_material_req_rec.position_path_id IS NULL ) THEN
1041 p_x_material_req_rec.position_path_id := l_old_material_req_rec.position_path_id;
1042 END IF;
1043
1044 IF ( p_x_material_req_rec.position_path = FND_API.G_MISS_CHAR ) THEN
1045 p_x_material_req_rec.position_path := null;
1046 ELSIF ( p_x_material_req_rec.position_path IS NULL ) THEN
1047 p_x_material_req_rec.position_path := l_old_material_req_rec.position_path;
1048 END IF;
1049
1050 IF ( p_x_material_req_rec.item_group_id = FND_API.G_MISS_NUM ) THEN
1051 p_x_material_req_rec.item_group_id := null;
1052 ELSIF ( p_x_material_req_rec.item_group_id IS NULL ) THEN
1053 p_x_material_req_rec.item_group_id := l_old_material_req_rec.item_group_id;
1054 END IF;
1055
1056 IF ( p_x_material_req_rec.item_group_name = FND_API.G_MISS_CHAR ) THEN
1057 p_x_material_req_rec.item_group_name := null;
1058 ELSIF ( p_x_material_req_rec.item_group_name IS NULL ) THEN
1059 p_x_material_req_rec.item_group_name := l_old_material_req_rec.item_group_name;
1060 END IF;
1061
1062 IF ( p_x_material_req_rec.inventory_item_id = FND_API.G_MISS_NUM ) THEN
1063 p_x_material_req_rec.inventory_item_id := null;
1064 ELSIF ( p_x_material_req_rec.inventory_item_id IS NULL ) THEN
1065 p_x_material_req_rec.inventory_item_id := l_old_material_req_rec.inventory_item_id;
1066 END IF;
1067
1068 IF ( p_x_material_req_rec.inventory_org_id = FND_API.G_MISS_NUM ) THEN
1069 p_x_material_req_rec.inventory_org_id := null;
1070 ELSIF ( p_x_material_req_rec.inventory_org_id IS NULL ) THEN
1071 p_x_material_req_rec.inventory_org_id := l_old_material_req_rec.inventory_org_id;
1072 END IF;
1073
1074 IF ( p_x_material_req_rec.item_number = FND_API.G_MISS_CHAR ) THEN
1075 p_x_material_req_rec.item_number := null;
1076 ELSIF ( p_x_material_req_rec.item_number IS NULL ) THEN
1077 p_x_material_req_rec.item_number := l_old_material_req_rec.item_number;
1078 END IF;
1079
1080 IF ( p_x_material_req_rec.uom_code = FND_API.G_MISS_CHAR ) THEN
1081 p_x_material_req_rec.uom_code := null;
1082 ELSIF ( p_x_material_req_rec.uom_code IS NULL ) THEN
1083 p_x_material_req_rec.uom_code := l_old_material_req_rec.uom_code;
1084 END IF;
1085
1086 IF ( p_x_material_req_rec.uom = FND_API.G_MISS_CHAR ) THEN
1087 p_x_material_req_rec.uom := null;
1088 ELSIF ( p_x_material_req_rec.uom IS NULL ) THEN
1089 p_x_material_req_rec.uom := l_old_material_req_rec.uom;
1090 END IF;
1091
1092 IF ( p_x_material_req_rec.quantity = FND_API.G_MISS_NUM ) THEN
1093 p_x_material_req_rec.quantity := null;
1094 ELSIF ( p_x_material_req_rec.quantity IS NULL ) THEN
1095 p_x_material_req_rec.quantity := l_old_material_req_rec.quantity;
1096 END IF;
1097
1098 IF ( p_x_material_req_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
1099 p_x_material_req_rec.attribute_category := null;
1100 ELSIF ( p_x_material_req_rec.attribute_category IS NULL ) THEN
1101 p_x_material_req_rec.attribute_category := l_old_material_req_rec.attribute_category;
1102 END IF;
1103
1104 IF ( p_x_material_req_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
1105 p_x_material_req_rec.attribute1 := null;
1106 ELSIF ( p_x_material_req_rec.attribute1 IS NULL ) THEN
1107 p_x_material_req_rec.attribute1 := l_old_material_req_rec.attribute1;
1108 END IF;
1109
1110 IF ( p_x_material_req_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
1111 p_x_material_req_rec.attribute2 := null;
1112 ELSIF ( p_x_material_req_rec.attribute2 IS NULL ) THEN
1113 p_x_material_req_rec.attribute2 := l_old_material_req_rec.attribute2;
1114 END IF;
1115
1116 IF ( p_x_material_req_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
1117 p_x_material_req_rec.attribute3 := null;
1118 ELSIF ( p_x_material_req_rec.attribute3 IS NULL ) THEN
1119 p_x_material_req_rec.attribute3 := l_old_material_req_rec.attribute3;
1120 END IF;
1121
1122 IF ( p_x_material_req_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
1123 p_x_material_req_rec.attribute4 := null;
1124 ELSIF ( p_x_material_req_rec.attribute4 IS NULL ) THEN
1125 p_x_material_req_rec.attribute4 := l_old_material_req_rec.attribute4;
1126 END IF;
1127
1128 IF ( p_x_material_req_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
1129 p_x_material_req_rec.attribute5 := null;
1130 ELSIF ( p_x_material_req_rec.attribute5 IS NULL ) THEN
1131 p_x_material_req_rec.attribute5 := l_old_material_req_rec.attribute5;
1132 END IF;
1133
1134 IF ( p_x_material_req_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
1135 p_x_material_req_rec.attribute6 := null;
1136 ELSIF ( p_x_material_req_rec.attribute6 IS NULL ) THEN
1137 p_x_material_req_rec.attribute6 := l_old_material_req_rec.attribute6;
1138 END IF;
1139
1140 IF ( p_x_material_req_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
1141 p_x_material_req_rec.attribute7 := null;
1142 ELSIF ( p_x_material_req_rec.attribute7 IS NULL ) THEN
1143 p_x_material_req_rec.attribute7 := l_old_material_req_rec.attribute7;
1144 END IF;
1145
1146 IF ( p_x_material_req_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
1147 p_x_material_req_rec.attribute8 := null;
1148 ELSIF ( p_x_material_req_rec.attribute8 IS NULL ) THEN
1149 p_x_material_req_rec.attribute8 := l_old_material_req_rec.attribute8;
1150 END IF;
1151
1152 IF ( p_x_material_req_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
1153 p_x_material_req_rec.attribute9 := null;
1154 ELSIF ( p_x_material_req_rec.attribute9 IS NULL ) THEN
1155 p_x_material_req_rec.attribute9 := l_old_material_req_rec.attribute9;
1156 END IF;
1157
1158 IF ( p_x_material_req_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
1159 p_x_material_req_rec.attribute10 := null;
1160 ELSIF ( p_x_material_req_rec.attribute10 IS NULL ) THEN
1161 p_x_material_req_rec.attribute10 := l_old_material_req_rec.attribute10;
1162 END IF;
1163
1164 IF ( p_x_material_req_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
1165 p_x_material_req_rec.attribute11 := null;
1166 ELSIF ( p_x_material_req_rec.attribute11 IS NULL ) THEN
1167 p_x_material_req_rec.attribute11 := l_old_material_req_rec.attribute11;
1168 END IF;
1169
1170 IF ( p_x_material_req_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
1171 p_x_material_req_rec.attribute12 := null;
1172 ELSIF ( p_x_material_req_rec.attribute12 IS NULL ) THEN
1173 p_x_material_req_rec.attribute12 := l_old_material_req_rec.attribute12;
1174 END IF;
1175
1176 IF ( p_x_material_req_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
1177 p_x_material_req_rec.attribute13 := null;
1178 ELSIF ( p_x_material_req_rec.attribute13 IS NULL ) THEN
1179 p_x_material_req_rec.attribute13 := l_old_material_req_rec.attribute13;
1180 END IF;
1181
1182 IF ( p_x_material_req_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
1183 p_x_material_req_rec.attribute14 := null;
1184 ELSIF ( p_x_material_req_rec.attribute14 IS NULL ) THEN
1185 p_x_material_req_rec.attribute14 := l_old_material_req_rec.attribute14;
1186 END IF;
1187
1188 IF ( p_x_material_req_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
1189 p_x_material_req_rec.attribute15 := null;
1190 ELSIF ( p_x_material_req_rec.attribute15 IS NULL ) THEN
1191 p_x_material_req_rec.attribute15 := l_old_material_req_rec.attribute15;
1192 END IF;
1193
1194 IF ( p_x_material_req_rec.exclude_flag = FND_API.G_MISS_CHAR ) THEN
1195 p_x_material_req_rec.exclude_flag := null;
1196 ELSIF ( p_x_material_req_rec.exclude_flag IS NULL ) THEN
1197 p_x_material_req_rec.exclude_flag := l_old_material_req_rec.exclude_flag;
1198 END IF;
1199
1200 --pdoki added for OGMA 105 issue
1201 IF ( p_x_material_req_rec.in_service = FND_API.G_MISS_CHAR ) THEN
1202 p_x_material_req_rec.in_service := null;
1203 ELSIF ( p_x_material_req_rec.in_service IS NULL ) THEN
1204 p_x_material_req_rec.in_service := l_old_material_req_rec.in_service;
1205 END IF;
1206
1207 IF ( p_x_material_req_rec.rework_percent = FND_API.G_MISS_NUM ) THEN
1208 p_x_material_req_rec.rework_percent := null;
1209 ELSIF ( p_x_material_req_rec.rework_percent IS NULL ) THEN
1210 p_x_material_req_rec.rework_percent := l_old_material_req_rec.rework_percent;
1211 END IF;
1212
1213 IF ( p_x_material_req_rec.replace_percent = FND_API.G_MISS_NUM ) THEN
1214 p_x_material_req_rec.replace_percent := null;
1215 ELSIF ( p_x_material_req_rec.replace_percent IS NULL ) THEN
1216 p_x_material_req_rec.replace_percent := l_old_material_req_rec.replace_percent;
1217 END IF;
1218
1219 END default_unchanged_attributes;
1220
1221 -- Procedure to add Default values for unchanged attributes (UPDATE)
1222 PROCEDURE default_efct_unchange_attribs
1223 (
1224 p_x_route_efct_rec IN OUT NOCOPY route_efct_rec_type
1225 )
1226 IS
1227
1228 l_old_route_efct_rec route_efct_rec_type;
1229
1230 CURSOR get_old_rec ( C_ROUTE_EFFECTIVITY_ID NUMBER )
1231 IS
1232 SELECT
1233 inventory_item_id
1234 , inventory_master_org_id
1235 , concatenated_segments
1236 , item_description
1237 , organization_code
1238 , mc_id
1239 , mc_name
1240 , mc_revision
1241 , mc_header_id
1242 , attribute_category
1243 , attribute1
1244 , attribute2
1245 , attribute3
1246 , attribute4
1247 , attribute5
1248 , attribute6
1249 , attribute7
1250 , attribute8
1251 , attribute9
1252 , attribute10
1253 , attribute11
1254 , attribute12
1255 , attribute13
1256 , attribute14
1257 , attribute15
1258 from ahl_route_effectivities_v
1259 WHERE ROUTE_EFFECTIVITY_ID = C_ROUTE_EFFECTIVITY_ID
1260 ;
1261
1262 BEGIN
1263
1264 -- Get the old record from AHL_RT_OPER_MATERIALS.
1265 OPEN get_old_rec( p_x_route_efct_rec.ROUTE_EFFECTIVITY_ID );
1266
1267 FETCH get_old_rec INTO
1268 l_old_route_efct_rec.inventory_item_id
1269 ,l_old_route_efct_rec.inventory_master_org_id
1270 ,l_old_route_efct_rec.ITEM_NUMBER
1271 ,l_old_route_efct_rec.description
1272 ,l_old_route_efct_rec.organization_code
1273 ,l_old_route_efct_rec.mc_id
1274 ,l_old_route_efct_rec.MC_NAME
1275 ,l_old_route_efct_rec.mc_revision
1276 ,l_old_route_efct_rec.mc_header_id
1277 ,l_old_route_efct_rec.attribute_category
1278 ,l_old_route_efct_rec.attribute1
1279 ,l_old_route_efct_rec.attribute2
1280 ,l_old_route_efct_rec.attribute3
1281 ,l_old_route_efct_rec.attribute4
1282 ,l_old_route_efct_rec.attribute5
1283 ,l_old_route_efct_rec.attribute6
1284 ,l_old_route_efct_rec.attribute7
1285 ,l_old_route_efct_rec.attribute8
1286 ,l_old_route_efct_rec.attribute9
1287 ,l_old_route_efct_rec.attribute10
1288 ,l_old_route_efct_rec.attribute11
1289 ,l_old_route_efct_rec.attribute12
1290 ,l_old_route_efct_rec.attribute13
1291 ,l_old_route_efct_rec.attribute14
1292 ,l_old_route_efct_rec.attribute15 ;
1293
1294 IF get_old_rec%NOTFOUND THEN
1295 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_efct_REC' );
1296 -- FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_route_efct_rec ) );
1297 FND_MSG_PUB.add;
1298 CLOSE get_old_rec;
1299 RAISE FND_API.G_EXC_ERROR;
1300 END IF;
1301
1302 CLOSE get_old_rec;
1303
1304 -- Convert G_MISS values to NULL and NULL values to Old values
1305 IF ( p_x_route_efct_rec.mc_header_id = FND_API.G_MISS_NUM ) THEN
1306 p_x_route_efct_rec.mc_header_id := null;
1307 ELSIF ( p_x_route_efct_rec.mc_header_id IS NULL ) THEN
1308 p_x_route_efct_rec.mc_header_id := l_old_route_efct_rec.mc_header_id;
1309 END IF;
1310
1311 IF ( p_x_route_efct_rec.mc_id = FND_API.G_MISS_NUM ) THEN
1312 p_x_route_efct_rec.mc_id := null;
1313 ELSIF ( p_x_route_efct_rec.mc_id IS NULL ) THEN
1314 p_x_route_efct_rec.mc_id := l_old_route_efct_rec.mc_id;
1315 END IF;
1316
1317 IF ( p_x_route_efct_rec.mc_name = FND_API.G_MISS_CHAR ) THEN
1318 p_x_route_efct_rec.mc_name := null;
1319 ELSIF ( p_x_route_efct_rec.mc_name IS NULL ) THEN
1320 p_x_route_efct_rec.mc_name := l_old_route_efct_rec.mc_name;
1321 END IF;
1322
1323 IF ( p_x_route_efct_rec.MC_REVISION = FND_API.G_MISS_CHAR ) THEN
1324 p_x_route_efct_rec.MC_REVISION := null;
1325 ELSIF ( p_x_route_efct_rec.MC_REVISION IS NULL ) THEN
1326 p_x_route_efct_rec.MC_REVISION := l_old_route_efct_rec.MC_REVISION;
1327 END IF;
1328
1329 IF ( p_x_route_efct_rec.inventory_item_id = FND_API.G_MISS_NUM ) THEN
1330 p_x_route_efct_rec.inventory_item_id := null;
1331 ELSIF ( p_x_route_efct_rec.inventory_item_id IS NULL ) THEN
1332 p_x_route_efct_rec.inventory_item_id := l_old_route_efct_rec.inventory_item_id;
1333 END IF;
1334
1335 IF ( p_x_route_efct_rec.inventory_master_org_id = FND_API.G_MISS_NUM ) THEN
1336 p_x_route_efct_rec.inventory_master_org_id := null;
1337 ELSIF ( p_x_route_efct_rec.inventory_master_org_id IS NULL ) THEN
1338 p_x_route_efct_rec.inventory_master_org_id := l_old_route_efct_rec.inventory_master_org_id;
1339 END IF;
1340
1341 IF ( p_x_route_efct_rec.item_number = FND_API.G_MISS_CHAR ) THEN
1342 p_x_route_efct_rec.item_number := null;
1343 ELSIF ( p_x_route_efct_rec.item_number IS NULL ) THEN
1344 p_x_route_efct_rec.item_number := l_old_route_efct_rec.item_number;
1345 END IF;
1346
1347
1348 IF ( p_x_route_efct_rec.organization_code = FND_API.G_MISS_CHAR ) THEN
1349 p_x_route_efct_rec.organization_code := null;
1350 ELSIF ( p_x_route_efct_rec.organization_code IS NULL ) THEN
1351 p_x_route_efct_rec.organization_code := l_old_route_efct_rec.organization_code;
1352 END IF;
1353
1354 IF ( p_x_route_efct_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
1355 p_x_route_efct_rec.attribute_category := null;
1356 ELSIF ( p_x_route_efct_rec.attribute_category IS NULL ) THEN
1357 p_x_route_efct_rec.attribute_category := l_old_route_efct_rec.attribute_category;
1358 END IF;
1359
1360 IF ( p_x_route_efct_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
1361 p_x_route_efct_rec.attribute1 := null;
1362 ELSIF ( p_x_route_efct_rec.attribute1 IS NULL ) THEN
1363 p_x_route_efct_rec.attribute1 := l_old_route_efct_rec.attribute1;
1364 END IF;
1365
1366 IF ( p_x_route_efct_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
1367 p_x_route_efct_rec.attribute2 := null;
1368 ELSIF ( p_x_route_efct_rec.attribute2 IS NULL ) THEN
1369 p_x_route_efct_rec.attribute2 := l_old_route_efct_rec.attribute2;
1370 END IF;
1371
1372 IF ( p_x_route_efct_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
1373 p_x_route_efct_rec.attribute3 := null;
1374 ELSIF ( p_x_route_efct_rec.attribute3 IS NULL ) THEN
1375 p_x_route_efct_rec.attribute3 := l_old_route_efct_rec.attribute3;
1376 END IF;
1377
1378 IF ( p_x_route_efct_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
1379 p_x_route_efct_rec.attribute4 := null;
1380 ELSIF ( p_x_route_efct_rec.attribute4 IS NULL ) THEN
1381 p_x_route_efct_rec.attribute4 := l_old_route_efct_rec.attribute4;
1382 END IF;
1383
1384 IF ( p_x_route_efct_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
1385 p_x_route_efct_rec.attribute5 := null;
1386 ELSIF ( p_x_route_efct_rec.attribute5 IS NULL ) THEN
1387 p_x_route_efct_rec.attribute5 := l_old_route_efct_rec.attribute5;
1388 END IF;
1389
1390 IF ( p_x_route_efct_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
1391 p_x_route_efct_rec.attribute6 := null;
1392 ELSIF ( p_x_route_efct_rec.attribute6 IS NULL ) THEN
1393 p_x_route_efct_rec.attribute6 := l_old_route_efct_rec.attribute6;
1394 END IF;
1395
1396 IF ( p_x_route_efct_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
1397 p_x_route_efct_rec.attribute7 := null;
1398 ELSIF ( p_x_route_efct_rec.attribute7 IS NULL ) THEN
1399 p_x_route_efct_rec.attribute7 := l_old_route_efct_rec.attribute7;
1400 END IF;
1401
1402 IF ( p_x_route_efct_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
1403 p_x_route_efct_rec.attribute8 := null;
1404 ELSIF ( p_x_route_efct_rec.attribute8 IS NULL ) THEN
1405 p_x_route_efct_rec.attribute8 := l_old_route_efct_rec.attribute8;
1406 END IF;
1407
1408 IF ( p_x_route_efct_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
1409 p_x_route_efct_rec.attribute9 := null;
1410 ELSIF ( p_x_route_efct_rec.attribute9 IS NULL ) THEN
1411 p_x_route_efct_rec.attribute9 := l_old_route_efct_rec.attribute9;
1412 END IF;
1413
1414 IF ( p_x_route_efct_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
1415 p_x_route_efct_rec.attribute10 := null;
1416 ELSIF ( p_x_route_efct_rec.attribute10 IS NULL ) THEN
1417 p_x_route_efct_rec.attribute10 := l_old_route_efct_rec.attribute10;
1418 END IF;
1419
1420 IF ( p_x_route_efct_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
1421 p_x_route_efct_rec.attribute11 := null;
1422 ELSIF ( p_x_route_efct_rec.attribute11 IS NULL ) THEN
1423 p_x_route_efct_rec.attribute11 := l_old_route_efct_rec.attribute11;
1424 END IF;
1425
1426 IF ( p_x_route_efct_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
1427 p_x_route_efct_rec.attribute12 := null;
1428 ELSIF ( p_x_route_efct_rec.attribute12 IS NULL ) THEN
1429 p_x_route_efct_rec.attribute12 := l_old_route_efct_rec.attribute12;
1430 END IF;
1431
1432 IF ( p_x_route_efct_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
1433 p_x_route_efct_rec.attribute13 := null;
1434 ELSIF ( p_x_route_efct_rec.attribute13 IS NULL ) THEN
1435 p_x_route_efct_rec.attribute13 := l_old_route_efct_rec.attribute13;
1436 END IF;
1437
1438 IF ( p_x_route_efct_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
1439 p_x_route_efct_rec.attribute14 := null;
1440 ELSIF ( p_x_route_efct_rec.attribute14 IS NULL ) THEN
1441 p_x_route_efct_rec.attribute14 := l_old_route_efct_rec.attribute14;
1442 END IF;
1443
1444 IF ( p_x_route_efct_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
1445 p_x_route_efct_rec.attribute15 := null;
1446 ELSIF ( p_x_route_efct_rec.attribute15 IS NULL ) THEN
1447 p_x_route_efct_rec.attribute15 := l_old_route_efct_rec.attribute15;
1448 END IF;
1449
1450 END default_efct_unchange_attribs;
1451
1452 -- Procedure to validate individual material_req attributes
1453 PROCEDURE validate_attributes
1454 (
1455 p_material_req_rec IN material_req_rec_type,
1456 p_association_type IN VARCHAR2,
1457 x_return_status OUT NOCOPY VARCHAR2
1458 )
1459 IS
1460
1461 CURSOR get_comms_nl_trackable_flag ( c_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
1462 c_inventory_org_id MTL_SYSTEM_ITEMS.organization_id%TYPE )
1463 IS
1464 SELECT DISTINCT MI.comms_nl_trackable_flag
1465 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
1466 WHERE MP.organization_id = MI.organization_id
1467 AND MI.inventory_item_id = c_inventory_item_id
1468 AND MI.organization_id = c_inventory_org_id
1469 AND MI.enabled_flag = 'Y'
1470 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
1471 AND NVL( MI.end_date_active, SYSDATE );
1472
1473 l_comms_nl_trackable_flag MTL_SYSTEM_ITEMS.COMMS_NL_TRACKABLE_FLAG%TYPE;
1474 BEGIN
1475 x_return_status := FND_API.G_RET_STS_SUCCESS;
1476
1477 IF ( p_material_req_rec.dml_operation <> 'D' ) THEN
1478
1479 -- Check if the Quantity does not column contains a null value.
1480 -- Check if the Quantity does not column a value less than or equal to zero.
1481 IF ( p_material_req_rec.dml_operation = 'C' AND
1482 -- p_association_type <> 'DISPOSITION' AND
1483 (p_material_req_rec.quantity IS NULL OR p_material_req_rec.quantity = FND_API.G_MISS_NUM)
1484 ) THEN
1485 IF (p_association_type = 'DISPOSITION')
1486 THEN
1487 FND_MESSAGE.set_name( 'AHL','AHL_RM_DISP_ITEM_QTY_NULL' );
1488 ELSE
1489 FND_MESSAGE.set_name( 'AHL','AHL_RM_MTL_QTY_NULL' );
1490 END IF;
1491 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1492 FND_MSG_PUB.add;
1493 x_return_status := FND_API.G_RET_STS_ERROR;
1494 ELSIF ( p_material_req_rec.dml_operation <> 'D' AND
1495 p_material_req_rec.quantity <= 0 ) THEN
1496 FND_MESSAGE.set_name( 'AHL','AHL_RM_MTL_QTY_LESS_ZERO' );
1497 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1498 FND_MSG_PUB.add;
1499 x_return_status := FND_API.G_RET_STS_ERROR;
1500 ELSIF ( p_material_req_rec.dml_operation <> 'D' AND
1501 p_association_type = 'DISPOSITION'
1502 AND
1503 (p_material_req_rec.INVENTORY_ITEM_ID IS NOT NULL OR p_material_req_rec.INVENTORY_ITEM_ID <> FND_API.G_MISS_NUM )
1504 AND
1505 (p_material_req_rec.quantity IS NULL OR p_material_req_rec.quantity = FND_API.G_MISS_NUM) ) THEN
1506 FND_MESSAGE.set_name( 'AHL','AHL_RM_DISP_ITEM_QTY_NULL' );
1507 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1508 FND_MSG_PUB.add;
1509 x_return_status := FND_API.G_RET_STS_ERROR;
1510 END IF;
1511
1512 -- Check if the UOM not column contains a null value.
1513 IF ( ( p_material_req_rec.dml_operation <> 'D' AND
1514 -- p_association_type <> 'DISPOSITION' AND
1515 p_material_req_rec.uom IS NULL AND
1516 p_material_req_rec.uom_code IS NULL ) OR
1517 ( p_material_req_rec.uom = FND_API.G_MISS_CHAR AND
1518 p_material_req_rec.uom_code = FND_API.G_MISS_CHAR ) )
1519 THEN
1520 FND_MESSAGE.set_name( 'AHL','AHL_RM_UOM_NULL' );
1521 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1522 FND_MSG_PUB.add;
1523 x_return_status := FND_API.G_RET_STS_ERROR;
1524 /*
1525 ELSIF
1526 p_material_req_rec.dml_operation <> 'D'
1527 AND p_association_type = 'DISPOSITION'
1528 AND (
1529 p_material_req_rec.quantity IS NOT NULL
1530 OR p_material_req_rec.quantity <> FND_API.G_MISS_NUM
1531 )
1532 AND (
1533 (p_material_req_rec.uom IS NULL AND p_material_req_rec.uom_code IS NULL )
1534 OR
1535 (p_material_req_rec.uom = FND_API.G_MISS_CHAR AND p_material_req_rec.uom_code = FND_API.G_MISS_CHAR)
1536 )
1537 THEN
1538 FND_MESSAGE.set_name( 'AHL','AHL_RM_UOM_NULL' );
1539 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1540 FND_MSG_PUB.add;
1541 x_return_status := FND_API.G_RET_STS_ERROR;
1542 */
1543 END IF;
1544
1545 END IF;
1546
1547 IF ( p_association_type = 'DISPOSITION' AND
1548 p_material_req_rec.dml_operation <> 'D' AND
1549 (
1550 (p_material_req_rec.REPLACE_PERCENT IS NOT NULL OR p_material_req_rec.REPLACE_PERCENT <> FND_API.G_MISS_NUM )
1551 OR (p_material_req_rec.REWORK_PERCENT IS NOT NULL OR p_material_req_rec.REWORK_PERCENT <> FND_API.G_MISS_NUM )
1552 )
1553 AND
1554 (
1555 (p_material_req_rec.POSITION_PATH_ID IS NULL OR p_material_req_rec.POSITION_PATH_ID = FND_API.G_MISS_NUM )
1556 AND (p_material_req_rec.ITEM_GROUP_ID IS NULL OR p_material_req_rec.ITEM_GROUP_ID = FND_API.G_MISS_NUM )
1557 AND (p_material_req_rec.INVENTORY_ITEM_ID IS NULL OR p_material_req_rec.INVENTORY_ITEM_ID = FND_API.G_MISS_NUM )
1558 )
1559 )
1560 THEN
1561 FND_MESSAGE.set_name( 'AHL','AHL_RM_DISP_REPLACE_INVALID' );
1562 FND_MESSAGE.set_name( 'AHL','AHL_RM_DISP_REWORK_INVALID' );
1563 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1564 FND_MSG_PUB.add;
1565 x_return_status := FND_API.G_RET_STS_ERROR;
1566 END IF ;
1567
1568 -- Check if the mandatory Replace column contains a null value.
1569 IF ( p_association_type = 'DISPOSITION' AND
1570 p_material_req_rec.dml_operation <> 'D' AND
1571 ( p_material_req_rec.REPLACE_PERCENT IS NULL OR
1572 p_material_req_rec.REPLACE_PERCENT = FND_API.G_MISS_NUM )
1573 )
1574 THEN
1575 FND_MESSAGE.set_name( 'AHL','AHL_RM_DISP_REPLACE_NULL' );
1576 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1577 FND_MSG_PUB.add;
1578 x_return_status := FND_API.G_RET_STS_ERROR;
1579
1580 ELSIF ( p_association_type = 'DISPOSITION' AND
1581 p_material_req_rec.dml_operation <> 'D' AND
1582 NOT ( p_material_req_rec.REPLACE_PERCENT BETWEEN 0 AND 100 )
1583 )
1584 THEN
1585 FND_MESSAGE.set_name( 'AHL','AHL_RM_DISP_REPLACE_INVALID' );
1586 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1587 FND_MSG_PUB.add;
1588 x_return_status := FND_API.G_RET_STS_ERROR;
1589
1590 END IF;
1591
1592
1593 -- Check if the Rework column contains a null value.
1594 OPEN get_comms_nl_trackable_flag( p_material_req_rec.INVENTORY_ITEM_ID , p_material_req_rec.INVENTORY_ORG_ID );
1595 FETCH get_comms_nl_trackable_flag INTO l_comms_nl_trackable_flag;
1596 l_comms_nl_trackable_flag := NVL(l_comms_nl_trackable_flag,'N');
1597 CLOSE get_comms_nl_trackable_flag;
1598
1599 IF (
1600 p_association_type = 'DISPOSITION' AND p_material_req_rec.dml_operation <> 'D'
1601 AND
1602 (
1603 ( ( p_material_req_rec.POSITION_PATH_ID IS NOT NULL AND p_material_req_rec.POSITION_PATH_ID <> FND_API.G_MISS_NUM )
1604 AND (p_material_req_rec.ITEM_GROUP_ID IS NULL OR p_material_req_rec.ITEM_GROUP_ID = FND_API.G_MISS_NUM )
1605 AND (p_material_req_rec.INVENTORY_ITEM_ID IS NULL OR p_material_req_rec.INVENTORY_ITEM_ID = FND_API.G_MISS_NUM )
1606 )
1607 OR
1608 ( (p_material_req_rec.INVENTORY_ITEM_ID IS NOT NULL AND p_material_req_rec.INVENTORY_ITEM_ID <> FND_API.G_MISS_NUM)
1609 AND (nvl(p_material_req_rec.COMP_MATERIAL_FLAG, 'N') = 'N' AND ((l_comms_nl_trackable_flag IS NOT NULL) AND (l_comms_nl_trackable_flag = 'Y')))
1610 -- to throw this error , item has to be an Additional Material and also a trackable item.
1611 -- if the item is from Composition then we do not check whether its trackable or not as its possible to go to Inventory and change the trackable flag.
1612 -- and under any condition COMP_MATERIAL_FLAG = 'Y' this error will never be thrown
1613 )
1614 )
1615 AND
1616 ( p_material_req_rec.REWORK_PERCENT IS NULL OR p_material_req_rec.REWORK_PERCENT = FND_API.G_MISS_NUM )
1617 )
1618 THEN
1619 FND_MESSAGE.set_name( 'AHL','AHL_RM_DISP_REWORK_NULL' );
1620 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1621 FND_MSG_PUB.add;
1622 x_return_status := FND_API.G_RET_STS_ERROR;
1623 ELSIF ( p_association_type = 'DISPOSITION' AND
1624 p_material_req_rec.dml_operation <> 'D'
1625 AND
1626 (
1627 (p_material_req_rec.POSITION_PATH_ID IS NOT NULL AND p_material_req_rec.POSITION_PATH_ID <> FND_API.G_MISS_NUM )
1628
1629 OR
1630 ( (p_material_req_rec.INVENTORY_ITEM_ID IS NOT NULL AND p_material_req_rec.INVENTORY_ITEM_ID <> FND_API.G_MISS_NUM)
1631 AND ((l_comms_nl_trackable_flag IS NOT NULL) AND (l_comms_nl_trackable_flag = 'Y'))
1632 )
1633 )
1634 AND NOT ( p_material_req_rec.REWORK_PERCENT BETWEEN 0 AND 100 )
1635 )
1636 THEN
1637 FND_MESSAGE.set_name( 'AHL','AHL_RM_DISP_REWORK_INVALID' );
1638 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1639 FND_MSG_PUB.add;
1640 x_return_status := FND_API.G_RET_STS_ERROR;
1641
1642 END IF;
1643 -- if item group then rework percent should not be allowed to be entered.
1644 IF
1645 (
1646 (p_association_type = 'DISPOSITION' AND p_material_req_rec.dml_operation <> 'D')
1647 AND
1648 (
1649 (
1650 (
1651 (p_material_req_rec.ITEM_GROUP_ID <> FND_API.G_MISS_NUM AND p_material_req_rec.ITEM_GROUP_ID IS NOT NULL )
1652 OR
1653 (p_material_req_rec.ITEM_GROUP_NAME <> FND_API.G_MISS_CHAR AND p_material_req_rec.ITEM_GROUP_NAME IS NOT NULL )
1654 )
1655 OR
1656 (
1657 ( p_material_req_rec.INVENTORY_ITEM_ID <> FND_API.G_MISS_NUM AND p_material_req_rec.INVENTORY_ITEM_ID IS NOT NULL )
1658 AND
1659 (p_material_req_rec.COMP_MATERIAL_FLAG = 'Y' OR ( nvl(p_material_req_rec.COMP_MATERIAL_FLAG, 'N') <> 'Y' AND l_comms_nl_trackable_flag <> 'Y'))
1660 )
1661 )
1662 AND
1663 (p_material_req_rec.REWORK_PERCENT <> FND_API.G_MISS_NUM OR p_material_req_rec.REWORK_PERCENT IS NOT NULL )
1664 --if non trackable item then rework percent should not be allowed to be entered.
1665
1666 )
1667 )
1668 THEN
1669 FND_MESSAGE.set_name( 'AHL','AHL_RM_DISP_REWORK_NOT_REQ' );
1670 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1671 FND_MSG_PUB.add;
1672 x_return_status := FND_API.G_RET_STS_ERROR;
1673 END IF;
1674
1675
1676 IF ( p_material_req_rec.dml_operation = 'C' ) THEN
1677 RETURN;
1678 END IF;
1679
1680 -- Check if the mandatory Effectivity ID column contains a null value.
1681 IF ( p_material_req_rec.rt_oper_material_id IS NULL OR
1682 p_material_req_rec.rt_oper_material_id = FND_API.G_MISS_NUM ) THEN
1683 FND_MESSAGE.set_name( 'AHL','AHL_RM_MTL_ID_NULL' );
1684 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1685 FND_MSG_PUB.add;
1686 x_return_status := FND_API.G_RET_STS_ERROR;
1687 RETURN;
1688 END IF;
1689
1690 -- Check if the mandatory Object Version Number column contains a null value.
1691 IF ( p_material_req_rec.object_version_number IS NULL OR
1692 p_material_req_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
1693 FND_MESSAGE.set_name( 'AHL','AHL_RM_MTL_OBJ_VERSION_NULL' );
1694 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1695 FND_MSG_PUB.add;
1696 x_return_status := FND_API.G_RET_STS_ERROR;
1697 RETURN;
1698 END IF;
1699
1700
1701
1702 END validate_attributes;
1703
1704 -- Procedure to validate individual efct attributes
1705 PROCEDURE validate_efct_attributes
1706 (
1707 p_route_efct_rec IN route_efct_rec_type,
1708 x_return_status OUT NOCOPY VARCHAR2
1709 )
1710 IS
1711
1712 BEGIN
1713 x_return_status := FND_API.G_RET_STS_SUCCESS;
1714
1715
1716 IF ( p_route_efct_rec.dml_operation = 'C' ) THEN
1717 RETURN;
1718 END IF;
1719
1720 -- Check if the mandatory column contains a null value.
1721 IF ( p_route_efct_rec.ROUTE_EFFECTIVITY_ID IS NULL OR
1722 p_route_efct_rec.ROUTE_EFFECTIVITY_ID = FND_API.G_MISS_NUM ) THEN
1723 FND_MESSAGE.set_name( 'AHL','AHL_RM_efct_ID_NULL' );
1724 -- FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1725 FND_MSG_PUB.add;
1726 x_return_status := FND_API.G_RET_STS_ERROR;
1727 RETURN;
1728 END IF;
1729
1730 -- Check if the mandatory Object Version Number column contains a null value.
1731 IF ( p_route_efct_rec.object_version_number IS NULL OR
1732 p_route_efct_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
1733 FND_MESSAGE.set_name( 'AHL','AHL_RM_efct_OBJ_VERSION_NULL' );
1734 -- FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1735 FND_MSG_PUB.add;
1736 x_return_status := FND_API.G_RET_STS_ERROR;
1737 RETURN;
1738 END IF;
1739
1740 END validate_efct_attributes;
1741
1742 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
1743 PROCEDURE validate_record
1744 (
1745 p_material_req_rec IN material_req_rec_type,
1746 p_association_type IN VARCHAR2,
1747 x_return_status OUT NOCOPY VARCHAR2
1748 )
1749 IS
1750
1751 l_return_status VARCHAR2(1);
1752 l_msg_data VARCHAR2(2000);
1753
1754 BEGIN
1755 x_return_status := FND_API.G_RET_STS_SUCCESS;
1756
1757 -- Check if both Item Group and Item are NULL
1758 IF ( p_association_type <> 'DISPOSITION' AND
1759 p_material_req_rec.item_group_id IS NULL AND
1760 p_material_req_rec.item_group_name IS NULL AND
1761 p_material_req_rec.inventory_item_id IS NULL AND
1762 p_material_req_rec.inventory_org_id IS NULL AND
1763 p_material_req_rec.item_number IS NULL ) THEN
1764 FND_MESSAGE.set_name( 'AHL','AHL_RM_ITEMGRP_ITEM_NULL' );
1765 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1766 FND_MSG_PUB.add;
1767 x_return_status := FND_API.G_RET_STS_ERROR;
1768 END IF;
1769
1770 -- Check if both Item Group and Item contain values
1771 IF (
1772 --p_association_type <> 'DISPOSITION' AND
1773 ( p_material_req_rec.inventory_item_id IS NOT NULL OR
1774 p_material_req_rec.inventory_org_id IS NOT NULL OR
1775 p_material_req_rec.item_number IS NOT NULL ) AND
1776 ( p_material_req_rec.item_group_id IS NOT NULL OR
1777 p_material_req_rec.item_group_name IS NOT NULL ) ) THEN
1778 FND_MESSAGE.set_name( 'AHL','AHL_RM_ITEMGRP_ITEM_NOTNULL' );
1779 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1780 FND_MSG_PUB.add;
1781 x_return_status := FND_API.G_RET_STS_ERROR;
1782 END IF;
1783
1784 -- Check if the given UOM is valid for the given Item
1785 IF ( p_material_req_rec.uom_code IS NOT NULL AND
1786 ( p_material_req_rec.item_group_id IS NOT NULL OR
1787 ( p_material_req_rec.inventory_item_id IS NOT NULL AND
1788 p_material_req_rec.inventory_org_id IS NOT NULL ) ) ) THEN
1789 AHL_RM_ROUTE_UTIL.validate_item_uom
1790 (
1791 x_return_status => l_return_status,
1792 x_msg_data => l_msg_data,
1793 p_item_group_id => p_material_req_rec.item_group_id,
1794 p_inventory_item_id => p_material_req_rec.inventory_item_id,
1795 p_inventory_org_id => p_material_req_rec.inventory_org_id,
1796 p_uom_code => p_material_req_rec.uom_code
1797 );
1798
1799 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1800 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1801 IF ( p_material_req_rec.uom IS NULL ) THEN
1802 FND_MESSAGE.set_token( 'FIELD1', p_material_req_rec.uom_code );
1803 ELSE
1804 FND_MESSAGE.set_token( 'FIELD1', p_material_req_rec.uom );
1805 END IF;
1806
1807 IF ( p_material_req_rec.item_number IS NOT NULL ) THEN
1808 FND_MESSAGE.set_token( 'FIELD2', p_material_req_rec.item_number );
1809 ELSIF ( p_material_req_rec.inventory_item_id IS NOT NULL AND
1810 p_material_req_rec.inventory_org_id IS NOT NULL ) THEN
1811 FND_MESSAGE.set_token( 'FIELD2', TO_CHAR( p_material_req_rec.inventory_item_id ) || '-' || TO_CHAR( p_material_req_rec.inventory_org_id ) );
1812 ELSIF ( p_material_req_rec.item_group_name IS NOT NULL ) THEN
1813 FND_MESSAGE.set_token( 'FIELD2', p_material_req_rec.item_group_name );
1814 ELSIF ( p_material_req_rec.item_group_id IS NOT NULL ) THEN
1815 FND_MESSAGE.set_token( 'FIELD2', TO_CHAR( p_material_req_rec.item_group_id ) );
1816 END IF;
1817
1818 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_material_req_rec ) );
1819 FND_MSG_PUB.add;
1820 x_return_status := l_return_status;
1821 END IF;
1822
1823 END IF;
1824
1825 END validate_record;
1826
1827 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
1828 PROCEDURE validate_efct_record
1829 (
1830 p_route_efct_rec IN route_efct_rec_type,
1831 x_return_status OUT NOCOPY VARCHAR2
1832 )
1833 IS
1834
1835 l_return_status VARCHAR2(1);
1836 l_msg_data VARCHAR2(2000);
1837
1838 BEGIN
1839 x_return_status := FND_API.G_RET_STS_SUCCESS;
1840
1841 -- Check if both MC and Item are NULL
1842 IF ( p_route_efct_rec.mc_id IS NULL AND
1843 p_route_efct_rec.mc_name IS NULL AND
1844 p_route_efct_rec.inventory_item_id IS NULL AND
1845 p_route_efct_rec.inventory_master_org_id IS NULL AND
1846 p_route_efct_rec.item_number IS NULL ) THEN
1847 FND_MESSAGE.set_name( 'AHL','AHL_RM_EFCT_ITEM_NULL' );
1848 -- FND_MESSAGE.set_token( 'RECORD', get_effct_identifier( p_route_efct_rec ) );
1849 FND_MSG_PUB.add;
1850 x_return_status := FND_API.G_RET_STS_ERROR;
1851 END IF;
1852
1853 -- Check if both MC and Item contain values
1854 IF ( ( p_route_efct_rec.inventory_item_id IS NOT NULL OR
1855 p_route_efct_rec.inventory_master_org_id IS NOT NULL OR
1856 p_route_efct_rec.item_number IS NOT NULL OR
1857 p_route_efct_rec.ORGANIZATION_CODE IS NOT NULL
1858
1859 ) AND
1860 ( p_route_efct_rec.mc_id IS NOT NULL OR
1861 p_route_efct_rec.mc_name IS NOT NULL OR
1862 p_route_efct_rec.MC_REVISION IS NOT NULL OR
1863 p_route_efct_rec.MC_HEADER_ID IS NOT NULL
1864 ) )
1865 THEN
1866 FND_MESSAGE.set_name( 'AHL','AHL_RM_efct_ITEM_NOTNULL' );
1867 FND_MESSAGE.set_token( 'RECORD', get_effct_identifier( p_route_efct_rec ) );
1868 FND_MSG_PUB.add;
1869 x_return_status := FND_API.G_RET_STS_ERROR;
1870 END IF;
1871
1872 -- Check if both Org and Item contain values
1873 IF ( ( p_route_efct_rec.inventory_item_id IS NOT NULL OR p_route_efct_rec.item_number IS NOT NULL )
1874 AND ( p_route_efct_rec.inventory_master_org_id IS NULL OR p_route_efct_rec.organization_code IS NULL)
1875 )
1876 THEN
1877 FND_MESSAGE.set_name( 'AHL','AHL_RM_efct_ITEM_ORG_NOTNULL' );
1878 -- FND_MESSAGE.set_token( 'RECORD', get_effct_identifier( p_route_efct_rec ) );
1879 FND_MSG_PUB.add;
1880 x_return_status := FND_API.G_RET_STS_ERROR;
1881 END IF;
1882
1883
1884 END validate_efct_record;
1885
1886
1887 -- Procedure to Perform cross records validation and duplicate checks
1888 PROCEDURE validate_records
1889 (
1890 p_object_id IN NUMBER,
1891 p_association_type IN VARCHAR2,
1892 x_return_status OUT NOCOPY VARCHAR2
1893 )
1894 IS
1895
1896 l_material_req_rec material_req_rec_type;
1897 l_association_type VARCHAR2(30);
1898 l_description VARCHAR2(240);
1899
1900 CURSOR get_dup_rec ( c_object_id NUMBER , c_association_type VARCHAR )
1901 IS
1902 SELECT
1903 ROMV.ASSOCIATION_TYPE_CODE,
1904 ROMV.POSITION_PATH_ID,
1905 ROMV.item_group_id,
1906 ROMV.inventory_item_id,
1907 ROMV.inventory_org_id
1908 FROM
1909 AHL_RT_OPER_MATERIALS ROMV
1910 WHERE
1911 ROMV.object_id = c_object_id AND
1912 ROMV.association_type_code = c_association_type AND
1913 NOT EXISTS
1914 (SELECT item_comp_detail_id
1915 FROM ahl_item_comp_details
1916 WHERE item_comp_detail_id = ROMV.item_comp_detail_id
1917 AND
1918 effective_end_date is not null)
1919 GROUP BY
1920 ROMV.ASSOCIATION_TYPE_CODE,
1921 ROMV.POSITION_PATH_ID,
1922 ROMV.item_group_id,
1923 ROMV.inventory_item_id,
1924 ROMV.inventory_org_id
1925 HAVING count(*) > 1;
1926
1927 BEGIN
1928
1929 x_return_status := FND_API.G_RET_STS_SUCCESS;
1930 /*
1931 IF ( p_association_type = 'DISPOSITION' ) THEN
1932 RETURN ;
1933 END IF;
1934 */
1935 -- Check whether any duplicate material_req records (based on Name) for the given OBJECT_ID and ASSOCIATION_TYPE
1936 OPEN get_dup_rec( p_object_id , p_association_type );
1937
1938 LOOP
1939
1940 FETCH get_dup_rec INTO
1941 l_association_type,
1942 l_material_req_rec.POSITION_PATH_ID,
1943 l_material_req_rec.item_group_id,
1944 l_material_req_rec.inventory_item_id,
1945 l_material_req_rec.inventory_org_id;
1946
1947 IF G_DEBUG = 'Y' THEN
1948 AHL_DEBUG_PUB.debug( 'association_type : ' || l_association_type );
1949 AHL_DEBUG_PUB.debug( 'POSITION_PATH_ID : ' || l_material_req_rec.POSITION_PATH_ID);
1950 AHL_DEBUG_PUB.debug( 'item_group_id : ' || l_material_req_rec.item_group_id);
1951 AHL_DEBUG_PUB.debug( 'inventory_item_id : ' || l_material_req_rec.inventory_item_id);
1952 AHL_DEBUG_PUB.debug( 'inventory_org_id : ' || l_material_req_rec.inventory_org_id);
1953 END IF;
1954 EXIT WHEN get_dup_rec%NOTFOUND;
1955
1956 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_MATERIAL_REQ_REC_DUP' );
1957 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_material_req_rec ) );
1958 FND_MSG_PUB.add;
1959
1960 END LOOP;
1961
1962 IF ( get_dup_rec%ROWCOUNT > 0 ) THEN
1963 CLOSE get_dup_rec;
1964 x_return_status := FND_API.G_RET_STS_ERROR;
1965 RETURN;
1966 END IF;
1967
1968 CLOSE get_dup_rec;
1969
1970 END validate_records;
1971
1972 -- Procedure to Perform cross records validation and duplicate checks
1973 PROCEDURE validate_efct_records
1974 (
1975 p_object_id IN NUMBER,
1976 x_return_status OUT NOCOPY VARCHAR2
1977 )
1978 IS
1979
1980 l_route_efct_req_rec route_efct_rec_type;
1981
1982 /*CURSOR get_dup_rec ( c_object_id NUMBER )
1983 IS
1984 SELECT
1985 mc_id,
1986 mc_name,
1987 MC_REVISION,
1988 mc_header_id,
1989 organization_code,
1990 inventory_item_id,
1991 inventory_master_org_id,
1992 CONCATENATED_SEGMENTS,
1993 item_description
1994 FROM AHL_ROUTE_EFFECTIVITIES_V
1995 WHERE ROUTE_ID = c_object_id
1996 GROUP BY
1997 mc_id,
1998 mc_name,
1999 MC_REVISION,
2000 mc_header_id,
2001 organization_code,
2002 inventory_item_id,
2003 inventory_master_org_id,
2004 CONCATENATED_SEGMENTS,
2005 item_description
2006 HAVING count(*) > 1;*/
2007
2008 --Bug 4913141. AMSRINIV. Tuned the above commented query.Using base table directly instead of View.
2009 CURSOR get_dup_rec ( c_object_id NUMBER )
2010 IS
2011 SELECT mc_id,
2012 mc_header_id,
2013 inventory_item_id,
2014 inventory_master_org_id
2015 FROM AHL_ROUTE_EFFECTIVITIES
2016 WHERE ROUTE_ID = c_object_id
2017 GROUP BY
2018 mc_id,
2019 mc_header_id,
2020 inventory_item_id,
2021 inventory_master_org_id
2022 HAVING count(*) > 1;
2023 --AMSRINIV.Bug 4913141. Code added
2024 --BEGIN
2025 CURSOR get_org_code ( c_inv_mast_org_id NUMBER )
2026 IS
2027 SELECT DISTINCT ORGANIZATION_CODE
2028 FROM
2029 mtl_parameters MP
2030 WHERE
2031 MP.MASTER_ORGANIZATION_ID = c_inv_mast_org_id AND
2032 MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID;
2033
2034 CURSOR get_item_number ( c_inventory_item_id NUMBER, c_inventory_master_org_id NUMBER )
2035 IS
2036 SELECT mtl.concatenated_segments
2037 FROM MTL_SYSTEM_ITEMS_KFV MTL
2038 WHERE
2039 mtl.ORGANIZATION_ID(+) = c_inventory_item_id AND
2040 mtl.inventory_item_id(+) = c_inventory_master_org_id;
2041
2042 CURSOR get_mc_name_and_revision ( c_mc_id NUMBER, c_mc_header_id NUMBER )
2043 IS
2044 SELECT
2045 mc.name,
2046 DECODE(c_mc_header_id, NULL, NULL, mc.revision)
2047 FROM ahl_mc_headers_b mc
2048 WHERE
2049 NVL(c_mc_header_id, c_mc_id)=mc.mc_header_id(+) AND
2050 mc.CONFIG_STATUS_CODE(+)='COMPLETE';
2051 --END
2052 BEGIN
2053
2054 IF G_DEBUG='Y' THEN
2055 AHL_DEBUG_PUB.enable_debug;
2056 END IF;
2057 x_return_status := FND_API.G_RET_STS_SUCCESS;
2058
2059 -- Check whether any duplicate records for the given OBJECT_ID
2060 OPEN get_dup_rec( p_object_id );
2061
2062 LOOP
2063
2064 FETCH get_dup_rec INTO
2065 l_route_efct_req_rec.mc_id,
2066 l_route_efct_req_rec.mc_header_id,
2067 l_route_efct_req_rec.inventory_item_id,
2068 l_route_efct_req_rec.inventory_master_org_id;
2069
2070
2071
2072
2073
2074 EXIT WHEN get_dup_rec%NOTFOUND;
2075 --AMSRINIV.Bug 4913141. Code added
2076 --BEGIN
2077 OPEN get_org_code ( l_route_efct_req_rec.inventory_master_org_id );
2078 FETCH get_org_code INTO l_route_efct_req_rec.organization_code;
2079 CLOSE get_org_code;
2080
2081 OPEN get_item_number ( l_route_efct_req_rec.inventory_item_id, l_route_efct_req_rec.inventory_master_org_id );
2082 FETCH get_item_number INTO l_route_efct_req_rec.organization_code;
2083 CLOSE get_item_number;
2084
2085 OPEN get_mc_name_and_revision ( l_route_efct_req_rec.mc_id, l_route_efct_req_rec.mc_header_id );
2086 FETCH get_mc_name_and_revision INTO
2087 l_route_efct_req_rec.mc_name,
2088 l_route_efct_req_rec.MC_REVISION;
2089 CLOSE get_mc_name_and_revision;
2090 --END
2091 IF ( get_dup_rec%FOUND ) THEN
2092 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_route_efct_REC_DUP' );
2093 FND_MESSAGE.set_token( 'RECORD', get_effct_identifier( l_route_efct_req_rec ) );
2094 FND_MSG_PUB.add;
2095 END IF ;
2096 END LOOP;
2097
2098 IF ( get_dup_rec%ROWCOUNT > 0 ) THEN
2099 CLOSE get_dup_rec;
2100 x_return_status := FND_API.G_RET_STS_ERROR;
2101 RETURN;
2102 END IF;
2103
2104 CLOSE get_dup_rec;
2105
2106 END validate_efct_records;
2107
2108
2109
2110 PROCEDURE process_material_req
2111 (
2112 p_api_version IN NUMBER := '1.0',
2113 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2114 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2115 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2116 p_default IN VARCHAR2 := FND_API.G_FALSE,
2117 p_module_type IN VARCHAR2 := NULL,
2118 x_return_status OUT NOCOPY VARCHAR2,
2119 x_msg_count OUT NOCOPY NUMBER,
2120 x_msg_data OUT NOCOPY VARCHAR2,
2121 p_x_material_req_tbl IN OUT NOCOPY material_req_tbl_type,
2122 p_object_id IN NUMBER,
2123 p_association_type IN VARCHAR2
2124 )
2125 IS
2126 cursor get_route_status (p_route_id in number)
2127 is
2128 select revision_status_code
2129 from ahl_routes_app_v
2130 where route_id = p_route_id;
2131
2132 l_obj_status VARCHAR2(30);
2133
2134 cursor get_oper_status (p_operation_id in number)
2135 is
2136 select revision_status_code
2137 from ahl_operations_b
2138 where operation_id = p_operation_id;
2139
2140 CURSOR get_efct_rec ( C_ROUTE_EFFECTIVITY_ID NUMBER )
2141 IS
2142 SELECT RM.ROUTE_ID
2143 FROM ahl_route_effectivities refct, AHL_ROUTES_APP_V RM
2144 WHERE refct.ROUTE_EFFECTIVITY_ID = C_ROUTE_EFFECTIVITY_ID
2145 AND RM.ROUTE_ID = refct.ROUTE_ID;
2146
2147 l_api_version CONSTANT NUMBER := 1.0;
2148 l_return_status VARCHAR2(1);
2149 l_msg_count NUMBER;
2150 l_msg_data VARCHAR2(30);
2151 l_rt_oper_material_id NUMBER;
2152 l_x_operation_rec AHL_RM_OPERATION_PVT.operation_rec_type ;
2153 l_x_route_rec AHL_RM_ROUTE_PVT.route_rec_type ;
2154 l_dummy_varchar VARCHAR2(1);
2155 l_dummy_number NUMBER;
2156
2157 BEGIN
2158 -- Initialize API return status to success
2159 x_return_status := FND_API.G_RET_STS_SUCCESS;
2160
2161 -- Standard Start of API savepoint
2162 SAVEPOINT process_material_req_PVT;
2163
2164 -- Standard call to check for call compatibility.
2165 IF NOT FND_API.compatible_api_call
2166 (
2167 l_api_version,
2168 p_api_version,
2169 G_API_NAME,
2170 G_PKG_NAME
2171 )
2172 THEN
2173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2174 END IF;
2175
2176 -- Initialize message list if p_init_msg_list is set to TRUE.
2177 IF FND_API.to_boolean( p_init_msg_list ) THEN
2178 FND_MSG_PUB.initialize;
2179 END IF;
2180
2181 -- Enable Debug (optional)
2182 IF ( G_DEBUG = 'Y' ) THEN
2183 AHL_DEBUG_PUB.enable_debug;
2184 END IF;
2185
2186 IF G_DEBUG = 'Y' THEN
2187 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Begin API' );
2188 END IF;
2189
2190
2191 --This is to be added before calling validate_efct_api_inputs
2192 -- Validate Application Usage
2193 IF (p_association_type = 'ROUTE')
2194 THEN
2195 AHL_RM_ROUTE_UTIL.validate_ApplnUsage
2196 (
2197 p_object_id => p_object_id,
2198 p_association_type => p_association_type,
2199 x_return_status => x_return_status,
2200 x_msg_data => x_msg_data
2201 );
2202
2203 -- If any severe error occurs, then, abort API.
2204 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2205 RAISE FND_API.G_EXC_ERROR;
2206 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2208 END IF;
2209
2210 END IF;
2211
2212
2213 -- Validate all the inputs of the API
2214 validate_api_inputs
2215 (
2216 p_x_material_req_tbl, -- IN
2217 p_object_id, -- IN
2218 p_association_type, -- IN
2219 l_return_status -- OUT
2220 );
2221
2222 -- If any severe error occurs, then, abort API.
2223 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2224 RAISE FND_API.G_EXC_ERROR;
2225 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2227 END IF;
2228
2229 -- If the module type is JSP, then default values for ID columns of LOV attributes
2230 IF ( p_module_type = 'JSP' ) THEN
2231 FOR i IN 1..p_x_material_req_tbl.count LOOP
2232 IF ( p_x_material_req_tbl(i).dml_operation <> 'D' ) THEN
2233 clear_lov_attribute_ids
2234 (
2235 p_x_material_req_tbl(i) -- IN OUT Record with Values and Ids
2236 );
2237 END IF;
2238 END LOOP;
2239 END IF;
2240
2241 -- Convert Values into Ids.
2242 FOR i IN 1..p_x_material_req_tbl.count LOOP
2243 IF ( p_x_material_req_tbl(i).dml_operation <> 'D' ) THEN
2244 convert_values_to_ids
2245 (
2246 p_x_material_req_tbl(i) , -- IN OUT Record with Values and Ids
2247 p_object_id,
2248 p_association_type, -- IN
2249 l_return_status -- OUT
2250 );
2251
2252 -- If any severe error occurs, then, abort API.
2253 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2254 RAISE FND_API.G_EXC_ERROR;
2255 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2257 END IF;
2258 END IF;
2259 END LOOP;
2260
2261 IF G_DEBUG = 'Y' THEN
2262 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after convert_values_to_ids' );
2263 END IF;
2264
2265 -- Default missing and unchanged attributes.
2266 FOR i IN 1..p_x_material_req_tbl.count LOOP
2267 IF ( p_x_material_req_tbl(i).dml_operation = 'U' ) THEN
2268 default_unchanged_attributes
2269 (
2270 p_x_material_req_tbl(i) -- IN OUT
2271 );
2272 ELSIF ( p_x_material_req_tbl(i).dml_operation = 'C' ) THEN
2273 default_missing_attributes
2274 (
2275 p_x_material_req_tbl(i) -- IN OUT
2276 );
2277 END IF;
2278 END LOOP;
2279
2280 IF G_DEBUG = 'Y' THEN
2281 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_unchanged_attributes / default_missing_attributes' );
2282 END IF;
2283
2284 -- Validate all attributes (Item level validation)
2285 FOR i IN 1..p_x_material_req_tbl.count LOOP
2286 validate_attributes
2287 (
2288 p_x_material_req_tbl(i), -- IN
2289 p_association_type, -- IN
2290 l_return_status -- OUT
2291 );
2292
2293 -- If any severe error occurs, then, abort API.
2294 /* IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2295 RAISE FND_API.G_EXC_ERROR;
2296 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2298 END IF;*/
2299
2300 END LOOP;
2301
2302 IF G_DEBUG = 'Y' THEN
2303 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_attributes' );
2304 END IF;
2305
2306 -- Perform cross attribute validation and missing attribute checks (Record level validation)
2307 FOR i IN 1..p_x_material_req_tbl.count LOOP
2308 IF ( p_x_material_req_tbl(i).dml_operation <> 'D' ) THEN
2309 validate_record
2310 (
2311 p_x_material_req_tbl(i), -- IN
2312 p_association_type, -- IN
2313 l_return_status -- OUT
2314 );
2315
2316 -- If any severe error occurs, then, abort API.
2317 /* IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2318 RAISE FND_API.G_EXC_ERROR;
2319 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2321 END IF;*/
2322 END IF;
2323 END LOOP;
2324
2325 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2326 l_msg_count := FND_MSG_PUB.count_msg;
2327 IF l_msg_count > 0 THEN
2328 x_msg_count := l_msg_count;
2329 RAISE FND_API.G_EXC_ERROR;
2330 END IF;
2331
2332
2333 IF G_DEBUG = 'Y' THEN
2334 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_record' );
2335 END IF;
2336
2337
2338 IF ( p_association_type = 'OPERATION')
2339 THEN
2340 -- Check if the Route is existing and in Draft status
2341 AHL_RM_ROUTE_UTIL.validate_operation_status
2342 (
2343 p_object_id,
2344 l_msg_data,
2345 l_return_status
2346 );
2347
2348 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2349 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
2350 FND_MSG_PUB.ADD;
2351 x_return_status := l_return_status;
2352 RETURN;
2353 END IF;
2354
2355 -- Update route status from APPROVAL_REJECTED to DRAFT
2356 OPEN get_oper_status (p_object_id);
2357 FETCH get_oper_status INTO l_obj_status;
2358 IF (get_oper_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
2359 THEN
2360 UPDATE ahl_operations_b
2361 SET revision_status_code = 'DRAFT'
2362 WHERE operation_id = p_object_id;
2363 END IF;
2364 CLOSE get_oper_status;
2365
2366 ELSIF (p_association_type = 'DISPOSITION')
2367 THEN
2368 OPEN get_efct_rec ( p_object_id ) ;
2369 FETCH get_efct_rec INTO l_dummy_number;
2370 IF get_efct_rec%NOTFOUND
2371 THEN
2372 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_efct_REC' );
2373 FND_MSG_PUB.add;
2374 CLOSE get_efct_rec;
2375 RAISE FND_API.G_EXC_ERROR;
2376 END IF;
2377 CLOSE get_efct_rec;
2378
2379 -- Check if the Route is existing and in Draft status
2380 AHL_RM_ROUTE_UTIL.validate_route_status
2381 (
2382 l_dummy_number,
2383 l_msg_data,
2384 l_return_status
2385 );
2386
2387 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2388 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
2389 FND_MSG_PUB.ADD;
2390 x_return_status := l_return_status;
2391 RETURN;
2392 END IF;
2393
2394 -- Update route status from APPROVAL_REJECTED to DRAFT
2395 OPEN get_route_status (l_dummy_number);
2396 FETCH get_route_status INTO l_obj_status;
2397 IF (get_route_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
2398 THEN
2399 UPDATE ahl_routes_b
2400 SET revision_status_code = 'DRAFT'
2401 WHERE route_id = l_dummy_number;
2402 END IF;
2403 CLOSE get_route_status;
2404
2405 ELSIF ( p_association_type = 'ROUTE' )
2406 THEN
2407 -- Check if the Route is existing and in Draft status
2408 AHL_RM_ROUTE_UTIL.validate_route_status
2409 (
2410 p_object_id,
2411 l_msg_data,
2412 l_return_status
2413 );
2414
2415 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2416 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
2417 FND_MSG_PUB.ADD;
2418 x_return_status := l_return_status;
2419 RETURN;
2420 END IF;
2421
2422 -- Update route status from APPROVAL_REJECTED to DRAFT
2423 OPEN get_route_status (p_object_id);
2424 FETCH get_route_status INTO l_obj_status;
2425 IF (get_route_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
2426 THEN
2427 UPDATE ahl_routes_b
2428 SET revision_status_code = 'DRAFT'
2429 WHERE route_id = p_object_id;
2430 END IF;
2431 CLOSE get_route_status;
2432 END IF ;
2433
2434
2435 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2436 l_msg_count := FND_MSG_PUB.count_msg;
2437 IF l_msg_count > 0 THEN
2438 x_msg_count := l_msg_count;
2439 RAISE FND_API.G_EXC_ERROR;
2440 END IF;
2441
2442 -- Perform the DML statement directly.
2443 FOR i IN 1..p_x_material_req_tbl.count LOOP
2444 IF G_DEBUG = 'Y' THEN
2445 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : p_x_material_req_tbl(i).dml_operation ' || p_x_material_req_tbl(i).dml_operation ) ;
2446 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before DML p_object_id ' || p_object_id );
2447 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before DML p_association_type ' || p_association_type );
2448 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before DML p_x_material_req_tbl(i).position_path_id ' || p_x_material_req_tbl(i).position_path_id );
2449 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before insert p_x_material_req_tbl(i).item_group_id ' || p_x_material_req_tbl(i).item_group_id );
2450 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before insert p_x_material_req_tbl(i).inventory_item_id ' || p_x_material_req_tbl(i).inventory_item_id );
2451 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before insert p_x_material_req_tbl(i).inventory_org_id ' || p_x_material_req_tbl(i).inventory_org_id );
2452 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before insert p_x_material_req_tbl(i).uom_code ' || p_x_material_req_tbl(i).uom_code);
2453 END IF;
2454 IF ( p_x_material_req_tbl(i).dml_operation = 'C' ) THEN
2455
2456 BEGIN
2457 -- Insert the record
2458 p_x_material_req_tbl(i).object_version_number := 1;
2459
2460 INSERT INTO AHL_RT_OPER_MATERIALS
2461 (
2462 RT_OPER_MATERIAL_ID,
2463 OBJECT_VERSION_NUMBER,
2464 OBJECT_ID,
2465 ASSOCIATION_TYPE_CODE,
2466 POSITION_PATH_ID,
2467 ITEM_GROUP_ID,
2468 INVENTORY_ITEM_ID,
2469 INVENTORY_ORG_ID,
2470 UOM_CODE,
2471 QUANTITY,
2472 ITEM_COMP_DETAIL_ID,
2473 EXCLUDE_FLAG,
2474 REWORK_PERCENT,
2475 REPLACE_PERCENT,
2476 ATTRIBUTE_CATEGORY,
2477 ATTRIBUTE1,
2478 ATTRIBUTE2,
2479 ATTRIBUTE3,
2480 ATTRIBUTE4,
2481 ATTRIBUTE5,
2482 ATTRIBUTE6,
2483 ATTRIBUTE7,
2484 ATTRIBUTE8,
2485 ATTRIBUTE9,
2486 ATTRIBUTE10,
2487 ATTRIBUTE11,
2488 ATTRIBUTE12,
2489 ATTRIBUTE13,
2490 ATTRIBUTE14,
2491 ATTRIBUTE15,
2492 LAST_UPDATE_DATE,
2493 LAST_UPDATED_BY,
2494 CREATION_DATE,
2495 CREATED_BY,
2496 LAST_UPDATE_LOGIN,
2497 IN_SERVICE --pdoki added for OGMA 105 issue
2498 ) VALUES
2499 (
2500 AHL_RT_OPER_MATERIALS_S.NEXTVAL,
2501 p_x_material_req_tbl(i).object_version_number,
2502 p_object_id,
2503 p_association_type,
2504 p_x_material_req_tbl(i).position_path_id ,
2505 p_x_material_req_tbl(i).item_group_id,
2506 p_x_material_req_tbl(i).inventory_item_id,
2507 p_x_material_req_tbl(i).inventory_org_id,
2508 p_x_material_req_tbl(i).uom_code,
2509 p_x_material_req_tbl(i).quantity,
2510 p_x_material_req_tbl(i).item_comp_detail_id,
2511 p_x_material_req_tbl(i).exclude_flag,
2512 p_x_material_req_tbl(i).rework_percent,
2513 p_x_material_req_tbl(i).replace_percent,
2514 p_x_material_req_tbl(i).attribute_category,
2515 p_x_material_req_tbl(i).attribute1,
2516 p_x_material_req_tbl(i).attribute2,
2517 p_x_material_req_tbl(i).attribute3,
2518 p_x_material_req_tbl(i).attribute4,
2519 p_x_material_req_tbl(i).attribute5,
2520 p_x_material_req_tbl(i).attribute6,
2521 p_x_material_req_tbl(i).attribute7,
2522 p_x_material_req_tbl(i).attribute8,
2523 p_x_material_req_tbl(i).attribute9,
2524 p_x_material_req_tbl(i).attribute10,
2525 p_x_material_req_tbl(i).attribute11,
2526 p_x_material_req_tbl(i).attribute12,
2527 p_x_material_req_tbl(i).attribute13,
2528 p_x_material_req_tbl(i).attribute14,
2529 p_x_material_req_tbl(i).attribute15,
2530 SYSDATE,
2531 FND_GLOBAL.user_id,
2532 SYSDATE,
2533 FND_GLOBAL.user_id,
2534 FND_GLOBAL.login_id,
2535 p_x_material_req_tbl(i).in_service --pdoki added for OGMA 105 issue
2536 ) RETURNING rt_oper_material_id INTO l_rt_oper_material_id;
2537
2538 IF G_DEBUG = 'Y' THEN
2539 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_object_id ' || p_object_id );
2540 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_association_type ' || p_association_type );
2541 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_material_req_tbl(i).position_path_id ' || p_x_material_req_tbl(i).position_path_id );
2542 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_material_req_tbl(i).item_group_id ' || p_x_material_req_tbl(i).item_group_id );
2543 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_material_req_tbl(i).inventory_item_id ' || p_x_material_req_tbl(i).inventory_item_id );
2544 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_material_req_tbl(i).inventory_org_id ' || p_x_material_req_tbl(i).inventory_org_id );
2545 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_material_req_tbl(i).uom_code ' || p_x_material_req_tbl(i).uom_code);
2546 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_material_req_tbl(i).quantity ' || p_x_material_req_tbl(i).quantity);
2547 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_material_req_tbl(i).item_comp_detail_id ' || p_x_material_req_tbl(i).item_comp_detail_id);
2548 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_material_req_tbl(i).exclude_flag ' || p_x_material_req_tbl(i).exclude_flag );
2549 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_material_req_tbl(i).rework_percent ' || p_x_material_req_tbl(i).rework_percent );
2550 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_material_req_tbl(i).replace_percent ' || p_x_material_req_tbl(i).replace_percent );
2551 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert l_rt_oper_material_id' || l_rt_oper_material_id);
2552 END IF;
2553
2554 -- Set OUT values
2555 p_x_material_req_tbl(i).rt_oper_material_id := l_rt_oper_material_id;
2556
2557 EXCEPTION
2558 WHEN OTHERS THEN
2559 IF ( SQLCODE = -1 ) THEN
2560 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_MATERIAL_REQ_REC_DUP' );
2561 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_material_req_tbl(i) ) );
2562 FND_MSG_PUB.add;
2563 ELSE
2564 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
2565 THEN
2566 fnd_log.string
2567 (
2568 fnd_log.level_unexpected,
2569 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME,
2570 'AHL_RT_OPER_MATERIALS insert error = ['||SQLERRM||']'
2571 );
2572 END IF;
2573 END IF;
2574 END;
2575
2576 ELSIF ( p_x_material_req_tbl(i).dml_operation = 'U' ) THEN
2577
2578 BEGIN
2579 -- Update the record
2580 UPDATE AHL_RT_OPER_MATERIALS SET
2581 object_version_number = object_version_number + 1,
2582 item_comp_detail_id = p_x_material_req_tbl(i).item_comp_detail_id ,
2583 position_path_id = p_x_material_req_tbl(i).position_path_id ,
2584 item_group_id = p_x_material_req_tbl(i).item_group_id,
2585 inventory_item_id = p_x_material_req_tbl(i).inventory_item_id,
2586 inventory_org_id = p_x_material_req_tbl(i).inventory_org_id,
2587 uom_code = p_x_material_req_tbl(i).uom_code,
2588 quantity = p_x_material_req_tbl(i).quantity,
2589 exclude_flag = p_x_material_req_tbl(i).exclude_flag,
2590 in_service = p_x_material_req_tbl(i).in_service, --pdoki added for OGMA 105 issue
2591 rework_percent = p_x_material_req_tbl(i).rework_percent,
2592 replace_percent = p_x_material_req_tbl(i).replace_percent,
2593 attribute_category = p_x_material_req_tbl(i).attribute_category,
2594 attribute1 = p_x_material_req_tbl(i).attribute1,
2595 attribute2 = p_x_material_req_tbl(i).attribute2,
2596 attribute3 = p_x_material_req_tbl(i).attribute3,
2597 attribute4 = p_x_material_req_tbl(i).attribute4,
2598 attribute5 = p_x_material_req_tbl(i).attribute5,
2599 attribute6 = p_x_material_req_tbl(i).attribute6,
2600 attribute7 = p_x_material_req_tbl(i).attribute7,
2601 attribute8 = p_x_material_req_tbl(i).attribute8,
2602 attribute9 = p_x_material_req_tbl(i).attribute9,
2603 attribute10 = p_x_material_req_tbl(i).attribute10,
2604 attribute11 = p_x_material_req_tbl(i).attribute11,
2605 attribute12 = p_x_material_req_tbl(i).attribute12,
2606 attribute13 = p_x_material_req_tbl(i).attribute13,
2607 attribute14 = p_x_material_req_tbl(i).attribute14,
2608 attribute15 = p_x_material_req_tbl(i).attribute15,
2609 last_update_date = SYSDATE,
2610 last_updated_by = FND_GLOBAL.user_id,
2611 last_update_login = FND_GLOBAL.login_id
2612 WHERE rt_oper_material_id = p_x_material_req_tbl(i).rt_oper_material_id
2613 AND object_version_number = p_x_material_req_tbl(i).object_version_number;
2614
2615 -- If the record does not exist, then, abort API.
2616 IF ( SQL%ROWCOUNT = 0 ) THEN
2617 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
2618 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_material_req_tbl(i) ) );
2619 FND_MSG_PUB.add;
2620 RAISE FND_API.G_EXC_ERROR;
2621 END IF;
2622
2623 -- Set OUT values
2624 p_x_material_req_tbl(i).object_version_number := p_x_material_req_tbl(i).object_version_number + 1;
2625
2626 EXCEPTION
2627 WHEN OTHERS THEN
2628 IF ( SQLCODE = -1 ) THEN
2629 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_MATERIAL_REQ_REC_DUP' );
2630 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_material_req_tbl(i) ) );
2631 FND_MSG_PUB.add;
2632 RAISE FND_API.G_EXC_ERROR;
2633 ELSE
2634 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
2635 THEN
2636 fnd_log.string
2637 (
2638 fnd_log.level_unexpected,
2639 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME,
2640 'AHL_RT_OPER_MATERIALS update error = ['||SQLERRM||']'
2641 );
2642 END IF;
2643 END IF;
2644 END;
2645
2646 ELSIF ( p_x_material_req_tbl(i).dml_operation = 'D' ) THEN
2647
2648 -- Delete the record
2649 DELETE AHL_RT_OPER_MATERIALS
2650 WHERE rt_oper_material_id = p_x_material_req_tbl(i).rt_oper_material_id
2651 AND object_version_number = p_x_material_req_tbl(i).object_version_number;
2652
2653 -- If the record does not exist, then, abort API.
2654 IF ( SQL%ROWCOUNT = 0 ) THEN
2655 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
2656 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
2657 FND_MSG_PUB.add;
2658 RAISE FND_API.G_EXC_ERROR;
2659 END IF;
2660
2661 END IF;
2662 END LOOP;
2663
2664
2665 IF G_DEBUG = 'Y' THEN
2666 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after DML operation' );
2667 END IF;
2668
2669 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2670 l_msg_count := FND_MSG_PUB.count_msg;
2671 IF l_msg_count > 0 THEN
2672 x_msg_count := l_msg_count;
2673 RAISE FND_API.G_EXC_ERROR;
2674 END IF;
2675
2676 -- Perform cross records validations and duplicate records check
2677 validate_records
2678 (
2679 p_object_id, -- IN
2680 p_association_type, -- IN
2681 l_return_status -- OUT
2682 );
2683 -- If any severe error occurs, then, abort API.
2684 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2685 RAISE FND_API.G_EXC_ERROR;
2686 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2687 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2688 END IF;
2689
2690 IF G_DEBUG = 'Y' THEN
2691 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_records' );
2692 END IF;
2693
2694 -- Perform the Commit (if requested)
2695 IF FND_API.to_boolean( p_commit ) THEN
2696 COMMIT WORK;
2697 END IF;
2698
2699 -- Count and Get messages (optional)
2700 FND_MSG_PUB.count_and_get
2701 (
2702 p_encoded => FND_API.G_FALSE,
2703 p_count => x_msg_count,
2704 p_data => x_msg_data
2705 );
2706
2707 -- Disable debug (if enabled)
2708 IF ( G_DEBUG = 'Y' ) THEN
2709 AHL_DEBUG_PUB.disable_debug;
2710 END IF;
2711
2712 EXCEPTION
2713
2714 WHEN FND_API.G_EXC_ERROR THEN
2715 ROLLBACK TO process_material_req_PVT;
2716 x_return_status := FND_API.G_RET_STS_ERROR ;
2717 FND_MSG_PUB.count_and_get
2718 (
2719 p_encoded => FND_API.G_FALSE,
2720 p_count => x_msg_count,
2721 p_data => x_msg_data
2722 );
2723
2724 -- Disable debug (if enabled)
2725 IF ( G_DEBUG = 'Y' ) THEN
2726 AHL_DEBUG_PUB.disable_debug;
2727 END IF;
2728
2729 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2730 ROLLBACK TO process_material_req_PVT;
2731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2732 FND_MSG_PUB.count_and_get
2733 (
2734 p_encoded => FND_API.G_FALSE,
2735 p_count => x_msg_count,
2736 p_data => x_msg_data
2737 );
2738
2739 -- Disable debug (if enabled)
2740 IF ( G_DEBUG = 'Y' ) THEN
2741 AHL_DEBUG_PUB.disable_debug;
2742 END IF;
2743
2744 WHEN OTHERS THEN
2745 ROLLBACK TO process_material_req_PVT;
2746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2747 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2748 THEN
2749 FND_MSG_PUB.add_exc_msg
2750 (
2751 p_pkg_name => G_PKG_NAME,
2752 p_procedure_name => G_API_NAME,
2753 p_error_text => SUBSTRB(SQLERRM,1,240)
2754 );
2755 END IF;
2756 FND_MSG_PUB.count_and_get
2757 (
2758 p_encoded => FND_API.G_FALSE,
2759 p_count => x_msg_count,
2760 p_data => x_msg_data
2761 );
2762
2763 -- Disable debug (if enabled)
2764 IF ( G_DEBUG = 'Y' ) THEN
2765 AHL_DEBUG_PUB.disable_debug;
2766 END IF;
2767
2768 END process_material_req;
2769
2770 PROCEDURE process_route_efcts
2771 (
2772 p_api_version IN NUMBER := 1.0,
2773 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2774 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2775 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2776 p_default IN VARCHAR2 := FND_API.G_FALSE,
2777 p_module_type IN VARCHAR2 := NULL,
2778 p_object_id IN NUMBER,
2779 x_return_status OUT NOCOPY VARCHAR2,
2780 x_msg_count OUT NOCOPY NUMBER,
2781 x_msg_data OUT NOCOPY VARCHAR2,
2782 p_x_route_efct_tbl IN OUT NOCOPY route_efct_tbl_type
2783 )
2784 IS
2785
2786 cursor get_route_status (p_route_id in number)
2787 is
2788 select revision_status_code
2789 from ahl_routes_app_v
2790 where route_id = p_route_id;
2791
2792 l_obj_status VARCHAR2(30);
2793
2794 l_api_version CONSTANT NUMBER := 1.0;
2795 l_return_status VARCHAR2(1);
2796 l_msg_count NUMBER;
2797 l_msg_data VARCHAR2(30);
2798 l_route_effectivitiy_id NUMBER;
2799 l_x_operation_rec AHL_RM_OPERATION_PVT.operation_rec_type ;
2800 l_x_route_rec AHL_RM_ROUTE_PVT.route_rec_type ;
2801 BEGIN
2802 -- Initialize API return status to success
2803 x_return_status := FND_API.G_RET_STS_SUCCESS;
2804
2805 -- Standard Start of API savepoint
2806 SAVEPOINT process_route_efcts;
2807
2808 -- Standard call to check for call compatibility.
2809 IF NOT FND_API.compatible_api_call
2810 (
2811 l_api_version,
2812 p_api_version,
2813 G_API_NAME1,
2814 G_PKG_NAME
2815 )
2816 THEN
2817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2818 END IF;
2819
2820 -- Initialize message list if p_init_msg_list is set to TRUE.
2821 IF FND_API.to_boolean( p_init_msg_list ) THEN
2822 FND_MSG_PUB.initialize;
2823 END IF;
2824
2825 IF G_DEBUG = 'Y' THEN
2826 AHL_DEBUG_PUB.enable_debug;
2827 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : Begin API' );
2828 END IF;
2829
2830 --This is to be added before calling validate_api_inputs
2831 -- Validate Application Usage
2832 AHL_RM_ROUTE_UTIL.validate_ApplnUsage
2833 (
2834 p_object_id => p_object_id,
2835 p_association_type => 'ROUTE',
2836 x_return_status => x_return_status,
2837 x_msg_data => x_msg_data
2838 );
2839
2840 -- If any severe error occurs, then, abort API.
2841 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2842 RAISE FND_API.G_EXC_ERROR;
2843 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2845 END IF;
2846
2847 -- Validate all the inputs of the API
2848 validate_efct_api_inputs
2849 (
2850 p_x_route_efct_tbl,
2851 p_object_id,
2852 l_return_status
2853 );
2854
2855 -- If any severe error occurs, then, abort API.
2856 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2857 RAISE FND_API.G_EXC_ERROR;
2858 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2859 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2860 END IF;
2861
2862 -- If the module type is JSP, then default values for ID columns of LOV attributes
2863 IF ( p_module_type = 'JSP' ) THEN
2864 FOR i IN 1..p_x_route_efct_tbl.count LOOP
2865 IF ( p_x_route_efct_tbl(i).dml_operation <> 'D' ) THEN
2866 clear_efct_attribute_ids
2867 (
2868 p_x_route_efct_tbl(i)
2869 );
2870 END IF;
2871 END LOOP;
2872 END IF;
2873
2874 -- Convert Values into Ids.
2875 FOR i IN 1..p_x_route_efct_tbl.count LOOP
2876 IF ( p_x_route_efct_tbl(i).dml_operation <> 'D' ) THEN
2877 convert_efct_values_to_ids
2878 (
2879 p_x_route_efct_tbl(i) ,
2880 l_return_status
2881 );
2882
2883 -- If any severe error occurs, then, abort API.
2884 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2885 RAISE FND_API.G_EXC_ERROR;
2886 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2888 END IF;
2889 END IF;
2890 END LOOP;
2891
2892 IF G_DEBUG = 'Y' THEN
2893 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after convert_values_to_ids' );
2894 END IF;
2895
2896 -- Validate all attributes (Item level validation)
2897 FOR i IN 1..p_x_route_efct_tbl.count LOOP
2898 validate_efct_attributes
2899 (
2900 p_x_route_efct_tbl(i),
2901 l_return_status
2902 );
2903
2904 -- If any severe error occurs, then, abort API.
2905 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2906 RAISE FND_API.G_EXC_ERROR;
2907 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2909 END IF;
2910 END LOOP;
2911
2912 IF G_DEBUG = 'Y' THEN
2913 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after validate_attributes' );
2914 END IF;
2915
2916 -- Default missing and unchanged attributes.
2917 FOR i IN 1..p_x_route_efct_tbl.count LOOP
2918 IF ( p_x_route_efct_tbl(i).dml_operation = 'U' ) THEN
2919 default_efct_unchange_attribs
2920 (
2921 p_x_route_efct_tbl(i) -- IN OUT
2922 );
2923 ELSIF ( p_x_route_efct_tbl(i).dml_operation = 'C' ) THEN
2924 default_efct_miss_attributes
2925 (
2926 p_x_route_efct_tbl(i) -- IN OUT
2927 );
2928 END IF;
2929 END LOOP;
2930
2931 IF G_DEBUG = 'Y' THEN
2932 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after default_unchanged_attributes / default_missing_attributes' );
2933 END IF;
2934
2935 -- Perform cross attribute validation and missing attribute checks (Record level validation)
2936 FOR i IN 1..p_x_route_efct_tbl.count LOOP
2937 IF ( p_x_route_efct_tbl(i).dml_operation <> 'D' ) THEN
2938 validate_efct_record
2939 (
2940 p_x_route_efct_tbl(i),
2941 l_return_status
2942 );
2943
2944 -- If any severe error occurs, then, abort API.
2945 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2946 RAISE FND_API.G_EXC_ERROR;
2947 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2948 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2949 END IF;
2950 END IF;
2951 END LOOP;
2952
2953 IF G_DEBUG = 'Y' THEN
2954 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after validate_record' );
2955 END IF;
2956 IF G_DEBUG='Y' THEN
2957 AHL_DEBUG_PUB.debug( 'Starting updating parent route/operation');
2958 END IF;
2959
2960 IF G_DEBUG='Y' THEN
2961 AHL_DEBUG_PUB.debug( 'p_association_type = ROUTE');
2962 END IF;
2963
2964 -- Check if the Route is existing and in Draft status
2965 AHL_RM_ROUTE_UTIL.validate_route_status
2966 (
2967 p_object_id,
2968 l_msg_data,
2969 l_return_status
2970 );
2971
2972 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2973 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
2974 FND_MSG_PUB.ADD;
2975 x_return_status := l_return_status;
2976 RETURN;
2977 END IF;
2978
2979 -- Update route status from APPROVAL_REJECTED to DRAFT
2980 OPEN get_route_status (p_object_id);
2981 FETCH get_route_status INTO l_obj_status;
2982 IF (get_route_status%FOUND AND l_obj_status = 'APPROVAL_REJECTED')
2983 THEN
2984 UPDATE ahl_routes_b
2985 SET revision_status_code = 'DRAFT'
2986 WHERE route_id = p_object_id;
2987 END IF;
2988 CLOSE get_route_status;
2989
2990 -- Perform the DML statement directly.
2991 FOR i IN 1..p_x_route_efct_tbl.count LOOP
2992 IF ( p_x_route_efct_tbl(i).dml_operation = 'C' ) THEN
2993 IF G_DEBUG = 'Y' THEN
2994 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : p_x_route_efct_tbl(i).dml_operation ' || p_x_route_efct_tbl(i).dml_operation ) ;
2995 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before DML p_object_id ' || p_object_id );
2996 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before DML p_x_route_efct_tbl(i).INVENTORY_ITEM_ID ' || p_x_route_efct_tbl(i).INVENTORY_ITEM_ID );
2997 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before insert p_x_route_efct_tbl(i).INVENTORY_MASTER_ORG_ID ' || p_x_route_efct_tbl(i).INVENTORY_MASTER_ORG_ID );
2998 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before insert p_x_route_efct_tbl(i).MC_ID ' || p_x_route_efct_tbl(i).MC_ID );
2999 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before insert p_x_route_efct_tbl(i).MC_HEADER_ID ' || p_x_route_efct_tbl(i).MC_HEADER_ID );
3000 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : before insert p_x_route_efct_tbl(i).object_version_number ' || p_x_route_efct_tbl(i).object_version_number);
3001 END IF;
3002 -- Insert the record
3003 p_x_route_efct_tbl(i).object_version_number := 1;
3004
3005 INSERT INTO ahl_route_effectivities
3006 (
3007 route_effectivity_id
3008 , route_id
3009 , inventory_item_id
3010 , inventory_master_org_id
3011 , mc_id
3012 , mc_header_id ,
3013 OBJECT_VERSION_NUMBER,
3014 LAST_UPDATE_DATE,
3015 LAST_UPDATED_BY,
3016 CREATION_DATE,
3017 CREATED_BY,
3018 LAST_UPDATE_LOGIN,
3019 security_group_id,
3020 ATTRIBUTE_CATEGORY,
3021 ATTRIBUTE1,
3022 ATTRIBUTE2,
3023 ATTRIBUTE3,
3024 ATTRIBUTE4,
3025 ATTRIBUTE5,
3026 ATTRIBUTE6,
3027 ATTRIBUTE7,
3028 ATTRIBUTE8,
3029 ATTRIBUTE9,
3030 ATTRIBUTE10,
3031 ATTRIBUTE11,
3032 ATTRIBUTE12,
3033 ATTRIBUTE13,
3034 ATTRIBUTE14,
3035 ATTRIBUTE15
3036 ) VALUES
3037 (
3038 ahl_route_effectivities_s.nextval,
3039 p_object_id,
3040 p_x_route_efct_tbl(i).INVENTORY_ITEM_ID,
3041 p_x_route_efct_tbl(i).INVENTORY_MASTER_ORG_ID,
3042 p_x_route_efct_tbl(i).MC_ID,
3043 p_x_route_efct_tbl(i).MC_HEADER_ID,
3044 p_x_route_efct_tbl(i).object_version_number,
3045 SYSDATE,
3046 FND_GLOBAL.USER_ID,
3047 SYSDATE,
3048 FND_GLOBAL.USER_ID,
3049 FND_GLOBAL.LOGIN_ID,
3050 NULL,
3051 p_x_route_efct_tbl(i).attribute_category,
3052 p_x_route_efct_tbl(i).attribute1,
3053 p_x_route_efct_tbl(i).attribute2,
3054 p_x_route_efct_tbl(i).attribute3,
3055 p_x_route_efct_tbl(i).attribute4,
3056 p_x_route_efct_tbl(i).attribute5,
3057 p_x_route_efct_tbl(i).attribute6,
3058 p_x_route_efct_tbl(i).attribute7,
3059 p_x_route_efct_tbl(i).attribute8,
3060 p_x_route_efct_tbl(i).attribute9,
3061 p_x_route_efct_tbl(i).attribute10,
3062 p_x_route_efct_tbl(i).attribute11,
3063 p_x_route_efct_tbl(i).attribute12,
3064 p_x_route_efct_tbl(i).attribute13,
3065 p_x_route_efct_tbl(i).attribute14,
3066 p_x_route_efct_tbl(i).attribute15
3067 ) RETURNING route_effectivity_id INTO l_route_effectivitiy_id ;
3068
3069 IF G_DEBUG = 'Y' THEN
3070 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after DML p_object_id ' || p_object_id );
3071 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_route_efct_tbl(i).INVENTORY_MASTER_ORG_ID ' || p_x_route_efct_tbl(i).INVENTORY_MASTER_ORG_ID );
3072 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_route_efct_tbl(i).MC_ID ' || p_x_route_efct_tbl(i).MC_ID );
3073 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_route_efct_tbl(i).MC_HEADER_ID ' || p_x_route_efct_tbl(i).MC_HEADER_ID );
3074 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after insert p_x_route_efct_tbl(i).object_version_number ' || p_x_route_efct_tbl(i).object_version_number);
3075 END IF;
3076
3077 -- Set OUT values
3078 p_x_route_efct_tbl(i).route_effectivity_id := l_route_effectivitiy_id;
3079
3080 ELSIF ( p_x_route_efct_tbl(i).dml_operation = 'U' ) THEN
3081
3082 -- Update the record
3083 UPDATE ahl_route_effectivities
3084 SET
3085 object_version_number = object_version_number + 1,
3086 last_update_date = SYSDATE,
3087 last_updated_by = FND_GLOBAL.USER_ID,
3088 last_update_login = FND_GLOBAL.LOGIN_ID,
3089 security_group_id = p_x_route_efct_tbl(i).security_group_id,
3090 attribute_category = p_x_route_efct_tbl(i).attribute_category,
3091 attribute1 = p_x_route_efct_tbl(i).attribute1,
3092 attribute2 = p_x_route_efct_tbl(i).attribute2,
3093 attribute3 = p_x_route_efct_tbl(i).attribute3,
3094 attribute4 = p_x_route_efct_tbl(i).attribute4,
3095 attribute5 = p_x_route_efct_tbl(i).attribute5,
3096 attribute6 = p_x_route_efct_tbl(i).attribute6,
3097 attribute7 = p_x_route_efct_tbl(i).attribute7,
3098 attribute8 = p_x_route_efct_tbl(i).attribute8,
3099 attribute9 = p_x_route_efct_tbl(i).attribute9,
3100 attribute10 = p_x_route_efct_tbl(i).attribute10,
3101 attribute11 = p_x_route_efct_tbl(i).attribute11,
3102 attribute12 = p_x_route_efct_tbl(i).attribute12,
3103 attribute13 = p_x_route_efct_tbl(i).attribute13,
3104 attribute14 = p_x_route_efct_tbl(i).attribute14,
3105 attribute15 = p_x_route_efct_tbl(i).attribute15
3106 WHERE route_effectivity_id = p_x_route_efct_tbl(i).route_effectivity_id
3107 AND object_version_number = p_x_route_efct_tbl(i).object_version_number;
3108
3109 -- If the record does not exist, then, abort API.
3110 IF ( SQL%ROWCOUNT = 0 ) THEN
3111 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
3112 -- FND_MESSAGE.set_token( 'RECORD', p_x_route_efct_tbl(i).aso_resource_name );
3113 FND_MSG_PUB.add;
3114 END IF;
3115
3116 -- Set OUT values
3117 p_x_route_efct_tbl(i).object_version_number := p_x_route_efct_tbl(i).object_version_number + 1;
3118
3119 ELSIF ( p_x_route_efct_tbl(i).dml_operation = 'D' ) THEN
3120
3121 -- Delete the record
3122 DELETE FROM AHL_RT_OPER_MATERIALS
3123 WHERE OBJECT_ID = p_x_route_efct_tbl(i).route_effectivity_id
3124 AND ASSOCIATION_TYPE_CODE = 'DISPOSITION';
3125
3126 -- If no records exist, then, Continue.
3127 IF ( SQL%ROWCOUNT = 0 ) THEN
3128 -- Ignore the Exception
3129 NULL;
3130 END IF;
3131
3132
3133 DELETE FROM ahl_route_effectivities
3134 WHERE route_effectivity_id = p_x_route_efct_tbl(i).route_effectivity_id
3135 AND object_version_number = p_x_route_efct_tbl(i).object_version_number;
3136
3137
3138
3139 -- If the record does not exist, then, abort API.
3140 IF ( SQL%ROWCOUNT = 0 ) THEN
3141 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
3142 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
3143 FND_MSG_PUB.add;
3144 END IF;
3145 END IF;
3146
3147 END LOOP;
3148
3149 IF G_DEBUG = 'Y' THEN
3150 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after DML operation' );
3151 END IF;
3152
3153 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
3154 l_msg_count := FND_MSG_PUB.count_msg;
3155 IF l_msg_count > 0 THEN
3156 x_msg_count := l_msg_count;
3157 RAISE FND_API.G_EXC_ERROR;
3158 END IF;
3159
3160 -- Perform cross records validations and duplicate records check
3161
3162 validate_efct_records
3163 (
3164 p_object_id,
3165 l_return_status
3166 );
3167
3168 -- If any severe error occurs, then, abort API.
3169 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3170 RAISE FND_API.G_EXC_ERROR;
3171 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3173 END IF;
3174
3175 IF G_DEBUG = 'Y' THEN
3176 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME1 || ' : after validate_records' );
3177 END IF;
3178
3179 -- Perform the Commit (if requested)
3180 IF FND_API.to_boolean( p_commit ) THEN
3181 COMMIT WORK;
3182 END IF;
3183
3184 -- Count and Get messages (optional)
3185 FND_MSG_PUB.count_and_get
3186 (
3187 p_encoded => FND_API.G_FALSE,
3188 p_count => x_msg_count,
3189 p_data => x_msg_data
3190 );
3191
3192 -- Disable debug (if enabled)
3193 IF G_DEBUG = 'Y' THEN
3194 AHL_DEBUG_PUB.disable_debug;
3195 END IF;
3196
3197 EXCEPTION
3198 WHEN FND_API.G_EXC_ERROR THEN
3199 ROLLBACK TO process_route_efcts;
3200 x_return_status := FND_API.G_RET_STS_ERROR ;
3201 FND_MSG_PUB.count_and_get
3202 (
3203 p_encoded => FND_API.G_FALSE,
3204 p_count => x_msg_count,
3205 p_data => x_msg_data
3206 );
3207
3208 -- Disable debug (if enabled)
3209 IF G_DEBUG = 'Y' THEN
3210 AHL_DEBUG_PUB.disable_debug;
3211 END IF;
3212
3213 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3214 ROLLBACK TO process_route_efcts;
3215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3216 FND_MSG_PUB.count_and_get
3217 (
3218 p_encoded => FND_API.G_FALSE,
3219 p_count => x_msg_count,
3220 p_data => x_msg_data
3221 );
3222
3223 -- Disable debug (if enabled)
3224 IF G_DEBUG = 'Y' THEN
3225 AHL_DEBUG_PUB.disable_debug;
3226 END IF;
3227
3228 WHEN OTHERS THEN
3229 ROLLBACK TO process_route_efcts;
3230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3231 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3232 THEN
3233 FND_MSG_PUB.add_exc_msg
3234 (
3235 p_pkg_name => G_PKG_NAME,
3236 p_procedure_name => G_API_NAME1,
3237 p_error_text => SUBSTRB(SQLERRM,1,240)
3238 );
3239 END IF;
3240 FND_MSG_PUB.count_and_get
3241 (
3242 p_encoded => FND_API.G_FALSE,
3243 p_count => x_msg_count,
3244 p_data => x_msg_data
3245 );
3246
3247 -- Disable debug (if enabled)
3248 IF G_DEBUG = 'Y' THEN
3249 AHL_DEBUG_PUB.disable_debug;
3250 END IF;
3251
3252 END process_route_efcts;
3253
3254 END AHL_RM_MATERIAL_AS_PVT;