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