1 package body ENG_CHANGE_LINES_PKG as
2 /* $Header: ENGCHLUB.pls 115.8 2003/11/22 18:59:27 sshrikha ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_CHANGE_LINE_ID in NUMBER,
6 X_REQUEST_ID in NUMBER,
7 X_CHANGE_ID in NUMBER,
8 X_SEQUENCE_NUMBER in NUMBER,
9 X_CHANGE_TYPE_ID in NUMBER,
10 X_STATUS_CODE in VARCHAR2,
11 X_ASSIGNEE_ID in NUMBER,
12 X_NEED_BY_DATE in DATE,
13 X_ORIGINAL_SYSTEM_REFERENCE in VARCHAR2,
14 X_NAME in VARCHAR2,
15 X_DESCRIPTION in VARCHAR2,
16 X_SCHEDULED_DATE in DATE,
17 X_IMPLEMENTATION_DATE in DATE,
18 X_CANCELATION_DATE in DATE,
19 X_CREATION_DATE in DATE,
20 X_CREATED_BY in NUMBER,
21 X_LAST_UPDATE_DATE in DATE,
22 X_LAST_UPDATED_BY in NUMBER,
23 X_LAST_UPDATE_LOGIN in NUMBER,
24 X_PROGRAM_ID IN NUMBER,
25 X_PROGRAM_APPLICATION_ID IN NUMBER,
26 X_PROGRAM_UPDATE_DATE IN DATE,
27 X_APPROVAL_STATUS_TYPE IN NUMBER,
28 X_APPROVAL_DATE IN DATE,
29 X_APPROVAL_REQUEST_DATE IN DATE,
30 X_ROUTE_ID IN NUMBER,
31 X_REQUIRED_FLAG IN VARCHAR2,
32 X_COMPLETE_BEFORE_STATUS_CODE IN NUMBER,
33 X_START_AFTER_STATUS_CODE IN NUMBER
34
35 ) is
36 cursor C is select ROWID from ENG_CHANGE_LINES
37 where CHANGE_LINE_ID = X_CHANGE_LINE_ID
38 ;
39 begin
40 insert into ENG_CHANGE_LINES (
41 CHANGE_LINE_ID,
42 REQUEST_ID,
43 CHANGE_ID,
44 SEQUENCE_NUMBER,
45 CHANGE_TYPE_ID,
46 SCHEDULED_DATE,
47 IMPLEMENTATION_DATE,
48 CANCELATION_DATE,
49 STATUS_CODE,
50 ASSIGNEE_ID,
51 NEED_BY_DATE,
52 ORIGINAL_SYSTEM_REFERENCE,
53 CREATION_DATE,
54 CREATED_BY,
55 LAST_UPDATE_DATE,
56 LAST_UPDATED_BY,
57 LAST_UPDATE_LOGIN,
58 APPROVAL_STATUS_TYPE,
59 APPROVAL_DATE,
60 APPROVAL_REQUEST_DATE,
61 ROUTE_ID,
62 REQUIRED_FLAG,
63 COMPLETE_BEFORE_STATUS_CODE,
64 START_AFTER_STATUS_CODE
65
66 ) values (
67 X_CHANGE_LINE_ID,
68 X_REQUEST_ID,
69 X_CHANGE_ID,
70 X_SEQUENCE_NUMBER,
71 X_CHANGE_TYPE_ID,
72 X_SCHEDULED_DATE,
73 X_IMPLEMENTATION_DATE,
74 X_CANCELATION_DATE,
75 X_STATUS_CODE,
76 X_ASSIGNEE_ID,
77 X_NEED_BY_DATE,
78 X_ORIGINAL_SYSTEM_REFERENCE,
79 X_CREATION_DATE,
80 X_CREATED_BY,
81 X_LAST_UPDATE_DATE,
82 X_LAST_UPDATED_BY,
83 X_LAST_UPDATE_LOGIN,
84 NVL(X_APPROVAL_STATUS_TYPE,1),
85 X_APPROVAL_DATE,
86 X_APPROVAL_REQUEST_DATE,
87 X_ROUTE_ID,
88 X_REQUIRED_FLAG,
89 X_COMPLETE_BEFORE_STATUS_CODE,
90 X_START_AFTER_STATUS_CODE
91 );
92
93 insert into ENG_CHANGE_LINES_TL (
94 CHANGE_LINE_ID,
95 CREATION_DATE,
96 CREATED_BY,
97 LAST_UPDATE_DATE,
98 LAST_UPDATED_BY,
99 LAST_UPDATE_LOGIN,
100 NAME,
101 DESCRIPTION,
102 LANGUAGE,
103 SOURCE_LANG
104 ) select
105 X_CHANGE_LINE_ID,
106 X_CREATION_DATE,
107 X_CREATED_BY,
108 X_LAST_UPDATE_DATE,
109 X_LAST_UPDATED_BY,
110 X_LAST_UPDATE_LOGIN,
111 X_NAME,
112 X_DESCRIPTION,
113 L.LANGUAGE_CODE,
114 userenv('LANG')
115 from FND_LANGUAGES L
116 where L.INSTALLED_FLAG in ('I', 'B')
117 and not exists
118 (select NULL
119 from ENG_CHANGE_LINES_TL T
120 where T.CHANGE_LINE_ID = X_CHANGE_LINE_ID
121 and T.LANGUAGE = L.LANGUAGE_CODE);
122
123 open c;
124 fetch c into X_ROWID;
125 if (c%notfound) then
126 close c;
127 raise no_data_found;
128 end if;
129 close c;
130
131 end INSERT_ROW;
132
133 procedure LOCK_ROW (
134 X_CHANGE_LINE_ID in NUMBER,
135 X_REQUEST_ID in NUMBER,
136 X_CHANGE_ID in NUMBER,
137 X_SEQUENCE_NUMBER in NUMBER,
138 X_CHANGE_TYPE_ID in NUMBER,
139 X_STATUS_CODE in VARCHAR2,
140 X_ASSIGNEE_ID in NUMBER,
141 X_NEED_BY_DATE in DATE,
142 X_ORIGINAL_SYSTEM_REFERENCE in VARCHAR2,
143 X_NAME in VARCHAR2,
144 X_DESCRIPTION in VARCHAR2,
145 X_SCHEDULED_DATE in DATE,
146 X_IMPLEMENTATION_DATE in DATE,
147 X_CANCELATION_DATE in DATE,
148 X_PROGRAM_ID IN NUMBER,
149 X_PROGRAM_APPLICATION_ID IN NUMBER,
150 X_PROGRAM_UPDATE_DATE IN DATE,
151 X_APPROVAL_STATUS_TYPE IN NUMBER,
152 X_APPROVAL_DATE IN DATE,
153 X_APPROVAL_REQUEST_DATE IN DATE,
154 X_ROUTE_ID IN NUMBER,
155 X_REQUIRED_FLAG IN VARCHAR2,
156 X_COMPLETE_BEFORE_STATUS_CODE IN NUMBER,
157 X_START_AFTER_STATUS_CODE IN NUMBER
158 ) is
159 cursor c is select
160 REQUEST_ID,
161 CHANGE_ID,
162 SEQUENCE_NUMBER,
163 CHANGE_TYPE_ID,
164 SCHEDULED_DATE,
165 IMPLEMENTATION_DATE,
166 CANCELATION_DATE,
167 STATUS_CODE,
168 ASSIGNEE_ID,
169 NEED_BY_DATE,
170 ORIGINAL_SYSTEM_REFERENCE,
171 APPROVAL_STATUS_TYPE,
172 APPROVAL_DATE,
173 APPROVAL_REQUEST_DATE,
174 ROUTE_ID,
175 REQUIRED_FLAG,
176 COMPLETE_BEFORE_STATUS_CODE,
177 START_AFTER_STATUS_CODE
178 from ENG_CHANGE_LINES
179 where CHANGE_LINE_ID = X_CHANGE_LINE_ID
180 for update of CHANGE_LINE_ID nowait;
181 recinfo c%rowtype;
182
183 cursor c1 is select
184 NAME,
185 DESCRIPTION,
186 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
187 from ENG_CHANGE_LINES_TL
188 where CHANGE_LINE_ID = X_CHANGE_LINE_ID
189 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
190 for update of CHANGE_LINE_ID nowait;
191 begin
192 open c;
193 fetch c into recinfo;
194 if (c%notfound) then
195 close c;
196 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
197 app_exception.raise_exception;
198 end if;
199 close c;
200 if ( ((recinfo.REQUEST_ID = X_REQUEST_ID)
201 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
202 AND (recinfo.CHANGE_ID= X_CHANGE_ID)
203 AND (recinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
204 AND (recinfo.CHANGE_TYPE_ID = X_CHANGE_TYPE_ID)
205 AND ((recinfo.SCHEDULED_DATE = X_SCHEDULED_DATE)
206 OR ((recinfo.SCHEDULED_DATE is null) AND (X_SCHEDULED_DATE is null)))
207 AND ((recinfo.IMPLEMENTATION_DATE = X_IMPLEMENTATION_DATE)
208 OR ((recinfo.IMPLEMENTATION_DATE is null) AND (X_IMPLEMENTATION_DATE is null)))
209 AND ((recinfo.CANCELATION_DATE = X_CANCELATION_DATE)
210 OR ((recinfo.CANCELATION_DATE is null) AND (X_CANCELATION_DATE is null)))
211 AND ((recinfo.STATUS_CODE = X_STATUS_CODE)
212 OR ((recinfo.STATUS_CODE is null) AND (X_STATUS_CODE is null)))
213 AND ((recinfo.ASSIGNEE_ID = X_ASSIGNEE_ID)
214 OR ((recinfo.ASSIGNEE_ID is null) AND (X_ASSIGNEE_ID is null)))
215 AND ((recinfo.NEED_BY_DATE= X_NEED_BY_DATE)
216 OR ((recinfo.NEED_BY_DATE is null) AND (X_NEED_BY_DATE is null)))
217 AND ((recinfo.ORIGINAL_SYSTEM_REFERENCE = X_ORIGINAL_SYSTEM_REFERENCE)
218 OR ((recinfo.ORIGINAL_SYSTEM_REFERENCE is null) AND (X_ORIGINAL_SYSTEM_REFERENCE is null)))
219 AND ((recinfo.APPROVAL_STATUS_TYPE = X_APPROVAL_STATUS_TYPE)
220 OR ((recinfo.APPROVAL_STATUS_TYPE IS NULL) AND (X_APPROVAL_STATUS_TYPE IS NULL)))
221 AND ((recinfo.APPROVAL_DATE = X_APPROVAL_DATE)
222 OR ((recinfo.APPROVAL_DATE IS NULL) AND (X_APPROVAL_DATE IS NULL)))
223 AND ((recinfo.APPROVAL_REQUEST_DATE = X_APPROVAL_REQUEST_DATE)
224 OR ((recinfo.APPROVAL_REQUEST_DATE IS NULL) AND (X_APPROVAL_REQUEST_DATE IS NULL)))
225 AND ((recinfo.ROUTE_ID = X_ROUTE_ID)
226 OR ((recinfo.ROUTE_ID IS NULL) AND (X_ROUTE_ID IS NULL)))
227 AND ((recinfo.REQUIRED_FLAG = X_REQUIRED_FLAG)
228 OR ((recinfo.REQUIRED_FLAG IS NULL) AND (X_REQUIRED_FLAG IS NULL)))
229 AND ((recinfo.COMPLETE_BEFORE_STATUS_CODE = X_COMPLETE_BEFORE_STATUS_CODE)
230 OR ((recinfo.COMPLETE_BEFORE_STATUS_CODE IS NULL) AND (X_COMPLETE_BEFORE_STATUS_CODE IS NULL)))
231 AND ((recinfo.START_AFTER_STATUS_CODE = X_START_AFTER_STATUS_CODE)
232 OR ((recinfo.START_AFTER_STATUS_CODE IS NULL) AND (X_START_AFTER_STATUS_CODE IS NULL)))
233 ) then
234 null;
235 else
236 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
237 app_exception.raise_exception;
238 end if;
239
240 for tlinfo in c1 loop
241 if (tlinfo.BASELANG = 'Y') then
242 if ( (tlinfo.NAME = X_NAME)
243 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
244 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
245 ) then
246 null;
247 else
248 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
249 app_exception.raise_exception;
250 end if;
251 end if;
252 end loop;
253 return;
254 end LOCK_ROW;
255
256 procedure UPDATE_ROW (
257 X_CHANGE_LINE_ID in NUMBER,
258 X_REQUEST_ID in NUMBER,
259 X_CHANGE_ID in NUMBER,
260 X_SEQUENCE_NUMBER in NUMBER,
261 X_CHANGE_TYPE_ID in NUMBER,
262 X_STATUS_CODE in VARCHAR2,
263 X_ASSIGNEE_ID in NUMBER,
264 X_NEED_BY_DATE in DATE,
265 X_ORIGINAL_SYSTEM_REFERENCE in VARCHAR2,
266 X_NAME in VARCHAR2,
267 X_DESCRIPTION in VARCHAR2,
268 X_SCHEDULED_DATE in DATE,
269 X_IMPLEMENTATION_DATE in DATE,
270 X_CANCELATION_DATE in DATE,
271 X_LAST_UPDATE_DATE in DATE,
272 X_LAST_UPDATED_BY in NUMBER,
273 X_LAST_UPDATE_LOGIN in NUMBER,
274 X_PROGRAM_ID IN NUMBER,
275 X_PROGRAM_APPLICATION_ID IN NUMBER,
276 X_PROGRAM_UPDATE_DATE IN DATE,
277 X_APPROVAL_STATUS_TYPE IN NUMBER,
278 X_APPROVAL_DATE IN DATE,
279 X_APPROVAL_REQUEST_DATE IN DATE,
280 X_ROUTE_ID IN NUMBER,
281 X_REQUIRED_FLAG IN VARCHAR2,
282 X_COMPLETE_BEFORE_STATUS_CODE IN NUMBER,
283 X_START_AFTER_STATUS_CODE IN NUMBER
284
285 ) is
286 begin
287 update ENG_CHANGE_LINES set
288 REQUEST_ID = X_REQUEST_ID,
289 CHANGE_ID = X_CHANGE_ID,
290 SEQUENCE_NUMBER = X_SEQUENCE_NUMBER,
291 CHANGE_TYPE_ID = X_CHANGE_TYPE_ID,
292 SCHEDULED_DATE = X_SCHEDULED_DATE,
293 IMPLEMENTATION_DATE = X_IMPLEMENTATION_DATE,
294 CANCELATION_DATE = X_CANCELATION_DATE,
295 STATUS_CODE = X_STATUS_CODE,
296 ASSIGNEE_ID = X_ASSIGNEE_ID,
297 NEED_BY_DATE = X_NEED_BY_DATE,
298 ORIGINAL_SYSTEM_REFERENCE = X_ORIGINAL_SYSTEM_REFERENCE,
299 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
300 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
301 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
302 APPROVAL_STATUS_TYPE = X_APPROVAL_STATUS_TYPE,
303 APPROVAL_DATE = X_APPROVAL_DATE,
304 APPROVAL_REQUEST_DATE = X_APPROVAL_REQUEST_DATE,
305 ROUTE_ID = X_ROUTE_ID,
306 REQUIRED_FLAG = X_REQUIRED_FLAG,
307 COMPLETE_BEFORE_STATUS_CODE = X_COMPLETE_BEFORE_STATUS_CODE,
308 START_AFTER_STATUS_CODE = X_START_AFTER_STATUS_CODE
309 where CHANGE_LINE_ID = X_CHANGE_LINE_ID;
310
311 if (sql%notfound) then
312 raise no_data_found;
313 end if;
314
315 update ENG_CHANGE_LINES_TL set
316 NAME = X_NAME,
317 DESCRIPTION = X_DESCRIPTION,
318 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
319 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
320 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
321 SOURCE_LANG = userenv('LANG')
322 where CHANGE_LINE_ID = X_CHANGE_LINE_ID
323 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
324
325 if (sql%notfound) then
326 raise no_data_found;
327 end if;
328 end UPDATE_ROW;
329
330 procedure DELETE_ROW (
331 X_CHANGE_LINE_ID in NUMBER
332 ) is
333 begin
334 delete from ENG_CHANGE_LINES_TL
335 where CHANGE_LINE_ID = X_CHANGE_LINE_ID;
336
337 if (sql%notfound) then
338 raise no_data_found;
339 end if;
340
341 delete from ENG_CHANGE_LINES
342 where CHANGE_LINE_ID = X_CHANGE_LINE_ID;
343
344 if (sql%notfound) then
345 raise no_data_found;
346 end if;
347 end DELETE_ROW;
348
349 procedure ADD_LANGUAGE
350 is
351 begin
352 delete from ENG_CHANGE_LINES_TL T
353 where not exists
354 (select NULL
355 from ENG_CHANGE_LINES B
356 where B.CHANGE_LINE_ID = T.CHANGE_LINE_ID
357 );
358
359 update ENG_CHANGE_LINES_TL T set (
360 NAME,
361 DESCRIPTION
362 ) = (select
363 B.NAME,
364 B.DESCRIPTION
365 from ENG_CHANGE_LINES_TL B
366 where B.CHANGE_LINE_ID = T.CHANGE_LINE_ID
367 and B.LANGUAGE = T.SOURCE_LANG)
368 where (
369 T.CHANGE_LINE_ID,
370 T.LANGUAGE
371 ) in (select
372 SUBT.CHANGE_LINE_ID,
373 SUBT.LANGUAGE
374 from ENG_CHANGE_LINES_TL SUBB, ENG_CHANGE_LINES_TL SUBT
375 where SUBB.CHANGE_LINE_ID = SUBT.CHANGE_LINE_ID
376 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
377 and (SUBB.NAME <> SUBT.NAME
378 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
379 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
380 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
381 ));
382
383 insert into ENG_CHANGE_LINES_TL (
384 CHANGE_LINE_ID,
385 CREATION_DATE,
386 CREATED_BY,
387 LAST_UPDATE_DATE,
388 LAST_UPDATED_BY,
389 LAST_UPDATE_LOGIN,
390 NAME,
391 DESCRIPTION,
392 LANGUAGE,
393 SOURCE_LANG
394 ) select
395 B.CHANGE_LINE_ID,
396 B.CREATION_DATE,
397 B.CREATED_BY,
398 B.LAST_UPDATE_DATE,
399 B.LAST_UPDATED_BY,
400 B.LAST_UPDATE_LOGIN,
401 B.NAME,
402 B.DESCRIPTION,
403 L.LANGUAGE_CODE,
404 B.SOURCE_LANG
405 from ENG_CHANGE_LINES_TL B, FND_LANGUAGES L
406 where L.INSTALLED_FLAG in ('I', 'B')
407 and B.LANGUAGE = userenv('LANG')
408 and not exists
409 (select NULL
410 from ENG_CHANGE_LINES_TL T
411 where T.CHANGE_LINE_ID = B.CHANGE_LINE_ID
412 and T.LANGUAGE = L.LANGUAGE_CODE);
413 end ADD_LANGUAGE;
414
415 end ENG_CHANGE_LINES_PKG;
[Home] [Help]