1 PACKAGE BODY pn_index_history_lines_pkg AS
2 -- $Header: PNTINHLB.pls 115.6 2002/11/12 23:06:12 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_LINES table.
11 | They include:
12 | INSERT_ROW - insert a row into PN_INDEX_HISTORY_LINES.
13 | DELETE_ROW - deletes a row from PN_INDEX_HISTORY_LINES.
14 | UPDATE_ROW - updates a row from PN_INDEX_HISTORY_LINES.
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 | 13-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_line_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_index_id IN NUMBER
36 ,x_index_date IN DATE
37 ,x_last_update_login IN NUMBER
38 ,x_index_figure IN NUMBER
39 ,x_index_estimate IN NUMBER
40 ,x_index_unadj_1 IN NUMBER
41 ,x_index_unadj_2 IN NUMBER
42 ,x_index_seasonally_unadj_1 IN NUMBER
43 ,x_index_seasonally_unadj_2 IN NUMBER
44 ,x_updated_flag IN VARCHAR2
45 ,x_attribute_category IN VARCHAR2
46 ,x_attribute1 IN VARCHAR2
47 ,x_attribute2 IN VARCHAR2
48 ,x_attribute3 IN VARCHAR2
49 ,x_attribute4 IN VARCHAR2
50 ,x_attribute5 IN VARCHAR2
51 ,x_attribute6 IN VARCHAR2
52 ,x_attribute7 IN VARCHAR2
53 ,x_attribute8 IN VARCHAR2
54 ,x_attribute9 IN VARCHAR2
55 ,x_attribute10 IN VARCHAR2
56 ,x_attribute11 IN VARCHAR2
57 ,x_attribute12 IN VARCHAR2
58 ,x_attribute13 IN VARCHAR2
59 ,x_attribute14 IN VARCHAR2
60 ,x_attribute15 IN VARCHAR2
61 ) IS
62 CURSOR c IS
63 SELECT ROWID
64 FROM pn_index_history_lines
65 WHERE index_line_id = x_index_line_id;
66
67 l_return_status VARCHAR2 (30) := NULL;
68 l_rowid VARCHAR2 (18) := NULL;
69 BEGIN
70
71 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_LINES_PKG.insert_row (+)');
72 -- If no INDEX_LINE_ID is provided, get one from sequence
73 IF (x_index_line_id IS NULL) THEN
74 SELECT pn_index_history_lines_s.NEXTVAL
75 INTO x_index_line_id
76 FROM DUAL;
77 END IF;
78
79 check_unq_index_line (l_return_status, x_index_id, x_index_line_id, x_index_date);
80
81 IF (l_return_status IS NOT NULL) THEN
82 app_exception.raise_exception;
83 END IF;
84
85 INSERT INTO pn_index_history_lines
86 (index_line_id
87 ,last_update_date
88 ,last_updated_by
89 ,creation_date
90 ,created_by
91 ,index_id
92 ,index_date
93 ,last_update_login
94 ,index_figure
95 ,index_estimate
96 ,index_unadj_1
97 ,index_unadj_2
98 ,index_seasonally_unadj_1
99 ,index_seasonally_unadj_2
100 ,updated_flag
101 ,attribute_category
102 ,attribute1
103 ,attribute2
104 ,attribute3
105 ,attribute4
106 ,attribute5
107 ,attribute6
108 ,attribute7
109 ,attribute8
110 ,attribute9
111 ,attribute10
112 ,attribute11
113 ,attribute12
114 ,attribute13
115 ,attribute14
116 ,attribute15
117 )
118 VALUES (x_index_line_id
119 ,x_last_update_date
120 ,x_last_updated_by
121 ,x_creation_date
122 ,x_created_by
123 ,x_index_id
124 ,x_index_date
125 ,x_last_update_login
126 ,x_index_figure
127 ,x_index_estimate
128 ,x_index_unadj_1
129 ,x_index_unadj_2
130 ,x_index_seasonally_unadj_1
131 ,x_index_seasonally_unadj_2
132 ,x_updated_flag
133 ,x_attribute_category
134 ,x_attribute1
135 ,x_attribute2
136 ,x_attribute3
137 ,x_attribute4
138 ,x_attribute5
139 ,x_attribute6
140 ,x_attribute7
141 ,x_attribute8
142 ,x_attribute9
143 ,x_attribute10
144 ,x_attribute11
145 ,x_attribute12
146 ,x_attribute13
147 ,x_attribute14
148 ,x_attribute15
149 );
150
151
152 -- Check if a valid record was created.
153 OPEN c;
154 FETCH c INTO x_rowid;
155
156 IF (c%NOTFOUND) THEN
157 CLOSE c;
158 RAISE NO_DATA_FOUND;
159 END IF;
160
161 CLOSE c;
162
163 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_LINES_PKG.insert_row (-)');
164 END insert_row;
165
166
167 ------------------------------------------------------------------------
168 -- PROCEDURE : UPDATE_ROW
169 ------------------------------------------------------------------------
170 PROCEDURE update_row (
171 x_rowid IN VARCHAR2
172 ,x_index_line_id IN NUMBER
173 ,x_last_update_date IN DATE
174 ,x_last_updated_by IN NUMBER
175 ,x_index_id IN NUMBER
176 ,x_index_date IN DATE
177 ,x_last_update_login IN NUMBER
178 ,x_index_figure IN NUMBER
179 ,x_index_estimate IN NUMBER
180 ,x_index_unadj_1 IN NUMBER
181 ,x_index_unadj_2 IN NUMBER
182 ,x_index_seasonally_unadj_1 IN NUMBER
183 ,x_index_seasonally_unadj_2 IN NUMBER
184 ,x_updated_flag IN VARCHAR2
185 ,x_attribute_category IN VARCHAR2
186 ,x_attribute1 IN VARCHAR2
187 ,x_attribute2 IN VARCHAR2
188 ,x_attribute3 IN VARCHAR2
189 ,x_attribute4 IN VARCHAR2
190 ,x_attribute5 IN VARCHAR2
191 ,x_attribute6 IN VARCHAR2
192 ,x_attribute7 IN VARCHAR2
193 ,x_attribute8 IN VARCHAR2
194 ,x_attribute9 IN VARCHAR2
195 ,x_attribute10 IN VARCHAR2
196 ,x_attribute11 IN VARCHAR2
197 ,x_attribute12 IN VARCHAR2
198 ,x_attribute13 IN VARCHAR2
199 ,x_attribute14 IN VARCHAR2
200 ,x_attribute15 IN VARCHAR2
201 ) IS
202 l_return_status VARCHAR2 (30) := NULL;
203 BEGIN
204
205 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_LINES_PKG.update_row (+)');
206
207 check_unq_index_line (l_return_status, x_index_id, x_index_line_id, x_index_date);
208
209 IF (l_return_status IS NOT NULL) THEN
210 app_exception.raise_exception;
211 END IF;
212
213 UPDATE pn_index_history_lines
214 SET last_update_date = x_last_update_date
215 ,last_updated_by = x_last_updated_by
216 ,index_id = x_index_id
217 ,index_date = x_index_date
218 ,last_update_login = x_last_update_login
219 ,index_figure = x_index_figure
220 ,index_estimate = x_index_estimate
221 ,index_unadj_1 = x_index_unadj_1
222 ,index_unadj_2 = x_index_unadj_2
223 ,index_seasonally_unadj_1 = x_index_seasonally_unadj_1
224 ,index_seasonally_unadj_2 = x_index_seasonally_unadj_2
225 ,updated_flag = x_updated_flag
226 ,attribute_category = x_attribute_category
227 ,attribute1 = x_attribute1
228 ,attribute2 = x_attribute2
229 ,attribute3 = x_attribute3
230 ,attribute4 = x_attribute4
231 ,attribute5 = x_attribute5
232 ,attribute6 = x_attribute6
233 ,attribute7 = x_attribute7
234 ,attribute8 = x_attribute8
235 ,attribute9 = x_attribute9
236 ,attribute10 = x_attribute10
237 ,attribute11 = x_attribute11
238 ,attribute12 = x_attribute12
239 ,attribute13 = x_attribute13
240 ,attribute14 = x_attribute14
241 ,attribute15 = x_attribute15
242 WHERE ROWID = x_rowid;
243
244 IF (SQL%NOTFOUND) THEN
245 RAISE NO_DATA_FOUND;
246 END IF;
247
248 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_LINES_PKG.update_row (-)');
249 END update_row;
250
251
252 ------------------------------------------------------------------------
253 -- PROCEDURE : LOCK_ROW
254 ------------------------------------------------------------------------
255 PROCEDURE lock_row (
256 x_rowid IN VARCHAR2
257 ,x_index_line_id IN NUMBER
258 ,x_index_id IN NUMBER
259 ,x_index_date IN DATE
260 ,x_index_figure IN NUMBER
261 ,x_index_estimate IN NUMBER
262 ,x_index_unadj_1 IN NUMBER
263 ,x_index_unadj_2 IN NUMBER
264 ,x_index_seasonally_unadj_1 IN NUMBER
265 ,x_index_seasonally_unadj_2 IN NUMBER
266 ,x_updated_flag IN VARCHAR2
267 ,x_attribute_category IN VARCHAR2
268 ,x_attribute1 IN VARCHAR2
269 ,x_attribute2 IN VARCHAR2
270 ,x_attribute3 IN VARCHAR2
271 ,x_attribute4 IN VARCHAR2
272 ,x_attribute5 IN VARCHAR2
273 ,x_attribute6 IN VARCHAR2
274 ,x_attribute7 IN VARCHAR2
275 ,x_attribute8 IN VARCHAR2
276 ,x_attribute9 IN VARCHAR2
277 ,x_attribute10 IN VARCHAR2
278 ,x_attribute11 IN VARCHAR2
279 ,x_attribute12 IN VARCHAR2
280 ,x_attribute13 IN VARCHAR2
281 ,x_attribute14 IN VARCHAR2
282 ,x_attribute15 IN VARCHAR2
283 ) IS
284 CURSOR c1 IS
285 SELECT *
286 FROM pn_index_history_lines
287 WHERE ROWID = x_rowid
288 FOR UPDATE OF index_line_id NOWAIT;
289
290 tlinfo c1%ROWTYPE;
291 BEGIN
292
293 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_LINES_PKG.lock_row (+)');
294 OPEN c1;
295 FETCH c1 INTO tlinfo;
296
297 IF (c1%NOTFOUND) THEN
298 CLOSE c1;
299 RETURN;
300 END IF;
301
302 CLOSE c1;
303
304 IF ( (tlinfo.index_line_id = x_index_line_id)
305 AND (tlinfo.index_id = x_index_id)
306 AND (tlinfo.index_date = x_index_date)
307 AND ( (tlinfo.index_figure = x_index_figure)
308 OR ( (tlinfo.index_figure IS NULL)
309 AND x_index_figure IS NULL
310 )
311 )
312 AND ( (tlinfo.index_estimate = x_index_estimate)
313 OR ( (tlinfo.index_estimate IS NULL)
314 AND x_index_estimate IS NULL
315 )
316 )
317 AND ( (tlinfo.index_unadj_1 = x_index_unadj_1)
318 OR ( (tlinfo.index_unadj_1 IS NULL)
319 AND x_index_unadj_1 IS NULL
320 )
321 )
322 AND ( (tlinfo.index_unadj_2 = x_index_unadj_2)
323 OR ( (tlinfo.index_unadj_2 IS NULL)
324 AND x_index_unadj_2 IS NULL
325 )
326 )
327 AND ( (tlinfo.index_seasonally_unadj_1 = x_index_seasonally_unadj_1)
328 OR ( (tlinfo.index_seasonally_unadj_1 IS NULL)
329 AND x_index_seasonally_unadj_1 IS NULL
330 )
331 )
332 AND ( (tlinfo.index_seasonally_unadj_2 = x_index_seasonally_unadj_2)
333 OR ( (tlinfo.index_seasonally_unadj_2 IS NULL)
334 AND x_index_seasonally_unadj_2 IS NULL
335 )
336 )
337 ) THEN
338 NULL;
339 ELSE
340 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
341 app_exception.raise_exception;
342 END IF;
343
344 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_LINES_PKG.lock_row (-)');
345 END lock_row;
346
347
348 ------------------------------------------------------------------------
349 -- PROCEDURE : DELETE_ROW
350 ------------------------------------------------------------------------
351 PROCEDURE delete_row (
352 x_rowid IN VARCHAR2
353 ) IS
354 BEGIN
355
356 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_LINES_PKG.delete_row (+)');
357 DELETE FROM pn_index_history_lines
358 WHERE ROWID = x_rowid;
359
360 IF (SQL%NOTFOUND) THEN
361 RAISE NO_DATA_FOUND;
362 END IF;
363
364 --PNP_DEBUG_PKG.debug (' PN_INDEX_HISTORY_LINES_PKG.delete_row (-)');
365 END delete_row;
366
367 PROCEDURE check_unq_index_line (
368 x_return_status IN OUT NOCOPY VARCHAR2
369 ,x_index_id IN NUMBER
370 ,x_index_line_id IN NUMBER
371 ,x_index_date IN DATE
372 ) IS
373 l_dummy NUMBER;
374 BEGIN
375 SELECT 1
376 INTO l_dummy
377 FROM DUAL
378 WHERE NOT EXISTS ( SELECT 1
379 FROM pn_index_history_lines
380 WHERE (TO_CHAR (index_date, 'MON-RRRR') =
381 TO_CHAR (x_index_date, 'MON-RRRR')
382 )
383 AND (index_id = x_index_id)
384 AND ( (x_index_line_id IS NULL)
385 OR (index_line_id <> x_index_line_id)
386 ));
387 EXCEPTION
388 WHEN NO_DATA_FOUND THEN
389 fnd_message.set_name ('PN', 'PN_DUP_INDEX_HIST_LINE');
390
391 -- fnd_message.set_token ('MONTH', x_index_date);
392 x_return_status := 'E';
393 END check_unq_index_line;
394 END pn_index_history_lines_pkg;
[Home] [Help]