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