[Home] [Help]
PACKAGE BODY: APPS.IBC_CITEM_KEYWORDS_PKG
Source
1 PACKAGE BODY Ibc_Citem_keywords_Pkg AS
2 /* $Header: ibctkwdb.pls 120.1 2005/05/31 00:04:33 appldev $*/
3
4 -- Purpose: Table Handler for ibc_citem_keywords table.
5
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- Edward Nunez 01/06/2002 Created Package
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBC_CITEM_KEYWORDS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ibctkwdb.pls';
13
14 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
15 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
16
17
18
19 PROCEDURE INSERT_ROW (
20 x_ROWID OUT NOCOPY VARCHAR2,
21 p_CONTENT_ITEM_ID IN NUMBER,
22 p_KEYWORD IN VARCHAR2,
23 p_OBJECT_VERSION_NUMBER IN NUMBER,
24 p_CREATION_DATE IN DATE DEFAULT NULL,
25 p_CREATED_BY IN NUMBER DEFAULT NULL,
26 p_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
27 p_LAST_UPDATED_BY IN NUMBER DEFAULT NULL
28 ) IS
29 CURSOR C IS SELECT ROWID FROM IBC_CITEM_KEYWORDS
30 WHERE CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
31 AND KEYWORD = p_KEYWORD;
32
33 G_API_NAME CONSTANT VARCHAR2(30) := 'INSERT_ROW';
34
35 BEGIN
36
37 OPEN c;
38 FETCH c INTO X_ROWID;
39 IF (c%NOTFOUND) THEN
40 INSERT INTO IBC_CITEM_KEYWORDS(
41 CONTENT_ITEM_ID,
42 KEYWORD,
43 OBJECT_VERSION_NUMBER,
44 CREATION_DATE,
45 CREATED_BY,
46 LAST_UPDATE_DATE,
47 LAST_UPDATED_BY
48 ) VALUES (
49 p_content_item_id
50 ,p_keyword
51 ,DECODE(p_object_version_number,NULL,1,NULL,1,p_object_version_number)
52 ,DECODE(p_creation_date,NULL,SYSDATE,NULL,SYSDATE,p_creation_date)
53 ,DECODE(p_created_by,NULL,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_created_by)
54 ,DECODE(p_last_update_date,NULL,SYSDATE,NULL,SYSDATE,p_last_update_date)
55 ,DECODE(p_last_updated_by,NULL,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
56 );
57 END IF;
58 CLOSE c;
59
60 END INSERT_ROW;
61
62
63 PROCEDURE LOCK_ROW (
64 p_CONTENT_ITEM_ID IN NUMBER,
65 p_KEYWORD IN VARCHAR2,
66 p_OBJECT_VERSION_NUMBER IN NUMBER
67 ) IS
68 CURSOR c IS SELECT
69 OBJECT_VERSION_NUMBER
70 FROM IBC_CITEM_KEYWORDS
71 WHERE CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
72 FOR UPDATE OF CONTENT_ITEM_ID NOWAIT;
73 recinfo c%ROWTYPE;
74
75
76 G_API_NAME CONSTANT VARCHAR2(30) := 'LOCK_ROW';
77
78 BEGIN
79 OPEN c;
80 FETCH c INTO recinfo;
81 IF (c%NOTFOUND) THEN
82 CLOSE c;
83 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
84 app_exception.raise_exception;
85 END IF;
86 CLOSE c;
87 IF NOT ((recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)) THEN
88 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
89 app_exception.raise_exception;
90 END IF;
91
92 END LOCK_ROW;
93
94
95 PROCEDURE UPDATE_ROW (
96 p_CONTENT_ITEM_ID IN NUMBER,
97 p_KEYWORD IN VARCHAR2,
98 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER -- DEFAULT NULL
99 ,p_last_update_date IN DATE -- DEFAULT NULL
100 ,p_last_updated_by IN NUMBER -- DEFAULT NULL
101 ) IS
102
103 G_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_ROW';
104
105 BEGIN
106 UPDATE IBC_CITEM_KEYWORDS SET
107 object_version_number = NVL(object_version_number,0) + 1
108 ,last_update_date = DECODE(p_last_update_date,NULL,SYSDATE,NULL,SYSDATE,p_last_update_date)
109 ,last_updated_by = DECODE(p_last_updated_by,NULL,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
110 WHERE CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
111 AND KEYWORD = p_KEYWORD
112 AND object_version_number = DECODE(px_object_version_number,
113 NULL,
114 object_version_number,
115 NULL,
116 object_version_number,
117 px_object_version_number);
118
119 px_object_version_number := px_object_version_number + 1;
120
121 IF (SQL%NOTFOUND) THEN
122 FND_MESSAGE.Set_Name('IBC', 'IBC_ERROR_RETURNED');
123 FND_MESSAGE.Set_token('PKG_NAME' , G_pkg_name);
124 FND_MESSAGE.Set_token('API_NAME' , G_api_name);
125 FND_MSG_PUB.ADD;
126 RAISE FND_API.G_EXC_ERROR;
127 END IF;
128
129 END UPDATE_ROW;
130
131
132 PROCEDURE DELETE_ROW (
133 p_CONTENT_ITEM_ID IN NUMBER
134 ,p_KEYWORD IN VARCHAR2
135 ) IS
136
137 G_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_ROW';
138
139 BEGIN
140
141 DELETE FROM IBC_CITEM_KEYWORDS
142 WHERE CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
143 AND KEYWORD = p_KEYWORD;
144
145 IF (SQL%NOTFOUND) THEN
146 FND_MESSAGE.Set_Name('IBC', 'IBC_ERROR_RETURNED');
147 FND_MESSAGE.Set_token('PKG_NAME' , G_pkg_name);
148 FND_MESSAGE.Set_token('API_NAME' , G_api_name);
149 FND_MSG_PUB.ADD;
150 RAISE FND_API.G_EXC_ERROR;
151 -- RAISE NO_DATA_FOUND;
152 END IF;
153 END DELETE_ROW;
154
155
156 PROCEDURE LOAD_ROW (
157 p_CONTENT_ITEM_ID IN NUMBER,
158 p_KEYWORD IN VARCHAR2,
159 p_OWNER IN VARCHAR2
160 ) IS
161 BEGIN
162 DECLARE
163 l_user_id NUMBER := 0;
164 l_row_id VARCHAR2(64);
165 lx_object_version_number NUMBER := NULL;
166 BEGIN
167 IF (p_OWNER = 'SEED') THEN
168 l_user_id := 1;
169 END IF;
170
171 UPDATE_ROW (
172 p_content_item_id => p_content_item_id
173 ,p_keyword => p_keyword
174 ,p_last_updated_by => l_user_id
175 ,p_last_update_date => SYSDATE
176 ,px_object_version_number => lx_object_version_number
177 );
178
179 EXCEPTION
180 WHEN FND_API.G_EXC_ERROR THEN
181 INSERT_ROW (
182 X_ROWID => l_row_id,
183 p_CONTENT_ITEM_ID => p_CONTENT_ITEM_ID,
184 p_KEYWORD => p_KEYWORD,
185 p_OBJECT_VERSION_NUMBER => 1,
186 p_CREATION_DATE => SYSDATE,
187 p_CREATED_BY => l_user_id,
188 p_LAST_UPDATE_DATE => SYSDATE,
189 p_LAST_UPDATED_BY => l_user_id);
190 END;
191 END LOAD_ROW;
192
193
194 END Ibc_citem_keywords_Pkg;