[Home] [Help]
PACKAGE BODY: APPS.ASO_DEAL_PUB
Source
1 PACKAGE BODY ASO_DEAL_PUB as
2 /* $Header: asoidmib.pls 120.9 2008/07/11 10:08:01 rassharm noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_DEAL_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoidmib.pls';
6
7
8 -- Start of Comments
9 -- Package name : ASO_DEAL_PUB
10 -- Purpose : API methods for implementing Deal Management Integration
11 -- End of Comments
12
13
14
15 Procedure Update_Quote_From_Deal
16 ( P_Quote_Header_Id IN NUMBER,
17 P_resource_id IN NUMBER,
18 P_event IN VARCHAR2 := FND_API.G_FALSE,
19 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
20 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
21 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
22 )
23 is
24
25 -- for fetching status id
26 CURSOR C_qte_status_id (pc_quote_status_code VARCHAR2)
27 IS
28 SELECT quote_status_id
29 FROM aso_quote_statuses_b
30 WHERE status_code = pc_quote_status_code;
31
32 -- for fetching quote header details
33 CURSOR C_quote_header(pc_quote_header_id NUMBER)
34 IS
35 SELECT last_update_date, quote_number, max_version_flag, pricing_status_indicator, tax_status_indicator,quote_status_id,price_request_id
36 FROM aso_quote_headers_all
37 WHERE quote_header_id = pc_quote_header_id;
38
39
40 -- for fetching line level details
41 CURSOR C_quote_line(pc_quote_line_id NUMBER)
42 IS
43 SELECT aqla.line_quote_price,aqla.quantity, nvl(aship.ship_method_code,'X') ship_method_code,nvl(payment_term_id,-1) payment_term_id,nvl(PRICING_LINE_TYPE_INDICATOR,'XXX') PRICING_LINE_TYPE_INDICATOR
44 FROM aso_quote_lines_all aqla, aso_shipments aship,aso_payments apay
45 WHERE aqla.quote_line_id = pc_quote_line_id
46 AND aqla.quote_line_id = aship.quote_line_id (+)
47 AND aqla.quote_line_id = apay.quote_line_id (+) ;
48
49
50 -- for fetching already applied adjustment except deal adjustment
51 cursor cur_app_adj(pc_quote_header_id NUMBER,pc_quote_line_id NUMBER,pc_modifier_line_id NUMBER)
52 IS
53 SELECT
54 PRICE_ADJUSTMENT_ID,
55 PRICE_BREAK_TYPE_CODE,
56 MODIFIER_HEADER_ID,
57 MODIFIER_LINE_ID,
58 MODIFIER_LINE_TYPE_CODE,
59 PRICING_GROUP_SEQUENCE,
60 PRICING_PHASE_ID,
61 ARITHMETIC_OPERATOR,
62 nvl(OPERAND_PER_PQTY,OPERAND) operand,
63 MODIFIED_FROM,
64 MODIFIED_TO,
65 UPDATE_ALLOWABLE_FLAG,
66 ON_INVOICE_FLAG,
67 MODIFIER_LEVEL_CODE,
68 BENEFIT_QTY,
69 BENEFIT_UOM_CODE,
70 LIST_LINE_NO,
71 ACCRUAL_FLAG,
72 ACCRUAL_CONVERSION_RATE,
73 CHARGE_TYPE_CODE,
74 CHARGE_SUBTYPE_CODE,
75 RANGE_BREAK_QUANTITY,
76 MODIFIER_MECHANISM_TYPE_CODE,
77 CHANGE_REASON_CODE,
78 CHANGE_REASON_TEXT,
79 adjusted_amount,
80 automatic_flag
81 from aso_price_adjustments
82 where quote_header_id=pc_quote_header_id
83 and quote_line_id =pc_quote_line_id
84 and applied_flag='Y'
85 and modifier_line_id<>nvl(pc_modifier_line_id,-1)
86 AND nvl(expiration_date,sysdate) >= sysdate;
87
88
89
90
91 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_QUOTE_FROM_DEAL';
92 l_api_version_number CONSTANT NUMBER := 1.0;
93 p_api_version_number CONSTANT NUMBER := 1.0;
94 p_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
95
96 l_access_level VARCHAR2(10);
97 l_db_link varchar2(240);
98 l_quote_line_id NUMBER;
99 l_updated_line_price NUMBER;
100 l_line_quote_price NUMBER;
101 i NUMBER:=0; -- line index
102 s_index NUMBER:=0; -- shipment index
103 p_index NUMBER:=0; -- price adjustment index
104 pa_index NUMBER:=0; -- payment term index
105
106 l_sqlstmt VARCHAR2(1000);
107 l_modifier_line_profile VARCHAR2(30);
108 l_continue VARCHAR2(1);
109 l_count_modifier NUMBER:=0;
110 l_uom_code varchar2(30);
111 l_currency_code varchar2(30);
112 l_ordered_qty NUMBER;
113 l_line_qty NUMBER;
114 l_modifier_name varchar2(240);
115 ln_count NUMBER:=0;
116 l_pricing_line_type_indicator ASO_QUOTE_LINES_ALL.PRICING_LINE_TYPE_INDICATOR%type;
117
118 -- For multi-row query dynamic execution
119 TYPE PRCCURREF is REF CURSOR;
120 prcadj_cv PRCCURREF;
121
122 l_last_update_date ASO_QUOTE_HEADERS_ALL.last_update_date%TYPE;
123 l_quote_number ASO_QUOTE_HEADERS_ALL.quote_number%TYPE;
124 l_max_version_flag ASO_QUOTE_HEADERS_ALL.max_version_flag%TYPE;
125 l_pricing_status ASO_QUOTE_HEADERS_ALL.PRICING_STATUS_INDICATOR%TYPE;
126 l_tax_status ASO_QUOTE_HEADERS_ALL.TAX_STATUS_INDICATOR%TYPE;
127 l_quote_status_id ASO_QUOTE_HEADERS_ALL.QUOTE_STATUS_ID%TYPE;
128 ln_quote_status_id ASO_QUOTE_HEADERS_ALL.QUOTE_STATUS_ID%TYPE;
129 ld_quote_status_id ASO_QUOTE_HEADERS_ALL.QUOTE_STATUS_ID%TYPE;
130 l_price_request_id ASO_QUOTE_HEADERS_ALL.PRICE_REQUEST_ID%TYPE;
131 l_payment_term_id ASO_PAYMENTS.PAYMENT_TERM_ID%TYPE;
132 l_line_payment_term_id ASO_PAYMENTS.PAYMENT_TERM_ID%TYPE;
133 l_shipment_method_code ASO_SHIPMENTS.SHIP_METHOD_CODE%TYPE;
134 l_line_ship_method_code ASO_SHIPMENTS.SHIP_METHOD_CODE%TYPE;
135 L_modifier_line_id ASO_PRICE_ADJUSTMENTS.MODIFIER_LINE_ID%TYPE;
136 l_price_adjustment_id ASO_PRICE_ADJUSTMENTS.PRICE_ADJUSTMENT_ID%TYPE;
137 l_applied_flag ASO_PRICE_ADJUSTMENTS.APPLIED_FLAG%TYPE;
138 l_operand ASO_PRICE_ADJUSTMENTS.OPERAND%TYPE;
139 ld_operand ASO_PRICE_ADJUSTMENTS.OPERAND%TYPE;
140
141
142 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_Miss_Control_Rec;
143 l_qte_header_rec ASO_QUOTE_PUB.qte_Header_Rec_Type:=ASO_QUOTE_PUB.G_MISS_Qte_Header_Rec;
144
145 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type:=ASO_QUOTE_PUB.G_MISS_Qte_Line_Rec;
146 l_qte_line_tbl ASO_QUOTE_PUB.QTE_LINE_Tbl_Type;
147
148 l_ln_Payment_rec ASO_QUOTE_PUB.Payment_Rec_Type:=ASO_QUOTE_PUB.G_MISS_PAYMENT_rec;
149 l_ln_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type:= ASO_QUOTE_PUB.G_MISS_PAYMENT_TBL;
150 l_ln_Payment_Tbl1 ASO_QUOTE_PUB.Payment_Tbl_Type:= ASO_QUOTE_PUB.G_MISS_PAYMENT_TBL;
151
152 l_ln_Shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type:=ASO_QUOTE_PUB.G_MISS_shipment_rec;
153 l_ln_Shipment_Tbl ASO_QUOTE_PUB.Shipment_Tbl_Type:= ASO_QUOTE_PUB.G_MISS_SHIPMENT_TBL;
154 l_ln_Shipment_Tbl1 ASO_QUOTE_PUB.Shipment_Tbl_Type:= ASO_QUOTE_PUB.G_MISS_SHIPMENT_TBL;
155
156 l_price_adj_rec ASO_QUOTE_PUB.Price_Adj_Rec_Type:= ASO_QUOTE_PUB.G_MISS_Price_Adj_REC ;
157 l_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type:= ASO_QUOTE_PUB.G_Miss_Price_Adj_Tbl;
158
159
160
161 --
162
163 lx_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
164 lx_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
165 lx_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
166 lx_hd_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
167 lx_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
168 lx_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
169 lx_hd_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
170 lx_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
171
172 lx_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
173 lx_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
174 lx_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
175 lx_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
176 lx_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
177 lx_ln_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
178 lx_ln_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
179 lx_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
180 lx_ln_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
181 lx_ln_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
182
183 l_file VARCHAR2(200); -- for log generation
184
185 l_len_sqlerrm Number ; -- For error handling
186 l_err number := 1; -- For error handling
187
188 BEGIN
189
190
191 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
192 --pp_debug('In update deal'|| aso_debug_pub.g_debug_flag);
193 if aso_debug_pub.g_debug_flag='Y' then -- enabling trace
194 aso_debug_pub.SetDebugLevel(10);
195 aso_debug_pub.Initialize;
196 l_file := ASO_DEBUG_PUB.Set_Debug_Mode('FILE');
197 --pp_debug('In update deal'|| l_file);
198 aso_debug_pub.debug_on;
199 end if;
200
201 --pp_debug('In update deal'|| aso_debug_pub.g_debug_flag);
202 IF aso_debug_pub.g_debug_flag = 'Y' then
203 aso_debug_pub.add('ASO_DEAL_PUB: ****** Start of Update_Quote_From_Deal API ******', 1, 'Y');
204 END IF;
205
206 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
207 1.0,
208 l_api_name,
209 G_PKG_NAME)
210 THEN
211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 END IF;
214
215 -- Initialize message list if p_init_msg_list is set to TRUE.
216 IF FND_API.to_Boolean( p_init_msg_list )
217 THEN
218 FND_MSG_PUB.initialize;
219 END IF;
220
221 -- Initialize API return status to SUCCESS
222 x_return_status := FND_API.G_RET_STS_SUCCESS;
223
224 --
225
226 --pp_debug('Event:' || p_event);
227 --pp_debug('Resource_id' || p_resource_id);
228 --pp_debug('Quote num: ' || p_quote_header_id);
229 --pp_debug('event'||p_event);
230 -- API body
231 IF P_event in ('SUBMITTED', 'CANCELED', 'ACCEPTED') then
232 open C_quote_header (p_quote_header_id);
233 FETCH C_quote_header INTO l_last_update_date, l_quote_number, l_max_version_flag, l_pricing_status, l_tax_status,l_quote_Status_id,l_price_request_id;
234 close C_quote_header;
235
236 --pp_debug('in first if'||p_event);
237 l_access_level := ASO_SECURITY_INT.Get_Quote_Access(p_resource_id,l_quote_number);
238
239 IF aso_debug_pub.g_debug_flag = 'Y' then
240 aso_debug_pub.add('ASO_DEAL_PUB: Access Level'||l_access_level, 1, 'Y');
241 END IF;
242 --pp_debug('Access level:' || l_access_level);
243 IF ((l_access_level <> 'UPDATE') or (l_max_version_flag <> 'Y') or (l_price_request_id is not null)) THEN
244 x_return_status := fnd_api.g_ret_sts_error;
245 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
246 if (l_max_version_flag <> 'Y') then
247 FND_MESSAGE.Set_Name('QOT', 'QOT_QTE_NOT_HIGH_VERSION');
248 FND_MSG_PUB.Add;
249 elsif (l_price_request_id is not null) then
250 FND_MESSAGE.Set_Name('ASO', 'ASO_CONC_REQUEST_RUNNING');
251 FND_MSG_PUB.Add;
252 end if;
253 RAISE FND_API.G_EXC_ERROR;
254 END IF;
255
256 ELSIF l_pricing_status IS NULL OR
257 l_pricing_status <> 'C' THEN
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
260 FND_MESSAGE.Set_Name('ASO', 'ASO_PRICING_INCOMPLETE');
261 FND_MSG_PUB.ADD;
262 END IF;
263 RAISE FND_API.G_EXC_ERROR;
264 ELSIF l_tax_status IS NULL OR
265 l_tax_status <> 'C' THEN
266 x_return_status := FND_API.G_RET_STS_ERROR;
267 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
268 FND_MESSAGE.Set_Name('ASO', 'ASO_TAX_INCOMPLETE');
269 FND_MSG_PUB.ADD;
270 END IF;
271 RAISE FND_API.G_EXC_ERROR;
272 END IF;
273
274
275 l_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(P_Quote_Header_Id); -- Assigning the header record
276
277 END IF; -- P_event in ('SUBMITTED', 'CANCELED', 'ACCEPTED')
278
279 IF p_event = 'SUBMITTED' THEN
280 IF aso_debug_pub.g_debug_flag = 'Y' then
281 aso_debug_pub.add('ASO_DEAL_PUB:Status - '||p_event, 1, 'Y');
282 END IF;
283 open C_qte_status_id('PRICE APPROVAL PENDING');
284 fetch C_qte_status_id into ln_quote_Status_id;
285 close C_qte_status_id;
286 --pp_debug('In Event Submitted'||ln_quote_Status_id);
287 -- Checking for the quote status transition
288 ASO_VALIDATE_PVT.Validate_Status_Transition(
289 p_init_msg_list => FND_API.G_FALSE,
290 p_source_status_id => l_quote_Status_id,
291 p_dest_status_id => ln_quote_Status_id,
292 x_return_status => x_return_status,
293 x_msg_count => x_msg_count,
294 x_msg_data => x_msg_data);
295
296
297 --pp_debug('After status validation In Event Submitted'||ln_quote_Status_id);
298
299 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
300 --pp_debug('After status validation failure');
301 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
302 FND_MESSAGE.Set_Name('ASO', 'ASO_ERR_NO_QTE_STAT_TRANSITION');
303 FND_MSG_PUB.ADD;
304 RAISE FND_API.G_EXC_ERROR;
305 END IF;
306
307 end if;
308
309 update aso_quote_headers_All
310 set last_update_date=l_last_update_date,quote_status_id=ln_quote_Status_id
311 where quote_header_id=p_quote_header_id;
312
313
314 ELSIF p_event = 'ACCEPTED' THEN
315 l_db_link := FND_PROFILE.VALUE('QPR_PN_DBLINK') ;
316
317 IF l_db_link is NOT NULL THEN
318 l_db_link := '@' || l_db_link;
319 END IF;
320
321
322 --pp_debug('accepted');
323 open C_qte_status_id('PRICE APPROVAL PENDING');
324 fetch C_qte_status_id into ld_quote_Status_id;
325 close C_qte_status_id;
326
327 if l_quote_Status_id<> ld_quote_Status_id then
328 SELECT count(*) into ln_count FROM
329 ASO_STATUS_TRANSITIONS_V
330 WHERE TO_STATUS_ID = ld_quote_Status_id
331 AND FROM_STATUS_ID = l_quote_Status_id;
332
333 if ln_count=0 then
334
335 IF aso_debug_pub.g_debug_flag = 'Y' then
336 aso_debug_pub.add('ASO_DEAL_PUB:Status ln_count=0', 1, 'Y');
337 END IF;
338
339 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
340 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
341 FND_MESSAGE.Set_Name('ASO', 'ASO_ERR_NO_QTE_STAT_TRANSITION');
342 FND_MSG_PUB.ADD;
343 RAISE FND_API.G_EXC_ERROR;
344 END IF;
345 end if;
346 else -- if valid transition to price approval pending
347 IF aso_debug_pub.g_debug_flag = 'Y' then
348 aso_debug_pub.add('ASO_DEAL_PUB:Status ln_count>0'||ld_quote_Status_id, 1, 'Y');
349 END IF;
350 update aso_quote_headers_All
351 set quote_status_id=ld_quote_Status_id
352 where quote_header_id=p_quote_header_id;
353 end if; -- status transition doesnot exist
354 end if; -- if status is not price approval pending
355
356 open C_qte_status_id('PRICING APPROVED');
357 fetch C_qte_status_id into ln_quote_Status_id;
358 close C_qte_status_id;
359
360 IF aso_debug_pub.g_debug_flag = 'Y' then
361 aso_debug_pub.add('ASO_DEAL_PUB:Status ln_count>0'||ld_quote_Status_id||'new stat'||ln_quote_Status_id, 1, 'Y');
362 END IF;
363
364 ASO_VALIDATE_PVT.Validate_Status_Transition(
365 p_init_msg_list => FND_API.G_FALSE,
366 p_source_status_id => ld_quote_Status_id,
367 p_dest_status_id => ln_quote_Status_id,
368 x_return_status => x_return_status,
369 x_msg_count => x_msg_count,
370 x_msg_data => x_msg_data);
371
372 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
373 --pp_debug('After status validation failure');
374 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
375 FND_MESSAGE.Set_Name('ASO', 'ASO_ERR_NO_QTE_STAT_TRANSITION');
376 FND_MSG_PUB.ADD;
377 RAISE FND_API.G_EXC_ERROR;
378 END IF;
379 end if;
380
381 l_qte_header_rec.quote_status_id:=ln_quote_Status_id;
382 l_qte_header_rec.last_update_date := l_last_update_date;
383
384
385
386 -- for pricing adjustment updations
387 IF aso_debug_pub.g_debug_flag='Y' THEN
388 aso_debug_pub.add( 'In call update quote from deal API Before Price Adjustment' , 1 ,'Y') ;
389 end if;
390 /*
391 l_modifier_line_profile:=fnd_profile.value('QPR_DEAL_DIFF_MODIFIER');
392 L_modifier_line_id:=to_number(l_modifier_line_profile);
393
394 IF L_modifier_line_id is NULL THEN
395 FND_MESSAGE.SET_NAME('ASO','ASO_DEAL_PRC_PROFILE_NOT_SET');
396 --FND_MESSAGE.SET_TOKEN('PROFILE', 'QPR_DEAL_DIFF_MODIFIER');
397 FND_MSG_PUB.ADD;
401 RAISE FND_API.G_EXC_ERROR;
398 IF aso_debug_pub.g_debug_flag='Y' THEN
399 aso_debug_pub.add( 'Profile is NULL ' , 1 ) ;
400 END IF;
402 END IF;
403
404
405 IF aso_debug_pub.g_debug_flag='Y' THEN
406 aso_debug_pub.add( 'In call Update Quote From Deal API-Modifier ' || L_modifier_line_id , 1 ,'Y') ;
407 END IF;
408 */
409
410 -- using ref cursor for dynamic execution of multirow query
411
412 OPEN prcadj_cv FOR 'SELECT UOM_CODE ,CURRENCY_CODE ,ORDERED_QTY ,PRICE, SOURCE_REF_LINE_ID, SHIP_METHOD_CODE ,PAYMENT_TERM_ID' ||
413 ' FROM QPR_INT_DEAL_V' || l_db_link ||
414 ' WHERE CHANGED = ' || '''Y''' ||
415 ' AND SOURCE_REF_HEADER_ID = :p_quote_header_id ' ||
416 ' AND SOURCE_REF_LINE_ID is not null AND SOURCE = 697'
417 USING p_quote_header_id ;
418
419 loop
420
421 fetch prcadj_cv into l_uom_code ,l_currency_code ,l_ordered_qty,l_updated_line_price,l_quote_line_id,l_shipment_method_code,l_payment_term_id;
422 --pp_debug('values from deal qty ' || l_ordered_qty||'price'||l_updated_line_price||'quote_line_id'||l_quote_line_id);
423 exit when prcadj_cv%NOTFOUND;
424 l_continue:='Y';
425 IF aso_debug_pub.g_debug_flag='Y' THEN
426 aso_debug_pub.add( 'In call Update Quote From Deal API-source line id ' || l_quote_line_id||'Price'||l_updated_line_price , 1 ) ;
427 END IF;
428
429
430 i:=i+1;
431 -- Entering line level detail
432
433 l_Qte_Line_Tbl(i) := ASO_UTILITY_PVT.Get_Qte_Line_rec;
434 l_qte_line_tbl(i).QUOTE_HEADER_ID := p_quote_header_id;
435 l_qte_line_tbl(i).QUOTE_line_id := l_quote_line_id;
436 l_qte_line_tbl(i).operation_code := 'UPDATE';
437
438
439
440
441 -- fetching line level details
442 open c_quote_line(l_quote_line_id);
443 fetch c_quote_line into l_line_quote_price,l_line_qty,l_line_ship_method_code,l_line_payment_term_id,l_pricing_line_type_indicator;
444 close c_quote_line;
445
446 if l_pricing_line_type_indicator<> 'F' then
447 l_qte_line_tbl(i).PRICING_LINE_TYPE_INDICATOR:='D';
448 end if;
449
450 -- checking for quanity
451 if (l_ordered_qty is not null) and (l_ordered_qty<>l_line_qty) then
452 IF aso_debug_pub.g_debug_flag='Y' THEN
453 aso_debug_pub.add( 'In call Update Quote From Deal API-qty change ' || l_ordered_qty , 1 ) ;
454 --pp_debug('In call Update Quote From Deal API-qty change ' || l_ordered_qty);
455 END IF;
456 l_qte_line_tbl(i).quantity :=l_ordered_qty;
457 l_qte_line_tbl(i).pricing_quantity :=l_ordered_qty;
458 end if;
459
460
461 -- for shipment line level changes
462 if (l_shipment_method_code is not null) and (l_shipment_method_code<>l_line_ship_method_code) then
463 l_ln_shipment_Tbl1:= ASO_UTILITY_PVT.Query_Shipment_Rows(P_Quote_Header_Id,l_quote_line_id); -- For Fetching line shipment terms
464
465 IF aso_debug_pub.g_debug_flag='Y' THEN
466 aso_debug_pub.add( 'In call update quote from deal API Before Shipment'|| l_ln_shipment_Tbl1.count , 1 ) ;
467 END IF;
468 s_index:=s_index+1;
469 if l_ln_shipment_Tbl1.count = 0 then
470 l_ln_shipment_Tbl(s_index):=ASO_QUOTE_PUB.G_MISS_Shipment_REC;
471 l_ln_shipment_Tbl(s_index).Quote_Header_Id := P_Quote_Header_Id;
472 l_ln_shipment_Tbl(s_index).Quote_line_Id := l_quote_line_id;
473 l_ln_shipment_Tbl(s_index).SHIP_METHOD_CODE := l_shipment_method_code;
474 l_ln_shipment_Tbl(s_index).operation_code := 'CREATE' ;
475 elsif l_ln_shipment_Tbl1.count > 0 then
476 l_ln_shipment_Tbl(s_index):=ASO_QUOTE_PUB.G_MISS_Shipment_REC;
477 l_ln_shipment_rec:=l_ln_shipment_Tbl1(1);
478 l_ln_shipment_rec.Quote_Header_Id := P_Quote_Header_Id;
479 l_ln_shipment_rec.Quote_line_Id := l_quote_line_id;
480 l_ln_shipment_rec.SHIP_METHOD_CODE := l_shipment_method_code;
481 l_ln_shipment_rec.operation_code := 'UPDATE' ;
482 l_ln_shipment_Tbl(s_index):=l_ln_shipment_rec;
483 --pp_debug('Entered shipment shipment id'|| l_ln_shipment_rec.shipment_id);
484 end if;
485
486 end if;
487 -- end shipment
488
489 IF aso_debug_pub.g_debug_flag='Y' THEN
490 aso_debug_pub.add( 'In call update quote from deal API after payment number of rows: '||l_ln_shipment_Tbl.count, 1 ) ;
491 END IF;
492
493
494 --pp_debug('before Entered payment'|| l_payment_term_id||'line'||l_line_payment_term_id);
495 -- for payment terms line level changes
496 IF (l_payment_term_id is not NULL) and (l_payment_term_id <> l_line_payment_term_id) THEN
497 l_ln_Payment_Tbl1:= ASO_UTILITY_PVT.Query_Payment_Rows(P_Quote_Header_Id,l_quote_line_id); -- For Fetching header payment terms
498 IF aso_debug_pub.g_debug_flag='Y' THEN
499 aso_debug_pub.add( 'In call update quote from deal API payment number of rows: '||l_ln_Payment_Tbl1.count||',payment term id'||l_payment_term_id , 1 ) ;
500 END IF;
501 pa_index:=pa_index+1;
502 if l_ln_Payment_Tbl1.count = 0 then
503 l_ln_Payment_Tbl(pa_index):=ASO_QUOTE_PUB.G_MISS_Payment_REC;
504 l_ln_Payment_Tbl(pa_index).Quote_Header_Id := P_Quote_Header_Id;
505 l_ln_Payment_Tbl(pa_index).Quote_line_Id := l_quote_line_id;
506 l_ln_Payment_Tbl(pa_index).PAYMENT_TERM_ID := l_payment_term_id;
507 l_ln_Payment_Tbl(pa_index).operation_code := 'CREATE' ;
508 elsif l_ln_Payment_Tbl1.count > 0 then
509 l_ln_Payment_Tbl(pa_index):=ASO_QUOTE_PUB.G_MISS_Payment_REC;
510 l_ln_Payment_rec:=l_ln_Payment_Tbl1(1);
511 IF aso_debug_pub.g_debug_flag='Y' THEN
512 aso_debug_pub.add( 'In call update quote from deal API after payment number of rows: '||l_ln_Payment_rec.payment_id, 1 ) ;
513 END IF;
514 l_ln_Payment_rec.PAYMENT_TERM_ID := l_payment_term_id;
515 l_ln_Payment_rec.Quote_Header_Id := P_Quote_Header_Id;
516 l_ln_Payment_rec.Quote_line_Id := l_quote_line_id;
517 l_ln_Payment_rec.operation_code := 'UPDATE' ;
518 l_ln_Payment_Tbl(pa_index):=l_ln_Payment_rec;
519 end if;
520
521 END IF;
522 -- end payment
523 IF aso_debug_pub.g_debug_flag='Y' THEN
524 aso_debug_pub.add( 'In call update quote from deal API after payment number of rows: '||l_ln_Payment_Tbl.count, 1 ) ;
525 END IF;
526
527 -- New Coding for already applied line level adjustments
528
529 -- Getting the modifier profile value
530 l_modifier_line_profile:=fnd_profile.value('QPR_DEAL_DIFF_MODIFIER');
531 L_modifier_line_id:=to_number(l_modifier_line_profile);
532
536
533
534 for cur_auto_adj in cur_app_adj(p_quote_header_id,l_quote_line_id,l_modifier_line_id)
535 loop
537 IF aso_debug_pub.g_debug_flag='Y' THEN
538 aso_debug_pub.add( 'Entered new code for previous adjustments cur_auto_adj', 1 ) ;
539 END IF;
540
541 p_index:=p_index+1;
542 -- Assigning the price adjustments for line id fetched
543 l_Price_Adjustment_Tbl(p_index):= ASO_UTILITY_PVT.Get_price_adj_Rec;
544 l_Price_Adjustment_Tbl(p_index).quote_header_id:=p_quote_header_id;
545 l_Price_Adjustment_Tbl(p_index).quote_line_id:=l_quote_line_id;
546 l_Price_Adjustment_Tbl(p_index).price_adjustment_id:= cur_auto_adj.price_adjustment_id;
547 l_Price_Adjustment_Tbl(p_index).PRICE_BREAK_TYPE_CODE:= cur_auto_adj.PRICE_BREAK_TYPE_CODE;
548
549 l_Price_Adjustment_Tbl(p_index).MODIFIER_HEADER_ID:= cur_auto_adj.MODIFIER_HEADER_ID;
550 l_Price_Adjustment_Tbl(p_index).MODIFIER_LINE_ID:= cur_auto_adj.MODIFIER_LINE_ID;
551 l_Price_Adjustment_Tbl(p_index).MODIFIER_LINE_TYPE_CODE:= cur_auto_adj.MODIFIER_LINE_TYPE_CODE;
552 l_Price_Adjustment_Tbl(p_index).PRICING_GROUP_SEQUENCE:= cur_auto_adj.PRICING_GROUP_SEQUENCE;
553
554 l_Price_Adjustment_Tbl(p_index).PRICING_PHASE_ID:= cur_auto_adj.PRICING_PHASE_ID;
555 l_Price_Adjustment_Tbl(p_index).MODIFIED_FROM:= cur_auto_adj.MODIFIED_FROM;
556 l_Price_Adjustment_Tbl(p_index).MODIFIED_TO:= cur_auto_adj.MODIFIED_TO;
557 l_Price_Adjustment_Tbl(p_index).UPDATE_ALLOWABLE_FLAG:= cur_auto_adj.UPDATE_ALLOWABLE_FLAG;
558
559 l_Price_Adjustment_Tbl(p_index).ON_INVOICE_FLAG:= cur_auto_adj.ON_INVOICE_FLAG;
560 l_Price_Adjustment_Tbl(p_index).MODIFIER_LEVEL_CODE:= cur_auto_adj.MODIFIER_LEVEL_CODE;
561 l_Price_Adjustment_Tbl(p_index).BENEFIT_QTY:= cur_auto_adj.BENEFIT_QTY;
562 l_Price_Adjustment_Tbl(p_index).BENEFIT_UOM_CODE:= cur_auto_adj.BENEFIT_UOM_CODE;
563
564 l_Price_Adjustment_Tbl(p_index).LIST_LINE_NO:= cur_auto_adj.LIST_LINE_NO;
565 l_Price_Adjustment_Tbl(p_index).ACCRUAL_FLAG:= cur_auto_adj.ACCRUAL_FLAG;
566 l_Price_Adjustment_Tbl(p_index).ACCRUAL_CONVERSION_RATE:= cur_auto_adj.ACCRUAL_CONVERSION_RATE;
567 l_Price_Adjustment_Tbl(p_index).CHARGE_TYPE_CODE:= cur_auto_adj.CHARGE_TYPE_CODE;
568
569 l_Price_Adjustment_Tbl(p_index).CHARGE_SUBTYPE_CODE:= cur_auto_adj.CHARGE_SUBTYPE_CODE;
570 l_Price_Adjustment_Tbl(p_index).RANGE_BREAK_QUANTITY:= cur_auto_adj.RANGE_BREAK_QUANTITY;
571 l_Price_Adjustment_Tbl(p_index).MODIFIER_MECHANISM_TYPE_CODE:= cur_auto_adj.MODIFIER_MECHANISM_TYPE_CODE;
572 l_Price_Adjustment_Tbl(p_index).automatic_flag:= cur_auto_adj.automatic_flag;
573
574
575 l_Price_Adjustment_Tbl(p_index).applied_flag:= 'Y';
576 l_Price_Adjustment_Tbl(p_index).updated_flag:= 'Y';
577 l_Price_Adjustment_Tbl(p_index).Operand := cur_auto_adj.operand;
578 l_Price_Adjustment_Tbl(p_index).adjusted_amount:=nvl(cur_auto_adj.adjusted_amount,cur_auto_adj.operand);
579 l_Price_Adjustment_Tbl(p_index).Arithmetic_operator := cur_auto_adj.ARITHMETIC_OPERATOR;
580 l_Price_Adjustment_Tbl(p_index).operation_code := 'UPDATE';
581 l_Price_Adjustment_Tbl(p_index).change_reason_code :=cur_auto_adj.change_reason_code;
582 l_Price_Adjustment_Tbl(p_index).change_reason_text:=cur_auto_adj.change_reason_text;
583 end loop;
584
585 IF aso_debug_pub.g_debug_flag='Y' THEN
586 aso_debug_pub.add( 'count new code for previous adjustments'||l_Price_Adjustment_Tbl.count, 1 ) ;
587 END IF;
588
589
590 -- end coding for already applied automatic adjustments
591
592 if (l_updated_line_price is not null) and (l_line_quote_price<>l_updated_line_price) then -- checking if price has been modified or not
593
594
595 /* l_modifier_line_profile:=fnd_profile.value('QPR_DEAL_DIFF_MODIFIER');
596 L_modifier_line_id:=to_number(l_modifier_line_profile);
597 */
598 IF aso_debug_pub.g_debug_flag='Y' THEN
599 aso_debug_pub.add( 'Line Modifier Value'||L_modifier_line_id, 1 ) ;
600 END IF;
601
602 IF L_modifier_line_id is NULL THEN
603 FND_MESSAGE.SET_NAME('ASO','ASO_DEAL_PRC_PROFILE_NOT_SET');
604 --FND_MESSAGE.SET_TOKEN('PROFILE', 'QPR_DEAL_DIFF_MODIFIER');
605 FND_MSG_PUB.ADD;
606 IF aso_debug_pub.g_debug_flag='Y' THEN
607 aso_debug_pub.add( 'Profile is NULL ' , 1 ) ;
608 END IF;
609 RAISE FND_API.G_EXC_ERROR;
610 END IF;
611
612
613 IF aso_debug_pub.g_debug_flag='Y' THEN
614 aso_debug_pub.add( 'In call Update Quote From Deal API-Modifier ' || L_modifier_line_id , 1 ,'Y') ;
615 END IF;
616
617
618
619 --pp_debug('in approved event price'||l_quote_line_id);
620 select count(*) into l_count_modifier
621 FROM aso_price_adjustments apa, Aso_quote_lines_all aqla
622 WHERE apa.quote_line_id = l_quote_line_id
623 AND apa.modifier_line_id = l_modifier_line_id
624 AND nvl(apa.expiration_date,sysdate) >= sysdate
625 AND apa.quote_line_id = aqla.quote_line_id;
626
627 if l_count_modifier = 1 then
628 l_continue:='Y';
629 SELECT apa.price_adjustment_id, apa.Applied_flag, apa.Operand, aqla.line_quote_price
630 into l_price_adjustment_id,l_applied_flag,l_operand,l_line_quote_price
631 FROM aso_price_adjustments apa, Aso_quote_lines_all aqla
632 WHERE apa.quote_line_id = l_quote_line_id
633 AND apa.modifier_line_id = l_modifier_line_id
634 AND nvl(apa.expiration_date,sysdate) >= sysdate
635 AND apa.quote_line_id = aqla.quote_line_id;
636 elsif l_count_modifier=0 then
637 l_continue:='N';
638 IF aso_debug_pub.g_debug_flag='Y' THEN
639 aso_debug_pub.add( 'Modifier NO data found-Dont update this Line for modifier' , 1,'Y' ) ; -- need to have an error message here
640 end if;
641
642 select name into l_modifier_name
643 from qp_list_headers_tl t,qp_list_lines td
644 where t.list_header_id = td.list_header_id
645 and list_line_id=l_modifier_line_id
646 AND t.LANGUAGE(+) = userenv('LANG');
647
651 RAISE FND_API.G_EXC_ERROR;
648 FND_MESSAGE.SET_NAME('ASO','ASO_DEAL_PRC_ADJ_NOT_DEFINED');
649 FND_MESSAGE.SET_TOKEN('COLUMN', l_modifier_name);
650 FND_MSG_PUB.ADD;
652 end if;
653
654
655 IF aso_debug_pub.g_debug_flag='Y' THEN
656 aso_debug_pub.add( 'Modifier Adjustment exist'||l_continue , 1,'Y' ) ;
657 --pp_debug( 'Modifier Adjustment exist'||l_continue);
658 end if;
659
660 IF (l_continue='Y') then -- Adjustment exists for the line
661
662 IF l_applied_flag = 'Y' then -- Calculating Operand
663 ld_operand:= (l_line_quote_price+l_operand) - l_updated_line_price;
664 Elsif l_applied_flag = 'N' then
665 --pp_debug( 'Modifier Adjustment exist ld_operand121'||ld_operand);
666 ld_operand:= l_line_quote_price - l_updated_line_price ;
667 End if;
668
669 IF aso_debug_pub.g_debug_flag='Y' THEN
670 aso_debug_pub.add( 'Modifier Calculated operand'||ld_operand , 1,'Y' ) ;
671 end if;
672 --pp_debug( 'Modifier Adjustment exist ld_operand'||ld_operand);
673
674 p_index:=p_index+1;
675 -- Assigning the price adjustments for line id fetched
676 l_Price_Adjustment_Tbl(p_index):= ASO_UTILITY_PVT.Get_price_adj_Rec;
677 l_Price_Adjustment_Tbl(p_index).quote_header_id:=p_quote_header_id;
678 l_Price_Adjustment_Tbl(p_index).quote_line_id:=l_quote_line_id;
679 l_Price_Adjustment_Tbl(p_index).price_adjustment_id:= l_price_adjustment_id;
680 l_Price_Adjustment_Tbl(p_index).applied_flag:= 'Y';
681 l_Price_Adjustment_Tbl(p_index).updated_flag:= 'Y';
682 l_Price_Adjustment_Tbl(p_index).Operand := ld_operand;
683 l_Price_Adjustment_Tbl(p_index).adjusted_amount:=ld_operand;
684 l_Price_Adjustment_Tbl(p_index).Arithmetic_operator := 'AMT';
685 l_Price_Adjustment_Tbl(p_index).operation_code := 'UPDATE';
686 l_Price_Adjustment_Tbl(p_index).change_reason_code :='DEALS';
687 l_Price_Adjustment_Tbl(p_index).change_reason_text:='Recommendation from Deal Management';
688
689
690
691 end if; -- l_continue = 'Y' end
692
693 end if; -- price adjustment end
694
695 end loop;
696 close prcadj_cv;
697
698
699
700 l_control_rec.auto_version_flag := 'N';
701 l_control_rec.header_pricing_event := 'BATCH';
702 l_control_rec.calculate_tax_flag := 'Y';
703 l_control_rec.calculate_freight_charge_flag := 'Y';
704 l_control_rec.price_mode := 'ENTIRE_QUOTE';
705 l_control_rec.pricing_request_type:='ASO';
706
707
708
709
710 IF aso_debug_pub.g_debug_flag='Y' THEN
711 aso_debug_pub.add( 'Modifier Price adjustment'||l_Price_Adjustment_Tbl.count , 1,'Y' ) ;
712 end if;
713 --pp_debug('end approved event price'||l_Price_Adjustment_Tbl.count);
714 --pp_debug('end approved event price'||l_Qte_Line_Tbl.count);
715
716 ELSIF p_event = 'CANCELED' THEN
717 -- Set quote status to 'PRICE APPROVAL CANCELED'
718 open C_qte_status_id('PRICE APPROVAL CANCELED');
719 fetch C_qte_status_id into ln_quote_Status_id;
720 close C_qte_status_id;
721
722 -- Checking for the quote status transition
723 ASO_VALIDATE_PVT.Validate_Status_Transition(
724 p_init_msg_list => FND_API.G_FALSE,
725 p_source_status_id => l_quote_Status_id,
726 p_dest_status_id => ln_quote_Status_id,
727 x_return_status => x_return_status,
728 x_msg_count => x_msg_count,
729 x_msg_data => x_msg_data);
730
731 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
732 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
733 FND_MESSAGE.Set_Name('ASO', 'ASO_ERR_NO_QTE_STAT_TRANSITION');
734 FND_MSG_PUB.ADD;
735 END IF;
736 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
738 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
739 RAISE FND_API.G_EXC_ERROR;
740 END IF;
741 end if;
742
743 update aso_quote_headers_All
744 set last_update_date=l_last_update_date,quote_status_id=ln_quote_Status_id
745 where quote_header_id=p_quote_header_id;
746
747 END IF;
748
749 IF P_event in ('ACCEPTED') then
750 IF aso_debug_pub.g_debug_flag = 'Y' then
751 aso_debug_pub.add('ASO_DEAL_PUB before aso_quote_pub.update_quote - '||p_event, 1, 'Y');
752 aso_debug_pub.add('ASO_DEAL_PUB:l_qte_header_rec.quote_status_id - '||l_qte_header_rec.quote_status_id, 1, 'Y');
753 aso_debug_pub.ADD ('Before calling update quote: Setting the single org context to org_id: '|| l_qte_header_rec.org_id,1,'N');
754 END IF;
755
756 -- Setting MOAC
757 mo_global.set_policy_context('S', l_qte_header_rec.org_id);
758
759 aso_quote_pub.update_quote (
760 p_api_version_number => 1.0,
761 p_init_msg_list => fnd_api.g_false,
762 p_commit => fnd_api.g_false,
763 p_control_rec => l_control_rec,
764 p_qte_header_rec => l_qte_header_rec,
765 p_hd_price_attributes_tbl => aso_quote_pub.g_miss_price_attributes_tbl,
766 p_hd_payment_tbl => aso_quote_pub.g_miss_payment_tbl,
767 p_hd_shipment_tbl => aso_quote_pub.g_miss_shipment_tbl,
768 p_hd_freight_charge_tbl => aso_quote_pub.g_miss_freight_charge_tbl,
769 p_hd_tax_detail_tbl => aso_quote_pub.g_miss_tax_detail_tbl,
770 p_qte_line_tbl => l_qte_line_tbl, -- need to change here
771 p_qte_line_dtl_tbl => aso_quote_pub.g_miss_qte_line_dtl_tbl,
772 p_line_attr_ext_tbl => aso_quote_pub.g_miss_line_attribs_ext_tbl,
776 p_price_adj_rltship_tbl => aso_quote_pub.g_miss_price_adj_rltship_tbl,
773 p_line_rltship_tbl => aso_quote_pub.g_miss_line_rltship_tbl,
774 p_price_adjustment_tbl => l_Price_Adjustment_Tbl,
775 p_price_adj_attr_tbl => aso_quote_pub.g_miss_price_adj_attr_tbl,
777 p_ln_price_attributes_tbl => aso_quote_pub.g_miss_price_attributes_tbl,
778 p_ln_payment_tbl => l_ln_Payment_Tbl,
779 p_ln_shipment_tbl => l_ln_shipment_Tbl,
780 p_ln_freight_charge_tbl => aso_quote_pub.g_miss_freight_charge_tbl,
781 p_ln_tax_detail_tbl => aso_quote_pub.g_miss_tax_detail_tbl,
782 x_qte_header_rec => lx_qte_header_rec,
783 x_qte_line_tbl => lx_qte_line_tbl,
784 x_qte_line_dtl_tbl => lx_qte_line_dtl_tbl,
785 x_hd_price_attributes_tbl => lx_hd_price_attr_tbl,
786 x_hd_payment_tbl => lx_hd_payment_tbl,
787 x_hd_shipment_tbl => lx_hd_shipment_tbl,
788 x_hd_freight_charge_tbl => lx_hd_freight_charge_tbl,
789 x_hd_tax_detail_tbl => lx_hd_tax_detail_tbl,
790 x_line_attr_ext_tbl => lx_line_attr_ext_tbl,
791 x_line_rltship_tbl => lx_line_rltship_tbl,
792 x_price_adjustment_tbl => lx_price_adjustment_tbl,
793 x_price_adj_attr_tbl => lx_price_adj_attr_tbl,
794 x_price_adj_rltship_tbl => lx_price_adj_rltship_tbl,
795 x_ln_price_attributes_tbl => lx_ln_price_attr_tbl,
796 x_ln_payment_tbl => lx_ln_payment_tbl,
797 x_ln_shipment_tbl => lx_ln_shipment_tbl,
798 x_ln_freight_charge_tbl => lx_ln_freight_charge_tbl,
799 x_ln_tax_detail_tbl => lx_ln_tax_detail_tbl,
800 x_return_status => x_return_status,
801 x_msg_count => x_msg_count,
802 x_msg_data => x_msg_data
803 );
804
805 --pp_debug('after calling aso_quote_pub');
806 IF aso_debug_pub.g_debug_flag = 'Y' THEN
807 aso_debug_pub.add('ASO_DEAL_PUB: after Update_Quote', 1, 'Y');
808 END IF;
809
810 if x_return_status = FND_API.G_RET_STS_SUCCESS then
811 Update aso_quote_lines_all
812 set PRICING_LINE_TYPE_INDICATOR=NULL
813 where quote_header_id = p_quote_header_id
814 and PRICING_LINE_TYPE_INDICATOR='D';
815
816
817 elsIF x_return_status = FND_API.G_RET_STS_ERROR THEN
818 --pp_debug('after calling aso_quote_pub error');
819 RAISE FND_API.G_EXC_ERROR;
820 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
821 --pp_debug('after calling aso_quote_pub unerror');
822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
823 END IF;
824 END IF; -- end if status
825
826
827 -- Standard call to get message count and if count is 1, get message info.
828 FND_MSG_PUB.Count_And_Get
829 ( p_count => x_msg_count,
830 p_data => x_msg_data
831 );
832
833 EXCEPTION
834 WHEN FND_API.G_EXC_ERROR THEN
835 --pp_debug('error aso_quote_pub');
836
837
838 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
839 aso_debug_pub.add('Exception in package : '|| G_PKG_NAME, 1, 'N');
840 aso_debug_pub.add('Exception in API : '|| L_API_NAME, 1, 'N');
841 end if;
842
843 x_return_status := FND_API.G_RET_STS_ERROR;
844
845 FND_MSG_PUB.Count_And_Get
846 ( p_count => x_msg_count,
847 p_data => x_msg_data
848 );
849
850
851 ASO_UTILITY_PVT.Get_Messages(x_msg_count, x_msg_data);
852
853
854
855 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
856 --pp_debug('unerror aso_quote_pub'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
857
858 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
859 aso_debug_pub.add('Exception in package : '|| G_PKG_NAME, 1, 'N');
860 aso_debug_pub.add('Exception in API : '|| L_API_NAME, 1, 'N');
861 end if;
862
863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
864 FND_MSG_PUB.Count_And_Get
865 ( p_count => x_msg_count,
866 p_data => x_msg_data
867 );
868 ASO_UTILITY_PVT.Get_Messages(x_msg_count, x_msg_data);
869
870 WHEN OTHERS THEN
871 --pp_debug('error others aso_quote_pub'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
872 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
873 aso_debug_pub.add('Exception in package : '|| G_PKG_NAME, 1, 'N');
874 aso_debug_pub.add('Exception in API : '|| L_API_NAME, 1, 'N');
875 aso_debug_pub.add('SQLCODE : '|| SQLCODE, 1, 'N');
876 aso_debug_pub.add('SQLERRM : '|| SQLERRM, 1, 'N');
877 END IF;
878
879 FND_MESSAGE.Set_Name('ASO', 'ASO_ERROR_RETURNED');
880 FND_MESSAGE.Set_token('PKG_NAME' , g_pkg_name);
881 FND_MESSAGE.Set_token('API_NAME' , l_api_name);
882 FND_MSG_PUB.ADD;
883
884 l_len_sqlerrm := Length(SQLERRM) ;
885 While l_len_sqlerrm >= l_err Loop
886 FND_MESSAGE.Set_Name('ASO', 'ASO_SQLERRM');
887 FND_MESSAGE.Set_token('ERR_TEXT' , substr(SQLERRM,l_err,240));
888 l_err := l_err + 240;
889 FND_MSG_PUB.ADD;
890 end loop;
891
892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
893
894
895 FND_MSG_PUB.Count_And_Get
896 ( p_count => x_msg_count,
897 p_data => x_msg_data
898 );
899 ASO_UTILITY_PVT.Get_Messages(x_msg_count, x_msg_data);
900
901
902 END Update_Quote_From_Deal;
903
904
905
906 FUNCTION Get_Deal_Access
910 ) RETURN VARCHAR2
907 (
908 P_RESOURCE_ID IN NUMBER,
909 P_QUOTE_HEADER_ID IN NUMBER
911 IS
912
913 l_access_level VARCHAR2(10):='NONE';
914 l_max_version_flag VARCHAR2(1):=null;
915 l_price_request_id NUMBER := null;
916 l_pricing_status VARCHAR2(1) := null;
917 l_quote_number NUMBER;
918 l_quote_status_id NUMBER;
919 t_quote_status_id NUMBER;
920 t_update_allowed_flag VARCHAR2(1) :='N';
921 l_status_override VARCHAR2(1) := 'Y';
922
923 BEGIN
924
925 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
926
927 -- API body
928 IF aso_debug_pub.g_debug_flag = 'Y' THEN
929 aso_debug_pub.add('ASO_DEAL_PUB: ****** Start of Get_Deal_Access API ******', 1, 'Y');
930 END IF;
931
932 IF aso_debug_pub.g_debug_flag = 'Y' THEN
933 aso_debug_pub.add('ASO_DEAL_PUB: Get_Deal_Access: P_RESOURCE_ID: ' || P_RESOURCE_ID, 1, 'Y');
934 aso_debug_pub.add('ASO_DEAL_PUB: Get_Deal_Access: P_QUOTE_HEADER_ID: ' || P_QUOTE_HEADER_ID, 1, 'Y');
935 END IF;
936
937 -- Getting quote header Details
938 SELECT nvl(max_version_flag,'N'), price_request_id, nvl(pricing_status_indicator,'C'),quote_number,quote_status_id
939 into l_max_version_flag, l_price_request_id,l_pricing_status,l_quote_number,l_quote_status_id
940 FROM aso_quote_headers_all
941 WHERE quote_header_id = p_quote_header_id;
942
943 -- Getting the user level access
944 l_access_level:=ASO_SECURITY_INT.Get_Quote_Access (p_resource_id,l_quote_number);
945
946 aso_debug_pub.add('ASO_DEAL_PUB: Get_Deal_Access: Access level returned by ASO_SECURITY_INT: ' || l_access_level, 1, 'Y');
947
948 -- User has update access to quote. In case user has access level 'READ' or 'NONE' for the quote nothing needs to be done
949 -- check additional conditions for max version, batch pricing, status
950 IF l_access_level = 'UPDATE' then
951
952 SELECT quote_status_id INTO t_quote_status_id
953 FROM aso_quote_statuses_b
954 WHERE status_code = 'PRICE APPROVAL PENDING';
955
956
957 if l_max_version_flag='N' then -- checking for max version
958 l_access_level:='READ';
959 elsif (t_quote_status_id<>l_quote_status_id) then
960
961 SELECT update_allowed_flag
962 INTO t_update_allowed_flag
963 FROM aso_quote_statuses_b
964 WHERE quote_status_id = l_quote_status_id;
965
966 l_status_override :=nvl(fnd_profile.value('ASO_STATUS_OVERRIDE'),'N');
967
968 If (t_update_allowed_flag='N' and l_status_override = 'N') then
969 l_access_level:='READ';
970 end if;
971
972 elsif l_price_request_id is not null then -- checking for batch pricing
973 l_access_level:= 'READ';
974 end if;
975 END IF;
976 -- End of API body
977 IF aso_debug_pub.g_debug_flag = 'Y' THEN
978 aso_debug_pub.add('ASO_DEAL_PUB: Get_Deal_Access: End of API body', 1, 'Y');
979 END IF;
980 RETURN l_access_level;
981
982 END Get_Deal_Access;
983
984
985 FUNCTION Get_Deal_Enable_Buttons
986 (
987 P_RESOURCE_ID IN NUMBER,
988 P_QUOTE_HEADER_ID IN NUMBER
989 ) RETURN VARCHAR2
990 IS
991
992 l_access_level VARCHAR2(10):='NONE';
993 l_max_version_flag VARCHAR2(1):=null;
994 l_price_request_id NUMBER := null;
995 l_pricing_status VARCHAR2(1) := null;
996 l_quote_number NUMBER;
997
998 BEGIN
999
1000 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
1001
1002 -- API body
1003 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1004 aso_debug_pub.add('ASO_DEAL_PUB: ****** Start of Get_Deal_Enable_Buttons API ******', 1, 'Y');
1005 END IF;
1006
1007 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1008 aso_debug_pub.add('ASO_DEAL_PUB: Get_Deal_Enable_Buttons: P_RESOURCE_ID: ' || P_RESOURCE_ID, 1, 'Y');
1009 aso_debug_pub.add('ASO_DEAL_PUB: Get_Deal_Enable_Buttons: P_QUOTE_HEADER_ID: ' || P_QUOTE_HEADER_ID, 1, 'Y');
1010 END IF;
1011
1012 -- Getting quote header Details
1013 SELECT nvl(max_version_flag,'N'), price_request_id, nvl(pricing_status_indicator,'C'),quote_number
1014 into l_max_version_flag, l_price_request_id,l_pricing_status,l_quote_number
1015 FROM aso_quote_headers_all
1016 WHERE quote_header_id = p_quote_header_id;
1017
1018 -- Getting the user level access
1019 l_access_level:=ASO_SECURITY_INT.Get_Quote_Access (p_resource_id,l_quote_number);
1020
1021 aso_debug_pub.add('ASO_DEAL_PUB: Get_Deal_Enable_Buttons: Access level returned by ASO_SECURITY_INT: ' || l_access_level, 1, 'Y');
1022
1023 -- User has update access to quote. In case user has access level 'READ' or 'NONE' for the quote nothing needs to be done
1024 -- check additional conditions for max version, batch pricing, status
1025 IF l_access_level = 'UPDATE' then
1026
1027
1028 if l_max_version_flag='N' then -- checking for max version
1029 l_access_level:='READ';
1030
1031 elsif l_price_request_id is not null then -- checking for batch pricing
1032 l_access_level:= 'READ';
1033 end if;
1034 END IF;
1035
1036 aso_debug_pub.add('ASO_DEAL_PUB: Get_Deal_Enable_Buttons: l_access_level: ' || l_access_level, 1, 'Y');
1037
1038 -- End of API body
1039 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1040 aso_debug_pub.add('ASO_DEAL_PUB: Get_Deal_Enable_Buttons: End of API body', 1, 'Y');
1041 END IF;
1042
1043
1044 if l_access_level = 'UPDATE' then
1045 return 'Y';
1046 else
1047 return 'N';
1048 end if;
1049
1050
1051
1052 END Get_Deal_Enable_Buttons;
1053
1054 End ASO_DEAL_PUB;