采购收货单明细查询

代码示例

SELECT  CONVERT(VARCHAR, T0.DocNum, 1000) AS '单据编号' ,
        T1.LineNum + 1 AS '行号' ,
        T0.DocDate ,
        T0.DocDueDate AS '交货日期' ,
        T0.TaxDate ,
        T0.CardCode ,
        T0.CardName ,
        ( CASE WHEN T0.DocStatus = 'C'
                    AND T0.CANCELED = 'N'
                    AND T0.DocManClsd = 'N' THEN '已收票'
               WHEN T0.DocStatus = 'O' THEN '未请'
               WHEN T0.DocStatus = 'C'
                    AND T0.CANCELED = 'Y' THEN '已取消'
               WHEN DocManClsd = 'Y'
                    AND DocStatus = 'C' THEN '手工关闭'
          END ) AS '单据状态' ,
        T1.ItemCode ,
        T1.Dscription ,
        T1.Quantity ,
        T1.OpenQty ,
        T1.unitMsr ,
        T1.PriceAfVAT AS '含税单价' ,
        T1.GTotal ,
        T1.NumPerMsr * T1.Quantity AS '库存单位数量' ,
        t2.InvntryUom ,
        t3.WhsName
FROM    OPDN T0
        INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
        LEFT JOIN OITM t2 ON T1.ItemCode = t2.ItemCode
        LEFT JOIN OWHS t3 ON t3.WhsCode = T1.WhsCode
WHERE   ( T0.DocDate >= [%1]
          OR [%1] = ''
        )
        AND ( T0.DocDate <= [%2]
              OR [%2] = ''
            )
UNION ALL
SELECT  ( '退货' + CONVERT(VARCHAR, T0.DocNum, 1000) ) ,
        T1.LineNum + 1 AS '行号' ,
        T0.DocDate ,
        T0.DocDueDate AS '交货日期' ,
        T0.TaxDate ,
        T0.CardCode ,
        T0.CardName ,
        '已退货' AS '单据状态' ,
        T1.ItemCode ,
        T1.Dscription ,
        -1 * Quantity ,
        T1.OpenQty ,
        T1.unitMsr ,
        T1.PriceAfVAT AS '含税单价' ,
        -1 * T1.GTotal ,
        -1 * T1.NumPerMsr * T1.Quantity AS '库存单位数量' ,
        t2.InvntryUom ,
        t3.WhsName
FROM    ORPD T0
        INNER JOIN RPD1 T1 ON T0.DocEntry = T1.DocEntry
        LEFT JOIN OITM t2 ON T1.ItemCode = t2.ItemCode
        LEFT JOIN OWHS t3 ON t3.WhsCode = T1.WhsCode
WHERE   ( T0.DocDate >= [%1]
          OR [%1] = ''
        )
        AND ( T0.DocDate <= [%2]
              OR [%2] = ''
            )