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