@AbapCatalog.sqlViewName: 'PPROJ_DLIWIP_BAS'
@ClientDependent: true
@AbapCatalog.compiler.compareFilter: true
@VDM.private:true //= @VDM.private
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED
//@EndUserText.label: 'WIP Detail for WBS elements'
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED
//Used for WIP detail app
//Selects from P_Wipdetailbasic1, prps, proj, rka01, cobk, t001 and P_Projwipdetpernr
//... no 1
define view P_Projectwipdetailbasic
as
select from I_WorkInProcessDetail as a
inner join I_WBSElement as b on b.WBSElementObject = a.ControllingObject
inner join KKAG_DIPRA_WBS ( p_client : $session.client ) as x on b.WBSElementInternalID = x.pspnr
inner join I_Project as c on c.ProjectInternalID = b.ProjectInternalID
left outer to one join I_PersonWorkAgreement_1 as ProjectManagerAgreement on c.ResponsiblePerson = ProjectManagerAgreement.PersonWorkAgreement
left outer to one join I_PersonWorkAgreement_1 as ProjectEmployeeAgreement on a.PersonnelNumber = ProjectEmployeeAgreement.PersonWorkAgreement
// association [0..1] to I_Employment as _EmploymentResPers on c.ResponsiblePerson = _EmploymentResPers.EmploymentInternalID
// and _EmploymentResPers.StartDate <= $session.system_date
// and _EmploymentResPers.EndDate >= $session.system_date
//
// association [0..1] to I_Employment as _Employment on $projection.PersonnelNumber = _Employment.EmploymentInternalID
// and _Employment.StartDate <= $session.system_date
// and _Employment.EndDate >= $session.system_date
{
cast ( a.PostingPeriod as fis_jahrper_conv preserving type ) as PostingPeriod,
a.CostElement,
a.CostCenter,
a.CostCtrActivityType,
a.Material,
a.BusinessProcess,
a.PersonnelNumber, //pernr, Employee
// c.ResponsiblePerson, //I_Project, vernr, Project Manager
cast( case
when c.ResponsiblePerson = '00000000' or c.ResponsiblePerson is null
then c._EntProjProjectRespPersn.PersonWorkAgreement
else c.ResponsiblePerson
end as persno preserving type ) as ResponsiblePerson,
cast ( case when ProjectEmployeeAgreement.PersonFullName is null then '' else ProjectEmployeeAgreement.PersonFullName end as fins_trr_employee_name ) as EmployeeFullName,
cast ( a.ManualInvoiceItem as pro_dw_man_item ) as ManualInvoiceItem,
a.ControllingObject,
a.BillingDocumentCategory,
a.BillingPlanItemUsage,
a.WorkItem,
a.EngagementProjectServiceOrg,
a.AccountingIndicatorCode,
a.CostAnalysisResource,
a.CalendarMonth,
a.TimeSheetOvertimeCategory,
a.ControllingDocument,
a.ControllingDocumentItem,
cast ( case when a.SalesDocumentItemCurrency is null then b._CompanyCode.Currency
when a.SalesDocumentItemCurrency = ' 'then b._CompanyCode.Currency
else a.SalesDocumentItemCurrency end as waerk ) as SalesDocumentItemCurrency,
@Semantics.currencyCode: true
cast ( case when a.TransactionCurrency is null then b._CompanyCode.Currency
when a.TransactionCurrency = ' ' then b._CompanyCode.Currency
else a.TransactionCurrency end as twaer ) as TransactionCurrency,
@Semantics.unitOfMeasure: true
cast ( case when a.UnitOfMeasure is null then 'H'
when a.UnitOfMeasure = ' ' then 'H'
else a.UnitOfMeasure end as co_meinh ) as UnitOfMeasure,
@Semantics.currencyCode: true
cast ( case when a.ControllingObjectCurrency is null then b._CompanyCode.Currency // todo: xwbuk nicht existiert in I_ControllingArea, and b._ControllingArea.xwbuk = 'X'
when a.ControllingObjectCurrency = ' ' then b._CompanyCode.Currency // todo: xwbuk nicht existiert in I_ControllingArea , and b._ControllingArea.xwbuk = 'X'
else a.ControllingObjectCurrency end as hwaer ) as ControllingObjectCurrency,
@Semantics.currencyCode: true
cast ( case when a.ControllingAreaCurrency is null then b._ControllingArea.ControllingAreaCurrency
when a.ControllingAreaCurrency = ' ' then b._ControllingArea.ControllingAreaCurrency
else a.ControllingAreaCurrency end as kwaer ) as ControllingAreaCurrency,
@Semantics.currencyCode: true
cast ( case when a.TransacCurrencyInvoiceSimln is null then b._ControllingArea.ControllingAreaCurrency
when a.TransacCurrencyInvoiceSimln = ' ' then b._ControllingArea.ControllingAreaCurrency
else a.TransacCurrencyInvoiceSimln end as pro_dw_revsim_waers ) as TransacCurrencyInvoiceSimln,
@Semantics.amount.currencyCode: 'TransactionCurrency'
cast ( ( a.TransacCrcyForInvoiceAmount - a.TransacCrcyInvoicedAmount ) as pro_dw_in_wtgbtr ) as BillableAmountInTransacCrcy,
@Semantics.amount.currencyCode: 'ControllingObjectCurrency'
cast ( ( a.ObjectCrcyForInvoiceAmount - a.ObjectCrcyInvoicedAmount ) as pro_dw_in_wogbtr ) as BillableAmountInCtrlgObjCrcy,
@Semantics.amount.currencyCode: 'ControllingAreaCurrency'
cast ( ( a.CtrlgAreaCrcyForInvoiceAmount - a.CtrlgAreaCrcyInvoicedAmount ) as pro_dw_in_wkgbtr ) as BillableAmountInCtrlgAreaCrcy,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
cast ( ( a.ForInvoiceQuantity - a.InvoicedQuantity ) as pro_dw_in_quantity ) as BillableQuantity,
// Amount not to be invoiced, "Not Billable Cost":
@Semantics.amount.currencyCode: 'TransactionCurrency'
cast ( a.TransacCrcyForRjcnAmount as pro_dw_no_wtgbtr ) as NonBillableAmountInTransacCrcy,
@Semantics.amount.currencyCode: 'ControllingObjectCurrency'
cast ( a.ObjectCrcyForRjcnAmount as pro_dw_no_wogbtr ) as NonBllbleAmtInCtrlgObjCurrency,
@Semantics.amount.currencyCode: 'ControllingAreaCurrency'
cast ( a.CtrlgAreaCrcyForRjcnAmount as pro_dw_no_wkgbtr ) as NonBillableAmtInCtrlgAreaCrcy,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
cast ( a.ForRjcnQuantity as pro_dw_no_quantity ) as NonBillableQuantity,
// Already billed costs, "Billed Cost":
@Semantics.amount.currencyCode: 'TransactionCurrency'
cast ( a.TransacCrcyInvoicedAmount as pro_dw_ain_wtgbtr ) as TransacCrcyInvoicedAmount,
@Semantics.amount.currencyCode: 'ControllingObjectCurrency'
cast ( a.ObjectCrcyInvoicedAmount as pro_dw_ain_wogbtr ) as ObjectCrcyInvoicedAmount,
@Semantics.amount.currencyCode: 'ControllingAreaCurrency'
cast ( a.CtrlgAreaCrcyInvoicedAmount as ad01abc_wkg ) as CtrlgAreaCrcyInvoicedAmount,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
cast ( a.InvoicedQuantity as pro_dw_ain_quantity ) as InvoicedQuantity,
@Semantics.amount.currencyCode: 'TransactionCurrency'
cast ( a.RejectedAmtInTransCrcy as pro_dw_ano_wtgbtr ) as RejectedAmtInTransCrcy,
@Semantics.amount.currencyCode: 'ControllingObjectCurrency'
cast ( a.RejectedAmountInObjectCurrency as pro_dw_ano_wogbtr ) as RejectedAmountInObjectCurrency,
@Semantics.amount.currencyCode: 'ControllingAreaCurrency'
cast ( a.RejectedAmountInCtrlgAreaCrcy as ad01noc_wkg ) as RejectedAmountInCtrlgAreaCrcy,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
cast ( a.RejectedQuantity as pro_dw_ano_quantity ) as RejectedQuantity,
// Not yet billed costs (WIP costs), "Unbilled Cost":
@Semantics.amount.currencyCode: 'TransactionCurrency'
cast ( a.TransacCrcyWIPAmount as pro_dw_wip_wtgbtr ) as TransacCrcyWIPAmount,
@Semantics.amount.currencyCode: 'ControllingObjectCurrency'
cast ( a.ObjectCrcyWIPAmount as pro_dw_wip_wogbtr ) as ObjectCrcyWIPAmount,
@Semantics.amount.currencyCode: 'ControllingAreaCurrency'
cast ( a.CtrlgAreaCrcyWIPAmount as ad01tbc_wkg ) as CtrlgAreaCrcyWIPAmount,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
cast ( a.WIPQuantity as pro_dw_wip_quantity ) as WIPQuantity,
// Revenue to be billed/simulated revenue (total), "Billable Revenue":
@Semantics.amount.currencyCode: 'TransacCurrencyInvoiceSimln'
cast ( a.TotTransacCrcyInvoiceSimlnAmt as pro_dw_revsim_wtgbtr ) as TotTransacCrcyInvoiceSimlnAmt,
@Semantics.amount.currencyCode: 'ControllingObjectCurrency'
cast ( a.TotObjectCrcyInvoiceSimlnAmt as pro_dw_revsim_wogbtr ) as TotObjectCrcyInvoiceSimlnAmt,
@Semantics.amount.currencyCode: 'ControllingAreaCurrency'
cast ( a.TotCtrlgAreaCrcyInvcSimlnAmt as ad01rev_wkg ) as TotCtrlgAreaCrcyInvcSimlnAmt,
cast ( a.RevenueCostElement as pro_dw_rev_kstar ) as RevenueCostElement,
cast ( a.SoldMaterial as pro_dw_rev_matnr ) as SoldMaterial,
// Revenue to be billed/simulated revenue (per revenue element):
@Semantics.amount.currencyCode: 'TransacCurrencyInvoiceSimln'
cast ( a.TransacCrcyInvoiceSimlnAmt as ad01rev_wtg ) as TransacCrcyInvoiceSimlnAmt,
@Semantics.amount.currencyCode: 'ControllingObjectCurrency'
cast ( a.ObjectCrcyInvoiceSimlnAmt as ad01rev_wog ) as ObjectCrcyInvoiceSimlnAmt,
@Semantics.amount.currencyCode: 'ControllingAreaCurrency'
cast ( a.CtrlgAreaCrcyInvoiceSimlnAmt as ad01rev_wkg ) as CtrlgAreaCrcyInvoiceSimlnAmt,
a.ResultAnalysisVersion,
a.ServicesRenderedDate as ServicesRenderedDate,
b.WBSElementInternalID as WBSElementInternalID,
b.WBSElement as WBSElement,
b.WBSDescription as WBSDescription,
c.ProjectInternalID as ProjectInternalID,
c.Project as Project,
c.ProjectDescription as ProjectDescription,
b.CompanyCode as CompanyCode,
b.BusinessArea as BusinessArea, // pgsbr nicht existiert in I_WBSElementBasicData
b.ControllingArea as ControllingArea,
b.ProfitCenter as ProfitCenter,
cast (b.ResultAnalysisInternalID as fins_trr_recgnkey ) as ResultAnalysisInternalID, // abgsl nicht existiert in I_WBSElementBasicData
b.Plant as Plant,
b.ControllingObjectClass as ControllingObjectClass, // scope nicht existiert in I_WBSElementBasicData
b.FunctionalArea as FunctionalArea,
b.CreationDate as CreationDate, // erdat nicht existiert in I_WBSElementBasicData
//cast ( c.ResponsiblePerson as fins_trr_project_manager ) as ProjectManager, // vernr nicht existiert in I_ProjectBasicData
//ResponsiblePerson is already used above AS ResponsiblePerson, dont know reason behind duplicate field under different name so keeping both
cast( case
when c.ResponsiblePerson = '00000000' or c.ResponsiblePerson is null
then c._EntProjProjectRespPersn.PersonWorkAgreement
else c.ResponsiblePerson
end as fins_trr_project_manager preserving type ) as ProjectManager,
//cast ( ProjectManagerAgreement.PersonFullName as fins_trr_projmng_name ) as ProjectManagerName,
cast( case
when c.ResponsiblePerson = '00000000' or c.ResponsiblePerson is null
then c._EntProjProjectRespPersn._WorkforcePerson.PersonFullName
else c.ResponsiblePersonName
end as fins_trr_projmng_name preserving type ) as ProjectManagerName,
//for aceess control
c._ResponsiblePerson,
c.ResponsiblePerson as ProjectResponsiblePerson,
c._EntProjProjectRespPersn
// _EmploymentResPers,
// _Employment
}
where
x.tadat = '00000000';
//Simulation details from P_Projectwiprevdetailbasic are not displayed
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_COMPANYCODE",
"I_CONTROLLINGAREA",
"I_ENTPROJPROJECTRESPPERSN",
"I_PERSONWORKAGREEMENT_1",
"I_PROJECT",
"I_WBSELEMENT",
"I_WORKFORCEPERSON",
"I_WORKINPROCESSDETAIL"
],
"ASSOCIATED":
[
"I_ENTPROJPROJECTRESPPERSN",
"I_PROJECTRESPONSIBLEPERSON"
],
"BASE":
[
"I_PROJECT"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/