/****** Object:  StoredProcedure [rdld].[GetFinanceForDriver]  ******/
-- =============================================
-- Author:     Jeff Spindler
-- Date: 11/22
-- Description:   Gets the finance info
-- =============================================
CREATE PROCEDURE [rdld].[GetFinanceForDriver]
  @DriverId       INT,
  @AcctNumbersCSV AS VARCHAR(MAX)
AS
BEGIN
   SET NOCOUNT ON;
   SET DEADLOCK_PRIORITY LOW;
   DECLARE @AcctNumbers AS dbo.Nvarchar32List;
   INSERT INTO             @AcctNumbers(Item)
   SELECT tuple
   FROM
        rdld.split_string(@AcctNumbersCSV, ',');
   SELECT DISTINCT
         'Statement' AS AccountType,
         (
             SELECT ISNULL(SUM(CASE
                                   WHEN DOCTYPE = 5
                                   THEN -1
                                   ELSE 1
                               END * CURTRXAM), 0) AS CurrentBalance
             FROM
                  PM20000_Synonym as PM20000
             WHERE  VENDORID = SysUserLaborTypeMap.VendorId
         ) AS CurrentBalance
   FROM
        SysUser
        INNER JOIN SysUserLaborTypeMap ON Sysuser.SysUserID = SysUserLaborTypeMap.SysUserFID
   WHERE @DriverId = SysUser.SysUserId
   UNION ALL
   SELECT DISTINCT
          AccountNumbers.Item AS AccountType,
   (
       SELECT ISNULL(SUM(GL20000.CRDTAMNT - GL20000.DEBITAMT), 0) AS CurrentBalance
       FROM
            GL20000_Synonym AS GL20000
            LEFT JOIN GL00105_Synonym AS GL00105 ON GL00105.ACTINDX = GL20000.ACTINDX
       WHERE  GL00105.ACTNUMST = AccountNumbers.Item
   ) AS CurrentBalance
   FROM
        @AcctNumbers AS AccountNumbers FOR XML RAW;

END;
GO
GRANT EXECUTE ON  [rdld].[GetFinanceForDriver] TO [MssExec]
GO