1 PACKAGE IBE_PRICE_PVT AUTHID CURRENT_USER as
2 /* $Header: IBEVPRCS.pls 115.34 2003/08/30 06:02:03 gzhang ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30) :=' IBE_PRICE_PVT';
6
7 type PRICE_REFCURSOR_TYPE is REF CURSOR;
8
9 -- test part--
10 Procedure getReqLineAttrAndQual(
11 p_inventory_item_id IN NUMBER
12 ,p_uom_code IN VARCHAR2
13 ,p_price_list_id IN NUMBER := FND_API.G_MISS_NUM
14 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
15 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
16
17 --gzhang 12/03/01 model bundle
18 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
19
20 ,p_line_index IN NUMBER
21 ,p_request_type_code IN VARCHAR2
22 ,px_req_line_attr_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE
23 ,px_req_qual_tbl IN OUT NOCOPY QP_PREQ_GRP.qual_TBL_TYPE
24 );
25
26 Procedure getReqHeaderAttrAndQual(
27 p_party_id IN NUMBER := FND_API.G_MISS_NUM
28 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
29 ,p_price_list_id IN NUMBER := FND_API.G_MISS_NUM
30
31 --gzhang 12/03/01 model bundle
32 --,p_model_id IN NUMBER := FND_API.G_MISS_NUM
33
34 ,p_line_index IN NUMBER
35 ,p_request_type_code IN VARCHAR2
36 ,px_req_line_attr_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE
37 ,px_req_qual_tbl IN OUT NOCOPY QP_PREQ_GRP.qual_TBL_TYPE
38 );
39
40 -- 2.a [using qp] get price of one item base on price_list_id
41 PROCEDURE GetPrice(
42 p_price_list_id IN NUMBER
43
44 --gzhang 12/03/01 model bundle
45 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
46 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
47 ,p_currency_code IN VARCHAR2
48 ,p_inventory_item_id IN NUMBER
49 ,p_uom_code IN VARCHAR2
50
51 --01/21/01 gzhang, model bundle cache, 02/12/02 bug fix#2222002
52 ,p_model_bundle_flag IN VARCHAR2 := NULL
53
54 ,p_request_type_code IN VARCHAR2
55 ,p_pricing_event IN VARCHAR2
56 ,x_listprice OUT NOCOPY NUMBER
57 ,x_bestprice OUT NOCOPY NUMBER
58 ,x_status_code OUT NOCOPY VARCHAR2
59 ,x_status_text OUT NOCOPY VARCHAR2
60 );
61
62 --2.b [using qp] get price of one item base on party_id and cust_account_id
63 PROCEDURE GetPrice(
64 p_party_id IN NUMBER
65 ,p_cust_account_id IN NUMBER
66
67 --gzhang 12/03/01 model bundle
68 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
69 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
70
71 ,p_currency_code IN VARCHAR2
72 ,p_inventory_item_id IN NUMBER
73 ,p_uom_code IN VARCHAR2
74 -- ,p_calculate_flag IN CHAR(1) :='Y'
75
76 --01/21/01 gzhang, model bundle cache, 02/12/02 bug fix#2222002
77 ,p_model_bundle_flag IN VARCHAR2 := NULL
78
79 ,p_request_type_code IN VARCHAR2
80 ,p_pricing_event IN VARCHAR2
81 ,x_listprice OUT NOCOPY NUMBER
82 ,x_bestprice OUT NOCOPY NUMBER
83 ,x_status_code OUT NOCOPY VARCHAR2
84 ,x_status_text OUT NOCOPY VARCHAR2
85 );
86
87 --2.b1 [using qp] get price of one item base on price_list_id, party_id and cust_account_id
88 PROCEDURE GetPrice(
89 p_price_list_id IN NUMBER
90 ,p_party_id IN NUMBER
91 ,p_cust_account_id IN NUMBER
92
93 --gzhang 12/03/01 model bundle
94 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
95 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
96
97 ,p_currency_code IN VARCHAR2
98 ,p_inventory_item_id IN NUMBER
99 ,p_uom_code IN VARCHAR2
100 -- ,p_calculate_flag IN CHAR(1) :='Y'
101
102 --01/21/01 gzhang, model bundle cache, 02/12/02 bug fix#2222002
103 ,p_model_bundle_flag IN VARCHAR2 := NULL
104
105 ,p_request_type_code IN VARCHAR2
106 ,p_pricing_event IN VARCHAR2
107 ,x_listprice OUT NOCOPY NUMBER
108 ,x_bestprice OUT NOCOPY NUMBER
109 ,x_status_code OUT NOCOPY VARCHAR2
110 ,x_status_text OUT NOCOPY VARCHAR2
111 );
112
113 -- 2.c [using qp] get price of one item base on price_list_id for service support
114 PROCEDURE GetPrice(
115 p_price_list_id IN NUMBER
116
117 --gzhang 12/03/01 model bundle
118 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
119 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
120
121 ,p_currency_code IN VARCHAR2
122 ,p_inventory_item_id IN NUMBER
123 ,p_uom_code IN VARCHAR2
124 ,p_related_inventory_item_id IN NUMBER
125 ,p_related_uom_code IN VARCHAR2
126 -- ,p_calculate_flag IN CHAR(1) :='Y'
127
128 --01/21/01 gzhang, model bundle cache, 02/12/02 bug fix#2222002
129 ,p_model_bundle_flag IN VARCHAR2 := NULL
130
131 ,p_request_type_code IN VARCHAR2
132 ,p_pricing_event IN VARCHAR2
133 ,x_listprice OUT NOCOPY NUMBER
134 ,x_bestprice OUT NOCOPY NUMBER
135 ,x_status_code OUT NOCOPY VARCHAR2
136 ,x_status_text OUT NOCOPY VARCHAR2
137 ,x_related_listprice OUT NOCOPY NUMBER
138 ,x_related_bestprice OUT NOCOPY NUMBER
139 ,x_related_status_code OUT NOCOPY VARCHAR2
140 ,x_related_status_text OUT NOCOPY VARCHAR2
141 );
142
143 -- 2.d [using qp] get price of one item base customer info for service support
144 PROCEDURE GetPrice(
145 p_party_id IN NUMBER
146 ,p_cust_account_id IN NUMBER
147
148 --gzhang 12/03/01 model bundle
149 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
150 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
151
152 ,p_currency_code IN VARCHAR2
153 ,p_inventory_item_id IN NUMBER
154 ,p_uom_code IN VARCHAR2
155 ,p_related_inventory_item_id IN NUMBER
156 ,p_related_uom_code IN VARCHAR2
157 -- ,p_calculate_flag IN CHAR(1) :='Y'
158
159 --01/21/01 gzhang, model bundle cache, 02/12/02 bug fix#2222002
160 ,p_model_bundle_flag IN VARCHAR2 := NULL
161
162 ,p_request_type_code IN VARCHAR2
163 ,p_pricing_event IN VARCHAR2
164 ,x_listprice OUT NOCOPY NUMBER
165 ,x_bestprice OUT NOCOPY NUMBER
166 ,x_status_code OUT NOCOPY VARCHAR2
167 ,x_status_text OUT NOCOPY VARCHAR2
168 ,x_related_listprice OUT NOCOPY NUMBER
169 ,x_related_bestprice OUT NOCOPY NUMBER
170 ,x_related_status_code OUT NOCOPY VARCHAR2
171 ,x_related_status_text OUT NOCOPY VARCHAR2
172 );
173
174 -- 2.d1 [using qp] get price of one item based on price list and customer info
175 -- for service support
176 PROCEDURE GetPrice(
177 p_price_list_id IN NUMBER
178 ,p_party_id IN NUMBER
179 ,p_cust_account_id IN NUMBER
180
181 --gzhang 12/03/01 model bundle
182 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
183 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
184
185 ,p_currency_code IN VARCHAR2
186 ,p_inventory_item_id IN NUMBER
187 ,p_uom_code IN VARCHAR2
188 ,p_related_inventory_item_id IN NUMBER
189 ,p_related_uom_code IN VARCHAR2
190 -- ,p_calculate_flag IN CHAR(1) :='Y'
191
192 --01/21/01 gzhang, model bundle cache, 02/12/02 bug fix#2222002
193 ,p_model_bundle_flag IN VARCHAR2 := NULL
194
195 ,p_request_type_code IN VARCHAR2
196 ,p_pricing_event IN VARCHAR2
197 ,x_listprice OUT NOCOPY NUMBER
198 ,x_bestprice OUT NOCOPY NUMBER
199 ,x_status_code OUT NOCOPY VARCHAR2
200 ,x_status_text OUT NOCOPY VARCHAR2
201 ,x_related_listprice OUT NOCOPY NUMBER
202 ,x_related_bestprice OUT NOCOPY NUMBER
203 ,x_related_status_code OUT NOCOPY VARCHAR2
204 ,x_related_status_text OUT NOCOPY VARCHAR2
205 );
206
207
208
209 -- 2.e [using qp] get prices for a list of items based on price_list_id
210 PROCEDURE GetPrices(
211 p_price_list_id IN NUMBER
212
213 --gzhang 12/03/01 model bundle
214 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
215 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
216
217 ,p_currency_code IN VARCHAR2
218 ,p_item_tbl IN JTF_NUMBER_TABLE
219 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
220 -- ,p_calculate_flag IN CHAR(1) :='Y'
221
222 --gzhang 01/21/01, model bundle cache
223 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
224
225 ,p_request_type_code IN VARCHAR2
226 ,p_pricing_event IN VARCHAR2
227 ,x_listprice_tbl OUT NOCOPY JTF_NUMBER_TABLE
228 ,x_bestprice_tbl OUT NOCOPY JTF_NUMBER_TABLE
229 ,x_status_code_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
230 ,x_status_text_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_300
231 ,x_return_status out NOCOPY VARCHAR2
232 ,x_return_status_text out NOCOPY VARCHAR2
233
234 );
235
236 -- 2.f [using qp] get prices of a list of items based on party_id and cust_accoutn_id
237 PROCEDURE GetPrices(
238 p_party_id IN NUMBER
239 ,p_cust_account_id IN NUMBER
240
241 --gzhang 12/03/01 model bundle
242 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
243 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
244
245 ,p_currency_code IN VARCHAR2
246 ,p_item_tbl IN JTF_NUMBER_TABLE
247 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
248 -- ,p_calculate_flag IN CHAR(1) :='Y'
249
250 --gzhang 01/21/01, model bundle cache
251 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
252
253 ,p_request_type_code IN VARCHAR2
254 ,p_pricing_event IN VARCHAR2
255 ,x_listprice_tbl OUT NOCOPY JTF_NUMBER_TABLE
256 ,x_bestprice_tbl OUT NOCOPY JTF_NUMBER_TABLE
257 ,x_status_code_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
258 ,x_status_text_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_300
259 ,x_return_status OUT NOCOPY VARCHAR2
260 ,x_return_status_text OUT NOCOPY VARCHAR2
261
262 );
263
264 -- 2.f1 [using qp] get prices of a list of items based on price_list_id, party_id,
265 -- and cust_account_id
266 PROCEDURE GetPrices(
267 p_price_list_id IN NUMBER
268 ,p_party_id IN NUMBER
269 ,p_cust_account_id IN NUMBER
270
271 --gzhang 12/03/01 model bundle
272 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
273 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
274
275 ,p_currency_code IN VARCHAR2
276 ,p_item_tbl IN JTF_NUMBER_TABLE
277 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
278 -- ,p_calculate_flag IN CHAR(1) :='Y'
279
280 --gzhang 01/21/01, model bundle cache
281 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
282
283 ,p_request_type_code IN VARCHAR2
284 ,p_pricing_event IN VARCHAR2
285 ,x_listprice_tbl OUT NOCOPY JTF_NUMBER_TABLE
286 ,x_bestprice_tbl OUT NOCOPY JTF_NUMBER_TABLE
287 ,x_status_code_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
288 ,x_status_text_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_300
289 ,x_return_status out NOCOPY VARCHAR2
290 ,x_return_status_text out NOCOPY VARCHAR2
291
292 );
293
294
295 -- 2.g [using qp] get prices of a list of items based on price_list_id for service support
296 PROCEDURE GetPrices(
297 p_price_list_id IN NUMBER
298
299 --gzhang 12/03/01 model bundle
300 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
301 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
302
303 ,p_currency_code IN VARCHAR2
304 ,p_item_tbl IN JTF_NUMBER_TABLE
305 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
306 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE
307 ,p_childIndex_tbl IN JTF_NUMBER_TABLE
308 -- ,p_calculate_flag IN CHAR(1) :='Y'
309
310 --gzhang 01/21/01, model bundle cache
311 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
312
313 ,p_request_type_code IN VARCHAR2
314 ,p_pricing_event IN VARCHAR2
315 ,x_listprice_tbl OUT NOCOPY JTF_NUMBER_TABLE
316 ,x_bestprice_tbl OUT NOCOPY JTF_NUMBER_TABLE
317 ,x_status_code_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
318 ,x_status_text_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_300
319 ,x_parentIndex_tbl out NOCOPY JTF_NUMBER_TABLE
320 ,x_childIndex_tbl out NOCOPY JTF_NUMBER_TABLE
321 ,x_return_status out NOCOPY VARCHAR2
322 ,x_return_status_text out NOCOPY VARCHAR2
323
324 );
325
326
327
328 -- 2.h [using qp] get prices of a list of items based on party_id and cust_accoutn_id
329 PROCEDURE GetPrices(
330 p_party_id IN NUMBER
331 ,p_cust_account_id IN number
332
333 --gzhang 12/03/01 model bundle
334 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
335 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
336
337 ,p_currency_code IN VARCHAR2
338 ,p_item_tbl IN JTF_NUMBER_TABLE
339 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
340 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE
341 ,p_childIndex_tbl IN JTF_NUMBER_TABLE
342 -- ,p_calculate_flag IN CHAR(1) :='Y'
343
344 --gzhang 01/21/01, model bundle cache
345 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
346
347 ,p_request_type_code IN VARCHAR2
348 ,p_pricing_event IN VARCHAR2
349 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
350 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
351 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
352 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
353 ,x_parentIndex_tbl out NOCOPY JTF_NUMBER_TABLE
354 ,x_childIndex_tbl out NOCOPY JTF_NUMBER_TABLE
355 ,x_return_status out nocopy varchar2
356 ,x_return_status_text out nocopy varchar2
357
358 );
359
360 -- 2.h1 [using qp] get prices of a list of items based on price_list_id,
361 -- party_id and cust_account_id
362 PROCEDURE GetPrices(
363 p_price_list_id IN NUMBER
364 ,p_party_id IN NUMBER
365 ,p_cust_account_id IN number
366
367 --gzhang 12/03/01 model bundle
368 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
369 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
370
371 ,p_currency_code IN VARCHAR2
372 ,p_item_tbl IN JTF_NUMBER_TABLE
373 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
374 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE
375 ,p_childIndex_tbl IN JTF_NUMBER_TABLE
376 -- ,p_calculate_flag IN CHAR(1) :='Y'
377
378 --gzhang 01/21/01, model bundle cache
379 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
380
381 ,p_request_type_code IN VARCHAR2
382 ,p_pricing_event IN VARCHAR2
383 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
384 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
385 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
386 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
387 ,x_parentIndex_tbl out NOCOPY JTF_NUMBER_TABLE
388 ,x_childIndex_tbl out NOCOPY JTF_NUMBER_TABLE
389 ,x_return_status out nocopy varchar2
390 ,x_return_status_text out nocopy varchar2
391
392 );
393
394 PROCEDURE PRICE_REQUEST(
395 p_price_list_id IN NUMBER := FND_API.G_MISS_NUM
396 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
397 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
398 ,p_currency_code IN VARCHAR2
399 ,p_minisite_id IN NUMBER := NULL
400 ,p_item_tbl IN QP_PREQ_GRP.NUMBER_TYPE
401 ,p_uom_code_tbl IN OUT NOCOPY QP_PREQ_GRP.VARCHAR_TYPE
402 ,p_model_id_tbl IN JTF_NUMBER_TABLE
403 ,p_line_quantity_tbl IN OUT NOCOPY QP_PREQ_GRP.NUMBER_TYPE
404 ,p_parentIndex_tbl IN QP_PREQ_GRP.NUMBER_TYPE
405 ,p_childIndex_tbl IN QP_PREQ_GRP.NUMBER_TYPE
406 ,p_request_type_code IN VARCHAR2 := 'ASO'
407 ,p_pricing_event IN VARCHAR2
408 ,x_price_csr OUT NOCOPY PRICE_REFCURSOR_TYPE
409 ,x_line_index_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
410 ,x_return_status OUT NOCOPY VARCHAR2
411 ,x_return_status_text OUT NOCOPY VARCHAR2
412 );
413
414 PROCEDURE PRICE_REQUEST(
415 p_price_list_id IN NUMBER := FND_API.G_MISS_NUM
416 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
417 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
418 ,p_currency_code IN VARCHAR2
419 ,p_minisite_id IN NUMBER := NULL
420 ,p_item_tbl IN JTF_NUMBER_TABLE
421 ,p_uom_code_tbl IN JTF_VARCHAR2_TABLE_100
422 ,p_model_id_tbl IN JTF_NUMBER_TABLE
423 ,p_line_quantity_tbl IN JTF_NUMBER_TABLE
424 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE := NULL
425 ,p_childIndex_tbl IN JTF_NUMBER_TABLE := NULL
426 ,p_request_type_code IN VARCHAR2 := 'ASO'
427 ,p_pricing_event IN VARCHAR2
428 ,x_price_csr OUT NOCOPY PRICE_REFCURSOR_TYPE
429 ,x_line_index_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
430 ,x_return_status OUT NOCOPY VARCHAR2
431 ,x_return_status_text OUT NOCOPY VARCHAR2
432 );
433
434 END IBE_PRICE_PVT;
[Home] [Help]