C_RO_SAFTSalesInvoiceHeader

@AbapCatalog.sqlViewName:'CROSAFTSLSINVH'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey:true
@VDM.viewType: #CONSUMPTION
@EndUserText.label: 'Sales invoice header for RO SAFT'
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@AccessControl.authorizationCheck: #MANDATORY
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED

define view C_RO_SAFTSalesInvoiceHeader
  with parameters

    P_AlternativeGLAccountIsUsed : saft_ro_alt_gl_account_flag,
    P_UserSystemIdentifier       : sysid,
    P_VATRegistration            : stceg

  as select from           P_RO_SAFTSalInvHdr1(P_AlternativeGLAccountIsUsed: $parameters.P_AlternativeGLAccountIsUsed)        as SalInvHeader

    inner join             I_AccountingDocument                                                                               as Bkpf             on  Bkpf.AccountingDocument     = SalInvHeader.AccountingDocument
                                                                                                                                                  and Bkpf.CompanyCode            = SalInvHeader.CompanyCode
                                                                                                                                                  and Bkpf.FiscalYear             = SalInvHeader.FiscalYear
                                                                                                                                                  and Bkpf.PostingDate            = SalInvHeader.PostingDate
                                                                                                                                                  and Bkpf.AccountingDocumentType = SalInvHeader.AccountingDocumentType

    inner join             P_RO_SAFTOplAcctgDocItmExc(P_AlternativeGLAccountIsUsed: $parameters.P_AlternativeGLAccountIsUsed) as Bseg             on  Bseg.AccountingDocument     = SalInvHeader.AccountingDocument
                                                                                                                                                  and Bseg.CompanyCode            = SalInvHeader.CompanyCode
                                                                                                                                                  and Bseg.FiscalYear             = SalInvHeader.FiscalYear
                                                                                                                                                  and Bseg.AccountingDocumentItem = SalInvHeader.AccountingDocumentItem
                                                                                                                                                  and Bseg.PostingDate            = SalInvHeader.PostingDate
                                                                                                                                                  and Bseg.AccountingDocumentType = SalInvHeader.AccountingDocumentType

    left outer to one join I_Customer                                                                                         as Customer         on Customer.Customer = Bseg.Customer

  // Customer country

    left outer to one join I_Country                                                                                          as BsegStcegCountry on BsegStcegCountry.Country = left(
      Bseg.VATRegistration, 2
    )

  // Country specific table with Customer VAT number information

    left outer to one join P_RO_SAFTCustomerVATNumber                                                                         as TaxType          on TaxType.Customer = Bseg.Customer

    left outer to one join I_RO_SAFTInvoiceType                                                                               as InvoiceTypeA     on  Bkpf.AccountingDocumentType  = InvoiceTypeA.AccountingDocumentType
                                                                                                                                                  and InvoiceTypeA.TransactionCode = ''
                                                                                                                                                  and InvoiceTypeA.DebitCreditCode = ''
    left outer to one join I_RO_SAFTInvoiceType                                                                               as InvoiceTypeB     on  Bkpf.AccountingDocumentType  = InvoiceTypeB.AccountingDocumentType
                                                                                                                                                  and Bkpf.TransactionCode         = InvoiceTypeB.TransactionCode
                                                                                                                                                  and InvoiceTypeB.DebitCreditCode = ''
    left outer to one join I_RO_SAFTInvoiceType                                                                               as InvoiceTypeC     on  Bkpf.AccountingDocumentType  = InvoiceTypeC.AccountingDocumentType
                                                                                                                                                  and InvoiceTypeC.TransactionCode = ''
                                                                                                                                                  and Bseg.BsegDebitCreditCode     = InvoiceTypeC.DebitCreditCode
    left outer to one join I_RO_SAFTInvoiceType                                                                               as InvoiceTypeD     on  Bkpf.AccountingDocumentType = InvoiceTypeD.AccountingDocumentType
                                                                                                                                                  and Bkpf.TransactionCode        = InvoiceTypeD.TransactionCode
                                                                                                                                                  and Bseg.BsegDebitCreditCode    = InvoiceTypeD.DebitCreditCode

    left outer to one join I_PaymentTerms                                                                                     as PayTerms         on Bseg.PaymentTerms = PayTerms.PaymentTerms

    left outer to one join I_RO_SAFTOneTimeCustomer                                                                           as OTC              on  OTC.AccountingDocument     = Bseg.AccountingDocument
                                                                                                                                                  and OTC.CompanyCode            = Bseg.CompanyCode
                                                                                                                                                  and OTC.FiscalYear             = Bseg.FiscalYear
                                                                                                                                                  and OTC.AccountingDocumentItem = Bseg.AccountingDocumentItem

{
  key SalInvHeader.StatryRptCategory,
  key SalInvHeader.StatryRptgEntity,
  key SalInvHeader.StatryRptRunID,
  key SalInvHeader.SourceLedger,
  key SalInvHeader.CompanyCode,
  key SalInvHeader.FiscalYear,
  key SalInvHeader.AccountingDocument,
  key SalInvHeader.Ledger,
      SalInvHeader.AccountingDocumentItem,
      Bkpf.FiscalPeriod,
      SalInvHeader.PostingDate,
      Bkpf.DocumentDate                                                                                                                                                       as InvoiceDate,
      SalInvHeader.AccountingDocumentType,
      Bkpf.AccountingDocCreatedByUser,
      Bkpf.DocumentReferenceID,
      Customer.Customer,
      case when TaxType.BPTaxLongNumber <> '0' then concat_with_space( Customer.BPAddrStreetName, Customer._AddressDefaultRepresentation.HouseNumber, 1 )
        else coalesce(OTC.StreetName, concat_with_space( Customer.BPAddrStreetName, Customer._AddressDefaultRepresentation.HouseNumber, 1 ))
      end                                                                                                                                                                     as StreetName,
      case when TaxType.BPTaxLongNumber <> '0' then Customer.BPAddrCityName
        else coalesce(OTC.CityName, Customer.BPAddrCityName)
      end                                                                                                                                                                     as CityName,
      case when TaxType.BPTaxLongNumber <> '0' then Customer.PostalCode
        else coalesce(OTC.PostalCode, Customer.PostalCode)
      end                                                                                                                                                                     as PostalCode,
      case when TaxType.BPTaxLongNumber <> '0' then Customer.Country
        else coalesce(OTC.Country, Customer.Country)
      end                                                                                                                                                                     as Country,

      case
      when InvoiceTypeD.RO_SAFTInvoiceType is not null then
      InvoiceTypeD.RO_SAFTInvoiceType
      when InvoiceTypeC.RO_SAFTInvoiceType is not null then
      InvoiceTypeC.RO_SAFTInvoiceType
      when InvoiceTypeB.RO_SAFTInvoiceType is not null then
      InvoiceTypeB.RO_SAFTInvoiceType
      else
      InvoiceTypeA.RO_SAFTInvoiceType
      end                                                                                                                                                                     as RO_SAFTInvoiceType,
      'X'                                                                                                                                                                     as ItemIsReportingRelevant,

      // VAT number from BSEG has the highest priority

      // If not found in BSEG, grab it from country specific table

      // or set to company code's vat number (legal requirement) if taxtype = 7 or if it was not found in country specific table at all

      // (in country specific table VAT number is already expected to be formatted while when taken from BSEG we have to format it)

      case
        when Bseg.VATRegistration <> '' and BsegStcegCountry.IsEuropeanUnionMember = 'X' then
          case
            when BsegStcegCountry.Country = 'RO' then replace(Bseg.VATRegistration, 'RO', '00')
            else cast(concat('01', Bseg.VATRegistration) as bptaxnumxl)
          end
        when TaxType.BPTaxLongNumber = 'EXCLUDE' then cast($parameters.P_VATRegistration as bptaxnumxl)
        when TaxType.BPTaxLongNumber <> '0' then TaxType.BPTaxLongNumber
        else OTC.VATRegistration
      end                                                                                                                                                                     as CustomerTaxID5,

      Bseg.RO_SAFTStandardAccount,

      cast(concat( $parameters.P_UserSystemIdentifier, concat( concat( concat( '/', cast( $session.client as char3 )), '/' ) , SalInvHeader.AccountingDocument ) ) as text18) as RO_SAFTSystemID,

      '    '                                                                                                                                                                  as PaymentTerms, //do not use

      coalesce(PayTerms._Text[1: Language='4'].PaymentTermsName, PayTerms._Text[1: Language='E'].PaymentTermsName)                                                            as PaymentTermsName,

      case
       when InvoiceTypeD.RO_SAFTInvoiceType is not null then
          case InvoiceTypeD.RO_SAFTInvoiceType
           when '389' then cast('389' as text)
           else cast('0' as text)
         end
       when InvoiceTypeC.RO_SAFTInvoiceType is not null then
          case InvoiceTypeC.RO_SAFTInvoiceType
           when '389' then cast('389' as text)
           else cast('0' as text)
         end
       when InvoiceTypeB.RO_SAFTInvoiceType is not null then
         case InvoiceTypeB.RO_SAFTInvoiceType
           when '389' then cast('389' as text)
           else cast('0' as text)
         end
       else
         case InvoiceTypeA.RO_SAFTInvoiceType
           when '389' then cast('389' as text)
           else cast('0' as text)
         end
      end                                                                                                                                                                     as SelfBillingValueItemDesc,

      concat(
        concat( SalInvHeader.CompanyCode, SalInvHeader.FiscalYear ),
        concat( Bkpf.AccountingDocumentType, SalInvHeader.AccountingDocument )
      )                                                                                                                                                                       as BusinessTransactionTypeName,

      cast(
        case
          when Bkpf.AlternativeReferenceDocument <> '' then Bkpf.AlternativeReferenceDocument
          when Bkpf.DocumentReferenceID <> '' then Bkpf.DocumentReferenceID
          else SalInvHeader.AccountingDocument
        end
      as /ceecv/ro_source_document_id)                                                                                                                                        as RO_SAFTSourceDocument
}