1 package body DOM_DOCUMENT_REVISIONS_PKG as
2 /* $Header: DOMREVB.pls 120.2 2006/03/24 17:32:26 dedatta noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_DOCUMENT_ID in NUMBER,
6 X_REVISION_ID in NUMBER,
7 X_CATEGORY_ID in NUMBER,
8 X_REVISION in VARCHAR2,
9 X_CHECKOUT_STATUS in VARCHAR2,
10 X_CHECKED_OUT_BY in NUMBER,
11 X_CREATION_REASON in VARCHAR2,
12 X_LIFECYCLE_PHASE_ID in NUMBER,
13 X_LIFECYCLE_TRACKING_ID in NUMBER,
14 X_COMMENTS in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from DOM_DOCUMENT_REVISIONS
22 where DOCUMENT_ID = X_DOCUMENT_ID
23 and REVISION_ID = X_REVISION_ID
24 ;
25 begin
26 insert into DOM_DOCUMENT_REVISIONS (
27 CATEGORY_ID,
28 DOCUMENT_ID,
29 REVISION_ID,
30 REVISION,
31 CHECKOUT_STATUS,
32 CHECKED_OUT_BY,
33 CREATION_REASON,
34 LIFECYCLE_PHASE_ID,
35 LIFECYCLE_TRACKING_ID,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN
41 ) values (
42 X_CATEGORY_ID,
43 X_DOCUMENT_ID,
44 X_REVISION_ID,
45 X_REVISION,
46 X_CHECKOUT_STATUS,
47 X_CHECKED_OUT_BY,
48 X_CREATION_REASON,
49 X_LIFECYCLE_PHASE_ID,
50 X_LIFECYCLE_TRACKING_ID,
51 X_CREATION_DATE,
52 X_CREATED_BY,
53 X_LAST_UPDATE_DATE,
54 X_LAST_UPDATED_BY,
55 X_LAST_UPDATE_LOGIN
56 );
57
58 insert into DOM_DOCUMENT_REVISIONS_TL (
59 DOCUMENT_ID,
60 CATEGORY_ID,
61 REVISION_ID,
62 COMMENTS,
63 CREATED_BY,
64 CREATION_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_DATE,
67 LAST_UPDATE_LOGIN,
68 LANGUAGE,
69 SOURCE_LANG
70 ) select
71 X_DOCUMENT_ID,
72 X_CATEGORY_ID,
73 X_REVISION_ID,
74 X_COMMENTS,
75 X_CREATED_BY,
76 X_CREATION_DATE,
77 X_LAST_UPDATED_BY,
78 X_LAST_UPDATE_DATE,
79 X_LAST_UPDATE_LOGIN,
80 L.LANGUAGE_CODE,
81 userenv('LANG')
82 from FND_LANGUAGES L
83 where L.INSTALLED_FLAG in ('I', 'B')
84 and not exists
85 (select NULL
86 from DOM_DOCUMENT_REVISIONS_TL T
87 where T.DOCUMENT_ID = X_DOCUMENT_ID
88 and T.REVISION_ID = X_REVISION_ID
89 and T.LANGUAGE = L.LANGUAGE_CODE);
90
91 open c;
92 fetch c into X_ROWID;
93 if (c%notfound) then
94 close c;
95 raise no_data_found;
96 end if;
97 close c;
98
99 end INSERT_ROW;
100
101 procedure LOCK_ROW (
102 X_DOCUMENT_ID in NUMBER,
103 X_REVISION_ID in NUMBER,
104 X_CATEGORY_ID in NUMBER,
105 X_REVISION in VARCHAR2,
106 X_CHECKOUT_STATUS in VARCHAR2,
107 X_CHECKED_OUT_BY in NUMBER,
108 X_CREATION_REASON in VARCHAR2,
109 X_LIFECYCLE_PHASE_ID in NUMBER,
110 X_LIFECYCLE_TRACKING_ID in NUMBER,
111 X_COMMENTS in VARCHAR2
112 ) is
113 cursor c is select
114 CATEGORY_ID,
115 REVISION,
116 CHECKOUT_STATUS,
117 CHECKED_OUT_BY,
118 CREATION_REASON,
119 LIFECYCLE_PHASE_ID,
120 LIFECYCLE_TRACKING_ID
121 from DOM_DOCUMENT_REVISIONS
122 where DOCUMENT_ID = X_DOCUMENT_ID
123 and REVISION_ID = X_REVISION_ID
124 for update of DOCUMENT_ID nowait;
125 recinfo c%rowtype;
126
127 cursor c1 is select
128 COMMENTS,
129 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
130 from DOM_DOCUMENT_REVISIONS_TL
131 where DOCUMENT_ID = X_DOCUMENT_ID
132 and REVISION_ID = X_REVISION_ID
133 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
134 for update of DOCUMENT_ID nowait;
135 begin
136 open c;
137 fetch c into recinfo;
138 if (c%notfound) then
139 close c;
140 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
141 app_exception.raise_exception;
142 end if;
143 close c;
144 if ( ((recinfo.CATEGORY_ID = X_CATEGORY_ID)
145 OR ((recinfo.CATEGORY_ID is null) AND (X_CATEGORY_ID is null)))
146 AND ((recinfo.REVISION = X_REVISION)
147 OR ((recinfo.REVISION is null) AND (X_REVISION is null)))
148 AND ((recinfo.CHECKOUT_STATUS = X_CHECKOUT_STATUS)
149 OR ((recinfo.CHECKOUT_STATUS is null) AND (X_CHECKOUT_STATUS is null)))
150 AND ((recinfo.CHECKED_OUT_BY = X_CHECKED_OUT_BY)
151 OR ((recinfo.CHECKED_OUT_BY is null) AND (X_CHECKED_OUT_BY is null)))
152 AND ((recinfo.CREATION_REASON = X_CREATION_REASON)
153 OR ((recinfo.CREATION_REASON is null) AND (X_CREATION_REASON is null)))
154 AND ((recinfo.LIFECYCLE_PHASE_ID = X_LIFECYCLE_PHASE_ID)
155 OR ((recinfo.LIFECYCLE_PHASE_ID is null) AND (X_LIFECYCLE_PHASE_ID is null)))
156 AND ((recinfo.LIFECYCLE_TRACKING_ID = X_LIFECYCLE_TRACKING_ID)
157 OR ((recinfo.LIFECYCLE_TRACKING_ID is null) AND (X_LIFECYCLE_TRACKING_ID is null)))
158 ) then
159 null;
160 else
161 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
162 app_exception.raise_exception;
163 end if;
164
165 for tlinfo in c1 loop
166 if (tlinfo.BASELANG = 'Y') then
167 if ( ((tlinfo.COMMENTS = X_COMMENTS)
168 OR ((tlinfo.COMMENTS is null) AND (X_COMMENTS is null)))
169 ) then
170 null;
171 else
172 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
173 app_exception.raise_exception;
174 end if;
175 end if;
176 end loop;
177 return;
178 end LOCK_ROW;
179
180 procedure UPDATE_ROW (
181 X_DOCUMENT_ID in NUMBER,
182 X_REVISION_ID in NUMBER,
183 X_CATEGORY_ID in NUMBER,
184 X_REVISION in VARCHAR2,
185 X_CHECKOUT_STATUS in VARCHAR2,
186 X_CHECKED_OUT_BY in NUMBER,
187 X_CREATION_REASON in VARCHAR2,
188 X_LIFECYCLE_PHASE_ID in NUMBER,
189 X_LIFECYCLE_TRACKING_ID in NUMBER,
190 X_COMMENTS in VARCHAR2,
191 X_LAST_UPDATE_DATE in DATE,
192 X_LAST_UPDATED_BY in NUMBER,
193 X_LAST_UPDATE_LOGIN in NUMBER
194 ) is
195 begin
196 update DOM_DOCUMENT_REVISIONS set
197 CATEGORY_ID = X_CATEGORY_ID,
198 REVISION = X_REVISION,
199 CHECKOUT_STATUS = X_CHECKOUT_STATUS,
200 CHECKED_OUT_BY = X_CHECKED_OUT_BY,
201 CREATION_REASON = X_CREATION_REASON,
202 LIFECYCLE_PHASE_ID = X_LIFECYCLE_PHASE_ID,
203 LIFECYCLE_TRACKING_ID = X_LIFECYCLE_TRACKING_ID,
204 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
205 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
206 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
207 where DOCUMENT_ID = X_DOCUMENT_ID
208 and REVISION_ID = X_REVISION_ID;
209
210 if (sql%notfound) then
211 raise no_data_found;
212 end if;
213
214 update DOM_DOCUMENT_REVISIONS_TL set
215 COMMENTS = X_COMMENTS,
216 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
217 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
218 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
219 SOURCE_LANG = userenv('LANG')
220 where DOCUMENT_ID = X_DOCUMENT_ID
221 and REVISION_ID = X_REVISION_ID
222 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
223
224 if (sql%notfound) then
225 raise no_data_found;
226 end if;
227 end UPDATE_ROW;
228
229 procedure DELETE_ROW (
230 X_DOCUMENT_ID in NUMBER,
231 X_REVISION_ID in NUMBER
232 ) is
233 begin
234 delete from DOM_DOCUMENT_REVISIONS_TL
235 where DOCUMENT_ID = X_DOCUMENT_ID
236 and REVISION_ID = X_REVISION_ID;
237
238 if (sql%notfound) then
239 raise no_data_found;
240 end if;
241
242 delete from DOM_DOCUMENT_REVISIONS
243 where DOCUMENT_ID = X_DOCUMENT_ID
244 and REVISION_ID = X_REVISION_ID;
245
246 if (sql%notfound) then
247 raise no_data_found;
248 end if;
249 end DELETE_ROW;
250
251 procedure ADD_LANGUAGE
252 is
253 begin
254 delete from DOM_DOCUMENT_REVISIONS_TL T
255 where not exists
256 (select NULL
257 from DOM_DOCUMENT_REVISIONS B
258 where B.DOCUMENT_ID = T.DOCUMENT_ID
259 and B.REVISION_ID = T.REVISION_ID
260 );
261
262 update DOM_DOCUMENT_REVISIONS_TL T set (
263 COMMENTS
264 ) = (select
265 B.COMMENTS
266 from DOM_DOCUMENT_REVISIONS_TL B
267 where B.DOCUMENT_ID = T.DOCUMENT_ID
268 and B.REVISION_ID = T.REVISION_ID
269 and B.LANGUAGE = T.SOURCE_LANG)
270 where (
271 T.DOCUMENT_ID,
272 T.REVISION_ID,
273 T.LANGUAGE
274 ) in (select
275 SUBT.DOCUMENT_ID,
276 SUBT.REVISION_ID,
277 SUBT.LANGUAGE
278 from DOM_DOCUMENT_REVISIONS_TL SUBB, DOM_DOCUMENT_REVISIONS_TL SUBT
279 where SUBB.DOCUMENT_ID = SUBT.DOCUMENT_ID
280 and SUBB.REVISION_ID = SUBT.REVISION_ID
281 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
282 and (SUBB.COMMENTS <> SUBT.COMMENTS
286
283 or (SUBB.COMMENTS is null and SUBT.COMMENTS is not null)
284 or (SUBB.COMMENTS is not null and SUBT.COMMENTS is null)
285 ));
287 insert into DOM_DOCUMENT_REVISIONS_TL (
288 DOCUMENT_ID,
289 CATEGORY_ID,
290 REVISION_ID,
291 COMMENTS,
292 CREATED_BY,
293 CREATION_DATE,
294 LAST_UPDATED_BY,
295 LAST_UPDATE_DATE,
296 LAST_UPDATE_LOGIN,
297 LANGUAGE,
298 SOURCE_LANG
299 ) select /*+ ORDERED */
300 B.DOCUMENT_ID,
301 B.CATEGORY_ID,
302 B.REVISION_ID,
303 B.COMMENTS,
304 B.CREATED_BY,
305 B.CREATION_DATE,
306 B.LAST_UPDATED_BY,
307 B.LAST_UPDATE_DATE,
308 B.LAST_UPDATE_LOGIN,
309 L.LANGUAGE_CODE,
310 B.SOURCE_LANG
311 from DOM_DOCUMENT_REVISIONS_TL B, FND_LANGUAGES L
312 where L.INSTALLED_FLAG in ('I', 'B')
313 and B.LANGUAGE = userenv('LANG')
314 and not exists
315 (select NULL
316 from DOM_DOCUMENT_REVISIONS_TL T
317 where T.DOCUMENT_ID = B.DOCUMENT_ID
318 and T.REVISION_ID = B.REVISION_ID
319 and T.LANGUAGE = L.LANGUAGE_CODE);
320 end ADD_LANGUAGE;
321
322 end DOM_DOCUMENT_REVISIONS_PKG;
[Home] [Help]