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