[Home] [Help]
PACKAGE BODY: APPS.ASO_PRICING_INT
Source
1 PACKAGE BODY ASO_PRICING_INT AS
2 /* $Header: asoiprcb.pls 120.7 2011/09/16 07:07:37 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 x_pass_line VARCHAR2(10); -- bug 12988510
599
600 BEGIN
601 -- Standard Start of API savepoint
602 SAVEPOINT PRICING_ITEM_PVT;
603
604 -- Standard call to check for call compatibility.
605 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
606 p_api_version_number,
607 l_api_name,
608 G_PKG_NAME)
609 THEN
610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
611 END IF;
612
613 -- Initialize message list if p_init_msg_list is set to TRUE.
614 IF FND_API.to_Boolean(p_init_msg_list)
615 THEN
616 FND_MSG_PUB.initialize;
617 END IF;
618
619 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
620
621 x_return_status := FND_API.G_RET_STS_SUCCESS;
622 l_request_type := NVL(p_control_rec.request_type,'ASO');
623 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
624 aso_debug_pub.add('ASO_PRICING_INT:In Pricing Item: p_qte_header_rec.quote_status_id'
625 ||p_qte_header_rec.quote_status_id,1,'Y');
626 END IF;
627 -- Bug No 6510202. Header rec needs to be intialized since the header rec frozen date is passed
628 -- to line rec price effective date which would be picked by pricing for selecting the correct price list
629 ASO_PRICING_INT.G_HEADER_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
630 p_qte_header_rec => p_qte_header_rec,
631 p_shipment_rec => p_hd_shipment_rec);
632
633 IF NVL(p_qte_header_rec.quote_status_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
634 l_pricing_event := 'BATCH'; --p_control_rec.pricing_event;
635 -- commented for bug no 6510202
636 /* ASO_PRICING_INT.G_HEADER_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
637 p_qte_header_rec => p_qte_header_rec,
638 p_shipment_rec => p_hd_shipment_rec);
639 */
640
641 -- bug 12988510, using overloaded funtion of build context which passes p_check_line_flag, p_pricing_event and returns x_pass_line
642 QP_ATTR_MAPPING_PUB.Build_Contexts (
643 P_REQUEST_TYPE_CODE => l_request_type,
644 P_PRICING_TYPE => 'H',
645 p_check_line_flag => 'N',
646 p_pricing_event => l_pricing_event,
647 X_PRICE_CONTEXTS_RESULT_TBL => l_hd_pricing_contexts_tbl,
648 X_QUAL_CONTEXTS_RESULT_TBL => l_hd_qual_contexts_tbl,
649 x_PASS_LINE => x_pass_line);
650
651 ASO_PRICING_CALLBACK_PVT.Copy_Attribs_To_Req (
652 p_line_index => 1,
653 p_pricing_contexts_tbl => l_hd_pricing_contexts_tbl,
654 p_qualifier_contexts_tbl => l_hd_qual_contexts_tbl,
655 px_req_line_attr_tbl => l_req_line_attr_tbl,
656 px_req_qual_tbl => l_req_qual_tbl);
657
658 ASO_PRICING_CALLBACK_PVT.Copy_Header_To_Request(
659 p_Request_Type => l_request_type,
660 p_pricing_event => l_pricing_event,
661 p_header_rec => p_qte_header_rec,
662 px_req_line_tbl => l_Req_line_tbl);
663
664 ASO_PRICING_INT.G_LINE_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec(
665 p_qte_line_rec => p_qte_line_rec,
666 p_qte_line_dtl_rec => p_qte_line_dtl_rec,
667 p_shipment_rec => p_ln_shipment_rec);
668
669 -- bug 12988510, using overloaded funtion of build context which passes p_check_line_flag, p_pricing_event and returns x_pass_line
670 QP_ATTR_MAPPING_PUB.Build_Contexts (
671 P_REQUEST_TYPE_CODE => l_request_type,
672 P_PRICING_TYPE => 'L',
673 p_check_line_flag => 'N',
674 p_pricing_event => l_pricing_event,
675 X_PRICE_CONTEXTS_RESULT_TBL => l_pricing_contexts_tbl,
676 X_QUAL_CONTEXTS_RESULT_TBL => l_qual_contexts_tbl,
677 x_PASS_LINE => x_pass_line);
678
679 ASO_PRICING_CALLBACK_PVT.Copy_Attribs_To_Req (
680 p_line_index => 1+1,
681 p_pricing_contexts_tbl => l_pricing_contexts_tbl,
682 p_qualifier_contexts_tbl => l_qual_contexts_tbl,
683 px_req_line_attr_tbl => l_req_line_attr_tbl,
684 px_req_qual_tbl => l_req_qual_tbl);
685
686 ASO_PRICING_CALLBACK_PVT.Copy_hdr_attr_to_line (
687 p_line_index => 1+1,
688 p_pricing_contexts_tbl => l_hd_pricing_contexts_tbl,
689 p_qualifier_contexts_tbl=> l_hd_qual_contexts_tbl,
690 px_req_line_attr_tbl => l_req_line_attr_tbl,
691 px_req_qual_tbl => l_req_qual_tbl);
692
693 ASO_PRICING_CALLBACK_PVT.Append_asked_for(
694 p_header_id => p_qte_header_rec.quote_header_id
695 ,p_line_id => p_qte_line_rec.quote_line_id
696 ,p_line_index => 1
697 ,px_Req_line_attr_tbl => l_Req_line_attr_tbl
698 ,px_Req_qual_tbl => l_Req_qual_tbl);
699
700 ASO_PRICING_CALLBACK_PVT.Copy_Line_To_Request(
701 p_Request_Type => l_request_type,
702 p_pricing_event => l_pricing_event,
703 p_line_rec => p_qte_line_rec,
704 p_line_dtl_rec => p_qte_line_dtl_rec,
705 p_control_rec => p_control_rec,
706 px_req_line_tbl => l_Req_line_tbl);
707
708 l_control_rec.pricing_event := l_pricing_event;
709 l_control_rec.calculate_flag := p_control_rec.calculate_flag;
710 l_control_rec.simulation_flag := p_control_rec.simulation_flag;
711 l_control_rec.source_order_amount_flag := 'Y';
712 l_control_rec.TEMP_TABLE_INSERT_FLAG := 'Y';
713 l_control_rec.GSA_CHECK_FLAG := 'Y';
714 l_control_rec.GSA_DUP_CHECK_FLAG := 'Y';
715 ELSE
716 l_pricing_event := NVL(p_control_rec.pricing_event,'LINE');
717 ASO_PRICING_INT.G_LINE_REC := ASO_PRICING_CORE_PVT.Set_Global_Rec (
718 p_qte_line_rec => p_qte_line_rec,
719 p_qte_line_dtl_rec => p_qte_line_dtl_rec,
720 p_shipment_rec => p_ln_shipment_rec);
721
722 -- bug 12988510, using overloaded funtion of build context which passes p_check_line_flag, p_pricing_event and returns x_pass_line
723 QP_ATTR_MAPPING_PUB.Build_Contexts (
724 P_REQUEST_TYPE_CODE => l_request_type,
725 P_PRICING_TYPE => 'L',
726 p_check_line_flag => 'N',
727 p_pricing_event => l_pricing_event,
728 X_PRICE_CONTEXTS_RESULT_TBL => l_pricing_contexts_tbl,
729 X_QUAL_CONTEXTS_RESULT_TBL => l_qual_contexts_tbl,
730 x_pass_line => x_pass_line);
731
732 ASO_PRICING_CALLBACK_PVT.Copy_Attribs_To_Req (
733 p_line_index => 1,
734 p_pricing_contexts_tbl => l_pricing_contexts_tbl,
735 p_qualifier_contexts_tbl => l_qual_contexts_tbl,
736 px_req_line_attr_tbl => l_req_line_attr_tbl,
737 px_req_qual_tbl => l_req_qual_tbl);
738 /************************************************************
739 ASO_PRICING_CALLBACK_PVT.Append_asked_for(
740 p_header_id => p_qte_header_rec.quote_header_id
741 ,p_line_id => p_qte_line_rec.quote_line_id
742 ,p_line_index => 1
743 ,px_Req_line_attr_tbl => l_Req_line_attr_tbl
744 ,px_Req_qual_tbl => l_Req_qual_tbl);
745 ********************************************************/
746 ASO_PRICING_CALLBACK_PVT.Append_asked_for(
747 p_line_index => 1,
748 p_pricing_attr_tbl => p_ln_price_attr_tbl,
749 px_Req_line_attr_tbl => l_Req_line_attr_tbl,
750 px_Req_qual_tbl => l_Req_qual_tbl);
751
752 ASO_PRICING_CALLBACK_PVT.Copy_Line_To_Request(
753 p_Request_Type => l_request_type,
754 p_pricing_event => l_pricing_event,
755 p_line_rec => p_qte_line_rec,
756 p_line_dtl_rec => p_qte_line_dtl_rec,
757 p_control_rec => p_control_rec,
758 px_req_line_tbl => l_Req_line_tbl);
759
760 l_control_rec.pricing_event := l_pricing_event;
761 l_control_rec.calculate_flag := p_control_rec.calculate_flag;
762 l_control_rec.simulation_flag := p_control_rec.simulation_flag;
763 l_control_rec.source_order_amount_flag := 'Y';
764 l_control_rec.TEMP_TABLE_INSERT_FLAG := 'Y';
765 l_control_rec.GSA_CHECK_FLAG := 'Y';
766 l_control_rec.GSA_DUP_CHECK_FLAG := 'Y';
767 END IF;
768 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
769 aso_debug_pub.add('ASO_PRICING_INT:In Pricing Item: l_control_rec.pricing_event'
770 ||l_control_rec.pricing_event,1,'Y');
771 aso_debug_pub.add('ASO_PRICING_INT:In Pricing Item: l_request_type'||l_request_type,1,'Y');
772 END IF;
773
774
775 /* Change for populating QP_PREQ_GRP.CONTROL_RECORD_TYPE.ORG_ID Yogeshwar (MOAC) */
776
777 IF ((p_qte_header_rec.org_id IS NULL) OR (p_qte_header_rec.org_id = FND_API.G_MISS_NUM)) THEN
778 IF fnd_msg_pub.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
779 FND_MESSAGE.Set_Name('ASO', 'ASO_MISSING_OU');
780 FND_MSG_PUB.ADD;
781 END IF;
782
783 RAISE FND_API.G_EXC_ERROR;
784 END IF;
785
786 l_control_rec.ORG_ID := p_qte_header_rec.org_id;
787
788 /* End of Change (MOAC) */
789
790
791 QP_PREQ_PUB.PRICE_REQUEST
792 (p_control_rec => l_control_rec,
793 p_line_tbl => l_Req_line_tbl,
794 p_qual_tbl => l_Req_qual_tbl,
795 p_line_attr_tbl => l_Req_line_attr_tbl,
796 p_line_detail_tbl => l_req_line_detail_tbl,
797 p_line_detail_qual_tbl => l_req_line_detail_qual_tbl,
798 p_line_detail_attr_tbl => l_req_line_detail_attr_tbl,
799 p_related_lines_tbl => l_req_related_lines_tbl,
800 x_line_tbl => lx_req_line_tbl,
801 x_line_qual => lx_Req_qual_tbl,
802 x_line_attr_tbl => lx_Req_line_attr_tbl,
803 x_line_detail_tbl => lx_req_line_detail_tbl,
804 x_line_detail_qual_tbl => lx_req_line_detail_qual_tbl,
805 x_line_detail_attr_tbl => lx_req_line_detail_attr_tbl,
806 x_related_lines_tbl => lx_req_related_lines_tbl,
807 x_return_status => x_return_status,
808 x_return_status_text => l_return_status_text);
809
810 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
811 aso_debug_pub.add('ASO_PRICING_INT:Price Request Status from Pricing Item:'||x_return_status,1,'Y');
812 END IF;
813
814 ASO_PRICING_INT.G_LINE_REC := NULL;
815 ASO_PRICING_INT.G_HEADER_REC := NULL;
816
817 i := lx_req_line_tbl.FIRST;
818 WHILE i IS NOT NULL LOOP
819 lx_req_line_rec := lx_req_line_tbl(i);
820 If lx_req_line_rec.status_code in(QP_PREQ_GRP.g_status_invalid_price_list,
821 QP_PREQ_GRP.g_sts_lhs_not_found,
822 QP_PREQ_GRP.g_status_formula_error,QP_PREQ_GRP.g_status_other_errors,
823 fnd_api.g_ret_sts_unexp_error,fnd_api.g_ret_sts_error,
824 QP_PREQ_GRP.g_status_calc_error,QP_PREQ_GRP.g_status_uom_failure,
825 QP_PREQ_GRP.g_status_invalid_uom,QP_PREQ_GRP.g_status_dup_price_list,
826 QP_PREQ_GRP.g_status_invalid_uom_conv,QP_PREQ_GRP.g_status_invalid_incomp,
827 QP_PREQ_GRP.g_status_best_price_eval_error,
828 QP_PREQ_PUB.g_back_calculation_sts) THEN
829 x_return_status := FND_API.G_RET_STS_ERROR;
830 END IF;
831
832 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
833 aso_debug_pub.add('ASO_PRICING_INT:Fnd_Profile Value for GSA:'
834 ||FND_PROFILE.value('ASO_GSA_PRICING'), 1, 'N');
835 aso_debug_pub.add('ASO_PRICING_INT:After price request in pricing_item for line id '
836 ||lx_req_line_rec.line_id ||'status code '||lx_req_line_rec.status_code, 1, 'N');
837 END IF;
838
839 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
840 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
842 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
843 FND_MESSAGE.Set_Name('ASO', 'ASO_API_UNEXP_ERROR');
844 FND_MESSAGE.Set_Token('ROW', 'ASO_PRICING_INT AFTER PRICING CALL', TRUE);
845 FND_MSG_PUB.ADD;
846 END IF;
847 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
848 ELSE
849 l_message_text := lx_req_line_rec.status_code || ': '||lx_req_line_rec.status_text;
850 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
851 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR');
852 FND_MESSAGE.Set_Token('MSG_TXT', substr(l_message_text,1,255), FALSE);
853 FND_MSG_PUB.ADD;
854 END IF;
855 END IF;
856 lv_return_status := x_return_status;
857 END IF;
858 i := lx_req_line_tbl.NEXT(i);
859 END LOOP;
860 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
861 aso_debug_pub.add('ASO_PRICING_INT:Before Copy_Request_To_Line in pricing_item', 1, 'Y');
862 END IF;
863 ASO_PRICING_CALLBACK_PVT.Copy_Request_To_Line (
864 p_req_line_tbl => lx_req_line_tbl,
865 p_req_line_qual => lx_Req_qual_tbl,
866 p_req_line_attr_tbl => lx_Req_line_attr_tbl,
867 p_req_line_detail_tbl => lx_req_line_detail_tbl,
868 p_req_line_detail_qual_tbl => lx_req_line_detail_qual_tbl,
869 p_req_line_detail_attr_tbl => lx_req_line_detail_attr_tbl,
870 p_req_related_lines_tbl => lx_req_related_lines_tbl,
871 p_qte_line_rec => p_qte_line_rec,
872 p_qte_line_dtl_rec => p_qte_line_dtl_rec,
873 x_qte_line_tbl => x_qte_line_tbl,
874 x_qte_line_dtl_tbl => x_qte_line_dtl_tbl,
875 x_price_adj_tbl => x_price_adj_tbl,
876 x_price_adj_attr_tbl => x_price_adj_attr_tbl,
877 x_price_adj_rltship_tbl => x_price_adj_rltship_tbl);
878 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
879 aso_debug_pub.add('ASO_PRICING_INT:after Copy_Request_To_Line in pricing_item', 1, 'Y');
880 END IF;
881
882 -- Standard check for p_commit
883 IF FND_API.to_Boolean( p_commit )
884 THEN
885 COMMIT WORK;
886 END IF;
887
888 FND_MSG_PUB.Count_And_Get
889 ( p_count => x_msg_count,
890 p_data => x_msg_data
891 );
892 IF lv_return_status <> FND_API.G_RET_STS_SUCCESS THEN
893 RAISE FND_API.G_EXC_ERROR;
894 End If;
895
896
897 EXCEPTION
898 WHEN FND_API.G_EXC_ERROR THEN
899 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
900 P_API_NAME => L_API_NAME
901 ,P_PKG_NAME => G_PKG_NAME
902 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
903 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
904 ,P_SQLCODE => SQLCODE
905 ,P_SQLERRM => SQLERRM
906 ,X_MSG_COUNT => X_MSG_COUNT
907 ,X_MSG_DATA => X_MSG_DATA
908 ,X_RETURN_STATUS => X_RETURN_STATUS);
909
910 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
911 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
912 P_API_NAME => L_API_NAME
913 ,P_PKG_NAME => G_PKG_NAME
914 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
915 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
916 ,P_SQLCODE => SQLCODE
917 ,P_SQLERRM => SQLERRM
918 ,X_MSG_COUNT => X_MSG_COUNT
919 ,X_MSG_DATA => X_MSG_DATA
920 ,X_RETURN_STATUS => X_RETURN_STATUS);
921
922 WHEN OTHERS THEN
923 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
924 P_API_NAME => L_API_NAME
925 ,P_PKG_NAME => G_PKG_NAME
926 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
927 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
928 ,P_SQLCODE => SQLCODE
929 ,P_SQLERRM => SQLERRM
930 ,X_MSG_COUNT => X_MSG_COUNT
931 ,X_MSG_DATA => X_MSG_DATA
932 ,X_RETURN_STATUS => X_RETURN_STATUS);
933
934 END Pricing_Item;
935
936
937 PROCEDURE Pricing_Order(
938 P_Api_Version_Number IN NUMBER,
939 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
940 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
941 p_control_rec IN PRICING_CONTROL_REC_TYPE,
942 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
943 p_hd_shipment_rec IN ASO_QUOTE_PUB.Shipment_Rec_Type
944 := ASO_QUOTE_PUB.G_Miss_Shipment_Rec,
945 p_hd_price_attr_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
946 := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl,
947 p_qte_line_tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
948 p_line_rltship_tbl IN ASO_QUOTE_PUB.Line_Rltship_Tbl_Type
949 := ASO_QUOTE_PUB.G_Miss_Line_Rltship_Tbl,
950 p_qte_line_dtl_tbl IN ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type
951 := ASO_QUOTE_PUB.G_Miss_Qte_Line_Dtl_Tbl,
952 p_ln_shipment_tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
953 := ASO_QUOTE_PUB.G_Miss_Shipment_Tbl,
954 p_ln_price_attr_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
955 := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl,
956 x_qte_header_rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
957 x_qte_line_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
958 x_qte_line_dtl_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type,
959 x_price_adj_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Price_Adj_Tbl_Type,
960 x_price_adj_attr_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type,
961 x_price_adj_rltship_tbl OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type,
962 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
963 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
964 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
965 IS
966 l_api_name CONSTANT VARCHAR2(30) := 'Pricing_Order';
967 l_api_version_number CONSTANT NUMBER := 1.0;
968 l_request_type VARCHAR2(60);
969 l_pricing_event VARCHAR2(30);
970 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
971 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
972 lx_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
973
974 BEGIN
975 -- Standard Start of API savepoint
976 SAVEPOINT PRICING_ORDER_PVT;
977
978 -- Standard call to check for call compatibility.
979 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
980 p_api_version_number,
981 l_api_name,
982 G_PKG_NAME)
983 THEN
984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
985 END IF;
986
987 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
988
989 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
990 aso_debug_pub.add('ASO_PRICING_INT:Start of Pricing Order.....',1,'Y');
991 END IF;
992
993 -- Initialize message list if p_init_msg_list is set to TRUE.
994 IF FND_API.to_Boolean( p_init_msg_list )
995 THEN
996 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
997 aso_debug_pub.add('ASO_PRICING_INT:Begin FND_API.to_Boolean'||p_init_msg_list, 1, 'Y');
998 END IF;
999 FND_MSG_PUB.initialize;
1000 END IF;
1001
1002 x_return_status := FND_API.G_RET_STS_SUCCESS;
1003
1004 ASO_PRICING_CORE_PVT.Initialize_Global_Tables;
1005
1006 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1007 aso_debug_pub.add('ASO_PRICING_INT:p_control_rec.request_type:'||p_control_rec.request_type,1,'Y');
1008 aso_debug_pub.add('ASO_PRICING_INT:p_control_rec.pricing_event:'||p_control_rec.pricing_event,1,'Y');
1009 END IF;
1010 l_request_type := p_control_rec.request_type;
1011 l_pricing_event := p_control_rec.pricing_event;
1012
1013 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1014 aso_debug_pub.add('ASO_PRICING_INT:Pricing Order Code Path Determination Flag - PRICE_CONFIG_FLAG:'
1015 || p_control_rec.PRICE_CONFIG_FLAG,1,'Y');
1016 aso_debug_pub.add('ASO_PRICING_INT:p_qte_line_tbl.count:'||nvl(p_qte_line_tbl.count,0),1,'Y');
1017 END IF;
1018 If p_control_rec.PRICE_CONFIG_FLAG = 'Y' then
1019 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1020 aso_debug_pub.add('ASO_PRICING_INT:Before Pricing Request Configurator Code Path Begins',1,'Y');
1021 END IF;
1022
1023 ASO_PRICING_CALLBACK_PVT.Config_Callback_Pricing_Order(
1024 P_Api_Version_Number => P_Api_Version_Number,
1025 P_Init_Msg_List => FND_API.G_FALSE,
1026 P_Commit => FND_API.G_FALSE,
1027 p_control_rec => p_control_rec,
1028 p_qte_header_rec => p_qte_header_rec,
1029 p_hd_shipment_rec => p_hd_shipment_rec,
1030 p_hd_price_attr_tbl => p_hd_price_attr_tbl,
1031 p_qte_line_tbl => p_qte_line_tbl,
1032 p_line_rltship_tbl => p_line_rltship_tbl,
1033 p_qte_line_dtl_tbl => p_qte_line_dtl_tbl,
1034 p_ln_shipment_tbl => p_ln_shipment_tbl,
1035 p_ln_price_attr_tbl => p_ln_price_attr_tbl,
1036 x_qte_header_rec => x_qte_header_rec,
1037 x_qte_line_tbl => x_qte_line_tbl,
1038 x_qte_line_dtl_tbl => x_qte_line_dtl_tbl,
1039 x_price_adj_tbl => x_price_adj_tbl,
1040 x_price_adj_attr_tbl => x_price_adj_attr_tbl,
1041 x_price_adj_rltship_tbl => x_price_adj_rltship_tbl,
1042 x_return_status => x_return_status,
1043 x_msg_count => x_msg_count,
1044 x_msg_data => x_msg_data);
1045
1046 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1047 RAISE FND_API.G_EXC_ERROR;
1048 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1049 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1050 END IF;
1051
1052 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1053 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');
1054 If x_qte_line_tbl.count > 0 then
1055 For i in 1..x_qte_line_tbl.count loop
1056 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');
1057 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');
1058 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');
1059 End Loop;
1060 End If;
1061
1062 END IF;-- ASO_DEBUG_PUB.G_Debug_Flag = 'Y'
1063
1064 Else
1065 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1066 aso_debug_pub.add('ASO_PRICING_INT: p_control_rec.price_mode: '||NVL(p_control_rec.price_mode,'NULL'),1,'Y');
1067 END IF;
1068
1069 --Non Configurator Code Path
1070
1071 If NVL(p_control_rec.price_mode,'ENTIRE_QUOTE') = 'ENTIRE_QUOTE' then
1072
1073 ASO_PRICING_FLOWS_PVT.Price_Entire_Quote(
1074 P_Api_Version_Number => P_Api_Version_Number,
1075 P_Init_Msg_List => FND_API.G_FALSE,
1076 P_Commit => FND_API.G_FALSE,
1077 p_control_rec => p_control_rec,
1078 p_qte_header_rec => p_qte_header_rec,
1079 p_hd_shipment_rec => p_hd_shipment_rec,
1080 p_qte_line_tbl => p_qte_line_tbl,
1081 x_qte_line_tbl => lx_qte_line_tbl,
1082 x_return_status => x_return_status,
1083 x_msg_count => x_msg_count,
1084 x_msg_data => x_msg_data);
1085
1086 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1087 RAISE FND_API.G_EXC_ERROR;
1088 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1089 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1090 END IF;
1091
1092 ELSIF (p_control_rec.price_mode = 'CHANGE_LINE') then
1093 -- Change Line logic code path
1094
1095 ASO_PRICING_FLOWS_PVT.Price_Quote_With_Change_Lines(
1096 P_Api_Version_Number => P_Api_Version_Number,
1097 P_Init_Msg_List => FND_API.G_FALSE,
1098 P_Commit => FND_API.G_FALSE,
1099 p_control_rec => p_control_rec,
1100 p_qte_header_rec => p_qte_header_rec,
1101 p_hd_shipment_rec => p_hd_shipment_rec,
1102 p_qte_line_tbl => p_qte_line_tbl,
1103 x_qte_line_tbl => lx_qte_line_tbl,
1104 x_return_status => x_return_status,
1105 x_msg_count => x_msg_count,
1106 x_msg_data => x_msg_data);
1107
1108 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1109 RAISE FND_API.G_EXC_ERROR;
1110 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1112 END IF;
1113
1114 Else
1115 --p_control_rec.price_mode = 'QUOTE_LINE'
1116
1117 ASO_PRICING_FLOWS_PVT.Price_Quote_Line(
1118 P_Api_Version_Number => P_Api_Version_Number,
1119 P_Init_Msg_List => FND_API.G_FALSE,
1120 P_Commit => FND_API.G_FALSE,
1121 p_control_rec => p_control_rec,
1122 p_qte_header_rec => p_qte_header_rec,
1123 p_hd_shipment_rec => p_hd_shipment_rec,
1124 p_qte_line_tbl => p_qte_line_tbl,
1125 x_return_status => x_return_status,
1126 x_msg_count => x_msg_count,
1127 x_msg_data => x_msg_data);
1128
1129 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1130 RAISE FND_API.G_EXC_ERROR;
1131 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1133 END IF;
1134
1135 End If;--NVL(p_control_rec.price_mode,'ENTIRE_QUOTE') = 'ENTIRE_QUOTE'
1136
1137
1138 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1139 aso_debug_pub.add('ASO_PRICING_INT:After Entire_Quote:lx_qte_line_tbl.count:'||nvl(lx_qte_line_tbl.count,0),1,'Y');
1140 END IF;
1141
1142 x_qte_line_tbl:= p_qte_line_tbl;
1143
1144 If lx_qte_line_tbl.count > 0 then
1145 For i in 1..lx_qte_line_tbl.count loop
1146 x_qte_line_tbl(x_qte_line_tbl.count+1) := lx_qte_line_tbl(i);
1147 end loop;
1148 end If;
1149 End if;--p_control_rec.PRICE_CONFIG_FLAG = 'Y'
1150
1151 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1152 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');
1153 END IF;
1154
1155 FND_MSG_PUB.Count_And_Get
1156 ( p_encoded => 'F',
1157 p_count => x_msg_count,
1158 p_data => x_msg_data
1159 );
1160
1161 for l in 1 .. x_msg_count loop
1162 x_msg_data := fnd_msg_pub.get( p_msg_index => l, p_encoded => 'F');
1163 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1164 aso_debug_pub.add('ASO_PRICING_INT:Messge count and get '||x_msg_data, 1, 'Y');
1165 aso_debug_pub.add('ASO_PRICING_INT:Messge count and get '||x_msg_count, 1, 'Y');
1166 END IF;
1167 end loop;
1168
1169 EXCEPTION
1170 WHEN FND_API.G_EXC_ERROR THEN
1171 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1172 aso_debug_pub.add('ASO_PRICING_INT:after inside EXCEPTION return status'||x_return_status, 1, 'Y');
1173 END IF;
1174 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1175 P_API_NAME => L_API_NAME
1176 ,P_PKG_NAME => G_PKG_NAME
1177 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1178 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1179 ,P_SQLCODE => SQLCODE
1180 ,P_SQLERRM => SQLERRM
1181 ,X_MSG_COUNT => X_MSG_COUNT
1182 ,X_MSG_DATA => X_MSG_DATA
1183 ,X_RETURN_STATUS => X_RETURN_STATUS);
1184
1185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1186 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1187 P_API_NAME => L_API_NAME
1188 ,P_PKG_NAME => G_PKG_NAME
1189 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1190 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1191 ,P_SQLCODE => SQLCODE
1192 ,P_SQLERRM => SQLERRM
1193 ,X_MSG_COUNT => X_MSG_COUNT
1194 ,X_MSG_DATA => X_MSG_DATA
1195 ,X_RETURN_STATUS => X_RETURN_STATUS);
1196
1197 WHEN OTHERS THEN
1198 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1199 P_API_NAME => L_API_NAME
1200 ,P_PKG_NAME => G_PKG_NAME
1201 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1202 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1203 ,P_SQLCODE => SQLCODE
1204 ,P_SQLERRM => SQLERRM
1205 ,X_MSG_COUNT => X_MSG_COUNT
1206 ,X_MSG_DATA => X_MSG_DATA
1207 ,X_RETURN_STATUS => X_RETURN_STATUS);
1208
1209
1210 END Pricing_Order;
1211
1212
1213 PROCEDURE Pricing_Item (
1214 P_Api_Version_Number IN NUMBER,
1215 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1216 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1217 p_control_rec IN PRICING_CONTROL_REC_TYPE,
1218 p_qte_line_id IN NUMBER,
1219 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1220 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1221 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1222 IS
1223 l_api_name CONSTANT VARCHAR2(30) := 'Pricing_Item';
1224 l_api_version_number CONSTANT NUMBER := 1.0;
1225 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
1226 l_qte_header_id NUMBER;
1227 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1228 l_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1229 l_hd_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1230 l_hd_price_attr_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1231 l_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type;
1232 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1233 l_ln_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1234 l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1235 l_ln_price_attr_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1236 lx_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1237 lx_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1238 lx_price_adj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1239 lx_price_adj_attr_tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
1240 lx_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
1241
1242 l_price_list_id NUMBER;
1243 l_CURRENCY_CODE VARCHAR2(15);
1244
1245
1246 CURSOR c_header_id IS
1247 SELECT QUOTE_HEADER_ID
1248 FROM ASO_QUOTE_LINES_ALL
1249 WHERE QUOTE_LINE_ID = p_qte_line_id;
1250
1251 CURSOR c_list_id(l_qte_header_id NUMBER) IS
1252 SELECT price_list_id , CURRENCY_CODE
1253 FROM ASO_QUOTE_HEADERS_ALL
1254 WHERE QUOTE_HEADER_ID = l_qte_header_id;
1255
1256 BEGIN
1257
1258 -- Standard Start of API savepoint
1259 SAVEPOINT PRICING_ITEM_PVT;
1260
1261 -- Standard call to check for call compatibility.
1262 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1263 p_api_version_number,
1264 l_api_name,
1265 G_PKG_NAME)
1266 THEN
1267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1268 END IF;
1269
1270
1271 -- Initialize message list if p_init_msg_list is set to TRUE.
1272 IF FND_API.to_Boolean( p_init_msg_list )
1273 THEN
1274 FND_MSG_PUB.initialize;
1275 END IF;
1276
1277 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
1278
1279 x_return_status := FND_API.G_RET_STS_SUCCESS;
1280 l_qte_line_rec := ASO_UTILITY_PVT.Query_Qte_Line_Row(p_qte_line_id);
1281 l_qte_header_id := l_qte_line_rec.QUOTE_HEADER_ID;
1282 l_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(l_qte_header_id);
1283 l_hd_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows(l_qte_header_id, NULL);
1284
1285 IF l_hd_shipment_tbl.count = 1 THEN
1286 l_hd_shipment_rec := l_hd_shipment_tbl(1);
1287 END IF;
1288 l_hd_price_attr_tbl := ASO_UTILITY_PVT.Query_Price_Attr_Rows(l_qte_header_id, null);
1289 l_qte_line_dtl_tbl := ASO_UTILITY_PVT.Query_Line_Dtl_Rows(p_qte_line_id);
1290
1291 IF l_qte_line_dtl_tbl.count = 1 THEN
1292 l_qte_line_dtl_rec := l_qte_line_dtl_tbl(1);
1293 END IF;
1294 l_ln_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows(l_qte_header_id, p_QTE_LINE_ID);
1295
1296 IF l_ln_shipment_tbl.count = 1 THEN
1297 l_ln_shipment_rec := l_ln_shipment_tbl(1);
1298 END IF;
1299 l_ln_price_attr_tbl := ASO_UTILITY_PVT.Query_Price_Attr_Rows(l_qte_header_id, p_qte_line_id);
1300 --Code changed on 04/18/2000
1301
1302 OPEN c_header_id;
1303 FETCH c_header_id INTO l_qte_header_id;
1304 CLOSE c_header_id;
1305
1306 OPEN c_list_id (l_qte_header_id);
1307 FETCH c_list_id INTO l_price_list_id,l_CURRENCY_CODE;
1308 CLOSE c_list_id;
1309
1310 IF l_qte_line_rec.price_list_id is NULL or l_qte_line_rec.price_list_id = FND_API.G_MISS_NUM THEN
1311 l_qte_line_rec.price_list_id := l_price_list_id ;
1312 ELSE
1313 l_qte_header_rec.price_list_id := NULL;
1314 END IF;
1315
1316 IF l_qte_line_rec.CURRENCY_CODE is NULL or l_qte_line_rec.CURRENCY_CODE = FND_API.G_MISS_CHAR THEN
1317 l_qte_line_rec.CURRENCY_CODE := l_CURRENCY_CODE ;
1318 END IF;
1319
1320 Pricing_Item (
1321 P_Api_Version_Number => 1,
1322 P_Init_Msg_List => FND_API.G_FALSE,
1323 P_Commit => FND_API.G_FALSE,
1324 p_control_rec => p_control_rec,
1325 p_qte_header_rec => l_qte_header_rec,
1326 p_hd_shipment_rec => l_hd_shipment_rec,
1327 p_hd_price_attr_tbl => l_hd_price_attr_tbl,
1328 p_qte_line_rec => l_qte_line_rec,
1329 p_qte_line_dtl_rec => l_qte_line_dtl_rec,
1330 p_ln_shipment_rec => l_ln_shipment_rec,
1331 p_ln_price_attr_tbl => l_ln_price_attr_tbl,
1332 x_qte_line_tbl => lx_qte_line_tbl,
1333 x_qte_line_dtl_tbl => lx_qte_line_dtl_tbl,
1334 x_price_adj_tbl => lx_price_adj_tbl,
1335 x_price_adj_attr_tbl => lx_price_adj_attr_tbl,
1336 x_price_adj_rltship_tbl => lx_price_adj_rltship_tbl,
1337 x_return_status => x_return_status,
1338 x_msg_data => x_msg_data,
1339 x_msg_count => x_msg_count);
1340
1341 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1342 RAISE FND_API.G_EXC_ERROR;
1343 END IF;
1344
1345 /* Changed the delete statement as per bug 1874082 */
1346 /*Removed the Complex Delete Statement with UNION bug 2585468 */
1347 DELETE from ASO_PRICE_ADJ_RELATIONSHIPS
1348 WHERE quote_line_id = p_qte_line_id;
1349 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1350 aso_debug_pub.add('ASO_PRICING_INT:Rltd adj Lines deleted '||sql%ROWCOUNT,1,'Y');
1351 END IF;
1352
1353
1354 DELETE FROM ASO_PRICE_ADJUSTMENTS
1355 WHERE quote_line_id = p_qte_line_id;
1356 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1357 aso_debug_pub.add('ASO_PRICING_INT:ADJ Lines deleted '||sql%ROWCOUNT,1,'Y');
1358 END IF;
1359
1360 FOR i IN 1..lx_qte_line_tbl.count LOOP
1361 l_qte_line_rec := ASO_UTILITY_PVT.Query_Qte_Line_Row(lx_qte_line_tbl(i).quote_line_id);
1362 lx_qte_line_tbl(i).price_list_id := l_qte_line_rec.price_list_id;
1363 END LOOP;
1364
1365 ASO_PRICING_CALLBACK_PVT.Update_Quote_Rows (
1366 p_qte_line_tbl => lx_qte_line_tbl,
1367 p_qte_line_dtl_tbl => lx_qte_line_dtl_tbl,
1368 p_price_adj_tbl => lx_price_adj_tbl,
1369 p_price_adj_attr_tbl => lx_price_adj_attr_tbl,
1370 p_price_adj_rltship_tbl => lx_price_adj_rltship_tbl);
1371
1372 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1373 aso_debug_pub.add('ASO_PRICING_INT:Pricing Item Ends if the second Pricing Item was called...',1,'Y');
1374 END IF;
1375
1376 -- Standard check for p_commit
1377 IF FND_API.to_Boolean( p_commit )
1378 THEN
1379 COMMIT WORK;
1380 END IF;
1381
1382 EXCEPTION
1383 WHEN FND_API.G_EXC_ERROR THEN
1384 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1385 P_API_NAME => L_API_NAME
1386 ,P_PKG_NAME => G_PKG_NAME
1387 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1388 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1389 ,P_SQLCODE => SQLCODE
1390 ,P_SQLERRM => SQLERRM
1391 ,X_MSG_COUNT => X_MSG_COUNT
1392 ,X_MSG_DATA => X_MSG_DATA
1393 ,X_RETURN_STATUS => X_RETURN_STATUS);
1394
1395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1396 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1397 P_API_NAME => L_API_NAME
1398 ,P_PKG_NAME => G_PKG_NAME
1399 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1400 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1401 ,P_SQLCODE => SQLCODE
1402 ,P_SQLERRM => SQLERRM
1403 ,X_MSG_COUNT => X_MSG_COUNT
1404 ,X_MSG_DATA => X_MSG_DATA
1405 ,X_RETURN_STATUS => X_RETURN_STATUS);
1406
1407 WHEN OTHERS THEN
1408 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1409 P_API_NAME => L_API_NAME
1410 ,P_PKG_NAME => G_PKG_NAME
1411 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1412 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1413 ,P_SQLCODE => SQLCODE
1414 ,P_SQLERRM => SQLERRM
1415 ,X_MSG_COUNT => X_MSG_COUNT
1416 ,X_MSG_DATA => X_MSG_DATA
1417 ,X_RETURN_STATUS => X_RETURN_STATUS);
1418
1419
1420 END Pricing_Item;
1421
1422
1423 PROCEDURE Pricing_Order (
1424 P_Api_Version_Number IN NUMBER,
1425 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1426 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1427 p_control_rec IN PRICING_CONTROL_REC_TYPE,
1428 p_qte_line_tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type,
1429 p_qte_header_id IN NUMBER,
1430 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1431 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1432 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1433 IS
1434
1435 l_api_name CONSTANT VARCHAR2(30) := 'Pricing_Order';
1436 l_api_version_number CONSTANT NUMBER := 1.0;
1437 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
1438 l_req_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
1439 l_Req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
1440 l_Req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
1441 l_Req_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
1442 l_req_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
1443 l_req_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
1444 l_req_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
1445 l_hd_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
1446 l_hd_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
1447 l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
1448 l_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
1449 lx_req_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
1450 lx_req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
1451 lx_req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
1452 lx_req_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
1453 lx_req_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
1454 lx_req_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
1455 lx_req_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
1456 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1457 l_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1458 l_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
1459 l_price_attr_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1460 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1461 l_qte_line_id NUMBER;
1462 l_qte_line_dtl_rec ASO_QUOTE_PUB.Qte_Line_Dtl_Rec_Type;
1463 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1464 l_return_status VARCHAR2(1);
1465 l_return_status_text VARCHAR2(2000);
1466 l_request_type VARCHAR2(60);
1467 l_pricing_event VARCHAR2(30);
1468 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_rec_Type;
1469 lx_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
1470 lx_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
1471 lx_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
1472 lx_price_adj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
1473 lx_price_adj_attr_tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
1474 lx_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
1475 l_message_text VARCHAR2(2000);
1476 i BINARY_INTEGER;
1477 ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
1478 l_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
1479 l_ln_price_attr_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
1480 lx_return_status VARCHAR2(50);
1481 lx_msg_count NUMBER;
1482 lx_msg_data VARCHAR2(2000);
1483
1484 BEGIN
1485 -- Standard Start of API savepoint
1486 SAVEPOINT PRICING_ORDER_PVT;
1487
1488 -- Standard call to check for call compatibility.
1489 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1490 p_api_version_number,
1491 l_api_name,
1492 G_PKG_NAME)
1493 THEN
1494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1495 END IF;
1496
1497
1498 -- Initialize message list if p_init_msg_list is set to TRUE.
1499 IF FND_API.to_Boolean( p_init_msg_list )
1500 THEN
1501 FND_MSG_PUB.initialize;
1502 END IF;
1503
1504 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1505 aso_debug_pub.add('ASO_PRICING_INT:In Pricing Order with hdr Id', 1, 'Y');
1506 END IF;
1507
1508 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
1509
1510 x_return_status := FND_API.G_RET_STS_SUCCESS;
1511 l_request_type := p_control_rec.request_type;
1512 l_pricing_event := p_control_rec.pricing_event;
1513
1514 l_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(p_qte_header_id);
1515 l_shipment_tbl := ASO_UTILITY_PVT.Query_Shipment_Rows(p_qte_header_id,NULL);
1516 IF l_shipment_tbl.count = 1 THEN
1517 l_shipment_rec := l_shipment_tbl(1);
1518 END IF;
1519 l_price_attr_tbl := ASO_UTILITY_PVT.Query_Price_Attr_Rows(p_qte_header_id, null);
1520 ASO_PRICING_INT.Pricing_Order(
1521 P_Api_Version_Number => 1.0,
1522 P_Init_Msg_List => FND_API.G_FALSE,
1523 P_Commit => FND_API.G_FALSE,
1524 p_control_rec => p_control_rec,
1525 p_qte_header_rec => l_qte_header_rec,
1526 p_hd_shipment_rec => l_shipment_rec,
1527 p_hd_price_attr_tbl => l_price_attr_tbl,
1528 p_qte_line_tbl => p_qte_line_tbl,
1529 p_line_rltship_tbl => l_line_rltship_tbl,
1530 p_qte_line_dtl_tbl => l_qte_line_dtl_tbl,
1531 p_ln_shipment_tbl => ln_shipment_tbl,
1532 p_ln_price_attr_tbl => l_ln_price_attr_tbl,
1533 x_qte_header_rec => lx_qte_header_rec,
1534 x_qte_line_tbl => lx_qte_line_tbl,
1535 x_qte_line_dtl_tbl => lx_qte_line_dtl_tbl,
1536 x_price_adj_tbl => lx_price_adj_tbl,
1537 x_price_adj_attr_tbl => lx_price_adj_attr_tbl,
1538 x_price_adj_rltship_tbl => lx_price_adj_rltship_tbl,
1539 x_return_status => x_return_status,
1540 x_msg_count => x_msg_count,
1541 x_msg_data => x_msg_data );
1542
1543 -- Standard check for p_commit
1544 IF FND_API.to_Boolean( p_commit )
1545 THEN
1546 COMMIT WORK;
1547 END IF;
1548
1549 FND_MSG_PUB.Count_And_Get
1550 ( p_count => x_msg_count,
1551 p_data => x_msg_data
1552 );
1553
1554 EXCEPTION
1555 WHEN FND_API.G_EXC_ERROR THEN
1556 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1557 P_API_NAME => L_API_NAME
1558 ,P_PKG_NAME => G_PKG_NAME
1559 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1560 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1561 ,P_SQLCODE => SQLCODE
1562 ,P_SQLERRM => SQLERRM
1563 ,X_MSG_COUNT => X_MSG_COUNT
1564 ,X_MSG_DATA => X_MSG_DATA
1565 ,X_RETURN_STATUS => X_RETURN_STATUS);
1566
1567 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1568 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1569 P_API_NAME => L_API_NAME
1570 ,P_PKG_NAME => G_PKG_NAME
1571 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1572 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1573 ,P_SQLCODE => SQLCODE
1574 ,P_SQLERRM => SQLERRM
1575 ,X_MSG_COUNT => X_MSG_COUNT
1576 ,X_MSG_DATA => X_MSG_DATA
1577 ,X_RETURN_STATUS => X_RETURN_STATUS);
1578
1579 WHEN OTHERS THEN
1580 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1581 P_API_NAME => L_API_NAME
1582 ,P_PKG_NAME => G_PKG_NAME
1583 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1584 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1585 ,P_SQLCODE => SQLCODE
1586 ,P_SQLERRM => SQLERRM
1587 ,X_MSG_COUNT => X_MSG_COUNT
1588 ,X_MSG_DATA => X_MSG_DATA
1589 ,X_RETURN_STATUS => X_RETURN_STATUS);
1590
1591 END Pricing_Order;
1592
1593
1594 Procedure Delete_Promotion (
1595 P_Api_Version_Number IN NUMBER,
1596 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1597 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1598 p_price_attr_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type,
1599 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1600 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1601 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1602 IS
1603 BEGIN
1604 ASO_PRICING_CORE_PVT.Delete_Promotion (
1605 P_Api_Version_Number => P_Api_Version_Number,
1606 P_Init_Msg_List => P_Init_Msg_List,
1607 P_Commit => P_Commit,
1608 p_price_attr_tbl => p_price_attr_tbl,
1609 x_return_status => x_return_status,
1610 x_msg_count => x_msg_count,
1611 x_msg_data => x_msg_data);
1612
1613 END Delete_Promotion;
1614
1615 -- hagrawal_start Funtion added
1616 FUNCTION Get_Cust_Po(
1617 p_qte_header_id number
1618 ) RETURN VARCHAR2
1619 IS
1620 Cursor get_po is SELECT payment_ref_number from aso_payments
1621 WHERE
1622 payment_type_code ='PO' and quote_header_id = p_qte_header_id and quote_line_id is NULL;
1623 Customer_PO VARCHAR2(240);
1624
1625 BEGIN
1626 OPEN get_po;
1627 fetch get_po into Customer_Po;
1628 CLOSE get_po;
1629 RETURN Customer_Po;
1630 END Get_Cust_Po;
1631
1632 FUNCTION Get_line_Cust_Po(
1633 p_qte_line_id number
1634 ) RETURN VARCHAR2
1635 IS
1636 Cursor get_po is SELECT payment_ref_number from aso_payments
1637 WHERE
1638 payment_type_code ='PO' and quote_line_id = p_qte_line_id;
1639 Customer_PO VARCHAR2(240);
1640
1641 BEGIN
1642 OPEN get_po;
1643 fetch get_po into Customer_Po;
1644 CLOSE get_po;
1645 RETURN Customer_Po;
1646 END Get_line_Cust_Po;
1647
1648 FUNCTION Get_Request_date(
1649 p_qte_header_id number
1650 ) RETURN DATE
1651 IS
1652
1653 Cursor get_req_date is SELECT request_date from aso_shipments
1654 WHERE
1655 quote_header_id = p_qte_header_id and quote_line_id is NULL;
1656 l_request_date DATE;
1657 x_request_date DATE;
1658 BEGIN
1659 OPEN get_req_date;
1660 fetch get_req_date into l_request_date;
1661 CLOSE get_req_date;
1662
1663 x_request_date := FND_DATE.DATE_TO_CANONICAL(l_request_date);
1664 RETURN x_request_date;
1665 END Get_Request_date;
1666
1667 FUNCTION Get_Line_Request_date(
1668 p_qte_line_id number
1669 ) RETURN DATE
1670 IS
1671
1672 Cursor get_req_date is SELECT request_date from aso_shipments
1673 WHERE quote_line_id = p_qte_line_id ;
1674 l_request_date DATE;
1675 x_request_date DATE;
1676 BEGIN
1677 OPEN get_req_date;
1678 fetch get_req_date into l_request_date;
1679 CLOSE get_req_date;
1680
1681 x_request_date := FND_DATE.DATE_TO_CANONICAL(l_request_date);
1682 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1683 aso_debug_pub.add('ASO_PRICING_INT:In Get_Line_Request_date'||x_request_date, 1, 'Y');
1684 END IF;
1685 RETURN x_request_date;
1686 END Get_line_Request_date;
1687
1688
1689 FUNCTION Get_Freight_term(
1690 p_qte_header_id number
1691 ) RETURN DATE
1692 IS
1693
1694 Cursor get_frieght is SELECT FREIGHT_TERMS_CODE from aso_shipments
1695 WHERE
1696 quote_header_id = p_qte_header_id and quote_line_id is NULL;
1697 l_freight_terms_code VARCHAR2(30);
1698 BEGIN
1699 OPEN get_frieght;
1700 fetch get_frieght into l_freight_terms_code;
1701 CLOSE get_frieght;
1702 RETURN l_freight_terms_code;
1703 END Get_Freight_term;
1704
1705 FUNCTION Get_line_Freight_term(
1706 p_qte_line_id number
1707 ) RETURN VARCHAR2
1708 IS
1709
1710 Cursor get_frieght is SELECT FREIGHT_TERMS_CODE from aso_shipments
1711 WHERE
1712 quote_line_id = p_qte_line_id;
1713 l_freight_terms_code VARCHAR2(30);
1714 BEGIN
1715 OPEN get_frieght;
1716 fetch get_frieght into l_freight_terms_code;
1717 CLOSE get_frieght;
1718 RETURN l_freight_terms_code;
1719 END Get_line_Freight_term;
1720
1721 FUNCTION Get_Payment_term(
1722 p_qte_header_id number
1723 ) RETURN NUMBER
1724 IS
1725
1726 Cursor get_pmnt_term is SELECT payment_term_id from aso_payments
1727 WHERE
1728 quote_header_id = p_qte_header_id and quote_line_id IS null;
1729 l_pmnt_term_id NUMBER;
1730 BEGIN
1731 OPEN get_pmnt_term;
1732 fetch get_pmnt_term into l_pmnt_term_id;
1733 CLOSE get_pmnt_term;
1734 RETURN l_pmnt_term_id;
1735 END Get_Payment_term;
1736
1737
1738 FUNCTION Get_line_Payment_term(
1739 p_qte_line_id number
1740 ) RETURN NUMBER
1741 IS
1742
1743 Cursor get_pmnt_term is SELECT payment_term_id from aso_payments
1744 WHERE
1745 quote_line_id = p_qte_line_id;
1746 l_pmnt_term_id NUMBER;
1747 BEGIN
1748 OPEN get_pmnt_term;
1749 fetch get_pmnt_term into l_pmnt_term_id;
1750 CLOSE get_pmnt_term;
1751 RETURN l_pmnt_term_id;
1752 END Get_line_Payment_term;
1753
1754
1755 End ASO_PRICING_INT;