P_RO_SAFTJournalEntryItem

@AbapCatalog.sqlViewName: 'PROSAFTJRNENITM'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@VDM.viewType: #CONSUMPTION
@VDM.private:true
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@AccessControl.authorizationCheck: #MANDATORY
define view P_RO_SAFTJournalEntryItem
  with parameters

    P_AlternativeGLAccountIsUsed : saft_ro_alt_gl_account_flag

  as select from           I_StRpJournalEntryHeaderLog                                                                           as Log

    inner join             P_RO_SAFTJournalEntryItemExc(P_AlternativeGLAccountIsUsed : $parameters.P_AlternativeGLAccountIsUsed) as Item             on  Log.AccountingDocument = Item.AccountingDocument
                                                                                                                                                     and Log.CompanyCode        = Item.CompanyCode
                                                                                                                                                     and Log.FiscalYear         = Item.FiscalYear

    left outer to one join I_OperationalAcctgDocItem                                                                             as Bseg             on  Bseg.AccountingDocument     = Item.AccountingDocument
                                                                                                                                                     and Bseg.CompanyCode            = Item.CompanyCode
                                                                                                                                                     and Bseg.FiscalYear             = Item.FiscalYear
                                                                                                                                                     and Bseg.AccountingDocumentItem = Item.AccountingDocumentItem

    left outer to one join I_Country                                                                                             as BsegStcegCountry on BsegStcegCountry.Country = left(
      Bseg.VATRegistration, 2
    )

  // Customer VAT Registration Information

    left outer to one join I_RO_BusinessPartnerTaxType                                                                           as CustomerTaxType  on  CustomerTaxType.FinancialAccountType          =  'D'
                                                                                                                                                     and CustomerTaxType.BusinessPartner               =  Item.Customer
                                                                                                                                                     and CustomerTaxType.BusinessPartnerIsSAFTRelevant =  'X'
                                                                                                                                                     and CustomerTaxType.ValidFrom                     <= Item.PostingDate
                                                                                                                                                     and CustomerTaxType.ValidTo                       >= Item.PostingDate

  // Supplier VAT Registration Information

    left outer to one join I_RO_BusinessPartnerTaxType                                                                           as SupplierTaxType  on  SupplierTaxType.FinancialAccountType          =  'K'
                                                                                                                                                     and SupplierTaxType.BusinessPartner               =  Item.Supplier
                                                                                                                                                     and SupplierTaxType.BusinessPartnerIsSAFTRelevant =  'X'
                                                                                                                                                     and SupplierTaxType.ValidFrom                     <= Item.PostingDate
                                                                                                                                                     and SupplierTaxType.ValidTo                       >= Item.PostingDate

    left outer to one join I_RO_SAFTOneTimeCustomer                                                                              as OTC              on  OTC.AccountingDocument     = Item.AccountingDocument
                                                                                                                                                     and OTC.CompanyCode            = Item.CompanyCode
                                                                                                                                                     and OTC.FiscalYear             = Item.FiscalYear
                                                                                                                                                     and OTC.AccountingDocumentItem = Item.AccountingDocumentItem

    left outer to one join I_RO_SAFTOneTimeSupplier                                                                              as OTS              on  OTS.AccountingDocument     = Item.AccountingDocument
                                                                                                                                                     and OTS.CompanyCode            = Item.CompanyCode
                                                                                                                                                     and OTS.FiscalYear             = Item.FiscalYear
                                                                                                                                                     and OTS.AccountingDocumentItem = Item.AccountingDocumentItem

    left outer to one join I_RO_SAFTDocumentTypeMap                                                                              as DocTypeMap       on  DocTypeMap.AccountingDocumentType = Item.AccountingDocumentType
                                                                                                                                                     and DocTypeMap.CompanyCode            = Item.CompanyCode
    left outer to one join I_JournalEntry                                                                                        as Bkpf             on  Bkpf.AccountingDocument = Log.AccountingDocument
                                                                                                                                                     and Bkpf.CompanyCode        = Log.CompanyCode
                                                                                                                                                     and Bkpf.FiscalYear         = Log.FiscalYear


{

  key Log.StatryRptCategory,
  key Log.StatryRptgEntity,
  key Log.StatryRptRunID,
  key Item.SourceLedger,
  key Item.CompanyCode,
  key Item.FiscalYear,
  key Item.AccountingDocument,
  key Item.LedgerGLLineItem,
  key Item.Ledger,
      Item.AccountingDocumentItem,
      Item.FinancialAccountType,
      Item.Customer,
      Item.Supplier,
      Item.DebitCreditCode,
      Item.Product,
      Item.Plant,
      Item.TaxCode,
      Item.TaxCountry,
      Item.ChartOfAccounts,
      Item.ActiveChartOfAccounts,
      Item.ActiveGLAccount,
      Item.GLAccount,
      Item.GLAccountType,
      Item.CountryChartOfAccounts,
      Item.AlternativeGLAccount,
      Item.PostingDate,
      Item.DocumentDate,
      Item._JournalEntry.AccountingDocumentCreationDate,
      Item._JournalEntry.AccountingDocCreatedByUser,
      Item.AccountingDocumentType,
      Item.ValueDate,
      case Item.DocumentItemText
        when '' then 'N.A'
        else Item.DocumentItemText
      end                                                                                             as DocumentItemText,
      Item.BaseUnit,
      Item.ControllingArea,
      Item.ProfitCenter,
      Item._ProfitCenterTxt,
      Item.Segment,
      Item._SegmentText,
      Item.CostCenter,
      Item._CostCenterTxt,
      Item.OrderID,
      Item._Order,
      cast(Item.WBSElement as fis_wbs_no_conv)                                                        as WBSElement,
      Item.WBSElementInternalID,
      Item._WBSElementBasicDataText,
      Item.TransactionTypeDetermination,

      case Item._AccountingDocumentTypeText[1: Language = '4'].AccountingDocumentTypeName
        when '' then 'N.A'
        else Item._AccountingDocumentTypeText[1: Language = '4'].AccountingDocumentTypeName
      end                                                                                             as AccountingDocumentTypeName,

      case
        when Item.FinancialAccountType <> 'D' then '0'
        when Item.Customer <> '' and Bseg.VATRegistration <> '' and BsegStcegCountry.IsEuropeanUnionMember = 'X' then
          case
            when BsegStcegCountry.Country = 'RO' then replace(Bseg.VATRegistration, 'RO', '00')
            else cast(concat('01', Bseg.VATRegistration) as stceg)
          end
        when Item.Customer = '' or CustomerTaxType.TaxType = '7' then '0'
        when CustomerTaxType.VATRegistration <> '0' then CustomerTaxType.VATRegistration
        else OTC.VATRegistration
      end                                                                                             as CustomerVATRegistration,

      case
        when Item.FinancialAccountType <> 'K' then '0'
        when Item.Supplier <> '' and Bseg.VATRegistration <> '' and BsegStcegCountry.IsEuropeanUnionMember = 'X' then
          case
            when BsegStcegCountry.Country = 'RO' then replace(Bseg.VATRegistration, 'RO', '00')
            else cast(concat('01', Bseg.VATRegistration) as stceg)
          end
        when Item.Supplier = ''  or SupplierTaxType.TaxType = '7' then '0'
        when SupplierTaxType.VATRegistration <> '0' then SupplierTaxType.VATRegistration
        else OTS.VATRegistration
      end                                                                                             as SupplierVATRegistration,

      case
        when Item.Customer <> '' and Item.FinancialAccountType = 'D' then Item._Customer._CustomerToBusinessPartner._BusinessPartner.BusinessPartner
        when Item.Supplier <> '' and Item.FinancialAccountType = 'K' then Item._Supplier._SupplierToBusinessPartner._BusinessPartner.BusinessPartner
        else ''
      end                                                                                             as BusinessPartner,

      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      case Item.DebitCreditCode
        when 'H' then cast(-1 * Item.AmountInCompanyCodeCurrency as fis_cr_hsl)
        else cast(0 as fis_cr_hsl)
      end                                                                                             as CreditAmountInCoCodeCrcy,

      @Semantics.currencyCode:true
      case Item.DebitCreditCode
        when 'H' then Item.TransactionCurrency
        else cast('' as fis_rwcur)
      end                                                                                             as CreditAmountCurrency,

      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      case Item.DebitCreditCode
        when 'S' then cast(Item.AmountInCompanyCodeCurrency as fis_dr_hsl)
        else cast(0 as fis_dr_hsl)
      end                                                                                             as DebitAmountInCoCodeCrcy,

      @Semantics.currencyCode:true
      case Item.DebitCreditCode
        when 'S' then Item.TransactionCurrency
        else cast('' as fis_rwcur)
      end                                                                                             as DebitAmountCurrency,

      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      case Item.DebitCreditCode
        when 'H' then -1 * Item.AmountInCompanyCodeCurrency
        else Item.AmountInCompanyCodeCurrency
      end                                                                                             as AmountInCompanyCodeCurrency,
      @Semantics.currencyCode:true
      Item.CompanyCodeCurrency,

      @Semantics.amount.currencyCode: 'TransactionCurrency'
      case Item.DebitCreditCode
        when 'H' then cast(-1 * Item.AmountInTransactionCurrency as fis_cr_wsl)
        else cast(0 as fis_cr_hsl)
      end                                                                                             as CreditAmountInTransCrcy,
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      case Item.DebitCreditCode
        when 'S' then cast(Item.AmountInTransactionCurrency as fis_dr_wsl)
        else cast(0 as fis_dr_hsl)
      end                                                                                             as DebitAmountInTransCrcy,

      @Semantics.amount.currencyCode: 'TransactionCurrency'
      case Item.DebitCreditCode
        when 'H' then -1 * Item.AmountInTransactionCurrency
        else Item.AmountInTransactionCurrency
      end                                                                                             as AmountInTransactionCurrency,
      @Semantics.currencyCode:true
      Item.TransactionCurrency,
      Item.LogicalSystem,

      '                    '                                                                          as VATRegistration,
      ' '                                                                                             as TaxType,

      CustomerTaxType.TaxType                                                                         as RO_SAFTCustomerTaxType,
      SupplierTaxType.TaxType                                                                         as RO_SAFTSupplierTaxType,

      cast(concat(Item.CompanyCode,
           concat(Item.FiscalYear,
           concat(Item.AccountingDocumentType, Item.AccountingDocument))) as /ceecv/ro_transactionid) as RO_SAFTJournalTransaction,

      cast(
      case
        when Item._JournalEntry.AlternativeReferenceDocument <> '' then Item._JournalEntry.AlternativeReferenceDocument
        when Item._JournalEntry.DocumentReferenceID <> '' then Item._JournalEntry.DocumentReferenceID
        else Item.AccountingDocument
      end as /ceecv/ro_source_document_id)                                                            as RO_SAFTSourceDocument,

      cast(
        case Item._JournalEntry.AccountingDocumentCategory
          when '' then 'normal'
          else Item._JournalEntry.AccountingDocumentCategory
        end
      as /ceecv/ro_transaction_type)                                                                  as RO_SAFTJournalTransactionType,

      cast(
        case
          when Item._JournalEntry.AccountingDocumentHeaderText <> '' then Item._JournalEntry.AccountingDocumentHeaderText
          when Item._AccountingDocumentTypeText[1: Language = '4'].AccountingDocumentTypeName <> '' then Item._AccountingDocumentTypeText[1: Language = '4'].AccountingDocumentTypeName
          else 'N.A'
        end
      as /ceecv/rodescription)                                                                        as RO_SAFTJournalTransDescription,

        case
          when DocTypeMap.RO_SAFTIsExchHeaderTable = 'X' then cast (Bkpf.ExchangeRate as saft_ro_exchange_rate)
          when Item.TransactionTypeDetermination = 'PRD' then cast (0 as saft_ro_exchange_rate)
          when Item.AmountInTransactionCurrency <> 0 then cast(division(Item.AmountInCompanyCodeCurrency, Item.AmountInTransactionCurrency, 4) as saft_ro_exchange_rate)
          else  cast (0 as saft_ro_exchange_rate)
        end                                                                                           as RO_SAFTExchangeRate,

      Item._JournalEntry                                                                              as _JournalEntry
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACCOUNTINGDOCUMENTTYPETEXT",
"I_BUSINESSPARTNER",
"I_COUNTRY",
"I_CUSTOMER",
"I_CUSTOMERTOBUSINESSPARTNER",
"I_JOURNALENTRY",
"I_OPERATIONALACCTGDOCITEM",
"I_RO_BUSINESSPARTNERTAXTYPE",
"I_RO_SAFTDOCUMENTTYPEMAP",
"I_RO_SAFTONETIMECUSTOMER",
"I_RO_SAFTONETIMESUPPLIER",
"I_STRPJOURNALENTRYHEADERLOG",
"I_SUPPLIER",
"I_SUPPLIERTOBUSINESSPARTNER",
"P_RO_SAFTJOURNALENTRYITEMEXC"
],
"ASSOCIATED":
[
"I_COSTCENTERTEXT",
"I_JOURNALENTRY",
"I_ORDER",
"I_PROFITCENTERTEXT",
"I_SEGMENTTEXT",
"I_WBSELEMENTBASICDATA"
],
"BASE":
[
"P_RO_SAFTJOURNALENTRYITEMEXC"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/