1 PACKAGE BODY IBE_CATALOG_PRELOAD_PUB AS
2 /* $Header: IBEPCPLB.pls 120.0 2005/05/30 02:54:06 appldev noship $ */
3
4
5 -- Start of comments
6 -- API name : Get_Preload_Section_Ids
7 -- Type : Public.
8 -- Function : Returns ref cursor containing section_ids of sections to be preloaded
9 -- into the cache. Section IDs should come from
10 -- IBE_DSP_SECTIONS_B.SECTION_ID or a table with a foreign key to
11 -- IBE_DSP_SECTIONS_B.SECTION_ID. Current implementation returns
12 -- all section ids.
13 --
14 -- Pre-reqs : None.
15 -- Parameters :
16 --
17 -- IN : p_api_version IN NUMBER Required
18 -- p_init_msg_list IN VARCHAR2 Optional
19 -- Default = FND_API.G_FALSE
20 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
21 -- x_msg_count OUT NOCOPY NUMBER
22 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
23 -- x_section_id_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE
24 -- Record type = IBE_ID_REC
25 --
26 -- Version : Current version 1.0
27 --
28 -- previous version None
29 --
30 -- Initial version 1.0
31 --
32 -- Notes : Note text
33 --
34 -- End of comments
35 procedure Get_Preload_Section_Ids
36 (p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41
42 x_section_id_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE
43 ) IS
44
45 l_api_name CONSTANT VARCHAR2(30) := 'Get_Preload_Section_Ids';
46 l_api_version CONSTANT NUMBER := 1.0;
47
48
49
50 BEGIN
51 --gzhang 08/08/2002, bug#2488246
52 --ibe_util.enable_debug;
53 -- standard call to check for call compatibility
54 IF NOT FND_API.Compatible_API_Call (l_api_version,
55 p_api_version,
56 l_api_name,
57 G_PKG_NAME )
58 THEN
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END IF;
61
62 -- initialize message list if p_init_msg_list is set to TRUE
63 IF FND_API.to_Boolean(p_init_msg_list) THEN
64 FND_MSG_PUB.initialize;
65 END IF;
66
67 -- initialize API return status to success
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
71 IBE_UTIL.debug('IBE_CATALOG_PRELOAD_PUB.Get_Preload_Section_Ids(+)');
72 END IF;
73 -- begin API body
74
75 OPEN x_section_id_csr FOR
76 SELECT s.SECTION_ID
77 FROM IBE_DSP_SECTIONS_B s
78 WHERE sysdate between s.start_date_active and nvl(s.end_date_active,sysdate);
79
80 -- end API body
81 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
82 IBE_UTIL.debug('IBE_CATALOG_PRELOAD_PUB.Get_Preload_Section_Ids(-)');
83 END IF;
84
85 -- standard call to get message count and if count is 1, get message info.
86 FND_MSG_PUB.Count_And_Get
87 ( p_encoded => FND_API.G_FALSE,
88 p_count => x_msg_count,
89 p_data => x_msg_data
90 );
91 --gzhang 08/08/2002, bug#2488246
92 --ibe_util.disable_debug;
93 EXCEPTION
94 WHEN FND_API.G_EXC_ERROR THEN
95 x_return_status := FND_API.G_RET_STS_ERROR;
96 FND_MSG_PUB.Count_And_Get
97 ( p_encoded => FND_API.G_FALSE,
98 p_count => x_msg_count,
99 p_data => x_msg_data
100 );
101 --gzhang 08/08/2002, bug#2488246
102 --ibe_util.disable_debug;
103 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
105 FND_MSG_PUB.Count_And_Get
106 ( p_encoded => FND_API.G_FALSE,
107 p_count => x_msg_count,
108 p_data => x_msg_data
109 );
110 --gzhang 08/08/2002, bug#2488246
111 --ibe_util.disable_debug;
112 WHEN OTHERS THEN
113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
114 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
115 FND_MESSAGE.Set_Token('ROUTINE', l_api_name || '4');
116 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
117 FND_MESSAGE.Set_Token('REASON', SQLERRM);
118 FND_MSG_PUB.Add;
119 IF FND_MSG_PUB.Check_Msg_Level
120 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
121 THEN FND_MSG_PUB.Add_Exc_Msg
122 ( G_PKG_NAME,
123 l_api_name
124 );
125 END IF;
126 FND_MSG_PUB.Count_And_Get
127 ( p_encoded => FND_API.G_FALSE,
128 p_count => x_msg_count,
129 p_data => x_msg_data
130 );
131 --gzhang 08/08/2002, bug#2488246
132 --ibe_util.disable_debug;
133 END Get_Preload_Section_Ids;
134
135
136 -- Start of comments
137 -- API name : Get_Preload_Item_Ids
138 -- Type : Public.
139 -- Function : Returns ref cursor containing inventory_item_ids of items to be
140 -- preloaded into the cache. Item IDs should come from
141 -- MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID or a table with a foreign
142 -- key to MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID (such as
143 -- IBE_DSP_SECTION_ITEMS). Current implementation returns inventory
144 -- item ids of all items in featured sections.
145 --
146 -- Pre-reqs : None.
147 -- Parameters :
148 --
149 -- IN : p_api_version IN NUMBER Required
150 -- p_init_msg_list IN VARCHAR2 Optional
151 -- Default = FND_API.G_FALSE
152 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
153 -- x_msg_count OUT NOCOPY NUMBER
154 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
155 -- x_item_id_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE
156 -- Record type = IBE_ID_REC
157 --
158 -- Version : Current version 1.0
159 --
160 -- previous version None
161 --
162 -- Initial version 1.0
163 --
164 -- Notes : Note text
165 --
166 -- End of comments
167 procedure Get_Preload_Item_Ids
168 (p_api_version IN NUMBER,
169 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
170 x_return_status OUT NOCOPY VARCHAR2,
171 x_msg_count OUT NOCOPY NUMBER,
172 x_msg_data OUT NOCOPY VARCHAR2,
173
174 x_item_id_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE
175 ) IS
176 l_api_name CONSTANT VARCHAR2(30) := 'Get_Preload_Item_Ids';
177 l_api_version CONSTANT NUMBER := 1.0;
178
179
180 BEGIN
181 --gzhang 08/08/2002, bug#2488246
182 --ibe_util.enable_debug;
183 -- standard call to check for call compatibility
184 IF NOT FND_API.Compatible_API_Call (l_api_version,
185 p_api_version,
186 l_api_name,
187 G_PKG_NAME )
188 THEN
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190 END IF;
191
192 -- initialize message list if p_init_msg_list is set to TRUE
193 IF FND_API.to_Boolean(p_init_msg_list) THEN
194 FND_MSG_PUB.initialize;
195 END IF;
196
197 -- initialize API return status to success
198 x_return_status := FND_API.G_RET_STS_SUCCESS;
199
200 IBE_UTIL.debug('IBE_CATALOG_PRELOAD_PUB.Get_Preload_Item_Ids(+)');
201 -- begin API body
202 OPEN x_item_id_csr FOR
203 SELECT DISTINCT si.INVENTORY_ITEM_ID
204 FROM IBE_DSP_SECTIONS_B s, IBE_DSP_SECTION_ITEMS si, MTL_SYSTEM_ITEMS_B i
205 WHERE s.SECTION_TYPE_CODE = 'F' AND s.SECTION_ID = si.SECTION_ID
206 AND si.INVENTORY_ITEM_ID = i.INVENTORY_ITEM_ID AND i.WEB_STATUS = 'PUBLISHED'
207 AND sysdate between s.start_date_active and nvl(s.end_date_active,sysdate)
208 AND sysdate between si.start_date_active and nvl(si.end_date_active,sysdate);
209
210 -- end API body
211 IBE_UTIL.debug('IBE_CATALOG_PRELOAD_PUB.Get_Preload_Item_Ids(-)');
212
213 -- standard call to get message count and if count is 1, get message info.
214 FND_MSG_PUB.Count_And_Get
215 ( p_encoded => FND_API.G_FALSE,
216 p_count => x_msg_count,
217 p_data => x_msg_data
218 );
219 --gzhang 08/08/2002, bug#2488246
220 --ibe_util.disable_debug;
221 EXCEPTION
222 WHEN FND_API.G_EXC_ERROR THEN
223 x_return_status := FND_API.G_RET_STS_ERROR;
224 FND_MSG_PUB.Count_And_Get
225 ( p_encoded => FND_API.G_FALSE,
226 p_count => x_msg_count,
227 p_data => x_msg_data
228 );
229 --gzhang 08/08/2002, bug#2488246
230 --ibe_util.disable_debug;
231 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233 FND_MSG_PUB.Count_And_Get
234 ( p_encoded => FND_API.G_FALSE,
235 p_count => x_msg_count,
236 p_data => x_msg_data
237 );
238 --gzhang 08/08/2002, bug#2488246
239 --ibe_util.disable_debug;
240 WHEN OTHERS THEN
241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
242 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
243 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
244 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
245 FND_MESSAGE.Set_Token('REASON', SQLERRM);
246 FND_MSG_PUB.Add;
247 IF FND_MSG_PUB.Check_Msg_Level
248 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
249 THEN FND_MSG_PUB.Add_Exc_Msg
250 ( G_PKG_NAME,
251 l_api_name
252 );
253 END IF;
254 FND_MSG_PUB.Count_And_Get
255 ( p_encoded => FND_API.G_FALSE,
256 p_count => x_msg_count,
257 p_data => x_msg_data
258 );
259 --gzhang 08/08/2002, bug#2488246
260 --ibe_util.disable_debug;
261 END Get_Preload_Item_Ids;
262
263
264
265 -------
266 -- (code for PROCEDURE Preload_Sections removed on 01/19/2005 by rgupta)
267 -- This procedure is no longer necessary due to a redesign of the iStore
268 -- Section cache.
269 --
270
271
272 end IBE_CATALOG_PRELOAD_PUB;
[Home] [Help]