SELECT

       'MySource'                                     AS SourceDB,
       'MySite'                                   AS 'SiteName',
       Rtrim(apibh.idvend)                          AS VendorNumber,
        
       
       Rtrim(apibh.idinvc)                          AS VoucherNumber,
       Sum(apibh.amtgrosdst)                        AS VoucherTotalDomestic,
       Sum(apibh.amtgrosdst) * ( apibh.exchratehc ) AS VoucherTotalUSD,
       (SELECT DISTINCT ( Sum(p.amtpaym) )
        FROM   aptcr r WITH(NOLOCK)
               INNER JOIN aptcp p
                       ON r.btchtype = p.batchtype
                          AND r.cntbtch = p.cntbtch
                          AND r.cntentr = p.cntrmit
               INNER JOIN apibh H
                       ON p.idvend = h.idvend
                          AND p.idinvc = H.idinvc
        WHERE  H.idinvc = apibh.idinvc
        GROUP  BY H.idinvc)                         AS PayAmt,
        
        
        (Sum(apibh.amtgrosdst) -
      
        (SELECT DISTINCT ( Sum(p.amtpaym) )
        FROM   aptcr r WITH(NOLOCK)
               INNER JOIN aptcp p
                       ON r.btchtype = p.batchtype
                          AND r.cntbtch = p.cntbtch
                          AND r.cntentr = p.cntrmit
               INNER JOIN apibh H
                       ON p.idvend = h.idvend
                          AND p.idinvc = H.idinvc
        WHERE  H.idinvc = apibh.idinvc
        GROUP  BY H.idinvc) )                        AS OpenAmountDomestic,
        
        
           (Sum(apibh.amtgrosdst) -
      
        ((SELECT DISTINCT ( Sum(p.amtpaym) )
        FROM   aptcr r  WITH(NOLOCK)
               INNER JOIN aptcp p
                       ON r.btchtype = p.batchtype
                          AND r.cntbtch = p.cntbtch
                          AND r.cntentr = p.cntrmit
               INNER JOIN apibh H
                       ON p.idvend = h.idvend
                          AND p.idinvc = H.idinvc
        WHERE  H.idinvc = apibh.idinvc
        GROUP  BY H.idinvc) )  *  ( apibh.exchratehc ))           AS OpenAmountUSD,
        
        (
        Select DATEDIFF(day,(SELECT DISTINCT ( Max(dbo.Udf_convert_int_date(r.datermit)) )
        FROM   aptcr r   WITH(NOLOCK)
               INNER JOIN aptcp p
                       ON r.btchtype = p.batchtype
                          AND r.cntbtch = p.cntbtch
                          AND r.cntentr = p.cntrmit
               INNER JOIN apibh H
                       ON p.idvend = h.idvend
                          AND p.idinvc = H.idinvc
        WHERE  H.idinvc = apibh.idinvc
        GROUP  BY H.idinvc),GETDATE()))AS DueDays, 
        
        '' As AgeDays ,
        
        
        
        
         (SELECT DISTINCT ( Max(dbo.Udf_convert_int_date(r.datermit)) )
        FROM   aptcr r  WITH(NOLOCK)
               INNER JOIN aptcp p
                       ON r.btchtype = p.batchtype
                          AND r.cntbtch = p.cntbtch
                          AND r.cntentr = p.cntrmit
               INNER JOIN apibh H
                       ON p.idvend = h.idvend
                          AND p.idinvc = H.idinvc
        WHERE  H.idinvc = apibh.idinvc
        GROUP  BY H.idinvc)                         AS PaidDate,
        
        
       dbo.Udf_convert_int_date(apibh.datebus)      AS PostedDate,
       dbo.Udf_convert_int_date(apibh.datebus)      AS AppliedDate,
       dbo.Udf_convert_int_date(apibh.dateinvc)     AS AgingDate,
       dbo.Udf_convert_int_date(apibh.datedue)      AS DueDate,
       dbo.Udf_convert_int_date(apibh.dateinvc)     AS DocumentDate,
       NULL                                         AS ReceivedDate,
       CASE
         WHEN ( apibh.datedisc ) = 0 THEN NULL
         ELSE dbo.Udf_convert_int_date(apibh.datedisc)
       END                                          AS DiscountDate,
       apibh.codecurn                               AS CurrencyCode,
       apibh.exchratehc                             AS EffectiveExchangeRate
FROM   apibh AS APIBH   WITH(NOLOCK)
WHERE  1 = 1
--AND APIBH.IDINVC = '22036'
--AND APIBH.IDVEND = 'ABE129'
GROUP  BY ( apibh.idvend ),
          ( apibh.idinvc ),
          ( apibh.amtgrosdst ),
          ( apibh.amtgrosdst - Isnull(apibh.amtgrosdst, 0) - apibh.amtdiscavl ),
          ( ( apibh.amtgrosdst * apibh.exchratehc ) -
            Isnull(apibh.amtgrosdst, 0) * apibh.exchratehc ) - (
          apibh.amtdiscavl * apibh.exchratehc ),
          ( apibh.datebus ),
          apibh.datebus,
          apibh.dateinvc,
          apibh.datedue,
          CASE
            WHEN ( apibh.datedisc ) = 0 THEN NULL
            ELSE dbo.Udf_convert_int_date(apibh.datedisc)
          END,
          apibh.amtdiscavl,
          apibh.codecurn,
          apibh.exchratehc,
          apibh.idtrx,
          Isnull(apibh.amtgrosdst, 0),
          apibh.amttotdist 

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass