SELECT
        KONA.Agreement,
        KONA.SalesOrg,
        KONA.DistChannel,
        KONA.Division,
        KONA.TYPE,
        KONA.ValidityStart,
        KONA.ValidityEnd,
        KONA.CondTypeGroup,
        KONA.Description,
        KONA.STATUS,
        VBRK.[Billing TYPE],
        VBRK.[Document Currency],
        VBRK.[Pricing PROCEDURE],
        VBRK.DocConditionNbr,
        VBRK.[Billing DATE],
        VBRK.Payer,
        '' AS [VBRK SoldTo],
        VBRK.NET,
        VBRK.TAX,
        VBRK.TOTAL,
        VBRK.BillDoc,
        VBRK.[Created ON],
        VBRK.TIME,
        '' AS [APRE BillDoc],
        '' AS [APRE Net],
        '' AS [APRE Tax],
        '' AS [APRE Canceled],
        '' AS [APRE ShipTo],
        '' AS [APRE TOTAL],
        '' AS [DTNO BillDoc],
        '' AS [DTNO Net],
        '' AS [DTNO Tax],
        '' AS [DTNO ShipTo],
        '' AS [DTNO Canceled],
        '' AS [DTNO TOTAL],
        '' AS [DTNI BillDoc],
        '' AS [DTNI Amount],
        '' AS [DTNI Tax],
        '' AS [DTNI Canc],
        '' AS [MACN BillDoc],
        '' AS [MACN Net],
        '' AS [MACN Tax],
        '' AS [MACN Canceled],
        '' AS [MACN ShipTo],
        '' AS [MACN TOTAL],
        '' AS [Payer Name 1],
        '' AS [Payer Name 2],
        '' AS [Payer City],
        '' AS [ShipTo Name 1],
        '' AS [ShipTo Name 2],
        '' AS [ShipTo City],
        '' AS [SoldTo Name 1],
        '' AS [SoldTo Name 2],
        '' AS [SoldTo City]


FROM (((((((((((((KONA
        RIGHT JOIN VBRK ON KONA.Agreement = VBRK.Agreement)
        LEFT JOIN APRE ON VBRK.Payer = APRE.Payer)
        LEFT JOIN DTNO ON APRE.Payer = DTNO.Payer)
        LEFT JOIN DTNI ON DTNO.Payer = DTNI.Payer)
        LEFT JOIN MACN ON DTNI.Payer = MACN.Payer)
        LEFT JOIN KNA1 KNA11 ON APRE.Payer = KNA11.Customer)
        LEFT JOIN KNA1 KNA12 ON APRE.Payer = KNA12.Customer)
        LEFT JOIN KNA1 KNA13 ON APRE.Payer = KNA13.Customer)
        LEFT JOIN KNA1 KNA21 ON APRE.ShipTo = KNA21.Customer)
        LEFT JOIN KNA1 KNA22 ON APRE.ShipTo = KNA22.Customer)
        LEFT JOIN KNA1 KNA23 ON APRE.SHipTo = KNA23.Customer)
        LEFT JOIN KNA1 KNA31 ON APRE.SoldTo = KNA31.Customer)
        LEFT JOIN KNA1 KNA32 ON APRE.SoldTo = KNA32.Customer)
        LEFT JOIN KNA1 KNA33 ON APRE.SoldTo = KNA33.Customer
WHERE
        VBRK.[Billing TYPE] LIKE 'ZY1' AND
        VBRK.CANCEL IS NULL AND
        KONA.DESCRIPTION LIKE '* - #' OR
        KONA.DESCRIPTION LIKE '* - 20S - *' AND
        VBRK.Payer IN
        (
            '30165269',
            '30163572',
            '30162857',
            '30162621',
            '30166585',
            '30161260',
            '30161141',
            '30158583',
            '30165514'
        )

UNION ALL

SELECT
        '' AS [Agreement],
        '' AS SalesOrg,
        '' AS DistChannel,
        '' AS Division,
        '' AS TYPE,
        '' AS ValidityStart,
        '' AS ValidityEnd,
        '' AS CondTypeGroup,
        '' AS Description,
        '' AS STATUS,
        '' AS [Billing TYPE],
        '' AS [Document Currency],
        '' AS [Pricing PROCEDURE],
        '' AS DocConditionNbr,
        '' AS [Billing DATE],
        VBRK.Payer,
        VBRK.SoldTo AS [VBRK SoldTo],
        SUM(VBRK.NET) AS NET,
        SUM(VBRK.TAX) AS TAX,
        SUM(VBRK.TOTAL) AS TOTAL,
        '' AS BillDoc,
        '' AS [Created ON],
        '' AS [TIME],
        APRE.BillDoc AS [APRE BillDoc],
        APRE.Net AS [APRE Net],
        APRE.Tax AS [APRE Tax],
        APRE.Canceled AS [APRE Canceled],
        APRE.ShipTo AS [APRE ShipTo],
        APRE.TOTAL AS [APRE TOTAL],
        DTNO.BillDoc AS [DTNO BillDoc],
        DTNO.NET AS [DTNO Net],
        DTNO.TAX AS [DTNO Tax],
        DTNO.ShipTo AS [DTNO ShipTo],
        DTNO.Canceled AS [DTNO Canceled],
        DTNO.TOTAL AS [DTNO TOTAL],
        DTNI.BillDoc AS [DTNI BillDoc],
        DTNI.Amount AS [DTNI Amount],
        DTNI.Tax AS [DTNI Tax],
        DTNI.Canc AS [DTNI Canc],
        MACN.BillDoc AS [MACN BillDoc],
        MACN.Net AS [MACN Net],
        MACN.Tax AS [MACN Tax],
        MACN.Canceled AS [MACN Canceled],
        MACN.ShipTo AS [MACN ShipTo],
        MACN.ToTAL AS [MACN TOTAL],
        KNA11.[Name 1] AS [Payer Name 1],
        KNA12.[Name 2] AS [Payer Name 2],
        KNA13.[City] AS [Payer City],
        KNA21.[Name 1] AS [ShipTo Name 1],
        KNA22.[Name 2] AS [ShipTo Name 2],
        KNA23.[City] AS [ShipTo City],
        KNA31.[Name 1] AS [SoldTo Name 1],
        KNA32.[Name 2] AS [SoldTo Name 2],
        KNA33.[City] AS [SoldTo City]

FROM (((((((((((((KONA
        RIGHT JOIN VBRK ON KONA.Agreement = VBRK.Agreement)
        LEFT JOIN APRE ON VBRK.Payer = APRE.Payer)
        LEFT JOIN DTNO ON APRE.Payer = DTNO.Payer)
        LEFT JOIN DTNI ON DTNO.Payer = DTNI.Payer)
        LEFT JOIN MACN ON DTNI.Payer = MACN.Payer)
        LEFT JOIN KNA1 KNA11 ON APRE.Payer = KNA11.Customer)
        LEFT JOIN KNA1 KNA12 ON APRE.Payer = KNA12.Customer)
        LEFT JOIN KNA1 KNA13 ON APRE.Payer = KNA13.Customer)
        LEFT JOIN KNA1 KNA21 ON APRE.ShipTo = KNA21.Customer)
        LEFT JOIN KNA1 KNA22 ON APRE.ShipTo = KNA22.Customer)
        LEFT JOIN KNA1 KNA23 ON APRE.SHipTo = KNA23.Customer)
        LEFT JOIN KNA1 KNA31 ON APRE.SoldTo = KNA31.Customer)
        LEFT JOIN KNA1 KNA32 ON APRE.SoldTo = KNA32.Customer)
        LEFT JOIN KNA1 KNA33 ON APRE.SoldTo = KNA33.Customer
WHERE
        VBRK.[Billing TYPE] LIKE 'ZY1' AND
        VBRK.CANCEL IS NULL AND
        KONA.DESCRIPTION LIKE '* - #' OR
        KONA.DESCRIPTION LIKE '* - 20S - *' AND
        VBRK.Payer IN
        (
            '30165269',
            '30163572',
            '30162857',
            '30162621',
            '30166585',
            '30161260',
            '30161141',
            '30158583',
            '30165514'
        )
GROUP BY
        VBRK.Payer,
        VBRK.SoldTo,
        APRE.BillDoc,
        APRE.NET,
        APRE.Tax,
        APRE.Canceled,
        APRE.ShipTo,
        APRE.TOTAL,
        DTNO.BillDoc,
        DTNO.NET,
        DTNO.TAX,
        DTNO.ShipTo,
        DTNO.Canceled,
        DTNO.TOTAL,
        DTNI.BillDoc,
        DTNI.Amount,
        DTNI.Tax,
        DTNI.Canc,
        MACN.BillDoc,
        MACN.Net,
        MACN.Tax,
        MACN.Canceled,
        MACN.ShipTo,
        MACN.ToTAL,
        KNA11.[Name 1],
        KNA12.[Name 2],
        KNA13.[City],
        KNA21.[Name 1],
        KNA22.[Name 2],
        KNA23.[City],
        KNA31.[Name 1],
        KNA32.[Name 2],
        KNA33.[City]