用友U8查询未审核单据

DECLARE @Start DATETIME,@End DATETIME
SET @Start='2024-01-01'
SET     @End='2024-12-31'
--库存期初
SELECT '苏州' AS Acc,dDate,cCode,'库存期初' AS cvouchName,ID,cMaker FROM rdrecord34 WHERE ISNULL(cHandler,N'')=N'' AND (( dDate < @Start AND ISNULL( bIsSTQc,0) = 1)
 OR ( dDate >= @Start
 AND ISNULL( bpufirst,0) = 0
 AND ISNULL( biafirst,0) = 0
 AND ISNULL( bOMFirst,0) = 0
 AND (NOT ( cBusType = N'假退料'AND  cVouchType = 11))
)
)    AND ((dDate >= @Start) AND (dDate <= @End))
UNION ALL
--采购入库单
 SELECT '苏州' AS Acc,dDate,cCode,'采购入库单' AS cvouchName,ID,cMaker FROM RdRecord01 WHERE ISNULL(cHandler,N'')=N'' AND (( dDate < @Start AND ISNULL( bIsSTQc,0) = 1)
 OR ( dDate >= @Start
 AND ISNULL( bpufirst,0) = 0
 AND ISNULL( biafirst,0) = 0
 AND ISNULL( bOMFirst,0) = 0
 AND (NOT ( cBusType = N'假退料'AND  cVouchType = 11))
)
)    AND ((dDate >= @Start) AND (dDate <= @End))
UNION ALL
--产成品入库单
 SELECT '苏州' AS Acc,dDate,cCode,'产成品入库单' AS cvouchName,ID,cMaker FROM rdrecord10 WHERE ISNULL(cHandler,N'')=N'' AND (( dDate < @Start AND ISNULL( bIsSTQc,0) = 1)
 OR ( dDate >= @Start
 AND ISNULL( bpufirst,0) = 0
 AND ISNULL( biafirst,0) = 0
 AND ISNULL( bOMFirst,0) = 0
 AND (NOT ( cBusType = N'假退料'AND  cVouchType = 11))
)
)    AND ((dDate >= @Start) AND (dDate <= @End))
UNION ALL
--其他入库单
 SELECT '苏州' AS Acc,dDate,cCode,'其他入库单' AS cvouchName,ID,cMaker FROM RdRecord08 WHERE ISNULL(cHandler,N'')=N'' AND (( dDate < @Start AND ISNULL( bIsSTQc,0) = 1)
 OR ( dDate >= @Start
 AND ISNULL( bpufirst,0) = 0
 AND ISNULL( biafirst,0) = 0
 AND ISNULL( bOMFirst,0) = 0
 AND (NOT ( cBusType = N'假退料'AND  cVouchType = 11))
)
)    AND ((dDate >= @Start) AND (dDate <= @End))
UNION ALL
--销售出库单
 SELECT '苏州' AS Acc,dDate,cCode,'销售出库单' AS cvouchName,ID,cMaker FROM rdrecord32 WHERE ISNULL(cHandler,N'')=N'' AND (( dDate < @Start AND ISNULL( bIsSTQc,0) = 1)
 OR ( dDate >= @Start
 AND ISNULL( bpufirst,0) = 0
 AND ISNULL( biafirst,0) = 0
 AND ISNULL( bOMFirst,0) = 0
 AND (NOT ( cBusType = N'假退料'AND  cVouchType = 11))
)
)    AND ((dDate >= @Start) AND (dDate <= @End))
UNION ALL
--材料出库单
 SELECT '苏州' AS Acc,dDate,cCode,'材料出库单' AS cvouchName,ID,cMaker FROM rdrecord11 WHERE ISNULL(cHandler,N'')=N'' AND (( dDate < @Start AND ISNULL( bIsSTQc,0) = 1)
 OR ( dDate >= @Start
 AND ISNULL( bpufirst,0) = 0
 AND ISNULL( biafirst,0) = 0
 AND ISNULL( bOMFirst,0) = 0
 AND (NOT ( cBusType = N'假退料'AND  cVouchType = 11))
)
)    AND ((dDate >=@Start) AND (dDate <= @End))
UNION ALL
--其他出库单
 SELECT '苏州' AS Acc,dDate,cCode,'其他出库单' AS cvouchName,ID,cMaker FROM RdRecord09 WHERE (ISNULL(cHandler,N'')=N''  OR (ISNULL(cVouchType,N'') = N'09'  AND ISNULL(ControlResult,-1) = 1  AND (ISNULL(iBG_OverFlag,0) = 1 OR ISNULL(iBG_OverFlag,0) = 3 )))  AND (( dDate < @Start AND ISNULL( bIsSTQc,0) = 1)
 OR ( dDate >= @Start
 AND ISNULL( bpufirst,0) = 0
 AND ISNULL( biafirst,0) = 0
 AND ISNULL( bOMFirst,0) = 0
 AND (NOT ( cBusType = N'假退料'AND  cVouchType = 11))
)
)    AND ((dDate >=@Start) AND (dDate <= @End))
UNION ALL
--调拨单
 SELECT '苏州' AS Acc,dDate,cCode,'调拨单' AS cvouchName,ID,cMaker FROM  (SELECT dTVDate AS dDate,cTVCode AS cCode,N'12' AS cVouchType,ID,cMaker,cVerifyPerson,cOWhCode,cIWhCode  FROM TransVouch ) aa  WHERE ISNULL(cVerifyPerson,N'')=N''      AND ((dDate >= @Start) AND (dDate <= @End))
 UNION ALL
 --盘点单
 SELECT '苏州' AS Acc,dDate,cCode,'盘点单' AS cvouchName,ID,cMaker FROM   (SELECT dCVDate AS dDate,cCVCode AS cCode,N'18' AS cVouchType,ID,cMaker,cWhCode,cDepCode,cPersonCode,cAccounter FROM  CheckVouch ) aa  WHERE ISNULL(cAccounter,N'')=N''     AND ((dDate >= @Start) AND (dDate <= @End))
 UNION ALL
 --组装拆卸形态转换单
 SELECT '苏州' AS Acc,dDate,cCode,'组装拆卸\形态转换单' AS cvouchName,ID,cMaker FROM  (SELECT a.dAVDate AS dDate,a.cAVCode AS cCode,a.cVouchType,a.ID,a.cMaker,a.cVerifyPerson,b.cWhCode AS cwhcode FROM AssemVouch a INNER JOIN AssemVouchs b  ON a.ID = b.ID) aa  WHERE ISNULL(cVerifyPerson,N'')=N''    AND ((dDate >= @Start) AND (dDate <= @End)) 
 UNION ALL
 --报废单
 SELECT '苏州' AS Acc,dDate,cCode,'报废单' AS cvouchName,Id,cMaker FROM  (SELECT dDate,cSpCode AS cCode,cVouchType,Id,cMaker,cVerifyPerson ,cInWhcode,cOutWhcode FROM ScrapVouch   WHERE ISNULL(cVerifyPerson,N'')=N''   AND dDate >=@Start  ) aa  WHERE   (dDate >= @Start) AND (dDate <= @End)
 UNION ALL
 --货位调整单
 SELECT '苏州' AS Acc,dDate,cCode,'货位调整单' AS cvouchName,Id,cMaker FROM  (SELECT  dDate,cVouchCode AS cCode,N'19' AS cVouchType,Id,cMaker,chandler,cWhCode FROM AdjustPVouch  ) aa  WHERE ISNULL(chandler,N'')=N''      AND ((dDate >= @Start) AND (dDate <= @End))
 UNION ALL
  --借出借用单
 SELECT '苏州' AS Acc,ddate,cCODE,'借出借用单' AS cvouchName, ID,cMaker FROM  HY_DZ_BorrowOut WHERE ISNULL(cHandler,N'')=N'' AND ((ddate >= @Start) AND (ddate <= @End))
   UNION ALL
 --借出转换单
 SELECT '苏州' AS Acc,ddate,cCODE,'借出转换单' AS cvouchName, ID,cMaker FROM  HY_DZ_BorrowOutChange WHERE ISNULL(cHandler,N'')=N'' AND ((ddate >= @Start) AND (ddate <= @End))
   UNION ALL
  --借出归还单
 SELECT '苏州' AS Acc,ddate,cCODE,'借出归还单' AS cvouchName, ID,cMaker FROM  HY_DZ_BorrowOutBack WHERE ISNULL(cHandler,N'')=N'' AND ((ddate >= @Start) AND (ddate <= @End))