select h.姓名 患者姓名,d.编码 药品代码,d.名称 药品名称,h.NO 处方号,a.审核日期 发药日期,a.已发数量 数量,a.零售价 销售单价, a.零售金额 收费金额,d.门诊单位 计价单位,
(select 名称 from 部门表 where id=a.对方部门ID) 领药科室,d.规格,d.产地 生产商,decode(h.收费类别, 5, '西药', 6, '成药', 7, '草药') 药品大类 FROM (
SELECT * FROM (
SELECT A.ID, A.NO,A.单据,A.药品ID, A.费用ID,A.批次,A.批号,A.效期,NVL(A.付数, 1) 付数,A.实际数量,NVL(A.付数, 1) * A.实际数量 - B.已发数量 已退数量,
B.已发数量,A.记录状态,A.发药窗口,A.零售价, round(a.零售价 * Nvl(a.付数, 1) * a.实际数量, 2) 零售金额, A.单量,A.频次,
A.用法,A.摘要,A.审核人,A.审核日期,A.对方部门ID,A.库房ID,1 可操作,A.填制人,A.处方类型, A.未取药
FROM (
SELECT A.ID, A.NO,a.单据,A.药品ID, A.序号, A.费用ID, A.批次, A.批号, A.效期, A.付数,A.实际数量,A.记录状态, A.发药窗口, A.零售价,
A.零售金额, A.单量,A.频次, A.用法,A.摘要, A.审核人, A.审核日期, A.对方部门ID, A.库房ID, A.填制人,Nvl(A.注册证号, 0) As 处方类型, Nvl(A.是否未取药, 0) As 未取药
FROM 药品收发记录 A
WHERE nvl(a.发药方式, -999) <> -1
and A.审核人 IS NOT NULL
AND (A.记录状态 = 1 OR MOD(A.记录状态, 3) = 0)
And A.审核日期 Between /*B0*/TO_DATE('2018-10-31 00:00:00','YYYY-MM-DD HH24:MI:SS')/*E0*/ and /*B1*/TO_DATE('2023-10-31 23:59:59','YYYY-MM-DD HH24:MI:SS')/*E1*/
And A.单据 In (8, 9)
And Not Exists (Select 1 From 输液配药内容 Y, 药品收发记录 Z Where y.收发id = Z.Id AND Z.NO = A.No And z.单据 = a.单据 And z.库房id = a.库房id)
) A,
(SELECT A.NO, A.单据, A.药品ID,A.序号, SUM(NVL(A.付数, 1) * A.实际数量) 已发数量 FROM 药品收发记录 A
WHERE nvl(a.发药方式, -999) <> -1
and A.审核人 IS NOT NULL
And A.单据 In (8, 9)
And A.审核日期 Between /*B0*/TO_DATE('2018-10-31 00:00:00','YYYY-MM-DD HH24:MI:SS')/*E0*/ and /*B1*/TO_DATE('2023-10-31 23:59:59','YYYY-MM-DD HH24:MI:SS')/*E1*/
GROUP BY A.NO, A.单据, A.药品ID, A.序号
) B
WHERE A.NO = B.NO
AND A.单据 = B.单据
AND A.药品ID + 0 = B.药品ID
AND A.序号 = B.序号
)
) A,
病人费用记录 H,
病人信息 B,部门表 c,药品目录 d
WHERE a.药品id=d.药品id
And (A.单据 = 8 Or A.单据 = 9)
AND H.病人ID = B.病人ID(+)
AND A.审核人 IS NOT NULL
AND A.费用ID = H.ID
And (H.门诊标志 = 1 or H.门诊标志 = 4)
and a.对方部门id=c.id
and h.收费类别 in ('5','6','7')
门诊药品收入明细
于 2023-10-31 15:34:54 首次发布