Interprise Suite Community Forum is your Home for Tips, Tricks, News, Assistance, and Information about Interprise Suite and its Related Products.

SELECT     a.InvoiceCode, cpv.AmountPaidRate AS Value, cpv.PaymentMethod, a.Ref1 AS Reference, a.Ref2 AS Reference2, taxRates.TaxRate AS TaxRate1,
                      a.TaxRate AS TaxValue, dbo.CreateAddress(a.BillToName, a.BillToCity, a.BillToState, a.BillToAddress, a.BillToPostalCode, a.BillToCounty, a.BillToCountry)
                      AS BillToNameAndAddress, dbo.CreateAddress(a.ShipToName, a.ShipToCity, a.ShipToState, a.ShipToAddress, a.ShipToPostalCode, a.ShipToCounty, a.ShipToCountry)
                      AS ShipToNameAndAddress, CASE WHEN ISNULL(a.BillToPhoneExtension, '') = '' THEN ISNULL(a.BillToPhone, '') ELSE ISNULL(a.BillToPhone, '')
                      + ' ext ' + ISNULL(a.BillToPhoneExtension, '') END AS BillToPhoneNumber, CASE WHEN ISNULL(a.ShipToPhoneExtension, '') = '' THEN ISNULL(a.ShipToPhone, '')
                      ELSE ISNULL(a.ShipToPhone, '') + ' ext ' + ISNULL(a.ShipToPhoneExtension, '') END AS ShipToPhoneNumber, g.TaxNumber AS BillToTaxNumber,
                      h.TaxNumber AS ShipToTaxNumber, a.InvoiceDate, a.DueDate, a.CurrencyCode, a.PaymentTerm, a.ShippingMethod, x_1.ItemName, x_1.LineNum,
                      CASE WHEN x_1.ItemName = '' THEN NULL ELSE x_1.QuantityShipped END AS QuantityShipped, CASE WHEN x_1.ItemName = '' THEN NULL
                      ELSE x_1.QuantityOrdered END AS QuantityOrdered, CASE WHEN x_1.ItemName = '' THEN NULL ELSE x_1.QuantityBackOrdered END AS QuantityBackOrdered,
                      CASE WHEN x_1.ItemName = '' THEN NULL ELSE x_1.UnitMeasureCode END AS UnitMeasureCode, x_1.ItemDescription, x_1.TaxCode,
                      CASE WHEN x_1.ItemName = '' THEN NULL ELSE x_1.SalesTaxAmountRate END AS SalesTaxAmountRate, CASE WHEN x_1.ItemName = '' THEN NULL
                      ELSE x_1.SalesPriceRate END AS SalesPriceRate, CASE WHEN x_1.ItemName = '' THEN NULL ELSE x_1.Discount END AS DiscountRate,
                      CASE WHEN x_1.ItemName = '' THEN NULL ELSE x_1.NetPriceRate END AS NetPriceRate, CASE WHEN x_1.ItemName = '' THEN NULL
                      ELSE x_1.ExtPriceRate END AS ExtPriceRate, a.SubTotalRate, a.FreightRate, a.OtherRate, a.TaxRate, a.TotalRate, a.AppliedCreditRate,
                      ISNULL(a.CouponDiscountRate, 0) + ISNULL(a.AmountPaidRate, 0) AS AmountPaidRate, a.BalanceRate, a.SubTotalRate + a.FreightRate AS NetValue, c.EntityName,
                      c.EntityEmail, c.EntityFax, c.EntityFaxExtension, c.ContactCode, c.ContactFullName, c.Email1 AS Email, c.BusinessFax, c.BusinessFaxExtension, a.IsPrinted,
                      a.PrintCount, a.POCode, ISNULL(EE.WebSiteCode, N'') AS WebSiteCode, x_1.CustomerPO,
                      'All goods remain the property of' + ' ' + d.CompanyName + ' ' + 'until paid for in full. Standard terms and conditions of sale apply.' AS NameOfCompany,
                      CASE WHEN ISNULL(a.WebSiteCode, '') = '' THEN d .CompanyName ELSE EE.WebSiteDescription END AS CompanyName, CASE WHEN EE.WebsiteLogo IS NULL
                      THEN NULL ELSE EE.WebSiteLogo END AS CompanyLogo
FROM         dbo.CustomerInvoiceView AS a LEFT OUTER JOIN
                          (SELECT     a.WarehouseCode, a.Warehouse, a.InvoiceCode, a.LineNum + 0.0 AS LineNum, ISNULL(a.ItemName, N'') + NCHAR(13)
                                                   + CASE WHEN ISNULL(a.CustomerItemCode, '') = '' THEN '' ELSE '(' + ISNULL(a.CustomerItemCode, '') + ')' END AS ItemName, a.QuantityOrdered,
                                                   a.QuantityAllocated, CASE WHEN ISNULL(a.IsInventoryDescription, 0) = 0 THEN a.InventoryDescription + NCHAR(13)
                                                   + '(' + a.ItemDescription + ')' ELSE a.ItemDescription END AS ItemDescription, a.UnitMeasureCode, a.TaxCode, 0 AS ItemKitDetailLineNum, a.ItemType,
                                                   a.QuantityShipped, a.QuantityBackOrdered, a.SalesTaxAmountRate, a.SalesPriceRate, a.Discount, a.NetPriceRate, a.ExtPriceRate, a.CustomerPO
                            FROM          dbo.CustomerInvoice AS b INNER JOIN
                                                   dbo.CustomerInvoiceDetailView AS a ON b.InvoiceCode = a.InvoiceCode
                            WHERE      (a.ItemType IN ('Assembly', 'Matrix Item', 'Stock', 'Kit', 'Non-Stock', 'Electronic Download', 'Service')) AND (b.Type = 'Invoice')
                            UNION ALL
                            SELECT     a.WarehouseCode, a.Warehouse, a.InvoiceCode, a.LineNum + 0.1 AS LineNum, '' AS ItemName, NULL AS QuantityOrdered, NULL AS QuantityAllocated,
                                                  CASE WHEN ISNULL(a.IsInventoryDescription, 0) = 0 THEN ISNULL(' - ' + b.ItemDescription, '') ELSE ISNULL(' - ' + b.ItemDescription, '')
                                                  END AS ItemDescription, '' AS UnitMeasureCode, '' AS TaxCode, b.ItemKitDetailLineNum, a.ItemType, NULL AS QuantityShipped, NULL
                                                  AS QuantityBackOrdered, NULL AS SalesTaxAmountRate, NULL AS SalesPriceRate, NULL AS Discount, NULL AS NetPriceRate, NULL AS ExtPriceRate,
                                                  a.CustomerPO
                            FROM         dbo.CustomerInvoice AS c INNER JOIN
                                                  dbo.CustomerInvoiceDetailView AS a ON a.InvoiceCode = c.InvoiceCode INNER JOIN
                                                  dbo.CustomerItemKitDetailView AS b ON a.ItemCode = b.ItemKitCode AND a.InvoiceCode = b.DocumentCode AND a.LineNum = b.LineNum + '9999'
                            WHERE     (c.Type = 'Invoice')) AS x_1 ON a.InvoiceCode = x_1.InvoiceCode LEFT OUTER JOIN
                          (SELECT     a.TaxCode, SUM(c.TaxRate) AS TaxRate
                            FROM          dbo.SystemTaxScheme AS a INNER JOIN
                                                   dbo.SystemTaxSchemeDetail AS b ON a.TaxCode = b.TaxCode INNER JOIN
                                                   dbo.SystemTaxDetail AS c ON b.TaxDetailCode = c.TaxDetailCode
                            GROUP BY a.TaxCode) AS taxRates ON x_1.TaxCode = taxRates.TaxCode LEFT OUTER JOIN
                          (SELECT     x.SalesOrderCode, x.PaymentMethod, SUM(x.AmountPaidRate) AS AmountPaidRate
                            FROM          dbo.CustomerPaymentView AS x INNER JOIN
                                                   dbo.CustomerTransactionReceipt AS y ON x.SalesOrderCode = y.DocumentCode AND x.ReceivableCode = y.ReceivableCode
                            GROUP BY x.SalesOrderCode, x.PaymentMethod) AS cpv ON a.InvoiceCode = cpv.SalesOrderCode LEFT OUTER JOIN
                      dbo.CRMContactView AS c ON a.ContactCode = c.ContactCode INNER JOIN
                      dbo.Customer AS g ON a.BillToCode = g.CustomerCode INNER JOIN
                      dbo.CustomerShipTo AS h ON a.ShipToCode = h.ShipToCode LEFT OUTER JOIN
                          (SELECT     WebSiteCode, WebSiteLogo, WebSiteDescription
                            FROM          dbo.WebSite) AS EE ON a.WebSiteCode = EE.WebSiteCode CROSS JOIN
                      dbo.SystemCompanyInformation AS d
WHERE     (a.Type = 'Invoice')