1 package body QP_LIST_HEADERS_PKG as
2 /* $Header: QPXLHDRB.pls 120.2 2006/03/21 11:17:49 rnayani noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY /* file.sql.39 change */ VARCHAR2,
5 X_CONTEXT in VARCHAR2,
6 X_ATTRIBUTE1 in VARCHAR2,
7 X_ATTRIBUTE2 in VARCHAR2,
8 X_ATTRIBUTE3 in VARCHAR2,
9 X_ATTRIBUTE4 in VARCHAR2,
10 X_ATTRIBUTE5 in VARCHAR2,
11 X_ATTRIBUTE6 in VARCHAR2,
12 X_ATTRIBUTE7 in VARCHAR2,
13 X_ATTRIBUTE8 in VARCHAR2,
14 X_ATTRIBUTE9 in VARCHAR2,
15 X_ATTRIBUTE10 in VARCHAR2,
16 X_ATTRIBUTE11 in VARCHAR2,
17 X_ATTRIBUTE12 in VARCHAR2,
18 X_ATTRIBUTE13 in VARCHAR2,
19 X_ATTRIBUTE14 in VARCHAR2,
20 X_ATTRIBUTE15 in VARCHAR2,
21 X_CURRENCY_CODE in VARCHAR2,
22 X_SHIP_METHOD_CODE in VARCHAR2,
23 X_FREIGHT_TERMS_CODE in VARCHAR2,
24 X_LIST_HEADER_ID in NUMBER,
25 X_CREATION_DATE in DATE,
26 X_START_DATE_ACTIVE in DATE,
27 X_END_DATE_ACTIVE in DATE,
28 X_AUTOMATIC_FLAG in VARCHAR2,
29 X_LIST_TYPE_CODE in VARCHAR2,
30 X_TERMS_ID in NUMBER,
31 X_ROUNDING_FACTOR in NUMBER,
32 X_REQUEST_ID in NUMBER,
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 X_PROGRAM_APPLICATION_ID in NUMBER DEFAULT NULL,
38 X_PROGRAM_ID in NUMBER DEFAULT NULL,
39 X_PROGRAM_UPDATE_DATE in DATE DEFAULT NULL,
40 X_DISCOUNT_LINES_FLAG in VARCHAR2 DEFAULT NULL,
41 X_NAME in VARCHAR2,
42 X_DESCRIPTION in VARCHAR2,
43 X_VERSION_NO in VARCHAR2 DEFAULT NULL,
44 X_COMMENTS in VARCHAR2 DEFAULT NULL,
45 X_GSA_INDICATOR in VARCHAR2 DEFAULT NULL,
46 X_PRORATE_FLAG in VARCHAR2 DEFAULT NULL,
47 X_SOURCE_SYSTEM_CODE in VARCHAR2 DEFAULT NULL,
48 X_ASK_FOR_FLAG in VARCHAR2 DEFAULT NULL,
49 X_PARENT_LIST_HEADER_ID in NUMBER DEFAULT NULL,
50 X_START_DATE_ACTIVE_FIRST in DATE DEFAULT NULL,
51 X_END_DATE_ACTIVE_FIRST in DATE DEFAULT NULL,
52 X_ACTIVE_DATE_FIRST_TYPE in VARCHAR2 DEFAULT NULL,
53 X_START_DATE_ACTIVE_SECOND in DATE DEFAULT NULL,
54 X_END_DATE_ACTIVE_SECOND in DATE DEFAULT NULL,
55 X_ACTIVE_DATE_SECOND_TYPE in VARCHAR2 DEFAULT NULL,
56 X_ACTIVE_FLAG in VARCHAR2 DEFAULT NULL,
57 X_MOBILE_DOWNLOAD in VARCHAR2 DEFAULT NULL,
58 X_CURRENCY_HEADER_ID in NUMBER DEFAULT NULL,
59 X_PTE_CODE in VARCHAR2 DEFAULT NULL,
60 X_LIST_SOURCE_CODE in VARCHAR2 DEFAULT NULL,
61 X_ORIG_SYSTEM_HEADER_REF in VARCHAR2 DEFAULT NULL,
62 X_GLOBAL_FLAG in VARCHAR2 DEFAULT NULL,
63 X_ORIG_ORG_ID in NUMBER DEFAULT NULL,
64 X_VIEW_FLAG in VARCHAR2 DEFAULT NULL,
65 X_UPDATE_FLAG in VARCHAR2 DEFAULT NULL
66 ) is
67 l_rowid VARCHAR2(240);
68 cursor C is select ROWID from QP_LIST_HEADERS_B
69 where LIST_HEADER_ID = X_LIST_HEADER_ID
70 ;
71 begin
72 insert into QP_LIST_HEADERS_B (
73 LIST_SOURCE_CODE,
74 ORIG_SYSTEM_HEADER_REF,
75 GLOBAL_FLAG,
76 ORIG_ORG_ID,
77 MOBILE_DOWNLOAD,
78 CURRENCY_HEADER_ID,
79 PTE_CODE,
80 LIST_HEADER_ID,
81 REQUEST_ID,
82 LIST_TYPE_CODE,
83 START_DATE_ACTIVE,
84 END_DATE_ACTIVE,
85 AUTOMATIC_FLAG,
86 CURRENCY_CODE,
87 ROUNDING_FACTOR,
88 SHIP_METHOD_CODE,
89 FREIGHT_TERMS_CODE,
90 TERMS_ID,
91 COMMENTS,
92 DISCOUNT_LINES_FLAG,
93 GSA_INDICATOR,
94 PRORATE_FLAG,
95 SOURCE_SYSTEM_CODE,
96 ASK_FOR_FLAG,
97 ACTIVE_FLAG,
98 PARENT_LIST_HEADER_ID,
99 START_DATE_ACTIVE_FIRST,
100 END_DATE_ACTIVE_FIRST,
101 ACTIVE_DATE_FIRST_TYPE,
102 START_DATE_ACTIVE_SECOND,
103 END_DATE_ACTIVE_SECOND,
104 ACTIVE_DATE_SECOND_TYPE,
105 CONTEXT,
106 ATTRIBUTE1,
107 ATTRIBUTE2,
108 ATTRIBUTE3,
109 ATTRIBUTE4,
110 ATTRIBUTE5,
111 ATTRIBUTE6,
112 ATTRIBUTE7,
113 ATTRIBUTE8,
114 ATTRIBUTE9,
115 ATTRIBUTE10,
116 ATTRIBUTE11,
117 ATTRIBUTE12,
118 ATTRIBUTE13,
119 ATTRIBUTE14,
120 ATTRIBUTE15,
121 CREATION_DATE,
122 CREATED_BY,
123 LAST_UPDATE_DATE,
124 LAST_UPDATED_BY,
125 LAST_UPDATE_LOGIN,
126 PROGRAM_APPLICATION_ID,
127 PROGRAM_ID,
128 PROGRAM_UPDATE_DATE
129 ) values (
130 X_LIST_SOURCE_CODE,
131 --ENH Upgrade BOAPI for orig_sys...ref RAVI
132 nvl(X_ORIG_SYSTEM_HEADER_REF,QP_PRICE_LIST_UTIL.Get_Orig_Sys_Hdr(X_LIST_HEADER_ID)),
133 X_GLOBAL_FLAG,
134 X_ORIG_ORG_ID,
135 X_MOBILE_DOWNLOAD,
136 X_CURRENCY_HEADER_ID,
137 X_PTE_CODE,
138 X_LIST_HEADER_ID,
139 X_REQUEST_ID,
140 X_LIST_TYPE_CODE,
141 X_START_DATE_ACTIVE,
142 X_END_DATE_ACTIVE,
143 X_AUTOMATIC_FLAG,
144 X_CURRENCY_CODE,
145 X_ROUNDING_FACTOR,
146 X_SHIP_METHOD_CODE,
147 X_FREIGHT_TERMS_CODE,
148 X_TERMS_ID,
149 X_COMMENTS,
150 X_DISCOUNT_LINES_FLAG,
151 X_GSA_INDICATOR,
152 X_PRORATE_FLAG,
153 X_SOURCE_SYSTEM_CODE,
154 X_ASK_FOR_FLAG,
155 X_ACTIVE_FLAG,
156 X_PARENT_LIST_HEADER_ID,
157 X_START_DATE_ACTIVE_FIRST,
158 X_END_DATE_ACTIVE_FIRST,
159 X_ACTIVE_DATE_FIRST_TYPE,
160 X_START_DATE_ACTIVE_SECOND,
161 X_END_DATE_ACTIVE_SECOND,
162 X_ACTIVE_DATE_SECOND_TYPE,
163 X_CONTEXT,
164 X_ATTRIBUTE1,
165 X_ATTRIBUTE2,
166 X_ATTRIBUTE3,
167 X_ATTRIBUTE4,
168 X_ATTRIBUTE5,
169 X_ATTRIBUTE6,
170 X_ATTRIBUTE7,
171 X_ATTRIBUTE8,
172 X_ATTRIBUTE9,
173 X_ATTRIBUTE10,
174 X_ATTRIBUTE11,
175 X_ATTRIBUTE12,
176 X_ATTRIBUTE13,
177 X_ATTRIBUTE14,
178 X_ATTRIBUTE15,
179 X_CREATION_DATE,
180 X_CREATED_BY,
181 X_LAST_UPDATE_DATE,
182 X_LAST_UPDATED_BY,
183 X_LAST_UPDATE_LOGIN,
184 X_PROGRAM_APPLICATION_ID,
185 X_PROGRAM_ID,
186 X_PROGRAM_UPDATE_DATE
187 );
188
189 insert into QP_LIST_HEADERS_TL (
190 LIST_HEADER_ID,
191 CREATION_DATE,
192 CREATED_BY,
193 LAST_UPDATE_DATE,
194 LAST_UPDATED_BY,
195 LAST_UPDATE_LOGIN,
196 NAME,
197 DESCRIPTION,
198 VERSION_NO,
199 LANGUAGE,
200 SOURCE_LANG
201 ) select
202 X_LIST_HEADER_ID,
203 X_CREATION_DATE,
204 X_CREATED_BY,
205 X_LAST_UPDATE_DATE,
206 X_LAST_UPDATED_BY,
207 X_LAST_UPDATE_LOGIN,
208 X_NAME,
209 X_DESCRIPTION,
210 X_VERSION_NO,
211 L.LANGUAGE_CODE,
212 userenv('LANG')
213 from FND_LANGUAGES L
214 where L.INSTALLED_FLAG in ('I', 'B')
215 and not exists
216 (select NULL
217 from QP_LIST_HEADERS_TL T
218 where T.LIST_HEADER_ID = X_LIST_HEADER_ID
219 and T.LANGUAGE = L.LANGUAGE_CODE);
220
221 open c;
222 fetch c into l_rowid;
223 if (c%notfound) then
224 close c;
225 raise no_data_found;
226 end if;
227 X_ROWID := l_rowid;
228 close c;
229
230 end INSERT_ROW;
231
232 procedure LOCK_ROW (
233 X_CONTEXT in VARCHAR2,
234 X_ATTRIBUTE1 in VARCHAR2,
235 X_ATTRIBUTE2 in VARCHAR2,
236 X_ATTRIBUTE3 in VARCHAR2,
237 X_ATTRIBUTE4 in VARCHAR2,
238 X_ATTRIBUTE5 in VARCHAR2,
239 X_ATTRIBUTE6 in VARCHAR2,
240 X_ATTRIBUTE7 in VARCHAR2,
241 X_ATTRIBUTE8 in VARCHAR2,
242 X_ATTRIBUTE9 in VARCHAR2,
243 X_ATTRIBUTE10 in VARCHAR2,
244 X_ATTRIBUTE11 in VARCHAR2,
245 X_ATTRIBUTE12 in VARCHAR2,
246 X_ATTRIBUTE13 in VARCHAR2,
247 X_ATTRIBUTE14 in VARCHAR2,
248 X_ATTRIBUTE15 in VARCHAR2,
249 X_CURRENCY_CODE in VARCHAR2,
250 X_SHIP_METHOD_CODE in VARCHAR2,
251 X_FREIGHT_TERMS_CODE in VARCHAR2,
252 X_LIST_HEADER_ID in NUMBER,
253 X_START_DATE_ACTIVE in DATE,
254 X_END_DATE_ACTIVE in DATE,
255 X_AUTOMATIC_FLAG in VARCHAR2,
256 X_LIST_TYPE_CODE in VARCHAR2,
257 X_TERMS_ID in NUMBER,
258 X_ROUNDING_FACTOR in NUMBER,
259 X_REQUEST_ID in NUMBER,
260 X_PROGRAM_APPLICATION_ID in NUMBER DEFAULT NULL,
261 X_PROGRAM_ID in NUMBER DEFAULT NULL,
262 X_PROGRAM_UPDATE_DATE in DATE DEFAULT NULL,
263 X_DISCOUNT_LINES_FLAG in VARCHAR2 DEFAULT NULL,
264 X_NAME in VARCHAR2,
265 X_DESCRIPTION in VARCHAR2,
266 X_VERSION_NO in VARCHAR2 DEFAULT NULL,
267 X_COMMENTS in VARCHAR2 DEFAULT NULL,
268 X_GSA_INDICATOR in VARCHAR2 DEFAULT NULL,
269 X_PRORATE_FLAG in VARCHAR2 DEFAULT NULL,
270 X_SOURCE_SYSTEM_CODE in VARCHAR2 DEFAULT NULL,
271 X_ASK_FOR_FLAG in VARCHAR2 DEFAULT NULL,
272 X_PARENT_LIST_HEADER_ID in NUMBER DEFAULT NULL,
273 X_START_DATE_ACTIVE_FIRST in DATE DEFAULT NULL,
274 X_END_DATE_ACTIVE_FIRST in DATE DEFAULT NULL,
275 X_ACTIVE_DATE_FIRST_TYPE in VARCHAR2 DEFAULT NULL,
276 X_START_DATE_ACTIVE_SECOND in DATE DEFAULT NULL,
277 X_END_DATE_ACTIVE_SECOND in DATE DEFAULT NULL,
278 X_ACTIVE_DATE_SECOND_TYPE in VARCHAR2 DEFAULT NULL,
279 X_ACTIVE_FLAG in VARCHAR2 DEFAULT NULL,
280 X_MOBILE_DOWNLOAD in VARCHAR2 DEFAULT NULL,
281 X_CURRENCY_HEADER_ID in NUMBER DEFAULT NULL,
282 X_PTE_CODE in VARCHAR2 DEFAULT NULL,
283 X_LIST_SOURCE_CODE in VARCHAR2 DEFAULT NULL,
284 X_ORIG_SYSTEM_HEADER_REF in VARCHAR2 DEFAULT NULL,
285 X_GLOBAL_FLAG in VARCHAR2 DEFAULT NULL,
286 X_ORIG_ORG_ID in NUMBER DEFAULT NULL,
287 X_VIEW_FLAG in VARCHAR2 DEFAULT NULL,
288 X_UPDATE_FLAG in VARCHAR2 DEFAULT NULL
289 ) is
290 cursor c is select
291 LIST_SOURCE_CODE,
292 ORIG_SYSTEM_HEADER_REF,
293 GLOBAL_FLAG,
294 ORIG_ORG_ID,
295 MOBILE_DOWNLOAD,
296 CURRENCY_HEADER_ID,
297 PTE_CODE,
298 REQUEST_ID,
299 LIST_TYPE_CODE,
300 START_DATE_ACTIVE,
301 END_DATE_ACTIVE,
302 AUTOMATIC_FLAG,
303 CURRENCY_CODE,
304 ROUNDING_FACTOR,
305 SHIP_METHOD_CODE,
306 FREIGHT_TERMS_CODE,
307 TERMS_ID,
308 COMMENTS,
309 DISCOUNT_LINES_FLAG,
310 GSA_INDICATOR,
311 PRORATE_FLAG,
312 SOURCE_SYSTEM_CODE,
313 ASK_FOR_FLAG,
314 ACTIVE_FLAG,
315 PARENT_LIST_HEADER_ID,
316 START_DATE_ACTIVE_FIRST,
317 END_DATE_ACTIVE_FIRST,
318 ACTIVE_DATE_FIRST_TYPE,
319 START_DATE_ACTIVE_SECOND,
320 END_DATE_ACTIVE_SECOND,
321 ACTIVE_DATE_SECOND_TYPE,
322 CONTEXT,
323 ATTRIBUTE1,
324 ATTRIBUTE2,
325 ATTRIBUTE3,
326 ATTRIBUTE4,
327 ATTRIBUTE5,
328 ATTRIBUTE6,
329 ATTRIBUTE7,
330 ATTRIBUTE8,
331 ATTRIBUTE9,
332 ATTRIBUTE10,
333 ATTRIBUTE11,
334 ATTRIBUTE12,
335 ATTRIBUTE13,
336 ATTRIBUTE14,
337 ATTRIBUTE15
338 from QP_LIST_HEADERS_B
339 where LIST_HEADER_ID = X_LIST_HEADER_ID
340 for update of LIST_HEADER_ID nowait;
341 recinfo c%rowtype;
342
343 cursor c1 is select
344 NAME,
345 DESCRIPTION,
346 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG,
347 VERSION_NO
348 from QP_LIST_HEADERS_TL
349 where LIST_HEADER_ID = X_LIST_HEADER_ID
350 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
351 for update of LIST_HEADER_ID nowait;
352 begin
353 open c;
354 fetch c into recinfo;
355 if (c%notfound) then
356 close c;
357 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
358 app_exception.raise_exception;
359 end if;
360 close c;
361 if ( ((recinfo.LIST_SOURCE_CODE = X_LIST_SOURCE_CODE)
362 OR ((recinfo.LIST_SOURCE_CODE is null) AND (X_LIST_SOURCE_CODE is null)))
363 AND ((recinfo.ORIG_SYSTEM_HEADER_REF = X_ORIG_SYSTEM_HEADER_REF)
364 OR ((recinfo.ORIG_SYSTEM_HEADER_REF is null) AND (X_ORIG_SYSTEM_HEADER_REF is null)))
365 AND ((recinfo.GLOBAL_FLAG = X_GLOBAL_FLAG)
366 OR ((recinfo.GLOBAL_FLAG is null) AND (X_GLOBAL_FLAG is null)))
367 AND ((recinfo.ORIG_ORG_ID = X_ORIG_ORG_ID)
368 OR ((recinfo.ORIG_ORG_ID is null) AND (X_ORIG_ORG_ID is null)))
369 AND ((recinfo.MOBILE_DOWNLOAD = X_MOBILE_DOWNLOAD)
370 OR ((recinfo.MOBILE_DOWNLOAD is null) AND (X_MOBILE_DOWNLOAD is null)))
371 AND ((recinfo.CURRENCY_HEADER_ID = X_CURRENCY_HEADER_ID)
372 OR ((recinfo.CURRENCY_HEADER_ID is null) AND (X_CURRENCY_HEADER_ID is null)))
373 AND ((recinfo.PTE_CODE = X_PTE_CODE)
374 OR ((recinfo.PTE_CODE is null) AND (X_PTE_CODE is null)))
375 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
379 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
376 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
377 AND (recinfo.LIST_TYPE_CODE = X_LIST_TYPE_CODE)
378 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
380 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
381 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
382 AND ((recinfo.AUTOMATIC_FLAG = X_AUTOMATIC_FLAG)
383 OR ((recinfo.AUTOMATIC_FLAG is null) AND (X_AUTOMATIC_FLAG is null)))
384 AND ((recinfo.CURRENCY_CODE = X_CURRENCY_CODE)
385 OR ((recinfo.CURRENCY_CODE is null) AND (X_CURRENCY_CODE is null)))
386 AND ((recinfo.ROUNDING_FACTOR = X_ROUNDING_FACTOR)
387 OR ((recinfo.ROUNDING_FACTOR is null) AND (X_ROUNDING_FACTOR is null)))
388 AND ((recinfo.SHIP_METHOD_CODE = X_SHIP_METHOD_CODE)
389 OR ((recinfo.SHIP_METHOD_CODE is null) AND (X_SHIP_METHOD_CODE is null)))
390 AND ((recinfo.FREIGHT_TERMS_CODE = X_FREIGHT_TERMS_CODE)
391 OR ((recinfo.FREIGHT_TERMS_CODE is null) AND (X_FREIGHT_TERMS_CODE is null)))
392 AND ((recinfo.TERMS_ID = X_TERMS_ID)
393 OR ((recinfo.TERMS_ID is null) AND (X_TERMS_ID is null)))
394 AND ((recinfo.COMMENTS = X_COMMENTS)
395 OR ((recinfo.COMMENTS is null) AND (X_COMMENTS is null)))
396 AND ((recinfo.DISCOUNT_LINES_FLAG = X_DISCOUNT_LINES_FLAG)
397 OR ((recinfo.DISCOUNT_LINES_FLAG is null) AND (X_DISCOUNT_LINES_FLAG is null)))
398 AND ((recinfo.GSA_INDICATOR = X_GSA_INDICATOR)
399 OR ((recinfo.GSA_INDICATOR is null) AND (X_GSA_INDICATOR is null)))
400 AND ((recinfo.PRORATE_FLAG = X_PRORATE_FLAG)
401 OR ((recinfo.PRORATE_FLAG is null) AND (X_PRORATE_FLAG is null)))
402 AND ((recinfo.SOURCE_SYSTEM_CODE = X_SOURCE_SYSTEM_CODE)
403 OR ((recinfo.SOURCE_SYSTEM_CODE is null) AND (X_SOURCE_SYSTEM_CODE is null)))
404 AND ((recinfo.ASK_FOR_FLAG = X_ASK_FOR_FLAG)
405 OR ((recinfo.ASK_FOR_FLAG is null) AND (X_ASK_FOR_FLAG is null)))
406 AND ((recinfo.ACTIVE_FLAG = X_ACTIVE_FLAG)
407 OR ((recinfo.ACTIVE_FLAG is null) AND (X_ACTIVE_FLAG is null)))
408 AND ((recinfo.PARENT_LIST_HEADER_ID = X_PARENT_LIST_HEADER_ID)
409 OR ((recinfo.PARENT_LIST_HEADER_ID is null) AND (X_PARENT_LIST_HEADER_ID is null)))
410 AND ((recinfo.START_DATE_ACTIVE_FIRST = X_START_DATE_ACTIVE_FIRST)
411 OR ((recinfo.START_DATE_ACTIVE_FIRST is null) AND (X_START_DATE_ACTIVE_FIRST is null)))
412 AND ((recinfo.END_DATE_ACTIVE_FIRST = X_END_DATE_ACTIVE_FIRST)
413 OR ((recinfo.END_DATE_ACTIVE_FIRST is null) AND (X_END_DATE_ACTIVE_FIRST is null)))
414 AND ((recinfo.ACTIVE_DATE_FIRST_TYPE = X_ACTIVE_DATE_FIRST_TYPE)
415 OR ((recinfo.ACTIVE_DATE_FIRST_TYPE is null) AND (X_ACTIVE_DATE_FIRST_TYPE is null)))
416 AND ((recinfo.START_DATE_ACTIVE_SECOND = X_START_DATE_ACTIVE_SECOND)
417 OR ((recinfo.START_DATE_ACTIVE_SECOND is null) AND (X_START_DATE_ACTIVE_SECOND is null)))
418 AND ((recinfo.END_DATE_ACTIVE_SECOND = X_END_DATE_ACTIVE_SECOND)
419 OR ((recinfo.END_DATE_ACTIVE_SECOND is null) AND (X_END_DATE_ACTIVE_SECOND is null)))
420 AND ((recinfo.ACTIVE_DATE_SECOND_TYPE = X_ACTIVE_DATE_SECOND_TYPE)
421 OR ((recinfo.ACTIVE_DATE_SECOND_TYPE is null) AND (X_ACTIVE_DATE_SECOND_TYPE is null)))
422 AND ((recinfo.CONTEXT = X_CONTEXT)
423 OR ((recinfo.CONTEXT is null) AND (X_CONTEXT is null)))
424 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
425 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
426 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
427 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
428 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
429 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
430 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
431 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
432 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
433 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
434 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
435 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
436 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
437 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
438 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
439 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
440 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
441 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
442 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
443 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
444 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
445 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
446 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
447 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
448 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
449 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
450 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
451 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
452 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
453 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
454 ) then
455 null;
456 else
457 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
458 app_exception.raise_exception;
459 end if;
460
461 for tlinfo in c1 loop
462 if (tlinfo.BASELANG = 'Y') then
463 if ( (tlinfo.NAME = X_NAME)
464 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
465 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
466 AND ((tlinfo.VERSION_NO = X_VERSION_NO)
467 OR ((tlinfo.VERSION_NO is null) AND (X_VERSION_NO is null)))
468 ) then
469 null;
470 else
471 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
472 app_exception.raise_exception;
473 end if;
474 end if;
475 end loop;
476 return;
477 end LOCK_ROW;
478
479 procedure UPDATE_ROW (
480 X_CONTEXT in VARCHAR2,
481 X_ATTRIBUTE1 in VARCHAR2,
482 X_ATTRIBUTE2 in VARCHAR2,
483 X_ATTRIBUTE3 in VARCHAR2,
484 X_ATTRIBUTE4 in VARCHAR2,
485 X_ATTRIBUTE5 in VARCHAR2,
486 X_ATTRIBUTE6 in VARCHAR2,
487 X_ATTRIBUTE7 in VARCHAR2,
488 X_ATTRIBUTE8 in VARCHAR2,
489 X_ATTRIBUTE9 in VARCHAR2,
490 X_ATTRIBUTE10 in VARCHAR2,
491 X_ATTRIBUTE11 in VARCHAR2,
492 X_ATTRIBUTE12 in VARCHAR2,
493 X_ATTRIBUTE13 in VARCHAR2,
494 X_ATTRIBUTE14 in VARCHAR2,
495 X_ATTRIBUTE15 in VARCHAR2,
496 X_CURRENCY_CODE in VARCHAR2,
497 X_SHIP_METHOD_CODE in VARCHAR2,
498 X_FREIGHT_TERMS_CODE in VARCHAR2,
499 X_LIST_HEADER_ID in NUMBER,
500 X_START_DATE_ACTIVE in DATE,
501 X_END_DATE_ACTIVE in DATE,
502 X_AUTOMATIC_FLAG in VARCHAR2,
503 X_LIST_TYPE_CODE in VARCHAR2,
504 X_TERMS_ID in NUMBER,
505 X_ROUNDING_FACTOR in NUMBER,
506 X_REQUEST_ID in NUMBER,
507 X_LAST_UPDATE_DATE in DATE,
508 X_LAST_UPDATED_BY in NUMBER,
509 X_LAST_UPDATE_LOGIN in NUMBER,
510 X_PROGRAM_APPLICATION_ID in NUMBER DEFAULT NULL,
514 X_NAME in VARCHAR2,
511 X_PROGRAM_ID in NUMBER DEFAULT NULL,
512 X_PROGRAM_UPDATE_DATE in DATE DEFAULT NULL,
513 X_DISCOUNT_LINES_FLAG in VARCHAR2 DEFAULT NULL,
515 X_DESCRIPTION in VARCHAR2,
516 X_VERSION_NO in VARCHAR2 DEFAULT NULL,
517 X_COMMENTS in VARCHAR2 DEFAULT NULL,
518 X_GSA_INDICATOR in VARCHAR2 DEFAULT NULL,
519 X_PRORATE_FLAG in VARCHAR2 DEFAULT NULL,
520 X_SOURCE_SYSTEM_CODE in VARCHAR2 DEFAULT NULL,
521 X_ASK_FOR_FLAG in VARCHAR2 DEFAULT NULL,
522 X_PARENT_LIST_HEADER_ID in NUMBER DEFAULT NULL,
523 X_START_DATE_ACTIVE_FIRST in DATE DEFAULT NULL,
524 X_END_DATE_ACTIVE_FIRST in DATE DEFAULT NULL,
525 X_ACTIVE_DATE_FIRST_TYPE in VARCHAR2 DEFAULT NULL,
526 X_START_DATE_ACTIVE_SECOND in DATE DEFAULT NULL,
527 X_END_DATE_ACTIVE_SECOND in DATE DEFAULT NULL,
528 X_ACTIVE_DATE_SECOND_TYPE in VARCHAR2 DEFAULT NULL,
529 X_ACTIVE_FLAG in VARCHAR2 DEFAULT NULL,
530 X_MOBILE_DOWNLOAD in VARCHAR2 DEFAULT NULL,
531 X_CURRENCY_HEADER_ID in NUMBER DEFAULT NULL,
532 X_PTE_CODE in VARCHAR2 DEFAULT NULL,
533 X_LIST_SOURCE_CODE in VARCHAR2 DEFAULT NULL,
534 X_ORIG_SYSTEM_HEADER_REF in VARCHAR2 DEFAULT NULL,
535 X_GLOBAL_FLAG in VARCHAR2 DEFAULT NULL,
536 X_ORIG_ORG_ID in NUMBER DEFAULT NULL,
537 X_VIEW_FLAG in VARCHAR2 DEFAULT NULL,
538 X_UPDATE_FLAG in VARCHAR2 DEFAULT NULL
539 ) is
540 begin
541 update QP_LIST_HEADERS_B set
542 LIST_SOURCE_CODE = X_LIST_SOURCE_CODE,
543 ORIG_SYSTEM_HEADER_REF = X_ORIG_SYSTEM_HEADER_REF,
544 GLOBAL_FLAG = X_GLOBAL_FLAG,
545 ORIG_ORG_ID = X_ORIG_ORG_ID,
546 MOBILE_DOWNLOAD = X_MOBILE_DOWNLOAD,
547 CURRENCY_HEADER_ID = X_CURRENCY_HEADER_ID,
548 PTE_CODE = X_PTE_CODE,
549 REQUEST_ID = X_REQUEST_ID,
550 LIST_TYPE_CODE = X_LIST_TYPE_CODE,
551 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
552 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
553 AUTOMATIC_FLAG = X_AUTOMATIC_FLAG,
554 CURRENCY_CODE = X_CURRENCY_CODE,
555 ROUNDING_FACTOR = X_ROUNDING_FACTOR,
556 SHIP_METHOD_CODE = X_SHIP_METHOD_CODE,
557 FREIGHT_TERMS_CODE = X_FREIGHT_TERMS_CODE,
558 TERMS_ID = X_TERMS_ID,
559 COMMENTS = X_COMMENTS,
560 DISCOUNT_LINES_FLAG = X_DISCOUNT_LINES_FLAG,
561 GSA_INDICATOR = X_GSA_INDICATOR,
562 PRORATE_FLAG = X_PRORATE_FLAG,
563 SOURCE_SYSTEM_CODE = X_SOURCE_SYSTEM_CODE,
564 ASK_FOR_FLAG = X_ASK_FOR_FLAG,
565 ACTIVE_FLAG = X_ACTIVE_FLAG,
566 PARENT_LIST_HEADER_ID = X_PARENT_LIST_HEADER_ID,
567 START_DATE_ACTIVE_FIRST = X_START_DATE_ACTIVE_FIRST,
568 END_DATE_ACTIVE_FIRST = X_END_DATE_ACTIVE_FIRST,
569 ACTIVE_DATE_FIRST_TYPE = X_ACTIVE_DATE_FIRST_TYPE,
570 START_DATE_ACTIVE_SECOND = X_START_DATE_ACTIVE_SECOND,
571 END_DATE_ACTIVE_SECOND = X_END_DATE_ACTIVE_SECOND,
572 ACTIVE_DATE_SECOND_TYPE = X_ACTIVE_DATE_SECOND_TYPE,
573 CONTEXT = X_CONTEXT,
574 ATTRIBUTE1 = X_ATTRIBUTE1,
575 ATTRIBUTE2 = X_ATTRIBUTE2,
576 ATTRIBUTE3 = X_ATTRIBUTE3,
577 ATTRIBUTE4 = X_ATTRIBUTE4,
578 ATTRIBUTE5 = X_ATTRIBUTE5,
579 ATTRIBUTE6 = X_ATTRIBUTE6,
580 ATTRIBUTE7 = X_ATTRIBUTE7,
581 ATTRIBUTE8 = X_ATTRIBUTE8,
582 ATTRIBUTE9 = X_ATTRIBUTE9,
583 ATTRIBUTE10 = X_ATTRIBUTE10,
584 ATTRIBUTE11 = X_ATTRIBUTE11,
585 ATTRIBUTE12 = X_ATTRIBUTE12,
586 ATTRIBUTE13 = X_ATTRIBUTE13,
587 ATTRIBUTE14 = X_ATTRIBUTE14,
588 ATTRIBUTE15 = X_ATTRIBUTE15,
589 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
590 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
591 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
592 where LIST_HEADER_ID = X_LIST_HEADER_ID;
593
594 if (sql%notfound) then
595 raise no_data_found;
596 end if;
597
598 update QP_LIST_HEADERS_TL set
599 NAME = X_NAME,
600 DESCRIPTION = X_DESCRIPTION,
601 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
602 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
603 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
604 SOURCE_LANG = userenv('LANG'),
605 VERSION_NO = X_VERSION_NO
606 where LIST_HEADER_ID = X_LIST_HEADER_ID
607 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
608
609 if (sql%notfound) then
610 raise no_data_found;
611 end if;
612 end UPDATE_ROW;
613
614 procedure DELETE_ROW (
615 X_LIST_HEADER_ID in NUMBER
616 ) is
617 begin
618 delete from QP_LIST_HEADERS_TL
619 where LIST_HEADER_ID = X_LIST_HEADER_ID;
620
621 if (sql%notfound) then
622 raise no_data_found;
623 end if;
624
625 delete from QP_LIST_HEADERS_B
626 where LIST_HEADER_ID = X_LIST_HEADER_ID;
627
628 if (sql%notfound) then
629 raise no_data_found;
630 end if;
631 end DELETE_ROW;
632
633 procedure ADD_LANGUAGE
634 is
635 begin
636 delete from QP_LIST_HEADERS_TL T
637 where not exists
638 (select NULL
639 from QP_LIST_HEADERS_B B
640 where B.LIST_HEADER_ID = T.LIST_HEADER_ID
641 );
642
643 update QP_LIST_HEADERS_TL T set (
644 NAME,
645 DESCRIPTION
646 ) = (select
647 B.NAME,
648 B.DESCRIPTION
649 from QP_LIST_HEADERS_TL B
650 where B.LIST_HEADER_ID = T.LIST_HEADER_ID
651 and B.LANGUAGE = T.SOURCE_LANG)
652 where (
653 T.LIST_HEADER_ID,
654 T.LANGUAGE
655 ) in (select
656 SUBT.LIST_HEADER_ID,
657 SUBT.LANGUAGE
658 from QP_LIST_HEADERS_TL SUBB, QP_LIST_HEADERS_TL SUBT
659 where SUBB.LIST_HEADER_ID = SUBT.LIST_HEADER_ID
660 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
661 and (SUBB.NAME <> SUBT.NAME
662 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
663 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
664 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
665 ));
666
667 insert into QP_LIST_HEADERS_TL (
668 LIST_HEADER_ID,
669 CREATION_DATE,
670 CREATED_BY,
671 LAST_UPDATE_DATE,
672 LAST_UPDATED_BY,
673 LAST_UPDATE_LOGIN,
674 NAME,
675 DESCRIPTION,
676 VERSION_NO,
677 LANGUAGE,
678 SOURCE_LANG
679 ) select
680 B.LIST_HEADER_ID,
681 B.CREATION_DATE,
682 B.CREATED_BY,
683 B.LAST_UPDATE_DATE,
684 B.LAST_UPDATED_BY,
685 B.LAST_UPDATE_LOGIN,
686 B.NAME,
687 B.DESCRIPTION,
688 B.VERSION_NO,
689 L.LANGUAGE_CODE,
690 B.SOURCE_LANG
691 from QP_LIST_HEADERS_TL B, FND_LANGUAGES L
692 where L.INSTALLED_FLAG in ('I', 'B')
693 and B.LANGUAGE = userenv('LANG')
694 and not exists
695 (select NULL
696 from QP_LIST_HEADERS_TL T
697 where T.LIST_HEADER_ID = B.LIST_HEADER_ID
698 and T.LANGUAGE = L.LANGUAGE_CODE);
699 end ADD_LANGUAGE;
700
701 end QP_LIST_HEADERS_PKG;
[Home] [Help]