[Home] [Help]
PACKAGE BODY: APPS.ASO_CONFIG_OPERATIONS_PVT
Source
1 PACKAGE BODY ASO_CONFIG_OPERATIONS_PVT as
2 /* $Header: asovcfob.pls 120.6 2006/11/02 23:51:49 skulkarn ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ASO_CONFIG_OPERATIONS_PVT';
5
6 PROCEDURE Add_to_Container_from_IB(
7 P_Api_Version_Number IN NUMBER,
8 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
9 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
10 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
11 P_Control_Rec IN ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_Miss_Control_Rec,
12 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type:=ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec,
13 P_Quote_line_Id IN NUMBER,
14 P_instance_tbl IN ASO_QUOTE_HEADERS_PVT.Instance_Tbl_Type,
15 x_Qte_Header_Rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
16 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
17 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
18 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
19 )is
20
21 /******************************************************************************
22 Cursor to retrieve Config header Id and Config rev number for a given instance
23 *******************************************************************************/
24
25 Cursor c_item_csr(p_instance_id Number) is
26 Select config_inst_hdr_id,config_inst_rev_num
27 from csi_item_instances
28 where instance_id = p_instance_id;
29
30 /****************************************************************
31 Cursor to find item details from Quote line containing Model Item
32 ****************************************************************/
33
34 Cursor c_mdl_item_details_csr(p_quote_line_id Number) is
35 Select a.quote_header_id, a.uom_code, a.quantity, a.inventory_item_id, a.organization_id
36 from
37 ASO_QUOTE_LINES_ALL a
38 where a.quote_line_id = p_quote_line_id;
39
40 /***************************************************************************************
41 Cursor to find complete configuration and valid configuration info from CZ_CONFIG_HDRS
42 *****************************************************************************************/
43 Cursor c_configuration_details_csr(p_config_header_id Number,p_config_rev_number Number) is
44 Select Decode (has_failures,'0','Y','N'), decode (config_status,'2','Y','N')
45 from CZ_CONFIG_HDRS
46 where config_hdr_id = p_config_header_id
47 and config_rev_nbr = p_config_rev_number;
48
49 Cursor c_last_update_date_csr(p_qte_header_id Number) is
50 SELECT last_update_date
51 FROM ASO_QUOTE_HEADERS_ALL
52 WHERE quote_header_id = p_qte_header_id;
53
54
55
56 l_api_name CONSTANT VARCHAR2(30) := 'Add_to_Container_from_IB';
57 l_api_version CONSTANT NUMBER := 1.0;
58 l_ins_config_hdr_tbl CZ_API_PUB.CONFIG_TBL_TYPE;
59 l_hdr_id Number;
60 l_rev_nbr Number;
61 l_complete_configuration_flag VARCHAR2(1);
62 l_valid_configuration_flag VARCHAR2(1);
63 l_config_hdr_id Number;
64 l_config_rev_nbr Number;
65 l_quote_header_id Number;
66 l_appl_param_rec CZ_API_PUB.appl_param_rec_type;
67 l_config_rec aso_quote_pub.qte_line_dtl_rec_type;
68 l_model_line_rec aso_quote_pub.qte_line_rec_type;
69 lx_config_tree_rec CZ_API_PUB.config_model_rec_type;
70 l_last_update_date Date;
71 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type:=ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec;
72
73 Begin
74
75 SAVEPOINT Add_to_Container_from_ib_pvt;
76
77 -- Standard call to check for call compatibility.
78 IF NOT FND_API.Compatible_API_Call (
79 l_api_version,
80 p_api_version_Number,
81 l_api_name,
82 G_PKG_NAME )
83 THEN
84 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 END IF;
86
87 -- Initialize message list if p_init_msg_list is set to TRUE.
88 IF FND_API.to_Boolean( p_init_msg_list ) THEN
89 FND_MSG_PUB.initialize;
90 END IF;
91
92
93 -- Initialize API return status to success
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95
96 /**************************************************************
97 Create Config Instance header table
98 **************************************************************/
99 For i IN 1.. P_Instance_tbl.count Loop
100 For c_item_rec IN c_item_csr(P_Instance_tbl(i).instance_id) LOOP
101
102 IF aso_debug_pub.g_debug_flag = 'Y' THEN
103 aso_debug_pub.add('Add_to_Container_from_IB:Config Header Id:'||c_item_rec.config_inst_hdr_id,1,'N');
104 aso_debug_pub.add('Add_to_Container_from_IB:Config Rev Num:'||c_item_rec.config_inst_rev_num,1,'N');
105 END IF;
106
107 l_ins_config_hdr_tbl(i).config_hdr_id := c_item_rec.config_inst_hdr_id;
108 l_ins_config_hdr_tbl(i).config_rev_nbr:= c_item_rec.config_inst_rev_num;
109 END LOOP;
110 END LOOP;
111
112 /*******************************************************************
113 Find Config Header Id and Config rev number for the container model
114 ********************************************************************/
115 Begin
116 Select a. config_header_id , a.config_revision_num
117 into l_hdr_id , l_rev_nbr
118 from
119 ASO_QUOTE_LINE_DETAILS a
120 where a.quote_line_id = p_quote_line_id;
121
122 Exception
123 WHEN NO_DATA_FOUND then
124 l_hdr_id := null;
125 l_rev_nbr:= null;
126 end;
127
128 IF aso_debug_pub.g_debug_flag = 'Y' THEN
129 aso_debug_pub.add('Add_to_Container_from_IB: Config Header Id:'||l_hdr_id,1,'N');
130 aso_debug_pub.add('Add_to_Container_from_IB: Config Rev Number:'||l_rev_nbr,1,'N');
131 END IF;
132
133 /**************************************************************
134 Find item details from Quote line containing Model Item
135 ***************************************************************/
136
137 For c_mdl_item_details_rec IN c_mdl_item_details_csr(p_quote_line_id) LOOP
138
139 IF aso_debug_pub.g_debug_flag = 'Y' THEN
140 aso_debug_pub.add('Add_to_Container_from_IB:Inventory Item Id:'||c_mdl_item_details_rec.inventory_item_id,1,'N');
141 aso_debug_pub.add('Add_to_Container_from_IB:Organization Id'||c_mdl_item_details_rec.organization_id,1,'N');
142 aso_debug_pub.add('Add_to_Container_from_IB:Quantity:'||c_mdl_item_details_rec.quantity,1,'N');
143 aso_debug_pub.add('Add_to_Container_from_IB:UOM Code:'||c_mdl_item_details_rec.uom_code,1,'N');
144 END IF;
145
146 /**************************************************************
147 Create Applicability parameter record
148 ***************************************************************/
149 --l_appl_param_rec.calling_application_id:= fnd_profile.value('JTF_PROFILE_DEFAULT_APPLICATION');
150
151
152 l_appl_param_rec.calling_application_id:= fnd_global.RESP_APPL_ID;
153 /**************************************************************
154 Add Instances to container model
155 **************************************************************/
156 if l_hdr_id is not null then
157
158 CZ_NETWORK_API_PUB.ADD_TO_CONFIG_TREE(
159 p_api_version => 1.0,
160 p_inventory_item_id => c_mdl_item_details_rec.inventory_item_id,
161 p_organization_id => c_mdl_item_details_rec.organization_id,
162 p_config_hdr_id => l_hdr_id,
163 p_config_rev_nbr => l_rev_nbr,
164 p_instance_tbl => l_ins_config_hdr_tbl,
165 p_tree_copy_mode => 'R',
166 p_appl_param_rec => l_appl_param_rec,
167 p_validation_context=> CZ_API_PUB.G_INSTALLED,
168 x_config_model_rec => lx_config_tree_rec,
169 x_return_status => x_return_status,
170 x_msg_count => x_msg_count,
171 x_msg_data => x_msg_data);
172
173 else
174
175 /**************************************************************
176 CZ call using model id and Instance header Ids
177 **************************************************************/
178
179 CZ_NETWORK_API_PUB.ADD_TO_CONFIG_TREE(
180 p_api_version => 1.0,
181 p_inventory_item_id => c_mdl_item_details_rec.inventory_item_id,
182 p_organization_id => c_mdl_item_details_rec.organization_id,
183 p_config_hdr_id => null,
184 p_config_rev_nbr => null,
185 p_instance_tbl => l_ins_config_hdr_tbl,
186 p_tree_copy_mode => 'R',
187 p_appl_param_rec => l_appl_param_rec,
188 p_validation_context=> CZ_API_PUB.G_INSTALLED,
189 x_config_model_rec => lx_config_tree_rec,
190 x_return_status => x_return_status,
191 x_msg_count => x_msg_count,
192 x_msg_data => x_msg_data);
193
194 end if;
195
196 IF aso_debug_pub.g_debug_flag = 'Y' THEN
197
198 aso_debug_pub.add('Add_to_Container_from_IB:Add_to_config_tree:Return status:'||x_return_status,1,'N');
199 aso_debug_pub.add('Add_to_Container_from_IB:Add_to_config_tree:Msg count:'||x_msg_count,1,'N');
200 aso_debug_pub.add('Add_to_Container_from_IB:Add_to_config_tree:Config Header Id:'||lx_config_tree_rec.config_hdr_id,1,'N');
201 aso_debug_pub.add('Add_to_Container_from_IB:Add_to_config_tree:Config Rev Number:'||lx_config_tree_rec.config_rev_nbr,1,'N');
202
203 END IF;
204
205 IF x_return_status = FND_API.G_RET_STS_ERROR then
206 raise FND_API.G_EXC_ERROR;
207 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
208 raise FND_API.G_EXC_UNEXPECTED_ERROR;
209 END IF;
210
211 /****************************************************************************
212 Get complete configuration and valid configuration info from CZ_CONFIG_HDRS
213 ****************************************************************************/
214 Open c_configuration_details_csr(lx_config_tree_rec.config_hdr_id,lx_config_tree_rec.config_rev_nbr);
215
216 fetch c_configuration_details_csr into l_valid_configuration_flag,l_complete_configuration_flag;
217
218 IF aso_debug_pub.g_debug_flag = 'Y' THEN
219 aso_debug_pub.add('Add_to_Container_from_IB:Config Valid Info:'||l_complete_configuration_flag,1,'N');
220 aso_debug_pub.add('Add_to_Container_from_IB:Config Complete Info:'||l_valid_configuration_flag,1,'N');
221 END IF;
222
223 IF c_configuration_details_csr%NOTFOUND THEN
224 CLOSE c_configuration_details_csr;
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
227 FND_MESSAGE.Set_Name('ASO', 'ASO_CFG_DETAILS_NOT_FOUND');
228 FND_MSG_PUB.Add;
229 END IF;
230 raise FND_API.G_EXC_ERROR;
231 END IF;
232
233 Close c_configuration_details_csr;
234
235 /**************************************************************
236 Copy config data from CZ_CONFIG_DETAILS_V
237 ***************************************************************/
238
239 l_config_rec.quote_line_id := p_quote_line_id;
240 l_config_rec.complete_configuration_flag := l_complete_configuration_flag;
241 l_config_rec.valid_configuration_flag := l_valid_configuration_flag;
242 l_config_rec.config_header_id := l_hdr_id;
243 l_config_rec.config_revision_num := l_rev_nbr;
244
245 l_model_line_rec.quote_line_id := p_quote_line_id;
246 l_model_line_rec.quantity := c_mdl_item_details_rec.quantity;
247 l_model_line_rec.uom_code := c_mdl_item_details_rec.uom_code;
248
249 l_config_hdr_id := lx_config_tree_rec.config_hdr_id ;
250 l_config_rev_nbr := lx_config_tree_rec.config_rev_nbr;
251
252 l_quote_header_id := c_mdl_item_details_rec.quote_header_id;
253
254 Open c_last_update_date_csr(P_QTE_HEADER_REC.quote_header_id);
255
256 fetch c_last_update_date_csr into l_last_update_date;
257
258 IF aso_debug_pub.g_debug_flag = 'Y' THEN
259 aso_debug_pub.add('Add_to_Container_from_IB:Last Update date:'||l_last_update_date,1,'N');
260 END IF;
261
262 IF c_last_update_date_csr%NOTFOUND THEN
263 CLOSE c_last_update_date_csr;
264 x_return_status := FND_API.G_RET_STS_ERROR;
265 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
266 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_COLUMN');
267 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
268 FND_MSG_PUB.ADD;
269 END IF;
270 raise FND_API.G_EXC_ERROR;
271
272 END IF;
273
274 Close c_last_update_date_csr;
275
276 l_qte_header_rec := p_qte_header_rec;
277
278 l_QTE_HEADER_REC.last_update_date := l_last_update_date;
279
280 ASO_CFG_PUB.Get_config_details(
281 P_Api_Version_Number =>1.0,
282 P_Init_Msg_List => FND_API.g_false,
283 p_commit => FND_API.g_false,
284 p_control_rec => p_control_rec,
285 p_config_rec => l_config_rec,
286 p_model_line_rec => l_model_line_rec,
287 p_config_hdr_id => l_config_hdr_id,
288 p_config_rev_nbr => l_config_rev_nbr ,
289 p_qte_header_rec => l_qte_header_rec,
290 x_return_status => x_return_status ,
291 x_msg_count => x_msg_count ,
292 x_msg_data => x_msg_data
293 );
294
295
296 IF aso_debug_pub.g_debug_flag = 'Y' THEN
297 aso_debug_pub.add('Add_to_Container_from_IB:Get_config_details:Return status:'||x_return_status,1,'N');
298 aso_debug_pub.add('Add_to_Container_from_IB:Get_config_details:Msg count:'||x_msg_count,1,'N');
299 END IF;
300
301 IF x_return_status = FND_API.G_RET_STS_ERROR then
302 raise FND_API.G_EXC_ERROR;
303 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
304 raise FND_API.G_EXC_UNEXPECTED_ERROR;
305 END IF;
306
307 x_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(l_quote_header_id);
308
309 -- End of API body
310 IF aso_debug_pub.g_debug_flag = 'Y' THEN
311 aso_debug_pub.add('****** End of add to container API ******', 1, 'Y');
312 END IF;
313
314 -- Standard check of p_commit
315 IF FND_API.To_Boolean(p_commit) THEN
316 COMMIT WORK;
317 END IF;
318
319 -- Standard call to get message count and if count is 1, get message info
320 FND_Msg_Pub.Count_And_Get(
321 p_encoded => FND_API.G_FALSE,
322 p_count => x_msg_count ,
323 p_data => x_msg_data
324 );
325
326 END LOOP;
327
328 EXCEPTION
329 WHEN FND_API.G_EXC_ERROR THEN
330 x_return_status := FND_API.G_RET_STS_ERROR;
331 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
332 P_API_NAME => L_API_NAME
333 ,P_PKG_NAME => G_PKG_NAME
334 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
335 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
336 ,X_MSG_COUNT => X_MSG_COUNT
337 ,X_MSG_DATA => X_MSG_DATA
338 ,X_RETURN_STATUS => X_RETURN_STATUS);
339
340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
342 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
343 P_API_NAME => L_API_NAME
344 ,P_PKG_NAME => G_PKG_NAME
345 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
346 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
347 ,X_MSG_COUNT => X_MSG_COUNT
348 ,X_MSG_DATA => X_MSG_DATA
349 ,X_RETURN_STATUS => X_RETURN_STATUS);
350
351 WHEN OTHERS THEN
352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
353 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
354 P_API_NAME => L_API_NAME
355 ,P_PKG_NAME => G_PKG_NAME
356 ,P_SQLERRM => sqlerrm
357 ,P_SQLCODE => sqlcode
358 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
359 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
360 ,X_MSG_COUNT => X_MSG_COUNT
361 ,X_MSG_DATA => X_MSG_DATA
362 ,X_RETURN_STATUS => X_RETURN_STATUS);
363
364 END Add_to_Container_from_IB;
365
366
367 PROCEDURE Reconfigure_from_IB(
368 P_Api_Version_Number IN NUMBER,
369 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
370 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
371 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
372 P_Control_Rec IN ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_Miss_Control_Rec,
373 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type:=ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec,
374 P_instance_tbl IN ASO_QUOTE_HEADERS_PVT.Instance_Tbl_Type,
375 x_Qte_Header_Rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
376 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
377 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
378 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
379 )IS
380
381 /************************************************************************************
382 Cursor to retrieve config header Id and Config revision number for a given instance
383 *************************************************************************************/
384
385 Cursor c_item_csr(p_instance_id Number) is
386 Select config_inst_hdr_id , config_inst_rev_num from csi_item_instances
387 where instance_id = p_instance_id;
388
389 /************************************************************************************
390 Cursor to retrieve UOM and quantity for a config header Id and Config revision number
391 *************************************************************************************/
392
393 Cursor c_uom_qty_csr(p_config_hdr_id Number,p_config_rev_nbr Number,p_config_item_id Number) is
394 Select a.uom_code, a.quantity
395 from
396 cz_config_details_v a
397 where a.config_hdr_id = p_config_hdr_id
398 and a.config_rev_nbr = p_config_rev_nbr
399 and a.config_item_id = p_config_item_id;
400
401 /***************************************************************************************
402 Cursor to find complete configuration and valid configuration info from CZ_CONFIG_HDRS
403 *****************************************************************************************/
404 Cursor c_configuration_details_csr(p_config_header_id Number,p_config_rev_number Number) is
405 Select Decode (has_failures,'0','Y','N'), decode (config_status,'2','Y','N')
406 from CZ_CONFIG_HDRS
407 where config_hdr_id = p_config_header_id
408 and config_rev_nbr = p_config_rev_number;
409
410 /****************************************************************
411 Cursor to find item details from Quote line containing Model Item
412 ****************************************************************/
413
414 Cursor c_mdl_item_details_csr(p_quote_line_id Number) is
415 Select a.quantity, a.uom_code
416 from aso_quote_lines_all a
417 where a.quote_line_id = p_quote_line_id;
418
419 /****************************************************************
420 Cursor to find last update date for quote
421 ****************************************************************/
422
423 Cursor c_last_update_date_csr(p_qte_header_id Number) is
424 SELECT last_update_date
425 FROM ASO_QUOTE_HEADERS_ALL
426 WHERE quote_header_id = p_qte_header_id;
427
428 l_api_name CONSTANT VARCHAR2(30) := 'Reconfigure_from_IB';
429 l_api_version CONSTANT NUMBER := 1.0;
430 l_ins_config_hdr_tbl CZ_API_PUB.CONFIG_TBL_TYPE;
431 l_uom VARCHAR2(3);
432 l_quantity Number;
433 l_complete_configuration_flag VARCHAR2(1);
434 l_valid_configuration_flag VARCHAR2(1);
435 l_config_hdr_id Number;
436 l_config_rev_nbr Number;
437 l_last_update_date Date;
438 l_appl_param_rec CZ_API_PUB.appl_param_rec_type;
439
440 l_config_rec aso_quote_pub.qte_line_dtl_rec_type;
441 l_model_line_rec aso_quote_pub.qte_line_rec_type;
442 l_QTE_LINE_TBL ASO_QUOTE_PUB.Qte_Line_Tbl_Type :=ASO_QUOTE_PUB.G_MISS_QTE_LINE_TBL;
443 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type:=ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec;
444
445 lx_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type:=ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec;
446 lx_QTE_LINE_TBL ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
447 lx_Qte_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_dtl_Tbl_Type;
448 lx_out_config_tree_tbl CZ_API_PUB.config_model_tbl_type;
449 lx_hd_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
450 lx_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
451 lx_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
452 lx_hd_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
453 lx_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
454 lX_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
455 lx_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
456 lx_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
457 lx_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
458 lx_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
459 lx_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
460 lx_hd_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
461 lx_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
462 lX_Ln_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
463 lX_Ln_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
464 lx_ln_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
465 lx_ln_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
466 lx_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
467 lx_ln_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
468 lx_ln_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
469
470
471 Begin
472
473 SAVEPOINT Reconfigure_from_IB_pvt;
474
475 -- Standard call to check for call compatibility.
476 IF NOT FND_API.Compatible_API_Call (
477 l_api_version,
478 p_api_version_Number,
479 l_api_name,
480 G_PKG_NAME )
481 THEN
482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
483 END IF;
484
485 -- Initialize message list if p_init_msg_list is set to TRUE.
486 IF FND_API.to_Boolean( p_init_msg_list ) THEN
487 FND_MSG_PUB.initialize;
488 END IF;
489
490
491 -- Initialize API return status to success
492 x_return_status := FND_API.G_RET_STS_SUCCESS;
493
494
495 /*********************************************************************
496 Create table of Config header Ids
497 **********************************************************************/
498
499 For i IN 1.. P_Instance_tbl.count Loop
500 For c_item_rec IN c_item_csr(P_Instance_tbl(i).instance_id) LOOP
501 l_ins_config_hdr_tbl(i).config_hdr_id := c_item_rec.config_inst_hdr_id;
502 l_ins_config_hdr_tbl(i).config_rev_nbr := c_item_rec.config_inst_rev_num;
503
504 IF aso_debug_pub.g_debug_flag = 'Y' THEN
505 aso_debug_pub.add('Reconfigure_from_IB:Config Header Id:'||c_item_rec.config_inst_hdr_id,1,'N');
506 aso_debug_pub.add('Reconfigure_from_IB:Config Rev Num:'||c_item_rec.config_inst_rev_num,1,'N');
507 END IF;
508
509 END LOOP;
510 END LOOP;
511
512 /**************************************************************
513 Create Applicability parameter record
514 ***************************************************************/
515 --l_appl_param_rec.calling_application_id:= fnd_profile.value('JTF_PROFILE_DEFAULT_APPLICATION');
516
517 l_appl_param_rec.calling_application_id:= fnd_global.RESP_APPL_ID;
518 /**************************************************************
519 Call CZ API to return config tree
520 **************************************************************/
521
522 CZ_NETWORK_API_PUB.Generate_config_trees(
523 p_api_version => 1.0,
524 p_config_tbl => l_ins_config_hdr_tbl,
525 p_tree_copy_mode => 'R',
526 p_appl_param_rec => l_appl_param_rec,
527 p_validation_context => CZ_API_PUB.G_INSTALLED,
528 x_config_model_tbl => lx_out_config_tree_tbl,
529 x_return_status => x_return_status,
530 x_msg_count => x_msg_count,
531 x_msg_data => x_msg_data);
532
533
534 IF aso_debug_pub.g_debug_flag = 'Y' THEN
535 aso_debug_pub.add('Reconfigure_from_IB:Generate_config_trees:Return status:'||x_return_status,1,'N');
536 aso_debug_pub.add('Reconfigure_from_IB:Generate_config_trees:Msg count:'||x_msg_count,1,'N');
537 END IF;
538
539 IF x_return_status = FND_API.G_RET_STS_ERROR then
540 raise FND_API.G_EXC_ERROR;
541 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
542 raise FND_API.G_EXC_UNEXPECTED_ERROR;
543 END IF;
544
545 /**************************************************************
546 Call Update Quote for creating Quote lines
547 **************************************************************/
548
549 For j IN 1.. lx_out_config_tree_tbl.count Loop
550
551 Open c_uom_qty_csr(lx_out_config_tree_tbl(j).config_hdr_id,lx_out_config_tree_tbl(j).config_rev_nbr,lx_out_config_tree_tbl(j).config_item_id);
552
553 fetch c_uom_qty_csr into l_uom, l_quantity;
554
555 IF aso_debug_pub.g_debug_flag = 'Y' THEN
556 aso_debug_pub.add('Reconfigure_from_IB:UOM:'||l_uom,1,'N');
557 aso_debug_pub.add('Reconfigure_from_IB:Quantity:'||l_quantity,1,'N');
558 END IF;
559
560 IF c_uom_qty_csr%NOTFOUND THEN
561 CLOSE c_uom_qty_csr;
562 x_return_status := FND_API.G_RET_STS_ERROR;
563 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
564 FND_MESSAGE.Set_Name('ASO', 'ASO_UOM_QTY_NOT_FOUND');
565 FND_MSG_PUB.Add;
566 END IF;
567 raise FND_API.G_EXC_ERROR;
568 END IF;
569
570 l_Qte_Line_tbl(j).QUANTITY := l_quantity;
571 l_Qte_Line_tbl(j).UOM_CODE := l_uom;
572
573 Close c_uom_qty_csr;
574
575 l_Qte_Line_tbl(j).quote_header_id := P_QTE_HEADER_REC.quote_header_id;
576 l_Qte_Line_tbl(j).OPERATION_CODE :='CREATE';
577 l_Qte_Line_tbl(j).ORGANIZATION_ID := lx_out_config_tree_tbl(j).organization_id;
578 l_Qte_Line_tbl(j).INVENTORY_ITEM_ID := lx_out_config_tree_tbl(j).inventory_item_id;
579 l_Qte_Line_tbl(j).LINE_CATEGORY_CODE := 'ORDER';
580
581 END LOOP;
582
583 Open c_last_update_date_csr(P_QTE_HEADER_REC.quote_header_id);
584
585 fetch c_last_update_date_csr into l_last_update_date;
586
587 IF aso_debug_pub.g_debug_flag = 'Y' THEN
588 aso_debug_pub.add('Reconfigure_from_IB:Last Update date:'||l_last_update_date,1,'N');
589 END IF;
590
591 IF c_last_update_date_csr%NOTFOUND THEN
592 CLOSE c_configuration_details_csr;
593 x_return_status := FND_API.G_RET_STS_ERROR;
594 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
595 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_COLUMN');
596 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
597 FND_MSG_PUB.ADD;
598 END IF;
599 raise FND_API.G_EXC_ERROR;
600
601 END IF;
602
603 Close c_last_update_date_csr;
604
605 l_qte_header_rec := p_qte_header_rec;
606
607 l_QTE_HEADER_REC.last_update_date := l_last_update_date;
608
609 ASO_QUOTE_PUB.Update_Quote(
610 p_api_version_number => 1.0,
611 p_init_msg_list => p_init_msg_list,
612 p_commit => p_commit,
613 p_control_rec => p_control_rec,
614 p_qte_header_rec => l_qte_header_rec,
615 P_Qte_Line_Tbl => l_Qte_Line_Tbl,
616 X_Qte_Header_Rec => lx_qte_header_rec,
617 X_Qte_Line_Tbl => lx_Qte_Line_Tbl,
618 X_Qte_Line_Dtl_Tbl => lx_Qte_Line_Dtl_Tbl,
619 X_hd_Price_Attributes_Tbl => lx_hd_Price_Attr_Tbl,
620 X_hd_Payment_Tbl => lx_hd_Payment_Tbl,
621 X_hd_Shipment_Tbl => lx_hd_Shipment_Tbl,
622 X_hd_Freight_Charge_Tbl => lx_hd_Freight_Charge_Tbl,
623 X_hd_Tax_Detail_Tbl => lx_hd_Tax_Detail_Tbl,
624 X_hd_Attr_Ext_Tbl => lX_hd_Attr_Ext_Tbl,
625 X_hd_Sales_Credit_Tbl => lx_hd_Sales_Credit_Tbl,
626 X_hd_Quote_Party_Tbl => lx_Quote_Party_Tbl,
627 X_Line_Attr_Ext_Tbl => lx_Line_Attr_Ext_Tbl,
628 X_line_rltship_tbl => lx_line_rltship_tbl,
629 X_Price_Adjustment_Tbl => lx_Price_Adjustment_Tbl,
630 X_Price_Adj_Attr_Tbl => lx_Price_Adj_Attr_Tbl,
631 X_Price_Adj_Rltship_Tbl => lx_Price_Adj_Rltship_Tbl,
632 X_ln_Price_Attributes_Tbl=> lx_ln_Price_Attr_Tbl,
633 X_ln_Payment_Tbl => lx_ln_Payment_Tbl,
634 X_ln_Shipment_Tbl => lx_ln_Shipment_Tbl,
635 X_ln_Freight_Charge_Tbl => lx_ln_Freight_Charge_Tbl,
636 X_ln_Tax_Detail_Tbl => lx_ln_Tax_Detail_Tbl,
637 X_Ln_Sales_Credit_Tbl => lX_Ln_Sales_Credit_Tbl,
638 X_Ln_Quote_Party_Tbl => lX_Ln_Quote_Party_Tbl,
639 X_Return_Status => x_Return_Status,
640 X_Msg_Count => x_Msg_Count,
641 X_Msg_Data => x_Msg_Data);
642
643 IF aso_debug_pub.g_debug_flag = 'Y' THEN
644 aso_debug_pub.add('Reconfigure_from_IB:update_Quote:Return status:'||x_return_status,1,'N');
645 aso_debug_pub.add('Reconfigure_from_IB:update_Quote:Msg count:'||x_msg_count,1,'N');
646 END IF;
647
648 IF x_return_status = FND_API.G_RET_STS_ERROR then
649 raise FND_API.G_EXC_ERROR;
650 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
651 raise FND_API.G_EXC_UNEXPECTED_ERROR;
652 END IF;
653
654
655 /**************************************************************
656 Copy config data from CZ_CONFIG_DETAILS_V
657 **************************************************************/
658
659 For k IN 1.. lx_out_config_tree_tbl.count Loop
660
661 /****************************************************************************
662 Get complete configuration and valid configuration info from CZ_CONFIG_HDRS
663 ****************************************************************************/
664 Open c_configuration_details_csr(lx_out_config_tree_tbl(k).config_hdr_id,lx_out_config_tree_tbl(k).config_rev_nbr);
665
666 fetch c_configuration_details_csr into l_valid_configuration_flag,l_complete_configuration_flag;
667
668 IF aso_debug_pub.g_debug_flag = 'Y' THEN
669 aso_debug_pub.add('Reconfigure_from_IB:Config Valid Info:'||l_complete_configuration_flag,1,'N');
670 aso_debug_pub.add('Reconfigure_from_IB:Config Complete Info:'||l_valid_configuration_flag,1,'N');
671 END IF;
672
673 IF c_configuration_details_csr%NOTFOUND THEN
674 CLOSE c_configuration_details_csr;
675 x_return_status := FND_API.G_RET_STS_ERROR;
676 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
677 FND_MESSAGE.Set_Name('ASO', 'ASO_CFG_DETAILS_NOT_FOUND');
678 FND_MSG_PUB.Add;
679 END IF;
680 raise FND_API.G_EXC_ERROR;
681
682 END IF;
683
684 Close c_configuration_details_csr;
685
686
687 l_config_rec.quote_line_id := lx_Qte_Line_Tbl(k).quote_line_id;
688 l_config_rec.complete_configuration_flag := l_complete_configuration_flag;
689 l_config_rec.valid_configuration_flag := l_valid_configuration_flag;
690 l_config_rec.config_header_id := lx_out_config_tree_tbl(k).config_hdr_id;
691 l_config_rec.config_revision_num :=lx_out_config_tree_tbl(k).config_rev_nbr;
692
693
694 Open c_mdl_item_details_csr(lx_Qte_Line_Tbl(k).quote_line_id);
695
696 fetch c_mdl_item_details_csr into l_quantity, l_uom;
697
698 IF aso_debug_pub.g_debug_flag = 'Y' THEN
699 aso_debug_pub.add('Reconfigure_from_IB:Config Valid Info:'||l_complete_configuration_flag,1,'N');
700 aso_debug_pub.add('Reconfigure_from_IB:Config Complete Info:'||l_valid_configuration_flag,1,'N');
701 END IF;
702
703 IF c_mdl_item_details_csr%NOTFOUND THEN
704 CLOSE c_mdl_item_details_csr;
705 x_return_status := FND_API.G_RET_STS_ERROR;
706 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
707 FND_MESSAGE.Set_Name('ASO', 'ASO_MDL_DETAILS_NOT_FOUND');
708 FND_MSG_PUB.Add;
709 END IF;
710 raise FND_API.G_EXC_ERROR;
711 END IF;
712
713 Close c_mdl_item_details_csr;
714
715 l_model_line_rec.quote_line_id := lx_Qte_Line_Tbl(k).quote_line_id;
716 l_model_line_rec.quantity := l_quantity;
717 l_model_line_rec.uom_code := l_uom;
718
719 l_config_hdr_id := lx_out_config_tree_tbl(k).config_hdr_id ;
720 l_config_rev_nbr := lx_out_config_tree_tbl(k).config_rev_nbr ;
721
722 ASO_CFG_PUB.Get_config_details(
723 P_Api_Version_Number => 1.0,
724 P_Init_Msg_List => FND_API.g_false,
725 p_commit => FND_API.g_false,
726 p_control_rec => p_control_rec,
727 p_config_rec => l_config_rec,
728 p_model_line_rec => l_model_line_rec,
729 p_config_hdr_id => l_config_hdr_id,
730 p_config_rev_nbr => l_config_rev_nbr ,
731 p_qte_header_rec => lx_qte_header_rec,
732 x_return_status => x_return_status ,
733 x_msg_count => x_msg_count ,
734 x_msg_data => x_msg_data
735 );
736
737 IF aso_debug_pub.g_debug_flag = 'Y' THEN
738 aso_debug_pub.add('Reconfigure_from_IB:Get_config_details:Return status:'||x_return_status,1,'N');
739 aso_debug_pub.add('Reconfigure_from_IB:Get_config_details:Msg count:'||x_msg_count,1,'N');
740 END IF;
741
742 IF x_return_status = FND_API.G_RET_STS_ERROR then
743 raise FND_API.G_EXC_ERROR;
744 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
745 raise FND_API.G_EXC_UNEXPECTED_ERROR;
746 END IF;
747
748 END LOOP;
749
750 x_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(P_QTE_HEADER_REC.quote_header_id);
751
752 -- End of API body
753
754 IF aso_debug_pub.g_debug_flag = 'Y' THEN
755 aso_debug_pub.add('****** End of Reconfigure API ******', 1, 'Y');
756 END IF;
757
758 -- Standard check of p_commit
759 IF FND_API.To_Boolean(p_commit) THEN
760 COMMIT WORK;
761 END IF;
762
763 -- Standard call to get message count and if count is 1, get message info
764 FND_Msg_Pub.Count_And_Get(
765 p_encoded => FND_API.G_FALSE,
766 p_count => x_msg_count,
767 p_data => x_msg_data
768 );
769
770
771 EXCEPTION
772 WHEN FND_API.G_EXC_ERROR THEN
773 x_return_status := FND_API.G_RET_STS_ERROR;
774 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
775 P_API_NAME => L_API_NAME
776 ,P_PKG_NAME => G_PKG_NAME
777 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
778 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
779 ,X_MSG_COUNT => X_MSG_COUNT
780 ,X_MSG_DATA => X_MSG_DATA
781 ,X_RETURN_STATUS => X_RETURN_STATUS);
782
783 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
786 P_API_NAME => L_API_NAME
787 ,P_PKG_NAME => G_PKG_NAME
788 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
789 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
790 ,X_MSG_COUNT => X_MSG_COUNT
791 ,X_MSG_DATA => X_MSG_DATA
792 ,X_RETURN_STATUS => X_RETURN_STATUS);
793
794 WHEN OTHERS THEN
795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
796 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
797 P_API_NAME => L_API_NAME
798 ,P_PKG_NAME => G_PKG_NAME
799 ,P_SQLERRM => sqlerrm
800 ,P_SQLCODE => sqlcode
801 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
802 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
803 ,X_MSG_COUNT => X_MSG_COUNT
804 ,X_MSG_DATA => X_MSG_DATA
805 ,X_RETURN_STATUS => X_RETURN_STATUS);
806
807 END Reconfigure_from_IB;
808
809
810 PROCEDURE Deactivate_from_quote(
811 P_Api_Version_Number IN NUMBER,
812 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
813 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
814 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
815 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type:=ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec,
816 P_Control_Rec IN ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_Miss_Control_Rec,
817 P_qte_line_tbl IN ASO_QUOTE_PUB.Qte_line_tbl_type := ASO_QUOTE_PUB.G_MISS_Qte_line_tbl,
818 p_delete_flag IN VARCHAR2 := FND_API.G_TRUE,
819 x_Qte_Header_Rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
820 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
821 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
822 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
823 ) IS
824
825 /*******************************************************************
826 Cursor to check if item can be deactivated
827 ********************************************************************/
828
829 Cursor c_deactivate_item_csr(p_quote_line_id Number) is
830 Select a.config_header_id, a.config_revision_num, a.config_item_id,a.config_delta
831 from aso_quote_line_details a
832 where a.quote_line_id =p_quote_line_id;
833
834 /*******************************************************************
835 Cursor to find all items in a quote that can be deactivated
836 ********************************************************************/
837
838 Cursor c_deactivate_all_item_csr(p_quote_header_id Number) is
839 Select b.quote_line_id from aso_quote_line_details a,aso_quote_lines_all b
840 where b.quote_header_id = p_quote_header_id
841 and a.quote_line_id = b.quote_line_id
842 and a.config_delta = 0
843 and not exists(Select null from aso_quote_line_details c
844 where c.quote_line_id = a.ref_line_id
845 and c.config_delta = 0
846 );
847
848 /****************************************************************************
849 Cursor to Check if the quote line contains the top model
850 *****************************************************************************/
851 Cursor c_chk_qte_line_mdl_csr(p_quote_line_id Number) is
852 Select b.config_header_id,b.config_revision_num
853 from
854 ASO_QUOTE_LINE_DETAILS b
855 where b.quote_line_id = P_quote_line_id
856 and b.ref_type_code ='CONFIG'
857 and b.ref_line_id is null;
858
859 /**************************************************************
860 Cursor to find the quote line containing the top model
861 ***************************************************************/
862
863 Cursor c_top_mdl_csr(p_config_header_id Number,p_config_revision_num Number) is
864 Select b.config_item_id
865 from
866 ASO_QUOTE_LINE_DETAILS b
867 where b.config_header_id = p_config_header_id
868 and b.config_revision_num = p_config_revision_num
869 and b.ref_type_code ='CONFIG'
870 and b.ref_line_id is null;
871
872 /**************************************************************
873 Cursor to find the quote line details containing the top model
874 ***************************************************************/
875 Cursor c_top_mdl_details_csr(p_config_header_id Number,p_config_revision_num Number,p_config_item_id Number) is
876 Select a.quote_line_id,a.quantity, a.uom_code,
877 b.complete_configuration_flag, b.valid_configuration_flag
878 from
879 ASO_QUOTE_LINES_ALL a, ASO_QUOTE_LINE_DETAILS b
880 where a.quote_line_id = b.quote_line_id
881 and b.config_header_id = p_config_header_id
882 and b.config_revision_num = p_config_revision_num
883 and b.config_item_id = p_config_item_id;
884
885 Cursor c_check_for_macd( p_qte_line_id number) is
886 select config_model_type
887 from aso_quote_lines_all
888 where quote_line_id = p_qte_line_id;
889
890 Cursor c_last_update_date_csr(p_qte_header_id Number) is
891 SELECT last_update_date,object_version_number
892 FROM ASO_QUOTE_HEADERS_ALL
893 WHERE quote_header_id = p_qte_header_id;
894
895
896 l_count Number := 0;
897 i Number;
898 l_api_name CONSTANT VARCHAR2(30) := 'Deactivate_from_quote';
899 l_api_version CONSTANT NUMBER := 1.0;
900 l_qte_line_tbl ASO_QUOTE_PUB.QTE_LINE_Tbl_Type := ASO_QUOTE_PUB.G_MISS_Qte_line_tbl;
901 l_macd_qte_line_tbl ASO_QUOTE_PUB.QTE_LINE_Tbl_Type := ASO_QUOTE_PUB.G_MISS_Qte_line_tbl;
902 l_qte_line_rec ASO_QUOTE_PUB.qte_line_Rec_Type := ASO_QUOTE_PUB.G_MISS_QTE_LINE_REC;
903 l_quote_line_id Number;
904 l_quantity Number;
905 l_uom_code VARCHAR2(10);
906 l_order_line_type_id Number;
907 l_complete_configuration_flag VARCHAR2(1);
908 l_valid_configuration_flag VARCHAR2(1);
909 l_rev_num Number;
910 l_config_item_id Number;
911 l_config_header_id Number;
912 l_config_rev_nbr Number;
913 l_config_rec aso_quote_pub.qte_line_dtl_rec_type;
914 l_model_line_rec aso_quote_pub.qte_line_rec_type := ASO_QUOTE_PUB.G_MISS_QTE_LINE_REC;
915 l_line_count Number :=0;
916 l_copy_conf_mdl_Tbl aso_quote_pub.qte_line_dtl_tbl_type := ASO_QUOTE_PUB.G_MISS_Qte_Line_Dtl_TBL;
917 l_deactivate_mdl_Tbl aso_quote_pub.qte_line_dtl_tbl_type := ASO_QUOTE_PUB.G_MISS_Qte_Line_Dtl_TBL;
918 x boolean := FALSE;
919 k Number;
920 l_macd_flag varchar2(30);
921
922 l_last_update_date Date;
923 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type:=ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec;
924 l_obj_version_number number;
925
926 Begin
927
928 SAVEPOINT Deactivate_from_quote_pvt;
929
930 -- Standard call to check for call compatibility.
931 IF NOT FND_API.Compatible_API_Call (
932 l_api_version,
933 p_api_version_Number,
934 l_api_name,
935 G_PKG_NAME )
936 THEN
937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
938 END IF;
939
940 -- Initialize message list if p_init_msg_list is set to TRUE.
941 IF FND_API.to_Boolean( p_init_msg_list ) THEN
942 FND_MSG_PUB.initialize;
943 END IF;
944
945
946 -- Initialize API return status to success
947 x_return_status := FND_API.G_RET_STS_SUCCESS;
948
949 /********************************************************************************
950 Construct "Deactivate quote line table" if "Deactivate flag" is set to 'True".
951 ********************************************************************************/
952 IF (P_Control_Rec.deactivate_all = FND_API.G_TRUE)THEN
953 For c_deactivate_all_item_rec IN c_deactivate_all_item_csr(P_Qte_Header_Rec.quote_header_id) LOOP
954 l_line_count := l_line_count + 1;
955
956 IF aso_debug_pub.g_debug_flag = 'Y' THEN
957 aso_debug_pub.add('Deactivate_from_quote:quote_line_id:'||c_deactivate_all_item_rec.quote_line_id,1,'N');
958 END IF;
959
960 l_qte_line_tbl(l_line_count).quote_line_id := c_deactivate_all_item_rec.quote_line_id;
961 END LOOP;
962 else
963 l_qte_line_tbl := p_qte_line_tbl;
964 END IF;
965
966 -- fix for bug 4900023, if quote has non-MACD lines then igonore those lines
967 for i in 1..l_qte_line_tbl.count loop
968 open c_check_for_macd(l_qte_line_tbl(i).quote_line_id);
969 fetch c_check_for_macd into l_macd_flag;
970 close c_check_for_macd;
971
972 IF aso_debug_pub.g_debug_flag = 'Y' THEN
973 aso_debug_pub.add('Deactivate_from_quote:quote_line_id: '|| l_qte_line_tbl(i).quote_line_id ,1,'N');
974 aso_debug_pub.add('Deactivate_from_quote:l_macd_flag: '|| l_macd_flag ,1,'N');
975 END IF;
976
977 if nvl(l_macd_flag,'X') = 'N' then
978 l_macd_qte_line_tbl(l_macd_qte_line_tbl.count + 1).quote_line_id := l_qte_line_tbl(i).quote_line_id;
979 end if;
980
981 -- reset the flag
982 l_macd_flag := null;
983
984 end loop;
985
986 IF aso_debug_pub.g_debug_flag = 'Y' THEN
987 aso_debug_pub.add('Deactivate_from_quote:l_macd_qte_line_tbl.count: '|| l_macd_qte_line_tbl.count,1,'N');
988 aso_debug_pub.add('Deactivate_from_quote:ORIGINAL l_qte_line_tbl.count: '|| l_qte_line_tbl.count,1,'N');
989 END IF;
990
991 -- reset the qte line tbl to have only MACD Lines
992 l_qte_line_tbl := l_macd_qte_line_tbl;
993
994 IF aso_debug_pub.g_debug_flag = 'Y' THEN
995 aso_debug_pub.add('Deactivate_from_quote:MODIFIED l_qte_line_tbl.count: '|| l_qte_line_tbl.count,1,'N');
996 END IF;
997
998 -- end of fox for bug 4900023
999
1000 /*******************************************************************
1001 Deactivate the items by calling CZ API
1002 ********************************************************************/
1003
1004 For i IN 1..l_qte_line_tbl.count Loop
1005
1006 IF (P_Control_Rec.deactivate_all = FND_API.G_FALSE)THEN
1007
1008 For c_de_item_rec IN c_deactivate_item_csr(l_qte_line_tbl(i).quote_line_id) LOOP
1009 if c_de_item_rec.config_delta > 0 then
1010 x_return_status := FND_API.G_RET_STS_ERROR;
1011 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1012 FND_MESSAGE.Set_Name('ASO', 'ASO_ITEM_CANT_BE_DEACTIVATED');
1013 FND_MSG_PUB.Add;
1014 END IF;
1015 raise FND_API.G_EXC_ERROR;
1016
1017 end if;
1018 END LOOP;
1019 end if;
1020
1021 /*******************************************************************
1022 Check quote line for model
1023 ********************************************************************/
1024
1025 Open c_chk_qte_line_mdl_csr(l_qte_line_tbl(i).quote_line_id);
1026
1027 fetch c_chk_qte_line_mdl_csr into l_config_header_id, l_rev_num;
1028
1029 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1030 aso_debug_pub.add('Deactivate_from_quote:Config header Id:'||l_config_header_id,1,'N');
1031 aso_debug_pub.add('Deactivate_from_quote:Config Rev num:'||l_rev_num,1,'N');
1032 END IF;
1033
1034 IF c_chk_qte_line_mdl_csr%NOTFOUND THEN
1035 l_config_header_id := null;
1036 END IF;
1037
1038 Close c_chk_qte_line_mdl_csr;
1039
1040 If l_config_header_id is not null and p_delete_flag = fnd_api.g_true then
1041
1042 /*******************************************************************
1043 Delete Quote line
1044 ********************************************************************/
1045 l_qte_line_rec.operation_code := 'DELETE';
1046 l_qte_line_rec.quote_line_id := l_qte_line_tbl(i).quote_line_id;
1047
1048 ASO_QUOTE_LINES_PVT.Delete_Quote_Line (
1049 P_Api_Version_Number => 1.0,
1050 p_control_rec => p_control_rec,
1051 p_update_header_flag => FND_API.G_FALSE,
1052 P_qte_Line_Rec => l_qte_line_rec,
1053 X_Return_Status => x_return_status,
1054 X_Msg_Count => x_msg_count,
1055 X_Msg_Data => x_msg_data);
1056
1057 IF x_return_status = FND_API.G_RET_STS_ERROR then
1058 raise FND_API.G_EXC_ERROR;
1059 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1060 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1061 END IF;
1062
1063 else
1064
1065 For c_deactivate_item_rec IN c_deactivate_item_csr(l_qte_line_tbl(i).quote_line_id) LOOP
1066
1067 CZ_NETWORK_API_PUB.ext_deactivate_item(
1068 P_Api_version => 1.0,
1069 P_config_hdr_id => c_deactivate_item_rec.config_header_id,
1070 p_config_rev_nbr => c_deactivate_item_rec.config_revision_num,
1071 p_config_item_id => c_deactivate_item_rec.config_item_id,
1072 x_return_status => x_return_status,
1073 x_msg_count => x_msg_count,
1074 x_msg_data => x_msg_data );
1075
1076 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1077 aso_debug_pub.add('Deactivate_from_quote:ext_deactivate_item: Return status:'||x_return_status,1,'N');
1078 aso_debug_pub.add('Deactivate_from_quote:ext_deactivate_item: Msg count:'||x_msg_count,1,'N');
1079 END IF;
1080
1081 IF x_return_status = FND_API.G_RET_STS_ERROR then
1082 raise FND_API.G_EXC_ERROR;
1083 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1084 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1085 END IF;
1086
1087 /**************************************************************
1088 Find the quote line containing the top model
1089 ***************************************************************/
1090 Open c_top_mdl_csr(c_deactivate_item_rec.config_header_id,c_deactivate_item_rec.config_revision_num);
1091
1092 fetch c_top_mdl_csr into l_config_item_id;
1093
1094 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1095 aso_debug_pub.add('Deactivate_from_quote:Config Valid Info:'||l_complete_configuration_flag,1,'N');
1096 aso_debug_pub.add('Deactivate_from_quote:Config Complete Info:'||l_valid_configuration_flag,1,'N');
1097 END IF;
1098
1099 IF c_top_mdl_csr%FOUND THEN
1100 l_copy_conf_mdl_Tbl(i).config_item_id := l_config_item_id;
1101 l_copy_conf_mdl_Tbl(i).config_header_id := c_deactivate_item_rec.config_header_id;
1102 l_copy_conf_mdl_Tbl(i).config_revision_num := c_deactivate_item_rec.config_revision_num;
1103 END IF;
1104
1105 Close c_top_mdl_csr;
1106
1107 END LOOP;
1108
1109 End if;
1110
1111 END LOOP;
1112
1113
1114
1115 If (l_copy_conf_mdl_Tbl.count > 0) then
1116
1117 For j IN 1..l_copy_conf_mdl_Tbl.count Loop
1118
1119 k := 1;
1120 x := FALSE;
1121 While (x = FALSE AND k <= l_deactivate_mdl_Tbl.count) Loop
1122
1123 if ((l_copy_conf_mdl_Tbl(j).config_item_id) =(l_deactivate_mdl_Tbl(k).config_item_id)) AND
1124 ((l_copy_conf_mdl_Tbl(j).config_header_id) =(l_deactivate_mdl_Tbl(k).config_header_id) ) AND
1125 ((l_copy_conf_mdl_Tbl(j).config_revision_num) = (l_deactivate_mdl_Tbl(k).config_revision_num))
1126 then
1127 x := TRUE;
1128 END IF;
1129
1130 k := k + 1;
1131 END LOOP;
1132
1133 If x = FALSE then
1134 l_count := l_count + 1;
1135
1136 /**************************************************************
1137 Find the quote line details containing the top model
1138 ***************************************************************/
1139
1140 Open c_top_mdl_details_csr(l_copy_conf_mdl_Tbl(j).config_header_id,l_copy_conf_mdl_Tbl(j).config_revision_num,l_copy_conf_mdl_Tbl(j).config_item_id);
1141
1142 fetch c_top_mdl_details_csr into l_quote_line_id,l_quantity, l_uom_code,l_complete_configuration_flag,
1143 l_valid_configuration_flag;
1144
1145 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1146 aso_debug_pub.add('Deactivate_from_quote:Config Valid Info:'||l_complete_configuration_flag,1,'N');
1147 aso_debug_pub.add('Deactivate_from_quote:Config Complete Info:'||l_valid_configuration_flag,1,'N');
1148 END IF;
1149
1150 IF c_top_mdl_details_csr%NOTFOUND THEN
1151 null;-- No Action
1152 END IF;
1153
1154 Close c_top_mdl_details_csr;
1155
1156 /**************************************************************
1157 Copy config data from CZ_CONFIG_DETAILS_V
1158 **************************************************************/
1159 l_config_rec.quote_line_id := l_quote_line_id;
1160 l_config_rec.complete_configuration_flag := l_complete_configuration_flag;
1161 l_config_rec.valid_configuration_flag := l_valid_configuration_flag;
1162 l_config_rec.config_header_id := l_copy_conf_mdl_Tbl(j).config_header_id;
1163 l_config_rec.config_revision_num := l_copy_conf_mdl_Tbl(j).config_revision_num;
1164
1165 l_model_line_rec.quote_line_id := l_quote_line_id;
1166 l_model_line_rec.quantity := l_quantity;
1167 l_model_line_rec.uom_code := l_uom_code;
1168
1169 Open c_last_update_date_csr(P_QTE_HEADER_REC.quote_header_id);
1170 fetch c_last_update_date_csr into l_last_update_date,l_obj_version_number;
1171
1172 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1173 aso_debug_pub.add('Deactivate_from_quote:Last Update date:'||l_last_update_date,1,'N');
1174 aso_debug_pub.add('Deactivate_from_quote:Object Version Number:'||l_obj_version_number,1,'N');
1175 END IF;
1176
1177 IF c_last_update_date_csr%NOTFOUND THEN
1178 CLOSE c_last_update_date_csr;
1179 x_return_status := FND_API.G_RET_STS_ERROR;
1180 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1181 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_COLUMN');
1182 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
1183 FND_MSG_PUB.ADD;
1184 END IF;
1185 raise FND_API.G_EXC_ERROR;
1186
1187 END IF;
1188
1189 Close c_last_update_date_csr;
1190
1191 l_qte_header_rec := p_qte_header_rec;
1192
1193 l_QTE_HEADER_REC.last_update_date := l_last_update_date;
1194 l_QTE_HEADER_REC.object_version_number := l_obj_version_number;
1195
1196 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1197 aso_debug_pub.add('Deactivate_from_quote:Before calling get_config_details ', 1, 'N');
1198 aso_debug_pub.add('Deactivate_from_quote:l_config_rec.config_header_id: '||l_config_rec.config_header_id, 1, 'N');
1199 aso_debug_pub.add('Deactivate_from_quote:l_config_rec.config_revision_num: '||l_config_rec.config_revision_num, 1, 'N');
1200 aso_debug_pub.add('Deactivate_from_quote:l_copy_conf_mdl_Tbl.config_header_id: '||l_copy_conf_mdl_Tbl(j).config_header_id, 1, 'N');
1201 aso_debug_pub.add('Deactivate_from_quote:l_copy_conf_mdl_Tbl.config_revision_num: '||l_copy_conf_mdl_Tbl(j).config_revision_num, 1, 'N');
1202 END IF;
1203
1204
1205
1206 ASO_CFG_PUB.Get_config_details(
1207 P_Api_Version_Number => P_Api_Version_Number,
1208 P_Init_Msg_List => FND_API.G_FALSE,
1209 p_commit => FND_API.G_FALSE,
1210 p_config_rec => l_config_rec,
1211 p_control_rec => p_control_rec,
1212 p_model_line_rec => l_model_line_rec,
1213 p_config_hdr_id => l_copy_conf_mdl_Tbl(j).config_header_id,
1214 p_config_rev_nbr => l_copy_conf_mdl_Tbl(j).config_revision_num,
1215 p_qte_header_rec => l_qte_header_rec,
1216 x_return_status => x_return_status ,
1217 x_msg_count => x_msg_count ,
1218 x_msg_data => x_msg_data
1219 );
1220
1221 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1222 aso_debug_pub.add('Deactivate_from_quote:Get_config_details:x_return_status'||x_return_status, 1, 'N');
1223 aso_debug_pub.add('Deactivate_from_quote:Get_config_details:x_return_status'||x_msg_count, 1, 'N');
1224 END IF;
1225
1226 IF x_return_status = FND_API.G_RET_STS_ERROR then
1227 raise FND_API.G_EXC_ERROR;
1228 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1229 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1230 END IF;
1231
1232 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1233 aso_debug_pub.add('Deactivate_from_quote:Write to l_deactivate_mdl_Tbl', 1, 'N');
1234 END IF;
1235
1236 l_deactivate_mdl_Tbl(l_count).config_item_id := l_copy_conf_mdl_Tbl(j).config_item_id;
1237 l_deactivate_mdl_Tbl(l_count).config_header_id := l_copy_conf_mdl_Tbl(j).config_header_id;
1238 l_deactivate_mdl_Tbl(l_count).config_revision_num := l_copy_conf_mdl_Tbl(j).config_revision_num;
1239
1240 End if;
1241
1242 END LOOP;
1243 end if;
1244
1245 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1246 aso_debug_pub.add('Deactivate_from_quote:Query Quote Header Rec', 1, 'N');
1247 END IF;
1248
1249 x_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(P_QTE_HEADER_REC.quote_header_id);
1250
1251 -- End of API body
1252
1253 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1254 aso_debug_pub.add('****** End of Deactivate from quote API ******', 1, 'Y');
1255 END IF;
1256
1257 -- Standard check of p_commit
1258 IF FND_API.To_Boolean(p_commit) THEN
1259 COMMIT WORK;
1260 END IF;
1261
1262 -- Standard call to get message count and if count is 1, get message info
1263 FND_Msg_Pub.Count_And_Get(
1264 p_encoded => FND_API.G_FALSE,
1265 p_count => x_msg_count,
1266 p_data => x_msg_data
1267 );
1268
1269 EXCEPTION
1270 WHEN FND_API.G_EXC_ERROR THEN
1271 x_return_status := FND_API.G_RET_STS_ERROR;
1272 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1273 P_API_NAME => L_API_NAME
1274 ,P_PKG_NAME => G_PKG_NAME
1275 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1276 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1277 ,X_MSG_COUNT => X_MSG_COUNT
1278 ,X_MSG_DATA => X_MSG_DATA
1279 ,X_RETURN_STATUS => X_RETURN_STATUS);
1280
1281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1283 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1284 P_API_NAME => L_API_NAME
1285 ,P_PKG_NAME => G_PKG_NAME
1286 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1287 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1288 ,X_MSG_COUNT => X_MSG_COUNT
1289 ,X_MSG_DATA => X_MSG_DATA
1290 ,X_RETURN_STATUS => X_RETURN_STATUS);
1291
1292 WHEN OTHERS THEN
1293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1294 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1295 P_API_NAME => L_API_NAME
1296 ,P_PKG_NAME => G_PKG_NAME
1297 ,P_SQLERRM => sqlerrm
1298 ,P_SQLCODE => sqlcode
1299 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1300 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1301 ,X_MSG_COUNT => X_MSG_COUNT
1302 ,X_MSG_DATA => X_MSG_DATA
1303 ,X_RETURN_STATUS => X_RETURN_STATUS);
1304
1305 END Deactivate_from_quote;
1306
1307 END ASO_CONFIG_OPERATIONS_PVT;
1308