@AbapCatalog.sqlViewName: 'PTRRSOBASE'
@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_Trrsobase
with parameters
P_ToFiscalYearPeriod : fis_tojahrper_c
as
select from I_GLAccountLineItem as semtag
inner join I_SemTagGLAccount as semTagGl on semTagGl.GLAccount = semtag.GLAccount
and semTagGl.ChartOfAccounts = semtag.ChartOfAccounts
inner join vbap as vbap on vbap.vbeln = semtag.SalesOrder
and vbap.posnr = semtag.SalesOrderItem
inner join P_SDItemCategory as ItemCategories on vbap.pstyv = ItemCategories.Pstyv
// inner join P_SupportedItemCategory( p_client:$session.client ) as ItemCategories on vbap.pstyv = ItemCategories.pstyv
left outer to one 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.Ledger = semtag.Ledger
and lifecyc.CompanyCode = semtag.CompanyCode
and lifecyc.SalesDocument = vbap.vbeln
and lifecyc.SalesDocumentItem = vbap.posnr
inner join tkkaa as tkkaa on tkkaa.abgsl = vbap.abgrs
{
key vbap.vbeln as SalesOrder,
key vbap.posnr as SalesOrderItem,
key semtag.Ledger as Ledger,
key semtag.CompanyCode as CompanyCode,
vbap.solution_order_id as BusinessSolutionOrder,
cast ( vbap.abgrs 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,
semtag.FiscalYearPeriod as ToFiscalYearPeriod,
semtag.CompanyCodeCurrency as CompanyCodeCurrency,
cast ( vbap.arktx as fins_trr_salesord_itm_text preserving type ) as SalesOrderItemText,
cast ( lifecyc.PostingFiscalPeriod as fis_jahrper_conv preserving type ) as PostingFiscalPeriod,
lifecyc.PostingDate as PostingDate,
vbap.vkorg_ana as SalesOrganization,
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 = '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,
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 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,
_CompanyCodeCurrency
}
where
(
semtag.AccountAssignmentType = 'OR'
or semtag.AccountAssignmentType = 'EO'
)
and semTagGl.ValidityStartDate <= $session.system_date
and semTagGl.ValidityEndDate >= $session.system_date
and semTagGl.GLAccountHierarchy = evalscp.GLAccountHierarchy
and semtag.FiscalPeriod <> '000'
and vbap.abgrs <> ' '
and tkkaa.rflg1 = 'X'
and(
vbap.vbtyp_ana = 'C'
or vbap.vbtyp_ana = 'H'
)
// and(
// vbap.pstyv = 'TAN'
// or vbap.pstyv = 'TANN'
// or vbap.pstyv = 'CBEN'
// // or vbap.pstyv = 'RENV'
// or vbap.pstyv = 'CB1'
// or vbap.pstyv = 'CB2'
// or vbap.pstyv = 'CBTC'
// or vbap.pstyv = 'TAD'
// or vbap.pstyv = 'CB1C'
// or vbap.pstyv = 'CB2C'
// or vbap.pstyv = 'TAQ'
// or vbap.pstyv = 'CBXN'
// or vbap.pstyv = 'CPHD'
// or vbap.pstyv = 'CBIC'
// )
and(
vbap.fkrel = 'A'
or vbap.fkrel = 'B'
or vbap.fkrel = 'F'
or vbap.fkrel = 'K'
or vbap.fkrel = 'Y'
)
and(
vbap.kzvbr = ' '
or vbap.kzvbr = 'V'
)
and semtag.FiscalYearPeriod <= $parameters.P_ToFiscalYearPeriod
group by
vbap.vbeln,
vbap.posnr,
vbap.abgrs,
vbap.arktx,
vbap.vkorg_ana,
vbap.solution_order_id,
semtag.CompanyCode,
semtag.CompanyCodeCurrency,
semtag.Ledger,
semtag.SalesOrganization,
semtag.FiscalYearPeriod,
lifecyc.PostingFiscalPeriod,
lifecyc.PostingDate
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_EVTBSDREVNRECGNEVALSCP",
"I_EVTBSDREVNRECGNOBJLIFECYCLE",
"I_GLACCOUNTLINEITEM",
"I_SEMTAGGLACCOUNT",
"P_SDITEMCATEGORY",
"TKKAA",
"VBAP"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_CURRENCY",
"I_LEDGER"
],
"BASE":
[
"I_GLACCOUNTLINEITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/