[Home] [Help]
PACKAGE BODY: APPS.OE_PRICING_CONT_PVT
Source
1 PACKAGE BODY OE_Pricing_Cont_PVT AS
2 /* $Header: OEXVPRCB.pls 120.3 2005/07/07 05:33:13 appldev ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Pricing_Cont_PVT';
7
8 FUNCTION Check_Delete_Agreement( p_Price_List_id IN NUMBER)
9 RETURN BOOLEAN;
10
11 FUNCTION Check_Order_Agreement( p_Agreement_id IN NUMBER)
12 RETURN BOOLEAN;
13
14 FUNCTION Check_Order_Lines_Agreement( p_Agreement_id IN NUMBER)
15 RETURN BOOLEAN;
16 -- Contract
17 -- This procedure is no longer used
18 /*
19 PROCEDURE Contract
20 ( p_validation_level IN NUMBER
21 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
22 , p_Contract_rec IN OE_Pricing_Cont_PUB.Contract_Rec_Type
23 , p_old_Contract_rec IN OE_Pricing_Cont_PUB.Contract_Rec_Type
24 , x_Contract_rec OUT NOCOPY OE_Pricing_Cont_PUB.Contract_Rec_Type --file.sql.39 change
25 , x_old_Contract_rec OUT NOCOPY OE_Pricing_Cont_PUB.Contract_Rec_Type --file.sql.39 change
26 )
27 IS
28 l_return_status VARCHAR2(1);
29 l_control_rec OE_GLOBALS.Control_Rec_Type;
30 l_Contract_rec OE_Pricing_Cont_PUB.Contract_Rec_Type := p_Contract_rec;
31 l_old_Contract_rec OE_Pricing_Cont_PUB.Contract_Rec_Type := p_old_Contract_rec;
32 l_p_Contract_rec OE_Pricing_Cont_PUB.Contract_Rec_Type; --[prarasto]
33 BEGIN
34
35 oe_debug_pub.add('------------------------------------------------');
36 oe_debug_pub.add('Entering Contract');
37
38 -- Load API control record
39
40 l_control_rec := OE_GLOBALS.Init_Control_Rec
41 ( p_operation => l_Contract_rec.operation
42 , p_control_rec => p_control_rec
43 );
44
45 -- Set record return status.
46
47 l_Contract_rec.return_status := FND_API.G_RET_STS_SUCCESS;
48
49 -- Prepare record.
50
51 IF l_Contract_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
52
53 l_Contract_rec.db_flag := FND_API.G_FALSE;
54
55 -- Set missing old record elements to NULL.
56
57 l_old_Contract_rec :=
58 OE_Contract_Util.Convert_Miss_To_Null (l_old_Contract_rec);
59
60 ELSIF (l_Contract_rec.operation = OE_GLOBALS.G_OPR_UPDATE
61 OR l_Contract_rec.operation = OE_GLOBALS.G_OPR_DELETE )
62 THEN
63
64 l_Contract_rec.db_flag := FND_API.G_TRUE;
65
66 -- Query Old if missing
67
68 IF l_old_Contract_rec.pricing_contract_id = FND_API.G_MISS_NUM
69 THEN
70
71 l_old_Contract_rec := OE_Contract_Util.Query_Row
72 ( p_pricing_contract_id => l_Contract_rec.pricing_contract_id
73 );
74
75 ELSE
76
77 -- Set missing old record elements to NULL.
78
79 l_old_Contract_rec :=
80 OE_Contract_Util.Convert_Miss_To_Null (l_old_Contract_rec);
81
82 END IF;
83
84 -- Complete new record from old
85
86 l_Contract_rec := OE_Contract_Util.Complete_Record
87 ( p_Contract_rec => l_Contract_rec
88 , p_old_Contract_rec => l_old_Contract_rec
89 );
90
91 END IF;
92
93 IF ( l_contract_rec.operation = OE_GLOBALS.G_OPR_UPDATE
94 OR l_contract_rec.operation = OE_GLOBALS.G_OPR_CREATE
95 OR l_contract_rec.operation = OE_GLOBALS.G_OPR_DELETE) THEN
96
97 -- Attribute level validation.
98
99 IF l_control_rec.default_attributes
100 OR l_control_rec.change_attributes
101 THEN
102
103 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
104
105 OE_Validate_Contract.Attributes
106 ( x_return_status => l_return_status
107 , p_Contract_rec => l_Contract_rec
108 , p_old_Contract_rec => l_old_Contract_rec
109 );
110
111 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
113 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
114 RAISE FND_API.G_EXC_ERROR;
115 END IF;
116
117 END IF;
118
119 END IF;
120
121 -- Clear dependent attributes.
122
123 IF l_control_rec.change_attributes THEN
124
125 l_p_Contract_rec := l_Contract_rec; --[prarasto]
126
127 OE_Contract_Util.Clear_Dependent_Attr
128 ( p_Contract_rec => l_p_Contract_rec
129 , p_old_Contract_rec => l_old_Contract_rec
130 , x_Contract_rec => l_Contract_rec
131 );
132
133 END IF;
134
135 -- Default missing attributes
136
137 IF l_control_rec.default_attributes
138 OR l_control_rec.change_attributes
139 THEN
140
141 l_p_Contract_rec := l_Contract_rec; --[prarasto]
142
143 OE_Default_Contract.Attributes
144 ( p_Contract_rec => l_p_Contract_rec
145 , x_Contract_rec => l_Contract_rec
146 );
147
148
149 END IF;
150
151 -- Apply attribute changes
152
153 IF l_control_rec.default_attributes
154 OR l_control_rec.change_attributes
155 THEN
156
157
158 l_p_Contract_rec := l_Contract_rec; --[prarasto]
159
160 OE_Contract_Util.Apply_Attribute_Changes
161 ( p_Contract_rec => l_p_Contract_rec
162 , p_old_Contract_rec => l_old_Contract_rec
163 , x_Contract_rec => l_Contract_rec
164 );
165
166 END IF;
167
168 -- Entity level validation.
169
170 IF l_control_rec.validate_entity THEN
171
172 IF l_Contract_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
173
174 OE_Validate_Contract.Entity_Delete
175 ( x_return_status => l_return_status
176 , p_Contract_rec => l_Contract_rec
177 );
178
179 ELSE
180
181 OE_Validate_Contract.Entity
182 ( x_return_status => l_return_status
183 , p_Contract_rec => l_Contract_rec
184 , p_old_Contract_rec => l_old_Contract_rec
185 );
186
187 END IF;
188
189 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
192 RAISE FND_API.G_EXC_ERROR;
193 END IF;
194
195 END IF;
196
197 -- Step 4. Write to DB
198
199 IF l_control_rec.write_to_db THEN
200
201 IF l_Contract_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
202
203 OE_Contract_Util.Delete_Row
204 ( p_pricing_contract_id => l_Contract_rec.pricing_contract_id
205 );
206
207 ELSE
208
209 -- Get Who Information
210
211 l_Contract_rec.last_update_date := SYSDATE;
212 l_Contract_rec.last_updated_by := FND_GLOBAL.USER_ID;
213 l_Contract_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
214
215 IF l_Contract_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
216
217 OE_Contract_Util.Update_Row (l_Contract_rec);
218
219 ELSIF l_Contract_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
220
221 l_Contract_rec.creation_date := SYSDATE;
222 l_Contract_rec.created_by := FND_GLOBAL.USER_ID;
223
224 OE_Contract_Util.Insert_Row (l_Contract_rec);
225
226 END IF;
227
228 END IF;
229
230 END IF;
231 END IF;
232 -- Load OUT parameters
233
234 x_Contract_rec := l_Contract_rec;
235 x_old_Contract_rec := l_old_Contract_rec;
236
237 oe_debug_pub.add('Exiting Contract');
238 oe_debug_pub.add('------------------------------------------------');
239
240 EXCEPTION
241
242 WHEN FND_API.G_EXC_ERROR THEN
243
244 l_Contract_rec.return_status := FND_API.G_RET_STS_ERROR;
245 x_Contract_rec := l_Contract_rec;
246 x_old_Contract_rec := l_old_Contract_rec;
247 RAISE;
248
249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
250
251 l_Contract_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252 x_Contract_rec := l_Contract_rec;
253 x_old_Contract_rec := l_old_Contract_rec;
254
255 RAISE;
256
257 WHEN OTHERS THEN
258
259 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
260 THEN
261 OE_MSG_PUB.Add_Exc_Msg
262 ( G_PKG_NAME
263 , 'Contract'
264 );
265 END IF;
266
267 l_Contract_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
268 x_Contract_rec := l_Contract_rec;
269 x_old_Contract_rec := l_old_Contract_rec;
270
271 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
272
273 END Contract;
274 -- End Procedure not required
275 */
276
277 /*commented this and exposed this procedure in the specification for agreements upgrade
278 PROCEDURE Create_Agreement_Qualifier
279 (p_list_header_id IN NUMBER,
280 p_old_list_header_id IN NUMBER,
281 p_Agreement_id IN NUMBER,
282 p_operation IN VARCHAR2,
283 x_return_status OUT NOCOPY VARCHAR2); --file.sql.39 change
284 */
285
286 -- Agreement
287
288 PROCEDURE Agreement
289 ( p_validation_level IN NUMBER
290 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
291 , p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
292 , p_old_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
293 , x_Agreement_rec OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Agreement_Rec_Type
294 , x_old_Agreement_rec OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Agreement_Rec_Type
295 )
296 IS
297 l_return_status VARCHAR2(1);
298 l_Agreement_Delete_Flag BOOLEAN;
299 l_Agreement_Lines_Delete_Flag BOOLEAN;
300 l_Price_List_Exists_Flag BOOLEAN;
301 l_control_rec OE_GLOBALS.Control_Rec_Type;
302 --l_control_rec QP_GLOBALS.Control_Rec_Type;
303 l_Agreement_rec OE_Pricing_Cont_PUB.Agreement_Rec_Type := p_Agreement_rec;
304 l_old_Agreement_rec OE_Pricing_Cont_PUB.Agreement_Rec_Type := p_old_Agreement_rec;
305 l_old_price_list_id QP_LIST_HEADERS_B.LIST_HEADER_ID%TYPE;
306 l_count_agr_qual NUMBER;
307
308 /* For creating qualifiers */
309 l_price_list_type_code QP_LIST_HEADERS_B.LIST_TYPE_CODE%TYPE;
310 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type ;
311 l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
312 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type ;
313 l_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type ;
314 l_QUALIFIER_RULES_val_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Val_Rec_Type;
315
316 l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type ;
317 l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
318 l_x_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type ;
319 l_x_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type ;
320 l_x_QUALIFIER_RULES_val_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Val_Rec_Type;
321 l_return_values VARCHAR2(1) := FND_API.G_FALSE;
322 l_commit VARCHAR2(1) := FND_API.G_FALSE;
323 l_msg_count NUMBER;
324 l_msg_data VARCHAR2(250);
325
326 l_p_Agreement_rec OE_Pricing_Cont_PUB.Agreement_Rec_Type; --[prarasto]
327 BEGIN
328
329 oe_debug_pub.add('------------------------------------------------');
330 oe_debug_pub.add('Entering Agreement');
331
332 -- Load API control record
333
334 l_control_rec := OE_GLOBALS.Init_Control_Rec
335 ( p_operation => l_Agreement_rec.operation
336 , p_control_rec => p_control_rec
337 );
338
339 -- Set record return status.
340
341 l_Agreement_rec.return_status := FND_API.G_RET_STS_SUCCESS;
342
343 -- Prepare record.
344
345 IF l_Agreement_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
346
347 l_Agreement_rec.db_flag := FND_API.G_FALSE;
348
349 -- Set missing old record elements to NULL.
350
351 l_old_Agreement_rec :=
352 OE_Agreement_Util.Convert_Miss_To_Null (l_old_Agreement_rec);
353
354 ELSIF l_Agreement_rec.operation = OE_GLOBALS.G_OPR_UPDATE
355 OR l_Agreement_rec.operation = OE_GLOBALS.G_OPR_DELETE
356 THEN
357
358 l_Agreement_rec.db_flag := FND_API.G_TRUE;
359
360 -- Query Old if missing
361
362 IF l_old_Agreement_rec.agreement_id = FND_API.G_MISS_NUM
363 THEN
364
365 l_old_Agreement_rec := OE_Agreement_Util.Query_Row
366 ( p_agreement_id => l_Agreement_rec.agreement_id
367 );
368
369 ELSE
370
371 -- Set missing old record elements to NULL.
372
373 l_old_Agreement_rec :=
374 OE_Agreement_Util.Convert_Miss_To_Null (l_old_Agreement_rec);
375
376 END IF;
377
378 -- Complete new record from old
379
380
381
382
383 l_Agreement_rec := OE_Agreement_Util.Complete_Record
384 ( p_Agreement_rec => l_Agreement_rec
385 , p_old_Agreement_rec => l_old_Agreement_rec
386 );
387
388 END IF;
389
390 IF ( l_agreement_rec.operation = OE_GLOBALS.G_OPR_UPDATE
391 OR l_agreement_rec.operation = OE_GLOBALS.G_OPR_CREATE
392 OR l_agreement_rec.operation = OE_GLOBALS.G_OPR_DELETE) THEN
393
394 -- Attribute level validation.
395
396 IF l_control_rec.default_attributes
397 OR l_control_rec.change_attributes
398 THEN
399
400 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
401
402 OE_Validate_Agreement.Attributes
403 ( x_return_status => l_return_status
404 , p_Agreement_rec => l_Agreement_rec
405 , p_old_Agreement_rec => l_old_Agreement_rec
406 );
407
408 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
410 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
411 RAISE FND_API.G_EXC_ERROR;
412 END IF;
413
414 END IF;
415
416 END IF;
417
418 -- Clear dependent attributes.
419
420 IF l_control_rec.change_attributes THEN
421
422 l_p_Agreement_rec := l_Agreement_rec; --[prarasto]
423
424 OE_Agreement_Util.Clear_Dependent_Attr
425 ( p_Agreement_rec => l_p_Agreement_rec
426 , p_old_Agreement_rec => l_old_Agreement_rec
427 , x_Agreement_rec => l_Agreement_rec
428 );
429
430 END IF;
431
432 -- Default missing attributes
433
434 IF l_control_rec.default_attributes
435 OR l_control_rec.change_attributes
436 THEN
437
438 l_p_Agreement_rec := l_Agreement_rec; --[prarasto]
439
440 OE_Default_Agreement.Attributes
441 ( p_Agreement_rec => l_p_Agreement_rec
442 , x_Agreement_rec => l_Agreement_rec
443 );
444
445 END IF;
446
447 -- Apply attribute changes
448
449 IF l_control_rec.default_attributes
450 OR l_control_rec.change_attributes
451 THEN
452
453 /*change made by spgopal 08/14/00 for duplicate qualifiers when updating
454 price list info on agreement*/
455
456 oe_debug_pub.add('old_list_id'||to_char(l_old_agreement_rec.price_list_id)||'new_list_id'||to_char(l_agreement_rec.price_list_id));
457
458 l_old_price_list_id := l_old_Agreement_rec.price_list_id;
459
460 l_p_Agreement_rec := l_Agreement_rec; --[prarasto]
461
462 OE_Agreement_Util.Apply_Attribute_Changes
463 ( p_Agreement_rec => l_p_Agreement_rec
464 , p_old_Agreement_rec => l_old_Agreement_rec
465 , x_Agreement_rec => l_Agreement_rec
466 );
467
468 END IF;
469
470 -- Entity level validation.
471
472 IF l_control_rec.validate_entity THEN
473
474 IF l_Agreement_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
475
476 OE_Validate_Agreement.Entity_Delete
477 ( x_return_status => l_return_status
478 , p_Agreement_rec => l_Agreement_rec
479 );
480
481 ELSE
482
483 IF l_Agreement_rec.operation = OE_GLOBALS.G_OPR_CREATE then
484
485 OE_Validate_Agreement.Entity
486 ( x_return_status => l_return_status
487 , p_Agreement_rec => l_Agreement_rec
488 , p_old_Agreement_rec => l_old_Agreement_rec
489 );
490 END if;
491
492 END IF;
493
494 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
497 RAISE FND_API.G_EXC_ERROR;
498 END IF;
499
500 END IF;
501
502 -- Step 4. Write to DB
503
504 IF l_control_rec.write_to_db THEN
505
506 IF l_Agreement_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
507
508
509 /* Check the price List associated with an agreement */
510 /* Bug 2321498 Commented following code as price List should not be deleted */
511 /*
512 oe_debug_pub.add('Geresh Before Check Delete Agreement');
513 if NOT ( Check_Delete_Agreement( p_Price_List_id => l_Agreement_rec.price_list_id )) THEN
514 l_Price_List_Exists_Flag := FALSE;
515 else
516 l_Price_List_Exists_Flag := TRUE;
517
518 end if;
519
520 oe_debug_pub.add('Geresh After Check Delete Agreement'); */
521 /* Bug 2321498 : Added following line to always set the flag to false */
522 l_Price_List_Exists_Flag := TRUE;
523
524
525 /* Check to see if an agreement used an in an Order */
526
527 if NOT ( Check_Order_Agreement(p_Agreement_id => l_Agreement_rec.agreement_id)) THEN
528 l_Agreement_Delete_Flag := FALSE;
529 else
530 l_Agreement_Delete_Flag := TRUE;
531 end if;
532
533 /* Check to see Agreement Used in Order Lines */
534 if NOT ( Check_Order_Lines_Agreement(p_Agreement_id => l_Agreement_rec.agreement_id)) THEN
535 l_Agreement_Lines_Delete_Flag := FALSE;
536 else
537 l_Agreement_Lines_Delete_Flag := TRUE;
538 end if;
539
540 OE_Agreement_Util.Delete_Row
541 ( x_return_status => l_return_status
542 , p_agreement_id => l_Agreement_rec.agreement_id
543 , p_Price_List_Exists_Flag => l_Price_list_Exists_Flag
544 , p_Agreement_Delete_Flag => l_Agreement_Delete_Flag
545 , p_Agreement_Lines_Delete_Flag => l_Agreement_Lines_Delete_Flag
546 );
547 /* Added following code for bug 2321498 */
548
549 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
551 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
552 RAISE FND_API.G_EXC_ERROR;
553 END IF;
554
555 ELSE
556
557 -- Get Who Information
558
559 l_Agreement_rec.last_update_date := SYSDATE;
560 l_Agreement_rec.last_updated_by := FND_GLOBAL.USER_ID;
561 l_Agreement_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
562
563 IF l_Agreement_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
564
565 OE_Agreement_Util.Update_Row (l_Agreement_rec);
566 -- update agreement qualifier
567
568 /*Changes made by spgopal for AGR fix conditionally create qualifiers 08/14/00*/
569
570 begin --Begin, Exception and End stmts added for OKC
571 select list_type_code into l_price_list_type_code from
572 qp_list_headers_vl where
573 list_header_id = l_Agreement_rec.price_list_id;
574 exception
575 when others then
576 l_price_list_type_code := NULL;
577 end;
578
579
580 IF l_price_list_type_code = 'AGR' THEN
581
582 --Create agreement qualifier for AGR type price lists only
583
584 oe_debug_pub.add('before update create_agr_qual');
585
586
587 Create_Agreement_Qualifier(l_Agreement_rec.price_list_id
588 ,l_old_price_list_id
589 ,l_Agreement_rec.Agreement_id
590 ,l_Agreement_rec.Operation
591 ,l_return_status);
592
593 oe_debug_pub.add('after update create_agr_qual');
594
595 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
597 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
598 RAISE FND_API.G_EXC_ERROR;
599 END IF;
600
601 END IF;
602
603 --END IF;
604
605 ELSIF l_Agreement_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
606
607 l_Agreement_rec.creation_date := SYSDATE;
608 l_Agreement_rec.created_by := FND_GLOBAL.USER_ID;
609
610 OE_Agreement_Util.Insert_Row (l_Agreement_rec);
611
612
613 /*Changes made by spgopal for AGR fix conditionally create qualifiers 08/14/00*/
614
615 begin --Begin, Exception and End added for OKC
616 select list_type_code into l_price_list_type_code from
617 qp_list_headers_vl where
618 list_header_id = l_Agreement_rec.price_list_id;
619 exception
620 when others then
621 l_price_list_type_code := NULL;
622 end;
623
624
625
626 IF l_price_list_type_code = 'AGR' THEN
627
628 --Create agreement qualifier for AGR type price lists only
629
630 oe_debug_pub.add('before insert create_agr_qual');
631
632 Create_Agreement_Qualifier(l_Agreement_rec.price_list_id
633 ,l_old_price_list_id
634 ,l_Agreement_rec.Agreement_id
635 ,l_Agreement_rec.Operation
636 ,l_return_status);
637
638 oe_debug_pub.add('after insert create_agr_qual');
639 END IF;
640
641 END IF;
642
643 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
644 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
645 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
646 RAISE FND_API.G_EXC_ERROR;
647 END IF;
648
649
650 /* Creating a quaklifier for a price list */
651
652 /*Changes made by spgopal for AGR fix conditionally create qualifiers 08/10/00*/
653
654 /*
655 select list_type_code into l_price_list_type_code from
656 qp_list_headers_vl where
657 list_header_id = l_Agreement_rec.price_list_id;
658
659
660 IF l_price_list_type_code = 'AGR' THEN
661
662 --Create agreement qualifier for AGR type price lists only
663
664 null;
665
666 */
667
668 /*changes made by spgopal 08/14/00 included the foll in procedure create_agreement_qualifier below*/
669 /*
670 l_qualifiers_rec.list_header_id := l_Agreement_rec.price_list_id ;
671 l_qualifiers_rec.qualifier_attr_value := l_Agreement_rec.agreement_id;
672 l_qualifiers_rec.qualifier_context := 'CUSTOMER';
673 l_qualifiers_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE7';
674 l_qualifiers_rec.operation := OE_GLOBALS.G_OPR_CREATE;
675
676 l_qualifiers_rec.qualifier_grouping_no := l_Agreement_rec.agreement_id;
677
678
679 l_qualifiers_rec.operation := OE_GLOBALS.G_OPR_CREATE;
680 l_qualifiers_tbl(1) := l_qualifiers_rec;
681
682 QP_Qualifier_Rules_PVT.Process_Qualifier_Rules
683 ( p_api_version_number => 1.0
684 , p_init_msg_list => FND_API.G_TRUE
685 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
686 , p_commit => FND_API.G_FALSE
687 , x_return_status => l_return_status
688 , x_msg_count => l_msg_count
689 , x_msg_data => l_msg_data
690 , p_control_rec => l_control_rec
691 , p_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
692 -- , p_QUALIFIER_RULES_val_rec => l_QUALIFIER_RULES_val_rec
693 , p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
694 -- , p_QUALIFIERS_val_tbl => l_QUALIFIERS_val_tbl
695 , x_QUALIFIER_RULES_rec => l_x_QUALIFIER_RULES_rec
696 -- , x_QUALIFIER_RULES_val_rec => l_x_QUALIFIER_RULES_val_rec
697 , x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
698 -- , x_QUALIFIERS_val_tbl => l_x_QUALIFIERS_val_tbl
699 );
700 */
701
702 null;
703
704 END IF;
705
706 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
708 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
709 RAISE FND_API.G_EXC_ERROR;
710 END IF;
711
712
713 END IF;
714
715 -- END IF;
716 END IF;
717 -- Load OUT parameters
718
719 x_Agreement_rec := l_Agreement_rec;
720 x_old_Agreement_rec := l_old_Agreement_rec;
721
722 oe_debug_pub.add('Exiting Agreement');
723 oe_debug_pub.add('------------------------------------------------');
724
725 EXCEPTION
726
727 WHEN FND_API.G_EXC_ERROR THEN
728
729 l_Agreement_rec.return_status := FND_API.G_RET_STS_ERROR;
730 x_Agreement_rec := l_Agreement_rec;
731 x_old_Agreement_rec := l_old_Agreement_rec;
732 RAISE;
733
734 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
735
736 l_Agreement_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737 x_Agreement_rec := l_Agreement_rec;
738 x_old_Agreement_rec := l_old_Agreement_rec;
739
740 RAISE;
741
742 WHEN OTHERS THEN
743
744 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
745 THEN
746 OE_MSG_PUB.Add_Exc_Msg
747 ( G_PKG_NAME
748 , 'Agreement'
749 );
750 END IF;
751
752 l_Agreement_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753 x_Agreement_rec := l_Agreement_rec;
754 x_old_Agreement_rec := l_old_Agreement_rec;
755
756 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
757
758 END Agreement;
759
760 -- Price_Lheader
761
762 PROCEDURE Price_Lheader
763 ( p_validation_level IN NUMBER
764 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
765 , p_Price_LHeader_rec IN OE_Price_List_PUB.Price_List_Rec_Type
766 , p_old_Price_LHeader_rec IN OE_Price_List_PUB.Price_List_Rec_Type
767 , x_Price_LHeader_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
768 , x_old_Price_LHeader_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
769 )
770 IS
771 l_return_status VARCHAR2(1);
772 l_control_rec OE_GLOBALS.Control_Rec_Type;
773 l_Price_LHeader_rec OE_Price_List_PUB.Price_List_Rec_Type := p_Price_LHeader_rec;
774 l_old_Price_LHeader_rec OE_Price_List_PUB.Price_List_Rec_Type := p_old_Price_LHeader_rec;
775
776 l_p_Price_LHeader_rec OE_Price_List_PUB.Price_List_Rec_Type; --[prarasto]
777 BEGIN
778
779
780 oe_debug_pub.add('------------------------------------------------');
781 oe_debug_pub.add('Entering Price_LHeader');
782 -- Load API control record
783
784 l_control_rec := OE_GLOBALS.Init_Control_Rec
785 ( p_operation => l_Price_LHeader_rec.operation
786 , p_control_rec => p_control_rec
787 );
788 l_Price_LHeader_rec.return_status := FND_API.G_RET_STS_SUCCESS;
789
790 -- Set record return status.
791
792
793 -- Prepare record.
794
795 IF l_Price_LHeader_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
796
797 l_Price_LHeader_rec.db_flag := FND_API.G_FALSE;
798
799 -- Set missing old record elements to NULL.
800
801 l_old_Price_LHeader_rec :=
802 OE_Price_List_Util.Convert_Miss_To_Null (l_old_Price_LHeader_rec);
803
804 ELSIF l_Price_LHeader_rec.operation = OE_GLOBALS.G_OPR_UPDATE
805 OR l_Price_LHeader_rec.operation = OE_GLOBALS.G_OPR_DELETE
806 THEN
807
808 l_Price_LHeader_rec.db_flag := FND_API.G_TRUE;
809
810 -- Query Old if missing
811
812 IF l_old_Price_LHeader_rec.name = FND_API.G_MISS_CHAR
813 OR l_old_Price_LHeader_rec.price_list_id = FND_API.G_MISS_NUM
814 THEN
815
816 l_old_Price_LHeader_rec := OE_Price_List_Util.Query_Row
817 ( p_name => l_Price_LHeader_rec.name ,
818 p_price_list_id => l_Price_LHeader_rec.price_list_id
819 );
820
821 ELSE
822
823 -- Set missing old record elements to NULL.
824
825 l_old_Price_LHeader_rec :=
826 OE_Price_List_Util.Convert_Miss_To_Null (l_old_Price_LHeader_rec);
827
828 END IF;
829
830 -- Complete new record from old
831
832 l_Price_LHeader_rec := OE_Price_List_Util.Complete_Record
833 ( p_PRICE_LIST_rec => l_Price_LHeader_rec
834 , p_old_PRICE_LIST_rec => l_old_Price_LHeader_rec
835 );
836
837 END IF;
838
839
840 IF ( l_Price_LHeader_rec.operation = OE_GLOBALS.G_OPR_UPDATE
841 OR l_Price_LHeader_rec.operation = OE_GLOBALS.G_OPR_CREATE
842 OR l_Price_LHeader_rec.operation = OE_GLOBALS.G_OPR_DELETE) THEN
843 -- Attribute level validation.
844
845 IF l_control_rec.default_attributes
846 OR l_control_rec.change_attributes
847 THEN
848
849 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
850
851 OE_Validate_Price_List.Attributes
852 ( x_return_status => l_return_status
853 , p_Price_LIST_rec => l_Price_LHeader_rec
854 , p_old_Price_LIST_rec => l_old_Price_LHeader_rec
855 );
856
857 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
859 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
860 RAISE FND_API.G_EXC_ERROR;
861 END IF;
862
863 END IF;
864
865 END IF;
866
867 -- Clear dependent attributes.
868 IF l_control_rec.change_attributes THEN
869
870 l_p_Price_LHeader_rec := l_Price_LHeader_rec; --[prarasto]
871
872 OE_Price_List_Util.Clear_Dependent_Attr
873 ( p_Price_list_rec => l_p_Price_LHeader_rec
874 , p_old_Price_list_rec => l_old_Price_LHeader_rec
875 , x_Price_list_rec => l_Price_LHeader_rec
876 );
877
878 END IF;
879
880 -- Default missing attributes
881
882 IF l_control_rec.default_attributes
883 OR l_control_rec.change_attributes
884 THEN
885
886 l_p_Price_LHeader_rec := l_Price_LHeader_rec; --[prarasto]
887
888 OE_Default_Price_List.Attributes
889 ( p_Price_list_rec => l_p_Price_LHeader_rec
890 , x_Price_list_rec => l_Price_LHeader_rec
891 );
892
893 END IF;
894
895 -- Apply attribute changes
896
897 IF l_control_rec.default_attributes
898 OR l_control_rec.change_attributes
899 THEN
900
901 l_p_Price_LHeader_rec := l_Price_LHeader_rec; --[prarasto]
902
903 OE_Price_List_Util.Apply_Attribute_Changes
904 ( p_Price_LIST_rec => l_p_Price_LHeader_rec
905 , p_old_Price_list_rec => l_old_Price_LHeader_rec
906 , x_Price_list_rec => l_Price_LHeader_rec
907 );
908
909 END IF;
910
911 -- Entity level validation.
912
913 IF l_control_rec.validate_entity THEN
914
915 IF l_Price_LHeader_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
916
917 OE_Validate_Price_List.Entity_Delete
918 ( x_return_status => l_return_status
919 , p_Price_list_rec => l_Price_LHeader_rec
920 );
921
922 ELSE
923
924 OE_Validate_Price_List.Entity
925 ( x_return_status => l_return_status
926 , p_Price_list_rec => l_Price_LHeader_rec
927 , p_old_Price_list_rec => l_old_Price_LHeader_rec
928 );
929
930 END IF;
931
932 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
933 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
934 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
935 RAISE FND_API.G_EXC_ERROR;
936 END IF;
937
938 END IF;
939
940 -- Step 4. Write to DB
941
942 IF l_control_rec.write_to_db THEN
943
944 IF l_Price_LHeader_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
945
946 OE_Price_List_Util.Delete_Row
947 ( p_name => l_Price_LHeader_rec.name,
948 p_price_list_id => l_Price_LHeader_rec.price_list_id
949 );
950
951 ELSE
952
953 -- Get Who Information
954
955 l_Price_LHeader_rec.last_update_date := SYSDATE;
956 l_Price_LHeader_rec.last_updated_by := FND_GLOBAL.USER_ID;
957 l_Price_LHeader_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
958
959 IF l_Price_LHeader_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
960
961 OE_Price_List_Util.Update_Row (l_Price_LHeader_rec);
962
963 ELSIF l_Price_LHeader_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
964
965 l_Price_LHeader_rec.creation_date := SYSDATE;
966 l_Price_LHeader_rec.created_by := FND_GLOBAL.USER_ID;
967
968 OE_Price_List_Util.Insert_Row (l_Price_LHeader_rec);
969
970 END IF;
971
972 END IF;
973
974 END IF;
975
976 END IF;
977 -- Load OUT parameters
978
979 x_Price_LHeader_rec := l_Price_LHeader_rec;
980 x_old_Price_LHeader_rec := l_old_Price_LHeader_rec;
981
982 oe_debug_pub.add('Exiting Price_LHeader');
983 oe_debug_pub.add('------------------------------------------------');
984
985 EXCEPTION
986
987 WHEN FND_API.G_EXC_ERROR THEN
988
989 l_Price_LHeader_rec.return_status := FND_API.G_RET_STS_ERROR;
990 x_Price_LHeader_rec := l_Price_LHeader_rec;
991 x_old_Price_LHeader_rec := l_old_Price_LHeader_rec;
992 RAISE;
993
994 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
995
996 l_Price_LHeader_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
997 x_Price_LHeader_rec := l_Price_LHeader_rec;
998 x_old_Price_LHeader_rec := l_old_Price_LHeader_rec;
999
1000 RAISE;
1001
1002 WHEN OTHERS THEN
1003
1004 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1005 THEN
1006 OE_MSG_PUB.Add_Exc_Msg
1007 ( G_PKG_NAME
1008 , 'Price_Lheader'
1009 );
1010 END IF;
1011
1012 l_Price_LHeader_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1013 x_Price_LHeader_rec := l_Price_LHeader_rec;
1014 x_old_Price_LHeader_rec := l_old_Price_LHeader_rec;
1015
1016 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1017
1018 END Price_Lheader;
1019
1020
1021 -- Price_Llines
1022
1023 PROCEDURE Price_Llines
1024 ( p_validation_level IN NUMBER
1025 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
1026 , p_Price_LLine_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type
1027 , p_old_Price_LLine_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type
1028 , x_Price_LLine_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
1029 , x_old_Price_LLine_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
1030 )
1031 IS
1032 l_return_status VARCHAR2(1);
1033 l_control_rec OE_GLOBALS.Control_Rec_Type;
1034 l_Price_LLine_rec OE_Price_List_PUB.Price_List_Line_Rec_Type;
1035 l_Price_LLine_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type;
1036 l_old_Price_LLine_rec OE_Price_List_PUB.Price_List_Line_Rec_Type;
1037 l_old_Price_LLine_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type;
1038
1039 l_p_Price_LLine_rec OE_Price_List_PUB.Price_List_Line_Rec_Type; --[prarasto]
1040
1041 BEGIN
1042
1043 oe_debug_pub.add('------------------------------------------------');
1044 oe_debug_pub.add('Entering Price_LLines');
1045
1046 -- Init local table variables.
1047
1048 l_Price_LLine_tbl := p_Price_LLine_tbl;
1049 l_old_Price_LLine_tbl := p_old_Price_LLine_tbl;
1050
1051 oe_debug_pub.add('No of Records in Price_List_Lines table : ' || to_char(l_Price_LLine_tbl.COUNT));
1052
1053 FOR I IN 1..l_Price_LLine_tbl.COUNT LOOP
1054 BEGIN
1055
1056 -- Load local records.
1057
1058 l_Price_LLine_rec := l_Price_LLine_tbl(I);
1059
1060 IF l_old_Price_LLine_tbl.EXISTS(I) THEN
1061 l_old_Price_LLine_rec := l_old_Price_LLine_tbl(I);
1062 ELSE
1063 l_old_Price_LLine_rec := OE_Price_List_PUB.G_MISS_PRICE_List_Line_REC;
1064 END IF;
1065
1066 -- Load API control record
1067
1068 l_control_rec := OE_GLOBALS.Init_Control_Rec
1069 ( p_operation => l_Price_LLine_rec.operation
1070 , p_control_rec => p_control_rec
1071 );
1072
1073 -- Set record return status.
1074
1075 l_Price_LLine_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1076
1077 -- Prepare record.
1078
1079 IF l_Price_LLine_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
1080
1081 l_Price_LLine_rec.db_flag := FND_API.G_FALSE;
1082
1083 -- Set missing old record elements to NULL.
1084
1085 l_old_Price_LLine_rec :=
1086 OE_Price_List_Line_Util.Convert_Miss_To_Null (l_old_Price_LLine_rec);
1087
1088 ELSIF l_Price_LLine_rec.operation = OE_GLOBALS.G_OPR_UPDATE
1089 OR l_Price_LLine_rec.operation = OE_GLOBALS.G_OPR_DELETE
1090 THEN
1091
1092 l_Price_LLine_rec.db_flag := FND_API.G_TRUE;
1093
1094 -- Query Old if missing
1095
1096 IF l_old_Price_LLine_rec.price_list_line_id = FND_API.G_MISS_NUM
1097 THEN
1098
1099 l_old_Price_LLine_rec := OE_Price_List_Line_Util.Query_Row
1100 ( p_price_list_line_id => l_Price_LLine_rec.price_list_line_id
1101 , p_price_list_id => l_Price_LLine_rec.price_list_id
1102 );
1103
1104 ELSE
1105
1106 -- Set missing old record elements to NULL.
1107
1108 l_old_Price_LLine_rec :=
1109 OE_Price_List_Line_Util.Convert_Miss_To_Null (l_old_Price_LLine_rec);
1110
1111 END IF;
1112
1113 -- Complete new record from old
1114
1115 l_Price_LLine_rec := OE_Price_List_Line_Util.Complete_Record
1116 ( p_PRICE_LIST_LINE_rec => l_Price_LLine_rec
1117 , p_old_PRICE_LIST_LINE_rec => l_old_Price_LLine_rec
1118 );
1119
1120 END IF;
1121
1122 IF ( l_Price_LLine_rec.operation = OE_GLOBALS.G_OPR_UPDATE
1123 OR l_Price_LLine_rec.operation = OE_GLOBALS.G_OPR_CREATE
1124 OR l_Price_LLine_rec.operation = OE_GLOBALS.G_OPR_DELETE) THEN
1125 -- Attribute level validation.
1126
1127 IF l_control_rec.default_attributes
1128 OR l_control_rec.change_attributes
1129 THEN
1130
1131 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
1132
1133 OE_Validate_Price_List_Line.Attributes
1134 ( x_return_status => l_return_status
1135 , p_PRICE_LIST_LINE_rec => l_Price_LLine_rec
1136 , p_old_PRICE_LIST_LINE_rec => l_old_Price_LLine_rec
1137 );
1138
1139 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1141 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1142 RAISE FND_API.G_EXC_ERROR;
1143 END IF;
1144
1145 END IF;
1146
1147 END IF;
1148
1149 -- Clear dependent attributes.
1150
1151 IF l_control_rec.change_attributes THEN
1152
1153 l_p_Price_LLine_rec := l_Price_LLine_rec; --[prarasto]
1154
1155 OE_Price_List_Line_Util.Clear_Dependent_Attr
1156 ( p_PRICE_LIST_LINE_rec => l_p_Price_LLine_rec
1157 , p_old_PRICE_LIST_LINE_rec => l_old_Price_LLine_rec
1158 , x_PRICE_LIST_LINE_rec => l_Price_LLine_rec
1159 );
1160
1161 END IF;
1162
1163 -- Default missing attributes
1164
1165 IF l_control_rec.default_attributes
1166 OR l_control_rec.change_attributes
1167 THEN
1168
1169 l_p_Price_LLine_rec := l_Price_LLine_rec; --[prarasto]
1170
1171 OE_Default_Price_List_Line.Attributes
1172 ( p_PRICE_LIST_LINE_rec => l_p_Price_LLine_rec
1173 , x_PRICE_LIST_LINE_rec => l_Price_LLine_rec
1174 );
1175
1176 END IF;
1177
1178 -- Apply attribute changes
1179
1180 IF l_control_rec.default_attributes
1181 OR l_control_rec.change_attributes
1182 THEN
1183
1184 l_p_Price_LLine_rec := l_Price_LLine_rec; --[prarasto]
1185
1186 OE_Price_List_Line_Util.Apply_Attribute_Changes
1187 ( p_PRICE_LIST_LINE_rec => l_p_Price_LLine_rec
1188 , p_old_PRICE_LIST_LINE_rec => l_old_Price_LLine_rec
1189 , x_PRICE_LIST_LINE_rec => l_Price_LLine_rec
1190 );
1191
1192 END IF;
1193
1194 -- Entity level validation.
1195
1196 IF l_control_rec.validate_entity THEN
1197
1198 IF l_Price_LLine_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
1199
1200 OE_Validate_Price_List_Line.Entity_Delete
1201 ( x_return_status => l_return_status
1202 , p_PRICE_LIST_LINE_rec => l_Price_LLine_rec
1203 );
1204
1205 ELSE
1206
1207 OE_Validate_Price_List_Line.Entity
1208 ( x_return_status => l_return_status
1209 , p_PRICE_LIST_LINE_rec => l_Price_LLine_rec
1210 , p_old_PRICE_LIST_LINE_rec => l_old_Price_LLine_rec
1211 );
1212
1213 END IF;
1214
1215 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1217 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1218 RAISE FND_API.G_EXC_ERROR;
1219 END IF;
1220
1221 END IF;
1222
1223 -- Step 4. Write to DB
1224
1225 IF l_control_rec.write_to_db THEN
1226
1227 IF l_Price_LLine_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
1228
1229 OE_Price_List_Line_Util.Delete_Row
1230 ( p_price_list_line_id => l_Price_LLine_rec.price_list_line_id
1231 );
1232
1233 ELSE
1234
1235 -- Get Who Information
1236
1237 l_Price_LLine_rec.last_update_date := SYSDATE;
1238 l_Price_LLine_rec.last_updated_by := FND_GLOBAL.USER_ID;
1239 l_Price_LLine_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
1240
1241 IF l_Price_LLine_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
1242
1243 OE_Price_List_Line_Util.Update_Row (l_Price_LLine_rec);
1244
1245 ELSIF l_Price_LLine_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
1246
1247 l_Price_LLine_rec.creation_date := SYSDATE;
1248 l_Price_LLine_rec.created_by := FND_GLOBAL.USER_ID;
1249
1250
1251 OE_Price_List_Line_Util.Insert_Row (l_Price_LLine_rec );
1252
1253
1254 END IF;
1255
1256 END IF;
1257
1258 END IF;
1259
1260 END IF;
1261 -- Load tables.
1262
1263 l_Price_LLine_tbl(I) := l_Price_LLine_rec;
1264 l_old_Price_LLine_tbl(I) := l_old_Price_LLine_rec;
1265
1266 -- For loop exception handler.
1267
1268 EXCEPTION
1269
1270 WHEN FND_API.G_EXC_ERROR THEN
1271
1272 l_Price_LLine_rec.return_status := FND_API.G_RET_STS_ERROR;
1273 l_Price_LLine_tbl(I) := l_Price_LLine_rec;
1274 l_old_Price_LLine_tbl(I) := l_old_Price_LLine_rec;
1275
1276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1277
1278 l_Price_LLine_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1279 l_Price_LLine_tbl(I) := l_Price_LLine_rec;
1280 l_old_Price_LLine_tbl(I) := l_old_Price_LLine_rec;
1281 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1282
1283 WHEN OTHERS THEN
1284
1285 l_Price_LLine_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1286 l_Price_LLine_tbl(I) := l_Price_LLine_rec;
1287 l_old_Price_LLine_tbl(I) := l_old_Price_LLine_rec;
1288
1289 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1290 THEN
1291 OE_MSG_PUB.Add_Exc_Msg
1292 ( G_PKG_NAME
1293 , 'Price_Llines'
1294 );
1295 END IF;
1296
1297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1298
1299 END;
1300 END LOOP;
1301
1302 -- Load OUT parameters
1303
1304 x_Price_LLine_tbl := l_Price_LLine_tbl;
1305 x_old_Price_LLine_tbl := l_old_Price_LLine_tbl;
1306
1307 oe_debug_pub.add('Exiting Price_LLines');
1308 oe_debug_pub.add('------------------------------------------------');
1309
1310 EXCEPTION
1311
1312 WHEN FND_API.G_EXC_ERROR THEN
1313
1314 RAISE;
1315
1316 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1317
1318
1319 RAISE;
1320
1321 WHEN OTHERS THEN
1322
1323 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1324 THEN
1325 OE_MSG_PUB.Add_Exc_Msg
1326 ( G_PKG_NAME
1327 , 'Price_Llines'
1328 );
1329 END IF;
1330
1331
1332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1333
1334 END Price_Llines;
1335
1336 -- Price_Breaks
1337
1338 PROCEDURE Price_Breaks
1339 ( p_validation_level IN NUMBER
1340 , p_control_rec IN OE_GLOBALS.Control_Rec_Type
1341 , p_Price_Break_tbl IN OE_Pricing_Cont_PUB.Price_Break_Tbl_Type
1342 , p_old_Price_Break_tbl IN OE_Pricing_Cont_PUB.Price_Break_Tbl_Type
1343 , x_Price_Break_tbl OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Price_Break_Tbl_Type
1344 , x_old_Price_Break_tbl OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Price_Break_Tbl_Type
1345 )
1346 IS
1347 l_return_status VARCHAR2(1);
1348 l_control_rec OE_GLOBALS.Control_Rec_Type;
1349 l_Price_Break_rec OE_Pricing_Cont_PUB.Price_Break_Rec_Type;
1350 l_Price_Break_tbl OE_Pricing_Cont_PUB.Price_Break_Tbl_Type;
1351 l_old_Price_Break_rec OE_Pricing_Cont_PUB.Price_Break_Rec_Type;
1352 l_old_Price_Break_tbl OE_Pricing_Cont_PUB.Price_Break_Tbl_Type;
1353
1354 l_p_Price_Break_rec OE_Pricing_Cont_PUB.Price_Break_Rec_Type; --[prarasto]
1355
1356 BEGIN
1357
1358 oe_debug_pub.add('------------------------------------------------');
1359 oe_debug_pub.add('Entering Price_Breaks');
1360
1361 -- Init local table variables.
1362
1363 l_Price_Break_tbl := p_Price_Break_tbl;
1364 l_old_Price_Break_tbl := p_old_Price_Break_tbl;
1365
1366 FOR I IN 1..l_Price_Break_tbl.COUNT LOOP
1367 BEGIN
1368
1369 -- Load local records.
1370
1371 l_Price_Break_rec := l_Price_Break_tbl(I);
1372
1373 IF l_old_Price_Break_tbl.EXISTS(I) THEN
1374 l_old_Price_Break_rec := l_old_Price_Break_tbl(I);
1375 ELSE
1376 l_old_Price_Break_rec := OE_Pricing_Cont_PUB.G_MISS_PRICE_BREAK_REC;
1377 END IF;
1378
1379 -- Load API control record
1380
1381 l_control_rec := OE_GLOBALS.Init_Control_Rec
1382 ( p_operation => l_Price_Break_rec.operation
1383 , p_control_rec => p_control_rec
1384 );
1385
1386 -- Set record return status.
1387
1388 l_Price_Break_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1389
1390 -- Prepare record.
1391
1392 IF l_Price_Break_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
1393
1394 l_Price_Break_rec.db_flag := FND_API.G_FALSE;
1395
1396 -- Set missing old record elements to NULL.
1397
1398 l_old_Price_Break_rec :=
1399 OE_Price_Break_Util.Convert_Miss_To_Null (l_old_Price_Break_rec);
1400
1401 ELSIF l_Price_Break_rec.operation = OE_GLOBALS.G_OPR_UPDATE
1402 OR l_Price_Break_rec.operation = OE_GLOBALS.G_OPR_DELETE
1403 THEN
1404
1405 l_Price_Break_rec.db_flag := FND_API.G_TRUE;
1406
1407 -- Query Old if missing
1408
1409 IF l_old_Price_Break_rec.discount_line_id = FND_API.G_MISS_NUM
1410 OR l_old_Price_Break_rec.method_type_code = FND_API.G_MISS_CHAR
1411 OR l_old_Price_Break_rec.price_break_high = FND_API.G_MISS_NUM
1412 OR l_old_Price_Break_rec.price_break_low = FND_API.G_MISS_NUM
1413 THEN
1414 NULL;
1415 /*
1416 l_old_Price_Break_rec := OE_Price_Break_Util.Query_Row
1417 ( p_discount_line_id => l_Price_Break_rec.discount_line_id
1418 , p_method_type_code => l_Price_Break_rec.method_type_code
1419 , p_price_break_high => l_Price_Break_rec.price_break_high
1420 , p_price_break_low => l_Price_Break_rec.price_break_low
1421 );
1422 */
1423
1424 ELSE
1425
1426 -- Set missing old record elements to NULL.
1427 null;
1428 /*
1429 l_old_Price_Break_rec :=
1430 OE_Price_Break_Util.Convert_Miss_To_Null (l_old_Price_Break_rec);
1431 */
1432
1433 END IF;
1434
1435 -- Complete new record from old
1436 /*
1437 l_Price_Break_rec := OE_Price_Break_Util.Complete_Record
1438 ( p_Price_Break_rec => l_Price_Break_rec
1439 , p_old_Price_Break_rec => l_old_Price_Break_rec
1440 );
1441 */
1442
1443 END IF;
1444
1445 -- Attribute level validation.
1446
1447 IF l_control_rec.default_attributes
1448 OR l_control_rec.change_attributes
1449 THEN
1450
1451 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
1452
1453 OE_Validate_Price_Break.Attributes
1454 ( x_return_status => l_return_status
1455 , p_Price_Break_rec => l_Price_Break_rec
1456 , p_old_Price_Break_rec => l_old_Price_Break_rec
1457 );
1458
1459 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1461 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1462 RAISE FND_API.G_EXC_ERROR;
1463 END IF;
1464
1465 END IF;
1466
1467 END IF;
1468
1469 -- Clear dependent attributes.
1470
1471 IF l_control_rec.change_attributes THEN
1472 NULL;
1473
1474 /*
1475 l_p_Price_Break_rec := l_Price_Break_rec; --[prarasto]
1476
1477 OE_Price_Break_Util.Clear_Dependent_Attr
1478 ( p_Price_Break_rec => l_p_Price_Break_rec
1479 , p_old_Price_Break_rec => l_old_Price_Break_rec
1480 , x_Price_Break_rec => l_Price_Break_rec
1481 );
1482 */
1483
1484 END IF;
1485
1486 -- Default missing attributes
1487
1488 IF l_control_rec.default_attributes
1489 OR l_control_rec.change_attributes
1490 THEN
1491
1492 l_p_Price_Break_rec := l_Price_Break_rec; --[prarasto]
1493
1494 OE_Default_Price_Break.Attributes
1495 ( p_Price_Break_rec => l_p_Price_Break_rec
1496 , x_Price_Break_rec => l_Price_Break_rec
1497 );
1498
1499 END IF;
1500
1501 -- Apply attribute changes
1502
1503 IF l_control_rec.default_attributes
1504 OR l_control_rec.change_attributes
1505 THEN
1506
1507 OE_Price_Break_Util.Apply_Attribute_Changes
1508 ( p_Price_Break_rec => l_Price_Break_rec
1509 , p_old_Price_Break_rec => l_old_Price_Break_rec
1510 , x_Price_Break_rec => l_Price_Break_rec
1511 );
1512
1513 END IF;
1514
1515 -- Entity level validation.
1516
1517 IF l_control_rec.validate_entity THEN
1518
1519 IF l_Price_Break_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
1520 /*
1521 OE_Validate_Price_Break.Entity_Delete
1522 ( x_return_status => l_return_status
1523 , p_Price_Break_rec => l_Price_Break_rec
1524 );
1525 */
1526 NULL;
1527
1528 ELSE
1529 NULL;
1530 /*
1531 OE_Validate_Price_Break.Entity
1532 ( x_return_status => l_return_status
1533 , p_Price_Break_rec => l_Price_Break_rec
1534 , p_old_Price_Break_rec => l_old_Price_Break_rec
1535 );
1536 */
1537
1538 END IF;
1539
1540 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1542 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1543 RAISE FND_API.G_EXC_ERROR;
1544 END IF;
1545
1546 END IF;
1547
1548 -- Step 4. Write to DB
1549
1550 IF l_control_rec.write_to_db THEN
1551
1552 IF l_Price_Break_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
1553
1554 NULL;
1555 /* OE_Price_Break_Util.Delete_Row
1556 ( p_discount_line_id => l_Price_Break_rec.discount_line_id
1557 , p_method_type_code => l_Price_Break_rec.method_type_code
1558 , p_price_break_high => l_Price_Break_rec.price_break_high
1559 , p_price_break_low => l_Price_Break_rec.price_break_low
1560 ) ;
1561 */
1562 NULL;
1563
1564
1565 ELSE
1566
1567 -- Get Who Information
1568
1569 l_Price_Break_rec.last_update_date := SYSDATE;
1570 l_Price_Break_rec.last_updated_by := FND_GLOBAL.USER_ID;
1571 l_Price_Break_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
1572
1573 IF l_Price_Break_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
1574
1575 NULL;
1576 /*
1577 OE_Price_Break_Util.Update_Row (l_Price_Break_rec);
1578 */
1579 ELSIF l_Price_Break_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
1580
1581 l_Price_Break_rec.creation_date := SYSDATE;
1582 l_Price_Break_rec.created_by := FND_GLOBAL.USER_ID;
1583 /*
1584 OE_Price_Break_Util.Insert_Row (l_Price_Break_rec);
1585 */
1586
1587 END IF;
1588
1589 END IF;
1590
1591 END IF;
1592
1593 -- Load tables.
1594
1595 l_Price_Break_tbl(I) := l_Price_Break_rec;
1596 l_old_Price_Break_tbl(I) := l_old_Price_Break_rec;
1597
1598 -- For loop exception handler.
1599
1600
1601 EXCEPTION
1602
1603 WHEN FND_API.G_EXC_ERROR THEN
1604
1605 l_Price_Break_rec.return_status := FND_API.G_RET_STS_ERROR;
1606 l_Price_Break_tbl(I) := l_Price_Break_rec;
1607 l_old_Price_Break_tbl(I) := l_old_Price_Break_rec;
1608
1609 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1610
1611 l_Price_Break_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1612 l_Price_Break_tbl(I) := l_Price_Break_rec;
1613 l_old_Price_Break_tbl(I) := l_old_Price_Break_rec;
1614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1615
1616 WHEN OTHERS THEN
1617
1618 l_Price_Break_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1619 l_Price_Break_tbl(I) := l_Price_Break_rec;
1620 l_old_Price_Break_tbl(I) := l_old_Price_Break_rec;
1621
1622 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1623 THEN
1624 OE_MSG_PUB.Add_Exc_Msg
1625 ( G_PKG_NAME
1626 , 'Price_Breaks'
1627 );
1628 END IF;
1629
1630 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1631
1632 END;
1633 END LOOP;
1634
1635 -- Load OUT parameters
1636
1637 x_Price_Break_tbl := l_Price_Break_tbl;
1638 x_old_Price_Break_tbl := l_old_Price_Break_tbl;
1639
1640 oe_debug_pub.add('Exiting Price_Breaks');
1641 oe_debug_pub.add('------------------------------------------------');
1642
1643 EXCEPTION
1644
1645 WHEN FND_API.G_EXC_ERROR THEN
1646
1647 RAISE;
1648
1649 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1650
1651
1652 RAISE;
1653
1654 WHEN OTHERS THEN
1655
1656 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1657 THEN
1658 OE_MSG_PUB.Add_Exc_Msg
1659 ( G_PKG_NAME
1660 , 'Price_Breaks'
1661 );
1662 END IF;
1663
1664
1665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1666
1667 END Price_Breaks;
1668
1669 -- Start of Comments
1670 -- API name Process_Pricing_Cont
1671 -- Type Private
1672 -- Function
1673 --
1674 -- Pre-reqs
1675 --
1676 -- Parameters
1677 --
1678 -- Version Current version = 1.0
1679 -- Initial version = 1.0
1680 --
1681 -- Notes
1682 --
1683 -- End of Comments
1684
1685 PROCEDURE Process_Pricing_Cont
1686 ( p_api_version_number IN NUMBER
1687 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1688 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1689 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1690 , p_control_rec IN OE_GLOBALS.Control_Rec_Type :=
1691 OE_GLOBALS.G_MISS_CONTROL_REC
1692 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1693 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1694 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1695 , p_Contract_rec IN OE_Pricing_Cont_PUB.Contract_Rec_Type :=
1696 OE_Pricing_Cont_PUB.G_MISS_CONTRACT_REC
1697 , p_old_Contract_rec IN OE_Pricing_Cont_PUB.Contract_Rec_Type :=
1698 OE_Pricing_Cont_PUB.G_MISS_CONTRACT_REC
1699 , p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type :=
1700 OE_Pricing_Cont_PUB.G_MISS_AGREEMENT_REC
1701 , p_old_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type :=
1702 OE_Pricing_Cont_PUB.G_MISS_AGREEMENT_REC
1703 , p_Price_LHeader_rec IN OE_Price_List_PUB.Price_List_Rec_Type :=
1704 OE_Price_List_PUB.G_MISS_PRICE_List_REC
1705 , p_old_Price_LHeader_rec IN OE_Price_List_PUB.Price_List_Rec_Type :=
1706 OE_Price_List_PUB.G_MISS_PRICE_List_REC
1707 , p_Discount_Header_rec IN OE_Pricing_Cont_PUB.Discount_Header_Rec_Type :=
1708 OE_Pricing_Cont_PUB.G_MISS_DISCOUNT_HEADER_REC
1709 , p_old_Discount_Header_rec IN OE_Pricing_Cont_PUB.Discount_Header_Rec_Type :=
1710 OE_Pricing_Cont_PUB.G_MISS_DISCOUNT_HEADER_REC
1711 , p_Price_LLine_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type :=
1712 OE_Price_List_PUB.G_MISS_PRICE_List_Line_TBL
1713 , p_old_Price_LLine_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type :=
1714 OE_Price_List_PUB.G_MISS_PRICE_List_Line_TBL
1715 , p_Discount_Cust_tbl IN OE_Pricing_Cont_PUB.Discount_Cust_Tbl_Type :=
1716 OE_Pricing_Cont_PUB.G_MISS_DISCOUNT_CUST_TBL
1717 , p_old_Discount_Cust_tbl IN OE_Pricing_Cont_PUB.Discount_Cust_Tbl_Type :=
1718 OE_Pricing_Cont_PUB.G_MISS_DISCOUNT_CUST_TBL
1719 , p_Discount_Line_tbl IN OE_Pricing_Cont_PUB.Discount_Line_Tbl_Type :=
1720 OE_Pricing_Cont_PUB.G_MISS_DISCOUNT_LINE_TBL
1721 , p_old_Discount_Line_tbl IN OE_Pricing_Cont_PUB.Discount_Line_Tbl_Type :=
1722 OE_Pricing_Cont_PUB.G_MISS_DISCOUNT_LINE_TBL
1723 , p_Price_Break_tbl IN OE_Pricing_Cont_PUB.Price_Break_Tbl_Type :=
1724 OE_Pricing_Cont_PUB.G_MISS_PRICE_BREAK_TBL
1725 , p_old_Price_Break_tbl IN OE_Pricing_Cont_PUB.Price_Break_Tbl_Type :=
1726 OE_Pricing_Cont_PUB.G_MISS_PRICE_BREAK_TBL
1727 , x_Contract_rec OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Contract_Rec_Type
1728 , x_Agreement_rec OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Agreement_Rec_Type
1729 , x_Price_LHeader_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
1730 , x_Discount_Header_rec OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Discount_Header_Rec_Type
1731 , x_Price_LLine_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
1732 , x_Discount_Cust_tbl OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Discount_Cust_Tbl_Type
1733 , x_Discount_Line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Discount_Line_Tbl_Type
1734 , x_Price_Break_tbl OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Price_Break_Tbl_Type
1735 )
1736 IS
1737 l_api_version_number CONSTANT NUMBER := 1.0;
1738 l_api_name CONSTANT VARCHAR2(30):= 'Process_Pricing_Cont';
1739 l_return_status VARCHAR2(1);
1740 l_control_rec OE_GLOBALS.Control_Rec_Type;
1741 l_Contract_rec OE_Pricing_Cont_PUB.Contract_Rec_Type := p_Contract_rec;
1742 l_old_Contract_rec OE_Pricing_Cont_PUB.Contract_Rec_Type := p_old_Contract_rec;
1743 l_Agreement_rec OE_Pricing_Cont_PUB.Agreement_Rec_Type := p_Agreement_rec;
1744 l_old_Agreement_rec OE_Pricing_Cont_PUB.Agreement_Rec_Type := p_old_Agreement_rec;
1745 l_Price_LHeader_rec OE_Price_List_PUB.Price_List_Rec_Type := p_Price_LHeader_rec;
1746 l_old_Price_LHeader_rec OE_Price_List_PUB.Price_List_Rec_Type := p_old_Price_LHeader_rec;
1747 l_Discount_Header_rec OE_Pricing_Cont_PUB.Discount_Header_Rec_Type := p_Discount_Header_rec;
1748 l_old_Discount_Header_rec OE_Pricing_Cont_PUB.Discount_Header_Rec_Type := p_old_Discount_Header_rec;
1749 l_Price_LLine_rec OE_Price_List_PUB.Price_List_Line_Rec_Type;
1750 l_Price_LLine_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type;
1751 l_old_Price_LLine_rec OE_Price_List_PUB.Price_List_Line_Rec_Type;
1752 l_old_Price_LLine_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type;
1753 l_Discount_Cust_rec OE_Pricing_Cont_PUB.Discount_Cust_Rec_Type;
1754 l_Discount_Cust_tbl OE_Pricing_Cont_PUB.Discount_Cust_Tbl_Type;
1755 l_old_Discount_Cust_rec OE_Pricing_Cont_PUB.Discount_Cust_Rec_Type;
1756 l_old_Discount_Cust_tbl OE_Pricing_Cont_PUB.Discount_Cust_Tbl_Type;
1757 l_Discount_Line_rec OE_Pricing_Cont_PUB.Discount_Line_Rec_Type;
1758 l_Discount_Line_tbl OE_Pricing_Cont_PUB.Discount_Line_Tbl_Type;
1759 l_old_Discount_Line_rec OE_Pricing_Cont_PUB.Discount_Line_Rec_Type;
1760 l_old_Discount_Line_tbl OE_Pricing_Cont_PUB.Discount_Line_Tbl_Type;
1761 l_Price_Break_rec OE_Pricing_Cont_PUB.Price_Break_Rec_Type;
1762 l_Price_Break_tbl OE_Pricing_Cont_PUB.Price_Break_Tbl_Type;
1763 l_old_Price_Break_rec OE_Pricing_Cont_PUB.Price_Break_Rec_Type;
1764 l_old_Price_Break_tbl OE_Pricing_Cont_PUB.Price_Break_Tbl_Type;
1765 l_msg_data VARCHAR2(250);
1766
1767 l_msg_count NUMBER;
1768
1769 /*
1770 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type ;
1771 l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
1772 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type ;
1773 l_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type ;
1774 l_QUALIFIER_RULES_val_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Val_Rec_Type;
1775
1776 l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type ;
1777 l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
1778 l_x_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type ;
1779 l_x_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type ;
1780 l_x_QUALIFIER_RULES_val_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Val_Rec_Type;
1781 */
1782 l_return_values VARCHAR2(1) := FND_API.G_FALSE;
1783 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1784 NO_UPDATE_PRIVILEGE EXCEPTION;
1785
1786 l_p_Agreement_rec OE_Pricing_Cont_PUB.Agreement_Rec_Type; --[prarasto]
1787 l_p_old_Agreement_rec OE_Pricing_Cont_PUB.Agreement_Rec_Type; --[prarasto]
1788 l_p_Price_LHeader_rec OE_Price_List_PUB.Price_List_Rec_Type; --[prarasto]
1789 l_p_old_Price_LHeader_rec OE_Price_List_PUB.Price_List_Rec_Type; --[prarasto]
1790 l_p_Price_LLine_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type; --[prarasto]
1791 l_p_old_Price_LLine_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type; --[prarasto]
1792 l_p_Price_Break_tbl OE_Pricing_Cont_PUB.Price_Break_Tbl_Type; --[prarasto]
1793 l_p_old_Price_Break_tbl OE_Pricing_Cont_PUB.Price_Break_Tbl_Type; --[prarasto]
1794
1795 BEGIN
1796
1797
1798 oe_debug_pub.add('Entring Process_Pricing_Cont');
1799
1800 -- Standard call to check for call compatibility
1801
1802 IF NOT FND_API.Compatible_API_Call
1803 ( l_api_version_number
1804 , p_api_version_number
1805 , l_api_name
1806 , G_PKG_NAME
1807 )
1808 THEN
1809 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1810 END IF;
1811
1812
1813 IF p_Agreement_rec.operation = QP_GLOBALS.G_OPR_UPDATE THEN
1814 -- Check the security privilege
1815 IF p_Agreement_rec.price_list_id IS NOT NULL
1816 THEN
1817 IF QP_security.check_function( p_function_name => QP_Security.G_FUNCTION_UPDATE,
1818 p_instance_type => QP_Security.G_PRICELIST_OBJECT,
1819 p_instance_pk1 => p_Agreement_rec.price_list_id) = 'F'
1820 THEN
1821 fnd_message.set_name('QP', 'QP_NO_PRIVILEGE');
1822 fnd_message.set_token('PRICING_OBJECT', 'Agreement');
1823 oe_msg_pub.Add;
1824
1825 RAISE NO_UPDATE_PRIVILEGE;
1826
1827 END IF; -- end of check security privilege
1828 END IF;
1829 END IF;
1830 -- Initialize message list.
1831
1832 IF FND_API.to_Boolean(p_init_msg_list) THEN
1833 OE_MSG_PUB.initialize;
1834 END IF;
1835
1836 -- Init local table variables.
1837
1838 l_Price_LLine_tbl := p_Price_LLine_tbl;
1839 l_old_Price_LLine_tbl := p_old_Price_LLine_tbl;
1840
1841 -- Init local table variables.
1842
1843 l_Discount_Cust_tbl := p_Discount_Cust_tbl;
1844 l_old_Discount_Cust_tbl := p_old_Discount_Cust_tbl;
1845
1846 -- Init local table variables.
1847
1848 l_Discount_Line_tbl := p_Discount_Line_tbl;
1849 l_old_Discount_Line_tbl := p_old_Discount_Line_tbl;
1850
1851 -- Init local table variables.
1852
1853 l_Price_Break_tbl := p_Price_Break_tbl;
1854 l_old_Price_Break_tbl := p_old_Price_Break_tbl;
1855
1856 -- This procedure not required , oe_pricing_contracts table
1857 -- made obselete
1858
1859 -- Contract
1860 /*
1861 Contract
1862 ( p_validation_level => p_validation_level
1863 , p_control_rec => p_control_rec
1864 , p_Contract_rec => l_Contract_rec
1865 , p_old_Contract_rec => l_old_Contract_rec
1866 , x_Contract_rec => l_Contract_rec
1867 , x_old_Contract_rec => l_old_Contract_rec
1868 );
1869
1870 -- Perform Contract group requests.
1871
1872 IF p_control_rec.process AND
1873 (p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_ALL OR
1874 p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_CONTRACT)
1875 THEN
1876
1877 NULL;
1878
1879 END IF;
1880
1881 -- end comments :: procedure call made obselete
1882 */
1883
1884 -- Load parent key if missing and operation is create.
1885
1886 IF l_Agreement_rec.operation = OE_GLOBALS.G_OPR_CREATE
1887 AND (l_Agreement_rec.pricing_contract_id IS NULL OR
1888 l_Agreement_rec.pricing_contract_id = FND_API.G_MISS_NUM)
1889 THEN
1890
1891 -- Copy parent_id.
1892
1893 l_Agreement_rec.pricing_contract_id := l_Contract_rec.pricing_contract_id;
1894 END IF;
1895
1896 if (l_Contract_rec.agreement_Id IS NOT NULL AND l_Contract_rec.agreement_Id <> FND_API.G_MISS_NUM)
1897 THEN
1898 l_Agreement_rec.agreement_id := l_Contract_rec.agreement_id;
1899 END IF;
1900
1901 -- Agreement
1902
1903 l_p_Agreement_rec := l_Agreement_rec; --[prarasto]
1904 l_p_old_Agreement_rec := l_old_Agreement_rec; --[prarasto]
1905
1906 Agreement
1907 ( p_validation_level => p_validation_level
1908 , p_control_rec => p_control_rec
1909 , p_Agreement_rec => l_p_Agreement_rec
1910 , p_old_Agreement_rec => l_p_old_Agreement_rec
1911 , x_Agreement_rec => l_Agreement_rec
1912 , x_old_Agreement_rec => l_old_Agreement_rec
1913 );
1914
1915
1916 -- Perform Agreement group requests.
1917
1918 IF p_control_rec.process AND
1919 (p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_ALL OR
1920 p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_AGREEMENT)
1921 THEN
1922
1923 NULL;
1924
1925 END IF;
1926
1927
1928 oe_debug_pub.add('attribute1 ='||l_Discount_Header_rec.attribute1);
1929
1930 -- The old logic exists when called from Agreement Form (Screen).
1931
1932 if l_Discount_Header_rec.attribute1 <> 'Calling from PUBLIC API' then
1933
1934
1935 -- Load parent key if missing and operation is create.
1936
1937 IF l_Price_LHeader_rec.operation = OE_GLOBALS.G_OPR_CREATE
1938 AND (l_Price_LHeader_rec.pricing_contract_id IS NULL OR
1939 l_Price_LHeader_rec.pricing_contract_id = FND_API.G_MISS_NUM)
1940 THEN
1941
1942 -- Copy parent_id.
1943
1944 l_Price_LHeader_rec.pricing_contract_id := l_Contract_rec.pricing_contract_id;
1945
1946 END IF;
1947
1948 -- Price_Lheader
1949
1950 l_p_Price_LHeader_rec := l_Price_LHeader_rec; --[prarasto]
1951 l_p_old_Price_LHeader_rec := l_old_Price_LHeader_rec; --[prarasto]
1952
1953 Price_Lheader
1954 ( p_validation_level => p_validation_level
1955 , p_control_rec => p_control_rec
1956 , p_Price_LHeader_rec => l_p_Price_LHeader_rec
1957 , p_old_Price_LHeader_rec => l_p_old_Price_LHeader_rec
1958 , x_Price_LHeader_rec => l_Price_LHeader_rec
1959 , x_old_Price_LHeader_rec => l_old_Price_LHeader_rec
1960 );
1961
1962 -- Perform Price_LHeader group requests.
1963
1964 IF p_control_rec.process AND
1965 (p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_ALL OR
1966 p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_PRICE_LHEADER)
1967 THEN
1968
1969 NULL;
1970
1971 END IF;
1972
1973 -- Load parent key if missing and operation is create.
1974
1975 IF l_Discount_Header_rec.operation = OE_GLOBALS.G_OPR_CREATE
1976 AND (l_Discount_Header_rec.pricing_contract_id IS NULL OR
1977 l_Discount_Header_rec.pricing_contract_id = FND_API.G_MISS_NUM)
1978 THEN
1979
1980 -- Copy parent_id.
1981
1982 l_Discount_Header_rec.pricing_contract_id := l_Contract_rec.pricing_contract_id;
1983 END IF;
1984
1985 if (l_Contract_rec.discount_Id IS NOT NULL AND l_Contract_rec.discount_Id <> FND_API.G_MISS_NUM)
1986 THEN
1987 l_Discount_Header_rec.discount_id := l_Contract_rec.discount_id;
1988 END IF;
1989
1990 -- Discount_Header
1991 -- Code Commented
1992 -- No need to create discounts now
1993 /*
1994 l_p_Discount_Header_rec := l_Discount_Header_rec; --[prarasto]
1995 l_p_old_Discount_Header_rec := l_old_Discount_Header_rec; --[prarasto]
1996
1997 Discount_Header
1998 ( p_validation_level => p_validation_level
1999 , p_control_rec => p_control_rec
2000 , p_Discount_Header_rec => l_p_Discount_Header_rec
2001 , p_old_Discount_Header_rec => l_p_old_Discount_Header_rec
2002 , x_Discount_Header_rec => l_Discount_Header_rec
2003 , x_old_Discount_Header_rec => l_old_Discount_Header_rec
2004 );
2005 */
2006
2007 -- Perform Discount_Header group requests.
2008
2009 IF p_control_rec.process AND
2010 (p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_ALL OR
2011 p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_DISCOUNT_HEADER)
2012 THEN
2013
2014 NULL;
2015
2016 END IF;
2017
2018 -- Load parent key if missing and operation is create.
2019
2020 FOR I IN 1..l_Price_LLine_tbl.COUNT LOOP
2021
2022 l_Price_LLine_rec := l_Price_LLine_tbl(I);
2023
2024 IF l_Price_LLine_rec.operation = OE_GLOBALS.G_OPR_CREATE
2025 AND (l_Price_LLine_rec.price_list_id IS NULL OR
2026 l_Price_LLine_rec.price_list_id = FND_API.G_MISS_NUM)
2027 THEN
2028
2029 -- Copy parent_id.
2030
2031 l_Price_LLine_tbl(I).price_list_id := l_Price_LHeader_rec.price_list_id;
2032 END IF;
2033 END LOOP;
2034
2035 -- Price_Llines
2036
2037 l_p_Price_LLine_tbl := l_Price_LLine_tbl; --[prarasto]
2038 l_p_old_Price_LLine_tbl := l_old_Price_LLine_tbl; --[prarasto]
2039
2040 Price_Llines
2041 ( p_validation_level => p_validation_level
2042 , p_control_rec => p_control_rec
2043 , p_Price_LLine_tbl => l_p_Price_LLine_tbl
2044 , p_old_Price_LLine_tbl => l_p_old_Price_LLine_tbl
2045 , x_Price_LLine_tbl => l_Price_LLine_tbl
2046 , x_old_Price_LLine_tbl => l_old_Price_LLine_tbl
2047 );
2048
2049 -- Perform Price_LLine group requests.
2050
2051 IF p_control_rec.process AND
2052 (p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_ALL OR
2053 p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_PRICE_LLINE)
2054 THEN
2055
2056 NULL;
2057
2058 END IF;
2059
2060 -- Load parent key if missing and operation is create.
2061
2062 FOR I IN 1..l_Discount_Cust_tbl.COUNT LOOP
2063
2064 l_Discount_Cust_rec := l_Discount_Cust_tbl(I);
2065
2066 IF l_Discount_Cust_rec.operation = OE_GLOBALS.G_OPR_CREATE
2067 AND (l_Discount_Cust_rec.discount_id IS NULL OR
2068 l_Discount_Cust_rec.discount_id = FND_API.G_MISS_NUM)
2069 THEN
2070
2071 -- Copy parent_id.
2072
2073 l_Discount_Cust_tbl(I).discount_id := l_Discount_Header_rec.discount_id;
2074 END IF;
2075 END LOOP;
2076
2077 -- Discount_Custs
2078 /*
2079 l_p_Discount_Cust_tbl := l_Discount_Cust_tbl; --[prarasto]
2080 l_p_old_Discount_Cust_tbl := l_old_Discount_Cust_tbl; --[prarasto]
2081
2082 Discount_Custs
2083 ( p_validation_level => p_validation_level
2084 , p_control_rec => p_control_rec
2085 , p_Discount_Cust_tbl => l_p_Discount_Cust_tbl
2086 , p_old_Discount_Cust_tbl => l_p_old_Discount_Cust_tbl
2087 , x_Discount_Cust_tbl => l_Discount_Cust_tbl
2088 , x_old_Discount_Cust_tbl => l_old_Discount_Cust_tbl
2089 );
2090 */
2091
2092 -- Perform Discount_Cust group requests.
2093
2094 IF p_control_rec.process AND
2095 (p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_ALL OR
2096 p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_DISCOUNT_CUST)
2097 THEN
2098
2099 NULL;
2100
2101 END IF;
2102
2103 -- Load parent key if missing and operation is create.
2104
2105 FOR I IN 1..l_Discount_Line_tbl.COUNT LOOP
2106
2107 l_Discount_Line_rec := l_Discount_Line_tbl(I);
2108
2109 IF l_Discount_Line_rec.operation = OE_GLOBALS.G_OPR_CREATE
2110 AND (l_Discount_Line_rec.discount_id IS NULL OR
2111 l_Discount_Line_rec.discount_id = FND_API.G_MISS_NUM)
2112 THEN
2113
2114 -- Copy parent_id.
2115
2116 l_Discount_Line_tbl(I).discount_id := l_Discount_Header_rec.discount_id;
2117 END IF;
2118 END LOOP;
2119 /*
2120 -- Discount_Lines
2121
2122 l_p_Discount_Line_tbl := l_Discount_Line_tbl; --[prarasto]
2123 l_p_old_Discount_Line_tbl := l_old_Discount_Line_tbl; --[prarasto]
2124
2125 Discount_Lines
2126 ( p_validation_level => p_validation_level
2127 , p_control_rec => p_control_rec
2128 , p_Discount_Line_tbl => l_p_Discount_Line_tbl
2129 , p_old_Discount_Line_tbl => l_p_old_Discount_Line_tbl
2130 , x_Discount_Line_tbl => l_Discount_Line_tbl
2131 , x_old_Discount_Line_tbl => l_old_Discount_Line_tbl
2132 );
2133 */
2134
2135 -- Perform Discount_Line group requests.
2136
2137 IF p_control_rec.process AND
2138 (p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_ALL OR
2139 p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_DISCOUNT_LINE)
2140 THEN
2141
2142 NULL;
2143
2144 END IF;
2145
2146 -- Load parent key if missing and operation is create.
2147
2148 FOR I IN 1..l_Price_Break_tbl.COUNT LOOP
2149
2150 l_Price_Break_rec := l_Price_Break_tbl(I);
2151
2152 IF l_Price_Break_rec.operation = OE_GLOBALS.G_OPR_CREATE
2153 AND (l_Price_Break_rec.discount_line_id IS NULL OR
2154 l_Price_Break_rec.discount_line_id = FND_API.G_MISS_NUM)
2155 THEN
2156
2157 -- Check If parent exists.
2158
2159 IF l_Discount_Line_tbl.EXISTS(l_Price_Break_rec.Discount_Line_index) THEN
2160
2161 -- Copy parent_id.
2162
2163 l_Price_Break_tbl(I).discount_line_id := l_Discount_Line_tbl(l_Price_Break_rec.Discount_Line_index).discount_line_id;
2164
2165 ELSE
2166
2167 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2168 THEN
2169
2170 FND_MESSAGE.SET_NAME('OE','OE_API_INV_PARENT_INDEX');
2171 FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Price_Break');
2172 FND_MESSAGE.SET_TOKEN('ENTITY_INDEX',I);
2173 FND_MESSAGE.SET_TOKEN('PARENT_INDEX',l_Price_Break_rec.Discount_Line_index);
2174 OE_MSG_PUB.Add;
2175
2176 END IF;
2177 END IF;
2178 END IF;
2179 END LOOP;
2180
2181 -- Price_Breaks
2182
2183 l_p_Price_Break_tbl := l_Price_Break_tbl; --[prarasto]
2184 l_p_old_Price_Break_tbl := l_old_Price_Break_tbl; --[prarasto]
2185
2186 Price_Breaks
2187 ( p_validation_level => p_validation_level
2188 , p_control_rec => p_control_rec
2189 , p_Price_Break_tbl => l_p_Price_Break_tbl
2190 , p_old_Price_Break_tbl => l_p_old_Price_Break_tbl
2191 , x_Price_Break_tbl => l_Price_Break_tbl
2192 , x_old_Price_Break_tbl => l_old_Price_Break_tbl
2193 );
2194
2195 -- Perform Price_Break group requests.
2196
2197 IF p_control_rec.process AND
2198 (p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_ALL OR
2199 p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_PRICE_BREAK)
2200 THEN
2201
2202 NULL;
2203
2204 END IF;
2205
2206 -- Step 6. Perform Object group logic
2207
2208 IF p_control_rec.process AND
2209 p_control_rec.process_entity = OE_GLOBALS.G_ENTITY_ALL
2210 THEN
2211
2212 NULL;
2213
2214 END IF;
2215
2216 end if;
2217 --end processing from screen
2218
2219 -- Done processing, load OUT parameters.
2220
2221 x_Contract_rec := l_Contract_rec;
2222 x_Agreement_rec := l_Agreement_rec;
2223 x_Price_LHeader_rec := l_Price_LHeader_rec;
2224 x_Discount_Header_rec := l_Discount_Header_rec;
2225 x_Price_LLine_tbl := l_Price_LLine_tbl;
2226 x_Discount_Cust_tbl := l_Discount_Cust_tbl;
2227 x_Discount_Line_tbl := l_Discount_Line_tbl;
2228 x_Price_Break_tbl := l_Price_Break_tbl;
2229
2230 -- Clear API cache.
2231
2232 IF p_control_rec.clear_api_cache THEN
2233
2234 NULL;
2235
2236 END IF;
2237
2238 -- Clear API request tbl.
2239
2240 IF p_control_rec.clear_api_requests THEN
2241
2242 NULL;
2243
2244 END IF;
2245
2246 -- Derive return status.
2247
2248
2249
2250 IF l_Contract_rec.return_status = FND_API.G_RET_STS_ERROR THEN
2251 x_return_status := FND_API.G_RET_STS_ERROR;
2252 END IF;
2253
2254 IF l_Agreement_rec.return_status = FND_API.G_RET_STS_ERROR THEN
2255 x_return_status := FND_API.G_RET_STS_ERROR;
2256 END IF;
2257
2258 IF l_Price_LHeader_rec.return_status = FND_API.G_RET_STS_ERROR THEN
2259 x_return_status := FND_API.G_RET_STS_ERROR;
2260 END IF;
2261
2262 IF l_Discount_Header_rec.return_status = FND_API.G_RET_STS_ERROR THEN
2263 x_return_status := FND_API.G_RET_STS_ERROR;
2264 END IF;
2265
2266 FOR I IN 1..l_Price_LLine_tbl.COUNT LOOP
2267
2268 IF l_Price_LLine_tbl(I).return_status = FND_API.G_RET_STS_ERROR THEN
2269 x_return_status := FND_API.G_RET_STS_ERROR;
2270 END IF;
2271
2272 END LOOP;
2273
2274 FOR I IN 1..l_Discount_Cust_tbl.COUNT LOOP
2275
2276 IF l_Discount_Cust_tbl(I).return_status = FND_API.G_RET_STS_ERROR THEN
2277 x_return_status := FND_API.G_RET_STS_ERROR;
2278 END IF;
2279
2280 END LOOP;
2281
2282 FOR I IN 1..l_Discount_Line_tbl.COUNT LOOP
2283
2284 IF l_Discount_Line_tbl(I).return_status = FND_API.G_RET_STS_ERROR THEN
2285 x_return_status := FND_API.G_RET_STS_ERROR;
2286 END IF;
2287
2288 END LOOP;
2289
2290 FOR I IN 1..l_Price_Break_tbl.COUNT LOOP
2291
2292 IF l_Price_Break_tbl(I).return_status = FND_API.G_RET_STS_ERROR THEN
2293 x_return_status := FND_API.G_RET_STS_ERROR;
2294 END IF;
2295
2296 END LOOP;
2297
2298 -- Get message count and data
2299
2300 OE_MSG_PUB.Count_And_Get
2301 ( p_count => x_msg_count
2302 , p_data => x_msg_data
2303 );
2304
2305 oe_debug_pub.add('Exiting Process_Pricing_Cont');
2306
2307 EXCEPTION
2308
2309 WHEN FND_API.G_EXC_ERROR THEN
2310
2311 x_return_status := FND_API.G_RET_STS_ERROR;
2312
2313 -- Get message count and data
2314
2315 OE_MSG_PUB.Count_And_Get
2316 ( p_count => x_msg_count
2317 , p_data => x_msg_data
2318 );
2319
2320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2321
2322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2323
2324 -- Get message count and data
2325
2326 OE_MSG_PUB.Count_And_Get
2327 ( p_count => x_msg_count
2328 , p_data => x_msg_data
2329 );
2330
2331 WHEN NO_UPDATE_PRIVILEGE THEN
2332 x_return_status := FND_API.G_RET_STS_SUCCESS;
2333
2334 -- Get message count and data
2335
2336 oe_msg_pub.Count_And_Get
2337 ( p_count => x_msg_count
2338 , p_data => x_msg_data
2339 );
2340
2341
2342 WHEN OTHERS THEN
2343
2344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2345
2346 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2347 THEN
2348 OE_MSG_PUB.Add_Exc_Msg
2349 ( G_PKG_NAME
2350 , 'Process_Pricing_Cont'
2351 );
2352 END IF;
2353
2354 -- Get message count and data
2355
2356 OE_MSG_PUB.Count_And_Get
2357 ( p_count => x_msg_count
2358 , p_data => x_msg_data
2359 );
2360
2361 END Process_Pricing_Cont;
2362
2363 -- Start of Comments
2364 -- API name Lock_Pricing_Cont
2365 -- Type Private
2366 -- Function
2367 --
2368 -- Pre-reqs
2369 --
2370 -- Parameters
2371 --
2372 -- Version Current version = 1.0
2373 -- Initial version = 1.0
2374 --
2375 -- Notes
2376 --
2377 -- End of Comments
2378
2379 PROCEDURE Lock_Pricing_Cont
2380 ( p_api_version_number IN NUMBER
2381 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2382 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2383 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
2384 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2385 , p_Contract_rec IN OE_Pricing_Cont_PUB.Contract_Rec_Type :=
2386 OE_Pricing_Cont_PUB.G_MISS_CONTRACT_REC
2387 , p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type :=
2388 OE_Pricing_Cont_PUB.G_MISS_AGREEMENT_REC
2389 , p_Price_LHeader_rec IN OE_Price_List_PUB.Price_List_Rec_Type :=
2390 OE_Price_List_PUB.G_MISS_PRICE_List_REC
2391 , p_Discount_Header_rec IN OE_Pricing_Cont_PUB.Discount_Header_Rec_Type :=
2392 OE_Pricing_Cont_PUB.G_MISS_DISCOUNT_HEADER_REC
2393 , p_Price_LLine_tbl IN OE_Price_List_PUB.Price_List_Line_Tbl_Type :=
2394 OE_Price_List_PUB.G_MISS_PRICE_List_Line_TBL
2395 , p_Discount_Cust_tbl IN OE_Pricing_Cont_PUB.Discount_Cust_Tbl_Type :=
2396 OE_Pricing_Cont_PUB.G_MISS_DISCOUNT_CUST_TBL
2397 , p_Discount_Line_tbl IN OE_Pricing_Cont_PUB.Discount_Line_Tbl_Type :=
2398 OE_Pricing_Cont_PUB.G_MISS_DISCOUNT_LINE_TBL
2399 , p_Price_Break_tbl IN OE_Pricing_Cont_PUB.Price_Break_Tbl_Type :=
2400 OE_Pricing_Cont_PUB.G_MISS_PRICE_BREAK_TBL
2401 , x_Contract_rec OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Contract_Rec_Type
2402 , x_Agreement_rec OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Agreement_Rec_Type
2403 , x_Price_LHeader_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
2404 , x_Discount_Header_rec OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Discount_Header_Rec_Type
2405 , x_Price_LLine_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
2406 , x_Discount_Cust_tbl OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Discount_Cust_Tbl_Type
2407 , x_Discount_Line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Discount_Line_Tbl_Type
2408 , x_Price_Break_tbl OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Price_Break_Tbl_Type
2409 )
2410 IS
2411 l_api_version_number CONSTANT NUMBER := 1.0;
2412 l_api_name CONSTANT VARCHAR2(30):= 'Lock_Pricing_Cont';
2413 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2414 l_Price_LLine_rec OE_Price_List_PUB.Price_List_Line_Rec_Type;
2415 l_Discount_Cust_rec OE_Pricing_Cont_PUB.Discount_Cust_Rec_Type;
2416 l_Discount_Line_rec OE_Pricing_Cont_PUB.Discount_Line_Rec_Type;
2417 l_Price_Break_rec OE_Pricing_Cont_PUB.Price_Break_Rec_Type;
2418 BEGIN
2419
2420 -- Standard call to check for call compatibility
2421
2422 IF NOT FND_API.Compatible_API_Call
2423 ( l_api_version_number
2424 , p_api_version_number
2425 , l_api_name
2426 , G_PKG_NAME
2427 )
2428 THEN
2429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2430 END IF;
2431
2432 -- Initialize message list.
2433
2434 IF FND_API.to_Boolean(p_init_msg_list) THEN
2435 OE_MSG_PUB.initialize;
2436 END IF;
2437
2438 -- Set Savepoint
2439
2440 SAVEPOINT Lock_Pricing_Cont_PVT;
2441
2442 -- Lock Contract
2443
2444 IF p_Contract_rec.operation = OE_GLOBALS.G_OPR_LOCK THEN
2445
2446 OE_Contract_Util.Lock_Row
2447 ( p_Contract_rec => p_Contract_rec
2448 , x_Contract_rec => x_Contract_rec
2449 , x_return_status => l_return_status
2450 );
2451
2452 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2454 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2455 RAISE FND_API.G_EXC_ERROR;
2456 END IF;
2457
2458
2459 END IF;
2460
2461 -- Lock Agreement
2462
2463 IF p_Agreement_rec.operation = OE_GLOBALS.G_OPR_LOCK THEN
2464
2465 OE_Agreement_Util.Lock_Row
2466 ( p_Agreement_rec => p_Agreement_rec
2467 , x_Agreement_rec => x_Agreement_rec
2468 , x_return_status => l_return_status
2469 );
2470
2471 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2473 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2474 RAISE FND_API.G_EXC_ERROR;
2475 END IF;
2476
2477
2478 END IF;
2479
2480 -- Lock Price_LHeader
2481
2482 IF p_Price_LHeader_rec.operation = OE_GLOBALS.G_OPR_LOCK THEN
2483
2484 OE_Price_List_Util.Lock_Row
2485 ( p_Price_LIST_rec => p_Price_LHeader_rec
2486 , x_Price_LIST_rec => x_Price_LHeader_rec
2487 , x_return_status => l_return_status
2488 );
2489
2490 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2492 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2493 RAISE FND_API.G_EXC_ERROR;
2494 END IF;
2495
2496
2497 END IF;
2498 /*
2499 -- Lock Discount_Header
2500
2501 IF p_Discount_Header_rec.operation = OE_GLOBALS.G_OPR_LOCK THEN
2502
2503 OE_Discount_Header_Util.Lock_Row
2504 ( p_Discount_Header_rec => p_Discount_Header_rec
2505 , x_Discount_Header_rec => x_Discount_Header_rec
2506 , x_return_status => l_return_status
2507 );
2508
2509 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2511 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2512 RAISE FND_API.G_EXC_ERROR;
2513 END IF;
2514
2515
2516 END IF;
2517 */
2518 -- Lock Price_LLine
2519
2520 FOR I IN 1..p_Price_LLine_tbl.COUNT LOOP
2521
2522 IF p_Price_LLine_tbl(I).operation = OE_GLOBALS.G_OPR_LOCK THEN
2523
2524 OE_Price_List_Line_Util.Lock_Row
2525 ( p_PRICE_LIST_LINE_rec => p_Price_LLine_tbl(I)
2526 , x_PRICE_LIST_LINE_rec => l_Price_LLine_rec
2527 , x_return_status => l_return_status
2528 );
2529
2530 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2532 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2533 RAISE FND_API.G_EXC_ERROR;
2534 END IF;
2535
2536 x_Price_LLine_tbl(I) := l_Price_LLine_rec;
2537
2538 END IF;
2539
2540 END LOOP;
2541 /*
2542 -- Lock Discount_Cust
2543
2544 FOR I IN 1..p_Discount_Cust_tbl.COUNT LOOP
2545
2546 IF p_Discount_Cust_tbl(I).operation = OE_GLOBALS.G_OPR_LOCK THEN
2547
2548 OE_Discount_Cust_Util.Lock_Row
2549 ( p_Discount_Cust_rec => p_Discount_Cust_tbl(I)
2550 , x_Discount_Cust_rec => l_Discount_Cust_rec
2551 , x_return_status => l_return_status
2552 );
2553
2554 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2556 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2557 RAISE FND_API.G_EXC_ERROR;
2558 END IF;
2559
2560 x_Discount_Cust_tbl(I) := l_Discount_Cust_rec;
2561
2562 END IF;
2563
2564 END LOOP;
2565
2566 -- Lock Discount_Line
2567
2568 FOR I IN 1..p_Discount_Line_tbl.COUNT LOOP
2569
2570 IF p_Discount_Line_tbl(I).operation = OE_GLOBALS.G_OPR_LOCK THEN
2571
2572 OE_Discount_Line_Util.Lock_Row
2573 ( p_Discount_Line_rec => p_Discount_Line_tbl(I)
2574 , x_Discount_Line_rec => l_Discount_Line_rec
2575 , x_return_status => l_return_status
2576 );
2577
2578 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2580 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2581 RAISE FND_API.G_EXC_ERROR;
2582 END IF;
2583
2584 x_Discount_Line_tbl(I) := l_Discount_Line_rec;
2585
2586 END IF;
2587
2588 END LOOP;
2589 */
2590
2591
2592 -- Lock Price_Break
2593
2594 FOR I IN 1..p_Price_Break_tbl.COUNT LOOP
2595
2596 IF p_Price_Break_tbl(I).operation = OE_GLOBALS.G_OPR_LOCK THEN
2597
2598 OE_Price_Break_Util.Lock_Row
2599 ( p_Price_Break_rec => p_Price_Break_tbl(I)
2600 , x_Price_Break_rec => l_Price_Break_rec
2601 , x_return_status => l_return_status
2602 );
2603
2604 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2606 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2607 RAISE FND_API.G_EXC_ERROR;
2608 END IF;
2609
2610 x_Price_Break_tbl(I) := l_Price_Break_rec;
2611
2612 END IF;
2613
2614 END LOOP;
2615
2616 -- Set return status
2617
2618 x_return_status := FND_API.G_RET_STS_SUCCESS;
2619
2620 -- Get message count and data
2621
2622 OE_MSG_PUB.Count_And_Get
2623 ( p_count => x_msg_count
2624 , p_data => x_msg_data
2625 );
2626
2627
2628 EXCEPTION
2629
2630 WHEN FND_API.G_EXC_ERROR THEN
2631
2632 x_return_status := FND_API.G_RET_STS_ERROR;
2633
2634 -- Get message count and data
2635
2636 OE_MSG_PUB.Count_And_Get
2637 ( p_count => x_msg_count
2638 , p_data => x_msg_data
2639 );
2640
2641 -- Rollback
2642
2643 ROLLBACK TO Lock_Pricing_Cont_PVT;
2644
2645 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2646
2647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2648
2649 -- Get message count and data
2650
2651 OE_MSG_PUB.Count_And_Get
2652 ( p_count => x_msg_count
2653 , p_data => x_msg_data
2654 );
2655
2656 -- Rollback
2657
2658 ROLLBACK TO Lock_Pricing_Cont_PVT;
2659
2660 WHEN OTHERS THEN
2661
2662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2663
2664 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2665 THEN
2666 OE_MSG_PUB.Add_Exc_Msg
2667 ( G_PKG_NAME
2668 , 'Lock_Pricing_Cont'
2669 );
2670 END IF;
2671
2672 -- Get message count and data
2673
2674 OE_MSG_PUB.Count_And_Get
2675 ( p_count => x_msg_count
2676 , p_data => x_msg_data
2677 );
2678
2679 -- Rollback
2680
2681 ROLLBACK TO Lock_Pricing_Cont_PVT;
2682
2683 END Lock_Pricing_Cont;
2684
2685 -- Start of Comments
2686 -- API name Get_Pricing_Cont
2687 -- Type Private
2688 -- Function
2689 --
2690 -- Pre-reqs
2691 --
2692 -- Parameters
2693 --
2694 -- Version Current version = 1.0
2695 -- Initial version = 1.0
2696 --
2697 -- Notes
2698 --
2699 -- End of Comments
2700
2701 PROCEDURE Get_Pricing_Cont
2702 ( p_api_version_number IN NUMBER
2703 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2704 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2705 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
2706 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2707 , p_pricing_contract_id IN NUMBER
2708 , p_name IN VARCHAR2
2709 , x_Contract_rec OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Contract_Rec_Type
2710 , x_Agreement_rec OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Agreement_Rec_Type
2711 , x_Price_LHeader_rec OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Rec_Type
2712 , x_Discount_Header_rec OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Discount_Header_Rec_Type
2713 , x_Price_LLine_tbl OUT NOCOPY /* file.sql.39 change */ OE_Price_List_PUB.Price_List_Line_Tbl_Type
2714 , x_Discount_Cust_tbl OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Discount_Cust_Tbl_Type
2715 , x_Discount_Line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Discount_Line_Tbl_Type
2716 , x_Price_Break_tbl OUT NOCOPY /* file.sql.39 change */ OE_Pricing_Cont_PUB.Price_Break_Tbl_Type
2717 )
2718 IS
2719 l_api_version_number CONSTANT NUMBER := 1.0;
2720 l_api_name CONSTANT VARCHAR2(30):= 'Get_Pricing_Cont';
2721 l_Contract_rec OE_Pricing_Cont_PUB.Contract_Rec_Type;
2722 l_Agreement_tbl OE_Pricing_Cont_PUB.Agreement_Tbl_Type;
2723 l_Agreement_rec OE_Pricing_Cont_PUB.Agreement_Rec_Type;
2724 l_Price_LHeader_tbl OE_Price_List_PUB.Price_List_Tbl_Type;
2725 l_Price_LHeader_rec OE_Price_List_PUB.Price_List_Rec_Type;
2726 l_Discount_Header_tbl OE_Pricing_Cont_PUB.Discount_Header_Tbl_Type;
2727 l_Discount_Header_rec OE_Pricing_Cont_PUB.Discount_Header_Rec_Type;
2728 l_Price_LLine_tbl OE_Price_List_PUB.Price_List_Line_Tbl_Type;
2729 l_Price_LLine_rec OE_Price_List_PUB.Price_List_Line_Rec_Type;
2730 l_Discount_Cust_tbl OE_Pricing_Cont_PUB.Discount_Cust_Tbl_Type;
2731 l_Discount_Line_tbl OE_Pricing_Cont_PUB.Discount_Line_Tbl_Type;
2732 l_Price_Break_tbl OE_Pricing_Cont_PUB.Price_Break_Tbl_Type;
2733 l_x_Price_Break_tbl OE_Pricing_Cont_PUB.Price_Break_Tbl_Type;
2734 BEGIN
2735
2736 -- Standard call to check for call compatibility
2737
2738 IF NOT FND_API.Compatible_API_Call
2739 ( l_api_version_number
2740 , p_api_version_number
2741 , l_api_name
2742 , G_PKG_NAME
2743 )
2744 THEN
2745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2746 END IF;
2747
2748 -- Initialize message list.
2749
2750 IF FND_API.to_Boolean(p_init_msg_list) THEN
2751 OE_MSG_PUB.initialize;
2752 END IF;
2753
2754 -- Get Contract ( parent = Contract )
2755
2756 l_Contract_rec := OE_Contract_Util.Query_Row
2757 ( p_pricing_contract_id => p_pricing_contract_id
2758 );
2759
2760 -- Get Agreement ( parent = Contract )
2761
2762 l_Agreement_tbl := OE_Agreement_Util.Query_Rows
2763 ( p_pricing_contract_id => l_Contract_rec.pricing_contract_id
2764 );
2765
2766 IF l_Agreement_tbl.COUNT > 0 THEN
2767 l_Agreement_rec := l_Agreement_tbl(1);
2768 END IF;
2769
2770
2771 -- Get Price_LHeader ( parent = Contract )
2772
2773 l_Price_LHeader_tbl(1) := OE_Price_List_Util.Query_Row
2774 ( p_name => l_Price_LHeader_rec.name
2775 ,p_price_list_id => l_contract_rec.pricing_contract_id
2776 );
2777
2778 IF l_Price_LHeader_tbl.COUNT > 0 THEN
2779 l_Price_LHeader_rec := l_Price_LHeader_tbl(1);
2780 END IF;
2781
2782
2783 -- Get Price_LLine ( parent = Price_LHeader )
2784
2785 l_Price_LLine_tbl(1) := OE_Price_List_Line_Util.Query_Row
2786 ( p_price_list_line_id => l_Price_LLine_rec.price_list_line_id
2787 , p_price_list_id => l_Price_LLine_rec.price_list_id
2788 );
2789
2790 IF l_Price_LLine_tbl.COUNT > 0 THEN
2791 l_Price_LLine_rec := l_Price_LLine_tbl(1);
2792 END IF;
2793
2794 /*
2795 -- Get Discount_Header ( parent = Contract )
2796 l_Discount_Header_tbl := OE_Discount_Header_Util.Query_Rows
2797 ( p_pricing_contract_id => l_Contract_rec.pricing_contract_id
2798 );
2799
2800 IF l_Discount_Header_tbl.COUNT > 0 THEN
2801 l_Discount_Header_rec := l_Discount_Header_tbl(1);
2802 END IF;
2803
2804
2805 -- Get Discount_Cust ( parent = Discount_Header )
2806
2807 l_Discount_Cust_tbl := OE_Discount_Cust_Util.Query_Rows
2808 ( p_discount_id => l_Discount_Header_rec.discount_id
2809 );
2810
2811
2812 -- Get Discount_Line ( parent = Discount_Header )
2813
2814 l_Discount_Line_tbl := OE_Discount_Line_Util.Query_Rows
2815 ( p_discount_id => l_Discount_Header_rec.discount_id
2816 );
2817
2818
2819 -- Loop over Discount_Line's children
2820
2821 FOR I3 IN 1..l_Discount_Line_tbl.COUNT LOOP
2822
2823 -- Get Price_Break ( parent = Discount_Line )
2824
2825 l_Price_Break_tbl := OE_Price_Break_Util.Query_Rows
2826 ( p_discount_line_id => l_Discount_Line_tbl(I3).discount_line_id
2827 );
2828
2829 FOR I4 IN 1..l_Price_Break_tbl.COUNT LOOP
2830 l_Price_Break_tbl(I4).Discount_Line_Index := I3;
2831 l_x_Price_Break_tbl
2832 (l_x_Price_Break_tbl.COUNT + 1) := l_Price_Break_tbl(I4);
2833 END LOOP;
2834
2835
2836 END LOOP;
2837 */
2838
2839 -- Load out parameters
2840
2841 x_Contract_rec := l_Contract_rec;
2842 x_Agreement_rec := l_Agreement_rec;
2843 x_Price_LHeader_rec := l_Price_LHeader_rec;
2844 x_Discount_Header_rec := l_Discount_Header_rec;
2845 x_Price_LLine_tbl := l_Price_LLine_tbl;
2846 x_Discount_Cust_tbl := l_Discount_Cust_tbl;
2847 x_Discount_Line_tbl := l_Discount_Line_tbl;
2848 x_Price_Break_tbl := l_x_Price_Break_tbl;
2849
2850 -- Set return status
2851
2852 x_return_status := FND_API.G_RET_STS_SUCCESS;
2853
2854 -- Get message count and data
2855
2856 OE_MSG_PUB.Count_And_Get
2857 ( p_count => x_msg_count
2858 , p_data => x_msg_data
2859 );
2860
2861
2862 EXCEPTION
2863
2864 WHEN FND_API.G_EXC_ERROR THEN
2865
2866 x_return_status := FND_API.G_RET_STS_ERROR;
2867
2868 -- Get message count and data
2869
2870 OE_MSG_PUB.Count_And_Get
2871 ( p_count => x_msg_count
2872 , p_data => x_msg_data
2873 );
2874
2875 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2876
2877 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2878
2879 -- Get message count and data
2880
2881 OE_MSG_PUB.Count_And_Get
2882 ( p_count => x_msg_count
2883 , p_data => x_msg_data
2884 );
2885
2886 WHEN OTHERS THEN
2887
2888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2889
2890 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2891 THEN
2892 OE_MSG_PUB.Add_Exc_Msg
2893 ( G_PKG_NAME
2894 , 'Get_Pricing_Cont'
2895 );
2896 END IF;
2897
2898 -- Get message count and data
2899
2900 OE_MSG_PUB.Count_And_Get
2901 ( p_count => x_msg_count
2902 , p_data => x_msg_data
2903 );
2904
2905 END Get_Pricing_Cont;
2906
2907
2908
2909 FUNCTION Check_Delete_Agreement( p_Price_List_id IN NUMBER)
2910 RETURN BOOLEAN
2911
2912 IS
2913 l_price_list_id NUMBER;
2914 l_count NUMBER;
2915 BEGIN
2916
2917 SELECT count(*)
2918 into l_count
2919 from oe_agreements_b
2920 where price_list_id = p_Price_List_id;
2921
2922 oe_debug_pub.add('Geresh In Check Delete Agreement' || p_Price_List_Id);
2923
2924 if ( l_count = 1) then
2925 RETURN TRUE;
2926 else
2927 if l_count > 1 THEN
2928 RETURN FALSE;
2929 else
2930 RETURN TRUE;
2931 end if;
2932 end if;
2933
2934
2935
2936
2937 EXCEPTION
2938 WHEN NO_DATA_FOUND THEN
2939 oe_debug_pub.add('Geresh In No Data Found Check Delete Agreement' || p_Price_List_Id);
2940 RETURN TRUE;
2941
2942 WHEN TOO_MANY_ROWS THEN
2943 oe_debug_pub.add('Geresh In No Data Found Check Delete Agreement' || p_Price_List_Id);
2944 RETURN FALSE;
2945
2946
2947 END Check_Delete_Agreement;
2948
2949 -------------
2950 FUNCTION Check_Order_Lines_Agreement( p_Agreement_id IN NUMBER)
2951 RETURN BOOLEAN
2952 IS
2953 l_agreement_id NUMBER;
2954 l_exist varchar2(1) := 'Y';
2955 BEGIN
2956
2957 /* Commented following code for Bug 2321498 */
2958
2959 /*
2960 oe_debug_pub.add('Geresh In Check Order Header' || p_Agreement_id);
2961 SELECT agreement_id into
2962 l_agreement_id
2963 from oe_order_lines
2964 where agreement_id = p_Agreement_id;
2965
2966
2967 oe_debug_pub.add('Geresh In Check Order Header After sql' || sql%rowcount);
2968 if SQL%ROWCOUNT > 0 then
2969 RETURN FALSE;
2970 else
2971 RETURN TRUE;
2972 end if; */
2973
2974 Select 'N' into l_exist from dual where
2975 not exists (select 1 from oe_order_lines
2976 where agreement_id = p_Agreement_id);
2977 oe_debug_pub.add('Geresh In Check Order Line After sql');
2978 oe_debug_pub.add('l_exist :'||l_exist);
2979
2980 If l_exist = 'N' Then
2981 RETURN TRUE;
2982 Else
2983 RETURN FALSE;
2984 End If;
2985 --oe_debug_pub.add('Geresh In Check Order Line After sql');
2986 --oe_debug_pub.add('l_exist :'||l_exist);
2987 EXCEPTION
2988 WHEN NO_DATA_FOUND THEN
2989 RETURN FALSE;
2990
2991 WHEN TOO_MANY_ROWS THEN
2992 RETURN TRUE;
2993
2994
2995
2996 END Check_Order_Lines_Agreement;
2997 --------
2998
2999 FUNCTION Check_Order_Agreement( p_Agreement_id IN NUMBER)
3000 RETURN BOOLEAN
3001 IS
3002 l_agreement_id NUMBER;
3003 l_exist VARCHAR2(1) := 'Y';
3004 BEGIN
3005 --Commented following code for bug 2321498
3006
3007 /*
3008 oe_debug_pub.add('Geresh In Check Order Header' || p_Agreement_id);
3009 SELECT agreement_id into
3010 l_agreement_id
3011 from oe_order_headers
3012 where agreement_id = p_Agreement_id;
3013
3014
3015 oe_debug_pub.add('Geresh In Check Order Header After sql' || sql%rowcount);
3016 if SQL%ROWCOUNT > 0 then
3017 RETURN FALSE;
3018 else
3019 RETURN TRUE;
3020 end if;
3021 */
3022 --Added following code for Bug 2321498
3023 Select 'N' into l_exist from dual where
3024 not exists (select 1 from oe_order_headers
3025 where agreement_id = p_Agreement_id);
3026 oe_debug_pub.add('Geresh In Check Order Header After sql');
3027 oe_debug_pub.add('l_exist :'||l_exist);
3028
3029 If l_exist = 'N' Then
3030 RETURN TRUE;
3031 Else
3032 RETURN FALSE;
3033 End If;
3034
3035
3036 EXCEPTION
3037 WHEN NO_DATA_FOUND THEN
3038 RETURN FALSE;
3039
3040 WHEN TOO_MANY_ROWS THEN
3041 RETURN TRUE;
3042
3043
3044
3045 END Check_Order_Agreement;
3046
3047 /*procedure added by spgopal 08/14/00 for duplication of qualifier on price list updates*/
3048
3049 /*
3050 PROCEDURE Create_Agreement_Qualifier
3051 (p_list_header_id IN NUMBER,
3052 p_old_list_header_id IN NUMBER,
3053 p_Agreement_id IN NUMBER,
3054 x_return_status OUT NOCOPY VARCHAR2); -- file.sql.39 change
3055 */
3056
3057
3058 PROCEDURE Create_Agreement_Qualifier
3059 (p_list_header_id IN NUMBER,
3060 p_old_list_header_id IN NUMBER,
3061 p_Agreement_id IN NUMBER,
3062 p_operation IN VARCHAR2,
3063 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
3064
3065 /* For creating qualifiers */
3066 l_price_list_type_code QP_LIST_HEADERS_B.LIST_TYPE_CODE%TYPE;
3067 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type ;
3068 l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
3069 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type ;
3070 l_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type ;
3071 l_QUALIFIER_RULES_val_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Val_Rec_Type;
3072 --nguha 2041504
3073 l_control_rec QP_GLOBALS.Control_Rec_Type;
3074
3075 l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type ;
3076 l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
3077 l_x_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type ;
3078 l_x_QUALIFIER_RULES_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Rec_Type ;
3079 l_x_QUALIFIER_RULES_val_rec QP_Qualifier_Rules_PUB.Qualifier_Rules_Val_Rec_Type;
3080 l_return_status VARCHAR2(1);
3081 l_return_values VARCHAR2(1) := FND_API.G_FALSE;
3082 l_commit VARCHAR2(1) := FND_API.G_FALSE;
3083 l_msg_count NUMBER;
3084 l_msg_data VARCHAR2(250);
3085 l_qual_count NUMBER;
3086 l_old_qual_count NUMBER;
3087
3088 BEGIN
3089
3090 oe_debug_pub.add('begin create_agr_qual'||to_char(p_old_list_header_id)||'old'||to_char(p_list_header_id)||'new');
3091
3092 IF p_list_header_id IS NOT NULL OR
3093 p_list_header_id <> FND_API.G_MISS_NUM THEN
3094
3095
3096 if p_operation = OE_GLOBALS.G_OPR_CREATE then
3097
3098 oe_debug_pub.add('in if create'||p_operation);
3099
3100 l_qualifiers_rec.list_header_id := p_list_header_id;
3101 l_qualifiers_rec.qualifier_attr_value := p_agreement_id;
3102 l_qualifiers_rec.qualifier_context := 'CUSTOMER';
3103 l_qualifiers_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE7';
3104 l_qualifiers_rec.qualifier_grouping_no := p_agreement_id;
3105
3106
3107 l_qualifiers_rec.operation := OE_GLOBALS.G_OPR_CREATE;
3108 l_qualifiers_tbl(1) := l_qualifiers_rec;
3109 --nguha 2041504
3110 -- set the called_from_ui indicator to 'Y', as
3111 -- QP_Qualifier_Rules_PVT.Process_Qualifier_Rules is being called from UI
3112
3113 l_control_rec.called_from_ui := 'Y';
3114
3115 -- We should call the private package and not the public
3116
3117 QP_Qualifier_Rules_PVT.Process_Qualifier_Rules
3118 ( p_api_version_number => 1.0
3119 , p_init_msg_list => FND_API.G_TRUE
3120 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
3121 , p_commit => FND_API.G_FALSE
3122 , x_return_status => x_return_status
3123 , x_msg_count => l_msg_count
3124 , x_msg_data => l_msg_data
3125 , p_control_rec => l_control_rec
3126 , p_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
3127 , p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
3128 , x_QUALIFIER_RULES_rec => l_x_QUALIFIER_RULES_rec
3129 , x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
3130 );
3131
3132
3133 elsif p_operation = OE_GLOBALS.G_OPR_UPDATE then
3134 oe_debug_pub.add('in if update'||p_operation);
3135
3136
3137 if p_old_list_header_id <> p_list_header_id then
3138 oe_debug_pub.add('in else id!=id'||p_operation);
3139 delete from qp_qualifiers where
3140 list_header_id = p_old_list_header_id
3141 and qualifier_context = 'CUSTOMER'
3142 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE7'
3143 and qualifier_attr_value = p_agreement_id;
3144
3145 else
3146 null;
3147 end if; --p_old_list_header_id = p_list_header_id
3148
3149 oe_debug_pub.add('in if id=id'||p_operation);
3150 BEGIN
3151
3152 select count(list_header_id) into l_qual_count
3153 from qp_qualifiers where
3154 list_header_id = p_list_header_id
3155 and qualifier_context = 'CUSTOMER'
3156 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE7'
3157 and qualifier_attr_value = p_agreement_id;
3158
3159 EXCEPTION
3160 When NO_DATA_FOUND Then
3161 l_qual_count := 0;
3162
3163 END;
3164
3165 if l_qual_count < 1 then
3166 oe_debug_pub.add('in if count'||to_char(l_qual_count));
3167 l_qualifiers_rec.list_header_id := p_list_header_id;
3168 l_qualifiers_rec.qualifier_attr_value := p_agreement_id;
3169 l_qualifiers_rec.qualifier_context := 'CUSTOMER';
3170 l_qualifiers_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE7';
3171 l_qualifiers_rec.qualifier_grouping_no := p_agreement_id;
3172
3173
3174 l_qualifiers_rec.operation := OE_GLOBALS.G_OPR_CREATE;
3175 l_qualifiers_tbl(1) := l_qualifiers_rec;
3176
3177 else
3178
3179 oe_debug_pub.add('in else count'||to_char(l_qual_count));
3180 null;
3181
3182 end if; --l_qual_count<1
3183
3184
3185 --nguha 2041504
3186 -- set the called_from_ui indicator to 'Y', as
3187 -- QP_Qualifier_Rules_PVT.Process_Qualifier_Rules is being called from UI
3188
3189 l_control_rec.called_from_ui := 'Y';
3190
3191 -- We should call the private package and not the public
3192
3193 QP_Qualifier_Rules_PVT.Process_Qualifier_Rules
3194 ( p_api_version_number => 1.0
3195 , p_init_msg_list => FND_API.G_TRUE
3196 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
3197 , p_commit => FND_API.G_FALSE
3198 , x_return_status => x_return_status
3199 , x_msg_count => l_msg_count
3200 , x_msg_data => l_msg_data
3201 , p_control_rec => l_control_rec
3202 , p_QUALIFIER_RULES_rec => l_QUALIFIER_RULES_rec
3203 , p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
3204 , x_QUALIFIER_RULES_rec => l_x_QUALIFIER_RULES_rec
3205 , x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
3206 );
3207
3208
3209 end if; --operation
3210 END IF;
3211
3212
3213
3214 oe_debug_pub.add('end create_agr_qual'||to_char(p_old_list_header_id)||'old'||to_char(p_list_header_id)||'new');
3215
3216 EXCEPTION
3217
3218 WHEN OTHERS THEN
3219
3220 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3221 THEN
3222 OE_MSG_PUB.Add_Exc_Msg
3223 ( G_PKG_NAME
3224 , 'Process_Modifiers'
3225 );
3226 END IF;
3227
3228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3229
3230 -- Get message count and data
3231
3232 OE_MSG_PUB.Count_And_Get
3233 ( p_count => l_msg_count
3234 , p_data => l_msg_data
3235 );
3236
3237 oe_debug_pub.add('exp create_agr_qual'||to_char(p_old_list_header_id)||'old'||to_char(p_list_header_id)||'new');
3238
3239 END Create_Agreement_Qualifier;
3240
3241 END OE_Pricing_Cont_PVT;