P_MX_JrnlEntrItmInvcCompType

@AbapCatalog.sqlViewName: 'PMXINVCOMP'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.personalData.blocking: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType: { sizeCategory: #XL , serviceQuality: #P, dataClass: #MIXED }
@Metadata.ignorePropagatedAnnotations:true

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

  as select distinct 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,
  key DocumentDetails.Ledger,
  key DocumentDetails.CompanyCode,
  key DocumentDetails.AccountingDocument,
  key DocumentDetails.FiscalYear,

      //Audit Trail

      StatryRptCategory,
      StatryRptgEntity,
      StatryRptRunID,

      DocumentDetails.AccountingDocumentItem,

      DocumentReferenceID,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      DocumentDetails.AmountInTransactionCurrency,

      @Semantics.currencyCode:true
      DocumentDetails.TransactionCurrency,

      ElectronicInvoiceUUID,
      DocumentDetails.JrnlEntryCntrySpecificRef5,
      TaxNumber1,
      IsFormatedForDigitalInvoice,

      @DefaultAggregation: #MIN
      cast ( case
        when DocumentDetails.TransactionCurrency <> 'MXN' then ExchangeRate
        else 0
      end as ukurs_curr preserving type ) as ExchangeRate,
      Country,
      DocumentDetails.Supplier,
      DocumentDetails.Customer,
      DocumentDetails.FinancialAccountType,
      DocumentDetails.ClearingDocFiscalYear,

      DocumentDetails.IsReversal,
      DocumentDetails.IsReversed,
      DocumentDetails.PaymentMethod,
      DocumentDetails.InvoiceReferenceFiscalYear,
      DocumentDetails.AccountingDocumentType,
      DocumentDetails.PostingDate,
      cast ('00000000' as augdt )         as ClearingDate, //partial payments may not have a clearing date, causing aggregation in upper view to break

      'X'                                 as IsOriginalDocument
}
where
  (
    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,
  key InvoiceDetails.Ledger,
  key PaidInvoices.ClearingCompanyCode                as CompanyCode,
  key PaidInvoices.ClearingAccountingDocument         as AccountingDocument,
  key PaidInvoices.ClearingDocFiscalYear              as FiscalYear,

      InvoiceDetails.StatryRptCategory,
      InvoiceDetails.StatryRptgEntity,
      InvoiceDetails.StatryRptRunID,

      InvoiceDetails.AccountingDocumentItem,

      InvoiceDetails.DocumentReferenceID,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      abs(InvoiceDetails.AmountInTransactionCurrency) as AmountInTransactionCurrency,

      @Semantics.currencyCode:true
      InvoiceDetails.TransactionCurrency,

      InvoiceDetails.ElectronicInvoiceUUID,
      InvoiceDetails.JrnlEntryCntrySpecificRef5,
      InvoiceDetails.TaxNumber1,
      InvoiceDetails.IsFormatedForDigitalInvoice,

      @DefaultAggregation: #MIN
      cast ( case
        when InvoiceDetails.TransactionCurrency <> 'MXN' then InvoiceDetails.ExchangeRate
        else 0
      end as ukurs_curr preserving type )             as ExchangeRate,

      InvoiceDetails.Country,
      InvoiceDetails.Supplier,
      InvoiceDetails.Customer,
      InvoiceDetails.FinancialAccountType,
      PaidInvoices.ClearingDocFiscalYear,
      InvoiceDetails.IsReversal,
      InvoiceDetails.IsReversed,
      InvoiceDetails.PaymentMethod,
      InvoiceDetails.InvoiceReferenceFiscalYear,
      InvoiceDetails.AccountingDocumentType,
      InvoiceDetails.PostingDate,
      InvoiceDetails.ClearingDate,
      ''                                              as IsOriginalDocument
}
where
  (
              PaidInvoices.FinancialAccountType          <> 'S' //disregard GL transfer documents

    //This filters only the old mexico reporting type of invoices, the Document Reference ID field MUST have a '-' or ' 'in it,

    //so the fields CFD_CBB_Serie and CFD_CBB_NumFol are filled in the SAT report

    and(
      (
        (
              InvoiceDetails.Country                     =  'MX'
          and InvoiceDetails.IsFormatedForDigitalInvoice =  'X'
        )
        or
        //This filters the Foreing compensation invoices that MUST have info in the field Document Reference ID

        (
              InvoiceDetails.Country                     <> 'MX'
          and InvoiceDetails.DocumentReferenceID         is not null
          and InvoiceDetails.DocumentReferenceID         <> ''
        )
      )
      //If the Document have a UUID it is a valid Invoice no other filters are needed

      or(
              InvoiceDetails.ElectronicInvoiceUUID       is not null
        and   InvoiceDetails.ElectronicInvoiceUUID       <> ''
      )
    )
  )

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,
  key SupplierInvoiceDetails.Ledger,
  key SupplierInvoiceDetails.CompanyCode,
  key GoodsReceiptDetails.MaterialDocumentNumber              as AccountingDocument,
  key GoodsReceiptDetails.MaterialDocumentYear                as FiscalYear,

      SupplierInvoiceDetails.StatryRptCategory,
      SupplierInvoiceDetails.StatryRptgEntity,
      SupplierInvoiceDetails.StatryRptRunID,

      SupplierInvoiceDetails.AccountingDocumentItem,

      SupplierInvoiceDetails.DocumentReferenceID,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      abs(SupplierInvoiceDetails.AmountInTransactionCurrency) as AmountInTransactionCurrency,

      @Semantics.currencyCode:true
      SupplierInvoiceDetails.TransactionCurrency,

      SupplierInvoiceDetails.ElectronicInvoiceUUID,
      SupplierInvoiceDetails.JrnlEntryCntrySpecificRef5,
      SupplierInvoiceDetails.TaxNumber1,
      SupplierInvoiceDetails.IsFormatedForDigitalInvoice,

      @DefaultAggregation: #MIN
      cast ( case
        when SupplierInvoiceDetails.TransactionCurrency <> 'MXN' then SupplierInvoiceDetails.ExchangeRate
        else 0
      end as ukurs_curr preserving type )                     as ExchangeRate,

      SupplierInvoiceDetails.Country,
      SupplierInvoiceDetails.Supplier,
      SupplierInvoiceDetails.Customer,
      SupplierInvoiceDetails.FinancialAccountType,
      SupplierInvoiceDetails.ClearingDocFiscalYear,
      SupplierInvoiceDetails.IsReversal,
      SupplierInvoiceDetails.IsReversed,
      SupplierInvoiceDetails.PaymentMethod,
      SupplierInvoiceDetails.InvoiceReferenceFiscalYear,
      SupplierInvoiceDetails.AccountingDocumentType,
      SupplierInvoiceDetails.PostingDate,
      SupplierInvoiceDetails.ClearingDate,
      ''                                                      as IsOriginalDocument
}