/****** Object: StoredProcedure [dbo].[usp_SNE_Invoices_Invoice] Script Date: 17/09/2020 12:32:47 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO [dbo].[Grant_All_User_Tables] CREATE PROC [dbo].[usp_SNE_Invoices_Invoice] @SP_InvoiceHistoryID AS CHAR(20) AS --Used by: -- --MNT30000 - Invoice.rpt --MNT30001 - Delivery Docket.rpt --MNT30002 - Picking Sheet.rpt --MNT30005 - Order Confirmation.rpt --This sp appends the value of CKSafetyDivision,COmmentLine T/F, HistoryText, DebtorClassifrication, BillWhenComplete, Invoice InitDate, CashSaleDebtor to the standard Jiwa sp SET NOCOUNT ON DECLARE @ShowKitDetail TINYINT SET @ShowKitDetail = 1 CREATE TABLE #TempTable ( InvoiceLineID CHAR(20) COLLATE database_default, LineNum SMALLINT, LineType VARCHAR(13) COLLATE database_default, PartNo VARCHAR(50) COLLATE database_default, QuantityThisDelivery DECIMAL(19,6), StyleCode VARCHAR(50) COLLATE database_default, StyleID CHAR(20) COLLATE database_default, Comment VARCHAR(200) COLLATE database_default, PartDescription VARCHAR(255) COLLATE database_default, PriceEachExGST DECIMAL(19,6), PriceEachIncGST DECIMAL(19,6), PriceTotalExGST DECIMAL(19,6), PriceTotalIncGST DECIMAL(19,6), StyleDescription VARCHAR(255) COLLATE database_default, InvoiceNo VARCHAR(20) COLLATE database_default, HistoryNo SMALLINT, CompanyName VARCHAR(100) COLLATE database_default, CompanyABN VARCHAR(100) COLLATE database_default, CompanyAddress1 VARCHAR(100) COLLATE database_default, CompanyAddress2 VARCHAR(100) COLLATE database_default, CompanyAddress3 VARCHAR(100) COLLATE database_default, CompanyAddress4 VARCHAR(100) COLLATE database_default, CompanyPhone VARCHAR(100) COLLATE database_default, CompanyFax VARCHAR(100) COLLATE database_default, CompanyEmail VARCHAR(100) COLLATE database_default, CompanyWebsite VARCHAR(100) COLLATE database_default, CustomerName VARCHAR(200) COLLATE database_default, CustomerAddress1 VARCHAR(50) COLLATE database_default, CustomerAddress2 VARCHAR(50) COLLATE database_default, CustomerAddress3 VARCHAR(50) COLLATE database_default, CustomerAddress4 VARCHAR(50) COLLATE database_default, CustomerPostCode VARCHAR(10) COLLATE database_default, CustomerABN VARCHAR(50) COLLATE database_default, OrderNo VARCHAR(50) COLLATE database_default, AccountNo VARCHAR(50) COLLATE database_default, TaxExemptNo VARCHAR(20) COLLATE database_default, CourierDetails VARCHAR(200) COLLATE database_default, SOReference VARCHAR(50) COLLATE database_default, SalesPerson VARCHAR(101) COLLATE database_default, RecordDate DATETIME, DelContactName VARCHAR(50) COLLATE database_default, DeliveryAddress1 VARCHAR(50) COLLATE database_default, DeliveryAddress2 VARCHAR(50) COLLATE database_default, DeliveryAddress3 VARCHAR(50) COLLATE database_default, DeliveryAddress4 VARCHAR(50) COLLATE database_default, DeliveryPostCode VARCHAR(10) COLLATE database_default, DeliveryAddressCountry VARCHAR(50) COLLATE database_default, Cart1IncGST DECIMAL(19,6), Cart2IncGST DECIMAL(19,6), Cart3IncGST DECIMAL(19,6), Notes VARCHAR(1000) COLLATE database_default, CreditNote BIT, QuantityDecimalPlaces SMALLINT, Cart1ExGST DECIMAL(19,6), Cart2ExGST DECIMAL(19,6), Cart3ExGST DECIMAL(19,6), LineGSTAmount DECIMAL(19,6), Cart1GST DECIMAL(19,6), Cart2GST DECIMAL(19,6), Cart3GST DECIMAL(19,6), KitHeader TINYINT, KitComponent TINYINT, ItemTaxToCharge DECIMAL(19,6), QuantityOrdered DECIMAL(19,6), QuantityOnBackOrder DECIMAL(19,6), Units VARCHAR(50) COLLATE database_default, KitLineID CHAR(20) COLLATE database_default, QuantityPreviousDelivery DECIMAL(19,6), UseSerialNo BIT, TotalAmountPaid DECIMAL(19,6), TotalAmountTendered DECIMAL(19,6), InvoiceID CHAR(20) COLLATE database_default, CashSaleName VARCHAR(50) COLLATE database_default, CashSaleCompany VARCHAR(50) COLLATE database_default, CashSaleAddress1 VARCHAR(50) COLLATE database_default, CashSaleAddress2 VARCHAR(50) COLLATE database_default, CashSaleAddress3 VARCHAR(50) COLLATE database_default, CashSaleAddress4 VARCHAR(50) COLLATE database_default, CashSalePostcode VARCHAR(10) COLLATE database_default, CashSalePhone VARCHAR(20) COLLATE database_default, CashSaleFax VARCHAR(20) COLLATE database_default, CashSaleContactName VARCHAR(50) COLLATE database_default, OrderedPriceTotalExGST DECIMAL(19,6), OrderedPriceTotalIncGST DECIMAL(19,6), LineOrderedGSTAmount DECIMAL(19,6), PeriodType SMALLINT, TermsDays SMALLINT, TermsType SMALLINT, QtyThisDelUom DECIMAL(19,6), UomName VARCHAR(50) COLLATE database_default, UomPartNo VARCHAR(50) COLLATE database_default, QtyOrderedUom DECIMAL(19,6), QtyBackOrdUom DECIMAL(19,6), ItemPriceUom DECIMAL(19,6), ItemPriceIncGSTUom DECIMAL(19,6), ExpectedDeliveryDate DATETIME, SubTotalOrderedExGST DECIMAL(19,6), OrderedInvoiceTotalExGST DECIMAL(19,6), OrderedInvoiceTotalGST DECIMAL(19,6), AltAccountNo VARCHAR(50) COLLATE database_default, ForwardOrderDate DATETIME, ScheduledDate DATETIME, CKSafetyDivision BIT, CommentLine BIT, HistoryText VARCHAR(200) COLLATE database_default, DebtorClassifrication VARCHAR(200) COLLATE database_default, BillWhenComplete SMALLINT, InitDate DATETIME, CashSaleDebtor BIT, DebtorID CHAR(20) COLLATE database_default, PrintTandC BIT, CustomerACN VARCHAR(50) COLLATE database_default, BSB VARCHAR(10) COLLATE database_default, BankAccountNo VARCHAR(20) COLLATE database_default, BankAccountName VARCHAR(200) COLLATE database_default ) CREATE TABLE #TempTable2 ( InvoiceLineID CHAR(20) COLLATE database_default, LineNum SMALLINT, LineType VARCHAR(13) COLLATE database_default, PartNo VARCHAR(50) COLLATE database_default, QuantityThisDelivery DECIMAL(19,6), StyleCode VARCHAR(50) COLLATE database_default, StyleID CHAR(20) COLLATE database_default, Comment VARCHAR(200) COLLATE database_default, PartDescription VARCHAR(255) COLLATE database_default, AvgPriceEachExGST DECIMAL(19,6), AvgPriceEachIncGST DECIMAL(19,6), PriceTotalExGST DECIMAL(19,6), PriceTotalIncGST DECIMAL(19,6), StyleDescription VARCHAR(255) COLLATE database_default, InvoiceNo VARCHAR(20) COLLATE database_default, HistoryNo SMALLINT, CompanyName VARCHAR(100) COLLATE database_default, CompanyABN VARCHAR(100) COLLATE database_default, CompanyAddress1 VARCHAR(100) COLLATE database_default, CompanyAddress2 VARCHAR(100) COLLATE database_default, CompanyAddress3 VARCHAR(100) COLLATE database_default, CompanyAddress4 VARCHAR(100) COLLATE database_default, CompanyPhone VARCHAR(100) COLLATE database_default, CompanyFax VARCHAR(100) COLLATE database_default, CompanyEmail VARCHAR(100) COLLATE database_default, CompanyWebsite VARCHAR(100) COLLATE database_default, CustomerName VARCHAR(200) COLLATE database_default, CustomerAddress1 VARCHAR(50) COLLATE database_default, CustomerAddress2 VARCHAR(50) COLLATE database_default, CustomerAddress3 VARCHAR(50) COLLATE database_default, CustomerAddress4 VARCHAR(50) COLLATE database_default, CustomerPostCode VARCHAR(10) COLLATE database_default, CustomerABN VARCHAR(50) COLLATE database_default, OrderNo VARCHAR(50) COLLATE database_default, AccountNo VARCHAR(50) COLLATE database_default, TaxExemptNo VARCHAR(20) COLLATE database_default, CourierDetails VARCHAR(200) COLLATE database_default, SOReference VARCHAR(50) COLLATE database_default, SalesPerson VARCHAR(101) COLLATE database_default, RecordDate DATETIME, DelContactName VARCHAR(50) COLLATE database_default, DeliveryAddress1 VARCHAR(50) COLLATE database_default, DeliveryAddress2 VARCHAR(50) COLLATE database_default, DeliveryAddress3 VARCHAR(50) COLLATE database_default, DeliveryAddress4 VARCHAR(50) COLLATE database_default, DeliveryPostCode VARCHAR(10) COLLATE database_default, DeliveryAddressCountry VARCHAR(50) COLLATE database_default, SubTotalIncGST DECIMAL(19,6), Cart1IncGST DECIMAL(19,6), Cart2IncGST DECIMAL(19,6), Cart3IncGST DECIMAL(19,6), InvoiceTotalIncGST DECIMAL(19,6), Notes VARCHAR(1000) COLLATE database_default, CreditNote BIT, QuantityDecimalPlaces SMALLINT, MoneyDecimalPlaces TINYINT, SubTotalExGST DECIMAL(19,6), Cart1ExGST DECIMAL(19,6), Cart2ExGST DECIMAL(19,6), Cart3ExGST DECIMAL(19,6), InvoiceTotalExGST DECIMAL(19,6), LineGSTAmount DECIMAL(19,6), SubTotalGST DECIMAL(19,6), Cart1GST DECIMAL(19,6), Cart2GST DECIMAL(19,6), Cart3GST DECIMAL(19,6), InvoiceTotalGST DECIMAL(19,6), KitHeader TINYINT, KitComponent TINYINT, ItemTaxToCharge DECIMAL(19,6), QuantityOrdered DECIMAL(19,6), QuantityOnBackOrder DECIMAL(19,6), Units VARCHAR(50) COLLATE database_default, KitLineID CHAR(20) COLLATE database_default, QuantityPreviousDelivery DECIMAL(19,6), UseSerialNo BIT, TotalAmountPaid DECIMAL(19,6), TotalAmountTendered DECIMAL(19,6), InvoiceID CHAR(20) COLLATE database_default, CashSaleName VARCHAR(50) COLLATE database_default, CashSaleCompany VARCHAR(50) COLLATE database_default, CashSaleAddress1 VARCHAR(50) COLLATE database_default, CashSaleAddress2 VARCHAR(50) COLLATE database_default, CashSaleAddress3 VARCHAR(50) COLLATE database_default, CashSaleAddress4 VARCHAR(50) COLLATE database_default, CashSalePostcode VARCHAR(10) COLLATE database_default, CashSalePhone VARCHAR(20) COLLATE database_default, CashSaleFax VARCHAR(20) COLLATE database_default, CashSaleContactName VARCHAR(50) COLLATE database_default, OrderedPriceTotalExGST DECIMAL(19,6), OrderedPriceTotalIncGST DECIMAL(19,6), SubTotalOrderedIncGST DECIMAL(19,6), OrderedInvoiceTotalIncGST DECIMAL(19,6), LineOrderedGSTAmount DECIMAL(19,6), SubTotalOrderedGST DECIMAL(19,6), PeriodType SMALLINT, TermsDays SMALLINT, TermsType SMALLINT, QtyThisDelUom DECIMAL(19,6), UomName VARCHAR(50), UomPartNo VARCHAR(50), QtyOrderedUom DECIMAL(19,6), QtyBackOrdUom DECIMAL(19,6), ItemPriceUom DECIMAL(19,6), ItemPriceIncGSTUom DECIMAL(19,6), ExpectedDeliveryDate DATETIME, SubTotalOrderedExGST DECIMAL(19,6), OrderedInvoiceTotalExGST DECIMAL(19,6), OrderedInvoiceTotalGST DECIMAL(19,6), AltAccountNo VARCHAR(50) COLLATE database_default, ForwardOrderDate DATETIME, ScheduledDate DATETIME, CKSafetyDivision BIT, CommentLine BIT, HistoryText VARCHAR(200) COLLATE database_default, DebtorClassifrication VARCHAR(200) COLLATE database_default, BillWhenComplete SMALLINT, InitDate DATETIME, CashSaleDebtor BIT, DebtorID CHAR(20) COLLATE database_default, PrintTandC BIT, CustomerACN VARCHAR(50) COLLATE database_default, BSB VARCHAR(10) COLLATE database_default, BankAccountNo VARCHAR(20) COLLATE database_default, BankAccountName VARCHAR(200) COLLATE database_default ) DECLARE @InvoiceID CHAR(20) DECLARE @CourierDetails VARCHAR(200) DECLARE @RecordDate DATETIME DECLARE @DelContactName VARCHAR(50) DECLARE @DeliveryAddress1 VARCHAR(50) DECLARE @DeliveryAddress2 VARCHAR(50) DECLARE @DeliveryAddress3 VARCHAR(50) DECLARE @DeliveryAddress4 VARCHAR(50) DECLARE @DeliveryPostCode VARCHAR(10) DECLARE @DeliveryAddressCountry VARCHAR(255) DECLARE @Notes VARCHAR(1000) DECLARE @KitRoundingPartInventoryID AS CHAR(20) DECLARE @SubTotalIncGST DECIMAL(19,6) DECLARE @SubTotalOrderedIncGST DECIMAL(19,6) DECLARE @SubTotalExGST DECIMAL(19,6) DECLARE @SubTotalOrderedExGST DECIMAL(19,6) DECLARE @SubTotalGST DECIMAL(19,6) DECLARE @SubTotalOrderedGST DECIMAL(19,6) DECLARE @InvoiceType SMALLINT DECLARE @MoneyDecimalPlaces TINYINT DECLARE @OrderedCart1IncGST DECIMAL(19,6) DECLARE @OrderedCart2IncGST DECIMAL(19,6) DECLARE @OrderedCart3IncGST DECIMAL(19,6) DECLARE @OrderedCart1ExGST DECIMAL(19,6) DECLARE @OrderedCart2ExGST DECIMAL(19,6) DECLARE @OrderedCart3ExGST DECIMAL(19,6) DECLARE @OrderedCart1GST DECIMAL(19,6) DECLARE @OrderedCart2GST DECIMAL(19,6) DECLARE @OrderedCart3GST DECIMAL(19,6) --@InvoiceType --0 = Ship & Bill --1 = Bill Complete --2 = Ship Complete --Get invoice type SET @InvoiceType = (SELECT TOP 1 BillWhenComplete FROM SO_Main INNER JOIN SO_History ON SO_Main.InvoiceID = SO_History.InvoiceID WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) --Get Kit Rounding Part SET @KitRoundingPartInventoryID = (SELECT TOP 1 COALESCE(Contents,'') FROM SY_Sysvalues WHERE IDKey = 'KitRoundingPart' AND Section = 'InvoicingParams') IF @KitRoundingPartInventoryID = '' SET @KitRoundingPartInventoryID = 'NONE' --Get Money Decimal Places SELECT TOP 1 @MoneyDecimalPlaces = DecimalPlaces FROM FX_Currency WHERE IsLocal = 1 IF @InvoiceType = 0 BEGIN INSERT INTO #TempTable SELECT SO_Lines.InvoiceLineID,SO_Lines.LineNum, CASE WHEN SO_Lines.PartNo = 'Comment Line' THEN 'Comment' WHEN CAST(SO_Lines.NonStock AS TINYINT) = 1 THEN 'Non-Inventory' WHEN CAST(IN_Main.TypeStyle AS TINYINT) = 1 THEN 'SSC Item' ELSE 'Normal' END, SO_Lines.PartNo,COALESCE(SO_Lines.QuantityThisDel,0),COALESCE(IN_Styles.StyleCode,SO_Lines.PartNo), COALESCE(IN_Styles.RecID,''), SO_Lines.HistoryText_Comment,SO_Lines.Description,SO_Lines.ItemPrice,SO_Lines.ItemPriceIncGST, SO_Lines.ItemPrice*SO_Lines.QuantityThisDel,SO_Lines.CurrentLineTotal, COALESCE(IN_Styles.Description,SO_Lines.Description), SO_Main.InvoiceNo,COALESCE(SO_History.HistoryNo,0),'','','','','','','','','','', DB_Main.Name,DB_Main.Address1,DB_Main.Address2,DB_Main.Address3,DB_Main.Address4, DB_Main.PostCode,DB_Main.ABN,SO_Main.OrderNo,DB_Main.AccountNo,SO_Main.TaxExemptNo, SO_History.CourierDetails,SO_Main.SOReference,HR_Staff.FName + ' ' + HR_Staff.SName, SO_History.RecordDate,SO_History.DelContactName,SO_History.DelAddress1,SO_History.DelAddress2, SO_History.DelAddress3,SO_History.DelAddress4,SO_History.Postcode, SO_History.DeliveryAddressCountry, SO_History.CartageCharge1 + SO_History.Cartage1TaxAmount,SO_History.CartageCharge2 + SO_History.Cartage2TaxAmount, SO_History.CartageCharge3 + SO_History.Cartage3TaxAmount,CAST(SO_History.Notes AS VARCHAR(1000)),CAST(CreditNote AS TINYINT), COALESCE(SO_Lines.DecimalPlaces, IN_Main.DecimalPlaces, 0),SO_History.CartageCharge1,SO_History.CartageCharge2,SO_History.CartageCharge3,SO_Lines.TaxToCharge, SO_History.Cartage1TaxAmount,SO_History.Cartage2TaxAmount,SO_History.Cartage3TaxAmount, CASE WHEN SO_Lines.KittingStatus = 1 THEN 1 ELSE 0 END,CASE WHEN SO_Lines.KittingStatus = 2 OR SO_Lines.InventoryID = @KitRoundingPartInventoryID THEN 1 ELSE 0 END, SO_Lines.ItemTaxToCharge,QuantityOrdered,QuantityBackOrd,SO_Lines.Units,KitLineID, QuantityPrevDel, SO_Lines.UseSerialNo, COALESCE((SELECT SUM(COALESCE(AmountPaid, 0)) FROM SO_Payments WHERE InvoiceID = SO_Main.InvoiceID), 0) [TotalAmountPaid], COALESCE((SELECT SUM(COALESCE(AmountTendered, 0)) FROM SO_Payments WHERE InvoiceID = SO_Main.InvoiceID), 0) [TotalAmountTendered], SO_Main.InvoiceID, SO_CashSale.Name, SO_CashSale.Company, SO_CashSale.Address1, SO_CashSale.Address2, SO_CashSale.Address3, SO_CashSale.Address4, SO_CashSale.PostCode, SO_CashSale.Phone, SO_CashSale.Fax, SO_CashSale.ContactName, (SO_Lines.ItemPrice*SO_Lines.QuantityOrdered), (SO_Lines.ItemPrice*SO_Lines.QuantityOrdered) + ((SO_Lines.ItemPrice*SO_Lines.QuantityOrdered) * SO_Lines.TaxRate / 100), ((SO_Lines.ItemPrice*SO_Lines.QuantityOrdered) * SO_Lines.TaxRate / 100), PeriodType, DB_Main.TermsDays, DB_Main.TermsType, isnull(dbo.ufn_JIWA_UOM_qty(IN_UnitOfMeasure_RecID, QuantityThisDel), QuantityThisDel) as QtyThisDelUom, isnull(IN_UnitOfMeasure.Name, SO_Lines.Units) as UomName, isnull(IN_UnitOfMeasure.PartNo,SO_Lines.PartNo) as UomPartNo, isnull(dbo.ufn_JIWA_UOM_qty(IN_UnitOfMeasure_RecID, QuantityOrdered), QuantityOrdered) as QtyOrderedUom, isnull(dbo.ufn_JIWA_UOM_qty(IN_UnitOfMeasure_RecID, QuantityBackOrd), QuantityBackOrd) as QtyBackOrdUom, isnull(dbo.ufn_JIWA_UOM_Price(IN_UnitOfMeasure_RecID, SO_Lines.ItemPrice), SO_Lines.ItemPrice) as ItemPriceUom, isnull(dbo.ufn_JIWA_UOM_Price(IN_UnitOfMeasure_RecID, SO_Lines.ItemPriceIncGST), SO_Lines.ItemPriceIncGST) as ItemPriceIncGSTUom, SO_Main.ExpectedDeliveryDate, 0, 0, 0, DB_Main.AltAccountNo, SO_Lines.ForwardOrderDate, SO_Lines.ScheduledDate,0,0,'',DB_Classification.Description, SO_Main.BillWhenComplete, SO_Main.InvoiceInitDate,0 , DB_Main.DebtorID,0, DB_Main.ACN,'','','' FROM dbo.SO_Main INNER JOIN dbo.SO_History ON dbo.SO_Main.InvoiceID = dbo.SO_History.InvoiceID INNER JOIN dbo.SO_Lines ON dbo.SO_History.InvoiceHistoryID = dbo.SO_Lines.InvoiceHistoryID INNER JOIN dbo.DB_Main ON dbo.SO_Main.DebtorID = dbo.DB_Main.DebtorID INNER JOIN dbo.HR_Staff ON dbo.SO_Main.StaffID = dbo.HR_Staff.StaffID INNER JOIN dbo.DB_Classification ON dbo.DB_Main.ClassificationID = dbo.DB_Classification.DebtorClassificationID LEFT OUTER JOIN dbo.IN_Main ON dbo.SO_Lines.InventoryID = dbo.IN_Main.InventoryID LEFT OUTER JOIN dbo.IN_Styles ON dbo.IN_Main.StyleID = dbo.IN_Styles.RecID LEFT OUTER JOIN dbo.IN_Sizes ON dbo.IN_Main.SizeID = dbo.IN_Sizes.RecID LEFT OUTER JOIN dbo.IN_Colours ON dbo.IN_Main.ColourID = dbo.IN_Colours.RecID LEFT OUTER JOIN dbo.SO_CashSale ON dbo.SO_Main.InvoiceID = dbo.SO_CashSale.InvoiceID LEFT OUTER JOIN dbo.IN_UnitOfMeasure ON dbo.SO_Lines.IN_UnitOfMeasure_RecID = dbo.IN_UnitOfMeasure.RecID WHERE SO_History.InvoiceHistoryID = @SP_InvoiceHistoryID INSERT INTO #TempTable2 SELECT MAX(InvoiceLineID),MIN(LineNum),MAX(LineType),MAX(PartNo),SUM(QuantityThisDelivery),StyleCode,MAX(StyleID),MAX(Comment), MAX(PartDescription), MAX(PriceEachExGST), MAX(PriceEachIncGST), SUM(PriceTotalExGST),SUM(PriceTotalIncGST), MAX(StyleDescription),MAX(InvoiceNo),MAX(HistoryNo),MAX(CompanyName),MAX(CompanyABN),MAX(CompanyAddress1), MAX(CompanyAddress2),MAX(CompanyAddress3),MAX(CompanyAddress4),MAX(CompanyPhone),MAX(CompanyFax),MAX(CompanyEmail), MAX(CompanyWebsite),MAX(CustomerName),MAX(CustomerAddress1),MAX(CustomerAddress2),MAX(CustomerAddress3), MAX(CustomerAddress4),MAX(CustomerPostCode),MAX(CustomerABN),MAX(OrderNo),MAX(AccountNo),MAX(TaxExemptNo), MAX(CourierDetails),MAX(SOReference),MAX(SalesPerson),MAX(RecordDate),MAX(DelContactName),MAX(DeliveryAddress1),MAX(DeliveryAddress2), MAX(DeliveryAddress3),MAX(DeliveryAddress4),MAX(DeliveryPostCode), MAX(DeliveryAddressCountry),0,MAX(Cart1IncGST),MAX(Cart2IncGST), MAX(Cart3IncGST),0,MAX(Notes),MAX(CAST(CreditNote AS TINYINT)),MAX(QuantityDecimalPlaces),@MoneyDecimalPlaces,0, MAX(Cart1ExGST),MAX(Cart2ExGST),MAX(Cart3ExGST),0,SUM(LineGSTAmount),0,MAX(Cart1GST),MAX(Cart2GST),MAX(Cart3GST), 0,MAX(KitHeader),MAX(KitComponent),SUM(ItemTaxToCharge),SUM(QuantityOrdered),SUM(QuantityOnBackOrder),MAX(Units), KitLineID, SUM(QuantityPreviousDelivery), MAX(CAST(UseSerialNo AS TINYINT)), MAX(TotalAmountPaid), MAX(TotalAmountTendered), MAX(InvoiceID), MAX(CashSaleName), MAX(CashSaleCompany), MAX(CashSaleAddress1), MAX(CashSaleAddress2), MAX(CashSaleAddress3), MAX(CashSaleAddress4), MAX(CashSalePostCode), MAX(CashSalePhone), MAX(CashSaleFax), MAX(CashSaleContactName), SUM(OrderedPriceTotalExGST), SUM(OrderedPriceTotalIncGST), 0, 0, SUM(LineOrderedGSTAmount), 0, MAX(PeriodType), MAX(TermsDays), MAX(TermsType), QtyThisDelUom, UomName, UomPartNo, QtyOrderedUom, QtyBackOrdUom, ItemPriceUom, ItemPriceIncGSTUom, MAX(ExpectedDeliveryDate), 0, 0, 0, MAX(AltAccountNo), MAX(ForwardOrderDate), MAX(ScheduledDate), MAX(CAST(CKSafetyDivision AS TINYINT)), MAX(CAST(CommentLine AS TINYINT)), MAX(HistoryText), MAX(DebtorClassifrication), MAX(BillWhenComplete), MAX(InitDate), MAX(CAST(CashSaleDebtor AS TINYINT)), MAX(DebtorID), MAX(CAST(PrintTandC AS TINYINT)), MAX(CustomerACN), MAX(BSB), MAX(BankAccountNo), MAX(BankAccountName) FROM #TempTable GROUP BY StyleCode, KitLineID, Comment, PartDescription, LineNum, QtyThisDelUom, UomName, UomPartNo, QtyOrderedUom, QtyBackOrdUom, ItemPriceUom, ItemPriceIncGSTUom END ELSE IF @InvoiceType = 1 BEGIN SET @InvoiceID = (SELECT TOP 1 InvoiceID FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) INSERT INTO #TempTable SELECT SO_Lines.InvoiceLineID,SO_Lines.LineNum, CASE WHEN SO_Lines.PartNo = 'Comment Line' THEN 'Comment' WHEN CAST(SO_Lines.NonStock AS TINYINT) = 1 THEN 'Non-Inventory' WHEN CAST(IN_Main.TypeStyle AS TINYINT) = 1 THEN 'SSC Item' ELSE 'Normal' END, SO_Lines.PartNo,COALESCE(SO_Lines.QuantityThisDel,0),COALESCE(IN_Styles.StyleCode,SO_Lines.PartNo), COALESCE(IN_Styles.RecID,''), SO_Lines.HistoryText_Comment,SO_Lines.Description,SO_Lines.ItemPrice,SO_Lines.ItemPriceIncGST, SO_Lines.ItemPrice*SO_Lines.QuantityThisDel,SO_Lines.CurrentLineTotal, COALESCE(IN_Styles.Description,SO_Lines.Description), SO_Main.InvoiceNo,COALESCE(SO_History.HistoryNo,0),'','','','','','','','','','', DB_Main.Name,DB_Main.Address1,DB_Main.Address2,DB_Main.Address3,DB_Main.Address4, DB_Main.PostCode,DB_Main.ABN,SO_Main.OrderNo,DB_Main.AccountNo,SO_Main.TaxExemptNo, SO_History.CourierDetails,SO_Main.SOReference,HR_Staff.Fname + ' ' + HR_Staff.Sname, SO_History.RecordDate,SO_History.DelContactName,SO_History.DelAddress1,SO_History.DelAddress2, SO_History.DelAddress3,SO_History.DelAddress4,SO_History.Postcode, SO_History.DeliveryAddressCountry, SO_History.CartageCharge1 + SO_History.Cartage1TaxAmount,SO_History.CartageCharge2 + SO_History.Cartage2TaxAmount, SO_History.CartageCharge3 + SO_History.Cartage3TaxAmount,CAST(SO_History.Notes AS VARCHAR(1000)),CAST(CreditNote AS TINYINT), COALESCE(SO_Lines.DecimalPlaces, IN_Main.DecimalPlaces, 0),SO_History.CartageCharge1,SO_History.CartageCharge2,SO_History.CartageCharge3,SO_Lines.TaxToCharge, SO_History.Cartage1TaxAmount,SO_History.Cartage2TaxAmount,SO_History.Cartage3TaxAmount, CASE WHEN SO_Lines.KittingStatus = 1 THEN 1 ELSE 0 END,CASE WHEN SO_Lines.KittingStatus = 2 OR SO_Lines.InventoryID = @KitRoundingPartInventoryID THEN 1 ELSE 0 END, SO_Lines.ItemTaxToCharge,QuantityOrdered,QuantityBackOrd,SO_Lines.Units,KitLineID, QuantityPrevDel, SO_Lines.UseSerialNo, COALESCE((SELECT SUM(COALESCE(AmountPaid, 0)) FROM SO_Payments WHERE InvoiceID = SO_Main.InvoiceID), 0) [TotalAmountPaid], COALESCE((SELECT SUM(COALESCE(AmountTendered, 0)) FROM SO_Payments WHERE InvoiceID = SO_Main.InvoiceID), 0) [TotalAmountTendered], SO_Main.InvoiceID, SO_CashSale.Name, SO_CashSale.Company, SO_CashSale.Address1, SO_CashSale.Address2, SO_CashSale.Address3, SO_CashSale.Address4, SO_CashSale.PostCode, SO_CashSale.Phone, SO_CashSale.Fax, SO_CashSale.ContactName, SO_Lines.ItemPrice*SO_Lines.QuantityOrdered, (SO_Lines.ItemPrice*SO_Lines.QuantityOrdered) + ((SO_Lines.ItemPrice*SO_Lines.QuantityOrdered) * SO_Lines.TaxRate / 100), ((SO_Lines.ItemPrice*SO_Lines.QuantityOrdered) * SO_Lines.TaxRate / 100), DB_Main.PeriodType, DB_Main.TermsDays, DB_Main.TermsType, isnull(dbo.ufn_JIWA_UOM_qty(IN_UnitOfMeasure_RecID, QuantityThisDel), QuantityTHisDel) as QtyThisDelUom, isnull(IN_UnitOfMeasure.Name, SO_Lines.Units) as UomName, isnull(IN_UnitOfMeasure.PartNo,SO_Lines.PartNo) as UomPartNo, isnull(dbo.ufn_JIWA_UOM_qty(IN_UnitOfMeasure_RecID, QuantityOrdered), QuantityOrdered) as QtyOrderedUom, isnull(dbo.ufn_JIWA_UOM_qty(IN_UnitOfMeasure_RecID, QuantityBackOrd), QuantityBackOrd) as QtyBackOrdUom, isnull(dbo.ufn_JIWA_UOM_Price(IN_UnitOfMeasure_RecID, SO_Lines.ItemPrice), SO_Lines.ItemPrice) as ItemPriceUom, isnull(dbo.ufn_JIWA_UOM_Price(IN_UnitOfMeasure_RecID, SO_Lines.ItemPriceIncGST), SO_Lines.ItemPriceIncGST) as ItemPriceIncGSTUom, SO_Main.ExpectedDeliveryDate, 0, 0, 0, DB_Main.AltAccountNo, SO_Lines.ForwardOrderDate, SO_Lines.ScheduledDate,0,0,'',DB_Classification.Description, SO_Main.BillWhenComplete, SO_Main.InvoiceInitDate,0 , DB_Main.DebtorID,0, DB_Main.ACN,'','','' FROM dbo.SO_Main INNER JOIN dbo.SO_History ON dbo.SO_Main.InvoiceID = dbo.SO_History.InvoiceID INNER JOIN dbo.SO_Lines ON dbo.SO_History.InvoiceHistoryID = dbo.SO_Lines.InvoiceHistoryID INNER JOIN dbo.DB_Main ON dbo.SO_Main.DebtorID = dbo.DB_Main.DebtorID INNER JOIN dbo.HR_Staff ON dbo.SO_Main.StaffID = dbo.HR_Staff.StaffID INNER JOIN dbo.DB_Classification ON dbo.DB_Main.ClassificationID = dbo.DB_Classification.DebtorClassificationID LEFT OUTER JOIN dbo.IN_Main ON dbo.SO_Lines.InventoryID = dbo.IN_Main.InventoryID LEFT OUTER JOIN dbo.IN_Styles ON dbo.IN_Main.StyleID = dbo.IN_Styles.RecID LEFT OUTER JOIN dbo.IN_Sizes ON dbo.IN_Main.SizeID = dbo.IN_Sizes.RecID LEFT OUTER JOIN dbo.IN_Colours ON dbo.IN_Main.ColourID = dbo.IN_Colours.RecID LEFT OUTER JOIN dbo.SO_CashSale ON dbo.SO_Main.InvoiceID = dbo.SO_CashSale.InvoiceID LEFT OUTER JOIN dbo.IN_UnitOfMeasure ON dbo.SO_Lines.IN_UnitOfMeasure_RecID = dbo.IN_UnitOfMeasure.RecID WHERE SO_Main.InvoiceID = @InvoiceID SET @CourierDetails = (SELECT TOP 1 CourierDetails FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @RecordDate = (SELECT TOP 1 RecordDate FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DelContactName = (SELECT TOP 1 DelContactName FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DeliveryAddress1 = (SELECT TOP 1 DelAddress1 FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DeliveryAddress2 = (SELECT TOP 1 DelAddress2 FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DeliveryAddress3 = (SELECT TOP 1 DelAddress3 FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DeliveryAddress4 = (SELECT TOP 1 DelAddress4 FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DeliveryPostCode = (SELECT TOP 1 PostCode FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DeliveryAddressCountry = (SELECT TOP 1 DeliveryAddressCountry FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @Notes = (SELECT TOP 1 CAST(Notes AS VARCHAR(1000)) FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) INSERT INTO #TempTable2 SELECT MAX(InvoiceLineID),LineNum,MAX(LineType),MAX(PartNo) ,SUM(QuantityThisDelivery),MAX(StyleCode), MAX(StyleID),MAX(Comment),MAX(PartDescription), MAX(PriceEachExGST), MAX(PriceEachIncGST), SUM(PriceTotalExGST),SUM(PriceTotalIncGST), MAX(StyleDescription),MAX(InvoiceNo),MAX(HistoryNo),MAX(CompanyName),MAX(CompanyABN),MAX(CompanyAddress1), MAX(CompanyAddress2),MAX(CompanyAddress3),MAX(CompanyAddress4),MAX(CompanyPhone),MAX(CompanyFax),MAX(CompanyEmail), MAX(CompanyWebsite),MAX(CustomerName),MAX(CustomerAddress1),MAX(CustomerAddress2),MAX(CustomerAddress3), MAX(CustomerAddress4),MAX(CustomerPostCode),MAX(CustomerABN),MAX(OrderNo),MAX(AccountNo),MAX(TaxExemptNo), @CourierDetails,MAX(SOReference),MAX(SalesPerson),@RecordDate,@DelContactName, @DeliveryAddress1,@DeliveryAddress2, @DeliveryAddress3,@DeliveryAddress4,@DeliveryPostCode, @DeliveryAddressCountry,0,MAX(Cart1IncGST),MAX(Cart2IncGST), MAX(Cart3IncGST),0,@Notes,MAX(CAST(CreditNote AS TINYINT)),MAX(QuantityDecimalPlaces),@MoneyDecimalPlaces,0, MAX(Cart1ExGST),MAX(Cart2ExGST),MAX(Cart3ExGST),0,SUM(LineGSTAmount),0,MAX(Cart1GST),MAX(Cart2GST),MAX(Cart3GST), 0,MAX(KitHeader),MAX(KitComponent),MAX(ItemTaxToCharge),MAX(QuantityOrdered),MIN(QuantityOnBackOrder),MAX(Units), MAX(KitLineID), MAX(QuantityPreviousDelivery), MAX(CAST(UseSerialNo AS TINYINT)), MAX(TotalAmountPaid), MAX(TotalAmountTendered), MAX(InvoiceID), MAX(CashSaleName), MAX(CashSaleCompany), MAX(CashSaleAddress1), MAX(CashSaleAddress2), MAX(CashSaleAddress3), MAX(CashSaleAddress4), MAX(CashSalePostCode), MAX(CashSalePhone), MAX(CashSaleFax), MAX(CashSaleContactName), SUM(OrderedPriceTotalExGST), SUM(OrderedPriceTotalIncGST), 0, 0, SUM(LineOrderedGSTAmount), 0, MAX(PeriodType), MAX(TermsDays), MAX(TermsType), QtyThisDelUom, UomName, UomPartNo, QtyOrderedUom, QtyBackOrdUom, ItemPriceUom, ItemPriceIncGSTUom, MAX(ExpectedDeliveryDate), 0, 0, 0, MAX(AltAccountNo), MAX(ForwardOrderDate), MAX(ScheduledDate), MAX(CAST(CKSafetyDivision AS TINYINT)), MAX(CAST(CommentLine AS TINYINT)), MAX(HistoryText), MAX(DebtorClassifrication), MAX(BillWhenComplete), MAX(InitDate), MAX(CAST(CashSaleDebtor AS TINYINT)), MAX(DebtorID), MAX(CAST(PrintTandC AS TINYINT)), MAX(CustomerACN), MAX(BSB), MAX(BankAccountNo), MAX(BankAccountName) FROM #TempTable GROUP BY LineNum, QtyThisDelUom, UomName, UomPartNo, QtyOrderedUom, QtyBackOrdUom, ItemPriceUom, ItemPriceIncGSTUom END ELSE IF @InvoiceType = 2 BEGIN SET @InvoiceID = (SELECT TOP 1 InvoiceID FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) INSERT INTO #TempTable SELECT SO_Lines.InvoiceLineID,SO_Lines.LineNum, CASE WHEN SO_Lines.PartNo = 'Comment Line' THEN 'Comment' WHEN CAST(SO_Lines.NonStock AS TINYINT) = 1 THEN 'Non-Inventory' WHEN CAST(IN_Main.TypeStyle AS TINYINT) = 1 THEN 'SSC Item' ELSE 'Normal' END, SO_Lines.PartNo,COALESCE(SO_Lines.QuantityThisDel,0),COALESCE(IN_Styles.StyleCode,SO_Lines.PartNo), COALESCE(IN_Styles.RecID,''), SO_Lines.HistoryText_Comment,SO_Lines.Description,SO_Lines.ItemPrice,SO_Lines.ItemPriceIncGST, SO_Lines.ItemPrice*SO_Lines.QuantityThisDel,SO_Lines.CurrentLineTotal, COALESCE(IN_Styles.Description,SO_Lines.Description), SO_Main.InvoiceNo,COALESCE(SO_History.HistoryNo,0),'','','','','','','','','','', DB_Main.Name,DB_Main.Address1,DB_Main.Address2,DB_Main.Address3,DB_Main.Address4, DB_Main.PostCode,DB_Main.ABN,SO_Main.OrderNo,DB_Main.AccountNo,SO_Main.TaxExemptNo, SO_History.CourierDetails,SO_Main.SOReference,HR_Staff.Fname + ' ' + HR_Staff.Sname, SO_History.RecordDate,SO_History.DelContactName,SO_History.DelAddress1,SO_History.DelAddress2, SO_History.DelAddress3,SO_History.DelAddress4,SO_History.Postcode, SO_History.DeliveryAddressCountry, SO_History.CartageCharge1 + SO_History.Cartage1TaxAmount,SO_History.CartageCharge2 + SO_History.Cartage2TaxAmount, SO_History.CartageCharge3 + SO_History.Cartage3TaxAmount,CAST(SO_History.Notes AS VARCHAR(1000)),CAST(CreditNote AS TINYINT), COALESCE(SO_Lines.DecimalPlaces, IN_Main.DecimalPlaces, 0),SO_History.CartageCharge1,SO_History.CartageCharge2,SO_History.CartageCharge3,SO_Lines.TaxToCharge, SO_History.Cartage1TaxAmount,SO_History.Cartage2TaxAmount,SO_History.Cartage3TaxAmount, CASE WHEN SO_Lines.KittingStatus = 1 THEN 1 ELSE 0 END,CASE WHEN SO_Lines.KittingStatus = 2 OR SO_Lines.InventoryID = @KitRoundingPartInventoryID THEN 1 ELSE 0 END, SO_Lines.ItemTaxToCharge,QuantityOrdered,QuantityBackOrd,SO_Lines.Units,KitLineID, QuantityPrevDel, SO_Lines.UseSerialNo, COALESCE((SELECT SUM(COALESCE(AmountPaid, 0)) FROM SO_Payments WHERE InvoiceID = SO_Main.InvoiceID), 0) [TotalAmountPaid], COALESCE((SELECT SUM(COALESCE(AmountTendered, 0)) FROM SO_Payments WHERE InvoiceID = SO_Main.InvoiceID), 0) [TotalAmountTendered], SO_Main.InvoiceID, SO_CashSale.Name, SO_CashSale.Company, SO_CashSale.Address1, SO_CashSale.Address2, SO_CashSale.Address3, SO_CashSale.Address4, SO_CashSale.PostCode, SO_CashSale.Phone, SO_CashSale.Fax, SO_CashSale.ContactName, SO_Lines.ItemPrice*SO_Lines.QuantityOrdered, (SO_Lines.ItemPrice*SO_Lines.QuantityOrdered) + ((SO_Lines.ItemPrice*SO_Lines.QuantityOrdered) * SO_Lines.TaxRate / 100), ((SO_Lines.ItemPrice*SO_Lines.QuantityOrdered) * SO_Lines.TaxRate / 100), DB_Main.PeriodType, DB_Main.TermsDays, DB_Main.TermsType, isnull(dbo.ufn_JIWA_UOM_qty(IN_UnitOfMeasure_RecID, QuantityThisDel), QuantityThisDel) as QtyThisDelUom, isnull(IN_UnitOfMeasure.Name, SO_Lines.Units) as UomName, isnull(IN_UnitOfMeasure.PartNo,SO_Lines.PartNo) as UomPartNo, isnull(dbo.ufn_JIWA_UOM_qty(IN_UnitOfMeasure_RecID, QuantityOrdered), QuantityOrdered) as QtyOrderedUom, isnull(dbo.ufn_JIWA_UOM_qty(IN_UnitOfMeasure_RecID, QuantityBackOrd), QuantityBackOrd) as QtyBackOrdUom, isnull(dbo.ufn_JIWA_UOM_Price(IN_UnitOfMeasure_RecID, SO_Lines.ItemPrice), SO_Lines.ItemPrice) as ItemPriceUom, isnull(dbo.ufn_JIWA_UOM_Price(IN_UnitOfMeasure_RecID, SO_Lines.ItemPrice), SO_Lines.ItemPrice) as ItemPriceIncGSTUom, SO_Main.ExpectedDeliveryDate, 0, 0, 0, DB_Main.AltAccountNo, SO_Lines.ForwardOrderDate, SO_Lines.ScheduledDate,0,0,'',DB_Classification.Description, SO_Main.BillWhenComplete, SO_Main.InvoiceInitDate,0 , DB_Main.DebtorID,0, DB_Main.ACN,'','','' FROM dbo.SO_Main INNER JOIN dbo.SO_History ON dbo.SO_Main.InvoiceID = dbo.SO_History.InvoiceID INNER JOIN dbo.SO_Lines ON dbo.SO_History.InvoiceHistoryID = dbo.SO_Lines.InvoiceHistoryID INNER JOIN dbo.DB_Main ON dbo.SO_Main.DebtorID = dbo.DB_Main.DebtorID INNER JOIN dbo.HR_Staff ON dbo.SO_Main.StaffID = dbo.HR_Staff.StaffID INNER JOIN dbo.DB_Classification ON dbo.DB_Main.ClassificationID = dbo.DB_Classification.DebtorClassificationID LEFT OUTER JOIN dbo.IN_Main ON dbo.SO_Lines.InventoryID = dbo.IN_Main.InventoryID LEFT OUTER JOIN dbo.IN_Styles ON dbo.IN_Main.StyleID = dbo.IN_Styles.RecID LEFT OUTER JOIN dbo.IN_Sizes ON dbo.IN_Main.SizeID = dbo.IN_Sizes.RecID LEFT OUTER JOIN dbo.IN_Colours ON dbo.IN_Main.ColourID = dbo.IN_Colours.RecID LEFT OUTER JOIN dbo.SO_CashSale ON dbo.SO_Main.InvoiceID = dbo.SO_CashSale.InvoiceID LEFT OUTER JOIN dbo.IN_UnitOfMeasure ON dbo.SO_Lines.IN_UnitOfMeasure_RecID = dbo.IN_UnitOfMeasure.RecID WHERE SO_Main.InvoiceID = @InvoiceID SET @CourierDetails = (SELECT TOP 1 CourierDetails FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @RecordDate = (SELECT TOP 1 RecordDate FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DelContactName = (SELECT TOP 1 DelContactName FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DeliveryAddress1 = (SELECT TOP 1 DelAddress1 FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DeliveryAddress2 = (SELECT TOP 1 DelAddress2 FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DeliveryAddress3 = (SELECT TOP 1 DelAddress3 FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DeliveryAddress4 = (SELECT TOP 1 DelAddress4 FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DeliveryPostCode = (SELECT TOP 1 PostCode FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @DeliveryAddressCountry = (SELECT TOP 1 DeliveryAddressCountry FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) SET @Notes = (SELECT TOP 1 CAST(Notes AS VARCHAR(1000)) FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID) INSERT INTO #TempTable2 SELECT MAX(InvoiceLineID),LineNum,MAX(LineType),MAX(PartNo),SUM(QuantityThisDelivery),MAX(StyleCode), MAX(StyleID),MAX(Comment),MAX(PartDescription), MAX(PriceEachExGST), MAX(PriceEachIncGST), SUM(PriceTotalExGST),SUM(PriceTotalIncGST), MAX(StyleDescription),MAX(InvoiceNo),MAX(HistoryNo),MAX(CompanyName),MAX(CompanyABN),MAX(CompanyAddress1), MAX(CompanyAddress2),MAX(CompanyAddress3),MAX(CompanyAddress4),MAX(CompanyPhone),MAX(CompanyFax),MAX(CompanyEmail), MAX(CompanyWebsite),MAX(CustomerName),MAX(CustomerAddress1),MAX(CustomerAddress2),MAX(CustomerAddress3), MAX(CustomerAddress4),MAX(CustomerPostCode),MAX(CustomerABN),MAX(OrderNo),MAX(AccountNo),MAX(TaxExemptNo), @CourierDetails,MAX(SOReference),MAX(SalesPerson),@RecordDate,@DelContactName, @DeliveryAddress1,@DeliveryAddress2, @DeliveryAddress3,@DeliveryAddress4,@DeliveryPostCode, @DeliveryAddressCountry, 0,MAX(Cart1IncGST),MAX(Cart2IncGST), MAX(Cart3IncGST),0,@Notes,MAX(CAST(CreditNote AS TINYINT)),MAX(QuantityDecimalPlaces),@MoneyDecimalPlaces,0, MAX(Cart1ExGST),MAX(Cart2ExGST),MAX(Cart3ExGST),0,SUM(LineGSTAmount),0,MAX(Cart1GST),MAX(Cart2GST),MAX(Cart3GST), 0,MAX(KitHeader),MAX(KitComponent),MAX(ItemTaxToCharge),MAX(QuantityOrdered),MIN(QuantityOnBackOrder),MAX(Units), MAX(KitLineID), MAX(QuantityPreviousDelivery), MAX(CAST(UseSerialNo AS TINYINT)), MAX(TotalAmountPaid), MAX(TotalAmountTendered), MAX(InvoiceID), MAX(CashSaleName), MAX(CashSaleCompany), MAX(CashSaleAddress1), MAX(CashSaleAddress2), MAX(CashSaleAddress3), MAX(CashSaleAddress4), MAX(CashSalePostCode), MAX(CashSalePhone), MAX(CashSaleFax), MAX(CashSaleContactName), SUM(OrderedPriceTotalExGST), SUM(OrderedPriceTotalIncGST), 0, 0, SUM(LineOrderedGSTAmount), 0, MAX(PeriodType), MAX(TermsDays), MAX(TermsType), QtyThisDelUom, UomName, UomPartNo, QtyOrderedUom, QtyBackOrdUom, ItemPriceUom, ItemPriceIncGSTUom, MAX(ExpectedDeliveryDate), 0, 0, 0, MAX(AltAccountNo), MAX(ForwardOrderDate), MAX(ScheduledDate), MAX(CAST(CKSafetyDivision AS TINYINT)), MAX(CAST(CommentLine AS TINYINT)), MAX(HistoryText), MAX(DebtorClassifrication), MAX(BillWhenComplete), MAX(InitDate), MAX(CAST(CashSaleDebtor AS TINYINT)), MAX(DebtorID), MAX(CAST(PrintTandC AS TINYINT)), MAX(CustomerACN), MAX(BSB), MAX(BankAccountNo), MAX(BankAccountName) FROM #TempTable GROUP BY LineNum, QtyThisDelUom, UomName, UomPartNo, QtyOrderedUom, QtyBackOrdUom, ItemPriceUom, ItemPriceIncGSTUom END --There should always be at least 1 row returned so that the header information can be used IF NOT EXISTS (SELECT TOP 1 * FROM #TempTable2) INSERT INTO #TempTable2 SELECT '',1,'Dummy','',0,'','','','',0,0,0,0,'',SO_Main.InvoiceNo,SO_History.HistoryNo,'','','','','','','','','','','','','','','','','','','','','','','','',GETDATE(),'','','','','','',0,0,0,0,0,'',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','', 0, 0, 0, 0, '', '', '', '', '', '', '', '', '', '', '', 0, 0, 0, 0, 0, 0, '',0,0,0,0,'',0,0,0,0, GETDATE(), 0, 0, 0, NULL, NULL, NULL ,0,0,'','',0,GETDATE(),0,'',0,'ACN','','','' FROM SO_Main INNER JOIN SO_History ON SO_Main.InvoiceID = SO_History.InvoiceID WHERE SO_History.InvoiceHistoryID = @SP_InvoiceHistoryID UPDATE #TempTable2 SET CompanyName = (SELECT TOP 1 Contents FROM SY_Sysvalues WHERE IDKey='CompanyName') UPDATE #TempTable2 SET CompanyABN = (SELECT TOP 1 Contents FROM SY_Sysvalues WHERE IDKey='CompanyABN') UPDATE #TempTable2 SET CompanyAddress1 = (SELECT TOP 1 Contents FROM SY_Sysvalues WHERE IDKey='CompanyAddress1') UPDATE #TempTable2 SET CompanyAddress2 = (SELECT TOP 1 Contents FROM SY_Sysvalues WHERE IDKey='CompanyAddress2') UPDATE #TempTable2 SET CompanyAddress3 = (SELECT TOP 1 Contents FROM SY_Sysvalues WHERE IDKey='CompanyAddress3') UPDATE #TempTable2 SET CompanyAddress4 = (SELECT TOP 1 Contents FROM SY_Sysvalues WHERE IDKey='CompanyAddress4') UPDATE #TempTable2 SET CompanyPhone = (SELECT TOP 1 Contents FROM SY_Sysvalues WHERE IDKey='CompanyPhone') UPDATE #TempTable2 SET CompanyFax = (SELECT TOP 1 Contents FROM SY_Sysvalues WHERE IDKey='CompanyFax') UPDATE #TempTable2 SET CompanyEmail = (SELECT TOP 1 Contents FROM SY_Sysvalues WHERE IDKey='CompanyEmail') UPDATE #TempTable2 SET CompanyWebsite = (SELECT TOP 1 Contents FROM SY_Sysvalues WHERE IDKey='CompanyWebsite') UPDATE #TempTable2 SET LineGSTAmount = (SELECT SUM(LineGSTAmount) FROM #TempTable2 ComponentTable WHERE #TempTable2.InvoiceLineID = ComponentTable.KitLineID ) WHERE KitHeader = 1 SET @SubTotalExGST = (SELECT SUM(PriceTotalExGST) FROM #TempTable2 WHERE KitHeader = 0 ) UPDATE #TempTable2 SET SubTotalExGST = @SubTotalExGST SET @SubTotalOrderedExGST = (SELECT SUM(OrderedPriceTotalExGST) FROM #TempTable2 WHERE KitHeader = 0 ) UPDATE #TempTable2 SET SubTotalOrderedExGST = @SubTotalOrderedExGST SET @SubTotalGST = (SELECT SUM(LineGSTAmount) FROM #TempTable2 WHERE KitHeader = 0 ) UPDATE #TempTable2 SET SubTotalGST = @SubTotalGST SET @SubTotalOrderedGST = (SELECT SUM(LineOrderedGSTAmount) FROM #TempTable2 WHERE KitHeader = 0 ) UPDATE #TempTable2 SET SubTotalOrderedGST = @SubTotalOrderedGST SET @SubTotalIncGST = (SELECT SUM(PriceTotalIncGST) FROM #TempTable2 WHERE KitHeader = 0) UPDATE #TempTable2 SET SubTotalIncGST = @SubTotalIncGST SET @SubTotalOrderedIncGST = (SELECT SUM(OrderedPriceTotalIncGST) FROM #TempTable2 WHERE KitHeader = 0) UPDATE #TempTable2 SET SubTotalOrderedIncGST = @SubTotalOrderedIncGST SET @OrderedCart1ExGST = (SELECT SUM(CartageCharge1) FROM SO_History WHERE InvoiceID IN (SELECT TOP 1 InvoiceID FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID)) SET @OrderedCart2ExGST = (SELECT SUM(CartageCharge2) FROM SO_History WHERE InvoiceID IN (SELECT TOP 1 InvoiceID FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID)) SET @OrderedCart3ExGST = (SELECT SUM(CartageCharge3) FROM SO_History WHERE InvoiceID IN (SELECT TOP 1 InvoiceID FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID)) SET @OrderedCart1GST = (SELECT SUM(Cartage1TaxAmount) FROM SO_History WHERE InvoiceID IN (SELECT TOP 1 InvoiceID FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID)) SET @OrderedCart2GST = (SELECT SUM(Cartage2TaxAmount) FROM SO_History WHERE InvoiceID IN (SELECT TOP 1 InvoiceID FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID)) SET @OrderedCart3GST = (SELECT SUM(Cartage3TaxAmount) FROM SO_History WHERE InvoiceID IN (SELECT TOP 1 InvoiceID FROM SO_History WHERE InvoiceHistoryID = @SP_InvoiceHistoryID)) SET @OrderedCart1IncGST = @OrderedCart1ExGST + @OrderedCart1GST SET @OrderedCart2IncGST = @OrderedCart2ExGST + @OrderedCart2GST SET @OrderedCart3IncGST = @OrderedCart3ExGST + @OrderedCart3GST UPDATE #TempTable2 SET InvoiceTotalExGST = SubTotalExGST + Cart1ExGST + Cart2ExGST + Cart3ExGST UPDATE #TempTable2 SET OrderedInvoiceTotalExGST = SubTotalOrderedExGST + @OrderedCart1ExGST + @OrderedCart2ExGST + @OrderedCart3ExGST UPDATE #TempTable2 SET InvoiceTotalIncGST = SubTotalIncGST + Cart1IncGST + Cart2IncGST + Cart3IncGST UPDATE #TempTable2 SET OrderedInvoiceTotalIncGST = SubTotalOrderedIncGST + @OrderedCart1IncGST + @OrderedCart2IncGST + @OrderedCart3IncGST UPDATE #TempTable2 SET InvoiceTotalGST = SubTotalGST + Cart1GST + Cart2GST + Cart3GST UPDATE #TempTable2 SET OrderedInvoiceTotalGST = SubTotalOrderedGST + @OrderedCart1GST + @OrderedCart2GST + @OrderedCart3GST UPDATE #TempTable2 SET CKSafetyDivision = (SELECT dbo.SO_CustomSettingValues.Contents FROM dbo.SO_CustomSetting INNER JOIN dbo.SO_CustomSettingValues ON dbo.SO_CustomSetting.SettingID = dbo.SO_CustomSettingValues.SettingID WHERE dbo.SO_CustomSetting.SettingName = 'CKSafetyDivision' AND dbo.SO_CustomSettingValues.InvoiceID = #TempTable2.InvoiceID ) UPDATE #TempTable2 SET CashSaleDebtor = (SELECT dbo.DB_CustomSettingValues.Contents FROM dbo.DB_Main INNER JOIN dbo.DB_CustomSettingValues ON dbo.DB_Main.DebtorID = dbo.DB_CustomSettingValues.DebtorID INNER JOIN dbo.DB_CustomSetting ON dbo.DB_CustomSettingValues.SettingID = dbo.DB_CustomSetting.SettingID WHERE (dbo.DB_CustomSetting.SettingName = 'CashOnly') AND #TempTable2.DebtorID = DB_CustomSettingValues.DebtorID) UPDATE #TempTable2 SET CashSaleDebtor = ISNULL(#TempTable2.CashSaleDebtor,0) UPDATE #TempTable2 SET PrintTandC = (SELECT dbo.SO_CustomSettingValues.Contents FROM dbo.SO_Main INNER JOIN dbo.SO_CustomSettingValues ON dbo.SO_Main.InvoiceID = dbo.SO_CustomSettingValues.InvoiceID INNER JOIN dbo.SO_CustomSetting ON dbo.SO_CustomSettingValues.SettingID = dbo.SO_CustomSetting.SettingID WHERE dbo.SO_CustomSetting.SettingName = 'PrintTandC' AND #TempTable2.InvoiceID = dbo.SO_CustomSettingValues.InvoiceID ) UPDATE #TempTable2 SET BSB = (select BSBN from GL_BankDetails WHERE DefaultBank = 1) UPDATE #TempTable2 SET BankAccountNo = (select AccountNumber from GL_BankDetails WHERE DefaultBank = 1) UPDATE #TempTable2 SET BankAccountName = (select AccountName from GL_BankDetails WHERE DefaultBank = 1) IF @ShowKitDetail = 1 SELECT *, @OrderedCart1ExGST [OrderedCartage1ExTax], @OrderedCart2ExGST [OrderedCartage2ExTax], @OrderedCart3ExGST [OrderedCartage3ExTax], @OrderedCart1GST [OrderedCartage1Tax], @OrderedCart2GST [OrderedCartage2Tax], @OrderedCart3GST [OrderedCartage3Tax], @OrderedCart1IncGST [OrderedCartage1IncTax], @OrderedCart2IncGST [OrderedCartage2IncTax], @OrderedCart3IncGST [OrderedCartage3IncTax] FROM #TempTable2 ORDER BY LineNum ELSE SELECT *, @OrderedCart1ExGST [OrderedCartage1ExTax], @OrderedCart2ExGST [OrderedCartage2ExTax], @OrderedCart3ExGST [OrderedCartage3ExTax], @OrderedCart1GST [OrderedCartage1Tax], @OrderedCart2GST [OrderedCartage2Tax], @OrderedCart3GST [OrderedCartage3Tax], @OrderedCart1IncGST [OrderedCartage1IncTax], @OrderedCart2IncGST [OrderedCartage2IncTax], @OrderedCart3IncGST [OrderedCartage3IncTax] FROM #TempTable2 ORDER BY LineNum SET NOCOUNT OFF