create procedure tran_Add_REGISTER_inas
declare @patientID char(10)
declare @registerID char(12)
declare @inpatientid varchar(12)
declare @inpatientdate datetime
declare @deptid char(10)
declare @areaid char(10)
declare @bedid char(10)
DECLARE @times tinyint declare @transferID char(12)
declare @targetdeptID char(18)
DECLARE @targetareaID char(18)
declare @targetbedID char(18) declare @bedCode char(10)
declare @bed_sex tinyint
declare @category varchar(10) declare @leaveID char(12)
declare @leave_type varchar(20)
declare @outDate datetime declare @e_deptid char(10)
declare @e_areaid char(10)
declare @e_roomid char(10)
declare @e_bedid char(10)
declare @pstatus tinyint --(1-in,3-out)
declare @inbedenddate datetime
declare @sex varchar(10) declare @t_deptid char(10)
declare @t_areaid char(10)
declare @t_bedid char(10) DECLARE @count int
DECLARE @t_count int set @count = 200
set @t_count = 200
declare cur1222 cursor for
select DISTINCT rtrim(a.InPatientID) as InPatientID,a.InPatientDate,rtrim(a.InDeptID) as InDeptID,rtrim(a.Area_ID) as Area_ID,rtrim(a.Room_ID),
rtrim(a.Bed_ID) as Bed_ID,a.InBedEndDate as InBedEndDate from InDeptInfo a inner join InPatientDateInfo b
on a.InPatientID = b.InPatientID and a.InPatientDate = b.InPatientDate where a.InBedEndDate='1900-1-1' --a.InPatientID ='D688302'
order by InPatientID,InBedEndDate
open cur1222
fetch next from cur1222 into @inpatientid,@inpatientdate,@e_deptid,@e_areaid,@e_roomid,@e_bedid,@inbedenddate
while(@@fetch_status=0)
begin
select @deptid=DEPTID_CHR from T_BSE_DEPTDESC where rtrim(CODE_VCHR) = @e_deptid
select @areaid=DEPTID_CHR from T_BSE_DEPTDESC where rtrim(CODE_VCHR) = @e_areaid select @bedCode=Bed_Name from InPatient_Bed_Desc where Bed_ID=@e_bedid select @bedid=BEDID_CHR from T_BSE_BED t where t.code_chr = bedCode and t.areaid_chr = @areaid if(@bedid is not null) begin
set @pstatus = '1'
end
else
set @pstatus = '0'
select @sex=Sex,@times=Times from patientbaseinfo where InPatientID=@inpatientid
if(@sex is not null)
begin
if(rtrim(@sex) = '男')
set @bed_sex='1'
else
set @bed_sex='2'
end
if(@times is null)
set @times='1'
select @registerID=REGISTERID_CHR from t_opr_bih_register where INPATIENTID_CHR = @inpatientid and INPATIENT_DAT = @inpatientdate
----------------------------------------------------------------------
if(@registerID is not null)
begin
select top 1 @t_deptid=DEPTID_CHR,@t_areaid=AREAID_CHR,@t_bedid=BEDID_CHR from T_OPR_BIH_REGISTER
where REGISTERID_CHR = @registerID --order by MODIFY_DAT desc
if(rtrim(@t_deptid) <> rtrim(@deptid) or rtrim(@t_areaid) <>rtrim(@areaid) or rtrim(@t_bedid) <>rtrim(@e_bedid))
begin
update T_OPR_BIH_REGISTER set MODIFY_DAT=getdate(),PSTATUS_INT=@pstatus,DEPTID_CHR=@deptid,AREAID_CHR=@areaid, BEDID_CHR=@bedid
where REGISTERID_CHR=@registerID
select top 1 @transferID=TRANSFERID_CHR,@category=TYPE_INT from T_OPR_BIH_TRANSFER where REGISTERID_CHR=@registerID order by MODIFY_DAT desc
if(@transferID is not null and @category=6)
update T_OPR_BIH_TRANSFER set SOURCEDEPTID_CHR=@t_deptid,SOURCEAREAID_CHR=@t_areaid,SOURCEBEDID_CHR=@t_bedid,TARGETDEPTID_CHR=@deptid,TARGETAREAID_CHR=@areaid,TARGETBEDID_CHR=@bedid,TYPE_INT='5',
MODIFY_DAT = getdate() where TRANSFERID_CHR=@transferID
else if(@transferID is not null and @category=5)
begin select @transferID=max(cast(TRANSFERID_CHR as int))+1 from T_OPR_BIH_TRANSFER
insert into T_OPR_BIH_TRANSFER(TRANSFERID_CHR,SOURCEDEPTID_CHR,SOURCEAREAID_CHR,SOURCEBEDID_CHR,TARGETDEPTID_CHR,TARGETAREAID_CHR,TARGETBEDID_CHR,TYPE_INT,REGISTERID_CHR,MODIFY_DAT)
values(@transferID,@t_deptid,@t_areaid,@t_bedid,@deptid,@areaid,@bedid,'5',@registerID,getdate())
end
delete from T_OPR_BIH_LEAVE where REGISTERID_CHR=@registerID
if(@inbedenddate <> '1900-1-1') begin
--set @t_count=@t_count+1
select @transferID=max(cast(TRANSFERID_CHR as int))+1 from T_OPR_BIH_TRANSFER
insert into T_OPR_BIH_TRANSFER(TRANSFERID_CHR,SOURCEDEPTID_CHR,SOURCEAREAID_CHR,SOURCEBEDID_CHR,TYPE_INT,REGISTERID_CHR,MODIFY_DAT)
values(@transferID,@deptid,@areaid,@bedid,'6',@registerID,getdate())
select @leaveID=max(cast(LEAVEID_CHR as int))+1 from T_OPR_BIH_LEAVE
insert into T_OPR_BIH_LEAVE(LEAVEID_CHR,REGISTERID_CHR,TYPE_INT,OUTDEPTID_CHR,OUTAREAID_CHR,STATUS_INT,MODIFY_DAT,OUTBEDID_CHR,PSTATUS_INT)
values(@leaveID,@registerID,'3',@deptid,@areaid,'1',@inbedenddate,@bedid,'1')
end if(@bedid is not null)
begin
update T_BSE_BED set STATUS_INT = '2',SEX_INT=@bed_sex where BEDID_CHR=@bedid
end end
else
begin
update T_OPR_BIH_REGISTER set MODIFY_DAT=getdate(),PSTATUS_INT=@pstatus where REGISTERID_CHR=@registerID
delete from T_OPR_BIH_LEAVE where REGISTERID_CHR=@registerID
end end
else
-------------------------------------------------------------
begin
select @registerID= right('000000000000'+rtrim(max(cast(REGISTERID_CHR as int))+1),12) from T_OPR_BIH_REGISTER
select @patientID=PATIENTID_CHR from T_BSE_PATIENT where INPATIENTID_CHR = @inpatientid
if(@patientID is null)
return
begin
insert into T_OPR_BIH_REGISTER(REGISTERID_CHR,MODIFY_DAT,PATIENTID_CHR,INPATIENTID_CHR,INPATIENT_DAT,DEPTID_CHR,AREAID_CHR,BEDID_CHR,TYPE_INT,INPATIENTCOUNT_INT,STATE_INT,STATUS_INT,PSTATUS_INT,INAREADATE_DAT)
values(@registerID,getdate(),@patientID,rtrim(@inpatientid),@inpatientdate,@deptid,@areaid,@bedid,'1',@times,'3','1','1',@inpatientdate)
select @transferID=right('000000000000'+rtrim(max(cast(TRANSFERID_CHR as int))+1),12) from T_OPR_BIH_TRANSFER
insert into T_OPR_BIH_TRANSFER(TRANSFERID_CHR,TARGETDEPTID_CHR,TARGETAREAID_CHR,TARGETBEDID_CHR,TYPE_INT,REGISTERID_CHR,MODIFY_DAT)
values(@transferID,@deptid,@areaid,@bedid,'5',@registerID,getdate())
if(@bedid is not null)
begin
update T_BSE_BED set STATUS_INT = '2',SEX_INT=@bed_sex where BEDID_CHR=@bedid
end
end
end
--set @count = @count+1
--set @t_count=@t_count+1
fetch next from cur1222 into @inpatientid,@inpatientdate,@e_deptid,@e_areaid,@e_roomid,@e_bedid,@outDate
end
close cur1222
deallocate cur1222
declare @patientID char(10)
declare @registerID char(12)
declare @inpatientid varchar(12)
declare @inpatientdate datetime
declare @deptid char(10)
declare @areaid char(10)
declare @bedid char(10)
DECLARE @times tinyint declare @transferID char(12)
declare @targetdeptID char(18)
DECLARE @targetareaID char(18)
declare @targetbedID char(18) declare @bedCode char(10)
declare @bed_sex tinyint
declare @category varchar(10) declare @leaveID char(12)
declare @leave_type varchar(20)
declare @outDate datetime declare @e_deptid char(10)
declare @e_areaid char(10)
declare @e_roomid char(10)
declare @e_bedid char(10)
declare @pstatus tinyint --(1-in,3-out)
declare @inbedenddate datetime
declare @sex varchar(10) declare @t_deptid char(10)
declare @t_areaid char(10)
declare @t_bedid char(10) DECLARE @count int
DECLARE @t_count int set @count = 200
set @t_count = 200
declare cur1222 cursor for
select DISTINCT rtrim(a.InPatientID) as InPatientID,a.InPatientDate,rtrim(a.InDeptID) as InDeptID,rtrim(a.Area_ID) as Area_ID,rtrim(a.Room_ID),
rtrim(a.Bed_ID) as Bed_ID,a.InBedEndDate as InBedEndDate from InDeptInfo a inner join InPatientDateInfo b
on a.InPatientID = b.InPatientID and a.InPatientDate = b.InPatientDate where a.InBedEndDate='1900-1-1' --a.InPatientID ='D688302'
order by InPatientID,InBedEndDate
open cur1222
fetch next from cur1222 into @inpatientid,@inpatientdate,@e_deptid,@e_areaid,@e_roomid,@e_bedid,@inbedenddate
while(@@fetch_status=0)
begin
select @deptid=DEPTID_CHR from T_BSE_DEPTDESC where rtrim(CODE_VCHR) = @e_deptid
select @areaid=DEPTID_CHR from T_BSE_DEPTDESC where rtrim(CODE_VCHR) = @e_areaid select @bedCode=Bed_Name from InPatient_Bed_Desc where Bed_ID=@e_bedid select @bedid=BEDID_CHR from T_BSE_BED t where t.code_chr = bedCode and t.areaid_chr = @areaid if(@bedid is not null) begin
set @pstatus = '1'
end
else
set @pstatus = '0'
select @sex=Sex,@times=Times from patientbaseinfo where InPatientID=@inpatientid
if(@sex is not null)
begin
if(rtrim(@sex) = '男')
set @bed_sex='1'
else
set @bed_sex='2'
end
if(@times is null)
set @times='1'
select @registerID=REGISTERID_CHR from t_opr_bih_register where INPATIENTID_CHR = @inpatientid and INPATIENT_DAT = @inpatientdate
----------------------------------------------------------------------
if(@registerID is not null)
begin
select top 1 @t_deptid=DEPTID_CHR,@t_areaid=AREAID_CHR,@t_bedid=BEDID_CHR from T_OPR_BIH_REGISTER
where REGISTERID_CHR = @registerID --order by MODIFY_DAT desc
if(rtrim(@t_deptid) <> rtrim(@deptid) or rtrim(@t_areaid) <>rtrim(@areaid) or rtrim(@t_bedid) <>rtrim(@e_bedid))
begin
update T_OPR_BIH_REGISTER set MODIFY_DAT=getdate(),PSTATUS_INT=@pstatus,DEPTID_CHR=@deptid,AREAID_CHR=@areaid, BEDID_CHR=@bedid
where REGISTERID_CHR=@registerID
select top 1 @transferID=TRANSFERID_CHR,@category=TYPE_INT from T_OPR_BIH_TRANSFER where REGISTERID_CHR=@registerID order by MODIFY_DAT desc
if(@transferID is not null and @category=6)
update T_OPR_BIH_TRANSFER set SOURCEDEPTID_CHR=@t_deptid,SOURCEAREAID_CHR=@t_areaid,SOURCEBEDID_CHR=@t_bedid,TARGETDEPTID_CHR=@deptid,TARGETAREAID_CHR=@areaid,TARGETBEDID_CHR=@bedid,TYPE_INT='5',
MODIFY_DAT = getdate() where TRANSFERID_CHR=@transferID
else if(@transferID is not null and @category=5)
begin select @transferID=max(cast(TRANSFERID_CHR as int))+1 from T_OPR_BIH_TRANSFER
insert into T_OPR_BIH_TRANSFER(TRANSFERID_CHR,SOURCEDEPTID_CHR,SOURCEAREAID_CHR,SOURCEBEDID_CHR,TARGETDEPTID_CHR,TARGETAREAID_CHR,TARGETBEDID_CHR,TYPE_INT,REGISTERID_CHR,MODIFY_DAT)
values(@transferID,@t_deptid,@t_areaid,@t_bedid,@deptid,@areaid,@bedid,'5',@registerID,getdate())
end
delete from T_OPR_BIH_LEAVE where REGISTERID_CHR=@registerID
if(@inbedenddate <> '1900-1-1') begin
--set @t_count=@t_count+1
select @transferID=max(cast(TRANSFERID_CHR as int))+1 from T_OPR_BIH_TRANSFER
insert into T_OPR_BIH_TRANSFER(TRANSFERID_CHR,SOURCEDEPTID_CHR,SOURCEAREAID_CHR,SOURCEBEDID_CHR,TYPE_INT,REGISTERID_CHR,MODIFY_DAT)
values(@transferID,@deptid,@areaid,@bedid,'6',@registerID,getdate())
select @leaveID=max(cast(LEAVEID_CHR as int))+1 from T_OPR_BIH_LEAVE
insert into T_OPR_BIH_LEAVE(LEAVEID_CHR,REGISTERID_CHR,TYPE_INT,OUTDEPTID_CHR,OUTAREAID_CHR,STATUS_INT,MODIFY_DAT,OUTBEDID_CHR,PSTATUS_INT)
values(@leaveID,@registerID,'3',@deptid,@areaid,'1',@inbedenddate,@bedid,'1')
end if(@bedid is not null)
begin
update T_BSE_BED set STATUS_INT = '2',SEX_INT=@bed_sex where BEDID_CHR=@bedid
end end
else
begin
update T_OPR_BIH_REGISTER set MODIFY_DAT=getdate(),PSTATUS_INT=@pstatus where REGISTERID_CHR=@registerID
delete from T_OPR_BIH_LEAVE where REGISTERID_CHR=@registerID
end end
else
-------------------------------------------------------------
begin
select @registerID= right('000000000000'+rtrim(max(cast(REGISTERID_CHR as int))+1),12) from T_OPR_BIH_REGISTER
select @patientID=PATIENTID_CHR from T_BSE_PATIENT where INPATIENTID_CHR = @inpatientid
if(@patientID is null)
return
begin
insert into T_OPR_BIH_REGISTER(REGISTERID_CHR,MODIFY_DAT,PATIENTID_CHR,INPATIENTID_CHR,INPATIENT_DAT,DEPTID_CHR,AREAID_CHR,BEDID_CHR,TYPE_INT,INPATIENTCOUNT_INT,STATE_INT,STATUS_INT,PSTATUS_INT,INAREADATE_DAT)
values(@registerID,getdate(),@patientID,rtrim(@inpatientid),@inpatientdate,@deptid,@areaid,@bedid,'1',@times,'3','1','1',@inpatientdate)
select @transferID=right('000000000000'+rtrim(max(cast(TRANSFERID_CHR as int))+1),12) from T_OPR_BIH_TRANSFER
insert into T_OPR_BIH_TRANSFER(TRANSFERID_CHR,TARGETDEPTID_CHR,TARGETAREAID_CHR,TARGETBEDID_CHR,TYPE_INT,REGISTERID_CHR,MODIFY_DAT)
values(@transferID,@deptid,@areaid,@bedid,'5',@registerID,getdate())
if(@bedid is not null)
begin
update T_BSE_BED set STATUS_INT = '2',SEX_INT=@bed_sex where BEDID_CHR=@bedid
end
end
end
--set @count = @count+1
--set @t_count=@t_count+1
fetch next from cur1222 into @inpatientid,@inpatientdate,@e_deptid,@e_areaid,@e_roomid,@e_bedid,@outDate
end
close cur1222
deallocate cur1222
http://www.win-download.com/864_download-MSSQL-to-Oracle-1-0.htm
AS
patientID CHAR(10);
registerID CHAR(12);
inpatientid VARCHAR2(12);
inpatientdate TIMESTAMP;
deptid CHAR(10);
areaid CHAR(10);
bedid CHAR(10);
times SMALLINT;
transferID CHAR(12);
targetdeptID CHAR(18);
targetareaID CHAR(18);
targetbedID CHAR(18);
bedCode CHAR(10);
bed_sex SMALLINT;
category VARCHAR2(10);
leaveID CHAR(12);
leave_type VARCHAR2(20);
outDate TIMESTAMP;
e_deptid CHAR(10);
e_areaid CHAR(10);
e_roomid CHAR(10);
e_bedid CHAR(10);
pstatus SMALLINT;
--(1-in,3-out)
inbedenddate TIMESTAMP;
sex VARCHAR2(10);
t_deptid CHAR(10);
t_areaid CHAR(10);
t_bedid CHAR(10);
COUNT_ADV INT;
t_count INT;
CURSOR cur1222
IS
SELECT DISTINCT
RTRIM(a.InPatientID) as InPatientID,
a.InPatientDate,
RTRIM(a.InDeptID) as InDeptID,
RTRIM(a.Area_ID) as Area_ID,
RTRIM(a.Room_ID),
RTRIM(a.Bed_ID) as Bed_ID,
a.InBedEndDate as InBedEndDate
FROM InDeptInfo a,
InPatientDateInfo b
WHERE a.InPatientID = b.InPatientID
AND a.InPatientDate = b.InPatientDate
AND (a.InBedEndDate = '1900-1-1')
/*a.InPatientID ='D688302' */ ORDER BY InPatientID,
InBedEndDate
;
BEGIN
COUNT_ADV := 200;
t_count := 200;
OPEN cur1222;
FETCH cur1222 INTO inpatientid, inpatientdate, e_deptid, e_areaid, e_roomid, e_bedid, inbedenddate;
<< LABEL7 >> WHILE ( cur1222%FOUND )
LOOP
BEGIN
BEGIN FOR REC IN ( SELECT DEPTID_CHR FROM T_BSE_DEPTDESC
WHERE RTRIM(CODE_VCHR) = tran_Add_REGISTER_in.e_deptid)
LOOP
tran_Add_REGISTER_in.deptid := REC.DEPTID_CHR;
END LOOP;
END;
BEGIN FOR REC IN ( SELECT DEPTID_CHR FROM T_BSE_DEPTDESC
WHERE RTRIM(CODE_VCHR) = tran_Add_REGISTER_in.e_areaid)
LOOP
tran_Add_REGISTER_in.areaid := REC.DEPTID_CHR;
END LOOP;
END;
BEGIN FOR REC IN ( SELECT Bed_Name FROM InPatient_Bed_Desc
WHERE Bed_ID = tran_Add_REGISTER_in.e_bedid)
LOOP
tran_Add_REGISTER_in.bedCode := REC.Bed_Name;
END LOOP;
END;
BEGIN FOR REC IN ( SELECT BEDID_CHR FROM T_BSE_BED t
WHERE t.code_chr = bedCode
AND t.areaid_chr = tran_Add_REGISTER_in.areaid)
LOOP
tran_Add_REGISTER_in.bedid := REC.BEDID_CHR;
END LOOP;
END;
IF( bedid is not null) THEN
BEGIN
pstatus := '1';
NULL;
END;
ELSE
pstatus := '0';
NULL;
END IF;
--未完待续
Sex,
Times FROM patientbaseinfo
WHERE InPatientID = tran_Add_REGISTER_in.inpatientid)
LOOP
tran_Add_REGISTER_in.sex := REC.Sex;
tran_Add_REGISTER_in.times := REC.Times;
END LOOP;
END;
IF( sex is not null) THEN
BEGIN
IF( RTRIM(sex) = '男') THEN
bed_sex := '1';
NULL;
ELSE
bed_sex := '2';
NULL;
END IF;
END;
END IF;
IF( times is null) THEN
times := '1';
NULL;
END IF;
BEGIN FOR REC IN ( SELECT REGISTERID_CHR FROM t_opr_bih_register
WHERE INPATIENTID_CHR = tran_Add_REGISTER_in.inpatientid
AND INPATIENT_DAT = tran_Add_REGISTER_in.inpatientdate)
LOOP
tran_Add_REGISTER_in.registerID := REC.REGISTERID_CHR;
END LOOP;
END;
----------------------------------------------------------------------
IF( registerID is not null) THEN
BEGIN
BEGIN FOR REC IN ( SELECT
DEPTID_CHR,
AREAID_CHR,
BEDID_CHR FROM T_OPR_BIH_REGISTER
WHERE REGISTERID_CHR = tran_Add_REGISTER_in.registerID
AND ROWNUM < 2
)
LOOP
tran_Add_REGISTER_in.t_deptid := REC.DEPTID_CHR;
tran_Add_REGISTER_in.t_areaid := REC.AREAID_CHR;
tran_Add_REGISTER_in.t_bedid := REC.BEDID_CHR;
END LOOP;
END;
--order by MODIFY_DAT desc
IF( RTRIM(t_deptid) <> RTRIM(deptid) or RTRIM(t_areaid) <> RTRIM(areaid) or RTRIM(t_bedid) <> RTRIM(e_bedid)) THEN
BEGIN
UPDATE T_OPR_BIH_REGISTER
SET MODIFY_DAT = (SYSDATE),
PSTATUS_INT = tran_Add_REGISTER_in.pstatus,
DEPTID_CHR = tran_Add_REGISTER_in.deptid,
AREAID_CHR = tran_Add_REGISTER_in.areaid,
BEDID_CHR = tran_Add_REGISTER_in.bedid
WHERE REGISTERID_CHR = tran_Add_REGISTER_in.registerID;
BEGIN FOR REC IN ( SELECT
TRANSFERID_CHR,
TYPE_INT FROM
(SELECT
TRANSFERID_CHR,
TYPE_INT
FROM T_OPR_BIH_TRANSFER
WHERE REGISTERID_CHR = tran_Add_REGISTER_in.registerID
ORDER BY MODIFY_DAT DESC
)WHERE ROWNUM < 2
)
LOOP
tran_Add_REGISTER_in.transferID := REC.TRANSFERID_CHR;
tran_Add_REGISTER_in.category := REC.TYPE_INT;
END LOOP;
END;
IF( transferID is not null and category = 6) THEN
update T_OPR_BIH_TRANSFER set SOURCEDEPTID_CHR = @t_deptid , SOURCEAREAID_CHR = @t_areaid , SOURCEBEDID_CHR = @t_bedid , TARGETDEPTID_CHR = @deptid , TARGETAREAID_CHR = @are aid , TARGETBEDID_CHR = @bedid , TYPE_INT = '5' , MODIFY_DAT = getdate () where TRANSFERID_CHR = @transferID
/* AdventNet SwisSQL MESSAGE : Above statement could not be parsed by SwisSQL - SQLOne API Parser */;
ELSE
IF( transferID is not null and category = 5) THEN
BEGIN
BEGIN FOR REC IN ( SELECT MAX(CAST(TRANSFERID_CHR as int)) + 1 TEMP_ALIAS_0 FROM T_OPR_BIH_TRANSFER
)
LOOP
tran_Add_REGISTER_in.transferID := REC.TEMP_ALIAS_0;
END LOOP;
END;
insert into T_OPR_BIH_TRANSFER ( TRANSFERID_CHR , SOURCEDEPTID_CHR , SOURCEAREAID_CHR , SOURCEBEDID_CHR , TARGETDEPTID_CHR , TARGETAREAID_CHR , TARGETB EDID_CHR , TYPE_INT , REGISTERID_CHR , MODIFY_DAT) values ( @transferID , @t_deptid , @t_areaid , @t_bedid , @deptid , @areaid , @bedid , '5' , @registerID , getdate ())
/* AdventNet SwisSQL MESSAGE : Above statement could not be parsed by SwisSQL - SQLOne API Parser */;
END;
END IF;
END IF;
DELETE FROM T_OPR_BIH_LEAVE
WHERE REGISTERID_CHR = tran_Add_REGISTER_in.registerID;
IF( inbedenddate <> '1900-1-1') THEN
BEGIN
/*set @t_count=@t_count+1*/ BEGIN FOR REC IN ( SELECT MAX(CAST(TRANSFERID_CHR as int)) + 1 TEMP_ALIAS_0 FROM T_OPR_BIH_TRANSFER
)
LOOP
tran_Add_REGISTER_in.transferID := REC.TEMP_ALIAS_0;
END LOOP;
END;
INSERT INTO T_OPR_BIH_TRANSFER
( TRANSFERID_CHR ,
SOURCEDEPTID_CHR ,
SOURCEAREAID_CHR ,
SOURCEBEDID_CHR ,
TYPE_INT ,
REGISTERID_CHR ,
MODIFY_DAT )
VALUES ( tran_Add_REGISTER_in.transferID ,
tran_Add_REGISTER_in.deptid ,
tran_Add_REGISTER_in.areaid ,
tran_Add_REGISTER_in.bedid ,
'6' ,
tran_Add_REGISTER_in.registerID ,
(SYSDATE) );
BEGIN FOR REC IN ( SELECT MAX(CAST(LEAVEID_CHR as int)) + 1 TEMP_ALIAS_0 FROM T_OPR_BIH_LEAVE
)
LOOP
tran_Add_REGISTER_in.leaveID := REC.TEMP_ALIAS_0;
END LOOP;
END;
insert into T_OPR_BIH_LEAVE ( LEAVEID_CHR , REGISTERID_CHR , TYPE_INT , OUTDEPTID_CHR , OUTAREAID_CHR , STATUS_INT , MODIFY_DAT , OUTBEDID_CHR , PSTATUS_IN T) values ( @leaveID , @registerID , '3' , @deptid , @areaid , '1' , @inbedenddate , @bedid , '1' )
/* AdventNet SwisSQL MESSAGE : Above statement could not be parsed by SwisSQL - SQLOne API Parser */;
END;
END IF;
IF( bedid is not null) THEN
BEGIN
UPDATE T_BSE_BED
SET STATUS_INT = '2',
SEX_INT = tran_Add_REGISTER_in.bed_sex
WHERE BEDID_CHR = tran_Add_REGISTER_in.bedid;
END;
END IF;
END;
--未完待续
BEGIN
UPDATE T_OPR_BIH_REGISTER
SET MODIFY_DAT = (SYSDATE),
PSTATUS_INT = tran_Add_REGISTER_in.pstatus
WHERE REGISTERID_CHR = tran_Add_REGISTER_in.registerID;
DELETE FROM T_OPR_BIH_LEAVE
WHERE REGISTERID_CHR = tran_Add_REGISTER_in.registerID;
END;
END IF;
END;
ELSE
-------------------------------------------------------------
BEGIN
BEGIN FOR REC IN ( SELECT SUBSTR('000000000000' || RTRIM(MAX(CAST(REGISTERID_CHR as int)) + 1), LENGTH('000000000000' || RTRIM(MAX(CAST(REGISTERID_CHR as int)) + 1)) - 12 +1, 12) TEMP_ALIAS_0 FROM T_OPR_BIH_REGISTER
)
LOOP
tran_Add_REGISTER_in.registerID := REC.TEMP_ALIAS_0;
END LOOP;
END;
BEGIN FOR REC IN ( SELECT PATIENTID_CHR FROM T_BSE_PATIENT
WHERE INPATIENTID_CHR = tran_Add_REGISTER_in.inpatientid
)
LOOP
tran_Add_REGISTER_in.patientID := REC.PATIENTID_CHR;
END LOOP;
END;
IF( patientID is null) THEN
return;
END IF;
BEGIN
insert into T_OPR_BIH_REGISTER ( REGISTERID_CHR , MODIFY_DAT , PATIENTID_CHR , INPATIENTID_CHR , INPATIENT_DAT , DEPTID_CHR , AREAID_CHR , BEDID_CHR , TYPE _INT , INPATIENTCOUNT_INT , STATE_INT , STATUS_INT , PSTATUS_INT , INAREADATE_DAT) values ( @registerID , getdate () , @patientID , rtrim ( @inpatientid) , @inpatientdate , @deptid , @areaid , @bedid , '1' , @times , '3' , '1' , '1' , @inp atientdate)
/* AdventNet SwisSQL MESSAGE : Above statement could not be parsed by SwisSQL - SQLOne API Parser */;
BEGIN FOR REC IN ( SELECT SUBSTR('000000000000' || RTRIM(MAX(CAST(TRANSFERID_CHR as int)) + 1), LENGTH('000000000000' || RTRIM(MAX(CAST(TRANSFERID_CHR as int)) + 1)) - 12 +1, 12) TEMP_ALIAS_0 FROM T_OPR_BIH_TRANSFER
)
LOOP
tran_Add_REGISTER_in.transferID := REC.TEMP_ALIAS_0;
END LOOP;
END;
INSERT INTO T_OPR_BIH_TRANSFER
( TRANSFERID_CHR ,
TARGETDEPTID_CHR ,
TARGETAREAID_CHR ,
TARGETBEDID_CHR ,
TYPE_INT ,
REGISTERID_CHR ,
MODIFY_DAT )
VALUES ( tran_Add_REGISTER_in.transferID ,
tran_Add_REGISTER_in.deptid ,
tran_Add_REGISTER_in.areaid ,
tran_Add_REGISTER_in.bedid ,
'5' ,
tran_Add_REGISTER_in.registerID ,
(SYSDATE) );
IF( bedid is not null) THEN
BEGIN
UPDATE T_BSE_BED
SET STATUS_INT = '2',
SEX_INT = tran_Add_REGISTER_in.bed_sex
WHERE BEDID_CHR = tran_Add_REGISTER_in.bedid;
END;
END IF;
END;
END;
END IF;
--set @count = @count+1
--set @t_count=@t_count+1
FETCH cur1222 INTO inpatientid, inpatientdate, e_deptid, e_areaid, e_roomid, e_bedid, outDate;
END;
END LOOP;
CLOSE cur1222;
END;
--已完