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