U9C-SQL-采购订单视图
SELECT
po.ID,
CONVERT ( VARCHAR ( 10 ), po.CreatedOn, 23 ) AS 签订日期,
org.Name AS 甲方,
po.DocNo AS 单号,
item.Code AS 料号,
item.Name AS 品名,
item.SPECS AS 规格,
item.DescFlexField_PrivateDescSeg1 AS 图号,
item.DescFlexField_PrivateDescSeg2 AS 材质,
pol.DocLineNo / 10 AS 行号,
format(pol.PurQtyTU,'0.0') AS 数量,
format(pol.SupplierConfirmQtyPU,'0.0') as 确认数量,
uom.Name AS 单位,
pol.FinallyPriceTC AS 单价,
pol.TaxRate AS 税率,
pol.TotalMnyTC AS 金额,
CONVERT ( VARCHAR ( 10 ), sline.DeliveryDate, 23 ) AS 交期,
pol.DescFlexSegments_PubDescSeg2 AS 备注,
sup1.Name AS 供应商,
ISNULL( ppy.Name, '' ) + pre.Name AS 付款方式,
sup.DescFlexField_PrivateDescSeg4 AS 地址,
sup.DescFlexField_PrivateDescSeg2 AS 账号,
sup.DescFlexField_PrivateDescSeg1 AS 开户行,
sup.Code AS 税号,
cur.Symbol
FROM
dbo.PM_PurchaseOrder AS po
LEFT OUTER JOIN dbo.PM_POLine AS pol ON pol.PurchaseOrder = po.ID
LEFT OUTER JOIN dbo.CBO_ItemMaster AS item ON item.ID = pol.ItemInfo_ItemID
LEFT OUTER JOIN dbo.CBO_Supplier AS sup ON sup.ID = po.Supplier_Supplier
LEFT OUTER JOIN dbo.CBO_Supplier_Trl AS sup1 ON sup1.ID = po.Supplier_Supplier
LEFT OUTER JOIN dbo.Base_UOM_Trl AS uom ON uom.ID = pol.TradeUOM
LEFT OUTER JOIN dbo.PM_POShipLine AS sline ON sline.POLine = pol.ID
LEFT OUTER JOIN dbo.Base_Organization_Trl AS org ON po.Org = org.ID
LEFT OUTER JOIN dbo.CBO_APConfirmTerm_Trl AS pre ON pre.ID = pol.ConfirmTerm
LEFT OUTER JOIN dbo.CBO_SCM_PrepayPolicy AS ppy ON ppy.ID = pol.PrepayPolicy
LEFT OUTER JOIN dbo.Base_Currency AS cur ON cur.ID = po.AC
WHERE
( 1 = 1 )
AND ( po.Status IN ( 1,2, 3 ) ) --0是开立，1是审核中，2是已核准，3是自然关闭，4是短缺关闭，5超额关闭
--ORDER BY pol.DocLineNo
--po.DocNo='PO2406060002'
