I_PT_SAFTSlsInvcBillgCust

@Consumption.dbHints: [ 'USE_HEX_PLAN' ]
@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #MANDATORY
@VDM.viewType: #COMPOSITE
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@EndUserText.label: 'Customer from Billing Document'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
  serviceQuality: #D,
  sizeCategory: #XL,
  dataClass: #MIXED
}
define view entity I_PT_SAFTSlsInvcBillgCust
  as select from           I_PT_BillgDocDgtlSgntr        as Sig

    inner join             I_BillingDocument             as Doc         on  Sig.BillingDocument = Doc.BillingDocument --NRG
                                                                        and Sig.CompanyCode     = Doc.CompanyCode --NRG
    inner join             I_Customer                    as Cust        on  Cust.Customer         = Doc.PayerParty
                                                                        and Cust.IsOneTimeAccount = ' '

    inner join             I_CustomerCompany             as CustCompany on  Cust.Customer   = CustCompany.Customer
                                                                        and Doc.CompanyCode = CustCompany.CompanyCode
    inner join             I_PT_SAFTBillgDocCustAddrHist as Hist        on  Sig.BillingDocument = Hist.BillingDocument
                                                                        and Sig.CompanyCode     = Hist.CompanyCode
                                                                        and Cust.Customer       = Hist.Customer

    inner join             I_PT_SAFTIsAlternateAccount   as AltAcct     on AltAcct.CompanyCode = Sig.CompanyCode

    inner join             I_GLAccountInCompanyCode      as CompAcct    on  CompAcct.GLAccount   = CustCompany.ReconciliationAccount
                                                                        and CompAcct.CompanyCode = Doc.CompanyCode

    left outer to one join I_PT_SAFTWorkingDocType       as w           on  Sig.NumberRangeInterval = w.NumberRangeInterval
                                                                        and Sig.CompanyCode         = w.CompanyCode
                                                                        and Doc.FiscalYear          = w.FiscalYear

  association [0..1] to I_SAFTCustTaxRegnNmbr as _CustLand on  Doc.PayerParty             = _CustLand.CustomerNum
                                                           and Doc.VATRegistrationCountry = _CustLand.Country

{
  key     Sig.CompanyCode,
  key     Sig.PortugueseInvoiceNumber,
  key     Doc.BillingDocumentDate                                 as DocumentDate,
  key     cast(case when _CustLand.Country is not initial
          then
          case when Doc.VATRegistrationCountry is not initial then
            concat(Hist.PortugueseCustomerWithVersion, concat('/', Doc.VATRegistrationCountry ))
            else concat(Hist.PortugueseCustomerWithVersion, concat('/', Hist.Country)) end
          //   concat(Hist.PortugueseCustomerWithVersion, concat('/', _MultVat.CustomerCountry))

          else case when Hist.Country != Cust.Country
             then concat(Hist.PortugueseCustomerWithVersion,concat('/',Hist.Country))
             else Hist.PortugueseCustomerWithVersion
             end end  as abap.char(30))                           as BusinessPartnerCustomer,

          Doc.BillingDocument                                     as AccountingDocument,

          Doc.PayerParty                                          as Customer,


          cast( case when _CustLand.Country  is not initial
           then case when _CustLand.Country = 'AE'
              then _CustLand.CustomerVATRegistration
              else substring(_CustLand.CustomerVATRegistration,3,18) end
          else
           case when Cust.VATRegistration is not initial
           then substring(Cust.VATRegistration,3,18)
           else case when Cust.TaxNumber1 is not initial then Cust.TaxNumber1
           else case when Cust.TaxNumber2 is not initial then Cust.TaxNumber2
           else case when Cust.TaxNumber3 is not initial then Cust.TaxNumber3
           else case when Cust.TaxNumber4 is not initial then Cust.TaxNumber4
           else case when Cust.TaxNumber5 is not initial then Cust.TaxNumber5
           else case when Cust.TaxNumber6 is not initial then Cust.TaxNumber6
//           else '999999990'

           end end end end end end end
          end  as abap.char(20))                                  as CustomerVATRegistration,

          case when AltAcct.AlternativeGLAccountIsUsed is not initial
                then CompAcct.AlternativeGLAccount
               else CustCompany.ReconciliationAccount
          end                                                     as ReconciliationAccount,

          cast (Hist.CompanyName as bu_name1tx)                   as CompanyName,
          case when Hist.CustomerSupplierAddress is initial
            then 'Desconhecido'
            else Hist.CustomerSupplierAddress
          end                                                     as StreetName,

          case when Hist.CityName is initial
            then 'Desconhecido'
            else Hist.CityName
          end                                                     as CityName,

          case when Hist.PostalCode is initial
            then '0000-000'
            else Hist.PostalCode
          end                                                     as PostalCode,

          case when Hist.Country is initial
            then 'Desconhecido'
            else Hist.Country
          end                                                     as PT_SAFTCountryText,


          coalesce ( Hist.ContactPersonName, '' )                 as CompanyContactPersonFullName,
          coalesce ( Hist.PhoneNumber, '' )                       as PhoneNumber,
          cast(coalesce ( Hist.FaxNumber, '' ) as abap.char(31) ) as FaxNumber,
          coalesce ( Hist.EmailAddress, '' )                      as EmailAddress,
          coalesce ( Hist.SearchURLAddress, '' )                  as WebsiteURL,

          cast( case
                when Doc.SDDocumentCategory = 'U' or Doc.SDDocumentCategory = '3' or Doc.SDDocumentCategory = '4' or w.NumberRangeInterval = Sig.NumberRangeInterval
                  then 'X'
                  else ' '
                end  as boole_d)                                  as SalesDocumentIsWorkingDocument,
          Doc.PayerParty,
          Doc.SDDocumentCategory,
          Cust.IsOneTimeAccount,
          Doc._PayerParty
}

where
      Doc.CancelledBillingDocument = '' //Do not select cancel documents

  //  and Doc.BillingDocumentCategory  <> 'P' //Do not select DP Requests

  and Cust.IsOneTimeAccount        = ' '

union select from        I_PT_BillgDocDgtlSgntr        as Sig

  inner join             I_InvoiceList                 as Doc         on  Doc.InvoiceList = Sig.BillingDocument
                                                                      and Doc.CompanyCode = Sig.CompanyCode
  inner join             I_Customer                    as Cust        on  Cust.Customer         = Doc.PayerParty
                                                                      and Cust.IsOneTimeAccount = ' '

  inner join             I_CustomerCompany             as CustCompany on  Cust.Customer   = CustCompany.Customer
                                                                      and Doc.CompanyCode = Sig.CompanyCode
  inner join             I_PT_SAFTBillgDocCustAddrHist as Hist        on  Sig.BillingDocument = Hist.BillingDocument
                                                                      and Sig.CompanyCode     = Hist.CompanyCode
                                                                      and Cust.Customer       = Hist.Customer

  inner join             I_PT_SAFTIsAlternateAccount   as AltAcct     on AltAcct.CompanyCode = Sig.CompanyCode

  inner join             I_GLAccountInCompanyCode      as CompAcct    on  CompAcct.GLAccount   = CustCompany.ReconciliationAccount
                                                                      and CompAcct.CompanyCode = Doc.CompanyCode

  left outer to one join I_PT_SAFTWorkingDocType       as w           on  Sig.NumberRangeInterval = w.NumberRangeInterval
                                                                      and Sig.CompanyCode         = w.CompanyCode
                                                                      and Doc.FiscalYear          = w.FiscalYear

{
  key    Sig.CompanyCode,
  key    Sig.PortugueseInvoiceNumber,

  key    Doc.InvoiceListBillingDate              as DocumentDate, //VDM

  key    Hist.PortugueseCustomerWithVersion      as BusinessPartnerCustomer,

         Doc.AccountingDocument,
         Doc.PayerParty                          as Customer,
         case when Hist.CustomerVATRegistration is initial or Hist.CustomerVATRegistration is null
                 then '999999990'
                 else Hist.CustomerVATRegistration
         end                                     as CustomerVATRegistration,

         case when AltAcct.AlternativeGLAccountIsUsed is not initial
               then CompAcct.AlternativeGLAccount
              else CustCompany.ReconciliationAccount
         end                                     as ReconciliationAccount,

         cast (Hist.CompanyName as bu_name1tx)   as CompanyName,
         case when Hist.CustomerSupplierAddress is initial
           then 'Desconhecido'
           else Hist.CustomerSupplierAddress
         end                                     as StreetName,

         case when Hist.CityName is initial
           then 'Desconhecido'
           else Hist.CityName
         end                                     as CityName,

         case when Hist.PostalCode is initial
           then '0000-000'
           else Hist.PostalCode
         end                                     as PostalCode,

         case when Hist.Country is initial
           then 'Desconhecido'
           else Hist.Country
         end                                     as PT_SAFTCountryText,

         coalesce ( Hist.ContactPersonName, '' ) as CompanyContactPersonFullName,
         coalesce ( Hist.PhoneNumber, '' )       as PhoneNumber,
         coalesce ( Hist.FaxNumber, '' )         as FaxNumber,
         coalesce ( Hist.EmailAddress, '' )      as EmailAddress,
         coalesce ( Hist.SearchURLAddress, '' )  as WebsiteURL,

         case
               when Doc.SDDocumentCategory = 'U' or Doc.SDDocumentCategory = '3' or Doc.SDDocumentCategory = '4' or w.NumberRangeInterval = Sig.NumberRangeInterval
                 then 'X'
                 else ' '
               end                               as SalesDocumentIsWorkingDocument,
         Doc.PayerParty,
         Doc.SDDocumentCategory,
         Cust.IsOneTimeAccount,
         Doc._PayerParty
}
where
      Doc.CancelledInvoiceList = '' //Do not select cancel documents

  //  and Doc.BillingDocumentCategory  <> 'P' //Do not select DP Requests

  and Cust.IsOneTimeAccount    = ' '