[Home] [Help]
PACKAGE BODY: APPS.OZF_TP_UTIL_QUERIES
Source
1 PACKAGE BODY OZF_TP_UTIL_QUERIES AS
2 /* $Header: ozfvtpqb.pls 120.4 2005/12/19 13:21:15 gramanat ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30):='OZF_TP_UTIL_QUERIES';
4
5 AMS_DEBUG_HIGH_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
6 AMS_DEBUG_LOW_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
7 AMS_DEBUG_MEDIUM_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
8
9 -- ------------------------------------------------------------------
10 -- ------------------------
11 -- Public Function
12 -- ------------------------
13 -- HISTORY
14 -- created 16-DEC-2003 mkothari
15 -- Name: get_alert
16 -- Desc: Called from Dashboard Account and Product VOs to get alert.
17 -- Note: Distinct alert_types are = { MTD, QTD, YTD, BACK_ORDER,
18 -- OUTSTAND_ORDER }
19 -- ------------------------------------------------------------------
20
21 FUNCTION get_a_alert(p_object_id IN NUMBER,
22 p_object_type IN VARCHAR2,
23 p_report_date IN DATE,
24 p_resource_id IN NUMBER,
25 p_alert_type IN VARCHAR2,
26 p_alert_for IN VARCHAR2)
27 RETURN VARCHAR2
28 IS
29 x_return_value varchar2(250) ;
30
31 BEGIN
32 IF p_object_type = 'PARTY' OR p_object_type = 'BILL_TO' then
33 x_return_value := 'UNDEFINED';
34 ELSIF p_object_type = 'SHIP_TO' THEN
35 IF p_alert_type = 'MTD' THEN
36 SELECT
37 MTD_ALERT INTO x_return_value
38 FROM
39 ozf_quota_alerts
40 WHERE
41 ship_to_site_use_id = p_object_id
42 AND report_date = p_report_date
43 AND resource_id = p_resource_id
44 AND alert_for = p_alert_for;
45 ELSIF p_alert_type = 'QTD' THEN
46 SELECT
47 QTD_ALERT INTO x_return_value
48 FROM
49 ozf_quota_alerts
50 WHERE
51 ship_to_site_use_id = p_object_id
52 AND report_date = p_report_date
53 AND resource_id = p_resource_id
54 AND alert_for = p_alert_for;
55 ELSIF p_alert_type = 'YTD' THEN
56 SELECT
57 YTD_ALERT INTO x_return_value
58 FROM
59 ozf_quota_alerts
60 WHERE
61 ship_to_site_use_id = p_object_id
62 AND report_date = p_report_date
63 AND resource_id = p_resource_id
64 AND alert_for = p_alert_for;
65 ELSIF p_alert_type = 'BACK_ORDER' THEN
66 SELECT
67 BACK_ORDER_ALERT INTO x_return_value
68 FROM
69 ozf_quota_alerts
70 WHERE
71 ship_to_site_use_id = p_object_id
72 AND report_date = p_report_date
73 AND resource_id = p_resource_id
74 AND alert_for = p_alert_for;
75 ELSIF p_alert_type = 'OUTSTAND_ORDER' THEN
76 SELECT
77 OUTSTAND_ORDER_ALERT INTO x_return_value
78 FROM
79 ozf_quota_alerts
80 WHERE
81 ship_to_site_use_id = p_object_id
82 AND report_date = p_report_date
83 AND resource_id = p_resource_id
84 AND alert_for = p_alert_for;
85 END IF;
86 END IF;
87
88 RETURN (x_return_value);
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 -- dbms_output.put_line(sqlerrm(sqlcode));
93 RETURN NULL;
94 END get_a_alert;
95
96 -- ------------------------------------------------------------------
97 -- ------------------------
98 -- Public Function
99 -- ------------------------
100 -- HISTORY
101 -- created 16-DEC-2003 mkothari
102 -- Name: get_alert
103 -- Desc: Called from Dashboard Account and Product VOs to get alert.
104 -- Note: Distinct alert_types are = { MTD, QTD, YTD, BACK_ORDER,
105 -- OUTSTAND_ORDER }
106 -- ------------------------------------------------------------------
107
108 FUNCTION get_alert(p_site_use_id IN NUMBER,
109 p_cust_account_id IN NUMBER,
110 p_report_date IN DATE,
111 p_resource_id IN NUMBER,
112 p_alert_type IN VARCHAR2,
113 p_alert_for IN VARCHAR2)
114 RETURN VARCHAR2
115 IS
116 x_return_value varchar2(250) ;
117
118 BEGIN
119
120 IF p_alert_type = 'MTD' THEN
121 SELECT
122 MTD_ALERT INTO x_return_value
123 FROM
124 ozf_quota_alerts
125 WHERE
126 cust_account_id = p_cust_account_id
127 AND ship_to_site_use_id = p_site_use_id
128 AND report_date = p_report_date
129 AND resource_id = p_resource_id
130 AND alert_for = p_alert_for;
131 ELSIF p_alert_type = 'QTD' THEN
132 SELECT
133 QTD_ALERT INTO x_return_value
134 FROM
135 ozf_quota_alerts
136 WHERE
137 cust_account_id = p_cust_account_id
138 AND ship_to_site_use_id = p_site_use_id
139 AND report_date = p_report_date
140 AND resource_id = p_resource_id
141 AND alert_for = p_alert_for;
142 ELSIF p_alert_type = 'YTD' THEN
143 SELECT
144 YTD_ALERT INTO x_return_value
145 FROM
146 ozf_quota_alerts
147 WHERE
148 cust_account_id = p_cust_account_id
149 AND ship_to_site_use_id = p_site_use_id
150 AND report_date = p_report_date
151 AND resource_id = p_resource_id
152 AND alert_for = p_alert_for;
153 ELSIF p_alert_type = 'BACK_ORDER' THEN
154 SELECT
155 BACK_ORDER_ALERT INTO x_return_value
156 FROM
157 ozf_quota_alerts
158 WHERE
159 cust_account_id = p_cust_account_id
160 AND ship_to_site_use_id = p_site_use_id
161 AND report_date = p_report_date
162 AND resource_id = p_resource_id
163 AND alert_for = p_alert_for;
164 ELSIF p_alert_type = 'OUTSTAND_ORDER' THEN
165 SELECT
166 OUTSTAND_ORDER_ALERT INTO x_return_value
167 FROM
168 ozf_quota_alerts
169 WHERE
170 cust_account_id = p_cust_account_id
171 AND ship_to_site_use_id = p_site_use_id
172 AND report_date = p_report_date
173 AND resource_id = p_resource_id
174 AND alert_for = p_alert_for;
175 END IF;
176
177 RETURN (x_return_value);
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 -- dbms_output.put_line(sqlerrm(sqlcode));
182 RETURN NULL;
183 END get_alert;
184
185
186 -----------------------------------------------------------
187 -- ------------------------------------------------------------------
188 -- ------------------------
189 -- Public Function
190 -- ------------------------
191 -- HISTORY
192 -- created 16-DEC-2003 mkothari
193 -- Name: get_alert - OVERLOADED FUNCTION -- NO LONGER OVRLOADED NOW
194 -- Desc: Called from Dashboard Account and Product VOs to get alert.
195 -- Note: Distinct alert_types are = { MTD, QTD, YTD, BACK_ORDER,
196 -- OUTSTAND_ORDER }
197 -- ------------------------------------------------------------------
198
199 FUNCTION get_p_alert(p_product_attribute IN VARCHAR2,
200 p_product_attr_value IN NUMBER,
201 p_report_date IN DATE,
202 p_resource_id IN NUMBER,
203 p_alert_type IN VARCHAR2,
204 p_alert_for IN VARCHAR2)
205 RETURN VARCHAR2
206 IS
207 x_return_value varchar2(250) ;
208
209 BEGIN
210
211 IF p_alert_type = 'MTD' THEN
212 SELECT
213 MTD_ALERT INTO x_return_value
214 FROM
215 ozf_quota_alerts
216 WHERE
217 product_attribute = p_product_attribute
218 AND product_attr_value = p_product_attr_value
219 AND report_date = p_report_date
220 AND resource_id = p_resource_id
221 AND alert_for = p_alert_for;
222 ELSIF p_alert_type = 'QTD' THEN
223 SELECT
224 QTD_ALERT INTO x_return_value
225 FROM
226 ozf_quota_alerts
227 WHERE
228 product_attribute = p_product_attribute
229 AND product_attr_value = p_product_attr_value
230 AND report_date = p_report_date
231 AND resource_id = p_resource_id
232 AND alert_for = p_alert_for;
233 ELSIF p_alert_type = 'YTD' THEN
234 SELECT
235 YTD_ALERT INTO x_return_value
236 FROM
237 ozf_quota_alerts
238 WHERE
239 product_attribute = p_product_attribute
240 AND product_attr_value = p_product_attr_value
241 AND report_date = p_report_date
242 AND resource_id = p_resource_id
243 AND alert_for = p_alert_for;
244 ELSIF p_alert_type = 'BACK_ORDER' THEN
245 SELECT
246 BACK_ORDER_ALERT INTO x_return_value
247 FROM
248 ozf_quota_alerts
249 WHERE
250 product_attribute = p_product_attribute
251 AND product_attr_value = p_product_attr_value
252 AND report_date = p_report_date
253 AND resource_id = p_resource_id
254 AND alert_for = p_alert_for;
255 ELSIF p_alert_type = 'OUTSTAND_ORDER' THEN
256 SELECT
257 OUTSTAND_ORDER_ALERT INTO x_return_value
258 FROM
259 ozf_quota_alerts
260 WHERE
261 product_attribute = p_product_attribute
262 AND product_attr_value = p_product_attr_value
263 AND report_date = p_report_date
264 AND resource_id = p_resource_id
265 AND alert_for = p_alert_for;
266 END IF;
267
268 RETURN (x_return_value);
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 -- dbms_output.put_line(sqlerrm(sqlcode));
273 RETURN NULL;
274 END get_p_alert;
275
276
277 -----------------------------------------------------------
278 -- PROCEDURE
279 -- get_activity_description
280 --
281 -- HISTORY
282 --
283 ------------------------------------------------------------
284
285 FUNCTION get_party_name(p_site_use_id IN NUMBER)
286 RETURN VARCHAR2
287 is
288 CURSOR org_cursor
289 IS
290 SELECT
291 NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99) from DUAL;
292
293
294 x_return_value varchar2(250);
295 l_org_id NUMBER;
296 BEGIN
297 OPEN org_cursor;
298 FETCH org_cursor into l_org_id;
299 CLOSE org_cursor;
300
301 Select p.party_name||' '||d.location into x_return_value
302 from hz_parties p,
303 hz_cust_acct_sites c,
304 hz_cust_site_uses d,
305 hz_cust_accounts e
306 where d.site_use_id = p_site_use_id
307 and d.cust_acct_site_id = c.cust_acct_site_id
308 and e.cust_account_id = c.cust_account_id
309 and p.party_id = e.party_id;
310
311 RETURN (x_return_value);
312 EXCEPTION
313 WHEN OTHERS THEN
314 RETURN (to_char(p_site_use_id));
315
316 END;
317
318 -----------------------------------------------------------
319 -- PROCEDURE
320 -- get_activity_description
321 --
322 -- HISTORY
323 --
324 ------------------------------------------------------------
325
326 FUNCTION get_activity_description(activity_class IN VARCHAR2 DEFAULT NULL,activity_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2
327 is
328 x_return_value varchar2(250) ;
329
330 BEGIN
331 if activity_class = 'CSCH' then
332 SELECT schedule_name
333 INTO x_return_value
334 FROM ams_campaign_schedules_tl
335 WHERE schedule_id = activity_id
336 AND language = userenv('LANG');
337 elsif activity_class = 'OFFR' then
338 SELECT description
339 INTO x_return_value
340 FROM qp_list_headers_tl
341 WHERE list_header_id = activity_id
342 AND language = userenv('LANG');
343 elsif activity_class = 'CAMP' OR activity_class = 'ECAM' OR activity_class = 'TRDP' then
344 SELECT campaign_name
345 INTO x_return_value
346 FROM ams_campaigns_all_tl
347 WHERE campaign_id = activity_id
348 AND language = userenv('LANG');
349 end if;
350
351 RETURN (x_return_value);
352
353 EXCEPTION
354 WHEN OTHERS THEN
355 -- dbms_output.put_line(sqlerrm(sqlcode));
356 null;
357
358 END get_activity_description;
359
360
361 FUNCTION get_activity_access(p_object_class IN VARCHAR2,p_object_id IN NUMBER,p_resource_id IN NUMBER) RETURN VARCHAR2
362 is
363 x_return_value varchar2(1) ;
364 x_object_count NUMBER;
365 l_resource_id NUMBER;
366
367 BEGIN
368
369 IF p_object_class = 'OFFR' THEN
370 select count(act.object_id) into x_object_count
371 from ams_act_access_denorm act, qp_list_headers_b qp, ozf_offers off
372 where act.object_id = p_object_id
373 and act.object_type = p_object_class
374 and act.resource_id= p_resource_id
375 and qp.list_header_id = act.object_id
376 and off.qp_list_header_id = act.object_id
377 and qp.source_system_code = FND_PROFILE.VALUE('QP_SOURCE_SYSTEM_CODE')
378 and NVL(off.budget_offer_yn,'N') = 'N';
379 ELSIF p_object_class = 'CSCH' THEN
380 select count(act.object_id) into x_object_count
381 from ams_act_access_denorm act
382 where act.object_id = p_object_id
383 and act.object_type = p_object_class
384 and act.resource_id= p_resource_id;
385 END IF;
386
387 IF x_object_count = 0 THEN
388 x_return_value := 'N';
389 ELSE
390 x_return_value := 'Y';
391 END IF;
392 RETURN (x_return_value);
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 -- dbms_output.put_line(sqlerrm(sqlcode));
397 null;
398
399 END get_activity_access;
400
401
402 FUNCTION get_uom_conv(p_object_id IN VARCHAR2
403 ,p_qty IN NUMBER
404 ,p_from_uom IN VARCHAR2
405 ,p_to_uom IN VARCHAR2) RETURN NUMBER
406 is
407 x_return_value NUMBER;
408
409 BEGIN
410
411 select inv_convert.inv_um_convert(p_object_id,null,p_qty,p_from_uom,p_to_uom,null,null)
412 into x_return_value
413 from dual;
414 RETURN (x_return_value);
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 -- dbms_output.put_line(sqlerrm(sqlcode));
419 null;
420
421 END get_uom_conv;
422
423
424 PROCEDURE get_list_price(
425 p_api_version IN NUMBER,
426 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
427 p_commit IN VARCHAR2 := FND_API.g_false,
428
429 p_obj_id IN NUMBER,
430 p_obj_type IN VARCHAR2,
431 p_product_attribute IN VARCHAR2,
432 p_product_attr_value IN VARCHAR2,
433 p_fcst_uom IN VARCHAR2,
434 p_currency_code IN VARCHAR2,
435 p_price_list_id IN NUMBER,
436 p_qualifier_tbl IN OZF_TP_UTIL_QUERIES.QUALIFIER_TBL_TYPE,
437
438 x_list_price OUT NOCOPY NUMBER,
439 x_return_status OUT NOCOPY VARCHAR2,
440 x_msg_count OUT NOCOPY NUMBER,
441 x_msg_data OUT NOCOPY VARCHAR2
442 )
443 IS
444
445
446 p_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
447 p_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
448 p_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
449 p_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
450 p_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
451 p_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
452 p_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
453 p_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
454 x_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
455 x_line_qual QP_PREQ_GRP.QUAL_TBL_TYPE;
456 x_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
457 x_line_detail_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
458 x_line_detail_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
459 x_line_detail_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
460 x_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
461 x_return_stat VARCHAR2(240);
462 x_return_status_text VARCHAR2(240);
463 qual_rec QP_PREQ_GRP.QUAL_REC_TYPE;
464 line_attr_rec QP_PREQ_GRP.LINE_ATTR_REC_TYPE;
465 line_rec QP_PREQ_GRP.LINE_REC_TYPE;
466 rltd_rec QP_PREQ_GRP.RELATED_LINES_REC_TYPE;
467
468 l_version VARCHAR2(240);
469 l_counter NUMBER;
470 I BINARY_INTEGER;
471
472 l_status_code VARCHAR2(240);
473 l_status_text VARCHAR2(2000);
474
475 l_api_version CONSTANT NUMBER := 1.0;
476 l_api_name CONSTANT VARCHAR2(30) := 'Get_List_Price';
477 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
478 l_return_status VARCHAR2(1);
479
480 CURSOR c_market_qualifiers IS
481 SELECT qualifier_context,
482 qualifier_attribute,
483 qualifier_attr_value
484 FROM ozf_worksheet_qualifiers
485 WHERE worksheet_header_id = p_obj_id;
486
487
488 BEGIN
489
490
491 IF FND_API.to_boolean(p_init_msg_list) THEN
492 FND_MSG_PUB.initialize;
493 END IF;
494
495 IF NOT FND_API.compatible_api_call(l_api_version,
496 p_api_version,
497 l_api_name,
498 g_pkg_name)
499 THEN
500 RAISE FND_API.g_exc_unexpected_error;
501 END IF;
502
503 x_return_status := FND_API.g_ret_sts_success;
504
505 -- Passing Information to the Pricing Engine
506
507 -- Setting up the control record variables
508 p_control_rec.pricing_event := 'LINE';
509 p_control_rec.calculate_flag := 'N';
510 p_control_rec.simulation_flag := 'Y';
511
512 -- Request Line (Order Line) Information
513 line_rec.request_type_code :='ONT';
514 line_rec.line_id :=999;
515 line_rec.line_Index := 1 ; -- Request Line Index
516 line_rec.line_type_code := 'LINE'; -- LINE or ORDER(Summary Line)
517 line_rec.pricing_effective_date := SYSDATE; -- Pricing as of what date ?
518 line_rec.active_date_first := SYSDATE; -- Can be Ordered Date or Ship Date
519 line_rec.active_date_second := SYSDATE; -- Can be Ordered Date or Ship Date
520 line_rec.active_date_first_type := 'NO TYPE'; -- ORD/SHIP
521 line_rec.active_date_second_type :='NO TYPE'; -- ORD/SHIP
522 line_rec.line_quantity := 1; -- Ordered Quantity
523 line_rec.line_uom_code := p_fcst_uom; -- Ordered UOM Code
524 line_rec.currency_code := p_currency_code; -- Currency Code
525 line_rec.price_flag := 'Y'; -- Price Flag can have 'Y' ,
526 -- 'N'(No pricing) , 'P'(Phase)
527 p_line_tbl(1) := line_rec;
528
529 -- Pricing Attributes Passed In
530 line_attr_rec.LINE_INDEX := 1 ;
531 line_attr_rec.PRICING_CONTEXT := 'ITEM';
532 line_attr_rec.PRICING_ATTRIBUTE := p_product_attribute;
533 line_attr_rec.PRICING_ATTR_VALUE_FROM := p_product_attr_value;
534
535 line_attr_rec.VALIDATED_FLAG :='N';
536 p_line_attr_tbl(1):= line_attr_rec;
537
538 -- Market Qualifiers
539 l_counter := 1;
540
541 qual_rec.LINE_INDEX := 1;
542 qual_rec.QUALIFIER_CONTEXT :='MODLIST';
543 qual_rec.QUALIFIER_ATTRIBUTE :='QUALIFIER_ATTRIBUTE4';
544 qual_rec.QUALIFIER_ATTR_VALUE_FROM := TO_CHAR(p_price_list_id);
545 qual_rec.COMPARISON_OPERATOR_CODE := '=';
546 qual_rec.VALIDATED_FLAG :='Y';
547 p_qual_tbl(l_counter):= qual_rec;
548
549 /* If p_obj_type is passed then use the qualifiers defined */
550 /* else iterate thru the array and add them to qual_rec. */
551 if p_obj_type is not null then
552 FOR i IN c_market_qualifiers
553 LOOP
554 l_counter := l_counter+1 ;
555
556 qual_rec.LINE_INDEX := 1;
557 qual_rec.QUALIFIER_CONTEXT := i.QUALIFIER_CONTEXT;
558 qual_rec.QUALIFIER_ATTRIBUTE := i.QUALIFIER_ATTRIBUTE;
559 qual_rec.QUALIFIER_ATTR_VALUE_FROM := i.QUALIFIER_ATTR_VALUE;
560 qual_rec.COMPARISON_OPERATOR_CODE := '=';
561 qual_rec.VALIDATED_FLAG :='Y';
562 p_qual_tbl(l_counter):= qual_rec;
563 END LOOP;
564 else
565 IF p_qualifier_tbl.COUNT > 0 THEN
566 FOR i IN p_qualifier_tbl.first..p_qualifier_tbl.last LOOP
567 IF p_qualifier_tbl.EXISTS(i) THEN
568 l_counter := l_counter+1 ;
569 qual_rec.LINE_INDEX := 1;
570 qual_rec.QUALIFIER_CONTEXT := p_qualifier_tbl(i).qualifier_context;
571 qual_rec.QUALIFIER_ATTRIBUTE := p_qualifier_tbl(i).qualifier_attribute;
572 qual_rec.QUALIFIER_ATTR_VALUE_FROM := p_qualifier_tbl(i).qualifier_attr_value;
573 qual_rec.COMPARISON_OPERATOR_CODE := '=';
574 qual_rec.VALIDATED_FLAG :='Y';
575 p_qual_tbl(l_counter):= qual_rec;
576 end if;
577 end loop;
578 end if;
579 end if;
580 -- Actual Call to the Pricing Engine
581 QP_PREQ_GRP.PRICE_REQUEST
582 (p_line_tbl,
583 p_qual_tbl,
584 p_line_attr_tbl,
585 p_line_detail_tbl,
586 p_line_detail_qual_tbl,
587 p_line_detail_attr_tbl,
588 p_related_lines_tbl,
589 p_control_rec,
590 x_line_tbl,
591 x_line_qual,
592 x_line_attr_tbl,
593 x_line_detail_tbl,
594 x_line_detail_qual_tbl,
595 x_line_detail_attr_tbl,
596 x_related_lines_tbl,
597 x_return_stat,
598 x_return_status_text);
599
600 IF x_return_stat = 'E'
601 THEN
602 x_return_status := FND_API.G_RET_STS_ERROR;
603 FND_MESSAGE.set_name('AMS', 'AMS_FCST_GET_LISTPRICE_FAILURE');
604 FND_MESSAGE.set_token('ERR_MSG',x_return_status_text);
605 FND_MSG_PUB.add;
606 RAISE FND_API.G_EXC_ERROR;
607 ELSE
608
609 I := x_line_tbl.FIRST;
610 IF I IS NOT NULL
611 THEN
612 x_list_price := x_line_tbl(I).line_unit_price;
613 l_status_code := x_line_tbl(I).status_code ;
614 l_status_text := x_line_tbl(I).status_text;
615 END IF;
616
617 IF l_status_code <> 'UPDATED'
618 THEN
619 x_return_status := FND_API.G_RET_STS_ERROR;
620 FND_MESSAGE.set_name('AMS', 'AMS_FCST_GET_LISTPRICE_FAILURE');
621 FND_MESSAGE.set_token('ERR_MSG',l_status_text);
622 FND_MSG_PUB.add;
623 RAISE FND_API.G_EXC_ERROR;
624
625 END IF;
626
627 END IF;
628
629
630 EXCEPTION
631
632 WHEN FND_API.G_EXC_ERROR THEN
633 IF (AMS_DEBUG_HIGH_ON) THEN
634
635 AMS_Utility_PVT.debug_message('Validate_get_list_price_of_goods: ' || substr(sqlerrm, 1, 100));
636 END IF;
637 x_return_status := FND_API.G_RET_STS_ERROR;
638
639 FND_MSG_PUB.Count_And_Get (
640 p_encoded => FND_API.g_false,
641 p_count => x_msg_count,
642 p_data => x_msg_data
643 );
644
645 WHEN OTHERS THEN
646 x_return_status := FND_API.g_ret_sts_unexp_error;
647
648 FND_MESSAGE.set_name('AMS', 'AMS_FCST_GET_LISTPRICE_FAILURE');
649 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
650 FND_MSG_PUB.add;
651
652 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
653 THEN
654 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
655 END IF;
656
657 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
658 p_count => x_msg_count,
659 p_data => x_msg_data);
660
661
662 END get_list_price;
663
664 -- Function used in OZF_ORDER_SALES_V
665
666 FUNCTION get_quota_unit RETURN VARCHAR2
667 IS
668 -- inanaiah - R12 change
669 /*
670 CURSOR sys_csr IS
671 SELECT NVL(quota_unit,'Q')
672 FROM ozf_sys_parameters;
673 */
674
675 l_quota_unit VARCHAR2(1);
676
677 BEGIN
678
679 -- inanaiah - R12 change
680 /*
681 OPEN sys_csr;
682 FETCH sys_csr INTO l_quota_unit;
683 CLOSE sys_csr;
684 */
685 RETURN fnd_profile.value('OZF_TP_QUOTA_ALLOCATION_BY'); --l_quota_unit ;
686
687 END get_quota_unit;
688
689 FUNCTION get_item_descr ( p_FlexField_Name IN VARCHAR2
690 ,p_Context_Name IN VARCHAR2
691 ,p_attribute_name IN VARCHAR2
692 ,p_attr_value IN VARCHAR2 ) RETURN VARCHAR2 IS
693
694 l_item_name varchar2(240) := NULL;
695 l_category_name varchar2(240) := NULL;
696
697 CURSOR c_category_descr IS
698 SELECT
699 NVL(d.category_desc, c.description) cat_descr
700 --NVL(d.concat_cat_parentage, c.description) cat_descr
701 FROM mtl_default_category_sets a,
702 mtl_category_sets_b b,
703 mtl_categories_v c,
704 ENI_PROD_DEN_HRCHY_PARENTS_V d
705 WHERE a.functional_area_id IN (7, 11)
706 AND a.category_set_id = b.category_set_id
707 AND b.structure_id = c.structure_id
708 AND c.category_id = d.category_id(+)
709 AND c.category_id = p_attr_value;
710
711
712
713 BEGIN
714
715 IF p_attribute_name = 'PRICING_ATTRIBUTE1' THEN
716 l_item_name:=QP_PRICE_LIST_LINE_UTIL.GET_PRODUCT_VALUE(p_FlexField_Name,p_Context_Name,p_attribute_name,p_attr_value);
717 RETURN l_item_name;
718 ELSE
719 OPEN c_category_descr;
720 FETCH c_category_descr INTO l_category_name;
721 CLOSE c_category_descr;
722 RETURN l_category_name;
723 END IF;
724 RETURN NULL;
725
726 END get_item_descr;
727
728 -- Used to get the description of an inventory_item
729 FUNCTION get_item_name ( p_inventory_item_id IN NUMBER) RETURN VARCHAR2 IS
730
731 l_item_name varchar2(240) := NULL;
732
733 BEGIN
734
735 select description into l_item_name
736 from mtl_system_items_vl
737 where inventory_item_id = p_inventory_item_id
738 and organization_id = fnd_profile.value('QP_ORGANIZATION_ID');
739
740 return l_item_name;
741
742 END get_item_name;
743
744 -- Used to get the cost of an inventory_item
745 FUNCTION get_item_cost ( p_inventory_item_id IN NUMBER) RETURN NUMBER IS
746
747 l_item_cost NUMBER := NULL;
748
749 BEGIN
750
751 l_item_cost := CST_COST_API.get_item_cost(1,p_inventory_item_id,fnd_profile.value('QP_ORGANIZATION_ID'), NULL,NULL);
752
753 return l_item_cost;
754
755 END get_item_cost;
756
757
758
759 END OZF_TP_UTIL_QUERIES;