[Home] [Help]
PACKAGE BODY: APPS.ASO_INSTBASE_INT
Source
1 PACKAGE BODY ASO_instbase_INT as
2 /* $Header: asoicsib.pls 120.2 2006/03/23 18:24:01 skulkarn ship $ */
3 -- Start of Comments
4 -- Package name : ASO_InstBase_INT
5 -- Purpose :
6 -- History :
7 -- 04/07/03 hyang - bug 2860045, performance fix.
8 -- NOTE :
9 -- End of Comments
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_InstBase_INT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoicsib.pls';
13
14 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
15
16
17 -- global variables
18
19
20 --------------------------------------------------------------------------
21
22 -- Start of comments
23 -- API name : Delete_Installation_Details
24 -- Type : Public
25 -- Function : This API is used to delete Installation details records.
26 -- Pre-reqs : None.
27 --
28 -- Standard IN Parameters:
29 -- p_api_version IN NUMBER Required
30 -- p_init_msg_list IN VARCHAR2 Optional
31 -- := FND_API.G_FALSE
32 --
33 -- Standard OUT NOCOPY /* file.sql.39 change */ Parameters:
34 -- x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2(1)
35 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
36 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2(2000)
37 --
38 -- Delete_Installation_Details IN Parameters:
39 -- p_line_inst_dtl_id NUMBER Required
40
41 -- Delete_Installation_Details OUT NOCOPY /* file.sql.39 change */ Parameters:
42 -- None
43 --
44 -- Version : Current version 1.0
45 -- Initial version 1.0
46 --
47 -- End of comments
48 --------------------------------------------------------------------------
49
50 PROCEDURE Delete_Installation_Detail
51 (
52 p_api_version_number IN NUMBER,
53 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
54 p_commit IN VARCHAR2 := FND_API.G_FALSE,
55 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
56 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
57 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
58 p_line_inst_dtl_id IN NUMBER
59 )
60 IS
61
62 l_api_version_number NUMBER := 1.0;
63 l_api_name VARCHAR2(50) := 'Delete_Installation_Detail';
64
65 BEGIN
66
67 -- Standard Start of API savepoint
68 SAVEPOINT Delete_Installation_detail_PUB;
69
70 -- Standard call to check for call compatibility.
71 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
72 p_api_version_number,
73 l_api_name,
74 G_PKG_NAME)
75 THEN
76 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77 END IF;
78
79
80 -- Initialize message list if p_init_msg_list is set to TRUE.
81 IF FND_API.to_Boolean( p_init_msg_list )
82 THEN
83 FND_MSG_PUB.initialize;
84 END IF;
85
86 -- Debug Message
87 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
88
89 -- Initialize API return status to SUCCESS
90 x_return_status := FND_API.G_RET_STS_SUCCESS;
91
92 IF NOT (CSI_utility_grp.ib_active())
93 then
94 -- old ib module.
95 null;
96 ELSE
97 -- new ib module.
98 CSI_T_Txn_Details_GRP.Delete_Transaction_Dtls(
99 p_api_version => 1.0,
100 p_init_msg_list => p_init_msg_list,
101 p_commit => p_commit,
102 x_return_status => x_return_status,
103 x_msg_count => x_msg_count,
104 x_msg_data => x_msg_data,
105 p_transaction_line_id => p_line_inst_dtl_id
106 );
107
108 END IF;
109
110
111 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
112 RAISE FND_API.G_EXC_ERROR;
113 END IF;
114
115
116 --
117 -- End of API body.
118 --
119
120 -- Standard check for p_commit
121 IF FND_API.to_Boolean( p_commit )
122 THEN
123 COMMIT WORK;
124 END IF;
125
126
127 -- Debug Message
128 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Public API: ' || l_api_name || 'end');
129
130
131 -- Standard call to get message count and if count is 1, get message info.
132 FND_MSG_PUB.Count_And_Get
133 ( p_count => x_msg_count,
134 p_data => x_msg_data
135 );
136
137 EXCEPTION
138 WHEN FND_API.G_EXC_ERROR THEN
139 x_return_status := FND_API.G_RET_STS_ERROR;
140 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
141 P_API_NAME => L_API_NAME
142 ,P_PKG_NAME => G_PKG_NAME
143 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
144 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
145 ,X_MSG_COUNT => X_MSG_COUNT
146 ,X_MSG_DATA => X_MSG_DATA
147 ,X_RETURN_STATUS => X_RETURN_STATUS);
148
149 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
151 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
152 P_API_NAME => L_API_NAME
153 ,P_PKG_NAME => G_PKG_NAME
154 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
155 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
156 ,X_MSG_COUNT => X_MSG_COUNT
157 ,X_MSG_DATA => X_MSG_DATA
158 ,X_RETURN_STATUS => X_RETURN_STATUS);
159
160 WHEN OTHERS THEN
161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
162 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
163 P_API_NAME => L_API_NAME
164 ,P_PKG_NAME => G_PKG_NAME
165 ,P_SQLERRM => sqlerrm
166 ,P_SQLCODE => sqlcode
167 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
168 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
169 ,X_MSG_COUNT => X_MSG_COUNT
170 ,X_MSG_DATA => X_MSG_DATA
171 ,X_RETURN_STATUS => X_RETURN_STATUS);
172 END Delete_Installation_Detail;
173
174
175
176 -- PROCEDURE Update_Inst_Details_ORDER
177 -- USAGE updates the installation detail to include the order line id.
178 -- NEED
179 -- 1. installation details need to be linked to an order. when creating installation details from OC the order line id does not exist. this procedure is called after the quote is converted to an order.
180
181
182 PROCEDURE Update_Inst_Details_ORDER
183 (
184 p_api_version_number IN NUMBER,
185 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
186 p_commit IN VARCHAR2 := FND_API.G_FALSE,
187 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
188 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
189 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
190 p_quote_line_shipment_id IN NUMBER,
191 p_order_line_id IN NUMBER
192 )
193 IS
194
195 -- hyang, bug 2860045, performance fix.
196
197 CURSOR C_quote_line(quote_line_shipment_id number)
198 IS
199 SELECT quote_line_id
200 FROM aso_shipments
201 WHERE shipment_id = quote_line_shipment_id;
202
203 CURSOR c_csi_details(quote_line_id number)
204 IS
205 select transaction_line_id
206 from csi_t_transaction_lines
207 where source_transaction_id = quote_line_id
208 and source_transaction_table = 'ASO_QUOTE_LINES_ALL';
209
210
211 l_api_version_number number := 1.0;
212 l_api_name VARCHAR2(240) := 'UPDATE_INST_DETAILS_ORDER';
213 l_src_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
214 lx_new_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
215 l_quote_line_id NUMBER;
216
217
218 BEGIN
219
220 -- Standard Start of API savepoint
221 SAVEPOINT Update_Inst_details_ORder_PUB;
222
223 -- Standard call to check for call compatibility.
224 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
225 p_api_version_number,
226 l_api_name,
227 G_PKG_NAME)
228 THEN
229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
230 END IF;
231
232
233 -- Initialize message list if p_init_msg_list is set to TRUE.
234 IF FND_API.to_Boolean( p_init_msg_list )
235 THEN
236 FND_MSG_PUB.initialize;
237 END IF;
238
239 -- Debug Message
240 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
241
242 -- Initialize API return status to SUCCESS
243 x_return_status := FND_API.G_RET_STS_SUCCESS;
244
245 if NOT (CSI_utility_grp.ib_active())
246 then
247 -- old ib module.
248 null;
249 ELSE
250 -- new ib module.
251
252 -- get quote_line_id by shipment_id.
253 Open C_quote_line(p_quote_line_shipment_id);
254 FETCH C_quote_line into l_src_txn_line_rec.source_transaction_id;
255
256 If ( C_quote_line%NOTFOUND) Then
257 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
258 FND_MESSAGE.Set_Name('ASO', 'API_MISSING_UPDATE_TARGET');
259 FND_MESSAGE.Set_Token ('INFO', 'quote', FALSE);
260 FND_MSG_PUB.Add;
261 END IF;
262 Close C_quote_line;
263 raise FND_API.G_EXC_ERROR;
264 END IF;
265 Close C_quote_line;
266
267 Open c_csi_details(l_src_txn_line_rec.source_transaction_id);
268 FETCH c_csi_details into l_quote_line_id;
269 IF (c_csi_details%found) THEN
270
271 l_src_txn_line_rec.source_transaction_table := 'ASO_QUOTE_LINES_ALL';
272 lx_new_txn_line_rec.source_transaction_table := 'OE_ORDER_LINES_ALL';
273 lx_new_txn_line_rec.source_transaction_id := p_order_line_id;
274
275 l_src_txn_line_rec.source_transaction_type_id := 56;
276 lx_new_txn_line_rec.source_transaction_type_id := 51;
277
278 CSI_T_Txn_Details_GRP.Copy_Transaction_Dtls(
279 p_api_version => 1.0,
280 p_init_msg_list => p_init_msg_list,
281 p_commit => p_commit,
282 x_return_status => x_return_status,
283 x_msg_count => x_msg_count,
284 x_msg_data => x_msg_data,
285 p_src_txn_line_rec => l_src_txn_line_rec,
286 px_new_txn_line_rec => lx_new_txn_line_rec
287 );
288 END IF;
289 Close C_csi_details;
290
291 END IF;
292
293 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
294 RAISE FND_API.G_EXC_ERROR;
295 END IF;
296
297 -- Standard check for p_commit
298 IF FND_API.to_Boolean( p_commit )
299 THEN
300 COMMIT WORK;
301 END IF;
302
303
304 -- Debug Message
305 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Public API: ' || l_api_name || 'end');
306
307
308 -- Standard call to get message count and if count is 1, get message info.
309 FND_MSG_PUB.Count_And_Get
310 ( p_count => x_msg_count,
311 p_data => x_msg_data
312 );
313
314 EXCEPTION
315 WHEN FND_API.G_EXC_ERROR THEN
316 x_return_status := FND_API.G_RET_STS_ERROR;
317 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
318 P_API_NAME => L_API_NAME
319 ,P_PKG_NAME => G_PKG_NAME
320 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
321 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
322 ,X_MSG_COUNT => X_MSG_COUNT
323 ,X_MSG_DATA => X_MSG_DATA
324 ,X_RETURN_STATUS => X_RETURN_STATUS);
325
326 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
328 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
329 P_API_NAME => L_API_NAME
330 ,P_PKG_NAME => G_PKG_NAME
331 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
332 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
333 ,X_MSG_COUNT => X_MSG_COUNT
334 ,X_MSG_DATA => X_MSG_DATA
335 ,X_RETURN_STATUS => X_RETURN_STATUS);
336
337 WHEN OTHERS THEN
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
339 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
340 P_API_NAME => L_API_NAME
341 ,P_PKG_NAME => G_PKG_NAME
342 ,P_SQLERRM => sqlerrm
343 ,P_SQLCODE => sqlcode
344 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
345 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
346 ,X_MSG_COUNT => X_MSG_COUNT
347 ,X_MSG_DATA => X_MSG_DATA
348 ,X_RETURN_STATUS => X_RETURN_STATUS);
349
350 END Update_Inst_Details_ORDER;
351
352
353
354 -- FUNCTION Get_top_model_line_id
355 -- USAGE Returns the quote line id for the model/top parent for a given quote line id.
356 -- NEED
357 -- 1. The form passes the model quote line id for child items in the hierarchy string when calling the Installation Details form. This takes place when accessing Installation Details from the Action menu.
358
359
360 FUNCTION Get_top_model_line_id(p_qte_line_id NUMBER)
361 RETURN NUMBER
362 IS
363
364 x_top_model_line_id NUMBER;
365 lv_quote_line_id NUMBER;
366 x_inventory_item_id NUMBER;
367
368 CURSOR C_top_model_line_id(l_quote_line_id NUMBER) IS
369 select quote_line_id
370 from aso_line_relationships aso_rel
371 where aso_rel.related_quote_line_id = l_quote_line_id;
372
373 CURSOR C_item_id IS
374 select inventory_item_id
375 from aso_quote_lines_all
376 where quote_line_id = x_top_model_line_id;
377
378
379 BEGIN
380
381 -- initialize G_Debug_Flag
382 ASO_DEBUG_PUB.G_Debug_Flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
383
384 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
385 aso_debug_pub.add('Get_top_model_line_id - Begin ',1,'Y');
386 aso_debug_pub.add('p_qte_line_id: '||nvl(to_char(p_qte_line_id),'null'),1,'N');
387 END IF;
388
389 x_top_model_line_id := p_qte_line_id;
390
391 OPEN C_top_model_line_id(x_top_model_line_id);
392 FETCH C_top_model_line_id INTO lv_quote_line_id;
393
394 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
395 aso_debug_pub.add('Before loop - lv_quote_line_id: '||nvl(to_char(lv_quote_line_id),'null'),1,'N');
396 END IF;
397
398 IF (C_top_model_line_id%NOTFOUND) THEN
399 CLOSE C_top_model_line_id;
400 return null;
401 END IF;
402 x_top_model_line_id := lv_quote_line_id;
403 CLOSE C_top_model_line_id;
404
405 Loop
406 OPEN C_top_model_line_id(x_top_model_line_id);
407 FETCH C_top_model_line_id INTO lv_quote_line_id;
408
409 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
413 IF (C_top_model_line_id%NOTFOUND) THEN
410 aso_debug_pub.add('Inside Loop - lv_quote_line_id: '||nvl(to_char(lv_quote_line_id),'null'),1,'N');
411 END IF;
412
414 CLOSE C_top_model_line_id;
415 EXIT;
416 END IF;
417
418 CLOSE C_top_model_line_id;
419 x_top_model_line_id := lv_quote_line_id;
420 End Loop;
421
422 return x_top_model_line_id ;
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 RETURN NULL;
427
428 END Get_top_model_line_id ;
429
430 END ASO_instbase_INT;