[Home] [Help]
PACKAGE BODY: APPS.PO_SOURCING2_SV
Source
1 PACKAGE BODY PO_SOURCING2_SV as
2 /* $Header: POXSCS2B.pls 120.5.12010000.2 2008/08/04 08:32:38 rramasam ship $ */
3
4 /*============================= PO_SOURCING2_SV ===========================*/
5
6 /**==========================================================================
7 *
8 * FUNCTION NAME: get_break_price()
9 *
10 * Change History
11 * ==============
12 * Modified By Date Descriptions
13 * Dreddy Overloaded the price break API
14 *===========================================================================*/
15
16 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_SOURCING2_SV';
17 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.';
18
19 -- Read the profile option that enables/disables the debug log
20 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
21
22 -- <FPJ Custom Price START>
23 -- Debugging
24 g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
25 g_debug_unexp BOOLEAN := PO_DEBUG.is_debug_unexp_on;
26 -- <FPJ Custom Price END>
27
28 FUNCTION get_break_price(x_order_quantity IN NUMBER,
29 x_ship_to_org IN NUMBER,
30 x_ship_to_loc IN NUMBER,
31 x_po_line_id IN NUMBER,
32 x_cum_flag IN BOOLEAN,
33 p_need_by_date IN DATE,
34 x_line_location_id IN NUMBER)
35 return NUMBER is
36
37 x_price NUMBER := null;
38 x_price_break_id NUMBER := null;
39
40 x_return_status VARCHAR2(1);
41
42 BEGIN
43 get_break_price
44 ( p_api_version => 1.0
45 , p_order_quantity => x_order_quantity
46 , p_ship_to_org => x_ship_to_org
47 , p_ship_to_loc => x_ship_to_loc
48 , p_po_line_id => x_po_line_id
49 , p_cum_flag => x_cum_flag
50 , p_need_by_date => p_need_by_date
51 , p_line_location_id => x_line_location_id
52 , x_price_break_id => x_price_break_id
53 , x_price => x_price
54 , x_return_status => x_return_status
55 );
56
57 return(x_price);
58
59 END;
60
61 -- <FPJ Advanced Price START>
62 PROCEDURE get_break_price(p_api_version IN NUMBER,
63 p_order_quantity IN NUMBER,
64 p_ship_to_org IN NUMBER,
65 p_ship_to_loc IN NUMBER,
66 p_po_line_id IN NUMBER,
67 p_cum_flag IN BOOLEAN,
68 p_need_by_date IN DATE,
69 p_line_location_id IN NUMBER,
70 x_price_break_id OUT NOCOPY NUMBER,
71 x_price OUT NOCOPY NUMBER,
72 x_return_status OUT NOCOPY VARCHAR2 )
73 IS
74 l_base_unit_price NUMBER;
75 BEGIN
76
77 get_break_price
78 ( p_api_version => 1.0
79 , p_order_quantity => p_order_quantity
80 , p_ship_to_org => p_ship_to_org
81 , p_ship_to_loc => p_ship_to_loc
82 , p_po_line_id => p_po_line_id
83 , p_cum_flag => p_cum_flag
84 , p_need_by_date => p_need_by_date
85 , p_line_location_id => p_line_location_id
86 , p_contract_id => NULL
87 , p_org_id => NULL
88 , p_supplier_id => NULL
89 , p_supplier_site_id => NULL
90 , p_creation_date => NULL
91 , p_order_header_id => NULL
92 , p_order_line_id => NULL
93 , p_line_type_id => NULL
94 , p_item_revision => NULL
95 , p_item_id => NULL
96 , p_category_id => NULL
97 , p_supplier_item_num=> NULL
98 , p_uom => NULL
99 , p_in_price => NULL
100 , p_currency_code => NULL -- Bug 3564863
101 , x_base_unit_price => l_base_unit_price
102 , x_price_break_id => x_price_break_id
103 , x_price => x_price
104 , x_return_status => x_return_status
105 );
106
107 END;
108 -- <FPJ Advanced Price END>
109
110
111 PROCEDURE get_break_price(p_api_version IN NUMBER,
112 p_order_quantity IN NUMBER,
113 p_ship_to_org IN NUMBER,
114 p_ship_to_loc IN NUMBER,
115 p_po_line_id IN NUMBER,
116 p_cum_flag IN BOOLEAN,
117 p_need_by_date IN DATE,
118 p_line_location_id IN NUMBER, -- TIMEPHASED FPI
119 -- <FPJ Advanced Price START>
120 p_contract_id IN NUMBER,
121 p_org_id IN NUMBER,
122 p_supplier_id IN NUMBER,
123 p_supplier_site_id IN NUMBER,
124 p_creation_date IN DATE,
125 p_order_header_id IN NUMBER,
126 p_order_line_id IN NUMBER,
127 p_line_type_id IN NUMBER,
128 p_item_revision IN VARCHAR2,
129 p_item_id IN NUMBER,
130 p_category_id IN NUMBER,
131 p_supplier_item_num IN VARCHAR2,
132 p_uom IN VARCHAR2,
133 p_in_price IN NUMBER,
134 p_currency_code IN VARCHAR2, -- Bug 3564863
135 x_base_unit_price OUT NOCOPY NUMBER,
136 -- <FPJ Advanced Price END>
137 x_price_break_id OUT NOCOPY NUMBER, -- SERVICES FPJ
138 x_price OUT NOCOPY NUMBER, -- SERVICES FPJ
139 x_return_status OUT NOCOPY VARCHAR2,
140 p_req_line_price IN NUMBER ) -- Bug 7154646
141
142 IS
143 l_from_advanced_pricing VARCHAR2(1);
144 BEGIN
145
146 get_break_price
147 ( p_api_version => 1.0
148 , p_order_quantity => p_order_quantity
149 , p_ship_to_org => p_ship_to_org
150 , p_ship_to_loc => p_ship_to_loc
151 , p_po_line_id => p_po_line_id
152 , p_cum_flag => p_cum_flag
153 , p_need_by_date => p_need_by_date
154 , p_line_location_id => p_line_location_id
155 , p_contract_id => p_contract_id
156 , p_org_id => p_org_id
157 , p_supplier_id => p_supplier_id
158 , p_supplier_site_id => p_supplier_site_id
159 , p_creation_date => p_creation_date
160 , p_order_header_id => p_order_header_id
161 , p_order_line_id => p_order_line_id
162 , p_line_type_id => p_line_type_id
163 , p_item_revision => p_item_revision
164 , p_item_id => p_item_id
165 , p_category_id => p_category_id
166 , p_supplier_item_num=> p_supplier_item_num
167 , p_uom => p_uom
168 , p_in_price => p_in_price
169 , p_currency_code => p_currency_code
170 , x_base_unit_price => x_base_unit_price
171 , x_price_break_id => x_price_break_id
172 , x_price => x_price
173 , x_from_advanced_pricing => l_from_advanced_pricing
174 , x_return_status => x_return_status
175 , p_req_line_price => p_req_line_price
176 );
177
178 END;
179
180 /**==========================================================================
181 *
182 * FUNCTION NAME: get_break_price()
183 *
184 * Change History
185 * ==============
186 * Modified By Date Descriptions
187 *
188 * davidng 09/26/2002 FPI Timephased Pricing Project.
189 * Commented out the existing code that retrieves the
190 * price from the database. Replaced that with a new
191 * cursor called unit_price.
192 * davidng 11/20/2002 FPI Timephased Pricing Project.
193 * Change the Order By statement in get_break_price()
194 * to obtain the best exact match order by creation
195 * date and price.
196 *===========================================================================*/
197 PROCEDURE get_break_price(p_api_version IN NUMBER,
198 p_order_quantity IN NUMBER,
199 p_ship_to_org IN NUMBER,
200 p_ship_to_loc IN NUMBER,
201 p_po_line_id IN NUMBER,
202 p_cum_flag IN BOOLEAN,
203 p_need_by_date IN DATE,
204 p_line_location_id IN NUMBER, -- TIMEPHASED FPI
205 -- <FPJ Advanced Price START>
206 p_contract_id IN NUMBER,
207 p_org_id IN NUMBER,
208 p_supplier_id IN NUMBER,
209 p_supplier_site_id IN NUMBER,
210 p_creation_date IN DATE,
211 p_order_header_id IN NUMBER,
212 p_order_line_id IN NUMBER,
213 p_line_type_id IN NUMBER,
214 p_item_revision IN VARCHAR2,
215 p_item_id IN NUMBER,
216 p_category_id IN NUMBER,
217 p_supplier_item_num IN VARCHAR2,
218 p_uom IN VARCHAR2,
219 p_in_price IN NUMBER,
220 p_currency_code IN VARCHAR2, -- Bug 3564863
221 x_base_unit_price OUT NOCOPY NUMBER,
222 -- <FPJ Advanced Price END>
223 x_price_break_id OUT NOCOPY NUMBER, -- SERVICES FPJ
224 x_price OUT NOCOPY NUMBER, -- SERVICES FPJ
225 x_from_advanced_pricing OUT NOCOPY VARCHAR2, -- Bug# 4148430: Adding this flag
226 x_return_status OUT NOCOPY VARCHAR2,
227 p_req_line_price IN NUMBER ) --Bug 7154646: Adding this Parameter
228
229 IS
230
231 release_to_date number := 0;
232 l_price number := NULL; /* <TIMEPHASED FPI> Changed price to l_price */
233 test_quantity number := p_order_quantity;
234 match_type varchar2(4) := NULL;
235 l_progress varchar2(3) := '000';
236 -- Bug 670873, lpo, 05/28/98
237 old_quantity number := 0;
238
239 -- <FPJ Custom Price START>
240 l_source_document_type PO_HEADERS.type_lookup_code%TYPE;
241 l_source_document_header_id PO_LINES.po_header_id%TYPE;
242 l_pricing_date PO_LINE_LOCATIONS.need_by_date%TYPE;
243 l_new_price PO_LINES.unit_price%TYPE;
244 l_return_status VARCHAR2(1);
245 l_api_name CONSTANT varchar2(30) := 'GET_BREAK_PRICE';
246 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
247 -- <FPJ Custom Price END>
248
249 /* <TIMEPHASED FPI START> */
250 /* Declaration of a cursor used to derive the unit price */
251 /*
252 Bug 2800681.
253 Change the defaulting of null quantity to 0 instead of -1 in the
254 ORDER BY clause
255 */
256 cursor unit_price IS
257 SELECT pll.price_override,
258 pll.line_location_id -- SERVICES FPJ
259 FROM po_line_locations_all pll, -- GA FPI
260 po_headers_all poh -- 5684820
261 /*
262 Bug fix for 2687718.
263 Added QUOTATION in the WHERE clause to ensure that pricing works when
264 a Standard PO is sourced to a Quotation through the Supplier Catalog.
265 */
266 WHERE pll.shipment_type in ('PRICE BREAK', 'QUOTATION')
267 AND pll.po_line_id = p_po_line_id
268 AND pll.po_header_id = poh.po_header_id -- 5684820
269 --bug #2696731 arusingh
270 AND nvl(pll.quantity, 0) <= nvl(test_quantity, 0)
271 -- bug #2696731: modified org/loc checks to remove match_type
272 AND ((p_ship_to_org = pll.ship_to_organization_id) OR
273 (pll.ship_to_organization_id is null))
274 AND ((p_ship_to_loc = pll.ship_to_location_id) OR
275 (pll.ship_to_location_id is null))
276 AND (nvl(trunc(l_pricing_date), trunc(sysdate)) >= trunc(pll.start_date) -- FPJ Custom Price
277 OR
278 pll.start_date is null)
279 AND (nvl(trunc(l_pricing_date), trunc(sysdate)) <= trunc(pll.end_date) -- FPJ Custom Price
280 OR
281 pll.end_date is null)
282 -- Begin 5684820
283 AND (( pll.shipment_type ='QUOTATION' AND
284 (EXISTS (SELECT 1
285 FROM po_quotation_approvals_all
286 WHERE line_location_id = pll.line_location_id
287 AND sysdate BETWEEN nvl(start_date_active, sysdate-1)
288 AND nvl(end_date_active, sysdate+1)
289 )
290 AND nvl(poh.APPROVAL_REQUIRED_FLAG,'N') = 'Y'
291 )
292 OR
293 nvl(poh.APPROVAL_REQUIRED_FLAG,'N') = 'N'
294 )
295 OR pll.shipment_type ='PRICE BREAK')
296 -- End 5684820
297 ORDER BY pll.ship_to_organization_id ASC, pll.ship_to_location_id ASC,
298 NVL(pll.quantity, 0) DESC, -- to obtain the best exact matches
299 trunc(pll.creation_date) DESC, pll.price_override ASC; -- to sort the matches by creation date and then by price
300 /* <TIMEPHASED FPI END> */
301
302 l_api_version NUMBER := 1.0;
303
304 -- <FPJ Advanced Price START>
305 l_rate PO_HEADERS.rate%TYPE;
306 l_rate_type PO_HEADERS.rate_type%TYPE;
307 l_currency_code PO_HEADERS.currency_code%TYPE;
308 -- <FPJ Advanced Price END>
309
310 -- Bug 3343892
311 l_base_unit_price PO_LINES.base_unit_price%TYPE;
312
313 l_adv_price NUMBER; --<R12 GBPA Adv Pricing >
314 l_contract_id PO_LINES.contract_id%TYPE; --<R12 GBPA Adv Pricing >
315
316
317 BEGIN
318
319 -- <FPJ Advanced Price START>
320 -- Initialize OUT parameters
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322 x_base_unit_price := p_in_price;
323 x_price := p_in_price;
324 x_price_break_id := NULL;
325
326 -- Bug 3422411: Moved the initialization inside ELSIF p_contract_id IS NOT NULL clause
327 -- Bug 3340552, initialize with passed in price
328 -- l_price := p_in_price;
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_api_version',p_api_version);
333 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_quantity',p_order_quantity);
334 PO_DEBUG.debug_var(l_log_head,l_progress,'p_ship_to_org',p_ship_to_org);
335 PO_DEBUG.debug_var(l_log_head,l_progress,'p_ship_to_loc',p_ship_to_loc);
336 PO_DEBUG.debug_var(l_log_head,l_progress,'p_po_line_id',p_po_line_id);
337 PO_DEBUG.debug_var(l_log_head,l_progress,'p_cum_flag',p_cum_flag);
338 PO_DEBUG.debug_var(l_log_head,l_progress,'p_need_by_date',p_need_by_date);
339 PO_DEBUG.debug_var(l_log_head,l_progress,'p_line_location_id',p_line_location_id);
340 PO_DEBUG.debug_var(l_log_head,l_progress,'p_contract_id',p_contract_id);
341 PO_DEBUG.debug_var(l_log_head,l_progress,'p_org_id',p_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_uom',p_uom);
353 PO_DEBUG.debug_var(l_log_head,l_progress,'p_in_price',p_in_price);
354 PO_DEBUG.debug_var(l_log_head,l_progress,'p_currency_code',p_currency_code);
355 END IF;
356 -- <FPJ Advanced Price END>
357
358 -- Check for the API version
359 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361 END IF;
362
363 -- <FPJ Custom Price START>
364 -- SQL What: Find out the source document header id, and source doument type
365 -- SQL Why : Get source document line id to call GET_CUSTOM_PRICE_DATE,
366 -- Get source document type since we only allow custom pricing for
367 -- Blanket and Quotation.
368 IF g_debug_stmt THEN
369 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Get source document header id and type');
370 END IF;
371
372 IF (p_po_line_id IS NOT NULL) THEN
373 SELECT ph.type_lookup_code,
374 pl.po_header_id
375 INTO l_source_document_type,
376 l_source_document_header_id
377 FROM po_headers_all ph,
378 po_lines_all pl
379 WHERE ph.po_header_id = pl.po_header_id
380 AND pl.po_line_id = p_po_line_id;
381 ELSE
382 SELECT ph.type_lookup_code,
383 ph.po_header_id
384 INTO l_source_document_type,
385 l_source_document_header_id
386 FROM po_headers_all ph
387 WHERE ph.po_header_id = p_contract_id;
388 END IF; /*IF (p_po_line_id IS NOT NULL)*/
389
390
391 /* call the Custom Pricing Date API */
392 l_progress := '010';
393 IF g_debug_stmt THEN
394 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call get_custom_price_date');
395 PO_DEBUG.debug_var(l_log_head,l_progress,'p_source_document_header_id',l_source_document_header_id);
396 PO_DEBUG.debug_var(l_log_head,l_progress,'p_source_document_line_id',p_po_line_id);
397 PO_DEBUG.debug_var(l_log_head,l_progress,'p_quantity',p_order_quantity);
398 PO_DEBUG.debug_var(l_log_head,l_progress,'p_ship_to_location_id',p_ship_to_loc);
399 PO_DEBUG.debug_var(l_log_head,l_progress,'p_ship_to_organization_id',p_ship_to_org);
400 PO_DEBUG.debug_var(l_log_head,l_progress,'p_need_by_date',p_need_by_date);
401 END IF; /* IF g_debug_stmt */
402
403 /*Bug5598011 Pass the order_type as PO */
404 PO_CUSTOM_PRICE_PUB.GET_CUSTOM_PRICE_DATE
405 (p_api_version => 1.0,
406 p_source_document_header_id=> l_source_document_header_id,
407 p_source_document_line_id => p_po_line_id,
408 p_order_line_id => p_order_line_id, -- <Bug 3754828>
409 p_quantity => p_order_quantity,
410 p_ship_to_location_id => p_ship_to_loc,
411 p_ship_to_organization_id => p_ship_to_org,
412 p_need_by_date => p_need_by_date,
413 x_pricing_date => l_pricing_date,
414 x_return_status => l_return_status,
415 p_order_type => 'PO');
416
417
418 l_progress := '020';
419 IF g_debug_stmt THEN
420 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Calling get_custom_price_date');
421 PO_DEBUG.debug_var(l_log_head,l_progress,'l_pricing_date',l_pricing_date);
422 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status',l_return_status);
423 END IF; /* IF g_debug_stmt */
424
425 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
426 app_exception.raise_exception;
427 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
428
429 IF (l_pricing_date IS NULL) THEN
430 l_pricing_date := NVL(p_need_by_date, SYSDATE);
431 END IF; /* IF (l_pricing_date IS NULL) */
432
433 -- <FPJ Custom Price END>
434
435 -- <FPJ Advanced Price START>
436 -- Only allow custom pricing for Blanket and Quotation
437 IF p_po_line_id IS NOT NULL THEN
438 -- <FPJ Advanced Price END>
439
440 if (p_cum_flag = TRUE) then
441 -- <Manual Price Override FPJ START>
442 -- To improve performance, moved this so that we only call
443 -- get_release_quantity if pricing is cumulative.
444 l_progress := '040';
445 IF g_debug_stmt THEN
446 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Get Release Quantity');
447 END IF;
448
449 /* Call get_release_quantity to a) determine how to select
450 ** the correct break price and b) the quantity released against
451 ** the corresponding shipment/organization combination.
452 */
453 release_to_date := PO_SOURCING2_SV.get_release_quantity(p_ship_to_org,
454 p_ship_to_loc,
455 p_po_line_id,
456 match_type);
457
458 l_progress := '060';
459 IF g_debug_stmt THEN
460 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Get Release Quantity');
461 END IF;
462 -- <Manual Price Override FPJ END>
463
464 -- Bug 670873, lpo, 05/28/98
465 -- Take into account the old quantity saved on database
466
467 if (p_line_location_id is not null) then
468 -- Get the old quantity saved on the database
469 begin
470 select nvl(quantity, 0)
471 into old_quantity
472 from po_line_locations
473 where line_location_id = p_line_location_id;
474 exception
475 when others then old_quantity := 0;
476 end;
477 else
478 old_quantity := 0; -- Shouldn't be necessary, just in case ...
479 end if;
480
481 test_quantity := p_order_quantity + release_to_date - old_quantity;
482
483 end if;
484
485 /* Select the next applicable price break for the designated
486 ** quantity if we know there is a matching price break.
487 */
488
489 if (NVL(p_cum_flag,FALSE) = FALSE) -- <Manual Price Override FPJ>
490 OR (match_type <> 'NONE') then
491
492 l_progress := '100';
493
494 /* <TIMEPHASED FPI START> */
495
496 /* Open the cursor */
497 OPEN unit_price;
498
499 /* Get the price from the cursor */
500 FETCH unit_price into l_price , x_price_break_id; -- SERVICES FPJ
501
502 /* Close the cursor */
503 CLOSE unit_price;
504
505 /* <TIMEPHASED FPI END> */
506
507 end if;
508
509 /* If the order quantity was too small to yield a price break,
510 ** or if no price breaks exist/match the release org/location then
511 ** get the blanket line price as a default.
512 */
513
514 l_progress := '120';
515
516 if (l_price is null) then /* <TIMEPHASED FPI> */
517 SELECT pl.unit_price
518 INTO l_price /* <TIMEPHASED FPI> */
519 FROM po_lines_all pl -- GA FPI
520 WHERE pl.po_line_id = p_po_line_id;
521
522 x_price_break_id := null; -- SERVICES FPJ
523 end if;
524
525 --<R12 GBPA Adv Pricing Support Start>
526
527 -- Pass the Price break price into Advance pricing
528 l_adv_price := l_price ;
529
530 -- Call Advanced Pricing for global Blanket purchase agreements
531 -- Do not call Advanced Pricing for Blanket Purchase agreements and quotations
532
533
534 IF ( l_source_document_type = 'BLANKET'
535 AND PO_GA_PVT.is_global_agreement(l_source_document_header_id))THEN
536
537
538 IF (PO_ADVANCED_PRICE_PVT.is_valid_qp_line_type(p_line_type_id)) THEN
539
540 -- bug5339880
541 -- Use PO Currency directly, if provided. No need to pass in
542 -- rate information anymore
543
544 IF (p_currency_code IS NULL) THEN
545 l_currency_code :=
546 PO_HEADERS_SV3.get_currency_code(l_source_document_header_id);
547 ELSE
548 l_currency_code := p_currency_code;
549 END IF;
550
551 l_rate_type := NULL;
552 l_rate := NULL;
553
554 l_progress := '125';
555 IF g_debug_stmt THEN
556 PO_DEBUG.debug_var(l_log_head,l_progress,'l_currency_code',l_currency_code);
557 PO_DEBUG.debug_var(l_log_head,l_progress,'l_adv_price',l_adv_price);
558 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call Advanced Pricing API(GBPA)');
559 END IF; /* IF g_debug_stmt */
560
561
562 PO_ADVANCED_PRICE_PVT.get_advanced_price
563 ( p_org_id => p_org_id
564 , p_supplier_id => p_supplier_id
565 , p_supplier_site_id => p_supplier_site_id
566 , p_creation_date => p_creation_date
567 , p_order_type => 'PO'
568 , p_ship_to_location_id => p_ship_to_loc
569 , p_ship_to_org_id => p_ship_to_org
570 , p_order_header_id => p_order_header_id
571 , p_order_line_id => p_order_line_id
572 , p_item_revision => p_item_revision
573 , p_item_id => p_item_id
574 , p_category_id => p_category_id
575 , p_supplier_item_num => p_supplier_item_num
576 , p_agreement_type => l_source_document_type
577 , p_agreement_id => l_source_document_header_id
578 , p_agreement_line_id => p_po_line_id
579 , p_rate => l_rate
580 , p_rate_type => l_rate_type
581 , p_currency_code => l_currency_code
582 , p_need_by_date => l_pricing_date
583 , p_quantity => p_order_quantity
584 , p_uom => p_uom
585 , p_unit_price => l_adv_price
586 , x_base_unit_price => l_base_unit_price
587 , x_unit_price => l_price
588 , x_return_status => l_return_status );
589
590 l_progress := '130';
591 IF g_debug_stmt THEN
592 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Call Advanced Pricing API(GBPA)');
593 END IF;
594
595 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
596 app_exception.raise_exception;
597 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
598
599 l_progress := '135';
600 IF g_debug_stmt THEN
601 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',l_return_status);
602 PO_DEBUG.debug_var(l_log_head,l_progress,'x_base_unit_price',l_base_unit_price);
603 PO_DEBUG.debug_var(l_log_head,l_progress,'x_price',l_price);
604 END IF;
605
606 ELSE /* Invalid Line type*/
607
608 IF g_debug_stmt THEN
609 PO_DEBUG.debug_stmt(l_log_head, l_progress,
610 'Not a valid price type to call Advanced Pricing API(GBPA)');
611 END IF;
612
613 END IF; /*IF (PO_ADVANCED_PRICE_PVT.is_valid_qp_line_type(p_line_type_id))*/
614
615 END IF; /* IF l_source_document_type IN ('BLANKET')* and global */
616
617 --<R12 GBPA Adv Pricing Support End>
618
619 --Pass the Price passed from Advance Pricing to Custom Price Hook
620
621
622 -- <FPJ Custom Price START>
623 l_progress := '140';
624 IF g_debug_stmt THEN
625 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Call get_custom_po_price');
626 PO_DEBUG.debug_var(l_log_head,l_progress, 'p_order_quantity',p_order_quantity);
627 PO_DEBUG.debug_var(l_log_head,l_progress, 'p_ship_to_org',p_ship_to_org);
628 PO_DEBUG.debug_var(l_log_head,l_progress, 'p_ship_to_loc',p_ship_to_loc);
629 PO_DEBUG.debug_var(l_log_head,l_progress, 'p_po_line_id',p_po_line_id);
630 PO_DEBUG.debug_var(l_log_head,l_progress, 'p_need_by_date',p_need_by_date);
631 PO_DEBUG.debug_var(l_log_head,l_progress, 'p_pricing_date',l_pricing_date);
632 PO_DEBUG.debug_var(l_log_head,l_progress, 'p_line_location_id',p_line_location_id);
633 PO_DEBUG.debug_var(l_log_head,l_progress, 'p_price',l_price);
634 END IF; /* IF g_debug_stmt */
635
636 PO_CUSTOM_PRICE_PUB.GET_CUSTOM_PO_PRICE(p_api_version => 1.0,
637 p_order_quantity => p_order_quantity,
638 p_ship_to_org => p_ship_to_org,
639 p_ship_to_loc => p_ship_to_loc,
640 p_po_line_id => p_po_line_id,
641 p_cum_flag => p_cum_flag,
642 p_need_by_date => p_need_by_date,
643 p_pricing_date => l_pricing_date,
644 p_line_location_id => p_line_location_id,
645 p_price => l_price,
646 x_new_price => l_new_price,
647 x_return_status => l_return_status,
648 p_req_line_price => p_req_line_price);
649
650 l_progress := '160';
651 IF g_debug_stmt THEN
652 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'After Call get_custom_po_price');
653 PO_DEBUG.debug_var(l_log_head,l_progress, 'x_new_price',l_new_price);
654 PO_DEBUG.debug_var(l_log_head,l_progress, 'x_return_status',x_return_status);
655 END IF; /* IF g_debug_stmt */
656
657 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
658 app_exception.raise_exception;
659 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
660
661 IF (l_new_price IS NOT NULL) THEN -- The custom API returned back a price.
662 IF (l_new_price < 0) THEN -- Price has to be greater than or equal to zero
663 po_message_s.app_error('PO_CUSTOM_PRICE_LESS_0',
664 'ROUTINE', l_api_name,
665 'ERR_NUMBER', l_progress,
666 'CUSTOM_PRICING_API', 'GET_CUSTOM_PO_PRICE');
667 app_exception.raise_exception;
668 END IF; /* IF (l_new_price <0) */
669 l_price := l_new_price;
670 END IF; /* IF (l_new_price is not NULL) */
671
672 x_base_unit_price := l_price; -- <FPJ Advanced Price>
673 -- <FPJ Custom Price END>
674
675 -- <FPJ Advanced Price START>
676 ELSIF (p_contract_id IS NOT NULL) THEN
677
678 -- Bug 3422411: Moved the initialization here to avoid regressing the behavior
679 -- when the referenced document is not contract. When a contract is referenced
680 -- and Advanced Pricing API does not return any new price,the value of p_in_price
681 -- will be passed back
682 l_price := p_in_price;
683
684 IF (PO_ADVANCED_PRICE_PVT.is_valid_qp_line_type(p_line_type_id)) THEN
685 l_progress := '180';
686 IF g_debug_stmt THEN
687 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call Advanced Pricing API(CPA)');
688 END IF;
689
690 l_progress := '200';
691 IF g_debug_stmt THEN
692 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Get Currency Info');
693 END IF; /* IF g_debug_stmt */
694
695
696 -- bug5339880
697 -- Use the currency code from the PO always. No need to pass in
698 -- rate information anymore
699
700 IF (p_currency_code IS NULL) THEN
701 l_currency_code := PO_HEADERS_SV3.get_currency_code(p_contract_id);
702 ELSE
703 l_currency_code := p_currency_code;
704 END IF;
705
706 l_rate_type := NULL;
707 l_rate := NULL;
708
709 l_progress := '220';
710 IF g_debug_stmt THEN
711 PO_DEBUG.debug_var(l_log_head,l_progress,'l_currency_code',l_currency_code);
712 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call Advanced Pricing API(CPA)');
713 END IF; /* IF g_debug_stmt */
714
715 PO_ADVANCED_PRICE_PVT.get_advanced_price
716 ( p_org_id => p_org_id
717 , p_supplier_id => p_supplier_id
718 , p_supplier_site_id => p_supplier_site_id
719 , p_creation_date => p_creation_date
720 , p_order_type => 'PO'
721 , p_ship_to_location_id => p_ship_to_loc
722 , p_ship_to_org_id => p_ship_to_org
723 , p_order_header_id => p_order_header_id
724 , p_order_line_id => p_order_line_id
725 , p_item_revision => p_item_revision
726 , p_item_id => p_item_id
727 , p_category_id => p_category_id
728 , p_supplier_item_num => p_supplier_item_num
729 , p_agreement_type => 'CONTRACT'
730 , p_agreement_id => p_contract_id
731 , p_rate => l_rate
732 , p_rate_type => l_rate_type
733 , p_currency_code => l_currency_code
734 , p_need_by_date => l_pricing_date
735 , p_quantity => p_order_quantity
736 , p_uom => p_uom
737 , p_unit_price => p_in_price
738 -- Bug 3343892, Don't pass back Advanced Price list price
739 -- , x_base_unit_price => x_base_unit_price
740 , x_base_unit_price => l_base_unit_price
741 , x_unit_price => l_price
742 , x_return_status => l_return_status );
743
744 l_progress := '380';
745 IF g_debug_stmt THEN
746 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Call Advanced Pricing API(CPA)');
747 END IF;
748
749 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
750 app_exception.raise_exception;
751 END IF; /* IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) */
752
753 ELSE
754 l_progress := '400';
755 IF g_debug_stmt THEN
756 PO_DEBUG.debug_stmt(l_log_head, l_progress,
757 'Not a valid price type to call Advanced Pricing API(CPA)');
758 END IF;
759 END IF; /*IF (PO_ADVANCED_PRICE_PVT.is_valid_qp_line_type(p_line_type_id))*/
760
761 END IF; /* IF p_po_line_id IS NOT NULL */
762 -- <FPJ Advanced Price END>
763
764 x_price := l_price;
765
766 -- Bug# 4148430: Calculating x_from_advanced_pricing.
767 -- WORKAROUND: Until QP fixes their API
768 x_from_advanced_pricing := 'N';
769 BEGIN
770 SELECT 'Y' INTO x_from_advanced_pricing
771 FROM qp_ldets_v
772 WHERE line_index = 1
773 AND LIST_LINE_TYPE_CODE = 'PLL';
774 EXCEPTION WHEN OTHERS THEN
775 null; -- if no_data_found then value remains unchanged. If other error no need to raise it
776 END;
777
778 l_progress := '400';
779 IF g_debug_stmt THEN
780 PO_DEBUG.debug_end(l_log_head);
781 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
782 PO_DEBUG.debug_var(l_log_head,l_progress,'x_base_unit_price',x_base_unit_price);
783 PO_DEBUG.debug_var(l_log_head,l_progress,'x_price',x_price);
784 PO_DEBUG.debug_var(l_log_head,l_progress,'x_price_break_id',x_price_break_id);
785 END IF; /* IF g_debug_stmt */
786
787 EXCEPTION
788
789 when others then
790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
791 po_message_s.sql_error('get_break_price', l_progress, sqlcode);
792 raise;
793
794 end get_break_price;
795
796 /*===========================================================================
797
798 FUNCTION NAME: get_release_quantity()
799
800 ===========================================================================*/
801 FUNCTION get_release_quantity(x_ship_to_org IN NUMBER,
802 x_ship_to_loc IN NUMBER,
803 x_po_line_id IN NUMBER,
804 x_match_type IN OUT NOCOPY VARCHAR2) return NUMBER is
805
806 price_break_org number := NULL;
807 price_break_loc number := NULL;
808 release_quantity number := 0;
809 temp_quantity number := 0;
810 all_rls_quantity number := 0;
811 exclude_quantity number := 0;
812 subtract_quantity number := 0;
813 candidate_quantity number := 0;
814 progress varchar2(3) := NULL;
815
816 /* Define a cursor to select the distinct ship-to
817 ** organization and location combinations in the
818 ** price breaks for the designated agreement
819 ** line.
820 */
821
822 cursor C1 is
823 SELECT distinct nvl(pll.ship_to_organization_id, -1),
824 nvl(pll.ship_to_location_id, -1)
825 FROM po_line_locations_all pll -- GA FPI
826 /*
827 Bug fix for 2687718.
828 Added QUOTATION in the WHERE clause to ensure that pricing works when
829 a Standard PO is sourced to a Quotation through the Supplier Catalog.
830 */
831 WHERE pll.shipment_type in ('PRICE BREAK', 'QUOTATION')
832 AND pll.po_line_id = x_po_line_id;
833
834 BEGIN
835
836 x_match_type := NULL;
837
838 open C1;
839 loop
840
841 FETCH C1
842 INTO price_break_org, price_break_loc;
843
844 exit when C1%notfound;
845
846 SELECT sum(pll.quantity - nvl(pll.quantity_cancelled, 0))
847 INTO temp_quantity
848 FROM po_line_locations pll
849 WHERE decode(price_break_org, -1, pll.ship_to_organization_id,
850 price_break_org) = pll.ship_to_organization_id
851 AND decode(price_break_loc, -1, pll.ship_to_location_id,
852 price_break_loc) = pll.ship_to_location_id
853 AND pll.shipment_type <> 'PRICE BREAK'
854 AND pll.po_line_id = x_po_line_id;
855
856 /* If no quantity is returned, then temp_quantity will be null.
857 ** Set to 0 for subsequent math using this variable.
858 */
859
860 if (temp_quantity is null) then
861 temp_quantity := 0;
862 end if;
863
864 /* See package specification for details on how this algorithm
865 ** works. It is easier to read if not laced with comments.
866 */
867
868 if (price_break_org = x_ship_to_org) then
869 if (price_break_loc = x_ship_to_loc) then
870 x_match_type := 'ALL';
871 release_quantity := temp_quantity;
872 exit;
873 elsif (price_break_loc = -1) then
874 x_match_type := 'ORG';
875 candidate_quantity := temp_quantity;
876 elsif (price_break_loc <> x_ship_to_loc) then
877 subtract_quantity := subtract_quantity + temp_quantity;
878 end if;
879 elsif (price_break_org = -1) then
880 if (x_match_type is null) then
881 x_match_type := 'NULL';
882 end if;
883 all_rls_quantity := temp_quantity;
884 else
885 exclude_quantity := exclude_quantity + temp_quantity;
886 end if;
887 end loop;
888
889 /* Use greatest function to return 0 if the result can be
890 ** a negative number.
891 */
892
893 if (x_match_type = 'ALL') then
894 return(release_quantity);
895
896 elsif (x_match_type = 'ORG') then
897 return(greatest(candidate_quantity - subtract_quantity, 0));
898
899 elsif (x_match_type = 'NULL') then
900 return(greatest(all_rls_quantity - candidate_quantity
901 - subtract_quantity
902 - exclude_quantity, 0));
903 else
904 x_match_type := 'NONE';
905 return(0);
906 end if;
907
908 EXCEPTION
909
910 when others then
911 po_message_s.sql_error('get_release_quantity', progress, sqlcode);
912 raise;
913
914 end get_release_quantity;
915
916 /*===========================================================================
917
918 FUNCTION NAME: get_item_detail()
919
920 ===========================================================================*/
921 FUNCTION get_item_detail(X_item_id IN NUMBER,
922 X_org_id IN NUMBER,
923 X_planned_item_flag IN OUT NOCOPY VARCHAR2,
924 X_list_price IN OUT NOCOPY NUMBER,
925 X_primary_uom IN OUT NOCOPY VARCHAR2) return BOOLEAN IS
926
927 X_progress varchar2(3) := NULL;
928
929 BEGIN
930
931 X_progress := '010';
932 /*Bug 979118
933 If the item's MRP Planning codes are (MRP/DRP-7
934 MPS/DRP-8
935 DRP -9) then the item should be
936 considered as a planned item.Prior to the fix items with planning codes
937 MRP(3) and MPS(4) only were considered as a planned item.
938 */
939
940 select decode(msi.mrp_planning_code, 3,'Y',4,'Y',7,'Y',8,'Y',9,'Y',
941 decode(msi.inventory_planning_code,1,'Y',2,'Y', 'N'))
942 , msi.list_price_per_unit
943 , msi.primary_unit_of_measure
944 into X_planned_item_flag
945 , X_list_price
946 , X_primary_uom
947 from mtl_system_items msi
948 where msi.inventory_item_id = X_item_id
949 and msi.organization_id = X_org_id;
950
951 return (TRUE);
952
953 EXCEPTION
954
955 when no_data_found then
956 return (FALSE);
957
958 when others then
959 po_message_s.sql_error('get_item_detail', X_progress, sqlcode);
960 raise;
961
962 END get_item_detail;
963
964 /*===========================================================================
965
966 FUNCTION NAME: get_display_find_option()
967
968 ===========================================================================*/
969 FUNCTION get_display_find_option return VARCHAR2 IS
970
971 X_progress varchar2(3) := NULL;
972 X_option_value varchar2(1) := NULL;
973
974 BEGIN
975
976 X_progress := '010';
977
978 fnd_profile.get('PO_SIC_DISPLAY_FIND', X_option_value);
979 return (X_option_value);
980
981 EXCEPTION
982
983 when others then
984 po_message_s.sql_error('get_display_find_option', X_progress, sqlcode);
985 raise;
986
987 END get_display_find_option;
988
989 /*===========================================================================
990
991 FUNCTION NAME: get_default_results_option()
992
993 ===========================================================================*/
994 FUNCTION get_default_results_option return VARCHAR2 IS
995
996 X_progress varchar2(3) := NULL;
997 X_option_value varchar2(30) := NULL;
998
999 BEGIN
1000
1001 X_progress := '010';
1002
1003 fnd_profile.get('PO_SIC_DEFAULT_OPTION', X_option_value);
1004 return (X_option_value);
1005
1006 EXCEPTION
1007
1008 when others then
1009 po_message_s.sql_error('get_display_find_option', X_progress, sqlcode);
1010 raise;
1011
1012 END get_default_results_option;
1013
1014
1015 /*===========================================================================
1016
1017 PROCEDURE NAME: update_line_price()
1018
1019 This procedure updates the line price of a document.
1020 ===========================================================================*/
1021 -- <FPJ Advanced Price START>
1022 PROCEDURE update_line_price
1023 (
1024 p_po_line_id IN NUMBER
1025 , p_price IN NUMBER
1026 , p_from_line_location_id IN NUMBER -- <SERVICES FPJ>
1027 )
1028 IS
1029 BEGIN
1030
1031 update_line_price
1032 ( p_po_line_id => p_po_line_id
1033 , p_price => p_price
1034 , p_base_unit_price => p_price
1035 , p_from_line_location_id => p_from_line_location_id
1036 );
1037 END update_line_price;
1038 -- <FPJ Advanced Price END>
1039
1040
1041 PROCEDURE update_line_price
1042 (
1043 p_po_line_id IN NUMBER
1044 , p_price IN NUMBER
1045 , p_base_unit_price IN NUMBER -- <FPJ Advanced Price>
1046 , p_from_line_location_id IN NUMBER -- <SERVICES FPJ>
1047 )
1048 IS
1049
1050 g_user_id number := fnd_global.user_id;
1051
1052 BEGIN
1053
1054 UPDATE po_lines_all
1055 SET unit_price = p_price,
1056 base_unit_price = p_base_unit_price, -- <FPJ Advanced Price>
1057 from_line_location_id = p_from_line_location_id, -- <SERVICES FPJ>
1058 last_update_date = sysdate,
1059 last_updated_by = g_user_id
1060 where po_line_id = p_po_line_id;
1061
1062 EXCEPTION
1063 when others then
1064 po_message_s.sql_error('update_line_price', '', sqlcode);
1065 raise;
1066
1067 END update_line_price;
1068
1069
1070 /*===========================================================================
1071
1072 PROCEDURE NAME: update_shipment_price()
1073
1074 This procedure updates the shipment price of a document.
1075 ===========================================================================*/
1076 PROCEDURE update_shipment_price(p_price IN NUMBER,
1077 p_line_location_id IN NUMBER) IS
1078 g_user_id number := fnd_global.user_id;
1079
1080 BEGIN
1081
1082 UPDATE po_line_locations
1083 SET price_override = p_price,
1084 last_update_date = sysdate,
1085 last_updated_by = g_user_id
1086 WHERE line_location_id = p_line_location_id;
1087
1088 EXCEPTION
1089 when others then
1090 po_message_s.sql_error('update_shipment_price', '', sqlcode);
1091 raise;
1092
1093 END update_shipment_price;
1094
1095
1096 /*===========================================================================
1097
1098 PROCEDURE NAME: get_min_shipment_num()
1099
1100 This procedure gets the minimum shipment number of a Standard PO line
1101 ===========================================================================*/
1102 PROCEDURE get_min_shipment_num(p_po_line_id IN NUMBER,
1103 x_min_shipment_num OUT NOCOPY NUMBER) IS
1104 BEGIN
1105 select min(shipment_num)
1106 into x_min_shipment_num
1107 from po_line_locations_all
1108 where po_line_id = p_po_line_id
1109 and nvl(cancel_flag, 'N') = 'N'
1110 and nvl(closed_code, 'OPEN') <> 'FINALLY CLOSED';
1111 EXCEPTION
1112 when others then
1113 po_message_s.sql_error('get_min_shipment_num', '', sqlcode);
1114 raise;
1115 END;
1116
1117
1118 /*===========================================================================
1119
1120 PROCEDURE NAME: get_shipment_price()
1121
1122 This procedure prices a Standard PO based on its source document reference.
1123 ===========================================================================*/
1124
1125 -- <FPJ Advanced Price START>
1126 PROCEDURE get_shipment_price
1127 ( p_po_line_id IN NUMBER,
1128 p_from_line_id IN NUMBER,
1129 p_min_shipment_num IN NUMBER,
1130 p_quantity IN NUMBER,
1131 x_price OUT NOCOPY NUMBER,
1132 x_from_line_location_id OUT NOCOPY NUMBER -- <SERVICES FPJ>
1133 )
1134 IS
1135 l_base_unit_price PO_LINES.base_unit_price%TYPE;
1136 BEGIN
1137 get_shipment_price
1138 ( p_po_line_id => p_po_line_id
1139 , p_from_line_id => p_from_line_id
1140 , p_min_shipment_num => p_min_shipment_num
1141 , p_quantity => p_quantity
1142 , p_contract_id => NULL
1143 , p_org_id => NULL
1144 , p_supplier_id => NULL
1145 , p_supplier_site_id => NULL
1146 , p_creation_date => NULL
1147 , p_order_header_id => NULL
1148 , p_order_line_id => NULL
1149 , p_line_type_id => NULL
1150 , p_item_revision => NULL
1151 , p_item_id => NULL
1152 , p_category_id => NULL
1153 , p_supplier_item_num => NULL
1154 , p_uom => NULL
1155 , p_currency_code => NULL -- Bug 3564863
1156 , p_in_price => NULL
1157 , x_base_unit_price => l_base_unit_price
1158 , x_price => x_price
1159 , x_from_line_location_id => x_from_line_location_id
1160 );
1161
1162 END get_shipment_price;
1163 -- <FPJ Advanced Price END>
1164
1165 PROCEDURE get_shipment_price
1166 ( p_po_line_id IN NUMBER,
1167 p_from_line_id IN NUMBER,
1168 p_min_shipment_num IN NUMBER,
1169 p_quantity IN NUMBER,
1170 -- <FPJ Advanced Price START>
1171 p_contract_id IN NUMBER,
1172 p_org_id IN NUMBER,
1173 p_supplier_id IN NUMBER,
1174 p_supplier_site_id IN NUMBER,
1175 p_creation_date IN DATE,
1176 p_order_header_id IN NUMBER,
1177 p_order_line_id IN NUMBER,
1178 p_line_type_id IN NUMBER,
1179 p_item_revision IN VARCHAR2,
1180 p_item_id IN NUMBER,
1181 p_category_id IN NUMBER,
1182 p_supplier_item_num IN VARCHAR2,
1183 p_uom IN VARCHAR2,
1184 p_currency_code IN VARCHAR2, -- Bug 3564863
1185 p_in_price IN NUMBER,
1186 x_base_unit_price OUT NOCOPY NUMBER,
1187 -- <FPJ Advanced Price END>
1188 x_price OUT NOCOPY NUMBER,
1189 x_from_line_location_id OUT NOCOPY NUMBER -- <SERVICES FPJ>
1190 ) IS
1191
1192 l_progress varchar2(3) := null;
1193 l_ship_to_location_id number := null;
1194 l_ship_to_organization_id number := null;
1195 l_need_by_date date := null;
1196 l_line_location_id number := null;
1197 l_return_status VARCHAR2(1); -- <SERVICES FPJ>
1198
1199 BEGIN
1200 l_progress := '001';
1201 BEGIN
1202 select poll.ship_to_location_id, poll.ship_to_organization_id, poll.need_by_date, poll.line_location_id
1203 into l_ship_to_location_id, l_ship_to_organization_id, l_need_by_date, l_line_location_id
1204 from po_line_locations_all poll
1205 where poll.po_line_id = p_po_line_id
1206 and poll.shipment_num = p_min_shipment_num;
1207 EXCEPTION
1208 when others then
1209 null;
1210 END;
1211
1212 l_progress := '002';
1213
1214 -- <SERVICES FPJ>
1215 --
1216 PO_SOURCING2_SV.get_break_price
1217 ( p_api_version => 1.0
1218 , p_order_quantity => p_quantity
1219 , p_ship_to_org => l_ship_to_organization_id
1220 , p_ship_to_loc => l_ship_to_location_id
1221 , p_po_line_id => p_from_line_id
1222 , p_cum_flag => FALSE
1223 , p_need_by_date => l_need_by_date
1224 , p_line_location_id => l_line_location_id
1225 -- <FPJ Advanced Price START>
1226 , p_contract_id => p_contract_id
1227 , p_org_id => p_org_id
1228 , p_supplier_id => p_supplier_id
1229 , p_supplier_site_id => p_supplier_site_id
1230 , p_creation_date => p_creation_date
1231 , p_order_header_id => p_order_header_id
1232 , p_order_line_id => p_order_line_id
1233 , p_line_type_id => p_line_type_id
1234 , p_item_revision => p_item_revision
1235 , p_item_id => p_item_id
1236 , p_category_id => p_category_id
1237 , p_supplier_item_num => p_supplier_item_num
1238 , p_in_price => p_in_price
1239 , p_uom => p_uom
1240 , p_currency_code => p_currency_code -- Bug 3564863
1241 , x_base_unit_price => x_base_unit_price
1242 -- <FPJ Advanced Price END>
1243 , x_price_break_id => x_from_line_location_id
1244 , x_price => x_price
1245 , x_return_status => l_return_status
1246 );
1247 -- <SERVICES FPJ END>
1248
1249 EXCEPTION
1250 when others then
1251 po_message_s.sql_error('get_shipment_price', l_progress, sqlcode);
1252 raise;
1253 END get_shipment_price;
1254
1255
1256 END PO_SOURCING2_SV;