1 PACKAGE BODY PO_CUSTOM_PRICE_PUB AS
2 /* $Header: POXPCPRB.pls 120.6.12020000.2 2013/02/10 19:38:18 vegajula ship $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'PO_CUSTOM_PRICE_PUB';
5
6 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.';
7
8 -- Debugging
9 g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
10 g_debug_unexp BOOLEAN := PO_DEBUG.is_debug_unexp_on;
11
12 --------------------------------------------------------------------------------
13 --Start of Comments
14 --Name: get_custom_price_date
15 --Pre-reqs:
16 -- None.
17 --Modifies:
18 -- None.
19 --Locks:
20 -- None.
21 --Function:
22 -- This procedure returns custom price date.
23 --Parameters:
24 --IN:
25 --p_api_version
26 -- Version number of API that caller expects. It
27 -- should match the l_api_version defined in the
28 -- procedure (expected value : 1.0)
29 --p_source_document_header_id
30 -- The header id of the source document.
31 --p_source_document_line_id
32 -- The line id of the source document.
33 --p_order_line_id
34 -- The line id of the order document (PO or Requisition).
35 --p_quantity
36 -- Quantity
37 --p_ship_to_location_id
38 -- Ship to location
39 --p_ship_to_organization_id
40 -- Ship to organization
41 --p_need_by_date
42 -- Need by date
43 --OUT:
44 --x_pricing_date
45 -- New customized price date
46 --x_return_status
47 -- FND_API.G_RET_STS_SUCCESS if API succeeds
48 -- FND_API.G_RET_STS_ERROR if API fails
49 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
50 -- Bug5598011 Added new parameter p_order_type which will indicate whether the
51 -- order document is REQUISITION or PO.
52 --Testing:
53 --
54 --End of Comments
55 -------------------------------------------------------------------------------
56 PROCEDURE GET_CUSTOM_PRICE_DATE(p_api_version IN NUMBER,
57 p_source_document_header_id IN NUMBER, -- <FPJ Advanced Price>
58 p_source_document_line_id IN NUMBER,
59 p_order_line_id IN NUMBER, -- <Bug 3754828>
60 p_quantity IN NUMBER,
61 p_ship_to_location_id IN NUMBER,
62 p_ship_to_organization_id IN NUMBER,
63 p_need_by_date IN DATE,
64 x_pricing_date OUT NOCOPY DATE,
65 x_return_status OUT NOCOPY VARCHAR2,
66 p_order_type IN VARCHAR2) --<Bug5598011>
67
68 IS
69 l_api_version NUMBER := 1.0;
70 l_api_name VARCHAR2(60) := 'GET_CUSTOM_PRICE_DATE';
71 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
72 l_progress VARCHAR2(3) := '000';
73 BEGIN
74 -- Check for the API version
75 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
76 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
77 END IF;
78
79 /* This is where the customer will plug in their own custom logic.
80 The following lines will have to be replaced with your custom code
81 determining the value of the OUT parameters. */
82 x_pricing_date := NULL;
83 x_return_status := FND_API.G_RET_STS_SUCCESS;
84
85 END GET_CUSTOM_PRICE_DATE;
86
87 --------------------------------------------------------------------------------
88 --Start of Comments
89 --Name: get_custom_req_price
90 --Pre-reqs:
91 -- None.
92 --Modifies:
93 -- None.
94 --Locks:
95 -- None.
96 --Function:
97 -- This procedure returns custom price.
98 --Parameters:
99 --IN:
100 --p_api_version
101 -- Version number of API that caller expects. It
102 -- should match the l_api_version defined in the
103 -- procedure (expected value : 1.0)
104 --p_source_document_header_id
105 -- The header id of the source document.
106 --p_source_document_line_num
107 -- The line number of the source document.
108 --p_quantity
109 -- Quantity
110 --p_unit_of_measure
111 -- Unit of Measure
112 --p_deliver_to_location_id
113 -- Deliver to location
114 --p_required_currency
115 -- Required currency
116 --p_required_rate_type
117 -- Required rate type
118 --p_need_by_date
119 -- Need By date
120 --p_pricing_date
121 -- New custom pricing date
122 --p_destination_org_id
123 -- Destination Org
124 --p_currency_price
125 -- Caculated currency price
126 --OUT:
127 --x_new_currency_price
128 -- New customized currency price
129 --x_return_status
130 -- FND_API.G_RET_STS_SUCCESS if API succeeds
131 -- FND_API.G_RET_STS_ERROR if API fails
132 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
133 --Testing:
134 --
135 --End of Comments
136 -------------------------------------------------------------------------------
137 PROCEDURE GET_CUSTOM_REQ_PRICE(p_api_version IN NUMBER,
138 p_source_document_header_id IN NUMBER,
139 p_source_document_line_num IN NUMBER,
140 p_order_line_id IN NUMBER, -- <Bug 3754828>
141 p_quantity IN NUMBER,
142 p_unit_of_measure IN VARCHAR2,
143 p_deliver_to_location_id IN NUMBER,
144 p_required_currency IN VARCHAR2,
145 p_required_rate_type IN VARCHAR2,
146 p_need_by_date IN DATE,
147 p_pricing_date IN DATE,
148 p_destination_org_id IN NUMBER,
149 p_currency_price IN NUMBER,
150 x_new_currency_price OUT NOCOPY NUMBER,
151 x_return_status OUT NOCOPY VARCHAR2)
152 IS
153 l_api_version NUMBER := 1.0;
154 l_api_name VARCHAR2(60) := 'GET_CUSTOM_REQ_PRICE';
155 BEGIN
156 -- Check for the API version
157 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
159 END IF;
160
161 /* This is where the customer will plug in their own custom pricing logic.
162 The following lines will have to be replaced with your custom code
163 determining the value of the OUT parameters. */
164 x_new_currency_price := NULL;
165 x_return_status := FND_API.G_RET_STS_SUCCESS;
166
167 END GET_CUSTOM_REQ_PRICE;
168
169 --------------------------------------------------------------------------------
170 --Start of Comments
171 --Name: get_custom_req_price
172 --Pre-reqs:
173 -- None.
174 --Modifies:
175 -- None.
176 --Locks:
177 -- None.
178 --Function:
179 -- This procedure returns custom price.
180 --Parameters:
181 --IN:
182 --p_api_version
183 -- Version number of API that caller expects. It
184 -- should match the l_api_version defined in the
185 -- procedure (expected value : 1.0)
186 --p_order_quantity
187 -- Order Quantity
188 --p_ship_to_org
189 -- Ship to Org
190 --p_ship_to_loc
191 -- Ship to location
192 --p_po_line_id
193 -- PO Line ID
194 --p_cum_flag
195 -- Cumulated flag
196 --p_need_by_date
197 -- Need By date
198 --p_pricing_date
199 -- New custom pricing date
200 --p_line_location_id
201 -- Line location ID
202 --p_price
203 -- Caculated price
204 -- /* Bug 7154646 Adding the following Parameter */
205 --p_base_unit_price
206 -- Base Unit Price
207 --OUT:
208 --x_new_price
209 -- New customized price
210 --p_order_line_id
211 -- The line id of the order document.
212 --x_return_status
213 -- FND_API.G_RET_STS_SUCCESS if API succeeds
214 -- FND_API.G_RET_STS_ERROR if API fails
215 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
216 --Testing:
217 --
218 --End of Comments
219 -------------------------------------------------------------------------------
220 PROCEDURE GET_CUSTOM_PO_PRICE(p_api_version IN NUMBER,
221 p_order_quantity IN NUMBER,
222 p_ship_to_org IN NUMBER,
223 p_ship_to_loc IN NUMBER,
224 p_po_line_id IN NUMBER,
225 p_cum_flag IN BOOLEAN,
226 p_need_by_date IN DATE,
227 p_pricing_date IN DATE,
228 p_line_location_id IN NUMBER,
229 p_price IN NUMBER,
230 x_new_price OUT NOCOPY NUMBER,
231 x_return_status OUT NOCOPY VARCHAR2,
232 p_req_line_price IN NUMBER,
233 p_order_line_id IN NUMBER DEFAULT NULL) -- <Bug 15871591>
234 IS
235 l_api_version NUMBER := 1.0;
236 l_api_name VARCHAR2(60) := 'GET_CUSTOM_PO_PRICE';
237 BEGIN
238 -- Check for the API version
239 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
241 END IF;
242
243 /* This is where the customer will plug in their own custom pricing logic.
244 The following lines will have to be replaced with your custom code
245 determining the value of the OUT parameters. */
246 x_new_price := NULL;
247 x_return_status := FND_API.G_RET_STS_SUCCESS;
248
249 END GET_CUSTOM_PO_PRICE;
250
251
252 -- <Bug 3794940 START>
253 -------------------------------------------------------------------------------
254 --Start of Comments
255 --Name: audit_qp_price_adjustment
256 --Pre-reqs:
257 -- None.
258 --Modifies:
259 -- None.
260 --Locks:
261 -- None.
262 --Function:
263 -- This procedure allows customer to audit advanced pricing adjustments.
264 --Parameters:
265 --IN:
266 --p_api_version
267 -- Version number of API that caller expects. It
268 -- should match the l_api_version defined in the
269 -- procedure (expected value : 1.0)
270 --p_order_type
271 -- The type of the order document (PO or Requisition).
272 --p_order_line_id
273 -- The line id of the order document (PO or Requisition).
274 --p_line_index
275 -- The index of pricing adjustments stored in temporary view QP_LDETS_V, the query to
276 -- fetch records from view QP_LDETS_V should have:
277 -- qp_ldets_v.line_index = p_line_index AND
278 -- qp_ldets_v.automatic_flag = 'Y'
279 --OUT:
280 --x_return_status
281 -- FND_API.G_RET_STS_SUCCESS if API succeeds
282 -- FND_API.G_RET_STS_ERROR if API fails
283 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
284 --Testing:
285 --
286 --End of Comments
287 -------------------------------------------------------------------------------
288
289 PROCEDURE audit_qp_price_adjustment(p_api_version IN NUMBER,
290 p_order_type IN VARCHAR2,
291 p_order_line_id IN NUMBER,
292 p_line_index IN NUMBER,
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_msg_count OUT NOCOPY NUMBER,
295 x_msg_data OUT NOCOPY VARCHAR2)
296 IS
297 l_api_version NUMBER := 1.0;
298 l_api_name VARCHAR2(60) := 'AUDIT_QP_PRICE_ADJUSTMENT';
299 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
300 l_progress VARCHAR2(3) := '000';
301 BEGIN
302 IF g_debug_stmt THEN
303 PO_DEBUG.debug_begin(l_log_head);
304 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_type', p_order_type);
305 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_line_id',p_order_line_id);
306 PO_DEBUG.debug_var(l_log_head,l_progress,'p_line_index',p_line_index);
307 END IF;
308
309 -- Check for the API version
310 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) )
311 THEN
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 END IF;
314
315 /* This is where the customer will plug in their own custom logic.
316 The following lines will have to be replaced with your custom code
317 determining the value of the OUT parameters. */
318 x_return_status := FND_API.G_RET_STS_SUCCESS;
319 x_msg_count := 0;
320 x_msg_data := NULL;
321
322 IF g_debug_stmt THEN
323 PO_DEBUG.debug_end(l_log_head);
324 END IF;
325
326 END audit_qp_price_adjustment;
327 -- <Bug 3794940 END>
328
329 -- < Bug 7430760 START>
330 --------------------------------------------------------------------------------
331 --Start of Comments
332 --Name: GET_CUST_INTERNAL_REQ_PRICE
333 --Pre-reqs:
334 -- None.
335 --Modifies:
336 -- None.
337 --Locks:
338 -- None.
339 --Function:
340 -- This procedure returns custom price date.
341 --Parameters:
342 -- IN PARAMETERS
343 --
344 -- p_item_id : inventory item id of the item for which custom price is fetched
345 --
346 -- p_category_id : category id of the category for which custom price is fetched
347 --
348 -- p_req_header_id : Header id of the requisition
349 --
350 -- p_req_line_id : Line of the requisition line
351 --
352 -- p_src_organization_id : source inventory organization of the requisition line
353 --
354 -- p_src_sub_inventory : Source sub inventory
355 --
356 -- p_dest_organization_id : destination inventory organization of the requisition line
357 --
358 -- p_dest_sub_inventory : destination sub inventory
359 --
360 -- p_deliver_to_location_id : deliver to location in the destination organization
361 --
362 -- p_need_by_date : need by date
363 --
364 -- p_unit_of_measure : unit of measure of the requisition line
365 --
366 -- p_quantity : quantity
367 --
368 -- p_currency_code : currency code of the source organization
369 --
370 -- p_rate : Rate of conversion of p_currency_code to fsp currency code
371 --
372 -- p_rate_type : type of conversion
373 --
374 -- p_rate_date : date of conversion
375 --
376 -- OUT PARAMETERS
377 -- x_return_status
378 -- FND_API.G_RET_STS_SUCCESS if API succeeds
379 -- FND_API.G_RET_STS_ERROR if API fails
380 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
381 --
382 -- IN OUT PARAMETERS
383 -- x_unit_price
384 -- custom price if the custom code is in place
385 -- system price if the custom code is not in place
386 --End of Comments
387 -------------------------------------------------------------------------------
388 PROCEDURE GET_CUST_INTERNAL_REQ_PRICE(p_api_version IN NUMBER,
389 p_item_id IN NUMBER DEFAULT NULL,
390 p_category_id IN NUMBER DEFAULT NULL,
391 p_req_header_id IN NUMBER DEFAULT NULL,
392 p_req_line_id IN NUMBER DEFAULT NULL,
393 p_src_organization_id IN NUMBER DEFAULT NULL,
394 p_src_sub_inventory IN VARCHAR2 DEFAULT NULL,
395 p_dest_organization_id IN NUMBER DEFAULT NULL,
396 p_dest_sub_inventory IN VARCHAR2 DEFAULT NULL,
397 p_deliver_to_location_id IN NUMBER DEFAULT NULL,
398 p_need_by_date IN DATE DEFAULT NULL,
399 p_unit_of_measure IN VARCHAR2 DEFAULT NULL,
400 p_quantity IN NUMBER DEFAULT NULL,
401 p_currency_code IN VARCHAR2 DEFAULT NULL,
402 p_rate IN NUMBER DEFAULT NULL,
403 p_rate_type IN VARCHAR2 DEFAULT NULL,
404 p_rate_date IN DATE DEFAULT NULL,
405 x_return_status OUT NOCOPY VARCHAR2,
406 x_unit_price IN OUT NOCOPY NUMBER
407 )
408
409 IS
410 l_api_version NUMBER := 1.0;
411 l_api_name VARCHAR2(60) := 'GET_CUST_INTERNAL_REQ_PRICE';
412 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
413 l_progress VARCHAR2(3) := '000';
414 BEGIN
418 END IF;
415 -- Check for the API version
416 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
419
420 /* This is where the customer will plug in their own custom logic.
421 The following lines will have to be replaced with your custom code
422 determining the value of the OUT parameters. Assign the value of
423 the custom price to the IN OUT parameter x_unit_price*/
424 x_return_status := FND_API.G_RET_STS_SUCCESS;
425
426 END GET_CUST_INTERNAL_REQ_PRICE;
427
428 -- < Bug 7430760 END>
429
430 --------------------------------------------------------------------------------
431 --Start of Comments
432 --Name: GET_CUSTOM_INTERNAL_REQ_PRICE
433 --Pre-reqs:
434 -- None.
435 --Modifies:
436 -- None.
437 --Locks:
438 -- None.
439 --Function:
440 -- This function call the procedure GET_CUST_INTERNAL_REQ_PRICE to and returns
441 -- the unit price.This function is called from req import to fetch the unit price.
442 -- This function is used only for internal coding purpose.
443 -- DO NOT CUSTOMIZE THIS FUNCTION.
444 -- CUSTOMIZE THE PROCEDURE GET_CUST_INTERNAL_REQ_PRICE
445 --Parameters:
446 -- IN PARAMETERS
447 --
448 -- p_item_id : inventory item id of the item for which custom price is fetched
449 --
450 -- p_category_id : category id of the category for which custom price is fetched
451 --
452 -- p_req_header_id : Header id of the requisition
453 --
454 -- p_req_line_id : Line of the requisition line
455 --
456 -- p_src_organization_id : source inventory organization of the requisition line
457 --
458 -- p_src_sub_inventory : Source sub inventory
459 --
460 -- p_dest_organization_id : destination inventory organization of the requisition line
461 --
462 -- p_dest_sub_inventory : destination sub inventory
463 --
464 -- p_deliver_to_location_id : deliver to location in the destination organization
465 --
466 -- p_need_by_date : need by date
467 --
468 -- p_unit_of_measure : unit of measure of the requisition line
469 --
470 -- p_quantity : quantity
471 --
472 -- p_currency_code : currency code of the source organization
473 --
474 -- p_rate : Rate of conversion of p_currency_code to fsp currency code
475 --
476 -- p_rate_type : type of conversion
477 --
478 -- p_rate_date : date of conversion
479 --
480 -- p_unit_price : unit price fetched from inventory. This price is returned
481 -- if there is no custom code.
482 --
483 -- OUT PARAMETERS
484
485 -- x_unit_price
486 -- custom price if the custom code is in place
487 -- system price if the custom code is not in place
488 --End of Comments
489 -------------------------------------------------------------------------------
490
491 FUNCTION GET_CUSTOM_INTERNAL_REQ_PRICE(p_api_version IN NUMBER,
492 p_item_id IN NUMBER DEFAULT NULL,
493 p_category_id IN NUMBER DEFAULT NULL,
494 p_req_header_id IN NUMBER DEFAULT NULL,
495 p_req_line_id IN NUMBER DEFAULT NULL,
496 p_src_organization_id IN NUMBER DEFAULT NULL,
497 p_src_sub_inventory IN VARCHAR2 DEFAULT NULL,
498 p_dest_organization_id IN NUMBER DEFAULT NULL,
499 p_dest_sub_inventory IN VARCHAR2 DEFAULT NULL,
500 p_deliver_to_location_id IN NUMBER DEFAULT NULL,
501 p_need_by_date IN DATE DEFAULT NULL,
502 p_unit_of_measure IN VARCHAR2 DEFAULT NULL,
503 p_quantity IN NUMBER DEFAULT NULL,
504 p_currency_code IN VARCHAR2 DEFAULT NULL,
505 p_rate IN NUMBER DEFAULT NULL,
506 p_rate_type IN VARCHAR2 DEFAULT NULL,
507 p_rate_date IN DATE DEFAULT NULL,
508 p_unit_price IN NUMBER DEFAULT NULL
509 ) RETURN NUMBER IS
510
511 l_api_version NUMBER := 1.0;
512 l_api_name VARCHAR2(60) := 'GET_CUSTOM_INTERNAL_REQ_PRICE';
513 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
514 l_progress VARCHAR2(3) := '000';
515 x_unit_price NUMBER;
516 x_return_status varchar2(1);
517
518 BEGIN
519
520 IF g_debug_stmt THEN
521 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
522 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
523 || l_progress,'Before calling procedure GET_CUST_INTERNAL_REQ_PRICE ');
524 END IF;
525 END IF;
526
527 x_unit_price := p_unit_price;
528
529 GET_CUST_INTERNAL_REQ_PRICE(p_api_version => 1.0,
530 p_item_id => p_item_id,
531 p_category_id => p_category_id,
532 p_req_header_id => p_req_header_id,
533 p_req_line_id => p_req_line_id,
534 p_src_organization_id => p_src_organization_id,
535 p_src_sub_inventory => p_src_sub_inventory,
536 p_dest_organization_id => p_dest_organization_id,
537 p_dest_sub_inventory => p_dest_sub_inventory,
538 p_deliver_to_location_id => p_deliver_to_location_id,
539 p_need_by_date => p_need_by_date,
540 p_unit_of_measure => p_unit_of_measure,
541 p_quantity => p_quantity,
542 p_currency_code => p_currency_code,
543 p_rate => p_rate,
544 p_rate_type => p_rate_type,
545 p_rate_date => p_rate_date,
549
546 x_return_status => x_return_status,
547 x_unit_price => x_unit_price
548 );
550 IF (x_return_status <> 'S') THEN
551 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
552 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
553 || l_progress,'After calling procedure GET_CUST_INTERNAL_REQ_PRICE. Return status is : '|| x_return_status );
554 END IF;
555 app_exception.raise_exception;
556 END IF;
557
558 return(x_unit_price);
559
560 END GET_CUSTOM_INTERNAL_REQ_PRICE;
561
562 END PO_CUSTOM_PRICE_PUB; -- Package spec
[Home] [Help]