plsql基础-->创建包

--包头

create or replace package TOLLCOLLECTION is

  -- Author  : DINGWEICHANG
  -- Created : 2012-04-25 17:00:02

  -- Purpose :


  -- 超高费用标准计算中的标准计算系数
  pck_coefficient number(10,3) := 0.3;


  -- 保存账目小数位数
  pck_accountDecimalDigits integer := 2;


  -- 操作人id
  pck_oUserId varchar2(36);


  -- 操作人姓名
  pck_oUserName varchar2(64);


  -- 设置生成多少户提交一次
  pck_commitNum integer := 1000;


  -- 生成应收年度
  pck_makeYear varchar2(10);


  -- 生成应收上一年度
  pck_beforMakeYear varchar2(10);


  -- 收费项目中热费Id
  pck_htcs_p_fee_item_id char(36) := '42066561-6E14-45A6-8E3F-2E30043052F5';


  -- 生成账目时标志
  pck_accountFlag0 varchar2(2) := '0'; -- 应收
  pck_accountFlag1 varchar2(2) := '1'; -- 基础费


  -- 日志关信息
  pck_logId char(36); -- 生成应收日志id
  pck_beginTime Date; -- 操作执行开始时间
  pck_endTime Date; -- 操作执行结束时间
  pck_operatorInfo varchar2(1000); -- 操作执行信息
  pck_logRemark varchar2(256); -- 备注


  -- 查询当前在用年度基本信息
  pck_htcs_p_basic_record htcs_p_basic%rowtype;


  -- 满足条件的合同(在网、当年未生成应收的用户)
cursor cursor_get_contract(p_layer varchar2) is
select  htcs_m_usercontract_id,
contractcode,
username,
cstate,
resideflag,
residedate,
makereceivedyear
from htcs_m_usercontract
where ((layer like p_layer || '%') or (htcs_m_usercontract_id = p_layer)) and
 (makereceivedyear is null or makereceivedyear != pck_makeYear) and
 cstate = '1'
order by layer;


  -- 满足调整条件的合同(网内用户、停供用户、当年已生成应收)
cursor c_get_contractForAdjust(p_layer varchar2) is
select  htcs_m_usercontract_id,
contractcode,
username,
roomno,
cstate,
makereceivedyear
from htcs_m_usercontract
where layer like p_layer || '%' and
 makereceivedyear = pck_makeYear and
 cstate in ('2','3','4')
order by layer;


  -- 根据合同id查询合同面积信息Cursor
cursor cursor_get_usercontract_area(p_ucid char) is
select t.htcs_m_usercontract_area_id, -- 合同面积表id
t.htcs_p_areakind_id, -- 面积类型id
t.area, -- 面积
t.stop_area, -- 停供面积
t.stopheatrate, -- 停供系数
t.floorratio, -- 层高系数（收费系数）
t1.unitprice, -- 面积单价
t2.areakind, -- 面积类型名称
t2.calcoverheightstyle, -- 超高计算方式 0：不计算 1：超高面积 2：超高系数 3：层高标准计算
t2.standardheight, -- 标高
t2.heightratio -- 层高
from HTCS_M_USERCONTRACT_AREA t
inner join HTCS_P_AREAKIND_ANNUAL t1 on t1.isactive = 'Y' and
t1.htcs_p_basic_id = pck_htcs_p_basic_record.htcs_p_basic_id and
t1.htcs_p_areakind_id = t.htcs_p_areakind_id
inner join htcs_p_areakind t2 on t2.isactive = 'Y' and
t2.htcs_p_areakind_id = t.htcs_p_areakind_id
where t.isactive = 'Y' and t.htcs_m_usercontract_id = p_ucid;


  -- 设置系统参数（面积种类及单价）
  procedure setSysParameters;


-- 保存到账目明细表cursor_get_usercontract_area
procedure saveToAccountDetail(cursor_get_contract_record cursor_get_contract%rowtype,
 cursor_get_uc_area_record cursor_get_usercontract_area%rowtype,
 receivablesum number,
 accountFlag varchar2); -- 0正常应收,1基础费应收


  -- 生成应收
  procedure makeAccountReceivable(in_layerAndId in varchar2, -- 层次码用于查询合同
in_oUserId in varchar2, -- 操作人ID
in_oUserName in varchar2, -- 操作人姓名
makeFlag in out varchar2, -- 生成结果
inout_logid in out varchar2); -- 生成应收日志id


-- 因单价调整引起账目调整
--procedure adjustAccountForPriceChanged(in_oUserId in number, -- 操作人ID
--   in_oUserName in varchar2,    -- 操作人姓名
--   makeFlag in out varchar2);   -- 生成结果
--
end TOLLCOLLECTION;

--包体

create or replace package body TOLLCOLLECTION is
-- 设置面积种类及单价
procedure setSysParameters
is


begin
-- 查询在用年度基本参数信息
select t.* into pck_htcs_p_basic_record
from htcs_p_basic t
where t.isactive = 'Y' and used = 'Y';


pck_makeYear := pck_htcs_p_basic_record.annual;
pck_beforMakeYear := to_number(substr(pck_makeYear,1,4))-1 || '-' || (to_number(substr(pck_makeYear,6))-1);


-- 查询设置账目小数位数
-- select accountdecimaldigits into accountDecimalDigits
-- from htcs_s_initconfig
-- where rownum = 1;
end setSysParameters;


-- 保存到账目明细表cursor_get_usercontract_area
procedure saveToAccountDetail(cursor_get_contract_record cursor_get_contract%rowtype,
 cursor_get_uc_area_record cursor_get_usercontract_area%rowtype,
 receivablesum number,
 accountFlag varchar2)-- 0正常应收,1基础费应收
is
-- 应收
v_receivablesum htcs_b_accountdetail.receivablesum%type;


-- 摘要
v_summary htcs_b_accountdetail.summary%type;


-- 单价
v_price htcs_b_accountdetail.price%type;
begin
if (receivablesum > 0) then
if(accountFlag = pck_accountFlag0) then
-- 应收
v_receivablesum := receivablesum;


-- 生成摘要
v_summary := cursor_get_uc_area_record.areakind || '应收';


-- 单价
v_price := nvl(cursor_get_uc_area_record.unitprice,0);
else
-- 基础费应收
v_receivablesum := receivablesum;


-- 生成摘要
v_summary := cursor_get_uc_area_record.areakind || '应收-基础费';


-- 基础费单价
v_price := nvl(cursor_get_uc_area_record.unitprice,0) * nvl(cursor_get_uc_area_record.stopheatrate,1);
end if;


-- 新生成账目保存到账目明细表
insert into htcs_b_accountdetail(
HTCS_B_ACCOUNTDETAIL_ID,-- 唯一标识
HTCS_M_USERCONTRACT_ID,-- 合同id
CONTRACTCODE, -- 合同号
UNAME, -- 用户名
INACTDATETIME,-- 入账时间
HEATYEAR, -- 供暖年度
TCITEMCODE, -- 收费项目id
TCITEM, -- 收费项目
SUMMARY, -- 摘要
AREA, -- 供热面积
AREAKIND, -- 面积类别
PRICE, -- 单价
RECEIVABLESUM,-- 应收金额
CHARGERATE, -- 收费系数
BASEFEEFLAG, -- 是否基础费
ENTERPRISEFEETYPE,-- 交费性质：0：热用户费用 1、开发商应收
ISCHECK, -- 审核
ACTFLAG, -- 账目标志
ACTFLAG1, -- 账目标志1
createuser, -- 创建人
createDatetime-- 创建时间
)
values(
GET_FORMATTED_GUID(),
cursor_get_contract_record.htcs_m_usercontract_id,
cursor_get_contract_record.contractcode,
cursor_get_contract_record.username,
sysdate,
pck_makeYear,
pck_htcs_p_fee_item_id,
'热费应收',
v_summary,
cursor_get_uc_area_record.area,
cursor_get_uc_area_record.htcs_p_areakind_id,
v_price,
ROUND(v_receivablesum,pck_accountDecimalDigits),
cursor_get_uc_area_record.floorratio,
accountFlag,
'0',
'0',
'0',
'6',
pck_oUserName,
sysdate
);
end if;
end saveToAccountDetail;


-- 单价调整引起账目调整


-- 保存日志
procedure addOperatorLog(oFlag in varchar2, -- 0:成功 -1:失败
oLayer in varchar2,-- 生成范围
remark in varchar2,-- 备注
oFlag1 in varchar2)-- 0:生成应收 1:应收调整
is
begin
if(oFlag1 = '0') then
-- 写入生成应收日志
insert into htcs_b_makereceivablelog(
htcs_b_makereceivablelog_id,
heatyear,
name,
begintime,
endtime,
makelayer,
makeInfo,
makeFlag,
remark)
values(
pck_logId,
pck_makeYear,
pck_oUserName,
pck_beginTime,
pck_endTime,
oLayer,
pck_operatorInfo,
oFlag,
pck_logRemark);
else
-- 写入应收调整日志
/*insert into htcs_b_adjustaccountbypricelog(
id,
heatyear,
adjustUserName,
begintime,
endtime,
adjustInfo,
adjustFlag,
remark)
values(
logId,
makeYear,
oUserName,
beginTime,
endTime,
operatorInfo,
oFlag,
remark);*/
null;
end if;


-- 提交
commit;
end addOperatorLog;


-- 生成应收,成功返回0,失败返回-1
procedure makeAccountReceivable(in_layerAndId in varchar2,
in_oUserId in varchar2,
in_oUserName in varchar2,
makeFlag in out varchar2,
inout_logid in out varchar2)
is
-- 生成应收的合同数（每生成1000合同提交一次）
v_makeCount integer := 0;


-- 单价
v_prive number(10,3) := 0;


-- 停供系数
v_stopheatrate number(10,3) := 1;


-- 收费系数
v_floorratio number(10,3) := 1;


-- 面积
v_area HTCS_M_USERCONTRACT_AREA.area%type;


-- 面积和
v_area_sum HTCS_M_USERCONTRACT_AREA.area%type;


-- 停供面积
v_stoparea HTCS_M_USERCONTRACT_AREA.area%type;


-- 供暖面积
v_heatarea HTCS_M_USERCONTRACT_AREA.area%type;


-- 超高面积
v_heightarea HTCS_M_USERCONTRACT_AREA.area%type;


-- 应收费用
v_receivablesum number(12,3);


-- 超高应收
v_receivablesum_height number(12,3);


-- 基础费应收
v_receivablesum_base number(12,3);


-- 汇总生成年度 上一年度欠费用Cursor
/*cursor get_oweSum_by_tcitem(p_cid number) is
select tcitemcode,ROUND((nvl(sum(receivablesum),0)-nvl(sum(receivedsum),0)),accountDecimalDigits) owesum
from htcs_b_accountdetail
where actflag='0' and cid = p_cid and heatyear = beforMakeYear
group by tcitemcode;*/


-- 修改合同面积单价SQL
v_updatePriceSql varchar2(1000);


-- 应生成合同数
v_makeAllcount integer;


-- 当前操作合同id
v_cur_contract_id char(36);


-- 当前操作合同号
v_cur_contract_code varchar2(16);
begin
-- 日志id
select GET_FORMATTED_GUID() into pck_logId from dual;
inout_logid := pck_logId;


-- 操作开始时间
select sysdate into pck_beginTime from dual;


-- 操作用户id
pck_oUserId := in_oUserId;


-- 操作用户名称
pck_oUserName := in_oUserName;


-- 设置成功标为false
makeFlag := '-1';


-- 取得系统可用面积及单价
setSysParameters();


-- 生成信息
select count(1) into v_makeAllcount
from htcs_m_usercontract
where ((layer like in_layerAndId || '%') or (layer || '-' || htcs_m_usercontract_id = in_layerAndId)) and
 (makereceivedyear is null or makereceivedyear != pck_makeYear) and
 cstate = '1';
pck_operatorInfo := '应生成' || v_makeAllcount || '户';


-- 根据层次码取得本次生成应收的合同
for cursor_get_contract_record in cursor_get_contract(in_layerAndId) loop
-- 如果未生成应收,则生成应收
if(cursor_get_contract_record.makereceivedyear is null or cursor_get_contract_record.makereceivedyear <> pck_makeYear) then
-- 当前操作合同id
v_cur_contract_id := cursor_get_contract_record.htcs_m_usercontract_id;


-- 当前操作合同号
v_cur_contract_code := cursor_get_contract_record.contractcode;


-- 生成预收转本期(账目明细表中汇总欠费如果为负数,则说明上年有余额,生成预收转本期)
/*for get_oweSum_by_tcitem_record in get_oweSum_by_tcitem(cursor_get_contract_record.id)loop
-- 保存到预收转本期表
if(get_oweSum_by_tcitem_record.owesum < 0) then
insert into htcs_b_advancetransfer(
id, -- id
cid, -- 合同id
ccode, -- 合同号
uname, -- 合同中的用户名
heatyear, -- 供热年度
inactDatetime,-- 入账时间
transfersum, -- 预收金额
remark, -- 备注
createuser, -- 创建人
createDatetime) -- 创建时间
values(
seq_htcs_b_advancetransfer.nextval,
cursor_get_contract_record.id,
cursor_get_contract_record.contractcode,
cursor_get_contract_record.username,
makeYear,
sysdate,
abs(get_oweSum_by_tcitem_record.owesum),
'',
oUserName,
sysdate);


-- 账目明细表中添加记录
-- 转入记录
insert into htcs_b_accountdetail(
id, -- id
cid, -- 合同id
ccode, -- 合同编号
uname, -- 用户名
inactDatetime,-- 入账时间
heatyear, -- 供暖年度
tcitemcode, -- 收费项目编码
tcitem, -- 收费项目
summary, -- 摘要
area, -- 供热面积
areakind, -- 面积类别
price, -- 单价
receivedsum, -- 实收金额
owesum, -- 欠缴金额
ischeck, -- 审核
actflag, -- 账目标志
actflag1, -- 账目标志1 5缴费,13预收转本期
createuser, -- 创建人
createDatetime-- 创建时间
)
values(
seq_htcs_b_accountdetail.nextval,
cursor_get_contract_record.id,
cursor_get_contract_record.contractcode,
cursor_get_contract_record.username,
sysdate,
makeYear,
get_oweSum_by_tcitem_record.tcitemcode,
'',
'预收转本期转入',
'',
'',
'',
abs(get_oweSum_by_tcitem_record.owesum),
'',
'0',
'0',
'13',
oUserName,
sysdate
);


-- 转出记录
insert into htcs_b_accountdetail(
id, -- id
cid, -- 合同id
ccode, -- 合同编号
uname, -- 用户名
inactDatetime,-- 入账时间
heatyear, -- 供暖年度
tcitemcode, -- 收费项目编码
tcitem, -- 收费项目
summary, -- 摘要
area, -- 供热面积
areakind, -- 面积类别
price, -- 单价
receivedsum, -- 实收金额
owesum, -- 欠缴金额
ischeck, -- 审核
actflag, -- 账目标志
actflag1, -- 账目标志1 5缴费
createuser, -- 创建人
createDatetime-- 创建时间
)
values(
seq_htcs_b_accountdetail.nextval,
cursor_get_contract_record.id,
cursor_get_contract_record.contractcode,
cursor_get_contract_record.username,
sysdate,
beforMakeYear,
get_oweSum_by_tcitem_record.tcitemcode,
'',
'预收转下期',
'',
'',
'',
get_oweSum_by_tcitem_record.owesum,
'',
'0',
'0',
'14',
oUserName,
sysdate
);
end if;
end loop;*/


-- 初始化v_updatePriceSql
--v_updatePriceSql := 'update htcs_m_usercontract set id = id';


-- 生成本年应收


-- 根据合同id取得合同面积信息
for cursor_get_uc_area_record in cursor_get_usercontract_area(cursor_get_contract_record.HTCS_M_USERCONTRACT_ID) loop
v_prive := nvl(cursor_get_uc_area_record.unitprice,0);
v_stopheatrate := nvl(cursor_get_uc_area_record.stopheatrate,1);
v_area := nvl(cursor_get_uc_area_record.area,0);
v_stoparea := nvl(cursor_get_uc_area_record.stop_area,0);
v_floorratio := nvl(cursor_get_uc_area_record.floorratio,1);
        v_receivablesum_base := 0;
        v_receivablesum := 0;
        v_receivablesum_height := 0;


if(cursor_get_uc_area_record.calcoverheightstyle = '0') then
-- 不计算
-- 停供面积费用计算（停供面积*单价*停供系数）
v_receivablesum_base := v_stoparea * v_prive*v_stopheatrate;


-- 供暖面积费用计算（供暖面积*单价）
v_heatarea := v_area - v_stoparea;
v_receivablesum := v_heatarea * v_prive;


elsif(cursor_get_uc_area_record.calcoverheightstyle = '1') then
-- 折算超高面积
select nvl(sum(convertarea),0) into v_heightarea from HTCS_M_USERCONTRACT_HEIGHT where isactive = 'Y' and
htcs_m_usercontract_area_id = cursor_get_uc_area_record.htcs_m_usercontract_area_id;
-- 面积和
v_area_sum := v_area + v_heightarea;


-- 停供面积费用计算（停供面积*单价*停供系数）
-- 如果面积为全部停供则超高面积也计算基础费
if(v_stoparea = v_area) then
v_stoparea := v_area_sum;
v_receivablesum_base := v_stoparea * v_prive*v_stopheatrate;
else
-- 供暖面积费用计算（供暖面积*单价）
v_heatarea := v_area_sum - v_stoparea;
v_receivablesum := v_heatarea * v_prive;
end if;
elsif(cursor_get_uc_area_record.calcoverheightstyle = '2') then
-- 按收费系数（无需查询超高表）
-- 停供面积费用计算（停供面积*单价*停供系数*收费系数）
v_receivablesum_base := v_stoparea * v_prive*v_stopheatrate*v_floorratio;


-- 供暖面积费用计算（供暖面积*单价*收费系数）
v_heatarea := v_area - v_stoparea;
v_receivablesum := v_heatarea * v_prive*v_floorratio;


elsif(cursor_get_uc_area_record.calcoverheightstyle = '3') then
-- 层高标准计算
-- 停供面积费用计算（停供面积*单价*停供系数）
v_receivablesum_base := v_stoparea * v_prive*v_stopheatrate;


-- 供暖面积费用计算（供暖面积*单价）
v_heatarea := v_area - v_stoparea;
v_receivablesum := v_heatarea * v_prive;


-- 超高面积费用（（层高-3）*单价*面积*超高费用标准计算中的标准计算系数)）
for htcs_m_uc_h_record in (select * from HTCS_M_USERCONTRACT_HEIGHT t where isactive = 'Y' and t.htcs_m_usercontract_area_id = cursor_get_uc_area_record.htcs_m_usercontract_area_id) loop
if(htcs_m_uc_h_record.floorheight > cursor_get_uc_area_record.standardheight) then
v_receivablesum_height := v_receivablesum_height + (htcs_m_uc_h_record.floorheight - cursor_get_uc_area_record.standardheight) *
nvl(htcs_m_uc_h_record.convertarea,0) * v_prive *
pck_coefficient;
end if;
end loop;
end if;


-- 保存至账目表
-- 保存到账目明细表(基础费)
saveToAccountDetail(cursor_get_contract_record,cursor_get_uc_area_record,v_receivablesum_base,pck_accountFlag1);


-- 保存到账目明细表(正常热费)
saveToAccountDetail(cursor_get_contract_record,cursor_get_uc_area_record,v_receivablesum,pck_accountFlag0);


-- 保存到账目明细表(标准超高费用)
saveToAccountDetail(cursor_get_contract_record,cursor_get_uc_area_record,v_receivablesum_height,pck_accountFlag0);


-- 修改合同面积表中的单价
update htcs_m_usercontract_area set area_price = v_prive
where htcs_m_usercontract_area_id = cursor_get_uc_area_record.htcs_m_usercontract_area_id;
end loop;


-- 修改合同表中的热费生成年度
update htcs_m_usercontract
set makereceivedyear = pck_makeYear
where htcs_m_usercontract_id = cursor_get_contract_record.htcs_m_usercontract_id;


-- 生成用户数加1
v_makeCount := v_makeCount + 1;
end if;


-- 每生成1000合同提交一次
if(v_makeCount != 0 and v_makeCount mod pck_commitNum = 0) then
commit;
end if;


end loop;


-- 提交
commit;


-- 实际生成合同数
pck_operatorInfo := pck_operatorInfo || ',实际生成' || v_makeCount || '户,生成成功。';


-- 操作结束时间
select sysdate into pck_endTime from dual;


-- 操作日志
addOperatorLog('0',in_layerAndId,'','0');


-- 设置成功标记为true
makeFlag := '0';
exception
when others then
makeFlag := '-1';


-- 操作结束时间
select sysdate into pck_endTime from dual;


-- 实际生成合同数
pck_operatorInfo := pck_operatorInfo || ',实际生成'|| v_makeCount || ',生成失败。生成失败的合同id为:' || v_cur_contract_id || '合同号为:' || v_cur_contract_code;


-- 备注
pck_logRemark := '错误号：' || SQLCODE || ',错误信息：' || SQLERRM;


-- 操作日志
addOperatorLog('-1',in_layerAndId,'','0');


rollback;
end makeAccountReceivable;


-- 面积单价调整后生成单价调整账,成功返回0,失败返回-1


end TOLLCOLLECTION;