I_PT_SAFTDeliveryCustomer_2

@VDM.viewType: #COMPOSITE // #BASIC

@AccessControl.authorizationCheck:#MANDATORY
@Metadata.ignorePropagatedAnnotations:true
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.dataClass: #MIXED
@AccessControl.personalData.blocking: #REQUIRED
@EndUserText.label: 'SAFT for Normal Customer'

define view entity I_PT_SAFTDeliveryCustomer_2
  as select from           I_PT_SAFTCustPartnerFunction as SignCust 
    inner join             I_Customer                   as Cust                   on Cust.Customer = SignCust.Customer
    left outer to one join I_CustomerCompany            as CustomerCompany        on  SignCust.Customer    = CustomerCompany.Customer
                                                                                  and SignCust.CompanyCode = CustomerCompany.CompanyCode
    left outer to one join I_GLAccountInCompanyCode     as GLAccountInCompanyCode on  CustomerCompany.ReconciliationAccount = GLAccountInCompanyCode.GLAccount
                                                                                  and CustomerCompany.CompanyCode           = GLAccountInCompanyCode.CompanyCode
    left outer to one join I_PT_SAFTIsAlternateAccount  as Account                on Account.CompanyCode = SignCust.CompanyCode

  association [0..1] to I_PT_DelDocCustAddrHist_2 as _History  on  SignCust.Customer         = _History.Customer  
                                                              and SignCust.DeliveryDocument = _History.DeliveryDocument
  association [0..1] to I_SAFTCustTaxRegnNmbr    as _CustLand on  SignCust.Customer = _CustLand.CustomerNum
                                                              and SignCust.Country  = _CustLand.Country
{
  key  SignCust.PortugueseDeliveryNumber                                                         as PortugueseDeliveryNumber,
  key  SignCust.DeliveryDocument                                                                 as DeliveryDocument,
  key  SignCust.CompanyCode                                                                      as CompanyCode,
       SignCust.DeliveryDate                                                                     as DocumentDate,
       SignCust.PortugueseDigitalSignatureDate                                                   as CreationDate,
       SignCust.PortugueseDigitalSignatureTime                                                   as CreationTime,
       SignCust.Customer,
       case
       when _History.BusinessPartnerCustomer is not initial
       then
         case
          when _CustLand.CustomerNum is not initial
             then  concat(_History.BusinessPartnerCustomer,concat('_',SignCust.Country))
             else _History.BusinessPartnerCustomer
          end
       else
           SignCust.Customer
       end                                                                                       as BusinessPartnerCustomer, 

       //       _History.PortugueseCustomerWithVersion   as PortugueseCustomerWithVersion,


       case
       when _History.BusinessPartnerCustomer is not initial
           then _History.CustomerVATRegistration
       else
          case
            when SignCust.VATRegistration is not initial
              then substring(SignCust.VATRegistration,3,18)
            when SignCust.TaxNumber1 is not initial
              then SignCust.TaxNumber1
            when SignCust.IsOneTimeAccount is initial
              then '999999990'
           end
       end                                                                                       as CustomerVATRegistration,

       case
        when Account.AlternativeGLAccountIsUsed  is not initial
            then GLAccountInCompanyCode.AlternativeGLAccount
        else CustomerCompany.ReconciliationAccount
       end                                                                                       as ReconciliationAccount,

       case
         when _History.BusinessPartnerCustomer is not initial
            then
                cast (_History.CompanyName as bu_name1tx)
            else
               cast (SignCust.CompanyName as  bu_name1tx)
       end                                                                                       as CompanyName,

       case
        when _History.BusinessPartnerCustomer is not initial
            then
            coalesce(_History.PT_CustomerSupplierAddressText, 'Desconhecido')             
            else
            coalesce(SignCust.StreetName, 'Desconhecido')
            end                                                                                  as StreetName,

       case
        when _History.BusinessPartnerCustomer is not initial
            then
            coalesce(_History.CityName, 'Desconhecido')
            else
            coalesce(SignCust.CityName, 'Desconhecido')
       end                                                                                       as CityName,

       case
        when _History.BusinessPartnerCustomer is not initial
            then
            coalesce(_History.PostalCode, '0000-000')
            else
            coalesce(SignCust.PostalCode, '0000-000')
        end                                                                                      as PostalCode,

       cast(case
        when _History.BusinessPartnerCustomer is not initial
            then
              _History.Country
            else
              SignCust.Country
       end as land1)                                                                              as Country,

       Cust._AddressRepresentation.HouseNumber                                                   as HouseNumber,
       Cust._AddressRepresentation.Region                                                        as Region,

       case
        when Cust._AddressRepresentation.StreetName is not initial
        or   Cust._AddressRepresentation.HouseNumber is not initial
            then
            concat ( Cust._AddressRepresentation.StreetName, Cust._AddressRepresentation.HouseNumber)
            else
            'Desconhecido'
        end                                                                      as PT_ShipToPartyAddressAsText, 

       case
       when _History.BusinessPartnerCustomer is not initial
       then
       coalesce ( _History.ContactPersonName, '' )
       else
       coalesce ( SignCust.CustomerFullName, '' )
       end                                                                                       as CompanyContactPersonFullName,

       case
       when _History.BusinessPartnerCustomer is not initial
       then
        coalesce ( _History.PhoneNumber, '' )
       else
        coalesce ( SignCust.PhoneNumber, '' )
       end                                                                                       as PhoneNumber,

       case
       when _History.BusinessPartnerCustomer is not initial
       then
        coalesce ( _History.FaxNumber, '' )
       else
        coalesce ( SignCust.FaxNumber, '' )
       end                                                                                       as FaxNumber,

       case
       when _History.BusinessPartnerCustomer is not initial
       then
        coalesce ( _History.EmailAddress, '' )
       else
        coalesce ( SignCust.EmailAddress, '' )
       end                                                                                       as EmailAddress,

       case
       when _History.BusinessPartnerCustomer is not initial
       then
        coalesce ( _History.SearchURLAddress, '' )
       else
        coalesce ( SignCust.WebsiteURL, '' )
       end                                                                                       as WebsiteURL,

       SignCust._DelDoc
}
where
 SignCust.PortugueseDigitalSignature <> ''
 and SignCust.IsOneTimeAccount <> 'X'