P_MX_JrnlEntrForeignCompDets

@AbapCatalog.sqlViewName: 'PMXJEFRGNCOMP'
@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_JrnlEntrForeignCompDets
  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 JournalEntryItem
{
  key JournalEntryItem.SourceLedger,
  key JournalEntryItem.Ledger,
  key JournalEntryItem.CompanyCode,
  key JournalEntryItem.AccountingDocument,
  key JournalEntryItem.FiscalYear,
  key JournalEntryItem.AccountingDocumentItem,
  key JournalEntryItem.StatryRptgEntity,
  key JournalEntryItem.StatryRptCategory,
  key JournalEntryItem.StatryRptRunID,
      JournalEntryItem.TaxNumber1,
      JournalEntryItem.DocumentReferenceID,
      JournalEntryItem.AmountInTransactionCurrency,
      JournalEntryItem.TransactionCurrency,
      JournalEntryItem.ExchangeRate,
      JournalEntryItem.PaymentMethod,
      JournalEntryItem.JrnlEntryCntrySpecificRef5,
      'X' as IsOriginalDocument

}
where
       JournalEntryItem.Country               <> 'MX'
  and(
       JournalEntryItem.ElectronicInvoiceUUID is null
    or JournalEntryItem.ElectronicInvoiceUUID =  ''
  )
  and  JournalEntryItem.DocumentReferenceID   is not initial
  and  JournalEntryItem.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,
  key InvoiceDetails.Ledger,
  key PaidInvoices.ClearingCompanyCode                as CompanyCode,
  key PaidInvoices.ClearingAccountingDocument         as AccountingDocument,
  key PaidInvoices.ClearingDocFiscalYear              as FiscalYear,
  key InvoiceDetails.AccountingDocumentItem,
  key InvoiceDetails.StatryRptgEntity,
  key InvoiceDetails.StatryRptCategory,
  key InvoiceDetails.StatryRptRunID,
      InvoiceDetails.TaxNumber1,
      InvoiceDetails.DocumentReferenceID,
      abs(InvoiceDetails.AmountInTransactionCurrency) as AmountInTransactionCurrency,
      InvoiceDetails.TransactionCurrency,
      InvoiceDetails.ExchangeRate,
      InvoiceDetails.PaymentMethod,
      InvoiceDetails.JrnlEntryCntrySpecificRef5,
      ''                                              as IsOriginalDocument
}
where
       InvoiceDetails.Country               <> 'MX'
  and(
       InvoiceDetails.ElectronicInvoiceUUID is null
    or InvoiceDetails.ElectronicInvoiceUUID =  ''
  )
  and  InvoiceDetails.DocumentReferenceID   is not initial
  and  PaidInvoices.FinancialAccountType    <> 'S'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_STRPJOURNALENTRYHEADERLOG",
"P_JRNLENTRADDLCLRGINFORMATION",
"P_MX_JRNLENTRITMINVCDETAILS"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/