1 PACKAGE BODY OE_Validate_Agreement AS
2 /* $Header: OEXLAGRB.pls 120.2 2005/12/14 16:00:12 shulin noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Validate_Agreement';
7
8 FUNCTION Handle_Revision (
9 p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
10 )
11 RETURN BOOLEAN ;
12
13 FUNCTION Allow_Revision (
14 p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
15 )
16 RETURN BOOLEAN ;
17
18 FUNCTION Check_EndDates (
19 p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
20 )
21 RETURN BOOLEAN ;
22
23 --Begin code added by rchellam for OKC
24 FUNCTION Valid_Agreement_Source (
25 p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
26 )
27 RETURN BOOLEAN;
28 --Begin code added by rchellam for OKC
29
30 FUNCTION Check_Dates (
31 p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
32 )
33 RETURN BOOLEAN ;
34
35 -- Procedure Entity
36
37 PROCEDURE Entity
38 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
39 , p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
40 , p_old_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type :=
41 OE_Pricing_Cont_PUB.G_MISS_AGREEMENT_REC
42 )
43 IS
44 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
45 BEGIN
46
47 -- Check required attributes.
48
49 IF p_Agreement_rec.agreement_id IS NULL
50 THEN
51
52 l_return_status := FND_API.G_RET_STS_ERROR;
53
54 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
55 THEN
56
57 FND_MESSAGE.SET_NAME('OE','OE_ATTRIBUTE_REQUIRED');
58 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','accounting_rule');
59 OE_MSG_PUB.Add;
60
61 END IF;
62
63 END IF;
64
65 --Begin code added by rchellam for OKC
66 IF p_Agreement_rec.agreement_source_code = 'PAGR' AND
67 p_Agreement_rec.price_list_id IS NULL
68 THEN
69
70 l_return_status := FND_API.G_RET_STS_ERROR;
71
72 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
73 THEN
74
75 FND_MESSAGE.SET_NAME('OE','OE_ATTRIBUTE_REQUIRED');
76 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Price List Id');
77 OE_MSG_PUB.Add;
78
79 END IF;
80
81 END IF;
82
83 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
84
85 RAISE FND_API.G_EXC_ERROR;
86
87 END IF;
88
89 --Commented by sssriniv for making terms field optional
90 /* IF p_Agreement_rec.agreement_source_code = 'PAGR' AND
91 p_Agreement_rec.term_id IS NULL
92 THEN
93
94 l_return_status := FND_API.G_RET_STS_ERROR;
95
96 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
97 THEN
98
99 FND_MESSAGE.SET_NAME('OE','OE_ATTRIBUTE_REQUIRED');
100 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Term Id');
101 OE_MSG_PUB.Add;
102
103 END IF;
104
105 END IF;
106 */
107
108 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
109
110 RAISE FND_API.G_EXC_ERROR;
111
112 END IF;
113 --End code added by rchellam for OKC
114
115 --
116 -- Check rest of required attributes here.
117 --
118 /* Revision Handling */
119 /* Check for Agreement Name exists */
120 IF p_Agreement_rec.name is not NULL THEN
121
122 -- OE_MSG_PUB.Add_Exc_Msg
123 -- ( G_PKG_NAME
124 -- , 'Checking Revsion '
125 -- );
126
127 if NOT ( Allow_Revision( p_Agreement_rec => p_Agreement_rec))
128 THEN
129 l_return_status := FND_API.G_RET_STS_ERROR;
130 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
131 THEN
132 FND_MESSAGE.SET_NAME('QP','QP_NO_REVISION');
133 OE_MSG_PUB.Add;
134 end if;
135
136 END IF;
137
138 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
139
140 RAISE FND_API.G_EXC_ERROR;
141
142 END IF;
143
144 IF NOT ( Handle_Revision (p_Agreement_rec => p_Agreement_rec ))
145 THEN
146
147 l_return_status := FND_API.G_RET_STS_ERROR;
148
149 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
150 THEN
151 FND_MESSAGE.SET_NAME('QP','QP_DUPLICATE_REVISION');
152 -- FND_MESSAGE.SET_TOKEN('ATTRIBUTE','revision');
153 OE_MSG_PUB.Add;
154 -- OE_MSG_PUB.Add_Exc_Msg
155 -- ( G_PKG_NAME
156 -- , 'Revsion Cannot be the Same'
157 -- );
158
159 END IF;
160
161 ELSE
162 /* revision is OK, check for dates overlapping */
163 if NOT ( Check_Dates(p_Agreement_rec => p_Agreement_rec ))
164 then
165
166 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
167 THEN
168 l_return_status := FND_API.G_RET_STS_ERROR;
169 FND_MESSAGE.SET_NAME('QP','QP_OVERLAPPING_START_DATE');
170 -- FND_MESSAGE.SET_TOKEN('ATTRIBUTE','revision');
171 OE_MSG_PUB.Add;
172 /*
173 OE_MSG_PUB.Add_Exc_Msg
174 ( G_PKG_NAME
175 , 'Start Dates Cannot Overlap During Agreement Revisions'
176 );
177 */
178 END IF;
179
180 end if;
181
182
183 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
184
185 RAISE FND_API.G_EXC_ERROR;
186
187 END IF;
188
189
190 IF NOT ( Check_EndDates(p_Agreement_rec => p_Agreement_rec ))
191 THEN
192
193 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
194 THEN
195 l_return_status := FND_API.G_RET_STS_ERROR;
196 FND_MESSAGE.SET_NAME('QP','QP_OVERLAPPING_END_DATE');
197 -- FND_MESSAGE.SET_TOKEN('ATTRIBUTE','revision');
198 OE_MSG_PUB.Add;
199 /*
200 OE_MSG_PUB.Add_Exc_Msg
201 ( G_PKG_NAME
202 , 'End Dates Cannot Overlap During Agreement Revisions'
203 );
204 */
205 END IF;
206
207 end if;
208
209
210 END IF;
211 END IF;
212
213 -- Return Error if a required attribute is missing.
214
215 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
216
217 RAISE FND_API.G_EXC_ERROR;
218
219 END IF;
220
221
222 --Begin code added by rchellam for OKC
223 IF NOT (Valid_Agreement_Source (p_Agreement_rec => p_Agreement_rec ))
224 THEN
225
226 l_return_status := FND_API.G_RET_STS_ERROR;
227
228 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
229 FND_MESSAGE.SET_NAME('QP','QP_INVALID_AGR_SOURCE_CODE');
230 OE_MSG_PUB.Add;
231 END IF;
232
233 END IF;
234
235 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
236
237 RAISE FND_API.G_EXC_ERROR;
238
239 END IF;
240 --End code added by rchellam for OKC
241
242
243 --
244 -- Check conditionally required attributes here.
245 --
246
247
248 --
249 -- Validate attribute dependencies here.
250 --
251
252
253 -- Done validating entity
254
255 x_return_status := l_return_status;
256
257 EXCEPTION
258
259 WHEN FND_API.G_EXC_ERROR THEN
260
261 x_return_status := FND_API.G_RET_STS_ERROR;
262
263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
264
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266
267 WHEN OTHERS THEN
268
269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
270
271 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
272 THEN
273 OE_MSG_PUB.Add_Exc_Msg
274 ( G_PKG_NAME
275 , 'Entity'
276 );
277 END IF;
278
279 END Entity;
280
281 -- Procedure Attributes
282
283 PROCEDURE Attributes
284 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
285 , p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
286 , p_old_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type :=
287 OE_Pricing_Cont_PUB.G_MISS_AGREEMENT_REC
288 )
289 IS
290 BEGIN
291
292 x_return_status := FND_API.G_RET_STS_SUCCESS;
293
294 -- Validate Agreement attributes
295
296 IF p_Agreement_rec.accounting_rule_id IS NOT NULL AND
297 ( p_Agreement_rec.accounting_rule_id <>
298 p_old_Agreement_rec.accounting_rule_id OR
299 p_old_Agreement_rec.accounting_rule_id IS NULL )
300 THEN
301 IF NOT OE_Validate_Attr.Accounting_Rule(p_Agreement_rec.accounting_rule_id) THEN
302 x_return_status := FND_API.G_RET_STS_ERROR;
303 END IF;
304 END IF;
305
306 IF p_Agreement_rec.agreement_contact_id IS NOT NULL AND
307 ( p_Agreement_rec.agreement_contact_id <>
308 p_old_Agreement_rec.agreement_contact_id OR
309 p_old_Agreement_rec.agreement_contact_id IS NULL )
310 THEN
311 IF NOT OE_Validate_Attr.Agreement_Contact(p_Agreement_rec.agreement_contact_id) THEN
312 x_return_status := FND_API.G_RET_STS_ERROR;
313 END IF;
314 END IF;
315
316 IF p_Agreement_rec.agreement_id IS NOT NULL AND
317 ( p_Agreement_rec.agreement_id <>
318 p_old_Agreement_rec.agreement_id OR
319 p_old_Agreement_rec.agreement_id IS NULL )
320 THEN
321 IF NOT OE_Validate_Attr.Agreement(p_Agreement_rec.agreement_id) THEN
322 x_return_status := FND_API.G_RET_STS_ERROR;
323 END IF;
324 END IF;
325
326 IF p_Agreement_rec.agreement_num IS NOT NULL AND
327 ( p_Agreement_rec.agreement_num <>
328 p_old_Agreement_rec.agreement_num OR
329 p_old_Agreement_rec.agreement_num IS NULL )
330 THEN
331 IF NOT OE_Validate_Attr.Agreement_Num(p_Agreement_rec.agreement_num) THEN
332 x_return_status := FND_API.G_RET_STS_ERROR;
333 END IF;
334 END IF;
335
336 IF p_Agreement_rec.agreement_type_code IS NOT NULL AND
337 ( p_Agreement_rec.agreement_type_code <>
338 p_old_Agreement_rec.agreement_type_code OR
339 p_old_Agreement_rec.agreement_type_code IS NULL )
340 THEN
341 IF NOT OE_Validate_Attr.Agreement_Type(p_Agreement_rec.agreement_type_code) THEN
342 x_return_status := FND_API.G_RET_STS_ERROR;
343 END IF;
344 END IF;
345
346 IF p_Agreement_rec.created_by IS NOT NULL AND
347 ( p_Agreement_rec.created_by <>
348 p_old_Agreement_rec.created_by OR
349 p_old_Agreement_rec.created_by IS NULL )
350 THEN
351 IF NOT OE_Validate_Attr.Created_By(p_Agreement_rec.created_by) THEN
352 x_return_status := FND_API.G_RET_STS_ERROR;
353 END IF;
354 END IF;
355
356 IF p_Agreement_rec.creation_date IS NOT NULL AND
357 ( p_Agreement_rec.creation_date <>
358 p_old_Agreement_rec.creation_date OR
359 p_old_Agreement_rec.creation_date IS NULL )
360 THEN
361 IF NOT OE_Validate_Attr.Creation_Date(p_Agreement_rec.creation_date) THEN
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 END IF;
364 END IF;
365
366 --
367
368 IF p_Agreement_rec.sold_to_org_id IS NOT NULL AND
369 ( p_Agreement_rec.sold_to_org_id <>
370 p_old_Agreement_rec.sold_to_org_id OR
371 p_old_Agreement_rec.sold_to_org_id IS NULL )
372 THEN
373 IF NOT OE_Validate_Attr.Customer(p_Agreement_rec.sold_to_org_id) THEN
374 x_return_status := FND_API.G_RET_STS_ERROR;
375 END IF;
376 END IF;
377
378
379 --
380 /*
381 IF p_Agreement_rec.customer_id IS NOT NULL AND
382 ( p_Agreement_rec.customer_id <>
383 p_old_Agreement_rec.customer_id OR
384 p_old_Agreement_rec.customer_id IS NULL )
385 THEN
386 IF NOT OE_Validate_Attr.Customer(p_Agreement_rec.customer_id) THEN
387 x_return_status := FND_API.G_RET_STS_ERROR;
388 END IF;
389 END IF;
390 */
391
392 IF p_Agreement_rec.end_date_active IS NOT NULL AND
393 ( p_Agreement_rec.end_date_active <>
394 p_old_Agreement_rec.end_date_active OR
395 p_old_Agreement_rec.end_date_active IS NULL )
396 THEN
397 IF NOT OE_Validate_Attr.End_Date_Active(p_Agreement_rec.end_date_active) THEN
398 x_return_status := FND_API.G_RET_STS_ERROR;
399 END IF;
400 END IF;
401
402 IF p_Agreement_rec.freight_terms_code IS NOT NULL AND
403 ( p_Agreement_rec.freight_terms_code <>
404 p_old_Agreement_rec.freight_terms_code OR
405 p_old_Agreement_rec.freight_terms_code IS NULL )
406 THEN
407 IF NOT OE_Validate_Attr.Freight_Terms(p_Agreement_rec.freight_terms_code) THEN
408 x_return_status := FND_API.G_RET_STS_ERROR;
409 END IF;
410 END IF;
411
412 IF p_Agreement_rec.invoice_contact_id IS NOT NULL AND
413 ( p_Agreement_rec.invoice_contact_id <>
414 p_old_Agreement_rec.invoice_contact_id OR
415 p_old_Agreement_rec.invoice_contact_id IS NULL )
416 THEN
417 IF NOT OE_Validate_Attr.Invoice_Contact(p_Agreement_rec.invoice_contact_id) THEN
418 x_return_status := FND_API.G_RET_STS_ERROR;
419 END IF;
420 END IF;
421
422 -- Changes
423
424 IF p_Agreement_rec.invoice_to_org_id IS NOT NULL AND
425 ( p_Agreement_rec.invoice_to_org_id <>
426 p_old_Agreement_rec.invoice_to_org_id OR
427 p_old_Agreement_rec.invoice_to_org_id IS NULL )
428 THEN
429 IF NOT OE_Validate_Attr.Invoice_To_Site_Use(p_Agreement_rec.invoice_to_org_id) THEN
430 x_return_status := FND_API.G_RET_STS_ERROR;
431 END IF;
432 END IF;
433
434 --
435 /* IF p_Agreement_rec.invoice_to_site_use_id IS NOT NULL AND
436 ( p_Agreement_rec.invoice_to_site_use_id <>
437 p_old_Agreement_rec.invoice_to_site_use_id OR
438 p_old_Agreement_rec.invoice_to_site_use_id IS NULL )
439 THEN
440 IF NOT OE_Validate_Attr.Invoice_To_Site_Use(p_Agreement_rec.invoice_to_site_use_id) THEN
441 x_return_status := FND_API.G_RET_STS_ERROR;
442 END IF;
443 END IF;
444 */
445
446 IF p_Agreement_rec.invoicing_rule_id IS NOT NULL AND
447 ( p_Agreement_rec.invoicing_rule_id <>
448 p_old_Agreement_rec.invoicing_rule_id OR
449 p_old_Agreement_rec.invoicing_rule_id IS NULL )
450 THEN
451 IF NOT OE_Validate_Attr.Invoicing_Rule(p_Agreement_rec.invoicing_rule_id) THEN
452 x_return_status := FND_API.G_RET_STS_ERROR;
453 END IF;
454 END IF;
455
456 IF p_Agreement_rec.last_updated_by IS NOT NULL AND
457 ( p_Agreement_rec.last_updated_by <>
458 p_old_Agreement_rec.last_updated_by OR
459 p_old_Agreement_rec.last_updated_by IS NULL )
460 THEN
461 IF NOT OE_Validate_Attr.Last_Updated_By(p_Agreement_rec.last_updated_by) THEN
462 x_return_status := FND_API.G_RET_STS_ERROR;
463 END IF;
464 END IF;
465
466 IF p_Agreement_rec.last_update_date IS NOT NULL AND
467 ( p_Agreement_rec.last_update_date <>
468 p_old_Agreement_rec.last_update_date OR
469 p_old_Agreement_rec.last_update_date IS NULL )
470 THEN
471 IF NOT OE_Validate_Attr.Last_Update_Date(p_Agreement_rec.last_update_date) THEN
472 x_return_status := FND_API.G_RET_STS_ERROR;
473 END IF;
474 END IF;
475
476 IF p_Agreement_rec.last_update_login IS NOT NULL AND
477 ( p_Agreement_rec.last_update_login <>
478 p_old_Agreement_rec.last_update_login OR
479 p_old_Agreement_rec.last_update_login IS NULL )
480 THEN
481 IF NOT OE_Validate_Attr.Last_Update_Login(p_Agreement_rec.last_update_login) THEN
482 x_return_status := FND_API.G_RET_STS_ERROR;
483 END IF;
484 END IF;
485
486 IF p_Agreement_rec.name IS NOT NULL AND
487 ( p_Agreement_rec.name <>
488 p_old_Agreement_rec.name OR
489 p_old_Agreement_rec.name IS NULL )
490 THEN
491 IF NOT OE_Validate_Attr.Name(p_Agreement_rec.name) THEN
492 x_return_status := FND_API.G_RET_STS_ERROR;
493 END IF;
494
495 /* Check Revisions */
496
497
498
499
500
501 END IF;
502
503 IF p_Agreement_rec.override_arule_flag IS NOT NULL AND
504 ( p_Agreement_rec.override_arule_flag <>
505 p_old_Agreement_rec.override_arule_flag OR
506 p_old_Agreement_rec.override_arule_flag IS NULL )
507 THEN
508 IF NOT OE_Validate_Attr.Override_Arule(p_Agreement_rec.override_arule_flag) THEN
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 END IF;
511 END IF;
512
513 IF p_Agreement_rec.override_irule_flag IS NOT NULL AND
514 ( p_Agreement_rec.override_irule_flag <>
515 p_old_Agreement_rec.override_irule_flag OR
516 p_old_Agreement_rec.override_irule_flag IS NULL )
517 THEN
518 IF NOT OE_Validate_Attr.Override_Irule(p_Agreement_rec.override_irule_flag) THEN
519 x_return_status := FND_API.G_RET_STS_ERROR;
520 END IF;
521 END IF;
522
523 IF p_Agreement_rec.price_list_id IS NOT NULL AND
524 ( p_Agreement_rec.price_list_id <>
525 p_old_Agreement_rec.price_list_id OR
526 p_old_Agreement_rec.price_list_id IS NULL )
527 THEN
528 IF NOT OE_Validate_Attr.Price_List(p_Agreement_rec.price_list_id) THEN
529 x_return_status := FND_API.G_RET_STS_ERROR;
530 END IF;
531 END IF;
532
533 IF p_Agreement_rec.purchase_order_num IS NOT NULL AND
534 ( p_Agreement_rec.purchase_order_num <>
535 p_old_Agreement_rec.purchase_order_num OR
536 p_old_Agreement_rec.purchase_order_num IS NULL )
537 THEN
538 IF NOT OE_Validate_Attr.Purchase_Order_Num(p_Agreement_rec.purchase_order_num) THEN
539 x_return_status := FND_API.G_RET_STS_ERROR;
540 END IF;
541 END IF;
542
543 IF p_Agreement_rec.revision IS NOT NULL AND
544 ( p_Agreement_rec.revision <>
545 p_old_Agreement_rec.revision OR
546 p_old_Agreement_rec.revision IS NULL )
547 THEN
548 IF NOT OE_Validate_Attr.Revision(p_Agreement_rec.revision) THEN
549 x_return_status := FND_API.G_RET_STS_ERROR;
550 END IF;
551 END IF;
552
553 IF p_Agreement_rec.revision_date IS NOT NULL AND
554 ( p_Agreement_rec.revision_date <>
555 p_old_Agreement_rec.revision_date OR
556 p_old_Agreement_rec.revision_date IS NULL )
557 THEN
558 IF NOT OE_Validate_Attr.Revision_Date(p_Agreement_rec.revision_date) THEN
559 x_return_status := FND_API.G_RET_STS_ERROR;
560 END IF;
561 END IF;
562
563 IF p_Agreement_rec.revision_reason_code IS NOT NULL AND
564 ( p_Agreement_rec.revision_reason_code <>
565 p_old_Agreement_rec.revision_reason_code OR
566 p_old_Agreement_rec.revision_reason_code IS NULL )
567 THEN
568 IF NOT OE_Validate_Attr.Revision_Reason(p_Agreement_rec.revision_reason_code) THEN
569 x_return_status := FND_API.G_RET_STS_ERROR;
570 END IF;
571 END IF;
572
573 IF p_Agreement_rec.salesrep_id IS NOT NULL AND
574 ( p_Agreement_rec.salesrep_id <>
575 p_old_Agreement_rec.salesrep_id OR
576 p_old_Agreement_rec.salesrep_id IS NULL )
577 THEN
578 IF NOT OE_Validate_Attr.Salesrep(p_Agreement_rec.salesrep_id) THEN
579 x_return_status := FND_API.G_RET_STS_ERROR;
580 END IF;
581 END IF;
582
583 IF p_Agreement_rec.ship_method_code IS NOT NULL AND
584 ( p_Agreement_rec.ship_method_code <>
585 p_old_Agreement_rec.ship_method_code OR
586 p_old_Agreement_rec.ship_method_code IS NULL )
587 THEN
588 IF NOT OE_Validate_Attr.Ship_Method(p_Agreement_rec.ship_method_code) THEN
589 x_return_status := FND_API.G_RET_STS_ERROR;
590 END IF;
591 END IF;
592
593 IF p_Agreement_rec.signature_date IS NOT NULL AND
594 ( p_Agreement_rec.signature_date <>
595 p_old_Agreement_rec.signature_date OR
596 p_old_Agreement_rec.signature_date IS NULL )
597 THEN
598 IF NOT OE_Validate_Attr.Signature_Date(p_Agreement_rec.signature_date) THEN
599 x_return_status := FND_API.G_RET_STS_ERROR;
600 END IF;
601 END IF;
602
603 IF p_Agreement_rec.start_date_active IS NOT NULL AND
604 ( p_Agreement_rec.start_date_active <>
605 p_old_Agreement_rec.start_date_active OR
606 p_old_Agreement_rec.start_date_active IS NULL )
607 THEN
608 IF NOT OE_Validate_Attr.Start_Date_Active(p_Agreement_rec.start_date_active) THEN
609 x_return_status := FND_API.G_RET_STS_ERROR;
610 END IF;
611 END IF;
612
613 IF p_Agreement_rec.term_id IS NOT NULL AND
614 ( p_Agreement_rec.term_id <>
615 p_old_Agreement_rec.term_id OR
616 p_old_Agreement_rec.term_id IS NULL )
617 THEN
618 IF NOT OE_Validate_Attr.Term(p_Agreement_rec.term_id) THEN
619 x_return_status := FND_API.G_RET_STS_ERROR;
620 END IF;
621 END IF;
622
623 -- Added for bug#4029589
624 IF p_Agreement_rec.invoice_to_customer_id IS NOT NULL AND
625 ( p_Agreement_rec.invoice_to_customer_id <>
626 p_old_Agreement_rec.invoice_to_customer_id OR
627 p_old_Agreement_rec.invoice_to_customer_id IS NULL )
628 THEN
629 IF NOT OE_Validate_Attr.Invoice_To_Customer_Id(p_Agreement_rec.invoice_to_customer_id) THEN
630 x_return_status := FND_API.G_RET_STS_ERROR;
631 END IF;
632 END IF;
633
634 IF (p_Agreement_rec.attribute1 IS NOT NULL AND
635 ( p_Agreement_rec.attribute1 <>
636 p_old_Agreement_rec.attribute1 OR
637 p_old_Agreement_rec.attribute1 IS NULL ))
638 OR (p_Agreement_rec.attribute10 IS NOT NULL AND
639 ( p_Agreement_rec.attribute10 <>
640 p_old_Agreement_rec.attribute10 OR
641 p_old_Agreement_rec.attribute10 IS NULL ))
642 OR (p_Agreement_rec.attribute11 IS NOT NULL AND
643 ( p_Agreement_rec.attribute11 <>
644 p_old_Agreement_rec.attribute11 OR
645 p_old_Agreement_rec.attribute11 IS NULL ))
646 OR (p_Agreement_rec.attribute12 IS NOT NULL AND
647 ( p_Agreement_rec.attribute12 <>
648 p_old_Agreement_rec.attribute12 OR
649 p_old_Agreement_rec.attribute12 IS NULL ))
650 OR (p_Agreement_rec.attribute13 IS NOT NULL AND
651 ( p_Agreement_rec.attribute13 <>
652 p_old_Agreement_rec.attribute13 OR
653 p_old_Agreement_rec.attribute13 IS NULL ))
654 OR (p_Agreement_rec.attribute14 IS NOT NULL AND
655 ( p_Agreement_rec.attribute14 <>
656 p_old_Agreement_rec.attribute14 OR
657 p_old_Agreement_rec.attribute14 IS NULL ))
658 OR (p_Agreement_rec.attribute15 IS NOT NULL AND
659 ( p_Agreement_rec.attribute15 <>
660 p_old_Agreement_rec.attribute15 OR
661 p_old_Agreement_rec.attribute15 IS NULL ))
662 OR (p_Agreement_rec.attribute2 IS NOT NULL AND
663 ( p_Agreement_rec.attribute2 <>
664 p_old_Agreement_rec.attribute2 OR
665 p_old_Agreement_rec.attribute2 IS NULL ))
666 OR (p_Agreement_rec.attribute3 IS NOT NULL AND
667 ( p_Agreement_rec.attribute3 <>
668 p_old_Agreement_rec.attribute3 OR
669 p_old_Agreement_rec.attribute3 IS NULL ))
670 OR (p_Agreement_rec.attribute4 IS NOT NULL AND
671 ( p_Agreement_rec.attribute4 <>
672 p_old_Agreement_rec.attribute4 OR
673 p_old_Agreement_rec.attribute4 IS NULL ))
674 OR (p_Agreement_rec.attribute5 IS NOT NULL AND
675 ( p_Agreement_rec.attribute5 <>
676 p_old_Agreement_rec.attribute5 OR
677 p_old_Agreement_rec.attribute5 IS NULL ))
678 OR (p_Agreement_rec.attribute6 IS NOT NULL AND
679 ( p_Agreement_rec.attribute6 <>
680 p_old_Agreement_rec.attribute6 OR
681 p_old_Agreement_rec.attribute6 IS NULL ))
682 OR (p_Agreement_rec.attribute7 IS NOT NULL AND
683 ( p_Agreement_rec.attribute7 <>
684 p_old_Agreement_rec.attribute7 OR
685 p_old_Agreement_rec.attribute7 IS NULL ))
686 OR (p_Agreement_rec.attribute8 IS NOT NULL AND
687 ( p_Agreement_rec.attribute8 <>
688 p_old_Agreement_rec.attribute8 OR
689 p_old_Agreement_rec.attribute8 IS NULL ))
690 OR (p_Agreement_rec.attribute9 IS NOT NULL AND
691 ( p_Agreement_rec.attribute9 <>
692 p_old_Agreement_rec.attribute9 OR
693 p_old_Agreement_rec.attribute9 IS NULL ))
694 OR (p_Agreement_rec.context IS NOT NULL AND
695 ( p_Agreement_rec.context <>
696 p_old_Agreement_rec.context OR
697 p_old_Agreement_rec.context IS NULL ))
698 THEN
699
700 -- These calls are temporarily commented out NOCOPY /* file.sql.39 change */
701
702 /*
703 FND_FLEX_DESC_VAL.Set_Column_Value
704 ( column_name => 'ATTRIBUTE1'
705 , column_value => p_Agreement_rec.attribute1
706 );
707 FND_FLEX_DESC_VAL.Set_Column_Value
708 ( column_name => 'ATTRIBUTE10'
709 , column_value => p_Agreement_rec.attribute10
710 );
711 FND_FLEX_DESC_VAL.Set_Column_Value
712 ( column_name => 'ATTRIBUTE11'
713 , column_value => p_Agreement_rec.attribute11
714 );
715 FND_FLEX_DESC_VAL.Set_Column_Value
716 ( column_name => 'ATTRIBUTE12'
717 , column_value => p_Agreement_rec.attribute12
718 );
719 FND_FLEX_DESC_VAL.Set_Column_Value
720 ( column_name => 'ATTRIBUTE13'
721 , column_value => p_Agreement_rec.attribute13
722 );
723 FND_FLEX_DESC_VAL.Set_Column_Value
724 ( column_name => 'ATTRIBUTE14'
725 , column_value => p_Agreement_rec.attribute14
726 );
727 FND_FLEX_DESC_VAL.Set_Column_Value
728 ( column_name => 'ATTRIBUTE15'
729 , column_value => p_Agreement_rec.attribute15
730 );
731 FND_FLEX_DESC_VAL.Set_Column_Value
732 ( column_name => 'ATTRIBUTE2'
733 , column_value => p_Agreement_rec.attribute2
734 );
735 FND_FLEX_DESC_VAL.Set_Column_Value
736 ( column_name => 'ATTRIBUTE3'
737 , column_value => p_Agreement_rec.attribute3
738 );
739 FND_FLEX_DESC_VAL.Set_Column_Value
740 ( column_name => 'ATTRIBUTE4'
741 , column_value => p_Agreement_rec.attribute4
742 );
743 FND_FLEX_DESC_VAL.Set_Column_Value
744 ( column_name => 'ATTRIBUTE5'
745 , column_value => p_Agreement_rec.attribute5
746 );
747 FND_FLEX_DESC_VAL.Set_Column_Value
748 ( column_name => 'ATTRIBUTE6'
749 , column_value => p_Agreement_rec.attribute6
750 );
751 FND_FLEX_DESC_VAL.Set_Column_Value
752 ( column_name => 'ATTRIBUTE7'
753 , column_value => p_Agreement_rec.attribute7
754 );
755 FND_FLEX_DESC_VAL.Set_Column_Value
756 ( column_name => 'ATTRIBUTE8'
757 , column_value => p_Agreement_rec.attribute8
758 );
759 FND_FLEX_DESC_VAL.Set_Column_Value
760 ( column_name => 'ATTRIBUTE9'
761 , column_value => p_Agreement_rec.attribute9
762 );
763 FND_FLEX_DESC_VAL.Set_Column_Value
764 ( column_name => 'CONTEXT'
765 , column_value => p_Agreement_rec.context
766 );
767 */
768
769 -- Validate descriptive flexfield.
770
771 IF NOT OE_Validate_Attr.Desc_Flex( 'AGREEMENT' ) THEN
772 x_return_status := FND_API.G_RET_STS_ERROR;
773 END IF;
774
775 END IF;
776
777 -- Done validating attributes
778
779 EXCEPTION
780
781 WHEN FND_API.G_EXC_ERROR THEN
782
783 x_return_status := FND_API.G_RET_STS_ERROR;
784
785 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
786
787 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
788
789 WHEN OTHERS THEN
790
791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
792
793 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
794 THEN
795 OE_MSG_PUB.Add_Exc_Msg
796 ( G_PKG_NAME
797 , 'Attributes'
798 );
799 END IF;
800
801 END Attributes;
802
803 -- Procedure Entity_Delete
804
805 PROCEDURE Entity_Delete
806 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
807 , p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
808 )
809 IS
810 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
811 BEGIN
812
813 -- Validate entity delete.
814
815 NULL;
816
817 -- Done.
818
819 x_return_status := l_return_status;
820
821 EXCEPTION
822
823 WHEN FND_API.G_EXC_ERROR THEN
824
825 x_return_status := FND_API.G_RET_STS_ERROR;
826
827 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
828
829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
830
831 WHEN OTHERS THEN
832
833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
834
835 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
836 THEN
837 OE_MSG_PUB.Add_Exc_Msg
838 ( G_PKG_NAME
839 , 'Entity_Delete'
840 );
841 END IF;
842
843 END Entity_Delete;
844
845
846
847 FUNCTION Check_EndDates (
848 p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
849 )
850 RETURN BOOLEAN
851 IS
852 l_count NUMBER;
853 l_dummy VARCHAR2(10);
854 BEGIN
855
856 /* Check Agreement Exists */
857 IF p_Agreement_rec.name is NOT NULL THEN
858 /* Check for end dates overlap */
859
860 select 'VALID' into l_dummy
861 from oe_agreements
862 where name = p_Agreement_rec.name
863 and ( nvl( trunc(start_date_active),sysdate) between
864 nvl(p_Agreement_rec.start_date_active, sysdate)
865 and
866 nvl(p_Agreement_rec.end_date_active, sysdate)
867 OR nvl(trunc(end_date_active), sysdate) between
868 nvl(p_Agreement_rec.start_date_active , sysdate)
869 and
870 nvl(p_Agreement_rec.end_date_active , sysdate)
871 );
872
873 if l_count = 0 THEN
874 RETURN TRUE;
875 else
876 RETURN FALSE;
877 end if;
878 -- OE_MSG_PUB.Add_Exc_Msg
879 -- ( G_PKG_NAME
880 -- , 'End Dates are Overlapping' || l_count
881 -- );
882 /* Revison needs to be changed */
883 END IF;
884
885
886 EXCEPTION
887 WHEN NO_DATA_FOUND THEN
888 RETURN TRUE;
889 WHEN TOO_MANY_ROWS THEN
890 RETURN FALSE;
891
892 END Check_EndDates;
893
894
895 --
896
897 FUNCTION Check_Dates (
898 p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
899 )
900 RETURN BOOLEAN
901 IS
902 l_count NUMBER;
903 l_dummy VARCHAR2(10);
904 BEGIN
905
906 /* Check Agreement Exists */
907 IF p_Agreement_rec.name is NOT NULL THEN
908
909 /* Check for start dates */
910
911 select count(*)
912 into l_count
913 from oe_agreements
914 where name = p_Agreement_rec.name
915 and nvl(p_Agreement_rec.start_date_active , sysdate)
916 BETWEEN nvl(trunc(start_date_active), sysdate) and
917 nvl(trunc(end_date_active),sysdate)
918 and p_Agreement_rec.revision <> revision;
919
920 if l_count = 0 THEN
921 RETURN TRUE;
922 else
923 /* Revison needs to be changed */
924 RETURN FALSE;
925 end if;
926 END IF;
927
928
929 EXCEPTION
930 WHEN NO_DATA_FOUND THEN
931 RETURN TRUE;
932
933 WHEN TOO_MANY_ROWS THEN
934 RETURN TRUE;
935
936 END Check_Dates;
937
938 --
939
940 FUNCTION Allow_Revision (
941 p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
942 )
943 RETURN BOOLEAN
944 IS
945 l_count NUMBER;
946 BEGIN
947
948 SELECT count(*)
949 into l_count
950 from oe_agreements
951 where name = p_Agreement_rec.name
952 -- and revision = '1' /* User may enter revision 2 to start with */
953 and ( start_date_active is null
954 or
955 end_date_active is null ) ;
956
957
958 if l_count = 0 THEN
959 RETURN TRUE;
960 else
961 RETURN FALSE;
962 END IF;
963
964 EXCEPTION
965 WHEN NO_DATA_FOUND THEN
966 RETURN TRUE;
967 WHEN TOO_MANY_ROWS THEN
968 NULL;
969 END Allow_Revision;
970
971
972 --Begin code added by rchellam for OKC
973
974 FUNCTION Valid_Agreement_Source (
975 p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
976 )
977 RETURN BOOLEAN
978 IS
979 l_dummy VARCHAR2(10);
980 BEGIN
981
982 --Check if agreement_source_code is valid
983 IF p_Agreement_rec.agreement_source_code is NOT NULL THEN
984
985 SELECT 'VALID'
986 INTO l_dummy
987 FROM qp_lookups
988 WHERE lookup_type = 'AGREEMENT_SOURCE_CODE'
989 AND lookup_code = p_Agreement_rec.agreement_source_code;
990
991 RETURN TRUE;
992
993 END IF;
994
995 EXCEPTION
996 WHEN NO_DATA_FOUND THEN
997 RETURN FALSE;
998
999 END Valid_Agreement_Source;
1000
1001 --End code added by rchellam for OKC
1002
1003
1004 FUNCTION Handle_Revision (
1005 p_Agreement_rec IN OE_Pricing_Cont_PUB.Agreement_Rec_Type
1006 )
1007 RETURN BOOLEAN
1008 IS
1009 l_dummy VARCHAR2(100);
1010 BEGIN
1011
1012 /* Check Agreement Exists */
1013 IF p_Agreement_rec.name is NOT NULL THEN
1014 SELECT 'VALID'
1015 into l_dummy
1016 FROM OE_AGREEMENTS
1017 where name = p_Agreement_rec.name
1018 and revision = p_Agreement_rec.revision;
1019
1020
1021 -- OE_MSG_PUB.Add_Exc_Msg
1022 -- ( G_PKG_NAME
1023 -- , 'Agreement Record Exists'
1024 -- );
1025
1026 /* Revison needs to be changed */
1027 RETURN FALSE;
1028 END IF;
1029
1030
1031 EXCEPTION
1032 WHEN NO_DATA_FOUND THEN
1033 RETURN TRUE;
1034
1035
1036 END Handle_Revision;
1037
1038 END OE_Validate_Agreement;
[Home] [Help]