|
create or replace package PG_PRD_TREE is
type commCur is ref cursor; -- 定义公用游标
-- 查询管理目录树
procedure queryOfrManageTree(o_result out commCur, -- 返回结果集
o_allcount out number,
i_nodeId in varchar2, -- 目录ID
i_number in varchar2,
i_ifFilter in number, --是否过滤
i_filterKey in varchar2, --过滤关键字 销售品名称
i_latnId in varchar2);
-- 查询管理目录树 某个节点下面销售个数
procedure queryOfrCount(o_count out number, -- 返回结果总数
i_nodeId in varchar2); -- 目录ID
-- 查询管理目录树 分页查询 某个节点下面销售品
procedure queryPaginagedOfrs(o_result out commCur, -- 返回结果集
o_allcount out number,
i_nodeId in varchar2, -- 目录ID
i_number in varchar2,
i_pageno in number,
i_ifFilter in number, --是否过滤
i_filterKey in varchar2, --过滤关键字 销售品名称
i_latnId in varchar2);
-- 查询定价目录树
procedure queryPricingTree(o_result out commCur, -- 返回结果集
o_resultCnt out number, -- 返回结果集数量
o_nodeType out varchar2, -- 节点类型
i_latnId in varchar2,
i_nodeId in number, -- 目录ID
i_pageSize in number, --每页数量
i_pageNo in number, --当前页数
i_ifFilter in number, --是否过滤
i_filterType in varchar2, --过滤类型，1模板类型；2定价模板；3定价策略
i_filterKey in varchar2 --过滤关键字
);
end PG_PRD_TREE;
/
create or replace package body PG_PRD_TREE IS
-- 查询管理目录树
procedure queryOfrManageTree(o_result out commCur, -- 返回结果集
o_allcount out number,
i_nodeId in varchar2, -- 目录ID
i_number in varchar2,
i_ifFilter in number, --是否过滤
i_filterKey in varchar2, --过滤关键字 销售品名称
i_latnId in varchar2) is
v_nodeId varchar2(30);
v_ofrName varchar2(500);
v_sql varchar2(5000);
v_latnId varchar2(800);
begin
v_nodeId := i_nodeId;
v_ofrName := '';
v_latnId := '';
if i_nodeId is null then
v_nodeId := -1;
end if;
if (i_ifFilter = 1 and length(trim(i_filterKey)) > 0) then
v_ofrName := ' and o.ofr_name like ''%' || trim(i_filterKey) || '%''';
end if;
if (i_latnId != '888') then
v_latnId := ' and nvl(rul.APPLY_OBJECT_ID,''888'') in(' || i_latnId ||
',888)';
end if;
v_sql := 'select count(1)
from (select distinct o.ofr_id
from tb_prd_ofr o,
tb_prd_ofr_calg_elt oce,
tb_prd_ofr_calg_node ocn,
(select * from tb_prd_ofr_applied_rule where APPLY_OBJECT_TYPE=3) rul
where o.ofr_id = oce.ofr_id
and oce.ofr_calg_node_id = ocn.ofr_calg_node_id
and o.ofr_id=rul.ofr_id(+) ' || v_latnId ||
' and oce.ofr_calg_node_id = ' || v_nodeId ||
' and ocn.ofr_calg_id = 70 ' || v_ofrName || ')';
execute immediate v_sql
into o_allcount;
v_sql := 'select *
from (select ocn.ofr_calg_node_id node_id,
ocn.ofr_calg_node_name node_name,
ocn.par_ofr_calg_node_id par_node_id,
ocn.seq,
1 node_type,
ocn.node_code,
ocn.node_level,
100 ofr_type_id,
ocn.is_open,
(select count(1)
from tb_prd_ofr_calg_node p
where p.par_ofr_calg_node_id = ocn.ofr_calg_node_id) is_leaf,
(select count(1)
from tb_prd_ofr ofr,
tb_prd_ofr_calg_elt elt,
tb_prd_ofr_calg_node node
where ofr.ofr_id = elt.ofr_id
and elt.ofr_calg_node_id = node.ofr_calg_node_id
and elt.ofr_calg_node_id = ocn.ofr_calg_node_id
and node.ofr_calg_id = 70) ofr_count
from tb_prd_ofr_calg_node ocn
where ocn.par_ofr_calg_node_id =' || v_nodeId ||
' and ocn.ofr_calg_id = 70
union
select o.ofr_id node_id,
o.ofr_name node_name,
oce.ofr_calg_node_id par_node_id,
oce.seq,
2 node_type,
o.ofr_code node_code,
(ocn.node_level + 1) node_level,
o.ofr_type_id ofr_type_id,
''0'' is_open,
0 is_leaf,
0 ofr_count
from tb_prd_ofr o,
tb_prd_ofr_calg_elt oce,
tb_prd_ofr_calg_node ocn,
(select * from tb_prd_ofr_applied_rule where APPLY_OBJECT_TYPE=3) rul
where o.ofr_id = oce.ofr_id
and oce.ofr_calg_node_id = ocn.ofr_calg_node_id
and o.ofr_id=rul.ofr_id(+) ' || v_latnId ||
' and oce.ofr_calg_node_id = ' || v_nodeId ||
' and ocn.ofr_calg_id = 70 ' || v_ofrName || 'and ROWNUM <= ' ||
i_number || ')
order by seq asc, node_id asc';
open o_result for v_sql;
end;
procedure queryOfrCount(o_count out number, -- 返回结果集
i_nodeId in varchar2) -- 目录ID
is
v_nodeId varchar2(30);
begin
v_nodeId := i_nodeId;
if i_nodeId is null then
v_nodeId := -1;
end if;
execute immediate 'select count(1) from tb_prd_ofr o,
tb_prd_ofr_calg_elt oce,
tb_prd_ofr_calg_node ocn
where o.ofr_id = oce.ofr_id
and oce.ofr_calg_node_id = ocn.ofr_calg_node_id
and oce.ofr_calg_node_id = :1 and ocn.ofr_calg_id = 70'
into o_count
using v_nodeId;
end;
procedure queryPaginagedOfrs(o_result out commCur, -- 返回结果集
o_allcount out number,
i_nodeId in varchar2, -- 目录ID
i_number in varchar2,
i_pageno in number,
i_ifFilter in number, --是否过滤
i_filterKey in varchar2, --过滤关键字 销售品名称
i_latnId in varchar2) is
v_nodeId varchar2(30);
v_pageno number(8);
v_ofrName varchar2(500);
v_sql varchar2(5000);
v_latnId varchar2(800);
begin
v_nodeId := i_nodeId;
v_ofrName := '';
v_latnId := '';
if i_nodeId is null then
v_nodeId := -1;
end if;
if (i_ifFilter = 1 and length(trim(i_filterKey)) > 0) then
v_ofrName := ' and o.ofr_name like ''%' || trim(i_filterKey) || '%''';
end if;
if (i_latnId != '888') then
v_latnId := ' and nvl(rul.APPLY_OBJECT_ID,''888'') in(' || i_latnId ||
',888)';
end if;
execute immediate 'select count(1) from (select distinct o.ofr_id from tb_prd_ofr o,
tb_prd_ofr_calg_elt oce,
tb_prd_ofr_calg_node ocn,
(select * from tb_prd_ofr_applied_rule where APPLY_OBJECT_TYPE=3) rul
where o.ofr_id = oce.ofr_id
and oce.ofr_calg_node_id = ocn.ofr_calg_node_id
and o.ofr_id=rul.ofr_id(+)' || v_latnId ||
' and oce.ofr_calg_node_id = :1 and ocn.ofr_calg_id = 70' ||
v_ofrName ||')'
into o_allcount
using v_nodeId;
v_pageno := i_pageno;
v_sql := 'select *
from (select A.*, ROWNUM RN
from (select distinct o.ofr_id node_id,
o.ofr_name node_name,
oce.ofr_calg_node_id par_node_id,
oce.seq,
2 node_type,
o.ofr_code node_code,
(ocn.node_level + 1) node_level,
o.ofr_type_id ofr_type_id,
''0'' is_open,
0 is_leaf,
0 ofr_count
from tb_prd_ofr o,
tb_prd_ofr_calg_elt oce,
tb_prd_ofr_calg_node ocn,
(select * from tb_prd_ofr_applied_rule where APPLY_OBJECT_TYPE=3) rul
where o.ofr_id = oce.ofr_id
and oce.ofr_calg_node_id = ocn.ofr_calg_node_id
and o.ofr_id=rul.ofr_id(+)' ||
v_latnId || ' and oce.ofr_calg_node_id =' || v_nodeId ||
' and ocn.ofr_calg_id = 70' || v_ofrName ||
'order by seq asc, node_id asc) A
WHERE ROWNUM <=' || v_pageno * i_number ||
') WHERE RN > 0';
open o_result for v_sql; ---(v_pageno - 1) * i_number
end;
-- 查询定价目录树
procedure queryPricingTree(o_result out commCur, -- 返回结果集
o_resultCnt out number, -- 返回结果集数量
o_nodeType out varchar2, -- 节点类型
i_latnId in varchar2,
i_nodeId in number, -- 目录ID
i_pageSize in number, --每页数量
i_pageNo in number, --当前页数
i_ifFilter in number, --是否过滤
i_filterType in varchar2, --过滤类型，1模板类型；2定价模板；3定价策略
i_filterKey in varchar2 --过滤关键字
) is
v_nodeId number(30) := -1; --父节点
v_tmpSql varchar2(2000) := ''; --临时SQL
v_filterKey varchar2(200) := ''; --关键字
begin
v_nodeId := i_nodeId;
if i_nodeId is null then
v_nodeId := -1;
end if;
--获取节点类型
select nvl(min(PRICING_CALG_NODE_TYPE), '-1')
into o_nodeType
from tb_bil_pricing_calg_node a
where a.pricing_calg_node_id = i_nodeId;
v_filterKey := regexp_replace(i_filterKey, '[<|>|&|''|]', ' ');
v_tmpSql := 'select pcn.pricing_calg_node_id node_id,
pcn.pricing_calg_node_name node_name,
pcn.par_pricing_calg_node_id par_node_id,
pcn.seq,
pcn.PRICING_CALG_NODE_TYPE node_type,
pcn.node_code,
pcn.pricing_calg_node_obj_id node_obj_id,
pcn.node_level,
pcn.is_open,
pcn.latn_id,
decode((select count(1)
from tb_bil_pricing_calg_node p
where p.par_pricing_calg_node_id =
pcn.pricing_calg_node_id),
0,
1,
0) is_leaf
from tb_bil_pricing_calg_node pcn
where pcn.par_pricing_calg_node_id = :v_nodeId
and pcn.pricing_calg_type = 1 ';
if (i_ifFilter = 1 and length(trim(i_filterKey)) > 0 and
i_filterType in ('1', '2', '3')) then
--过滤
-- v_ifFilter := 1;
--需要按关键字过滤
if (i_filterType = '1') then
--按“模板类型”过滤
v_tmpSql := v_tmpSql ||
' and ((pcn.PRICING_CALG_NODE_TYPE=2 and pcn.pricing_calg_node_name like ''%' ||
v_filterKey || '%'') ';
v_tmpSql := v_tmpSql || ' or pcn.PRICING_CALG_NODE_TYPE<>2 )';
elsif (i_filterType = '2') then
--按“定价模板”过滤
v_tmpSql := v_tmpSql ||
' and ((pcn.PRICING_CALG_NODE_TYPE=3 and pcn.pricing_calg_node_name like ''%' ||
v_filterKey || '%'') ';
v_tmpSql := v_tmpSql || ' or pcn.PRICING_CALG_NODE_TYPE<>3) ';
elsif (i_filterType = '3') then
--按“定价策略”过滤
v_tmpSql := v_tmpSql ||
' and ((pcn.PRICING_CALG_NODE_TYPE=7 and pcn.pricing_calg_node_name like ''%' ||
v_filterKey || '%'') ';
v_tmpSql := v_tmpSql || ' or pcn.PRICING_CALG_NODE_TYPE<>7) ';
end if;
execute immediate 'select count(*) from (' || v_tmpSql || ') '
into o_resultCnt
using v_nodeId;
/*if (o_nodeType = '3') then
--查询定价策略节点时才需要分页
open o_result for ' select A.*, ROWNUM RN
from (
select * from (' || v_tmpSql || '
and exists(select 1 from tb_bil_evt_pricing_strategy str
where pcn.par_pricing_calg_node_id=str.EVENT_PRICING_STRATEGY_ID
and decodestr.latn_id in())
) order by seq asc, node_obj_id asc
) A
WHERE ROWNUM <= :i_pageNo * :i_pageSize'
using v_nodeId, i_pageNo, i_pageSize;
else
open o_result for ' select * from (' || v_tmpSql || ' ) order by seq asc, node_obj_id asc '
using v_nodeId;
end if;*/
end if;
if (i_latnId <> '888' and o_nodeType = '3') then
--在查询定价策略时，如果不按全省查询时，需要按本地网过滤
v_tmpSql := v_tmpSql || ' and exists(select 1 from tb_bil_evt_pricing_strategy str
where pcn.pricing_calg_node_obj_id=str.EVENT_PRICING_STRATEGY_ID
and nvl(str.latn_id,888) in(' || i_latnId ||
',888))';
end if;
execute immediate 'select count(*) from (' || v_tmpSql || ') '
into o_resultCnt
using v_nodeId;
if (o_nodeType = '3') then
--查询定价策略节点时才需要分页
open o_result for 'select A.*, ROWNUM RN
from (
select * from (' || v_tmpSql || ' ) order by seq asc, node_obj_id asc
) A
WHERE ROWNUM <= :i_pageNo * :i_pageSize'
using v_nodeId, i_pageNo, i_pageSize;
else
open o_result for ' select * from (' || v_tmpSql || ' ) order by seq asc, node_obj_id asc '
using v_nodeId;
end if;
end;
END;
/
|
