1 PACKAGE BODY AHL_MC_ITEMGROUP_PUB AS
2 /* $Header: AHLPIGPB.pls 115.18 2004/04/16 14:46:23 sjayacha noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_MC_ITEMGROUP_PVT';
6
7
8
9 ------------------------------
10 -- Declare Local Procedures --
11 ------------------------------
12
13 PROCEDURE Convert_InTo_ID(p_x_item_assoc_rec IN OUT NOCOPY AHL_MC_ItemGroup_Pvt.Item_association_rec_type);
14
15
16
17 -----------------------------------------
18 -- Define Procedures for Item Groups --
19 -----------------------------------------
20
21 -- Start of Comments --
22 -- Procedure name : PROCESS_ITEM_GROUP
23 -- Type : Public
24 -- Function : Creates Item Group for Master Configuration in ahl_item_groups_b and TL tables. Also creates item-group association in
25 --
26 --ahl_item_associations table.
27 -- Pre-reqs :
28 -- Parameters :
29 -- Standard IN Parameters :
30 -- p_api_version IN NUMBER Required
31 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
32 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
33 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
34 --
35 -- Standard OUT Parameters :
36 -- x_return_status OUT VARCHAR2 Required
37 -- x_msg_count OUT NUMBER Required
38 -- x_msg_data OUT VARCHAR2 Required
39 -- Item Group Record :
40 -- Name or item_group_id Required.
41 -- operation_flag only if record needs to be modified (M) or deleted.(D)
42 -- Item Associations Record :
43 -- Inventory_item_id/item_number or organization_code/Organization_id
44 -- Required, present and trackable in mtl_system_items_b.
45 -- priority Required.
46 -- Operation_code Required to be 'C'.(Create)
47 -- INterchange_code if present, must exist in fnd_lookups.
48 -- Item_type if present, must exist in fnd_lookups.
49 --
50 -- End of Comments --
51
52
53 PROCEDURE PROCESS_ITEM_GROUP(p_api_version IN NUMBER,
54 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
55 p_commit IN VARCHAR2 := FND_API.G_FALSE,
56 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
57 p_module_type IN VARCHAR2 := NULL,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2,
61 p_x_item_group_rec IN OUT NOCOPY AHL_MC_ItemGroup_Pvt.Item_Group_Rec_Type,
62 p_x_items_tbl IN OUT NOCOPY AHL_MC_ItemGroup_Pvt.Item_Association_Tbl_Type
63 ) IS
64
65
66 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Group';
67 l_api_version CONSTANT NUMBER := 1.0;
68
69
70 -- For item_group_id.
71 CURSOR ahl_item_group_csr(p_grp_name IN VARCHAR2) IS
72 SELECT item_group_id,
73 type_code
74 FROM ahl_item_groups_b
75 WHERE name = p_grp_name;
76
77 -- For item_group_id.
78 CURSOR Item_grp_name_csr(p_item_grp_id IN VARCHAR2) IS
79 SELECT name,
80 type_code
81 FROM ahl_item_groups_b
82 WHERE item_group_id = p_item_grp_id;
83
84 l_item_group_id NUMBER;
85 l_item_group_name VARCHAR2(30);
86 l_type_code VARCHAR2(30);
87 l_item_group_rec AHL_MC_ItemGroup_Pvt.Item_Group_Rec_Type DEFAULT p_X_item_group_rec;
88 l_lookup_code VARCHAR2(30);
89 l_return_val BOOLEAN;
90 l_status VARCHAR2(1);
91
92
93 BEGIN
94
95 -- Standard start of API savepoint
96 SAVEPOINT Process_Item_group_Pub;
97
98 -- Standard call to check for call compatibility
99 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
100 G_PKG_NAME) THEN
101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
102 END IF;
103
104 -- Initialize message list if p_init_msg_list is set to TRUE
105 IF FND_API.To_Boolean(p_init_msg_list) THEN
106 FND_MSG_PUB.Initialize;
107 END IF;
108
109 -- Initialize API return status to success
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111
112 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
113 THEN
114 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
115 'ahl_mc_itemgroup_pub.process_item_group', 'Begin of process_item_group');
116 END IF;
117
118 IF l_item_group_rec.OPERATION_FLAG <> 'D' THEN
119 IF l_item_group_rec.type_code IS NOT NULL THEN
120 IF NOT AHL_UTIL_MC_PKG.VALIDATE_LOOKUP_CODE(P_LOOKUP_TYPE => 'AHL_ITEMGROUP_TYPE',
121 P_LOOKUP_CODE =>l_item_group_rec.type_code) THEN
122 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_TYPE_INAVLID');
123 FND_MSG_PUB.ADD;
124 END IF;
125 ELSIF l_item_group_rec.type_meaning IS NOT NULL THEN
126 AHL_UTIL_MC_PKG.CONVERT_TO_LOOKUPCODE(
127 P_LOOKUP_TYPE => 'AHL_ITEMGROUP_TYPE',
128 P_LOOKUP_MEANING => l_item_group_rec.TYPE_MEANING,
129 X_LOOKUP_CODE => l_item_group_rec.type_code,
130 X_RETURN_VAL => l_return_val);
131 IF NOT l_return_val THEN
132 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_TYPE_INAVLID');
133 FND_MSG_PUB.ADD;
134 END IF;
135 ELSIF l_item_group_rec.OPERATION_FLAG = 'C' THEN
136 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_TYPE_NULL');
137 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_group_rec.name);
138 FND_MSG_PUB.ADD;
139 END IF;
140
141 IF l_item_group_rec.status_code IS NOT NULL THEN
142 IF NOT AHL_UTIL_MC_PKG.VALIDATE_LOOKUP_CODE(P_LOOKUP_TYPE => 'AHL_ITEMGROUP_STATUS',
143 P_LOOKUP_CODE =>l_item_group_rec.status_code) THEN
144 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_STATUS_INAVLID');
145 FND_MSG_PUB.ADD;
146 END IF;
147 ELSIF l_item_group_rec.status_meaning IS NOT NULL THEN
148 AHL_UTIL_MC_PKG.CONVERT_TO_LOOKUPCODE(
149 P_LOOKUP_TYPE => 'AHL_ITEMGROUP_STATUS',
150 P_LOOKUP_MEANING => l_item_group_rec.status_meaning,
151 X_LOOKUP_CODE => l_item_group_rec.status_code,
152 X_RETURN_VAL => l_return_val);
153 IF NOT l_return_val THEN
154 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_STATUS_INAVLID');
155 FND_MSG_PUB.ADD;
156 END IF;
157 END IF;
158
159 END IF;
160
161 -- Convert values to ID's for Item Association record columns.
162 IF (p_x_items_tbl.COUNT > 0) THEN
163 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
164 THEN
165 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
166 'ahl_mc_itemgroup_pub.process_item_group', 'Begin Loop to convert Item Name to Id');
167 END IF;
168
169
170 FOR i IN p_x_items_tbl.FIRST..p_x_items_tbl.LAST LOOP
171
172 -- Check p_module_type.
173 -- Blank out id's and re-built them based on Values.
174 IF (p_module_type = 'JSP') THEN
175 p_x_items_tbl(i).inventory_item_id := NULL;
176 END IF;
177 IF p_x_items_tbl(i).OPERATION_FLAG <> 'D' THEN
178 Convert_InTo_ID(p_x_items_tbl(i));
179
180 END IF;
181
182 END LOOP;
183 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
184 THEN
185 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
186 'ahl_mc_itemgroup_pub.process_item_group', 'End of Loop');
187 END IF;
188
189 END IF;
190
191 IF l_item_group_rec.OPERATION_FLAG = 'C' THEN
192
193
194 IF l_item_group_rec.name IS NULL
195 THEN
196 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_NAME_NULL');
197 FND_MSG_PUB.ADD;
198
199 END IF;
200
201 IF l_item_group_rec.status_code IS NOT NULL
202 AND l_item_group_rec.status_code <> FND_API.G_MISS_CHAR
203 AND l_item_group_rec.status_code <> 'DRAFT'
204 THEN
205 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_CRE_DRAFT');
206 FND_MSG_PUB.ADD;
207
208 END IF;
209
210 IF l_item_group_rec.type_code IS NULL AND
211 l_item_group_rec.TYPE_MEANING IS NULL
212 THEN
213 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_TYPE_NULL');
214 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_group_rec.name);
215 FND_MSG_PUB.ADD;
216 ELSIF l_item_group_rec.TYPE_MEANING IS NOT NULL THEN
217 AHL_UTIL_MC_PKG.Convert_To_LookupCode('AHL_ITEMGROUP_TYPE',
218 l_item_group_rec.TYPE_MEANING,
219 l_lookup_code,
220 l_return_val);
221 IF (l_return_val) THEN
222 p_x_item_group_rec.type_code := l_lookup_code;
223 ELSE
224 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_TYPE_INAVLID');
225 FND_MSG_PUB.ADD;
226 END IF;
227
228 END IF;
229 END IF;
230
231
232 IF (l_item_group_rec.OPERATION_FLAG in ('M','D')) THEN
233 -- Convert group name to ID for Item_group_rec.
234 IF (l_item_group_rec.item_group_id IS NULL) OR
235 (l_item_group_rec.item_group_id = FND_API.G_MISS_NUM)
236 THEN
237 -- Check if group name exists.
238 IF (l_item_group_rec.name IS NOT NULL) AND
239 (l_item_group_rec.name <> FND_API.G_MISS_CHAR) THEN
240 OPEN ahl_item_group_csr(l_item_group_rec.name);
241 FETCH ahl_item_group_csr INTO l_item_group_id,l_type_code;
242 IF (ahl_item_group_csr%FOUND) THEN
243 p_x_item_group_rec.item_group_id := l_item_group_id;
244 IF l_type_code <> p_x_item_group_rec.type_code THEN
245 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_TYPE_NOUPDATE');
246 FND_MSG_PUB.ADD;
247 END IF;
248
249 ELSE
250 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_INVALID');
251 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_group_rec.name);
252 FND_MSG_PUB.ADD;
253 END IF;
254
255 ELSE
256 -- Both ID and name are missing.
257 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_NULL');
258 FND_MSG_PUB.ADD;
259
260 END IF;
261 ELSIF NVL(p_module_type,'X') <> 'JSP' THEN
262 OPEN Item_grp_name_csr(l_item_group_rec.item_group_id);
263 FETCH Item_grp_name_csr INTO l_item_group_name,l_type_code;
264 IF (Item_grp_name_csr%FOUND) THEN
265 IF l_item_group_name <> p_x_item_group_rec.name
266 THEN
267 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_INVALID');
268 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_group_rec.name);
269 FND_MSG_PUB.ADD;
270 END IF;
271 IF l_type_code <> p_x_item_group_rec.type_code THEN
272 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_TYPE_NOUPDATE');
273 FND_MSG_PUB.ADD;
274 END IF;
275
276 ELSE
277 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_INVALID');
278 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_group_rec.name);
279 FND_MSG_PUB.ADD;
280 END IF;
281
282 END IF;
283
284 END IF;
285
286 IF (l_item_group_rec.OPERATION_FLAG = 'M') THEN
287
288 IF (p_x_items_tbl.COUNT > 0) THEN
289 FOR i IN p_x_items_tbl.FIRST..p_x_items_tbl.LAST LOOP
290
291 -- For group name.
292 IF (p_x_items_tbl(i).item_group_id IS NULL) OR
293 (p_x_items_tbl(i).item_group_id = FND_API.G_MISS_NUM)
294 THEN
295 -- Check if assoc group name same as group_rec name.
296 IF (p_x_items_tbl(i).item_group_name = l_item_group_rec.name) THEN
297 p_x_items_tbl(i).item_group_id := p_x_item_group_rec.item_group_id;
298 ELSE
299 -- if group name exists then it does not match group_rec.
300 IF (p_x_items_tbl(i).item_group_name IS NOT NULL) AND
301 (p_x_items_tbl(i).item_group_name <> FND_API.G_MISS_CHAR) THEN
302 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_MISMATCH');
303 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_group_rec.name);
304 FND_MESSAGE.Set_Token('ASSO_GRP',p_x_items_tbl(i).item_group_name);
305 FND_MSG_PUB.ADD;
306 --dbms_output.put_line('Item Association record does not match Item Group');
307 ELSE
308 -- Both ID and Name are missing.
309 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_NULL');
310 FND_MSG_PUB.ADD;
311 END IF;
312 END IF;
313 END IF;
314
315
316 END LOOP;
317 END IF; /* for count > 0 */
318
319 END IF;
320
321 -- Check Error Message stack.
322 x_msg_count := FND_MSG_PUB.count_msg;
323 IF x_msg_count > 0 THEN
324 RAISE FND_API.G_EXC_ERROR;
325 END IF;
326
327
328 IF p_x_item_group_rec.operation_flag ='C' THEN
329
330
331 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
332 THEN
333 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
334 'ahl_mc_itemgroup_pub.process_item_group', 'Calling ahl_mc_itemgroup_pvt.Create_Item_group');
335 END IF;
336
337 -- Call Private API for Create
338
339 ahl_mc_itemgroup_pvt.Create_Item_group(
340 p_api_version => p_api_version,
341 --p_init_msg_list => p_init_msg_list,
342 --p_commit => p_commit,
343 p_validation_level => p_validation_level,
344 p_x_item_group_rec => p_x_item_group_rec,
345 p_x_items_tbl => p_x_items_tbl ,
346 x_return_status => x_return_status,
347 x_msg_count => x_msg_count,
348 x_msg_data => x_msg_data );
349
350 ELSIF p_x_item_group_rec.operation_flag ='M' THEN
351
352 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
353 THEN
354 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
355 'ahl_mc_itemgroup_pub.process_item_group', 'Calling ahl_mc_itemgroup_pvt.Modify_Item_group');
356 END IF;
357
358 -- Call Private API for Create
359 AHL_MC_ItemGroup_Pvt.Modify_Item_group(
360 p_api_version => p_api_version,
361 p_init_msg_list => p_init_msg_list,
362 --p_commit => p_commit,
363 p_validation_level => p_validation_level,
364 p_item_group_rec => p_x_item_group_rec,
365 p_x_items_tbl => p_x_items_tbl ,
366 x_return_status => x_return_status,
367 x_msg_count => x_msg_count,
368 x_msg_data => x_msg_data );
369
370 ELSIF p_x_item_group_rec.operation_flag ='D' THEN
371
372 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
373 THEN
374 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
375 'ahl_mc_itemgroup_pub.process_item_group', 'Calling ahl_mc_itemgroup_pvt.Remove_Item_group');
376 END IF;
377
378 -- Call Private API for Create
379 AHL_MC_ItemGroup_Pvt.Remove_Item_group(
380 p_api_version => p_api_version,
384 p_item_group_rec => p_x_item_group_rec,
381 --p_init_msg_list => p_init_msg_list,
382 --p_commit => p_commit,
383 p_validation_level => p_validation_level,
385 x_return_status => x_return_status,
386 x_msg_count => x_msg_count,
387 x_msg_data => x_msg_data );
388
389 END IF;
390
391 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
392 RAISE FND_API.G_EXC_ERROR;
393 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
394 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
395 END IF;
396
397 -- Standard check of p_commit
398 IF FND_API.To_Boolean(p_commit) THEN
399 COMMIT WORK;
400 END IF;
401
402 -- Standard call to get message count and if count is 1, get message
403 FND_MSG_PUB.Count_And_Get
404 ( p_count => x_msg_count,
405 p_data => x_msg_data,
406 p_encoded => fnd_api.g_false);
407
408 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
409 THEN
410 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
411 'ahl_mc_itemgroup_pub.process_item_group', 'End of process_item_group');
412 END IF;
413
414 --
415 EXCEPTION
416 WHEN FND_API.G_EXC_ERROR THEN
417 x_return_status := FND_API.G_RET_STS_ERROR;
418 Rollback to Process_Item_group_Pub;
419 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
420 p_data => x_msg_data,
421 p_encoded => fnd_api.g_false);
422
423 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
424 THEN
425 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
426 'ahl_mc_itemgroup_pub.process_item_group', 'Error in process_item_group');
427 END IF;
428
429
430 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
432 Rollback to Process_Item_group_Pub;
433 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
434 p_data => x_msg_data,
435 p_encoded => fnd_api.g_false);
436 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
437 THEN
438 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
439 'ahl_mc_itemgroup_pub.process_item_group', 'Unexpected Error in process_item_group');
440 END IF;
441
442 WHEN OTHERS THEN
443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
444 Rollback to Process_Item_group_Pub;
445 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
446 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
447 p_procedure_name => 'Process_Item_group',
448 p_error_text => SQLERRM);
449 END IF;
450 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
451 p_data => x_msg_data,
452 p_encoded => fnd_api.g_false);
453 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
454 THEN
455 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
456 'ahl_mc_itemgroup_pub.process_item_group', 'Unknown Error in process_item_group');
457 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
458 'ahl_mc_itemgroup_pub.process_item_group', SQLERRM);
459 END IF;
460
461
462
463 END PROCESS_ITEM_GROUP;
464
465
466
467 ------------------------------
468 -- Define Local Procedures --
469 ------------------------------
470
471 PROCEDURE Convert_InTo_ID(p_x_item_assoc_rec IN OUT NOCOPY AHL_MC_ItemGroup_Pvt.Item_association_rec_type) IS
472
473
474 -- For organization id.
475 CURSOR mtl_parameters_csr (p_org_code IN VARCHAR2) IS
476 SELECT organization_id
477 FROM mtl_parameters
478 WHERE organization_code = p_org_code;
479
480 -- For inventory_item_id.
481 CURSOR mtl_system_items_csr(p_inventory_item_name IN VARCHAR2,
482 p_inv_organization_id IN NUMBER) IS
483 SELECT inventory_item_id
484 FROM ahl_mtl_items_non_ou_v
485 WHERE concatenated_segments = p_inventory_item_name
486 AND inventory_org_id = p_inv_organization_id;
487
488 -- For concatenated segments.
489 CURSOR mtl_segment_csr(p_inventory_item_id IN NUMBER,
490 p_inv_organization_id IN NUMBER) IS
491 SELECT concatenated_segments
492 FROM ahl_mtl_items_non_ou_v
493 WHERE inventory_item_id = p_inventory_item_id
494 AND inventory_org_id = p_inv_organization_id;
495
496 -- For item association id.
497 CURSOR ahl_item_associations_csr(p_item_grp_id IN NUMBER,
498 p_inventory_org_id IN NUMBER,
499 p_inventory_item_id IN NUMBER) IS
500 SELECT item_association_id
501 FROM ahl_item_associations_vl
502 WHERE item_group_id = p_item_grp_id
503 AND inventory_org_id = p_inventory_org_id
504 AND inventory_item_id = p_inventory_item_id;
505
506
507 l_inventory_id NUMBER;
511 l_item_group_id NUMBER;
508 l_item_assoc_rec ahl_mc_itemgroup_pvt.Item_association_rec_type DEFAULT p_x_item_assoc_rec;
509 l_inventory_org_id NUMBER;
510 l_item_association_id NUMBER;
512 l_lookup_code fnd_lookups.lookup_code%TYPE;
513 l_return_val BOOLEAN;
514 l_concatenated_segments ahl_mtl_items_non_ou_v.concatenated_segments%TYPE;
515 l_dummy varchar2(200);
516
517 BEGIN
518
519 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
520 THEN
521 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
522 'ahl_mc_itemgroup_pub.Convert_InTo_ID', 'Begin of Convert_InTo_ID');
523 END IF;
524
525
526 -- For Inventory Organization Code.
527 IF (l_item_assoc_rec.inventory_org_id IS NULL) OR
528 (l_item_assoc_rec.inventory_org_id = FND_API.G_MISS_NUM)
529 THEN
530 -- if code is present.
531 IF (l_item_assoc_rec.inventory_org_code IS NOT NULL) AND
532 (l_item_assoc_rec.inventory_org_code <> FND_API.G_MISS_CHAR) THEN
533 OPEN mtl_parameters_csr (l_item_assoc_rec.inventory_org_code);
534 FETCH mtl_parameters_csr INTO l_inventory_org_id;
535 IF (mtl_parameters_csr%FOUND) THEN
536 l_item_assoc_rec.inventory_org_id := l_inventory_org_id;
537 ELSE
538 FND_MESSAGE.Set_Name('AHL','AHL_MC_ORG_INVALID');
539 FND_MESSAGE.Set_Token('ORG',l_item_assoc_rec.inventory_org_code);
540 FND_MSG_PUB.ADD;
541 END IF;
542 CLOSE mtl_parameters_csr;
543 ELSIF (l_item_assoc_rec.operation_flag = 'C') THEN
544 -- Both ID and code are missing.
545 FND_MESSAGE.Set_Name('AHL','AHL_MC_ORG_NULL');
546 FND_MSG_PUB.ADD;
547 END IF;
548
549 END IF;
550
551 -- For Inventory item.
552 IF (l_item_assoc_rec.inventory_item_id IS NULL) OR
553 (l_item_assoc_rec.inventory_item_id = FND_API.G_MISS_NUM)
554 THEN
555 -- check if name exists.
556 IF (l_item_assoc_rec.inventory_item_name IS NOT NULL) AND
557 (l_item_assoc_rec.inventory_item_name <> FND_API.G_MISS_CHAR) THEN
558
559 OPEN mtl_system_items_csr(l_item_assoc_rec.inventory_item_name,
560 l_item_assoc_rec.inventory_org_id);
561 FETCH mtl_system_items_csr INTO l_inventory_id;
562 IF (mtl_system_items_csr%FOUND) THEN
563 l_item_assoc_rec.inventory_item_id := l_inventory_id;
564
565 ELSE
566 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_INVALID');
567 FND_MESSAGE.Set_Token('INV_ITEM',l_item_assoc_rec.inventory_item_name);
568 FND_MSG_PUB.ADD;
569 END IF;
570 CLOSE mtl_system_items_csr;
571 ELSIF (l_item_assoc_rec.operation_flag = 'C') THEN
572 -- Both ID and name missing.
573 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_NULL');
574 FND_MSG_PUB.ADD;
575 END IF;
576
577 ELSE
578 OPEN mtl_segment_csr(l_item_assoc_rec.inventory_item_id,
579 l_item_assoc_rec.inventory_org_id);
580 FETCH mtl_segment_csr INTO l_concatenated_segments;
581 IF (mtl_segment_csr%NOTFOUND) THEN
582 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_INVALID');
583 FND_MESSAGE.Set_Token('INV_ITEM',l_item_assoc_rec.inventory_item_id);
584 FND_MSG_PUB.ADD;
585 ELSE
586 l_item_assoc_rec.inventory_item_name := l_concatenated_segments;
587 END IF;
588 CLOSE mtl_segment_csr;
589
590 END IF;
591
592 -- For priority
593 IF (l_item_assoc_rec.priority IS NULL OR
594 l_item_assoc_rec.priority = FND_API.G_MISS_NUM)
595 THEN
596 FND_MESSAGE.Set_Name('AHL','AHL_MC_PRIORITY_NULL');
597 FND_MESSAGE.Set_Token('INV_ITEM',l_item_assoc_rec.inventory_item_name);
598 FND_MSG_PUB.ADD;
599 ELSIF ( INSTR(l_item_assoc_rec.priority,'.') > 0 )
600 THEN
601 FND_MESSAGE.Set_Name('AHL','AHL_MC_PRIORITY_INVALID_JSP');
602 FND_MSG_PUB.ADD;
603 END IF;
604
605
606 -- Check if item association id exists; if not populate it.
607 IF (l_item_assoc_rec.operation_flag <> 'C') THEN
608 IF (l_item_assoc_rec.item_association_id IS NULL OR
609 l_item_assoc_rec.item_association_id = FND_API.G_MISS_NUM) THEN
610 OPEN ahl_item_associations_csr(l_item_assoc_rec.item_group_id,
611 l_item_assoc_rec.inventory_org_id,
612 l_item_assoc_rec.inventory_item_id);
613
614 FETCH ahl_item_associations_csr INTO l_item_association_id;
615 IF (ahl_item_associations_csr%FOUND) THEN
616 l_item_assoc_rec.item_association_id := l_item_association_id;
617 ELSE
618 FND_MESSAGE.Set_Name('AHL','AHL_MC_ASSOC_NULL');
619 FND_MSG_PUB.ADD;
620 END IF;
621 END IF;
622 END IF; /* operation flag */
623
624 -- For Interchange_type_meaning.
625 IF (l_item_assoc_rec.interchange_type_code IS NULL) OR
626 (l_item_assoc_rec.interchange_type_code = FND_API.G_MISS_CHAR)
630 (l_item_assoc_rec.Interchange_type_meaning <> FND_API.G_MISS_CHAR) THEN
627 THEN
628 -- Check if meaning exists.
629 IF (l_item_assoc_rec.Interchange_type_meaning IS NOT NULL) AND
631 AHL_UTIL_MC_PKG.Convert_To_LookupCode('AHL_INTERCHANGE_ITEM_TYPE',
632 l_item_assoc_rec.Interchange_type_meaning,
633 l_lookup_code,
634 l_return_val);
635 IF (l_return_val) THEN
636 l_item_assoc_rec.interchange_type_code := l_lookup_code;
637 ELSE
638 FND_MESSAGE.Set_Name('AHL','AHL_MC_INTER_INVALID');
639 FND_MESSAGE.Set_Token('INV_ITEM',l_item_assoc_rec.inventory_item_name);
640 FND_MESSAGE.Set_Token('INTER_CODE',l_item_assoc_rec.Interchange_type_meaning);
641 FND_MSG_PUB.ADD;
642 END IF;
643 ELSE
644 IF (l_item_assoc_rec.Interchange_type_meaning IS NULL) THEN
645 l_item_assoc_rec.interchange_type_code := null;
646 END IF;
647 END IF;
648 END IF;
649
650 -- return changed record.
651 p_x_item_assoc_rec := l_item_assoc_rec;
652
653
654 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
655 THEN
656 l_dummy := 'Inventory p_x_item_assoc_rec '||to_char(p_x_item_assoc_rec.inventory_item_id);
657 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
658 'ahl_mc_itemgroup_pvt.Convert_InTo_ID', l_dummy);
659 END IF;
660
661
662 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
663 THEN
664 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
665 'ahl_mc_itemgroup_pub.Convert_InTo_ID', 'End of Convert_InTo_ID');
666 END IF;
667
668 END Convert_InTo_ID;
669
670
671 End AHL_MC_ITEMGROUP_PUB;
[Home] [Help]