[Home] [Help]
PACKAGE BODY: APPS.IBC_CITEM_VERSION_LABELS_PKG
Source
1 PACKAGE BODY Ibc_Citem_Version_Labels_Pkg AS
2 /* $Header: ibctcvlb.pls 120.1 2005/07/29 15:07:45 appldev ship $*/
3
4 -- Purpose: Table Handler for Ibc_Citem_Version_Labels table.
5
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- Sri Rangarajan 01/06/2002 Created Package
10 -- shitij.vatsa 11/04/2002 Updated for FND_API.G_MISS_XXX
11 -- SHARMA 07/04/2005 Modified LOAD_ROW, TRANSLATE_ROW and created
12 -- LOAD_SEED_ROW for R12 LCT standards bug 4411674
13
14 PROCEDURE INSERT_ROW (
15 x_rowid OUT NOCOPY VARCHAR2
16 ,p_content_item_id IN NUMBER
17 ,p_label_code IN VARCHAR2
18 ,p_citem_version_id IN NUMBER
19 ,p_object_version_number IN NUMBER
20 ,p_creation_date IN DATE --DEFAULT NULL
21 ,p_created_by IN NUMBER --DEFAULT NULL
22 ,p_last_update_date IN DATE --DEFAULT NULL
23 ,p_last_updated_by IN NUMBER --DEFAULT NULL
24 ,p_last_update_login IN NUMBER --DEFAULT NULL
25 ) IS
26 CURSOR C IS SELECT ROWID FROM IBC_CITEM_VERSION_LABELS
27 WHERE CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
28 AND LABEL_CODE = p_LABEL_CODE;
29 BEGIN
30 INSERT INTO IBC_CITEM_VERSION_LABELS (
31 CONTENT_ITEM_ID,
32 LABEL_CODE,
33 CITEM_VERSION_ID,
34 OBJECT_VERSION_NUMBER,
35 CREATION_DATE,
36 CREATED_BY,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_LOGIN
40 ) VALUES (
41 p_content_item_id
42 ,p_label_code
43 ,p_citem_version_id
44 ,DECODE(p_object_version_number,FND_API.G_MISS_NUM,1,NULL,1,p_object_version_number)
45 ,DECODE(p_creation_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_creation_date)
46 ,DECODE(p_created_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_created_by)
47 ,DECODE(p_last_update_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
48 ,DECODE(p_last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
49 ,DECODE(p_last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,p_last_update_login)
50 );
51
52
53 OPEN c;
54 FETCH c INTO x_ROWID;
55 IF (c%NOTFOUND) THEN
56 CLOSE c;
57 RAISE NO_DATA_FOUND;
58 END IF;
59 CLOSE c;
60
61 END INSERT_ROW;
62
63 PROCEDURE LOCK_ROW (
64 p_CONTENT_ITEM_ID IN NUMBER,
65 p_LABEL_CODE IN VARCHAR2,
66 p_CITEM_VERSION_ID IN NUMBER,
67 p_OBJECT_VERSION_NUMBER IN NUMBER
68 ) IS
69 CURSOR c IS SELECT
70 CITEM_VERSION_ID,
71 OBJECT_VERSION_NUMBER
72 FROM IBC_CITEM_VERSION_LABELS
73 WHERE CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
74 AND LABEL_CODE = p_LABEL_CODE
75 FOR UPDATE OF CONTENT_ITEM_ID NOWAIT;
76 recinfo c%ROWTYPE;
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 ( (recinfo.CITEM_VERSION_ID = p_CITEM_VERSION_ID)
88 AND (recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
89 ) THEN
90 NULL;
91 ELSE
92 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
93 app_exception.raise_exception;
94 END IF;
95
96 END LOCK_ROW;
97
98 PROCEDURE UPDATE_ROW (
99 p_content_item_id IN NUMBER
100 ,p_label_code IN VARCHAR2
101 ,p_citem_version_id IN NUMBER --DEFAULT NULL
102 ,p_last_updated_by IN NUMBER --DEFAULT NULL
103 ,p_last_update_date IN DATE --DEFAULT NULL
104 ,p_last_update_login IN NUMBER --DEFAULT NULL
105 ,p_object_version_number IN NUMBER --DEFAULT NULL
106 ) IS
107 BEGIN
108 UPDATE IBC_CITEM_VERSION_LABELS SET
109 citem_version_id = DECODE(p_citem_version_id,FND_API.G_MISS_NUM,NULL,NULL,citem_version_id,p_citem_version_id)
110 ,object_version_number = NVL(object_version_number,0) + 1
111 ,last_update_date = DECODE(p_last_update_date,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,p_last_update_date)
112 ,last_updated_by = DECODE(p_last_updated_by,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,p_last_updated_by)
113 ,last_update_login = DECODE(p_last_update_login,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,p_last_update_login)
114 WHERE CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
115 AND LABEL_CODE = p_LABEL_CODE
116 AND object_version_number = DECODE(p_object_version_number,
117 FND_API.G_MISS_NUM,
118 object_version_number,
119 NULL,
120 object_version_number,
121 p_object_version_number);
122
123 IF (SQL%NOTFOUND) THEN
124 RAISE NO_DATA_FOUND;
125 END IF;
126
127 END UPDATE_ROW;
128
129 PROCEDURE DELETE_ROW (
130 p_CONTENT_ITEM_ID IN NUMBER,
131 p_LABEL_CODE IN VARCHAR2
132 ) IS
133 BEGIN
134
135 DELETE FROM IBC_CITEM_VERSION_LABELS
136 WHERE CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
137 AND LABEL_CODE = p_LABEL_CODE;
138
139 IF (SQL%NOTFOUND) THEN
140 RAISE NO_DATA_FOUND;
141 END IF;
142 END DELETE_ROW;
143
144 PROCEDURE LOAD_SEED_ROW (
145 p_UPLOAD_MODE IN VARCHAR2,
146 p_CONTENT_ITEM_ID IN NUMBER,
147 p_LABEL_CODE IN VARCHAR2,
148 p_CITEM_VERSION_ID IN NUMBER,
149 p_OWNER IN VARCHAR2,
150 p_LAST_UPDATE_DATE IN VARCHAR2) IS
151
152 l_temp NUMBER;
153
154 BEGIN
155 IF ( p_UPLOAD_MODE = 'NLS') THEN
156 NULL;
157 ELSE
158 BEGIN
159
160 SELECT '1' INTO l_temp
161 FROM IBC_LABELS_B
162 WHERE LABEL_CODE = p_LABEL_CODE;
163
164
165 SELECT '1' INTO l_temp FROM IBC_CITEM_VERSION_LABELS
166 WHERE last_updated_by <> 1
167 AND CITEM_VERSION_ID = p_CITEM_VERSION_ID
168 AND CONTENT_ITEM_ID = p_CONTENT_ITEM_ID;
169
170 EXCEPTION WHEN no_data_found THEN
171 Ibc_Citem_Version_Labels_Pkg.LOAD_ROW(
172 p_UPLOAD_MODE => p_UPLOAD_MODE,
173 p_CONTENT_ITEM_ID => p_UPLOAD_MODE,
174 p_LABEL_CODE => p_UPLOAD_MODE,
175 p_CITEM_VERSION_ID => p_UPLOAD_MODE,
176 p_OWNER => p_UPLOAD_MODE,
177 p_LAST_UPDATE_DATE => p_UPLOAD_MODE);
178 END;
179 END IF;
180 END LOAD_SEED_ROW;
181
182 PROCEDURE LOAD_ROW (
183 p_UPLOAD_MODE IN VARCHAR2,
184 p_CONTENT_ITEM_ID IN NUMBER,
185 p_LABEL_CODE IN VARCHAR2,
186 p_CITEM_VERSION_ID IN NUMBER,
187 p_OWNER IN VARCHAR2,
188 p_LAST_UPDATE_DATE IN VARCHAR2) IS
189
190 l_user_id NUMBER := 0;
191 l_row_id VARCHAR2(64);
192 l_object_version_number NUMBER := FND_API.G_MISS_NUM;
193 l_last_update_date DATE;
194
195 db_user_id NUMBER := 0;
196 db_last_update_date DATE;
197
198 BEGIN
199 --get last updated by user id
200 l_user_id := FND_LOAD_UTIL.OWNER_ID(p_OWNER);
201
202 --translate data type VARCHAR2 to DATE for last_update_date
203 l_last_update_date := nvl(TO_DATE(p_last_update_date, 'YYYY/MM/DD'),SYSDATE);
204
205
206 -- get updatedby and update_date values if existing in db
207 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
208 FROM IBC_CITEM_VERSION_LABELS
209 WHERE CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
210 AND LABEL_CODE = p_LABEL_CODE;
211
212 IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
213 db_user_id, db_last_update_date, p_upload_mode )) THEN
214 BEGIN
215 Ibc_Citem_Version_Labels_Pkg.UPDATE_ROW (
216 p_content_item_id => NVL(p_content_item_id,FND_API.G_MISS_NUM)
217 ,p_label_code => NVL(p_label_code,FND_API.G_MISS_CHAR)
218 ,p_citem_version_id => NVL(p_citem_version_id,FND_API.G_MISS_NUM)
219 ,p_last_updated_by => l_user_id
220 ,p_last_update_date => SYSDATE
221 ,p_last_update_login => 0
222 ,p_object_version_number => l_object_version_number);
223 EXCEPTION
224 WHEN NO_DATA_FOUND THEN
225 Ibc_Citem_Version_Labels_Pkg.INSERT_ROW (
226 x_rowid => l_row_id,
227 p_CONTENT_ITEM_ID => p_CONTENT_ITEM_ID,
228 p_LABEL_CODE => p_LABEL_CODE,
229 p_CITEM_VERSION_ID => p_CITEM_VERSION_ID,
230 p_OBJECT_VERSION_NUMBER => 1,
231 p_CREATION_DATE => SYSDATE,
232 p_CREATED_BY => l_user_id,
233 p_LAST_UPDATE_DATE => SYSDATE,
234 p_LAST_UPDATED_BY => l_user_id,
235 p_LAST_UPDATE_LOGIN => 0);
236 END;
237 END IF;
238 END LOAD_ROW;
239
240
241 END Ibc_Citem_Version_Labels_Pkg;