[Home] [Help]
PACKAGE BODY: APPS.OE_LINE_PRICE_AATTR_UTIL
Source
1 PACKAGE BODY Oe_Line_Price_Aattr_util AS
2 /* $Header: OEXULAAB.pls 120.0 2005/06/01 02:26:30 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Oe_Line_Price_Aattr_util';
5
6 PROCEDURE Query_Row
7 ( p_price_adj_attrib_id IN NUMBER
8 , x_Line_Adj_Att_Rec IN OUT NOCOPY OE_Order_PUB.Line_Adj_Att_Rec_Type
9 )
10 IS
11
12 l_Line_Adj_Att_Tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
13
14 --
15 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
16 --
17 BEGIN
18 Query_Rows
19 ( p_price_adj_attrib_id => p_price_adj_attrib_id
20 , x_Line_Adj_Att_Tbl => l_Line_Adj_Att_Tbl
21 );
22
23 x_Line_Adj_Att_Rec := l_Line_Adj_Att_Tbl(1);
24
25 END Query_Row;
26
27
28
29 PROCEDURE Query_Rows
30 ( p_price_adj_attrib_id IN NUMBER :=
31 FND_API.G_MISS_NUM
32 , p_price_adjustment_id IN NUMBER :=
33 FND_API.G_MISS_NUM
34 , x_Line_Adj_Att_Tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Adj_Att_Tbl_Type
35 )
36 IS
37 l_count NUMBER;
38
39 CURSOR l_price_Adj_att_csr IS
40 SELECT
41 PRICE_ADJUSTMENT_ID
42 ,PRICING_CONTEXT
43 ,PRICING_ATTRIBUTE
44 ,CREATION_DATE
45 ,CREATED_BY
46 ,LAST_UPDATE_DATE
47 ,LAST_UPDATED_BY
48 ,LAST_UPDATE_LOGIN
49 ,PROGRAM_APPLICATION_ID
50 ,PROGRAM_ID
51 ,PROGRAM_UPDATE_DATE
52 ,REQUEST_ID
53 ,PRICING_ATTR_VALUE_FROM
54 ,PRICING_ATTR_VALUE_TO
55 ,COMPARISON_OPERATOR
56 ,FLEX_TITLE
57 ,PRICE_ADJ_ATTRIB_ID
58 ,LOCK_CONTROL
59 from oe_price_adj_attribs where
60 PRICE_ADJ_ATTRIB_ID = p_price_adj_attrib_id
61 Union all
62 SELECT
63 PRICE_ADJUSTMENT_ID
64 ,PRICING_CONTEXT
65 ,PRICING_ATTRIBUTE
66 ,CREATION_DATE
67 ,CREATED_BY
68 ,LAST_UPDATE_DATE
69 ,LAST_UPDATED_BY
70 ,LAST_UPDATE_LOGIN
71 ,PROGRAM_APPLICATION_ID
72 ,PROGRAM_ID
73 ,PROGRAM_UPDATE_DATE
74 ,REQUEST_ID
75 ,PRICING_ATTR_VALUE_FROM
76 ,PRICING_ATTR_VALUE_TO
77 ,COMPARISON_OPERATOR
78 ,FLEX_TITLE
79 ,PRICE_ADJ_ATTRIB_ID
80 ,LOCK_CONTROL
81 from oe_price_adj_attribs where
82 price_adjustment_id = p_price_adjustment_id;
83
84 --
85 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
86 --
87 BEGIN
88
89 l_count := 1;
90 FOR iml_rec IN l_price_Adj_att_csr LOOP
91
92 x_Line_Adj_Att_Tbl(l_count).PRICE_ADJUSTMENT_ID := iml_rec.PRICE_ADJUSTMENT_ID;
93 x_Line_Adj_Att_Tbl(l_count).PRICING_CONTEXT := iml_rec.PRICING_CONTEXT;
94 x_Line_Adj_Att_Tbl(l_count).PRICING_ATTRIBUTE := iml_rec.PRICING_ATTRIBUTE;
95 x_Line_Adj_Att_Tbl(l_count).CREATION_DATE := iml_rec.CREATION_DATE;
96 x_Line_Adj_Att_Tbl(l_count).CREATED_BY := iml_rec.CREATED_BY;
97 x_Line_Adj_Att_Tbl(l_count).LAST_UPDATE_DATE := iml_rec.LAST_UPDATE_DATE;
98 x_Line_Adj_Att_Tbl(l_count).LAST_UPDATED_BY := iml_rec.LAST_UPDATED_BY;
99 x_Line_Adj_Att_Tbl(l_count).LAST_UPDATE_LOGIN := iml_rec.LAST_UPDATE_LOGIN;
100 x_Line_Adj_Att_Tbl(l_count).PROGRAM_APPLICATION_ID := iml_rec.PROGRAM_APPLICATION_ID;
101 x_Line_Adj_Att_Tbl(l_count).PROGRAM_ID := iml_rec.PROGRAM_ID;
102 x_Line_Adj_Att_Tbl(l_count).PROGRAM_UPDATE_DATE := iml_rec.PROGRAM_UPDATE_DATE;
103 x_Line_Adj_Att_Tbl(l_count).REQUEST_ID := iml_rec.REQUEST_ID;
104 x_Line_Adj_Att_Tbl(l_count).PRICING_ATTR_VALUE_FROM := iml_rec.PRICING_ATTR_VALUE_FROM;
105 x_Line_Adj_Att_Tbl(l_count).PRICING_ATTR_VALUE_TO := iml_rec.PRICING_ATTR_VALUE_TO;
106 x_Line_Adj_Att_Tbl(l_count).COMPARISON_OPERATOR := iml_rec.COMPARISON_OPERATOR;
107 x_Line_Adj_Att_Tbl(l_count).FLEX_TITLE := iml_rec.FLEX_TITLE;
108 x_Line_Adj_Att_Tbl(l_count).PRICE_ADJ_ATTRIB_ID := iml_rec.PRICE_ADJ_ATTRIB_ID;
109 x_Line_Adj_Att_Tbl(l_count).LOCK_CONTROL := iml_rec.LOCK_CONTROL;
110
111 -- set values for non-DB fields
112 x_Line_Adj_Att_Tbl(l_count).db_flag := FND_API.G_TRUE;
113 x_Line_Adj_Att_Tbl(l_count).operation := FND_API.G_MISS_CHAR;
114 x_Line_Adj_Att_Tbl(l_count).return_status := FND_API.G_MISS_CHAR;
115
116 l_count := l_count + 1;
117 END LOOP;
118
119 IF ( p_price_Adj_attrib_id IS NOT NULL
120 and p_price_Adj_attrib_id <> FND_API.G_MISS_NUM)
121 AND
122 (x_Line_Adj_Att_tbl.COUNT = 0 )
123 THEN
124 RAISE NO_DATA_FOUND;
125 END IF;
126
127 -- RETURN l_Line_Adj_Att_tbl;
128
129 EXCEPTION
130
131 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
132
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134
135 WHEN OTHERS THEN
136
137 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
138 THEN
139 OE_MSG_PUB.Add_Exc_Msg
140 ( G_PKG_NAME
141 , 'Query_Rows'
142 );
143 END IF;
144
145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
146
147 END Query_Rows;
148
149 PROCEDURE Insert_Row
150 ( p_Line_Adj_Att_Rec IN OUT NOCOPY OE_Order_PUB.Line_Adj_Att_Rec_Type
151 )
152 IS
153 l_lock_control NUMBER := 1;
154
155 --
156 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
157 --
158 BEGIN
159 IF l_debug_level > 0 THEN
160 oe_debug_pub.add('Entering Oe_Line_Price_Aattr_util.insert_row');
161 END IF;
162
163 INSERT INTO OE_PRICE_ADJ_ATTRIBS
164 ( PRICE_ADJUSTMENT_ID
165 ,PRICING_CONTEXT
166 ,PRICING_ATTRIBUTE
167 ,CREATION_DATE
168 ,CREATED_BY
169 ,LAST_UPDATE_DATE
170 ,LAST_UPDATED_BY
171 ,LAST_UPDATE_LOGIN
172 ,PROGRAM_APPLICATION_ID
173 ,PROGRAM_ID
174 ,PROGRAM_UPDATE_DATE
175 ,REQUEST_ID
176 ,PRICING_ATTR_VALUE_FROM
177 ,PRICING_ATTR_VALUE_TO
178 ,COMPARISON_OPERATOR
179 ,FLEX_TITLE
180 ,PRICE_ADJ_ATTRIB_ID
181 ,LOCK_CONTROL
182 )
183 VALUES
184 ( p_Line_Adj_Att_Rec.PRICE_ADJUSTMENT_ID
185 ,p_Line_Adj_Att_Rec.PRICING_CONTEXT
186 ,p_Line_Adj_Att_Rec.PRICING_ATTRIBUTE
187 ,p_Line_Adj_Att_Rec.CREATION_DATE
188 ,p_Line_Adj_Att_Rec.CREATED_BY
189 ,p_Line_Adj_Att_Rec.LAST_UPDATE_DATE
190 ,p_Line_Adj_Att_Rec.LAST_UPDATED_BY
191 ,p_Line_Adj_Att_Rec.LAST_UPDATE_LOGIN
192 ,p_Line_Adj_Att_Rec.PROGRAM_APPLICATION_ID
193 ,p_Line_Adj_Att_Rec.PROGRAM_ID
194 ,p_Line_Adj_Att_Rec.PROGRAM_UPDATE_DATE
195 ,p_Line_Adj_Att_Rec.REQUEST_ID
196 ,p_Line_Adj_Att_Rec.PRICING_ATTR_VALUE_FROM
197 ,p_Line_Adj_Att_Rec.PRICING_ATTR_VALUE_TO
198 ,p_Line_Adj_Att_Rec.COMPARISON_OPERATOR
199 ,p_Line_Adj_Att_Rec.FLEX_TITLE
200 ,p_Line_Adj_Att_Rec.PRICE_ADJ_ATTRIB_ID
201 ,l_lock_control
202 );
203
204 p_Line_Adj_Att_Rec.lock_control := l_lock_control;
205
206 IF l_debug_level > 0 THEN
207 oe_debug_pub.add('Leaving Oe_Line_Price_Aattr_util.insert_row');
208 END IF;
209
210 EXCEPTION
211
212 WHEN OTHERS THEN
213
214 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
215 THEN
216 FND_MSG_PUB.Add_Exc_Msg
217 ( G_PKG_NAME
218 , 'Insert_Row'
219 );
220 END IF;
221
222 IF l_debug_level > 0 THEN
223 oe_debug_pub.add('Exceptions in Oe_Line_Price_Aattr_util.insert_row:'||SQLERRM);
224 END IF;
225
226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
227
228 END Insert_Row;
229
230 PROCEDURE Update_Row
231 ( p_Line_Adj_Att_Rec IN OUT NOCOPY OE_Order_PUB.Line_Adj_Att_Rec_Type
232 )
233 IS
234 l_lock_control NUMBER;
235
236 --
237 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
238 --
239 BEGIN
240 SELECT lock_control
241 INTO l_lock_control
242 FROM OE_PRICE_ADJ_ATTRIBS
243 WHERE price_adj_attrib_id = p_Line_Adj_Att_rec.price_adj_attrib_id;
244
245 l_lock_control := l_lock_control + 1;
246
247 UPDATE OE_PRICE_ADJ_ATTRIBS
248 SET PRICE_ADJUSTMENT_ID = p_Line_Adj_Att_Rec.Price_Adjustment_id
249 ,PRICING_CONTEXT = p_Line_Adj_Att_Rec.Pricing_Context
250 ,PRICING_ATTRIBUTE = p_Line_Adj_Att_Rec.Pricing_Attribute
251 ,CREATION_DATE = p_Line_Adj_Att_Rec.creation_date
252 ,CREATED_BY = p_Line_Adj_Att_Rec.created_by
253 ,LAST_UPDATE_DATE = p_Line_Adj_Att_Rec.last_update_date
254 ,LAST_UPDATED_BY = p_Line_Adj_Att_Rec.last_updated_by
255 ,LAST_UPDATE_LOGIN = p_Line_Adj_Att_Rec.last_update_login
256 ,PROGRAM_APPLICATION_ID = p_Line_Adj_Att_Rec.program_application_id
257 ,PROGRAM_ID = p_Line_Adj_Att_Rec.program_id
258 ,PROGRAM_UPDATE_DATE = p_Line_Adj_Att_Rec.program_update_date
259 ,REQUEST_ID = p_Line_Adj_Att_Rec.request_id
260 ,PRICING_ATTR_VALUE_FROM = p_Line_Adj_Att_Rec.pricing_attr_value_from
261 ,PRICING_ATTR_VALUE_TO = p_Line_Adj_Att_Rec.pricing_attr_value_to
262 ,COMPARISON_OPERATOR = p_Line_Adj_Att_Rec.comparison_operator
263 ,FLEX_TITLE = p_Line_Adj_Att_Rec.flex_title
264 ,PRICE_ADJ_ATTRIB_ID = p_Line_Adj_Att_Rec.price_adj_attrib_id
265 ,LOCK_CONTROL = l_lock_control
266
267 WHERE PRICE_ADJ_ATTRIB_ID = p_Line_Adj_Att_Rec.price_adj_attrib_id;
268
269 p_Line_Adj_Att_Rec.lock_control := l_lock_control;
270
271
272 EXCEPTION
273
274 WHEN OTHERS THEN
275
276 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
277 THEN
278 OE_MSG_PUB.Add_Exc_Msg
279 ( G_PKG_NAME
280 , 'Update_Row'
281 );
282 END IF;
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
284
285
286
287 END Update_Row ;
288
289 PROCEDURE Delete_Row
290 ( p_price_adj_attrib_id NUMBER := FND_API.G_MISS_NUM
291 , p_price_adjustment_id NUMBER := FND_API.G_MISS_NUM
292 )
293 IS
294 --
295 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
296 --
297 BEGIN
298
299 IF p_price_adjustment_id <> FND_API.G_MISS_NUM then
300 --bug3528335 splitting the DELETE statement to delete attributes corresponding to child lines first (if the parent is PBH) and then the parent
301 --bug3405372 deleting the rows corresponding to the child lines of PBH modifiers as well.
302 DELETE FROM OE_PRICE_ADJ_ATTRIBS
303 WHERE PRICE_ADJUSTMENT_ID IN (SELECT RLTD_PRICE_ADJ_ID
304 FROM OE_PRICE_ADJ_ASSOCS ASSOCS,
305 OE_PRICE_ADJUSTMENTS PARENT
306 WHERE ASSOCS.PRICE_ADJUSTMENT_ID=PARENT.PRICE_ADJUSTMENT_ID
307 AND PARENT.PRICE_ADJUSTMENT_ID=p_price_adjustment_id
308 AND PARENT.LIST_LINE_TYPE_CODE='PBH');
309 IF l_debug_level > 0 THEN
310 oe_debug_pub.add('pviprana: Deleted '||SQL%ROWCOUNT||' pricing attributes of child lines');
311 END IF;
312
313
314
315 DELETE FROM OE_PRICE_ADJ_ATTRIBS
316 WHERE PRICE_ADJUSTMENT_ID = p_price_adjustment_id;
317 --bug3528335 end
318 else
319 DELETE OE_PRICE_ADJ_ATTRIBS
320 WHERE PRICE_ADJ_ATTRIB_ID = p_price_adj_attrib_id;
321 end if;
322
323 EXCEPTION
324
325 WHEN OTHERS THEN
326 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
327 THEN
328 FND_MSG_PUB.Add_Exc_Msg
329 ( G_PKG_NAME
330 , 'Delete_Row'
331 );
332 END IF;
333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
334
335 END Delete_Row;
336
337 PROCEDURE Complete_Record
338 ( p_x_Line_Adj_Att_rec IN OUT NOCOPY OE_Order_PUB.Line_Adj_Att_Rec_Type
339 , p_old_Line_Adj_Att_rec IN OE_Order_PUB.Line_Adj_Att_Rec_Type
340 )
341 IS
342
343 l_Line_Adj_Att_rec OE_Order_PUB.Line_Adj_Att_Rec_Type := p_x_Line_Adj_Att_rec;
344 --
345 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
346 --
347 BEGIN
348
349 IF l_Line_Adj_Att_rec.PRICE_ADJUSTMENT_ID = FND_API.G_MISS_NUM THEN
350 l_Line_Adj_Att_rec.PRICE_ADJUSTMENT_ID := p_old_Line_Adj_Att_rec.PRICE_ADJUSTMENT_ID;
351 END IF;
352
353 IF l_Line_Adj_Att_rec.PRICING_CONTEXT = FND_API.G_MISS_CHAR THEN
354 l_Line_Adj_Att_rec.PRICING_CONTEXT := p_old_Line_Adj_Att_rec.PRICING_CONTEXT;
355 END IF;
356
357 IF l_Line_Adj_Att_rec.PRICING_ATTRIBUTE = FND_API.G_MISS_CHAR THEN
358 l_Line_Adj_Att_rec.PRICING_ATTRIBUTE := p_old_Line_Adj_Att_rec.PRICING_ATTRIBUTE;
359 END IF;
360
361 IF l_Line_Adj_Att_rec.CREATION_DATE = FND_API.G_MISS_DATE THEN
362 l_Line_Adj_Att_rec.CREATION_DATE := p_old_Line_Adj_Att_rec.CREATION_DATE;
363 END IF;
364
365 IF l_Line_Adj_Att_rec.CREATED_BY = FND_API.G_MISS_NUM THEN
366 l_Line_Adj_Att_rec.CREATED_BY := p_old_Line_Adj_Att_rec.CREATED_BY;
367 END IF;
368
369 IF l_Line_Adj_Att_rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE THEN
370 l_Line_Adj_Att_rec.LAST_UPDATE_DATE := p_old_Line_Adj_Att_rec.LAST_UPDATE_DATE;
371 END IF;
372
373 IF l_Line_Adj_Att_rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM THEN
374 l_Line_Adj_Att_rec.LAST_UPDATED_BY := p_old_Line_Adj_Att_rec.LAST_UPDATED_BY;
375 END IF;
376
377 IF l_Line_Adj_Att_rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM THEN
378 l_Line_Adj_Att_rec.LAST_UPDATE_LOGIN := p_old_Line_Adj_Att_rec.LAST_UPDATE_LOGIN;
379 END IF;
380
381 IF l_Line_Adj_Att_rec.PROGRAM_APPLICATION_ID = FND_API.G_MISS_NUM THEN
382 l_Line_Adj_Att_rec.PROGRAM_APPLICATION_ID := p_old_Line_Adj_Att_rec.PROGRAM_APPLICATION_ID;
383 END IF;
384
385 IF l_Line_Adj_Att_rec.PROGRAM_ID = FND_API.G_MISS_NUM THEN
386 l_Line_Adj_Att_rec.PROGRAM_ID := p_old_Line_Adj_Att_rec.PROGRAM_ID;
387 END IF;
388
389 IF l_Line_Adj_Att_rec.PROGRAM_UPDATE_DATE = FND_API.G_MISS_DATE THEN
393 IF l_Line_Adj_Att_rec.REQUEST_ID = FND_API.G_MISS_NUM THEN
390 l_Line_Adj_Att_rec.PROGRAM_UPDATE_DATE := p_old_Line_Adj_Att_rec.PROGRAM_UPDATE_DATE;
391 END IF;
392
394 l_Line_Adj_Att_rec.REQUEST_ID := p_old_Line_Adj_Att_rec.REQUEST_ID;
395 END IF;
396
397 IF l_Line_Adj_Att_rec.PRICING_ATTR_VALUE_FROM = FND_API.G_MISS_CHAR THEN
398 l_Line_Adj_Att_rec.PRICING_ATTR_VALUE_FROM := p_old_Line_Adj_Att_rec.PRICING_ATTR_VALUE_FROM;
399 END IF;
400
401 IF l_Line_Adj_Att_rec.PRICING_ATTR_VALUE_TO = FND_API.G_MISS_CHAR THEN
402 l_Line_Adj_Att_rec.PRICING_ATTR_VALUE_TO := p_old_Line_Adj_Att_rec.PRICING_ATTR_VALUE_TO;
403 END IF;
404
405 IF l_Line_Adj_Att_rec.COMPARISON_OPERATOR = FND_API.G_MISS_CHAR THEN
406 l_Line_Adj_Att_rec.COMPARISON_OPERATOR := p_old_Line_Adj_Att_rec.COMPARISON_OPERATOR;
407 END IF;
408
409 IF l_Line_Adj_Att_rec.FLEX_TITLE = FND_API.G_MISS_CHAR THEN
410 l_Line_Adj_Att_rec.FLEX_TITLE := p_old_Line_Adj_Att_rec.FLEX_TITLE;
411 END IF;
412
413 IF l_Line_Adj_Att_rec.PRICE_ADJ_ATTRIB_ID = FND_API.G_MISS_NUM THEN
414 l_Line_Adj_Att_rec.PRICE_ADJ_ATTRIB_ID := p_old_Line_Adj_Att_rec.PRICE_ADJ_ATTRIB_ID;
415 END IF;
416
417 -- RETURN l_Line_Adj_Att_rec;
418 p_x_Line_Adj_Att_rec := l_Line_Adj_Att_rec;
419
420 END Complete_Record;
421
422 PROCEDURE Convert_Miss_To_Null
423 ( p_x_Line_Adj_Att_rec IN OUT NOCOPY OE_Order_PUB.Line_Adj_Att_Rec_Type
424 )
425 IS
426 l_Line_Adj_Att_rec OE_Order_PUB.Line_Adj_Att_Rec_Type := p_x_Line_Adj_Att_rec;
427 --
428 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
429 --
430 BEGIN
431
432 IF l_Line_Adj_Att_rec.PRICE_ADJUSTMENT_ID = FND_API.G_MISS_NUM THEN
433 l_Line_Adj_Att_rec.PRICE_ADJUSTMENT_ID := Null;
434 END IF;
435
436 IF l_Line_Adj_Att_rec.PRICING_CONTEXT = FND_API.G_MISS_CHAR THEN
437 l_Line_Adj_Att_rec.PRICING_CONTEXT := Null;
438 END IF;
439
440 IF l_Line_Adj_Att_rec.PRICING_ATTRIBUTE = FND_API.G_MISS_CHAR THEN
441 l_Line_Adj_Att_rec.PRICING_ATTRIBUTE := Null;
442 END IF;
443
444 IF l_Line_Adj_Att_rec.CREATION_DATE = FND_API.G_MISS_DATE THEN
445 l_Line_Adj_Att_rec.CREATION_DATE := Null;
446 END IF;
447
448 IF l_Line_Adj_Att_rec.CREATED_BY = FND_API.G_MISS_NUM THEN
449 l_Line_Adj_Att_rec.CREATED_BY := Null;
450 END IF;
451
452 IF l_Line_Adj_Att_rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE THEN
453 l_Line_Adj_Att_rec.LAST_UPDATE_DATE := Null;
454 END IF;
455
456 IF l_Line_Adj_Att_rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM THEN
457 l_Line_Adj_Att_rec.LAST_UPDATED_BY := Null;
458 END IF;
459
460 IF l_Line_Adj_Att_rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM THEN
461 l_Line_Adj_Att_rec.LAST_UPDATE_LOGIN := Null;
462 END IF;
463
464 IF l_Line_Adj_Att_rec.PROGRAM_APPLICATION_ID = FND_API.G_MISS_NUM THEN
465 l_Line_Adj_Att_rec.PROGRAM_APPLICATION_ID := Null;
466 END IF;
467
468 IF l_Line_Adj_Att_rec.PROGRAM_ID = FND_API.G_MISS_NUM THEN
469 l_Line_Adj_Att_rec.PROGRAM_ID := Null;
470 END IF;
471
472 IF l_Line_Adj_Att_rec.PROGRAM_UPDATE_DATE = FND_API.G_MISS_DATE THEN
473 l_Line_Adj_Att_rec.PROGRAM_UPDATE_DATE := Null;
474 END IF;
475
476 IF l_Line_Adj_Att_rec.REQUEST_ID = FND_API.G_MISS_NUM THEN
477 l_Line_Adj_Att_rec.REQUEST_ID := Null;
478 END IF;
479
480 IF l_Line_Adj_Att_rec.PRICING_ATTR_VALUE_FROM = FND_API.G_MISS_CHAR THEN
481 l_Line_Adj_Att_rec.PRICING_ATTR_VALUE_FROM := Null;
482 END IF;
483
484 IF l_Line_Adj_Att_rec.PRICING_ATTR_VALUE_TO = FND_API.G_MISS_CHAR THEN
485 l_Line_Adj_Att_rec.PRICING_ATTR_VALUE_TO := Null;
486 END IF;
487
488 IF l_Line_Adj_Att_rec.COMPARISON_OPERATOR = FND_API.G_MISS_CHAR THEN
489 l_Line_Adj_Att_rec.COMPARISON_OPERATOR := Null;
490 END IF;
491
492 IF l_Line_Adj_Att_rec.FLEX_TITLE = FND_API.G_MISS_CHAR THEN
493 l_Line_Adj_Att_rec.FLEX_TITLE := Null;
494 END IF;
495
496 IF l_Line_Adj_Att_rec.PRICE_ADJ_ATTRIB_ID = FND_API.G_MISS_NUM THEN
497 l_Line_Adj_Att_rec.PRICE_ADJ_ATTRIB_ID := Null;
498 END IF;
499
500 -- RETURN l_Line_Adj_Att_rec;
501 p_x_Line_Adj_Att_rec := l_Line_Adj_Att_rec;
502
503 END Convert_Miss_To_Null;
504
505 PROCEDURE Apply_Attribute_Changes
506 ( p_x_Line_Adj_Att_rec IN OUT NOCOPY OE_Order_PUB.Line_Adj_Att_Rec_Type
507 , p_old_Line_Adj_Att_rec IN OE_Order_PUB.Line_Adj_Att_Rec_Type := OE_Order_PUB.G_MISS_Line_Adj_Att_REC
508 -- , x_Line_Adj_Att_rec OUT OE_Order_PUB.Line_Adj_Att_Rec_Type
509 )
510 IS
511
512 --
513 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
514 --
515 BEGIN
516
517 -- x_Line_Adj_Att_rec := p_Line_Adj_Att_rec;
518 p_x_Line_Adj_Att_rec := p_x_Line_Adj_Att_rec;
519
520 END Apply_Attribute_Changes;
521
522 PROCEDURE Lock_Row
523 ( x_return_status OUT NOCOPY VARCHAR2
524
525 , p_x_Line_Adj_Att_rec IN OUT NOCOPY OE_Order_PUB.Line_Adj_Att_Rec_Type
526 , p_price_adj_attrib_id IN NUMBER := FND_API.G_MISS_NUM
527 )
531 l_price_adj_attrib_id NUMBER;
528 is
529 l_Line_Adj_Att_rec OE_Order_PUB.Line_Adj_Att_Rec_Type;
530 l_lock_control NUMBER;
532
533 --
534 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
535 --
536 BEGIN
537
538 IF l_debug_level > 0 THEN
539 oe_debug_pub.add( 'ENTERING OE_LINE_PRICE_PATTR_UTIL.LOCK_ROW.' , 1 ) ;
540 END IF;
541 SAVEPOINT Lock_Row;
542
543 l_lock_control := NULL;
544
545 IF p_price_adj_attrib_id <> FND_API.G_MISS_NUM THEN
546 l_price_adj_attrib_id := p_price_adj_attrib_id;
547 ELSE
548 l_price_adj_attrib_id := p_x_Line_Adj_Att_rec.price_adj_attrib_id;
549 l_lock_control := p_x_Line_Adj_Att_rec.lock_control;
550 END IF;
551
552 SELECT price_adj_attrib_id
553 INTO l_price_adj_attrib_id
554 FROM oe_price_adj_attribs
555 WHERE price_adj_attrib_id = l_price_adj_attrib_id
556 FOR UPDATE NOWAIT;
557
558 OE_Line_Price_Aattr_Util.Query_Row
559 (p_price_adj_attrib_id => l_price_adj_attrib_id
560 ,x_Line_Adj_Att_rec => p_x_Line_Adj_Att_rec
561 );
562
563
564 IF l_debug_level > 0 THEN
565 oe_debug_pub.add( 'QUERIED LOCK_CONTROL: '|| P_X_LINE_ADJ_ATT_REC.LOCK_CONTROL , 1 ) ;
566 END IF;
567
568 -- If lock_control is not passed(is null or missing), then return the locked record.
569
570 IF l_lock_control is null OR
571 l_lock_control = FND_API.G_MISS_NUM
572 THEN
573
574 -- Set return status
575 x_return_status := FND_API.G_RET_STS_SUCCESS;
576 p_x_Line_Adj_Att_rec.return_status := FND_API.G_RET_STS_SUCCESS;
577
578 -- return for lock by ID.
579 RETURN;
580
581 END IF;
582
583 -- Row locked. If the whole record is passed, then
584 -- Compare lock_control.
585
586 IF l_debug_level > 0 THEN
587 oe_debug_pub.add( 'COMPARE ' , 1 ) ;
588 END IF;
589
590 IF OE_GLOBALS.Equal(p_x_Line_Adj_Att_rec.lock_control,
591 l_lock_control)
592 THEN
593
594 -- Row has not changed. Set out parameter.
595
596 IF l_debug_level > 0 THEN
597 oe_debug_pub.add( 'LOCKED ROW' , 1 ) ;
598 END IF;
599
600 -- Set return status
601
602 x_return_status := FND_API.G_RET_STS_SUCCESS;
603 p_x_Line_Adj_Att_rec.return_status := FND_API.G_RET_STS_SUCCESS;
604
605 ELSE
606
607 -- Row has changed by another user.
608 IF l_debug_level > 0 THEN
609 oe_debug_pub.add( 'ROW CHANGED BY OTHER USER' , 1 ) ;
610 END IF;
611
612 x_return_status := FND_API.G_RET_STS_ERROR;
613 p_x_Line_Adj_Att_rec.return_status := FND_API.G_RET_STS_ERROR;
614
615 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
616 THEN
617
618 -- Release the lock
619 ROLLBACK TO Lock_Row;
620
621 fnd_message.set_name('ONT','OE_LOCK_ROW_CHANGED');
622 OE_MSG_PUB.Add;
623
624 END IF;
625
626 END IF;
627
628 EXCEPTION
629
630 WHEN NO_DATA_FOUND THEN
631
632 x_return_status := FND_API.G_RET_STS_ERROR;
633 p_x_Line_Adj_Att_rec.return_status := FND_API.G_RET_STS_ERROR;
634
635 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
636 THEN
637
638 FND_MESSAGE.SET_NAME('ONT','OE_LOCK_ROW_DELETED');
639 FND_MSG_PUB.Add;
640
641 END IF;
642 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
643
644 x_return_status := FND_API.G_RET_STS_ERROR;
645 p_x_Line_Adj_Att_rec.return_status := FND_API.G_RET_STS_ERROR;
646
647 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
648 THEN
649
650 FND_MESSAGE.SET_NAME('ONT','OE_LOCK_ROW_ALREADY_LOCKED');
651 FND_MSG_PUB.Add;
652
653 END IF;
654 WHEN OTHERS THEN
655
656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
657 p_x_Line_Adj_Att_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
658
659 end lock_row;
660
661 -- procedure lock_rows
662 PROCEDURE Lock_Rows
663 ( p_price_adj_attrib_id IN NUMBER
664 := FND_API.G_MISS_NUM
665 , p_price_adjustment_id IN NUMBER
666 := FND_API.G_MISS_NUM
667 , x_Line_Adj_Att_tbl OUT NOCOPY OE_Order_PUB.Line_Adj_Att_Tbl_Type
668 , x_return_status OUT NOCOPY VARCHAR2
669
670 )
671 IS
672
673 CURSOR lock_att_lines(p_price_adjustment_id IN NUMBER) IS
674 SELECT price_adj_attrib_id
675 FROM oe_price_adj_attribs
676 WHERE price_adjustment_id = p_price_adjustment_id
677 FOR UPDATE NOWAIT;
678
679 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
680 l_price_adj_attrib_id NUMBER;
681 l_lock_control NUMBER;
682
683 --
684 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
685 --
686 BEGIN
687
688 IF l_debug_level > 0 THEN
689 oe_debug_pub.add( 'ENTERING OE_LINE_PRICE_AATTR_UTIL.LOCK_ROWS.' , 1 ) ;
690 END IF;
691
692 IF (p_price_adj_attrib_id IS NOT NULL AND
693 p_price_adj_attrib_id <> FND_API.G_MISS_NUM) AND
694 (p_price_adjustment_id IS NOT NULL AND
695 p_price_adjustment_id <> FND_API.G_MISS_NUM)
696 THEN
697 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
698 OE_MSG_PUB.Add_Exc_Msg
699 ( G_PKG_NAME
700 , 'Lock_Rows'
701 , 'Keys are mutually exclusive: price_adj_attrib_id = ' ||
702 p_price_adj_attrib_id || ', price_adjustment_id = ' || p_price_adjustment_id );
703 END IF;
704
705 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
706 END IF;
707
708 IF p_price_adj_attrib_id <> FND_API.G_MISS_NUM THEN
709
710 SELECT price_adj_attrib_id
711 INTO l_price_adj_attrib_id
712 FROM oe_price_adj_attribs
713 WHERE price_adj_attrib_id = p_price_adj_attrib_id
714 FOR UPDATE NOWAIT;
715 END IF;
716
717 -- null price_adjustment_id shouldn't be passed in unnecessarily if
718 -- price_adj_attrib_id is passed in already.
719 BEGIN
720 IF p_price_adjustment_id <> FND_API.G_MISS_NUM THEN
721 SAVEPOINT LOCK_ROWS;
722 OPEN lock_att_lines(p_price_adjustment_id);
723
724 LOOP
725 FETCH lock_att_lines INTO l_price_adj_attrib_id;
726 EXIT WHEN lock_att_lines%NOTFOUND;
727 END LOOP;
728 CLOSE lock_att_lines;
729 END IF;
730 EXCEPTION
731 WHEN OTHERS THEN
732 ROLLBACK TO LOCK_ROWS;
733
734 IF lock_att_lines%ISOPEN THEN
735 CLOSE lock_att_lines;
736 END IF;
737
738 RAISE;
739 END;
740
741
742 OE_Line_Price_Aattr_Util.Query_Rows
743 ( p_price_adj_attrib_id => p_price_adj_attrib_id
744 , p_price_adjustment_id => p_price_adjustment_id
745 , x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
746 );
747
748 x_return_status := FND_API.G_RET_STS_SUCCESS;
749
750 EXCEPTION
751 WHEN NO_DATA_FOUND THEN
752 x_return_status := FND_API.G_RET_STS_ERROR;
753 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
754 THEN
755 fnd_message.set_name('ONT','OE_LOCK_ROW_DELETED');
756 OE_MSG_PUB.Add;
757 END IF;
758
759 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
760 x_return_status := FND_API.G_RET_STS_ERROR;
761 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
762 THEN
763 fnd_message.set_name('ONT','OE_LOCK_ROW_ALREADY_LOCKED');
764 OE_MSG_PUB.Add;
765 END IF;
766
767 WHEN OTHERS THEN
768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
769 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
770 THEN
771 OE_MSG_PUB.Add_Exc_Msg
772 ( G_PKG_NAME
773 , 'Lock_Rows'
774 );
775 END IF;
776
777 IF l_debug_level > 0 THEN
778 oe_debug_pub.add( 'EXITING OE_LINE_PRICE_AATTR_UTIL.LOCK_ROWS.' , 1 ) ;
779 END IF;
780
781
782 END lock_rows;
783
784 END Oe_Line_Price_Aattr_util;