firststuff

Query Tracking

Oct 14th, 2021
690
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- General Info --
  2. SELECT [CustOrderNo], FORMAT(SODate, 'yyyy-MM-dd') AS SODate, [FMMReffNo], [CustomerName], [AcctCD] AS CustomerCode, Amount
  3. FROM (
  4.   SELECT CustOrderNo, FMMReffNo, CustomerName, AcctCD, MIN(SODate) AS SODate, SUM(UnitPrice*OrderQty) AS Amount
  5.   FROM [AcumaticaProduction_NEW].[dbo].[fmvInventoryOrderTracking] GROUP BY CustOrderNo, FMMReffNo, CustomerName, AcctCD
  6. ) A
  7. WHERE CustOrderNo = '1310081144' OR FMMReffNo = '1310081144'
  8.  
  9. -- List Item --
  10. SELECT OrderNbr , FORMAT(SODate, 'yyyy-MM-dd') AS SODate , FMMReffNo , CustOrderNo , CustomerName , AcctCD AS CustomerCode , Amount , InventoryCD , InventoryDesc , OrderQty , PONbr , POReceiptNbr , DOCustomer
  11. FROM (
  12.     SELECT OrderNbr, SODate, FMMReffNo, AcctCD, CustOrderNo, CustomerName, InventoryCD, InventoryDesc, OrderQty,PONbr, POReceiptNbr, DOCustomer, SOLineCost AS Amount
  13.     FROM [AcumaticaProduction_NEW].[dbo].[fmvInventoryOrderTracking]
  14. ) AS a
  15. WHERE (InventoryCD LIKE '%%' OR InventoryDesc LIKE '%%') AND (CustOrderNo = '1310081144' OR FMMReffNo = '1310081144') ORDER BY SODate asc OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
  16.  
  17. -- DO Shipment --
  18.  
  19. SELECT OrderNbr , FORMAT(SODate, 'yyyy-MM-dd') AS SODate , FMMReffNo , CustOrderNo , CustomerName , AcctCD AS CustomerCode , Amount , InventoryCD , InventoryDesc , OrderQty , ShippedQty , PONbr , POReceiptNbr , DOCustomer , CASE WHEN ShipComplete = 'Y' THEN 'COMPLETE' ELSE 'ON PROGRESS' END AS ShipComplete , FORMAT(ShipmentDate, 'yyyy-MM-dd') AS ShipmentDate , AJUAWBNbr AWB , InvoiceNbr
  20. FROM (
  21.     SELECT OrderNbr, SODate, FMMReffNo, AcctCD, CustOrderNo, CustomerName, InventoryCD, InventoryDesc, OrderQty, ShippedQty, PONbr, POReceiptNbr, DOCustomer, SOLineCost AS Amount, ShipComplete, ShipmentDate, AJUAWBNbr, InvoiceNbr
  22.     FROM [AcumaticaProduction_NEW].[dbo].[fmvInventoryOrderTracking]
  23. ) AS a
  24. WHERE DOCustomer IS NOT NULL AND (InventoryCD LIKE '%%' OR InventoryDesc LIKE '%%') AND (CustOrderNo = '1310081144' OR FMMReffNo = '1310081144') ORDER BY InventoryCD asc OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
  25.  
  26. -- List Invoice --
  27.  
  28. SELECT FMMReffNo , CustOrderNo , CustomerName , AcctCD AS CustomerCode , Amount , CASE WHEN InvoiceReleased = 1 THEN 'CLOSED' ELSE 'OPEN' END AS InvoiceReleased , InvoiceNbr
  29. FROM (
  30.     SELECT CustOrderNo, FMMReffNo, CustomerName, AcctCD, InvoiceNbr, InvoiceReleased, SUM(UnitPrice*ShippedQty) AS Amount
  31.     FROM [AcumaticaProduction_NEW].[dbo].[fmvInventoryOrderTracking]
  32.     GROUP BY CustOrderNo, FMMReffNo, CustomerName, AcctCD, InvoiceNbr, InvoiceReleased
  33. ) AS a
  34. WHERE InvoiceNbr LIKE '%%' AND (CustOrderNo = '1310081144' OR FMMReffNo = '1310081144') ORDER BY InvoiceNbr asc OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
  35.  
RAW Paste Data