1 PACKAGE BODY OE_ORDER_UTIL AS
2 /* $Header: OEXUORDB.pls 120.5 2005/12/23 16:17:36 btea ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_ORDER_UTIL';
6
7 /*lchen*/
8 -- Constants added for notification framework
9 G_MAX_REQUESTS NUMBER := 10000;
10
11
12 -- GET_ATTRIBUTE_NAME
13 -- Returns the translated display name of the attribute from the AK
14 -- dictionary based on the attribute code
15 -- Use this function to resolve message tokens that display attribute
16 -- names.
17 ---------------------------------------------------------------
18 FUNCTION GET_ATTRIBUTE_NAME
19 ( p_attribute_code IN VARCHAR2
20 )
21 RETURN VARCHAR2
22 IS
23 -- Fix bug#1349549:
24 -- Increased l_attribute_name length to 240 as length
25 -- of column - NAME on AK_ATTRIBUTES_VL was increased
26 l_attribute_name VARCHAR2(240);
27 BEGIN
28
29 -- Bug 2648277 => NAME column is not translatable any more.
30 -- Use ATTRIBUTE_LABEL_LONG column, this is translated.
31 SELECT AK.ATTRIBUTE_LABEL_LONG
32 INTO l_attribute_name
33 FROM AK_ATTRIBUTES_VL AK
34 WHERE ak.attribute_code = upper(p_attribute_code)
35 AND ak.attribute_application_id = 660;
36
37 RETURN(l_attribute_name);
38
39 EXCEPTION
40 When Others Then
41 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
42 THEN
43 OE_MSG_PUB.Add_Exc_Msg
44 ( G_PKG_NAME
45 , 'GET_ATTRIBUTE_NAME'
46 );
47 END IF;
48 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
49 END GET_ATTRIBUTE_NAME;
50
51 ---------------------------------------------------------------
52 PROCEDURE LOCK_ORDER_OBJECT
53 (p_header_id IN NUMBER
54 ,x_return_status OUT NOCOPY VARCHAR2
55
56 )
57 IS
58 CURSOR header IS
59 SELECT header_id
60 FROM OE_ORDER_HEADERS
61 WHERE HEADER_ID = p_header_id
62 FOR UPDATE NOWAIT;
63 CURSOR lines IS
64 SELECT line_id
65 FROM OE_ORDER_LINES
66 WHERE HEADER_ID = p_header_id
67 FOR UPDATE NOWAIT;
68 CURSOR price_adjustments IS
69 SELECT price_adjustment_id
70 FROM OE_PRICE_ADJUSTMENTS
71 WHERE HEADER_ID = p_header_id
72 FOR UPDATE NOWAIT;
73 CURSOR sales_credits IS
74 SELECT sales_credit_id
75 FROM OE_SALES_CREDITS
76 WHERE HEADER_ID = p_header_id
77 FOR UPDATE NOWAIT;
78 BEGIN
79 SAVEPOINT Lock_Order_Object;
80
81 x_return_status := FND_API.G_RET_STS_SUCCESS;
82
83 -- Lock order header
84 OPEN header;
85 CLOSE header;
86
87 -- Lock all the lines in this order
88 OPEN lines;
89 CLOSE lines;
90
91 -- Lock all the price adjustments for this order
92 OPEN price_adjustments;
93 CLOSE price_adjustments;
94
95 -- Lock all the sales credits for this order
96 OPEN sales_credits;
97 CLOSE sales_credits;
98
99 EXCEPTION
100 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
101 x_return_status := FND_API.G_RET_STS_ERROR;
102 FND_MESSAGE.SET_NAME('ONT','OE_ORDER_OBJECT_LOCKED');
103 OE_MSG_PUB.ADD;
104 IF (header%ISOPEN) THEN
105 CLOSE header;
106 ELSIF (lines%ISOPEN) THEN
107 CLOSE lines;
108 ELSIF (price_adjustments%ISOPEN) THEN
109 CLOSE price_adjustments;
110 ELSIF (sales_credits%ISOPEN) THEN
111 CLOSE sales_credits;
112 END IF;
113 ROLLBACK TO Lock_Order_Object;
114 WHEN OTHERS THEN
115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
116 IF FND_MSG_PUB.Check_Msg_Level
117 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
118 THEN
119 OE_MSG_PUB.Add_Exc_Msg
120 ( G_PKG_NAME
121 , 'Lock_Order_Object'
122 );
123 END IF;
124 IF (header%ISOPEN) THEN
125 CLOSE header;
126 ELSIF (lines%ISOPEN) THEN
127 CLOSE lines;
128 ELSIF (price_adjustments%ISOPEN) THEN
129 CLOSE price_adjustments;
130 ELSIF (sales_credits%ISOPEN) THEN
131 CLOSE sales_credits;
132 END IF;
133 ROLLBACK TO Lock_Order_Object;
134 END LOCK_ORDER_OBJECT;
135
136
137 -- Update_Global_Picture
138 -- This procedure takes in the entity to be updated and inserts or updates the
139 -- global old and new tables holding that entity information for the order feedback
140 -- notification processes.
141 -- This routine should only be updating the global tables if the order is booked.
142 -- We should always be passed:
143 -- 1- id for the entity to be updated,
144 -- 2- old record for the entity to be updated, and
145 -- 3- new record for the entity to be updated.
146 -- If the action is insert, the old record passed will be empty.
147 -- If the action is delete, the new record passed will be empty.
148
149 PROCEDURE Update_Global_Picture
150 (
151 p_Upd_New_Rec_If_Exists IN BOOLEAN := TRUE
152 , p_Header_Rec IN OE_Order_Pub.Header_Rec_Type := NULL
153 , p_Line_Rec IN OE_Order_Pub.Line_Rec_Type := NULL
154 , p_Hdr_Scr_Rec IN OE_Order_Pub.Header_Scredit_Rec_Type := NULL
155 , p_Hdr_Adj_Rec IN OE_Order_Pub.Header_Adj_Rec_Type := NULL
156 , p_Line_Adj_Rec IN OE_Order_Pub.Line_Adj_Rec_Type := NULL
157 , p_Line_Scr_Rec IN OE_Order_Pub.Line_Scredit_Rec_Type := NULL
158 , p_Lot_Serial_Rec IN OE_Order_Pub.Lot_Serial_Rec_Type := NULL
159 , p_old_Header_Rec IN OE_Order_Pub.Header_Rec_Type := NULL
160 , p_old_Line_Rec IN OE_Order_Pub.Line_Rec_Type := NULL
161 , p_old_Hdr_Scr_Rec IN OE_Order_Pub.Header_Scredit_Rec_Type := NULL
162 , p_old_Hdr_Adj_Rec IN OE_Order_Pub.Header_Adj_Rec_Type := NULL
163 , p_old_Line_Adj_Rec IN OE_Order_Pub.Line_Adj_Rec_Type := NULL
164 , p_old_Line_Scr_Rec IN OE_Order_Pub.Line_Scredit_Rec_Type := NULL
165 , p_old_Lot_Serial_Rec IN OE_Order_Pub.Lot_Serial_Rec_Type := NULL
166 , p_header_id IN NUMBER := NULL
167 , p_line_id IN NUMBER := NULL
168 , p_hdr_scr_id IN NUMBER := NULL
169 , p_line_scr_id IN NUMBER := NULL
170 , p_hdr_adj_id IN NUMBER := NULL
171 , p_line_adj_id IN NUMBER := NULL
172 , p_lot_serial_id IN NUMBER := NULL
173 , x_index OUT NOCOPY NUMBER
174
175 , x_return_status OUT NOCOPY VARCHAR2)
176
177
178 IS
179 l_ind PLS_INTEGER;
180 l_result VARCHAR2(30);
181 l_return_stat VARCHAR2(1);
182 l_old_line_rec OE_Order_Pub.Line_Rec_Type;
183 l_old_Header_Rec OE_Order_Pub.Header_Rec_Type;
184 l_old_Hdr_Scr_Rec OE_Order_Pub.Header_Scredit_Rec_Type;
185 l_old_Hdr_Adj_Rec OE_Order_Pub.Header_Adj_Rec_Type;
186 l_old_Line_Adj_Rec OE_Order_Pub.Line_Adj_Rec_Type;
187 l_old_Line_Scr_Rec OE_Order_Pub.Line_Scredit_Rec_Type;
188 l_old_Lot_Serial_Rec OE_Order_Pub.Lot_Serial_Rec_Type;
189
190 -- Begin Audit/Versioning Changes
191 l_reason_existed BOOLEAN := FALSE;
192 l_change_reason VARCHAR2(30);
193 l_change_comments VARCHAR2(2000);
194 -- End Audit/Versioning Changes
195 --
196 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
197 --
198 BEGIN
199 x_return_status := FND_API.G_RET_STS_SUCCESS;
200
201 IF l_debug_level > 0 THEN
202 oe_debug_pub.add( 'ENTERING OE_ORDER_UTIL.UPDATE_GLOBAL_PICTURE' , 1 ) ;
203 END IF;
204
205 /* The global entities will be populated for booked orders */
206 /* coming in via Process Order or for entered orders via the GUI */
207 /* or at 11.5.10 or higher (for versioning changes) */
208
209 IF NOT (OE_GLOBALS.G_UI_FLAG ) THEN
210 IF l_debug_level > 0 THEN
211 oe_debug_pub.add( 'UI FLAG IS FALSE' , 1 ) ;
212 END IF;
213 end if;
214
215 -- check for code set level, Update_Global_Picture is at OM Pack H level
216
217 IF l_debug_level > 0 THEN
218 oe_debug_pub.add( 'CODE_RELEASE_LEVEL='|| OE_CODE_CONTROL.CODE_RELEASE_LEVEL , 1 ) ;
219 END IF;
220
221 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508'
222 THEN
223
224 IF p_header_id is not NULL THEN
225 IF l_debug_level > 0 THEN
226 oe_debug_pub.add( 'GETTING THE CACHE' || P_HEADER_ID ) ;
227 END IF;
228 OE_Order_Cache.Load_Order_Header(p_header_id);
229 END IF;
230
231 IF l_debug_level > 0 THEN
232 oe_debug_pub.add( 'CACHED VALUE' || OE_ORDER_CACHE.G_HEADER_REC.BOOKED_FLAG ) ;
233 END IF;
234 IF (oe_order_cache.g_header_rec.booked_flag = 'Y') OR
235 (OE_GLOBALS.G_UI_FLAG ) OR
236 OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
237
238 -- If the order is booked, then do the logic for each entity as follows:
239 -- 1- search old table for this line
240 -- and if it's not there, query it and add to global picture
241 -- 2- search new table for this line
242 -- and if it is there, update the record and return the index
243 -- and if it's not there, query it and add to global picture
244
245 IF(p_header_id is not null) THEN
246 IF l_debug_level > 0 THEN
247 oe_debug_pub.add( 'P_HEADER_ID=' || P_HEADER_ID , 1 ) ;
248 END IF;
249
250 IF g_old_header_rec.header_id=FND_API.G_MISS_NUM OR
251 g_old_header_rec.header_id is NULL THEN
252
253 IF p_old_header_rec.header_id=FND_API.G_MISS_NUM OR
254 P_old_header_rec.header_id is NULL THEN
255
256 IF p_header_Rec.header_id is not null and
257 p_header_Rec.operation = oe_globals.g_opr_create THEN
258 IF l_debug_level > 0 THEN
259
260 oe_debug_pub.add( 'P_HEADER_REC.HEADER_ID=' || P_HEADER_REC.HEADER_ID , 1 ) ;
261 END IF;
262 g_old_header_rec := p_header_Rec;
263 IF l_debug_level > 0 THEN
264 oe_debug_pub.add( 'JPN: OLD HEADER GLOBAL PIC UPDATED' ) ;
265 END IF;
266 ELSE
267 oe_header_util.query_row( p_header_id =>p_header_id,
268 x_header_rec => l_old_header_rec);
269 g_old_header_rec := l_old_header_rec;
270 IF l_debug_level > 0 THEN
271 oe_debug_pub.add( 'JPN: OLD HEADER GLOBAL PIC UPDATED AFTER QUERY' ) ;
272 END IF;
273 END IF;
274 ELSE
275 g_old_header_rec := p_old_header_rec;
276 IF l_debug_level > 0 THEN
277 oe_debug_pub.add( 'JPN: OLD HEADER GLOBAL PIC UPDATED IN THIS LOOP' ) ;
278 END IF;
279 END IF;
280 END IF;
281
282 IF l_debug_level > 0 THEN
283 oe_debug_pub.add( 'GLOBAL OLD HEADER BOOKED FLAG VALUE' || G_OLD_HEADER_REC.BOOKED_FLAG ) ;
284 END IF;
285
286 IF p_upd_new_rec_If_Exists THEN
287 /* Update the record in the new global table */
288 IF l_debug_level > 0 THEN
289 oe_debug_pub.add( 'INSIDE LOOP P_UPD_NEW_REC' ) ;
290 END IF;
291 -- Begin Audit/Versioning changes (retain old change reason)
292 IF (OE_CODE_CONTROL.Code_Release_Level >= '110510' AND
293 g_header_rec.change_reason IS NOT NULL AND
294 g_header_rec.change_reason <> FND_API.G_MISS_CHAR) THEN
295 l_reason_existed := TRUE;
296 l_change_reason := g_header_rec.change_reason;
297 l_change_comments := g_header_rec.change_comments;
298 END IF;
299 -- End Audit/Versioning changes
300
301 g_header_rec := p_header_rec;
302
303 -- Begin Audit/Versioning changes
304 IF (l_reason_existed) THEN
305 g_header_rec.change_reason := l_change_reason;
306 g_header_rec.change_comments := l_change_comments;
307 l_reason_existed := FALSE;
308 END IF;
309 -- End Audit/Versioning changes
310
311 IF l_debug_level > 0 THEN
312 oe_debug_pub.add( 'GLOBAL HEADER BOOKED FLAG VALUE' || G_HEADER_REC.BOOKED_FLAG ) ;
313 oe_debug_pub.add( 'GLOBAL HEADER REC OPERATION' || G_HEADER_REC.OPERATION ) ;
314 END IF;
315 END IF; -- update flag is set
316
317 /* return the index value */
318 x_index:=1;
319
320 IF l_debug_level > 0 THEN
321 oe_debug_pub.add( ' OD GLOBAL HEADER_ID= '|| G_OLD_HEADER_REC.HEADER_ID , 1 ) ;
322 oe_debug_pub.add( ' NEW GLOBAL HEADER_ID= '|| G_HEADER_REC.HEADER_ID , 1 ) ;
323 END IF;
324
325 END IF; -- header_id is not null
326
327 IF (p_hdr_scr_id is not null) THEN
328 IF l_debug_level > 0 THEN
329 oe_debug_pub.add( 'P_HDR_SCR_ID=' || P_HDR_SCR_ID , 1 ) ;
330 END IF;
331
332 /* search the old global header sales credits table */
333 Return_Glb_Ent_Index(
334 OE_GLOBALS.G_ENTITY_HEADER_SCREDIT,
335 p_hdr_scr_id,
336 l_ind,
337 l_result,
338 l_return_stat);
339
340 IF l_debug_level > 0 THEN
341 oe_debug_pub.add( 'INDEX=' || L_IND , 1 ) ;
342 oe_debug_pub.add( 'L_RETURN_STATUS =' || L_RETURN_STAT , 1 ) ;
343 oe_debug_pub.add( 'L_RESULT =' || L_RESULT , 1 ) ;
344 END IF;
345
346 IF l_result = FND_API.G_FALSE THEN
347
348 IF p_old_hdr_scr_rec.sales_credit_id = FND_API.G_MISS_NUM OR
349 p_old_hdr_scr_rec.sales_credit_id is NULL THEN
350
351 IF p_hdr_scr_rec.sales_credit_id is not null and
352 p_hdr_scr_rec.operation = oe_globals.g_opr_create THEN
353 g_old_header_scredit_tbl(l_ind):= p_hdr_scr_rec;
354 IF l_debug_level > 0 THEN
355 oe_debug_pub.add( 'JPN: OLD HEADER SALES CREDITS GLOBAL PIC UPDATED' ) ;
356 END IF;
357 ELSE
358 OE_HEADER_SCREDIT_UTIL.Query_Row(p_sales_credit_id => p_hdr_scr_id,
359 x_header_scredit_rec =>l_old_hdr_scr_rec); g_old_header_scredit_tbl(l_ind) :=l_old_hdr_scr_rec;
360 IF l_debug_level > 0 THEN
361 oe_debug_pub.add( 'JPN: OLD HEADER SCREDIT GLOBAL PIC UPDATED AFTER QUERY' ) ;
362 END IF;
363 END IF;
364
365 ELSE
366 g_old_header_scredit_tbl(l_ind) := p_old_hdr_scr_rec;
367 END IF;
368 END IF;
369
370 IF p_upd_new_rec_If_Exists THEN
371 -- Begin Audit/Versioning changes (retain old change reason)
372 IF (OE_CODE_CONTROL.Code_Release_Level >= '110510' AND
373 g_header_scredit_tbl.exists(l_ind) AND
374 g_header_scredit_tbl(l_ind).change_reason IS NOT NULL AND
375 g_header_scredit_tbl(l_ind).change_reason <> FND_API.G_MISS_CHAR) THEN
376 l_reason_existed := TRUE;
377 l_change_reason := g_header_scredit_tbl(l_ind).change_reason;
378 l_change_comments := g_header_scredit_tbl(l_ind).change_comments;
379 END IF;
380 -- End Audit/Versioning changes
381
382 /* Update the record in the new global table */
383 g_header_scredit_tbl(l_ind) := p_hdr_scr_rec;
384
385 -- Begin Audit/Versioning changes
386 IF (l_reason_existed) THEN
387 g_header_scredit_tbl(l_ind).change_reason := l_change_reason;
388 g_header_scredit_tbl(l_ind).change_comments := l_change_comments;
389 l_reason_existed := FALSE;
390 END IF;
391 -- End Audit/Versioning changes
392
393 IF l_debug_level > 0 THEN
394 oe_debug_pub.add( 'GLOBAL HDR_SCR_REC OPERATION' || G_HEADER_SCREDIT_TBL ( L_IND ) .OPERATION ) ;
395 END IF;
396 END IF; -- update flag is set
397
398 /* return the index value */
399 x_index := l_ind;
400
401 IF l_debug_level > 0 THEN
402 oe_debug_pub.add( 'OLD SCR_ID ' || G_OLD_HEADER_SCREDIT_TBL ( L_IND ) .SALES_CREDIT_ID , 1 ) ;
403 oe_debug_pub.add( 'NEW SCR_ID' || G_HEADER_SCREDIT_TBL ( L_IND ) .SALES_CREDIT_ID , 1 ) ;
404 END IF;
405
406 END IF; -- hdr_scr_id is not null
407
408
409 IF (p_hdr_adj_id is not null) THEN
410 IF l_debug_level > 0 THEN
411 oe_debug_pub.add( 'P_HDR_ADJ_ID=' || P_HDR_ADJ_ID , 1 ) ;
412 END IF;
413
414 /* search the old global header adjustments table */
415 Return_Glb_Ent_Index(
416 OE_GLOBALS.G_ENTITY_HEADER_ADJ,
417 p_hdr_adj_id,
418 l_ind,
419 l_result,
420 l_return_stat);
421
422 IF l_debug_level > 0 THEN
423 oe_debug_pub.add( 'INDEX=' || L_IND , 1 ) ;
424 oe_debug_pub.add( 'L_RETURN_STATUS =' || L_RETURN_STAT , 1 ) ;
425 oe_debug_pub.add( 'L_RESULT =' || L_RESULT , 1 ) ;
426 END IF;
427
428 IF l_result = FND_API.G_FALSE THEN
429 IF p_old_hdr_adj_rec.price_adjustment_id = FND_API.G_MISS_NUM OR
430 p_old_hdr_adj_rec.price_adjustment_id is NULL THEN
431
432 IF p_hdr_adj_rec.price_adjustment_id is not null and
433 p_hdr_adj_rec.operation = oe_globals.g_opr_create THEN
434 g_old_header_adj_tbl(l_ind):= p_hdr_adj_rec;
435 IF l_debug_level > 0 THEN
436 oe_debug_pub.add( 'JPN: OLD HEADER ADJ GLOBAL PIC UPDATED' ) ;
437 END IF;
438 ELSE
439 OE_HEADER_ADJ_UTIL.Query_Row(p_price_adjustment_id => p_hdr_adj_id,
440 x_header_adj_rec =>l_old_hdr_adj_rec);
441 g_old_header_adj_tbl(l_ind):=l_old_hdr_adj_rec;
442 END IF;
443 ELSE
444 IF l_debug_level > 0 THEN
445 oe_debug_pub.add('Updating the old global table');
446 END IF;
447 g_old_header_adj_tbl(l_ind) := p_old_hdr_adj_rec;
448 END IF;
449 END IF;
450
451 IF p_upd_new_rec_If_Exists THEN
452 -- Begin Audit/Versioning changes (retain old change reason)
453 IF (OE_CODE_CONTROL.Code_Release_Level >= '110510' AND
454 g_header_adj_tbl.exists(l_ind) AND
455 g_header_adj_tbl(l_ind).change_reason_code IS NOT NULL AND
456 g_header_adj_tbl(l_ind).change_reason_code <> FND_API.G_MISS_CHAR) THEN
457 l_reason_existed := TRUE;
458 l_change_reason := g_header_adj_tbl(l_ind).change_reason_code;
459 l_change_comments := g_header_adj_tbl(l_ind).change_reason_text;
460 END IF;
461 -- End Audit/Versioning changes
462
463 /* Update the record in the new global table */
464 g_header_adj_tbl(l_ind) := p_hdr_adj_rec;
465
466 -- Begin Audit/Versioning changes
467 IF (l_reason_existed) THEN
468 g_header_adj_tbl(l_ind).change_reason_code := l_change_reason;
469 g_header_adj_tbl(l_ind).change_reason_text := l_change_comments;
470 l_reason_existed := FALSE;
471 END IF;
472 -- End Audit/Versioning changes
473
474 IF l_debug_level > 0 THEN
475 oe_debug_pub.add( 'GLOBAL HDR_ADJ_REC OPERATION' || G_HEADER_ADJ_TBL ( L_IND ) .OPERATION ) ;
476 END IF;
477 END IF; -- update flag is set
478
479 /* return the index value */
480 x_index := l_ind;
481
482 /* commented the debug statement for bug # 2919714 hashraf
483 IF l_debug_level > 0 THEN
484 oe_debug_pub.add( 'OLD HDR ADJ ID' || G_OLD_HEADER_ADJ_TBL ( L_IND ) .PRICE_ADJUSTMENT_ID , 1 ) ;
485 END IF;
486 */
487
488 END IF; -- adj_hdr_id is not null
489
490
491 IF (p_line_id is not null) THEN
492 IF l_debug_level > 0 THEN
493 oe_debug_pub.add( 'P_LINE_ID=' || P_LINE_ID , 1 ) ;
494 END IF;
495
496 /* search the old global line table */
497 Return_Glb_Ent_Index(
498 OE_GLOBALS.G_ENTITY_LINE,
499 p_line_id,
500 l_ind,
501 l_result,
502 l_return_stat);
503
504 IF l_debug_level > 0 THEN
505 oe_debug_pub.add( 'INDEX=' || L_IND , 1 ) ;
506 oe_debug_pub.add( 'L_RETURN_STATUS =' || L_RETURN_STAT , 1 ) ;
507 oe_debug_pub.add( 'L_RESULT =' || L_RESULT , 1 ) ;
508 END IF;
509
510 IF l_result = FND_API.G_FALSE then
511 IF p_old_line_rec.line_id = FND_API.G_MISS_NUM OR
512 p_old_line_rec.line_id is NULL THEN
513
514 IF p_line_rec.line_id is not null and
515 p_line_rec.operation = oe_globals.g_opr_create THEN
516 g_old_line_tbl(l_ind):= p_line_rec;
517 IF l_debug_level > 0 THEN
518 oe_debug_pub.add( 'JPN: OLD LINE GLOBAL PIC UPDATED' ) ;
519 END IF;
520 ELSE
521 OE_LINE_UTIL.Query_Row(p_line_id => p_line_id,
522 x_line_rec => l_old_line_rec);
523 g_old_line_tbl(l_ind):=l_old_line_rec;
524 IF l_debug_level > 0 THEN
525 oe_debug_pub.add( 'JPN: OLD LINE GLOBAL PIC UPDATED AFTER QUERY' ) ;
526 END IF;
527 END IF;
528
529 ELSE
530 g_old_line_tbl(l_ind) := p_old_line_rec;
531 END IF;
532 END IF;
533
534 IF p_upd_new_rec_If_Exists THEN
535 -- Begin Audit/Versioning changes (retain old change reason)
536 IF (OE_CODE_CONTROL.Code_Release_Level >= '110510' AND
537 g_line_tbl.exists(l_ind) AND
538 g_line_tbl(l_ind).change_reason IS NOT NULL AND
539 g_line_tbl(l_ind).change_reason <> FND_API.G_MISS_CHAR) THEN
540 l_reason_existed := TRUE;
541 l_change_reason := g_line_tbl(l_ind).change_reason;
542 l_change_comments := g_line_tbl(l_ind).change_comments;
543 END IF;
544 -- End Audit/Versioning changes
545
546 /* Update the record in the new global table */
547 g_line_tbl(l_ind) := p_line_rec;
548
549 -- Begin Audit/Versioning changes
550 IF (l_reason_existed) THEN
551 g_line_tbl(l_ind).change_reason := l_change_reason;
552 g_line_tbl(l_ind).change_comments := l_change_comments;
553 l_reason_existed := FALSE;
554 END IF;
555 -- End Audit/Versioning changes
556
557 IF l_debug_level > 0 THEN
558 oe_debug_pub.add( 'GLOBAL LINE REC OPERATION' || G_LINE_TBL ( L_IND ) .OPERATION ) ;
559 END IF;
560 END IF; -- update flag is set
561
562 /* return the index value */
563 x_index := l_ind;
564
565 IF l_debug_level > 0 THEN
566 oe_debug_pub.add( 'OLD LINE ID' || G_OLD_LINE_TBL ( L_IND ) .LINE_ID , 1 ) ;
567 END IF;
568
569 END IF; -- line_id is not null
570
571
572 IF (p_line_scr_id is not null) THEN
573 IF l_debug_level > 0 THEN
574 oe_debug_pub.add( 'P_LINE_SCR_ID=' || P_LINE_SCR_ID , 1 ) ;
575 END IF;
576
577 /* search the old global line sales credits table */
578 Return_Glb_Ent_Index(
579 OE_GLOBALS.G_ENTITY_LINE_SCREDIT,
580 p_line_scr_id,
581 l_ind,
582 l_result,
583 l_return_stat);
584
585 IF l_debug_level > 0 THEN
586 oe_debug_pub.add( 'INDEX=' || L_IND , 1 ) ;
587 oe_debug_pub.add( 'L_RETURN_STATUS =' || L_RETURN_STAT , 1 ) ;
588 oe_debug_pub.add( 'L_RESULT =' || L_RESULT , 1 ) ;
589 END IF;
590
591 IF l_result = FND_API.G_FALSE THEN
592 IF p_old_line_scr_rec.sales_credit_id = FND_API.G_MISS_NUM OR
593 p_old_line_scr_rec.sales_credit_id is NULL THEN
594
595 IF p_line_scr_rec.sales_credit_id is not null and
596 p_line_scr_rec.operation = oe_globals.g_opr_create THEN
597 g_old_line_scredit_tbl(l_ind):= p_line_scr_rec;
598 IF l_debug_level > 0 THEN
599 oe_debug_pub.add( 'JPN: OLD LINE SALES CREDITS GLOBAL PIC UPDATED' ) ;
600 END IF;
601 ELSE
602 OE_LINE_SCREDIT_UTIL.Query_Row(p_sales_credit_id =>p_line_scr_id,
603 x_line_scredit_rec =>l_old_line_scr_rec);
604 g_old_line_scredit_tbl(l_ind):=l_old_line_scr_rec;
605 END IF;
606 ELSE
607 g_old_line_scredit_tbl(l_ind) := p_old_line_scr_rec;
608
609 END IF;
610 END IF;
611
612 IF p_upd_new_rec_If_Exists THEN
613 -- Begin Audit/Versioning changes (retain old change reason)
614 IF (OE_CODE_CONTROL.Code_Release_Level >= '110510' AND
615 g_line_scredit_tbl.exists(l_ind) AND
616 g_line_scredit_tbl(l_ind).change_reason IS NOT NULL AND
617 g_line_scredit_tbl(l_ind).change_reason <> FND_API.G_MISS_CHAR) THEN
618 l_reason_existed := TRUE;
619 l_change_reason := g_line_scredit_tbl(l_ind).change_reason;
620 l_change_comments := g_line_scredit_tbl(l_ind).change_comments;
621 END IF;
622 -- End Audit/Versioning changes
623
624 /* Update the record in the new global table */
625 g_line_scredit_tbl(l_ind) := p_line_scr_rec;
626
627 -- Begin Audit/Versioning changes
628 IF (l_reason_existed) THEN
629 g_line_scredit_tbl(l_ind).change_reason := l_change_reason;
630 g_line_scredit_tbl(l_ind).change_comments := l_change_comments;
631 l_reason_existed := FALSE;
632 END IF;
633 -- End Audit/Versioning changes
634
635 IF l_debug_level > 0 THEN
636 oe_debug_pub.add( 'GLOBAL LINE_SCR_REC OPERATION' || G_LINE_SCREDIT_TBL ( L_IND ) .OPERATION ) ;
637 END IF;
638 END IF; -- update flag is set
639
640 /* return the index value */
641 x_index := l_ind;
642
643 IF l_debug_level > 0 THEN
644 oe_debug_pub.add( 'OLD LINE SCR ID' || G_OLD_LINE_SCREDIT_TBL ( L_IND ) .SALES_CREDIT_ID , 1 ) ;
645 oe_debug_pub.add( 'NEW LINE SCR ID' || G_LINE_SCREDIT_TBL ( L_IND ) .SALES_CREDIT_ID , 1 ) ;
646 END IF;
647
648 END IF; -- scr_line_id is not null
649
650
651 IF (p_line_adj_id is not null) THEN
652 IF l_debug_level > 0 THEN
653 oe_debug_pub.add( 'P_LINE_ADJ_ID=' || P_LINE_ADJ_ID , 1 ) ;
654 END IF;
655
656 /* search the old global line adjustments table */
657 Return_Glb_Ent_Index(
658 OE_GLOBALS.G_ENTITY_LINE_ADJ,
659 p_line_adj_id,
660 l_ind,
661 l_result,
662 l_return_stat);
663
664 IF l_debug_level > 0 THEN
665 oe_debug_pub.add( 'INDEX=' || L_IND , 1 ) ;
666 oe_debug_pub.add( 'L_RETURN_STATUS =' || L_RETURN_STAT , 1 ) ;
667 oe_debug_pub.add( 'L_RESULT =' || L_RESULT , 1 ) ;
668 oe_debug_pub.add( 'OPERATION =' || P_LINE_ADJ_REC.OPERATION , 1 ) ;
669 END IF;
670
671 IF l_result = FND_API.G_FALSE then
672 IF p_old_line_adj_rec.price_adjustment_id = FND_API.G_MISS_NUM OR
673 p_old_line_adj_rec.price_adjustment_id is NULL THEN
674
675 IF p_line_adj_rec.price_adjustment_id is not null and
676 p_line_adj_rec.operation = oe_globals.g_opr_create THEN
677 g_old_line_adj_tbl(l_ind):= p_line_adj_rec;
678 IF l_debug_level > 0 THEN
679 oe_debug_pub.add( 'JPN: OLD LINE ADJ GLOBAL PIC UPDATED' ) ;
680 END IF;
681 ELSE
682 OE_LINE_ADJ_UTIL.Query_Row(p_price_adjustment_id =>p_line_adj_id,
683 x_line_adj_rec =>l_old_line_adj_rec);
684 g_old_line_adj_tbl(l_ind):=l_old_line_adj_rec;
685 END IF;
686 ELSE
687 g_old_line_adj_tbl(l_ind) := p_old_line_adj_rec;
688 END IF;
689 END IF;
690
691 IF p_upd_new_rec_If_Exists THEN
692 -- Begin Audit/Versioning changes (retain old change reason)
693 IF (OE_CODE_CONTROL.Code_Release_Level >= '110510' AND
694 g_line_adj_tbl.exists(l_ind) AND
695 g_line_adj_tbl(l_ind).change_reason_code IS NOT NULL AND
696 g_line_adj_tbl(l_ind).change_reason_code <> FND_API.G_MISS_CHAR) THEN
697 l_reason_existed := TRUE;
698 l_change_reason := g_line_adj_tbl(l_ind).change_reason_code;
699 l_change_comments := g_line_adj_tbl(l_ind).change_reason_text;
700 END IF;
701 -- End Audit/Versioning changes
702
703 /* Update the record in the new global table */
704 g_line_adj_tbl(l_ind) := p_line_adj_rec;
705
706 -- Begin Audit/Versioning changes
707 IF (l_reason_existed) THEN
708 g_line_adj_tbl(l_ind).change_reason_code := l_change_reason;
709 g_line_adj_tbl(l_ind).change_reason_text := l_change_comments;
710 l_reason_existed := FALSE;
711 END IF;
712 -- End Audit/Versioning changes
713
714 IF l_debug_level > 0 THEN
715 oe_debug_pub.add( 'GLOBAL LINE_ADJ_REC OPERATION' || G_LINE_ADJ_TBL ( L_IND ) .OPERATION ) ;
716 END IF;
717 END IF; -- update flag is set
718
719 /* return the index value */
720 x_index := l_ind;
721
722 IF l_debug_level > 0 THEN
723 oe_debug_pub.add( 'OLD LINE ADJ ID' || G_OLD_LINE_ADJ_TBL ( L_IND ) .PRICE_ADJUSTMENT_ID , 1 ) ;
724 -- do not uncomment the line below...it will give no-data found error
725 -- oe_debug_pub.add( 'NEW LINE ADJ ID' || G_LINE_ADJ_TBL ( L_IND ) .PRICE_ADJUSTMENT_ID , 1 ) ;
726 END IF;
727
728 END IF; -- adj_line_id is not null
729
730
731 IF (p_lot_serial_id is not null) THEN
732 IF l_debug_level > 0 THEN
733 oe_debug_pub.add( 'P_LOT_SERIAL_ID=' || P_LOT_SERIAL_ID , 1 ) ;
734 END IF;
735
736 /* search the old global lot serial table */
737 Return_Glb_Ent_Index(
738 OE_GLOBALS.G_ENTITY_LOT_SERIAL,
739 p_lot_serial_id,
740 l_ind,
741 l_result,
742 l_return_stat);
743
744 IF l_debug_level > 0 THEN
745 oe_debug_pub.add( 'INDEX=' || L_IND , 1 ) ;
746 oe_debug_pub.add( 'L_RETURN_STATUS =' || L_RETURN_STAT , 1 ) ;
747 oe_debug_pub.add( 'L_RESULT =' || L_RESULT , 1 ) ;
748 END IF;
749
750 IF l_result = FND_API.G_FALSE then
751 IF p_old_lot_serial_rec.lot_serial_id = FND_API.G_MISS_NUM OR
752 p_old_lot_serial_rec.lot_serial_id is NULL THEN
753
754 IF p_lot_serial_rec.lot_serial_id is not null and
755 p_lot_serial_rec.operation = oe_globals.g_opr_create THEN
756 g_old_lot_serial_tbl(l_ind):= p_lot_serial_rec;
757 IF l_debug_level > 0 THEN
758 oe_debug_pub.add( 'JPN: OLD LOT SERIAL GLOBAL PIC UPDATED' ) ;
759 END IF;
760 ELSE
761 OE_LOT_SERIAL_UTIL.Query_Row(p_lot_serial_id =>p_lot_serial_id,
762 x_lot_serial_rec =>l_old_lot_serial_rec);
763 g_old_lot_serial_tbl(l_ind):= l_old_lot_serial_rec;
764 END IF;
765 ELSE
766 g_old_lot_serial_tbl(l_ind) := p_old_lot_serial_rec;
767 END IF;
768 END IF;
769
770 IF p_upd_new_rec_If_Exists THEN
771 /* Update the record in the new global table */
772 g_lot_serial_tbl(l_ind) := p_lot_serial_rec;
773 IF l_debug_level > 0 THEN
774 oe_debug_pub.add( 'GLOBAL LOT_SERIAL_REC OPERATION' || G_LOT_SERIAL_TBL ( L_IND ) .OPERATION ) ;
775 END IF;
776 END IF; -- update flag is set
777
778 /* return the index value */
779 x_index := l_ind;
780
781 IF l_debug_level > 0 THEN
782 oe_debug_pub.add( ' OLD LOT SERIAL ID' || G_OLD_LOT_SERIAL_TBL ( L_IND ) .LOT_SERIAL_ID , 1 ) ;
783 oe_debug_pub.add( 'NEW LOT SERIAL ID' || G_LOT_SERIAL_TBL ( L_IND ) .LOT_SERIAL_ID , 1 ) ;
784 END IF;
785
786 END IF; -- lot_serial_id is not null
787
788 IF l_debug_level > 0 THEN
789 oe_debug_pub.add( 'EXITING OE_ORDER_UTIL.UPDATE_GLOBAL_PICTURE' , 1 ) ;
790 END IF;
791
792 END IF; /* check for booked flag or g_ui_flag */
793 END IF; /* check for code set level*/
794
795 EXCEPTION
796
797 WHEN OTHERS THEN
798 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
799 THEN
800 OE_MSG_PUB.Add_Exc_Msg
801 (G_PKG_NAME
802 ,'Update_Global_picture');
803 END IF;
804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
805
806 END Update_Global_Picture;
807
808
809 -- Return_Glb_Ent_Index
810 -- This procedure, given an entity code and id, searches the new or old global tables
811 -- for the index.
812 -- If it exists in the global table specified, x_result = TRUE and location = x_index.
813 -- If it doesn't exist in the global table, x_result = FALSE and x_index holds the
814 -- location where it should be inserted.
815
816 Procedure Return_Glb_Ent_Index(
817 p_entity_code IN VARCHAR2,
818 p_entity_id IN NUMBER,
819 x_index OUT NOCOPY NUMBER,
820
821 x_result OUT NOCOPY VARCHAR2,
822
823 x_return_status OUT NOCOPY VARCHAR2 )
824
825 IS
826 l_ind PLS_INTEGER;
827 l_max_ind PLS_INTEGER;
828
829 --
830 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
831 --
832 BEGIN
833
834 x_return_status := FND_API.G_RET_STS_SUCCESS;
835 x_result := FND_API.G_FALSE;
836
837 l_ind := (mod(p_entity_id,100000) * G_MAX_REQUESTS)+1;
838 l_max_ind := l_ind + G_MAX_REQUESTS - 1;
839
840 IF l_debug_level > 0 THEN
841 oe_debug_pub.add( 'ENTERING OE_ORDER_UTIL.RETURN_GLB_ENT_INDEX' , 1 ) ;
842 END IF;
843
844 IF p_entity_code = OE_GLOBALS.G_ENTITY_LINE THEN
845
846 -- search until we find the first index position with a value
847 IF NOT g_old_line_tbl.Exists(l_ind) THEN
848 x_index := l_ind;
849 l_ind := g_old_line_tbl.Next(l_ind);
850 END IF;
851
852 -- search the index positions for our entity id, or where it should be placed
853 WHILE g_old_line_tbl.Exists(l_ind) AND l_ind <= l_max_ind LOOP
854
855 x_index := l_ind+1;
856
857 IF (g_old_line_tbl(l_ind).line_id = p_entity_id OR
858 g_old_line_tbl(l_ind).line_id IS NULL )
859 THEN
860 x_index := l_ind;
861 x_result := FND_API.G_TRUE;
862 EXIT;
863 END IF;
864
865 l_ind := g_old_line_tbl.Next(l_ind);
866 END LOOP;
867 -- end entity is line
868
869 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER_SCREDIT THEN
870
871 -- search until we find the first index position with a value
872 IF NOT g_old_header_scredit_tbl.Exists(l_ind) THEN
873 x_index := l_ind;
874 l_ind := g_old_header_scredit_tbl.Next(l_ind);
875 END IF;
876
877 -- search the index positions for our entity id, or where it should be placed
878 WHILE g_old_header_scredit_tbl.Exists(l_ind)
879 AND l_ind <= l_max_ind LOOP
880
881 x_index := l_ind+1;
882
883 IF (g_old_header_scredit_tbl(l_ind).sales_credit_id = p_entity_id OR
884 g_old_header_scredit_tbl(l_ind).sales_credit_id IS NULL)
885 THEN
886 x_index := l_ind;
887 x_result := FND_API.G_TRUE;
888 EXIT;
889 END IF;
890
891 l_ind := g_old_header_scredit_tbl.Next(l_ind);
892 END LOOP;
893 -- end entity is header sales credit
894
895 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE_SCREDIT THEN
896
897 -- search until we find the first index position with a value
898 IF NOT g_old_line_scredit_tbl.Exists(l_ind) THEN
899 x_index := l_ind;
900 l_ind := g_old_line_scredit_tbl.Next(l_ind);
901 END IF;
902
903 -- search the index positions for our entity id, or where it should be placed
904 WHILE g_old_line_scredit_tbl.Exists(l_ind)
905 AND l_ind <= l_max_ind LOOP
906
907 x_index := l_ind+1;
908
909 IF (g_old_line_scredit_tbl(l_ind).sales_credit_id = p_entity_id OR
910 g_old_line_scredit_tbl(l_ind).sales_credit_id IS NULL)
911 THEN
912 x_index := l_ind;
913 x_result := FND_API.G_TRUE;
914 EXIT;
915 END IF;
916
917 l_ind := g_old_line_scredit_tbl.Next(l_ind);
918 END LOOP;
919 -- end entity is line sales credit
920
921
922 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER_ADJ THEN
923
924 -- search until we find the first index position with a value
925 IF NOT g_old_header_adj_tbl.Exists(l_ind) THEN
926 x_index := l_ind;
927 l_ind := g_old_header_adj_tbl.Next(l_ind);
928 END IF;
929
930 -- search the index positions for our entity id, or where it should be placed
931 WHILE g_old_header_adj_tbl.Exists(l_ind)
932 AND l_ind <= l_max_ind LOOP
933
934 x_index := l_ind+1;
935
936 IF (g_old_header_adj_tbl(l_ind).price_adjustment_id= p_entity_id OR
937 g_old_header_adj_tbl(l_ind).price_adjustment_id IS NULL)
938 THEN
939 x_index := l_ind;
940 x_result := FND_API.G_TRUE;
941 EXIT;
942 END IF;
943
944 l_ind := g_old_header_adj_tbl.Next(l_ind);
945 END LOOP;
946 -- end entity is header adjustment
947
948
949 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE_ADJ THEN
950
951 -- search until we find the first index position with a value
952 IF NOT g_old_line_adj_tbl.Exists(l_ind) THEN
953 x_index := l_ind;
954 l_ind := g_old_line_adj_tbl.Next(l_ind);
955 END IF;
956
957 -- search the index positions for our entity id, or where it should be placed
958 WHILE g_old_line_adj_tbl.Exists(l_ind) AND l_ind <= l_max_ind LOOP
959 x_index := l_ind+1;
960
961 IF (g_old_line_adj_tbl(l_ind).price_adjustment_id = p_entity_id OR
962 g_old_line_adj_tbl(l_ind).price_adjustment_id IS NULL)
963 THEN
964 x_index := l_ind;
965 x_result := FND_API.G_TRUE;
966 EXIT;
967 END IF;
968
969 l_ind := g_old_line_adj_tbl.Next(l_ind);
970 END LOOP;
971 -- entity is line adjustment
972
973
974 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LOT_SERIAL THEN
975
976 -- search until we find the first index position with a value
977 IF NOT g_old_lot_serial_tbl.Exists(l_ind) THEN
978 x_index := l_ind;
979 l_ind := g_old_lot_serial_tbl.Next(l_ind);
980 END IF;
981
982 -- search the index positions for our entity id, or where it should be placed
983 WHILE g_old_lot_serial_tbl.Exists(l_ind) AND l_ind <= l_max_ind LOOP
984 x_index := l_ind+1;
985
986 IF (g_old_lot_serial_tbl(l_ind).lot_serial_id = p_entity_id OR
987 g_old_lot_serial_tbl(l_ind).lot_serial_id IS NULL)
988 THEN
989 x_index := l_ind;
990 x_result := FND_API.G_TRUE;
991 EXIT;
992 END IF;
993
994 l_ind := g_old_lot_serial_tbl.Next(l_ind);
995 END LOOP;
996 -- entity is lot/serial number
997 END IF; -- going through 7 entities
998
999 IF x_index > l_max_ind THEN
1000 FND_MESSAGE.SET_NAME('ONT','OE_MAX_REQUESTS_EXCEEDED');
1001 OE_MSG_PUB.ADD;
1002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1003 END IF;
1004
1005 IF l_debug_level > 0 THEN
1006 oe_debug_pub.add( 'EXITING OE_ORDER_UTIL.RETURN_GLB_ENT_INDEX' , 1 ) ;
1007 END IF;
1008
1009 EXCEPTION
1010
1011 WHEN OTHERS THEN
1012 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1013 THEN
1014 OE_MSG_PUB.Add_Exc_Msg
1015 (G_PKG_NAME
1016 ,'Return_Glb_Ent_Index');
1017 END IF;
1018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1019
1020
1021 END Return_Glb_Ent_Index;
1022
1023 Procedure Clear_Global_Picture(x_return_status OUT NOCOPY VARCHAR2)
1024
1025 IS
1026
1027 --
1028 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1029 --
1030 BEGIN
1031
1032 IF l_debug_level > 0 THEN
1033 oe_debug_pub.add( 'ENTERING PROCEDURE CLEAR_GLOBAL_STRUCTURE' ) ;
1034 END IF;
1035 G_Header_Rec := NULL;
1036 G_Old_Header_Rec := NULL;
1037 G_line_tbl.DELETE;
1038 G_old_line_tbl.DELETE;
1039 G_Header_Scredit_tbl.DELETE;
1040 G_Old_Header_Scredit_tbl.DELETE;
1041 G_Old_Line_Scredit_tbl.DELETE;
1042 G_Line_Scredit_tbl.DELETE;
1043 G_old_Header_Adj_tbl.DELETE;
1044 G_Header_Adj_tbl.DELETE;
1045 G_old_Line_Adj_tbl.DELETE;
1046 G_Line_Adj_tbl.DELETE;
1047 G_old_Lot_Serial_tbl.DELETE;
1048 G_Lot_Serial_tbl.DELETE;
1049 --Bug 4569284
1050 OE_GLOBALS.G_FTE_REINVOKE:=NULL;
1051 --Bug 4569284
1052 x_return_status := FND_API.G_RET_STS_SUCCESS;
1053
1054 EXCEPTION
1055
1056 WHEN OTHERS THEN
1057
1058 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1059 OE_MSG_PUB.Add_Exc_Msg
1060 (G_PKG_NAME
1061 ,'Clear_Global_Picture');
1062 END IF;
1063
1064 x_return_status := FND_API.G_RET_STS_ERROR;
1065
1066 End Clear_Global_Picture;
1067
1068 PROCEDURE Initialize_Access_List
1069 IS
1070 --
1071 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1072 --
1073 l_is_xdo_licensed varchar2(1);
1074
1075 BEGIN
1076 IF l_debug_level > 0 THEN
1077 oe_debug_pub.add( 'ENTERING OE_ORDER_UTIL.INITIALIZE_ACCESS_LIST' , 1 ) ;
1078 END IF;
1079 IF FND_FUNCTION.TEST('ONT_OEXOEORD_APPLY_HOLDS') THEN
1080 Add_Access('APPLY_HOLDS');
1081 END IF;
1082
1083 IF FND_FUNCTION.TEST('ONT_OEXOEORD_RELEASE_HOLDS') THEN
1084 Add_Access('RELEASE_HOLDS');
1085 END IF;
1086
1087 IF FND_FUNCTION.TEST('ONT_OEXOEORD_CANCEL_ORDER') THEN
1088 Add_Access('CANCEL_ORDER');
1089 END IF;
1090
1091 IF FND_FUNCTION.TEST('ONT_OEXOEORD_COPY_ORDER') THEN
1092 Add_Access('COPY');
1093 END IF;
1094 IF FND_FUNCTION.TEST('ONT_OEXOEORD_PRICE_ORDER') THEN
1095 Add_Access('PRICE_ORDER');
1096 END IF;
1097
1098 IF FND_FUNCTION.TEST('ONT_OEXOEORD_CALCULATE_TAX') THEN
1099 Add_Access('CALCULATE_TAX');
1100 END IF;
1101
1102 IF FND_FUNCTION.TEST('ONT_OEXOEORD_PROGRESS_ORDER') THEN
1103 Add_Access('PROGRESS_ORDER');
1104 END IF;
1105
1106 --datafix_begin
1107 IF FND_FUNCTION.TEST('ONT_OEXOEORD_RETRY_WF') THEN
1108 Add_Access('RETRY_WF');
1109 END IF;
1110
1111 IF FND_FUNCTION.TEST('ONT_OEXOEORD_PROCESS_MESSAGES') THEN
1112 Add_Access('PROCESS_MESSAGES');
1113 END IF;
1114 --datafix_end
1115
1116 IF FND_FUNCTION.TEST('ONT_OEXOEORD_CONFIGURATIONS') THEN
1117 Add_Access('CONFIGURATIONS');
1118 END IF;
1119
1120 IF FND_FUNCTION.TEST('ONT_OEXOEORD_SALES_CREDITS') THEN
1121 Add_Access('SALES_CREDITS');
1122 END IF;
1123
1124 IF FND_FUNCTION.TEST('ONT_OEXOEORD_CHARGES') THEN
1125 Add_Access('CHARGES');
1126 END IF;
1127
1128 IF FND_FUNCTION.TEST('ONT_OEXOEORD_BOOK_ORDER') THEN
1129 Add_Access('BOOK_ORDER');
1130 END IF;
1131
1132 IF FND_FUNCTION.TEST('ONT_OEXOEORD_NOTIFICATION') THEN
1133 Add_Access('NOTIFICATION');
1134 END IF;
1135
1136
1137 IF FND_FUNCTION.TEST('ONT_OEXOEORD_AUTHORIZE_PAYMENT') THEN
1138 Add_Access('AUTHORIZE_PAYMENT');
1139 END IF;
1140
1141 IF FND_FUNCTION.TEST('ONT_OEXOEORD_SCHEDULE') THEN
1142 Add_Access('SCHEDULE');
1143 END IF;
1144
1145 IF FND_FUNCTION.TEST('ONT_OEXOEORD_INSTALL_BASE') THEN
1146 Add_Access('INSTALL_BASE');
1147 END IF;
1148
1149 IF FND_FUNCTION.TEST('ONT_OEXOEORD_REMOVE_UCOMP') THEN
1150 Add_Access('REMOVE_UCOMP');
1151 END IF;
1152
1153 --Payment Receipt Report
1154 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >='110510' THEN
1155 IF FND_FUNCTION.TEST('ONT_OEXOEORD_PRINT_RECEIPT') THEN
1156 Add_Access('PAYMENT_RECEIPT');
1157 END IF;
1158 END IF;
1159 --Payment Receipt Report
1160
1161 --choose ship method
1162 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >='110510' THEN
1163 IF FND_FUNCTION.TEST('ONT_OEXOEFCH') THEN
1164 Add_Access('CHOOSE_SHIP_METHOD');
1165 END IF;
1166 END IF;
1167 --choose ship method
1168
1169 --IF OE_FEATURES_PVT.Is_Margin_Avail THEN
1170 --MRG BGN
1171 IF FND_FUNCTION.TEST('ONT_OEXOEORD_VIEW_MARGIN') THEN
1172 Add_Access('VIEW_MARGIN');
1173 ELSE
1174 IF l_debug_level > 0 THEN
1175 oe_debug_pub.add( 'NO PERMISSION TO VIEW MARGIN' ) ;
1176 END IF;
1177 END IF;
1178 --MRG END
1179 --END IF;
1180
1181 --Freight Rating Begin
1182 /*IF OE_FREIGHT_RATING_UTIL.IS_FREIGHT_RATING_AVAILABLE THEN
1183 Add_Access('GET_FREIGHT_RATES');
1184 END IF;*/
1185 --Freight Rating End
1186 --oe_debug_pub.add('Freight RATE :-(');
1187 IF FND_FUNCTION.TEST('ONT_OEXOEORD_FRTCOSTS') THEN
1188 Add_Access('VIEW_FREIGHT_COSTS');
1189 END IF;
1190
1191
1192 --ABH
1193 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' THEN
1194 IF FND_FUNCTION.TEST('OKC_CONTRACT_TERMS_FORMS') THEN
1195
1196 --Make a check to see if contracts has been licensed
1197 IF OE_Contracts_util.check_license() = 'Y' THEN
1198 Add_Access('CONTRACT_TERMS');
1199 END IF;
1200
1201 END IF;
1202
1203 IF FND_FUNCTION.TEST('ONT_PRINT') THEN
1204
1205 --Add the preview print only if XDO is licensed
1206 l_is_xdo_licensed := Oe_Globals.CHECK_PRODUCT_INSTALLED (p_application_id => 603); --Oracle XML Publisher
1207 IF l_is_xdo_licensed = 'Y' THEN
1208 Add_Access('PREVIEW_AGREEMENT');
1209 END IF;
1210
1211 END IF;
1212
1213 IF FND_FUNCTION.TEST('OKC_REPO_DOC_SUMMARY_FORMS') THEN
1214
1215 --Make a check to see if contracts has been licensed
1216 IF OE_Contracts_util.check_license() = 'Y' THEN
1217 Add_Access('CONTRACT_DOCUMENT');
1218 END IF;
1219
1220 END IF;
1221 END IF;
1222 --ABH
1223 --Customer Acceptance
1224 IF FND_FUNCTION.TEST('ONT_OEXOEORD_FULFILL_ACCEPT') THEN
1225 Add_Access('FULFILLMENT_ACCEPTANCE');
1226 END IF;
1227
1228
1229 OE_ORDER_UTIL.G_Access_List_Initialized:='Y';
1230 IF l_debug_level > 0 THEN
1231 oe_debug_pub.add( 'EXITING OE_ORDER_UTIL.INITIALIZE_ACCESS_LIST' , 1 ) ;
1232 END IF;
1233
1234 END Initialize_Access_List;
1235
1236 PROCEDURE Add_Access(Function_Name VARCHAR2)
1237 IS
1238 i number:=0;
1239 --
1240 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1241 --
1242 BEGIN
1243 IF l_debug_level > 0 THEN
1244 oe_debug_pub.add( 'ENTERING OE_ORDER_UTIL.ADD_ACCESS' , 1 ) ;
1245 END IF;
1246 IF OE_GLOBALS.G_ACCESS_List.Count=0 THEN
1247 OE_GLOBALS.G_Access_List(1):=Function_Name;
1248 ELSIF OE_GLOBALS.G_ACCESS_List.Count>0 THEN
1249 i:=OE_GLOBALS.G_ACCESS_List.Last+1;
1250 OE_GLOBALS.G_ACCESS_List(i):=Function_Name;
1251 END IF;
1252 IF l_debug_level > 0 THEN
1253 oe_debug_pub.add( 'EXITING OE_ORDER_UTIL.ADD_ACCESS' , 1 ) ;
1254 END IF;
1255 EXCEPTION
1256 When Others Then
1257 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1258 THEN
1259 OE_MSG_PUB.Add_Exc_Msg
1260 ( G_PKG_NAME
1261 , 'IS_ACTION_IN_ACCESS_LIST'
1262 );
1263 END IF;
1264
1265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1266 END Add_Access;
1267
1268 FUNCTION IS_ACTION_IN_ACCESS_LIST(Action_code in varchar2)
1269 RETURN BOOLEAN IS
1270 rg_count number;
1271 j number;
1272 exit_function exception;
1273
1274
1275 --
1276 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1277 --
1278 BEGIN
1279 IF l_debug_level > 0 THEN
1280 oe_debug_pub.add( 'ENTERING OE_ORDER_UTIL.IS_ACTION_IN_ACCESS_LIST' , 1 ) ;
1281 END IF;
1282 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >='110508' THEN
1283 IF OE_ORDER_UTIL.G_Access_List_Initialized IS NULL THEN
1284 Initialize_Access_List;
1285 END IF;
1286
1287 rg_count:=OE_GLOBALS.G_ACCESS_List.Last;
1288
1289 IF rg_count=0 THEN
1290 Return FALSE ;
1291 ELSE
1292 For j in 1..rg_count LOOP
1293 IF upper(OE_GLOBALS.G_Access_List(j))=upper(action_code) THEN
1294 return TRUE;
1295 END IF;
1296 END LOOP;
1297 END IF;
1298 ELSE
1299 Return(True);
1300 END IF;
1301 IF l_debug_level > 0 THEN
1302 oe_debug_pub.add( 'EXITING OE_ORDER_UTIL.IS_ACTION_IN_ACCESS_LIST' , 1 ) ;
1303 END IF;
1304 RAISE Exit_Function;
1305 EXCEPTION
1306 When Exit_Function Then
1307 Return FALSE;
1308 When Others Then
1309 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1310 THEN
1311 OE_MSG_PUB.Add_Exc_Msg
1312 ( G_PKG_NAME
1313 , 'IS_ACTION_IN_ACCESS_LIST'
1314 );
1315 END IF;
1316
1317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1318
1319 END IS_ACTION_IN_ACCESS_LIST;
1320
1321 PROCEDURE Get_Access_List
1322 (
1323 p_Access_List OUT NOCOPY OE_GLOBALS.ACCESS_LIST)
1324
1325 IS
1326
1327 BEGIN
1328 Initialize_Access_List;
1329 p_access_list:=OE_GLOBALS.G_ACCESS_LIST;
1330 END Get_Access_List;
1331
1332 Function Get_Precision(
1333 p_currency_code IN Varchar2 Default Null,
1334 p_header_id IN Number Default Null,
1335 p_line_id IN Number Default Null
1336 )
1337 RETURN BOOLEAN IS
1338 l_currency_code Varchar2(100);
1339 l_precision NUMBER;
1340 l_ext_precision NUMBER;
1341 l_min_acct_unit NUMBER;
1342 --
1343 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1344 --
1345 BEGIN
1346
1347 IF p_currency_code IS NULL AND p_header_id IS NOT NULL THEN
1348 SELECT TRANSACTIONAL_CURR_CODE
1349 INTO l_currency_code
1350 FROM OE_ORDER_HEADERS
1351 WHERE HEADER_ID=p_header_id;
1352 ELSIF p_header_id IS NULL AND p_currency_code IS NULL AND p_line_id IS NOT NULL THEN
1353 SELECT /*MOAC_SQL_CHANGES*/ OH.TRANSACTIONAL_CURR_CODE
1354 INTO l_currency_code
1355 FROM OE_ORDER_HEADERS OH, OE_ORDER_LINES_ALL OL
1356 WHERE OH.HEADER_ID=OL.HEADER_ID
1357 AND OL.LINE_ID=p_line_id;
1358 ELSIF p_currency_code IS NOT NULL THEN
1359 l_currency_code:=p_currency_code;
1360 END IF;
1361
1362 IF l_debug_level > 0 THEN
1363 OE_DEBUG_PUB.add('Currency Code : '||l_currency_code,5);
1364 END IF;
1365
1366 IF l_currency_code IS NOT NULL THEN
1367 FND_CURRENCY.GET_INFO(l_currency_code,
1368 l_precision,
1369 l_ext_precision,
1370 l_min_acct_unit
1371 );
1372 -- #2713025
1373 OE_ORDER_UTIL.G_Precision := l_precision;
1374 G_Header_Id:=p_header_id;
1375 G_line_id:=p_line_id;
1376 Return(TRUE);
1377 ELSE
1378 l_precision:=2;
1379 OE_ORDER_UTIL.G_Precision := l_precision;
1380 G_Header_Id:=p_header_id;
1381 G_line_id:=p_line_id;
1382 Return(TRUE);
1383 END IF;
1384 EXCEPTION WHEN NO_DATA_FOUND THEN
1385 l_precision:=2;
1386 G_Header_Id:=p_header_id;
1387 G_line_id:=p_line_id;
1388 Return(TRUE);
1389 WHEN TOO_MANY_ROWS THEN
1390 Return(FALSE);
1391 WHEN OTHERS THEN
1392 Return(FALSE);
1393 END GET_Precision;
1394
1395 END OE_ORDER_UTIL;