[Home] [Help]
PACKAGE BODY: APPS.ASO_PRICING_FLOWS_PVT
Source
1 PACKAGE BODY ASO_PRICING_FLOWS_PVT as
2 /* $Header: asovpflb.pls 120.13 2012/01/02 06:16:04 rassharm ship $ */
3 -- Start of Comments
4 -- Package name : ASO_PRICING_FLOWS_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_PRICING_FLOWS_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovpflb.pls';
13 --G_ADJ_NUM CONSTANT NUMBER := 999;
14
15 /******************************************************************************
16 * *
17 * Code Path when price mode is ENTIRE_QUOTE *
18 * *
19 ******************************************************************************/
20
21 PROCEDURE Price_Entire_Quote(
22 P_Api_Version_Number IN NUMBER,
23 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
24 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
25 p_control_rec IN ASO_PRICING_INT.PRICING_CONTROL_REC_TYPE,
26 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
27 p_hd_shipment_rec IN ASO_QUOTE_PUB.Shipment_Rec_Type
28 := ASO_QUOTE_PUB.G_Miss_Shipment_Rec,
29 p_qte_line_tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
30 p_internal_call_flag IN VARCHAR2 := 'N',
31 x_qte_line_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
32 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
33 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
34 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
35 IS
36 l_api_name CONSTANT VARCHAR2(30) := 'Price_Entire_Quote';
37 l_api_version_number CONSTANT NUMBER := 1.0;
38 l_req_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
39 l_prc_control_rec ASO_PRICING_INT.PRICING_CONTROL_REC_TYPE;
40 l_qte_line_id NUMBER;
41 l_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type;
42 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
43 l_return_status VARCHAR2(1);
44 l_return_status_text VARCHAR2(2000);
45 l_message_text VARCHAR2(2000);
46 i BINARY_INTEGER;
47 j BINARY_INTEGER;
48 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
49 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
50 l_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
51 x_pass_line VARCHAR2(10);
52 l_qte_line_id_tbl JTF_NUMBER_TABLE;
53 l_qte_adj_id_tbl JTF_NUMBER_TABLE;
54 l_service_qte_line_id_tbl JTF_NUMBER_TABLE;
55 l_index_counter Number; -- This is used to keep track of lx_index_counter
56 lx_index_counter Number;
57 lx_order_status_rec QP_UTIL_PUB.ORDER_LINES_STATUS_REC_TYPE;
58 l_adj_id_tbl Index_Link_Tbl_Type;
59 l_price_index Number;
60 px_line_index_search_tbl ASO_PRICING_CORE_PVT.Index_Link_Tbl_Type;
61 l_global_pls_tbl QP_PREQ_GRP.pls_integer_type;
62 l_global_num_tbl QP_PREQ_GRP.NUMBER_TYPE;
63 l_pricing_start_time NUMBER;
64 l_pricing_end_time NUMBER;
65 l_accum_aso_time NUMBER;
66 l_accum_qp_time NUMBER;
67
68
69 BEGIN
70
71 -- Standard call to check for call compatibility.
72 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
73 p_api_version_number,
74 l_api_name,
75 G_PKG_NAME)
76 THEN
77 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
78 END IF;
79
80 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
81 l_pricing_start_time := dbms_utility.get_time;
82 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Start of Price_Entire_Quote',1,'Y');
83 END IF;
84
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86
87 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
88 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:p_control_rec.request_type:'||p_control_rec.request_type,1,'Y');
89 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:p_control_rec.pricing_event:'||p_control_rec.pricing_event,1,'Y');
90 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_control_rec.calculate_flag:'||p_control_rec.calculate_flag,1,'Y');
91 END IF;
92
93 l_prc_control_rec := p_control_rec;
94 l_price_index := 1;
95
96 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
97 aso_debug_pub.add('********************* PROCESSING HEADER STARTS *******************************',1,'Y');
98 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_internal_call_flag: '||p_internal_call_flag,1,'Y');
99 END IF;
100
101 If p_internal_call_flag = 'N' then
102 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
103 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Set_Global_Rec - Header Level... ',1,'Y');
104 END IF;
105 ASO_PRICING_INT.G_HEADER_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
106 p_qte_header_rec => p_qte_header_rec,
107 p_shipment_rec => p_hd_shipment_rec);
108 end If;--If p_internal_call_flag = 'N' then
109
110 ASO_PRICING_CORE_PVT.Print_G_Header_Rec;
111 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
112 l_pricing_end_time := dbms_utility.get_time;
113 l_accum_aso_time := l_pricing_end_time - l_pricing_start_time;
114 END IF;
115
116 QP_Price_Request_Context.Set_Request_Id;
117
118 QP_ATTR_MAPPING_PUB.Build_Contexts (
119 P_REQUEST_TYPE_CODE => p_control_rec.request_type,
120 P_PRICING_TYPE_CODE => 'H',
121 P_line_index => l_price_index,
122 P_pricing_event => p_control_rec.pricing_event,
123 p_check_line_flag => 'N',
124 x_pass_line => x_pass_line);
125
126
127 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
128 l_pricing_start_time := dbms_utility.get_time;
129 l_accum_qp_time := l_pricing_start_time - l_pricing_end_time;
130 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: x_pass_line: '||nvl(x_pass_line,'null'),1,'Y');
131 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Copy_Header_To_Request...',1,'Y');
132 END IF;
133
134
135 ASO_PRICING_CORE_PVT.Copy_Header_To_Request(
136 p_Request_Type => p_control_rec.request_type,
137 p_price_line_index => l_price_index,
138 px_index_counter => 1);
139
140 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
141 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Append_asked_for...',1,'Y');
142 END IF;
143
144 lx_index_counter:= 1;
145 ASO_PRICING_CORE_PVT.Append_asked_for(
146 p_pricing_event => p_control_rec.pricing_event,
147 p_price_line_index => l_price_index,
148 p_header_id => p_qte_header_rec.quote_header_id,
149 px_index_counter => lx_index_counter);
150
151 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
152 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:After Append Ask for lx_index_counter:'
153 ||lx_index_counter,1,'Y');
154 END IF;
155 --increment the line index
156 l_price_index:= l_price_index+1;
157
158 -- Header ends here
159
160 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
161 aso_debug_pub.add('************************ HEADER LEVEL ENDS *******************************',1,'Y');
162 aso_debug_pub.add(' ',1,'Y');
163 aso_debug_pub.add('************************ LINE LEVEL BEGINS *******************************',1,'Y');
164 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of ASO UTL PVT Query_Pricing_Line_Rows...',1,'Y');
165 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Before call Get_Order_Lines_Status:p_control_rec.pricing_event:'
166 ||p_control_rec.pricing_event,1,'Y');
167 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Before call Get_Order_Lines_Status:p_control_rec.request_type:'
168 ||p_control_rec.request_type,1,'Y');
169 l_pricing_end_time := dbms_utility.get_time;
170 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
171 END IF;
172
173 QP_UTIL_PUB.Get_Order_Lines_Status(p_event_code => p_control_rec.pricing_event,
174 x_order_status_rec => lx_order_status_rec,
175 p_freight_call_flag => 'Y',
176 p_request_type_code => p_control_rec.request_type);
177
178 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
179 l_pricing_start_time := dbms_utility.get_time;
180 l_accum_qp_time := l_accum_qp_time + (l_pricing_start_time - l_pricing_end_time);
181
182 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:lx_order_status_rec.all_lines_flag:'
183 ||lx_order_status_rec.all_lines_flag,1,'Y');
184 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:lx_order_status_rec.changed_lines_flag:'
185 ||lx_order_status_rec.changed_lines_flag,1,'Y');
186 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:lx_order_status_rec.summary_line_flag:'
187 ||lx_order_status_rec.summary_line_flag,1,'Y');
188 END IF;
189
190 If lx_order_status_rec.all_lines_flag = 'N' then
191 l_prc_control_rec.PRG_REPRICE_MODE := 'F';
192 else
193 l_prc_control_rec.PRG_REPRICE_MODE := 'A';
194 end if;
195
196 l_qte_line_tbl := ASO_UTILITY_PVT.Query_Pricing_Line_Rows(p_qte_header_rec.quote_header_id);
197
198 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
199 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl.count :'||NVL(l_qte_line_tbl.count,0),1,'Y');
200 l_pricing_end_time := dbms_utility.get_time;
201 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
202 END IF;
203
204 -- Added this code to delete all the adjustments when there are no items in the cart
205 If l_qte_line_tbl.count = 0 Then
206 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
207 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:No lines in the database',1,'Y');
208 END IF;
209
210 Else
211 -- Line Count <> 0.
212
213 For i in 1..l_qte_line_tbl.count Loop
214
215 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
216 l_pricing_start_time := dbms_utility.get_time;
217 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Line_Dtl_Rows...',1,'Y');
218 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl(i).quote_line_id:'
219 ||NVL(l_qte_line_tbl(i).quote_line_id,0),1,'Y');
220 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl(i).pricing_line_type_indicator:'
221 ||l_qte_line_tbl(i).pricing_line_type_indicator,1,'Y');
222 END IF;
223
224 l_qte_line_id := l_qte_line_tbl(i).quote_line_id;
225 l_qte_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows(l_qte_line_id);
226
227 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
228 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Defaulting Hdr Lvl Price List and Currency..',1,'Y');
229 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_header_rec.currency_code :'
230 ||NVL(p_qte_header_rec.currency_code,'NULL'),1,'Y');
231 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_header_rec.price_list_id :'
232 ||NVL(to_char(p_qte_header_rec.price_list_id),'NULL'),1,'Y');
233 END IF;
234 -- Pass header_level currency_code to line level by default.
235 l_qte_line_tbl(i).currency_code := p_qte_header_rec.currency_code;
236
237 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
238 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl(i).price_list_id :'
239 ||NVL(to_char(l_qte_line_tbl(i).price_list_id),'NULL'),1,'Y');
240 END IF;
241 -- Pass Header level price list by default.
242 IF l_qte_line_tbl(i).pricing_line_type_indicator = 'F' then
243 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
244 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Free line so not defaulting of the price list',1,'Y');
245 END IF;
246 ELSE
247 /*Default the price list*/
248 If (l_qte_line_tbl(i).price_list_id is null
249 OR l_qte_line_tbl(i).price_list_id= FND_API.G_MISS_NUM) Then
250 l_qte_line_tbl(i).price_list_id := p_qte_header_rec.price_list_id;
251 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
252 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Price list id defaulted from Header:'
253 ||NVL(to_char(l_qte_line_tbl(i).price_list_id),'NULL'),1,'Y');
254 END IF;
255 End if;
256 END IF;--l_qte_line_tbl(i).pricing_line_type_indicator = 'F'
257
258 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
259 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_dtl_tbl.count :'
260 ||NVL(l_qte_line_dtl_tbl.count,0),1,'Y');
261 END IF;
262 IF l_qte_line_dtl_tbl.count = 1 THEN
263 l_qte_line_dtl_rec := l_qte_line_dtl_tbl(1);
264 ELSE
265 l_qte_line_dtl_rec := ASO_QUOTE_PUB.G_Miss_Qte_Line_Dtl_REC ;
266 END IF;
267
268 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
269 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Shipment_Rows..',1,'Y');
270 END IF;
271 l_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows
272 (p_qte_header_rec.quote_header_id, l_QTE_LINE_ID);
273 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
274 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_shipment_tbl.count :'
275 ||NVL(l_shipment_tbl.count,0),1,'Y');
276 END IF;
277 IF l_shipment_tbl.count = 1 THEN
278 l_shipment_rec := l_shipment_tbl(1);
279 else
280 l_shipment_rec := ASO_QUOTE_PUB.G_Miss_Shipment_rec;
281 END IF;
282
283 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
284 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Set_Global_Rec - Line Level...', 1, 'Y');
285 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:These values will be defaulted from header for pricing qualification only:',1,'Y');
286 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_qte_line_tbl(i).invoice_to_party_site_id:'
287 ||NVL(to_char(l_qte_line_tbl(i).invoice_to_party_site_id),'NULL'),1,'Y');
288 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.ship_to_party_site_id:'
289 ||NVL(to_char(l_shipment_rec.ship_to_party_site_id),'NULL'),1,'Y');
290 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_qte_line_tbl(i).agreement_id:'
291 ||NVL(to_char(l_qte_line_tbl(i).agreement_id),'NULL'),1,'Y');
292 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.ship_method_code:'
293 ||NVL(l_shipment_rec.ship_method_code,'NULL'),1,'Y');
294 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.freight_terms_code:'
295 ||NVL(l_shipment_rec.freight_terms_code,'NULL'),1,'Y');
296 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.FREIGHT_CARRIER_CODE:'
297 ||NVL(l_shipment_rec.FREIGHT_CARRIER_CODE,'NULL'),1,'Y');
298 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.FOB_CODE:'
299 ||NVL(l_shipment_rec.FOB_CODE,'NULL'),1,'Y');
300 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.REQUEST_DATE:'
301 ||NVL(to_char(l_shipment_rec.REQUEST_DATE),'NULL'),1,'Y');
302 END IF;
303
304 ASO_PRICING_INT.G_LINE_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
305 p_qte_line_rec => l_qte_line_tbl(i),
306 p_qte_line_dtl_rec => l_qte_line_dtl_rec,
307 p_shipment_rec => l_shipment_rec);
308
309 ASO_PRICING_CORE_PVT.Print_G_Line_Rec;
310
311 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
312 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of QP Build_Contexts - Line Level...',1,'Y');
313 l_pricing_end_time := dbms_utility.get_time;
314 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
315 END IF;
316 QP_ATTR_MAPPING_PUB.Build_Contexts (
317 p_request_type_code => p_control_rec.request_type,
318 p_line_index => l_price_index,
319 p_check_line_flag => 'N',
320 p_pricing_event => p_control_rec.pricing_event,
321 p_pricing_type_code => 'L',
322 p_price_list_validated_flag => 'N',
323 x_pass_line => x_pass_line);
324 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
325 l_pricing_start_time := dbms_utility.get_time;
326 l_accum_qp_time := l_accum_qp_time +(l_pricing_start_time - l_pricing_end_time);
327 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: x_pass_line: '||nvl(x_pass_line,'null'),1,'Y');
328 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Copy_Line_To_Request...',1,'Y');
329 END IF;
330
331 ASO_PRICING_CORE_PVT.Copy_Line_To_Request(
332 p_Request_Type => p_control_rec.request_type,
333 p_price_line_index => l_price_index,
334 px_index_counter => i+1);
335
336
337 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
338 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Append_asked_for...',1,'Y');
339 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Before Append Ask for lx_index_counter:'
340 ||lx_index_counter,1,'Y');
341 END IF;
342 l_index_counter := lx_index_counter;
343 ASO_PRICING_CORE_PVT.Append_asked_for(
344 p_pricing_event => p_control_rec.pricing_event,
345 p_price_line_index => l_price_index,
346 p_header_id => p_qte_header_rec.quote_header_id,
347 p_line_id => l_qte_line_tbl(i).quote_line_id,
348 px_index_counter => lx_index_counter);
349 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
350 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:After Append Ask for lx_index_counter:'
351 ||lx_index_counter,1,'Y');
352 END IF;
353 If lx_index_counter = 0 then
354 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
355 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Assigning the value of l_index_counter back to lx_index_counter:'||l_index_counter,1,'Y');
356 END IF;
357 lx_index_counter := l_index_counter;
358 end if;
359
360 /*Store the line_id of all the service_lines*/
361 If l_qte_line_tbl(i).service_item_flag = 'Y' then
362 if l_service_qte_line_id_tbl.EXISTS(1) then
363 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
364 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of l_service_qte_line_id_tbl.extend...',1,'Y');
365 END IF;
366 l_service_qte_line_id_tbl.extend;
367 l_service_qte_line_id_tbl(l_service_qte_line_id_tbl.count)
368 := l_qte_line_tbl(i).quote_line_id;
369 else
370 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
371 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: First quote_line_id in l_service_qte_line_id_tbl',1,'Y');
372 END IF;
373 l_service_qte_line_id_tbl:= JTF_NUMBER_TABLE(l_qte_line_tbl(i).quote_line_id);
374 end if;
375 end if;-- l_qte_line_tbl(i).service_item_flag = 'Y'
376
377 /*Store all the quote_line_id processed*/
378 IF l_Qte_Line_id_tbl.EXISTS(1) THEN
379 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
380 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of l_qte_line_id_tbl.extend...',1,'Y');
381 END IF;
382 l_Qte_Line_id_tbl.extend;
383 l_Qte_Line_id_tbl(l_Qte_Line_id_tbl.count) := l_qte_line_tbl(i).quote_line_id;
384 ELSE
385 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
386 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: First quote_line_id in l_qte_line_id_tbl',1,'Y');
387 END IF;
388 l_Qte_Line_id_tbl := JTF_NUMBER_TABLE(l_qte_line_tbl(i).quote_line_id);
389 END IF;
390 --increment the line index
391 px_line_index_search_tbl(l_qte_line_id) := l_price_index;
392 l_price_index:= l_price_index+1;
393 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
394 l_pricing_end_time := dbms_utility.get_time;
395 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
396 END IF;
397 End Loop; -- l_Qte_Line_tbl.count checking.
398
399 -- Call to Price Adjustment Relationships and Service Relationships
400 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
401 l_pricing_start_time := dbms_utility.get_time;
402 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Price_Adjustments...',1,'Y');
403 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_id_tbl.count :'
404 ||NVL(l_qte_line_id_tbl.count,0),1,'Y');
405 END IF;
406 ASO_PRICING_CORE_PVT.Query_Price_Adjustments
407 (p_quote_header_id => p_qte_header_rec.quote_header_id,
408 p_qte_line_id_tbl => l_qte_line_id_tbl,
409 x_adj_id_tbl => l_qte_adj_id_tbl);
410
411 If l_qte_adj_id_tbl.exists(1) OR l_service_qte_line_id_tbl.exists(1) Then
412 -- Adjustment and line Relationships and services...
413 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
414 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Adj_rltship_Bulk...',1,'Y');
415 If l_qte_adj_id_tbl is not null then
416 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_adj_id_tbl.count :'
417 ||NVL(l_qte_adj_id_tbl.count,0),1,'Y');
418 end if;
419 If l_service_qte_line_id_tbl is not null then
420 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_qte_line_id_tbl.count :'
421 ||NVL(l_service_qte_line_id_tbl.count,0),1,'Y');
422 end if;
423 END IF;
424 ASO_PRICING_CORE_PVT.Query_relationships
425 (p_qte_adj_id_tbl => l_qte_adj_id_tbl,
426 p_service_qte_line_id_tbl => l_service_qte_line_id_tbl);
427 else
428 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
429 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: There are no service lines or manual related Adjustments',1,'Y');
430 END IF;
431 end if;--If l_qte_adj_id_tbl.exists(1) OR l_service_qte_line_id_tbl.exists(1)
432
433 END IF; -- l_qte_line_tbl.count = 0 check.
434
435
436 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
437 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin bulk collect adj ids', 1, 'Y');
438 END IF;
439
440 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
441 --If nvl(l_qte_adj_id_tbl.count,0) > 0 then
442 If l_qte_adj_id_tbl is not null then
443 For i in 1..l_qte_adj_id_tbl.count loop
444 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Querying for the following adjustment ids:',1,'Y');
445 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_qte_adj_id_tbl(i):'||l_qte_adj_id_tbl(i),1,'Y');
446 end loop;
447 end if;
448 END IF;
449
450 SELECT adj.PRICE_ADJUSTMENT_ID
451 BULK COLLECT INTO
452 l_adj_id_tbl
453 FROM ASO_PRICE_ADJUSTMENTS adj
454 WHERE adj.quote_header_id = p_qte_header_rec.quote_header_id
455 AND adj.price_adjustment_id NOT IN (SELECT column_value
456 FROM TABLE (CAST(l_qte_adj_id_tbl AS JTF_NUMBER_TABLE)) passed_adj);
457
458 if aso_debug_pub.g_debug_flag = 'Y' THEN
459 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: No. of adjustment lines selected is sql%rowcount: '||sql%rowcount,1,'Y');
460 end if;
461
462 if aso_debug_pub.g_debug_flag = 'Y' THEN
463
464 if l_adj_id_tbl.count > 0 then
465
466 for k in l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST loop
467 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_adj_id_tbl('||k||'): ' || l_adj_id_tbl(k),1,'Y');
468 end loop;
469
470 end if;
471
472 end if;
473
474 if l_adj_id_tbl.count > 0 then
475 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
476 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin DELETE FROM ASO_PRICE_ADJ_ATTRIBS', 1, 'Y');
477 END IF;
478 FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
479 DELETE FROM ASO_PRICE_ADJ_ATTRIBS
480 WHERE PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i);
481
482 if aso_debug_pub.g_debug_flag = 'Y' THEN
483 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:No of adjustment attribute lines deleted is sql%rowcount: '||sql%rowcount,1,'Y');
484 end if;
485
486 end if;
487
488 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
489 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin DELETE FROM ASO_PRICE_ADJ_RELATIONSHIPS', 1, 'Y');
490 END IF;
491 DELETE FROM ASO_PRICE_ADJ_RELATIONSHIPS
492 WHERE QUOTE_LINE_ID in (SELECT quote_line_id
493 FROM ASO_PRICE_ADJUSTMENTS
494 WHERE quote_header_id = p_qte_header_rec.quote_header_id
495 AND quote_line_id IS NOT NULL);
496
497 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
498 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Rltd adj Lines deleted '||sql%ROWCOUNT,1,'Y');
499 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin DELETE FROM ASO_PRICE_ADJUSTMENTS', 1, 'Y');
500 END IF;
501
502 DELETE FROM ASO_PRICE_ADJUSTMENTS
503 WHERE quote_header_id = p_qte_header_rec.quote_header_id;
504 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
505 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Adjustment Lines deleted '||sql%ROWCOUNT,1,'Y');
506 END IF;
507
508
509 If (l_qte_line_tbl.count <> 0 ) OR (p_control_rec.pricing_event = 'ORDER') then
510 --Condition was placed because engine call was not necessary if the event is price and there are no lines
511
512 --Need to modify the global index table of pls integer types
513 l_global_pls_tbl := ASO_PRICING_CORE_PVT.G_LDET_LINE_INDEX_TBL;
514 ASO_PRICING_CORE_PVT.G_LDET_LINE_INDEX_TBL:= ASO_PRICING_CORE_PVT.Modify_Global_PlsIndex_Table(
515 p_global_tbl => l_global_pls_tbl,
516 p_search_tbl => px_line_index_search_tbl);
517 l_global_num_tbl := ASO_PRICING_CORE_PVT.G_RLTD_LINE_INDEX_TBL;
518 ASO_PRICING_CORE_PVT.G_RLTD_LINE_INDEX_TBL:= ASO_PRICING_CORE_PVT.Modify_Global_NumIndex_Table(
519 p_global_tbl => l_global_num_tbl,
520 p_search_tbl => px_line_index_search_tbl);
521 l_global_num_tbl := ASO_PRICING_CORE_PVT.G_RLTD_RELATED_LINE_IND_TBL;
522 ASO_PRICING_CORE_PVT.G_RLTD_RELATED_LINE_IND_TBL:= ASO_PRICING_CORE_PVT.Modify_Global_NumIndex_Table(
523 p_global_tbl => l_global_num_tbl,
524 p_search_tbl => px_line_index_search_tbl);
525
526 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
527 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Populate_QP_Temp_tables...',1,'Y');
528 l_pricing_end_time := dbms_utility.get_time;
529 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
530 END IF;
531
532 ASO_PRICING_CORE_PVT.populate_qp_temp_tables;
533
534 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
535 l_pricing_start_time := dbms_utility.get_time;
536 l_accum_qp_time := l_accum_qp_time + (l_pricing_start_time - l_pricing_end_time);
537 END IF;
538
539
540 -- Set the control rec for QP
541
542 l_req_control_rec.pricing_event := p_control_rec.pricing_event;
543 l_req_control_rec.calculate_flag := p_control_rec.calculate_flag;
544 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
545 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_req_control_rec.calculate_flag:'||l_req_control_rec.calculate_flag,1,'Y');
546 END IF;
547 l_req_control_rec.simulation_flag := p_control_rec.simulation_flag;
548 l_req_control_rec.TEMP_TABLE_INSERT_FLAG := 'N'; ---- Modified
549 l_req_control_rec.source_order_amount_flag := 'Y';
550 l_req_control_rec.GSA_CHECK_FLAG := 'Y';
551 l_req_control_rec.GSA_DUP_CHECK_FLAG := 'Y';
552 l_req_control_rec.REQUEST_TYPE_CODE := p_control_rec.request_type;
553 l_req_control_rec.rounding_flag := 'Q';
554
555 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
556 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of QP_PREQ_PUB.PRICE_REQUEST', 1, 'Y');
557 l_pricing_end_time := dbms_utility.get_time;
558 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
559 END IF;
560
561 /* Change for populating QP_PREQ_GRP.CONTROL_RECORD_TYPE.ORG_ID Yogeshwar (MOAC) */
562
563 l_req_control_rec.ORG_ID := p_qte_header_rec.org_id;
564
565 /* End of Change (MOAC) */
566
567
568
569 QP_PREQ_PUB.PRICE_REQUEST
570 (p_control_rec =>l_req_control_rec
571 ,x_return_status =>l_return_status
572 ,x_return_status_Text =>l_return_status_Text
573 );
574 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
575 x_return_status := l_return_status;
576 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
577 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR');
578 FND_MESSAGE.Set_Token('MSG_TXT', substr(l_return_status_text, 1,255), FALSE);
579 FND_MSG_PUB.ADD;
580 END IF;
581 RAISE FND_API.G_EXC_ERROR;
582 END IF;
583
584 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
585 l_pricing_start_time := dbms_utility.get_time;
586 l_accum_qp_time := l_accum_qp_time + (l_pricing_start_time - l_pricing_end_time);
587 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: End of QP_PREQ_PUB.PRICE_REQUEST', 1, 'Y');
588 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After PRICE_REQUEST l_return_status:'
589 ||l_return_status, 1, 'Y');
590 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After PRICE_REQUEST l_return_status_text '
591 ||l_return_status_text,1,'Y');
592 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Post_Price_Request...',1,'Y');
593 END IF;
594
595
596 ASO_PRICING_CORE_PVT.Copy_Price_To_Quote(
597 P_Api_Version_Number => 1.0,
598 P_Init_Msg_List => FND_API.G_FALSE,
599 P_Commit => FND_API.G_FALSE,
600 p_control_rec => l_prc_control_rec,
601 p_qte_header_rec => p_qte_header_rec,
602 x_return_status => x_return_status,
603 x_msg_count => x_msg_count,
604 x_msg_data => x_msg_data);
605 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
606 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Copy_Price_To_Quote : x_return_status: '
607 ||nvl(x_return_status,'NULL'),1,'Y');
608 END IF;
609
610 If p_internal_call_flag = 'N' then
611 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
612 ASO_PRICING_CORE_PVT.Process_PRG(
613 P_Api_Version_Number => 1.0,
614 P_Init_Msg_List => FND_API.G_FALSE,
615 P_Commit => FND_API.G_FALSE,
616 p_control_rec => l_prc_control_rec,
617 p_qte_header_rec => p_qte_header_rec,
618 x_qte_line_tbl => x_qte_line_tbl,
619 x_return_status => x_return_status,
620 x_msg_count => x_msg_count,
621 x_msg_data => x_msg_data);
622
623 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
624 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Process_PRG : x_return_status: '
625 ||nvl(x_return_status,'NULL'),1,'Y');
626 END IF;
627
628 END IF;--x_return_status = FND_API.G_RET_STS_SUCCESS
629
630 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
631 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
632 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After Process_PRG : x_qte_line_tbl.count: '
633 ||x_qte_line_tbl.count,1,'Y');
634 END IF;
635
636 ASO_PRICING_CORE_PVT.Process_Charges(
637 P_Api_Version_Number => 1.0,
638 P_Init_Msg_List => FND_API.G_FALSE,
639 P_Commit => FND_API.G_FALSE,
640 p_control_rec => l_prc_control_rec,
641 p_qte_header_rec => p_qte_header_rec,
642 x_return_status => x_return_status,
643 x_msg_count => x_msg_count,
644 x_msg_data => x_msg_data);
645 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
646 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Process_Charges : x_return_status: '
647 ||nvl(x_return_status,'NULL'),1,'Y');
648 END IF;
649
650 END IF;--x_return_status = FND_API.G_RET_STS_SUCCESS
651
652 End If;--p_internal_call_flag = 'N'
653 End if;--(l_qte_line_tbl.count <> 0 ) OR (p_control_rec.pricing_event = 'ORDER')
654
655
656 FND_MSG_PUB.Count_And_Get
657 ( p_encoded => 'F',
658 p_count => x_msg_count,
659 p_data => x_msg_data
660 );
661
662 FOR l IN 1 .. x_msg_count LOOP
663 x_msg_data := FND_MSG_PUB.GET( p_msg_index => l, p_encoded => 'F');
664 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
665 aso_debug_pub.add('Messge count and get '||x_msg_data, 1, 'Y');
666 aso_debug_pub.add('Messge count and get '||x_msg_count, 1, 'Y');
667 END IF;
668 END LOOP;
669
670 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
671 l_pricing_end_time := dbms_utility.get_time;
672 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
673 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:**********Timing in Price Entire Quote**************',1,'Y');
674 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Total time taken (in secs) in Price Entire Quote(Besides Pricing):'||l_accum_aso_time/100,1,'Y');
675 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Total time taken (in secs) in Pricing+BuildContext:'||l_accum_qp_time/100,1,'Y');
676 END IF;
677
678 END Price_Entire_Quote;
679
680
681 /******************************************************************************
682 * *
683 * Code Path when price mode is QUOTE_LINE *
684 * *
685 ******************************************************************************/
686
687
688 PROCEDURE Price_Quote_Line(
689
690 P_Api_Version_Number IN NUMBER,
691 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
692 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
693 p_control_rec IN ASO_PRICING_INT.PRICING_CONTROL_REC_TYPE,
694 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
695 p_hd_shipment_rec IN ASO_QUOTE_PUB.Shipment_Rec_Type
696 := ASO_QUOTE_PUB.G_Miss_Shipment_Rec,
697 p_qte_line_tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
698 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
699 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
700 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
701 IS
702 l_api_name CONSTANT VARCHAR2(30) := 'Price_Quote_Line';
703 l_api_version_number CONSTANT NUMBER := 1.0;
704 --l_request_type VARCHAR2(60);
705 l_req_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
706 l_qte_line_id NUMBER;
707 l_child_qte_line_id NUMBER;
708 l_found NUMBER;
709 l_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type;
710 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
711 l_return_status VARCHAR2(1);
712 l_return_status_text VARCHAR2(2000);
713 l_message_text VARCHAR2(2000);
714 i BINARY_INTEGER;
715 j BINARY_INTEGER;
716 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
717 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
718 l_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
719 x_pass_line VARCHAR2(10);
720 l_qte_line_id_tbl JTF_NUMBER_TABLE;
721 l_qte_adj_id_tbl JTF_NUMBER_TABLE;
722 l_service_qte_line_id_tbl JTF_NUMBER_TABLE;
723 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
724 l_ref_type_code ASO_QUOTE_LINE_DETAILS.SERVICE_REF_TYPE_CODE%TYPE;
725 l_service_ref_line_id ASO_QUOTE_LINE_DETAILS.SERVICE_REF_LINE_ID%TYPE;
726 l_service_proc_done_flag VARCHAR2(1);
727 l_service_product_id_tbl Index_Link_Tbl_Type;
728 l_service_ref_line_id_tbl Index_Link_Tbl_Type;
729 l_service_id_tbl Index_Link_Tbl_Type;
730 lx_index_counter Number;
731 l_index_counter Number; -- This is used to keep track of lx_index_counter
732 l_price_index Number;
733 px_line_index_search_tbl ASO_PRICING_CORE_PVT.Index_Link_Tbl_Type;
734 l_global_pls_tbl QP_PREQ_GRP.pls_integer_type;
735 l_global_num_tbl QP_PREQ_GRP.NUMBER_TYPE;
736
737 -- The following three cursors are needed for service lines processing.
738 CURSOR c_Qte_Line_SVC_ref(p_qte_line_id IN NUMBER) IS
739 SELECT service_ref_type_code , service_ref_line_id
740 FROM ASO_QUOTE_LINE_DETAILS
741 WHERE quote_line_id = p_qte_line_id;
742
743 CURSOR c_order_line (p_order_line_id IN NUMBER) IS
744 SELECT line_id, inventory_item_id, pricing_quantity, pricing_quantity_uom,
745 unit_list_price, price_list_id
746 FROM OE_ORDER_LINES_ALL
747 WHERE line_id = p_order_line_id;
748
749 -- Cursors used for the customer account (Sold_to_org_id)
750 /*** Start: BugNo 8647883: R12.1.2 Service reference SUN ER ***/
751 CURSOR c_get_cust_acct_id IS
752 select decode(nvl(fnd_profile.value('ASO_FILTER_SERVICE_RF_END_CUST'),'N'),'Y',nvl(END_CUSTOMER_CUST_ACCOUNT_ID,cust_account_id),cust_account_id) cust_account_id
753 from ASO_QUOTE_HEADERS_ALL
754 WHERE quote_header_id = p_qte_header_rec.quote_header_id;
755
756 CURSOR c_get_cust_acct_id_ln(p_qte_line_id number) IS
757 select decode(nvl(fnd_profile.value('ASO_FILTER_SERVICE_RF_END_CUST'),'N'),'Y',END_CUSTOMER_CUST_ACCOUNT_ID) cust_account_id
758 from ASO_QUOTE_LINES_ALL
759 WHERE quote_line_id = p_qte_line_id;
760
761 cursor c_get_price_list(p_qte_hdr_id number) is
762 select price_list_id
763 from aso_quote_headers_all
764 where quote_header_id = p_qte_hdr_id;
765
766 l_cust_account_id number;
767 /*** End: BugNo 8647883: R12.1.2 Service reference SUN ER ***/
768
769
770 CURSOR c_csi_line(p_instance_id IN NUMBER, p_cust_account_id NUMBER) IS
771 SELECT original_order_line_id
772 FROM csi_instance_accts_rg_v
773 WHERE customer_product_id = p_instance_id
774 AND account_id = p_cust_account_id;
775
776
777 /****** Start SUN Changes ER: 3802859 **********/
778
779 l_order_found BOOLEAN := FALSE;
780
781 -- changed cursor for bug 12839557
782 CURSOR c_csi_line_details(p_instance_id IN NUMBER, p_cust_account_id NUMBER) IS
783 SELECT si.concatenated_segments product, si.inventory_item_id, cii.quantity, cii.unit_of_measure
784 FROM mtl_system_items_kfv si, csi_item_instances cii
785 WHERE NVL(cii.active_end_date, (SYSDATE + 1)) > SYSDATE
786 AND cii.inventory_item_id = si.inventory_item_id
787 AND si.organization_id = cii.inv_master_organization_id
788 AND cii.instance_id =p_instance_id;
789
790 /* SELECT distinct a.product,b.inventory_item_id,a.quantity,a.unit_of_measure_code
791 FROM csi_instance_accts_rg_v a ,mtl_system_items_vl b
792 where a.product = b.concatenated_segments
793 AND a.customer_product_id = p_instance_id;
794 --AND a.account_id = p_cust_account_id;
795 */
796
797 l_prod varchar2(1000);
798 l_item_id number;
799 l_qty number;
800 l_uom varchar2(30);
801
802 /******* End SUN Changes ER: 3802859 ***********/
803
804 -- Coded for sun GSI
805 l_service_item_flg varchar2(1):='N';
806 l_service_item_flg1 varchar2(1):='N';
807 l_servicable_item_flg varchar2(1):='N';
808 l_servicable_item_flg1 varchar2(1):='N';
809
810
811 BEGIN
812
813
814 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
815 aso_debug_pub.add('Start of Price_Quote_Line',1,'Y');
816 END IF;
817 -- Standard Start of API savepoint
818 SAVEPOINT PRICE_QUOTE_LINE_PVT;
819
820 -- Standard call to check for call compatibility.
821 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
822 p_api_version_number,
823 l_api_name,
824 G_PKG_NAME)
825 THEN
826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827 END IF;
828
829 x_return_status := FND_API.G_RET_STS_SUCCESS;
830
831
832 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
833 aso_debug_pub.add('p_control_rec.request_type:'||p_control_rec.request_type,1,'Y');
834 aso_debug_pub.add('p_control_rec.pricing_event:'||p_control_rec.pricing_event,1,'Y');
835 END IF;
836
837
838 l_price_index := 1;
839
840
841 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
842 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Set_Global_Rec - Header Level... ',1,'Y');
843 END IF;
844 ASO_PRICING_INT.G_HEADER_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
845 p_qte_header_rec => p_qte_header_rec,
846 p_shipment_rec => p_hd_shipment_rec);
847
848 ASO_PRICING_CORE_PVT.Print_G_Header_Rec;
849
850 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
851 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of QP Build Contexts - Header Level ... ',1,'Y');
852 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_header_rec.quote_header_id: '
853 ||NVL(to_char(p_qte_header_rec.quote_header_id),'NULL'),1,'Y');
854 END IF;
855
856 QP_Price_Request_Context.Set_Request_Id;
857
858 QP_ATTR_MAPPING_PUB.Build_Contexts (
859 P_REQUEST_TYPE_CODE => p_control_rec.request_type,
860 P_PRICING_TYPE_CODE => 'H',
861 P_line_index => l_price_index,
862 P_pricing_event => p_control_rec.pricing_event,
863 p_check_line_flag => 'N',
864 x_pass_line => x_pass_line);
865
866 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
867 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: x_pass_line: '||nvl(x_pass_line,'null'),1,'Y');
868 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Copy_Header_To_Request...',1,'Y');
869 END IF;
870
871
872 ASO_PRICING_CORE_PVT.Copy_Header_To_Request(
873 p_Request_Type => p_control_rec.request_type,
874 p_price_line_index => l_price_index,
875 px_index_counter => 1);
876
877 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
878 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Append_asked_for...',1,'Y');
879 END IF;
880
881 lx_index_counter := 1;
882 ASO_PRICING_CORE_PVT.Append_asked_for(
883 p_pricing_event => p_control_rec.pricing_event,
884 p_price_line_index => l_price_index,
885 p_header_id => p_qte_header_rec.quote_header_id,
886 px_index_counter => lx_index_counter);
887 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
888 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After Header Append_asked_for: lx_index_counter:'
889 ||lx_index_counter,1,'Y');
890 END IF;
891
892 IF ( p_control_rec.pricing_event = 'BATCH' ) THEN
893 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
894 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Price_Adj_Header...',1,'Y');
895 END IF;
896 ASO_PRICING_CORE_PVT.Query_Price_Adj_Header(
897 p_quote_header_id => p_qte_header_rec.quote_header_id) ;
898 END IF;
899 --increment the line index
900 l_price_index:= l_price_index+1;
901
902 -- Header ends here
903
904 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
905 aso_debug_pub.add('************************ HEADER LEVEL ENDS *******************************',1,'Y');
906 aso_debug_pub.add('Code Path PRICE_MODE = QUOTE_LINE where Line Count <> 0 ',1,'Y');
907 aso_debug_pub.add('************************ LINE LEVEL BEGINS *******************************',1,'Y');
908 END IF;
909
910 -- Added this code to delete all the adjustments when there are no items in the cart
911 IF NVL(p_qte_line_tbl.count,0) = 0 THEN
912 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
913 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Deleting from ASO_PRICE_ADJUSTMENTS if p_qte_line_tbl.count = 0',1,'Y');
914 END IF;
915
916 IF p_control_rec.pricing_event = 'BATCH' THEN
917 DELETE FROM aso_price_adjustments
918 WHERE quote_header_id = p_qte_header_rec.quote_header_id
919 AND quote_line_id IS NULL;
920 End if;--p_control_rec.pricing_event = 'BATCH'
921
922 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
923 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Adjustment Lines deleted if p_qte_line_tbl.count = 0 '||sql%ROWCOUNT,1,'Y');
924 END IF;
925 END IF; -- p_qte_line_tbl.count = 0
926
927
928 IF p_qte_line_tbl.count IS NOT NULL AND
929 p_qte_line_tbl.count > 0
930 THEN
931 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
932 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_line_tbl.count :'||NVL(p_qte_line_tbl.count,0),1,'Y');
933 END IF;
934
935
936 -- Filtering OUT NOCOPY /* file.sql.39 change */ deleted and updated lines from p_qte_line_tbl.
937
938 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
939 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Filtering Quote lines that are deleted...',1,'Y');
940 END IF;
941 l_service_proc_done_flag := 'N';
942 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
943 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Initialize... l_service_proc_done_flag :'||NVL(l_service_proc_done_flag,'null'),1,'Y');
944 END IF;
945 For i in 1..p_qte_line_tbl.count Loop
946 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
947 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_line_tbl('||i||').quote_line_id :'||NVL(to_char(p_qte_line_tbl(i).quote_line_id),'NULL'),1,'Y');
948 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_line_tbl('||i||').operation_code :'||NVL(p_qte_line_tbl(i).operation_code,'null'),1,'Y');
949 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_line_tbl('||i||').service_item_flag :'||NVL(p_qte_line_tbl(i).service_item_flag,'null'),1,'Y');
950 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_line_tbl('||i||').serviceable_product_flag:'||NVL(p_qte_line_tbl(i).serviceable_product_flag,'null'),1,'Y');
951 END IF;
952 IF p_qte_line_tbl(i).operation_code = 'DELETE' THEN
953 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
954 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_line_tbl('||i||').quote_line_id - deleted :'||NVL(to_char(p_qte_line_tbl(i).quote_line_id),'NULL'),1,'Y');
955 END IF;
956 -- This Quote Line is not added to the l_quote_line_tbl.
957 ELSE
958 -- Operation is either CREATE or UPDATE.
959
960 if p_qte_line_tbl(i).operation_code='UPDATE' then
961
962 select nvl(service_item_flag,'N'), nvl(serviceable_product_flag,'N')
963 into l_service_item_flg, l_servicable_item_flg
964 from aso_quote_lines_all
965 where quote_line_id=p_qte_line_tbl(i).quote_line_id;
966 else
967 l_service_item_flg:= NVL(p_qte_line_tbl(i).service_item_flag,'N') ;
968 l_servicable_item_flg:=nvl(p_qte_line_tbl(i).serviceable_product_flag,'N');
969
970 end if;
971 -- Service Processing Logic Begins Here...
972
973 aso_debug_pub.add('rassharm ASO_PRICING_FLOWS_PVT: quote_line_id:'||p_qte_line_tbl(i).quote_line_id,1,'Y');
974 aso_debug_pub.add('rassharm ASO_PRICING_FLOWS_PVT: l_service_item_flg:'||l_service_item_flg,1,'Y');
975 aso_debug_pub.add('rassharm ASO_PRICING_FLOWS_PVT: l_servicable_item_flg:'||l_servicable_item_flg,1,'Y');
976
977 -- IF NVL(p_qte_line_tbl(i).serviceable_product_flag,'N') = 'Y' AND
978 if l_servicable_item_flg='Y' and
979 NVL(l_service_proc_done_flag,'N') = 'N'
980 THEN
981 l_service_product_id_tbl(p_qte_line_tbl(i).quote_line_id) := p_qte_line_tbl(i).quote_line_id;
982 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
983 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_product_id_tbl('||p_qte_line_tbl(i).quote_line_id||'). :'||NVL(to_char(l_service_product_id_tbl(p_qte_line_tbl(i).quote_line_id)),'null'),1,'Y');
984 END IF;
985 END IF;
986
987 -- IF NVL(p_qte_line_tbl(i).service_item_flag,'N') = 'Y' THEN
988 If l_service_item_flg='Y' then
989 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
990 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Service lines processing........ ',1,'Y');
991 END IF;
992 IF NVL(l_service_proc_done_flag,'N') = 'N' THEN
993 FOR j IN i..p_qte_line_tbl.COUNT LOOP
994
995 if p_qte_line_tbl(j).operation_code='UPDATE' then
996
997 select nvl(service_item_flag,'N'), nvl(serviceable_product_flag,'N')
998 into l_service_item_flg1, l_servicable_item_flg1
999 from aso_quote_lines_all
1000 where quote_line_id=p_qte_line_tbl(j).quote_line_id;
1001 else
1002 l_service_item_flg1:= NVL(p_qte_line_tbl(j).service_item_flag,'N');
1003 l_servicable_item_flg1:=nvl(p_qte_line_tbl(j).serviceable_product_flag,'N');
1004
1005 end if;
1006 aso_debug_pub.add('rassharm ASO_PRICING_FLOWS_PVT: quote_line_id:'||p_qte_line_tbl(j).quote_line_id,1,'Y');
1007 aso_debug_pub.add('rassharm ASO_PRICING_FLOWS_PVT: l_service_item_flg1:'||l_service_item_flg1,1,'Y');
1008 aso_debug_pub.add('rassharm ASO_PRICING_FLOWS_PVT: l_servicable_item_flg1:'||l_servicable_item_flg1,1,'Y');
1009
1010 IF l_servicable_item_flg1 = 'Y' THEN
1011 l_service_product_id_tbl(p_qte_line_tbl(j).quote_line_id) := p_qte_line_tbl(j).quote_line_id;
1012 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1013 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_product_id_tbl('||p_qte_line_tbl(j).quote_line_id||'). :'||NVL(to_char(l_service_product_id_tbl(p_qte_line_tbl(j).quote_line_id)),'null'),1,'Y');
1014 END IF;
1015 END IF;
1016 IF l_service_item_flg1 = 'Y' THEN
1017 l_service_id_tbl(p_qte_line_tbl(j).quote_line_id) := p_qte_line_tbl(j).quote_line_id;
1018 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1019 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_id_tbl('||p_qte_line_tbl(j).quote_line_id||'). :'||NVL(to_char(l_service_id_tbl(p_qte_line_tbl(j).quote_line_id)),'null'),1,'Y');
1020 END IF;
1021 END IF;
1022 END LOOP;
1023 l_service_proc_done_flag := 'Y';
1024 END IF;
1025 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1026 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_proc_done_flag: '||NVL(l_service_proc_done_flag,'null'),1,'Y');
1027 IF (l_service_id_tbl.exists(1)) THEN
1028 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_id_tbl.count: '||NVL(l_service_id_tbl.count,0),1,'Y');
1029 END IF;
1030 IF (l_service_product_id_tbl.exists(1)) THEN
1031 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_product_id_tbl.count: '||NVL(l_service_product_id_tbl.count,0),1,'Y');
1032 END IF;
1033 END IF;
1034
1035 OPEN c_qte_line_SVC_ref(p_qte_line_tbl(i).quote_line_id);
1036 FETCH c_qte_line_SVC_ref INTO l_ref_type_code, l_service_ref_line_id;
1037 CLOSE c_qte_line_SVC_ref;
1038
1039 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1040 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_ref_type_code: '||NVL(l_ref_type_code,'null'),1,'Y');
1041 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_ref_line_id: '||NVL(to_char(l_service_ref_line_id),'NULL'),1,'Y');
1042 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Service Line collection to pass for Query Relationships procedure... ',1,'Y');
1043 END IF;
1044
1045 -- l_service_qte_line_id_tbl is used to collect all quote line id(s) of service lines only.
1046 -- l_service_qte_line_id_tbl is one of the parameter for ASO_PRICING_CORE_PVT.Query_Relationships procedure.
1047 IF l_service_qte_line_id_tbl.exists(1) THEN
1048 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1049 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Service Qte Line ID Tbl already exists... ',1,'Y');
1050 END IF;
1051 l_service_qte_line_id_tbl.extend;
1052 l_service_qte_line_id_tbl(l_service_qte_line_id_tbl.COUNT) := p_qte_line_tbl(i).quote_line_id;
1053 ELSE
1054 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1055 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Service Qte Line ID Tbl NOT exists... ',1,'Y');
1056 END IF;
1057 l_service_qte_line_id_tbl := JTF_NUMBER_TABLE(p_qte_line_tbl(i).quote_line_id);
1058 END IF;
1059
1060 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1061 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Parent Service Line collection ... ',1,'Y');
1062 END IF;
1063
1064
1065 -- l_service_ref_line_id_tbl is used for parent line id collection and its different
1066 -- from l_service_qte_line_id_tbl.
1067
1068 IF l_ref_type_code = 'ORDER' THEN
1069 IF l_service_ref_line_id_tbl.exists(l_service_ref_line_id) THEN
1070 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1071 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Parent Order Line exists in p_qte_line_tbl. ',1,'Y');
1072 END IF;
1073 ELSE
1074 FOR c_order_line_rec IN c_order_line(l_service_ref_line_id) LOOP
1075 l_service_ref_line_id_tbl(l_service_ref_line_id) := l_service_ref_line_id;
1076 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1077 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Parent Order Line Not Exists... ', 1, 'N');
1078 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_ref_line_id_tbl('||l_service_ref_line_id||').: '||NVL(to_char(l_service_ref_line_id_tbl(l_service_ref_line_id)),'null'),1,'Y');
1079 END IF;
1080 l_qte_line_rec.QUOTE_LINE_ID := c_order_line_rec.LINE_ID;
1081 l_qte_line_rec.INVENTORY_ITEM_ID := c_order_line_rec.INVENTORY_ITEM_ID;
1082 l_qte_line_rec.QUANTITY := c_order_line_rec.PRICING_QUANTITY;
1083 l_qte_line_rec.UOM_CODE := c_order_line_rec.PRICING_QUANTITY_UOM;
1084 l_qte_line_rec.PRICE_LIST_ID := c_order_line_rec.PRICE_LIST_ID;
1085 l_qte_line_rec.LINE_LIST_PRICE := c_order_line_rec.UNIT_LIST_PRICE;
1086 l_qte_line_rec.LINE_CATEGORY_CODE := 'SERVICE_REF_ORDER_LINE';
1087
1088 l_Qte_Line_tbl(l_Qte_Line_tbl.COUNT+1) := l_Qte_Line_rec;
1089 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1090 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Order Line Count: '|| NVL(l_Qte_line_tbl.COUNT,0), 1, 'N');
1091 END IF;
1092 END LOOP;
1093 END IF; -- Parent line already exists.
1094 ELSIF l_ref_type_code = 'CUSTOMER_PRODUCT' THEN
1095 IF l_service_ref_line_id_tbl.exists(l_service_ref_line_id) THEN
1096 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1097 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Parent Instance Line exists in p_qte_line_tbl. ',1,'Y');
1098 END IF;
1099 ELSE
1100 -- FOR c_get_cust_acct_id_rec IN c_get_cust_acct_id LOOP
1101 /*** Start: BugNo 8647883: R12.1.2 Service reference SUN ER ***/
1102 open c_get_cust_acct_id_ln(p_qte_line_tbl(i).quote_line_id);
1103 fetch c_get_cust_acct_id_ln into l_cust_account_id;
1104 if (c_get_cust_acct_id_ln%NOTFOUND) or (l_cust_account_id is null) THEN
1105 open c_get_cust_acct_id;
1106 fetch c_get_cust_acct_id into l_cust_account_id;
1107 if c_get_cust_acct_id%NOTFOUND THEN
1108 l_cust_account_id := NULL;
1109 end if;
1110 close c_get_cust_acct_id;
1111 end if;
1112 close c_get_cust_acct_id_ln;
1113 /*** End: BugNo 8647883: R12.1.2 Service reference SUN ER ***/
1114 FOR c_csi_line_rec IN c_csi_line
1115 (l_service_ref_line_id,l_cust_account_id) LOOP
1116 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1117 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_cust_account_id'||l_cust_account_id, 1, 'N');
1118 END IF;
1119 l_service_ref_line_id_tbl(l_service_ref_line_id) := l_service_ref_line_id;
1120 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1121 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Parent Instance Line not exists...', 1, 'N');
1122 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_ref_line_id_tbl('||l_service_ref_line_id||').: '||NVL(to_char(l_service_ref_line_id_tbl(l_service_ref_line_id)),'null'),1,'Y');
1123 END IF;
1124 FOR c_order_line_rec IN c_order_line(c_csi_line_rec.original_order_line_id) LOOP
1125 l_order_found := TRUE;
1126 l_qte_line_rec.QUOTE_LINE_ID := l_service_ref_line_id;
1127 l_qte_line_rec.INVENTORY_ITEM_ID := c_order_line_rec.INVENTORY_ITEM_ID;
1128 l_qte_line_rec.QUANTITY := c_order_line_rec.PRICING_QUANTITY;
1129 l_qte_line_rec.UOM_CODE := c_order_line_rec.PRICING_QUANTITY_UOM;
1130 l_qte_line_rec.PRICE_LIST_ID := c_order_line_rec.PRICE_LIST_ID;
1131 l_qte_line_rec.LINE_LIST_PRICE := c_order_line_rec.UNIT_LIST_PRICE;
1132 l_qte_line_rec.LINE_CATEGORY_CODE := 'SERVICE_REF_CUSTOMER_LINE';
1133 l_Qte_Line_tbl(l_Qte_Line_tbl.COUNT+1) := l_Qte_Line_rec;
1134 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1135 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Instance Line Count: '|| NVL(l_Qte_line_tbl.COUNT,0), 1, 'N');
1136 END IF;
1137 END LOOP;--c_order_line
1138 /******* Start SUN Changes ER: 3802859 ***********/
1139
1140 IF l_order_found = FALSE THEN -- this means no order line was found then
1141 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1142 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** ER: 3802859 ****** Inside new condition', 1, 'N');
1143 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** ER: 3802859 ****** l_service_ref_line_id: '|| l_service_ref_line_id, 1, 'N');
1144 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** ER: 3802859 ****** l_cust_account_id: '|| l_cust_account_id, 1, 'N');
1145 END IF;
1146
1147 open c_csi_line_details(l_service_ref_line_id,l_cust_account_id);
1148 fetch c_csi_line_details into l_prod,l_item_id,l_qty,l_uom;
1149
1150 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1151 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** SUN ****** After getting the csi line details', 1, 'N');
1152 END IF;
1153
1154 l_qte_line_rec.QUOTE_LINE_ID := l_service_ref_line_id;
1155 l_qte_line_rec.INVENTORY_ITEM_ID := l_item_id;
1156 l_qte_line_rec.QUANTITY := l_qty;
1157 l_qte_line_rec.UOM_CODE := l_uom;
1158
1159 -- Get the price list from the header
1160 open c_get_price_list(p_qte_header_rec.quote_header_id);
1161 fetch c_get_price_list INTO l_qte_line_rec.PRICE_LIST_ID;
1162 CLOSE c_get_price_list;
1163
1164 l_qte_line_rec.LINE_LIST_PRICE := 0;
1165 l_qte_line_rec.LINE_CATEGORY_CODE := 'SERVICE_REF_CUSTOMER_LINE';
1166 l_Qte_Line_tbl(l_Qte_Line_tbl.COUNT+1) := l_Qte_Line_rec;
1167
1168 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1169 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** ER: 3802859 ****** l_service_ref_line_id: '|| l_service_ref_line_id, 1, 'N');
1170 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** ER: 3802859 ****** l_item_id: '|| l_item_id, 1, 'N');
1171 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** ER: 3802859 ****** l_qty: '|| l_qty, 1, 'N');
1172 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** ER: 3802859 ****** l_uom: '|| l_uom, 1, 'N');
1173 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** ER: 3802859 ****** l_qte_line_rec.PRICE_LIST_ID: '||l_qte_line_rec.PRICE_LIST_ID, 1, 'N');
1174 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** ER: 3802859 ****** Instance Line Count: '|| NVL(l_Qte_line_tbl.COUNT,0), 1, 'N');
1175 END IF;
1176
1177 close c_csi_line_details;
1178 END IF;
1179
1180 /******* End SUN Changes ER: 3802859 ***********/
1181
1182
1183
1184 END LOOP;--c_csi_line
1185 --END LOOP;-- c_get_cust_acct_id
1186 END IF; -- Parent line already exists.
1187 ELSIF l_ref_type_code = 'QUOTE' THEN
1188 IF l_service_ref_line_id_tbl.exists(l_service_ref_line_id) OR
1189 l_service_product_id_tbl.exists(l_service_ref_line_id)
1190 THEN
1191 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1192 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Parent Quote Line exists in p_qte_line_tbl. ',1,'Y');
1193 END IF;
1194 ELSE
1195 l_service_ref_line_id_tbl(l_service_ref_line_id) := l_service_ref_line_id;
1196 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1197 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Parent Quote Line NOT exists in p_qte_line_tbl. ',1,'Y');
1198 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_ref_line_id_tbl('||l_service_ref_line_id||'). :'||NVL(to_char(l_service_ref_line_id_tbl(l_service_ref_line_id)),'null'),1,'Y');
1199 END IF;
1200 l_qte_line_tbl(l_qte_line_tbl.COUNT+1) := ASO_UTILITY_PVT.Query_Qte_Line_Row(l_service_ref_line_id);
1201 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1202 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Quote Line Count: '|| NVL(l_Qte_line_tbl.COUNT,0), 1, 'N');
1203 END IF;
1204 END IF;-- Parent line already exists.
1205 /*** Start: BugNo 8647883: R12.1.2 Service reference SUN ER ***/
1206 ELSIF l_ref_type_code = 'PRODUCT_CATALOG' THEN
1207 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1208 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** SUN ER PRODUCT CATALOG ****** ', 1, 'N');
1209 END IF;
1210 IF l_service_ref_line_id_tbl.exists(l_service_ref_line_id) THEN
1211 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1212 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Parent Instance Line exists in p_qte_line_tbl. ',1,'Y');
1213 END IF;
1214 ELSE
1215 l_service_ref_line_id_tbl(l_service_ref_line_id) := l_service_ref_line_id;
1216 l_qte_line_rec.QUOTE_LINE_ID := l_service_ref_line_id;
1217 l_qte_line_rec.INVENTORY_ITEM_ID := l_service_ref_line_id;
1218 l_qte_line_rec.QUANTITY :=p_qte_line_tbl(i).QUANTITY;
1219 l_qte_line_rec.UOM_CODE := p_qte_line_tbl(i).UOM_CODE;
1220
1221 -- Get the price list from the header
1222 open c_get_price_list(p_qte_header_rec.quote_header_id);
1223 fetch c_get_price_list INTO l_qte_line_rec.PRICE_LIST_ID;
1224 CLOSE c_get_price_list;
1225
1226 l_qte_line_rec.LINE_LIST_PRICE := 0;
1227 l_qte_line_rec.LINE_CATEGORY_CODE := 'SERVICE_REF_CUSTOMER_LINE';
1228 l_Qte_Line_tbl(l_Qte_Line_tbl.COUNT+1) := l_Qte_Line_rec;
1229
1230 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1231 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** SUN ER PRODUCT CATALOG ****** l_service_ref_line_id: '|| l_service_ref_line_id, 1, 'N');
1232 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** SUN ER PRODUCT CATALOG ****** l_item_id: '|| l_service_ref_line_id, 1, 'N');
1233 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** SUN ER PRODUCT CATALOG ****** l_qty: '|| p_qte_line_tbl(i).QUANTITY, 1, 'N');
1234 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** SUN ER PRODUCT CATALOG ****** l_uom: '|| p_qte_line_tbl(i).UOM_CODE, 1, 'N');
1235 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** SUN ER PRODUCT CATALOG ****** l_qte_line_rec.PRICE_LIST_ID: '||l_qte_line_rec.PRICE_LIST_ID, 1, 'N');
1236 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: **** SUN ER PRODUCT CATALOG ****** Instance Line Count: '|| NVL(l_Qte_line_tbl.COUNT,0), 1, 'N');
1237 END IF;
1238 END IF;-- l_service_ref_line_id_tbl
1239 /*** End: BugNo 8647883: R12.1.2 Service reference SUN ER ***/
1240 END IF; -- ref_type_code check.
1241 END IF; -- Service Item Flag Check.
1242 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1243 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start the Process of adding the service(child)or any normal lines into l_qte_line_tbl ...',1,'Y');
1244 END IF;
1245 IF l_qte_line_tbl.exists(1) THEN
1246 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1247 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: One or more records exists in l_qte_line_tbl...',1,'Y');
1248 END IF;
1249 --changes for bug 4353654
1250 l_qte_line_rec := ASO_UTILITY_PVT.Query_Qte_Line_Row(p_qte_line_tbl(i).quote_line_id);
1251 l_qte_line_tbl(l_qte_line_tbl.COUNT+1) := l_qte_line_rec;
1252 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1253 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After adding a new record in l_qte_line_tbl...',1,'Y');
1254 END IF;
1255 ELSE
1256 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1257 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: No record exists in l_qte_line_tbl...',1,'Y');
1258 END IF;
1259 l_qte_line_tbl(1) := ASO_UTILITY_PVT.Query_Qte_Line_Row(p_qte_line_tbl(i).quote_line_id);
1260 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1261 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After adding a new record in l_qte_line_tbl...',1,'Y');
1262 END IF;
1263 END IF; -- l_qte_line_tbl.exists.
1264 END IF; -- operation code check
1265 END LOOP;
1266
1267 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1268 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: TOTAL Number of records in l_qte_line_tbl... :'||NVL(l_qte_line_tbl.count,0),1,'Y');
1269 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: *************** l_qte_line_tbl List **************** ',1,'Y');
1270 For i in 1..l_qte_line_tbl.count Loop
1271 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT : l_qte_line_tbl('||i||').quote_line_id :'||NVL(l_qte_line_tbl(i).quote_line_id,0),1,'Y');
1272 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT : l_qte_line_tbl('||i||').serviceable_product_flag:'||NVL(l_qte_line_tbl(i).serviceable_product_flag,'null'),1,'Y');
1273 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT : l_qte_line_tbl('||i||').service_item_flag:'||NVL(l_qte_line_tbl(i).service_item_flag,'null'),1,'Y');
1274 END LOOP;
1275 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: *************** l_qte_line_tbl List End **************** ',1,'Y');
1276 END IF;
1277
1278 IF (l_service_qte_line_id_tbl.exists(1)) THEN
1279 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1280 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_qte_line_id_tbl.count :'||NVL(l_service_qte_line_id_tbl.count,0),1,'Y');
1281 END IF;
1282 ELSE
1283 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1284 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: No service lines exists',1,'Y');
1285 END IF;
1286 END IF;
1287
1288 For i in 1..l_qte_line_tbl.count Loop
1289
1290 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1291 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Line_Dtl_Rows...',1,'Y');
1292 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl('||i||').quote_line_id :'||NVL(l_qte_line_tbl(i).quote_line_id,0),1,'Y');
1293 END IF;
1294 l_qte_line_id := l_qte_line_tbl(i).quote_line_id;
1295 l_qte_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows(l_qte_line_id);
1296
1297
1298 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1299 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Defaulting Hdr Lvl Price List and Currency..',1,'Y');
1300 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_header_rec.currency_code :'||NVL(p_qte_header_rec.currency_code,'NULL'),1,'Y');
1301 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_header_rec.price_list_id :'||NVL(to_char(p_qte_header_rec.price_list_id),'NULL'),1,'Y');
1302 END IF;
1303 -- Pass header_level currency_code to line level by default.
1304 l_qte_line_tbl(i).currency_code := p_qte_header_rec.currency_code;
1305
1306
1307 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1308 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl(i).price_list_id :'||NVL(to_char(l_qte_line_tbl(i).price_list_id),'NULL'),1,'Y');
1309 END IF;
1310 -- Pass Header level price list by default.
1311 IF (l_qte_line_tbl(i).price_list_id is null OR
1312 l_qte_line_tbl(i).price_list_id= FND_API.G_MISS_NUM) THEN
1313 l_qte_line_tbl(i).price_list_id := p_qte_header_rec.price_list_id;
1314 END IF;
1315
1316
1317 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1318 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_dtl_tbl.count :'||NVL(l_qte_line_dtl_tbl.count,0),1,'Y');
1319 END IF;
1320 IF l_qte_line_dtl_tbl.count = 1 THEN
1321 l_qte_line_dtl_rec := l_qte_line_dtl_tbl(1);
1322 ELSE
1323 l_qte_line_dtl_rec := ASO_QUOTE_PUB.G_Miss_Qte_Line_Dtl_REC ;
1324 END IF;
1325
1326 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1327 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Shipment_Rows..',1,'Y');
1328 END IF;
1329 l_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows
1330 (p_qte_header_rec.quote_header_id, l_QTE_LINE_ID);
1331 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1332 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_shipment_tbl.count :'||NVL(l_shipment_tbl.count,0),1,'Y');
1333 END IF;
1334 l_shipment_rec := ASO_QUOTE_PUB.G_Miss_Shipment_rec;
1335 IF l_shipment_tbl.count = 1 THEN
1336 l_shipment_rec := l_shipment_tbl(1);
1337 END IF;
1338
1339
1340 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1341 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Set_Global_Rec - Line Level...', 1, 'Y');
1342 END IF;
1343 ASO_PRICING_INT.G_LINE_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
1344 p_qte_line_rec => l_qte_line_tbl(i),
1345 p_qte_line_dtl_rec => l_qte_line_dtl_rec,
1346 p_shipment_rec => l_shipment_rec);
1347
1348 ASO_PRICING_CORE_PVT.Print_G_Line_Rec;
1349
1350
1351 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1352 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of QP Build_Contexts - Line Level...',1,'Y');
1353 END IF;
1354
1355 QP_ATTR_MAPPING_PUB.Build_Contexts (
1356 p_request_type_code => p_control_rec.request_type,
1357 p_line_index => l_price_index,
1358 p_check_line_flag => 'N',
1359 p_pricing_event => p_control_rec.pricing_event,
1360 p_pricing_type_code => 'L',
1361 p_price_list_validated_flag => 'N',
1362 x_pass_line => x_pass_line);
1363
1364
1365 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1366 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: x_pass_line : '||nvl(x_pass_line,'null'),1,'Y');
1367 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Copy_Line_To_Request...',1,'Y');
1368 END IF;
1369
1370 -- Begin fix for bug 5951790.
1371 -- We should send null as selling_price to pricing engine when the pricing_event is PRICE.
1372
1373 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1374
1375 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_control_rec.pricing_event: ' || p_control_rec.pricing_event,1,'Y');
1376 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Old ASO_PRICING_INT.G_LINE_REC.line_quote_price: ' || ASO_PRICING_INT.G_LINE_REC.line_quote_price,1,'Y');
1377
1378 END IF;
1379
1380 IF (p_control_rec.pricing_event = 'PRICE') THEN
1381 ASO_PRICING_INT.G_LINE_REC.line_quote_price := null;
1382 END IF;
1383
1384 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1385 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: New ASO_PRICING_INT.G_LINE_REC.line_quote_price: ' || ASO_PRICING_INT.G_LINE_REC.line_quote_price,1,'Y');
1386 END IF;
1387
1388 -- End fix for bug 5951790.
1389
1390 ASO_PRICING_CORE_PVT.Copy_Line_To_Request(
1391 p_Request_Type => p_control_rec.request_type,
1392 p_price_line_index => l_price_index,
1393 px_index_counter => i+1);
1394
1395
1396 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1397 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Append_asked_for...',1,'Y');
1398 END IF;
1399
1400 l_index_counter := lx_index_counter;
1401 ASO_PRICING_CORE_PVT.Append_asked_for(
1402 p_pricing_event => p_control_rec.pricing_event
1403 ,p_price_line_index => l_price_index
1404 ,p_header_id => p_qte_header_rec.quote_header_id
1405 ,p_line_id => l_qte_line_tbl(i).quote_line_id
1406 ,px_index_counter => lx_index_counter);
1407 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1408 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After Line Append_asked_for: lx_index_counter : '||NVL(lx_index_counter,0),1,'Y');
1409 END IF;
1410 IF lx_index_counter = 0 THEN
1411 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1412 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Assigning the value of l_index_counter back to lx_index_counter:'||l_index_counter,1,'Y');
1413 END IF;
1414 lx_index_counter := l_index_counter;
1415 END IF;
1416
1417 IF ( i = 1 ) THEN
1418 l_Qte_Line_id_tbl := JTF_NUMBER_TABLE(l_qte_line_tbl(i).quote_line_id);
1419 ELSE
1420 l_Qte_Line_id_tbl.extend;
1421 l_Qte_Line_id_tbl(i) := l_qte_line_tbl(i).quote_line_id;
1422 END IF;
1423
1424 --increment the line index
1425 px_line_index_search_tbl(l_qte_line_id) := l_price_index;
1426 l_price_index:= l_price_index+1;
1427
1428 END LOOP;
1429
1430 -- Call to Price Adjustment Relationships and Service Relationships
1431 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1432 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Price_Adjustments...',1,'Y');
1433 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_id_tbl.count : '||NVL(l_qte_line_id_tbl.count,0),1,'Y');
1434 END IF;
1435
1436 IF p_control_rec.pricing_event = 'BATCH' THEN
1437 ASO_PRICING_CORE_PVT.Query_Price_Adjustments(
1438 p_quote_header_id => p_qte_header_rec.quote_header_id,
1439 p_qte_line_id_tbl => l_qte_line_id_tbl,
1440 x_adj_id_tbl => l_qte_adj_id_tbl);
1441 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1442 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_adj_id_tbl.count : '||NVL(l_qte_adj_id_tbl.count,0),1,'Y');
1443 END IF;
1444 END IF;--p_control_rec.pricing_event = 'BATCH'
1445
1446 -- Adjustment,line,Services Relationships.
1447 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1448 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Relationships...',1,'Y');
1449 END IF;
1450 ASO_PRICING_CORE_PVT.Query_Relationships(
1451 p_qte_adj_id_tbl => l_qte_adj_id_tbl,
1452 p_service_qte_line_id_tbl => l_service_qte_line_id_tbl);
1453
1454 FOR i IN 1..l_qte_line_tbl.count LOOP
1455 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1456 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin DELETE FROM ASO_PRICE_ADJ_ATTRIBS', 1, 'Y');
1457 END IF;
1458 IF p_control_rec.pricing_event = 'BATCH' THEN
1459 DELETE FROM ASO_PRICE_ADJ_ATTRIBS
1460 WHERE PRICE_ADJUSTMENT_ID IN (SELECT PRICE_ADJUSTMENT_ID
1461 FROM ASO_PRICE_ADJUSTMENTS
1462 WHERE QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
1463 AND QUOTE_LINE_ID = l_qte_line_tbl(i).quote_line_id);
1464
1465 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1466 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Adj Attribs Lines deleted '||sql%ROWCOUNT,1,'Y');
1467 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin DELETE FROM ASO_PRICE_ADJ_RELATIONSHIPS', 1, 'Y');
1468 END IF;
1469 DELETE FROM ASO_PRICE_ADJ_RELATIONSHIPS
1470 WHERE QUOTE_LINE_ID = l_qte_line_tbl(i).quote_line_id;
1471 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1472 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Rltd adj Lines deleted '||sql%ROWCOUNT,1,'Y');
1473 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin DELETE FROM ASO_PRICE_ADJUSTMENTS', 1, 'Y');
1474 END IF;
1475
1476 DELETE FROM ASO_PRICE_ADJUSTMENTS
1477 WHERE quote_line_id = l_qte_line_tbl(i).quote_line_id;
1478 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1479 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Adjustment Lines deleted '||sql%ROWCOUNT,1,'Y');
1480 END IF;
1481 END IF;--p_control_rec.pricing_event = 'BATCH'
1482 END LOOP;
1483
1484 END IF; -- p_qte_line_tbl.count > 0.
1485
1486 If l_qte_line_tbl.count >0 then
1487
1488 --Need to modify the global index table of pls integer types
1489 l_global_pls_tbl := ASO_PRICING_CORE_PVT.G_LDET_LINE_INDEX_TBL;
1490 ASO_PRICING_CORE_PVT.G_LDET_LINE_INDEX_TBL:= ASO_PRICING_CORE_PVT.Modify_Global_PlsIndex_Table(
1491 p_global_tbl => l_global_pls_tbl,
1492 p_search_tbl => px_line_index_search_tbl);
1493 l_global_num_tbl := ASO_PRICING_CORE_PVT.G_RLTD_LINE_INDEX_TBL;
1494 ASO_PRICING_CORE_PVT.G_RLTD_LINE_INDEX_TBL:= ASO_PRICING_CORE_PVT.Modify_Global_NumIndex_Table(
1495 p_global_tbl => l_global_num_tbl,
1496 p_search_tbl => px_line_index_search_tbl);
1497 l_global_num_tbl := ASO_PRICING_CORE_PVT.G_RLTD_RELATED_LINE_IND_TBL;
1498 ASO_PRICING_CORE_PVT.G_RLTD_RELATED_LINE_IND_TBL:= ASO_PRICING_CORE_PVT.Modify_Global_NumIndex_Table(
1499 p_global_tbl => l_global_num_tbl,
1500 p_search_tbl => px_line_index_search_tbl);
1501
1502 End If; -- l_qte_line_tbl.count >0
1503
1504 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1505 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Populate_QP_Temp_tables...',1,'Y');
1506 END IF;
1507
1508 ASO_PRICING_CORE_PVT.populate_qp_temp_tables;
1509
1510 -- Set the control rec for QP
1511
1512 l_req_control_rec.pricing_event := p_control_rec.pricing_event;
1513 l_req_control_rec.calculate_flag := p_control_rec.calculate_flag;
1514 l_req_control_rec.simulation_flag := p_control_rec.simulation_flag;
1515 l_req_control_rec.TEMP_TABLE_INSERT_FLAG := 'N'; ---- Modified
1516 l_req_control_rec.source_order_amount_flag := 'Y';
1517 l_req_control_rec.GSA_CHECK_FLAG := 'Y';
1518 l_req_control_rec.GSA_DUP_CHECK_FLAG := 'Y';
1519 l_req_control_rec.REQUEST_TYPE_CODE := p_control_rec.request_type;
1520 l_req_control_rec.rounding_flag := 'Q';
1521
1522 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1523 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of QP_PREQ_PUB.PRICE_REQUEST', 1, 'Y');
1524 END IF;
1525
1526 /* Change for populating QP_PREQ_GRP.CONTROL_RECORD_TYPE.ORG_ID Yogeshwar (MOAC) */
1527
1528 l_req_control_rec.ORG_ID := p_qte_header_rec.org_id;
1529
1530 /* End of Change (MOAC) */
1531
1532
1533
1534 QP_PREQ_PUB.PRICE_REQUEST
1535 (p_control_rec =>l_req_control_rec
1536 ,x_return_status =>l_return_status
1537 ,x_return_status_Text =>l_return_status_Text
1538 );
1539
1540 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1541 x_return_status := l_return_status;
1542 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1543 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR');
1544 FND_MESSAGE.Set_Token('MSG_TXT', substr(l_return_status_text, 1,255), FALSE);
1545 FND_MSG_PUB.ADD;
1546 END IF;
1547 RAISE FND_API.G_EXC_ERROR;
1548 END IF;
1549
1550 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1551 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: End of QP_PREQ_PUB.PRICE_REQUEST', 1, 'Y');
1552 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After PRICE_REQUEST l_return_status:'
1553 ||l_return_status, 1, 'Y');
1554 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After PRICE_REQUEST l_return_status_text '
1555 ||l_return_status_text,1,'Y');
1556 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Copy_Price_To_Quote...',1,'Y');
1557 END IF;
1558
1559
1560 ASO_PRICING_CORE_PVT.Copy_Price_To_Quote(
1561 P_Api_Version_Number => 1.0,
1562 P_Init_Msg_List => FND_API.G_FALSE,
1563 P_Commit => p_commit,
1564 p_control_rec => p_control_rec,
1565 p_qte_header_rec => p_qte_header_rec,
1566 x_return_status => x_return_status,
1567 x_msg_count => x_msg_count,
1568 x_msg_data => x_msg_data);
1569 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1570 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Copy_Price_To_Quote : x_return_status: '
1571 ||NVL(x_return_status,'X'),1,'Y');
1572 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Copy_Price_To_Quote : x_msg_count: '
1573 ||NVL(x_msg_count,0),1,'Y');
1574 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Copy_Price_To_Quote : x_msg_data: '
1575 ||NVL(x_msg_data,'X'),1,'Y');
1576 END IF;
1577
1578 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1579
1580 ASO_PRICING_CORE_PVT.Process_Charges(
1581 P_Api_Version_Number => 1.0,
1582 P_Init_Msg_List => FND_API.G_FALSE,
1583 P_Commit => p_commit,
1584 p_control_rec => p_control_rec,
1585 p_qte_header_rec => p_qte_header_rec,
1586 x_return_status => x_return_status,
1587 x_msg_count => x_msg_count,
1588 x_msg_data => x_msg_data);
1589 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1590 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Process_Charges : x_return_status: '
1591 ||nvl(x_return_status,'NULL'),1,'Y');
1592 END IF;
1593
1594 END IF;--x_return_status = FND_API.G_RET_STS_SUCCESS
1595 FND_MSG_PUB.Count_And_Get
1596 ( p_encoded => 'F',
1597 p_count => x_msg_count,
1598 p_data => x_msg_data
1599 );
1600
1601 FOR l IN 1 .. x_msg_count LOOP
1602 x_msg_data := FND_MSG_PUB.GET( p_msg_index => l, p_encoded => 'F');
1603 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1604 aso_debug_pub.add('Messge count and get '||x_msg_data, 1, 'Y');
1605 aso_debug_pub.add('Messge count and get '||x_msg_count, 1, 'Y');
1606 END IF;
1607 END LOOP;
1608
1609 EXCEPTION
1610 WHEN FND_API.G_EXC_ERROR THEN
1611 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1612 aso_debug_pub.add('after inside EXCEPTION return status'||x_return_status, 1, 'Y');
1613 END IF;
1614 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1615 P_API_NAME => L_API_NAME
1616 ,P_PKG_NAME => G_PKG_NAME
1617 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1618 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1619 ,P_SQLCODE => SQLCODE
1620 ,P_SQLERRM => SQLERRM
1621 ,X_MSG_COUNT => X_MSG_COUNT
1622 ,X_MSG_DATA => X_MSG_DATA
1623 ,X_RETURN_STATUS => X_RETURN_STATUS);
1624
1625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1626 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1627 P_API_NAME => L_API_NAME
1628 ,P_PKG_NAME => G_PKG_NAME
1629 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1630 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1631 ,P_SQLCODE => SQLCODE
1632 ,P_SQLERRM => SQLERRM
1633 ,X_MSG_COUNT => X_MSG_COUNT
1634 ,X_MSG_DATA => X_MSG_DATA
1635 ,X_RETURN_STATUS => X_RETURN_STATUS);
1636
1637 WHEN OTHERS THEN
1638 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1639 P_API_NAME => L_API_NAME
1640 ,P_PKG_NAME => G_PKG_NAME
1641 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1642 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1643 ,P_SQLCODE => SQLCODE
1644 ,P_SQLERRM => SQLERRM
1645 ,X_MSG_COUNT => X_MSG_COUNT
1646 ,X_MSG_DATA => X_MSG_DATA
1647 ,X_RETURN_STATUS => X_RETURN_STATUS);
1648
1649 END Price_Quote_Line;
1650
1651
1652 /******************************************************************************
1653 * *
1654 * Code Path with change lines logic *
1655 * *
1656 ******************************************************************************/
1657
1658 PROCEDURE Price_Quote_With_Change_Lines(
1659 P_Api_Version_Number IN NUMBER,
1660 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1661 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1662 p_control_rec IN ASO_PRICING_INT.PRICING_CONTROL_REC_TYPE,
1663 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
1664 p_hd_shipment_rec IN ASO_QUOTE_PUB.Shipment_Rec_Type
1665 := ASO_QUOTE_PUB.G_Miss_Shipment_Rec,
1666 p_qte_line_tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
1667 p_internal_call_flag IN VARCHAR2 := 'N',
1668 x_qte_line_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
1669 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1670 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1671 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1672 IS
1673 l_api_name CONSTANT VARCHAR2(30) := 'Price_Quote_With_Change_Lines';
1674 l_api_version_number CONSTANT NUMBER := 1.0;
1675 l_req_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
1676 l_prc_control_rec ASO_PRICING_INT.PRICING_CONTROL_REC_TYPE;
1677 l_qte_line_id NUMBER;
1678 l_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type;
1679 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1680 l_return_status VARCHAR2(1);
1681 l_return_status_text VARCHAR2(2000);
1682 l_message_text VARCHAR2(2000);
1683 i BINARY_INTEGER;
1684 j BINARY_INTEGER;
1685 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1686 lx_Qte_Line_Tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1687 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1688 l_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1689 x_pass_line VARCHAR2(10);
1690 l_qte_line_id_tbl JTF_NUMBER_TABLE;
1691 l_qte_adj_id_tbl JTF_NUMBER_TABLE;
1692 l_service_qte_line_id_tbl JTF_NUMBER_TABLE;
1693 l_index_counter Number; -- This is used to keep track of lx_index_counter
1694 lx_index_counter Number;
1695 lx_order_status_rec QP_UTIL_PUB.ORDER_LINES_STATUS_REC_TYPE;
1696 l_adj_id_tbl Index_Link_Tbl_Type;
1697 l_price_index Number;
1698 px_line_index_search_tbl ASO_PRICING_CORE_PVT.Index_Link_Tbl_Type;
1699 l_global_pls_tbl QP_PREQ_GRP.pls_integer_type;
1700 l_global_num_tbl QP_PREQ_GRP.NUMBER_TYPE;
1701
1702 l_changed_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1703 l_changed_line_index NUMBER;
1704 l_check_line_flag VARCHAR2(1);
1705 l_complete_qte_flag VARCHAR2(1);
1706 l_db_ln_counter NUMBER;
1707 l_processed_flag VARCHAR2(1);
1708
1709 l_pricing_start_time NUMBER;
1710 l_pricing_end_time NUMBER;
1711 l_accum_aso_time NUMBER;
1712 l_accum_qp_time NUMBER;
1713
1714 l_item_type_code VARCHAR2(30);
1715
1716 -- bug 13482837
1717 ls_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1718 ls number:=0;
1719
1720
1721 BEGIN
1722
1723 -- Standard call to check for call compatibility.
1724 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1725 p_api_version_number,
1726 l_api_name,
1727 G_PKG_NAME)
1728 THEN
1729 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1730 END IF;
1731
1732 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1733 l_pricing_start_time := dbms_utility.get_time;
1734 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Start of Price_Quote_With_Change_Lines',1,'Y');
1735 END IF;
1736
1737 x_return_status := FND_API.G_RET_STS_SUCCESS;
1738
1739 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1740 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:p_control_rec.request_type:'||p_control_rec.request_type,1,'Y');
1741 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:p_control_rec.pricing_event:'||p_control_rec.pricing_event,1,'Y');
1742 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:p_control_rec.price_mode:'||p_control_rec.price_mode,1,'Y');
1743 END IF;
1744
1745 l_prc_control_rec := p_control_rec;
1746 l_price_index := 1;
1747 l_complete_qte_flag := 'Y';
1748
1749 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1750 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_prc_control_rec.price_mode:'||l_prc_control_rec.price_mode,1,'Y');
1751 END IF;
1752
1753 --Always process and send the summary line
1754
1755 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1756 aso_debug_pub.add('********************* PROCESSING HEADER STARTS *******************************',1,'Y');
1757 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_internal_call_flag: '||p_internal_call_flag,1,'Y');
1758 END IF;
1759
1760 If p_internal_call_flag = 'N' then
1761 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1762 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Set_Global_Rec - Header Level... ',1,'Y');
1763 END IF;
1764 ASO_PRICING_INT.G_HEADER_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
1765 p_qte_header_rec => p_qte_header_rec,
1766 p_shipment_rec => p_hd_shipment_rec);
1767 end If;--If p_internal_call_flag = 'N' then
1768
1769 ASO_PRICING_CORE_PVT.Print_G_Header_Rec;
1770
1771 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1772 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Build_Context - Header Level... ',1,'Y');
1773 l_pricing_end_time := dbms_utility.get_time;
1774 l_accum_aso_time := l_pricing_end_time - l_pricing_start_time;
1775 END IF;
1776
1777 QP_Price_Request_Context.Set_Request_Id;
1778
1779
1780 QP_ATTR_MAPPING_PUB.Build_Contexts (
1781 P_REQUEST_TYPE_CODE => p_control_rec.request_type,
1782 P_PRICING_TYPE_CODE => 'H',
1783 P_line_index => l_price_index,
1784 P_pricing_event => p_control_rec.pricing_event,
1785 p_check_line_flag => 'N',
1786 x_pass_line => x_pass_line);
1787
1788 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1789 l_pricing_start_time := dbms_utility.get_time;
1790 l_accum_qp_time := l_pricing_start_time - l_pricing_end_time;
1791 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: x_pass_line: '||nvl(x_pass_line,'null'),1,'Y');
1792 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Copy_Header_To_Request...',1,'Y');
1793 END IF;
1794
1795
1796 ASO_PRICING_CORE_PVT.Copy_Header_To_Request(
1797 p_Request_Type => p_control_rec.request_type,
1798 p_price_line_index => l_price_index,
1799 px_index_counter => 1);
1800
1801 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1802 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Append_asked_for...',1,'Y');
1803 END IF;
1804
1805 lx_index_counter:= 1;
1806 ASO_PRICING_CORE_PVT.Append_asked_for(
1807 p_pricing_event => p_control_rec.pricing_event,
1808 p_price_line_index => l_price_index,
1809 p_header_id => p_qte_header_rec.quote_header_id,
1810 px_index_counter => lx_index_counter);
1811
1812 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1813 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:After Append Ask for lx_index_counter:'
1814 ||lx_index_counter,1,'Y');
1815 END IF;
1816 --increment the line index
1817 l_price_index:= l_price_index+1;
1818
1819 -- Header ends here
1820
1821 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1822 aso_debug_pub.add('************************ HEADER LEVEL ENDS *******************************',1,'Y');
1823 aso_debug_pub.add(' ',1,'Y');
1824 aso_debug_pub.add('************************ LINE LEVEL BEGINS *******************************',1,'Y');
1825 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Before call Get_Order_Lines_Status:p_control_rec.pricing_event:'
1826 ||p_control_rec.pricing_event,1,'Y');
1827 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Before call Get_Order_Lines_Status:p_control_rec.request_type:'
1828 ||p_control_rec.request_type,1,'Y');
1829 l_pricing_end_time := dbms_utility.get_time;
1830 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
1831 END IF;
1832
1833 QP_UTIL_PUB.Get_Order_Lines_Status(p_event_code => p_control_rec.pricing_event,
1834 x_order_status_rec => lx_order_status_rec,
1835 p_freight_call_flag => 'Y',
1836 p_request_type_code => p_control_rec.request_type);
1837
1838 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1839 l_pricing_start_time := dbms_utility.get_time;
1840 l_accum_qp_time := l_accum_qp_time + (l_pricing_start_time - l_pricing_end_time);
1841
1842 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:lx_order_status_rec.all_lines_flag:'
1843 ||lx_order_status_rec.all_lines_flag,1,'Y');
1844 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:lx_order_status_rec.changed_lines_flag:'
1845 ||lx_order_status_rec.changed_lines_flag,1,'Y');
1846 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:lx_order_status_rec.summary_line_flag:'
1847 ||lx_order_status_rec.summary_line_flag,1,'Y');
1848 END IF;
1849
1850 If lx_order_status_rec.all_lines_flag = 'N' then
1851 l_prc_control_rec.PRG_REPRICE_MODE := 'F';
1852 else
1853 l_prc_control_rec.PRG_REPRICE_MODE := 'A';
1854 end if;
1855
1856 -- Initialize the id tables
1857 if l_qte_line_id_tbl is not NULL then
1858 l_qte_line_id_tbl.delete;
1859 end if;
1860 if l_qte_adj_id_tbl is not NULL then
1861 l_qte_adj_id_tbl.delete;
1862 end if;
1863 if l_service_qte_line_id_tbl is not NULL then
1864 l_service_qte_line_id_tbl.delete;
1865 end if;
1866
1867 --Query all lines and Serviceable lines
1868 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1869 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Pricing_Line_Rows...',1,'Y');
1870 END IF;
1871
1872 l_qte_line_tbl := ASO_UTILITY_PVT.Query_Pricing_Line_Rows(P_Qte_Header_Id => p_qte_header_rec.quote_header_id,
1873 P_change_line_flag => FND_API.G_TRUE);
1874 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1875 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After Query_Lines_lineIdlocation l_qte_line_tbl.count :'
1876 ||NVL(l_qte_line_tbl.count,0),1,'Y');
1877 l_pricing_end_time := dbms_utility.get_time;
1878 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
1879 END IF;
1880
1881 If l_qte_line_tbl.count > 0 Then
1882 --l_qte_line_tbl.count > 0
1883 --Set Check line flag from the given QP matrix
1884 --If the line is not changed and the all_lines_flag is 'Y' then only l_check_line_flag = 'Y'
1885 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1886 l_pricing_start_time := dbms_utility.get_time;
1887 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Count of passed table p_qte_line_tbl.count:'
1888 ||nvl(p_qte_line_tbl.count,0),1,'Y');
1889 END IF;
1890
1891 For i in 1..p_qte_line_tbl.count loop
1892 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1893 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Operation code:'||p_qte_line_tbl(i).operation_code,1,'Y');
1894 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:p_qte_line_tbl(i).quote_line_id:'
1895 ||p_qte_line_tbl(i).quote_line_id,1,'Y');
1896 END IF;
1897 if p_qte_line_tbl(i).operation_code <> 'DELETE' Then
1898 --changed line
1899 --assign all the Is_line_changed_flag to 'N' for the ones that are created and updated i.e. are the chg lines
1900 --Is Line Changed flag is set to 'N' because this value is what needs to be sent to BuildContext
1901 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1902 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Assign the Is_line_changed_flag to N',1,'Y');
1903 END IF;
1904 l_qte_line_tbl(p_qte_line_tbl(i).quote_line_id).Is_line_changed_flag := 'N';
1905 l_changed_qte_line_tbl(p_qte_line_tbl(i).quote_line_id) := l_qte_line_tbl(p_qte_line_tbl(i).quote_line_id);
1906
1907 -- Code added for option lines bug 8976983
1908 select nvl(item_type_code,'X') into l_item_type_code
1909 from aso_quote_lines_all
1910 where quote_line_id = p_qte_line_tbl(i).quote_line_id;
1911
1912 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1913 aso_debug_pub.add('8976983 ASO_PRICING_FLOWS_PVT:MODEL ITEM header id :'||p_qte_line_tbl(i).item_type_code,1,'Y');
1914 aso_debug_pub.add('8976983 ASO_PRICING_FLOWS_PVT:MODEL ITEM line id :'||p_qte_line_tbl(i).operation_code,1,'Y');
1915 aso_debug_pub.add('8976983 ASO_PRICING_FLOWS_PVT:l_item_type_code :'||l_item_type_code,1,'Y');
1916 END IF;
1917
1918 -- bug 13482837 rassharm
1919 if p_qte_line_tbl(i).item_type_code='SRV' and (p_qte_line_tbl(i).operation_code='CREATE' or p_qte_line_tbl(i).operation_code = 'UPDATE') then
1920 If (lx_order_status_rec.all_lines_flag = 'N') AND (lx_order_status_rec.changed_lines_flag = 'Y') then
1921 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1922 aso_debug_pub.add(' rassharm ASO_PRICING_FLOWS_PVT:service ITEM line id :'||p_qte_line_tbl(i).quote_line_id,1,'Y');
1923 end if;
1924 ls_qte_line_tbl:=ASO_UTILITY_PVT.Query_Pricing_Line_Row(P_Qte_Header_Id => p_qte_header_rec.quote_header_id, p_qte_line_id=>p_qte_line_tbl(i).quote_line_id);
1925 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1926 aso_debug_pub.add(' ASO_PRICING_FLOWS_PVT:ls_qte_line_tbl count :'||ls_qte_line_tbl.count,1,'Y');
1927 end if;
1928 for ls in 1..ls_qte_line_tbl.count loop
1929 l_qte_line_tbl(ls_qte_line_tbl(ls).quote_line_id).Is_line_changed_flag := 'N';
1930 l_changed_qte_line_tbl(ls_qte_line_tbl(ls).quote_line_id) := l_qte_line_tbl(ls_qte_line_tbl(ls).quote_line_id);
1931
1932 end loop;
1933 end if; -- all lines flag
1934 end if; -- item code ='SRV'
1935 --- end bug 13482837 rassharm
1936
1937 if (l_item_type_code='MDL') and (p_qte_line_tbl(i).operation_code = 'UPDATE') then
1938 If (lx_order_status_rec.all_lines_flag = 'N') AND (lx_order_status_rec.changed_lines_flag = 'Y') then
1939 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1940 aso_debug_pub.add('8976983 ASO_PRICING_FLOWS_PVT:MODEL ITEM header id :'||p_qte_line_tbl(i).quote_header_id,1,'Y');
1941 aso_debug_pub.add('8976983 ASO_PRICING_FLOWS_PVT:MODEL ITEM line id :'||p_qte_line_tbl(i).quote_line_id,1,'Y');
1942 END IF;
1943 for opt_cursor in (
1944 SELECT quote_line_id
1945 FROM aso_quote_line_details
1946 WHERE ref_type_code = 'CONFIG'
1947 AND top_model_line_id = p_qte_line_tbl(i).quote_line_id
1948 AND quote_line_id in (
1949 select quote_line_id from aso_quote_lines_all
1950 where quote_header_id=p_qte_line_tbl(i).quote_header_id
1951 and item_type_code in ('CFG')))
1952 loop
1953 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1954 aso_debug_pub.add('8976983 ASO_PRICING_FLOWS_PVT:MODEL ITEM:'||opt_cursor.quote_line_id,1,'Y');
1955 END IF;
1956 l_qte_line_tbl(opt_cursor.quote_line_id).Is_line_changed_flag := 'N';
1957 l_changed_qte_line_tbl(opt_cursor.quote_line_id) := l_qte_line_tbl(opt_cursor.quote_line_id);
1958 end loop;
1959 end if; -- changed flags
1960 end if; -- model item
1961 -- end Code added for option lines bug 8976983
1962 end if;
1963 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1964 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_qte_line_tbl(p_qte_line_tbl(i).quote_line_id).Is_line_changed_flag:'
1965 ||l_qte_line_tbl(p_qte_line_tbl(i).quote_line_id).Is_line_changed_flag,1,'Y');
1966 END IF;
1967 end loop;
1968
1969 --If all lines flag is 'N' and changed lines flag 'Y' then in the table just have the changed lines.
1970 If (lx_order_status_rec.all_lines_flag = 'N') AND (lx_order_status_rec.changed_lines_flag = 'Y') then
1971 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1972 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_changed_qte_line_tbl.count:'
1973 ||NVL(l_changed_qte_line_tbl.count,0),1,'Y');
1974 END IF;
1975 l_qte_line_tbl := l_changed_qte_line_tbl;
1976 End If;
1977
1978 i := l_qte_line_tbl.first;
1979 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1980 l_pricing_end_time := dbms_utility.get_time;
1981 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
1982 END IF;
1983
1984 While i is not null Loop
1985 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1986 l_pricing_start_time := dbms_utility.get_time;
1987 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:--------------Start line loop---------------',1,'Y');
1988 END IF;
1989 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1990 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl(i).quote_line_id:'
1991 ||NVL(l_qte_line_tbl(i).quote_line_id,0),1,'Y');
1992 END IF;
1993
1994 l_check_line_flag := l_qte_line_tbl(i).Is_line_changed_flag;
1995 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1996 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_check_line_flag passed to Build Context:'
1997 ||l_check_line_flag,1,'Y');
1998 END IF;
1999
2000 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2001 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Defaulting Hdr Lvl Price List and Currency..',1,'Y');
2002 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_header_rec.currency_code :'
2003 ||NVL(p_qte_header_rec.currency_code,'NULL'),1,'Y');
2004 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_header_rec.price_list_id :'
2005 ||NVL(to_char(p_qte_header_rec.price_list_id),'NULL'),1,'Y');
2006 END IF;
2007
2008 -- Pass header_level currency_code to line level by default.
2009 l_qte_line_tbl(i).currency_code := p_qte_header_rec.currency_code;
2010
2011 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2012 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl(i).price_list_id :'
2013 ||NVL(to_char(l_qte_line_tbl(i).price_list_id),'NULL'),1,'Y');
2014 END IF;
2015 -- Pass Header level price list by default.
2016 IF l_qte_line_tbl(i).pricing_line_type_indicator = 'F' then
2017 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2018 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Free line so not defaulting of the price list',1,'Y');
2019 END IF;
2020 ELSE
2021 /*Default the price list*/
2022 If l_qte_line_tbl(i).price_list_id is null Then
2023 l_qte_line_tbl(i).price_list_id := p_qte_header_rec.price_list_id;
2024 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2025 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Price list id defaulted from Header:'
2026 ||NVL(to_char(l_qte_line_tbl(i).price_list_id),'NULL'),1,'Y');
2027 END IF;
2028 End if;
2029 END IF;--l_qte_line_tbl(i).pricing_line_type_indicator = 'F'
2030
2031 l_qte_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows(l_qte_line_tbl(i).quote_line_id);
2032 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2033 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_dtl_tbl.count :'
2034 ||NVL(l_qte_line_dtl_tbl.count,0),1,'Y');
2035 END IF;
2036 IF l_qte_line_dtl_tbl.count = 1 THEN
2037 l_qte_line_dtl_rec := l_qte_line_dtl_tbl(1);
2038 ELSE
2039 l_qte_line_dtl_rec := ASO_QUOTE_PUB.G_Miss_Qte_Line_Dtl_REC ;
2040 END IF;
2041
2042 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2043 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Shipment_Rows..',1,'Y');
2044 END IF;
2045 l_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows
2046 (p_qte_header_rec.quote_header_id, l_qte_line_tbl(i).quote_line_id);
2047 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2048 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_shipment_tbl.count :'
2049 ||NVL(l_shipment_tbl.count,0),1,'Y');
2050 END IF;
2051 IF l_shipment_tbl.count = 1 THEN
2052 l_shipment_rec := l_shipment_tbl(1);
2053 else
2054 l_shipment_rec := ASO_QUOTE_PUB.G_Miss_Shipment_rec;
2055 END IF;
2056 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2057 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Set_Global_Rec - Line Level...', 1, 'Y');
2058 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:These values will be defaulted from header for pricing qualification only:',1,'Y');
2059 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_qte_line_tbl(i).invoice_to_party_site_id:'
2060 ||NVL(to_char(l_qte_line_tbl(i).invoice_to_party_site_id),'NULL'),1,'Y');
2061 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.ship_to_party_site_id:'
2062 ||NVL(to_char(l_shipment_rec.ship_to_party_site_id),'NULL'),1,'Y');
2063 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_qte_line_tbl(i).agreement_id:'
2064 ||NVL(to_char(l_qte_line_tbl(i).agreement_id),'NULL'),1,'Y');
2065 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.ship_method_code:'
2066 ||NVL(l_shipment_rec.ship_method_code,'NULL'),1,'Y');
2067 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.freight_terms_code:'
2068 ||NVL(l_shipment_rec.freight_terms_code,'NULL'),1,'Y');
2069 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.FREIGHT_CARRIER_CODE:'
2070 ||NVL(l_shipment_rec.FREIGHT_CARRIER_CODE,'NULL'),1,'Y');
2071 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.FOB_CODE:'
2072 ||NVL(l_shipment_rec.FOB_CODE,'NULL'),1,'Y');
2073 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.REQUEST_DATE:'
2074 ||NVL(to_char(l_shipment_rec.REQUEST_DATE),'NULL'),1,'Y');
2075 END IF;
2076
2077 ASO_PRICING_INT.G_LINE_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
2078 p_qte_line_rec => l_qte_line_tbl(i),
2079 p_qte_line_dtl_rec => l_qte_line_dtl_rec,
2080 p_shipment_rec => l_shipment_rec);
2081
2082 ASO_PRICING_CORE_PVT.Print_G_Line_Rec;
2083
2084 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2085 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of QP Build_Contexts - Line Level...',1,'Y');
2086 l_pricing_end_time := dbms_utility.get_time;
2087 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
2088 END IF;
2089
2090 QP_ATTR_MAPPING_PUB.Build_Contexts (
2091 p_request_type_code => p_control_rec.request_type,
2092 p_line_index => l_price_index,
2093 p_check_line_flag => l_check_line_flag,
2094 p_pricing_event => p_control_rec.pricing_event,
2095 p_pricing_type_code => 'L',
2096 p_price_list_validated_flag => 'N',
2097 x_pass_line => x_pass_line);
2098
2099 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2100 l_pricing_start_time := dbms_utility.get_time;
2101 l_accum_qp_time := l_accum_qp_time + (l_pricing_start_time - l_pricing_end_time);
2102 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: x_pass_line: '||nvl(x_pass_line,'null'),1,'Y');
2103 END IF;
2104
2105 --Note: If the line has been changed, we have to send this line to pricing engine
2106 --Also, if the line has not been changed but x_pass_line is 'Y' then we have to send this line to PE
2107 If (x_pass_line = 'Y' AND l_check_line_flag = 'Y') OR (l_check_line_flag = 'N') then
2108 --Store this in the processed id table and put it in the PE structure
2109 /*Store all the quote_line_id processed*/
2110 IF l_Qte_Line_id_tbl.EXISTS(1) THEN
2111 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2112 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of l_qte_line_id_tbl.extend...',1,'Y');
2113 END IF;
2114 l_Qte_Line_id_tbl.extend;
2115 l_Qte_Line_id_tbl(l_Qte_Line_id_tbl.count) := l_qte_line_tbl(i).quote_line_id;
2116 ELSE
2117 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2118 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: First quote_line_id in l_qte_line_id_tbl',1,'Y');
2119 END IF;
2120 l_Qte_Line_id_tbl := JTF_NUMBER_TABLE(l_qte_line_tbl(i).quote_line_id);
2121 END IF;
2122
2123 --Store all the service lines processed in the service line id table
2124 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2125 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl(i).service_item_flag: '
2126 ||l_qte_line_tbl(i).service_item_flag,1,'Y');
2127 END IF;
2128 If l_qte_line_tbl(i).service_item_flag = 'Y' then
2129 if l_service_qte_line_id_tbl.EXISTS(1) then
2130 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2131 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of l_service_qte_line_id_tbl.extend...',1,'Y');
2132 END IF;
2133 l_service_qte_line_id_tbl.extend;
2134 l_service_qte_line_id_tbl(l_service_qte_line_id_tbl.count) := l_qte_line_tbl(i).quote_line_id;
2135 else
2136 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2137 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: First quote_line_id in l_service_qte_line_id_tbl',1,'Y');
2138 END IF;
2139 l_service_qte_line_id_tbl:= JTF_NUMBER_TABLE(l_qte_line_tbl(i).quote_line_id);
2140 end if;
2141 end if;-- l_qte_line_tbl(i).service_item_flag = 'Y'
2142
2143 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2144 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Copy_Line_To_Request...',1,'Y');
2145 END IF;
2146 ASO_PRICING_CORE_PVT.Copy_Line_To_Request(
2147 p_Request_Type => p_control_rec.request_type,
2148 p_price_line_index => l_price_index,
2149 px_index_counter => l_price_index);
2150
2151 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2152 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Append_asked_for...',1,'Y');
2153 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Before Append Ask for lx_index_counter:'
2154 ||lx_index_counter,1,'Y');
2155 END IF;
2156 l_index_counter := lx_index_counter;
2157 ASO_PRICING_CORE_PVT.Append_asked_for(
2158 p_pricing_event => p_control_rec.pricing_event,
2159 p_price_line_index => l_price_index,
2160 p_header_id => p_qte_header_rec.quote_header_id,
2161 p_line_id => l_qte_line_tbl(i).quote_line_id,
2162 px_index_counter => lx_index_counter);
2163 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2164 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:After Append Ask for lx_index_counter:'
2165 ||lx_index_counter,1,'Y');
2166 END IF;
2167 If lx_index_counter = 0 then
2168 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2169 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Assigning l_index_counter back to lx_index_counter:'
2170 ||l_index_counter,1,'Y');
2171 END IF;
2172 lx_index_counter := l_index_counter;
2173 end if;
2174
2175 px_line_index_search_tbl(l_qte_line_tbl(i).quote_line_id) := l_price_index;--Used for modify globals
2176 --end of building one line
2177 i:= l_qte_line_tbl.next(i);
2178 l_price_index:= l_price_index+1;
2179 Else
2180 -- x_pass_line = 'N' OR l_check_line_flag = 'Y'
2181 l_complete_qte_flag := 'N';
2182 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2183 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:This line is not passed to PE:'
2184 ||l_qte_line_tbl(i).quote_line_id,1,'Y');
2185 END IF;
2186 i:= l_qte_line_tbl.next(i);
2187 end If;--x_pass_line = 'Y' AND l_check_line_flag = 'Y'
2188 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2189 l_pricing_end_time := dbms_utility.get_time;
2190 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
2191 END IF;
2192
2193 End Loop;--End of While loop
2194
2195 End If; --l_qte_line_tbl.count > 0
2196
2197
2198 --- Need to track the changed lines counter.....
2199 If (lx_order_status_rec.all_lines_flag = 'N') AND (lx_order_status_rec.changed_lines_flag = 'Y') then
2200 --If l_price_index = 2 then there were no changed lines as all the records
2201 --in p_qte_line_tbl were deletes.
2202 --Check no of lines in the db and the (l_price_index - 2), if <> then l_complete_qte_flag is 'N'
2203
2204 SELECT count(rowid)
2205 INTO l_db_ln_counter
2206 FROM ASO_QUOTE_LINES_ALL
2207 WHERE quote_header_id = p_qte_header_rec.quote_header_id;
2208 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2209 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_db_ln_counter:'||l_db_ln_counter,1,'Y');
2210 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_price_index:'||l_price_index,1,'Y');
2211 END IF;
2212 If (l_db_ln_counter <> (l_price_index - 2)) Then
2213 l_complete_qte_flag := 'N';
2214 end if;
2215 elsif (lx_order_status_rec.all_lines_flag = 'N') AND (lx_order_status_rec.changed_lines_flag = 'N') then
2216 --Changed Lines flag = 'N'
2217 --then pass only header to Price_Request
2218 --Since we are only sending the summary line to pricing the quote is not completely priced
2219 l_complete_qte_flag := 'N';
2220
2221 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2222 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:All line flag is N and changed line flag is N',1,'Y');
2223 END IF;
2224
2225 End If;
2226
2227 -- Call to Price Adjustments, Adjustments Relationships, and Service Relationships
2228 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2229 l_pricing_start_time := dbms_utility.get_time;
2230 END IF;
2231 IF l_Qte_Line_id_tbl.EXISTS(1) THEN
2232 -- Call to Price Adjustment Relationships and Service Relationships
2233 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2234 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Price_Adjustments...',1,'Y');
2235 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_id_tbl.count :'
2236 ||NVL(l_qte_line_id_tbl.count,0),1,'Y');
2237 END IF;
2238 ASO_PRICING_CORE_PVT.Query_Price_Adjustments
2239 (p_quote_header_id => p_qte_header_rec.quote_header_id,
2240 p_qte_line_id_tbl => l_qte_line_id_tbl,
2241 x_adj_id_tbl => l_qte_adj_id_tbl);
2242
2243 If l_qte_adj_id_tbl.exists(1) OR l_service_qte_line_id_tbl.exists(1) Then
2244 -- Adjustment and line Relationships and services...
2245 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2246 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Adj_rltship_Bulk...',1,'Y');
2247 If l_qte_adj_id_tbl is not null then
2248 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_adj_id_tbl.count :'
2249 ||NVL(l_qte_adj_id_tbl.count,0),1,'Y');
2250 end if;
2251 If l_service_qte_line_id_tbl is not null then
2252 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_service_qte_line_id_tbl.count :'
2253 ||NVL(l_service_qte_line_id_tbl.count,0),1,'Y');
2254 end if;
2255 END IF;
2256 ASO_PRICING_CORE_PVT.Query_relationships
2257 (p_qte_adj_id_tbl => l_qte_adj_id_tbl,
2258 p_service_qte_line_id_tbl => l_service_qte_line_id_tbl);
2259 else
2260 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2261 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: There are no service lines or manual related Adjustments',1,'Y');
2262 END IF;
2263 end if;--If l_qte_adj_id_tbl.exists(1) OR l_service_qte_line_id_tbl.exists(1)
2264 ELSIF (lx_order_status_rec.summary_line_flag = 'Y') THEN
2265 --Just send the header level manual adjustments
2266 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2267 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Price_Adj_Header...',1,'Y');
2268 END IF;
2269 ASO_PRICING_CORE_PVT.Query_Price_Adj_Header
2270 (p_quote_header_id => p_qte_header_rec.quote_header_id);
2271
2272 END IF; -- l_Qte_Line_id_tbl.EXISTS(1) check.
2273
2274 -- Start of Deleting of attributes logic
2275
2276 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2277 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin bulk collect adj ids that are not passed to PE', 1, 'Y');
2278 END IF;
2279
2280 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2281 If l_qte_adj_id_tbl is not null then
2282 For i in 1..l_qte_adj_id_tbl.count loop
2283 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Querying for the following adjustment ids:',1,'Y');
2284 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_qte_adj_id_tbl(i):'||l_qte_adj_id_tbl(i),1,'Y');
2285 end loop;
2286 end if;
2287 END IF;
2288
2289 If l_qte_adj_id_tbl.EXISTS(1) then
2290 SELECT adj.PRICE_ADJUSTMENT_ID
2291 BULK COLLECT INTO
2292 l_adj_id_tbl
2293 FROM ASO_PRICE_ADJUSTMENTS adj
2294 WHERE adj.quote_header_id = p_qte_header_rec.quote_header_id
2295 AND adj.price_adjustment_id NOT IN (SELECT column_value
2296 FROM TABLE (CAST(l_qte_adj_id_tbl AS JTF_NUMBER_TABLE)) passed_adj);
2297
2298 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2299 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: No. of adjustment lines selected is sql%rowcount(Both hdr and lines): '
2300 ||sql%rowcount,1,'Y');
2301 END IF;
2302 Elsif (lx_order_status_rec.summary_line_flag = 'Y') Then
2303 SELECT adj.PRICE_ADJUSTMENT_ID
2304 BULK COLLECT INTO
2305 l_adj_id_tbl
2306 FROM ASO_PRICE_ADJUSTMENTS adj
2307 WHERE adj.quote_header_id = p_qte_header_rec.quote_header_id
2308 AND adj.quote_line_id IS NULL;
2309 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2310 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: No. of adjustment lines selected is sql%rowcount(Header): '
2311 ||sql%rowcount,1,'Y');
2312 END IF;
2313 END If;--l_qte_adj_id_tbl.EXISTS(1)
2314
2315 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2316
2317 if l_adj_id_tbl.count > 0 then
2318
2319 for k in l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST loop
2320 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_adj_id_tbl('||k||'): ' || l_adj_id_tbl(k),1,'Y');
2321 end loop;
2322
2323 end if;
2324
2325 END IF;
2326
2327 if l_adj_id_tbl.count > 0 then
2328 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2329 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin DELETE FROM ASO_PRICE_ADJ_ATTRIBS', 1, 'Y');
2330 END IF;
2331 FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
2332 DELETE FROM ASO_PRICE_ADJ_ATTRIBS
2333 WHERE PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i);
2334
2335 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2336 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:No of adjustment attribute lines deleted is sql%rowcount: '
2337 ||sql%rowcount,1,'Y');
2338 END IF;
2339
2340 end if;
2341
2342 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2343 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_complete_qte_flag before delete:'||l_complete_qte_flag,1,'Y');
2344 END IF;
2345
2346 -- End Deleting of attributes
2347
2348 if l_complete_qte_flag = 'Y' then
2349 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2350 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin DELETE FROM ASO_PRICE_ADJ_RELATIONSHIPS', 1, 'Y');
2351 END IF;
2352 DELETE FROM ASO_PRICE_ADJ_RELATIONSHIPS
2353 WHERE QUOTE_LINE_ID in (SELECT quote_line_id
2354 FROM ASO_PRICE_ADJUSTMENTS
2355 WHERE quote_header_id = p_qte_header_rec.quote_header_id
2356 AND quote_line_id IS NOT NULL);
2357
2358 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2359 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Rltd adj Lines deleted '||sql%ROWCOUNT,1,'Y');
2360 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin DELETE FROM ASO_PRICE_ADJUSTMENTS', 1, 'Y');
2361 END IF;
2362
2363 DELETE FROM ASO_PRICE_ADJUSTMENTS
2364 WHERE quote_header_id = p_qte_header_rec.quote_header_id;
2365 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2366 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Adjustment Lines deleted '||sql%ROWCOUNT,1,'Y');
2367 END IF;
2368
2369 else
2370 --l_complete_qte_flag is 'N'
2371
2372 IF l_Qte_Line_id_tbl.EXISTS(1) THEN
2373 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2374 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin DELETE FROM ASO_PRICE_ADJ_RELATIONSHIPS', 1, 'Y');
2375 For i in 1..l_qte_line_id_tbl.count loop
2376 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin DELETE for l_qte_line_id_tbl(i):'
2377 ||l_qte_line_id_tbl(i), 1, 'Y');
2378 end loop;
2379 END IF;
2380
2381 DELETE FROM ASO_PRICE_ADJ_RELATIONSHIPS
2382 WHERE quote_line_id IN (SELECT column_value
2383 FROM TABLE (CAST(l_qte_line_id_tbl AS JTF_NUMBER_TABLE)));
2384
2385 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2386 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Rltd adj Lines deleted '||sql%ROWCOUNT,1,'Y');
2387 END IF;
2388
2389 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2390 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Begin DELETE FROM ASO_PRICE_ADJUSTMENTS', 1, 'Y');
2391 END IF;
2392 DELETE FROM ASO_PRICE_ADJUSTMENTS
2393 WHERE quote_line_id IN (SELECT column_value
2394 FROM TABLE (CAST(l_qte_line_id_tbl AS JTF_NUMBER_TABLE)))
2395 AND quote_header_id = p_qte_header_rec.quote_header_id;
2396 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2397 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Adjustment Lines deleted '||sql%ROWCOUNT,1,'Y');
2398 END IF;
2399
2400 DELETE FROM ASO_PRICE_ADJUSTMENTS
2401 WHERE quote_header_id = p_qte_header_rec.quote_header_id
2402 AND quote_line_id is NULL;
2403
2404 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2405 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Header Adjustment deleted '||sql%ROWCOUNT,1,'Y');
2406 END IF;
2407 ELSIF (lx_order_status_rec.summary_line_flag = 'Y') Then
2408 DELETE FROM ASO_PRICE_ADJUSTMENTS
2409 WHERE quote_header_id = p_qte_header_rec.quote_header_id
2410 AND quote_line_id is NULL;
2411
2412 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2413 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Header Adjustment deleted '||sql%ROWCOUNT,1,'Y');
2414 END IF;
2415
2416 END IF;--l_Qte_Line_id_tbl.EXISTS(1)
2417
2418
2419 end if;--l_complete_qte_flag = 'Y'
2420
2421
2422 If (l_Qte_Line_id_tbl.EXISTS(1))
2423 OR (p_control_rec.pricing_event = 'ORDER')
2424 OR (lx_order_status_rec.summary_line_flag = 'Y') then
2425 --Condition was placed because engine call was not necessary if the event is price and there are no lines
2426
2427 --Need to modify the global index table of pls integer types
2428 l_global_pls_tbl := ASO_PRICING_CORE_PVT.G_LDET_LINE_INDEX_TBL;
2429 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2430 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Modifying G_LDET_LINE_INDEX_TBL',1,'Y');
2431 END IF;
2432 ASO_PRICING_CORE_PVT.G_LDET_LINE_INDEX_TBL:= ASO_PRICING_CORE_PVT.Modify_Global_PlsIndex_Table(
2433 p_global_tbl => l_global_pls_tbl,
2434 p_search_tbl => px_line_index_search_tbl);
2435 l_global_num_tbl := ASO_PRICING_CORE_PVT.G_RLTD_LINE_INDEX_TBL;
2436 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2437 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Modifying G_RLTD_LINE_INDEX_TBL',1,'Y');
2438 END IF;
2439 ASO_PRICING_CORE_PVT.G_RLTD_LINE_INDEX_TBL:= ASO_PRICING_CORE_PVT.Modify_Global_NumIndex_Table(
2440 p_global_tbl => l_global_num_tbl,
2441 p_search_tbl => px_line_index_search_tbl);
2442 l_global_num_tbl := ASO_PRICING_CORE_PVT.G_RLTD_RELATED_LINE_IND_TBL;
2443 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2444 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Modifying G_RLTD_RELATED_LINE_IND_TBL',1,'Y');
2445 END IF;
2446 ASO_PRICING_CORE_PVT.G_RLTD_RELATED_LINE_IND_TBL:= ASO_PRICING_CORE_PVT.Modify_Global_NumIndex_Table(
2447 p_global_tbl => l_global_num_tbl,
2448 p_search_tbl => px_line_index_search_tbl);
2449
2450 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2451 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Populate_QP_Temp_tables...',1,'Y');
2452 l_pricing_end_time := dbms_utility.get_time;
2453 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
2454 END IF;
2455
2456 ASO_PRICING_CORE_PVT.populate_qp_temp_tables;
2457
2458 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2459 l_pricing_start_time := dbms_utility.get_time;
2460 l_accum_qp_time := l_accum_qp_time + (l_pricing_start_time - l_pricing_end_time);
2461 END IF;
2462 -- Set the control rec for QP
2463
2464 l_req_control_rec.pricing_event := p_control_rec.pricing_event;
2465 l_req_control_rec.calculate_flag := p_control_rec.calculate_flag;
2466 l_req_control_rec.simulation_flag := p_control_rec.simulation_flag;
2467 l_req_control_rec.TEMP_TABLE_INSERT_FLAG := 'N'; ---- Modified
2468 l_req_control_rec.source_order_amount_flag := 'Y';
2469 l_req_control_rec.GSA_CHECK_FLAG := 'Y';
2470 l_req_control_rec.GSA_DUP_CHECK_FLAG := 'Y';
2471 l_req_control_rec.REQUEST_TYPE_CODE := p_control_rec.request_type;
2472 l_req_control_rec.rounding_flag := 'Q';
2473 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2474 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_req_control_rec.calculate_flag:'||l_req_control_rec.calculate_flag,1,'Y');
2475 END IF;
2476
2477 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2478 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of QP_PREQ_PUB.PRICE_REQUEST', 1, 'Y');
2479 l_pricing_end_time := dbms_utility.get_time;
2480 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
2481 END IF;
2482
2483 /* Change for populating QP_PREQ_GRP.CONTROL_RECORD_TYPE.ORG_ID Yogeshwar (MOAC) */
2484
2485 l_req_control_rec.ORG_ID := p_qte_header_rec.org_id;
2486
2487 /* End of Change (MOAC) */
2488
2489
2490 QP_PREQ_PUB.PRICE_REQUEST
2491 (p_control_rec =>l_req_control_rec
2492 ,x_return_status =>l_return_status
2493 ,x_return_status_Text =>l_return_status_Text
2494 );
2495 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2496 x_return_status := l_return_status;
2497 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2498 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR');
2499 FND_MESSAGE.Set_Token('MSG_TXT', substr(l_return_status_text, 1,255), FALSE);
2500 FND_MSG_PUB.ADD;
2501 END IF;
2502 RAISE FND_API.G_EXC_ERROR;
2503 END IF;
2504
2505 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2506 l_pricing_start_time := dbms_utility.get_time;
2507 l_accum_qp_time := l_accum_qp_time + (l_pricing_start_time - l_pricing_end_time);
2508 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: End of QP_PREQ_PUB.PRICE_REQUEST', 1, 'Y');
2509 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After PRICE_REQUEST l_return_status:'
2510 ||l_return_status, 1, 'Y');
2511 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After PRICE_REQUEST l_return_status_text '
2512 ||l_return_status_text,1,'Y');
2513 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Post_Price_Request...',1,'Y');
2514 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_complete_qte_flag:'||l_complete_qte_flag,1,'Y');
2515 END IF;
2516 /*Insert all the applied adjustments and the nonapplied manual adjustments*/
2517 ASO_PRICING_CORE_PVT.Copy_Price_To_Quote(
2518 P_Api_Version_Number => 1.0,
2519 P_Init_Msg_List => FND_API.G_FALSE,
2520 P_Commit => FND_API.G_FALSE,
2521 p_control_rec => l_prc_control_rec,
2522 p_qte_header_rec => p_qte_header_rec,
2523 P_Insert_Type => 'HDR',
2524 x_return_status => x_return_status,
2525 x_msg_count => x_msg_count,
2526 x_msg_data => x_msg_data);
2527 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2528 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Copy_Price_To_Quote : x_return_status: '
2529 ||nvl(x_return_status,'NULL'),1,'Y');
2530 END IF;
2531
2532 If p_internal_call_flag = 'N' then
2533 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2534 ASO_PRICING_CORE_PVT.Process_PRG(
2535 P_Api_Version_Number => 1.0,
2536 P_Init_Msg_List => FND_API.G_FALSE,
2537 P_Commit => FND_API.G_FALSE,
2538 p_control_rec => l_prc_control_rec,
2539 p_qte_header_rec => p_qte_header_rec,
2540 x_qte_line_tbl => x_qte_line_tbl,
2541 x_return_status => x_return_status,
2542 x_msg_count => x_msg_count,
2543 x_msg_data => x_msg_data);
2544
2545 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2546 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Process_PRG : x_return_status: '
2547 ||nvl(x_return_status,'NULL'),1,'Y');
2548 END IF;
2549
2550 END IF;--x_return_status = FND_API.G_RET_STS_SUCCESS
2551
2552 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2553 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2554 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After Process_PRG : x_qte_line_tbl.count: '
2555 ||x_qte_line_tbl.count,1,'Y');
2556 END IF;
2557
2558 ASO_PRICING_CORE_PVT.Process_Charges(
2559 P_Api_Version_Number => 1.0,
2560 P_Init_Msg_List => FND_API.G_FALSE,
2561 P_Commit => FND_API.G_FALSE,
2562 p_control_rec => l_prc_control_rec,
2563 p_qte_header_rec => p_qte_header_rec,
2564 x_return_status => x_return_status,
2565 x_msg_count => x_msg_count,
2566 x_msg_data => x_msg_data);
2567 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2568 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Process_Charges : x_return_status: '
2569 ||nvl(x_return_status,'NULL'),1,'Y');
2570 END IF;
2571
2572 END IF;--x_return_status = FND_API.G_RET_STS_SUCCESS
2573 End If;--p_internal_call_flag = 'N'??????????Is this okay????
2574
2575 -- After the code review according to SPGOPAL if the summary line flag is 'N' then we don't need to make
2576 -- the calculate flag. Also we cannot compare the percentage of the header level since in future if there
2577 -- is a bucketing implementation for the header level modifier ASO does not have a way to compare the percentages.
2578 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2579 l_pricing_end_time := dbms_utility.get_time;
2580 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
2581 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:**********Timing Before Calculate Call****************',1,'Y');
2582
2583 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Total time taken (in secs) in Price Entire Quote(Besides Pricing):'||l_accum_aso_time/100,1,'Y');
2584 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Total time taken (in secs) in Pricing+BuildContext:'||l_accum_qp_time/100,1,'Y');
2585 END IF;
2586
2587 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2588 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2589 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Before Calling Calculate Call l_complete_qte_flag:'
2590 ||l_complete_qte_flag,1,'Y');
2591 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Before Calling Calculate Call lx_order_status_rec.summary_line_flag:'
2592 ||lx_order_status_rec.summary_line_flag,1,'Y');
2593 END IF;
2594 If (l_complete_qte_flag = 'N') then
2595 If (lx_order_status_rec.summary_line_flag <> 'N') then
2596 --If the l_processed_flag value is 'C' reprice the whole quote with all the lines with calculate only option
2597 --Since the processed flag is not coded for Order Capture, Current work around is such that Order Capture should
2598 --always make the second call except in the above two conditions of complete qte flag and summary line flag.
2599
2600 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2601 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Price Request should be called again to get header level adj',1,'Y');
2602 END IF;
2603
2604 l_prc_control_rec.calculate_flag := QP_PREQ_GRP.G_CALCULATE_ONLY;
2605
2606 --Call Calculate only call
2607
2608 Price_Quote_Calculate_Call(
2609 P_Api_Version_Number => P_Api_Version_Number,
2610 P_Init_Msg_List => FND_API.G_FALSE,
2611 P_Commit => FND_API.G_FALSE,
2612 p_control_rec => l_prc_control_rec,
2613 p_qte_header_rec => p_qte_header_rec,
2614 p_delta_line_id_tbl => l_qte_line_id_tbl,
2615 x_qte_line_tbl => lx_Qte_Line_Tbl,
2616 x_return_status => x_return_status,
2617 x_msg_count => x_msg_count,
2618 x_msg_data => x_msg_data);
2619
2620 End If; -- If (lx_order_status_rec.summary_line_flag <> 'N')
2621
2622 End If;-- If l_complete_qte_flag = 'N'
2623
2624
2625 END IF;-- x_return_status = FND_API.G_RET_STS_SUCCESS
2626
2627
2628 --End If;--p_internal_call_flag = 'N'
2629 End if;-- (l_Qte_Line_id_tbl.EXISTS(1)) OR (p_control_rec.pricing_event = 'ORDER')
2630
2631
2632 FND_MSG_PUB.Count_And_Get
2633 ( p_encoded => 'F',
2634 p_count => x_msg_count,
2635 p_data => x_msg_data
2636 );
2637
2638 FOR l IN 1 .. x_msg_count LOOP
2639 x_msg_data := FND_MSG_PUB.GET( p_msg_index => l, p_encoded => 'F');
2640 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2641 aso_debug_pub.add('Messge count and get '||x_msg_data, 1, 'Y');
2642 aso_debug_pub.add('Messge count and get '||x_msg_count, 1, 'Y');
2643 END IF;
2644 END LOOP;
2645
2646 END Price_Quote_With_Change_Lines;
2647
2648 PROCEDURE Price_Quote_Calculate_Call(
2649 P_Api_Version_Number IN NUMBER,
2650 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2651 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2652 p_control_rec IN ASO_PRICING_INT.PRICING_CONTROL_REC_TYPE,
2653 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
2654 p_delta_line_id_tbl IN JTF_NUMBER_TABLE,
2655 x_qte_line_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
2656 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2657 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2658 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
2659 IS
2660 l_api_name CONSTANT VARCHAR2(30) := 'Price_Quote_Calculate_Call';
2661 l_api_version_number CONSTANT NUMBER := 1.0;
2662 l_price_index NUMBER;
2663 l_req_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
2664 l_prc_control_rec ASO_PRICING_INT.PRICING_CONTROL_REC_TYPE;
2665 x_pass_line VARCHAR2(10);
2666 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
2667 l_qte_line_id NUMBER;
2668 l_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type;
2669 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
2670 l_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
2671 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
2672 lx_index_counter Number;
2673 l_index_counter Number;
2674 l_return_status_text VARCHAR2(2000);
2675 l_message_text VARCHAR2(2000);
2676 l_return_status VARCHAR2(1);
2677
2678 l_service_qte_line_id_tbl JTF_NUMBER_TABLE;
2679 l_qte_line_id_tbl JTF_NUMBER_TABLE;
2680 l_qte_adj_id_tbl JTF_NUMBER_TABLE;
2681 px_line_index_search_tbl ASO_PRICING_CORE_PVT.Index_Link_Tbl_Type;
2682 l_global_pls_tbl QP_PREQ_GRP.pls_integer_type;
2683 l_global_num_tbl QP_PREQ_GRP.NUMBER_TYPE;
2684
2685 l_pricing_start_time NUMBER;
2686 l_pricing_end_time NUMBER;
2687 l_accum_aso_time NUMBER;
2688 l_accum_qp_time NUMBER;
2689
2690 BEGIN
2691 -- Standard call to check for call compatibility.
2692 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2693 p_api_version_number,
2694 l_api_name,
2695 G_PKG_NAME)
2696 THEN
2697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2698 END IF;
2699
2700 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2701 l_pricing_start_time := dbms_utility.get_time;
2702 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Start of Price_Quote_Calculate_Call',1,'Y');
2703 END IF;
2704
2705 x_return_status := FND_API.G_RET_STS_SUCCESS;
2706
2707 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2708 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:p_control_rec.request_type:'||p_control_rec.request_type,1,'Y');
2709 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:p_control_rec.pricing_event:'||p_control_rec.pricing_event,1,'Y');
2710 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:p_control_rec.calculate_flag:'||p_control_rec.calculate_flag,1,'Y');
2711 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:p_qte_header_rec.quote_header_id:'||p_qte_header_rec.quote_header_id,1,'Y');
2712 IF p_delta_line_id_tbl is not null THEN
2713 for i in 1..p_delta_line_id_tbl.count loop
2714 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:p_delta_line_id_tbl(i):'||p_delta_line_id_tbl(i), 1, 'Y');
2715 end loop;
2716 END IF;
2717 END IF;
2718 l_prc_control_rec := p_control_rec;
2719 l_price_index := 1;
2720
2721 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2722 aso_debug_pub.add('********************* PROCESSING HEADER STARTS *******************************',1,'Y');
2723 END IF;
2724 ASO_PRICING_CORE_PVT.Print_G_Header_Rec;
2725 /*Initialize the global tables*/
2726 ASO_PRICING_CORE_PVT.Initialize_Global_Tables;
2727
2728 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2729 l_pricing_end_time := dbms_utility.get_time;
2730 l_accum_aso_time := l_pricing_end_time - l_pricing_start_time;
2731 END IF;
2732
2733 QP_Price_Request_Context.Set_Request_Id;
2734
2735 QP_ATTR_MAPPING_PUB.Build_Contexts (
2736 P_REQUEST_TYPE_CODE => p_control_rec.request_type,
2737 P_PRICING_TYPE_CODE => 'H',
2738 P_line_index => l_price_index,
2739 P_pricing_event => p_control_rec.pricing_event,
2740 p_check_line_flag => 'N',
2741 x_pass_line => x_pass_line);
2742
2743 l_price_index := 1;
2744 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2745 l_pricing_start_time := dbms_utility.get_time;
2746 l_accum_qp_time := l_pricing_start_time - l_pricing_end_time;
2747 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: x_pass_line: '||nvl(x_pass_line,'null'),1,'Y');
2748 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Copy_Header_To_Request...',1,'Y');
2749 END IF;
2750 ASO_PRICING_CORE_PVT.Copy_Header_To_Request(
2751 p_Request_Type => p_control_rec.request_type,
2752 p_price_line_index => l_price_index,
2753 px_index_counter => 1);
2754
2755
2756 -- Do we need to do append ask for in the calculate only call????
2757 --increment the line index
2758 l_price_index:= l_price_index+1;
2759
2760 -- Header ends here
2761
2762 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2763 aso_debug_pub.add('************************ HEADER LEVEL ENDS *******************************',1,'Y');
2764 aso_debug_pub.add(' ',1,'Y');
2765 aso_debug_pub.add('************************ LINE LEVEL BEGINS *******************************',1,'Y');
2766 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of ASO UTL PVT Query_Pricing_Line_Rows...',1,'Y');
2767 END IF;
2768
2769 l_qte_line_tbl := ASO_UTILITY_PVT.Query_Pricing_Line_Rows(p_qte_header_rec.quote_header_id);
2770 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2771 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl.count :'||NVL(l_qte_line_tbl.count,0),1,'Y');
2772 l_pricing_end_time := dbms_utility.get_time;
2773 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
2774 END IF;
2775
2776 If l_qte_line_tbl.count = 0 Then
2777 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2778 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:No lines in the database',1,'Y');
2779 END IF;
2780
2781 Else
2782 -- Line Count <> 0.
2783 For i in 1..l_qte_line_tbl.count Loop
2784
2785 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2786 l_pricing_start_time := dbms_utility.get_time;
2787 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Line_Dtl_Rows...',1,'Y');
2788 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl(i).quote_line_id:'
2789 ||NVL(l_qte_line_tbl(i).quote_line_id,0),1,'Y');
2790 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl(i).pricing_line_type_indicator:'
2791 ||l_qte_line_tbl(i).pricing_line_type_indicator,1,'Y');
2792 END IF;
2793
2794 l_qte_line_id := l_qte_line_tbl(i).quote_line_id;
2795 l_qte_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows(l_qte_line_id);
2796
2797 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2798 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Defaulting Hdr Lvl Price List and Currency..',1,'Y');
2799 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_header_rec.currency_code :'
2800 ||NVL(p_qte_header_rec.currency_code,'NULL'),1,'Y');
2801 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: p_qte_header_rec.price_list_id :'
2802 ||NVL(to_char(p_qte_header_rec.price_list_id),'NULL'),1,'Y');
2803 END IF;
2804 -- Pass header_level currency_code to line level by default.
2805 l_qte_line_tbl(i).currency_code := p_qte_header_rec.currency_code;
2806
2807 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2808 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_tbl(i).price_list_id :'
2809 ||NVL(to_char(l_qte_line_tbl(i).price_list_id),'NULL'),1,'Y');
2810 END IF;
2811 -- Pass Header level price list by default.
2812 IF l_qte_line_tbl(i).pricing_line_type_indicator = 'F' then
2813 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2814 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Free line so not defaulting of the price list',1,'Y');
2815 END IF;
2816 ELSE
2817 /*Default the price list*/
2818 If l_qte_line_tbl(i).price_list_id is null Then
2819 l_qte_line_tbl(i).price_list_id := p_qte_header_rec.price_list_id;
2820 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2821 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Price list id defaulted from Header:'
2822 ||NVL(to_char(l_qte_line_tbl(i).price_list_id),'NULL'),1,'Y');
2823 END IF;
2824 End if;
2825 END IF;--l_qte_line_tbl(i).pricing_line_type_indicator = 'F'
2826
2827 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2828 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_qte_line_dtl_tbl.count :'
2829 ||NVL(l_qte_line_dtl_tbl.count,0),1,'Y');
2830 END IF;
2831 IF l_qte_line_dtl_tbl.count = 1 THEN
2832 l_qte_line_dtl_rec := l_qte_line_dtl_tbl(1);
2833 ELSE
2834 l_qte_line_dtl_rec := ASO_QUOTE_PUB.G_Miss_Qte_Line_Dtl_REC ;
2835 END IF;
2836
2837 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2838 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Shipment_Rows..',1,'Y');
2839 END IF;
2840 l_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows
2841 (p_qte_header_rec.quote_header_id, l_QTE_LINE_ID);
2842 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2843 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: l_shipment_tbl.count :'
2844 ||NVL(l_shipment_tbl.count,0),1,'Y');
2845 END IF;
2846 IF l_shipment_tbl.count = 1 THEN
2847 l_shipment_rec := l_shipment_tbl(1);
2848 else
2849 l_shipment_rec := ASO_QUOTE_PUB.G_Miss_Shipment_rec;
2850 END IF;
2851
2852
2853 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2854 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Set_Global_Rec - Line Level...', 1, 'Y');
2855 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:These values will be defaulted from header for pricing qualification only:',1,'Y');
2856 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_qte_line_tbl(i).invoice_to_party_site_id:'
2857 ||NVL(to_char(l_qte_line_tbl(i).invoice_to_party_site_id),'NULL'),1,'Y');
2858 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.ship_to_party_site_id:'
2859 ||NVL(to_char(l_shipment_rec.ship_to_party_site_id),'NULL'),1,'Y');
2860 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_qte_line_tbl(i).agreement_id:'
2861 ||NVL(to_char(l_qte_line_tbl(i).agreement_id),'NULL'),1,'Y');
2862 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.ship_method_code:'
2863 ||NVL(l_shipment_rec.ship_method_code,'NULL'),1,'Y');
2864 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.freight_terms_code:'
2865 ||NVL(l_shipment_rec.freight_terms_code,'NULL'),1,'Y');
2866 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.FREIGHT_CARRIER_CODE:'
2867 ||NVL(l_shipment_rec.FREIGHT_CARRIER_CODE,'NULL'),1,'Y');
2868 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.FOB_CODE:'
2869 ||NVL(l_shipment_rec.FOB_CODE,'NULL'),1,'Y');
2870 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:l_shipment_rec.REQUEST_DATE:'
2871 ||NVL(to_char(l_shipment_rec.REQUEST_DATE),'NULL'),1,'Y');
2872 END IF;
2873
2874 ASO_PRICING_INT.G_LINE_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
2875 p_qte_line_rec => l_qte_line_tbl(i),
2876 p_qte_line_dtl_rec => l_qte_line_dtl_rec,
2877 p_shipment_rec => l_shipment_rec);
2878
2879 ASO_PRICING_CORE_PVT.Print_G_Line_Rec;
2880
2881 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2882 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of QP Build_Contexts - Line Level...',1,'Y');
2883 l_pricing_end_time := dbms_utility.get_time;
2884 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
2885 END IF;
2886
2887 QP_ATTR_MAPPING_PUB.Build_Contexts (
2888 p_request_type_code => p_control_rec.request_type,
2889 p_line_index => l_price_index,
2890 p_check_line_flag => 'N',
2891 p_pricing_event => p_control_rec.pricing_event,
2892 p_pricing_type_code => 'L',
2893 p_price_list_validated_flag => 'N',
2894 x_pass_line => x_pass_line);
2895 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2896 l_pricing_start_time := dbms_utility.get_time;
2897 l_accum_qp_time := l_accum_qp_time + (l_pricing_start_time - l_pricing_end_time);
2898 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: x_pass_line: '||nvl(x_pass_line,'null'),1,'Y');
2899 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Copy_Line_To_Request...',1,'Y');
2900 END IF;
2901
2902 ASO_PRICING_CORE_PVT.Copy_Line_To_Request(
2903 p_Request_Type => p_control_rec.request_type,
2904 p_price_line_index => l_price_index,
2905 px_index_counter => i+1);
2906
2907
2908 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2909 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Append_asked_for...',1,'Y');
2910 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Before Append Ask for lx_index_counter:'
2911 ||lx_index_counter,1,'Y');
2912 END IF;
2913 l_index_counter := lx_index_counter;
2914
2915 --Do we need to call append ask for at line level
2916
2917 /*Store the line_id of all the service_lines*/
2918 If l_qte_line_tbl(i).service_item_flag = 'Y' then
2919 if l_service_qte_line_id_tbl.EXISTS(1) then
2920 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2921 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of l_service_qte_line_id_tbl.extend...',1,'Y');
2922 END IF;
2923 l_service_qte_line_id_tbl.extend;
2924 l_service_qte_line_id_tbl(l_service_qte_line_id_tbl.count)
2925 := l_qte_line_tbl(i).quote_line_id;
2926 else
2927 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2928 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: First quote_line_id in l_service_qte_line_id_tbl',1,'Y');
2929 END IF;
2930 l_service_qte_line_id_tbl:= JTF_NUMBER_TABLE(l_qte_line_tbl(i).quote_line_id);
2931 end if;
2932 end if;-- l_qte_line_tbl(i).service_item_flag = 'Y'
2933
2934 /*Store all the quote_line_id processed*/
2935 IF l_Qte_Line_id_tbl.EXISTS(1) THEN
2936 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2937 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of l_qte_line_id_tbl.extend...',1,'Y');
2938 END IF;
2939 l_Qte_Line_id_tbl.extend;
2940 l_Qte_Line_id_tbl(l_Qte_Line_id_tbl.count) := l_qte_line_tbl(i).quote_line_id;
2941 ELSE
2942 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2943 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: First quote_line_id in l_qte_line_id_tbl',1,'Y');
2944 END IF;
2945 l_Qte_Line_id_tbl := JTF_NUMBER_TABLE(l_qte_line_tbl(i).quote_line_id);
2946 END IF;
2947 --increment the line index
2948 px_line_index_search_tbl(l_qte_line_id) := l_price_index;
2949 l_price_index:= l_price_index+1;
2950 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2951 l_pricing_end_time := dbms_utility.get_time;
2952 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
2953 END IF;
2954
2955 End Loop; -- l_Qte_Line_tbl.count checking.
2956
2957 -- Call to Price Adjustment Relationships and Service Relationships
2958 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2959 l_pricing_start_time := dbms_utility.get_time;
2960 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Price_Adj_All...',1,'Y');
2961 END IF;
2962
2963 ASO_PRICING_CORE_PVT.Query_Price_Adj_All
2964 (p_quote_header_id => p_qte_header_rec.quote_header_id,
2965 x_adj_id_tbl => l_qte_adj_id_tbl);
2966
2967
2968 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2969 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Query_Relationships...',1,'Y');
2970 END IF;
2971
2972 ASO_PRICING_CORE_PVT.Query_Relationships(
2973 p_qte_adj_id_tbl => l_qte_adj_id_tbl,
2974 p_service_qte_line_id_tbl => l_service_qte_line_id_tbl);
2975
2976
2977 END IF; -- l_qte_line_tbl.count = 0 check.
2978
2979 -- Delete only the header level adj that are applied or updated because
2980 -- line level modifiers will not change in Calculate only call
2981 DELETE FROM ASO_PRICE_ADJUSTMENTS
2982 WHERE quote_header_id = p_qte_header_rec.quote_header_id
2983 AND quote_line_id IS NULL
2984 AND (applied_flag = 'Y' OR updated_flag = 'Y');
2985 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2986 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Adjustment Lines deleted '||sql%ROWCOUNT,1,'Y');
2987 END IF;
2988 DELETE FROM ASO_PRICE_ADJ_ATTRIBS
2989 WHERE PRICE_ADJUSTMENT_ID IN (SELECT PRICE_ADJUSTMENT_ID
2990 FROM ASO_PRICE_ADJUSTMENTS
2991 WHERE QUOTE_HEADER_ID = p_qte_header_rec.quote_header_id
2992 AND QUOTE_LINE_ID is NULL
2993 AND (applied_flag = 'Y' OR updated_flag = 'Y'));
2994 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2995 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Hdr Adjustment Attributes deleted '||sql%ROWCOUNT,1,'Y');
2996 END IF;
2997
2998 --Need to modify the global index table of pls integer types
2999 l_global_pls_tbl := ASO_PRICING_CORE_PVT.G_LDET_LINE_INDEX_TBL;
3000 ASO_PRICING_CORE_PVT.G_LDET_LINE_INDEX_TBL:= ASO_PRICING_CORE_PVT.Modify_Global_PlsIndex_Table(
3001 p_global_tbl => l_global_pls_tbl,
3002 p_search_tbl => px_line_index_search_tbl);
3003 l_global_num_tbl := ASO_PRICING_CORE_PVT.G_RLTD_LINE_INDEX_TBL;
3004 ASO_PRICING_CORE_PVT.G_RLTD_LINE_INDEX_TBL:= ASO_PRICING_CORE_PVT.Modify_Global_NumIndex_Table(
3005 p_global_tbl => l_global_num_tbl,
3006 p_search_tbl => px_line_index_search_tbl);
3007 l_global_num_tbl := ASO_PRICING_CORE_PVT.G_RLTD_RELATED_LINE_IND_TBL;
3008 ASO_PRICING_CORE_PVT.G_RLTD_RELATED_LINE_IND_TBL:= ASO_PRICING_CORE_PVT.Modify_Global_NumIndex_Table(
3009 p_global_tbl => l_global_num_tbl,
3010 p_search_tbl => px_line_index_search_tbl);
3011
3012 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3013 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Populate_QP_Temp_tables...',1,'Y');
3014 l_pricing_end_time := dbms_utility.get_time;
3015 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
3016 END IF;
3017
3018 ASO_PRICING_CORE_PVT.populate_qp_temp_tables;
3019
3020 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3021 l_pricing_start_time := dbms_utility.get_time;
3022 l_accum_qp_time := l_accum_qp_time + (l_pricing_start_time - l_pricing_end_time);
3023 END IF;
3024 -- Set the control rec for QP
3025
3026 l_req_control_rec.pricing_event := '';
3027 l_req_control_rec.calculate_flag := p_control_rec.calculate_flag;
3028 l_req_control_rec.simulation_flag := p_control_rec.simulation_flag;
3029 l_req_control_rec.TEMP_TABLE_INSERT_FLAG := 'N'; ---- Modified
3030 l_req_control_rec.source_order_amount_flag := 'Y';
3031 l_req_control_rec.GSA_CHECK_FLAG := 'Y';
3032 l_req_control_rec.GSA_DUP_CHECK_FLAG := 'Y';
3033 l_req_control_rec.REQUEST_TYPE_CODE := p_control_rec.request_type;
3034 l_req_control_rec.rounding_flag := 'Q';
3035
3036 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3037 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of QP_PREQ_PUB.PRICE_REQUEST', 1, 'Y');
3038 l_pricing_end_time := dbms_utility.get_time;
3039 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
3040 END IF;
3041
3042 /* Change for populating QP_PREQ_GRP.CONTROL_RECORD_TYPE.ORG_ID Yogeshwar (MOAC) */
3043
3044 l_req_control_rec.ORG_ID := p_qte_header_rec.org_id;
3045
3046 /* End of Change (MOAC) */
3047
3048
3049
3050
3051 QP_PREQ_PUB.PRICE_REQUEST
3052 (p_control_rec =>l_req_control_rec
3053 ,x_return_status =>l_return_status
3054 ,x_return_status_Text =>l_return_status_Text
3055 );
3056 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3057 x_return_status := l_return_status;
3058 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3059 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR');
3060 FND_MESSAGE.Set_Token('MSG_TXT', substr(l_return_status_text, 1,255), FALSE);
3061 FND_MSG_PUB.ADD;
3062 END IF;
3063 RAISE FND_API.G_EXC_ERROR;
3064 END IF;
3065
3066 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3067 l_pricing_start_time := dbms_utility.get_time;
3068 l_accum_qp_time := l_accum_qp_time + (l_pricing_start_time - l_pricing_end_time);
3069 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: End of QP_PREQ_PUB.PRICE_REQUEST', 1, 'Y');
3070 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After PRICE_REQUEST l_return_status:'
3071 ||l_return_status, 1, 'Y');
3072 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: After PRICE_REQUEST l_return_status_text '
3073 ||l_return_status_text,1,'Y');
3074 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Start of Post_Price_Request...',1,'Y');
3075 END IF;
3076
3077 ASO_PRICING_CORE_PVT.Copy_Price_To_Quote(
3078 P_Api_Version_Number => 1.0,
3079 P_Init_Msg_List => FND_API.G_FALSE,
3080 P_Commit => FND_API.G_FALSE,
3081 p_control_rec => l_prc_control_rec,
3082 p_qte_header_rec => p_qte_header_rec,
3083 p_insert_type => 'HDR_ONLY',
3084 x_return_status => x_return_status,
3085 x_msg_count => x_msg_count,
3086 x_msg_data => x_msg_data);
3087 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3088 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Copy_Price_To_Quote : x_return_status: '
3089 ||nvl(x_return_status,'NULL'),1,'Y');
3090 END IF;
3091 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3092 ASO_PRICING_CORE_PVT.Process_Charges(
3093 P_Api_Version_Number => 1.0,
3094 P_Init_Msg_List => FND_API.G_FALSE,
3095 P_Commit => FND_API.G_FALSE,
3096 p_control_rec => l_prc_control_rec,
3097 p_qte_header_rec => p_qte_header_rec,
3098 x_return_status => x_return_status,
3099 x_msg_count => x_msg_count,
3100 x_msg_data => x_msg_data);
3101 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3102 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT: Process_Charges : x_return_status: '
3103 ||nvl(x_return_status,'NULL'),1,'Y');
3104 END IF;
3105 END IF;
3106
3107 FND_MSG_PUB.Count_And_Get
3108 ( p_encoded => 'F',
3109 p_count => x_msg_count,
3110 p_data => x_msg_data
3111 );
3112
3113 FOR l IN 1 .. x_msg_count LOOP
3114 x_msg_data := FND_MSG_PUB.GET( p_msg_index => l, p_encoded => 'F');
3115 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3116 aso_debug_pub.add('Messge count and get '||x_msg_data, 1, 'Y');
3117 aso_debug_pub.add('Messge count and get '||x_msg_count, 1, 'Y');
3118 END IF;
3119 END LOOP;
3120
3121
3122 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3123 l_pricing_end_time := dbms_utility.get_time;
3124 l_accum_aso_time := l_accum_aso_time + (l_pricing_end_time - l_pricing_start_time);
3125 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:********Timing in Calculate Call**************',1,'Y');
3126 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Total time taken (in secs) in Calculate call(Besides Pricing):'||l_accum_aso_time/100,1,'Y');
3127 aso_debug_pub.add('ASO_PRICING_FLOWS_PVT:Total time taken (in secs) in Pricing+BuildContext:'||l_accum_qp_time/100,1,'Y');
3128 END IF;
3129
3130
3131
3132 END Price_Quote_Calculate_Call;
3133
3134
3135
3136 End ASO_PRICING_FLOWS_PVT;