ArticleDynamics NAVBusiness CentralSQLERP
Microsoft Dynamics NAV SQL Queries
Part of our ERP query series. Fifteen SQL queries for Navision, Dynamics NAV and Business Central that turn up in every ETL and BI project.
As part of our ERP query series we bring you Navision / Microsoft Dynamics NAV / Dynamics 365 Business Central, the new name for the latter. The schemas are all pretty close and there are many tables, but there are a few core ones that are always used in ETL and BI analytics projects.
Here are the SQL queries you can use in your data projects. You can download them directly from the Navision-SQL-Queries repository on GitHub.
Each query lives in the repository as its own file, from CustomerLedgerTransactionQuery.sql through to SupplierQuery.sql. Expand any of them below to read the SQL.
Customer Ledger Transactions CustomerLedgerTransactionQuery.sql
;WITH
I AS
( SELECT SIH.No_,
SIH.[Payment Terms Code],
2 AS DocumentType
FROM dbo.[Navision Company$Sales Invoice Header] AS SIH
UNION
SELECT SCMH.No_,
SCMH.[Payment Terms Code],
3 AS DocumentType
FROM dbo.[Navision Company$Sales Cr_Memo Header] AS SCMH
UNION
SELECT SIH.No_,
SIH.[Payment Terms Code],
2 AS DocumentType
FROM dbo.[Navision Company$Service Invoice Header] AS SIH
UNION
SELECT SCMH.No_,
SCMH.[Payment Terms Code],
3 AS DocumentType
FROM dbo.[Navision Company$Service Cr_Memo Header] AS SCMH ),
PT AS
( SELECT PT.Code, PT.Description FROM dbo.[Navision Company$Payment Terms] AS PT )
SELECT DCLE.[Entry No_] AS CUSTOMER_LEDGER_TRASANCTION_ID,
DCLE.[Cust_ Ledger Entry No_] AS CUSTOMER_LEDGER_ID,
ISNULL(NULLIF(DCLE.[Currency Code], N''), GLS.[LCY Code]) AS CURRENCY_ID,
DCLE.[Customer No_] AS CUSTOMER_ID,
CONVERT(CHAR(10), DCLE.[Posting Date], 121) AS DATE_POSTED,
--CONVERT(CHAR(10), CLE.[Document Date], 121) AS DOCUMENT_DATE,
CONVERT(CHAR(10), DCLE.[Initial Entry Due Date], 121) AS DATE_DUE,
CLE.Description AS DESCRIPTION,
DCLE.[Document No_] AS DOCUMENT_NO,
CASE WHEN DCLE.[Document Type] = 1 THEN 'Payment'
WHEN DCLE.[Document Type] = 2 THEN 'Invoice'
WHEN DCLE.[Document Type] = 3 THEN 'Credit Memo'
WHEN DCLE.[Document Type] = 4 THEN 'Finance Charge Memo'
WHEN DCLE.[Document Type] = 5 THEN 'Reminder'
WHEN DCLE.[Document Type] = 6 THEN 'Refund'
ELSE NULL
END AS DOCUMENT_TYPE,
CASE WHEN DCLE.[Entry Type] = 1 THEN 'Initial Entry'
WHEN DCLE.[Entry Type] = 2 THEN 'Application'
WHEN DCLE.[Entry Type] = 3 THEN 'Unrealized Loss'
WHEN DCLE.[Entry Type] = 4 THEN 'Unrealized Gain'
WHEN DCLE.[Entry Type] = 5 THEN 'Realized Loss'
WHEN DCLE.[Entry Type] = 6 THEN 'Realized Gain'
WHEN DCLE.[Entry Type] = 7 THEN 'Payment Discount'
WHEN DCLE.[Entry Type] = 8 THEN 'Payment Discount (VAT Excl.)'
WHEN DCLE.[Entry Type] = 9 THEN 'Payment Discount (VAT Adjustment)'
WHEN DCLE.[Entry Type] = 10 THEN 'Appln. Rounding'
WHEN DCLE.[Entry Type] = 11 THEN 'Correction of Remaining Amount'
WHEN DCLE.[Entry Type] = 12 THEN 'Payment Tolerance'
WHEN DCLE.[Entry Type] = 13 THEN 'Payment Discount Tolerance'
WHEN DCLE.[Entry Type] = 14 THEN 'Payment Tolerance (VAT Excl.)'
WHEN DCLE.[Entry Type] = 15 THEN 'Payment Tolerance (VAT Adjustment)'
WHEN DCLE.[Entry Type] = 16 THEN 'Payment Discount Tolerance (VAT Excl.)'
WHEN DCLE.[Entry Type] = 17 THEN 'Payment Discount Tolerance (VAT Adjustment)'
ELSE NULL
END AS ENTRY_TYPE,
PT.Code AS PAYMENT_TERMS,
PT.Description AS PAYMENT_TERMS_DESCRIPTION,
DCLE.Amount AS AMOUNT,
DCLE.[Debit Amount] AS AMOUNT_DEBIT,
DCLE.[Credit Amount] AS AMOUNT_CREDIT,
DCLE.[Amount (LCY)] AS AMOUNT_BASE,
DCLE.[Debit Amount (LCY)] AS AMOUNT_DEBIT_BASE,
DCLE.[Credit Amount (LCY)] AS AMOUNT_CREDIT_BASE,
IIF(CLE.[Customer Posting Group] = 'INTERNAL', 1, 0) AS IS_INTERNAL,
CLE.[Open] AS IS_OPEN,
CONVERT(CHAR(19), DCLE.[Posting Date], 121) AS DATE_LAST_MODIFIED
FROM dbo.[Navision Company$Detailed Cust_ Ledg_ Entry] AS DCLE
INNER JOIN dbo.[Navision Company$Cust_ Ledger Entry] AS CLE ON DCLE.[Cust_ Ledger Entry No_] = CLE.[Entry No_]
LEFT OUTER JOIN I ON DCLE.[Document No_] = I.No_
AND DCLE.[Document Type] = I.DocumentType
LEFT OUTER JOIN PT ON I.[Payment Terms Code] = PT.Code
OUTER APPLY dbo.[Navision Company$General Ledger Setup] AS GLS
ORDER BY DCLE.[Entry No_];Customer Parameters CustomerParameterQuery.sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT C.No_ AS CUSTOMER_PARAMETER_ID,
C.No_ AS CUSTOMER_ID,
CAST(C.[Credit Limit (LCY)] AS DECIMAL(38, 4)) AS CREDIT_LIMIT,
CASE WHEN C.Blocked = 0 THEN CAST(0 AS BIT)ELSE CAST(1 AS BIT)END AS IS_CREDIT_BLOCKED,
C.[Payment Terms Code] AS PAYMENT_TERMS_CODE,
PT.Description AS PAYMENT_TERMS_DESCRIPTION,
FORMAT(C.[Last Date Modified], 'yyyy-MM-dd HH:mm:ss') AS DATE_LAST_MODIFIED
FROM dbo.[Navision Company$Customer] AS C
LEFT OUTER JOIN dbo.[Navision Company$Payment Terms] AS PT ON C.[Payment Terms Code] = PT.Code;Customers CustomerQuery.sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT C.No_ AS CUSTOMER_ID,
Name AS CUSTOMER_NAME,
C.[IC Partner Code] AS ASSOCIATION_NO,
CONVERT(CHAR(10), SI.FirstOrderDate, 121) AS ACCOUNT_OPENED_DATE,
C.[Currency Code] AS DEF_CURRENCY,
NULL AS MARKET_SECTOR,
C.[Country_Region Code] AS CUSTOMER_COUNTRY_CODE,
NULL AS ADDRESS_ID,
C.Address AS ADDRESS1,
C.[Address 2] AS ADDRESS2,
NULL AS ADDRESS3,
NULL AS CITY,
C.County AS COUNTY,
C.[Country_Region Code] AS ADDRESS_COUNTRY,
C.[Post Code] AS POSTALCODE,
C.[Phone No_] AS ADDRESS_ID_PHONE,
C.[Home Page] AS ADDRESS_ID_WWW,
CONVERT(CHAR(19), C.[Last Date Modified], 121) AS DATE_LAST_MODIFIED
FROM dbo.[Navision Company$Customer] AS C
LEFT OUTER JOIN ( SELECT SI.[Bill-to Customer No_],
MIN(SI.[Order Date]) AS FirstOrderDate
FROM dbo.[Navision Company$Sales Invoice Header] AS SI
WHERE SI.[Bill-to Customer No_] <> '' AND
SI.[Order Date] > '1753-01-01 00:00:00.000'
GROUP BY SI.[Bill-to Customer No_]) AS SI ON C.No_ = SI.[Bill-to Customer No_];General Ledger GeneralLedgerQuery.sql
SELECT GLE.[Entry No_] AS GeneralLedgerEntryID,
GLE.[G_L Account No_] AS GLAccountNumber,
GLA.Name AS GLAccountName,
GLE.[Posting Date] AS PostingDate,
GLE.[Document Date] AS DocuemntDate,
GLE.[Document Type] AS DocumentType,
GLE.[Document No_] AS DocumentNo,
GLE.Description,
GLE.[Debit Amount] AS DebitAmount,
GLE.[Credit Amount] AS CreditAmount,
GLE.[VAT Amount] AS VATAmount,
GLE.[Source Code] AS SourceCode,
GLE.[Gen_ Bus_ Posting Group] AS GLBusinessPostingGroup,
GLE.[Gen_ Prod_ Posting Group] AS GLProductionPostingGroup,
GLE.[Transaction No_] AS TransactionNo,
GLE.[VAT Bus_ Posting Group] AS VATBusinessPostingGroup,
GLE.[VAT Prod_ Posting Group] AS VATProductionPostingGroup,
GLE.[Dimension Set ID] AS DimensionSetID
FROM dbo.[Navision Company$G_L Entry] AS GLE
INNER JOIN dbo.[Navision Company$G_L Account] AS GLA ON GLE.[G_L Account No_] = GLA.No_;Inventory InventoryQuery.sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH
LE AS
( SELECT LE1.[Item No_] AS ItemNo,
MAX(NULLIF(LE1.[Location Code], '')) AS LocationCode,
SUM(LE1.Quantity) AS Quantity,
MAX(LE1.[Posting Date]) AS PostingDate
FROM dbo.[Navision Company$Item Ledger Entry] AS LE1
GROUP BY LE1.[Item No_] ),
I AS
( SELECT I.No_ AS ItemNo,
I.[Reorder Quantity] AS ReorderQuantity,
I.[Safety Stock Quantity] AS SafetyStockQuantity
FROM dbo.[Navision Company$Item] AS I ),
PNS AS
( SELECT SL.[Item No_] AS ItemNo,
SUM(SL.[Qty_ Picked (Base)] - SL.[Qty_ Shipped (Base)]) AS PickedNotShippedQuantity
FROM dbo.[Navision Company$Warehouse Shipment Line] AS SL
GROUP BY SL.[Item No_] ),
RMQ AS
( SELECT AH.[Item No_] AS ItemNo,
SUM(AH.[Remaining Quantity (Base)]) AS RemainingQuantity
FROM dbo.[Navision Company$Assembly Header] AS AH
GROUP BY AH.[Item No_] ),
OQ AS
( SELECT PL.No_ AS ItemNo,
SUM(PL.[Outstanding Qty_ (Base)]) AS OutstandingQuantity,
MAX(PL.[Expected Receipt Date]) AS ExpectedReceiptDate
FROM dbo.[Navision Company$Purchase Line] AS PL
WHERE PL.[Outstanding Qty_ (Base)] <> 0
AND PL.[Document Type] IN ( 1, 4 )
AND PL.Type = 2
GROUP BY PL.No_ ),
ITQ AS
( SELECT TL.[Item No_] AS ItemNo,
SUM(TL.[Qty_ in Transit (Base)]) AS InTransitQuantity
FROM dbo.[Navision Company$Transfer Line] AS TL
GROUP BY TL.[Item No_] ),
RVQ AS
( SELECT RE.[Item No_] AS ItemNo,
SUM(RE.[Quantity (Base)]) AS ReseveredQuantity
FROM dbo.[Navision Company$Reservation Entry] AS RE
WHERE RE.[Source Type] = 37
AND RE.[Reservation Status] = 0
GROUP BY RE.[Item No_] )
SELECT CASE WHEN LE.LocationCode IS NULL THEN LE.ItemNo ELSE LE.ItemNo + '-' + LE.LocationCode END AS INVENTORY_ID,
LE.LocationCode AS INVENTORY_LOCATION_ID,
LE.LocationCode AS LOCATION_ID,
LE.ItemNo AS ITEM_ID,
CAST(ISNULL(LE.Quantity, 0) - ISNULL(PNS.PickedNotShippedQuantity, 0) AS DECIMAL(38, 4)) AS QUANTITY_ON_HAND,
CAST(ISNULL(RVQ.ReseveredQuantity, 0) AS DECIMAL(38, 4)) AS QUANTITY_RESERVED,
CAST(ISNULL(OQ.OutstandingQuantity, 0) - ISNULL(RMQ.RemainingQuantity, 0) AS DECIMAL(38, 4)) AS QUANTITY_ON_ORDER,
CAST(0 AS DECIMAL(38, 4)) AS QUANTITY_RECEIVED,
CAST(ISNULL(ITQ.InTransitQuantity, 0) AS DECIMAL(38, 4)) AS QUANTITY_IN_TRANSIT,
CAST(ISNULL(LE.Quantity, 0) - ISNULL(PNS.PickedNotShippedQuantity, 0) - ISNULL(RVQ.ReseveredQuantity, 0) AS DECIMAL(38, 4)) AS QUANTITY_AVAILABLE,
CAST(ISNULL(I.ReorderQuantity, 0) AS DECIMAL(38, 4)) AS QUANTITY_REORDER,
CAST(ISNULL(I.SafetyStockQuantity, 0) AS DECIMAL(38, 4)) AS QUANTITY_SAFETY,
OQ.ExpectedReceiptDate AS DATE_ON_ORDER_DUE,
CONVERT(CHAR(19), LE.PostingDate, 121) AS DATE_LAST_MODIFIED
FROM LE
INNER JOIN I ON LE.ItemNo = I.ItemNo
LEFT OUTER JOIN PNS ON LE.ItemNo = PNS.ItemNo
LEFT OUTER JOIN RMQ ON LE.ItemNo = RMQ.ItemNo
LEFT OUTER JOIN OQ ON LE.ItemNo = OQ.ItemNo
LEFT OUTER JOIN ITQ ON LE.ItemNo = ITQ.ItemNo
LEFT OUTER JOIN RVQ ON LE.ItemNo = RVQ.ItemNo;Invoice Items InvoiceItemQuery.sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT II.LEGAL_ENTITY,
II.INVOICE_ID,
II.CUSTOMERID_INVOICE,
II.QUANTITY,
II.SALE_UNIT_PRICE,
II.TAX_RATE,
II.SALES_DISCOUNT,
II.SALES_TAX_AMOUNT,
II.SALES_NET_AMOUNT,
II.SALES_GROSS_AMOUNT,
II.BASE_SALE_UNIT_PRICE,
II.BASE_VAT_AMOUNT,
II.BASE_NET_AMOUNT,
II.BASE_GROSS_AMOUNT,
II.DATE_LAST_MODIFIED,
II.INVOICE_ITEM_ID,
II.LINE_NO,
II.SITE,
II.PART_CODE,
II.DESCRIPTION,
II.CATEGORY,
II.UOM
FROM( SELECT SIL.[Document No_] AS INVOICE_ID,
CONVERT(CHAR(10), SIH.[Document Date], 121) AS INVOICE_DATE,
ISNULL(NULLIF(SIL.[Sell-to Customer No_], ''), SIH.[Bill-to Customer No_]) AS CUSTOMERID_INVOICE,
CAST(SIL.Quantity AS DECIMAL(38, 4)) AS QUANTITY,
CAST(SIL.[Unit Price] AS DECIMAL(38, 4)) AS SALE_UNIT_PRICE,
CAST(SIL.[VAT _] AS DECIMAL(38, 4)) AS TAX_RATE,
CAST(SIL.[Line Discount _] AS DECIMAL(38, 4)) AS SALES_DISCOUNT,
CAST(SIL.[Amount Including VAT] - SIL.Amount AS DECIMAL(38, 4)) AS SALES_TAX_AMOUNT,
CAST(SIL.Amount AS DECIMAL(38, 4)) AS SALES_NET_AMOUNT,
CAST(SIL.[Amount Including VAT] AS DECIMAL(38, 4)) AS SALES_GROSS_AMOUNT,
CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.[Unit Price]
ELSE ( 1 / SIH.[Currency Factor] ) * ( SIL.[Unit Price] )
END AS DECIMAL(38, 4)) AS BASE_SALE_UNIT_PRICE,
CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.[Amount Including VAT] - SIL.Amount
ELSE ( 1 / SIH.[Currency Factor] ) * ( SIL.[Amount Including VAT] - SIL.Amount )
END AS DECIMAL(38, 4)) AS BASE_VAT_AMOUNT,
CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.Amount ELSE ( 1 / SIH.[Currency Factor] ) * SIL.Amount END AS DECIMAL(38, 4)) AS BASE_NET_AMOUNT,
CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.[Amount Including VAT]
ELSE ( 1 / SIH.[Currency Factor] ) * SIL.[Amount Including VAT]
END AS DECIMAL(38, 4)) AS BASE_GROSS_AMOUNT,
CONVERT(CHAR(19), SIL.[Posting Date], 121) AS DATE_LAST_MODIFIED,
SIL.[Document No_] + '-' + CAST(SIL.[Line No_] AS VARCHAR(16)) AS INVOICE_ITEM_ID,
SIL.[Line No_] AS LINE_NO,
SIL.[Location Code] AS LOCATION_ID,
SIL.No_ AS PART_CODE,
SIL.Description AS DESCRIPTION,
SIL.[Item Category Code] AS CATEGORY,
SIL.[Unit of Measure] AS UOM
FROM [Navision Company$Sales Invoice Header] AS SIH
LEFT OUTER JOIN dbo.[Navision Company$Sales Invoice Line] AS SIL ON SIH.No_ = SIL.[Document No_]
UNION ALL
SELECT SIL.[Document No_] AS INVOICE_ID,
CONVERT(CHAR(10), SIH.[Document Date], 121) AS INVOICE_DATE,
SIH.[Bill-to Customer No_] AS CUSTOMERID_INVOICE,
CAST(SIL.Quantity AS DECIMAL(38, 4)) AS QUANTITY,
CAST(SIL.[Unit Price] AS DECIMAL(38, 4)) AS SALE_UNIT_PRICE,
CAST(SIL.[VAT _] AS DECIMAL(38, 4)) AS TAX_RATE,
CAST(SIL.[Line Discount _] AS DECIMAL(38, 4)) AS SALES_DISCOUNT,
CAST(SIL.[Amount Including VAT] - SIL.Amount AS DECIMAL(38, 4)) AS SALES_TAX_AMOUNT,
CAST(SIL.Amount AS DECIMAL(38, 4)) AS SALES_NET_AMOUNT,
CAST(SIL.[Amount Including VAT] AS DECIMAL(38, 4)) AS SALES_GROSS_AMOUNT,
CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.[Unit Price]
ELSE ( 1 / SIH.[Currency Factor] ) * ( SIL.[Unit Price] )
END AS DECIMAL(38, 4)) AS BASE_SALE_UNIT_PRICE,
CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.[Amount Including VAT] - SIL.Amount
ELSE ( 1 / SIH.[Currency Factor] ) * ( SIL.[Amount Including VAT] - SIL.Amount )
END AS DECIMAL(38, 4)) AS BASE_VAT_AMOUNT,
CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.Amount ELSE ( 1 / SIH.[Currency Factor] ) * SIL.Amount END AS DECIMAL(38, 4)) AS BASE_NET_AMOUNT,
CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.[Amount Including VAT]
ELSE ( 1 / SIH.[Currency Factor] ) * SIL.[Amount Including VAT]
END AS DECIMAL(38, 4)) AS BASE_GROSS_AMOUNT,
CONVERT(CHAR(19), SIL.[Posting Date], 121) AS DATE_LAST_MODIFIED,
SIL.[Document No_] + '-' + CAST(SIL.[Line No_] AS VARCHAR(16)) AS INVOICE_ITEM_ID,
SIL.[Line No_] AS LINE_NO,
SIL.[Location Code] AS LOCATION_ID,
SIL.No_ AS PART_CODE,
SIL.Description AS DESCRIPTION,
SIL.[Item Category Code] AS CATEGORY,
SIL.[Unit of Measure] AS UOM
FROM [Navision Company$Service Invoice Header] AS SIH
LEFT OUTER JOIN dbo.[Navision Company$Service Invoice Line] AS SIL ON SIH.No_ = SIL.[Document No_]
UNION ALL
SELECT SCL.[Document No_] AS INVOICE_ID,
CONVERT(CHAR(10), SCH.[Document Date], 121) AS INVOICE_DATE,
ISNULL(NULLIF(SCL.[Sell-to Customer No_], ''), SCH.[Bill-to Customer No_]) AS CUSTOMERID_INVOICE,
CAST(SCL.Quantity AS DECIMAL(38, 4)) AS QUANTITY,
CAST(SCL.[Unit Price] AS DECIMAL(38, 4)) AS SALE_UNIT_PRICE,
CAST(SCL.[VAT _] AS DECIMAL(38, 4)) AS TAX_RATE,
CAST(SCL.[Line Discount _] AS DECIMAL(38, 4)) AS SALES_DISCOUNT,
-CAST(SCL.[Amount Including VAT] - SCL.Amount AS DECIMAL(38, 4)) AS SALES_TAX_AMOUNT,
-CAST(SCL.Amount AS DECIMAL(38, 4)) AS SALES_NET_AMOUNT,
-CAST(SCL.[Amount Including VAT] AS DECIMAL(38, 4)) AS SALES_GROSS_AMOUNT,
CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SCL.[Unit Price]
ELSE ( 1 / SCH.[Currency Factor] ) * ( SCL.[Unit Price] )
END AS DECIMAL(38, 4)) AS BASE_SALE_UNIT_PRICE,
-CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SCL.[Amount Including VAT] - SCL.Amount
ELSE ( 1 / SCH.[Currency Factor] ) * ( SCL.[Amount Including VAT] - SCL.Amount )
END AS DECIMAL(38, 4)) AS BASE_VAT_AMOUNT,
-CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SCL.Amount ELSE ( 1 / SCH.[Currency Factor] ) * SCL.Amount END AS DECIMAL(38, 4)) AS BASE_NET_AMOUNT,
-CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SCL.[Amount Including VAT]
ELSE ( 1 / SCH.[Currency Factor] ) * SCL.[Amount Including VAT]
END AS DECIMAL(38, 4)) AS BASE_GROSS_AMOUNT,
CONVERT(CHAR(19), SCL.[Posting Date], 121) AS DATE_LAST_MODIFIED,
SCL.[Document No_] + '-' + CAST(SCL.[Line No_] AS VARCHAR(16)) AS INVOICE_ITEM_ID,
SCL.[Line No_] AS LINE_NO,
SCL.[Location Code] AS LOCATION_ID,
SCL.No_ AS PART_CODE,
SCL.Description AS DESCRIPTION,
SCL.[Item Category Code] AS CATEGORY,
SCL.[Unit of Measure] AS UOM
FROM dbo.[Navision Company$Sales Cr_Memo Header] AS SCH
LEFT OUTER JOIN dbo.[Navision Company$Sales Cr_Memo Line] AS SCL ON SCH.No_ = SCL.[Document No_]
UNION ALL
SELECT SCL.[Document No_] AS INVOICE_ID,
CONVERT(CHAR(10), SCH.[Document Date], 121) AS INVOICE_DATE,
SCH.[Bill-to Customer No_] AS CUSTOMERID_INVOICE,
CAST(SCL.Quantity AS DECIMAL(38, 4)) AS QUANTITY,
CAST(SCL.[Unit Price] AS DECIMAL(38, 4)) AS SALE_UNIT_PRICE,
CAST(SCL.[VAT _] AS DECIMAL(38, 4)) AS TAX_RATE,
CAST(SCL.[Line Discount _] AS DECIMAL(38, 4)) AS SALES_DISCOUNT,
-CAST(SCL.[Amount Including VAT] - SCL.Amount AS DECIMAL(38, 4)) AS SALES_TAX_AMOUNT,
-CAST(SCL.Amount AS DECIMAL(38, 4)) AS SALES_NET_AMOUNT,
-CAST(SCL.[Amount Including VAT] AS DECIMAL(38, 4)) AS SALES_GROSS_AMOUNT,
CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SCL.[Unit Price]
ELSE ( 1 / SCH.[Currency Factor] ) * ( SCL.[Unit Price] )
END AS DECIMAL(38, 4)) AS BASE_SALE_UNIT_PRICE,
-CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SCL.[Amount Including VAT] - SCL.Amount
ELSE ( 1 / SCH.[Currency Factor] ) * ( SCL.[Amount Including VAT] - SCL.Amount )
END AS DECIMAL(38, 4)) AS BASE_VAT_AMOUNT,
-CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SCL.Amount ELSE ( 1 / SCH.[Currency Factor] ) * SCL.Amount END AS DECIMAL(38, 4)) AS BASE_NET_AMOUNT,
-CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SCL.[Amount Including VAT]
ELSE ( 1 / SCH.[Currency Factor] ) * SCL.[Amount Including VAT]
END AS DECIMAL(38, 4)) AS BASE_GROSS_AMOUNT,
CONVERT(CHAR(19), SCL.[Posting Date], 121) AS DATE_LAST_MODIFIED,
SCL.[Document No_] + '-' + CAST(SCL.[Line No_] AS VARCHAR(16)) AS INVOICE_ITEM_ID,
SCL.[Line No_] AS LINE_NO,
SCL.[Location Code] AS LOCATION_ID,
SCL.No_ AS PART_CODE,
SCL.Description AS DESCRIPTION,
SCL.[Item Category Code] AS CATEGORY,
SCL.[Unit of Measure] AS UOM
FROM dbo.[Navision Company$Service Cr_Memo Header] AS SCH
LEFT OUTER JOIN dbo.[Navision Company$Service Cr_Memo Line] AS SCL ON SCH.No_ = SCL.[Document No_] ) AS II;Invoice Ledger InvoiceLedgerQuery.sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT CLE.[Entry No_] AS INVOICE_LEDGER_ID,
ISNULL(NULLIF(CLE.[Currency Code], N''), GLS.[LCY Code]) AS CURRENCY,
ISNULL(NULLIF(CLE.[Customer No_], ''), CLE.[Sell-to Customer No_]) AS CUSTOMER_ID,
CLE.[Document No_] AS INVOICE_ID,
CASE WHEN CLE.[Document Type] = 2 THEN 'DEBIT'
WHEN CLE.[Document Type] = 3 THEN 'CREDIT'
ELSE NULL
END AS INVOICE_TYPE,
CLE.[Document No_] AS INVOICE_NO,
CONVERT(CHAR(10), CLE.[Document Date], 121) AS INVOICE_DATE,
SIH.[Payment Terms Code] AS PAYMENT_TERMS,
SDPT.Description AS PAYMENT_TERMS_DESCRIPTION,
CONVERT(CHAR(10), CLE.[Due Date], 121) AS DUE_DATE,
CONVERT(CHAR(10), NULLIF(CLE.[Closed at Date], N'1753-01-01 00:00:00.000'), 121) AS PAID_DATE,
CAST(SIL.InvoiceTotal AS DECIMAL(38, 4)) AS SALES_INVOICE_AMOUNT,
CAST(CLE.[Closed by Amount] AS DECIMAL(38, 4)) AS SALES_PAID_AMOUNT,
CASE WHEN CAST(SIL.InvoiceTotal - CLE.[Closed by Amount] AS DECIMAL(38, 4)) <= 0 THEN CAST(0 AS DECIMAL(38, 4))
ELSE CAST(SIL.InvoiceTotal - CLE.[Closed by Amount] AS DECIMAL(38, 4))
END AS SALES_DUE_AMOUNT,
CAST(CASE WHEN CLE.[Adjusted Currency Factor] = 0 THEN SIL.InvoiceTotal
ELSE ( 1 / CLE.[Adjusted Currency Factor] ) * SIL.InvoiceTotal
END AS DECIMAL(38, 4)) AS BASE_INVOICE_AMOUNT,
CAST(CASE WHEN CLE.[Adjusted Currency Factor] = 0 THEN CLE.[Closed by Amount]
ELSE CLE.[Closed by Amount (LCY)]
END AS DECIMAL(38, 4)) AS BASE_PAID_AMOUNT,
CASE WHEN CAST(SIL.InvoiceTotal - CLE.[Closed by Amount] AS DECIMAL(38, 4)) <= 0 THEN CAST(0 AS DECIMAL(38, 4))
ELSE CAST(CASE WHEN CLE.[Adjusted Currency Factor] = 0 THEN ( SIL.InvoiceTotal - CLE.[Closed by Amount] )
ELSE ( SIL.InvoiceTotal - CLE.[Closed by Amount (LCY)] )
END AS DECIMAL(38, 4))
END AS BASE_DUE_AMOUNT,
CASE WHEN SIL.InvoiceTotal - CLE.[Closed by Amount] <= 0
AND [Open] = 0 THEN 1
WHEN SIL.InvoiceTotal IS NULL
AND [Open] = 0 THEN 1
ELSE 0
END AS IS_FULLY_PAID,
CLE.[Document No_] AS PAYMENT_REFERENCE,
CONVERT(CHAR(19), CLE.[Posting Date], 121) AS DATE_LAST_MODIFIED
FROM dbo.[Navision Company$Cust_ Ledger Entry] AS CLE
LEFT OUTER JOIN dbo.[Navision Company$Sales Invoice Header] AS SIH ON CLE.[Document No_] = SIH.No_
LEFT OUTER JOIN( SELECT [Document No_],
SUM([Amount Including VAT]) AS InvoiceTotal
FROM dbo.[Navision Company$Sales Invoice Line]
GROUP BY [Document No_] ) AS SIL ON SIL.[Document No_] = SIH.No_
LEFT OUTER JOIN dbo.[Navision Company$Customer] AS SDC ON CLE.[Customer No_] = SDC.No_
LEFT OUTER JOIN dbo.[Navision Company$Payment Terms] AS SDPT ON SIH.[Payment Terms Code] = SDPT.Code
OUTER APPLY dbo.[Navision Company$General Ledger Setup] AS GLS
WHERE( CLE.[Document Type] IN ( 2, 3 ))
AND ( CLE.[Sales (LCY)] <> 0 );Invoice InvoiceQuery.sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT I.LEGAL_ENTITY,
I.INVOICE_ID,
I.INVOICE_TYPE,
I.CURRENCY,
I.CUSTOMERID_INVOICE,
I.CUSTOMERID_ORDER,
I.INVOICE_NO,
I.ORDER_REFERENCE,
I.RMA_REFERENCE,
I.INVOICE_DATE,
I.SALES_TAX_AMOUNT,
I.SALES_NET_AMOUNT,
I.SALES_GROSS_AMOUNT,
I.BASE_VAT_AMOUNT,
I.BASE_NET_AMOUNT,
I.BASE_GROSS_AMOUNT,
I.DATE_LAST_MODIFIED,
I.SITE
FROM( SELECT SIH.No_ AS INVOICE_ID,
'DEBIT' AS INVOICE_TYPE,
ISNULL(NULLIF(SIH.[Currency Code], ''), GLS.[LCY Code]) AS CURRENCY,
SIH.[Bill-to Customer No_] AS CUSTOMERID_INVOICE,
SIH.[Sell-to Customer No_] AS CUSTOMERID_ORDER,
SIH.No_ AS INVOICE_NO,
SIH.[Order No_] AS ORDER_REFERENCE,
NULL AS RMA_REFERENCE,
CONVERT(CHAR(10), SIH.[Document Date], 121) AS INVOICE_DATE,
SUM(CAST(SIL.[Amount Including VAT] - SIL.Amount AS DECIMAL(38, 4))) AS SALES_TAX_AMOUNT,
SUM(CAST(SIL.Amount AS DECIMAL(38, 4))) AS SALES_NET_AMOUNT,
SUM(CAST(SIL.[Amount Including VAT] AS DECIMAL(38, 4))) AS SALES_GROSS_AMOUNT,
SUM(CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.[Amount Including VAT] - SIL.Amount
ELSE ( 1 / SIH.[Currency Factor] ) * ( SIL.[Amount Including VAT] - SIL.Amount )
END AS DECIMAL(38, 4))) AS BASE_VAT_AMOUNT,
SUM(CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.Amount ELSE ( 1 / SIH.[Currency Factor] ) * SIL.Amount END AS DECIMAL(38, 4))) AS BASE_NET_AMOUNT,
SUM(CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.[Amount Including VAT]
ELSE ( 1 / SIH.[Currency Factor] ) * SIL.[Amount Including VAT]
END AS DECIMAL(38, 4))) AS BASE_GROSS_AMOUNT,
CONVERT(CHAR(19), SIH.[Posting Date], 121) AS DATE_LAST_MODIFIED,
SIH.[Location Code] AS LOCATION_ID
FROM [Navision Company$Sales Invoice Header] AS SIH
LEFT OUTER JOIN dbo.[Navision Company$Sales Invoice Line] AS SIL ON SIH.No_ = SIL.[Document No_]
OUTER APPLY dbo.[Navision Company$General Ledger Setup] AS GLS
GROUP BY ISNULL(NULLIF(SIH.[Currency Code], ''), GLS.[LCY Code]),
SIH.[Posting Date],
SIH.[Document Date],
SIH.No_,
SIH.[Bill-to Customer No_],
SIH.[Sell-to Customer No_],
SIH.No_,
SIH.[Order No_],
SIH.[Location Code]
UNION ALL
SELECT SIH.No_ AS INVOICE_ID,
'DEBIT' AS INVOICE_TYPE,
ISNULL(NULLIF(SIH.[Currency Code], ''), GLS.[LCY Code]) AS CURRENCY,
SIH.[Bill-to Customer No_] AS CUSTOMERID_INVOICE,
NULL AS CUSTOMERID_ORDER,
SIH.No_ AS INVOICE_NO,
SIH.[Order No_] AS ORDER_REFERENCE,
NULL AS RMA_REFERENCE,
CONVERT(CHAR(10), SIH.[Document Date], 121) AS INVOICE_DATE,
SUM(CAST(SIL.[Amount Including VAT] - SIL.Amount AS DECIMAL(38, 4))) AS SALES_TAX_AMOUNT,
SUM(CAST(SIL.Amount AS DECIMAL(38, 4))) AS SALES_NET_AMOUNT,
SUM(CAST(SIL.[Amount Including VAT] AS DECIMAL(38, 4))) AS SALES_GROSS_AMOUNT,
SUM(CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.[Amount Including VAT] - SIL.Amount
ELSE ( 1 / SIH.[Currency Factor] ) * ( SIL.[Amount Including VAT] - SIL.Amount )
END AS DECIMAL(38, 4))) AS BASE_VAT_AMOUNT,
SUM(CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.Amount ELSE ( 1 / SIH.[Currency Factor] ) * SIL.Amount END AS DECIMAL(38, 4))) AS BASE_NET_AMOUNT,
SUM(CAST(CASE WHEN SIH.[Currency Factor] = 0 THEN SIL.[Amount Including VAT]
ELSE ( 1 / SIH.[Currency Factor] ) * SIL.[Amount Including VAT]
END AS DECIMAL(38, 4))) AS BASE_GROSS_AMOUNT,
CONVERT(CHAR(19), SIH.[Posting Date], 121) AS DATE_LAST_MODIFIED,
SIH.[Location Code] AS LOCATION_ID
FROM [Navision Company$Service Invoice Header] AS SIH
LEFT OUTER JOIN dbo.[Navision Company$Service Invoice Line] AS SIL ON SIH.No_ = SIL.[Document No_]
OUTER APPLY dbo.[Navision Company$General Ledger Setup] AS GLS
GROUP BY ISNULL(NULLIF(SIH.[Currency Code], ''), GLS.[LCY Code]),
SIH.[Posting Date],
SIH.[Document Date],
SIH.No_,
SIH.[Bill-to Customer No_],
SIH.No_,
SIH.[Order No_],
SIH.[Location Code]
UNION ALL
SELECT SCH.No_ AS INVOICE_ID,
'DEBIT' AS INVOICE_TYPE,
ISNULL(NULLIF(SCH.[Currency Code], ''), GLS.[LCY Code]) AS CURRENCY,
SCH.[Bill-to Customer No_] AS CUSTOMERID_INVOICE,
SCH.[Sell-to Customer No_] AS CUSTOMERID_ORDER,
SCH.No_ AS INVOICE_NO,
SCH.[Return Order No_] AS ORDER_REFERENCE,
SCH.[Return Order No_] AS RMA_REFERENCE,
CONVERT(CHAR(10), SCH.[Document Date], 121) AS INVOICE_DATE,
-SUM(CAST(SIL.[Amount Including VAT] - SIL.Amount AS DECIMAL(38, 4))) AS SALES_TAX_AMOUNT,
-SUM(CAST(SIL.Amount AS DECIMAL(38, 4))) AS SALES_NET_AMOUNT,
-SUM(CAST(SIL.[Amount Including VAT] AS DECIMAL(38, 4))) AS SALES_GROSS_AMOUNT,
-SUM(CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SIL.[Amount Including VAT] - SIL.Amount
ELSE ( 1 / SCH.[Currency Factor] ) * ( SIL.[Amount Including VAT] - SIL.Amount )
END AS DECIMAL(38, 4))) AS BASE_VAT_AMOUNT,
-SUM(CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SIL.Amount ELSE ( 1 / SCH.[Currency Factor] ) * SIL.Amount END AS DECIMAL(38, 4))) AS BASE_NET_AMOUNT,
-SUM(CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SIL.[Amount Including VAT]
ELSE ( 1 / SCH.[Currency Factor] ) * SIL.[Amount Including VAT]
END AS DECIMAL(38, 4))) AS BASE_GROSS_AMOUNT,
CONVERT(CHAR(19), SCH.[Posting Date], 121) AS DATE_LAST_MODIFIED,
SCH.[Location Code] AS LOCATION_ID
FROM [Navision Company$Sales Cr_Memo Header] AS SCH
LEFT OUTER JOIN dbo.[Navision Company$Sales Cr_Memo Line] AS SIL ON SCH.No_ = SIL.[Document No_]
OUTER APPLY dbo.[Navision Company$General Ledger Setup] AS GLS
GROUP BY ISNULL(NULLIF(SCH.[Currency Code], ''), GLS.[LCY Code]),
SCH.[Posting Date],
SCH.[Document Date],
SCH.No_,
SCH.[Bill-to Customer No_],
SCH.[Sell-to Customer No_],
SCH.No_,
SCH.[Return Order No_],
SCH.[Return Order No_],
SCH.[Location Code]
UNION ALL
SELECT SCH.No_ AS INVOICE_ID,
'DEBIT' AS INVOICE_TYPE,
ISNULL(NULLIF(SCH.[Currency Code], ''), GLS.[LCY Code]) AS CURRENCY,
SCH.[Bill-to Customer No_] AS CUSTOMERID_INVOICE,
NULL AS CUSTOMERID_ORDER,
SCH.No_ AS INVOICE_NO,
NULL AS ORDER_REFERENCE,
NULL AS RMA_REFERENCE,
CONVERT(CHAR(10), SCH.[Document Date], 121) AS INVOICE_DATE,
-SUM(CAST(SIL.[Amount Including VAT] - SIL.Amount AS DECIMAL(38, 4))) AS SALES_TAX_AMOUNT,
-SUM(CAST(SIL.Amount AS DECIMAL(38, 4))) AS SALES_NET_AMOUNT,
-SUM(CAST(SIL.[Amount Including VAT] AS DECIMAL(38, 4))) AS SALES_GROSS_AMOUNT,
-SUM(CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SIL.[Amount Including VAT] - SIL.Amount
ELSE ( 1 / SCH.[Currency Factor] ) * ( SIL.[Amount Including VAT] - SIL.Amount )
END AS DECIMAL(38, 4))) AS BASE_VAT_AMOUNT,
-SUM(CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SIL.Amount ELSE ( 1 / SCH.[Currency Factor] ) * SIL.Amount END AS DECIMAL(38, 4))) AS BASE_NET_AMOUNT,
-SUM(CAST(CASE WHEN SCH.[Currency Factor] = 0 THEN SIL.[Amount Including VAT]
ELSE ( 1 / SCH.[Currency Factor] ) * SIL.[Amount Including VAT]
END AS DECIMAL(38, 4))) AS BASE_GROSS_AMOUNT,
CONVERT(CHAR(19), SCH.[Posting Date], 121) AS DATE_LAST_MODIFIED,
SCH.[Location Code] AS LOCATION_ID
FROM [Navision Company$Service Cr_Memo Header] AS SCH
LEFT OUTER JOIN dbo.[Navision Company$Service Cr_Memo Line] AS SIL ON SCH.No_ = SIL.[Document No_]
OUTER APPLY dbo.[Navision Company$General Ledger Setup] AS GLS
GROUP BY ISNULL(NULLIF(SCH.[Currency Code], ''), GLS.[LCY Code]),
SCH.[Posting Date],
SCH.[Document Date],
SCH.No_,
SCH.[Bill-to Customer No_],
SCH.No_,
SCH.[Location Code] ) AS I;Product ProductQuery.sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT I.No_ AS ITEM_ID,
I.No_ AS PRODUCT_CODE,
I.Description AS DESCRIPTION,
IC.Description AS CATEGORY,
I.[Base Unit of Measure] AS UNIT_OF_MEASURE,
CONVERT(CHAR(19), I.[Created Datetime], 121) AS DATE_CREATED,
CONVERT(
CHAR(19),
DATETIMEFROMPARTS(
DATEPART(YEAR, I.[Last Date Modified]),
DATEPART(MONTH, I.[Last Date Modified]),
DATEPART(DAY, I.[Last Date Modified]),
DATEPART(HOUR, I.[Last Time Modified]),
DATEPART(MINUTE, I.[Last Time Modified]),
DATEPART(SECOND, I.[Last Time Modified]),
DATEPART(MILLISECOND, I.[Last Time Modified])),
121) AS DATE_LAST_MODIFIED
FROM dbo.[Navision Company$Item] AS I
LEFT OUTER JOIN dbo.[Navision Company$Item Category] AS IC ON I.[Item Category Code] = IC.Code;Purchase Invoice Line PurchaseInvoiceLineQuery.sql
WITH
PINV AS
( SELECT PIL.[Document No_] + '-I-' + CAST(PIL.[Line No_] AS VARCHAR(16)) AS PURCHASE_INVOICE_ITEM_ID,
NULLIF(PIH.[Purchaser Code], '') AS BUYER_ID,
ISNULL(NULLIF(PIH.[Currency Code], N''), GLS.[LCY Code]) AS CURRENCY_ID,
PIL.No_ AS ITEM_ID,
PIL.[Location Code] AS LOCATION_ID,
PIH.[Pay-to Vendor No_] AS BUY_FROM_SUPPLIER_ID,
PIL.[Buy-from Vendor No_] AS PAY_TO_SUPPLIER_ID,
NULLIF(LTRIM(RTRIM(I.[Vendor Item No_])), '') AS SUPPLIER_ITEM_ID,
PIL.[Document No_] AS INVOICE_NUMBER,
NULLIF(LTRIM(RTRIM(PIH.[Order No_])), '') AS PURCHASE_ORDER_NUMBER,
PIL.[Line No_] AS LINE_NUMBER,
NULL AS BUYER_NAME,
VBF.Name AS BUY_FROM_SUPPLIER_NAME,
VPT.Name AS PAY_TO_SUPPLIER_NAME,
NULL AS DELIVERY_NUMBER,
'Purchase Invoice' AS DOCUMENT_TYPE,
CONVERT(CHAR(10), PIH.[Document Date], 121) AS INVOICE_DATE,
NULL AS INVOICE_STATUS,
NULL AS LINE_STATUS,
PIL.Description AS DESCRIPTION,
-- PIL.[Item Category Code] AS ITEM_CATEGORY_CODE
PIL.Quantity AS QUANTITY,
NULLIF(LTRIM(RTRIM(PIL.[Unit of Measure])), '') AS UNIT_OF_MEASURE,
PIL.[VAT Identifier] AS TAX_CODE,
PIL.[VAT _] AS TAX_RATE,
CONVERT(CHAR(10), PIH.[Due Date], 121) AS DUE_DATE,
CONVERT(CHAR(10), PIH.[Due Date], 121) AS DATE_PAYMENT_PLANNED,
CONVERT(CHAR(10), PIL.[Expected Receipt Date], 121) AS RECEIPT_DATE,
PIL.[Line Discount _] AS LINE_DISCOUNT_RATE,
PIH.[Payment Discount _] AS INVOICE_DISCOUNT_RATE,
NULL AS TOTAL_DISCOUNT_RATE,
PIL.[Unit Cost] AS UNIT_COST,
PIL.[Line Discount Amount] AS DISCOUNT_AMOUNT,
PIL.Amount AS NET_AMOUNT,
PIL.[Amount Including VAT] - PIL.Amount AS TAX_AMOUNT,
PIL.[Amount Including VAT] AS GROSS_AMOUNT,
PIL.[Unit Cost (LCY)] AS BASE_UNIT_COST,
CASE WHEN PIH.[Currency Factor] = 0 THEN PIL.[Line Discount Amount]
ELSE ( 1 / PIH.[Currency Factor] ) * PIL.[Line Discount Amount]
END AS BASE_DISCOUNT_AMOUNT,
CASE WHEN PIH.[Currency Factor] = 0 THEN PIL.Amount ELSE ( 1 / PIH.[Currency Factor] ) * PIL.Amount END AS BASE_NET_AMOUNT,
CASE WHEN PIH.[Currency Factor] = 0 THEN PIL.[Amount Including VAT] - PIL.Amount
ELSE ( 1 / PIH.[Currency Factor] ) * ( PIL.[Amount Including VAT] - PIL.Amount )
END AS BASE_TAX_AMOUNT,
CASE WHEN PIH.[Currency Factor] = 0 THEN PIL.[Amount Including VAT]
ELSE ( 1 / PIH.[Currency Factor] ) * PIL.[Amount Including VAT]
END AS BASE_GROSS_AMOUNT,
PIH.[Payment Terms Code] AS PAYMENT_TERMS,
PT.Description AS PAYMENT_TERMS_DESCRIPTION,
NULL AS LOADED_BY,
U.[User Name] AS CREATED_BY,
U.[User Name] AS MODIFIED_BY,
CONVERT(CHAR(19), GETDATE(), 121) AS DATE_LOADED,
CONVERT(CHAR(19), PIL.[Posting Date], 121) AS DATE_CREATED,
CONVERT(CHAR(19), PIL.[Posting Date], 121) AS DATE_MODIFIED
FROM dbo.[Navision Company$Purch_ Inv_ Header] AS PIH
LEFT OUTER JOIN dbo.[Navision Company$Purch_ Inv_ Line] AS PIL ON PIH.No_ = PIL.[Document No_]
LEFT OUTER JOIN dbo.[Navision Company$Item] AS I ON PIL.No_ = I.No_
LEFT OUTER JOIN dbo.[Navision Company$Vendor] AS VBF ON PIH.[Buy-from Vendor No_] = VBF.No_
LEFT OUTER JOIN dbo.[Navision Company$Vendor] AS VPT ON PIH.[Pay-to Vendor No_] = VPT.No_
LEFT OUTER JOIN dbo.[User] AS U ON PIH.[User ID] = U.[User Name]
LEFT OUTER JOIN dbo.[Navision Company$Payment Terms] AS PT ON PIH.[Payment Terms Code] = PT.Code
OUTER APPLY dbo.[Navision Company$General Ledger Setup] AS GLS
WHERE PIL.Amount <> 0
UNION ALL
SELECT PIL.[Document No_] + '-R-' + CAST(PIL.[Line No_] AS VARCHAR(16)) AS PURCHASE_INVOICE_ITEM_ID,
NULLIF(PIH.[Purchaser Code], '') AS BUYER_ID,
ISNULL(NULLIF(PIH.[Currency Code], N''), GLS.[LCY Code]) AS CURRENCY_ID,
PIH.[Currency Factor] AS CURRENCY_RATE,
PIL.No_ AS ITEM_ID,
PIL.[Location Code] AS LOCATION_ID,
PIH.[Pay-to Vendor No_] AS BUY_FROM_SUPPLIER_ID,
PIL.[Buy-from Vendor No_] AS PAY_TO_SUPPLIER_ID,
NULLIF(LTRIM(RTRIM(I.[Vendor Item No_])), '') AS SUPPLIER_ITEM_ID,
PIL.[Document No_] AS INVOICE_NUMBER,
NULLIF(LTRIM(RTRIM(PIH.[Return Order No_])), '') AS PURCHASE_ORDER_NUMBER,
PIL.[Line No_] AS LINE_NUMBER,
NULL AS BUYER_NAME,
VBF.Name AS BUY_FROM_SUPPLIER_NAME,
VPT.Name AS PAY_TO_SUPPLIER_NAME,
NULL AS DELIVERY_NUMBER,
'Purchase Invoice Return' AS DOCUMENT_TYPE,
CONVERT(CHAR(10), PIH.[Document Date], 121) AS INVOICE_DATE,
NULL AS INVOICE_STATUS,
NULL AS LINE_STATUS,
PIL.Description AS DESCRIPTION,
-- PIL.[Item Category Code] AS ITEM_CATEGORY_CODE
CAST(PIL.Quantity AS DECIMAL(38, 4)) AS QUANTITY,
NULLIF(LTRIM(RTRIM(PIL.[Unit of Measure])), '') AS UNIT_OF_MEASURE,
PIL.[VAT Identifier] AS TAX_CODE,
PIL.[VAT _] AS TAX_RATE,
CONVERT(CHAR(10), PIH.[Due Date], 121) AS DUE_DATE,
CONVERT(CHAR(10), PIH.[Due Date], 121) AS DATE_PAYMENT_PLANNED,
CONVERT(CHAR(10), PIL.[Expected Receipt Date], 121) AS RECEIPT_DATE,
PIL.[Line Discount _] AS LINE_DISCOUNT_RATE,
PIH.[Payment Discount _] AS INVOICE_DISCOUNT_RATE,
NULL AS TOTAL_DISCOUNT_RATE,
PIL.[Unit Cost] AS UNIT_COST,
CASE WHEN SIGN(PIL.[Line Discount Amount]) IN ( 1, 0 ) THEN PIL.[Line Discount Amount]
ELSE -ABS(PIL.[Line Discount Amount])
END AS DISCOUNT_AMOUNT,
CASE WHEN SIGN(PIL.Amount) IN ( 1, 0 ) THEN PIL.Amount ELSE -ABS(PIL.Amount) END AS NET_AMOUNT,
CASE WHEN SIGN(PIL.[Amount Including VAT] - PIL.Amount) IN ( 1, 0 ) THEN PIL.[Amount Including VAT] - PIL.Amount
ELSE -ABS(PIL.[Amount Including VAT] - PIL.Amount)
END AS TAX_AMOUNT,
CASE WHEN SIGN(PIL.[Amount Including VAT]) IN ( 1, 0 ) THEN PIL.[Amount Including VAT]
ELSE -ABS(PIL.[Amount Including VAT])
END AS GROSS_AMOUNT,
PIL.[Unit Cost (LCY)] AS BASE_UNIT_COST,
CASE WHEN PIH.[Currency Factor] = 0 THEN CASE WHEN SIGN(PIL.[Line Discount Amount]) IN ( 1, 0 ) THEN PIL.[Line Discount Amount]
ELSE -ABS(PIL.[Line Discount Amount])
END
ELSE ( 1 / PIH.[Currency Factor] ) * CASE WHEN SIGN(PIL.[Line Discount Amount]) IN ( 1, 0 ) THEN PIL.[Line Discount Amount]
ELSE -ABS(PIL.[Line Discount Amount])
END
END AS BASE_DISCOUNT_AMOUNT,
CASE WHEN PIH.[Currency Factor] = 0 THEN CASE WHEN SIGN(PIL.Amount) IN ( 1, 0 ) THEN PIL.Amount ELSE -ABS(PIL.Amount) END
ELSE ( 1 / PIH.[Currency Factor] ) * CASE WHEN SIGN(PIL.Amount) IN ( 1, 0 ) THEN PIL.Amount ELSE -ABS(PIL.Amount) END
END AS BASE_NET_AMOUNT,
CASE WHEN PIH.[Currency Factor] = 0 THEN CASE WHEN SIGN(PIL.[Amount Including VAT] - PIL.Amount) IN ( 1, 0 ) THEN PIL.[Amount Including VAT] - PIL.Amount
ELSE -ABS(PIL.[Amount Including VAT] - PIL.Amount)
END
ELSE ( 1 / PIH.[Currency Factor] ) * CASE WHEN SIGN(PIL.[Amount Including VAT] - PIL.Amount) IN ( 1, 0 ) THEN PIL.[Amount Including VAT] - PIL.Amount
ELSE -ABS(PIL.[Amount Including VAT] - PIL.Amount)
END
END AS BASE_TAX_AMOUNT,
CASE WHEN PIH.[Currency Factor] = 0 THEN CASE WHEN SIGN(PIL.[Amount Including VAT]) IN ( 1, 0 ) THEN PIL.[Amount Including VAT]
ELSE -ABS(PIL.[Amount Including VAT])
END
ELSE ( 1 / PIH.[Currency Factor] ) * CASE WHEN SIGN(PIL.[Amount Including VAT]) IN ( 1, 0 ) THEN PIL.[Amount Including VAT]
ELSE -ABS(PIL.[Amount Including VAT])
END
END AS BASE_GROSS_AMOUNT,
PIH.[Payment Terms Code] AS PAYMENT_TERMS,
PT.Description AS PAYMENT_TERMS_DESCRIPTION,
NULL AS LOADED_BY,
U.[User Name] AS CREATED_BY,
U.[User Name] AS MODIFIED_BY,
CONVERT(CHAR(19), GETDATE(), 121) AS DATE_LOADED,
CONVERT(CHAR(19), PIL.[Posting Date], 121) AS DATE_CREATED,
CONVERT(CHAR(19), PIL.[Posting Date], 121) AS DATE_MODIFIED
FROM dbo.[Navision Company$Purch_ Cr_ Memo Hdr_] AS PIH
LEFT OUTER JOIN dbo.[Navision Company$Purch_ Cr_ Memo Line] AS PIL ON PIH.No_ = PIL.[Document No_]
LEFT OUTER JOIN dbo.[Navision Company$Item] AS I ON PIL.No_ = I.No_
LEFT OUTER JOIN dbo.[Navision Company$Vendor] AS VBF ON PIH.[Buy-from Vendor No_] = VBF.No_
LEFT OUTER JOIN dbo.[Navision Company$Vendor] AS VPT ON PIH.[Pay-to Vendor No_] = VPT.No_
LEFT OUTER JOIN dbo.[User] AS U ON PIH.[User ID] = U.[User Name]
LEFT OUTER JOIN dbo.[Navision Company$Payment Terms] AS PT ON PIH.[Payment Terms Code] = PT.Code
OUTER APPLY dbo.[Navision Company$General Ledger Setup] AS GLS )
SELECT PINV.PURCHASE_INVOICE_ITEM_ID,
PINV.BUYER_ID,
PINV.CURRENCY_ID,
PINV.LEGAL_ENTITY_ID,
PINV.ITEM_ID,
PINV.LOCATION_ID,
PINV.BUY_FROM_SUPPLIER_ID,
PINV.PAY_TO_SUPPLIER_ID,
PINV.SUPPLIER_ITEM_ID,
PINV.INVOICE_NUMBER,
PINV.PURCHASE_ORDER_NUMBER,
PINV.LINE_NUMBER,
PINV.BUYER_NAME,
PINV.BUY_FROM_SUPPLIER_NAME,
PINV.PAY_TO_SUPPLIER_NAME,
PINV.DELIVERY_NUMBER,
PINV.DOCUMENT_TYPE,
PINV.INVOICE_DATE,
PINV.INVOICE_STATUS,
PINV.LINE_STATUS,
PINV.DESCRIPTION,
PINV.QUANTITY,
PINV.UNIT_OF_MEASURE,
PINV.TAX_CODE,
CAST(PINV.TAX_RATE AS DECIMAL(38, 4)) AS TAX_RATE,
PINV.DUE_DATE,
PINV.DATE_PAYMENT_PLANNED,
PINV.RECEIPT_DATE,
CAST(PINV.LINE_DISCOUNT_RATE AS DECIMAL(38, 4)) AS LINE_DISCOUNT_RATE,
CAST(PINV.INVOICE_DISCOUNT_RATE AS DECIMAL(38, 4)) AS INVOICE_DISCOUNT_RATE,
CAST(PINV.TOTAL_DISCOUNT_RATE AS DECIMAL(38, 4)) AS TOTAL_DISCOUNT_RATE,
CAST(PINV.UNIT_COST AS DECIMAL(38, 4)) AS UNIT_COST,
CAST(PINV.DISCOUNT_AMOUNT AS DECIMAL(38, 4)) AS DISCOUNT_AMOUNT,
CAST(PINV.NET_AMOUNT AS DECIMAL(38, 4)) AS NET_AMOUNT,
CAST(PINV.TAX_AMOUNT AS DECIMAL(38, 4)) AS TAX_AMOUNT,
CAST(PINV.GROSS_AMOUNT AS DECIMAL(38, 4)) AS GROSS_AMOUNT,
CAST(PINV.BASE_UNIT_COST AS DECIMAL(38, 4)) AS BASE_UNIT_COST,
CAST(PINV.BASE_DISCOUNT_AMOUNT AS DECIMAL(38, 4)) AS BASE_DISCOUNT_AMOUNT,
CAST(PINV.BASE_NET_AMOUNT AS DECIMAL(38, 4)) AS BASE_NET_AMOUNT,
CAST(PINV.BASE_TAX_AMOUNT AS DECIMAL(38, 4)) AS BASE_TAX_AMOUNT,
CAST(PINV.BASE_GROSS_AMOUNT AS DECIMAL(38, 4)) AS BASE_GROSS_AMOUNT,
PINV.PAYMENT_TERMS,
PINV.PAYMENT_TERMS_DESCRIPTION,
PINV.LOADED_BY,
PINV.CREATED_BY,
PINV.MODIFIED_BY,
PINV.DATE_LOADED,
PINV.DATE_CREATED,
PINV.DATE_MODIFIED
FROM PINV;Sales Order Line SalesOrderLineQuery.sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
/*** Sales Orders ***/
CAST(SL.[Document Type] AS VARCHAR(32)) + '-' + SL.[Document No_] + '-' + CAST(SL.[Line No_] AS VARCHAR(32)) AS SALES_ORDER_LINE_ID,
SH.[Bill-to Customer No_] AS CUSTOMERID_INVOICE,
SH.[Sell-to Customer No_] AS CUSTOMERID_ORDER,
ISNULL(NULLIF(SH.[Currency Code], N''), GLS.[LCY Code]) AS CURRENCY_ID,
SL.No_ AS ITEM_ID,
CAST(SL.[Document Type] AS VARCHAR(32)) + '-' + SL.[Document No_] AS SALES_ORDER_ID,
CASE WHEN SL.[Location Code] <> '' THEN SL.[Location Code]
WHEN SL.[Location Code] = ''
AND SH.[Location Code] <> '' THEN SH.[Location Code]
END AS LOCATION_ID,
SH.[Salesperson Code] AS SALES_REP_ID,
SUBSTRING(SH.[Assigned User ID], CHARINDEX('\', SH.[Assigned User ID]) + 1, 50) AS CUSTOMER_SERVICE_REP_ID,
SL.[Line No_] AS LINE_NUMBER,
NULL AS DELIVERY_NUMBER,
CASE WHEN SH.[Document Type] = 1 THEN 'Sales Order'
WHEN SH.[Document Type] = 5 THEN 'Sales Return'
END AS SALES_ORDER_TYPE,
CONVERT(CHAR(10), SH.[Order Date], 121) AS ORDER_DATE,
CASE WHEN SH.Status = 0 THEN 'Planned'
WHEN SH.Status = 1 THEN 'Released'
WHEN SH.Status = 2 THEN 'Released'
WHEN SH.Status = 3 THEN 'Released'
WHEN SH.Status = 4 THEN 'Invoiced/Closed'
WHEN SH.Status = 5 THEN 'Invoiced/Closed'
WHEN SH.Status = 7 THEN 'Cancelled'
ELSE 'Unknown'
END AS ORDER_STATUS,
CASE WHEN SH.Status = 0 THEN 'Planned'
WHEN SH.Status = 1 THEN 'Released'
WHEN SH.Status = 2 THEN 'Released'
WHEN SH.Status = 3 THEN 'Released'
WHEN SH.Status = 4 THEN 'Invoiced/Closed'
WHEN SH.Status = 5 THEN 'Invoiced/Closed'
WHEN SH.Status = 7 THEN 'Cancelled'
ELSE 'Unknown'
END AS LINE_STATUS,
SL.Description AS DESCRIPTION,
CAST(SL.Quantity AS DECIMAL(38, 4)) AS QUANTITY_ORDERED,
CAST(SL.[Quantity Invoiced] AS DECIMAL(38, 4)) AS QUANTITY_INVOICED,
CAST(SL.[Outstanding Quantity] AS DECIMAL(38, 4)) AS QUANTITY_OUTSTANDING,
SL.[Unit of Measure] AS UNIT_OF_MEASURE,
SH.[VAT Bus_ Posting Group] AS TAX_CODE,
CAST(SL.[VAT _] AS DECIMAL(38, 4)) AS TAX_RATE,
SL.[Requested Delivery Date] AS DATE_DELIVERY_REQUIRED,
SL.[Planned Shipment Date] AS DATE_SHIPMENT_PLANNED,
SH.[Shipment Date] AS DATE_SHIPPED,
CAST(0 AS DECIMAL(38, 4)) AS ORDER_DISCOUNT_RATE,
CAST(SL.[Line Discount _] AS DECIMAL(38, 4)) AS LINE_DISCOUNT_RATE,
CAST(SL.[Line Discount _] AS DECIMAL(38, 4)) AS TOTAL_DISCOUNT_RATE,
CAST(SL.[Unit Cost] AS DECIMAL(38, 4)) AS SALES_UNIT_COST,
CAST(SL.[Unit Price] AS DECIMAL(38, 4)) AS SALES_UNIT_PRICE,
CAST(SL.Amount AS DECIMAL(38, 4)) AS SALES_NET_AMOUNT,
CAST(SL.[Line Discount Amount] AS DECIMAL(38, 4)) AS SALES_DISCOUNT_AMOUNT,
CAST(SL.[Amount Including VAT] - SL.Amount AS DECIMAL(38, 4)) AS SALES_TAX_AMOUNT,
CAST(SL.[Amount Including VAT] AS DECIMAL(38, 4)) AS SALES_GROSS_AMOUNT,
CAST(SL.[Unit Cost (LCY)] AS DECIMAL(38, 4)) AS BASE_UNIT_COST,
CAST(( CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Unit Price]
ELSE ( 1 / SH.[Currency Factor] ) * SL.[Unit Price]
END ) AS DECIMAL(38, 4)) AS BASE_UNIT_PRICE,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.Amount ELSE ( 1 / SH.[Currency Factor] ) * SL.Amount END AS DECIMAL(38, 4)) AS BASE_NET_AMOUNT,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Line Discount Amount]
ELSE ( 1 / SH.[Currency Factor] ) * SL.[Line Discount Amount]
END AS DECIMAL(38, 4)) AS BASE_DISCOUNT_AMOUNT,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Amount Including VAT] - SL.Amount
ELSE ( 1 / SH.[Currency Factor] ) * ( SL.[Amount Including VAT] - SL.Amount )
END AS DECIMAL(38, 4)) AS BASE_TAX_AMOUNT,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Amount Including VAT]
ELSE ( 1 / SH.[Currency Factor] ) * SL.[Amount Including VAT]
END AS DECIMAL(38, 4)) AS BASE_GROSS_AMOUNT,
CAST(SL.[Outstanding Amount] AS DECIMAL(38, 4)) AS SALES_NET_AMOUNT_OUTSTANDING,
CAST(SL.[Outstanding Amount (LCY)] AS DECIMAL(38, 4)) AS BASE_NET_AMOUNT_OUTSTANDING,
NULL AS SOURCE_PRICE,
NULL AS PRICE_SOURCE,
SP.Name AS SALES_REP_NAME,
U.[Full Name] AS CUSTOMER_SERVICE_REP_NAME,
NULL AS VESSEL_NAME,
SH.[Ship-to City] AS DEL_LOCATION,
SH.[Shipment Method Code] + ' - ' + SM.Description AS DELIVERY_TERMS,
SH.[External Document No_] AS CUST_REF,
NULL AS CANCEL_REASON,
CONVERT(CHAR(19), SH.[Document Date], 121) AS DATE_LAST_MODIFIED
FROM dbo.[Navision Company$Sales Header] AS SH
LEFT OUTER JOIN dbo.[Navision Company$Sales Line] AS SL ON SH.[Document Type] = SL.[Document Type]
AND SH.No_ = SL.[Document No_]
OUTER APPLY dbo.[Navision Company$General Ledger Setup] AS GLS
LEFT OUTER JOIN dbo.[Navision Company$Salesperson_Purchaser] AS SP ON SP.Code = SH.[Salesperson Code]
LEFT OUTER JOIN dbo.[Navision Company$Shipment Method] AS SM ON SM.Code = SH.[Shipment Method Code]
LEFT OUTER JOIN dbo.[User] AS U ON SH.[Assigned User ID] = U.[User Name]
WHERE SH.[Document Type] IN ( 1, 5 )
AND SL.Amount <> 0
UNION ALL
SELECT
/*** Service Orders ***/
CAST(SL.[Document Type] AS VARCHAR(32)) + '-' + SL.[Document No_] + '-' + CAST(SL.[Line No_] AS VARCHAR(32)) AS SALES_ORDER_LINE_ID,
SH.[Bill-to Customer No_] AS CUSTOMERID_INVOICE,
SH.[Bill-to Customer No_] AS CUSTOMERID_ORDER,
ISNULL(NULLIF(SH.[Currency Code], N''), GLS.[LCY Code]) AS CURRENCY_ID,
SL.No_ AS ITEM_ID,
CAST(SL.[Document Type] AS VARCHAR(32)) + '-' + SL.[Document No_] AS SALES_ORDER_ID,
CASE WHEN SL.[Location Code] <> '' THEN SL.[Location Code]
WHEN SL.[Location Code] = ''
AND SH.[Location Code] <> '' THEN SH.[Location Code]
END AS LOCATION_ID,
SH.[Salesperson Code] AS SALES_REP_ID,
SUBSTRING(SH.[Assigned User ID], CHARINDEX('\', SH.[Assigned User ID]) + 1, 50) AS CUSTOMER_SERVICE_REP_ID,
SL.[Line No_] AS LINE_NUMBER,
NULL AS DELIVERY_NUMBER,
CASE WHEN SH.[Document Type] = 1 THEN 'Service Sales Order'
WHEN SH.[Document Type] = 5 THEN 'Service Sales Return'
END AS SALES_ORDER_TYPE,
CONVERT(CHAR(10), SH.[Order Date], 121) AS ORDER_DATE,
CASE WHEN SH.Status = 0 THEN 'Planned'
WHEN SH.Status = 1 THEN 'Released'
WHEN SH.Status = 2 THEN 'Released'
WHEN SH.Status = 3 THEN 'Released'
WHEN SH.Status = 4 THEN 'Invoiced/Closed'
WHEN SH.Status = 5 THEN 'Invoiced/Closed'
WHEN SH.Status = 7 THEN 'Cancelled'
ELSE 'Unknown'
END AS ORDER_STATUS,
CASE WHEN SH.Status = 0 THEN 'Planned'
WHEN SH.Status = 1 THEN 'Released'
WHEN SH.Status = 2 THEN 'Released'
WHEN SH.Status = 3 THEN 'Released'
WHEN SH.Status = 4 THEN 'Invoiced/Closed'
WHEN SH.Status = 5 THEN 'Invoiced/Closed'
WHEN SH.Status = 7 THEN 'Cancelled'
ELSE 'Unknown'
END AS LINE_STATUS,
SL.Description AS DESCRIPTION,
CAST(SL.Quantity AS DECIMAL(38, 4)) AS QUANTITY_ORDERED,
CAST(SL.[Quantity Invoiced] AS DECIMAL(38, 4)) AS QUANTITY_INVOICED,
CAST(SL.[Outstanding Quantity] AS DECIMAL(38, 4)) AS QUANTITY_OUTSTANDING,
SL.[Unit of Measure] AS UNIT_OF_MEASURE,
SH.[VAT Bus_ Posting Group] AS TAX_CODE,
CAST(SL.[VAT _] AS DECIMAL(38, 4)) AS TAX_RATE,
SL.[Requested Delivery Date] AS DATE_DELIVERY_REQUIRED,
SH.[Expected Finishing Date] AS DATE_SHIPMENT_PLANNED,
SH.[Finishing Date] AS DATE_SHIPPED,
CAST(0 AS DECIMAL(38, 4)) AS ORDER_DISCOUNT_RATE,
CAST(SL.[Line Discount _] AS DECIMAL(38, 4)) AS LINE_DISCOUNT_RATE,
CAST(SL.[Line Discount _] AS DECIMAL(38, 4)) AS TOTAL_DISCOUNT_RATE,
CAST(SL.[Unit Cost] AS DECIMAL(38, 4)) AS SALES_UNIT_COST,
CAST(SL.[Unit Price] AS DECIMAL(38, 4)) AS SALES_UNIT_PRICE,
CAST(SL.Amount AS DECIMAL(38, 4)) AS SALES_NET_AMOUNT,
CAST(SL.[Line Discount Amount] AS DECIMAL(38, 4)) AS SALES_DISCOUNT_AMOUNT,
CAST(SL.[Amount Including VAT] - SL.Amount AS DECIMAL(38, 4)) AS SALES_TAX_AMOUNT,
CAST(SL.[Amount Including VAT] AS DECIMAL(38, 4)) AS SALES_GROSS_AMOUNT,
CAST(SL.[Unit Cost (LCY)] AS DECIMAL(38, 4)) AS BASE_UNIT_COST,
CAST(( CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Unit Price]
ELSE ( 1 / SH.[Currency Factor] ) * SL.[Unit Price]
END ) AS DECIMAL(38, 4)) AS BASE_UNIT_PRICE,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.Amount ELSE ( 1 / SH.[Currency Factor] ) * SL.Amount END AS DECIMAL(38, 4)) AS BASE_NET_AMOUNT,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Line Discount Amount]
ELSE ( 1 / SH.[Currency Factor] ) * SL.[Line Discount Amount]
END AS DECIMAL(38, 4)) AS BASE_DISCOUNT_AMOUNT,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Amount Including VAT] - SL.Amount
ELSE ( 1 / SH.[Currency Factor] ) * ( SL.[Amount Including VAT] - SL.Amount )
END AS DECIMAL(38, 4)) AS BASE_TAX_AMOUNT,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Amount Including VAT]
ELSE ( 1 / SH.[Currency Factor] ) * SL.[Amount Including VAT]
END AS DECIMAL(38, 4)) AS BASE_GROSS_AMOUNT,
CAST(SL.[Outstanding Amount] AS DECIMAL(38, 4)) AS SALES_NET_AMOUNT_OUTSTANDING,
CAST(SL.[Outstanding Amount (LCY)] AS DECIMAL(38, 4)) AS BASE_NET_AMOUNT_OUTSTANDING,
NULL AS SOURCE_PRICE,
NULL AS PRICE_SOURCE,
SP.Name AS SALES_REP_NAME,
U.[Full Name] AS CUSTOMER_SERVICE_REP_NAME,
NULL AS VESSEL_NAME,
SH.[Ship-to City] AS DEL_LOCATION,
SH.[Shipment Method Code] + ' - ' + SM.Description AS DELIVERY_TERMS,
SH.[Contract No_] AS CUST_REF,
NULL AS CANCEL_REASON,
CONVERT(CHAR(19), SH.[Document Date], 121) AS DATE_LAST_MODIFIED
FROM dbo.[Navision Company$Service Header] AS SH
LEFT OUTER JOIN dbo.[Navision Company$Service Line] AS SL ON SH.[Document Type] = SL.[Document Type]
AND SH.No_ = SL.[Document No_]
OUTER APPLY dbo.[Navision Company$General Ledger Setup] AS GLS
LEFT OUTER JOIN dbo.[Navision Company$Salesperson_Purchaser] AS SP ON SP.Code = SH.[Salesperson Code]
LEFT OUTER JOIN dbo.[Navision Company$Shipment Method] AS SM ON SM.Code = SH.[Shipment Method Code]
LEFT OUTER JOIN dbo.[User] AS U ON SH.[Assigned User ID] = U.[User Name]
WHERE SH.[Document Type] IN ( 1, 5 )
AND SL.Amount <> 0;Sales Quotation Line SalesQuotationLineQuery.sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT SL.[Document No_] + N'-' + CAST(SL.[Line No_] AS NVARCHAR(32)) AS SALES_QUOTE_LINE_ID,
SH.[Bill-to Customer No_] AS CUSTOMERID_INVOICE,
SH.[Sell-to Customer No_] AS CUSTOMERID_QUOTE,
SUBSTRING(SH.[Assigned User ID], CHARINDEX('\', SH.[Assigned User ID]) + 1, 50) AS CUSTOMER_SERVICE_REP_ID,
ISNULL(NULLIF(SH.[Currency Code], N''), GLS.[LCY Code]) AS CURRENCY_ID,
SL.No_ AS ITEM_ID,
SH.[Salesperson Code] AS SALES_REP_ID,
SL.[Location Code] AS LOCATION_ID,
SL.[Document No_] AS SALES_QUOTE_NO,
SL.[Line No_] AS LINE_NUMBER,
NULL AS DELIVERY_NUMBER,
CONVERT(CHAR(10), SH.[Order Date], 121) AS SALES_QUOTE_DATE,
CASE WHEN SH.Status = 0 THEN 'Planned'
WHEN SH.Status = 1 THEN 'Released'
WHEN SH.Status = 2 THEN 'Released'
WHEN SH.Status = 3 THEN 'Released'
ELSE 'Unknown'
END AS QUOTATION_STATUS,
CASE WHEN SH.Status = 0 THEN 'Planned'
WHEN SH.Status = 1 THEN 'Released'
WHEN SH.Status = 2 THEN 'Released'
WHEN SH.Status = 3 THEN 'Released'
ELSE 'Unknown'
END AS LINE_STATUS,
SL.Description AS DESCRIPTION,
CAST(SL.Quantity AS DECIMAL(38, 4)) AS QUANTITY_QUOTED,
SL.[Unit of Measure] AS UNIT_OF_MEASURE,
SH.[VAT Bus_ Posting Group] AS TAX_CODE,
CAST(SL.[VAT _] AS DECIMAL(38, 4)) AS TAX_RATE,
SL.[Requested Delivery Date] AS DATE_DELIVERY_REQUIRED,
SL.[Planned Shipment Date] AS DATE_SHIPMENT_PLANNED,
CAST(SL.[Line Discount _] AS DECIMAL(38, 4)) AS LINE_DISCOUNT_RATE,
CAST(SH.[Invoice Discount Value] AS DECIMAL(38, 4)) AS HEADER_DISCOUNT_RATE,
CAST(SL.[Line Discount _] AS DECIMAL(38, 4)) AS TOTAL_DISCOUNT_RATE,
CAST(SL.[Unit Cost] AS DECIMAL(38, 4)) AS SALES_UNIT_COST,
CAST(SL.[Unit Price] AS DECIMAL(38, 4)) AS SALES_UNIT_PRICE,
CAST(SL.[Line Discount Amount] AS DECIMAL(38, 4)) AS SALES_DISCOUNT_AMOUNT,
CAST(SL.Amount AS DECIMAL(38, 4)) AS SALES_NET_AMOUNT,
CAST(SL.[Amount Including VAT] - SL.Amount AS DECIMAL(38, 4)) AS SALES_TAX_AMOUNT,
CAST(SL.[Amount Including VAT] AS DECIMAL(38, 4)) AS SALES_GROSS_AMOUNT,
CAST(SL.[Unit Cost (LCY)] AS DECIMAL(38, 4)) AS BASE_UNIT_COST,
CAST(CASE WHEN SL.[Unit Price] = 0 THEN SL.Amount ELSE ( 1 / SL.[Unit Price] ) * SL.Amount END AS DECIMAL(38, 4)) AS BASE_UNIT_PRICE,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Line Discount Amount]
ELSE ( 1 / SH.[Currency Factor] ) * SL.[Line Discount Amount]
END AS DECIMAL(38, 4)) AS BASE_DISCOUNT_AMOUNT,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.Amount ELSE ( 1 / SH.[Currency Factor] ) * SL.Amount END AS DECIMAL(38, 4)) AS BASE_NET_AMOUNT,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Amount Including VAT] - SL.Amount
ELSE ( 1 / SH.[Currency Factor] ) * ( SL.[Amount Including VAT] - SL.Amount )
END AS DECIMAL(38, 4)) AS BASE_TAX_AMOUNT,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Amount Including VAT]
ELSE ( 1 / SH.[Currency Factor] ) * SL.[Amount Including VAT]
END AS DECIMAL(38, 4)) AS BASE_GROSS_AMOUNT,
SP.Name AS SALES_REP_NAME,
U.[Full Name] AS CUSTOMER_SERVICE_REP_NAME,
NULL AS VESSEL_NAME,
SH.[Ship-to City] AS DELIVERY_LOCATION,
SH.[Shipment Method Code] + ' - ' + SM.Description AS DELIVERY_TERMS,
SH.[External Document No_] AS CUSTOMER_REFERENCE,
NULL AS CANCEL_REASON,
NULL AS PROBABILITY,
NULL AS LOST_TO,
NULL AS WIN_LOSE_REASON,
CONVERT(CHAR(19), SH.[Document Date], 121) AS DATE_LAST_MODIFIED
FROM dbo.[Navision Company$Sales Header] AS SH
LEFT JOIN dbo.[Navision Company$Sales Line] AS SL ON SH.[Document Type] = SL.[Document Type]
AND SH.No_ = SL.[Document No_]
LEFT JOIN dbo.[Navision Company$General Ledger Setup] AS GLS ON 1 = 1
LEFT JOIN dbo.[Navision Company$Salesperson_Purchaser] AS SP ON SH.[Salesperson Code] = SP.Code
LEFT JOIN dbo.[User] AS U ON SH.[Assigned User ID] = U.[User Name]
LEFT JOIN dbo.[Navision Company$Shipment Method] AS SM ON SH.[Shipment Method Code] = SM.Code
AND SH.[Document Type] = 0
AND SL.Amount <> 0
UNION ALL
SELECT SL.[Document No_] + N'-' + CAST(SL.[Line No_] AS NVARCHAR(32)) AS SALES_QUOTE_LINE_ID,
SH.[Bill-to Customer No_] AS CUSTOMERID_INVOICE,
SH.[Bill-to Customer No_] AS CUSTOMERID_QUOTE,
SUBSTRING(SH.[Assigned User ID], CHARINDEX('\', SH.[Assigned User ID]) + 1, 50) AS CUSTOMER_SERVICE_REP_ID,
ISNULL(NULLIF(SH.[Currency Code], N''), GLS.[LCY Code]) AS CURRENCY_ID,
SL.No_ AS ITEM_ID,
SH.[Salesperson Code] AS SALES_REP_ID,
NULL AS LOCATION_ID,
SL.[Document No_] AS SALES_QUOTE_NO,
SL.[Line No_] AS LINE_NUMBER,
NULL AS DELIVERY_NUMBER,
CONVERT(CHAR(10), SH.[Order Date], 121) AS SALES_QUOTE_DATE,
CASE WHEN SH.Status = 0 THEN 'Planned'
WHEN SH.Status = 1 THEN 'Released'
WHEN SH.Status = 2 THEN 'Released'
WHEN SH.Status = 3 THEN 'Released'
ELSE 'Unknown'
END AS QUOTATION_STATUS,
CASE WHEN SH.Status = 0 THEN 'Planned'
WHEN SH.Status = 1 THEN 'Released'
WHEN SH.Status = 2 THEN 'Released'
WHEN SH.Status = 3 THEN 'Released'
ELSE 'Unknown'
END AS LINE_STATUS,
SL.Description AS DESCRIPTION,
CAST(SL.Quantity AS DECIMAL(38, 4)) AS QUANTITY_QUOTED,
SL.[Unit of Measure] AS UNIT_OF_MEASURE,
SH.[VAT Bus_ Posting Group] AS TAX_CODE,
CAST(SL.[VAT _] AS DECIMAL(38, 4)) AS TAX_RATE,
SL.[Requested Delivery Date] AS DATE_DELIVERY_REQUIRED,
SH.[Expected Finishing Date] AS DATE_SHIPMENT_PLANNED,
CAST(SL.[Line Discount _] AS DECIMAL(38, 4)) AS LINE_DISCOUNT_RATE,
CAST(SH.[Invoice Discount Value] AS DECIMAL(38, 4)) AS HEADER_DISCOUNT_RATE,
CAST(SL.[Line Discount _] AS DECIMAL(38, 4)) AS TOTAL_DISCOUNT_RATE,
CAST(SL.[Unit Cost] AS DECIMAL(38, 4)) AS SALES_UNIT_COST,
CAST(SL.[Unit Price] AS DECIMAL(38, 4)) AS SALES_UNIT_PRICE,
CAST(SL.[Line Discount Amount] AS DECIMAL(38, 4)) AS SALES_DISCOUNT_AMOUNT,
CAST(SL.Amount AS DECIMAL(38, 4)) AS SALES_NET_AMOUNT,
CAST(SL.[Amount Including VAT] - SL.Amount AS DECIMAL(38, 4)) AS SALES_TAX_AMOUNT,
CAST(SL.[Amount Including VAT] AS DECIMAL(38, 4)) AS SALES_GROSS_AMOUNT,
CAST(SL.[Unit Cost (LCY)] AS DECIMAL(38, 4)) AS BASE_UNIT_COST,
CAST(CASE WHEN SL.[Unit Price] = 0 THEN SL.Amount ELSE ( 1 / SL.[Unit Price] ) * SL.Amount END AS DECIMAL(38, 4)) AS BASE_UNIT_PRICE,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Line Discount Amount]
ELSE ( 1 / SH.[Currency Factor] ) * SL.[Line Discount Amount]
END AS DECIMAL(38, 4)) AS BASE_DISCOUNT_AMOUNT,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.Amount ELSE ( 1 / SH.[Currency Factor] ) * SL.Amount END AS DECIMAL(38, 4)) AS BASE_NET_AMOUNT,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Amount Including VAT] - SL.Amount
ELSE ( 1 / SH.[Currency Factor] ) * ( SL.[Amount Including VAT] - SL.Amount )
END AS DECIMAL(38, 4)) AS BASE_TAX_AMOUNT,
CAST(CASE WHEN SH.[Currency Factor] = 0 THEN SL.[Amount Including VAT]
ELSE ( 1 / SH.[Currency Factor] ) * SL.[Amount Including VAT]
END AS DECIMAL(38, 4)) AS BASE_GROSS_AMOUNT,
SP.Name AS SALES_REP_NAME,
U.[Full Name] AS CUSTOMER_SERVICE_REP_NAME,
NULL AS VESSEL_NAME,
SH.[Ship-to City] AS DELIVERY_LOCATION,
SH.[Shipment Method Code] + ' - ' + SM.Description AS DELIVERY_TERMS,
SH.[Contract No_] AS CUSTOMER_REFERENCE,
NULL AS CANCEL_REASON,
NULL AS PROBABILITY,
NULL AS LOST_TO,
NULL AS WIN_LOSE_REASON,
CONVERT(CHAR(19), SH.[Document Date], 121) AS DATE_LAST_MODIFIED
FROM dbo.[Navision Company$Service Header] AS SH
LEFT JOIN dbo.[Navision Company$Service Line] AS SL ON SH.[Document Type] = SL.[Document Type]
AND SH.No_ = SL.[Document No_]
LEFT JOIN dbo.[Navision Company$General Ledger Setup] AS GLS ON 1 = 1
LEFT JOIN dbo.[Navision Company$Salesperson_Purchaser] AS SP ON SH.[Salesperson Code] = SP.Code
LEFT JOIN dbo.[User] AS U ON SH.[Assigned User ID] = U.[User Name]
LEFT JOIN dbo.[Navision Company$Shipment Method] AS SM ON SH.[Shipment Method Code] = SM.Code
WHERE SH.[Document Type] = 0
AND SL.Amount <> 0;Supplier Ledger Transactions SupplierLedgerTransactionQuery.sql
;WITH
I AS
( SELECT PIH.No_,
PIH.[Payment Terms Code],
2 AS DocumentType
FROM dbo.[Navision Company$Purch_ Inv_ Header] AS PIH
UNION
SELECT PCMH.No_,
PCMH.[Payment Terms Code],
3 AS DocumentType
FROM dbo.[Navision Company$Purch_ Cr_ Memo Hdr_] AS PCMH ),
PT AS
( SELECT PT.Code, PT.Description FROM dbo.[Navision Company$Payment Terms] AS PT )
SELECT DVLE.[Entry No_] AS SUPPLIER_LEDGER_TRASANCTION_ID,
ISNULL(NULLIF(DVLE.[Currency Code], N''), GLS.[LCY Code]) AS CURRENCY_ID,
DVLE.[Vendor No_] AS SUPPLIER_ID,
DVLE.[Vendor Ledger Entry No_] AS SUPPLIER_LEDGER_ID,
CONVERT(CHAR(10), DVLE.[Posting Date], 121) AS DATE_POSTED,
CONVERT(CHAR(10), DVLE.[Initial Entry Due Date], 121) AS DATE_DUE,
VLE.Description AS DESCRIPTION,
DVLE.[Document No_] AS DOCUMENT_NO,
CASE WHEN DVLE.[Document Type] = 1 THEN 'Payment'
WHEN DVLE.[Document Type] = 2 THEN 'Invoice'
WHEN DVLE.[Document Type] = 3 THEN 'Credit Memo'
WHEN DVLE.[Document Type] = 4 THEN 'Finance Charge Memo'
WHEN DVLE.[Document Type] = 5 THEN 'Reminder'
WHEN DVLE.[Document Type] = 6 THEN 'Refund'
ELSE NULL
END AS DOCUMENT_TYPE,
CASE WHEN DVLE.[Entry Type] = 1 THEN 'Initial Entry'
WHEN DVLE.[Entry Type] = 2 THEN 'Application'
WHEN DVLE.[Entry Type] = 3 THEN 'Unrealized Loss'
WHEN DVLE.[Entry Type] = 4 THEN 'Unrealized Gain'
WHEN DVLE.[Entry Type] = 5 THEN 'Realized Loss'
WHEN DVLE.[Entry Type] = 6 THEN 'Realized Gain'
WHEN DVLE.[Entry Type] = 7 THEN 'Payment Discount'
WHEN DVLE.[Entry Type] = 8 THEN 'Payment Discount (VAT Excl.)'
WHEN DVLE.[Entry Type] = 9 THEN 'Payment Discount (VAT Adjustment)'
WHEN DVLE.[Entry Type] = 10 THEN 'Appln. Rounding'
WHEN DVLE.[Entry Type] = 11 THEN 'Correction of Remaining Amount'
WHEN DVLE.[Entry Type] = 12 THEN 'Payment Tolerance'
WHEN DVLE.[Entry Type] = 13 THEN 'Payment Discount Tolerance'
WHEN DVLE.[Entry Type] = 14 THEN 'Payment Tolerance (VAT Excl.)'
WHEN DVLE.[Entry Type] = 15 THEN 'Payment Tolerance (VAT Adjustment)'
WHEN DVLE.[Entry Type] = 16 THEN 'Payment Discount Tolerance (VAT Excl.)'
WHEN DVLE.[Entry Type] = 17 THEN 'Payment Discount Tolerance (VAT Adjustment)'
ELSE NULL
END AS ENTRY_TYPE,
PT.Code AS PAYMENT_TERMS,
PT.Description AS PAYMENT_TERMS_DESCRIPTION,
DVLE.Amount AS AMOUNT,
DVLE.[Debit Amount] AS AMOUNT_DEBIT,
DVLE.[Credit Amount] AS AMOUNT_CREDIT,
DVLE.[Amount (LCY)] AS AMOUNT_BASE,
DVLE.[Debit Amount (LCY)] AS AMOUNT_DEBIT_BASE,
DVLE.[Credit Amount (LCY)] AS AMOUNT_CREDIT_BASE,
CASE WHEN VLE.[Vendor Posting Group] = 'INTERNAL' THEN CAST(1 AS BIT)ELSE CAST(0 AS BIT)END AS IS_INTERNAL,
VLE.[Open] AS IS_OPEN,
NULL AS LOADED_BY,
U.[User Name] AS CREATED_BY,
U.[User Name] AS MODIFIED_BY,
CONVERT(CHAR(19), GETDATE(), 121) AS DATE_LOADED,
CONVERT(CHAR(19), DVLE.[Posting Date], 121) AS DATE_CREATED,
CONVERT(CHAR(19), DVLE.[Posting Date], 121) AS DATE_MODIFIED
FROM dbo.[Navision Company$Detailed Vendor Ledg_ Entry] AS DVLE
INNER JOIN dbo.[Navision Company$Vendor Ledger Entry] AS VLE ON DVLE.[Vendor Ledger Entry No_] = VLE.[Entry No_]
LEFT OUTER JOIN dbo.[User] AS U ON VLE.[User ID] = U.[User Name]
LEFT OUTER JOIN I ON DVLE.[Document No_] = I.No_
AND DVLE.[Document Type] = I.DocumentType
LEFT OUTER JOIN PT ON I.[Payment Terms Code] = PT.Code
OUTER APPLY dbo.[Navision Company$General Ledger Setup] AS GLS
ORDER BY DVLE.[Entry No_];Supplier Parameters SupplierParameterQuery.sql
SELECT V.No_ AS SUPPLIER_PARAMETER_ID,
V.No_ AS SUPPLIER_ID,
NULL AS CREDIT_LIMIT,
CASE WHEN V.Blocked = 0 THEN CAST(0 AS BIT)ELSE CAST(1 AS BIT)END AS IS_CREDIT_BLOCKED,
V.[Payment Terms Code] AS PAYMENT_TERMS_CODE,
PT.Description AS PAYMENT_TERMS_DESCRIPTION,
FORMAT(V.[Last Date Modified], 'yyyy-MM-dd HH:mm:ss') AS DATE_LAST_MODIFIED
FROM dbo.[Navision Company$Vendor] AS V
LEFT OUTER JOIN dbo.[Navision Company$Payment Terms] AS PT ON V.[Payment Terms Code] = PT.Code;Suppliers SupplierQuery.sql
SELECT V.No_ AS SUPPLIER_ID,
NULLIF(V.[Purchaser Code], '') AS BUYER_ID,
NULLIF(V.[Country_Region Code], '') AS COUNTRY_ID,
ISNULL(NULLIF(V.[Currency Code], N''), GLS.[LCY Code]) AS CURRENCY_ID,
NULL AS DEFAULT_ADDRESS_ID,
NULLIF(SP.Name, '') AS BUYER_NAME,
V.Name AS SUPPLIER_NAME,
CONVERT(CHAR(10), PIH.FirstOrderDate, 121) AS ACCOUNT_OPENED_DATE,
NULLIF(V.[IC Partner Code], '') AS ASSOCIATION_REFERENCE,
NULLIF(V.[Phone No_], '') AS PHONE_NUMBER,
NULLIF(V.[Home Page], '') AS WEBSITE_URL,
NULLIF(V.Address, '') AS ADDRESS1,
NULLIF(V.[Address 2], '') AS ADDRESS2,
NULL AS ADDRESS3,
NULLIF(V.City, '') AS CITY,
NULLIF(V.County, '') AS STATE_PROVINCE,
NULL AS COUNTRY,
NULLIF(V.[Post Code], '') AS POSTAL_CODE,
CASE WHEN V.[Vendor Posting Group] = '' OR V.[Vendor Posting Group] = 'INTERNAL' THEN 1 ELSE 0 END AS IS_INTERNAL,
NULL AS LOADED_BY,
NULL AS CREATED_BY,
NULL AS MODIFIED_BY,
CONVERT(CHAR(19), GETDATE(), 121) AS DATE_LOADED,
CONVERT(CHAR(19), PIH.FirstOrderDate, 121) AS DATE_CREATED,
CONVERT(CHAR(19), V.[Last Date Modified], 121) AS DATE_MODIFIED
FROM dbo.[Navision Company$Vendor] AS V
LEFT OUTER JOIN dbo.[Navision Company$Salesperson_Purchaser] AS SP ON V.[Purchaser Code] = SP.Code
LEFT OUTER JOIN( SELECT PIH.[Pay-to Vendor No_],
MIN(PIH.[Order Date]) AS FirstOrderDate
FROM dbo.[Navision Company$Purch_ Inv_ Header] AS PIH
WHERE PIH.[Pay-to Vendor No_] <> ''
AND PIH.[Order Date] > '1753-01-01 00:00:00.000'
GROUP BY PIH.[Pay-to Vendor No_] ) AS PIH ON V.No_ = PIH.[Pay-to Vendor No_]
OUTER APPLY dbo.[Navision Company$General Ledger Setup] AS GLS
WHERE V.No_ IS NOT NULL
AND V.No_ <> '';If you want more on Navision’s tables and schema, head over to dynamicsdocs.com.