价格折扣问题

function fran_price_qualifier
(
    p_customer_id varchar2,
    p_order_type_id varchar2,
    p_price_list_id varchar2
) return number as
    v_match boolean:=true;
    v_list_header_id number;
    v_list_line_id number;
    v_order_type_id varchar2(30);
    v_price_list_id varchar2(30);
    t_customer_name varchar2(200);
    t_customer_class varchar2(200);
    t_sales_channel varchar2(100);
    v_group_no_header number;
    v_group_no_line number;
    v_context varchar2(200);
    v_attribute varchar2(200);
    v_comparision varchar2(30);
    v_value varchar2(200);
    v_discount number:=0;
    discount number:=0;
    v_temp number:=0;
    v_arithmetic varchar2(20);
    v_count number;
    v_out number;
    cursor c_price is select qb.list_header_id from qp.qp_list_headers_b qb where qb.active_flag='Y' and qb.list_type_code='DLT'
        and (qb.start_date_active is null or qb.start_date_active <=sysdate) and (qb.end_date_active is null or qb.end_date_active >=sysdate);
    cursor c_group_no_header is select distinct qq.qualifier_grouping_no from qp.qp_qualifiers qq where qq.list_header_id=v_list_header_id
        and qq.list_line_id=-1 and qq.active_flag='Y' and (qq.start_date_active is null or qq.start_date_active<=sysdate)
        and (qq.end_date_active is null or qq.end_date_active>=sysdate);
    cursor c_group_header is select qq.qualifier_context,qq.qualifier_attribute,qq.qualifier_attr_value,qq.comparison_operator_code
        from qp.qp_qualifiers qq where qq.list_header_id=v_list_header_id and qq.list_line_id =-1 and (qq.start_date_active is null or qq.start_date_active <=sysdate)
        and (qq.end_date_active is null or qq.end_date_active>=sysdate) and qq.active_flag='Y' and qq.qualifier_grouping_no=v_group_no_header;
    cursor c_line_id is select ql.list_line_id,ql.operand,ql.arithmetic_operator from qp.qp_list_lines ql where ql.list_header_id=v_list_header_id 
        and (ql.start_date_active is null or ql.start_date_active <=sysdate)and (ql.end_date_active is null or ql.end_date_active >=sysdate)
        and ql.modifier_level_code='LINE' and ql.list_line_type_code='DIS';
    cursor c_group_no_line is select distinct qq.qualifier_grouping_no from qp.qp_qualifiers qq where qq.list_header_id=v_list_header_id 
        and qq.list_line_id=v_list_line_id and qq.active_flag='Y' and (qq.start_date_active is null or qq.start_date_active<=sysdate)
        and (qq.end_date_active is null or qq.end_date_active>=sysdate);
    cursor c_group_line is select qq.qualifier_context,qq.qualifier_attribute,qq.qualifier_attr_value,qq.comparison_operator_code
        from qp.qp_qualifiers qq where qq.list_header_id=v_list_header_id  and qq.list_line_id=v_list_line_id and qq.active_flag='Y'
        and (qq.start_date_active is null or qq.start_date_active<=sysdate) and (qq.end_date_active is null or qq.end_date_active>=sysdate)
        and qq.qualifier_grouping_no=v_group_no_line;
begin
    begin
        select p_order_type_id into v_order_type_id from dual;
        select p_price_list_id into v_price_list_id from dual;
       
        select to_char(t.customer_id),t.sales_channel_code,t.customer_class_code
        into t_customer_name,t_customer_class,t_sales_channel
        from apps.ra_customers t
        where t.status='A' and t.customer_id=to_number(p_customer_id);
    exception
        when others then
            discount:=0;
            return discount;
    end ;

    open c_price;
    loop
        fetch c_price into v_list_header_id;
        exit when c_price%notfound;
       
        select count(*) into v_count from qp.qp_list_lines ql where ql.list_header_id=v_list_header_id
        and (ql.start_date_active is null or ql.start_date_active <=sysdate)
        and (ql.end_date_active is null or ql.end_date_active >=sysdate)
        and ql.modifier_level_code='LINE';
       
        if v_count=0 then
            v_discount:=0;
        else
            select count(*) into v_count from qp.qp_qualifiers qq where qq.list_header_id=v_list_header_id
            and qq.list_line_id=-1 and qq.list_type_code='DLT' and qq.active_flag='Y'
            and (qq.start_date_active is null or qq.start_date_active <=sysdate)
            and (qq.end_date_active is null or qq.end_date_active >=sysdate);
/*=================================================================*/
            if v_count>0 then
                open c_group_no_header;
                loop
                    fetch c_group_no_header into v_group_no_header;
                    exit when c_group_no_header%notfound;
                    open c_group_header;
                    loop
                        fetch c_group_header into v_context,v_attribute,v_value,v_comparision;
                        exit when c_group_header%notfound;
                        if v_comparision='=' then
                            select count(*) into v_count from qp.qp_qualifiers qq
                            where
                                1=1
                                and qq.list_header_id=v_list_header_id
                                and qq.list_line_id =-1
                                and (qq.start_date_active is null or qq.start_date_active <=sysdate)
                                and (qq.end_date_active is null or qq.end_date_active>=sysdate)
                                and qq.active_flag='Y'
                                and qq.qualifier_grouping_no=v_group_no_header
                                and qq.qualifier_context=v_context
                                and qq.qualifier_attribute=v_attribute
                                and v_value=decode(qq.qualifier_context,
                                                                  'ORDER',decode(qq.qualifier_attribute,
                                                                                 'QUALIFIER_ATTRIBUTE9',v_order_type_id,
                                                                                 '-99'),
                                                                  'MODLIST',decode(qq.qualifier_attribute,
                                                                                   'QUALIFIER_ATTRIBUTE4',v_price_list_id,
                                                                                   '-99'),
                                                                  'CUSTOMER',decode(qq.qualifier_attribute,
                                                                                    'QUALIFIER_ATTRIBUTE2',t_customer_name,
                                                                                    'QUALIFIER_ATTRIBUTE1',t_customer_class,
                                                                                    'QUALIFIER_ATTRIBUTE13',t_sales_channel,
                                                                                    '-99'),
                                                                   '-99');
                        else
                            select count(*) into v_count from qp.qp_qualifiers qq
                            where
                                1=1
                                and qq.list_header_id=v_list_header_id
                                and qq.list_line_id =-1
                                and (qq.start_date_active is null or qq.start_date_active <=sysdate)
                                and (qq.end_date_active is null or qq.end_date_active>=sysdate)
                                and qq.active_flag='Y'
                                and qq.qualifier_grouping_no=v_group_no_header
                                and qq.comparison_operator_code='NOT ='
                                and qq.qualifier_context=v_context
                                and qq.qualifier_attribute=v_attribute
                                and v_value!=decode(qq.qualifier_context,
                                                                    'ORDER',decode(qq.qualifier_attribute,
                                                                                   'QUALIFIER_ATTRIBUTE9',v_order_type_id,
                                                                                   '-99'),
                                                                    'MODLIST',decode(qq.qualifier_attribute,
                                                                                     'QUALIFIER_ATTRIBUTE4',v_price_list_id,
                                                                                     '-99'),
                                                                    'CUSTOMER',decode(qq.qualifier_attribute,
                                                                                      'QUALIFIER_ATTRIBUTE2',t_customer_name,
                                                                                      'QUALIFIER_ATTRIBUTE1',t_customer_class,
                                                                                      'QUALIFIER_ATTRIBUTE13',t_sales_channel,
                                                                                      '-99'),
                                                                     '-99');         
                        end if;
                        if v_count=0 then
                            v_match :=false;
                            exit;
                        else
                            v_match:=true;
                        end if;
                    end loop;
                    close c_group_header;
                    if v_match=true then
                        exit;
                    end if;
                end loop;
                close c_group_no_header;
            else
                v_match:=true;
            end if;
           /*
           if v_match=true then
               return i;
           else
               return 91;
           end if;
           */
/*====================================================================*/
            if v_match=true then
                open c_line_id;
                loop
                    fetch c_line_id into v_list_line_id,v_temp,v_arithmetic;
                    exit when c_line_id%notfound;
                        select count(*) into v_count from qp.qp_qualifiers qq where 1=1 and qq.list_header_id=v_list_header_id and qq.list_line_id=v_list_line_id
                        and qq.list_type_code='DLT' and qq.active_flag='Y' and (qq.start_date_active is null or qq.start_date_active<=sysdate)
                        and (qq.end_date_active is null or qq.end_date_active >=sysdate);
                        if v_count=0 then
                            v_match:=true;
                        else
                            open c_group_no_line; 
                            loop
                                fetch c_group_no_line into v_group_no_line;
                                exit when c_group_no_line%notfound;
                                    open c_group_line;
                                    loop
                                        fetch c_group_line into v_context,v_attribute,v_value,v_comparision;
                                        exit when c_group_line%notfound;
                                        if v_comparision='=' then
                                            select count(*) into v_count from qp.qp_qualifiers qq where 1=1 and qq.list_header_id=v_list_header_id
                                            and qq.list_line_id =v_list_line_id and (qq.start_date_active is null or qq.start_date_active <=sysdate)
                                            and (qq.end_date_active is null or qq.end_date_active>=sysdate) and qq.active_flag='Y'
                                            and qq.qualifier_grouping_no=v_group_no_line and qq.qualifier_context=v_context
                                            and qq.qualifier_attribute=v_attribute
                                            and qq.qualifier_attr_value=decode(qq.qualifier_context,
                                                                                  'ORDER',decode(qq.qualifier_attribute,
                                                                                                 'QUALIFIER_ATTRIBUTE9',v_order_type_id,
                                                                                                 '-99'),
                                                                                  'MODLIST',decode(qq.qualifier_attribute,
                                                                                                   'QUALIFIER_ATTRIBUTE4',v_price_list_id,
                                                                                                   '-99'),
                                                                                  'CUSTOMER',decode(qq.qualifier_attribute,
                                                                                                    'QUALIFIER_ATTRIBUTE2',t_customer_name,
                                                                                                    'QUALIFIER_ATTRIBUTE1',t_customer_class,
                                                                                                    'QUALIFIER_ATTRIBUTE13',t_sales_channel,
                                                                                                    '-99'),
                                                                                   '-99');
               
                                        else
                                            select count(*) into v_count from qp.qp_qualifiers qq
                                            where 1=1
                                                and qq.list_header_id=v_list_header_id
                                                and qq.list_line_id =v_list_line_id
                                                and (qq.start_date_active is null or qq.start_date_active <=sysdate)
                                                and (qq.end_date_active is null or qq.end_date_active>=sysdate)
                                                and qq.active_flag='Y'
                                                and qq.qualifier_grouping_no=v_group_no_line
                                                and qq.qualifier_context=v_context
                                                and qq.qualifier_attribute=v_attribute
                                                and qq.qualifier_attr_value!=decode(qq.qualifier_context,
                                                                                  'ORDER',decode(qq.qualifier_attribute,
                                                                                                 'QUALIFIER_ATTRIBUTE9',v_order_type_id,
                                                                                                 '-99'),
                                                                                  'MODLIST',decode(qq.qualifier_attribute,
                                                                                                   'QUALIFIER_ATTRIBUTE4',v_price_list_id,
                                                                                                   '-99'),
                                                                                  'CUSTOMER',decode(qq.qualifier_attribute,
                                                                                                    'QUALIFIER_ATTRIBUTE2',t_customer_name,
                                                                                                    'QUALIFIER_ATTRIBUTE1',t_customer_class,
                                                                                                    'QUALIFIER_ATTRIBUTE13',t_sales_channel,
                                                                                                    '-99'),
                                                                                   '-99');
                                        end if;
                                        if v_count=0 then
                                            v_match :=false;
                                            exit;
                                        else
                                            v_match:=true;
                                        end if;
                                    end loop;
                                    close c_group_line;                                   
                                    if v_match=true then
                                        exit;
                                    end if;
                        end loop;
                        close c_group_no_line;
                    end if;
                   
                    if v_match=true then
                        v_out:=-3;
                        --exit;
                    else
                        v_out:=-4;
                        v_temp:=0;
                    end if;

                    v_discount:=v_discount+v_temp;

                end loop;
                close c_line_id;
            else
                v_discount:=0;
            end if;
        end if;
        discount:=discount+v_discount;
    end loop;
    close c_price;
    return discount;
    exception
        when others then
            discount:=0;
            return discount;  
end ;