[Home] [Help]
PACKAGE BODY: APPS.ENG_ECO_REVISION_UTIL
Source
1 PACKAGE BODY ENG_Eco_Revision_Util AS
2 /* $Header: ENGUREVB.pls 115.26 2004/06/03 06:17:10 lkasturi ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Eco_Revision_Util';
7 G_CONTROL_REC BOM_BO_PUB.Control_Rec_Type;
8 --bug 3047312
9 FUNCTION ret_app_status
10 (
11 p_change_id IN NUMBER
12 ,x_change_order_type_id OUT NOCOPY VARCHAR2
13 ,x_route_id OUT NOCOPY NUMBER
14 ,x_priority_code OUT NOCOPY VARCHAR2 -- Bug 3665542
15 )
16 RETURN NUMBER
17 IS
18 l_id NUMBER;
19 BEGIN
20
21 SELECT approval_status_type, change_order_type_id ,route_id, priority_code
22 INTO l_id, x_change_order_type_id ,x_route_id ,x_priority_code
23 FROM eng_engineering_changes
24 WHERE change_id = p_change_id ;
25
26 RETURN l_id;
27
28 EXCEPTION
29
30 WHEN NO_DATA_FOUND THEN
31 RETURN NULL;
32
33 WHEN OTHERS THEN
34 RETURN FND_API.G_MISS_NUM;
35
36 END ret_app_status;
37
38
39
40 FUNCTION ret_pro_name
41 (
42 p_change_order_type_id IN NUMBER
43 ,p_priority_code IN VARCHAR2 -- Bug 3665542
44 )
45 RETURN NUMBER
46 IS
47 l_id NUMBER :=1;
48 l_process_name eng_change_type_processes.process_name%TYPE;
49 BEGIN
50 SELECT process_name into l_process_name
51 FROM eng_change_type_processes
52 WHERE change_order_type_id = p_change_order_type_id --;
53 -- Bug 3665542: Added additional where clause to fetch process name
54 AND ((p_priority_code is NOT NULL
55 AND eng_change_priority_code = p_priority_code
56 AND organization_id = -1)
57 OR
58 (p_priority_code is NULL
59 AND eng_change_priority_code is NULL));
60 if l_process_name is null then
61 l_id :=0;
62 else
63 l_id :=1;
64 end if;
65 return l_id;
66 -- Bug 3665542: Added exception handling
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 l_id :=0;
70 RETURN l_id;
71 WHEN OTHERS THEN
72 RETURN FND_API.G_MISS_NUM;
73 END ret_pro_name ;
74 -- End Changes 3047312
75
76 -- Procedure Clear_Dependent_Attr
77
78 PROCEDURE Clear_Dependent_Attr
79 ( p_attr_id IN NUMBER := NULL --FND_API.G_MISS_NUM
80 , p_eco_revision_rec IN ENG_Eco_PUB.Eco_Revision_Rec_Type
81 , p_old_eco_revision_rec IN ENG_Eco_PUB.Eco_Revision_Rec_Type
82 , x_eco_revision_rec IN OUT NOCOPY ENG_Eco_PUB.Eco_Revision_Rec_Type
83 )
84 IS
85 BEGIN
86
87 -- Load out record
88
89 x_eco_revision_rec := p_eco_revision_rec;
90
91 END Clear_Dependent_Attr;
92
93 -- Procedure Apply_Attribute_Changes
94
95 PROCEDURE Apply_Attribute_Changes
96 ( p_eco_revision_rec IN ENG_Eco_PUB.Eco_Revision_Rec_Type
97 , p_old_eco_revision_rec IN ENG_Eco_PUB.Eco_Revision_Rec_Type
98 , x_eco_revision_rec IN OUT NOCOPY ENG_Eco_PUB.Eco_Revision_Rec_Type
99 )
100 IS
101 BEGIN
102 -- Load out record
103
104 x_eco_revision_rec := p_eco_revision_rec;
105
106 END Apply_Attribute_Changes;
107
108
109 -- Function Convert_Miss_To_Null
110
111 FUNCTION Convert_Miss_To_Null
112 ( p_eco_revision_rec IN ENG_Eco_PUB.Eco_Revision_Rec_Type
113 ) RETURN ENG_Eco_PUB.Eco_Revision_Rec_Type
114 IS
115 l_eco_revision_rec ENG_Eco_PUB.Eco_Revision_Rec_Type :=
116 p_eco_revision_rec;
117 BEGIN
118
119 RETURN l_eco_revision_rec;
120
121 END Convert_Miss_To_Null;
122
123 /****************************************************************************
124 *Procedure : Update_Row
125 *Parameters IN : Eco Revision exposed columns record
126 * Eco Revision unexposed columns record
127 *Parameters OUT : Mesg Token Table
128 * Return_Status
129 *Purpose : Update Row procedure will update any changed columns of the
130 * record. If it fails then an unexpected error will be returned
131 * with message text in Mesg_Token_Tbl and return_status of U.
132 ****************************************************************************/
133 PROCEDURE Update_Row
134 ( p_eco_revision_rec IN ENG_Eco_PUB.Eco_Revision_Rec_Type
135 , p_Eco_Rev_Unexp_Rec IN Eng_Eco_Pub.Eco_Rev_Unexposed_Rec_Type
136 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
137 , x_Return_Status OUT NOCOPY VARCHAR2
138 )
139 IS
140 l_Return_Status VARCHAR2(1);
141 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
142 l_err_text VARCHAR2(2000);
143 l_user_id NUMBER;
144 l_login_id NUMBER;
145 l_prog_appid NUMBER;
146 l_prog_id NUMBER;
147 l_request_id NUMBER;
148
149 BEGIN
150
151
152 l_user_id := Eng_Globals.Get_User_Id;
153 l_login_id := Eng_Globals.Get_Login_Id;
154 l_request_id := ENG_GLOBALS.Get_request_id;
155 l_prog_appid := ENG_GLOBALS.Get_prog_appid;
156 l_prog_id := ENG_GLOBALS.Get_prog_id;
157
158 UPDATE ENG_CHANGE_ORDER_REVISIONS
159 SET REVISION = DECODE
160 ( p_eco_revision_rec.new_revision, NULL,
161 p_eco_revision_rec.revision,
162 FND_API.G_MISS_CHAR,
163 p_eco_revision_rec.revision,
164 p_eco_revision_rec.new_revision )
165 , LAST_UPDATE_DATE = SYSDATE
166 , LAST_UPDATED_BY = l_user_id
167 , LAST_UPDATE_LOGIN = l_login_id
168 , COMMENTS = DECODE( p_eco_revision_rec.comments
169 , FND_API.G_MISS_CHAR
170 , null
171 , p_eco_revision_rec.comments
172 )
173 , ATTRIBUTE_CATEGORY = p_eco_revision_rec.attribute_category
174 , ATTRIBUTE1 = p_eco_revision_rec.attribute1
175 , ATTRIBUTE2 = p_eco_revision_rec.attribute2
176 , ATTRIBUTE3 = p_eco_revision_rec.attribute3
177 , ATTRIBUTE4 = p_eco_revision_rec.attribute4
178 , ATTRIBUTE5 = p_eco_revision_rec.attribute5
179 , ATTRIBUTE6 = p_eco_revision_rec.attribute6
180 , ATTRIBUTE7 = p_eco_revision_rec.attribute7
181 , ATTRIBUTE8 = p_eco_revision_rec.attribute8
182 , ATTRIBUTE9 = p_eco_revision_rec.attribute9
183 , ATTRIBUTE10 = p_eco_revision_rec.attribute10
184 , ATTRIBUTE11 = p_eco_revision_rec.attribute11
185 , ATTRIBUTE12 = p_eco_revision_rec.attribute12
186 , ATTRIBUTE13 = p_eco_revision_rec.attribute13
187 , ATTRIBUTE14 = p_eco_revision_rec.attribute14
188 , ATTRIBUTE15 = p_eco_revision_rec.attribute15
189 , Original_System_Reference =
190 p_eco_revision_rec.Original_System_Reference
191 , CHANGE_ID = p_Eco_Rev_Unexp_Rec.change_id
192
193
194 WHERE REVISION_ID = p_Eco_Rev_Unexp_Rec.revision_id;
195
196 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
197 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
198
199 EXCEPTION
200
201 WHEN OTHERS THEN
202
203 IF G_CONTROL_REC.caller_type = 'FORM'
204 THEN
205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
206 RAISE;
207 END IF;
208
209 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
210 THEN
211 l_err_text := G_PKG_NAME || ' : Utility (ECO Update) '
212 || substrb(SQLERRM,1,200);
213 Error_Handler.Add_Error_Token
214 ( p_Message_Text => l_err_text
215 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
216 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
217 );
218 END IF;
219
220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
221
222 END Update_Row;
223
224 /***************************************************************************
225 *Procedure : Insert_Row
226 *Parameters IN : Eco Revisions exposed columns record
227 * Eco Revisions unexposed columns record
228 *Parameters OUT : Mesg Token Table
229 * Return_Status
230 *Purpose : Insert a new revision record. Failure to do so will return
231 * an unexpected error with message text in the Mesg token
232 * Table and a return status of U
233 ***************************************************************************/
234 PROCEDURE Insert_Row
235 ( p_eco_revision_rec IN Eng_Eco_Pub.Eco_Revision_Rec_Type
236 , p_Eco_Rev_Unexp_Rec IN Eng_Eco_Pub.Eco_Rev_Unexposed_Rec_Type
237 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
238 , x_Return_Status OUT NOCOPY VARCHAR2
239 )
240 IS
241 l_Mesg_token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
242 l_user_id NUMBER;
243 l_login_id NUMBER;
244 l_prog_appid NUMBER;
245 l_prog_id NUMBER;
246 l_request_id NUMBER;
247 l_chk_co_app eng_engineering_changes.approval_status_type%TYPE;
248 l_change_order_type_id NUMBER;
249 l_route_id NUMBER;
250 l_process NUMBER ;
251 l_priority_code eng_engineering_changes.priority_code%TYPE; -- Bug 3665542
252
253 BEGIN
254
255 l_user_id := Eng_Globals.Get_User_Id;
256 l_login_id := Eng_Globals.Get_Login_Id;
257 l_request_id := ENG_GLOBALS.Get_request_id;
258 l_prog_appid := ENG_GLOBALS.Get_prog_appid;
259 l_prog_id := ENG_GLOBALS.Get_prog_id;
260
261
262 INSERT INTO ENG_CHANGE_ORDER_REVISIONS
263 ( REVISION_ID
264 , CHANGE_NOTICE
265 , ORGANIZATION_ID
266 , REVISION
267 , COMMENTS
268 , ATTRIBUTE_CATEGORY
269 , ATTRIBUTE1
270 , ATTRIBUTE2
271 , ATTRIBUTE3
272 , ATTRIBUTE4
273 , ATTRIBUTE5
274 , ATTRIBUTE6
275 , ATTRIBUTE7
276 , ATTRIBUTE8
277 , ATTRIBUTE9
278 , ATTRIBUTE10
279 , ATTRIBUTE11
280 , ATTRIBUTE12
281 , ATTRIBUTE13
282 , ATTRIBUTE14
283 , ATTRIBUTE15
284 , PROGRAM_ID
285 , PROGRAM_APPLICATION_ID
286 , PROGRAM_UPDATE_DATE
287 , REQUEST_ID
288 , LAST_UPDATE_DATE
289 , LAST_UPDATED_BY
290 , CREATION_DATE
291 , CREATED_BY
292 , LAST_UPDATE_LOGIN
293 , Original_System_Reference
294 , CHANGE_ID
295 )
296 VALUES
297 ( p_Eco_Rev_Unexp_Rec.revision_id
298 , p_Eco_Revision_Rec.Eco_Name
299 , p_Eco_Rev_Unexp_Rec.organization_id
300 , p_eco_revision_rec.revision
301 , DECODE( p_eco_revision_rec.comments
302 , FND_API.G_MISS_CHAR
303 , NULL
304 , p_eco_revision_rec.comments
305 )
306 , p_eco_revision_rec.attribute_category
307 , p_eco_revision_rec.attribute1
308 , p_eco_revision_rec.attribute2
309 , p_eco_revision_rec.attribute3
310 , p_eco_revision_rec.attribute4
311 , p_eco_revision_rec.attribute5
312 , p_eco_revision_rec.attribute6
313 , p_eco_revision_rec.attribute7
314 , p_eco_revision_rec.attribute8
315 , p_eco_revision_rec.attribute9
316 , p_eco_revision_rec.attribute10
317 , p_eco_revision_rec.attribute11
318 , p_eco_revision_rec.attribute12
319 , p_eco_revision_rec.attribute13
320 , p_eco_revision_rec.attribute14
321 , p_eco_revision_rec.attribute15
322 , l_Prog_Id
323 , l_Prog_AppId
324 , SYSDATE /* Program Update Date */
325 , NULL /* Request Id */
326 , SYSDATE /* Last Upate Date */
327 , l_User_Id /* Last Updated By */
328 , SYSDATE /* Creation Date */
329 , l_User_Id /* Created By */
330 , l_User_Id /* Last Updated Login */
331 , p_eco_revision_rec.Original_System_Reference
332 , p_Eco_Rev_Unexp_Rec.change_id
333 );
334
335
336 /* Bug no:3047312
337 UPDATE eng_engineering_changes
338 SET approval_status_type = 1
339 WHERE change_id = p_Eco_Rev_Unexp_Rec.change_id;
340 end of Bug no:3047312 */
341 /* Bug no:3047312*/
342 if ENG_GLOBALS.G_ENG_LAUNCH_IMPORT = 1 then --this should'nt be done for propagation
343 l_chk_co_app := ret_app_status ( p_Eco_Rev_Unexp_Rec.change_id,l_change_order_type_id,l_route_id, l_priority_code);
344 if l_route_id is null then
345 l_process := ret_pro_name (l_change_order_type_id, l_priority_code );
346 end if;
347 if (l_chk_co_app = 5 AND l_route_id is not null )
348 OR
349 (l_chk_co_app = 5 AND l_process = 1)
350 then
351 -- Set ECO to 'Not Submitted For Approval'
352
353 UPDATE eng_engineering_changes
354 SET approval_status_type = 1,
355 approval_request_date = null,
356 approval_date = null,
357 last_update_date = SYSDATE,
358 last_updated_by = FND_GLOBAL.USER_ID,
359 last_update_login = FND_GLOBAL.LOGIN_ID
360 WHERE change_id = p_Eco_Rev_Unexp_Rec.change_id;
361
362 -- Set all "Scheduled" revised items to "Open"
363
364 UPDATE eng_revised_items
365 SET status_type = 1,
366 last_update_date = SYSDATE,
367 last_updated_by = FND_GLOBAL.USER_ID,
368 last_update_login = FND_GLOBAL.LOGIN_ID
369 WHERE change_id = p_Eco_Rev_Unexp_Rec.change_id
370 AND status_type = 4;
371
372 end if;
373 end if;
374 /* end of Bug no:3047312 */
375
376 EXCEPTION
377
378 WHEN OTHERS THEN
379
380 IF G_CONTROL_REC.caller_type = 'FORM'
381 THEN
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383 RAISE;
384 END IF;
385
386 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
387 THEN
388 Error_Handler.Add_Error_Token
389 ( p_Message_Name => NULL
390 , p_Message_Text => 'ERROR in Insert Row (ECO Rev) ' ||
391 SUBSTR(SQLERRM, 1, 100) || ' ' ||
392 TO_CHAR(SQLCODE)
393 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
394 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
395 );
396 END IF;
397 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
398 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
399
400 END Insert_Row;
401
402 /***************************************************************************
403 *Procedure : Delete_Row
404 *Parameters IN : Eco Revisions Key column
405 *Parameters OUT : Mesg Token Table
406 * Return_Status
407 *Purpose : Delete an Eco Revision Record.
408 ***************************************************************************/
409
410 PROCEDURE Delete_Row
411 ( p_revision_id IN NUMBER
412 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
413 , x_Return_Status OUT NOCOPY VARCHAR2
414 )
415 IS
416 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
417 BEGIN
418
419 DELETE FROM ENG_CHANGE_ORDER_REVISIONS
420 WHERE REVISION_ID = p_revision_id;
421
422 EXCEPTION
423
424 WHEN NO_DATA_FOUND THEN
425 IF G_CONTROL_REC.caller_type = 'FORM'
426 THEN
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428 RAISE;
429 END IF;
430
431 Error_Handler.Add_Error_Token
432 ( p_Message_Name => 'OE_LOCK_ROW_DELETED'
433 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
434 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
435 );
436 x_Return_Status := FND_API.G_RET_STS_ERROR;
437 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
438 WHEN OTHERS THEN
439
440 IF G_CONTROL_REC.caller_type = 'FORM'
441 THEN
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
443 RAISE;
444 END IF;
445
446 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
447 THEN
448 Error_Handler.Add_Error_Token
449 ( p_Message_Name => NULL
450 , p_Message_Text => 'ERROR in Delete Row (ECO Rev) ' ||
451 substr(SQLERRM, 1, 30) || ' ' ||
452 to_char(SQLCODE)
453 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
454 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
455 );
456 END IF;
457
458 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
459 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
460
461 END Delete_Row;
462
463 /***************************************************************************
464 *Procedure : Query_Row
465 *Parameters IN : Eco Revisions Key column
466 *Parameters OUT : Return_Status
467 * Eco Revision exposed column record
468 * Eco Revision unexposed column record
469 *Purpose : Query up an Eco Revision Record an seperately return
470 * the record of exposed columns and unexposed record.
471 ***************************************************************************/
472
473 PROCEDURE Query_Row
474 ( p_Change_Notice IN VARCHAR2
475 , p_Organization_Id IN NUMBER
476 , p_Revision IN VARCHAR2
477 , x_Eco_Revision_Rec OUT NOCOPY Eng_Eco_Pub.Eco_Revision_Rec_Type
478 , x_Eco_Rev_Unexp_Rec OUT NOCOPY Eng_Eco_Pub.Eco_Rev_Unexposed_Rec_Type
479 , x_Return_Status OUT NOCOPY Varchar2
480 )
481 IS
482 l_err_text VARCHAR2(2000);
483 BEGIN
484
485 SELECT REVISION_ID
486 , CHANGE_NOTICE
487 , ORGANIZATION_ID
488 , REVISION
489 , COMMENTS
490 , ATTRIBUTE_CATEGORY
491 , ATTRIBUTE1
492 , ATTRIBUTE2
493 , ATTRIBUTE3
494 , ATTRIBUTE4
495 , ATTRIBUTE5
496 , ATTRIBUTE6
497 , ATTRIBUTE7
498 , ATTRIBUTE8
499 , ATTRIBUTE9
500 , ATTRIBUTE10
501 , ATTRIBUTE11
502 , ATTRIBUTE12
503 , ATTRIBUTE13
504 , ATTRIBUTE14
505 , ATTRIBUTE15
506 , CHANGE_ID --column added
507 INTO x_Eco_Rev_Unexp_Rec.revision_id
508 , x_eco_revision_rec.Eco_Name
509 , x_Eco_Rev_Unexp_Rec.organization_id
510 , x_eco_revision_rec.revision
511 , x_eco_revision_rec.comments
512 , x_eco_revision_rec.attribute_category
513 , x_eco_revision_rec.attribute1
514 , x_eco_revision_rec.attribute2
515 , x_eco_revision_rec.attribute3
516 , x_eco_revision_rec.attribute4
517 , x_eco_revision_rec.attribute5
518 , x_eco_revision_rec.attribute6
519 , x_eco_revision_rec.attribute7
520 , x_eco_revision_rec.attribute8
521 , x_eco_revision_rec.attribute9
522 , x_eco_revision_rec.attribute10
523 , x_eco_revision_rec.attribute11
524 , x_eco_revision_rec.attribute12
525 , x_eco_revision_rec.attribute13
526 , x_eco_revision_rec.attribute14
527 , x_eco_revision_rec.attribute15
528 , x_Eco_Rev_Unexp_Rec.change_id
529 FROM ENG_CHANGE_ORDER_REVISIONS
530 WHERE REVISION = p_revision
531 AND CHANGE_NOTICE = p_Change_Notice
532 AND ORGANIZATION_ID = p_Organization_Id;
533
534 x_Return_Status := Eng_Globals.G_RECORD_FOUND;
535
536 EXCEPTION
537
538 WHEN NO_DATA_FOUND THEN
539 x_Return_Status := Eng_Globals.G_RECORD_NOT_FOUND;
540 WHEN OTHERS THEN
541 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
542
543 END Query_Row;
544
545 /****************************************************************************
546 * Procedure : Perform_Writes
547 * Parameters IN : Eco Revision exposed column record
548 * Eco Revision unexposed Column record
549 * Prameters OUT : Mesg token Tbl
550 * Return Status
551 * Purpose : Based on the transaction type, this procedure will call the
552 * insert, update or delete procedures. When it comes to writing
553 * data to the entity tables, this is the only exposed procedure.
554 ******************************************************************************/
555 PROCEDURE Perform_Writes
556 ( p_eco_revision_rec IN Eng_Eco_Pub.Eco_Revision_Rec_Type
557 , p_eco_rev_unexp_rec IN Eng_Eco_Pub.Eco_Rev_Unexposed_Rec_Type
558 , p_control_rec IN BOM_BO_PUB.Control_Rec_Type
559 := BOM_BO_PUB.G_DEFAULT_CONTROL_REC
560 , x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
561 , x_return_status OUT NOCOPY VARCHAR2
562 )
563 IS
564 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
565 l_return_status VARCHAR2(1);
566 BEGIN
567 l_return_status := FND_API.G_RET_STS_SUCCESS;
568
569 G_CONTROL_REC := p_control_rec;
570
571 IF p_eco_revision_rec.transaction_type = Eng_Globals.G_OPR_CREATE
572 THEN
573 Insert_Row
574 ( p_eco_revision_rec => p_eco_revision_rec
575 , p_eco_rev_unexp_rec => p_eco_rev_unexp_rec
576 , x_mesg_token_tbl => l_mesg_token_tbl
577 , x_return_status => l_return_status
578 );
579 ELSIF p_eco_revision_rec.transaction_type = Eng_Globals.G_OPR_UPDATE
580 THEN
581 Update_Row
582 ( p_eco_revision_rec => p_eco_revision_rec
583 , p_eco_rev_unexp_rec => p_eco_rev_unexp_rec
584 , x_mesg_token_tbl => l_mesg_token_tbl
585 , x_return_status => l_return_status
586 );
587
588 ELSIF p_eco_revision_rec.transaction_type = Eng_Globals.G_OPR_DELETE
589 THEN
590 Delete_Row
591 ( p_revision_id => p_eco_rev_unexp_rec.revision_id
592 , x_mesg_token_tbl => l_mesg_token_tbl
593 , x_return_status => l_return_status
594 );
595 END IF;
596
597 x_return_status := l_return_status;
598 x_mesg_token_tbl := l_mesg_token_tbl;
599
600 END Perform_Writes;
601
602 -- Procedure lock_Row
603 -- NOT USED CURRENTLY
604 PROCEDURE Lock_Row
605 ( x_return_status OUT NOCOPY VARCHAR2
606 , p_eco_revision_rec IN ENG_Eco_PUB.Eco_Revision_Rec_Type
607 , x_eco_revision_rec IN OUT NOCOPY ENG_Eco_PUB.Eco_Revision_Rec_Type
608 , x_err_text OUT NOCOPY VARCHAR2
609 )
610 IS
611 l_eco_revision_rec ENG_Eco_PUB.Eco_Revision_Rec_Type;
612 l_err_text VARCHAR2(255);
613 BEGIN
614 NULL;
615 /*
616 SELECT ATTRIBUTE11
617 , ATTRIBUTE12
618 , ATTRIBUTE13
619 , ATTRIBUTE14
620 , ATTRIBUTE15
621 , PROGRAM_APPLICATION_ID
622 , PROGRAM_ID
623 , PROGRAM_UPDATE_DATE
624 , REQUEST_ID
625 , REVISION_ID
626 , CHANGE_NOTICE
627 , ORGANIZATION_ID
628 , REVISION
629 , LAST_UPDATE_DATE
630 , LAST_UPDATED_BY
631 , CREATION_DATE
632 , CREATED_BY
633 , LAST_UPDATE_LOGIN
634 , COMMENTS
635 , ATTRIBUTE_CATEGORY
636 , ATTRIBUTE1
637 , ATTRIBUTE2
638 , ATTRIBUTE3
639 , ATTRIBUTE4
640 , ATTRIBUTE5
641 , ATTRIBUTE6
642 , ATTRIBUTE7
643 , ATTRIBUTE8
644 , ATTRIBUTE9
645 , ATTRIBUTE10
646 INTO l_eco_revision_rec.attribute11
647 , l_eco_revision_rec.attribute12
648 , l_eco_revision_rec.attribute13
649 , l_eco_revision_rec.attribute14
650 , l_eco_revision_rec.attribute15
651 , l_eco_revision_rec.program_application_id
652 , l_eco_revision_rec.program_id
653 , l_eco_revision_rec.program_update_date
654 , l_eco_revision_rec.request_id
655 , l_eco_revision_rec.revision_id
656 , l_eco_revision_rec.change_notice
657 , l_eco_revision_rec.organization_id
658 , l_eco_revision_rec.rev
659 , l_eco_revision_rec.last_update_date
660 , l_eco_revision_rec.last_updated_by
661 , l_eco_revision_rec.creation_date
662 , l_eco_revision_rec.created_by
663 , l_eco_revision_rec.last_update_login
664 , l_eco_revision_rec.comments
665 , l_eco_revision_rec.attribute_category
666 , l_eco_revision_rec.attribute1
667 , l_eco_revision_rec.attribute2
668 , l_eco_revision_rec.attribute3
669 , l_eco_revision_rec.attribute4
670 , l_eco_revision_rec.attribute5
671 , l_eco_revision_rec.attribute6
672 , l_eco_revision_rec.attribute7
673 , l_eco_revision_rec.attribute8
674 , l_eco_revision_rec.attribute9
675 , l_eco_revision_rec.attribute10
676 FROM ENG_CHANGE_ORDER_REVISIONS
677 WHERE REVISION_ID = p_eco_revision_rec.revision_id
678 FOR UPDATE NOWAIT;
679
680 -- Row locked. Compare IN attributes to DB attributes.
681
682 IF ( (l_eco_revision_rec.attribute11 =
683 p_eco_revision_rec.attribute11) OR
684 ((p_eco_revision_rec.attribute11 = FND_API.G_MISS_CHAR) OR
685 ( (l_eco_revision_rec.attribute11 IS NULL) AND
686 (p_eco_revision_rec.attribute11 IS NULL))))
687 AND ( (l_eco_revision_rec.attribute12 =
688 p_eco_revision_rec.attribute12) OR
689 ((p_eco_revision_rec.attribute12 = FND_API.G_MISS_CHAR) OR
690 ( (l_eco_revision_rec.attribute12 IS NULL) AND
691 (p_eco_revision_rec.attribute12 IS NULL))))
692 AND ( (l_eco_revision_rec.attribute13 =
693 p_eco_revision_rec.attribute13) OR
694 ((p_eco_revision_rec.attribute13 = FND_API.G_MISS_CHAR) OR
695 ( (l_eco_revision_rec.attribute13 IS NULL) AND
696 (p_eco_revision_rec.attribute13 IS NULL))))
697 AND ( (l_eco_revision_rec.attribute14 =
698 p_eco_revision_rec.attribute14) OR
699 ((p_eco_revision_rec.attribute14 = FND_API.G_MISS_CHAR) OR
700 ( (l_eco_revision_rec.attribute14 IS NULL) AND
701 (p_eco_revision_rec.attribute14 IS NULL))))
702 AND ( (l_eco_revision_rec.attribute15 =
703 p_eco_revision_rec.attribute15) OR
704 ((p_eco_revision_rec.attribute15 = FND_API.G_MISS_CHAR) OR
705 ( (l_eco_revision_rec.attribute15 IS NULL) AND
706 (p_eco_revision_rec.attribute15 IS NULL))))
707 AND ( (l_eco_revision_rec.program_application_id =
708 p_eco_revision_rec.program_application_id) OR
709 ((p_eco_revision_rec.program_application_id = FND_API.G_MISS_NUM) OR
710 ( (l_eco_revision_rec.program_application_id IS NULL) AND
711 (p_eco_revision_rec.program_application_id IS NULL))))
712 AND ( (l_eco_revision_rec.program_id =
713 p_eco_revision_rec.program_id) OR
714 ((p_eco_revision_rec.program_id = FND_API.G_MISS_NUM) OR
715 ( (l_eco_revision_rec.program_id IS NULL) AND
716 (p_eco_revision_rec.program_id IS NULL))))
717 AND ( (l_eco_revision_rec.program_update_date =
718 p_eco_revision_rec.program_update_date) OR
719 ((p_eco_revision_rec.program_update_date = FND_API.G_MISS_DATE) OR
720 ( (l_eco_revision_rec.program_update_date IS NULL) AND
721 (p_eco_revision_rec.program_update_date IS NULL))))
722 AND ( (l_eco_revision_rec.request_id =
723 p_eco_revision_rec.request_id) OR
724 ((p_eco_revision_rec.request_id = FND_API.G_MISS_NUM) OR
725 ( (l_eco_revision_rec.request_id IS NULL) AND
726 (p_eco_revision_rec.request_id IS NULL))))
727 AND ( (l_eco_revision_rec.revision_id =
728 p_eco_revision_rec.revision_id) OR
729 ((p_eco_revision_rec.revision_id = FND_API.G_MISS_NUM) OR
730 ( (l_eco_revision_rec.revision_id IS NULL) AND
731 (p_eco_revision_rec.revision_id IS NULL))))
732 AND ( (l_eco_revision_rec.change_notice =
733 p_eco_revision_rec.change_notice) OR
734 ((p_eco_revision_rec.change_notice = FND_API.G_MISS_CHAR) OR
735 ( (l_eco_revision_rec.change_notice IS NULL) AND
736 (p_eco_revision_rec.change_notice IS NULL))))
737 AND ( (l_eco_revision_rec.organization_id =
738 p_eco_revision_rec.organization_id) OR
739 ((p_eco_revision_rec.organization_id = FND_API.G_MISS_NUM) OR
740 ( (l_eco_revision_rec.organization_id IS NULL) AND
741 (p_eco_revision_rec.organization_id IS NULL))))
742 AND ( (l_eco_revision_rec.rev =
743 p_eco_revision_rec.rev) OR
744 ((p_eco_revision_rec.rev = FND_API.G_MISS_CHAR) OR
745 ( (l_eco_revision_rec.rev IS NULL) AND
746 (p_eco_revision_rec.rev IS NULL))))
747 AND ( (l_eco_revision_rec.last_update_date =
748 p_eco_revision_rec.last_update_date) OR
749 ((p_eco_revision_rec.last_update_date = FND_API.G_MISS_DATE) OR
750 ( (l_eco_revision_rec.last_update_date IS NULL) AND
751 (p_eco_revision_rec.last_update_date IS NULL))))
752 AND ( (l_eco_revision_rec.last_updated_by =
753 p_eco_revision_rec.last_updated_by) OR
754 ((p_eco_revision_rec.last_updated_by = FND_API.G_MISS_NUM) OR
755 ( (l_eco_revision_rec.last_updated_by IS NULL) AND
756 (p_eco_revision_rec.last_updated_by IS NULL))))
757 AND ( (l_eco_revision_rec.creation_date =
758 p_eco_revision_rec.creation_date) OR
759 ((p_eco_revision_rec.creation_date = FND_API.G_MISS_DATE) OR
760 ( (l_eco_revision_rec.creation_date IS NULL) AND
761 (p_eco_revision_rec.creation_date IS NULL))))
762 AND ( (l_eco_revision_rec.created_by =
763 p_eco_revision_rec.created_by) OR
764 ((p_eco_revision_rec.created_by = FND_API.G_MISS_NUM) OR
765 ( (l_eco_revision_rec.created_by IS NULL) AND
766 (p_eco_revision_rec.created_by IS NULL))))
767 AND ( (l_eco_revision_rec.last_update_login =
768 p_eco_revision_rec.last_update_login) OR
769 ((p_eco_revision_rec.last_update_login = FND_API.G_MISS_NUM) OR
770 ( (l_eco_revision_rec.last_update_login IS NULL) AND
771 (p_eco_revision_rec.last_update_login IS NULL))))
772 AND ( (l_eco_revision_rec.comments =
773 p_eco_revision_rec.comments) OR
774 ((p_eco_revision_rec.comments = FND_API.G_MISS_CHAR) OR
775 ( (l_eco_revision_rec.comments IS NULL) AND
776 (p_eco_revision_rec.comments IS NULL))))
777 AND ( (l_eco_revision_rec.attribute_category =
778 p_eco_revision_rec.attribute_category) OR
779 ((p_eco_revision_rec.attribute_category = FND_API.G_MISS_CHAR) OR
780 ( (l_eco_revision_rec.attribute_category IS NULL) AND
781 (p_eco_revision_rec.attribute_category IS NULL))))
782 AND ( (l_eco_revision_rec.attribute1 =
783 p_eco_revision_rec.attribute1) OR
784 ((p_eco_revision_rec.attribute1 = FND_API.G_MISS_CHAR) OR
785 ( (l_eco_revision_rec.attribute1 IS NULL) AND
786 (p_eco_revision_rec.attribute1 IS NULL))))
787 AND ( (l_eco_revision_rec.attribute2 =
788 p_eco_revision_rec.attribute2) OR
789 ((p_eco_revision_rec.attribute2 = FND_API.G_MISS_CHAR) OR
790 ( (l_eco_revision_rec.attribute2 IS NULL) AND
791 (p_eco_revision_rec.attribute2 IS NULL))))
792 AND ( (l_eco_revision_rec.attribute3 =
793 p_eco_revision_rec.attribute3) OR
794 ((p_eco_revision_rec.attribute3 = FND_API.G_MISS_CHAR) OR
795 ( (l_eco_revision_rec.attribute3 IS NULL) AND
796 (p_eco_revision_rec.attribute3 IS NULL))))
797 AND ( (l_eco_revision_rec.attribute4 =
798 p_eco_revision_rec.attribute4) OR
799 ((p_eco_revision_rec.attribute4 = FND_API.G_MISS_CHAR) OR
800 ( (l_eco_revision_rec.attribute4 IS NULL) AND
801 (p_eco_revision_rec.attribute4 IS NULL))))
802 AND ( (l_eco_revision_rec.attribute5 =
803 p_eco_revision_rec.attribute5) OR
804 ((p_eco_revision_rec.attribute5 = FND_API.G_MISS_CHAR) OR
805 ( (l_eco_revision_rec.attribute5 IS NULL) AND
806 (p_eco_revision_rec.attribute5 IS NULL))))
807 AND ( (l_eco_revision_rec.attribute6 =
808 p_eco_revision_rec.attribute6) OR
809 ((p_eco_revision_rec.attribute6 = FND_API.G_MISS_CHAR) OR
810 ( (l_eco_revision_rec.attribute6 IS NULL) AND
811 (p_eco_revision_rec.attribute6 IS NULL))))
812 AND ( (l_eco_revision_rec.attribute7 =
813 p_eco_revision_rec.attribute7) OR
814 ((p_eco_revision_rec.attribute7 = FND_API.G_MISS_CHAR) OR
815 ( (l_eco_revision_rec.attribute7 IS NULL) AND
816 (p_eco_revision_rec.attribute7 IS NULL))))
817 AND ( (l_eco_revision_rec.attribute8 =
818 p_eco_revision_rec.attribute8) OR
819 ((p_eco_revision_rec.attribute8 = FND_API.G_MISS_CHAR) OR
820 ( (l_eco_revision_rec.attribute8 IS NULL) AND
821 (p_eco_revision_rec.attribute8 IS NULL))))
822 AND ( (l_eco_revision_rec.attribute9 =
823 p_eco_revision_rec.attribute9) OR
824 ((p_eco_revision_rec.attribute9 = FND_API.G_MISS_CHAR) OR
825 ( (l_eco_revision_rec.attribute9 IS NULL) AND
826 (p_eco_revision_rec.attribute9 IS NULL))))
827 AND ( (l_eco_revision_rec.attribute10 =
828 p_eco_revision_rec.attribute10) OR
829 ((p_eco_revision_rec.attribute10 = FND_API.G_MISS_CHAR) OR
830 ( (l_eco_revision_rec.attribute10 IS NULL) AND
831 (p_eco_revision_rec.attribute10 IS NULL))))
832 THEN
833
834 -- Row has not changed. Set out parameter.
835
836 x_eco_revision_rec := l_eco_revision_rec;
837
838 -- Set return status
839
840 x_return_status := FND_API.G_RET_STS_SUCCESS;
841 x_eco_revision_rec.return_status := FND_API.G_RET_STS_SUCCESS;
842
843 ELSE
844
845 -- Row has changed by another user.
846
847 x_return_status := FND_API.G_RET_STS_ERROR;
848 x_eco_revision_rec.return_status := FND_API.G_RET_STS_ERROR;
849 Eng_Eco_Pub.Log_Error( p_who_rec => ENG_GLOBALS.G_WHO_REC
850 , p_msg_name => 'OE_LOCK_ROW_CHANGED'
851 , x_err_text => x_err_text );
852 END IF;
853
854 EXCEPTION
855
856 WHEN NO_DATA_FOUND THEN
857
858 x_return_status := FND_API.G_RET_STS_ERROR;
859 x_eco_revision_rec.return_status := FND_API.G_RET_STS_ERROR;
860 Eng_Eco_Pub.Log_Error( p_who_rec => ENG_GLOBALS.G_WHO_REC
861 , p_msg_name => 'OE_LOCK_ROW_DELETED'
862 , x_err_text => x_err_text );
863
864 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
865
866 x_return_status := FND_API.G_RET_STS_ERROR;
867 x_eco_revision_rec.return_status := FND_API.G_RET_STS_ERROR;
868 Eng_Eco_Pub.Log_Error( p_who_rec => ENG_GLOBALS.G_WHO_REC
869 , p_msg_name => 'OE_LOCK_ROW_ALREADY_LOCKED'
870 , x_err_text => x_err_text );
871 WHEN OTHERS THEN
872
873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
874 x_eco_revision_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
875
876 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
877 THEN
878 x_err_text := G_PKG_NAME || '( Utility ) - Lock_Row' || substr(SQLERRM,1,60);
879 END IF;
880 */
881 END Lock_Row;
882
883 END ENG_Eco_Revision_Util;