价格折扣问题
来源：互联网 发布：淘宝灰色赚钱项目 编辑：程序博客网 时间：2024/04/28 11:34
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 ;
- 价格折扣问题
- 实现判断商品折扣价格
- ecshop会员折扣价格计算函数
- 价格折扣的小数位数显示
- 关于价格的折扣的css
- 商品折扣问题
- zencart lightinthebox 根据不同数量显示折扣优惠价格
- ecshop不同的支付方式对应不同的价格折扣
- 折扣
- 买书折扣问题的贪心解法
- 用Switch语句判断消费折扣问题
- 商品折扣价 下划线不显示问题
- 价格精度问题
- 价格统计的问题
- 录入订单时出现错误信息“APP-ONT-250271：不允许联机折扣，价目表价格为零或没有价目表”
- js实现h5日期选择类似携程，日期上可以添加价格，是否休息，折扣等数据等等..
- 价值与价格的问题
- 现金折扣
- 确定弹出窗口是否被拦截
- java编程思想读书笔记： 二 万物皆对象
- 做选择真难
- 国务院公布明年元旦春节等节日放假安排
- 2004年度中国社会十大恶心人物排行榜[转帖]
- 价格折扣问题
- [ZT]Apusic应用服务器的性能调节_JVM优化
- 我们是工人老大哥。[转帖]
- 求APS可视化排程源码、程序设计流程图、开发文档、相关资料、程序、思路。请作过ERP的朋友顶力相助(那怕是排课软件方面的也可以参考)试 。
- Avalon介绍和概述(转载)
- 转载：面向方面开发
- Java开发中的线程安全选择与Swing[Z]
- 网络大典－－PPP 挑战握手认证协议(CHAP)
- Palm OS 编程简介
