@AbapCatalog.sqlViewName: 'PCSTCTRCMTMT2'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #COMPOSITE
@VDM.private: true
//@EndUserText.label: 'Cost Center Commitment - Private'
define view P_CostCenterCommitmentUnion_2
with parameters
P_KeyDate : vdm_v_key_date
as select from I_FinancialPlanningEntryItem as _FinancialPlanningEntryItem
association [0..1] to I_Supplier as _Supplier on $projection.Supplier = _Supplier.Supplier
association [0..1] to I_SrvcDocByDocumentType as _ServiceDocument on $projection.ServiceDocumentType = _ServiceDocument.ServiceDocumentType
and $projection.ServiceDocument = _ServiceDocument.ServiceDocument
association [0..1] to I_AccountingDocumentType as _AccountingDocumentType on $projection.AccountingDocumentType = _AccountingDocumentType.AccountingDocumentType
{
key FinancialPlanningReqTransSqnc,
key FinancialPlanningDataPacket,
key FinancialPlanningEntryItem,
key cast('' as fis_docln) as LedgerGLLineItem,
key Ledger,
key cast('' as fis_belnr) as AccountingDocument,
key ControllingArea,
key CompanyCode,
key FiscalYear,
key CostCenter,
key GLAccount,
FiscalYearVariant,
FiscalPeriod,
cast('' as fins_ledger_pers) as SourceLedger,
cast('' as md_supplier) as Supplier,
Product as Material,
cast(0 as quan1) as CommittedQuantity,
ValuationQuantity as PlannedQuantity,
cast(0 as quan1_12) as Quantity,
cast('' as co_meinh) as UnitOfMeasure,
ChartOfAccounts,
cast('' as fis_ebeln) as PurchasingDocument,
cast('00000' as fis_ebelp) as PurchasingDocumentItem,
cast('' as esart) as PurchasingDocumentType,
cast('' as ebstyp) as PurchasingDocumentCategory,
PostingDate,
PlanningCategory,
cast('' as fis_src_awref) as SourceReferenceDocument,
cast('000000' as fis_src_awitem) as SourceReferenceDocumentItem,
CompanyCodeCurrency,
GlobalCurrency,
cast('' as fins_currfc) as FunctionalCurrency,
FreeDefinedCurrency1,
FreeDefinedCurrency2,
FreeDefinedCurrency3,
FreeDefinedCurrency4,
FreeDefinedCurrency5,
FreeDefinedCurrency6,
FreeDefinedCurrency7,
FreeDefinedCurrency8,
cast(0 as fis_vfccur12) as ActualAmountInFunctionalCrcy,
cast(0 as fis_vfccur12) as CmtmtAmountInFunctionalCrcy,
cast(0 as fis_act_osl) as ActualAmountInFreeDfndCrcy1,
cast(0 as fis_act_vsl) as ActualAmountInFreeDfndCrcy2,
cast(0 as fis_act_bsl) as ActualAmountInFreeDfndCrcy3,
cast(0 as fis_act_csl) as ActualAmountInFreeDfndCrcy4,
cast(0 as fis_act_dsl) as ActualAmountInFreeDfndCrcy5,
cast(0 as fis_act_esl) as ActualAmountInFreeDfndCrcy6,
cast(0 as fis_act_fsl) as ActualAmountInFreeDfndCrcy7,
cast(0 as fis_act_gsl) as ActualAmountInFreeDfndCrcy8,
cast(0 as fis_cmtmt_osl) as CmtmtAmountInFreeDfndCrcy1,
cast(0 as fis_cmtmt_vsl) as CmtmtAmountInFreeDfndCrcy2,
cast(0 as fis_cmtmt_bsl) as CmtmtAmountInFreeDfndCrcy3,
cast(0 as fis_cmtmt_csl) as CmtmtAmountInFreeDfndCrcy4,
cast(0 as fis_cmtmt_dsl) as CmtmtAmountInFreeDfndCrcy5,
cast(0 as fis_cmtmt_esl) as CmtmtAmountInFreeDfndCrcy6,
cast(0 as fis_cmtmt_fsl) as CmtmtAmountInFreeDfndCrcy7,
cast(0 as fis_cmtmt_gsl) as CmtmtAmountInFreeDfndCrcy8,
cast(AmountInFreeDefinedCurrency1 as fis_plan_osl) as PlanAmountInFreeDefinedCrcy1,
cast(AmountInFreeDefinedCurrency2 as fis_plan_vsl) as PlanAmountInFreeDefinedCrcy2,
cast(AmountInFreeDefinedCurrency3 as fis_plan_bsl) as PlanAmountInFreeDefinedCrcy3,
cast(AmountInFreeDefinedCurrency4 as fis_plan_csl) as PlanAmountInFreeDefinedCrcy4,
cast(AmountInFreeDefinedCurrency5 as fis_plan_dsl) as PlanAmountInFreeDefinedCrcy5,
cast(AmountInFreeDefinedCurrency6 as fis_plan_esl) as PlanAmountInFreeDefinedCrcy6,
cast(AmountInFreeDefinedCurrency7 as fis_plan_fsl) as PlanAmountInFreeDefinedCrcy7,
cast(AmountInFreeDefinedCurrency8 as fis_plan_gsl) as PlanAmountInFreeDefinedCrcy8,
cast(0 as fis_hsl) as ActualAmountInCompanyCodeCrcy,
cast(0 as fis_ksl) as ActualAmountInGlobalCurrency,
cast(0 as fis_hsl) as CmtmtAmtInCompanyCodeCurrency,
cast(0 as fis_ksl) as CmtmtAmountInGlobalCurrency,
cast(AmountInCompanyCodeCurrency as fis_hsl) as PlanAmountInCompanyCodeCrcy,
cast(AmountInGlobalCurrency as fis_ksl) as PlanAmountInGlobalCurrency,
cast('PLN' as costcenterdocumentsource) as CostCenterDocumentSource,
AccountAssignmentType,
cast('' as fis_accasty_n1) as StstclAccountAssignmentType1,
cast('' as fis_accasty_n2) as StstclAccountAssignmentType2,
cast('' as fis_accasty_n3) as StstclAccountAssignmentType3,
cast('' as fins_xkostl_stat) as IsStatisticalCostCenter,
//for DCL
cast('' as bwkey) as ValuationArea,
cast('' as fco_srvdoc_type) as ServiceDocumentType,
Segment,
SalesOrganization,
ProfitCenter,
Plant,
OrganizationDivision,
cast('' as farp_koart) as FinancialAccountType,
DistributionChannel,
BusinessArea,
AssetClass,
cast('' as fco_srvdoc_id) as ServiceDocument,
cast('' as fis_blart) as AccountingDocumentType,
SalesDocument,
OrderID,
Customer,
FunctionalArea,
_Supplier,
_ServiceDocument,
_SalesDocument,
_Order,
_GLAccountInCompanyCode,
_Customer,
_AccountingDocumentType
}
where CostCenter != ''
union all select from I_GLAccountLineItemRawData as _GLAccountLineItem
association [0..1] to I_PurchasingDocument as _PurchasingDocument on $projection.PurchasingDocument = _PurchasingDocument.PurchasingDocument
association [0..1] to I_CostCenter as _CostCenter on $projection.ControllingArea = _CostCenter.ControllingArea
and $projection.CostCenter = _CostCenter.CostCenter
and _CostCenter.ValidityStartDate <= $parameters.P_KeyDate
and _CostCenter.ValidityEndDate >= $parameters.P_KeyDate
{
key cast('00000000000000000000000' as rspm_request_tsn) as FinancialPlanningReqTransSqnc,
key cast('000000' as rsdatapid) as FinancialPlanningDataPacket,
key cast(0 as rsarecord) as FinancialPlanningEntryItem,
key LedgerGLLineItem,
key cast(SourceLedger as fis_rldnr) as Ledger,
key AccountingDocument,
key ControllingArea,
key CompanyCode,
key FiscalYear,
key CostCenter,
key GLAccount,
FiscalYearVariant,
FiscalPeriod,
SourceLedger,
Supplier,
Product as Material,
cast(case IsCommitment
when 'X' then Quantity
else 0
end as quan1) as CommittedQuantity,
cast(0 as vquan1_12 ) as PlannedQuantity,
cast(case IsCommitment
when 'X' then 0
else Quantity
end as quan1_12) as Quantity,
BaseUnit as UnitOfMeasure,
ChartOfAccounts,
PurchasingDocument,
PurchasingDocumentItem,
_PurchasingDocument.PurchasingDocumentType,
_PurchasingDocument.PurchasingDocumentCategory,
PostingDate,
cast('' as fis_category) as PlanningCategory,
SourceReferenceDocument,
SourceReferenceDocumentItem,
CompanyCodeCurrency,
GlobalCurrency,
FunctionalCurrency,
FreeDefinedCurrency1,
FreeDefinedCurrency2,
FreeDefinedCurrency3,
FreeDefinedCurrency4,
FreeDefinedCurrency5,
FreeDefinedCurrency6,
FreeDefinedCurrency7,
FreeDefinedCurrency8,
cast(case IsCommitment
when 'X' then 0
else AmountInFunctionalCurrency
end as fis_vfccur12 ) as ActualAmountInFunctionalCrcy,
cast(case IsCommitment
when 'X' then AmountInFunctionalCurrency
else 0
end as fis_vfccur12 ) as CmtmtAmountInFunctionalCrcy,
cast(case IsCommitment
when 'X' then 0
else AmountInFreeDefinedCurrency1
end as fis_act_osl) as ActualAmountInFreeDfndCrcy1,
cast(case IsCommitment
when 'X' then 0
else AmountInFreeDefinedCurrency2
end as fis_act_vsl) as ActualAmountInFreeDfndCrcy2,
cast(case IsCommitment
when 'X' then 0
else AmountInFreeDefinedCurrency3
end as fis_act_bsl) as ActualAmountInFreeDfndCrcy3,
cast(case IsCommitment
when 'X' then 0
else AmountInFreeDefinedCurrency4
end as fis_act_csl) as ActualAmountInFreeDfndCrcy4,
cast(case IsCommitment
when 'X' then 0
else AmountInFreeDefinedCurrency5
end as fis_act_dsl) as ActualAmountInFreeDfndCrcy5,
cast(case IsCommitment
when 'X' then 0
else AmountInFreeDefinedCurrency6
end as fis_act_esl) as ActualAmountInFreeDfndCrcy6,
cast(case IsCommitment
when 'X' then 0
else AmountInFreeDefinedCurrency7
end as fis_act_fsl) as ActualAmountInFreeDfndCrcy7,
cast(case IsCommitment
when 'X' then 0
else AmountInFreeDefinedCurrency8
end as fis_act_gsl) as ActualAmountInFreeDfndCrcy8,
cast(case IsCommitment
when 'X' then AmountInFreeDefinedCurrency1
else 0
end as fis_act_osl) as CmtmtAmountInFreeDfndCrcy1,
cast(case IsCommitment
when 'X' then AmountInFreeDefinedCurrency2
else 0
end as fis_act_vsl) as CmtmtAmountInFreeDfndCrcy2,
cast(case IsCommitment
when 'X' then AmountInFreeDefinedCurrency3
else 0
end as fis_act_bsl) as CmtmtAmountInFreeDfndCrcy3,
cast(case IsCommitment
when 'X' then AmountInFreeDefinedCurrency4
else 0
end as fis_act_csl) as CmtmtAmountInFreeDfndCrcy4,
cast(case IsCommitment
when 'X' then AmountInFreeDefinedCurrency5
else 0
end as fis_act_dsl) as CmtmtAmountInFreeDfndCrcy5,
cast(case IsCommitment
when 'X' then AmountInFreeDefinedCurrency6
else 0
end as fis_act_esl) as CmtmtAmountInFreeDfndCrcy6,
cast(case IsCommitment
when 'X' then AmountInFreeDefinedCurrency7
else 0
end as fis_act_fsl) as CmtmtAmountInFreeDfndCrcy7,
cast(case IsCommitment
when 'X' then AmountInFreeDefinedCurrency8
else 0
end as fis_act_gsl) as CmtmtAmountInFreeDfndCrcy8,
cast(0 as fis_plan_osl) as PlanAmountInFreeDefinedCrcy1,
cast(0 as fis_plan_vsl) as PlanAmountInFreeDefinedCrcy2,
cast(0 as fis_plan_bsl) as PlanAmountInFreeDefinedCrcy3,
cast(0 as fis_plan_csl) as PlanAmountInFreeDefinedCrcy4,
cast(0 as fis_plan_dsl) as PlanAmountInFreeDefinedCrcy5,
cast(0 as fis_plan_esl) as PlanAmountInFreeDefinedCrcy6,
cast(0 as fis_plan_fsl) as PlanAmountInFreeDefinedCrcy7,
cast(0 as fis_plan_gsl) as PlanAmountInFreeDefinedCrcy8,
cast(case IsCommitment
when 'X' then 0
else AmountInCompanyCodeCurrency
end as fis_hsl) as ActualAmountInCompanyCodeCrcy,
cast(case IsCommitment
when 'X' then 0
else AmountInGlobalCurrency
end as fis_ksl) as ActualAmountInGlobalCurrency,
cast(case IsCommitment
when 'X' then AmountInCompanyCodeCurrency
else 0
end as fis_hsl) as CmtmtAmtInCompanyCodeCurrency,
cast(case IsCommitment
when 'X' then AmountInGlobalCurrency
else 0
end as fis_ksl) as CmtmtAmountInGlobalCurrency,
cast(0 as fis_hsl) as PlanAmountInCompanyCodeCrcy,
cast(0 as fis_ksl) as PlanAmountInGlobalCurrency,
cast(case IsCommitment
when 'X' then 'CMT'
else 'ACT'
end as costcenterdocumentsource) as CostCenterDocumentSource,
AccountAssignmentType,
StstclAccountAssignmentType1,
StstclAccountAssignmentType2,
StstclAccountAssignmentType3,
cast( (
case when AccountAssignmentType = 'KL' or AccountAssignmentType = 'KS'
then ''
else 'X'
end )
as fins_xkostl_stat preserving type ) as IsStatisticalCostCenter,
//for DCL
ValuationArea,
ServiceDocumentType,
Segment,
SalesOrganization,
ProfitCenter,
Plant,
OrganizationDivision,
FinancialAccountType,
DistributionChannel,
BusinessArea,
AssetClass,
ServiceDocument,
AccountingDocumentType,
SalesDocument,
OrderID,
Customer,
FunctionalArea,
_Supplier,
_ServiceDocument,
_SalesDocument,
_Order,
_GLAccountInCompanyCode,
_Customer,
_AccountingDocumentType
}
where (GLAccountType = 'P' or GLAccountType = 'S')
//and CostCenter <> ''
and (
(AccountAssignmentType = 'KS' or AccountAssignmentType = 'KL')
or
( (StstclAccountAssignmentType1 = 'KS') or (StstclAccountAssignmentType1 = 'KS') or (StstclAccountAssignmentType1 = 'KS') )
)
and ( (IsCommitment = 'X' and _CostCenter.IsBlockedForCommitmentPosting = '' ) or IsCommitment = '' )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_COSTCENTER",
"I_FINANCIALPLANNINGENTRYITEM",
"I_GLACCOUNTLINEITEMRAWDATA",
"I_PURCHASINGDOCUMENT"
],
"ASSOCIATED":
[
"I_ACCOUNTINGDOCUMENTTYPE",
"I_CUSTOMER",
"I_GLACCOUNTINCOMPANYCODE",
"I_ORDER",
"I_SALESDOCUMENT",
"I_SRVCDOCBYDOCUMENTTYPE",
"I_SUPPLIER"
],
"BASE":
[
"I_FINANCIALPLANNINGENTRYITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/