@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Prima nota entries without EBWP posting'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #S,
dataClass: #MIXED
}
@VDM.private:true
@VDM.viewType: #COMPOSITE
define view entity P_PrimaNotaEntriesWithoutEBWP
with parameters
P_FiscalPeriodStartDate : budat,
P_FiscalPeriodEndDate : budat
as select from I_GLAccountLineItemRawData as prima_nota
//inner join I_Ledger as ledger on ledger.Ledger = prima_nota.SourceLedger
// and ledger.LedgerType = '' //standard ledger only
left outer to one join P_EventBasedTotalVariance(
P_FiscalPeriodEndDate: $parameters.P_FiscalPeriodEndDate) as total_variance on prima_nota.ControllingObject = total_variance.ControllingObject
and prima_nota.OrderID = total_variance.OrderID
and prima_nota.SourceLedger = total_variance.SourceLedger
and prima_nota.CompanyCode = total_variance.CompanyCode
left outer to one join I_GLAccountLineItemRawData as posted_wip on prima_nota.OrderID = posted_wip.OrderID
and prima_nota.OrderItem = posted_wip.OrderItem
and prima_nota.SourceLedger = posted_wip.SourceLedger
and prima_nota.CompanyCode = posted_wip.CompanyCode
and prima_nota.ReferenceDocumentType = posted_wip.ReferenceDocumentType
and prima_nota.ReferenceDocumentContext = posted_wip.ReferenceDocumentContext
and prima_nota.ReferenceDocument = posted_wip.ReferenceDocument
and (
posted_wip.SubLedgerAcctLineItemType = '09100'
or posted_wip.SubLedgerAcctLineItemType = '09110'
)
and posted_wip.PostingDate <= $parameters.P_FiscalPeriodEndDate
left outer to one join R_EvtBsdProdCostgSettlmtRef as refrence_table on prima_nota.ControllingObject = refrence_table.ControllingObject
and prima_nota.OrderID = refrence_table.OrderID
and prima_nota.SourceLedger = refrence_table.Ledger
and prima_nota.ReferenceDocumentType = refrence_table.ReferenceDocumentType
and prima_nota.ReferenceDocumentContext = refrence_table.ReferenceDocumentContext
and prima_nota.ReferenceDocument = refrence_table.ReferenceDocument
and refrence_table.PostingDate <= $parameters.P_FiscalPeriodEndDate
and refrence_table.BusinessTransactionCategory = 'EBWP'
and refrence_table.IsCleared = ''
// left outer to one join R_EvtBsdProdCostgSettlmtRef as cleared_wip on prima_nota.ControllingObject = cleared_wip.ControllingObject
// and prima_nota.OrderID = cleared_wip.OrderID
// and prima_nota.SourceLedger = cleared_wip.Ledger
// and cleared_wip.IsCleared = 'X'
// and cleared_wip.PostingDate >= $parameters.P_FiscalPeriodStartDate
// and cleared_wip.PostingDate <= $parameters.P_FiscalPeriodEndDate
// and cleared_wip.BusinessTransactionCategory = 'EBWP'
{
key prima_nota.CompanyCode,
key prima_nota.SourceLedger as Ledger,
key prima_nota.AccountingDocument,
key prima_nota.LedgerGLLineItem,
key prima_nota.FiscalYear,
prima_nota.ControllingObject,
prima_nota.ReferenceDocumentType,
prima_nota.ReferenceDocumentContext,
prima_nota.ReferenceDocument,
prima_nota.OrderID,
prima_nota.OrderItem,
prima_nota.OrderCategory,
prima_nota.GLAccount,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
prima_nota.AmountInCompanyCodeCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
prima_nota.AmountInGlobalCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency1'} }
prima_nota.AmountInFreeDefinedCurrency1,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency2'} }
prima_nota.AmountInFreeDefinedCurrency2,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency3'} }
prima_nota.AmountInFreeDefinedCurrency3,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency4'} }
prima_nota.AmountInFreeDefinedCurrency4,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency5'} }
prima_nota.AmountInFreeDefinedCurrency5,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency6'} }
prima_nota.AmountInFreeDefinedCurrency6,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency7'} }
prima_nota.AmountInFreeDefinedCurrency7,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency8'} }
prima_nota.AmountInFreeDefinedCurrency8,
prima_nota.CompanyCodeCurrency,
prima_nota.GlobalCurrency,
prima_nota.FreeDefinedCurrency1,
prima_nota.FreeDefinedCurrency2,
prima_nota.FreeDefinedCurrency3,
prima_nota.FreeDefinedCurrency4,
prima_nota.FreeDefinedCurrency5,
prima_nota.FreeDefinedCurrency6,
prima_nota.FreeDefinedCurrency7,
prima_nota.FreeDefinedCurrency8,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case
when (prima_nota.PostingDate >= $parameters.P_FiscalPeriodStartDate and prima_nota.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
prima_nota.AmountInCompanyCodeCurrency
end as PeriodAmountInCompanyCodeCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
case
when (prima_nota.PostingDate >= $parameters.P_FiscalPeriodStartDate and prima_nota.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
prima_nota.AmountInGlobalCurrency
end as PeriodAmountInGlobalCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency1'} }
case
when (prima_nota.PostingDate >= $parameters.P_FiscalPeriodStartDate and prima_nota.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
prima_nota.AmountInFreeDefinedCurrency1
end as PeriodAmountInFreeDfndCrcy1,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency2'} }
case
when (prima_nota.PostingDate >= $parameters.P_FiscalPeriodStartDate and prima_nota.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
prima_nota.AmountInFreeDefinedCurrency2
end as PeriodAmountInFreeDfndCrcy2,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency3'} }
case
when (prima_nota.PostingDate >= $parameters.P_FiscalPeriodStartDate and prima_nota.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
prima_nota.AmountInFreeDefinedCurrency3
end as PeriodAmountInFreeDfndCrcy3,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency4'} }
case
when (prima_nota.PostingDate >= $parameters.P_FiscalPeriodStartDate and prima_nota.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
prima_nota.AmountInFreeDefinedCurrency4
end as PeriodAmountInFreeDfndCrcy4,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency5'} }
case
when (prima_nota.PostingDate >= $parameters.P_FiscalPeriodStartDate and prima_nota.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
prima_nota.AmountInFreeDefinedCurrency5
end as PeriodAmountInFreeDfndCrcy5,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency6'} }
case
when (prima_nota.PostingDate >= $parameters.P_FiscalPeriodStartDate and prima_nota.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
prima_nota.AmountInFreeDefinedCurrency6
end as PeriodAmountInFreeDfndCrcy6,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency7'} }
case
when (prima_nota.PostingDate >= $parameters.P_FiscalPeriodStartDate and prima_nota.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
prima_nota.AmountInFreeDefinedCurrency7
end as PeriodAmountInFreeDfndCrcy7,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'FreeDefinedCurrency8'} }
case
when (prima_nota.PostingDate >= $parameters.P_FiscalPeriodStartDate and prima_nota.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
prima_nota.AmountInFreeDefinedCurrency8
end as PeriodAmountInFreeDfndCrcy8
}
where
(
prima_nota.AccountAssignmentType = 'OR'
or prima_nota.AccountAssignmentType = 'OP'
)
and(
prima_nota.OrderCategory = '10'
or prima_nota.OrderCategory = '40'
)
and prima_nota.BusinessTransactionCategory <> 'TBCS' //ignore TBCS posting
and prima_nota.BusinessTransactionCategory <> 'EBWP' //ignore Event-Based WIP posting
and prima_nota.OriginCtrlgDebitCreditCode <> 'A' //To be posted amount in Inspector should not consider the settlement data of open order
and prima_nota.PostingDate <= $parameters.P_FiscalPeriodEndDate
and(
(
posted_wip.ReferenceDocument is null
and refrence_table.ReferenceDocument is null
)
or total_variance.ControllingObject is not null
//or cleared_wip.ControllingObject is not null
)
//Not reversed EBVP
//union all select from P_EventBasedTotalVariance(
// P_FiscalPeriodEndDate: $parameters.P_FiscalPeriodEndDate,
// P_CompanyCode: $parameters.P_CompanyCode) as total_variance
//{
// key total_variance.CompanyCode,
// key total_variance.SourceLedger as Ledger,
// key total_variance.AccountingDocument,
// key total_variance.LedgerGLLineItem,
// key total_variance.FiscalYear,
// total_variance.ControllingObject,
// total_variance.ReferenceDocumentType,
// total_variance.ReferenceDocumentContext,
// total_variance.ReferenceDocument,
// total_variance.OrderID,
// total_variance.OrderItem,
// total_variance.OrderCategory,
// total_variance.GLAccount,
//
// -total_variance.AmountInCompanyCodeCurrency as AmountInCompanyCodeCurrency,
// -total_variance.AmountInGlobalCurrency as AmountInGlobalCurrency,
// -total_variance.AmountInFreeDefinedCurrency1 as AmountInFreeDefinedCurrency1,
// -total_variance.AmountInFreeDefinedCurrency2 as AmountInFreeDefinedCurrency2,
// -total_variance.AmountInFreeDefinedCurrency3 as AmountInFreeDefinedCurrency3,
// -total_variance.AmountInFreeDefinedCurrency4 as AmountInFreeDefinedCurrency4,
// -total_variance.AmountInFreeDefinedCurrency5 as AmountInFreeDefinedCurrency5,
// -total_variance.AmountInFreeDefinedCurrency6 as AmountInFreeDefinedCurrency6,
// -total_variance.AmountInFreeDefinedCurrency7 as AmountInFreeDefinedCurrency7,
// -total_variance.AmountInFreeDefinedCurrency8 as AmountInFreeDefinedCurrency8,
//
// total_variance.CompanyCodeCurrency,
// total_variance.GlobalCurrency,
// total_variance.FreeDefinedCurrency1,
// total_variance.FreeDefinedCurrency2,
// total_variance.FreeDefinedCurrency3,
// total_variance.FreeDefinedCurrency4,
// total_variance.FreeDefinedCurrency5,
// total_variance.FreeDefinedCurrency6,
// total_variance.FreeDefinedCurrency7,
// total_variance.FreeDefinedCurrency8,
//
// case
// when (total_variance.PostingDate >= $parameters.P_FiscalPeriodStartDate and total_variance.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
// -total_variance.AmountInCompanyCodeCurrency
// end as PeriodAmountInCompanyCodeCrcy,
//
// case
// when (total_variance.PostingDate >= $parameters.P_FiscalPeriodStartDate and total_variance.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
// -total_variance.AmountInGlobalCurrency
// end as PeriodAmountInGlobalCurrency,
//
// case
// when (total_variance.PostingDate >= $parameters.P_FiscalPeriodStartDate and total_variance.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
// -total_variance.AmountInFreeDefinedCurrency1
// end as PeriodAmountInFreeDfndCrcy1,
//
// case
// when (total_variance.PostingDate >= $parameters.P_FiscalPeriodStartDate and total_variance.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
// -total_variance.AmountInFreeDefinedCurrency2
// end as PeriodAmountInFreeDfndCrcy2,
//
// case
// when (total_variance.PostingDate >= $parameters.P_FiscalPeriodStartDate and total_variance.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
// -total_variance.AmountInFreeDefinedCurrency3
// end as PeriodAmountInFreeDfndCrcy3,
//
// case
// when (total_variance.PostingDate >= $parameters.P_FiscalPeriodStartDate and total_variance.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
// -total_variance.AmountInFreeDefinedCurrency4
// end as PeriodAmountInFreeDfndCrcy4,
//
// case
// when (total_variance.PostingDate >= $parameters.P_FiscalPeriodStartDate and total_variance.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
// -total_variance.AmountInFreeDefinedCurrency5
// end as PeriodAmountInFreeDfndCrcy5,
//
// case
// when (total_variance.PostingDate >= $parameters.P_FiscalPeriodStartDate and total_variance.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
// -total_variance.AmountInFreeDefinedCurrency6
// end as PeriodAmountInFreeDfndCrcy6,
//
// case
// when (total_variance.PostingDate >= $parameters.P_FiscalPeriodStartDate and total_variance.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
// -total_variance.AmountInFreeDefinedCurrency7
// end as PeriodAmountInFreeDfndCrcy7,
//
// case
// when (total_variance.PostingDate >= $parameters.P_FiscalPeriodStartDate and total_variance.PostingDate <= $parameters.P_FiscalPeriodEndDate) then
// -total_variance.AmountInFreeDefinedCurrency8
// end as PeriodAmountInFreeDfndCrcy8
//
//}