CREATE OR REPLACE VIEW APPS.XX_PRICE_MV#
(LIST_HEADER_ID,NAME,DESCRIPTION,CURRENCY_CODE,PRICE_LIST_ID,CUST_ACCOUNT_ID,ORDER_TYPE_ID,QUALIFIER_CONTEXT1,QUALIFIER_PRECEDENCE1,QUALIFIER_CONTEXT2,QUALIFIER_PRECEDENCE2,QUALIFIER_CONTEXT3,QUALIFIER_PRECEDENCE3,SITE_USE_ID,CDB_BRANCH)
AS
SELECT h.list_header_id,
h.name,
h.description,
h.currency_code,
-1 price_list_id,
a.cust_account_id cust_account_id,
-1 order_type_id,
NULL qualifier_context1,
999 qualifier_precedence1,
NULL qualifier_context2,
999 qualifier_precedence2,
NULL qualifier_context3,
999 qualifier_precedence3,
null site_use_id,
null cdb_branch
FROM xx_secu_list_headers_vl h, hz_cust_accounts a
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q
WHERE q.list_header_id = h.list_header_id
AND q.list_line_id = -1)
UNION ALL
-- account --
SELECT h.list_header_id,
h.name,
h.description,
h.currency_code,
-1 price_list_id,
a.cust_account_id cust_account_id,
-1 order_type_id,
q.qualifier_context qualifier_context1,
q.qualifier_precedence qualifier_precedence1,
NULL qualifier_context2,
999 qualifier_precedence2,
NULL qualifier_context3,
999 qualifier_precedence3,
null site_use_id,
null cdb_branch
FROM xx_secu_list_headers_vl h, qp_qualifiers q, hz_cust_accounts a
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND q.list_header_id = h.list_header_id
AND q.qualifier_context = 'CUSTOMER'
AND ((q.qualifier_attribute = 'QUALIFIER_ATTRIBUTE2'
AND q.qualifier_attr_value = TO_CHAR (a.cust_account_id))
OR (q.qualifier_attribute = 'QUALIFIER_ATTRIBUTE13'
AND q.qualifier_attr_value = to_char(a.sales_channel_code)))
AND q.active_flag = 'Y'
AND q.comparison_operator_code = '='
AND q.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q.start_date_active, SYSDATE - 1)
AND NVL (q.end_date_active, SYSDATE + 1)
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q2
WHERE q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'ORDER'
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q2.start_date_active,
SYSDATE - 1)
AND NVL (
q2.end_date_active,
SYSDATE + 1))
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q2
WHERE q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'MODLIST'
AND q2.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q2.start_date_active,
SYSDATE - 1)
AND NVL (
q2.end_date_active,
SYSDATE + 1))
UNION ALL
-- account site--
SELECT h.list_header_id,
h.name,
h.description,
h.currency_code,
-1 price_list_id,
-1 cust_account_id,
-1 order_type_id,
q.qualifier_context qualifier_context1,
q.qualifier_precedence qualifier_precedence1,
NULL qualifier_context2,
999 qualifier_precedence2,
NULL qualifier_context3,
999 qualifier_precedence3,
q.qualifier_attr_value site_use_id,
null cdb_branch
FROM xx_secu_list_headers_vl h, qp_qualifiers q
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND q.list_header_id = h.list_header_id
AND q.qualifier_context = 'CUSTOMER'
AND q.qualifier_attribute in ('QUALIFIER_ATTRIBUTE14','QUALIFIER_ATTRIBUTE11')
AND q.active_flag = 'Y'
AND q.comparison_operator_code = '='
AND q.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q.start_date_active, SYSDATE - 1)
AND NVL (q.end_date_active, SYSDATE + 1)
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q2
WHERE q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'ORDER'
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q2.start_date_active,
SYSDATE - 1)
AND NVL (
q2.end_date_active,
SYSDATE + 1))
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q2
WHERE q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'MODLIST'
AND q2.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q2.start_date_active,
SYSDATE - 1)
AND NVL (
q2.end_date_active,
SYSDATE + 1))
UNION ALL
-- price list --
SELECT h.list_header_id,
h.name,
h.description,
h.currency_code,
TO_NUMBER (q.qualifier_attr_value) price_list_id,
-1 cust_account_id,
-1 order_type_id,
NULL qualifier_context1,
999 qualifier_precedence1,
q.qualifier_context qualifier_context2,
q.qualifier_precedence qualifier_precedence2,
NULL qualifier_context3,
999 qualifier_precedence3,
null site_use_id,
null cdb_branch
FROM xx_secu_list_headers_vl h, qp_qualifiers q
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND q.list_header_id = h.list_header_id
AND q.qualifier_context = 'MODLIST'
AND q.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND q.active_flag = 'Y'
AND q.comparison_operator_code = '='
AND q.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q.start_date_active, SYSDATE - 1)
AND NVL (q.end_date_active, SYSDATE + 1)
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q2
WHERE q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'CUSTOMER'
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q2.start_date_active,
SYSDATE - 1)
AND NVL (
q2.end_date_active,
SYSDATE + 1))
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q2
WHERE q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'ORDER'
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q2.start_date_active,
SYSDATE - 1)
AND NVL (
q2.end_date_active,
SYSDATE + 1))
UNION ALL
-- order type --
SELECT h.list_header_id,
h.name,
h.description,
h.currency_code,
-1 price_list_id,
-1 cust_account_id,
TO_NUMBER (q.qualifier_attr_value) order_type_id,
NULL qualifier_context1,
999 qualifier_precedence1,
NULL qualifier_context2,
999 qualifier_precedence2,
q.qualifier_context qualifier_context3,
q.qualifier_precedence qualifier_precedence3,
null site_use_id,
null cdb_branch
FROM xx_secu_list_headers_vl h, qp_qualifiers q
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND q.list_header_id = h.list_header_id
AND q.qualifier_context = 'ORDER'
AND q.qualifier_attribute = 'QUALIFIER_ATTRIBUTE9'
AND q.active_flag = 'Y'
AND q.comparison_operator_code = '='
AND q.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q.start_date_active, SYSDATE - 1)
AND NVL (q.end_date_active, SYSDATE + 1)
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q2
WHERE q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'CUSTOMER'
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q2.start_date_active,
SYSDATE - 1)
AND NVL (
q2.end_date_active,
SYSDATE + 1))
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q2
WHERE q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'MODLIST'
AND q2.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q2.start_date_active,
SYSDATE - 1)
AND NVL (
q2.end_date_active,
SYSDATE + 1))
UNION ALL
-- price list and account --
SELECT h.list_header_id,
h.name,
h.description,
h.currency_code,
TO_NUMBER (q1.qualifier_attr_value) price_list_id,
a.cust_account_id cust_account_id,
-1 order_type_id,
q2.qualifier_context qualifier_context1,
q2.qualifier_precedence qualifier_precedence1,
q1.qualifier_context qualifier_context2,
q1.qualifier_precedence qualifier_precedence2,
NULL qualifier_context3,
999 qualifier_precedence3,
null site_use_id,
null cdb_branch
FROM xx_secu_list_headers_vl h,
qp_qualifiers q1,
qp_qualifiers q2,
hz_cust_accounts a
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND q1.list_header_id = h.list_header_id
AND q1.qualifier_context = 'MODLIST'
AND q1.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND q1.active_flag = 'Y'
AND q1.comparison_operator_code = '='
AND q1.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q1.start_date_active, SYSDATE - 1)
AND NVL (q1.end_date_active, SYSDATE + 1)
AND q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'CUSTOMER'
AND ((q2.qualifier_attribute = 'QUALIFIER_ATTRIBUTE2'
AND q2.qualifier_attr_value = TO_CHAR (a.cust_account_id))
OR (q2.qualifier_attribute = 'QUALIFIER_ATTRIBUTE13'
AND q2.qualifier_attr_value = to_char(a.sales_channel_code)))
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q2.start_date_active, SYSDATE - 1)
AND NVL (q2.end_date_active, SYSDATE + 1)
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q3
WHERE q3.list_header_id = h.list_header_id
AND q3.qualifier_context = 'ORDER'
AND q3.qualifier_attribute = 'QUALIFIER_ATTRIBUTE9'
AND q3.active_flag = 'Y'
AND q3.comparison_operator_code = '='
AND q3.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q3.start_date_active,
SYSDATE - 1)
AND NVL (
q3.end_date_active,
SYSDATE + 1))
UNION ALL
-- price list and account site --
SELECT h.list_header_id,
h.name,
h.description,
h.currency_code,
TO_NUMBER (q1.qualifier_attr_value) price_list_id,
-1 cust_account_id,
-1 order_type_id,
q2.qualifier_context qualifier_context1,
q2.qualifier_precedence qualifier_precedence1,
q1.qualifier_context qualifier_context2,
q1.qualifier_precedence qualifier_precedence2,
NULL qualifier_context3,
999 qualifier_precedence3,
q2.qualifier_attr_value site_use_id,
null cdb_branch
FROM xx_secu_list_headers_vl h,
qp_qualifiers q1,
qp_qualifiers q2
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND q1.list_header_id = h.list_header_id
AND q1.qualifier_context = 'MODLIST'
AND q1.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND q1.active_flag = 'Y'
AND q1.comparison_operator_code = '='
AND q1.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q1.start_date_active, SYSDATE - 1)
AND NVL (q1.end_date_active, SYSDATE + 1)
AND q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'CUSTOMER'
AND q2.qualifier_attribute in ('QUALIFIER_ATTRIBUTE14','QUALIFIER_ATTRIBUTE11')
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q2.start_date_active, SYSDATE - 1)
AND NVL (q2.end_date_active, SYSDATE + 1)
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q3
WHERE q3.list_header_id = h.list_header_id
AND q3.qualifier_context = 'ORDER'
AND q3.qualifier_attribute = 'QUALIFIER_ATTRIBUTE9'
AND q3.active_flag = 'Y'
AND q3.comparison_operator_code = '='
AND q3.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q3.start_date_active,
SYSDATE - 1)
AND NVL (
q3.end_date_active,
SYSDATE + 1))
UNION ALL
-- account and order type --
SELECT h.list_header_id,
h.name,
h.description,
h.currency_code,
-1 price_list_id,
a.cust_account_id cust_account_id,
TO_NUMBER (q1.qualifier_attr_value) order_type_id,
q1.qualifier_context qualifier_context1,
q1.qualifier_precedence qualifier_precedence1,
NULL qualifier_context2,
999 qualifier_precedence2,
q2.qualifier_context qualifier_context3,
q2.qualifier_precedence qualifier_precedence3,
null site_use_id,
null cdb_branch
FROM xx_secu_list_headers_vl h,
qp_qualifiers q1,
qp_qualifiers q2,
hz_cust_accounts a
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND q1.list_header_id = h.list_header_id
AND q1.qualifier_context = 'ORDER'
AND q1.qualifier_attribute = 'QUALIFIER_ATTRIBUTE9'
AND q1.active_flag = 'Y'
AND q1.comparison_operator_code = '='
AND q1.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q1.start_date_active, SYSDATE - 1)
AND NVL (q1.end_date_active, SYSDATE + 1)
AND q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'CUSTOMER'
AND ((q2.qualifier_attribute = 'QUALIFIER_ATTRIBUTE2'
AND q2.qualifier_attr_value = TO_CHAR (a.cust_account_id))
OR (q2.qualifier_attribute = 'QUALIFIER_ATTRIBUTE13'
AND q2.qualifier_attr_value = to_char(a.sales_channel_code)))
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q2.start_date_active, SYSDATE - 1)
AND NVL (q2.end_date_active, SYSDATE + 1)
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q3
WHERE q3.list_header_id = h.list_header_id
AND q3.qualifier_context = 'MODLIST'
AND q3.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND q3.active_flag = 'Y'
AND q3.comparison_operator_code = '='
AND q3.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q3.start_date_active,
SYSDATE - 1)
AND NVL (
q3.end_date_active,
SYSDATE + 1))
UNION ALL
-- account site and order type --
SELECT h.list_header_id,
h.name,
h.description,
h.currency_code,
-1 price_list_id,
-1 cust_account_id,
TO_NUMBER (q1.qualifier_attr_value) order_type_id,
q1.qualifier_context qualifier_context1,
q1.qualifier_precedence qualifier_precedence1,
NULL qualifier_context2,
999 qualifier_precedence2,
q2.qualifier_context qualifier_context3,
q2.qualifier_precedence qualifier_precedence3,
q2.qualifier_attr_value site_use_id,
null cdb_branch
FROM xx_secu_list_headers_vl h,
qp_qualifiers q1,
qp_qualifiers q2
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND q1.list_header_id = h.list_header_id
AND q1.qualifier_context = 'ORDER'
AND q1.qualifier_attribute = 'QUALIFIER_ATTRIBUTE9'
AND q1.active_flag = 'Y'
AND q1.comparison_operator_code = '='
AND q1.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q1.start_date_active, SYSDATE - 1)
AND NVL (q1.end_date_active, SYSDATE + 1)
AND q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'CUSTOMER'
AND q2.qualifier_attribute in ('QUALIFIER_ATTRIBUTE14','QUALIFIER_ATTRIBUTE11')
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q2.start_date_active, SYSDATE - 1)
AND NVL (q2.end_date_active, SYSDATE + 1)
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q3
WHERE q3.list_header_id = h.list_header_id
AND q3.qualifier_context = 'MODLIST'
AND q3.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND q3.active_flag = 'Y'
AND q3.comparison_operator_code = '='
AND q3.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q3.start_date_active,
SYSDATE - 1)
AND NVL (
q3.end_date_active,
SYSDATE + 1))
UNION ALL
-- price list and order type --
SELECT h.list_header_id,
h.name,
h.description,
h.currency_code,
TO_NUMBER (q1.qualifier_attr_value) price_list_id,
a.cust_account_id cust_account_id,
TO_NUMBER (q2.qualifier_attr_value) order_type_id,
NULL qualifier_context1,
999 qualifier_precedence1,
q1.qualifier_context qualifier_context2,
q1.qualifier_precedence qualifier_precedence2,
q2.qualifier_context qualifier_context3,
q2.qualifier_precedence qualifier_precedence3,
null site_use_id,
null cdb_branch
FROM xx_secu_list_headers_vl h,
qp_qualifiers q1,
qp_qualifiers q2,
hz_cust_accounts a
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND q1.list_header_id = h.list_header_id
AND q1.qualifier_context = 'MODLIST'
AND q1.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND q1.active_flag = 'Y'
AND q1.comparison_operator_code = '='
AND q1.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q1.start_date_active, SYSDATE - 1)
AND NVL (q1.end_date_active, SYSDATE + 1)
AND q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'ORDER'
AND q2.qualifier_attribute = 'QUALIFIER_ATTRIBUTE9'
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q2.start_date_active, SYSDATE - 1)
AND NVL (q2.end_date_active, SYSDATE + 1)
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q3
WHERE q3.list_header_id = h.list_header_id
AND q3.qualifier_context = 'CUSTOMER'
AND q3.active_flag = 'Y'
AND q3.comparison_operator_code = '='
AND q3.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q3.start_date_active,
SYSDATE - 1)
AND NVL (
q3.end_date_active,
SYSDATE + 1))
UNION ALL
-- account, price list and order type --
-- This one below LT
SELECT /*+ opt_param('_hash_join_enabled','true') */ h.list_header_id,
h.name,
h.description,
h.currency_code,
TO_NUMBER (q1.qualifier_attr_value) price_list_id,
a.cust_account_id cust_account_id,
TO_NUMBER (q3.qualifier_attr_value) order_type_id,
q2.qualifier_context qualifier_context2,
q2.qualifier_precedence qualifier_precedence2,
q1.qualifier_context qualifier_context2,
q1.qualifier_precedence qualifier_precedence2,
q3.qualifier_context qualifier_context3,
q3.qualifier_precedence qualifier_precedence3,
null site_use_id,
null cdb_branch
FROM xx_secu_list_headers_vl h,
qp_qualifiers q1,
qp_qualifiers q2,
qp_qualifiers q3,
hz_cust_accounts a
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND q1.list_header_id = h.list_header_id
AND q1.qualifier_context = 'MODLIST'
AND q1.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND q1.active_flag = 'Y'
AND q1.comparison_operator_code = '='
AND q1.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q1.start_date_active, SYSDATE - 1)
AND NVL (q1.end_date_active, SYSDATE + 1)
AND q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'CUSTOMER'
AND ((q2.qualifier_attribute = 'QUALIFIER_ATTRIBUTE2'
AND q2.qualifier_attr_value = TO_CHAR (a.cust_account_id))
OR (q2.qualifier_attribute = 'QUALIFIER_ATTRIBUTE13'
AND q2.qualifier_attr_value = to_char(a.sales_channel_code)))
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q2.start_date_active, SYSDATE - 1)
AND NVL (q2.end_date_active, SYSDATE + 1)
AND q3.list_header_id = h.list_header_id
AND q3.qualifier_context = 'ORDER'
AND q3.qualifier_attribute = 'QUALIFIER_ATTRIBUTE9'
AND q3.active_flag = 'Y'
AND q3.comparison_operator_code = '='
AND q3.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q3.start_date_active, SYSDATE - 1)
AND NVL (q3.end_date_active, SYSDATE + 1)
UNION ALL
-- account site, price list and order type --
SELECT h.list_header_id,
h.name,
h.description,
h.currency_code,
TO_NUMBER (q1.qualifier_attr_value) price_list_id,
-1 cust_account_id,
TO_NUMBER (q3.qualifier_attr_value) order_type_id,
q2.qualifier_context qualifier_context2,
q2.qualifier_precedence qualifier_precedence2,
q1.qualifier_context qualifier_context2,
q1.qualifier_precedence qualifier_precedence2,
q3.qualifier_context qualifier_context3,
q3.qualifier_precedence qualifier_precedence3,
q2.qualifier_attr_value site_use_id,
null cdb_branch
FROM xx_secu_list_headers_vl h,
qp_qualifiers q1,
qp_qualifiers q2,
qp_qualifiers q3
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND q1.list_header_id = h.list_header_id
AND q1.qualifier_context = 'MODLIST'
AND q1.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND q1.active_flag = 'Y'
AND q1.comparison_operator_code = '='
AND q1.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q1.start_date_active, SYSDATE - 1)
AND NVL (q1.end_date_active, SYSDATE + 1)
AND q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'CUSTOMER'
AND q2.qualifier_attribute in ('QUALIFIER_ATTRIBUTE14','QUALIFIER_ATTRIBUTE11')
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q2.start_date_active, SYSDATE - 1)
AND NVL (q2.end_date_active, SYSDATE + 1)
AND q3.list_header_id = h.list_header_id
AND q3.qualifier_context = 'ORDER'
AND q3.qualifier_attribute = 'QUALIFIER_ATTRIBUTE9'
AND q3.active_flag = 'Y'
AND q3.comparison_operator_code = '='
AND q3.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q3.start_date_active, SYSDATE - 1)
AND NVL (q3.end_date_active, SYSDATE + 1)
UNION ALL
-- cdb branch
SELECT h.list_header_id,
h.name,
h.description,
h.currency_code,
-1 price_list_id,
-1 cust_account_id,
-1 order_type_id,
q.qualifier_context qualifier_context1,
q.qualifier_precedence qualifier_precedence1,
NULL qualifier_context2,
999 qualifier_precedence2,
NULL qualifier_context3,
999 qualifier_precedence3,
null site_use_id,
q.qualifier_attr_value cdb_branch
FROM xx_secu_list_headers_vl h, qp_qualifiers q
WHERE h.active_flag = 'Y'
AND h.list_type_code = 'DLT'
AND q.list_header_id = h.list_header_id
AND q.qualifier_context = 'CUSTOMER'
AND q.qualifier_attribute = 'QUALIFIER_ATTRIBUTE35'
AND q.active_flag = 'Y'
AND q.comparison_operator_code = '='
AND q.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (q.start_date_active, SYSDATE - 1)
AND NVL (q.end_date_active, SYSDATE + 1)
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q2
WHERE q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'ORDER'
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q2.start_date_active,
SYSDATE - 1)
AND NVL (
q2.end_date_active,
SYSDATE + 1))
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q2
WHERE q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'MODLIST'
AND q2.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q2.start_date_active,
SYSDATE - 1)
AND NVL (
q2.end_date_active,
SYSDATE + 1))
AND NOT EXISTS
(SELECT 1
FROM qp_qualifiers q2
WHERE q2.list_header_id = h.list_header_id
AND q2.qualifier_context = 'CUSTOMER'
AND q2.qualifier_attribute in ('QUALIFIER_ATTRIBUTE14','QUALIFIER_ATTRIBUTE11')
AND q2.active_flag = 'Y'
AND q2.comparison_operator_code = '='
AND q2.list_line_id = -1
AND TRUNC (SYSDATE) BETWEEN NVL (
q2.start_date_active,
SYSDATE - 1)
AND NVL (
q2.end_date_active,
SYSDATE + 1)) ;
;