[Home] [Help]
Skip to content
PACKAGE BODY: APPS.ASO_CFG_INT
Source
1 PACKAGE BODY aso_cfg_int as
2 /* $Header: asoicfgb.pls 120.11 2011/03/01 13:12:54 rassharm ship $ */
3 -- Start of Comments
4 -- Package name : aso_cfg_int
5 -- Purpose :
6 -- History :
7 -- NOTE : 8/21/04 skulkarn: added the MACD changes into the get_config_details API
8 -- 9/16/04 bmishra: Made changes in pricing_callback and query_qte_line_rows. Fix for bug#3850782
9 -- 9/17/04 skulkarn: fixed bug 3883545
10 -- 11/23/04 skulkarn: fixed bug 3998564
11 -- 12/06/04 skulkarn: fixed bug3938943
12 -- End of Comments
13 --private variable declaration
14
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_CFG_INT';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoicfgb.pls';
17
18 Procedure Populate_output_table(
19 p_oe_line_tbl IN OE_ORDER_PUB.line_tbl_type ,
20 x_qte_line_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.qte_line_tbl_type,
21 x_qte_line_dtl_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.qte_line_dtl_tbl_type,
22 x_shipment_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.shipment_tbl_type) AS
23 Begin
24 If p_oe_line_tbl.count <= 0 Then
25 Return;
26 End If;
27
28 For i In p_oe_line_tbl.FIRST .. p_oe_line_tbl.LAST Loop
29
30 x_qte_line_tbl(i).inventory_item_id :=
31 p_oe_line_tbl(i).inventory_item_id ;
32
33 x_qte_line_dtl_tbl(i).component_code :=
34 p_oe_line_tbl(i).component_code ;
35 x_qte_line_dtl_tbl(i).config_header_id :=
36 p_oe_line_tbl(i).config_header_id ;
37 x_qte_line_dtl_tbl(i).config_revision_num :=
38 p_oe_line_tbl(i).config_rev_nbr ;
39 x_shipment_tbl(i).shipment_id :=
40 p_oe_line_tbl(i).source_document_line_id ;
41
42 End Loop ;
43 End Populate_output_table;
44
45 PROCEDURE Get_configuration_lines(
46 P_Api_Version_Number IN NUMBER,
47 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
48 p_top_model_line_id IN NUMBER,
49 x_qte_line_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.qte_line_tbl_type,
50 x_qte_line_dtl_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.qte_line_dtl_tbl_type,
51 x_shipment_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.shipment_tbl_type ,
52 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
53 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
54 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
55 AS
56 l_oe_line_tbl OE_ORDER_PUB.Line_Tbl_Type ;
57 l_api_name VARCHAR2(30) := 'Get_Configuration_Lines' ;
58 l_api_version_number Number := 1.0 ;
59 BEGIN
60 -- Standard Start of API savepoint
61 SAVEPOINT GET_CONFIGURATION_LINES_PUB;
62
63 OE_ORDER_GRP.Get_Option_Lines(
64 p_api_version_number => l_api_version_number ,
65 p_init_msg_list => FND_API.G_FALSE ,
66 p_top_model_line_id => p_top_model_line_id ,
67 x_line_tbl => l_oe_line_tbl ,
68 x_return_status => x_return_status ,
69 x_msg_count => x_msg_count ,
70 x_msg_data => x_msg_data ) ;
71
72 If x_return_status <> FND_API.G_RET_STS_SUCCESS Then
73 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
74 End if;
75
76 Populate_output_table(p_oe_line_tbl => l_oe_line_tbl ,
77 x_qte_line_tbl => x_qte_line_tbl,
78 x_qte_line_dtl_tbl => x_qte_line_dtl_tbl ,
79 x_shipment_tbl => x_shipment_tbl );
80
81 EXCEPTION
82 WHEN FND_API.G_EXC_ERROR THEN
83 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
84 P_API_NAME => L_API_NAME
85 ,P_PKG_NAME => G_PKG_NAME
86 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
87 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
88 ,X_MSG_COUNT => X_MSG_COUNT
89 ,X_MSG_DATA => X_MSG_DATA
90 ,X_RETURN_STATUS => X_RETURN_STATUS);
91
92 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
93 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
94 P_API_NAME => L_API_NAME
95 ,P_PKG_NAME => G_PKG_NAME
96 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
97 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
98 ,X_MSG_COUNT => X_MSG_COUNT
99 ,X_MSG_DATA => X_MSG_DATA
100 ,X_RETURN_STATUS => X_RETURN_STATUS);
101
102 WHEN OTHERS THEN
103 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
104 P_API_NAME => L_API_NAME
105 ,P_PKG_NAME => G_PKG_NAME
106 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
107 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
108 ,X_MSG_COUNT => X_MSG_COUNT
109 ,X_MSG_DATA => X_MSG_DATA
110 ,X_RETURN_STATUS => X_RETURN_STATUS);
111
112
113 END Get_Configuration_Lines;
114
115 PROCEDURE Delete_configuration(
116 P_Api_version_NUmber IN NUMBER,
117 P_Init_msg_List IN VARCHAR2 := FND_API.G_FALSE,
118 P_config_hdr_id IN NUMBER,
119 p_config_rev_nbr IN NUMBER,
123 IS
120 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
121 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
122 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
124 l_usage_exists NUMBER;
125 l_Error_message VARCHAR2(2000);
126 l_Return_value NUMBER;
127 BEGIN
128
129 x_return_status := FND_API.G_RET_STS_SUCCESS;
130
131 cz_cf_api.delete_configuration(P_config_hdr_id,
132 p_config_rev_nbr,
133 l_usage_exists ,
134 l_Error_message ,
135 l_Return_value );
136
137 IF l_Return_value = 0 Then
138 x_return_status := FND_API.G_RET_STS_ERROR;
139 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
140 FND_MESSAGE.Set_Name('ASO', 'ASO_CZ_DELETE_ERR');
141 FND_MESSAGE.Set_token('MSG_TXT' , l_error_message,FALSE);
142 FND_MSG_PUB.ADD;
143 END IF;
144 END IF;
145
146 END Delete_configuration;
147
148
149 PROCEDURE Delete_configuration_auto(
150 P_Api_version_NUmber IN NUMBER,
151 P_Init_msg_List IN VARCHAR2 := FND_API.G_FALSE,
152 P_config_hdr_id IN NUMBER,
153 p_config_rev_nbr IN NUMBER,
154 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
155 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
156 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
157 IS
158
159 PRAGMA AUTONOMOUS_TRANSACTION;
160
161 BEGIN
162 x_return_status := FND_API.G_RET_STS_SUCCESS;
163
164 DELETE_CONFIGURATION(
165 P_API_VERSION_NUMBER => 1.0,
166 P_INIT_MSG_LIST => FND_API.G_FALSE,
167 P_CONFIG_HDR_ID => P_config_hdr_id,
168 P_CONFIG_REV_NBR => p_config_rev_nbr,
169 X_RETURN_STATUS => x_return_status,
170 X_MSG_COUNT => x_msg_count,
171 X_MSG_DATA => x_msg_data);
172
173 IF x_return_status = FND_API.G_RET_STS_SUCCESS Then
174 commit;
175 ELSE
176 rollback;
177 END IF;
178
179 END Delete_configuration_auto;
180
181 Procedure Copy_Configuration( p_api_version_number IN NUMBER,
182 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
183 p_commit IN VARCHAR2 := FND_API.G_FALSE,
184 p_config_header_id IN NUMBER,
185 p_config_revision_num IN NUMBER,
186 p_copy_mode IN VARCHAR2,
187 p_handle_deleted_flag IN VARCHAR2 := NULL,
188 p_new_name IN VARCHAR2 := NULL,
189 p_autonomous_flag IN VARCHAR2 := FND_API.G_FALSE,
190 x_config_header_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
191 x_config_revision_num OUT NOCOPY /* file.sql.39 change */ NUMBER,
192 x_orig_item_id_tbl OUT NOCOPY CZ_API_PUB.number_tbl_type,
193 x_new_item_id_tbl OUT NOCOPY CZ_API_PUB.number_tbl_type,
194 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
195 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
196 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
197 )
198 IS
199
200 l_api_name CONSTANT VARCHAR2(30) := 'COPY_CONFIGURATION';
201 l_api_version_number CONSTANT NUMBER := 1.0;
202 l_config_rev_nbr NUMBER;
203
204 -- ER 3177722
205 l_autonomous_flag VARCHAR2(1);
206 l_copy_config_profile varchar2(1):=nvl(fnd_profile.value('ASO_COPY_CONFIG_EFF_DATE'),'Y');
207
208 cursor c_config_rev_nbr is
209 select config_rev_nbr
210 from cz_config_details_v
211 where config_hdr_id = p_config_header_id
212 and config_rev_nbr = p_config_revision_num;
213
214
215 cursor c_config_max_rev_nbr is select max(config_rev_nbr)
216 from cz_config_details_v
217 where config_hdr_id = p_config_header_id;
218
219
220 BEGIN
221 SAVEPOINT COPY_CONFIGURATION_INT;
222
223 IF aso_debug_pub.g_debug_flag = 'Y' THEN
224 aso_debug_pub.add('ASO_CFG_INT: Begin Copy_Configuration');
225 END IF;
226
227 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
228 p_api_version_number,
229 l_api_name,
230 G_PKG_NAME) THEN
231
232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
233
234
235 END IF;
236
237 IF aso_debug_pub.g_debug_flag = 'Y' THEN
238 aso_debug_pub.add('copy_configuration: p_init_msg_list: '|| p_init_msg_list);
239 END IF;
240
241 IF FND_API.to_Boolean( p_init_msg_list ) THEN
242 FND_MSG_PUB.initialize;
243 END IF;
244
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246
247 IF aso_debug_pub.g_debug_flag = 'Y' THEN
248
249 aso_debug_pub.add('copy_configuration: p_config_header_id: '|| p_config_header_id);
250 aso_debug_pub.add('copy_configuration: p_config_revision_num: '|| p_config_revision_num);
251 aso_debug_pub.add('copy_configuration: p_copy_mode: '|| p_copy_mode);
255
252 aso_debug_pub.add('copy_configuration: p_autonomous_flag: '|| p_autonomous_flag);
253
254 END IF;
256
257 open c_config_rev_nbr;
258 fetch c_config_rev_nbr into l_config_rev_nbr;
259
260 IF aso_debug_pub.g_debug_flag = 'Y' THEN
261 aso_debug_pub.add('After cursor c_config_rev_nbr l_config_rev_nbr: '||l_config_rev_nbr);
262 END IF;
263
264 IF c_config_rev_nbr%NOTFOUND THEN
265
266 open c_config_max_rev_nbr;
267 fetch c_config_max_rev_nbr into l_config_rev_nbr;
268
269 IF aso_debug_pub.g_debug_flag = 'Y' THEN
270 aso_debug_pub.add('After cursor c_config_max_rev_nbr l_config_rev_nbr: '||l_config_rev_nbr);
271 END IF;
272
273 close c_config_max_rev_nbr;
274
275 END IF;
276 close c_config_rev_nbr;
277
278 IF aso_debug_pub.g_debug_flag = 'Y' THEN
279 aso_debug_pub.add('Before call to cz_config_api_pub.copy_configuration');
280 END IF;
281 -- ER 3177722
282 l_autonomous_flag := p_autonomous_flag;
283 if l_copy_config_profile='N' then
284 l_autonomous_flag:=fnd_api.g_true;
285 end if;
286 IF l_autonomous_flag = fnd_api.g_true THEN
287
288 cz_config_api_pub.copy_configuration_auto( p_api_version => 1.0,
289 p_config_hdr_id => p_config_header_id,
290 p_config_rev_nbr => l_config_rev_nbr,
291 p_copy_mode => p_copy_mode,
292 p_handle_deleted_flag => p_handle_deleted_flag,
293 p_new_name => p_new_name,
294 x_config_hdr_id => x_config_header_id,
295 x_config_rev_nbr => x_config_revision_num,
296 x_orig_item_id_tbl => x_orig_item_id_tbl,
297 x_new_item_id_tbl => x_new_item_id_tbl,
298 x_return_status => x_return_status,
299 x_msg_count => x_msg_count,
300 x_msg_data => x_msg_data
301 );
302
303 IF aso_debug_pub.g_debug_flag = 'Y' THEN
304 aso_debug_pub.add('After call to cz_config_api_pub.copy_configuration_auto');
305 aso_debug_pub.add('copy_configuration: x_return_status: '|| x_return_status);
306 END IF;
307
308 ELSE
309
310 cz_config_api_pub.copy_configuration( p_api_version => 1.0,
311 p_config_hdr_id => p_config_header_id,
312 p_config_rev_nbr => l_config_rev_nbr,
313 p_copy_mode => p_copy_mode,
314 p_handle_deleted_flag => p_handle_deleted_flag,
315 p_new_name => p_new_name,
316 x_config_hdr_id => x_config_header_id,
317 x_config_rev_nbr => x_config_revision_num,
318 x_orig_item_id_tbl => x_orig_item_id_tbl,
319 x_new_item_id_tbl => x_new_item_id_tbl,
320 x_return_status => x_return_status,
321 x_msg_count => x_msg_count,
322 x_msg_data => x_msg_data
323 );
324
325 IF aso_debug_pub.g_debug_flag = 'Y' THEN
326 aso_debug_pub.add('After call to cz_config_api_pub.copy_configuration');
327 aso_debug_pub.add('copy_configuration: x_return_status: '|| x_return_status);
328 END IF;
329
330 END IF;
331
332 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
333
334
335 RAISE FND_API.G_EXC_ERROR;
336
337 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
338
339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
340
341 END IF;
342
343 IF FND_API.to_Boolean( p_commit ) THEN
344 COMMIT WORK;
345 END IF;
346
347
348 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
349 p_data => x_msg_data );
350
351
352 EXCEPTION
353
354 WHEN FND_API.G_EXC_ERROR THEN
355
356 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS( P_API_NAME => L_API_NAME,
357 P_PKG_NAME => G_PKG_NAME,
358 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
359 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT,
360 X_MSG_COUNT => X_MSG_COUNT,
361 X_MSG_DATA => X_MSG_DATA,
362 X_RETURN_STATUS => X_RETURN_STATUS);
363
364 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
365
366 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS( P_API_NAME => L_API_NAME,
367 P_PKG_NAME => G_PKG_NAME,
371 X_MSG_DATA => X_MSG_DATA,
368 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
369 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT,
370 X_MSG_COUNT => X_MSG_COUNT,
372 X_RETURN_STATUS => X_RETURN_STATUS);
373
374
375 WHEN OTHERS THEN
376
377 IF aso_debug_pub.g_debug_flag = 'Y' THEN
378 aso_debug_pub.add('ASO_CFG_INT: copy_configuration: Inside when others exception');
379 END IF;
380
381 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS( P_API_NAME => L_API_NAME,
382 P_PKG_NAME => G_PKG_NAME,
383 P_SQLERRM => SQLERRM,
384 P_SQLCODE => SQLCODE,
385 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
386 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT,
387 X_MSG_COUNT => X_MSG_COUNT,
388 X_MSG_DATA => X_MSG_DATA,
389 X_RETURN_STATUS => X_RETURN_STATUS);
390
391
392 END Copy_Configuration;
393
394
395
396 PROCEDURE Update_revision_num(
397 p_quote_header_id IN NUMBER ,
398 p_config_hdr_id IN NUMBER ,
399 p_config_rev_nbr IN NUMBER ,
400 p_to_config_hdr_id IN NUMBER ,
401 p_to_config_rev_nbr IN NUMBER ,
402 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
403 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER ,
404 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ) IS
405 Cursor c_update_revision IS
406 SELECT quote_line_id,
407 quote_line_Detail_id
408 From aso_quote_line_details
409 Where config_header_id = p_config_hdr_id
410 AND config_revision_num = p_config_rev_nbr ;
411
412 l_Api_Version_Number NUMBER := 1.0 ;
413 l_Qte_Line_Rec ASO_QUOTE_PUB.Qte_Line_Rec_Type ;
414 l_miss_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type ;
415 l_Qte_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type ;
416 l_miss_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type ;
417 X_Qte_Line_Rec ASO_QUOTE_PUB.Qte_Line_Rec_Type ;
418 X_Qte_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type ;
419 X_payment_tbl ASO_QUOTE_PUB.Payment_tbl_type ;
420 X_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type ;
421 X_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
422 X_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
423 X_price_attributes_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
424 X_price_adj_attr_tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
425 X_line_attribs_ext_tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_type;
426 X_price_adj_tbl ASO_QUOTE_PUB.Price_adj_tbl_type ;
427 X_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type ;
428 X_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type ;
429 Begin
430
431 For i_update_revision IN c_update_revision Loop
432 l_qte_line_rec := l_miss_line_rec ;
433 l_qte_line_dtl_tbl := l_miss_line_dtl_tbl ;
434
435 l_qte_line_rec.quote_line_id := i_update_revision.quote_line_id ;
436 l_qte_line_rec.quote_header_id := p_quote_header_id ;
437 l_qte_line_dtl_tbl(1).operation_code := 'UPDATE';
438 l_qte_line_dtl_tbl(1).quote_line_id := i_update_revision.quote_line_id;
439 l_qte_line_dtl_tbl(1).quote_line_detail_id :=
440 i_update_revision.quote_line_detail_id;
441 l_qte_line_dtl_tbl(1).config_revision_num := p_to_config_rev_nbr ;
442
443 ASO_QUOTE_LINES_PVT.Update_Quote_Line(
444 P_Api_Version_Number => l_api_version_number ,
445 P_Init_Msg_List => FND_API.G_FALSE,
446 P_Commit => FND_API.G_FALSE,
447 P_Validation_Level => FND_API.G_VALID_LEVEL_NONE ,
448 P_Qte_Line_Rec => l_qte_line_REC,
449 P_Qte_Line_Dtl_Tbl => l_qte_line_dtl_TBL,
450 P_Update_Header_Flag => FND_API.G_FALSE ,
451 X_Qte_Line_Rec => x_Qte_Line_Rec,
452 X_payment_tbl => x_payment_tbl,
453 X_price_adj_tbl => x_price_adj_tbl ,
454 X_Qte_Line_Dtl_Tbl => x_Qte_Line_Dtl_Tbl ,
455 X_shipment_tbl => x_shipment_tbl ,
456 X_tax_detail_tbl => x_tax_detail_tbl ,
457 X_freight_charge_tbl => x_freight_charge_tbl ,
458 X_price_attributes_tbl => x_price_attributes_tbl ,
459 X_price_adj_attr_tbl => x_price_adj_attr_tbl ,
460 X_line_attribs_ext_tbl => x_line_attribs_ext_tbl ,
461 X_Sales_Credit_Tbl => x_sales_credit_tbl ,
462 X_Quote_Party_Tbl => x_quote_party_tbl ,
463 X_Return_Status => x_return_status ,
464 X_Msg_Count => x_msg_count,
465 X_Msg_Data => x_msg_data );
466
467 --check for success
468 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
470 END IF;
471 End Loop;
472
473 End Update_Revision_Num ;
474
475 PROCEDURE Create_Relationship(parent_quote_line_id IN NUMBER ,
476 p_config_item_id IN NUMBER ,
477 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
478 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
482 ASO_QUOTE_PUB.G_MISS_LINE_RLTSHIP_REC ;
479 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ) IS
480
481 l_LINE_RLTSHIP_Rec ASO_quote_PUB.LINE_RLTSHIP_Rec_Type :=
483 l_api_name Constant Varchar2(30) := 'Create_Relationship' ;
484 l_api_version_number NUMBER := 1.0 ;
485 l_line_relationship_id NUMBER ;
486 l_dummy_line_id NUMBER;
487 l_return_status varchar2(1);
488
489 CURSOR c_rel_exist( p_quote_line_id NUMBER ) is
490 select related_quote_line_id
491 from aso_line_relationships
492 where related_quote_line_id = p_quote_line_id;
493
494
495 BEGIN
496 l_return_status := FND_API.G_RET_STS_SUCCESS;
497 If G_rtln_tbl.First IS NULL Then
498 return;
499 end if;
500
501 FOR i IN G_rtln_tbl.FIRST .. G_rtln_tbl.LAST LOOP
502 If G_rtln_tbl(i).parent_config_item_id = p_config_item_id
503 AND G_rtln_tbl(i).included_flag = 'N'
504 AND G_rtln_tbl(i).parent_config_item_id IS NOT NULL Then
505
506 l_line_rltship_rec := aso_quote_pub.G_MISS_Line_rltship_rec ;
507
508 --populate line relationship record
509 l_line_rltship_rec.OPERATION_CODE := 'CREATE' ;
510 l_line_rltship_rec.QUOTE_LINE_ID := parent_quote_line_id ;
511 l_line_rltship_rec.RELATED_QUOTE_LINE_ID := G_rtln_tbl(i).quote_line_id ;
512 l_line_rltship_rec.RELATIONSHIP_TYPE_CODE := 'CONFIG' ;
513
514
515 If G_rtln_tbl(i).created_flag = 'N' Then
516 ASO_LINE_RLTSHIP_PVT.Create_line_rltship(
517 P_Api_Version_Number => l_api_version_number ,
518 P_Init_Msg_List => FND_API.G_FALSE,
519 P_Commit => FND_API.G_FALSE,
520 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
521 P_LINE_RLTSHIP_Rec => l_line_rltship_rec ,
522 X_LINE_RELATIONSHIP_ID => l_line_relationship_id ,
523 X_Return_Status => x_return_status ,
524 X_Msg_Count => x_msg_count,
525 X_Msg_Data => x_msg_data );
526
527 l_return_status := x_return_status;
528
529 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
530 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
531 END IF;
532 End If;
533
534 G_rtln_tbl(i).included_flag := 'Y';
535
536 Create_relationship(parent_quote_line_id => G_rtln_tbl(i).quote_line_id ,
537 p_config_item_id => G_rtln_tbl(i).config_item_id,
538 x_return_status => x_return_status ,
539 x_msg_count => x_msg_count ,
540 x_msg_data => x_msg_data ) ;
541 End If;
542 END LOOP;
543
544 x_return_status := l_return_status;
545 END create_relationship ;
546
547
548 Procedure Populate_Rtln_Tbl(p_quote_header_id IN NUMBER ,
549 p_quote_line_id IN NUMBER ,
550 p_config_hdr_id IN NUMBER ,
551 p_config_rev_nbr IN NUMBER ) IS
552
553 CURSOR c_options ( l_config_hdr_id NUMBER ,
554 l_config_rev_nbr NUMBER ) IS
555 SELECT qte_dtl.quote_line_id ,
556 cfg_dtl.parent_config_item_id ,
557 cfg_dtl.config_item_id ,
558 cfg_dtl.inventory_item_id ,
559 cfg_dtl.organization_id ,
560 cfg_dtl.component_code ,
561 cfg_dtl.quantity ,
562 cfg_dtl.uom_code -- 6661597
563 FROM cz_config_details_v cfg_dtl ,
564 aso_quote_line_details qte_dtl
565 WHERE cfg_dtl.config_hdr_id = l_config_hdr_id
566 AND cfg_dtl.config_rev_nbr = l_config_rev_nbr
567 AND cfg_dtl.config_hdr_id = qte_dtl.config_header_id
568 AND cfg_dtl.config_rev_nbr = qte_dtl.config_revision_num
569 AND qte_dtl.config_item_id = cfg_dtl.config_item_id ;
570
571
572 CURSOR c_model ( l_model_line_id NUMBER ,
573 l_config_hdr_id NUMBER ,
574 l_config_rev_nbr NUMBER ) IS
575 SELECT qte_dtl.quote_line_id ,
576 cfg_dtl.parent_config_item_id ,
577 cfg_dtl.config_item_id ,
578 cfg_dtl.inventory_item_id ,
579 cfg_dtl.organization_id ,
580 cfg_dtl.component_code ,
581 cfg_dtl.quantity ,
582 cfg_dtl.uom_code -- 6661597
583 FROM cz_config_details_v cfg_dtl ,
584 aso_quote_line_details qte_dtl
585 WHERE qte_dtl.quote_line_id = l_model_line_id
586 AND cfg_dtl.config_hdr_id = l_config_hdr_id
587 AND cfg_dtl.config_rev_nbr = l_config_rev_nbr
588 AND cfg_dtl.config_hdr_id = qte_dtl.config_header_id
589 AND cfg_dtl.config_rev_nbr = qte_dtl.config_revision_num
590 AND qte_dtl.config_item_id = cfg_dtl.config_item_id;
591
592 l_rec_options c_options%ROWTYPE;
593 l_rec_model c_model%ROWTYPE;
594 l_index BINARY_INTEGER;
595 l_dummy_line_id NUMBER;
596
597 -- added by 6661597
598 l_validated_quantity NUMBER;
599 l_primary_quantity NUMBER;
600 l_qty_return_status VARCHAR2(1);
601 p_item_id NUMBER;
602 p_organization_id NUMBER;
603 p_uom_code VARCHAR2(50);
604 p_input_quantity NUMBER;
605 x_output_quantity NUMBER;
606 x_ret_Stat VARCHAR2(50);
607 -- end 6661597
608
609
610 BEGIN
611 IF G_rtln_tbl.EXISTS(1) Then
612
613 -- This is the first time Model is configured, hence all the options
614 -- are in G_RTLN_TBL. No need to populate.
615 RETURN ;
616 END IF;
617
621 G_rtln_tbl(1).quote_line_id := l_rec_model.quote_line_id;
618 FOR l_rec_model IN c_model (p_quote_line_id,p_config_hdr_id,p_config_rev_nbr)
619 LOOP
620 -- Assumption is configured items will have only one detail per quote line
622 G_rtln_tbl(1).parent_config_item_id := NULL;
623 G_rtln_tbl(1).config_item_id := l_rec_model.config_item_id;
624 G_rtln_tbl(1).inventory_item_id := l_rec_model.inventory_item_id;
625 G_rtln_tbl(1).organization_id := l_rec_model.organization_id;
626 G_rtln_tbl(1).component_code := l_rec_model.component_code;
627 -- 6661597
628 p_item_id:=l_rec_model.inventory_item_id;
629 p_organization_id:=l_rec_model.organization_id;
630 p_uom_code:=l_rec_model.uom_code;
631 p_input_quantity:=l_rec_model.quantity;
632 x_output_quantity:=FND_API.G_MISS_NUM;
633
634 -- inv quantity validation 6661597
635 IF (p_input_quantity is not null AND p_input_quantity <> FND_API.G_MISS_NUM) THEN
636 inv_decimals_pub.validate_quantity(
637 p_item_id => p_item_id ,
638 p_organization_id => p_organization_id ,
639 p_input_quantity => p_input_quantity,
640 p_uom_code => p_uom_code,
641 x_output_quantity => l_validated_quantity,
642 x_primary_quantity => l_primary_quantity,
643 x_return_status => x_ret_Stat);
644
645 if x_ret_Stat = 'E' THEN
646 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
647 FND_MESSAGE.Set_Name('ASO', 'ASO_ERR_FRACTIONAL_QUANTITY');
648 FND_MSG_PUB.ADD;
649 END IF;
650 RAISE FND_API.G_EXC_ERROR;
651 elsif x_ret_Stat = 'W' then
652 x_output_quantity:= l_validated_quantity;
653 else
654 x_output_quantity:=p_input_quantity;
655 end if;
656 END IF; -- quantity not null
657
658 -- end quantity validation changes 6661597
659
660
661 G_rtln_tbl(1).quantity := x_output_quantity; -- l_rec_model.quantity; 6661597
662 --G_rtln_tbl(1).quantity := l_rec_model.quantity;
663 G_rtln_tbl(1).included_flag := 'N';
664 G_rtln_tbl(1).created_flag := 'Y';
665
666 END LOOP ;
667
668 l_index := G_rtln_tbl.LAST + 1 ;
669
670 FOR l_rec_options IN c_options (p_config_hdr_id,p_config_rev_nbr)
671 LOOP
672
673 G_rtln_tbl(l_index).quote_line_id := l_rec_options.quote_line_id;
674 G_rtln_tbl(l_index).parent_config_item_id := l_rec_options.parent_config_item_id;
675 G_rtln_tbl(l_index).config_item_id := l_rec_options.config_item_id;
676 G_rtln_tbl(l_index).inventory_item_id := l_rec_options.inventory_item_id;
677 G_rtln_tbl(l_index).organization_id := l_rec_options.organization_id;
678 G_rtln_tbl(l_index).component_code := l_rec_options.component_code;
679 -- 6661597
680 p_item_id:=l_rec_options.inventory_item_id;
681 p_organization_id:=l_rec_options.organization_id;
682 p_uom_code:=l_rec_options.uom_code;
683 p_input_quantity:=l_rec_options.quantity;
684 x_output_quantity:=FND_API.G_MISS_NUM;
685
686 -- inv quantity validation 6661597
687 IF (p_input_quantity is not null AND p_input_quantity <> FND_API.G_MISS_NUM) THEN
688 inv_decimals_pub.validate_quantity(
689 p_item_id => p_item_id ,
690 p_organization_id => p_organization_id ,
691 p_input_quantity => p_input_quantity,
692 p_uom_code => p_uom_code,
693 x_output_quantity => l_validated_quantity,
694 x_primary_quantity => l_primary_quantity,
695 x_return_status => x_ret_Stat);
696
697 if x_ret_Stat = 'E' THEN
698 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
699 FND_MESSAGE.Set_Name('ASO', 'ASO_ERR_FRACTIONAL_QUANTITY');
700 FND_MSG_PUB.ADD;
701 END IF;
702 RAISE FND_API.G_EXC_ERROR;
703 elsif x_ret_Stat = 'W' then
704 x_output_quantity:= l_validated_quantity;
705 else
706 x_output_quantity:=p_input_quantity;
707 end if;
708 END IF; -- quantity not null
709
710 -- end quantity validation changes 6661597
711 G_rtln_tbl(l_index).quantity := x_output_quantity;--l_rec_options.quantity;
712 G_rtln_tbl(l_index).included_flag := 'N';
713 G_rtln_tbl(l_index).created_flag := 'N';
714
715 l_index := l_index + 1;
716
717 END LOOP ;
718
719 END Populate_Rtln_Tbl ;
720
721
722
723 PROCEDURE Get_config_details(
724 p_api_version_number IN NUMBER,
725 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
726 p_commit IN VARCHAR2 := FND_API.G_FALSE,
727 p_control_rec IN aso_quote_pub.control_rec_type
728 := aso_quote_pub.G_MISS_control_rec,
729 p_qte_header_rec IN aso_quote_pub.qte_header_rec_type,
730 p_model_line_rec IN aso_quote_pub.qte_line_rec_type,
731 p_config_rec IN aso_quote_pub.qte_line_dtl_rec_type,
732 p_config_hdr_id IN NUMBER,
733 p_config_rev_nbr IN NUMBER,
737 )
734 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
735 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
736 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
738 IS
739 CURSOR C_config_details_ins (l_config_hdr_id NUMBER,
740 l_config_rev_nbr NUMBER ) IS
741 SELECT config_hdr_id,
742 config_rev_nbr ,
743 config_item_id ,
744 parent_config_item_id ,
745 inventory_item_id ,
746 organization_id ,
747 component_code ,
748 quantity ,
749 uom_code,
750 bom_sort_order,
751 config_delta,
752 name,
753 line_type,
754 component_sequence_id,
755 ato_config_item_id,
756 model_config_item_id
757 FROM cz_config_details_v cfg_dtl
758 WHERE config_hdr_id = l_config_hdr_id
759 AND config_rev_nbr = l_config_rev_nbr
760 AND NOT EXISTS (SELECT NULL
761 FROM ASO_QUOTE_LINE_DETAILS qte_dtl
762 WHERE qte_dtl.config_header_id = cfg_dtl.config_hdr_id
763 AND qte_dtl.config_revision_num = cfg_dtl.config_rev_nbr
764 AND qte_dtl.config_item_id = cfg_dtl.config_item_id )
765 ORDER BY cfg_dtl.bom_sort_order;
766
767 --assumption is currently we are updating only the qty/uom/the flags and bom_sort_order
768 CURSOR C_config_details_upd ( l_config_hdr_id NUMBER ,
769 l_config_rev_nbr NUMBER ,
770 l_complete_configuration_flag VARCHAR2,
771 l_valid_configuration_flag VARCHAR2 ) IS
772 SELECT dtl.quote_line_id,
773 dtl.quote_line_detail_id,
774 cfg.inventory_item_id ,
775 cfg.organization_id ,
776 cfg.component_code ,
777 cfg.quantity ,
778 cfg.uom_code,
779 cfg.bom_sort_order,
780 cfg.config_delta,
781 cfg.line_type,
782 cfg.name,
783 qte.line_type_source_flag
784 FROM ASO_QUOTE_LINE_DETAILS dtl,
785 CZ_CONFIG_DETAILS_V cfg ,
786 ASO_QUOTE_LINES_ALL qte
787 WHERE dtl.config_header_id = l_config_hdr_id
788 AND cfg.config_rev_nbr = l_config_rev_nbr
789 AND dtl.config_header_id = cfg.config_hdr_id
790 AND dtl.config_revision_num = cfg.config_rev_nbr
791 AND dtl.config_item_id = cfg.config_item_id
792 AND dtl.quote_line_id = qte.quote_line_id
793 AND ((qte.quantity <> cfg.quantity)
794 OR (qte.uom_code <> cfg.uom_code)
795 OR (dtl.complete_configuration_flag <> l_complete_configuration_flag)
796 OR (dtl.valid_configuration_flag <> l_valid_configuration_flag)
797 OR (dtl.bom_sort_order <> cfg.bom_sort_order)
798 OR (dtl.config_instance_name <> cfg.name)
799 OR (nvl(dtl.config_delta,-1) <> nvl(cfg.config_delta, -1))
800 OR (nvl(qte.order_line_type_id,-1) <> nvl(cfg.line_type, -1)));
801
802 CURSOR C_config_details_del( l_config_hdr_id NUMBER ,
803 l_config_rev_nbr NUMBER ) IS
804 SELECT dtl.quote_line_id
805 FROM ASO_QUOTE_LINE_DETAILS dtl
806 WHERE dtl.config_header_id = l_config_hdr_id
807 AND dtl.config_revision_num = l_config_rev_nbr
808 AND NOT EXISTS ( SELECT NULL
809 FROM CZ_CONFIG_DETAILS_V cfg
810 WHERE cfg.config_item_id = dtl.config_item_id
811 AND cfg.config_hdr_id = l_config_hdr_id
812 AND cfg.config_rev_nbr = l_config_rev_nbr );
813
814 CURSOR C_config_all(p_parent_config_item_id number) IS
815 SELECT quote_line_id
816 FROM aso_quote_line_details
817 WHERE config_header_id = p_config_hdr_id
818 AND config_revision_num = p_config_rev_nbr
819 AND config_item_id = p_parent_config_item_id;
820
821 CURSOR C_Config_Exists( l_config_hdr_id NUMBER ,
822 l_config_rev_nbr NUMBER ) IS
823 SELECT quote_line_id
824 FROM aso_quote_line_details
825 WHERE ref_type_code = 'CONFIG'
826 AND ref_line_id IS NULL
827 AND config_header_id = l_config_hdr_id
828 AND config_revision_num = l_config_rev_nbr;
829
830 CURSOR c_quote(c_qte_header_id NUMBER) IS
831 SELECT last_update_date, quote_type
832 FROM ASO_QUOTE_HEADERS_ALL
833 WHERE quote_header_id = c_qte_header_id;
834
835 CURSOR Order_Type_C IS
836 SELECT order_line_type_id, line_category_code, price_list_id, line_number,ship_model_complete_flag,
837 config_model_type
838 FROM aso_quote_lines_all
839 WHERE quote_line_id = p_config_rec.quote_line_id;
840
841 CURSOR c_messages is
842 SELECT constraint_type, message
843 FROM cz_config_messages
844 WHERE config_hdr_id = p_config_hdr_id
845 AND config_rev_nbr = p_config_rev_nbr;
846
847 CURSOR C_diff_Config_Exists IS
848 SELECT config_header_id
849 FROM aso_quote_line_details
850 WHERE ref_type_code = 'CONFIG'
851 AND ref_line_id IS NULL
852 AND quote_line_id = p_config_rec.quote_line_id;
853
854 CURSOR c_config_exist_in_cz (p_config_hdr_id number, p_config_rev_nbr number) IS
855 select config_hdr_id
856 from cz_config_details_v
857 where config_hdr_id = p_config_hdr_id
858 and config_rev_nbr = p_config_rev_nbr;
859
863 l_complete_configuration_flag VARCHAR2(1);
860 l_api_name CONSTANT VARCHAR2(30) := 'Get_Config_Details' ;
861 l_api_version_number CONSTANT NUMBER := 1.0;
862 l_index BINARY_INTEGER ;
864 l_valid_configuration_flag VARCHAR2(1);
865 l_quote_line_id NUMBER;
866 l_last_update_date date;
867 p NUMBER;
868 l_order_line_type_id NUMBER;
869 l_line_category_code VARCHAR2(30);
870 l_price_list_id NUMBER;
871 l_quote_type VARCHAR2(1);
872 l_line_number NUMBER;
873 i NUMBER;
874 l_len_msg NUMBER;
875 l_old_config_hdr_id NUMBER;
876 l_ship_model_complete_flag VARCHAR2(1);
877
878 l_control_rec ASO_PRICING_INT.PRICING_CONTROL_REC_TYPE
879 := ASO_UTILITY_PVT.Get_Pricing_Control_Rec;
880 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
881 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type := ASO_QUOTE_PUB.G_MISS_Qte_Line_Tbl;
882 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type := ASO_QUOTE_PUB.G_MISS_Qte_Line_Dtl_Tbl;
883 -- l_qte_line_dtl_search ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type := ASO_QUOTE_PUB.G_MISS_Qte_Line_Dtl_Tbl;
884 -- bug 11696691
885 l_qte_line_dtl_search ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type1 := ASO_QUOTE_PUB.G_MISS_Qte_Line_Dtl_Tbl1;
886
887 l_hd_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
888 l_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
889 l_hd_shipment_rec ASO_QUOTE_PUB.Shipment_rec_Type;
890 l_hd_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
891 l_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
892 l_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
893 l_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
894 l_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
895 l_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
896 l_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
897 l_ln_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
898 l_ln_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
899 l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
900 l_ln_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
901 l_ln_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
902 l_shipment_tbl ASO_QUOTE_PUB.Shipment_tbl_Type;
903
904 lx_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
905 lx_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
906 lx_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
907 lx_hd_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
908 lx_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
909 lx_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
910 lx_hd_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
911 lx_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
912 lx_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
913 lx_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
914 lx_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
915 lx_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
916 lx_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
917 lx_ln_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
918 lx_ln_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
919 lx_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
920 lx_ln_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
921 lx_ln_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
922
923 l_file varchar2(200);
924 lx_return_status varchar2(10);
925 l_config_model_type varchar2(30);
926
927 -- added by 6661597
928 l_validated_quantity NUMBER;
929 l_primary_quantity NUMBER;
930 l_qty_return_status VARCHAR2(1);
931 p_item_id NUMBER;
932 p_organization_id NUMBER;
933 p_uom_code VARCHAR2(50);
934 p_input_quantity NUMBER;
935 x_output_quantity NUMBER;
936 x_ret_Stat VARCHAR2(50);
937 -- end 6661597
938
939 BEGIN
940 -- Standard Start of API savepoint
941 SAVEPOINT GET_CONFIG_DETAILS_INT;
942
943 /*
944 aso_debug_pub.g_debug_flag := 'Y';
945 aso_debug_pub.SetDebugLevel(10);
946 aso_debug_pub.Initialize;
947 l_file := ASO_DEBUG_PUB.Set_Debug_Mode('FILE');
948 aso_debug_pub.debug_on;
949 */
950
951 IF aso_debug_pub.g_debug_flag = 'Y' THEN
952
953 aso_debug_pub.add('6661597 ASO_CFG_INT: GET_CONFIG_DETAILS: Start %%%%%%%%%%%%%%%%%%%', 1, 'Y');
954
955 aso_debug_pub.add('GET_CONFIG_DETAILS: p_qte_header_rec.quote_header_id: '|| p_qte_header_rec.quote_header_id);
956 aso_debug_pub.add('GET_CONFIG_DETAILS: p_config_hdr_id: '|| p_config_hdr_id);
957 aso_debug_pub.add('GET_CONFIG_DETAILS: p_config_rev_nbr: '|| p_config_rev_nbr);
958 aso_debug_pub.add('p_config_rec.valid_configuration_flag: '|| p_config_rec.valid_configuration_flag);
959 aso_debug_pub.add('p_config_rec.complete_configuration_flag: '|| p_config_rec.complete_configuration_flag);
960 END IF;
961
962 -- Standard call to check for call compatibility.
963 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
964 p_api_version_number,
965 l_api_name,
966 G_PKG_NAME) THEN
967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
968 END IF;
969
970 -- Initialize message list if p_init_msg_list is set to TRUE.
971 IF FND_API.to_Boolean( p_init_msg_list ) THEN
972 FND_MSG_PUB.initialize;
973 END IF;
974
978
975 -- Set return status to success
976 x_return_status := FND_API.G_RET_STS_SUCCESS;
977
979 --Procedure added by Anoop Rajan on 30/09/2005 to print login details
980 IF aso_debug_pub.g_debug_flag = 'Y' THEN
981 aso_debug_pub.add('Before call to printing login info details', 1, 'Y');
982 ASO_UTILITY_PVT.print_login_info;
983 aso_debug_pub.add('After call to printing login info details', 1, 'Y');
984 END IF;
985
986 -- Change Done By Girish
987 -- Procedure added to validate the operating unit
988 ASO_VALIDATE_PVT.VALIDATE_OU(p_qte_header_rec);
989
990
991 -- check whether a different config_header_id is already
992 -- associated with this model item. If yes raise an error.
993
994 IF aso_debug_pub.g_debug_flag = 'Y' THEN
995 aso_debug_pub.add( 'ASO_CFG_INT: Get_config_details: Before C_diff_Config_Exists cursor open');
996 END IF;
997
998 OPEN C_diff_Config_Exists;
999 FETCH C_diff_Config_Exists INTO l_old_config_hdr_id ;
1000 CLOSE C_diff_Config_Exists;
1001
1002 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1003 aso_debug_pub.add( 'ASO_CFG_INT: Get_config_details: l_old_config_hdr_id: '||l_old_config_hdr_id, 1, 'Y');
1004 END IF;
1005
1006 IF l_old_config_hdr_id IS NOT NULL AND l_old_config_hdr_id <> p_config_hdr_id THEN
1007
1008 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1009 aso_debug_pub.add( 'ASO_CFG_INT: Get_config_details: Inside If l_old_config_hdr_id <> p_config_hdr_id cond', 1, 'Y');
1010 END IF;
1011
1012 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1013 FND_MESSAGE.Set_Name('ASO', 'ASO_DIFFERENT_CONFIG_EXISTS');
1014 FND_MSG_PUB.ADD;
1015 END IF;
1016 RAISE FND_API.G_EXC_ERROR;
1017
1018 END IF;
1019
1020 -- check whether the config_header_id+config_revision_num is already
1021 -- associated with other model item. If yes raise an error.
1022
1023 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1024 aso_debug_pub.add( 'ASO_CFG_INT: Get_config_details: Before C_config_exists cursor open');
1025 END IF;
1026
1027 OPEN C_config_exists(l_config_hdr_id => p_config_hdr_id ,
1028 l_config_rev_nbr => p_config_rev_nbr);
1029 FETCH C_config_exists INTO l_quote_line_id ;
1030 CLOSE C_config_exists;
1031
1032 IF l_quote_line_id IS NOT NULL AND l_quote_line_id <> p_config_rec.quote_line_id THEN
1033
1034 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1035 aso_debug_pub.add( 'ASO_CFG_INT: Get_config_details: Inside C_config_exists cursor l_quote_line_id: '||l_quote_line_id);
1036 END IF;
1037
1038 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1039 FND_MESSAGE.Set_Name('ASO', 'ASO_API_CONFIG_EXISTS');
1040 FND_MSG_PUB.ADD;
1041 END IF;
1042 RAISE FND_API.G_EXC_ERROR;
1043
1044 END IF;
1045
1046 --check if the quote has been modified by someone else
1047
1048 OPEN c_quote(p_qte_header_rec.quote_header_id);
1049 FETCH c_quote INTO l_last_update_date, l_quote_type;
1050 CLOSE c_quote;
1051
1052 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1053 aso_debug_pub.add('Get_config_details: p_qte_header_rec.last_update_date: ' || to_char(p_qte_header_rec.last_update_date, 'DD-MON-YYYY HH24:MI:SS'));
1054 aso_debug_pub.add('Get_config_details: l_last_update_date: ' || to_char(l_last_update_date, 'DD-MON-YYYY HH24:MI:SS'));
1055 aso_debug_pub.add('ASO_CFG_INT: Get_config_details: l_quote_type: ' || l_quote_type);
1056 END IF;
1057
1058 if (p_qte_header_rec.last_update_date is not null) and (p_qte_header_rec.last_update_date <> fnd_api.g_miss_date) then
1059
1060 If (l_last_update_date <> p_qte_header_rec.last_update_date) Then
1061
1062 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1063 FND_MESSAGE.Set_Name('ASO', 'ASO_API_RECORD_CHANGED');
1064 FND_MESSAGE.Set_Token('INFO', 'quote', FALSE);
1065 FND_MSG_PUB.ADD;
1066 END IF;
1067 raise FND_API.G_EXC_ERROR;
1068
1069 End if;
1070
1071 end if;
1072
1073 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1074 aso_debug_pub.add( 'ASO_CFG_INT: Get_config_details: After C_config_exists cursor');
1075 END IF;
1076
1077 --check if revision number has changed for this configuration.
1078 --if yes update all the previous selected options to the current
1079 --revision number.This is necessary as the current revision number
1080 --will not be the same as in aso_quote_line_details.
1081
1082 IF ((p_config_rec.config_header_id <> FND_API.G_Miss_num AND
1083 p_config_rec.config_revision_num <> FND_API.G_Miss_Num) AND
1084 (p_config_rec.config_header_id IS NOT NULL AND
1085 p_config_rec.config_revision_num IS NOT NULL)) AND
1086 (p_config_rec.config_header_id <> p_config_hdr_id OR
1087 p_config_rec.config_revision_num <> p_config_rev_nbr) THEN
1088 BEGIN
1089 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1090 aso_debug_pub.add( 'ASO_CFG_INT: Get_config_details: Revision number has changed so updating');
1091 END IF;
1092
1093 UPDATE aso_quote_line_details
1094 SET config_revision_num = p_config_rev_nbr,
1095 last_update_date = sysdate,
1096 last_updated_by = FND_GLOBAL.USER_ID,
1097 last_update_login = FND_GLOBAL.CONC_LOGIN_ID
1098 WHERE config_header_id = p_config_rec.config_header_id
1099 AND config_revision_num = p_config_rec.config_revision_num ;
1100
1101 EXCEPTION
1105 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1102
1103 WHEN OTHERS THEN
1104
1106 aso_debug_pub.add('Get_config_details: Inside WHEN OTHERS Exception of Update config_revision_num');
1107 END IF;
1108 END;
1109
1110 END IF;
1111
1112
1113 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1114 aso_debug_pub.add( 'ASO_CFG_INT: Get_config_details: After Update config_revision_num');
1115 END IF;
1116
1117 OPEN Order_Type_C;
1118 FETCH Order_Type_C INTO l_order_line_type_id, l_line_category_code, l_price_list_id, l_line_number,l_ship_model_complete_flag,l_config_model_type;
1119
1120 IF Order_Type_C%NOTFOUND THEN
1121
1122 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1123 aso_debug_pub.add( 'ASO_CFG_INT: Get_config_details: Cursor Order_Type_C NOTFOUND');
1124 END IF;
1125
1126 END IF;
1127 CLOSE Order_Type_C;
1128
1129 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1130
1131 aso_debug_pub.add('Get_config_details: Model: p_config_rec.quote_line_id: '|| p_config_rec.quote_line_id);
1132 aso_debug_pub.add('Get_config_details: Model: l_order_line_type_id: ' || l_order_line_type_id);
1133 aso_debug_pub.add('Get_config_details: Model: l_line_category_code: ' || l_line_category_code);
1134 aso_debug_pub.add('Get_config_details: Model: l_price_list_id: ' || l_price_list_id);
1135 aso_debug_pub.add('Get_config_details: Model: l_line_number: ' || l_line_number);
1136 aso_debug_pub.add('Get_config_details: Model: l_ship_model_complete_flag: ' || l_ship_model_complete_flag);
1137 aso_debug_pub.add('Get_config_details: Model: l_config_model_type: ' || l_config_model_type);
1138 END IF;
1139
1140 l_index := 0;
1141
1142 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1143 aso_debug_pub.add('ASO_CFG_INT: Get_Config_details: Before C_config_details_ins cursor LOOP');
1144 END IF;
1145
1146 FOR row IN C_config_details_ins(p_config_hdr_id, p_config_rev_nbr)
1147 LOOP
1148
1149 l_index := l_index + 1;
1150
1151 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1152
1153 aso_debug_pub.add('6661597 ASO_CFG_INT: Get_Config_details: Inside C_config_details_ins cursor LOOP');
1154 aso_debug_pub.add('Get_Config_Details: l_index: '|| l_index);
1155 aso_debug_pub.add('Get_Config_Details: config_header_id: '|| row.config_hdr_id);
1156 aso_debug_pub.add('Get_Config_Details: config_revision_num: '|| row.config_rev_nbr);
1157 aso_debug_pub.add('Get_Config_Details: config_item_id: '|| row.config_item_id);
1158 aso_debug_pub.add('Get_Config_Details: parent_config_item_id: '|| row.parent_config_item_id);
1159 aso_debug_pub.add('Get_Config_Details: inventory_item_id: '|| row.inventory_item_id);
1160 aso_debug_pub.add('Get_Config_Details: organization_id: '|| row.organization_id);
1161 aso_debug_pub.add('Get_Config_Details: component_code: '|| row.component_code);
1162 aso_debug_pub.add('Get_Config_Details: quantity: '|| row.quantity);
1163 aso_debug_pub.add('Get_Config_Details: uom_code: '|| row.uom_code);
1164 aso_debug_pub.add('Get_Config_Details: bom_sort_order: '|| row.bom_sort_order);
1165 aso_debug_pub.add('Get_Config_Details: config_delta: '|| row.config_delta);
1166 aso_debug_pub.add('Get_Config_Details: name: '|| row.name);
1167 aso_debug_pub.add('Get_Config_Details: line_type: '|| row.line_type);
1168 aso_debug_pub.add('Get_Config_Details: component_sequence_id: '|| row.component_sequence_id);
1169 aso_debug_pub.add('Get_Config_Details: ato_config_item_id: '|| row.ato_config_item_id);
1170 aso_debug_pub.add('Get_Config_Details: model_config_item_id: '|| row.model_config_item_id);
1171 END IF;
1172
1173 p_item_id:=row.inventory_item_id;
1174 p_organization_id:=row.organization_id;
1175 p_uom_code:=row.uom_code;
1176 p_input_quantity:=row.quantity;
1177 x_output_quantity:=FND_API.G_MISS_NUM;
1178 -- inv quantity validation 6661597
1179 IF (p_input_quantity is not null AND p_input_quantity <> FND_API.G_MISS_NUM) THEN
1180 inv_decimals_pub.validate_quantity(
1181 p_item_id => p_item_id ,
1182 p_organization_id => p_organization_id ,
1183 p_input_quantity => p_input_quantity,
1184 p_uom_code => p_uom_code,
1185 x_output_quantity => l_validated_quantity,
1186 x_primary_quantity => l_primary_quantity,
1187 x_return_status => x_ret_Stat);
1188 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1189 if x_ret_Stat = 'E' THEN
1190 x_return_status:= FND_API.G_RET_STS_ERROR;
1191 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1192 FND_MESSAGE.Set_Name('ASO', 'ASO_ERR_FRACTIONAL_QUANTITY');
1193 FND_MSG_PUB.ADD;
1194 END IF;
1195 RAISE FND_API.G_EXC_ERROR;
1196 elsif x_ret_Stat = 'W' then
1197 x_output_quantity:= l_validated_quantity;
1198 else
1199 x_output_quantity:=p_input_quantity;
1200 end if;
1201 END IF; -- quantity not null
1202
1203 -- end quantity validation changes 6661597
1204
1205
1206 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1207 aso_debug_pub.add('Get_Config_Details: quantity: after inv validation '|| x_output_quantity);
1208 end if;
1209 IF to_char( row.inventory_item_id ) = row.component_code THEN
1210
1211 l_Qte_Line_Tbl(l_index).item_type_code := 'MDL';
1212 l_Qte_Line_Tbl(l_index).OPERATION_CODE := 'UPDATE';
1216 -- bug 3883545
1213 l_Qte_Line_Tbl(l_index).quote_header_id := p_qte_header_rec.quote_header_id;
1214 l_Qte_Line_Tbl(l_index).quote_line_id := p_config_rec.quote_line_id ;
1215
1217
1218 l_Qte_Line_Tbl(l_index).quantity := x_output_quantity ; -- changed by 6661597
1219 l_qte_line_dtl_tbl(l_index).operation_code := 'CREATE';
1220 l_qte_line_dtl_tbl(l_index).qte_line_index := l_index;
1221 l_qte_line_dtl_tbl(l_index).config_header_id := p_config_hdr_id;
1222 l_qte_line_dtl_tbl(l_index).config_revision_num := p_config_rev_nbr;
1223 l_qte_line_dtl_tbl(l_index).complete_configuration_flag := p_config_rec.complete_configuration_flag;
1224 l_qte_line_dtl_tbl(l_index).valid_configuration_flag := p_config_rec.valid_configuration_flag;
1225 l_qte_line_dtl_tbl(l_index).component_code := row.component_code;
1226 l_Qte_Line_dtl_Tbl(l_index).quote_line_id := p_config_rec.quote_line_id;
1227 l_Qte_Line_dtl_Tbl(l_index).config_item_id := row.config_item_id;
1228 l_Qte_Line_dtl_Tbl(l_index).parent_config_item_id := NULL;
1229 l_qte_line_dtl_tbl(l_index).ref_type_code := 'CONFIG';
1230 l_qte_line_dtl_tbl(l_index).bom_sort_order := row.bom_sort_order;
1231 l_qte_line_dtl_tbl(l_index).config_delta := row.config_delta;
1232 l_qte_line_dtl_tbl(l_index).config_instance_name := row.name;
1233 l_qte_line_dtl_search(row.config_item_id).quote_line_id := p_config_rec.quote_line_id;
1234 l_qte_line_dtl_tbl(l_index).component_sequence_id := row.component_sequence_id;
1235 IF row.ato_config_item_id IS NOT NULL THEN
1236 l_qte_line_dtl_tbl(l_index).ato_line_id := p_config_rec.quote_line_id;
1237 END IF;
1238 l_qte_line_dtl_tbl(l_index).top_model_line_id := p_config_rec.quote_line_id;
1239
1240
1241
1242 ELSE
1243
1244 l_Qte_Line_Tbl(l_index).OPERATION_CODE := 'CREATE';
1245 l_Qte_Line_Tbl(l_index).quote_header_id := p_qte_header_rec.quote_header_id;
1246 l_Qte_Line_Tbl(l_index).item_type_code := 'CFG';
1247 l_Qte_Line_Tbl(l_index).organization_id := row.organization_id;
1248 l_Qte_Line_Tbl(l_index).inventory_item_id := row.inventory_item_id;
1249 l_Qte_Line_Tbl(l_index).quantity := x_output_quantity; -- changed by 6661597
1250 l_Qte_Line_Tbl(l_index).uom_code := row.uom_code;
1251 --l_Qte_Line_Tbl(l_index).order_line_type_id := l_order_line_type_id; -- has been commented
1252 l_Qte_Line_Tbl(l_index).line_category_code := l_line_category_code;
1253 l_Qte_Line_Tbl(l_index).price_list_id := l_price_list_id;
1254 l_Qte_Line_Tbl(l_index).line_number := l_line_number;
1255 l_Qte_Line_Tbl(l_index).ship_model_complete_flag := l_ship_model_complete_flag;
1256 l_Qte_Line_Tbl(l_index).config_model_type := l_config_model_type;
1257 l_qte_line_dtl_tbl(l_index).operation_code := 'CREATE';
1258 l_qte_line_dtl_tbl(l_index).qte_line_index := l_index;
1259 l_qte_line_dtl_tbl(l_index).config_header_id := p_config_hdr_id;
1260 l_qte_line_dtl_tbl(l_index).config_revision_num := p_config_rev_nbr;
1261 l_qte_line_dtl_tbl(l_index).complete_configuration_flag := p_config_rec.complete_configuration_flag;
1262 l_qte_line_dtl_tbl(l_index).valid_configuration_flag := p_config_rec.valid_configuration_flag;
1263 l_qte_line_dtl_tbl(l_index).component_code := row.component_code;
1264 l_qte_line_dtl_tbl(l_index).config_item_id := row.config_item_id;
1265 l_qte_line_dtl_tbl(l_index).parent_config_item_id := row.parent_config_item_id;
1266 l_qte_line_dtl_tbl(l_index).ref_type_code := 'CONFIG';
1267 l_qte_line_dtl_tbl(l_index).bom_sort_order := row.bom_sort_order;
1268 l_qte_line_dtl_tbl(l_index).config_delta := row.config_delta;
1269 l_qte_line_dtl_tbl(l_index).config_instance_name := row.name;
1270 l_qte_line_dtl_search(row.config_item_id).qte_line_index := l_index;
1271 l_qte_line_dtl_tbl(l_index).component_sequence_id := row.component_sequence_id;
1272 l_qte_line_dtl_tbl(l_index).top_model_line_id := p_config_rec.quote_line_id;
1273 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1274 aso_debug_pub.add('Get_Config_Details: l_qte_line_dtl_search('||row.config_item_id||').qte_line_index: '||l_qte_line_dtl_search(row.config_item_id).qte_line_index);
1275
1276 END IF;
1277
1278 --Creating the parent-child relationship
1279
1280 IF l_qte_line_dtl_search.EXISTS(row.parent_config_item_id) THEN
1281
1282 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1283 aso_debug_pub.add('Index of parent: l_qte_line_dtl_search('||row.parent_config_item_id||').qte_line_index: '||l_qte_line_dtl_search(row.parent_config_item_id).qte_line_index);
1284 aso_debug_pub.add('Quote_line_id of parent: l_qte_line_dtl_search('||row.parent_config_item_id||').quote_line_id: '||l_qte_line_dtl_search(row.parent_config_item_id).quote_line_id);
1285
1286 END IF;
1287 l_qte_line_dtl_tbl(l_index).ref_line_index := l_qte_line_dtl_search(row.parent_config_item_id).qte_line_index;
1288 l_qte_line_dtl_tbl(l_index).ref_line_id := l_qte_line_dtl_search(row.parent_config_item_id).quote_line_id;
1289
1290 ELSE
1291
1292 OPEN C_config_all(l_qte_line_dtl_tbl(l_index).parent_config_item_id);
1293 FETCH C_config_all INTO l_qte_line_dtl_tbl(l_index).ref_line_id;
1294 CLOSE C_config_all;
1295
1296 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1297
1301
1298 aso_debug_pub.add('l_qte_line_dtl_tbl('||l_index||').ref_line_id: '||l_qte_line_dtl_tbl(l_index).ref_line_id);
1299
1300 END IF;
1302
1303
1304
1305 END IF;
1306
1307 -- Populating the ato_line_id
1308
1309 IF l_qte_line_dtl_search.EXISTS(row.ato_config_item_id) THEN
1310
1311 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1312
1313 aso_debug_pub.add('Index of ato : l_qte_line_dtl_search('||row.ato_config_item_id||').qte_line_index: '||l_qte_line_dtl_search(row.ato_config_item_id).qte_line_index);
1314
1315 aso_debug_pub.add('Quote_line_id of ato: l_qte_line_dtl_search('||row.ato_config_item_id||').quote_line_id: '||l_qte_line_dtl_search(row.ato_config_item_id).quote_line_id);
1316
1317 END IF;
1318
1319 l_qte_line_dtl_tbl(l_index).ato_line_index := l_qte_line_dtl_search(row.ato_config_item_id).qte_line_index;
1320 l_qte_line_dtl_tbl(l_index).ato_line_id := l_qte_line_dtl_search(row.ato_config_item_id).quote_line_id;
1321
1322 ELSE
1323
1324 OPEN C_config_all(row.ato_config_item_id);
1325 FETCH C_config_all INTO l_qte_line_dtl_tbl(l_index).ato_line_id;
1326 CLOSE C_config_all;
1327
1328 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1329
1330 aso_debug_pub.add('l_qte_line_dtl_tbl('||l_index||').ato_line_id: '||l_qte_line_dtl_tbl(l_index).ato_line_id);
1331
1332 END IF;
1333 END IF;
1334
1335 -- End of logic for populating the ato_line_id
1336
1337
1338 --Populating order_line_type_id value based on CZ line_type value
1339
1340 IF row.line_type IS NOT NULL THEN
1341
1342 l_Qte_Line_Tbl(l_index).order_line_type_id := row.line_type;
1343 l_Qte_Line_Tbl(l_index).Line_type_source_flag := 'C';
1344
1345 ELSE
1346
1347 l_Qte_Line_Tbl(l_index).order_line_type_id := l_order_line_type_id;
1348
1349 END IF;
1350
1351 END IF;
1352 END LOOP;
1353
1354 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1355
1356 aso_debug_pub.add( 'ASO_CFG_INT: Get_Config_details: After C_config_details_ins cursor LOOP l_index: '|| l_index);
1357
1358 aso_debug_pub.add( 'ASO_CFG_INT: Get_Config_details: Before C_config_details_upd cursor LOOP');
1359
1360 END IF;
1361
1362 FOR row IN C_config_details_upd( p_config_hdr_id,
1363 p_config_rev_nbr,
1364 p_config_rec.complete_configuration_flag,
1365 p_config_rec.valid_configuration_flag)
1366 LOOP
1367
1368 l_index := l_index + 1;
1369
1370 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1371
1372 aso_debug_pub.add('ASO_CFG_INT: Get_Config_details: Inside C_config_details_upd cursor LOOP');
1373 aso_debug_pub.add('Get_Config_Details: l_index: '|| l_index);
1374 aso_debug_pub.add('Get_Config_Details: quote_line_id: '|| row.quote_line_id);
1375 aso_debug_pub.add('Get_Config_Details: quote_line_detail_id: '|| row.quote_line_detail_id);
1376 aso_debug_pub.add('Get_Config_Details: inventory_item_id: '|| row.inventory_item_id);
1377 aso_debug_pub.add('Get_Config_Details: organization_id: '|| row.organization_id);
1378 aso_debug_pub.add('Get_Config_Details: component_code: '|| row.component_code);
1379 aso_debug_pub.add('Get_Config_Details: quantity: '|| row.quantity);
1380 aso_debug_pub.add('Get_Config_Details: uom_code: '|| row.uom_code);
1381 aso_debug_pub.add('Get_Config_Details: bom_sort_order: '|| row.bom_sort_order);
1382 aso_debug_pub.add('Get_Config_Details: config_delta: '|| row.config_delta);
1383 aso_debug_pub.add('Get_Config_Details: name: '|| row.name);
1384 aso_debug_pub.add('Get_Config_Details: line_type: '|| row.line_type);
1385 END IF;
1386
1387 -- added by 6661597
1388 p_item_id:=row.inventory_item_id;
1389 p_organization_id:=row.organization_id;
1390 p_uom_code:=row.uom_code;
1391 p_input_quantity:=row.quantity;
1392 x_output_quantity:=FND_API.G_MISS_NUM;
1393 -- inv quantity validation 6661597
1394 IF (p_input_quantity is not null AND p_input_quantity <> FND_API.G_MISS_NUM) THEN
1395 inv_decimals_pub.validate_quantity(
1396 p_item_id => p_item_id ,
1397 p_organization_id => p_organization_id ,
1398 p_input_quantity => p_input_quantity,
1399 p_uom_code => p_uom_code,
1400 x_output_quantity => l_validated_quantity,
1401 x_primary_quantity => l_primary_quantity,
1402 x_return_status => x_ret_Stat);
1403 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1404 if x_ret_Stat = 'E' THEN
1405 x_return_status:= FND_API.G_RET_STS_ERROR;
1406 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1407 FND_MESSAGE.Set_Name('ASO', 'ASO_ERR_FRACTIONAL_QUANTITY');
1408 FND_MSG_PUB.ADD;
1409 END IF;
1410 RAISE FND_API.G_EXC_ERROR;
1411 elsif x_ret_Stat = 'W' then
1412 x_output_quantity:= l_validated_quantity;
1413 else
1414 x_output_quantity:=p_input_quantity;
1415 end if;
1416 END IF; -- quantity not null
1417
1421 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1418 -- end quantity validation changes rassharm
1419
1420
1422 aso_debug_pub.add('Get_Config_Details: quantity: after inv validation '|| x_output_quantity);
1423 end if;
1424
1425 -- end 6661597
1426 l_Qte_Line_Tbl(l_index).quote_header_id := p_qte_header_rec.quote_header_id;
1427 l_Qte_Line_Tbl(l_index).quote_line_id := row.quote_line_id;
1428 l_Qte_Line_Tbl(l_index).OPERATION_CODE := 'UPDATE';
1429 l_Qte_Line_Tbl(l_index).quantity := x_output_quantity; -- added by 6661597
1430 l_Qte_Line_Tbl(l_index).uom_code := row.uom_code;
1431
1432 l_Qte_Line_dtl_Tbl(l_index).OPERATION_CODE := 'UPDATE';
1433 l_Qte_Line_dtl_Tbl(l_index).quote_line_detail_id := row.quote_line_detail_id;
1434 l_Qte_Line_dtl_Tbl(l_index).quote_line_id := row.quote_line_id;
1435 l_Qte_Line_dtl_Tbl(l_index).complete_configuration_flag := p_config_rec.complete_configuration_flag;
1436 l_Qte_Line_dtl_Tbl(l_index).valid_configuration_flag := p_config_rec.valid_configuration_flag;
1437 l_qte_line_dtl_tbl(l_index).bom_sort_order := row.bom_sort_order;
1438 l_qte_line_dtl_tbl(l_index).config_delta := row.config_delta;
1439 l_qte_line_dtl_tbl(l_index).config_instance_name := row.name;
1440
1441 --Updating order_line_type_id value based on CZ line_type value
1442
1443 IF row.line_type IS NOT NULL THEN
1444
1445 l_Qte_Line_Tbl(l_index).order_line_type_id := row.line_type;
1446 l_Qte_Line_Tbl(l_index).line_type_source_flag := 'C';
1447
1448 ELSIF row.line_type_source_flag = 'C' THEN
1449
1450 l_Qte_Line_Tbl(l_index).order_line_type_id := NULL;
1451
1452 END IF;
1453
1454
1455
1456 END LOOP;
1457
1458 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1459
1460 aso_debug_pub.add( 'Get_Config_details: After C_config_details_upd cursor LOOP l_index: '|| l_index);
1461 aso_debug_pub.add( 'ASO_CFG_INT: Get_Config_details: Before C_config_details_del cursor LOOP');
1462
1463 END IF;
1464
1465 FOR row IN C_config_details_del( p_config_hdr_id, p_config_rev_nbr )
1466 LOOP
1467
1468 l_index := l_index + 1;
1469
1470 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1471 aso_debug_pub.add('Get_Config_details: Inside C_config_details_del cursor LOOP');
1472 aso_debug_pub.add('Get_Config_Details: l_index: '|| l_index);
1473 aso_debug_pub.add('Get_Config_Details: quote_line_id: '|| row.quote_line_id);
1474 END IF;
1475
1476 l_Qte_Line_Tbl(l_index).OPERATION_CODE := 'DELETE';
1477 l_Qte_Line_Tbl(l_index).quote_line_id := row.quote_line_id;
1478
1479 END LOOP;
1480
1481 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1482
1483 aso_debug_pub.add('ASO_CFG_INT: Get_Config_details: After C_config_details_del cursor LOOP l_index: '|| l_index);
1484
1485 aso_debug_pub.add('ASO_CFG_INT: Get_Config_details: l_quote_type: '|| l_quote_type);
1486 aso_debug_pub.add('Get_Config_details: p_control_rec.CALCULATE_TAX_FLAG: '|| p_control_rec.CALCULATE_TAX_FLAG);
1487 aso_debug_pub.add('Get_Config_details: p_control_rec.CALCULATE_FREIGHT_CHARGE_FLAG: '|| p_control_rec.CALCULATE_FREIGHT_CHARGE_FLAG);
1488 aso_debug_pub.add('Get_Config_details: p_control_rec.pricing_request_type: '|| p_control_rec.pricing_request_type);
1489 aso_debug_pub.add('Get_Config_details: p_control_rec.header_pricing_event: '|| p_control_rec.header_pricing_event);
1490
1491 END IF;
1492
1493
1494 --Populate quote header record
1495
1496 l_qte_header_rec := p_qte_header_rec;
1497 l_qte_header_rec.last_update_date := l_last_update_date;
1498 l_qte_header_rec.CALL_BATCH_VALIDATION_FLAG := FND_API.G_FALSE;
1499
1500 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1501
1502 aso_debug_pub.add( 'Get_Config_details: Before call to Update Quote table count');
1503 aso_debug_pub.add( 'Get_Config_details: l_Qte_Line_Tbl.count: '||l_Qte_Line_Tbl.count);
1504 aso_debug_pub.add( 'Get_Config_details: l_Qte_Line_Dtl_Tbl.count: '||l_Qte_Line_Dtl_Tbl.count);
1505
1506 END IF;
1507
1508 ASO_QUOTE_PUB.Update_Quote(
1509 p_api_version_number => 1.0,
1510 p_init_msg_list => FND_API.G_FALSE,
1511 p_commit => FND_API.G_FALSE,
1512 p_control_rec => p_control_rec,
1513 p_qte_header_rec => l_qte_header_rec,
1514 p_hd_tax_detail_tbl => l_hd_tax_detail_tbl,
1515 --P_hd_Shipment_Tbl => l_Shipment_tbl,
1516 P_Qte_Line_Tbl => l_Qte_Line_Tbl,
1517 P_Qte_Line_Dtl_Tbl => l_Qte_Line_Dtl_Tbl,
1518 P_ln_Payment_Tbl => l_ln_Payment_Tbl,
1519 --P_ln_Tax_Detail_Tbl => l_ln_tax_detail_tbl,
1520 x_Qte_Header_Rec => lx_qte_header_rec,
1521 X_Qte_Line_Tbl => lx_Qte_Line_Tbl,
1522 X_Qte_Line_Dtl_Tbl => lx_Qte_Line_Dtl_Tbl,
1523 X_hd_Price_Attributes_Tbl => lx_hd_Price_Attr_Tbl,
1524 X_hd_Payment_Tbl => lx_hd_Payment_Tbl,
1525 X_hd_Shipment_Tbl => lx_hd_Shipment_Tbl,
1526 X_hd_Freight_Charge_Tbl => lx_hd_Freight_Charge_Tbl,
1527 X_hd_Tax_Detail_Tbl => lx_hd_Tax_Detail_Tbl,
1528 x_Line_Attr_Ext_Tbl => lx_Line_Attr_Ext_Tbl,
1529 X_line_rltship_tbl => lx_line_rltship_tbl,
1533 X_ln_Price_Attributes_Tbl => lx_ln_Price_Attr_Tbl,
1530 X_Price_Adjustment_Tbl => lx_Price_Adjustment_Tbl,
1531 X_Price_Adj_Attr_Tbl => lx_Price_Adj_Attr_Tbl,
1532 X_Price_Adj_Rltship_Tbl => lx_Price_Adj_Rltship_Tbl,
1534 X_ln_Payment_Tbl => lx_ln_Payment_Tbl,
1535 X_ln_Shipment_Tbl => lx_ln_Shipment_Tbl,
1536 X_ln_Freight_Charge_Tbl => lx_ln_Freight_Charge_Tbl,
1537 X_ln_Tax_Detail_Tbl => lx_ln_Tax_Detail_Tbl,
1538 X_Return_Status => x_Return_Status,
1539 X_Msg_Count => x_Msg_Count,
1540 X_Msg_Data => x_Msg_Data);
1541
1542 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1543 aso_debug_pub.add('Get_config_details: After call to Update_quote x_Return_Status: ' || x_Return_Status);
1544 END IF;
1545
1546 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1547
1548 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1549 aso_debug_pub.add('Get_config_details: Before deleting the previous version from CZ schema.');
1550 END IF;
1551
1552 IF ((p_config_rec.config_header_id <> FND_API.G_Miss_num AND
1553 p_config_rec.config_revision_num <> FND_API.G_Miss_Num) AND
1554 (p_config_rec.config_header_id IS NOT NULL AND
1555 p_config_rec.config_revision_num IS NOT NULL)) AND
1556 (p_config_rec.config_header_id <> p_config_hdr_id OR
1557 p_config_rec.config_revision_num <> p_config_rev_nbr) THEN
1558
1559 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1560 aso_debug_pub.add('Get_config_details: A previous version exist for this configuration so deleting it from CZ');
1561 END IF;
1562
1563 ASO_CFG_INT.DELETE_CONFIGURATION( P_API_VERSION_NUMBER => 1.0,
1564 P_INIT_MSG_LIST => FND_API.G_FALSE,
1565 P_CONFIG_HDR_ID => p_config_rec.config_header_id,
1566 P_CONFIG_REV_NBR => p_config_rec.config_revision_num,
1567 X_RETURN_STATUS => lx_return_status,
1568 X_MSG_COUNT => x_msg_count,
1569 X_MSG_DATA => x_msg_data);
1570
1571 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1572 aso_debug_pub.add('After call to ASO_CFG_INT.DELETE_CONFIGURATION: x_Return_Status: ' || lx_Return_Status);
1573 END IF;
1574
1575 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1576
1577 x_return_status := lx_return_status;
1578 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1579 FND_MESSAGE.Set_Name('ASO', 'ASO_DELETE');
1580 FND_MESSAGE.Set_Token('OBJECT', 'CONFIGURATION', FALSE);
1581 FND_MSG_PUB.ADD;
1582 END IF;
1583
1584 RAISE FND_API.G_EXC_ERROR;
1585
1586 END IF;
1587
1588 END IF;
1589
1590 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1591 RAISE FND_API.G_EXC_ERROR;
1592
1593 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1595
1596 END IF;
1597
1598 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1599 aso_debug_pub.add('Get_config_details: Before deleting records from aso_line_relationships table');
1600 END IF;
1601
1602 DELETE aso_line_relationships
1603 WHERE line_relationship_id IN (SELECT line_relationship_id
1604 FROM aso_line_relationships a
1605 WHERE a.relationship_type_code = 'CONFIG'
1606 START WITH a.quote_line_id = p_config_rec.quote_line_id
1607 CONNECT BY PRIOR a.related_quote_line_id = a.quote_line_id);
1608
1609 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1610 aso_debug_pub.add('Get_config_details: After deleting records from aso_line_relationships table');
1611 END IF;
1612
1613 G_rtln_tbl := G_MISS_rtln_tbl;
1614
1615 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1616 aso_debug_pub.add('ASO_CFG_INT: Get_config_details: Before call to populate_rtln_Tbl');
1617 END IF;
1618
1619 populate_rtln_Tbl( p_quote_header_id => p_qte_header_rec.quote_header_id,
1620 p_quote_line_id => p_config_rec.quote_line_id,
1621 p_config_hdr_id => p_config_hdr_id,
1622 p_config_rev_nbr => p_config_rev_nbr );
1623
1624
1625 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1626
1627 aso_debug_pub.add('ASO_CFG_INT: Get_config_details: After call to populate_rtln_Tbl');
1628
1629 FOR p IN G_rtln_tbl.first..G_rtln_tbl.last LOOP
1630
1631 aso_debug_pub.add( 'Get_config_details: G_rtln_tbl('||p||').quote_line_id: '|| G_rtln_tbl(p).quote_line_id);
1632 aso_debug_pub.add( 'Get_config_details: G_rtln_tbl('||p||').parent_config_item_id: '|| G_rtln_tbl(p).parent_config_item_id);
1633 aso_debug_pub.add( 'Get_config_details: G_rtln_tbl('||p||').config_item_id: '|| G_rtln_tbl(p).config_item_id);
1634 aso_debug_pub.add( 'Get_config_details: G_rtln_tbl('||p||').inventory_item_id: '|| G_rtln_tbl(p).inventory_item_id);
1635 aso_debug_pub.add( 'Get_config_details: G_rtln_tbl('||p||').organization_id: '|| G_rtln_tbl(p).organization_id);
1636 aso_debug_pub.add( 'Get_config_details: G_rtln_tbl('||p||').component_code: '|| G_rtln_tbl(p).component_code);
1637 aso_debug_pub.add( 'Get_config_details: G_rtln_tbl('||p||').quantity: '|| G_rtln_tbl(p).quantity);
1638 aso_debug_pub.add( 'Get_config_details: G_rtln_tbl('||p||').included_flag: '|| G_rtln_tbl(p).included_flag);
1639 aso_debug_pub.add( 'Get_config_details: G_rtln_tbl('||p||').created_flag: '|| G_rtln_tbl(p).created_flag);
1640
1641 END LOOP;
1642
1643 aso_debug_pub.add('ASO_CFG_INT: Get_config_details: Before call to Create_Relationship procedure');
1644
1645 END IF;
1646
1647 Create_Relationship( parent_quote_line_id => G_rtln_tbl(1).quote_line_id,
1648 p_config_item_id => G_rtln_tbl(1).config_item_id,
1649 x_return_status => x_return_status,
1650 x_msg_count => x_msg_count,
1651 x_msg_data => x_msg_data );
1652
1653 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1654
1655 aso_debug_pub.add('Get_config_details: After call to Create_Relationship: x_return_status: '|| x_return_status);
1656
1657 END IF;
1658
1659 -- Check return status from the above procedure call
1660
1661 IF x_return_status = FND_API.G_RET_STS_ERROR then
1662 raise FND_API.G_EXC_ERROR;
1663 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1664 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1665 END IF;
1666
1667 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1668 aso_debug_pub.add('Get_config_details: Before deleting the previous version from CZ schema.');
1669 END IF;
1670
1671 IF ((p_config_rec.config_header_id <> FND_API.G_Miss_num AND
1672 p_config_rec.config_revision_num <> FND_API.G_Miss_Num) AND
1673 (p_config_rec.config_header_id IS NOT NULL AND
1674 p_config_rec.config_revision_num IS NOT NULL)) AND
1675 (p_config_rec.config_header_id <> p_config_hdr_id OR
1676 p_config_rec.config_revision_num <> p_config_rev_nbr) THEN
1677
1678 open c_config_exist_in_cz(p_config_rec.config_header_id, p_config_rec.config_revision_num);
1679 fetch c_config_exist_in_cz into l_old_config_hdr_id;
1680 if c_config_exist_in_cz%found then
1681
1682 close c_config_exist_in_cz;
1683
1684 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1685 aso_debug_pub.add('Get_config_details: A previous version exist for this configuration so deleting it from CZ');
1686 END IF;
1687
1688 ASO_CFG_INT.DELETE_CONFIGURATION( P_API_VERSION_NUMBER => 1.0,
1689 P_INIT_MSG_LIST => FND_API.G_FALSE,
1690 P_CONFIG_HDR_ID => p_config_rec.config_header_id,
1691 P_CONFIG_REV_NBR => p_config_rec.config_revision_num,
1692 X_RETURN_STATUS => lx_return_status,
1693 X_MSG_COUNT => x_msg_count,
1694 X_MSG_DATA => x_msg_data);
1695
1696 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1697 aso_debug_pub.add('After call to ASO_CFG_INT.DELETE_CONFIGURATION: x_Return_Status: ' || lx_Return_Status);
1698 END IF;
1699
1700 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1701
1702 x_return_status := lx_return_status;
1703 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1704 FND_MESSAGE.Set_Name('ASO', 'ASO_DELETE');
1705 FND_MESSAGE.Set_Token('OBJECT', 'CONFIGURATION', FALSE);
1706 FND_MSG_PUB.ADD;
1707 END IF;
1708
1709 RAISE FND_API.G_EXC_ERROR;
1710
1711 END IF;
1712
1713 else
1714 close c_config_exist_in_cz;
1715 end if;
1716
1717 END IF;
1718
1719 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1720 aso_debug_pub.add( 'ASO_CFG_INT: GET_CONFIG_DETAILS: Finish %%%%%%%%%%%%%%%%%%%', 1, 'Y' );
1721 END IF;
1722
1723 EXCEPTION
1724
1725 WHEN FND_API.G_EXC_ERROR THEN
1726
1727 open c_config_exist_in_cz(p_config_hdr_id, p_config_rev_nbr);
1728 fetch c_config_exist_in_cz into l_old_config_hdr_id;
1729
1730 if c_config_exist_in_cz%found then
1731
1732 close c_config_exist_in_cz;
1733
1734 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1735 aso_debug_pub.add('Get_config_details: A previous version exist for this configuration so deleting it from CZ');
1736 END IF;
1737
1738 ASO_CFG_INT.DELETE_CONFIGURATION_AUTO( P_API_VERSION_NUMBER => 1.0,
1742 X_RETURN_STATUS => lx_return_status,
1739 P_INIT_MSG_LIST => FND_API.G_FALSE,
1740 P_CONFIG_HDR_ID => p_config_hdr_id,
1741 P_CONFIG_REV_NBR => p_config_rev_nbr,
1743 X_MSG_COUNT => x_msg_count,
1744 X_MSG_DATA => x_msg_data);
1745
1746 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1747 aso_debug_pub.add('After call to ASO_CFG_INT.DELETE_CONFIGURATION: x_Return_Status: ' || lx_Return_Status);
1748 END IF;
1749
1750 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1751
1752 x_return_status := lx_return_status;
1753 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1754 FND_MESSAGE.Set_Name('ASO', 'ASO_DELETE');
1755 FND_MESSAGE.Set_Token('OBJECT', 'CONFIGURATION', FALSE);
1756 FND_MSG_PUB.ADD;
1757 END IF;
1758
1759 RAISE FND_API.G_EXC_ERROR;
1760
1761 END IF;
1762
1763 else
1764 close c_config_exist_in_cz;
1765 end if;
1766
1767 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1768 P_API_NAME => L_API_NAME
1769 ,P_PKG_NAME => G_PKG_NAME
1770 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1771 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
1772 ,X_MSG_COUNT => X_MSG_COUNT
1773 ,X_MSG_DATA => X_MSG_DATA
1774 ,X_RETURN_STATUS => X_RETURN_STATUS);
1775
1776 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1777
1778 open c_config_exist_in_cz(p_config_hdr_id, p_config_rev_nbr);
1779 fetch c_config_exist_in_cz into l_old_config_hdr_id;
1780
1781 if c_config_exist_in_cz%found then
1782
1783 close c_config_exist_in_cz;
1784
1785 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1786 aso_debug_pub.add('Get_config_details: A previous version exist for this configuration so deleting it from CZ');
1787 END IF;
1788
1789 ASO_CFG_INT.DELETE_CONFIGURATION_AUTO( P_API_VERSION_NUMBER => 1.0,
1790 P_INIT_MSG_LIST => FND_API.G_FALSE,
1791 P_CONFIG_HDR_ID => p_config_hdr_id,
1795 X_MSG_DATA => x_msg_data);
1792 P_CONFIG_REV_NBR => p_config_rev_nbr,
1793 X_RETURN_STATUS => lx_return_status,
1794 X_MSG_COUNT => x_msg_count,
1796
1797 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1798 aso_debug_pub.add('After call to ASO_CFG_INT.DELETE_CONFIGURATION: x_Return_Status: ' || lx_Return_Status);
1799 END IF;
1800
1801 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1802
1803 x_return_status := lx_return_status;
1804 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1805 FND_MESSAGE.Set_Name('ASO', 'ASO_DELETE');
1806 FND_MESSAGE.Set_Token('OBJECT', 'CONFIGURATION', FALSE);
1807 FND_MSG_PUB.ADD;
1808 END IF;
1809
1810 RAISE FND_API.G_EXC_ERROR;
1811
1812 END IF;
1813
1814 else
1815 close c_config_exist_in_cz;
1816 end if;
1817
1818 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1819 P_API_NAME => L_API_NAME
1820 ,P_PKG_NAME => G_PKG_NAME
1821 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1822 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
1823 ,X_MSG_COUNT => X_MSG_COUNT
1824 ,X_MSG_DATA => X_MSG_DATA
1825 ,X_RETURN_STATUS => X_RETURN_STATUS);
1826
1827 WHEN OTHERS THEN
1828
1829 open c_config_exist_in_cz(p_config_hdr_id, p_config_rev_nbr);
1830 fetch c_config_exist_in_cz into l_old_config_hdr_id;
1831
1832 if c_config_exist_in_cz%found then
1833
1834 close c_config_exist_in_cz;
1835
1836 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1837 aso_debug_pub.add('Get_config_details: A previous version exist for this configuration so deleting it from CZ');
1838 END IF;
1839
1840 ASO_CFG_INT.DELETE_CONFIGURATION_AUTO( P_API_VERSION_NUMBER => 1.0,
1841 P_INIT_MSG_LIST => FND_API.G_FALSE,
1842 P_CONFIG_HDR_ID => p_config_hdr_id,
1843 P_CONFIG_REV_NBR => p_config_rev_nbr,
1844 X_RETURN_STATUS => lx_return_status,
1845 X_MSG_COUNT => x_msg_count,
1846 X_MSG_DATA => x_msg_data);
1847
1848 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1849 aso_debug_pub.add('After call to ASO_CFG_INT.DELETE_CONFIGURATION: x_Return_Status: ' || lx_Return_Status);
1850 END IF;
1851
1852 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1853
1854 x_return_status := lx_return_status;
1855 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1856 FND_MESSAGE.Set_Name('ASO', 'ASO_DELETE');
1857 FND_MESSAGE.Set_Token('OBJECT', 'CONFIGURATION', FALSE);
1861 RAISE FND_API.G_EXC_ERROR;
1858 FND_MSG_PUB.ADD;
1859 END IF;
1860
1862
1863 END IF;
1864
1865 else
1866 close c_config_exist_in_cz;
1867 end if;
1868
1869 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1870 P_API_NAME => L_API_NAME
1871 ,P_PKG_NAME => G_PKG_NAME
1872 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1873 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
1874 ,P_SQLCODE => SQLCODE
1875 ,P_SQLERRM => SQLERRM
1876 ,X_MSG_COUNT => X_MSG_COUNT
1877 ,X_MSG_DATA => X_MSG_DATA
1878 ,X_RETURN_STATUS => X_RETURN_STATUS);
1879
1880 END Get_Config_Details;
1881
1882
1883 -- This pricing_callback procedure needs the followings things
1884 -- The cz_prc_callback_util.root_bom_config_item_id function will always return the config_item_id of the
1885 -- root model no matter what the price_type is.
1886 PROCEDURE Pricing_Callback( p_config_session_key IN VARCHAR2,
1887 p_price_type IN VARCHAR2,
1888 x_total_price OUT NOCOPY /* file.sql.39 change */ NUMBER )
1889 IS
1890
1891 Cursor c_options is
1892 Select item_key, cz_atp_callback_util.inv_item_id_from_item_key(item_key) item_id,
1893 quantity, uom_code,substr(item_key, 1,instr( item_key, ':' ,1)-1) component_code,
1894 config_item_id
1895 from cz_pricing_structures
1896 Where configurator_session_key = p_config_session_key
1897 and item_key_type = 1;
1898
1899 Cursor c_quote_hdr_id ( p_quote_line_id NUMBER ) is
1900 select a.quote_header_id, a.price_list_id, b.org_id
1901 from aso_quote_lines_all a, aso_quote_headers_all b
1902 where a.quote_header_id = b.quote_header_id
1903 and a.quote_line_id = p_quote_line_id;
1904
1905 Cursor c_config_header_id ( p_quote_line_id NUMBER ) is
1906 Select config_header_id
1907 from aso_quote_line_details
1908 where quote_line_id = p_quote_line_id;
1909
1910 Cursor c_pricelist_id ( p_config_item_id NUMBER, p_config_header_id NUMBER ) is
1911 Select qtl.price_list_id, qtl.quote_line_id
1912 from aso_quote_lines_all qtl,
1913 aso_quote_line_details qtl_dtl
1914 where qtl.quote_line_id = qtl_dtl.quote_line_id
1915 and qtl_dtl.config_item_id = p_config_item_id
1916 and qtl_dtl.config_header_id = p_config_header_id
1917 and ref_line_id is not null;
1918
1919 Cursor c_config_line(p_quote_line_id NUMBER, p_config_header_id NUMBER) is
1920 Select quote_line_id
1921 from aso_quote_line_details
1922 where quote_line_id = p_quote_line_id
1923 and config_header_id = p_config_header_id
1924 and ref_line_id is not null;
1925
1926 Cursor c_get_pricing_structure(p_session_key VARCHAR2) is
1927 select list_price,selling_price,config_item_id
1928 from cz_pricing_structures
1929 where configurator_session_key = p_session_key;
1930
1931 Cursor c_charge_periodicity_code(p_inventory_item_id number, p_organization_id number) is
1932 select charge_periodicity_code
1933 from mtl_system_items_b
1934 where inventory_item_id = p_inventory_item_id
1935 and organization_id = p_organization_id;
1936
1937 l_pricing_control_rec ASO_PRICING_INT.Pricing_Control_rec_Type;
1938 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1939 l_hd_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1940 l_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1941 l_hd_price_attr_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1942 l_hd_price_attr_rec ASO_QUOTE_PUB.Price_Attributes_Rec_Type;
1943 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
1944 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1945 l_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type;
1946 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1947 l_l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1948 l_ln_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1949 l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1950 l_l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1951 l_ln_price_attr_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1952 l_l_ln_price_attr_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1953 l_ln_price_attr_rec ASO_QUOTE_PUB.Price_Attributes_rec_Type;
1954 l_price_adj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1955 l_l_price_adj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1956 l_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
1957
1958 lx_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1959 lx_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1960 lx_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1961 lx_price_adj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1962 lx_price_adj_attr_tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
1963 lx_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
1964
1965 lx_return_status VARCHAR2(1);
1966 lx_msg_count NUMBER;
1967 lx_msg_data VARCHAR2(2000);
1968
1969 l_model_quote_line_id NUMBER;
1970 l_quote_line_id NUMBER;
1971 l_c_quote_line_id NUMBER;
1972 l_quote_header_id NUMBER;
1973 l_model_price_list_id NUMBER;
1974 i NUMBER;
1975 record_count1 NUMBER := 0;
1976 l_line_price_list_id NUMBER;
1977 l_file VARCHAR2(200);
1978 l_mymsg VARCHAR2(2000);
1979 l_root_model_config_item_id NUMBER;
1980 l_config_header_id NUMBER;
1981 l_count NUMBER := 0;
1982 l_org_id NUMBER;
1983 l_master_organization_id NUMBER;
1984
1985 -- added by 6661597
1986 l_validated_quantity NUMBER;
1987 l_primary_quantity NUMBER;
1988 l_qty_return_status VARCHAR2(1);
1989 p_item_id NUMBER;
1990 p_organization_id NUMBER;
1991 p_uom_code VARCHAR2(50);
1992 p_input_quantity NUMBER;
1993 x_output_quantity NUMBER;
1994 x_ret_Stat VARCHAR2(50);
1995 -- end 6661597
1996
1997
1998 Begin
1999 /*
2000 aso_debug_pub.g_debug_flag := 'Y';
2001 aso_debug_pub.SetDebugLevel(10);
2002 aso_debug_pub.Initialize;
2003 l_file := ASO_DEBUG_PUB.Set_Debug_Mode('FILE');
2004 aso_debug_pub.debug_on;
2005 */
2006
2007 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2008
2009 aso_debug_pub.add('ASO_CFG_INT: PRICING CALLBACK: Start %%%%%%%%%%%%%%%%%%%%' , 1, 'Y' );
2010 aso_debug_pub.add('ASO_CFG_INT: PRICING CALLBACK: p_config_session_key: '|| p_config_session_key);
2011 aso_debug_pub.add('ASO_CFG_INT: PRICING CALLBACK: p_price_type: '|| p_price_type);
2012
2013 END IF;
2014
2015 -- Store the derived model item quote_line_id from the p_config_session_key for subsequent use
2016
2017 l_model_quote_line_id := to_number( substr(p_config_session_key, 1,instr( p_config_session_key, '-') - 1));
2018
2019 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2020 aso_debug_pub.add('PRICING CALLBACK: l_model_quote_line_id: ' || l_model_quote_line_id);
2021 END IF;
2022
2023 OPEN c_quote_hdr_id( l_model_quote_line_id );
2024 FETCH c_quote_hdr_id into l_quote_header_id, l_model_price_list_id, l_org_id;
2025
2026 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2027 aso_debug_pub.add('PRICING CALLBACK: l_quote_header_id: ' || l_quote_header_id);
2028 aso_debug_pub.add('PRICING CALLBACK: l_model_price_list_id: ' || l_model_price_list_id);
2029 aso_debug_pub.add('PRICING CALLBACK: l_org_id: ' || l_org_id);
2030 END IF;
2031
2032 IF c_quote_hdr_id%FOUND THEN
2033
2034 l_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row ( l_quote_header_id );
2035
2036 -- The following function returns all other rows of the quote which do not belong to
2037 -- this configuration plus the model line itself
2038
2039 l_qte_line_tbl := Query_Qte_Line_Rows( l_quote_header_id,l_model_quote_line_id );
2040
2041 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2042 aso_debug_pub.add('PRICING CALLBACK: After call to Query_Qte_Line_Rows');
2043 aso_debug_pub.add('PRICING CALLBACK: l_qte_line_tbl.count: '|| l_qte_line_tbl.count);
2044 END IF;
2045
2046 l_master_organization_id := oe_sys_parameters.value(param_name => 'MASTER_ORGANIZATION_ID', p_org_id => l_org_id);
2047
2048 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2049 aso_debug_pub.add('PRICING CALLBACK: l_master_organization_id: ' || l_master_organization_id);
2050 END IF;
2051
2052 ELSE
2053
2054 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2055 aso_debug_pub.add('ASO_CFG_INT: PRICING CALLBACK: c_quote_hdr_id NOT FOUND.');
2056 END IF;
2057
2058 END IF;
2059
2060 CLOSE c_quote_hdr_id;
2061
2062 --Get the config_header_id of the model line. The config_header_id will be null in
2063 --case it is first time configuration
2064
2065 OPEN c_config_header_id( l_model_quote_line_id );
2066 FETCH c_config_header_id into l_config_header_id;
2067 CLOSE c_config_header_id;
2068
2069 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2070 aso_debug_pub.add('ASO_CFG_INT: PRICING CALLBACK: l_config_header_id: ' || l_config_header_id);
2071 END IF;
2072
2073 IF p_price_type = cz_prc_callback_util.g_prc_type_list THEN
2074
2075 FOR row IN C_options LOOP
2076
2077 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2078
2079 aso_debug_pub.add( 'PRICING CALLBACK: item_key: ' || row.item_key);
2080 aso_debug_pub.add( 'PRICING CALLBACK: (inv) item_id: ' || row.item_id);
2084 aso_debug_pub.add( 'PRICING CALLBACK: config_item_id: ' || row.config_item_id);
2081 aso_debug_pub.add( 'PRICING CALLBACK: quantity: ' || row.quantity);
2082 aso_debug_pub.add( 'PRICING CALLBACK: uom_code: ' || row.uom_code);
2083 aso_debug_pub.add( 'PRICING CALLBACK: component_code: ' || row.component_code);
2085
2086 END IF;
2087
2088 record_count1 := record_count1 + 1;
2089
2090 -- 6661597
2091 p_item_id:=row.item_id;
2092 p_organization_id:=l_master_organization_id;
2093 p_uom_code:=row.uom_code;
2094 p_input_quantity:=row.quantity;
2095 x_output_quantity:=FND_API.G_MISS_NUM;
2096
2097 -- inv quantity validation 6661597
2098 IF (p_input_quantity is not null AND p_input_quantity <> FND_API.G_MISS_NUM) THEN
2099 inv_decimals_pub.validate_quantity(
2100 p_item_id => p_item_id ,
2101 p_organization_id => p_organization_id ,
2102 p_input_quantity => p_input_quantity,
2103 p_uom_code => p_uom_code,
2104 x_output_quantity => l_validated_quantity,
2105 x_primary_quantity => l_primary_quantity,
2106 x_return_status => x_ret_Stat);
2107
2108 if x_ret_Stat = 'E' THEN
2109
2110 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2111 FND_MESSAGE.Set_Name('ASO', 'ASO_ERR_FRACTIONAL_QUANTITY');
2112 FND_MSG_PUB.ADD;
2113 END IF;
2114 RAISE FND_API.G_EXC_ERROR;
2115 elsif x_ret_Stat = 'W' then
2116 x_output_quantity:= l_validated_quantity;
2117 else
2118 x_output_quantity:=p_input_quantity;
2119 end if;
2120 END IF; -- quantity not null
2121
2122
2123 l_qte_line_tbl(record_count1).inventory_item_id := row.item_id;
2124 l_qte_line_tbl(record_count1).quantity := x_output_quantity;--row.quantity;
2125 l_qte_line_tbl(record_count1).uom_code := row.uom_code;
2126 l_qte_line_dtl_tbl(record_count1).config_item_id := row.config_item_id;
2127
2128 open c_charge_periodicity_code(row.item_id, l_master_organization_id);
2129 fetch c_charge_periodicity_code into l_qte_line_tbl(record_count1).charge_periodicity_code;
2130 close c_charge_periodicity_code;
2131
2132 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2133 aso_debug_pub.add('PRICING CALLBACK: l_qte_line_tbl('|| record_count1 ||').charge_periodicity_code: '|| l_qte_line_tbl(record_count1).charge_periodicity_code);
2134 End if;
2135
2136 IF l_config_header_id IS NOT NULL THEN
2137
2138 OPEN c_pricelist_id(row.config_item_id, l_config_header_id);
2139 FETCH c_pricelist_id into l_line_price_list_id, l_quote_line_id;
2140
2141 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2142 aso_debug_pub.add('PRICING CALLBACK: l_line_price_list_id: ' || l_line_price_list_id);
2143 aso_debug_pub.add('PRICING CALLBACK: l_quote_line_id: ' || l_quote_line_id);
2144 END IF;
2145
2146 IF c_pricelist_id%FOUND THEN
2147
2148 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2149 aso_debug_pub.add('PRICING CALLBACK: Inside c_pricelist_id cursor FOUND');
2150 END IF;
2151
2152 IF l_line_price_list_id IS NOT NULL THEN
2153 l_qte_line_tbl(record_count1).price_list_id := l_line_price_list_id;
2154 ELSE
2155 l_qte_line_tbl(record_count1).price_list_id := l_model_price_list_id;
2156 END IF;
2157
2158 l_qte_line_tbl(record_count1).quote_line_id := l_quote_line_id;
2159
2160 ELSE
2161
2162 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2163 aso_debug_pub.add('PRICING CALLBACK: Inside ELSE c_pricelist_id cursor FOUND');
2164 END IF;
2165
2166 l_qte_line_tbl(record_count1).quote_line_id := 0;
2167 l_qte_line_tbl(record_count1).price_list_id := l_model_price_list_id;
2168
2169 END IF;
2170
2171 CLOSE c_pricelist_id;
2172
2173 ELSE
2174
2175 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2176 aso_debug_pub.add('PRICING CALLBACK: Inside ELSE l_config_header_id IS NOT NULL');
2177 END IF;
2178
2179 l_qte_line_tbl(record_count1).quote_line_id := 0;
2180 l_qte_line_tbl(record_count1).price_list_id := l_model_price_list_id;
2181
2182 END IF;
2183
2184 END LOOP;
2185
2186 l_pricing_control_rec.request_type := 'ASO';
2187 l_pricing_control_rec.pricing_event := 'PRICE';
2188 l_pricing_control_rec.price_mode := 'QUOTE_LINE';
2189
2190 ELSE
2191
2192 -- Get the config_item_id of the root model
2193 l_root_model_config_item_id := cz_prc_callback_util.root_bom_config_item_id(p_config_session_key);
2194
2195 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2196 aso_debug_pub.add( 'ASO_CFG_INT: PRICING CALLBACK: l_root_model_config_item_id: ' || l_root_model_config_item_id);
2197 END IF;
2198
2199 record_count1 := l_qte_line_tbl.count;
2200 l_count := l_qte_line_tbl.count;
2201
2202 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2203 aso_debug_pub.add('ASO_CFG_INT: PRICING CALLBACK: l_count: ' || l_count);
2204 END IF;
2205
2206 FOR row IN C_options LOOP
2207
2208 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2209
2210 aso_debug_pub.add( 'PRICING CALLBACK: item_key: ' || row.item_key);
2211 aso_debug_pub.add( 'PRICING CALLBACK: (inv) item_id: ' || row.item_id);
2212 aso_debug_pub.add( 'PRICING CALLBACK: quantity: ' || row.quantity);
2213 aso_debug_pub.add( 'PRICING CALLBACK: uom_code: ' || row.uom_code);
2214 aso_debug_pub.add( 'PRICING CALLBACK: component_code: ' || row.component_code);
2215 aso_debug_pub.add( 'PRICING CALLBACK: config_item_id: ' || row.config_item_id);
2216
2217 END IF;
2218
2219
2220 -- 6661597
2221 p_item_id:=row.item_id;
2222 p_organization_id:=l_master_organization_id;
2223 p_uom_code:=row.uom_code;
2224 p_input_quantity:=row.quantity;
2225 x_output_quantity:=FND_API.G_MISS_NUM;
2226
2230 p_item_id => p_item_id ,
2227 -- inv quantity validation 6661597
2228 IF (p_input_quantity is not null AND p_input_quantity <> FND_API.G_MISS_NUM) THEN
2229 inv_decimals_pub.validate_quantity(
2231 p_organization_id => p_organization_id ,
2232 p_input_quantity => p_input_quantity,
2233 p_uom_code => p_uom_code,
2234 x_output_quantity => l_validated_quantity,
2235 x_primary_quantity => l_primary_quantity,
2236 x_return_status => x_ret_Stat);
2237
2238 if x_ret_Stat = 'E' THEN
2239
2240 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2241 FND_MESSAGE.Set_Name('ASO', 'ASO_ERR_FRACTIONAL_QUANTITY');
2242 FND_MSG_PUB.ADD;
2243 END IF;
2244 RAISE FND_API.G_EXC_ERROR;
2245 elsif x_ret_Stat = 'W' then
2246 x_output_quantity:= l_validated_quantity;
2247 else
2248 x_output_quantity:=p_input_quantity;
2249 end if;
2250 END IF; -- quantity not null
2251
2252
2253
2254
2255 IF row.config_item_id <> l_root_model_config_item_id THEN
2256
2257 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2258 aso_debug_pub.add('PRICING CALLBACK: It is a child line');
2259 END IF;
2260
2261 record_count1 := record_count1 + 1;
2262
2263 l_qte_line_tbl(record_count1).inventory_item_id := row.item_id;
2264 l_qte_line_tbl(record_count1).quantity := x_output_quantity;--row.quantity;
2265 l_qte_line_tbl(record_count1).uom_code := row.uom_code;
2266 l_qte_line_dtl_tbl(record_count1).config_item_id := row.config_item_id;
2267
2268 IF l_config_header_id IS NOT NULL THEN
2269
2270 OPEN c_pricelist_id(row.config_item_id, l_config_header_id);
2271 FETCH c_pricelist_id into l_line_price_list_id, l_quote_line_id;
2272
2273 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2274 aso_debug_pub.add('PRICING CALLBACK: l_line_price_list_id: ' || l_line_price_list_id);
2275 aso_debug_pub.add('PRICING CALLBACK: l_quote_line_id: ' || l_quote_line_id);
2276 END IF;
2277
2278 IF c_pricelist_id%FOUND THEN
2279
2280 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2281 aso_debug_pub.add('PRICING CALLBACK: Inside c_pricelist_id cursor FOUND');
2282 END IF;
2283
2284 IF l_line_price_list_id IS NOT NULL THEN
2285 l_qte_line_tbl(record_count1).price_list_id := l_line_price_list_id;
2286 ELSE
2287 l_qte_line_tbl(record_count1).price_list_id := l_model_price_list_id;
2288 END IF;
2289 l_qte_line_tbl(record_count1).quote_line_id := l_quote_line_id;
2290
2291 ELSE
2292
2293 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2294 aso_debug_pub.add('PRICING CALLBACK: Inside ELSE c_pricelist_id cursor FOUND');
2295 END IF;
2296
2297 l_qte_line_tbl(record_count1).quote_line_id := 0;
2298 l_qte_line_tbl(record_count1).price_list_id := l_model_price_list_id;
2299
2300 END IF;
2304 ELSE
2301
2302 CLOSE c_pricelist_id;
2303
2305
2306 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2307 aso_debug_pub.add('PRICING CALLBACK: Inside ELSE l_config_header_id IS NOT NULL');
2308 END IF;
2309
2310 l_qte_line_tbl(record_count1).quote_line_id := 0;
2311 l_qte_line_tbl(record_count1).price_list_id := l_model_price_list_id;
2312
2313 END IF;
2314
2315 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2316 aso_debug_pub.add('PRICING CALLBACK: It is a child line: After populating the child line information');
2317 END IF;
2318
2319 ELSE
2320
2321 record_count1 := record_count1 + 1;
2322 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2323 aso_debug_pub.add('PRICING CALLBACK: ELSE cond of row.config_item_id <> l_root_model_config_item_id: It is model line');
2324 END IF;
2325
2326 l_qte_line_tbl(record_count1).inventory_item_id := row.item_id;
2327 l_qte_line_tbl(record_count1).quantity := x_output_quantity;--row.quantity;
2328 l_qte_line_tbl(record_count1).uom_code := row.uom_code;
2329 l_qte_line_tbl(record_count1).price_list_id := l_model_price_list_id;
2330 l_qte_line_tbl(record_count1).quote_line_id := l_model_quote_line_id;
2331 l_qte_line_dtl_tbl(record_count1).config_item_id := row.config_item_id;
2332
2333 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2334 aso_debug_pub.add('PRICING CALLBACK: It is model line: After populating the model information');
2335 END IF;
2336
2337 END IF;
2338
2339 open c_charge_periodicity_code(row.item_id, l_master_organization_id);
2340 fetch c_charge_periodicity_code into l_qte_line_tbl(record_count1).charge_periodicity_code;
2341 close c_charge_periodicity_code;
2342
2343 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2344 aso_debug_pub.add('PRICING CALLBACK: l_qte_line_tbl('|| record_count1 ||').charge_periodicity_code: '|| l_qte_line_tbl(record_count1).charge_periodicity_code);
2345 End if;
2346
2347 END LOOP;
2348
2349 l_pricing_control_rec.request_type := 'ASO';
2350 l_pricing_control_rec.pricing_event := 'BATCH';
2351 l_pricing_control_rec.price_mode := 'ENTIRE_QUOTE';
2352
2353 END IF;
2354
2355 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2356
2357 aso_debug_pub.add('PRICING CALLBACK: After C_options cursor loop: l_qte_line_tbl.count: '||l_qte_line_tbl.count);
2358
2359 FOR i IN 1..l_qte_line_tbl.count LOOP
2360
2361 aso_debug_pub.add('PRICING CALLBACK: l_qte_line_tbl('||i||').quote_line_id: '|| l_qte_line_tbl(i).quote_line_id);
2362 aso_debug_pub.add('PRICING CALLBACK: l_qte_line_tbl('||i||').inventory_item_id: '|| l_qte_line_tbl(i).inventory_item_id);
2366 aso_debug_pub.add('PRICING CALLBACK: l_qte_line_tbl('||i||').charge_periodicity_code: '|| l_qte_line_tbl(i).charge_periodicity_code);
2363 aso_debug_pub.add('PRICING CALLBACK: l_qte_line_tbl('||i||').quantity: '|| l_qte_line_tbl(i).quantity);
2364 aso_debug_pub.add('PRICING CALLBACK: l_qte_line_tbl('||i||').uom_code: '|| l_qte_line_tbl(i).uom_code);
2365 aso_debug_pub.add('PRICING CALLBACK: l_qte_line_tbl('||i||').price_list_id: '|| l_qte_line_tbl(i).price_list_id);
2367 --aso_debug_pub.add('PRICING CALLBACK: l_qte_line_dtl_tbl('||i||').config_item_id:'|| l_qte_line_dtl_tbl(i).config_item_id);
2368
2369 END LOOP;
2370
2371 END IF;
2372
2373 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2374
2375 aso_debug_pub.add('PRICING CALLBACK: After C_options cursor loop: l_qte_line_dtl_tbl.count: '||l_qte_line_dtl_tbl.count);
2376
2377 FOR i IN 1..l_qte_line_dtl_tbl.count LOOP
2378
2379 aso_debug_pub.add('PRICING CALLBACK: l_qte_line_dtl_tbl('||i||').config_item_id:'|| l_qte_line_dtl_tbl(i).config_item_id);
2380
2381 END LOOP;
2382
2383 END IF;
2384
2385 --Set the control record parameter values
2386
2387 l_pricing_control_rec.price_config_flag := 'Y';
2388
2389 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2390
2391 aso_debug_pub.add('l_pricing_control_rec.request_type: '||l_pricing_control_rec.request_type);
2392 aso_debug_pub.add('l_pricing_control_rec.pricing_event: '||l_pricing_control_rec.pricing_event);
2393 aso_debug_pub.add('l_pricing_control_rec.price_mode: '||l_pricing_control_rec.price_mode);
2394 aso_debug_pub.add('l_pricing_control_rec.price_config_flag: '||l_pricing_control_rec.price_config_flag);
2395
2396 aso_debug_pub.add('ASO_CFG_INT: PRICING CALLBACK: Before call to ASO_PRICING_INT.Pricing_Order');
2397
2398 END IF;
2399
2400 ASO_PRICING_INT.Pricing_Order(
2401 P_Api_Version_Number => 1.0,
2402 P_Init_Msg_List => FND_API.G_TRUE,
2403 P_Commit => FND_API.G_FALSE,
2404 p_control_rec => l_pricing_control_rec,
2405 p_qte_header_rec => l_qte_header_rec,
2406 p_hd_shipment_rec => l_hd_shipment_rec,
2407 p_hd_price_attr_tbl => l_hd_price_attr_tbl,
2408 p_qte_line_tbl => l_qte_line_tbl,
2409 p_line_rltship_tbl => l_line_rltship_tbl,
2410 p_qte_line_dtl_tbl => l_l_qte_line_dtl_tbl,
2411 p_ln_shipment_tbl => l_l_ln_shipment_tbl,
2412 p_ln_price_attr_tbl => l_l_ln_price_attr_tbl,
2413 --p_price_adj_tbl => l_l_price_adj_tbl,
2414 x_qte_header_rec => lx_qte_header_rec,
2415 x_qte_line_tbl => lx_qte_line_tbl,
2416 x_qte_line_dtl_tbl => lx_qte_line_dtl_tbl,
2417 x_price_adj_tbl => lx_price_adj_tbl,
2418 x_price_adj_attr_tbl => lx_price_adj_attr_tbl,
2419 x_price_adj_rltship_tbl => lx_price_adj_rltship_tbl,
2420 x_return_status => lx_return_status ,
2421 x_msg_count => lx_msg_count,
2422 x_msg_data => lx_msg_data
2423 );
2424
2425 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2426
2427 aso_debug_pub.add('PRICING CALLBACK: After call to ASO_PRICING_INT.Pricing_Order');
2428 aso_debug_pub.add('PRICING CALLBACK: lx_return_status: '|| lx_return_status);
2432
2429 aso_debug_pub.add('PRICING CALLBACK: lx_msg_count: '|| lx_msg_count);
2430 aso_debug_pub.add('PRICING CALLBACK: lx_msg_data: '|| lx_msg_data);
2431 aso_debug_pub.add('PRICING CALLBACK: lx_qte_line_tbl.count: '|| lx_qte_line_tbl.count);
2433 END IF;
2434
2435 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2436
2437 fnd_msg_pub.count_and_get( p_encoded => 'F',
2438 p_count => lx_msg_count,
2439 p_data => lx_msg_data);
2440
2441 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2442
2443 aso_debug_pub.add('PRICING CALLBACK: After call to fnd_msg_pub.count_and_get');
2444 aso_debug_pub.add('PRICING CALLBACK: lx_msg_count: '|| lx_msg_count);
2445 aso_debug_pub.add('PRICING CALLBACK: lx_msg_data: '|| lx_msg_data);
2446
2447 END IF;
2448
2449 FOR k IN 1 .. lx_msg_count LOOP
2450
2451 lx_msg_data := fnd_msg_pub.get( p_msg_index => k,
2452 p_encoded => 'F');
2453
2454 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2455 aso_debug_pub.add('PRICING CALLBACK: Inside Loop fnd_msg_pub.get: lx_msg_data: ' ||lx_msg_data);
2456 END IF;
2457
2458 l_mymsg := l_mymsg || ' ' || lx_msg_data;
2459
2460 END LOOP;
2461
2462 END IF;
2463
2464 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2465 aso_debug_pub.add('ASO_CFG_INT: PRICING CALLBACK: l_mymsg: ' || l_mymsg);
2466 END IF;
2467
2468 -- set the error message in the model line msg_data field of cz_pricing_structure
2469
2470 IF lx_return_status <> FND_API.G_RET_STS_SUCCESS AND p_price_type <> 'LIST' THEN
2471
2472 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2473 aso_debug_pub.add('PRICING CALLBACK: Inside IF condition lx_return_status <> FND_API.G_RET_STS_SUCCESS');
2474 END IF;
2475
2476 UPDATE CZ_PRICING_STRUCTURES
2477 SET MSG_DATA = l_mymsg
2478 WHERE configurator_session_key = p_config_session_key;
2479 --AND config_item_id = l_root_model_config_item_id;
2480
2481 END IF;
2482
2483 -- Assuming that the ASO_PRICING_INT.Pricing_Order will return the same number of lines send as input.
2484 -- That means the l_qte_line_tbl and lx_qte_line_tbl will have same count and order else the update of
2485 -- in cz_pricing_structure will have incorrect result.
2486
2487 FOR i IN l_count+1..lx_qte_line_tbl.count LOOP
2488
2489
2490 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2491
2492 aso_debug_pub.add('PRICING CALLBACK: Inside Loop IF quote_line_id = 0');
2493 aso_debug_pub.add('PRICING CALLBACK: lx_qte_line_tbl('||i||').quote_line_id: '|| lx_qte_line_tbl(i).quote_line_id);
2494 aso_debug_pub.add('PRICING CALLBACK: lx_qte_line_tbl('||i||').line_list_price: '|| lx_qte_line_tbl(i).line_list_price);
2495 aso_debug_pub.add('PRICING CALLBACK: lx_qte_line_tbl('||i||').line_quote_price: '|| lx_qte_line_tbl(i).line_quote_price);
2496 aso_debug_pub.add('PRICING CALLBACK: l_count: '|| l_count);
2497 --aso_debug_pub.add('PRICING CALLBACK: l_qte_line_dtl_tbl('|| i - l_count||').config_item_id: '|| l_qte_line_dtl_tbl(i - l_count).config_item_id);
2498 aso_debug_pub.add('PRICING CALLBACK: l_qte_line_dtl_tbl('|| i||').config_item_id: '|| l_qte_line_dtl_tbl(i
2499 - l_count).config_item_id);
2500
2501 END IF;
2502
2503 UPDATE CZ_PRICING_STRUCTURES
2504 SET selling_price = lx_qte_line_tbl(i).LINE_QUOTE_PRICE,
2505 list_price = lx_qte_line_tbl(i).line_list_price
2506 WHERE configurator_session_key = p_config_session_key
2507 AND config_item_id = l_qte_line_dtl_tbl(i - l_count).config_item_id;
2508
2509 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2510 aso_debug_pub.add('PRICING CALLBACK: After Update sql%rowcount: '|| sql%rowcount);
2511 END IF;
2512
2513 END LOOP;
2514
2515 BEGIN
2516
2517 SELECT sum(selling_price) INTO x_total_price
2518 FROM CZ_PRICING_STRUCTURES
2519 WHERE configurator_session_key = p_config_session_key;
2520
2521 EXCEPTION
2522
2523 WHEN OTHERS THEN
2524
2525 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2526 aso_debug_pub.add('ASO_CFG_INT: PRICING CALLBACK: Inside When Others Exception for select sum(selling_price)');
2527 END IF;
2528
2529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2530
2531 END;
2532
2533
2534 -- Writing Data from CZ table to ASO Debug File
2535
2536 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2537
2538 FOR row IN c_get_pricing_structure(p_config_session_key) LOOP
2539
2540 aso_debug_pub.add('PRICING CALLBACK: Data in CZ_PRICING_STRUCTURES table after update to list and selling prices columns.');
2541 aso_debug_pub.add('PRICING CALLBACK: CZ_PRICING_STRUCTURES: config_item_id: ' || row.config_item_id);
2542 aso_debug_pub.add('PRICING CALLBACK: CZ_PRICING_STRUCTURES: list_price: ' || row.list_price);
2543 aso_debug_pub.add('PRICING CALLBACK: CZ_PRICING_STRUCTURES: selling_price: ' || row.selling_price);
2544
2545 END LOOP;
2546
2547 END IF;
2548
2549
2550 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2551 aso_debug_pub.add('ASO_CFG_INT: PRICING CALLBACK End %%%%%%%%%%%%%%%%%%%%', 1, 'Y' );
2552 END IF;
2553
2554
2555 EXCEPTION
2556
2557 WHEN OTHERS THEN
2558
2559 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2560 aso_debug_pub.add('ASO_CFG_INT: PRICING CALLBACK: Inside When Others Exception');
2564 UPDATE CZ_PRICING_STRUCTURES
2561 END IF;
2562
2563 -- set the error message in the model line msg_data field of cz_pricing_structure
2565 SET MSG_DATA = lx_msg_data
2566 WHERE configurator_session_key = p_config_session_key;
2567 --AND config_item_id = l_root_model_config_item_id;
2568
2569 END Pricing_Callback;
2570
2571
2572
2573 -- This function returns all the quote lines which belong to the given quote but not belong
2574 -- to the given configuration plus the root model line of the configuration
2575
2576 FUNCTION Query_Qte_Line_Rows (
2577 P_Qte_Header_Id IN NUMBER,
2578 p_qte_line_id IN NUMBER
2579 ) RETURN ASO_QUOTE_PUB.Qte_Line_Tbl_Type
2580 IS
2581
2582 cursor c_qte_line is
2583 select quote_line_id,
2584 inventory_item_id,
2585 quantity,
2586 uom_code,
2587 price_list_id,
2588 charge_periodicity_code
2589 from aso_quote_lines_all
2590 where quote_header_id = p_qte_header_id
2591 and quote_line_id not in ( select a.quote_line_id
2592 from aso_quote_line_details a
2593 where (a.config_header_id, a.config_revision_num)
2594 = ( select config_header_id, config_revision_num
2595 from aso_quote_line_details
2596 where quote_line_id = p_qte_line_id ))
2597 and quote_line_id <> p_qte_line_id;
2598
2599
2600 l_Qte_Line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
2601 l_Qte_Line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
2602
2603 l_index NUMBER := 0;
2604
2605 BEGIN
2606
2607 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2608 aso_debug_pub.add('ASO_CFG_INT: Query_Qte_Line_Rows: P_Qte_Header_Id: '|| P_Qte_Header_Id);
2609 aso_debug_pub.add('ASO_CFG_INT: Query_Qte_Line_Rows: p_qte_line_id : '|| p_qte_line_id );
2610 END IF;
2611
2612 FOR line_rec IN c_Qte_Line LOOP
2613
2614 l_qte_line_rec.QUOTE_LINE_ID := line_rec.QUOTE_LINE_ID;
2615 l_qte_line_rec.INVENTORY_ITEM_ID := line_rec.INVENTORY_ITEM_ID;
2616 l_qte_line_rec.QUANTITY := line_rec.QUANTITY;
2617 l_qte_line_rec.UOM_CODE := line_rec.UOM_CODE;
2618 l_qte_line_rec.PRICE_LIST_ID := line_rec.PRICE_LIST_ID;
2619 l_qte_line_rec.charge_periodicity_code := line_rec.charge_periodicity_code;
2620
2621 l_index := l_index + 1;
2622
2623 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2624
2625 aso_debug_pub.add('Query_Qte_Line_Rows: line_rec.QUOTE_LINE_ID: '|| line_rec.QUOTE_LINE_ID);
2626 aso_debug_pub.add('Query_Qte_Line_Rows: line_rec.QUANTITY: '|| line_rec.QUANTITY);
2627 aso_debug_pub.add('Query_Qte_Line_Rows: line_rec.UOM_CODE: '|| line_rec.UOM_CODE);
2628 aso_debug_pub.add('Query_Qte_Line_Rows: line_rec.PRICE_LIST_ID: '|| line_rec.PRICE_LIST_ID);
2629 aso_debug_pub.add('Query_Qte_Line_Rows: line_rec.INVENTORY_ITEM_ID: '|| line_rec.INVENTORY_ITEM_ID);
2630 aso_debug_pub.add('Query_Qte_Line_Rows: line_rec.charge_periodicity_code: '|| line_rec.charge_periodicity_code);
2631
2632 END IF;
2633
2634 l_Qte_Line_tbl(l_index) := l_Qte_Line_rec;
2635
2636 END LOOP;
2637
2638 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2639 aso_debug_pub.add('ASO_CFG_INT: Query_Qte_Line_Rows: l_Qte_Line_tbl.count: '|| l_Qte_Line_tbl.count);
2640 END IF;
2641
2642 RETURN l_Qte_Line_tbl;
2643
2644 END Query_Qte_Line_Rows;
2645
2646
2647 /*-------------------------------------------------------------------------
2648 Procedure Name : Create_hdr_xml
2649 Description : creates a batch validation header message.
2650 --------------------------------------------------------------------------*/
2651
2652 PROCEDURE Create_hdr_xml
2653 ( p_model_line_id IN NUMBER,
2654 x_xml_hdr OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2655 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
2656 IS
2657
2658 Cursor C_org_id (p_quote_header_id NUMBER) is
2659 select org_id from aso_quote_headers_all
2660 where quote_header_id = p_quote_header_id;
2661
2662 Cursor c_inv_org_id (p_quote_line_id NUMBER) is
2663 select organization_id from aso_quote_lines_all
2664 where quote_line_id = p_quote_line_id;
2665
2666 TYPE param_name_type IS TABLE OF VARCHAR2(25)
2667 INDEX BY BINARY_INTEGER;
2668
2669 TYPE param_value_type IS TABLE OF VARCHAR2(255)
2670 INDEX BY BINARY_INTEGER;
2671
2672 param_name param_name_type;
2673 param_value param_value_type;
2674
2675 l_rec_index BINARY_INTEGER;
2676
2677 l_model_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
2678 l_model_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
2679 l_org_id NUMBER;
2680
2681 --Configurator specific params
2682 l_calling_application_id VARCHAR2(30);
2683 l_responsibility_id VARCHAR2(30);
2684 l_database_id VARCHAR2(255);
2685 l_read_only VARCHAR2(30) := null;
2686 l_save_config_behavior VARCHAR2(30) := 'new_revision';
2687 l_ui_type VARCHAR2(30) := null;
2688 l_msg_behavior VARCHAR2(30) := 'brief';
2689 l_icx_session_ticket VARCHAR2(200);
2690
2691 --Order Capture specific parameters
2695 l_config_header_id VARCHAR2(30);
2692 l_context_org_id VARCHAR2(30);
2693 l_config_creation_date VARCHAR2(30);
2694 l_inventory_item_id VARCHAR2(30);
2696 l_config_rev_nbr VARCHAR2(30);
2697 l_model_quantity VARCHAR2(30);
2698 l_count NUMBER;
2699 --l_validation_org_id NUMBER;
2700
2701 --message related
2702 l_xml_hdr VARCHAR2(2000):= '<initialize>';
2703 l_dummy VARCHAR2(500) := NULL;
2704
2705 -- CZ ER 3177722
2706 l_config_effective_date_prof VARCHAR2(1):=nvl(fnd_profile.value('ASO_CONFIG_EFFECTIVE_DATE'),'X');
2707 l_current_date VARCHAR2(30);
2708 x_config_effective_date DATE;
2709 x_config_lookup_date DATE;
2710
2711 BEGIN
2712 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2713 aso_debug_pub.add('Create_hdr_xml Begins.', 1, 'Y');
2714 END IF;
2715
2716 --Initialize API return status to SUCCESS
2717 x_return_status := FND_API.G_RET_STS_SUCCESS;
2718
2719 l_model_line_rec := aso_utility_pvt.Query_Qte_Line_Row( P_Qte_Line_Id => p_model_line_id );
2720
2721 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2722 aso_debug_pub.add('Create_hdr_xml: After call to aso_utility_pvt.Query_Qte_Line_Row');
2723 END IF;
2724
2725 l_model_line_dtl_tbl := aso_utility_pvt.Query_Line_Dtl_Rows( P_Qte_Line_Id => p_model_line_id );
2726
2727 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2728 aso_debug_pub.add('Create_hdr_xml: After call to aso_utility_pvt.Query_Line_Dtl_Rows');
2729 END IF;
2730
2731 /* Fix for bug 3998564 */
2732 --OPEN C_org_id( l_model_line_rec.quote_header_id);
2733 --FETCH C_org_id INTO l_org_id;
2734 --CLOSE C_org_id;
2735 OPEN c_inv_org_id( l_model_line_rec.quote_line_id);
2736 FETCH c_inv_org_id INTO l_org_id;
2737 CLOSE c_inv_org_id;
2738 /* End of fix for bug 3998564 */
2739
2740 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2741 aso_debug_pub.add('Create_hdr_xml: After C_org_id cursor: l_org_id: '|| l_org_id, 1, 'N');
2742 END IF;
2743
2744 IF l_org_id IS NULL THEN
2745
2746 --Commented Code Start Yogeshwar(MOAC)
2747 /* IF SUBSTRB(USERENV('CLIENT_INFO'),1 ,1) = ' ' THEN
2748 l_org_id := NULL;
2749 ELSE
2750 l_org_id := TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1,10));
2751 END IF;
2752 */
2753 --Commented Code End Yogeshwar (MOAC)
2754
2755 L_org_id := l_model_line_rec.org_id; --New Code Yogeshwar MOAC
2756
2757 END IF;
2758
2759 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2760 aso_debug_pub.add('Create_hdr_xml: After Defaulting from client info. l_org_id: '|| l_org_id);
2761 END IF;
2762
2763 --Set the values from model_line_rec, model_line_dtl_tbl and org_id
2764 l_context_org_id := to_char(l_org_id);
2765 l_inventory_item_id := to_char(l_model_line_rec.inventory_item_id);
2766 l_config_header_id := to_char(l_model_line_dtl_tbl(1).config_header_id);
2767 l_config_rev_nbr := to_char(l_model_line_dtl_tbl(1).config_revision_num);
2768 l_config_creation_date := to_char(l_model_line_rec.creation_date,'MM-DD-YYYY-HH24-MI-SS');
2769 l_model_quantity := to_char(l_model_line_rec.quantity);
2770 l_current_date:= to_char(sysdate,'MM-DD-YYYY-HH24-MI-SS');
2771
2772 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2773
2774 aso_debug_pub.add('Create_hdr_xml: l_context_org_id :' || l_context_org_id);
2775 aso_debug_pub.add('Create_hdr_xml: l_inventory_item_id :' || l_inventory_item_id);
2776 aso_debug_pub.add('Create_hdr_xml: l_config_header_id :' || l_config_header_id);
2777 aso_debug_pub.add('Create_hdr_xml: l_config_rev_nbr :' || l_config_rev_nbr);
2778 aso_debug_pub.add('Create_hdr_xml: l_config_creation_date:' || l_config_creation_date);
2779 aso_debug_pub.add('Create_hdr_xml: l_model_quantity :' || l_model_quantity);
2780 aso_debug_pub.add('Create_hdr_xml: l_current_date :' || l_current_date);
2781
2782 END IF;
2783
2784 -- Set values from profiles and env. variables.
2785 l_calling_application_id := fnd_global.resp_appl_id;
2786 l_responsibility_id := fnd_global.resp_id;
2787 l_database_id := fnd_web_config.database_id;
2788 l_icx_session_ticket := cz_cf_api.icx_session_ticket;
2789
2790 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2791
2792 aso_debug_pub.add('Create_hdr_xml: l_calling_application_id:' || l_calling_application_id);
2793 aso_debug_pub.add('Create_hdr_xml: l_responsibility_id :' || l_responsibility_id);
2794 aso_debug_pub.add('Create_hdr_xml: l_database_id :' || l_database_id);
2795 aso_debug_pub.add('Create_hdr_xml: l_icx_session_ticket :' || l_icx_session_ticket);
2796 aso_debug_pub.add('Create_hdr_xml: profile value:'|| l_config_effective_date_prof);
2797
2798 END IF;
2799
2800 -- set param_names
2801 param_name(1) := 'database_id';
2802 param_name(2) := 'context_org_id';
2803 param_name(3) := 'config_creation_date';
2804 param_name(4) := 'calling_application_id';
2805 param_name(5) := 'responsibility_id';
2806 param_name(6) := 'model_id';
2807 param_name(7) := 'config_header_id';
2808 param_name(8) := 'config_rev_nbr';
2809 param_name(9) := 'read_only';
2813 param_name(11) := 'terminate_msg_behavior';
2810 param_name(10) := 'save_config_behavior';
2811 --param_name(11) := 'ui_type';
2812 --param_name(12) := 'validation_org_id';
2814 param_name(12) := 'model_quantity';
2815 param_name(13) := 'icx_session_ticket';
2816
2817
2818 -- Added extra parameters for config effective and lookup date ER 3177722
2819 param_name(14) := 'config_effective_date';
2820 param_name(15) := 'config_model_lookup_date';
2821 l_count := 15;
2822 --l_count := 13;
2823
2824 -- set parameter values
2825
2826 param_value(1) := l_database_id;
2827 param_value(2) := l_context_org_id;
2828 param_value(3) := l_config_creation_date;
2829 param_value(4) := l_calling_application_id;
2830 param_value(5) := l_responsibility_id;
2831 param_value(6) := l_inventory_item_id;
2832 param_value(7) := l_config_header_id;
2833 param_value(8) := l_config_rev_nbr;
2834 param_value(9) := l_read_only;
2835 param_value(10) := l_save_config_behavior;
2836 --param_value(11) := l_ui_type;
2837 --param_value(12) := l_validation_org_id;
2838 param_value(11) := l_msg_behavior;
2839 param_value(12) := l_model_quantity;
2840 param_value(13) := l_icx_session_ticket;
2841
2842 -- Added extra parameters for config effective and lookup date ER 3177722 and setting the value based on new profile ASO : Configuration Effective Date
2843 if l_config_effective_date_prof='C' then -- set to creation date
2844 param_value(14) := l_config_creation_date;
2845 param_value(15) := l_config_creation_date;
2846 elsif l_config_effective_date_prof='S' then -- set to current date
2847 param_value(14) :=to_char(sysdate,'MM-DD-YYYY-HH24-MI-SS');--l_current_date;
2848 param_value(15) :=to_char(sysdate,'MM-DD-YYYY-HH24-MI-SS');--l_current_date;
2849 elsif l_config_effective_date_prof='F' then -- set to callback function Add code for callback function
2850 ASO_QUOTE_HOOK.Get_Model_Configuration_Date
2851 ( p_quote_header_id=>l_model_line_rec.quote_header_id,
2852 P_QUOTE_LINE_ID=> l_model_line_rec.quote_line_id,
2853 X_CONFIG_EFFECTIVE_DATE=> x_config_effective_date,
2854 X_CONFIG_MODEL_LOOKUP_DATE=> x_config_lookup_date
2855 );
2856 param_value(14) := to_char(x_config_effective_date,'MM-DD-YYYY-HH24-MI-SS');
2857 param_value(15) := to_char(x_config_lookup_date,'MM-DD-YYYY-HH24-MI-SS');
2858
2859 else -- profile not set
2860 param_value(14) := null;
2861 param_value(15) := null;
2862 end if;
2863
2864 l_rec_index := 1;
2865
2866 LOOP
2867 -- ex : <param name="config_header_id">1890</param>
2868
2869 IF (param_value(l_rec_index) IS NOT NULL) THEN
2870
2871 l_dummy := '<param name=' ||
2872 '"' || param_name(l_rec_index) || '"'
2873 ||'>'|| param_value(l_rec_index) ||
2874 '</param>';
2875
2876 l_xml_hdr := l_xml_hdr || l_dummy;
2877
2878 END IF;
2879
2880 l_dummy := NULL;
2881
2882 l_rec_index := l_rec_index + 1;
2883 EXIT WHEN l_rec_index > l_count;
2884
2885 END LOOP;
2886
2887 -- add termination tags
2888
2889 l_xml_hdr := l_xml_hdr || '</initialize>';
2890 l_xml_hdr := REPLACE(l_xml_hdr, ' ' , '+');
2891
2892 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2893
2894 aso_debug_pub.add('Create_hdr_xml: Length of l_xml_hdr mesg: '||length(l_xml_hdr));
2895 aso_debug_pub.add('Create_hdr_xml: 1st Part of l_xml_hdr is: '||SUBSTR(l_xml_hdr, 1, 100));
2896 aso_debug_pub.add('Create_hdr_xml: 2nd Part of l_xml_hdr is: '||SUBSTR(l_xml_hdr, 101, 100));
2897 aso_debug_pub.add('Create_hdr_xml: 3rd Part of l_xml_hdr is: '||SUBSTR(l_xml_hdr, 201, 100));
2898 aso_debug_pub.add('Create_hdr_xml: 4th Part of l_xml_hdr is: '||SUBSTR(l_xml_hdr, 301, 100));
2899 aso_debug_pub.add('Create_hdr_xml: 5st Part of l_xml_hdr is: '||SUBSTR(l_xml_hdr, 401, 100));
2900 aso_debug_pub.add('Create_hdr_xml: 6nd Part of l_xml_hdr is: '||SUBSTR(l_xml_hdr, 501, 100));
2901 aso_debug_pub.add('Create_hdr_xml: 7rd Part of l_xml_hdr is: '||SUBSTR(l_xml_hdr, 601, 100));
2902 aso_debug_pub.add('Create_hdr_xml: 8th Part of l_xml_hdr is: '||SUBSTR(l_xml_hdr, 701, 100));
2903
2904 END IF;
2905
2906 x_xml_hdr := l_xml_hdr;
2907
2908 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2909 aso_debug_pub.add('End of Create_hdr_xml.', 1, 'Y');
2910 END IF;
2911
2912
2913 EXCEPTION
2914
2915 when others then
2916
2917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2918
2919 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2920 aso_debug_pub.add('Create_hdr_xml: Inside When Others Exception: x_return_status: '||x_return_status, 1, 'N');
2921 END IF;
2922
2923 END Create_hdr_xml;
2924
2925
2926
2927 -- create xml message, send it to ui manager
2928 -- get back pieces of xml message
2929 -- process them and generate a long output xml message
2930 -- hardcoded :url,user, passwd, gwyuid,fndnam,two_task
2931
2932 /*-------------------------------------------------------------------------
2933 Procedure Name : Send_input_xml
2934 Description : sends the xml batch validation message to SPC that has
2935 options that are newly inserted/updated/deleted
2936 from the model.
2940 CONFIG_PROCESSED_NO_TERMINATE constant NUMBER :=1;
2937
2938 SPC validation_status :
2939 CONFIG_PROCESSED constant NUMBER :=0;
2941 INIT_TOO_LONG constant NUMBER :=2;
2942 INVALID_OPTION_REQUEST constant NUMBER :=3;
2943 CONFIG_EXCEPTION constant NUMBER :=4;
2944 DATABASE_ERROR constant NUMBER :=5;
2945 UTL_HTTP_INIT_FAILED constant NUMBER :=6;
2946 UTL_HTTP_REQUEST_FAILED constant NUMBER :=7;
2947
2948
2949 --------------------------------------------------------------------------*/
2950
2951 PROCEDURE Send_input_xml
2952 ( P_Qte_Line_Tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type
2953 := ASO_QUOTE_PUB.G_MISS_QTE_LINE_TBL,
2954 P_Qte_Line_Dtl_Tbl IN ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type
2955 := ASO_QUOTE_PUB.G_MISS_QTE_LINE_DTL_TBL,
2956 P_xml_hdr IN VARCHAR2,
2957 X_out_xml_msg OUT NOCOPY /* file.sql.39 change */ LONG ,
2958 X_config_changed OUT NOCOPY /* file.sql.39 change */ VARCHAR2, -- CZ ER
2959 X_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2960 X_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2961 X_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2962 )
2963 IS
2964 l_html_pieces CZ_CF_API.CFG_OUTPUT_PIECES; -- table of VARCHAR2(2000)
2965 l_option_rec CZ_CF_API.INPUT_SELECTION;
2966 l_batch_val_tbl CZ_CF_API.CFG_INPUT_LIST;
2967
2968 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
2969 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
2970
2971 l_delete_qty VARCHAR2(30) := '0';
2972
2973 --variable to fetch from cursor Get_Options
2974 l_component_code VARCHAR2(1000);
2975 --l_config_item_id NUMBER;
2976 l_inventory_item_id VARCHAR2(30);
2977 l_option_quantity VARCHAR2(30);
2978
2979 -- message related
2980 l_validation_status NUMBER;
2981 l_url VARCHAR2(500):= FND_PROFILE.Value('CZ_UIMGR_URL');
2982 l_xml_hdr VARCHAR2(2000);
2983 l_dummy VARCHAR2(2000) := NULL;
2984 l_long_xml LONG := NULL;
2985 l_item_type_code VARCHAR2(50);
2986 l_index BINARY_INTEGER;
2987 i NUMBER;
2988 l_return_status VARCHAR2(1);
2989 BEGIN
2990 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2991 aso_debug_pub.add('ASO_CFG_INT: Send_input_xml Begin.', 1, 'Y');
2992 END IF;
2993
2994 --Initialize API return status to SUCCESS
2995 l_return_status := FND_API.G_RET_STS_SUCCESS;
2996
2997 l_xml_hdr := p_xml_hdr;
2998
2999 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3000 aso_debug_pub.add('ASO_CFG_INT: Send_input_xml: Before the quote line Loop.', 1, 'Y');
3001 END IF;
3002
3003 FOR i IN 1..P_Qte_Line_Tbl.COUNT LOOP
3004
3005 l_option_rec.input_seq := i;
3006 l_option_rec.component_code := p_qte_line_dtl_tbl(i).component_code;
3007 l_option_rec.config_item_id := p_qte_line_dtl_tbl(i).config_item_id;
3008
3009 IF P_Qte_Line_Tbl(i).operation_code = 'DELETE' THEN
3010 l_option_rec.quantity := l_delete_qty;
3011 ELSIF P_Qte_Line_Tbl(i).operation_code = 'UPDATE' THEN
3012 l_option_rec.quantity := P_Qte_Line_Tbl(i).quantity;
3013 END IF;
3014
3015 l_batch_val_tbl(i) := l_option_rec;
3016
3017 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3018
3019 aso_debug_pub.add('l_batch_val_tbl('||i||').input_seq: '||l_batch_val_tbl(i).input_seq);
3020 aso_debug_pub.add('l_batch_val_tbl('||i||').component_code: '||l_batch_val_tbl(i).component_code);
3021 aso_debug_pub.add('l_batch_val_tbl('||i||').quantity: '||l_batch_val_tbl(i).quantity);
3022 aso_debug_pub.add('l_batch_val_tbl('||i||').config_item_id: '||l_batch_val_tbl(i).config_item_id);
3023
3024 END IF;
3025
3026 END LOOP;
3027
3028 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3029 aso_debug_pub.add('ASO_CFG_INT: Send_input_xml: After the quote line Loop.', 1, 'Y');
3030 END IF;
3031
3032 -- delete previous data.
3033 IF (l_html_pieces.COUNT <> 0) THEN
3034 l_html_pieces.DELETE;
3035 END IF;
3036
3037 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3038 aso_debug_pub.add('Send_input_xml: l_html_pieces.COUNT: '||l_html_pieces.COUNT);
3039 aso_debug_pub.add('Send_input_xml: Before call to CZ_CF_API.Validate');
3040 END IF;
3041
3042 CZ_CF_API.Validate( config_input_list => l_batch_val_tbl,
3043 init_message => l_xml_hdr,
3044 p_check_config_flag => 'Y',
3045 config_messages => l_html_pieces,
3046 x_return_config_changed => X_config_changed,
3047 validation_status => l_validation_status,
3048 URL => l_url );
3049
3050 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3051 aso_debug_pub.add('Send_input_xml: After call to CZ_CF_API.Validate: l_validation_status: '||l_validation_status);
3052 END IF;
3053
3054 IF l_validation_status <> 0 THEN
3055
3056 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3057 aso_debug_pub.add('Send_input_xml: Error returned from CZ_CF_API.Validate');
3058 END IF;
3059
3060 FND_MESSAGE.Set_Name('ASO', 'ASO_BATCH_VALIDATE');
3064 END IF;
3061 FND_MESSAGE.Set_token('ERR_TEXT' , 'Error returned from CZ_CF_API.Validate, validation_status <> 0' );
3062 FND_MSG_PUB.ADD;
3063 l_return_status := FND_API.G_RET_STS_ERROR;
3065
3066 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3067 aso_debug_pub.add('Send_input_xml: After call to CZ_CF_API.Validate: l_html_pieces.COUNT: '||l_html_pieces.COUNT);
3068 END IF;
3069
3070 IF (l_html_pieces.COUNT <= 0) THEN
3071
3072 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3073 aso_debug_pub.add('Send_input_xml: No XML message returned from CZ_CF_API.Validate api', 1, 'Y');
3074 END IF;
3075
3076 FND_MESSAGE.Set_Name('ASO', 'ASO_BATCH_VALIDATE');
3077 FND_MESSAGE.Set_token('ERR_TEXT' , 'Error returned from CZ_CF_API.Validate, config_messages: l_html_pieces.COUNT <= 0' );
3078 FND_MSG_PUB.ADD;
3079 l_return_status := FND_API.G_RET_STS_ERROR;
3080
3081 END IF;
3082
3083 l_index := l_html_pieces.FIRST;
3084
3085 LOOP
3086
3087 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3088 aso_debug_pub.add('Send_input_xml: Part of output_message :'|| SUBSTR(l_html_pieces(l_index), 1, 100));
3089 END IF;
3090
3091 l_long_xml := l_long_xml || l_html_pieces(l_index);
3092
3093 EXIT WHEN l_index = l_html_pieces.LAST;
3094 l_index := l_html_pieces.NEXT(l_index);
3095
3096 END LOOP;
3097
3098 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3099
3100 aso_debug_pub.add('Send_input_xml: Part of output_message :'|| SUBSTR(l_long_xml, 1, 100));
3101 aso_debug_pub.add('Send_input_xml: Part of output_message :'|| SUBSTR(l_long_xml, 101, 200));
3102 aso_debug_pub.add('Send_input_xml: Part of output_message :'|| SUBSTR(l_long_xml, 201, 300));
3103 aso_debug_pub.add('Send_input_xml: Part of output_message :'|| SUBSTR(l_long_xml, 301, 400));
3104 aso_debug_pub.add('Send_input_xml: X_config_changed :'|| X_config_changed);
3105
3106 END IF;
3107
3108 -- Return the output XML message
3109 x_out_xml_msg := l_long_xml;
3110 x_return_status := l_return_status;
3111
3112 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3113 aso_debug_pub.Add('End of Send_input_xml', 1, 'Y');
3114 END IF;
3115
3116 EXCEPTION
3117
3118 WHEN OTHERS THEN
3119
3120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3121
3122 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3123 aso_debug_pub.add('Send_input_xml: Inside When Others Exception:', 1, 'N');
3124 END IF;
3125
3126 END Send_input_xml;
3127
3128
3129 /*-------------------------------------------------------------------------
3130 Procedure Name : Parse_output_xml
3131 Description : Parses the output XML message returned from the CZ to get the
3132 valid and complete configuration flag.
3133 If error is returned then populates CZ messages into ASO
3134 message stack.
3135 --------------------------------------------------------------------------*/
3136
3137 PROCEDURE Parse_output_xml
3138 ( p_xml_msg IN LONG,
3139 x_valid_configuration_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3140 x_complete_configuration_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3141 x_config_header_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
3142 x_config_revision_num OUT NOCOPY /* file.sql.39 change */ NUMBER,
3143 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3144 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3145 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3146 )
3147 IS
3148
3149 CURSOR c_messages(p_config_hdr_id NUMBER, p_config_rev_nbr NUMBER) is
3150 SELECT constraint_type , message
3151 FROM cz_config_messages
3152 WHERE config_hdr_id = p_config_hdr_id
3153 AND config_rev_nbr = p_config_rev_nbr;
3154
3155 i NUMBER := 1;
3156 l_config_header_id NUMBER;
3157 l_config_revision_num NUMBER;
3158 l_valid_configuration VARCHAR2(10);
3159 l_complete_configuration VARCHAR2(10);
3160 l_complete_configuration_flag VARCHAR2(1);
3161 l_valid_configuration_flag VARCHAR2(1);
3162 l_message_type VARCHAR2(100);
3163 l_message_text VARCHAR2(4000);
3164 l_exit VARCHAR2(100);
3165 l_msg VARCHAR2(2000);
3166 l_len_msg NUMBER;
3167 l_constraint VARCHAR2(16);
3168
3169 l_return_status VARCHAR2(1);
3170 l_msg_count NUMBER;
3171 l_msg_data VARCHAR2(2000);
3172
3173 BEGIN
3174 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3175 aso_debug_pub.add('ASO_CFG_INT: Parse_output_xml Begin.', 1, 'Y');
3176 END IF;
3177
3178 --Initialize API return status to SUCCESS
3179 l_return_status := FND_API.G_RET_STS_SUCCESS;
3180
3181 l_config_header_id := to_number(substr(p_xml_msg,(instr(p_xml_msg, '<config_header_id>',1,1)+18),
3182 (instr(p_xml_msg,'</config_header_id>',1,1) -
3183 (instr(p_xml_msg, '<config_header_id>',1,1)+18))));
3184
3185 l_config_revision_num := to_number(substr(p_xml_msg,(instr(p_xml_msg,'<config_rev_nbr>',1,1)+16),
3186 (instr(p_xml_msg,'</config_rev_nbr>',1,1) -
3190 (instr(p_xml_msg,'</valid_configuration>',1,1) -
3187 (instr(p_xml_msg,'<config_rev_nbr>',1,1)+16))));
3188
3189 l_valid_configuration := substr(p_xml_msg,(instr(p_xml_msg,'<valid_configuration>',1,1)+21),
3191 (instr(p_xml_msg,'<valid_configuration>',1,1)+21)));
3192
3193 l_complete_configuration := substr(p_xml_msg,(instr(p_xml_msg,'<complete_configuration>',1,1)+24),
3194 (instr(p_xml_msg,'</complete_configuration>',1,1) -
3195 (instr(p_xml_msg,'<complete_configuration>',1,1)+24)));
3196
3197 l_message_type := substr(p_xml_msg,(instr(p_xml_msg,'<message_type>',1,1)+14),
3198 (instr(p_xml_msg,'</message_type>',1,1) -
3199 (instr(p_xml_msg,'<message_type>',1,1)+14)));
3200
3201 l_message_text := substr(p_xml_msg,(instr(p_xml_msg,'<message_text>',1,1)+14),
3202 (instr(p_xml_msg,'</message_text>',1,1) -
3203 (instr(p_xml_msg,'<message_text>',1,1)+14)));
3204
3205 l_exit := substr(p_xml_msg,(instr(p_xml_msg,'<exit>',1,1)+6),
3206 (instr(p_xml_msg,'</exit>',1,1) -
3207 (instr(p_xml_msg,'<exit>',1,1)+6)));
3208
3209
3210 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3211 aso_debug_pub.add('Parse_output_xml: l_message_type: '|| l_message_type);
3212 aso_debug_pub.add('Parse_output_xml: l_message_text: '|| substr(l_message_text,1,150));
3213 aso_debug_pub.add('Parse_output_xml: l_exit : '|| l_exit);
3214 END IF;
3215
3216 IF l_exit = 'error' AND l_message_type = 'error' THEN
3217
3218 i := 1;
3219 l_len_msg := Length(l_message_text);
3220
3221 While l_len_msg >= i Loop
3222
3223 FND_MESSAGE.Set_Name('ASO', 'ASO_BATCH_VALIDATE');
3224 FND_MESSAGE.Set_token('ERR_TEXT' , substr(l_message_text,i,240));
3225 FND_MSG_PUB.ADD;
3226
3227 i := i + 240;
3228
3229 End Loop;
3230
3231 l_return_status := FND_API.G_RET_STS_ERROR;
3232
3233 END IF;
3234
3235
3236 IF (nvl(l_valid_configuration, 'N') <> 'true') THEN
3237 l_valid_configuration_flag := 'N';
3238 ELSE
3239 l_valid_configuration_flag := 'Y';
3240 END IF ;
3241
3242 IF (nvl(l_complete_configuration, 'N') <> 'true' ) THEN
3243 l_complete_configuration_flag := 'N';
3244 ELSE
3245 l_complete_configuration_flag := 'Y';
3246 END IF;
3247
3248
3249 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3250 aso_debug_pub.add('Parse_output_xml: l_valid_configuration_flag: '|| l_valid_configuration_flag);
3251 aso_debug_pub.add('Parse_output_xml: l_complete_configuration_flag: '|| l_complete_configuration_flag);
3252 END IF;
3253
3254 IF l_config_header_id is NULL THEN
3255
3256 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3257 aso_debug_pub.add('Parse_output_xml: Getting messages from cz_config_messages');
3258 END IF;
3259
3260 OPEN c_messages(l_config_header_id, l_config_revision_num);
3261
3262 LOOP
3263
3264 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3265 aso_debug_pub.add('Parse_output_xml: CZ message: c_messages%rowcount: '||c_messages%rowcount);
3266 END IF;
3267
3268 FETCH c_messages into l_constraint,l_msg;
3269 EXIT when c_messages%notfound;
3270
3271 i := 1;
3272 l_len_msg := Length(l_msg);
3273
3274 While l_len_msg >= i Loop
3275
3276 FND_MESSAGE.Set_Name('ASO', 'ASO_BATCH_VALIDATE');
3277 FND_MESSAGE.Set_token('ERR_TEXT' , substr(l_msg,i,240));
3278 i := i + 240;
3279 FND_MSG_PUB.ADD;
3280
3281 End Loop;
3282
3283 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3284 aso_debug_pub.add('Parse_output_xml: '|| substr(l_msg, 1, 100));
3285 aso_debug_pub.add('Parse_output_xml: '|| substr(l_msg, 101, 200));
3286 aso_debug_pub.add('Parse_output_xml: '|| substr(l_msg, 201, 300));
3287 END IF;
3288
3289 END LOOP;
3290
3291 l_return_status := FND_API.G_RET_STS_ERROR;
3292
3293 END IF;
3294
3295 -- if everything ok, set return values
3296
3297 x_valid_configuration_flag := l_valid_configuration_flag;
3298 x_complete_configuration_flag := l_complete_configuration_flag;
3299 x_return_status := l_return_status;
3300 x_config_header_id := l_config_header_id;
3301 x_config_revision_num := l_config_revision_num;
3302 x_msg_count := l_msg_count;
3303 x_msg_data := l_msg_data;
3304
3305 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3306 aso_debug_pub.Add('End of parse_output_xml', 1, 'Y');
3307 END IF;
3308
3309 EXCEPTION
3310
3311 WHEN OTHERS THEN
3312
3313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3314
3315 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3316 aso_Debug_Pub.Add( 'Parse_Output_xml: In WHEN OTHERS exception ', 1, 'N');
3317 END IF;
3318
3319 END Parse_output_xml;
3320
3321
3322
3323 /*----------------------------------------------------------------------
3324 PROCEDURE : Validate_configuration
3325 Description : Checks if the configuration is complete and valid.
3326 Returns success/error as status. It calls
3330 that are updated and deleted from the model.
3327 Create_hdr_xml : To create the CZ batch validation header xml message
3328 Send_input_xml : Sends the xml message created by Create_hdr_xml to the
3329 CZ configurator along with a pl/sql table which has options
3331 Parse_output_xml : parses the CZ output xml message to see if the configuration
3332 is valid and complete.
3333 Get_config_details : To save options along with the model line in ASO_QUOTE_LINES_ALL
3334 , ASO_QUOTE_LINE_DETAILS and ASO_LINE_RELATIONSHIPS
3335 -----------------------------------------------------------------------*/
3336
3337 PROCEDURE Validate_Configuration
3338 (P_Api_Version_Number IN NUMBER := FND_API.G_MISS_NUM,
3339 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3340 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
3341 p_control_rec IN aso_quote_pub.control_rec_type
3342 := aso_quote_pub.G_MISS_control_rec,
3343 P_model_line_id IN NUMBER,
3344 P_Qte_Line_Tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type
3345 := ASO_QUOTE_PUB.G_MISS_QTE_LINE_TBL,
3346 P_Qte_Line_Dtl_Tbl IN ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type
3347 := ASO_QUOTE_PUB.G_MISS_QTE_LINE_DTL_TBL,
3348 X_config_header_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
3349 X_config_revision_num OUT NOCOPY /* file.sql.39 change */ NUMBER,
3350 X_valid_configuration_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3351 X_complete_configuration_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3352 X_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3353 X_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3354 X_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3355 )
3356 IS
3357 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Configuration' ;
3358 l_api_version_number CONSTANT NUMBER := 1.0;
3359
3360 l_model_line_id NUMBER := p_model_line_id;
3361 l_qte_header_rec aso_quote_pub.qte_header_rec_type := aso_quote_pub.g_miss_qte_header_rec;
3362 l_model_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type := ASO_QUOTE_PUB.G_MISS_QTE_LINE_REC;
3363 l_model_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type := ASO_QUOTE_PUB.G_MISS_QTE_LINE_DTL_TBL;
3364
3365 l_config_header_id NUMBER;
3366 l_config_revision_num NUMBER;
3367 l_valid_configuration_flag VARCHAR2(1);
3368 l_complete_configuration_flag VARCHAR2(1);
3369 --l_model_qty NUMBER;
3370 l_msg_count NUMBER;
3371 l_msg_data VARCHAR2(2000);
3372
3373 l_result_out VARCHAR2(30);
3374
3375 -- input xml message
3376 l_xml_message LONG := NULL;
3377 l_xml_hdr VARCHAR2(2000);
3378
3379 -- upgrade stuff
3380 l_upgraded_flag VARCHAR2(1);
3381
3382 -- cz's delete return value
3383 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3384 l_delete_config VARCHAR2(1) := fnd_api.g_false;
3385 l_old_config_hdr_id NUMBER;
3386 l_config_changed VARCHAR2(1);
3387 BEGIN
3388 -- Standard Start of API savepoint
3389 -- SAVEPOINT VALIDATE_CONFIGURATION_INT;
3390
3391 l_return_status := FND_API.G_RET_STS_SUCCESS;
3392
3393 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3394 aso_debug_pub.add('ASO_CFG_INT: Validate_Configuration Begins', 1, 'Y');
3395 END IF;
3396
3397 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3398 p_api_version_number,
3399 l_api_name,
3400 G_PKG_NAME) THEN
3401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3402 END IF;
3403
3404 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3405 FND_MSG_PUB.initialize;
3406 END IF;
3407
3408 -- Get model line info
3409 l_model_line_rec := ASO_UTILITY_PVT.Query_Qte_Line_Row(p_model_line_id);
3410 l_model_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows(p_model_line_id);
3411
3412 -- Call Create_hdr_xml to create the input header XML message
3413 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3414 aso_debug_pub.add('Validate_Configuration: Before call to Create_hdr_xml.');
3415 END IF;
3416
3417 Create_hdr_xml ( P_model_line_id => P_model_line_id,
3418 X_xml_hdr => l_xml_hdr,
3419 X_return_status => l_return_status );
3420
3421 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3422
3423 aso_debug_pub.add('Validate_Configuration: After call to Create_hdr_xml l_return_status: '||l_return_status);
3424 aso_debug_pub.add('Validate_Configuration: After call to Create_hdr_xml Length of l_xml_hdr : '||length(l_xml_hdr));
3425
3426 END IF;
3427
3428 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3429
3430 -- Call Send_Input_Xml to call CZ batch validate procedure and get the output XML message
3431
3432 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3433 aso_debug_pub.add('ASO_CFG_INT: Validate_Configuration: Before call to Send_input_xml');
3434 END IF;
3435
3436 Send_input_xml( P_Qte_Line_Tbl => P_Qte_Line_Tbl,
3437 P_Qte_Line_Dtl_Tbl => P_Qte_Line_Dtl_Tbl,
3438 P_xml_hdr => l_xml_hdr,
3439 X_out_xml_msg => l_xml_message,
3440 X_config_changed => l_config_changed, -- CZ ER
3441 X_return_status => l_return_status,
3442 X_msg_count => l_msg_count,
3443 X_msg_data => l_msg_data
3444 );
3445
3446 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3447 aso_debug_pub.add('Validate_Configuration: After call to Send_input_xml');
3448 aso_debug_pub.add('Validate_Configuration: l_return_status: '||l_return_status);
3449 aso_debug_pub.add('Validate_Configuration: l_config_changed: '||l_config_changed);
3450 END IF;
3451
3452
3453
3454 -- extract data from xml message.
3455
3456 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3457 l_delete_config := fnd_api.g_true;
3458 END IF;
3459
3460 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3461 aso_debug_pub.add('Validate_Configuration: Before Call to Parse_Output_xml',1,'N');
3462 aso_debug_pub.add('Validate_Configuration: l_delete_config: '||l_delete_config);
3463 END IF;
3464
3465 Parse_output_xml
3466 ( p_xml_msg => l_xml_message,
3467 x_valid_configuration_flag => l_valid_configuration_flag,
3468 x_complete_configuration_flag => l_complete_configuration_flag,
3469 x_config_header_id => l_config_header_id,
3470 x_config_revision_num => l_config_revision_num,
3471 x_return_status => l_return_status,
3472 x_msg_count => l_msg_count,
3473 x_msg_data => l_msg_data
3474 );
3475
3476 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3477 aso_debug_pub.add('Validate_Configuration: After call to Parse_output_xml');
3478 aso_debug_pub.add('Validate_Configuration: l_return_status: '||l_return_status);
3479 END IF;
3480
3481 END IF;
3482
3483 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) and (l_delete_config = fnd_api.g_false) THEN
3484
3485 -- Call GET_CONFIG_DETAILS to update the existing configuration
3486 -- Set the Call_batch_validation_flag to FND_API.G_FALSE to avoid recursive call to update_quote
3487
3488 l_model_line_dtl_tbl(1).valid_configuration_flag := l_valid_configuration_flag;
3489 l_model_line_dtl_tbl(1).complete_configuration_flag := l_complete_configuration_flag;
3490
3491 l_qte_header_rec.quote_header_id := l_model_line_rec.quote_header_id;
3492
3493
3494 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3495 aso_debug_pub.add('Validate_Configuration: Before Call to ASO_CFG_INT.Get_config_details');
3496 END IF;
3497
3498 ASO_CFG_INT.Get_config_details(
3499 p_api_version_number => 1.0,
3500 p_init_msg_list => FND_API.G_FALSE,
3501 p_commit => FND_API.G_FALSE,
3502 p_control_rec => p_control_rec,
3503 p_qte_header_rec => l_qte_header_rec,
3504 p_model_line_rec => l_model_line_rec,
3505 p_config_rec => l_model_line_dtl_tbl(1),
3506 p_config_hdr_id => l_config_header_id,
3507 p_config_rev_nbr => l_config_revision_num,
3508 x_return_status => l_return_status,
3509 x_msg_count => l_msg_count,
3510 x_msg_data => l_msg_data );
3511
3512 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3513 aso_debug_pub.add('Validate_Configuration: After Call to Get_config_details');
3514 aso_debug_pub.add('Validate_Configuration: l_return_status: '||l_return_status);
3515 END IF;
3516
3517 ELSE
3518 l_delete_config := fnd_api.g_true;
3519 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3520 aso_debug_pub.add('Validate_Configuration: l_delete_config: '||l_delete_config);
3521 END IF;
3522
3523 END IF;
3524
3525 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3526
3527 aso_debug_pub.add('End of procedure Validate_Configuration');
3528 aso_debug_pub.add('l_return_status: '|| l_return_status);
3529 aso_debug_pub.add('l_valid_configuration_flag: '|| l_valid_configuration_flag);
3530 aso_debug_pub.add('l_complete_configuration_flag: '|| l_complete_configuration_flag);
3531 aso_debug_pub.add('l_config_changed: '|| l_config_changed);
3532
3533 END IF;
3534
3535 x_config_header_id := l_config_header_id;
3536 x_config_revision_num := l_config_revision_num;
3537 x_valid_configuration_flag := l_valid_configuration_flag;
3538 x_complete_configuration_flag := l_complete_configuration_flag;
3539 x_return_status := l_return_status;
3540 x_msg_count := l_msg_count;
3541 x_msg_data := l_msg_data;
3542
3543 if l_delete_config = fnd_api.g_true then
3544
3545 x_return_status := FND_API.G_RET_STS_ERROR;
3546
3547 end if;
3548
3549 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3550 aso_debug_pub.add('End of Validate_Configuration', 1, 'N');
3551 END IF;
3552
3553 EXCEPTION
3554
3555 WHEN OTHERS THEN
3556
3557 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3558 aso_debug_pub.add('Validate_Configuration: Inside WHEN OTHERS EXCEPTION', 1, 'Y');
3559 END IF;
3560
3561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3562
3563 END Validate_Configuration;
3564
3565
3566 End aso_cfg_int;