[Home] [Help]
PACKAGE BODY: APPS.IBC_STYLESHEETS_PKG
Source
1 PACKAGE BODY Ibc_Stylesheets_Pkg AS
2 /* $Header: ibctstyb.pls 120.2 2005/08/08 13:58:14 appldev ship $*/
3
4 -- Purpose: Table Handler for Ibc_Stylesheets table.
5
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- Sri Rangarajan 01/06/2002 Created Package
10 -- vicho 11/05/2002 Remove G_MISS defaulting on UPDATE_ROW
11 -- Sharma 07/04/2005 Modified LOAD_ROW, TRANSLATE_ROW and created
12 -- LOAD_SEED_ROW for R12 LCT standards bug 4411674
13 -- Sri.rangarajan 08/08/2005 Added the logic to not override the default stylesheet
14 -- set by the user.
15
16 PROCEDURE INSERT_ROW (
17 x_ROWID OUT NOCOPY VARCHAR2,
18 p_CONTENT_TYPE_CODE IN VARCHAR2,
19 p_CONTENT_ITEM_ID IN NUMBER,
20 p_OBJECT_VERSION_NUMBER IN NUMBER,
21 p_default_stylesheet_flag IN VARCHAR2,
22 p_CREATION_DATE IN DATE,
23 p_CREATED_BY IN NUMBER,
24 p_LAST_UPDATE_DATE IN DATE,
25 p_LAST_UPDATED_BY IN NUMBER,
26 p_LAST_UPDATE_LOGIN IN NUMBER
27 ) IS
28 CURSOR C IS SELECT ROWID FROM IBC_STYLESHEETS
29 WHERE CONTENT_TYPE_CODE = p_CONTENT_TYPE_CODE
30 AND CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
31 ;
32 BEGIN
33 INSERT INTO IBC_STYLESHEETS (
34 CONTENT_TYPE_CODE,
35 CONTENT_ITEM_ID,
36 default_stylesheet_flag,
37 OBJECT_VERSION_NUMBER,
38 CREATION_DATE,
39 CREATED_BY,
40 LAST_UPDATE_DATE,
41 LAST_UPDATED_BY,
42 LAST_UPDATE_LOGIN
43 ) VALUES (
44 p_CONTENT_TYPE_CODE,
45 p_CONTENT_ITEM_ID,
46 p_default_stylesheet_flag,
47 p_OBJECT_VERSION_NUMBER,
48 DECODE(p_creation_date, Fnd_Api.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
49 p_creation_date),
50 DECODE(p_created_by, Fnd_Api.G_MISS_NUM, Fnd_Global.user_id,
51 NULL, Fnd_Global.user_id, p_created_by),
52 DECODE(p_last_update_date, Fnd_Api.G_MISS_DATE, SYSDATE, NULL, SYSDATE,
53 p_last_update_date),
54 DECODE(p_last_updated_by, Fnd_Api.G_MISS_NUM, Fnd_Global.user_id,
55 NULL, Fnd_Global.user_id, p_last_updated_by),
56 DECODE(p_last_update_login, Fnd_Api.G_MISS_NUM, Fnd_Global.login_id,
57 NULL, Fnd_Global.login_id, p_last_update_login)
58 );
59
60 OPEN c;
61 FETCH c INTO x_ROWID;
62 IF (c%NOTFOUND) THEN
63 CLOSE c;
64 RAISE NO_DATA_FOUND;
65 END IF;
66 CLOSE c;
67
68 END INSERT_ROW;
69
70 PROCEDURE LOCK_ROW (
71 p_CONTENT_TYPE_CODE IN VARCHAR2,
72 p_CONTENT_ITEM_ID IN NUMBER,
73 p_OBJECT_VERSION_NUMBER IN NUMBER
74 ) IS
75 CURSOR c IS SELECT
76 OBJECT_VERSION_NUMBER
77 FROM IBC_STYLESHEETS
78 WHERE CONTENT_TYPE_CODE = p_CONTENT_TYPE_CODE
79 AND CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
80 FOR UPDATE OF CONTENT_TYPE_CODE NOWAIT;
81 recinfo c%ROWTYPE;
82
83 BEGIN
84 OPEN c;
85 FETCH c INTO recinfo;
86 IF (c%NOTFOUND) THEN
87 CLOSE c;
88 Fnd_Message.set_name('FND', 'FORM_RECORD_DELETED');
89 App_Exception.raise_exception;
90 END IF;
91 CLOSE c;
92 IF ( (recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
93 ) THEN
94 NULL;
95 ELSE
96 Fnd_Message.set_name('FND', 'FORM_RECORD_CHANGED');
97 App_Exception.raise_exception;
98 END IF;
99
100 END LOCK_ROW;
101
102 PROCEDURE UPDATE_ROW (
103 p_CONTENT_ITEM_ID IN NUMBER,
104 p_CONTENT_TYPE_CODE IN VARCHAR2,
105 p_default_stylesheet_flag IN VARCHAR2,
106 p_LAST_UPDATED_BY IN NUMBER,
107 p_LAST_UPDATE_DATE IN DATE,
108 p_LAST_UPDATE_LOGIN IN NUMBER,
109 p_OBJECT_VERSION_NUMBER IN NUMBER
110 ) IS
111 BEGIN
112 UPDATE IBC_STYLESHEETS SET
113 CONTENT_TYPE_CODE = DECODE(p_CONTENT_TYPE_CODE,Fnd_Api.G_MISS_CHAR,NULL,NULL,CONTENT_TYPE_CODE,p_CONTENT_TYPE_CODE),
114 CONTENT_ITEM_ID = DECODE(p_CONTENT_ITEM_ID,Fnd_Api.G_MISS_NUM,NULL,NULL,CONTENT_ITEM_ID,p_CONTENT_ITEM_ID),
115 default_stylesheet_flag = DECODE(p_default_stylesheet_flag,Fnd_Api.G_MISS_CHAR,'F',NULL,default_stylesheet_flag,p_default_stylesheet_flag),
116 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
117 last_update_date = DECODE(p_last_update_date, Fnd_Api.G_MISS_DATE, SYSDATE,
118 NULL, SYSDATE, p_last_update_date),
119 last_updated_by = DECODE(p_last_updated_by, Fnd_Api.G_MISS_NUM,
120 Fnd_Global.user_id, NULL, Fnd_Global.user_id,
121 p_last_updated_by),
122 last_update_login = DECODE(p_last_update_login, Fnd_Api.G_MISS_NUM,
123 Fnd_Global.login_id, NULL, Fnd_Global.login_id,
124 p_last_update_login)
125 WHERE CONTENT_TYPE_CODE = p_CONTENT_TYPE_CODE
126 AND CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
127 AND object_version_number = DECODE(p_object_version_number,
128 Fnd_Api.G_MISS_NUM,object_version_number,
129 NULL,object_version_number,
130 p_object_version_number);
131
132 IF (SQL%NOTFOUND) THEN
133 RAISE NO_DATA_FOUND;
134 END IF;
135
136 END UPDATE_ROW;
137
138 PROCEDURE DELETE_ROW (
139 p_CONTENT_TYPE_CODE IN VARCHAR2,
140 p_CONTENT_ITEM_ID IN NUMBER
141 ) IS
142 BEGIN
143
144 DELETE FROM IBC_STYLESHEETS
145 WHERE CONTENT_TYPE_CODE = p_CONTENT_TYPE_CODE
146 AND CONTENT_ITEM_ID = p_CONTENT_ITEM_ID;
147
148 IF (SQL%NOTFOUND) THEN
149 RAISE NO_DATA_FOUND;
150 END IF;
151 END DELETE_ROW;
152
153 PROCEDURE LOAD_SEED_ROW (
154 p_UPLOAD_MODE IN VARCHAR2,
155 p_CONTENT_ITEM_ID NUMBER,
156 p_CONTENT_TYPE_CODE VARCHAR2,
157 p_default_stylesheet_flag VARCHAR2, --DEFAULT 'F',
158 p_OWNER VARCHAR2,
159 p_LAST_UPDATE_DATE IN VARCHAR2) IS
160 BEGIN
161 IF (p_UPLOAD_MODE = 'NLS') THEN
162 NULL;
163 ELSE
164 IBC_STYLESHEETS_PKG.LOAD_ROW (
165 p_UPLOAD_MODE => p_UPLOAD_MODE,
166 p_CONTENT_TYPE_CODE => p_CONTENT_TYPE_CODE,
167 p_CONTENT_ITEM_ID => p_CONTENT_ITEM_ID,
168 p_default_stylesheet_flag => p_default_stylesheet_flag,
169 p_OWNER =>p_OWNER,
170 p_LAST_UPDATE_DATE => p_LAST_UPDATE_DATE );
171 END IF;
172 END;
173
174
175 PROCEDURE LOAD_ROW (
176 p_UPLOAD_MODE IN VARCHAR2,
177 p_CONTENT_ITEM_ID NUMBER,
178 p_CONTENT_TYPE_CODE VARCHAR2,
179 p_default_stylesheet_flag VARCHAR2, --DEFAULT 'F',
180 p_OWNER VARCHAR2,
181 p_LAST_UPDATE_DATE IN VARCHAR2) IS
182
183 l_default_stylesheet_flag CHAR(1);
184
185 BEGIN
186
187 l_default_stylesheet_flag := p_default_stylesheet_flag;
188
189
190 DECLARE
191 l_user_id NUMBER := 0;
192 l_row_id VARCHAR2(64);
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 -- get updatedby and update_date values if existing in db
206 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE INTO db_user_id, db_last_update_date
207 FROM IBC_STYLESHEETS
208 WHERE CONTENT_TYPE_CODE = p_CONTENT_TYPE_CODE
209 AND CONTENT_ITEM_ID = p_CONTENT_ITEM_ID
210 AND object_version_number = DECODE(object_version_number,
211 Fnd_Api.G_MISS_NUM,object_version_number,
212 NULL,object_version_number,
213 object_version_number);
214 DECLARE
215 l_temp INTEGER;
216
217 BEGIN
218
219 l_temp := 0;
220
221 IF (l_default_stylesheet_flag ='T') THEN
222
223 -- if the user has set some other stylesheet
224 -- as the default stylesheet. Don't override the
225 -- user settings
226 --
227 SELECT count(*) INTO l_temp from IBC_STYLESHEETS
228 WHERE content_type_code = p_CONTENT_TYPE_CODE
229 and CONTENT_ITEM_ID <> p_CONTENT_ITEM_ID
230 and default_stylesheet_flag = 'T';
231
232 If l_temp <> 0 THEN
233 l_default_stylesheet_flag :='F';
234 END IF;
235
236 END IF;
237 EXCEPTION
238 when others THEN
239 NULL;
240 END;
241
242 IF (FND_LOAD_UTIL.UPLOAD_TEST(l_user_id, l_last_update_date,
243 db_user_id, db_last_update_date, p_upload_mode )) THEN
244
245 UPDATE_ROW (
246 p_CONTENT_ITEM_ID => p_CONTENT_ITEM_ID,
247 p_CONTENT_TYPE_CODE => p_CONTENT_TYPE_CODE,
248 p_default_stylesheet_flag => nvl(l_default_stylesheet_flag,FND_API.G_MISS_CHAR),
249 p_LAST_UPDATED_BY => l_user_id,
250 p_LAST_UPDATE_DATE => SYSDATE,
251 p_LAST_UPDATE_LOGIN => 0,
252 p_OBJECT_VERSION_NUMBER => NULL );
253 END IF;
254
255 EXCEPTION
256 WHEN NO_DATA_FOUND THEN
257 INSERT_ROW (
258 X_ROWID => l_row_id,
259 p_CONTENT_ITEM_ID => p_CONTENT_ITEM_ID,
260 p_CONTENT_TYPE_CODE => p_CONTENT_TYPE_CODE,
261 p_default_stylesheet_flag => p_default_stylesheet_flag,
262 p_OBJECT_VERSION_NUMBER => 1,
263 p_CREATION_DATE => SYSDATE,
264 p_CREATED_BY => l_user_id,
265 p_LAST_UPDATE_DATE => SYSDATE,
266 p_LAST_UPDATED_BY => l_user_id,
267 p_LAST_UPDATE_LOGIN => 0);
268 END;
269 END LOAD_ROW;
270
271 END Ibc_Stylesheets_Pkg;