[Home] [Help]
PACKAGE BODY: APPS.PO_PRICE_BREAK_GRP
Source
1 package body po_price_break_grp as /* <TIMEPHASED FPI> */
2 /* $Header: POXPRBKB.pls 120.4.12000000.2 2007/10/05 09:56:56 adbharga ship $ */
3
4 /*===========================================================================*/
5 /*======================= SPECIFICATIONS (PRIVATE) ==========================*/
6 /*===========================================================================*/
7
8 G_PKG_NAME CONSTANT varchar2(30) := 'PO_PRICE_BREAK_GRP';
9 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.';
10
11 -- Read the profile option that enables/disables the debug log
12 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
13
14 -- <FPJ Custom Price START>
15 -- Debugging
16 g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
17 g_debug_unexp BOOLEAN := PO_DEBUG.is_debug_unexp_on;
18 -- <FPJ Custom Price END>
19
20 FUNCTION get_conversion_rate -- <2694908>
21 ( p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
22 ) RETURN PO_HEADERS_ALL.rate%TYPE;
23
24 FUNCTION get_line_price -- <2694908>
25 ( p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE ,
26 p_po_line_num IN PO_LINES_ALL.line_num%TYPE
27 ) RETURN PO_LINES_ALL.unit_price%TYPE;
28
29 /*===========================================================================*/
30 /*============================ BODY (Overloaded) ============================*/
31 /*===========================================================================*/
32
33 Procedure Get_Price_Break (
34 source_document_header_id IN NUMBER,
35 source_document_line_num IN NUMBER,
36 in_quantity IN NUMBER,
37 unit_of_measure IN VARCHAR2,
38 deliver_to_location_id IN NUMBER,
39 required_currency IN VARCHAR2,
40 required_rate_type IN VARCHAR2,
41 p_need_by_date IN DATE, -- <TIMEPHASED FPI>
42 p_destination_org_id IN NUMBER, -- <TIMEPHASED FPI>
43 base_price OUT NOCOPY NUMBER,
44 currency_price OUT NOCOPY NUMBER,
45 discount OUT NOCOPY NUMBER,
46 currency_code OUT NOCOPY VARCHAR2,
47 rate_type OUT NOCOPY VARCHAR2,
48 rate_date OUT NOCOPY DATE,
49 rate OUT NOCOPY NUMBER
50 ) is
51
52 l_price_break_id number;
53 Begin
54
55 get_price_break
56 ( p_source_document_header_id => source_document_header_id
57 , p_source_document_line_num => source_document_line_num
58 , p_in_quantity => in_quantity
59 , p_unit_of_measure => unit_of_measure
60 , p_deliver_to_location_id => deliver_to_location_id
61 , p_required_currency => required_currency
62 , p_required_rate_type => required_rate_type
63 , p_need_by_date => p_need_by_date -- <TIMEPHASED FPI>
64 , p_destination_org_id => p_destination_org_id -- <TIMEPHASED FPI>
65 , x_base_price => base_price
66 , x_currency_price => currency_price
67 , x_discount => discount
68 , x_currency_code => currency_code
69 , x_rate_type => rate_type
70 , x_rate_date => rate_date
71 , x_rate => rate
72 , x_price_break_id => l_price_break_id
73 );
74
75 End;
76
77
78 Procedure Get_Price_Break (
79 p_source_document_header_id IN NUMBER,
80 p_source_document_line_num IN NUMBER,
81 p_in_quantity IN NUMBER,
82 p_unit_of_measure IN VARCHAR2,
83 p_deliver_to_location_id IN NUMBER,
84 p_required_currency IN VARCHAR2,
85 p_required_rate_type IN VARCHAR2,
86 p_need_by_date IN DATE, -- <TIMEPHASED FPI>
87 p_destination_org_id IN NUMBER, -- <TIMEPHASED FPI>
88 x_base_price OUT NOCOPY NUMBER,
89 x_currency_price OUT NOCOPY NUMBER,
90 x_discount OUT NOCOPY NUMBER,
91 x_currency_code OUT NOCOPY VARCHAR2,
92 x_rate_type OUT NOCOPY VARCHAR2,
93 x_rate_date OUT NOCOPY DATE,
94 x_rate OUT NOCOPY NUMBER,
95 x_price_break_id OUT NOCOPY NUMBER -- <SERVICES FPJ>
96 ) is
97 -- <FPJ Advanced Price START>
98 l_base_unit_price PO_REQUISITION_LINES.base_unit_price%TYPE;
99 -- <FPJ Advanced Price END>
100 Begin
101
102 get_price_break
103 ( p_source_document_header_id => p_source_document_header_id
104 , p_source_document_line_num => p_source_document_line_num
105 , p_in_quantity => p_in_quantity
106 , p_unit_of_measure => p_unit_of_measure
107 , p_deliver_to_location_id => p_deliver_to_location_id
108 , p_required_currency => p_required_currency
109 , p_required_rate_type => p_required_rate_type
110 , p_need_by_date => p_need_by_date -- <TIMEPHASED FPI>
111 , p_destination_org_id => p_destination_org_id -- <TIMEPHASED FPI>
112 -- <FPJ Advanced Price START>
113 , p_org_id => NULL
114 , p_supplier_id => NULL
115 , p_supplier_site_id => NULL
116 , p_creation_date => NULL
117 , p_order_header_id => NULL
118 , p_order_line_id => NULL
119 , p_line_type_id => NULL
120 , p_item_revision => NULL
121 , p_item_id => NULL
122 , p_category_id => NULL
123 , p_supplier_item_num => NULL
124 , p_in_price => NULL
125 , x_base_unit_price => l_base_unit_price
126 -- <FPJ Advanced Price END>
127 , x_base_price => x_base_price
128 , x_currency_price => x_currency_price
129 , x_discount => x_discount
130 , x_currency_code => x_currency_code
131 , x_rate_type => x_rate_type
132 , x_rate_date => x_rate_date
133 , x_rate => x_rate
134 , x_price_break_id => x_price_break_id
135 );
136
137 End;
138
139
140 /*===========================================================================*/
141 /*============================ BODY (PUBLIC) ================================*/
142 /*===========================================================================*/
143
144 Procedure Get_Price_Break (
145 p_source_document_header_id IN NUMBER,
146 p_source_document_line_num IN NUMBER,
147 p_in_quantity IN NUMBER,
148 p_unit_of_measure IN VARCHAR2,
149 p_deliver_to_location_id IN NUMBER,
150 p_required_currency IN VARCHAR2,
151 p_required_rate_type IN VARCHAR2,
152 p_need_by_date IN DATE, -- <TIMEPHASED FPI>
153 p_destination_org_id IN NUMBER, -- <TIMEPHASED FPI>
154 -- <FPJ Advanced Price START>
155 p_org_id IN NUMBER,
156 p_supplier_id IN NUMBER,
157 p_supplier_site_id IN NUMBER,
158 p_creation_date IN DATE,
159 p_order_header_id IN NUMBER,
160 p_order_line_id IN NUMBER,
161 p_line_type_id IN NUMBER,
162 p_item_revision IN VARCHAR2,
163 p_item_id IN NUMBER,
164 p_category_id IN NUMBER,
165 p_supplier_item_num IN VARCHAR2,
166 p_in_price IN NUMBER,
167 x_base_unit_price OUT NOCOPY NUMBER,
168 -- <FPJ Advanced Price END>
169 x_base_price OUT NOCOPY NUMBER,
170 x_currency_price OUT NOCOPY NUMBER,
171 x_discount OUT NOCOPY NUMBER,
172 x_currency_code OUT NOCOPY VARCHAR2,
173 x_rate_type OUT NOCOPY VARCHAR2,
174 x_rate_date OUT NOCOPY DATE,
175 x_rate OUT NOCOPY NUMBER,
176 x_price_break_id OUT NOCOPY NUMBER -- <SERVICES FPJ>
177 ) is
178
179 v_ship_to_location_id number;
180 v_temp number;
181 v_return_unit_of_measure varchar2(26);
182
183 v_req_sob_id number; -- FPI GA
184 v_req_rate_type po_headers_all.rate_type%TYPE; -- FPI GA
185 v_ga_flag varchar2(1); -- FPI GA
186 v_ga_currency po_headers_all.currency_code%TYPE; -- FPI GA
187 v_conversion_rate po_headers_all.rate%TYPE; -- FPI GA
188 v_po_rate po_headers_all.rate%TYPE; -- FPI GA
189
190 l_ship_to_org_id po_line_locations_all.ship_to_organization_id%TYPE; /* <TIMEPHASED FPI> */
191
192 l_dummy_var BOOLEAN; -- <2694908>
193
194 l_progress varchar2(4);
195
196 /* Bug2842675 */
197 l_base_curr_ext_precision number;
198
199 -- <FPJ Custom Price START>
200 l_source_document_type PO_HEADERS.type_lookup_code%TYPE;
201 l_source_document_line_id PO_LINES.po_line_id%TYPE;
202 l_pricing_date PO_LINE_LOCATIONS.need_by_date%TYPE;
203 l_new_currency_price PO_LINES.unit_price%TYPE;
204 l_return_status varchar2(1);
205 l_api_name CONSTANT varchar2(30) := 'GET_PRICE_BREAK';
206 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
207 -- <FPJ Custom Price END>
208
209 -- Bug 3343892
210 l_base_unit_price PO_LINES.base_unit_price%TYPE;
211
212 -- Bug 3373445
213 l_currency_unit_price NUMBER := null;
214 l_precision NUMBER := null;
215 l_ext_precision NUMBER := null;
216 l_min_acct_unit NUMBER := null;
217 l_qp_license VARCHAR2(30) := NULL;
218 --------------------------------------------------------------
219 -- Bug 2401468 (anhuang) 6/6/02
220 --------------------------------------------------------------
221 -- The following fixes were taken from the original USER_EXIT:
222 -- 1) Truncated all sysdates. (Bug 1655381)
223 -- 2) Added decode statement for QUOTATIONs in unit_price cursor
224 -- so it is equivalent to PRICE BREAK case. (Bug 1934869)
225
226 /*
227 Bug 2800681.
228 Change the defaulting of null quantity to 0 instead of -1 in the
229 ORDER BY clause
230 Bug 2842675 Rounded the price to the extended precision of the base
231 currency
232 */
233
234 -- bug4043100
235 -- Removed outer join to poll since it already handles null price case
236 CURSOR loc_unit_price IS
237 SELECT poll.price_override
238 , round(poll.price_override * v_conversion_rate, l_base_curr_ext_precision )
239 , poh.rate_date
240 , poh.rate
241 , poh.currency_code
242 , poh.rate_type
243 , poll.price_discount
244 , poll.price_override
245 , decode( poll.line_location_id,
246 null, pol.unit_meas_lookup_code,
247 poll.unit_meas_lookup_code)
248 , poll.line_location_id -- SERVICES FPJ
249 FROM po_headers_all poh -- FPI GA
250 , po_lines_all pol -- FPI GA
251 , po_line_locations_all poll -- FPI GA
252 WHERE poh.po_header_id = p_source_document_header_id
253 and poh.po_header_id = pol.po_header_id
254 and pol.line_num = p_source_document_line_num
255 and pol.po_line_id = poll.po_line_id -- bug4043100
256 and ( p_required_currency is null
257 or poh.currency_code = p_required_currency )
258 and ( p_required_rate_type is null
259 or poh.rate_type = p_required_rate_type )
260 and nvl(poll.unit_meas_lookup_code, nvl(p_unit_of_measure,
261 pol.unit_meas_lookup_code))
262 = nvl(p_unit_of_measure, pol.unit_meas_lookup_code)
263
264 /* <TIMEPHASED FPI START> */
265 /*
266 Change sysdate to l_pricing_date in order to use the Need By Date
267 to determine the price.
268 */
269 and (trunc(nvl(l_pricing_date, trunc(sysdate))) >= trunc(poll.start_date) -- FPJ Custom Price
270 OR
271 poll.start_date is null)
272 and (trunc(nvl(l_pricing_date, trunc(sysdate))) <= trunc(poll.end_date) -- FPJ Custom Price
273 OR
274 poll.end_date is null)
275 /* <TIMEPHASED FPI END> */
276 --Bug #2693408: added nvl clause to quantity check
277 and nvl(poll.quantity, 0) <= nvl(p_in_quantity, 0)
278
279
280 /* <TIMEPHASED FPI START> */
281 /*
282 Determining the price based on ship-to-location and destination organization
283 */
284 and ((poll.ship_to_location_id = v_ship_to_location_id OR poll.ship_to_location_id is null)
285 AND
286 (poll.ship_to_organization_id = p_destination_org_id OR poll.ship_to_organization_id is null))
287
288 /* <TIMEPHASED FPI END> */
289
290 and poll.shipment_type in ('PRICE BREAK', 'QUOTATION')
291
292 -- <2721775 START>: Make sure Quotation Price Breaks are Approved.
293 --
294 -- bug4043100 - remove poll.shipment_type is null check
295 AND ( ( poll.shipment_type = 'PRICE BREAK' )
296 OR ( ( poll.shipment_type = 'QUOTATION' )
297 AND ( ( poh.approval_required_flag <> 'Y' )
298 OR ( EXISTS ( SELECT ('Price Break is Approved')
299 FROM po_quotation_approvals pqa
300 WHERE pqa.line_location_id = poll.line_location_id
301 AND pqa.approval_type IN ('ALL ORDERS', 'REQUISITIONS')
302 AND (start_date_active is null
303 OR trunc(nvl(l_pricing_date, sysdate)) >= start_date_active)
304 AND (end_date_active is null
305 OR trunc(nvl(l_pricing_date, sysdate)) <= end_date_active)
306 )))))
307 -- <2721775 END>
308 order by poll.ship_to_organization_id ASC, poll.ship_to_location_id ASC,
309 NVL(poll.quantity, 0) DESC,
310 trunc(poll.creation_date) DESC, poll.price_override ASC; /* <TIMEPHASED FPI> */
311
312
313 BEGIN
314
315 -- <FPJ Advanced Price START>
316 -- Initialize OUT parameters
317 x_base_unit_price := p_in_price;
318 x_base_price := p_in_price;
319 x_currency_price := p_in_price;
320 x_discount := NULL;
321 x_currency_code := NULL;
322 x_rate_type := NULL;
323 x_rate_date := NULL;
324 x_rate := NULL;
325 x_price_break_id := NULL;
326
327 IF g_debug_stmt THEN
328 PO_DEBUG.debug_begin(l_log_head);
329 PO_DEBUG.debug_var(l_log_head,l_progress,'p_org_id',p_org_id);
330 PO_DEBUG.debug_var(l_log_head,l_progress,'p_source_document_header_id',p_source_document_header_id);
331 PO_DEBUG.debug_var(l_log_head,l_progress,'p_source_document_line_num',p_source_document_line_num);
332 PO_DEBUG.debug_var(l_log_head,l_progress,'p_in_quantity',p_in_quantity);
333 PO_DEBUG.debug_var(l_log_head,l_progress,'p_unit_of_measure',p_unit_of_measure);
334 PO_DEBUG.debug_var(l_log_head,l_progress,'p_deliver_to_location_id',p_deliver_to_location_id);
335 PO_DEBUG.debug_var(l_log_head,l_progress,'p_required_currency',p_required_currency);
336 PO_DEBUG.debug_var(l_log_head,l_progress,'p_required_rate_type',p_required_rate_type);
337 PO_DEBUG.debug_var(l_log_head,l_progress,'p_need_by_date',p_need_by_date);
338 PO_DEBUG.debug_var(l_log_head,l_progress,'p_destination_org_id',p_destination_org_id);
339 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_id',p_supplier_id);
340 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_site_id',p_supplier_site_id);
341 PO_DEBUG.debug_var(l_log_head,l_progress,'p_creation_date',p_creation_date);
342 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_header_id',p_order_header_id);
343 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_line_id',p_order_line_id);
344 PO_DEBUG.debug_var(l_log_head,l_progress,'p_line_type_id',p_line_type_id);
345 PO_DEBUG.debug_var(l_log_head,l_progress,'p_item_revision',p_item_revision);
346 PO_DEBUG.debug_var(l_log_head,l_progress,'p_item_id',p_item_id);
347 PO_DEBUG.debug_var(l_log_head,l_progress,'p_category_id',p_category_id);
348 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_item_num',p_supplier_item_num);
349 PO_DEBUG.debug_var(l_log_head,l_progress,'p_in_price',p_in_price);
350 PO_DEBUG.debug_stmt(l_log_head,l_progress,'get_ship_to_location');
351 END IF;
352 -- <FPJ Advanced Price END>
353
354 -- <2694908 START>
355
356 -- Initialize Variables Used in Cursors ===================================
357 --
358 -- (v_ship_to_location_id)
359 l_dummy_var :=
360 PO_LOCATIONS_S.get_ship_to_location( p_deliver_to_location_id, -- IN
361 v_ship_to_location_id ); -- IN/OUT
362
363 l_progress := '020';
364 IF g_debug_stmt THEN
365 PO_DEBUG.debug_var(l_log_head,l_progress,'v_ship_to_location_id',v_ship_to_location_id);
366 PO_DEBUG.debug_stmt(l_log_head,l_progress,'get_conversion_rate');
367 END IF;
368
369 -- (v_conversion_rate): Gets the correct rate for GAs and Local Blankets.
370 v_conversion_rate := get_conversion_rate( p_source_document_header_id );
371
372 l_progress := '040';
373 IF g_debug_stmt THEN
374 PO_DEBUG.debug_var(l_log_head,l_progress,'v_conversion_rate',v_conversion_rate);
375 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Get the base currency extended precision');
376 END IF;
377
378 -- Fetch Price from Cursors ===============================================
379 --
380 /* Bug 2842675 Get the base currency extended precision */
381 SELECT nvl(FND.extended_precision,5)
382 INTO l_base_curr_ext_precision
383 FROM FND_CURRENCIES FND,
384 FINANCIALS_SYSTEM_PARAMETERS FSP,
385 GL_SETS_OF_BOOKS GSB
386 WHERE FSP.set_of_books_id = GSB.set_of_books_id AND
387 FND.currency_code = GSB.currency_code;
388
389 l_progress := '060';
390 IF g_debug_stmt THEN
391 PO_DEBUG.debug_var(l_log_head,l_progress,'l_base_curr_ext_precision',l_base_curr_ext_precision);
392 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Check source document type');
393 END IF;
394
395 -- <FPJ Custom Price START>
396
397 -- SQL What: Find out the source document line id, and source doument type
398 -- SQL Why : Get source document line id to call GET_CUSTOM_PRICE_DATE,
399 -- Get source document type since we only allow custom pricing for
400 -- Blanket and Quotation.
401 SELECT ph.type_lookup_code,
402 pl.po_line_id
403 INTO l_source_document_type,
404 l_source_document_line_id
405 FROM po_headers_all ph,
406 po_lines_all pl
407 WHERE ph.po_header_id = p_source_document_header_id
408 AND pl.po_header_id(+) = ph.po_header_id
409 AND pl.line_num(+) = p_source_document_line_num;
410
411 l_progress := '080';
412 IF g_debug_stmt THEN
413 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call get_custom_price_date');
414 PO_DEBUG.debug_var(l_log_head,l_progress,'p_source_document_header_id',p_source_document_header_id);
415 PO_DEBUG.debug_var(l_log_head,l_progress,'p_source_document_line_id',l_source_document_line_id);
416 PO_DEBUG.debug_var(l_log_head,l_progress,'p_quantity',p_in_quantity);
417 PO_DEBUG.debug_var(l_log_head,l_progress,'p_ship_to_location_id',v_ship_to_location_id);
418 PO_DEBUG.debug_var(l_log_head,l_progress,'p_ship_to_organization_id',p_destination_org_id);
419 PO_DEBUG.debug_var(l_log_head,l_progress,'p_need_by_date',p_need_by_date);
420 END IF; /* IF g_debug_stmt */
421 /*Bug5598011 Passing the order_type as REQUISITION */
422 /* call the Custom Price Date API */
423 PO_CUSTOM_PRICE_PUB.GET_CUSTOM_PRICE_DATE
424 (p_api_version => 1.0,
425 p_source_document_header_id=> p_source_document_header_id, -- <FPJ Advanced Price>
426 p_source_document_line_id => l_source_document_line_id,
427 p_order_line_id => p_order_line_id, -- <Bug 3754828>
428 p_quantity => p_in_quantity,
429 p_ship_to_location_id => v_ship_to_location_id,
430 p_ship_to_organization_id => p_destination_org_id,
431 p_need_by_date => p_need_by_date,
432 x_pricing_date => l_pricing_date,
433 x_return_status => l_return_status,
434 p_order_type => 'REQUISITION');
435
436 l_progress := '100';
437 IF g_debug_stmt THEN
438 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Calling get_custom_price_date');
439 PO_DEBUG.debug_var(l_log_head,l_progress,'l_pricing_date',l_pricing_date);
440 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status',l_return_status);
441 END IF; /* IF g_debug_stmt */
442
443 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
444 app_exception.raise_exception;
445 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
446
447 IF (l_pricing_date IS NULL) THEN
448 l_pricing_date := NVL(p_need_by_date, SYSDATE);
449 END IF; /* IF (l_pricing_date IS NULL) */
450
451 -- <FPJ Custom Price END>
452
453 IF l_source_document_type IN ('BLANKET', 'QUOTATION') THEN
454 l_progress := '120';
455 IF g_debug_stmt THEN
456 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Open Cursor loc_unit_price');
457 END IF; /* IF g_debug_stmt */
458
459 OPEN loc_unit_price;
460
461 l_progress := '140';
462 IF g_debug_stmt THEN
463 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Fetch Cursor loc_unit_price');
464 END IF; /* IF g_debug_stmt */
465
466 FETCH loc_unit_price INTO
467 v_temp
468 , x_base_price
469 , x_rate_date
470 , x_rate
471 , x_currency_code
472 , x_rate_type
473 , x_discount
474 , x_currency_price
475 , v_return_unit_of_measure
476 , x_price_break_id; -- SERVICES FPJ
477
478 -- If 'loc_unit_price' returned no rows, get line price.
479 --
480 /*
481 Bug 2803841.
482 Removed the call to use cursor unit_price as this cursor
483 is removed.
484 */
485 IF (loc_unit_price%ROWCOUNT = 0) THEN
486
487 l_progress := '160';
488 IF g_debug_stmt THEN
489 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Cursor loc_unit_price returned no rows');
490 PO_DEBUG.debug_stmt(l_log_head,l_progress,'get line price');
491 END IF; /* IF g_debug_stmt */
492
493 x_currency_price := get_line_price ( p_source_document_header_id ,
494 p_source_document_line_num );
495 l_progress := '180';
496 IF g_debug_stmt THEN
497 PO_DEBUG.debug_var(l_log_head,l_progress,'x_currency_price',x_currency_price);
498 END IF; /* IF g_debug_stmt */
499
500 /* Bug 2842675 Should be rounded off to the ext precision of the
501 base currency */
502 x_base_price := round(x_currency_price * v_conversion_rate, l_base_curr_ext_precision);
503 l_progress := '200';
504 IF g_debug_stmt THEN
505 PO_DEBUG.debug_var(l_log_head,l_progress,'x_base_price',x_base_price);
506 PO_DEBUG.debug_stmt(l_log_head,l_progress,'get currency info');
507 END IF; /* IF g_debug_stmt */
508
509 x_discount := 0; -- line price does not have a discount
510 x_price_break_id := NULL; -- SERVICES FPJ
511
512 PO_HEADERS_SV3.get_currency_info ( p_source_document_header_id ,
513 x_currency_code ,
514 x_rate_type ,
515 x_rate_date ,
516 x_rate );
517 l_progress := '220';
518 IF g_debug_stmt THEN
519 PO_DEBUG.debug_var(l_log_head,l_progress,'x_currency_code',x_currency_code);
520 PO_DEBUG.debug_var(l_log_head,l_progress,'x_rate_type',x_rate_type);
521 PO_DEBUG.debug_var(l_log_head,l_progress,'x_rate_date',x_rate_date);
522 PO_DEBUG.debug_var(l_log_head,l_progress,'x_rate',x_rate);
523 END IF; /* IF g_debug_stmt */
524 ELSE
525 l_progress := '240';
526 IF g_debug_stmt THEN
527 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Cursor loc_unit_price returned rows');
528 PO_DEBUG.debug_var(l_log_head,l_progress,'v_temp',v_temp);
529 PO_DEBUG.debug_var(l_log_head,l_progress,'x_base_price',x_base_price);
530 PO_DEBUG.debug_var(l_log_head,l_progress,'x_rate_date',x_rate_date);
531 PO_DEBUG.debug_var(l_log_head,l_progress,'x_rate',x_rate);
532 PO_DEBUG.debug_var(l_log_head,l_progress,'x_currency_code',x_currency_code);
533 PO_DEBUG.debug_var(l_log_head,l_progress,'x_rate_type',x_rate_type);
534 PO_DEBUG.debug_var(l_log_head,l_progress,'x_discount',x_discount);
535 PO_DEBUG.debug_var(l_log_head,l_progress,'x_currency_price',x_currency_price);
536 PO_DEBUG.debug_var(l_log_head,l_progress,'v_return_unit_of_measure',v_return_unit_of_measure);
537 PO_DEBUG.debug_var(l_log_head,l_progress,'x_price_break_id',x_price_break_id);
538 END IF; /* IF g_debug_stmt */
539
540 END IF; /*IF (loc_unit_price%ROWCOUNT = 0)*/
541
542 l_progress := '260';
543 IF g_debug_stmt THEN
544 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Close Cursor loc_unit_price');
545 END IF; /* IF g_debug_stmt */
546
547 -- <FPJ Advanced START>
548 -- Bug 3417479, don't populate base_unit_price if source document is not CONTRACT
549 -- x_base_unit_price := x_base_price;
550 -- <FPJ Advanced END>
551
552 CLOSE loc_unit_price;
553
554 -- Unit of Measure ========================================================
555
556 IF ( v_return_unit_of_measure <> p_unit_of_measure)
557 THEN
558 x_rate := 0;
559 v_conversion_rate := 0;
560 x_discount := 0;
561 END IF;
562
563 -- <2694908 END>
564
565 --<FPJ Advanced Price START>
566 ELSIF l_source_document_type = 'CONTRACT' THEN
567 l_progress := '280';
568 IF g_debug_stmt THEN
569 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Get Currency Info');
570 END IF; /* IF g_debug_stmt */
571
572 PO_HEADERS_SV3.get_currency_info ( p_source_document_header_id ,
573 x_currency_code ,
574 x_rate_type ,
575 x_rate_date ,
576 x_rate );
577 l_progress := '300';
578 IF g_debug_stmt THEN
579 PO_DEBUG.debug_var(l_log_head,l_progress,'x_currency_code',x_currency_code);
580 PO_DEBUG.debug_var(l_log_head,l_progress,'x_rate_type',x_rate_type);
581 PO_DEBUG.debug_var(l_log_head,l_progress,'x_rate_date',x_rate_date);
582 PO_DEBUG.debug_var(l_log_head,l_progress,'x_rate',x_rate);
583 END IF; /* IF g_debug_stmt */
584 END IF; /* l_source_document_type IN ('BLANKET', 'QUOTATION') */
585 --<FPJ Advanced Price END>
586
587 -- Global Agreements: Correct Currency/Rate info ==========================
588
589 -- <GA FPI START>: Need to return GA rate info instead of from the PO.
590 --
591 IF ( PO_GA_PVT.is_global_agreement( p_source_document_header_id ) ) THEN
592
593 PO_GA_PVT.get_currency_info( p_source_document_header_id ,
594 x_currency_code ,
595 x_rate_type ,
596 x_rate_date ,
597 x_rate );
598 END IF;
599 -- <GA FPI END>
600
601 -- <FPJ Custom Price START>
602
603 /* call the Custom Pricing API */
604 -- Only allow custom pricing for Blanket and Quotation
605 IF l_source_document_type IN ('BLANKET', 'QUOTATION') THEN
606
607 --<R12 GBPA Adv Pricing Support Start>
608 -- Call Advanced Pricing for global Blanket purchase agreements
609 -- Do not call Advanced Pricing for Blanket Purchase agreements and quotations
610
611 IF (l_source_document_type = 'BLANKET'
612 AND PO_GA_PVT.is_global_agreement(p_source_document_header_id)) THEN
613
614 IF (PO_ADVANCED_PRICE_PVT.is_valid_qp_line_type(p_line_type_id)) THEN
615
616 l_progress := '305';
617 IF g_debug_stmt THEN
618 PO_DEBUG.debug_stmt(l_log_head,l_progress,'x_currency_price: ' || x_currency_price);
619 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call Advanced Pricing API(GBPA)');
620 END IF;
621
622 PO_ADVANCED_PRICE_PVT.get_advanced_price
623 ( p_org_id => p_org_id
624 , p_supplier_id => p_supplier_id
625 , p_supplier_site_id => p_supplier_site_id
626 , p_creation_date => p_creation_date
627 , p_order_type => 'REQUISITION'
628 , p_ship_to_location_id => v_ship_to_location_id
629 , p_ship_to_org_id => p_destination_org_id
630 , p_order_header_id => p_order_header_id
631 , p_order_line_id => p_order_line_id
632 , p_item_revision => p_item_revision
633 , p_item_id => p_item_id
634 , p_category_id => p_category_id
635 , p_supplier_item_num => p_supplier_item_num
636 , p_agreement_type => l_source_document_type
637 , p_agreement_id => p_source_document_header_id
638 , p_agreement_line_id => l_source_document_line_id
639 , p_rate => x_rate
640 , p_rate_type => x_rate_type
641 , p_currency_code => x_currency_code
642 , p_need_by_date => l_pricing_date
643 , p_quantity => p_in_quantity
644 , p_uom => p_unit_of_measure
645 , p_unit_price => x_currency_price
646 , x_base_unit_price => l_base_unit_price
647 , x_unit_price => l_new_currency_price
648 , x_return_status => l_return_status );
649
650 l_progress := '310';
651 IF g_debug_stmt THEN
652 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Call Advanced Pricing API(GBPA)');
653 END IF;
654
655 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
656 app_exception.raise_exception;
657 END IF;
658
659 x_base_price := round(l_new_currency_price * v_conversion_rate,
660 l_base_curr_ext_precision);
661 x_currency_price := l_new_currency_price;
662
663 ELSE /* Invalid Line type*/
664
665 l_progress := '315';
666 IF g_debug_stmt THEN
667 PO_DEBUG.debug_stmt(l_log_head, l_progress,
668 'Not a valid price type to call Advanced Pricing API(GBPA)');
669 END IF;
670 END IF; /*IF (PO_ADVANCED_PRICE_PVT.is_valid_qp_line_type(p_line_type_id))*/
671
672
673 END IF; /* (l_source_document_type IN ('BLANKET')*/
674
675 --<R12 GBPA Adv Pricing Support End>
676 --Pass the Price passed from Advance Pricing to Custom Price Hook
677
678 l_progress := '320';
679 IF g_debug_stmt THEN
680 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call get_custom_req_price');
681 PO_DEBUG.debug_var(l_log_head,l_progress,'p_source_document_header_id',p_source_document_header_id);
682 PO_DEBUG.debug_var(l_log_head,l_progress,'p_source_document_line_num',p_source_document_line_num);
683 PO_DEBUG.debug_var(l_log_head,l_progress,'p_quantity',p_in_quantity);
684 PO_DEBUG.debug_var(l_log_head,l_progress,'p_unit_of_measure',p_unit_of_measure);
685 PO_DEBUG.debug_var(l_log_head,l_progress,'p_deliver_to_location_id',p_deliver_to_location_id);
686 PO_DEBUG.debug_var(l_log_head,l_progress,'p_required_currency',p_required_currency);
687 PO_DEBUG.debug_var(l_log_head,l_progress,'p_required_rate_type',p_required_rate_type);
688 PO_DEBUG.debug_var(l_log_head,l_progress,'p_need_by_date',p_need_by_date);
689 PO_DEBUG.debug_var(l_log_head,l_progress,'p_pricing_date',l_pricing_date);
690 PO_DEBUG.debug_var(l_log_head,l_progress,'p_destination_org_id',p_destination_org_id);
691 PO_DEBUG.debug_var(l_log_head,l_progress,'p_currency_price',x_currency_price);
692 END IF; /* IF g_debug_stmt */
693
694 PO_CUSTOM_PRICE_PUB.GET_CUSTOM_REQ_PRICE
695 (p_api_version => 1.0,
696 p_source_document_header_id=> p_source_document_header_id,
697 p_source_document_line_num=> p_source_document_line_num,
698 p_order_line_id => p_order_line_id, -- <Bug 3754828>
699 p_quantity => p_in_quantity,
700 p_unit_of_measure => p_unit_of_measure,
701 p_deliver_to_location_id => p_deliver_to_location_id,
702 p_required_currency => p_required_currency,
703 p_required_rate_type => p_required_rate_type,
704 p_need_by_date => p_need_by_date,
705 p_pricing_date => l_pricing_date,
706 p_destination_org_id => p_destination_org_id,
707 p_currency_price => x_currency_price,
708 x_new_currency_price => l_new_currency_price,
709 x_return_status => l_return_status);
710
711 l_progress := '340';
712 IF g_debug_stmt THEN
713 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Calling get_custom_req_price');
714 PO_DEBUG.debug_var(l_log_head,l_progress,'l_new_currency_price',l_new_currency_price);
715 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status',l_return_status);
716 END IF; /* IF g_debug_stmt */
717
718 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
719 app_exception.raise_exception;
720 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
721
722 IF (l_new_currency_price IS NOT NULL) THEN -- The custom API returned back a price.
723 IF (l_new_currency_price < 0) THEN -- Price has to be greater than or equal to zero
724 po_message_s.app_error('PO_CUSTOM_PRICE_LESS_0',
725 'ROUTINE', l_api_name,
726 'ERR_NUMBER', l_progress,
727 'CUSTOM_PRICING_API', 'GET_CUSTOM_REQ_PRICE');
728 app_exception.raise_exception;
729 END IF; /* IF (l_new_currency_price <0) */
730 x_base_price := round(l_new_currency_price * v_conversion_rate,
731 l_base_curr_ext_precision);
732 -- Bug 3417479, don't populate base_unit_price if source document is not CONTRACT
733 -- x_base_unit_price := x_base_price;
734 x_currency_price := l_new_currency_price;
735 END IF; /* IF (l_new_price is not NULL) */
736
737 -- <FPJ Advanced Price START>
738 ELSIF (l_source_document_type = 'CONTRACT') THEN
739
740 -- Bug 5516257: Get the profile value to check if Adv Pricing is installed
741 FND_PROFILE.get('QP_LICENSED_FOR_PRODUCT',l_qp_license);
742 l_progress := '345';
743 IF g_debug_stmt THEN
744 PO_DEBUG.debug_var(l_log_head,l_progress,'l_qp_license',l_qp_license);
745 END IF;
746
747 --Bug 5516257: Added the logic to nullify the output unitprice if the Adv Pricing API
748 --is not installed or licensed to PO;
749 IF (l_qp_license IS NULL OR l_qp_license <> 'PO') THEN
750 x_currency_price := null;
751
752 l_progress := '350';
753 IF g_debug_stmt THEN
754 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Advanced Pricing is not installed, nullify the new price');
755 END IF;
756 ELSE --Bug 5516257: Call Adv Pricing API if it's installed
757
758 IF (PO_ADVANCED_PRICE_PVT.is_valid_qp_line_type(p_line_type_id)) THEN
759
760 -- Bug 3373445 START
761 -- p_in_price is in base currency, convert it to transaction currency
762 l_progress := '350';
763 IF g_debug_stmt THEN
764 PO_DEBUG.debug_stmt(l_log_head,l_progress,
765 'Get currency extended precision for ' || x_currency_code);
766 END IF;
767 fnd_currency.get_info(x_currency_code, l_precision,
768 l_ext_precision, l_min_acct_unit);
769 IF g_debug_stmt THEN
770 PO_DEBUG.debug_stmt(l_log_head,l_progress,'p_in_price: ' || p_in_price);
771 PO_DEBUG.debug_stmt(l_log_head,l_progress,'v_conversion_rate: ' || v_conversion_rate);
772 PO_DEBUG.debug_stmt(l_log_head,l_progress,'l_ext_precision: ' || l_ext_precision);
773 END IF;
774
775 l_currency_unit_price := round(p_in_price/v_conversion_rate, l_ext_precision);
776 -- Bug 3373445 END
777
778 l_progress := '360';
779 IF g_debug_stmt THEN
780 PO_DEBUG.debug_stmt(l_log_head,l_progress,'l_currency_unit_price: ' || l_currency_unit_price);
781 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call Advanced Pricing API(CPA)');
782 END IF;
783
784
785 PO_ADVANCED_PRICE_PVT.get_advanced_price
786 ( p_org_id => p_org_id
787 , p_supplier_id => p_supplier_id
788 , p_supplier_site_id => p_supplier_site_id
789 , p_creation_date => p_creation_date
790 , p_order_type => 'REQUISITION'
791 , p_ship_to_location_id => v_ship_to_location_id
792 , p_ship_to_org_id => p_destination_org_id
793 , p_order_header_id => p_order_header_id
794 , p_order_line_id => p_order_line_id
795 , p_item_revision => p_item_revision
796 , p_item_id => p_item_id
797 , p_category_id => p_category_id
798 , p_supplier_item_num => p_supplier_item_num
799 , p_agreement_type => 'CONTRACT'
800 , p_agreement_id => p_source_document_header_id
801 , p_rate => x_rate
802 , p_rate_type => x_rate_type
803 , p_currency_code => x_currency_code
804 , p_need_by_date => l_pricing_date
805 , p_quantity => p_in_quantity
806 , p_uom => p_unit_of_measure
807 -- Bug 3373445
808 , p_unit_price => l_currency_unit_price
809 -- Bug 3343892, Don't pass back Advanced Price List price
810 -- , x_base_unit_price => x_base_unit_price
811 , x_base_unit_price => l_base_unit_price
812 , x_unit_price => l_new_currency_price
813 , x_return_status => l_return_status );
814
815
816 l_progress := '380';
817 IF g_debug_stmt THEN
818 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Call Advanced Pricing API(CPA)');
819 END IF;
820
821 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
822 app_exception.raise_exception;
823 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
824
825 -- Bug 3373445
826 x_base_price := round(l_new_currency_price * v_conversion_rate,
827 l_base_curr_ext_precision);
828 x_currency_price := l_new_currency_price;
829 ELSE
830 l_progress := '400';
831 IF g_debug_stmt THEN
832 PO_DEBUG.debug_stmt(l_log_head, l_progress,
833 'Not a valid price type to call Advanced Pricing API(CPA)');
834 END IF;
835 END IF; /*IF (PO_ADVANCED_PRICE_PVT.is_valid_qp_line_type(p_line_type_id))*/
836
837 -- <FPJ Advanced Price END>
838
839 END IF; -- if QP is not installed , license is <> PO
840
841 END IF; /* IF l_source_document_type IN ('BLANKET', 'QUOTATION') */
842
843 -- <FPJ Custom Price END>
844
845
846 l_progress := '500';
847 IF g_debug_stmt THEN
848 PO_DEBUG.debug_end(l_log_head);
849 PO_DEBUG.debug_var(l_log_head,l_progress,'x_base_unit_price',x_base_unit_price);
850 PO_DEBUG.debug_var(l_log_head,l_progress,'x_base_price',x_base_price);
851 PO_DEBUG.debug_var(l_log_head,l_progress,'x_currency_price',x_currency_price);
852 PO_DEBUG.debug_var(l_log_head,l_progress,'x_discount',x_discount);
853 PO_DEBUG.debug_var(l_log_head,l_progress,'x_currency_code',x_currency_code);
854 PO_DEBUG.debug_var(l_log_head,l_progress,'x_rate_type',x_rate_type);
855 PO_DEBUG.debug_var(l_log_head,l_progress,'x_rate_date',x_rate_date);
856 PO_DEBUG.debug_var(l_log_head,l_progress,'x_rate',x_rate);
857 PO_DEBUG.debug_var(l_log_head,l_progress,'x_price_break_id',x_price_break_id);
858 END IF; /* IF g_debug_stmt */
859
860 EXCEPTION
861 WHEN OTHERS THEN
862 IF g_debug_unexp THEN
863 PO_DEBUG.debug_exc(l_log_head,l_progress);
864 END IF;
865
866 -- bug4043100
867 -- Close th e cursor if it's still open
868 IF (loc_unit_price%ISOPEN) THEN
869 CLOSE loc_unit_price;
870 END IF;
871
872 x_base_unit_price := NULL;
873 x_base_price := NULL;
874 x_currency_price := NULL;
875 x_discount := NULL;
876 x_currency_code := NULL;
877 x_rate_type := NULL;
878 x_rate_date := NULL;
879 x_rate := NULL;
880 x_price_break_id := NULL;
881 po_message_s.sql_error('get_price_break', l_progress, sqlcode);
882 raise;
883
884 END get_price_break;
885
886
887 --<TIMEPHASED FPI START>
888 /* This procedure is a wrapper for get_price_break */
889 /* It is called by the ReqImport code */
890 Procedure Reqimport_Set_Break_Price(
891 p_request_id IN po_requisitions_interface.request_id%TYPE
892 ) IS
893 l_rowid VARCHAR2(250) := '';
894 l_src_blanket_header_id po_requisition_lines.blanket_po_header_id%TYPE;
895 l_src_blanket_line_num po_requisition_lines.blanket_po_line_num%TYPE;
896 l_quantity po_requisition_lines.quantity%TYPE;
897 l_deliver_to_location po_requisition_lines.deliver_to_location_id%TYPE;
898 l_currency_code po_requisition_lines.currency_code%TYPE;
899 l_rate_type po_requisition_lines.rate_type%TYPE;
900 l_need_by_date po_requisition_lines.need_by_date%TYPE;
901 l_destination_org po_requisition_lines.destination_organization_id%TYPE;
902 l_base_price_out po_requisition_lines.unit_price%TYPE;
903 l_currency_price_out po_requisition_lines.currency_unit_price%TYPE;
904 l_discount_out NUMBER;
905 l_currency_code_out po_requisition_lines.currency_code%TYPE;
906 l_rate_type_out po_requisition_lines.rate_type%TYPE;
907 l_rate_date_out po_requisition_lines.rate_date%TYPE;
908 l_rate_out po_requisition_lines.rate%TYPE;
909 l_uom po_requisitions_interface.unit_of_measure%TYPE;
910 l_price_break_id po_line_locations_all.line_location_id%TYPE; -- <SERVICES FPJ>
911 l_progress VARCHAR2(3) := NULL;
912
913 -- <FPJ Advanced Price START>
914 l_org_id po_requisitions_interface.org_id%TYPE;
915 l_requisition_header_id po_requisition_lines.requisition_header_id%TYPE;
916 l_requisition_line_id po_requisition_lines.requisition_line_id%TYPE;
917 l_creation_date po_requisitions_interface.creation_date%TYPE;
918 l_item_id po_requisitions_interface.item_id%TYPE;
919 l_item_revision po_requisitions_interface.item_revision%TYPE;
920 l_category_id po_requisitions_interface.category_id%TYPE;
921 l_line_type_id po_requisitions_interface.line_type_id%TYPE;
922 l_suggested_vendor_item_num po_requisitions_interface.suggested_vendor_item_num%TYPE;
923 l_suggested_vendor_id po_requisitions_interface.suggested_vendor_id%TYPE;
924 l_suggested_vendor_site_id po_requisitions_interface.suggested_vendor_site_id%TYPE;
925 -- Bug 3343892
926 l_base_unit_price po_requisitions_interface.base_unit_price%TYPE;
927 l_base_unit_price_out po_requisition_lines.base_unit_price%TYPE;
928 -- <FPJ Advanced Price END>
929
930 CURSOR req_lines IS
931 SELECT pri.rowid, pri.autosource_doc_header_id, pri.autosource_doc_line_num,
932 pri.quantity, pri.deliver_to_location_id, pri.currency_code,
933 pri.rate_type, pri.need_by_date, pri.destination_organization_id,
934 pri.unit_of_measure,
935 -- <FPJ Advanced Price START>
936 pri.org_id,
937 NULL requisition_header_id,
938 NULL requisition_line_id,
939 pri.creation_date,
940 pri.item_id,
941 pri.item_revision,
942 pri.category_id,
943 pri.line_type_id,
944 pri.suggested_vendor_item_num,
945 pri.suggested_vendor_id,
946 pri.suggested_vendor_site_id,
947 -- Bug 3343892
948 pri.base_unit_price
949 -- <FPJ Advanced Price END>
950 FROM po_requisitions_interface pri
951 WHERE pri.autosource_flag in ('Y', 'P')
952 AND pri.item_id is not NULL
953 AND pri.source_type_code = 'VENDOR'
954 AND pri.autosource_doc_header_id is not NULL
955 -- Bug 3417479
956 -- AND pri.autosource_doc_line_num is not NULL
957 AND pri.request_id = p_request_id;
958
959 BEGIN
960
961 l_progress :='010';
962
963 OPEN req_lines;
964 LOOP
965 FETCH req_lines into
966 l_rowid, l_src_blanket_header_id, l_src_blanket_line_num,
967 l_quantity, l_deliver_to_location, l_currency_code,
968 l_rate_type, l_need_by_date, l_destination_org, l_uom,
969 -- <FPJ Advanced Price START>
970 l_org_id,
971 l_requisition_header_id,
972 l_requisition_line_id,
973 l_creation_date,
974 l_item_id,
975 l_item_revision,
976 l_category_id,
977 l_line_type_id,
978 l_suggested_vendor_item_num,
979 l_suggested_vendor_id,
980 l_suggested_vendor_site_id,
981 -- Bug 3343892
982 l_base_unit_price;
983 -- <FPJ Advanced Price END>
984 EXIT WHEN req_lines%NOTFOUND or req_lines%NOTFOUND IS NULL;
985
986 l_progress :='020';
987 get_price_break
988 ( p_source_document_header_id => l_src_blanket_header_id
989 , p_source_document_line_num => l_src_blanket_line_num
990 , p_in_quantity => l_quantity
991 , p_unit_of_measure => l_uom
992 , p_deliver_to_location_id => l_deliver_to_location
993 , p_required_currency => l_currency_code
994 , p_required_rate_type => l_rate_type
995 , p_need_by_date => l_need_by_date -- <TIMEPHASED FPI>
996 , p_destination_org_id => l_destination_org -- <TIMEPHASED FPI>
997 -- <FPJ Advanced Price START>
998 , p_org_id => l_org_id
999 , p_supplier_id => l_suggested_vendor_id
1000 , p_supplier_site_id => l_suggested_vendor_site_id
1001 , p_creation_date => l_creation_date
1002 , p_order_header_id => l_requisition_header_id
1003 , p_order_line_id => l_requisition_line_id
1004 , p_line_type_id => l_line_type_id
1005 , p_item_revision => l_item_revision
1006 , p_item_id => l_item_id
1007 , p_category_id => l_category_id
1008 , p_supplier_item_num => l_suggested_vendor_item_num
1009 -- Bug 3343892
1010 , p_in_price => l_base_unit_price
1011 , x_base_unit_price => l_base_unit_price_out
1012 -- <FPJ Advanced Price END>
1013 , x_base_price => l_base_price_out
1014 , x_currency_price => l_currency_price_out
1015 , x_discount => l_discount_out
1016 , x_currency_code => l_currency_code_out
1017 , x_rate_type => l_rate_type_out
1018 , x_rate_date => l_rate_date_out
1019 , x_rate => l_rate_out
1020 , x_price_break_id => l_price_break_id -- <SERVICES FPJ>
1021 );
1022
1023 l_progress := '030';
1024
1025 UPDATE po_requisitions_interface pri
1026 SET -- Bug 3417479, only set NOT NULL price
1027 -- pri.unit_price = l_base_price_out,
1028 -- pri.base_unit_price = l_base_unit_price_out, -- <FPJ Advanced Price>
1029 -- pri.currency_unit_price = l_currency_price_out,
1030 pri.unit_price = NVL(l_base_price_out, pri.unit_price),
1031 pri.base_unit_price = NVL(l_base_unit_price_out, pri.base_unit_price),
1032 pri.currency_unit_price = NVL(l_currency_price_out, pri.currency_unit_price),
1033 pri.currency_code = l_currency_code_out,
1034 pri.rate_type = l_rate_type_out,
1035 pri.rate_date = l_rate_date_out,
1036 pri.rate = l_rate_out
1037 WHERE pri.rowid = l_rowid;
1038
1039 l_progress :='040';
1040 END LOOP;
1041 CLOSE req_lines;
1042
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 po_message_s.sql_error('set_break_price', l_progress, sqlcode);
1046 RAISE;
1047 END Reqimport_Set_Break_Price;
1048 --<TIMEPHASED FPI END>
1049
1050 /*===========================================================================*/
1051 /*=========================== BODY (PRIVATE) ================================*/
1052 /*===========================================================================*/
1053
1054 /*=============================================================================
1055
1056 FUNCTION: get_conversion_rate <2694908>
1057
1058 DESCRIPTION: Gets the rate for the given po_header_id.
1059 If the document is a local Blanket, gets the rate defined
1060 on the document headers. If it is a Global Agreement, gets
1061 the rate defined in the Set of Books for the functional
1062 currency and the GA's currency.
1063
1064 =============================================================================*/
1065 FUNCTION get_conversion_rate
1066 (
1067 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
1068 )
1069 RETURN PO_HEADERS_ALL.rate%TYPE
1070 IS
1071 l_currency_code PO_HEADERS_ALL.currency_code%TYPE;
1072 l_ga_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
1073 l_po_rate PO_HEADERS_ALL.rate%TYPE;
1074
1075 l_sob_id FINANCIALS_SYSTEM_PARAMETERS.set_of_books_id%TYPE;
1076 l_rate_type PO_SYSTEM_PARAMETERS.default_rate_type%TYPE;
1077
1078 x_rate PO_HEADERS_ALL.rate%TYPE;
1079
1080 BEGIN
1081
1082 SELECT currency_code ,
1083 nvl(global_agreement_flag, 'N') ,
1084 nvl(rate, 1) -- <TIMEPHASED FPI>
1085 INTO l_currency_code ,
1086 l_ga_flag ,
1087 l_po_rate
1088 FROM po_headers_all
1089 WHERE po_header_id = p_po_header_id;
1090
1091 -- If document is local Blanket, get rate from document header
1092 --
1093 IF ( l_ga_flag = 'N' ) THEN
1094
1095 x_rate := l_po_rate;
1096
1097 -- Else, document is Global Agreement.
1098 -- Get rate between GA_currency and functional_currency.
1099 --
1100 ELSE
1101
1102 SELECT set_of_books_id
1103 INTO l_sob_id
1104 FROM financials_system_parameters;
1105
1106 SELECT default_rate_type
1107 INTO l_rate_type
1108 FROM po_system_parameters;
1109
1110 x_rate := PO_CORE_S.get_conversion_rate( l_sob_id ,
1111 l_currency_code ,
1112 sysdate ,
1113 l_rate_type );
1114 END IF;
1115
1116 return (x_rate);
1117
1118 EXCEPTION
1119
1120 WHEN OTHERS THEN
1121 return (NULL);
1122
1123 END get_conversion_rate;
1124
1125
1126 /*=============================================================================
1127
1128 FUNCTION: get_line_price <2694908>
1129
1130 DESCRIPTION: Gets the line price for the given document and line number
1131 (in the document's currency).
1132
1133 =============================================================================*/
1134 FUNCTION get_line_price
1135 (
1136 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE ,
1137 p_po_line_num IN PO_LINES_ALL.line_num%TYPE
1138 )
1139 RETURN PO_LINES_ALL.unit_price%TYPE
1140 IS
1141 x_unit_price PO_LINES_ALL.unit_price%TYPE;
1142
1143 BEGIN
1144
1145 SELECT unit_price
1146 INTO x_unit_price
1147 FROM po_lines_all
1148 WHERE po_header_id = p_po_header_id
1149 AND line_num = p_po_line_num;
1150
1151 return (x_unit_price);
1152
1153 EXCEPTION
1154
1155 WHEN OTHERS THEN
1156 return (NULL);
1157
1158 END get_line_price;
1159
1160
1161 END po_price_break_grp;