C_RO_SAFTPurInvoiceHeader

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

define view C_RO_SAFTPurInvoiceHeader
  with parameters

    P_AlternativeGLAccountIsUsed : saft_ro_alt_gl_account_flag,
    P_UserSystemIdentifier       : sysid,
    P_VATRegistration            : stceg

  as select from           P_RO_SAFTPurInvHdr1(P_AlternativeGLAccountIsUsed: $parameters.P_AlternativeGLAccountIsUsed)        as PurInvHeader

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

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

    left outer to one join I_Supplier                                                                                         as Supplier         on Supplier.Supplier = Bseg.Supplier

  // Supplier country

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

  // Country specific table with supplier VAT number information

    left outer to one join P_RO_SAFTSupplierVATNumber                                                                         as TaxType          on TaxType.Supplier = Bseg.Supplier

    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_SAFTOneTimeSupplier                                                                           as OTS              on  OTS.AccountingDocument     = Bseg.AccountingDocument
                                                                                                                                                  and OTS.CompanyCode            = Bseg.CompanyCode
                                                                                                                                                  and OTS.FiscalYear             = Bseg.FiscalYear
                                                                                                                                                  and OTS.AccountingDocumentItem = Bseg.AccountingDocumentItem

{
  key PurInvHeader.StatryRptCategory,
  key PurInvHeader.StatryRptgEntity,
  key PurInvHeader.StatryRptRunID,
  key PurInvHeader.SourceLedger,
  key PurInvHeader.Ledger,
  key PurInvHeader.CompanyCode,
  key PurInvHeader.FiscalYear,
  key PurInvHeader.AccountingDocument,
      PurInvHeader.AccountingDocumentItem,
      Bkpf.FiscalPeriod,
      PurInvHeader.PostingDate,
      Bkpf.DocumentDate                                                                                                                                                       as InvoiceDate,
      PurInvHeader.AccountingDocumentType,
      Bkpf.AccountingDocCreatedByUser,
      Bkpf.DocumentReferenceID,
      Supplier.Supplier,
      case when TaxType.BPTaxLongNumber <> '0' then concat_with_space( Supplier.BPAddrStreetName, Supplier._AddressDefaultRepresentation.HouseNumber, 1 )
        else coalesce(OTS.StreetName, concat_with_space( Supplier.BPAddrStreetName, Supplier._AddressDefaultRepresentation.HouseNumber, 1 ))
      end                                                                                                                                                                     as StreetName,
      case when TaxType.BPTaxLongNumber <> '0' then Supplier.BPAddrCityName
        else coalesce(OTS.CityName, Supplier.BPAddrCityName)
      end                                                                                                                                                                     as CityName,
      case when TaxType.BPTaxLongNumber <> '0' then Supplier.PostalCode
        else coalesce(OTS.PostalCode, Supplier.PostalCode)
      end                                                                                                                                                                     as PostalCode,
      case when TaxType.BPTaxLongNumber <> '0' then Supplier.Country
        else coalesce(OTS.Country, Supplier.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 OTS.VATRegistration
      end                                                                                                                                                                     as SupplierTaxID5,

      Bseg.RO_SAFTStandardAccount,

      cast(concat( $parameters.P_UserSystemIdentifier, concat( concat( concat( '/', cast( $session.client as char3 )), '/' ) , PurInvHeader.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( PurInvHeader.CompanyCode, PurInvHeader.FiscalYear ),
        concat( Bkpf.AccountingDocumentType, PurInvHeader.AccountingDocument )
      )                                                                                                                                                                       as BusinessTransactionTypeName,

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