SELECT a.name,a.description,a.currency_code,a.start_date_active,a.end_date_active,a.comments,
a.version_no,a.list_type_code,a.automatic_flag,a.active_flag,a.start_date_active_first,
a.end_date_active_first,a.active_date_first_type,a.active_date_second_type,
a.start_date_active_second,a.end_date_active_second,
a.list_header_id list_header_id,
a.ask_for_flag,a.gsa_indicator,a.parent_list_header_id,
QP_QPXPACRL_XMLP_PKG.cf_parent_numberformula(a.parent_list_header_id) CF_parent_number,
QP_QPXPACRL_XMLP_PKG.cf_parent_versionformula(a.parent_list_header_id) CF_parent_version,
QP_QPXPACRL_XMLP_PKG.cf_list_type_codeformula(a.list_type_code) CF_list_type_code
FROM qp_list_headers_vl a
WHERE (:P_modifier_name_from IS NULL OR
a.description >= :P_modifier_name_from) AND
(:P_modifier_name_to IS NULL OR
a.description <= :P_modifier_name_to) AND
(:P_start_date_active IS NULL OR
a.start_date_active = :P_start_date_active) AND
(:P_end_date_active IS NULL OR
a.end_date_active = :P_end_date_active) AND
( (:P_qualifier_context IS NULL
and :P_qualifier_attribute_from IS NULL
and :P_qualifier_attribute_to IS NULL)
OR
a.list_header_id IN (SELECT list_header_id
FROM qp_qualifiers_v b
WHERE (:P_qualifier_context IS NULL OR
b.qualifier_context = :P_qualifier_context) AND
(:P_qualifier_attribute_from IS NULL OR
b.qualifier_attr_value = :P_qualifier_attribute_from) AND
(:P_qualifier_attribute_to IS NULL OR
b.qualifier_attr_value_to = :P_qualifier_attribute_to)) )
AND
exists (select 'X' from qp_list_lines qpl where a.list_header_id = qpl.list_header_id and qpl.accrual_flag = 'Y')
ORDER BY 1 |
