[Home] [Help]
PACKAGE BODY: APPS.ASO_PRICING_INT
Source
1 PACKAGE BODY ASO_PRICING_INT AS
2 /* $Header: asoiprcb.pls 120.6 2008/01/09 10:36:12 rassharm ship $ */
3 -- Start of Comments
4 -- Package name : ASO_PRICING_INT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_PRICING_INT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoiprcb.pls';
13
14
15 FUNCTION Set_Global_Rec (
16 p_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type,
17 p_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type)
18 RETURN PRICING_HEADER_REC_TYPE
19 IS
20 BEGIN
21
22 return ASO_PRICING_CORE_PVT.Set_Global_Rec(p_qte_header_rec => p_qte_header_rec,
23 p_shipment_rec => p_shipment_rec);
24 END Set_Global_Rec;
25
26 FUNCTION Set_Global_Rec (
27 p_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type,
28 p_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type,
29 p_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type)
30 RETURN PRICING_LINE_REC_TYPE
31 IS
32 BEGIN
33
34 return ASO_PRICING_CORE_PVT.Set_Global_Rec(p_qte_line_rec => p_qte_line_rec,
35 p_qte_line_dtl_rec => p_qte_line_dtl_rec,
36 p_shipment_rec => p_shipment_rec);
37 END Set_Global_Rec;
38
39 -- kchervel start
40 FUNCTION Get_Cust_Acct (p_quote_header_id NUMBER)
41 RETURN NUMBER
42 IS
43 x_cust_account_id NUMBER;
44 BEGIN
45
46 SELECT cust_account_id
47 INTO x_cust_account_id
48 FROM aso_quote_headers_all
49 WHERE quote_header_id = p_quote_header_id;
50
51 IF (SQL%NOTFOUND) THEN
52 null;
53 x_cust_account_id := null;
54 END IF;
55
56 return x_cust_account_id;
57
58 END Get_Cust_Acct;
59
60 -- the following four APIs actually create the site use if needed
61 -- this should be changed in the party int
62
63 FUNCTION Get_Ship_to_Site_Use (p_quote_header_id NUMBER)
64 RETURN NUMBER
65 IS
66 x_ship_to_org_id NUMBER := NULL;
67 l_cust_account_id NUMBER;
68 l_ship_party_site_id NUMBER;
69 l_return_status VARCHAR2(1);
70 l_msg_count NUMBER;
71 l_msg_data NUMBER;
72 l_ship_to_cust_account_id NUMBER;
73
74 CURSOR C_get_quote_info (l_quote_header_id NUMBER) IS
75 SELECT qh.cust_account_id, qs.ship_to_party_site_id,qs.ship_to_cust_account_id
76 FROM aso_quote_headers_all qh, aso_shipments qs
77 WHERE qh.quote_header_id = qs.quote_header_id
78 AND qh.quote_header_id = l_quote_header_id
79 AND qs.quote_line_id is NULL;
80
81 BEGIN
82
83 OPEN C_get_quote_info(p_quote_header_id);
84 FETCH C_get_quote_info INTO l_cust_account_id, l_ship_party_site_id,l_ship_to_cust_account_id;
85 IF (C_get_quote_info%NOTFOUND) THEN
86 return x_ship_to_org_id;
87 END IF;
88 CLOSE C_get_quote_info;
89
90 if l_ship_to_cust_account_id is not null OR l_ship_to_cust_account_id <> fnd_api.G_MISS_NUM then
91 l_cust_account_id := l_ship_to_cust_account_id;
92 end if;
93
94 IF l_cust_account_id is not NULL
95 AND l_ship_party_site_id is not NULL THEN
96 ASO_PARTY_INT.GET_ACCT_SITE_USES (
97 p_api_version => 1.0
98 ,P_Cust_Account_Id => l_cust_account_id
99 ,P_Party_Site_Id => l_ship_party_site_id
100 ,P_Acct_Site_type => 'SHIP_TO'
101 ,x_return_status => l_return_status
102 ,x_msg_count => l_msg_count
103 ,x_msg_data => l_msg_data
104 ,x_site_use_id => x_ship_to_org_id
105 );
106 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
107 x_ship_to_org_id := NULL;
108 END IF;
109
110 END IF; -- not null
111
112 return x_ship_to_org_id;
113
114 END Get_Ship_to_Site_Use;
115
116 FUNCTION Get_Line_Ship_to_Site_Use (p_quote_line_id NUMBER)
117 RETURN NUMBER
118 IS
119 x_ship_to_org_id NUMBER := NULL;
120 l_cust_account_id NUMBER;
121 l_ship_party_site_id NUMBER;
122 l_return_status VARCHAR2(1);
123 l_msg_count NUMBER;
124 l_msg_data NUMBER;
125 l_ship_to_cust_account_id NUMBER;
126
127 CURSOR C_get_quote_info (l_quote_line_id NUMBER) IS
128 SELECT qh.cust_account_id, qs.ship_to_party_site_id,qs.ship_to_cust_account_id
129 FROM aso_quote_headers_all qh,
130 aso_shipments qs,
131 aso_quote_lines_all ql
132 WHERE qh.quote_header_id = qs.quote_header_id
133 AND ql.quote_header_id = qh.quote_header_id
134 AND ql.quote_line_id = l_quote_line_id
135 AND ql.quote_line_id = qs.quote_line_id;
136
137 BEGIN
138
139 OPEN C_get_quote_info(p_quote_line_id);
140 FETCH C_get_quote_info INTO l_cust_account_id, l_ship_party_site_id,l_ship_to_cust_account_id;
141 IF (C_get_quote_info%NOTFOUND) THEN
142 return x_ship_to_org_id;
143 END IF;
144 CLOSE C_get_quote_info;
145
146 if l_ship_to_cust_account_id is not null
147 OR l_ship_to_cust_account_id <> fnd_api.G_MISS_NUM then
148 l_cust_account_id := l_ship_to_cust_account_id;
149 end if;
150
151 IF l_cust_account_id is not NULL
152 AND l_ship_party_site_id is not NULL THEN
153 ASO_PARTY_INT.GET_ACCT_SITE_USES (
154 p_api_version => 1.0
155 ,P_Cust_Account_Id => l_cust_account_id
156 ,P_Party_Site_Id => l_ship_party_site_id
157 ,P_Acct_Site_type => 'SHIP_TO'
158 ,x_return_status => l_return_status
159 ,x_msg_count => l_msg_count
160 ,x_msg_data => l_msg_data
161 ,x_site_use_id => x_ship_to_org_id
162 );
163 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
164 x_ship_to_org_id := NULL;
165 END IF;
166
167 END IF; -- not null
168
169 return x_ship_to_org_id;
170
171 END Get_Line_Ship_to_Site_Use;
172
173
174 FUNCTION Get_Invoice_to_Site_Use (p_quote_header_id NUMBER)
175 RETURN NUMBER
176 IS
177 x_invoice_to_org_id NUMBER := NULL;
178 l_cust_account_id NUMBER;
179 l_invoice_party_site_id NUMBER;
180 l_return_status VARCHAR2(1);
181 l_msg_count NUMBER;
182 l_msg_data NUMBER;
183 l_invoice_to_cust_account_id NUMBER;
184
185 CURSOR C_get_quote_info (l_quote_header_id NUMBER) IS
186 SELECT cust_account_id,
187 invoice_to_party_site_id,
188 invoice_to_cust_account_id
189 FROM aso_quote_headers_all
190 WHERE quote_header_id = l_quote_header_id;
191
192 BEGIN
193
194 OPEN C_get_quote_info(p_quote_header_id);
195 FETCH C_get_quote_info INTO
196 l_cust_account_id,
197 l_invoice_party_site_id,
198 l_invoice_to_cust_account_id;
199 IF (C_get_quote_info%NOTFOUND) THEN
200 return x_invoice_to_org_id;
201 END IF;
202 CLOSE C_get_quote_info;
203
204 if l_invoice_to_cust_account_id is not null
205 OR l_invoice_to_cust_account_id <> fnd_api.G_MISS_NUM then
206 l_cust_account_id := l_invoice_to_cust_account_id;
207 end if;
208
209 IF l_cust_account_id is not NULL
210 AND l_invoice_party_site_id is not NULL THEN
211 ASO_PARTY_INT.GET_ACCT_SITE_USES (
212 p_api_version => 1.0
213 ,P_Cust_Account_Id => l_cust_account_id
214 ,P_Party_Site_Id => l_invoice_party_site_id
215 ,P_Acct_Site_type => 'BILL_TO'
216 ,x_return_status => l_return_status
217 ,x_msg_count => l_msg_count
218 ,x_msg_data => l_msg_data
219 ,x_site_use_id => x_invoice_to_org_id
220 );
221 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
222 x_invoice_to_org_id := NULL;
223 END IF;
224
225 END IF; -- not null
226
227 return x_invoice_to_org_id;
228
229 END Get_Invoice_to_Site_Use;
230
231
232 FUNCTION Get_Line_Invoice_Site_Use (p_quote_line_id NUMBER)
233 RETURN NUMBER
234 IS
235 x_invoice_to_org_id NUMBER := NULL;
236 l_cust_account_id NUMBER;
237 l_invoice_party_site_id NUMBER;
238 l_return_status VARCHAR2(1);
239 l_msg_count NUMBER;
240 l_msg_data NUMBER;
241 l_invoice_to_cust_account_id NUMBER;
242
243 CURSOR C_get_quote_info (l_quote_line_id NUMBER) IS
244 SELECT qh.cust_account_id,
245 ql.invoice_to_party_site_id,
246 ql.invoice_to_cust_account_id
247 FROM aso_quote_headers_all qh, aso_quote_lines_all ql
248 WHERE ql.quote_line_id = l_quote_line_id
249 AND ql.quote_header_id = qh.quote_header_id;
250
251 BEGIN
252
253 OPEN C_get_quote_info(p_quote_line_id);
254 FETCH C_get_quote_info
255 INTO l_cust_account_id,l_invoice_party_site_id,l_invoice_to_cust_account_id;
256 IF (C_get_quote_info%NOTFOUND) THEN
257 return x_invoice_to_org_id;
258 END IF;
259 CLOSE C_get_quote_info;
260
261 if l_invoice_to_cust_account_id is not null
262 OR l_invoice_to_cust_account_id <> fnd_api.G_MISS_NUM then
263 l_cust_account_id := l_invoice_to_cust_account_id;
264 end if;
265
266 IF l_cust_account_id is not NULL
267 AND l_invoice_party_site_id is not NULL THEN
268 ASO_PARTY_INT.GET_ACCT_SITE_USES (
269 p_api_version => 1.0
270 ,P_Cust_Account_Id => l_cust_account_id
271 ,P_Party_Site_Id => l_invoice_party_site_id
272 ,P_Acct_Site_type => 'BILL_TO'
273 ,x_return_status => l_return_status
274 ,x_msg_count => l_msg_count
275 ,x_msg_data => l_msg_data
276 ,x_site_use_id => x_invoice_to_org_id
277 );
278 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
279 x_invoice_to_org_id := NULL;
280 END IF;
281
282 END IF; -- not null
283
284 return x_invoice_to_org_id;
285
286 END Get_Line_Invoice_Site_Use;
287
288
289 FUNCTION Get_Ship_to_Party_Site (p_quote_header_id NUMBER)
290 RETURN NUMBER
291 IS
292 CURSOR C_get_quote_info (l_quote_header_id NUMBER) IS
293 SELECT qs.ship_to_party_site_id
294 FROM aso_shipments qs
295 WHERE qs.quote_header_id = l_quote_header_id
296 AND qs.quote_line_id IS NULL;
297
298 x_ship_party_site_id NUMBER := NULL;
299
300 BEGIN
301
302 OPEN C_get_quote_info(p_quote_header_id);
303 FETCH C_get_quote_info INTO x_ship_party_site_id;
304 IF (C_get_quote_info%NOTFOUND) THEN
305 return x_ship_party_site_id;
306 END IF;
307 CLOSE C_get_quote_info;
308
309 return x_ship_party_site_id;
310 END Get_Ship_to_Party_Site;
311
312
313
314 FUNCTION Get_Line_Ship_Party_Site (p_quote_line_id NUMBER)
315 RETURN NUMBER
316 IS
317 CURSOR C_get_quote_info (l_quote_line_id NUMBER) IS
318 SELECT qs.ship_to_party_site_id
319 FROM aso_shipments qs
320 WHERE qs.quote_line_id = l_quote_line_id;
321
322 x_ship_party_site_id NUMBER;
323
324 BEGIN
325
326 OPEN C_get_quote_info(p_quote_line_id);
327 FETCH C_get_quote_info INTO x_ship_party_site_id;
328 IF (C_get_quote_info%NOTFOUND) THEN
329 return x_ship_party_site_id;
330 END IF;
331 CLOSE C_get_quote_info;
332
333 return x_ship_party_site_id;
334 END Get_Line_Ship_Party_Site;
335
336
337 FUNCTION Get_Invoice_to_Party_Site (p_quote_header_id NUMBER)
338 RETURN NUMBER
339 IS
340 CURSOR C_get_quote_info (l_quote_header_id NUMBER) IS
341 SELECT qs.invoice_to_party_site_id
342 FROM aso_quote_headers_all qs
343 WHERE qs.quote_header_id = l_quote_header_id;
344
345 x_invoice_party_site_id NUMBER := NULL;
346
347 BEGIN
348
349 OPEN C_get_quote_info(p_quote_header_id);
350 FETCH C_get_quote_info INTO x_invoice_party_site_id;
351 IF (C_get_quote_info%NOTFOUND) THEN
352 return x_invoice_party_site_id;
353 END IF;
354 CLOSE C_get_quote_info;
355
356 return x_invoice_party_site_id;
357 END Get_Invoice_to_Party_Site;
358
359
360
361 FUNCTION Get_Line_Invoice_Party_Site (p_quote_line_id NUMBER)
362 RETURN NUMBER
363 IS
364 CURSOR C_get_quote_info (l_quote_line_id NUMBER) IS
365 SELECT qs.invoice_to_party_site_id
366 FROM aso_quote_lines_all qs
367 WHERE qs.quote_line_id = l_quote_line_id;
368
369 xl_inv_party_site_id NUMBER;
370
371 BEGIN
372
373 OPEN C_get_quote_info(p_quote_line_id);
374 FETCH C_get_quote_info INTO xl_inv_party_site_id;
375 IF (C_get_quote_info%NOTFOUND) THEN
376 return xl_inv_party_site_id;
377 END IF;
378 CLOSE C_get_quote_info;
379
380 return xl_inv_party_site_id;
381 END Get_Line_Invoice_Party_Site;
382
383
384 -- wli_start
385 FUNCTION Get_Customer_Class(p_cust_account_id IN NUMBER)
386 RETURN VARCHAR2
387 IS
388 x_class_code VARCHAR2(240);
389 BEGIN
390
391 SELECT customer_class_code
392 INTO x_class_code
393 FROM hz_cust_accounts
394 WHERE cust_account_id = p_cust_account_id;
395
396 RETURN x_class_code;
397
398 END Get_Customer_Class;
399
400 FUNCTION Get_Account_Type (p_cust_account_id IN NUMBER)
401 RETURN QP_Attr_Mapping_PUB.t_MultiRecord
402 IS
403
404 TYPE t_cursor IS REF CURSOR;
405
406 x_account_type_ids QP_Attr_Mapping_PUB.t_MultiRecord;
407 l_account_type_id VARCHAR2(30);
408 v_count NUMBER := 1;
409 l_acct_type_cursor t_cursor;
410 BEGIN
411
412 OPEN l_acct_type_cursor FOR
413 SELECT profile_class_id
414 FROM HZ_CUSTOMER_PROFILES
415 WHERE cust_account_id = p_cust_account_id;
416
417 LOOP
418
419 FETCH l_acct_type_cursor INTO l_account_type_id;
420 EXIT WHEN l_acct_type_cursor%NOTFOUND;
421
422 x_account_type_ids(v_count) := l_account_type_id;
423 v_count := v_count + 1;
424
425 END LOOP;
426
427 CLOSE l_acct_type_cursor;
428
429 RETURN x_account_type_ids;
430
431 END Get_Account_Type;
432
433 FUNCTION Get_Sales_Channel (p_cust_account_id IN NUMBER)
434 RETURN VARCHAR2
435 IS
436 x_sales_channel_code VARCHAR2(240);
437 BEGIN
438
439 SELECT sales_channel_code
440 INTO x_sales_channel_code
441 FROM hz_cust_accounts
442 WHERE cust_account_id = p_cust_account_id;
443
444 RETURN x_sales_channel_code;
445
446
447 END Get_Sales_Channel;
448
449
450 FUNCTION Get_GSA (p_cust_account_id NUMBER)
451 RETURN VARCHAR2
452 IS
453 x_gsa VARCHAR2(1);
454 BEGIN
455
456 SELECT DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION',PARTY.GSA_INDICATOR_FLAG,'N') gsa_indicator
457 INTO x_gsa
458 FROM hz_cust_accounts cust_acct, hz_parties party
459 WHERE cust_acct.cust_account_id = p_cust_account_id
460 and CUST_ACCT.PARTY_ID = PARTY.PARTY_ID;
461
462 RETURN x_gsa;
463
464 END get_gsa;
465
466 -- Why do we need
467
468 FUNCTION Get_quote_Qty (p_qte_header_id IN NUMBER)
469 RETURN VARCHAR2
470 IS
471
472 x_quote_qty VARCHAR2(30);
473 l_quote_qty NUMBER;
474
475 BEGIN
476
477 SELECT SUM(nvl(quantity,0))
478 INTO l_quote_qty
479 FROM aso_quote_lines_all
480 WHERE quote_header_id=p_qte_header_id
481 AND (line_category_code<>'RETURN' OR line_category_code IS NULL)
482 GROUP BY quote_header_id;
483
484
485 IF (SQL%NOTFOUND) THEN
486 l_quote_qty :=0;
487 end if;
488
489 x_quote_qty := FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_quote_qty, 0));
490 RETURN x_quote_qty;
491
492 END Get_quote_Qty;
493
494
495 FUNCTION Get_quote_Amount(p_qte_header_id IN NUMBER) RETURN VARCHAR2
496 IS
497 x_quote_amount VARCHAR2(30);
498 l_quote_amount NUMBER;
499
500 BEGIN
501
502 SELECT SUM((nvl(quantity,0))*(LINE_LIST_PRICE))
503 INTO l_quote_amount
504 FROM aso_quote_lines_all
505 WHERE quote_header_id=p_qte_header_id
506 AND (line_category_code<>'RETURN' OR line_category_code IS NULL)
507 AND charge_periodicity_code IS NULL
508 GROUP BY quote_header_id;
509
510
511 IF (SQL%NOTFOUND) THEN
512 l_quote_amount :=0;
513 END IF;
514
515 x_quote_amount:=FND_NUMBER.NUMBER_TO_CANONICAL(NVL(l_quote_amount,0));
516 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
517 aso_debug_pub.add('ASO_PRICING_INT:In Get_quote_amount: x_quote_amount:'||x_quote_amount,1,'Y');
518 END IF;
519 RETURN x_quote_amount;
520
521 END Get_quote_Amount;
522
523 -- order context
524
525 FUNCTION Get_shippable_flag(p_qte_line_id NUMBER)
526 RETURN VARCHAR2
527 IS
528 x_shippable_item_flag VARCHAR2(1);
529 BEGIN
530
531 SELECT shippable_item_flag
532 INTO x_shippable_item_flag
533 FROM aso_i_items_v i, aso_quote_lines_all l
534 WHERE l.quote_line_id = p_qte_line_id
535 and l.inventory_item_id = i.inventory_item_id
536 and l.organization_id = i.organization_id;
537
538 RETURN x_shippable_item_flag;
539 END get_shippable_flag;
540
541 --wli_end
542 PROCEDURE Pricing_Item (
543 P_Api_Version_Number IN NUMBER,
544 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
545 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
546 p_control_rec IN PRICING_CONTROL_REC_TYPE,
547 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
548 p_hd_shipment_rec IN ASO_QUOTE_PUB.Shipment_Rec_Type
549 := ASO_QUOTE_PUB.G_Miss_Shipment_Rec,
550 p_hd_price_attr_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
551 := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl,
552 p_qte_line_rec IN ASO_QUOTE_PUB.Qte_Line_Rec_Type,
553 p_qte_line_dtl_rec IN ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type
554 := ASO_QUOTE_PUB.G_Miss_Qte_Line_Dtl_Rec,
555 p_ln_shipment_rec IN ASO_QUOTE_PUB.Shipment_Rec_Type
556 := ASO_QUOTE_PUB.G_Miss_Shipment_Rec,
557 p_ln_price_attr_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
558 := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl,
559 x_qte_line_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
560 x_qte_line_dtl_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type,
561 x_price_adj_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Price_Adj_Tbl_Type,
562 x_price_adj_attr_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type,
563 x_price_adj_rltship_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type,
564 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
565 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
566 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
567 IS
568 l_api_name CONSTANT VARCHAR2(30) := 'Pricing_Item';
569 l_api_version_number CONSTANT NUMBER := 1.0;
570 l_request_type VARCHAR2(60);
571 l_pricing_event VARCHAR2(30);
572 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
573 l_req_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
574 l_Req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
575 l_Req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
576 l_Req_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
577 l_req_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
578 l_req_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
579 l_req_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
580 l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
581 l_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
582 lx_req_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
583 lx_req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
584 lx_req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
585 lx_req_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
586 lx_req_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
587 lx_req_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
588 lx_req_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
589 l_return_status VARCHAR2(1);
590 l_return_status_text VARCHAR2(2000);
591 l_message_text VARCHAR2(2000);
592 lx_req_line_rec QP_PREQ_GRP.LINE_REC_TYPE;
593 lv_return_status VARCHAR2(1);
594 i BINARY_INTEGER;
595 l_hd_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
596 l_hd_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
597
598 BEGIN
599 -- Standard Start of API savepoint
600 SAVEPOINT PRICING_ITEM_PVT;
601
602 -- Standard call to check for call compatibility.
603 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
604 p_api_version_number,
605 l_api_name,
606 G_PKG_NAME)
607 THEN
608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
609 END IF;
610
611 -- Initialize message list if p_init_msg_list is set to TRUE.
612 IF FND_API.to_Boolean(p_init_msg_list)
613 THEN
614 FND_MSG_PUB.initialize;
615 END IF;
616
617 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
618
619 x_return_status := FND_API.G_RET_STS_SUCCESS;
620 l_request_type := NVL(p_control_rec.request_type,'ASO');
621 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
622 aso_debug_pub.add('ASO_PRICING_INT:In Pricing Item: p_qte_header_rec.quote_status_id'
623 ||p_qte_header_rec.quote_status_id,1,'Y');
624 END IF;
625 -- Bug No 6510202. Header rec needs to be intialized since the header rec frozen date is passed
626 -- to line rec price effective date which would be picked by pricing for selecting the correct price list
627 ASO_PRICING_INT.G_HEADER_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
628 p_qte_header_rec => p_qte_header_rec,
629 p_shipment_rec => p_hd_shipment_rec);
630
631 IF NVL(p_qte_header_rec.quote_status_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
632 l_pricing_event := 'BATCH'; --p_control_rec.pricing_event;
633 -- commented for bug no 6510202
634 /* ASO_PRICING_INT.G_HEADER_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
635 p_qte_header_rec => p_qte_header_rec,
636 p_shipment_rec => p_hd_shipment_rec);
637 */
638 QP_ATTR_MAPPING_PUB.Build_Contexts (
639 P_REQUEST_TYPE_CODE => l_request_type,
640 P_PRICING_TYPE => 'H',
641 X_PRICE_CONTEXTS_RESULT_TBL => l_hd_pricing_contexts_tbl,
642 X_QUAL_CONTEXTS_RESULT_TBL => l_hd_qual_contexts_tbl);
643
644 ASO_PRICING_CALLBACK_PVT.Copy_Attribs_To_Req (
645 p_line_index => 1,
646 p_pricing_contexts_tbl => l_hd_pricing_contexts_tbl,
647 p_qualifier_contexts_tbl => l_hd_qual_contexts_tbl,
648 px_req_line_attr_tbl => l_req_line_attr_tbl,
649 px_req_qual_tbl => l_req_qual_tbl);
650
651 ASO_PRICING_CALLBACK_PVT.Copy_Header_To_Request(
652 p_Request_Type => l_request_type,
653 p_pricing_event => l_pricing_event,
654 p_header_rec => p_qte_header_rec,
655 px_req_line_tbl => l_Req_line_tbl);
656
657 ASO_PRICING_INT.G_LINE_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec(
658 p_qte_line_rec => p_qte_line_rec,
659 p_qte_line_dtl_rec => p_qte_line_dtl_rec,
660 p_shipment_rec => p_ln_shipment_rec);
661
662 QP_ATTR_MAPPING_PUB.Build_Contexts (
663 P_REQUEST_TYPE_CODE => l_request_type,
664 P_PRICING_TYPE => 'L',
665 X_PRICE_CONTEXTS_RESULT_TBL => l_pricing_contexts_tbl,
666 X_QUAL_CONTEXTS_RESULT_TBL => l_qual_contexts_tbl);
667
668 ASO_PRICING_CALLBACK_PVT.Copy_Attribs_To_Req (
669 p_line_index => 1+1,
670 p_pricing_contexts_tbl => l_pricing_contexts_tbl,
671 p_qualifier_contexts_tbl => l_qual_contexts_tbl,
672 px_req_line_attr_tbl => l_req_line_attr_tbl,
673 px_req_qual_tbl => l_req_qual_tbl);
674
675 ASO_PRICING_CALLBACK_PVT.Copy_hdr_attr_to_line (
676 p_line_index => 1+1,
677 p_pricing_contexts_tbl => l_hd_pricing_contexts_tbl,
678 p_qualifier_contexts_tbl=> l_hd_qual_contexts_tbl,
679 px_req_line_attr_tbl => l_req_line_attr_tbl,
680 px_req_qual_tbl => l_req_qual_tbl);
681
682 ASO_PRICING_CALLBACK_PVT.Append_asked_for(
683 p_header_id => p_qte_header_rec.quote_header_id
684 ,p_line_id => p_qte_line_rec.quote_line_id
685 ,p_line_index => 1
686 ,px_Req_line_attr_tbl => l_Req_line_attr_tbl
687 ,px_Req_qual_tbl => l_Req_qual_tbl);
688
689 ASO_PRICING_CALLBACK_PVT.Copy_Line_To_Request(
690 p_Request_Type => l_request_type,
691 p_pricing_event => l_pricing_event,
692 p_line_rec => p_qte_line_rec,
693 p_line_dtl_rec => p_qte_line_dtl_rec,
694 p_control_rec => p_control_rec,
695 px_req_line_tbl => l_Req_line_tbl);
696
697 l_control_rec.pricing_event := l_pricing_event;
698 l_control_rec.calculate_flag := p_control_rec.calculate_flag;
699 l_control_rec.simulation_flag := p_control_rec.simulation_flag;
700 l_control_rec.source_order_amount_flag := 'Y';
701 l_control_rec.TEMP_TABLE_INSERT_FLAG := 'Y';
702 l_control_rec.GSA_CHECK_FLAG := 'Y';
703 l_control_rec.GSA_DUP_CHECK_FLAG := 'Y';
704 ELSE
705 l_pricing_event := NVL(p_control_rec.pricing_event,'LINE');
706 ASO_PRICING_INT.G_LINE_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
707 p_qte_line_rec => p_qte_line_rec,
708 p_qte_line_dtl_rec => p_qte_line_dtl_rec,
709 p_shipment_rec => p_ln_shipment_rec);
710
711 QP_ATTR_MAPPING_PUB.Build_Contexts (
712 P_REQUEST_TYPE_CODE => l_request_type,
713 P_PRICING_TYPE => 'L',
714 X_PRICE_CONTEXTS_RESULT_TBL => l_pricing_contexts_tbl,
715 X_QUAL_CONTEXTS_RESULT_TBL => l_qual_contexts_tbl);
716
717 ASO_PRICING_CALLBACK_PVT.Copy_Attribs_To_Req (
718 p_line_index => 1,
719 p_pricing_contexts_tbl => l_pricing_contexts_tbl,
720 p_qualifier_contexts_tbl => l_qual_contexts_tbl,
721 px_req_line_attr_tbl => l_req_line_attr_tbl,
722 px_req_qual_tbl => l_req_qual_tbl);
723 /************************************************************
724 ASO_PRICING_CALLBACK_PVT.Append_asked_for(
725 p_header_id => p_qte_header_rec.quote_header_id
726 ,p_line_id => p_qte_line_rec.quote_line_id
727 ,p_line_index => 1
728 ,px_Req_line_attr_tbl => l_Req_line_attr_tbl
729 ,px_Req_qual_tbl => l_Req_qual_tbl);
730 ********************************************************/
731 ASO_PRICING_CALLBACK_PVT.Append_asked_for(
732 p_line_index => 1,
733 p_pricing_attr_tbl => p_ln_price_attr_tbl,
734 px_Req_line_attr_tbl => l_Req_line_attr_tbl,
735 px_Req_qual_tbl => l_Req_qual_tbl);
736
737 ASO_PRICING_CALLBACK_PVT.Copy_Line_To_Request(
738 p_Request_Type => l_request_type,
739 p_pricing_event => l_pricing_event,
740 p_line_rec => p_qte_line_rec,
741 p_line_dtl_rec => p_qte_line_dtl_rec,
742 p_control_rec => p_control_rec,
743 px_req_line_tbl => l_Req_line_tbl);
744
745 l_control_rec.pricing_event := l_pricing_event;
746 l_control_rec.calculate_flag := p_control_rec.calculate_flag;
747 l_control_rec.simulation_flag := p_control_rec.simulation_flag;
748 l_control_rec.source_order_amount_flag := 'Y';
749 l_control_rec.TEMP_TABLE_INSERT_FLAG := 'Y';
750 l_control_rec.GSA_CHECK_FLAG := 'Y';
751 l_control_rec.GSA_DUP_CHECK_FLAG := 'Y';
752 END IF;
753 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
754 aso_debug_pub.add('ASO_PRICING_INT:In Pricing Item: l_control_rec.pricing_event'
755 ||l_control_rec.pricing_event,1,'Y');
756 aso_debug_pub.add('ASO_PRICING_INT:In Pricing Item: l_request_type'||l_request_type,1,'Y');
757 END IF;
758
759
760 /* Change for populating QP_PREQ_GRP.CONTROL_RECORD_TYPE.ORG_ID Yogeshwar (MOAC) */
761
762 IF ((p_qte_header_rec.org_id IS NULL) OR (p_qte_header_rec.org_id = FND_API.G_MISS_NUM)) THEN
763 IF fnd_msg_pub.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
764 FND_MESSAGE.Set_Name('ASO', 'ASO_MISSING_OU');
765 FND_MSG_PUB.ADD;
766 END IF;
767
768 RAISE FND_API.G_EXC_ERROR;
769 END IF;
770
771 l_control_rec.ORG_ID := p_qte_header_rec.org_id;
772
773 /* End of Change (MOAC) */
774
775
776 QP_PREQ_PUB.PRICE_REQUEST
777 (p_control_rec => l_control_rec,
778 p_line_tbl => l_Req_line_tbl,
779 p_qual_tbl => l_Req_qual_tbl,
780 p_line_attr_tbl => l_Req_line_attr_tbl,
781 p_line_detail_tbl => l_req_line_detail_tbl,
782 p_line_detail_qual_tbl => l_req_line_detail_qual_tbl,
783 p_line_detail_attr_tbl => l_req_line_detail_attr_tbl,
784 p_related_lines_tbl => l_req_related_lines_tbl,
785 x_line_tbl => lx_req_line_tbl,
786 x_line_qual => lx_Req_qual_tbl,
787 x_line_attr_tbl => lx_Req_line_attr_tbl,
788 x_line_detail_tbl => lx_req_line_detail_tbl,
789 x_line_detail_qual_tbl => lx_req_line_detail_qual_tbl,
790 x_line_detail_attr_tbl => lx_req_line_detail_attr_tbl,
791 x_related_lines_tbl => lx_req_related_lines_tbl,
792 x_return_status => x_return_status,
793 x_return_status_text => l_return_status_text);
794
795 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
796 aso_debug_pub.add('ASO_PRICING_INT:Price Request Status from Pricing Item:'||x_return_status,1,'Y');
797 END IF;
798
799 ASO_PRICING_INT.G_LINE_REC := NULL;
800 ASO_PRICING_INT.G_HEADER_REC := NULL;
801
802 i := lx_req_line_tbl.FIRST;
803 WHILE i IS NOT NULL LOOP
804 lx_req_line_rec := lx_req_line_tbl(i);
805 If lx_req_line_rec.status_code in(QP_PREQ_GRP.g_status_invalid_price_list,
806 QP_PREQ_GRP.g_sts_lhs_not_found,
807 QP_PREQ_GRP.g_status_formula_error,QP_PREQ_GRP.g_status_other_errors,
808 fnd_api.g_ret_sts_unexp_error,fnd_api.g_ret_sts_error,
809 QP_PREQ_GRP.g_status_calc_error,QP_PREQ_GRP.g_status_uom_failure,
810 QP_PREQ_GRP.g_status_invalid_uom,QP_PREQ_GRP.g_status_dup_price_list,
811 QP_PREQ_GRP.g_status_invalid_uom_conv,QP_PREQ_GRP.g_status_invalid_incomp,
812 QP_PREQ_GRP.g_status_best_price_eval_error,
813 QP_PREQ_PUB.g_back_calculation_sts) THEN
814 x_return_status := FND_API.G_RET_STS_ERROR;
815 END IF;
816
817 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
818 aso_debug_pub.add('ASO_PRICING_INT:Fnd_Profile Value for GSA:'
819 ||FND_PROFILE.value('ASO_GSA_PRICING'), 1, 'N');
820 aso_debug_pub.add('ASO_PRICING_INT:After price request in pricing_item for line id '
821 ||lx_req_line_rec.line_id ||'status code '||lx_req_line_rec.status_code, 1, 'N');
822 END IF;
823
824 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
825 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
827 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
828 FND_MESSAGE.Set_Name('ASO', 'ASO_API_UNEXP_ERROR');
829 FND_MESSAGE.Set_Token('ROW', 'ASO_PRICING_INT AFTER PRICING CALL', TRUE);
830 FND_MSG_PUB.ADD;
831 END IF;
832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
833 ELSE
834 l_message_text := lx_req_line_rec.status_code || ': '||lx_req_line_rec.status_text;
835 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
836 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR');
837 FND_MESSAGE.Set_Token('MSG_TXT', substr(l_message_text,1,255), FALSE);
838 FND_MSG_PUB.ADD;
839 END IF;
840 END IF;
841 lv_return_status := x_return_status;
842 END IF;
843 i := lx_req_line_tbl.NEXT(i);
844 END LOOP;
845 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
846 aso_debug_pub.add('ASO_PRICING_INT:Before Copy_Request_To_Line in pricing_item', 1, 'Y');
847 END IF;
848 ASO_PRICING_CALLBACK_PVT.Copy_Request_To_Line (
849 p_req_line_tbl => lx_req_line_tbl,
850 p_req_line_qual => lx_Req_qual_tbl,
851 p_req_line_attr_tbl => lx_Req_line_attr_tbl,
852 p_req_line_detail_tbl => lx_req_line_detail_tbl,
853 p_req_line_detail_qual_tbl => lx_req_line_detail_qual_tbl,
854 p_req_line_detail_attr_tbl => lx_req_line_detail_attr_tbl,
855 p_req_related_lines_tbl => lx_req_related_lines_tbl,
856 p_qte_line_rec => p_qte_line_rec,
857 p_qte_line_dtl_rec => p_qte_line_dtl_rec,
858 x_qte_line_tbl => x_qte_line_tbl,
859 x_qte_line_dtl_tbl => x_qte_line_dtl_tbl,
860 x_price_adj_tbl => x_price_adj_tbl,
861 x_price_adj_attr_tbl => x_price_adj_attr_tbl,
862 x_price_adj_rltship_tbl => x_price_adj_rltship_tbl);
863 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
864 aso_debug_pub.add('ASO_PRICING_INT:after Copy_Request_To_Line in pricing_item', 1, 'Y');
865 END IF;
866
867 -- Standard check for p_commit
868 IF FND_API.to_Boolean( p_commit )
869 THEN
870 COMMIT WORK;
871 END IF;
872
873 FND_MSG_PUB.Count_And_Get
874 ( p_count => x_msg_count,
875 p_data => x_msg_data
876 );
877 IF lv_return_status <> FND_API.G_RET_STS_SUCCESS THEN
878 RAISE FND_API.G_EXC_ERROR;
879 End If;
880
881
882 EXCEPTION
883 WHEN FND_API.G_EXC_ERROR THEN
884 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
885 P_API_NAME => L_API_NAME
886 ,P_PKG_NAME => G_PKG_NAME
887 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
888 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
889 ,P_SQLCODE => SQLCODE
890 ,P_SQLERRM => SQLERRM
891 ,X_MSG_COUNT => X_MSG_COUNT
892 ,X_MSG_DATA => X_MSG_DATA
893 ,X_RETURN_STATUS => X_RETURN_STATUS);
894
895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
896 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
897 P_API_NAME => L_API_NAME
898 ,P_PKG_NAME => G_PKG_NAME
899 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
900 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
901 ,P_SQLCODE => SQLCODE
902 ,P_SQLERRM => SQLERRM
903 ,X_MSG_COUNT => X_MSG_COUNT
904 ,X_MSG_DATA => X_MSG_DATA
905 ,X_RETURN_STATUS => X_RETURN_STATUS);
906
907 WHEN OTHERS THEN
908 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
909 P_API_NAME => L_API_NAME
910 ,P_PKG_NAME => G_PKG_NAME
911 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
912 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
913 ,P_SQLCODE => SQLCODE
914 ,P_SQLERRM => SQLERRM
915 ,X_MSG_COUNT => X_MSG_COUNT
916 ,X_MSG_DATA => X_MSG_DATA
917 ,X_RETURN_STATUS => X_RETURN_STATUS);
918
919 END Pricing_Item;
920
921
922 PROCEDURE Pricing_Order(
923 P_Api_Version_Number IN NUMBER,
924 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
925 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
926 p_control_rec IN PRICING_CONTROL_REC_TYPE,
927 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
928 p_hd_shipment_rec IN ASO_QUOTE_PUB.Shipment_Rec_Type
929 := ASO_QUOTE_PUB.G_Miss_Shipment_Rec,
930 p_hd_price_attr_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
931 := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl,
932 p_qte_line_tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
933 p_line_rltship_tbl IN ASO_QUOTE_PUB.Line_Rltship_Tbl_Type
934 := ASO_QUOTE_PUB.G_Miss_Line_Rltship_Tbl,
935 p_qte_line_dtl_tbl IN ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type
936 := ASO_QUOTE_PUB.G_Miss_Qte_Line_Dtl_Tbl,
937 p_ln_shipment_tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
938 := ASO_QUOTE_PUB.G_Miss_Shipment_Tbl,
939 p_ln_price_attr_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
940 := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl,
941 x_qte_header_rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
942 x_qte_line_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
943 x_qte_line_dtl_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type,
944 x_price_adj_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Price_Adj_Tbl_Type,
945 x_price_adj_attr_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type,
946 x_price_adj_rltship_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type,
947 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
948 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
949 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
950 IS
951 l_api_name CONSTANT VARCHAR2(30) := 'Pricing_Order';
952 l_api_version_number CONSTANT NUMBER := 1.0;
953 l_request_type VARCHAR2(60);
954 l_pricing_event VARCHAR2(30);
955 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
956 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
957 lx_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
958
959 BEGIN
960 -- Standard Start of API savepoint
961 SAVEPOINT PRICING_ORDER_PVT;
962
963 -- Standard call to check for call compatibility.
964 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
965 p_api_version_number,
966 l_api_name,
967 G_PKG_NAME)
968 THEN
969 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
970 END IF;
971
972 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
973
974 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
975 aso_debug_pub.add('ASO_PRICING_INT:Start of Pricing Order.....',1,'Y');
976 END IF;
977
978 -- Initialize message list if p_init_msg_list is set to TRUE.
979 IF FND_API.to_Boolean( p_init_msg_list )
980 THEN
981 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
982 aso_debug_pub.add('ASO_PRICING_INT:Begin FND_API.to_Boolean'||p_init_msg_list, 1, 'Y');
983 END IF;
984 FND_MSG_PUB.initialize;
985 END IF;
986
987 x_return_status := FND_API.G_RET_STS_SUCCESS;
988
989 ASO_PRICING_CORE_PVT.Initialize_Global_Tables;
990
991 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
992 aso_debug_pub.add('ASO_PRICING_INT:p_control_rec.request_type:'||p_control_rec.request_type,1,'Y');
993 aso_debug_pub.add('ASO_PRICING_INT:p_control_rec.pricing_event:'||p_control_rec.pricing_event,1,'Y');
994 END IF;
995 l_request_type := p_control_rec.request_type;
996 l_pricing_event := p_control_rec.pricing_event;
997
998 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
999 aso_debug_pub.add('ASO_PRICING_INT:Pricing Order Code Path Determination Flag - PRICE_CONFIG_FLAG:'
1000 || p_control_rec.PRICE_CONFIG_FLAG,1,'Y');
1001 aso_debug_pub.add('ASO_PRICING_INT:p_qte_line_tbl.count:'||nvl(p_qte_line_tbl.count,0),1,'Y');
1002 END IF;
1003 If p_control_rec.PRICE_CONFIG_FLAG = 'Y' then
1004 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1005 aso_debug_pub.add('ASO_PRICING_INT:Before Pricing Request Configurator Code Path Begins',1,'Y');
1006 END IF;
1007
1008 ASO_PRICING_CALLBACK_PVT.Config_Callback_Pricing_Order(
1009 P_Api_Version_Number => P_Api_Version_Number,
1010 P_Init_Msg_List => FND_API.G_FALSE,
1011 P_Commit => FND_API.G_FALSE,
1012 p_control_rec => p_control_rec,
1013 p_qte_header_rec => p_qte_header_rec,
1014 p_hd_shipment_rec => p_hd_shipment_rec,
1015 p_hd_price_attr_tbl => p_hd_price_attr_tbl,
1016 p_qte_line_tbl => p_qte_line_tbl,
1017 p_line_rltship_tbl => p_line_rltship_tbl,
1018 p_qte_line_dtl_tbl => p_qte_line_dtl_tbl,
1019 p_ln_shipment_tbl => p_ln_shipment_tbl,
1020 p_ln_price_attr_tbl => p_ln_price_attr_tbl,
1021 x_qte_header_rec => x_qte_header_rec,
1022 x_qte_line_tbl => x_qte_line_tbl,
1023 x_qte_line_dtl_tbl => x_qte_line_dtl_tbl,
1024 x_price_adj_tbl => x_price_adj_tbl,
1025 x_price_adj_attr_tbl => x_price_adj_attr_tbl,
1026 x_price_adj_rltship_tbl => x_price_adj_rltship_tbl,
1027 x_return_status => x_return_status,
1028 x_msg_count => x_msg_count,
1029 x_msg_data => x_msg_data);
1030
1031 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1032 RAISE FND_API.G_EXC_ERROR;
1033 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1034 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1035 END IF;
1036
1037 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1038 aso_debug_pub.add('ASO_PRICING_INT:After Config_Callback_Pricing_Order:x_qte_line_tbl.count:'||nvl(x_qte_line_tbl.count,0),1,'Y');
1039 If x_qte_line_tbl.count > 0 then
1040 For i in 1..x_qte_line_tbl.count loop
1041 aso_debug_pub.add('ASO_PRICING_INT:x_qte_line_tbl('||i||').LINE_LIST_PRICE: '||x_qte_line_tbl(i).LINE_LIST_PRICE,1,'Y');
1042 aso_debug_pub.add('ASO_PRICING_INT:x_qte_line_tbl('||i||').LINE_QUOTE_PRICE: '||x_qte_line_tbl(i).LINE_QUOTE_PRICE,1,'Y');
1043 aso_debug_pub.add('ASO_PRICING_INT:x_qte_line_tbl('||i||').INVENTORY_ITEM_ID: '||x_qte_line_tbl(i).INVENTORY_ITEM_ID,1,'Y');
1044 End Loop;
1045 End If;
1046
1047 END IF;-- ASO_DEBUG_PUB.G_Debug_Flag = 'Y'
1048
1049 Else
1050 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1051 aso_debug_pub.add('ASO_PRICING_INT: p_control_rec.price_mode: '||NVL(p_control_rec.price_mode,'NULL'),1,'Y');
1052 END IF;
1053
1054 --Non Configurator Code Path
1055
1056 If NVL(p_control_rec.price_mode,'ENTIRE_QUOTE') = 'ENTIRE_QUOTE' then
1057
1058 ASO_PRICING_FLOWS_PVT.Price_Entire_Quote(
1059 P_Api_Version_Number => P_Api_Version_Number,
1060 P_Init_Msg_List => FND_API.G_FALSE,
1061 P_Commit => FND_API.G_FALSE,
1062 p_control_rec => p_control_rec,
1063 p_qte_header_rec => p_qte_header_rec,
1064 p_hd_shipment_rec => p_hd_shipment_rec,
1065 p_qte_line_tbl => p_qte_line_tbl,
1066 x_qte_line_tbl => lx_qte_line_tbl,
1067 x_return_status => x_return_status,
1068 x_msg_count => x_msg_count,
1069 x_msg_data => x_msg_data);
1070
1071 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1072 RAISE FND_API.G_EXC_ERROR;
1073 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1075 END IF;
1076
1077 ELSIF (p_control_rec.price_mode = 'CHANGE_LINE') then
1078 -- Change Line logic code path
1079
1080 ASO_PRICING_FLOWS_PVT.Price_Quote_With_Change_Lines(
1081 P_Api_Version_Number => P_Api_Version_Number,
1082 P_Init_Msg_List => FND_API.G_FALSE,
1083 P_Commit => FND_API.G_FALSE,
1084 p_control_rec => p_control_rec,
1085 p_qte_header_rec => p_qte_header_rec,
1086 p_hd_shipment_rec => p_hd_shipment_rec,
1087 p_qte_line_tbl => p_qte_line_tbl,
1088 x_qte_line_tbl => lx_qte_line_tbl,
1089 x_return_status => x_return_status,
1090 x_msg_count => x_msg_count,
1091 x_msg_data => x_msg_data);
1092
1093 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1094 RAISE FND_API.G_EXC_ERROR;
1095 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097 END IF;
1098
1099 Else
1100 --p_control_rec.price_mode = 'QUOTE_LINE'
1101
1102 ASO_PRICING_FLOWS_PVT.Price_Quote_Line(
1103 P_Api_Version_Number => P_Api_Version_Number,
1104 P_Init_Msg_List => FND_API.G_FALSE,
1105 P_Commit => FND_API.G_FALSE,
1106 p_control_rec => p_control_rec,
1107 p_qte_header_rec => p_qte_header_rec,
1108 p_hd_shipment_rec => p_hd_shipment_rec,
1109 p_qte_line_tbl => p_qte_line_tbl,
1110 x_return_status => x_return_status,
1111 x_msg_count => x_msg_count,
1112 x_msg_data => x_msg_data);
1113
1114 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1115 RAISE FND_API.G_EXC_ERROR;
1116 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1118 END IF;
1119
1120 End If;--NVL(p_control_rec.price_mode,'ENTIRE_QUOTE') = 'ENTIRE_QUOTE'
1121
1122
1123 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1124 aso_debug_pub.add('ASO_PRICING_INT:After Entire_Quote:lx_qte_line_tbl.count:'||nvl(lx_qte_line_tbl.count,0),1,'Y');
1125 END IF;
1126
1127 x_qte_line_tbl:= p_qte_line_tbl;
1128
1129 If lx_qte_line_tbl.count > 0 then
1130 For i in 1..lx_qte_line_tbl.count loop
1131 x_qte_line_tbl(x_qte_line_tbl.count+1) := lx_qte_line_tbl(i);
1132 end loop;
1133 end If;
1134 End if;--p_control_rec.PRICE_CONFIG_FLAG = 'Y'
1135
1136 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1137 aso_debug_pub.add('ASO_PRICING_INT:End of Pricing Order:x_qte_line_tbl.count:'||nvl(x_qte_line_tbl.count,0),1,'Y');
1138 END IF;
1139
1140 FND_MSG_PUB.Count_And_Get
1141 ( p_encoded => 'F',
1142 p_count => x_msg_count,
1143 p_data => x_msg_data
1144 );
1145
1146 for l in 1 .. x_msg_count loop
1147 x_msg_data := fnd_msg_pub.get( p_msg_index => l, p_encoded => 'F');
1148 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1149 aso_debug_pub.add('ASO_PRICING_INT:Messge count and get '||x_msg_data, 1, 'Y');
1150 aso_debug_pub.add('ASO_PRICING_INT:Messge count and get '||x_msg_count, 1, 'Y');
1151 END IF;
1152 end loop;
1153
1154 EXCEPTION
1155 WHEN FND_API.G_EXC_ERROR THEN
1156 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1157 aso_debug_pub.add('ASO_PRICING_INT:after inside EXCEPTION return status'||x_return_status, 1, 'Y');
1158 END IF;
1159 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1160 P_API_NAME => L_API_NAME
1161 ,P_PKG_NAME => G_PKG_NAME
1162 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1163 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1164 ,P_SQLCODE => SQLCODE
1165 ,P_SQLERRM => SQLERRM
1166 ,X_MSG_COUNT => X_MSG_COUNT
1167 ,X_MSG_DATA => X_MSG_DATA
1168 ,X_RETURN_STATUS => X_RETURN_STATUS);
1169
1170 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1171 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1172 P_API_NAME => L_API_NAME
1173 ,P_PKG_NAME => G_PKG_NAME
1174 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1175 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1176 ,P_SQLCODE => SQLCODE
1177 ,P_SQLERRM => SQLERRM
1178 ,X_MSG_COUNT => X_MSG_COUNT
1179 ,X_MSG_DATA => X_MSG_DATA
1180 ,X_RETURN_STATUS => X_RETURN_STATUS);
1181
1182 WHEN OTHERS THEN
1183 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1184 P_API_NAME => L_API_NAME
1185 ,P_PKG_NAME => G_PKG_NAME
1186 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1187 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1188 ,P_SQLCODE => SQLCODE
1189 ,P_SQLERRM => SQLERRM
1190 ,X_MSG_COUNT => X_MSG_COUNT
1191 ,X_MSG_DATA => X_MSG_DATA
1192 ,X_RETURN_STATUS => X_RETURN_STATUS);
1193
1194
1195 END Pricing_Order;
1196
1197
1198 PROCEDURE Pricing_Item (
1199 P_Api_Version_Number IN NUMBER,
1200 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1201 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1202 p_control_rec IN PRICING_CONTROL_REC_TYPE,
1203 p_qte_line_id IN NUMBER,
1204 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1205 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1206 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1207 IS
1208 l_api_name CONSTANT VARCHAR2(30) := 'Pricing_Item';
1209 l_api_version_number CONSTANT NUMBER := 1.0;
1210 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
1211 l_qte_header_id NUMBER;
1212 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1213 l_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1214 l_hd_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1215 l_hd_price_attr_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1216 l_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type;
1217 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1218 l_ln_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1219 l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1220 l_ln_price_attr_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1221 lx_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1222 lx_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1223 lx_price_adj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1224 lx_price_adj_attr_tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
1225 lx_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
1226
1227 l_price_list_id NUMBER;
1228 l_CURRENCY_CODE VARCHAR2(15);
1229
1230
1231 CURSOR c_header_id IS
1232 SELECT QUOTE_HEADER_ID
1233 FROM ASO_QUOTE_LINES_ALL
1234 WHERE QUOTE_LINE_ID = p_qte_line_id;
1235
1236 CURSOR c_list_id(l_qte_header_id NUMBER) IS
1237 SELECT price_list_id , CURRENCY_CODE
1238 FROM ASO_QUOTE_HEADERS_ALL
1239 WHERE QUOTE_HEADER_ID = l_qte_header_id;
1240
1241 BEGIN
1242
1243 -- Standard Start of API savepoint
1244 SAVEPOINT PRICING_ITEM_PVT;
1245
1246 -- Standard call to check for call compatibility.
1247 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1248 p_api_version_number,
1249 l_api_name,
1250 G_PKG_NAME)
1251 THEN
1252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1253 END IF;
1254
1255
1256 -- Initialize message list if p_init_msg_list is set to TRUE.
1257 IF FND_API.to_Boolean( p_init_msg_list )
1258 THEN
1259 FND_MSG_PUB.initialize;
1260 END IF;
1261
1262 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
1263
1264 x_return_status := FND_API.G_RET_STS_SUCCESS;
1265 l_qte_line_rec := ASO_UTILITY_PVT.Query_Qte_Line_Row(p_qte_line_id);
1266 l_qte_header_id := l_qte_line_rec.QUOTE_HEADER_ID;
1267 l_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(l_qte_header_id);
1268 l_hd_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows(l_qte_header_id, NULL);
1269
1270 IF l_hd_shipment_tbl.count = 1 THEN
1271 l_hd_shipment_rec := l_hd_shipment_tbl(1);
1272 END IF;
1273 l_hd_price_attr_tbl := ASO_UTILITY_PVT.Query_Price_Attr_Rows(l_qte_header_id, null);
1274 l_qte_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows(p_qte_line_id);
1275
1276 IF l_qte_line_dtl_tbl.count = 1 THEN
1277 l_qte_line_dtl_rec := l_qte_line_dtl_tbl(1);
1278 END IF;
1279 l_ln_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows(l_qte_header_id, p_QTE_LINE_ID);
1280
1281 IF l_ln_shipment_tbl.count = 1 THEN
1282 l_ln_shipment_rec := l_ln_shipment_tbl(1);
1283 END IF;
1284 l_ln_price_attr_tbl := ASO_UTILITY_PVT.Query_Price_Attr_Rows(l_qte_header_id, p_qte_line_id);
1285 --Code changed on 04/18/2000
1286
1287 OPEN c_header_id;
1288 FETCH c_header_id INTO l_qte_header_id;
1289 CLOSE c_header_id;
1290
1291 OPEN c_list_id (l_qte_header_id);
1292 FETCH c_list_id INTO l_price_list_id,l_CURRENCY_CODE;
1293 CLOSE c_list_id;
1294
1295 IF l_qte_line_rec.price_list_id is NULL or l_qte_line_rec.price_list_id = FND_API.G_MISS_NUM THEN
1296 l_qte_line_rec.price_list_id := l_price_list_id ;
1297 ELSE
1298 l_qte_header_rec.price_list_id := NULL;
1299 END IF;
1300
1301 IF l_qte_line_rec.CURRENCY_CODE is NULL or l_qte_line_rec.CURRENCY_CODE = FND_API.G_MISS_CHAR THEN
1302 l_qte_line_rec.CURRENCY_CODE := l_CURRENCY_CODE ;
1303 END IF;
1304
1305 Pricing_Item (
1306 P_Api_Version_Number => 1,
1307 P_Init_Msg_List => FND_API.G_FALSE,
1308 P_Commit => FND_API.G_FALSE,
1309 p_control_rec => p_control_rec,
1310 p_qte_header_rec => l_qte_header_rec,
1311 p_hd_shipment_rec => l_hd_shipment_rec,
1312 p_hd_price_attr_tbl => l_hd_price_attr_tbl,
1313 p_qte_line_rec => l_qte_line_rec,
1314 p_qte_line_dtl_rec => l_qte_line_dtl_rec,
1315 p_ln_shipment_rec => l_ln_shipment_rec,
1316 p_ln_price_attr_tbl => l_ln_price_attr_tbl,
1317 x_qte_line_tbl => lx_qte_line_tbl,
1318 x_qte_line_dtl_tbl => lx_qte_line_dtl_tbl,
1319 x_price_adj_tbl => lx_price_adj_tbl,
1320 x_price_adj_attr_tbl => lx_price_adj_attr_tbl,
1321 x_price_adj_rltship_tbl => lx_price_adj_rltship_tbl,
1322 x_return_status => x_return_status,
1323 x_msg_data => x_msg_data,
1324 x_msg_count => x_msg_count);
1325
1326 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1327 RAISE FND_API.G_EXC_ERROR;
1328 END IF;
1329
1330 /* Changed the delete statement as per bug 1874082 */
1331 /*Removed the Complex Delete Statement with UNION bug 2585468 */
1332 DELETE from ASO_PRICE_ADJ_RELATIONSHIPS
1333 WHERE quote_line_id = p_qte_line_id;
1334 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1335 aso_debug_pub.add('ASO_PRICING_INT:Rltd adj Lines deleted '||sql%ROWCOUNT,1,'Y');
1336 END IF;
1337
1338
1339 DELETE FROM ASO_PRICE_ADJUSTMENTS
1340 WHERE quote_line_id = p_qte_line_id;
1341 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1342 aso_debug_pub.add('ASO_PRICING_INT:ADJ Lines deleted '||sql%ROWCOUNT,1,'Y');
1343 END IF;
1344
1345 FOR i IN 1..lx_qte_line_tbl.count LOOP
1346 l_qte_line_rec := ASO_UTILITY_PVT.Query_Qte_Line_Row(lx_qte_line_tbl(i).quote_line_id);
1347 lx_qte_line_tbl(i).price_list_id := l_qte_line_rec.price_list_id;
1348 END LOOP;
1349
1350 ASO_PRICING_CALLBACK_PVT.Update_Quote_Rows (
1351 p_qte_line_tbl => lx_qte_line_tbl,
1352 p_qte_line_dtl_tbl => lx_qte_line_dtl_tbl,
1353 p_price_adj_tbl => lx_price_adj_tbl,
1354 p_price_adj_attr_tbl => lx_price_adj_attr_tbl,
1355 p_price_adj_rltship_tbl => lx_price_adj_rltship_tbl);
1356
1357 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1358 aso_debug_pub.add('ASO_PRICING_INT:Pricing Item Ends if the second Pricing Item was called...',1,'Y');
1359 END IF;
1360
1361 -- Standard check for p_commit
1362 IF FND_API.to_Boolean( p_commit )
1363 THEN
1364 COMMIT WORK;
1365 END IF;
1366
1367 EXCEPTION
1368 WHEN FND_API.G_EXC_ERROR THEN
1369 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1370 P_API_NAME => L_API_NAME
1371 ,P_PKG_NAME => G_PKG_NAME
1372 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1373 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1374 ,P_SQLCODE => SQLCODE
1375 ,P_SQLERRM => SQLERRM
1376 ,X_MSG_COUNT => X_MSG_COUNT
1377 ,X_MSG_DATA => X_MSG_DATA
1378 ,X_RETURN_STATUS => X_RETURN_STATUS);
1379
1380 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1381 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1382 P_API_NAME => L_API_NAME
1383 ,P_PKG_NAME => G_PKG_NAME
1384 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1385 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1386 ,P_SQLCODE => SQLCODE
1387 ,P_SQLERRM => SQLERRM
1388 ,X_MSG_COUNT => X_MSG_COUNT
1389 ,X_MSG_DATA => X_MSG_DATA
1390 ,X_RETURN_STATUS => X_RETURN_STATUS);
1391
1392 WHEN OTHERS THEN
1393 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1394 P_API_NAME => L_API_NAME
1395 ,P_PKG_NAME => G_PKG_NAME
1396 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1397 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1398 ,P_SQLCODE => SQLCODE
1399 ,P_SQLERRM => SQLERRM
1400 ,X_MSG_COUNT => X_MSG_COUNT
1401 ,X_MSG_DATA => X_MSG_DATA
1402 ,X_RETURN_STATUS => X_RETURN_STATUS);
1403
1404
1405 END Pricing_Item;
1406
1407
1408 PROCEDURE Pricing_Order (
1409 P_Api_Version_Number IN NUMBER,
1410 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1411 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1412 p_control_rec IN PRICING_CONTROL_REC_TYPE,
1413 p_qte_line_tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
1414 p_qte_header_id IN NUMBER,
1415 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1416 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1417 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1418 IS
1419
1420 l_api_name CONSTANT VARCHAR2(30) := 'Pricing_Order';
1421 l_api_version_number CONSTANT NUMBER := 1.0;
1422 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
1423 l_req_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
1424 l_Req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
1425 l_Req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
1426 l_Req_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
1427 l_req_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
1428 l_req_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
1429 l_req_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
1430 l_hd_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
1431 l_hd_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
1432 l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
1433 l_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
1434 lx_req_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
1435 lx_req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
1436 lx_req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
1437 lx_req_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
1438 lx_req_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
1439 lx_req_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
1440 lx_req_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
1441 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1442 l_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1443 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1444 l_price_attr_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1445 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1446 l_qte_line_id NUMBER;
1447 l_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type;
1448 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1449 l_return_status VARCHAR2(1);
1450 l_return_status_text VARCHAR2(2000);
1451 l_request_type VARCHAR2(60);
1452 l_pricing_event VARCHAR2(30);
1453 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_rec_Type;
1454 lx_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1455 lx_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1456 lx_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1457 lx_price_adj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1458 lx_price_adj_attr_tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
1459 lx_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
1460 l_message_text VARCHAR2(2000);
1461 i BINARY_INTEGER;
1462 ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1463 l_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
1464 l_ln_price_attr_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1465 lx_return_status VARCHAR2(50);
1466 lx_msg_count NUMBER;
1467 lx_msg_data VARCHAR2(2000);
1468
1469 BEGIN
1470 -- Standard Start of API savepoint
1471 SAVEPOINT PRICING_ORDER_PVT;
1472
1473 -- Standard call to check for call compatibility.
1474 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1475 p_api_version_number,
1476 l_api_name,
1477 G_PKG_NAME)
1478 THEN
1479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1480 END IF;
1481
1482
1483 -- Initialize message list if p_init_msg_list is set to TRUE.
1484 IF FND_API.to_Boolean( p_init_msg_list )
1485 THEN
1486 FND_MSG_PUB.initialize;
1487 END IF;
1488
1489 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1490 aso_debug_pub.add('ASO_PRICING_INT:In Pricing Order with hdr Id', 1, 'Y');
1491 END IF;
1492
1493 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
1494
1495 x_return_status := FND_API.G_RET_STS_SUCCESS;
1496 l_request_type := p_control_rec.request_type;
1497 l_pricing_event := p_control_rec.pricing_event;
1498
1499 l_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(p_qte_header_id);
1500 l_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows(p_qte_header_id,NULL);
1501 IF l_shipment_tbl.count = 1 THEN
1502 l_shipment_rec := l_shipment_tbl(1);
1503 END IF;
1504 l_price_attr_tbl := ASO_UTILITY_PVT.Query_Price_Attr_Rows(p_qte_header_id, null);
1505 ASO_PRICING_INT.Pricing_Order(
1506 P_Api_Version_Number => 1.0,
1507 P_Init_Msg_List => FND_API.G_FALSE,
1508 P_Commit => FND_API.G_FALSE,
1509 p_control_rec => p_control_rec,
1510 p_qte_header_rec => l_qte_header_rec,
1511 p_hd_shipment_rec => l_shipment_rec,
1512 p_hd_price_attr_tbl => l_price_attr_tbl,
1513 p_qte_line_tbl => p_qte_line_tbl,
1514 p_line_rltship_tbl => l_line_rltship_tbl,
1515 p_qte_line_dtl_tbl => l_qte_line_dtl_tbl,
1516 p_ln_shipment_tbl => ln_shipment_tbl,
1517 p_ln_price_attr_tbl => l_ln_price_attr_tbl,
1518 x_qte_header_rec => lx_qte_header_rec,
1519 x_qte_line_tbl => lx_qte_line_tbl,
1520 x_qte_line_dtl_tbl => lx_qte_line_dtl_tbl,
1521 x_price_adj_tbl => lx_price_adj_tbl,
1522 x_price_adj_attr_tbl => lx_price_adj_attr_tbl,
1523 x_price_adj_rltship_tbl => lx_price_adj_rltship_tbl,
1524 x_return_status => x_return_status,
1525 x_msg_count => x_msg_count,
1526 x_msg_data => x_msg_data );
1527
1528 -- Standard check for p_commit
1529 IF FND_API.to_Boolean( p_commit )
1530 THEN
1531 COMMIT WORK;
1532 END IF;
1533
1534 FND_MSG_PUB.Count_And_Get
1535 ( p_count => x_msg_count,
1536 p_data => x_msg_data
1537 );
1538
1539 EXCEPTION
1540 WHEN FND_API.G_EXC_ERROR THEN
1541 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1542 P_API_NAME => L_API_NAME
1543 ,P_PKG_NAME => G_PKG_NAME
1544 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1545 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1546 ,P_SQLCODE => SQLCODE
1547 ,P_SQLERRM => SQLERRM
1548 ,X_MSG_COUNT => X_MSG_COUNT
1549 ,X_MSG_DATA => X_MSG_DATA
1550 ,X_RETURN_STATUS => X_RETURN_STATUS);
1551
1552 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1553 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1554 P_API_NAME => L_API_NAME
1555 ,P_PKG_NAME => G_PKG_NAME
1556 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1557 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1558 ,P_SQLCODE => SQLCODE
1559 ,P_SQLERRM => SQLERRM
1560 ,X_MSG_COUNT => X_MSG_COUNT
1561 ,X_MSG_DATA => X_MSG_DATA
1562 ,X_RETURN_STATUS => X_RETURN_STATUS);
1563
1564 WHEN OTHERS THEN
1565 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1566 P_API_NAME => L_API_NAME
1567 ,P_PKG_NAME => G_PKG_NAME
1568 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1569 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1570 ,P_SQLCODE => SQLCODE
1571 ,P_SQLERRM => SQLERRM
1572 ,X_MSG_COUNT => X_MSG_COUNT
1573 ,X_MSG_DATA => X_MSG_DATA
1574 ,X_RETURN_STATUS => X_RETURN_STATUS);
1575
1576 END Pricing_Order;
1577
1578
1579 Procedure Delete_Promotion (
1580 P_Api_Version_Number IN NUMBER,
1581 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1582 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1583 p_price_attr_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type,
1584 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1585 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1586 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1587 IS
1588 BEGIN
1589 ASO_PRICING_CORE_PVT.Delete_Promotion (
1590 P_Api_Version_Number => P_Api_Version_Number,
1591 P_Init_Msg_List => P_Init_Msg_List,
1592 P_Commit => P_Commit,
1593 p_price_attr_tbl => p_price_attr_tbl,
1594 x_return_status => x_return_status,
1595 x_msg_count => x_msg_count,
1596 x_msg_data => x_msg_data);
1597
1598 END Delete_Promotion;
1599
1600 -- hagrawal_start Funtion added
1601 FUNCTION Get_Cust_Po(
1602 p_qte_header_id number
1603 ) RETURN VARCHAR2
1604 IS
1605 Cursor get_po is SELECT payment_ref_number from aso_payments
1606 WHERE
1607 payment_type_code ='PO' and quote_header_id = p_qte_header_id and quote_line_id is NULL;
1608 Customer_PO VARCHAR2(240);
1609
1610 BEGIN
1611 OPEN get_po;
1612 fetch get_po into Customer_Po;
1613 CLOSE get_po;
1614 RETURN Customer_Po;
1615 END Get_Cust_Po;
1616
1617 FUNCTION Get_line_Cust_Po(
1618 p_qte_line_id number
1619 ) RETURN VARCHAR2
1620 IS
1621 Cursor get_po is SELECT payment_ref_number from aso_payments
1622 WHERE
1623 payment_type_code ='PO' and quote_line_id = p_qte_line_id;
1624 Customer_PO VARCHAR2(240);
1625
1626 BEGIN
1627 OPEN get_po;
1628 fetch get_po into Customer_Po;
1629 CLOSE get_po;
1630 RETURN Customer_Po;
1631 END Get_line_Cust_Po;
1632
1633 FUNCTION Get_Request_date(
1634 p_qte_header_id number
1635 ) RETURN DATE
1636 IS
1637
1638 Cursor get_req_date is SELECT request_date from aso_shipments
1639 WHERE
1640 quote_header_id = p_qte_header_id and quote_line_id is NULL;
1641 l_request_date DATE;
1642 x_request_date DATE;
1643 BEGIN
1644 OPEN get_req_date;
1645 fetch get_req_date into l_request_date;
1646 CLOSE get_req_date;
1647
1648 x_request_date := FND_DATE.DATE_TO_CANONICAL(l_request_date);
1649 RETURN x_request_date;
1650 END Get_Request_date;
1651
1652 FUNCTION Get_Line_Request_date(
1653 p_qte_line_id number
1654 ) RETURN DATE
1655 IS
1656
1657 Cursor get_req_date is SELECT request_date from aso_shipments
1658 WHERE quote_line_id = p_qte_line_id ;
1659 l_request_date DATE;
1660 x_request_date DATE;
1661 BEGIN
1662 OPEN get_req_date;
1663 fetch get_req_date into l_request_date;
1664 CLOSE get_req_date;
1665
1666 x_request_date := FND_DATE.DATE_TO_CANONICAL(l_request_date);
1667 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1668 aso_debug_pub.add('ASO_PRICING_INT:In Get_Line_Request_date'||x_request_date, 1, 'Y');
1669 END IF;
1670 RETURN x_request_date;
1671 END Get_line_Request_date;
1672
1673
1674 FUNCTION Get_Freight_term(
1675 p_qte_header_id number
1676 ) RETURN DATE
1677 IS
1678
1679 Cursor get_frieght is SELECT FREIGHT_TERMS_CODE from aso_shipments
1680 WHERE
1681 quote_header_id = p_qte_header_id and quote_line_id is NULL;
1682 l_freight_terms_code VARCHAR2(30);
1683 BEGIN
1684 OPEN get_frieght;
1685 fetch get_frieght into l_freight_terms_code;
1686 CLOSE get_frieght;
1687 RETURN l_freight_terms_code;
1688 END Get_Freight_term;
1689
1690 FUNCTION Get_line_Freight_term(
1691 p_qte_line_id number
1692 ) RETURN VARCHAR2
1693 IS
1694
1695 Cursor get_frieght is SELECT FREIGHT_TERMS_CODE from aso_shipments
1696 WHERE
1697 quote_line_id = p_qte_line_id;
1698 l_freight_terms_code VARCHAR2(30);
1699 BEGIN
1700 OPEN get_frieght;
1701 fetch get_frieght into l_freight_terms_code;
1702 CLOSE get_frieght;
1703 RETURN l_freight_terms_code;
1704 END Get_line_Freight_term;
1705
1706 FUNCTION Get_Payment_term(
1707 p_qte_header_id number
1708 ) RETURN NUMBER
1709 IS
1710
1711 Cursor get_pmnt_term is SELECT payment_term_id from aso_payments
1712 WHERE
1713 quote_header_id = p_qte_header_id and quote_line_id IS null;
1714 l_pmnt_term_id NUMBER;
1715 BEGIN
1716 OPEN get_pmnt_term;
1717 fetch get_pmnt_term into l_pmnt_term_id;
1718 CLOSE get_pmnt_term;
1719 RETURN l_pmnt_term_id;
1720 END Get_Payment_term;
1721
1722
1723 FUNCTION Get_line_Payment_term(
1724 p_qte_line_id number
1725 ) RETURN NUMBER
1726 IS
1727
1728 Cursor get_pmnt_term is SELECT payment_term_id from aso_payments
1729 WHERE
1730 quote_line_id = p_qte_line_id;
1731 l_pmnt_term_id NUMBER;
1732 BEGIN
1733 OPEN get_pmnt_term;
1734 fetch get_pmnt_term into l_pmnt_term_id;
1735 CLOSE get_pmnt_term;
1736 RETURN l_pmnt_term_id;
1737 END Get_line_Payment_term;
1738
1739
1740 End ASO_PRICING_INT;