U9C-SQL-采购订单视图

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'