1 package body AMW_AP_STEPS_PKG as
2 /* $Header: amwtstpb.pls 120.0 2005/05/31 20:56:14 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_AP_STEP_ID in NUMBER,
6 X_ATTRIBUTE4 in VARCHAR2,
7 X_ATTRIBUTE5 in VARCHAR2,
8 X_ATTRIBUTE1 in VARCHAR2,
9 X_ATTRIBUTE6 in VARCHAR2,
10 X_ATTRIBUTE7 in VARCHAR2,
11 X_ATTRIBUTE8 in VARCHAR2,
12 X_ATTRIBUTE9 in VARCHAR2,
13 X_SAMPLESIZE in NUMBER,
14 X_AUDIT_PROCEDURE_ID in NUMBER,
15 X_SEQNUM in NUMBER,
16 X_ATTRIBUTE2 in VARCHAR2,
17 X_ATTRIBUTE3 in VARCHAR2,
18 X_ATTRIBUTE10 in VARCHAR2,
19 X_ATTRIBUTE11 in VARCHAR2,
20 X_ATTRIBUTE12 in VARCHAR2,
21 X_ATTRIBUTE13 in VARCHAR2,
22 X_ATTRIBUTE14 in VARCHAR2,
23 X_ATTRIBUTE15 in VARCHAR2,
24 X_SECURITY_GROUP_ID in NUMBER,
25 X_OBJECT_VERSION_NUMBER in NUMBER,
26 X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
27 X_REQUESTOR_ID in NUMBER,
28 X_ATTRIBUTE_CATEGORY in VARCHAR2,
29 X_NAME in VARCHAR2,
30 X_DESCRIPTION in VARCHAR2,
31 X_CREATION_DATE in DATE,
32 X_CREATED_BY in NUMBER,
33 X_LAST_UPDATE_DATE in DATE,
34 X_LAST_UPDATED_BY in NUMBER,
35 X_LAST_UPDATE_LOGIN in NUMBER,
36 X_FROM_REV_NUM in NUMBER,
37 X_TO_REV_NUM in NUMBER,
38 X_CSEQNUM in VARCHAR2
39 ) is
40 cursor C is select ROWID from AMW_AP_STEPS_B
41 where AP_STEP_ID = X_AP_STEP_ID
42 ;
43 begin
44 insert into AMW_AP_STEPS_B (
45 AP_STEP_ID,
46 AUDIT_PROCEDURE_ID,
47 SEQNUM,
48 SAMPLESIZE,
49 ATTRIBUTE_CATEGORY,
50 ATTRIBUTE1,
51 ATTRIBUTE2,
52 ATTRIBUTE3,
53 ATTRIBUTE4,
54 ATTRIBUTE5,
55 ATTRIBUTE6,
56 ATTRIBUTE7,
57 ATTRIBUTE8,
58 ATTRIBUTE9,
59 ATTRIBUTE10,
60 ATTRIBUTE11,
61 ATTRIBUTE12,
62 ATTRIBUTE13,
63 ATTRIBUTE14,
64 ATTRIBUTE15,
65 SECURITY_GROUP_ID,
66 OBJECT_VERSION_NUMBER,
67 ORIG_SYSTEM_REFERENCE,
68 REQUESTOR_ID,
69 CREATION_DATE,
70 CREATED_BY,
71 LAST_UPDATE_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_LOGIN,
74 FROM_REV_NUM,
75 TO_REV_NUM,
76 CSEQNUM
77 ) values (
78 X_AP_STEP_ID,
79 X_AUDIT_PROCEDURE_ID,
80 X_SEQNUM,
81 X_SAMPLESIZE,
82 X_ATTRIBUTE_CATEGORY,
83 X_ATTRIBUTE1,
84 X_ATTRIBUTE2,
85 X_ATTRIBUTE3,
86 X_ATTRIBUTE4,
87 X_ATTRIBUTE5,
88 X_ATTRIBUTE6,
89 X_ATTRIBUTE7,
90 X_ATTRIBUTE8,
91 X_ATTRIBUTE9,
92 X_ATTRIBUTE10,
93 X_ATTRIBUTE11,
94 X_ATTRIBUTE12,
95 X_ATTRIBUTE13,
96 X_ATTRIBUTE14,
97 X_ATTRIBUTE15,
98 X_SECURITY_GROUP_ID,
99 X_OBJECT_VERSION_NUMBER,
100 X_ORIG_SYSTEM_REFERENCE,
101 X_REQUESTOR_ID,
102 X_CREATION_DATE,
103 X_CREATED_BY,
104 X_LAST_UPDATE_DATE,
105 X_LAST_UPDATED_BY,
106 X_LAST_UPDATE_LOGIN,
107 X_FROM_REV_NUM,
108 X_TO_REV_NUM,
109 X_CSEQNUM
110 );
111
112 insert into AMW_AP_STEPS_TL (
113 DESCRIPTION,
114 LAST_UPDATE_LOGIN,
115 SECURITY_GROUP_ID,
116 NAME,
117 AP_STEP_ID,
118 CREATED_BY,
119 CREATION_DATE,
120 LAST_UPDATED_BY,
121 LAST_UPDATE_DATE,
122 LANGUAGE,
123 SOURCE_LANG,
124 SOURCE_LANGUAGE
125 ) select
126 X_DESCRIPTION,
127 X_LAST_UPDATE_LOGIN,
128 X_SECURITY_GROUP_ID,
129 X_NAME,
130 X_AP_STEP_ID,
131 X_CREATED_BY,
132 X_CREATION_DATE,
133 X_LAST_UPDATED_BY,
134 X_LAST_UPDATE_DATE,
135 L.LANGUAGE_CODE,
136 userenv('LANG'),
137 userenv('LANG')
138 from FND_LANGUAGES L
139 where L.INSTALLED_FLAG in ('I', 'B')
140 and not exists
141 (select NULL
142 from AMW_AP_STEPS_TL T
143 where T.AP_STEP_ID = X_AP_STEP_ID
144 and T.LANGUAGE = L.LANGUAGE_CODE);
145
146 open c;
147 fetch c into X_ROWID;
148 if (c%notfound) then
149 close c;
150 raise no_data_found;
151 end if;
152 close c;
153
154 end INSERT_ROW;
155
156 procedure LOCK_ROW (
157 X_AP_STEP_ID in NUMBER,
158 X_ATTRIBUTE4 in VARCHAR2,
159 X_ATTRIBUTE5 in VARCHAR2,
160 X_ATTRIBUTE1 in VARCHAR2,
161 X_ATTRIBUTE6 in VARCHAR2,
162 X_ATTRIBUTE7 in VARCHAR2,
163 X_ATTRIBUTE8 in VARCHAR2,
164 X_ATTRIBUTE9 in VARCHAR2,
165 X_SAMPLESIZE in NUMBER,
166 X_AUDIT_PROCEDURE_ID in NUMBER,
167 X_SEQNUM in NUMBER,
168 X_ATTRIBUTE2 in VARCHAR2,
169 X_ATTRIBUTE3 in VARCHAR2,
170 X_ATTRIBUTE10 in VARCHAR2,
171 X_ATTRIBUTE11 in VARCHAR2,
172 X_ATTRIBUTE12 in VARCHAR2,
173 X_ATTRIBUTE13 in VARCHAR2,
174 X_ATTRIBUTE14 in VARCHAR2,
175 X_ATTRIBUTE15 in VARCHAR2,
176 X_SECURITY_GROUP_ID in NUMBER,
177 X_OBJECT_VERSION_NUMBER in NUMBER,
178 X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
179 X_REQUESTOR_ID in NUMBER,
180 X_ATTRIBUTE_CATEGORY in VARCHAR2,
181 X_NAME in VARCHAR2,
182 X_DESCRIPTION in VARCHAR2,
183 X_FROM_REV_NUM in NUMBER,
184 X_TO_REV_NUM in NUMBER,
185 X_CSEQNUM in VARCHAR2
186 ) is
187 cursor c is select
188 AUDIT_PROCEDURE_ID,
189 SEQNUM,
190 SAMPLESIZE,
191 ATTRIBUTE_CATEGORY,
192 ATTRIBUTE1,
193 ATTRIBUTE2,
194 ATTRIBUTE3,
195 ATTRIBUTE4,
196 ATTRIBUTE5,
197 ATTRIBUTE6,
198 ATTRIBUTE7,
199 ATTRIBUTE8,
200 ATTRIBUTE9,
201 ATTRIBUTE10,
202 ATTRIBUTE11,
203 ATTRIBUTE12,
204 ATTRIBUTE13,
205 ATTRIBUTE14,
206 ATTRIBUTE15,
207 SECURITY_GROUP_ID,
208 OBJECT_VERSION_NUMBER,
209 ORIG_SYSTEM_REFERENCE,
210 REQUESTOR_ID,
211 FROM_REV_NUM,
212 TO_REV_NUM,
213 CSEQNUM
214 from AMW_AP_STEPS_B
215 where AP_STEP_ID = X_AP_STEP_ID
216 for update of AP_STEP_ID nowait;
217 recinfo c%rowtype;
218
219 cursor c1 is select
220 NAME,
221 DESCRIPTION,
222 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
223 from AMW_AP_STEPS_TL
224 where AP_STEP_ID = X_AP_STEP_ID
225 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
226 for update of AP_STEP_ID nowait;
227 begin
228 open c;
229 fetch c into recinfo;
230 if (c%notfound) then
231 close c;
232 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
233 app_exception.raise_exception;
234 end if;
235 close c;
236 if ( (recinfo.AUDIT_PROCEDURE_ID = X_AUDIT_PROCEDURE_ID)
237 AND ((recinfo.SEQNUM = X_SEQNUM) OR ((recinfo.SEQNUM is null) AND (X_SEQNUM is null)))
238 AND (recinfo.CSEQNUM = X_CSEQNUM)
239 AND (recinfo.FROM_REV_NUM = X_FROM_REV_NUM)
240 AND ((recinfo.TO_REV_NUM = X_TO_REV_NUM)
241 OR ((recinfo.TO_REV_NUM is null) AND (X_TO_REV_NUM is null)))
242 AND ((recinfo.SAMPLESIZE = X_SAMPLESIZE)
243 OR ((recinfo.SAMPLESIZE is null) AND (X_SAMPLESIZE is null)))
244 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
245 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
246 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
247 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
248 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
249 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
250 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
251 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
252 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
253 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
254 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
255 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
256 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
257 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
258 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
259 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
260 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
261 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
262 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
263 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
264 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
265 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
266 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
267 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
268 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
269 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
270 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
271 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
272 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
273 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
274 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
275 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
276 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
277 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
278 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
279 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
280 AND ((recinfo.ORIG_SYSTEM_REFERENCE = X_ORIG_SYSTEM_REFERENCE)
281 OR ((recinfo.ORIG_SYSTEM_REFERENCE is null) AND (X_ORIG_SYSTEM_REFERENCE is null)))
282 AND ((recinfo.REQUESTOR_ID = X_REQUESTOR_ID)
283 OR ((recinfo.REQUESTOR_ID is null) AND (X_REQUESTOR_ID is null)))
284 ) then
285 null;
286 else
287 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
288 app_exception.raise_exception;
289 end if;
290
291 for tlinfo in c1 loop
292 if (tlinfo.BASELANG = 'Y') then
293 if ( (tlinfo.NAME = X_NAME)
294 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
295 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
296 ) then
297 null;
298 else
299 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
300 app_exception.raise_exception;
301 end if;
302 end if;
303 end loop;
304 return;
305 end LOCK_ROW;
306
307 procedure UPDATE_ROW (
308 X_AP_STEP_ID in NUMBER,
309 X_ATTRIBUTE4 in VARCHAR2,
310 X_ATTRIBUTE5 in VARCHAR2,
311 X_ATTRIBUTE1 in VARCHAR2,
312 X_ATTRIBUTE6 in VARCHAR2,
313 X_ATTRIBUTE7 in VARCHAR2,
314 X_ATTRIBUTE8 in VARCHAR2,
315 X_ATTRIBUTE9 in VARCHAR2,
316 X_SAMPLESIZE in NUMBER,
317 X_AUDIT_PROCEDURE_ID in NUMBER,
318 X_SEQNUM in NUMBER,
319 X_ATTRIBUTE2 in VARCHAR2,
320 X_ATTRIBUTE3 in VARCHAR2,
321 X_ATTRIBUTE10 in VARCHAR2,
322 X_ATTRIBUTE11 in VARCHAR2,
323 X_ATTRIBUTE12 in VARCHAR2,
324 X_ATTRIBUTE13 in VARCHAR2,
325 X_ATTRIBUTE14 in VARCHAR2,
326 X_ATTRIBUTE15 in VARCHAR2,
327 X_SECURITY_GROUP_ID in NUMBER,
328 X_OBJECT_VERSION_NUMBER in NUMBER,
329 X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
330 X_REQUESTOR_ID in NUMBER,
331 X_ATTRIBUTE_CATEGORY in VARCHAR2,
332 X_NAME in VARCHAR2,
333 X_DESCRIPTION in VARCHAR2,
334 X_LAST_UPDATE_DATE in DATE,
335 X_LAST_UPDATED_BY in NUMBER,
336 X_LAST_UPDATE_LOGIN in NUMBER,
337 X_FROM_REV_NUM in NUMBER,
338 X_TO_REV_NUM in NUMBER,
339 X_CSEQNUM in VARCHAR2
340
341 ) is
342 begin
343 update AMW_AP_STEPS_B set
344 AUDIT_PROCEDURE_ID = X_AUDIT_PROCEDURE_ID,
345 SEQNUM = X_SEQNUM,
346 SAMPLESIZE = X_SAMPLESIZE,
347 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
348 ATTRIBUTE1 = X_ATTRIBUTE1,
349 ATTRIBUTE2 = X_ATTRIBUTE2,
350 ATTRIBUTE3 = X_ATTRIBUTE3,
354 ATTRIBUTE7 = X_ATTRIBUTE7,
351 ATTRIBUTE4 = X_ATTRIBUTE4,
352 ATTRIBUTE5 = X_ATTRIBUTE5,
353 ATTRIBUTE6 = X_ATTRIBUTE6,
355 ATTRIBUTE8 = X_ATTRIBUTE8,
356 ATTRIBUTE9 = X_ATTRIBUTE9,
357 ATTRIBUTE10 = X_ATTRIBUTE10,
358 ATTRIBUTE11 = X_ATTRIBUTE11,
359 ATTRIBUTE12 = X_ATTRIBUTE12,
360 ATTRIBUTE13 = X_ATTRIBUTE13,
361 ATTRIBUTE14 = X_ATTRIBUTE14,
362 ATTRIBUTE15 = X_ATTRIBUTE15,
363 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
364 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
365 ORIG_SYSTEM_REFERENCE = X_ORIG_SYSTEM_REFERENCE,
366 REQUESTOR_ID = X_REQUESTOR_ID,
367 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
368 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
369 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
370 FROM_REV_NUM = X_FROM_REV_NUM,
371 TO_REV_NUM = X_TO_REV_NUM,
372 CSEQNUM = X_CSEQNUM
373 where AP_STEP_ID = X_AP_STEP_ID;
374
375 if (sql%notfound) then
376 raise no_data_found;
377 end if;
378
379 update AMW_AP_STEPS_TL set
380 NAME = X_NAME,
381 DESCRIPTION = X_DESCRIPTION,
382 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
383 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
384 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
385 SOURCE_LANG = userenv('LANG'),
386 SOURCE_LANGUAGE = userenv('LANG')
387 where AP_STEP_ID = X_AP_STEP_ID
388 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
389
390 if (sql%notfound) then
391 raise no_data_found;
392 end if;
393 end UPDATE_ROW;
394
395 procedure DELETE_ROW (
396 X_AP_STEP_ID in NUMBER
397 ) is
398 begin
399 delete from AMW_AP_STEPS_TL
400 where AP_STEP_ID = X_AP_STEP_ID;
401
402 if (sql%notfound) then
403 raise no_data_found;
404 end if;
405
406 delete from AMW_AP_STEPS_B
407 where AP_STEP_ID = X_AP_STEP_ID;
408
409 if (sql%notfound) then
410 raise no_data_found;
411 end if;
412 end DELETE_ROW;
413
414 procedure ADD_LANGUAGE
415 is
416 begin
417 delete from AMW_AP_STEPS_TL T
418 where not exists
419 (select NULL
420 from AMW_AP_STEPS_B B
421 where B.AP_STEP_ID = T.AP_STEP_ID
422 );
423
424 update AMW_AP_STEPS_TL T set (
425 NAME,
426 DESCRIPTION
427 ) = (select
428 B.NAME,
429 B.DESCRIPTION
430 from AMW_AP_STEPS_TL B
431 where B.AP_STEP_ID = T.AP_STEP_ID
432 and B.LANGUAGE = T.SOURCE_LANG)
433 where (
434 T.AP_STEP_ID,
435 T.LANGUAGE
436 ) in (select
437 SUBT.AP_STEP_ID,
438 SUBT.LANGUAGE
439 from AMW_AP_STEPS_TL SUBB, AMW_AP_STEPS_TL SUBT
440 where SUBB.AP_STEP_ID = SUBT.AP_STEP_ID
441 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
442 and (SUBB.NAME <> SUBT.NAME
443 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
444 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
445 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
446 ));
447
448 insert into AMW_AP_STEPS_TL (
449 DESCRIPTION,
450 LAST_UPDATE_LOGIN,
451 SECURITY_GROUP_ID,
452 NAME,
453 AP_STEP_ID,
454 CREATED_BY,
455 CREATION_DATE,
456 LAST_UPDATED_BY,
457 LAST_UPDATE_DATE,
458 LANGUAGE,
459 SOURCE_LANG,
460 SOURCE_LANGUAGE
461 ) select
462 B.DESCRIPTION,
463 B.LAST_UPDATE_LOGIN,
464 B.SECURITY_GROUP_ID,
465 B.NAME,
466 B.AP_STEP_ID,
467 B.CREATED_BY,
468 B.CREATION_DATE,
469 B.LAST_UPDATED_BY,
470 B.LAST_UPDATE_DATE,
471 L.LANGUAGE_CODE,
472 B.SOURCE_LANG,
473 B.SOURCE_LANG
474 from AMW_AP_STEPS_TL B, FND_LANGUAGES L
475 where L.INSTALLED_FLAG in ('I', 'B')
476 and B.LANGUAGE = userenv('LANG')
477 and not exists
478 (select NULL
479 from AMW_AP_STEPS_TL T
480 where T.AP_STEP_ID = B.AP_STEP_ID
481 and T.LANGUAGE = L.LANGUAGE_CODE);
482 end ADD_LANGUAGE;
483
484 end AMW_AP_STEPS_PKG;
[Home] [Help]