1 package body JTF_RS_ROLE_RELATE_AUD_PKG as
2 /* $Header: jtfrstxb.pls 120.0 2005/05/11 08:22:41 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_ROLE_RELATE_AUDIT_ID in NUMBER,
6 X_ROLE_RELATE_ID in NUMBER,
7 X_NEW_ROLE_RESOURCE_TYPE in VARCHAR2,
8 X_OLD_ROLE_RESOURCE_TYPE in VARCHAR2,
9 X_NEW_ROLE_RESOURCE_ID in NUMBER,
10 X_OLD_ROLE_RESOURCE_ID in NUMBER,
11 X_NEW_ROLE_ID in NUMBER,
12 X_OLD_ROLE_ID in NUMBER,
13 X_NEW_START_DATE_ACTIVE in DATE,
14 X_OLD_START_DATE_ACTIVE in DATE,
15 X_NEW_END_DATE_ACTIVE in DATE,
16 X_OLD_END_DATE_ACTIVE in DATE,
17 X_NEW_OBJECT_VERSION_NUMBER in NUMBER,
18 X_OLD_OBJECT_VERSION_NUMBER in NUMBER,
19 X_CREATION_DATE in DATE,
20 X_CREATED_BY in NUMBER,
21 X_LAST_UPDATE_DATE in DATE,
22 X_LAST_UPDATED_BY in NUMBER,
23 X_LAST_UPDATE_LOGIN in NUMBER
24 ) is
25 cursor C is select ROWID from JTF_RS_ROLE_RELATE_AUD
26 where ROLE_RELATE_AUDIT_ID = X_ROLE_RELATE_AUDIT_ID
27 ;
28 begin
29 insert into JTF_RS_ROLE_RELATE_AUD (
30 ROLE_RELATE_AUDIT_ID,
31 ROLE_RELATE_ID,
32 NEW_ROLE_RESOURCE_TYPE,
33 OLD_ROLE_RESOURCE_TYPE,
34 NEW_ROLE_RESOURCE_ID,
35 OLD_ROLE_RESOURCE_ID,
36 NEW_ROLE_ID,
37 OLD_ROLE_ID,
38 NEW_START_DATE_ACTIVE,
39 OLD_START_DATE_ACTIVE,
40 NEW_END_DATE_ACTIVE,
41 OLD_END_DATE_ACTIVE,
42 NEW_OBJECT_VERSION_NUMBER,
43 OLD_OBJECT_VERSION_NUMBER,
44 CREATED_BY,
45 CREATION_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_DATE,
48 LAST_UPDATE_LOGIN
49 ) values (
50 X_ROLE_RELATE_AUDIT_ID,
51 X_ROLE_RELATE_ID,
52 X_NEW_ROLE_RESOURCE_TYPE,
53 X_OLD_ROLE_RESOURCE_TYPE,
54 X_NEW_ROLE_RESOURCE_ID,
55 X_OLD_ROLE_RESOURCE_ID,
56 X_NEW_ROLE_ID,
57 X_OLD_ROLE_ID,
58 X_NEW_START_DATE_ACTIVE,
59 X_OLD_START_DATE_ACTIVE,
60 X_NEW_END_DATE_ACTIVE,
61 X_OLD_END_DATE_ACTIVE,
62 X_NEW_OBJECT_VERSION_NUMBER,
63 X_OLD_OBJECT_VERSION_NUMBER,
64 X_CREATED_BY,
65 X_CREATION_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_DATE,
68 X_LAST_UPDATE_LOGIN);
69
70 open c;
71 fetch c into X_ROWID;
72 if (c%notfound) then
73 close c;
74 raise no_data_found;
75 end if;
76 close c;
77
78 end INSERT_ROW;
79
80 procedure LOCK_ROW (
81 X_ROLE_RELATE_AUDIT_ID in NUMBER,
82 X_ROLE_RELATE_ID in NUMBER,
83 X_NEW_ROLE_RESOURCE_TYPE in VARCHAR2,
84 X_OLD_ROLE_RESOURCE_TYPE in VARCHAR2,
85 X_NEW_ROLE_RESOURCE_ID in NUMBER,
86 X_OLD_ROLE_RESOURCE_ID in NUMBER,
87 X_NEW_ROLE_ID in NUMBER,
88 X_OLD_ROLE_ID in NUMBER,
89 X_NEW_START_DATE_ACTIVE in DATE,
90 X_OLD_START_DATE_ACTIVE in DATE,
91 X_NEW_END_DATE_ACTIVE in DATE,
92 X_OLD_END_DATE_ACTIVE in DATE,
93 X_NEW_OBJECT_VERSION_NUMBER in NUMBER,
94 X_OLD_OBJECT_VERSION_NUMBER in NUMBER
95 ) is
96 cursor c1 is select
97 ROLE_RELATE_ID,
98 NEW_ROLE_RESOURCE_TYPE,
99 OLD_ROLE_RESOURCE_TYPE,
100 NEW_ROLE_RESOURCE_ID,
101 OLD_ROLE_RESOURCE_ID,
102 NEW_ROLE_ID,
103 OLD_ROLE_ID,
104 NEW_START_DATE_ACTIVE,
105 OLD_START_DATE_ACTIVE,
106 NEW_END_DATE_ACTIVE,
107 OLD_END_DATE_ACTIVE,
108 NEW_OBJECT_VERSION_NUMBER,
109 OLD_OBJECT_VERSION_NUMBER
110 from JTF_RS_ROLE_RELATE_AUD
111 where ROLE_RELATE_AUDIT_ID = X_ROLE_RELATE_AUDIT_ID
112 for update of ROLE_RELATE_AUDIT_ID nowait;
113 tlinfo c1%rowtype;
114 begin
115 open c1;
116 fetch c1 into tlinfo;
117 if (c1%notfound) then
118 fnd_message.set_name('FND','FORM_RECORD_DELETED');
119 app_exception.raise_exception;
120 close c1;
121 end if;
122 close c1;
123
124 if ( (tlinfo.ROLE_RELATE_ID = X_ROLE_RELATE_ID)
125 AND ((tlinfo.NEW_ROLE_RESOURCE_TYPE = X_NEW_ROLE_RESOURCE_TYPE)
126 OR ((tlinfo.NEW_ROLE_RESOURCE_TYPE is null) AND (X_NEW_ROLE_RESOURCE_TYPE is null)))
127 AND ((tlinfo.OLD_ROLE_RESOURCE_TYPE = X_OLD_ROLE_RESOURCE_TYPE)
128 OR ((tlinfo.OLD_ROLE_RESOURCE_TYPE is null) AND (X_OLD_ROLE_RESOURCE_TYPE is null)))
129 AND ((tlinfo.NEW_ROLE_RESOURCE_ID = X_NEW_ROLE_RESOURCE_ID)
130 OR ((tlinfo.NEW_ROLE_RESOURCE_ID is null) AND (X_NEW_ROLE_RESOURCE_ID is null)))
131 AND ((tlinfo.OLD_ROLE_RESOURCE_ID = X_OLD_ROLE_RESOURCE_ID)
132 OR ((tlinfo.OLD_ROLE_RESOURCE_ID is null) AND (X_OLD_ROLE_RESOURCE_ID is null)))
133 AND ((tlinfo.NEW_ROLE_ID = X_NEW_ROLE_ID)
134 OR ((tlinfo.NEW_ROLE_ID is null) AND (X_NEW_ROLE_ID is null)))
135 AND ((tlinfo.OLD_ROLE_ID = X_OLD_ROLE_ID)
136 OR ((tlinfo.OLD_ROLE_ID is null) AND (X_OLD_ROLE_ID is null)))
137 AND ((tlinfo.NEW_START_DATE_ACTIVE = X_NEW_START_DATE_ACTIVE)
138 OR ((tlinfo.NEW_START_DATE_ACTIVE is null) AND (X_NEW_START_DATE_ACTIVE is null)))
139 AND ((tlinfo.OLD_START_DATE_ACTIVE = X_OLD_START_DATE_ACTIVE)
140 OR ((tlinfo.OLD_START_DATE_ACTIVE is null) AND (X_OLD_START_DATE_ACTIVE is null)))
141 AND ((tlinfo.NEW_END_DATE_ACTIVE = X_NEW_END_DATE_ACTIVE)
142 OR ((tlinfo.NEW_END_DATE_ACTIVE is null) AND (X_NEW_END_DATE_ACTIVE is null)))
143 AND ((tlinfo.OLD_END_DATE_ACTIVE = X_OLD_END_DATE_ACTIVE)
144 OR ((tlinfo.OLD_END_DATE_ACTIVE is null) AND (X_OLD_END_DATE_ACTIVE is null)))
145 AND ((tlinfo.NEW_OBJECT_VERSION_NUMBER = X_NEW_OBJECT_VERSION_NUMBER)
146 OR ((tlinfo.NEW_OBJECT_VERSION_NUMBER is null) AND (X_NEW_OBJECT_VERSION_NUMBER is null)))
147 AND ((tlinfo.OLD_OBJECT_VERSION_NUMBER = X_OLD_OBJECT_VERSION_NUMBER)
148 OR ((tlinfo.OLD_OBJECT_VERSION_NUMBER is null) AND (X_OLD_OBJECT_VERSION_NUMBER is null)))
149 ) then
150 null;
151 else
152 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
153 app_exception.raise_exception;
154 end if;
155 return;
156 end LOCK_ROW;
157
158 procedure UPDATE_ROW (
159 X_ROLE_RELATE_AUDIT_ID in NUMBER,
160 X_ROLE_RELATE_ID in NUMBER,
161 X_NEW_ROLE_RESOURCE_TYPE in VARCHAR2,
162 X_OLD_ROLE_RESOURCE_TYPE in VARCHAR2,
163 X_NEW_ROLE_RESOURCE_ID in NUMBER,
164 X_OLD_ROLE_RESOURCE_ID in NUMBER,
165 X_NEW_ROLE_ID in NUMBER,
166 X_OLD_ROLE_ID in NUMBER,
167 X_NEW_START_DATE_ACTIVE in DATE,
168 X_OLD_START_DATE_ACTIVE in DATE,
169 X_NEW_END_DATE_ACTIVE in DATE,
170 X_OLD_END_DATE_ACTIVE in DATE,
171 X_NEW_OBJECT_VERSION_NUMBER in NUMBER,
172 X_OLD_OBJECT_VERSION_NUMBER in NUMBER,
173 X_LAST_UPDATE_DATE in DATE,
174 X_LAST_UPDATED_BY in NUMBER,
175 X_LAST_UPDATE_LOGIN in NUMBER
176 ) is
177 begin
178 update JTF_RS_ROLE_RELATE_AUD set
179 ROLE_RELATE_ID = X_ROLE_RELATE_ID,
180 NEW_ROLE_RESOURCE_TYPE = X_NEW_ROLE_RESOURCE_TYPE,
181 OLD_ROLE_RESOURCE_TYPE = X_OLD_ROLE_RESOURCE_TYPE,
182 NEW_ROLE_RESOURCE_ID = X_NEW_ROLE_RESOURCE_ID,
183 OLD_ROLE_RESOURCE_ID = X_OLD_ROLE_RESOURCE_ID,
184 NEW_ROLE_ID = X_NEW_ROLE_ID,
185 OLD_ROLE_ID = X_OLD_ROLE_ID,
186 NEW_START_DATE_ACTIVE = X_NEW_START_DATE_ACTIVE,
187 OLD_START_DATE_ACTIVE = X_OLD_START_DATE_ACTIVE,
188 NEW_END_DATE_ACTIVE = X_NEW_END_DATE_ACTIVE,
189 OLD_END_DATE_ACTIVE = X_OLD_END_DATE_ACTIVE,
190 NEW_OBJECT_VERSION_NUMBER = X_NEW_OBJECT_VERSION_NUMBER,
191 OLD_OBJECT_VERSION_NUMBER = X_OLD_OBJECT_VERSION_NUMBER,
192 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
193 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
194 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
195 where ROLE_RELATE_AUDIT_ID = X_ROLE_RELATE_AUDIT_ID;
196
197 if (sql%notfound) then
198 raise no_data_found;
199 end if;
200 end UPDATE_ROW;
201
202 procedure DELETE_ROW (
203 X_ROLE_RELATE_AUDIT_ID in NUMBER
204 ) is
205 begin
206 delete from JTF_RS_ROLE_RELATE_AUD
207 where ROLE_RELATE_AUDIT_ID = X_ROLE_RELATE_AUDIT_ID;
208
209 if (sql%notfound) then
210 raise no_data_found;
211 end if;
212
213 end DELETE_ROW;
214
215 end JTF_RS_ROLE_RELATE_AUD_PKG;
[Home] [Help]