I_PT_SAFTDeliveryOneTimeCust

@AbapCatalog.sqlViewName: 'IPTSAFTDELOTC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #REQUIRED
@EndUserText.label: 'SAF-T PT Billing One Time Custmer'
define view I_PT_SAFTDeliveryOneTimeCust
  as select distinct from I_SAFTDeliveryOneTimeCustomer as SD
    inner join            I_PT_DeliveryDgtlSgntr       as Sig     on  Sig.DeliveryDocument = SD.DeliveryDocument
                                                                  and Sig.CompanyCode     = SD.CompanyCode

    left outer join       P_PT_SAFTCUSTMAX             as CM      on  CM.CompanyCode             = SD.CompanyCode
                                                                  and CM.Customer                = SD.Customer
                                                                  and CM.CustomerVATRegistration = SD.CustomerVATRegistration

    left outer join       I_Address                    as Address on Address.AddressID = CM.AddressID


  association [0..1] to I_CustomerCompany as _CustCompany on  SD.Customer     = _CustCompany.Customer
                                                          and Sig.CompanyCode = _CustCompany.CompanyCode //update 1905


{
  key Sig.CompanyCode,
  key Sig.PortugueseDeliveryNumber as PortugueseDeliveryNumber,
      SD.DeliveryDocument,
      SD.DeliveryDate                                                as DocumentDate,
      Sig.PortugueseDigitalSignatureDate as CreationDate,
      Sig.PortugueseDigitalSignatureTime as CreationTime,

      case when SD.CustomerVATRegistration is initial or SD.CustomerVATRegistration is null
        then concat(SD.Customer, concat('/',  '999999990'))
        else concat(SD.Customer, concat('/',  SD.CustomerVATRegistration))
      end                                                                   as PortugueseCustomerWithVersion,

      SD.Customer,

      case when SD.CustomerVATRegistration is initial or SD.CustomerVATRegistration is null
        then
          '999999990'
        else
          SD.CustomerVATRegistration
      end                                                                   as CustomerVATRegistration,

      cast(
        case when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                  Address.FullName is not null and Address.FullName is not initial
             then Address.FullName
             when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                  CM.CompanyName is not initial and CM.CompanyName is not null
             then CM.CompanyName
             else 'Consumidor Final'
        end as bu_name1tx)                                                  as CompanyName,

      case when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                Address.StreetName is not null and Address.StreetName is not initial
           then Address.StreetName
           when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                CM.StreetName is not null and CM.StreetName is not initial
           then CM.StreetName
           else 'Desconhecido'
      end                                                                   as StreetName,

      case when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                Address.CityName is not null and Address.CityName is not initial
           then Address.CityName
           when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                CM.CityName is not null and CM.CityName is not initial
           then CM.CityName
           else 'Desconhecido'
      end                                                                   as CityName,

      case when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                Address.PostalCode is not null and Address.PostalCode is not initial
           then Address.PostalCode
           when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                CM.PostalCode is not null and CM.PostalCode is not initial
           then CM.PostalCode
           else 'Desconhecido'
      end                                                                   as PostalCode,

      case when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                Address.Country is not null and Address.Country is not initial
           then Address.Country
           when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                CM.Country is not null and CM.Country is not initial
           then CM.Country
           else 'Desconhecido'
      end                                                                   as Country,
      //CE1908 - Beg 

      case when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                Address._DefaultPhoneNumber.PhoneNumber is not null and Address._DefaultPhoneNumber.PhoneNumber is not initial
           then Address._DefaultPhoneNumber.PhoneNumber
           when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                CM.PhoneNumber is not null and CM.PhoneNumber is not initial
           then CM.PhoneNumber          
      end   as PhoneNumber,

      case when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                Address._DefaultFaxNumber.FaxNumber is not null and Address._DefaultFaxNumber.FaxNumber is not initial
           then Address._DefaultFaxNumber.FaxNumber
           when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                CM.FaxNumber is not null and CM.FaxNumber is not initial
           then CM.FaxNumber          
      end   as FaxNumber,

      case when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                Address._DefaultEmailAddress.EmailAddress is not null and Address._DefaultEmailAddress.EmailAddress is not initial
           then Address._DefaultEmailAddress.EmailAddress 
           when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                CM.EmailAddress is not null and CM.EmailAddress is not initial
           then CM.EmailAddress          
      end   as EmailAddress,

      case when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                Address._DefaultURLAddress.SearchURLAddress is not null and Address._DefaultURLAddress.SearchURLAddress is not initial
           then cast(lower(Address._DefaultURLAddress.SearchURLAddress) as ad_uri2)
           when SD.CustomerVATRegistration is not null and SD.CustomerVATRegistration is not initial and
                CM.WebsiteURL is not null and CM.WebsiteURL is not initial
           then CM.WebsiteURL         
      end   as WebsiteURL,     
      //CE1908 - End

           
      //Associations  

      _CustCompany //reconciliation account - update 1905

}
where
  SD.PartnerFunction = 'AG'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADDRESS",
"I_ADDRESSEMAILADDRESS",
"I_ADDRESSFAXNUMBER",
"I_ADDRESSHOMEPAGEURL",
"I_ADDRESSPHONENUMBER",
"I_PT_DELIVERYDGTLSGNTR",
"I_SAFTDELIVERYONETIMECUSTOMER",
"P_PT_SAFTCUSTMAX"
],
"ASSOCIATED":
[
"I_CUSTOMERCOMPANY"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/