1 PACKAGE BODY PO_CUSTOM_PRICE_PUB AS
2 /* $Header: POXPCPRB.pls 120.2.12010000.4 2008/09/29 07:42:25 lswamina 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 --x_return_status
211 -- FND_API.G_RET_STS_SUCCESS if API succeeds
212 -- FND_API.G_RET_STS_ERROR if API fails
213 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
214 --Testing:
215 --
216 --End of Comments
217 -------------------------------------------------------------------------------
218 PROCEDURE GET_CUSTOM_PO_PRICE(p_api_version IN NUMBER,
219 p_order_quantity IN NUMBER,
220 p_ship_to_org IN NUMBER,
221 p_ship_to_loc IN NUMBER,
222 p_po_line_id IN NUMBER,
223 p_cum_flag IN BOOLEAN,
224 p_need_by_date IN DATE,
225 p_pricing_date IN DATE,
226 p_line_location_id IN NUMBER,
227 p_price IN NUMBER,
228 x_new_price OUT NOCOPY NUMBER,
229 x_return_status OUT NOCOPY VARCHAR2,
230 p_req_line_price IN NUMBER)
231 IS
232 l_api_version NUMBER := 1.0;
233 l_api_name VARCHAR2(60) := 'GET_CUSTOM_PO_PRICE';
234 BEGIN
235 -- Check for the API version
236 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238 END IF;
239
240 /* This is where the customer will plug in their own custom pricing logic.
241 The following lines will have to be replaced with your custom code
242 determining the value of the OUT parameters. */
243 x_new_price := NULL;
244 x_return_status := FND_API.G_RET_STS_SUCCESS;
245
246 END GET_CUSTOM_PO_PRICE;
247
248
249 -- <Bug 3794940 START>
250 -------------------------------------------------------------------------------
251 --Start of Comments
252 --Name: audit_qp_price_adjustment
253 --Pre-reqs:
254 -- None.
255 --Modifies:
256 -- None.
257 --Locks:
258 -- None.
259 --Function:
260 -- This procedure allows customer to audit advanced pricing adjustments.
261 --Parameters:
262 --IN:
263 --p_api_version
264 -- Version number of API that caller expects. It
265 -- should match the l_api_version defined in the
266 -- procedure (expected value : 1.0)
267 --p_order_type
268 -- The type of the order document (PO or Requisition).
269 --p_order_line_id
270 -- The line id of the order document (PO or Requisition).
271 --p_line_index
272 -- The index of pricing adjustments stored in temporary view QP_LDETS_V, the query to
273 -- fetch records from view QP_LDETS_V should have:
274 -- qp_ldets_v.line_index = p_line_index AND
275 -- qp_ldets_v.automatic_flag = 'Y'
276 --OUT:
277 --x_return_status
278 -- FND_API.G_RET_STS_SUCCESS if API succeeds
279 -- FND_API.G_RET_STS_ERROR if API fails
280 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
281 --Testing:
282 --
283 --End of Comments
284 -------------------------------------------------------------------------------
285
286 PROCEDURE audit_qp_price_adjustment(p_api_version IN NUMBER,
287 p_order_type IN VARCHAR2,
288 p_order_line_id IN NUMBER,
289 p_line_index IN NUMBER,
290 x_return_status OUT NOCOPY VARCHAR2,
291 x_msg_count OUT NOCOPY NUMBER,
292 x_msg_data OUT NOCOPY VARCHAR2)
293 IS
294 l_api_version NUMBER := 1.0;
295 l_api_name VARCHAR2(60) := 'AUDIT_QP_PRICE_ADJUSTMENT';
296 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
297 l_progress VARCHAR2(3) := '000';
298 BEGIN
299 IF g_debug_stmt THEN
300 PO_DEBUG.debug_begin(l_log_head);
301 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_type', p_order_type);
302 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_line_id',p_order_line_id);
303 PO_DEBUG.debug_var(l_log_head,l_progress,'p_line_index',p_line_index);
304 END IF;
305
306 -- Check for the API version
307 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) )
308 THEN
309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
310 END IF;
311
312 /* This is where the customer will plug in their own custom logic.
313 The following lines will have to be replaced with your custom code
314 determining the value of the OUT parameters. */
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316 x_msg_count := 0;
317 x_msg_data := NULL;
318
319 IF g_debug_stmt THEN
320 PO_DEBUG.debug_end(l_log_head);
321 END IF;
322
323 END audit_qp_price_adjustment;
324 -- <Bug 3794940 END>
325
326 -- < Bug 7430760 START>
327 --------------------------------------------------------------------------------
328 --Start of Comments
329 --Name: GET_CUST_INTERNAL_REQ_PRICE
330 --Pre-reqs:
331 -- None.
332 --Modifies:
333 -- None.
334 --Locks:
335 -- None.
336 --Function:
337 -- This procedure returns custom price date.
338 --Parameters:
339 -- IN PARAMETERS
340 --
341 -- p_item_id : inventory item id of the item for which custom price is fetched
342 --
343 -- p_category_id : category id of the category for which custom price is fetched
344 --
345 -- p_req_header_id : Header id of the requisition
346 --
347 -- p_req_line_id : Line of the requisition line
348 --
349 -- p_src_organization_id : source inventory organization of the requisition line
350 --
351 -- p_src_sub_inventory : Source sub inventory
352 --
353 -- p_dest_organization_id : destination inventory organization of the requisition line
354 --
355 -- p_dest_sub_inventory : destination sub inventory
356 --
357 -- p_deliver_to_location_id : deliver to location in the destination organization
358 --
359 -- p_need_by_date : need by date
360 --
361 -- p_unit_of_measure : unit of measure of the requisition line
362 --
363 -- p_quantity : quantity
364 --
365 -- p_currency_code : currency code of the source organization
366 --
367 -- p_rate : Rate of conversion of p_currency_code to fsp currency code
368 --
369 -- p_rate_type : type of conversion
370 --
371 -- p_rate_date : date of conversion
372 --
373 -- OUT PARAMETERS
374 -- x_return_status
375 -- FND_API.G_RET_STS_SUCCESS if API succeeds
376 -- FND_API.G_RET_STS_ERROR if API fails
377 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
378 --
379 -- IN OUT PARAMETERS
380 -- x_unit_price
381 -- custom price if the custom code is in place
382 -- system price if the custom code is not in place
383 --End of Comments
384 -------------------------------------------------------------------------------
385 PROCEDURE GET_CUST_INTERNAL_REQ_PRICE(p_api_version IN NUMBER,
386 p_item_id IN NUMBER DEFAULT NULL,
387 p_category_id IN NUMBER DEFAULT NULL,
388 p_req_header_id IN NUMBER DEFAULT NULL,
389 p_req_line_id IN NUMBER DEFAULT NULL,
390 p_src_organization_id IN NUMBER DEFAULT NULL,
391 p_src_sub_inventory IN VARCHAR2 DEFAULT NULL,
392 p_dest_organization_id IN NUMBER DEFAULT NULL,
393 p_dest_sub_inventory IN VARCHAR2 DEFAULT NULL,
394 p_deliver_to_location_id IN NUMBER DEFAULT NULL,
395 p_need_by_date IN DATE DEFAULT NULL,
396 p_unit_of_measure IN VARCHAR2 DEFAULT NULL,
397 p_quantity IN NUMBER DEFAULT NULL,
398 p_currency_code IN VARCHAR2 DEFAULT NULL,
399 p_rate IN NUMBER DEFAULT NULL,
400 p_rate_type IN VARCHAR2 DEFAULT NULL,
401 p_rate_date IN DATE DEFAULT NULL,
402 x_return_status OUT NOCOPY VARCHAR2,
403 x_unit_price IN OUT NOCOPY NUMBER
404 )
405
406 IS
407 l_api_version NUMBER := 1.0;
408 l_api_name VARCHAR2(60) := 'GET_CUST_INTERNAL_REQ_PRICE';
409 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
410 l_progress VARCHAR2(3) := '000';
411 BEGIN
412 -- Check for the API version
413 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
415 END IF;
416
417 /* This is where the customer will plug in their own custom logic.
418 The following lines will have to be replaced with your custom code
419 determining the value of the OUT parameters. Assign the value of
420 the custom price to the IN OUT parameter x_unit_price*/
421 x_return_status := FND_API.G_RET_STS_SUCCESS;
422
423 END GET_CUST_INTERNAL_REQ_PRICE;
424
425 -- < Bug 7430760 END>
426
427 END PO_CUSTOM_PRICE_PUB; -- Package spec