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

Vendors (vendors)

SELECT AP_Vendor.VendorNo, AP_Vendor.VendorName

FROM AP_Vendor AP_Vendor

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

Production Orders (shoporders)

Do not have query

Bill of Material (bom)

Do not have query

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.