@AbapCatalog.sqlViewName: 'PTRRMONBASE'
@VDM.viewType: #COMPOSITE
@VDM.private:true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_Trrmonbase
with parameters
P_ToFiscalYearPeriod : fis_tojahrper_c
as
select from I_WBSElement as WBSElement
inner join P_TRRMONWBSUNION as wbsunion on wbsunion.WBSElementInternalID = WBSElement.WBSElementInternalID
inner join tkkaa as tkkaa on tkkaa.abgsl = WBSElement.ResultAnalysisInternalID
inner join I_GLAccountLineItem as semtag on semtag.WBSElementInternalID = wbsunion.WBSElementIntIDUnion
and semtag.ProjectInternalID = WBSElement.ProjectInternalID
and semtag.CompanyCode = WBSElement.CompanyCode
inner join I_SemTagGLAccount as semTagGl on semTagGl.GLAccount = semtag.GLAccount
and semTagGl.ChartOfAccounts = semtag.ChartOfAccounts
inner join I_EvtBsdRevnRecgnEvalScp as evalscp on evalscp.CompanyCode = semtag.CompanyCode
and evalscp.Ledger = semtag.Ledger
left outer to one join I_EvtBsdRevnRecgnObjLifeCycle as lifecyc on lifecyc.CompanyCode = WBSElement.CompanyCode
and lifecyc.WBSElementInternalID = WBSElement.WBSElementInternalID
and lifecyc.Ledger = semtag.Ledger
{
key WBSElement.ProjectInternalID as ProjectInternalID,
key cast ( WBSElement.WBSElementInternalID as fins_trr_wbselementinternalid preserving type ) as WBSElementInternalID,
key semtag.Ledger as Ledger,
key semtag.CompanyCode as CompanyCode,
WBSElement.WBSElementObject as WBSElementObject,
cast ( WBSElement.Project as ps_pspid_edit preserving type ) as ProjectExternalID,
WBSElement._Project.ProjectDescription as ProjectDescription,
cast ( WBSElement.WBSElement as ps_posid_edit preserving type ) as WBSElementExternalID,
cast ( WBSElement.ResultAnalysisInternalID as fins_trr_recgnkey preserving type ) as ResultAnalysisInternalID,
cast ( '000' as fins_fiscalperiod preserving type ) as FiscalPeriod,
cast ( '0000' as fis_gjahr preserving type) as FiscalYear,
cast ( semtag.FiscalYearPeriod as fis_jahrper_conv preserving type ) as ToFiscalYearPeriod,
cast ( WBSElement.ResponsiblePerson as fins_trr_project_manager preserving type ) as ProjectManager,
cast ( WBSElement.ResponsiblePersonName as fins_trr_project_manager_name preserving type ) as ProjectManagerName,
cast ( WBSElement.WBSDescription as fins_trr_wbs_description preserving type ) as WBSElementDescription,
semtag.CompanyCodeCurrency as CompanyCodeCurrency,
cast ( lifecyc.PostingFiscalPeriod as fis_jahrper_conv preserving type ) as PostingFiscalPeriod,
cast ( lifecyc.PostingDate as fins_trr_last_evaldat preserving type ) as PostingDate,
// DecoupleDate,
sum(
case when semtag.BusinessTransactionType <> 'TBRR' and semTagGl.SemanticTag = 'SALES_DED'
then cast ( -AmountInCompanyCodeCurrency as fins_trr_slsded preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_slsded preserving type ) end ) as ActlSlsDeductionAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType <> 'TBRR' and semTagGl.SemanticTag = 'ACT_COST'
then cast ( -AmountInCompanyCodeCurrency as fins_trr_actcost preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_actcost preserving type ) end ) as RecognizableCostAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType <> 'TBRR' and semTagGl.SemanticTag = 'BILL_REV'
then cast ( -AmountInCompanyCodeCurrency as fins_trr_actrevn preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_actrevn preserving type ) end ) as RecognizableRevenueAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType = 'TBRR' and semTagGl.SemanticTag = 'ADJ_REV'
then cast ( -AmountInCompanyCodeCurrency as fins_trr_revnadjust preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_revnadjust preserving type) end ) as RevenueAdjustmentAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType = 'TBRR' and semTagGl.SemanticTag = 'ADJ_COS'
then cast ( -AmountInCompanyCodeCurrency as fins_trr_cogsadj preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_cogsadj preserving type ) end ) as COGSAdjustmentAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType = 'TBRR' and semTagGl.SemanticTag = 'DEF_COST'
then cast (AmountInCompanyCodeCurrency as fins_trr_defcogs preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_defcogs preserving type ) end ) as DeferredCOGSAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType = 'TBRR' and semTagGl.SemanticTag = 'DEF_REV'
then cast (AmountInCompanyCodeCurrency as fins_trr_defrevn preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_defrevn preserving type ) end ) as DeferredRevenueAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType = 'TBRR' and semTagGl.SemanticTag = 'ACR_REV'
then cast (AmountInCompanyCodeCurrency as fins_trr_accrevn preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_accrevn preserving type ) end ) as AccruedRevenueAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType = 'TBRR' and semTagGl.SemanticTag = 'ACR_COST'
then cast (AmountInCompanyCodeCurrency as fins_trr_acccogs preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_acccogs preserving type ) end ) as AccruedCOGSAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType = 'TBRR' and semTagGl.SemanticTag = 'RES_MIL'
then cast (AmountInCompanyCodeCurrency as fins_trr_resimmloss preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_resimmloss preserving type) end ) as ImminentLossRsrvAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType = 'TBRR' and semTagGl.SemanticTag = 'RES_MASD'
then cast (AmountInCompanyCodeCurrency as fins_trr_ressalesded preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_ressalesded preserving type ) end ) as SalesDeductReserveAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType = 'TBRR' and semTagGl.SemanticTag = 'RES_MUC'
then cast (AmountInCompanyCodeCurrency as fins_trr_unrealcosts preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_unrealcosts preserving type ) end ) as UnrlzdCostsReserveAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType = 'TBRR' and semTagGl.SemanticTag = 'UNBILL_REV'
then cast (AmountInCompanyCodeCurrency as fins_trr_unbilledrevenue preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_unbilledrevenue preserving type ) end ) as UnbilledRevnInCoCodeCrcy,
sum(
case when semtag.BusinessTransactionType = 'TBRR' and semTagGl.SemanticTag = 'MCONTR_PL'
then cast ( -AmountInCompanyCodeCurrency as fins_trr_cntraccrpul_cccrcy preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_cntraccrpul_cccrcy preserving type ) end ) as MnlContrAccrPnLItmAmtInCCCrcy,
sum(
case when semtag.BusinessTransactionType = 'TBRR' and semTagGl.SemanticTag = 'MCONTR_BS'
then cast (AmountInCompanyCodeCurrency as fins_trr_cntraccrbalsht_cccrcy preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_cntraccrbalsht_cccrcy preserving type ) end ) as MnlContrAccrBalShtAmtInCCCrcy,
0 as PlannedCosAmtInCCCrcy,
0 as PlannedRevenueAmtInCCCrcy,
sum(
case when SemanticTag = 'RECO_COS'
then cast ( -AmountInCompanyCodeCurrency as fins_trr_reccogs preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_reccogs preserving type ) end ) as RecognizedCOGSAmtInCCCrcy,
sum(
case when SemanticTag = 'RECO_REV'
then cast ( -AmountInCompanyCodeCurrency as fins_trr_recrevn preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_recrevn preserving type) end ) as RecognizedRevnAmtInCCCrcy,
sum(
case when SemanticTag = 'REC_MARGIN'
then cast ( -AmountInCompanyCodeCurrency as fins_trr_recmargin preserving type )
else cast ( cast ('0' as abap.curr( 23,2)) as fins_trr_recmargin preserving type ) end ) as RecognizedMarginAmtInCCCrcy,
_Ledger
// _CompanyCode
}
where
semtag.AccountAssignmentType = 'PR'
and semTagGl.ValidityStartDate <= $session.system_date
and semTagGl.ValidityEndDate >= $session.system_date
and semtag.FiscalPeriod <> '000'
and semtag.WBSElementInternalID <> '00000000'
and semtag.ProjectInternalID <> '00000000'
and WBSElement.ResultAnalysisInternalID <> ' '
and semtag.FiscalYearPeriod <= $parameters.P_ToFiscalYearPeriod
and semTagGl.GLAccountHierarchy = evalscp.GLAccountHierarchy
and tkkaa.rflg1 = 'X'
group by
WBSElement.ProjectInternalID,
WBSElement.WBSElementInternalID,
WBSElement.ResponsiblePerson,
WBSElement.ResponsiblePersonName,
WBSElement.ResultAnalysisInternalID,
WBSElement.WBSDescription,
WBSElement._Project.ProjectDescription,
WBSElement.Project,
WBSElement.WBSElement,
WBSElement.WBSElementObject,
semtag.CompanyCode,
semtag.CompanyCodeCurrency,
semtag.Ledger,
semtag.FiscalYearPeriod,
lifecyc.PostingFiscalPeriod,
lifecyc.PostingDate
// lifecyc.DecoupleDate
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_EVTBSDREVNRECGNEVALSCP",
"I_EVTBSDREVNRECGNOBJLIFECYCLE",
"I_GLACCOUNTLINEITEM",
"I_PROJECTBYINTERNALKEY",
"I_SEMTAGGLACCOUNT",
"I_WBSELEMENT",
"P_TRRMONWBSUNION",
"TKKAA"
],
"ASSOCIATED":
[
"I_LEDGER"
],
"BASE":
[
"I_GLACCOUNTLINEITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/