DROP TABLE IF EXISTS vv_PartnerConnectionInfoAccount;
DROP VIEW IF EXISTS vv_PartnerConnectionInfoAccount;
CREATE view vv_PartnerConnectionInfoAccount
as
   select '01-Account' MainType,
          t2.Oid Oid,
          t2.CustomersFrom CustomersFrom,
          t1.Customer Customers,
          t1.PartnerType PartnerType,
          t2.DocumentNumber ConnectInfo,
          t2.CurrencyName CurrencyName,
          sum(t1.AmountHUF) BallanceHUF,
          sum(t1.AmountDEV) BallanceDEV,
          t3.ShortName Currency,
          t2.DatePayment DatePayment,
          t2.DateCreated DateCreated,
          t2.DateExecution DateExecution,
          t2.CurrencyRate CurrencyRate,
          t2.DocumentNumber DocumentNumber,
          max(t2.RegistryNumber) RegistryNumber
   from GLRows t1 join GLHeader t2 on
        t1.GLHeader=t2.Oid left join CurrencyName t3 on
        t2.CurrencyName=t3.Oid
   where t2.GCRecord is null and
         t2.GLDocumentType=1 and
         ifnull(t2.IsEditable,0)=0 and 
         t1.PartnerType in (0,1) and
         t1.GCRecord is null
   group by t2.Oid,t2.CustomersFrom,t1.Customer,t1.PartnerType,
            t2.DocumentNumber,t2.CurrencyName,t3.ShortName,t2.DateExecution,t2.DatePayment,
            t2.DateCreated,t2.DateExecution,t2.CurrencyRate
        
  union all         
   
   select '01-Account' MainType,
          t1.Oid Oid,
          t1.CustomersFrom CustomersFrom,
          t1.Customers Customers,
          0 PartnerType,
          t1.DocumentNumber ConnectInfo,
          t1.CurrencyName CurrencyName,
          t1.TotalBruttoHUF BallanceHUF,
          t1.TotalBruttoDEV BallanceDEV,
          t3.ShortName Currency,
          t1.DatePayment DatePayment,
          t1.DateCreated DateCreated,
          t1.DateExecution DateExecution,
          t1.CurrencyRate CurrencyRate,
          t1.DocumentNumber DocumentNumber,
          t1.DocumentNumber RegistryNumber
   from InvoiceHeader t1 left join CurrencyName t3 on
        t1.CurrencyName=t3.Oid
   where t1.GLAccounts is null and
         t1.IsEditable=0 and
         t1.GCRecord is null

   union all 
   
   select '01-Account' MainType,
          t1.Oid Oid,
          t1.CustomersFrom CustomersFrom,
          t1.Customers Customers,
          1 PartnerType,
          t1.DocumentNumber ConnectInfo,
          t1.F_CurrencyName CurrencyName,
          t1.F_AmountBruttoHUF BallanceHUF,
          t1.F_AmountBruttoDEV BallanceDEV,
          t3.ShortName Currency,
          t1.F_DatePayment DatePayment,
          t1.F_DateCreated DateCreated,
          t1.F_DateExecution DateExecution,
          t1.F_CurrencyRate CurrencyRate,
          t1.DocumentNumber DocumentNumber,
          t1.RegistryNumber RegistryNumber
   from RegistryHeader t1 left join RegistryType t2 on
        t1.RegistryType=t2.Oid left join CurrencyName t3 on
        t1.F_CurrencyName=t3.Oid
   where t1.GCRecord is null and
         t1.F_GLAccounts is null and
         t1.IsEditable=0 and
         t1.IsStorno=0 and
         t2.RegistryMainType=0 and
         t1.RegistryAcceptState=0