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