@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.sqlViewName: 'PEPSLSPLNDATA'
@AbapCatalog.compiler.compareFilter: true
@VDM.private: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #COMPOSITE
//@EndUserText.label: 'Engagement Project Sales Plan Data'
define view P_EngmtProjSalesPlanData
as select from I_EngmntProjAssgmtToSessionUsr as A
inner join I_EngmntProjPlnDataUnitVers as I_EngagementProjectPlanData on A.EngagementProject = I_EngagementProjectPlanData.EngagementProject
left outer to one join I_EngmntProjSupplyEmployee as SupplyEmployee on I_EngagementProjectPlanData.EngagementProject = SupplyEmployee.EngagementProject
and I_EngagementProjectPlanData.ResourceDemandUUID = SupplyEmployee.ResourceDemandUUID
and I_EngagementProjectPlanData.EngmtProjFinPlanVersion = SupplyEmployee.EngmtProjFinPlanVersion
left outer to one join P_EngmtProjDmndByPerdKPI as PlanLineItemKPI on I_EngagementProjectPlanData.EngagementProject = PlanLineItemKPI.EngagementProject
and I_EngagementProjectPlanData.ResourceDemandUUID = PlanLineItemKPI.ResourceDemandUUID
and I_EngagementProjectPlanData.EngmtProjFinPlanVersion = PlanLineItemKPI.EngmtProjFinPlanVersion
and I_EngagementProjectPlanData.FiscalYear = PlanLineItemKPI.FiscalYear
and I_EngagementProjectPlanData.FiscalPeriod = PlanLineItemKPI.FiscalPeriod
left outer to one join I_CompanyCode as CompanyCode on A.EngmntProjectHeaderCompanyCode = CompanyCode.CompanyCode
{
I_EngagementProjectPlanData.EngagementProject,
I_EngagementProjectPlanData.WorkPackage,
I_EngagementProjectPlanData.WorkItem,
I_EngagementProjectPlanData.Currency,
CompanyCode.Currency as CompanyCodeCurrency,
I_EngagementProjectPlanData.EngagementProjFinPlanUUID,
I_EngagementProjectPlanData.EngmtProjFinPlanVersion,
_WorkPackage.SalesOrder,
_WorkPackage.SalesOrderItem,
I_EngagementProjectPlanData.FiscalYear,
I_EngagementProjectPlanData.FiscalPeriod,
I_EngagementProjectPlanData.EngagementProjectResourceType,
case
when SupplyEmployee.SuppliedEmploymentInternalID <> '' then
cast('X' as /cpd/sc_conf )
else cast('' as /cpd/sc_conf) end as Confirmed,
case
when SupplyEmployee.SuppliedEmploymentInternalID <> '' then
SupplyEmployee.SuppliedEmploymentInternalID
else I_EngagementProjectPlanData.EmploymentInternalID end as EmploymentInternalID,
cast ( cast ((PlanLineItemKPI.SupplyCostAmt + PlanLineItemKPI.RemainingDemandCostAmt + PlanLineItemKPI.PlannedExpenseCost ) as abap.curr( 23, 2 )) as /cpd/pfp_curr ) as PlndCostAmt,
cast ( cast ((PlanLineItemKPI.SupplyEffortQty + PlanLineItemKPI.RemainingDemandEffortQty ) as abap.quan( 15, 3 ) ) as megxxx) as PlndEffortQty,
cast ( cast ((PlanLineItemKPI.SupplyRevnAmt + PlanLineItemKPI.RemainingDemandRevnAmt + PlanLineItemKPI.PlannedExpenseRevenue ) as abap.curr( 23, 2 )) as /cpd/pfp_curr ) as PlndRevnAmt,
case
when I_EngagementProjectPlanData.IsUnitBased = 'X'
//or I_EngagementProjectPlanData.EngagementProjectResourceType = '0EXP'
then sum(I_EngagementProjectPlanData.PlndRevnAmt) end as PlannedUnitRevenue,
I_EngagementProjectPlanData.ActualPlanDeviationCost as ActualPlanDeviationCost,
I_EngagementProjectPlanData.ActualPlanDeviationEffort as ActualPlanDeviationEffort,
cast('X' as abap.char( 2 )) as AccountingDocumentStatus,
concat (I_EngagementProjectPlanData.FiscalYear,I_EngagementProjectPlanData.FiscalPeriod) as FiscalYearPeriod,
I_EngagementProjectPlanData.UnitOfMeasure as UnitOfMeasure,
I_EngagementProjectPlanData.EngmntProjPlanLineType as EngmntProjPlanLineType,
A.IsMyProject,
A.EngagementProjectCategory
}
/*where
I_EngagementProjectPlanData.EngmntProjPlanLineType = 'D'*/
group by
I_EngagementProjectPlanData.EngagementProject,
I_EngagementProjectPlanData.EngmntProjPlanLineType,
I_EngagementProjectPlanData.FiscalYear,
I_EngagementProjectPlanData.UnitOfMeasure,
I_EngagementProjectPlanData.ActualPlanDeviationEffort,
I_EngagementProjectPlanData.ActualPlanDeviationCost,
I_EngagementProjectPlanData.EngagementProjFinPlanUUID,
I_EngagementProjectPlanData.EngmtProjFinPlanVersion,
_WorkPackage.SalesOrder,
I_EngagementProjectPlanData.WorkPackage,
I_EngagementProjectPlanData.FiscalPeriod,
I_EngagementProjectPlanData.EngagementProjectResourceType,
PlanLineItemKPI.SupplyRevnAmt,
I_EngagementProjectPlanData.WorkItem,
_WorkPackage.SalesOrderItem,
PlanLineItemKPI.PlannedExpenseRevenue,
PlanLineItemKPI.PlannedExpenseCost,
I_EngagementProjectPlanData.Currency,
SupplyEmployee.SuppliedEmploymentInternalID,
I_EngagementProjectPlanData.EmploymentInternalID,
PlanLineItemKPI.SupplyCostAmt,
PlanLineItemKPI.SupplyEffortQty,
PlanLineItemKPI.RemainingDemandRevnAmt,
PlanLineItemKPI.RemainingDemandEffortQty,
PlanLineItemKPI.RemainingDemandCostAmt,
PlanLineItemKPI.ResourceDemandUUID,
CompanyCode.Currency,
I_EngagementProjectPlanData.IsUnitBased,
A.IsMyProject,
A.EngagementProjectCategory