1 PACKAGE BODY AK_UNIQUE_KEY_COLUMNS_PKG as
2 /* $Header: AKDUKCB.pls 115.5 2002/01/17 12:31:12 pkm ship $ */
3 --######################################################################
4 procedure INSERT_ROW (
5 X_UNIQUE_KEY_NAME in VARCHAR2,
6 X_UNIQUE_KEY_SEQUENCE in NUMBER,
7 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
8 X_ATTRIBUTE_CODE in VARCHAR2,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER,
14 X_ATTRIBUTE_CATEGORY in VARCHAR2,
15 X_ATTRIBUTE1 in VARCHAR2,
16 X_ATTRIBUTE2 in VARCHAR2,
17 X_ATTRIBUTE3 in VARCHAR2,
18 X_ATTRIBUTE4 in VARCHAR2,
19 X_ATTRIBUTE5 in VARCHAR2,
20 X_ATTRIBUTE6 in VARCHAR2,
21 X_ATTRIBUTE7 in VARCHAR2,
22 X_ATTRIBUTE8 in VARCHAR2,
23 X_ATTRIBUTE9 in VARCHAR2,
24 X_ATTRIBUTE10 in VARCHAR2,
25 X_ATTRIBUTE11 in VARCHAR2,
26 X_ATTRIBUTE12 in VARCHAR2,
27 X_ATTRIBUTE13 in VARCHAR2,
28 X_ATTRIBUTE14 in VARCHAR2,
29 X_ATTRIBUTE15 in VARCHAR2)
30 is
31 begin
32 insert into AK_UNIQUE_KEY_COLUMNS (
33 UNIQUE_KEY_NAME,
34 UNIQUE_KEY_SEQUENCE,
35 ATTRIBUTE_APPLICATION_ID,
36 ATTRIBUTE_CODE,
37 CREATION_DATE,
38 CREATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATED_BY,
41 LAST_UPDATE_LOGIN,
42 ATTRIBUTE_CATEGORY,
43 ATTRIBUTE1,
44 ATTRIBUTE2,
45 ATTRIBUTE3,
46 ATTRIBUTE4,
47 ATTRIBUTE5,
48 ATTRIBUTE6,
49 ATTRIBUTE7,
50 ATTRIBUTE8,
51 ATTRIBUTE9,
52 ATTRIBUTE10,
53 ATTRIBUTE11,
54 ATTRIBUTE12,
55 ATTRIBUTE13,
56 ATTRIBUTE14,
57 ATTRIBUTE15)
58 values (
59 X_UNIQUE_KEY_NAME,
60 X_UNIQUE_KEY_SEQUENCE,
61 X_ATTRIBUTE_APPLICATION_ID,
62 X_ATTRIBUTE_CODE,
63 X_CREATION_DATE,
64 X_CREATED_BY,
65 X_LAST_UPDATE_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_LOGIN,
68 X_ATTRIBUTE_CATEGORY,
69 X_ATTRIBUTE1,
70 X_ATTRIBUTE2,
71 X_ATTRIBUTE3,
72 X_ATTRIBUTE4,
73 X_ATTRIBUTE5,
74 X_ATTRIBUTE6,
75 X_ATTRIBUTE7,
76 X_ATTRIBUTE8,
77 X_ATTRIBUTE9,
78 X_ATTRIBUTE10,
79 X_ATTRIBUTE11,
80 X_ATTRIBUTE12,
81 X_ATTRIBUTE13,
82 X_ATTRIBUTE14,
83 X_ATTRIBUTE15);
84 end INSERT_ROW;
85 --######################################################################
86 --I-O is based on the unique key columns
87 -- UNIQUE_KEY_NAME and UNIQUE_KEY_SEQUENCE,
88 -- which are NOT the Primary Key.
89 procedure LOCK_ROW (
90 X_UNIQUE_KEY_NAME in VARCHAR2,
91 X_UNIQUE_KEY_SEQUENCE in NUMBER,
92 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
93 X_ATTRIBUTE_CODE in VARCHAR2,
94 X_ATTRIBUTE_CATEGORY in VARCHAR2,
95 X_ATTRIBUTE1 in VARCHAR2,
96 X_ATTRIBUTE2 in VARCHAR2,
97 X_ATTRIBUTE3 in VARCHAR2,
98 X_ATTRIBUTE4 in VARCHAR2,
99 X_ATTRIBUTE5 in VARCHAR2,
100 X_ATTRIBUTE6 in VARCHAR2,
101 X_ATTRIBUTE7 in VARCHAR2,
102 X_ATTRIBUTE8 in VARCHAR2,
103 X_ATTRIBUTE9 in VARCHAR2,
104 X_ATTRIBUTE10 in VARCHAR2,
105 X_ATTRIBUTE11 in VARCHAR2,
106 X_ATTRIBUTE12 in VARCHAR2,
107 X_ATTRIBUTE13 in VARCHAR2,
108 X_ATTRIBUTE14 in VARCHAR2,
109 X_ATTRIBUTE15 in VARCHAR2)
110 is
111 cursor c is select
112 ATTRIBUTE_CODE,
113 ATTRIBUTE_APPLICATION_ID,
114 ATTRIBUTE_CATEGORY,
115 ATTRIBUTE1,
116 ATTRIBUTE2,
117 ATTRIBUTE3,
118 ATTRIBUTE4,
119 ATTRIBUTE5,
120 ATTRIBUTE6,
121 ATTRIBUTE7,
122 ATTRIBUTE8,
123 ATTRIBUTE9,
124 ATTRIBUTE10,
125 ATTRIBUTE11,
126 ATTRIBUTE12,
127 ATTRIBUTE13,
128 ATTRIBUTE14,
129 ATTRIBUTE15
130 from AK_UNIQUE_KEY_COLUMNS
131 where UNIQUE_KEY_NAME = X_UNIQUE_KEY_NAME
132 and UNIQUE_KEY_SEQUENCE = X_UNIQUE_KEY_SEQUENCE
133 for update of UNIQUE_KEY_NAME nowait;
134 recinfo c%rowtype;
135 begin
136 open c;
137 fetch c into recinfo;
138 if (c%notfound) then
139 close c;
140 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
141 app_exception.raise_exception;
142 end if;
143 close c;
144 if ( (recinfo.ATTRIBUTE_CODE = X_ATTRIBUTE_CODE)
145 AND (recinfo.ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID)
146 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
147 OR ((recinfo.ATTRIBUTE_CATEGORY is null)
148 AND (X_ATTRIBUTE_CATEGORY is null)))
149 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
150 OR ((recinfo.ATTRIBUTE1 is null)
151 AND (X_ATTRIBUTE1 is null)))
152 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
153 OR ((recinfo.ATTRIBUTE2 is null)
154 AND (X_ATTRIBUTE2 is null)))
155 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
156 OR ((recinfo.ATTRIBUTE3 is null)
157 AND (X_ATTRIBUTE3 is null)))
158 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
159 OR ((recinfo.ATTRIBUTE4 is null)
160 AND (X_ATTRIBUTE4 is null)))
161 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
162 OR ((recinfo.ATTRIBUTE5 is null)
163 AND (X_ATTRIBUTE5 is null)))
164 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
165 OR ((recinfo.ATTRIBUTE6 is null)
166 AND (X_ATTRIBUTE6 is null)))
167 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
168 OR ((recinfo.ATTRIBUTE7 is null)
169 AND (X_ATTRIBUTE7 is null)))
170 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
171 OR ((recinfo.ATTRIBUTE8 is null)
172 AND (X_ATTRIBUTE8 is null)))
173 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
174 OR ((recinfo.ATTRIBUTE9 is null)
175 AND (X_ATTRIBUTE9 is null)))
176 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
177 OR ((recinfo.ATTRIBUTE10 is null)
178 AND (X_ATTRIBUTE10 is null)))
179 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
180 OR ((recinfo.ATTRIBUTE11 is null)
181 AND (X_ATTRIBUTE11 is null)))
182 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
183 OR ((recinfo.ATTRIBUTE12 is null)
184 AND (X_ATTRIBUTE12 is null)))
185 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
186 OR ((recinfo.ATTRIBUTE13 is null)
187 AND (X_ATTRIBUTE13 is null)))
188 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
189 OR ((recinfo.ATTRIBUTE14 is null)
190 AND (X_ATTRIBUTE14 is null)))
191 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
192 OR ((recinfo.ATTRIBUTE15 is null)
193 AND (X_ATTRIBUTE15 is null)))
194 ) then
195 null;
196 else
197 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
198 app_exception.raise_exception;
199 end if;
200 return;
201 end LOCK_ROW;
202 --######################################################################
203 --I-O is based on the unique key columns
204 -- UNIQUE_KEY_NAME and UNIQUE_KEY_SEQUENCE,
205 -- which are NOT the Primary Key.
206 procedure UPDATE_ROW (
207 X_UNIQUE_KEY_NAME in VARCHAR2,
208 X_UNIQUE_KEY_SEQUENCE in NUMBER,
209 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
210 X_ATTRIBUTE_CODE in VARCHAR2,
211 X_LAST_UPDATE_DATE in DATE,
212 X_LAST_UPDATED_BY in NUMBER,
213 X_LAST_UPDATE_LOGIN in NUMBER,
214 X_ATTRIBUTE_CATEGORY in VARCHAR2,
215 X_ATTRIBUTE1 in VARCHAR2,
216 X_ATTRIBUTE2 in VARCHAR2,
217 X_ATTRIBUTE3 in VARCHAR2,
218 X_ATTRIBUTE4 in VARCHAR2,
219 X_ATTRIBUTE5 in VARCHAR2,
220 X_ATTRIBUTE6 in VARCHAR2,
221 X_ATTRIBUTE7 in VARCHAR2,
222 X_ATTRIBUTE8 in VARCHAR2,
223 X_ATTRIBUTE9 in VARCHAR2,
224 X_ATTRIBUTE10 in VARCHAR2,
225 X_ATTRIBUTE11 in VARCHAR2,
226 X_ATTRIBUTE12 in VARCHAR2,
227 X_ATTRIBUTE13 in VARCHAR2,
228 X_ATTRIBUTE14 in VARCHAR2,
229 X_ATTRIBUTE15 in VARCHAR2
230 ) is
231 begin
232 update AK_UNIQUE_KEY_COLUMNS set
233 UNIQUE_KEY_NAME = X_UNIQUE_KEY_NAME,
234 UNIQUE_KEY_SEQUENCE = X_UNIQUE_KEY_SEQUENCE,
235 ATTRIBUTE_APPLICATION_ID = X_ATTRIBUTE_APPLICATION_ID,
236 ATTRIBUTE_CODE = X_ATTRIBUTE_CODE,
237 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
238 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
239 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
240 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
241 ATTRIBUTE1 = X_ATTRIBUTE1,
242 ATTRIBUTE2 = X_ATTRIBUTE2,
243 ATTRIBUTE3 = X_ATTRIBUTE3,
244 ATTRIBUTE4 = X_ATTRIBUTE4,
245 ATTRIBUTE5 = X_ATTRIBUTE5,
246 ATTRIBUTE6 = X_ATTRIBUTE6,
247 ATTRIBUTE7 = X_ATTRIBUTE7,
248 ATTRIBUTE8 = X_ATTRIBUTE8,
249 ATTRIBUTE9 = X_ATTRIBUTE9,
250 ATTRIBUTE10 = X_ATTRIBUTE10,
251 ATTRIBUTE11 = X_ATTRIBUTE11,
252 ATTRIBUTE12 = X_ATTRIBUTE12,
253 ATTRIBUTE13 = X_ATTRIBUTE13,
254 ATTRIBUTE14 = X_ATTRIBUTE14,
255 ATTRIBUTE15 = X_ATTRIBUTE15
256 where UNIQUE_KEY_NAME = X_UNIQUE_KEY_NAME
257 and UNIQUE_KEY_SEQUENCE = X_UNIQUE_KEY_SEQUENCE;
258 if (sql%notfound) then
259 raise no_data_found;
260 end if;
261 end UPDATE_ROW;
262 --######################################################################
263 --I-O is based on the unique key columns
264 -- UNIQUE_KEY_NAME and UNIQUE_KEY_SEQUENCE,
265 -- which are NOT the Primary Key.
266 procedure DELETE_ROW (
267 X_UNIQUE_KEY_NAME in VARCHAR2,
268 X_UNIQUE_KEY_SEQUENCE in NUMBER,
269 X_ATTRIBUTE_APPLICATION_ID in NUMBER,
270 X_ATTRIBUTE_CODE in VARCHAR2) is
271 begin
272 delete from AK_UNIQUE_KEY_COLUMNS
273 where UNIQUE_KEY_NAME = X_UNIQUE_KEY_NAME
274 and UNIQUE_KEY_SEQUENCE = X_UNIQUE_KEY_SEQUENCE;
275 if (sql%notfound) then
276 raise no_data_found;
277 end if;
278 end DELETE_ROW;
279 --######################################################################
280 end AK_UNIQUE_KEY_COLUMNS_PKG;
[Home] [Help]