[Home] [Help]
PACKAGE BODY: APPS.QP_PRICE_LIST_PVT
Source
1 PACKAGE BODY QP_PRICE_LIST_PVT AS
2 /* $Header: OEXVPRLB.pls 120.2 2005/07/07 05:35:34 appldev ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_PRICE_LIST_PVT';
7
8 G_Fetch_Level NUMBER := 0;
9
10 -- Utility function called by Fetch)List_Price API.
11
12 FUNCTION Get_Sec_Price_List
13 ( p_price_list_id IN NUMBER )
14 RETURN NUMBER;
15
16 FUNCTION Get_Price_List_Name
17 ( p_price_list_id IN NUMBER )
18 RETURN VARCHAR2;
19
20 FUNCTION Get_Item_Description
21 ( p_item_id IN NUMBER )
22 RETURN VARCHAR2;
23
24 FUNCTION Get_Unit_Name
25 ( p_unit_code IN VARCHAR2 )
26 RETURN VARCHAR2;
27
28 -- Price_List
29
30 PROCEDURE Price_List
31 ( p_validation_level IN NUMBER
32 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
33 , p_PRICE_LIST_rec IN OE_Price_List_PUB.Price_List_Rec_Type
34 , p_old_PRICE_LIST_rec IN OE_Price_List_PUB.Price_List_Rec_Type
35 , x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
36 , x_old_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
37 )
38 IS
39 l_return_status VARCHAR2(1);
40 l_control_rec OE_GLOBALS.Control_Rec_Type;
41 l_PRICE_LIST_rec OE_Price_List_PUB.Price_List_Rec_Type := p_PRICE_LIST_rec;
42 l_p_PRICE_LIST_rec OE_Price_List_PUB.Price_List_Rec_Type := p_PRICE_LIST_rec;
43 l_old_PRICE_LIST_rec OE_Price_List_PUB.Price_List_Rec_Type := p_old_PRICE_LIST_rec;
44 BEGIN
45
46 -- Load API control record
47
48 l_control_rec := OE_GLOBALS.Init_Control_Rec
49 ( p_operation => l_PRICE_LIST_rec.operation
50 , p_control_rec => p_control_rec
51 );
52
53 -- Set record return status.
54
55 l_PRICE_LIST_rec.return_status := FND_API.G_RET_STS_SUCCESS;
56
57 -- Prepare record.
58
59 IF l_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
60
61 l_PRICE_LIST_rec.db_flag := FND_API.G_FALSE;
62
63 -- Set missing old record elements to NULL.
64
65 l_old_PRICE_LIST_rec :=
66 OE_Price_List_Util.Convert_Miss_To_Null (l_old_PRICE_LIST_rec);
67
68 ELSIF l_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_UPDATE
69 OR l_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_DELETE
70 THEN
71
72 l_PRICE_LIST_rec.db_flag := FND_API.G_TRUE;
73
74 -- Query Old if missing
75
76 IF l_old_PRICE_LIST_rec.Name = FND_API.G_MISS_CHAR
77 OR l_old_PRICE_LIST_rec.price_list_id = FND_API.G_MISS_NUM
78 THEN
79
80 l_old_PRICE_LIST_rec := OE_Price_List_Util.Query_Row
81 ( p_name => l_PRICE_LIST_rec.name
82 , p_price_list_id => l_PRICE_LIST_rec.price_list_id
83 );
84
85 ELSE
86
87 -- Set missing old record elements to NULL.
88
89 l_old_PRICE_LIST_rec :=
90 OE_Price_List_Util.Convert_Miss_To_Null (l_old_PRICE_LIST_rec);
91
92 END IF;
93
94 -- Complete new record from old
95
96 l_PRICE_LIST_rec := OE_Price_List_Util.Complete_Record
97 ( p_PRICE_LIST_rec => l_PRICE_LIST_rec
98 , p_old_PRICE_LIST_rec => l_old_PRICE_LIST_rec
99 );
100
101 END IF;
102
103 -- Attribute level validation.
104
105 IF l_control_rec.default_attributes
106 OR l_control_rec.change_attributes
107 THEN
108
109 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
110
111 OE_Validate_Price_List.Attributes
112 ( x_return_status => l_return_status
113 , p_PRICE_LIST_rec => l_PRICE_LIST_rec
114 , p_old_PRICE_LIST_rec => l_old_PRICE_LIST_rec
115 );
116
117 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
119 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
120 RAISE FND_API.G_EXC_ERROR;
121 END IF;
122
123 END IF;
124
125 END IF;
126
127 -- Clear dependent attributes.
128
129 IF l_control_rec.change_attributes THEN
130 l_p_PRICE_LIST_rec := l_PRICE_LIST_rec;
131 OE_Price_List_Util.Clear_Dependent_Attr
132 ( p_PRICE_LIST_rec => l_p_PRICE_LIST_rec
133 , p_old_PRICE_LIST_rec => l_old_PRICE_LIST_rec
134 , x_PRICE_LIST_rec => l_PRICE_LIST_rec
135 );
136
137 END IF;
138
139 -- Default missing attributes
140
141 IF l_control_rec.default_attributes
142 OR l_control_rec.change_attributes
143 THEN
144 l_p_PRICE_LIST_rec := l_PRICE_LIST_rec;
145 OE_Default_Price_List.Attributes
146 ( p_PRICE_LIST_rec => l_p_PRICE_LIST_rec
147 , x_PRICE_LIST_rec => l_PRICE_LIST_rec
148 );
149
150 END IF;
151
152 -- Apply attribute changes
153
154 IF l_control_rec.default_attributes
155 OR l_control_rec.change_attributes
156 THEN
157 l_p_PRICE_LIST_rec := l_PRICE_LIST_rec;
158 OE_Price_List_Util.Apply_Attribute_Changes
159 ( p_PRICE_LIST_rec => l_p_PRICE_LIST_rec
160 , p_old_PRICE_LIST_rec => l_old_PRICE_LIST_rec
161 , x_PRICE_LIST_rec => l_PRICE_LIST_rec
162 );
163
164 END IF;
165
166 -- Entity level validation.
167
168 IF l_control_rec.validate_entity THEN
169
170 IF l_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
171
172 OE_Validate_Price_List.Entity_Delete
173 ( x_return_status => l_return_status
174 , p_PRICE_LIST_rec => l_PRICE_LIST_rec
175 );
176
177 ELSE
178
179 OE_Validate_Price_List.Entity
180 ( x_return_status => l_return_status
181 , p_PRICE_LIST_rec => l_PRICE_LIST_rec
182 , p_old_PRICE_LIST_rec => l_old_PRICE_LIST_rec
183 );
184
185 END IF;
186
187 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
190 RAISE FND_API.G_EXC_ERROR;
191 END IF;
192
193 END IF;
194
195 -- Step 4. Write to DB
196
197 IF l_control_rec.write_to_db THEN
198
199 IF l_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
200
201 OE_Price_List_Util.Delete_Row
202 ( p_name => l_PRICE_LIST_rec.name
203 , p_price_list_id => l_PRICE_LIST_rec.price_list_id
204 );
205
206 ELSE
207
208 -- Get Who Information
209
210 l_PRICE_LIST_rec.last_update_date := SYSDATE;
211 l_PRICE_LIST_rec.last_updated_by := FND_GLOBAL.USER_ID;
212 l_PRICE_LIST_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
213
214 IF l_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
215
216 OE_Price_List_Util.Update_Row (l_PRICE_LIST_rec);
217
218 ELSIF l_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
219
220 l_PRICE_LIST_rec.creation_date := SYSDATE;
221 l_PRICE_LIST_rec.created_by := FND_GLOBAL.USER_ID;
222
223 OE_Price_List_Util.Insert_Row (l_PRICE_LIST_rec);
224
225 END IF;
226
227 END IF;
228
229 END IF;
230
231 -- Load OUT parameters
232
233 x_PRICE_LIST_rec := l_PRICE_LIST_rec;
234 x_old_PRICE_LIST_rec := l_old_PRICE_LIST_rec;
235
236 EXCEPTION
237
238 WHEN FND_API.G_EXC_ERROR THEN
239
240 l_PRICE_LIST_rec.return_status := FND_API.G_RET_STS_ERROR;
241 x_PRICE_LIST_rec := l_PRICE_LIST_rec;
242 x_old_PRICE_LIST_rec := l_old_PRICE_LIST_rec;
243 RAISE;
244
245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
246
247 l_PRICE_LIST_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
248 x_PRICE_LIST_rec := l_PRICE_LIST_rec;
249 x_old_PRICE_LIST_rec := l_old_PRICE_LIST_rec;
250
251 RAISE;
252
253 WHEN OTHERS THEN
254
255 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
256 THEN
257 OE_MSG_PUB.Add_Exc_Msg
258 ( G_PKG_NAME
259 , 'Price_List'
260 );
261 END IF;
262
263 l_PRICE_LIST_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264 x_PRICE_LIST_rec := l_PRICE_LIST_rec;
265 x_old_PRICE_LIST_rec := l_old_PRICE_LIST_rec;
266
267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
268
269 END Price_List;
270
271 -- Price_List_Lines
272
273 PROCEDURE Price_List_Lines
274 ( p_validation_level IN NUMBER
275 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
276 , p_PRICE_LIST_LINE_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type
277 , p_old_PRICE_LIST_LINE_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type
278 , x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
279 , x_old_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
280 )
281 IS
282 l_return_status VARCHAR2(1);
283 l_control_rec OE_GLOBALS.Control_Rec_Type;
284 l_PRICE_LIST_LINE_rec OE_Price_List_PUB.Price_List_Line_Rec_Type;
285 l_p_PRICE_LIST_LINE_rec OE_Price_List_PUB.Price_List_Line_Rec_Type;
286 l_PRICE_LIST_LINE_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type;
287 l_old_PRICE_LIST_LINE_rec OE_Price_List_PUB.Price_List_Line_Rec_Type;
288 l_old_PRICE_LIST_LINE_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type;
289 BEGIN
290
291 -- Init local table variables.
292
293 l_PRICE_LIST_LINE_tbl := p_PRICE_LIST_LINE_tbl;
294 l_old_PRICE_LIST_LINE_tbl := p_old_PRICE_LIST_LINE_tbl;
295
296 FOR I IN 1..l_PRICE_LIST_LINE_tbl.COUNT LOOP
297 BEGIN
298
299 -- Load local records.
300
301 l_PRICE_LIST_LINE_rec := l_PRICE_LIST_LINE_tbl(I);
302
303 IF l_old_PRICE_LIST_LINE_tbl.EXISTS(I) THEN
304 l_old_PRICE_LIST_LINE_rec := l_old_PRICE_LIST_LINE_tbl(I);
305 ELSE
306 l_old_PRICE_LIST_LINE_rec := OE_Price_List_PUB.G_MISS_PRICE_LIST_LINE_REC;
307 END IF;
308
309 -- Load API control record
310
311 l_control_rec := OE_GLOBALS.Init_Control_Rec
312 ( p_operation => l_PRICE_LIST_LINE_rec.operation
313 , p_control_rec => p_control_rec
314 );
315
316 -- Set record return status.
317
318 l_PRICE_LIST_LINE_rec.return_status := FND_API.G_RET_STS_SUCCESS;
319
320 -- Prepare record.
321
322 IF l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
323
324 l_PRICE_LIST_LINE_rec.db_flag := FND_API.G_FALSE;
325
326 -- Set missing old record elements to NULL.
327
328 l_old_PRICE_LIST_LINE_rec :=
329 OE_Price_List_Line_Util.Convert_Miss_To_Null (l_old_PRICE_LIST_LINE_rec);
330
331 ELSIF l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_UPDATE
332 OR l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_DELETE
333 THEN
334
335 l_PRICE_LIST_LINE_rec.db_flag := FND_API.G_TRUE;
336
337 -- Query Old if missing
338
339 IF l_old_PRICE_LIST_LINE_rec.price_list_line_id = FND_API.G_MISS_NUM
340 THEN
341
342 l_old_PRICE_LIST_LINE_rec := OE_Price_List_Line_Util.Query_Row
343 ( p_price_list_line_id => l_PRICE_LIST_LINE_rec.price_list_line_id
344 , p_price_list_id => l_PRICE_LIST_LINE_rec.price_list_id
345 );
346
347 ELSE
348
349 -- Set missing old record elements to NULL.
350
351 l_old_PRICE_LIST_LINE_rec :=
352 OE_Price_List_Line_Util.Convert_Miss_To_Null (l_old_PRICE_LIST_LINE_rec);
353
354 END IF;
355
356 -- Complete new record from old
357
358 l_PRICE_LIST_LINE_rec := OE_Price_List_Line_Util.Complete_Record
359 ( p_PRICE_LIST_LINE_rec => l_PRICE_LIST_LINE_rec
360 , p_old_PRICE_LIST_LINE_rec => l_old_PRICE_LIST_LINE_rec
361 );
362
363 END IF;
364
365 -- Attribute level validation.
366
367 IF l_control_rec.default_attributes
368 OR l_control_rec.change_attributes
369 THEN
370
371 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
372
373 OE_Validate_Price_List_Line.Attributes
374 ( x_return_status => l_return_status
375 , p_PRICE_LIST_LINE_rec => l_PRICE_LIST_LINE_rec
376 , p_old_PRICE_LIST_LINE_rec => l_old_PRICE_LIST_LINE_rec
377 );
378
379 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
381 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
382 RAISE FND_API.G_EXC_ERROR;
383 END IF;
384
385 END IF;
386
387 END IF;
388
389 -- Clear dependent attributes.
390
391 IF l_control_rec.change_attributes THEN
392 l_p_PRICE_LIST_LINE_rec := l_PRICE_LIST_LINE_rec;
393 OE_Price_List_Line_Util.Clear_Dependent_Attr
394 ( p_PRICE_LIST_LINE_rec => l_p_PRICE_LIST_LINE_rec
395 , p_old_PRICE_LIST_LINE_rec => l_old_PRICE_LIST_LINE_rec
396 , x_PRICE_LIST_LINE_rec => l_PRICE_LIST_LINE_rec
397 );
398
399 END IF;
400
401 -- Default missing attributes
402
403 IF l_control_rec.default_attributes
404 OR l_control_rec.change_attributes
405 THEN
406 l_p_PRICE_LIST_LINE_rec := l_PRICE_LIST_LINE_rec;
407 OE_Default_Price_List_Line.Attributes
408 ( p_PRICE_LIST_LINE_rec => l_p_PRICE_LIST_LINE_rec
409 , x_PRICE_LIST_LINE_rec => l_PRICE_LIST_LINE_rec
410 );
411
412 END IF;
413
414 -- Apply attribute changes
415
416 IF l_control_rec.default_attributes
417 OR l_control_rec.change_attributes
418 THEN
419 l_p_PRICE_LIST_LINE_rec := l_PRICE_LIST_LINE_rec;
420 OE_Price_List_Line_Util.Apply_Attribute_Changes
421 ( p_PRICE_LIST_LINE_rec => l_p_PRICE_LIST_LINE_rec
422 , p_old_PRICE_LIST_LINE_rec => l_old_PRICE_LIST_LINE_rec
423 , x_PRICE_LIST_LINE_rec => l_PRICE_LIST_LINE_rec
424 );
425
426 END IF;
427
428 -- Entity level validation.
429
430 IF l_control_rec.validate_entity THEN
431
432 IF l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
433
434 OE_Validate_Price_List_Line.Entity_Delete
435 ( x_return_status => l_return_status
436 , p_PRICE_LIST_LINE_rec => l_PRICE_LIST_LINE_rec
437 );
438
439 ELSE
440
441 OE_Validate_Price_List_Line.Entity
442 ( x_return_status => l_return_status
443 , p_PRICE_LIST_LINE_rec => l_PRICE_LIST_LINE_rec
444 , p_old_PRICE_LIST_LINE_rec => l_old_PRICE_LIST_LINE_rec
445 );
446
447 END IF;
448
449 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
451 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
452 RAISE FND_API.G_EXC_ERROR;
453 END IF;
454
455 END IF;
456
457 -- Step 4. Write to DB
458
459 IF l_control_rec.write_to_db THEN
460
461 IF l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
462
463 OE_Price_List_Line_Util.Delete_Row
464 ( p_price_list_line_id => l_PRICE_LIST_LINE_rec.price_list_line_id
465 );
466
467 ELSE
468
469 -- Get Who Information
470
471 l_PRICE_LIST_LINE_rec.last_update_date := SYSDATE;
472 l_PRICE_LIST_LINE_rec.last_updated_by := FND_GLOBAL.USER_ID;
473 l_PRICE_LIST_LINE_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
474
475 IF l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
476
477 OE_Price_List_Line_Util.Update_Row (l_PRICE_LIST_LINE_rec);
478
479 ELSIF l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
480
481 l_PRICE_LIST_LINE_rec.creation_date := SYSDATE;
482 l_PRICE_LIST_LINE_rec.created_by := FND_GLOBAL.USER_ID;
483
484 OE_Price_List_Line_Util.Insert_Row (l_PRICE_LIST_LINE_rec);
485
486 END IF;
487
488 END IF;
489
490 END IF;
491
492 -- Load tables.
493
494 l_PRICE_LIST_LINE_tbl(I) := l_PRICE_LIST_LINE_rec;
495 l_old_PRICE_LIST_LINE_tbl(I) := l_old_PRICE_LIST_LINE_rec;
496
497 -- For loop exception handler.
498
499
500 EXCEPTION
501
502 WHEN FND_API.G_EXC_ERROR THEN
503
504 l_PRICE_LIST_LINE_rec.return_status := FND_API.G_RET_STS_ERROR;
505 l_PRICE_LIST_LINE_tbl(I) := l_PRICE_LIST_LINE_rec;
506 l_old_PRICE_LIST_LINE_tbl(I) := l_old_PRICE_LIST_LINE_rec;
507
508 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
509
510 l_PRICE_LIST_LINE_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
511 l_PRICE_LIST_LINE_tbl(I) := l_PRICE_LIST_LINE_rec;
512 l_old_PRICE_LIST_LINE_tbl(I) := l_old_PRICE_LIST_LINE_rec;
513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
514
515 WHEN OTHERS THEN
516
517 l_PRICE_LIST_LINE_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
518 l_PRICE_LIST_LINE_tbl(I) := l_PRICE_LIST_LINE_rec;
519 l_old_PRICE_LIST_LINE_tbl(I) := l_old_PRICE_LIST_LINE_rec;
520
521 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
522 THEN
523 OE_MSG_PUB.Add_Exc_Msg
524 ( G_PKG_NAME
525 , 'Price_List_Lines'
526 );
527 END IF;
528
529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
530
531 END;
532 END LOOP;
533
534 -- Load OUT parameters
535
536 x_PRICE_LIST_LINE_tbl := l_PRICE_LIST_LINE_tbl;
537 x_old_PRICE_LIST_LINE_tbl := l_old_PRICE_LIST_LINE_tbl;
538
539 EXCEPTION
540
541 WHEN FND_API.G_EXC_ERROR THEN
542
543 RAISE;
544
545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
546
547
548 RAISE;
549
550 WHEN OTHERS THEN
551
552 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
553 THEN
554 OE_MSG_PUB.Add_Exc_Msg
555 ( G_PKG_NAME
556 , 'Price_List_Lines'
557 );
558 END IF;
559
560
561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
562
563 END Price_List_Lines;
564
565 -- Start of Comments
566 -- API name Process_Price_List
567 -- Type Private
568 -- Function
569 --
570 -- Pre-reqs
571 --
572 -- Parameters
573 --
574 -- Version Current version = 1.0
575 -- Initial version = 1.0
576 --
577 -- Notes
578 --
579 -- End of Comments
580
581 PROCEDURE Process_Price_List
582 ( p_api_version_number IN NUMBER
583 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
584 , p_commit IN VARCHAR2 := FND_API.G_FALSE
585 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
586 , p_control_rec IN OE_GLOBALS.Control_Rec_Type :=
587 OE_GLOBALS.G_MISS_CONTROL_REC
588 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
589 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
590 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
591 , p_PRICE_LIST_rec IN OE_Price_List_PUB.Price_List_Rec_Type :=
592 OE_Price_List_PUB.G_MISS_PRICE_LIST_REC
593 , p_old_PRICE_LIST_rec IN OE_Price_List_PUB.Price_List_Rec_Type :=
594 OE_Price_List_PUB.G_MISS_PRICE_LIST_REC
595 , p_PRICE_LIST_LINE_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type :=
596 OE_Price_List_PUB.G_MISS_PRICE_LIST_LINE_TBL
597 , p_old_PRICE_LIST_LINE_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type :=
598 OE_Price_List_PUB.G_MISS_PRICE_LIST_LINE_TBL
599 , x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
600 , x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
601 )
602 IS
603 l_api_version_number CONSTANT NUMBER := 1.0;
604 l_api_name CONSTANT VARCHAR2(30):= 'Process_Price_List';
605 l_return_status VARCHAR2(1);
606 l_control_rec OE_GLOBALS.Control_Rec_Type;
607 l_PRICE_LIST_rec OE_Price_List_PUB.Price_List_Rec_Type := p_PRICE_LIST_rec;
608 l_p_PRICE_LIST_rec OE_Price_List_PUB.Price_List_Rec_Type := p_PRICE_LIST_rec;
609 l_old_PRICE_LIST_rec OE_Price_List_PUB.Price_List_Rec_Type := p_old_PRICE_LIST_rec;
610 l_p_old_PRICE_LIST_rec OE_Price_List_PUB.Price_List_Rec_Type := p_old_PRICE_LIST_rec;
611 l_PRICE_LIST_LINE_rec OE_Price_List_PUB.Price_List_Line_Rec_Type;
612 l_PRICE_LIST_LINE_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type;
613 l_p_PRICE_LIST_LINE_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type;
614 l_old_PRICE_LIST_LINE_rec OE_Price_List_PUB.Price_List_Line_Rec_Type;
615 l_old_PRICE_LIST_LINE_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type;
616 l_p_old_PRICE_LIST_LINE_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type;
617 BEGIN
618
619 -- Standard call to check for call compatibility
620
621 IF NOT FND_API.Compatible_API_Call
622 ( l_api_version_number
623 , p_api_version_number
624 , l_api_name
625 , G_PKG_NAME
626 )
627 THEN
628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629 END IF;
630
631 -- Initialize message list.
632
633 IF FND_API.to_Boolean(p_init_msg_list) THEN
634 OE_MSG_PUB.initialize;
635 END IF;
636
637 -- Init local table variables.
638
639 l_PRICE_LIST_LINE_tbl := p_PRICE_LIST_LINE_tbl;
640 l_old_PRICE_LIST_LINE_tbl := p_old_PRICE_LIST_LINE_tbl;
641
642 -- Price_List
643 l_p_PRICE_LIST_rec := l_PRICE_LIST_rec;
644 l_p_old_PRICE_LIST_rec := l_old_PRICE_LIST_rec;
645 Price_List
646 ( p_validation_level => p_validation_level
647 , p_control_rec => p_control_rec
648 , p_PRICE_LIST_rec => l_p_PRICE_LIST_rec
649 , p_old_PRICE_LIST_rec => l_p_old_PRICE_LIST_rec
650 , x_PRICE_LIST_rec => l_PRICE_LIST_rec
651 , x_old_PRICE_LIST_rec => l_old_PRICE_LIST_rec
652 );
653
654 -- Perform PRICE_LIST group requests.
655
656 IF p_control_rec.process AND
657 (p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_ALL OR
658 p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_PRICE_LHEADER)
659 THEN
660
661 NULL;
662
663 END IF;
664
665 -- Load parent key if missing and operation is create.
666
667 FOR I IN 1..l_PRICE_LIST_LINE_tbl.COUNT LOOP
668
669 l_PRICE_LIST_LINE_rec := l_PRICE_LIST_LINE_tbl(I);
670
671 IF l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_CREATE
672 -- AND (l_PRICE_LIST_LINE_rec.name IS NULL OR
673 -- l_PRICE_LIST_LINE_rec.name = FND_API.G_MISS_CHAR)
674 AND (l_PRICE_LIST_LINE_rec.price_list_id IS NULL OR
675 l_PRICE_LIST_LINE_rec.price_list_id = FND_API.G_MISS_NUM)
676 THEN
677
678 -- Copy parent_id.
679
680 -- l_PRICE_LIST_LINE_tbl(I).name := l_PRICE_LIST_rec.price_list_id;
681 l_PRICE_LIST_LINE_tbl(I).price_list_id := l_PRICE_LIST_rec.price_list_id;
682 END IF;
683 END LOOP;
684
685 -- Price_List_Lines
686 l_p_PRICE_LIST_LINE_tbl := l_PRICE_LIST_LINE_tbl;
687 l_p_old_PRICE_LIST_LINE_tbl := l_old_PRICE_LIST_LINE_tbl;
688 Price_List_Lines
689 ( p_validation_level => p_validation_level
690 , p_control_rec => p_control_rec
691 , p_PRICE_LIST_LINE_tbl => l_p_PRICE_LIST_LINE_tbl
692 , p_old_PRICE_LIST_LINE_tbl => l_p_old_PRICE_LIST_LINE_tbl
693 , x_PRICE_LIST_LINE_tbl => l_PRICE_LIST_LINE_tbl
694 , x_old_PRICE_LIST_LINE_tbl => l_old_PRICE_LIST_LINE_tbl
695 );
696
697 -- Perform PRICE_LIST_LINE group requests.
698
699 IF p_control_rec.process AND
700 (p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_ALL OR
701 p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_PRICE_LLINE)
702 THEN
703
704 NULL;
705
706 END IF;
707
708 -- Step 6. Perform Object group logic
709
710 IF p_control_rec.process AND
711 p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_ALL
712 THEN
713
714 NULL;
715
716 END IF;
717
718 -- Done processing, load OUT parameters.
719
720 x_PRICE_LIST_rec := l_PRICE_LIST_rec;
721 x_PRICE_LIST_LINE_tbl := l_PRICE_LIST_LINE_tbl;
722
723 -- Clear API cache.
724
725 IF p_control_rec.clear_api_cache THEN
726
727 NULL;
728
729 END IF;
730
731 -- Clear API request tbl.
732
733 IF p_control_rec.clear_api_requests THEN
734
735 NULL;
736
737 END IF;
738
739 -- Derive return status.
740
741 x_return_status := FND_API.G_RET_STS_SUCCESS;
742
743 IF l_PRICE_LIST_rec.return_status = FND_API.G_RET_STS_ERROR THEN
744 x_return_status := FND_API.G_RET_STS_ERROR;
745 END IF;
746
747 FOR I IN 1..l_PRICE_LIST_LINE_tbl.COUNT LOOP
748
749 IF l_PRICE_LIST_LINE_tbl(I).return_status = FND_API.G_RET_STS_ERROR THEN
750 x_return_status := FND_API.G_RET_STS_ERROR;
751 END IF;
752
753 END LOOP;
754
755 -- Get message count and data
756
757 OE_MSG_PUB.Count_And_Get
758 ( p_count => x_msg_count
759 , p_data => x_msg_data
760 );
761
762
763 EXCEPTION
764
765 WHEN FND_API.G_EXC_ERROR THEN
766
767 x_return_status := FND_API.G_RET_STS_ERROR;
768
769 -- Get message count and data
770
771 OE_MSG_PUB.Count_And_Get
772 ( p_count => x_msg_count
773 , p_data => x_msg_data
774 );
775
776 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
777
778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
779
780 -- Get message count and data
781
782 OE_MSG_PUB.Count_And_Get
783 ( p_count => x_msg_count
784 , p_data => x_msg_data
785 );
786
787 WHEN OTHERS THEN
788
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
790
791 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
792 THEN
793 OE_MSG_PUB.Add_Exc_Msg
794 ( G_PKG_NAME
795 , 'Process_Price_List'
796 );
797 END IF;
798
799 -- Get message count and data
800
801 OE_MSG_PUB.Count_And_Get
802 ( p_count => x_msg_count
803 , p_data => x_msg_data
804 );
805
806 END Process_Price_List;
807
808 -- Start of Comments
809 -- API name Lock_Price_List
810 -- Type Private
811 -- Function
812 --
813 -- Pre-reqs
814 --
815 -- Parameters
816 --
817 -- Version Current version = 1.0
818 -- Initial version = 1.0
819 --
820 -- Notes
821 --
822 -- End of Comments
823
824 PROCEDURE Lock_Price_List
825 ( p_api_version_number IN NUMBER
826 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
827 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
828 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
829 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
830 , p_PRICE_LIST_rec IN OE_Price_List_PUB.Price_List_Rec_Type :=
831 OE_Price_List_PUB.G_MISS_PRICE_LIST_REC
832 , p_PRICE_LIST_LINE_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type :=
833 OE_Price_List_PUB.G_MISS_PRICE_LIST_LINE_TBL
834 , x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
835 , x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
836 )
837 IS
838 l_api_version_number CONSTANT NUMBER := 1.0;
839 l_api_name CONSTANT VARCHAR2(30):= 'Lock_Price_List';
840 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
841 l_PRICE_LIST_LINE_rec OE_Price_List_PUB.Price_List_Line_Rec_Type;
842 BEGIN
843
844 -- Standard call to check for call compatibility
845
846 IF NOT FND_API.Compatible_API_Call
847 ( l_api_version_number
848 , p_api_version_number
849 , l_api_name
850 , G_PKG_NAME
851 )
852 THEN
853 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
854 END IF;
855
856 -- Initialize message list.
857
858 IF FND_API.to_Boolean(p_init_msg_list) THEN
859 OE_MSG_PUB.initialize;
860 END IF;
861
862 -- Set Savepoint
863
864 SAVEPOINT Lock_Price_List_PVT;
865
866 -- Lock PRICE_LIST
867
868 IF p_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_LOCK THEN
869
870 OE_Price_List_Util.Lock_Row
871 ( p_PRICE_LIST_rec => p_PRICE_LIST_rec
872 , x_PRICE_LIST_rec => x_PRICE_LIST_rec
873 , x_return_status => l_return_status
874 );
875
876 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
877 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
878 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
879 RAISE FND_API.G_EXC_ERROR;
880 END IF;
881
882
883 END IF;
884
885 -- Lock PRICE_LIST_LINE
886
887 FOR I IN 1..p_PRICE_LIST_LINE_tbl.COUNT LOOP
888
889 IF p_PRICE_LIST_LINE_tbl(I).operation = OE_GLOBALS.G_OPR_LOCK THEN
890
891 OE_Price_List_Line_Util.Lock_Row
892 ( p_PRICE_LIST_LINE_rec => p_PRICE_LIST_LINE_tbl(I)
893 , x_PRICE_LIST_LINE_rec => l_PRICE_LIST_LINE_rec
894 , x_return_status => l_return_status
895 );
896
897 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
898 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
899 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
900 RAISE FND_API.G_EXC_ERROR;
901 END IF;
902
903 x_PRICE_LIST_LINE_tbl(I) := l_PRICE_LIST_LINE_rec;
904
905 END IF;
906
907 END LOOP;
908
909 -- Set return status
910
911 x_return_status := FND_API.G_RET_STS_SUCCESS;
912
913 -- Get message count and data
914
915 OE_MSG_PUB.Count_And_Get
916 ( p_count => x_msg_count
917 , p_data => x_msg_data
918 );
919
920
921 EXCEPTION
922
923 WHEN FND_API.G_EXC_ERROR THEN
924
925 x_return_status := FND_API.G_RET_STS_ERROR;
926
927 -- Get message count and data
928
929 OE_MSG_PUB.Count_And_Get
930 ( p_count => x_msg_count
931 , p_data => x_msg_data
932 );
933
934 -- Rollback
935
936 ROLLBACK TO Lock_Price_List_PVT;
937
938 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
939
940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
941
942 -- Get message count and data
943
944 OE_MSG_PUB.Count_And_Get
945 ( p_count => x_msg_count
946 , p_data => x_msg_data
947 );
948
949 -- Rollback
950
951 ROLLBACK TO Lock_Price_List_PVT;
952
953 WHEN OTHERS THEN
954
955 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
956
957 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
958 THEN
959 OE_MSG_PUB.Add_Exc_Msg
960 ( G_PKG_NAME
961 , 'Lock_Price_List'
962 );
963 END IF;
964
965 -- Get message count and data
966
967 OE_MSG_PUB.Count_And_Get
968 ( p_count => x_msg_count
969 , p_data => x_msg_data
970 );
971
972 -- Rollback
973
974 ROLLBACK TO Lock_Price_List_PVT;
975
976 END Lock_Price_List;
977
978 -- Start of Comments
979 -- API name Get_Price_List
980 -- Type Private
981 -- Function
982 --
983 -- Pre-reqs
984 --
985 -- Parameters
986 --
987 -- Version Current version = 1.0
988 -- Initial version = 1.0
989 --
990 -- Notes
991 --
992 -- End of Comments
993
994 PROCEDURE Get_Price_List
995 ( p_api_version_number IN NUMBER
996 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
997 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
998 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
999 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1000 , p_name IN VARCHAR2
1001 , p_price_list_id IN NUMBER
1002 , x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
1003 , x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
1004 )
1005 IS
1006 l_api_version_number CONSTANT NUMBER := 1.0;
1007 l_api_name CONSTANT VARCHAR2(30):= 'Get_Price_List';
1008 l_PRICE_LIST_rec OE_Price_List_PUB.Price_List_Rec_Type;
1009 l_PRICE_LIST_LINE_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type;
1010 BEGIN
1011
1012 -- Standard call to check for call compatibility
1013
1014 IF NOT FND_API.Compatible_API_Call
1015 ( l_api_version_number
1016 , p_api_version_number
1017 , l_api_name
1018 , G_PKG_NAME
1019 )
1020 THEN
1021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1022 END IF;
1023
1024 -- Initialize message list.
1025
1026 IF FND_API.to_Boolean(p_init_msg_list) THEN
1027 OE_MSG_PUB.initialize;
1028 END IF;
1029
1030 -- Get PRICE_LIST ( parent = PRICE_LIST )
1031
1032 l_PRICE_LIST_rec := OE_Price_List_Util.Query_Row
1033 ( p_name => p_name
1034 , p_price_list_id => p_price_list_id
1035 );
1036
1037 -- Get PRICE_LIST_LINE ( parent = PRICE_LIST )
1038
1039 l_PRICE_LIST_LINE_tbl := OE_Price_List_Line_Util.Query_Rows
1040 ( p_price_list_id => p_price_list_id);
1041
1042 -- l_PRICE_LIST_LINE_rec := OE_Price_List_Line_Util.Query_Row
1043 -- ( p_price_list_line_id => l_PRICE_LIST_LINE_rec.price_list_line_id);
1044
1045 -- Load out parameters
1046
1047 x_PRICE_LIST_rec := l_PRICE_LIST_rec;
1048 x_PRICE_LIST_LINE_tbl := l_PRICE_LIST_LINE_tbl;
1049
1050 -- Set return status
1051
1052 x_return_status := FND_API.G_RET_STS_SUCCESS;
1053
1054 -- Get message count and data
1055
1056 OE_MSG_PUB.Count_And_Get
1057 ( p_count => x_msg_count
1058 , p_data => x_msg_data
1059 );
1060
1061
1062 EXCEPTION
1063
1064 WHEN FND_API.G_EXC_ERROR THEN
1065
1066 x_return_status := FND_API.G_RET_STS_ERROR;
1067
1068 -- Get message count and data
1069
1070 OE_MSG_PUB.Count_And_Get
1071 ( p_count => x_msg_count
1072 , p_data => x_msg_data
1073 );
1074
1075 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1076
1077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1078
1079 -- Get message count and data
1080
1081 OE_MSG_PUB.Count_And_Get
1082 ( p_count => x_msg_count
1083 , p_data => x_msg_data
1084 );
1085
1086 WHEN OTHERS THEN
1087
1088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1089
1090 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1091 THEN
1092 OE_MSG_PUB.Add_Exc_Msg
1093 ( G_PKG_NAME
1094 , 'Get_Price_List'
1095 );
1096 END IF;
1097
1098 -- Get message count and data
1099
1100 OE_MSG_PUB.Count_And_Get
1101 ( p_count => x_msg_count
1102 , p_data => x_msg_data
1103 );
1104
1105 END Get_Price_List;
1106
1107 PROCEDURE Fetch_List_Price
1108 ( p_api_version_number IN NUMBER ,
1109 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1110 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1111 p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
1112 p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER ,
1113 p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
1114 p_price_list_id IN NUMBER := NULL ,
1115 p_inventory_item_id IN NUMBER := NULL ,
1116 p_unit_code IN VARCHAR2 := NULL ,
1117 p_service_duration IN NUMBER := NULL ,
1118 p_item_type_code IN VARCHAR2 := NULL ,
1119 p_prc_method_code IN VARCHAR2 := NULL ,
1120 p_pricing_attribute1 IN VARCHAR2 := NULL ,
1121 p_pricing_attribute2 IN VARCHAR2 := NULL ,
1122 p_pricing_attribute3 IN VARCHAR2 := NULL ,
1123 p_pricing_attribute4 IN VARCHAR2 := NULL ,
1124 p_pricing_attribute5 IN VARCHAR2 := NULL ,
1125 p_pricing_attribute6 IN VARCHAR2 := NULL ,
1126 p_pricing_attribute7 IN VARCHAR2 := NULL ,
1127 p_pricing_attribute8 IN VARCHAR2 := NULL ,
1128 p_pricing_attribute9 IN VARCHAR2 := NULL ,
1129 p_pricing_attribute10 IN VARCHAR2 := NULL ,
1130 p_pricing_attribute11 IN VARCHAR2 := NULL ,
1131 p_pricing_attribute12 IN VARCHAR2 := NULL ,
1132 p_pricing_attribute13 IN VARCHAR2 := NULL ,
1133 p_pricing_attribute14 IN VARCHAR2 := NULL ,
1134 p_pricing_attribute15 IN VARCHAR2 := NULL ,
1135 p_base_price IN NUMBER := NULL ,
1136 p_pricing_date IN DATE := NULL ,
1137 p_fetch_attempts IN NUMBER := G_PRC_LST_DEF_ATTEMPTS ,
1138 p_price_list_id_out OUT NOCOPY /* file.sql.39 change */ NUMBER ,
1139 p_prc_method_code_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
1140 p_list_price OUT NOCOPY /* file.sql.39 change */ NUMBER ,
1141 p_list_percent OUT NOCOPY /* file.sql.39 change */ NUMBER ,
1142 p_rounding_factor OUT NOCOPY /* file.sql.39 change */ NUMBER
1143 )
1144 IS
1145 l_api_version_number CONSTANT NUMBER := 1.0;
1146 l_api_name CONSTANT VARCHAR2(30):= 'Fetch_List_Price';
1147 l_return_status VARCHAR2(1);
1148 l_fetch_attempts NUMBER := p_fetch_attempts;
1149 l_validation_error BOOLEAN := FALSE;
1150 l_prc_method_code VARCHAR2(4) := p_prc_method_code ;
1151 l_price_list_id NUMBER := p_price_list_id ;
1152 l_prc_method_code_out VARCHAR2(4) := NULL ;
1153 l_list_price NUMBER := NULL ;
1154 l_list_percent NUMBER := NULL ;
1155 l_rounding_factor NUMBER := NULL ;
1156 l_pricing_date DATE := NVL(p_pricing_date, SYSDATE);
1157 l_percent_price NUMBER := NULL;
1158 l_pricing_attribute1 VARCHAR2(150) := p_pricing_attribute1;
1159 l_pricing_attribute2 VARCHAR2(150) := p_pricing_attribute2;
1160 l_pricing_attribute3 VARCHAR2(150) := p_pricing_attribute3;
1161 l_pricing_attribute4 VARCHAR2(150) := p_pricing_attribute4;
1162 l_pricing_attribute5 VARCHAR2(150) := p_pricing_attribute5;
1163 l_pricing_attribute6 VARCHAR2(150) := p_pricing_attribute6;
1164 l_pricing_attribute7 VARCHAR2(150) := p_pricing_attribute7;
1165 l_pricing_attribute8 VARCHAR2(150) := p_pricing_attribute8;
1166 l_pricing_attribute9 VARCHAR2(150) := p_pricing_attribute9;
1167 l_pricing_attribute10 VARCHAR2(150) := p_pricing_attribute10;
1168 l_pricing_attribute11 VARCHAR2(150) := p_pricing_attribute11;
1169 l_pricing_attribute12 VARCHAR2(150) := p_pricing_attribute12;
1170 l_pricing_attribute13 VARCHAR2(150) := p_pricing_attribute13;
1171 l_pricing_attribute14 VARCHAR2(150) := p_pricing_attribute14;
1172 l_pricing_attribute15 VARCHAR2(150) := p_pricing_attribute15;
1173
1174
1175 fname varchar2(80);
1176
1177
1178 BEGIN
1179
1180 p_price_list_id_out :=1000;
1181 p_prc_method_code_out :='AMT';
1182 p_list_price :=10.25;
1183 p_list_percent :=2;
1184 p_rounding_factor :=2;
1185 return;
1186
1187 --For backward compatibility, we need to convert AMNT to AMT and etc.
1188 IF l_prc_method_code = 'AMNT' THEN l_prc_method_code := 'AMT'; END IF;
1189 IF l_prc_method_code = 'PERC' THEN l_prc_method_code := '%'; END IF;
1190
1191
1192 --Make sure there is no MISS Charaters
1193
1194 IF p_pricing_attribute1 = FND_API.G_MISS_CHAR THEN
1195 l_pricing_attribute1 := NULL;
1196 END IF;
1197
1198 IF (p_pricing_attribute2 = FND_API.G_MISS_CHAR) THEN
1199 l_pricing_attribute2:=NULL;
1200 END IF;
1201
1202 IF (p_pricing_attribute3 = FND_API.G_MISS_CHAR) THEN
1203 l_pricing_attribute3:=NULL;
1204 END IF;
1205
1206 IF (p_pricing_attribute4 = FND_API.G_MISS_CHAR) THEN
1207 l_pricing_attribute4:=NULL;
1208 END IF;
1209
1210 IF (p_pricing_attribute5 = FND_API.G_MISS_CHAR) THEN
1211 l_pricing_attribute5:=NULL;
1212 END IF;
1213
1214 IF (p_pricing_attribute6 = FND_API.G_MISS_CHAR) THEN
1215 l_pricing_attribute6:=NULL;
1216 END IF;
1217
1218 IF (p_pricing_attribute7 = FND_API.G_MISS_CHAR) THEN
1219 l_pricing_attribute7:=NULL;
1220 END IF;
1221
1222 IF (p_pricing_attribute8 = FND_API.G_MISS_CHAR) THEN
1223 l_pricing_attribute8:=NULL;
1224 END IF;
1225
1226 IF (p_pricing_attribute9 = FND_API.G_MISS_CHAR) THEN
1227 l_pricing_attribute9:=NULL;
1228 END IF;
1229
1230 IF (p_pricing_attribute10 = FND_API.G_MISS_CHAR) THEN
1231 l_pricing_attribute10 := NULL;
1232 END IF;
1233
1234 IF (p_pricing_attribute11 = FND_API.G_MISS_CHAR) THEN
1235 l_pricing_attribute11 := NULL;
1236 END IF;
1237
1238 IF (p_pricing_attribute12 = FND_API.G_MISS_CHAR) THEN
1239 l_pricing_attribute12:=NULL;
1240 END IF;
1241
1242 IF (p_pricing_attribute13 = FND_API.G_MISS_CHAR) THEN
1243 l_pricing_attribute13:=NULL;
1244 END IF;
1245
1246 IF (p_pricing_attribute14 = FND_API.G_MISS_CHAR) THEN
1247 l_pricing_attribute14:=NULL;
1248 END IF;
1249
1250 IF (p_pricing_attribute15 = FND_API.G_MISS_CHAR) THEN
1251 l_pricing_attribute15:=NULL;
1252 END IF;
1253
1254 -- Standard call to check for call compatibility
1255 --oe_debug_pub.initialize;
1256 oe_debug_pub.debug_on;
1257 fname := oe_debug_pub.set_debug_mode('FILE');
1258 oe_debug_pub.add('Debugging is on in Procedure Fetch List Price ');
1259 oe_debug_pub.add('Begin Kanan Fetch_List_Price');
1260 oe_debug_pub.add('============================');
1261 oe_debug_pub.add(' p_validation_level: '|| p_validation_level);
1262 oe_debug_pub.add('price_list_id: '||p_price_list_id);
1263 oe_debug_pub.add('inventory_item_id: '|| p_inventory_item_id);
1264 oe_debug_pub.add('Unit code :'||p_unit_code);
1265 oe_debug_pub.add('p_service_duration :'|| p_service_duration);
1266 oe_debug_pub.add(' p_item_type_code :'|| p_item_type_code);
1267 oe_debug_pub.add('Base Price : '|| p_base_price);
1268 oe_debug_pub.add(' p_pricing_date: '|| p_pricing_date);
1269 oe_debug_pub.add('p_fetch_attempts: '||p_fetch_attempts );
1270 oe_debug_pub.add('p_prc_method_code: '||p_prc_method_code);
1271 oe_debug_pub.add(' p_pricing_attribute1: '|| p_pricing_attribute1);
1272 oe_debug_pub.add(' p_pricing_attribute2: '|| p_pricing_attribute2);
1273 oe_debug_pub.add(' p_pricing_attribute3: '|| p_pricing_attribute3);
1274 oe_debug_pub.add(' p_pricing_attribute4: '|| p_pricing_attribute4);
1275
1276
1277 --DBMS_output.Put_line('Begin Kanan Fetch_List_Price');
1278 --DBMS_output.Put_line('============================');
1279 --DBMS_OUTPUT.PUT_LINE('p_api_version_number: '||p_api_version_number);
1280 --DBMS_OUTPUT.PUT_LINE(' p_validation_level: '|| p_validation_level);
1281 --DBMS_OUTPUT.PUT_LINE('p_init_msg_list: '||p_init_msg_list);
1282 --DBMS_OUTPUT.PUT_LINE('price_list_id: '||p_price_list_id);
1283 --DBMS_OUTPUT.PUT_LINE('inventory_item_id: '|| p_inventory_item_id);
1284 --DBMS_OUTPUT.PUT_LINE('Unit code :'||p_unit_code);
1285 --DBMS_OUTPUT.PUT_LINE('p_service_duration :'|| p_service_duration);
1286 --DBMS_OUTPUT.PUT_LINE(' p_item_type_code :'|| p_item_type_code);
1287 --DBMS_OUTPUT.PUT_LINE('Base Price : '|| p_base_price);
1288 --DBMS_OUTPUT.PUT_LINE(' p_pricing_date: '|| p_pricing_date);
1289 --DBMS_OUTPUT.PuT_LINE('p_fetch_attempts: '||p_fetch_attempts );
1290 --DBMS_OUTPUT.PuT_LINE('p_prc_method_code: '||p_prc_method_code);
1291 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute1: '|| p_pricing_attribute1);
1292 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute2: '|| p_pricing_attribute2);
1293 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute3: '|| p_pricing_attribute3);
1294 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute4: '|| p_pricing_attribute4);
1295 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute5: '|| p_pricing_attribute5);
1296 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute6: '|| p_pricing_attribute6);
1297 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute7: '|| p_pricing_attribute7);
1298 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute8: '|| p_pricing_attribute8);
1299 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute9: '|| p_pricing_attribute9);
1300 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute10: '|| p_pricing_attribute10);
1301 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute11: '|| p_pricing_attribute11);
1302 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute12: '|| p_pricing_attribute12);
1303 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute13: '|| p_pricing_attribute13);
1304 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute14: '|| p_pricing_attribute14);
1305 --DBMS_OUTPUT.PUT_LINE(' p_pricing_attribute15: '|| p_pricing_attribute15);
1306
1307
1308
1309
1310 IF NOT FND_API.Compatible_API_Call
1311 ( l_api_version_number,
1312 p_api_version_number,
1313 l_api_name ,
1314 G_PKG_NAME )
1315 THEN
1316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1317 END IF;
1318
1319 -- Initialize message list if p_init_msg_list is set to TRUE
1320
1321 IF FND_API.to_Boolean(p_init_msg_list) THEN
1322
1323 OE_MSG_PUB.initialize;
1324
1325 END IF;
1326
1327 -- Initialize p_return_status
1328
1329 p_return_status := FND_API.G_RET_STS_SUCCESS;
1330
1331 -- Validate Input. Start with mandatory validation.
1332
1333 -- Fetch_attempts can not be greater that max attempts allowed
1334
1335 --DBMS_Output.Put_LIne(Substr('P_Fetch_Attempts = ' ||
1336 --To_Char(p_Fetch_attempts) || ' G_PRC_LST_MAX_ATTEMPTS = '
1337 --|| To_Char(G_PRC_LST_MAX_ATTEMPTS),1,250));
1338 IF p_fetch_attempts > G_PRC_LST_MAX_ATTEMPTS THEN
1339
1340 l_validation_error := TRUE;
1341
1342 FND_MESSAGE.SET_NAME('OE','OE_PRC_LIST_INVALID_FETCH_ATTEMPTS');
1343 FND_MESSAGE.SET_TOKEN('PASSED_FETCH_ATTEMPTS',p_fetch_attempts);
1344 FND_MESSAGE.SET_TOKEN('MAX_FETCH_ATTEMPTS',G_PRC_LST_MAX_ATTEMPTS);
1345 OE_MSG_PUB.Add;
1346
1347 END IF;
1348
1349 -- Validation that can be turned off through the use of
1350 -- validation level.
1351
1352 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
1353
1354 -- Validate :
1355 -- price_list_id
1356 -- item_id
1357 -- unit_code
1358 -- item_type_code
1359 -- fetch_attempts
1360 -- This code needs to be added in the future if we provide a
1361 -- public API.
1362
1363 NULL;
1364
1365 END IF;
1366
1367 IF l_validation_error THEN
1368 --DBMS_output.put_line('validation_error');
1369 RAISE FND_API.G_EXC_ERROR;
1370 END IF;
1371
1372 -- Check required parameters.
1373
1374 IF p_price_list_id IS NULL
1375 OR p_inventory_item_id IS NULL
1376 OR p_unit_code IS NULL
1377 THEN
1378 --DBMS_Output.put_line('returning since price_list_id, inventory_id, unit_code is null');
1379 RETURN;
1380 END IF;
1381
1382 -- Set the G_Fetch_level. Since this API calls itself
1383 -- recursively, this variable indicates the call level. It is
1384 -- used forward on.
1385
1386 G_Fetch_Level := G_Fetch_Level + 1;
1387
1388 -- Fetch list price.
1389
1390 -- There are two fetch statements :
1391 -- 1. General statement that drives on item id.
1392 -- 2. Special case for Oracle USA where we drive on item_id
1393 -- and pricing_attribute2
1394 -- In case a customer doesn't have an index on
1395 -- PRICING_ATTRIBUTE2, it shouldn't be aproblem because
1396 -- the statement will still drive on item_id.
1397 --
1398 -- The ROWNUM = 1 condition is to accomodate the case where there
1399 -- is more than one active price list line that meets the select
1400 -- criteria. Inherited from release 9.
1401
1402
1403 -- Block encapsulating the fetch statements to handle the case
1404 -- where no rows are found. The reason it is not handled in the
1405 -- API exception handler, is that the handler itself may raise
1406 -- exceptions that should be handled by the API exception
1407 -- handler. are no rows.
1408
1409 BEGIN
1410
1411 IF p_pricing_attribute2 IS NULL THEN
1412
1413 -- Debug info
1414 /*
1415
1416 OE_MSG_PUB.Add_Exc_Msg
1417 ( p_error_text => 'pricing_attribute2 is null' );
1418 */
1419
1420 -- Fecth driving on item_id
1421 /* ordered use_nl(OELST OELIN) index(OELST OE_PRICE_LISTS_U1) index(OELIN OE_PRICE_LIST_LINES_N1) */
1422
1423 SELECT
1424 QPLST.ROUNDING_FACTOR
1425 , QPLIN.ARITHMETIC_OPERATOR
1426 , QPLIN.LIST_PRICE
1427 , QPLIN.PERCENT_PRICE
1428 INTO
1429 l_rounding_factor
1430 , l_prc_method_code_out
1431 , l_list_price
1432 , l_percent_price
1433 FROM QP_LIST_HEADERS QPLST
1434 , QP_LIST_LINES QPLIN
1435 , QP_PRICING_ATTRIBUTES QPPRC
1436 WHERE QPLST.LIST_HEADER_ID = QPLIN.LIST_HEADER_ID
1437 AND QPLIN.LIST_LINE_ID = QPPRC.LIST_LINE_ID
1438 AND QPLIN.LIST_LINE_TYPE_CODE = G_PRC_PRICE_LIST_LINE
1439 AND DECODE(QPLIN.LIST_PRICE,NULL,G_PRC_METHOD_PERCENT,G_PRC_METHOD_AMOUNT) =
1440 NVL( l_prc_method_code,DECODE(QPLIN.LIST_PRICE,NULL,G_PRC_METHOD_PERCENT,G_PRC_METHOD_AMOUNT) )
1441 AND TRUNC(L_PRICING_DATE)
1442 BETWEEN NVL( QPLIN.START_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
1443 AND NVL( QPLIN.END_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
1444 AND Decode(l_pricing_attribute1,Null,
1445 QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1446 p_inventory_item_id, p_unit_code,
1447 --the following two attributes must
1448 -- not be hardcoded...Kannan..10/01/99
1449 'ALL',
1450 'ALL',
1451 'ALL',
1452 Null,
1453 qplin.list_line_id,
1454 qplin.list_header_id),
1455 QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist (
1456 'ITEM', 'PRICING_ATTRIBUTE1',
1457 p_inventory_item_id, p_unit_code,
1458 --the following two attributes must
1459 -- not be hardcoded...Kannan..10/01/99
1460 'PRICING_ATTRIBUTE_CONTEXT',
1461 'PRICING_ATTRIBUTE1',
1462 l_pricing_attribute1,
1463 l_pricing_attribute1,
1464 qplin.list_line_id,
1465 qplin.list_header_id)) = 'Y'
1466 AND Decode(l_pricing_attribute2,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1467 p_inventory_item_id, p_unit_code,
1468 --the following two attributes must
1469 -- not be hardcoded...Kannan..10/01/99
1470 'PRICING_ATTRIBUTE_CONTEXT',
1471 'PRICING_ATTRIBUTE2',
1472 l_pricing_attribute2,
1473 l_pricing_attribute2,
1474 qplin.list_line_id,
1475 qplin.list_header_id)) = 'Y'
1476 AND Decode(l_pricing_attribute3,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1477 p_inventory_item_id, p_unit_code,
1478 --the following two attributes must
1479 -- not be hardcoded...Kannan..10/01/99
1480 'PRICING_ATTRIBUTE_CONTEXT',
1481 'PRICING_ATTRIBUTE3',
1482 l_pricing_attribute3,
1483 l_pricing_attribute3,
1484 qplin.list_line_id,
1485 qplin.list_header_id)) = 'Y'
1486 AND Decode(l_pricing_attribute4,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1487 p_inventory_item_id, p_unit_code,
1488 --the following two attributes must
1489 -- not be hardcoded...Kannan..10/01/99
1490 'PRICING_ATTRIBUTE_CONTEXT',
1491 'PRICING_ATTRIBUTE4',
1492 l_pricing_attribute4,
1493 l_pricing_attribute4,
1494 qplin.list_line_id,
1495 qplin.list_header_id)) = 'Y'
1496 AND Decode(l_pricing_attribute5,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1497 p_inventory_item_id, p_unit_code,
1498 --the following two attributes must
1499 -- not be hardcoded...Kannan..10/01/99
1500 'PRICING_ATTRIBUTE_CONTEXT',
1501 'PRICING_ATTRIBUTE5',
1502 l_pricing_attribute5,
1503 l_pricing_attribute5,
1504 qplin.list_line_id,
1505 qplin.list_header_id)) = 'Y'
1506 AND Decode(l_pricing_attribute6,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1507 p_inventory_item_id, p_unit_code,
1508 --the following two attributes must
1509 -- not be hardcoded...Kannan..10/01/99
1510 'PRICING_ATTRIBUTE_CONTEXT',
1511 'PRICING_ATTRIBUTE6',
1512 l_pricing_attribute6,
1513 l_pricing_attribute6,
1514 qplin.list_line_id,
1515 qplin.list_header_id)) = 'Y'
1516 AND Decode(l_pricing_attribute7,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1517 p_inventory_item_id, p_unit_code,
1518 --the following two attributes must
1519 -- not be hardcoded...Kannan..10/01/99
1520 'PRICING_ATTRIBUTE_CONTEXT',
1521 'PRICING_ATTRIBUTE7',
1522 l_pricing_attribute7,
1523 l_pricing_attribute7,
1524 qplin.list_line_id,
1525 qplin.list_header_id)) = 'Y'
1526 AND Decode(l_pricing_attribute8,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1527 p_inventory_item_id, p_unit_code,
1528 --the following two attributes must
1529 -- not be hardcoded...Kannan..10/01/99
1530 'PRICING_ATTRIBUTE_CONTEXT',
1531 'PRICING_ATTRIBUTE8',
1532 l_pricing_attribute8,
1533 l_pricing_attribute8,
1534 qplin.list_line_id,
1535 qplin.list_header_id)) = 'Y'
1536 AND Decode(l_pricing_attribute9,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1537 p_inventory_item_id, p_unit_code,
1538 --the following two attributes must
1539 -- not be hardcoded...Kannan..10/01/99
1540 'PRICING_ATTRIBUTE_CONTEXT',
1541 'PRICING_ATTRIBUTE9',
1542 l_pricing_attribute9,
1543 l_pricing_attribute9,
1544 qplin.list_line_id,
1545 qplin.list_header_id)) = 'Y'
1546 AND Decode(l_pricing_attribute10,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1547 p_inventory_item_id, p_unit_code,
1548 --the following two attributes must
1549 -- not be hardcoded...Kannan..10/01/99
1550 'PRICING_ATTRIBUTE_CONTEXT',
1551 'PRICING_ATTRIBUTE10',
1552 l_pricing_attribute10,
1553 l_pricing_attribute10,
1554 qplin.list_line_id,
1555 qplin.list_header_id)) = 'Y'
1556 AND Decode(l_pricing_attribute11,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1557 p_inventory_item_id, p_unit_code,
1558 --the following two attributes must
1559 -- not be hardcoded...Kannan..10/01/99
1560 'PRICING_ATTRIBUTE_CONTEXT',
1561 'PRICING_ATTRIBUTE11',
1562 l_pricing_attribute11,
1563 l_pricing_attribute11,
1564 qplin.list_line_id,
1565 qplin.list_header_id)) = 'Y'
1566 AND Decode(l_pricing_attribute12,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1567 p_inventory_item_id, p_unit_code,
1568 --the following two attributes must
1569 -- not be hardcoded...Kannan..10/01/99
1570 'PRICING_ATTRIBUTE_CONTEXT',
1571 'PRICING_ATTRIBUTE12',
1572 l_pricing_attribute12,
1573 l_pricing_attribute12,
1574 qplin.list_line_id,
1575 qplin.list_header_id)) = 'Y'
1576 AND Decode(l_pricing_attribute13,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1577 p_inventory_item_id, p_unit_code,
1578 --the following two attributes must
1579 -- not be hardcoded...Kannan..10/01/99
1580 'PRICING_ATTRIBUTE_CONTEXT',
1581 'PRICING_ATTRIBUTE13',
1582 l_pricing_attribute13,
1583 l_pricing_attribute13,
1584 qplin.list_line_id,
1585 qplin.list_header_id)) = 'Y'
1586 AND Decode(l_pricing_attribute14,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1587 p_inventory_item_id, p_unit_code,
1588 --the following two attributes must
1589 -- not be hardcoded...Kannan..10/01/99
1590 'PRICING_ATTRIBUTE_CONTEXT',
1591 'PRICING_ATTRIBUTE14',
1592 l_pricing_attribute14,
1593 l_pricing_attribute14,
1594 qplin.list_line_id,
1595 qplin.list_header_id)) = 'Y'
1596 AND Decode(l_pricing_attribute15,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
1597 p_inventory_item_id, p_unit_code,
1598 --the following two attributes must
1599 -- not be hardcoded...Kannan..10/01/99
1600 'PRICING_ATTRIBUTE_CONTEXT',
1601 'PRICING_ATTRIBUTE15',
1602 l_pricing_attribute15,
1603 l_pricing_attribute15,
1604 qplin.list_line_id,
1605 qplin.list_header_id)) = 'Y'
1606 AND QPLST.LIST_HEADER_ID = p_price_list_id
1607 AND TRUNC(L_PRICING_DATE)
1608 BETWEEN NVL( QPLST.START_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
1609 AND NVL( QPLST.END_DATE_ACTIVE, TRUNC(L_PRICING_DATE) );
1610 ----DBMS_Output.put_line(' l_rounding_factor = ' || to_char(l_rounding_factor));
1611 ----DBMS_Output.put_line(' l_prc_method_code_out = ' ||l_prc_method_code_out);
1612 ----DBMS_Output.put_line(' l_list_price = ' || to_char(l_list_price));
1613
1614 ELSE
1615
1616 Null;
1617
1618 -- Fetch driving on p_pricing_attribute2
1619 /* ordered use_nl(OELST OELIN) index(OELST OE_PRICE_LISTS_U1) index(OELIN OE_PRICE_LIST_LINES_N1) */
1620
1621 END IF;
1622
1623
1624 -- Debug info
1625 /*
1626 OE_MSG_PUB.Add_Exc_Msg
1627 ( p_error_text => 'list price is not null - '||
1628 ' list_price = '||l_list_price||
1629 ' l_prc_method_code = '||l_prc_method_code_out||
1630 ' l_rounding_factor = '||l_rounding_factor
1631 );
1632 */
1633
1634
1635 /*
1636 --DBMS_output.put_line('In Kanan fetch list price');
1637 --DBMS_output.put_line('l_rounding_factor: '||l_rounding_factor);
1638 --DBMS_output.put_line('l_prc_method_code_out: '||l_prc_method_code_out);
1639 --DBMS_output.put_line('l_list_price: '||l_list_price);
1640 --DBMS_output.put_line('l_percent_price: '||l_percent_price);
1641 --DBMS_output.put_line('Leaving Kanan fetch list price');
1642 */
1643 -- Calculate list price.
1644
1645 IF l_percent_price Is NULL Then
1646
1647 l_list_price := ROUND ( l_list_price , - l_rounding_factor );
1648 l_list_percent := NULL ;
1649
1650 --QP doing this because arithmetic_operator is null for price list
1651 -- the only way to know if it is a percent or amt is to check if the column -- is null. If percent_price is null then it is an AMT.
1652
1653 l_prc_method_code_out := G_PRC_METHOD_AMOUNT;
1654
1655 ELSIF l_list_price is Null Then
1656
1657 -- List percent is the selected list price
1658
1659 l_list_percent := l_percent_price ;
1660 l_prc_method_code_out := G_PRC_METHOD_PERCENT;
1661 IF p_base_price IS NULL
1662 THEN
1663
1664 -- No base price
1665
1666 l_list_price := NULL ;
1667
1668 ELSE
1669
1670 l_list_price := l_list_percent * p_base_price / 100 ;
1671
1672 IF p_item_type_code = G_PRC_ITEM_SERVICE THEN
1673
1674 l_list_price := l_list_price * p_service_duration ;
1675
1676 END IF;
1677
1678 l_list_price := ROUND ( l_list_price , l_rounding_factor );
1679
1680 END IF;
1681
1682 ELSE
1683
1684 -- Unexpected error, invalid pricing method
1685
1686 IF OE_MSG_PUB.Check_Msg_Level (
1687 OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1688 THEN
1689
1690 OE_MSG_PUB.Add_Exc_Msg
1691 ( G_PKG_NAME ,
1692 l_api_name ,
1693 'Invalid pricing method ='||l_prc_method_code_out
1694 );
1695
1696 END IF;
1697
1698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1699
1700 END IF; -- prc_method_code
1701
1702 p_price_list_id_out := p_price_list_id ;
1703 p_prc_method_code_out := l_prc_method_code_out ;
1704 p_list_price := l_list_price ;
1705 p_list_percent := l_list_percent ;
1706 p_rounding_factor := l_rounding_factor ;
1707
1708 --END IF; -- There is a list price
1709
1710 EXCEPTION
1711
1712 WHEN NO_DATA_FOUND THEN
1713
1714 -- Debug info
1715
1716 OE_MSG_PUB.Add_Exc_Msg
1717 ( p_error_text => 'Primary fetch not successful' );
1718
1719
1720 -- Check if the maximum number of attempts has been
1721 -- exceeded. When l_fetch attempts is 1 this means there
1722 -- should be no more fetch attempts, else, look for a
1723 -- secondary list.
1724 --dbms_output.put_line('In no data found exception');
1725 IF l_fetch_attempts > 1 THEN
1726
1727 l_fetch_attempts := l_fetch_attempts - 1;
1728
1729 -- Get secondary_price_list_id
1730
1731 l_price_list_id := Get_Secondary_Price_List ( p_price_list_id );
1732
1733 IF l_price_list_id IS NOT NULL THEN
1734
1735 -- Call Fetch_List_Price using the sec list.
1736
1737 Fetch_List_Price
1738 ( p_api_version_number ,
1739 FND_API.G_FALSE ,
1740 FND_API.G_VALID_LEVEL_NONE ,
1741 l_return_status ,
1742 p_msg_count ,
1743 p_msg_data ,
1744 l_price_list_id ,
1745 p_inventory_item_id ,
1746 p_unit_code ,
1747 p_service_duration ,
1748 p_item_type_code ,
1749 p_prc_method_code ,
1750 p_pricing_attribute1 ,
1751 p_pricing_attribute2 ,
1752 p_pricing_attribute3 ,
1753 p_pricing_attribute4 ,
1754 p_pricing_attribute5 ,
1755 p_pricing_attribute6 ,
1756 p_pricing_attribute7 ,
1757 p_pricing_attribute8 ,
1758 p_pricing_attribute9 ,
1759 p_pricing_attribute10 ,
1760 p_pricing_attribute11 ,
1761 p_pricing_attribute12 ,
1762 p_pricing_attribute13 ,
1763 p_pricing_attribute14 ,
1764 p_pricing_attribute15 ,
1765 p_base_price ,
1766 l_pricing_date ,
1767 l_fetch_attempts ,
1768 p_price_list_id_out ,
1769 p_prc_method_code_out ,
1770 l_list_price ,
1771 p_list_percent ,
1772 p_rounding_factor
1773 );
1774
1775 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1776
1777 -- Unexpected error, abort processing
1778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1779
1780 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1781
1782 -- Error, abort processing
1783 RAISE FND_API.G_EXC_ERROR;
1784
1785 ELSE
1786
1787 -- Set p_list_price. We don't receive the
1788 -- list price in p_list_price because we need
1789 -- to check its value after the call.
1790
1791 p_list_price := l_list_price ;
1792
1793 END IF;
1794
1795 END IF; -- There was a secondary price list.
1796
1797 --END IF; -- fetch_attempts > 1
1798 ELSE --After all attempts still couldn't find price list
1799 FND_MESSAGE.SET_NAME('QP','QP_PR_LIST_NOT_FOUND');
1800 FND_MESSAGE.SET_TOKEN('%',p_inventory_item_id);
1801 OE_MSG_PUB.Add;
1802
1803
1804
1805 END IF;
1806
1807 WHEN OTHERS THEN
1808
1809 -- Unexpected error
1810
1811 IF OE_MSG_PUB.Check_Msg_Level(
1812 OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1813 THEN
1814
1815 OE_MSG_PUB.Add_Exc_Msg
1816 ( G_PKG_NAME ,
1817 l_api_name
1818 );
1819
1820
1821
1822 END IF;
1823
1824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1825
1826 END; -- BEGIN select list price block.
1827
1828 -- At this point, All processing is done, and all the secondary
1829 -- fetches have been performed.
1830
1831 -- If list_price is NULL and the fetch level =1 meaning that this
1832 -- is the execution coresponding to the primary fetch. Then add an
1833 -- informational message to inform the caller that the item was
1834 -- not found o the price list.
1835
1836 -- Debug info
1837 /*
1838 OE_MSG_PUB.Add_Exc_Msg
1839 ( p_error_text => 'End of Fetch_List_Price - '||
1840 ' l_list_price = '||l_list_price||
1841 ' G_Fetch_Level = '||G_Fetch_Level
1842 );
1843 */
1844
1845
1846 IF l_list_price IS NULL AND
1847 G_Fetch_Level = 1
1848 THEN
1849
1850 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1851 THEN
1852
1853 FND_MESSAGE.SET_NAME('QP','OE_PRC_NO_LIST_PRICE');
1854 FND_MESSAGE.SET_TOKEN('PRICE_LIST', Get_Price_List_Name
1855 (p_price_list_id) );
1856 FND_MESSAGE.SET_TOKEN('ITEM', Get_Item_Description
1857 (p_inventory_item_id) );
1858 FND_MESSAGE.SET_TOKEN('UNIT',Get_Unit_Name(p_unit_code ));
1859 OE_MSG_PUB.Add;
1860
1861
1862 END IF;
1863
1864 END IF;
1865
1866 -- Decement G_Fetch_Level
1867
1868 G_Fetch_Level := G_Fetch_Level - 1;
1869
1870 -- Get message count and if 1, return message data
1871
1872 OE_MSG_PUB.Count_And_Get
1873 ( p_count => p_msg_count ,
1874 p_data => p_msg_data
1875 );
1876
1877
1878 EXCEPTION
1879
1880 WHEN FND_API.G_EXC_ERROR THEN
1881
1882 p_return_status := FND_API.G_RET_STS_ERROR;
1883
1884 -- Get message count and if 1, return message data
1885
1886 OE_MSG_PUB.Count_And_Get
1887 (p_count => p_msg_count,
1888 p_data => p_msg_data
1889 );
1890
1891 -- Decement G_Fetch_Level
1892
1893 G_Fetch_Level := G_Fetch_Level - 1;
1894
1895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1896
1897 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1898
1899 -- Get message count and if 1, return message data
1900
1901 OE_MSG_PUB.Count_And_Get
1902 (p_count => p_msg_count,
1903 p_data => p_msg_data
1904 );
1905
1906 -- Decrement G_Fetch_Level
1907
1908 G_Fetch_Level := G_Fetch_Level - 1;
1909
1910
1911 WHEN OTHERS THEN
1912
1913 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1914
1915 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1916 OE_MSG_PUB.Add_Exc_Msg
1917 ( G_PKG_NAME ,
1918 l_api_name
1919 );
1920 END IF;
1921
1922 -- Get message count and if 1, return message data
1923
1924 OE_MSG_PUB.Count_And_Get
1925 ( p_count => p_msg_count,
1926 p_data => p_msg_data
1927 );
1928
1929 -- Decement G_Fetch_Level
1930
1931 G_Fetch_Level := G_Fetch_Level - 1;
1932
1933 END; -- Fetch_List_Price
1934
1935
1936 FUNCTION Get_Sec_Price_List
1937 ( p_price_list_id IN NUMBER )
1938 RETURN NUMBER
1939 IS
1940 l_sec_price_list_id NUMBER := NULL;
1941 BEGIN
1942
1943 return null;
1944
1945 END Get_Sec_Price_List;
1946
1947 FUNCTION Get_Price_List_Name
1948 ( p_price_list_id IN NUMBER )
1949 RETURN VARCHAR2
1950 IS
1951 l_name VARCHAR2(80) := NULL;
1952 BEGIN
1953
1954 return null;
1955
1956 END Get_Price_List_Name;
1957
1958 FUNCTION Get_Item_Description
1959 ( p_item_id IN NUMBER )
1960 RETURN VARCHAR2
1961 IS
1962 l_desc VARCHAR2(240) := NULL;
1963 l_org_id NUMBER := NULL;
1964 BEGIN
1965
1966 l_org_id := FND_PROFILE.VALUE ('OE_ORGANIZATION_ID');
1967
1968 IF p_item_id IS NULL OR
1969 l_org_id IS NULL
1970 THEN
1971 RETURN NULL;
1972 END IF;
1973
1974 SELECT DESCRIPTION
1975 INTO l_desc
1976 FROM MTL_SYSTEM_ITEMS
1977 WHERE INVENTORY_ITEM_ID = p_item_id
1978 AND ORGANIZATION_ID = l_org_id;
1979
1980 RETURN l_desc;
1981
1982 EXCEPTION
1983
1984 WHEN OTHERS THEN
1985
1986 OE_MSG_PUB.Add_Exc_Msg
1987 ( G_PKG_NAME ,
1988 'Get_Item_Description - p_item_id = '||p_item_id||
1989 ' org_id ='||l_org_id
1990 );
1991
1992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1993
1994 END Get_Item_Description;
1995
1996 FUNCTION Get_Unit_Name
1997 ( p_unit_code IN VARCHAR2 )
1998 RETURN VARCHAR2
1999 IS
2000 l_name VARCHAR2(80) := NULL;
2001 BEGIN
2002
2003 IF p_unit_code IS NULL THEN
2004 RETURN NULL;
2005 END IF;
2006
2007 SELECT UNIT_OF_MEASURE
2008 INTO l_name
2009 FROM MTL_UNITS_OF_MEASURE
2010 WHERE UOM_CODE = p_unit_code;
2011
2012 RETURN l_name;
2013
2014 EXCEPTION
2015
2016 WHEN OTHERS THEN
2017
2018 OE_MSG_PUB.Add_Exc_Msg
2019 ( G_PKG_NAME ,
2020 'Get_Unit_Name - p_unit_code = '||p_unit_code
2021 );
2022
2023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2024
2025 END Get_Unit_Name;
2026
2027
2028 Function Get_Secondary_Price_List(p_list_header_id in NUMBER) return number
2029 is
2030 l_sec_price_list_id number;
2031 l_context varchar2(30);
2032 l_attribute varchar2(30);
2033 begin
2034
2035 QP_UTIL.Get_Context_Attribute('PRICE_LIST_ID', l_context, l_attribute);
2036
2037 select list_header_id
2038 into l_sec_price_list_id
2039 from qp_qualifiers
2040 where qualifier_context = l_context
2041 and qualifier_attribute = l_attribute
2042 and qualifier_attr_value = to_char(p_list_header_id)
2043 and list_header_id <> p_list_header_id
2044 and list_header_id in ( select list_header_id
2045 from qp_list_headers_b
2046 where list_type_code = 'PRL' )
2047 and qualifier_rule_id is null
2048 and rownum < 2;
2049
2050 return l_sec_price_list_id;
2051
2052 exception
2053
2054 when no_data_found then return null;
2055
2056
2057 end Get_Secondary_Price_List;
2058
2059 Function Get_Inventory_Item_Id( p_list_line_id in NUMBER) return number
2060 is
2061 l_inventory_item_id number;
2062 l_context varchar2(30);
2063 l_attribute varchar2(30);
2064 begin
2065
2066 QP_UTIL.Get_Context_Attribute('1001', l_context, l_attribute);
2067
2068 select to_number(product_attr_value)
2069 into l_inventory_item_id
2070 from qp_pricing_attributes
2071 where list_line_id = p_list_line_id
2072 and product_attribute_context = l_context
2073 and product_attribute = l_attribute
2074 and rownum = 1;
2075
2076 return l_inventory_item_id;
2077
2078 exception
2079
2080 when no_data_found then return null;
2081
2082 end Get_Inventory_Item_Id;
2083
2084 Function Get_Customer_Item_Id( p_list_line_id in NUMBER) return number
2085 is
2086 l_customer_item_id number;
2087 l_context varchar2(30);
2088 l_attribute varchar2(30);
2089 begin
2090
2091 QP_UTIL.Get_Context_Attribute('CUSTOMER_ITEM_ID', l_context, l_attribute);
2092
2093 select to_number(pricing_attr_value_from)
2094 into l_customer_item_id
2095 from qp_pricing_attributes
2096 where list_line_id = p_list_line_id
2097 and pricing_attribute_context = l_context
2098 and pricing_attribute = l_attribute;
2099
2100 return l_customer_item_id;
2101
2102 exception
2103
2104 when no_data_found then return null;
2105
2106
2107
2108 end Get_Customer_Item_Id;
2109
2110
2111 Function Get_Pricing_Attr_Context( p_list_line_id in NUMBER) return varchar2
2112 is
2113 l_pricing_context varchar2(30);
2114 l_context varchar2(30);
2115 l_attribute varchar2(30);
2116 begin
2117
2118 select pricing_attribute_context
2119 into l_pricing_context
2120 from qp_pricing_attributes
2121 where list_line_id = p_list_line_id
2122 and pricing_attribute in ('PRICING_ATTRIBUTE1',
2123 'PRICING_ATTRIBUTE2',
2124 'PRICING_ATTRIBUTE3',
2125 'PRICING_ATTRIBUTE4',
2126 'PRICING_ATTRIBUTE5',
2127 'PRICING_ATTRIBUTE6',
2128 'PRICING_ATTRIBUTE7',
2129 'PRICING_ATTRIBUTE8',
2130 'PRICING_ATTRIBUTE9',
2131 'PRICING_ATTRIBUTE10',
2132 'PRICING_ATTRIBUTE11',
2133 'PRICING_ATTRIBUTE12',
2134 'PRICING_ATTRIBUTE13',
2135 'PRICING_ATTRIBUTE14',
2136 'PRICING_ATTRIBUTE15' )
2137 and rownum = 1;
2138
2139
2140 return l_pricing_context;
2141
2142 exception
2143
2144 when no_data_found then return null;
2145
2146
2147 end Get_Pricing_Attr_Context;
2148
2149 Function Get_Pricing_Attribute( p_list_line_id in NUMBER,
2150 p_pricing_attr in varchar2) return varchar2
2151 is
2152 l_pricing_attribute1 varchar2(240);
2153 l_context varchar2(30);
2154 l_attribute varchar2(30);
2155 begin
2156
2157
2158 select pricing_attr_value_from
2159 into l_pricing_attribute1
2160 from qp_pricing_attributes
2161 where list_line_id = p_list_line_id
2162 and pricing_attribute = p_pricing_attr;
2163
2164 return l_pricing_attribute1;
2165
2166 exception
2167
2168 when no_data_found then return null;
2169
2170 end Get_Pricing_Attribute;
2171
2172 Function Does_Pricing_Attribute_Exist
2173 (P_Product_Attr_Context In Varchar2,
2174 p_Product_Attr In Varchar2,
2175 P_Product_Attr_Val In Varchar2,
2176 P_PRODUCT_UOM_CODE In Varchar2,
2177 P_PRICING_ATTRIBUTE_CONTEXT In Varchar2,
2178 P_PRICING_ATTRIBUTE In Varchar2,
2179 P_PRICING_ATTR_VALUE_FROM In Varchar2,
2180 P_PRICING_ATTR_VALUE_TO In Varchar2,
2181 P_LIST_LINE_ID In Number,
2182 P_LIST_HEADER_ID In Number
2183 ) Return Varchar2 Is
2184 Dummy_variable Varchar2(1);
2185 Begin
2186
2187 SELECT 'x'
2188 Into Dummy_Variable
2189 from QP_PRICING_ATTRIBUTES QPPA --, QP_LIST_LINES QPLL
2190 Where
2191 PRODUCT_ATTRIBUTE_CONTEXT = P_Product_Attr_Context
2192 And PRODUCT_ATTRIBUTE = P_Product_Attr
2193 And PRODUCT_ATTR_VALUE = P_Product_Attr_Val
2194 And PRODUCT_UOM_CODE = P_PRODUCT_UOM_CODE
2195 And PRICING_ATTRIBUTE_CONTEXT = P_PRICING_ATTRIBUTE_CONTEXT
2196 And PRICING_ATTRIBUTE = P_PRICING_ATTRIBUTE
2197 And P_PRICING_ATTR_VALUE_FROM between
2198 PRICING_ATTR_VALUE_FROM And Nvl(PRICING_ATTR_VALUE_TO,PRICING_ATTR_VALUE_FROM)
2199 -- AND QPPA.LIST_LINE_ID = QPLL.LIST_LINE_ID
2200 AND QPPA.LIST_LINE_ID = P_LIST_LINE_ID;
2201
2202 Return 'Y';
2203 Exception
2204 When No_Data_Found Then
2205 Return 'N';
2206 When Too_Many_Rows Then
2207 Return 'D';
2208 End Does_Pricing_Attribute_Exist ;
2209
2210 Function Get_Price_Break_High ( p_list_line_id IN NUMBER ) return number
2211 is
2212 l_context varchar2(30);
2213 l_pricing_attribute varchar2(240);
2214 l_price_break_high number;
2215 l_attribute varchar2(30);
2216 begin
2217 QP_UTIL.Get_Context_Attribute ( 'UNITS',l_context, l_attribute);
2218
2219 select to_number(pricing_attr_value_to) into
2220 l_price_break_high
2221 from qp_pricing_attributes
2222 where list_line_id = p_list_line_id
2223 and pricing_attribute_context = l_context
2224 and pricing_attribute = l_attribute;
2225
2226 return l_price_break_high;
2227
2228 end Get_Price_Break_High;
2229
2230
2231 Function Get_Price_Break_Low ( p_list_line_id IN NUMBER ) return number
2232 is
2233 l_context varchar2(30);
2234 l_pricing_attribute varchar2(240);
2235 l_price_break_low number;
2236 l_attribute varchar2(30);
2237 begin
2238
2239 QP_UTIL.Get_Context_Attribute ( 'UNITS',l_context, l_attribute);
2240
2241 select to_number(pricing_attr_value_from) into
2242 l_price_break_low
2243 from qp_pricing_attributes
2244 where list_line_id = p_list_line_id
2245 and pricing_attribute_context = l_context
2246 and pricing_attribute = l_attribute;
2247
2248 return l_price_break_low;
2249
2250 end Get_Price_Break_Low;
2251
2252 Function Get_Product_UOM_Code ( p_list_line_id IN NUMBER ) return varchar2
2253 is
2254 l_context varchar2(30);
2255 l_pricing_attribute varchar2(240);
2256 l_attribute varchar2(30);
2257 l_uom_code varchar2(3);
2258 begin
2259
2260 QP_UTIL.Get_Context_Attribute ( '1001',l_context, l_attribute);
2261
2262 select product_uom_code
2263 into l_uom_code
2264 from qp_pricing_attributes
2265 where list_line_id = p_list_line_id
2266 and product_attribute_context = l_context
2267 and product_attribute = l_attribute
2268 and rownum = 1;
2269
2270 return l_uom_code;
2271
2272
2273 end Get_Product_UOM_Code;
2274
2275
2276 END QP_PRICE_LIST_PVT;