P_MX_OPERATIONALACCTDOCITEM

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

define view P_MX_OperationalAcctDocItem
  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 I_JournalEntryItem        as JournalEntryItem

    inner join   I_StRpJournalEntryHeaderLog               on  I_StRpJournalEntryHeaderLog.CompanyCode        = JournalEntryItem.CompanyCode
                                                           and I_StRpJournalEntryHeaderLog.FiscalYear         = JournalEntryItem.FiscalYear
                                                           and I_StRpJournalEntryHeaderLog.AccountingDocument = JournalEntryItem.AccountingDocument

    inner join   I_OperationalAcctgDocItem as DocumentItem on  JournalEntryItem.CompanyCode            = DocumentItem.CompanyCode
                                                           and JournalEntryItem.AccountingDocument     = DocumentItem.AccountingDocument
                                                           and JournalEntryItem.FiscalYear             = DocumentItem.FiscalYear
                                                           and JournalEntryItem.AccountingDocumentItem = DocumentItem.AccountingDocumentItem

  association [0..1] to I_JournalEntryItemOneTimeData as _OneTimeAccountBP on  _OneTimeAccountBP.CompanyCode            = $projection.CompanyCode
                                                                           and _OneTimeAccountBP.AccountingDocument     = $projection.AccountingDocument
                                                                           and _OneTimeAccountBP.FiscalYear             = $projection.FiscalYear
                                                                           and _OneTimeAccountBP.AccountingDocumentItem = $projection.AccountingDocumentItem


{
      // This view was created to solve the BP Name, Country and TaxNumber for OneTimeBP, supplier and customer in specifics fields

  key JournalEntryItem.SourceLedger,
  key JournalEntryItem.Ledger,
  key JournalEntryItem.CompanyCode,
  key JournalEntryItem.AccountingDocument,
  key cast( JournalEntryItem.FiscalYear as fis_gjahr_no_conv preserving type )                                                                                                                            as FiscalYear,
  key JournalEntryItem.AccountingDocumentItem,
  key I_StRpJournalEntryHeaderLog.StatryRptgEntity,
  key I_StRpJournalEntryHeaderLog.StatryRptCategory,
  key I_StRpJournalEntryHeaderLog.StatryRptRunID,

      cast( coalesce( _OneTimeAccountBP.BusinessPartnerName1, coalesce( JournalEntryItem._Supplier.OrganizationBPName1,  JournalEntryItem._Customer.OrganizationBPName1 ) ) as ad_name1 preserving type ) as OrganizationBPName1,
      cast( coalesce( _OneTimeAccountBP.Country,              coalesce( JournalEntryItem._Supplier.Country,              JournalEntryItem._Customer.Country             ) ) as land1_gp preserving type ) as Country,
      cast( coalesce( _OneTimeAccountBP.TaxID1,               coalesce( JournalEntryItem._Supplier.TaxNumber1,           JournalEntryItem._Customer.TaxNumber1          ) ) as stcd1 preserving type )    as TaxNumber1,

      //This field verify if there is one of the options for separete the prefix to the folio number,

      //that is used in the P_MX_JrnlEntrItmInvcDetails view for logic

      instr( JournalEntryItem._JournalEntry.DocumentReferenceID, '-' )                                                                                                                                    as DigInvcOp1,
      instr( JournalEntryItem._JournalEntry.DocumentReferenceID, ' ' )                                                                                                                                    as DigInvcOp2,

      cast( case when JournalEntryItem._JournalEntry.TransactionCurrency <> 'MXN' then JournalEntryItem._JournalEntry.ExchangeRate end as kursf preserving type )                                         as ExchangeRate,

      JournalEntryItem._JournalEntry.DocumentReferenceID,
      JournalEntryItem.AmountInTransactionCurrency,
      JournalEntryItem.TransactionCurrency,
      JournalEntryItem.DebitCreditCode,

      JournalEntryItem.ReferenceDocument,
      JournalEntryItem.ReferenceDocumentContext,
      JournalEntryItem.ReferenceDocumentType,
      JournalEntryItem.IsReversal,
      JournalEntryItem.IsReversed,
      JournalEntryItem._JournalEntry.SenderLogicalSystem,
      JournalEntryItem._JournalEntry.AccountingDocumentHeaderText,
      JournalEntryItem._JournalEntry.TransactionCode,

      // UUID fields at header and item levels

      JournalEntryItem._JournalEntry.JrnlEntryCntrySpecificRef1,
      DocumentItem.OplAcctgDocItmCntrySpcfcRef1,
      // Manual Payment Details

      DocumentItem.PaymentMethod,
      cast( substring( JournalEntryItem._JournalEntry.JrnlEntryCntrySpecificRef5, 1, 50 ) as figlmx_manualpaymentdetails preserving type )                                                                as JrnlEntryCntrySpecificRef5,

      // Tax Number at line item level

      DocumentItem.Reference3IDByBusinessPartner                                                                                                                                                          as TaxNumberLineItem,

      JournalEntryItem.PostingDate,
      JournalEntryItem.FinancialAccountType,
      JournalEntryItem.Customer,
      JournalEntryItem.Supplier,
      JournalEntryItem.AccountingDocumentType,
      JournalEntryItem.ClearingDate,
      JournalEntryItem.ClearingDocFiscalYear,
      JournalEntryItem.InvoiceReference,
      cast( JournalEntryItem.InvoiceReferenceFiscalYear as fis_gjahr_no_conv preserving type )                                                                                                            as InvoiceReferenceFiscalYear,

      // Associations

      JournalEntryItem._Customer,
      JournalEntryItem._Supplier,
      JournalEntryItem._JournalEntry,
      _OneTimeAccountBP
}
where
        JournalEntryItem.Ledger                   = $parameters.P_Ledger
  and   JournalEntryItem.FiscalYear               = $parameters.P_FiscalYear
  and   JournalEntryItem.PostingDate              between $parameters.P_FromPostingDate and $parameters.P_ToPostingDate
  and   JournalEntryItem.FinancialAccountType     = 'K'
  or    JournalEntryItem.FinancialAccountType     = 'D'
  or(
        JournalEntryItem.FinancialAccountType     = 'S'
    and DocumentItem.OplAcctgDocItmCntrySpcfcRef1 is not null
    and DocumentItem.OplAcctgDocItmCntrySpcfcRef1 is not initial
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CUSTOMER",
"I_JOURNALENTRY",
"I_JOURNALENTRYITEM",
"I_JOURNALENTRYITEMONETIMEDATA",
"I_OPERATIONALACCTGDOCITEM",
"I_STRPJOURNALENTRYHEADERLOG",
"I_SUPPLIER"
],
"ASSOCIATED":
[
"I_CUSTOMER",
"I_JOURNALENTRY",
"I_JOURNALENTRYITEMONETIMEDATA",
"I_SUPPLIER"
],
"BASE":
[
"I_JOURNALENTRYITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/