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