1 PACKAGE BODY OE_Versioning_Util AS
2 /* $Header: OEXUVERB.pls 120.9 2006/06/14 18:22:42 xding noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Versioning_Util';
5
6 G_VERSION_NUMBER NUMBER;
7
8 -- Forward declaration to delete created records from history
9 FUNCTION Delete_Created_Records
10 RETURN VARCHAR2
11 IS
12 i NUMBER;
13 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
14 l_temp_table num_tbl;
15 l_return_status VARCHAR2(30);
16 --
17 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
18 --
19 BEGIN
20
21 l_return_status := FND_API.G_RET_STS_SUCCESS;
22
23 IF l_debug_level > 0 THEN
24 oe_debug_pub.add('Entering Delete_Created_Records ',1);
25 END IF;
26
27 -- Get created records
28
29 i := oe_order_util.g_old_line_tbl.FIRST; -- get subscript of first element
30 WHILE i IS NOT NULL LOOP
31 if oe_order_util.g_old_line_tbl(i).operation = OE_GLOBALS.G_OPR_CREATE then
32 l_temp_table(l_temp_table.count + 1) := oe_order_util.g_old_line_tbl(i).line_id;
33 end if;
34 i := oe_order_util.g_old_line_tbl.NEXT(i); -- get subscript of next element
35 END LOOP;
36
37 if l_temp_table.count > 0 then
38 FORALL i in 1..l_temp_table.COUNT
39 DELETE FROM OE_ORDER_LINES_HISTORY
40 WHERE line_id = l_temp_table(i);
41 l_temp_table.DELETE;
42 end if;
43
44 i := oe_order_util.g_old_header_adj_tbl.FIRST;
45 WHILE i IS NOT NULL LOOP
46 if oe_order_util.g_old_header_adj_tbl(i).operation = OE_GLOBALS.G_OPR_CREATE then
47 l_temp_table(l_temp_table.count + 1) := oe_order_util.g_old_header_adj_tbl(i).price_adjustment_id;
48 end if;
49 i := oe_order_util.g_old_header_adj_tbl.NEXT(i);
50 END LOOP;
51
52 i := oe_order_util.g_old_line_adj_tbl.FIRST;
53 WHILE i IS NOT NULL LOOP
54 if oe_order_util.g_old_line_adj_tbl(i).operation = OE_GLOBALS.G_OPR_CREATE then
55 l_temp_table(l_temp_table.count + 1) := oe_order_util.g_old_line_adj_tbl(i).price_adjustment_id;
56 end if;
57 i := oe_order_util.g_old_line_adj_tbl.NEXT(i);
58 END LOOP;
59
60 if l_temp_table.count > 0 then
61 FORALL i in 1..l_temp_table.COUNT
62 DELETE FROM OE_PRICE_ADJS_HISTORY
63 WHERE price_adjustment_id = l_temp_table(i);
64 l_temp_table.DELETE;
65 end if;
66
67 i := oe_order_util.g_old_header_scredit_tbl.FIRST;
68 WHILE i IS NOT NULL LOOP
69 if oe_order_util.g_old_header_scredit_tbl(i).operation = OE_GLOBALS.G_OPR_CREATE then
70 l_temp_table(l_temp_table.count + 1) := oe_order_util.g_old_header_scredit_tbl(i).sales_credit_id;
71 end if;
72 i := oe_order_util.g_old_header_scredit_tbl.NEXT(i);
73 END LOOP;
74
75 i := oe_order_util.g_old_line_scredit_tbl.FIRST;
76 WHILE i IS NOT NULL LOOP
77 if oe_order_util.g_old_line_scredit_tbl(i).operation = OE_GLOBALS.G_OPR_CREATE then
78 l_temp_table(l_temp_table.count + 1) := oe_order_util.g_old_line_scredit_tbl(i).sales_credit_id;
79 end if;
80 i := oe_order_util.g_old_line_scredit_tbl.NEXT(i);
81 END LOOP;
82
83 if l_temp_table.count > 0 then
84 FORALL i in 1..l_temp_table.COUNT
85 DELETE FROM OE_SALES_CREDIT_HISTORY
86 WHERE sales_credit_id = l_temp_table(i);
87 l_temp_table.DELETE;
88 end if;
89
90 IF l_debug_level > 0 THEN
91 oe_debug_pub.add('Exiting Delete_Created_Records ',1);
92 END IF;
93
94 RETURN l_return_status;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 ROLLBACK TO SAVEPOINT Perform_Versioning;
99
100 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
101 OE_MSG_PUB.Add_Exc_Msg
102 ( G_PKG_NAME
103 , 'Delete_Created_Records'
104 );
105 END IF;
106
107 RETURN l_return_status;
108 END Delete_Created_Records;
109
110 Procedure Execute_Versioning_Request(
111 p_header_id IN NUMBER,
112 p_document_type IN VARCHAR2,
113 p_changed_attribute IN VARCHAR2 default null,
114 x_msg_count OUT NOCOPY NUMBER,
115 x_msg_data OUT NOCOPY VARCHAR2,
116 x_return_status OUT NOCOPY VARCHAR2
117 )
118 IS
119 l_return_status VARCHAR2(30);
120 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
121
122 BEGIN
123 IF l_debug_level > 0 THEN
124 oe_debug_pub.add('Executing Versioning/Audit delayed request');
125 END IF;
126
127 l_return_status := FND_API.G_RET_STS_SUCCESS;
128
129 IF OE_GLOBALS.G_ROLL_VERSION <> 'N' THEN
130 Perform_Versioning (p_header_id => p_header_id,
131 p_document_type => p_document_type,
132 p_changed_attribute => p_changed_attribute,
133 x_msg_count => x_msg_count,
134 x_msg_data => x_msg_data,
135 x_return_status => l_return_status);
136
137 --takintoy Version articles
138 If l_return_status = FND_API.G_RET_STS_SUCCESS
139 AND OE_Code_Control.Get_Code_Release_Level >= '110510'
140 Then
141 OE_Contracts_Util.Version_Articles(
142 p_api_version => 1,
143 p_doc_type => OE_Contracts_Util.G_SO_DOC_TYPE,
144 p_doc_id => p_header_id,
145 -- p_version_number => OE_ORDER_UTIL.G_OLD_HEADER_REC.VERSION_NUMBER ,
146 p_version_number => G_VERSION_NUMBER,
147 x_return_status => l_return_status,
148 x_msg_data => x_msg_data,
149 x_msg_count => x_msg_count);
150 End If;
151 ELSE
152 --Perform Audit Trail
153 Record_Changed_Records(
154 p_changed_attribute => p_changed_attribute,
155 x_return_status => l_return_status);
156 IF (NOT OE_Versioning_Util.Reset_Globals) THEN
157 l_return_status := FND_API.G_RET_STS_ERROR;
158 END IF;
159 END IF;
160
161 IF l_debug_level > 0 THEN
162 oe_debug_pub.add('End Versioning/Audit Request '|| l_return_status);
163 END IF;
164
165 x_return_status := l_return_status;
166
167 END Execute_Versioning_Request;
168
169
170 Procedure Perform_Versioning (
171 p_header_id IN NUMBER,
172 p_document_type IN VARCHAR2,
173 p_changed_attribute IN VARCHAR2 := NULL,
174 x_msg_count OUT NOCOPY NUMBER,
175 x_msg_data OUT NOCOPY VARCHAR2,
176 x_return_status OUT NOCOPY VARCHAR2
177 )
178 IS
179
180 l_return_status VARCHAR2(30);
181 l_lock_control NUMBER;
182 l_version_number NUMBER;
183 l_entity_id NUMBER;
184 l_reason_id NUMBER;
185 l_version_flag VARCHAR2(1);
186 l_phase_change_flag VARCHAR2(1);
187 i NUMBER;
188
189 --
190 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
191 --
192 BEGIN
193
194 SAVEPOINT Perform_Versioning;
195
196 l_return_status := FND_API.G_RET_STS_SUCCESS;
197 l_version_flag := 'N';
198 l_phase_change_flag := 'N';
199
200 IF l_debug_level > 0 THEN
201 oe_debug_pub.add('Entering Perform_Versioning ',1);
202 END IF;
203
204 IF (OE_GLOBALS.G_ROLL_VERSION = 'PV') THEN
205 l_version_flag := 'Y';
206 l_phase_change_flag := 'Y';
207 IF (p_document_type <> 'BLANKETS') THEN
208 IF OE_ORDER_UTIL.g_old_header_rec.version_number IS NULL
209 OR OE_ORDER_UTIL.g_old_header_rec.version_number = FND_API.G_MISS_NUM THEN
210 select version_number into l_version_number from oe_order_headers_all where header_id = p_header_id;
211 ELSE
212 l_version_number := OE_ORDER_UTIL.g_old_header_rec.version_number;
213 END IF;
214 END IF;
215 ELSIF (OE_GLOBALS.G_ROLL_VERSION = 'P') THEN
216 l_version_flag := 'N';
217 l_phase_change_flag := 'Y';
218 ELSE
219 l_phase_change_flag := 'N';
220 l_version_flag := 'Y';
221 IF (p_document_type <> 'BLANKETS') THEN
222 IF OE_ORDER_UTIL.g_old_header_rec.version_number IS NULL
223 OR OE_ORDER_UTIL.g_old_header_rec.version_number = FND_API.G_MISS_NUM THEN
224 select version_number into l_version_number from oe_order_headers_all where header_id = p_header_id;
225 ELSE
226 l_version_number := OE_ORDER_UTIL.g_old_header_rec.version_number;
227 END IF;
228 END IF;
229 END IF;
230
231
232 IF (p_document_type = 'BLANKETS') THEN
233
234 OE_Blanket_Util.Record_Blanket_History(p_phase_change_flag => l_phase_change_flag, p_version_flag => l_version_flag, x_return_status => l_return_status);
235
236 IF (l_version_flag = 'Y') THEN
237
238 SELECT LOCK_CONTROL, VERSION_NUMBER
239 INTO l_lock_control, l_version_number
240 FROM OE_BLANKET_HEADERS_ALL
241 WHERE HEADER_ID = p_header_id;
242
243 IF l_version_number = OE_Blanket_Util.g_old_header_hist_rec.version_number THEN
244
245 UPDATE OE_BLANKET_HEADERS_ALL
246 SET VERSION_NUMBER = l_version_number + 1,
247 LOCK_CONTROL = l_lock_control + 1
248 WHERE HEADER_ID = p_header_id;
249
250 OE_GLOBALS.G_CASCADING_REQUEST_LOGGED := TRUE;
251 ELSIF l_version_number < OE_Blanket_Util.g_old_header_hist_rec.version_number THEN
252 FND_MESSAGE.SET_NAME('ONT','OE_BLKT_INVALID_REVISION_NUM');
253 OE_MSG_PUB.ADD;
254
255 RAISE FND_API.G_EXC_ERROR;
256 END IF;
257 END IF;
258
259 --Apply reason changes
260 IF OE_GLOBALS.G_CAPTURED_REASON = 'Y' AND
261 OE_GLOBALS.G_REASON_CODE <> FND_API.G_MISS_CHAR THEN
262 OE_Reasons_Util.Apply_Reason(
263 p_entity_code => 'BLANKET_HEADER',
264 p_entity_id => p_header_id,
265 p_header_id => p_header_id,
266 p_version_number => OE_Blanket_Util.g_old_header_hist_rec.version_number,
267 p_reason_type => 'CANCEL_CODE',
268 p_reason_code => OE_GLOBALS.G_REASON_CODE,
269 p_reason_comments => OE_GLOBALS.G_REASON_COMMENTS,
270 x_reason_id => l_reason_id,
271 x_return_status => l_return_status);
272
273 UPDATE OE_BLANKET_HEADERS_HIST
274 SET REASON_ID = l_reason_id
275 WHERE phase_change_flag = l_phase_change_flag
276 AND version_flag = l_version_flag
277 AND version_number = OE_Blanket_Util.g_old_header_hist_rec.version_number;
278
279 ELSIF OE_GLOBALS.G_CAPTURED_REASON = 'V' THEN
280 FND_MESSAGE.SET_NAME('ONT','OE_AUDIT_REASON_RQD');
281 OE_MSG_PUB.ADD;
282
283 RAISE FND_API.G_EXC_ERROR;
284 END IF;
285
286 -- Bug 5156668
287 -- This needs to be called only in the end, hence commenting
288 -- IF (NOT Reset_Globals) THEN
289 -- RAISE FND_API.G_EXC_ERROR;
290 -- END IF;
291
292 -- XDING: Commentting out this DOESNOT fix bug 5156668 but cause
293 -- regression issue 505564, so put this code back
294 IF (NOT Reset_Globals) THEN
295 RAISE FND_API.G_EXC_ERROR;
296 END IF;
297
298 RETURN;
299 END IF;
300
301
302 -- Return if header is created in same operation
303 -- add g_header_created check to fix bug 3700341
304 IF OE_Order_Util.g_header_rec.operation = OE_GLOBALS.G_OPR_CREATE OR
305 OE_GLOBALS.G_HEADER_CREATED THEN
306 IF l_debug_level > 0 THEN
307 oe_debug_pub.add('Not versioning because header was also created',1);
308 END IF;
309
310 x_return_status := l_return_status;
311
312 -- Bug 5156668
313 -- This needs to be called only in the end, hence commenting
314 -- IF (NOT Reset_Globals) THEN
315 -- RAISE FND_API.G_EXC_ERROR;
316 -- END IF;
317
318 -- XDING: Commentting out this DOESNOT fix bug 5156668 but cause
319 -- regression issue 505564, so put this code back
320 IF (NOT Reset_Globals) THEN
321 RAISE FND_API.G_EXC_ERROR;
322 END IF;
323
324 RETURN;
325 END IF;
326
327 -- Record changed records
328
329 Record_Changed_Records(p_version_flag => l_version_flag,
330 p_phase_change_flag => l_phase_change_flag,
331 p_changed_attribute => p_changed_attribute,
332 x_return_status => l_return_status);
333
334 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
336 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
337 RAISE FND_API.G_EXC_ERROR;
338 END IF;
339
340 -- Record unchanged records
341 IF l_debug_level > 0 THEN
342 oe_debug_pub.add('Calling Create_Version_History ',1);
343 END IF;
344
345 OE_Version_History_Util.Create_Version_History(p_header_id => p_header_id,
346 p_version_number => l_version_number,
347 p_phase_change_flag => l_phase_change_flag,
348 p_changed_attribute => p_changed_attribute,
349 x_return_status => l_return_status);
350
351 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
353 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
354 RAISE FND_API.G_EXC_ERROR;
355 END IF;
356
357 l_return_status := Delete_Created_Records;
358
359 IF IS_REASON_RQD <> 'N' THEN
360 IF OE_GLOBALS.G_REASON_CODE IS NULL OR
361 OE_GLOBALS.G_REASON_CODE = FND_API.G_MISS_CHAR THEN
362 -- Get stored reason in event of SYSTEM change
363 Get_Reason_Info(x_reason_code => OE_GLOBALS.G_REASON_CODE,
364 x_reason_comments => OE_GLOBALS.G_REASON_COMMENTS);
365
366 IF OE_GLOBALS.G_REASON_CODE IS NOT NULL OR
367 OE_GLOBALS.G_REASON_CODE = FND_API.G_MISS_CHAR THEN
368 OE_GLOBALS.G_CAPTURED_REASON := 'Y';
369 END IF;
370 END IF;
371 END IF;
372
373 --Apply reason changes
374 IF (IS_REASON_RQD = 'Y' AND
375 OE_GLOBALS.G_REASON_CODE <> FND_API.G_MISS_CHAR) OR
376 (IS_REASON_RQD <> 'N' AND
377 OE_GLOBALS.G_DEFAULT_REASON) THEN
378
379 IF OE_GLOBALS.G_DEFAULT_REASON THEN
380 IF l_debug_level > 0 THEN
381 oe_debug_pub.add('Defaulting Versioning Reason if necessary');
382 END IF;
383 OE_GLOBALS.G_REASON_CODE := nvl(OE_GLOBALS.G_REASON_CODE, 'SYSTEM');
384 END IF;
385
386 IF l_debug_level > 0 THEN
387 oe_debug_pub.add('Calling Apply_Reason ' || OE_GLOBALS.G_REASON_CODE,1);
388 END IF;
389
390 OE_Reasons_Util.Apply_Reason(
391 p_entity_code => 'HEADER',
392 p_entity_id => p_header_id,
393 p_header_id => p_header_id,
394 p_version_number => l_version_number,
395 p_reason_type => nvl(OE_GLOBALS.G_REASON_TYPE, 'CANCEL_CODE'),
396 p_reason_code => OE_GLOBALS.G_REASON_CODE,
397 p_reason_comments => OE_GLOBALS.G_REASON_COMMENTS,
398 x_reason_id => l_reason_id,
399 x_return_status => l_return_status);
400
401 UPDATE OE_ORDER_HEADER_HISTORY
402 SET REASON_ID = l_reason_id
403 WHERE HEADER_ID = p_header_id
404 AND phase_change_flag = l_phase_change_flag
405 AND version_flag = l_version_flag
406 AND version_number = l_version_number;
407
408 ELSIF IS_REASON_RQD <> 'N' THEN
409 FND_MESSAGE.SET_NAME('ONT','OE_AUDIT_REASON_RQD');
410 OE_MSG_PUB.ADD;
411
412 RAISE FND_API.G_EXC_ERROR;
413
414 END IF;
415
416 --Update l_version_number
417 IF (l_version_flag = 'Y') THEN
418
419 IF l_debug_level > 0 THEN
420 oe_debug_pub.add(' Updating version number, lock control ',1);
421 END IF;
422 SELECT LOCK_CONTROL
423 INTO l_lock_control
424 FROM OE_ORDER_HEADERS_ALL
425 WHERE HEADER_ID = p_header_id;
426
427 IF OE_ORDER_UTIL.G_HEADER_REC.VERSION_NUMBER = OE_ORDER_UTIL.G_OLD_HEADER_REC.VERSION_NUMBER
428 OR OE_ORDER_UTIL.G_HEADER_REC.VERSION_NUMBER IS NULL
429 OR OE_ORDER_UTIL.G_HEADER_REC.VERSION_NUMBER = FND_API.G_MISS_NUM
430 OR OE_ORDER_UTIL.G_OLD_HEADER_REC.VERSION_NUMBER IS NULL
431 OR OE_ORDER_UTIL.G_OLD_HEADER_REC.VERSION_NUMBER = FND_API.G_MISS_NUM THEN
432 UPDATE OE_ORDER_HEADERS_ALL
433 SET VERSION_NUMBER = l_version_number + 1,
434 LOCK_CONTROL = l_lock_control + 1
435 WHERE HEADER_ID = p_header_id;
436
437 /* update the order cache so that the header can be updated without requery
438 after creating a new version from line, bug 4523686*/
439 IF l_debug_level > 0 THEN
440 oe_debug_pub.add(' Updating oe_order_cache.g_header_rec with version number, lock control ',1);
441 END IF;
442 OE_ORDER_CACHE.g_header_rec.version_number := l_version_number + 1;
443 OE_ORDER_CACHE.g_header_rec.lock_control := l_lock_control + 1;
444
445 OE_GLOBALS.G_CASCADING_REQUEST_LOGGED := TRUE;
446 ELSIF OE_ORDER_UTIL.G_HEADER_REC.VERSION_NUMBER < OE_ORDER_UTIL.G_OLD_HEADER_REC.VERSION_NUMBER THEN
447 FND_MESSAGE.SET_NAME('ONT','OE_BLKT_INVALID_REVISION_NUM');
448 OE_MSG_PUB.ADD;
449
450 RAISE FND_API.G_EXC_ERROR;
451 END IF;
452 END IF;
453
454 G_VERSION_NUMBER := l_version_number;
455 --added for bug5216912 start
456 if G_VERSION_NUMBER is null then
457 G_VERSION_NUMBER := OE_ORDER_UTIL.g_old_header_rec.version_number;
458 end if;
459 --added for bug5216912 end
460 -- added for 3679627
461 x_return_status := l_return_status;
462
463 -- Bug 5156668
464 -- This needs to be called only in the end, hence commenting
465 --IF (NOT Reset_Globals) THEN
466 -- RAISE FND_API.G_EXC_ERROR;
467 --END IF;
468
469 -- XDING: Commentting out this DOESNOT fix bug 5156668 but cause
470 -- regression issue 505564, so put this code back
471 IF (NOT Reset_Globals) THEN
472 RAISE FND_API.G_EXC_ERROR;
473 END IF;
474
475 EXCEPTION
476 WHEN FND_API.G_EXC_ERROR THEN
477 ROLLBACK TO SAVEPOINT Perform_Versioning;
478 x_return_status := FND_API.G_RET_STS_ERROR;
479 RAISE FND_API.G_EXC_ERROR;
480 WHEN OTHERS THEN
481 ROLLBACK TO SAVEPOINT Perform_Versioning;
482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
483
484 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
485 OE_MSG_PUB.Add_Exc_Msg
486 ( G_PKG_NAME
487 , 'Perform_Versioning'
488 );
489 END IF;
490
491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
492
493 END Perform_Versioning;
494
495
496 FUNCTION Get_Header_ID
497 RETURN NUMBER
498 IS
499 i NUMBER;
500 BEGIN
501
502 -- Get current header_id
503
504 if oe_order_util.g_header_id <> FND_API.G_MISS_NUM then
505 RETURN oe_order_util.g_header_id;
506 elsif
507 oe_order_util.g_old_header_rec.header_id <> FND_API.G_MISS_NUM then
508 RETURN oe_order_util.g_old_header_rec.header_id;
509 elsif
510 oe_order_util.g_header_rec.header_id <> FND_API.G_MISS_NUM then
511 RETURN oe_order_util.g_header_rec.header_id;
512 else
513 i := oe_order_util.g_old_line_tbl.FIRST; -- get subscript of first element
514 WHILE i IS NOT NULL LOOP
515 if oe_order_util.g_old_line_tbl(i).header_id <> FND_API.G_MISS_NUM then
516 RETURN oe_order_util.g_old_line_tbl(i).header_id;
517 end if;
518 i := oe_order_util.g_old_line_tbl.NEXT(i); -- get subscript of next element
519 END LOOP;
520
521 i := oe_order_util.g_line_tbl.FIRST; -- get subscript of first element
522 WHILE i IS NOT NULL LOOP
523 if oe_order_util.g_line_tbl(i).header_id <> FND_API.G_MISS_NUM then
524 RETURN oe_order_util.g_line_tbl(i).header_id;
525 end if;
526 i := oe_order_util.g_line_tbl.NEXT(i); -- get subscript of next element
527 END LOOP;
528
529 i := oe_order_util.g_old_header_adj_tbl.FIRST; -- get subscript of first element
530 WHILE i IS NOT NULL LOOP
531 if oe_order_util.g_old_header_adj_tbl(i).header_id <> FND_API.G_MISS_NUM then
532 RETURN oe_order_util.g_old_header_adj_tbl(i).header_id;
533 end if;
534 i := oe_order_util.g_old_header_adj_tbl.NEXT(i); -- get subscript of next element
535 END LOOP;
536
537 i := oe_order_util.g_header_adj_tbl.FIRST; -- get subscript of first element
538 WHILE i IS NOT NULL LOOP
539 if oe_order_util.g_header_adj_tbl(i).header_id <> FND_API.G_MISS_NUM then
540 RETURN oe_order_util.g_header_adj_tbl(i).header_id;
541 end if;
542 i := oe_order_util.g_header_adj_tbl.NEXT(i); -- get subscript of next element
543 END LOOP;
544
545 i := oe_order_util.g_old_line_adj_tbl.FIRST; -- get subscript of first element
546 WHILE i IS NOT NULL LOOP
547 if oe_order_util.g_old_line_adj_tbl(i).header_id <> FND_API.G_MISS_NUM then
548 RETURN oe_order_util.g_old_line_adj_tbl(i).header_id;
549 end if;
550 i := oe_order_util.g_old_line_adj_tbl.NEXT(i); -- get subscript of next element
551 END LOOP;
552
553 i := oe_order_util.g_line_adj_tbl.FIRST; -- get subscript of first element
554 WHILE i IS NOT NULL LOOP
555 if oe_order_util.g_line_adj_tbl(i).header_id <> FND_API.G_MISS_NUM then
556 RETURN oe_order_util.g_line_adj_tbl(i).header_id;
557 end if;
558 i := oe_order_util.g_line_adj_tbl.NEXT(i); -- get subscript of next element
559 END LOOP;
560
561 i := oe_order_util.g_old_header_scredit_tbl.FIRST; -- get subscript of first element
562 WHILE i IS NOT NULL LOOP
563 if oe_order_util.g_old_header_scredit_tbl(i).header_id <> FND_API.G_MISS_NUM then
564 RETURN oe_order_util.g_old_header_scredit_tbl(i).header_id;
565 end if;
566 i := oe_order_util.g_old_header_scredit_tbl.NEXT(i); -- get subscript of next element
567 END LOOP;
568
569 i := oe_order_util.g_header_scredit_tbl.FIRST; -- get subscript of first element
570 WHILE i IS NOT NULL LOOP
571 if oe_order_util.g_header_scredit_tbl(i).header_id <> FND_API.G_MISS_NUM then
572 RETURN oe_order_util.g_header_scredit_tbl(i).header_id;
573 end if;
574 i := oe_order_util.g_header_scredit_tbl.NEXT(i); -- get subscript of next element
575 END LOOP;
576
577 i := oe_order_util.g_old_line_scredit_tbl.FIRST; -- get subscript of first element
578 WHILE i IS NOT NULL LOOP
579 if oe_order_util.g_old_line_scredit_tbl(i).header_id <> FND_API.G_MISS_NUM then
580 RETURN oe_order_util.g_old_line_scredit_tbl(i).header_id;
581 end if;
582 i := oe_order_util.g_old_line_scredit_tbl.NEXT(i); -- get subscript of next element
583 END LOOP;
584
585 i := oe_order_util.g_line_scredit_tbl.FIRST; -- get subscript of first element
586 WHILE i IS NOT NULL LOOP
587 if oe_order_util.g_line_scredit_tbl(i).header_id <> FND_API.G_MISS_NUM then
588 RETURN oe_order_util.g_line_scredit_tbl(i).header_id;
589 end if;
590 i := oe_order_util.g_line_scredit_tbl.NEXT(i); -- get subscript of next element
591 END LOOP;
592
593 end if;
594
595 END Get_Header_Id;
596
597 --Procedure runs from delayed request. This procedure is called to handle
598 --changed records, handling both audit trail requests and changed records
599 --prior to recording unchanged records in the event of versioning/phase change
600
601 Procedure Record_Changed_Records(
602 p_version_flag IN VARCHAR2 := NULL,
603 p_phase_change_flag IN VARCHAR2 := NULL,
604 p_changed_attribute IN VARCHAR2 := NULL,
605 x_return_status OUT NOCOPY VARCHAR2)
606 IS
607 i NUMBER;
608 l_return_status VARCHAR2(1);
609 l_hist_type_code VARCHAR2(30);
610 l_audit_flag VARCHAR2(1);
611 l_header_id NUMBER;
612 l_version_number NUMBER;
613 l_reason_id NUMBER;
614 --
615 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
616 --
617 BEGIN
618
619 SAVEPOINT Record_Changed_Records;
620 l_return_status := FND_API.G_RET_STS_SUCCESS;
621
622 IF l_debug_level > 0 THEN
623 oe_debug_pub.add('Entering OE_Versioning_util.record_changed_records ',1);
624 END IF;
625
626 l_header_id := Get_Header_ID;
627
628 IF l_debug_level > 0 THEN
629 oe_debug_pub.add('OE_Versioning_Util header_id: ' || l_header_id,1);
630 END IF;
631
632 IF OE_ORDER_UTIL.g_old_header_rec.version_number IS NULL
633 OR OE_ORDER_UTIL.g_old_header_rec.version_number = FND_API.G_MISS_NUM THEN
634 select version_number into l_version_number from oe_order_headers_all where header_id = l_header_id;
635 ELSE
636 l_version_number := OE_ORDER_UTIL.g_old_header_rec.version_number;
637 END IF;
638
639 IF l_debug_level > 0 THEN
640 oe_debug_pub.add('Before updating history ',1);
641 END IF;
642
643 --Update history with audit flag information gathered in global picture
644 IF OE_Order_Util.g_old_header_rec.header_id <> FND_API.G_MISS_NUM THEN
645 IF NOT OE_GLOBALS.Equal(OE_Order_Util.g_old_header_rec.operation, OE_GLOBALS.G_OPR_CREATE) AND
646 (p_version_flag = 'Y' OR p_phase_change_flag = 'Y' or
647 g_audit_header_hist_code IS NOT NULL) THEN
648
649 IF g_audit_header_hist_code IS NOT NULL THEN
650 l_audit_flag := 'Y';
651 l_hist_type_code := g_audit_header_hist_code;
652 ELSE
653 l_audit_flag := 'N';
654 END IF;
655
656
657 if g_audit_header_reason_required and l_audit_flag = 'Y' then
658 --Apply Reason for audit
659 OE_Reasons_Util.Apply_Reason(
660 p_entity_code => 'HEADER',
661 p_entity_id => oe_order_util.g_old_header_rec.header_id,
662 p_header_id => oe_order_util.g_old_header_rec.header_id,
663 p_version_number => l_version_number,
664 p_reason_type => 'CANCEL_CODE',
665 p_reason_code => OE_ORDER_UTIL.g_header_rec.change_reason,
666 p_reason_comments => OE_ORDER_UTIL.g_header_rec.change_comments,
667 x_reason_id => l_reason_id,
668 x_return_status => l_return_status);
669
670 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
671 IF l_debug_level > 0 THEN
672 oe_debug_pub.add('Applying Audit Reason Caused Error on Header',1);
673 END IF;
674
675 if l_return_status = FND_API.G_RET_STS_ERROR then
676 raise FND_API.G_EXC_ERROR;
677 else
678 raise FND_API.G_EXC_UNEXPECTED_ERROR;
679 end if;
680 end if;
681
682 end if; --end apply reason
683
684 IF l_debug_level > 0 THEN
685 oe_debug_pub.add('Calling recordheaderhist operation: '|| oe_order_util.g_old_header_rec.operation,1);
686 END IF;
687
688 OE_CHG_ORDER_PVT.RecordHeaderHist
689 ( p_header_id => oe_order_util.g_old_header_rec.header_id,
690 p_header_rec => oe_order_util.g_old_header_rec,
691 p_hist_type_code => l_hist_type_code,
692 p_reason_code => NULL,
693 p_comments => NULL,
694 p_audit_flag => l_audit_flag,
695 p_version_flag => p_version_flag,
696 p_phase_change_flag => p_phase_change_flag,
697 p_version_number => l_version_number,
698 p_reason_id => l_reason_id,
699 p_wf_activity_code => null,
700 p_wf_result_code => null,
701 p_changed_attribute => p_changed_attribute,
702 x_return_status => l_return_status
703 );
704
705 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
706 IF l_debug_level > 0 THEN
707 oe_debug_pub.add('Inserting Header History Caused Error ',1);
708 END IF;
709
710 if l_return_status = FND_API.G_RET_STS_ERROR then
711 raise FND_API.G_EXC_ERROR;
712 else
713 raise FND_API.G_EXC_UNEXPECTED_ERROR;
714 end if;
715 end if;
716
717 END IF;
718 END IF;
719
720 IF l_debug_level > 0 THEN
721 oe_debug_pub.add(' OEXUVERB: before recording lines ',1);
722 END IF;
723
724 --Update history for lines
725 i := oe_order_util.g_old_line_tbl.FIRST; -- get subscript of first element
726 WHILE i IS NOT NULL LOOP
727 IF NOT OE_GLOBALS.Equal(oe_order_util.g_old_line_tbl(i).operation, OE_GLOBALS.G_OPR_CREATE) AND
728 (p_version_flag = 'Y' OR p_phase_change_flag = 'Y' or
729 g_audit_line_id_tbl.exists(i)) THEN
730 IF g_audit_line_id_tbl.exists(i) THEN
731 l_audit_flag := 'Y';
732 l_hist_type_code := g_audit_line_id_tbl(i).hist_type_code;
733 ELSE
734 l_audit_flag := 'N';
735 l_hist_type_code := 'VERSIONING';
736 END IF;
737
738 if l_debug_level > 0 then
739 oe_debug_pub.add('audit flag :'||l_audit_flag);
740 oe_debug_pub.add('hist type code :'||l_hist_type_code);
741 end if;
742
743 if l_audit_flag = 'Y' and g_audit_line_id_tbl.exists(i) and
744 (g_audit_line_id_tbl(i).reason_required
745 -- Cancellation (hist type of CANCELLATION)
746 -- Or qty decrease (hist type of QUANTITY UPDATE) can always send
747 -- in a reason as cancel action window has a reason field. This
748 -- value can be passed even if constraint does not require reason
749 -- but if reason is supplied for these hist types, it should be
750 -- captured.
751 OR (l_hist_type_code IN ('CANCELLATION','QUANTITY UPDATE')
752 and OE_ORDER_UTIL.g_line_tbl(i).change_reason is not null
753 and OE_ORDER_UTIL.g_line_tbl(i).change_reason <> fnd_api.g_miss_char
754 )
755 ) then
756 --Apply Reason for audit
757 OE_Reasons_Util.Apply_Reason(
758 p_entity_code => 'LINE',
759 p_entity_id => oe_order_util.g_old_line_tbl(i).line_id,
760 p_header_id => oe_order_util.g_old_line_tbl(i).header_id,
761 p_version_number => l_version_number,
762 p_reason_type => 'CANCEL_CODE',
763 p_reason_code => OE_ORDER_UTIL.g_line_tbl(i).change_reason,
764 p_reason_comments => OE_ORDER_UTIL.g_line_tbl(i).change_comments,
765 x_reason_id => l_reason_id,
766 x_return_status => l_return_status);
767
768 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
769 IF l_debug_level > 0 THEN
770 oe_debug_pub.add('Applying Audit Reason Caused Error on Line',1);
771 END IF;
772
773 if l_return_status = FND_API.G_RET_STS_ERROR then
774 raise FND_API.G_EXC_ERROR;
775 else
776 raise FND_API.G_EXC_UNEXPECTED_ERROR;
777 end if;
778 end if;
779
780 end if; --end apply reason
781
782 IF l_debug_level > 0 THEN
783 oe_debug_pub.add('Calling recordlinehist operation: '|| oe_order_util.g_old_line_tbl(i).operation,1);
784 END IF;
785
786 OE_CHG_ORDER_PVT.RecordLineHist
787 (p_line_id => oe_order_util.g_old_line_tbl(i).line_id,
788 p_line_rec => oe_order_util.g_old_line_tbl(i),
789 p_hist_type_code => l_hist_type_code,
790 p_reason_code => NULL,
791 p_comments => NULL,
792 p_audit_flag => l_audit_flag,
793 p_version_flag => p_version_flag,
794 p_phase_change_flag => p_phase_change_flag,
795 p_version_number => l_version_number,
796 p_reason_id => l_reason_id,
797 p_wf_activity_code => null,
798 p_wf_result_code => null,
799 x_return_status => l_return_status);
800 IF l_debug_level > 0 THEN
801 OE_DEBUG_PUB.add('IN OEXUVERB:After'||l_return_status,5);
802 END IF;
803
804 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
805 IF l_debug_level > 0 THEN
806 oe_debug_pub.add('Inserting Line Audit History error',1);
807 END IF;
808 IF l_return_status = FND_API.G_RET_STS_ERROR then
809 raise FND_API.G_EXC_ERROR;
810 ELSE
811 raise FND_API.G_EXC_UNEXPECTED_ERROR;
812 END IF;
813 END IF;
814
815 END IF;
816 i := oe_order_util.g_old_line_tbl.NEXT(i); -- get subscript of next element
817 END LOOP;
818
819 IF l_debug_level > 0 THEN
820 oe_debug_pub.add(' OEXUVERB: before recording header adjs ',1);
821 END IF;
822
823 --Update history for header adj
824 i := oe_order_util.g_old_header_adj_tbl.FIRST; -- get subscript of first element
825 WHILE i IS NOT NULL LOOP
826 IF NOT OE_GLOBALS.Equal(oe_order_util.g_old_header_adj_tbl(i).operation, OE_GLOBALS.G_OPR_CREATE) AND
827 (p_version_flag = 'Y' OR p_phase_change_flag = 'Y' or
828 g_audit_header_adj_id_tbl.exists(i)) THEN
829 IF g_audit_header_adj_id_tbl.exists(i) THEN
830 l_audit_flag := 'Y';
831 l_hist_type_code := g_audit_header_adj_id_tbl(i).hist_type_code;
832 ELSE
833 l_audit_flag := 'N';
834 l_hist_type_code := NULL;
835 END IF;
836
837 if l_audit_flag = 'Y' and g_audit_header_adj_id_tbl.exists(i) and
838 g_audit_header_adj_id_tbl(i).reason_required then
839 --Apply Reason for audit
840 OE_Reasons_Util.Apply_Reason(
841 p_entity_code => 'HEADER_ADJ',
842 p_entity_id => oe_order_util.g_old_header_adj_tbl(i).price_adjustment_id,
843 p_header_id => oe_order_util.g_old_header_adj_tbl(i).header_id,
844 p_version_number => l_version_number,
845 p_reason_type => 'CHANGE_CODE',
846 p_reason_code => OE_ORDER_UTIL.g_header_adj_tbl(i).change_reason_code,
847 p_reason_comments => OE_ORDER_UTIL.g_header_adj_tbl(i).change_reason_text,
848 x_reason_id => l_reason_id,
849 x_return_status => l_return_status);
850
851 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
852 IF l_debug_level > 0 THEN
853 oe_debug_pub.add('Applying Audit Reason Caused Error on Header Adj',1);
854 END IF;
855
856 if l_return_status = FND_API.G_RET_STS_ERROR then
857 raise FND_API.G_EXC_ERROR;
858 else
859 raise FND_API.G_EXC_UNEXPECTED_ERROR;
860 end if;
861 end if;
862
863 end if; --end apply reason
864
865 IF l_debug_level > 0 THEN
866 oe_debug_pub.add('Calling recordHPAdjhist operation: '|| oe_order_util.g_old_header_adj_tbl(i).operation,1);
867 END IF;
868
869 OE_CHG_ORDER_PVT.RecordHPAdjHist
870 (p_header_adj_id => oe_order_util.g_old_header_adj_tbl(i).price_adjustment_id,
871 p_header_adj_rec => oe_order_util.g_old_header_adj_tbl(i),
872 p_hist_type_code => l_hist_type_code,
873 p_reason_code => oe_globals.g_reason_code,
874 p_comments => oe_globals.g_reason_comments,
875 p_audit_flag => l_audit_flag,
876 p_version_flag => p_version_flag,
877 p_phase_change_flag => p_phase_change_flag,
878 p_version_number => l_version_number,
879 p_reason_id => l_reason_id,
880 p_wf_activity_code => null,
881 p_wf_result_code => null,
882 x_return_status => l_return_status);
883
884 IF l_debug_level > 0 THEN
885 OE_DEBUG_PUB.add('IN OEXUVERB:After'||l_return_status,5);
886 END IF;
887 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
888 IF l_debug_level > 0 THEN
889 oe_debug_pub.add('Inserting Line Audit History error',1);
890 END IF;
891
892 IF l_return_status = FND_API.G_RET_STS_ERROR then
893 raise FND_API.G_EXC_ERROR;
894 ELSE
895 raise FND_API.G_EXC_UNEXPECTED_ERROR;
896 END IF;
897 END IF;
898
899 END IF;
900 i := oe_order_util.g_old_header_adj_tbl.NEXT(i); -- get subscript of next element
901 END LOOP;
902
903
904 IF l_debug_level > 0 THEN
905 oe_debug_pub.add(' OEXUVERB: before recording header scredits ',1);
906 END IF;
907
908 --Update history for header scredit
909 i := oe_order_util.g_old_header_scredit_tbl.FIRST; -- get subscript of first element
910 WHILE i IS NOT NULL LOOP
911 IF NOT OE_GLOBALS.Equal(oe_order_util.g_old_header_scredit_tbl(i).operation, OE_GLOBALS.G_OPR_CREATE) AND
912 (p_version_flag = 'Y' OR p_phase_change_flag = 'Y' or
913 g_audit_header_scredit_id_tbl.exists(i)) THEN
914 IF g_audit_header_scredit_id_tbl.exists(i) THEN
915 l_audit_flag := 'Y';
916 l_hist_type_code := g_audit_header_scredit_id_tbl(i).hist_type_code;
917 ELSE
918 l_audit_flag := 'N';
919 l_hist_type_code := NULL;
920 END IF;
921
922 if l_audit_flag = 'Y' and g_audit_header_scredit_id_tbl.exists(i) and
923 g_audit_header_scredit_id_tbl(i).reason_required then
924 --Apply Reason for audit
925 OE_Reasons_Util.Apply_Reason(
926 p_entity_code => 'HEADER_SCREDIT',
927 p_entity_id => oe_order_util.g_old_header_scredit_tbl(i).sales_credit_id,
928 p_header_id => oe_order_util.g_old_header_scredit_tbl(i).header_id,
929 p_version_number => l_version_number,
930 p_reason_type => 'CANCEL_CODE',
931 p_reason_code => OE_ORDER_UTIL.g_header_scredit_tbl(i).change_reason,
932 p_reason_comments => OE_ORDER_UTIL.g_header_scredit_tbl(i).change_comments,
933 x_reason_id => l_reason_id,
934 x_return_status => l_return_status);
935
936 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
937 IF l_debug_level > 0 THEN
938 oe_debug_pub.add('Applying Audit Reason Caused Error on Header Scredit',1);
939 END IF;
940
941 if l_return_status = FND_API.G_RET_STS_ERROR then
942 raise FND_API.G_EXC_ERROR;
943 else
944 raise FND_API.G_EXC_UNEXPECTED_ERROR;
945 end if;
946 end if;
947
948 end if; --end apply reason
949
950 IF l_debug_level > 0 THEN
951 oe_debug_pub.add('Calling recordHSCredithist operation: '|| oe_order_util.g_old_header_scredit_tbl(i).operation,1);
952 END IF;
953
954 OE_CHG_ORDER_PVT.RecordHSCreditHist
955 (p_header_scredit_id => oe_order_util.g_old_header_scredit_tbl(i).sales_credit_id,
956 p_header_scredit_rec => oe_order_util.g_old_header_scredit_tbl(i),
957 p_hist_type_code => l_hist_type_code,
958 p_reason_code => oe_globals.g_reason_code,
959 p_comments => oe_globals.g_reason_comments,
960 p_audit_flag => l_audit_flag,
961 p_version_flag => p_version_flag,
962 p_phase_change_flag => p_phase_change_flag,
963 p_version_number => l_version_number,
964 p_reason_id => l_reason_id,
965 p_wf_activity_code => null,
966 p_wf_result_code => null,
967 x_return_status => l_return_status);
968
969 IF l_debug_level > 0 THEN
970 OE_DEBUG_PUB.add('IN OEXUVERB:After'||l_return_status,5);
971 END IF;
972 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
973 IF l_debug_level > 0 THEN
974 oe_debug_pub.add('Inserting Line Audit History error',1);
975 END IF;
976 IF l_return_status = FND_API.G_RET_STS_ERROR then
977 raise FND_API.G_EXC_ERROR;
978 ELSE
979 raise FND_API.G_EXC_UNEXPECTED_ERROR;
980 END IF;
981 END IF;
982
983 END IF;
984 i := oe_order_util.g_old_header_scredit_tbl.NEXT(i); -- get subscript of next element
985 END LOOP;
986
987 IF l_debug_level > 0 THEN
988 oe_debug_pub.add(' OEXUVERB: before recording line adjs ',1);
989 END IF;
990 --Update history for line adj
991 i := oe_order_util.g_old_line_adj_tbl.FIRST; -- get subscript of first element
992 WHILE i IS NOT NULL LOOP
993 IF NOT OE_GLOBALS.Equal(oe_order_util.g_old_line_adj_tbl(i).operation, OE_GLOBALS.G_OPR_CREATE) AND
994 (p_version_flag = 'Y' OR p_phase_change_flag = 'Y' or
995 g_audit_line_adj_id_tbl.exists(i)) THEN
996 IF g_audit_line_adj_id_tbl.exists(i) THEN
997 l_audit_flag := 'Y';
998 l_hist_type_code := g_audit_line_adj_id_tbl(i).hist_type_code;
999 ELSE
1000 l_audit_flag := 'N';
1001 l_hist_type_code := NULL;
1002 END IF;
1003
1004 if l_audit_flag = 'Y' and g_audit_line_adj_id_tbl.exists(i) and
1005 g_audit_line_adj_id_tbl(i).reason_required then
1006 --Apply Reason for audit
1007 OE_Reasons_Util.Apply_Reason(
1008 p_entity_code => 'LINE_ADJ',
1009 p_entity_id => oe_order_util.g_old_line_adj_tbl(i).price_adjustment_id,
1010 p_header_id => oe_order_util.g_old_line_adj_tbl(i).header_id,
1011 p_version_number => l_version_number,
1012 p_reason_type => 'CHANGE_CODE',
1013 p_reason_code => OE_ORDER_UTIL.g_line_adj_tbl(i).change_reason_code,
1014 p_reason_comments => OE_ORDER_UTIL.g_line_adj_tbl(i).change_reason_text,
1015 x_reason_id => l_reason_id,
1016 x_return_status => l_return_status);
1017
1018 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1019 IF l_debug_level > 0 THEN
1020 oe_debug_pub.add('Applying Audit Reason Caused Error on Line Adj',1);
1021 END IF;
1022
1023 if l_return_status = FND_API.G_RET_STS_ERROR then
1024 raise FND_API.G_EXC_ERROR;
1025 else
1026 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1027 end if;
1028 end if;
1029
1030 end if; --end apply reason
1031
1032 IF l_debug_level > 0 THEN
1033 oe_debug_pub.add('Calling recordLPAdjhist operation: '|| oe_order_util.g_old_line_adj_tbl(i).operation,1);
1034 END IF;
1035
1036 OE_CHG_ORDER_PVT.RecordLPAdjHist
1037 (p_line_adj_id => oe_order_util.g_old_line_adj_tbl(i).price_adjustment_id,
1038 p_line_adj_rec => oe_order_util.g_old_line_adj_tbl(i),
1039 p_hist_type_code => l_hist_type_code,
1040 p_reason_code => oe_globals.g_reason_code,
1041 p_comments => oe_globals.g_reason_comments,
1042 p_audit_flag => l_audit_flag,
1043 p_version_flag => p_version_flag,
1044 p_phase_change_flag => p_phase_change_flag,
1045 p_version_number => l_version_number,
1046 p_reason_id => l_reason_id,
1047 p_wf_activity_code => null,
1048 p_wf_result_code => null,
1049 x_return_status => l_return_status);
1050
1051 IF l_debug_level > 0 THEN
1052 OE_DEBUG_PUB.add('IN OEXUVERB:After'||l_return_status,5);
1053 END IF;
1054 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1055 IF l_debug_level > 0 THEN
1056 oe_debug_pub.add('Inserting Line Audit History error',1);
1057 END IF;
1058
1059 IF l_return_status = FND_API.G_RET_STS_ERROR then
1060 raise FND_API.G_EXC_ERROR;
1061 ELSE
1062 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1063 END IF;
1064 END IF;
1065
1066 END IF;
1067 i := oe_order_util.g_old_line_adj_tbl.NEXT(i); -- get subscript of next element
1068 END LOOP;
1069
1070 IF l_debug_level > 0 THEN
1071 oe_debug_pub.add(' OEXUVERB: before recording line scredits ',1);
1072 END IF;
1073 --Update history for line scredit
1074 i := oe_order_util.g_old_line_scredit_tbl.FIRST; -- get subscript of first element
1075 WHILE i IS NOT NULL LOOP
1076 IF NOT OE_GLOBALS.Equal(oe_order_util.g_old_line_scredit_tbl(i).operation, OE_GLOBALS.G_OPR_CREATE) AND
1077 (p_version_flag = 'Y' OR p_phase_change_flag = 'Y' or
1078 g_audit_line_scredit_id_tbl.exists(i)) THEN
1079 IF g_audit_line_scredit_id_tbl.exists(i) THEN
1080 l_audit_flag := 'Y';
1081 l_hist_type_code := g_audit_line_scredit_id_tbl(i).hist_type_code;
1082 ELSE
1083 l_audit_flag := 'N';
1084 l_hist_type_code := NULL;
1085 END IF;
1086
1087 if l_audit_flag = 'Y' and g_audit_line_scredit_id_tbl.exists(i) and
1088 g_audit_line_scredit_id_tbl(i).reason_required then
1089 --Apply Reason for audit
1090 OE_Reasons_Util.Apply_Reason(
1091 p_entity_code => 'LINE_SCREDIT',
1092 p_entity_id => oe_order_util.g_old_line_scredit_tbl(i).sales_credit_id,
1093 p_header_id => oe_order_util.g_old_line_scredit_tbl(i).header_id,
1094 p_version_number => l_version_number,
1095 p_reason_type => 'CANCEL_CODE',
1096 p_reason_code => OE_ORDER_UTIL.g_line_scredit_tbl(i).change_reason,
1097 p_reason_comments => OE_ORDER_UTIL.g_line_scredit_tbl(i).change_comments,
1098 x_reason_id => l_reason_id,
1099 x_return_status => l_return_status);
1100
1101 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1102 IF l_debug_level > 0 THEN
1103 oe_debug_pub.add('Applying Audit Reason Caused Error on Line Scredit',1);
1104 END IF;
1105
1106 if l_return_status = FND_API.G_RET_STS_ERROR then
1107 raise FND_API.G_EXC_ERROR;
1108 else
1109 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1110 end if;
1111 end if;
1112
1113 end if; --end apply reason
1114
1115 IF l_debug_level > 0 THEN
1116 oe_debug_pub.add('Calling recordLSCredithist operation: '|| oe_order_util.g_old_line_scredit_tbl(i).operation,1);
1117 END IF;
1118
1119 OE_CHG_ORDER_PVT.RecordLSCreditHist
1120 (p_line_scredit_id => oe_order_util.g_old_line_scredit_tbl(i).sales_credit_id,
1121 p_line_scredit_rec => oe_order_util.g_old_line_scredit_tbl(i),
1122 p_hist_type_code => l_hist_type_code,
1123 p_reason_code => oe_globals.g_reason_code,
1124 p_comments => oe_globals.g_reason_comments,
1125 p_audit_flag => l_audit_flag,
1126 p_version_flag => p_version_flag,
1127 p_phase_change_flag => p_phase_change_flag,
1128 p_version_number => l_version_number,
1129 p_reason_id => l_reason_id,
1130 p_wf_activity_code => null,
1131 p_wf_result_code => null,
1132 x_return_status => l_return_status);
1133
1134 IF l_debug_level > 0 THEN
1135 OE_DEBUG_PUB.add('IN OEXUVERB:After'||l_return_status,5);
1136 END IF;
1137 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1138 IF l_debug_level > 0 THEN
1139 oe_debug_pub.add('Inserting Line Audit History error',1);
1140 END IF;
1141 IF l_return_status = FND_API.G_RET_STS_ERROR then
1142 raise FND_API.G_EXC_ERROR;
1143 ELSE
1144 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1145 END IF;
1146 END IF;
1147
1148 END IF;
1149 i := oe_order_util.g_old_line_scredit_tbl.NEXT(i); -- get subscript of next element
1150 END LOOP;
1151
1152 -- added for bug 4321689
1153 x_return_status := l_return_status;
1154
1155 /*
1156 IF (NOT Reset_Globals) THEN
1157 RAISE FND_API.G_EXC_ERROR;
1158 END IF;
1159 */
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 ROLLBACK TO SAVEPOINT Record_Changed_Records;
1163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1164 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1165 OE_MSG_PUB.Add_Exc_Msg
1166 ( G_PKG_NAME
1167 , 'Record Changed Records'
1168 );
1169 END IF;
1170 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1171
1172 END Record_Changed_Records;
1173
1174 Function Reset_Globals
1175 RETURN BOOLEAN
1176 IS
1177 --
1178 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1179 --
1180 BEGIN
1181
1182 --Reset globals
1183 IF l_debug_level > 0 THEN
1184 oe_debug_pub.add(' OEXUVERB: reset_globals ',1);
1185 END IF;
1186
1187 OE_GLOBALS.G_ROLL_VERSION := 'N';
1188 OE_GLOBALS.G_CAPTURED_REASON := 'N';
1189 OE_GLOBALS.G_REASON_CODE := NULL;
1190 OE_GLOBALS.G_REASON_COMMENTS := NULL;
1191 OE_GLOBALS.G_REASON_TYPE := NULL;
1192 G_Audit_Line_ID_Tbl.DELETE;
1193 G_Audit_Header_Adj_ID_Tbl.DELETE;
1194 G_Audit_Line_Adj_ID_Tbl.DELETE;
1195 G_Audit_Header_Scredit_ID_Tbl.DELETE;
1196 G_Audit_Line_Scredit_ID_Tbl.DELETE;
1197
1198 G_AUDIT_HEADER_HIST_CODE := NULL;
1199
1200 RETURN TRUE;
1201 END Reset_Globals;
1202
1203 Procedure Check_Security(
1204 p_column_name IN VARCHAR2,
1205 p_on_operation_action IN NUMBER)
1206 IS
1207 --
1208 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1209 --
1210 BEGIN
1211 IF l_debug_level > 0 THEN
1212 oe_debug_pub.add(' OEXUVERB: check security ',1);
1213 oe_debug_pub.add(' OEXUVERB: gen_version action: '|| p_on_operation_action,1);
1214 END IF;
1215
1216 -- added for bug 3518059
1217 -- add g_header_created check to fix bug 3700341
1218 if OE_Order_Util.g_header_rec.operation = OE_GLOBALS.G_OPR_CREATE OR
1219 OE_GLOBALS.G_HEADER_CREATED OR
1220 OE_Blanket_Util.g_header_rec.operation = OE_GLOBALS.G_OPR_CREATE then
1221 IF l_debug_level > 0 THEN
1222 oe_debug_pub.add(' OEXUVERB: header is created, do nothing',1);
1223 END IF;
1224 else
1225 IF p_column_name = 'TRANSACTION_PHASE_CODE' AND
1226 OE_QUOTE_UTIL.G_COMPLETE_NEG = 'Y' THEN
1227 --if transaction_phase is changing
1228 IF OE_GLOBALS.G_ROLL_VERSION = 'Y' THEN
1229 OE_GLOBALS.G_ROLL_VERSION := 'PV';
1230 ELSIF OE_GLOBALS.G_ROLL_VERSION = 'N' THEN
1231 OE_GLOBALS.G_ROLL_VERSION := 'P';
1232 END IF;
1233 END IF;
1234
1235 IF p_on_operation_action IN (.1,.2) THEN
1236 IF OE_GLOBALS.G_ROLL_VERSION = 'P' THEN
1237 OE_GLOBALS.G_ROLL_VERSION := 'PV';
1238 ELSIF OE_GLOBALS.G_ROLL_VERSION = 'N' THEN
1239 OE_GLOBALS.G_ROLL_VERSION := 'Y';
1240 END IF;
1241
1242 IF p_on_operation_action = .1 AND OE_GLOBALS.G_CAPTURED_REASON = 'N' THEN
1243 --Capture reason required for versioning
1244 OE_GLOBALS.G_CAPTURED_REASON := 'V';
1245 END IF;
1246 END IF;
1247 end if;
1248 IF l_debug_level > 0 THEN
1249 oe_debug_pub.add(' OEXUVERB: gen_version roll: '|| oe_globals.g_roll_version,1);
1250 END IF;
1251
1252 END Check_Security;
1253
1254
1255 FUNCTION IS_REASON_RQD RETURN Varchar2 IS
1256 BEGIN
1257 IF OE_GLOBALS.G_CAPTURED_REASON IN ('V') THEN
1258 RETURN OE_GLOBALS.G_CAPTURED_REASON;
1259 ELSIF OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG = 'Y' THEN
1260 RETURN 'A';
1261 ELSE
1262 -- else condition added for bug 4144357
1263 IF OE_GLOBALS.OE_AUDIT_HISTORY_TBL.count > 0 THEN
1264 FOR l_ind in 1..OE_GLOBALS.oe_audit_history_tbl.last LOOP
1265 IF OE_GLOBALS.OE_AUDIT_HISTORY_TBL.exists(l_ind) AND
1266 OE_GLOBALS.oe_audit_history_tbl(l_ind).HISTORY_TYPE = 'R' THEN
1267 OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG := 'Y';
1268 RETURN 'A';
1269 END IF;
1270 END LOOP;
1271 END IF;
1272 END IF;
1273
1274 IF OE_GLOBALS.G_CAPTURED_REASON IN ('N','Y') THEN
1275 RETURN OE_GLOBALS.G_CAPTURED_REASON;
1276 END IF;
1277 END;
1278
1279 FUNCTION IS_AUDIT_REASON_CAPTURED
1280 (p_entity_code IN VARCHAR2,
1281 p_entity_id IN NUMBER)
1282 RETURN BOOLEAN
1283 IS
1284 l_ind NUMBER;
1285 l_result VARCHAR2(1);
1286 l_return_stat VARCHAR2(1);
1287
1288 BEGIN
1289
1290 G_UI_CALLED := TRUE;
1291
1292 IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER THEN
1293 IF OE_Order_Util.g_header_rec.change_reason <> FND_API.G_MISS_CHAR THEN
1294 g_temp_reason_code := OE_Order_Util.g_header_rec.change_reason;
1295 g_temp_reason_comments := OE_Order_Util.g_header_rec.change_comments;
1296 RETURN TRUE;
1297 END IF;
1298 ELSE
1299
1300 OE_Order_Util.Return_Glb_Ent_Index(
1301 p_entity_code,
1302 p_entity_id,
1303 l_ind,
1304 l_result,
1305 l_return_stat);
1306
1307 IF l_result = FND_API.G_TRUE THEN
1308 IF p_entity_code = OE_GLOBALS.G_ENTITY_LINE THEN
1309 IF OE_Order_Util.g_line_tbl(l_ind).change_reason <> FND_API.G_MISS_CHAR THEN
1310 g_temp_reason_code := OE_Order_Util.g_line_tbl(l_ind).change_reason;
1311 g_temp_reason_comments := OE_Order_Util.g_line_tbl(l_ind).change_comments;
1312 RETURN TRUE;
1313 END IF;
1314 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER_ADJ THEN
1315 IF OE_Order_Util.g_header_adj_tbl(l_ind).change_reason_code <> FND_API.G_MISS_CHAR THEN
1316 g_temp_reason_code := OE_Order_Util.g_header_adj_tbl(l_ind).change_reason_code;
1317 g_temp_reason_comments := OE_Order_Util.g_header_adj_tbl(l_ind).change_reason_text;
1318 RETURN TRUE;
1319 END IF;
1320 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER_SCREDIT THEN
1321 IF OE_Order_Util.g_header_scredit_tbl(l_ind).change_reason <> FND_API.G_MISS_CHAR THEN
1322 g_temp_reason_code := OE_Order_Util.g_header_scredit_tbl(l_ind).change_reason;
1323 g_temp_reason_comments := OE_Order_Util.g_header_scredit_tbl(l_ind).change_comments;
1324 RETURN TRUE;
1325 END IF;
1326 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE_ADJ THEN
1327 IF OE_Order_Util.g_line_adj_tbl(l_ind).change_reason_code <> FND_API.G_MISS_CHAR THEN
1328 g_temp_reason_code := OE_Order_Util.g_line_adj_tbl(l_ind).change_reason_code;
1329 g_temp_reason_comments := OE_Order_Util.g_line_adj_tbl(l_ind).change_reason_text;
1330 RETURN TRUE;
1331 END IF;
1332 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE_SCREDIT THEN
1333 IF OE_Order_Util.g_line_scredit_tbl(l_ind).change_reason <> FND_API.G_MISS_CHAR THEN
1334 g_temp_reason_code := OE_Order_Util.g_line_scredit_tbl(l_ind).change_reason;
1335 g_temp_reason_comments := OE_Order_Util.g_line_scredit_tbl(l_ind).change_comments;
1336 RETURN TRUE;
1337 END IF;
1338 END IF;
1339 END IF;
1340 END IF;
1341
1342 RETURN FALSE;
1343
1344 END;
1345
1346 FUNCTION CAPTURED_REASON RETURN Varchar2 IS
1347 BEGIN
1348 RETURN OE_GLOBALS.G_REASON_CODE;
1349 END CAPTURED_REASON;
1350
1351
1352 Procedure Capture_Audit_Info(
1353 p_entity_code IN VARCHAR2,
1354 p_entity_id IN NUMBER,
1355 p_hist_type_code IN VARCHAR2
1356 )
1357 IS
1358 l_ind NUMBER;
1359 l_result VARCHAR2(1);
1360 l_return_stat VARCHAR2(1);
1361 --
1362 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1363 --
1364 BEGIN
1365
1366 if l_debug_level > 0 then
1367 oe_debug_pub.add('capture audit info');
1368 oe_debug_pub.add('entity code :'||p_entity_code);
1369 oe_debug_pub.add('entity id :'||p_entity_id);
1370 oe_debug_pub.add('hist type :'||p_hist_type_code);
1371 end if;
1372
1373 IF p_entity_code <> OE_GLOBALS.G_ENTITY_HEADER THEN
1374 OE_Order_Util.Return_Glb_Ent_Index(
1375 p_entity_code,
1376 p_entity_id,
1377 l_ind,
1378 l_result,
1379 l_return_stat);
1380 END IF;
1381
1382 IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER THEN
1383 G_Audit_Header_Hist_Code := 'UPDATE';
1384 IF OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG = 'Y' THEN
1385 G_Audit_Header_Reason_Required := TRUE;
1386 END IF;
1387 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE THEN
1388 IF NOT G_Audit_Line_ID_Tbl.exists(l_ind)
1389 OR G_Audit_Line_ID_Tbl(l_ind).hist_type_code NOT IN ('SPLIT', 'CANCELLATION') THEN
1390 G_Audit_Line_ID_Tbl(l_ind).entity_id := p_entity_id;
1391 G_Audit_Line_ID_Tbl(l_ind).hist_type_code := p_hist_type_code;
1392 IF OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG = 'Y' THEN
1393 G_Audit_Line_ID_Tbl(l_ind).Reason_Required := TRUE;
1394 END IF;
1395 END IF;
1396 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER_ADJ THEN
1397 IF NOT G_Audit_Header_Adj_ID_Tbl.exists(l_ind)
1398 OR G_Audit_Header_Adj_ID_Tbl(l_ind).hist_type_code NOT IN ('SPLIT', 'CANCELLATION') THEN
1399 G_Audit_Header_Adj_ID_Tbl(l_ind).entity_id := p_entity_id;
1400 G_Audit_Header_Adj_ID_Tbl(l_ind).hist_type_code := p_hist_type_code;
1401 IF OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG = 'Y' THEN
1402 G_Audit_Header_Adj_ID_Tbl(l_ind).Reason_Required := TRUE;
1403 END IF;
1404 END IF;
1405 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER_SCREDIT THEN
1406 IF NOT G_Audit_Header_scredit_ID_Tbl.exists(l_ind)
1407 OR G_Audit_Header_scredit_ID_Tbl(l_ind).hist_type_code NOT IN ('SPLIT', 'CANCELLATION') THEN
1408 G_Audit_Header_scredit_ID_Tbl(l_ind).entity_id := p_entity_id;
1409 G_Audit_Header_scredit_ID_Tbl(l_ind).hist_type_code := p_hist_type_code;
1410 IF OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG = 'Y' THEN
1411 G_Audit_Header_Scredit_ID_Tbl(l_ind).Reason_Required := TRUE;
1412 END IF;
1413 END IF;
1414 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE_ADJ THEN
1415 IF NOT G_Audit_Line_Adj_ID_Tbl.exists(l_ind)
1416 OR G_Audit_Line_Adj_ID_Tbl(l_ind).hist_type_code NOT IN ('SPLIT', 'CANCELLATION') THEN
1417 G_Audit_Line_Adj_ID_Tbl(l_ind).entity_id := p_entity_id;
1418 G_Audit_Line_Adj_ID_Tbl(l_ind).hist_type_code := p_hist_type_code;
1419 IF OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG = 'Y' THEN
1420 G_Audit_Line_Adj_ID_Tbl(l_ind).Reason_Required := TRUE;
1421 END IF;
1422 END IF;
1423 ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE_SCREDIT THEN
1424 IF NOT G_Audit_Line_scredit_ID_Tbl.exists(l_ind)
1425 OR G_Audit_Line_scredit_ID_Tbl(l_ind).hist_type_code NOT IN ('SPLIT', 'CANCELLATION') THEN
1426 G_Audit_Line_scredit_ID_Tbl(l_ind).entity_id := p_entity_id;
1427 G_Audit_Line_scredit_ID_Tbl(l_ind).hist_type_code := p_hist_type_code;
1428 IF OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG = 'Y' THEN
1429 G_Audit_Line_Scredit_ID_Tbl(l_ind).Reason_Required := TRUE;
1430 END IF;
1431 END IF;
1432 END IF;
1433
1434 OE_GLOBALS.G_AUDIT_REASON_RQD_FLAG := 'N';
1435 END Capture_Audit_Info;
1436
1437 Procedure Get_Reason_Info(
1438 x_reason_code OUT NOCOPY VARCHAR2,
1439 x_reason_comments OUT NOCOPY VARCHAR2
1440 )
1441 IS
1442 i NUMBER;
1443 BEGIN
1444
1445 -- Get reason from temp values
1446 if G_UI_CALLED THEN
1447 x_reason_code := G_TEMP_REASON_CODE;
1448 x_reason_comments := G_TEMP_REASON_COMMENTS;
1449
1450 G_TEMP_REASON_CODE := NULL;
1451 G_TEMP_REASON_COMMENTS := NULL;
1452
1453 G_UI_CALLED := FALSE;
1454
1455 RETURN;
1456 end if;
1457
1458 -- Get current change reason
1459
1460 if OE_GLOBALS.G_REASON_CODE IS NOT NULL THEN
1461 x_reason_code := OE_GLOBALS.G_REASON_CODE;
1462 x_reason_comments := OE_GLOBALS.G_REASON_COMMENTS;
1463 end if;
1464
1465 if oe_order_util.g_header_rec.change_reason <> FND_API.G_MISS_CHAR then
1466 x_reason_code := oe_order_util.g_header_rec.change_reason;
1467 x_reason_comments := oe_order_util.g_header_rec.change_comments;
1468 RETURN;
1469 else
1470
1471 i := oe_order_util.g_header_adj_tbl.FIRST; -- get subscript of first element
1472 WHILE i IS NOT NULL LOOP
1473 if oe_order_util.g_header_adj_tbl(i).change_reason_code <> FND_API.G_MISS_CHAR then
1474 x_reason_code := oe_order_util.g_header_adj_tbl(i).change_reason_code;
1475 x_reason_comments := oe_order_util.g_header_adj_tbl(i).change_reason_text;
1476 RETURN;
1477 end if;
1478 i := oe_order_util.g_header_adj_tbl.NEXT(i); -- get subscript of next element
1479 END LOOP;
1480
1481 i := oe_order_util.g_header_scredit_tbl.FIRST;
1482 WHILE i IS NOT NULL LOOP
1483 if oe_order_util.g_header_scredit_tbl(i).change_reason <> FND_API.G_MISS_CHAR then
1484 x_reason_code := oe_order_util.g_header_scredit_tbl(i).change_reason;
1485 x_reason_comments := oe_order_util.g_header_scredit_tbl(i).change_comments;
1486 RETURN;
1487 end if;
1488 i := oe_order_util.g_header_scredit_tbl.NEXT(i);
1489 END LOOP;
1490
1491 i := oe_order_util.g_line_tbl.FIRST;
1492 WHILE i IS NOT NULL LOOP
1493 if oe_order_util.g_line_tbl(i).change_reason <> FND_API.G_MISS_CHAR then
1494 x_reason_code := oe_order_util.g_line_tbl(i).change_reason;
1495 x_reason_comments := oe_order_util.g_line_tbl(i).change_comments;
1496 RETURN;
1497 end if;
1498 i := oe_order_util.g_line_tbl.NEXT(i);
1499 END LOOP;
1500
1501 i := oe_order_util.g_line_adj_tbl.FIRST;
1502 WHILE i IS NOT NULL LOOP
1503 if oe_order_util.g_line_adj_tbl(i).change_reason_code <> FND_API.G_MISS_CHAR then
1504 x_reason_code := oe_order_util.g_line_adj_tbl(i).change_reason_code;
1505 x_reason_comments := oe_order_util.g_line_adj_tbl(i).change_reason_text;
1506 RETURN;
1507 end if;
1508 i := oe_order_util.g_line_adj_tbl.NEXT(i);
1509 END LOOP;
1510
1511 i := oe_order_util.g_line_scredit_tbl.FIRST;
1512 WHILE i IS NOT NULL LOOP
1513 if oe_order_util.g_line_scredit_tbl(i).change_reason <> FND_API.G_MISS_CHAR then
1514 x_reason_code := oe_order_util.g_line_scredit_tbl(i).change_reason;
1515 x_reason_comments := oe_order_util.g_line_scredit_tbl(i).change_comments;
1516 RETURN;
1517 end if;
1518 i := oe_order_util.g_line_scredit_tbl.NEXT(i);
1519 END LOOP;
1520
1521 end if;
1522
1523 END Get_Reason_Info;
1524
1525 -------------------------------
1526 -- QUERY_ROW(S) Procedures have been moved to
1527 -- OE_Version_History_Util (OEXHVERS/B.pls)
1528 -------------------------------
1529
1530 END OE_Versioning_Util;
[Home] [Help]