[Home] [Help]
PACKAGE BODY: APPS.PO_PRICE_BREAK_S
Source
4 SOURCE_DOCUMENT_HEADER_ID IN NUMBER,
1 package body po_price_break_s as
2 /* $Header: POXPRBKB.pls 115.6.1158.2 2002/09/30 23:18:43 dreddy ship $ */
3 Procedure Get_Price_Break (
5 SOURCE_DOCUMENT_LINE_NUM IN NUMBER,
6 IN_QUANTITY IN NUMBER,
7 UNIT_OF_MEASURE IN VARCHAR2,
8 DELIVER_TO_LOCATION_ID IN NUMBER,
9 REQUIRED_CURRENCY IN VARCHAR2,
10 REQUIRED_RATE_TYPE IN VARCHAR2,
11 BASE_PRICE OUT NUMBER,
12 CURRENCY_PRICE OUT NUMBER,
13 DISCOUNT OUT NUMBER,
14 CURRENCY_CODE OUT VARCHAR2,
15 RATE_TYPE OUT VARCHAR2,
16 RATE_DATE OUT DATE,
17 RATE OUT NUMBER) is
18 v_ship_to_location_id number;
19 v_temp number;
20 v_return_unit_of_measure varchar2(26);
21
22 --------------------------------------------------------------
23 -- Bug 2401468 (anhuang) 6/6/02
24 --------------------------------------------------------------
25 -- The following fixes were taken from the original USER_EXIT:
26 -- 1) Truncated all sysdates. (Bug 1655381)
27 -- 2) Added decode statement for QUOTATIONs in unit_price cursor
28 -- so it is equivalent to PRICE BREAK case. (Bug 1934869)
29
30 CURSOR loc_unit_price IS
31 SELECT poll.price_override
32 , round(poll.price_override * decode(poh.rate, 0, 1, null, 1, poh.rate), 5 )
33 , poh.rate_date
34 , poh.rate
35 , poh.currency_code
36 , poh.rate_type
37 , poll.price_discount
38 , poll.price_override
39 , decode( poll.line_location_id,
40 null, pol.unit_meas_lookup_code,
41 poll.unit_meas_lookup_code)
42 FROM po_headers poh
43 , po_lines pol
44 , po_line_locations poll
45 WHERE poh.po_header_id = source_document_header_id
46 and poh.po_header_id = pol.po_header_id
47 and pol.line_num = source_document_line_num
48 and pol.po_line_id = poll.po_line_id (+)
49 and ( required_currency is null
50 or poh.currency_code = required_currency )
51 and ( required_rate_type is null
52 or poh.rate_type = required_rate_type )
53 and nvl(poll.unit_meas_lookup_code, nvl(unit_of_measure,
54 pol.unit_meas_lookup_code))
55 = nvl(unit_of_measure, pol.unit_meas_lookup_code)
56 and trunc(sysdate) between nvl(poll.start_date, trunc(sysdate))
57 and nvl(poll.end_date, trunc(sysdate))
58 and poll.quantity <= in_quantity
59 and poll.ship_to_location_id = v_ship_to_location_id
60 and poll.shipment_type in ('PRICE BREAK', 'QUOTATION')
61 order by 1 asc;
62
63 CURSOR unit_price IS
64 SELECT decode( poll.shipment_type,
65 'PRICE BREAK', decode( poll.ship_to_location_id,
66 null, poll.price_override,
67 pol.unit_price) ,
68 'QUOTATION', decode( poll.ship_to_location_id,
69 null, poll.price_override,
70 pol.unit_price) ,
71 pol.unit_price)
72 , round( decode( poll.shipment_type,
73 'PRICE BREAK', decode( poll.ship_to_location_id,
74 null, poll.price_override,
75 pol.unit_price) ,
76 'QUOTATION', decode( poll.ship_to_location_id,
77 null, poll.price_override,
78 pol.unit_price) ,
79 pol.unit_price)
80 * decode(poh.rate, 0, 1, null, 1, poh.rate), 5 )
81 , poh.rate_date
82 , poh.rate
83 , poh.currency_code
84 , poh.rate_type
85 , poll.price_discount
86 , decode( poll.shipment_type,
87 'PRICE BREAK', decode( poll.ship_to_location_id,
88 null, poll.price_override ,
89 pol.unit_price) ,
90 'QUOTATION', decode( poll.ship_to_location_id,
91 null, poll.price_override ,
92 pol.unit_price) ,
93 pol.unit_price)
94 , decode( poll.line_location_id,
95 null, pol.unit_meas_lookup_code,
96 poll.unit_meas_lookup_code)
97 FROM po_headers poh
98 , po_lines pol
99 , po_line_locations poll
100 WHERE poh.po_header_id = source_document_header_id
101 and poh.po_header_id = pol.po_header_id
102 and pol.line_num = source_document_line_num
103 and pol.po_line_id = poll.po_line_id (+)
104 and ( required_currency is null
105 or poh.currency_code = required_currency )
106 and ( required_rate_type is null
107 or poh.rate_type = required_rate_type )
108 and nvl(poll.unit_meas_lookup_code, nvl( unit_of_measure,
109 pol.unit_meas_lookup_code))
110 = nvl(unit_of_measure, pol.unit_meas_lookup_code)
111 and trunc(sysdate) BETWEEN nvl(poll.start_date,trunc(sysdate) ) AND nvl(poll.end_date,trunc(sysdate))
112 and poll.quantity (+) <= in_quantity
113 order by 1 asc ;
114
115 BEGIN
116
117 BEGIN
118 SELECT ship_to_location_id
119 INTO v_ship_to_location_id
120 FROM hr_locations
121 WHERE location_id = deliver_to_location_id ;
122 EXCEPTION
123
124 WHEN NO_DATA_FOUND THEN
125 begin
129 where location_id = deliver_to_location_id;
126 select location_id
127 into v_ship_to_location_id
128 from hz_locations
130
131 exception
132
133 when no_data_found then
134
135 null;
136 end;
137
138 WHEN OTHERS THEN
139 null;
140 END;
141
142 /* Bug 2596651 */
143 /* Get the unit price and the currency price from the blanket line */
144 /* This price will be used if the below cursors do not return anything*/
145
146 BEGIN
147 SELECT pol.unit_price,
148 round(pol.unit_price * decode(poh.rate, 0, 1, null, 1, poh.rate), 5 )
149 INTO base_price,
150 currency_price
151 FROM po_lines pol,
152 po_headers poh
153 WHERE pol.po_header_id = poh.po_header_id
154 AND pol.po_header_id = source_document_header_id
155 AND pol.line_num = source_document_line_num;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 null;
160 END;
161
162 OPEN loc_unit_price;
163
164 FETCH loc_unit_price INTO
165 v_temp
166 , base_price
167 , rate_date
168 , rate
169 , currency_code
170 , rate_type
171 , discount
172 , currency_price
173 , v_return_unit_of_measure;
174
175 /*
176 ** If no row returned from the SQL statement, find the price
177 ** without using location.
178 */
179 IF (loc_unit_price%ROWCOUNT = 0) THEN
180
181 OPEN unit_price;
182
183 FETCH unit_price INTO
184 v_temp
185 , base_price
186 , rate_date
187 , rate
188 , currency_code
189 , rate_type
190 , discount
191 , currency_price
192 , v_return_unit_of_measure;
193
194 /*
195 ** If no row returned from the SQL statement, return an error to
196 ** the calling form.
197 */
198 /* Bug 2596651 - do not set price to 0 if no rows returned
199 we need to return the line price from above */
200
201 IF (unit_price%ROWCOUNT= 0) THEN
202 rate := 0;
203 -- currency_price := 0;
204 discount := 0;
205 -- base_price := 0;
206 END IF;
207
208 END IF;
209 IF ( v_return_unit_of_measure <> unit_of_measure) THEN
210 rate := 0;
211 -- currency_price := 0;
212 discount := 0;
213 -- base_price := 0;
214 END IF;
215 end;
216 end;