[Home] [Help]
PACKAGE BODY: APPS.QP_VALIDATE_LIMITS
Source
1 PACKAGE BODY QP_Validate_Limits AS
2 /* $Header: QPXLLMTB.pls 120.1 2005/06/08 04:27:49 appldev $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Validate_Limits';
7
8 PROCEDURE Validate_List_Header_Limits
9 (x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
10 ,p_LIMITS_rec IN QP_Limits_PUB.Limits_Rec_Type
11 )
12 IS
13 l_list_type_code VARCHAR2(30) := null;
14 l_error_code NUMBER := 0;
15 l_return_status VARCHAR2(1);
16 BEGIN
17
18
19 SELECT list_type_code into l_list_type_code from QP_LIST_HEADERS_B
20 WHERE LIST_HEADER_ID = p_LIMITS_rec.list_header_id;
21
22 IF (l_list_type_code IN ('DLT','SLT','DEL','PRO'))
23 THEN
24 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','COST','USAGE'))
25 THEN
26 IF p_LIMITS_rec.limit_level_code = 'TRANSACTION'
27 THEN
28 IF (p_LIMITS_rec.organization_flag = 'N')
29 THEN
30 IF ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'CUSTOMER') AND
31 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'CUSTOMER'))
32 THEN
33 l_error_code := 0;
34 x_return_status := FND_API.G_RET_STS_SUCCESS;
35 return;
36 ELSE
37 x_return_status := FND_API.G_RET_STS_ERROR;
38 FND_MESSAGE.SET_NAME('QP','QP_CONT_CUST_NOT_ALLOWED');
39 OE_MSG_PUB.Add;
40 l_error_code := 1;
41 return;
42 END IF;
43 ELSE
44 x_return_status := FND_API.G_RET_STS_ERROR;
45 FND_MESSAGE.SET_NAME('QP','QP_ORG_NOT_ALLOWED');
46 OE_MSG_PUB.Add;
47 l_error_code := 1;
48 return;
49 END IF;
50 END IF;
51 ELSE
52 x_return_status := FND_API.G_RET_STS_ERROR;
53 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
54 OE_MSG_PUB.Add;
55 l_error_code := 1;
56 return;
57 END IF;
58
59 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 1' || 'l_error_code = ' || l_error_code);
60
61 END IF;
62
63 IF (l_list_type_code = 'CHARGES')
64 THEN
65 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','CHARGE','USAGE'))
66 THEN
67 IF p_LIMITS_rec.limit_level_code = 'TRANSACTION'
68 THEN
69 IF (p_LIMITS_rec.organization_flag = 'N')
70 THEN
71 IF ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'CUSTOMER') AND
72 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'CUSTOMER'))
73 THEN
74 l_error_code := 0;
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76 return;
77 ELSE
78 x_return_status := FND_API.G_RET_STS_ERROR;
79 FND_MESSAGE.SET_NAME('QP','QP_CONT_CUST_NOT_ALLOWED');
80 OE_MSG_PUB.Add;
81 l_error_code := 1;
82 return;
83 END IF;
84 ELSE
85 x_return_status := FND_API.G_RET_STS_ERROR;
86 FND_MESSAGE.SET_NAME('QP','QP_ORG_NOT_ALLOWED');
87 OE_MSG_PUB.Add;
88 l_error_code := 1;
89 return;
90 END IF;
91 END IF;
92 ELSE
93 x_return_status := FND_API.G_RET_STS_ERROR;
94 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
95 OE_MSG_PUB.Add;
96 l_error_code := 1;
97 return;
98 END IF;
99
100 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 2' || 'l_error_code = ' || l_error_code);
101 END IF;
102
103 IF l_error_code = 0 -- Validation Passed
104 THEN
105 l_return_status := FND_API.G_RET_STS_SUCCESS;
106
107 END IF;
108
109 --Done validating List Header Limits
110
111 x_return_status := l_return_status;
112
113 EXCEPTION
114
115 WHEN OTHERS THEN
116
117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
118
119 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
120 THEN
121 OE_MSG_PUB.Add_Exc_Msg
122 ( G_PKG_NAME
123 , 'Validate_List_Header_Limits'
124 );
125 END IF;
126
127 END Validate_List_Header_Limits;
128
129 -- Procedure Entity
130
131 PROCEDURE Validate_List_Line_Limits
132 (x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
133 ,p_LIMITS_rec IN QP_Limits_PUB.Limits_Rec_Type
134 )
135 IS
136 l_list_line_type_code VARCHAR2(30) := null;
137 l_application_method VARCHAR2(30) := null;
138 l_modifier_level_code VARCHAR2(30) := null;
139 l_accrual_flag VARCHAR2(1) := null;
140 l_return_status VARCHAR2(1);
141 l_benefit_qty NUMBER := 0;
142 l_error_code NUMBER := 0;
143 l_qualification_ind NUMBER := 0;
144 l_monetary_accrual BOOLEAN := false;
145 l_non_monetary_accrual BOOLEAN := false;
146 l_accruals_also BOOLEAN := false;
147 l_applies_to_entire_brk BOOLEAN := false;
148 l_applies_to_entire_mod BOOLEAN := false;
149 BEGIN
150
151
152 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY Before Select Statement' || 'l_error_code = ' || l_error_code);
153 SELECT LIST_LINE_TYPE_CODE,
154 BENEFIT_QTY,
155 ACCRUAL_FLAG,
156 ARITHMETIC_OPERATOR,
157 MODIFIER_LEVEL_CODE,
158 QUALIFICATION_IND
159 INTO
160 l_list_line_type_code,
161 l_benefit_qty,
162 l_accrual_flag,
163 l_application_method,
164 l_modifier_level_code,
165 l_qualification_ind
166 FROM QP_LIST_LINES
167 WHERE LIST_LINE_ID = p_LIMITS_rec.list_line_id;
168
169 --DBMS_OUTPUT.PUT_LINE('LIST_LINE_TYPE_CODE ' || l_list_line_type_code || ' BENEFIT_QTY ' || l_benefit_qty || ' ACCRUAL_FLAG ' || l_accrual_flag || ' ARITHMETIC_OPERATOR ' || l_application_method || ' MODIFIER_LEVEL_CODE ' || l_modifier_level_code);
170
171 -- Check for Monetary/Non-Monetary Accrual
172
173 IF (l_accrual_flag = 'Y') and (nvl(l_benefit_qty,0) <> 0)
174 THEN
175 l_non_monetary_accrual := true;
176 ELSIF (l_accrual_flag = 'Y') and (nvl(l_benefit_qty,0) = 0)
177 THEN
178 l_monetary_accrual := true;
179 END IF;
180
181 -- Check for Accruals Also
182
183 IF (l_accrual_flag = 'Y')
184 THEN
185 l_accruals_also := true;
186 ELSIF (l_accrual_flag = 'N')
187 THEN
188 l_accruals_also := false;
189 END IF;
190
191 IF mod(l_qualification_ind,2) = 1
192 THEN
193 l_applies_to_entire_brk := false;
194 l_applies_to_entire_mod := false;
195 ELSIF mod(l_qualification_ind,2) = 0
196 THEN
197 l_applies_to_entire_brk := true;
198 l_applies_to_entire_mod := true;
199 END IF;
200
201 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY After Select Statement' || 'l_error_code = ' || l_error_code);
202
203
204 -- Validating Line Level Limits for Header Level Modifiers
205
206 IF (l_modifier_level_code = 'ORDER')
207 THEN
208 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 5' || 'l_error_code = ' || l_error_code);
209
210 IF (l_non_monetary_accrual = true) AND
211 (l_application_method = '%')
212 THEN
213 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','ACCRUAL','USAGE'))
214 THEN
215 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','ACCRUAL'))
216 THEN
217 IF ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'CUSTOMER')
218 AND
219 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'CUSTOMER')) AND
220 ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'ITEM')
221 AND
222 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'ITEM'))
223 THEN
224 l_error_code := 0;
225 x_return_status := FND_API.G_RET_STS_SUCCESS;
226 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 6' || 'l_error_code = ' || l_error_code);
227 return;
228 ELSE
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 FND_MESSAGE.SET_NAME('QP','QP_CONT_CUST_PROD_NOT_ALLOWED');
231 OE_MSG_PUB.Add;
232 l_error_code := 1;
233 return;
234 END IF;
235 END IF;
236 IF (p_LIMITS_rec.basis = 'USAGE')
237 THEN
238 IF (p_LIMITS_rec.limit_level_code = 'ACROSS_TRANSACTION')
239 THEN
240 IF ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'CUSTOMER')
241 AND
242 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'CUSTOMER')) AND
243 ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'ITEM')
244 AND
245 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'ITEM'))
246 THEN
247 l_error_code := 0;
248 x_return_status := FND_API.G_RET_STS_SUCCESS;
249 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 7' || 'l_error_code = ' || l_errorde);
250 return;
251 ELSE
252 x_return_status := FND_API.G_RET_STS_ERROR;
253 FND_MESSAGE.SET_NAME('QP','QP_CONT_CUST_PROD_NOT_ALLOWED');
254 OE_MSG_PUB.Add;
255 l_error_code := 1;
256 return;
257 END IF;
258 ELSE
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_LEVEL');
261 OE_MSG_PUB.Add;
262 l_error_code := 1;
263 return;
264 END IF;
265 END IF;
266 ELSE
267 x_return_status := FND_API.G_RET_STS_ERROR;
268 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
269 OE_MSG_PUB.Add;
270 l_error_code := 1;
271 return;
272 END IF;
273 END IF;
274
275 IF ((l_list_line_type_code IN ('DIS','SUR')) OR (l_monetary_accrual = true)) AND
276 (l_application_method = '%')
277 THEN
278 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','COST','USAGE'))
279 THEN
280 --DBMS_OUTPUT.PUT_LINE('VALIDATE ENTITY GR/COS/USA');
281 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','COST'))
282 THEN
283 --DBMS_OUTPUT.PUT_LINE('VALIDATE ENTITY GR/COS');
284 IF ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'CUSTOMER')
285 AND
286 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'CUSTOMER')) AND
287 ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'ITEM')
288 AND
289 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'ITEM'))
290 THEN
291 l_error_code := 0;
292 x_return_status := FND_API.G_RET_STS_SUCCESS;
293 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 3' || 'l_error_code = ' || l_error_code);
294 return;
295 ELSE
296 x_return_status := FND_API.G_RET_STS_ERROR;
297 FND_MESSAGE.SET_NAME('QP','QP_CONT_CUST_PROD_NOT_ALLOWED');
298 OE_MSG_PUB.Add;
299 l_error_code := 1;
300 return;
301 END IF;
302 ELSIF (p_LIMITS_rec.basis = 'USAGE')
303 THEN
304 --DBMS_OUTPUT.PUT_LINE('VALIDATE ENTITY USA');
305 IF (p_LIMITS_rec.limit_level_code = 'ACROSS_TRANSACTION')
306 THEN
307 IF ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'CUSTOMER')
308 AND
309 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'CUSTOMER')) AND
310 ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'ITEM')
311 AND
312 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'ITEM'))
313 THEN
314 l_error_code := 0;
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 4' || 'l_error_code = ' || l_error_code);
317 return;
318 ELSE
319 x_return_status := FND_API.G_RET_STS_ERROR;
320 FND_MESSAGE.SET_NAME('QP','QP_CONT_CUST_PROD_NOT_ALLOWED');
321 OE_MSG_PUB.Add;
322 l_error_code := 1;
323 return;
324 END IF;
325 ELSE
326 x_return_status := FND_API.G_RET_STS_ERROR;
327 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_LEVEL');
328 OE_MSG_PUB.Add;
329 l_error_code := 1;
330 return;
331 END IF;
332 END IF;
333 ELSE
334 x_return_status := FND_API.G_RET_STS_ERROR;
335 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
336 OE_MSG_PUB.Add;
337 l_error_code := 1;
338 return;
339 END IF;
340 END IF;
341
342 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 8' || 'l_error_code = ' || l_error_code);
343 IF (l_list_line_type_code = 'FREIGHT_CHARGE') AND
344 (l_application_method = 'LUMPSUM')
345 THEN
346 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','CHARGE','USAGE'))
347 THEN
348 IF (p_LIMITS_rec.limit_level_code = 'ACROSS_TRANSACTION')
349 THEN
350 IF ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'CUSTOMER')
351 AND
352 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'CUSTOMER')) AND
353 ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'ITEM')
354 AND
355 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'ITEM'))
356 THEN
357 l_error_code := 0;
358 x_return_status := FND_API.G_RET_STS_SUCCESS;
359 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 9' || 'l_error_code = ' || l_error_code);
360 return;
361 ELSE
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 FND_MESSAGE.SET_NAME('QP','QP_CONT_CUST_PROD_NOT_ALLOWED');
364 OE_MSG_PUB.Add;
365 l_error_code := 1;
366 return;
367 END IF;
368 ELSE
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_LEVEL');
371 OE_MSG_PUB.Add;
372 l_error_code := 1;
373 return;
374 END IF;
375 ELSE
376 x_return_status := FND_API.G_RET_STS_ERROR;
377 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
378 OE_MSG_PUB.Add;
379 l_error_code := 1;
380 return;
381 END IF;
382 END IF;
383
384 IF (l_list_line_type_code = 'PRG')
385 THEN
386 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','COST','USAGE'))
387 THEN
388 IF (p_LIMITS_rec.limit_level_code = 'ACROSS_TRANSACTION')
389 THEN
390 IF ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'CUSTOMER')
391 AND
392 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'CUSTOMER')) AND
393 ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'ITEM')
394 AND
395 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'ITEM'))
396 THEN
397 l_error_code := 0;
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 10' || 'l_error_code = ' || l_error_code);
400 return;
401 ELSE
402 x_return_status := FND_API.G_RET_STS_ERROR;
403 FND_MESSAGE.SET_NAME('QP','QP_CONT_CUST_PROD_NOT_ALLOWED');
404 OE_MSG_PUB.Add;
405 l_error_code := 1;
406 return;
407 END IF;
408 ELSE
409 x_return_status := FND_API.G_RET_STS_ERROR;
410 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_LEVEL');
411 OE_MSG_PUB.Add;
412 l_error_code := 1;
413 return;
414 END IF;
415 ELSE
416 x_return_status := FND_API.G_RET_STS_ERROR;
417 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
418 OE_MSG_PUB.Add;
419 l_error_code := 1;
420 return;
421 END IF;
422 END IF;
423
424 IF (l_list_line_type_code = 'CIE')
425 THEN
426 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','USAGE'))
427 THEN
428 IF (p_LIMITS_rec.limit_level_code = 'ACROSS_TRANSACTION')
429 THEN
430 IF ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'CUSTOMER')
431 AND
432 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'CUSTOMER')) AND
433 ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'ITEM')
434 AND
435 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'ITEM'))
436 THEN
437 l_error_code := 0;
438 x_return_status := FND_API.G_RET_STS_SUCCESS;
439 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 11' || 'l_error_code = ' || l_error_code);
440 return;
441 ELSE
442 x_return_status := FND_API.G_RET_STS_ERROR;
443 FND_MESSAGE.SET_NAME('QP','QP_CONT_CUST_PROD_NOT_ALLOWED');
444 OE_MSG_PUB.Add;
445 l_error_code := 1;
446 return;
447 END IF;
448 ELSE
449 x_return_status := FND_API.G_RET_STS_ERROR;
450 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_LEVEL');
451 OE_MSG_PUB.Add;
452 l_error_code := 1;
453 return;
454 END IF;
455 ELSE
456 x_return_status := FND_API.G_RET_STS_ERROR;
457 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
458 OE_MSG_PUB.Add;
459 l_error_code := 1;
460 return;
461 END IF;
462 END IF;
463
464 IF (l_list_line_type_code = 'TSN')
465 THEN
466 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','COST','USAGE'))
467 THEN
468 IF (p_LIMITS_rec.limit_level_code = 'ACROSS_TRANSACTION')
469 THEN
470 IF ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'CUSTOMER')
471 AND
472 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'CUSTOMER')) AND
473 ((nvl(p_LIMITS_rec.multival_attr1_context,'NA') <> 'ITEM')
474 AND
475 (nvl(p_LIMITS_rec.multival_attr2_context,'NA') <> 'ITEM'))
476 THEN
477 l_error_code := 0;
478 x_return_status := FND_API.G_RET_STS_SUCCESS;
479 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 12' || 'l_error_code = ' || l_error_code);
480 return;
481 ELSE
482 x_return_status := FND_API.G_RET_STS_ERROR;
483 FND_MESSAGE.SET_NAME('QP','QP_CONT_CUST_PROD_NOT_ALLOWED');
484 OE_MSG_PUB.Add;
485 l_error_code := 1;
486 return;
487 END IF;
488 ELSE
489 x_return_status := FND_API.G_RET_STS_ERROR;
490 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_LEVEL');
491 OE_MSG_PUB.Add;
492 l_error_code := 1;
493 return;
494 END IF;
495 ELSE
496 x_return_status := FND_API.G_RET_STS_ERROR;
497 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
498 OE_MSG_PUB.Add;
499 l_error_code := 1;
500 return;
501 END IF;
502 END IF;
503 END IF;
504
505 -- Validating Line Level Limits for Line Level Modifiers
506
507 -- Non-Monetary Accruals
508
509 IF (l_non_monetary_accrual = true) THEN
510 IF (l_application_method IN ('AMT','LUMPSUM')) THEN
511 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','ACCRUAL','USAGE','QUANTITY')) THEN
512 l_error_code := 0;
513 x_return_status := FND_API.G_RET_STS_SUCCESS;
514 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 17' || 'l_error_code = ' || l_error_code);
515 return;
516 ELSE
517 x_return_status := FND_API.G_RET_STS_ERROR;
518 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
519 OE_MSG_PUB.Add;
520 l_error_code := 1;
521 return;
522 END IF;
523 ELSE
524 x_return_status := FND_API.G_RET_STS_ERROR;
525 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
526 OE_MSG_PUB.Add;
527 l_error_code := 1;
528 return;
529 END IF;
530 END IF;
531
532 -- Discount/Surcharge
533
534 IF (l_modifier_level_code = 'LINE') OR (l_modifier_level_code = 'LINEGROUP')
535 THEN
536 IF (l_list_line_type_code IN ('DIS','SUR')) AND
537 (l_application_method IN ('%','AMT','LUMPSUM')) AND
538 (l_accruals_also = true)
539 THEN
540 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','COST','USAGE','QUANTITY'))
541 THEN
542 l_error_code := 0;
543 x_return_status := FND_API.G_RET_STS_SUCCESS;
544 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 13' || 'l_error_code = ' || l_error_code);
545 return;
546 ELSE
547 x_return_status := FND_API.G_RET_STS_ERROR;
548 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
549 OE_MSG_PUB.Add;
550 l_error_code := 1;
551 return;
552 END IF;
553 END IF;
554
555 IF (l_list_line_type_code IN ('DIS','SUR')) AND
556 (l_application_method = 'NEWPRICE') AND
557 (l_accruals_also = false)
558 THEN
559 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','COST','USAGE','QUANTITY'))
560 THEN
561 l_error_code := 0;
562 x_return_status := FND_API.G_RET_STS_SUCCESS;
563 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 14' || 'l_error_code = ' || l_error_code);
564 return;
565 ELSE
566 x_return_status := FND_API.G_RET_STS_ERROR;
567 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
568 OE_MSG_PUB.Add;
569 l_error_code := 1;
570 return;
571 END IF;
572 END IF;
573
574
575 -- Monetary Accruals
576
577 IF (l_monetary_accrual = true) AND
578 (l_application_method IN ('%','AMT','LUMPSUM')) AND
579 (l_accruals_also = true)
580 THEN
581 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','COST','USAGE','QUANTITY'))
582 THEN
583 l_error_code := 0;
584 x_return_status := FND_API.G_RET_STS_SUCCESS;
585 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 15' || 'l_error_code = ' || l_error_code);
586 return;
587 ELSE
588 x_return_status := FND_API.G_RET_STS_ERROR;
589 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
590 OE_MSG_PUB.Add;
591 l_error_code := 1;
592 return;
593 END IF;
594 END IF;
595
596 IF (l_monetary_accrual = true) AND
597 (l_application_method = 'NEWPRICE') AND
598 (l_accruals_also = false)
599 THEN
600 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','COST','USAGE','QUANTITY'))
601 THEN
602 l_error_code := 0;
603 x_return_status := FND_API.G_RET_STS_SUCCESS;
604 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 16' || 'l_error_code = ' || l_error_code);
605 return;
606 ELSE
607 x_return_status := FND_API.G_RET_STS_ERROR;
608 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
609 OE_MSG_PUB.Add;
610 l_error_code := 1;
611 return;
612 END IF;
613 END IF;
614
615
616 -- Freight and Special Charges
617
618 IF (l_list_line_type_code = 'FREIGHT_CHARGE') AND
619 (l_application_method IN ('%','AMT','LUMPSUM'))
620 THEN
621 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','CHARGE','USAGE','QUANTITY'))
622 THEN
623 l_error_code := 0;
624 x_return_status := FND_API.G_RET_STS_SUCCESS;
625 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 18' || 'l_error_code = ' || l_error_code);
626 return;
627 ELSE
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
630 OE_MSG_PUB.Add;
631 l_error_code := 1;
632 return;
633 END IF;
634 END IF;
635
636 -- Other Item Discounts
637
638 IF (l_list_line_type_code = 'OID') and (l_applies_to_entire_mod = true)
639 THEN
640 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','COST','USAGE'))
641 THEN
642 l_error_code := 0;
643 x_return_status := FND_API.G_RET_STS_SUCCESS;
644 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 19' || 'l_error_code = ' || l_error_code);
645 return;
646 ELSE
647 x_return_status := FND_API.G_RET_STS_ERROR;
648 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
649 OE_MSG_PUB.Add;
650 l_error_code := 1;
651 return;
652 END IF;
653 END IF;
654
655 -- Discount Breaks
656
657 IF (l_list_line_type_code = 'PBH') and (l_applies_to_entire_brk = true)
658 THEN
659 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','COST','USAGE','ACCRUAL','QUANTITY'))
660 THEN
661 l_error_code := 0;
662 x_return_status := FND_API.G_RET_STS_SUCCESS;
663 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 20' || 'l_error_code = ' || l_error_code);
664 return;
665 ELSE
666 x_return_status := FND_API.G_RET_STS_ERROR;
667 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
668 OE_MSG_PUB.Add;
669 l_error_code := 1;
670 return;
671 END IF;
672 END IF;
673
674 -- Promotional Goods
675
676 IF (l_list_line_type_code = 'PRG') and (l_applies_to_entire_mod = true)
677 THEN
678 IF (p_LIMITS_rec.basis IN ('GROSS_REVENUE','COST','USAGE'))
679 THEN
680 l_error_code := 0;
681 x_return_status := FND_API.G_RET_STS_SUCCESS;
682 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY 21' || 'l_error_code = ' || l_error_code);
683 return;
684 ELSE
685 x_return_status := FND_API.G_RET_STS_ERROR;
686 FND_MESSAGE.SET_NAME('QP','QP_INVALID_LIMIT_BASIS');
687 OE_MSG_PUB.Add;
688 l_error_code := 1;
689 return;
690 END IF;
691 END IF;
692
693 END IF;
694
695 --DBMS_OUTPUT.PUT_LINE('INSIDE VALIDATE ENTITY ' || 'l_error_code = ' || l_error_code);
696
697 IF (l_error_code = 0) -- Validation Passed
698 THEN
699 l_return_status := FND_API.G_RET_STS_SUCCESS;
700 END IF;
701
702 --Done validating List Line Limits
703
704 x_return_status := l_return_status;
705
706 EXCEPTION
707
708 WHEN OTHERS THEN
709
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711
712 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
713 THEN
714 OE_MSG_PUB.Add_Exc_Msg
715 ( G_PKG_NAME
716 , 'Validate_List_Line_Limits'
717 );
718 END IF;
719
720
721 END Validate_List_Line_Limits;
722
723
724 PROCEDURE Entity
725 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
726 , p_LIMITS_rec IN QP_Limits_PUB.Limits_Rec_Type
727 , p_old_LIMITS_rec IN QP_Limits_PUB.Limits_Rec_Type :=
728 QP_Limits_PUB.G_MISS_LIMITS_REC
729 )
730 IS
731 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
732 l_LIMITS_rec QP_Limits_PUB.Limits_Rec_Type;
733 BEGIN
734 l_LIMITS_rec := p_LIMITS_rec;
735 -- Check required attributes.
736
737 IF p_LIMITS_rec.limit_id IS NULL
738 THEN
739
740 l_return_status := FND_API.G_RET_STS_ERROR;
741
742 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
743 THEN
744
745 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
746 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','amount');
747 OE_MSG_PUB.Add;
748
749 END IF;
750
751 END IF;
752
753 --
754 -- Check rest of required attributes here.
755 --
756
757
758 -- Return Error if a required attribute is missing.
759
760 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
761
762 RAISE FND_API.G_EXC_ERROR;
763
764 END IF;
765
766 --
767 -- Check conditionally required attributes here.
768 --
769
770
771 --
772 -- Validate attribute dependencies here.
773 --
774 --DBMS_OUTPUT.PUT_LINE('Here ');
775 IF (l_LIMITS_rec.list_line_id = -1) or (l_LIMITS_rec.list_line_id is null) THEN
776 Validate_List_Header_Limits(x_return_status => l_return_status
777 ,p_LIMITS_rec => l_LIMITS_rec
778 );
779 --DBMS_OUTPUT.PUT_LINE('Here1 ');
780 ELSE
781 Validate_List_Line_Limits(x_return_status => l_return_status
782 ,p_LIMITS_rec => l_LIMITS_rec
783 );
784 --DBMS_OUTPUT.PUT_LINE('Here2 ' || ' Return Status ' || l_return_status);
785 END IF;
786
787 -- Done validating entity
788
789 x_return_status := l_return_status;
790
791 EXCEPTION
792
793 WHEN FND_API.G_EXC_ERROR THEN
794
795 x_return_status := FND_API.G_RET_STS_ERROR;
796
797 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
798
799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
800
801 WHEN OTHERS THEN
802
803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
804
805 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
806 THEN
807 OE_MSG_PUB.Add_Exc_Msg
808 ( G_PKG_NAME
809 , 'Entity'
810 );
811 END IF;
812
813 END Entity;
814
815 -- Procedure Attributes
816
817 PROCEDURE Attributes
818 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
819 , p_LIMITS_rec IN QP_Limits_PUB.Limits_Rec_Type
820 , p_old_LIMITS_rec IN QP_Limits_PUB.Limits_Rec_Type :=
821 QP_Limits_PUB.G_MISS_LIMITS_REC
822 )
823 IS
824 BEGIN
825
826 x_return_status := FND_API.G_RET_STS_SUCCESS;
827
828 --dbms_output.put_line('Inside QP_Validate_Limits.Attributes ' || x_return_status);
829 -- Validate LIMITS attributes
830 IF p_LIMITS_rec.amount IS NOT NULL AND
831 ( p_LIMITS_rec.amount <>
832 p_old_LIMITS_rec.amount OR
833 p_old_LIMITS_rec.amount IS NULL )
834 THEN
835 IF NOT QP_Validate.Amount(p_LIMITS_rec.amount) THEN
836 x_return_status := FND_API.G_RET_STS_ERROR;
837 END IF;
838 --dbms_output.put_line('Inside QP_Validate.Amount ' || x_return_status);
839 END IF;
840
841 IF p_LIMITS_rec.basis IS NOT NULL AND
842 ( p_LIMITS_rec.basis <>
843 p_old_LIMITS_rec.basis OR
844 p_old_LIMITS_rec.basis IS NULL )
845 THEN
846 IF NOT QP_Validate.Basis(p_LIMITS_rec.basis) THEN
847 x_return_status := FND_API.G_RET_STS_ERROR;
848 END IF;
849 --dbms_output.put_line('Inside QP_Validate.Basis ' || x_return_status);
850 END IF;
851
852 IF p_LIMITS_rec.created_by IS NOT NULL AND
853 ( p_LIMITS_rec.created_by <>
854 p_old_LIMITS_rec.created_by OR
855 p_old_LIMITS_rec.created_by IS NULL )
856 THEN
857 IF NOT QP_Validate.Created_By(p_LIMITS_rec.created_by) THEN
858 x_return_status := FND_API.G_RET_STS_ERROR;
859 END IF;
860 --dbms_output.put_line('Inside QP_Validate.created_by ' || x_return_status);
861 END IF;
862
863 IF p_LIMITS_rec.creation_date IS NOT NULL AND
864 ( p_LIMITS_rec.creation_date <>
865 p_old_LIMITS_rec.creation_date OR
866 p_old_LIMITS_rec.creation_date IS NULL )
867 THEN
868 IF NOT QP_Validate.Creation_Date(p_LIMITS_rec.creation_date) THEN
869 x_return_status := FND_API.G_RET_STS_ERROR;
870 END IF;
871 --dbms_output.put_line('Inside QP_Validate.Creation_Date ' || x_return_status);
872 END IF;
873
874 IF p_LIMITS_rec.last_updated_by IS NOT NULL AND
875 ( p_LIMITS_rec.last_updated_by <>
876 p_old_LIMITS_rec.last_updated_by OR
877 p_old_LIMITS_rec.last_updated_by IS NULL )
878 THEN
879 IF NOT QP_Validate.Last_Updated_By(p_LIMITS_rec.last_updated_by) THEN
880 x_return_status := FND_API.G_RET_STS_ERROR;
881 END IF;
882 --dbms_output.put_line('Inside QP_Validate.Last_Updated_By ' || x_return_status);
883 END IF;
884
885 IF p_LIMITS_rec.last_update_date IS NOT NULL AND
886 ( p_LIMITS_rec.last_update_date <>
887 p_old_LIMITS_rec.last_update_date OR
888 p_old_LIMITS_rec.last_update_date IS NULL )
889 THEN
890 IF NOT QP_Validate.Last_Update_Date(p_LIMITS_rec.last_update_date) THEN
891 x_return_status := FND_API.G_RET_STS_ERROR;
892 END IF;
893 --dbms_output.put_line('Inside QP_Validate.Last_Update_Date ' || x_return_status);
894 END IF;
895
896 IF p_LIMITS_rec.last_update_login IS NOT NULL AND
897 ( p_LIMITS_rec.last_update_login <>
898 p_old_LIMITS_rec.last_update_login OR
899 p_old_LIMITS_rec.last_update_login IS NULL )
900 THEN
901 IF NOT QP_Validate.Last_Update_Login(p_LIMITS_rec.last_update_login) THEN
902 x_return_status := FND_API.G_RET_STS_ERROR;
903 END IF;
904 --dbms_output.put_line('Inside QP_Validate.last_update_login ' || x_return_status);
905 END IF;
906
907 IF p_LIMITS_rec.limit_exceed_action_code IS NOT NULL AND
908 ( p_LIMITS_rec.limit_exceed_action_code <>
909 p_old_LIMITS_rec.limit_exceed_action_code OR
910 p_old_LIMITS_rec.limit_exceed_action_code IS NULL )
911 THEN
912 IF NOT QP_Validate.Limit_Exceed_Action(p_LIMITS_rec.limit_exceed_action_code) THEN
913 x_return_status := FND_API.G_RET_STS_ERROR;
914 END IF;
915 --dbms_output.put_line('Inside QP_Validate.Limit_Exceed_Action ' || x_return_status);
916 END IF;
917
918 IF p_LIMITS_rec.limit_hold_flag IS NOT NULL AND
919 ( p_LIMITS_rec.limit_hold_flag <>
920 p_old_LIMITS_rec.limit_hold_flag OR
921 p_old_LIMITS_rec.limit_hold_flag IS NULL )
922 THEN
923 IF NOT QP_Validate.LIMIT_HOLD(p_LIMITS_rec.limit_hold_flag) THEN
924 x_return_status := FND_API.G_RET_STS_ERROR;
925 END IF;
926 --dbms_output.put_line('Inside QP_Validate.LIMIT_HOLD ' || x_return_status);
927 END IF;
928
929 IF p_LIMITS_rec.limit_id IS NOT NULL AND
930 ( p_LIMITS_rec.limit_id <>
931 p_old_LIMITS_rec.limit_id OR
932 p_old_LIMITS_rec.limit_id IS NULL )
933 THEN
934 IF NOT QP_Validate.Limit(p_LIMITS_rec.limit_id) THEN
935 x_return_status := FND_API.G_RET_STS_ERROR;
936 END IF;
937 --dbms_output.put_line('Inside QP_Validate.limit_id ' || x_return_status);
938 END IF;
939
940 IF p_LIMITS_rec.limit_level_code IS NOT NULL AND
941 ( p_LIMITS_rec.limit_level_code <>
942 p_old_LIMITS_rec.limit_level_code OR
943 p_old_LIMITS_rec.limit_level_code IS NULL )
944 THEN
945 IF NOT QP_Validate.Limit_Level(p_LIMITS_rec.limit_level_code) THEN
946 x_return_status := FND_API.G_RET_STS_ERROR;
947 END IF;
948 --dbms_output.put_line('Inside QP_Validate.Limit_Level ' || x_return_status);
949 END IF;
950
951 IF p_LIMITS_rec.limit_number IS NOT NULL AND
952 ( p_LIMITS_rec.limit_number <>
953 p_old_LIMITS_rec.limit_number OR
954 p_old_LIMITS_rec.limit_number IS NULL )
955 THEN
956 IF NOT QP_Validate.Limit_Number(p_LIMITS_rec.limit_number) THEN
957 x_return_status := FND_API.G_RET_STS_ERROR;
958 END IF;
959 --dbms_output.put_line('Inside QP_Validate.Limit_Number ' || x_return_status);
960 END IF;
961
962 IF p_LIMITS_rec.list_header_id IS NOT NULL AND
963 ( p_LIMITS_rec.list_header_id <>
964 p_old_LIMITS_rec.list_header_id OR
965 p_old_LIMITS_rec.list_header_id IS NULL )
966 THEN
967 IF NOT QP_Validate.List_Header(p_LIMITS_rec.list_header_id) THEN
968 x_return_status := FND_API.G_RET_STS_ERROR;
969 END IF;
970 --dbms_output.put_line('Inside QP_Validate.List_Header ' || x_return_status);
971 END IF;
972
973 IF p_LIMITS_rec.list_line_id IS NOT NULL AND
974 ( p_LIMITS_rec.list_line_id <>
975 p_old_LIMITS_rec.list_line_id OR
976 p_old_LIMITS_rec.list_line_id IS NULL )
977 THEN
978 IF NOT QP_Validate.List_Line(p_LIMITS_rec.list_line_id) THEN
979 x_return_status := FND_API.G_RET_STS_ERROR;
980 END IF;
981 --dbms_output.put_line('Inside QP_Validate.List_Line ' || x_return_status);
982 END IF;
983
984 IF p_LIMITS_rec.multival_attr1_type IS NOT NULL AND
985 ( p_LIMITS_rec.multival_attr1_type <>
986 p_old_LIMITS_rec.multival_attr1_type OR
987 p_old_LIMITS_rec.multival_attr1_type IS NULL )
988 THEN
989 IF NOT QP_Validate.Multival_Attr1_Type(p_LIMITS_rec.multival_attr1_type) THEN
990 x_return_status := FND_API.G_RET_STS_ERROR;
991 END IF;
992 END IF;
993
994 IF p_LIMITS_rec.multival_attr1_context IS NOT NULL AND
995 ( p_LIMITS_rec.multival_attr1_context <>
996 p_old_LIMITS_rec.multival_attr1_context OR
997 p_old_LIMITS_rec.multival_attr1_context IS NULL )
998 THEN
999 IF NOT QP_Validate.Multival_Attr1_Context(p_LIMITS_rec.multival_attr1_context) THEN
1000 x_return_status := FND_API.G_RET_STS_ERROR;
1001 END IF;
1002 END IF;
1003
1004 IF p_LIMITS_rec.multival_attribute2 IS NOT NULL AND
1005 ( p_LIMITS_rec.multival_attribute2 <>
1006 p_old_LIMITS_rec.multival_attribute2 OR
1007 p_old_LIMITS_rec.multival_attribute2 IS NULL )
1008 THEN
1009 IF NOT QP_Validate.Multival_Attribute2(p_LIMITS_rec.multival_attribute2) THEN
1010 x_return_status := FND_API.G_RET_STS_ERROR;
1011 END IF;
1012 END IF;
1013
1014 IF p_LIMITS_rec.multival_attr1_datatype IS NOT NULL AND
1015 ( p_LIMITS_rec.multival_attr1_datatype <>
1016 p_old_LIMITS_rec.multival_attr1_datatype OR
1017 p_old_LIMITS_rec.multival_attr1_datatype IS NULL )
1018 THEN
1019 IF NOT QP_Validate.Multival_Attr1_Datatype(p_LIMITS_rec.multival_attr1_datatype) THEN
1020 x_return_status := FND_API.G_RET_STS_ERROR;
1021 END IF;
1022 END IF;
1023
1024 IF p_LIMITS_rec.multival_attr2_type IS NOT NULL AND
1025 ( p_LIMITS_rec.multival_attr2_type <>
1026 p_old_LIMITS_rec.multival_attr2_type OR
1027 p_old_LIMITS_rec.multival_attr2_type IS NULL )
1028 THEN
1029 IF NOT QP_Validate.Multival_Attr2_Type(p_LIMITS_rec.multival_attr2_type) THEN
1030 x_return_status := FND_API.G_RET_STS_ERROR;
1031 END IF;
1032 END IF;
1033
1034 IF p_LIMITS_rec.multival_attr2_context IS NOT NULL AND
1035 ( p_LIMITS_rec.multival_attr2_context <>
1036 p_old_LIMITS_rec.multival_attr2_context OR
1037 p_old_LIMITS_rec.multival_attr2_context IS NULL )
1038 THEN
1039 IF NOT QP_Validate.Multival_Attr2_Context(p_LIMITS_rec.multival_attr2_context) THEN
1040 x_return_status := FND_API.G_RET_STS_ERROR;
1041 END IF;
1042 END IF;
1043
1044 IF p_LIMITS_rec.multival_attribute2 IS NOT NULL AND
1045 ( p_LIMITS_rec.multival_attribute2 <>
1046 p_old_LIMITS_rec.multival_attribute2 OR
1047 p_old_LIMITS_rec.multival_attribute2 IS NULL )
1048 THEN
1049 IF NOT QP_Validate.Multival_Attribute2(p_LIMITS_rec.multival_attribute2) THEN
1050 x_return_status := FND_API.G_RET_STS_ERROR;
1051 END IF;
1052 END IF;
1053
1054 IF p_LIMITS_rec.multival_attr2_datatype IS NOT NULL AND
1055 ( p_LIMITS_rec.multival_attr2_datatype <>
1056 p_old_LIMITS_rec.multival_attr2_datatype OR
1057 p_old_LIMITS_rec.multival_attr2_datatype IS NULL )
1058 THEN
1059 IF NOT QP_Validate.Multival_Attr2_Datatype(p_LIMITS_rec.multival_attr2_datatype) THEN
1060 x_return_status := FND_API.G_RET_STS_ERROR;
1061 END IF;
1062 END IF;
1063
1064 IF p_LIMITS_rec.organization_flag IS NOT NULL AND
1065 ( p_LIMITS_rec.organization_flag <>
1066 p_old_LIMITS_rec.organization_flag OR
1067 p_old_LIMITS_rec.organization_flag IS NULL )
1068 THEN
1069 IF NOT QP_Validate.Organization(p_LIMITS_rec.organization_flag) THEN
1070 x_return_status := FND_API.G_RET_STS_ERROR;
1071 END IF;
1072 --dbms_output.put_line('Inside QP_Validate.Organization ' || x_return_status);
1073 END IF;
1074
1075
1076 IF p_LIMITS_rec.program_application_id IS NOT NULL AND
1077 ( p_LIMITS_rec.program_application_id <>
1078 p_old_LIMITS_rec.program_application_id OR
1079 p_old_LIMITS_rec.program_application_id IS NULL )
1080 THEN
1081 IF NOT QP_Validate.Program_Application(p_LIMITS_rec.program_application_id) THEN
1082 x_return_status := FND_API.G_RET_STS_ERROR;
1083 END IF;
1084 END IF;
1085
1086 IF p_LIMITS_rec.program_id IS NOT NULL AND
1087 ( p_LIMITS_rec.program_id <>
1088 p_old_LIMITS_rec.program_id OR
1089 p_old_LIMITS_rec.program_id IS NULL )
1090 THEN
1091 IF NOT QP_Validate.Program(p_LIMITS_rec.program_id) THEN
1092 x_return_status := FND_API.G_RET_STS_ERROR;
1093 END IF;
1094 END IF;
1095
1096 IF p_LIMITS_rec.program_update_date IS NOT NULL AND
1097 ( p_LIMITS_rec.program_update_date <>
1098 p_old_LIMITS_rec.program_update_date OR
1099 p_old_LIMITS_rec.program_update_date IS NULL )
1100 THEN
1101 IF NOT QP_Validate.Program_Update_Date(p_LIMITS_rec.program_update_date) THEN
1102 x_return_status := FND_API.G_RET_STS_ERROR;
1103 END IF;
1104 END IF;
1105
1106 IF p_LIMITS_rec.request_id IS NOT NULL AND
1107 ( p_LIMITS_rec.request_id <>
1108 p_old_LIMITS_rec.request_id OR
1109 p_old_LIMITS_rec.request_id IS NULL )
1110 THEN
1111 IF NOT QP_Validate.Request(p_LIMITS_rec.request_id) THEN
1112 x_return_status := FND_API.G_RET_STS_ERROR;
1113 --dbms_output.put_line('Inside QP_Validate.request_id ' || x_return_status);
1114 END IF;
1115 END IF;
1116
1117 IF (p_LIMITS_rec.attribute1 IS NOT NULL AND
1118 ( p_LIMITS_rec.attribute1 <>
1119 p_old_LIMITS_rec.attribute1 OR
1120 p_old_LIMITS_rec.attribute1 IS NULL ))
1121 OR (p_LIMITS_rec.attribute10 IS NOT NULL AND
1122 ( p_LIMITS_rec.attribute10 <>
1123 p_old_LIMITS_rec.attribute10 OR
1124 p_old_LIMITS_rec.attribute10 IS NULL ))
1125 OR (p_LIMITS_rec.attribute11 IS NOT NULL AND
1126 ( p_LIMITS_rec.attribute11 <>
1127 p_old_LIMITS_rec.attribute11 OR
1128 p_old_LIMITS_rec.attribute11 IS NULL ))
1129 OR (p_LIMITS_rec.attribute12 IS NOT NULL AND
1130 ( p_LIMITS_rec.attribute12 <>
1131 p_old_LIMITS_rec.attribute12 OR
1132 p_old_LIMITS_rec.attribute12 IS NULL ))
1133 OR (p_LIMITS_rec.attribute13 IS NOT NULL AND
1134 ( p_LIMITS_rec.attribute13 <>
1135 p_old_LIMITS_rec.attribute13 OR
1136 p_old_LIMITS_rec.attribute13 IS NULL ))
1137 OR (p_LIMITS_rec.attribute14 IS NOT NULL AND
1138 ( p_LIMITS_rec.attribute14 <>
1139 p_old_LIMITS_rec.attribute14 OR
1140 p_old_LIMITS_rec.attribute14 IS NULL ))
1141 OR (p_LIMITS_rec.attribute15 IS NOT NULL AND
1142 ( p_LIMITS_rec.attribute15 <>
1143 p_old_LIMITS_rec.attribute15 OR
1144 p_old_LIMITS_rec.attribute15 IS NULL ))
1145 OR (p_LIMITS_rec.attribute2 IS NOT NULL AND
1146 ( p_LIMITS_rec.attribute2 <>
1147 p_old_LIMITS_rec.attribute2 OR
1148 p_old_LIMITS_rec.attribute2 IS NULL ))
1149 OR (p_LIMITS_rec.attribute3 IS NOT NULL AND
1150 ( p_LIMITS_rec.attribute3 <>
1151 p_old_LIMITS_rec.attribute3 OR
1152 p_old_LIMITS_rec.attribute3 IS NULL ))
1153 OR (p_LIMITS_rec.attribute4 IS NOT NULL AND
1154 ( p_LIMITS_rec.attribute4 <>
1155 p_old_LIMITS_rec.attribute4 OR
1156 p_old_LIMITS_rec.attribute4 IS NULL ))
1157 OR (p_LIMITS_rec.attribute5 IS NOT NULL AND
1158 ( p_LIMITS_rec.attribute5 <>
1159 p_old_LIMITS_rec.attribute5 OR
1160 p_old_LIMITS_rec.attribute5 IS NULL ))
1161 OR (p_LIMITS_rec.attribute6 IS NOT NULL AND
1162 ( p_LIMITS_rec.attribute6 <>
1163 p_old_LIMITS_rec.attribute6 OR
1164 p_old_LIMITS_rec.attribute6 IS NULL ))
1165 OR (p_LIMITS_rec.attribute7 IS NOT NULL AND
1166 ( p_LIMITS_rec.attribute7 <>
1167 p_old_LIMITS_rec.attribute7 OR
1168 p_old_LIMITS_rec.attribute7 IS NULL ))
1169 OR (p_LIMITS_rec.attribute8 IS NOT NULL AND
1170 ( p_LIMITS_rec.attribute8 <>
1171 p_old_LIMITS_rec.attribute8 OR
1172 p_old_LIMITS_rec.attribute8 IS NULL ))
1173 OR (p_LIMITS_rec.attribute9 IS NOT NULL AND
1174 ( p_LIMITS_rec.attribute9 <>
1175 p_old_LIMITS_rec.attribute9 OR
1176 p_old_LIMITS_rec.attribute9 IS NULL ))
1177 OR (p_LIMITS_rec.context IS NOT NULL AND
1178 ( p_LIMITS_rec.context <>
1179 p_old_LIMITS_rec.context OR
1180 p_old_LIMITS_rec.context IS NULL ))
1181 THEN
1182
1183 -- These calls are temporarily commented out
1184
1185 /*
1186 FND_FLEX_DESC_VAL.Set_Column_Value
1187 ( column_name => 'ATTRIBUTE1'
1188 , column_value => p_LIMITS_rec.attribute1
1189 );
1190 FND_FLEX_DESC_VAL.Set_Column_Value
1191 ( column_name => 'ATTRIBUTE10'
1192 , column_value => p_LIMITS_rec.attribute10
1193 );
1194 FND_FLEX_DESC_VAL.Set_Column_Value
1195 ( column_name => 'ATTRIBUTE11'
1196 , column_value => p_LIMITS_rec.attribute11
1197 );
1198 FND_FLEX_DESC_VAL.Set_Column_Value
1199 ( column_name => 'ATTRIBUTE12'
1200 , column_value => p_LIMITS_rec.attribute12
1201 );
1202 FND_FLEX_DESC_VAL.Set_Column_Value
1203 ( column_name => 'ATTRIBUTE13'
1204 , column_value => p_LIMITS_rec.attribute13
1205 );
1206 FND_FLEX_DESC_VAL.Set_Column_Value
1207 ( column_name => 'ATTRIBUTE14'
1208 , column_value => p_LIMITS_rec.attribute14
1209 );
1210 FND_FLEX_DESC_VAL.Set_Column_Value
1211 ( column_name => 'ATTRIBUTE15'
1212 , column_value => p_LIMITS_rec.attribute15
1213 );
1214 FND_FLEX_DESC_VAL.Set_Column_Value
1215 ( column_name => 'ATTRIBUTE2'
1216 , column_value => p_LIMITS_rec.attribute2
1217 );
1218 FND_FLEX_DESC_VAL.Set_Column_Value
1219 ( column_name => 'ATTRIBUTE3'
1220 , column_value => p_LIMITS_rec.attribute3
1221 );
1222 FND_FLEX_DESC_VAL.Set_Column_Value
1223 ( column_name => 'ATTRIBUTE4'
1224 , column_value => p_LIMITS_rec.attribute4
1225 );
1226 FND_FLEX_DESC_VAL.Set_Column_Value
1227 ( column_name => 'ATTRIBUTE5'
1228 , column_value => p_LIMITS_rec.attribute5
1229 );
1230 FND_FLEX_DESC_VAL.Set_Column_Value
1231 ( column_name => 'ATTRIBUTE6'
1232 , column_value => p_LIMITS_rec.attribute6
1233 );
1234 FND_FLEX_DESC_VAL.Set_Column_Value
1235 ( column_name => 'ATTRIBUTE7'
1236 , column_value => p_LIMITS_rec.attribute7
1237 );
1238 FND_FLEX_DESC_VAL.Set_Column_Value
1239 ( column_name => 'ATTRIBUTE8'
1240 , column_value => p_LIMITS_rec.attribute8
1241 );
1242 FND_FLEX_DESC_VAL.Set_Column_Value
1243 ( column_name => 'ATTRIBUTE9'
1244 , column_value => p_LIMITS_rec.attribute9
1245 );
1246 FND_FLEX_DESC_VAL.Set_Column_Value
1247 ( column_name => 'CONTEXT'
1248 , column_value => p_LIMITS_rec.context
1249 );
1250 */
1251
1252 -- Validate descriptive flexfield.
1253
1254 IF NOT QP_Validate.Desc_Flex( 'LIMITS' ) THEN
1255 x_return_status := FND_API.G_RET_STS_ERROR;
1256 END IF;
1257
1258 END IF;
1259
1260 -- Done validating attributes
1261
1262 EXCEPTION
1263
1264 WHEN FND_API.G_EXC_ERROR THEN
1265
1266 x_return_status := FND_API.G_RET_STS_ERROR;
1267
1268 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1269
1270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1271
1272 WHEN OTHERS THEN
1273
1274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1275
1276 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1277 THEN
1278 OE_MSG_PUB.Add_Exc_Msg
1279 ( G_PKG_NAME
1280 , 'Attributes'
1281 );
1282 END IF;
1283
1284 END Attributes;
1285
1286 -- Procedure Entity_Delete
1287
1288 PROCEDURE Entity_Delete
1289 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1290 , p_LIMITS_rec IN QP_Limits_PUB.Limits_Rec_Type
1291 )
1292 IS
1293 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1294 l_dummy NUMBER := 0;
1295 BEGIN
1296
1297 -- Validate entity delete.
1298
1299 SELECT count(*) into l_dummy from QP_LIMIT_BALANCES
1300 WHERE limit_id = p_LIMITS_rec.limit_id
1301 AND nvl(consumed_amount,0) > 0;
1302
1303 IF l_dummy = 0
1304 THEN
1305 l_return_status := FND_API.G_RET_STS_SUCCESS;
1306 ELSIF l_dummy > 0
1307 THEN
1308 l_return_status := FND_API.G_RET_STS_ERROR;
1309 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_DEL_LIMIT_BAL_EXISTS');
1310 FND_MESSAGE.SET_TOKEN('ENTITY1','Limit');
1311 FND_MESSAGE.SET_TOKEN('ENTITY2','Limit');
1312 OE_MSG_PUB.Add;
1313 END IF;
1314 -- Done.
1315
1316 x_return_status := l_return_status;
1317
1318 EXCEPTION
1319
1320 WHEN FND_API.G_EXC_ERROR THEN
1321
1322 x_return_status := FND_API.G_RET_STS_ERROR;
1323
1324 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1325
1326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1327
1328 WHEN OTHERS THEN
1329
1330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1331
1332 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1333 THEN
1334 OE_MSG_PUB.Add_Exc_Msg
1335 ( G_PKG_NAME
1336 , 'Entity_Delete'
1337 );
1338 END IF;
1339
1340 END Entity_Delete;
1341
1342
1343 PROCEDURE Entity_Update
1344 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1345 , p_LIMITS_rec IN QP_Limits_PUB.Limits_Rec_Type
1346 )
1347 IS
1348 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1349 l_limits_rec QP_Limits_PUB.Limits_Rec_Type;
1350 l_error_code NUMBER := 0;
1351 l_dummy NUMBER := 0;
1352 l_consumed_amount NUMBER := 0;
1353 BEGIN
1354
1355 -- Validate entity update.
1356
1357 l_limits_rec := QP_Limits_Util.Query_Row(p_LIMITS_rec.limit_id);
1358
1359 SELECT count(*) into l_dummy from QP_LIMIT_BALANCES
1360 WHERE limit_id = p_LIMITS_rec.limit_id
1361 AND nvl(consumed_amount,0) > 0;
1362
1363 SELECT MAX(consumed_amount) INTO l_consumed_amount FROM QP_LIMIT_BALANCES
1364 WHERE limit_id = p_LIMITS_rec.limit_id;
1365
1366 IF l_dummy = 0
1367 THEN
1368 l_return_status := FND_API.G_RET_STS_SUCCESS;
1369 ELSIF l_dummy > 0
1370 THEN
1371
1372 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.list_header_id
1373 ,l_limits_rec.list_header_id)
1374 THEN
1375 l_error_code := 1;
1376 END IF;
1377
1378 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.list_line_id
1379 ,l_limits_rec.list_line_id)
1380 THEN
1381 l_error_code := 1;
1382 END IF;
1383
1384 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.limit_number
1385 ,l_limits_rec.limit_number)
1386 THEN
1387 l_error_code := 1;
1388 END IF;
1389
1390 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.basis
1391 ,l_limits_rec.basis)
1392 THEN
1393 l_error_code := 1;
1394 END IF;
1395
1396 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.organization_flag
1397 ,l_limits_rec.organization_flag)
1398 THEN
1399 l_error_code := 1;
1400 END IF;
1401
1402 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.limit_level_code
1403 ,l_limits_rec.limit_level_code)
1404 THEN
1405 l_error_code := 1;
1406 END IF;
1407
1408 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.limit_exceed_action_code
1409 ,l_limits_rec.limit_exceed_action_code)
1410 THEN
1411 l_error_code := 1;
1412 END IF;
1413
1414 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.limit_hold_flag
1415 ,l_limits_rec.limit_hold_flag)
1416 THEN
1417 l_error_code := 1;
1418 END IF;
1419
1420 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.multival_attr1_type
1421 ,l_limits_rec.multival_attr1_type)
1422 THEN
1423 l_error_code := 1;
1424 END IF;
1425
1426 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.multival_attr1_context
1427 ,l_limits_rec.multival_attr1_context)
1428 THEN
1429 l_error_code := 1;
1430 END IF;
1431
1432 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.multival_attribute1
1433 ,l_limits_rec.multival_attribute1)
1434 THEN
1435 l_error_code := 1;
1436 END IF;
1437
1438 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.multival_attr1_datatype
1439 ,l_limits_rec.multival_attr1_datatype)
1440 THEN
1441 l_error_code := 1;
1442 END IF;
1443
1444 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.multival_attr2_type
1445 ,l_limits_rec.multival_attr2_type)
1446 THEN
1447 l_error_code := 1;
1448 END IF;
1449
1450 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.multival_attr2_context
1451 ,l_limits_rec.multival_attr2_context)
1452 THEN
1453 l_error_code := 1;
1454 END IF;
1455
1456 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.multival_attribute2
1457 ,l_limits_rec.multival_attribute2)
1458 THEN
1459 l_error_code := 1;
1460 END IF;
1461
1462 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.multival_attr2_datatype
1463 ,l_limits_rec.multival_attr2_datatype)
1464 THEN
1465 l_error_code := 1;
1466 END IF;
1467
1468 IF NOT QP_GLOBALS.Equal(p_LIMITS_rec.amount
1469 ,l_limits_rec.amount)
1470 THEN
1471 IF p_LIMITS_rec.amount < l_consumed_amount
1472 THEN
1473 l_error_code := 1;
1474 END IF;
1475 END IF;
1476
1477 IF l_error_code = 1
1478 THEN
1479 l_return_status := FND_API.G_RET_STS_ERROR;
1480 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_UPD_LIMIT_BAL_EXISTS');
1481 FND_MESSAGE.SET_TOKEN('ENTITY1','Limit');
1482 FND_MESSAGE.SET_TOKEN('ENTITY2','Limit');
1483 OE_MSG_PUB.Add;
1484 ELSIF l_error_code = 0
1485 THEN
1486 l_return_status := FND_API.G_RET_STS_SUCCESS;
1487 END IF;
1488
1489 END IF;
1490 -- Done.
1491
1492 x_return_status := l_return_status;
1493
1494 EXCEPTION
1495
1496 WHEN FND_API.G_EXC_ERROR THEN
1497
1498 x_return_status := FND_API.G_RET_STS_ERROR;
1499
1500 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1501
1502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1503
1504 WHEN OTHERS THEN
1505
1506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1507
1508 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1509 THEN
1510 OE_MSG_PUB.Add_Exc_Msg
1511 ( G_PKG_NAME
1512 , 'Entity_Delete'
1513 );
1514 END IF;
1515
1516 END Entity_Update;
1517
1518 END QP_Validate_Limits;