P_MX_JrnlEntrNationalCompDets

@AbapCatalog.sqlViewName: 'PMXJENATCOMP'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.personalData.blocking: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations:true
@ObjectModel.usageType.serviceQuality: #P

define view P_MX_JrnlEntrNationalCompDets
  with parameters
    P_Ledger          : fins_ledger,
    P_FiscalYear      : fis_gjahr_no_conv,
    P_FromPostingDate : fis_budat_from,
    P_ToPostingDate   : fis_budat_to

  as select from P_MX_JrnlEntrItmInvcDetails( P_Ledger          : $parameters.P_Ledger,
                                              P_FiscalYear      : $parameters.P_FiscalYear,
                                              P_FromPostingDate : $parameters.P_FromPostingDate,
                                              P_ToPostingDate   : $parameters.P_ToPostingDate ) as DocumentDetails

  // process all original documents (invoices and payments)

{
  key DocumentDetails.SourceLedger                as SourceLedger,
  key DocumentDetails.Ledger                      as Ledger,
  key DocumentDetails.CompanyCode                 as CompanyCode,
  key DocumentDetails.AccountingDocument          as AccountingDocument,
  key DocumentDetails.FiscalYear                  as FiscalYear,
  key DocumentDetails.AccountingDocumentItem      as AccountingDocumentItem,
  key DocumentDetails.StatryRptCategory           as StatryRptCategory,
  key DocumentDetails.StatryRptgEntity            as StatryRptgEntity,
  key DocumentDetails.StatryRptRunID              as StatryRptRunID,
      DocumentDetails.AmountInTransactionCurrency as AmountInTransactionCurrency,
      DocumentDetails.TransactionCurrency         as TransactionCurrency,
      DocumentDetails.ElectronicInvoiceUUID       as ElectronicInvoiceUUID,
      DocumentDetails.TaxNumber1                  as TaxNumber1,
      DocumentDetails.TaxNumberLineItem           as TaxNumberLineItem,
      DocumentDetails.ExchangeRate                as ExchangeRate,
      DocumentDetails.PaymentMethod               as PaymentMethod,
      DocumentDetails.JrnlEntryCntrySpecificRef5  as JrnlEntryCntrySpecificRef5,
      'X'                                         as IsOriginalDocument
}
where
      DocumentDetails.ElectronicInvoiceUUID <> ''
  and DocumentDetails.ElectronicInvoiceUUID is not null
  and DocumentDetails.IsReversal            <> 'X'

union all

// process all related documents, for example, invoices paid by a given payment document

select from  P_JrnlEntrAddlClrgInformation                                                  as PaidInvoices

  inner join P_MX_JrnlEntrItmInvcDetails( P_Ledger          : $parameters.P_Ledger,
                                          P_FiscalYear      : $parameters.P_FiscalYear,
                                          P_FromPostingDate : $parameters.P_FromPostingDate,
                                          P_ToPostingDate   : $parameters.P_ToPostingDate ) as InvoiceDetails on  InvoiceDetails.CompanyCode        = PaidInvoices.ClearingCompanyCode
                                                                                                              and InvoiceDetails.CompanyCode        = PaidInvoices.CompanyCode
                                                                                                              and InvoiceDetails.AccountingDocument = PaidInvoices.AccountingDocument
                                                                                                              and InvoiceDetails.FiscalYear         = PaidInvoices.FiscalYear

  inner join I_StRpJournalEntryHeaderLog                                                                      on  I_StRpJournalEntryHeaderLog.CompanyCode        = PaidInvoices.ClearingCompanyCode
                                                                                                              and I_StRpJournalEntryHeaderLog.AccountingDocument = PaidInvoices.ClearingAccountingDocument
                                                                                                              and I_StRpJournalEntryHeaderLog.FiscalYear         = PaidInvoices.ClearingDocFiscalYear
                                                                                                              and I_StRpJournalEntryHeaderLog.StatryRptCategory  = InvoiceDetails.StatryRptCategory
                                                                                                              and I_StRpJournalEntryHeaderLog.StatryRptgEntity   = InvoiceDetails.StatryRptgEntity
                                                                                                              and I_StRpJournalEntryHeaderLog.StatryRptRunID     = InvoiceDetails.StatryRptRunID
{
      // use the payment document key for all the invoices being paid

  key InvoiceDetails.SourceLedger                     as SourceLedger,
  key InvoiceDetails.Ledger                           as Ledger,
  key PaidInvoices.ClearingCompanyCode                as CompanyCode,
  key PaidInvoices.ClearingAccountingDocument         as AccountingDocument,
  key PaidInvoices.ClearingDocFiscalYear              as FiscalYear,
  key InvoiceDetails.AccountingDocumentItem           as AccountingDocumentItem,
  key InvoiceDetails.StatryRptCategory                as StatryRptCategory,
  key InvoiceDetails.StatryRptgEntity                 as StatryRptgEntity,
  key InvoiceDetails.StatryRptRunID                   as StatryRptRunID,

      abs(InvoiceDetails.AmountInTransactionCurrency) as AmountInTransactionCurrency,

      InvoiceDetails.TransactionCurrency              as TransactionCurrency,
      InvoiceDetails.ElectronicInvoiceUUID            as ElectronicInvoiceUUID,
      InvoiceDetails.TaxNumber1                       as TaxNumber1,
      InvoiceDetails.TaxNumberLineItem                as TaxNumberLineItem,
      InvoiceDetails.ExchangeRate                     as ExchangeRate,
      InvoiceDetails.PaymentMethod                    as PaymentMethod,
      InvoiceDetails.JrnlEntryCntrySpecificRef5       as JrnlEntryCntrySpecificRef5,
      ''                                              as IsOriginalDocument
}
where
      InvoiceDetails.ElectronicInvoiceUUID <> ''
  and InvoiceDetails.ElectronicInvoiceUUID is not null
  and PaidInvoices.FinancialAccountType    <> 'S' //disregard GL transfer documents


union all

// take compensation details from MM supplier invoices (MIRO) to include them under the related goods receipt (MIGO) document node

select from  P_MX_JrnlEntrItmInvcDetails( P_Ledger          : $parameters.P_Ledger,
                                          P_FiscalYear      : $parameters.P_FiscalYear,
                                          P_FromPostingDate : $parameters.P_FromPostingDate,
                                          P_ToPostingDate   : $parameters.P_ToPostingDate ) as SupplierInvoiceDetails

  inner join P_MX_JournalEntryGRHistory2                                                    as GoodsReceiptDetails on  GoodsReceiptDetails.Ledger             = SupplierInvoiceDetails.Ledger
                                                                                                                   and GoodsReceiptDetails.CompanyCode        = SupplierInvoiceDetails.CompanyCode
                                                                                                                   and GoodsReceiptDetails.AccountingDocument = SupplierInvoiceDetails.AccountingDocument
                                                                                                                   and GoodsReceiptDetails.FiscalYear         = SupplierInvoiceDetails.FiscalYear
{
  key SupplierInvoiceDetails.SourceLedger                     as SourceLedger,
  key SupplierInvoiceDetails.Ledger                           as Ledger,
  key SupplierInvoiceDetails.CompanyCode                      as CompanyCode,
  key GoodsReceiptDetails.MaterialDocumentNumber              as AccountingDocument,
  key GoodsReceiptDetails.MaterialDocumentYear                as FiscalYear,
  key SupplierInvoiceDetails.AccountingDocumentItem           as AccountingDocumentItem,
  key SupplierInvoiceDetails.StatryRptCategory                as StatryRptCategory,
  key SupplierInvoiceDetails.StatryRptgEntity                 as StatryRptgEntity,
  key SupplierInvoiceDetails.StatryRptRunID                   as StatryRptRunID,

      abs(SupplierInvoiceDetails.AmountInTransactionCurrency) as AmountInTransactionCurrency,

      SupplierInvoiceDetails.TransactionCurrency              as TransactionCurrency,
      SupplierInvoiceDetails.ElectronicInvoiceUUID            as ElectronicInvoiceUUID,
      SupplierInvoiceDetails.TaxNumber1                       as TaxNumber1,
      SupplierInvoiceDetails.TaxNumberLineItem                as TaxNumberLineItem,
      SupplierInvoiceDetails.ExchangeRate                     as ExchangeRate,
      SupplierInvoiceDetails.PaymentMethod                    as PaymentMethod,
      SupplierInvoiceDetails.JrnlEntryCntrySpecificRef5       as JrnlEntryCntrySpecificRef5,
      ''                                                      as IsOriginalDocument
}
where
  (
       SupplierInvoiceDetails.ReferenceDocumentType    =  'RMRP'
    or SupplierInvoiceDetails.ReferenceDocumentType    =  'CF3PM'
  )
  and  SupplierInvoiceDetails.ElectronicInvoiceUUID    <> ''
  and  SupplierInvoiceDetails.ElectronicInvoiceUUID    is not null
  and  SupplierInvoiceDetails.ReferenceDocument        is not null
  and  SupplierInvoiceDetails.ReferenceDocument        <> ''
  and  SupplierInvoiceDetails.ReferenceDocumentContext is not null
  and  SupplierInvoiceDetails.ReferenceDocumentContext <> ''
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_STRPJOURNALENTRYHEADERLOG",
"P_JRNLENTRADDLCLRGINFORMATION",
"P_MX_JOURNALENTRYGRHISTORY2",
"P_MX_JRNLENTRITMINVCDETAILS"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/