USE [CollectionSystem]
GO
/****** 对象:  StoredProcedure [dbo].[OverDueForfeit]    脚本日期: 05/31/2011 09:38:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[OverDueForfeit] as
delete from dbo.contract_id_over_due
delete from Tb_RentalOverDue
insert into dbo.contract_id_over_due
select distinct contract_id from dbo.OverDueInfo

DECLARE @TIME VARCHAR(10),@ID varchar(10)
DECLARE @TSQL varchar(8000),@constant1 varchar(10),@constant2 varchar(10)--,@id varchar(10)
--SELECT  @ID='142'
declare cursor1 cursor for         --定义游标cursor1
select contract_id from contract_id_over_due   --使用游标的对象(跟据需要填入select)
open cursor1                       --打开游标
fetch next from cursor1 into @ID  --将游标向下移行，获取的数据放入之前定义的变量@id,@name中
while @@fetch_status=0           --判断是否成功获取数据
begin  
select @TIME=CONVERT(VARCHAR(10),processing_dte,120)
from openquery
(
FIATPRODUCT,
'select processing_dte from cms_fiat.processing_date'
)
select  @constant1='C'
select  @constant2='yyyy-mm-dd'
select  @TSQL='select contract_id,rental_id,ACCRUED_OD_AMT from OPENQUERY(FIATPRODUCT,''select * from Table(CMS_FIAT.FN_CTR_RENTAL_CALC_OD('''''+@ID+''''','''''+@constant1+''''',to_date('''''+@TIME+''''','''''+@constant2+'''''))) '')'
insert into Tb_RentalOverDue   --进行相应处理(跟据需要填入SQL文)
exec (@TSQL)
fetch next from cursor1 into @id  --将游标向下移行
end
close cursor1                   --关闭游标
deallocate cursor1