17,377
社区成员
发帖
与我相关
我的任务
分享
procedure SALE(para_s_rq in varchar2 ,
para_e_rq in varchar2,
para_reminder_id in varchar2,
para_buyer_id in varchar2,
para_goods_id in varchar2,
para_supplier_id in varchar2,
para_saler_id in varchar2,
para_orgid in integer,
para_p_cursor out t_cursor) is
begin
insert into A
select * from B
where b.date between(para_s_rq and para_e_rq)
(case when para_supplier_id is null or t.policy_supp_id =para_supplier_id then 1 else 0 end)=1
and (case when para_reminder_id is null or t.policy_reminder_id =para_reminder_id then 1 else 0 end)=1
and (case when para_orgid is null or b.org_id =para_orgid then 1 else 0 end)=1
and (case when para_goods_id is null or b.goodsid=para_goods_id then 1 else 0 end)=1
and (case when para_buyer_id is null or b.SUPPLYERID=para_buyer_id then 1 else 0 end)=1
and (case when para_reminder_id is null or t.policy_reminder_id=para_reminder_id then 1 else 0 end)=1
and (case when para_saler_id is null or b.salerid=para_saler_id then 1 else 0 end)=1
end SALE_REBATE_SETTLE;
select *
from rebate_policy_sales_v t
inner join base_sale_erp b
on
(b.CREDATE between t.SECTION_BEGIN_DATE and t.SECTION_END_DATE)
and b.supplyid = t.policy_supp_id
and t.org_id=b.org_id
left join (select b.dictdata_name, b.dictdata_value from tb_dictionary a, tb_dictionary_data b where a.id = b.dict_id and a.dict_name = 'RM_REBATE_MODEL_TYPE') d1
on t.calculation_type = d1.dictdata_value
left join (select b.dictdata_name, b.dictdata_value from tb_dictionary a, tb_dictionary_data b where a.id = b.dict_id and a.dict_name = 'RM_BASE_CALCULATION') d2
on t.base_calculation = d2.dictdata_value
left join (select b.dictdata_name, b.dictdata_value from tb_dictionary a, tb_dictionary_data b where a.id = b.dict_id and a.dict_name = 'RM_CALCULATION_METHOD') d3
on t.calculation_method = d3.dictdata_value
left join (select b.dictdata_name, b.dictdata_value from tb_dictionary a, tb_dictionary_data b where a.id = b.dict_id and a.dict_name = 'RM_CREDIT_TYPE') d4
on t.credit_type = d4.dictdata_value
where (case when '' is null or t.policy_supp_id ='' then 1 else 0 end)=1
and (case when '' is null or t.policy_reminder_id ='' then 1 else 0 end)=1
and (case when '' is null or b.org_id ='' then 1 else 0 end)=1
and b.CREDATE between to_date('2015-03-31' ,'yyyy-mm-dd hh24:mi:ss') and to_date('2016-03-31' ||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
and EXISTS(select 1 from base_goods_group_rl where group_id =t.goods_group_id and is_active=1 AND GOODS_ID=B.GOODSID)
and EXISTS(select 1 from base_customer_group_rl where group_id=t.custom_group_id and is_active=1 AND CUSTOMS_ID=B.CUSTOMID)
and (case when '' is null or b.goodsid='' then 1 else 0 end)=1
and (case when '' is null or b.SUPPLYERID='' then 1 else 0 end)=1
and (case when '' is null or t.policy_reminder_id='' then 1 else 0 end)=1
and (case when '' is null or b.salerid='' then 1 else 0 end)=1
and t.detail_active = 1
and t.rebate_policy_type = 3;