[Home] [Help]
PACKAGE BODY: APPS.PN_INDEX_HISTORY_HEADERS_PKG
Source
1 PACKAGE BODY pn_index_history_headers_pkg AS
2 -- $Header: PNTINHHB.pls 115.6 2002/11/12 23:05:51 stripath ship $
3
4 /*============================================================================+
5 | Copyright (c) 2001 Oracle Corporation
6 | Redwood Shores, California, USA
7 | All rights reserved.
8 | DESCRIPTION
9 |
10 | These procedures consist are used a table handlers for the PN_INDEX_HISTORY_HEADERS table.
11 | They include:
12 | INSERT_ROW - insert a row into PN_INDEX_HISTORY_HEADERS.
13 | DELETE_ROW - deletes a row from PN_INDEX_HISTORY_HEADERS.
14 | UPDATE_ROW - updates a row from PN_INDEX_HISTORY_HEADERS.
15 | LOCKS_ROW - will check if a row has been modified since being queried by form.
16 |
17 |
18 | HISTORY
19 | 24-APR-2001 jbreyes o Created
20 | 10-DEC-2001 Mrinal Misra o Added dbdrv command.
21 | 15-JAN-2002 Mrinal Misra o In dbdrv command changed phase=pls to phase=plb.
22 | Added checkfile.Ref. Bug# 2184724.
23 +==============================================================================*/
24
25 ------------------------------------------------------------------------
26 -- PROCEDURE : INSERT_ROW
27 ------------------------------------------------------------------------
28 PROCEDURE insert_row (
29 x_rowid IN OUT NOCOPY VARCHAR2
30 ,x_index_id IN OUT NOCOPY NUMBER
31 ,x_last_update_date IN DATE
32 ,x_last_updated_by IN NUMBER
33 ,x_creation_date IN DATE
34 ,x_created_by IN NUMBER
35 ,x_name IN VARCHAR2
36 ,x_last_update_login IN NUMBER
37 ,x_source IN VARCHAR2
38 ,x_comments IN VARCHAR2
39 ,x_attribute_category IN VARCHAR2
40 ,x_attribute1 IN VARCHAR2
41 ,x_attribute2 IN VARCHAR2
42 ,x_attribute3 IN VARCHAR2
43 ,x_attribute4 IN VARCHAR2
44 ,x_attribute5 IN VARCHAR2
45 ,x_attribute6 IN VARCHAR2
46 ,x_attribute7 IN VARCHAR2
47 ,x_attribute8 IN VARCHAR2
48 ,x_attribute9 IN VARCHAR2
49 ,x_attribute10 IN VARCHAR2
50 ,x_attribute11 IN VARCHAR2
51 ,x_attribute12 IN VARCHAR2
52 ,x_attribute13 IN VARCHAR2
53 ,x_attribute14 IN VARCHAR2
54 ,x_attribute15 IN VARCHAR2
55 ) IS
56 CURSOR c IS
57 SELECT ROWID
58 FROM pn_index_history_headers
59 WHERE index_id = x_index_id;
60
61 l_return_status VARCHAR2 (30) := NULL;
62 l_rowid VARCHAR2 (18) := NULL;
63 BEGIN
64
65 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_HEADERS_PKG.insert_row (+)');
66 -- If no INDEX_ID is provided, get one from sequence
67 IF (x_index_id IS NULL) THEN
68 SELECT pn_index_history_headers_s.NEXTVAL
69 INTO x_index_id
70 FROM DUAL;
71 END IF;
72
73 check_unq_index_type (l_return_status, x_index_id, x_name);
74
75 IF (l_return_status IS NOT NULL) THEN
76 app_exception.raise_exception;
77 END IF;
78
79
80 -- IF (l_return_status IS NOT NULL) THEN APP_EXCEPTION.Raise_Exception; END IF;
81 INSERT INTO pn_index_history_headers
82 (index_id
83 ,last_update_date
84 ,last_updated_by
85 ,creation_date
86 ,created_by
87 ,name
88 ,last_update_login
89 ,source
90 ,comments
91 ,attribute_category
92 ,attribute1
93 ,attribute2
94 ,attribute3
95 ,attribute4
96 ,attribute5
97 ,attribute6
98 ,attribute7
99 ,attribute8
100 ,attribute9
101 ,attribute10
102 ,attribute11
103 ,attribute12
104 ,attribute13
105 ,attribute14
106 ,attribute15
107 )
108 VALUES (x_index_id
109 ,x_last_update_date
110 ,x_last_updated_by
111 ,x_creation_date
112 ,x_created_by
113 ,x_name
114 ,x_last_update_login
115 ,x_source
116 ,x_comments
117 ,x_attribute_category
118 ,x_attribute1
119 ,x_attribute2
120 ,x_attribute3
121 ,x_attribute4
122 ,x_attribute5
123 ,x_attribute6
124 ,x_attribute7
125 ,x_attribute8
126 ,x_attribute9
127 ,x_attribute10
128 ,x_attribute11
129 ,x_attribute12
130 ,x_attribute13
131 ,x_attribute14
132 ,x_attribute15
133 );
134
135
136 -- Check if a valid record was created.
137 OPEN c;
138 FETCH c INTO x_rowid;
139
140 IF (c%NOTFOUND) THEN
141 CLOSE c;
142 RAISE NO_DATA_FOUND;
143 END IF;
144
145 CLOSE c;
146
147 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_HEADERS_PKG.insert_row (-)');
148 END insert_row;
149
150
151 ------------------------------------------------------------------------
152 -- PROCEDURE : UPDATE_ROW
153 ------------------------------------------------------------------------
154 PROCEDURE update_row (
155 x_rowid IN VARCHAR2
156 ,x_index_id IN NUMBER
157 ,x_last_update_date IN DATE
158 ,x_last_updated_by IN NUMBER
159 ,x_name IN VARCHAR2
160 ,x_last_update_login IN NUMBER
161 ,x_source IN VARCHAR2
162 ,x_comments IN VARCHAR2
163 ,x_attribute_category IN VARCHAR2
164 ,x_attribute1 IN VARCHAR2
165 ,x_attribute2 IN VARCHAR2
166 ,x_attribute3 IN VARCHAR2
167 ,x_attribute4 IN VARCHAR2
168 ,x_attribute5 IN VARCHAR2
169 ,x_attribute6 IN VARCHAR2
170 ,x_attribute7 IN VARCHAR2
171 ,x_attribute8 IN VARCHAR2
172 ,x_attribute9 IN VARCHAR2
173 ,x_attribute10 IN VARCHAR2
174 ,x_attribute11 IN VARCHAR2
175 ,x_attribute12 IN VARCHAR2
176 ,x_attribute13 IN VARCHAR2
177 ,x_attribute14 IN VARCHAR2
178 ,x_attribute15 IN VARCHAR2
179 ) IS
180 l_return_status VARCHAR2 (30) := NULL;
181 BEGIN
182
183 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_HEADERS_PKG.update_row (+)');
184
185
186 check_unq_index_type (l_return_status, x_index_id, x_name);
187
188 IF (l_return_status IS NOT NULL) THEN
189 app_exception.raise_exception;
190 END IF;
191
192 UPDATE pn_index_history_headers
193 SET last_update_date = x_last_update_date
194 ,last_updated_by = x_last_updated_by
195 ,name = x_name
196 ,last_update_login = x_last_update_login
197 ,source = x_source
198 ,comments = x_comments
199 ,attribute_category = x_attribute_category
200 ,attribute1 = x_attribute1
201 ,attribute2 = x_attribute2
202 ,attribute3 = x_attribute3
203 ,attribute4 = x_attribute4
204 ,attribute5 = x_attribute5
205 ,attribute6 = x_attribute6
206 ,attribute7 = x_attribute7
207 ,attribute8 = x_attribute8
208 ,attribute9 = x_attribute9
209 ,attribute10 = x_attribute10
210 ,attribute11 = x_attribute11
211 ,attribute12 = x_attribute12
212 ,attribute13 = x_attribute13
213 ,attribute14 = x_attribute14
214 ,attribute15 = x_attribute15
215 WHERE ROWID = x_rowid;
216
217 IF (SQL%NOTFOUND) THEN
218 RAISE NO_DATA_FOUND;
219 END IF;
220
221 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_HEADERS_PKG.update_row (-)');
222 END update_row;
223
224
225 ------------------------------------------------------------------------
226 -- PROCEDURE : LOCK_ROW
227 ------------------------------------------------------------------------
228 PROCEDURE lock_row (
229 x_rowid IN VARCHAR2
230 ,x_index_id IN NUMBER
231 ,x_name IN VARCHAR2
232 ,x_source IN VARCHAR2
233 ,x_comments IN VARCHAR2
234 ,x_attribute_category IN VARCHAR2
235 ,x_attribute1 IN VARCHAR2
236 ,x_attribute2 IN VARCHAR2
237 ,x_attribute3 IN VARCHAR2
238 ,x_attribute4 IN VARCHAR2
239 ,x_attribute5 IN VARCHAR2
240 ,x_attribute6 IN VARCHAR2
241 ,x_attribute7 IN VARCHAR2
242 ,x_attribute8 IN VARCHAR2
243 ,x_attribute9 IN VARCHAR2
244 ,x_attribute10 IN VARCHAR2
245 ,x_attribute11 IN VARCHAR2
246 ,x_attribute12 IN VARCHAR2
247 ,x_attribute13 IN VARCHAR2
248 ,x_attribute14 IN VARCHAR2
249 ,x_attribute15 IN VARCHAR2
250 ) IS
251 CURSOR c1 IS
252 SELECT *
253 FROM pn_index_history_headers
254 WHERE ROWID = x_rowid
255 FOR UPDATE OF index_id NOWAIT;
256
257 tlinfo c1%ROWTYPE;
258 BEGIN
259
260 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_HEADERS_PKG.lock_row (+)');
261 OPEN c1;
262 FETCH c1 INTO tlinfo;
263
264 IF (c1%NOTFOUND) THEN
265 CLOSE c1;
266 RETURN;
267 END IF;
268
269 CLOSE c1;
270
271 IF ( (tlinfo.index_id = x_index_id)
272 AND (tlinfo.name = x_name)
273 AND ( (tlinfo.source = x_source)
274 OR ( (tlinfo.source IS NULL)
275 AND x_source IS NULL
276 )
277 )
278 AND ( (tlinfo.comments = x_comments)
279 OR ( (tlinfo.comments IS NULL)
280 AND x_comments IS NULL
281 )
282 )
283 ) THEN
284 NULL;
285 ELSE
286 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
287 app_exception.raise_exception;
288 END IF;
289
290 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_HEADERS_PKG.lock_row (-)');
291 END lock_row;
292
293
294 ------------------------------------------------------------------------
295 -- PROCEDURE : DELETE_ROW
296 ------------------------------------------------------------------------
297 PROCEDURE delete_row (
298 x_rowid IN VARCHAR2
299 ) IS
300 BEGIN
301
302 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_HEADERS_PKG.delete_row (+)');
303 DELETE FROM pn_index_history_headers
304 WHERE ROWID = x_rowid;
305
306 IF (SQL%NOTFOUND) THEN
307 RAISE NO_DATA_FOUND;
308 END IF;
309
310 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_HEADERS_PKG.delete_row (-)');
311 END delete_row;
312
313 PROCEDURE check_unq_index_type (
314 x_return_status IN OUT NOCOPY VARCHAR2
315 ,x_index_id IN NUMBER
316 ,x_name IN VARCHAR2
317 ) IS
318 l_dummy NUMBER;
319 BEGIN
320 SELECT 1
321 INTO l_dummy
322 FROM DUAL
323 WHERE NOT EXISTS ( SELECT 1
324 FROM pn_index_history_headers
325 WHERE (name = x_name)
326 AND ( (x_index_id IS NULL)
327 OR (index_id <> x_index_id)
328 ));
329 EXCEPTION
330 WHEN NO_DATA_FOUND THEN
331 fnd_message.set_name ('PN', 'PN_DUP_INDEX_TYPE');
332
333 -- fnd_message.set_token ('NAME', x_name);
334 x_return_status := 'E';
335 END check_unq_index_type;
336 END pn_index_history_headers_pkg;