@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 I_AccountingDocument as Bkpf on Bkpf.AccountingDocument = Log.AccountingDocument
and Bkpf.CompanyCode = Log.CompanyCode
and Bkpf.FiscalYear = Log.FiscalYear
inner join I_RO_SAFTDocumentTypeMap as DocType on DocType.AccountingDocumentType = Bkpf.AccountingDocumentType
and DocType.CompanyCode = Log.CompanyCode
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_OperationalAcctgDocItem as Bseg on Bseg.AccountingDocument = Acdoca.AccountingDocument
and Bseg.CompanyCode = Acdoca.CompanyCode
and Bseg.FiscalYear = Acdoca.FiscalYear
and Bseg.AccountingDocumentItem = Acdoca.AccountingDocumentItem
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
{
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.DebitCreditCode = 'H' or (Bseg.DebitCreditCode = 'S' and 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.DebitCreditCode = 'H' or (Bseg.DebitCreditCode = 'S' and 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
}
/**
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'