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.
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: |
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: |
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
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;
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
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:
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
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: ------------------------------------------------------------------------
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
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
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: ------------------------------------------------------------------------
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;
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
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: ------------------------------------------------------------------------
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
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;
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
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)
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;