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