1 PACKAGE BODY ENI_UPD_ASSGN AS
2 /* $Header: ENIIASGB.pls 120.1 2005/10/18 03:22:49 lparihar noship $ */
3
4 g_catset_id NUMBER := ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID; -- Variable To Hold Product Catalog Category Set
5
6 PROCEDURE UPDATE_ASSGN_FLAG(
7 p_new_category_id IN NUMBER,
8 p_old_category_id IN NUMBER,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_msg_count OUT NOCOPY NUMBER,
11 x_msg_data OUT NOCOPY VARCHAR2) IS
12
13 l_user_id NUMBER := FND_GLOBAL.USER_ID; -- Bug# 3045649, user_id to be updated in last_updated_by column
14 l_count NUMBER;
15 l_rows_updated NUMBER; --Bug 4598106
16 BEGIN
17
18 -- Updating Item Assignment flag to 'Y' for new_category_id
19 -- Bug# 3045649, added WHO columns in update statements
20 UPDATE ENI_DENORM_HIERARCHIES B
21 SET ITEM_ASSGN_FLAG = 'Y',
22 LAST_UPDATE_DATE = SYSDATE,
23 LAST_UPDATED_BY = l_user_id,
24 LAST_UPDATE_LOGIN = l_user_id
25 WHERE OBJECT_TYPE = 'CATEGORY_SET'
26 AND OBJECT_ID = g_catset_id
27 AND ITEM_ASSGN_FLAG = 'N'
28 AND CHILD_ID = NVL(p_new_category_id, -1);
29
30 l_rows_updated := SQL%ROWCOUNT;
31
32 -- Updating Item Assignment to 'N' for old_category_id, if there are no more items assigned to it
33 -- Bug# 3045649, added WHO columns in update statements
34 UPDATE ENI_DENORM_HIERARCHIES B
35 SET ITEM_ASSGN_FLAG = 'N',
36 LAST_UPDATE_DATE = SYSDATE,
37 LAST_UPDATED_BY = l_user_id,
38 LAST_UPDATE_LOGIN = l_user_id
39 WHERE OBJECT_TYPE = 'CATEGORY_SET'
40 AND OBJECT_ID = g_catset_id
41 AND CHILD_ID = NVL(p_old_category_id, -1)
42 AND ITEM_ASSGN_FLAG = 'Y'
43 AND CHILD_ID <> -1
44 AND NOT EXISTS (SELECT NULL
45 FROM MTL_ITEM_CATEGORIES C
46 WHERE C.CATEGORY_SET_ID = g_catset_id
47 AND C.CATEGORY_ID = B.CHILD_ID);
48
49 l_rows_updated := l_rows_updated + SQL%ROWCOUNT;
50
51 /*Bug 4598106
52 If no rows updated then the old_category and new category id are not
53 product categories hence no need to execute further*/
54 IF l_rows_updated = 0 then
55 return;
56 END IF;
57
58
59 -- Checking Item assignment flag for Unassigned category
60 -- if all items are attached to some categories within this category set then
61 -- Item assignment flag for Unassigned node will be 'N'
62 l_count := 0;
63
64 BEGIN
65 /**Bug 4675565 Replaced with the query below
66 SELECT 1 INTO l_count
67 FROM MTL_SYSTEM_ITEMS_B IT
68 WHERE ROWNUM = 1
69 AND NOT EXISTS (SELECT NULL FROM MTL_ITEM_CATEGORIES C
70 WHERE C.CATEGORY_SET_ID = g_catset_id
71 AND C.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID
72 AND C.ORGANIZATION_ID = IT.ORGANIZATION_ID);*/
73
74 SELECT 1 INTO l_count
75 FROM ENI_OLTP_ITEM_STAR star
76 WHERE star.vbh_category_id = -1
77 AND rownum = 1;
78
79 EXCEPTION WHEN NO_DATA_FOUND THEN
80 l_count := 0;
81 END;
82
83 UPDATE ENI_DENORM_HIERARCHIES B
84 SET
85 ITEM_ASSGN_FLAG = DECODE(l_count, 0, 'N', 'Y'),
86 LAST_UPDATE_DATE = SYSDATE,
87 LAST_UPDATED_BY = l_user_id,
88 LAST_UPDATE_LOGIN = l_user_id
89 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
90 AND B.OBJECT_ID = g_catset_id
91 AND B.ITEM_ASSGN_FLAG = DECODE(l_count, 0, 'Y', 'N')
92 AND B.CHILD_ID = -1
93 AND B.PARENT_ID = -1;
94
95 x_return_status := 'S';
96 x_msg_count := 0;
97 x_msg_data := null;
98 EXCEPTION WHEN OTHERS THEN
99 x_return_status := 'U';
100 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
101 FND_MSG_PUB.ADD_EXC_MSG('ENI_UPD_ASSGN', 'UPDATE_ASSGN_FLAG', SQLERRM);
102 END IF;
103 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
104 END UPDATE_ASSGN_FLAG;
105
106 END ENI_UPD_ASSGN;
[Home] [Help]