1 PACKAGE PO_SOURCING2_SV AUTHID CURRENT_USER as
2 /* $Header: POXSCS2S.pls 120.5 2011/01/20 00:51:48 ajunnikr ship $ */
3
4 /*===========================================================================
5 PACKAGE NAME: PO_SOURCING2_SV
6
7 DESCRIPTION: This package contains the server side Supplier Item
8 Catalog and Sourcing Application Program Interfaces
9 (APIs).
10
11 CLIENT/SERVER: Server
12
13 OWNER: Liza Broadbent
14
15 FUNCTION/PROCEDURE: get_break_price()
16 get_release_quantity()
17 get_display_find_option()
18 get_default_results_option()
19 get_item_detail()
20
21 ===========================================================================*/
22
23 /*===========================================================================
24 FUNCTION NAME: get_default_results_option
25
26 DESCRIPTION: Retrieves the PO: Default Supplier-Item Catalog Results
27 profile option.
28
29
30 PARAMETERS:
31
32 RETURN TYPE: VARCHAR2
33
34 DESIGN REFERENCES:
35
36 ALGORITHM:
37
38 NOTES:
39
40 OPEN ISSUES:
41
42 CLOSED ISSUES:
43
44 CHANGE HISTORY: Created 15-AUG-95 LBROADBE
45 ===========================================================================*/
46 FUNCTION get_default_results_option return VARCHAR2;
47
48 /*===========================================================================
49 FUNCTION NAME: get_display_find_option
50
51 DESCRIPTION: Retreives the PO: Display Find on Open Catalog profile
52 profile option.
53
54
55 PARAMETERS:
56
57 RETURN TYPE: VARCHAR2
58
59 DESIGN REFERENCES:
60
61 ALGORITHM:
62
63 NOTES:
64
65 OPEN ISSUES:
66
67 CLOSED ISSUES:
68
69 CHANGE HISTORY: Created 15-AUG-95 LBROADBE
70 ===========================================================================*/
71 FUNCTION get_display_find_option return VARCHAR2;
72
73 /*===========================================================================
74 FUNCTION NAME: get_break_price
75
76 DESCRIPTION: Returns the appropriate break price for a blanket
77 release shipment. If no break price is available
78 (the order quantity is too small, there are no
79 matching price breaks, or the blanket line has no
80 price breaks) it returns the blanket line price.
81 SERVICES FPJ : Also returns price break id if the price is from a price break
82
83
84 PARAMETERS:
85
86 RETURN TYPE: NUMBER
87
88 DESIGN REFERENCES:
89
90 ALGORITHM:
91
92 NOTES:
93
94 OPEN ISSUES:
95
96 CLOSED ISSUES:
97
98 CHANGE HISTORY: Created 01-NOV-95 LBROADBE
99 ===========================================================================*/
100
101 PROCEDURE get_break_price(p_api_version IN NUMBER,
102 p_order_quantity IN NUMBER,
103 p_ship_to_org IN NUMBER,
104 p_ship_to_loc IN NUMBER,
105 p_po_line_id IN NUMBER,
106 p_cum_flag IN BOOLEAN,
107 p_need_by_date IN DATE, -- TIMEPHASED FPI
108 p_line_location_id IN NUMBER,
109 p_req_line_price IN NUMBER DEFAULT NULL,--bug 8845486
110 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
111 p_pricing_call_src IN VARCHAR2 DEFAULT NULL, --<Enhanced Pricing: parameter to identify calls from retro/auto creation>
112 x_price_break_id OUT NOCOPY NUMBER, -- SERVICES FPJ
113 x_price OUT NOCOPY NUMBER, -- SERVICES FPJ
114 x_return_status OUT NOCOPY VARCHAR2 );
115
116 --<Enhanced Pricing Start>
117 /*===========================================================================
118 PROCEDURE NAME: get_break_price
119
120 DESCRIPTION: Overloaded price break API
121
122 ===========================================================================*/
123 PROCEDURE get_break_price(p_order_quantity IN NUMBER,
124 p_ship_to_org IN NUMBER,
125 p_ship_to_loc IN NUMBER,
126 p_po_line_id IN NUMBER,
127 p_cum_flag IN BOOLEAN,
128 p_need_by_date IN DATE,
129 p_line_location_id IN NUMBER,
130 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
131 p_pricing_call_src IN VARCHAR2 DEFAULT NULL, --<Enhanced Pricing: parameter to identify calls from retro/auto creation>
132 x_price OUT NOCOPY NUMBER,
133 x_base_unit_price OUT NOCOPY NUMBER
134 );
135 --<Enhanced Pricing End>
136
137 /*===========================================================================
138 FUNCTION NAME: get_break_price
139
140 DESCRIPTION: Overloaded price break API
141
142 ===========================================================================*/
143 FUNCTION get_break_price(x_order_quantity IN NUMBER,
144 x_ship_to_org IN NUMBER,
145 x_ship_to_loc IN NUMBER,
146 x_po_line_id IN NUMBER,
147 x_cum_flag IN BOOLEAN,
148 p_need_by_date IN DATE, /* <TIMEPHASED FPI> */
149 x_line_location_id IN NUMBER DEFAULT NULL,
150 p_req_line_price IN NUMBER DEFAULT NULL, --bug 8845486
151 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
152 p_pricing_call_src IN VARCHAR2 DEFAULT NULL --<Enhanced Pricing: parameter to identify calls from retro/auto creation>
153 )
154 return NUMBER;
155
156
157 -- <FPJ Advanced Price START>
158 PROCEDURE get_break_price(p_api_version IN NUMBER,
159 p_order_quantity IN NUMBER,
160 p_ship_to_org IN NUMBER,
161 p_ship_to_loc IN NUMBER,
162 p_po_line_id IN NUMBER,
163 p_cum_flag IN BOOLEAN,
164 p_need_by_date IN DATE,
165 p_line_location_id IN NUMBER,
166 p_contract_id IN NUMBER,
167 p_org_id IN NUMBER,
168 p_supplier_id IN NUMBER,
169 p_supplier_site_id IN NUMBER,
170 p_creation_date IN DATE,
171 p_order_header_id IN NUMBER,
172 p_order_line_id IN NUMBER,
173 p_line_type_id IN NUMBER,
174 p_item_revision IN VARCHAR2,
175 p_item_id IN NUMBER,
176 p_category_id IN NUMBER,
177 p_supplier_item_num IN VARCHAR2,
178 p_uom IN VARCHAR2,
179 p_in_price IN NUMBER,
180 p_currency_code IN VARCHAR2, -- Bug 3564863
181 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
182 p_pricing_call_src IN VARCHAR2 DEFAULT NULL, --<Enhanced Pricing: parameter to identify calls from retro/auto creation>
183 x_base_unit_price OUT NOCOPY NUMBER,
184 x_price_break_id OUT NOCOPY NUMBER,
185 x_price OUT NOCOPY NUMBER,
186 x_return_status OUT NOCOPY VARCHAR2,
187 p_req_line_price IN NUMBER DEFAULT NULL ); -- Bug 7154646
188 -- <FPJ Advanced Price END>
189 -- Bug# 4148430: Adding x_negotiated_by_preparer_flag.
190 PROCEDURE get_break_price(p_api_version IN NUMBER,
191 p_order_quantity IN NUMBER,
192 p_ship_to_org IN NUMBER,
193 p_ship_to_loc IN NUMBER,
194 p_po_line_id IN NUMBER,
195 p_cum_flag IN BOOLEAN,
196 p_need_by_date IN DATE,
197 p_line_location_id IN NUMBER,
198 p_contract_id IN NUMBER,
199 p_org_id IN NUMBER,
200 p_supplier_id IN NUMBER,
201 p_supplier_site_id IN NUMBER,
202 p_creation_date IN DATE,
203 p_order_header_id IN NUMBER,
204 p_order_line_id IN NUMBER,
205 p_line_type_id IN NUMBER,
206 p_item_revision IN VARCHAR2,
207 p_item_id IN NUMBER,
208 p_category_id IN NUMBER,
209 p_supplier_item_num IN VARCHAR2,
210 p_uom IN VARCHAR2,
211 p_in_price IN NUMBER,
212 p_currency_code IN VARCHAR2, -- Bug 3564863
213 --<Enhanced Pricing Start>
214 p_draft_id IN NUMBER DEFAULT NULL,
215 p_src_flag IN VARCHAR2 DEFAULT NULL,
216 p_doc_sub_type IN VARCHAR2 DEFAULT NULL,
217 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
218 p_pricing_call_src IN VARCHAR2 DEFAULT NULL, --<Enhanced Pricing: parameter to identify calls from retro/auto creation>
219 --<Enhanced Pricing End>
220 x_base_unit_price OUT NOCOPY NUMBER,
221 x_price_break_id OUT NOCOPY NUMBER,
222 x_price OUT NOCOPY NUMBER,
223 -- Bug# 4148430
224 x_from_advanced_pricing OUT NOCOPY VARCHAR2,
225 x_return_status OUT NOCOPY VARCHAR2,
226 p_req_line_price IN NUMBER DEFAULT NULL ); -- Bug 7154646
227
228 /*===========================================================================
229 FUNCTION NAME: get_release_quantity
230
231 DESCRIPTION: Returns a) the quantity released to-date and b)
232 the appropriate price break org/location combination
233 that should be used to select the break price.
234
235
236 PARAMETERS:
237
238 RETURN TYPE: NUMBER
239
240 DESIGN REFERENCES:
241
242 ALGORITHM: Assume the following ship-to organization/location
243 combinations exist for a blanket line's price
244 breaks:
245
246 ORG LOC MAP TO QUANTIY VARIABLES
247 ----------- ------------------------
248 | A | X | --> release_quantity
249 -----------
250 | A | - | --> candidate_quantity
251 -----------
252 | - | - | --> all_rls_quantity
253 -----------
254 | A | E | --> subtract_quantity
255 -----------
256 | D | H | --> exclude_quantity
257 -----------
258
259 To find the quantity released for org A, loc X:
260
261 o Sum the quantity released against nonmatching
262 orgs (in this case D). Store the result in
263 variable exclude_quantity.
264
265 o Sum the quantity released against org A, and
266 locations != X (in this case A, E). Store
267 the result in variable subtract_quantity.
268
269 o Sum the quantity released against org A and a
270 NULL location. Store the result in variable
271 candidate_quantity. If we do not find an
272 exact org/loc match, this is the price break
273 org/loc combination we are interested in.
274
275 If a price break exists (with matching org and
276 null shipment), set x_match_type = 'ORG'.
277
278 o Sum the quantity released against org A, loc X.
279
280 If a price break exists (with matching org and
281 matching location), set x_match_type = 'ALL'
282 and exit the loop so we can return this qty.
283
284 o If there is a price break with a NULL org and
285 a NULL location, sum total quantity released
286 against the blanket line. Store this result
287 in variable all_rls_quantity. If x_match_type
288 is null, set x_match_type = 'NULL.' If it is
289 not null, then it will equal 'ORG' which is a
290 closer match than a fully null price break (it
291 will never equal 'ALL' for this test since we
292 exit as soon as we find the exact match).
293
294 RESULT HIERARCHY
295 ----------------
296 If an exact match is found (org A, loc X) return
297 this exact release quantity and x_match_type of
298 'ALL.'
299
300 If a half-match exists, return an x_match_type of
301 'ORG.' The release quantity in this case =
302 candidate_quantity - subtract_quantity. If the
303 result is negative, set to 0.
304
305 If a null org/loc price break exists, return an
306 x_match_type of 'NULL.' The release quantity in
307 this case = all_rls_quantity - candidate_quantity -
308 subtract_quantity - exclude_quantity. If the
309 result is negative, set to 0.
310
311 Elsif no matching price breaks are found,
312 the x_match_type is 'NONE' and the release
313 quantity = 0.
314
315
316 NOTES: This is different from release 10 functionality
317 (which was incorrect). The release 10 code for
318 AutoSource and Releases will need to be changed
319 for consistency. Verified w/ kmiller.
320
321 OPEN ISSUES:
322
323 CLOSED ISSUES:
324
325 CHANGE HISTORY: Created 01-NOV-95 LBROADBE
326 ===========================================================================*/
327 FUNCTION get_release_quantity(x_ship_to_org IN NUMBER,
328 x_ship_to_loc IN NUMBER,
329 x_po_line_id IN NUMBER,
330 x_match_type IN OUT NOCOPY VARCHAR2) return NUMBER;
331
332 /*===========================================================================
333 FUNCTION NAME: get_item_detail
334
335 DESCRIPTION:
336
337 PARAMETERS: X_item_id IN NUMBER,
338 X_org_id IN NUMBER,
339 X_planned_item_flag IN OUT VARCHAR2,
340 X_list_price IN OUT NUMBER,
341 X_primary_uom IN OUT VARCHAR2
342
343 RETURN VALUE: BOOLEAN
344
345 DESIGN REFERENCES:
346
347 ALGORITHM:
348
349 NOTES:
350
351 OPEN ISSUES:
352
353 CLOSED ISSUES:
354
355 CHANGE HISTORY: Created 15-AUG-95 LBROADBE
356 ===========================================================================*/
357 FUNCTION get_item_detail(X_item_id IN NUMBER,
358 X_org_id IN NUMBER,
359 X_planned_item_flag IN OUT NOCOPY VARCHAR2,
360 X_list_price IN OUT NOCOPY NUMBER,
361 X_primary_uom IN OUT NOCOPY VARCHAR2) return BOOLEAN;
362
363
364
365 PROCEDURE update_line_price
366 ( p_po_line_id IN NUMBER
367 , p_price IN NUMBER
368 , p_from_line_location_id IN NUMBER -- <SERVICES FPJ>
369 );
370
371 -- <FPJ Advanced Price START>
372 PROCEDURE update_line_price
373 ( p_po_line_id IN NUMBER
374 , p_price IN NUMBER
375 , p_base_unit_price IN NUMBER
376 , p_from_line_location_id IN NUMBER -- <SERVICES FPJ>
377 );
378 -- <FPJ Advanced Price END>
379
380
381 /*===========================================================================
382 PROCEDURE NAME: update_shipment_price
383
384 DESCRIPTION:
385
386 PARAMETERS: p_price IN NUMBER,
387 p_line_location_id IN NUMBER
388
389 RETURN VALUE: None
390
391 DESIGN REFERENCES:
392
393 ALGORITHM:
394
395 NOTES:
396
397 OPEN ISSUES:
398
399 CLOSED ISSUES:
400
401 CHANGE HISTORY: Created 20-DEC-02 DAVIDNG
402 ===========================================================================*/
403 PROCEDURE update_shipment_price(p_price IN NUMBER,
404 p_line_location_id IN NUMBER);
405
406
407 /*===========================================================================
408 PROCEDURE NAME: get_min_shipment_num
409
410 DESCRIPTION:
411
412 PARAMETERS: p_po_line_id IN NUMBER,
413 x_min_shipment_num OUT NOCOPY NUMBER
414
415 RETURN VALUE: None
416
417 DESIGN REFERENCES:
418
419 ALGORITHM:
420
421 NOTES:
422
423 OPEN ISSUES:
424
425 CLOSED ISSUES:
426
427 CHANGE HISTORY: Created 20-DEC-02 DAVIDNG
431
428 ===========================================================================*/
429 PROCEDURE get_min_shipment_num(p_po_line_id IN NUMBER,
430 x_min_shipment_num OUT NOCOPY NUMBER);
432
433 /*===========================================================================
434 PROCEDURE NAME: get_shipment_price
435
436 DESCRIPTION:
437
438 PARAMETERS: p_po_line_id IN NUMBER,
439 p_from_line_id IN NUMBER,
440 p_min_shipment_num IN NUMBER,
441 p_quantity IN NUMBER,
442 x_price OUT NOCOPY NUMBER
443
444 RETURN VALUE: None
445
446 DESIGN REFERENCES:
447
448 ALGORITHM:
449
450 NOTES:
451
452 OPEN ISSUES:
453
454 CLOSED ISSUES:
455
456 CHANGE HISTORY: Created 20-DEC-02 DAVIDNG
457 ===========================================================================*/
458
459 PROCEDURE get_shipment_price
460 ( p_po_line_id IN NUMBER,
461 p_from_line_id IN NUMBER,
462 p_min_shipment_num IN NUMBER,
463 p_quantity IN NUMBER,
464 x_price OUT NOCOPY NUMBER,
465 x_from_line_location_id OUT NOCOPY NUMBER); -- <SERVICES FPJ>
466
467 -- <FPJ Advanced Price START>
468 PROCEDURE get_shipment_price
469 ( p_po_line_id IN NUMBER,
470 p_from_line_id IN NUMBER,
471 p_min_shipment_num IN NUMBER,
472 p_quantity IN NUMBER,
473 p_contract_id IN NUMBER,
474 p_org_id IN NUMBER,
475 p_supplier_id IN NUMBER,
476 p_supplier_site_id IN NUMBER,
477 p_creation_date IN DATE,
478 p_order_header_id IN NUMBER,
479 p_order_line_id IN NUMBER,
480 p_line_type_id IN NUMBER,
481 p_item_revision IN VARCHAR2,
482 p_item_id IN NUMBER,
483 p_category_id IN NUMBER,
484 p_supplier_item_num IN VARCHAR2,
485 p_uom IN VARCHAR2,
486 p_currency_code IN VARCHAR2, -- Bug 3564863
487 p_in_price IN NUMBER,
488 x_base_unit_price OUT NOCOPY NUMBER,
489 x_price OUT NOCOPY NUMBER,
490 x_from_line_location_id OUT NOCOPY NUMBER); -- <SERVICES FPJ>
491 -- <FPJ Advanced Price END>
492
493 END PO_SOURCING2_SV;
[Home] [Help]