P_DeltaOpenItem

@AbapCatalog.sqlViewName: 'PGROITDII'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck:  #MANDATORY 
//@EndUserText.label: 'Delta Open Item'

@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.representativeKey: 'AccountingDocumentItem'
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass:  #MIXED
@ObjectModel.usageType.serviceQuality: #D
@VDM.viewType: #CONSUMPTION
//@UI.presentationVariant.sortOrder.by: 'CreationDate, CreationTime'

//@UI.presentationVariant.sortOrder.direction: #ASC

@VDM.private: true
define view P_DeltaOpenItem
  as

  select from I_OperationalAcctgDocItem as InvoiceItem

    inner join I_AccountingDocument as InvoiceHeader on

        InvoiceItem.CompanyCode               = InvoiceHeader.CompanyCode and

        InvoiceItem.FiscalYear                = InvoiceHeader.FiscalYear and

        InvoiceItem.AccountingDocument        = InvoiceHeader.AccountingDocument

    left outer to one join I_AccountingDocument      as ClearingHeader on

        InvoiceItem.CompanyCode                = ClearingHeader.CompanyCode and

        InvoiceItem.ClearingDocFiscalYear      = ClearingHeader.FiscalYear and

        InvoiceItem.ClearingAccountingDocument = ClearingHeader.AccountingDocument
{
  key InvoiceItem.CompanyCode,
  key InvoiceItem.FiscalYear                                 as FiscalYear,
  key InvoiceItem.AccountingDocument                         as AccountingDocument,
  key InvoiceItem.AccountingDocumentItem                     as AccountingDocumentItem,
      InvoiceHeader.AccountingDocumentCreationDate           as CreationDate,
      InvoiceHeader.CreationTime                             as CreationTime,
      ClearingHeader.AccountingDocumentCreationDate          as ClearingCreationDate,
      ClearingHeader.CreationTime                            as ClearingCreationTime,
      InvoiceItem.DebitCreditCode,
      InvoiceItem.CompanyCodeCurrency,
      InvoiceItem.AmountInCompanyCodeCurrency,
      InvoiceItem.TransactionCurrency,
      InvoiceItem.AmountInTransactionCurrency,
      InvoiceItem.Customer,
      InvoiceItem.BillingDocument,
      InvoiceItem.AssignmentReference,
      InvoiceHeader.AccountingDocumentHeaderText, //                 as HeaderText,

      InvoiceItem.DocumentItemText,//                               as ItemtText,

      InvoiceItem.PaymentDifferenceReason,
      InvoiceHeader.AccountingDocumentType,
      InvoiceItem.ClearingDate,
      InvoiceItem.ClearingDocFiscalYear,
      InvoiceItem.ClearingAccountingDocument,
      cast( InvoiceItem.ClearingItem as agzei )           as ClearingItem,
      InvoiceHeader.LastChangeDate,
      InvoiceItem.PostingDate,
      InvoiceItem.DocumentDate,
      InvoiceItem.NetDueDate,
      InvoiceItem.CashDiscountBaseAmount,
      InvoiceItem.CashDiscountAmount,
      InvoiceItem.PaymentTerms,
      InvoiceItem.CashDiscount1Days,
      InvoiceItem.CashDiscount2Days,
      InvoiceItem.NetPaymentDays,
      InvoiceItem.CashDiscount1Percent,
      InvoiceItem.CashDiscount2Percent,
      InvoiceItem.DueCalculationBaseDate,
      InvoiceItem.FixedCashDiscount,
      InvoiceItem.PaymentBlockingReason,
      case when ( InvoiceItem.ClearingAccountingDocument is null or InvoiceItem.ClearingAccountingDocument = '' ) then
        concat(InvoiceHeader.AccountingDocumentCreationDate, InvoiceHeader.CreationTime)
      else
        concat(ClearingHeader.AccountingDocumentCreationDate, ClearingHeader.CreationTime)
      end                  as LastCreationOrClearingDatetime
}
where
    // We are not interested in residual items. They are already taken care by other extractors

    // We are currently also not interested in partial payment items, credit memo items and down payment items.

    (
      InvoiceItem.FollowOnDocumentType is null or
      InvoiceItem.FollowOnDocumentType = ''
    )
    and
      // Wer are only interested in A/R items

      InvoiceItem.FinancialAccountType = 'D'
    and
    // We are only interested in items of catagory '', 'S', or 'A'

    (
        InvoiceItem.AccountingDocumentCategory  = '' or
        InvoiceItem.AccountingDocumentCategory  = 'S' or
        InvoiceItem.AccountingDocumentCategory  = 'A'
    )