1 package body PA_STATUS_LIST_ITEMS_PKG as
2 /* $Header: PACISITB.pls 120.2 2005/08/22 05:13:16 sukhanna noship $ */
3 procedure INSERT_ROW (
4 X_RECORD_VERSION_NUMBER in NUMBER,
5 X_STATUS_LIST_ITEM_ID in out NOCOPY NUMBER, --File.Sql.39 bug 4440895
6 X_STATUS_LIST_ID in NUMBER,
7 X_PROJECT_STATUS_CODE in VARCHAR2,
8 X_CREATION_DATE in DATE ,
9 X_CREATED_BY in NUMBER ,
10 X_LAST_UPDATE_DATE in DATE ,
11 X_LAST_UPDATED_BY in NUMBER ,
12 X_LAST_UPDATE_LOGIN in NUMBER
13 ) is
14 cursor C is select ROWID from PA_STATUS_LIST_ITEMS
15 where STATUS_LIST_ITEM_ID = X_STATUS_LIST_ITEM_ID
16 ;
17 l_rowid ROWID;
18 l_status_list_item_id NUMBER;
19 begin
20 l_status_list_item_id := x_status_list_item_id; --Added for bug 4565156
21 IF (X_STATUS_LIST_ITEM_ID = -99) THEN
22 SELECT pa_status_list_items_s.nextval into l_status_list_item_id FROM dual;
23 X_STATUS_LIST_ITEM_ID := l_status_list_item_id;
24 END IF;
25
26 insert into PA_STATUS_LIST_ITEMS (
27 STATUS_LIST_ID,
28 RECORD_VERSION_NUMBER,
29 PROJECT_STATUS_CODE,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_LOGIN,
35 STATUS_LIST_ITEM_ID
36 ) values(
37 X_STATUS_LIST_ID,
38 1,
39 X_PROJECT_STATUS_CODE,
40 sysdate,
41 fnd_global.user_id,
42 sysdate,
43 fnd_global.user_id,
44 fnd_global.user_id,
45 X_STATUS_LIST_ITEM_ID);
46
47 open c;
48 fetch c into l_rowid;
49 if (c%notfound) then
50 close c;
51 raise no_data_found;
52 end if;
53 close c;
54
55 exception --Added for bug 4565156
56 when others then
57 x_status_list_item_id := l_status_list_item_id;
58 raise;
59 end INSERT_ROW;
60
61 procedure LOCK_ROW (
62 X_STATUS_LIST_ITEM_ID in NUMBER,
63 X_STATUS_LIST_ID in NUMBER,
64 X_RECORD_VERSION_NUMBER in NUMBER,
65 X_PROJECT_STATUS_CODE in VARCHAR2
66 ) is
67 cursor c1 is select
68 STATUS_LIST_ID,
69 RECORD_VERSION_NUMBER,
70 PROJECT_STATUS_CODE,
71 STATUS_LIST_ITEM_ID
72 from PA_STATUS_LIST_ITEMS
73 where STATUS_LIST_ITEM_ID = X_STATUS_LIST_ITEM_ID
74 for update of STATUS_LIST_ITEM_ID nowait;
75 begin
76 for tlinfo in c1 loop
77 if ( (tlinfo.STATUS_LIST_ITEM_ID = X_STATUS_LIST_ITEM_ID)
78 AND (tlinfo.STATUS_LIST_ID = X_STATUS_LIST_ID)
79 AND ((tlinfo.RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER)
80 OR ((tlinfo.RECORD_VERSION_NUMBER is null) AND (X_RECORD_VERSION_NUMBER is null)))
81 AND (tlinfo.PROJECT_STATUS_CODE = X_PROJECT_STATUS_CODE)
82 ) then
83 null;
84 else
85 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
86 app_exception.raise_exception;
87 end if;
88 end loop;
89 return;
90 end LOCK_ROW;
91
92 procedure UPDATE_ROW (
93 X_RECORD_VERSION_NUMBER in NUMBER,
94 X_STATUS_LIST_ITEM_ID in NUMBER,
95 X_STATUS_LIST_ID in NUMBER,
96 X_PROJECT_STATUS_CODE in VARCHAR2,
97 X_LAST_UPDATE_DATE in DATE ,
98 X_LAST_UPDATED_BY in NUMBER ,
99 X_LAST_UPDATE_LOGIN in NUMBER ,
100 X_CREATION_DATE IN DATE ,
101 X_CREATED_BY IN NUMBER
102 ) is
103 begin
104 update PA_STATUS_LIST_ITEMS set
105 STATUS_LIST_ID = X_STATUS_LIST_ID,
106 RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER,
107 PROJECT_STATUS_CODE = X_PROJECT_STATUS_CODE,
108 STATUS_LIST_ITEM_ID = X_STATUS_LIST_ITEM_ID,
109 LAST_UPDATE_DATE = sysdate,
110 LAST_UPDATED_BY = fnd_global.user_id,
111 LAST_UPDATE_LOGIN = fnd_global.user_id,
112 CREATION_DATE = X_CREATION_DATE,
113 CREATED_BY = X_CREATED_BY
114 where STATUS_LIST_ITEM_ID = X_STATUS_LIST_ITEM_ID;
115
116 if (sql%notfound) then
117 raise no_data_found;
118 end if;
119 end UPDATE_ROW;
120
121 procedure DELETE_ROW (
122 X_STATUS_LIST_ITEM_ID in NUMBER,
123 X_RECORD_VERSION_NUMBER in NUMBER
124 ) is
125 begin
126 delete from PA_STATUS_LIST_ITEMS
127 where STATUS_LIST_ITEM_ID = X_STATUS_LIST_ITEM_ID;
128
129 if (sql%notfound) then
130 raise no_data_found;
131 end if;
132
133 end DELETE_ROW;
134
135 end PA_STATUS_LIST_ITEMS_PKG;
[Home] [Help]