I_GLJrnlEntryItemAmount

@AbapCatalog.sqlViewName: 'IGLJEIAMT'
@VDM.viewType: #COMPOSITE
@AccessControl.personalData.blocking: #NOT_REQUIRED
@AbapCatalog.compiler.compareFilter: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass:  #TRANSACTIONAL
@ObjectModel.usageType.sizeCategory: #XL
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'View to Calculate Line Item Amount'
define view I_GLJrnlEntryItemAmount
  //  as select from I_GLJrnlEntryItemToBeVerified as _LineItem

  //    inner join   I_AccountingDocument          as _Header on  _LineItem.SourceCompanyCode  = _Header.CompanyCode //CompanyCode

  //                                                          and _LineItem.AccountingDocument = _Header.AccountingDocument

  //                                                          and _LineItem.FiscalYear         = _Header.FiscalYear

  as select from    I_AccountingDocument          as _Header
    left outer join I_GLJrnlEntryItemToBeVerified as _LineItem on  _Header.CompanyCode        = _LineItem.SourceCompanyCode //CompanyCode

                                                               and _Header.AccountingDocument = _LineItem.AccountingDocument
                                                               and _Header.FiscalYear         = _LineItem.FiscalYear
{
  key _Header.CompanyCode        as CompanyCode,
  key _Header.AccountingDocument as AccountingDocument,
  key _Header.FiscalYear         as FiscalYear,

      @DefaultAggregation: #SUM
      cast(
       ( sum(
            case when _LineItem.DebitCreditCode = 'S'
                 then (
                       case when _Header.CompanyCode = _LineItem.CompanyCode or( _Header.CompanyCode <> _LineItem.CompanyCode and _Header.CompanyCodeCurrency = _LineItem.CompanyCodeCurrency )
                            then _LineItem.AmountInCompanyCodeCurrency
                            when _Header.CompanyCode <> _LineItem.CompanyCode and _Header.CompanyCodeCurrency <> _LineItem.CompanyCodeCurrency
                            then currency_conversion(           amount=>_LineItem.AmountInTransactionCurrency,
                                                                source_currency => _Header.Currency,
                                                                round =>  'X',
                                                                target_currency => _Header.CompanyCodeCurrency,
                                                                exchange_rate_date => _LineItem.ExchangeRateDate,
                                                                error_handling => 'SET_TO_NULL'
                                                    )
                       end
                 )
                 end
             ) )
             as azsol_f05a )     as GLDebitAmountInCoCodeCrcy,

      @DefaultAggregation: #SUM
      cast(
       ( sum(
            case when _LineItem.DebitCreditCode = 'H'
                 then (
                       case when _Header.CompanyCode = _LineItem.CompanyCode or( _Header.CompanyCode <> _LineItem.CompanyCode and _Header.CompanyCodeCurrency = _LineItem.CompanyCodeCurrency )
                            then _LineItem.AmountInCompanyCodeCurrency
                            when _Header.CompanyCode <> _LineItem.CompanyCode and _Header.CompanyCodeCurrency <> _LineItem.CompanyCodeCurrency
                            then currency_conversion(           amount=>_LineItem.AmountInTransactionCurrency,
                                                                source_currency => _Header.Currency,
                                                                round =>  'X',
                                                                target_currency => _Header.CompanyCodeCurrency,
                                                                exchange_rate_date => _LineItem.ExchangeRateDate,
                                                                error_handling => 'SET_TO_NULL'
                                                    )
                       end
                 )
                 end
             ) )
             as azsol_f05a )     as GLCreditAmountInCoCodeCrcy,

      @DefaultAggregation: #SUM
      sum(
      case when _LineItem.DebitCreditCode = 'S'
           then _LineItem.AmountInTransactionCurrency
           end
       )                         as AmountInTransactionCurrency




      //  cast(sum(_LineItem.AmountInCompanyCodeCurrency) as azsol_f05a ) as AmountInCompanyCodeCurrency,   //Amount in local currency



      //  cast(sum(_LineItem.AmountInTransactionCurrency) as azsol_f05a ) as AmountInTransactionCurrency   //Amount in document currency

}
//where (_LineItem.DebitCreditCode = 'S' and _LineItem.IsNegativePosting = '')

//  or (_LineItem.DebitCreditCode = 'H' and _LineItem.IsNegativePosting = 'X')

/* add by i337033 2021-05-28
   performance tuning */
where
  (
    (
         _Header.AccountingDocumentCategory = 'Z'
      or _Header.AccountingDocumentCategory = 'V'
    )
    and(
         _Header.TransactionCode            = 'FBDC_P001'
      or _Header.TransactionCode            = 'FBDC_P050'
      or _Header.TransactionCode            = 'FBDC_P051'
    )
  )

  or(
    (
         _Header.AccountingDocumentCategory = ''
      or _Header.AccountingDocumentCategory = 'L'
      or _Header.AccountingDocumentCategory = 'U'
    )
    and(
         _Header.TransactionCode            = 'FBVB'
    )
  )

group by
  _Header.CompanyCode,
  _Header.AccountingDocument,
  _Header.FiscalYear
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACCOUNTINGDOCUMENT",
"I_GLJRNLENTRYITEMTOBEVERIFIED"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/