CREATE PROCEDURE test1
(@SRCH VARCHAR(50) ='')
AS
BEGIN
SELECT qryTransSearchAll.fldOrderID AS ID, qryTransSearchAll.fldDblGlzSysRef AS Ref, qryTransSearchAll.fldOQBRef AS [QB Invoice], qryTransSearchAll.fldOQBDptRef AS [QB Deposit], qryTransSearchAll.cfClient AS Name, qryTransSearchAll.fldCBusiness AS Business, qryTransSearchAll.cfAddress AS Site, qryTransSearchAll.fldOJobDescription AS Job, qryTransSearchAll.cftotal AS Total, qryTransSearchAll.cfpayment AS Payment, qryTransSearchAll.fldPayType AS Type, qryTransSearchAll.fldOrStatus AS Status
FROM (
SELECT qryOrderExtended.fldOrderID, qryOrderExtended.fldDblGlzSysRef, qryOrderExtended.fldOQBRef, qryOrderExtended.fldOQBDptRef, dbo.tblClients.fldCBusiness,
qryOrderExtended.fldOJobDescription, Str(CAST([cfGrandTotal] AS MONEY)) AS cfTotal, Str(CAST([fldTCredit] AS MONEY)) AS cfPayment,
qryTransactionsExtended.fldPayType, dbo.lkptblOrderStatus.fldOrStatus, lkpqryClient2.cfClient, lkpqryAddress1.cfAddress
FROM (
SELECT dbo.tblAddress.fldAddressID, concat([fldAAddress1], ' ', [fldastreet], ', ', [fldacity], ', ', [fldapostcode]) AS cfAddress
FROM dbo.tblClients INNER JOIN dbo.tblAddress ON dbo.tblClients.fldClientID = dbo.tblAddress.fldAClientID
) lkpqryAddress1 INNER JOIN (((((
SELECT *, ISNULL([fldototalquote], 0)+[cfvat]+[cfAgent] AS cfGrandTotal
FROM (SELECT *, CAST((ISNULL([fldototalquote], 0)+ISNULL([cfvat], 0))*(ISNULL(([fldoagentcommision]/100),0))*((ISNULL([fldovatrate], 0)/100)+1) AS MONEY) AS cfAgent
FROM (SELECT dbo.tblOrders.*, dbo.tblAddress.fldAClientID, CAST(ISNULL(([fldototalquote]*([fldOvatpercentage]/100))*([fldovatrate]/100), 0) AS MONEY) AS cfVat
FROM dbo.tblAddress RIGHT JOIN dbo.tblOrders ON dbo.tblAddress.fldAddressID = dbo.tblOrders.fldOAddressID
) qryAmountDue1) qryAmountDue2
) qryOrderExtended LEFT JOIN (
SELECT dbo.tblTransaction.*, dbo.lkptblPayType.fldPayType
FROM dbo.lkptblPayType INNER JOIN dbo.tblTransaction ON dbo.lkptblPayType.fldPayTypeID = dbo.tblTransaction.fldTPayTypeID
) qryTransactionsExtended ON qryOrderExtended.fldOrderID = qryTransactionsExtended.fldTOrderID) INNER JOIN
dbo.tblClients ON qryOrderExtended.fldAClientID = dbo.tblClients.fldClientID) INNER JOIN dbo.lkptblOrderStatus ON qryOrderExtended.fldOStatusID = dbo.lkptblOrderStatus.fldOrderStatusID)
INNER JOIN(
SELECT dbo.tblClients.fldClientID, concat([fldCFirstname], ' ', [fldclastname]) AS cfClient
FROM dbo.tblClients
) lkpqryClient2 ON dbo.tblClients.fldClientID = lkpqryClient2.fldClientID) ON lkpqryAddress1.fldAddressID = qryOrderExtended.fldOAddressID
) qryTransSearchAll
WHERE (((qryTransSearchAll.fldOrderID) Like '%@SRCH%')) OR (((qryTransSearchAll.fldDblGlzSysRef) Like '%@SRCH%')) OR (((qryTransSearchAll.cfClient) Like '%@SRCH%'))
OR (((qryTransSearchAll.fldCBusiness) Like '%@SRCH%')) OR (((qryTransSearchAll.cfAddress) Like '%@SRCH%')) OR (((qryTransSearchAll.fldOJobDescription) Like '%@SRCH%'))
OR (((qryTransSearchAll.cftotal) Like '%@SRCH%')) OR (((qryTransSearchAll.cfpayment) Like '%@SRCH%')) OR (((qryTransSearchAll.fldPayType) Like '%@SRCH%'))
OR (((qryTransSearchAll.fldOQBRef) Like '%@SRCH%')) OR (((qryTransSearchAll.fldOQBDptRef) Like '%@SRCH%'))
ORDER BY qryTransSearchAll.cfClient, qryTransSearchAll.fldCBusiness
END