1 Package body OE_DBI_UTIL As
2 /* $Header: OEXUDBIB.pls 120.3.12010000.3 2009/04/15 13:26:43 ckasera ship $ */
3
4
5 Procedure Update_DBI_Log
6 ( x_return_status OUT NOCOPY VARCHAR2
7
8 ) IS
9
10 l_header_id NUMBER;
11 l_line_id NUMBER;
12 I BINARY_INTEGER;
13 j BINARY_INTEGER;
14 l_exists VARCHAR2(1) :='N';
15 l_set_of_books_rec oe_order_cache.Set_Of_Books_Rec_Type;
16 l_set_of_books_id NUMBER;
17 l_currency_code VARCHAR2(15);
18 l_last_update_date DATE;
19 l_last_update_date_old DATE;
20 l_header_last_update_date DATE;
21
22 CURSOR get_lines(p_header_id number)
23 IS
24 select line_id, header_id, l_header_last_update_date, 'N'
25 from oe_order_lines_all
26 where header_id = p_header_id;
27
28 --
29 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
30 --
31 TYPE line_index_tbl IS TABLE OF VARCHAR2(1)
32 INDEX BY VARCHAR2(40);
33 l_line_index_tbl line_index_tbl;
34
35 TYPE T_NUM IS TABLE OF NUMBER;
36 TYPE T_DATE IS TABLE OF DATE;
37 TYPE T_V1 IS TABLE OF VARCHAR2(1);
38
39 TYPE DBI_REC IS RECORD
40 (
41 header_id T_NUM := T_NUM(),
42 line_id T_NUM := T_NUM(),
43 last_update_date T_DATE := T_DATE(),
44 rec_exists_flag T_V1 := T_V1()
45 );
46
47 l_dbi_rec DBI_REC;
48 l_dbi_create_rec DBI_REC;
49
50 BEGIN
51
52 IF l_debug_level > 0 THEN
53 oe_debug_pub.add( 'ENTERING UPDATE_DBI API' , 1 ) ;
54 END IF;
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56
57 l_set_of_books_rec := OE_ORDER_CACHE.LOAD_SET_OF_BOOKS;
58 l_set_of_books_id := l_set_of_books_rec.set_of_books_id;
59 l_currency_code := l_set_of_books_rec.currency_code;
60
61 IF (l_set_of_books_id IS NULL) OR (l_currency_code IS NULL) THEN
62 oe_debug_pub.add( 'SET_OF_BOOKS_ID= '|| L_SET_OF_BOOKS_ID , 1 ) ;
63 oe_debug_pub.add( 'CURRENCY_CODE= '|| L_CURRENCY_CODE , 1 ) ;
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65 END IF;
66
67
68 IF (oe_order_util.g_header_rec.header_id is NOT NULL AND
69 oe_order_util.g_header_rec.header_id <> FND_API.G_MISS_NUM AND
70 oe_order_util.g_line_tbl.COUNT = 0 AND
71 oe_order_util.g_line_scredit_tbl.COUNT = 0 AND
72 oe_order_util. g_line_adj_tbl.COUNT = 0)
73 THEN
74
75 /* Some field in the header is changed and no line level changes */
76 IF l_debug_level > 0 THEN
77 oe_debug_pub.add( 'SOMETHING IN THE HEADER CHANGED' ) ;
78 END IF;
79 l_header_id := oe_order_util.g_header_rec.header_id;
80 IF l_debug_level > 0 THEN
81 oe_debug_pub.add( 'JPN: HEADER ID' || L_HEADER_ID ) ;
82 END IF;
83 l_header_last_update_date := oe_order_util.g_header_rec.last_update_date;
84
85 ELSIF (oe_order_util.g_header_scredit_tbl.count > 0 AND
86 oe_order_util.g_line_tbl.COUNT = 0 AND
87 oe_order_util.g_line_scredit_tbl.COUNT = 0) THEN
88
89 -- Some field in the header sales credit changed and no line level
90 -- changes
91
92 IF l_debug_level > 0 THEN
93 oe_debug_pub.add( 'SOMETHING IN THE HEADER sales credit CHANGED' ) ;
94 END IF;
95 I := oe_order_util.g_old_header_scredit_tbl.FIRST;
96 l_header_id := oe_order_util.g_old_header_scredit_tbl(I).header_id;
97 IF l_debug_level > 0 THEN
98 oe_debug_pub.add( 'JPN: HEADER ID' || L_HEADER_ID ) ;
99 END IF;
100
101 IF oe_order_util.g_header_scredit_tbl(I).last_update_date IS NULL THEN
102 l_header_last_update_date :=
103 oe_order_util.g_old_header_scredit_tbl(I).last_update_date;
104 ELSE
105 l_header_last_update_date :=
106 oe_order_util.g_header_scredit_tbl(I).last_update_date;
107 END IF;
108 IF l_debug_level > 0 THEN
109 oe_debug_pub.add( 'LAST UPDATE DATE OLD=' || l_header_last_update_date ) ;
110 END IF;
111
112 ELSIF (oe_order_util.g_header_adj_tbl.count > 0 ) THEN
113
114 IF l_debug_level > 0 THEN
115 oe_debug_pub.add( 'SOMETHING IN THE HEADER ADJUSTMENTS CHANGED' ) ;
116 END IF;
117 I := oe_order_util.g_header_adj_tbl.FIRST;
118 l_header_id := oe_order_util.g_header_adj_tbl(I).header_id;
119
120 IF l_debug_level > 0 THEN
121 oe_debug_pub.add( 'JPN: HEADER ID' || L_HEADER_ID ) ;
122 END IF;
123
124 l_header_last_update_date := oe_order_util.g_header_adj_tbl(I).last_update_date;
125
126 IF l_debug_level > 0 THEN
127 oe_debug_pub.add( 'LAST UPDATE DATE OLD=' || l_header_last_update_date ) ;
128 END IF;
129
130 END IF;
131
132 IF l_header_id IS NOT NULL THEN
133
134 BEGIN
135 IF l_debug_level > 0 THEN
136 oe_debug_pub.add( 'Opening get_lines' ) ;
137 END IF;
138 OPEN get_lines(l_header_id);
139 FETCH get_lines BULK COLLECT INTO
140 l_dbi_rec.line_id,
141 l_dbi_rec.header_id,
142 l_dbi_rec.last_update_date,
143 l_dbi_rec.rec_exists_flag;
144 IF get_lines%ROWCOUNT = 0 THEN
145 raise NO_DATA_FOUND;
146 END IF;
147 CLOSE get_lines;
148
149 EXCEPTION
150 WHEN NO_DATA_FOUND THEN
151 IF l_debug_level > 0 THEN
152 oe_debug_pub.add( 'There are no lines in this order' ) ;
153 END IF;
154 -- Header has no lines.
155 RETURN;
156 END;
157 GOTO HANDLE_DBI_REC;
158
159 END IF;
160
161 j := 1;
162
163 -- Check if there are changed lines
164 IF oe_order_util.g_line_tbl.COUNT > 0 THEN
165
166 l_dbi_rec.line_id.EXTEND(oe_order_util.g_line_tbl.COUNT);
167 l_dbi_rec.last_update_date.EXTEND(oe_order_util.g_line_tbl.COUNT);
168 l_dbi_rec.rec_exists_flag.EXTEND(oe_order_util.g_line_tbl.COUNT);
169 l_dbi_rec.header_id.EXTEND(oe_order_util.g_line_tbl.COUNT); ---bug 7319732,7347663
170 I := oe_order_util.g_line_tbl.FIRST;
171 l_header_id := oe_order_util.g_old_line_tbl(I).header_id;
172
173 IF l_debug_level > 0 THEN
174 oe_debug_pub.add( 'IN LINE HEADER_ID= '||L_HEADER_ID, 1 ) ;
175 END IF;
176 WHILE I IS NOT NULL LOOP
177 IF l_debug_level > 0 THEN
178 oe_debug_pub.add( 'LOOP INDEX='|| I , 1 ) ;
179 END IF;
180
181 l_dbi_rec.line_id(j) :=oe_order_util.g_old_line_tbl(I).line_id;
182 l_dbi_rec.rec_exists_flag(j) := 'N';
183 l_dbi_rec.header_id(j) :=oe_order_util.g_old_line_tbl(I).Header_id ;--bug 7319732,7347663
184 -- Set the value for line_index table to Y to indicate that record
185 -- already captured in l_dbi_rec.line_id for the given line_id.
186 l_line_index_tbl(l_dbi_rec.line_id(j)) := 'Y';
187
188 IF oe_order_util.g_line_tbl(I).last_update_date IS NULL THEN
189 l_dbi_rec.last_update_date(j) :=
190 oe_order_util.g_old_line_tbl(I).last_update_date;
191 ELSE
192 l_dbi_rec.last_update_date(j) :=
193 oe_order_util.g_line_tbl(I).last_update_date;
194 END IF;
195
196 IF l_debug_level > 0 THEN
197 oe_debug_pub.add( 'LINE_ID= '||l_dbi_rec.line_id(j) ) ;
198 oe_debug_pub.add( 'LAST UPDATE DATE=' || l_dbi_rec.last_update_date(j) ) ;
199 oe_debug_pub.add( 'LINE TBL HEADER ID=' || L_HEADER_ID ) ;
200 oe_debug_pub.add( 'LINE TBL HEADER ID=' ||l_dbi_rec.header_id(j)); --bug 7319732,7347663
201 END IF;
202 I := oe_order_util.g_line_tbl.NEXT(I);
203 j := j + 1;
204 END LOOP;
205 END IF;
206
207 IF l_dbi_rec.line_id.COUNT > 0 THEN
208 j := l_dbi_rec.line_id.COUNT + 1;
209 ELSE
210 j := 1;
211 END IF;
212
213 IF oe_order_util.g_line_scredit_tbl.COUNT > 0 THEN
214
215 I := oe_order_util.g_line_scredit_tbl.FIRST;
216 l_header_id := oe_order_util.g_old_line_scredit_tbl(I).header_id;
217 IF l_debug_level > 0 THEN
218 oe_debug_pub.add( 'IN LINE SC HEADER_ID= '|| l_header_id, 1 ) ;
219 END IF;
220
221 WHILE I IS NOT NULL LOOP
222 IF l_debug_level > 0 THEN
223 oe_debug_pub.add( 'LOOP INDEX='|| I , 1 ) ;
224 END IF;
225
226 l_line_id :=oe_order_util.g_old_line_scredit_tbl(I).line_id;
227 IF NOT l_line_index_tbl.EXISTS(to_char(l_line_id)) THEN
228
229 l_dbi_rec.line_id.EXTEND;
230 l_dbi_rec.last_update_date.EXTEND;
231 l_dbi_rec.rec_exists_flag.EXTEND;
232 l_dbi_rec.Header_id.EXTEND ; ---bug 7319732 ,7347663
233 l_dbi_rec.Header_id(j) :=oe_order_util.g_old_line_scredit_tbl(I).header_id; --bug 7319732,7347663
234 l_dbi_rec.line_id(j) := oe_order_util.g_old_line_scredit_tbl(I).line_id;
235 l_dbi_rec.rec_exists_flag(j) := 'N';
236 -- Set the value for line_index table to Y to indicate that record
237 -- already captured in l_dbi_rec.line_id for the given line_id.
238 l_line_index_tbl(l_dbi_rec.line_id(j)) := 'Y';
239
240 IF oe_order_util.g_line_scredit_tbl(I).last_update_date IS NULL
241 THEN
242 l_dbi_rec.last_update_date(j) :=
243 oe_order_util.g_old_line_scredit_tbl(I).last_update_date;
244 ELSE
245 l_dbi_rec.last_update_date(j) :=
246 oe_order_util.g_line_scredit_tbl(I).last_update_date;
247
248 END IF;
249 IF l_debug_level > 0 THEN
250 oe_debug_pub.add( 'LINE_ID= '||l_dbi_rec.line_id(j) ) ;
251 oe_debug_pub.add( 'LAST UPDATE DATE=' || l_dbi_rec.last_update_date(J) ) ;
252 END IF;
253 j := j + 1;
254
255 END IF;
256 I := oe_order_util.g_line_scredit_tbl.NEXT(I);
257 END LOOP;
258
259 END IF;
260
261 IF l_dbi_rec.line_id.COUNT > 0 THEN
262 j := l_dbi_rec.line_id.COUNT + 1;
263 ELSE
264 j := 1;
265 END IF;
266
267 IF oe_order_util.g_line_adj_tbl.COUNT >0 THEN
268
269 I := oe_order_util.g_line_adj_tbl.FIRST;
270 l_header_id := oe_order_util.g_old_line_adj_tbl(I).header_id;
271
272 IF l_debug_level > 0 THEN
273 oe_debug_pub.add( 'IN LINE PRICE ADJUSTMENTS HEADER_ID= '|| l_header_id, 1 ) ;
274 END IF;
275
276 WHILE I IS NOT NULL LOOP
277 IF l_debug_level > 0 THEN
278 oe_debug_pub.add( 'PA LOOP INDEX='|| I , 1 ) ;
279 END IF;
280 l_line_id :=oe_order_util.g_old_line_adj_tbl(I).line_id;
281
282 IF NOT l_line_index_tbl.EXISTS(to_char(l_line_id)) THEN
283
284 l_dbi_rec.line_id.EXTEND;
285 l_dbi_rec.last_update_date.EXTEND;
286 l_dbi_rec.rec_exists_flag.EXTEND;
287 l_dbi_rec.Header_id.EXTEND ; ---bug 7319732 ,7347663
288 l_dbi_rec.Header_id(j) :=oe_order_util.g_old_line_adj_tbl(I).header_id; --bug 7319732 ,7347663
289 l_dbi_rec.line_id(j) := oe_order_util.g_old_line_adj_tbl(I).line_id;
290 l_dbi_rec.rec_exists_flag(j) := 'N';
291 -- Set the value for line_index table to Y to indicate that record
292 -- already captured in l_dbi_rec.line_id for the given line_id.
293 l_line_index_tbl(l_dbi_rec.line_id(j)) := 'Y';
294
295 IF oe_order_util.g_line_adj_tbl(I).last_update_date IS NULL
296 THEN
297 l_dbi_rec.last_update_date(J) :=
298 oe_order_util.g_old_line_adj_tbl(I).last_update_date;
299 ELSE
300 l_dbi_rec.last_update_date(J) :=
301 oe_order_util.g_line_adj_tbl(I).last_update_date;
302
303 END IF;
304 IF l_debug_level > 0 THEN
305 oe_debug_pub.add( 'LINE_ID= '||l_dbi_rec.line_id(j) ) ;
306 oe_debug_pub.add( 'LAST UPDATE DATE=' || l_dbi_rec.last_update_date(J) ) ;
307 END IF;
308 j := j + 1;
309
310 END IF;
311 I := oe_order_util.g_line_adj_tbl.NEXT(I);
312
313 END LOOP;
314
315 END IF;
316
317 <<HANDLE_DBI_REC>>
318
319 j:= 0;
320
321 IF (l_dbi_rec.line_id.count > 0 ) THEN -- Bug # 5022517
322
323 FOR i IN l_dbi_rec.line_id.FIRST..l_dbi_rec.line_id.LAST LOOP
324
325 BEGIN
326 IF l_debug_level > 0 THEN
327 oe_debug_pub.add(' Trying to lock the ONT_DBI_CHANGE_LOG');
328 END IF;
329
330 -- Check if the record exists in ONT_DBI_CHANGE_LOG.
331 -- If yes then first take a lock on that record.
332
333 SELECT 'Y'
334 INTO l_dbi_rec.rec_exists_flag(i)
335 FROM ONT_DBI_CHANGE_LOG
336 --- WHERE header_id = l_header_id bug 7319732 ,7347663
337 WHERE header_id=l_dbi_rec.header_id(i) ---bug 7319732 ,7347663
338 AND line_id = l_dbi_rec.line_id(i)
339 AND set_of_books_id = l_set_of_books_id
340 AND currency_code = l_currency_code
341 FOR UPDATE;
342
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 IF l_debug_level > 0 THEN
346 oe_debug_pub.add(' Record does not exist');
347 END IF;
348 null;
349 WHEN TOO_MANY_ROWS THEN
350 IF l_debug_level > 0 THEN
351 oe_debug_pub.add(' Multiple records exists');
352 END IF;
353 l_dbi_rec.rec_exists_flag(i) := 'Y';
354 END;
355
356 -- If record doesn't exists then mark it for CREATE
357 IF l_dbi_rec.rec_exists_flag(i) = 'N' THEN
358 j := j + 1;
359 l_dbi_create_rec.header_id.EXTEND ; ---bug 7319732 ,7347663
360 l_dbi_create_rec.line_id.EXTEND;
361 l_dbi_create_rec.last_update_date.EXTEND;
362 l_dbi_create_rec.line_id(j) := l_dbi_rec.line_id(i);
363 l_dbi_create_rec.last_update_date(j) := l_dbi_rec.last_update_date(i);
364 l_dbi_create_rec.header_id(j):=l_dbi_rec.header_id(i) ; ---bug 7319732,7347663
365 IF l_debug_level > 0 THEN
366 oe_debug_pub.add(' Creating new record ' || j);
367 oe_debug_pub.add(' Line_Id is ' || l_dbi_create_rec.line_id(j));
368 END IF;
369 END IF;
370
371 END LOOP;
372
373 END IF; -- Bug # 5022517
374
375 IF l_dbi_create_rec.line_id.EXISTS(1) THEN
376 -- Create records in ONT_DBI_CHANGE_LOG
377 ---changed l_header_id to l_dbi_create_rec.header_id(j) --bug 7319732,7347663
378 IF l_debug_level > 0 THEN
379 oe_debug_pub.add('Inserting new records ' || l_dbi_create_rec.line_id.COUNT );
380 END IF;
381 FORALL i IN 1..l_dbi_create_rec.line_id.COUNT
382 INSERT INTO ONT_DBI_CHANGE_LOG
383 (HEADER_ID
384 ,LINE_ID
385 ,SET_OF_BOOKS_ID
386 ,CURRENCY_CODE
387 ,LAST_UPDATE_DATE
388 )
389 VALUES
390 (l_dbi_create_rec.header_id(i)
391 ,l_dbi_create_rec.line_id(i)
392 ,l_set_of_books_id
393 ,l_currency_code
394 ,l_dbi_create_rec.last_update_date(i)
395 );
396 END IF;
397 --removed l_dbi_create_rec.header_id(j) in above update bug 7647650,7562662
398 -- If all records are new then no need to execute UPDATE.
399 IF j <> l_dbi_rec.line_id.COUNT THEN
400 IF l_debug_level > 0 THEN
401 oe_debug_pub.add('Updating existing records '|| l_dbi_rec.line_id.COUNT );
402 END IF;
403
404 FORALL i in 1..l_dbi_rec.line_id.COUNT
405 UPDATE ONT_DBI_CHANGE_LOG
406 SET LAST_UPDATE_DATE = SYSDATE
407 --WHERE HEADER_ID = l_header_id
408 WHERE HEADER_ID = l_dbi_rec.header_id(i) --bug 7319732,7347663
409 AND LINE_ID = l_dbi_rec.line_id(i)
410 AND SET_OF_BOOKS_ID = l_set_of_books_id
411 AND CURRENCY_CODE = l_currency_code
412 AND l_dbi_rec.rec_exists_flag(i) = 'Y';
413
414 END IF;
415
416 EXCEPTION
417 WHEN NO_DATA_FOUND THEN
418 IF l_debug_level > 0 THEN
419 oe_debug_pub.add( 'NO DATA FOUND' , 1 ) ;
420 END IF;
421 x_return_status := FND_API.G_RET_STS_ERROR;
422 RAISE FND_API.G_EXC_ERROR;
423 WHEN OTHERS THEN
424 IF l_debug_level > 0 THEN
425 oe_debug_pub.add( 'In Others of Update_DBI_Log' , 1 ) ;
426 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
427 END IF;
428 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
429 THEN
430 OE_MSG_PUB.Add_Exc_Msg
431 (G_PKG_NAME
432 ,'Update_DBI_log');
433 END IF;
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
436
437 END Update_DBI_Log;
438
439 END OE_DBI_UTIL;
[Home] [Help]