MAS Data Extraction Queries - Version 1
Item (item)
SELECT IM.ItemCode AS 'Item Code',
IM.ItemCodeDesc AS 'Item Desc',
IW.QuantityOnHand AS 'On Hand Qunatity',
'' AS 'Reorder Level Quantity',
IV.StandardLeadTime AS 'Lead Time Days',
IW.MinimumOrderQty AS 'Min Order Quantity',
IM.PrimaryVendorNo AS 'Vendor Code',
IM.StandardUnitofMeasure AS 'Unit of Measure',
IM.ProductLine AS 'Class Code',
IM.LastTotalUnitCost AS 'Current Cost',
'1' as 'Inventoried',
IM.ProcurementType AS 'Make or Buy',
IM.PurchaseUnitofMeasure AS 'Case Pack Quantity',
'' AS 'Case Cube',
'' AS 'Safety Stock',
IM.StandardUnitPrice AS 'Unit Price'
FROM CI_Item IM, IM_ItemVendor IV, IM_ItemWarehouse IW
WHERE IM.ItemCode = IV.ItemCode
AND IM.ItemCode = IW.ItemCode
AND IV.ItemCode = IW.ItemCode
AND IM.PrimaryVendorNo = IV.VendorNo
AND (
((IW.WarehouseCode='000') AND (IM.Category4='03') AND (IM.Category2 In ('AC','AP','ASSM','D','DAP')))
OR ((IW.WarehouseCode='000') AND (IM.Category4='01') AND (IM.ProductLine='LDSW'))
OR ((IW.WarehouseCode='000') AND (IM.Category4='01') AND (IM.Category3='MLM'))
)
Customer (customers)
SELECT AR_Customer.CustomerNo, AR_Customer.CustomerName
FROM AR_Customer AR_Customer
Product Classes (productclasses)
SELECT IM_ProductLine.ProductLine AS 'prodclass', IM_ProductLine.ProductLineDesc AS 'proddesc'
FROM IM_ProductLine IM_ProductLine
Customer Orders (customerorders)
Select
h.SalesOrderNo AS "Order Number",
'O' AS 'Status',
h.OrderDate AS "Order Date",
h.CustomerNo AS "Customer Code",
d.ItemCode AS "ItemCode",
(d.QuantityOrdered * d.UnitOfMeasureConvFactor) as "Order Quantity",
h.UDF_REQ_SHIP_DATE AS "Due Date",
'' AS 'Ship Date',
'' AS 'Quantity Shipped',
(d.UnitPrice/d.UnitOfMeasureConvFactor) AS 'Sales Price',
(d.UnitCost/d.UnitOfMeasureConvFactor) AS 'Cost'
FROM
SO_SalesOrderHeader h, SO_SalesOrderDetail d
WHERE h.SalesOrderNo = d.SalesOrderNo
AND d.ItemType = '1'
AND h.OrderStatus <> 'C'
AND h.OrderType IN ('B','S')
AND (d.QuantityOrdered - d.QuantityShipped) > 0
AND d.warehousecode IN ('000','TOP','TAN','CPW')
ORDER BY d.ItemCode, h.CustomerNo, h.SalesOrderNo
Customer Orders History
SELECT
sh.EntryNo AS 'Order Number',
'C' AS 'Status',
sh.TransactionDate AS 'Order Date',
sh.CustomerNo AS 'Customer Code',
sh.ItemCode AS 'Item Code',
-1*sh.TransactionQty AS 'Order Quantity',
sh.TransactionDate AS 'Due Date',
sh.TransactionDate AS 'Ship Date',
-1*sh.TransactionQty AS 'Quantity Shipped',
sh.UnitPrice AS 'Sales Price',
sh.UnitCost AS 'Cost'
FROM
IM_ItemTransactionHistory sh
WHERE sh.TransactionCode IN ('SO', 'SI', 'IS')
AND sh.TransactionDate > {d '2010-01-01'}
AND sh.WarehouseCode IN ('000','TOP','TAN','CPW')
AND (-1*sh.TransactionQty)>0
ORDER BY sh.ItemCode, sh.TransactionDate
Vendor Orders (vendororders)
SELECT PO_PurchaseOrderHeader.PurchaseOrderNo AS 'vendorno', PO_PurchaseOrderHeader.OrderStatus AS 'Status', PO_PurchaseOrderHeader.PurchaseOrderDate AS 'Order Date', PO_PurchaseOrderHeader.VendorNo AS 'Vendor Code', PO_PurchaseOrderDetail.ItemCode AS 'Item Code', (PO_PurchaseOrderDetail.QuantityOrdered*PO_PurchaseOrderDetail.UnitOfMeasureConvFactor) AS 'Order Qty',
PO_PurchaseOrderDetail.RequiredDate AS 'Due Date', PO_PurchaseOrderDetail.QuantityReceived AS 'Actual Qty', '' AS 'Actual Date', PO_PurchaseOrderDetail.WarehouseCode AS 'Location Code'
FROM PO_PurchaseOrderDetail PO_PurchaseOrderDetail, PO_PurchaseOrderHeader PO_PurchaseOrderHeader
WHERE PO_PurchaseOrderDetail.PurchaseOrderNo = PO_PurchaseOrderHeader.PurchaseOrderNo
0 Comments
Add your comment