I_AU_StRpTPARInvoiceLineItems

@AbapCatalog.sqlViewName: 'IAUTPARINVCLI'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #NOT_REQUIRED
@EndUserText.label: 'Australia TPAR Invoice Line Items'
define view I_AU_StRpTPARInvoiceLineItems
  with parameters
    P_FromReportingDate : figen_rep_date_from,
    P_ToReportingDate   : figen_rep_date_to

  as select from            P_AU_StRpTPARSuplrAcctgDocItm
                 ( P_FromReportingDate : :P_FromReportingDate,
                            P_ToReportingDate   : :P_ToReportingDate ) as StRpTPARSuplrAcctgDocItm

  //Inner join with supplier to remove the one time vendors

    inner join              I_Supplier                                 as Supplier                on Supplier.Supplier = StRpTPARSuplrAcctgDocItm.Supplier

  //Inner join for getting the G/L lines

    inner join              I_OperationalAcctgDocItem                  as OperationalAcctgDocItem on  StRpTPARSuplrAcctgDocItm.CompanyCode        = OperationalAcctgDocItem.CompanyCode
                                                                                                  and StRpTPARSuplrAcctgDocItm.AccountingDocument = OperationalAcctgDocItem.AccountingDocument
                                                                                                  and StRpTPARSuplrAcctgDocItm.FiscalYear         = OperationalAcctgDocItem.FiscalYear
  //Inner join for getting cost elements (Internal Order and WBS Element)

    inner join              I_JournalEntryItemCube                     as JournalEntryItemCube on  StRpTPARSuplrAcctgDocItm.CompanyCode        = JournalEntryItemCube.CompanyCode
                                                                                               and StRpTPARSuplrAcctgDocItm.AccountingDocument = JournalEntryItemCube.AccountingDocument
                                                                                               and StRpTPARSuplrAcctgDocItm.FiscalYear         = JournalEntryItemCube.FiscalYear

    inner join             I_Ledger                                   as Ledger    on JournalEntryItemCube.Ledger       = Ledger.Ledger
                                                                                  and JournalEntryItemCube.SourceLedger = Ledger.Ledger
                                                                                  and Ledger.IsLeadingLedger            = 'X'
  //Inner join to get the GL accounts of the TPAR Services and Grants

    inner join              P_AU_StrpTPARServiceType                   as StRpTPARServiceType
                                                                                                  on JournalEntryItemCube.CompanyCode                        =       StRpTPARServiceType.CompanyCode
                                                                                                  and JournalEntryItemCube.GLAccount                         between StRpTPARServiceType.FromGLAccount and StRpTPARServiceType.ToGLAccount
                                                                                                  and (
                                                                                                     StRpTPARServiceType.AU_TPARServiceType                    =       'P'

                                                                                                     or(
                                                                                                       (
                                                                                                         StRpTPARServiceType.AU_TPARServiceType                =       'G'
                                                                                                       )
                                                                                                       and(
                                                                                                         (
                                                                                                           StRpTPARServiceType.AU_TPARGrantHasConfignObject    =       'W'
                                                                                                           and JournalEntryItemCube.WBSElementInternalID       =       StRpTPARServiceType.WBSElementInternalIDCharFormat
                                                                                                         )
                                                                                                         or(
                                                                                                           StRpTPARServiceType.AU_TPARGrantHasConfignObject    =       'I'
                                                                                                           and JournalEntryItemCube.OrderID                    =       StRpTPARServiceType.AU_TPARGrantObject
                                                                                                         )

                                                                                                         or(
                                                                                                           StRpTPARServiceType.AU_TPARGrantHasConfignObject    is null
                                                                                                           or StRpTPARServiceType.AU_TPARGrantHasConfignObject is initial
                                                                                                         )
                                                                                                       )
                                                                                                     )
                                                                                                   )
  // Removal of accounting documents present in with_item.

    left outer to many join I_Withholdingtaxitem                       as Withholdingtaxitem      on  StRpTPARSuplrAcctgDocItm.CompanyCode        = Withholdingtaxitem.CompanyCode
                                                                                                  and StRpTPARSuplrAcctgDocItm.AccountingDocument = Withholdingtaxitem.AccountingDocument
                                                                                                  and StRpTPARSuplrAcctgDocItm.FiscalYear         = Withholdingtaxitem.FiscalYear
{
  key        StRpTPARSuplrAcctgDocItm.CompanyCode,
  key        StRpTPARSuplrAcctgDocItm.AccountingDocument,
  key        StRpTPARSuplrAcctgDocItm.FiscalYear,
  key        OperationalAcctgDocItem.AccountingDocumentItem,
             JournalEntryItemCube.GLAccount,
             StRpTPARSuplrAcctgDocItm.Supplier,
             StRpTPARSuplrAcctgDocItm.ReportingDate,
             StRpTPARSuplrAcctgDocItm.TotalGrossAmountInCoCodeCrcy,
             StRpTPARServiceType.AU_TPARServiceType,
             StRpTPARServiceType.AU_TPARGrantName,
             StRpTPARSuplrAcctgDocItm.ClearingJournalEntry,
             StRpTPARSuplrAcctgDocItm.PaymentDocument,
             cast( case when StRpTPARServiceType.AU_TPARServiceType = 'G'
             then
               case
                  when StRpTPARServiceType.AU_TPARGrantHasConfignObject = 'I' then JournalEntryItemCube.OrderID
//                  when StRpTPARServiceType.AU_TPARGrantHasConfignObject = 'W' then OperationalAcctgDocItem.WBSElementInternalID

                  when StRpTPARServiceType.AU_TPARGrantHasConfignObject = 'W' then StRpTPARServiceType.AU_TPARGrantObject
//                  when StRpTPARServiceType.AU_TPARGrantHasConfignObject is null or StRpTPARServiceType.AU_TPARGrantHasConfignObject is initial

//                  then ' ' // when G/L account is used to identifying Grants

                  else ' ' // when G/L account is used to identifying Grants

               end
             else ' '
             end as fiau_tpar_grant_object ) as AU_TPARGrantObject
}
where
      OperationalAcctgDocItem.FinancialAccountType       =  'S'
  and JournalEntryItemCube.FinancialAccountType          =  'S'
  and OperationalAcctgDocItem.AccountingDocumentItemType <> 'T'
  and Supplier.IsOneTimeAccount                          is initial //one time vendors are removed

  and Withholdingtaxitem.AccountingDocument              is null //remove those documents which are present in with_item