Editing Add Unit Price to Order Item Report
Jump to navigation
Jump to search
Warning: You are not logged in. Your IP address will be publicly visible if you make any edits. If you log in or create an account, your edits will be attributed to your username, along with other benefits.
The edit can be undone. Please check the comparison below to verify that this is what you want to do, and then save the changes below to finish undoing the edit.
Latest revision | Your text | ||
Line 1: | Line 1: | ||
+ | |||
== Duplicate the Standard Order Ticket - Order Item report == | == Duplicate the Standard Order Ticket - Order Item report == | ||
Line 19: | Line 20: | ||
<br>[[File:SQLStatementSelectAll.png]] | <br>[[File:SQLStatementSelectAll.png]] | ||
<br>3. Delete the selected text to clear the SQL Statement box. | <br>3. Delete the selected text to clear the SQL Statement box. | ||
− | <br>4. Copy the query | + | <br>4. Copy the query below and paste into the SQL Statement box. |
− | + | <br>5. Click '''Test SQL'''. You should get a message that SQL is Valid. Click '''Save''' at the top. | |
− | <br> | + | |
+ | |||
+ | SELECT | ||
+ | UID, InvoiceUID, OrderNumber, ServPerf, ServReq | ||
+ | INTO #TempOrders | ||
+ | FROM SERVICE | ||
+ | WHERE | ||
+ | (:RecordUID is null or :RecordUID = InvoiceUID) | ||
+ | AND | ||
+ | InvoiceTypeCode =7 | ||
+ | UNION ALL | ||
+ | --Install | ||
+ | SELECT | ||
+ | UID, InvoiceUID, orderNumber, ServPerf, ServReq | ||
+ | FROM Install | ||
+ | WHERE | ||
+ | (:RecordUID is null or :RecordUID = InvoiceUID) AND | ||
+ | InvoiceTypeCode =7 | ||
+ | UNION ALL | ||
+ | --Invoice that is not a type code 7 which handled above | ||
+ | SELECT | ||
+ | UID, UID as InvoiceUID, InvoiceNumber as Ordernumber, ServPerf, ServReq | ||
+ | FROM Invoice | ||
+ | WHERE | ||
+ | (:RecordUID is null or :RecordUID = UID) | ||
+ | and InvoiceTypeCode <> 7; | ||
+ | --Fill in Report information | ||
+ | --generic reference | ||
+ | SELECT | ||
+ | TOS.InvoiceUID,TOS.OrderNumber, TOS.ServPerf as ServicePerformed, TOS.ServReq as ServiceRequested, | ||
+ | OD.*, | ||
+ | I.*,(I.Payments + I.PrepaidUsed) as PayCredit, 0.00 as UnitPrice, null as itemnum | ||
+ | FROM OrderDetail OD | ||
+ | LEFT JOIN #TempOrders TOS ON OD.OrderUID = TOS.UID | ||
+ | FULL JOIN Invoice I ON TOS.InvoiceUID = I.UID | ||
+ | WHERE | ||
+ | (:RecordUID is null or I.UID = :RecordUID or TOS.InvoiceUID =:RecordUID )and | ||
+ | ( OD.PrintHide = 'False' or OD.PrintHide is null) and (OD.ItemTypeCode != 1 and OD.ItemTypeCode != 2 and OD.ItemTypeCode != 3) | ||
+ | |||
+ | UNION ALL | ||
+ | SELECT | ||
+ | TOS.InvoiceUID,TOS.OrderNumber, TOS.ServPerf as ServicePerformed, TOS.ServReq as ServiceRequested, | ||
+ | OD.*, | ||
+ | I.*,(I.Payments + I.PrepaidUsed) as PayCredit, ot.LaborRate as unitprice, null as itemnum | ||
+ | FROM OrderDetail OD | ||
+ | LEFT JOIN #TempOrders TOS ON OD.OrderUID = TOS.UID | ||
+ | FULL JOIN Invoice I ON TOS.InvoiceUID = I.UID | ||
+ | join OrderTask OT on OD.DetailUID = OT.UID | ||
+ | WHERE | ||
+ | (:RecordUID is null or I.UID = :RecordUID or TOS.InvoiceUID =:RecordUID )and | ||
+ | ( OD.PrintHide = 'False' or OD.PrintHide is null) and (OD.ItemTypeCode = 1) | ||
+ | UNION ALL | ||
+ | SELECT | ||
+ | TOS.InvoiceUID,TOS.OrderNumber, TOS.ServPerf as ServicePerformed, TOS.ServReq as ServiceRequested, | ||
+ | OD.*, | ||
+ | I.*,(I.Payments + I.PrepaidUsed) as PayCredit, ot.Retail as unitprice, null as itemnum | ||
+ | FROM OrderDetail OD | ||
+ | LEFT JOIN #TempOrders TOS ON OD.OrderUID = TOS.UID | ||
+ | FULL JOIN Invoice I ON TOS.InvoiceUID = I.UID | ||
+ | join OrderServices OT on OD.DetailUID = OT.UID | ||
+ | WHERE | ||
+ | (:RecordUID is null or I.UID = :RecordUID or TOS.InvoiceUID =:RecordUID )and | ||
+ | ( OD.PrintHide = 'False' or OD.PrintHide is null) and (OD.ItemTypeCode = 2) | ||
+ | UNION ALL | ||
+ | SELECT | ||
+ | TOS.InvoiceUID,TOS.OrderNumber, TOS.ServPerf as ServicePerformed, TOS.ServReq as ServiceRequested, | ||
+ | OD.*, | ||
+ | I.*,(I.Payments + I.PrepaidUsed) as PayCredit, ot.Retail as unitprice, ot.ItemNum | ||
+ | FROM OrderDetail OD | ||
+ | LEFT JOIN #TempOrders TOS ON OD.OrderUID = TOS.UID | ||
+ | FULL JOIN Invoice I ON TOS.InvoiceUID = I.UID | ||
+ | join OrderMat OT on OD.DetailUID = OT.UID | ||
+ | WHERE | ||
+ | (:RecordUID is null or I.UID = :RecordUID or TOS.InvoiceUID =:RecordUID )and | ||
+ | ( OD.PrintHide = 'False' or OD.PrintHide is null) and (OD.ItemTypeCode = 3) | ||
+ | |||
+ | Order by I.UID , OrderNumber, OD.Sequence | ||
+ | |||
== Make Room for Unit Price == | == Make Room for Unit Price == |