quote:

---

USE [db_1]
GO
/****** Object: StoredProcedure [dbo].[Central] Script Date: 06/14/2010 13:24:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Central]
@key varchar(7),
@pid varchar(7),
@sit varchar(25),
@epi varchar(7),
@adm date,
@dis date,
@idr varchar(16)
AS
BEGIN
Set NoCount On
UPDATE Central
SET "Direct_Costs" =
(CASE WHEN (select StkTotalC from ProcedureConsCost where Procedure_ID = @pid and Status = 'P') is NULL then '0' ELSE (select StkTotalC from ProcedureConsCost where Procedure_ID = @pid and Status = 'P') END +
CASE WHEN (select StkTotalC from ProcedureConsCost where Procedure_ID = @pid and Status = 'A') is NULL then '0' ELSE (select StkTotalC from ProcedureConsCost where Procedure_ID = @pid and Status = 'A') END)
WHERE Patient_ID = @key and Episode_ID = @epi
IF @@ROWCOUNT=0

--Sets the core data about the patient record.
INSERT INTO db_1.dbo.Central
SELECT DISTINCT
@key,@epi,@sit,@adm,@dis,
"Direct_Costs" =
CASE WHEN (select StkTotalC from ProcedureConsCost where Procedure_ID = @pid and Status = 'P') is NULL then '0' ELSE (select StkTotalC from ProcedureConsCost where Procedure_ID = @pid and Status = 'P') END +
CASE WHEN (select StkTotalC from ProcedureConsCost where Procedure_ID = @pid and Status = 'A') is NULL then '0' ELSE (select StkTotalC from ProcedureConsCost where Procedure_ID = @pid and Status = 'A') END,
'' as "Indirect_Costs",'' as "Fees_Charged",
'' as "Total_Cost",@idr as "ID"
FROM db_1.dbo.ProcedureConsCost

--Updates Indirect_Costs
UPDATE Central
SET "Indirect_Costs" =
(SELECT Indirect_Costs = COALESCE(SUM(CASE WHEN b.Department IN ('IMG','PAT','PHY') THEN Cost_Per_unit ELSE 0 END) + (select SUM(b.Cost_Per_Unit) from IndirectCosts b where b.Apportionment = 'PDE'), 0)
FROM db_1.dbo.IndirectCosts b
WHERE Episode_ID = @epi)
WHERE Episode_ID = @epi and Patient_ID = @key

--Updates the Total Cost
UPDATE Central
SET Total_Cost = Direct_Costs + Indirect_Costs
WHERE Episode_ID = @epi and Patient_ID = @key

--Updates to say if invoiced.
UPDATE Central
SET Fees_Charged =
CASE WHEN (SELECT CASE WHEN CASE WHEN LEFT(invoice,1) = 'I' THEN [gross_amt] ELSE (gross_amt - gross_amt - gross_amt) END > '0' THEN 'Yes' ELSE 'No' END
FROM db_2.dbo.invoices WHERE episode = @epi AND LEFT(item,3) = 'HRG' AND @sit = 'Claremont Hospital'
UNION ALL
SELECT CASE WHEN CASE WHEN LEFT(invoice,1) = 'I' THEN [gross_amt] ELSE (gross_amt - gross_amt - gross_amt) END > '0' THEN 'Yes' ELSE 'No' END
FROM db_2.dbo.invoices WHERE episode = @epi AND LEFT(item,3) = 'HRG' AND @sit = 'site2') IS NULL THEN 'No' ELSE 'Yes' END
Where Episode_ID = @epi AND Patient_ID = @key
END

---

UPDATE		c
SET		c.[Direct_Costs] = COALESCE(pcc.x, 0)
FROM		dbo.Central AS c
CROSS JOIN	(
			SELECT	SUM(StkTotalC) AS x
			FROM	dbo.ProcedureConsCost
			WHERE	[Status] IN ('A', 'P')
				AND Procedure_ID = @pid
		) AS pcc
WHERE		c.[Patient_ID] = @key
		AND c.[Episode_ID] = @epi