[Home] [Help]
PACKAGE BODY: APPS.QP_SECU_CTRL_PVT
Source
1 package body qp_secu_ctrl_pvt as
2 /* $Header: QPXSECCB.pls 120.3.12010000.4 2009/04/10 06:49:49 jputta ship $ */
3
4 procedure switch(
5 err_buff out nocopy varchar2,
6 retcode out nocopy number,
7 p_security_control in varchar2,
8 p_control in varchar2 default g_n
9 )
10 is
11 l_security_control_prof varchar2(30);
12 l_stmt varchar2(20000);
13 l_save boolean;
14 begin
15 l_security_control_prof := nvl(fnd_profile.value(g_security_control_prof), g_security_off);
16
17 if (p_security_control = g_security_off and (l_security_control_prof = g_security_on or p_control = g_y)) then
18 begin
19 l_save := fnd_profile.save(x_name => g_security_control_prof,
20 x_value => g_security_off,
21 x_level_name => 'SITE');
22
23 l_stmt :=
24 'create or replace view QP_ARCH_SECU_LIST_HDRS_V as
25 select
26 AB.ROWID ROW_ID,
27 AB.CONTEXT,
28 AB.ATTRIBUTE1,
29 AB.ATTRIBUTE2,
30 AB.ATTRIBUTE3,
31 AB.ATTRIBUTE4,
32 AB.ATTRIBUTE5,
33 AB.ATTRIBUTE6,
34 AB.ATTRIBUTE7,
35 AB.ATTRIBUTE8,
36 AB.ATTRIBUTE9,
37 AB.ATTRIBUTE10,
38 AB.ATTRIBUTE11,
39 AB.ATTRIBUTE12,
40 AB.ATTRIBUTE13,
41 AB.ATTRIBUTE14,
42 AB.ATTRIBUTE15,
43 AB.CURRENCY_CODE,
44 AB.SHIP_METHOD_CODE,
45 AB.FREIGHT_TERMS_CODE,
46 AB.LIST_HEADER_ID,
47 AB.CREATION_DATE,
48 AB.START_DATE_ACTIVE,
49 AB.END_DATE_ACTIVE,
50 AB.AUTOMATIC_FLAG,
51 AB.LIST_TYPE_CODE,
52 AB.TERMS_ID,
53 AB.ROUNDING_FACTOR,
54 AB.REQUEST_ID,
55 AB.CREATED_BY,
56 AB.LAST_UPDATE_DATE,
57 AB.LAST_UPDATED_BY,
58 AB.LAST_UPDATE_LOGIN,
59 AB.PROGRAM_APPLICATION_ID,
60 AB.PROGRAM_ID,
61 AB.PROGRAM_UPDATE_DATE,
62 AB.DISCOUNT_LINES_FLAG,
63 AT.NAME,
64 AT.DESCRIPTION,
65 AB.COMMENTS,
66 AB.GSA_INDICATOR,
67 AB.PRORATE_FLAG,
68 AB.SOURCE_SYSTEM_CODE,
69 AT.VERSION_NO,
70 AB.ACTIVE_FLAG,
71 AB.MOBILE_DOWNLOAD,
72 AB.CURRENCY_HEADER_ID,
73 AB.PTE_CODE,
74 AB.LIST_SOURCE_CODE,
75 AB.ORIG_SYSTEM_HEADER_REF,
76 AB.GLOBAL_FLAG,
77 AB.ORIG_ORG_ID,
78 ''Y'' as VIEW_FLAG,
79 ''Y'' as UPDATE_FLAG,
80 AB.SHAREABLE_FLAG,
81 AB.SOLD_TO_ORG_ID,
82 AB.LIMIT_EXISTS_FLAG,
83 AB.ARCH_PURG_REQUEST_ID
84 FROM QP_ARCH_LIST_HEADERS_TL AT, QP_ARCH_LIST_HEADERS_B AB
85 where AB.LIST_HEADER_ID = AT.LIST_HEADER_ID
86 AND AB.ARCH_PURG_REQUEST_ID = AT.ARCH_PURG_REQUEST_ID
87 AND AT.LANGUAGE = userenv(''LANG'')
88 AND AB.LIST_TYPE_CODE = ''PRL''';
89
90 execute immediate l_stmt;
91
92 l_stmt :=
93 'create or replace view QP_ARCH_SECU_LIST_HDRS_VL as
94 select
95 AB.ROWID ROW_ID,
96 AB.CONTEXT,
97 AB.ATTRIBUTE1,
98 AB.ATTRIBUTE2,
99 AB.ATTRIBUTE3,
100 AB.ATTRIBUTE4,
101 AB.ATTRIBUTE5,
102 AB.ATTRIBUTE6,
103 AB.ATTRIBUTE7,
104 AB.ATTRIBUTE8,
105 AB.ATTRIBUTE9,
106 AB.ATTRIBUTE10,
107 AB.ATTRIBUTE11,
108 AB.ATTRIBUTE12,
109 AB.ATTRIBUTE13,
110 AB.ATTRIBUTE14,
111 AB.ATTRIBUTE15,
112 AB.CURRENCY_CODE,
113 AB.SHIP_METHOD_CODE,
114 AB.FREIGHT_TERMS_CODE,
115 AB.LIST_HEADER_ID,
116 AB.CREATION_DATE,
117 AB.START_DATE_ACTIVE,
118 AB.END_DATE_ACTIVE,
119 AB.AUTOMATIC_FLAG,
120 AB.LIST_TYPE_CODE,
121 AB.TERMS_ID,
122 AB.ROUNDING_FACTOR,
123 AB.REQUEST_ID,
124 AB.CREATED_BY,
125 AB.LAST_UPDATE_DATE,
126 AB.LAST_UPDATED_BY,
127 AB.LAST_UPDATE_LOGIN,
128 AB.PROGRAM_APPLICATION_ID,
129 AB.PROGRAM_ID,
130 AB.PROGRAM_UPDATE_DATE,
131 AB.DISCOUNT_LINES_FLAG,
132 AT.NAME,
133 AT.DESCRIPTION,
134 AT.VERSION_NO,
135 AB.COMMENTS,
136 AB.GSA_INDICATOR,
137 AB.PRORATE_FLAG,
138 AB.SOURCE_SYSTEM_CODE,
139 AB.ASK_FOR_FLAG,
140 AB.PARENT_LIST_HEADER_ID,
141 AB.START_DATE_ACTIVE_FIRST,
142 AB.END_DATE_ACTIVE_FIRST,
143 AB.ACTIVE_DATE_FIRST_TYPE,
144 AB.START_DATE_ACTIVE_SECOND,
145 AB.END_DATE_ACTIVE_SECOND,
146 AB.ACTIVE_DATE_SECOND_TYPE,
147 AB.ACTIVE_FLAG,
148 AB.MOBILE_DOWNLOAD,
149 AB.CURRENCY_HEADER_ID,
150 AB.PTE_CODE,
151 AB.LIST_SOURCE_CODE,
152 AB.ORIG_SYSTEM_HEADER_REF,
153 AB.GLOBAL_FLAG,
154 AB.ORIG_ORG_ID,
155 ''Y'' as VIEW_FLAG, ''Y'' as UPDATE_FLAG,
156 AB.SHAREABLE_FLAG,
157 AB.SOLD_TO_ORG_ID,
158 AB.LIMIT_EXISTS_FLAG,
159 AB.ARCH_PURG_REQUEST_ID
160 FROM QP_ARCH_LIST_HEADERS_TL AT, QP_ARCH_LIST_HEADERS_B AB
161 where AB.LIST_HEADER_ID = AT.LIST_HEADER_ID
162 and AB.ARCH_PURG_REQUEST_ID = AT.ARCH_PURG_REQUEST_ID
163 AND AT.LANGUAGE = userenv(''LANG'')';
164
165 execute immediate l_stmt;
166
167 l_stmt :=
168 'create or replace view QP_SECU_LIST_HEADERS_V as
169 select
170 B.ROWID ROW_ID,
171 B.CONTEXT,
172 B.ATTRIBUTE1,
173 B.ATTRIBUTE2,
174 B.ATTRIBUTE3,
175 B.ATTRIBUTE4,
176 B.ATTRIBUTE5,
177 B.ATTRIBUTE6,
178 B.ATTRIBUTE7,
179 B.ATTRIBUTE8,
180 B.ATTRIBUTE9,
181 B.ATTRIBUTE10,
182 B.ATTRIBUTE11,
183 B.ATTRIBUTE12,
184 B.ATTRIBUTE13,
185 B.ATTRIBUTE14,
186 B.ATTRIBUTE15,
187 B.CURRENCY_CODE,
188 B.SHIP_METHOD_CODE,
189 B.FREIGHT_TERMS_CODE,
190 B.LIST_HEADER_ID,
191 B.CREATION_DATE,
192 B.START_DATE_ACTIVE,
193 B.END_DATE_ACTIVE,
194 B.AUTOMATIC_FLAG,
195 B.LIST_TYPE_CODE,
196 B.TERMS_ID,
197 B.ROUNDING_FACTOR,
198 B.REQUEST_ID,
199 B.CREATED_BY,
200 B.LAST_UPDATE_DATE,
201 B.LAST_UPDATED_BY,
202 B.LAST_UPDATE_LOGIN,
203 B.PROGRAM_APPLICATION_ID,
204 B.PROGRAM_ID,
205 B.PROGRAM_UPDATE_DATE,
206 B.DISCOUNT_LINES_FLAG,
207 T.NAME,
208 T.DESCRIPTION,
209 B.COMMENTS,
210 B.GSA_INDICATOR,
211 B.PRORATE_FLAG,
212 B.SOURCE_SYSTEM_CODE,
213 T.VERSION_NO, B.ACTIVE_FLAG,
214 B.MOBILE_DOWNLOAD,
215 B.CURRENCY_HEADER_ID,
216 B.PTE_CODE,
217 B.LIST_SOURCE_CODE,
218 B.ORIG_SYSTEM_HEADER_REF,
219 B.GLOBAL_FLAG,
220 B.ORIG_ORG_ID,
221 ''Y'' as VIEW_FLAG,
222 ''Y'' as UPDATE_FLAG,
223 B.SHAREABLE_FLAG,
224 B.SOLD_TO_ORG_ID,
225 B.LIMIT_EXISTS_FLAG,
226 B.LOCKED_FROM_LIST_HEADER_ID
227 FROM QP_LIST_HEADERS_ALL_B B, QP_LIST_HEADERS_TL T
228 where B.LIST_HEADER_ID = T.LIST_HEADER_ID
229 AND T.LANGUAGE = userenv(''LANG'')
230 AND B.LIST_TYPE_CODE = ''PRL''';
231
232 execute immediate l_stmt;
233
234 l_stmt :=
235 'create or replace view QP_SECU_LIST_HEADERS_VL as
236 select
237 B.ROWID ROW_ID,
238 B.CONTEXT,
239 B.ATTRIBUTE1,
240 B.ATTRIBUTE2,
241 B.ATTRIBUTE3,
242 B.ATTRIBUTE4,
243 B.ATTRIBUTE5,
244 B.ATTRIBUTE6,
245 B.ATTRIBUTE7,
246 B.ATTRIBUTE8,
247 B.ATTRIBUTE9,
248 B.ATTRIBUTE10,
249 B.ATTRIBUTE11,
250 B.ATTRIBUTE12,
251 B.ATTRIBUTE13,
252 B.ATTRIBUTE14,
253 B.ATTRIBUTE15,
254 B.CURRENCY_CODE,
255 B.SHIP_METHOD_CODE,
256 B.FREIGHT_TERMS_CODE,
257 B.LIST_HEADER_ID,
258 B.CREATION_DATE,
259 B.START_DATE_ACTIVE,
260 B.END_DATE_ACTIVE,
261 B.AUTOMATIC_FLAG,
262 B.LIST_TYPE_CODE,
263 B.TERMS_ID,
264 B.ROUNDING_FACTOR,
265 B.REQUEST_ID,
266 B.CREATED_BY,
267 B.LAST_UPDATE_DATE,
268 B.LAST_UPDATED_BY,
269 B.LAST_UPDATE_LOGIN,
270 B.PROGRAM_APPLICATION_ID,
271 B.PROGRAM_ID,
272 B.PROGRAM_UPDATE_DATE,
273 B.DISCOUNT_LINES_FLAG,
274 T.NAME,
275 T.DESCRIPTION,
276 T.VERSION_NO,
277 B.COMMENTS,
278 B.GSA_INDICATOR,
279 B.PRORATE_FLAG,
280 B.SOURCE_SYSTEM_CODE,
281 B.ASK_FOR_FLAG,
282 B.PARENT_LIST_HEADER_ID,
283 B.START_DATE_ACTIVE_FIRST,
284 B.END_DATE_ACTIVE_FIRST,
285 B.ACTIVE_DATE_FIRST_TYPE,
286 B.START_DATE_ACTIVE_SECOND,
287 B.END_DATE_ACTIVE_SECOND,
288 B.ACTIVE_DATE_SECOND_TYPE,
289 B.ACTIVE_FLAG,
290 B.MOBILE_DOWNLOAD,
291 B.CURRENCY_HEADER_ID,
292 B.PTE_CODE,
293 B.LIST_SOURCE_CODE,
294 B.ORIG_SYSTEM_HEADER_REF,
295 B.GLOBAL_FLAG,
296 B.ORIG_ORG_ID,
297 ''Y'' as VIEW_FLAG,
298 ''Y'' as UPDATE_FLAG,
299 B.SHAREABLE_FLAG,
300 B.SOLD_TO_ORG_ID,
301 B.LIMIT_EXISTS_FLAG,
302 B.LOCKED_FROM_LIST_HEADER_ID
303 FROM QP_LIST_HEADERS_ALL_B B, QP_LIST_HEADERS_TL T
304 where B.LIST_HEADER_ID = T.LIST_HEADER_ID
305 and T.LANGUAGE = userenv(''LANG'')';
306
307 execute immediate l_stmt;
308 commit;
309
310 exception
311 when others then
312 err_buff := sqlerrm;
313 retcode := 2;
314 end;
315
316 elsif (p_security_control = g_security_on and (l_security_control_prof = g_security_off or p_control = g_y)) then
317 begin
318 l_save := fnd_profile.save(x_name => g_security_control_prof,
319 x_value => g_security_on,
320 x_level_name => 'SITE');
321
322 l_stmt :=
323 'create or replace view QP_ARCH_SECU_LIST_HDRS_V as
324 SELECT /*+ leading(update_v) */ AB.ROWID ROW_ID,
325 AB.CONTEXT,
326 AB.ATTRIBUTE1,
327 AB.ATTRIBUTE2,
328 AB.ATTRIBUTE3,
329 AB.ATTRIBUTE4,
330 AB.ATTRIBUTE5,
331 AB.ATTRIBUTE6,
332 AB.ATTRIBUTE7,
333 AB.ATTRIBUTE8,
334 AB.ATTRIBUTE9,
335 AB.ATTRIBUTE10,
336 AB.ATTRIBUTE11,
337 AB.ATTRIBUTE12,
338 AB.ATTRIBUTE13,
339 AB.ATTRIBUTE14,
340 AB.ATTRIBUTE15,
341 AB.CURRENCY_CODE,
342 AB.SHIP_METHOD_CODE,
343 AB.FREIGHT_TERMS_CODE,
344 AB.LIST_HEADER_ID,
345 AB.CREATION_DATE,
346 AB.START_DATE_ACTIVE,
347 AB.END_DATE_ACTIVE,
348 AB.AUTOMATIC_FLAG,
349 AB.LIST_TYPE_CODE,
350 AB.TERMS_ID,
351 AB.ROUNDING_FACTOR,
352 AB.REQUEST_ID,
353 AB.CREATED_BY,
354 AB.LAST_UPDATE_DATE,
355 AB.LAST_UPDATED_BY,
356 AB.LAST_UPDATE_LOGIN,
357 AB.PROGRAM_APPLICATION_ID,
358 AB.PROGRAM_ID,
359 AB.PROGRAM_UPDATE_DATE,
360 AB.DISCOUNT_LINES_FLAG,
361 AT.NAME,
362 AT.DESCRIPTION,
363 AB.COMMENTS,
364 AB.GSA_INDICATOR,
365 AB.PRORATE_FLAG,
366 AB.SOURCE_SYSTEM_CODE,
367 AT.VERSION_NO,
368 AB.ACTIVE_FLAG,
369 AB.MOBILE_DOWNLOAD,
370 AB.CURRENCY_HEADER_ID,
371 AB.PTE_CODE,
372 AB.LIST_SOURCE_CODE,
373 AB.ORIG_SYSTEM_HEADER_REF,
374 AB.GLOBAL_FLAG,
375 AB.ORIG_ORG_ID,
376 ''Y'' as VIEW_FLAG,
377 ''Y'' as UPDATE_FLAG,
378 AB.SHAREABLE_FLAG,
379 AB.SOLD_TO_ORG_ID,
380 AB.LIMIT_EXISTS_FLAG,
381 AB.ARCH_PURG_REQUEST_ID
382 FROM QP_ARCH_LIST_HEADERS_TL AT, QP_ARCH_LIST_HEADERS_B AB,
383 (select distinct instance_pk1_value from TABLE(CAST(qp_security.auth_instances(''QP_SECU_UPDATE'') as "SYSTEM".qp_inst_pk_vals))) UPDATE_V
384 WHERE AB.LIST_HEADER_ID = AT.LIST_HEADER_ID
385 and AB.ARCH_PURG_REQUEST_ID = AT.ARCH_PURG_REQUEST_ID
386 AND AT.LANGUAGE = userenv(''LANG'')
387 AND AB.LIST_TYPE_CODE = ''PRL''
388 AND AB.LIST_HEADER_ID = UPDATE_V.INSTANCE_PK1_VALUE
389 UNION ALL
393 AB.ATTRIBUTE2,
390 SELECT /*+ leading(view_v) */ AB.ROWID,
391 AB.CONTEXT,
392 AB.ATTRIBUTE1,
394 AB.ATTRIBUTE3,
395 AB.ATTRIBUTE4,
396 AB.ATTRIBUTE5,
397 AB.ATTRIBUTE6,
398 AB.ATTRIBUTE7,
399 AB.ATTRIBUTE8,
400 AB.ATTRIBUTE9,
401 AB.ATTRIBUTE10,
402 AB.ATTRIBUTE11,
403 AB.ATTRIBUTE12,
404 AB.ATTRIBUTE13,
405 AB.ATTRIBUTE14,
406 AB.ATTRIBUTE15,
407 AB.CURRENCY_CODE,
408 AB.SHIP_METHOD_CODE,
409 AB.FREIGHT_TERMS_CODE,
410 AB.LIST_HEADER_ID,
411 AB.CREATION_DATE,
412 AB.START_DATE_ACTIVE,
413 AB.END_DATE_ACTIVE,
414 AB.AUTOMATIC_FLAG,
415 AB.LIST_TYPE_CODE,
416 AB.TERMS_ID,
417 AB.ROUNDING_FACTOR,
418 AB.REQUEST_ID,
419 AB.CREATED_BY,
420 AB.LAST_UPDATE_DATE,
421 AB.LAST_UPDATED_BY,
422 AB.LAST_UPDATE_LOGIN,
423 AB.PROGRAM_APPLICATION_ID,
424 AB.PROGRAM_ID,
425 AB.PROGRAM_UPDATE_DATE,
426 AB.DISCOUNT_LINES_FLAG,
427 AT.NAME,
428 AT.DESCRIPTION,
429 AB.COMMENTS,
430 AB.GSA_INDICATOR,
431 AB.PRORATE_FLAG,
432 AB.SOURCE_SYSTEM_CODE,
433 AT.VERSION_NO,
434 AB.ACTIVE_FLAG,
435 AB.MOBILE_DOWNLOAD,
436 AB.CURRENCY_HEADER_ID,
437 AB.PTE_CODE,
438 AB.LIST_SOURCE_CODE,
439 AB.ORIG_SYSTEM_HEADER_REF,
440 AB.GLOBAL_FLAG,
441 AB.ORIG_ORG_ID,
442 ''Y'' as VIEW_FLAG,
443 ''N'' as UPDATE_FLAG,
444 AB.SHAREABLE_FLAG,
445 AB.SOLD_TO_ORG_ID,
446 AB.LIMIT_EXISTS_FLAG,
447 AB.ARCH_PURG_REQUEST_ID
448 FROM QP_ARCH_LIST_HEADERS_TL AT, QP_ARCH_LIST_HEADERS_B AB,
449 (select distinct instance_pk1_value from TABLE(CAST(qp_security.auth_instances(''QP_SECU_VIEW'') as "SYSTEM".qp_inst_pk_vals)) ) VIEW_V,
450 (select distinct instance_pk1_value from TABLE(CAST(qp_security.auth_instances(''QP_SECU_UPDATE'') as "SYSTEM".qp_inst_pk_vals))) UPDATE_V
451 WHERE AB.LIST_HEADER_ID = AT.LIST_HEADER_ID
452 and AB.ARCH_PURG_REQUEST_ID = AT.ARCH_PURG_REQUEST_ID
453 AND AT.LANGUAGE = userenv(''LANG'')
454 AND AB.LIST_TYPE_CODE = ''PRL''
455 AND AB.LIST_HEADER_ID = VIEW_V.INSTANCE_PK1_VALUE
456 and AB.LIST_HEADER_ID = update_v.instance_pk1_value (+)
457 and update_v.instance_pk1_value IS NULL
458 UNION ALL
459 SELECT /*+ leading(view_v) */ AB.ROWID,
460 AB.CONTEXT,
461 AB.ATTRIBUTE1,
462 AB.ATTRIBUTE2,
463 AB.ATTRIBUTE3,
464 AB.ATTRIBUTE4,
465 AB.ATTRIBUTE5,
466 AB.ATTRIBUTE6,
467 AB.ATTRIBUTE7,
468 AB.ATTRIBUTE8,
469 AB.ATTRIBUTE9,
470 AB.ATTRIBUTE10,
471 AB.ATTRIBUTE11,
472 AB.ATTRIBUTE12,
473 AB.ATTRIBUTE13,
474 AB.ATTRIBUTE14,
475 AB.ATTRIBUTE15,
476 AB.CURRENCY_CODE,
477 AB.SHIP_METHOD_CODE,
478 AB.FREIGHT_TERMS_CODE,
479 AB.LIST_HEADER_ID,
480 AB.CREATION_DATE,
481 AB.START_DATE_ACTIVE,
482 AB.END_DATE_ACTIVE,
483 AB.AUTOMATIC_FLAG,
484 AB.LIST_TYPE_CODE,
485 AB.TERMS_ID,
486 AB.ROUNDING_FACTOR,
487 AB.REQUEST_ID,
488 AB.CREATED_BY,
489 AB.LAST_UPDATE_DATE,
490 AB.LAST_UPDATED_BY,
491 AB.LAST_UPDATE_LOGIN,
492 AB.PROGRAM_APPLICATION_ID,
493 AB.PROGRAM_ID,
494 AB.PROGRAM_UPDATE_DATE,
495 AB.DISCOUNT_LINES_FLAG,
496 AT.NAME,
497 AT.DESCRIPTION,
498 AB.COMMENTS,
499 AB.GSA_INDICATOR,
500 AB.PRORATE_FLAG,
501 AB.SOURCE_SYSTEM_CODE,
502 AT.VERSION_NO,
503 AB.ACTIVE_FLAG,
504 AB.MOBILE_DOWNLOAD,
505 AB.CURRENCY_HEADER_ID,
506 AB.PTE_CODE,
507 AB.LIST_SOURCE_CODE,
508 AB.ORIG_SYSTEM_HEADER_REF,
509 AB.GLOBAL_FLAG,
510 AB.ORIG_ORG_ID,
511 ''N'' as VIEW_FLAG,
512 ''N'' as UPDATE_FLAG,
513 AB.SHAREABLE_FLAG,
514 AB.SOLD_TO_ORG_ID,
515 AB.LIMIT_EXISTS_FLAG,
516 AB.ARCH_PURG_REQUEST_ID
517 FROM QP_ARCH_LIST_HEADERS_TL AT, QP_ARCH_LIST_HEADERS_B AB,
518 (select distinct instance_pk1_value from TABLE(CAST(qp_security.auth_instances(''QP_SECU_VIEW'') as "SYSTEM".qp_inst_pk_vals)) ) VIEW_V
519 WHERE AB.LIST_HEADER_ID = AT.LIST_HEADER_ID
520 and AB.ARCH_PURG_REQUEST_ID = AT.ARCH_PURG_REQUEST_ID
521 AND AT.LANGUAGE = userenv(''LANG'')
522 AND AB.LIST_TYPE_CODE = ''PRL''
523 AND AB.LIST_HEADER_ID = VIEW_V.instance_pk1_value(+)
524 and VIEW_V.instance_pk1_value IS NULL';
525
526 execute immediate l_stmt;
527
528 l_stmt :=
529 'create or replace view QP_ARCH_SECU_LIST_HDRS_VL as
530 SELECT /*+ leading(update_v) */ AB.ROWID ROW_ID,
531 AB.CONTEXT,
532 AB.ATTRIBUTE1,
533 AB.ATTRIBUTE2,
534 AB.ATTRIBUTE3,
535 AB.ATTRIBUTE4,
536 AB.ATTRIBUTE5,
537 AB.ATTRIBUTE6,
538 AB.ATTRIBUTE7,
539 AB.ATTRIBUTE8,
540 AB.ATTRIBUTE9,
541 AB.ATTRIBUTE10,
542 AB.ATTRIBUTE11,
543 AB.ATTRIBUTE12,
544 AB.ATTRIBUTE13,
545 AB.ATTRIBUTE14,
546 AB.ATTRIBUTE15,
547 AB.CURRENCY_CODE,
551 AB.CREATION_DATE,
548 AB.SHIP_METHOD_CODE,
549 AB.FREIGHT_TERMS_CODE,
550 AB.LIST_HEADER_ID,
552 AB.START_DATE_ACTIVE,
553 AB.END_DATE_ACTIVE,
554 AB.AUTOMATIC_FLAG,
555 AB.LIST_TYPE_CODE,
556 AB.TERMS_ID,
557 AB.ROUNDING_FACTOR,
558 AB.REQUEST_ID,
559 AB.CREATED_BY,
560 AB.LAST_UPDATE_DATE,
561 AB.LAST_UPDATED_BY,
562 AB.LAST_UPDATE_LOGIN,
563 AB.PROGRAM_APPLICATION_ID,
564 AB.PROGRAM_ID,
565 AB.PROGRAM_UPDATE_DATE,
566 AB.DISCOUNT_LINES_FLAG,
567 AT.NAME,
568 AT.DESCRIPTION,
569 AT.VERSION_NO,
570 AB.COMMENTS,
571 AB.GSA_INDICATOR,
572 AB.PRORATE_FLAG,
573 AB.SOURCE_SYSTEM_CODE,
574 AB.ASK_FOR_FLAG,
575 AB.PARENT_LIST_HEADER_ID,
576 AB.START_DATE_ACTIVE_FIRST,
577 AB.END_DATE_ACTIVE_FIRST,
578 AB.ACTIVE_DATE_FIRST_TYPE,
579 AB.START_DATE_ACTIVE_SECOND,
580 AB.END_DATE_ACTIVE_SECOND,
581 AB.ACTIVE_DATE_SECOND_TYPE,
582 AB.ACTIVE_FLAG,
583 AB.MOBILE_DOWNLOAD,
584 AB.CURRENCY_HEADER_ID,
585 AB.PTE_CODE,
586 AB.LIST_SOURCE_CODE,
587 AB.ORIG_SYSTEM_HEADER_REF,
588 AB.GLOBAL_FLAG,
589 AB.ORIG_ORG_ID,
590 ''Y'' as VIEW_FLAG,
591 ''Y'' as UPDATE_FLAG,
592 AB.SHAREABLE_FLAG,
593 AB.SOLD_TO_ORG_ID,
594 AB.LIMIT_EXISTS_FLAG,
595 AB.ARCH_PURG_REQUEST_ID
596 FROM QP_ARCH_LIST_HEADERS_TL AT, QP_ARCH_LIST_HEADERS_B AB,
597 (select distinct instance_pk1_value from TABLE(CAST(qp_security.auth_instances(''QP_SECU_UPDATE'') as "SYSTEM". qp_inst_pk_vals))) UPDATE_V
598 WHERE AB.LIST_HEADER_ID = AT.LIST_HEADER_ID
599 and AB.ARCH_PURG_REQUEST_ID = AT.ARCH_PURG_REQUEST_ID
600 AND AT.LANGUAGE = userenv(''LANG'')
601 and AB.LIST_HEADER_ID = UPDATE_V.INSTANCE_PK1_VALUE
602 UNION ALL
603 SELECT /*+ leading(view_v) */ AB.ROWID ROW_ID,
604 AB.CONTEXT,
605 AB.ATTRIBUTE1,
606 AB.ATTRIBUTE2,
607 AB.ATTRIBUTE3,
608 AB.ATTRIBUTE4,
609 AB.ATTRIBUTE5,
610 AB.ATTRIBUTE6,
611 AB.ATTRIBUTE7,
612 AB.ATTRIBUTE8,
613 AB.ATTRIBUTE9,
614 AB.ATTRIBUTE10,
615 AB.ATTRIBUTE11,
616 AB.ATTRIBUTE12,
617 AB.ATTRIBUTE13,
618 AB.ATTRIBUTE14,
619 AB.ATTRIBUTE15,
620 AB.CURRENCY_CODE,
621 AB.SHIP_METHOD_CODE,
622 AB.FREIGHT_TERMS_CODE,
623 AB.LIST_HEADER_ID,
624 AB.CREATION_DATE,
625 AB.START_DATE_ACTIVE,
626 AB.END_DATE_ACTIVE,
627 AB.AUTOMATIC_FLAG,
628 AB.LIST_TYPE_CODE,
629 AB.TERMS_ID,
630 AB.ROUNDING_FACTOR,
631 AB.REQUEST_ID,
632 AB.CREATED_BY,
633 AB.LAST_UPDATE_DATE,
634 AB.LAST_UPDATED_BY,
635 AB.LAST_UPDATE_LOGIN,
636 AB.PROGRAM_APPLICATION_ID,
637 AB.PROGRAM_ID,
638 AB.PROGRAM_UPDATE_DATE,
639 AB.DISCOUNT_LINES_FLAG,
640 AT.NAME,
641 AT.DESCRIPTION,
642 AT.VERSION_NO,
643 AB.COMMENTS,
644 AB.GSA_INDICATOR,
645 AB.PRORATE_FLAG,
646 AB.SOURCE_SYSTEM_CODE,
647 AB.ASK_FOR_FLAG,
648 AB.PARENT_LIST_HEADER_ID,
649 AB.START_DATE_ACTIVE_FIRST,
650 AB.END_DATE_ACTIVE_FIRST,
651 AB.ACTIVE_DATE_FIRST_TYPE,
652 AB.START_DATE_ACTIVE_SECOND,
653 AB.END_DATE_ACTIVE_SECOND,
654 AB.ACTIVE_DATE_SECOND_TYPE,
655 AB.ACTIVE_FLAG,
656 AB.MOBILE_DOWNLOAD,
657 AB.CURRENCY_HEADER_ID,
658 AB.PTE_CODE,
659 AB.LIST_SOURCE_CODE,
660 AB.ORIG_SYSTEM_HEADER_REF,
661 AB.GLOBAL_FLAG,
662 AB.ORIG_ORG_ID,
663 ''Y'' as VIEW_FLAG,
664 ''N'' as UPDATE_FLAG,
665 AB.SHAREABLE_FLAG,
666 AB.SOLD_TO_ORG_ID,
667 AB.LIMIT_EXISTS_FLAG,
668 AB.ARCH_PURG_REQUEST_ID
669 FROM QP_ARCH_LIST_HEADERS_TL AT, QP_ARCH_LIST_HEADERS_B AB,
670 (select distinct instance_pk1_value from TABLE(CAST(qp_security.auth_instances(''QP_SECU_VIEW'') as "SYSTEM".qp_inst_pk_vals)) ) VIEW_V,
671 (select distinct instance_pk1_value from TABLE(CAST(qp_security.auth_instances(''QP_SECU_UPDATE'') as "SYSTEM".qp_inst_pk_vals))) UPDATE_V
672 WHERE AB.LIST_HEADER_ID = AT.LIST_HEADER_ID
673 and AB.ARCH_PURG_REQUEST_ID = AT.ARCH_PURG_REQUEST_ID
674 AND AT.LANGUAGE = userenv(''LANG'')
675 and AB.LIST_HEADER_ID = VIEW_V.INSTANCE_PK1_VALUE
676 and AB.LIST_HEADER_ID = update_v.instance_pk1_value (+)
677 and update_v.instance_pk1_value IS NULL
678 UNION ALL
679 SELECT /*+ leading(view_v) */ AB.ROWID ROW_ID,
680 AB.CONTEXT,
681 AB.ATTRIBUTE1,
682 AB.ATTRIBUTE2,
683 AB.ATTRIBUTE3,
684 AB.ATTRIBUTE4,
685 AB.ATTRIBUTE5,
686 AB.ATTRIBUTE6,
687 AB.ATTRIBUTE7,
688 AB.ATTRIBUTE8,
689 AB.ATTRIBUTE9,
690 AB.ATTRIBUTE10,
691 AB.ATTRIBUTE11,
692 AB.ATTRIBUTE12,
693 AB.ATTRIBUTE13,
694 AB.ATTRIBUTE14,
695 AB.ATTRIBUTE15,
696 AB.CURRENCY_CODE,
697 AB.SHIP_METHOD_CODE,
698 AB.FREIGHT_TERMS_CODE,
699 AB.LIST_HEADER_ID,
700 AB.CREATION_DATE,
701 AB.START_DATE_ACTIVE,
702 AB.END_DATE_ACTIVE,
706 AB.ROUNDING_FACTOR,
703 AB.AUTOMATIC_FLAG,
704 AB.LIST_TYPE_CODE,
705 AB.TERMS_ID,
707 AB.REQUEST_ID,
708 AB.CREATED_BY,
709 AB.LAST_UPDATE_DATE,
710 AB.LAST_UPDATED_BY,
711 AB.LAST_UPDATE_LOGIN,
712 AB.PROGRAM_APPLICATION_ID,
713 AB.PROGRAM_ID,
714 AB.PROGRAM_UPDATE_DATE,
715 AB.DISCOUNT_LINES_FLAG,
716 AT.NAME,
717 AT.DESCRIPTION,
718 AT.VERSION_NO,
719 AB.COMMENTS,
720 AB.GSA_INDICATOR,
721 AB.PRORATE_FLAG,
722 AB.SOURCE_SYSTEM_CODE,
723 AB.ASK_FOR_FLAG,
724 AB.PARENT_LIST_HEADER_ID,
725 AB.START_DATE_ACTIVE_FIRST,
726 AB.END_DATE_ACTIVE_FIRST,
727 AB.ACTIVE_DATE_FIRST_TYPE,
728 AB.START_DATE_ACTIVE_SECOND,
729 AB.END_DATE_ACTIVE_SECOND,
730 AB.ACTIVE_DATE_SECOND_TYPE,
731 AB.ACTIVE_FLAG,
732 AB.MOBILE_DOWNLOAD,
733 AB.CURRENCY_HEADER_ID,
734 AB.PTE_CODE,
735 AB.LIST_SOURCE_CODE,
736 AB.ORIG_SYSTEM_HEADER_REF,
737 AB.GLOBAL_FLAG,
738 AB.ORIG_ORG_ID,
739 ''N'' as VIEW_FLAG,
740 ''N'' as UPDATE_FLAG,
741 AB.SHAREABLE_FLAG,
742 AB.SOLD_TO_ORG_ID,
743 AB.LIMIT_EXISTS_FLAG,
744 AB.ARCH_PURG_REQUEST_ID
745 FROM QP_ARCH_LIST_HEADERS_TL AT, QP_ARCH_LIST_HEADERS_B AB,
746 (select distinct instance_pk1_value from TABLE(CAST(qp_security.auth_instances(''QP_SECU_VIEW'') as "SYSTEM".qp_inst_pk_vals)) ) VIEW_V
747 WHERE AB.LIST_HEADER_ID = AT.LIST_HEADER_ID
748 and AB.ARCH_PURG_REQUEST_ID = AT.ARCH_PURG_REQUEST_ID
749 AND AT.LANGUAGE = userenv(''LANG'')
750 and AB.LIST_HEADER_ID = VIEW_V.instance_pk1_value(+)
751 and VIEW_V.instance_pk1_value IS NULL';
752
753 execute immediate l_stmt;
754
755 l_stmt :=
756 'create or replace view QP_SECU_LIST_HEADERS_V as
757 SELECT B.ROWID ROW_ID, B.CONTEXT, B.ATTRIBUTE1
758 , B.ATTRIBUTE2, B.ATTRIBUTE3, B.ATTRIBUTE4, B.ATTRIBUTE5, B.ATTRIBUTE6
759 , B.ATTRIBUTE7, B.ATTRIBUTE8, B.ATTRIBUTE9, B.ATTRIBUTE10, B.ATTRIBUTE11
760 , B.ATTRIBUTE12, B.ATTRIBUTE13, B.ATTRIBUTE14, B.ATTRIBUTE15, B.CURRENCY_CODE
761 , B.SHIP_METHOD_CODE, B.FREIGHT_TERMS_CODE, B.LIST_HEADER_ID, B.CREATION_DATE
762 , B.START_DATE_ACTIVE, B.END_DATE_ACTIVE, B.AUTOMATIC_FLAG, B.LIST_TYPE_CODE
763 , B.TERMS_ID, B.ROUNDING_FACTOR, B.REQUEST_ID, B.CREATED_BY, B.LAST_UPDATE_DATE
764 , B.LAST_UPDATED_BY, B.LAST_UPDATE_LOGIN, B.PROGRAM_APPLICATION_ID, B.PROGRAM_ID
765 , B.PROGRAM_UPDATE_DATE, B.DISCOUNT_LINES_FLAG, T.NAME, T.DESCRIPTION
766 , B.COMMENTS, B.GSA_INDICATOR, B.PRORATE_FLAG, B.SOURCE_SYSTEM_CODE, T.VERSION_NO
767 , B.ACTIVE_FLAG, B.MOBILE_DOWNLOAD, B.CURRENCY_HEADER_ID, B.PTE_CODE
768 , B.LIST_SOURCE_CODE, B.ORIG_SYSTEM_HEADER_REF, B.GLOBAL_FLAG, B.ORIG_ORG_ID
769 , ''Y'' as VIEW_FLAG, QP_SECURITY.GET_UPDATE_ALLOWED (''QP_LIST_HEADERS'', B.list_header_id) as UPDATE_FLAG
770 , B.SHAREABLE_FLAG, B.SOLD_TO_ORG_ID, B.LIMIT_EXISTS_FLAG, B.LOCKED_FROM_LIST_HEADER_ID
771 FROM (SELECT DISTINCT G.INSTANCE_ID FROM
772 QP_GRANTS G WHERE ( (G.GRANTEE_TYPE = ''USER'' AND G.GRANTEE_ID = QP_SECURITY.GET_USER_ID)
773 OR (G.GRANTEE_TYPE = ''RESP'' AND G.GRANTEE_ID = QP_SECURITY.GET_RESP_ID)
774 OR (G.GRANTEE_TYPE = ''OU'' AND ((MO_GLOBAL.get_access_mode = ''S'' and G.GRANTEE_ID = sys_context(''multi_org2'', ''current_org_id''))
775 or (MO_GLOBAL.get_access_mode =''A'') or (MO_GLOBAL.get_access_mode =''M'' and MO_GLOBAL.check_access(G.GRANTEE_ID) = ''Y'')))
776 OR (G.GRANTEE_TYPE = ''GLOBAL'' AND G.GRANTEE_ID = -1)) AND nvl (G.END_DATE, SYSDATE) >= SYSDATE
777 AND G.START_DATE <= SYSDATE AND ROWNUM > 0) E
778 , QP_LIST_HEADERS_ALL_B B, QP_LIST_HEADERS_TL T
779 WHERE E.INSTANCE_ID = B.LIST_HEADER_ID
780 AND B.LIST_HEADER_ID = T.LIST_HEADER_ID
781 AND T.LANGUAGE = userenv(''LANG'')
782 AND B.LIST_TYPE_CODE = ''PRL''
783 UNION ALL
784 SELECT B.ROWID, B.CONTEXT, B.ATTRIBUTE1, B.ATTRIBUTE2, B.ATTRIBUTE3, B.ATTRIBUTE4
785 , B.ATTRIBUTE5, B.ATTRIBUTE6, B.ATTRIBUTE7, B.ATTRIBUTE8, B.ATTRIBUTE9, B.ATTRIBUTE10
786 , B.ATTRIBUTE11, B.ATTRIBUTE12, B.ATTRIBUTE13, B.ATTRIBUTE14, B.ATTRIBUTE15, B.CURRENCY_CODE
787 , B.SHIP_METHOD_CODE, B.FREIGHT_TERMS_CODE, B.LIST_HEADER_ID, B.CREATION_DATE
788 , B.START_DATE_ACTIVE, B.END_DATE_ACTIVE, B.AUTOMATIC_FLAG, B.LIST_TYPE_CODE
789 , B.TERMS_ID, B.ROUNDING_FACTOR, B.REQUEST_ID, B.CREATED_BY, B.LAST_UPDATE_DATE
790 , B.LAST_UPDATED_BY, B.LAST_UPDATE_LOGIN, B.PROGRAM_APPLICATION_ID, B.PROGRAM_ID
791 , B.PROGRAM_UPDATE_DATE, B.DISCOUNT_LINES_FLAG, T.NAME, T.DESCRIPTION
792 , B.COMMENTS, B.GSA_INDICATOR, B.PRORATE_FLAG, B.SOURCE_SYSTEM_CODE, T.VERSION_NO
793 , B.ACTIVE_FLAG, B.MOBILE_DOWNLOAD, B.CURRENCY_HEADER_ID, B.PTE_CODE
794 , B.LIST_SOURCE_CODE, B.ORIG_SYSTEM_HEADER_REF, B.GLOBAL_FLAG, B.ORIG_ORG_ID
795 , ''Y'' as VIEW_FLAG, QP_SECURITY.GET_UPDATE_ALLOWED (''QP_LIST_HEADERS'', B.list_header_id) as UPDATE_FLAG
796 , B.SHAREABLE_FLAG, B.SOLD_TO_ORG_ID, B.LIMIT_EXISTS_FLAG, B.LOCKED_FROM_LIST_HEADER_ID
797 FROM QP_POLICY_LIST_HEADERS_V B, QP_LIST_HEADERS_TL T
798 WHERE B.list_header_id is not null
799 and NOT EXISTS ( SELECT DISTINCT
800 G.INSTANCE_ID FROM QP_GRANTS G
801 WHERE ((G.GRANTEE_TYPE = ''USER'' AND G.GRANTEE_ID = QP_SECURITY.GET_USER_ID)
802 OR (G.GRANTEE_TYPE = ''RESP'' AND G.GRANTEE_ID = QP_SECURITY.GET_RESP_ID)
803 OR (G.GRANTEE_TYPE = ''OU'' AND ((MO_GLOBAL.get_access_mode = ''S'' and G.GRANTEE_ID = sys_context(''multi_org2'', ''current_org_id''))
804 OR (MO_GLOBAL.get_access_mode =''A'') or (MO_GLOBAL.get_access_mode =''M'' and MO_GLOBAL.check_access(G.GRANTEE_ID) = ''Y'')))
808 AND ROWNUM > 0) AND B.LIST_HEADER_ID = T.LIST_HEADER_ID
805 OR (G.GRANTEE_TYPE = ''GLOBAL'' AND G.GRANTEE_ID = -1))
806 AND nvl (G.END_DATE, sysdate) >= SYSDATE AND G.START_DATE <= SYSDATE
807 AND b.list_header_id = g.instance_id
809 AND T.LANGUAGE = userenv(''LANG'')
810 AND B.LIST_TYPE_CODE = ''PRL''';
811
812 execute immediate l_stmt;
813
814 l_stmt :=
815 'create or replace view QP_SECU_LIST_HEADERS_VL as
816 SELECT B.ROWID ROW_ID, B.CONTEXT , B.ATTRIBUTE1, B.ATTRIBUTE2, B.ATTRIBUTE3, B.ATTRIBUTE4
817 , B.ATTRIBUTE5, B.ATTRIBUTE6, B.ATTRIBUTE7, B.ATTRIBUTE8, B.ATTRIBUTE9, B.ATTRIBUTE10
818 , B.ATTRIBUTE11, B.ATTRIBUTE12, B.ATTRIBUTE13, B.ATTRIBUTE14, B.ATTRIBUTE15
819 , B.CURRENCY_CODE, B.SHIP_METHOD_CODE, B.FREIGHT_TERMS_CODE, B.LIST_HEADER_ID
820 , B.CREATION_DATE, B.START_DATE_ACTIVE, B.END_DATE_ACTIVE, B.AUTOMATIC_FLAG
821 , B.LIST_TYPE_CODE, B.TERMS_ID, B.ROUNDING_FACTOR, B.REQUEST_ID, B.CREATED_BY
822 , B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.LAST_UPDATE_LOGIN
823 , B.PROGRAM_APPLICATION_ID, B.PROGRAM_ID, B.PROGRAM_UPDATE_DATE, B.DISCOUNT_LINES_FLAG
824 , T.NAME, T.DESCRIPTION, T.VERSION_NO, B.COMMENTS, B.GSA_INDICATOR, B.PRORATE_FLAG
825 , B.SOURCE_SYSTEM_CODE, B.ASK_FOR_FLAG, B.PARENT_LIST_HEADER_ID, B.START_DATE_ACTIVE_FIRST
826 , B.END_DATE_ACTIVE_FIRST, B.ACTIVE_DATE_FIRST_TYPE, B.START_DATE_ACTIVE_SECOND
827 , B.END_DATE_ACTIVE_SECOND, B.ACTIVE_DATE_SECOND_TYPE, B.ACTIVE_FLAG
828 , B.MOBILE_DOWNLOAD, B.CURRENCY_HEADER_ID, B.PTE_CODE
829 , B.LIST_SOURCE_CODE, B.ORIG_SYSTEM_HEADER_REF, B.GLOBAL_FLAG
830 , B.ORIG_ORG_ID, ''Y'' as VIEW_FLAG
831 , qp_security.GET_UPDATE_ALLOWED (''QP_LIST_HEADERS'', B.list_header_id) as UPDATE_FLAG
832 , B.SHAREABLE_FLAG, B.SOLD_TO_ORG_ID, B.LIMIT_EXISTS_FLAG
833 , B.LOCKED_FROM_LIST_HEADER_ID
834 FROM (SELECT DISTINCT G.INSTANCE_ID FROM QP_GRANTS G
835 WHERE ((G.GRANTEE_TYPE = ''USER'' AND G.GRANTEE_ID = qp_security.GET_USER_ID)
836 OR (G.GRANTEE_TYPE = ''RESP'' AND G.GRANTEE_ID = qp_security.GET_RESP_ID)
837 OR (G.GRANTEE_TYPE = ''OU'' AND ((MO_GLOBAL.get_access_mode = ''S'' and G.GRANTEE_ID = sys_context(''multi_org2'', ''current_org_id''))
838 or (MO_GLOBAL.get_access_mode =''A'') or (MO_GLOBAL.get_access_mode =''M'' and MO_GLOBAL.check_access(G.GRANTEE_ID) = ''Y'')))
839 OR (G.GRANTEE_TYPE = ''GLOBAL'' AND G.GRANTEE_ID = -1))
840 AND nvl (G.END_DATE, SYSDATE) >= SYSDATE AND G.START_DATE <= SYSDATE AND ROWNUM > 0) E
841 , QP_LIST_HEADERS_ALL_B B, QP_LIST_HEADERS_TL T
842 WHERE E.INSTANCE_ID = B.LIST_HEADER_ID AND B.LIST_HEADER_ID = T.LIST_HEADER_ID
843 AND T.LANGUAGE = userenv(''LANG'')
844 UNION ALL
845 SELECT B.ROWID ROW_ID, B.CONTEXT, B.ATTRIBUTE1, B.ATTRIBUTE2, B.ATTRIBUTE3
846 , B.ATTRIBUTE4, B.ATTRIBUTE5, B.ATTRIBUTE6, B.ATTRIBUTE7, B.ATTRIBUTE8
847 , B.ATTRIBUTE9, B.ATTRIBUTE10, B.ATTRIBUTE11, B.ATTRIBUTE12, B.ATTRIBUTE13
848 , B.ATTRIBUTE14, B.ATTRIBUTE15, B.CURRENCY_CODE, B.SHIP_METHOD_CODE, B.FREIGHT_TERMS_CODE
849 , B.LIST_HEADER_ID, B.CREATION_DATE, B.START_DATE_ACTIVE, B.END_DATE_ACTIVE
850 , B.AUTOMATIC_FLAG, B.LIST_TYPE_CODE, B.TERMS_ID, B.ROUNDING_FACTOR, B.REQUEST_ID
851 , B.CREATED_BY, B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.LAST_UPDATE_LOGIN
852 , B.PROGRAM_APPLICATION_ID, B.PROGRAM_ID, B.PROGRAM_UPDATE_DATE, B.DISCOUNT_LINES_FLAG
853 , T.NAME, T.DESCRIPTION, T.VERSION_NO, B.COMMENTS, B.GSA_INDICATOR, B.PRORATE_FLAG
854 , B.SOURCE_SYSTEM_CODE, B.ASK_FOR_FLAG, B.PARENT_LIST_HEADER_ID, B.START_DATE_ACTIVE_FIRST
855 , B.END_DATE_ACTIVE_FIRST, B.ACTIVE_DATE_FIRST_TYPE, B.START_DATE_ACTIVE_SECOND
856 , B.END_DATE_ACTIVE_SECOND, B.ACTIVE_DATE_SECOND_TYPE, B.ACTIVE_FLAG, B.MOBILE_DOWNLOAD
857 , B.CURRENCY_HEADER_ID, B.PTE_CODE, B.LIST_SOURCE_CODE, B.ORIG_SYSTEM_HEADER_REF
858 , B.GLOBAL_FLAG, B.ORIG_ORG_ID, ''Y'' as VIEW_FLAG
859 , qp_security.GET_UPDATE_ALLOWED (''QP_LIST_HEADERS'', B.list_header_id) as UPDATE_FLAG
860 , B.SHAREABLE_FLAG, B.SOLD_TO_ORG_ID, B.LIMIT_EXISTS_FLAG, B.LOCKED_FROM_LIST_HEADER_ID
861 FROM QP_POLICY_LIST_HEADERS_VL B, QP_LIST_HEADERS_TL T
862 WHERE B.list_header_id is not null
863 and NOT EXISTS ( SELECT G.INSTANCE_ID FROM QP_GRANTS G
864 WHERE ((G.GRANTEE_TYPE = ''USER'' AND G.GRANTEE_ID = qp_security.GET_USER_ID)
865 OR (G.GRANTEE_TYPE = ''RESP'' AND G.GRANTEE_ID = qp_security.GET_RESP_ID)
866 OR (G.GRANTEE_TYPE = ''OU'' AND ((MO_GLOBAL.get_access_mode = ''S'' and G.GRANTEE_ID = sys_context(''multi_org2'', ''current_org_id''))
867 or (MO_GLOBAL.get_access_mode =''A'') or (MO_GLOBAL.get_access_mode =''M'' and MO_GLOBAL.check_access(G.GRANTEE_ID) = ''Y'')))
868 OR (G.GRANTEE_TYPE = ''GLOBAL'' AND G.GRANTEE_ID = -1)) AND nvl (G.END_DATE,SYSDATE) >= SYSDATE
869 AND G.START_DATE <= SYSDATE AND B.LIST_HEADER_ID = G.INSTANCE_ID AND ROWNUM > 0) AND B.LIST_HEADER_ID = T.LIST_HEADER_ID
870 AND T.LANGUAGE = userenv(''LANG'')';
871
872 execute immediate l_stmt;
873 commit;
874
875 exception
876 when others then
877 err_buff := sqlerrm;
878 retcode := 2;
879 end;
880 end if;
881
882 end switch;
883
884 end qp_secu_ctrl_pvt;