P_ARLineItemForCorrespondence

@AbapCatalog.sqlViewName: 'PARLIITFORCOR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true

@VDM.viewType: #COMPOSITE
@VDM.private: true

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

@AccessControl.authorizationCheck: #NOT_REQUIRED

@ClientHandling.algorithm: #SESSION_VARIABLE

define view P_ARLineItemForCorrespondence
  with parameters
    P_KeyDate   : vdm_v_key_date,
    @Environment.systemField: #SYSTEM_DATE
    P_TodayDate : abap.dats

  // posted debit items

  as select from I_OperationalAcctgDocItem
{
  key CompanyCode,
  key AccountingDocument,
  key FiscalYear,
  key AccountingDocumentItem,
      FinancialAccountType,
      //      case when FinancialAccountType = 'D'

      //        then Customer

      //        else Supplier

      //        end as CustomerSupplierAccount,

      Customer                                                                                as  CustomerSupplierAccount,
      case :P_KeyDate
        when '00000000' then
           cast( :P_TodayDate as farp_keydt )
        else
           cast( :P_KeyDate as farp_keydt )
        end                                                                                    as  KeyDate,
      PostingDate,
      DocumentDate,
      I_OperationalAcctgDocItem._JournalEntry.AccountingDocumentCreationDate                   as  AccountingDocumentCreationDate,
      TransactionCurrency,
      CompanyCodeCurrency,
      I_OperationalAcctgDocItem._JournalEntry.DocumentReferenceID                              as  DocumentReferenceID,
      AccountingDocumentType,
      AccountingDocumentCategory,
      case AccountingDocumentCategory
          when 'S' then //Noted items

              'M'
          when 'V' then //Parked document

              'P'
          when 'W' then //Parked document with change of document ID

              'P'
          else          //Normal Document

              case when SpecialGLCode = '' then
                  'N'
              else
                  'S'
              end
      end                                                                                      as  DueItemType,
      BranchAccount,
      FiscalPeriod,
      PostingKey,
      SpecialGLCode,
      Customer,
      Supplier,
      ClearingDate,
      ClearingJournalEntry                                                                     as  ClearingAccountingDocument,
      AssignmentReference,
      DebitCreditCode,
      AmountInTransactionCurrency,
      AmountInCompanyCodeCurrency,
      BalanceTransactionCurrency,
      AmountInBalanceTransacCrcy,
      NetDueDate,
      DocumentItemText,
      //      Do not use the auxiliary field ITEM_TYPE_BRANCH. It is only for SAP internal usage. It might be removed in the future.

      //      The field is needed for performance optimization. It specifies the branch of the Union per which a line item was selected.

      cast ( 'BSEG_DEBIT_SIDE_BRANCH' as char30 )                                              as  ItemTypeBranch,
      //      BELEGNUM,

      //      blart_desc,

      //      bschl_desc,

      //      umskz_desc

      DueCalculationBaseDate,
      CashDiscount1Days,
      CashDiscount2Days,
      CashDiscount1DueDate,
      CashDiscount2DueDate,
      BillingDocument,
      case
        when InvoiceReference = '' and FollowOnDocumentType = ''
          then AccountingDocument
          else InvoiceReference
      end
        as InvoiceReference,
      case
        when InvoiceReference = '' and FollowOnDocumentType = ''
          then FiscalYear
          else InvoiceReferenceFiscalYear
      end
        as InvoiceReferenceFiscalYear,
      case
        when InvoiceReference = '' and FollowOnDocumentType = ''
          then cast('000' as fis_rebzz)
          else InvoiceItemReference
      end
        as InvoiceItemReference,
      Reference1IDByBusinessPartner,
      Reference2IDByBusinessPartner,
      Reference3IDByBusinessPartner,
      BPBankAccountInternalID,
      PaymentReference,
      ReferenceDocumentType,
      OriginalReferenceDocument
}
where
  (
      FinancialAccountType     = 'D'
  )
  and not(
    AccountingDocumentCategory = 'D'
  )
  and not(
    AccountingDocumentCategory = 'M'
  )

union all
//  posted credit items of mixed accounts

select from  I_OperationalAcctgDocItem
  inner join P_CustomerAsSupplier on I_OperationalAcctgDocItem.Supplier = P_CustomerAsSupplier.Supplier
{
  key CompanyCode,
  key AccountingDocument,
  key FiscalYear,
  key AccountingDocumentItem,
      FinancialAccountType,
      //      case when FinancialAccountType = 'D'

      //        then Customer

      //        else Supplier

      //        end as CustomerSupplierAccount,

     I_OperationalAcctgDocItem.Supplier                                                       as  CustomerSupplierAccount,
      case :P_KeyDate
        when '00000000' then
           cast( :P_TodayDate as farp_keydt )
        else
           cast( :P_KeyDate as farp_keydt )
        end                                                                                    as  KeyDate,
      PostingDate,
      DocumentDate,
      I_OperationalAcctgDocItem._JournalEntry.AccountingDocumentCreationDate                   as  AccountingDocumentCreationDate,
      TransactionCurrency,
      CompanyCodeCurrency,
      I_OperationalAcctgDocItem._JournalEntry.DocumentReferenceID                              as  DocumentReferenceID,
      AccountingDocumentType,
      AccountingDocumentCategory,
      case AccountingDocumentCategory
          when 'S' then //Noted items

              'M'
          when 'V' then //Parked document

              'P'
          when 'W' then //Parked document with change of document ID

              'P'
          else          //Normal Document

              case when SpecialGLCode = '' then
                  'N'
              else
                  'S'
              end
      end                                                                                      as  DueItemType,
      BranchAccount,
      FiscalPeriod,
      PostingKey,
      SpecialGLCode,
      P_CustomerAsSupplier.Customer                                                            as  Customer,
      P_CustomerAsSupplier.Supplier                                                            as  Supplier,
      ClearingDate,
      ClearingJournalEntry                                                                     as  ClearingAccountingDocument,
      AssignmentReference,
      DebitCreditCode,
      AmountInTransactionCurrency,
      AmountInCompanyCodeCurrency,
      BalanceTransactionCurrency,
      AmountInBalanceTransacCrcy,
      NetDueDate,
      DocumentItemText,
      //      Do not use the auxiliary field ITEM_TYPE_BRANCH. It is only for SAP internal usage. It might be removed in the future.

      //      The field is needed for performance optimization. It specifies the branch of the Union per which a line item was selected.

      cast ( 'BSEG_CREDIT_SIDE_BRANCH' as char30 )                                             as  ItemTypeBranch,
      DueCalculationBaseDate,
      CashDiscount1Days,
      CashDiscount2Days,
      CashDiscount1DueDate,
      CashDiscount2DueDate,
      BillingDocument,
      case
        when InvoiceReference = '' and FollowOnDocumentType = ''
          then AccountingDocument
          else InvoiceReference
      end
        as InvoiceReference,
      case
        when InvoiceReference = '' and FollowOnDocumentType = ''
          then FiscalYear
          else InvoiceReferenceFiscalYear
      end
        as InvoiceReferenceFiscalYear,
      case
        when InvoiceReference = '' and FollowOnDocumentType = ''
          then cast('000' as fis_rebzz)
          else InvoiceItemReference
      end
        as InvoiceItemReference,
      Reference1IDByBusinessPartner,
      Reference2IDByBusinessPartner,
      Reference3IDByBusinessPartner,
      BPBankAccountInternalID,
      PaymentReference,
      ReferenceDocumentType,
      OriginalReferenceDocument
}
where
      FinancialAccountType     = 'K'
  and not(
    AccountingDocumentCategory = 'D'
  ) //D = Recurring entry document

  and not(
    AccountingDocumentCategory = 'M'
  ) //M = Sample document

  and P_CustomerAsSupplier.Supplier != ''

union all
//  parked debit items

select from I_ParkedReceivablesItem
association [1..1] to I_ParkedAccountingDocument as _ParkedAccountingDocument on  $projection.AccountingDocument = _ParkedAccountingDocument.AccountingDocument
                                                                              and $projection.FiscalYear         = _ParkedAccountingDocument.FiscalYear
                                                                              and $projection.CompanyCode        = _ParkedAccountingDocument.CompanyCode
{
  key CompanyCode,
  key AccountingDocument,
  key FiscalYear,
  key AccountingDocumentItem,
      'D'                                                                                      as  FinancialAccountType,
      //      case when FinancialAccountType = 'D'

      //        then Customer

      //        else Supplier

      //        end as CustomerSupplierAccount,

      I_ParkedReceivablesItem.Debtor                                                          as  CustomerSupplierAccount,
      case :P_KeyDate
        when '00000000' then
           cast( :P_TodayDate as farp_keydt )
        else
           cast( :P_KeyDate as farp_keydt )
        end                                                                                    as  KeyDate,
      _ParkedAccountingDocument.PostingDate,
      _ParkedAccountingDocument.DocumentDate,
      _ParkedAccountingDocument.AccountingDocumentCreationDate                                 as  AccountingDocumentCreationDate,
      TransactionCurrency,
      CompanyCodeCurrency,
      _ParkedAccountingDocument.DocumentReferenceID                                            as  DocumentReferenceID,
      _ParkedAccountingDocument.AccountingDocumentType,
      _ParkedAccountingDocument.AccountingDocumentCategory,
      case _ParkedAccountingDocument.AccountingDocumentCategory
          when 'S' then //Noted items

              'M'
          when 'V' then //Parked document

              'P'
          when 'W' then //Parked document with change of document ID

              'P'
          else          //Normal Document

              case when SpecialGLCode = '' then
                  'N'
              else
                  'S'
              end
      end                                                                                      as  DueItemType,
      I_ParkedReceivablesItem.BranchAccount                                                    as  BranchAccount,
      _ParkedAccountingDocument.FiscalPeriod,
      PostingKey,
      SpecialGLCode,
      I_ParkedReceivablesItem.Debtor                                                           as  Customer,
      ''                                                                                       as  Supplier,
      '00000000'                                                                               as  ClearingDate,
      '          '                                                                             as  ClearingAccountingDocument,
      AssignmentReference,
      DebitCreditCode,
      AmountInTransactionCurrency,
      AmountInCompanyCodeCurrency,
      '     '                                                                                  as  BalanceTransactionCurrency,
      0                                                                                        as  AmountInBalanceTransacCrcy,
      NetDueDate,
      DocumentItemText,
      //      Do not use the auxiliary field ITEM_TYPE_BRANCH. It is only for SAP internal usage. It might be removed in the future.

      //      The field is needed for performance optimization. It specifies the branch of the Union per which a line item was selected.

      cast ( 'VBSEGD_BRANCH' as char30 )                                                       as  ItemTypeBranch,
      DueCalculationBaseDate,
      CashDiscount1Days,
      CashDiscount2Days,
      CashDiscount1DueDate,
      CashDiscount2DueDate,
      '          '                                                                             as  BillingDocument,
      case
        when InvoiceReference = '' and FollowOnDocumentType = ''
          then AccountingDocument
          else InvoiceReference
      end
        as InvoiceReference,
      case
        when InvoiceReference = '' and FollowOnDocumentType = ''
          then FiscalYear
          else InvoiceReferenceFiscalYear
      end
        as InvoiceReferenceFiscalYear,
      case
        when InvoiceReference = '' and FollowOnDocumentType = ''
          then cast('000' as fis_rebzz)
          else InvoiceItemReference
      end
        as InvoiceItemReference,
      Reference1IDByBusinessPartner,
      Reference2IDByBusinessPartner,
      Reference3IDByBusinessPartner,
      BPBankAccountInternalID,
      PaymentReference,
      ReferenceDocumentType,
      OriginalReferenceDocument
}

union all
//  parked credit items of mixed accounts

select from  I_ParkedPayablesItem
  inner join P_CustomerAsSupplier on I_ParkedPayablesItem.Creditor = P_CustomerAsSupplier.Supplier

association [1..1] to I_ParkedAccountingDocument as _ParkedAccountingDocument on  $projection.AccountingDocument = _ParkedAccountingDocument.AccountingDocument
                                                                              and $projection.FiscalYear         = _ParkedAccountingDocument.FiscalYear
                                                                              and $projection.CompanyCode        = _ParkedAccountingDocument.CompanyCode

{
  key CompanyCode,
  key AccountingDocument,
  key FiscalYear,
  key AccountingDocumentItem,
      'K'                                                                                               as  FinancialAccountType,
      //      case when FinancialAccountType = 'D'

      //        then Customer

      //        else Supplier

      //        end as CustomerSupplierAccount,

      P_CustomerAsSupplier.Supplier                                                                     as  CustomerSupplierAccount,
      case :P_KeyDate
        when '00000000' then
           cast( :P_TodayDate as farp_keydt )
        else
           cast( :P_KeyDate as farp_keydt )
        end                                                                                             as  KeyDate,
      _ParkedAccountingDocument.PostingDate,
      _ParkedAccountingDocument.DocumentDate,
      _ParkedAccountingDocument.AccountingDocumentCreationDate                                          as  AccountingDocumentCreationDate,
      TransactionCurrency,
      CompanyCodeCurrency,
      _ParkedAccountingDocument.DocumentReferenceID                                                     as  DocumentReferenceID,
      _ParkedAccountingDocument.AccountingDocumentType,
      _ParkedAccountingDocument.AccountingDocumentCategory,
      case _ParkedAccountingDocument.AccountingDocumentCategory
      when 'S' then     //Noted items

        'M'
      when 'V' then     //Parked document

        'P'
      when 'W' then     //Parked document with change of document ID

        'P'
      else              //Normal Document

        case when SpecialGLCode = '' then
            'N'
        else
            'S'
        end
      end                                                                                               as  DueItemType,
      I_ParkedPayablesItem.BranchAccount                                                                as  BranchAccount,
      _ParkedAccountingDocument.FiscalPeriod,
      PostingKey,
      SpecialGLCode,
      P_CustomerAsSupplier.Customer                                                                     as  Customer,
      P_CustomerAsSupplier.Supplier                                                                     as  Supplier,
      '00000000'                                                                                        as  ClearingDate,
      '          '                                                                                      as  ClearingAccountingDocument,
      AssignmentReference,
      DebitCreditCode,
      AmountInTransactionCurrency,
      AmountInCompanyCodeCurrency,
      '     '                                                                                           as  BalanceTransactionCurrency,
      0                                                                                                 as  AmountInBalanceTransacCrcy,
      NetDueDate,
      DocumentItemText,
      //      Do not use the auxiliary field ITEM_TYPE_BRANCH. It is only for SAP internal usage. It might be removed in the future.

      //      The field is needed for performance optimization. It specifies the branch of the Union per which a line item was selected.

      cast ( 'VBSEGK_BRANCH' as char30 )                                                                as  ItemTypeBranch,
      DueCalculationBaseDate,
      CashDiscount1Days,
      CashDiscount2Days,
      CashDiscount1DueDate,
      CashDiscount2DueDate,
      '          '                                                                                      as  BillingDocument,
      case
        when InvoiceReference = '' and FollowOnDocumentType = ''
          then AccountingDocument
          else InvoiceReference
      end
        as InvoiceReference,
      case
        when InvoiceReference = '' and FollowOnDocumentType = ''
          then FiscalYear
          else InvoiceReferenceFiscalYear
      end
        as InvoiceReferenceFiscalYear,
      case
        when InvoiceReference = '' and FollowOnDocumentType = ''
          then cast('000' as fis_rebzz)
          else InvoiceItemReference
      end
        as InvoiceItemReference,
      Reference1IDByBusinessPartner,
      Reference2IDByBusinessPartner,
      Reference3IDByBusinessPartner,
      BPBankAccountInternalID,
      PaymentReference,
      ReferenceDocumentType,
      OriginalReferenceDocument
}
where
  P_CustomerAsSupplier.Supplier != ''
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_JOURNALENTRY",
"I_OPERATIONALACCTGDOCITEM",
"I_PARKEDACCOUNTINGDOCUMENT",
"I_PARKEDPAYABLESITEM",
"I_PARKEDRECEIVABLESITEM",
"P_CUSTOMERASSUPPLIER"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/