create or replace procedure up_Reg_FirstReg
(
nOpTypeNum number,
nRecYear number,
nRecNum number,
aRegUserName varchar2,
aCheckFlag number,
aSucc out integer,
aReason out varchar2
)
/**************************************************************************
功 能:
输入参数:
输出参数:
创建日期:
策 略:
**************************************************************************/
is
vSql varchar2(4000);
nBuildID number;
nHouseID number;
vTempRc pk_var.RC;
vTbrghouseRow tbrghouse@DLREGBOOK%rowtype;
vTbRgBuildRow TbRgBuild@DLREGBOOK%rowtype;
vTbCadHouseDWGRow TbRgHouseCadDWG@DLREGBOOK%rowtype;
vRgInfoFid number(15,0);
vRegBookKey number(15,0);
sBlockNum varchar2(4000);
sLandCert varchar2(4000);
sUseDate varchar2(4000);
sLandProp varchar2(4000);
sNationalLandGetType varchar2(4000);
sCollectiveLandUseType varchar2(4000);
nWorkUnitID number;
vReghousenum varchar2(32);
vSitnumgathe varchar2(200);
vArchitarea number(32,2);
vRightRangeNum number;
vSucc number;
vTempRcA pk_var.RC;
vHouseCount number;
vReceiveSitNumGather varchar2(1000);
begin
aSucc := 0;
up_Reg_GetLandInfo(nOpTypeNum, nRecYear, nRecNum,
sBlockNum, sLandCert, sUseDate,
sLandProp, sNationalLandGetType, sCollectiveLandUseType,
vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, aReason, true);
end if;
nWorkUnitID := uf_reg_getworkunit;
vSql :='select count(*)
from tbwkhouse a
where a.OpTypeNum = :OpTypeNum3
and a.RecYear = :RecYear3
and a.RecNum = :RecNum3';
execute immediate vSql into vHouseCount using nOpTypeNum, nRecYear, nRecNum;
vSql :=
'select
a.RecNumGather, a.oppartnum, trim(b.sitnumgather) SitNumGather, b.district,
b.roadcode,trim(b.sitnumgather) SitNumGather,b.cadid,
c.buildingname,b.unitname,b.floorname,b.doorname,
b.buildingid,b.unitnum,b.floornum,b.doornum,
b.totalfloorcounts,b.architarea,
b.netarearea,b.apportarea,b.alonearea,
b.houseusage,
b.housetype,
b.architstruct,
UF_Reg_GetBuildDate(b.completedyear) BuildDate,
b.buildingid,b.houseid,b.fid,b.sitnumgather,
b.blocknum,b.landcertnum,
b.landcharacter,
b.nationallandgettype, b.collectivelandusetype,
b.slandusedate
from tbwkbasicinfo a,tbwkhouse b,tbwkbuilding c,
(select /*+ rule */ d1.fid, d1.buildingid, c1.apportarea
from tbwkbuilding d1,
(select a1.buildingid, sum(nvl(a1.apportarea, 0)) apportarea
from tbwkhouse a1
where a1.OpTypeNum = :OpTypeNum1
and a1.RecYear = :RecYear1
and a1.RecNum = :RecNum1
group by a1.buildingid) c1
where d1.buildingid = c1.buildingid) e
where a.fid = b.basicinfoid
and b.buildingid=c.buildingid
and b.houseid > 0
and b.houserighttype=''3''
and a.OpTypeNum = :OpTypeNum3 and a.RecYear = :RecYear3 and a.RecNum = :RecNum3
and b.buildingid = e.buildingid(+)
';
--取出游标数据
open vTempRc for vSql using nOpTypeNum, nRecYear, nRecNum,
nOpTypeNum, nRecYear, nRecNum;
loop
fetch vTempRc into
vTbrghouseRow.RECNUMGATHER, vTbrghouseRow.oppartnum, vTbrghouseRow.SITNUMGATHER, vTbrghouseRow.DISTRICT,
vTbrghouseRow.ROADCODE, vTbrghouseRow.SITNAME, vTbCadHouseDWGRow.cadhousenum,
vTbrghouseRow.BUILDNAME, vTbrghouseRow.UNITNAME, vTbrghouseRow.FLOORNAME, vTbrghouseRow.DOORNAME,
vTbrghouseRow.BUILDNUM, vTbrghouseRow.UNITNUM, vTbrghouseRow.FLOORNUM, vTbrghouseRow.DOORNUM,
vTbrghouseRow.TOTALFLOORCOUNTS, vTbrghouseRow.ARCHITAREA,
vTbrghouseRow.NETAREAREA, vTbrghouseRow.APPORTAREA, vTbrghouseRow.ALONEAREA,
vTbrghouseRow.HOUSEUSAGE,
vTbrghouseRow.HOUSETYPE,
vTbrghouseRow.ARCHITSTRUCT,
vTbrghouseRow.COMPLETEDYEAR,
nBuildID, nHouseID, vRightRangeNum, vReceiveSitNumGather,
vTbrghouseRow.BlockNum, vTbrghouseRow.LANDCERTNUM,
vTbrghouseRow.LANDCHARACTER,
vTbrghouseRow.NATIONALLANDGETTYPE, vTbrghouseRow.COLLECTIVELANDUSETYPE,
vTbrghouseRow.SLANDUSEDATE;
exit when vTempRc%notfound;
if vHouseCount = 1 then
if vReceiveSitNumGather is not null then
vTbrghouseRow.SITNUMGATHER := vReceiveSitNumGather;
end if;
end if;
up_Reg_GetTbRgInfoID(nOpTypeNum, nRecYear, nRecNum,
nBuildID, nHouseID,
vTbrghouseRow.DISTRICT, vTbrghouseRow.COMPLETEDYEAR,
vRgInfoFid, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, aReason, true);
end if;
vTbrghouseRow.RgInfoFid := vRgInfoFid;
vTbrghouseRow.RegUserName := aRegUserName;
up_REG_Insert_House(vTbrghouseRow, vTbCadHouseDWGRow, '', aCheckFlag, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, aReason, true);
end if;
up_REG_Insert_HouseCAD(vTbCadHouseDWGRow.cadhousenum, vRgInfoFid, vTbrghouseRow.DISTRICT, aCheckFlag, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, '写入房屋CAD信息时出错!', true);
end if;
vRegBookKey := UF_GetMaxRegBookKey@DLREGBOOK(vTbrghouseRow.DISTRICT);
vSql :=
' Insert into TbRgDroit@DLREGBOOK
(FID, RECNUMGATHER, OpPartNum, OwnerName,
RegDate, RegUserName, RgInfoFid, CheckFlag)
values
( :FID, :RECNUMGATHER, :OpPartNum, :OwnerName,
sysdate, :RegUserName, :RgInfoFid, :CheckFlag)';
execute immediate vSql using
vRegBookKey, vTbrghouseRow.RECNUMGATHER, vTbrghouseRow.oppartnum, '全体业主',
aRegUserName, vRgInfoFid, aCheckFlag;
vRegBookKey := UF_GetMaxRegBookKey@DLREGBOOK(vTbrghouseRow.DISTRICT);
vSql := 'insert into TbRgOwnerDetail@DLREGBOOK
(FID, RECNUMGATHER, OWNERNAME,
RGINFOFID)
values
(:FID, :RECNUMGATHER, :OWNERNAME,
:RGINFOFID)';
execute immediate vSql using vRegBookKey, vTbrghouseRow.RECNUMGATHER, '全体业主',
vRgInfoFid;
end loop;
close vTempRc;
--打开房表游标
vSql :=
'select
a.RecNumGather, a.oppartnum, trim(b.sitnumgather) SitNumGather, b.district,
b.roadcode,trim(b.sitnumgather) SitNumGather,b.cadid,
c.buildingname,b.unitname,b.floorname,b.doorname,
b.buildingid,b.unitnum,b.floornum,b.doornum,
b.totalfloorcounts,b.architarea,
b.netarearea,b.apportarea,b.alonearea,
b.houseusage,
b.housetype,
b.architstruct,
UF_Reg_GetBuildDate(b.completedyear) BuildDate,
b.buildingid,b.houseid,b.fid,b.sitnumgather,
b.blocknum,b.landcertnum,
b.landcharacter,
b.nationallandgettype, b.collectivelandusetype,
b.slandusedate,
c.buildingsite, e.apportarea
from tbwkbasicinfo a,tbwkhouse b,tbwkbuilding c,
(select /*+ rule */ d1.fid, d1.buildingid, c1.apportarea
from tbwkbuilding d1,
(select a1.buildingid, sum(nvl(a1.apportarea, 0)) apportarea
from tbwkhouse a1
where a1.OpTypeNum = :OpTypeNum1
and a1.RecYear = :RecYear1
and a1.RecNum = :RecNum1
group by a1.buildingid) c1
where d1.buildingid = c1.buildingid) e
where a.fid = b.basicinfoid
and b.buildingid=c.buildingid
and b.houseid > 0
and a.OpTypeNum = :OpTypeNum3 and a.RecYear = :RecYear3 and a.RecNum = :RecNum3
and b.buildingid = e.buildingid(+)
and b.houserighttype in (''1'',''2'')
';
--取出游标数据
open vTempRc for vSql using nOpTypeNum, nRecYear, nRecNum,
nOpTypeNum, nRecYear, nRecNum
;
loop
fetch vTempRc into
vTbrghouseRow.RECNUMGATHER, vTbrghouseRow.oppartnum, vTbrghouseRow.SITNUMGATHER, vTbrghouseRow.DISTRICT,
vTbrghouseRow.ROADCODE, vTbrghouseRow.SITNAME, vTbCadHouseDWGRow.cadhousenum,
vTbrghouseRow.BUILDNAME, vTbrghouseRow.UNITNAME, vTbrghouseRow.FLOORNAME, vTbrghouseRow.DOORNAME,
vTbrghouseRow.BUILDNUM, vTbrghouseRow.UNITNUM, vTbrghouseRow.FLOORNUM, vTbrghouseRow.DOORNUM,
vTbrghouseRow.TOTALFLOORCOUNTS, vTbrghouseRow.ARCHITAREA,
vTbrghouseRow.NETAREAREA, vTbrghouseRow.APPORTAREA, vTbrghouseRow.ALONEAREA, vTbrghouseRow.HOUSEUSAGE,
vTbrghouseRow.HOUSETYPE,
vTbrghouseRow.ARCHITSTRUCT,
vTbrghouseRow.COMPLETEDYEAR,
nBuildID, nHouseID, vRightRangeNum, vReceiveSitNumGather,
vTbrghouseRow.BlockNum, vTbrghouseRow.LANDCERTNUM,
vTbrghouseRow.LANDCHARACTER,
vTbrghouseRow.NATIONALLANDGETTYPE, vTbrghouseRow.COLLECTIVELANDUSETYPE,
vTbrghouseRow.SLANDUSEDATE,
vTbRgBuildRow.BuildName, vTbRgBuildRow.CommTotalAreas;
exit when vTempRc%notfound;
if vHouseCount = 1 then
if vReceiveSitNumGather is not null then
vTbrghouseRow.SITNUMGATHER := vReceiveSitNumGather;
end if;
end if;
up_Reg_GetTbRgInfoID(nOpTypeNum, nRecYear, nRecNum,
nBuildID, nHouseID,
vTbrghouseRow.DISTRICT, vTbrghouseRow.COMPLETEDYEAR,
vRgInfoFid, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, aReason, true);
end if;
vTbrghouseRow.RgInfoFid := vRgInfoFid;
vTbrghouseRow.RegUserName := aRegUserName;
up_REG_Insert_House(vTbrghouseRow, vTbCadHouseDWGRow, '', aCheckFlag, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, aReason, true);
end if;
up_REG_Insert_HouseCAD(vTbCadHouseDWGRow.cadhousenum, vRgInfoFid, vTbrghouseRow.DISTRICT, aCheckFlag, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, '写入房屋CAD信息时出错!', true);
end if;
up_Reg_InsertDroit(nOpTypeNum, nRecYear, nRecNum, vTbrghouseRow.RgInfoFid, vRightRangeNum,
vTbrghouseRow.DISTRICT, vTbrghouseRow.RegUserName, aCheckFlag, vSucc, aReason);
if vSucc = 0 then
Raise_Application_Error(PK_RegBook.CI_Exception_RegBook, aReason, true);
end if;
if vTbRgBuildRow.CommTotalAreas > 0 then
vRegBookKey := UF_GetMaxRegBookKey@DLREGBOOK(vTbrghouseRow.DISTRICT);
vSql :=
' insert into TbRgBuild@DLREGBOOK
(FID,RecNumGather, Buildname,
Commtotalareas, Owner,
Regdate, Regusername, Rginfofid)
values
(:FID, :Buildname, :RecNumGather,
:Commtotalareas, :Owner,
sysdate, :RegUserName, :RgInfoFid)';
execute immediate vSql using
vRegBookKey,vTbrghouseRow.RECNUMGATHER, vTbRgBuildRow.BuildName,
vTbRgBuildRow.CommTotalAreas, '该幢所有业主',
aRegUserName, vRgInfoFid;
end if;
vsql := 'select b.reghousenum, a.sitnumgather, a.architarea
from tbwkhouse a, tbrginfo@DLREGBOOK b
where a.OpTypeNum = :OpTypeNum
and a.RecYear = :RecYear
and a.RecNum = :RecNum
and a.houserighttype=''3''
and a.houseid=b.houseid
and b.workunit = :workunit ';
open vTempRcA for vsql using nOpTypeNum, nRecYear, nRecNum, nWorkUnitID;
loop
fetch vTempRcA into vReghousenum, vSitnumgathe, vArchitarea;
exit when vTempRcA%notfound;
vRegBookKey := UF_GetMaxRegBookKey@DLREGBOOK(vTbrghouseRow.DISTRICT);
vsql := 'insert into TbRgBuild@DLREGBOOK
(Fid,RecNumGather, Buildnumber, Buildname, Commtotalareas, Owner,
Regdate, Regusername, Rginfofid, CheckFlag)
values(:Fid,:RecNumGather, :Buildnumber, :Buildname, :Commtotalareas, :Owner,
sysdate, :Regusername, :Rginfofid, :CheckFlag)';
execute immediate vSql using
vRegBookKey,vSitnumgathe, vReghousenum, vSitnumgathe, vArchitarea, '全体业主',
aRegUserName, vRgInfoFid, aCheckFlag;
end loop;
close vTempRcA;
end loop;
close vTempRc;
--commit;
aSucc := 1;
exception
when others then
--rollback;
aSucc := 0;
aReason := 'up_Reg_FirstReg'||SubStr(sqlerrm, 1, 200)||aReason;
end up_Reg_FirstReg;