[Home] [Help]
PACKAGE BODY: APPS.AMS_PRICE_PVT
Source
1 PACKAGE BODY AMS_PRICE_PVT as
2 /* $Header: amsvprcb.pls 120.0 2005/05/31 22:35:26 appldev noship $ */
3
4 g_use_header_qual CONSTANT varchar2(1) := 'Y';
5
6 AMS_DEBUG_HIGH_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
7 AMS_DEBUG_LOW_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
8 AMS_DEBUG_MEDIUM_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
9
10 PROCEDURE GET_TIME(t OUT NOCOPY number)
11 IS
12 BEGIN
13 t := to_char(sysdate, 'ssss');
14 --select to_char(sysdate, 'sssss') into t from dual;
15 END GET_TIME;
16
17 Function set_oe_Header_rec (
18 p_party_id number := FND_API.G_MISS_NUM
19 ,p_cust_account_id number := FND_API.G_MISS_NUM
20 ,p_price_list_id number := FND_API.G_MISS_NUM
21 ) return oe_order_pub.header_rec_type
22 is
23 l_pricing_header_rec oe_order_pub.header_rec_type;
24 begin
25 -- l_pricing_header_rec.party_id := p_party_id;
26 l_pricing_header_rec.sold_to_org_id := p_cust_account_id;
27 l_pricing_header_rec.price_list_id:= p_price_list_id;
28 return l_pricing_header_rec;
29 end set_oe_header_rec;
30
31 Function set_aso_Header_rec (
32 p_party_id number := FND_API.G_MISS_NUM
33 ,p_cust_account_id number := FND_API.G_MISS_NUM
34 ,p_price_list_id number := FND_API.G_MISS_NUM
35 ) return aso_pricing_int.PRICING_HEADER_REC_TYPE
36 IS
37 l_pricing_header_rec aso_pricing_int.PRICING_HEADER_REC_TYPE;
38 BEGIN
39 l_pricing_header_rec.party_id := p_party_id;
40 l_pricing_header_rec.cust_account_id := p_cust_account_id;
41 l_pricing_header_rec.price_list_id:= p_price_list_id;
42 return l_pricing_header_rec;
43 END set_aso_header_rec;
44
45
46 Function set_oe_line_rec (
47 p_inventory_item_id number := FND_API.G_MISS_NUM
48 ,p_uom_code varchar2 := FND_API.G_MISS_CHAR
49 ,p_price_list_id number := FND_API.G_MISS_NUM
50 ,p_party_id number := FND_API.G_MISS_NUM
51 ,p_cust_account_id number := FND_API.G_MISS_NUM
52 ) return oe_order_pub.line_rec_type
53 is
54 l_pricing_line_rec oe_order_pub.line_rec_type;
55 begin
56 l_pricing_line_rec.inventory_item_id := p_inventory_item_id;
57 l_pricing_line_rec.order_quantity_uom:= p_uom_code;
58 l_pricing_line_rec.ordered_quantity:= 1;
59 l_pricing_line_rec.price_list_id := p_price_list_id;
60 -- l_pricing_line_rec.party_id := p_party_id;
61 -- l_pricing_line_rec.cust_account_id := p_cust_account_id;
62 return l_pricing_line_rec;
63 end set_oe_line_rec;
64
65
66 Function set_aso_line_rec (
67 p_inventory_item_id number := FND_API.G_MISS_NUM
68 ,p_uom_code varchar2 := FND_API.G_MISS_CHAR
69 ,p_price_list_id number := FND_API.G_MISS_NUM
70 ,p_party_id number := FND_API.G_MISS_NUM
71 ,p_cust_account_id number := FND_API.G_MISS_NUM
72 ) return aso_pricing_int.PRICING_line_REC_TYPE
73 is
74 l_pricing_line_rec aso_pricing_int.PRICING_line_REC_TYPE;
75 begin
76 l_pricing_line_rec.inventory_item_id := p_inventory_item_id;
77 l_pricing_line_rec.uom_code:= p_uom_code;
78 l_pricing_line_rec.quantity:= 1;
79 l_pricing_line_rec.price_list_id := p_price_list_id;
80 -- l_pricing_line_rec.party_id := p_party_id;
81 -- l_pricing_line_rec.cust_account_id := p_cust_account_id;
82 return l_pricing_line_rec;
83 end set_aso_line_rec;
84
85
86
87 PROCEDURE Copy_Attribs_To_Req(
88 p_line_index number,
89 p_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type,
90 p_qualifier_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type,
91 px_Req_line_attr_tbl in out nocopy QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
92 px_Req_qual_tbl in out nocopy QP_PREQ_GRP.QUAL_TBL_TYPE)
93 IS
94 l_attr_index number := nvl(px_Req_line_attr_tbl.last,0);
95 l_qual_index number := nvl(px_Req_qual_tbl.last,0);
96 BEGIN
97 for i in 1..p_pricing_contexts_Tbl.count loop
98 l_attr_index := l_attr_index +1;
99 AMS_UTILITY_PVT.debug_message(' Copy_attribs_to_req: pricing_context p_line_index = '
100 || to_char(p_line_index) ||
101 'l_attr_index = ' || to_char(l_attr_index) );
102
103 px_Req_line_attr_tbl(l_attr_index).VALIDATED_FLAG := 'N';
104 px_Req_line_attr_tbl(l_attr_index).line_index := p_line_index;
105 -- Product and Pricing Contexts go into pricing contexts...
106 px_Req_line_attr_tbl(l_attr_index).PRICING_CONTEXT :=
107 p_pricing_contexts_Tbl(i).context_name;
108 px_Req_line_attr_tbl(l_attr_index).PRICING_ATTRIBUTE :=
109 p_pricing_contexts_Tbl(i).Attribute_Name;
110 px_Req_line_attr_tbl(l_attr_index).PRICING_ATTR_VALUE_FROM :=
111 p_pricing_contexts_Tbl(i).attribute_value;
112 end loop;
113 -- Copy the qualifiers
114 for i in 1..p_qualifier_contexts_Tbl.count loop
115 l_qual_index := l_qual_index +1;
116 AMS_UTILITY_PVT.debug_message(' Copy_attribs_to_req: pricing_context = '
117 || to_char(p_line_index) ||
118 'l_qual_index = ' || to_char(l_qual_index) );
119
120 px_Req_qual_tbl(l_qual_index).VALIDATED_FLAG := 'Y';
121 px_Req_qual_tbl(l_qual_index).line_index := p_line_index;
122 px_Req_qual_tbl(l_qual_index).QUALIFIER_CONTEXT :=
123 p_qualifier_contexts_Tbl(i).context_name;
124 px_Req_qual_tbl(l_qual_index).QUALIFIER_ATTRIBUTE :=
125 p_qualifier_contexts_Tbl(i).Attribute_Name;
126 px_Req_qual_tbl(l_qual_index).QUALIFIER_ATTR_VALUE_FROM :=
127 p_qualifier_contexts_Tbl(i).attribute_value;
128 end loop;
129 end copy_attribs_to_Req;
130
131 Procedure getReqLineAttrAndQual(
132 p_inventory_item_id in number
133 ,p_uom_code in varchar2
134 ,p_price_list_id in number := FND_API.G_MISS_NUM
135 ,p_party_id in number := FND_API.G_MISS_NUM
136 ,p_cust_account_id in number := FND_API.G_MISS_NUM
137 ,p_line_index in number
138 ,p_request_type_code in varchar2
139 ,px_req_line_attr_tbl in out nocopy QP_PREQ_GRP.LINE_ATTR_TBL_TYPE
140 ,px_req_qual_tbl in out nocopy QP_PREQ_GRP.qual_TBL_TYPE
141 )
142 is
143 l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
144 l_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
145
146 -- l_Req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
147 -- l_Req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
148
149 begin
150
151
152 /*
153 oe_order_pub.G_LINE :=set_oe_line_rec(
154 p_inventory_item_id => p_inventory_item_id
155 ,p_uom_code => p_uom_code
156 ,p_price_list_id => p_price_list_id
157 ,p_party_id => p_party_id
158 ,p_cust_account_id => p_cust_account_id);
159 */
160
161 aso_pricing_int.G_LINE_rec :=set_aso_line_rec(
162 p_inventory_item_id => p_inventory_item_id
163 ,p_uom_code => p_uom_code
164 ,p_price_list_id => p_price_list_id
165 ,p_party_id => p_party_id
166 ,p_cust_account_id => p_cust_account_id);
167
168
169 QP_ATTR_MAPPING_PUB.Build_Contexts (
170 P_REQUEST_TYPE_CODE => p_request_type_code,
171 P_PRICING_TYPE => 'L',
172 X_PRICE_CONTEXTS_RESULT_TBL => l_pricing_contexts_tbl,
173 X_QUAL_CONTEXTS_RESULT_TBL => l_qual_contexts_tbl);
174
175 IF (AMS_DEBUG_HIGH_ON) THEN
176
177
178
179 AMS_UTILITY_PVT.debug_message('getReqLineAttrAndQual last_attr_in='
180 || to_char(nvl(px_req_line_attr_tbl.last,0)));
181
182 END IF;
183 IF (AMS_DEBUG_HIGH_ON) THEN
184
185 AMS_UTILITY_PVT.debug_message('getReqLineAttrAndQual last_qual_in='
186 || to_char(nvl(px_req_qual_tbl.last,0)));
187 END IF;
188
189 Copy_attribs_to_req(p_line_index => p_line_index,
190 p_pricing_contexts_tbl => l_pricing_contexts_tbl,
191 p_qualifier_contexts_tbl=> l_qual_contexts_tbl,
192 px_req_line_attr_tbl => px_req_line_attr_tbl,
193 px_req_qual_tbl => px_req_qual_tbl);
194
195 end getREQLineAttrAndQual;
196
197
198 Procedure getHeaderAttrAndQual(
199 p_party_id in number := FND_API.G_MISS_NUM
200 ,p_cust_account_id in number := FND_API.G_MISS_NUM
201 ,p_price_list_id in number := FND_API.G_MISS_NUM
202 ,p_request_type_code in varchar2
203 ,x_pricing_contexts_tbl out nocopy QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type
204 ,x_qual_contexts_tbl out nocopy QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type
205 )
206 is
207 -- l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
208 -- l_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
209
210
211 begin
212
213 /*
214 oe_order_pub.g_hdr :=set_oe_Header_rec(
215 p_party_id => p_party_id,
216 p_cust_account_id => p_cust_account_id,
217 p_price_list_id => p_price_list_id );
218
219 */
220 aso_pricing_int.g_header_rec := set_aso_Header_rec(
221 p_party_id => p_party_id,
222 p_cust_account_id => p_cust_account_id,
223 p_price_list_id => p_price_list_id );
224
225
226 QP_ATTR_MAPPING_PUB.Build_Contexts (
227 P_REQUEST_TYPE_CODE => p_request_type_code,
228 P_PRICING_TYPE => 'H',
229 X_PRICE_CONTEXTS_RESULT_TBL => x_pricing_contexts_tbl,
230 X_QUAL_CONTEXTS_RESULT_TBL => x_qual_contexts_tbl);
231
232 end getHeaderAttrAndQual;
233
234
235
236 --- wendy start only used by testing purpose
237
238 Procedure getReqHeaderAttrAndQual(
239 p_party_id in number := FND_API.G_MISS_NUM
240 ,p_cust_account_id in number := FND_API.G_MISS_NUM
241 ,p_price_list_id in number := FND_API.G_MISS_NUM
242 ,p_line_index in number
243 ,p_request_type_code in varchar2
244 ,px_req_line_attr_tbl in out nocopy QP_PREQ_GRP.LINE_ATTR_TBL_TYPE
245 ,px_req_qual_tbl in out nocopy QP_PREQ_GRP.qual_TBL_TYPE
246 )
247 is
248 l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
249 l_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
250 -- l_Req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
251 -- l_Req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
252
253 begin
254 getHeaderAttrAndQual(
255 p_party_id => p_party_id,
256 p_cust_account_id => p_cust_account_id,
257 p_price_list_id => p_price_list_id,
258 p_request_type_code => p_request_type_code,
259 x_pricing_contexts_tbl => l_pricing_contexts_tbl,
260 x_qual_contexts_tbl => l_qual_contexts_tbl);
261
262 Copy_attribs_to_req(p_line_index => p_line_index,
263 p_pricing_contexts_tbl => l_pricing_contexts_tbl,
264 p_qualifier_contexts_tbl=> l_qual_contexts_tbl,
265 px_req_line_attr_tbl => px_req_line_attr_tbl,
266 px_req_qual_tbl => px_req_qual_tbl);
267
268 end getReqHeaderAttrAndQual;
269
270
271 --- end
272
273
274
275
276 PROCEDURE Set_Control_Rec(
277 p_pricing_event in varchar2
278 ,x_control_rec OUT nocopy QP_PREQ_GRP.CONTROL_RECORD_TYPE
279 )
280 IS
281 BEGIN
282 -- setup control record
283 -- x_control_rec.pricing_event := 'LINE';
284 x_control_rec.pricing_event := p_pricing_event;
285 x_control_rec.calculate_flag := 'Y';
286 x_control_rec.simulation_flag := 'N';
287 END Set_Control_Rec;
288
289
290 PROCEDURE Set_Line_Rec(
291 p_line_id IN Number
292 ,p_line_index IN Number
293 ,p_uom_code IN VARCHAR2
294 ,p_request_type_code IN varchar2
295 ,px_line_rec IN OUT NOCOPY QP_PREQ_GRP.LINE_REC_TYPE
296 )
297 IS
298 BEGIN
299
300 IF (AMS_DEBUG_HIGH_ON) THEN
301
302
303
304 AMS_UTILITY_PVT.debug_message('Set_Line_Rec: uom_code=' || p_uom_code);
305
306 END IF;
307
308 px_line_rec.request_type_code := p_request_type_code;
309 px_line_rec.line_id :=p_line_id;
310 px_line_rec.line_Index :=p_line_index;
311 px_line_rec.line_type_code := 'LINE';
312 px_line_rec.pricing_effective_date := sysdate;
313 px_line_rec.line_quantity := 1;
314 px_line_rec.line_uom_code := p_uom_code;
315 px_line_rec.currency_code := 'USD';
316 px_line_rec.price_flag :='Y';
317 END Set_Line_Rec;
318
319 PROCEDURE getReqLine(
320 p_uom_code in varchar2
321 ,p_currency_code in varchar2
322 ,p_line_id in number
323 ,p_line_index in number
324 ,p_request_type_code in varchar2
325 ,px_line_tbl in out nocopy QP_PREQ_GRP.Line_TBL_TYPE)
326 IS
327 l_index number := nvl(px_line_tbl.last,0);
328 l_line_rec QP_PREQ_GRP.Line_REC_TYPE;
329
330 BEGIN
331
332 l_line_rec.request_type_code := p_request_type_code;
333 l_line_rec.line_id :=p_line_id;
334 l_line_rec.line_Index :=p_line_index;
335 l_line_rec.line_type_code := 'LINE';
336 l_line_rec.pricing_effective_date := sysdate;
337 l_line_rec.line_quantity := 1;
338 l_line_rec.line_uom_code := p_uom_code;
339 l_line_rec.currency_code := p_currency_code;
340 l_line_rec.price_flag :='Y';
341
342 -- set_line_rec(p_line_id, p_line_index, p_uom_code, p_request_type_code, l_line_rec);
343 l_index := l_index +1;
344 px_line_Tbl(l_index) := l_line_rec;
345
346 end getReqLine;
347
348
349
350 PROCEDURE Set_Line_Attr_Rec(
351 p_line_index IN Number
352 ,p_inventory_item_id IN VARCHAR2
353 ,px_line_attr_rec IN OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_REC_TYPE
354 )
355 IS
356 BEGIN
357 -- setup line_attr_rec
358 px_line_attr_rec.LINE_INDEX := p_line_index;
359 px_line_attr_rec.PRICING_CONTEXT :='ITEM';
360 px_line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE1';
361 px_line_attr_rec.PRICING_ATTR_VALUE_FROM :=p_inventory_item_id;
362 px_line_attr_rec.VALIDATED_FLAG :='N';
363 END Set_Line_Attr_Rec;
364
365
366 PROCEDURE GetReqLineAttr(
367 p_line_index IN Number
368 ,p_inventory_item_id IN VARCHAR2
369 ,px_line_attr_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE
370 )
371 is
372 l_index number := nvl(px_line_attr_tbl.last,0);
373 l_line_attr_rec QP_PREQ_GRP.LINE_ATTR_REC_TYPE;
374 begin
375 set_line_attr_rec(p_line_index, p_inventory_item_id, l_line_attr_rec);
376 l_index := l_index +1;
377 px_line_attr_tbl(l_index) := l_line_attr_rec;
378 end GetReqLineAttr;
379
380
381 PROCEDURE Set_Qual_Rec(
382 p_line_index IN Number
383 ,p_price_list_id IN VARCHAR2
384 ,px_qual_rec IN OUT NOCOPY QP_PREQ_GRP.QUAL_REC_TYPE
385 )
386 IS
387 BEGIN
388 IF (AMS_DEBUG_HIGH_ON) THEN
389
390 AMS_UTILITY_PVT.debug_message('Set_Qual_Rec price_list_id='
391 || p_price_list_id);
392 END IF;
393 px_qual_rec.LINE_INDEX := p_line_index;
394 px_qual_rec.QUALIFIER_CONTEXT :='MODLIST';
395 px_qual_rec.QUALIFIER_ATTRIBUTE :='QUALIFIER_ATTRIBUTE4';
396 px_qual_rec.QUALIFIER_ATTR_VALUE_FROM :=p_price_list_id;
397 px_qual_rec.QUALIFIER_ATTR_VALUE_TO :=p_price_list_id;
398 px_qual_rec.COMPARISON_OPERATOR_CODE := '=';
399 px_qual_rec.VALIDATED_FLAG :='Y';
400 END Set_Qual_Rec;
401
402 PROCEDURE GetReqQual(
403 p_line_index IN Number
404 ,p_price_list_id IN VARCHAR2
405 ,px_qual_tbl IN OUT NOCOPY QP_PREQ_GRP.QUAL_TBL_TYPE
406 )
407 is
408 l_index number := nvl(px_qual_tbl.last,0);
409 l_qual_rec QP_PREQ_GRP.QUAL_REC_TYPE;
410 begin
411 set_qual_rec(p_line_index, p_price_list_id, l_qual_rec);
412 l_index := l_index +1;
413 px_qual_tbl(l_index) := l_qual_rec;
414 end GetReqQual;
415
416
417 -- wendy start
418 PROCEDURE SetRequest(
419 p_inventory_item_id in number
420 ,p_uom_code in varchar2
421 ,p_currency_code in varchar2
422 ,p_price_list_id in number := FND_API.G_MISS_NUM
423 ,p_party_id in number := FND_API.G_MISS_NUM
424 ,p_cust_account_id in number := FND_API.G_MISS_NUM
425 ,p_line_id in number
426 ,p_line_index in number
427 ,p_request_type_code varchar2
431 ,px_req_line_attr_tbl in out nocopy QP_PREQ_GRP.LINE_ATTR_TBL_TYPE
428 ,p_pricing_contexts_tbl in QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type
429 ,p_qual_contexts_tbl in QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type
430 ,px_line_tbl in out nocopy QP_PREQ_GRP.Line_TBL_TYPE
432 ,px_req_qual_tbl in out nocopy QP_PREQ_GRP.qual_TBL_TYPE
433
434 )
435 IS
436 BEGIN
437 -- setup request line
438 GetReqLine(p_uom_code, p_currency_code,
439 p_line_id, p_line_index, p_request_type_code, px_line_tbl);
440 if (g_use_header_qual = 'Y') then
441 IF (AMS_DEBUG_HIGH_ON) THEN
442
443 AMS_UTILITY_PVT.debug_message('set_request: Using header Qualify');
444 END IF;
445 getReqLineAttrAndQual(
446 p_inventory_item_id => p_inventory_item_id,
447 p_uom_code => p_uom_code,
448 p_line_index => p_line_index,
449 p_request_type_code => p_request_type_code,
450 px_req_line_attr_tbl => px_req_line_attr_tbl,
451 px_req_qual_tbl => px_req_qual_tbl);
452 copy_attribs_to_req(
453 p_line_index => p_line_index,
454 p_pricing_contexts_tbl => p_pricing_contexts_tbl,
455 p_qualifier_contexts_tbl => p_qual_contexts_tbl,
456 px_req_line_attr_tbl => px_req_line_attr_tbl,
457 px_req_qual_tbl => px_req_qual_tbl);
458
459 else
460 IF (AMS_DEBUG_HIGH_ON) THEN
461
462 AMS_UTILITY_PVT.debug_message('set_request: Using Line Qualify');
463 END IF;
464
465 getReqLineAttrAndQual(
466 p_inventory_item_id => p_inventory_item_id,
467 p_uom_code => p_uom_code,
468 p_price_list_id => p_price_list_id,
469 p_party_id => p_party_id,
470 p_cust_account_id => p_cust_account_id,
471 p_line_index => p_line_index,
472 p_request_type_code => p_request_type_code,
473 px_req_line_attr_tbl => px_req_line_attr_tbl,
474 px_req_qual_tbl => px_req_qual_tbl);
475 end if;
476 END SetRequest;
477
478 -- wendy end
479
480 -- clears the values in global structures
481 procedure clear_Global_Structures IS
482 BEGIN
483 aso_pricing_int.G_LINE_rec := NULL;
484 aso_pricing_int.g_header_rec := NULL;
485 END clear_Global_Structures;
486
487
488 PROCEDURE GetPricesFromQP(
489 p_price_list_id IN NUMBER := FND_API.G_MISS_NUM
490 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
491 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
492 ,p_currency_code IN VARCHAR2
493 ,p_item_tbl IN JTF_NUMBER_TABLE
494 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
495 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE := null
496 ,p_childIndex_tbl IN JTF_NUMBER_TABLE := null
497 -- ,p_calculate_flag IN CHAR(1) :='Y'
498 ,p_request_type_code IN VARCHAR2
499 ,p_pricing_event IN VARCHAR2
500 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
501 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
502 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
503 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
504 ,x_parentIndex_tbl OUT nocopy JTF_NUMBER_TABLE
505 ,x_childIndex_tbl out nocopy JTF_NUMBER_TABLE
506 ,x_return_status out nocopy varchar2
507 ,x_return_status_text out nocopy varchar2
508 )
509 IS
510 l_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
511 l_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
512 l_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
513 l_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
514 l_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
515 l_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
516 l_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
517 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
518 x_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
519 x_line_qual QP_PREQ_GRP.QUAL_TBL_TYPE;
520 x_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
521 x_line_detail_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
522 x_line_detail_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
523 x_line_detail_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
524 x_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
525
526
527 l_qual_rec QP_PREQ_GRP.QUAL_REC_TYPE;
528 l_line_attr_rec QP_PREQ_GRP.LINE_ATTR_REC_TYPE;
529 l_line_rec QP_PREQ_GRP.LINE_REC_TYPE;
530 l_rltd_rec QP_PREQ_GRP.RELATED_LINES_REC_TYPE;
531
532 l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
533 l_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
534
535 I BINARY_INTEGER;
536 l_version VARCHAR2(240);
537
538 l_line_index NUMBER;
539 l_line_id number;
540
541
542 l_related_inventory_item_id NUMBER;
543 l_related_uom_code VARCHAR2(100);
544
545 l_related_line_index number;
546 l_t1 char(5);
547 l_t2 char(5);
548 BEGIN
549 -- setup control record
550 set_control_rec(p_pricing_event,l_control_rec);
551
552 -- get header qualify
553 if (g_use_header_qual = 'Y') then
554 getHeaderAttrAndQual(
555 p_party_id => p_party_id,
556 p_cust_account_id => p_cust_account_id,
557 p_price_list_id => p_price_list_id,
558 p_request_type_code => p_request_type_code,
562 end if;
559 x_pricing_contexts_tbl => l_pricing_contexts_tbl,
560 x_qual_contexts_tbl => l_qual_contexts_tbl);
561
563
564 for I in 1..p_item_tbl.count loop
565 l_line_index := I ;
566 SetRequest(
567 p_inventory_item_id => p_item_tbl(I)
568 ,p_uom_code => p_uom_tbl(I)
569 ,p_currency_code => p_currency_code
570 ,p_price_list_id => p_price_list_id
571 ,p_party_id => p_party_id
572 ,p_cust_account_id => p_cust_account_id
573 ,p_line_id => l_line_index
574 ,p_line_index => l_line_index
575 ,p_request_type_code => p_request_type_code
576 ,p_pricing_contexts_tbl => l_pricing_contexts_tbl
577 ,p_qual_contexts_tbl => l_qual_contexts_tbl
578 ,px_line_tbl => l_line_tbl
579 ,px_req_line_attr_tbl => l_line_attr_tbl
580 ,px_req_qual_tbl => l_qual_tbl);
581 END LOOP;
582 -- l_line_index := p_item_tbl.count;
583
584 -- only for service item support
585 IF (p_parentIndex_tbl is not null and p_childindex_tbl is not null) then
586 FOR I in 1..p_parentIndex_tbl.count Loop
587 l_rltd_rec.line_index := p_parentIndex_tbl(I);
588 IF (AMS_DEBUG_HIGH_ON) THEN
589
590 AMS_UTILITY_PVT.debug_message('getpricefromqp: Line index='||l_rltd_rec.line_index);
591 END IF;
592 l_rltd_rec.LINE_DETAIL_INDEX := 0;
593 l_rltd_rec.RELATED_LINE_INDEX :=p_childIndex_tbl(I);
594 IF (AMS_DEBUG_HIGH_ON) THEN
595
596 AMS_UTILITY_PVT.debug_message('getpricefromqp: Line index='||l_rltd_rec.related_line_index);
597 END IF;
598 l_rltd_rec.RELATIONSHIP_TYPE_CODE := QP_PREQ_GRP.G_SERVICE_LINE;
599 l_related_lines_tbl(I) := l_rltd_rec;
600 END LOOP;
601 END IF;
602
603
604 get_time(l_t1);
605 QP_PREQ_GRP.PRICE_REQUEST(l_line_tbl,
606 l_qual_tbl,
607 l_line_attr_tbl,
608 l_line_detail_tbl,
609 l_line_detail_qual_tbl,
610 l_line_detail_attr_tbl,
611 l_related_lines_tbl,
612 l_control_rec,
613 x_line_tbl,
614 x_line_qual,
615 x_line_attr_tbl,
616 x_line_detail_tbl,
617 x_line_detail_qual_tbl,
618 x_line_detail_attr_tbl,
619 x_related_lines_tbl,
620 x_return_status,
621 x_return_status_text);
622
623 get_time(l_t2);
624 IF (AMS_DEBUG_HIGH_ON) THEN
625
626 AMS_UTILITY_PVT.debug_message('Call duration of price_request Time(secs) ='|| to_char(l_t2-l_t1));
627 END IF;
628
629 -- clear values in global structures that were used
630 clear_Global_Structures;
631
632 x_listprice_tbl := JTF_NUMBER_TABLE();
633 x_bestprice_tbl := JTF_NUMBER_TABLE();
634 x_status_code_tbl := JTF_VARCHAR2_TABLE_100();
635 x_status_text_tbl := JTF_VARCHAR2_TABLE_300();
636
637 x_parentIndex_tbl := JTF_NUMBER_TABLE();
638 x_childIndex_tbl := JTF_NUMBER_TABLE();
639
640 x_listprice_tbl.extend(l_line_tbl.count);
641 x_bestprice_tbl.extend(l_line_tbl.count);
642 x_status_code_tbl.extend(l_line_tbl.count);
643 x_status_text_tbl.extend(l_line_tbl.count);
644
645 for I in 1..x_line_tbl.count Loop
646 x_listprice_tbl(I) := x_line_tbl(I).unit_price *
647 x_line_tbl(I).priced_quantity;
648 x_bestprice_tbl(I) := x_line_tbl(I).adjusted_unit_price *
649 x_line_tbl(I).priced_quantity;
650 x_status_code_tbl(I) := x_line_tbl(I).status_code;
651 x_status_text_tbl(I) := x_line_tbl(I).status_text;
652 END LOOP;
653
654 -- get related information
655 x_parentIndex_tbl.extend(x_related_lines_tbl.count);
656 x_childIndex_tbl.extend(x_related_lines_tbl.count);
657
658 FOR I IN 1..x_related_lines_tbl.COUNT LOOP
659 x_parentIndex_tbl(I) := x_related_lines_tbl(I).line_index;
660 x_childIndex_tbl(I) := x_related_lines_tbl(I).related_line_index;
661 END LOOP;
662
663 END GetPricesFromQP;
664
665 -- 2.a [using qp] get price of one item base on price_list_id
666 PROCEDURE GetPrice(
667 p_price_list_id IN NUMBER
668 ,p_currency_code IN VARCHAR2
669 ,p_inventory_item_id IN NUMBER
670 ,p_uom_code IN VARCHAR2
671 -- ,p_calculate_flag IN CHAR(1) :='Y'
672 ,p_request_type_code IN VARCHAR2
673 ,p_pricing_event IN VARCHAR2
674 ,x_listprice OUT NOCOPY NUMBER
675 ,x_bestprice OUT NOCOPY NUMBER
676 ,x_status_code OUT NOCOPY varchar2
677 ,x_status_text OUT NOCOPY varchar2
678 )
679 IS
680 l_item_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
681 l_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
682 l_listprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
683 l_bestprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
684 l_status_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
685 l_status_text_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
686
687 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
688 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
689
690 x_return_status VARCHAR2(1);
691 x_return_status_text VARCHAR2(240);
692
693 BEGIN
694 l_item_tbl.extend();
695 l_item_tbl(1) := p_inventory_item_id;
696 l_uom_tbl.extend();
700 p_currency_code => p_currency_code,
697 l_uom_tbl(1) := p_uom_code;
698 getpricesfromqp(
699 p_price_list_id => p_price_list_id,
701 p_item_tbl => l_item_tbl,
702 p_uom_tbl => l_uom_tbl,
703 p_request_type_code => p_request_type_code,
704 p_pricing_event => p_pricing_event,
705 x_listprice_tbl => l_listprice_tbl,
706 x_bestprice_tbl => l_bestprice_tbl,
707 x_status_code_tbl => l_status_code_tbl,
708 x_status_text_tbl => l_status_text_tbl,
709 x_parentIndex_tbl => l_parentIndex_tbl,
710 x_childIndex_tbl => l_childIndex_tbl,
711 x_return_status => x_return_status,
712 x_return_status_text => x_return_status_text
713 );
714 x_listprice := l_listprice_tbl(1);
715 x_bestprice := l_bestprice_tbl(1);
716 x_status_code := l_status_code_tbl(1);
717 x_status_text := l_status_text_tbl(1);
718 END GetPrice;
719
720
721 --2.b [using qp] get price of one item base on party_id and cust_account_id
722 PROCEDURE GetPrice(
723 p_party_id IN NUMBER
724 ,p_cust_account_id IN NUMBER
725 ,p_currency_code IN VARCHAR2
726 ,p_inventory_item_id IN NUMBER
727 ,p_uom_code IN VARCHAR2
728 -- ,p_calculate_flag IN CHAR(1) :='Y'
729 ,p_request_type_code IN VARCHAR2
730 ,p_pricing_event IN VARCHAR2
731 ,x_listprice OUT NOCOPY NUMBER
732 ,x_bestprice OUT NOCOPY NUMBER
733 ,x_status_code OUT NOCOPY varchar2
734 ,x_status_text OUT NOCOPY varchar2
735 )
736 IS
737 l_item_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
738 l_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
739 l_listprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
740 l_bestprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
741 l_status_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
742 l_status_text_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
743
744 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
745 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
746
747
748 x_return_status VARCHAR2(1);
749 x_return_status_text VARCHAR2(240);
750
751
752 BEGIN
753 l_item_tbl.extend();
754 l_item_tbl(1) := p_inventory_item_id;
755 l_uom_tbl.extend();
756 l_uom_tbl(1) := p_uom_code;
757 getpricesfromqp(
758 p_party_id => p_party_id,
759 p_cust_account_id => p_cust_account_id,
760 p_currency_code => p_currency_code,
761 p_item_tbl => l_item_tbl,
762 p_uom_tbl => l_uom_tbl,
763 p_request_type_code => p_request_type_code,
764 p_pricing_event => p_pricing_event,
765 x_listprice_tbl => l_listprice_tbl,
766 x_bestprice_tbl => l_bestprice_tbl,
767 x_status_code_tbl => l_status_code_tbl,
768 x_status_text_tbl => l_status_text_tbl,
769 x_parentIndex_tbl => l_parentIndex_tbl,
770 x_childIndex_tbl => l_childIndex_tbl,
771 x_return_status => x_return_status,
772 x_return_status_text => x_return_status_text
773 );
774 x_listprice := l_listprice_tbl(1);
775 x_bestprice := l_bestprice_tbl(1);
776 x_status_code := l_status_code_tbl(1);
777 x_status_text := l_status_text_tbl(1);
778 END GetPrice;
779
780 --2.b1 [using qp] get price of one item base on price_list_id, party_id,
781 -- and cust_account_id
782 PROCEDURE GetPrice(
783 p_price_list_id IN NUMBER
784 ,p_party_id IN NUMBER
785 ,p_cust_account_id IN NUMBER
786 ,p_currency_code IN VARCHAR2
787 ,p_inventory_item_id IN NUMBER
788 ,p_uom_code IN VARCHAR2
789 -- ,p_calculate_flag IN CHAR(1) :='Y'
790 ,p_request_type_code IN VARCHAR2
791 ,p_pricing_event IN VARCHAR2
792 ,x_listprice OUT NOCOPY NUMBER
793 ,x_bestprice OUT NOCOPY NUMBER
794 ,x_status_code OUT NOCOPY varchar2
795 ,x_status_text OUT NOCOPY varchar2
796 )
797 IS
798 l_item_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
799 l_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
800 l_listprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
801 l_bestprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
802 l_status_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
803 l_status_text_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
804
805 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
806 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
807
808
809 x_return_status VARCHAR2(1);
810 x_return_status_text VARCHAR2(240);
811
812
813 BEGIN
814 l_item_tbl.extend();
815 l_item_tbl(1) := p_inventory_item_id;
816 l_uom_tbl.extend();
817 l_uom_tbl(1) := p_uom_code;
818 IF (AMS_DEBUG_HIGH_ON) THEN
819
820 AMS_UTILITY_PVT.debug_message('price list: ' || p_price_list_id);
821 END IF;
822 IF (AMS_DEBUG_HIGH_ON) THEN
823
824 AMS_UTILITY_PVT.debug_message('party: ' || p_party_id);
825 END IF;
826 IF (AMS_DEBUG_HIGH_ON) THEN
827
828 AMS_UTILITY_PVT.debug_message('account: ' || p_cust_account_id);
829 END IF;
830
831 getpricesfromqp(
832 p_price_list_id => p_price_list_id,
833 p_party_id => p_party_id,
834 p_cust_account_id => p_cust_account_id,
838 p_request_type_code => p_request_type_code,
835 p_currency_code => p_currency_code,
836 p_item_tbl => l_item_tbl,
837 p_uom_tbl => l_uom_tbl,
839 p_pricing_event => p_pricing_event,
840 x_listprice_tbl => l_listprice_tbl,
841 x_bestprice_tbl => l_bestprice_tbl,
842 x_status_code_tbl => l_status_code_tbl,
843 x_status_text_tbl => l_status_text_tbl,
844 x_parentIndex_tbl => l_parentIndex_tbl,
845 x_childIndex_tbl => l_childIndex_tbl,
846 x_return_status => x_return_status,
847 x_return_status_text => x_return_status_text
848 );
849 x_listprice := l_listprice_tbl(1);
850 x_bestprice := l_bestprice_tbl(1);
851 x_status_code := l_status_code_tbl(1);
852 x_status_text := l_status_text_tbl(1);
853 END GetPrice;
854
855
856 -- 2.c [using qp] get price of one item base on price_list_id for service support
857 PROCEDURE GetPrice(
858 p_price_list_id IN NUMBER
859 ,p_currency_code IN VARCHAR2
860 ,p_inventory_item_id IN NUMBER
861 ,p_uom_code IN VARCHAR2
862 ,p_related_inventory_item_id IN NUMBER
863 ,p_related_uom_code IN VARCHAR2
864 -- ,p_calculate_flag IN CHAR(1) :='Y'
865 ,p_request_type_code IN VARCHAR2
866 ,p_pricing_event IN VARCHAR2
867 ,x_listprice OUT NOCOPY NUMBER
868 ,x_bestprice OUT NOCOPY NUMBER
869 ,x_status_code OUT NOCOPY varchar2
870 ,x_status_text OUT NOCOPY varchar2
871 ,x_related_listprice OUT NOCOPY NUMBER
872 ,x_related_bestprice OUT NOCOPY NUMBER
873 ,x_related_status_code OUT NOCOPY varchar2
874 ,x_related_status_text OUT NOCOPY varchar2
875 )
876 IS
877 l_item_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
878 l_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
879 l_listprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
880 l_bestprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
881 l_status_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
882 l_status_text_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
883
884 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
885 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
886
887 lx_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
888 lx_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
889
890 x_return_status VARCHAR2(1);
891 x_return_status_text VARCHAR2(240);
892
893 BEGIN
894 l_item_tbl.extend();
895 l_uom_tbl.extend();
896 l_item_tbl(1) := p_inventory_item_id;
897 l_uom_tbl(1) := p_uom_code;
898
899
900 l_item_tbl.extend();
901 l_uom_tbl.extend();
902 l_item_tbl(2) := p_related_inventory_item_id;
903 l_uom_tbl(2) := p_related_uom_code;
904
905 l_parentIndex_tbl.extend();
906 l_childIndex_tbl.extend();
907
908 l_parentIndex_tbl(1) := 1;
909 l_childIndex_tbl(1) := 2;
910
911 getpricesfromqp(
912 p_price_list_id => p_price_list_id,
913 p_currency_code => p_currency_code,
914 p_item_tbl => l_item_tbl,
915 p_uom_tbl => l_uom_tbl,
916 p_parentIndex_tbl => l_parentIndex_tbl,
917 p_childIndex_tbl => l_childIndex_tbl,
918 p_request_type_code => p_request_type_code,
919 p_pricing_event => p_pricing_event,
920 x_listprice_tbl => l_listprice_tbl,
921 x_bestprice_tbl => l_bestprice_tbl,
922 x_status_code_tbl => l_status_code_tbl,
923 x_status_text_tbl => l_status_text_tbl,
924 x_parentIndex_tbl => lx_parentIndex_tbl,
925 x_childIndex_tbl => lx_childIndex_tbl,
926 x_return_status => x_return_status,
927 x_return_status_text => x_return_status_text
928 );
929
930 x_listprice := l_listprice_tbl(1);
931 x_bestprice := l_bestprice_tbl(1);
932 x_status_code := l_status_code_tbl(1);
933 x_status_text := l_status_text_tbl(1);
934
935 x_related_listprice := l_listprice_tbl(2);
936 x_related_bestprice := l_bestprice_tbl(2);
937 x_related_status_code := l_status_code_tbl(2);
938 x_related_status_text := l_status_text_tbl(2);
939
940 END GetPrice;
941
942
943
944 -- 2.d [using qp] get price of one item base customer info for service support
945 PROCEDURE GetPrice(
946 p_party_id IN NUMBER
947 ,p_cust_account_id IN NUMBER
948 ,p_currency_code IN VARCHAR2
949 ,p_inventory_item_id IN NUMBER
950 ,p_uom_code IN VARCHAR2
951 ,p_related_inventory_item_id IN NUMBER
952 ,p_related_uom_code IN VARCHAR2
953 -- ,p_calculate_flag IN CHAR(1) :='Y'
954 ,p_request_type_code IN VARCHAR2
955 ,p_pricing_event IN VARCHAR2
956 ,x_listprice OUT NOCOPY NUMBER
957 ,x_bestprice OUT NOCOPY NUMBER
958 ,x_status_code OUT NOCOPY varchar2
959 ,x_status_text OUT NOCOPY varchar2
960 ,x_related_listprice OUT NOCOPY NUMBER
961 ,x_related_bestprice OUT NOCOPY NUMBER
962 ,x_related_status_code OUT NOCOPY varchar2
963 ,x_related_status_text OUT NOCOPY varchar2
964 )
965 IS
966 l_item_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
967 l_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
968 l_listprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
972
969 l_bestprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
970 l_status_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
971 l_status_text_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
973 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
974 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
975
976 lx_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
977 lx_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
978
979
980 x_return_status VARCHAR2(1);
981 x_return_status_text VARCHAR2(240);
982
983 BEGIN
984 l_item_tbl.extend();
985 l_uom_tbl.extend();
986 l_item_tbl(1) := p_inventory_item_id;
987 l_uom_tbl(1) := p_uom_code;
988
989 l_item_tbl.extend();
990 l_uom_tbl.extend();
991 l_item_tbl(2) := p_related_inventory_item_id;
992 l_uom_tbl(2) := p_related_uom_code;
993
994 l_parentIndex_tbl.extend();
995 l_childIndex_tbl.extend();
996
997 l_parentIndex_tbl(1) := 1;
998 l_childIndex_tbl(1) := 2;
999
1000
1001 getpricesfromqp(
1002 p_party_id => p_party_id,
1003 p_cust_account_id => p_cust_account_id,
1004 p_currency_code => p_currency_code,
1005 p_item_tbl => l_item_tbl,
1006 p_uom_tbl => l_uom_tbl,
1007 p_parentIndex_tbl => l_parentIndex_tbl,
1008 p_childIndex_tbl => l_childIndex_tbl,
1009 p_request_type_code => p_request_type_code,
1010 p_pricing_event => p_pricing_event,
1011 x_listprice_tbl => l_listprice_tbl,
1012 x_bestprice_tbl => l_bestprice_tbl,
1013 x_status_code_tbl => l_status_code_tbl,
1014 x_status_text_tbl => l_status_text_tbl,
1015 x_parentIndex_tbl => lx_parentIndex_tbl,
1016 x_childIndex_tbl => lx_childIndex_tbl,
1017 x_return_status => x_return_status,
1018 x_return_status_text => x_return_status_text
1019 );
1020 x_listprice := l_listprice_tbl(1);
1021 x_bestprice := l_bestprice_tbl(1);
1022 x_status_code := l_status_code_tbl(1);
1023 x_status_text := l_status_text_tbl(1);
1024
1025
1026 x_related_listprice := l_listprice_tbl(2);
1027 x_related_bestprice := l_bestprice_tbl(2);
1028 x_related_status_code := l_status_code_tbl(2);
1029 x_related_status_text := l_status_text_tbl(2);
1030
1031
1032 END GetPrice;
1033
1034
1035 -- 2.d1 [using qp] get price of one item based on price_list_id and
1036 -- customer info for service support
1037 PROCEDURE GetPrice(
1038 p_price_list_id IN NUMBER
1039 ,p_party_id IN NUMBER
1040 ,p_cust_account_id IN NUMBER
1041 ,p_currency_code IN VARCHAR2
1042 ,p_inventory_item_id IN NUMBER
1043 ,p_uom_code IN VARCHAR2
1044 ,p_related_inventory_item_id IN NUMBER
1045 ,p_related_uom_code IN VARCHAR2
1046 -- ,p_calculate_flag IN CHAR(1) :='Y'
1047 ,p_request_type_code IN VARCHAR2
1048 ,p_pricing_event IN VARCHAR2
1049 ,x_listprice OUT NOCOPY NUMBER
1050 ,x_bestprice OUT NOCOPY NUMBER
1051 ,x_status_code OUT NOCOPY varchar2
1052 ,x_status_text OUT NOCOPY varchar2
1053 ,x_related_listprice OUT NOCOPY NUMBER
1054 ,x_related_bestprice OUT NOCOPY NUMBER
1055 ,x_related_status_code OUT NOCOPY varchar2
1056 ,x_related_status_text OUT NOCOPY varchar2
1057 )
1058 IS
1059 l_item_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1060 l_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1061 l_listprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1062 l_bestprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1063 l_status_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1064 l_status_text_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
1065
1066 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1067 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1068
1069 lx_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1070 lx_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1071
1072
1073 x_return_status VARCHAR2(1);
1074 x_return_status_text VARCHAR2(240);
1075
1076 BEGIN
1077 l_item_tbl.extend();
1078 l_uom_tbl.extend();
1079 l_item_tbl(1) := p_inventory_item_id;
1080 l_uom_tbl(1) := p_uom_code;
1081
1082 l_item_tbl.extend();
1083 l_uom_tbl.extend();
1084 l_item_tbl(2) := p_related_inventory_item_id;
1085 l_uom_tbl(2) := p_related_uom_code;
1086
1087 l_parentIndex_tbl.extend();
1088 l_childIndex_tbl.extend();
1089
1090 l_parentIndex_tbl(1) := 1;
1091 l_childIndex_tbl(1) := 2;
1092
1093
1094 getpricesfromqp(
1095 p_price_list_id => p_price_list_id,
1096 p_party_id => p_party_id,
1097 p_cust_account_id => p_cust_account_id,
1098 p_currency_code => p_currency_code,
1099 p_item_tbl => l_item_tbl,
1100 p_uom_tbl => l_uom_tbl,
1101 p_parentIndex_tbl => l_parentIndex_tbl,
1102 p_childIndex_tbl => l_childIndex_tbl,
1103 p_request_type_code => p_request_type_code,
1104 p_pricing_event => p_pricing_event,
1105 x_listprice_tbl => l_listprice_tbl,
1106 x_bestprice_tbl => l_bestprice_tbl,
1107 x_status_code_tbl => l_status_code_tbl,
1108 x_status_text_tbl => l_status_text_tbl,
1109 x_parentIndex_tbl => lx_parentIndex_tbl,
1110 x_childIndex_tbl => lx_childIndex_tbl,
1114 x_listprice := l_listprice_tbl(1);
1111 x_return_status => x_return_status,
1112 x_return_status_text => x_return_status_text
1113 );
1115 x_bestprice := l_bestprice_tbl(1);
1116 x_status_code := l_status_code_tbl(1);
1117 x_status_text := l_status_text_tbl(1);
1118
1119
1120 x_related_listprice := l_listprice_tbl(2);
1121 x_related_bestprice := l_bestprice_tbl(2);
1122 x_related_status_code := l_status_code_tbl(2);
1123 x_related_status_text := l_status_text_tbl(2);
1124 END GetPrice;
1125
1126
1127 -- 2.e [using qp] get prices for a list of items based on price_list_id
1128 PROCEDURE GetPrices(
1129 p_price_list_id IN NUMBER
1130 ,p_currency_code IN VARCHAR2
1131 ,p_item_tbl IN JTF_NUMBER_TABLE
1132 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
1133 -- ,p_calculate_flag IN CHAR(1) :='Y'
1134 ,p_request_type_code IN VARCHAR2
1135 ,p_pricing_event IN VARCHAR2
1136 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
1137 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
1138 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
1139 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
1140 ,x_return_status out nocopy varchar2
1141 ,x_return_status_text out nocopy varchar2
1142
1143 )
1144 IS
1145 l_version VARCHAR2(240);
1146
1147 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1148 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1149
1150 BEGIN
1151 getpricesfromqp(
1152 p_price_list_id => p_price_list_id,
1153 p_currency_code => p_currency_code,
1154 p_item_tbl => p_item_tbl,
1155 p_uom_tbl => p_uom_tbl,
1156 p_request_type_code => p_request_type_code,
1157 p_pricing_event => p_pricing_event,
1158 x_listprice_tbl => x_listprice_tbl,
1159 x_bestprice_tbl => x_bestprice_tbl,
1160 x_status_code_tbl => x_status_code_tbl,
1161 x_status_text_tbl => x_status_text_tbl,
1162 x_parentIndex_tbl => l_parentIndex_tbl,
1163 x_childIndex_tbl => l_childIndex_tbl,
1164 x_return_status => x_return_status,
1165 x_return_status_text => x_return_status_text
1166 );
1167 END GetPrices;
1168
1169 -- 2.f [using qp] get prices of a list of items based on party_id and cust_accoutn_id
1170 PROCEDURE GetPrices(
1171 p_party_id IN NUMBER
1172 ,p_cust_account_id IN number
1173 ,p_currency_code IN VARCHAR2
1174 ,p_item_tbl IN JTF_NUMBER_TABLE
1175 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
1176 -- ,p_calculate_flag IN CHAR(1) :='Y'
1177 ,p_request_type_code IN VARCHAR2
1178 ,p_pricing_event IN VARCHAR2
1179 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
1180 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
1181 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
1182 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
1183 ,x_return_status out nocopy varchar2
1184 ,x_return_status_text out nocopy varchar2
1185
1186 )
1187 IS
1188 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1189 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1190
1191 BEGIN
1192 getpricesfromqp(
1193 p_party_id => p_party_id,
1194 p_cust_account_id => p_cust_account_id,
1195 p_currency_code => p_currency_code,
1196 p_item_tbl => p_item_tbl,
1197 p_uom_tbl => p_uom_tbl,
1198 p_request_type_code => p_request_type_code,
1199 p_pricing_event => p_pricing_event,
1200 x_listprice_tbl => x_listprice_tbl,
1201 x_bestprice_tbl => x_bestprice_tbl,
1202 x_status_code_tbl => x_status_code_tbl,
1203 x_status_text_tbl => x_status_text_tbl,
1204 x_parentIndex_tbl => l_parentIndex_tbl,
1205 x_childIndex_tbl => l_childIndex_tbl,
1206 x_return_status => x_return_status,
1207 x_return_status_text => x_return_status_text
1208
1209 );
1210 END GetPrices;
1211
1212 -- 2.f1 [using qp] get prices of a list of items based on price_list_id, party_id,
1213 -- and cust_account_id
1214 PROCEDURE GetPrices(
1215 p_price_list_id IN NUMBER
1216 ,p_party_id IN NUMBER
1217 ,p_cust_account_id IN number
1218 ,p_currency_code IN VARCHAR2
1219 ,p_item_tbl IN JTF_NUMBER_TABLE
1220 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
1221 -- ,p_calculate_flag IN CHAR(1) :='Y'
1222 ,p_request_type_code IN VARCHAR2
1223 ,p_pricing_event IN VARCHAR2
1224 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
1225 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
1226 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
1227 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
1228 ,x_return_status out nocopy varchar2
1229 ,x_return_status_text out nocopy varchar2
1230
1231 )
1232 IS
1233 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1234 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1235
1236 BEGIN
1237 getpricesfromqp(
1238 p_price_list_id => p_price_list_id,
1239 p_party_id => p_party_id,
1240 p_cust_account_id => p_cust_account_id,
1241 p_currency_code => p_currency_code,
1242 p_item_tbl => p_item_tbl,
1243 p_uom_tbl => p_uom_tbl,
1244 p_request_type_code => p_request_type_code,
1245 p_pricing_event => p_pricing_event,
1246 x_listprice_tbl => x_listprice_tbl,
1247 x_bestprice_tbl => x_bestprice_tbl,
1248 x_status_code_tbl => x_status_code_tbl,
1249 x_status_text_tbl => x_status_text_tbl,
1250 x_parentIndex_tbl => l_parentIndex_tbl,
1251 x_childIndex_tbl => l_childIndex_tbl,
1252 x_return_status => x_return_status,
1253 x_return_status_text => x_return_status_text
1254
1255 );
1256 END GetPrices;
1257
1258
1259 -- 2.g [using qp] get prices of a list of items based on price_list_id for service support
1260 PROCEDURE GetPrices(
1261 p_price_list_id IN NUMBER
1262 ,p_currency_code IN VARCHAR2
1263 ,p_item_tbl IN JTF_NUMBER_TABLE
1264 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
1265 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE
1266 ,p_childIndex_tbl IN JTF_NUMBER_TABLE
1267 -- ,p_calculate_flag IN CHAR(1) :='Y'
1268 ,p_request_type_code IN VARCHAR2
1269 ,p_pricing_event IN VARCHAR2
1270 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
1271 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
1272 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
1273 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
1274 ,x_parentIndex_tbl out nocopy JTF_NUMBER_TABLE
1275 ,x_childIndex_tbl out nocopy JTF_NUMBER_TABLE
1276 ,x_return_status out nocopy varchar2
1277 ,x_return_status_text out nocopy varchar2
1278 )
1279 IS
1280
1281 BEGIN
1282 getpricesfromqp(
1283 p_price_list_id => p_price_list_id,
1284 p_currency_code => p_currency_code,
1285 p_item_tbl => p_item_tbl,
1286 p_uom_tbl => p_uom_tbl,
1287 p_parentIndex_tbl => p_parentIndex_tbl,
1288 p_childIndex_tbl => p_childIndex_tbl,
1289 p_request_type_code => p_request_type_code,
1290 p_pricing_event => p_pricing_event,
1291 x_listprice_tbl => x_listprice_tbl,
1292 x_bestprice_tbl => x_bestprice_tbl,
1293 x_status_code_tbl => x_status_code_tbl,
1294 x_status_text_tbl => x_status_text_tbl,
1295 x_parentIndex_tbl => x_parentIndex_tbl,
1296 x_childIndex_tbl => x_childIndex_tbl,
1297 x_return_status => x_return_status,
1298 x_return_status_text => x_return_status_text
1299
1300 );
1301 END GetPrices;
1302
1303
1304
1305 -- 2.h [using qp] get prices of a list of items based on party_id and cust_accoutn_id
1306 PROCEDURE GetPrices(
1307 p_party_id IN NUMBER
1308 ,p_cust_account_id IN number
1309 ,p_currency_code IN VARCHAR2
1310 ,p_item_tbl IN JTF_NUMBER_TABLE
1311 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
1312 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE
1313 ,p_childIndex_tbl IN JTF_NUMBER_TABLE
1314 -- ,p_calculate_flag IN CHAR(1) :='Y'
1315 ,p_request_type_code IN VARCHAR2
1316 ,p_pricing_event IN VARCHAR2
1317 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
1318 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
1319 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
1320 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
1321 ,x_parentIndex_tbl OUT NOCOPY JTF_NUMBER_TABLE
1322 ,x_childIndex_tbl OUT NOCOPY JTF_NUMBER_TABLE
1323 ,x_return_status out nocopy varchar2
1324 ,x_return_status_text out nocopy varchar2
1325
1326 )
1327 IS
1328
1329 BEGIN
1330 getpricesfromqp(
1331 p_party_id => p_party_id,
1332 p_cust_account_id => p_cust_account_id,
1333 p_currency_code => p_currency_code,
1334 p_item_tbl => p_item_tbl,
1335 p_uom_tbl => p_uom_tbl,
1336 p_parentIndex_tbl => p_parentIndex_tbl,
1337 p_childIndex_tbl => p_childIndex_tbl,
1338 p_request_type_code => p_request_type_code,
1339 p_pricing_event => p_pricing_event,
1340 x_listprice_tbl => x_listprice_tbl,
1341 x_bestprice_tbl => x_bestprice_tbl,
1342 x_status_code_tbl => x_status_code_tbl,
1343 x_status_text_tbl => x_status_text_tbl,
1344 x_parentIndex_tbl => x_parentIndex_tbl,
1345 x_childIndex_tbl => x_childIndex_tbl,
1346 x_return_status => x_return_status,
1347 x_return_status_text => x_return_status_text
1348
1349 );
1350 END GetPrices;
1351
1352 -- 2.h1 [using qp] get prices of a list of items based on price_list_id,
1353 -- party_id and cust_account_id
1354 PROCEDURE GetPrices(
1355 p_price_list_id IN NUMBER
1356 ,p_party_id IN NUMBER
1357 ,p_cust_account_id IN number
1358 ,p_currency_code IN VARCHAR2
1359 ,p_item_tbl IN JTF_NUMBER_TABLE
1360 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
1361 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE
1362 ,p_childIndex_tbl IN JTF_NUMBER_TABLE
1363 -- ,p_calculate_flag IN CHAR(1) :='Y'
1364 ,p_request_type_code IN VARCHAR2
1365 ,p_pricing_event IN VARCHAR2
1366 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
1367 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
1368 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
1369 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
1370 ,x_parentIndex_tbl OUT NOCOPY JTF_NUMBER_TABLE
1371 ,x_childIndex_tbl OUT NOCOPY JTF_NUMBER_TABLE
1372 ,x_return_status out nocopy varchar2
1373 ,x_return_status_text out nocopy varchar2
1374
1375 )
1376 IS
1377
1378 BEGIN
1379 getpricesfromqp(
1380 p_price_list_id => p_price_list_id,
1381 p_party_id => p_party_id,
1382 p_cust_account_id => p_cust_account_id,
1383 p_currency_code => p_currency_code,
1384 p_item_tbl => p_item_tbl,
1385 p_uom_tbl => p_uom_tbl,
1386 p_parentIndex_tbl => p_parentIndex_tbl,
1387 p_childIndex_tbl => p_childIndex_tbl,
1388 p_request_type_code => p_request_type_code,
1389 p_pricing_event => p_pricing_event,
1390 x_listprice_tbl => x_listprice_tbl,
1391 x_bestprice_tbl => x_bestprice_tbl,
1392 x_status_code_tbl => x_status_code_tbl,
1393 x_status_text_tbl => x_status_text_tbl,
1394 x_parentIndex_tbl => x_parentIndex_tbl,
1395 x_childIndex_tbl => x_childIndex_tbl,
1396 x_return_status => x_return_status,
1397 x_return_status_text => x_return_status_text
1398
1399 );
1400 END GetPrices;
1401
1402
1403 END AMS_PRICE_PVT;