[Home] [Help]
PACKAGE BODY: APPS.ASO_COPY_TMPL_PUB
Source
1 PACKAGE BODY ASO_COPY_TMPL_PUB AS
2 /* $Header: asoptcpb.pls 120.4.12020000.2 2012/08/30 10:31:36 rassharm ship $ */
3 -- Start of Comments
4 -- Package name : ASO_COPY_TMPL_PUB
5 -- Purpose :
6 -- This package body contains procedure for creating template from quote
7 -- Public API of Order Capture.
8 --
9 -- Procedures:
10 -- Copy_Quote_To_Tmpl
11 --
12 -- History :
13 -- NOTE :
14 --
15 -- End of Comments
16
17 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
18 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
19 G_PKG_NAME CONSTANT VARCHAR2 ( 30 ) := 'ASO_COPY_TMPL _PUB';
20 G_FILE_NAME CONSTANT VARCHAR2 ( 12 ) := 'asoptcpb.pls';
21
22
23 PROCEDURE Copy_Quote_To_Tmpl (
24 P_Api_Version_Number IN NUMBER
25 , P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
26 , P_Commit IN VARCHAR2 := FND_API.G_FALSE
27 , P_old_quote_header_Id IN NUMBER
28 , X_Qte_Header_Id OUT NOCOPY /* file.sql.39 change */ NUMBER
29 , X_Qte_Number OUT NOCOPY /* file.sql.39 change */ NUMBER
30 , X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
31 , X_Msg_Count OUT NOCOPY /* file.sql.39 change */ VARCHAR2
32 , X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
33 )
34 IS
35
36 CURSOR C_Qte_Number IS
37 SELECT ASO_QUOTE_NUMBER_S.NEXTVAL
38 FROM sys.DUAL;
39
40 CURSOR C_Qte_Details
41 (l_quote_header_Id NUMBER)
42 IS
43 select org_id,quote_name||':'||quote_number||':'||quote_version qte_name,currency_code,price_list_id,order_type_id,quote_category_code
44 from aso_Quote_headers_all
45 where quote_header_id=l_quote_header_Id;
46
47
48 CURSOR C_Qte_Status_Id (
49 c_status_code VARCHAR2
50 ) IS
51 SELECT quote_status_id
52 FROM ASO_QUOTE_STATUSES_B
53 WHERE status_code = c_status_code;
54
55 l_api_name CONSTANT VARCHAR2 ( 30 ) := 'Copy_Quote_To_Tmpl';
56 l_api_version_number CONSTANT NUMBER := 1.0;
57 l_return_status VARCHAR2 ( 1 );
58 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
59 l_qte_num NUMBER;
60 l_quote_name varchar2(2000);
61 l_currency_code varchar2(15);
62 l_price_list_id number;
63 l_order_type_id number;
64 l_quote_category_code varchar2(240);
65
66 -- ER 3177722
67 lx_config_tbl ASO_QUOTE_PUB.Config_Vaild_Tbl_Type;
68 l_copy_config_profile varchar2(1):=nvl(fnd_profile.value('ASO_COPY_CONFIG_EFF_DATE'),'Y');
69
70
71 begin
72
73 -- Standard Start of API savepoint
74 SAVEPOINT COPY_QUOTE_TO_TMPL;
75 -- Standard call to check for call compatibility.
76 IF NOT FND_API.Compatible_API_Call (
77 l_api_version_number
78 , p_api_version_number
79 , l_api_name
80 , G_PKG_NAME
81 ) THEN
82 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
83 END IF;
84
85 -- Initialize message list if p_init_msg_list is set to TRUE.
86 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
87 FND_MSG_PUB.initialize;
88 END IF;
89
90 -- Debug Message
91 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ) THEN
92 FND_MESSAGE.Set_Name ('ASO' , 'Copy Quote To Template API: Start' );
93 FND_MSG_PUB.ADD;
94 END IF;
95
96 -- Initialize API return status to success
97 l_return_status := FND_API.G_RET_STS_SUCCESS;
98
99 --
100 -- API body
101 --
102 -- ******************************************************************
103 -- Validate Environment
104 -- ******************************************************************
105 IF FND_GLOBAL.User_Id IS NULL THEN
106 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
107 FND_MESSAGE.Set_Name ('ASO' , 'UT_CANNOT_GET_PROFILE_VALUE' );
108 FND_MESSAGE.Set_Token ('PROFILE' , 'USER_ID', FALSE );
109 FND_MSG_PUB.ADD;
110 END IF;
111 RAISE FND_API.G_EXC_ERROR;
112 END IF;
113
114 -- ******************************************************************
115
116 IF aso_debug_pub.g_debug_flag = 'Y' THEN
117 aso_debug_pub.ADD ( 'Copy_Tmpl - Begin- ASO_COPY_TMPL_PUB.Copy_Tmpl_to_tmpl ' , 1 , 'Y' );
118 aso_debug_pub.ADD ( 'Copy_Tmpl - Begin- ASO_COPY_TMPL_PUB.Copy_Quote_to_tmpl quote_header_id'||P_old_quote_header_Id , 1 , 'Y' );
119 end if;
120
121 OPEN C_Qte_Number;
122 FETCH C_Qte_Number INTO l_qte_header_rec.quote_number;
123 CLOSE C_Qte_Number;
124
125 l_qte_header_rec.quote_version := 1;
126 l_qte_header_rec.max_version_flag := 'Y';
127 l_qte_header_rec.AUTOMATIC_PRICE_FLAG:= 'A';
128 l_qte_header_rec.AUTOMATIC_Tax_FLAG:= 'A';
129 l_qte_header_rec.PRICING_STATUS_INDICATOR:='I';
130 l_qte_header_rec.Tax_STATUS_INDICATOR:='I';
131 l_qte_header_rec.QUOTE_SOURCE_CODE := 'Order Capture Quotes';
132 l_qte_header_rec.QUOTE_TYPE := 'T';
133
134
135 OPEN C_Qte_Details(P_old_quote_header_Id);
136 FETCH C_Qte_Details INTO l_qte_header_rec.org_id,l_quote_name,l_currency_code,l_price_list_id,l_order_type_id,l_quote_category_code;
137 CLOSE C_Qte_Details;
138
139 l_qte_header_rec.QUOTE_NAME:=substr(l_quote_name,1,240);
140 l_qte_header_rec.currency_code:= l_currency_code;
141 l_qte_header_rec.order_type_id:=l_order_type_id;
142 l_qte_header_rec.quote_category_code:=l_quote_category_code;
143
144
145 OPEN c_qte_status_id ( fnd_profile.VALUE ('ASO_DEFAULT_STATUS_CODE' ) );
146 FETCH c_qte_status_id INTO l_qte_header_rec.quote_status_id;
147
148 IF c_qte_status_id%NOTFOUND THEN
149 x_return_status := FND_API.G_RET_STS_ERROR;
150 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
151 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_NO_PROFILE_VALUE' );
152 FND_MESSAGE.Set_Token ( 'PROFILE' , 'ASO_DEFAULT_STATUS_CODE' , FALSE );
153 FND_MSG_PUB.ADD;
154 END IF;
155 CLOSE c_qte_status_id;
156 RAISE FND_API.G_EXC_ERROR;
157 END IF;
158
159 CLOSE c_qte_status_id;
160
161
162
163 IF aso_debug_pub.g_debug_flag = 'Y' THEN
164 aso_debug_pub.ADD ( 'Copy_Tmpl - Begin- ASO_COPY_TMPL_PUB.Copy_Quote_to_tmpl ' , 1 , 'Y' );
165 aso_debug_pub.ADD ( 'Copy_Tmpl - Begin- ASO_COPY_TMPL_PUB.Copy_Quote_to_tmpl quote_number'|| l_qte_header_rec.quote_number , 1 , 'Y' );
166 aso_debug_pub.ADD ( 'Copy_Tmpl - Begin- ASO_COPY_TMPL_PUB.Copy_Quote_to_tmpl org_id'|| l_qte_header_rec.org_id , 1 , 'Y' );
167 end if;
168
169
170 l_qte_header_rec.TOTAL_LIST_PRICE := NULL;
171 l_qte_header_rec.TOTAL_ADJUSTED_AMOUNT := NULL;
172 l_qte_header_rec.TOTAL_ADJUSTED_PERCENT := NULL;
173 l_qte_header_rec.TOTAL_TAX := NULL;
174 l_qte_header_rec.TOTAL_SHIPPING_CHARGE := NULL;
175 l_qte_header_rec.SURCHARGE := NULL;
176 l_qte_header_rec.TOTAL_QUOTE_PRICE := NULL;
177 l_qte_header_rec.PAYMENT_AMOUNT := NULL;
178 l_qte_header_rec.ORDERED_DATE := NULL;
179
180
181 l_qte_header_rec.PUBLISH_FLAG := NULL;
182 l_qte_header_rec.ORDER_ID := NULL;
183 l_qte_header_rec.ORDER_NUMBER := NULL;
184 l_qte_header_rec.quote_header_id := NULL;
185 l_qte_header_rec.price_updated_date := NULL;
186 l_qte_header_rec.tax_updated_date := NULL;
187 l_qte_header_rec.price_request_id := NULL;
188 l_qte_header_rec.price_frozen_date := NULL;
189
190
191 l_qte_header_rec.Customer_Name_And_Title := NULL;
192 l_qte_header_rec.Customer_Signature_Date := NULL;
193 l_qte_header_rec.Supplier_Name_And_Title := NULL;
194 l_qte_header_rec.Supplier_Signature_Date := NULL;
195 l_qte_header_rec.OBJECT_VERSION_NUMBER:=1;
196
197 l_qte_header_rec.ASSISTANCE_REQUESTED := null;
198 l_qte_header_rec.ASSISTANCE_REASON_CODE := null;
199
200
201 Copy_Tmpl_Header (
202 P_Api_Version_Number => 1.0
203 , P_Init_Msg_List => FND_API.G_FALSE
204 , P_Commit => FND_API.G_FALSE
205 , P_qte_Header_Rec => l_qte_header_rec
206 , X_Qte_Header_id => x_qte_header_id
207 , X_Return_Status => l_return_status
208 , X_Msg_Count => x_msg_count
209 , X_Msg_Data => x_msg_data
210 );
211 IF aso_debug_pub.g_debug_flag = 'Y' THEN
212 aso_debug_pub.ADD ( 'Copy_Tmpl - After copy_ header rows ' || l_return_status , 1 , 'Y' );
213 END IF;
214
215 X_Qte_Number:= l_qte_header_rec.quote_number;
216 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
218 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
219 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_UNEXP_ERROR' );
220 FND_MESSAGE.Set_Token ('ROW' , 'ASO_QUOTE_HEADER', TRUE );
221 FND_MSG_PUB.ADD;
222 END IF;
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
224 ELSIF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 RAISE FND_API.G_EXC_ERROR;
227 END IF;
228
229 IF aso_debug_pub.g_debug_flag = 'Y' THEN
230 aso_debug_pub.ADD ( 'Copy_template - before copy template rows ', 1 , 'Y' );
231 END IF;
232
233
234
235 Copy_Tmpl_Lines (
236 P_Api_Version_Number => 1.0
237 , P_Init_Msg_List => FND_API.G_FALSE
238 , P_Commit => FND_API.G_FALSE
239 , P_qte_Header_Id => P_old_quote_header_Id
240 , P_new_qte_header_id => x_qte_header_id
241 --, P_Qte_Header_Rec => l_copy_line_qte_header_rec
242 , P_Control_Rec => ASO_QUOTE_PUB.G_MISS_Control_Rec
243 , X_Return_Status => l_return_status
244 , X_Msg_Count => x_msg_count
245 , X_Msg_Data => x_msg_data
246 );
247 IF aso_debug_pub.g_debug_flag = 'Y' THEN
248 aso_debug_pub.ADD ( 'Copy_template - template rows ' || l_return_status , 1 , 'Y' );
249 END IF;
250
251 if (l_return_status =FND_API.G_RET_STS_SUCCESS) then
252
253
254 if l_copy_config_profile='N' then
255 IF aso_debug_pub.g_debug_flag = 'Y' THEN
256 aso_debug_pub.add('Copy_template -before ASO_QUOTE_PUB.validate_model_configuration return status: ', 1, 'N');
257 end if;
258
259 ASO_QUOTE_PUB.validate_model_configuration
260 (
261 P_Api_Version_Number => 1.0,
262 P_Init_Msg_List => FND_API.G_FALSE,
263 P_Commit => FND_API.G_FALSE,
264 P_Quote_header_id =>x_qte_header_id,
265 P_UPDATE_QUOTE =>'T',
266 P_CONFIG_EFFECTIVE_DATE => sysdate,
267 P_CONFIG_model_lookup_DATE => sysdate,
268 X_Config_tbl => lx_config_tbl,
269 X_Return_Status => l_return_status,
270 X_Msg_Count => x_msg_count,
271 X_Msg_Data => x_msg_data
272 );
273
274 if l_Return_Status=FND_API.G_RET_STS_SUCCESS then
275 commit work;
276 end if;
277
278 IF aso_debug_pub.g_debug_flag = 'Y' THEN
279 aso_debug_pub.add('Copy_template -After ASO_QUOTE_PUB.validate_model_configuration return status: '||l_Return_Status, 1, 'N');
280 aso_debug_pub.add('Copy_template -After ASO_QUOTE_PUB.validate_model_configuration lx_config_tbl: '||lx_config_tbl.count, 1, 'N');
281 END IF;
282
283 end if; -- profile
284 end if; -- success
285
286
287
288 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
291 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_UNEXP_ERROR' );
292 FND_MESSAGE.Set_Token ('ROW' , 'ASO_QUOTE_HEADER', TRUE );
293 FND_MSG_PUB.ADD;
294 END IF;
295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
296 ELSIF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
297 x_return_status := FND_API.G_RET_STS_ERROR;
298 RAISE FND_API.G_EXC_ERROR;
299 END IF;
300
301 X_Return_Status := l_return_status;
302
303 end Copy_Quote_To_Tmpl;
304
305 PROCEDURE Copy_Tmpl_Header (
306 P_Api_Version_Number IN NUMBER
307 , P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
308 , P_Commit IN VARCHAR2 := FND_API.G_FALSE
309 , P_Qte_Header_Rec IN ASO_QUOTE_PUB.qte_header_rec_Type
310 , X_Qte_Header_Id OUT NOCOPY /* file.sql.39 change */ NUMBER
311 , X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
312 , X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER
313 , X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
314 ) IS
315
316 l_api_version CONSTANT NUMBER := 1.0;
317 l_return_status VARCHAR2 ( 1 );
318 l_api_name CONSTANT VARCHAR2 ( 30 ) := 'Copy_Tmpl_Header';
319 l_api_version_number CONSTANT NUMBER := 1.0;
320
321
322 -- out tables parameters
323 l_qte_header_rec_out ASO_QUOTE_PUB.Qte_Header_Rec_Type;
324 l_Price_Attr_Tbl_out ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
325 l_Price_Adj_Attr_Tbl_out ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
326 l_freight_charge_tbl_out ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
327 l_tax_detail_tbl_out ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
328 l_payment_tbl_out ASO_QUOTE_PUB.Payment_Tbl_Type;
329 l_hd_Price_Adj_Tbl_out ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
330 l_shipment_rec_out ASO_QUOTE_PUB.Shipment_Rec_Type;
331 l_hd_Attr_Ext_Tbl_out ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
332 l_Sales_Credit_Tbl_out ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
333 l_Quote_Party_Tbl_out ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
334 l_qte_access_tbl_out aso_quote_pub.qte_access_tbl_type ;
335
336
337
338 begin
339
340 -- Standard Start of API savepoint
341 SAVEPOINT COPY_TMPL_HEADER;
342
343 -- Standard call to check for call compatibility.
344 IF NOT FND_API.Compatible_API_Call (
345 l_api_version_number
346 , p_api_version_number
347 , l_api_name
348 , G_PKG_NAME
349 ) THEN
350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
351 END IF;
352
353 -- Initialize message list if p_init_msg_list is set to TRUE.
354 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
355 FND_MSG_PUB.initialize;
356 END IF;
357
358 -- Debug Message
359 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ) THEN
360 FND_MESSAGE.Set_Name ('ASO' , 'Copy Template Header API: Start'||P_Qte_Header_Rec.quote_number );
361 FND_MSG_PUB.ADD;
362 END IF;
363
364 -- Initialize API return status to success
365 l_return_status := FND_API.G_RET_STS_SUCCESS;
366
367 --
368 -- API body
369 --
370
371 ASO_QUOTE_HEADERS_PVT.Insert_Rows (
372 p_qte_header_rec => P_Qte_Header_Rec
373 , p_Price_Attributes_Tbl => aso_quote_pub.G_Miss_Price_Attributes_Tbl
374 , P_Price_Adjustment_Tbl => aso_quote_pub.G_MISS_Price_Adj_TBL
375 , P_Price_Adj_Attr_Tbl => aso_quote_pub.G_MISS_PRICE_ADJ_ATTR_TBL
376 , P_Payment_Tbl => aso_quote_pub.G_MISS_PAYMENT_TBL
377 , P_Shipment_Tbl => aso_quote_pub.G_MISS_Shipment_TBL
378 , P_Freight_Charge_Tbl => aso_quote_pub.G_Miss_Freight_Charge_Tbl
379 , P_Tax_Detail_Tbl => aso_quote_pub.G_Miss_Tax_Detail_Tbl
380 , P_hd_Attr_Ext_Tbl => aso_quote_pub.G_MISS_Line_Attribs_Ext_TBL
381 , P_Sales_Credit_Tbl => aso_quote_pub.G_MISS_Sales_Credit_Tbl
382 , P_Quote_Party_Tbl => aso_quote_pub.G_MISS_Quote_Party_Tbl
383 , P_qte_access_Tbl => aso_quote_pub.G_MISS_QTE_ACCESS_TBL
384 , x_qte_header_rec => l_qte_header_rec_out
385 , x_Price_Attributes_Tbl => l_price_attr_tbl_out
386 , x_Price_Adjustment_Tbl => l_hd_Price_Adj_Tbl_out
387 , x_Price_Adj_Attr_Tbl => l_price_adj_attr_tbl_out
388 , x_Payment_Tbl => l_payment_tbl_out
389 , x_Shipment_Rec => l_shipment_rec_out
390 , x_Freight_Charge_Tbl => l_freight_charge_tbl_out
391 , x_Tax_Detail_Tbl => l_tax_detail_tbl_out
392 , x_hd_Attr_Ext_Tbl => l_hd_Attr_Ext_Tbl_out
393 , x_sales_credit_tbl => l_sales_credit_tbl_out
394 , x_quote_party_tbl => l_quote_party_tbl_out
395 , x_qte_access_Tbl => l_qte_access_tbl_out
396 , X_Return_Status => l_return_status
397 , X_Msg_Count => x_msg_count
398 , X_Msg_Data => x_msg_data
399 );
400
401
402
403 --l_qte_header_rec :=l_qte_header_rec_out ;
404
405 IF aso_debug_pub.g_debug_flag = 'Y' THEN
406 aso_debug_pub.ADD ( 'Copy_Template - After insert_rows - status: ' || l_return_status , 1 , 'Y' );
407 END IF;
408
409 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
411 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
412 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_UNEXP_ERROR' );
413 FND_MESSAGE.Set_Token ( 'ROW' , 'ASO_COPYHEADER AFTER_INSERT' , TRUE );
414 FND_MSG_PUB.ADD;
415 END IF;
416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
417 ELSIF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
418 x_return_status := FND_API.G_RET_STS_ERROR;
419 RAISE FND_API.G_EXC_ERROR;
420 END IF;
421
422 x_qte_header_id := l_qte_header_rec_out.quote_header_id;
423
424 X_Return_Status := l_return_status;
425
426 end Copy_Tmpl_Header;
427
428
429 PROCEDURE Copy_Tmpl_Lines(
430 P_Api_Version_Number IN NUMBER,
431 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
432 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
433 P_Qte_Header_Id IN NUMBER,
434 P_New_Qte_Header_Id IN NUMBER,
435 P_Control_Rec IN ASO_QUOTE_PUB.Control_Rec_Type,
436 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
437 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
438 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
439 as
440 CURSOR c_line_relation (
441 x_quote_header_id NUMBER
442 ) IS
443 SELECT LINE_RELATIONSHIP_ID, CREATION_DATE, CREATED_BY
444 , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
445 , REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID
446 , PROGRAM_UPDATE_DATE, QUOTE_LINE_ID, RELATED_QUOTE_LINE_ID
447 , RELATIONSHIP_TYPE_CODE, RECIPROCAL_FLAG, OBJECT_VERSION_NUMBER
448 FROM ASO_LINE_RELATIONSHIPS
449 WHERE quote_line_id IN ( SELECT quote_line_id
450 FROM aso_quote_lines_all
451 WHERE quote_header_id = x_quote_header_id )
452 AND relationship_type_code <> 'SERVICE';
453
454
455 CURSOR C_Serviceable_Product (
456 l_organization_id NUMBER
457 , l_inv_item_id NUMBER
458 ) IS
459 SELECT serviceable_product_flag, service_item_flag
460 FROM MTL_SYSTEM_ITEMS_VL
461 WHERE inventory_item_id = l_inv_item_id
462 AND organization_id = l_organization_id;
463
464 CURSOR C_Get_Ship_Id (
465 lc_line_id NUMBER
466 ) IS
467 SELECT shipment_id
468 FROM ASO_SHIPMENTS
469 WHERE quote_line_id = lc_line_id;
470
471 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
472 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
473 l_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
474 l_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
475 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
476 l_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
477 l_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
478 l_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
479 l_Price_Adj_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
480 l_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
481 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
482 l_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
483 lx_ln_Price_Adj_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
484 lx_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
485 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type;
486
487 l_line_index_link_tbl ASO_QUOTE_HEADERS_PVT.Index_Link_Tbl_Type;
488 l_qte_line_id NUMBER;
489 l_index NUMBER;
490 l_index_2 NUMBER;
491 l_service_ref_line_id NUMBER;
492 l_config_control_rec ASO_CFG_INT.Control_Rec_Type
493 := ASO_CFG_INT.G_MISS_Control_Rec;
494 l_old_config_header_id NUMBER;
495 l_old_config_revision_num NUMBER;
496 l_config_hdr_id NUMBER;
497 l_config_rev_nbr NUMBER;
498 LX_PRICE_ADJ_RLTSHIP_ID NUMBER;
499 LX_LINE_RELATIONSHIP_ID NUMBER;
500 X_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
501 X_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
502 X_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
503 l_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
504 l_quote_party_tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
505 l_quote_party_rec ASO_QUOTE_PUB.Quote_Party_rec_Type;
506 l_sales_credit_tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
507 l_sales_credit_rec ASO_QUOTE_PUB.Sales_Credit_rec_Type;
508 l_api_version CONSTANT NUMBER := 1.0;
509 l_api_name CONSTANT VARCHAR2 ( 30 ) := 'Copy_Line_Rows';
510 l_service_item_flag VARCHAR2 ( 1 );
511 l_serviceable_product_flag VARCHAR2 ( 1 );
512 l_return_status VARCHAR2 ( 1 );
513 l_api_version_number CONSTANT NUMBER := 1.0;
514 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
515 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
516 l_ship_id NUMBER;
517 l_orig_item_id_tbl CZ_API_PUB.number_tbl_type;
518 l_new_item_id_tbl CZ_API_PUB.number_tbl_type;
519 -- hyang: for bug 2692785
520 lx_status VARCHAR2(1);
521
522 l_dup_Price_Adj_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
523
524 --- New Code added for Copy Quote Line Functionality
525
526 CURSOR C_Validate_Quote_Line (
527 x_qte_header_id NUMBER,
528 x_qte_line_id NUMBER
529 ) IS
530 SELECT 'X'
531 FROM ASO_QUOTE_LINES_ALL
532 WHERE quote_header_id = x_qte_header_id
533 AND quote_line_id = x_qte_line_id;
534
535 CURSOR c_line_relation_from_line_id (
536 x_quote_line_id NUMBER
537 ) IS
538 SELECT LINE_RELATIONSHIP_ID, CREATION_DATE, CREATED_BY
539 , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
540 , REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID
541 , PROGRAM_UPDATE_DATE, QUOTE_LINE_ID, RELATED_QUOTE_LINE_ID
542 , RELATIONSHIP_TYPE_CODE, RECIPROCAL_FLAG, OBJECT_VERSION_NUMBER
543 FROM ASO_LINE_RELATIONSHIPS
544 WHERE relationship_type_code <> 'SERVICE'
545 CONNECT BY PRIOR related_quote_line_id = quote_line_id
546 START WITH quote_line_id = x_quote_line_id;
547
548 l_val varchar2(1);
549 -- l_appl_param_rec CZ_API_PUB.appl_param_rec_type;
550 l_last_update_date DATE;
551 l_line_number NUMBER;
552
553
554
555 x_qte_header_rec aso_quote_pub.qte_header_rec_type;
556 x_qte_line_tbl aso_quote_pub.qte_line_tbl_type;
557 x_qte_line_dtl_tbl aso_quote_pub.qte_line_dtl_tbl_type;
558 x_hd_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
559 x_hd_payment_tbl aso_quote_pub.payment_tbl_type;
560 x_hd_shipment_tbl aso_quote_pub.shipment_tbl_type;
561 x_hd_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
562 x_hd_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
563 x_line_attr_ext_tbl aso_quote_pub.line_attribs_ext_tbl_type;
564 x_line_rltship_tbl aso_quote_pub.line_rltship_tbl_type;
565 x_price_adjustment_tbl aso_quote_pub.price_adj_tbl_type;
566 x_price_adj_attr_tbl aso_quote_pub.price_adj_attr_tbl_type;
567 x_price_adj_rltship_tbl aso_quote_pub.price_adj_rltship_tbl_type;
568 x_ln_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
569 x_ln_payment_tbl aso_quote_pub.payment_tbl_type;
570 x_ln_shipment_tbl aso_quote_pub.shipment_tbl_type;
571 x_ln_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
572 x_ln_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
573 l_Qte_Header_Rec ASO_QUOTE_PUB.Qte_Header_Rec_Type := ASO_QUOTE_PUB.G_MISS_Qte_Header_Rec;
574
575 l_quote_party_tbl_out ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
576 l_sales_credit_tbl_out ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
577 l_tax_detail_tbl_out ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
578 l_freight_charge_tbl_out ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
579 l_shipment_tbl_out ASO_QUOTE_PUB.Shipment_Tbl_Type;
580 l_payment_tbl_out ASO_QUOTE_PUB.Payment_Tbl_Type;
581 l_Price_Adj_Attr_Tbl_out ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
582 l_Price_Attr_Tbl_out ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
583 l_qte_line_dtl_tbl_out ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
584 l_Line_Attr_Ext_Tbl_out ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
585 l_ato_model VARCHAR2(1) := FND_API.G_FALSE;
586
587 l_copy_flag varchar2(1):='T';
588
589
590 BEGIN
591
592 -- Standard Start of API savepoint
593 SAVEPOINT COPY_TMPL_LINES;
594
595 -- Standard call to check for call compatibility.
596 IF NOT FND_API.Compatible_API_Call (
597 l_api_version_number
598 , p_api_version_number
599 , l_api_name
600 , G_PKG_NAME
601 ) THEN
602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
603 END IF;
604
605 -- Initialize message list if p_init_msg_list is set to TRUE.
606 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
607 FND_MSG_PUB.initialize;
608 END IF;
609
610 -- Debug Message
611 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ) THEN
612 FND_MESSAGE.Set_Name ('ASO' , 'Copy Template Lines API: Start' );
613 FND_MSG_PUB.ADD;
614 END IF;
615
616 -- Initialize API return status to success
617 x_return_status := FND_API.G_RET_STS_SUCCESS;
618
619 --
620 -- API body
621 --
622
623 IF aso_debug_pub.g_debug_flag = 'Y' THEN
624 aso_debug_pub.ADD ('Copy_Template - Header and Lines' , 1, 'N' );
625 END IF;
626
627
628 l_qte_line_tbl := ASO_UTILITY_PVT.Query_Qte_Line_Rows ( p_qte_header_id );
629
630
631 FOR i IN 1 .. l_qte_line_tbl.COUNT LOOP
632 l_line_index_link_tbl ( l_qte_line_tbl ( i ).quote_line_id ) := FND_API.G_MISS_NUM;
633 END LOOP;
634
635
636
637 FOR i IN 1 .. l_qte_line_tbl.COUNT LOOP
638 l_copy_flag:='T';
639
640 IF l_qte_line_tbl ( i ).uom_code = 'ENR' THEN
641 l_copy_flag:='F';
642 END IF;
643
644 -- for promotional items
645 if l_qte_line_tbl ( i ).PRICING_LINE_TYPE_INDICATOR='F' then
646 l_copy_flag:='F';
647 END IF;
648
649 l_qte_line_tbl ( i ).quote_header_id := P_New_Qte_Header_Id;
650 l_qte_line_id := l_qte_line_tbl ( i ).quote_line_id;
651
652 -- Setting customer information, price list and currency to null bug 10212323
653 l_qte_line_tbl ( i ).INVOICE_TO_PARTY_SITE_ID := Null;
654 l_qte_line_tbl ( i ).INVOICE_TO_PARTY_ID := Null;
655 l_qte_line_tbl ( i ).INVOICE_TO_CUST_ACCOUNT_ID := Null;
656 l_qte_line_tbl ( i ).INVOICE_TO_CUST_PARTY_ID := Null;
657
658 l_qte_line_tbl ( i ).END_CUSTOMER_PARTY_ID := Null;
659 l_qte_line_tbl ( i ).END_CUSTOMER_PARTY_SITE_ID := Null;
660 l_qte_line_tbl ( i ).END_CUSTOMER_CUST_ACCOUNT_ID := Null;
661 l_qte_line_tbl ( i ).END_CUSTOMER_CUST_PARTY_ID := Null;
662
663 l_qte_line_tbl( i ).PRICE_LIST_ID := Null;
664 l_qte_line_tbl( i ).CURRENCY_CODE := Null;
665
666 -- Setting DFF information, to null (bug 12850154)
667 l_qte_line_tbl( i ).ATTRIBUTE_CATEGORY := Null;
668 l_qte_line_tbl( i ).ATTRIBUTE1 := Null;
669 l_qte_line_tbl( i ).ATTRIBUTE2 := Null;
670 l_qte_line_tbl( i ).ATTRIBUTE3 := Null;
671 l_qte_line_tbl( i ).ATTRIBUTE4 := Null;
672 l_qte_line_tbl( i ).ATTRIBUTE5 := Null;
673 l_qte_line_tbl( i ).ATTRIBUTE6 := Null;
674 l_qte_line_tbl( i ).ATTRIBUTE7 := Null;
675 l_qte_line_tbl( i ).ATTRIBUTE8 := Null;
676 l_qte_line_tbl( i ).ATTRIBUTE9 := Null;
677 l_qte_line_tbl( i ).ATTRIBUTE10 := Null;
678 l_qte_line_tbl( i ).ATTRIBUTE11 := Null;
679 l_qte_line_tbl( i ).ATTRIBUTE12 := Null;
680 l_qte_line_tbl( i ).ATTRIBUTE13 := Null;
681 l_qte_line_tbl( i ).ATTRIBUTE14 := Null;
682 l_qte_line_tbl( i ).ATTRIBUTE15 := Null;
683 l_qte_line_tbl( i ).ATTRIBUTE16 := Null;
684 l_qte_line_tbl( i ).ATTRIBUTE17 := Null;
685 l_qte_line_tbl( i ).ATTRIBUTE18 := Null;
686 l_qte_line_tbl( i ).ATTRIBUTE19 := Null;
687 l_qte_line_tbl( i ).ATTRIBUTE20 := Null;
688
689 IF aso_debug_pub.g_debug_flag = 'Y' THEN
690 aso_debug_pub.ADD ( 'qte line id = ' || l_qte_line_id, 1, 'N' );
691 aso_debug_pub.ADD ( 'i = ' || i, 1, 'N' );
692 aso_debug_pub.ADD ( 'item_type_code = ' || l_qte_line_tbl ( i ).item_type_code , 1 , 'N' );
693 aso_debug_pub.ADD ( 'l_copy_flag ='||l_copy_flag);
694 END IF;
695
696
697
698 IF (l_copy_flag = 'T') then
699 IF l_line_index_link_tbl ( l_qte_line_id ) = FND_API.G_MISS_NUM THEN
700
701 l_qte_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows ( l_qte_line_id );
702
703 -- for reconfigure from install base
704 IF l_qte_line_tbl(i).line_category_code = 'RETURN' THEN
705
706 IF ( l_qte_line_dtl_tbl(1).RETURN_REF_TYPE = 'SALES ORDER' AND l_qte_line_dtl_tbl(1).RETURN_REF_LINE_ID IS NOT NULL AND
707 l_qte_line_dtl_tbl(1).INSTANCE_ID IS NOT NULL ) OR ( l_qte_line_dtl_tbl(1).REF_TYPE_CODE = 'TOP_MODEL' ) THEN
708 l_copy_flag:='F';
709 IF aso_debug_pub.g_debug_flag = 'Y' THEN
710 aso_debug_pub.ADD ('Install Base Check Failed' , 1, 'N' );
711 END IF;
712
713 end if;
714 end if; -- end "RETURN"
715
716
717 -- for Trade in
718 IF (l_qte_line_tbl(i).item_type_code = 'STD' ) and ( l_qte_line_tbl(i).line_category_code = 'RETURN' )THEN
719 if (l_qte_line_dtl_tbl(1).INSTANCE_ID IS NOT NULL) then
720 l_copy_flag:='F';
721 IF aso_debug_pub.g_debug_flag = 'Y' THEN
722 aso_debug_pub.ADD ('Trade in from install Base Check Failed' , 1, 'N' );
723 END IF;
724 end if;
725 end if; -- Trade in
726
727
728
729
730
731
732 IF (l_qte_line_tbl ( i ).item_type_code = 'MDL' ) and (l_copy_flag='T') THEN
733
734 IF l_qte_line_dtl_tbl.COUNT > 0 THEN
735
736 IF l_qte_line_dtl_tbl ( 1 ).config_header_id IS NOT NULL
737 AND l_qte_line_dtl_tbl ( 1 ).config_revision_num IS NOT NULL THEN
738
739 l_config_control_rec.new_config_flag := FND_API.G_TRUE;
740
741 -- set the flag for ato model
742 IF (l_qte_line_dtl_tbl(1).ato_line_id is not null and l_qte_line_dtl_tbl(1).ato_line_id <> fnd_api.g_miss_num) then
743 l_ato_model := fnd_api.g_true;
744 end if;
745
746
747 IF aso_debug_pub.g_debug_flag = 'Y' THEN
748 aso_debug_pub.ADD ( 'Before Calling ASO_CGF_INT.Copy Configuration' , 1 , 'N' );
749 END IF;
750
751 ASO_CFG_INT.Copy_Configuration (
752 P_Api_version_NUmber => 1.0
753 , P_config_header_id => l_qte_line_dtl_tbl ( 1 ).config_header_id
754 , p_config_revision_num => l_qte_line_dtl_tbl ( 1 ).config_revision_num
755 , p_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE
756 , x_config_header_id => l_config_hdr_id
757 , x_config_revision_num => l_config_rev_nbr
758 , x_orig_item_id_tbl => l_orig_item_id_tbl
759 , x_new_item_id_tbl => l_new_item_id_tbl
760 , x_return_status => l_return_status
761 , x_msg_count => x_msg_count
762 , x_msg_data => x_msg_data
763 , p_autonomous_flag => FND_API.G_FALSE
764 );
765
766 IF aso_debug_pub.g_debug_flag = 'Y' THEN
767 aso_debug_pub.ADD ( 'After Calling ASO_CGF_INT.Copy Configuration' , 1 , 'N' );
768 END IF;
769
770 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
771 l_old_config_header_id :=
772 l_qte_line_dtl_tbl ( 1 ).config_header_id;
773 l_old_config_revision_num :=
774 l_qte_line_dtl_tbl ( 1 ).config_revision_num;
775 l_qte_line_dtl_tbl ( 1 ).config_header_id :=
776 l_config_hdr_id;
777 l_qte_line_dtl_tbl ( 1 ).config_revision_num :=
778 l_config_rev_nbr;
779 ELSE
780 x_return_status := FND_API.G_RET_STS_ERROR;
781 RAISE FND_API.G_EXC_ERROR;
782 END IF;
783
784 END IF; -- config_header_id
785
786 END IF; -- line_dtl_tbl.count
787
788 END IF; -- 'MDL'
789
790 l_serviceable_product_flag := l_qte_line_tbl ( i ).SERVICEABLE_PRODUCT_FLAG;
791 l_service_item_flag := l_qte_line_tbl ( i ).service_item_flag;
792
793 IF aso_debug_pub.g_debug_flag = 'Y' THEN
794 aso_debug_pub.ADD ( 'service item flag = ' || l_service_item_flag , 1 , 'N' );
795 aso_debug_pub.ADD ( 'serviceable_product_flag = ' || l_serviceable_product_flag , 1 , 'N' );
796 END IF;
797
798
799
800 -- Only copy only with no service reference and service reference with current quote
801 IF l_service_item_flag = 'Y'
802 AND (l_qte_line_dtl_tbl ( 1 ).service_ref_type_code IS NULL OR
803 l_qte_line_dtl_tbl ( 1 ).service_ref_type_code <> 'QUOTE' )
804 THEN
805 l_service_item_flag := 'N';
806 -- Do not copy service ref type other than quote
807 if (l_qte_line_dtl_tbl ( 1 ).service_ref_type_code IS NOT NULL) then
808 l_copy_flag := 'F';
809 end if;
810 END IF;
811
812 IF aso_debug_pub.g_debug_flag = 'Y' THEN
813 aso_debug_pub.ADD ( 'service item flag 2= ' || l_service_item_flag , 1 , 'N' );
814 END IF;
815
816
817
818
819
820
821 IF l_qte_line_tbl ( i ).item_type_code <> 'CFG'
822 AND l_qte_line_tbl ( i ).item_type_code <> 'OPT'
823 AND l_service_item_flag <> 'Y'
824 AND (l_copy_flag='T') THEN
825
826 -- Setting Pricing parameters as null
827 l_qte_line_tbl(i).line_list_price:=FND_API.G_MISS_NUM;
828 l_qte_line_tbl(i).line_adjusted_amount:=FND_API.G_MISS_NUM;
829 l_qte_line_tbl(i).line_Quote_price:=FND_API.G_MISS_NUM;
830 l_qte_line_tbl(i).priced_price_list_id:=FND_API.G_MISS_NUM;
831 l_qte_line_tbl(i).pricing_quantity_uom:=null;
832 l_qte_line_tbl(i).pricing_quantity:=FND_API.G_MISS_NUM;
833
834
835 -- Setting the shipment data
836 l_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows(p_qte_header_id, L_QTE_LINE_ID);
837 l_qte_line_tbl(i).quote_line_id := NULL;
838
839 l_qte_line_tbl ( i ).object_version_number := FND_API.G_MISS_NUM;
840
841 FOR j IN 1 .. l_shipment_tbl.COUNT LOOP
842 l_shipment_tbl( j ).QUOTE_HEADER_ID := p_new_qte_header_id;
843 l_shipment_tbl( j ).shipment_id := null;
844 l_shipment_tbl( j ).object_version_number := FND_API.G_MISS_NUM;
845 l_shipment_tbl( j ).SHIP_TO_CUST_ACCOUNT_ID := Null;
846 l_shipment_tbl( j ).SHIP_TO_CUST_PARTY_ID := Null;
847 l_shipment_tbl( j ).SHIP_TO_PARTY_ID := Null;
848 l_shipment_tbl( j ).SHIP_TO_PARTY_SITE_ID := Null;
849 l_shipment_tbl( j ).SHIP_TO_PARTY_NAME := Null;
850 l_shipment_tbl( j ).SHIP_TO_CONTACT_FIRST_NAME := Null;
851 l_shipment_tbl( j ).SHIP_TO_CONTACT_MIDDLE_NAME := Null;
852 l_shipment_tbl( j ).SHIP_TO_CONTACT_LAST_NAME := Null;
853 l_shipment_tbl( j ).SHIP_TO_ADDRESS1 := Null;
854 l_shipment_tbl( j ).SHIP_TO_ADDRESS2 := Null;
855 l_shipment_tbl( j ).SHIP_TO_ADDRESS3 := Null;
856 l_shipment_tbl( j ).SHIP_TO_ADDRESS4 := Null;
857 l_shipment_tbl( j ).SHIP_TO_COUNTRY_CODE := Null;
858 l_shipment_tbl( j ).SHIP_TO_COUNTRY := Null;
859 l_shipment_tbl( j ).SHIP_TO_CITY := Null;
860 l_shipment_tbl( j ).SHIP_TO_POSTAL_CODE := Null;
861 l_shipment_tbl( j ).SHIP_TO_STATE := Null;
862 l_shipment_tbl( j ).SHIP_TO_PROVINCE := Null;
863 l_shipment_tbl( j ).SHIP_TO_COUNTY := Null;
864 l_shipment_tbl( j ).FREIGHT_TERMS_CODE := Null;
865 l_shipment_tbl( j ).FOB_CODE := Null;
866 l_shipment_tbl( j ).DEMAND_CLASS_CODE := Null;
867 l_shipment_tbl( j ).REQUEST_DATE_TYPE := Null;
868 l_shipment_tbl( j ).REQUEST_DATE := Null;
869 l_shipment_tbl( j ).SHIPMENT_PRIORITY_CODE := Null;
870 l_shipment_tbl( j ).SHIPPING_INSTRUCTIONS := Null;
871 l_shipment_tbl( j ).PACKING_INSTRUCTIONS := Null;
872 END LOOP;
873
874
875
876
877 --BC4J Fix
878
879 FOR j IN 1 .. l_qte_line_dtl_tbl.COUNT LOOP
880 l_qte_line_dtl_tbl(j).quote_line_detail_id := null;
881 l_qte_line_dtl_tbl(j).object_version_number := FND_API.G_MISS_NUM;
882 l_qte_line_dtl_tbl(j).top_model_line_id := null;
883 l_qte_line_dtl_tbl(j).ato_line_id := null;
884 l_qte_line_dtl_tbl(j).qte_line_index := i;
885 END LOOP;
886
887
888
889
890
891 IF aso_debug_pub.g_debug_flag = 'Y' THEN
892 aso_debug_pub.ADD ( 'Copy_Line_Rows - Before insert_quote_line_rows: ' || l_qte_line_id , 1 , 'Y' );
893 END IF;
894
895
896
897
898
899 ASO_QUOTE_LINES_PVT.Insert_Quote_Line_Rows (
900 p_control_rec => l_control_rec
901 , P_qte_Line_Rec => l_qte_line_tbl ( i )
902 , P_qte_line_dtl_tbl => l_qte_line_dtl_tbl
903 , P_Line_Attribs_Ext_Tbl => l_line_attr_ext_tbl
904 , P_price_attributes_tbl => l_price_attr_tbl
905 , P_Price_Adj_Tbl => l_price_adj_tbl
906 , P_Price_Adj_Attr_Tbl => l_Price_Adj_Attr_Tbl
907 , P_Payment_Tbl => ASO_QUOTE_PUB.g_miss_payment_tbl
908 , P_Shipment_Tbl => l_shipment_tbl
909 , P_Freight_Charge_Tbl => l_freight_charge_tbl
910 , P_Tax_Detail_Tbl => l_tax_detail_tbl
911 , P_Sales_Credit_Tbl => l_sales_credit_tbl
912 , P_Quote_Party_Tbl => l_quote_party_tbl
913 , x_qte_Line_Rec => lx_qte_line_rec
914 , x_qte_line_dtl_tbl => l_qte_line_dtl_tbl_out
915 , x_Line_Attribs_Ext_Tbl => l_line_attr_Ext_Tbl_out
916 , x_price_attributes_tbl => l_price_attr_tbl_out
917 , x_Price_Adj_Tbl => lx_ln_price_adj_tbl
918 , x_Price_Adj_Attr_Tbl => l_Price_Adj_Attr_Tbl_out
919 , x_Payment_Tbl => l_payment_tbl_out
920 , x_Shipment_Tbl => l_shipment_tbl_out
921 , x_Freight_Charge_Tbl => l_freight_charge_tbl_out
922 , x_Tax_Detail_Tbl => l_tax_detail_tbl_out
923 , x_Sales_Credit_Tbl => l_sales_credit_tbl_out
924 , x_Quote_Party_Tbl => l_quote_party_tbl_out
925 , x_Return_Status => l_return_status
926 , x_Msg_Count => x_msg_count
927 , x_Msg_Data => x_msg_data
928 );
929
930 l_quote_party_tbl :=l_quote_party_tbl_out ;
931 l_sales_credit_tbl :=l_sales_credit_tbl_out ;
932 l_tax_detail_tbl := l_tax_detail_tbl_out ;
933 l_freight_charge_tbl := l_freight_charge_tbl_out ;
934 l_shipment_tbl := l_shipment_tbl_out ;
935 l_payment_tbl := l_payment_tbl_out ;
936 l_Price_Adj_Attr_Tbl := l_Price_Adj_Attr_Tbl_out ;
937 l_Price_Attr_Tbl := l_Price_Attr_Tbl_out ;
938 l_qte_line_dtl_tbl := l_qte_line_dtl_tbl_out ;
939 l_Line_Attr_Ext_Tbl := l_Line_Attr_Ext_Tbl_out;
940
941
942 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
944 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
945 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_UNEXP_ERROR' );
946 FND_MESSAGE.Set_Token ( 'ROW' , 'ASO_COPYLINE AFTER_INSERT' , TRUE );
947 FND_MSG_PUB.ADD;
948 END IF;
949 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
950 ELSIF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
951 x_return_status := FND_API.G_RET_STS_ERROR;
952 RAISE FND_API.G_EXC_ERROR;
953 END IF;
954 IF aso_debug_pub.g_debug_flag = 'Y' THEN
955 aso_debug_pub.ADD ( 'Copy_Template - After insert_quote_line_rows - status: ' || l_return_status , 1 , 'Y' );
956 END IF;
957
958 IF aso_debug_pub.g_debug_flag = 'Y' THEN
959 aso_debug_pub.ADD ( 'Copy_Template - Updating the top model and ato line id for the top model line ', 1 , 'Y' );
960 aso_debug_pub.ADD ( 'Copy_Template - l_ato_model: ' || l_ato_model , 1 , 'Y' );
961 END IF;
962 update aso_quote_line_details
963 set top_model_line_id = lx_qte_line_rec.quote_line_id,
964 ato_line_id = decode(l_ato_model,fnd_api.g_true,lx_qte_line_rec.quote_line_id,null)
965 where quote_line_id = lx_qte_line_rec.quote_line_id;
966
967
968 l_line_index_link_tbl ( l_qte_line_id ) :=
969 lx_qte_line_rec.quote_line_id;
970 IF aso_debug_pub.g_debug_flag = 'Y' THEN
971 aso_debug_pub.ADD ( 'Copy_Config - l_qte_line_tbl(i).item_type_code ' || l_qte_line_tbl ( i ).item_type_code , 1 , 'Y' );
972 aso_debug_pub.ADD ( 'Copy - l_qte_line_tbl(i).inventory_item_id ' || l_qte_line_tbl ( i ).inventory_item_id , 1 , 'Y' );
973 aso_debug_pub.ADD ( 'Copy - l_serviceable_product_flag ' || l_serviceable_product_flag , 1 , 'Y' );
974 END IF;
975
976
977 -- code for copying service
978
979 IF (l_serviceable_product_flag = 'Y' ) and (l_copy_flag='T') THEN
980
981 IF aso_debug_pub.g_debug_flag = 'Y' THEN
982 aso_debug_pub.ADD ( 'Before Calling Service Copy ' , 1 , 'N' );
983 END IF;
984
985 Copy_Tmpl_Service (
986 p_qte_line_id => l_qte_line_id
987 , p_new_qte_header_id => p_new_qte_header_id
988 , p_qte_header_id => p_qte_header_id
989 , lx_line_index_link_tbl => l_line_index_link_tbl
990 , X_Return_Status => l_return_status
991 , X_Msg_Count => x_msg_count
992 , X_Msg_Data => x_msg_data
993 );
994
995
996
997 IF aso_debug_pub.g_debug_flag = 'Y' THEN
998 aso_debug_pub.ADD ( ' After Calling Service Copy' , 1 , 'N' );
999 END IF;
1000
1001
1002 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1003 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1004 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
1005 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_UNEXP_ERROR' );
1006 FND_MESSAGE.Set_Token ( 'ROW' , 'ASO_COPYLINE AFTER_SERVICE' , TRUE );
1007 FND_MSG_PUB.ADD;
1008 END IF;
1009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1010 ELSIF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1011 x_return_status := FND_API.G_RET_STS_ERROR;
1012 RAISE FND_API.G_EXC_ERROR;
1013 END IF;
1014
1015 END IF;
1016
1017
1018
1019
1020
1021
1022 END IF; -- If <> CFG and OPT
1023
1024
1025
1026 IF (l_qte_line_tbl ( i ).item_type_code = 'MDL' ) and (l_copy_flag='T') THEN
1027 IF l_qte_line_dtl_tbl.COUNT > 1 THEN
1028 FOR k IN 2 .. l_qte_line_dtl_tbl.COUNT LOOP
1029 l_qte_line_dtl_tbl ( k ).config_header_id :=
1030 l_config_hdr_id;
1031 l_qte_line_dtl_tbl ( k ).config_revision_num :=
1032 l_config_rev_nbr;
1033 END LOOP;
1034 END IF;
1035
1036 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1037 aso_debug_pub.ADD ( 'l_old_config_header_id = ' || l_old_config_header_id , 1 , 'N' );
1038 aso_debug_pub.ADD ( ' Before Calling Tmpl Quote Config Copy' , 1 , 'N' );
1039 END IF;
1040
1041 Config_Copy_Tmpl (
1042 p_old_config_header_id => l_old_config_header_id
1043 , p_old_config_revision_num => l_old_config_revision_num
1044 , p_config_header_id => l_config_hdr_id
1045 , p_config_revision_num => l_config_rev_nbr
1046 , p_new_qte_header_id => p_new_qte_header_id
1047 , p_qte_header_id => p_qte_header_id
1048 , lx_line_index_link_tbl => l_line_index_link_tbl
1049 -- , lx_price_index_link_tbl => l_price_index_link_tbl
1050 , X_Return_Status => l_return_status
1051 , X_Msg_Count => x_msg_count
1052 , X_Msg_Data => x_msg_data
1053 );
1054
1055 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1056 aso_debug_pub.ADD ( ' After Calling Copy Tmp Config Copy' , 1 , 'N' );
1057 END IF;
1058
1059
1060 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1062 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
1063 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_UNEXP_ERROR' );
1064 FND_MESSAGE.Set_Token ( 'ROW' , 'ASO_COPYLINE AFTER_CONFIG_COPY' , TRUE );
1065 FND_MSG_PUB.ADD;
1066 END IF;
1067 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1068 ELSIF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1069 x_return_status := FND_API.G_RET_STS_ERROR;
1070 RAISE FND_API.G_EXC_ERROR;
1071 END IF;
1072
1073 /* bug 1903605*/
1074 l_old_config_header_id := NULL;
1075 l_old_config_revision_num := NULL;
1076 /* bug 1903605*/
1077
1078 IF (l_orig_item_id_tbl IS NOT NULL) AND (l_new_item_id_tbl IS NOT NULL) THEN
1079
1080 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1081 aso_debug_pub.ADD ( 'item_id_count > 0 ', 1 , 'N' );
1082 aso_debug_pub.ADD ( 'Orig Tbl Count ' ||l_orig_item_id_tbl.count, 1 , 'N' );
1083 aso_debug_pub.ADD ( 'New Tbl Count '||l_new_item_id_tbl.count, 1 , 'N' );
1084
1085 END IF;
1086
1087 IF l_orig_item_id_tbl.count > 0 AND l_new_item_id_tbl.count > 0 THEN
1088
1089 FORALL i IN l_orig_item_id_tbl.FIRST..l_orig_item_id_tbl.LAST
1090 UPDATE aso_quote_line_details
1091 SET config_item_id = l_new_item_id_tbl(i)
1092 ,last_update_date = SYSDATE
1093 ,last_updated_by =G_USER_ID
1094 ,last_update_login = G_LOGIN_ID
1095
1096 WHERE config_header_id = l_config_hdr_id
1097 AND config_revision_num = l_config_rev_nbr
1098 AND config_item_id = l_orig_item_id_tbl(i);
1099
1100 END IF;
1101
1102 END IF;
1103
1104 END IF; -- 'MDL'
1105
1106
1107
1108
1109 END IF; -- checking index link tbl
1110
1111 END IF; -- for copy_flag
1112
1113
1114 END LOOP;
1115
1116
1117 -- copy line relationships
1118
1119 FOR line_rel_rec IN c_line_relation ( p_qte_header_id ) LOOP
1120 lx_LINE_RELATIONSHIP_ID := FND_API.G_MISS_NUM;
1121 ASO_LINE_RELATIONSHIPS_PKG.Insert_Row (
1122 px_LINE_RELATIONSHIP_ID => lx_LINE_RELATIONSHIP_ID
1123 , p_CREATION_DATE => SYSDATE
1124 , p_CREATED_BY => G_USER_ID
1125 , p_LAST_UPDATED_BY => G_USER_ID
1126 , p_LAST_UPDATE_DATE => SYSDATE
1127 , p_LAST_UPDATE_LOGIN => G_LOGIN_ID
1128 , p_REQUEST_ID => line_rel_rec.REQUEST_ID
1129 , p_PROGRAM_APPLICATION_ID => line_rel_rec.PROGRAM_APPLICATION_ID
1130 , p_PROGRAM_ID => line_rel_rec.PROGRAM_ID
1131 , p_PROGRAM_UPDATE_DATE => line_rel_rec.PROGRAM_UPDATE_DATE
1132 , p_QUOTE_LINE_ID => l_line_index_link_tbl ( line_rel_rec.quote_line_id )
1133 , p_RELATED_QUOTE_LINE_ID => l_line_index_link_tbl ( line_rel_rec.related_quote_line_id )
1134 , p_RECIPROCAL_FLAG => line_rel_rec.RECIPROCAL_FLAG
1135 , P_RELATIONSHIP_TYPE_CODE => line_rel_rec.RELATIONSHIP_TYPE_CODE
1136 , p_OBJECT_VERSION_NUMBER => line_rel_rec.OBJECT_VERSION_NUMBER
1137 );
1138 END LOOP;
1139 --end if;
1140
1141
1142
1143
1144 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1145 aso_debug_pub.ADD ( ' End Copy template Lines API ', 1 , 'N' );
1146 END IF;
1147
1148 x_return_status := FND_API.G_RET_STS_SUCCESS;
1149
1150 --
1151 -- End of API body
1152 --
1153
1154 -- Standard check for p_commit
1155 IF FND_API.to_Boolean ( p_commit ) THEN
1156 COMMIT WORK;
1157 END IF;
1158
1159 -- Standard call to get message count and if count is 1, get message info.
1160 FND_MSG_PUB.Count_And_Get (
1161 p_count => x_msg_count
1162 , p_data => x_msg_data
1163 );
1164
1165 EXCEPTION
1166
1167 WHEN FND_API.G_EXC_ERROR THEN
1168 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS (
1169 P_API_NAME => L_API_NAME
1170 , P_PKG_NAME => G_PKG_NAME
1171 , P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1172 , P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1173 , X_MSG_COUNT => X_MSG_COUNT
1174 , X_MSG_DATA => X_MSG_DATA
1175 , X_RETURN_STATUS => X_RETURN_STATUS
1176 );
1177
1178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1179 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS (
1180 P_API_NAME => L_API_NAME
1181 , P_PKG_NAME => G_PKG_NAME
1182 , P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1183 , P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1184 , X_MSG_COUNT => X_MSG_COUNT
1185 , X_MSG_DATA => X_MSG_DATA
1186 , X_RETURN_STATUS => X_RETURN_STATUS
1187 );
1188
1189 WHEN OTHERS THEN
1190 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS (
1191 P_API_NAME => L_API_NAME
1192 , P_PKG_NAME => G_PKG_NAME
1193 , P_SQLCODE => SQLCODE
1194 , P_SQLERRM => SQLERRM
1195 , P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1196 , P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1197 , X_MSG_COUNT => X_MSG_COUNT
1198 , X_MSG_DATA => X_MSG_DATA
1199 , X_RETURN_STATUS => X_RETURN_STATUS
1200 );
1201
1202
1203
1204 end Copy_Tmpl_Lines;
1205
1206 PROCEDURE Copy_Tmpl_Service(
1207 p_qte_line_id IN NUMBER
1208 , p_new_qte_header_id IN NUMBER
1209 , p_qte_header_id IN NUMBER
1210 , lx_line_index_link_tbl IN OUT NOCOPY ASO_QUOTE_HEADERS_PVT.Index_Link_Tbl_Type
1211 , X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1212 , X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER
1213 , X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1214
1215 ) IS
1216
1217 CURSOR line_id_from_service (
1218 qte_ln_id NUMBER
1219 ) IS
1220 SELECT related_quote_line_id
1221 FROM aso_line_relationships
1222 WHERE quote_line_id = qte_ln_id
1223 AND relationship_type_code = 'SERVICE';
1224
1225 l_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
1226 l_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1227 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1228 l_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
1229 l_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
1230 l_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1231 l_Price_Adj_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1232 l_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
1233 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1234 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
1235 l_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
1236 lx_ln_Price_Adj_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1237 lx_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
1238 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type;
1239 LX_PRICE_ADJ_RLTSHIP_ID NUMBER;
1240 LX_LINE_RELATIONSHIP_ID NUMBER;
1241 X_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
1242 X_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
1243 X_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
1244 l_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
1245 l_quote_party_tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
1246 l_quote_party_rec ASO_QUOTE_PUB.Quote_Party_rec_Type;
1247 l_sales_credit_tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
1248 l_sales_credit_rec ASO_QUOTE_PUB.Sales_Credit_rec_Type;
1249 l_service_ref_line_id NUMBER;
1250 l_return_status VARCHAR2 ( 1 );
1251 qte_line_id NUMBER;
1252 i NUMBER;
1253 j NUMBER;
1254 k NUMBER;
1255 l_api_version CONSTANT NUMBER := 1.0;
1256
1257 Cursor get_qte_line_number (x_qte_line_id NUMBER) IS
1258 SELECT line_number
1259 FROM aso_quote_lines_all
1260 WHERE quote_line_id = x_qte_line_id ;
1261
1262 l_line_number NUMBER;
1263
1264 l_quote_party_tbl_out ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
1265 l_sales_credit_tbl_out ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
1266 l_tax_detail_tbl_out ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
1267 l_freight_charge_tbl_out ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
1268 l_shipment_tbl_out ASO_QUOTE_PUB.Shipment_Tbl_Type;
1269 l_payment_tbl_out ASO_QUOTE_PUB.Payment_Tbl_Type;
1270 l_Price_Adj_Attr_Tbl_out ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
1271 l_Price_Attr_Tbl_out ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1272 l_qte_line_dtl_tbl_out ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1273 l_Line_Attr_Ext_Tbl_out ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
1274
1275 l_dup_Price_Adj_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1276
1277
1278 BEGIN
1279
1280 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1281 aso_debug_pub.ADD ('Copy_Tmpl_Service - Begin ' , 1, 'Y' );
1282 aso_debug_pub.ADD ( 'Copy_Tmpl_Service - p_new_qte_header_id ' || p_new_qte_header_id , 1 , 'Y' );
1283 aso_debug_pub.ADD ( 'Copy_Tmpl_Service - p_qte_header_id ' || p_qte_header_id , 1 , 'Y' );
1284 aso_debug_pub.ADD ( 'Copy_Tmpl_Service - p_qte_line_id ' || p_qte_line_id , 1 , 'Y' );
1285 END IF;
1286
1287 x_return_status := FND_API.G_RET_STS_SUCCESS;
1288
1289 OPEN line_id_from_service ( p_qte_line_id );
1290
1291 LOOP
1292 FETCH line_id_from_service INTO qte_line_id;
1293 EXIT WHEN line_id_from_service%NOTFOUND;
1294 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1295 aso_debug_pub.ADD ( 'Copy_Tmpl_Service - inside cursor qte_line_id ' || qte_line_id , 1 , 'Y' );
1296 END IF;
1297
1298 l_qte_line_rec :=
1299 ASO_UTILITY_PVT.Query_Qte_Line_Row ( qte_line_id );
1300
1301 IF p_new_qte_header_id = p_qte_header_id THEN
1302
1303 OPEN get_qte_line_number(lx_line_index_link_tbl(p_qte_line_id));
1304 FETCH get_qte_line_number into l_line_number;
1305 CLOSE get_qte_line_number;
1306
1307 l_qte_line_rec.line_number := l_line_number;
1308
1309 END IF;
1310
1311
1312 l_qte_line_rec.quote_header_id := p_new_qte_header_id;
1313 l_qte_line_dtl_tbl :=
1314 ASO_UTILITY_PVT.Query_Line_Dtl_Rows ( qte_line_id );
1315
1316 IF l_qte_line_dtl_tbl.COUNT > 0 THEN
1317
1318 FOR k IN 1 .. l_qte_line_dtl_tbl.COUNT LOOP
1319
1320
1321 IF l_qte_line_dtl_tbl ( k ).service_ref_type_code = 'QUOTE' THEN
1322
1323 IF l_qte_line_dtl_tbl ( k ).service_ref_line_id IS NOT NULL THEN
1324
1325 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1326 aso_debug_pub.ADD ( 'Copy_Tmpl_Service - l_qte_line_dtl_tbl(k).service_ref_line_id ' || l_qte_line_dtl_tbl ( k ).service_ref_line_id , 1 , 'Y' );
1327 END IF;
1328 l_service_ref_line_id :=
1329 lx_line_index_link_tbl ( l_qte_line_dtl_tbl ( k ).service_ref_line_id );
1330 l_qte_line_dtl_tbl ( k ).service_ref_line_id :=
1331 l_service_ref_line_id;
1332 END IF;
1333
1334 END IF;
1335
1336 END LOOP;
1337
1338 END IF;
1339
1340 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1341 aso_debug_pub.ADD ( 'Copy_Tmpl_Service - 2 l_service_ref_line_id ' || l_service_ref_line_id , 1 , 'Y' );
1342 END IF;
1343
1344 --l_line_attr_Ext_Tbl := ASO_UTILITY_PVT.Query_Line_Attribs_Ext_Rows ( qte_line_id );
1345
1346 --l_price_adj_tbl := ASO_UTILITY_PVT.Query_Price_Adj_Rows ( p_qte_header_id , qte_line_id );
1347
1348 -- l_dup_price_adj_tbl := l_price_adj_tbl;
1349
1350 --l_price_adj_attr_tbl := ASO_UTILITY_PVT.Query_Price_Adj_Attr_Rows ( p_price_adj_tbl => l_price_adj_tbl );
1351
1352 --l_price_attr_tbl := ASO_UTILITY_PVT.Query_Price_Attr_Rows ( p_qte_header_id , qte_line_id );
1353
1354 --l_payment_tbl := ASO_UTILITY_PVT.Query_Payment_Rows ( p_qte_header_id , QTE_LINE_ID );
1355
1356 l_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows ( p_qte_header_id , QTE_LINE_ID );
1357
1358 --l_sales_credit_tbl := ASO_UTILITY_PVT.Query_Sales_Credit_Row ( p_qte_header_id , QTE_LINE_ID );
1359
1360 --l_quote_party_tbl := ASO_UTILITY_PVT.Query_Quote_Party_Row ( p_qte_header_id , QTE_LINE_ID );
1361
1362 --l_freight_charge_tbl := ASO_UTILITY_PVT.Query_Freight_Charge_Rows ( l_shipment_tbl );
1363
1364 --l_tax_detail_tbl := ASO_UTILITY_PVT.Query_Tax_Detail_Rows ( p_qte_header_id , QTE_LINE_ID , l_shipment_tbl );
1365
1366 l_qte_line_rec.quote_line_id := NULL;
1367 l_qte_line_rec.object_version_number := FND_API.G_MISS_NUM;
1368
1369 --Setting line attributes as null as they are not required for template 10212323
1370
1371 l_qte_line_rec.INVOICE_TO_PARTY_SITE_ID := Null;
1372 l_qte_line_rec.INVOICE_TO_PARTY_ID := Null;
1373 l_qte_line_rec.INVOICE_TO_CUST_ACCOUNT_ID := Null;
1374 l_qte_line_rec.INVOICE_TO_CUST_PARTY_ID := Null;
1375
1376 l_qte_line_rec.END_CUSTOMER_PARTY_ID := Null;
1377 l_qte_line_rec.END_CUSTOMER_PARTY_SITE_ID := Null;
1378 l_qte_line_rec.END_CUSTOMER_CUST_ACCOUNT_ID := Null;
1379 l_qte_line_rec.END_CUSTOMER_CUST_PARTY_ID := Null;
1380
1381 l_qte_line_rec.PRICE_LIST_ID := Null;
1382 l_qte_line_rec.CURRENCY_CODE := Null;
1383
1384 l_qte_line_rec.line_list_price:= NULL;
1385 l_qte_line_rec.line_adjusted_amount:= NULL;
1386 l_qte_line_rec.line_Quote_price:= NULL;
1387 l_qte_line_rec.priced_price_list_id:= NULL;
1388 l_qte_line_rec.pricing_quantity_uom:= null;
1389 l_qte_line_rec.pricing_quantity:= NULL;
1390
1391
1392 --BC4J Fix
1393
1394 FOR j IN 1 .. l_qte_line_dtl_tbl.COUNT LOOP
1395 l_qte_line_dtl_tbl(j).quote_line_detail_id := null;
1396 l_qte_line_dtl_tbl(j).object_version_number := FND_API.G_MISS_NUM;
1397 END LOOP;
1398
1399 /*FOR j IN 1 .. l_price_adj_tbl.COUNT LOOP
1400 l_price_adj_tbl ( j ).QUOTE_HEADER_ID := p_new_qte_header_id;
1401 l_price_adj_tbl ( j ).price_adjustment_id := null;
1402 l_price_adj_tbl ( j ).object_version_number := FND_API.G_MISS_NUM;
1403 END LOOP;
1404
1405 FOR j IN 1 .. l_price_adj_attr_tbl.COUNT LOOP
1406 l_price_adj_attr_tbl(j).price_adj_attrib_id := null;
1407 l_price_adj_attr_tbl(j).object_version_number := FND_API.G_MISS_NUM;
1408 END LOOP;
1409
1410 FOR j IN 1 .. l_price_attr_tbl.COUNT LOOP
1411 l_price_attr_tbl ( j ).QUOTE_HEADER_ID := p_new_qte_header_id;
1412 l_price_attr_tbl ( j ).price_attribute_id := null;
1413 l_price_attr_tbl ( j ).object_version_number := FND_API.G_MISS_NUM;
1414 END LOOP;
1415
1416 */
1417 FOR j IN 1 .. l_shipment_tbl.COUNT LOOP
1418 l_shipment_tbl( j ).QUOTE_HEADER_ID := p_new_qte_header_id;
1419 l_shipment_tbl( j ).shipment_id := null;
1420 l_shipment_tbl( j ).object_version_number := FND_API.G_MISS_NUM;
1421 l_shipment_tbl( j ).SHIP_TO_CUST_ACCOUNT_ID := Null;
1422 l_shipment_tbl( j ).SHIP_TO_CUST_PARTY_ID := Null;
1423 l_shipment_tbl( j ).SHIP_TO_PARTY_ID := Null;
1424 l_shipment_tbl( j ).SHIP_TO_PARTY_SITE_ID := Null;
1425 l_shipment_tbl( j ).SHIP_TO_PARTY_NAME := Null;
1426 l_shipment_tbl( j ).SHIP_TO_CONTACT_FIRST_NAME := Null;
1427 l_shipment_tbl( j ).SHIP_TO_CONTACT_MIDDLE_NAME := Null;
1428 l_shipment_tbl( j ).SHIP_TO_CONTACT_LAST_NAME := Null;
1429 l_shipment_tbl( j ).SHIP_TO_ADDRESS1 := Null;
1430 l_shipment_tbl( j ).SHIP_TO_ADDRESS2 := Null;
1431 l_shipment_tbl( j ).SHIP_TO_ADDRESS3 := Null;
1432 l_shipment_tbl( j ).SHIP_TO_ADDRESS4 := Null;
1433 l_shipment_tbl( j ).SHIP_TO_COUNTRY_CODE := Null;
1434 l_shipment_tbl( j ).SHIP_TO_COUNTRY := Null;
1435 l_shipment_tbl( j ).SHIP_TO_CITY := Null;
1436 l_shipment_tbl( j ).SHIP_TO_POSTAL_CODE := Null;
1437 l_shipment_tbl( j ).SHIP_TO_STATE := Null;
1438 l_shipment_tbl( j ).SHIP_TO_PROVINCE := Null;
1439 l_shipment_tbl( j ).SHIP_TO_COUNTY := Null;
1440 l_shipment_tbl( j ).FREIGHT_TERMS_CODE := Null;
1441 l_shipment_tbl( j ).FOB_CODE := Null;
1442 l_shipment_tbl( j ).DEMAND_CLASS_CODE := Null;
1443 l_shipment_tbl( j ).REQUEST_DATE_TYPE := Null;
1444 l_shipment_tbl( j ).REQUEST_DATE := Null;
1445 l_shipment_tbl( j ).SHIPMENT_PRIORITY_CODE := Null;
1446 l_shipment_tbl( j ).SHIPPING_INSTRUCTIONS := Null;
1447 l_shipment_tbl( j ).PACKING_INSTRUCTIONS := Null;
1448 END LOOP;
1449
1450 /*
1451 FOR j IN 1 .. l_sales_credit_tbl.COUNT LOOP
1452 l_sales_credit_tbl(j).QUOTE_HEADER_ID := p_new_qte_header_id;
1453 l_sales_credit_tbl(j).sales_credit_id := null;
1454 l_sales_credit_tbl(j).object_version_number := FND_API.G_MISS_NUM;
1455 END LOOP;
1456
1457 FOR j IN 1 .. l_quote_party_tbl.COUNT LOOP
1458 l_quote_party_tbl(j).QUOTE_HEADER_ID := p_new_qte_header_id;
1459 l_quote_party_tbl(j).QUOTE_PARTY_ID := null;
1460 l_quote_party_tbl(j).object_version_number := FND_API.G_MISS_NUM;
1461 END LOOP;
1462
1463 FOR j IN 1 .. l_tax_detail_tbl.COUNT LOOP
1464 l_tax_detail_tbl(j).tax_detail_id := null;
1465 l_tax_detail_tbl(j).object_version_number := FND_API.G_MISS_NUM;
1466 END LOOP;
1467
1468 FOR j IN 1 .. l_line_attr_Ext_Tbl.COUNT LOOP
1469 l_line_attr_Ext_Tbl(j).line_attribute_id := null;
1470 l_line_attr_Ext_Tbl(j).object_version_number := FND_API.G_MISS_NUM;
1471 END LOOP;
1472
1473 FOR j IN 1 .. l_freight_charge_tbl.COUNT LOOP
1474 l_freight_charge_tbl(j).freight_charge_id := null;
1475 l_freight_charge_tbl(j).object_version_number := FND_API.G_MISS_NUM;
1476 END LOOP;
1477 */
1478
1479 --End of BC4J Fix
1480
1481 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1482 aso_debug_pub.ADD ( 'Copy_Tmpl_Service - Before insert_quote_line_rows: ' || p_qte_line_id , 1 , 'Y' );
1483 END IF;
1484 ASO_QUOTE_LINES_PVT.Insert_Quote_Line_Rows (
1485 p_control_rec => l_control_rec
1486 , P_qte_Line_Rec => l_qte_line_rec
1487 , P_qte_line_dtl_tbl => l_qte_line_dtl_tbl
1488 , P_Line_Attribs_Ext_Tbl => l_line_attr_ext_tbl
1489 , P_price_attributes_tbl => l_price_attr_tbl
1490 , P_Price_Adj_Tbl => l_price_adj_tbl
1491 , P_Price_Adj_Attr_Tbl => l_Price_Adj_Attr_Tbl
1492 , P_Payment_Tbl => ASO_QUOTE_PUB.g_miss_payment_tbl
1493 , P_Shipment_Tbl => l_shipment_tbl
1494 , P_Freight_Charge_Tbl => l_freight_charge_tbl
1495 , P_Tax_Detail_Tbl => l_tax_detail_tbl
1496 , P_Sales_Credit_Tbl => l_sales_credit_tbl
1497 , P_Quote_Party_Tbl => l_quote_party_tbl
1498 , x_qte_Line_Rec => lx_qte_line_rec
1499 , x_qte_line_dtl_tbl => l_qte_line_dtl_tbl_out
1500 , x_Line_Attribs_Ext_Tbl => l_line_attr_Ext_Tbl_out
1501 , x_price_attributes_tbl => l_price_attr_tbl_out
1502 , x_Price_Adj_Tbl => lx_ln_price_adj_tbl
1503 , x_Price_Adj_Attr_Tbl => l_Price_Adj_Attr_Tbl_out
1504 , x_Payment_Tbl => l_payment_tbl_out
1505 , x_Shipment_Tbl => l_shipment_tbl_out
1506 , x_Freight_Charge_Tbl => l_freight_charge_tbl_out
1507 , x_Tax_Detail_Tbl => l_tax_detail_tbl_out
1508 , X_Sales_Credit_Tbl => l_sales_credit_tbl_out
1509 , X_Quote_Party_Tbl => l_quote_party_tbl_out
1510 , X_Return_Status => l_return_status
1511 , X_Msg_Count => x_msg_count
1512 , X_Msg_Data => x_msg_data
1513 );
1514
1515 l_quote_party_tbl :=l_quote_party_tbl_out ;
1516 l_sales_credit_tbl :=l_sales_credit_tbl_out ;
1517 l_tax_detail_tbl := l_tax_detail_tbl_out ;
1518 l_freight_charge_tbl := l_freight_charge_tbl_out ;
1519 l_shipment_tbl := l_shipment_tbl_out ;
1520 l_payment_tbl := l_payment_tbl_out ;
1521 l_Price_Adj_Attr_Tbl := l_Price_Adj_Attr_Tbl_out ;
1522 l_Price_Attr_Tbl := l_Price_Attr_Tbl_out ;
1523 l_qte_line_dtl_tbl := l_qte_line_dtl_tbl_out ;
1524 l_Line_Attr_Ext_Tbl := l_Line_Attr_Ext_Tbl_out;
1525
1526
1527
1528 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1529 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
1530 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_UNEXP_ERROR' );
1531 FND_MESSAGE.Set_Token ( 'ROW' , 'ASO_COPYSERVICE AFTER_INSERT' , TRUE );
1532 FND_MSG_PUB.ADD;
1533 END IF;
1534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1535 ELSIF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
1536 RAISE FND_API.G_EXC_ERROR;
1537 END IF;
1538
1539
1540 lx_line_index_link_tbl ( qte_line_id ) := lx_qte_line_rec.quote_line_id;
1541
1542
1543
1544 END LOOP;
1545
1546 CLOSE line_id_from_service;
1547
1548 END Copy_Tmpl_Service;
1549
1550 PROCEDURE Config_Copy_Tmpl (
1551 p_old_config_header_id IN NUMBER
1552 , p_old_config_revision_num IN NUMBER
1553 , p_config_header_id IN NUMBER
1554 , p_config_revision_num IN NUMBER
1555 , p_new_qte_header_id IN NUMBER
1556 , p_qte_header_id IN NUMBER
1557 , lx_line_index_link_tbl IN OUT NOCOPY ASO_QUOTE_HEADERS_PVT.Index_Link_Tbl_Type
1558 , X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1559 , X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER
1560 , X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1561 ) as
1562 -- made changes to the cursor as per bug4036748
1563 CURSOR line_id_from_config (
1564 config_hdr_id NUMBER
1565 , config_rev_number NUMBER
1566 , qte_hdr_id NUMBER
1567 ) IS
1568 SELECT ASO_Quote_Line_Details.QUOTE_LINE_ID
1569 FROM ASO_Quote_Line_Details
1570 , ASO_Quote_Lines_all
1571 WHERE ASO_Quote_Line_Details.config_header_id = config_hdr_id
1572 AND ASO_Quote_Line_Details.config_revision_num = config_rev_number
1573 AND ASO_quote_line_details.quote_line_id = ASO_Quote_Lines_all.quote_line_id
1574 AND ASO_quote_line_details.ref_type_code = 'CONFIG'
1575 AND ASO_quote_line_details.ref_line_id is not null
1576 AND aso_quote_lines_all.quote_header_id = qte_hdr_id
1577 ORDER BY aso_quote_line_details.bom_sort_order;
1578
1579 CURSOR C_Serviceable_Product (
1580 l_organization_id NUMBER
1581 , l_inv_item_id NUMBER
1582 ) IS
1583 SELECT serviceable_product_flag
1584 FROM MTL_SYSTEM_ITEMS_VL
1585 WHERE inventory_item_id = l_inv_item_id
1586 AND organization_id = l_organization_id;
1587
1588 l_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
1589 l_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1590 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1591 l_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
1592 l_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
1593 l_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1594 l_Price_Adj_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1595 l_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
1596 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1597 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
1598 l_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
1599 lx_ln_Price_Adj_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1600 lx_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
1601 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type;
1602 LX_PRICE_ADJ_RLTSHIP_ID NUMBER;
1603 LX_LINE_RELATIONSHIP_ID NUMBER;
1604 X_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
1605 X_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
1606 X_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
1607 l_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
1608 l_quote_party_tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
1609 l_quote_party_rec ASO_QUOTE_PUB.Quote_Party_rec_Type;
1610 l_sales_credit_tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
1611 l_sales_credit_rec ASO_QUOTE_PUB.Sales_Credit_rec_Type;
1612 l_return_status VARCHAR2 ( 1 );
1613 qte_line_id NUMBER;
1614 i NUMBER;
1615 j NUMBER;
1616 k NUMBER;
1617 l_quote_line_id NUMBER;
1618 l_serviceable_product_flag VARCHAR2 ( 1 );
1619 l_api_version CONSTANT NUMBER := 1.0;
1620 l_ref_ln_id NUMBER;
1621 l_ato_line_id NUMBER;
1622 l_top_model_line_id NUMBER;
1623
1624
1625 Cursor get_qte_line_number (x_qte_line_id NUMBER) IS
1626 SELECT line_number
1627 FROM aso_quote_lines_all
1628 WHERE quote_line_id = x_qte_line_id ;
1629
1630 l_line_number NUMBER;
1631 l_old_quote_line_id Number;
1632
1633 CURSOR get_old_line_id (
1634 config_hdr_id NUMBER
1635 , config_rev_number NUMBER
1636 , qte_hdr_id NUMBER
1637 ) IS
1638 SELECT ASO_Quote_Line_Details.QUOTE_LINE_ID
1639 FROM ASO_Quote_Line_Details
1640 , ASO_Quote_Lines_all
1641 WHERE ASO_Quote_Line_Details.config_header_id = config_hdr_id
1642 AND ASO_Quote_Line_Details.config_revision_num = config_rev_number
1643 AND ASO_quote_line_details.quote_line_id = ASO_Quote_Lines_all.quote_line_id
1644 AND ASO_Quote_Lines_all.item_type_code = 'MDL'
1645 AND aso_quote_lines_all.quote_header_id = qte_hdr_id;
1646
1647 l_quote_party_tbl_out ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
1648 l_sales_credit_tbl_out ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
1649 l_tax_detail_tbl_out ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
1650 l_freight_charge_tbl_out ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
1651 l_shipment_tbl_out ASO_QUOTE_PUB.Shipment_Tbl_Type;
1652 l_payment_tbl_out ASO_QUOTE_PUB.Payment_Tbl_Type;
1653 l_Price_Adj_Attr_Tbl_out ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
1654 l_Price_Attr_Tbl_out ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1655 l_qte_line_dtl_tbl_out ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1656 l_Line_Attr_Ext_Tbl_out ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
1657
1658 l_dup_Price_Adj_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1659
1660
1661 BEGIN
1662
1663 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1664 aso_debug_pub.ADD ('Copy_Config_Tmpl - Begin ' , 1, 'Y' );
1665 aso_debug_pub.ADD ( 'Copy_Config_Tmpl - p_new_qte_header_id ' || p_new_qte_header_id , 1 , 'Y' );
1666 aso_debug_pub.ADD ( 'Copy_Config_Tmpl - p_qte_header_id ' || p_qte_header_id , 1 , 'Y' );
1667 END IF;
1668
1669 x_return_status := FND_API.G_RET_STS_SUCCESS;
1670
1671 OPEN line_id_from_config (
1672 p_old_config_header_id
1673 , p_old_config_revision_num
1674 , p_qte_header_id
1675 );
1676 LOOP
1677 FETCH line_id_from_config INTO qte_line_id;
1678 EXIT WHEN line_id_from_config%NOTFOUND;
1679 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1680 aso_debug_pub.ADD ( 'Copy_Config_Tmpl - inside cursor qte_line_id ' || qte_line_id , 1 , 'Y' );
1681 END IF;
1682
1683 l_qte_line_rec := ASO_UTILITY_PVT.Query_Qte_Line_Row ( qte_line_id );
1684
1685 IF p_new_qte_header_id = p_qte_header_id THEN
1686
1687 OPEN get_old_line_id (
1688 p_old_config_header_id
1689 , p_old_config_revision_num
1690 , p_qte_header_id
1691 );
1692
1693 FETCH get_old_line_id INTO l_old_quote_line_id;
1694 CLOSE get_old_line_id;
1695
1696 OPEN get_qte_line_number(lx_line_index_link_tbl(l_old_quote_line_id));
1697 FETCH get_qte_line_number into l_line_number;
1698 CLOSE get_qte_line_number;
1699
1700 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1701 aso_debug_pub.ADD ( 'Line Number -- ' || l_line_number , 1 , 'Y' );
1702 END IF;
1703
1704
1705 l_qte_line_rec.line_number := l_line_number;
1706
1707 END IF;
1708
1709 l_qte_line_rec.quote_header_id := p_new_qte_header_id;
1710
1711
1712 l_qte_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows ( qte_line_id );
1713
1714 FOR k IN 1 .. l_qte_line_dtl_tbl.COUNT LOOP
1715 l_qte_line_dtl_tbl ( k ).config_header_id := p_config_header_id;
1716 l_qte_line_dtl_tbl ( k ).config_revision_num := p_config_revision_num;
1717 END LOOP;
1718
1719 /* l_line_attr_Ext_Tbl := ASO_UTILITY_PVT.Query_Line_Attribs_Ext_Rows ( qte_line_id );
1720
1721 l_price_adj_tbl := ASO_UTILITY_PVT.Query_Price_Adj_Rows ( p_qte_header_id , qte_line_id );
1722 l_dup_price_adj_tbl := l_price_adj_tbl;
1723
1724 l_price_adj_attr_tbl := ASO_UTILITY_PVT.Query_Price_Adj_Attr_Rows(p_price_adj_tbl => l_price_adj_tbl);
1725
1726 l_price_attr_tbl := ASO_UTILITY_PVT.Query_Price_Attr_Rows ( p_qte_header_id , qte_line_id );
1727
1728 */
1729
1730 l_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows ( p_qte_header_id , qte_line_id );
1731
1732 /*l_sales_credit_tbl := ASO_UTILITY_PVT.Query_Sales_Credit_Row ( p_qte_header_id , qte_line_id );
1733
1734 l_quote_party_tbl := ASO_UTILITY_PVT.Query_Quote_Party_Row ( p_qte_header_id , qte_line_id );
1735
1736 l_freight_charge_tbl := ASO_UTILITY_PVT.Query_Freight_Charge_Rows ( l_shipment_tbl );
1737
1738 l_tax_detail_tbl := ASO_UTILITY_PVT.Query_Tax_Detail_Rows ( p_qte_header_id , qte_line_id , l_shipment_tbl );
1739
1740 */
1741
1742 OPEN C_Serviceable_Product ( l_qte_line_rec.organization_id , l_qte_line_rec.inventory_item_id );
1743 FETCH C_Serviceable_Product INTO l_serviceable_product_flag;
1744 CLOSE C_Serviceable_Product;
1745
1746 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1747 aso_debug_pub.ADD ('Copy_Config_Tmpl - After querying all the records for the line ' , 1, 'Y' );
1748 END IF;
1749
1750 FOR j IN 1 .. l_qte_line_dtl_tbl.COUNT LOOP
1751
1752 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1753 aso_debug_pub.ADD ('Copy_Config_Tmpl - l_qte_line_dtl_tbl('||j||')ref_line_id: '||l_qte_line_dtl_tbl(j).ref_line_id , 1, 'Y' );
1754 aso_debug_pub.ADD ('Copy_Config_Tmpl - l_qte_line_dtl_tbl('||j||')top_model_line_id: '||l_qte_line_dtl_tbl(j).top_model_line_id , 1, 'Y' );
1755 aso_debug_pub.ADD ('Copy_Config_Tmpl - l_qte_line_dtl_tbl('||j||')ato_line_id: '||l_qte_line_dtl_tbl(j).ato_line_id , 1, 'Y' );
1756 END IF;
1757
1758 l_ref_ln_id := lx_line_index_link_tbl ( l_qte_line_dtl_tbl ( j ).ref_line_id );
1759 l_qte_line_dtl_tbl ( j ).ref_line_id := l_ref_ln_id;
1760
1761 IF (l_qte_line_dtl_tbl(j).top_model_line_id IS NOT NULL AND l_qte_line_dtl_tbl(j).top_model_line_id <> FND_API.G_MISS_NUM
1762 and lx_line_index_link_tbl.exists(l_qte_line_dtl_tbl(j).top_model_line_id) ) THEN
1763 l_top_model_line_id := lx_line_index_link_tbl ( l_qte_line_dtl_tbl ( j ).top_model_line_id );
1764 l_qte_line_dtl_tbl ( j ).top_model_line_id := l_top_model_line_id;
1765 END IF;
1766
1767 IF (l_qte_line_dtl_tbl ( j ).ato_line_id IS NOT NULL AND l_qte_line_dtl_tbl ( j ).ato_line_id <> FND_API.G_MISS_NUM
1768 and lx_line_index_link_tbl.exists(l_qte_line_dtl_tbl(j).ato_line_id) ) THEN
1769 l_ato_line_id := lx_line_index_link_tbl ( l_qte_line_dtl_tbl ( j ).ato_line_id );
1770 l_qte_line_dtl_tbl ( j ).ato_line_id := l_ato_line_id;
1771 END IF;
1772
1773
1774 END LOOP;
1775
1776 l_quote_line_id := l_qte_line_rec.quote_line_id;
1777 l_qte_line_rec.quote_line_id := NULL;
1778 l_qte_line_rec.object_version_number := FND_API.G_MISS_NUM;
1779
1780 --Setting line attributes as null as they are not required for template 10212323
1781
1782 l_qte_line_rec.INVOICE_TO_PARTY_SITE_ID := Null;
1783 l_qte_line_rec.INVOICE_TO_PARTY_ID := Null;
1784 l_qte_line_rec.INVOICE_TO_CUST_ACCOUNT_ID := Null;
1785 l_qte_line_rec.INVOICE_TO_CUST_PARTY_ID := Null;
1786
1787 l_qte_line_rec.END_CUSTOMER_PARTY_ID := Null;
1788 l_qte_line_rec.END_CUSTOMER_PARTY_SITE_ID := Null;
1789 l_qte_line_rec.END_CUSTOMER_CUST_ACCOUNT_ID := Null;
1790 l_qte_line_rec.END_CUSTOMER_CUST_PARTY_ID := Null;
1791
1792 l_qte_line_rec.PRICE_LIST_ID := Null;
1793 l_qte_line_rec.CURRENCY_CODE := Null;
1794
1795 l_qte_line_rec.line_list_price:= NULL;
1796 l_qte_line_rec.line_adjusted_amount:= NULL;
1797 l_qte_line_rec.line_Quote_price:= NULL;
1798 l_qte_line_rec.priced_price_list_id:= NULL;
1799 l_qte_line_rec.pricing_quantity_uom:= null;
1800 l_qte_line_rec.pricing_quantity:= NULL;
1801
1802
1803 --BC4J Fix
1804
1805 FOR j IN 1 .. l_qte_line_dtl_tbl.COUNT LOOP
1806 l_qte_line_dtl_tbl(j).quote_line_detail_id := null;
1807 l_qte_line_dtl_tbl(j).object_version_number := FND_API.G_MISS_NUM;
1808 END LOOP;
1809
1810 /*FOR j IN 1 .. l_price_adj_tbl.COUNT LOOP
1811 l_price_adj_tbl ( j ).QUOTE_HEADER_ID := p_new_qte_header_id;
1812 l_price_adj_tbl ( j ).price_adjustment_id := null;
1813 l_price_adj_tbl ( j ).object_version_number := FND_API.G_MISS_NUM;
1814 END LOOP;
1815
1816 FOR j IN 1 .. l_price_adj_attr_tbl.COUNT LOOP
1817 l_price_adj_attr_tbl(j).price_adj_attrib_id := null;
1818 l_price_adj_attr_tbl(j).object_version_number := FND_API.G_MISS_NUM;
1819 END LOOP;
1820
1821 FOR j IN 1 .. l_price_attr_tbl.COUNT LOOP
1822 l_price_attr_tbl ( j ).QUOTE_HEADER_ID := p_new_qte_header_id;
1823 l_price_attr_tbl ( j ).price_attribute_id := null;
1824 l_price_attr_tbl ( j ).object_version_number := FND_API.G_MISS_NUM;
1825 END LOOP;
1826
1827 */
1828
1829 FOR j IN 1 .. l_shipment_tbl.COUNT LOOP
1830 l_shipment_tbl( j ).QUOTE_HEADER_ID := p_new_qte_header_id;
1831 l_shipment_tbl( j ).shipment_id := null;
1832 l_shipment_tbl( j ).object_version_number := FND_API.G_MISS_NUM;
1833 l_shipment_tbl( j ).SHIP_TO_CUST_ACCOUNT_ID := Null;
1834 l_shipment_tbl( j ).SHIP_TO_CUST_PARTY_ID := Null;
1835 l_shipment_tbl( j ).SHIP_TO_PARTY_ID := Null;
1836 l_shipment_tbl( j ).SHIP_TO_PARTY_SITE_ID := Null;
1837 l_shipment_tbl( j ).SHIP_TO_PARTY_NAME := Null;
1838 l_shipment_tbl( j ).SHIP_TO_CONTACT_FIRST_NAME := Null;
1839 l_shipment_tbl( j ).SHIP_TO_CONTACT_MIDDLE_NAME := Null;
1840 l_shipment_tbl( j ).SHIP_TO_CONTACT_LAST_NAME := Null;
1841 l_shipment_tbl( j ).SHIP_TO_ADDRESS1 := Null;
1842 l_shipment_tbl( j ).SHIP_TO_ADDRESS2 := Null;
1843 l_shipment_tbl( j ).SHIP_TO_ADDRESS3 := Null;
1844 l_shipment_tbl( j ).SHIP_TO_ADDRESS4 := Null;
1845 l_shipment_tbl( j ).SHIP_TO_COUNTRY_CODE := Null;
1846 l_shipment_tbl( j ).SHIP_TO_COUNTRY := Null;
1847 l_shipment_tbl( j ).SHIP_TO_CITY := Null;
1848 l_shipment_tbl( j ).SHIP_TO_POSTAL_CODE := Null;
1849 l_shipment_tbl( j ).SHIP_TO_STATE := Null;
1850 l_shipment_tbl( j ).SHIP_TO_PROVINCE := Null;
1851 l_shipment_tbl( j ).SHIP_TO_COUNTY := Null;
1852 l_shipment_tbl( j ).FREIGHT_TERMS_CODE := Null;
1853 l_shipment_tbl( j ).FOB_CODE := Null;
1854 l_shipment_tbl( j ).DEMAND_CLASS_CODE := Null;
1855 l_shipment_tbl( j ).REQUEST_DATE_TYPE := Null;
1856 l_shipment_tbl( j ).REQUEST_DATE := Null;
1857 l_shipment_tbl( j ).SHIPMENT_PRIORITY_CODE := Null;
1858 l_shipment_tbl( j ).SHIPPING_INSTRUCTIONS := Null;
1859 l_shipment_tbl( j ).PACKING_INSTRUCTIONS := Null;
1860 END LOOP;
1861
1862 /* FOR j IN 1 .. l_sales_credit_tbl.COUNT LOOP
1863 l_sales_credit_tbl(j).QUOTE_HEADER_ID := p_new_qte_header_id;
1864 l_sales_credit_tbl(j).sales_credit_id := null;
1865 l_sales_credit_tbl(j).object_version_number := FND_API.G_MISS_NUM;
1866 END LOOP;
1867
1868 FOR j IN 1 .. l_tax_detail_tbl.COUNT LOOP
1869 l_tax_detail_tbl(j).tax_detail_id := null;
1870 l_tax_detail_tbl(j).object_version_number := FND_API.G_MISS_NUM;
1871 END LOOP;
1872
1873 FOR j IN 1 .. l_quote_party_tbl.COUNT LOOP
1874 l_quote_party_tbl(j).QUOTE_HEADER_ID := p_new_qte_header_id;
1875 l_quote_party_tbl(j).QUOTE_PARTY_ID := null;
1876 l_quote_party_tbl(j).object_version_number := FND_API.G_MISS_NUM;
1877 END LOOP;
1878
1879 FOR j IN 1 .. l_line_attr_Ext_Tbl.COUNT LOOP
1880 l_line_attr_Ext_Tbl(j).line_attribute_id := null;
1881 l_line_attr_Ext_Tbl(j).object_version_number := FND_API.G_MISS_NUM;
1882 END LOOP;
1883
1884 FOR j IN 1 .. l_freight_charge_tbl.COUNT LOOP
1885 l_freight_charge_tbl(j).freight_charge_id := null;
1886 l_freight_charge_tbl(j).object_version_number := FND_API.G_MISS_NUM;
1887 END LOOP;
1888 */
1889
1890 --End of BC4J Fix
1891
1892 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1893 aso_debug_pub.ADD ( 'Copy_Config_Tmpl - Before insert_quote_line_rows: ' || l_quote_line_id , 1 , 'Y' );
1894 END IF;
1895 ASO_QUOTE_LINES_PVT.Insert_Quote_Line_Rows (
1896 p_control_rec => l_control_rec
1897 , P_qte_Line_Rec => l_qte_line_rec
1898 , P_qte_line_dtl_tbl => l_qte_line_dtl_tbl
1899 , P_Line_Attribs_Ext_Tbl => l_line_attr_ext_tbl
1900 , P_price_attributes_tbl => l_price_attr_tbl
1901 , P_Price_Adj_Tbl => l_price_adj_tbl
1902 , P_Price_Adj_Attr_Tbl => l_Price_Adj_Attr_Tbl
1903 , P_Payment_Tbl => ASO_QUOTE_PUB.g_miss_payment_tbl
1904 , P_Shipment_Tbl => l_shipment_tbl
1905 , P_Freight_Charge_Tbl => l_freight_charge_tbl
1906 , P_Tax_Detail_Tbl => l_tax_detail_tbl
1907 , P_Sales_Credit_Tbl => l_sales_credit_tbl
1908 , P_Quote_Party_Tbl => l_quote_party_tbl
1909
1910 , x_qte_Line_Rec => lx_qte_line_rec
1911 , x_qte_line_dtl_tbl => l_qte_line_dtl_tbl_out
1912 , x_Line_Attribs_Ext_Tbl => l_line_attr_Ext_Tbl_out
1913 , x_price_attributes_tbl => l_price_attr_tbl_out
1914 , x_Price_Adj_Tbl => lx_ln_price_adj_tbl
1915 , x_Price_Adj_Attr_Tbl => l_Price_Adj_Attr_Tbl_out
1916 , x_Payment_Tbl => l_payment_tbl_out
1917 , x_Shipment_Tbl => l_shipment_tbl_out
1918 , x_Freight_Charge_Tbl => l_freight_charge_tbl_out
1919 , x_Tax_Detail_Tbl => l_tax_detail_tbl_out
1920 , X_Sales_Credit_Tbl => l_sales_credit_tbl_out
1921 , X_Quote_Party_Tbl => l_quote_party_tbl_out
1922 , X_Return_Status => x_return_status
1923 , X_Msg_Count => x_msg_count
1924 , X_Msg_Data => x_msg_data
1925 );
1926
1927 l_quote_party_tbl :=l_quote_party_tbl_out ;
1928 l_sales_credit_tbl :=l_sales_credit_tbl_out ;
1929 l_tax_detail_tbl := l_tax_detail_tbl_out ;
1930 l_freight_charge_tbl := l_freight_charge_tbl_out ;
1931 l_shipment_tbl := l_shipment_tbl_out ;
1932 l_payment_tbl := l_payment_tbl_out ;
1933 l_Price_Adj_Attr_Tbl := l_Price_Adj_Attr_Tbl_out ;
1934 l_Price_Attr_Tbl := l_Price_Attr_Tbl_out ;
1935 l_qte_line_dtl_tbl := l_qte_line_dtl_tbl_out ;
1936 l_Line_Attr_Ext_Tbl := l_Line_Attr_Ext_Tbl_out;
1937
1938
1939
1940 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1941 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
1942 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_UNEXP_ERROR' );
1943 FND_MESSAGE.Set_Token ( 'ROW' , 'ASO_COPYCONFIG AFTER_INSERT' , TRUE );
1944 FND_MSG_PUB.ADD;
1945 END IF;
1946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1947 ELSIF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
1948 RAISE FND_API.G_EXC_ERROR;
1949 END IF;
1950
1951
1952
1953 lx_line_index_link_tbl(qte_line_id) := lx_qte_line_rec.quote_line_id;
1954
1955
1956
1957 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1958 aso_debug_pub.ADD('Copy_Config_Tmpl - l_qte_line_tbl(i).item_type_code ' || l_qte_line_rec.item_type_code , 1 , 'Y' );
1959 aso_debug_pub.ADD('Copy - l_qte_line_tbl(i).inventory_item_id ' || l_qte_line_rec.inventory_item_id , 1 , 'Y' );
1960 aso_debug_pub.ADD('Copy - l_serviceable_product_flag ' || l_serviceable_product_flag , 1 , 'Y' );
1961 END IF;
1962
1963 IF l_serviceable_product_flag = 'Y' THEN
1964
1965 Copy_Tmpl_Service (
1966 p_qte_line_id => l_quote_line_id
1967 , p_new_qte_header_id => p_new_qte_header_id
1968 , p_qte_header_id => p_qte_header_id
1969 , lx_line_index_link_tbl => lx_line_index_link_tbl
1970 , X_Return_Status => l_return_status
1971 , X_Msg_Count => x_msg_count
1972 , X_Msg_Data => x_msg_data
1973 );
1974
1975
1976 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1977 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_ERROR ) THEN
1978 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_UNEXP_ERROR' );
1979 FND_MESSAGE.Set_Token ( 'ROW' , 'ASO_COPY_TMPLCONFIG AFTER_SERVICE' , TRUE );
1980 FND_MSG_PUB.ADD;
1981 END IF;
1982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1983 ELSIF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
1984 RAISE FND_API.G_EXC_ERROR;
1985 END IF;
1986 END IF;
1987 END LOOP;
1988
1989 CLOSE line_id_from_config;
1990
1991 end Config_Copy_Tmpl;
1992
1993 end ASO_COPY_TMPL_PUB;