DECLARE @幚巤婜娫fr datetime
DECLARE @幚巤婜娫to datetime
DECLARE @媥柊懳徾擔 datetime
DECLARE @PR棜楌No int
DECLARE @彜昳No int
DECLARE @屭媞No int
DECLARE @搊榐僗僞僢僼No int

SET @PR棜楌No = 1
SET @彜昳No = 1
SET @屭媞No = 1
SET @搊榐僗僞僢僼No = 1
SET @幚巤婜娫fr = '2002/1/11'
SET @幚巤婜娫to = '2003/7/11'
SET @媥柊懳徾擔 = '2001/7/15'



---- Fill
--SELECT
--	 H10僾儘儌乕僔儑儞梊掕_懳徾屭媞.PR棜楌No
--	,H10僾儘儌乕僔儑儞梊掕_懳徾屭媞.屭媞No
--	,M03屭媞.屭媞ID
--	,M03屭媞.巵柤
--	,M03屭媞.巵柤僇僫
--	,M01屌掕懏惈.抣 AS 惈暿
--	,M03屭媞.惗擭寧擔
--	,M03屭媞.梄曋斣崋
--	,M03屭媞.廧強
--	,M03屭媞.TEL
--	,M03屭媞.儊乕儖傾僪儗僗
--FROM
--	M01屌掕懏惈 RIGHT OUTER JOIN
--	M03屭媞 ON M01屌掕懏惈.屌掕懏惈No = M03屭媞.惈暿 RIGHT OUTER JOIN
--	H10僾儘儌乕僔儑儞梊掕_懳徾屭媞 ON M03屭媞.屭媞No = H10僾儘儌乕僔儑儞梊掕_懳徾屭媞.屭媞No
--WHERE
--	H10僾儘儌乕僔儑儞梊掕_懳徾屭媞.慻怐No = @慻怐No AND
--	H10僾儘儌乕僔儑儞梊掕_懳徾屭媞.PR棜楌No = @PR棜楌No
SELECT
	H10僾儘儌乕僔儑儞梊掕_懳徾屭媞.僾儘儌乕僔儑儞梊掕_懳徾屭媞No, 
	H10僾儘儌乕僔儑儞梊掕_懳徾屭媞.PR棜楌No, 
	H10僾儘儌乕僔儑儞梊掕_懳徾屭媞.屭媞No,
	M03屭媞.屭媞ID,
	M03屭媞.巵柤, 
	M03屭媞.巵柤僇僫,
	M04懏惈.抣 AS 惈暿,
	M03屭媞.惗擭寧擔, 
	M03屭媞.梄曋斣崋,
	M03屭媞.廧強,
	M03屭媞.TEL, 
	M03屭媞.儊乕儖傾僪儗僗
FROM
	M04懏惈 RIGHT OUTER JOIN
	M03屭媞 ON M04懏惈.懏惈No = M03屭媞.惈暿 RIGHT OUTER JOIN
	H10僾儘儌乕僔儑儞梊掕_懳徾屭媞 ON M03屭媞.屭媞No = H10僾儘儌乕僔儑儞梊掕_懳徾屭媞.屭媞No
WHERE
	H10僾儘儌乕僔儑儞梊掕_懳徾屭媞.PR棜楌No = @PR棜楌No


---- DeleteByPR棜楌No
--DELETE FROM
--	H10僾儘儌乕僔儑儞梊掕_懳徾屭媞
--WHERE
--	慻怐No = @慻怐No AND
--	PR棜楌No = @PR棜楌No
DELETE FROM
	H10僾儘儌乕僔儑儞梊掕_懳徾屭媞
WHERE
	PR棜楌No = @PR棜楌No



---- InsertCommand
--INSERT INTO H10僾儘儌乕僔儑儞梊掕_懳徾屭媞
--(
--	 慻怐No
--	,PR棜楌No
--	,屭媞No
--	,搊榐儐乕僓乕No
--	,搊榐擔帪
--	,峏怴儐乕僓乕No
--	,峏怴擔帪
--)VALUES(
--	 @慻怐No
--	,@PR棜楌No
--	,@屭媞No
--	,@搊榐儐乕僓乕No
--	,getdate()
--	,@搊榐儐乕僓乕No
--	,getdate()
--)
INSERT INTO H10僾儘儌乕僔儑儞梊掕_懳徾屭媞
(
	 PR棜楌No
	,屭媞No
	,搊榐僗僞僢僼No
	,搊榐擔帪
	,峏怴僗僞僢僼No
	,峏怴擔帪
)VALUES(
	 @PR棜楌No
	,@屭媞No
	,@搊榐僗僞僢僼No
	,getdate()
	,@搊榐僗僞僢僼No
	,getdate()
)
