SQL Code

--Payment:
SELECT 'P', 'CRD', 'Hosp.Payables', B.ADDRESS4, B.PYMNT_ID_REF, SUM(A.PAID_AMT), B.REMIT_VENDOR, A.REMIT_ADDR_SEQ_NUM,
B.NAME1, 'USD'
FROM PS_PYMNT_VCHR_XREF A
INNER JOIN PS_PAYMENT_TBL B ON B.PYMNT_ID = A.PYMNT_ID AND A.BANK_SETID = B.BANK_SETID
     AND A.BANK_CD = B.BANK_CD
     AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
WHERE B.PYMNT_DT = '2021-01-04'
 --AND A.PYMNT_ID = '0000263556'
   AND B.REMIT_VENDOR IN ('51503A', '71520A')
   --AND B.REMIT_VENDOR = '71520A'
  AND B.PYMNT_STATUS NOT IN ('S','V')
     AND (( A.BANK_SETID = 'SHARE'
     AND A.BANK_CD = 'MT'
     AND A.BANK_ACCT_KEY = 'TGC')
     OR ( A.BANK_SETID = 'SHARE'
     AND A.BANK_CD = 'PNC'
     AND A.BANK_ACCT_KEY = '1'))
     --AND D.VENDOR_CLASS <> 'E'
GROUP BY B.ADDRESS4, B.PYMNT_ID_REF, B.REMIT_VENDOR, A.REMIT_ADDR_SEQ_NUM,
B.NAME1
ORDER BY B.NAME1, PYMNT_ID_REF

--Remittances:
SELECT 2, A.PYMNT_ID_REF, C.INVOICE_ID, C.INVOICE_DT, C.GROSS_AMT, C.DSCNT_AMT, B.VOUCHER_ID,
C.PO_ID
FROM PS_PYMNT_VCHR_XREF B
INNER JOIN PS_PAYMENT_TBL A ON B.PYMNT_ID = A.PYMNT_ID AND A.BANK_SETID = B.BANK_SETID
     AND A.BANK_CD = B.BANK_CD
     AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
INNER JOIN PS_VOUCHER C ON B.VOUCHER_ID = C.VOUCHER_ID AND C.BUSINESS_UNIT = B.BUSINESS_UNIT
WHERE A.PYMNT_DT = '2021-01-04'
 --AND A.PYMNT_ID = '0000263556'
 --AND B.REMIT_VENDOR = '51503A'
 AND A.PYMNT_ID_REF IN ('023946', '023943')
 --OR A.PYMNT_ID_REF =  '023943'
 ORDER BY PYMNT_ID_REF


--Control data:
WITH CONTROLTOTALS AS (
SELECT  SUM(A.PAID_AMT) AS TOT_PAID_AMT
FROM PS_PYMNT_VCHR_XREF A
INNER JOIN PS_PAYMENT_TBL B ON B.PYMNT_ID = A.PYMNT_ID AND A.BANK_SETID = B.BANK_SETID
     AND A.BANK_CD = B.BANK_CD
     AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
WHERE B.PYMNT_DT = '2021-01-04'
   AND B.PYMNT_ID_REF IN ('023946','023943'))

, CONTROLCOUNT1 AS (

SELECT COUNT(PYMNT_ID_REF) AS PYMNT_COUNT
FROM PS_PAYMENT_TBL B
WHERE B.PYMNT_DT = '2021-01-04'
AND PYMNT_ID <> ''
  AND  B.PYMNT_ID_REF IN ('023946','023943')) ,


CONTROLCOUNT2 AS (
SELECT COUNT(*) AS REMIT_COUNT
FROM PS_PYMNT_VCHR_XREF A
INNER JOIN PS_PAYMENT_TBL B ON B.PYMNT_ID = A.PYMNT_ID AND A.BANK_SETID = B.BANK_SETID
     AND A.BANK_CD = B.BANK_CD
     AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
WHERE PYMNT_DT = '2021-01-04'
 AND B.PYMNT_ID_REF IN ('023946','023943') )


SELECT 'C', A.TOT_PAID_AMT, B.PYMNT_COUNT, C.REMIT_COUNT
FROM CONTROLTOTALS A, CONTROLCOUNT1 B, CONTROLCOUNT2 C