[rdld].[GetFinanceDetailForDriverAccount]
CREATE PROCEDURE [rdld].[GetFinanceDetailForDriverAccount]
@DriverId INT,
@AccountNumber AS VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;
IF @AccountNumber = 'Statement'
BEGIN
SET @AccountNumber =
(
SELECT TOP 1 VendorID
FROM SysUserLaborTypeMap
INNER JOIN LaborType ON LaborType.PriKey = SysUserLaborTypeMap.LaborTypeFID AND LaborType.IsLongDistance = 1
WHERE SysUserFID = @DriverId
AND VendorID IS NOT NULL
GROUP BY VendorID
ORDER BY COUNT(*) DESC
);
END;
SELECT
AccountNumber,
TransactionDate,
Description,
SUM(Amount) AS Amount,
OrderNumber
FROM
(
SELECT GL00105.ACTNUMST AS AccountNumber,
DATEADD(dd, 0, DATEDIFF(dd, 0, GL20000.TRXDATE)) AS TransactionDate,
GL20000.REFRENCE AS Description,
GL20000.CRDTAMNT - GL20000.DEBITAMT AS Amount,
'N/A' AS OrderNumber
FROM
GL20000_Synonym as GL20000
LEFT JOIN GL00105_Synonym as GL00105 ON GL00105.ACTINDX = GL20000.ACTINDX
UNION ALL
SELECT GL00105.ACTNUMST AS AccountNumber,
DATEADD(dd, 0, DATEDIFF(dd, 0, GL30000.TRXDATE)) AS TransactionDate,
GL30000.REFRENCE AS Description,
GL30000.CRDTAMNT - GL30000.DEBITAMT AS Amount,
'N/A' AS OrderNumber
FROM
GL30000_Synonym as GL30000
LEFT JOIN GL00105_Synonym as GL00105 ON GL00105.ACTINDX = GL30000.ACTINDX
UNION ALL
SELECT VENDORID AS AccountNumber,
DATEADD(dd, 0, DATEDIFF(dd, 0, PSTGDATE)) AS TransactionDate,
TRXDSCRN AS Description,
CASE
WHEN DOCTYPE IN(5, 6)
THEN -1
ELSE 1
END * DOCAMNT AS Amount,
'N/A' AS OrderNumber
FROM
PM20000_Synonym as PM20000
WHERE VCHRNMBR NOT LIKE 'RV%'
UNION ALL
SELECT VENDORID AS AccountNumber,
DATEADD(dd, 0, DATEDIFF(dd, 0, PSTGDATE)) AS TransactionDate,
CASE
WHEN DOCTYPE = 6
THEN 'STMT'
ELSE TRXDSCRN
END AS Description,
CASE
WHEN DOCTYPE IN(5, 6)
THEN -1
ELSE 1
END * DOCAMNT AS Amount,
'N/A' AS OrderNumber
FROM
PM30200_Synonym as PM30200
WHERE VCHRNMBR NOT LIKE 'RV%'
UNION ALL
SELECT CommissionedDetail.VendorID AS AccountNumber,
DATEADD(dd, 0, DATEDIFF(dd, 0, CommissionedDetail.PostedDate)) AS TransactionDate,
Orders.OrderNo AS Description,
SUM(CommissionedDetail.Amount) AS Amount,
Orders.OrderNo AS OrderNumber
FROM
CommissionedDetail
INNER JOIN BillingMinorItem ON CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey
INNER JOIN BillingMajorItem ON BillingMinorItem.BMajPriKey = BillingMajorItem.BMajPriKey
INNER JOIN Orders ON BillingMajorItem.OrdPriKey = Orders.PriKey
GROUP BY CommissionedDetail.VendorID,
Orders.OrderNo,
DATEADD(dd, 0, DATEDIFF(dd, 0, CommissionedDetail.PostedDate))
UNION ALL
SELECT dbo.CommissionedDetail.VendorID AS AccountNumber,
DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.CommissionedDetail.PostedDate)) AS TransactionDate,
dbo.Orders.OrderNo AS Description,
SUM(dbo.CommissionedDetail.Amount) AS Amount,
dbo.Orders.OrderNo AS OrderNumber
FROM
dbo.CommissionedDetail
INNER JOIN dbo.BillingMinorDist ON dbo.CommissionedDetail.BminDistPriKey = dbo.BillingMinorDist.BMinDistPriKey
INNER JOIN dbo.BillingMinorItem ON dbo.BillingMinorDist.BMinPriKey = dbo.BillingMinorItem.BMinPriKey
INNER JOIN dbo.BillingMajorItem ON dbo.BillingMinorItem.BMajPriKey = dbo.BillingMajorItem.BMajPriKey
INNER JOIN dbo.Orders ON dbo.BillingMajorItem.OrdPriKey = dbo.Orders.PriKey
GROUP BY dbo.CommissionedDetail.VendorID,
dbo.Orders.OrderNo,
DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.CommissionedDetail.PostedDate))
UNION ALL
SELECT
SplitCommissions.VendorID AS AccountNumber,
DATEADD(dd, 0, DATEDIFF(dd, 0, SplitCommissions.PostedDate)) AS TransactionDate,
Orders.OrderNo AS Description,
SUM(ISNULL(SplitCommissions.Amount,0)) AS Amount,
Orders.OrderNo AS OrderNumber
FROM
SplitCommissions
INNER JOIN CommissionedDetail ON SplitCommissions.CDPriKey = CommissionedDetail.CDPriKey
INNER JOIN BillingMinorItem ON CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey
INNER JOIN BillingMajorItem ON BillingMinorItem.BMajPriKey = BillingMajorItem.BMajPriKey
INNER JOIN Orders ON BillingMajorItem.OrdPriKey = Orders.PriKey
GROUP BY SplitCommissions.VendorID,
Orders.OrderNo,
BillingMinorItem.Description,
DATEADD(dd, 0, DATEDIFF(dd, 0, SplitCommissions.PostedDate))
UNION ALL
SELECT
ThirdParty.VendorID AS AccountNumber,
DATEADD(dd, 0, DATEDIFF(dd, 0, ThirdParty.PostedDate)) AS TransactionDate,
Orders.OrderNo AS Description,
SUM(ISNULL(ThirdParty.PayableAmount,0)) AS Amount,
Orders.OrderNo AS OrderNumber
FROM
ThirdParty
INNER JOIN BillingMinorItem ON ThirdParty.BMinPriKey = BillingMinorItem.BMinPriKey
INNER JOIN BillingMajorItem ON BillingMinorItem.BMajPriKey = BillingMajorItem.BMajPriKey
INNER JOIN Orders ON BillingMajorItem.OrdPriKey = Orders.PriKey
GROUP BY ThirdParty.VendorID,
Orders.OrderNo,
DATEADD(dd, 0, DATEDIFF(dd, 0, ThirdParty.PostedDate))
) AS Tbl1
WHERE AccountNumber = @AccountNumber
AND TransactionDate > DATEADD(mm, -6, GETDATE())
AND Amount <> 0
GROUP BY AccountNumber,
TransactionDate,
Description,
OrderNumber
FOR XML RAW;
END;
GO
GRANT EXECUTE ON [rdld].[GetFinanceDetailForDriverAccount] TO [MssExec]
GO
