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