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 ;