Comptable requete

SELECT *
from (select 'FACTURE'                                                   as Type_facture,
             MAX(to_char(l.created_at, 'DD/MM/YYYY'))                    as date_facture,
             MAX(li.reference)                                           as Ref_facture,
             AVG(l.seller_id)                                            as Num_seller,
             AVG(l.buyer_id)                                             as Num_buyer,
             (SELECT SUM(lilHT.quantity * lilHT.unit_price)
              FROM lorder_invoice as liHT
                       LEFT JOIN lorder_invoice_line as lilHT ON liHT.id = lilHT.lorder_invoice_id
              WHERE liHT.id = li.id
              GROUP BY liHT.reference)
                                                                         as tot_HT,
             (SELECT SUM(lilTVA20.quantity * lilTVA20.unit_price)
              FROM lorder_invoice as liTVA20
                       LEFT JOIN lorder_invoice_line as lilTVA20 ON liTVA20.id = lilTVA20.lorder_invoice_id
              WHERE lilTVA20.vat_rate = '20.00'
                and liTVA20.id = li.id
              GROUP BY liTVA20.reference)
                                                                         as TVA20,
             (SELECT SUM(lilTVA210.quantity * lilTVA210.unit_price)
              FROM lorder_invoice as liTVA210
                       LEFT JOIN lorder_invoice_line as lilTVA210 ON liTVA210.id = lilTVA210.lorder_invoice_id
              WHERE lilTVA210.vat_rate = '2.10'
                and liTVA210.id = li.id
              GROUP BY liTVA210.reference)
                                                                         as TVA210,
             (SELECT SUM(lilTVA55.quantity * lilTVA55.unit_price)
              FROM lorder_invoice as liTVA55
                       LEFT JOIN lorder_invoice_line as lilTVA55 ON liTVA55.id = lilTVA55.lorder_invoice_id
              WHERE lilTVA55.vat_rate = '5.50'
                and liTVA55.id = li.id
              GROUP BY liTVA55.reference)
                                                                         as TVA55,
             (SELECT SUM(lilTVA10.quantity * lilTVA10.unit_price)
              FROM lorder_invoice as liTVA10
                       LEFT JOIN lorder_invoice_line as lilTVA10 ON liTVA10.id = lilTVA10.lorder_invoice_id
              WHERE lilTVA10.vat_rate = '10.00'
                and liTVA10.id = li.id
              GROUP BY liTVA10.reference)
                                                                         as TVA10,
             AVG(li.total_ttc)                                           as Tot_ttc,
             AVG(l.shipping_fees)                                        as FDP,
             MAX(ROUND(commission_invoice_buyer.total_ttc / '1.2', '2')) as com_buyer,
             MAX(ROUND(cis.total_ttc / '1.2', '2'))                      as com_seller,
             MAX(commission_invoice_buyer.reference)                     as Num_facture_buyer,
             MAX(cis.reference)                                          as Num_facture_seller,
             '-'                                                         as Reference_vente_remb,
             MAX(l.reference)                                            as Reference_cmde,
             max(subPayIn.date_Payin)                                    as Date_Debit,
             max(subTransfer.date_transfert)                             as Date_Transfert,
             max(subPayOUT.date_Payout)                                  as Date_Credit,
             max(ls.machine_name)                                        as Statut_commande
      from lorder l
               left join (select right(mt.tag, 15)             as refcmd,
                                 to_timestamp(mt.creationdate) as date_Payin
                          from mangopay_transaction mt
                                   left join lorder l on right(mt.tag, 15) = l.reference
                          where mt.status in ('SUCCEEDED', 'CREATED')
                            and mt.type = 'PAYIN'
      ) subPayIn on l.reference = subPayIn.refcmd
               left join (select right(mt.tag, 15)             as refcmd,
                                 to_timestamp(mt.creationdate) as date_transfert
                          from mangopay_transaction mt
                                   right join lorder l on l.reference = right(mt.tag, 15)
                          where substr(mt.tag, 1, 5) = 'order'
                            and mt.nature = 'REGULAR'
                            and mt.status = 'SUCCEEDED'
                            and mt.type = 'TRANSFER'
      ) subTransfer on l.reference = subTransfer.refcmd
               left join(select right(mt.tag, 15)             as refcmd,
                                to_timestamp(mt.creationdate) as date_Payout
                         from mangopay_transaction mt
                                  right join lorder l on l.reference = right(mt.tag, 15)
                         where mt.nature = 'REGULAR'
                           and mt.status = 'SUCCEEDED'
                           and mt.type = 'PAYOUT'
                           and substr(mt.tag, 1, 5) = 'order'
      ) subPayOUT on l.reference = subPayOUT.refcmd

          --left join client c on l.seller_id = c.id
               left join lorder_invoice li on l.id = li.order_id
          -- LEFT JOIN lorder_invoice_line liline ON li.id = liline.lorder_invoice_id
          -- LEFT JOIN shopping_cart_line scl ON l.id = scl.order_id
               LEFT JOIN commission_invoice_buyer ON l.id = commission_invoice_buyer.order_id
               LEFT JOIN commission_invoice_seller cis ON l.id = cis.order_id
               Left join lorder_status ls ON ls.id = l.status_id
      where (l.created_at between '2019-01-01' and '2019-12-31')
      group by li.id
      UNION
      SELECT 'AVOIR'                                    as Type_facture,
             MAX(to_char(cnr.created_at, 'DD/MM/YYYY')) as date_facture,
             MAX(cnr.reference_bill)                    as Ref_facture,
             AVG(l.seller_id)                           as Num_seller,
             AVG(l.buyer_id)                            as Num_buyer,
             (SELECT SUM(lrcHT.quantity * lrcHT.discounted_price * (-1))
              FROM lorder_invoice as liCreditHT
                       LEFT JOIN lorder as orderRefundHT ON liCreditHT.order_id = orderRefundHT.id
                       LEFT JOIN lorder_refund as lrHT ON orderRefundHT.id = lrHT.order_id
                       LEFT JOIN lorder_refund_content as lrcHT ON lrHT.id = lrcHT.lorder_refund_id
              WHERE liCreditHT.id = li.id
              GROUP BY liCreditHT.reference)            as tot_HT,
             (SELECT SUM(lrc20CR.quantity * lrc20CR.discounted_price * (-1))
              FROM lorder_invoice as liTVA20CRED
                       LEFT JOIN lorder as orderTVA20CRED ON liTVA20CRED.order_id = orderTVA20CRED.id
                       LEFT JOIN lorder_refund as lr20CR ON orderTVA20CRED.id = lr20CR.order_id
                       LEFT JOIN lorder_refund_content as lrc20CR ON lr20CR.id = lrc20CR.lorder_refund_id
              WHERE lrc20CR.vat_rate = '20.00'
                and liTVA20CRED.id = li.id
              GROUP BY liTVA20CRED.reference)
                                                        as TVA20,
             (SELECT SUM(lrc210CR.quantity * lrc210CR.discounted_price * (-1))
              FROM lorder_invoice as liTVA210CRED
                       LEFT JOIN lorder as orderTVA210CRED ON liTVA210CRED.order_id = orderTVA210CRED.id
                       LEFT JOIN lorder_refund as lr210CR ON orderTVA210CRED.id = lr210CR.order_id
                       LEFT JOIN lorder_refund_content as lrc210CR ON lr210CR.id = lrc210CR.lorder_refund_id
              WHERE lrc210CR.vat_rate = '2.10'
                and liTVA210CRED.id = li.id
              GROUP BY liTVA210CRED.reference)
                                                        as TVA210,
             (SELECT SUM(lrc55CR.quantity * lrc55CR.discounted_price * (-1))
              FROM lorder_invoice as liTVA55CRED
                       LEFT JOIN lorder as orderTVA55CRED ON liTVA55CRED.order_id = orderTVA55CRED.id
                       LEFT JOIN lorder_refund as lr55CR ON orderTVA55CRED.id = lr55CR.order_id
                       LEFT JOIN lorder_refund_content as lrc55CR ON lr55CR.id = lrc55CR.lorder_refund_id
              WHERE lrc55CR.vat_rate = '5.50'
                and liTVA55CRED.id = li.id
              GROUP BY liTVA55CRED.reference)
                                                        as TVA55,
             (SELECT SUM(lrc10CR.quantity * lrc10CR.discounted_price * (-1))
              FROM lorder_invoice as liTVA10CRED
                       LEFT JOIN lorder as orderTVA10CRED ON liTVA10CRED.order_id = orderTVA10CRED.id
                       LEFT JOIN lorder_refund as lr10CR ON orderTVA10CRED.id = lr10CR.order_id
                       LEFT JOIN lorder_refund_content as lrc10CR ON lr10CR.id = lrc10CR.lorder_refund_id
              WHERE lrc10CR.vat_rate = '10'
                and liTVA10CRED.id = li.id
              GROUP BY liTVA10CRED.reference)
                                                        as TVA10,
             (SELECT ROUND(SUM(lrcTTC.quantity * lrcTTC.discounted_price * ('1' + (lrcTTC.vat_rate / '100'))), '2') *
                     (-1)
              FROM lorder_invoice as liCredit
                       LEFT JOIN lorder as orderRefund ON liCredit.order_id = orderRefund.id
                       LEFT JOIN lorder_refund as lrTTC ON orderRefund.id = lrTTC.order_id
                       LEFT JOIN lorder_refund_content as lrcTTC ON lrTTC.id = lrcTTC.lorder_refund_id
              WHERE liCredit.id = li.id
              GROUP BY liCredit.reference)              as Tot_ttc,
             AVG(cn.shipping_fees) * (-1)               as FDP,
             (SELECT ROUND(SUM(lrComBuy.refund_commissions_amount) / '1.2', '2') * (-1)
              FROM lorder_invoice as liComBuy
                       LEFT JOIN lorder as orderComBuy ON liComBuy.order_id = orderComBuy.id
                       LEFT JOIN lorder_refund as lrComBuy ON orderComBuy.id = lrComBuy.order_id
              WHERE liComBuy.id = li.id
              GROUP BY orderComBuy.id)                  as com_buyer,
             (SELECT ROUND(SUM(lrComSel.refund_commissions_amount) / '1.2', '2') * (-1)
              FROM lorder_invoice as liComSel
                       LEFT JOIN lorder as orderComSel ON liComSel.order_id = orderComSel.id
                       LEFT JOIN lorder_refund as lrComSel ON orderComSel.id = lrComSel.order_id
              WHERE liComSel.id = li.id
              GROUP BY orderComSel.id)                  as com_seller,
             MAX(commission_invoice_buyer.reference)    as Num_facture_buyer,
             MAX(cis.reference)                         as Num_facture_seller,
             MAX(li.reference)                          as Reference_vente_remb,
             MAX(l.reference)                           as Reference_cmde,
             to_timestamp(0)                            as date_debit,
             to_timestamp(0)                            as date_transfer,
             max(subPayOUT.date_Payout)                 as date_Credit,
             max(ls.machine_name)                       as Statut_commande
      FROM lorder l
               left join (select right(mt.tag, 15)             as refcmd,
                                 to_timestamp(mt.creationdate) as date_Payout
                          from mangopay_transaction mt
                                   right join lorder l on l.reference = right(mt.tag, 15)
                          where mt.nature = 'REFUND'
                            and mt.status = 'SUCCEEDED'
                            and mt.type = 'PAYOUT') subPayOUT on l.reference = subPayOUT.refcmd
               LEFT JOIN lorder_invoice as li ON l.id = li.order_id
          --LEFT JOIN lorder_invoice_line as liline ON li.id = liline.lorder_invoice_id
          --LEFT JOIN shopping_cart_line as scl ON lorder.id = scl.order_id
               LEFT JOIN commission_invoice_buyer ON l.id = commission_invoice_buyer.order_id
               LEFT JOIN commission_invoice_seller as cis ON l.id = cis.order_id
               LEFT JOIN credit_note cn ON l.id = cn.order_id
               LEFT JOIN credit_note_reference cnr ON cn.credit_note_reference_id = cnr.id
          --LEFT JOIN lorder_refund ON lorder.id = lorder_refund.order_id
          --LEFT JOIN lorder_refund_content ON lorder_refund.id = lorder_refund_content.lorder_refund_id
               Left join lorder_status as ls ON ls.id = l.status_id
      WHERE cn.credit_type = 'PARTIAL'
        and (l.created_at between '2019-01-01' and '2019-12-31')
      GROUP BY li.id
      UNION
      SELECT 'AVOIR'                                                            as Type_facture,
             MAX(to_char(cnr.created_at, 'DD/MM/YYYY'))                         as date_facture,
             MAX(cnr.reference_bill)                                            as Ref_facture,
             AVG(l.seller_id)                                                   as Num_seller,
             AVG(l.buyer_id)                                                    as Num_buyer,
             (SELECT SUM(lilHT.quantity * lilHT.unit_price * (-1))
              FROM lorder_invoice as liHT
                       LEFT JOIN lorder_invoice_line as lilHT ON liHT.id = lilHT.lorder_invoice_id
              WHERE liHT.id = li.id
              GROUP BY liHT.reference)
                                                                                as tot_HT,
             (SELECT SUM(lilTVA20.quantity * lilTVA20.unit_price * (-1))
              FROM lorder_invoice as liTVA20
                       LEFT JOIN lorder_invoice_line as lilTVA20 ON liTVA20.id = lilTVA20.lorder_invoice_id
              WHERE lilTVA20.vat_rate = '20.00'
                and liTVA20.id = li.id
              GROUP BY liTVA20.reference)
                                                                                as TVA20,
             (SELECT SUM(lilTVA210.quantity * lilTVA210.unit_price * (-1))
              FROM lorder_invoice as liTVA210
                       LEFT JOIN lorder_invoice_line as lilTVA210 ON liTVA210.id = lilTVA210.lorder_invoice_id
              WHERE lilTVA210.vat_rate = '2.10'
                and liTVA210.id = li.id
              GROUP BY liTVA210.reference)
                                                                                as TVA210,
             (SELECT SUM(lilTVA55.quantity * lilTVA55.unit_price * (-1))
              FROM lorder_invoice as liTVA55
                       LEFT JOIN lorder_invoice_line as lilTVA55 ON liTVA55.id = lilTVA55.lorder_invoice_id
              WHERE lilTVA55.vat_rate = '5.50'
                and liTVA55.id = li.id
              GROUP BY liTVA55.reference)
                                                                                as TVA55,
             (SELECT SUM(lilTVA10.quantity * lilTVA10.unit_price * (-1))
              FROM lorder_invoice as liTVA10
                       LEFT JOIN lorder_invoice_line as lilTVA10 ON liTVA10.id = lilTVA10.lorder_invoice_id
              WHERE lilTVA10.vat_rate = '10.00'
                and liTVA10.id = li.id
              GROUP BY liTVA10.reference)
                                                                                as TVA10,
             AVG(li.total_ttc) * (-1)                                           as Tot_ttc,
             AVG(l.shipping_fees) * (-1)                                        as FDP,
             AVG(ROUND(commission_invoice_buyer.total_ttc / '1.2', '2')) * (-1) as com_buyer,
             AVG(ROUND(cis.total_ttc / '1.2', '2')) * (-1)                      as com_seller,
             MAX(commission_invoice_buyer.reference)                            as Num_facture_buyer,
             MAX(cis.reference)                                                 as Num_facture_seller,
             '-'                                                                as Reference_vente_remb,
             MAX(l.reference)                                                   as Reference_cmde,
             to_timestamp(0)                                                    as date_debit,
             to_timestamp(0)                                                    as date_transfer,
             max(subPayOUT.date_Payout)                                         as date_Credit,

             max(ls.machine_name)                                               as Statut_commande
      FROM lorder l
               left join (select right(mt.tag, 15)             as refcmd,
                                 to_timestamp(mt.creationdate) as date_Payout
                          from mangopay_transaction mt
                                   right join lorder l on l.reference = right(mt.tag, 15)

                          where mt.nature = 'REFUND'
                            and mt.status = 'SUCCEEDED'
                            and mt.type = 'PAYOUT') subPayOUT on l.reference = subPayOUT.refcmd
               LEFT JOIN lorder_invoice as li ON li.order_id = l.id
          --LEFT JOIN lorder_invoice_line as liline ON li.id = liline.lorder_invoice_id
          --LEFT JOIN shopping_cart_line as scl ON lorder.id = scl.order_id
               LEFT JOIN commission_invoice_buyer ON l.id = commission_invoice_buyer.order_id
               LEFT JOIN commission_invoice_seller as cis ON l.id = cis.order_id
               Left join lorder_status as ls ON ls.id = l.status_id
               LEFT JOIN credit_note cn ON l.id = cn.order_id
               LEFT JOIN credit_note_reference cnr ON cn.credit_note_reference_id = cnr.id
      WHERE cn.credit_type = 'TOTAL'
        and (l.created_at between '2019-01-01' and '2019-12-31')
      GROUP BY li.id) as sub
WHERE Ref_facture NOTNULL
ORDER BY sub.Reference_cmde ASC;