[Home] [Help]
PACKAGE BODY: APPS.ASO_QUOTE_TMPL_PVT
Source
1 PACKAGE BODY ASO_QUOTE_TMPL_PVT AS
2 /* $Header: asovqtmb.pls 120.3 2006/08/22 18:26:03 bmishra noship $ */
3
4 -- Start of Comments
5 -- Package name : ASO_QUOTE_TMPL_PVT
6 -- Purpose :
7 -- End of Comments
8
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ASO_QUOTE_TMPL_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovqtmb.pls';
13
14
15
16 PROCEDURE Add_Template_To_Quote(
17 P_API_VERSION_NUMBER IN NUMBER,
18 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
19 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
20 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
21 P_UPDATE_FLAG IN VARCHAR2 := 'Y',
22 P_TEMPLATE_ID_TBL IN ASO_QUOTE_TMPL_INT.LIST_TEMPLATE_TBL_TYPE,
23 P_QTE_HEADER_REC IN ASO_QUOTE_PUB.Qte_Header_Rec_Type := ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec,
24 P_CONTROL_REC IN ASO_QUOTE_PUB.CONTROL_REC_TYPE := ASO_QUOTE_PUB.G_MISS_control_REC,
25 x_Qte_Line_Tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
26 x_Qte_Line_Dtl_Tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type,
27 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
28 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
29 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2
30 )
31 IS
32
33 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
34 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
35
36 L_API_NAME VARCHAR2(50) := 'Add_Template_To_Quote';
37 L_API_VERSION CONSTANT NUMBER := 1.0;
38
39 l_template_line_count NUMBER := 0;
40 l_conc_segments VARCHAR2(40);
41 l_top_model_line_id NUMBER := FND_API.G_MISS_NUM;
42 l_dropped_flag VARCHAR2(1) := 'N';
43 l_service_flag VARCHAR2(1) := 'N';
44 l_config_header_id NUMBER;
45 l_config_rev_number NUMBER;
46 lx_config_header_id NUMBER;
47 lx_config_rev_number NUMBER;
48 lx_line_relationship_id NUMBER;
49
50 l_dropped_line_id_tbl ASO_QUOTE_TMPL_INT.List_Template_Tbl_Type := ASO_QUOTE_TMPL_INT.G_Miss_List_Template_Tbl;
51 l_search_line_id_tbl ASO_QUOTE_TMPL_INT.List_Template_Tbl_Type := ASO_QUOTE_TMPL_INT.G_Miss_List_Template_Tbl;
52 l_temp_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
53 l_cfg_control_rec ASO_CFG_INT.Control_rec_Type := ASO_CFG_INT.G_Miss_Control_Rec;
54 l_line_rltship_rec ASO_QUOTE_PUB.Line_Rltship_Rec_Type := ASO_QUOTE_PUB.G_Miss_Line_Rltship_Rec;
55
56 -- Variables to hold values to be passed to ASO_VALIDATE_PRICING_PVT.Validate_Pricing_Order()
57 l_pricing_control_rec ASO_PRICING_INT.Pricing_Control_Rec_Type;
58 lp_Qte_Line_Tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type := ASO_QUOTE_PUB.G_Miss_Qte_Line_Tbl;
59 lpx_Qte_Header_Rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
60 lpx_Qte_Line_Tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
61 lpx_Qte_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
62 lpx_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
63 lpx_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
64 lpx_Price_Adj_Rltship_Tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
65
66 -- Variables to hold values to be passed to ASO_QUOTE_PUB.Update_Quote()
67 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_type := ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec;
68 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type := ASO_QUOTE_PUB.G_Miss_Qte_Line_Tbl;
69 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
70 X_Qte_Header_Rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
71 --X_Qte_Line_Tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
72 --X_Qte_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
73 X_Hd_Price_Attributes_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
74 X_Hd_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
75 X_Hd_Shipment_Tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
76 X_Hd_Freight_Charge_Tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
77 X_Hd_Tax_Detail_Tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
78 X_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
79 X_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
80 X_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
81 X_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
82 X_Price_Adj_Rltship_Tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
83 X_Ln_Price_Attributes_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
84 X_Ln_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
85 X_Ln_Shipment_Tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
86 X_Ln_Freight_Charge_Tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
87 X_Ln_Tax_Detail_Tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
88 lx_orig_item_id_tbl CZ_API_PUB.number_tbl_type;
89 lx_new_item_id_tbl CZ_API_PUB.number_tbl_type;
90
91 TYPE search_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
92 l_orig_config_id_tbl search_type;
93
94
95
96 CURSOR c_conc_segments (l_inventory_item_id NUMBER) IS
97 SELECT concatenated_segments
98 FROM MTL_SYSTEM_ITEMS_VL
99 WHERE inventory_item_id = l_inventory_item_id;
100
101 CURSOR c_config_lines (l_quote_line_id NUMBER) IS
102 SELECT A.quote_line_id
103 FROM ASO_QUOTE_LINE_DETAILS A
104 WHERE (config_header_id, config_revision_num) = ( SELECT config_header_id, config_revision_num FROM ASO_QUOTE_LINE_DETAILS WHERE quote_line_id = l_quote_line_id );
105
106 CURSOR c_service_lines (l_quote_line_id NUMBER) IS
107 SELECT quote_line_id
108 FROM ASO_QUOTE_LINE_DETAILS
109 WHERE service_ref_line_id = l_quote_line_id;
110
111 CURSOR c_service_items (l_inventory_item_id NUMBER, l_organization_id NUMBER) IS
112 SELECT service_item_flag
113 FROM MTL_SYSTEM_ITEMS_VL
114 WHERE inventory_item_id = l_inventory_item_id
115 AND organization_id = l_organization_id;
116
117 CURSOR c_get_org_id ( l_qte_hdr_id NUMBER) IS
118 SELECT org_id
119 FROM aso_quote_headers_all
120 WHERE quote_header_id = l_qte_hdr_id;
121
122 BEGIN
123
124 -- Standard Start of API savepoint
125 SAVEPOINT Add_Template_To_Quote_PVT;
126
127 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
128
129 IF aso_debug_pub.g_debug_flag = 'Y' THEN
130 aso_debug_pub.add('****** Start of Add_Template_To_Quote API ******', 1, 'Y');
131 END IF;
132
133 -- Standard call to check for call compatibility.
134 IF NOT FND_API.Compatible_API_Call(
135 L_API_VERSION ,
136 P_API_VERSION_NUMBER,
137 L_API_NAME ,
138 G_PKG_NAME
139 ) THEN
140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
141 END IF;
142
143 -- Initialize message list if p_init_msg_list is set to TRUE.
144 IF FND_API.To_Boolean(p_init_msg_list) THEN
145 FND_Msg_Pub.initialize;
146 END IF;
147
148 -- Initialize API return status to success
149 x_return_status := FND_API.G_RET_STS_SUCCESS;
150
151 -- API body
152 l_qte_header_rec := p_qte_header_rec;
153
154 IF (p_qte_header_rec.org_id is null or p_qte_header_rec.org_id = fnd_api.g_miss_num ) then
155 IF aso_debug_pub.g_debug_flag = 'Y' THEN
156 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: Input ogr_id is null or g_miss', 1, 'Y');
157 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: p_qte_header_rec.quote_header_id: ' || p_qte_header_rec.quote_header_id, 1, 'Y');
158 END IF;
159
160 open c_get_org_id(p_qte_header_rec.quote_header_id);
161 fetch c_get_org_id into l_qte_header_rec.org_id;
162 close c_get_org_id;
163
164 IF aso_debug_pub.g_debug_flag = 'Y' THEN
165 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: l_qte_header_rec.org_id: '|| l_qte_header_rec.org_id, 1, 'Y');
166 END IF;
167
168 END IF;
169
170
171
172 FOR i IN 1..p_template_id_tbl.count LOOP
173 l_qte_line_tbl := ASO_UTILITY_PVT.Query_Qte_Line_Rows_sort(p_template_id_tbl(i));
174 FOR j IN 1..l_qte_line_tbl.count LOOP
175 l_template_line_count := l_template_line_count + 1;
176 lp_qte_line_tbl(l_template_line_count) := l_qte_line_tbl(j);
177 END LOOP;
178 l_qte_line_tbl := ASO_QUOTE_PUB.G_Miss_Qte_Line_Tbl;
179 END LOOP;
180
181 l_pricing_control_rec.request_type := 'ASO';
182 l_pricing_control_rec.pricing_event := 'BATCH';
183
184 IF aso_debug_pub.g_debug_flag = 'Y' THEN
185 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: before Validate_Pricing_Order', 1, 'Y');
186 END IF;
187
188 ASO_VALIDATE_PRICING_PVT.Validate_Pricing_Order(
189 p_api_version_number => 1.0,
190 p_init_msg_list => FND_API.G_FALSE,
191 p_commit => FND_API.G_FALSE,
192 p_control_rec => l_pricing_control_rec,
193 p_qte_header_rec => l_qte_header_rec,
194 p_qte_line_tbl => lp_qte_line_tbl,
195 x_qte_header_rec => lpx_qte_header_rec,
196 x_qte_line_tbl => lpx_qte_line_tbl,
197 x_qte_line_dtl_tbl => lpx_qte_line_dtl_tbl,
198 x_price_adj_tbl => lpx_price_adjustment_tbl,
199 x_price_adj_attr_tbl => lpx_price_adj_attr_tbl,
200 x_price_adj_rltship_tbl => lpx_price_adj_rltship_tbl,
201 x_return_status => x_return_status,
202 x_msg_count => x_msg_count,
203 x_msg_data => x_msg_data
204 );
205
206 IF aso_debug_pub.g_debug_flag = 'Y' THEN
207 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: after Validate_Pricing_Order', 1, 'Y');
208 END IF;
209
210 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
211 RAISE FND_API.G_EXC_ERROR;
212 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
214 END IF;
215
216 IF aso_debug_pub.g_debug_flag = 'Y' THEN
217 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: start validated lines loop', 1, 'Y');
218 END IF;
219
220 FOR i IN 1..lpx_qte_line_tbl.count LOOP
221 IF lpx_qte_line_tbl(i).pricing_status_code <> FND_API.G_RET_STS_SUCCESS THEN
222 l_dropped_line_id_tbl(lpx_qte_line_tbl(i).quote_line_id) := lpx_qte_line_tbl(i).quote_line_id;
223
224 FOR conc_segments_rec IN c_conc_segments(lpx_qte_line_tbl(i).inventory_item_id) LOOP
225 l_conc_segments := conc_segments_rec.concatenated_segments;
226 END LOOP;
227
228 IF aso_debug_pub.g_debug_flag = 'Y' THEN
229 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: item has pricing error: ' || lpx_qte_line_tbl(i).pricing_status_text, 1, 'N');
230 END IF;
231 FND_MESSAGE.Set_Name('ASO', 'ASO_QTM_INVALID_PRICELIST');
232 FND_MESSAGE.Set_Token('INVITEM', l_conc_segments, FALSE);
233 FND_MSG_PUB.ADD;
234
235 IF lpx_qte_line_tbl(i).item_type_code IN ('MDL','CFG') THEN
236 FOR config_lines_rec IN c_config_lines(lpx_qte_line_tbl(i).quote_line_id) LOOP
237 IF config_lines_rec.quote_line_id <> lpx_qte_line_tbl(i).quote_line_id THEN
238 l_dropped_line_id_tbl(config_lines_rec.quote_line_id) := config_lines_rec.quote_line_id;
239 END IF;
240 END LOOP;
241 END IF;
242
243 FOR service_lines_rec IN c_service_lines(lpx_qte_line_tbl(i).quote_line_id) LOOP
244 l_dropped_line_id_tbl(service_lines_rec.quote_line_id) := service_lines_rec.quote_line_id;
245 END LOOP;
246 END IF;
247 END LOOP;
248
249 IF aso_debug_pub.g_debug_flag = 'Y' THEN
250 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: end validated lines loop', 1, 'Y');
251 END IF;
252
253 l_template_line_count := 0;
254
255 IF aso_debug_pub.g_debug_flag = 'Y' THEN
256 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: start drop items loop', 1, 'Y');
257 END IF;
258
259 FOR i IN 1..lpx_qte_line_tbl.count LOOP
260 l_dropped_flag := 'N';
261
262 IF l_dropped_line_id_tbl.EXISTS(lpx_qte_line_tbl(i).quote_line_id) THEN
263 l_dropped_flag := 'Y';
264 END IF;
265
266 IF l_dropped_flag = 'N' THEN
267 l_template_line_count := l_template_line_count + 1;
268 l_qte_line_tbl(l_template_line_count) := lpx_qte_line_tbl(i);
269 l_search_line_id_tbl(lpx_qte_line_tbl(i).quote_line_id) := l_template_line_count;
270 END IF;
271 END LOOP;
272
273 IF aso_debug_pub.g_debug_flag = 'Y' THEN
274 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: end drop items loop', 1, 'Y');
275 END IF;
276
277 l_template_line_count := 0;
278
279 IF aso_debug_pub.g_debug_flag = 'Y' THEN
280 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: start line details loop', 1, 'Y');
281 END IF;
282
283 FOR i IN 1..l_qte_line_tbl.count LOOP
284 IF l_qte_line_tbl(i).item_type_code IN ('MDL','CFG') THEN
285 l_temp_line_dtl_tbl := ASO_QUOTE_PUB.G_Miss_Qte_Line_Dtl_Tbl;
286 l_temp_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows(l_qte_line_tbl(i).quote_line_id);
287
288 IF l_temp_line_dtl_tbl.count > 0 THEN
289 l_template_line_count := l_template_line_count + 1;
290 l_qte_line_dtl_tbl(l_template_line_count) := l_temp_line_dtl_tbl(1);
291 l_qte_line_dtl_tbl(l_template_line_count).operation_code := 'CREATE';
292 l_qte_line_dtl_tbl(l_template_line_count).qte_line_index := i;
293 l_qte_line_dtl_tbl(l_template_line_count).quote_line_id := NULL;
294
295 IF l_search_line_id_tbl.EXISTS(l_qte_line_dtl_tbl(l_template_line_count).ref_line_id) THEN
296 l_qte_line_dtl_tbl(l_template_line_count).ref_line_index := l_search_line_id_tbl(l_qte_line_dtl_tbl(l_template_line_count).ref_line_id);
297 l_qte_line_dtl_tbl(l_template_line_count).ref_line_id := NULL;
298 END IF;
299 END IF;
300 ELSE
301 l_service_flag := 'N';
302
303 FOR service_items_rec in c_service_items(l_qte_line_tbl(i).inventory_item_id, l_qte_line_tbl(i).organization_id) LOOP
304 l_service_flag := service_items_rec.service_item_flag;
305 END LOOP;
306
307 IF l_service_flag = 'Y' THEN
308 l_temp_line_dtl_tbl := ASO_QUOTE_PUB.G_Miss_Qte_Line_Dtl_Tbl;
309 l_temp_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows(l_qte_line_tbl(i).quote_line_id);
310
311 IF l_temp_line_dtl_tbl.count > 0 THEN
312 l_template_line_count := l_template_line_count + 1;
313 l_qte_line_dtl_tbl(l_template_line_count) := l_temp_line_dtl_tbl(1);
314 l_qte_line_dtl_tbl(l_template_line_count).qte_line_index := i;
315 l_qte_line_dtl_tbl(l_template_line_count).operation_code := 'CREATE';
316 l_qte_line_dtl_tbl(l_template_line_count).quote_line_id := NULL;
317 l_qte_line_dtl_tbl(l_template_line_count).quote_line_detail_id := NULL;
318
319 IF l_search_line_id_tbl.EXISTS(l_qte_line_dtl_tbl(l_template_line_count).service_ref_line_id) THEN
320 l_qte_line_dtl_tbl(l_template_line_count).service_ref_qte_line_index := l_search_line_id_tbl(l_qte_line_dtl_tbl(l_template_line_count).service_ref_line_id);
321 l_qte_line_dtl_tbl(l_template_line_count).service_ref_line_id := NULL;
322 END IF;
323 END IF;
324
325 l_qte_line_tbl(i).start_date_active := sysdate;
326 l_qte_line_tbl(i).end_date_active := null;
327
328 END IF;
329 END IF;
330
331 END LOOP;
332
333 IF aso_debug_pub.g_debug_flag = 'Y' THEN
334 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: end line details loop', 1, 'Y');
335 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: start copy configuration loop', 1, 'Y');
336 END IF;
337
338 FOR i IN 1..l_qte_line_tbl.count LOOP
339 IF l_qte_line_tbl(i).item_type_code = 'MDL' THEN
340 FOR j IN 1..l_qte_line_dtl_tbl.count LOOP
341 IF l_qte_line_dtl_tbl(j).qte_line_index = i THEN
342 IF (l_qte_line_dtl_tbl(j).config_header_id <> FND_API.G_MISS_NUM AND l_qte_line_dtl_tbl(j).config_header_id IS NOT NULL) AND
343 (l_qte_line_dtl_tbl(j).config_revision_num <> FND_API.G_MISS_NUM AND l_qte_line_dtl_tbl(j).config_revision_num IS NOT NULL) THEN
344
345 l_config_header_id := l_qte_line_dtl_tbl(j).config_header_id;
346 l_config_rev_number := l_qte_line_dtl_tbl(j).config_revision_num;
347
348
349 IF aso_debug_pub.g_debug_flag = 'Y' THEN
350 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: before Copy_Configuration', 1, 'Y');
351 END IF;
352
353
354 /*ASO_CFG_INT.Copy_Configuration(
355 p_api_version_number => 1.0,
356 p_control_rec => l_cfg_control_rec,
357 p_config_hdr_id => l_config_header_id,
358 p_config_rev_nbr => l_config_rev_number,
359 x_config_hdr_id => lx_config_header_id,
360 x_config_rev_nbr => lx_config_rev_number,
361 x_return_status => x_return_status,
362 x_msg_count => x_msg_count,
363 x_msg_data => x_msg_data
364 );*/
365
366 ASO_CFG_INT.Copy_Configuration(
367 p_api_version_number => 1.0,
368 p_init_msg_list => FND_API.G_FALSE,
369 p_commit => FND_API.G_FALSE,
370 p_config_header_id => l_config_header_id,
371 p_config_revision_num => l_config_rev_number,
372 p_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE,
373 p_handle_deleted_flag => NULL,
374 p_new_name => NULL,
375 x_config_header_id => lx_config_header_id,
376 x_config_revision_num => lx_config_rev_number,
377 x_orig_item_id_tbl => lx_orig_item_id_tbl,
378 x_new_item_id_tbl => lx_new_item_id_tbl,
379 x_return_status => x_return_status,
380 x_msg_count => x_msg_count,
381 x_msg_data => x_msg_data
382 );
383
384 IF aso_debug_pub.g_debug_flag = 'Y' THEN
385 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: after Copy_Configuration', 1, 'Y');
386 END IF;
387
388 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
389 RAISE FND_API.G_EXC_ERROR;
390 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
392 END IF;
393
394 -- Changes for Config item Id
395
396 IF (lx_orig_item_id_tbl.count > 0) AND (lx_new_item_id_tbl.count > 0) THEN
397 FOR i IN lx_orig_item_id_tbl.FIRST ..lx_orig_item_id_tbl.LAST LOOP
398 IF (lx_orig_item_id_tbl.exists(i)) and (lx_orig_item_id_tbl(i) is not null) THEN
399 l_orig_config_id_tbl(lx_new_item_id_tbl(i)) := lx_new_item_id_tbl(i);
400 END IF;
401 END LOOP;
402 end if;
403
404
405 FOR k IN 1..l_qte_line_dtl_tbl.count LOOP
406 IF l_qte_line_dtl_tbl(k).config_header_id = l_config_header_id AND l_qte_line_dtl_tbl(k).config_revision_num = l_config_rev_number THEN
407 l_qte_line_dtl_tbl(k).config_header_id := lx_config_header_id;
408 l_qte_line_dtl_tbl(k).config_revision_num := lx_config_rev_number;
409 l_qte_line_dtl_tbl(k).quote_line_detail_id := null;
410 IF l_orig_config_id_tbl.exists(l_qte_line_dtl_tbl(k).config_item_id) THEN
411 l_qte_line_dtl_tbl(k).config_item_id := l_orig_config_id_tbl(l_qte_line_dtl_tbl(k).config_item_id);
412 end if;
413 END IF;
414 END LOOP;-- loop to assign new header and rev
415
416 END IF;
417 END IF;
418 END LOOP; -- loop on line detail tbl
419 END IF;
420
421 l_qte_line_tbl(i).quote_header_id := p_qte_header_rec.quote_header_id;
422 l_qte_line_tbl(i).operation_code := 'CREATE';
423 l_qte_line_tbl(i).quote_line_id := NULL;
424 l_qte_line_tbl(i).line_number := FND_API.G_MISS_NUM;
425 END LOOP;
426
427 -- Template Manager Changes
428 IF p_update_flag = 'N' THEN
429 x_qte_line_tbl := l_qte_line_tbl;
430 x_qte_line_dtl_tbl := l_qte_line_dtl_tbl;
431 ELSE
432
433 IF aso_debug_pub.g_debug_flag = 'Y' THEN
434 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: end copy configuration loop', 1, 'Y');
435 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: before Update_Quote', 1, 'Y');
436
437 END IF;
438
439 ASO_QUOTE_PUB.Update_Quote(
440 P_Api_Version_Number => 1.0,
441 P_Init_Msg_List => FND_API.G_FALSE,
442 P_Commit => FND_API.G_FALSE,
443 P_Control_Rec => p_control_rec,
444 P_Qte_Header_Rec => l_qte_header_rec,
445 P_Qte_Line_Tbl => l_qte_line_tbL,
446 P_Qte_Line_Dtl_Tbl => l_qte_line_dtl_tbl,
447 X_Qte_Header_Rec => X_Qte_Header_Rec,
448 X_Qte_Line_Tbl => X_Qte_Line_Tbl,
449 X_Qte_Line_Dtl_Tbl => X_Qte_Line_Dtl_Tbl,
450 X_Hd_Price_Attributes_Tbl => X_Hd_Price_Attributes_Tbl,
451 X_Hd_Payment_Tbl => X_Hd_Payment_Tbl,
452 X_Hd_Shipment_Tbl => X_Hd_Shipment_Tbl,
453 X_Hd_Freight_Charge_Tbl => X_Hd_Freight_Charge_Tbl,
454 X_Hd_Tax_Detail_Tbl => X_Hd_Tax_Detail_Tbl,
455 X_Line_Attr_Ext_Tbl => X_Line_Attr_Ext_Tbl,
456 X_line_rltship_tbl => X_line_rltship_tbl,
457 X_Price_Adjustment_Tbl => X_Price_Adjustment_Tbl,
458 X_Price_Adj_Attr_Tbl => X_Price_Adj_Attr_Tbl,
459 X_Price_Adj_Rltship_Tbl => X_Price_Adj_Rltship_Tbl,
460 X_Ln_Price_Attributes_Tbl => X_Ln_Price_Attributes_Tbl,
461 X_Ln_Payment_Tbl => X_Ln_Payment_Tbl,
462 X_Ln_Shipment_Tbl => X_Ln_Shipment_Tbl,
463 X_Ln_Freight_Charge_Tbl => X_Ln_Freight_Charge_Tbl,
464 X_Ln_Tax_Detail_Tbl => X_Ln_Tax_Detail_Tbl,
465 X_Return_Status => x_return_status,
466 X_Msg_Count => x_msg_count,
467 X_Msg_Data => x_msg_data
468 );
469
470 IF aso_debug_pub.g_debug_flag = 'Y' THEN
471 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: after Update_Quote', 1, 'Y');
472 END IF;
473
474 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
475 RAISE FND_API.G_EXC_ERROR;
476 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
478 END IF;
479
480 x_qte_line_tbl := ASO_QUOTE_PUB.G_Miss_Qte_Line_Tbl;
481 x_qte_line_tbl := ASO_UTILITY_PVT.Query_Qte_Line_Rows_sort(p_qte_header_rec.quote_header_id);
482
483 IF aso_debug_pub.g_debug_flag = 'Y' THEN
484 For l in x_qte_line_tbl.FIRST..x_qte_line_tbl.LAST LOOP
485 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT:quote line id :'||x_qte_line_tbl(l).quote_line_id,1,'Y');
486 END LOOP;
487 end if;
488
489 IF aso_debug_pub.g_debug_flag = 'Y' THEN
490 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: start line relationships loop', 1, 'Y');
491 END IF;
492
493 FOR i in 1..x_qte_line_tbl.count LOOP
494 l_line_rltship_rec := ASO_QUOTE_PUB.G_Miss_Line_Rltship_Rec;
495 x_qte_line_dtl_tbl := ASO_QUOTE_PUB.G_Miss_Qte_Line_Dtl_Tbl;
496 x_qte_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows(x_qte_line_tbl(i).quote_line_id);
497
498 IF x_qte_line_dtl_tbl.count > 0 THEN
499
500 IF aso_debug_pub.g_debug_flag = 'Y' THEN
501 For k in x_qte_line_dtl_tbl.FIRST..x_qte_line_dtl_tbl.LAST LOOP
502 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT:quote line detail id :'||x_qte_line_dtl_tbl(k).quote_line_detail_id,1,'Y');
503 END LOOP;
504 end if;
505
506
507
508 IF x_qte_line_dtl_tbl(1).ref_line_id IS NOT NULL AND x_qte_line_dtl_tbl(1).ref_line_id <> FND_API.G_MISS_NUM THEN
509 l_line_rltship_rec.OPERATION_CODE := 'CREATE';
510 l_line_rltship_rec.QUOTE_LINE_ID := x_qte_line_dtl_tbl(1).ref_line_id;
511 l_line_rltship_rec.RELATED_QUOTE_LINE_ID := x_qte_line_dtl_tbl(1).quote_line_id;
512 l_line_rltship_rec.RELATIONSHIP_TYPE_CODE := 'CONFIG';
513
514 IF aso_debug_pub.g_debug_flag = 'Y' THEN
515 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: before Create_Line_Rltship', 1, 'Y');
516 END IF;
517
518 ASO_LINE_RLTSHIP_PVT.Create_Line_Rltship(
519 P_Api_Version_Number => 1.0,
520 P_Init_Msg_List => FND_API.G_FALSE,
521 P_Commit => FND_API.G_FALSE,
522 P_Validation_Level => p_validation_level,
523 P_Line_Rltship_Rec => l_line_rltship_rec,
524 X_LINE_RELATIONSHIP_ID => lx_line_relationship_id,
525 X_Return_Status => x_return_status,
526 X_Msg_Count => x_msg_count,
527 X_Msg_Data => x_msg_data
528 );
529
530 IF aso_debug_pub.g_debug_flag = 'Y' THEN
531 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: after Create_Line_Rltship: lx_line_relationship_id: '||lx_line_relationship_id, 1, 'Y');
532 END IF;
533
534 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
535 RAISE FND_API.G_EXC_ERROR;
536 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
537 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
538 END IF;
539 END IF;
540 END IF;
541 END LOOP;
542
543 IF aso_debug_pub.g_debug_flag = 'Y' THEN
544 aso_debug_pub.add('ASO_QUOTE_TMPL_PVT: end line relationships loop', 1, 'Y');
545
546 END IF;
547 END IF;--Template manager
548
549
550 -- End of API body
551 IF aso_debug_pub.g_debug_flag = 'Y' THEN
552 aso_debug_pub.add('****** End of Add_Template_To_Quote API ******', 1, 'Y');
553 END IF;
554
555 -- Standard check of p_commit
556 IF FND_API.To_Boolean(p_commit) THEN
557 COMMIT WORK;
558 END IF;
559
560 -- Standard call to get message count and if count is 1, get message info
561 FND_Msg_Pub.Count_And_Get(
562 p_encoded => FND_API.G_FALSE,
563 p_count => x_msg_count ,
564 p_data => x_msg_data
565 );
566
567 EXCEPTION
568 WHEN FND_API.G_EXC_ERROR THEN
569 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
570 P_API_NAME => L_API_NAME,
571 P_PKG_NAME => G_PKG_NAME,
572 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
573 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
574 P_SQLCODE => SQLCODE,
575 P_SQLERRM => SQLERRM,
576 X_MSG_COUNT => X_MSG_COUNT,
577 X_MSG_DATA => X_MSG_DATA,
578 X_RETURN_STATUS => X_RETURN_STATUS
579 );
580
581 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
582 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
583 P_API_NAME => L_API_NAME,
584 P_PKG_NAME => G_PKG_NAME,
585 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
586 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
587 P_SQLCODE => SQLCODE,
588 P_SQLERRM => SQLERRM,
589 X_MSG_COUNT => X_MSG_COUNT,
590 X_MSG_DATA => X_MSG_DATA,
591 X_RETURN_STATUS => X_RETURN_STATUS
592 );
593
594 WHEN OTHERS THEN
595 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
596 P_API_NAME => L_API_NAME,
597 P_PKG_NAME => G_PKG_NAME,
598 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
599 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
600 P_SQLCODE => SQLCODE,
601 P_SQLERRM => SQLERRM,
602 X_MSG_COUNT => X_MSG_COUNT,
603 X_MSG_DATA => X_MSG_DATA,
604 X_RETURN_STATUS => X_RETURN_STATUS
605 );
606
607 END Add_Template_To_Quote;
608
609
610 END ASO_QUOTE_TMPL_PVT;
611