@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.sqlViewName: 'PEPACTS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #COMPOSITE
@VDM.private:true
//@EndUserText.label: 'Project Actuals'
define view P_EngmtProjActuals
as select from I_EngmntProjAssgmtToSessionUsr as A
inner join P_EngmntProjGLAcctRawData as ProjectActuals on A.EngagementProject = ProjectActuals.Project
// Incorrect Debt on Margin Analysis App as the join as to be on ACDOCA-AWREF and BSEG-VBELN and NOT on ACDOCA-BELNR and BSEG-VBELN
left outer to one join I_ReceivablesPayablesItem as Receivables on ProjectActuals.ReferenceDocument = Receivables.BillingDocument
inner join I_Ledger as Ledger on ProjectActuals.SourceLedger = Ledger.Ledger
left outer to one join I_PersonWorkAgrmtOrglDetails as EmpOrg on ProjectActuals.PersonnelNumber = EmpOrg.PersonWorkAgreement
and ProjectActuals.Product <> ''
and ProjectActuals.MaterialGroup = 'P001'
and ProjectActuals.ControllingObjectDebitType = '1'
// and ProjectActuals.PostingDate >= EmpOrg.StartDate
// and ProjectActuals.PostingDate <= EmpOrg.EndDate
{
///////////////////////////////
// Project
//////////////////////////////
ProjectActuals.Project,
ProjectActuals.WBSElement,
ProjectActuals.ControllingObject,
ProjectActuals.WorkPackage,
ProjectActuals.WorkItem,
EmpOrg.CompanyCode as PartnerCompanyCode,
EmpOrg.CostCenter as PartnerCostCenter,
EmpOrg.StartDate,
EmpOrg.EndDate,
//ProjectActuals.PartnerCostCtrActivityType,
A.EngagementProjFinPlanUUID,
A.IsMyProject,
A.Currency,
///////////////////////////////
// Sales
//////////////////////////////
ProjectActuals.SalesDocument as SalesOrder,
ProjectActuals.SalesDocumentItem as SalesOrderItem,
ProjectActuals.Product as Material,
///////////////////////////////
// Bill
//////////////////////////////
Receivables.ClearingAccountingDocument,
///////////////////////////////
// Fin
//////////////////////////////
ProjectActuals.GLAccount,
ProjectActuals.ChartOfAccounts,
ProjectActuals.ControllingObjectDebitType,
ProjectActuals.BusinessTransactionType,
ProjectActuals.PersonnelNumber,
case
when ProjectActuals.ControllingObjectDebitType = '1' and ProjectActuals.Product <> '' and ProjectActuals.MaterialGroup = 'P001'
then
ProjectActuals.Product
when ProjectActuals.ControllingObjectDebitType = '2' and ProjectActuals.Product <> ''
then
ProjectActuals.Product
else
ProjectActuals.PartnerCostCtrActivityType
end as PartnerCostCtrActivityType,
case
when ProjectActuals.ControllingObjectDebitType = '1' and ProjectActuals.Product <> '' and ProjectActuals.MaterialGroup = 'P001'
then
ProjectActuals.Quantity
else
ProjectActuals.ValuationQuantity
end as ValuationQuantity,
case
when ProjectActuals.ControllingObjectDebitType = '1' and ProjectActuals.Product <> '' and ProjectActuals.MaterialGroup = 'P001'
then
ProjectActuals.BaseUnit
//Target Unit while calculating the Total Quantity(i.e., Billed Efforts) on Revenue Lines is always H, hence setting the base unit as H
when ProjectActuals.ControllingObjectDebitType = '2' and ProjectActuals.Product <> ''
then
cast('H' as meins)
else
ProjectActuals.CostSourceUnit
end as BaseUnit,
// ProjectActuals.document as AccountingDocument,
///////////////////////////////
// Posting Durations
//////////////////////////////
// ProjectActuals.FiscalYear,
// ProjectActuals.FiscalPeriod,
ProjectActuals.FiscalYearPeriod,
ProjectActuals.PostingDate,
substring(ProjectActuals.PostingDate,1,4) as FiscalYear,
substring (ProjectActuals.PostingDate,5,2) as FiscalPeriod,
///////////////////////////////
// Currencies and amounts
//////////////////////////////
ProjectActuals.CompanyCodeCurrency,
ProjectActuals.AmountInCompanyCodeCurrency,
case
when A.Currency = ProjectActuals.CompanyCodeCurrency
then
ProjectActuals.AmountInCompanyCodeCurrency
when A.Currency = ProjectActuals.ControllingObjectCurrency
then
ProjectActuals.AmountInObjectCurrency
when A.Currency = ProjectActuals.GlobalCurrency
then
ProjectActuals.AmountInGlobalCurrency
else
currency_conversion( amount => ProjectActuals.AmountInTransactionCurrency,
source_currency => ProjectActuals.TransactionCurrency,
target_currency => A.Currency,
exchange_rate_date => ProjectActuals.PostingDate,
exchange_rate_type => 'M',
error_handling => 'SET_TO_NULL' )
end as AmountInPlanCurrency,
///////////////////////////////
// Units and Quantities
//////////////////////////////
case
when ProjectActuals.ControllingObjectDebitType = '2'
then
unit_conversion( quantity => ProjectActuals.Quantity,
source_unit => ProjectActuals.BaseUnit,
target_unit => cast( 'H' as abap.unit(3) ),
error_handling => 'SET_TO_NULL' )
end as TotalQuantity,
//ProjectActuals.BaseUnit,
//ProjectActuals.ValuationQuantity,
//ProjectActuals.CompanyCode,
ProjectActuals.CostSourceUnit,
ProjectActuals.Quantity,
case when EmpOrg.CompanyCode <> ''
then EmpOrg.CompanyCode
else ProjectActuals.PartnerCompanyCode
end as CompanyCode,
case when EmpOrg.CostCenter <> ''
then EmpOrg.CostCenter
else ProjectActuals.PartnerCostCenter
end as CostCenter,
// ProjectActuals.PartnerCompanyCode as CompanyCode,
ProjectActuals.ControllingArea,
// ProjectActuals.PartnerCostCenter as CostCenter,
ProjectActuals.SourceLedger as Ledger,
Ledger.IsLeadingLedger as IsLeadingLedger,
A.EngagementProjectCategory,
A.EngagementProjectServiceOrg as CustomerProjectServiceOrg,
A.Customer,
A.ProjectVisibility,
A.EngagementProjectStage,
A.ProjectProfileCode,
A.EngagementProjectType,
A.EngmntProjectHeaderCompanyCode,
A.EngagementProjectCostCenter,
A.ProfitCenter,
A.ControllingArea as EngmntProjectControllingArea,
A.LastChangeDateTime
}
where
Ledger.IsLeadingLedger = 'X'
and
// ProjectActuals.AccountAssignmentType = 'PR' and - This check is moved to the CDS view I_ENGMNTPROJJOURNALENTRYITEM
(
(
EmpOrg.CompanyCode is null
and EmpOrg.CostCenter is null
)
or(
EmpOrg.CompanyCode is not null
and EmpOrg.CostCenter is not null
and ProjectActuals.PostingDate >= EmpOrg.StartDate
and ProjectActuals.PostingDate <= EmpOrg.EndDate
)
)
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ENGMNTPROJASSGMTTOSESSIONUSR",
"I_LEDGER",
"I_PERSONWORKAGRMTORGLDETAILS",
"I_RECEIVABLESPAYABLESITEM",
"P_ENGMNTPROJGLACCTRAWDATA"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/