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