I_PT_SAFTPaymentHeader

@AbapCatalog.sqlViewName: 'IPTSAFTPAYHEADER'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass:  #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'SAF-T PT Payment Header'

define view I_PT_SAFTPaymentHeader
  as select distinct from I_SAFTAccountingHeader        as a

    inner join            I_ClrgInfoRelationForAcctgDoc as d                 on  d.ClearingInfoCompanyCode    =  a.CompanyCode
                                                                             and d.ClearingAccountingDocument =  a.AccountingDocument
                                                                             and d.ClearingFiscalYear         =  a.FiscalYear
                                                                             and d.ClearingInformationIndex   =  '000001'
                                                                             and d.AccountType                =  'D' //only customers accounts

                                                                             and d.SpecialGLCode              <> 'F'

    inner join            I_AccountingDocumentType      as b                 on b.AccountingDocumentType = a.AccountingDocumentType

    inner join            I_PT_DgtlSgntrNmbrRngeAcctg   as NrRange           on  NrRange.CompanyCode                 =  a.CompanyCode
                                                                             and NrRange.ToFiscalYear                >= a.FiscalYear
                                                                             and NrRange.NumberRangeInterval         =  b.AccountingDocumentNumberRange
                                                                             and NrRange.PT_DigitalSignatureFromDate <= a.PostingDate

    left outer join       I_PT_SAFTDownPaymentFinance   as ClearingToExclude on  ClearingToExclude.CompanyCode    =  a.CompanyCode
                                                                             and ClearingToExclude.FromFiscalYear <= a.FiscalYear
                                                                             and ClearingToExclude.ToFiscalYear   >= a.FiscalYear
                                                                             and ClearingToExclude.DocumentType   =  a.AccountingDocumentType
  //and ClearingToExclude.SpecialGLCode = a.SpecialGLCode


    left outer join       I_PT_PaytRcptSersDocNumber    as atcud             on  a.CompanyCode        = atcud.CompanyCode
                                                                             and a.AccountingDocument = atcud.AccountingDocument
                                                                             and a.FiscalYear         = atcud.FiscalYear

  association [0..1] to I_PT_SAFTPaymentCustomer      as _Customers      on  _Customers.CompanyCode        = a.CompanyCode
                                                                         and _Customers.AccountingDocument = a.AccountingDocument
                                                                         and _Customers.FiscalYear         = a.FiscalYear

  association [0..1] to I_PT_SAFTPaymentOneTimeCust   as _OneTimeAccount on  _OneTimeAccount.CompanyCode        = a.CompanyCode
                                                                         and _OneTimeAccount.AccountingDocument = a.AccountingDocument
                                                                         and _OneTimeAccount.FiscalYear         = a.FiscalYear

  association [0..1] to P_PT_SAFTTAXLAND              as _MultVat        on  _MultVat.Customer = a.Customer


  association [1..1] to I_PT_SAFTPaymentTotalAmtCheck as _DocTotals      on  $projection.ClearingAccountingDocument = _DocTotals.ClearingAccountingDocument
                                                                         and $projection.FiscalYear                 = _DocTotals.ClearingFiscalYear
                                                                         and $projection.CompanyCode                = _DocTotals.CompanyCode

{

  key a.CompanyCode,
  key a.AccountingDocument                                                                                                    as ClearingAccountingDocument,
  key a.FiscalYear,

      //MultiVAT

      case
        when _MultVat.CustomerCountry is not initial
            and a.DelivOfGoodsDestCountry is not initial

          then  a.DelivOfGoodsDestCountry
          else _MultVat.CustomerCountry

        end                                                                                                                   as CustomerCountry,

      a.Customer,

      //only 1 per doc

      a.PaymentMethod,

      case
        when a.ReverseDocument is not initial and a.IsReversed <> ''
         then 'A'
        else 'N'
      end                                                                                                                     as DocumentBillingStatus,

      case
        when a.ReverseDocument is not initial and a.IsReversed <> ''
         then  _ReversalDocument.AccountingDocCreatedByUser
        else a.CreatedByUser
      end                                                                                                                     as LastChangedByUser, ///SOURCEID,



      case
        when a.ReverseDocument is not initial and a.IsReversed <> ''
         then concat(substring(a.AccountingDocumentCreationDate,1,4),
               concat('-',
                 concat(substring(_ReversalDocument.AccountingDocumentCreationDate,5,2),
                   concat('-',
                     concat(substring(_ReversalDocument.AccountingDocumentCreationDate,7,2),
                       concat('T',
                         concat(substring(_ReversalDocument.CreationTime,1,2),
                           concat(':',
                             concat(substring(_ReversalDocument.CreationTime,3,2),
                               concat(':', substring(_ReversalDocument.CreationTime,5,2)))))))))))

        else concat(substring(a.AccountingDocumentCreationDate,1,4),
               concat('-',
                 concat(substring(a.AccountingDocumentCreationDate,5,2),
                   concat('-',
                     concat(substring(a.AccountingDocumentCreationDate,7,2),
                       concat('T',
                         concat(substring(a.CreationTime,1,2),
                           concat(':',
                             concat(substring(a.CreationTime,3,2),
                               concat(':', substring(a.CreationTime,5,2)))))))))))
      end                                                                                                                     as PaymentStatusDate,


      a.ReverseDocument,
      a.IsReversed,
      a.ReverseDocumentFiscalYear,


      a.AccountingDocumentCreationDate                                                                                        as AccountingDocumentCreationDate,
      a.CreationTime                                                                                                          as CreationTime,
      a.ReversalReason                                                                                                        as ReversalReason,


      a.AccountingDocumentCategory,

      a.FiscalPeriod, //Period


      a.DocumentDate,

      a.PostingDate,

      a.AccountingDocumentType,

      @Semantics.currencyCode: true
      a.CompanyCodeCurrency                                                                                                   as CompanyCodeCurrency,

      a.CreatedByUser, //SOURCEID 4.1.4.10,


      @Semantics.currencyCode: true
      case when a.TransactionCurrency <> a._CompanyCode.Currency and a.TransactionCurrency is not initial
      then a.TransactionCurrency
      else cast('' as waers)
      end                                                                                                                     as TransactionCurrency,

      case when a.TransactionCurrency <> a._CompanyCode.Currency and a.TransactionCurrency is not initial
        then a.ExchangeRate
        else 0
      end                                                                                                                     as ExchangeRate,

      concat(
        concat(
          concat_with_space(a.AccountingDocumentType, NrRange.PortugueseDgtlSignatureSeries, 1), '/'),  a.AccountingDocument) as PaymentReference,

      concat_with_space(concat(substring(a.DocumentDate,1,4),
        concat('-',
          concat(substring(a.DocumentDate,5,2),
            concat('-', substring(a.DocumentDate,7,2))))),
              concat_with_space(a.AccountingDocumentType, a.AccountingDocument, 1), 1)                                        as DocumentTransaction,

      case
        when ClearingToExclude.DownPaymentType = '2'
          then 'X'
        else
         ' '
      end                                                                                                                     as SelectedValueIsExcluded,

      a.IsOneTimeAccount,
      a.DelivOfGoodsDestCountry,

      case when atcud.PT_SeriesDocumentType is initial or atcud.PT_SeriesDocumentType is null
        then cast('RG' as glo_pt_series_dt)
        else atcud.PT_SeriesDocumentType
      end                                                                                                                     as PT_SeriesDocumentType,

      case when atcud.PT_SeriesDocumentNumber is initial or atcud.PT_SeriesDocumentNumber is null
        then cast( '0' as glo_pt_atcud )
        else atcud.PT_SeriesDocumentNumber
      end                                                                                                                     as PT_SeriesDocumentNumber,

      _OneTimeAccount,
      _Customers,
      _TotalItems,
      _AccountingDocumentCategory,
      a._CompanyCode,
      a._CompanyCodeCurrency,
      _OperationalAcctgDocItem,
      _ReversalDocument,
      _RevReason,
      _DocTotals,
      _AccountingDocumentType

}
where
  a.IsReversal = ' '
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACCOUNTINGDOCUMENT",
"I_ACCOUNTINGDOCUMENTTYPE",
"I_CLRGINFORELATIONFORACCTGDOC",
"I_COMPANYCODE",
"I_PT_DGTLSGNTRNMBRRNGEACCTG",
"I_PT_PAYTRCPTSERSDOCNUMBER",
"I_PT_SAFTDOWNPAYMENTFINANCE",
"I_SAFTACCOUNTINGHEADER",
"P_PT_SAFTTAXLAND"
],
"ASSOCIATED":
[
"I_ACCOUNTINGDOCUMENT",
"I_ACCOUNTINGDOCUMENTCATEGORY",
"I_ACCOUNTINGDOCUMENTTYPE",
"I_COMPANYCODE",
"I_CURRENCY",
"I_OPERATIONALACCTGDOCITEM",
"I_PT_SAFTPAYMENTCUSTOMER",
"I_PT_SAFTPAYMENTONETIMECUST",
"I_PT_SAFTPAYMENTTOTALAMTCHECK",
"I_REVERSALREASON",
"I_SAFTACCOUNTINGTAXCHECK",
"P_PT_SAFTTAXLAND"
],
"BASE":
[
"I_SAFTACCOUNTINGHEADER"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/