1 PACKAGE BODY EGO_BROWSE_PVT AS
2 /* $Header: EGOVBRWB.pls 120.2.12010000.2 2009/07/13 14:42:44 snandana ship $ */
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 /* Bug 7042156. Collect statistics only if the no.of records is bigger than the profile
47 option threshold */
48 IF (l_num_rows_inserted > nvl(fnd_profile.value('EGO_GATHER_STATS'),100)) THEN --{
49 FND_STATS.GATHER_TABLE_STATS(l_schema, 'EGO_ITEM_CAT_DENORM_HIER');
50 FND_STATS.GATHER_INDEX_STATS(l_schema, 'EGO_ITEM_CAT_DENORM_HIER_U1');
51 END IF; --}
52
53 x_return_status := 'S';
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 x_return_status := 'U';
58
59 END Reload_ICG_Denorm_Hier_Table; --}
60
61 PROCEDURE Sync_ICG_Denorm_Hier_Table (
62 p_catalog_group_id IN NUMBER,
63 p_old_parent_id IN NUMBER DEFAULT NULL,
64 x_return_status OUT NOCOPY VARCHAR2
65 ) IS
66 --{
67 CURSOR old_parent_hierarchy
68 IS
69 SELECT PARENT_CATALOG_GROUP_ID
70 FROM MTL_ITEM_CATALOG_GROUPS_B IC
71 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
72 START WITH ITEM_CATALOG_GROUP_ID = p_old_parent_id;
73
74 CURSOR new_parent_hierarchy
75 IS
76 SELECT PARENT_CATALOG_GROUP_ID
77 FROM MTL_ITEM_CATALOG_GROUPS_B IC
78 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
79 START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id;
80
81 l_num_rows_updated NUMBER := 0;
82 l_status VARCHAR2(1);
83 l_industry VARCHAR2(1);
84 l_schema VARCHAR2(30);
85 --}
86 BEGIN
87 --{
88
89 IF FND_INSTALLATION.GET_APP_INFO('EGO', l_status, l_industry, l_schema) THEN --{
90 IF l_schema IS NULL THEN
91 Raise_Application_Error (-20001, 'EGO Schema could not be located.');
92 END IF; --}
93 ELSE --{
94 Raise_Application_Error (-20001, 'EGO Schema could not be located.');
95 END IF; --}
96
97 --First delete the child hierarchy entries for the item catalog being create/updated
98 DELETE FROM EGO_ITEM_CAT_DENORM_HIER
99 WHERE PARENT_CATALOG_GROUP_ID = p_catalog_group_id;
100 l_num_rows_updated := l_num_rows_updated + SQL%ROWCOUNT;
101
102 --Now insert the child hierarchy for the item catalog being create/updated
103 INSERT INTO EGO_ITEM_CAT_DENORM_HIER(PARENT_CATALOG_GROUP_ID
104 , CHILD_CATALOG_GROUP_ID)
105 ( SELECT p_catalog_group_id PARENT_CATALOG_GROUP_ID,
106 IC.ITEM_CATALOG_GROUP_ID CHILD_CATALOG_GROUP_ID
107 FROM MTL_ITEM_CATALOG_GROUPS_B IC
108 CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
109 START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id );
110 l_num_rows_updated := l_num_rows_updated + SQL%ROWCOUNT;
111
112 IF(p_old_parent_id IS NOT NULL AND p_old_parent_id <> -1) THEN
113 --{
114
115 --Delete the records for the current child hierarchy from the old parent hierarchy
116 DELETE FROM EGO_ITEM_CAT_DENORM_HIER
117 WHERE PARENT_CATALOG_GROUP_ID = p_old_parent_id
118 AND CHILD_CATALOG_GROUP_ID IN
119 ( SELECT IC.ITEM_CATALOG_GROUP_ID
120 FROM MTL_ITEM_CATALOG_GROUPS_B IC
121 CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
122 START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id );
123 l_num_rows_updated := l_num_rows_updated + SQL%ROWCOUNT;
124
125 --Now delete the records for the current child hierarchy for each parent in the old parent hierarchy,
126 FOR old_parent_rec IN old_parent_hierarchy
127 LOOP
128 --{
129 IF (old_parent_rec.PARENT_CATALOG_GROUP_ID IS NOT NULL) THEN
130 --{
131 DELETE FROM EGO_ITEM_CAT_DENORM_HIER
132 WHERE PARENT_CATALOG_GROUP_ID = old_parent_rec.PARENT_CATALOG_GROUP_ID
133 AND CHILD_CATALOG_GROUP_ID IN
134 ( SELECT IC.ITEM_CATALOG_GROUP_ID
135 FROM MTL_ITEM_CATALOG_GROUPS_B IC
136 CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
137 START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id );
138 l_num_rows_updated := l_num_rows_updated + SQL%ROWCOUNT;
139 --}
140 END IF;
141 --}
142 END LOOP;
143
144 --}
145 END IF;
146
147 --Now insert the records for the current child hierarchy for each parent in the new parent hierarchy,
148 FOR new_parent_rec IN new_parent_hierarchy
149 LOOP
150 --{
151 IF (new_parent_rec.PARENT_CATALOG_GROUP_ID IS NOT NULL) THEN
152 --{
153 INSERT INTO EGO_ITEM_CAT_DENORM_HIER(PARENT_CATALOG_GROUP_ID
154 , CHILD_CATALOG_GROUP_ID)
155 ( SELECT new_parent_rec.PARENT_CATALOG_GROUP_ID PARENT_CATALOG_GROUP_ID,
156 IC.ITEM_CATALOG_GROUP_ID CHILD_CATALOG_GROUP_ID
157 FROM MTL_ITEM_CATALOG_GROUPS_B IC
158 CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
159 START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id );
160 l_num_rows_updated := l_num_rows_updated + SQL%ROWCOUNT;
161 --}
162 END IF;
163 --}
164 END LOOP;
165
166 --Comupte stats on EGO_ITEM_CAT_DENORM_HIER
167 /* Bug 7042156. Collect statistics only if the no.of records is bigger than the profile
168 option threshold */
169 IF (l_num_rows_updated > nvl(fnd_profile.value('EGO_GATHER_STATS'),100)) THEN --{
170 FND_STATS.GATHER_TABLE_STATS(l_schema, 'EGO_ITEM_CAT_DENORM_HIER');
171 FND_STATS.GATHER_INDEX_STATS(l_schema, 'EGO_ITEM_CAT_DENORM_HIER_U1');
172 END IF; --}
173
174 x_return_status := 'S';
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 x_return_status := 'U';
179
180 --}
181 END Sync_ICG_Denorm_Hier_Table;
182
183
184
185
186 END EGO_BROWSE_PVT;
[Home] [Help]