P_RO_SAFTPurInvItem

@AbapCatalog.sqlViewName: 'PROSAFTPURINVI'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@VDM.viewType: #CONSUMPTION
@VDM.private:true
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #NOT_REQUIRED
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view P_RO_SAFTPurInvItem
  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 Acdoca             on  Acdoca.AccountingDocument = Log.AccountingDocument
                                                                                                                                                      and Acdoca.CompanyCode        = Log.CompanyCode
                                                                                                                                                      and Acdoca.FiscalYear         = Log.FiscalYear

    inner join             I_AccountingDocument                                                                                 as Bkpf               on  Bkpf.AccountingDocument     = Acdoca.AccountingDocument
                                                                                                                                                      and Bkpf.CompanyCode            = Acdoca.CompanyCode
                                                                                                                                                      and Bkpf.FiscalYear             = Acdoca.FiscalYear
                                                                                                                                                      and Bkpf.PostingDate            = Acdoca.PostingDate
                                                                                                                                                      and Bkpf.AccountingDocumentType = Acdoca.AccountingDocumentType

    inner join             I_RO_SAFTDocumentTypeMap                                                                             as DocType            on  DocType.AccountingDocumentType = Acdoca.AccountingDocumentType
                                                                                                                                                      and DocType.CompanyCode            = Acdoca.CompanyCode


    inner join             I_OperationalAcctgDocItem                                                                            as Bseg               on  Bseg.AccountingDocument     = Acdoca.AccountingDocument
                                                                                                                                                      and Bseg.CompanyCode            = Acdoca.CompanyCode
                                                                                                                                                      and Bseg.FiscalYear             = Acdoca.FiscalYear
                                                                                                                                                      and Bseg.AccountingDocumentItem = Acdoca.AccountingDocumentItem
                                                                                                                                                      and Bseg.PostingDate            = Acdoca.PostingDate
                                                                                                                                                      and Bseg.AccountingDocumentType = Acdoca.AccountingDocumentType

    inner join             I_CompanyCode                                                                                        as CompanyCode        on CompanyCode.CompanyCode = Bkpf.CompanyCode

    left outer to one join P_ITEM_ONLINE                                                                                        as rseg               on  Acdoca.ReferenceDocument        = rseg.SupplierInvoice
                                                                                                                                                      and Acdoca.ReferenceDocumentContext = rseg.FiscalYear
                                                                                                                                                      and Acdoca.ReferenceDocumentItem    = rseg.SupplierInvoiceItem

    left outer to one join I_Product                                                                                            as Product            on Product.Product = Acdoca.Product

    left outer to one join I_RO_SAFTGoodsService                                                                                as GoodsService       on Product.ProductType = GoodsService.MaterialType

    left outer to one join I_RO_SAFTUnitOfMeasureMap                                                                            as UoM                on Acdoca.BaseUnit = UoM.SourceUnitOfMeasure

    left outer to one join I_RO_SAFTUnitOfMeasureMap                                                                            as RUoM               on Acdoca.ReferenceQuantityUnit = RUoM.SourceUnitOfMeasure

    left outer to one join I_RO_SAFTUnitOfMeasureMap                                                                            as BUoM               on Bseg.BaseUnit = BUoM.SourceUnitOfMeasure

    left outer to one join I_RO_SAFTUnitOfMeasureMap                                                                            as RsUoM              on rseg.PurchaseOrderPriceUnit = RsUoM.SourceUnitOfMeasure

    left outer to one join I_ProductText                                                                                        as ProductText        on  Acdoca.Product       = ProductText.Product
                                                                                                                                                      and ProductText.Language = '4'

    left outer to one join I_ProductText                                                                                        as ProductTextE       on  Acdoca.Product        = ProductTextE.Product
                                                                                                                                                      and ProductTextE.Language = 'E'

    left outer to one join I_RO_SAFTSpecialGLIndicator                                                                          as SpecialGLIndicator on  Acdoca.CompanyCode          = SpecialGLIndicator.CompanyCode
                                                                                                                                                      and Acdoca.FinancialAccountType = SpecialGLIndicator.AccountType
                                                                                                                                                      and Acdoca.SpecialGLCode        = SpecialGLIndicator.SpecialGLCode

    left outer to one join I_Currency                                                                                           as TCurrency          on TCurrency.Currency = Acdoca.TransactionCurrency

{
  key Log.StatryRptCategory,
  key Log.StatryRptgEntity,
  key Log.StatryRptRunID,
  key Acdoca.SourceLedger,
  key Acdoca.Ledger,
  key Acdoca.CompanyCode,
  key Acdoca.FiscalYear,
  key Acdoca.AccountingDocument,
  key Acdoca.LedgerGLLineItem,
      Acdoca.AccountingDocumentItem,
      Acdoca.AccountingDocumentType,
      Acdoca.Product,
      case
        when Acdoca.BaseUnit <> '' then Acdoca.BaseUnit
        when Acdoca.ReferenceQuantityUnit <> '' then Acdoca.ReferenceQuantityUnit
        when Bseg.BaseUnit <> '' then Bseg.BaseUnit
        when rseg.PurchaseOrderPriceUnit is not null and rseg.PurchaseOrderPriceUnit <> '' then rseg.PurchaseOrderPriceUnit
        else cast('' as  meins ) end                                                                                                                               as BaseUnit,
      Acdoca.TransactionTypeDetermination,
      Acdoca.PostingDate,
      @Semantics.currencyCode:true
      Acdoca.CompanyCodeCurrency,
      @Semantics.currencyCode:true
      Acdoca.TransactionCurrency,
      Bseg.IsNegativePosting,
      GoodsService.RO_SAFTGoodsService,
      coalesce(ProductText.ProductName, ProductTextE.ProductName)                                                                                                  as ProductDescription,
      coalesce(UoM.SourceUnitOfMeasureFactor, coalesce(RUoM.SourceUnitOfMeasureFactor, coalesce(BUoM.SourceUnitOfMeasureFactor, RsUoM.SourceUnitOfMeasureFactor))) as SourceUnitOfMeasureFactor,

      // Negative posting is already considered in P_Acdoca_COM1 and the indicator

      // is also adjusted there accordingly thus there is no need to do it here

      case Acdoca.DebitCreditCode
        when 'H' then 'C'
        else 'D'
      end                                                                                                                                                          as DebitCreditCode,

      0                                                                                                                                                            as ConversionFactor,

      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      case when Bseg.OriglTaxBaseAmountInCoCodeCrcy <> 0 then
        case when  Bseg.IsNegativePosting = 'X'
          then -1 * abs(Bseg.OriglTaxBaseAmountInCoCodeCrcy)
          else abs(Bseg.OriglTaxBaseAmountInCoCodeCrcy) end
        when Acdoca.DebitCreditCode = 'H' then -1 * Acdoca.AmountInCompanyCodeCurrency
        else Acdoca.AmountInCompanyCodeCurrency
      end                                                                                                                                                          as AmountInCompanyCodeCurrency,

      @Semantics.amount.currencyCode: 'TransactionCurrency'
      case when Bseg.OriginalTaxBaseAmount <> 0 then
         case when Bseg.IsNegativePosting = 'X'
           then -1 * abs(Bseg.OriginalTaxBaseAmount)
           else abs(Bseg.OriginalTaxBaseAmount) end
         when Acdoca.DebitCreditCode = 'H' then -1 * Acdoca.AmountInTransactionCurrency
         else Acdoca.AmountInTransactionCurrency
       end                                                                                                                                                         as AmountInTransactionCurrency,


      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      case
      //quantities 0

        when Acdoca.Quantity = 0 and Acdoca.ReferenceQuantity = 0 and Bseg.Quantity = 0 and (rseg.QtyInPurchaseOrderPriceUnit = 0 or rseg.QtyInPurchaseOrderPriceUnit is null) //and (rseg.QuantityInPurchaseOrderUnit = 0 or rseg.QuantityInPurchaseOrderUnit is null)

         then
          case Acdoca.RO_SAFTIsDefaultZeroQuantity
            when 'X' then 0
            else case when Bseg.OriglTaxBaseAmountInCoCodeCrcy <> 0 then abs(Bseg.OriglTaxBaseAmountInCoCodeCrcy) else abs(Acdoca.AmountInCompanyCodeCurrency) end end
      //there is original tax base filled, use that

        when Bseg.OriglTaxBaseAmountInCoCodeCrcy <> 0 then
          case
            when Acdoca.Quantity <> 0 then abs(division(Bseg.OriglTaxBaseAmountInCoCodeCrcy, Acdoca.Quantity, 2))
            when Acdoca.ReferenceQuantity <> 0 then abs(division(Bseg.OriglTaxBaseAmountInCoCodeCrcy, Acdoca.ReferenceQuantity, 2))
            when Bseg.Quantity <> 0 then abs(division(Bseg.OriglTaxBaseAmountInCoCodeCrcy, Bseg.Quantity, 2))
            when rseg.QtyInPurchaseOrderPriceUnit <> 0 then abs(division(Bseg.OriglTaxBaseAmountInCoCodeCrcy, rseg.QtyInPurchaseOrderPriceUnit, 2))
      //            when rseg.QuantityInPurchaseOrderUnit <> 0 then abs(division(Bseg.OriglTaxBaseAmountInCoCodeCrcy, rseg.QuantityInPurchaseOrderUnit, 2))

            else 0
          end
      //original tax base not filled, use AmountInCompanyCodeCurrency

        else
          case
            when Acdoca.Quantity <> 0 then abs(division(Acdoca.AmountInCompanyCodeCurrency, Acdoca.Quantity, 2))
            when Acdoca.ReferenceQuantity <> 0 then abs(division(Acdoca.AmountInCompanyCodeCurrency, Acdoca.ReferenceQuantity, 2))
            when Bseg.Quantity <> 0 then abs(division(Acdoca.AmountInCompanyCodeCurrency, Bseg.Quantity, 2))
            when rseg.QtyInPurchaseOrderPriceUnit <> 0 then abs(division(Acdoca.AmountInCompanyCodeCurrency, rseg.QtyInPurchaseOrderPriceUnit, 2))
      //            when rseg.QuantityInPurchaseOrderUnit <> 0 then abs(division(Acdoca.AmountInCompanyCodeCurrency, rseg.QuantityInPurchaseOrderUnit, 2))

            else 0
          end
      end                                                                                                                                                          as UnitPriceAmountInReportingCrcy,

      case
        when Bkpf.TaxReportingDate is initial then Bkpf.DocumentDate
        else Bkpf.TaxReportingDate
      end                                                                                                                                                          as TaxReportingDate,

      case
        when Acdoca.DocumentItemText is not initial then Acdoca.DocumentItemText
        when ProductText.ProductName is not initial then ProductText.ProductName
        when ProductTextE.ProductName is not initial then ProductTextE.ProductName
        else cast('NULL' as farp_sgtxt)
      end                                                                                                                                                          as DocumentItemText, // Description


      @Semantics.quantity.unitOfMeasure: 'BaseUnit'
      case
        when Acdoca.Quantity <> 0 then abs(Acdoca.Quantity)
        when Acdoca.ReferenceQuantity <> 0 then abs(Acdoca.ReferenceQuantity)
        when Bseg.Quantity <> 0 then abs(Bseg.Quantity)
        when rseg.QtyInPurchaseOrderPriceUnit <> 0 then abs(rseg.QtyInPurchaseOrderPriceUnit)
      //        when rseg.QuantityInPurchaseOrderUnit <> 0 then abs(rseg.QuantityInPurchaseOrderUnit)

        else case Acdoca.RO_SAFTIsDefaultZeroQuantity
            when 'X' then cast(0 as quan1_12)
            else cast(1 as quan1_12) end
      end                                                                                                                                                          as Quantity, // Quantity


      cast(
        case
          when DocType.RO_SAFTIsExchHeaderTable = 'X' then Bkpf.ExchangeRate
          when Acdoca.AmountInTransactionCurrency <> 0 then division(Acdoca.AmountInCompanyCodeCurrency, Acdoca.AmountInTransactionCurrency, 4)
          else 0
        end
      as saft_ro_exchange_rate)                                                                                                                                    as RO_SAFTExchangeRate,
      Acdoca.ChartOfAccounts,
      Acdoca.GLAccount,
      Acdoca.CountryChartOfAccounts,
      Acdoca.AlternativeGLAccount,
      Acdoca.ActiveChartOfAccounts,
      Acdoca.ActiveGLAccount,
      Acdoca.RO_SAFTStandardAccount,
      TCurrency.Decimals                                                                                                                                           as TransactionCurrencyDecimals

}
/**
  The same WHERE condition is used in C_RO_SAFTSalesInvoiceItemC and P_RO_SAFTInvoiceItemCount.
  If you adjust it here keep in mind to update in other places as well.
**/
where
       DocType.RO_SAFTSourceDocumentType =  '03'
  and
  // exclude customer vendor lines

  (
       Acdoca.FinancialAccountType       <> 'D'
    or Acdoca.SpecialGLCode              =  SpecialGLIndicator.SpecialGLCode
  )
  and(
       Acdoca.FinancialAccountType       <> 'K'
    or Acdoca.SpecialGLCode              =  SpecialGLIndicator.SpecialGLCode
  )
  and
  // exclude VAT lines

       Bseg.AccountingDocumentItemType   <> 'T'