1 PACKAGE BODY INV_ShortCheckExec_PUB AS
2 /* $Header: INVSEPUB.pls 120.1 2005/06/21 05:36:13 appldev ship $*/
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ShortCheckExec_PUB';
4 -- Start OF comments
5 -- API name : ExecCheck
6 -- TYPE : Public
7 -- Pre-reqs : None
8 -- FUNCTION :
9 -- Parameters:
10 -- IN :
11 -- p_api_version IN NUMBER (required)
12 -- API Version of this procedure
13 --
14 -- p_init_msg_list IN VARCHAR2 (optional)
15 -- DEFAULT = FND_API.G_FALSE,
16 --
17 -- p_commit IN VARCHAR2 (optional)
18 -- DEFAULT = FND_API.G_FALSE
19 --
20 --
21 -- OUT :
22 -- x_return_status OUT NUMBER
23 -- Result of all the operations
24 --
25 -- x_msg_count OUT NUMBER,
26 --
27 -- x_msg_data OUT VARCHAR2,
28 --
29 -- x_check_result OUT VARCHAR2
30 --
31 -- Version: Current Version 1.0
32 -- Changed : Nothing
33 -- No Previous Version 0.0
34 -- Initial version 1.0
35 -- Notes :
36 -- END OF comments
37 PROCEDURE ExecCheck (
38 p_api_version IN NUMBER ,
39 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
40 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
41 x_return_status IN OUT NOCOPY VARCHAR2,
42 x_msg_count IN OUT NOCOPY NUMBER,
43 x_msg_data IN OUT NOCOPY VARCHAR2,
44 p_sum_detail_flag IN NUMBER,
45 p_organization_id IN NUMBER,
46 p_inventory_item_id IN NUMBER,
47 p_comp_att_qty_flag IN NUMBER,
48 p_primary_quantity IN NUMBER DEFAULT 0,
49 x_seq_num OUT NOCOPY NUMBER,
50 x_check_result OUT NOCOPY VARCHAR2
51 )
52 IS
53 L_api_version CONSTANT NUMBER := 1.0;
54 L_api_name CONSTANT VARCHAR2(30) := 'ExecCheck';
55 L_Object_Exists VARCHAR2(1);
56 --
57 CURSOR L_Item_crs ( p_organization_id IN NUMBER,
58 p_inventory_item_id IN NUMBER ) IS
59 SELECT 'X'
60 FROM mtl_system_items
61 WHERE inventory_item_id = p_inventory_item_id
62 AND organization_id = p_organization_id;
63 --
64 BEGIN
65 -- Standard Call to check for call compatibility
66 IF NOT FND_API.Compatible_API_Call(l_api_version
67 , p_api_version
68 , l_api_name
69 , G_PKG_NAME) THEN
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
71 END IF;
72 --
73 -- Initialize message list if p_init_msg_list is set to true
74 IF FND_API.to_Boolean(p_init_msg_list) THEN
75 FND_MSG_PUB.initialize;
76 END IF;
77 --
78 -- Initialize API return status to access
79 x_return_status := FND_API.G_RET_STS_SUCCESS;
80 --
81 -- Validate p_inventory_item_id if not null or statement type is summary
82 IF p_inventory_item_id IS NOT NULL OR p_sum_detail_flag = 2 THEN
83 OPEN L_Item_crs ( p_organization_id,
84 p_inventory_item_id );
85 FETCH L_Item_crs INTO L_Object_Exists;
86 IF L_Item_crs%NOTFOUND THEN
87 FND_MESSAGE.SET_NAME('INV','INV_SHORT_ITEM_NOT_FOUND');
88 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',TO_CHAR(p_inventory_item_id));
89 FND_MSG_PUB.Add;
90 RAISE FND_API.G_EXC_ERROR;
91 END IF;
92 CLOSE L_Item_crs;
93 END IF;
94 --
95 INV_ShortCheckExec_PVT.ExecCheck (
96 p_api_version => 1.0,
97 p_init_msg_list => p_init_msg_list,
98 p_commit => p_commit,
99 x_return_status => x_return_status,
100 x_msg_count => x_msg_count,
101 x_msg_data => x_msg_data,
102 p_sum_detail_flag => p_sum_detail_flag,
103 p_organization_id => p_organization_id,
104 p_inventory_item_id => p_inventory_item_id,
105 p_comp_att_qty_flag => p_comp_att_qty_flag,
106 p_primary_quantity => p_primary_quantity,
107 x_seq_num => x_seq_num,
108 x_check_result => x_check_result
109 );
110 --
111 -- Standard check of p_commit
112 IF FND_API.to_Boolean(p_commit) THEN
113 COMMIT;
114 END IF;
115 -- Standard call to get message count and if count is 1, get message info
116 FND_MSG_PUB.Count_And_Get
117 (p_count => x_msg_count
118 , p_data => x_msg_data);
119 EXCEPTION
120 WHEN FND_API.G_EXC_ERROR THEN
121 --
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 --
124 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
125 , p_data => x_msg_data);
126 --
127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
128 --
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130 --
131 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
132 , p_data => x_msg_data);
133 --
134 WHEN OTHERS THEN
135 --
136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
137 --
138 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
139 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
140 END IF;
141 --
142 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
143 , p_data => x_msg_data);
144 END;
145 -- Start OF comments
146 -- API name : CheckPrerequisites
147 -- TYPE : Public
148 -- Pre-reqs : None
149 -- FUNCTION :
150 -- Parameters:
151 -- IN :
152 -- p_api_version IN NUMBER (required)
153 -- API Version of this procedure
154 --
155 -- p_init_msg_list IN VARCHAR2 (optional)
156 -- DEFAULT = FND_API.G_FALSE,
157 --
158 -- p_commit IN VARCHAR2 (optional)
159 -- DEFAULT = FND_API.G_FALSE
160 --
161 --
162 -- OUT :
163 -- x_return_status OUT NUMBER
164 -- Result of all the operations
165 --
166 -- x_msg_count OUT NUMBER,
167 --
168 -- x_msg_data OUT VARCHAR2,
169 --
170 -- Version: Current Version 1.0
171 -- Changed : Nothing
172 -- No Previous Version 0.0
173 -- Initial version 1.0
174 -- Notes :
175 -- END OF comments
176 PROCEDURE CheckPrerequisites (
177 p_api_version IN NUMBER ,
178 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
179 x_return_status IN OUT NOCOPY VARCHAR2,
180 x_msg_count IN OUT NOCOPY NUMBER,
181 x_msg_data IN OUT NOCOPY VARCHAR2,
182 p_sum_detail_flag IN NUMBER,
183 p_organization_id IN NUMBER,
184 p_inventory_item_id IN NUMBER,
185 p_transaction_type_id IN NUMBER,
186 x_check_result OUT NOCOPY VARCHAR2
187 )
188 IS
189 L_api_version CONSTANT NUMBER := 1.0;
190 L_api_name CONSTANT VARCHAR2(30) := 'CheckPrerequisites';
191 L_Object_Exists VARCHAR2(1);
192 --
193 CURSOR L_Item_crs ( p_organization_id IN NUMBER,
194 p_inventory_item_id IN NUMBER ) IS
195 SELECT 'X'
196 FROM mtl_system_items
197 WHERE inventory_item_id = p_inventory_item_id
198 AND organization_id = p_organization_id;
199 --
200 CURSOR L_TransType_crs ( p_transaction_type_id IN NUMBER ) IS
201 SELECT 'X'
202 FROM mtl_transaction_types
203 WHERE transaction_type_id = p_transaction_type_id;
204 --
205 BEGIN
206 -- Standard Call to check for call compatibility
207 IF NOT FND_API.Compatible_API_Call(l_api_version
208 , p_api_version
209 , l_api_name
210 , G_PKG_NAME) THEN
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212 END IF;
213 --
214 -- Initialize message list if p_init_msg_list is set to true
215 IF FND_API.to_Boolean(p_init_msg_list) THEN
216 FND_MSG_PUB.initialize;
217 END IF;
218 --
219 -- Initialize API return status to access
220 x_return_status := FND_API.G_RET_STS_SUCCESS;
221 --
222 -- Validate p_inventory_item_id
223 OPEN L_Item_crs ( p_organization_id,
224 p_inventory_item_id );
225 FETCH L_Item_crs INTO L_Object_Exists;
226 IF L_Item_crs%NOTFOUND THEN
227 FND_MESSAGE.SET_NAME('INV','INV_ITEM_NOT_FOUND');
228 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',TO_CHAR(p_inventory_item_id));
229 FND_MSG_PUB.Add;
230 RAISE FND_API.G_EXC_ERROR;
231 END IF;
232 CLOSE L_Item_crs;
233 --
234 -- Validate p_transaction_type_id
235 IF p_transaction_type_id IS NOT NULL THEN
236 OPEN L_TransType_crs ( p_transaction_type_id );
237 FETCH L_TransType_crs INTO L_Object_Exists;
238 IF L_TransType_crs%NOTFOUND THEN
239 FND_MESSAGE.SET_NAME('INV','INV_TRANSACTION_TYPE_NOT_FOUND');
240 FND_MESSAGE.SET_TOKEN('TRANSACTION_TYPE_ID',
241 TO_CHAR(p_transaction_type_id));
242 FND_MSG_PUB.Add;
243 RAISE FND_API.G_EXC_ERROR;
244 END IF;
245 CLOSE L_TransType_crs;
246 END IF;
247 --
248 INV_ShortCheckExec_PVT.CheckPrerequisites (
249 p_api_version => 1.0,
250 p_init_msg_list => p_init_msg_list,
251 x_return_status => x_return_status,
252 x_msg_count => x_msg_count,
253 x_msg_data => x_msg_data,
254 p_sum_detail_flag => p_sum_detail_flag,
255 p_organization_id => p_organization_id,
256 p_inventory_item_id => p_inventory_item_id,
257 p_transaction_type_id => p_transaction_type_id,
258 x_check_result => x_check_result
259 );
260 --
261 -- Standard call to get message count and if count is 1, get message info
262 FND_MSG_PUB.Count_And_Get
263 (p_count => x_msg_count
264 , p_data => x_msg_data);
265 EXCEPTION
266 WHEN FND_API.G_EXC_ERROR THEN
267 --
268 x_return_status := FND_API.G_RET_STS_ERROR;
269 --
270 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
271 , p_data => x_msg_data);
272 --
273 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
274 --
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 --
277 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
278 , p_data => x_msg_data);
279 --
280 WHEN OTHERS THEN
281 --
282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
283 --
284 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
285 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
286 END IF;
287 --
288 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
289 , p_data => x_msg_data);
290 END;
291 -- Start OF comments
292 -- API name : PurgeTempTable
293 -- TYPE : Public
294 -- Pre-reqs : None
295 -- FUNCTION :
296 -- Parameters:
297 -- IN :
298 -- p_api_version IN NUMBER (required)
299 -- API Version of this procedure
300 --
301 -- p_init_msg_list IN VARCHAR2 (optional)
302 -- DEFAULT = FND_API.G_FALSE,
303 --
304 -- p_commit IN VARCHAR2 (optional)
305 -- DEFAULT = FND_API.G_FALSE
306 --
307 -- p_seq_num IN NUMBER
308 -- Sequence number of rows which have to be deleted
309 --
310 --
311 -- OUT :
312 -- x_return_status OUT NUMBER
313 -- Result of all the operations
314 --
315 -- x_msg_count OUT NUMBER,
316 --
317 -- x_msg_data OUT VARCHAR2,
318 --
319 --
320 -- Version: Current Version 1.0
321 -- Changed : Nothing
322 -- No Previous Version 0.0
323 -- Initial version 1.0
324 -- Notes :
325 -- END OF comments
326 PROCEDURE PurgeTempTable (
327 p_api_version IN NUMBER ,
328 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
329 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
330 x_return_status IN OUT NOCOPY VARCHAR2,
331 x_msg_count IN OUT NOCOPY NUMBER,
332 x_msg_data IN OUT NOCOPY VARCHAR2,
333 p_seq_num IN NUMBER
334 )
335 IS
336 L_api_version CONSTANT NUMBER := 1.0;
337 L_api_name CONSTANT VARCHAR2(30) := 'PurgeTempTable';
338 BEGIN
339 -- Standard Call to check for call compatibility
340 IF NOT FND_API.Compatible_API_Call(l_api_version
341 , p_api_version
342 , l_api_name
343 , G_PKG_NAME) THEN
344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
345 END IF;
346 --
347 -- Initialize message list if p_init_msg_list is set to true
348 IF FND_API.to_Boolean(p_init_msg_list) THEN
349 FND_MSG_PUB.initialize;
350 END IF;
351 --
352 -- Initialize API return status to access
353 x_return_status := FND_API.G_RET_STS_SUCCESS;
354 --
355 INV_ShortCheckExec_PVT.PurgeTempTable (
356 p_api_version => 1.0,
357 p_init_msg_list => p_init_msg_list,
358 p_commit => p_commit,
359 x_return_status => x_return_status,
360 x_msg_count => x_msg_count,
361 x_msg_data => x_msg_data,
362 p_seq_num => p_seq_num
363 );
364 --
365 -- Standard check of p_commit
366 IF FND_API.to_Boolean(p_commit) THEN
367 COMMIT;
368 END IF;
369 -- Standard call to get message count and if count is 1, get message info
370 FND_MSG_PUB.Count_And_Get
371 (p_count => x_msg_count
372 , p_data => x_msg_data);
373 EXCEPTION
374 WHEN FND_API.G_EXC_ERROR THEN
375 --
376 x_return_status := FND_API.G_RET_STS_ERROR;
377 --
378 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
379 , p_data => x_msg_data);
380 --
381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
382 --
383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
384 --
385 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
386 , p_data => x_msg_data);
387 --
388 WHEN OTHERS THEN
389 --
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
391 --
392 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
393 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
394 END IF;
395 --
396 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
397 , p_data => x_msg_data);
398 END;
399 END INV_ShortCheckExec_PUB;
[Home] [Help]