P_InitialResidualOpenItem

@AbapCatalog.sqlViewName: 'PAGROII2'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #MANDATORY
//@EndUserText.label: 'Residual Open item'

@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #NOT_REQUIRED

//@ObjectModel.representativeKey: 'AccountingDocumentItem'

@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass:  #MIXED
@ObjectModel.usageType.serviceQuality: #D

@VDM.viewType: #CONSUMPTION

@Metadata.ignorePropagatedAnnotations: true

//@UI.presentationVariant.sortOrder.by: 'CreationDate, CreationTime'

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


@VDM.private: true
define view P_InitialResidualOpenItem
  as

  select from I_OperationalAcctgDocItem as ResidualOpenItem

    inner join I_OperationalAcctgDocItem as ResidualItem on

        // Make sure that the ResidualItem reference the same invoice accountig document


        // A/R line item as the ResidualOpenItem


        ResidualOpenItem.CompanyCode                  = ResidualItem.CompanyCode and

        ResidualOpenItem.InvoiceReferenceFiscalYear   = ResidualItem.InvoiceReferenceFiscalYear and

        ResidualOpenItem.InvoiceReference             = ResidualItem.InvoiceReference and

        ResidualOpenItem.InvoiceItemReference         = ResidualItem.InvoiceItemReference and

    // Make sure that ResidualOpenItem is A/R item


        ResidualOpenItem.FinancialAccountType         = 'D' and

    // Make sure that ResidualOpenItem is residual item


        ResidualOpenItem.FollowOnDocumentType         = 'V' and

    // Make sure that ResidualOpenItem is open item


        (
            ResidualOpenItem.ClearingAccountingDocument    is null or

            ResidualOpenItem.ClearingAccountingDocument = ''
        ) and

    // Make sure that ResidualOpenItem is only searched in relevant categories


       (
            ResidualOpenItem.AccountingDocumentCategory    = '' or

            ResidualOpenItem.AccountingDocumentCategory    = 'S' or

            ResidualOpenItem.AccountingDocumentCategory    = 'A'
       ) and

    // Make sure that ResidualItem is A/R item


       ResidualItem.FinancialAccountType             = 'D' and

    // Make sure that ResidualItem is residual item


       ResidualItem.FollowOnDocumentType             = 'V' and

    // Make sure that ResidualItem is only searched in relevant categories


       (
            ResidualItem.AccountingDocumentCategory        = '' or

            ResidualItem.AccountingDocumentCategory        = 'S' or

            ResidualItem.AccountingDocumentCategory        = 'A'
       )

  // Make sure that ResidualItem is not part of a reversal accounting document


    inner join I_AccountingDocument      as ResidualHeader on

        ResidualItem.CompanyCode        = ResidualHeader.CompanyCode and

        ResidualItem.AccountingDocument = ResidualHeader.AccountingDocument and

        ResidualItem.FiscalYear         = ResidualHeader.FiscalYear and

        (
            ResidualHeader.IsReversal     is null or

            ResidualHeader.IsReversal  = ''
        ) and

        (
            ResidualHeader.IsReversed     is null or

            ResidualHeader.IsReversed  = ''
        )

  // Make sure that ResidualOpenItem is not part of a reversal accounting document


    inner join I_AccountingDocument      as ResidualOpenHeader on

        ResidualOpenItem.CompanyCode        = ResidualOpenHeader.CompanyCode and

        ResidualOpenItem.AccountingDocument = ResidualOpenHeader.AccountingDocument and

        ResidualOpenItem.FiscalYear         = ResidualOpenHeader.FiscalYear
/*
        (
            ResidualOpenHeader.IsReversal     is null or

            ResidualOpenHeader.IsReversal  = ''
        ) and

        (
            ResidualOpenHeader.IsReversed     is null or

            ResidualOpenHeader.IsReversed  = ''
        )
*/
    inner join I_OperationalAcctgDocItem as InvoiceItem on
        ResidualOpenItem.CompanyCode                  = InvoiceItem.CompanyCode and
        ResidualOpenItem.InvoiceReferenceFiscalYear   = InvoiceItem.FiscalYear and
        ResidualOpenItem.InvoiceReference             = InvoiceItem.AccountingDocument and
        ResidualOpenItem.InvoiceItemReference         = InvoiceItem.AccountingDocumentItem
{

  key ResidualItem.CompanyCode,

  key ResidualItem.FiscalYear,

  key ResidualItem.AccountingDocument,

  key ResidualItem.AccountingDocumentItem,

  key ResidualOpenItem.FiscalYear                                 as OpenItemFiscalYear,

  key ResidualOpenItem.AccountingDocument                         as OpenItemAccountingDocument,

  key ResidualOpenItem.AccountingDocumentItem                     as OpenItemAccountingDocumentItem,

      ResidualItem.DebitCreditCode,

      ResidualItem.CompanyCodeCurrency,

      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      ResidualItem.AmountInCompanyCodeCurrency,

      ResidualItem.TransactionCurrency,

      @Semantics.amount.currencyCode: 'TransactionCurrency'
      ResidualItem.AmountInTransactionCurrency,

      ResidualItem.Customer,

      ResidualItem.BillingDocument,

      ResidualItem.AssignmentReference,

      ResidualItem.InvoiceReferenceFiscalYear,

      ResidualItem.InvoiceReference,

      ResidualItem.InvoiceItemReference,

      ResidualHeader.AccountingDocumentHeaderText,

      ResidualItem.DocumentItemText,

      ResidualItem.PaymentDifferenceReason,

      ResidualItem.ClearingDate,

      ResidualItem.ClearingCreationDate,

      ResidualItem.ClearingDocFiscalYear,

      ResidualItem.ClearingAccountingDocument,

      cast( ResidualItem.ClearingItem as agzei )                  as ClearingItem,

      ResidualHeader.AccountingDocumentCreationDate               as CreationDate,

      ResidualHeader.CreationTime                                 as CreationTime,

      ResidualOpenItem.CompanyCode                                as OpenItemCompanyCode,

      ResidualOpenHeader.AccountingDocumentCreationDate           as OpenItemCreationDate,

      ResidualOpenHeader.CreationTime                             as OpenItemCreationTime,

      ResidualItem.PostingDate,
      ResidualItem.DocumentDate,
      ResidualItem.NetDueDate,
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      ResidualItem.CashDiscountBaseAmount,
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      ResidualItem.CashDiscountAmount,
      ResidualItem.PaymentTerms,
      ResidualItem.CashDiscount1Days,
      ResidualItem.CashDiscount2Days,
      ResidualItem.NetPaymentDays,
      ResidualItem.CashDiscount1Percent,
      ResidualItem.CashDiscount2Percent,
      ResidualItem.DueCalculationBaseDate,
      ResidualItem.FixedCashDiscount,
      ResidualItem.PaymentBlockingReason,
      InvoiceItem.DebitCreditCode as II_DebitCreditCode,
      InvoiceItem.CompanyCodeCurrency as II_CompanyCodeCurrency,
      InvoiceItem.AmountInCompanyCodeCurrency as II_AmountInCompanyCodeCurrency,
      InvoiceItem.TransactionCurrency as II_TransactionCurrency,
      InvoiceItem.AmountInTransactionCurrency as II_AmountInTransactionCurrency,
      InvoiceItem.Customer as II_Customer,
      InvoiceItem.BillingDocument as II_BillingDocument,
      InvoiceItem.AssignmentReference as II_AssignmentReference,
      InvoiceItem.DocumentItemText as II_DocumentItemText,
      InvoiceItem.PaymentDifferenceReason as II_PaymentDifferenceReason,
      InvoiceItem.ClearingDate as II_ClearingDate,
      InvoiceItem.ClearingCreationDate as II_ClearingCreationDate,
      InvoiceItem.ClearingDocFiscalYear as II_ClearingDocFiscalYear,
      InvoiceItem.ClearingAccountingDocument as II_ClearingAccountingDocument,
      cast( InvoiceItem.ClearingItem as agzei ) as II_ClearingItem,
      InvoiceItem.PostingDate as II_PostingDate,
      InvoiceItem.DocumentDate as II_DocumentDate,
      InvoiceItem.NetDueDate as II_NetDueDate,
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      InvoiceItem.CashDiscountBaseAmount as II_CashDiscountBaseAmount,
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      InvoiceItem.CashDiscountAmount as II_CashDiscountAmount,
      InvoiceItem.PaymentTerms as II_PaymentTerms,
      InvoiceItem.CashDiscount1Days as II_CashDiscount1Days,
      InvoiceItem.CashDiscount2Days as II_CashDiscount2Days,
      InvoiceItem.NetPaymentDays as II_NetPaymentDays,
      InvoiceItem.CashDiscount1Percent as II_CashDiscount1Percent,
      InvoiceItem.CashDiscount2Percent as II_CashDiscount2Percent,
      InvoiceItem.DueCalculationBaseDate as II_DueCalculationBaseDate,
      InvoiceItem.FixedCashDiscount as II_FixedCashDiscount,
      InvoiceItem.PaymentBlockingReason as II_PaymentBlockingReason

}