P_MLColl_AcctgDocItemCleared

@AbapCatalog: { sqlViewName: 'PMLACCTGDOCICLRD',
                compiler: { compareFilter: true },
                preserveKey: true }
@ClientHandling: { algorithm: #SESSION_VARIABLE }
@AccessControl: { authorizationCheck: #NOT_REQUIRED }
@VDM: { private:  true,
        viewType: #COMPOSITE }

define view P_MLColl_AcctgDocItemCleared
  with parameters
    P_ExchangeRateType : kurst,
    P_TargetCurrency   : vdm_v_target_currency,
    @Environment: { systemField : #SYSTEM_DATE }
    P_SystemDate       : sydate
  as select from I_OperationalAcctgDocItem as _AccDoc
    inner join   P_MLColl_DirectPayers5    as _DirectPayers on  _AccDoc.CompanyCode = _DirectPayers.CompanyCode
                                                            and _AccDoc.Customer    = _DirectPayers.Customer

  // VDM associations

  // Clearing date

  association [0..1] to I_CalendarDate              as _ClearingDate              on $projection.ClearingDate = _ClearingDate.CalendarDate

  // Customer to Business Partner

  association [0..1] to I_BusinessPartnerCustomer as _CustomerToBusinessPartner on $projection.Customer = _CustomerToBusinessPartner.Customer

{
      // VDM Fields

  key _AccDoc.CompanyCode,
  key _AccDoc.AccountingDocument,
  key _AccDoc.FiscalYear,
  key _AccDoc.AccountingDocumentItem,
      _AccDoc.DebitCreditCode,
      _AccDoc.Customer,
      _CustomerToBusinessPartner.BusinessPartner,
      _AccDoc._Customer.Country,
      _AccDoc._Customer.CustomerAccountGroup,

      substring( dats_add_months( cast( concat( _AccDoc.FiscalYear,
                                                '0101') as abap.dats ),
                                  12,
                                  'INITIAL' ) ,1 ,4 ) as NextFiscalYear,

      _AccDoc.ClearingDate                            as ClearingDate,
      _AccDoc.DueCalculationBaseDate                  as DueCalculationBaseDate,
      _AccDoc.LastDunningDate                         as LastDunningDate,
      _AccDoc.DunningLevel                            as DunningLevel,

      case when _AccDoc.DunningLevel > '0'
            then 1
           else 0 end                                 as NumberOfDunnedAcctgDocItems,

      _AccDoc.NetDueDate                              as NetDueDate,
      _AccDoc.AmountInTransactionCurrency,
      _AccDoc.TransactionCurrency                     as TransactionCurrency,
      _AccDoc.PaymentTerms                            as PaymentTerms,

      dats_days_between( _AccDoc.NetDueDate,
                         _AccDoc.ClearingDate )       as ClearingDelayInDays,

      cast( 1 as farr_counter )                       as NumberOfClearingDatePosInMonth,

      case
        when _ClearingDate.CalendarDay > '25'
          then 0
         when _ClearingDate.CalendarDay <= '25' and
              _ClearingDate.CalendarDay > '20'
           then 1
         when _ClearingDate.CalendarDay <= '20' and
              _ClearingDate.CalendarDay > '15'
           then 2
         when _ClearingDate.CalendarDay <= '15' and
              _ClearingDate.CalendarDay > '10'
           then 3
         when _ClearingDate.CalendarDay <= '10' and
              _ClearingDate.CalendarDay > '05'
           then 4
         else 5
      end                                             as ClearingDatePosInMonth,

      dats_days_between( _AccDoc.NetDueDate,
                         dats_add_days( $parameters.P_SystemDate,
                                        - 120,
                                        'INITIAL' ) ) as OverdueDays,

      :P_TargetCurrency                               as TargetCurrency,

      @Semantics: { amount: { currencyCode: 'TargetCurrency' } }
      currency_conversion(
          amount             => _AccDoc.AmountInTransactionCurrency,
          source_currency    => _AccDoc.TransactionCurrency,
          target_currency    => :P_TargetCurrency,
          exchange_rate_date => _AccDoc.DueCalculationBaseDate,
          exchange_rate_type => :P_ExchangeRateType,
          error_handling     => 'SET_TO_NULL',
          decimal_shift      => #CDSBoolean.true,
          round              => #CDSBoolean.true )    as AmountInTargetCurrency
}
where
      _AccDoc.DebitCreditCode        =  'S'
  and _AccDoc.ClearingDate           <> '00000000'
  and _AccDoc.NetDueDate             <> '00000000'
  and _AccDoc.DueCalculationBaseDate <> '00000000'
  and _AccDoc.DocumentDate           <= _AccDoc.DueCalculationBaseDate
  and _AccDoc.ClearingDate           >= _AccDoc.DocumentDate
  and _AccDoc.ClearingDate           >= _AccDoc.DueCalculationBaseDate
  and _AccDoc.InvoiceReference       =  ''
  and _AccDoc.FinancialAccountType   =  'D'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BUSINESSPARTNERCUSTOMER",
"I_CALENDARDATE",
"I_CUSTOMER",
"I_OPERATIONALACCTGDOCITEM",
"P_MLCOLL_DIRECTPAYERS5"
],
"ASSOCIATED":
[
"I_BUSINESSPARTNERCUSTOMER",
"I_CALENDARDATE"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/