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