1 PACKAGE PO_SOURCING2_SV as
2 /* $Header: POXSCS2S.pls 120.1.12010000.2 2008/08/04 08:32:49 rramasam 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 x_price_break_id OUT NOCOPY NUMBER, -- SERVICES FPJ
110 x_price OUT NOCOPY NUMBER, -- SERVICES FPJ
111 x_return_status OUT NOCOPY VARCHAR2 );
112
113 /*===========================================================================
114 FUNCTION NAME: get_break_price
115
116 DESCRIPTION: Overloaded price break API
117
118 ===========================================================================*/
119 FUNCTION get_break_price(x_order_quantity IN NUMBER,
120 x_ship_to_org IN NUMBER,
121 x_ship_to_loc IN NUMBER,
122 x_po_line_id IN NUMBER,
123 x_cum_flag IN BOOLEAN,
124 p_need_by_date IN DATE, /* <TIMEPHASED FPI> */
125 x_line_location_id IN NUMBER DEFAULT NULL)
126 return NUMBER;
127
128
129 -- <FPJ Advanced Price START>
130 PROCEDURE get_break_price(p_api_version IN NUMBER,
131 p_order_quantity IN NUMBER,
132 p_ship_to_org IN NUMBER,
133 p_ship_to_loc IN NUMBER,
134 p_po_line_id IN NUMBER,
135 p_cum_flag IN BOOLEAN,
136 p_need_by_date IN DATE,
137 p_line_location_id IN NUMBER,
138 p_contract_id IN NUMBER,
139 p_org_id IN NUMBER,
140 p_supplier_id IN NUMBER,
141 p_supplier_site_id IN NUMBER,
142 p_creation_date IN DATE,
143 p_order_header_id IN NUMBER,
144 p_order_line_id IN NUMBER,
145 p_line_type_id IN NUMBER,
146 p_item_revision IN VARCHAR2,
147 p_item_id IN NUMBER,
148 p_category_id IN NUMBER,
149 p_supplier_item_num IN VARCHAR2,
150 p_uom IN VARCHAR2,
151 p_in_price IN NUMBER,
152 p_currency_code IN VARCHAR2, -- Bug 3564863
153 x_base_unit_price OUT NOCOPY NUMBER,
154 x_price_break_id OUT NOCOPY NUMBER,
155 x_price OUT NOCOPY NUMBER,
156 x_return_status OUT NOCOPY VARCHAR2,
157 p_req_line_price IN NUMBER DEFAULT NULL ); -- Bug 7154646
158 -- <FPJ Advanced Price END>
159 -- Bug# 4148430: Adding x_negotiated_by_preparer_flag.
160 PROCEDURE get_break_price(p_api_version IN NUMBER,
161 p_order_quantity IN NUMBER,
162 p_ship_to_org IN NUMBER,
163 p_ship_to_loc IN NUMBER,
164 p_po_line_id IN NUMBER,
165 p_cum_flag IN BOOLEAN,
166 p_need_by_date IN DATE,
167 p_line_location_id IN NUMBER,
168 p_contract_id IN NUMBER,
169 p_org_id IN NUMBER,
170 p_supplier_id IN NUMBER,
171 p_supplier_site_id IN NUMBER,
172 p_creation_date IN DATE,
173 p_order_header_id IN NUMBER,
174 p_order_line_id IN NUMBER,
175 p_line_type_id IN NUMBER,
176 p_item_revision IN VARCHAR2,
177 p_item_id IN NUMBER,
178 p_category_id IN NUMBER,
179 p_supplier_item_num IN VARCHAR2,
180 p_uom IN VARCHAR2,
181 p_in_price IN NUMBER,
182 p_currency_code IN VARCHAR2, -- Bug 3564863
183 x_base_unit_price OUT NOCOPY NUMBER,
184 x_price_break_id OUT NOCOPY NUMBER,
185 x_price OUT NOCOPY NUMBER,
186 -- Bug# 4148430
187 x_from_advanced_pricing OUT NOCOPY VARCHAR2,
188 x_return_status OUT NOCOPY VARCHAR2,
189 p_req_line_price IN NUMBER DEFAULT NULL ); -- Bug 7154646
190
191 /*===========================================================================
192 FUNCTION NAME: get_release_quantity
193
194 DESCRIPTION: Returns a) the quantity released to-date and b)
195 the appropriate price break org/location combination
196 that should be used to select the break price.
197
198
199 PARAMETERS:
200
201 RETURN TYPE: NUMBER
202
203 DESIGN REFERENCES:
204
205 ALGORITHM: Assume the following ship-to organization/location
206 combinations exist for a blanket line's price
207 breaks:
208
209 ORG LOC MAP TO QUANTIY VARIABLES
210 ----------- ------------------------
211 | A | X | --> release_quantity
212 -----------
213 | A | - | --> candidate_quantity
214 -----------
215 | - | - | --> all_rls_quantity
216 -----------
217 | A | E | --> subtract_quantity
218 -----------
219 | D | H | --> exclude_quantity
220 -----------
221
222 To find the quantity released for org A, loc X:
223
224 o Sum the quantity released against nonmatching
225 orgs (in this case D). Store the result in
226 variable exclude_quantity.
227
228 o Sum the quantity released against org A, and
229 locations != X (in this case A, E). Store
230 the result in variable subtract_quantity.
231
232 o Sum the quantity released against org A and a
233 NULL location. Store the result in variable
234 candidate_quantity. If we do not find an
235 exact org/loc match, this is the price break
236 org/loc combination we are interested in.
237
238 If a price break exists (with matching org and
239 null shipment), set x_match_type = 'ORG'.
240
241 o Sum the quantity released against org A, loc X.
242
243 If a price break exists (with matching org and
244 matching location), set x_match_type = 'ALL'
245 and exit the loop so we can return this qty.
246
247 o If there is a price break with a NULL org and
248 a NULL location, sum total quantity released
249 against the blanket line. Store this result
250 in variable all_rls_quantity. If x_match_type
251 is null, set x_match_type = 'NULL.' If it is
252 not null, then it will equal 'ORG' which is a
253 closer match than a fully null price break (it
254 will never equal 'ALL' for this test since we
255 exit as soon as we find the exact match).
256
257 RESULT HIERARCHY
258 ----------------
259 If an exact match is found (org A, loc X) return
260 this exact release quantity and x_match_type of
261 'ALL.'
262
263 If a half-match exists, return an x_match_type of
264 'ORG.' The release quantity in this case =
265 candidate_quantity - subtract_quantity. If the
266 result is negative, set to 0.
267
268 If a null org/loc price break exists, return an
269 x_match_type of 'NULL.' The release quantity in
270 this case = all_rls_quantity - candidate_quantity -
271 subtract_quantity - exclude_quantity. If the
272 result is negative, set to 0.
273
274 Elsif no matching price breaks are found,
275 the x_match_type is 'NONE' and the release
276 quantity = 0.
277
278
279 NOTES: This is different from release 10 functionality
280 (which was incorrect). The release 10 code for
281 AutoSource and Releases will need to be changed
282 for consistency. Verified w/ kmiller.
283
284 OPEN ISSUES:
285
286 CLOSED ISSUES:
287
288 CHANGE HISTORY: Created 01-NOV-95 LBROADBE
289 ===========================================================================*/
290 FUNCTION get_release_quantity(x_ship_to_org IN NUMBER,
291 x_ship_to_loc IN NUMBER,
292 x_po_line_id IN NUMBER,
293 x_match_type IN OUT NOCOPY VARCHAR2) return NUMBER;
294
295 /*===========================================================================
296 FUNCTION NAME: get_item_detail
297
298 DESCRIPTION:
299
300 PARAMETERS: X_item_id IN NUMBER,
301 X_org_id IN NUMBER,
302 X_planned_item_flag IN OUT VARCHAR2,
303 X_list_price IN OUT NUMBER,
304 X_primary_uom IN OUT VARCHAR2
305
306 RETURN VALUE: BOOLEAN
307
308 DESIGN REFERENCES:
309
310 ALGORITHM:
311
312 NOTES:
313
314 OPEN ISSUES:
315
316 CLOSED ISSUES:
317
318 CHANGE HISTORY: Created 15-AUG-95 LBROADBE
319 ===========================================================================*/
320 FUNCTION get_item_detail(X_item_id IN NUMBER,
321 X_org_id IN NUMBER,
322 X_planned_item_flag IN OUT NOCOPY VARCHAR2,
323 X_list_price IN OUT NOCOPY NUMBER,
324 X_primary_uom IN OUT NOCOPY VARCHAR2) return BOOLEAN;
325
326
327
328 PROCEDURE update_line_price
329 ( p_po_line_id IN NUMBER
330 , p_price IN NUMBER
331 , p_from_line_location_id IN NUMBER -- <SERVICES FPJ>
332 );
333
334 -- <FPJ Advanced Price START>
335 PROCEDURE update_line_price
336 ( p_po_line_id IN NUMBER
337 , p_price IN NUMBER
338 , p_base_unit_price IN NUMBER
339 , p_from_line_location_id IN NUMBER -- <SERVICES FPJ>
340 );
341 -- <FPJ Advanced Price END>
342
343
344 /*===========================================================================
345 PROCEDURE NAME: update_shipment_price
346
347 DESCRIPTION:
348
349 PARAMETERS: p_price IN NUMBER,
350 p_line_location_id IN NUMBER
351
352 RETURN VALUE: None
353
354 DESIGN REFERENCES:
355
356 ALGORITHM:
357
358 NOTES:
359
360 OPEN ISSUES:
361
362 CLOSED ISSUES:
363
364 CHANGE HISTORY: Created 20-DEC-02 DAVIDNG
365 ===========================================================================*/
366 PROCEDURE update_shipment_price(p_price IN NUMBER,
367 p_line_location_id IN NUMBER);
368
369
370 /*===========================================================================
371 PROCEDURE NAME: get_min_shipment_num
372
373 DESCRIPTION:
374
375 PARAMETERS: p_po_line_id IN NUMBER,
376 x_min_shipment_num OUT NOCOPY NUMBER
377
378 RETURN VALUE: None
379
380 DESIGN REFERENCES:
381
382 ALGORITHM:
383
384 NOTES:
385
386 OPEN ISSUES:
387
388 CLOSED ISSUES:
389
390 CHANGE HISTORY: Created 20-DEC-02 DAVIDNG
391 ===========================================================================*/
392 PROCEDURE get_min_shipment_num(p_po_line_id IN NUMBER,
393 x_min_shipment_num OUT NOCOPY NUMBER);
394
395
396 /*===========================================================================
397 PROCEDURE NAME: get_shipment_price
398
399 DESCRIPTION:
400
401 PARAMETERS: p_po_line_id IN NUMBER,
402 p_from_line_id IN NUMBER,
403 p_min_shipment_num IN NUMBER,
404 p_quantity IN NUMBER,
405 x_price OUT NOCOPY NUMBER
406
407 RETURN VALUE: None
408
409 DESIGN REFERENCES:
410
411 ALGORITHM:
412
413 NOTES:
414
415 OPEN ISSUES:
416
417 CLOSED ISSUES:
418
419 CHANGE HISTORY: Created 20-DEC-02 DAVIDNG
420 ===========================================================================*/
421
422 PROCEDURE get_shipment_price
423 ( p_po_line_id IN NUMBER,
424 p_from_line_id IN NUMBER,
425 p_min_shipment_num IN NUMBER,
426 p_quantity IN NUMBER,
427 x_price OUT NOCOPY NUMBER,
428 x_from_line_location_id OUT NOCOPY NUMBER); -- <SERVICES FPJ>
429
430 -- <FPJ Advanced Price START>
431 PROCEDURE get_shipment_price
432 ( p_po_line_id IN NUMBER,
433 p_from_line_id IN NUMBER,
434 p_min_shipment_num IN NUMBER,
435 p_quantity IN NUMBER,
436 p_contract_id IN NUMBER,
437 p_org_id IN NUMBER,
438 p_supplier_id IN NUMBER,
439 p_supplier_site_id IN NUMBER,
440 p_creation_date IN DATE,
441 p_order_header_id IN NUMBER,
442 p_order_line_id IN NUMBER,
443 p_line_type_id IN NUMBER,
444 p_item_revision IN VARCHAR2,
445 p_item_id IN NUMBER,
446 p_category_id IN NUMBER,
447 p_supplier_item_num IN VARCHAR2,
448 p_uom IN VARCHAR2,
449 p_currency_code IN VARCHAR2, -- Bug 3564863
450 p_in_price IN NUMBER,
451 x_base_unit_price OUT NOCOPY NUMBER,
452 x_price OUT NOCOPY NUMBER,
453 x_from_line_location_id OUT NOCOPY NUMBER); -- <SERVICES FPJ>
454 -- <FPJ Advanced Price END>
455
456 END PO_SOURCING2_SV;
[Home] [Help]