应收发票数据查询

示例代码

SELECT  CONVERT(VARCHAR, T0.[DocNum], 1000) AS '单据编号' ,
        T0.[DocDate] AS '过账日期' ,
        T0.[CardCode] AS '客户/供应商代码' ,
        T0.[CardName] AS '客户/供应商名称' ,
        T1.[ItemCode] AS '物料编号' ,
        T1.[Dscription] AS '物料' ,
        t4.itmsgrpnam AS '物料组' ,
        T1.[unitMsr] AS '单位' ,
        T1.[Quantity] AS '开票数量' , T1.[OpenQty] AS '未开票数量', 
        T1.[PriceAfVAT] AS '含税单价' ,
        T1.[LineTotal] AS '不含税总计' ,
        T1.[GTotal] AS '价税合计' ,
        T1.grossbuypr 物料成本 ,
        T1.grossbuypr * T1.Quantity 成本合计 ,
        T1.WhsCode AS '仓库' ,
        T3.SlpName AS '销售员'
FROM    OINV T0
        INNER  JOIN [dbo].[INV1] T1 ON T1.[DocEntry] = T0.[DocEntry]
        INNER  JOIN [dbo].[OITM] T2 ON T2.[ItemCode] = T1.[ItemCode]
        LEFT JOIN OSLP t3 ON t3.SlpCode = T0.SlpCode
        LEFT JOIN OITB T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod
WHERE   ( t0.DocDate >= [%1]
          OR [%1] = ''
        )
        AND ( t0.DocDate <= [%2]
              OR [%2] = ''
            )
UNION ALL
SELECT  ( '贷项' + CONVERT(VARCHAR, T0.[DocNum], 1000) ) AS '单据编号' ,
        T0.[DocDate] AS '过账日期' ,
        T0.[CardCode] AS '客户/供应商代码' ,
        T0.[CardName] AS '客户/供应商名称' ,
        T1.[ItemCode] AS '物料编号' ,
        T1.[Dscription] AS '物料' ,
        t4.itmsgrpnam AS '物料组' ,
        T1.[unitMsr] AS '单位' ,
        -1 * T1.[Quantity] AS '开票数量' ,  T1.[OpenQty] AS '未开票数量',  
        -1 * T1.[PriceAfVAT] AS '含税单价' ,
        -1 * T1.[LineTotal] AS '不含税总计' ,
        -1 * T1.[GTotal] AS '价税合计' ,
        -1 * T1.grossbuypr AS 物料成本 ,
        -1 * T1.grossbuypr * T1.Quantity AS 成本合计 ,
        T1.WhsCode AS '仓库' ,
        T3.SlpName AS '销售员'
FROM    ORIN T0
        INNER  JOIN RIN1 T1 ON T1.[DocEntry] = T0.[DocEntry]
        INNER  JOIN OITM T2 ON T2.[ItemCode] = T1.[ItemCode]
        LEFT JOIN OSLP t3 ON t3.SlpCode = T0.SlpCode
        LEFT JOIN OITB T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod
WHERE   ( t0.DocDate >= [%1]
          OR [%1] = ''
        )
        AND ( t0.DocDate <= [%2]
              OR [%2] = ''
            )