1 PACKAGE BODY EGO_BROWSE_PVT AS
2 /* $Header: EGOVBRWB.pls 120.2 2006/03/17 05:33 bparthas noship $ */
3
4 PROCEDURE Reload_ICG_Denorm_Hier_Table
5 (x_return_status OUT NOCOPY VARCHAR2)
6 IS
7 CURSOR item_catalog_group_cursor
8 IS
9 SELECT ITEM_CATALOG_GROUP_ID
10 FROM MTL_ITEM_CATALOG_GROUPS_B;
11
12 l_status VARCHAR2(1);
13 l_industry VARCHAR2(1);
14 l_schema VARCHAR2(30);
15 l_num_rows_inserted NUMBER := 0;
16
17 BEGIN --{
18
19 IF FND_INSTALLATION.GET_APP_INFO('EGO', l_status, l_industry, l_schema) THEN --{
20 IF l_schema IS NULL THEN
21 Raise_Application_Error (-20001, 'EGO Schema could not be located.');
22 END IF; --}
23 ELSE --{
24 Raise_Application_Error (-20001, 'EGO Schema could not be located.');
25 END IF; --}
26
27
28 --First delete all records in the denorm table.
29 DELETE EGO_ITEM_CAT_DENORM_HIER;
30
31 --Now for each record in the MTL_ITEM_CATALOG_GROUPS_B table
32 --insert the complete child heirarchy into the denorm table.
33 FOR cat_rec IN item_catalog_group_cursor
34 LOOP --{
35 INSERT INTO EGO_ITEM_CAT_DENORM_HIER(PARENT_CATALOG_GROUP_ID, CHILD_CATALOG_GROUP_ID)
36 SELECT cat_rec.ITEM_CATALOG_GROUP_ID PARENT_CATALOG_GROUP_ID
37 , IC.ITEM_CATALOG_GROUP_ID CHILD_CATALOG_GROUP_ID
38 FROM MTL_ITEM_CATALOG_GROUPS_B IC
39 CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
40 START WITH ITEM_CATALOG_GROUP_ID = cat_rec.ITEM_CATALOG_GROUP_ID;
41
42 l_num_rows_inserted := l_num_rows_inserted + SQL%ROWCOUNT;
43 END LOOP; --}
44
45 --Comupte stats on EGO_ITEM_CAT_DENORM_HIER
46 IF (l_num_rows_inserted > 50) THEN --{
47 FND_STATS.GATHER_TABLE_STATS(l_schema, 'EGO_ITEM_CAT_DENORM_HIER');
48 FND_STATS.GATHER_INDEX_STATS(l_schema, 'EGO_ITEM_CAT_DENORM_HIER_U1');
49 END IF; --}
50
51 x_return_status := 'S';
52
53 EXCEPTION
54 WHEN OTHERS THEN
55 x_return_status := 'U';
56
57 END Reload_ICG_Denorm_Hier_Table; --}
58
59 PROCEDURE Sync_ICG_Denorm_Hier_Table (
60 p_catalog_group_id IN NUMBER,
61 p_old_parent_id IN NUMBER DEFAULT NULL,
62 x_return_status OUT NOCOPY VARCHAR2
63 ) IS
64 --{
65 CURSOR old_parent_hierarchy
66 IS
67 SELECT PARENT_CATALOG_GROUP_ID
68 FROM MTL_ITEM_CATALOG_GROUPS_B IC
69 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
70 START WITH ITEM_CATALOG_GROUP_ID = p_old_parent_id;
71
72 CURSOR new_parent_hierarchy
73 IS
74 SELECT PARENT_CATALOG_GROUP_ID
75 FROM MTL_ITEM_CATALOG_GROUPS_B IC
76 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
77 START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id;
78
79 l_num_rows_updated NUMBER := 0;
80 l_status VARCHAR2(1);
81 l_industry VARCHAR2(1);
82 l_schema VARCHAR2(30);
83 --}
84 BEGIN
85 --{
86
87 IF FND_INSTALLATION.GET_APP_INFO('EGO', l_status, l_industry, l_schema) THEN --{
88 IF l_schema IS NULL THEN
89 Raise_Application_Error (-20001, 'EGO Schema could not be located.');
90 END IF; --}
91 ELSE --{
92 Raise_Application_Error (-20001, 'EGO Schema could not be located.');
93 END IF; --}
94
95 --First delete the child hierarchy entries for the item catalog being create/updated
96 DELETE FROM EGO_ITEM_CAT_DENORM_HIER
97 WHERE PARENT_CATALOG_GROUP_ID = p_catalog_group_id;
98 l_num_rows_updated := l_num_rows_updated + SQL%ROWCOUNT;
99
100 --Now insert the child hierarchy for the item catalog being create/updated
101 INSERT INTO EGO_ITEM_CAT_DENORM_HIER(PARENT_CATALOG_GROUP_ID
102 , CHILD_CATALOG_GROUP_ID)
103 ( SELECT p_catalog_group_id PARENT_CATALOG_GROUP_ID,
104 IC.ITEM_CATALOG_GROUP_ID CHILD_CATALOG_GROUP_ID
105 FROM MTL_ITEM_CATALOG_GROUPS_B IC
106 CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
107 START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id );
108 l_num_rows_updated := l_num_rows_updated + SQL%ROWCOUNT;
109
110 IF(p_old_parent_id IS NOT NULL AND p_old_parent_id <> -1) THEN
111 --{
112
113 --Delete the records for the current child hierarchy from the old parent hierarchy
114 DELETE FROM EGO_ITEM_CAT_DENORM_HIER
115 WHERE PARENT_CATALOG_GROUP_ID = p_old_parent_id
116 AND CHILD_CATALOG_GROUP_ID IN
117 ( SELECT IC.ITEM_CATALOG_GROUP_ID
118 FROM MTL_ITEM_CATALOG_GROUPS_B IC
119 CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
120 START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id );
121 l_num_rows_updated := l_num_rows_updated + SQL%ROWCOUNT;
122
123 --Now delete the records for the current child hierarchy for each parent in the old parent hierarchy,
124 FOR old_parent_rec IN old_parent_hierarchy
125 LOOP
126 --{
127 IF (old_parent_rec.PARENT_CATALOG_GROUP_ID IS NOT NULL) THEN
128 --{
129 DELETE FROM EGO_ITEM_CAT_DENORM_HIER
130 WHERE PARENT_CATALOG_GROUP_ID = old_parent_rec.PARENT_CATALOG_GROUP_ID
131 AND CHILD_CATALOG_GROUP_ID IN
132 ( SELECT IC.ITEM_CATALOG_GROUP_ID
133 FROM MTL_ITEM_CATALOG_GROUPS_B IC
134 CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
135 START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id );
136 l_num_rows_updated := l_num_rows_updated + SQL%ROWCOUNT;
137 --}
138 END IF;
139 --}
140 END LOOP;
141
142 --}
143 END IF;
144
145 --Now insert the records for the current child hierarchy for each parent in the new parent hierarchy,
146 FOR new_parent_rec IN new_parent_hierarchy
147 LOOP
148 --{
149 IF (new_parent_rec.PARENT_CATALOG_GROUP_ID IS NOT NULL) THEN
150 --{
151 INSERT INTO EGO_ITEM_CAT_DENORM_HIER(PARENT_CATALOG_GROUP_ID
152 , CHILD_CATALOG_GROUP_ID)
153 ( SELECT new_parent_rec.PARENT_CATALOG_GROUP_ID PARENT_CATALOG_GROUP_ID,
154 IC.ITEM_CATALOG_GROUP_ID CHILD_CATALOG_GROUP_ID
155 FROM MTL_ITEM_CATALOG_GROUPS_B IC
156 CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
157 START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id );
158 l_num_rows_updated := l_num_rows_updated + SQL%ROWCOUNT;
159 --}
160 END IF;
161 --}
162 END LOOP;
163
164 --Comupte stats on EGO_ITEM_CAT_DENORM_HIER
165 IF (l_num_rows_updated > 50) THEN --{
166 FND_STATS.GATHER_TABLE_STATS(l_schema, 'EGO_ITEM_CAT_DENORM_HIER');
167 FND_STATS.GATHER_INDEX_STATS(l_schema, 'EGO_ITEM_CAT_DENORM_HIER_U1');
168 END IF; --}
169
170 x_return_status := 'S';
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 x_return_status := 'U';
175
176 --}
177 END Sync_ICG_Denorm_Hier_Table;
178
179
180
181
182 END EGO_BROWSE_PVT;
[Home] [Help]