SET NOCOUNT ON
DECLARE @poc INT,@PurchaseOrderId NVARCHAR(40),@EBELP INT,@sql NVARCHAR(max),
@PurchaseOrderDetailId NVARCHAR(40),@ExpenseShareId NVARCHAR(40),
@DetailCursor CURSOR, --订单明细游标
@ShareCursor CURSOR--订单分摊游标
DECLARE OrderCursor CURSOR LOCAL FOR SELECT DISTINCT PurchaseOrderCode FROM SAPMiddleDB.dbo.SAP_PurchaseOrder_Item --WHERE PurchaseOrderCode IN (1129,1130)
OPEN OrderCursor
FETCH NEXT FROM OrderCursor INTO @poc
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @PurchaseOrderId=NULL
------------------------------------------------------------------------------------------------------------------
SELECT @PurchaseOrderId=PurchaseOrderId FROM purchase20150427.dbo.t_d_PurchaseOrder WHERE PurchaseOrderCode=@poc
IF(@PurchaseOrderId IS NOT NULL)
BEGIN
set @DetailCursor = CURSOR LOCAL for select PurchaseOrderDetailId from purchase20150427.dbo.t_d_PurchaseOrder_Detail
where PurchaseOrderId=@PurchaseOrderId ORDER BY DisplayIndex
open @DetailCursor
SET @EBELP=10
fetch next from @DetailCursor into @PurchaseOrderDetailId
WHILE(@@FETCH_STATUS=0)
BEGIN
set @ShareCursor = CURSOR LOCAL for SELECT ExpenseShareId from purchase20150427.dbo.t_d_PurchaseOrder_ExpenseShare
where PurchaseOrderDetailId = @PurchaseOrderDetailId order by DisplayIndex
open @ShareCursor
fetch next from @ShareCursor into @ExpenseShareId
WHILE(@@FETCH_STATUS=0)
BEGIN
UPDATE SAPMiddleDB.dbo.SAP_PurchaseOrder_Item
SET DetailId=@PurchaseOrderDetailId,ExpenseShareId=@ExpenseShareId
WHERE PurchaseOrderCode=@poc AND EBELP=@EBELP
/*
SET @sql='select PurchaseOrderCode,EBELP,DetailId,ExpenseShareId from SAPMiddleDB.dbo.SAP_PurchaseOrder_Item WHERE PurchaseOrderCode='
+CAST(@poc AS NVARCHAR(10)) +' AND EBELP='+CAST(@EBELP AS NVARCHAR(10))
PRINT @sql
SET @sql='UPDATE SAPMiddleDB.dbo.SAP_PurchaseOrder_Item SET DetailId='''
+@PurchaseOrderDetailId+''',ExpenseShareId='''
+@ExpenseShareId+''' WHERE PurchaseOrderCode='
+CAST(@poc AS NVARCHAR(10)) +' AND EBELP='
+CAST(@EBELP AS NVARCHAR(10))
PRINT @sql
*/
set @EBELP = @EBELP + 10
FETCH NEXT FROM @ShareCursor into @ExpenseShareId
END
close @ShareCursor
deallocate @ShareCursor
fetch next from @DetailCursor into @PurchaseOrderDetailId
END
CLOSE @DetailCursor
DEALLOCATE @DetailCursor
END
------------------------------------------------------------------------------------------------------------------
FETCH NEXT FROM OrderCursor INTO @poc
END
CLOSE OrderCursor
DEALLOCATE OrderCursor
