[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_ITEMGROUP_PVT
Source
1 PACKAGE BODY AHL_MC_ITEMGROUP_PVT AS
2 /* $Header: AHLVIGPB.pls 120.10.12020000.2 2012/12/10 13:41:56 shnatu 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 -- SATHAPLI::Bug 9891818, 20-July-2010, replaced the following non-existent error message with an existing one
924 -- FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_ASSOS_NOT_EXISTS');
925 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IA_INVALID'); -- Invalid association of item ITEM.
926 FND_MESSAGE.Set_Token('ITEM', l_x_item_assoc_rec.inventory_item_name);
927 FND_MSG_PUB.ADD;
928 --dbms_output.put_line('Item Assoc does not exist');
929 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
930 END IF;
931
932 -- Set variables.
933 x_row_id := l_item_assoc_rec.row_id;
934 l_inv_segment := l_item_assoc_rec.concatenated_segments || ',' || l_item_assoc_rec.organization_code;
935 l_revision_qty_control_code := l_item_assoc_rec.revision_qty_cntrl_code;
936 l_serial_number_control := l_item_assoc_rec.serial_nbr_cntrl_code;
937
938 -- Check if primary key changed.
939 IF ((l_x_item_assoc_rec.item_group_id IS NOT NULL) AND
940 (l_x_item_assoc_rec.item_group_id <> l_item_assoc_rec.item_group_id))
941 OR ((l_x_item_assoc_rec.inventory_item_id IS NOT NULL) AND
942 (l_x_item_assoc_rec.inventory_item_id <> l_item_assoc_rec.inventory_item_id))
943 OR ((l_x_item_assoc_rec.inventory_org_id IS NOT NULL) AND
944 (l_x_item_assoc_rec.inventory_org_id <> l_item_assoc_rec.inventory_org_id))
945 THEN
946 CLOSE Item_assoc_csr;
947 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
948 THEN
949 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);
950 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
951 'ahl_mc_itemgroup_pvt.Validate_Item_Assoc', l_item_key);
952 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);
953 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
954 'ahl_mc_itemgroup_pvt.Validate_Item_Assoc', l_item_key);
955 END IF;
956
957 FND_MESSAGE.Set_Name('AHL','AHL_COM_KEY_NOUPDATE');
958 FND_MSG_PUB.ADD;
959 --dbms_output.put_line('Primary key cannot be updated');
960 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
961 END IF;
962
963 -- plug primary key values.
964 l_x_item_assoc_rec.inventory_item_id := l_item_assoc_rec.inventory_item_id;
965 l_x_item_assoc_rec.inventory_org_id := l_item_assoc_rec.inventory_org_id;
966 l_x_item_assoc_rec.item_group_id := l_item_assoc_rec.item_group_id;
967
968
969 END IF; /* operation_flag */
970
971 -- Validate Inventory.
972 IF (l_x_item_assoc_rec.operation_flag = 'C') THEN
973 Validate_InventoryID(l_x_item_assoc_rec.inventory_item_id,
974 l_x_item_assoc_rec.inventory_org_id, p_type_code,l_master_org_id,l_inv_segment,
975 l_revision_qty_control_code, l_serial_number_control);
976
977 -- Assigning the Master Org ID from the Validate procedure .
978 -- As per 115.10 the Master Org Id should be stored in the Table.
979 p_item_assoc_rec.INVENTORY_ORG_ID := l_master_org_id;
980
981 -- This is performed only if item assoc records are being created for a
982 -- existing item_group_id (i.e this is called from Modify_Item_Group.)
983 IF (p_item_group_id IS NOT NULL ) THEN
984 -- Priyan Bug Number 4069855
985 -- Changed on 21st Dec 2004 , to allow for Items with different revisions to be added to the same Group
986 Validate_Dup_Inventory(p_name,
987 p_item_group_id,
988 l_x_item_assoc_rec.inventory_item_id,
989 l_x_item_assoc_rec.inventory_org_id,
990 l_x_item_assoc_rec.revision,
991 l_inv_segment,
992 l_x_item_assoc_rec.operation_flag
993 );
994 END IF;
995
996 -- Check for duplicate inventory items in the 'create' list.
997 l_inventory_item_id := l_x_item_assoc_rec.inventory_item_id;
998 l_inventory_org_id := l_x_item_assoc_rec.inventory_org_id;
999
1000 -- Bug Number 4069855
1001 -- Changed on 21st Dec 2004 , to allow for Items with different revisions to be added to the same Group
1002 -- 000 is used to denote that user has not entered a revision
1003 IF (l_x_item_assoc_rec.revision = FND_API.G_MISS_CHAR OR l_x_item_assoc_rec.revision IS NULL) THEN
1004 l_inventory_item_revision := '000';
1005 ELSE
1006 l_inventory_item_revision := l_x_item_assoc_rec.revision;
1007 END IF;
1008
1009 -- Bug Number 4069855
1010 -- Changed on 21st Dec 2004 , to allow for Items with different revisions to be added to the same Group
1011 -- SATHAPLI::Bug# 4330922 fix
1012 -- Changed the way p_x_inventory_list was being created and
1013 -- checked for duplicate item-org-rev combinations...the
1014 -- delimiter ':' will be put in the end now
1015
1016 -- Changes by skpathak for bug-7437855 on 18-NOV-2008 start
1017 -- Duplicate item association check is now done using G_ITEM_DTL_TYPE, instead of a string.
1018 -- IF (p_x_inventory_list IS NOT NULL) THEN
1019 l_item_key := TO_CHAR(l_inventory_item_id) || '-' || TO_CHAR(l_inventory_org_id) || '-' || l_inventory_item_revision;
1020
1021 -- check if inventory id exists.
1022 --Priyan : Another condition added in the IF to see if the revision is null
1023 /*IF (INSTR(p_x_inventory_list,l_inventory_item_id || '-' || l_inventory_org_id||'-'||l_inventory_item_revision||':') > 0
1024 or (INSTR(p_x_inventory_list,l_inventory_item_id || '-' || l_inventory_org_id||'-'||'000:') >0) ) THEN*/
1025
1026 IF (p_x_inventory_list.EXISTS(l_item_key)) THEN
1027 -- if two lines without Revision are the same, show the old Error Message
1028 IF (l_inventory_item_revision = '000') THEN
1029 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_ASSOC_DUP');
1030 FND_MESSAGE.Set_Token('GRP_NAME',p_name);
1031 FND_MESSAGE.Set_Token('INV_ITEM',l_inv_segment);
1032 FND_MSG_PUB.ADD;
1033 --dbms_output.put_line('Item already exists in the list');
1034 ELSE
1035 -- if two lines have the same Revision, show new error message with Revision
1036 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEM_ASSOC_REV_DUP');
1037 FND_MESSAGE.Set_Token('GRP_NAME',p_name);
1038 FND_MESSAGE.Set_Token('INV_ITEM',l_inventory_item_revision || ',' || l_inv_segment);
1039 FND_MSG_PUB.ADD;
1040 END IF;
1041 ELSE
1042 -- match not found, so add to the associative array p_x_inventory_list.
1043 -- p_x_inventory_list := p_x_inventory_list || l_inventory_item_id || '-' || l_inventory_org_id || '-' || l_inventory_item_revision || ':' ;
1044 p_x_inventory_list(l_item_key) := 1;
1045 END IF;
1046
1047 /*ELSE
1048 p_x_inventory_list := p_x_inventory_list || to_char(l_inventory_item_id) || '-' || to_char(l_inventory_org_id) || '-' || l_inventory_item_revision || ':' ;
1049 END IF;*/
1050 -- Changes by skpathak for bug-7437855 on 18-NOV-2008 end
1051
1052 END IF; /* for operation flag = 'C' */
1053
1054 --Priyan (revision check change for update for Item Group)
1055 --Bug # 4330922
1056 if (( l_x_item_assoc_rec.operation_flag = 'M') and
1057 nvl(l_x_item_assoc_rec.revision,FND_API.G_MISS_CHAR) <>
1058 nvl(l_item_assoc_rec.revision,FND_API.G_MISS_CHAR)) THEN
1059
1060 Validate_Dup_Inventory(p_name,
1061 p_item_group_id,
1062 l_x_item_assoc_rec.inventory_item_id,
1063 l_x_item_assoc_rec.inventory_org_id,
1064 l_x_item_assoc_rec.revision,
1065 l_inv_segment,
1066 l_x_item_assoc_rec.operation_flag
1067 );
1068 END IF;
1069 -- End of Changes -Priyan
1070
1071 -- Validate lookup codes and revision if present.
1072 IF (l_x_item_assoc_rec.operation_flag <> 'D') THEN
1073 Validate_Interchange_Code(l_x_item_assoc_rec.interchange_type_code,
1074 l_x_item_assoc_rec.interchange_reason,
1075 l_inv_segment);
1076
1077 Validate_Revision(l_x_item_assoc_rec.revision,
1078 l_x_item_assoc_rec.inventory_item_id,
1079 l_x_item_assoc_rec.inventory_org_id, l_inv_segment,
1080 l_revision_qty_control_code);
1081
1082 --Interchange Reason can not be null for 1-way interchanges
1083 IF (l_x_item_assoc_rec.INTERCHANGE_TYPE_CODE IS NOT NULL AND
1084 l_x_item_assoc_rec.INTERCHANGE_TYPE_CODE = G_FND_1WAY_CODE AND
1085 l_x_item_assoc_rec.INTERCHANGE_REASON IS NULL) THEN
1086 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_1WAY_MISSING_REASON');
1087 FND_MESSAGE.Set_Token('INV_ITEM',l_inv_segment);
1088 FND_MSG_PUB.ADD;
1089 END IF;
1090 END IF;
1091
1092
1093 -- Validate priority only for 'Create' and if changed during modify.
1094 IF (l_x_item_assoc_rec.priority <> FND_API.G_MISS_NUM AND l_x_item_assoc_rec.priority IS NOT NULL)
1095 THEN
1096 IF (l_x_item_assoc_rec.priority <= 0) THEN
1097 FND_MESSAGE.Set_Name('AHL','AHL_MC_PRIORITY_INVALID');
1098 FND_MESSAGE.Set_Token('INV_ITEM',l_inv_segment);
1099 FND_MESSAGE.Set_Token('PRIORITY',l_x_item_assoc_rec.priority);
1100 FND_MSG_PUB.ADD;
1101 --dbms_output.put_line('Invalid priority');
1102 END IF;
1103 ELSIF l_x_item_assoc_rec.operation_flag = 'C' OR
1104 (l_x_item_assoc_rec.operation_flag = 'M' AND l_x_item_assoc_rec.priority = FND_API.G_MISS_NUM) THEN
1105 FND_MESSAGE.Set_Name('AHL','AHL_MC_PRIORITY_NULL');
1106 FND_MESSAGE.Set_Token('INV_ITEM',l_inv_segment);
1107 FND_MSG_PUB.ADD;
1108 --dbms_output.put_line('Null priority');
1109 END IF;
1110
1111 -- set quantity and uom values into local variables for validation.
1112 IF (l_x_item_assoc_rec.operation_flag = 'C') THEN
1113 l_quantity := l_x_item_assoc_rec.quantity;
1114 l_uom_code := l_x_item_assoc_rec.uom_code;
1115 ELSIF (l_x_item_assoc_rec.operation_flag = 'M') THEN
1116
1117 -- For quantity
1118 IF (l_x_item_assoc_rec.quantity = FND_API.G_MISS_NUM) THEN
1119 l_quantity := null;
1120 ELSIF (l_x_item_assoc_rec.quantity is null ) THEN
1121 l_quantity := l_item_assoc_rec.quantity;
1122 ELSE
1123 l_quantity := l_x_item_assoc_rec.quantity;
1124 END IF;
1125
1126 -- For uom code.
1127 IF (l_x_item_assoc_rec.uom_code = FND_API.G_MISS_CHAR) THEN
1128 l_uom_code := null;
1129 ELSIF (l_x_item_assoc_rec.uom_code is null) THEN
1130 l_uom_code := l_item_assoc_rec.uom_code;
1131 ELSE
1132 l_uom_code := l_x_item_assoc_rec.uom_code;
1133 END IF;
1134
1135 END IF;
1136
1137 -- Validate quantity and UOM.
1138
1139
1140 IF (l_x_item_assoc_rec.operation_flag = 'C' OR
1141 l_x_item_assoc_rec.operation_flag = 'M' )
1142 THEN
1143 Validate_Qty_UOM(p_uom_code => l_uom_code,
1144 p_quantity => l_quantity,
1145 p_inventory_item_id => l_x_item_assoc_rec.inventory_item_id,
1146 p_inventory_org_id => l_x_item_assoc_rec.inventory_org_id,
1147 p_inv_segment => l_inv_segment);
1148
1149 END IF;
1150
1151 -- For serialized items quantity must be 1; if quantity not present then raise error.
1152 IF (l_x_item_assoc_rec.operation_flag = 'C' OR
1153 l_x_item_assoc_rec.operation_flag = 'M' )
1154 THEN
1155 IF (nvl(l_serial_number_control,0) IN (2,5,6)) THEN
1156 IF (l_quantity IS NULL OR
1157 l_quantity = 0 OR
1158 l_quantity = FND_API.G_MISS_NUM) THEN
1159 FND_MESSAGE.Set_Name('AHL','AHL_MC_SRLQTY_NULL');
1160 FND_MESSAGE.Set_Token('INV_ITEM',l_inv_segment);
1161 FND_MSG_PUB.ADD;
1162 --dbms_output.put_line('Invalid UOM code for the item');
1163 ELSE
1164 IF (l_quantity <> 1) THEN
1165 FND_MESSAGE.Set_Name('AHL','AHL_UC_SRLQTY_MISMATCH');
1166 FND_MESSAGE.Set_Token('QTY',l_quantity);
1167 FND_MESSAGE.Set_Token('INV_ITEM',l_inv_segment);
1168 FND_MSG_PUB.ADD;
1169 --dbms_output.put_line('For serialized items Quantity must be 1');
1170 END IF;
1171 END IF;
1172 END IF;
1173 END IF; /* operation */
1174
1175 -- Added for ER# 2631303.
1176 -- If item association is being deleted, then verify that this part is not installed in
1177 -- any of the unit configurations that are using this item group through their MC's.
1178 IF (l_x_item_assoc_rec.operation_flag = 'D') THEN
1179 Validate_UCItem_Assignment(l_x_item_assoc_rec.item_group_id,
1180 l_x_item_assoc_rec.inventory_item_id,
1181 l_x_item_assoc_rec.inventory_org_id,
1182 l_inv_segment);
1183 END IF;
1184
1185 IF (Item_assoc_csr%ISOPEN) THEN
1186 CLOSE Item_assoc_csr;
1187 END IF;
1188
1189 End Validate_Item_Assoc;
1190
1191 -------------------------------------
1192 -- Insert/Update/Delete procedures --
1193 -------------------------------------
1194 PROCEDURE Insert_Item_Group(p_x_item_grp_rec IN OUT NOCOPY AHL_MC_ITEMGROUP_PVT.Item_Group_Rec_Type) IS
1195
1196 l_item_grp_id NUMBER;
1197 l_last_update_login NUMBER;
1198 l_last_updated_by NUMBER;
1199 l_row_id VARCHAR2(30);
1200
1201 BEGIN
1202
1203 -- Set default values.
1204 IF p_x_item_grp_rec.DESCRIPTION = FND_API.G_MISS_CHAR THEN
1205 p_x_item_grp_rec.DESCRIPTION := null;
1206 END IF;
1207 IF p_x_item_grp_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR THEN
1208 p_x_item_grp_rec.ATTRIBUTE_CATEGORY := null;
1209 END IF;
1210 IF p_x_item_grp_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR THEN
1211 p_x_item_grp_rec.ATTRIBUTE1 := null;
1212 END IF;
1213 IF p_x_item_grp_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR THEN
1214 p_x_item_grp_rec.ATTRIBUTE2 := null;
1215 END IF;
1216 IF p_x_item_grp_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR THEN
1217 p_x_item_grp_rec.ATTRIBUTE3 := null;
1218 END IF;
1219 IF p_x_item_grp_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR THEN
1220 p_x_item_grp_rec.ATTRIBUTE4 := null;
1221 END IF;
1222 IF p_x_item_grp_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR THEN
1223 p_x_item_grp_rec.ATTRIBUTE5 := null;
1224 END IF;
1225 IF p_x_item_grp_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR THEN
1226 p_x_item_grp_rec.ATTRIBUTE6 := null;
1227 END IF;
1228 IF p_x_item_grp_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR THEN
1229 p_x_item_grp_rec.ATTRIBUTE7 := null;
1230 END IF;
1231 IF p_x_item_grp_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR THEN
1232 p_x_item_grp_rec.ATTRIBUTE8 := null;
1233 END IF;
1234 IF p_x_item_grp_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR THEN
1235 p_x_item_grp_rec.ATTRIBUTE9 := null;
1236 END IF;
1237 IF p_x_item_grp_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR THEN
1238 p_x_item_grp_rec.ATTRIBUTE10 := null;
1239 END IF;
1240 IF p_x_item_grp_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR THEN
1241 p_x_item_grp_rec.ATTRIBUTE11 := null;
1242 END IF;
1243 IF p_x_item_grp_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR THEN
1244 p_x_item_grp_rec.ATTRIBUTE12 := null;
1245 END IF;
1246 IF p_x_item_grp_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR THEN
1247 p_x_item_grp_rec.ATTRIBUTE13 := null;
1248 END IF;
1249 IF p_x_item_grp_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR THEN
1250 p_x_item_grp_rec.ATTRIBUTE14 := null;
1251 END IF;
1252 IF p_x_item_grp_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR THEN
1253 p_x_item_grp_rec.ATTRIBUTE15 := null;
1254 END IF;
1255
1256
1257 --Gets the sequence Number
1258 SELECT AHL_ITEM_GROUPS_B_S.nextval INTO
1259 l_item_grp_id from DUAL;
1260
1261 l_last_updated_by := to_number(fnd_global.USER_ID);
1262 l_last_update_login := to_number(fnd_global.LOGIN_ID);
1263
1264 AHL_ITEM_GROUPS_PKG.INSERT_ROW(
1265 X_ROWID => l_row_id,
1266 X_ITEM_GROUP_ID => l_item_grp_id,
1267 X_TYPE_CODE => p_x_item_grp_rec.type_code,
1268 X_STATUS_CODE => 'DRAFT',
1269 X_SOURCE_ITEM_GROUP_ID => NULL,
1270 X_OBJECT_VERSION_NUMBER => 1,
1271 X_NAME => p_x_item_grp_rec.name,
1272 X_ATTRIBUTE_CATEGORY => p_x_item_grp_rec.ATTRIBUTE_CATEGORY,
1273 X_ATTRIBUTE1 => p_x_item_grp_rec.attribute1,
1274 X_ATTRIBUTE2 => p_x_item_grp_rec.attribute2,
1275 X_ATTRIBUTE3 => p_x_item_grp_rec.attribute3,
1276 X_ATTRIBUTE4 => p_x_item_grp_rec.attribute4,
1277 X_ATTRIBUTE5 => p_x_item_grp_rec.attribute5,
1278 X_ATTRIBUTE6 => p_x_item_grp_rec.attribute6,
1279 X_ATTRIBUTE7 => p_x_item_grp_rec.attribute7,
1280 X_ATTRIBUTE8 => p_x_item_grp_rec.attribute8,
1281 X_ATTRIBUTE9 => p_x_item_grp_rec.attribute9,
1282 X_ATTRIBUTE10 => p_x_item_grp_rec.attribute10,
1283 X_ATTRIBUTE11 => p_x_item_grp_rec.attribute11,
1284 X_ATTRIBUTE12 => p_x_item_grp_rec.attribute12,
1285 X_ATTRIBUTE13 => p_x_item_grp_rec.attribute13,
1286 X_ATTRIBUTE14 => p_x_item_grp_rec.attribute14,
1287 X_ATTRIBUTE15 => p_x_item_grp_rec.attribute15,
1288 X_DESCRIPTION => p_x_item_grp_rec.description,
1289 X_CREATION_DATE => sysdate,
1290 X_CREATED_BY => to_number(fnd_global.USER_ID),
1291 X_LAST_UPDATE_DATE => sysdate,
1292 X_LAST_UPDATED_BY => l_last_updated_by,
1293 X_LAST_UPDATE_LOGIN => l_last_update_login);
1294
1295
1296 p_x_item_grp_rec.ITEM_GROUP_ID := l_item_grp_id; -- update id in record variable.
1297 p_x_item_grp_rec.OBJECT_VERSION_NUMBER := 1;
1298
1299
1300 END Insert_Item_Group;
1301
1302 ---------------------------------
1303 PROCEDURE Create_Association(p_item_assoc_rec IN OUT NOCOPY AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type)
1304 IS
1305
1306 l_item_assoc_rec AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type DEFAULT p_item_assoc_rec;
1307
1308 l_item_association_id NUMBER;
1309 l_row_id VARCHAR2(30);
1310 BEGIN
1311
1312 -- Replace G_MISS values with nulls.
1313 IF (l_item_assoc_rec.REVISION = FND_API.G_MISS_CHAR) THEN
1314 l_item_assoc_rec.REVISION := null;
1315 END IF;
1316 IF (l_item_assoc_rec.QUANTITY = FND_API.G_MISS_NUM) THEN
1317 l_item_assoc_rec.QUANTITY := null;
1318 l_item_assoc_rec.UOM_CODE := null;
1319 ELSIF (l_item_assoc_rec.QUANTITY IS NULL OR l_item_assoc_rec.QUANTITY = 0) THEN
1320 l_item_assoc_rec.UOM_CODE := null; -- if quantity = 0 then uom must be null.
1321 END IF;
1322 IF (l_item_assoc_rec.UOM_CODE = FND_API.G_MISS_CHAR) THEN
1323 l_item_assoc_rec.UOM_CODE := null;
1324 END IF;
1325 IF (l_item_assoc_rec.INTERCHANGE_TYPE_CODE = FND_API.G_MISS_CHAR) THEN
1326 l_item_assoc_rec.INTERCHANGE_TYPE_CODE := null;
1327 END IF;
1328 IF (l_item_assoc_rec.INTERCHANGE_REASON = FND_API.G_MISS_CHAR) THEN
1329 l_item_assoc_rec.INTERCHANGE_REASON := null;
1330 END IF;
1331
1332 IF (l_item_assoc_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR) THEN
1333 l_item_assoc_rec.ATTRIBUTE_CATEGORY := null;
1334 END IF;
1335 IF (l_item_assoc_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
1336 l_item_assoc_rec.ATTRIBUTE1 := null;
1337 END IF;
1338 IF (l_item_assoc_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
1339 l_item_assoc_rec.ATTRIBUTE2 := null;
1340 END IF;
1341 IF (l_item_assoc_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
1342 l_item_assoc_rec.ATTRIBUTE3 := null;
1343 END IF;
1344 IF (l_item_assoc_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
1345 l_item_assoc_rec.ATTRIBUTE4 := null;
1346 END IF;
1347 IF (l_item_assoc_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR) THEN
1348 l_item_assoc_rec.ATTRIBUTE5 := null;
1349 END IF;
1350 IF (l_item_assoc_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
1351 l_item_assoc_rec.ATTRIBUTE6 := null;
1352 END IF;
1353 IF (l_item_assoc_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
1354 l_item_assoc_rec.ATTRIBUTE7 := null;
1355 END IF;
1356 IF (l_item_assoc_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
1357 l_item_assoc_rec.ATTRIBUTE8 := null;
1358 END IF;
1359 IF (l_item_assoc_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
1360 l_item_assoc_rec.ATTRIBUTE9 := null;
1361 END IF;
1362 IF (l_item_assoc_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
1363 l_item_assoc_rec.ATTRIBUTE10 := null;
1364 END IF;
1365 IF (l_item_assoc_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
1366 l_item_assoc_rec.ATTRIBUTE11 := null;
1367 END IF;
1368 IF (l_item_assoc_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
1369 l_item_assoc_rec.ATTRIBUTE12 := null;
1370 END IF;
1371 IF (l_item_assoc_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
1372 l_item_assoc_rec.ATTRIBUTE13 := null;
1373 END IF;
1374 IF (l_item_assoc_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
1375 l_item_assoc_rec.ATTRIBUTE14 := null;
1376 END IF;
1377 IF (l_item_assoc_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
1378 l_item_assoc_rec.ATTRIBUTE15 := null;
1379 END IF;
1380 --Gets the sequence Number
1381 SELECT AHL_ITEM_ASSOCIATIONS_B_S.nextval INTO
1382 l_item_association_id from DUAL;
1383
1384 AHL_ITEM_ASSOCIATIONS_PKG.INSERT_ROW(
1385 X_ROWID => l_row_id,
1386 X_ITEM_ASSOCIATION_ID => l_item_association_id,
1387 X_SOURCE_ITEM_ASSOCIATION_ID => NULL,
1388 X_OBJECT_VERSION_NUMBER => 1,
1389 X_ITEM_GROUP_ID => l_item_assoc_rec.ITEM_GROUP_ID,
1390 X_INVENTORY_ITEM_ID => l_item_assoc_rec.INVENTORY_ITEM_ID,
1391 X_INVENTORY_ORG_ID => l_item_assoc_rec.INVENTORY_ORG_ID,
1392 X_PRIORITY => l_item_assoc_rec.PRIORITY,
1393 X_UOM_CODE => l_item_assoc_rec.UOM_CODE,
1394 X_QUANTITY => l_item_assoc_rec.QUANTITY,
1395 X_REVISION => l_item_assoc_rec.REVISION,
1396 X_INTERCHANGE_TYPE_CODE => l_item_assoc_rec.INTERCHANGE_TYPE_CODE,
1397 X_ITEM_TYPE_CODE => NULL,
1398 X_ATTRIBUTE_CATEGORY => l_item_assoc_rec.ATTRIBUTE_CATEGORY,
1399 X_ATTRIBUTE1 => l_item_assoc_rec.ATTRIBUTE1,
1400 X_ATTRIBUTE2 => l_item_assoc_rec.ATTRIBUTE2,
1401 X_ATTRIBUTE3 => l_item_assoc_rec.ATTRIBUTE3,
1402 X_ATTRIBUTE4 => l_item_assoc_rec.ATTRIBUTE4,
1403 X_ATTRIBUTE5 => l_item_assoc_rec.ATTRIBUTE5,
1404 X_ATTRIBUTE6 => l_item_assoc_rec.ATTRIBUTE6,
1405 X_ATTRIBUTE7 => l_item_assoc_rec.ATTRIBUTE7,
1406 X_ATTRIBUTE8 => l_item_assoc_rec.ATTRIBUTE8,
1407 X_ATTRIBUTE9 => l_item_assoc_rec.ATTRIBUTE9,
1408 X_ATTRIBUTE10 => l_item_assoc_rec.ATTRIBUTE10,
1409 X_ATTRIBUTE11 => l_item_assoc_rec.ATTRIBUTE11,
1410 X_ATTRIBUTE12 => l_item_assoc_rec.ATTRIBUTE12,
1411 X_ATTRIBUTE13 => l_item_assoc_rec.ATTRIBUTE13,
1412 X_ATTRIBUTE14 => l_item_assoc_rec.ATTRIBUTE14,
1413 X_ATTRIBUTE15 => l_item_assoc_rec.ATTRIBUTE15,
1414 X_INTERCHANGE_REASON => l_item_assoc_rec.INTERCHANGE_REASON,
1415 X_CREATION_DATE => sysdate,
1416 X_CREATED_BY => fnd_global.USER_ID,
1417 X_LAST_UPDATE_DATE => sysdate,
1418 X_LAST_UPDATED_BY => fnd_global.USER_ID,
1419 X_LAST_UPDATE_LOGIN => fnd_global.LOGIN_ID
1420 );
1421
1422 --Insert in AHL_ITEM_ASSOCIATIONS_B table
1423
1424 l_item_assoc_rec.item_association_id := l_item_association_id;
1425 l_item_assoc_rec.object_version_number := 1;
1426 -- Set out parameter.
1427 p_item_assoc_rec := l_item_assoc_rec;
1428
1429 END Create_Association;
1430
1431 ----------------------------
1432 PROCEDURE Update_Association(p_item_assoc_rec IN AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type,
1433 p_row_id IN UROWID) IS
1434
1435 CURSOR Item_assoc_csr(p_rowid UROWID) IS
1436 SELECT
1437 b.ROWID ROW_ID,
1438 b.ITEM_ASSOCIATION_ID,
1439 b.SOURCE_ITEM_ASSOCIATION_ID,
1440 b.ITEM_GROUP_ID,
1441 b.INVENTORY_ITEM_ID,
1442 b.INVENTORY_ORG_ID,
1443 b.REVISION,
1444 b.PRIORITY,
1445 b.QUANTITY,
1446 b.UOM_CODE,
1447 b.INTERCHANGE_TYPE_CODE,
1448 tl.INTERCHANGE_REASON,
1449 -- b.ITEM_TYPE_CODE,
1450 b.OBJECT_VERSION_NUMBER,
1451 tl.LANGUAGE,
1452 tl.SOURCE_LANG,
1453 b.ATTRIBUTE_CATEGORY,
1454 b.ATTRIBUTE1,
1455 b.ATTRIBUTE2,
1456 b.ATTRIBUTE3,
1457 b.ATTRIBUTE4,
1458 b.ATTRIBUTE5,
1459 b.ATTRIBUTE6 ,
1460 b.ATTRIBUTE7 ,
1461 b.ATTRIBUTE8,
1462 b.ATTRIBUTE9 ,
1463 b.ATTRIBUTE10,
1464 b.ATTRIBUTE11 ,
1465 b.ATTRIBUTE12,
1466 b.ATTRIBUTE13,
1467 b.ATTRIBUTE14,
1468 b.ATTRIBUTE15,
1469 b.LAST_UPDATE_DATE,
1470 b.LAST_UPDATED_BY,
1471 b.LAST_UPDATE_LOGIN
1472 FROM ahl_item_associations_b b, ahl_item_associations_tl tl
1473 WHERE b.item_association_id = tl.item_association_id
1474 and b.rowid = p_rowid
1475 AND tl.LANGUAGE = USERENV('LANG')
1476 FOR UPDATE OF object_version_number NOWAIT;
1477
1478 l_item_assoc_rec AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type DEFAULT p_item_assoc_rec;
1479 l_old_item_assoc_rec Item_assoc_csr%ROWTYPE;
1480
1481 BEGIN
1482
1483 OPEN Item_assoc_csr(p_row_id);
1484 FETCH Item_assoc_csr INTO l_old_item_assoc_rec;
1485 IF (Item_assoc_csr%NOTFOUND) THEN
1486 CLOSE Item_assoc_csr;
1487 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
1488 FND_MSG_PUB.ADD;
1489 --dbms_output.put_line('Item Assoc does not exist');
1490 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1491 END IF;
1492
1493 -- Check Object version number.
1494 IF (l_old_item_assoc_rec.object_version_number <> l_item_assoc_rec.object_version_number) THEN
1495 CLOSE Item_assoc_csr;
1496 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1497 FND_MSG_PUB.ADD;
1498 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1499 END IF;
1500
1501 -- Check for changed columns.
1502
1503 IF (l_item_assoc_rec.REVISION is NULL) THEN
1504 l_item_assoc_rec.REVISION := l_old_item_assoc_rec.REVISION;
1505 ELSIF (l_item_assoc_rec.REVISION = FND_API.G_MISS_CHAR) THEN
1506 l_item_assoc_rec.REVISION := NULL;
1507 END IF;
1508
1509 IF (l_item_assoc_rec.PRIORITY is NULL) THEN
1510 l_item_assoc_rec.PRIORITY := l_old_item_assoc_rec.PRIORITY;
1511 ELSIF (l_item_assoc_rec.PRIORITY = FND_API.G_MISS_NUM) THEN
1512 l_item_assoc_rec.PRIORITY := NULL;
1513 END IF;
1514
1515 IF (l_item_assoc_rec.QUANTITY is NULL) THEN
1516 l_item_assoc_rec.QUANTITY := l_old_item_assoc_rec.QUANTITY;
1517 ELSIF (l_item_assoc_rec.QUANTITY = FND_API.G_MISS_NUM) THEN
1518 l_item_assoc_rec.QUANTITY := NULL;
1519 ELSIF (l_item_assoc_rec.QUANTITY = 0) THEN
1520 l_item_assoc_rec.UOM_CODE := null;
1521 END IF;
1522
1523 IF (l_item_assoc_rec.UOM_CODE is NULL) THEN
1524 l_item_assoc_rec.UOM_CODE := l_old_item_assoc_rec.UOM_CODE;
1525 ELSIF (l_item_assoc_rec.UOM_CODE = FND_API.G_MISS_CHAR) THEN
1526 l_item_assoc_rec.UOM_CODE := NULL;
1527 END IF;
1528
1529 IF (l_item_assoc_rec.INTERCHANGE_TYPE_CODE is NULL) THEN
1530 l_item_assoc_rec.INTERCHANGE_TYPE_CODE := l_old_item_assoc_rec.INTERCHANGE_TYPE_CODE;
1531 ELSIF (l_item_assoc_rec.INTERCHANGE_TYPE_CODE = FND_API.G_MISS_CHAR) THEN
1532 l_item_assoc_rec.INTERCHANGE_TYPE_CODE := NULL;
1533 END IF;
1534
1535 IF (l_item_assoc_rec.INTERCHANGE_REASON is NULL) THEN
1536 l_item_assoc_rec.INTERCHANGE_REASON := l_old_item_assoc_rec.INTERCHANGE_REASON;
1537 ELSIF (l_item_assoc_rec.INTERCHANGE_REASON = FND_API.G_MISS_CHAR) THEN
1538 l_item_assoc_rec.INTERCHANGE_REASON := NULL;
1539 END IF;
1540
1541
1542 IF (l_item_assoc_rec.ATTRIBUTE_CATEGORY is NULL) THEN
1543 l_item_assoc_rec.ATTRIBUTE_CATEGORY := l_old_item_assoc_rec.ATTRIBUTE_CATEGORY;
1544 ELSIF (l_item_assoc_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR) THEN
1545 l_item_assoc_rec.ATTRIBUTE_CATEGORY := NULL;
1546 END IF;
1547
1548 IF (l_item_assoc_rec.ATTRIBUTE1 is NULL) THEN
1549 l_item_assoc_rec.ATTRIBUTE1 := l_old_item_assoc_rec.ATTRIBUTE1;
1550 ELSIF (l_item_assoc_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
1551 l_item_assoc_rec.ATTRIBUTE1 := NULL;
1552 END IF;
1553
1554 IF (l_item_assoc_rec.ATTRIBUTE2 is NULL) THEN
1555 l_item_assoc_rec.ATTRIBUTE2 := l_old_item_assoc_rec.ATTRIBUTE2;
1556 ELSIF (l_item_assoc_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
1557 l_item_assoc_rec.ATTRIBUTE2 := NULL;
1558 END IF;
1559
1560 IF (l_item_assoc_rec.ATTRIBUTE3 is NULL) THEN
1561 l_item_assoc_rec.ATTRIBUTE3 := l_old_item_assoc_rec.ATTRIBUTE3;
1562 ELSIF (l_item_assoc_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
1563 l_item_assoc_rec.ATTRIBUTE3 := NULL;
1564 END IF;
1565
1566 IF (l_item_assoc_rec.ATTRIBUTE4 is NULL) THEN
1567 l_item_assoc_rec.ATTRIBUTE4 := l_old_item_assoc_rec.ATTRIBUTE4;
1568 ELSIF (l_item_assoc_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
1569 l_item_assoc_rec.ATTRIBUTE4 := NULL;
1570 END IF;
1571
1572 IF (l_item_assoc_rec.ATTRIBUTE6 is NULL) THEN
1573 l_item_assoc_rec.ATTRIBUTE6 := l_old_item_assoc_rec.ATTRIBUTE6;
1574 ELSIF (l_item_assoc_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
1575 l_item_assoc_rec.ATTRIBUTE6 := NULL;
1576 END IF;
1577
1578 IF (l_item_assoc_rec.ATTRIBUTE7 is NULL) THEN
1579 l_item_assoc_rec.ATTRIBUTE7 := l_old_item_assoc_rec.ATTRIBUTE7;
1580 ELSIF (l_item_assoc_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
1581 l_item_assoc_rec.ATTRIBUTE7 := NULL;
1582 END IF;
1583
1584 IF (l_item_assoc_rec.ATTRIBUTE8 is NULL) THEN
1585 l_item_assoc_rec.ATTRIBUTE8 := l_old_item_assoc_rec.ATTRIBUTE8;
1586 ELSIF (l_item_assoc_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
1587 l_item_assoc_rec.ATTRIBUTE8 := NULL;
1588 END IF;
1589
1590 IF (l_item_assoc_rec.ATTRIBUTE9 is NULL) THEN
1591 l_item_assoc_rec.ATTRIBUTE9 := l_old_item_assoc_rec.ATTRIBUTE9;
1592 ELSIF (l_item_assoc_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
1593 l_item_assoc_rec.ATTRIBUTE9 := NULL;
1594 END IF;
1595
1596 IF (l_item_assoc_rec.ATTRIBUTE10 is NULL) THEN
1597 l_item_assoc_rec.ATTRIBUTE10 := l_old_item_assoc_rec.ATTRIBUTE10;
1598 ELSIF (l_item_assoc_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
1599 l_item_assoc_rec.ATTRIBUTE10 := NULL;
1600 END IF;
1601
1602 IF (l_item_assoc_rec.ATTRIBUTE11 is NULL) THEN
1603 l_item_assoc_rec.ATTRIBUTE11 := l_old_item_assoc_rec.ATTRIBUTE11;
1604 ELSIF (l_item_assoc_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
1605 l_item_assoc_rec.ATTRIBUTE11 := NULL;
1606 END IF;
1607
1608 IF (l_item_assoc_rec.ATTRIBUTE12 is NULL) THEN
1609 l_item_assoc_rec.ATTRIBUTE12 := l_old_item_assoc_rec.ATTRIBUTE12;
1610 ELSIF (l_item_assoc_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
1611 l_item_assoc_rec.ATTRIBUTE12 := NULL;
1612 END IF;
1613
1614 IF (l_item_assoc_rec.ATTRIBUTE13 is NULL) THEN
1615 l_item_assoc_rec.ATTRIBUTE13 := l_old_item_assoc_rec.ATTRIBUTE13;
1616 ELSIF (l_item_assoc_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
1617 l_item_assoc_rec.ATTRIBUTE13 := NULL;
1618 END IF;
1619
1620 IF (l_item_assoc_rec.ATTRIBUTE14 is NULL) THEN
1621 l_item_assoc_rec.ATTRIBUTE14 := l_old_item_assoc_rec.ATTRIBUTE14;
1622 ELSIF (l_item_assoc_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
1623 l_item_assoc_rec.ATTRIBUTE14 := NULL;
1624 END IF;
1625
1626 IF (l_item_assoc_rec.ATTRIBUTE15 is NULL) THEN
1627 l_item_assoc_rec.ATTRIBUTE15 := l_old_item_assoc_rec.ATTRIBUTE15;
1628 ELSIF (l_item_assoc_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
1629 l_item_assoc_rec.ATTRIBUTE15 := NULL;
1630 END IF;
1631
1632 /*
1633 IF (l_item_assoc_rec.SOURCE_LANG is NULL) THEN
1634 l_item_assoc_rec.SOURCE_LANG := l_old_item_assoc_rec.SOURCE_LANG;
1635 ELSIF (l_item_assoc_rec.SOURCE_LANG = FND_API.G_MISS_CHAR) THEN
1636 l_item_assoc_rec.SOURCE_LANG := NULL;
1637 END IF;
1638 */
1639
1640 AHL_ITEM_ASSOCIATIONS_PKG.UPDATE_ROW(
1641 X_ITEM_ASSOCIATION_ID => l_item_assoc_rec.item_association_id,
1642 X_SOURCE_ITEM_ASSOCIATION_ID => l_old_item_assoc_rec.SOURCE_ITEM_ASSOCIATION_ID,
1643 X_OBJECT_VERSION_NUMBER => l_old_item_assoc_rec.object_version_number + 1,
1644 X_ITEM_GROUP_ID => l_item_assoc_rec.item_group_id,
1645 X_INVENTORY_ITEM_ID => l_item_assoc_rec.inventory_item_id,
1646 X_INVENTORY_ORG_ID => l_item_assoc_rec.inventory_org_id,
1647 X_PRIORITY => l_item_assoc_rec.priority,
1648 X_UOM_CODE => l_item_assoc_rec.uom_code,
1649 X_QUANTITY => l_item_assoc_rec.quantity,
1650 X_REVISION => l_item_assoc_rec.revision,
1651 X_INTERCHANGE_TYPE_CODE => l_item_assoc_rec.interchange_type_code,
1652 X_ITEM_TYPE_CODE => NULL,
1653 X_ATTRIBUTE_CATEGORY => l_item_assoc_rec.ATTRIBUTE_CATEGORY,
1654 X_ATTRIBUTE1 => l_item_assoc_rec.attribute1,
1655 X_ATTRIBUTE2 => l_item_assoc_rec.attribute2,
1656 X_ATTRIBUTE3 => l_item_assoc_rec.attribute3,
1657 X_ATTRIBUTE4 => l_item_assoc_rec.attribute4,
1658 X_ATTRIBUTE5 => l_item_assoc_rec.attribute5,
1659 X_ATTRIBUTE6 => l_item_assoc_rec.attribute6,
1660 X_ATTRIBUTE7 => l_item_assoc_rec.attribute7,
1661 X_ATTRIBUTE8 => l_item_assoc_rec.attribute8,
1662 X_ATTRIBUTE9 => l_item_assoc_rec.attribute9,
1663 X_ATTRIBUTE10 => l_item_assoc_rec.attribute10,
1664 X_ATTRIBUTE11 => l_item_assoc_rec.attribute11,
1665 X_ATTRIBUTE12 => l_item_assoc_rec.attribute12,
1666 X_ATTRIBUTE13 => l_item_assoc_rec.attribute13,
1667 X_ATTRIBUTE14 => l_item_assoc_rec.attribute14,
1668 X_ATTRIBUTE15 => l_item_assoc_rec.attribute15,
1669 X_INTERCHANGE_REASON => l_item_assoc_rec.interchange_reason,
1670 X_LAST_UPDATE_DATE => sysdate,
1671 X_LAST_UPDATED_BY => fnd_global.USER_ID,
1672 X_LAST_UPDATE_LOGIN => fnd_global.LOGIN_ID);
1673
1674
1675 CLOSE Item_assoc_csr;
1676
1677 END Update_Association;
1678
1679 -----------------------------
1680 PROCEDURE Delete_Association(p_item_assoc_rec IN AHL_MC_ITEMGROUP_PVT.Item_Association_Rec_Type,
1681 p_row_id IN UROWID) IS
1682
1683 CURSOR Item_assoc_csr(p_row_id UROWID) IS
1684 SELECT
1685 Object_version_number
1686 FROM ahl_item_associations_vl
1687 WHERE row_id = p_row_id
1688 FOR UPDATE OF object_version_number NOWAIT;
1689
1690 l_object_version_number NUMBER;
1691
1692 BEGIN
1693
1694 OPEN Item_assoc_csr(p_row_id);
1695 FETCH Item_assoc_csr INTO l_object_version_number;
1696 IF (Item_assoc_csr%NOTFOUND) THEN
1697 CLOSE Item_assoc_csr;
1698 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
1699 FND_MSG_PUB.ADD;
1700 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1701 END IF;
1702
1703 -- Check Object version number.
1704 IF (l_object_version_number <> p_item_assoc_rec.object_version_number) THEN
1705 CLOSE Item_assoc_csr;
1706 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1707 FND_MSG_PUB.ADD;
1708 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1709 END IF;
1710
1711 -- Delete record.
1712 DELETE ahl_item_associations_b
1713 WHERE item_association_id = p_item_assoc_rec.item_association_id;
1714
1715 DELETE ahl_item_associations_tl
1716 WHERE item_association_id = p_item_assoc_rec.item_association_id;
1717
1718 CLOSE Item_assoc_csr;
1719
1720 END Delete_Association;
1721
1722
1723 -----------------------------------------
1724 -- Procedures for Item Groups --
1725 -----------------------------------------
1726 -- Start of Comments --
1727 -- Procedure name : Create_Item_group
1728 -- Type : Private
1729 -- 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.
1730 -- Pre-reqs :
1731 -- Parameters :
1732 -- End of Comments --
1733
1734 PROCEDURE Create_Item_group (p_api_version IN NUMBER,
1735 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1736 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1737 x_return_status OUT NOCOPY VARCHAR2,
1738 x_msg_count OUT NOCOPY NUMBER,
1739 x_msg_data OUT NOCOPY VARCHAR2,
1740 p_x_item_group_rec IN OUT NOCOPY AHL_MC_ITEMGROUP_PVT.Item_Group_Rec_Type,
1741 p_x_items_tbl IN OUT NOCOPY AHL_MC_ITEMGROUP_PVT.Item_Association_Tbl_Type
1742 ) IS
1743
1744 l_api_name CONSTANT VARCHAR2(30) := 'Create_Item_Group';
1745 l_api_version CONSTANT NUMBER := 1.0;
1746
1747 -- Changes by skpathak for bug-7437855 on 18-NOV-2008
1748 -- Duplicate item association check is now done using G_ITEM_DTL_TYPE, instead of a string.
1749 l_inventory_list G_ITEM_DTL_TYPE;
1750 l_inventory_item_id NUMBER;
1751 l_item_group_id NUMBER; -- sequence generated item group id.
1752 l_name ahl_item_groups_b.name%TYPE;
1753 l_inv_segment ahl_mtl_items_non_ou_v.concatenated_segments%TYPE;
1754 l_row_id VARCHAR2(30);
1755
1756 BEGIN
1757 -- Standard start of API savepoint
1758 SAVEPOINT Create_Item_group_Pvt;
1759
1760
1761 -- Standard call to check for call compatibility
1762 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1763 G_PKG_NAME) THEN
1764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1765 END IF;
1766
1767 -- Initialize message list if p_init_msg_list is set to TRUE
1768 IF FND_API.To_Boolean(p_init_msg_list) THEN
1769 FND_MSG_PUB.Initialize;
1770 END IF;
1771
1772 -- Initialize API return status to success
1773 x_return_status := FND_API.G_RET_STS_SUCCESS;
1774
1775 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1776 THEN
1777 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1778 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Inside Create_Item_group');
1779 END IF;
1780
1781 --dbms_output.put_line('Inside Create_Item_group');
1782
1783 -- Validate Item Group Name.
1784 p_x_item_group_rec.name := RTRIM(p_x_item_group_rec.name);
1785 -- TAMAL -- IG Amendments --
1786 Validate_Item_Group_Name(p_x_item_group_rec.name, null, p_x_item_group_rec.source_item_group_id);
1787 -- TAMAL -- IG Amendments --
1788 l_name := p_x_item_group_rec.name; -- Item Group name.
1789
1790 --dbms_output.put_line('After validating Item Group Name');
1791
1792 -- Validate Item Association record columns.
1793 IF (p_x_items_tbl.COUNT > 0) THEN
1794 -- Added by skpathak for bug-7437855 on 18-NOV-2008 - Clear l_inventory_list before using.
1795 l_inventory_list.DELETE;
1796
1797 FOR i IN p_x_items_tbl.FIRST..p_x_items_tbl.LAST LOOP
1798 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1799 THEN
1800 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1801 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Before validating Item Group Associations');
1802 END IF;
1803
1804 Validate_Item_Assoc(p_name => p_x_item_group_rec.name,
1805 p_type_code => p_x_item_group_rec.type_code,
1806 p_item_assoc_rec => p_x_items_tbl(i),
1807 p_x_inventory_list => l_inventory_list,
1808 x_row_id => l_row_id);
1809
1810 END LOOP;
1811 END IF; /* for count > 0 */
1812
1813 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1814 THEN
1815 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1816 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'After validating Item Group Associations');
1817 END IF;
1818
1819
1820 -- Check Error Message stack.
1821 x_msg_count := FND_MSG_PUB.count_msg;
1822 IF x_msg_count > 0 THEN
1823 RAISE FND_API.G_EXC_ERROR;
1824 END IF;
1825
1826 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1827 THEN
1828 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1829 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Before calling Insert_Item_group');
1830 END IF;
1831
1832 -- Insert into ahl_item_groups_b and TL.
1833 Insert_Item_group(p_x_item_group_rec);
1834
1835 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1836 THEN
1837 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1838 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'After calling Insert_Item_group');
1839 END IF;
1840
1841
1842 l_item_group_id := p_x_item_group_rec.item_group_id;
1843
1844 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1845 THEN
1846 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1847 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Before loop of Item Association');
1848 END IF;
1849
1850
1851 -- Insert into ahl_item_associations_b/_tl.
1852 IF (p_x_items_tbl.COUNT > 0) THEN
1853 FOR i IN p_x_items_tbl.FIRST..p_x_items_tbl.LAST LOOP
1854 p_x_items_tbl(i).ITEM_GROUP_ID := l_item_group_id;
1855 Create_Association(p_x_items_tbl(i));
1856 END LOOP;
1857 END IF; /* count > 0 */
1858
1859 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1860 THEN
1861 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1862 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'After loop of Item Association');
1863 END IF;
1864
1865 validate_priority(l_item_group_id);
1866
1867 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1868 THEN
1869 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1870 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'End of create_item_group private');
1871 END IF;
1872
1873
1874 -- Standard call to get message count and if count is 1, get message info
1875 FND_MSG_PUB.Count_And_Get
1876 ( p_count => x_msg_count,
1877 p_data => x_msg_data,
1878 p_encoded => fnd_api.g_false);
1879
1880 EXCEPTION
1881 WHEN FND_API.G_EXC_ERROR THEN
1882 x_return_status := FND_API.G_RET_STS_ERROR;
1883 Rollback to Create_Item_group_pvt;
1884 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1885 p_data => x_msg_data,
1886 p_encoded => fnd_api.g_false);
1887 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1888 THEN
1889 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1890 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Error in create_item_group private');
1891 END IF;
1892
1893
1894
1895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1896 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1897 Rollback to Create_Item_group_pvt;
1898 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1899 p_data => x_msg_data,
1900 p_encoded => fnd_api.g_false);
1901 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
1902 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1903 THEN
1904 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1905 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Unexpected error in create_item_group private');
1906 END IF;
1907
1908
1909 WHEN OTHERS THEN
1910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1911 Rollback to Create_Item_group_pvt;
1912 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1913 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1914 p_procedure_name => 'Create_Item_group',
1915 p_error_text => SQLERRM);
1916 END IF;
1917 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1918 p_data => x_msg_data,
1919 p_encoded => fnd_api.g_false);
1920 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
1921
1922 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
1923 THEN
1924 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1925 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', 'Unknown error in create_item_group private');
1926 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1927 'ahl.plsql.ahl_mc_itemgroup_pvt.create_item_group', SQLERRM);
1928 END IF;
1929
1930 END Create_Item_group;
1931
1932
1933 -- Start of Comments --
1934 -- Procedure name : Modify_Item_group
1935 -- Type : Private
1936 -- 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.
1937 -- Pre-reqs :
1938 -- Parameters :
1939 -- Standard IN Parameters :
1940
1941 PROCEDURE Modify_Item_group (p_api_version IN NUMBER,
1942 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1943 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1944 x_return_status OUT NOCOPY VARCHAR2,
1945 x_msg_count OUT NOCOPY NUMBER,
1946 x_msg_data OUT NOCOPY VARCHAR2,
1947 p_item_group_rec IN AHL_MC_ITEMGROUP_PVT.Item_Group_Rec_Type,
1948 p_x_items_tbl IN OUT NOCOPY AHL_MC_ITEMGROUP_PVT.Item_Association_Tbl_Type
1949 ) IS
1950
1951 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Item_Group';
1952 l_api_version CONSTANT NUMBER := 1.0;
1953
1954 -- SATHAPLI::Enigma Phase II integration, 01-Mar-2010, modified the cursor to fetch sourced_from_enigma_flag as well
1955 CURSOR Item_group_csr(p_item_group_id IN NUMBER) IS
1956 SELECT
1957 b.ROWID ROW_ID,
1958 b.ITEM_GROUP_ID,
1959 b.source_item_group_id,
1960 b.NAME,
1961 b.type_code,
1962 b.status_code,
1963 b.OBJECT_VERSION_NUMBER,
1964 b.ATTRIBUTE_CATEGORY,
1965 b.ATTRIBUTE1,
1966 b.ATTRIBUTE2,
1967 b.ATTRIBUTE3,
1968 b.ATTRIBUTE4,
1969 b.ATTRIBUTE5,
1970 b.ATTRIBUTE6,
1971 b.ATTRIBUTE7,
1972 b.ATTRIBUTE8,
1973 b.ATTRIBUTE9,
1974 b.ATTRIBUTE10,
1975 b.ATTRIBUTE11,
1976 b.ATTRIBUTE12,
1977 b.ATTRIBUTE13,
1978 b.ATTRIBUTE14,
1979 b.ATTRIBUTE15,
1980 b.LAST_UPDATE_DATE,
1981 b.LAST_UPDATED_BY,
1982 b.CREATION_DATE,
1983 b.CREATED_BY,
1984 b.LAST_UPDATE_LOGIN,
1985 b.sourced_from_enigma_flag,
1986 TL.LANGUAGE,
1987 TL.SOURCE_LANG,
1988 TL.DESCRIPTION
1989 FROM
1990 AHL_ITEM_GROUPS_B b, AHL_ITEM_GROUPS_TL tl
1991 WHERE
1992 b.ITEM_GROUP_ID = tl.ITEM_GROUP_ID
1993 AND b.ITEM_GROUP_ID = p_item_group_id
1994 AND tl.LANGUAGE = USERENV('LANG')
1995 FOR UPDATE OF b.OBJECT_VERSION_NUMBER NOWAIT;
1996
1997 l_old_item_group_rec Item_group_csr%ROWTYPE;
1998 l_item_group_rec AHL_MC_ITEMGROUP_PVT.Item_group_rec_Type DEFAULT p_item_group_rec;
1999
2000 TYPE l_rowid_tbl_type IS TABLE OF UROWID INDEX BY BINARY_INTEGER;
2001 -- Build table with Rowid for Item Associations.
2002
2003 -- Changes by skpathak for bug-7437855 on 18-NOV-2008
2004 -- Duplicate item association check is now done using G_ITEM_DTL_TYPE, instead of a string.
2005 l_inventory_list G_ITEM_DTL_TYPE;
2006 l_rowid_tbl l_rowid_tbl_type;
2007 l_row_id VARCHAR2(30);
2008
2009 -- SATHAPLI::Bug# 4328454 fix
2010 l_full_name CONSTANT VARCHAR(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2011 l_invalid_item_instance_tbl AHL_UTIL_UC_PKG.Instance_Tbl_Type2;
2012 l_update_flag VARCHAR2(1) := 'N';
2013 l_index NUMBER := 1;
2014
2015 BEGIN
2016
2017 -- Standard start of API savepoint
2018 SAVEPOINT Modify_Item_group_Pvt;
2019
2020 -- Standard call to check for call compatibility
2021 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
2022 G_PKG_NAME) THEN
2023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2024 END IF;
2025
2026 -- Initialize message list if p_init_msg_list is set to TRUE
2027 IF FND_API.To_Boolean(p_init_msg_list) THEN
2028 FND_MSG_PUB.Initialize;
2029 END IF;
2030
2031 -- Initialize API return status to success
2032 x_return_status := FND_API.G_RET_STS_SUCCESS;
2033
2034 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2035 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2036 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Begin of Modify_Item_group');
2037 END IF;
2038
2039 -- Validate Item Group record.
2040 OPEN Item_group_csr(p_item_group_rec.item_group_id);
2041 FETCH Item_group_csr INTO l_old_item_group_rec;
2042 IF (Item_group_csr%NOTFOUND) THEN
2043 CLOSE Item_group_csr;
2044 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_NOT_EXISTS');
2045 FND_MSG_PUB.ADD;
2046 --dbms_output.put_line('Item Group does not exist');
2047 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2048 END IF;
2049
2050 -- SATHAPLI::Enigma Phase II integration, 01-Mar-2010, bypass the following check for Enigma sourced item groups
2051 -- Check Object version number.
2052 -- IF (l_old_item_group_rec.object_version_number <> NVL(p_item_group_rec.object_version_number,0)) THEN
2053 IF (NVL(l_old_item_group_rec.sourced_from_enigma_flag, 'N') = 'N' AND
2054 l_old_item_group_rec.object_version_number <> NVL(p_item_group_rec.object_version_number,0)) THEN
2055 CLOSE Item_group_csr;
2056 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
2057 FND_MSG_PUB.ADD;
2058 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2059 END IF;
2060
2061 -- Check name
2062 -- TAMAL -- IG Amendments --
2063 IF (l_old_item_group_rec.status_code = 'DRAFT' and l_old_item_group_rec.source_item_group_id is not null)
2064 THEN
2065 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);
2066 x_msg_count := FND_MSG_PUB.count_msg;
2067 IF x_msg_count > 0
2068 THEN
2069 CLOSE Item_group_csr;
2070 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2071 END IF;
2072 l_old_item_group_rec.name := l_item_group_rec.name;
2073 -- TAMAL -- IG Amendments --
2074 ELSIF (l_old_item_group_rec.name <> p_item_group_rec.name )
2075 THEN
2076 CLOSE Item_group_csr;
2077 FND_MESSAGE.Set_Name('AHL','AHL_COM_KEY_NOUPDATE');
2078 FND_MSG_PUB.ADD;
2079 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2080 END IF;
2081
2082 -- SATHAPLI::Enigma Phase II integration, 01-Mar-2010, bypass the following check for Enigma sourced item groups
2083 -- IF l_old_item_group_rec.status_code in ('COMPLETE','APPROVAL_PENDING')
2084 IF NVL(l_old_item_group_rec.sourced_from_enigma_flag, 'N') = 'N' AND
2085 l_old_item_group_rec.status_code in ('COMPLETE','APPROVAL_PENDING')
2086 THEN
2087 CLOSE Item_group_csr;
2088 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_NOUPDATE');
2089 FND_MESSAGE.Set_Token('STATUS',p_item_group_rec.status_code);
2090 FND_MSG_PUB.ADD;
2091 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2092 --dbms_output.put_line('Item Group cannot be updated if status is Complete or Approval Rejected');
2093 END IF;
2094
2095 IF (p_item_group_rec.status_code <> FND_API.G_MISS_CHAR) THEN
2096
2097 IF (p_item_group_rec.status_code <> l_old_item_group_rec.status_code)
2098 THEN
2099 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_STAT_NOUPDATE');
2100 FND_MSG_PUB.ADD;
2101 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2102 --dbms_output.put_line('Item Group Status cannot be updated');
2103 END IF;
2104 END IF;
2105
2106 IF (p_item_group_rec.type_code <> FND_API.G_MISS_CHAR) THEN
2107 IF (p_item_group_rec.type_code <> l_old_item_group_rec.type_code)
2108 THEN
2109 FND_MESSAGE.Set_Name('AHL','AHL_MC_IG_TYPE_NOUPDATE');
2110 FND_MSG_PUB.ADD;
2111 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2112 --dbms_output.put_line('Item Group Type cannot be updated');
2113 END IF;
2114
2115 END IF;
2116
2117 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2118 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2119 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Loop to validate Item Association');
2120 END IF;
2121
2122 -- Validate Item Assoc records.
2123 IF (p_x_items_tbl.COUNT > 0 ) THEN
2124 -- Added by skpathak for bug-7437855 on 18-NOV-2008 - Clear l_inventory_list before using.
2125 l_inventory_list.DELETE;
2126
2127 FOR i IN p_x_items_tbl.FIRST..p_x_items_tbl.LAST LOOP
2128 -- Check if association record belongs to the item group.
2129 IF (p_x_items_tbl(i).item_group_id <> p_item_group_rec.item_group_id) THEN
2130 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_MISMATCH');
2131 FND_MESSAGE.Set_Token('ITEM_GRP',p_item_group_rec.item_group_id);
2132 FND_MESSAGE.Set_Token('ASSO_GRP',p_x_items_tbl(i).item_group_id);
2133 FND_MSG_PUB.ADD;
2134 --dbms_output.put_line('Item Association record does not match Item Group');
2135 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2136 END IF;
2137
2138 Validate_Item_Assoc(p_name => p_item_group_rec.name,
2139 p_type_code => p_item_group_rec.type_code,
2140 p_item_group_id => p_item_group_rec.item_group_id,
2141 p_item_assoc_rec => p_x_items_tbl(i),
2142 p_x_inventory_list => l_inventory_list,
2143 x_row_id => l_row_id);
2144
2145 l_rowid_tbl(i) := l_row_id;
2146
2147 END LOOP;
2148 END IF; /* count > 0 */
2149
2150 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2151 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2152 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'End of Loop');
2153 END IF;
2154
2155 -- Check Error Message stack.
2156 x_msg_count := FND_MSG_PUB.count_msg;
2157 IF x_msg_count > 0 THEN
2158 RAISE FND_API.G_EXC_ERROR;
2159 END IF;
2160
2161 IF (l_item_group_rec.operation_flag = 'M') THEN
2162
2163 -- Check for changed values.
2164
2165 IF (l_old_item_group_rec.status_code = 'APPROVAL_REJECTED') THEN
2166 l_old_item_group_rec.status_code := 'DRAFT';
2167 END IF;
2168
2169 IF (l_item_group_rec.DESCRIPTION is NULL) THEN
2170 l_item_group_rec.DESCRIPTION := l_old_item_group_rec.DESCRIPTION;
2171 ELSIF (l_item_group_rec.DESCRIPTION = FND_API.G_MISS_CHAR) THEN
2172 l_item_group_rec.DESCRIPTION := NULL;
2173 END IF;
2174
2175 IF (l_item_group_rec.ATTRIBUTE_CATEGORY is NULL) THEN
2176 l_item_group_rec.ATTRIBUTE_CATEGORY := l_old_item_group_rec.ATTRIBUTE_CATEGORY;
2177 ELSIF (l_item_group_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR) THEN
2178 l_item_group_rec.ATTRIBUTE_CATEGORY := NULL;
2179 END IF;
2180
2181 IF (l_item_group_rec.ATTRIBUTE1 is NULL) THEN
2182 l_item_group_rec.ATTRIBUTE1 := l_old_item_group_rec.ATTRIBUTE1;
2183 ELSIF (l_item_group_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
2184 l_item_group_rec.ATTRIBUTE1 := NULL;
2185 END IF;
2186
2187 IF (l_item_group_rec.ATTRIBUTE2 is NULL) THEN
2188 l_item_group_rec.ATTRIBUTE2 := l_old_item_group_rec.ATTRIBUTE2;
2189 ELSIF (l_item_group_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
2190 l_item_group_rec.ATTRIBUTE2 := NULL;
2191 END IF;
2192
2193 IF (l_item_group_rec.ATTRIBUTE3 is NULL) THEN
2194 l_item_group_rec.ATTRIBUTE3 := l_old_item_group_rec.ATTRIBUTE3;
2195 ELSIF (l_item_group_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
2196 l_item_group_rec.ATTRIBUTE3 := NULL;
2197 END IF;
2198
2199 IF (l_item_group_rec.ATTRIBUTE4 is NULL) THEN
2200 l_item_group_rec.ATTRIBUTE4 := l_old_item_group_rec.ATTRIBUTE4;
2201 ELSIF (l_item_group_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
2202 l_item_group_rec.ATTRIBUTE4 := NULL;
2203 END IF;
2204
2205 IF (l_item_group_rec.ATTRIBUTE6 is NULL) THEN
2206 l_item_group_rec.ATTRIBUTE6 := l_old_item_group_rec.ATTRIBUTE6;
2207 ELSIF (l_item_group_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
2208 l_item_group_rec.ATTRIBUTE6 := NULL;
2209 END IF;
2210
2211 IF (l_item_group_rec.ATTRIBUTE7 is NULL) THEN
2212 l_item_group_rec.ATTRIBUTE7 := l_old_item_group_rec.ATTRIBUTE7;
2213 ELSIF (l_item_group_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
2214 l_item_group_rec.ATTRIBUTE7 := NULL;
2215 END IF;
2216
2217 IF (l_item_group_rec.ATTRIBUTE8 is NULL) THEN
2218 l_item_group_rec.ATTRIBUTE8 := l_old_item_group_rec.ATTRIBUTE8;
2219 ELSIF (l_item_group_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
2220 l_item_group_rec.ATTRIBUTE8 := NULL;
2221 END IF;
2222
2223 IF (l_item_group_rec.ATTRIBUTE9 is NULL) THEN
2224 l_item_group_rec.ATTRIBUTE9 := l_old_item_group_rec.ATTRIBUTE9;
2225 ELSIF (l_item_group_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
2226 l_item_group_rec.ATTRIBUTE9 := NULL;
2227 END IF;
2228
2229 IF (l_item_group_rec.ATTRIBUTE10 is NULL) THEN
2230 l_item_group_rec.ATTRIBUTE10 := l_old_item_group_rec.ATTRIBUTE10;
2231 ELSIF (l_item_group_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
2232 l_item_group_rec.ATTRIBUTE10 := NULL;
2233 END IF;
2234
2235 IF (l_item_group_rec.ATTRIBUTE11 is NULL) THEN
2236 l_item_group_rec.ATTRIBUTE11 := l_old_item_group_rec.ATTRIBUTE11;
2237 ELSIF (l_item_group_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
2238 l_item_group_rec.ATTRIBUTE11 := NULL;
2239 END IF;
2240
2241 IF (l_item_group_rec.ATTRIBUTE12 is NULL) THEN
2242 l_item_group_rec.ATTRIBUTE12 := l_old_item_group_rec.ATTRIBUTE12;
2243 ELSIF (l_item_group_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
2244 l_item_group_rec.ATTRIBUTE12 := NULL;
2245 END IF;
2246
2247 IF (l_item_group_rec.ATTRIBUTE13 is NULL) THEN
2248 l_item_group_rec.ATTRIBUTE13 := l_old_item_group_rec.ATTRIBUTE13;
2249 ELSIF (l_item_group_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
2250 l_item_group_rec.ATTRIBUTE13 := NULL;
2251 END IF;
2252
2253 IF (l_item_group_rec.ATTRIBUTE14 is NULL) THEN
2254 l_item_group_rec.ATTRIBUTE14 := l_old_item_group_rec.ATTRIBUTE14;
2255 ELSIF (l_item_group_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
2256 l_item_group_rec.ATTRIBUTE14 := NULL;
2257 END IF;
2258
2259 IF (l_item_group_rec.ATTRIBUTE15 is NULL) THEN
2260 l_item_group_rec.ATTRIBUTE15 := l_old_item_group_rec.ATTRIBUTE15;
2261 ELSIF (l_item_group_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
2262 l_item_group_rec.ATTRIBUTE15 := NULL;
2263 END IF;
2264
2265 /*
2266 IF (l_item_group_rec.SOURCE_LANG is NULL) THEN
2267 l_item_group_rec.SOURCE_LANG := l_old_item_group_rec.SOURCE_LANG;
2268 ELSIF (l_item_group_rec.SOURCE_LANG = FND_API.G_MISS_CHAR) THEN
2269 l_item_group_rec.SOURCE_LANG := NULL;
2270 END IF;
2271 */
2272
2273 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2274 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2275 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Before calling Item Group Table Handler');
2276 END IF;
2277
2278 AHL_ITEM_GROUPS_PKG.UPDATE_ROW(
2279 X_ITEM_GROUP_ID => l_old_item_group_rec.item_group_id,
2280 X_TYPE_CODE => l_old_item_group_rec.type_code,
2281 X_STATUS_CODE => l_old_item_group_rec.status_code,
2282 X_SOURCE_ITEM_GROUP_ID => l_old_item_group_rec.source_item_group_id,
2283 X_OBJECT_VERSION_NUMBER => l_old_item_group_rec.OBJECT_VERSION_NUMBER + 1,
2284 X_NAME => l_old_item_group_rec.name,
2285 X_ATTRIBUTE_CATEGORY => l_item_group_rec.ATTRIBUTE_CATEGORY,
2286 X_ATTRIBUTE1 => l_item_group_rec.ATTRIBUTE1,
2287 X_ATTRIBUTE2 => l_item_group_rec.ATTRIBUTE2,
2288 X_ATTRIBUTE3 => l_item_group_rec.ATTRIBUTE3,
2289 X_ATTRIBUTE4 => l_item_group_rec.ATTRIBUTE4,
2290 X_ATTRIBUTE5 => l_item_group_rec.ATTRIBUTE5,
2291 X_ATTRIBUTE6 => l_item_group_rec.ATTRIBUTE6,
2292 X_ATTRIBUTE7 => l_item_group_rec.ATTRIBUTE7,
2293 X_ATTRIBUTE8 => l_item_group_rec.ATTRIBUTE8,
2294 X_ATTRIBUTE9 => l_item_group_rec.ATTRIBUTE9,
2295 X_ATTRIBUTE10 => l_item_group_rec.ATTRIBUTE10,
2296 X_ATTRIBUTE11 => l_item_group_rec.ATTRIBUTE11,
2297 X_ATTRIBUTE12 => l_item_group_rec.ATTRIBUTE12,
2298 X_ATTRIBUTE13 => l_item_group_rec.ATTRIBUTE13,
2299 X_ATTRIBUTE14 => l_item_group_rec.ATTRIBUTE14,
2300 X_ATTRIBUTE15 => l_item_group_rec.ATTRIBUTE15,
2301 X_DESCRIPTION => l_item_group_rec.DESCRIPTION,
2302 X_LAST_UPDATE_DATE => sysdate,
2303 X_LAST_UPDATED_BY => fnd_global.USER_ID,
2304 X_LAST_UPDATE_LOGIN => fnd_global.LOGIN_ID);
2305
2306 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2307 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2308 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'After Table Handler');
2309 END IF;
2310
2311
2312 END IF; /* update only if operation_flag set */
2313 -- End Updates for Item_Groups.
2314
2315 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2316 FND_LOG.string(FND_LOG.level_statement,l_full_name,'l_old_item_group_rec.source_item_group_id => '||
2317 l_old_item_group_rec.source_item_group_id);
2318 END IF;
2319
2320 -- SATHAPLI::Bug# 5566764 fix
2321 -- Checking whether the item group being updated is a new revision or not
2322 -- The validation of the revision update will be done only for revisions
2323 IF l_old_item_group_rec.source_item_group_id IS NOT NULL THEN
2324
2325 -- SATHAPLI::Bug# 4328454 fix
2326 -- Validate the deleted items before actually deleting them
2327 IF (p_x_items_tbl.COUNT > 0 ) THEN
2328 FOR i IN p_x_items_tbl.FIRST..p_x_items_tbl.LAST LOOP
2329 IF (p_x_items_tbl(i).operation_flag = 'D') THEN
2330 get_Item_detail(
2331 p_assoc_id => p_x_items_tbl(i).item_association_id,
2332 x_item_group_id => l_invalid_item_instance_tbl(l_index).item_group_id,
2333 x_inventory_item_id => l_invalid_item_instance_tbl(l_index).inventory_item_id,
2334 x_inventory_org_id => l_invalid_item_instance_tbl(l_index).inventory_org_id,
2335 x_concatenated_segments => l_invalid_item_instance_tbl(l_index).concatenated_segments,
2336 x_revision => l_invalid_item_instance_tbl(l_index).revision
2337 );
2338 l_index := l_index + 1;
2339 END IF;
2340 END LOOP;
2341 END IF;
2342
2343 IF (l_index > 1) THEN
2344 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2345 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Validating '||l_index||
2346 ' items in the IG for remove.');
2347 END IF;
2348
2349 AHL_UTIL_UC_PKG.Check_Invalidate_Instance
2350 (
2351 p_api_version => 1.0,
2352 p_init_msg_list => FND_API.G_FALSE,
2353 p_instance_tbl => l_invalid_item_instance_tbl,
2354 p_operator => 'D',
2355 x_return_status => x_return_status,
2356 x_msg_count => x_msg_count,
2357 x_msg_data => x_msg_data
2358 );
2359 END IF;
2360
2361 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2362 RAISE FND_API.G_EXC_ERROR;
2363 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2365 END IF;
2366
2367 END IF; -- end of l_old_item_group_rec.source_item_group_id IS NOT NULL
2368
2369 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2370 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2371 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Item Association Loop');
2372 END IF;
2373
2374 -- Process Item Associations table.
2375 -- Priyan . Changed the order of the DML flag checks made for item group associations.
2376 --Bug # 4330922
2377 IF (p_x_items_tbl.COUNT > 0 ) THEN
2378 FOR i IN p_x_items_tbl.FIRST..p_x_items_tbl.LAST LOOP
2379 IF (p_x_items_tbl(i).operation_flag = 'D') THEN
2380 Delete_Association(p_x_items_tbl(i),l_rowid_tbl(i));
2381 ELSIF (p_x_items_tbl(i).operation_flag = 'M') THEN
2382 Update_Association(p_x_items_tbl(i), l_rowid_tbl(i));
2383
2384 -- SATHAPLI::Bug# 4328454 fix
2385 l_update_flag := 'Y';
2386
2387 ELSIF (p_x_items_tbl(i).operation_flag = 'C') THEN
2388 Create_Association(p_x_items_tbl(i));
2389 END IF;
2390 END LOOP;
2391 END IF;
2392
2393 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2394 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2395 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'End of Loop');
2396 END IF;
2397
2398 -- The validation of the revision update will be done only for revisions
2399 IF l_old_item_group_rec.source_item_group_id IS NOT NULL THEN
2400
2401 -- SATHAPLI::Bug# 4328454 fix
2402 -- Validate updation of interchange_type_code
2403 IF (l_update_flag = 'Y') THEN
2404 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2405 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Calling validate_IG_update.');
2406 END IF;
2407
2408 validate_IG_update
2409 (
2410 p_ItemGroup_id => p_item_group_rec.item_group_id,
2411 x_return_status => x_return_status,
2412 x_msg_count => x_msg_count,
2413 x_msg_data => x_msg_data
2414 );
2415 END IF;
2416
2417 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2418 RAISE FND_API.G_EXC_ERROR;
2419 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2421 END IF;
2422
2423 END IF; -- end of l_old_item_group_rec.source_item_group_id IS NOT NULL
2424
2425 CLOSE Item_group_csr;
2426
2427 -- Validate priority for duplicate.
2428 validate_priority(l_old_item_group_rec.item_group_id);
2429
2430 --Priyan for mass update
2431 --Validate revision
2432 --Bug # 4330922
2433 validate_IG_revision(l_old_item_group_rec.item_group_id);
2434 --Priyan End
2435
2436 -- Standard call to get message count and if count is 1, get message info
2437 FND_MSG_PUB.Count_And_Get
2438 ( p_count => x_msg_count,
2439 p_data => x_msg_data,
2440 p_encoded => fnd_api.g_false);
2441
2442 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2443 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2444 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group',
2445 'End of Modify_Item_group...x_return_status => '||x_return_status);
2446 END IF;
2447
2448
2449 EXCEPTION
2450 WHEN FND_API.G_EXC_ERROR THEN
2451 x_return_status := FND_API.G_RET_STS_ERROR;
2452 Rollback to Modify_Item_group_pvt;
2453 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2454 p_data => x_msg_data,
2455 p_encoded => fnd_api.g_false);
2456 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
2457
2458 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2459 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2460 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Error in Modify_Item_group');
2461 END IF;
2462
2463
2464 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2466 Rollback to Modify_Item_group_pvt;
2467 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2468 p_data => x_msg_data,
2469 p_encoded => fnd_api.g_false);
2470 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
2471 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2472 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2473 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Unexpected error in Modify_Item_group');
2474 END IF;
2475
2476
2477 WHEN OTHERS THEN
2478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2479 Rollback to Modify_Item_group_pvt;
2480 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2481 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2482 p_procedure_name => 'Modify_Item_Group',
2483 p_error_text => SUBSTR(SQLERRM,1,240));
2484 END IF;
2485 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2486 p_data => x_msg_data,
2487 p_encoded => fnd_api.g_false);
2488 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
2489
2490 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2491 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2492 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', 'Unknown Error in Modify_Item_group');
2493 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2494 'ahl.plsql.ahl_mc_itemgroup_pvt.Modify_Item_group', SQLERRM);
2495 END IF;
2496
2497
2498 END Modify_Item_group;
2499
2500
2501 -- Start of Comments --
2502 -- Procedure name : Remove_Item_group
2503 -- Type : Private
2504 -- Function : Deletes an Item Group and associated item associations.
2505 -- Pre-reqs :
2506 -- Parameters :
2507 -- Standard IN Parameters :
2508 PROCEDURE Remove_Item_group(p_api_version IN NUMBER,
2509 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2510 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2511 x_return_status OUT NOCOPY VARCHAR2,
2512 x_msg_count OUT NOCOPY NUMBER,
2513 x_msg_data OUT NOCOPY VARCHAR2,
2514 p_item_group_rec IN AHL_MC_ITEMGROUP_PVT.Item_Group_Rec_Type
2515 ) IS
2516
2517 l_api_name CONSTANT VARCHAR2(30) := 'Remove_Item_Group';
2518 l_api_version CONSTANT NUMBER := 1.0;
2519
2520
2521 CURSOR Item_group_csr(p_item_group_id IN NUMBER) IS
2522 SELECT
2523 b.ROWID ROW_ID,
2524 b.ITEM_GROUP_ID,
2525 b.OBJECT_VERSION_NUMBER,
2526 b.NAME,
2527 b.Status_Code,
2528 b.source_item_group_id
2529 FROM
2530 AHL_ITEM_GROUPS_B b
2531 WHERE
2532 b.ITEM_GROUP_ID = p_item_group_id
2533 FOR UPDATE OF b.OBJECT_VERSION_NUMBER NOWAIT;
2534
2535 /*
2536 CURSOR ahl_relationships_csr(p_item_group_id IN NUMBER) IS
2537 SELECT 'x'
2538 from ahl_mc_relationships_v posn, ahl_mc_relationships_v topnode
2539 where trunc(nvl(posn.active_end_date,sysdate+1)) > trunc(sysdate)
2540 and posn.item_group_id = p_item_group_id
2541 and topnode.relationship_id = (SELECT reln.relationship_id from
2542 ahl_mc_relationships reln
2543 where parent_relationship_id is null
2544 start with relationship_id = posn.relationship_id
2545 and trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate)
2546 connect by prior parent_relationship_id = relationship_id
2547 and trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate)
2548 );
2549 */
2550
2551 CURSOR ahl_relationships_csr(p_item_group_id IN NUMBER) IS
2552 SELECT 'x'
2553 FROM ahl_mc_relationships
2554 WHERE trunc(nvl(active_end_date, sysdate + 1)) > trunc(sysdate)
2555 AND item_group_id = p_item_group_id;
2556
2557 CURSOR ahl_item_comp_csr(p_item_group_id IN NUMBER) IS
2558 SELECT 'x'
2559 FROM ahl_item_comp_details
2560 WHERE trunc(nvl(effective_end_date, sysdate + 1)) > trunc(sysdate)
2561 AND item_group_id = p_item_group_id;
2562
2563 l_item_group_rec Item_group_csr%ROWTYPE;
2564 l_dummy VARCHAR2(1);
2565
2566 BEGIN
2567
2568 -- Standard start of API savepoint
2569 SAVEPOINT Remove_Item_group_Pvt;
2570
2571 -- Standard call to check for call compatibility
2572 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
2573 G_PKG_NAME) THEN
2574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2575 END IF;
2576
2577 -- Initialize message list if p_init_msg_list is set to TRUE
2578 IF FND_API.To_Boolean(p_init_msg_list) THEN
2579 FND_MSG_PUB.Initialize;
2580 END IF;
2581
2582 -- Initialize API return status to success
2583 x_return_status := FND_API.G_RET_STS_SUCCESS;
2584
2585 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2586 THEN
2587 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2588 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'Begin of Remove_Item_group');
2589 END IF;
2590
2591
2592 -- Validate Item Group record.
2593 OPEN Item_group_csr(p_item_group_rec.item_group_id);
2594 FETCH Item_group_csr INTO l_item_group_rec;
2595 IF (Item_group_csr%NOTFOUND) THEN
2596 CLOSE Item_group_csr;
2597 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_INVALID');
2598 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_group_rec.name);
2599 FND_MSG_PUB.ADD;
2600 --dbms_output.put_line('Item Group does not exist');
2601 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2602 END IF;
2603
2604 -- Check Object version number.
2605 IF (l_item_group_rec.object_version_number <> p_item_group_rec.object_version_number) THEN
2606 CLOSE Item_group_csr;
2607 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
2608 FND_MSG_PUB.ADD;
2609 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2610 END IF;
2611
2612 -- Check if this item group has any positions associated.
2613 OPEN ahl_relationships_csr(p_item_group_rec.item_group_id);
2614 FETCH ahl_relationships_csr INTO l_dummy;
2615 IF (ahl_relationships_csr%FOUND) THEN
2616 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_POSN_EXISTS');
2617 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_group_rec.name);
2618 FND_MSG_PUB.ADD;
2619 --dbms_output.put_line('Positions exist for this item group');
2620 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2621 END IF;
2622
2623 CLOSE ahl_relationships_csr;
2624
2625 -- Check if this item group has any composition associated.
2626 OPEN ahl_item_comp_csr(p_item_group_rec.item_group_id);
2627 FETCH ahl_item_comp_csr INTO l_dummy;
2628 IF (ahl_item_comp_csr%FOUND) THEN
2629 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_COMP_EXISTS');
2630 FND_MESSAGE.Set_Token('ITEM_GRP',l_item_group_rec.name);
2631 FND_MSG_PUB.ADD;
2632 --dbms_output.put_line('Positions exist for this item group');
2633 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2634 END IF;
2635
2636 CLOSE ahl_item_comp_csr;
2637
2638
2639 -- Coded for 11.5.10
2640
2641 IF l_item_group_rec.status_code ='APPROVAL_PENDING'
2642 THEN
2643 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_APPR_PEND');
2644 FND_MSG_PUB.ADD;
2645 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2646 END IF;
2647
2648
2649 IF l_item_group_rec.status_code ='REMOVED'
2650 THEN
2651 FND_MESSAGE.Set_Name('AHL','AHL_MC_ITEMGRP_REMOVED');
2652 FND_MSG_PUB.ADD;
2653 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2654 END IF;
2655
2656
2657 IF l_item_group_rec.status_code IN ('DRAFT' ,'APPROVAL_REJECTED')
2658 THEN
2659
2660 IF (l_item_group_rec.status_code = 'DRAFT' and nvl(l_item_group_rec.source_item_group_id, 0) > 0)
2661 THEN
2662 UPDATE ahl_mc_relationships
2663 SET temp_item_group_id = null
2664 WHERE item_group_id = p_item_group_rec.item_group_id;
2665 END IF;
2666
2667 -- Delete item associations.
2668 /* AHL_ITEM_ASSOCIATIONS_PKG.DELETE_ROW(
2669 X_ITEM_ASSOCIATION_ID => p_item_group_rec.item_group_id
2670 );
2671
2672 */
2673
2674 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2675 THEN
2676 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2677 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'Deleting Item Group');
2678 END IF;
2679
2680 DELETE AHL_ITEM_ASSOCIATIONS_TL
2681 WHERE item_association_id IN ( SELECT item_association_id
2682 FROM ahl_item_associations_b
2683 WHERE item_group_id = p_item_group_rec.item_group_id );
2684
2685 DELETE AHL_ITEM_ASSOCIATIONS_B
2686 WHERE item_group_id = p_item_group_rec.item_group_id;
2687
2688 -- Delete ahl_item_groups
2689 AHL_ITEM_GROUPS_PKG.DELETE_ROW(
2690 X_ITEM_GROUP_ID => p_item_group_rec.item_group_id
2691 );
2692
2693 ELSIF l_item_group_rec.status_code ='COMPLETE'
2694 THEN
2695
2696 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2697 THEN
2698 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2699 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'Updating Item Group to Removed');
2700 END IF;
2701
2702 Update Ahl_Item_groups_b
2703 set status_code ='REMOVED',
2704 object_version_number = object_version_number +1
2705 Where item_group_id = p_item_group_rec.item_group_id;
2706
2707 --Update Ahl_item_associations_b
2708 -- set INTERCHANGE_TYPE_CODE = 'REMOVED',
2709 -- object_version_number = object_version_number +1
2710 -- Where item_group_id = p_item_group_rec.item_group_id;
2711
2712 END IF;
2713
2714 CLOSE Item_group_csr;
2715
2716 -- Standard call to get message count and if count is 1, get message info
2717 FND_MSG_PUB.Count_And_Get
2718 ( p_count => x_msg_count,
2719 p_data => x_msg_data,
2720 p_encoded => fnd_api.g_false);
2721
2722 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2723 THEN
2724 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2725 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'End of Remove_Item_group');
2726 END IF;
2727
2728
2729
2730 EXCEPTION
2731 WHEN FND_API.G_EXC_ERROR THEN
2732 x_return_status := FND_API.G_RET_STS_ERROR;
2733 Rollback to Remove_Item_group_pvt;
2734 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2735 p_data => x_msg_data,
2736 p_encoded => fnd_api.g_false);
2737
2738 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2739 THEN
2740 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2741 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'Error in Remove_Item_group');
2742 END IF;
2743
2744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2746 Rollback to Remove_Item_group_pvt;
2747 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2748 p_data => x_msg_data,
2749 p_encoded => fnd_api.g_false);
2750 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2751 THEN
2752 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2753 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'Unexpected error in Remove_Item_group');
2754 END IF;
2755
2756
2757 WHEN OTHERS THEN
2758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2759 Rollback to Remove_Item_group_pvt;
2760 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2761 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2762 p_procedure_name => 'Remove_Item_Group',
2763 p_error_text => SUBSTR(SQLERRM,1,240));
2764 END IF;
2765 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2766 p_data => x_msg_data,
2767 p_encoded => fnd_api.g_false);
2768 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2769 THEN
2770 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2771 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', 'Unknown error in Remove_Item_group');
2772 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2773 'ahl.plsql.ahl_mc_itemgroup_pvt.Remove_Item_group', SQLERRM);
2774 END IF;
2775
2776
2777 END Remove_Item_group;
2778
2779 -- Start of Comments --
2780 -- Procedure name : Initiate_Itemgroup_Appr
2781 -- Type : Private
2782 -- Function : Intiates Approval Process for Item groups
2783 --
2784 -- Pre-reqs :
2785 -- Parameters :
2786 -- Standard IN Parameters :
2787 -- p_api_version IN NUMBER Required
2788 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2789 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2790 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2791 --
2792 -- Standard OUT Parameters :
2793 -- x_return_status OUT VARCHAR2 Required
2794 -- x_msg_count OUT NUMBER Required
2795 -- x_msg_data OUT VARCHAR2 Required
2796 --
2797 -- Source_Item_Group_id Required.
2798 -- Object_version_number Required.
2799 -- Approval type Required.
2800 --
2801 -- Enhancement 115.10
2802 -- End of Comments --
2803 PROCEDURE Initiate_Itemgroup_Appr (
2804 p_api_version IN NUMBER,
2805 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2806 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2807 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2808 p_default IN VARCHAR2 := FND_API.G_FALSE,
2809 p_module_type IN VARCHAR2,
2810 x_return_status OUT NOCOPY VARCHAR2,
2811 x_msg_count OUT NOCOPY NUMBER,
2812 x_msg_data OUT NOCOPY VARCHAR2,
2813 p_source_item_group_id IN NUMBER,
2814 p_object_version_number IN NUMBER,
2815 p_approval_type IN VARCHAR2
2816 )
2817 IS
2818
2819 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_Itemgroup_Appr';
2820 l_api_version CONSTANT NUMBER := 1.0;
2821
2822 l_counter NUMBER:=0;
2823 l_object VARCHAR2(30):='IGWF';
2824 l_approval_type VARCHAR2(100):='CONCEPT';
2825 l_active VARCHAR2(50) := 'N';
2826 l_process_name VARCHAR2(50);
2827 l_item_type VARCHAR2(50);
2828 l_return_status VARCHAR2(50);
2829 l_msg_count NUMBER;
2830 l_msg_data VARCHAR2(2000);
2831 l_activity_id NUMBER:=p_source_item_group_id;
2832 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
2833 l_object_Version_number NUMBER:=nvl(p_object_Version_number,0);
2834
2835 l_upd_status VARCHAR2(50);
2836 l_rev_status VARCHAR2(50);
2837
2838
2839
2840 CURSOR get_Itemgroup_Det(c_itemgroup_id NUMBER)
2841 is
2842 Select Name,
2843 Status_code,
2844 Object_version_number,
2845 source_item_group_id
2846 From ahl_item_groups_vl
2847 Where item_group_id = c_itemgroup_id;
2848
2849 CURSOR validate_item_group(c_itemgroup_id NUMBER)
2850 is
2851 Select 'x' from dual
2852 Where exists ( select item_association_id from ahl_item_associations_vl
2853 Where Item_Group_id = c_itemgroup_id and
2854 INTERCHANGE_TYPE_CODE in ('1-WAY INTERCHANGEABLE','2-WAY INTERCHANGEABLE') );
2855
2856 CURSOR validate_item_group_positions(c_itemgroup_id NUMBER)
2857 is
2858 select 'x'
2859 from ahl_mc_relationships a, ahl_mc_relationships b
2860 where b.RELATIONSHIP_ID = a.PARENT_RELATIONSHIP_ID
2861 and a.ITEM_GROUP_ID = c_itemgroup_id
2862 and exists
2863 ( select 'x'
2864 from ahl_item_associations_b
2865 where item_group_id = c_itemgroup_id
2866 and quantity > 1 );
2867
2868
2869 CURSOR Item_group_name(p_name VARCHAR2) IS
2870 select 'x'
2871 from ahl_item_groups_b
2872 where name = p_name and
2873 p_source_item_group_id <> item_group_id;
2874
2875
2876 l_itemgroup_rec get_Itemgroup_Det%rowtype;
2877
2878
2879 l_msg VARCHAR2(30);
2880 l_dummy VARCHAR2(1);
2881
2882 l_appr_status VARCHAR2(30) :='APPROVED';
2883 l_fork_or_merge NUMBER;
2884
2885
2886 BEGIN
2887 SAVEPOINT Initiate_Itemgroup_Appr;
2888
2889 -- Standard call to check for call compatibility.
2890
2891 IF FND_API.to_boolean(l_init_msg_list) THEN
2892 FND_MSG_PUB.initialize;
2893 END IF;
2894
2895 -- Initialize API return status to success
2896
2897 x_return_status :=FND_API.G_RET_STS_SUCCESS;
2898
2899 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
2900 THEN
2901 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2902 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Begin Initiate_Itemgroup_Appr');
2903 END IF;
2904
2905
2906
2907
2908 -- Start work Flow Process
2909 ahl_utility_pvt.get_wf_process_name(
2910 p_object =>l_object,
2911 x_active =>l_active,
2912 x_process_name =>l_process_name ,
2913 x_item_type =>l_item_type,
2914 x_return_status=>l_return_status,
2915 x_msg_count =>l_msg_count,
2916 x_msg_data =>l_msg_data);
2917
2918 IF p_object_Version_number is null or p_object_Version_number=FND_API.G_MISS_NUM
2919 THEN
2920 FND_MESSAGE.SET_NAME('AHL','AHL_MC_OBJ_VERSION_NULL');
2921 FND_MSG_PUB.ADD;
2922 END IF;
2923
2924 IF p_source_item_group_id is null or p_source_item_group_id = FND_API.G_MISS_NUM
2925 THEN
2926 FND_MESSAGE.SET_NAME('AHL','AHL_MC_OBJECT_ID_NULL');
2927 FND_MSG_PUB.ADD;
2928 ELSE
2929 OPEN get_Itemgroup_Det(p_source_item_group_id);
2930 FETCH get_Itemgroup_Det INTO l_itemgroup_rec;
2931 CLOSE get_Itemgroup_Det;
2932
2933 IF l_itemgroup_rec.source_item_group_id IS NOT NULL
2934 AND Fork_Or_Merge(p_source_item_group_id) = 0
2935 THEN
2936 OPEN Item_group_name(l_itemgroup_rec.name);
2937 FETCH Item_group_name INTO l_dummy;
2938 IF Item_group_name%FOUND
2939 THEN
2940 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IG_MOD_NAME');
2941 FND_MSG_PUB.ADD;
2942 CLOSE Item_group_name;
2943 RAISE FND_API.G_EXC_ERROR;
2944 END IF;
2945 CLOSE Item_group_name;
2946 END IF;
2947
2948
2949 IF p_approval_type = 'APPROVE'
2950 THEN
2951 IF l_itemgroup_rec.status_code='DRAFT' or
2952 l_itemgroup_rec.status_code='APPROVAL_REJECTED'
2953 THEN
2954 l_upd_status := 'APPROVAL_PENDING';
2955 l_fork_or_merge := Fork_Or_Merge(p_source_item_group_id);
2956
2957 IF (l_fork_or_merge = 0)
2958 THEN
2959 Validate_Item_Group_Name(l_itemgroup_rec.name, p_source_item_group_id, null);
2960 ELSE
2961 Validate_Item_Group_Name(l_itemgroup_rec.name, p_source_item_group_id, l_itemgroup_rec.source_item_group_id);
2962 END IF;
2963
2964 ELSE
2965 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IG_STAT_NOT_DRFT');
2966 FND_MESSAGE.set_token('IG',l_itemgroup_rec.name,false);
2967 FND_MSG_PUB.ADD;
2968 END IF;
2969 ELSE
2970 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_TYPE_CODE_MISSING');
2971 FND_MSG_PUB.ADD;
2972 END IF;
2973
2974 OPEN validate_item_group(p_source_item_group_id);
2975 FETCH validate_item_group INTO l_dummy;
2976 IF validate_item_group%NOTFOUND THEN
2977 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IG_INTERCHANGE_INAVLID');
2978 FND_MSG_PUB.ADD;
2979 END IF;
2980 CLOSE validate_item_group;
2981
2982 OPEN validate_item_group_positions(p_source_item_group_id);
2983 FETCH validate_item_group_positions INTO l_dummy;
2984 IF validate_item_group_positions%FOUND THEN
2985 FND_MESSAGE.SET_NAME('AHL','AHL_MC_PAR_QTY_INV');
2986 FND_MESSAGE.set_token('POSREF','');
2987 FND_MSG_PUB.ADD;
2988 END IF;
2989 CLOSE validate_item_group_positions;
2990
2991
2992
2993 END IF;
2994
2995 l_msg_count := FND_MSG_PUB.count_msg;
2996
2997 IF l_msg_count > 0
2998 THEN
2999 X_msg_count := l_msg_count;
3000 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3001 RAISE FND_API.G_EXC_ERROR;
3002 END IF;
3003
3004
3005 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3006 THEN
3007 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3008 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'l_active flag is yes');
3009 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3010 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Updating Item group');
3011 END IF;
3012
3013 Update AHL_ITEM_GROUPS_B
3014 Set STATUS_CODE=l_upd_status,
3015 OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
3016 Where ITEM_GROUP_ID = p_source_item_group_id
3017 and OBJECT_VERSION_NUMBER=p_object_Version_number;
3018
3019 IF sql%rowcount=0
3020 THEN
3021 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
3022 FND_MSG_PUB.ADD;
3023 RAISE FND_API.G_EXC_ERROR;
3024 END IF;
3025
3026 IF l_ACTIVE='Y'
3027 THEN
3028
3029
3030 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3031 THEN
3032 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3033 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Calling ahl_generic_aprv_pvt.start_wf_process');
3034 END IF;
3035
3036 AHL_GENERIC_APRV_PVT.START_WF_PROCESS(
3037 P_OBJECT =>l_object,
3038 P_ACTIVITY_ID =>l_activity_id,
3039 P_APPROVAL_TYPE =>'CONCEPT',
3040 P_OBJECT_VERSION_NUMBER =>l_object_version_number+1,
3041 P_ORIG_STATUS_CODE =>'ACTIVE',
3042 P_NEW_STATUS_CODE =>'APPROVED',
3043 P_REJECT_STATUS_CODE =>'REJECTED',
3044 P_REQUESTER_USERID =>fnd_global.user_id,
3045 P_NOTES_FROM_REQUESTER =>NULL,
3046 P_WORKFLOWPROCESS =>'AHL_GEN_APPROVAL',
3047 P_ITEM_TYPE =>'AHLGAPP');
3048 ELSE
3049
3050 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3051 THEN
3052 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3053 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Calling ahl_mc_itemgroup_pvt.Approve_ItemGroups');
3054 END IF;
3055
3056 AHL_MC_ITEMGROUP_PVT.Approve_ItemGroups
3057 (
3058 p_api_version =>l_api_version,
3059 -- p_init_msg_list =>l_init_msg_list,
3060 -- p_commit =>l_commit,
3061 -- p_validation_level =>NULL ,
3062 -- p_default =>NULL ,
3063 p_module_type =>NULL,
3064 x_return_status =>l_return_status,
3065 x_msg_count =>l_msg_count ,
3066 x_msg_data =>l_msg_data ,
3067 p_appr_status =>l_appr_status,
3068 p_ItemGroups_id =>p_source_item_group_id,
3069 p_object_version_number =>p_object_Version_number+1
3070 );
3071 END IF ;
3072
3073 l_msg_count := FND_MSG_PUB.count_msg;
3074
3075 IF l_msg_count > 0
3076 THEN
3077 X_msg_count := l_msg_count;
3078 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3079 RAISE FND_API.G_EXC_ERROR;
3080 END IF;
3081
3082
3083 -- Standard check of p_commit
3084 IF FND_API.TO_BOOLEAN(p_commit) THEN
3085 COMMIT WORK;
3086 END IF;
3087
3088 -- Standard call to get message count and if count is 1, get message info
3089 FND_MSG_PUB.Count_And_Get
3090 ( p_count => x_msg_count,
3091 p_data => x_msg_data,
3092 p_encoded => fnd_api.g_false);
3093
3094 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3095 THEN
3096 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3097 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'End of Initiate_Itemgroup_Appr');
3098 END IF;
3099
3100
3101 EXCEPTION
3102 WHEN FND_API.G_EXC_ERROR THEN
3103 x_return_status := FND_API.G_RET_STS_ERROR;
3104 Rollback to Initiate_Itemgroup_Appr;
3105 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3106 p_data => x_msg_data,
3107 p_encoded => fnd_api.g_false);
3108 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3109 THEN
3110 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3111 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Error in Initiate_Itemgroup_Appr');
3112 END IF;
3113
3114
3115
3116 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3118 Rollback to Initiate_Itemgroup_Appr;
3119 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3120 p_data => x_msg_data,
3121 p_encoded => fnd_api.g_false);
3122 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3123 THEN
3124 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3125 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Unexpected Error in Initiate_Itemgroup_Appr');
3126 END IF;
3127
3128
3129 WHEN OTHERS THEN
3130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3131 Rollback to Initiate_Itemgroup_Appr;
3132 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3133 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3134 p_procedure_name => 'Initiate_Itemgroup_Appr',
3135 p_error_text => SUBSTR(SQLERRM,1,240));
3136 END IF;
3137 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3138 p_data => x_msg_data,
3139 p_encoded => fnd_api.g_false);
3140
3141 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3142 THEN
3143 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3144 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', 'Unknown Error in Initiate_Itemgroup_Appr');
3145 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3146 'ahl.plsql.ahl_mc_itemgroup_pvt.Initiate_Itemgroup_Appr', SQLERRM);
3147 END IF;
3148
3149 END Initiate_Itemgroup_Appr;
3150
3151
3152 -- Start of Comments --
3153 -- Procedure name : Create_ItemGroup_Revision
3154 -- Type : Private
3155 -- Function : To create a New Revision of Item group
3156 --
3157 -- Pre-reqs :
3158 -- Parameters :
3159 -- Standard IN Parameters :
3160 -- p_api_version IN NUMBER Required
3161 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3162 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3163 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3164 --
3165 -- Standard OUT Parameters :
3166 -- x_return_status OUT VARCHAR2 Required
3167 -- x_msg_count OUT NUMBER Required
3168 -- x_msg_data OUT VARCHAR2 Required
3169
3170 -- Source_Item_Group_id Required.
3171 -- Object_version_number Required.
3172 -- Enhancement 115.10
3173 --
3174 -- End of Comments --
3175
3176 PROCEDURE Create_ItemGroup_Revision (
3177 p_api_version IN NUMBER,
3178 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3179 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3180 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3181 P_DEFAULT IN VARCHAR2 := FND_API.G_FALSE,
3182 P_MODULE_TYPE IN VARCHAR2,
3183 x_return_status OUT NOCOPY VARCHAR2,
3184 x_msg_count OUT NOCOPY NUMBER,
3185 x_msg_data OUT NOCOPY VARCHAR2,
3186 p_source_ItemGroup_id IN NUMBER,
3187 p_object_version_number IN NUMBER,
3188 x_ItemGroup_id OUT NOCOPY NUMBER
3189 ) AS
3190
3191 cursor get_itemgroup_det(c_itemgroup_id in Number)
3192 Is
3193 Select
3194 Name,
3195 Status_Code,
3196 Type_Code,
3197 Description,
3198 object_version_number ,
3199 attribute_category ,
3200 attribute1 ,
3201 attribute2 ,
3202 attribute3 ,
3203 attribute4 ,
3204 attribute5 ,
3205 attribute6 ,
3206 attribute7 ,
3207 attribute8 ,
3208 attribute9 ,
3209 attribute10 ,
3210 attribute11 ,
3211 attribute12 ,
3212 attribute13 ,
3213 attribute14 ,
3214 attribute15
3215 from ahl_item_groups_vl
3216 Where Item_Group_id = c_itemgroup_id;
3217
3218 l_itemgroups_det get_itemgroup_det%rowtype;
3219
3220 cursor get_itemgroup_assos_det(c_itemgroup_id in Number)
3221 Is
3222 Select
3223 item_association_id ,
3224 object_version_number ,
3225 item_group_id ,
3226 inventory_item_id ,
3227 inventory_org_id ,
3228 priority ,
3229 uom_code ,
3230 quantity ,
3231 revision ,
3232 interchange_type_code ,
3233 interchange_reason ,
3234 source_item_association_id,
3235 attribute_category ,
3236 attribute1 ,
3237 attribute2 ,
3238 attribute3 ,
3239 attribute4 ,
3240 attribute5 ,
3241 attribute6 ,
3242 attribute7 ,
3243 attribute8 ,
3244 attribute9 ,
3245 attribute10 ,
3246 attribute11 ,
3247 attribute12 ,
3248 attribute13 ,
3249 attribute14 ,
3250 attribute15
3251 from ahl_item_associations_vl
3252 where item_group_id = c_itemgroup_id;
3253
3254
3255 cursor get_revision_info(c_itemgroup_id in Number)
3256 is
3257 Select 'x'
3258 from ahl_item_groups_vl
3259 where source_item_group_id = c_itemgroup_id and
3260 status_code <> 'COMPLETE';
3261
3262 l_dummy VARCHAR2(1);
3263 l_msg_count Number;
3264 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
3265 l_itemgroup_det get_itemgroup_det%rowtype;
3266 l_item_group_id Number;
3267 l_last_update_login NUMBER;
3268 l_last_updated_by NUMBER;
3269 l_rowid VARCHAR2(30);
3270 l_item_association_id NUMBER;
3271 l_created_by NUMBER;
3272
3273 -- TAMAL -- IG Amendments --
3274 CURSOR get_mc_posisions (c_item_group_id in number)
3275 IS
3276 SELECT relationship_id, object_version_number
3277 FROM ahl_mc_relationships
3278 WHERE item_group_id = c_item_group_id;
3279 -- TAMAL -- IG Amendments --
3280
3281 BEGIN
3282
3283
3284 SAVEPOINT Create_ItemGroup_Revision;
3285
3286 -- Standard call to check for call compatibility.
3287
3288 IF FND_API.to_boolean(l_init_msg_list) THEN
3289 FND_MSG_PUB.initialize;
3290 END IF;
3291
3292 -- Initialize API return status to success
3293
3294 x_return_status :=FND_API.G_RET_STS_SUCCESS;
3295
3296 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3297 THEN
3298 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3299 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'Begin of Create_ItemGroup_Revision');
3300 END IF;
3301
3302
3303
3304 OPEN get_itemgroup_det(p_source_ItemGroup_id);
3305 Fetch get_itemgroup_det into l_itemgroup_det;
3306 IF get_itemgroup_det%NOTFOUND THEN
3307 FND_MESSAGE.SET_NAME('AHL','AHL_MC_OBJECT_ID_NULL');
3308 FND_MSG_PUB.ADD;
3309 END IF;
3310 close get_itemgroup_det;
3311
3312
3313 IF l_itemgroup_det.Status_Code <> 'COMPLETE'
3314 THEN
3315 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IG_STAT_NOT_COMP');
3316 FND_MESSAGE.Set_Token('IG',l_itemgroup_det.name);
3317 FND_MSG_PUB.ADD;
3318 END IF;
3319
3320
3321 IF l_itemgroup_det.object_version_number <> p_object_version_number
3322 THEN
3323 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD');
3324 FND_MSG_PUB.ADD;
3325 END IF;
3326
3327
3328 OPEN get_revision_info(p_source_ItemGroup_id);
3329 FETCH get_revision_info INTO l_dummy;
3330 IF get_revision_info%FOUND THEN
3331 FND_MESSAGE.SET_NAME('AHL','AHL_MC_IG_REVISION_EXIST');
3332 FND_MSG_PUB.ADD;
3333 END IF;
3334 CLOSE get_revision_info;
3335
3336
3337
3338 l_msg_count := FND_MSG_PUB.count_msg;
3339
3340 IF l_msg_count > 0
3341 THEN
3342 X_msg_count := l_msg_count;
3343 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3344 RAISE FND_API.G_EXC_ERROR;
3345 END IF;
3346
3347 -- Sequence Number for the New Revision.
3348
3349 Select AHL_ITEM_GROUPS_B_S.nextval
3350 into l_item_group_id
3351 from dual;
3352
3353 l_last_updated_by := to_number(fnd_global.USER_ID);
3354 l_last_update_login := to_number(fnd_global.LOGIN_ID);
3355 l_created_by := to_number(fnd_global.user_id);
3356
3357
3358 -- Inserting a new Revision in the Header Table Using table Handler
3359
3360 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3361 THEN
3362 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3363 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'Calling Table handler');
3364 END IF;
3365
3366
3367 ahl_item_groups_pkg.insert_row(
3368 x_rowid => l_rowid,
3369 x_item_group_id => l_item_group_id,
3370 x_type_code => l_itemgroup_det.type_code,
3371 x_status_code => 'DRAFT',
3372 x_source_item_group_id => p_source_ItemGroup_id,
3373 x_object_version_number => 1,
3374 x_name => l_itemgroup_det.name,
3375 x_attribute_category => l_itemgroup_det.attribute_category,
3376 x_attribute1 => l_itemgroup_det.attribute1,
3377 x_attribute2 => l_itemgroup_det.attribute2,
3378 x_attribute3 => l_itemgroup_det.attribute3,
3379 x_attribute4 => l_itemgroup_det.attribute4,
3380 x_attribute5 => l_itemgroup_det.attribute5,
3381 x_attribute6 => l_itemgroup_det.attribute6,
3382 x_attribute7 => l_itemgroup_det.attribute7,
3383 x_attribute8 => l_itemgroup_det.attribute8,
3384 x_attribute9 => l_itemgroup_det.attribute9,
3385 x_attribute10 => l_itemgroup_det.attribute10,
3386 x_attribute11 => l_itemgroup_det.attribute11,
3387 x_attribute12 => l_itemgroup_det.attribute12,
3388 x_attribute13 => l_itemgroup_det.attribute13,
3389 x_attribute14 => l_itemgroup_det.attribute14,
3390 x_attribute15 => l_itemgroup_det.attribute15,
3391 x_description => l_itemgroup_det.description,
3392 x_creation_date => sysdate,
3393 x_created_by => l_created_by,
3394 x_last_update_date => sysdate,
3395 x_last_updated_by => l_last_updated_by,
3396 x_last_update_login => l_last_update_login);
3397
3398
3399 x_ItemGroup_id := l_item_group_id;
3400
3401 -- TAMAL -- IG Amendments --
3402 FOR item_group_rec IN get_mc_posisions(p_source_itemgroup_id)
3403 LOOP
3404 UPDATE ahl_mc_relationships
3405 SET temp_item_group_id = x_itemgroup_id,
3406 object_version_number = item_group_rec.object_version_number,
3407 last_update_date = sysdate,
3408 last_updated_by = l_last_updated_by,
3409 last_update_login = l_last_update_login
3410 WHERE relationship_id = item_group_rec.relationship_id and
3411 trunc(nvl(active_end_date, sysdate + 1)) > trunc(sysdate);
3412
3413 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3414 THEN
3415 FND_LOG.STRING
3416 (
3417 FND_LOG.LEVEL_PROCEDURE,
3418 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision',
3419 'Updated MC position '||item_group_rec.relationship_id||' with temp_item_group_id '||x_itemgroup_id
3420 );
3421 END IF;
3422 END LOOP;
3423 -- TAMAL -- IG Amendments --
3424
3425 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3426 THEN
3427 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3428 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'Loop to create Item Association');
3429 END IF;
3430
3431 FOR I IN get_itemgroup_assos_det(p_source_ItemGroup_id)
3432 LOOP
3433
3434 --Gets the sequence Number
3435 SELECT AHL_ITEM_ASSOCIATIONS_B_S.nextval INTO
3436 l_item_association_id from DUAL;
3437
3438
3439 ahl_item_associations_pkg.insert_row
3440 (
3441 x_rowid => l_rowid,
3442 x_item_association_id => l_item_association_id,
3443 x_source_item_association_id => I.source_item_association_id,
3444 x_object_version_number => 1,
3445 x_item_group_id => l_item_group_id,
3446 x_inventory_item_id => I.inventory_item_id,
3447 x_inventory_org_id => I.INVENTORY_ORG_ID ,
3448 x_priority => I.PRIORITY ,
3449 x_uom_code => I.UOM_CODE ,
3450 x_quantity => I.QUANTITY ,
3451 x_revision => I.REVISION ,
3452 x_interchange_type_code => I.INTERCHANGE_TYPE_CODE ,
3453 x_item_type_code => null,
3454 x_attribute_category => I.ATTRIBUTE_CATEGORY,
3455 x_attribute1 => i.attribute1,
3456 x_attribute2 => i.attribute2,
3457 x_attribute3 => i.attribute3,
3458 x_attribute4 => i.attribute4,
3459 x_attribute5 => i.attribute5,
3460 x_attribute6 => i.attribute6,
3461 x_attribute7 => i.attribute7,
3462 x_attribute8 => i.attribute8,
3463 x_attribute9 => i.attribute9,
3464 x_attribute10 => i.attribute10,
3465 x_attribute11 => i.attribute11,
3466 x_attribute12 => i.attribute12,
3467 x_attribute13 => i.attribute13,
3468 x_attribute14 => i.attribute14,
3469 x_attribute15 => i.attribute15,
3470 x_interchange_reason => I.INTERCHANGE_REASON,
3471 x_creation_date => sysdate,
3472 x_created_by => l_created_by,
3473 x_last_update_date => sysdate,
3474 x_last_updated_by => l_last_updated_by,
3475 x_last_update_login => l_last_update_login
3476 );
3477 END LOOP;
3478
3479 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3480 THEN
3481 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3482 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'End of Loop');
3483 END IF;
3484
3485 -- Standard check of p_commit
3486 IF FND_API.TO_BOOLEAN(p_commit) THEN
3487 COMMIT WORK;
3488 END IF;
3489
3490 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3491 THEN
3492 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3493 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'End of Create_ItemGroup_Revision');
3494 END IF;
3495
3496
3497
3498 EXCEPTION
3499 WHEN FND_API.G_EXC_ERROR THEN
3500 x_return_status := FND_API.G_RET_STS_ERROR;
3501 Rollback to Create_ItemGroup_Revision;
3502 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3503 p_data => x_msg_data,
3504 p_encoded => fnd_api.g_false);
3505 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3506 THEN
3507 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3508 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'Error in Create_ItemGroup_Revision');
3509 END IF;
3510
3511
3512 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3514 Rollback to Create_ItemGroup_Revision;
3515 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3516 p_data => x_msg_data,
3517 p_encoded => fnd_api.g_false);
3518 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3519 THEN
3520 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3521 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'Unecpected Error in Create_ItemGroup_Revision');
3522 END IF;
3523
3524
3525 WHEN OTHERS THEN
3526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3527 Rollback to Create_ItemGroup_Revision;
3528 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3529 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3530 p_procedure_name => 'Create_ItemGroup_Revision',
3531 p_error_text => SUBSTR(SQLERRM,1,240));
3532 END IF;
3533 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3534 p_data => x_msg_data,
3535 p_encoded => fnd_api.g_false);
3536 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3537 THEN
3538 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3539 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision', 'Unknown Error in Create_ItemGroup_Revision');
3540 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3541 'ahl.plsql.ahl_mc_itemgroup_pvt.Create_ItemGroup_Revision',SQLERRM );
3542
3543 END IF;
3544
3545
3546
3547 END Create_ItemGroup_Revision;
3548
3549
3550 PROCEDURE update_histroy (
3551 p_ItemGroups_id IN NUMBER,
3552 p_action IN VARCHAR2
3553 )IS
3554 --
3555 cursor get_item_assos_det_csr(c_itemgroup_id in number)
3556 is
3557 Select
3558 item_association_id ,
3559 object_version_number ,
3560 last_update_date ,
3561 last_updated_by ,
3562 creation_date ,
3563 created_by ,
3564 last_update_login ,
3565 item_group_id ,
3566 inventory_item_id ,
3567 inventory_org_id ,
3568 priority ,
3569 uom_code ,
3570 quantity ,
3571 revision ,
3572 interchange_type_code ,
3573 interchange_reason ,
3574 item_type_code ,
3575 source_item_association_id,
3576 attribute_category ,
3577 attribute1 ,
3578 attribute2 ,
3579 attribute3 ,
3580 attribute4 ,
3581 attribute5 ,
3582 attribute6 ,
3583 attribute7 ,
3584 attribute8 ,
3585 attribute9 ,
3586 attribute10 ,
3587 attribute11 ,
3588 attribute12 ,
3589 attribute13 ,
3590 attribute14 ,
3591 attribute15
3592 from ahl_item_associations_vl
3593 where item_group_id = c_itemgroup_id;
3594 --
3595
3596 l_version_number NUMBER;
3597 l_item_associations_h_id NUMBER;
3598 l_item_group_h_id NUMBER;
3599 l_rowid VARCHAR2(30);
3600 l_item_assos_det get_item_assos_det_csr%ROWTYPE;
3601
3602 --
3603 BEGIN
3604
3605 -- To get the maximum of version number
3606 Select NVl(max(VERSION_NUMBER),0)
3607 into l_version_number
3608 from ahl_item_groups_b_h
3609 where ITEM_GROUP_ID = p_ItemGroups_id;
3610
3611
3612 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3613 THEN
3614 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3615 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Inserting into History Tables');
3616 END IF;
3617
3618
3619 Select ahl_item_associations_b_h_s.nextval
3620 into l_item_associations_h_id from dual;
3621
3622 INSERT INTO ahl_item_groups_b_h
3623 (item_group_h_id ,
3624 item_group_id ,
3625 object_version_number,
3626 creation_date ,
3627 created_by ,
3628 last_update_date ,
3629 last_updated_by ,
3630 name ,
3631 type_code ,
3632 status_code ,
3633 version_number ,
3634 transaction_date ,
3635 action ,
3636 source_item_group_id ,
3637 last_update_login ,
3638 attribute_category ,
3639 attribute1 ,
3640 attribute2 ,
3641 attribute3 ,
3642 attribute4 ,
3643 attribute5 ,
3644 attribute6 ,
3645 attribute7 ,
3646 attribute8 ,
3647 attribute9 ,
3648 attribute10 ,
3649 attribute11 ,
3650 attribute12 ,
3651 attribute13 ,
3652 attribute14 ,
3653 attribute15 )
3654
3655 SELECT
3656
3657 AHL_ITEM_GROUPS_B_H_S.NEXTVAL ,
3658 item_group_id ,
3659 object_version_number,
3660 creation_date ,
3661 created_by ,
3662 last_update_date ,
3663 last_updated_by ,
3664 name ,
3665 type_code ,
3666 status_code ,
3667 l_version_number+1 ,
3668 sysdate ,
3669 p_action ,
3670 source_item_group_id ,
3671 last_update_login ,
3672 attribute_category ,
3673 attribute1 ,
3674 attribute2 ,
3675 attribute3 ,
3676 attribute4 ,
3677 attribute5 ,
3678 attribute6 ,
3679 attribute7 ,
3680 attribute8 ,
3681 attribute9 ,
3682 attribute10 ,
3683 attribute11 ,
3684 attribute12 ,
3685 attribute13 ,
3686 attribute14 ,
3687 attribute15
3688 FROM ahl_item_groups_b
3689 WHERE item_group_id = p_ItemGroups_id;
3690
3691
3692 INSERT INTO ahl_item_groups_tl_h
3693 (item_group_h_id ,
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 SELECT
3703 AHL_ITEM_GROUPS_B_H_S.CURRVAL ,
3704 language ,
3705 last_update_date ,
3706 last_updated_by ,
3707 source_lang ,
3708 creation_date ,
3709 created_by ,
3710 description ,
3711 last_update_login
3712 FROM ahl_item_groups_tl
3713 WHERE item_group_id = p_ItemGroups_id;
3714
3715
3716
3717 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3718 THEN
3719 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3720 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Inserting Item Association into history table Start of Loop');
3721 END IF;
3722
3723
3724 FOR l_item_assos_det IN get_item_assos_det_csr(p_ItemGroups_id) LOOP
3725
3726 Select ahl_item_associations_b_h_s.nextval
3727 into l_item_associations_h_id from dual;
3728
3729 AHL_ITEM_ASSOCIATIONS_H_PKG.INSERT_ROW(
3730 X_ROWID => l_rowid,
3731 X_ITEM_ASSOCIATION_H_ID => l_item_associations_h_id ,
3732 X_ITEM_ASSOCIATION_ID => l_item_assos_det.ITEM_ASSOCIATION_ID ,
3733 X_ITEM_GROUP_ID => l_item_assos_det.ITEM_GROUP_ID ,
3734 X_OBJECT_VERSION_NUMBER => l_item_assos_det.OBJECT_VERSION_NUMBER ,
3735 X_INVENTORY_ITEM_ID => l_item_assos_det.INVENTORY_ITEM_ID ,
3736 X_INVENTORY_ORG_ID => l_item_assos_det.INVENTORY_ORG_ID ,
3737 X_PRIORITY => l_item_assos_det.PRIORITY ,
3738 X_TRANSACTION_DATE => sysdate,
3739 X_ACTION => p_action,
3740 X_SOURCE_ITEM_ASSOCIATION_ID => l_item_assos_det.SOURCE_ITEM_ASSOCIATION_ID,
3741 X_VERSION_NUMBER => l_version_number ,
3742 X_UOM_CODE => l_item_assos_det.UOM_CODE ,
3743 X_QUANTITY => l_item_assos_det.QUANTITY ,
3744 X_REVISION => l_item_assos_det.REVISION ,
3745 X_INTERCHANGE_TYPE_CODE => l_item_assos_det.INTERCHANGE_TYPE_CODE ,
3746 X_ATTRIBUTE_CATEGORY => l_item_assos_det.ATTRIBUTE_CATEGORY ,
3747 X_ATTRIBUTE1 => l_item_assos_det.ATTRIBUTE1 ,
3748 X_ATTRIBUTE2 => l_item_assos_det.ATTRIBUTE2 ,
3749 X_ATTRIBUTE3 => l_item_assos_det.ATTRIBUTE3 ,
3750 X_ATTRIBUTE4 => l_item_assos_det.ATTRIBUTE4 ,
3751 X_ATTRIBUTE5 => l_item_assos_det.ATTRIBUTE5 ,
3752 X_ATTRIBUTE6 => l_item_assos_det.ATTRIBUTE6 ,
3753 X_ATTRIBUTE7 => l_item_assos_det.ATTRIBUTE7 ,
3754 X_ATTRIBUTE8 => l_item_assos_det.ATTRIBUTE8 ,
3755 X_ATTRIBUTE9 => l_item_assos_det.ATTRIBUTE9 ,
3756 X_ATTRIBUTE10 => l_item_assos_det.ATTRIBUTE10 ,
3757 X_ATTRIBUTE11 => l_item_assos_det.ATTRIBUTE11 ,
3758 X_ATTRIBUTE12 => l_item_assos_det.ATTRIBUTE12 ,
3759 X_ATTRIBUTE13 => l_item_assos_det.ATTRIBUTE13 ,
3760 X_ATTRIBUTE14 => l_item_assos_det.ATTRIBUTE14 ,
3761 X_ATTRIBUTE15 => l_item_assos_det.ATTRIBUTE15 ,
3762 X_INTERCHANGE_REASON => l_item_assos_det.INTERCHANGE_REASON ,
3763 X_CREATION_DATE => l_item_assos_det.CREATION_DATE ,
3764 X_CREATED_BY => l_item_assos_det.CREATED_BY ,
3765 X_LAST_UPDATE_DATE => l_item_assos_det.LAST_UPDATE_DATE ,
3766 X_LAST_UPDATED_BY => l_item_assos_det.LAST_UPDATED_BY ,
3767 X_LAST_UPDATE_LOGIN => l_item_assos_det.LAST_UPDATE_LOGIN );
3768
3769 END LOOP;
3770
3771 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3772 THEN
3773 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3774 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'End of Loop');
3775 END IF;
3776
3777
3778 END update_histroy;
3779
3780
3781 -- Start of Comments --
3782 -- Procedure name : Approve_ItemGroups
3783 -- Type : Private
3784 -- Function : To Approve Item group will be called by approval package
3785 -- Version : Added for 115.10
3786 --
3787 -- Pre-reqs :
3788 -- Parameters :
3789 -- Standard IN Parameters :
3790 -- p_api_version IN NUMBER Required
3791 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3792 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3793 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3794 --
3795 -- Standard OUT Parameters :
3796 -- x_return_status OUT VARCHAR2 Required
3797 -- x_msg_count OUT NUMBER Required
3798 -- x_msg_data OUT VARCHAR2 Required
3799
3800 -- P_appr_status Required.
3801 -- Item_Group_id Required.
3802 -- Object_version_number Required.
3803 --
3804 --
3805 -- End of Comments --
3806
3807 PROCEDURE Approve_ItemGroups (
3808 p_api_version IN NUMBER,
3809 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3810 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3811 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3812 P_DEFAULT IN VARCHAR2 := FND_API.G_FALSE,
3813 P_MODULE_TYPE IN VARCHAR2,
3814 x_return_status OUT NOCOPY VARCHAR2,
3815 x_msg_count OUT NOCOPY NUMBER,
3816 x_msg_data OUT NOCOPY VARCHAR2,
3817 p_appr_status IN VARCHAR2,
3818 p_ItemGroups_id IN NUMBER,
3819 p_object_version_number IN NUMBER)
3820
3821 AS
3822
3823 cursor get_itemgroup_det(c_itemgroup_id in Number)
3824 Is Select
3825 item_group_id,
3826 Name,
3827 Status_Code,
3828 Type_Code,
3829 Source_Item_group_id,
3830 object_version_number,
3831 Description,
3832 attribute_category ,
3833 attribute1 ,
3834 attribute2 ,
3835 attribute3 ,
3836 attribute4 ,
3837 attribute5 ,
3838 attribute6 ,
3839 attribute7 ,
3840 attribute8 ,
3841 attribute9 ,
3842 attribute10 ,
3843 attribute11 ,
3844 attribute12 ,
3845 attribute13 ,
3846 attribute14 ,
3847 attribute15
3848 from ahl_item_groups_vl
3849 Where Item_Group_id = c_itemgroup_id;
3850
3851 l_itemgroup_det get_itemgroup_det%rowType;
3852
3853
3854 cursor get_itemgroup_assos_det(c_itemgroup_id in Number)
3855 Is
3856 Select
3857 item_association_id ,
3858 source_item_association_id ,
3859 object_version_number ,
3860 item_group_id ,
3861 inventory_item_id ,
3862 inventory_org_id ,
3863 priority ,
3864 uom_code ,
3865 quantity ,
3866 revision ,
3867 interchange_type_code ,
3868 interchange_reason ,
3869 source_item_association_id,
3870 attribute_category ,
3871 attribute1 ,
3872 attribute2 ,
3873 attribute3 ,
3874 attribute4 ,
3875 attribute5 ,
3876 attribute6 ,
3877 attribute7 ,
3878 attribute8 ,
3879 attribute9 ,
3880 attribute10 ,
3881 attribute11 ,
3882 attribute12 ,
3883 attribute13 ,
3884 attribute14 ,
3885 attribute15
3886 from ahl_item_associations_vl
3887 where item_group_id = c_itemgroup_id;
3888
3889
3890 l_status VARCHAR2(30);
3891 l_msg_count Number;
3892 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
3893 l_rowid VARCHAR2(30);
3894 l_action varchar2(2);
3895
3896 -- SATHAPLI::Bug# 4328454 fix
3897 -- The declared variables are not being used now as the call to
3898 -- AHL_UTIL_UC_PKG.Invalidate_Instance will not be made in this procedure.
3899 -- The validation of Item group updates for active UCs is now being done
3900 -- in procedure Modify_Item_group. Refer to old version of the package
3901 -- for details.
3902
3903 l_fork_or_merge NUMBER;
3904
3905 BEGIN
3906
3907 SAVEPOINT Approve_ItemGroups;
3908
3909
3910 -- Standard call to check for call compatibility.
3911
3912 IF FND_API.to_boolean(l_init_msg_list) THEN
3913 FND_MSG_PUB.initialize;
3914 END IF;
3915
3916 -- Initialize API return status to success
3917
3918 x_return_status :=FND_API.G_RET_STS_SUCCESS;
3919
3920 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3921 THEN
3922 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3923 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Begin of Approve_ItemGroups');
3924 END IF;
3925
3926
3927
3928 OPEN get_itemgroup_det(p_ItemGroups_id);
3929 FETCH get_itemgroup_det INTO l_itemgroup_det;
3930 IF get_itemgroup_det%NOTFOUND
3931 THEN
3932 FND_MESSAGE.SET_NAME('AHL','AHL_MC_OBJECT_ID_NULL');
3933 FND_MSG_PUB.ADD;
3934 END IF;
3935 CLOSE get_itemgroup_det;
3936
3937 IF l_itemgroup_det.object_version_number <> p_object_version_number
3938 THEN
3939 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD');
3940 FND_MSG_PUB.ADD;
3941 END IF;
3942
3943
3944 l_msg_count := FND_MSG_PUB.count_msg;
3945
3946 IF l_msg_count > 0
3947 THEN
3948 X_msg_count := l_msg_count;
3949 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3950 RAISE FND_API.G_EXC_ERROR;
3951 END IF;
3952
3953
3954 IF p_appr_status='APPROVED'
3955 THEN
3956 l_status:='COMPLETE';
3957 ELSE
3958 l_status:='APPROVAL_REJECTED';
3959 END IF;
3960
3961
3962 IF l_status = 'COMPLETE'
3963 THEN
3964 -- Insert record into histroy table.
3965
3966
3967 IF l_itemgroup_det.Source_Item_group_id IS NULL THEN
3968
3969 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3970 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3971 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Source Item group id is null');
3972 END IF;
3973
3974 update ahl_item_groups_b
3975 set status_code=l_status,
3976 object_version_number = object_version_number+1,
3977 last_update_date = sysdate,
3978 last_updated_by = to_number(fnd_global.user_id),
3979 last_update_login = to_number(fnd_global.login_id)
3980 where item_group_id=l_itemgroup_det.item_group_id
3981 and object_version_number = l_itemgroup_det.object_version_number;
3982
3983 l_action :='C';
3984
3985 update_histroy (
3986 p_ItemGroups_id => l_itemgroup_det.item_group_id,
3987 p_action => l_action
3988 );
3989
3990 ELSE
3991
3992
3993 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3994 THEN
3995 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3996 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Source Item Group id is not null');
3997 END IF;
3998
3999 l_fork_or_merge := Fork_Or_Merge(l_itemgroup_det.item_group_id);
4000 IF (l_fork_or_merge = 0)
4001 THEN
4002
4003 -- SATHAPLI::Bug# 4328454 fix
4004 -- Call to AHL_UTIL_UC_PKG.Invalidate_Instance will not be made in this
4005 -- procedure.The validation of Item group updates for active UCs is now
4006 -- being done in procedure Modify_Item_group. Refer to old version of
4007 -- the package for details.
4008
4009 -- Fork the IG to a new one, maintain the earlier complete version
4010
4011 -- SATHAPLI::Service Bulletin Effectivity, 23-Mar-2011, update affected SB rules, if any.
4012 -- NOTE::No need to check for error stack, as it is checked where Approve_ItemGroups is called.
4013 AHL_SB_RULES_PVT.Update_Rules_For_IG(
4014 p_frk_or_mrg_flg => 'F',
4015 p_item_group_id => l_itemgroup_det.item_group_id,
4016 p_src_item_group_id => l_itemgroup_det.source_item_group_id
4017 );
4018
4019 -- Update the IG with status as complete
4020 update ahl_item_groups_b
4021 set status_code = 'COMPLETE',
4022 object_version_number = object_version_number + 1,
4023 source_item_group_id = NULL,
4024 last_update_date = sysdate,
4025 last_updated_by = to_number(fnd_global.user_id),
4026 last_update_login = to_number(fnd_global.login_id)
4027 where item_group_id = l_itemgroup_det.item_group_id and
4028 object_version_number = l_itemgroup_det.object_version_number;
4029
4030 -- For the positions with temp_item_group_id = null, such positions are not associated to the forked
4031 -- copy of the IG, hence no change to the positions
4032
4033 -- For the positions with temp_item_group_id <> null, such positions are associated to the forked
4034 -- copy of the IG, hence update the item_group_id = temp_item_group_id's value and set latter = null
4035 update ahl_mc_relationships
4036 set item_group_id = l_itemgroup_det.item_group_id,
4037 temp_item_group_id = null
4038 where item_group_id = l_itemgroup_det.source_item_group_id and
4039 temp_item_group_id is not null and
4040 trunc(nvl(active_end_date, sysdate+1)) > trunc(sysdate);
4041
4042 -- Update history table
4043 update_histroy
4044 (
4045 p_itemgroups_id => l_itemgroup_det.item_group_id,
4046 p_action => 'U'
4047 );
4048
4049 -- SATHAPLI::Bug# 4328454 fix
4050 -- Code pertaining to call to AHL_UTIL_UC_PKG.Invalidate_Instance removed.
4051 -- Refer to old version of the package for details.
4052
4053 ELSE
4054 -- SATHAPLI::Service Bulletin Effectivity, 23-Mar-2011, update affected SB rules, if any.
4055 -- NOTE::No need to check for error stack, as it is checked where Approve_ItemGroups is called.
4056 AHL_SB_RULES_PVT.Update_Rules_For_IG(
4057 p_frk_or_mrg_flg => 'M',
4058 p_item_group_id => l_itemgroup_det.item_group_id,
4059 p_src_item_group_id => l_itemgroup_det.source_item_group_id
4060 );
4061
4062 -- Merge the IG with the previous complete version
4063 ahl_item_groups_pkg.update_row(
4064 x_item_group_id => l_itemgroup_det.Source_Item_group_id,
4065 x_type_code => l_itemgroup_det.type_code,
4066 x_status_code => 'COMPLETE',
4067 x_source_item_group_id => null,
4068 x_object_version_number => l_itemgroup_det.object_version_number + 1,
4069 x_name => l_itemgroup_det.name,
4070 x_attribute_category => l_itemgroup_det.attribute_category,
4071 x_attribute1 => l_itemgroup_det.attribute1,
4072 x_attribute2 => l_itemgroup_det.attribute2,
4073 x_attribute3 => l_itemgroup_det.attribute3,
4074 x_attribute4 => l_itemgroup_det.attribute4,
4075 x_attribute5 => l_itemgroup_det.attribute5,
4076 x_attribute6 => l_itemgroup_det.attribute6,
4077 x_attribute7 => l_itemgroup_det.attribute7,
4078 x_attribute8 => l_itemgroup_det.attribute8,
4079 x_attribute9 => l_itemgroup_det.attribute9,
4080 x_attribute10 => l_itemgroup_det.attribute10,
4081 x_attribute11 => l_itemgroup_det.attribute11,
4082 x_attribute12 => l_itemgroup_det.attribute12,
4083 x_attribute13 => l_itemgroup_det.attribute13,
4084 x_attribute14 => l_itemgroup_det.attribute14,
4085 x_attribute15 => l_itemgroup_det.attribute15,
4086 x_description => l_itemgroup_det.description,
4087 x_last_update_date => sysdate,
4088 x_last_updated_by => fnd_global.user_id,
4089 x_last_update_login => fnd_global.login_id);
4090
4091 -- SATHAPLI::Bug# 4328454 fix
4092 -- Call to AHL_UTIL_UC_PKG.Invalidate_Instance will not be made in this
4093 -- procedure.The validation of Item group updates for active UCs is now
4094 -- being done in procedure Modify_Item_group. Refer to old version of
4095 -- the package for details.
4096
4097 Delete from ahl_item_associations_tl
4098 where item_association_id in
4099 ( Select item_association_id
4100 from ahl_item_associations_b
4101 where item_group_id = l_itemgroup_det.Source_Item_group_id);
4102
4103 Delete from ahl_item_associations_b
4104 where item_group_id = l_itemgroup_det.Source_Item_group_id;
4105
4106 -- The following is to associate the Temporary Item Group Part Numbers
4107 -- to the Permant(Complete) Item Group.
4108
4109 update ahl_item_associations_b
4110 set item_group_id = l_itemgroup_det.Source_Item_group_id,
4111 object_version_number = object_version_number+1
4112 Where item_group_id = l_itemgroup_det.item_group_id;
4113
4114 -- This is to update the Master Configuration Node which are associated
4115 -- with 'Draft' Item Group.
4116
4117
4118 update ahl_mc_relationships
4119 set ITEM_GROUP_ID = l_itemgroup_det.Source_Item_group_id,
4120 object_version_number = object_version_number+1
4121 Where item_group_id = l_itemgroup_det.item_group_id;
4122
4123 l_action :='U';
4124
4125
4126 -- Updating the history tables.
4127
4128 update_histroy (
4129 p_ItemGroups_id => l_itemgroup_det.item_group_id,
4130 p_action => l_action
4131 );
4132
4133 -- This is to delete the temporary version of Item group.
4134
4135
4136 Delete from ahl_item_groups_tl
4137 where item_group_id = l_itemgroup_det.item_group_id;
4138
4139 Delete from ahl_item_groups_b
4140 where item_group_id = l_itemgroup_det.item_group_id;
4141
4142 -- SATHAPLI::Bug# 4328454 fix
4143 -- Code pertaining to call to AHL_UTIL_UC_PKG.Invalidate_Instance removed.
4144 -- Refer to old version of the package for details.
4145
4146 END IF; -- Fork_Or_Merge
4147 End if;
4148
4149
4150
4151
4152
4153 ELSIF l_status = 'APPROVAL_REJECTED' THEN
4154
4155 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4156 THEN
4157 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4158 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Approval Rejected');
4159 END IF;
4160
4161 update ahl_item_groups_b
4162 set status_code=l_status,
4163 object_version_number = object_version_number+1
4164 where item_group_id=l_itemgroup_det.item_group_id
4165 and object_version_number = l_itemgroup_det.object_version_number;
4166
4167
4168
4169
4170 End if;
4171
4172 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4173 THEN
4174 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4175 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'End of Approve_ItemGroups');
4176 END IF;
4177
4178
4179
4180 EXCEPTION
4181 WHEN FND_API.G_EXC_ERROR THEN
4182 x_return_status := FND_API.G_RET_STS_ERROR;
4183 Rollback to Approve_ItemGroups;
4184 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4185 p_data => x_msg_data,
4186 p_encoded => fnd_api.g_false);
4187
4188 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4189 THEN
4190 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4191 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Error in Approve_ItemGroups');
4192 END IF;
4193
4194
4195 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4197 Rollback to Approve_ItemGroups;
4198 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4199 p_data => x_msg_data,
4200 p_encoded => fnd_api.g_false);
4201
4202 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4203 THEN
4204 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4205 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Unexpected Error in Approve_ItemGroups');
4206 END IF;
4207
4208 WHEN OTHERS THEN
4209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4210 Rollback to Approve_ItemGroups;
4211 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4212 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
4213 p_procedure_name => 'Approve_ItemGroups',
4214 p_error_text => SUBSTR(SQLERRM,1,240));
4215 END IF;
4216 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4217 p_data => x_msg_data,
4218 p_encoded => fnd_api.g_false);
4219
4220 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
4221 THEN
4222 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4223 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', 'Unknown Error in Approve_ItemGroups');
4224 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4225 'ahl.plsql.ahl_mc_itemgroup_pvt.Approve_ItemGroups', SQLERRM);
4226
4227 END IF;
4228
4229
4230 END Approve_ItemGroups;
4231
4232 PROCEDURE Modify_Position_Assos
4233 (
4234 p_api_version IN NUMBER,
4235 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4236 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4237 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4238 p_module_type IN VARCHAR2,
4239 x_return_status OUT NOCOPY VARCHAR2,
4240 x_msg_count OUT NOCOPY NUMBER,
4241 x_msg_data OUT NOCOPY VARCHAR2,
4242 p_item_group_id IN NUMBER,
4243 p_object_version_number IN NUMBER,
4244 p_nodes_tbl IN AHL_MC_Node_PVT.Node_Tbl_Type
4245 )
4246 IS
4247 CURSOR get_itemgroup_details
4248 (
4249 p_item_group_id in number
4250 )
4251 IS
4252 SELECT object_version_number, source_item_group_id, status_code
4253 FROM ahl_item_groups_b
4254 WHERE item_group_id = p_item_group_id;
4255
4256 CURSOR check_position_exists
4257 (
4258 p_relationship_id in number
4259 )
4260 IS
4261 SELECT 'x'
4262 FROM ahl_mc_relationships
4263 WHERE relationship_id = p_relationship_id;
4264
4265 -- Define local variables
4266 l_api_name CONSTANT VARCHAR2(30) := 'Create_Node';
4267 l_api_version CONSTANT NUMBER := 1.0;
4268 l_return_status VARCHAR2(1);
4269 l_msg_count NUMBER;
4270 l_msg_data VARCHAR2(2000);
4271
4272 l_obj_ver_num NUMBER;
4273 l_source_id NUMBER;
4274 l_status VARCHAR2(30);
4275 l_junk VARCHAR2(1);
4276
4277 BEGIN
4278
4279 -- Standard start of API savepoint
4280 SAVEPOINT Modify_Position_Assos_SP;
4281
4282 -- Standard call to check for call compatibility
4283 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
4284 THEN
4285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4286 END IF;
4287
4288 -- Initialize message list if p_init_msg_list is set to TRUE
4289 IF FND_API.TO_BOOLEAN(p_init_msg_list)
4290 THEN
4291 FND_MSG_PUB.Initialize;
4292 END IF;
4293
4294 -- Initialize API return status to success
4295 x_return_status := FND_API.G_RET_STS_SUCCESS;
4296
4297 -- API body starts here
4298 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4299 THEN
4300 fnd_log.string
4301 (
4302 fnd_log.level_procedure,
4303 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
4304 'At the start of PLSQL procedure'
4305 );
4306 END IF;
4307
4308 -- Validate whether the IG exists, if yes, then validate that object_version_number has not been
4309 -- already bounced and status = 'DRAFT' and the IG is a draft copy
4310 OPEN get_itemgroup_details(p_item_group_id);
4311 FETCH get_itemgroup_details INTO l_obj_ver_num, l_source_id, l_status;
4312 IF (get_itemgroup_details%NOTFOUND)
4313 THEN
4314 CLOSE get_itemgroup_details;
4315 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_DELETED');
4316 FND_MSG_PUB.ADD;
4317 RAISE FND_API.G_EXC_ERROR;
4318 ELSIF (l_obj_ver_num <> p_object_version_number)
4319 THEN
4320 CLOSE get_itemgroup_details;
4321 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
4322 FND_MSG_PUB.ADD;
4323 RAISE FND_API.G_EXC_ERROR;
4324 ELSIF (l_status <> 'DRAFT' or nvl(l_source_id, 0) <= 0)
4325 THEN
4326 CLOSE get_itemgroup_details;
4327 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_NOUPDATE');
4328 FND_MSG_PUB.ADD;
4329 RAISE FND_API.G_EXC_ERROR;
4330 END IF;
4331 CLOSE get_itemgroup_details;
4332
4333 -- All above validations have passed
4334 IF (p_nodes_tbl.COUNT > 0)
4335 THEN
4336 -- For each node in the p_nodes_tbl, unassociate the itemgroup, assumption is that only the
4337 -- to-be-unassociated records are passed from the frontend
4338 FOR i IN p_nodes_tbl.FIRST..p_nodes_tbl.LAST
4339 LOOP
4340 OPEN check_position_exists(p_nodes_tbl(i).relationship_id);
4341 FETCH check_position_exists INTO l_junk;
4342 -- Validate node exists, if yes, go ahead and unassociate the item group
4343 IF (check_position_exists%NOTFOUND)
4344 THEN
4345 CLOSE check_position_exists;
4346 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_NOT_FOUND');
4347 FND_MSG_PUB.ADD;
4348 ELSIF (p_nodes_tbl(i).operation_flag = 'C')
4349 THEN
4350 UPDATE ahl_mc_relationships
4351 SET temp_item_group_id = p_item_group_id
4352 WHERE relationship_id = p_nodes_tbl(i).relationship_id;
4353 ELSIF (p_nodes_tbl(i).operation_flag = 'D')
4354 THEN
4355 UPDATE ahl_mc_relationships
4356 SET temp_item_group_id = null
4357 WHERE relationship_id = p_nodes_tbl(i).relationship_id;
4358 END IF;
4359 CLOSE check_position_exists;
4360 END LOOP;
4361
4362 -- Check Error Message stack.
4363 x_msg_count := FND_MSG_PUB.count_msg;
4364 IF x_msg_count > 0 THEN
4365 RAISE FND_API.G_EXC_ERROR;
4366 END IF;
4367 END IF;
4368
4369 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4370 THEN
4371 fnd_log.string
4372 (
4373 fnd_log.level_procedure,
4374 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
4375 'At the end of PLSQL procedure'
4376 );
4377 END IF;
4378 -- API body ends here
4379
4380 -- Check Error Message stack.
4381 x_msg_count := FND_MSG_PUB.count_msg;
4382 IF x_msg_count > 0 THEN
4383 RAISE FND_API.G_EXC_ERROR;
4384 END IF;
4385
4386 -- Standard check for p_commit
4387 IF FND_API.TO_BOOLEAN (p_commit)
4388 THEN
4389 COMMIT WORK;
4390 END IF;
4391
4392 -- Standard call to get message count and if count is 1, get message info
4393 FND_MSG_PUB.count_and_get
4394 (
4395 p_count => x_msg_count,
4396 p_data => x_msg_data,
4397 p_encoded => FND_API.G_FALSE
4398 );
4399
4400 EXCEPTION
4401 WHEN FND_API.G_EXC_ERROR THEN
4402 x_return_status := FND_API.G_RET_STS_ERROR;
4403 Rollback to Modify_Position_Assos_SP;
4404 FND_MSG_PUB.count_and_get
4405 (
4406 p_count => x_msg_count,
4407 p_data => x_msg_data,
4408 p_encoded => FND_API.G_FALSE
4409 );
4410
4411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4413 Rollback to Modify_Position_Assos_SP;
4414 FND_MSG_PUB.count_and_get
4415 (
4416 p_count => x_msg_count,
4417 p_data => x_msg_data,
4418 p_encoded => FND_API.G_FALSE
4419 );
4420
4421 WHEN OTHERS THEN
4422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4423 Rollback to Modify_Position_Assos_SP;
4424 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4425 THEN
4426 FND_MSG_PUB.add_exc_msg
4427 (
4428 p_pkg_name => G_PKG_NAME,
4429 p_procedure_name => 'Modify_Position_Assos',
4430 p_error_text => SUBSTR(SQLERRM,1,240)
4431 );
4432 END IF;
4433 FND_MSG_PUB.count_and_get
4434 (
4435 p_count => x_msg_count,
4436 p_data => x_msg_data,
4437 p_encoded => FND_API.G_FALSE
4438 );
4439
4440 END Modify_Position_Assos;
4441
4442 FUNCTION Fork_Or_Merge
4443 (
4444 p_item_group_id in number
4445 )
4446 RETURN NUMBER
4447 -- Return values:
4448 -- -1 : Neither fork nor merge, for the case of a non-draft copy of the IG
4449 -- 0 : Fork the draft copy of the IG
4450 -- 1 : Merge the draft copy of the IG
4451 IS
4452
4453 CURSOR get_itemgroup_details
4454 (
4455 p_item_group_id in number
4456 )
4457 IS
4458 SELECT source_item_group_id, status_code
4459 FROM ahl_item_groups_b
4460 WHERE item_group_id = p_item_group_id;
4461
4462 CURSOR check_fork
4463 (
4464 p_parent_ig_id in number
4465 )
4466 IS
4467 SELECT 'x'
4468 FROM ahl_mc_relationships
4469 WHERE temp_item_group_id is null and
4470 item_group_id = p_parent_ig_id and
4471 trunc(nvl(active_end_date, sysdate + 1)) > trunc(sysdate);
4472
4473 l_source_id NUMBER;
4474 l_status VARCHAR2(30);
4475 l_junk VARCHAR2(1);
4476
4477 BEGIN
4478 OPEN get_itemgroup_details(p_item_group_id);
4479 FETCH get_itemgroup_details INTO l_source_id, l_status;
4480 CLOSE get_itemgroup_details;
4481
4482 -- For checking fork/merge, the IG should be a draft copy
4483 IF (nvl(l_source_id, 0) > 0 and (l_status = 'DRAFT' or l_status = 'APPROVAL_PENDING'))
4484 THEN
4485 OPEN check_fork(l_source_id);
4486 FETCH check_fork INTO l_junk;
4487 IF (check_fork%FOUND)
4488 THEN
4489 -- IG is to be forked
4490 CLOSE check_fork;
4491 RETURN 0;
4492 ELSE
4493 -- IG is to be merged
4494 CLOSE check_fork;
4495 RETURN 1;
4496 END IF;
4497 ELSE
4498 -- IG is not a draft copy
4499 RETURN -1;
4500 END IF;
4501
4502 END Fork_Or_Merge;
4503
4504 End AHL_MC_ITEMGROUP_PVT;