[Home] [Help]
PACKAGE BODY: APPS.QP_PRICE_FORMULA_PVT
Source
1 PACKAGE BODY QP_Price_Formula_PVT AS
2 /* $Header: QPXVPRFB.pls 120.2 2005/07/06 02:00:42 appldev ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Price_Formula_PVT';
7
8 -- Formula
9
10 PROCEDURE Formula
11 ( p_validation_level IN NUMBER
12 , p_control_rec IN QP_GLOBALS.Control_Rec_Type
13 , p_FORMULA_rec IN QP_Price_Formula_PUB.Formula_Rec_Type
14 , p_old_FORMULA_rec IN QP_Price_Formula_PUB.Formula_Rec_Type
15 , x_FORMULA_rec OUT NOCOPY /* file.sql.39 change */ QP_Price_Formula_PUB.Formula_Rec_Type
16 , x_old_FORMULA_rec OUT NOCOPY /* file.sql.39 change */ QP_Price_Formula_PUB.Formula_Rec_Type
17 )
18 IS
19 l_return_status VARCHAR2(1);
20 l_control_rec QP_GLOBALS.Control_Rec_Type;
21 l_FORMULA_rec QP_Price_Formula_PUB.Formula_Rec_Type := p_FORMULA_rec;
22 l_old_FORMULA_rec QP_Price_Formula_PUB.Formula_Rec_Type := p_old_FORMULA_rec;
23
24 --[prarasto]
25 l_p_FORMULA_rec QP_Price_Formula_PUB.Formula_Rec_Type;
26
27 BEGIN
28
29 oe_debug_pub.add('Entering procedure Formula in Pvt formula package');
30 -- Load API control record
31
32 l_control_rec := QP_GLOBALS.Init_Control_Rec
33 ( p_operation => l_FORMULA_rec.operation
34 , p_control_rec => p_control_rec
35 );
36
37 -- Set record return status.
38
39 l_FORMULA_rec.return_status := FND_API.G_RET_STS_SUCCESS;
40
41 -- Prepare record.
42
43 IF l_FORMULA_rec.operation = QP_GLOBALS.G_OPR_CREATE THEN
44
45 l_FORMULA_rec.db_flag := FND_API.G_FALSE;
46
47 -- Set missing old record elements to NULL.
48
49 l_old_FORMULA_rec :=
50 QP_Formula_Util.Convert_Miss_To_Null (l_old_FORMULA_rec);
51
52 ELSIF l_FORMULA_rec.operation = QP_GLOBALS.G_OPR_UPDATE
53 OR l_FORMULA_rec.operation = QP_GLOBALS.G_OPR_DELETE
54 THEN
55
56 l_FORMULA_rec.db_flag := FND_API.G_TRUE;
57
58 -- Query Old if missing
59
60 IF l_old_FORMULA_rec.price_formula_id = FND_API.G_MISS_NUM
61 THEN
62
63 l_old_FORMULA_rec := QP_Formula_Util.Query_Row
64 ( p_price_formula_id => l_FORMULA_rec.price_formula_id
65 );
66
67 ELSE
68
69 -- Set missing old record elements to NULL.
70
71 l_old_FORMULA_rec :=
72 QP_Formula_Util.Convert_Miss_To_Null (l_old_FORMULA_rec);
73
74 END IF;
75
76 -- Complete new record from old
77
78 l_FORMULA_rec := QP_Formula_Util.Complete_Record
79 ( p_FORMULA_rec => l_FORMULA_rec
80 , p_old_FORMULA_rec => l_old_FORMULA_rec
81 );
82
83 END IF;
84
85 -- Attribute level validation.
86
87 IF l_FORMULA_rec.operation = QP_GLOBALS.G_OPR_CREATE OR
88 l_FORMULA_rec.operation = QP_GLOBALS.G_OPR_UPDATE OR
89 l_FORMULA_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
90
91 IF l_control_rec.default_attributes
92 OR l_control_rec.change_attributes
93 THEN
94
95 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
96
97 QP_Validate_Formula.Attributes
98 ( x_return_status => l_return_status
99 , p_FORMULA_rec => l_FORMULA_rec
100 , p_old_FORMULA_rec => l_old_FORMULA_rec
101 );
102
103 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
105 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
106 RAISE FND_API.G_EXC_ERROR;
107 END IF;
108
109 END IF;
110
111 END IF;
112
113 -- Clear dependent attributes.
114
115 IF l_control_rec.change_attributes THEN
116
117 l_p_FORMULA_rec := l_FORMULA_rec; --[prarasto]
118
119 QP_Formula_Util.Clear_Dependent_Attr
120 ( p_FORMULA_rec => l_p_FORMULA_rec
121 , p_old_FORMULA_rec => l_old_FORMULA_rec
122 , x_FORMULA_rec => l_FORMULA_rec
123 );
124
125 END IF;
126
127 -- Default missing attributes
128
129 IF l_control_rec.default_attributes
130 OR l_control_rec.change_attributes
131 THEN
132
133 l_p_FORMULA_rec := l_FORMULA_rec; --[prarasto]
134
135 QP_Default_Formula.Attributes
136 ( p_FORMULA_rec => l_p_FORMULA_rec
137 , x_FORMULA_rec => l_FORMULA_rec
138 );
139
140 END IF;
141
142 -- Apply attribute changes
143
144 IF l_control_rec.default_attributes
145 OR l_control_rec.change_attributes
146 THEN
147
148 l_p_FORMULA_rec := l_FORMULA_rec; --[prarasto]
149
150 QP_Formula_Util.Apply_Attribute_Changes
151 ( p_FORMULA_rec => l_p_FORMULA_rec
152 , p_old_FORMULA_rec => l_old_FORMULA_rec
153 , x_FORMULA_rec => l_FORMULA_rec
154 );
155
156 END IF;
157
158 -- Entity level validation.
159
160 IF l_control_rec.validate_entity THEN
161
162 IF l_FORMULA_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
163
164 QP_Validate_Formula.Entity_Delete
165 ( x_return_status => l_return_status
166 , p_FORMULA_rec => l_FORMULA_rec
167 );
168
169 ELSE
170
171 QP_Validate_Formula.Entity
172 ( x_return_status => l_return_status
173 , p_FORMULA_rec => l_FORMULA_rec
174 , p_old_FORMULA_rec => l_old_FORMULA_rec
175 );
176
177 END IF;
178
179 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
182 RAISE FND_API.G_EXC_ERROR;
183 END IF;
184
185 END IF;
186
187 -- Step 4. Write to DB
188
189 IF l_control_rec.write_to_db THEN
190
191 IF l_FORMULA_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
192
193 QP_Formula_Util.Delete_Row
194 ( p_price_formula_id => l_FORMULA_rec.price_formula_id
195 );
196
197 ELSE
198
199 -- Get Who Information
200
201 l_FORMULA_rec.last_update_date := SYSDATE;
202 l_FORMULA_rec.last_updated_by := FND_GLOBAL.USER_ID;
203 l_FORMULA_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
204
205 IF l_FORMULA_rec.operation = QP_GLOBALS.G_OPR_UPDATE THEN
206
207 QP_Formula_Util.Update_Row (l_FORMULA_rec);
208
209 ELSIF l_FORMULA_rec.operation = QP_GLOBALS.G_OPR_CREATE THEN
210
211 l_FORMULA_rec.creation_date := SYSDATE;
212 l_FORMULA_rec.created_by := FND_GLOBAL.USER_ID;
213
214 QP_Formula_Util.Insert_Row (l_FORMULA_rec);
215
216 END IF;
217
218 END IF;
219
220 END IF;
221
222 END IF;/* End of IF operation is create, update or delete only */
223
224 -- Load OUT parameters
225
226 x_FORMULA_rec := l_FORMULA_rec;
227 x_old_FORMULA_rec := l_old_FORMULA_rec;
228
229 oe_debug_pub.add('Leaving procedure Formula in Pvt formula package' );
230 EXCEPTION
231
232 WHEN FND_API.G_EXC_ERROR THEN
233
234 l_FORMULA_rec.return_status := FND_API.G_RET_STS_ERROR;
235 x_FORMULA_rec := l_FORMULA_rec;
236 x_old_FORMULA_rec := l_old_FORMULA_rec;
237 RAISE;
238
239 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
240
241 l_FORMULA_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
242 x_FORMULA_rec := l_FORMULA_rec;
243 x_old_FORMULA_rec := l_old_FORMULA_rec;
244
245 RAISE;
246
247 WHEN OTHERS THEN
248
249 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
250 THEN
251 OE_MSG_PUB.Add_Exc_Msg
252 ( G_PKG_NAME
253 , 'Formula'
254 );
255 END IF;
256
257 l_FORMULA_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258 x_FORMULA_rec := l_FORMULA_rec;
259 x_old_FORMULA_rec := l_old_FORMULA_rec;
260
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262
263 END Formula;
264
265 -- Formula_Liness
266
267 PROCEDURE Formula_Liness
268 ( p_validation_level IN NUMBER
269 , p_control_rec IN QP_GLOBALS.Control_Rec_Type
270 , p_FORMULA_LINES_tbl IN QP_Price_Formula_PUB.Formula_Lines_Tbl_Type
271 , p_old_FORMULA_LINES_tbl IN QP_Price_Formula_PUB.Formula_Lines_Tbl_Type
272 , x_FORMULA_LINES_tbl OUT NOCOPY /* file.sql.39 change */ QP_Price_Formula_PUB.Formula_Lines_Tbl_Type
273 , x_old_FORMULA_LINES_tbl OUT NOCOPY /* file.sql.39 change */ QP_Price_Formula_PUB.Formula_Lines_Tbl_Type
274 )
275 IS
276 l_return_status VARCHAR2(1);
277 l_control_rec QP_GLOBALS.Control_Rec_Type;
278 l_FORMULA_LINES_rec QP_Price_Formula_PUB.Formula_Lines_Rec_Type;
279 l_FORMULA_LINES_tbl QP_Price_Formula_PUB.Formula_Lines_Tbl_Type;
280 l_old_FORMULA_LINES_rec QP_Price_Formula_PUB.Formula_Lines_Rec_Type;
281 l_old_FORMULA_LINES_tbl QP_Price_Formula_PUB.Formula_Lines_Tbl_Type;
282
283 --[prarasto]
284 l_p_FORMULA_LINES_rec QP_Price_Formula_PUB.Formula_Lines_Rec_Type;
285 BEGIN
286
287 oe_debug_pub.add('Entering procedure Formula Liness in Pvt formula package');
288 -- Init local table variables.
289
290 l_FORMULA_LINES_tbl := p_FORMULA_LINES_tbl;
291 l_old_FORMULA_LINES_tbl := p_old_FORMULA_LINES_tbl;
292
293 FOR I IN 1..l_FORMULA_LINES_tbl.COUNT LOOP
294 BEGIN
295
296 -- Load local records.
297
298 l_FORMULA_LINES_rec := l_FORMULA_LINES_tbl(I);
299
300 IF l_old_FORMULA_LINES_tbl.EXISTS(I) THEN
301 l_old_FORMULA_LINES_rec := l_old_FORMULA_LINES_tbl(I);
302 ELSE
303 l_old_FORMULA_LINES_rec := QP_Price_Formula_PUB.G_MISS_FORMULA_LINES_REC;
304 END IF;
305
306 -- Load API control record
307
308 l_control_rec := QP_GLOBALS.Init_Control_Rec
309 ( p_operation => l_FORMULA_LINES_rec.operation
310 , p_control_rec => p_control_rec
311 );
312
313 -- Set record return status.
314
315 l_FORMULA_LINES_rec.return_status := FND_API.G_RET_STS_SUCCESS;
316
317 -- Prepare record.
318
319 IF l_FORMULA_LINES_rec.operation = QP_GLOBALS.G_OPR_CREATE THEN
320
321 l_FORMULA_LINES_rec.db_flag := FND_API.G_FALSE;
322
323 -- Set missing old record elements to NULL.
324
325 l_old_FORMULA_LINES_rec :=
326 QP_Formula_Lines_Util.Convert_Miss_To_Null (l_old_FORMULA_LINES_rec);
327
328 ELSIF l_FORMULA_LINES_rec.operation = QP_GLOBALS.G_OPR_UPDATE
329 OR l_FORMULA_LINES_rec.operation = QP_GLOBALS.G_OPR_DELETE
330 THEN
331
332 l_FORMULA_LINES_rec.db_flag := FND_API.G_TRUE;
333
334 -- Query Old if missing
335
336 IF l_old_FORMULA_LINES_rec.price_formula_line_id = FND_API.G_MISS_NUM
337 THEN
338
339 l_old_FORMULA_LINES_rec := QP_Formula_Lines_Util.Query_Row
340 ( p_price_formula_line_id => l_FORMULA_LINES_rec.price_formula_line_id
341 );
342
343 ELSE
344
345 -- Set missing old record elements to NULL.
346
347 l_old_FORMULA_LINES_rec :=
348 QP_Formula_Lines_Util.Convert_Miss_To_Null (l_old_FORMULA_LINES_rec);
349
350 END IF;
351
352 -- Complete new record from old
353
354 l_FORMULA_LINES_rec := QP_Formula_Lines_Util.Complete_Record
355 ( p_FORMULA_LINES_rec => l_FORMULA_LINES_rec
356 , p_old_FORMULA_LINES_rec => l_old_FORMULA_LINES_rec
357 );
358
359 END IF;
360
361 -- Attribute level validation.
362
363 IF l_control_rec.default_attributes
364 OR l_control_rec.change_attributes
365 THEN
366
367 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
368
369
370 QP_Validate_Formula_Lines.Attributes
371 ( x_return_status => l_return_status
372 , p_FORMULA_LINES_rec => l_FORMULA_LINES_rec
373 , p_old_FORMULA_LINES_rec => l_old_FORMULA_LINES_rec
374 );
375
376 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
378 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
379 RAISE FND_API.G_EXC_ERROR;
380 END IF;
381
382 END IF;
383
384 END IF;
385
386 -- Clear dependent attributes.
387
388 IF l_control_rec.change_attributes THEN
389
390 l_p_FORMULA_LINES_rec := l_FORMULA_LINES_rec; --[prarasto]
391
392 QP_Formula_Lines_Util.Clear_Dependent_Attr
393 ( p_FORMULA_LINES_rec => l_p_FORMULA_LINES_rec
394 , p_old_FORMULA_LINES_rec => l_old_FORMULA_LINES_rec
395 , x_FORMULA_LINES_rec => l_FORMULA_LINES_rec
396 );
397
398 END IF;
399
400 -- Default missing attributes
401
402 IF l_control_rec.default_attributes
403 OR l_control_rec.change_attributes
404 THEN
405
406 l_p_FORMULA_LINES_rec := l_FORMULA_LINES_rec; --[prarasto]
407
408 QP_Default_Formula_Lines.Attributes
409 ( p_FORMULA_LINES_rec => l_p_FORMULA_LINES_rec
410 , x_FORMULA_LINES_rec => l_FORMULA_LINES_rec
411 );
412
413 END IF;
414
415 -- Apply attribute changes
416
417 IF l_control_rec.default_attributes
418 OR l_control_rec.change_attributes
419 THEN
420
421 l_p_FORMULA_LINES_rec := l_FORMULA_LINES_rec; --[prarasto]
422
423 QP_Formula_Lines_Util.Apply_Attribute_Changes
424 ( p_FORMULA_LINES_rec => l_p_FORMULA_LINES_rec
425 , p_old_FORMULA_LINES_rec => l_old_FORMULA_LINES_rec
426 , x_FORMULA_LINES_rec => l_FORMULA_LINES_rec
427 );
428
429 END IF;
430
431 -- Entity level validation.
432
433 IF l_control_rec.validate_entity THEN
434
435 IF l_FORMULA_LINES_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
436
437 QP_Validate_Formula_Lines.Entity_Delete
438 ( x_return_status => l_return_status
439 , p_FORMULA_LINES_rec => l_FORMULA_LINES_rec
440 );
441
442 ELSE
443
444 QP_Validate_Formula_Lines.Entity
445 ( x_return_status => l_return_status
446 , p_FORMULA_LINES_rec => l_FORMULA_LINES_rec
447 , p_old_FORMULA_LINES_rec => l_old_FORMULA_LINES_rec
448 );
449
450 END IF;
451
452 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
454 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
455 RAISE FND_API.G_EXC_ERROR;
456 END IF;
457
458 END IF;
459
460 -- Step 4. Write to DB
461
462 IF l_control_rec.write_to_db THEN
463
464 IF l_FORMULA_LINES_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
465
466 QP_Formula_Lines_Util.Delete_Row
467 ( p_price_formula_line_id => l_FORMULA_LINES_rec.price_formula_line_id
468 );
469
470 ELSE
471
472 -- Get Who Information
473
474 l_FORMULA_LINES_rec.last_update_date := SYSDATE;
475 l_FORMULA_LINES_rec.last_updated_by := FND_GLOBAL.USER_ID;
476 l_FORMULA_LINES_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
477
478 IF l_FORMULA_LINES_rec.operation = QP_GLOBALS.G_OPR_UPDATE THEN
479
480 QP_Formula_Lines_Util.Update_Row (l_FORMULA_LINES_rec);
481
482 ELSIF l_FORMULA_LINES_rec.operation = QP_GLOBALS.G_OPR_CREATE THEN
483
484 l_FORMULA_LINES_rec.creation_date := SYSDATE;
485 l_FORMULA_LINES_rec.created_by := FND_GLOBAL.USER_ID;
486
487 QP_Formula_Lines_Util.Insert_Row (l_FORMULA_LINES_rec);
488
489 END IF;
490
491 END IF;
492
493 END IF;
494
495 -- Load tables.
496
497 l_FORMULA_LINES_tbl(I) := l_FORMULA_LINES_rec;
498 l_old_FORMULA_LINES_tbl(I) := l_old_FORMULA_LINES_rec;
499
500 -- For loop exception handler.
501
502
503 EXCEPTION
504
505 WHEN FND_API.G_EXC_ERROR THEN
506
507 l_FORMULA_LINES_rec.return_status := FND_API.G_RET_STS_ERROR;
508 l_FORMULA_LINES_tbl(I) := l_FORMULA_LINES_rec;
509 l_old_FORMULA_LINES_tbl(I) := l_old_FORMULA_LINES_rec;
510
511 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
512
513 l_FORMULA_LINES_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
514 l_FORMULA_LINES_tbl(I) := l_FORMULA_LINES_rec;
515 l_old_FORMULA_LINES_tbl(I) := l_old_FORMULA_LINES_rec;
516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
517
518 WHEN OTHERS THEN
519
520 l_FORMULA_LINES_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
521 l_FORMULA_LINES_tbl(I) := l_FORMULA_LINES_rec;
522 l_old_FORMULA_LINES_tbl(I) := l_old_FORMULA_LINES_rec;
523
524 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
525 THEN
526 OE_MSG_PUB.Add_Exc_Msg
527 ( G_PKG_NAME
528 , 'Formula_Liness'
529 );
530 END IF;
531
532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
533
534 END;
535 END LOOP;
536
537 -- Load OUT parameters
538
539 x_FORMULA_LINES_tbl := l_FORMULA_LINES_tbl;
540 x_old_FORMULA_LINES_tbl := l_old_FORMULA_LINES_tbl;
541
542 oe_debug_pub.add('Leaving procedure Formula Liness in Pvt formula package');
543 EXCEPTION
544
545 WHEN FND_API.G_EXC_ERROR THEN
546
547 RAISE;
548
549 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
550
551
552 RAISE;
553
554 WHEN OTHERS THEN
555
556 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
557 THEN
558 OE_MSG_PUB.Add_Exc_Msg
559 ( G_PKG_NAME
560 , 'Formula_Liness'
561 );
562 END IF;
563
564
565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
566
567 END Formula_Liness;
568
569 -- Start of Comments
570 -- API name Process_Price_Formula
571 -- Type Private
572 -- Function
573 --
574 -- Pre-reqs
575 --
576 -- Parameters
577 --
578 -- Version Current version = 1.0
579 -- Initial version = 1.0
580 --
581 -- Notes
582 --
583 -- End of Comments
584
585 PROCEDURE Process_Price_Formula
586 ( p_api_version_number IN NUMBER
587 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
588 , p_commit IN VARCHAR2 := FND_API.G_FALSE
589 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
590 , p_control_rec IN QP_GLOBALS.Control_Rec_Type :=
591 QP_GLOBALS.G_MISS_CONTROL_REC
592 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
593 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
594 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
595 , p_FORMULA_rec IN QP_Price_Formula_PUB.Formula_Rec_Type :=
596 QP_Price_Formula_PUB.G_MISS_FORMULA_REC
597 , p_old_FORMULA_rec IN QP_Price_Formula_PUB.Formula_Rec_Type :=
598 QP_Price_Formula_PUB.G_MISS_FORMULA_REC
599 , p_FORMULA_LINES_tbl IN QP_Price_Formula_PUB.Formula_Lines_Tbl_Type :=
600 QP_Price_Formula_PUB.G_MISS_FORMULA_LINES_TBL
601 , p_old_FORMULA_LINES_tbl IN QP_Price_Formula_PUB.Formula_Lines_Tbl_Type :=
602 QP_Price_Formula_PUB.G_MISS_FORMULA_LINES_TBL
603 , x_FORMULA_rec OUT NOCOPY /* file.sql.39 change */ QP_Price_Formula_PUB.Formula_Rec_Type
604 , x_FORMULA_LINES_tbl OUT NOCOPY /* file.sql.39 change */ QP_Price_Formula_PUB.Formula_Lines_Tbl_Type
605 )
606 IS
607 l_api_version_number CONSTANT NUMBER := 1.0;
608 l_api_name CONSTANT VARCHAR2(30):= 'Process_Price_Formula';
609 l_return_status VARCHAR2(1);
610 l_control_rec QP_GLOBALS.Control_Rec_Type;
611 l_FORMULA_rec QP_Price_Formula_PUB.Formula_Rec_Type := p_FORMULA_rec;
612 l_old_FORMULA_rec QP_Price_Formula_PUB.Formula_Rec_Type := p_old_FORMULA_rec;
613 l_FORMULA_LINES_rec QP_Price_Formula_PUB.Formula_Lines_Rec_Type;
614 l_FORMULA_LINES_tbl QP_Price_Formula_PUB.Formula_Lines_Tbl_Type;
615 l_old_FORMULA_LINES_rec QP_Price_Formula_PUB.Formula_Lines_Rec_Type;
616 l_old_FORMULA_LINES_tbl QP_Price_Formula_PUB.Formula_Lines_Tbl_Type;
617 l_qp_status VARCHAR2(1);
618
619 --[prarasto]
620 l_p_FORMULA_rec QP_Price_Formula_PUB.Formula_Rec_Type;
621 l_p_old_FORMULA_rec QP_Price_Formula_PUB.Formula_Rec_Type;
622 l_p_FORMULA_LINES_tbl QP_Price_Formula_PUB.Formula_Lines_Tbl_Type;
623 l_p_old_FORMULA_LINES_tbl QP_Price_Formula_PUB.Formula_Lines_Tbl_Type;
624
625 BEGIN
626
627 oe_debug_pub.add('Entering procedure Process_Price_Formula in Pvt package');
628 -- Standard call to check for call compatibility
629
630 IF NOT FND_API.Compatible_API_Call
631 ( l_api_version_number
632 , p_api_version_number
633 , l_api_name
634 , G_PKG_NAME
635 )
636 THEN
637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
638 END IF;
639
640 -- Initialize message list.
641
642 IF FND_API.to_Boolean(p_init_msg_list) THEN
643 OE_MSG_PUB.initialize;
644 END IF;
645
646
647 -- Disallow calls to procedure if QP not installed or installed only
648 -- as Shared(Basic)
649
650 l_qp_status := QP_UTIL.get_qp_status;
651
652 IF l_qp_status = 'N' THEN
653
654 l_return_status := FND_API.G_RET_STS_ERROR;
655 FND_MESSAGE.SET_NAME('QP','QP_PRICING_NOT_INSTALLED');
656 OE_MSG_PUB.Add;
657 RAISE FND_API.G_EXC_ERROR;
658
659 END IF;
660
661 -- Init local table variables.
662
663 l_FORMULA_LINES_tbl := p_FORMULA_LINES_tbl;
664 l_old_FORMULA_LINES_tbl := p_old_FORMULA_LINES_tbl;
665
666 -- Formula
667
668 l_p_FORMULA_rec := l_FORMULA_rec; --[prarasto]
669 l_p_old_FORMULA_rec := l_old_FORMULA_rec; --[prarasto]
670
671 Formula
672 ( p_validation_level => p_validation_level
673 , p_control_rec => p_control_rec
674 , p_FORMULA_rec => l_p_FORMULA_rec
675 , p_old_FORMULA_rec => l_p_old_FORMULA_rec
676 , x_FORMULA_rec => l_FORMULA_rec
677 , x_old_FORMULA_rec => l_old_FORMULA_rec
678 );
679
680 -- Perform FORMULA group requests.
681
682 IF p_control_rec.process AND
683 (p_control_rec.process_entity = QP_GLOBALS.G_ENTITY_ALL OR
684 p_control_rec.process_entity = QP_GLOBALS.G_ENTITY_FORMULA)
685 THEN
686
687 NULL;
688
689 END IF;
690
691 -- Load parent key if missing and operation is create.
692
693 FOR I IN 1..l_FORMULA_LINES_tbl.COUNT LOOP
694
695 l_FORMULA_LINES_rec := l_FORMULA_LINES_tbl(I);
696
697 IF l_FORMULA_LINES_rec.operation = QP_GLOBALS.G_OPR_CREATE
698 AND (l_FORMULA_LINES_rec.price_formula_id IS NULL OR
699 l_FORMULA_LINES_rec.price_formula_id = FND_API.G_MISS_NUM)
700 THEN
701
702 -- Copy parent_id.
703
704 l_FORMULA_LINES_tbl(I).price_formula_id := l_FORMULA_rec.price_formula_id;
705 END IF;
706 END LOOP;
707
708 -- Formula_Liness
709
710 l_p_FORMULA_LINES_tbl := l_FORMULA_LINES_tbl; --[prarasto]
711 l_p_old_FORMULA_LINES_tbl := l_old_FORMULA_LINES_tbl; --[prarasto]
712
713 Formula_Liness
714 ( p_validation_level => p_validation_level
715 , p_control_rec => p_control_rec
716 , p_FORMULA_LINES_tbl => l_p_FORMULA_LINES_tbl
717 , p_old_FORMULA_LINES_tbl => l_p_old_FORMULA_LINES_tbl
718 , x_FORMULA_LINES_tbl => l_FORMULA_LINES_tbl
719 , x_old_FORMULA_LINES_tbl => l_old_FORMULA_LINES_tbl
720 );
721
722 -- Perform FORMULA_LINES group requests.
723
724 IF p_control_rec.process AND
725 (p_control_rec.process_entity = QP_GLOBALS.G_ENTITY_ALL OR
726 p_control_rec.process_entity = QP_GLOBALS.G_ENTITY_FORMULA_LINES)
727 THEN
728
729 NULL;
730
731 END IF;
732
733 -- Step 6. Perform Object group logic
734
735 IF p_control_rec.process AND
736 p_control_rec.process_entity = QP_GLOBALS.G_ENTITY_ALL
737 THEN
738
739 NULL;
740
741 END IF;
742
743 -- Done processing, load OUT parameters.
744
745 x_FORMULA_rec := l_FORMULA_rec;
746 x_FORMULA_LINES_tbl := l_FORMULA_LINES_tbl;
747
748 -- Clear API cache.
749
750 IF p_control_rec.clear_api_cache THEN
751
752 NULL;
753
754 END IF;
755
756 -- Clear API request tbl.
757
758 IF p_control_rec.clear_api_requests THEN
759
760 NULL;
761
762 END IF;
763
764 -- Derive return status.
765
766 x_return_status := FND_API.G_RET_STS_SUCCESS;
767
768 IF l_FORMULA_rec.return_status = FND_API.G_RET_STS_ERROR THEN
769 x_return_status := FND_API.G_RET_STS_ERROR;
770 END IF;
771
772 FOR I IN 1..l_FORMULA_LINES_tbl.COUNT LOOP
773
774 IF l_FORMULA_LINES_tbl(I).return_status = FND_API.G_RET_STS_ERROR THEN
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 END IF;
777
778 END LOOP;
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 oe_debug_pub.add('Leaving procedure Process_Price_Formula in Pvt package');
788
789 EXCEPTION
790
791 WHEN FND_API.G_EXC_ERROR THEN
792
793 x_return_status := FND_API.G_RET_STS_ERROR;
794
795 -- Get message count and data
796
797 OE_MSG_PUB.Count_And_Get
798 ( p_count => x_msg_count
799 , p_data => x_msg_data
800 );
801
802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
803
804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
805
806 -- Get message count and data
807
808 OE_MSG_PUB.Count_And_Get
809 ( p_count => x_msg_count
810 , p_data => x_msg_data
811 );
812
813 WHEN OTHERS THEN
814
815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
816
817 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
818 THEN
819 OE_MSG_PUB.Add_Exc_Msg
820 ( G_PKG_NAME
821 , 'Process_Price_Formula'
822 );
823 END IF;
824
825 -- Get message count and data
826
827 OE_MSG_PUB.Count_And_Get
828 ( p_count => x_msg_count
829 , p_data => x_msg_data
830 );
831
832 END Process_Price_Formula;
833
834 -- Start of Comments
835 -- API name Lock_Price_Formula
836 -- Type Private
837 -- Function
838 --
839 -- Pre-reqs
840 --
841 -- Parameters
842 --
843 -- Version Current version = 1.0
844 -- Initial version = 1.0
845 --
846 -- Notes
847 --
848 -- End of Comments
849
850 PROCEDURE Lock_Price_Formula
851 ( p_api_version_number IN NUMBER
852 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
853 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
854 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
855 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
856 , p_FORMULA_rec IN QP_Price_Formula_PUB.Formula_Rec_Type :=
857 QP_Price_Formula_PUB.G_MISS_FORMULA_REC
858 , p_FORMULA_LINES_tbl IN QP_Price_Formula_PUB.Formula_Lines_Tbl_Type :=
859 QP_Price_Formula_PUB.G_MISS_FORMULA_LINES_TBL
860 , x_FORMULA_rec OUT NOCOPY /* file.sql.39 change */ QP_Price_Formula_PUB.Formula_Rec_Type
861 , x_FORMULA_LINES_tbl OUT NOCOPY /* file.sql.39 change */ QP_Price_Formula_PUB.Formula_Lines_Tbl_Type
862 )
863 IS
864 l_api_version_number CONSTANT NUMBER := 1.0;
865 l_api_name CONSTANT VARCHAR2(30):= 'Lock_Price_Formula';
866 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
867 l_FORMULA_LINES_rec QP_Price_Formula_PUB.Formula_Lines_Rec_Type;
868 BEGIN
869
870 oe_debug_pub.add('Entering procedure Lock_Price_Formula in Pvt package');
871 -- Standard call to check for call compatibility
872
873 IF NOT FND_API.Compatible_API_Call
874 ( l_api_version_number
875 , p_api_version_number
876 , l_api_name
877 , G_PKG_NAME
878 )
879 THEN
880 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
881 END IF;
882
883 -- Initialize message list.
884
885 IF FND_API.to_Boolean(p_init_msg_list) THEN
886 OE_MSG_PUB.initialize;
887 END IF;
888
889 -- Set Savepoint
890
891 SAVEPOINT Lock_Price_Formula_PVT;
892
893 -- Lock FORMULA
894
895 IF p_FORMULA_rec.operation = QP_GLOBALS.G_OPR_LOCK THEN
896
897 QP_Formula_Util.Lock_Row
898 ( p_FORMULA_rec => p_FORMULA_rec
899 , x_FORMULA_rec => x_FORMULA_rec
900 , x_return_status => l_return_status
901 );
902
903 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
904 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
905 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
906 RAISE FND_API.G_EXC_ERROR;
907 END IF;
908
909
910 END IF;
911
912 -- Lock FORMULA_LINES
913
914 FOR I IN 1..p_FORMULA_LINES_tbl.COUNT LOOP
915
916 IF p_FORMULA_LINES_tbl(I).operation = QP_GLOBALS.G_OPR_LOCK THEN
917
918 QP_Formula_Lines_Util.Lock_Row
919 ( p_FORMULA_LINES_rec => p_FORMULA_LINES_tbl(I)
920 , x_FORMULA_LINES_rec => l_FORMULA_LINES_rec
921 , x_return_status => l_return_status
922 );
923
924 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
926 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
927 RAISE FND_API.G_EXC_ERROR;
928 END IF;
929
930 x_FORMULA_LINES_tbl(I) := l_FORMULA_LINES_rec;
931
932 END IF;
933
934 END LOOP;
935
936 -- Set return status
937
938 x_return_status := FND_API.G_RET_STS_SUCCESS;
939
940 -- Get message count and data
941
942 OE_MSG_PUB.Count_And_Get
943 ( p_count => x_msg_count
944 , p_data => x_msg_data
945 );
946
947 oe_debug_pub.add('Leaving procedure Lock_Price_Formula in Pvt package');
948
949 EXCEPTION
950
951 WHEN FND_API.G_EXC_ERROR THEN
952
953 x_return_status := FND_API.G_RET_STS_ERROR;
954
955 -- Get message count and data
956
957 OE_MSG_PUB.Count_And_Get
958 ( p_count => x_msg_count
959 , p_data => x_msg_data
960 );
961
962 -- Rollback
963
964 ROLLBACK TO Lock_Price_Formula_PVT;
965
966 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
967
968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
969
970 -- Get message count and data
971
972 OE_MSG_PUB.Count_And_Get
973 ( p_count => x_msg_count
974 , p_data => x_msg_data
975 );
976
977 -- Rollback
978
979 ROLLBACK TO Lock_Price_Formula_PVT;
980
981 WHEN OTHERS THEN
982
983 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
984
985 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
986 THEN
987 OE_MSG_PUB.Add_Exc_Msg
988 ( G_PKG_NAME
989 , 'Lock_Price_Formula'
990 );
991 END IF;
992
993 -- Get message count and data
994
995 OE_MSG_PUB.Count_And_Get
996 ( p_count => x_msg_count
997 , p_data => x_msg_data
998 );
999
1000 -- Rollback
1001
1002 ROLLBACK TO Lock_Price_Formula_PVT;
1003
1004 END Lock_Price_Formula;
1005
1006 -- Start of Comments
1007 -- API name Get_Price_Formula
1008 -- Type Private
1009 -- Function
1010 --
1011 -- Pre-reqs
1012 --
1013 -- Parameters
1014 --
1015 -- Version Current version = 1.0
1016 -- Initial version = 1.0
1017 --
1018 -- Notes
1019 --
1020 -- End of Comments
1021
1022 PROCEDURE Get_Price_Formula
1023 ( p_api_version_number IN NUMBER
1024 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1025 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1026 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1027 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1028 , p_price_formula_id IN NUMBER
1029 , x_FORMULA_rec OUT NOCOPY /* file.sql.39 change */ QP_Price_Formula_PUB.Formula_Rec_Type
1030 , x_FORMULA_LINES_tbl OUT NOCOPY /* file.sql.39 change */ QP_Price_Formula_PUB.Formula_Lines_Tbl_Type
1031 )
1032 IS
1033 l_api_version_number CONSTANT NUMBER := 1.0;
1034 l_api_name CONSTANT VARCHAR2(30):= 'Get_Price_Formula';
1035 l_FORMULA_rec QP_Price_Formula_PUB.Formula_Rec_Type;
1036 l_FORMULA_LINES_tbl QP_Price_Formula_PUB.Formula_Lines_Tbl_Type;
1037 BEGIN
1038
1039 oe_debug_pub.add('Entering procedure Get_Price_Formula in Pvt package');
1040 -- Standard call to check for call compatibility
1041
1042 IF NOT FND_API.Compatible_API_Call
1043 ( l_api_version_number
1044 , p_api_version_number
1045 , l_api_name
1046 , G_PKG_NAME
1047 )
1048 THEN
1049 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1050 END IF;
1051
1052 -- Initialize message list.
1053
1054 IF FND_API.to_Boolean(p_init_msg_list) THEN
1055 OE_MSG_PUB.initialize;
1056 END IF;
1057
1058 -- Get FORMULA ( parent = FORMULA )
1059
1060 l_FORMULA_rec := QP_Formula_Util.Query_Row
1061 ( p_price_formula_id => p_price_formula_id
1062 );
1063
1064 -- Get FORMULA_LINES ( parent = FORMULA )
1065
1066 l_FORMULA_LINES_tbl := QP_Formula_Lines_Util.Query_Rows
1067 ( p_price_formula_id => l_FORMULA_rec.price_formula_id
1068 );
1069
1070
1071 -- Load out parameters
1072
1073 x_FORMULA_rec := l_FORMULA_rec;
1074 x_FORMULA_LINES_tbl := l_FORMULA_LINES_tbl;
1075
1076 -- Set return status
1077
1078 x_return_status := FND_API.G_RET_STS_SUCCESS;
1079
1080 -- Get message count and data
1081
1082 OE_MSG_PUB.Count_And_Get
1083 ( p_count => x_msg_count
1084 , p_data => x_msg_data
1085 );
1086
1087 oe_debug_pub.add('Leaving procedure Get_Price_Formula in Pvt package');
1088
1089 EXCEPTION
1090
1091 WHEN FND_API.G_EXC_ERROR THEN
1092
1093 x_return_status := FND_API.G_RET_STS_ERROR;
1094
1095 -- Get message count and data
1096
1097 OE_MSG_PUB.Count_And_Get
1098 ( p_count => x_msg_count
1099 , p_data => x_msg_data
1100 );
1101
1102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1103
1104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1105
1106 -- Get message count and data
1107
1108 OE_MSG_PUB.Count_And_Get
1109 ( p_count => x_msg_count
1110 , p_data => x_msg_data
1111 );
1112
1113 WHEN OTHERS THEN
1114
1115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1116
1117 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1118 THEN
1119 OE_MSG_PUB.Add_Exc_Msg
1120 ( G_PKG_NAME
1121 , 'Get_Price_Formula'
1122 );
1123 END IF;
1124
1125 -- Get message count and data
1126
1127 OE_MSG_PUB.Count_And_Get
1128 ( p_count => x_msg_count
1129 , p_data => x_msg_data
1130 );
1131
1132 END Get_Price_Formula;
1133
1134 END QP_Price_Formula_PVT;