/*声名用于添加记录的变量*/
DECLARE @YNum Integer
DECLARE @YMoney Numeric(18, 2)
DECLARE @YP_Bh VarChar(10)
DECLARE @YP_PDH VarChar(20)
DEClARE @YP_Class Char (1)
DECLARE @YP_Name VarChar(60)
DECLARE @YP_Size VarChar(20)
DECLARE @YP_Unit VarChar(20)
DECLARE @YP_JX VarChar(8)
DECLARE @YP_JFDJ money
DECLARE @YP_JFSL integer
DECLARE @YP_JFJE Numeric(18, 2)
DECLARE @YP_KFBH Char(3)
DECLARE @YP_KFName VarChar(16)
/*得到余金额、余数量,在每一次取药品的数量帐信息时打开一次*/
DECLARE RestMoneyNUM_cursor SCROLL CURSOR FOR
SELECT YP_YSL, YP_YJE FROM YP_YKList
WHERE (YP_KFBH = '11') AND
(YP_Class = '1') AND
(YP_Bh = @YP_BH) --此处的变量老是传不过去。
ORDER BY JZ_XH DESC
/*得到要添加的所有旧药品的流水帐*/
DECLARE AppendSource_cursor SCROLL CURSOR FOR
SELECT YP_BH, YP_RPDH AS YP_PDH, YP_Name, YP_Size, YP_Unit,
YP_JX, YP_PFJ AS YP_JFDJ, YP_RSL AS YP_JFSL, YP_RJE AS YP_JFJE
FROM YP_YKIn
WHERE (YP_RPDH IN('XRK11_010012')) AND
(YP_BH IN(SELECT YP_BH FROM YP_DrugDict))
GROUP BY YP_BH, YP_RPDH, YP_Name, YP_Size, YP_Unit, YP_JX, YP_PFJ,
YP_RSL, YP_RJE
OPEN AppendSource_cursor
/*开始添加药品流水帐*/
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM AppendSource_cursor
INTO @YP_BH, @YP_PDH, @YP_Name, @YP_Size, @YP_Unit,
@YP_JX, @YP_JFDJ, @YP_JFSL, @YP_JFJE
/*取出此药品的余金额,余数量*/
OPEN RestMoneyNUM_cursor
FETCH FIRST FROM RestMoneyNUM_cursor INTO @YNum, @YMoney
CLOSE RestMoneyNUM_cursor
/*添加一个药品的数量帐信息*/
INSERT INTO YP_YKList (YP_KFBH, YP_KFName, YP_Class, YP_PDH,YP_BH,
YP_Name, YP_Size, YP_Unit, YP_JX, YP_JFDJ, YP_JFSL,YP_JFJE, YP_YSL,
YP_YJE)VALUES('11', '西药库
', '1', @YP_PDH, @YP_BH, @YP_Name,
@YP_Size, @YP_Unit, @YP_JX, @YP_JFDJ, @YP_JFSL, @YP_JFJE,
@YP_JFSL + @YNum, @YP_JFJE + @YMoney)
END
DEALLOCATE RestMoneyNUM_cursor
CLOSE AppendSource_cursor
DEALLOCATE AppendSource_cursor