[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_ITEMGROUP_PVT
Source
1 PACKAGE BODY AHL_MC_ITEMGROUP_PVT AS
2 /* $Header: AHLVIGPB.pls 120.6.12010000.2 2008/11/18 07:06:40 skpathak ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Ahl_MC_ItemGroup_Pvt';
5 G_FND_1WAY_CODE CONSTANT VARCHAR2(30) := '1-WAY';
6
7 -- Added by skpathak for bug-7437855 on 18-NOV-2008 - Define a package scope VARCHAR2 associative array.
8 TYPE G_ITEM_DTL_TYPE IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
9
10 ----------------------------------
11 -- Define Validation Procedures --
12 ----------------------------------
13 PROCEDURE Validate_Item_Group_Name(p_name IN VARCHAR2, p_item_group_id IN NUMBER, p_source_id IN NUMBER) IS
14
15 -- Validation for Create item group.
16 -- TAMAL -- IG Amendments --
17 CURSOR Item_group_csr IS
18 select 'x'
19 from ahl_item_groups_b
20 where name = p_name and
21 nvl(p_item_group_id, -1) <> item_group_id and
22 nvl(p_source_id, -1) <> item_group_id;
23 -- TAMAL -- IG Amendments --
24
25 l_junk VARCHAR2(1);
26
27 BEGIN
28
29 IF (p_name IS NULL OR p_name = FND_API.G_MISS_CHAR) THEN
30 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_NAME_NULL');
31 FND_MSG_PUB.ADD;
32 RETURN;
33 END IF;
34
35 OPEN Item_group_csr;
36 FETCH Item_group_csr INTO l_junk;
37 IF (Item_group_csr%FOUND) THEN
38 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_EXISTS');
39 FND_MESSAGE.Set_Token('ITEM_GRP',p_name);
40 FND_MSG_PUB.ADD;
41 --dbms_output.put_line('Item Group already exists');
42 END IF;
43 CLOSE Item_group_csr;
44
45 End Validate_Item_Group_Name;
46
47 ----------------------------------
48 PROCEDURE Validate_priority
49 (
50 p_item_group_id in Number
51 ) IS
52
53 CURSOR check_priority_dup_exists
54 IS
55 SELECT priority
56 FROM ahl_item_associations_b
57 WHERE item_group_id = p_item_group_id
58 group by priority
59 having count(item_group_id) > 1;
60
61 l_priority NUMBER;
62
63 BEGIN
64
65
66 OPEN check_priority_dup_exists;
67 FETCH check_priority_dup_exists INTO l_priority;
68 IF (check_priority_dup_exists%FOUND)
69 THEN
70 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PRIORITY_NON_UNIQUE');
71 FND_MSG_PUB.ADD;
72 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
73 THEN
74 fnd_log.message
75 (
76 fnd_log.level_exception,
77 'ahl.plsql.'||G_PKG_NAME||'.Validate_priority',
78 true
79 );
80 END IF;
81 CLOSE check_priority_dup_exists;
82 RAISE FND_API.G_EXC_ERROR;
83 END IF;
84
85 END Validate_priority;
86
87 --Priyan IG Mass Update Changes
88 --Bug # 4330922
89 PROCEDURE Validate_IG_revision
90 (
91 p_item_group_id IN NUMBER
92 )
93 IS
94
95 CURSOR check_revision_dup_exists
96 IS
97 SELECT revision
98 FROM ahl_item_associations_b
99 WHERE item_group_id = p_item_group_id
100 group by inventory_item_id,revision
101 having count(INVENTORY_ITEM_ID) > 1;
102
103 l_revision VARCHAR2(2);
104
105 BEGIN
106
107 OPEN check_revision_dup_exists;
108 FETCH check_revision_dup_exists INTO l_revision;
109 IF (check_revision_dup_exists%FOUND)
110 THEN
111 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_REVISION_NON_UNIQUE');
112 FND_MSG_PUB.ADD;
113 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
114 THEN
115 fnd_log.message
116 (
117 fnd_log.level_exception,
118 'ahl.plsql.'||G_PKG_NAME||'.Validate_IG_revision',
119 true
120 );
121 END IF;
122 CLOSE check_revision_dup_exists;
123 RAISE FND_API.G_EXC_ERROR;
124 END IF;
125
126 END Validate_IG_revision;
127 --Priyan IG Mass Update Changes Ends.
128
129 -------------------------------------
130
131 -- SATHAPLI::Bug# 4328454 fix
132 -- This private procedure is called to get the item details for an item association id to be deleted.
133 PROCEDURE get_Item_detail(
134 p_assoc_id IN NUMBER,
135 x_item_group_id OUT NOCOPY NUMBER,
136 x_inventory_item_id OUT NOCOPY NUMBER,
137 x_inventory_org_id OUT NOCOPY NUMBER,
138 x_concatenated_segments OUT NOCOPY VARCHAR2,
139 x_revision OUT NOCOPY VARCHAR2
140 ) IS
141
142 CURSOR c_get_det (p_assoc_id NUMBER) IS
143 SELECT SOURCE.item_group_id item_group_id,
144 SOURCE.inventory_item_id inventory_item_id,
145 SOURCE.inventory_org_id inventory_org_id,
146 SOURCE.revision revision,
147 MTL.concatenated_segments concatenated_segments
148 FROM AHL_ITEM_ASSOCIATIONS_B SOURCE, AHL_ITEM_ASSOCIATIONS_B REVISION,
149 AHL_ITEM_GROUPS_B IGROUP, MTL_SYSTEM_ITEMS_KFV MTL
150 WHERE REVISION.item_association_id = p_assoc_id AND
151 IGROUP.item_group_id = REVISION.item_group_id AND
152 SOURCE.item_group_id = IGROUP.source_item_group_id AND
153 SOURCE.inventory_item_id = REVISION.inventory_item_id AND
154 SOURCE.inventory_org_id = REVISION.inventory_org_id AND
155 MTL.inventory_item_id = SOURCE.inventory_item_id AND
156 MTL.organization_id = SOURCE.inventory_org_id;
157
158 l_get_det c_get_det%ROWTYPE;
159
160 l_api_name CONSTANT VARCHAR2(30) := 'get_Item_detail';
161 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
162
163 BEGIN
164
165 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
166 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'Start of the API...p_assoc_id => '||p_assoc_id);
167 END IF;
168
169 OPEN c_get_det(p_assoc_id);
170 FETCH c_get_det INTO l_get_det;
171
172 IF (c_get_det%FOUND) THEN
173 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
174 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Item details found.');
175 END IF;
176
177 x_item_group_id := l_get_det.item_group_id;
178 x_inventory_item_id := l_get_det.inventory_item_id;
179 x_inventory_org_id := l_get_det.inventory_org_id;
180 x_concatenated_segments := l_get_det.concatenated_segments;
181 x_revision := l_get_det.revision;
182 ELSE
183
184 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
185 FND_LOG.string(FND_LOG.level_statement,l_full_name,'The item is newly added in the revision');
186 END IF;
187
188 NULL;
189 END IF;
190
191 CLOSE c_get_det;
192
193 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
194 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
195 END IF;
196
197 END get_Item_detail;
198
199 -------------------------------------
200
201 -- SATHAPLI::Bug# 4328454 fix
202 -- This private procedure is called to validate updation of interchange_type_code in an IG
203 -- against all the valid Units where the item is installed
204 PROCEDURE validate_IG_update(
205 p_ItemGroup_id IN NUMBER,
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2
209 ) IS
210
211 l_api_name CONSTANT VARCHAR2(30) := 'validate_IG_update';
212 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
213
214 l_invalid_item_instance_tbl AHL_UTIL_UC_PKG.Instance_Tbl_Type2;
215
216 TYPE t_id IS TABLE OF NUMBER
217 INDEX BY BINARY_INTEGER;
218
219 l_item_group_id_tbl t_id;
220 l_inventory_item_id_tbl t_id;
221 l_inventory_org_id_tbl t_id;
222
223 TYPE t_item_det IS TABLE OF VARCHAR2(80)
224 INDEX BY BINARY_INTEGER;
225
226 l_concatenated_segments_tbl t_item_det;
227 l_revision_tbl t_item_det;
228 l_interchange_type_tbl t_item_det;
229
230 BEGIN
231
232 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
233 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'Start of the API');
234 END IF;
235
236 -- SATHAPLI::Bug# 5566764 fix
237 -- Initialize API return status to success
238 x_return_status := FND_API.G_RET_STS_SUCCESS;
239
240 BEGIN
241 -- Retrieve the items that are invalidated in the current revision
242 SELECT SOURCE.item_group_id,
243 SOURCE.inventory_item_id,
244 SOURCE.inventory_org_id,
245 SOURCE.revision,
246 MTL.concatenated_segments,
247 FL.meaning
248 BULK COLLECT
249 INTO l_item_group_id_tbl,
250 l_inventory_item_id_tbl,
251 l_inventory_org_id_tbl,
252 l_revision_tbl,
253 l_concatenated_segments_tbl,
254 l_interchange_type_tbl
255 FROM AHL_ITEM_ASSOCIATIONS_B SOURCE, AHL_ITEM_ASSOCIATIONS_B REVISION,
256 AHL_ITEM_GROUPS_B IGROUP, MTL_SYSTEM_ITEMS_KFV MTL,
257 FND_LOOKUP_VALUES_VL FL
258 WHERE IGROUP.item_group_id = p_ItemGroup_id AND
259 REVISION.item_group_id = IGROUP.item_group_id AND
260 SOURCE.item_group_id = IGROUP.source_item_group_id AND
261 SOURCE.inventory_item_id = REVISION.inventory_item_id AND
262 SOURCE.inventory_org_id = REVISION.inventory_org_id AND
263 MTL.inventory_item_id = SOURCE.inventory_item_id AND
264 MTL.organization_id = SOURCE.inventory_org_id AND
265 FL.lookup_type = 'AHL_INTERCHANGE_ITEM_TYPE' AND
266 FL.lookup_code = REVISION.interchange_type_code AND
267 NVL(REVISION.interchange_type_code, 'DELETED') IN ('REFERENCE', 'DELETED');
268 EXCEPTION
269 WHEN NO_DATA_FOUND THEN
270 RETURN;
271 END;
272
273 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
274 FND_LOG.string(FND_LOG.level_statement,l_full_name,'After the BEGIN-END block.');
275 END IF;
276
277 IF (l_item_group_id_tbl.COUNT > 0) THEN
278 FOR i IN l_item_group_id_tbl.FIRST..l_item_group_id_tbl.LAST
279 LOOP
280 l_invalid_item_instance_tbl(i).item_group_id := l_item_group_id_tbl(i);
281 l_invalid_item_instance_tbl(i).inventory_item_id := l_inventory_item_id_tbl(i);
282 l_invalid_item_instance_tbl(i).inventory_org_id := l_inventory_org_id_tbl(i);
283 l_invalid_item_instance_tbl(i).concatenated_segments := l_concatenated_segments_tbl(i);
284 l_invalid_item_instance_tbl(i).revision := l_revision_tbl(i);
285 l_invalid_item_instance_tbl(i).interchange_type := l_interchange_type_tbl(i);
286 END LOOP;
287
288 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
289 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Validating '||l_item_group_id_tbl.COUNT||
290 ' items in the IG for update.');
291 END IF;
292
293 -- Call UC procedure to check if active Units are getting affected
294 AHL_UTIL_UC_PKG.Check_Invalidate_Instance
295 (
296 p_api_version => 1.0,
297 p_init_msg_list => FND_API.G_FALSE,
298 p_instance_tbl => l_invalid_item_instance_tbl,
299 p_operator => 'U',
300 x_return_status => x_return_status,
301 x_msg_count => x_msg_count,
302 x_msg_data => x_msg_data
303 );
304 END IF;
305
306 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
307 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
308 END IF;
309
310 END validate_IG_update;
311
312 -------------------------------------
313 PROCEDURE Validate_InventoryID(p_inventory_item_id IN NUMBER,
314 p_inventory_org_id IN NUMBER,
315 p_type_code IN VARCHAR2,
316 x_master_org_id OUT NOCOPY NUMBER,
317 x_inv_segment OUT NOCOPY VARCHAR2,
318 x_revision_qty_control_code OUT NOCOPY NUMBER,
319 x_serial_number_control OUT NOCOPY NUMBER) IS
320
321 CURSOR mtl_system_items_csr(p_inventory_item_id IN NUMBER,
322 p_inventory_org_id IN NUMBER) IS
323 SELECT NVL(comms_nl_trackable_flag,'N'), concatenated_segments,
324 SERIAL_NBR_CNTRL_CODE,revision_qty_cntrl_code
325 FROM ahl_mtl_items_non_ou_v
326 WHERE inventory_item_id = p_inventory_item_id
327 AND inventory_org_id = p_inventory_org_id;
328
329 CURSOR mtl_system_items_non_ou_csr(p_inventory_item_id IN NUMBER,
330 p_inventory_org_id IN NUMBER) IS
331 SELECT NVL(comms_nl_trackable_flag,'N'), concatenated_segments,
332 SERIAL_NBR_CNTRL_CODE,revision_qty_cntrl_code
333 FROM ahl_mtl_items_non_ou_v
334 WHERE inventory_item_id = p_inventory_item_id
335 AND inventory_org_id = p_inventory_org_id;
336
337 CURSOR mtl_parameters_csr(p_inventory_org_id IN NUMBER) IS
338 SELECT organization_code,
339 master_organization_id
340 FROM mtl_parameters
341 WHERE organization_id = p_inventory_org_id;
342
343 l_instance_track VARCHAR2(1);
344 l_segment1 ahl_mtl_items_non_ou_v.concatenated_segments%TYPE;
345 l_serial_number_control NUMBER;
346 l_revision_qty_control_code NUMBER;
347 l_organization_code mtl_parameters.organization_code%TYPE;
348
349 BEGIN
350
351 IF (p_inventory_item_id IS NULL)
352 OR (p_inventory_item_id = FND_API.G_MISS_NUM)
353 OR (p_inventory_org_id IS NULL)
354 OR (p_inventory_org_id = FND_API.G_MISS_NUM) THEN
355 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_NULL');
356 --FND_MESSAGE.Set_Token('INV_ITEM',p_inventory_item_id);
357 FND_MSG_PUB.ADD;
358 --dbms_output.put_line('Inventory item is null.');
359 RETURN;
360 END IF;
361
362 -- For organization code
363 OPEN mtl_parameters_csr(p_inventory_org_id);
364 FETCH mtl_parameters_csr INTO l_organization_code,x_master_org_id;
365 IF (mtl_parameters_csr%NOTFOUND) THEN
366 FND_MESSAGE.Set_Name('AHL','AHL_MC_ORG_INVALID');
367 FND_MESSAGE.Set_Token('ORG',p_inventory_org_id);
368 FND_MSG_PUB.ADD;
369 --dbms_output.put_line('Organization does not exist');
370 END IF;
371 CLOSE mtl_parameters_csr;
372
373
374 OPEN mtl_system_items_csr(p_inventory_item_id,p_inventory_org_id);
375 FETCH mtl_system_items_csr INTO l_instance_track, l_segment1, l_serial_number_control,
376 l_revision_qty_control_code;
377 l_segment1 := l_segment1 || ',' || l_organization_code;
378
379 IF (mtl_system_items_csr%NOTFOUND) THEN
380 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_INVALID');
381 FND_MESSAGE.Set_Token('INV_ITEM',p_inventory_item_id);
382 FND_MSG_PUB.ADD;
383 --dbms_output.put_line('Inventory item does not exist');
384
385 l_segment1 := null;
386 l_revision_qty_control_code := null;
387 l_serial_number_control := null;
388
389 ELSE
390
391 /*
392 IF ( UPPER(l_instance_track) <> 'Y') THEN
393 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_TRACK');
394 FND_MESSAGE.Set_Token('INV_ITEM',l_segment1);
395 FND_MSG_PUB.ADD;
396 --dbms_output.put_line('Inventory item not trackable');
397 END IF;
398
399 */
400 IF ( UPPER(p_type_code) = 'TRACKED' AND UPPER(l_instance_track) <> 'Y')
401 THEN
402 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_TRACK');
403 FND_MESSAGE.Set_Token('INV_ITEM',l_segment1);
404 FND_MSG_PUB.ADD;
405 --dbms_output.put_line('Inventory item not trackable');
406 ELSIF ( UPPER(p_type_code) = 'NON-TRACKED' AND UPPER(l_instance_track) = 'Y')
407 THEN
408 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_NON_TRACK');
409 FND_MESSAGE.Set_Token('INV_ITEM',l_segment1);
410 FND_MSG_PUB.ADD;
411 --dbms_output.put_line('Inventory item are trackable');
412 END IF;
413
414 END IF;
415
416 CLOSE mtl_system_items_csr;
417
418 x_inv_segment := l_segment1;
419 x_revision_qty_control_code := l_revision_qty_control_code;
420 x_serial_number_control := l_serial_number_control;
421
422 END Validate_InventoryID;
423
424 ----------------------------------
425
426 PROCEDURE Validate_Interchange_Code(p_interchange_type_code IN VARCHAR2,
427 p_interchange_reason IN VARCHAR2,
428 p_inv_segment IN VARCHAR2) IS
429
430 BEGIN
431 IF (p_interchange_type_code IS NULL
432 OR p_interchange_type_code = FND_API.G_MISS_CHAR) THEN
433 FND_MESSAGE.Set_Name('AHL','AHL_MC_INTER_TYP_NULL');
434 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
435 FND_MSG_PUB.ADD;
436 RETURN;
437 END IF;
438
439 IF NOT(AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_INTERCHANGE_ITEM_TYPE', p_interchange_type_code))
440 OR p_interchange_type_code = 'REMOVED' THEN
441 FND_MESSAGE.Set_Name('AHL','AHL_MC_INTER_INVALID');
442 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
443 FND_MESSAGE.Set_Token('INTER_CODE',p_interchange_type_code);
444 FND_MSG_PUB.ADD;
445 --dbms_output.put_line('Interchange type code is invalid');
446 END IF;
447
448 IF p_interchange_type_code = '1-WAY INTERCHANGEABLE'
449 AND TRIM(p_interchange_reason) IS NULL
450 THEN
451 FND_MESSAGE.Set_Name('AHL','AHL_MC_INTER_REASON_NULL');
452 FND_MESSAGE.Set_Token('INTER_CODE',p_interchange_type_code);
453 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
454 FND_MSG_PUB.ADD;
455 --dbms_output.put_line('Interchange Reason is Null');
456 END IF;
457
458
459 END Validate_Interchange_Code;
460
461 -----------------------------------
462 PROCEDURE Validate_Revision(p_revision IN VARCHAR2,
463 p_inventory_id IN NUMBER,
464 p_organization_id IN NUMBER,
465 p_inv_segment IN VARCHAR2,
466 p_revision_qty_control_code IN NUMBER ) IS
467
468 CURSOR mtl_item_revisions_csr (p_revision IN VARCHAR2,
469 p_inventory_id IN NUMBER,
470 p_organization_id IN NUMBER) IS
471 SELECT 'x'
472 FROM mtl_item_revisions
473 WHERE inventory_item_id = p_inventory_id
474 AND organization_id = p_organization_id
475 AND revision = p_revision;
476
477 l_junk VARCHAR2(1);
478
479 BEGIN
480 IF (p_revision IS NULL) OR (p_revision = FND_API.G_MISS_CHAR) THEN
481 RETURN;
482 END IF;
483
484 IF (nvl(p_revision_qty_control_code,0) <> 2) THEN
485 FND_MESSAGE.Set_Name('AHL','AHL_UC_REV_NOTNULL');
486 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
487 FND_MSG_PUB.ADD;
488 --dbms_output.put_line('Revision is not null. Revision not required.');
489 ELSE
490 OPEN mtl_item_revisions_csr(p_revision,p_inventory_id, p_organization_id);
491 FETCH mtl_item_revisions_csr INTO l_junk;
492 IF (mtl_item_revisions_csr%NOTFOUND) THEN
493 FND_MESSAGE.Set_Name('AHL','AHL_MC_INVREVI_INVALID');
494 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
495 FND_MESSAGE.Set_Token('REVISION',p_revision);
496 FND_MSG_PUB.ADD;
497 --dbms_output.put_line('Revision does not exist');
498 END IF;
499
500 CLOSE mtl_item_revisions_csr;
501
502 END IF;
503
504 END Validate_Revision;
505
506 ----------------------------------
507 PROCEDURE Validate_Dup_Inventory(p_name IN VARCHAR2,
508 p_item_group_id IN NUMBER,
509 p_inventory_item_id IN NUMBER,
510 p_inventory_org_id IN NUMBER,
511 p_item_revision IN VARCHAR2,
512 p_inv_segment IN VARCHAR2,
513 p_operation_flag IN VARCHAR2) IS
514
515 --Priyan Item Group Change - Begins
516 -- This cursor checks if the same Item with a revision(null/notnull)already exists in the DB..
517 --Bug # 4330922
518 CURSOR item_revnotnull_csr(p_item_group_id IN NUMBER,
519 p_inventory_item_id IN NUMBER,
520 p_inventory_org_id IN NUMBER) IS
521 SELECT REVISION
522 FROM AHL_ITEM_ASSOCIATIONS_VL
523 WHERE item_group_id = p_item_group_id
524 AND inventory_org_id = ( Select master_organization_id
525 from mtl_parameters
526 where organization_id = p_inventory_org_id)
527 AND inventory_item_id = p_inventory_item_id;
528
529 -- This cursor checks if a record of the same Item with Null revision as the one passed from the UI, exists..
530 CURSOR item_revnull_csr(p_item_group_id IN NUMBER,
531 p_inventory_item_id IN NUMBER,
532 p_item_revision IN VARCHAR2,
533 p_inventory_org_id IN NUMBER) IS
534 SELECT 'x'
535 FROM AHL_ITEM_ASSOCIATIONS_VL
536 WHERE item_group_id = p_item_group_id
537 --AND (REVISION IS NULL OR REVISION = p_item_revision)
538 AND (REVISION IS NULL)
539 AND inventory_org_id = ( Select master_organization_id
540 from mtl_parameters
541 where organization_id = p_inventory_org_id)
542 AND inventory_item_id = p_inventory_item_id;
543
544
545 --This cursor checks if a record of the same revision passed from the UI exists in the DB or not
546 CURSOR rev_exists_csr(p_item_group_id IN NUMBER,
547 p_inventory_item_id IN NUMBER,
548 p_inventory_org_id IN NUMBER,
549 p_item_revision IN VARCHAR2) IS
550 SELECT 'x'
551 FROM AHL_ITEM_ASSOCIATIONS_VL
552 WHERE item_group_id = p_item_group_id
553 AND (REVISION = p_item_revision)
554 AND inventory_org_id = (Select master_organization_id
555 from mtl_parameters
556 where organization_id = p_inventory_org_id)
557 AND inventory_item_id = p_inventory_item_id;
558
559 l_junk VARCHAR2(30);
560
561 BEGIN
562
563 IF (p_operation_flag = 'C' ) THEN
564 IF (p_item_revision = FND_API.G_MISS_CHAR OR p_item_revision IS NULL) THEN
565 OPEN item_revnotnull_csr(p_item_group_id, p_inventory_item_id, p_inventory_org_id);
566 FETCH item_revnotnull_csr INTO l_junk;
567 IF (item_revnotnull_csr%FOUND) THEN
568 if l_junk is not null then
569 FND_MESSAGE.Set_Name('AHL','AHL_MC_NOTNULL_REV_EXISTS');
570 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
571 FND_MESSAGE.Set_Token('ORG',p_inventory_org_id);
572 FND_MESSAGE.Set_Token('GRP_NAME',p_name);
573 FND_MSG_PUB.ADD;
574 ELSE
575 FND_MESSAGE.Set_Name('AHL','AHL_MC_NULL_REV_EXISTS');
576 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
577 FND_MESSAGE.Set_Token('ORG',p_inventory_org_id);
578 FND_MESSAGE.Set_Token('GRP_NAME',p_name);
579 FND_MSG_PUB.ADD;
580 END IF;
581 END IF;
582 CLOSE item_revnotnull_csr;
583 ELSE
584 OPEN item_revnull_csr(p_item_group_id, p_inventory_item_id,p_item_revision,p_inventory_org_id);
585 FETCH item_revnull_csr INTO l_junk;
586 IF (item_revnull_csr%FOUND) THEN
587 FND_MESSAGE.Set_Name('AHL','AHL_MC_NULL_REV_EXISTS');
588 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
589 FND_MESSAGE.Set_Token('ORG',p_inventory_org_id);
590 FND_MESSAGE.Set_Token('GRP_NAME',p_name);
591 FND_MSG_PUB.ADD;
592 CLOSE item_revnull_csr;
593 ELSE
594 OPEN rev_exists_csr(p_item_group_id,p_inventory_item_id,p_inventory_org_id,p_item_revision);
595 FETCH rev_exists_csr INTO l_junk;
596 IF (rev_exists_csr%FOUND) THEN
597 FND_MESSAGE.Set_Name('AHL','AHL_MC_SAME_REV_EXISTS');
598 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
599 FND_MESSAGE.Set_Token('ORG',p_inventory_org_id);
600 FND_MESSAGE.Set_Token('GRP_NAME',p_name);
601 FND_MSG_PUB.ADD;
602 END IF;
603 CLOSE rev_exists_csr;
604 END IF;
605 END IF;
606 ELSIF (p_operation_flag = 'M' ) THEN
607 IF (p_item_revision = FND_API.G_MISS_CHAR OR p_item_revision IS NULL) THEN
608 -- Check if any record exists, in the DB .
609 -- If there are more than one row then the user cannot change the revison to Null as other not nul revision exists.
610 -- If there is only one record, then no validation is required.
611 -- If there are no records , then do nothing.
612 -- If others,then raise unexpected error.
613 BEGIN
614 SELECT 'x'
615 INTO
616 l_junk
617 FROM
618 AHL_ITEM_ASSOCIATIONS_VL
619 WHERE
620 item_group_id = p_item_group_id
621 AND inventory_org_id = (Select master_organization_id
622 from mtl_parameters
623 where organization_id = p_inventory_org_id)
624 AND inventory_item_id = p_inventory_item_id;
625
626 EXCEPTION
627 WHEN TOO_MANY_ROWS THEN
628 FND_MESSAGE.Set_Name('AHL','AHL_MC_NOTNULL_REV_EXISTS');
629 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
630 FND_MESSAGE.Set_Token('ORG',p_inventory_org_id);
631 FND_MESSAGE.Set_Token('GRP_NAME',p_name);
632 FND_MSG_PUB.ADD;
633 WHEN NO_DATA_FOUND THEN
634 NULL;
635 WHEN OTHERS THEN
636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
637 END;
638
639 /*ELSE --commented out for mass update changes --Priyan
640
641 -- While updating check is made to see if the (itemgroup,item,org and rev) combination already exists in the DB,
642 -- if so the user is not allowed to update the revision. Else the revision is updated
643 OPEN rev_exists_csr(p_item_group_id,
644 p_inventory_item_id,
645 p_inventory_org_id,
646 p_item_revision);
647
648 FETCH rev_exists_csr INTO l_junk;
649 IF (rev_exists_csr%FOUND) THEN
650 FND_MESSAGE.Set_Name('AHL','AHL_MC_NULL_OR_SAME_REV_EXISTS');
651 FND_MESSAGE.Set_Token('GRP_NAME',p_name);
652 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
653 FND_MSG_PUB.ADD;
654 END IF;
655 CLOSE rev_exists_csr;*/
656 END IF;
657 END IF; --checking of operation flag
658
659 END Validate_Dup_Inventory;
660
661 --Priyan Item Group Change- Ends
662
663 /* -- Bug Number 4069855
664 -- Cursor changed on 21st Dec 2004 , to allow for Items with different revisions to be added to the same Group
665 -- 000 is used to denote that user has not entered a revision
666 CURSOR item_assoc_csr(p_item_group_id IN NUMBER,
667 p_inventory_item_id IN NUMBER,
668 p_item_revision IN VARCHAR2,
669 p_inventory_org_id IN NUMBER) IS
670 SELECT 'x'
671 FROM AHL_ITEM_ASSOCIATIONS_VL
672 WHERE item_group_id = p_item_group_id
673 AND NVL(REVISION,'000') = p_item_revision
674 AND inventory_org_id = ( Select master_organization_id
675 from mtl_parameters
676 where organization_id = p_inventory_org_id)
677 AND inventory_item_id = p_inventory_item_id;
678 BEGIN
679 -- Bug Number 4069855
680 -- Changed on 21st Dec 2004, to allow for Items with different revisions to be added to the same Item Group
681 -- Now we have different Error mesages, depending on the combination which is Duplicate
682 IF (p_item_revision = FND_API.G_MISS_CHAR OR p_item_revision IS NULL) THEN
683 OPEN item_assoc_csr(p_item_group_id, p_inventory_item_id,'000', p_inventory_org_id);
684 FETCH item_assoc_csr INTO l_junk;
685 IF (item_assoc_csr%FOUND) THEN
686 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_ASSOC_DUP');
687 FND_MESSAGE.Set_Token('GRP_NAME',p_name);
688 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
689 FND_MSG_PUB.ADD;
690 END IF;
691 CLOSE item_assoc_csr;
692 ELSE
693 OPEN item_assoc_csr(p_item_group_id, p_inventory_item_id,p_item_revision,p_inventory_org_id);
694 FETCH item_assoc_csr INTO l_junk;
695 IF (item_assoc_csr%FOUND) THEN
696 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_ASSOC_REV_DUP');
697 FND_MESSAGE.Set_Token('GRP_NAME',p_name);
698 FND_MESSAGE.Set_Token('INV_ITEM',p_item_revision || ',' || p_inv_segment );
699 FND_MSG_PUB.ADD;
700 END IF;
701 CLOSE item_assoc_csr;
702 END IF;
703 END Validate_Dup_Inventory;*/
704
705 ----------------------------------
706 PROCEDURE Validate_Qty_UOM(p_uom_code IN VARCHAR2,
707 p_quantity IN NUMBER,
708 p_inventory_item_id IN NUMBER,
709 p_inventory_org_id IN NUMBER,
710 p_inv_segment IN VARCHAR2) IS
711
712
713 BEGIN
714
715 IF (p_uom_code IS NULL AND (p_quantity IS NULL OR p_quantity = 0)) THEN
716 RETURN;
717 END IF;
718
719
720 -- Check if UOM entered and valid.
721 IF (p_uom_code IS NULL OR p_uom_code = FND_API.G_MISS_CHAR) THEN
722 -- uom_code is null but quantity is not null.
723 FND_MESSAGE.Set_Name('AHL','AHL_MC_INVUOM_NULL');
724 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
725 FND_MSG_PUB.ADD;
726 --dbms_output.put_line('Uom is null');
727 ELSIF NOT(inv_convert.Validate_Item_Uom(p_item_id => p_inventory_item_id,
728 p_organization_id => p_inventory_org_id,
729 p_uom_code => p_uom_code))
730 THEN
731 FND_MESSAGE.Set_Name('AHL','AHL_MC_INVUOM_INVALID');
732 FND_MESSAGE.Set_Token('UOM_CODE',p_uom_code);
733 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
734 FND_MSG_PUB.ADD;
735 --dbms_output.put_line('Invalid UOM code for the item');
736 END IF;
737
738 -- Validate quantity.
739 IF (p_quantity IS NULL OR p_quantity = FND_API.G_MISS_NUM) THEN
740 FND_MESSAGE.Set_Name('AHL','AHL_MC_INVQTY_INVALID');
741 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
742 FND_MESSAGE.Set_Token('QUANTITY',p_quantity);
743 FND_MSG_PUB.ADD;
744 --dbms_output.put_line('Quantity is null');
745 ELSIF (p_quantity < 0) THEN
746 FND_MESSAGE.Set_Name('AHL','AHL_MC_INVQTY_INVALID');
747 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
748 FND_MESSAGE.Set_Token('QUANTITY',p_quantity);
749 FND_MSG_PUB.ADD;
750 --dbms_output.put_line('Invalid quantity');
751 END IF;
752
753 END Validate_Qty_UOM;
754
755 ----------------------------------
756 -- Added for ER# 2631303.
757 -- This procedure will validate if any part assignments exist in
758 -- Unit Configurations. This procedure will be called only when a part
759 -- is being deleted from an item group.
760 PROCEDURE Validate_UCItem_Assignment(p_item_group_id IN NUMBER,
761 p_inventory_item_id IN NUMBER,
762 p_inventory_org_id IN NUMBER,
763 p_inv_segment IN VARCHAR2) IS
764 /*
765 -- Get all the positions associated to this item group.
766 CURSOR get_associated_posns_csr(p_item_group_id IN NUMBER) IS
767 SELECT posn.relationship_id
768 FROM ahl_relationships_b posn, ahl_item_associations_b iassoc,
769 ahl_relationships_b topnode
770 WHERE trunc(nvl(posn.active_end_date,sysdate+1)) > trunc(sysdate)
771 AND iassoc.item_group_id = posn.item_group_id
772 AND iassoc.item_group_id = p_item_group_id
773 AND topnode.relationship_id = (SELECT reln.relationship_id
774 FROM ahl_relationships_b reln
775 WHERE parent_relationship_id is null
776 START WITH relationship_id = posn.relationship_id
777 AND trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate)
778 CONNECT BY PRIOR parent_relationship_id = relationship_id
779 AND trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate)
780 );
781 */
782 -- Get all the positions associated to this item group.
783 CURSOR get_associated_posns_csr(p_item_group_id IN NUMBER) IS
784 SELECT relationship_id
785 FROM ahl_mc_relationships
786 WHERE trunc(nvl(active_end_date, sysdate + 1)) > trunc(sysdate)
787 AND item_group_id = p_item_group_id;
788
789 -- Get item instances that match the position and inventory_item_id.
790 CURSOR get_item_instances_csr(p_position_reference IN VARCHAR2,
791 p_inventory_item_id IN NUMBER,
792 p_inventory_org_id IN NUMBER) IS
793 SELECT instance_id csi_item_instance_id
794 FROM csi_ii_relationships reln, csi_item_instances csi
795 WHERE reln.subject_id = csi.instance_id
796 AND TRUNC(SYSDATE) < TRUNC(NVL(reln.active_end_date, SYSDATE+1))
797 AND reln.relationship_type_code = 'COMPONENT-OF'
798 AND reln.position_reference = p_position_reference
799 AND csi.inventory_item_id = p_inventory_item_id
800 AND csi.last_vld_organization_id = p_inventory_org_id;
801
802 -- Check top nodes of a unit that match.
803 CURSOR chk_top_node_csr(p_relationship_id IN NUMBER,
804 p_inventory_item_id IN NUMBER,
805 p_inventory_org_id IN NUMBER) IS
806 SELECT 'x'
807 FROM DUAL
808 WHERE EXISTS (SELECT name
809 FROM ahl_unit_config_headers unit, csi_item_instances csi
810 WHERE unit.csi_item_instance_id = csi.instance_id
811 AND master_config_id = p_relationship_id
812 AND TRUNC(SYSDATE) < TRUNC(NVL(unit.active_end_date, SYSDATE+1))
813 AND csi.inventory_item_id = p_inventory_item_id
814 AND csi.last_vld_organization_id = p_inventory_org_id
815 );
816
817 l_unitname ahl_unit_config_headers.name%TYPE;
818 l_unit_found BOOLEAN;
819 l_junk VARCHAR2(1);
820 BEGIN
821 -- initialize.
822 l_unit_found := FALSE;
823
824 -- for each position that is associated to the item group, get all the item instances
825 -- which match the position and inventory_item_id.
826 FOR position_rec IN get_associated_posns_csr(p_item_group_id) LOOP
827 -- Check if item assigned as top node.
828 OPEN chk_top_node_csr(position_rec.relationship_id,
829 p_inventory_item_id,
830 p_inventory_org_id);
831 FETCH chk_top_node_csr INTO l_junk;
832 IF (chk_top_node_csr%FOUND) THEN
833 FND_MESSAGE.Set_Name('AHL','AHL_MC_PARTASSOC_EXISTS');
834 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
835 FND_MSG_PUB.ADD;
836 l_unit_found := TRUE;
837 END IF;
838 CLOSE chk_top_node_csr;
839
840 --Check if item assigned as a component.
841 IF NOT(l_unit_found) THEN
842 FOR item_instance_rec IN get_item_instances_csr(position_rec.relationship_id,
843 p_inventory_item_id,
844 p_inventory_org_id)
845 LOOP
846 l_unitname := AHL_UMP_UTIL_PKG.Get_UnitName(item_instance_rec.csi_item_instance_id);
847 IF (l_unitname IS NOT NULL) THEN
848 FND_MESSAGE.Set_Name('AHL','AHL_MC_PARTASSOC_EXISTS');
849 FND_MESSAGE.Set_Token('INV_ITEM',p_inv_segment);
850 FND_MSG_PUB.ADD;
851 l_unit_found := TRUE;
852 EXIT; -- exit instance loop.
853 END IF;
854 END LOOP;
855 END IF;
856
857 -- If unit found then exit.
858 IF (l_unit_found) THEN
859 EXIT; -- exit position loop.
860 END IF;
861
862 END LOOP;
863
864
865 END Validate_UCItem_Assignment;
866
867 ----------------------------------
868 PROCEDURE Validate_Item_Assoc(p_item_group_id IN NUMBER := NULL,
869 p_name IN VARCHAR2,
870 p_type_code IN VARCHAR2,
871 p_item_assoc_rec IN OUT NOCOPY AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type,
872 -- Changes by skpathak for bug-7437855 on 18-NOV-2008
873 -- Duplicate item association check is now done using G_ITEM_DTL_TYPE, instead of a string.
874 p_x_inventory_list IN OUT NOCOPY G_ITEM_DTL_TYPE,
875 x_row_id OUT NOCOPY UROWID) IS
876
877
878 --ROW_ID column points to AHL_ITEM_ASSOCIATIONS_B table.
879 CURSOR Item_assoc_csr(p_item_assoc_id IN NUMBER) IS
880 SELECT
881 row_id,
882 item_association_id,
883 item_group_id,
884 inventory_item_id,
885 inventory_org_id,
886 uom_code,
887 quantity,
888 concatenated_segments,
889 revision_qty_cntrl_code,
890 serial_nbr_cntrl_code ,
891 organization_code,
892 REVISION
893 FROM AHL_ITEM_ASSOCIATIONS_V
894 WHERE item_association_id = p_item_assoc_id;
895
896 l_item_assoc_rec Item_assoc_csr%ROWTYPE;
897 l_inv_segment ahl_mtl_items_non_ou_v.concatenated_segments%TYPE;
898 l_x_item_assoc_rec AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type DEFAULT p_item_assoc_rec;
899
900 l_revision_qty_control_code NUMBER;
901 l_serial_number_control NUMBER;
902
903 l_inventory_item_id NUMBER;
904
905 -- Bug Number 4069855
906 -- Added on 21st Dec 2004 , to allow for Items with different revisions to be added to the same Group
907 l_inventory_item_revision VARCHAR2(3) := '000';
908
909 l_inventory_org_id NUMBER;
910 l_quantity ahl_item_associations_b.quantity%TYPE;
911 l_uom_code ahl_item_associations_b.uom_code%TYPE;
912 l_master_org_id NUMBER;
913 l_item_key VARCHAR2(200);
914
915 BEGIN
916
917 IF (l_x_item_assoc_rec.operation_flag <> 'C') THEN
918 -- Check if record exists in ahl_item_associations_b.
919 OPEN Item_assoc_csr(l_x_item_assoc_rec.item_association_id);
920 FETCH Item_assoc_csr INTO l_item_assoc_rec;
921 IF (Item_assoc_csr%NOTFOUND) THEN
922 CLOSE Item_assoc_csr;
923 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_ASSOS_NOT_EXISTS');
924 FND_MESSAGE.Set_Token('INV_ITEM',l_item_assoc_rec.concatenated_segments);
925 FND_MSG_PUB.ADD;
926 --dbms_output.put_line('Item Assoc does not exist');
927 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
928 END IF;
929
930 -- Set variables.
931 x_row_id := l_item_assoc_rec.row_id;
932 l_inv_segment := l_item_assoc_rec.concatenated_segments || ',' || l_item_assoc_rec.organization_code;
933 l_revision_qty_control_code := l_item_assoc_rec.revision_qty_cntrl_code;
934 l_serial_number_control := l_item_assoc_rec.serial_nbr_cntrl_code;
935
936 -- Check if primary key changed.
937 IF ((l_x_item_assoc_rec.item_group_id IS NOT NULL) AND
938 (l_x_item_assoc_rec.item_group_id <> l_item_assoc_rec.item_group_id))
939 OR ((l_x_item_assoc_rec.inventory_item_id IS NOT NULL) AND
940 (l_x_item_assoc_rec.inventory_item_id <> l_item_assoc_rec.inventory_item_id))
941 OR ((l_x_item_assoc_rec.inventory_org_id IS NOT NULL) AND
942 (l_x_item_assoc_rec.inventory_org_id <> l_item_assoc_rec.inventory_org_id))
943 THEN
944 CLOSE Item_assoc_csr;
945 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
946 THEN
947 l_item_key := 'Inventory Item Id '||to_char(l_x_item_assoc_rec.inventory_item_id)||' - '||to_Char(l_item_assoc_rec.inventory_item_id);
948 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
949 'ahl_mc_itemgroup_pvt.Validate_Item_Assoc', l_item_key);
950 l_item_key := 'Inventory Org Id '||to_Char(l_x_item_assoc_rec.inventory_org_id)||' - '||to_char(l_item_assoc_rec.inventory_org_id);
951 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
952 'ahl_mc_itemgroup_pvt.Validate_Item_Assoc', l_item_key);
953 END IF;
954
955 FND_MESSAGE.Set_Name('AHL','AHL_COM_KEY_NOUPDATE');
956 FND_MSG_PUB.ADD;
957 --dbms_output.put_line('Primary key cannot be updated');
958 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
959 END IF;
960
961 -- plug primary key values.
962 l_x_item_assoc_rec.inventory_item_id := l_item_assoc_rec.inventory_item_id;
963 l_x_item_assoc_rec.inventory_org_id := l_item_assoc_rec.inventory_org_id;
964 l_x_item_assoc_rec.item_group_id := l_item_assoc_rec.item_group_id;
965
966
967 END IF; /* operation_flag */
968
969 -- Validate Inventory.
970 IF (l_x_item_assoc_rec.operation_flag = 'C') THEN
971 Validate_InventoryID(l_x_item_assoc_rec.inventory_item_id,
972 l_x_item_assoc_rec.inventory_org_id, p_type_code,l_master_org_id,l_inv_segment,
973 l_revision_qty_control_code, l_serial_number_control);
974
975 -- Assigning the Master Org ID from the Validate procedure .
976 -- As per 115.10 the Master Org Id should be stored in the Table.
977 p_item_assoc_rec.INVENTORY_ORG_ID := l_master_org_id;
978
979 -- This is performed only if item assoc records are being created for a
980 -- existing item_group_id (i.e this is called from Modify_Item_Group.)
981 IF (p_item_group_id IS NOT NULL ) THEN
982 -- Priyan Bug Number 4069855
983 -- Changed on 21st Dec 2004 , to allow for Items with different revisions to be added to the same Group
984 Validate_Dup_Inventory(p_name,
985 p_item_group_id,
986 l_x_item_assoc_rec.inventory_item_id,
987 l_x_item_assoc_rec.inventory_org_id,
988 l_x_item_assoc_rec.revision,
989 l_inv_segment,
990 l_x_item_assoc_rec.operation_flag
991 );
992 END IF;
993
994 -- Check for duplicate inventory items in the 'create' list.
995 l_inventory_item_id := l_x_item_assoc_rec.inventory_item_id;
996 l_inventory_org_id := l_x_item_assoc_rec.inventory_org_id;
997
998 -- Bug Number 4069855
999 -- Changed on 21st Dec 2004 , to allow for Items with different revisions to be added to the same Group
1000 -- 000 is used to denote that user has not entered a revision
1001 IF (l_x_item_assoc_rec.revision = FND_API.G_MISS_CHAR OR l_x_item_assoc_rec.revision IS NULL) THEN
1002 l_inventory_item_revision := '000';
1003 ELSE
1004 l_inventory_item_revision := l_x_item_assoc_rec.revision;
1005 END IF;
1006
1007 -- Bug Number 4069855
1008 -- Changed on 21st Dec 2004 , to allow for Items with different revisions to be added to the same Group
1009 -- SATHAPLI::Bug# 4330922 fix
1010 -- Changed the way p_x_inventory_list was being created and
1011 -- checked for duplicate item-org-rev combinations...the
1012 -- delimiter ':' will be put in the end now
1013
1014 -- Changes by skpathak for bug-7437855 on 18-NOV-2008 start
1015 -- Duplicate item association check is now done using G_ITEM_DTL_TYPE, instead of a string.
1016 -- IF (p_x_inventory_list IS NOT NULL) THEN
1017 l_item_key := TO_CHAR(l_inventory_item_id) || '-' || TO_CHAR(l_inventory_org_id) || '-' || l_inventory_item_revision;
1018
1019 -- check if inventory id exists.
1020 --Priyan : Another condition added in the IF to see if the revision is null
1021 /*IF (INSTR(p_x_inventory_list,l_inventory_item_id || '-' || l_inventory_org_id||'-'||l_inventory_item_revision||':') > 0
1022 or (INSTR(p_x_inventory_list,l_inventory_item_id || '-' || l_inventory_org_id||'-'||'000:') >0) ) THEN*/
1023
1024 IF (p_x_inventory_list.EXISTS(l_item_key)) THEN
1025 -- if two lines without Revision are the same, show the old Error Message
1026 IF (l_inventory_item_revision = '000') THEN
1027 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_ASSOC_DUP');
1028 FND_MESSAGE.Set_Token('GRP_NAME',p_name);
1029 FND_MESSAGE.Set_Token('INV_ITEM',l_inv_segment);
1030 FND_MSG_PUB.ADD;
1031 --dbms_output.put_line('Item already exists in the list');
1032 ELSE
1033 -- if two lines have the same Revision, show new error message with Revision
1034 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_ASSOC_REV_DUP');
1035 FND_MESSAGE.Set_Token('GRP_NAME',p_name);
1036 FND_MESSAGE.Set_Token('INV_ITEM',l_inventory_item_revision || ',' || l_inv_segment);
1037 FND_MSG_PUB.ADD;
1038 END IF;
1039 ELSE
1040 -- match not found, so add to the associative array p_x_inventory_list.
1041 -- p_x_inventory_list := p_x_inventory_list || l_inventory_item_id || '-' || l_inventory_org_id || '-' || l_inventory_item_revision || ':' ;
1042 p_x_inventory_list(l_item_key) := 1;
1043 END IF;
1044
1045 /*ELSE
1046 p_x_inventory_list := p_x_inventory_list || to_char(l_inventory_item_id) || '-' || to_char(l_inventory_org_id) || '-' || l_inventory_item_revision || ':' ;
1047 END IF;*/
1048 -- Changes by skpathak for bug-7437855 on 18-NOV-2008 end
1049
1050 END IF; /* for operation flag = 'C' */
1051
1052 --Priyan (revision check change for update for Item Group)
1053 --Bug # 4330922
1054 if (( l_x_item_assoc_rec.operation_flag = 'M') and
1055 nvl(l_x_item_assoc_rec.revision,FND_API.G_MISS_CHAR) <>
1056 nvl(l_item_assoc_rec.revision,FND_API.G_MISS_CHAR)) THEN
1057
1058 Validate_Dup_Inventory(p_name,
1059 p_item_group_id,
1060 l_x_item_assoc_rec.inventory_item_id,
1061 l_x_item_assoc_rec.inventory_org_id,
1062 l_x_item_assoc_rec.revision,
1063 l_inv_segment,
1064 l_x_item_assoc_rec.operation_flag
1065 );
1066 END IF;
1067 -- End of Changes -Priyan
1068
1069 -- Validate lookup codes and revision if present.
1070 IF (l_x_item_assoc_rec.operation_flag <> 'D') THEN
1071 Validate_Interchange_Code(l_x_item_assoc_rec.interchange_type_code,
1072 l_x_item_assoc_rec.interchange_reason,
1073 l_inv_segment);
1074
1075 Validate_Revision(l_x_item_assoc_rec.revision,
1076 l_x_item_assoc_rec.inventory_item_id,
1077 l_x_item_assoc_rec.inventory_org_id, l_inv_segment,
1078 l_revision_qty_control_code);
1079
1080 --Interchange Reason can not be null for 1-way interchanges
1081 IF (l_x_item_assoc_rec.INTERCHANGE_TYPE_CODE IS NOT NULL AND
1082 l_x_item_assoc_rec.INTERCHANGE_TYPE_CODE = G_FND_1WAY_CODE AND
1083 l_x_item_assoc_rec.INTERCHANGE_REASON IS NULL) THEN
1084 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_1WAY_MISSING_REASON');
1085 FND_MESSAGE.Set_Token('INV_ITEM',l_inv_segment);
1086 FND_MSG_PUB.ADD;
1087 END IF;
1088 END IF;
1089
1090
1091 -- Validate priority only for 'Create' and if changed during modify.
1092 IF (l_x_item_assoc_rec.priority <> FND_API.G_MISS_NUM AND l_x_item_assoc_rec.priority IS NOT NULL)
1093 THEN
1094 IF (l_x_item_assoc_rec.priority <= 0) THEN
1095 FND_MESSAGE.Set_Name('AHL','AHL_MC_PRIORITY_INVALID');
1096 FND_MESSAGE.Set_Token('INV_ITEM',l_inv_segment);
1097 FND_MESSAGE.Set_Token('PRIORITY',l_x_item_assoc_rec.priority);
1098 FND_MSG_PUB.ADD;
1099 --dbms_output.put_line('Invalid priority');
1100 END IF;
1101 ELSIF l_x_item_assoc_rec.operation_flag = 'C' OR
1102 (l_x_item_assoc_rec.operation_flag = 'M' AND l_x_item_assoc_rec.priority = FND_API.G_MISS_NUM) THEN
1103 FND_MESSAGE.Set_Name('AHL','AHL_MC_PRIORITY_NULL');
1104 FND_MESSAGE.Set_Token('INV_ITEM',l_inv_segment);
1105 FND_MSG_PUB.ADD;
1106 --dbms_output.put_line('Null priority');
1107 END IF;
1108
1109 -- set quantity and uom values into local variables for validation.
1110 IF (l_x_item_assoc_rec.operation_flag = 'C') THEN
1111 l_quantity := l_x_item_assoc_rec.quantity;
1112 l_uom_code := l_x_item_assoc_rec.uom_code;
1113 ELSIF (l_x_item_assoc_rec.operation_flag = 'M') THEN
1114
1115 -- For quantity
1116 IF (l_x_item_assoc_rec.quantity = FND_API.G_MISS_NUM) THEN
1117 l_quantity := null;
1118 ELSIF (l_x_item_assoc_rec.quantity = null ) THEN
1119 l_quantity := l_item_assoc_rec.quantity;
1120 ELSE
1121 l_quantity := l_x_item_assoc_rec.quantity;
1122 END IF;
1123
1124 -- For uom code.
1125 IF (l_x_item_assoc_rec.uom_code = FND_API.G_MISS_CHAR) THEN
1126 l_uom_code := null;
1127 ELSIF (l_x_item_assoc_rec.uom_code = null) THEN
1128 l_uom_code := l_item_assoc_rec.uom_code;
1129 ELSE
1130 l_uom_code := l_x_item_assoc_rec.uom_code;
1131 END IF;
1132
1133 END IF;
1134
1135 -- Validate quantity and UOM.
1136
1137
1138 IF (l_x_item_assoc_rec.operation_flag = 'C' OR
1139 l_x_item_assoc_rec.operation_flag = 'M' )
1140 THEN
1141 Validate_Qty_UOM(p_uom_code => l_uom_code,
1142 p_quantity => l_quantity,
1143 p_inventory_item_id => l_x_item_assoc_rec.inventory_item_id,
1144 p_inventory_org_id => l_x_item_assoc_rec.inventory_org_id,
1145 p_inv_segment => l_inv_segment);
1146
1147 END IF;
1148
1149 -- For serialized items quantity must be 1; if quantity not present then raise error.
1150 IF (l_x_item_assoc_rec.operation_flag = 'C' OR
1151 l_x_item_assoc_rec.operation_flag = 'M' )
1152 THEN
1153 IF (nvl(l_serial_number_control,0) IN (2,5,6)) THEN
1154 IF (l_quantity IS NULL OR
1155 l_quantity = 0 OR
1156 l_quantity = FND_API.G_MISS_NUM) THEN
1157 FND_MESSAGE.Set_Name('AHL','AHL_MC_SRLQTY_NULL');
1158 FND_MESSAGE.Set_Token('INV_ITEM',l_inv_segment);
1159 FND_MSG_PUB.ADD;
1160 --dbms_output.put_line('Invalid UOM code for the item');
1161 ELSE
1162 IF (l_quantity <> 1) THEN
1163 FND_MESSAGE.Set_Name('AHL','AHL_UC_SRLQTY_MISMATCH');
1164 FND_MESSAGE.Set_Token('QTY',l_quantity);
1165 FND_MESSAGE.Set_Token('INV_ITEM',l_inv_segment);
1166 FND_MSG_PUB.ADD;
1167 --dbms_output.put_line('For serialized items Quantity must be 1');
1168 END IF;
1169 END IF;
1170 END IF;
1171 END IF; /* operation */
1172
1173 -- Added for ER# 2631303.
1174 -- If item association is being deleted, then verify that this part is not installed in
1175 -- any of the unit configurations that are using this item group through their MC's.
1176 IF (l_x_item_assoc_rec.operation_flag = 'D') THEN
1177 Validate_UCItem_Assignment(l_x_item_assoc_rec.item_group_id,
1178 l_x_item_assoc_rec.inventory_item_id,
1179 l_x_item_assoc_rec.inventory_org_id,
1180 l_inv_segment);
1181 END IF;
1182
1183 IF (Item_assoc_csr%ISOPEN) THEN
1184 CLOSE Item_assoc_csr;
1185 END IF;
1186
1187 End Validate_Item_Assoc;
1188
1189 -------------------------------------
1190 -- Insert/Update/Delete procedures --
1191 -------------------------------------
1192 PROCEDURE Insert_Item_Group(p_x_item_grp_rec IN OUT NOCOPY AHL_MC_ITEMGROUP_PVT.Item_Group_Rec_Type) IS
1193
1194 l_item_grp_id NUMBER;
1195 l_last_update_login NUMBER;
1196 l_last_updated_by NUMBER;
1197 l_row_id VARCHAR2(30);
1198
1199 BEGIN
1200
1201 -- Set default values.
1202 IF p_x_item_grp_rec.DESCRIPTION = FND_API.G_MISS_CHAR THEN
1203 p_x_item_grp_rec.DESCRIPTION := null;
1204 END IF;
1205 IF p_x_item_grp_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR THEN
1206 p_x_item_grp_rec.ATTRIBUTE_CATEGORY := null;
1207 END IF;
1208 IF p_x_item_grp_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR THEN
1209 p_x_item_grp_rec.ATTRIBUTE1 := null;
1210 END IF;
1211 IF p_x_item_grp_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR THEN
1212 p_x_item_grp_rec.ATTRIBUTE2 := null;
1213 END IF;
1214 IF p_x_item_grp_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR THEN
1215 p_x_item_grp_rec.ATTRIBUTE3 := null;
1216 END IF;
1217 IF p_x_item_grp_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR THEN
1218 p_x_item_grp_rec.ATTRIBUTE4 := null;
1219 END IF;
1220 IF p_x_item_grp_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR THEN
1221 p_x_item_grp_rec.ATTRIBUTE5 := null;
1222 END IF;
1223 IF p_x_item_grp_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR THEN
1224 p_x_item_grp_rec.ATTRIBUTE6 := null;
1225 END IF;
1226 IF p_x_item_grp_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR THEN
1227 p_x_item_grp_rec.ATTRIBUTE7 := null;
1228 END IF;
1229 IF p_x_item_grp_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR THEN
1230 p_x_item_grp_rec.ATTRIBUTE8 := null;
1231 END IF;
1232 IF p_x_item_grp_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR THEN
1233 p_x_item_grp_rec.ATTRIBUTE9 := null;
1234 END IF;
1235 IF p_x_item_grp_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR THEN
1236 p_x_item_grp_rec.ATTRIBUTE10 := null;
1237 END IF;
1238 IF p_x_item_grp_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR THEN
1239 p_x_item_grp_rec.ATTRIBUTE11 := null;
1240 END IF;
1241 IF p_x_item_grp_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR THEN
1242 p_x_item_grp_rec.ATTRIBUTE12 := null;
1243 END IF;
1244 IF p_x_item_grp_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR THEN
1245 p_x_item_grp_rec.ATTRIBUTE13 := null;
1246 END IF;
1247 IF p_x_item_grp_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR THEN
1248 p_x_item_grp_rec.ATTRIBUTE14 := null;
1249 END IF;
1250 IF p_x_item_grp_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR THEN
1251 p_x_item_grp_rec.ATTRIBUTE15 := null;
1252 END IF;
1253
1254
1255 --Gets the sequence Number
1256 SELECT AHL_ITEM_GROUPS_B_S.nextval INTO
1257 l_item_grp_id from DUAL;
1258
1259 l_last_updated_by := to_number(fnd_global.USER_ID);
1260 l_last_update_login := to_number(fnd_global.LOGIN_ID);
1261
1262 AHL_ITEM_GROUPS_PKG.INSERT_ROW(
1263 X_ROWID => l_row_id,
1264 X_ITEM_GROUP_ID => l_item_grp_id,
1265 X_TYPE_CODE => p_x_item_grp_rec.type_code,
1266 X_STATUS_CODE => 'DRAFT',
1267 X_SOURCE_ITEM_GROUP_ID => NULL,
1268 X_OBJECT_VERSION_NUMBER => 1,
1269 X_NAME => p_x_item_grp_rec.name,
1270 X_ATTRIBUTE_CATEGORY => p_x_item_grp_rec.ATTRIBUTE_CATEGORY,
1271 X_ATTRIBUTE1 => p_x_item_grp_rec.attribute1,
1272 X_ATTRIBUTE2 => p_x_item_grp_rec.attribute2,
1273 X_ATTRIBUTE3 => p_x_item_grp_rec.attribute3,
1274 X_ATTRIBUTE4 => p_x_item_grp_rec.attribute4,
1275 X_ATTRIBUTE5 => p_x_item_grp_rec.attribute5,
1276 X_ATTRIBUTE6 => p_x_item_grp_rec.attribute6,
1277 X_ATTRIBUTE7 => p_x_item_grp_rec.attribute7,
1278 X_ATTRIBUTE8 => p_x_item_grp_rec.attribute8,
1279 X_ATTRIBUTE9 => p_x_item_grp_rec.attribute9,
1280 X_ATTRIBUTE10 => p_x_item_grp_rec.attribute10,
1281 X_ATTRIBUTE11 => p_x_item_grp_rec.attribute11,
1282 X_ATTRIBUTE12 => p_x_item_grp_rec.attribute12,
1283 X_ATTRIBUTE13 => p_x_item_grp_rec.attribute13,
1284 X_ATTRIBUTE14 => p_x_item_grp_rec.attribute14,
1285 X_ATTRIBUTE15 => p_x_item_grp_rec.attribute15,
1286 X_DESCRIPTION => p_x_item_grp_rec.description,
1287 X_CREATION_DATE => sysdate,
1288 X_CREATED_BY => to_number(fnd_global.USER_ID),
1289 X_LAST_UPDATE_DATE => sysdate,
1290 X_LAST_UPDATED_BY => l_last_updated_by,
1291 X_LAST_UPDATE_LOGIN => l_last_update_login);
1292
1293
1294 p_x_item_grp_rec.ITEM_GROUP_ID := l_item_grp_id; -- update id in record variable.
1295 p_x_item_grp_rec.OBJECT_VERSION_NUMBER := 1;
1296
1297
1298 END Insert_Item_Group;
1299
1300 ---------------------------------
1301 PROCEDURE Create_Association(p_item_assoc_rec IN OUT NOCOPY AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type)
1302 IS
1303
1304 l_item_assoc_rec AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type DEFAULT p_item_assoc_rec;
1305
1306 l_item_association_id NUMBER;
1307 l_row_id VARCHAR2(30);
1308 BEGIN
1309
1310 -- Replace G_MISS values with nulls.
1311 IF (l_item_assoc_rec.REVISION = FND_API.G_MISS_CHAR) THEN
1312 l_item_assoc_rec.REVISION := null;
1313 END IF;
1314 IF (l_item_assoc_rec.QUANTITY = FND_API.G_MISS_NUM) THEN
1315 l_item_assoc_rec.QUANTITY := null;
1316 l_item_assoc_rec.UOM_CODE := null;
1317 ELSIF (l_item_assoc_rec.QUANTITY IS NULL OR l_item_assoc_rec.QUANTITY = 0) THEN
1318 l_item_assoc_rec.UOM_CODE := null; -- if quantity = 0 then uom must be null.
1319 END IF;
1320 IF (l_item_assoc_rec.UOM_CODE = FND_API.G_MISS_CHAR) THEN
1321 l_item_assoc_rec.UOM_CODE := null;
1322 END IF;
1323 IF (l_item_assoc_rec.INTERCHANGE_TYPE_CODE = FND_API.G_MISS_CHAR) THEN
1324 l_item_assoc_rec.INTERCHANGE_TYPE_CODE := null;
1325 END IF;
1326 IF (l_item_assoc_rec.INTERCHANGE_REASON = FND_API.G_MISS_CHAR) THEN
1327 l_item_assoc_rec.INTERCHANGE_REASON := null;
1328 END IF;
1329
1330 IF (l_item_assoc_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR) THEN
1331 l_item_assoc_rec.ATTRIBUTE_CATEGORY := null;
1332 END IF;
1333 IF (l_item_assoc_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
1334 l_item_assoc_rec.ATTRIBUTE1 := null;
1335 END IF;
1336 IF (l_item_assoc_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
1337 l_item_assoc_rec.ATTRIBUTE2 := null;
1338 END IF;
1339 IF (l_item_assoc_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
1340 l_item_assoc_rec.ATTRIBUTE3 := null;
1341 END IF;
1342 IF (l_item_assoc_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
1343 l_item_assoc_rec.ATTRIBUTE4 := null;
1344 END IF;
1345 IF (l_item_assoc_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR) THEN
1346 l_item_assoc_rec.ATTRIBUTE5 := null;
1347 END IF;
1348 IF (l_item_assoc_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
1349 l_item_assoc_rec.ATTRIBUTE6 := null;
1350 END IF;
1351 IF (l_item_assoc_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
1352 l_item_assoc_rec.ATTRIBUTE7 := null;
1353 END IF;
1354 IF (l_item_assoc_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
1355 l_item_assoc_rec.ATTRIBUTE8 := null;
1356 END IF;
1357 IF (l_item_assoc_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
1358 l_item_assoc_rec.ATTRIBUTE9 := null;
1359 END IF;
1360 IF (l_item_assoc_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
1361 l_item_assoc_rec.ATTRIBUTE10 := null;
1362 END IF;
1363 IF (l_item_assoc_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
1364 l_item_assoc_rec.ATTRIBUTE11 := null;
1365 END IF;
1366 IF (l_item_assoc_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
1367 l_item_assoc_rec.ATTRIBUTE12 := null;
1368 END IF;
1369 IF (l_item_assoc_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
1370 l_item_assoc_rec.ATTRIBUTE13 := null;
1371 END IF;
1372 IF (l_item_assoc_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
1373 l_item_assoc_rec.ATTRIBUTE14 := null;
1374 END IF;
1375 IF (l_item_assoc_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
1376 l_item_assoc_rec.ATTRIBUTE15 := null;
1377 END IF;
1378 --Gets the sequence Number
1379 SELECT AHL_ITEM_ASSOCIATIONS_B_S.nextval INTO
1380 l_item_association_id from DUAL;
1381
1382 AHL_ITEM_ASSOCIATIONS_PKG.INSERT_ROW(
1383 X_ROWID => l_row_id,
1384 X_ITEM_ASSOCIATION_ID => l_item_association_id,
1385 X_SOURCE_ITEM_ASSOCIATION_ID => NULL,
1386 X_OBJECT_VERSION_NUMBER => 1,
1387 X_ITEM_GROUP_ID => l_item_assoc_rec.ITEM_GROUP_ID,
1388 X_INVENTORY_ITEM_ID => l_item_assoc_rec.INVENTORY_ITEM_ID,
1389 X_INVENTORY_ORG_ID => l_item_assoc_rec.INVENTORY_ORG_ID,
1390 X_PRIORITY => l_item_assoc_rec.PRIORITY,
1391 X_UOM_CODE => l_item_assoc_rec.UOM_CODE,
1392 X_QUANTITY => l_item_assoc_rec.QUANTITY,
1393 X_REVISION => l_item_assoc_rec.REVISION,
1394 X_INTERCHANGE_TYPE_CODE => l_item_assoc_rec.INTERCHANGE_TYPE_CODE,
1395 X_ITEM_TYPE_CODE => NULL,
1396 X_ATTRIBUTE_CATEGORY => l_item_assoc_rec.ATTRIBUTE_CATEGORY,
1397 X_ATTRIBUTE1 => l_item_assoc_rec.ATTRIBUTE1,
1398 X_ATTRIBUTE2 => l_item_assoc_rec.ATTRIBUTE2,
1399 X_ATTRIBUTE3 => l_item_assoc_rec.ATTRIBUTE3,
1400 X_ATTRIBUTE4 => l_item_assoc_rec.ATTRIBUTE4,
1401 X_ATTRIBUTE5 => l_item_assoc_rec.ATTRIBUTE5,
1402 X_ATTRIBUTE6 => l_item_assoc_rec.ATTRIBUTE6,
1403 X_ATTRIBUTE7 => l_item_assoc_rec.ATTRIBUTE7,
1404 X_ATTRIBUTE8 => l_item_assoc_rec.ATTRIBUTE8,
1405 X_ATTRIBUTE9 => l_item_assoc_rec.ATTRIBUTE9,
1406 X_ATTRIBUTE10 => l_item_assoc_rec.ATTRIBUTE10,
1407 X_ATTRIBUTE11 => l_item_assoc_rec.ATTRIBUTE11,
1408 X_ATTRIBUTE12 => l_item_assoc_rec.ATTRIBUTE12,
1409 X_ATTRIBUTE13 => l_item_assoc_rec.ATTRIBUTE13,
1410 X_ATTRIBUTE14 => l_item_assoc_rec.ATTRIBUTE14,
1411 X_ATTRIBUTE15 => l_item_assoc_rec.ATTRIBUTE15,
1412 X_INTERCHANGE_REASON => l_item_assoc_rec.INTERCHANGE_REASON,
1413 X_CREATION_DATE => sysdate,
1414 X_CREATED_BY => fnd_global.USER_ID,
1415 X_LAST_UPDATE_DATE => sysdate,
1416 X_LAST_UPDATED_BY => fnd_global.USER_ID,
1417 X_LAST_UPDATE_LOGIN => fnd_global.LOGIN_ID
1418 );
1419
1420 --Insert in AHL_ITEM_ASSOCIATIONS_B table
1421
1422 l_item_assoc_rec.item_association_id := l_item_association_id;
1423 l_item_assoc_rec.object_version_number := 1;
1424 -- Set out parameter.
1425 p_item_assoc_rec := l_item_assoc_rec;
1426
1427 END Create_Association;
1428
1429 ----------------------------
1430 PROCEDURE Update_Association(p_item_assoc_rec IN AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type,
1431 p_row_id IN UROWID) IS
1432
1433 CURSOR Item_assoc_csr(p_rowid UROWID) IS
1434 SELECT
1435 b.ROWID ROW_ID,
1436 b.ITEM_ASSOCIATION_ID,
1437 b.SOURCE_ITEM_ASSOCIATION_ID,
1438 b.ITEM_GROUP_ID,
1439 b.INVENTORY_ITEM_ID,
1440 b.INVENTORY_ORG_ID,
1441 b.REVISION,
1442 b.PRIORITY,
1443 b.QUANTITY,
1444 b.UOM_CODE,
1445 b.INTERCHANGE_TYPE_CODE,
1446 tl.INTERCHANGE_REASON,
1447 -- b.ITEM_TYPE_CODE,
1448 b.OBJECT_VERSION_NUMBER,
1449 tl.LANGUAGE,
1450 tl.SOURCE_LANG,
1451 b.ATTRIBUTE_CATEGORY,
1452 b.ATTRIBUTE1,
1453 b.ATTRIBUTE2,
1454 b.ATTRIBUTE3,
1455 b.ATTRIBUTE4,
1456 b.ATTRIBUTE5,
1457 b.ATTRIBUTE6 ,
1458 b.ATTRIBUTE7 ,
1459 b.ATTRIBUTE8,
1460 b.ATTRIBUTE9 ,
1461 b.ATTRIBUTE10,
1462 b.ATTRIBUTE11 ,
1463 b.ATTRIBUTE12,
1464 b.ATTRIBUTE13,
1465 b.ATTRIBUTE14,
1466 b.ATTRIBUTE15,
1467 b.LAST_UPDATE_DATE,
1468 b.LAST_UPDATED_BY,
1469 b.LAST_UPDATE_LOGIN
1470 FROM ahl_item_associations_b b, ahl_item_associations_tl tl
1471 WHERE b.item_association_id = tl.item_association_id
1472 and b.rowid = p_rowid
1473 AND tl.LANGUAGE = USERENV('LANG')
1474 FOR UPDATE OF object_version_number NOWAIT;
1475
1476 l_item_assoc_rec AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type DEFAULT p_item_assoc_rec;
1477 l_old_item_assoc_rec Item_assoc_csr%ROWTYPE;
1478
1479 BEGIN
1480
1481 OPEN Item_assoc_csr(p_row_id);
1482 FETCH Item_assoc_csr INTO l_old_item_assoc_rec;
1483 IF (Item_assoc_csr%NOTFOUND) THEN
1484 CLOSE Item_assoc_csr;
1485 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
1486 FND_MSG_PUB.ADD;
1487 --dbms_output.put_line('Item Assoc does not exist');
1488 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1489 END IF;
1490
1491 -- Check Object version number.
1492 IF (l_old_item_assoc_rec.object_version_number <> l_item_assoc_rec.object_version_number) THEN
1493 CLOSE Item_assoc_csr;
1494 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1495 FND_MSG_PUB.ADD;
1496 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1497 END IF;
1498
1499 -- Check for changed columns.
1500
1501 IF (l_item_assoc_rec.REVISION = NULL) THEN
1502 l_item_assoc_rec.REVISION := l_old_item_assoc_rec.REVISION;
1503 ELSIF (l_item_assoc_rec.REVISION = FND_API.G_MISS_CHAR) THEN
1504 l_item_assoc_rec.REVISION := NULL;
1505 END IF;
1506
1507 IF (l_item_assoc_rec.PRIORITY = NULL) THEN
1508 l_item_assoc_rec.PRIORITY := l_old_item_assoc_rec.PRIORITY;
1509 ELSIF (l_item_assoc_rec.PRIORITY = FND_API.G_MISS_NUM) THEN
1510 l_item_assoc_rec.PRIORITY := NULL;
1511 END IF;
1512
1513 IF (l_item_assoc_rec.QUANTITY = NULL) THEN
1514 l_item_assoc_rec.QUANTITY := l_old_item_assoc_rec.QUANTITY;
1515 ELSIF (l_item_assoc_rec.QUANTITY = FND_API.G_MISS_NUM) THEN
1516 l_item_assoc_rec.QUANTITY := NULL;
1517 ELSIF (l_item_assoc_rec.QUANTITY = 0) THEN
1518 l_item_assoc_rec.UOM_CODE := null;
1519 END IF;
1520
1521 IF (l_item_assoc_rec.UOM_CODE = NULL) THEN
1522 l_item_assoc_rec.UOM_CODE := l_old_item_assoc_rec.UOM_CODE;
1523 ELSIF (l_item_assoc_rec.UOM_CODE = FND_API.G_MISS_CHAR) THEN
1524 l_item_assoc_rec.UOM_CODE := NULL;
1525 END IF;
1526
1527 IF (l_item_assoc_rec.INTERCHANGE_TYPE_CODE = NULL) THEN
1528 l_item_assoc_rec.INTERCHANGE_TYPE_CODE := l_old_item_assoc_rec.INTERCHANGE_TYPE_CODE;
1529 ELSIF (l_item_assoc_rec.INTERCHANGE_TYPE_CODE = FND_API.G_MISS_CHAR) THEN
1530 l_item_assoc_rec.INTERCHANGE_TYPE_CODE := NULL;
1531 END IF;
1532
1533 IF (l_item_assoc_rec.INTERCHANGE_REASON = NULL) THEN
1534 l_item_assoc_rec.INTERCHANGE_REASON := l_old_item_assoc_rec.INTERCHANGE_REASON;
1535 ELSIF (l_item_assoc_rec.INTERCHANGE_REASON = FND_API.G_MISS_CHAR) THEN
1536 l_item_assoc_rec.INTERCHANGE_REASON := NULL;
1537 END IF;
1538
1539
1540 IF (l_item_assoc_rec.ATTRIBUTE_CATEGORY = NULL) THEN
1541 l_item_assoc_rec.ATTRIBUTE_CATEGORY := l_old_item_assoc_rec.ATTRIBUTE_CATEGORY;
1542 ELSIF (l_item_assoc_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR) THEN
1543 l_item_assoc_rec.ATTRIBUTE_CATEGORY := NULL;
1544 END IF;
1545
1546 IF (l_item_assoc_rec.ATTRIBUTE1 = NULL) THEN
1547 l_item_assoc_rec.ATTRIBUTE1 := l_old_item_assoc_rec.ATTRIBUTE1;
1548 ELSIF (l_item_assoc_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
1549 l_item_assoc_rec.ATTRIBUTE1 := NULL;
1550 END IF;
1551
1552 IF (l_item_assoc_rec.ATTRIBUTE2 = NULL) THEN
1553 l_item_assoc_rec.ATTRIBUTE2 := l_old_item_assoc_rec.ATTRIBUTE2;
1554 ELSIF (l_item_assoc_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
1555 l_item_assoc_rec.ATTRIBUTE2 := NULL;
1556 END IF;
1557
1558 IF (l_item_assoc_rec.ATTRIBUTE3 = NULL) THEN
1559 l_item_assoc_rec.ATTRIBUTE3 := l_old_item_assoc_rec.ATTRIBUTE3;
1560 ELSIF (l_item_assoc_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
1561 l_item_assoc_rec.ATTRIBUTE3 := NULL;
1562 END IF;
1563
1564 IF (l_item_assoc_rec.ATTRIBUTE4 = NULL) THEN
1565 l_item_assoc_rec.ATTRIBUTE4 := l_old_item_assoc_rec.ATTRIBUTE4;
1566 ELSIF (l_item_assoc_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
1567 l_item_assoc_rec.ATTRIBUTE4 := NULL;
1568 END IF;
1569
1570 IF (l_item_assoc_rec.ATTRIBUTE6 = NULL) THEN
1571 l_item_assoc_rec.ATTRIBUTE6 := l_old_item_assoc_rec.ATTRIBUTE6;
1572 ELSIF (l_item_assoc_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
1573 l_item_assoc_rec.ATTRIBUTE6 := NULL;
1574 END IF;
1575
1576 IF (l_item_assoc_rec.ATTRIBUTE7 = NULL) THEN
1577 l_item_assoc_rec.ATTRIBUTE7 := l_old_item_assoc_rec.ATTRIBUTE7;
1578 ELSIF (l_item_assoc_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
1579 l_item_assoc_rec.ATTRIBUTE7 := NULL;
1580 END IF;
1581
1582 IF (l_item_assoc_rec.ATTRIBUTE8 = NULL) THEN
1583 l_item_assoc_rec.ATTRIBUTE8 := l_old_item_assoc_rec.ATTRIBUTE8;
1584 ELSIF (l_item_assoc_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
1585 l_item_assoc_rec.ATTRIBUTE8 := NULL;
1586 END IF;
1587
1588 IF (l_item_assoc_rec.ATTRIBUTE9 = NULL) THEN
1589 l_item_assoc_rec.ATTRIBUTE9 := l_old_item_assoc_rec.ATTRIBUTE9;
1590 ELSIF (l_item_assoc_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
1591 l_item_assoc_rec.ATTRIBUTE9 := NULL;
1592 END IF;
1593
1594 IF (l_item_assoc_rec.ATTRIBUTE10 = NULL) THEN
1595 l_item_assoc_rec.ATTRIBUTE10 := l_old_item_assoc_rec.ATTRIBUTE10;
1596 ELSIF (l_item_assoc_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
1597 l_item_assoc_rec.ATTRIBUTE10 := NULL;
1598 END IF;
1599
1600 IF (l_item_assoc_rec.ATTRIBUTE11 = NULL) THEN
1601 l_item_assoc_rec.ATTRIBUTE11 := l_old_item_assoc_rec.ATTRIBUTE11;
1602 ELSIF (l_item_assoc_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
1603 l_item_assoc_rec.ATTRIBUTE11 := NULL;
1604 END IF;
1605
1606 IF (l_item_assoc_rec.ATTRIBUTE12 = NULL) THEN
1607 l_item_assoc_rec.ATTRIBUTE12 := l_old_item_assoc_rec.ATTRIBUTE12;
1608 ELSIF (l_item_assoc_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
1609 l_item_assoc_rec.ATTRIBUTE12 := NULL;
1610 END IF;
1611
1612 IF (l_item_assoc_rec.ATTRIBUTE13 = NULL) THEN
1613 l_item_assoc_rec.ATTRIBUTE13 := l_old_item_assoc_rec.ATTRIBUTE13;
1614 ELSIF (l_item_assoc_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
1615 l_item_assoc_rec.ATTRIBUTE13 := NULL;
1616 END IF;
1617
1618 IF (l_item_assoc_rec.ATTRIBUTE14 = NULL) THEN
1619 l_item_assoc_rec.ATTRIBUTE14 := l_old_item_assoc_rec.ATTRIBUTE14;
1620 ELSIF (l_item_assoc_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
1621 l_item_assoc_rec.ATTRIBUTE14 := NULL;
1622 END IF;
1623
1624 IF (l_item_assoc_rec.ATTRIBUTE15 = NULL) THEN
1625 l_item_assoc_rec.ATTRIBUTE15 := l_old_item_assoc_rec.ATTRIBUTE15;
1626 ELSIF (l_item_assoc_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
1627 l_item_assoc_rec.ATTRIBUTE15 := NULL;
1628 END IF;
1629
1630 /*
1631 IF (l_item_assoc_rec.SOURCE_LANG = NULL) THEN
1632 l_item_assoc_rec.SOURCE_LANG := l_old_item_assoc_rec.SOURCE_LANG;
1633 ELSIF (l_item_assoc_rec.SOURCE_LANG = FND_API.G_MISS_CHAR) THEN
1634 l_item_assoc_rec.SOURCE_LANG := NULL;
1635 END IF;
1636 */
1637
1638 AHL_ITEM_ASSOCIATIONS_PKG.UPDATE_ROW(
1639 X_ITEM_ASSOCIATION_ID => l_item_assoc_rec.item_association_id,
1640 X_SOURCE_ITEM_ASSOCIATION_ID => l_old_item_assoc_rec.SOURCE_ITEM_ASSOCIATION_ID,
1641 X_OBJECT_VERSION_NUMBER => l_old_item_assoc_rec.object_version_number + 1,
1642 X_ITEM_GROUP_ID => l_item_assoc_rec.item_group_id,
1643 X_INVENTORY_ITEM_ID => l_item_assoc_rec.inventory_item_id,
1644 X_INVENTORY_ORG_ID => l_item_assoc_rec.inventory_org_id,
1645 X_PRIORITY => l_item_assoc_rec.priority,
1646 X_UOM_CODE => l_item_assoc_rec.uom_code,
1647 X_QUANTITY => l_item_assoc_rec.quantity,
1648 X_REVISION => l_item_assoc_rec.revision,
1649 X_INTERCHANGE_TYPE_CODE => l_item_assoc_rec.interchange_type_code,
1650 X_ITEM_TYPE_CODE => NULL,
1651 X_ATTRIBUTE_CATEGORY => l_item_assoc_rec.ATTRIBUTE_CATEGORY,
1652 X_ATTRIBUTE1 => l_item_assoc_rec.attribute1,
1653 X_ATTRIBUTE2 => l_item_assoc_rec.attribute2,
1654 X_ATTRIBUTE3 => l_item_assoc_rec.attribute3,
1655 X_ATTRIBUTE4 => l_item_assoc_rec.attribute4,
1656 X_ATTRIBUTE5 => l_item_assoc_rec.attribute5,
1657 X_ATTRIBUTE6 => l_item_assoc_rec.attribute6,
1658 X_ATTRIBUTE7 => l_item_assoc_rec.attribute7,
1659 X_ATTRIBUTE8 => l_item_assoc_rec.attribute8,
1660 X_ATTRIBUTE9 => l_item_assoc_rec.attribute9,
1661 X_ATTRIBUTE10 => l_item_assoc_rec.attribute10,
1662 X_ATTRIBUTE11 => l_item_assoc_rec.attribute11,
1663 X_ATTRIBUTE12 => l_item_assoc_rec.attribute12,
1664 X_ATTRIBUTE13 => l_item_assoc_rec.attribute13,
1665 X_ATTRIBUTE14 => l_item_assoc_rec.attribute14,
1666 X_ATTRIBUTE15 => l_item_assoc_rec.attribute15,
1667 X_INTERCHANGE_REASON => l_item_assoc_rec.interchange_reason,
1668 X_LAST_UPDATE_DATE => sysdate,
1669 X_LAST_UPDATED_BY => fnd_global.USER_ID,
1670 X_LAST_UPDATE_LOGIN => fnd_global.LOGIN_ID);
1671
1672
1673 CLOSE Item_assoc_csr;
1674
1675 END Update_Association;
1676
1677 -----------------------------
1678 PROCEDURE Delete_Association(p_item_assoc_rec IN AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type,
1679 p_row_id IN UROWID) IS
1680
1681 CURSOR Item_assoc_csr(p_row_id UROWID) IS
1682 SELECT
1683 Object_version_number
1684 FROM ahl_item_associations_vl
1685 WHERE row_id = p_row_id
1686 FOR UPDATE OF object_version_number NOWAIT;
1687
1688 l_object_version_number NUMBER;
1689
1690 BEGIN
1691
1692 OPEN Item_assoc_csr(p_row_id);
1693 FETCH Item_assoc_csr INTO l_object_version_number;
1694 IF (Item_assoc_csr%NOTFOUND) THEN
1695 CLOSE Item_assoc_csr;
1696 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
1697 FND_MSG_PUB.ADD;
1698 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1699 END IF;
1700
1701 -- Check Object version number.
1702 IF (l_object_version_number <> p_item_assoc_rec.object_version_number) THEN
1703 CLOSE Item_assoc_csr;
1704 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1705 FND_MSG_PUB.ADD;
1706 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1707 END IF;
1708
1709 -- Delete record.
1710 DELETE ahl_item_associations_b
1711 WHERE item_association_id = p_item_assoc_rec.item_association_id;
1712
1713 DELETE ahl_item_associations_tl
1714 WHERE item_association_id = p_item_assoc_rec.item_association_id;
1715
1716 CLOSE Item_assoc_csr;
1717
1718 END Delete_Association;
1719
1720
1721 -----------------------------------------
1722 -- Procedures for Item Groups --
1723 -----------------------------------------
1724 -- Start of Comments --
1725 -- Procedure name : Create_Item_group
1726 -- Type : Private
1727 -- Function : Creates Item Group for Master Configuration in ahl_item_groups_b and TL tables. Also creates item-group association in ahl_item_associations_b/_tl table.
1728 -- Pre-reqs :
1729 -- Parameters :
1730 -- End of Comments --
1731
1732 PROCEDURE Create_Item_group (p_api_version IN NUMBER,
1733 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1734 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1735 x_return_status OUT NOCOPY VARCHAR2,
1736 x_msg_count OUT NOCOPY NUMBER,
1737 x_msg_data OUT NOCOPY VARCHAR2,
1738 p_x_item_group_rec IN OUT NOCOPY AHL_MC_ITEMGROUP_PVT.Item_Group_Rec_Type,
1739 p_x_items_tbl IN OUT NOCOPY AHL_MC_ITEMGROUP_PVT.Item_Association_Tbl_Type
1740 ) IS
1741
1742 l_api_name CONSTANT VARCHAR2(30) := 'Create_Item_Group';
1743 l_api_version CONSTANT NUMBER := 1.0;
1744
1745 -- Changes by skpathak for bug-7437855 on 18-NOV-2008
1746 -- Duplicate item association check is now done using G_ITEM_DTL_TYPE, instead of a string.
1747 l_inventory_list G_ITEM_DTL_TYPE;
1748 l_inventory_item_id NUMBER;
1749 l_item_group_id NUMBER; -- sequence generated item group id.
1750 l_name ahl_item_groups_b.name%TYPE;
1751 l_inv_segment ahl_mtl_items_non_ou_v.concatenated_segments%TYPE;
1752 l_row_id VARCHAR2(30);
1753
1754 BEGIN
1755 -- Standard start of API savepoint
1756 SAVEPOINT Create_Item_group_Pvt;
1757
1758
1759 -- Standard call to check for call compatibility
1760 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1761 G_PKG_NAME) THEN
1762 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1763 END IF;
1764
1765 -- Initialize message list if p_init_msg_list is set to TRUE
1766 IF FND_API.To_Boolean(p_init_msg_list) THEN
1767 FND_MSG_PUB.Initialize;
1768 END IF;
1769
1770 -- Initialize API return status to success
1771 x_return_status := FND_API.G_RET_STS_SUCCESS;
1772
1773 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1774 THEN
1775 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1776 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Inside Create_Item_group');
1777 END IF;
1778
1779 --dbms_output.put_line('Inside Create_Item_group');
1780
1781 -- Validate Item Group Name.
1782 p_x_item_group_rec.name := RTRIM(p_x_item_group_rec.name);
1783 -- TAMAL -- IG Amendments --
1784 Validate_Item_Group_Name(p_x_item_group_rec.name, null, p_x_item_group_rec.source_item_group_id);
1785 -- TAMAL -- IG Amendments --
1786 l_name := p_x_item_group_rec.name; -- Item Group name.
1787
1788 --dbms_output.put_line('After validating Item Group Name');
1789
1790 -- Validate Item Association record columns.
1791 IF (p_x_items_tbl.COUNT > 0) THEN
1792 -- Added by skpathak for bug-7437855 on 18-NOV-2008 - Clear l_inventory_list before using.
1793 l_inventory_list.DELETE;
1794
1795 FOR i IN p_x_items_tbl.FIRST..p_x_items_tbl.LAST LOOP
1796 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1797 THEN
1798 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1799 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Before validating Item Group Associations');
1800 END IF;
1801
1802 Validate_Item_Assoc(p_name => p_x_item_group_rec.name,
1803 p_type_code => p_x_item_group_rec.type_code,
1804 p_item_assoc_rec => p_x_items_tbl(i),
1805 p_x_inventory_list => l_inventory_list,
1806 x_row_id => l_row_id);
1807
1808 END LOOP;
1809 END IF; /* for count > 0 */
1810
1811 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1812 THEN
1813 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1814 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'After validating Item Group Associations');
1815 END IF;
1816
1817
1818 -- Check Error Message stack.
1819 x_msg_count := FND_MSG_PUB.count_msg;
1820 IF x_msg_count > 0 THEN
1821 RAISE FND_API.G_EXC_ERROR;
1822 END IF;
1823
1824 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1825 THEN
1826 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1827 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Before calling Insert_Item_group');
1828 END IF;
1829
1830 -- Insert into ahl_item_groups_b and TL.
1831 Insert_Item_group(p_x_item_group_rec);
1832
1833 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1834 THEN
1835 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1836 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'After calling Insert_Item_group');
1837 END IF;
1838
1839
1840 l_item_group_id := p_x_item_group_rec.item_group_id;
1841
1842 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1843 THEN
1844 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1845 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Before loop of Item Association');
1846 END IF;
1847
1848
1849 -- Insert into ahl_item_associations_b/_tl.
1850 IF (p_x_items_tbl.COUNT > 0) THEN
1851 FOR i IN p_x_items_tbl.FIRST..p_x_items_tbl.LAST LOOP
1852 p_x_items_tbl(i).ITEM_GROUP_ID := l_item_group_id;
1853 Create_Association(p_x_items_tbl(i));
1854 END LOOP;
1855 END IF; /* count > 0 */
1856
1857 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1858 THEN
1859 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1860 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'After loop of Item Association');
1861 END IF;
1862
1863 validate_priority(l_item_group_id);
1864
1865 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1866 THEN
1867 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1868 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'End of create_item_group private');
1869 END IF;
1870
1871
1872 -- Standard call to get message count and if count is 1, get message info
1873 FND_MSG_PUB.Count_And_Get
1874 ( p_count => x_msg_count,
1875 p_data => x_msg_data,
1876 p_encoded => fnd_api.g_false);
1877
1878 EXCEPTION
1879 WHEN FND_API.G_EXC_ERROR THEN
1880 x_return_status := FND_API.G_RET_STS_ERROR;
1881 Rollback to Create_Item_group_pvt;
1882 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1883 p_data => x_msg_data,
1884 p_encoded => fnd_api.g_false);
1885 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1886 THEN
1887 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1888 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Error in create_item_group private');
1889 END IF;
1890
1891
1892
1893 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1894 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1895 Rollback to Create_Item_group_pvt;
1896 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1897 p_data => x_msg_data,
1898 p_encoded => fnd_api.g_false);
1899 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
1900 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1901 THEN
1902 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1903 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Unexpected error in create_item_group private');
1904 END IF;
1905
1906
1907 WHEN OTHERS THEN
1908 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1909 Rollback to Create_Item_group_pvt;
1910 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1911 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1912 p_procedure_name => 'Create_Item_group',
1913 p_error_text => SQLERRM);
1914 END IF;
1915 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1916 p_data => x_msg_data,
1917 p_encoded => fnd_api.g_false);
1918 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
1919
1920 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1921 THEN
1922 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1923 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Unknown error in create_item_group private');
1924 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1925 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', SQLERRM);
1926 END IF;
1927
1928 END Create_Item_group;
1929
1930
1931 -- Start of Comments --
1932 -- Procedure name : Modify_Item_group
1933 -- Type : Private
1934 -- Function : Modifies Item Group for Master Configuration in ahl_item_groups_b and TL tables. Also creates/deletes/modifies item-group association in ahl_item_associations_b/_tl table.
1935 -- Pre-reqs :
1936 -- Parameters :
1937 -- Standard IN Parameters :
1938
1939 PROCEDURE Modify_Item_group (p_api_version IN NUMBER,
1940 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1941 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1942 x_return_status OUT NOCOPY VARCHAR2,
1943 x_msg_count OUT NOCOPY NUMBER,
1944 x_msg_data OUT NOCOPY VARCHAR2,
1945 p_item_group_rec IN AHL_MC_ITEMGROUP_PVT.Item_Group_Rec_Type,
1946 p_x_items_tbl IN OUT NOCOPY AHL_MC_ITEMGROUP_PVT.Item_Association_Tbl_Type
1947 ) IS
1948
1949 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Item_Group';
1950 l_api_version CONSTANT NUMBER := 1.0;
1951
1952 CURSOR Item_group_csr(p_item_group_id IN NUMBER) IS
1953 SELECT
1954 b.ROWID ROW_ID,
1955 b.ITEM_GROUP_ID,
1956 b.source_item_group_id,
1957 b.NAME,
1958 b.type_code,
1959 b.status_code,
1960 b.OBJECT_VERSION_NUMBER,
1961 b.ATTRIBUTE_CATEGORY,
1962 b.ATTRIBUTE1,
1963 b.ATTRIBUTE2,
1964 b.ATTRIBUTE3,
1965 b.ATTRIBUTE4,
1966 b.ATTRIBUTE5,
1967 b.ATTRIBUTE6,
1968 b.ATTRIBUTE7,
1969 b.ATTRIBUTE8,
1970 b.ATTRIBUTE9,
1971 b.ATTRIBUTE10,
1972 b.ATTRIBUTE11,
1973 b.ATTRIBUTE12,
1974 b.ATTRIBUTE13,
1975 b.ATTRIBUTE14,
1976 b.ATTRIBUTE15,
1977 b.LAST_UPDATE_DATE,
1978 b.LAST_UPDATED_BY,
1979 b.CREATION_DATE,
1980 b.CREATED_BY,
1981 b.LAST_UPDATE_LOGIN,
1982 TL.LANGUAGE,
1983 TL.SOURCE_LANG,
1984 TL.DESCRIPTION
1985 FROM
1986 AHL_ITEM_GROUPS_B b, AHL_ITEM_GROUPS_TL tl
1987 WHERE
1988 b.ITEM_GROUP_ID = tl.ITEM_GROUP_ID
1989 AND b.ITEM_GROUP_ID = p_item_group_id
1990 AND tl.LANGUAGE = USERENV('LANG')
1991 FOR UPDATE OF b.OBJECT_VERSION_NUMBER NOWAIT;
1992
1993 l_old_item_group_rec Item_group_csr%ROWTYPE;
1994 l_item_group_rec AHL_MC_ITEMGROUP_PVT.Item_group_rec_Type DEFAULT p_item_group_rec;
1995
1996 TYPE l_rowid_tbl_type IS TABLE OF UROWID INDEX BY BINARY_INTEGER;
1997 -- Build table with Rowid for Item Associations.
1998
1999 -- Changes by skpathak for bug-7437855 on 18-NOV-2008
2000 -- Duplicate item association check is now done using G_ITEM_DTL_TYPE, instead of a string.
2001 l_inventory_list G_ITEM_DTL_TYPE;
2002 l_rowid_tbl l_rowid_tbl_type;
2003 l_row_id VARCHAR2(30);
2004
2005 -- SATHAPLI::Bug# 4328454 fix
2006 l_full_name CONSTANT VARCHAR(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2007 l_invalid_item_instance_tbl AHL_UTIL_UC_PKG.Instance_Tbl_Type2;
2008 l_update_flag VARCHAR2(1) := 'N';
2009 l_index NUMBER := 1;
2010
2011 BEGIN
2012
2013 -- Standard start of API savepoint
2014 SAVEPOINT Modify_Item_group_Pvt;
2015
2016 -- Standard call to check for call compatibility
2017 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
2018 G_PKG_NAME) THEN
2019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2020 END IF;
2021
2022 -- Initialize message list if p_init_msg_list is set to TRUE
2023 IF FND_API.To_Boolean(p_init_msg_list) THEN
2024 FND_MSG_PUB.Initialize;
2025 END IF;
2026
2027 -- Initialize API return status to success
2028 x_return_status := FND_API.G_RET_STS_SUCCESS;
2029
2030 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2031 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2032 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Begin of Modify_Item_group');
2033 END IF;
2034
2035 -- Validate Item Group record.
2036 OPEN Item_group_csr(p_item_group_rec.item_group_id);
2037 FETCH Item_group_csr INTO l_old_item_group_rec;
2038 IF (Item_group_csr%NOTFOUND) THEN
2039 CLOSE Item_group_csr;
2040 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_NOT_EXISTS');
2041 FND_MSG_PUB.ADD;
2042 --dbms_output.put_line('Item Group does not exist');
2043 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2044 END IF;
2045
2046 -- Check Object version number.
2047 IF (l_old_item_group_rec.object_version_number <> NVL(p_item_group_rec.object_version_number,0)) THEN
2048 CLOSE Item_group_csr;
2049 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
2050 FND_MSG_PUB.ADD;
2051 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2052 END IF;
2053
2054 -- Check name
2055 -- TAMAL -- IG Amendments --
2056 IF (l_old_item_group_rec.status_code = 'DRAFT' and l_old_item_group_rec.source_item_group_id is not null)
2057 THEN
2058 Validate_Item_Group_Name (l_item_group_rec.name, l_old_item_group_rec.item_group_id, l_old_item_group_rec.source_item_group_id);
2059 x_msg_count := FND_MSG_PUB.count_msg;
2060 IF x_msg_count > 0
2061 THEN
2062 CLOSE Item_group_csr;
2063 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2064 END IF;
2065 l_old_item_group_rec.name := l_item_group_rec.name;
2066 -- TAMAL -- IG Amendments --
2067 ELSIF (l_old_item_group_rec.name <> p_item_group_rec.name )
2068 THEN
2069 CLOSE Item_group_csr;
2070 FND_MESSAGE.Set_Name('AHL','AHL_COM_KEY_NOUPDATE');
2071 FND_MSG_PUB.ADD;
2072 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2073 END IF;
2074
2075 IF l_old_item_group_rec.status_code in ('COMPLETE','APPROVAL_PENDING')
2076 THEN
2077 CLOSE Item_group_csr;
2078 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_NOUPDATE');
2079 FND_MESSAGE.Set_Token('STATUS',p_item_group_rec.status_code);
2080 FND_MSG_PUB.ADD;
2081 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2082 --dbms_output.put_line('Item Group cannot be updated if status is Complete or Approval Rejected');
2083 END IF;
2084
2085 IF (p_item_group_rec.status_code <> FND_API.G_MISS_CHAR) THEN
2086
2087 IF (p_item_group_rec.status_code <> l_old_item_group_rec.status_code)
2088 THEN
2089 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_STAT_NOUPDATE');
2090 FND_MSG_PUB.ADD;
2091 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2092 --dbms_output.put_line('Item Group Status cannot be updated');
2093 END IF;
2094 END IF;
2095
2096 IF (p_item_group_rec.type_code <> FND_API.G_MISS_CHAR) THEN
2097 IF (p_item_group_rec.type_code <> l_old_item_group_rec.type_code)
2098 THEN
2099 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_TYPE_NOUPDATE');
2100 FND_MSG_PUB.ADD;
2101 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2102 --dbms_output.put_line('Item Group Type cannot be updated');
2103 END IF;
2104
2105 END IF;
2106
2107 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2108 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2109 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Loop to validate Item Association');
2110 END IF;
2111
2112 -- Validate Item Assoc records.
2113 IF (p_x_items_tbl.COUNT > 0 ) THEN
2114 -- Added by skpathak for bug-7437855 on 18-NOV-2008 - Clear l_inventory_list before using.
2115 l_inventory_list.DELETE;
2116
2117 FOR i IN p_x_items_tbl.FIRST..p_x_items_tbl.LAST LOOP
2118 -- Check if association record belongs to the item group.
2119 IF (p_x_items_tbl(i).item_group_id <> p_item_group_rec.item_group_id) THEN
2120 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_MISMATCH');
2121 FND_MESSAGE.Set_Token('ITEM_GRP',p_item_group_rec.item_group_id);
2122 FND_MESSAGE.Set_Token('ASSO_GRP',p_x_items_tbl(i).item_group_id);
2123 FND_MSG_PUB.ADD;
2124 --dbms_output.put_line('Item Association record does not match Item Group');
2125 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2126 END IF;
2127
2128 Validate_Item_Assoc(p_name => p_item_group_rec.name,
2129 p_type_code => p_item_group_rec.type_code,
2130 p_item_group_id => p_item_group_rec.item_group_id,
2131 p_item_assoc_rec => p_x_items_tbl(i),
2132 p_x_inventory_list => l_inventory_list,
2133 x_row_id => l_row_id);
2134
2135 l_rowid_tbl(i) := l_row_id;
2136
2137 END LOOP;
2138 END IF; /* count > 0 */
2139
2140 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2141 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2142 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'End of Loop');
2143 END IF;
2144
2145 -- Check Error Message stack.
2146 x_msg_count := FND_MSG_PUB.count_msg;
2147 IF x_msg_count > 0 THEN
2148 RAISE FND_API.G_EXC_ERROR;
2149 END IF;
2150
2151 IF (l_item_group_rec.operation_flag = 'M') THEN
2152
2153 -- Check for changed values.
2154
2155 IF (l_old_item_group_rec.status_code = 'APPROVAL_REJECTED') THEN
2156 l_old_item_group_rec.status_code := 'DRAFT';
2157 END IF;
2158
2159 IF (l_item_group_rec.DESCRIPTION = NULL) THEN
2160 l_item_group_rec.DESCRIPTION := l_old_item_group_rec.DESCRIPTION;
2161 ELSIF (l_item_group_rec.DESCRIPTION = FND_API.G_MISS_CHAR) THEN
2162 l_item_group_rec.DESCRIPTION := NULL;
2163 END IF;
2164
2165 IF (l_item_group_rec.ATTRIBUTE_CATEGORY = NULL) THEN
2166 l_item_group_rec.ATTRIBUTE_CATEGORY := l_old_item_group_rec.ATTRIBUTE_CATEGORY;
2167 ELSIF (l_item_group_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR) THEN
2168 l_item_group_rec.ATTRIBUTE_CATEGORY := NULL;
2169 END IF;
2170
2171 IF (l_item_group_rec.ATTRIBUTE1 = NULL) THEN
2172 l_item_group_rec.ATTRIBUTE1 := l_old_item_group_rec.ATTRIBUTE1;
2173 ELSIF (l_item_group_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
2174 l_item_group_rec.ATTRIBUTE1 := NULL;
2175 END IF;
2176
2177 IF (l_item_group_rec.ATTRIBUTE2 = NULL) THEN
2178 l_item_group_rec.ATTRIBUTE2 := l_old_item_group_rec.ATTRIBUTE2;
2179 ELSIF (l_item_group_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
2180 l_item_group_rec.ATTRIBUTE2 := NULL;
2181 END IF;
2182
2183 IF (l_item_group_rec.ATTRIBUTE3 = NULL) THEN
2184 l_item_group_rec.ATTRIBUTE3 := l_old_item_group_rec.ATTRIBUTE3;
2185 ELSIF (l_item_group_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
2186 l_item_group_rec.ATTRIBUTE3 := NULL;
2187 END IF;
2188
2189 IF (l_item_group_rec.ATTRIBUTE4 = NULL) THEN
2190 l_item_group_rec.ATTRIBUTE4 := l_old_item_group_rec.ATTRIBUTE4;
2191 ELSIF (l_item_group_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
2192 l_item_group_rec.ATTRIBUTE4 := NULL;
2193 END IF;
2194
2195 IF (l_item_group_rec.ATTRIBUTE6 = NULL) THEN
2196 l_item_group_rec.ATTRIBUTE6 := l_old_item_group_rec.ATTRIBUTE6;
2197 ELSIF (l_item_group_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
2198 l_item_group_rec.ATTRIBUTE6 := NULL;
2199 END IF;
2200
2201 IF (l_item_group_rec.ATTRIBUTE7 = NULL) THEN
2202 l_item_group_rec.ATTRIBUTE7 := l_old_item_group_rec.ATTRIBUTE7;
2203 ELSIF (l_item_group_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
2204 l_item_group_rec.ATTRIBUTE7 := NULL;
2205 END IF;
2206
2207 IF (l_item_group_rec.ATTRIBUTE8 = NULL) THEN
2208 l_item_group_rec.ATTRIBUTE8 := l_old_item_group_rec.ATTRIBUTE8;
2209 ELSIF (l_item_group_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
2210 l_item_group_rec.ATTRIBUTE8 := NULL;
2211 END IF;
2212
2213 IF (l_item_group_rec.ATTRIBUTE9 = NULL) THEN
2214 l_item_group_rec.ATTRIBUTE9 := l_old_item_group_rec.ATTRIBUTE9;
2215 ELSIF (l_item_group_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
2216 l_item_group_rec.ATTRIBUTE9 := NULL;
2217 END IF;
2218
2219 IF (l_item_group_rec.ATTRIBUTE10 = NULL) THEN
2220 l_item_group_rec.ATTRIBUTE10 := l_old_item_group_rec.ATTRIBUTE10;
2221 ELSIF (l_item_group_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
2222 l_item_group_rec.ATTRIBUTE10 := NULL;
2223 END IF;
2224
2225 IF (l_item_group_rec.ATTRIBUTE11 = NULL) THEN
2226 l_item_group_rec.ATTRIBUTE11 := l_old_item_group_rec.ATTRIBUTE11;
2227 ELSIF (l_item_group_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
2228 l_item_group_rec.ATTRIBUTE11 := NULL;
2229 END IF;
2230
2231 IF (l_item_group_rec.ATTRIBUTE12 = NULL) THEN
2232 l_item_group_rec.ATTRIBUTE12 := l_old_item_group_rec.ATTRIBUTE12;
2233 ELSIF (l_item_group_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
2234 l_item_group_rec.ATTRIBUTE12 := NULL;
2235 END IF;
2236
2237 IF (l_item_group_rec.ATTRIBUTE13 = NULL) THEN
2238 l_item_group_rec.ATTRIBUTE13 := l_old_item_group_rec.ATTRIBUTE13;
2239 ELSIF (l_item_group_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
2240 l_item_group_rec.ATTRIBUTE13 := NULL;
2241 END IF;
2242
2243 IF (l_item_group_rec.ATTRIBUTE14 = NULL) THEN
2244 l_item_group_rec.ATTRIBUTE14 := l_old_item_group_rec.ATTRIBUTE14;
2245 ELSIF (l_item_group_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
2246 l_item_group_rec.ATTRIBUTE14 := NULL;
2247 END IF;
2248
2249 IF (l_item_group_rec.ATTRIBUTE15 = NULL) THEN
2250 l_item_group_rec.ATTRIBUTE15 := l_old_item_group_rec.ATTRIBUTE15;
2251 ELSIF (l_item_group_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
2252 l_item_group_rec.ATTRIBUTE15 := NULL;
2253 END IF;
2254
2255 /*
2256 IF (l_item_group_rec.SOURCE_LANG = NULL) THEN
2257 l_item_group_rec.SOURCE_LANG := l_old_item_group_rec.SOURCE_LANG;
2258 ELSIF (l_item_group_rec.SOURCE_LANG = FND_API.G_MISS_CHAR) THEN
2259 l_item_group_rec.SOURCE_LANG := NULL;
2260 END IF;
2261 */
2262
2263 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2264 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2265 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Before calling Item Group Table Handler');
2266 END IF;
2267
2268 AHL_ITEM_GROUPS_PKG.UPDATE_ROW(
2269 X_ITEM_GROUP_ID => l_old_item_group_rec.item_group_id,
2270 X_TYPE_CODE => l_old_item_group_rec.type_code,
2271 X_STATUS_CODE => l_old_item_group_rec.status_code,
2272 X_SOURCE_ITEM_GROUP_ID => l_old_item_group_rec.source_item_group_id,
2273 X_OBJECT_VERSION_NUMBER => l_old_item_group_rec.OBJECT_VERSION_NUMBER + 1,
2274 X_NAME => l_old_item_group_rec.name,
2275 X_ATTRIBUTE_CATEGORY => l_item_group_rec.ATTRIBUTE_CATEGORY,
2276 X_ATTRIBUTE1 => l_item_group_rec.ATTRIBUTE1,
2277 X_ATTRIBUTE2 => l_item_group_rec.ATTRIBUTE2,
2278 X_ATTRIBUTE3 => l_item_group_rec.ATTRIBUTE3,
2279 X_ATTRIBUTE4 => l_item_group_rec.ATTRIBUTE4,
2280 X_ATTRIBUTE5 => l_item_group_rec.ATTRIBUTE5,
2281 X_ATTRIBUTE6 => l_item_group_rec.ATTRIBUTE6,
2282 X_ATTRIBUTE7 => l_item_group_rec.ATTRIBUTE7,
2283 X_ATTRIBUTE8 => l_item_group_rec.ATTRIBUTE8,
2284 X_ATTRIBUTE9 => l_item_group_rec.ATTRIBUTE9,
2285 X_ATTRIBUTE10 => l_item_group_rec.ATTRIBUTE10,
2286 X_ATTRIBUTE11 => l_item_group_rec.ATTRIBUTE11,
2287 X_ATTRIBUTE12 => l_item_group_rec.ATTRIBUTE12,
2288 X_ATTRIBUTE13 => l_item_group_rec.ATTRIBUTE13,
2289 X_ATTRIBUTE14 => l_item_group_rec.ATTRIBUTE14,
2290 X_ATTRIBUTE15 => l_item_group_rec.ATTRIBUTE15,
2291 X_DESCRIPTION => l_item_group_rec.DESCRIPTION,
2292 X_LAST_UPDATE_DATE => sysdate,
2293 X_LAST_UPDATED_BY => fnd_global.USER_ID,
2294 X_LAST_UPDATE_LOGIN => fnd_global.LOGIN_ID);
2295
2296 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2297 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2298 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'After Table Handler');
2299 END IF;
2300
2301
2302 END IF; /* update only if operation_flag set */
2303 -- End Updates for Item_Groups.
2304
2305 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2306 FND_LOG.string(FND_LOG.level_statement,l_full_name,'l_old_item_group_rec.source_item_group_id => '||
2307 l_old_item_group_rec.source_item_group_id);
2308 END IF;
2309
2310 -- SATHAPLI::Bug# 5566764 fix
2311 -- Checking whether the item group being updated is a new revision or not
2312 -- The validation of the revision update will be done only for revisions
2313 IF l_old_item_group_rec.source_item_group_id IS NOT NULL THEN
2314
2315 -- SATHAPLI::Bug# 4328454 fix
2316 -- Validate the deleted items before actually deleting them
2317 IF (p_x_items_tbl.COUNT > 0 ) THEN
2318 FOR i IN p_x_items_tbl.FIRST..p_x_items_tbl.LAST LOOP
2319 IF (p_x_items_tbl(i).operation_flag = 'D') THEN
2320 get_Item_detail(
2321 p_assoc_id => p_x_items_tbl(i).item_association_id,
2322 x_item_group_id => l_invalid_item_instance_tbl(l_index).item_group_id,
2323 x_inventory_item_id => l_invalid_item_instance_tbl(l_index).inventory_item_id,
2324 x_inventory_org_id => l_invalid_item_instance_tbl(l_index).inventory_org_id,
2325 x_concatenated_segments => l_invalid_item_instance_tbl(l_index).concatenated_segments,
2326 x_revision => l_invalid_item_instance_tbl(l_index).revision
2327 );
2328 l_index := l_index + 1;
2329 END IF;
2330 END LOOP;
2331 END IF;
2332
2333 IF (l_index > 1) THEN
2334 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2335 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Validating '||l_index||
2336 ' items in the IG for remove.');
2337 END IF;
2338
2339 AHL_UTIL_UC_PKG.Check_Invalidate_Instance
2340 (
2341 p_api_version => 1.0,
2342 p_init_msg_list => FND_API.G_FALSE,
2343 p_instance_tbl => l_invalid_item_instance_tbl,
2344 p_operator => 'D',
2345 x_return_status => x_return_status,
2346 x_msg_count => x_msg_count,
2347 x_msg_data => x_msg_data
2348 );
2349 END IF;
2350
2351 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2352 RAISE FND_API.G_EXC_ERROR;
2353 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2354 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2355 END IF;
2356
2357 END IF; -- end of l_old_item_group_rec.source_item_group_id IS NOT NULL
2358
2359 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2360 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2361 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Item Association Loop');
2362 END IF;
2363
2364 -- Process Item Associations table.
2365 -- Priyan . Changed the order of the DML flag checks made for item group associations.
2366 --Bug # 4330922
2367 IF (p_x_items_tbl.COUNT > 0 ) THEN
2368 FOR i IN p_x_items_tbl.FIRST..p_x_items_tbl.LAST LOOP
2369 IF (p_x_items_tbl(i).operation_flag = 'D') THEN
2370 Delete_Association(p_x_items_tbl(i),l_rowid_tbl(i));
2371 ELSIF (p_x_items_tbl(i).operation_flag = 'M') THEN
2372 Update_Association(p_x_items_tbl(i), l_rowid_tbl(i));
2373
2374 -- SATHAPLI::Bug# 4328454 fix
2375 l_update_flag := 'Y';
2376
2377 ELSIF (p_x_items_tbl(i).operation_flag = 'C') THEN
2378 Create_Association(p_x_items_tbl(i));
2379 END IF;
2380 END LOOP;
2381 END IF;
2382
2383 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2384 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2385 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'End of Loop');
2386 END IF;
2387
2388 -- The validation of the revision update will be done only for revisions
2389 IF l_old_item_group_rec.source_item_group_id IS NOT NULL THEN
2390
2391 -- SATHAPLI::Bug# 4328454 fix
2392 -- Validate updation of interchange_type_code
2393 IF (l_update_flag = 'Y') THEN
2394 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2395 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Calling validate_IG_update.');
2396 END IF;
2397
2398 validate_IG_update
2399 (
2400 p_ItemGroup_id => p_item_group_rec.item_group_id,
2401 x_return_status => x_return_status,
2402 x_msg_count => x_msg_count,
2403 x_msg_data => x_msg_data
2404 );
2405 END IF;
2406
2407 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2408 RAISE FND_API.G_EXC_ERROR;
2409 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2411 END IF;
2412
2413 END IF; -- end of l_old_item_group_rec.source_item_group_id IS NOT NULL
2414
2415 CLOSE Item_group_csr;
2416
2417 -- Validate priority for duplicate.
2418 validate_priority(l_old_item_group_rec.item_group_id);
2419
2420 --Priyan for mass update
2421 --Validate revision
2422 --Bug # 4330922
2423 validate_IG_revision(l_old_item_group_rec.item_group_id);
2424 --Priyan End
2425
2426 -- Standard call to get message count and if count is 1, get message info
2427 FND_MSG_PUB.Count_And_Get
2428 ( p_count => x_msg_count,
2429 p_data => x_msg_data,
2430 p_encoded => fnd_api.g_false);
2431
2432 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2433 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2434 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group',
2435 'End of Modify_Item_group...x_return_status => '||x_return_status);
2436 END IF;
2437
2438
2439 EXCEPTION
2440 WHEN FND_API.G_EXC_ERROR THEN
2441 x_return_status := FND_API.G_RET_STS_ERROR;
2442 Rollback to Modify_Item_group_pvt;
2443 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2444 p_data => x_msg_data,
2445 p_encoded => fnd_api.g_false);
2446 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
2447
2448 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2449 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2450 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Error in Modify_Item_group');
2451 END IF;
2452
2453
2454 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2456 Rollback to Modify_Item_group_pvt;
2457 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2458 p_data => x_msg_data,
2459 p_encoded => fnd_api.g_false);
2460 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
2461 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2462 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2463 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Unexpected error in Modify_Item_group');
2464 END IF;
2465
2466
2467 WHEN OTHERS THEN
2468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2469 Rollback to Modify_Item_group_pvt;
2470 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2471 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2472 p_procedure_name => 'Modify_Item_Group',
2473 p_error_text => SUBSTR(SQLERRM,1,240));
2474 END IF;
2475 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2476 p_data => x_msg_data,
2477 p_encoded => fnd_api.g_false);
2478 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
2479
2480 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2481 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2482 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Unknown Error in Modify_Item_group');
2483 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2484 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', SQLERRM);
2485 END IF;
2486
2487
2488 END Modify_Item_group;
2489
2490
2491 -- Start of Comments --
2492 -- Procedure name : Remove_Item_group
2493 -- Type : Private
2494 -- Function : Deletes an Item Group and associated item associations.
2495 -- Pre-reqs :
2496 -- Parameters :
2497 -- Standard IN Parameters :
2498 PROCEDURE Remove_Item_group(p_api_version IN NUMBER,
2499 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2500 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2501 x_return_status OUT NOCOPY VARCHAR2,
2502 x_msg_count OUT NOCOPY NUMBER,
2503 x_msg_data OUT NOCOPY VARCHAR2,
2504 p_item_group_rec IN AHL_MC_ITEMGROUP_PVT.Item_Group_Rec_Type
2505 ) IS
2506
2507 l_api_name CONSTANT VARCHAR2(30) := 'Remove_Item_Group';
2508 l_api_version CONSTANT NUMBER := 1.0;
2509
2510
2511 CURSOR Item_group_csr(p_item_group_id IN NUMBER) IS
2512 SELECT
2513 b.ROWID ROW_ID,
2514 b.ITEM_GROUP_ID,
2515 b.OBJECT_VERSION_NUMBER,
2516 b.NAME,
2517 b.Status_Code,
2518 b.source_item_group_id
2519 FROM
2520 AHL_ITEM_GROUPS_B b
2521 WHERE
2522 b.ITEM_GROUP_ID = p_item_group_id
2523 FOR UPDATE OF b.OBJECT_VERSION_NUMBER NOWAIT;
2524
2525 /*
2526 CURSOR ahl_relationships_csr(p_item_group_id IN NUMBER) IS
2527 SELECT 'x'
2528 from ahl_mc_relationships_v posn, ahl_mc_relationships_v topnode
2529 where trunc(nvl(posn.active_end_date,sysdate+1)) > trunc(sysdate)
2530 and posn.item_group_id = p_item_group_id
2531 and topnode.relationship_id = (SELECT reln.relationship_id from
2532 ahl_mc_relationships reln
2533 where parent_relationship_id is null
2534 start with relationship_id = posn.relationship_id
2535 and trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate)
2536 connect by prior parent_relationship_id = relationship_id
2537 and trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate)
2538 );
2539 */
2540
2541 CURSOR ahl_relationships_csr(p_item_group_id IN NUMBER) IS
2542 SELECT 'x'
2543 FROM ahl_mc_relationships
2544 WHERE trunc(nvl(active_end_date, sysdate + 1)) > trunc(sysdate)
2545 AND item_group_id = p_item_group_id;
2546
2547 CURSOR ahl_item_comp_csr(p_item_group_id IN NUMBER) IS
2548 SELECT 'x'
2549 FROM ahl_item_comp_details
2550 WHERE trunc(nvl(effective_end_date, sysdate + 1)) > trunc(sysdate)
2551 AND item_group_id = p_item_group_id;
2552
2553 l_item_group_rec Item_group_csr%ROWTYPE;
2554 l_dummy VARCHAR2(1);
2555
2556 BEGIN
2557
2558 -- Standard start of API savepoint
2559 SAVEPOINT Remove_Item_group_Pvt;
2560
2561 -- Standard call to check for call compatibility
2562 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
2563 G_PKG_NAME) THEN
2564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2565 END IF;
2566
2567 -- Initialize message list if p_init_msg_list is set to TRUE
2568 IF FND_API.To_Boolean(p_init_msg_list) THEN
2569 FND_MSG_PUB.Initialize;
2570 END IF;
2571
2572 -- Initialize API return status to success
2573 x_return_status := FND_API.G_RET_STS_SUCCESS;
2574
2575 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2576 THEN
2577 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2578 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'Begin of Remove_Item_group');
2579 END IF;
2580
2581
2582 -- Validate Item Group record.
2583 OPEN Item_group_csr(p_item_group_rec.item_group_id);
2584 FETCH Item_group_csr INTO l_item_group_rec;
2585 IF (Item_group_csr%NOTFOUND) THEN
2586 CLOSE Item_group_csr;
2587 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_INVALID');
2588 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_group_rec.name);
2589 FND_MSG_PUB.ADD;
2590 --dbms_output.put_line('Item Group does not exist');
2591 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2592 END IF;
2593
2594 -- Check Object version number.
2595 IF (l_item_group_rec.object_version_number <> p_item_group_rec.object_version_number) THEN
2596 CLOSE Item_group_csr;
2597 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
2598 FND_MSG_PUB.ADD;
2599 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2600 END IF;
2601
2602 -- Check if this item group has any positions associated.
2603 OPEN ahl_relationships_csr(p_item_group_rec.item_group_id);
2604 FETCH ahl_relationships_csr INTO l_dummy;
2605 IF (ahl_relationships_csr%FOUND) THEN
2606 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_POSN_EXISTS');
2607 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_group_rec.name);
2608 FND_MSG_PUB.ADD;
2609 --dbms_output.put_line('Positions exist for this item group');
2610 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2611 END IF;
2612
2613 CLOSE ahl_relationships_csr;
2614
2615 -- Check if this item group has any composition associated.
2616 OPEN ahl_item_comp_csr(p_item_group_rec.item_group_id);
2617 FETCH ahl_item_comp_csr INTO l_dummy;
2618 IF (ahl_item_comp_csr%FOUND) THEN
2619 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_COMP_EXISTS');
2620 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_group_rec.name);
2621 FND_MSG_PUB.ADD;
2622 --dbms_output.put_line('Positions exist for this item group');
2623 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2624 END IF;
2625
2626 CLOSE ahl_item_comp_csr;
2627
2628
2629 -- Coded for 11.5.10
2630
2631 IF l_item_group_rec.status_code ='APPROVAL_PENDING'
2632 THEN
2633 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_APPR_PEND');
2634 FND_MSG_PUB.ADD;
2635 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2636 END IF;
2637
2638
2639 IF l_item_group_rec.status_code ='REMOVED'
2640 THEN
2641 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_REMOVED');
2642 FND_MSG_PUB.ADD;
2643 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2644 END IF;
2645
2646
2647 IF l_item_group_rec.status_code IN ('DRAFT' ,'APPROVAL_REJECTED')
2648 THEN
2649
2650 IF (l_item_group_rec.status_code = 'DRAFT' and nvl(l_item_group_rec.source_item_group_id, 0) > 0)
2651 THEN
2652 UPDATE ahl_mc_relationships
2653 SET temp_item_group_id = null
2654 WHERE item_group_id = p_item_group_rec.item_group_id;
2655 END IF;
2656
2657 -- Delete item associations.
2658 /* AHL_ITEM_ASSOCIATIONS_PKG.DELETE_ROW(
2659 X_ITEM_ASSOCIATION_ID => p_item_group_rec.item_group_id
2660 );
2661
2662 */
2663
2664 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2665 THEN
2666 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2667 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'Deleting Item Group');
2668 END IF;
2669
2670 DELETE AHL_ITEM_ASSOCIATIONS_TL
2671 WHERE item_association_id IN ( SELECT item_association_id
2672 FROM ahl_item_associations_b
2673 WHERE item_group_id = p_item_group_rec.item_group_id );
2674
2675 DELETE AHL_ITEM_ASSOCIATIONS_B
2676 WHERE item_group_id = p_item_group_rec.item_group_id;
2677
2678 -- Delete ahl_item_groups
2679 AHL_ITEM_GROUPS_PKG.DELETE_ROW(
2680 X_ITEM_GROUP_ID => p_item_group_rec.item_group_id
2681 );
2682
2683 ELSIF l_item_group_rec.status_code ='COMPLETE'
2684 THEN
2685
2686 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2687 THEN
2688 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2689 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'Updating Item Group to Removed');
2690 END IF;
2691
2692 Update Ahl_Item_groups_b
2693 set status_code ='REMOVED',
2694 object_version_number = object_version_number +1
2695 Where item_group_id = p_item_group_rec.item_group_id;
2696
2697 --Update Ahl_item_associations_b
2698 -- set INTERCHANGE_TYPE_CODE = 'REMOVED',
2699 -- object_version_number = object_version_number +1
2700 -- Where item_group_id = p_item_group_rec.item_group_id;
2701
2702 END IF;
2703
2704 CLOSE Item_group_csr;
2705
2706 -- Standard call to get message count and if count is 1, get message info
2707 FND_MSG_PUB.Count_And_Get
2708 ( p_count => x_msg_count,
2709 p_data => x_msg_data,
2710 p_encoded => fnd_api.g_false);
2711
2712 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2713 THEN
2714 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2715 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'End of Remove_Item_group');
2716 END IF;
2717
2718
2719
2720 EXCEPTION
2721 WHEN FND_API.G_EXC_ERROR THEN
2722 x_return_status := FND_API.G_RET_STS_ERROR;
2723 Rollback to Remove_Item_group_pvt;
2724 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2725 p_data => x_msg_data,
2726 p_encoded => fnd_api.g_false);
2727
2728 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2729 THEN
2730 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2731 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'Error in Remove_Item_group');
2732 END IF;
2733
2734 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2736 Rollback to Remove_Item_group_pvt;
2737 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2738 p_data => x_msg_data,
2739 p_encoded => fnd_api.g_false);
2740 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2741 THEN
2742 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2743 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'Unexpected error in Remove_Item_group');
2744 END IF;
2745
2746
2747 WHEN OTHERS THEN
2748 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2749 Rollback to Remove_Item_group_pvt;
2750 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2751 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2752 p_procedure_name => 'Remove_Item_Group',
2753 p_error_text => SUBSTR(SQLERRM,1,240));
2754 END IF;
2755 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2756 p_data => x_msg_data,
2757 p_encoded => fnd_api.g_false);
2758 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2759 THEN
2760 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2761 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'Unknown error in Remove_Item_group');
2762 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2763 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', SQLERRM);
2764 END IF;
2765
2766
2767 END Remove_Item_group;
2768
2769 -- Start of Comments --
2770 -- Procedure name : Initiate_Itemgroup_Appr
2771 -- Type : Private
2772 -- Function : Intiates Approval Process for Item groups
2773 --
2774 -- Pre-reqs :
2775 -- Parameters :
2776 -- Standard IN Parameters :
2777 -- p_api_version IN NUMBER Required
2778 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2779 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2780 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2781 --
2782 -- Standard OUT Parameters :
2783 -- x_return_status OUT VARCHAR2 Required
2784 -- x_msg_count OUT NUMBER Required
2785 -- x_msg_data OUT VARCHAR2 Required
2786 --
2787 -- Source_Item_Group_id Required.
2788 -- Object_version_number Required.
2789 -- Approval type Required.
2790 --
2791 -- Enhancement 115.10
2792 -- End of Comments --
2793 PROCEDURE Initiate_Itemgroup_Appr (
2794 p_api_version IN NUMBER,
2795 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2796 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2797 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2798 p_default IN VARCHAR2 := FND_API.G_FALSE,
2799 p_module_type IN VARCHAR2,
2800 x_return_status OUT NOCOPY VARCHAR2,
2801 x_msg_count OUT NOCOPY NUMBER,
2802 x_msg_data OUT NOCOPY VARCHAR2,
2803 p_source_item_group_id IN NUMBER,
2804 p_object_version_number IN NUMBER,
2805 p_approval_type IN VARCHAR2
2806 )
2807 IS
2808
2809 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_Itemgroup_Appr';
2810 l_api_version CONSTANT NUMBER := 1.0;
2811
2812 l_counter NUMBER:=0;
2813 l_object VARCHAR2(30):='IGWF';
2814 l_approval_type VARCHAR2(100):='CONCEPT';
2815 l_active VARCHAR2(50) := 'N';
2816 l_process_name VARCHAR2(50);
2817 l_item_type VARCHAR2(50);
2818 l_return_status VARCHAR2(50);
2819 l_msg_count NUMBER;
2820 l_msg_data VARCHAR2(2000);
2821 l_activity_id NUMBER:=p_source_item_group_id;
2822 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
2823 l_object_Version_number NUMBER:=nvl(p_object_Version_number,0);
2824
2825 l_upd_status VARCHAR2(50);
2826 l_rev_status VARCHAR2(50);
2827
2828
2829
2830 CURSOR get_Itemgroup_Det(c_itemgroup_id NUMBER)
2831 is
2832 Select Name,
2833 Status_code,
2834 Object_version_number,
2835 source_item_group_id
2836 From ahl_item_groups_vl
2837 Where item_group_id = c_itemgroup_id;
2838
2839 CURSOR validate_item_group(c_itemgroup_id NUMBER)
2840 is
2841 Select 'x' from dual
2842 Where exists ( select item_association_id from ahl_item_associations_vl
2843 Where Item_Group_id = c_itemgroup_id and
2844 INTERCHANGE_TYPE_CODE in ('1-WAY INTERCHANGEABLE','2-WAY INTERCHANGEABLE') );
2845
2846 CURSOR validate_item_group_positions(c_itemgroup_id NUMBER)
2847 is
2848 select 'x'
2849 from ahl_mc_relationships a, ahl_mc_relationships b
2850 where b.RELATIONSHIP_ID = a.PARENT_RELATIONSHIP_ID
2851 and a.ITEM_GROUP_ID = c_itemgroup_id
2852 and exists
2853 ( select 'x'
2854 from ahl_item_associations_b
2855 where item_group_id = c_itemgroup_id
2856 and quantity > 1 );
2857
2858
2859 CURSOR Item_group_name(p_name VARCHAR2) IS
2860 select 'x'
2861 from ahl_item_groups_b
2862 where name = p_name and
2863 p_source_item_group_id <> item_group_id;
2864
2865
2866 l_itemgroup_rec get_Itemgroup_Det%rowtype;
2867
2868
2869 l_msg VARCHAR2(30);
2870 l_dummy VARCHAR2(1);
2871
2872 l_appr_status VARCHAR2(30) :='APPROVED';
2873 l_fork_or_merge NUMBER;
2874
2875
2876 BEGIN
2877 SAVEPOINT Initiate_Itemgroup_Appr;
2878
2879 -- Standard call to check for call compatibility.
2880
2881 IF FND_API.to_boolean(l_init_msg_list) THEN
2882 FND_MSG_PUB.initialize;
2883 END IF;
2884
2885 -- Initialize API return status to success
2886
2887 x_return_status :=FND_API.G_RET_STS_SUCCESS;
2888
2889 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2890 THEN
2891 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2892 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Begin Initiate_Itemgroup_Appr');
2893 END IF;
2894
2895
2896
2897
2898 -- Start work Flow Process
2899 ahl_utility_pvt.get_wf_process_name(
2900 p_object =>l_object,
2901 x_active =>l_active,
2902 x_process_name =>l_process_name ,
2903 x_item_type =>l_item_type,
2904 x_return_status=>l_return_status,
2905 x_msg_count =>l_msg_count,
2906 x_msg_data =>l_msg_data);
2907
2908 IF p_object_Version_number is null or p_object_Version_number=FND_API.G_MISS_NUM
2909 THEN
2910 FND_MESSAGE.SET_NAME('AHL','AHL_MC_OBJ_VERSION_NULL');
2911 FND_MSG_PUB.ADD;
2912 END IF;
2913
2914 IF p_source_item_group_id is null or p_source_item_group_id = FND_API.G_MISS_NUM
2915 THEN
2916 FND_MESSAGE.SET_NAME('AHL','AHL_MC_OBJECT_ID_NULL');
2917 FND_MSG_PUB.ADD;
2918 ELSE
2919 OPEN get_Itemgroup_Det(p_source_item_group_id);
2920 FETCH get_Itemgroup_Det INTO l_itemgroup_rec;
2921 CLOSE get_Itemgroup_Det;
2922
2923 IF l_itemgroup_rec.source_item_group_id IS NOT NULL
2924 AND Fork_Or_Merge(p_source_item_group_id) = 0
2925 THEN
2926 OPEN Item_group_name(l_itemgroup_rec.name);
2927 FETCH Item_group_name INTO l_dummy;
2928 IF Item_group_name%FOUND
2929 THEN
2930 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IG_MOD_NAME');
2931 FND_MSG_PUB.ADD;
2932 CLOSE Item_group_name;
2933 RAISE FND_API.G_EXC_ERROR;
2934 END IF;
2935 CLOSE Item_group_name;
2936 END IF;
2937
2938
2939 IF p_approval_type = 'APPROVE'
2940 THEN
2941 IF l_itemgroup_rec.status_code='DRAFT' or
2942 l_itemgroup_rec.status_code='APPROVAL_REJECTED'
2943 THEN
2944 l_upd_status := 'APPROVAL_PENDING';
2945 l_fork_or_merge := Fork_Or_Merge(p_source_item_group_id);
2946
2947 IF (l_fork_or_merge = 0)
2948 THEN
2949 Validate_Item_Group_Name(l_itemgroup_rec.name, p_source_item_group_id, null);
2950 ELSE
2951 Validate_Item_Group_Name(l_itemgroup_rec.name, p_source_item_group_id, l_itemgroup_rec.source_item_group_id);
2952 END IF;
2953
2954 ELSE
2955 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IG_STAT_NOT_DRFT');
2956 FND_MESSAGE.set_token('IG',l_itemgroup_rec.name,false);
2957 FND_MSG_PUB.ADD;
2958 END IF;
2959 ELSE
2960 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_TYPE_CODE_MISSING');
2961 FND_MSG_PUB.ADD;
2962 END IF;
2963
2964 OPEN validate_item_group(p_source_item_group_id);
2965 FETCH validate_item_group INTO l_dummy;
2966 IF validate_item_group%NOTFOUND THEN
2967 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IG_INTERCHANGE_INAVLID');
2968 FND_MSG_PUB.ADD;
2969 END IF;
2970 CLOSE validate_item_group;
2971
2972 OPEN validate_item_group_positions(p_source_item_group_id);
2973 FETCH validate_item_group_positions INTO l_dummy;
2974 IF validate_item_group_positions%FOUND THEN
2975 FND_MESSAGE.SET_NAME('AHL','AHL_MC_PAR_QTY_INV');
2976 FND_MESSAGE.set_token('POSREF','');
2977 FND_MSG_PUB.ADD;
2978 END IF;
2979 CLOSE validate_item_group_positions;
2980
2981
2982
2983 END IF;
2984
2985 l_msg_count := FND_MSG_PUB.count_msg;
2986
2987 IF l_msg_count > 0
2988 THEN
2989 X_msg_count := l_msg_count;
2990 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2991 RAISE FND_API.G_EXC_ERROR;
2992 END IF;
2993
2994
2995 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2996 THEN
2997 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2998 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'l_active flag is yes');
2999 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3000 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Updating Item group');
3001 END IF;
3002
3003 Update AHL_ITEM_GROUPS_B
3004 Set STATUS_CODE=l_upd_status,
3005 OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
3006 Where ITEM_GROUP_ID = p_source_item_group_id
3007 and OBJECT_VERSION_NUMBER=p_object_Version_number;
3008
3009 IF sql%rowcount=0
3010 THEN
3011 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
3012 FND_MSG_PUB.ADD;
3013 RAISE FND_API.G_EXC_ERROR;
3014 END IF;
3015
3016 IF l_ACTIVE='Y'
3017 THEN
3018
3019
3020 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3021 THEN
3022 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3023 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Calling ahl_generic_aprv_pvt.start_wf_process');
3024 END IF;
3025
3026 AHL_GENERIC_APRV_PVT.START_WF_PROCESS(
3027 P_OBJECT =>l_object,
3028 P_ACTIVITY_ID =>l_activity_id,
3029 P_APPROVAL_TYPE =>'CONCEPT',
3030 P_OBJECT_VERSION_NUMBER =>l_object_version_number+1,
3031 P_ORIG_STATUS_CODE =>'ACTIVE',
3032 P_NEW_STATUS_CODE =>'APPROVED',
3033 P_REJECT_STATUS_CODE =>'REJECTED',
3034 P_REQUESTER_USERID =>fnd_global.user_id,
3035 P_NOTES_FROM_REQUESTER =>NULL,
3036 P_WORKFLOWPROCESS =>'AHL_GEN_APPROVAL',
3037 P_ITEM_TYPE =>'AHLGAPP');
3038 ELSE
3039
3040 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3041 THEN
3042 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3043 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Calling ahl_mc_itemgroup_pvt.Approve_ItemGroups');
3044 END IF;
3045
3046 AHL_MC_ITEMGROUP_PVT.Approve_ItemGroups
3047 (
3048 p_api_version =>l_api_version,
3049 -- p_init_msg_list =>l_init_msg_list,
3050 -- p_commit =>l_commit,
3051 -- p_validation_level =>NULL ,
3052 -- p_default =>NULL ,
3053 p_module_type =>NULL,
3054 x_return_status =>l_return_status,
3055 x_msg_count =>l_msg_count ,
3056 x_msg_data =>l_msg_data ,
3057 p_appr_status =>l_appr_status,
3058 p_ItemGroups_id =>p_source_item_group_id,
3059 p_object_version_number =>p_object_Version_number+1
3060 );
3061 END IF ;
3062
3063 l_msg_count := FND_MSG_PUB.count_msg;
3064
3065 IF l_msg_count > 0
3066 THEN
3067 X_msg_count := l_msg_count;
3068 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3069 RAISE FND_API.G_EXC_ERROR;
3070 END IF;
3071
3072
3073 -- Standard check of p_commit
3074 IF FND_API.TO_BOOLEAN(p_commit) THEN
3075 COMMIT WORK;
3076 END IF;
3077
3078 -- Standard call to get message count and if count is 1, get message info
3079 FND_MSG_PUB.Count_And_Get
3080 ( p_count => x_msg_count,
3081 p_data => x_msg_data,
3082 p_encoded => fnd_api.g_false);
3083
3084 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3085 THEN
3086 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3087 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'End of Initiate_Itemgroup_Appr');
3088 END IF;
3089
3090
3091 EXCEPTION
3092 WHEN FND_API.G_EXC_ERROR THEN
3093 x_return_status := FND_API.G_RET_STS_ERROR;
3094 Rollback to Initiate_Itemgroup_Appr;
3095 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3096 p_data => x_msg_data,
3097 p_encoded => fnd_api.g_false);
3098 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3099 THEN
3100 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3101 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Error in Initiate_Itemgroup_Appr');
3102 END IF;
3103
3104
3105
3106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3107 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3108 Rollback to Initiate_Itemgroup_Appr;
3109 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3110 p_data => x_msg_data,
3111 p_encoded => fnd_api.g_false);
3112 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3113 THEN
3114 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3115 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Unexpected Error in Initiate_Itemgroup_Appr');
3116 END IF;
3117
3118
3119 WHEN OTHERS THEN
3120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3121 Rollback to Initiate_Itemgroup_Appr;
3122 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3123 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3124 p_procedure_name => 'Initiate_Itemgroup_Appr',
3125 p_error_text => SUBSTR(SQLERRM,1,240));
3126 END IF;
3127 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3128 p_data => x_msg_data,
3129 p_encoded => fnd_api.g_false);
3130
3131 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3132 THEN
3133 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3134 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Unknown Error in Initiate_Itemgroup_Appr');
3135 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3136 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', SQLERRM);
3137 END IF;
3138
3139 END Initiate_Itemgroup_Appr;
3140
3141
3142 -- Start of Comments --
3143 -- Procedure name : Create_ItemGroup_Revision
3144 -- Type : Private
3145 -- Function : To create a New Revision of Item group
3146 --
3147 -- Pre-reqs :
3148 -- Parameters :
3149 -- Standard IN Parameters :
3150 -- p_api_version IN NUMBER Required
3151 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3152 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3153 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3154 --
3155 -- Standard OUT Parameters :
3156 -- x_return_status OUT VARCHAR2 Required
3157 -- x_msg_count OUT NUMBER Required
3158 -- x_msg_data OUT VARCHAR2 Required
3159
3160 -- Source_Item_Group_id Required.
3161 -- Object_version_number Required.
3162 -- Enhancement 115.10
3163 --
3164 -- End of Comments --
3165
3166 PROCEDURE Create_ItemGroup_Revision (
3167 p_api_version IN NUMBER,
3168 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3169 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3170 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3171 P_DEFAULT IN VARCHAR2 := FND_API.G_FALSE,
3172 P_MODULE_TYPE IN VARCHAR2,
3173 x_return_status OUT NOCOPY VARCHAR2,
3174 x_msg_count OUT NOCOPY NUMBER,
3175 x_msg_data OUT NOCOPY VARCHAR2,
3176 p_source_ItemGroup_id IN NUMBER,
3177 p_object_version_number IN NUMBER,
3178 x_ItemGroup_id OUT NOCOPY NUMBER
3179 ) AS
3180
3181 cursor get_itemgroup_det(c_itemgroup_id in Number)
3182 Is
3183 Select
3184 Name,
3185 Status_Code,
3186 Type_Code,
3187 Description,
3188 object_version_number ,
3189 attribute_category ,
3190 attribute1 ,
3191 attribute2 ,
3192 attribute3 ,
3193 attribute4 ,
3194 attribute5 ,
3195 attribute6 ,
3196 attribute7 ,
3197 attribute8 ,
3198 attribute9 ,
3199 attribute10 ,
3200 attribute11 ,
3201 attribute12 ,
3202 attribute13 ,
3203 attribute14 ,
3204 attribute15
3205 from ahl_item_groups_vl
3206 Where Item_Group_id = c_itemgroup_id;
3207
3208 l_itemgroups_det get_itemgroup_det%rowtype;
3209
3210 cursor get_itemgroup_assos_det(c_itemgroup_id in Number)
3211 Is
3212 Select
3213 item_association_id ,
3214 object_version_number ,
3215 item_group_id ,
3216 inventory_item_id ,
3217 inventory_org_id ,
3218 priority ,
3219 uom_code ,
3220 quantity ,
3221 revision ,
3222 interchange_type_code ,
3223 interchange_reason ,
3224 source_item_association_id,
3225 attribute_category ,
3226 attribute1 ,
3227 attribute2 ,
3228 attribute3 ,
3229 attribute4 ,
3230 attribute5 ,
3231 attribute6 ,
3232 attribute7 ,
3233 attribute8 ,
3234 attribute9 ,
3235 attribute10 ,
3236 attribute11 ,
3237 attribute12 ,
3238 attribute13 ,
3239 attribute14 ,
3240 attribute15
3241 from ahl_item_associations_vl
3242 where item_group_id = c_itemgroup_id;
3243
3244
3245 cursor get_revision_info(c_itemgroup_id in Number)
3246 is
3247 Select 'x'
3248 from ahl_item_groups_vl
3249 where source_item_group_id = c_itemgroup_id and
3250 status_code <> 'COMPLETE';
3251
3252 l_dummy VARCHAR2(1);
3253 l_msg_count Number;
3254 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
3255 l_itemgroup_det get_itemgroup_det%rowtype;
3256 l_item_group_id Number;
3257 l_last_update_login NUMBER;
3258 l_last_updated_by NUMBER;
3259 l_rowid VARCHAR2(30);
3260 l_item_association_id NUMBER;
3261 l_created_by NUMBER;
3262
3263 -- TAMAL -- IG Amendments --
3264 CURSOR get_mc_posisions (c_item_group_id in number)
3265 IS
3266 SELECT relationship_id, object_version_number
3267 FROM ahl_mc_relationships
3268 WHERE item_group_id = c_item_group_id;
3269 -- TAMAL -- IG Amendments --
3270
3271 BEGIN
3272
3273
3274 SAVEPOINT Create_ItemGroup_Revision;
3275
3276 -- Standard call to check for call compatibility.
3277
3278 IF FND_API.to_boolean(l_init_msg_list) THEN
3279 FND_MSG_PUB.initialize;
3280 END IF;
3281
3282 -- Initialize API return status to success
3283
3284 x_return_status :=FND_API.G_RET_STS_SUCCESS;
3285
3286 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3287 THEN
3288 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3289 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'Begin of Create_ItemGroup_Revision');
3290 END IF;
3291
3292
3293
3294 OPEN get_itemgroup_det(p_source_ItemGroup_id);
3295 Fetch get_itemgroup_det into l_itemgroup_det;
3296 IF get_itemgroup_det%NOTFOUND THEN
3297 FND_MESSAGE.SET_NAME('AHL','AHL_MC_OBJECT_ID_NULL');
3298 FND_MSG_PUB.ADD;
3299 END IF;
3300 close get_itemgroup_det;
3301
3302
3303 IF l_itemgroup_det.Status_Code <> 'COMPLETE'
3304 THEN
3305 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IG_STAT_NOT_COMP');
3306 FND_MESSAGE.Set_Token('IG',l_itemgroup_det.name);
3307 FND_MSG_PUB.ADD;
3308 END IF;
3309
3310
3311 IF l_itemgroup_det.object_version_number <> p_object_version_number
3312 THEN
3313 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD');
3314 FND_MSG_PUB.ADD;
3315 END IF;
3316
3317
3318 OPEN get_revision_info(p_source_ItemGroup_id);
3319 FETCH get_revision_info INTO l_dummy;
3320 IF get_revision_info%FOUND THEN
3321 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IG_REVISION_EXIST');
3322 FND_MSG_PUB.ADD;
3323 END IF;
3324 CLOSE get_revision_info;
3325
3326
3327
3328 l_msg_count := FND_MSG_PUB.count_msg;
3329
3330 IF l_msg_count > 0
3331 THEN
3332 X_msg_count := l_msg_count;
3333 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3334 RAISE FND_API.G_EXC_ERROR;
3335 END IF;
3336
3337 -- Sequence Number for the New Revision.
3338
3339 Select AHL_ITEM_GROUPS_B_S.nextval
3340 into l_item_group_id
3341 from dual;
3342
3343 l_last_updated_by := to_number(fnd_global.USER_ID);
3344 l_last_update_login := to_number(fnd_global.LOGIN_ID);
3345 l_created_by := to_number(fnd_global.user_id);
3346
3347
3348 -- Inserting a new Revision in the Header Table Using table Handler
3349
3350 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3351 THEN
3352 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3353 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'Calling Table handler');
3354 END IF;
3355
3356
3357 ahl_item_groups_pkg.insert_row(
3358 x_rowid => l_rowid,
3359 x_item_group_id => l_item_group_id,
3360 x_type_code => l_itemgroup_det.type_code,
3361 x_status_code => 'DRAFT',
3362 x_source_item_group_id => p_source_ItemGroup_id,
3363 x_object_version_number => 1,
3364 x_name => l_itemgroup_det.name,
3365 x_attribute_category => l_itemgroup_det.attribute_category,
3366 x_attribute1 => l_itemgroup_det.attribute1,
3367 x_attribute2 => l_itemgroup_det.attribute2,
3368 x_attribute3 => l_itemgroup_det.attribute3,
3369 x_attribute4 => l_itemgroup_det.attribute4,
3370 x_attribute5 => l_itemgroup_det.attribute5,
3371 x_attribute6 => l_itemgroup_det.attribute6,
3372 x_attribute7 => l_itemgroup_det.attribute7,
3373 x_attribute8 => l_itemgroup_det.attribute8,
3374 x_attribute9 => l_itemgroup_det.attribute9,
3375 x_attribute10 => l_itemgroup_det.attribute10,
3376 x_attribute11 => l_itemgroup_det.attribute11,
3377 x_attribute12 => l_itemgroup_det.attribute12,
3378 x_attribute13 => l_itemgroup_det.attribute13,
3379 x_attribute14 => l_itemgroup_det.attribute14,
3380 x_attribute15 => l_itemgroup_det.attribute15,
3381 x_description => l_itemgroup_det.description,
3382 x_creation_date => sysdate,
3383 x_created_by => l_created_by,
3384 x_last_update_date => sysdate,
3385 x_last_updated_by => l_last_updated_by,
3386 x_last_update_login => l_last_update_login);
3387
3388
3389 x_ItemGroup_id := l_item_group_id;
3390
3391 -- TAMAL -- IG Amendments --
3392 FOR item_group_rec IN get_mc_posisions(p_source_itemgroup_id)
3393 LOOP
3394 UPDATE ahl_mc_relationships
3395 SET temp_item_group_id = x_itemgroup_id,
3396 object_version_number = item_group_rec.object_version_number,
3397 last_update_date = sysdate,
3398 last_updated_by = l_last_updated_by,
3399 last_update_login = l_last_update_login
3400 WHERE relationship_id = item_group_rec.relationship_id and
3401 trunc(nvl(active_end_date, sysdate + 1)) > trunc(sysdate);
3402
3403 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3404 THEN
3405 FND_LOG.STRING
3406 (
3407 FND_LOG.LEVEL_PROCEDURE,
3408 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision',
3409 'Updated MC position '||item_group_rec.relationship_id||' with temp_item_group_id '||x_itemgroup_id
3410 );
3411 END IF;
3412 END LOOP;
3413 -- TAMAL -- IG Amendments --
3414
3415 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3416 THEN
3417 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3418 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'Loop to create Item Association');
3419 END IF;
3420
3421 FOR I IN get_itemgroup_assos_det(p_source_ItemGroup_id)
3422 LOOP
3423
3424 --Gets the sequence Number
3425 SELECT AHL_ITEM_ASSOCIATIONS_B_S.nextval INTO
3426 l_item_association_id from DUAL;
3427
3428
3429 ahl_item_associations_pkg.insert_row
3430 (
3431 x_rowid => l_rowid,
3432 x_item_association_id => l_item_association_id,
3433 x_source_item_association_id => I.source_item_association_id,
3434 x_object_version_number => 1,
3435 x_item_group_id => l_item_group_id,
3436 x_inventory_item_id => I.inventory_item_id,
3437 x_inventory_org_id => I.INVENTORY_ORG_ID ,
3438 x_priority => I.PRIORITY ,
3439 x_uom_code => I.UOM_CODE ,
3440 x_quantity => I.QUANTITY ,
3441 x_revision => I.REVISION ,
3442 x_interchange_type_code => I.INTERCHANGE_TYPE_CODE ,
3443 x_item_type_code => null,
3444 x_attribute_category => I.ATTRIBUTE_CATEGORY,
3445 x_attribute1 => i.attribute1,
3446 x_attribute2 => i.attribute2,
3447 x_attribute3 => i.attribute3,
3448 x_attribute4 => i.attribute4,
3449 x_attribute5 => i.attribute5,
3450 x_attribute6 => i.attribute6,
3451 x_attribute7 => i.attribute7,
3452 x_attribute8 => i.attribute8,
3453 x_attribute9 => i.attribute9,
3454 x_attribute10 => i.attribute10,
3455 x_attribute11 => i.attribute11,
3456 x_attribute12 => i.attribute12,
3457 x_attribute13 => i.attribute13,
3458 x_attribute14 => i.attribute14,
3459 x_attribute15 => i.attribute15,
3460 x_interchange_reason => I.INTERCHANGE_REASON,
3461 x_creation_date => sysdate,
3462 x_created_by => l_created_by,
3463 x_last_update_date => sysdate,
3464 x_last_updated_by => l_last_updated_by,
3465 x_last_update_login => l_last_update_login
3466 );
3467 END LOOP;
3468
3469 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3470 THEN
3471 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3472 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'End of Loop');
3473 END IF;
3474
3475 -- Standard check of p_commit
3476 IF FND_API.TO_BOOLEAN(p_commit) THEN
3477 COMMIT WORK;
3478 END IF;
3479
3480 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3481 THEN
3482 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3483 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'End of Create_ItemGroup_Revision');
3484 END IF;
3485
3486
3487
3488 EXCEPTION
3489 WHEN FND_API.G_EXC_ERROR THEN
3490 x_return_status := FND_API.G_RET_STS_ERROR;
3491 Rollback to Create_ItemGroup_Revision;
3492 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3493 p_data => x_msg_data,
3494 p_encoded => fnd_api.g_false);
3495 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3496 THEN
3497 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3498 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'Error in Create_ItemGroup_Revision');
3499 END IF;
3500
3501
3502 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3503 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3504 Rollback to Create_ItemGroup_Revision;
3505 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3506 p_data => x_msg_data,
3507 p_encoded => fnd_api.g_false);
3508 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3509 THEN
3510 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3511 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'Unecpected Error in Create_ItemGroup_Revision');
3512 END IF;
3513
3514
3515 WHEN OTHERS THEN
3516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3517 Rollback to Create_ItemGroup_Revision;
3518 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3519 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3520 p_procedure_name => 'Create_ItemGroup_Revision',
3521 p_error_text => SUBSTR(SQLERRM,1,240));
3522 END IF;
3523 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3524 p_data => x_msg_data,
3525 p_encoded => fnd_api.g_false);
3526 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3527 THEN
3528 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3529 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'Unknown Error in Create_ItemGroup_Revision');
3530 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3531 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision',SQLERRM );
3532
3533 END IF;
3534
3535
3536
3537 END Create_ItemGroup_Revision;
3538
3539
3540 PROCEDURE update_histroy (
3541 p_ItemGroups_id IN NUMBER,
3542 p_action IN VARCHAR2
3543 )IS
3544 --
3545 cursor get_item_assos_det_csr(c_itemgroup_id in number)
3546 is
3547 Select
3548 item_association_id ,
3549 object_version_number ,
3550 last_update_date ,
3551 last_updated_by ,
3552 creation_date ,
3553 created_by ,
3554 last_update_login ,
3555 item_group_id ,
3556 inventory_item_id ,
3557 inventory_org_id ,
3558 priority ,
3559 uom_code ,
3560 quantity ,
3561 revision ,
3562 interchange_type_code ,
3563 interchange_reason ,
3564 item_type_code ,
3565 source_item_association_id,
3566 attribute_category ,
3567 attribute1 ,
3568 attribute2 ,
3569 attribute3 ,
3570 attribute4 ,
3571 attribute5 ,
3572 attribute6 ,
3573 attribute7 ,
3574 attribute8 ,
3575 attribute9 ,
3576 attribute10 ,
3577 attribute11 ,
3578 attribute12 ,
3579 attribute13 ,
3580 attribute14 ,
3581 attribute15
3582 from ahl_item_associations_vl
3583 where item_group_id = c_itemgroup_id;
3584 --
3585
3586 l_version_number NUMBER;
3587 l_item_associations_h_id NUMBER;
3588 l_item_group_h_id NUMBER;
3589 l_rowid VARCHAR2(30);
3590 l_item_assos_det get_item_assos_det_csr%ROWTYPE;
3591
3592 --
3593 BEGIN
3594
3595 -- To get the maximum of version number
3596 Select NVl(max(VERSION_NUMBER),0)
3597 into l_version_number
3598 from ahl_item_groups_b_h
3599 where ITEM_GROUP_ID = p_ItemGroups_id;
3600
3601
3602 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3603 THEN
3604 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3605 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Inserting into History Tables');
3606 END IF;
3607
3608
3609 Select ahl_item_associations_b_h_s.nextval
3610 into l_item_associations_h_id from dual;
3611
3612 INSERT INTO ahl_item_groups_b_h
3613 (item_group_h_id ,
3614 item_group_id ,
3615 object_version_number,
3616 creation_date ,
3617 created_by ,
3618 last_update_date ,
3619 last_updated_by ,
3620 name ,
3621 type_code ,
3622 status_code ,
3623 version_number ,
3624 transaction_date ,
3625 action ,
3626 source_item_group_id ,
3627 last_update_login ,
3628 attribute_category ,
3629 attribute1 ,
3630 attribute2 ,
3631 attribute3 ,
3632 attribute4 ,
3633 attribute5 ,
3634 attribute6 ,
3635 attribute7 ,
3636 attribute8 ,
3637 attribute9 ,
3638 attribute10 ,
3639 attribute11 ,
3640 attribute12 ,
3641 attribute13 ,
3642 attribute14 ,
3643 attribute15 )
3644
3645 SELECT
3646
3647 AHL_ITEM_GROUPS_B_H_S.NEXTVAL ,
3648 item_group_id ,
3649 object_version_number,
3650 creation_date ,
3651 created_by ,
3652 last_update_date ,
3653 last_updated_by ,
3654 name ,
3655 type_code ,
3656 status_code ,
3657 l_version_number+1 ,
3658 sysdate ,
3659 p_action ,
3660 source_item_group_id ,
3661 last_update_login ,
3662 attribute_category ,
3663 attribute1 ,
3664 attribute2 ,
3665 attribute3 ,
3666 attribute4 ,
3667 attribute5 ,
3668 attribute6 ,
3669 attribute7 ,
3670 attribute8 ,
3671 attribute9 ,
3672 attribute10 ,
3673 attribute11 ,
3674 attribute12 ,
3675 attribute13 ,
3676 attribute14 ,
3677 attribute15
3678 FROM ahl_item_groups_b
3679 WHERE item_group_id = p_ItemGroups_id;
3680
3681
3682 INSERT INTO ahl_item_groups_tl_h
3683 (item_group_h_id ,
3684 language ,
3685 last_update_date ,
3686 last_updated_by ,
3687 source_lang ,
3688 creation_date ,
3689 created_by ,
3690 description ,
3691 last_update_login )
3692 SELECT
3693 AHL_ITEM_GROUPS_B_H_S.CURRVAL ,
3694 language ,
3695 last_update_date ,
3696 last_updated_by ,
3697 source_lang ,
3698 creation_date ,
3699 created_by ,
3700 description ,
3701 last_update_login
3702 FROM ahl_item_groups_tl
3703 WHERE item_group_id = p_ItemGroups_id;
3704
3705
3706
3707 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3708 THEN
3709 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3710 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Inserting Item Association into history table Start of Loop');
3711 END IF;
3712
3713
3714 FOR l_item_assos_det IN get_item_assos_det_csr(p_ItemGroups_id) LOOP
3715
3716 Select ahl_item_associations_b_h_s.nextval
3717 into l_item_associations_h_id from dual;
3718
3719 AHL_ITEM_ASSOCIATIONS_H_PKG.INSERT_ROW(
3720 X_ROWID => l_rowid,
3721 X_ITEM_ASSOCIATION_H_ID => l_item_associations_h_id ,
3722 X_ITEM_ASSOCIATION_ID => l_item_assos_det.ITEM_ASSOCIATION_ID ,
3723 X_ITEM_GROUP_ID => l_item_assos_det.ITEM_GROUP_ID ,
3724 X_OBJECT_VERSION_NUMBER => l_item_assos_det.OBJECT_VERSION_NUMBER ,
3725 X_INVENTORY_ITEM_ID => l_item_assos_det.INVENTORY_ITEM_ID ,
3726 X_INVENTORY_ORG_ID => l_item_assos_det.INVENTORY_ORG_ID ,
3727 X_PRIORITY => l_item_assos_det.PRIORITY ,
3728 X_TRANSACTION_DATE => sysdate,
3729 X_ACTION => p_action,
3730 X_SOURCE_ITEM_ASSOCIATION_ID => l_item_assos_det.SOURCE_ITEM_ASSOCIATION_ID,
3731 X_VERSION_NUMBER => l_version_number ,
3732 X_UOM_CODE => l_item_assos_det.UOM_CODE ,
3733 X_QUANTITY => l_item_assos_det.QUANTITY ,
3734 X_REVISION => l_item_assos_det.REVISION ,
3735 X_INTERCHANGE_TYPE_CODE => l_item_assos_det.INTERCHANGE_TYPE_CODE ,
3736 X_ATTRIBUTE_CATEGORY => l_item_assos_det.ATTRIBUTE_CATEGORY ,
3737 X_ATTRIBUTE1 => l_item_assos_det.ATTRIBUTE1 ,
3738 X_ATTRIBUTE2 => l_item_assos_det.ATTRIBUTE2 ,
3739 X_ATTRIBUTE3 => l_item_assos_det.ATTRIBUTE3 ,
3740 X_ATTRIBUTE4 => l_item_assos_det.ATTRIBUTE4 ,
3741 X_ATTRIBUTE5 => l_item_assos_det.ATTRIBUTE5 ,
3742 X_ATTRIBUTE6 => l_item_assos_det.ATTRIBUTE6 ,
3743 X_ATTRIBUTE7 => l_item_assos_det.ATTRIBUTE7 ,
3744 X_ATTRIBUTE8 => l_item_assos_det.ATTRIBUTE8 ,
3745 X_ATTRIBUTE9 => l_item_assos_det.ATTRIBUTE9 ,
3746 X_ATTRIBUTE10 => l_item_assos_det.ATTRIBUTE10 ,
3747 X_ATTRIBUTE11 => l_item_assos_det.ATTRIBUTE11 ,
3748 X_ATTRIBUTE12 => l_item_assos_det.ATTRIBUTE12 ,
3749 X_ATTRIBUTE13 => l_item_assos_det.ATTRIBUTE13 ,
3750 X_ATTRIBUTE14 => l_item_assos_det.ATTRIBUTE14 ,
3751 X_ATTRIBUTE15 => l_item_assos_det.ATTRIBUTE15 ,
3752 X_INTERCHANGE_REASON => l_item_assos_det.INTERCHANGE_REASON ,
3753 X_CREATION_DATE => l_item_assos_det.CREATION_DATE ,
3754 X_CREATED_BY => l_item_assos_det.CREATED_BY ,
3755 X_LAST_UPDATE_DATE => l_item_assos_det.LAST_UPDATE_DATE ,
3756 X_LAST_UPDATED_BY => l_item_assos_det.LAST_UPDATED_BY ,
3757 X_LAST_UPDATE_LOGIN => l_item_assos_det.LAST_UPDATE_LOGIN );
3758
3759 END LOOP;
3760
3761 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3762 THEN
3763 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3764 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'End of Loop');
3765 END IF;
3766
3767
3768 END update_histroy;
3769
3770
3771 -- Start of Comments --
3772 -- Procedure name : Approve_ItemGroups
3773 -- Type : Private
3774 -- Function : To Approve Item group will be called by approval package
3775 -- Version : Added for 115.10
3776 --
3777 -- Pre-reqs :
3778 -- Parameters :
3779 -- Standard IN Parameters :
3780 -- p_api_version IN NUMBER Required
3781 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3782 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3783 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3784 --
3785 -- Standard OUT Parameters :
3786 -- x_return_status OUT VARCHAR2 Required
3787 -- x_msg_count OUT NUMBER Required
3788 -- x_msg_data OUT VARCHAR2 Required
3789
3790 -- P_appr_status Required.
3791 -- Item_Group_id Required.
3792 -- Object_version_number Required.
3793 --
3794 --
3795 -- End of Comments --
3796
3797 PROCEDURE Approve_ItemGroups (
3798 p_api_version IN NUMBER,
3799 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3800 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3801 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3802 P_DEFAULT IN VARCHAR2 := FND_API.G_FALSE,
3803 P_MODULE_TYPE IN VARCHAR2,
3804 x_return_status OUT NOCOPY VARCHAR2,
3805 x_msg_count OUT NOCOPY NUMBER,
3806 x_msg_data OUT NOCOPY VARCHAR2,
3807 p_appr_status IN VARCHAR2,
3808 p_ItemGroups_id IN NUMBER,
3809 p_object_version_number IN NUMBER)
3810
3811 AS
3812
3813 cursor get_itemgroup_det(c_itemgroup_id in Number)
3814 Is Select
3815 item_group_id,
3816 Name,
3817 Status_Code,
3818 Type_Code,
3819 Source_Item_group_id,
3820 object_version_number,
3821 Description,
3822 attribute_category ,
3823 attribute1 ,
3824 attribute2 ,
3825 attribute3 ,
3826 attribute4 ,
3827 attribute5 ,
3828 attribute6 ,
3829 attribute7 ,
3830 attribute8 ,
3831 attribute9 ,
3832 attribute10 ,
3833 attribute11 ,
3834 attribute12 ,
3835 attribute13 ,
3836 attribute14 ,
3837 attribute15
3838 from ahl_item_groups_vl
3839 Where Item_Group_id = c_itemgroup_id;
3840
3841 l_itemgroup_det get_itemgroup_det%rowType;
3842
3843
3844 cursor get_itemgroup_assos_det(c_itemgroup_id in Number)
3845 Is
3846 Select
3847 item_association_id ,
3848 source_item_association_id ,
3849 object_version_number ,
3850 item_group_id ,
3851 inventory_item_id ,
3852 inventory_org_id ,
3853 priority ,
3854 uom_code ,
3855 quantity ,
3856 revision ,
3857 interchange_type_code ,
3858 interchange_reason ,
3859 source_item_association_id,
3860 attribute_category ,
3861 attribute1 ,
3862 attribute2 ,
3863 attribute3 ,
3864 attribute4 ,
3865 attribute5 ,
3866 attribute6 ,
3867 attribute7 ,
3868 attribute8 ,
3869 attribute9 ,
3870 attribute10 ,
3871 attribute11 ,
3872 attribute12 ,
3873 attribute13 ,
3874 attribute14 ,
3875 attribute15
3876 from ahl_item_associations_vl
3877 where item_group_id = c_itemgroup_id;
3878
3879
3880 l_status VARCHAR2(30);
3881 l_msg_count Number;
3882 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
3883 l_rowid VARCHAR2(30);
3884 l_action varchar2(2);
3885
3886 -- SATHAPLI::Bug# 4328454 fix
3887 -- The declared variables are not being used now as the call to
3888 -- AHL_UTIL_UC_PKG.Invalidate_Instance will not be made in this procedure.
3889 -- The validation of Item group updates for active UCs is now being done
3890 -- in procedure Modify_Item_group. Refer to old version of the package
3891 -- for details.
3892
3893 l_fork_or_merge NUMBER;
3894
3895 BEGIN
3896
3897 SAVEPOINT Approve_ItemGroups;
3898
3899
3900 -- Standard call to check for call compatibility.
3901
3902 IF FND_API.to_boolean(l_init_msg_list) THEN
3903 FND_MSG_PUB.initialize;
3904 END IF;
3905
3906 -- Initialize API return status to success
3907
3908 x_return_status :=FND_API.G_RET_STS_SUCCESS;
3909
3910 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3911 THEN
3912 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3913 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Begin of Approve_ItemGroups');
3914 END IF;
3915
3916
3917
3918 OPEN get_itemgroup_det(p_ItemGroups_id);
3919 FETCH get_itemgroup_det INTO l_itemgroup_det;
3920 IF get_itemgroup_det%NOTFOUND
3921 THEN
3922 FND_MESSAGE.SET_NAME('AHL','AHL_MC_OBJECT_ID_NULL');
3923 FND_MSG_PUB.ADD;
3924 END IF;
3925 CLOSE get_itemgroup_det;
3926
3927 IF l_itemgroup_det.object_version_number <> p_object_version_number
3928 THEN
3929 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD');
3930 FND_MSG_PUB.ADD;
3931 END IF;
3932
3933
3934 l_msg_count := FND_MSG_PUB.count_msg;
3935
3936 IF l_msg_count > 0
3937 THEN
3938 X_msg_count := l_msg_count;
3939 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3940 RAISE FND_API.G_EXC_ERROR;
3941 END IF;
3942
3943
3944 IF p_appr_status='APPROVED'
3945 THEN
3946 l_status:='COMPLETE';
3947 ELSE
3948 l_status:='APPROVAL_REJECTED';
3949 END IF;
3950
3951
3952 IF l_status = 'COMPLETE'
3953 THEN
3954 -- Insert record into histroy table.
3955
3956
3957 IF l_itemgroup_det.Source_Item_group_id IS NULL THEN
3958
3959 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3960 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3961 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Source Item group id is null');
3962 END IF;
3963
3964 update ahl_item_groups_b
3965 set status_code=l_status,
3966 object_version_number = object_version_number+1,
3967 last_update_date = sysdate,
3968 last_updated_by = to_number(fnd_global.user_id),
3969 last_update_login = to_number(fnd_global.login_id)
3970 where item_group_id=l_itemgroup_det.item_group_id
3971 and object_version_number = l_itemgroup_det.object_version_number;
3972
3973 l_action :='C';
3974
3975 update_histroy (
3976 p_ItemGroups_id => l_itemgroup_det.item_group_id,
3977 p_action => l_action
3978 );
3979
3980 ELSE
3981
3982
3983 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3984 THEN
3985 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3986 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Source Item Group id is not null');
3987 END IF;
3988
3989 l_fork_or_merge := Fork_Or_Merge(l_itemgroup_det.item_group_id);
3990 IF (l_fork_or_merge = 0)
3991 THEN
3992
3993 -- SATHAPLI::Bug# 4328454 fix
3994 -- Call to AHL_UTIL_UC_PKG.Invalidate_Instance will not be made in this
3995 -- procedure.The validation of Item group updates for active UCs is now
3996 -- being done in procedure Modify_Item_group. Refer to old version of
3997 -- the package for details.
3998
3999 -- Fork the IG to a new one, maintain the earlier complete version
4000
4001 -- Update the IG with status as complete
4002 update ahl_item_groups_b
4003 set status_code = 'COMPLETE',
4004 object_version_number = object_version_number + 1,
4005 source_item_group_id = NULL,
4006 last_update_date = sysdate,
4007 last_updated_by = to_number(fnd_global.user_id),
4008 last_update_login = to_number(fnd_global.login_id)
4009 where item_group_id = l_itemgroup_det.item_group_id and
4010 object_version_number = l_itemgroup_det.object_version_number;
4011
4012 -- For the positions with temp_item_group_id = null, such positions are not associated to the forked
4013 -- copy of the IG, hence no change to the positions
4014
4015 -- For the positions with temp_item_group_id <> null, such positions are associated to the forked
4016 -- copy of the IG, hence update the item_group_id = temp_item_group_id's value and set latter = null
4017 update ahl_mc_relationships
4018 set item_group_id = l_itemgroup_det.item_group_id,
4019 temp_item_group_id = null
4020 where item_group_id = l_itemgroup_det.source_item_group_id and
4021 temp_item_group_id is not null and
4022 trunc(nvl(active_end_date, sysdate+1)) > trunc(sysdate);
4023
4024 -- Update history table
4025 update_histroy
4026 (
4027 p_itemgroups_id => l_itemgroup_det.item_group_id,
4028 p_action => 'U'
4029 );
4030
4031 -- SATHAPLI::Bug# 4328454 fix
4032 -- Code pertaining to call to AHL_UTIL_UC_PKG.Invalidate_Instance removed.
4033 -- Refer to old version of the package for details.
4034
4035 ELSE
4036 -- Merge the IG with the previous complete version
4037 ahl_item_groups_pkg.update_row(
4038 x_item_group_id => l_itemgroup_det.Source_Item_group_id,
4039 x_type_code => l_itemgroup_det.type_code,
4040 x_status_code => 'COMPLETE',
4041 x_source_item_group_id => null,
4042 x_object_version_number => l_itemgroup_det.object_version_number + 1,
4043 x_name => l_itemgroup_det.name,
4044 x_attribute_category => l_itemgroup_det.attribute_category,
4045 x_attribute1 => l_itemgroup_det.attribute1,
4046 x_attribute2 => l_itemgroup_det.attribute2,
4047 x_attribute3 => l_itemgroup_det.attribute3,
4048 x_attribute4 => l_itemgroup_det.attribute4,
4049 x_attribute5 => l_itemgroup_det.attribute5,
4050 x_attribute6 => l_itemgroup_det.attribute6,
4051 x_attribute7 => l_itemgroup_det.attribute7,
4052 x_attribute8 => l_itemgroup_det.attribute8,
4053 x_attribute9 => l_itemgroup_det.attribute9,
4054 x_attribute10 => l_itemgroup_det.attribute10,
4055 x_attribute11 => l_itemgroup_det.attribute11,
4056 x_attribute12 => l_itemgroup_det.attribute12,
4057 x_attribute13 => l_itemgroup_det.attribute13,
4058 x_attribute14 => l_itemgroup_det.attribute14,
4059 x_attribute15 => l_itemgroup_det.attribute15,
4060 x_description => l_itemgroup_det.description,
4061 x_last_update_date => sysdate,
4062 x_last_updated_by => fnd_global.user_id,
4063 x_last_update_login => fnd_global.login_id);
4064
4065 -- SATHAPLI::Bug# 4328454 fix
4066 -- Call to AHL_UTIL_UC_PKG.Invalidate_Instance will not be made in this
4067 -- procedure.The validation of Item group updates for active UCs is now
4068 -- being done in procedure Modify_Item_group. Refer to old version of
4069 -- the package for details.
4070
4071 Delete from ahl_item_associations_tl
4072 where item_association_id in
4073 ( Select item_association_id
4074 from ahl_item_associations_b
4075 where item_group_id = l_itemgroup_det.Source_Item_group_id);
4076
4077 Delete from ahl_item_associations_b
4078 where item_group_id = l_itemgroup_det.Source_Item_group_id;
4079
4080 -- The following is to associate the Temporary Item Group Part Numbers
4081 -- to the Permant(Complete) Item Group.
4082
4083 update ahl_item_associations_b
4084 set item_group_id = l_itemgroup_det.Source_Item_group_id,
4085 object_version_number = object_version_number+1
4086 Where item_group_id = l_itemgroup_det.item_group_id;
4087
4088 -- This is to update the Master Configuration Node which are associated
4089 -- with 'Draft' Item Group.
4090
4091
4092 update ahl_mc_relationships
4093 set ITEM_GROUP_ID = l_itemgroup_det.Source_Item_group_id,
4094 object_version_number = object_version_number+1
4095 Where item_group_id = l_itemgroup_det.item_group_id;
4096
4097 l_action :='U';
4098
4099
4100 -- Updating the history tables.
4101
4102 update_histroy (
4103 p_ItemGroups_id => l_itemgroup_det.item_group_id,
4104 p_action => l_action
4105 );
4106
4107 -- This is to delete the temporary version of Item group.
4108
4109
4110 Delete from ahl_item_groups_tl
4111 where item_group_id = l_itemgroup_det.item_group_id;
4112
4113 Delete from ahl_item_groups_b
4114 where item_group_id = l_itemgroup_det.item_group_id;
4115
4116 -- SATHAPLI::Bug# 4328454 fix
4117 -- Code pertaining to call to AHL_UTIL_UC_PKG.Invalidate_Instance removed.
4118 -- Refer to old version of the package for details.
4119
4120 END IF; -- Fork_Or_Merge
4121 End if;
4122
4123
4124
4125
4126
4127 ELSIF l_status = 'APPROVAL_REJECTED' THEN
4128
4129 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4130 THEN
4131 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4132 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Approval Rejected');
4133 END IF;
4134
4135 update ahl_item_groups_b
4136 set status_code=l_status,
4137 object_version_number = object_version_number+1
4138 where item_group_id=l_itemgroup_det.item_group_id
4139 and object_version_number = l_itemgroup_det.object_version_number;
4140
4141
4142
4143
4144 End if;
4145
4146 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4147 THEN
4148 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4149 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'End of Approve_ItemGroups');
4150 END IF;
4151
4152
4153
4154 EXCEPTION
4155 WHEN FND_API.G_EXC_ERROR THEN
4156 x_return_status := FND_API.G_RET_STS_ERROR;
4157 Rollback to Approve_ItemGroups;
4158 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4159 p_data => x_msg_data,
4160 p_encoded => fnd_api.g_false);
4161
4162 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4163 THEN
4164 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4165 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Error in Approve_ItemGroups');
4166 END IF;
4167
4168
4169 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4171 Rollback to Approve_ItemGroups;
4172 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4173 p_data => x_msg_data,
4174 p_encoded => fnd_api.g_false);
4175
4176 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4177 THEN
4178 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4179 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Unexpected Error in Approve_ItemGroups');
4180 END IF;
4181
4182 WHEN OTHERS THEN
4183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4184 Rollback to Approve_ItemGroups;
4185 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4186 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
4187 p_procedure_name => 'Approve_ItemGroups',
4188 p_error_text => SUBSTR(SQLERRM,1,240));
4189 END IF;
4190 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4191 p_data => x_msg_data,
4192 p_encoded => fnd_api.g_false);
4193
4194 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4195 THEN
4196 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4197 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Unknown Error in Approve_ItemGroups');
4198 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4199 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', SQLERRM);
4200
4201 END IF;
4202
4203
4204 END Approve_ItemGroups;
4205
4206 PROCEDURE Modify_Position_Assos
4207 (
4208 p_api_version IN NUMBER,
4209 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4210 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4211 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4212 p_module_type IN VARCHAR2,
4213 x_return_status OUT NOCOPY VARCHAR2,
4214 x_msg_count OUT NOCOPY NUMBER,
4215 x_msg_data OUT NOCOPY VARCHAR2,
4216 p_item_group_id IN NUMBER,
4217 p_object_version_number IN NUMBER,
4218 p_nodes_tbl IN AHL_MC_Node_PVT.Node_Tbl_Type
4219 )
4220 IS
4221 CURSOR get_itemgroup_details
4222 (
4223 p_item_group_id in number
4224 )
4225 IS
4226 SELECT object_version_number, source_item_group_id, status_code
4227 FROM ahl_item_groups_b
4228 WHERE item_group_id = p_item_group_id;
4229
4230 CURSOR check_position_exists
4231 (
4232 p_relationship_id in number
4233 )
4234 IS
4235 SELECT 'x'
4236 FROM ahl_mc_relationships
4237 WHERE relationship_id = p_relationship_id;
4238
4239 -- Define local variables
4240 l_api_name CONSTANT VARCHAR2(30) := 'Create_Node';
4241 l_api_version CONSTANT NUMBER := 1.0;
4242 l_return_status VARCHAR2(1);
4243 l_msg_count NUMBER;
4244 l_msg_data VARCHAR2(2000);
4245
4246 l_obj_ver_num NUMBER;
4247 l_source_id NUMBER;
4248 l_status VARCHAR2(30);
4249 l_junk VARCHAR2(1);
4250
4251 BEGIN
4252
4253 -- Standard start of API savepoint
4254 SAVEPOINT Modify_Position_Assos_SP;
4255
4256 -- Standard call to check for call compatibility
4257 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
4258 THEN
4259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4260 END IF;
4261
4262 -- Initialize message list if p_init_msg_list is set to TRUE
4263 IF FND_API.TO_BOOLEAN(p_init_msg_list)
4264 THEN
4265 FND_MSG_PUB.Initialize;
4266 END IF;
4267
4268 -- Initialize API return status to success
4269 x_return_status := FND_API.G_RET_STS_SUCCESS;
4270
4271 -- API body starts here
4272 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4273 THEN
4274 fnd_log.string
4275 (
4276 fnd_log.level_procedure,
4277 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
4278 'At the start of PLSQL procedure'
4279 );
4280 END IF;
4281
4282 -- Validate whether the IG exists, if yes, then validate that object_version_number has not been
4283 -- already bounced and status = 'DRAFT' and the IG is a draft copy
4284 OPEN get_itemgroup_details(p_item_group_id);
4285 FETCH get_itemgroup_details INTO l_obj_ver_num, l_source_id, l_status;
4286 IF (get_itemgroup_details%NOTFOUND)
4287 THEN
4288 CLOSE get_itemgroup_details;
4289 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_DELETED');
4290 FND_MSG_PUB.ADD;
4291 RAISE FND_API.G_EXC_ERROR;
4292 ELSIF (l_obj_ver_num <> p_object_version_number)
4293 THEN
4294 CLOSE get_itemgroup_details;
4295 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
4296 FND_MSG_PUB.ADD;
4297 RAISE FND_API.G_EXC_ERROR;
4298 ELSIF (l_status <> 'DRAFT' or nvl(l_source_id, 0) <= 0)
4299 THEN
4300 CLOSE get_itemgroup_details;
4301 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_NOUPDATE');
4302 FND_MSG_PUB.ADD;
4303 RAISE FND_API.G_EXC_ERROR;
4304 END IF;
4305 CLOSE get_itemgroup_details;
4306
4307 -- All above validations have passed
4308 IF (p_nodes_tbl.COUNT > 0)
4309 THEN
4310 -- For each node in the p_nodes_tbl, unassociate the itemgroup, assumption is that only the
4311 -- to-be-unassociated records are passed from the frontend
4312 FOR i IN p_nodes_tbl.FIRST..p_nodes_tbl.LAST
4313 LOOP
4314 OPEN check_position_exists(p_nodes_tbl(i).relationship_id);
4315 FETCH check_position_exists INTO l_junk;
4316 -- Validate node exists, if yes, go ahead and unassociate the item group
4317 IF (check_position_exists%NOTFOUND)
4318 THEN
4319 CLOSE check_position_exists;
4320 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_NOT_FOUND');
4321 FND_MSG_PUB.ADD;
4322 ELSIF (p_nodes_tbl(i).operation_flag = 'C')
4323 THEN
4324 UPDATE ahl_mc_relationships
4325 SET temp_item_group_id = p_item_group_id
4326 WHERE relationship_id = p_nodes_tbl(i).relationship_id;
4327 ELSIF (p_nodes_tbl(i).operation_flag = 'D')
4328 THEN
4329 UPDATE ahl_mc_relationships
4330 SET temp_item_group_id = null
4331 WHERE relationship_id = p_nodes_tbl(i).relationship_id;
4332 END IF;
4333 CLOSE check_position_exists;
4334 END LOOP;
4335
4336 -- Check Error Message stack.
4337 x_msg_count := FND_MSG_PUB.count_msg;
4338 IF x_msg_count > 0 THEN
4339 RAISE FND_API.G_EXC_ERROR;
4340 END IF;
4341 END IF;
4342
4343 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4344 THEN
4345 fnd_log.string
4346 (
4347 fnd_log.level_procedure,
4348 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
4349 'At the end of PLSQL procedure'
4350 );
4351 END IF;
4352 -- API body ends here
4353
4354 -- Check Error Message stack.
4355 x_msg_count := FND_MSG_PUB.count_msg;
4356 IF x_msg_count > 0 THEN
4357 RAISE FND_API.G_EXC_ERROR;
4358 END IF;
4359
4360 -- Standard check for p_commit
4361 IF FND_API.TO_BOOLEAN (p_commit)
4362 THEN
4363 COMMIT WORK;
4364 END IF;
4365
4366 -- Standard call to get message count and if count is 1, get message info
4367 FND_MSG_PUB.count_and_get
4368 (
4369 p_count => x_msg_count,
4370 p_data => x_msg_data,
4371 p_encoded => FND_API.G_FALSE
4372 );
4373
4374 EXCEPTION
4375 WHEN FND_API.G_EXC_ERROR THEN
4376 x_return_status := FND_API.G_RET_STS_ERROR;
4377 Rollback to Modify_Position_Assos_SP;
4378 FND_MSG_PUB.count_and_get
4379 (
4380 p_count => x_msg_count,
4381 p_data => x_msg_data,
4382 p_encoded => FND_API.G_FALSE
4383 );
4384
4385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4387 Rollback to Modify_Position_Assos_SP;
4388 FND_MSG_PUB.count_and_get
4389 (
4390 p_count => x_msg_count,
4391 p_data => x_msg_data,
4392 p_encoded => FND_API.G_FALSE
4393 );
4394
4395 WHEN OTHERS THEN
4396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4397 Rollback to Modify_Position_Assos_SP;
4398 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4399 THEN
4400 FND_MSG_PUB.add_exc_msg
4401 (
4402 p_pkg_name => G_PKG_NAME,
4403 p_procedure_name => 'Modify_Position_Assos',
4404 p_error_text => SUBSTR(SQLERRM,1,240)
4405 );
4406 END IF;
4407 FND_MSG_PUB.count_and_get
4408 (
4409 p_count => x_msg_count,
4410 p_data => x_msg_data,
4411 p_encoded => FND_API.G_FALSE
4412 );
4413
4414 END Modify_Position_Assos;
4415
4416 FUNCTION Fork_Or_Merge
4417 (
4418 p_item_group_id in number
4419 )
4420 RETURN NUMBER
4421 -- Return values:
4422 -- -1 : Neither fork nor merge, for the case of a non-draft copy of the IG
4423 -- 0 : Fork the draft copy of the IG
4424 -- 1 : Merge the draft copy of the IG
4425 IS
4426
4427 CURSOR get_itemgroup_details
4428 (
4429 p_item_group_id in number
4430 )
4431 IS
4432 SELECT source_item_group_id, status_code
4433 FROM ahl_item_groups_b
4434 WHERE item_group_id = p_item_group_id;
4435
4436 CURSOR check_fork
4437 (
4438 p_parent_ig_id in number
4439 )
4440 IS
4441 SELECT 'x'
4442 FROM ahl_mc_relationships
4443 WHERE temp_item_group_id is null and
4444 item_group_id = p_parent_ig_id and
4445 trunc(nvl(active_end_date, sysdate + 1)) > trunc(sysdate);
4446
4447 l_source_id NUMBER;
4448 l_status VARCHAR2(30);
4449 l_junk VARCHAR2(1);
4450
4451 BEGIN
4452 OPEN get_itemgroup_details(p_item_group_id);
4453 FETCH get_itemgroup_details INTO l_source_id, l_status;
4454 CLOSE get_itemgroup_details;
4455
4456 -- For checking fork/merge, the IG should be a draft copy
4457 IF (nvl(l_source_id, 0) > 0 and (l_status = 'DRAFT' or l_status = 'APPROVAL_PENDING'))
4458 THEN
4459 OPEN check_fork(l_source_id);
4460 FETCH check_fork INTO l_junk;
4461 IF (check_fork%FOUND)
4462 THEN
4463 -- IG is to be forked
4464 CLOSE check_fork;
4465 RETURN 0;
4466 ELSE
4467 -- IG is to be merged
4468 CLOSE check_fork;
4469 RETURN 1;
4470 END IF;
4471 ELSE
4472 -- IG is not a draft copy
4473 RETURN -1;
4474 END IF;
4475
4476 END Fork_Or_Merge;
4477
4478 End AHL_MC_ITEMGROUP_PVT;