[Home] [Help]
PACKAGE BODY: APPS.IBE_PRICE_PVT
Source
1 PACKAGE BODY IBE_PRICE_PVT as
2 /* $Header: IBEVPRCB.pls 120.5 2006/07/17 07:03:01 apgupta ship $ */
3
4 g_use_header_qual CONSTANT varchar2(1) := 'Y';
5
6 PROCEDURE GET_TIME(t out NOCOPY number)
7 IS
8 BEGIN
9 t := to_char(sysdate, 'ssss');
10 t := t*10;
11 --select to_char(sysdate, 'sssss') into t from dual;
12 END GET_TIME;
13
14 Function set_oe_Header_rec (
15 p_party_id number := FND_API.G_MISS_NUM
16 ,p_cust_account_id number := FND_API.G_MISS_NUM
17 ,p_price_list_id number := FND_API.G_MISS_NUM
18 ) return oe_order_pub.header_rec_type
19 is
20 l_pricing_header_rec oe_order_pub.header_rec_type;
21 begin
22 -- l_pricing_header_rec.party_id := p_party_id;
23 l_pricing_header_rec.sold_to_org_id := p_cust_account_id;
24 l_pricing_header_rec.price_list_id:= p_price_list_id;
25 return l_pricing_header_rec;
26 end set_oe_header_rec;
27
28 Function set_aso_Header_rec (
29 p_party_id number := FND_API.G_MISS_NUM
30 ,p_cust_account_id number := FND_API.G_MISS_NUM
31 ,p_price_list_id number := FND_API.G_MISS_NUM
32 ) return aso_pricing_int.PRICING_HEADER_REC_TYPE
33 IS
34 l_pricing_header_rec aso_pricing_int.PRICING_HEADER_REC_TYPE;
35 BEGIN
36 l_pricing_header_rec.party_id := p_party_id;
37 l_pricing_header_rec.cust_account_id := p_cust_account_id;
38 l_pricing_header_rec.price_list_id:= p_price_list_id;
39
40 return l_pricing_header_rec;
41 END set_aso_header_rec;
42
43
44 Function set_oe_line_rec (
45 p_inventory_item_id number := FND_API.G_MISS_NUM
46 ,p_uom_code varchar2 := FND_API.G_MISS_CHAR
47 ,p_price_list_id number := FND_API.G_MISS_NUM
48 ,p_party_id number := FND_API.G_MISS_NUM
49 ,p_cust_account_id number := FND_API.G_MISS_NUM
50 ) return oe_order_pub.line_rec_type
51 is
52 l_pricing_line_rec oe_order_pub.line_rec_type;
53 begin
54 l_pricing_line_rec.inventory_item_id := p_inventory_item_id;
55 l_pricing_line_rec.order_quantity_uom:= p_uom_code;
56 l_pricing_line_rec.ordered_quantity:= 1;
57 l_pricing_line_rec.price_list_id := p_price_list_id;
58 -- l_pricing_line_rec.party_id := p_party_id;
59 -- l_pricing_line_rec.cust_account_id := p_cust_account_id;
60 return l_pricing_line_rec;
61 end set_oe_line_rec;
62
63
64 Function set_aso_line_rec (
65 p_inventory_item_id number := FND_API.G_MISS_NUM
66 ,p_uom_code varchar2 := FND_API.G_MISS_CHAR
67 ,p_price_list_id number := FND_API.G_MISS_NUM
68 ,p_party_id number := FND_API.G_MISS_NUM
69 ,p_cust_account_id number := FND_API.G_MISS_NUM
70
71 --gzhang 12/03/01 model bundle
72 ,p_model_id number --:= FND_API.G_MISS_NUM
73
74 ) return aso_pricing_int.PRICING_line_REC_TYPE
75 is
76 l_pricing_line_rec aso_pricing_int.PRICING_line_REC_TYPE;
77 begin
78 l_pricing_line_rec.inventory_item_id := p_inventory_item_id;
79 l_pricing_line_rec.uom_code:= p_uom_code;
80 l_pricing_line_rec.quantity:= 1;
81 l_pricing_line_rec.price_list_id := p_price_list_id;
82
83 --gzhang 12/03/01 model bundle
84 l_pricing_line_rec.model_id := p_model_id;
85
86 -- l_pricing_line_rec.party_id := p_party_id;
87 -- l_pricing_line_rec.cust_account_id := p_cust_account_id;
88 return l_pricing_line_rec;
89 end set_aso_line_rec;
90
91
92
93 PROCEDURE Copy_Attribs_To_Req(
94 p_line_index number,
95 p_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type,
96 p_qualifier_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type,
97 px_Req_line_attr_tbl in out nocopy QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
98 px_Req_qual_tbl in out nocopy QP_PREQ_GRP.QUAL_TBL_TYPE)
99 IS
100 l_attr_index number ;
101 l_qual_index number ;
102 BEGIN
103 l_attr_index := nvl(px_Req_line_attr_tbl.last,0);
104 l_qual_index := nvl(px_Req_qual_tbl.last,0);
105
106 for i in 1..p_pricing_contexts_Tbl.count loop
107 l_attr_index := l_attr_index +1;
108 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
109 ibe_util.debug(' Copy_attribs_to_req: pricing_context p_line_index = '
110 || to_char(p_line_index) ||
111 'l_attr_index = ' || to_char(l_attr_index) );
112 END IF;
113
114 px_Req_line_attr_tbl(l_attr_index).VALIDATED_FLAG := 'N';
115 px_Req_line_attr_tbl(l_attr_index).line_index := p_line_index;
116 -- Product and Pricing Contexts go into pricing contexts...
117 px_Req_line_attr_tbl(l_attr_index).PRICING_CONTEXT :=
118 p_pricing_contexts_Tbl(i).context_name;
119 px_Req_line_attr_tbl(l_attr_index).PRICING_ATTRIBUTE :=
120 p_pricing_contexts_Tbl(i).Attribute_Name;
121 px_Req_line_attr_tbl(l_attr_index).PRICING_ATTR_VALUE_FROM :=
122 p_pricing_contexts_Tbl(i).attribute_value;
123 end loop;
124 -- Copy the qualifiers
125 for i in 1..p_qualifier_contexts_Tbl.count loop
126 l_qual_index := l_qual_index +1;
127 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
128 ibe_util.debug(' Copy_attribs_to_req: pricing_context = '
129 || to_char(p_line_index) ||
130 'l_qual_index = ' || to_char(l_qual_index) );
131 END IF;
132
133 px_Req_qual_tbl(l_qual_index).VALIDATED_FLAG := 'Y';
134 px_Req_qual_tbl(l_qual_index).line_index := p_line_index;
135 px_Req_qual_tbl(l_qual_index).QUALIFIER_CONTEXT :=
136 p_qualifier_contexts_Tbl(i).context_name;
137 px_Req_qual_tbl(l_qual_index).QUALIFIER_ATTRIBUTE :=
138 p_qualifier_contexts_Tbl(i).Attribute_Name;
139 px_Req_qual_tbl(l_qual_index).QUALIFIER_ATTR_VALUE_FROM :=
140 p_qualifier_contexts_Tbl(i).attribute_value;
141 end loop;
142 end copy_attribs_to_Req;
143
144 Procedure getReqLineAttrAndQual(
145 p_inventory_item_id in number
146 ,p_uom_code in varchar2
147 ,p_price_list_id in number := FND_API.G_MISS_NUM
148 ,p_party_id in number := FND_API.G_MISS_NUM
149 ,p_cust_account_id in number := FND_API.G_MISS_NUM
150
151 --gzhang 12/03/01 model bundle
152 ,p_model_id in number --:= FND_API.G_MISS_NUM
153
154 ,p_line_index in number
155 ,p_request_type_code in varchar2
156 ,px_req_line_attr_tbl in out nocopy QP_PREQ_GRP.LINE_ATTR_TBL_TYPE
157 ,px_req_qual_tbl in out nocopy QP_PREQ_GRP.qual_TBL_TYPE
158 )
159 is
160 l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
161 l_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
162
163 -- l_Req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
164 -- l_Req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
165
166 begin
167
168 aso_pricing_int.G_LINE_rec :=set_aso_line_rec(
169 p_inventory_item_id => p_inventory_item_id
170 ,p_uom_code => p_uom_code
171 ,p_price_list_id => p_price_list_id
172 ,p_party_id => p_party_id
173 ,p_cust_account_id => p_cust_account_id
174
175 --gzhang 12/03/01 model bundle
176 ,p_model_id => p_model_id);
177
178
179 QP_ATTR_MAPPING_PUB.Build_Contexts (
180 P_REQUEST_TYPE_CODE => p_request_type_code,
181 P_PRICING_TYPE => 'L',
182 X_PRICE_CONTEXTS_RESULT_TBL => l_pricing_contexts_tbl,
183 X_QUAL_CONTEXTS_RESULT_TBL => l_qual_contexts_tbl);
184
185 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
186 ibe_util.debug('getReqLineAttrAndQual last_attr_in='
187 || to_char(nvl(px_req_line_attr_tbl.last,0)));
188 ibe_util.debug('getReqLineAttrAndQual last_qual_in='
189 || to_char(nvl(px_req_qual_tbl.last,0)));
190 END IF;
191
192 Copy_attribs_to_req(p_line_index => p_line_index,
193 p_pricing_contexts_tbl => l_pricing_contexts_tbl,
194 p_qualifier_contexts_tbl=> l_qual_contexts_tbl,
195 px_req_line_attr_tbl => px_req_line_attr_tbl,
196 px_req_qual_tbl => px_req_qual_tbl);
197
198 end getREQLineAttrAndQual;
199
200
201 Procedure getHeaderAttrAndQual(
202 p_party_id in number := FND_API.G_MISS_NUM
203 ,p_cust_account_id in number := FND_API.G_MISS_NUM
204 ,p_price_list_id in number := FND_API.G_MISS_NUM
205
206 --gzhang 12/03/01 model bundle
207 --,p_model_id in number := FND_API.G_MISS_NUM
208
209 ,p_request_type_code in varchar2
210 ,x_pricing_contexts_tbl out nocopy QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type
211 ,x_qual_contexts_tbl out nocopy QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type
212 )
213 is
214 -- l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
215 -- l_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
216
217
218 begin
219
220 /*
221 oe_order_pub.g_hdr :=set_oe_Header_rec(
222 p_party_id => p_party_id,
223 p_cust_account_id => p_cust_account_id,
224 p_price_list_id => p_price_list_id );
225
226 */
227 aso_pricing_int.g_header_rec := set_aso_Header_rec(
228 p_party_id => p_party_id,
229 p_cust_account_id => p_cust_account_id,
230 p_price_list_id => p_price_list_id);
231
232
233 QP_ATTR_MAPPING_PUB.Build_Contexts (
234 P_REQUEST_TYPE_CODE => p_request_type_code,
235 P_PRICING_TYPE => 'H',
236 X_PRICE_CONTEXTS_RESULT_TBL => x_pricing_contexts_tbl,
237 X_QUAL_CONTEXTS_RESULT_TBL => x_qual_contexts_tbl);
238
239 end getHeaderAttrAndQual;
240
241
242
243 --- wendy start only used by testing purpose
244
245 Procedure getReqHeaderAttrAndQual(
246 p_party_id in number := FND_API.G_MISS_NUM
247 ,p_cust_account_id in number := FND_API.G_MISS_NUM
248 ,p_price_list_id in number := FND_API.G_MISS_NUM
249
250 --gzhang 12/03/01 model bundle
251 --,p_model_id in number := FND_API.G_MISS_NUM
252
253 ,p_line_index in number
254 ,p_request_type_code in varchar2
255 ,px_req_line_attr_tbl in out nocopy QP_PREQ_GRP.LINE_ATTR_TBL_TYPE
256 ,px_req_qual_tbl in out nocopy QP_PREQ_GRP.qual_TBL_TYPE
257 )
258 is
259 l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
260 l_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
261 -- l_Req_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
262 -- l_Req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
263
264 begin
265 getHeaderAttrAndQual(
266 p_party_id => p_party_id,
267 p_cust_account_id => p_cust_account_id,
268 p_price_list_id => p_price_list_id,
269
270 --gzhang 12/03/01 model bundle
271 --p_model_id => p_model_id,
272
273 p_request_type_code => p_request_type_code,
274 x_pricing_contexts_tbl => l_pricing_contexts_tbl,
275 x_qual_contexts_tbl => l_qual_contexts_tbl);
276
277 Copy_attribs_to_req(p_line_index => p_line_index,
278 p_pricing_contexts_tbl => l_pricing_contexts_tbl,
279 p_qualifier_contexts_tbl=> l_qual_contexts_tbl,
280 px_req_line_attr_tbl => px_req_line_attr_tbl,
281 px_req_qual_tbl => px_req_qual_tbl);
282
283 end getReqHeaderAttrAndQual;
284
285
286 --- end
287
288
289
290
291 PROCEDURE Set_Control_Rec(
292 p_pricing_event in varchar2
293 ,x_control_rec OUT nocopy QP_PREQ_GRP.CONTROL_RECORD_TYPE
294 )
295 IS
296 BEGIN
297 -- setup control record
298 -- x_control_rec.pricing_event := 'LINE';
299 x_control_rec.pricing_event := p_pricing_event;
300 x_control_rec.calculate_flag := 'Y';
301 x_control_rec.simulation_flag := 'N';
302 END Set_Control_Rec;
303
304
305 PROCEDURE Set_Line_Rec(
306 p_line_id IN Number
307 ,p_line_index IN Number
308 ,p_uom_code IN VARCHAR2
309 ,p_request_type_code IN varchar2
310 ,px_line_rec IN OUT NOCOPY QP_PREQ_GRP.LINE_REC_TYPE
311 )
312 IS
313 BEGIN
314 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
315 ibe_util.debug('Set_Line_Rec: uom_code=' || p_uom_code);
316 END IF;
317
318 px_line_rec.request_type_code := p_request_type_code;
319 px_line_rec.line_id :=p_line_id;
320 px_line_rec.line_Index :=p_line_index;
321 px_line_rec.line_type_code := 'LINE';
322 px_line_rec.pricing_effective_date := sysdate;
323 px_line_rec.line_quantity := 1;
324 px_line_rec.line_uom_code := p_uom_code;
325 px_line_rec.currency_code := 'USD';
326 px_line_rec.price_flag :='Y';
327 END Set_Line_Rec;
328
329 PROCEDURE getReqLine(
330 p_uom_code in varchar2
331 ,p_currency_code in varchar2
332 ,p_line_id in number
333 ,p_line_index in number
334 ,p_request_type_code in varchar2
335 ,px_line_tbl in out nocopy QP_PREQ_GRP.Line_TBL_TYPE)
336 IS
337 l_index number;
338 l_line_rec QP_PREQ_GRP.Line_REC_TYPE;
339
340 BEGIN
341 l_index := nvl(px_line_tbl.last,0);
342
343 l_line_rec.request_type_code := p_request_type_code;
344 l_line_rec.line_id :=p_line_id;
345 l_line_rec.line_Index :=p_line_index;
346 l_line_rec.line_type_code := 'LINE';
347 l_line_rec.pricing_effective_date := sysdate;
348 l_line_rec.line_quantity := 1;
349 l_line_rec.line_uom_code := p_uom_code;
350 l_line_rec.currency_code := p_currency_code;
351 l_line_rec.price_flag :='Y';
352
353 -- set_line_rec(p_line_id, p_line_index, p_uom_code, p_request_type_code, l_line_rec);
354 l_index := l_index +1;
355 px_line_Tbl(l_index) := l_line_rec;
356
357 end getReqLine;
358
359
360
361 PROCEDURE Set_Line_Attr_Rec(
362 p_line_index IN Number
363 ,p_inventory_item_id IN VARCHAR2
364 ,px_line_attr_rec IN OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_REC_TYPE
365 )
366 IS
367 BEGIN
368 -- setup line_attr_rec
369 px_line_attr_rec.LINE_INDEX := p_line_index;
370 px_line_attr_rec.PRICING_CONTEXT :='ITEM';
371 px_line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE1';
372 px_line_attr_rec.PRICING_ATTR_VALUE_FROM :=p_inventory_item_id;
373 px_line_attr_rec.VALIDATED_FLAG :='N';
374 END Set_Line_Attr_Rec;
375
376
377 PROCEDURE GetReqLineAttr(
378 p_line_index IN Number
379 ,p_inventory_item_id IN VARCHAR2
380 ,px_line_attr_tbl IN OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE
381 )
382 is
383 l_index number;
387 set_line_attr_rec(p_line_index, p_inventory_item_id, l_line_attr_rec);
384 l_line_attr_rec QP_PREQ_GRP.LINE_ATTR_REC_TYPE;
385 begin
386 l_index := nvl(px_line_attr_tbl.last,0);
388 l_index := l_index +1;
389 px_line_attr_tbl(l_index) := l_line_attr_rec;
390 end GetReqLineAttr;
391
392
393 PROCEDURE Set_Qual_Rec(
394 p_line_index IN Number
395 ,p_price_list_id IN VARCHAR2
396 ,px_qual_rec IN OUT NOCOPY QP_PREQ_GRP.QUAL_REC_TYPE
397 )
398 IS
399 BEGIN
400 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
401 ibe_util.debug('Set_Qual_Rec price_list_id='
402 || p_price_list_id);
403 END IF;
404
405 px_qual_rec.LINE_INDEX := p_line_index;
406 px_qual_rec.QUALIFIER_CONTEXT :='MODLIST';
407 px_qual_rec.QUALIFIER_ATTRIBUTE :='QUALIFIER_ATTRIBUTE4';
408 px_qual_rec.QUALIFIER_ATTR_VALUE_FROM :=p_price_list_id;
409 px_qual_rec.QUALIFIER_ATTR_VALUE_TO :=p_price_list_id;
410 px_qual_rec.COMPARISON_OPERATOR_CODE := '=';
411 px_qual_rec.VALIDATED_FLAG :='Y';
412 END Set_Qual_Rec;
413
414 PROCEDURE GetReqQual(
415 p_line_index IN Number
416 ,p_price_list_id IN VARCHAR2
417 ,px_qual_tbl IN OUT NOCOPY QP_PREQ_GRP.QUAL_TBL_TYPE
418 )
419 is
420 l_index number;
421 l_qual_rec QP_PREQ_GRP.QUAL_REC_TYPE;
422 begin
423 l_index := nvl(px_qual_tbl.last,0);
424 set_qual_rec(p_line_index, p_price_list_id, l_qual_rec);
425 l_index := l_index +1;
426 px_qual_tbl(l_index) := l_qual_rec;
427 end GetReqQual;
428
429
430 -- wendy start
431 PROCEDURE SetRequest(
432 p_inventory_item_id in number
433 ,p_uom_code in varchar2
434 ,p_currency_code in varchar2
435 ,p_price_list_id in number := FND_API.G_MISS_NUM
436 ,p_party_id in number := FND_API.G_MISS_NUM
437 ,p_cust_account_id in number := FND_API.G_MISS_NUM
438
439 --gzhang 12/03/01 model bundle
440 ,p_model_id in number --:= FND_API.G_MISS_NUM
441
442 ,p_line_id in number
443 ,p_line_index in number
444 ,p_request_type_code varchar2
445 ,p_pricing_contexts_tbl in QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type
446 ,p_qual_contexts_tbl in QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type
447 ,px_line_tbl in out nocopy QP_PREQ_GRP.Line_TBL_TYPE
448 ,px_req_line_attr_tbl in out nocopy QP_PREQ_GRP.LINE_ATTR_TBL_TYPE
449 ,px_req_qual_tbl in out nocopy QP_PREQ_GRP.qual_TBL_TYPE
450
451 )
452 IS
453 BEGIN
454 -- setup request line
455 GetReqLine(p_uom_code, p_currency_code,
456 p_line_id, p_line_index, p_request_type_code, px_line_tbl);
457 if (g_use_header_qual = 'Y') then
458 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
459 ibe_util.debug('set_request: Using header Qualify');
460 END IF;
461
462 getReqLineAttrAndQual(
463 p_inventory_item_id => p_inventory_item_id,
464 p_uom_code => p_uom_code,
465
466 --gzhang 12/06/01 model bundle
467 p_model_id => p_model_id,
468
469 p_line_index => p_line_index,
470 p_request_type_code => p_request_type_code,
471 px_req_line_attr_tbl => px_req_line_attr_tbl,
472 px_req_qual_tbl => px_req_qual_tbl);
473 copy_attribs_to_req(
474 p_line_index => p_line_index,
475 p_pricing_contexts_tbl => p_pricing_contexts_tbl,
476 p_qualifier_contexts_tbl => p_qual_contexts_tbl,
477 px_req_line_attr_tbl => px_req_line_attr_tbl,
478 px_req_qual_tbl => px_req_qual_tbl);
479
480 else
481 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
482 ibe_util.debug('set_request: Using Line Qualify');
483 END IF;
484
485 getReqLineAttrAndQual(
486 p_inventory_item_id => p_inventory_item_id,
487 p_uom_code => p_uom_code,
488 p_price_list_id => p_price_list_id,
489 p_party_id => p_party_id,
490 p_cust_account_id => p_cust_account_id,
491
492 --gzhang 12/03/01 model bundle
493 p_model_id => p_model_id,
494
495 p_line_index => p_line_index,
496 p_request_type_code => p_request_type_code,
497 px_req_line_attr_tbl => px_req_line_attr_tbl,
498 px_req_qual_tbl => px_req_qual_tbl);
499 end if;
500 END SetRequest;
501
502 -- wendy end
503
504 -- clears the values in global structures
505 procedure clear_Global_Structures IS
506 BEGIN
507 aso_pricing_int.G_LINE_rec := NULL;
508 aso_pricing_int.g_header_rec := NULL;
509 oe_order_pub.g_hdr := NULL;
510 oe_order_pub.g_line := NULL;
511 END clear_Global_Structures;
512
513
514 PROCEDURE GetPricesFromQP(
515 p_price_list_id IN NUMBER := FND_API.G_MISS_NUM
516 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
517 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
518
519 --gzhang 12/03/01 model bundle support
520 ,p_model_id IN NUMBER --:= FND_API.G_MISS_NUM
521
522 ,p_currency_code IN VARCHAR2
523 ,p_item_tbl IN JTF_NUMBER_TABLE
524 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
525 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE := null
526 ,p_childIndex_tbl IN JTF_NUMBER_TABLE := null
527 -- ,p_calculate_flag IN CHAR(1) :='Y'
528 ,p_request_type_code IN VARCHAR2
529 ,p_pricing_event IN VARCHAR2
530 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
531 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
532 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
533 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
534 ,x_parentIndex_tbl OUT nocopy JTF_NUMBER_TABLE
538 )
535 ,x_childIndex_tbl out nocopy JTF_NUMBER_TABLE
536 ,x_return_status out nocopy varchar2
537 ,x_return_status_text out nocopy varchar2
539 IS
540 l_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
541 l_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
542 l_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
543 l_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
544 l_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
545 l_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
546 l_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
547 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
548 x_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
549 x_line_qual QP_PREQ_GRP.QUAL_TBL_TYPE;
550 x_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
551 x_line_detail_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
552 x_line_detail_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
553 x_line_detail_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
554 x_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
555
556
557 l_qual_rec QP_PREQ_GRP.QUAL_REC_TYPE;
558 l_line_attr_rec QP_PREQ_GRP.LINE_ATTR_REC_TYPE;
559 l_line_rec QP_PREQ_GRP.LINE_REC_TYPE;
560 l_rltd_rec QP_PREQ_GRP.RELATED_LINES_REC_TYPE;
561
562 l_pricing_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
563 l_qual_contexts_Tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
564
565 I BINARY_INTEGER;
566 l_version VARCHAR2(240);
567
568 l_line_index NUMBER;
569 l_line_id number;
570
571
572 l_related_inventory_item_id NUMBER;
573 l_related_uom_code VARCHAR2(100);
574
575 l_related_line_index number;
576 l_t0 char(5);
577 l_t1 char(5);
578 l_t2 char(5);
579 l_t3 char(5);
580 BEGIN
581
582 -- clear ASO and OE global pricing structures
583 get_time(l_t0);
584 clear_Global_Structures;
585
586 -- setup control record
587 set_control_rec(p_pricing_event,l_control_rec);
588
589 -- get header qualify
590 if (g_use_header_qual = 'Y') then
591 getHeaderAttrAndQual(
592 p_party_id => p_party_id,
593 p_cust_account_id => p_cust_account_id,
594 p_price_list_id => p_price_list_id,
595
596 --gzhang 12/03/01, model bundle
597 --p_model_id => p_model_id,
598
599 p_request_type_code => p_request_type_code,
600 x_pricing_contexts_tbl => l_pricing_contexts_tbl,
601 x_qual_contexts_tbl => l_qual_contexts_tbl);
602
603 end if;
604
605
606 for I in 1..p_item_tbl.count loop
607 l_line_index := I ;
608 SetRequest(
609 p_inventory_item_id => p_item_tbl(I)
610 ,p_uom_code => p_uom_tbl(I)
611 --gzhang 12/03/01, model bundle
612 ,p_model_id => p_model_id
613 ,p_currency_code => p_currency_code
614 ,p_price_list_id => p_price_list_id
615 ,p_party_id => p_party_id
616 ,p_cust_account_id => p_cust_account_id
617 ,p_line_id => l_line_index
618 ,p_line_index => l_line_index
619 ,p_request_type_code => p_request_type_code
620 ,p_pricing_contexts_tbl => l_pricing_contexts_tbl
621 ,p_qual_contexts_tbl => l_qual_contexts_tbl
622 ,px_line_tbl => l_line_tbl
623 ,px_req_line_attr_tbl => l_line_attr_tbl
624 ,px_req_qual_tbl => l_qual_tbl);
625 END LOOP;
626 -- l_line_index := p_item_tbl.count;
627
628 -- only for service item support
629 IF (p_parentIndex_tbl is not null and p_childindex_tbl is not null) then
630 FOR I in 1..p_parentIndex_tbl.count Loop
631 l_rltd_rec.line_index := p_parentIndex_tbl(I);
632
633 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
634 ibe_util.debug('getpricefromqp: Line index='||l_rltd_rec.line_index);
635 END IF;
636
637 l_rltd_rec.LINE_DETAIL_INDEX := 0;
638 l_rltd_rec.RELATED_LINE_INDEX :=p_childIndex_tbl(I);
639
640 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
641 ibe_util.debug('getpricefromqp: Line index='||l_rltd_rec.related_line_index);
642 END IF;
643
644 l_rltd_rec.RELATIONSHIP_TYPE_CODE := QP_PREQ_GRP.G_SERVICE_LINE;
645 l_related_lines_tbl(I) := l_rltd_rec;
646 END LOOP;
647 END IF;
648
649
650 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
651 ibe_util.debug('total lines ='|| l_line_tbl.count);
652 END IF;
653
654 get_time(l_t1);
655 QP_PREQ_GRP.PRICE_REQUEST(l_line_tbl,
656 l_qual_tbl,
657 l_line_attr_tbl,
658 l_line_detail_tbl,
659 l_line_detail_qual_tbl,
660 l_line_detail_attr_tbl,
661 l_related_lines_tbl,
662 l_control_rec,
663 x_line_tbl,
664 x_line_qual,
665 x_line_attr_tbl,
666 x_line_detail_tbl,
667 x_line_detail_qual_tbl,
668 x_line_detail_attr_tbl,
669 x_related_lines_tbl,
670 x_return_status,
671 x_return_status_text);
672
673 get_time(l_t2);
674 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
675 ibe_util.debug('Call duration of price_request Time(secs) ='|| to_char(l_t2-l_t1));
676 END IF;
677
678 -- clear values in global structures that were used
679 clear_Global_Structures;
680
684 x_status_text_tbl := JTF_VARCHAR2_TABLE_300();
681 x_listprice_tbl := JTF_NUMBER_TABLE();
682 x_bestprice_tbl := JTF_NUMBER_TABLE();
683 x_status_code_tbl := JTF_VARCHAR2_TABLE_100();
685
686 x_parentIndex_tbl := JTF_NUMBER_TABLE();
687 x_childIndex_tbl := JTF_NUMBER_TABLE();
688
689 x_listprice_tbl.extend(l_line_tbl.count);
690 x_bestprice_tbl.extend(l_line_tbl.count);
691 x_status_code_tbl.extend(l_line_tbl.count);
692 x_status_text_tbl.extend(l_line_tbl.count);
693
694 for I in 1..x_line_tbl.count Loop
695 x_listprice_tbl(I) := x_line_tbl(I).unit_price *
696 x_line_tbl(I).priced_quantity;
697 x_bestprice_tbl(I) := x_line_tbl(I).adjusted_unit_price *
698 x_line_tbl(I).priced_quantity;
699 x_status_code_tbl(I) := x_line_tbl(I).status_code;
700
701 if(lengthb(x_line_tbl(I).status_text)>300) then
702 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
703 ibe_util.debug('status_text has more than 300 bytes and has been truncated to 300 bytes:' || x_line_tbl(I).status_text);
704 END IF;
705
706 x_status_text_tbl(I) := substrb(x_line_tbl(I).status_text, 1, 300);
707 else
708 x_status_text_tbl(I) := x_line_tbl(I).status_text;
709 end if;
710
711 END LOOP;
712
713 -- get related information
714 x_parentIndex_tbl.extend(x_related_lines_tbl.count);
715 x_childIndex_tbl.extend(x_related_lines_tbl.count);
716
717 FOR I IN 1..x_related_lines_tbl.COUNT LOOP
718 x_parentIndex_tbl(I) := x_related_lines_tbl(I).line_index;
719 x_childIndex_tbl(I) := x_related_lines_tbl(I).related_line_index;
720 END LOOP;
721 get_time(l_t2);
722
723 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
724 ibe_util.debug('Duration of populating Line Items Time(ms) ='|| to_char(l_t1-l_t0));
725 ibe_util.debug('Duration of Price Request Time(ms) ='|| to_char(l_t2-l_t1));
726 ibe_util.debug('Duration of Price Request Time(ms) ='|| to_char(l_t3-l_t2));
727 END IF;
728
729 END GetPricesFromQP;
730
731
732 --gzhang 12/01/04 model bundle
733 -- new API
734 PROCEDURE CalculatePrices(
735 p_price_list_id IN NUMBER := FND_API.G_MISS_NUM
736 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
737 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
738 ,p_model_id IN NUMBER --:= FND_API.G_MISS_NUM
739
740 ,p_organization_id IN NUMBER
741 ,p_currency_code IN VARCHAR2
742 ,p_item_tbl IN JTF_NUMBER_TABLE
743 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
744 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE := null
745 ,p_childIndex_tbl IN JTF_NUMBER_TABLE := null
746
747 --gzhang 01/21/01, model bundle cache
748 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
749
750 ,p_request_type_code IN VARCHAR2
751 ,p_pricing_event IN VARCHAR2
752 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
753 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
754 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
755 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
756 ,x_parentIndex_tbl OUT nocopy JTF_NUMBER_TABLE
757 ,x_childIndex_tbl out nocopy JTF_NUMBER_TABLE
758 ,x_return_status out nocopy varchar2
759 ,x_return_status_text out nocopy varchar2
760 )
761 IS
762
763 L_API VARCHAR2(64);
764 l_itm_id NUMBER;
765
766 l_sub_itm_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
767 l_sub_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
768 l_sub_qty_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
769
770 l_sub_listprice_tbl JTF_NUMBER_TABLE;
771 l_sub_bestprice_tbl JTF_NUMBER_TABLE;
772
773 l_sub_status_code_tbl JTF_VARCHAR2_TABLE_100;
774 l_sub_status_text_tbl JTF_VARCHAR2_TABLE_300;
775
776 l_sub_parentIndex_tbl JTF_NUMBER_TABLE;
777 l_sub_childIndex_tbl JTF_NUMBER_TABLE;
778
779 l_sub_return_status varchar2(1);
780 l_sub_return_status_text varchar2(30);
781
782 --l_model_itm_id NUMBER;
783 l_index NUMBER;
784 l_model_bundle_flag VARCHAR2(1);
785
786 l_msg_data VARCHAR2(100);
787 l_msg_count NUMBER;
788 l_return_status VARCHAR2(30);
789 l_item_csr IBE_CCTBOM_PVT.IBE_CCTBOM_REF_CSR_TYPE;
790 l_bom_exp_rec IBE_CCTBOM_PVT.IBE_BOM_EXPLOSION_REC;
791
792 l_bom_item_type NUMBER;
793 l_primary_uom_code VARCHAR2(3);
794
795 cursor l_bom_item_type_csr(l_itmid NUMBER) IS
796 select MSIV.bom_item_type, MSIV.primary_uom_code
797 from mtl_system_items_vl MSIV
798 where MSIV.inventory_item_id = l_itmid AND MSIV.organization_id = p_organization_id;
799
800 l_resp_id NUMBER;
801 l_resp_appl_id NUMBER;
802 l_start_time NUMBER;
803 l_end_time NUMBER;
804
805 BEGIN
806 L_API := 'CalculatePrices';
807 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
808 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: total items='||p_item_tbl.count);
809 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Before calling QP:p_price_list_id='||p_price_list_id
810 ||',p_party_id='||p_party_id
811 ||',p_cust_account_id='||p_cust_account_id
812 ||',p_model_id='||p_model_id
816 l_start_time := DBMS_UTILITY.GET_TIME;
813 ||',p_currency_code='||p_currency_code);
814 END IF;
815
817 GetPricesFromQP(
818 p_price_list_id => p_price_list_id,
819 p_party_id => p_party_id,
820 p_cust_account_id => p_cust_account_id,
821 p_model_id => p_model_id,
822 p_currency_code => p_currency_code,
823 p_item_tbl => p_item_tbl,
824 p_uom_tbl => p_uom_tbl,
825 p_parentIndex_tbl => p_parentIndex_tbl,
826 p_childIndex_tbl => p_childIndex_tbl,
827 p_request_type_code => p_request_type_code,
828 p_pricing_event => p_pricing_event,
829 x_listprice_tbl => x_listprice_tbl,
830 x_bestprice_tbl => x_bestprice_tbl,
831 x_status_code_tbl => x_status_code_tbl,
832 x_status_text_tbl => x_status_text_tbl,
833 x_parentIndex_tbl => x_parentIndex_tbl,
834 x_childIndex_tbl => x_childIndex_tbl,
835 x_return_status => x_return_status,
836 x_return_status_text => x_return_status_text
837 );
838
839 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
840 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Returned from GetPricesFromQP total items='||p_item_tbl.count);
841 END IF;
842 --ZHGG_UTIL.Debug(g_pkg_name||'.CalculatePrices: Returned from GetPricesFromQP total items='||p_item_tbl.count);
843
844
845 l_resp_id := FND_PROFILE.value('RESP_ID');
846 l_resp_appl_id := FND_PROFILE.value('RESP_APPL_ID');
847
848 FOR I IN 1..p_item_tbl.count LOOP
849 l_itm_id := p_item_tbl(I);
850
851 OPEN l_bom_item_type_csr(l_itm_id);
852 FETCH l_bom_item_type_csr INTO l_bom_item_type, l_primary_uom_code;
853 CLOSE l_bom_item_type_csr;
854
855 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
856 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: item='||l_itm_id||', bom_item_type = '|| l_bom_item_type
857 ||',uom='||p_uom_tbl(I)
858 ||',primary_uom_code='||l_primary_uom_code
859 ||',listPrice='||x_listprice_tbl(I)
860 ||',bestPrice='||x_bestprice_tbl(I));
861 END IF;
862
863 --gzhang 01/21/01, model bundle cache
864 IF p_model_bundle_flag_tbl IS NULL OR p_model_bundle_flag_tbl(I) IS NULL THEN
865 l_model_bundle_flag := IBE_CCTBOM_PVT.Is_Model_Bundle(p_api_version =>1.0, p_model_id =>l_itm_id, p_organization_id => p_organization_id);
866 ELSE
867 l_model_bundle_flag := p_model_bundle_flag_tbl(I);
868 END IF;
869
870 IF l_model_bundle_flag = FND_API.G_TRUE THEN
871 IF l_primary_uom_code = p_uom_tbl(I) THEN --Model Bundle only support pricing for primary uom code
872
873 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
874 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Item '||l_itm_id||' is a model bundle.');
875 END IF;
876
877 IBE_CCTBOM_PVT.Load_Components(p_api_version =>1.0,
878 x_return_status=>l_return_status,
879 x_msg_data=>l_msg_data,
880 x_msg_count =>l_msg_count,
881 p_model_id =>l_itm_id,
882 p_organization_id =>p_organization_id,
883 x_item_csr =>l_item_csr);
884
885 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
886 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: returned from IBE_CCTBOM_PVT');
887 END IF;
888
889 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN --gzhang 05/24/2002, bug#2279562
890 l_index := 1;
891 FETCH l_item_csr INTO l_bom_exp_rec;
892 WHILE l_item_csr%FOUND LOOP
893 l_sub_itm_tbl.EXTEND;
894 l_sub_uom_tbl.EXTEND;
895 l_sub_qty_tbl.EXTEND;
896
897 l_sub_itm_tbl(l_index) := l_bom_exp_rec.component_item_id;
898
899 l_sub_uom_tbl(l_index) := l_bom_exp_rec.primary_uom_code;
900 l_sub_qty_tbl(l_index) := l_bom_exp_rec.component_quantity;
901
902 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
903 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Component '||l_index||' of Model Item '||l_itm_id||': item_id='||l_sub_itm_tbl(l_index)||',uom='||l_sub_uom_tbl(l_index)||',qty='||l_sub_qty_tbl(l_index));
904 END IF;
905
906 l_index := l_index + 1;
907 FETCH l_item_csr INTO l_bom_exp_rec;
908 END LOOP;
909 CLOSE l_item_csr;
910
911 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
912 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Currency Code for Model Item '||l_itm_id||':'||p_currency_code);
913 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Price List for Model Item '||l_itm_id||':'||p_price_list_id);
914 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Organization ID for Model Item '||l_itm_id||':'||p_organization_id);
915 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Request Type for Model Item '||l_itm_id||':'||p_request_type_code);
916 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Pricing Event for Model Item '||l_itm_id||':'||p_pricing_event);
917 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Before QP total items='||l_sub_itm_tbl.count);
918 END IF;
919 --ZHGG_UTIL.Debug(g_pkg_name||'.CalculatePrices: total component items='||l_sub_itm_tbl.count);
920 GetPricesFromQP(
921 p_price_list_id => p_price_list_id,
922 p_model_id => l_itm_id,
926 p_uom_tbl => l_sub_uom_tbl,
923 --p_organization_id => p_organization_id,
924 p_currency_code => p_currency_code,
925 p_item_tbl => l_sub_itm_tbl,
927 --p_parentIndex_tbl => l_non_model_bundle_parentIndex_tbl,
928 --p_childIndex_tbl => l_non_model_childIndex_tbl,
929 p_request_type_code => p_request_type_code,
930 p_pricing_event => p_pricing_event,
931 x_listprice_tbl => l_sub_listprice_tbl,
932 x_bestprice_tbl => l_sub_bestprice_tbl,
933 x_status_code_tbl => l_sub_status_code_tbl,
934 x_status_text_tbl => l_sub_status_text_tbl,
935 x_parentIndex_tbl => l_sub_parentIndex_tbl,
936 x_childIndex_tbl => l_sub_childIndex_tbl,
937 x_return_status => l_sub_return_status,
938 x_return_status_text => l_sub_return_status_text
939 );
940
941 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
942 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: After QP total items='||l_sub_itm_tbl.count);
943 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Return Status for Model Item '||l_itm_id||':'||l_sub_return_status);
944 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Return Status Text for Model Item '||l_itm_id||':'||l_sub_return_status_text);
945 END IF;
946
947 IF l_sub_return_status = FND_API.G_RET_STS_SUCCESS THEN
948 FOR J IN 1..l_sub_itm_tbl.count LOOP
949 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
950 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Subprice for Model Item '||l_itm_id||'-'||l_sub_itm_tbl(J)||'-'||l_sub_uom_tbl(J)||':list='||l_sub_listprice_tbl(J)||',best='||l_sub_bestprice_tbl(J));
951 END IF;
952 x_listprice_tbl(I) := x_listprice_tbl(I) + l_sub_listprice_tbl(J)*l_sub_qty_tbl(J);
953 x_bestprice_tbl(I) := x_bestprice_tbl(I) + l_sub_bestprice_tbl(J)*l_sub_qty_tbl(J);
954 END LOOP;
955 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
956 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Total Price of Model Item '||l_itm_id||': list='||x_listprice_tbl(I)||',best='||x_bestprice_tbl(I));
957 END IF;
958 ELSE -- Exception in component item pricing
959 x_listprice_tbl(I) := NULL;
960 x_bestprice_tbl(I) := NULL;
961 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
962 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: Exception in component item pricing. set price to null');
963 END IF;
964 END IF;
965
966 l_sub_itm_tbl.DELETE;
967 l_sub_uom_tbl.DELETE;
968 l_sub_qty_tbl.DELETE;
969 ELSE --gzhang 05/24/2002, bug#2279562, BOM Exception
970 x_listprice_tbl(I) := NULL;
971 x_bestprice_tbl(I) := NULL;
972 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
973 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: BOM Explode Exception. set price to null');
974 END IF;
975 END IF;
976 ELSE -- Not a primary UOM code
977 x_listprice_tbl(I) := NULL;
978 x_bestprice_tbl(I) := NULL;
979 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
980 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: uom ='||p_uom_tbl(I)||', not primary uom code, set price null.');
981 END IF;
982 END IF;
983 /*gzhang 01/30/2003 bug fix#2690511
984 ELSIF l_bom_item_type = 1 AND CZ_CF_API.UI_FOR_ITEM(l_itm_id, p_organization_id, SYSDATE, 'DHTML', FND_API.G_MISS_NUM, l_resp_id, l_resp_appl_id) IS NULL THEN
985 -- invalid model bundle
986 x_listprice_tbl(I) := NULL;
987 x_bestprice_tbl(I) := NULL;
988 IBE_UTIL.Debug(g_pkg_name||'.CalculatePrices: invalid model bundle (item='||l_itm_id||', set price null.');*/
989 END IF;
990 END LOOP;
991 --gzhang 08/08/2002, bug#2488246
992 --IBE_UTIL.Disable_Debug;
993 l_end_time := DBMS_UTILITY.GET_TIME;
994 --ZHGG_UTIL.debug(G_PKG_NAME||'.'||L_API||': end, elapsed time (s) ='||(l_end_time-l_start_time)/100);
995 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
996 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': end, elapsed time (s) ='||(l_end_time-l_start_time)/100);
997 END IF;
998
999 END CalculatePrices;
1000
1001 -- 2.a [using qp] get price of one item base on price_list_id
1002 PROCEDURE GetPrice(
1003 p_price_list_id IN NUMBER
1004
1005 --gzhang 12/03/01 model bundle
1006 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
1007 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1008
1009 ,p_currency_code IN VARCHAR2
1010 ,p_inventory_item_id IN NUMBER
1011 ,p_uom_code IN VARCHAR2
1012 -- ,p_calculate_flag IN CHAR(1) :='Y'
1013
1014 --01/21/01 gzhang, model bundle cache
1015 ,p_model_bundle_flag IN VARCHAR2 := NULL
1016
1017 ,p_request_type_code IN VARCHAR2
1018 ,p_pricing_event IN VARCHAR2
1019 ,x_listprice OUT NOCOPY NUMBER
1020 ,x_bestprice OUT NOCOPY NUMBER
1021 ,x_status_code OUT NOCOPY varchar2
1022 ,x_status_text OUT NOCOPY varchar2
1023 )
1024 IS
1025 l_item_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1026 l_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1027 l_listprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1028 l_bestprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1032 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1029 l_status_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1030 l_status_text_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
1031
1033 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1034
1035 --gzhang 01/21/01, model bundle cache
1036 l_modelbundle_flag_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1037
1038 x_return_status VARCHAR2(1);
1039 x_return_status_text VARCHAR2(240);
1040
1041 BEGIN
1042 --gzhang 08/08/2002, bug#2488246
1043 --ibe_util.enable_debug;
1044 l_item_tbl.extend();
1045 l_item_tbl(1) := p_inventory_item_id;
1046 l_uom_tbl.extend();
1047 l_uom_tbl(1) := p_uom_code;
1048
1049 --gzhang 01/21/01, model bundle cache
1050 l_modelbundle_flag_tbl.extend;
1051 l_modelbundle_flag_tbl(1) := p_model_bundle_flag;
1052
1053 CalculatePrices(
1054 p_price_list_id => p_price_list_id,
1055
1056 --gzhang 12/03/01 model bundle
1057 p_model_id => p_model_id,
1058 p_organization_id => p_organization_id,
1059
1060 p_currency_code => p_currency_code,
1061 p_item_tbl => l_item_tbl,
1062 p_uom_tbl => l_uom_tbl,
1063
1064 --gzhang 01/21/01, model bundle cache
1065 p_model_bundle_flag_tbl => l_modelbundle_flag_tbl,
1066
1067 p_request_type_code => p_request_type_code,
1068 p_pricing_event => p_pricing_event,
1069 x_listprice_tbl => l_listprice_tbl,
1070 x_bestprice_tbl => l_bestprice_tbl,
1071 x_status_code_tbl => l_status_code_tbl,
1072 x_status_text_tbl => l_status_text_tbl,
1073 x_parentIndex_tbl => l_parentIndex_tbl,
1074 x_childIndex_tbl => l_childIndex_tbl,
1075 x_return_status => x_return_status,
1076 x_return_status_text => x_return_status_text
1077 );
1078 x_listprice := l_listprice_tbl(1);
1079 x_bestprice := l_bestprice_tbl(1);
1080 x_status_code := l_status_code_tbl(1);
1081 x_status_text := l_status_text_tbl(1);
1082 --gzhang 08/08/2002, bug#2488246
1083 --ibe_util.disable_debug;
1084 END GetPrice;
1085
1086
1087 --2.b [using qp] get price of one item base on party_id and cust_account_id
1088 PROCEDURE GetPrice(
1089 p_party_id IN NUMBER
1090 ,p_cust_account_id IN NUMBER
1091
1092 --gzhang 12/03/01 model bundle
1093 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
1094 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1095
1096 ,p_currency_code IN VARCHAR2
1097 ,p_inventory_item_id IN NUMBER
1098 ,p_uom_code IN VARCHAR2
1099 -- ,p_calculate_flag IN CHAR(1) :='Y'
1100
1101 --01/21/01 gzhang, model bundle cache
1102 ,p_model_bundle_flag IN VARCHAR2 := NULL
1103
1104 ,p_request_type_code IN VARCHAR2
1105 ,p_pricing_event IN VARCHAR2
1106 ,x_listprice OUT NOCOPY NUMBER
1107 ,x_bestprice OUT NOCOPY NUMBER
1108 ,x_status_code OUT NOCOPY varchar2
1109 ,x_status_text OUT NOCOPY varchar2
1110 )
1111 IS
1112 l_item_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1113 l_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1114 l_listprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1115 l_bestprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1116 l_status_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1117 l_status_text_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
1118
1119 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1120 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1121
1122 --gzhang 01/21/01, model bundle cache
1123 l_modelbundle_flag_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1124
1125 x_return_status VARCHAR2(1);
1126 x_return_status_text VARCHAR2(240);
1127
1128
1129 BEGIN
1130 --gzhang 08/08/2002, bug#2488246
1131 --ibe_util.enable_debug;
1132 l_item_tbl.extend();
1133 l_item_tbl(1) := p_inventory_item_id;
1134 l_uom_tbl.extend();
1135 l_uom_tbl(1) := p_uom_code;
1136
1137 --gzhang 01/21/01, model bundle cache, bug fix#2222002
1138 l_modelbundle_flag_tbl.extend;
1139 l_modelbundle_flag_tbl(1) := p_model_bundle_flag;
1140
1141 CalculatePrices(
1142 p_party_id => p_party_id,
1143 p_cust_account_id => p_cust_account_id,
1144
1145 --gzhang 12/03/01 model bundle
1146 p_model_id => p_model_id,
1147 p_organization_id => p_organization_id,
1148
1149 p_currency_code => p_currency_code,
1150 p_item_tbl => l_item_tbl,
1151 p_uom_tbl => l_uom_tbl,
1152
1153 --gzhang 01/21/01, model bundle cache
1154 p_model_bundle_flag_tbl => l_modelbundle_flag_tbl,
1155
1156 p_request_type_code => p_request_type_code,
1157 p_pricing_event => p_pricing_event,
1158 x_listprice_tbl => l_listprice_tbl,
1159 x_bestprice_tbl => l_bestprice_tbl,
1160 x_status_code_tbl => l_status_code_tbl,
1161 x_status_text_tbl => l_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 x_listprice := l_listprice_tbl(1);
1168 x_bestprice := l_bestprice_tbl(1);
1169 x_status_code := l_status_code_tbl(1);
1170 x_status_text := l_status_text_tbl(1);
1174
1171 --gzhang 08/08/2002, bug#2488246
1172 --ibe_util.disable_debug;
1173 END GetPrice;
1175 --2.b1 [using qp] get price of one item base on price_list_id, party_id,
1176 -- and cust_account_id
1177 PROCEDURE GetPrice(
1178 p_price_list_id IN NUMBER
1179 ,p_party_id IN NUMBER
1180 ,p_cust_account_id IN NUMBER
1181
1182 --gzhang 12/03/01 model bundle
1183 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
1184 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1185
1186 ,p_currency_code IN VARCHAR2
1187 ,p_inventory_item_id IN NUMBER
1188 ,p_uom_code IN VARCHAR2
1189 -- ,p_calculate_flag IN CHAR(1) :='Y'
1190
1191 --01/21/01 gzhang, model bundle cache
1192 ,p_model_bundle_flag IN VARCHAR2 := NULL
1193
1194 ,p_request_type_code IN VARCHAR2
1195 ,p_pricing_event IN VARCHAR2
1196 ,x_listprice OUT NOCOPY NUMBER
1197 ,x_bestprice OUT NOCOPY NUMBER
1198 ,x_status_code OUT NOCOPY varchar2
1199 ,x_status_text OUT NOCOPY varchar2
1200 )
1201 IS
1202 l_item_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1203 l_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1204 l_listprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1205 l_bestprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1206 l_status_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1207 l_status_text_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
1208
1209 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1210 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1211
1212 --gzhang 01/21/01, model bundle cache
1213 l_modelbundle_flag_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1214
1215 x_return_status VARCHAR2(1);
1216 x_return_status_text VARCHAR2(240);
1217
1218
1219 BEGIN
1220 --gzhang 08/08/2002, bug#2488246
1221 --ibe_util.enable_debug;
1222 l_item_tbl.extend();
1223 l_item_tbl(1) := p_inventory_item_id;
1224 l_uom_tbl.extend();
1225 l_uom_tbl(1) := p_uom_code;
1226 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1227 IBE_UTIL.DEBUG('price list: ' || p_price_list_id);
1228 IBE_UTIL.DEBUG('party: ' || p_party_id);
1229 IBE_UTIL.DEBUG('account: ' || p_cust_account_id);
1230 END IF;
1231
1232 --gzhang 01/21/01, model bundle cache, bug fix#2222002
1233 l_modelbundle_flag_tbl.extend;
1234 l_modelbundle_flag_tbl(1) := p_model_bundle_flag;
1235
1236 CalculatePrices(
1237 p_price_list_id => p_price_list_id,
1238 p_party_id => p_party_id,
1239 p_cust_account_id => p_cust_account_id,
1240
1241 --gzhang 12/03/01 model bundle
1242 p_model_id => p_model_id,
1243 p_organization_id => p_organization_id,
1244
1245 p_currency_code => p_currency_code,
1246 p_item_tbl => l_item_tbl,
1247 p_uom_tbl => l_uom_tbl,
1248
1249 --gzhang 01/21/01, model bundle cache
1250 p_model_bundle_flag_tbl => l_modelbundle_flag_tbl,
1251
1252 p_request_type_code => p_request_type_code,
1253 p_pricing_event => p_pricing_event,
1254 x_listprice_tbl => l_listprice_tbl,
1255 x_bestprice_tbl => l_bestprice_tbl,
1256 x_status_code_tbl => l_status_code_tbl,
1257 x_status_text_tbl => l_status_text_tbl,
1258 x_parentIndex_tbl => l_parentIndex_tbl,
1259 x_childIndex_tbl => l_childIndex_tbl,
1260 x_return_status => x_return_status,
1261 x_return_status_text => x_return_status_text
1262 );
1263 x_listprice := l_listprice_tbl(1);
1264 x_bestprice := l_bestprice_tbl(1);
1265 x_status_code := l_status_code_tbl(1);
1266 x_status_text := l_status_text_tbl(1);
1267 --gzhang 08/08/2002, bug#2488246
1268 --ibe_util.disable_debug;
1269 END GetPrice;
1270
1271
1272 -- 2.c [using qp] get price of one item base on price_list_id for service support
1273 PROCEDURE GetPrice(
1274 p_price_list_id IN NUMBER
1275
1276 --gzhang 12/03/01 model bundle
1277 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
1278 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1279
1280 ,p_currency_code IN VARCHAR2
1281 ,p_inventory_item_id IN NUMBER
1282 ,p_uom_code IN VARCHAR2
1283 ,p_related_inventory_item_id IN NUMBER
1284 ,p_related_uom_code IN VARCHAR2
1285 -- ,p_calculate_flag IN CHAR(1) :='Y'
1286
1287 --01/21/01 gzhang, model bundle cache
1288 ,p_model_bundle_flag IN VARCHAR2 := NULL
1289
1290 ,p_request_type_code IN VARCHAR2
1291 ,p_pricing_event IN VARCHAR2
1292 ,x_listprice OUT NOCOPY NUMBER
1293 ,x_bestprice OUT NOCOPY NUMBER
1294 ,x_status_code OUT NOCOPY varchar2
1295 ,x_status_text OUT NOCOPY varchar2
1296 ,x_related_listprice OUT NOCOPY NUMBER
1297 ,x_related_bestprice OUT NOCOPY NUMBER
1298 ,x_related_status_code OUT NOCOPY varchar2
1299 ,x_related_status_text OUT NOCOPY varchar2
1300 )
1301 IS
1302 l_item_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1303 l_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1304 l_listprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1305 l_bestprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1309 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1306 l_status_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1307 l_status_text_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
1308
1310 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1311
1312 lx_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1313 lx_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1314
1315 --gzhang 01/21/01, model bundle cache
1316 l_modelbundle_flag_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1317
1318 x_return_status VARCHAR2(1);
1319 x_return_status_text VARCHAR2(240);
1320
1321 BEGIN
1322 --gzhang 08/08/2002, bug#2488246
1323 --ibe_util.enable_debug;
1324 l_item_tbl.extend();
1325 l_uom_tbl.extend();
1326 l_item_tbl(1) := p_inventory_item_id;
1327 l_uom_tbl(1) := p_uom_code;
1328
1329
1330 l_item_tbl.extend();
1331 l_uom_tbl.extend();
1332 l_item_tbl(2) := p_related_inventory_item_id;
1333 l_uom_tbl(2) := p_related_uom_code;
1334
1335 --gzhang 01/21/01, model bundle cache, bug fix#2222002
1336 l_modelbundle_flag_tbl.extend;
1337 l_modelbundle_flag_tbl(1) := p_model_bundle_flag;
1338
1339 l_parentIndex_tbl.extend();
1340 l_childIndex_tbl.extend();
1341
1342 l_parentIndex_tbl(1) := 1;
1343 l_childIndex_tbl(1) := 2;
1344
1345 CalculatePrices(
1346 p_price_list_id => p_price_list_id,
1347
1348 --gzhang 12/03/01 model bundle
1349 p_model_id => p_model_id,
1350 p_organization_id => p_organization_id,
1351
1352 p_currency_code => p_currency_code,
1353 p_item_tbl => l_item_tbl,
1354 p_uom_tbl => l_uom_tbl,
1355 p_parentIndex_tbl => l_parentIndex_tbl,
1356 p_childIndex_tbl => l_childIndex_tbl,
1357
1358 --gzhang 01/21/01, model bundle cache
1359 p_model_bundle_flag_tbl => l_modelbundle_flag_tbl,
1360
1361 p_request_type_code => p_request_type_code,
1362 p_pricing_event => p_pricing_event,
1363 x_listprice_tbl => l_listprice_tbl,
1364 x_bestprice_tbl => l_bestprice_tbl,
1365 x_status_code_tbl => l_status_code_tbl,
1366 x_status_text_tbl => l_status_text_tbl,
1367 x_parentIndex_tbl => lx_parentIndex_tbl,
1368 x_childIndex_tbl => lx_childIndex_tbl,
1369 x_return_status => x_return_status,
1370 x_return_status_text => x_return_status_text
1371 );
1372
1373 x_listprice := l_listprice_tbl(1);
1374 x_bestprice := l_bestprice_tbl(1);
1375 x_status_code := l_status_code_tbl(1);
1376 x_status_text := l_status_text_tbl(1);
1377
1378 x_related_listprice := l_listprice_tbl(2);
1379 x_related_bestprice := l_bestprice_tbl(2);
1380 x_related_status_code := l_status_code_tbl(2);
1381 x_related_status_text := l_status_text_tbl(2);
1382
1383 --gzhang 08/08/2002, bug#2488246
1384 --ibe_util.disable_debug;
1385 END GetPrice;
1386
1387
1388
1389 -- 2.d [using qp] get price of one item base customer info for service support
1390 PROCEDURE GetPrice(
1391 p_party_id IN NUMBER
1392 ,p_cust_account_id IN NUMBER
1393
1394 --gzhang 12/03/01 model bundle
1395 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
1396 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1397
1398 ,p_currency_code IN VARCHAR2
1399 ,p_inventory_item_id IN NUMBER
1400 ,p_uom_code IN VARCHAR2
1401 ,p_related_inventory_item_id IN NUMBER
1402 ,p_related_uom_code IN VARCHAR2
1403 -- ,p_calculate_flag IN CHAR(1) :='Y'
1404
1405 --01/21/01 gzhang, model bundle cache
1406 ,p_model_bundle_flag IN VARCHAR2 := NULL
1407
1408 ,p_request_type_code IN VARCHAR2
1409 ,p_pricing_event IN VARCHAR2
1410 ,x_listprice OUT NOCOPY NUMBER
1411 ,x_bestprice OUT NOCOPY NUMBER
1412 ,x_status_code OUT NOCOPY varchar2
1413 ,x_status_text OUT NOCOPY varchar2
1414 ,x_related_listprice OUT NOCOPY NUMBER
1415 ,x_related_bestprice OUT NOCOPY NUMBER
1416 ,x_related_status_code OUT NOCOPY varchar2
1417 ,x_related_status_text OUT NOCOPY varchar2
1418 )
1419 IS
1420 l_item_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1421 l_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1422 l_listprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1423 l_bestprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1424 l_status_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1425 l_status_text_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
1426
1427 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1428 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1429
1430 lx_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1431 lx_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1432
1433 --gzhang 01/21/01, model bundle cache
1434 l_modelbundle_flag_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1435
1436 x_return_status VARCHAR2(1);
1437 x_return_status_text VARCHAR2(240);
1438
1439 BEGIN
1440 --gzhang 08/08/2002, bug#2488246
1441 --ibe_util.enable_debug;
1442 l_item_tbl.extend();
1443 l_uom_tbl.extend();
1444 l_item_tbl(1) := p_inventory_item_id;
1445 l_uom_tbl(1) := p_uom_code;
1446
1447 l_item_tbl.extend();
1448 l_uom_tbl.extend();
1452 --gzhang 01/21/01, model bundle cache, bug fix#2222002
1449 l_item_tbl(2) := p_related_inventory_item_id;
1450 l_uom_tbl(2) := p_related_uom_code;
1451
1453 l_modelbundle_flag_tbl.extend;
1454 l_modelbundle_flag_tbl(1) := p_model_bundle_flag;
1455
1456 l_parentIndex_tbl.extend();
1457 l_childIndex_tbl.extend();
1458
1459 l_parentIndex_tbl(1) := 1;
1460 l_childIndex_tbl(1) := 2;
1461
1462
1463 CalculatePrices(
1464 p_party_id => p_party_id,
1465 p_cust_account_id => p_cust_account_id,
1466
1467 --gzhang 12/03/01 model bundle
1468 p_model_id => p_model_id,
1469 p_organization_id => p_organization_id,
1470
1471 p_currency_code => p_currency_code,
1472 p_item_tbl => l_item_tbl,
1473 p_uom_tbl => l_uom_tbl,
1474 p_parentIndex_tbl => l_parentIndex_tbl,
1475 p_childIndex_tbl => l_childIndex_tbl,
1476
1477 --gzhang 01/21/01, model bundle cache
1478 p_model_bundle_flag_tbl => l_modelbundle_flag_tbl,
1479
1480 p_request_type_code => p_request_type_code,
1481 p_pricing_event => p_pricing_event,
1482 x_listprice_tbl => l_listprice_tbl,
1483 x_bestprice_tbl => l_bestprice_tbl,
1484 x_status_code_tbl => l_status_code_tbl,
1485 x_status_text_tbl => l_status_text_tbl,
1486 x_parentIndex_tbl => lx_parentIndex_tbl,
1487 x_childIndex_tbl => lx_childIndex_tbl,
1488 x_return_status => x_return_status,
1489 x_return_status_text => x_return_status_text
1490 );
1491 x_listprice := l_listprice_tbl(1);
1492 x_bestprice := l_bestprice_tbl(1);
1493 x_status_code := l_status_code_tbl(1);
1494 x_status_text := l_status_text_tbl(1);
1495
1496
1497 x_related_listprice := l_listprice_tbl(2);
1498 x_related_bestprice := l_bestprice_tbl(2);
1499 x_related_status_code := l_status_code_tbl(2);
1500 x_related_status_text := l_status_text_tbl(2);
1501
1502 --gzhang 08/08/2002, bug#2488246
1503 --ibe_util.disable_debug;
1504
1505 END GetPrice;
1506
1507
1508 -- 2.d1 [using qp] get price of one item based on price_list_id and
1509 -- customer info for service support
1510 PROCEDURE GetPrice(
1511 p_price_list_id IN NUMBER
1512 ,p_party_id IN NUMBER
1513 ,p_cust_account_id IN NUMBER
1514
1515 --gzhang 12/03/01 model bundle
1516 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
1517 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1518
1519 ,p_currency_code IN VARCHAR2
1520 ,p_inventory_item_id IN NUMBER
1521 ,p_uom_code IN VARCHAR2
1522 ,p_related_inventory_item_id IN NUMBER
1523 ,p_related_uom_code IN VARCHAR2
1524 -- ,p_calculate_flag IN CHAR(1) :='Y'
1525
1526 --01/21/01 gzhang, model bundle cache
1527 ,p_model_bundle_flag IN VARCHAR2 := NULL
1528
1529 ,p_request_type_code IN VARCHAR2
1530 ,p_pricing_event IN VARCHAR2
1531 ,x_listprice OUT NOCOPY NUMBER
1532 ,x_bestprice OUT NOCOPY NUMBER
1533 ,x_status_code OUT NOCOPY varchar2
1534 ,x_status_text OUT NOCOPY varchar2
1535 ,x_related_listprice OUT NOCOPY NUMBER
1536 ,x_related_bestprice OUT NOCOPY NUMBER
1537 ,x_related_status_code OUT NOCOPY varchar2
1538 ,x_related_status_text OUT NOCOPY varchar2
1539 )
1540 IS
1541 l_item_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1542 l_uom_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1543 l_listprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1544 l_bestprice_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1545 l_status_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1546 l_status_text_tbl JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
1547
1548 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1549 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1550
1551 lx_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1552 lx_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1553
1554 --gzhang 01/21/01, model bundle cache
1555 l_modelbundle_flag_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1556
1557 x_return_status VARCHAR2(1);
1558 x_return_status_text VARCHAR2(240);
1559
1560 BEGIN
1561 --gzhang 08/08/2002, bug#2488246
1562 --ibe_util.enable_debug;
1563 l_item_tbl.extend();
1564 l_uom_tbl.extend();
1565 l_item_tbl(1) := p_inventory_item_id;
1566 l_uom_tbl(1) := p_uom_code;
1567
1568 l_item_tbl.extend();
1569 l_uom_tbl.extend();
1570 l_item_tbl(2) := p_related_inventory_item_id;
1571 l_uom_tbl(2) := p_related_uom_code;
1572
1573 --gzhang 01/21/01, model bundle cache, bug fix#2222002
1574 l_modelbundle_flag_tbl.extend;
1575 l_modelbundle_flag_tbl(1) := p_model_bundle_flag;
1576
1577 l_parentIndex_tbl.extend();
1578 l_childIndex_tbl.extend();
1579
1580 l_parentIndex_tbl(1) := 1;
1581 l_childIndex_tbl(1) := 2;
1582
1583
1584 CalculatePrices(
1585 p_price_list_id => p_price_list_id,
1586 p_party_id => p_party_id,
1587 p_cust_account_id => p_cust_account_id,
1588
1589 --gzhang 12/03/01 model bundle
1590 p_model_id => p_model_id,
1591 p_organization_id => p_organization_id,
1592
1593 p_currency_code => p_currency_code,
1594 p_item_tbl => l_item_tbl,
1598
1595 p_uom_tbl => l_uom_tbl,
1596 p_parentIndex_tbl => l_parentIndex_tbl,
1597 p_childIndex_tbl => l_childIndex_tbl,
1599 --gzhang 01/21/01, model bundle cache
1600 p_model_bundle_flag_tbl => l_modelbundle_flag_tbl,
1601
1602 p_request_type_code => p_request_type_code,
1603 p_pricing_event => p_pricing_event,
1604 x_listprice_tbl => l_listprice_tbl,
1605 x_bestprice_tbl => l_bestprice_tbl,
1606 x_status_code_tbl => l_status_code_tbl,
1607 x_status_text_tbl => l_status_text_tbl,
1608 x_parentIndex_tbl => lx_parentIndex_tbl,
1609 x_childIndex_tbl => lx_childIndex_tbl,
1610 x_return_status => x_return_status,
1611 x_return_status_text => x_return_status_text
1612 );
1613 x_listprice := l_listprice_tbl(1);
1614 x_bestprice := l_bestprice_tbl(1);
1615 x_status_code := l_status_code_tbl(1);
1616 x_status_text := l_status_text_tbl(1);
1617
1618
1619 x_related_listprice := l_listprice_tbl(2);
1620 x_related_bestprice := l_bestprice_tbl(2);
1621 x_related_status_code := l_status_code_tbl(2);
1622 x_related_status_text := l_status_text_tbl(2);
1623 --gzhang 08/08/2002, bug#2488246
1624 --ibe_util.disable_debug;
1625 END GetPrice;
1626
1627
1628 -- 2.e [using qp] get prices for a list of items based on price_list_id
1629 PROCEDURE GetPrices(
1630 p_price_list_id IN NUMBER
1631
1632 --gzhang 12/03/01 model bundle
1633 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
1634 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1635
1636 ,p_currency_code IN VARCHAR2
1637 ,p_item_tbl IN JTF_NUMBER_TABLE
1638 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
1639 -- ,p_calculate_flag IN CHAR(1) :='Y'
1640
1641 --gzhang 01/21/01, model bundle cache
1642 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
1643
1644 ,p_request_type_code IN VARCHAR2
1645 ,p_pricing_event IN VARCHAR2
1646 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
1647 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
1648 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
1649 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
1650 ,x_return_status out nocopy varchar2
1651 ,x_return_status_text out nocopy varchar2
1652
1653 )
1654 IS
1655 l_version VARCHAR2(240);
1656
1657 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1658 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1659
1660 BEGIN
1661 --gzhang 08/08/2002, bug#2488246
1662 --ibe_util.enable_debug;
1663 CalculatePrices(
1664 p_price_list_id => p_price_list_id,
1665
1666 --gzhang 12/03/01 model bundle
1667 p_model_id => p_model_id,
1668 p_organization_id => p_organization_id,
1669
1670 p_currency_code => p_currency_code,
1671 p_item_tbl => p_item_tbl,
1672 p_uom_tbl => p_uom_tbl,
1673
1674 --gzhang 01/21/01, model bundle cache
1675 p_model_bundle_flag_tbl => p_model_bundle_flag_tbl,
1676
1677 p_request_type_code => p_request_type_code,
1678 p_pricing_event => p_pricing_event,
1679 x_listprice_tbl => x_listprice_tbl,
1680 x_bestprice_tbl => x_bestprice_tbl,
1681 x_status_code_tbl => x_status_code_tbl,
1682 x_status_text_tbl => x_status_text_tbl,
1683 x_parentIndex_tbl => l_parentIndex_tbl,
1684 x_childIndex_tbl => l_childIndex_tbl,
1685 x_return_status => x_return_status,
1686 x_return_status_text => x_return_status_text
1687 );
1688 --gzhang 08/08/2002, bug#2488246
1689 --ibe_util.disable_debug;
1690 END GetPrices;
1691
1692 -- 2.f [using qp] get prices of a list of items based on party_id and cust_accoutn_id
1693 PROCEDURE GetPrices(
1694 p_party_id IN NUMBER
1695 ,p_cust_account_id IN number
1696
1697 --gzhang 12/03/01 model bundle
1698 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
1699 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1700
1701 ,p_currency_code IN VARCHAR2
1702 ,p_item_tbl IN JTF_NUMBER_TABLE
1703 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
1704 -- ,p_calculate_flag IN CHAR(1) :='Y'
1705
1706 --gzhang 01/21/01, model bundle cache
1707 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
1708
1709 ,p_request_type_code IN VARCHAR2
1710 ,p_pricing_event IN VARCHAR2
1711 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
1712 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
1713 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
1714 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
1715 ,x_return_status out nocopy varchar2
1716 ,x_return_status_text out nocopy varchar2
1717
1718 )
1719 IS
1720 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1721 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1722
1723 BEGIN
1724 --gzhang 08/08/2002, bug#2488246
1725 --ibe_util.enable_debug;
1726 CalculatePrices(
1727 p_party_id => p_party_id,
1728 p_cust_account_id => p_cust_account_id,
1729
1730 --gzhang 12/03/01 model bundle
1731 p_model_id => p_model_id,
1732 p_organization_id => p_organization_id,
1733
1734 p_currency_code => p_currency_code,
1735 p_item_tbl => p_item_tbl,
1736 p_uom_tbl => p_uom_tbl,
1737
1741 p_request_type_code => p_request_type_code,
1738 --gzhang 01/21/01, model bundle cache
1739 p_model_bundle_flag_tbl => p_model_bundle_flag_tbl,
1740
1742 p_pricing_event => p_pricing_event,
1743 x_listprice_tbl => x_listprice_tbl,
1744 x_bestprice_tbl => x_bestprice_tbl,
1745 x_status_code_tbl => x_status_code_tbl,
1746 x_status_text_tbl => x_status_text_tbl,
1747 x_parentIndex_tbl => l_parentIndex_tbl,
1748 x_childIndex_tbl => l_childIndex_tbl,
1749 x_return_status => x_return_status,
1750 x_return_status_text => x_return_status_text
1751
1752 );
1753 --gzhang 08/08/2002, bug#2488246
1754 --ibe_util.disable_debug;
1755 END GetPrices;
1756
1757 -- 2.f1 [using qp] get prices of a list of items based on price_list_id, party_id,
1758 -- and cust_account_id
1759 PROCEDURE GetPrices(
1760 p_price_list_id IN NUMBER
1761 ,p_party_id IN NUMBER
1762 ,p_cust_account_id IN number
1763
1764 --gzhang 12/03/01 model bundle
1765 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
1766 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1767
1768 ,p_currency_code IN VARCHAR2
1769 ,p_item_tbl IN JTF_NUMBER_TABLE
1770 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
1771 -- ,p_calculate_flag IN CHAR(1) :='Y'
1772
1773 --gzhang 01/21/01, model bundle cache
1774 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
1775
1776 ,p_request_type_code IN VARCHAR2
1777 ,p_pricing_event IN VARCHAR2
1778 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
1779 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
1780 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
1781 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
1782 ,x_return_status out nocopy varchar2
1783 ,x_return_status_text out nocopy varchar2
1784
1785 )
1786 IS
1787 l_parentIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1788 l_childIndex_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1789
1790 BEGIN
1791 --gzhang 08/08/2002, bug#2488246
1792 --ibe_util.enable_debug;
1793 CalculatePrices(
1794 p_price_list_id => p_price_list_id,
1795 p_party_id => p_party_id,
1796 p_cust_account_id => p_cust_account_id,
1797
1798 --gzhang 12/03/01 model bundle
1799 p_model_id => p_model_id,
1800 p_organization_id => p_organization_id,
1801
1802 p_currency_code => p_currency_code,
1803 p_item_tbl => p_item_tbl,
1804 p_uom_tbl => p_uom_tbl,
1805
1806 --gzhang 01/21/01, model bundle cache
1807 p_model_bundle_flag_tbl => p_model_bundle_flag_tbl,
1808
1809 p_request_type_code => p_request_type_code,
1810 p_pricing_event => p_pricing_event,
1811 x_listprice_tbl => x_listprice_tbl,
1812 x_bestprice_tbl => x_bestprice_tbl,
1813 x_status_code_tbl => x_status_code_tbl,
1814 x_status_text_tbl => x_status_text_tbl,
1815 x_parentIndex_tbl => l_parentIndex_tbl,
1816 x_childIndex_tbl => l_childIndex_tbl,
1817 x_return_status => x_return_status,
1818 x_return_status_text => x_return_status_text
1819
1820 );
1821 --gzhang 08/08/2002, bug#2488246
1822 --ibe_util.disable_debug;
1823 END GetPrices;
1824
1825
1826 -- 2.g [using qp] get prices of a list of items based on price_list_id for service support
1827 PROCEDURE GetPrices(
1828 p_price_list_id IN NUMBER
1829
1830 --gzhang 12/03/01 model bundle
1831 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
1832 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1833
1834 ,p_currency_code IN VARCHAR2
1835 ,p_item_tbl IN JTF_NUMBER_TABLE
1836 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
1837 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE
1838 ,p_childIndex_tbl IN JTF_NUMBER_TABLE
1839 -- ,p_calculate_flag IN CHAR(1) :='Y'
1840
1841 --gzhang 01/21/01, model bundle cache
1842 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
1843
1844 ,p_request_type_code IN VARCHAR2
1845 ,p_pricing_event IN VARCHAR2
1846 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
1847 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
1848 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
1849 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
1850 ,x_parentIndex_tbl out nocopy JTF_NUMBER_TABLE
1851 ,x_childIndex_tbl out nocopy JTF_NUMBER_TABLE
1852 ,x_return_status out nocopy varchar2
1853 ,x_return_status_text out nocopy varchar2
1854 )
1855 IS
1856
1857 BEGIN
1858 --gzhang 08/08/2002, bug#2488246
1859 --ibe_util.enable_debug;
1860 CalculatePrices(
1861 p_price_list_id => p_price_list_id,
1862
1863 --gzhang 12/03/01 model bundle
1864 p_model_id => p_model_id,
1865 p_organization_id => p_organization_id,
1866
1867 p_currency_code => p_currency_code,
1868 p_item_tbl => p_item_tbl,
1869 p_uom_tbl => p_uom_tbl,
1870 p_parentIndex_tbl => p_parentIndex_tbl,
1871 p_childIndex_tbl => p_childIndex_tbl,
1872
1873 --gzhang 01/21/01, model bundle cache
1874 p_model_bundle_flag_tbl => p_model_bundle_flag_tbl,
1875
1876 p_request_type_code => p_request_type_code,
1877 p_pricing_event => p_pricing_event,
1878 x_listprice_tbl => x_listprice_tbl,
1879 x_bestprice_tbl => x_bestprice_tbl,
1883 x_childIndex_tbl => x_childIndex_tbl,
1880 x_status_code_tbl => x_status_code_tbl,
1881 x_status_text_tbl => x_status_text_tbl,
1882 x_parentIndex_tbl => x_parentIndex_tbl,
1884 x_return_status => x_return_status,
1885 x_return_status_text => x_return_status_text
1886
1887 );
1888 --gzhang 08/08/2002, bug#2488246
1889 --ibe_util.disable_debug;
1890 END GetPrices;
1891
1892
1893
1894 -- 2.h [using qp] get prices of a list of items based on party_id and cust_accoutn_id
1895 PROCEDURE GetPrices(
1896 p_party_id IN NUMBER
1897 ,p_cust_account_id IN number
1898
1899 --gzhang 12/03/01 model bundle
1900 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
1901 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1902
1903 ,p_currency_code IN VARCHAR2
1904 ,p_item_tbl IN JTF_NUMBER_TABLE
1905 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
1906 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE
1907 ,p_childIndex_tbl IN JTF_NUMBER_TABLE
1908 -- ,p_calculate_flag IN CHAR(1) :='Y'
1909
1910 --gzhang 01/21/01, model bundle cache
1911 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
1912
1913 ,p_request_type_code IN VARCHAR2
1914 ,p_pricing_event IN VARCHAR2
1915 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
1916 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
1917 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
1918 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
1919 ,x_parentIndex_tbl OUT NOCOPY JTF_NUMBER_TABLE
1920 ,x_childIndex_tbl OUT NOCOPY JTF_NUMBER_TABLE
1921 ,x_return_status out nocopy varchar2
1922 ,x_return_status_text out nocopy varchar2
1923
1924 )
1925 IS
1926
1927 BEGIN
1928 --gzhang 08/08/2002, bug#2488246
1929 --ibe_util.enable_debug;
1930 CalculatePrices(
1931 p_party_id => p_party_id,
1932 p_cust_account_id => p_cust_account_id,
1933
1934 --gzhang 12/03/01 model bundle
1935 p_model_id => p_model_id,
1936 p_organization_id => p_organization_id,
1937
1938 p_currency_code => p_currency_code,
1939 p_item_tbl => p_item_tbl,
1940 p_uom_tbl => p_uom_tbl,
1941 p_parentIndex_tbl => p_parentIndex_tbl,
1942 p_childIndex_tbl => p_childIndex_tbl,
1943
1944 --gzhang 01/21/01, model bundle cache
1945 p_model_bundle_flag_tbl => p_model_bundle_flag_tbl,
1946
1947 p_request_type_code => p_request_type_code,
1948 p_pricing_event => p_pricing_event,
1949 x_listprice_tbl => x_listprice_tbl,
1950 x_bestprice_tbl => x_bestprice_tbl,
1951 x_status_code_tbl => x_status_code_tbl,
1952 x_status_text_tbl => x_status_text_tbl,
1953 x_parentIndex_tbl => x_parentIndex_tbl,
1954 x_childIndex_tbl => x_childIndex_tbl,
1955 x_return_status => x_return_status,
1956 x_return_status_text => x_return_status_text
1957
1958 );
1959 --gzhang 08/08/2002, bug#2488246
1960 --ibe_util.disable_debug;
1961 END GetPrices;
1962
1963 -- 2.h1 [using qp] get prices of a list of items based on price_list_id,
1964 -- party_id and cust_account_id
1965 PROCEDURE GetPrices(
1966 p_price_list_id IN NUMBER
1967 ,p_party_id IN NUMBER
1968 ,p_cust_account_id IN number
1969
1970 --gzhang 12/03/01 model bundle
1971 ,p_model_id IN NUMBER := FND_API.G_MISS_NUM
1972 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1973
1974 ,p_currency_code IN VARCHAR2
1975 ,p_item_tbl IN JTF_NUMBER_TABLE
1976 ,p_uom_tbl IN JTF_VARCHAR2_TABLE_100
1977 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE
1978 ,p_childIndex_tbl IN JTF_NUMBER_TABLE
1979 -- ,p_calculate_flag IN CHAR(1) :='Y'
1980
1981 --gzhang 01/21/01, model bundle cache
1982 ,p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100 := NULL
1983
1984 ,p_request_type_code IN VARCHAR2
1985 ,p_pricing_event IN VARCHAR2
1986 ,x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE
1987 ,x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE
1988 ,x_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100
1989 ,x_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300
1990 ,x_parentIndex_tbl OUT NOCOPY JTF_NUMBER_TABLE
1991 ,x_childIndex_tbl OUT NOCOPY JTF_NUMBER_TABLE
1992 ,x_return_status out nocopy varchar2
1993 ,x_return_status_text out nocopy varchar2
1994
1995 )
1996 IS
1997
1998 BEGIN
1999 --gzhang 08/08/2002, bug#2488246
2000 --ibe_util.enable_debug;
2001 CalculatePrices(
2002 p_price_list_id => p_price_list_id,
2003 p_party_id => p_party_id,
2004 p_cust_account_id => p_cust_account_id,
2005
2006 --gzhang 12/03/01 model bundle
2007 p_model_id => p_model_id,
2008 p_organization_id => p_organization_id,
2009
2010 p_currency_code => p_currency_code,
2011 p_item_tbl => p_item_tbl,
2012 p_uom_tbl => p_uom_tbl,
2013 p_parentIndex_tbl => p_parentIndex_tbl,
2014 p_childIndex_tbl => p_childIndex_tbl,
2015
2016 --gzhang 01/21/01, model bundle cache
2017 p_model_bundle_flag_tbl => p_model_bundle_flag_tbl,
2018
2019 p_request_type_code => p_request_type_code,
2020 p_pricing_event => p_pricing_event,
2024 x_status_text_tbl => x_status_text_tbl,
2021 x_listprice_tbl => x_listprice_tbl,
2022 x_bestprice_tbl => x_bestprice_tbl,
2023 x_status_code_tbl => x_status_code_tbl,
2025 x_parentIndex_tbl => x_parentIndex_tbl,
2026 x_childIndex_tbl => x_childIndex_tbl,
2027 x_return_status => x_return_status,
2028 x_return_status_text => x_return_status_text
2029
2030 );
2031 --gzhang 08/08/2002, bug#2488246
2032 --ibe_util.disable_debug;
2033 END GetPrices;
2034
2035 -- integration with QP TEMP table
2036 PROCEDURE PRICE_REQUEST(
2037 p_price_list_id IN NUMBER := FND_API.G_MISS_NUM
2038 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
2039 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
2040 ,p_currency_code IN VARCHAR2
2041 ,p_minisite_id IN NUMBER := NULL
2042 ,p_item_tbl IN QP_PREQ_GRP.NUMBER_TYPE
2043 ,p_uom_code_tbl IN OUT NOCOPY QP_PREQ_GRP.VARCHAR_TYPE
2044 ,p_model_id_tbl IN JTF_NUMBER_TABLE
2045 ,p_line_quantity_tbl IN OUT NOCOPY QP_PREQ_GRP.NUMBER_TYPE
2046 ,p_parentIndex_tbl IN QP_PREQ_GRP.NUMBER_TYPE
2047 ,p_childIndex_tbl IN QP_PREQ_GRP.NUMBER_TYPE
2048 ,p_request_type_code IN VARCHAR2 := 'ASO'
2049 ,p_pricing_event IN VARCHAR2
2050 ,x_price_csr OUT NOCOPY PRICE_REFCURSOR_TYPE
2051 ,x_line_index_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
2052 ,x_return_status OUT NOCOPY VARCHAR2
2053 ,x_return_status_text OUT NOCOPY VARCHAR2
2054 )
2055 IS
2056
2057 L_API VARCHAR2(64);
2058
2059 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
2060
2061 I BINARY_INTEGER;
2062 l_t0 NUMBER;
2063 l_t1 NUMBER;
2064 l_t2 NUMBER;
2065 l_ti NUMBER;
2066
2067 G_LINE_INDEX_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
2068 G_LINE_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2069 G_PRICING_EFFECTIVE_DATE_TBL QP_PREQ_GRP.DATE_TYPE ;
2070 G_ACTIVE_DATE_FIRST_TBL QP_PREQ_GRP.DATE_TYPE ;
2071 G_ACTIVE_DATE_FIRST_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2072 G_ACTIVE_DATE_SECOND_TBL QP_PREQ_GRP.DATE_TYPE ;
2073 G_ACTIVE_DATE_SECOND_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE ;
2074 --G_LINE_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE ;
2075 --G_LINE_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2076 G_REQUEST_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2077 G_PRICED_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
2078 G_UOM_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
2079 G_PRICED_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2080 G_CURRENCY_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2081 G_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
2082 G_PERCENT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
2083 G_ADJUSTED_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
2084 G_UPD_ADJUSTED_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
2085 G_PROCESSED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2086 G_PRICE_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2087 G_LINE_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
2088 G_PROCESSING_ORDER_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
2089 G_ROUNDING_FACTOR_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
2090 G_ROUNDING_FLAG_TBL QP_PREQ_GRP.FLAG_TYPE;
2091 G_QUALIFIERS_EXIST_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2092 G_PRICING_ATTRS_EXIST_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2093 G_PRICE_LIST_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
2094 G_PL_VALIDATED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2095 G_PRICE_REQUEST_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2096 G_USAGE_PRICING_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2097 G_LINE_CATEGORY_TBL QP_PREQ_GRP.VARCHAR_TYPE;
2098 G_PRICING_STATUS_CODE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
2099 G_PRICING_STATUS_TEXT_tbl QP_PREQ_GRP.VARCHAR_TYPE;
2100 G_RELATIONSHIP_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
2101 G_LINE_DETAIL_INDEX_tbl QP_PREQ_GRP.NUMBER_TYPE;
2102 G_RLTD_LINE_DETAIL_INDEX_tbl QP_PREQ_GRP.NUMBER_TYPE;
2103
2104 L_PRICE_LIST NUMBER := -9999;
2105 l_model_id NUMBER;
2106 l_cust_party_id NUMBER;
2107 l_service_duration NUMBER;
2108 l_service_duration_period_code VARCHAR2(10);
2109 l_target_duration NUMBER;
2110 l_organization_id NUMBER := -9999;
2111 l_operating_unit NUMBER;
2112 CURSOR get_party_id_cur(l_cust_account_id NUMBER) IS
2113 SELECT party_id
2114 FROM hz_cust_accounts
2115 WHERE cust_account_id = l_cust_account_id;
2116
2117 CURSOR get_organization_id_cur(l_operating_unit NUMBER) IS
2118 SELECT master_organization_id
2119 FROM oe_system_parameters_all
2120 WHERE org_id = l_operating_unit;
2121
2122 CURSOR get_service_info_cur(l_item_id NUMBER, l_organization_id NUMBER) IS
2123 SELECT service_duration, service_duration_period_code
2124 FROM mtl_system_items_vl
2125 WHERE inventory_item_id = l_item_id
2126 AND organization_id = l_organization_id;
2127
2128 BEGIN
2129 L_API := 'PRICE_REQUEST';
2130 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2131 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': QP Version'||QP_PREQ_GRP.GET_VERSION);
2132 END IF;
2133
2134 l_t0 := DBMS_UTILITY.GET_TIME;
2135 IF p_price_list_id IS NOT NULL AND p_price_list_id <> FND_API.G_MISS_NUM THEN
2136 L_PRICE_LIST := p_price_list_id;
2137 END IF;
2138 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2139 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': internal QP price list: '||L_PRICE_LIST);
2143 ASO_PRICING_INT.G_LINE_REC := NULL;
2140 END IF;
2141
2142 -- clear ASO global structures
2144 ASO_PRICING_INT.G_HEADER_REC := NULL;
2145 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2146 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': ASO Global structures were cleared');
2147 END IF;
2148
2149 -- set the request_id
2150 QP_PRICE_REQUEST_CONTEXT.SET_REQUEST_ID();
2151 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2152 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': request id was set');
2153 END IF;
2154
2155 -- setup control record
2156 l_control_rec.pricing_event := p_pricing_event;
2157 l_control_rec.calculate_flag := 'Y';
2158 l_control_rec.simulation_flag := 'N';
2159 l_control_rec.temp_table_insert_flag := 'N';
2160 l_control_rec.request_type_code := 'ASO';
2161 l_control_rec.rounding_flag := 'Q';
2162 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2163 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': control record was set');
2164 END IF;
2165
2166 -- setup ASO G_HEADER_REC
2167 --gzhang 06/07/2003, bug#2987376
2168 ASO_PRICING_INT.G_HEADER_REC.party_id := p_party_id;
2169 ASO_PRICING_INT.G_HEADER_REC.cust_account_id := p_cust_account_id;
2170
2171 --ssekar 22/09/2005 bug#4529258
2172 OPEN get_party_id_cur(p_cust_account_id);
2173 FETCH get_party_id_cur INTO l_cust_party_id;
2174 CLOSE get_party_id_cur;
2175
2176 ASO_PRICING_INT.G_HEADER_REC.cust_party_id:= l_cust_party_id;
2177 ASO_PRICING_INT.G_HEADER_REC.price_list_id:= p_price_list_id;
2178 ASO_PRICING_INT.G_HEADER_REC.minisite_id:= p_minisite_id;
2179 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2180 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': price_list_id='||
2181 p_price_list_id||',cust_account_is='||p_cust_account_id||
2182 ',party_id'||p_party_id);
2183 END IF;
2184
2185 -- populate line items
2186 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2187 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||
2188 ': populating line items..., total line items='||p_item_tbl.count);
2189 END IF;
2190
2191 X_LINE_INDEX_TBL := JTF_VARCHAR2_TABLE_100();
2192 X_LINE_INDEX_TBL.EXTEND(p_item_tbl.count+1);
2193 FOR I in p_item_tbl.FIRST..p_item_tbl.LAST LOOP
2194 l_target_duration := NULL;
2195 l_service_duration := NULL;
2196 l_service_duration_period_code := NULL;
2197
2198 IF p_model_id_tbl(I) = -1 THEN
2199 l_model_id := FND_API.G_MISS_NUM;
2200 ELSE
2201 l_model_id := p_model_id_tbl(I);
2202 END IF;
2203 IF l_model_id = FND_API.G_MISS_NUM THEN
2204 X_LINE_INDEX_TBL(I) := 'L:'||p_item_tbl(I);
2205 ELSE
2206 X_LINE_INDEX_TBL(I) := 'L:'||p_item_tbl(I)||':'||l_model_id;
2207 END IF;
2208
2209 -- bug 4890626 ssekar
2210 IF (p_childindex_tbl IS NOT NULL AND p_childindex_tbl.count >0) THEN
2211 FOR J IN p_childindex_tbl.FIRST..p_childindex_tbl.LAST LOOP
2212 IF I = p_childindex_tbl(J) THEN
2213 IF l_organization_id < 0 THEN
2214 -- fetch the organization id for the current OU.
2215 l_operating_unit := MO_GLOBAL.get_current_org_id();
2216
2217 OPEN get_organization_id_cur(l_operating_unit);
2218 FETCH get_organization_id_cur INTO l_organization_id;
2219 CLOSE get_organization_id_cur;
2220 END IF;
2221
2222 -- obtain the service duration and the period for an item
2223 OPEN get_service_info_cur(p_item_tbl(I),l_organization_id);
2224 FETCH get_service_info_cur INTO
2225 l_service_duration, l_service_duration_period_code;
2226 CLOSE get_service_info_cur;
2227
2228 IF (p_uom_code_tbl(I) IS NOT NULL
2229 AND l_service_duration_period_code IS NOT NULL
2230 AND l_service_duration_period_code <> p_uom_code_tbl(I)) THEN
2231 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2232 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||
2233 'Uom Code and Service Period Different for item '||
2234 p_item_tbl(I));
2235 END IF;
2236 l_target_duration := oks_omint_pub.get_target_duration (
2237 null,null,p_uom_code_tbl(I),
2238 l_service_duration,l_service_duration_period_code,
2239 l_operating_unit);
2240 ELSIF ((l_service_duration_period_code = p_uom_code_tbl(I)) AND
2241 l_service_duration > 1) THEN
2242 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2243 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||
2244 'Uom Code and Service Period equal for item, but quantity >1'||
2245 p_item_tbl(I));
2246 END IF;
2247 l_target_duration := l_service_duration;
2248 END IF;
2249 EXIT;
2250 END IF;
2251 END LOOP;
2252 END IF;
2253 -- populate line item
2254 G_line_index_tbl(I) :=I; -- 1: Request Line Index
2255 G_line_type_code_tbl(I) := 'LINE'; -- 2: LINE or ORDER(Summary Line)
2259 G_active_date_second_tbl(I) := sysdate; -- 6:? Can be Ordered Date or Ship Date==>leave as sysdate
2256 G_pricing_effective_date_tbl(I) := trunc(sysdate); -- 3: Pricing as of what date ? sysdate
2257 G_active_date_first_tbl(I) := sysdate; -- 4:? Can be Ordered Date or Ship Date==> leave as sysdate
2258 G_active_date_first_type_tbl(I) := 'NO TYPE'; -- 5: ORD/SHIP ==>'ORD'? what does 'NO TYPE' mean?
2260 G_active_date_second_type_tbl(I) :='NO TYPE'; -- 7:ORD/SHIP ==?'ORD'? should be "NO TYPE'
2261
2262 --G_line_quantity_tbl(I) := 1; -- 8: Ordered Quantity
2263 --G_LINE_UOM_CODE_TBL(I) := p_uom_code_tbl(I); -- 9: Ordered UOM Code
2264
2265 G_REQUEST_TYPE_CODE_TBL(I) := 'ASO'; --10:
2266 G_PRICED_QUANTITY_TBL(I) := null; --11:used by qp
2267 G_PRICED_UOM_CODE_TBL(I) := null; --12:used by qp
2268 G_CURRENCY_CODE_TBL(I) := p_currency_code; --13:Currency Code
2269 G_UNIT_PRICE_TBL(I) := null; --14:used by qp
2270 G_PERCENT_PRICE_TBL(I) := null; --15:used by qp
2271 G_UOM_QUANTITY_TBL(I) := l_target_duration; --16:
2272 G_ADJUSTED_UNIT_PRICE_TBL(I) := null; --17:
2273 G_UPD_ADJUSTED_UNIT_PRICE_TBL(I) := null; --18:
2274 G_PROCESSED_FLAG_TBL(I) := null; --19:
2275 G_PRICE_FLAG_TBL(I) := 'Y'; --20: Price Flag can have 'Y' , 'N'(No pricing) , 'P'(Phase)
2276 G_LINE_ID_TBL(I) := I; --21: Order Line Id.
2277 G_PROCESSING_ORDER_TBL(I) := null; --22:
2278 G_PRICING_STATUS_CODE_tbl(I) := QP_PREQ_GRP.G_STATUS_UNCHANGED; --23:
2279 G_PRICING_STATUS_TEXT_tbl(I) := null; --24:
2280 G_ROUNDING_FLAG_TBL(I) := null; --25:
2281 G_ROUNDING_FACTOR_TBL(I) := null; --26:
2282 G_QUALIFIERS_EXIST_FLAG_TBL(I) := 'N'; --27:
2283 G_PRICING_ATTRS_EXIST_FLAG_TBL(I) := 'N'; --28:
2284 G_PRICE_LIST_ID_TBL(I) := L_PRICE_LIST; --29:price list id used by qp
2285 G_PL_VALIDATED_FLAG_TBL(I) := 'N'; --30:
2286 G_PRICE_REQUEST_CODE_TBL(I) := null; --31:
2287 G_usage_pricing_type_tbl(I) := QP_PREQ_GRP.G_REGULAR_USAGE_TYPE; --32: This can be 'REGULAR', 'AUTHORING', 'BILLING' --used in usage pricing
2288 --G_LINE_CATEGORY_tbl(I) := null; --33:
2289
2290 -- populate line attributes/qualifiers
2291 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2292 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': building line attributes/qualifiers,line index='||I);
2293 END IF;
2294
2295 ASO_PRICING_INT.G_LINE_REC.inventory_item_id := p_item_tbl(I);
2296 ASO_PRICING_INT.G_LINE_REC.uom_code:= p_uom_code_tbl(I);
2297 ASO_PRICING_INT.G_LINE_REC.quantity:= p_line_quantity_tbl(I);
2298 ASO_PRICING_INT.G_LINE_REC.price_list_id := p_price_list_id;
2299 ASO_PRICING_INT.G_LINE_rec.model_id := l_model_id;
2300 ASO_PRICING_INT.G_LINE_REC.minisite_id := p_minisite_id;
2301
2302 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2303 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': item id ='||p_item_tbl(I)||', uom code ='||p_uom_code_tbl(I)||', model id ='||l_model_id);
2304 END IF;
2305
2306 QP_ATTR_MAPPING_PUB.BUILD_CONTEXTS
2307 (p_request_type_code => p_request_type_code,
2308 p_line_index => I,
2309 p_pricing_type_code => 'L');
2310
2311 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2312 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': line item populated,line index='||I);
2313 END IF;
2314
2315 END LOOP;
2316
2317 -- populate summary line
2318 I := p_item_tbl.LAST;
2319
2320 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2321 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': populating the summary line, last line index='||I);
2322 END IF;
2323
2324 I := I + 1;
2325 G_LINE_INDEX_TBL(I) :=I; -- 1: Request Line Index
2326 G_LINE_TYPE_CODE_TBL(I) := 'ORDER'; -- 2: LINE or ORDER(Summary Line)
2327 G_pricing_effective_date_tbl(I) :=trunc(sysdate); -- 3: Pricing as of what date ?
2328 G_active_date_first_tbl(I) := sysdate; -- 4:? Can be Ordered Date or Ship Date
2329 G_active_date_first_type_tbl(I) := 'NO TYPE'; -- 5: ORD/SHIP ==>'ORD'? what does 'NO TYPE' mean?
2330 G_active_date_second_tbl(I) := sysdate; -- 6:? Can be Ordered Date or Ship Date
2331 G_active_date_second_type_tbl(I) :='NO TYPE'; -- 7:? ORD/SHIP ==?'ORD'?
2332
2333 P_LINE_QUANTITY_TBL(I) := 1; -- 8: Ordered Quantity
2334 --G_LINE_UOM_CODE_TBL(I) := NULL; -- 9: Ordered UOM Code
2335 P_UOM_CODE_TBL(I) := NULL; -- 9: Ordered UOM Code
2336 G_request_type_code_tbl(I) := 'ASO'; --10:
2337 G_PRICED_QUANTITY_TBL(I) := null; --11:?
2341 G_PERCENT_PRICE_TBL(I) := null; --15:?
2338 G_PRICED_UOM_CODE_TBL(I) := null; --12:?
2339 G_currency_code_tbl(I) := p_currency_code; --13: Currency Code
2340 G_UNIT_PRICE_TBL(I) := null; --14:?
2342 G_UOM_QUANTITY_TBL(I) := null; --16:
2343 G_ADJUSTED_UNIT_PRICE_TBL(I) := null; --17:
2344 G_UPD_ADJUSTED_UNIT_PRICE_TBL(I) := null; --18:
2345 G_PROCESSED_FLAG_TBL(I) := null; --19:
2346 G_PRICE_FLAG_TBL(I) := 'Y'; --20: Price Flag can have 'Y' , 'N'(No pricing) , 'P'(Phase)
2347 G_LINE_ID_TBL(I) := I; --21: Order Line Id.
2348 G_PROCESSING_ORDER_TBL(I) := null; --22:
2349 G_PRICING_STATUS_CODE_tbl(I) := QP_PREQ_GRP.G_STATUS_UNCHANGED; --23:
2350 G_PRICING_STATUS_TEXT_tbl(I) := null; --24:
2351 G_ROUNDING_FLAG_TBL(I) := null; --25:
2352 G_ROUNDING_FACTOR_TBL(I) := null; --26:
2353 G_QUALIFIERS_EXIST_FLAG_TBL(I) := 'N'; --27:
2354 G_PRICING_ATTRS_EXIST_FLAG_TBL(I) := 'N'; --28:
2355 G_PRICE_LIST_ID_TBL(I) := L_PRICE_LIST; --29:???
2356 G_PL_VALIDATED_FLAG_TBL(I) := 'N'; --30:
2357 G_PRICE_REQUEST_CODE_TBL(I) := null; --31:
2358 G_usage_pricing_type_tbl(I) := QP_PREQ_GRP.G_REGULAR_USAGE_TYPE; --32: This can be 'REGULAR', 'AUTHORING', 'BILLING' --used in usage pricing
2359 --G_LINE_CATEGORY_tbl(I) := null; --33:
2360
2361 -- populate header attibutes/qualifiers
2362 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2363 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': building header attributes/qualifiers, summary line index='||I);
2364 END IF;
2365
2366 QP_ATTR_MAPPING_PUB.BUILD_CONTEXTS
2367 (p_request_type_code => p_request_type_code,
2368 p_line_index => I,
2369 p_pricing_type_code => 'H');
2370
2371 --X_LINE_INDEX_TBL.EXTEND;
2372 X_LINE_INDEX_TBL(I) := 'H:';
2373 IF p_price_list_id = FND_API.G_MISS_NUM THEN
2374 X_LINE_INDEX_TBL(I) := X_LINE_INDEX_TBL(I)||':NULL';
2375 ELSE
2376 X_LINE_INDEX_TBL(I) := X_LINE_INDEX_TBL(I)||':'||p_price_list_id;
2377 END IF;
2378 IF p_party_id = FND_API.G_MISS_NUM THEN
2379 X_LINE_INDEX_TBL(I) := X_LINE_INDEX_TBL(I)||':NULL';
2380 ELSE
2381 X_LINE_INDEX_TBL(I) := X_LINE_INDEX_TBL(I)||':'||p_party_id;
2382 END IF;
2383 IF p_cust_account_id = FND_API.G_MISS_NUM THEN
2384 X_LINE_INDEX_TBL(I) := X_LINE_INDEX_TBL(I)||':NULL';
2385 ELSE
2386 X_LINE_INDEX_TBL(I) := X_LINE_INDEX_TBL(I)||':'||p_cust_account_id;
2387 END IF;
2388
2389 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2390 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': inserting request lines into QP temp table, total lines='||G_LINE_INDEX_TBL.count);
2391 END IF;
2392
2393 l_ti := DBMS_UTILITY.GET_TIME;
2394 QP_PREQ_GRP.INSERT_LINES2
2395 (p_LINE_INDEX => G_LINE_INDEX_TBL,
2396 p_LINE_TYPE_CODE => G_LINE_TYPE_CODE_TBL,
2397 p_PRICING_EFFECTIVE_DATE => G_PRICING_EFFECTIVE_DATE_TBL,
2398 p_ACTIVE_DATE_FIRST => G_ACTIVE_DATE_FIRST_TBL,
2399 p_ACTIVE_DATE_FIRST_TYPE => G_ACTIVE_DATE_FIRST_TYPE_TBL,
2400 p_ACTIVE_DATE_SECOND => G_ACTIVE_DATE_SECOND_TBL,
2401 p_ACTIVE_DATE_SECOND_TYPE => G_ACTIVE_DATE_SECOND_TYPE_TBL,
2402 p_LINE_QUANTITY => P_LINE_QUANTITY_TBL,
2403 p_LINE_UOM_CODE => P_UOM_CODE_TBL,
2404 p_REQUEST_TYPE_CODE => G_REQUEST_TYPE_CODE_TBL,
2405 p_PRICED_QUANTITY => G_PRICED_QUANTITY_TBL,
2406 p_PRICED_UOM_CODE => P_UOM_CODE_TBL,
2407 p_CURRENCY_CODE => G_CURRENCY_CODE_TBL,
2408 p_UNIT_PRICE => G_UNIT_PRICE_TBL,
2409 p_PERCENT_PRICE => G_PERCENT_PRICE_TBL,
2410 p_UOM_QUANTITY => G_UOM_QUANTITY_TBL,
2411 p_ADJUSTED_UNIT_PRICE => G_ADJUSTED_UNIT_PRICE_TBL,
2412 p_UPD_ADJUSTED_UNIT_PRICE => G_UPD_ADJUSTED_UNIT_PRICE_TBL,
2413 p_PROCESSED_FLAG => G_PROCESSED_FLAG_TBL,
2414 p_PRICE_FLAG => G_PRICE_FLAG_TBL,
2415 p_LINE_ID => G_LINE_ID_TBL,
2416 p_PROCESSING_ORDER => G_PROCESSING_ORDER_TBL,
2417 p_PRICING_STATUS_CODE => G_PRICING_STATUS_CODE_TBL,
2418 p_PRICING_STATUS_TEXT => G_PRICING_STATUS_TEXT_TBL,
2419 p_ROUNDING_FLAG => G_ROUNDING_FLAG_TBL,
2420 p_ROUNDING_FACTOR => G_ROUNDING_FACTOR_TBL,
2421 p_QUALIFIERS_EXIST_FLAG => G_QUALIFIERS_EXIST_FLAG_TBL,
2422 p_PRICING_ATTRS_EXIST_FLAG => G_PRICING_ATTRS_EXIST_FLAG_TBL,
2423 p_PRICE_LIST_ID => G_PRICE_LIST_ID_TBL,
2424 p_VALIDATED_FLAG => G_PL_VALIDATED_FLAG_TBL,
2425 p_PRICE_REQUEST_CODE => G_PRICE_REQUEST_CODE_TBL,
2426 p_USAGE_PRICING_TYPE => G_USAGE_PRICING_TYPE_TBL,
2427 --p_line_category => G_LINE_CATEGORY_TBL,
2428 x_status_code => x_return_status,
2429 x_status_text => x_return_status_text);
2430
2434 END IF;
2431 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2432 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2433 IBE_UTIL.debug('Error in insert_lines '||x_return_status_text);
2435 END IF;
2436 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2437 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': done, total lines='||G_LINE_INDEX_TBL.COUNT);
2438 END IF;
2439
2440 -- populate related line records for service items
2441 IF (p_parentIndex_tbl IS NOT NULL AND p_parentIndex_tbl.count > 0 AND
2442 p_childindex_tbl IS NOT NULL AND p_parentIndex_tbl.count = p_childindex_tbl.count) THEN
2443 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2444 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': populating related lines...');
2445 END IF;
2446 FOR I IN p_parentIndex_tbl.FIRST..p_parentIndex_tbl.LAST LOOP
2447 G_RELATIONSHIP_TYPE_CODE(I) := QP_PREQ_GRP.G_SERVICE_LINE;
2448 G_LINE_DETAIL_INDEX_TBL(I) := NULL;
2449 G_RLTD_LINE_DETAIL_INDEX_TBL(I) := NULL; --gzhang 06/07/2003
2450 END LOOP;
2451
2452 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2453 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': inserting related lines...');
2454 END IF;
2455
2456 QP_PREQ_GRP.INSERT_RLTD_LINES2
2457 (p_LINE_INDEX => p_parentIndex_tbl,
2458 p_LINE_DETAIL_INDEX => G_LINE_DETAIL_INDEX_TBL,
2459 p_RELATIONSHIP_TYPE_CODE => G_RELATIONSHIP_TYPE_CODE,
2460 p_RELATED_LINE_INDEX => p_childindex_tbl,
2461 p_RELATED_LINE_DETAIL_INDEX => G_RLTD_LINE_DETAIL_INDEX_TBL,
2462 x_status_code => x_return_status,
2463 x_status_text => x_return_status_text);
2464
2465 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2466 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': related lines inserted, status='||x_return_status||', '||x_return_status_text);
2467 END IF;
2468
2469 END IF;
2470
2471 -- calling pricing engine
2472 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2473 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': Calling pricing engine...');
2474 END IF;
2475
2476 l_t1 := DBMS_UTILITY.GET_TIME;
2477 QP_PREQ_PUB.PRICE_REQUEST
2478 (p_control_rec => l_control_rec,
2479 x_return_status => x_return_status,
2480 x_return_status_text => x_return_status_text);
2481 l_t2 := DBMS_UTILITY.GET_TIME;
2482
2483 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2484 ibe_util.debug(g_pkg_name||'.'||L_API||': Duration of Price Request Time(s) ='|| (l_t2-l_t1)/100);
2485 END IF;
2486
2487 -- retrieve prices
2488 OPEN x_price_csr FOR --gzhang 01/30/03, bug#2774739
2489 SELECT LINE_ID, LINE_UOM_CODE, LINE_QUANTITY, LINE_UNIT_PRICE, ORDER_UOM_SELLING_PRICE, PRICING_STATUS_CODE, PRICING_STATUS_TEXT
2490 FROM QP_PREQ_LINES_TMP
2491 WHERE PRICING_STATUS_CODE=QP_PREQ_PUB.G_STATUS_UPDATED
2492 ORDER BY LINE_ID;
2493
2494 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2495 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||': RETURN');
2496 END IF;
2497
2498 EXCEPTION
2499 WHEN OTHERS THEN
2500 x_return_status := FND_API.G_RET_STS_ERROR;
2501 x_return_status_text :=SQLERRM;
2502
2503 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2504 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||':'||SQLERRM);
2505 END IF;
2506 END Price_Request;
2507
2508 PROCEDURE PRICE_REQUEST(
2509 p_price_list_id IN NUMBER := FND_API.G_MISS_NUM
2510 ,p_party_id IN NUMBER := FND_API.G_MISS_NUM
2511 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
2512 ,p_currency_code IN VARCHAR2
2513 ,p_minisite_id IN NUMBER := NULL
2514 ,p_item_tbl IN JTF_NUMBER_TABLE
2515 ,p_uom_code_tbl IN JTF_VARCHAR2_TABLE_100
2516 ,p_model_id_tbl IN JTF_NUMBER_TABLE
2517 ,p_line_quantity_tbl IN JTF_NUMBER_TABLE
2518 ,p_parentIndex_tbl IN JTF_NUMBER_TABLE := NULL
2519 ,p_childIndex_tbl IN JTF_NUMBER_TABLE := NULL
2520 ,p_request_type_code IN VARCHAR2 := 'ASO'
2521 ,p_pricing_event IN VARCHAR2
2522 ,x_price_csr OUT NOCOPY PRICE_REFCURSOR_TYPE
2523 ,x_line_index_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
2524 ,x_return_status OUT NOCOPY VARCHAR2
2525 ,x_return_status_text OUT NOCOPY VARCHAR2
2526 )
2527 IS
2528 L_API VARCHAR2(64);
2529 l_itmid_tbl QP_PREQ_GRP.NUMBER_TYPE;
2530 l_uom_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
2531 l_line_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE;
2532 l_parentIndex_tbl QP_PREQ_GRP.NUMBER_TYPE;
2533 l_childIndex_tbl QP_PREQ_GRP.NUMBER_TYPE;
2534 idx BINARY_INTEGER;
2535 l_total_lines INTEGER;
2536 l_pricing_flag BOOLEAN := TRUE;
2537 l_start_time NUMBER;
2538 l_end_time NUMBER;
2539 l_curr_time NUMBER;
2540 BEGIN
2541 L_API := 'PRICE_REQUEST';
2542 --IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': begin');
2543 l_start_time := DBMS_UTILITY.GET_TIME;
2544 IF p_item_tbl IS NOT NULL AND p_uom_code_tbl IS NOT NULL AND p_line_quantity_tbl IS NOT NULL THEN
2545 l_total_lines := p_item_tbl.COUNT;
2546 --IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': total line items: '||l_total_lines);
2547 IF l_total_lines = p_uom_code_tbl.COUNT AND l_total_lines = p_line_quantity_tbl.COUNT THEN
2548 --IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': converting JTF Tables to QP Tables - request lines ...');
2549 FOR idx IN 1..l_total_lines LOOP
2550 l_itmid_tbl(idx) := p_item_tbl(idx);
2551 l_uom_code_tbl(idx) := p_uom_code_tbl(idx);
2552 l_line_quantity_tbl(idx) := p_line_quantity_tbl(idx);
2553 END LOOP;
2554 l_curr_time := DBMS_UTILITY.GET_TIME;
2555 IF p_parentIndex_tbl IS NOT NULL AND p_childIndex_tbl IS NOT NULL THEN
2556 l_total_lines := p_parentIndex_tbl.count;
2557
2558 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2559 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': total related lines: '||l_total_lines);
2560 END IF;
2561
2562 IF l_total_lines >0 AND l_total_lines = p_childIndex_tbl.COUNT THEN
2563
2564 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2565 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': converting JTF Tables to QP Tables - related lines...');
2566 END IF;
2567
2568 FOR idx IN 1..l_total_lines LOOP
2569 l_parentIndex_tbl(idx) := p_parentIndex_tbl(idx);
2570 l_childIndex_tbl(idx) := p_childIndex_tbl(idx);
2571 END LOOP;
2572
2573 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2574 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': PL/SQL tables converted');
2575 END IF;
2576 ELSE
2577 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2578 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': related lines mismatched');
2579 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': total child indexes: '||p_childIndex_tbl.COUNT);
2580 END IF;
2581
2582 l_pricing_flag := FALSE;
2583 x_return_status := FND_API.G_RET_STS_ERROR;
2584 x_return_status_text := 'invalid related lines - mismatched input tables';
2585 END IF;
2586 ELSE
2587 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2588 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': no related lines found');
2589 END IF;
2590 END IF;
2591
2592 IF l_pricing_flag THEN
2593 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2594 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api||':sending request...');
2595 END IF;
2596
2597 l_curr_time := DBMS_UTILITY.GET_TIME;
2598 IBE_PRICE_PVT.PRICE_REQUEST(
2599 p_price_list_id => p_price_list_id,
2600 p_party_id => p_party_id,
2601 p_cust_account_id => p_cust_account_id,
2602 p_currency_code => p_currency_code,
2603 p_minisite_id => p_minisite_id,
2604 p_item_tbl => l_itmid_tbl,
2605 p_uom_code_tbl => l_uom_code_tbl,
2606 p_model_id_tbl => p_model_id_tbl,
2607 p_line_quantity_tbl => l_line_quantity_tbl,
2608 p_parentIndex_tbl => l_parentIndex_tbl,
2609 p_childIndex_tbl => l_childIndex_tbl,
2610 p_request_type_code => p_request_type_code,
2611 p_pricing_event => p_pricing_event,
2612 x_price_csr => x_price_csr,
2613 x_line_index_tbl=> x_line_index_tbl,
2614 x_return_status => x_return_status,
2615 x_return_status_text => x_return_status_text);
2616 l_curr_time := DBMS_UTILITY.GET_TIME;
2617
2618 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2619 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api||':done, return status = '||x_return_status||': '||x_return_status_text);
2620 END IF;
2621
2622 END IF;
2623 ELSE
2624 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2625 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': request lines mismatched');
2626 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': total uom codes: '||p_uom_code_tbl.COUNT);
2627 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': total line quantities: '||p_line_quantity_tbl.COUNT);
2628 END IF;
2629
2630 x_return_status := FND_API.G_RET_STS_ERROR;
2631 x_return_status_text := 'invalid request lines - mismatched input tables';
2632 END IF;
2633 ELSE
2634 IF p_item_tbl IS NULL THEN
2635 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2636 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||':p_item_tbl is NULL');
2637 END IF;
2638 END IF;
2639 IF p_uom_code_tbl IS NULL THEN
2640 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2641 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||':p_uom_code_tbl is NULL');
2642 END IF;
2643 END IF;
2644 IF p_line_quantity_tbl IS NULL THEN
2645 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2646 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||':p_line_quantity_tbl is NULL');
2647 END IF;
2648 END IF;
2649 x_return_status := FND_API.G_RET_STS_ERROR;
2650 x_return_status_text := 'invalid request lines - input table(s) is null';
2651 END IF;
2652 l_end_time := DBMS_UTILITY.GET_TIME;
2653
2654 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2655 IBE_UTIL.debug(G_PKG_NAME||'.'||L_API||': end, elapsed time (s) ='||(l_end_time-l_start_time)/100);
2656 END IF;
2657 EXCEPTION
2658 WHEN OTHERS THEN
2659 x_return_status := FND_API.G_RET_STS_ERROR;
2660 x_return_status_text :=SQLERRM;
2661
2662 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2663 IBE_UTIL.DEBUG(g_pkg_name||'.'||L_API||':'||SQLERRM);
2664 END IF;
2665 END Price_Request;
2666 END IBE_PRICE_PVT;