DECLARE @幚巤婜娫fr datetime
DECLARE @幚巤婜娫to datetime
DECLARE @媥柊懳徾擔 datetime
DECLARE @PR棜楌No int
DECLARE @彜昳No int
DECLARE @屭媞No int
DECLARE @搊榐僗僞僢僼No int
DECLARE @僾儘儌乕僔儑儞梊掕No int
DECLARE @僾儘儌乕僔儑儞幚愌No int
DECLARE @PR嬫暘No int
DECLARE @岞奐梊掕擔 datetime
DECLARE @岞奐幚巤擔 datetime
DECLARE @梊掕婜娫fr datetime
DECLARE @梊掕婜娫to datetime
DECLARE @岞奐梊掕擔fr datetime
DECLARE @岞奐梊掕擔to datetime
DECLARE @岞奐幚巤擔fr datetime
DECLARE @岞奐幚巤擔to datetime
DECLARE @憤旓梡 int
DECLARE @懳徾彜昳憤悢 int
DECLARE @懳徾婛懚屭媞憤悢 int
DECLARE @桳岠擔悢撪偺憤攧忋 int
DECLARE @怴婯屭媞妉摼悢 int
DECLARE @尒崬屭媞妉摼悢 int
DECLARE @孈婲偟屭媞悢 int
DECLARE @PR柤 varchar
DECLARE @撪梕 varchar
DECLARE @旛峫 varchar
DECLARE @峏怴僗僞僢僼No int

SET @PR棜楌No = 1
SET @彜昳No = 1
SET @屭媞No = 1
SET @搊榐僗僞僢僼No = 1
SET @岞奐梊掕擔 = '2002/1/11'
SET @岞奐幚巤擔 = '2002/1/11'
SET @幚巤婜娫fr = '2002/1/11'
SET @幚巤婜娫to = '2003/7/11'
SET @媥柊懳徾擔 = '2001/7/15'
SET @梊掕婜娫fr = '2001/7/15'
SET @梊掕婜娫to = '2001/7/15'
SET @僾儘儌乕僔儑儞梊掕No = 1
SET @僾儘儌乕僔儑儞幚愌No = 1
SET @PR嬫暘No = 1
SET @岞奐梊掕擔fr = '2002/1/11'
SET @岞奐梊掕擔to = '2002/1/11'
SET @岞奐幚巤擔fr = '2002/1/11'
SET @岞奐幚巤擔to = '2002/1/11'
SET @憤旓梡 = 1
SET @懳徾彜昳憤悢 = 1
SET @懳徾婛懚屭媞憤悢 = 1
SET @桳岠擔悢撪偺憤攧忋 = 1
SET @怴婯屭媞妉摼悢 = 1
SET @尒崬屭媞妉摼悢 = 1
SET @孈婲偟屭媞悢 = 1
SET @PR柤 = 'a'
SET @撪梕 = 'a'
SET @旛峫 = 'a'
SET @峏怴僗僞僢僼No = 1



---- Fill
--SELECT
--	 0 AS 怴婯
--	,0 AS 曄峏
--	,0 AS 嶍彍
--	,H06PR棜楌.PR棜楌No
--	,H06PR棜楌.PR嬫暘No
--	,H06PR棜楌.PR柤
--	,H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔
--	,H11僾儘儌乕僔儑儞幚愌.岞奐幚巤擔
--	,H06PR棜楌.搊榐儐乕僓乕No
--	,H06PR棜楌.搊榐擔帪
--	,H06PR棜楌.峏怴儐乕僓乕No
--	,H06PR棜楌.峏怴擔帪
--FROM
--	H06PR棜楌 LEFT OUTER JOIN
--	H07僾儘儌乕僔儑儞梊掕 ON H06PR棜楌.PR棜楌No = H07僾儘儌乕僔儑儞梊掕.PR棜楌No LEFT OUTER JOIN
--	H11僾儘儌乕僔儑儞幚愌 ON H06PR棜楌.PR棜楌No = H11僾儘儌乕僔儑儞幚愌.PR棜楌No
--WHERE
--	H06PR棜楌.慻怐No = @慻怐No
SELECT
	 0 AS 怴婯
	,0 AS 曄峏
	,0 AS 嶍彍
	,H06PR棜楌.PR棜楌No
	,H06PR棜楌.PR嬫暘No
	,H06PR棜楌.PR柤
	,H07僾儘儌乕僔儑儞梊掕.僾儘儌乕僔儑儞梊掕No
	,H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔
	,H11僾儘儌乕僔儑儞幚愌.僾儘儌乕僔儑儞幚愌No
	,H11僾儘儌乕僔儑儞幚愌.岞奐幚巤擔
	,H06PR棜楌.搊榐僗僞僢僼No
	,H06PR棜楌.搊榐擔帪
	,H06PR棜楌.峏怴僗僞僢僼No
	,H06PR棜楌.峏怴擔帪
FROM
	H06PR棜楌 LEFT OUTER JOIN
	H07僾儘儌乕僔儑儞梊掕 ON H06PR棜楌.PR棜楌No = H07僾儘儌乕僔儑儞梊掕.PR棜楌No LEFT OUTER JOIN
	H11僾儘儌乕僔儑儞幚愌 ON H06PR棜楌.PR棜楌No = H11僾儘儌乕僔儑儞幚愌.PR棜楌No


---- DeletePR棜楌
--Delete From
--	H06PR棜楌
--WHERE
--	慻怐No = @慻怐No AND
--	PR棜楌No = @PR棜楌No
Delete From
	H06PR棜楌
WHERE
	PR棜楌No = @PR棜楌No


---- Delete僾儘儌乕僔儑儞幚愌
--Delete From
--	H11僾儘儌乕僔儑儞幚愌
--WHERE
--	慻怐No = @慻怐No AND
--	僾儘儌乕僔儑儞幚愌No = @僾儘儌乕僔儑儞幚愌No
Delete From
	H11僾儘儌乕僔儑儞幚愌
WHERE
	僾儘儌乕僔儑儞幚愌No = @僾儘儌乕僔儑儞幚愌No


---- Delete僾儘儌乕僔儑儞梊掕
--Delete From
--	H07僾儘儌乕僔儑儞梊掕
--WHERE
--	慻怐No = @慻怐No AND
--	僾儘儌乕僔儑儞梊掕No = @僾儘儌乕僔儑儞梊掕No
Delete From
	H07僾儘儌乕僔儑儞梊掕
WHERE
	僾儘儌乕僔儑儞梊掕No = @僾儘儌乕僔儑儞梊掕No


---- FillBy専嶕
--SELECT
--	 0 AS 怴婯
--	,0 AS 曄峏
--	,0 AS 嶍彍
--	,H06PR棜楌.PR棜楌No
--	,H06PR棜楌.PR嬫暘No
--	,H06PR棜楌.PR柤
--	,H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔
--	,H11僾儘儌乕僔儑儞幚愌.岞奐幚巤擔
--	,H06PR棜楌.搊榐儐乕僓乕No
--	,H06PR棜楌.搊榐擔帪
--	,H06PR棜楌.峏怴儐乕僓乕No
--	,H06PR棜楌.峏怴擔帪
--FROM
--	H06PR棜楌 LEFT OUTER JOIN
--	H07僾儘儌乕僔儑儞梊掕 ON H06PR棜楌.PR棜楌No = H07僾儘儌乕僔儑儞梊掕.PR棜楌No LEFT OUTER JOIN
--	H11僾儘儌乕僔儑儞幚愌 ON H06PR棜楌.PR棜楌No = H11僾儘儌乕僔儑儞幚愌.PR棜楌No
--WHERE
--	H06PR棜楌.慻怐No = @慻怐No AND
--	((@PR嬫暘No IS NULL) OR (PR嬫暘No = @PR嬫暘No)) AND 
--	((@PR柤 IS NULL) OR (PR柤 LIKE '%' + @PR柤 + '%')) AND 
--	(H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔 >= @岞奐梊掕擔fr) AND 
--	(H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔 <= @岞奐梊掕擔to) AND 
--	((@岞奐幚巤擔fr IS NULL) OR (H11僾儘儌乕僔儑儞幚愌.岞奐幚巤擔 >= @岞奐幚巤擔fr)) AND 
--	((@岞奐幚巤擔to IS NULL) OR (H11僾儘儌乕僔儑儞幚愌.岞奐幚巤擔 <= @岞奐幚巤擔to))
SELECT
	0 AS 怴婯,
	0 AS 曄峏,
	0 AS 嶍彍,
	H06PR棜楌.PR棜楌No,
	H06PR棜楌.PR嬫暘No,
	H06PR棜楌.PR柤,
	H07僾儘儌乕僔儑儞梊掕.僾儘儌乕僔儑儞梊掕No, 
	H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔, 
	H11僾儘儌乕僔儑儞幚愌.僾儘儌乕僔儑儞幚愌No, 
	H11僾儘儌乕僔儑儞幚愌.岞奐幚巤擔, H06PR棜楌.搊榐僗僞僢僼No, 
	H06PR棜楌.搊榐擔帪,
	H06PR棜楌.峏怴僗僞僢僼No,
	H06PR棜楌.峏怴擔帪
FROM
	H06PR棜楌 LEFT OUTER JOIN
	H07僾儘儌乕僔儑儞梊掕 ON H06PR棜楌.PR棜楌No = H07僾儘儌乕僔儑儞梊掕.PR棜楌No LEFT OUTER JOIN
	H11僾儘儌乕僔儑儞幚愌 ON H06PR棜楌.PR棜楌No = H11僾儘儌乕僔儑儞幚愌.PR棜楌No
WHERE
	(@PR嬫暘No IS NULL OR @PR嬫暘No IS NOT NULL AND H06PR棜楌.PR嬫暘No = @PR嬫暘No) AND 
	(@PR柤 IS NULL OR @PR柤 IS NOT NULL AND H06PR棜楌.PR柤 LIKE '%' + @PR柤 + '%') AND 
	(H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔 >= @岞奐梊掕擔fr) AND 
	(H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔 <= @岞奐梊掕擔to) AND 
	(@岞奐幚巤擔fr IS NULL) AND (@岞奐幚巤擔to IS NULL) OR
	(@PR嬫暘No IS NULL OR @PR嬫暘No IS NOT NULL AND H06PR棜楌.PR嬫暘No = @PR嬫暘No) AND 
	(@PR柤 IS NULL OR @PR柤 IS NOT NULL AND H06PR棜楌.PR柤 LIKE '%' + @PR柤 + '%') AND 
	(H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔 >= @岞奐梊掕擔fr) AND 
	(H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔 <= @岞奐梊掕擔to) AND 
	(H11僾儘儌乕僔儑儞幚愌.岞奐幚巤擔 >= @岞奐幚巤擔fr) AND 
	(@岞奐幚巤擔to IS NULL) OR
	(@PR嬫暘No IS NULL OR @PR嬫暘No IS NOT NULL AND H06PR棜楌.PR嬫暘No = @PR嬫暘No) AND 
	(@PR柤 IS NULL OR @PR柤 IS NOT NULL AND H06PR棜楌.PR柤 LIKE '%' + @PR柤 + '%') AND 
	(H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔 >= @岞奐梊掕擔fr) AND 
	(H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔 <= @岞奐梊掕擔to) AND 
	(H11僾儘儌乕僔儑儞幚愌.岞奐幚巤擔 <= @岞奐幚巤擔to) AND 
	(@岞奐幚巤擔fr IS NULL) OR 
	(@PR嬫暘No IS NULL OR @PR嬫暘No IS NOT NULL AND H06PR棜楌.PR嬫暘No = @PR嬫暘No) AND 
	(@PR柤 IS NULL OR @PR柤 IS NOT NULL AND H06PR棜楌.PR柤 LIKE '%' + @PR柤 + '%') AND 
	(H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔 >= @岞奐梊掕擔fr) AND 
	(H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔 <= @岞奐梊掕擔to) AND 
	(H11僾儘儌乕僔儑儞幚愌.岞奐幚巤擔 >= @岞奐幚巤擔fr) AND 
	(H11僾儘儌乕僔儑儞幚愌.岞奐幚巤擔 <= @岞奐幚巤擔to)
ORDER BY
	 H07僾儘儌乕僔儑儞梊掕.岞奐梊掕擔 DESC
	,H11僾儘儌乕僔儑儞幚愌.岞奐幚巤擔 DESC


---- InsertPR棜楌
--INSERT INTO H06PR棜楌 (
--	 慻怐No
--	,PR棜楌No
--	,PR嬫暘No
--	,PR柤
--	,搊榐儐乕僓乕No
--	,搊榐擔帪
--	,峏怴儐乕僓乕No
--	,峏怴擔帪
--) VALUES (
--	 @慻怐No
--	,@PR棜楌No
--	,@PR嬫暘No
--	,@PR柤
--	,@搊榐儐乕僓乕No
--	,getdate()
--	,@搊榐儐乕僓乕No
--	,getdate()
--)
INSERT INTO H06PR棜楌 (
	PR嬫暘No,
	PR柤,
	搊榐僗僞僢僼No,
	搊榐擔帪,
	峏怴僗僞僢僼No,
	峏怴擔帪
) VALUES (
	@PR嬫暘No,
	@PR柤,
	@搊榐僗僞僢僼No,
	getdate(),
	@搊榐僗僞僢僼No,
	getdate()
);
--SELECT @PR棜楌No = SCOPE_IDENTITY();


---- Insert僾儘儌乕僔儑儞幚愌
--INSERT INTO H11僾儘儌乕僔儑儞幚愌 (
--	 慻怐No
--	,PR棜楌No
--	,岞奐幚巤擔
--	,幚巤婜娫fr
--	,幚巤婜娫to
--	,憤旓梡
--	,撪梕 
--	,旛峫
--	,搊榐儐乕僓乕No
--	,搊榐擔帪
--	,峏怴儐乕僓乕No
--	,峏怴擔帪
--) VALUES (
--	 @慻怐No
--	,@PR棜楌No
--	,@岞奐幚巤擔
--	,@幚巤婜娫fr
--	,@幚巤婜娫to
--	,@憤旓梡
--	,@撪梕
--	,@旛峫
--	,@搊榐儐乕僓乕No
--	,getdate()
--	,@搊榐儐乕僓乕No
--	,getdate()
--)
INSERT INTO H11僾儘儌乕僔儑儞幚愌 (
	PR棜楌No,
	岞奐幚巤擔,
	幚巤婜娫fr,
	幚巤婜娫to,
	憤旓梡,
	撪梕,
	旛峫, 
	搊榐僗僞僢僼No,
	搊榐擔帪,
	峏怴僗僞僢僼No,
	峏怴擔帪
) VALUES (
	@PR棜楌No,
	@岞奐幚巤擔,
	@幚巤婜娫fr,
	@幚巤婜娫to,
	@憤旓梡,
	@撪梕,
	@旛峫,
	@搊榐僗僞僢僼No,
	GETDATE(),
	@搊榐僗僞僢僼No,
	GETDATE()
)
                       

---- Insert僾儘儌乕僔儑儞梊掕
--INSERT INTO H07僾儘儌乕僔儑儞梊掕 (
--	 慻怐No
--	,PR棜楌No
--	,岞奐梊掕擔
--	,梊掕婜娫fr
--	,梊掕婜娫to
--	,憤旓梡
--	,懳徾彜昳憤悢
--	,懳徾婛懚屭媞憤悢
--	,桳岠擔悢撪偺憤攧忋
--	,怴婯屭媞妉摼悢
--	,尒崬屭媞妉摼悢
--	,孈婲偟屭媞悢
--	,撪梕
--	,旛峫
--	,搊榐儐乕僓乕No
--	,搊榐擔帪
--	,峏怴儐乕僓乕No
--	,峏怴擔帪
--) VALUES (
--	 @慻怐No
--	,@PR棜楌No
--	,@岞奐梊掕擔
--	,@梊掕婜娫fr
--	,@梊掕婜娫to
--	,@憤旓梡
--	,@懳徾彜昳憤悢
--	,@懳徾婛懚屭媞憤悢
--	,@桳岠擔悢撪偺憤攧忋
--	,@怴婯屭媞妉摼悢
--	,@尒崬屭媞妉摼悢
--	,@孈婲偟屭媞悢
--	,@撪梕
--	,@旛峫
--	,@搊榐儐乕僓乕No
--	,getdate()
--	,@搊榐儐乕僓乕No
--	,getdate()
--)
INSERT INTO H07僾儘儌乕僔儑儞梊掕 (
	PR棜楌No,
	岞奐梊掕擔,
	梊掕婜娫fr,
	梊掕婜娫to,
	憤旓梡,
	懳徾彜昳憤悢, 
	懳徾婛懚屭媞憤悢,
	桳岠擔悢撪偺憤攧忋,
	怴婯屭媞妉摼悢,
	尒崬屭媞妉摼悢, 
	孈婲偟屭媞悢,
	撪梕,
	旛峫,
	搊榐僗僞僢僼No,
	搊榐擔帪,
	峏怴僗僞僢僼No, 
	峏怴擔帪
) VALUES (
	@PR棜楌No,
	@岞奐梊掕擔,
	@梊掕婜娫fr,
	@梊掕婜娫to,
	@憤旓梡,
	@懳徾彜昳憤悢,
	@懳徾婛懚屭媞憤悢,
	@桳岠擔悢撪偺憤攧忋,
	@怴婯屭媞妉摼悢,
	@尒崬屭媞妉摼悢,
	@孈婲偟屭媞悢,
	@撪梕,@旛峫,
	@搊榐僗僞僢僼No,
	GETDATE(),
	@搊榐僗僞僢僼No,
	GETDATE()
)
                       
                       
---- UpdatePR棜楌
--UPDATE H06PR棜楌
--SET
--	 PR嬫暘No = @PR嬫暘No
--	,PR柤 = @PR柤
--	,峏怴儐乕僓乕No = @峏怴儐乕僓乕No
--	,峏怴擔帪 = getdate()
--WHERE
--	慻怐No = @慻怐No AND
--	 PR棜楌No = @PR棜楌No
UPDATE
	H06PR棜楌
SET
	PR嬫暘No = @PR嬫暘No,
	PR柤 = @PR柤,
	峏怴僗僞僢僼No = @峏怴僗僞僢僼No, 
	峏怴擔帪 = GETDATE()
WHERE
	(PR棜楌No = @PR棜楌No)


---- Update僾儘儌乕僔儑儞幚愌
--UPDATE H11僾儘儌乕僔儑儞幚愌
--SET
--	 岞奐幚巤擔 = @岞奐幚巤擔
--	,幚巤婜娫fr = @幚巤婜娫fr
--	,幚巤婜娫to = @幚巤婜娫to
--	,憤旓梡 = @憤旓梡
--	,撪梕 = @撪梕
--	,旛峫 = @旛峫
--	,峏怴儐乕僓乕No = @峏怴儐乕僓乕No
--	,峏怴擔帪 = getdate()
--WHERE
--	慻怐No = @慻怐No AND
--	PR棜楌No = @PR棜楌No
UPDATE
	H11僾儘儌乕僔儑儞幚愌
SET
	PR棜楌No = @PR棜楌No,
	岞奐幚巤擔 = @岞奐幚巤擔, 
	幚巤婜娫fr = @幚巤婜娫fr,
	幚巤婜娫to = @幚巤婜娫to,
	憤旓梡 = @憤旓梡, 
	撪梕 = @撪梕,
	旛峫 = @旛峫,
	峏怴僗僞僢僼No = @峏怴僗僞僢僼No, 
	峏怴擔帪 = GETDATE()
WHERE
	(僾儘儌乕僔儑儞幚愌No = @僾儘儌乕僔儑儞幚愌No)


---- Update僾儘儌乕僔儑儞梊掕
--UPDATE H07僾儘儌乕僔儑儞梊掕
--SET
--	 岞奐梊掕擔 = @岞奐梊掕擔
--	,梊掕婜娫fr = @梊掕婜娫fr
--	,梊掕婜娫to = @梊掕婜娫to
--	,憤旓梡 = @憤旓梡
--	,懳徾彜昳憤悢 = @懳徾彜昳憤悢
--	,懳徾婛懚屭媞憤悢 = @懳徾婛懚屭媞憤悢
--	,桳岠擔悢撪偺憤攧忋 = @桳岠擔悢撪偺憤攧忋
--	,怴婯屭媞妉摼悢 = @怴婯屭媞妉摼悢
--	,尒崬屭媞妉摼悢 = @尒崬屭媞妉摼悢
--	,孈婲偟屭媞悢 = @孈婲偟屭媞悢
--	,撪梕 = @撪梕
--	,旛峫 = @旛峫
--	,峏怴儐乕僓乕No = @峏怴儐乕僓乕No
--	,峏怴擔帪 = getdate()
--WHERE
--	慻怐No = @慻怐No AND
--	PR棜楌No = @PR棜楌No
UPDATE
	H07僾儘儌乕僔儑儞梊掕
SET
	PR棜楌No = @PR棜楌No,
	岞奐梊掕擔 = @岞奐梊掕擔, 
	梊掕婜娫fr = @梊掕婜娫fr,
	梊掕婜娫to = @梊掕婜娫to,
	憤旓梡 = @憤旓梡, 
	懳徾彜昳憤悢 = @懳徾彜昳憤悢,
	懳徾婛懚屭媞憤悢 = @懳徾婛懚屭媞憤悢, 
	桳岠擔悢撪偺憤攧忋 = @桳岠擔悢撪偺憤攧忋, 
	怴婯屭媞妉摼悢 = @怴婯屭媞妉摼悢,
	尒崬屭媞妉摼悢 = @尒崬屭媞妉摼悢, 
	孈婲偟屭媞悢 = @孈婲偟屭媞悢,
	撪梕 = @撪梕,
	旛峫 = @旛峫, 
	峏怴僗僞僢僼No = @峏怴僗僞僢僼No,
	峏怴擔帪 = GETDATE()
WHERE
	(僾儘儌乕僔儑儞梊掕No = @僾儘儌乕僔儑儞梊掕No)

