@AbapCatalog.sqlViewName: 'PPPRJBLGELMFLW00'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@VDM.private: true
@AccessControl.privilegedAssociations: ['_SlsDocItemBillingPlanDueDate']
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
@Consumption.dbHints: ['NO_SUBPLAN_SHARING'] -- Performance testing
define view P_ProjBillingElementEntrFlw00
as select from I_ProjectBillingElementEntrFlw as _ProjBillingElemEntrFlw
left outer join I_PrjBlgElmEntrJrnlEntrLink as _PrjBlgElmEntrJrnlEntrLink on _PrjBlgElmEntrJrnlEntrLink.ProjBillgElmntEntrItmUUID = _ProjBillingElemEntrFlw.ProjBillgElmntEntrItmUUID
left outer join I_PrjBlgElmEntrBillgPlnLink as _PrjBlgElmEntrBillgPlnLink on _PrjBlgElmEntrBillgPlnLink.ProjBillgElmntEntrItmUUID = _ProjBillingElemEntrFlw.ProjBillgElmntEntrItmUUID
inner join I_ProjectBillingElementEntry as ProjectBillingElementEntry on _ProjBillingElemEntrFlw.ProjBillgElmntEntrItmUUID = ProjectBillingElementEntry.ProjBillgElmntEntrItmUUID
inner join I_ProjectBillingElement as _ProjectBillingElement on _ProjectBillingElement.ProjectBillingElementUUID = ProjectBillingElementEntry.ProjectBillingElementUUID
-- Performance improvement start POC
// association [0..1] to I_ProjectBillingRequest as _ProjectBillingRequest on _ProjBillingElemEntrFlw.ProjectBillingRequest = _ProjectBillingRequest.ProjectBillingRequest
// association [0..1] to I_ProjectBillingRequestItem as _ProjectBillingRequestItem on $projection.ProjectBillingRequestUUID = _ProjectBillingRequestItem.ProjectBillingRequestUUID
// and $projection.ProjBillgElmntEntrItmUUID = _ProjectBillingRequestItem.ProjBillgElmntEntrItmUUID
-- Performance improvement end POC
association [1..1] to I_GLAccountLineItemRawData as _GLAccountLineItemRawData on _PrjBlgElmEntrJrnlEntrLink.AccountingDocument = _GLAccountLineItemRawData.AccountingDocument
and _PrjBlgElmEntrJrnlEntrLink.LedgerGLLineItem = _GLAccountLineItemRawData.LedgerGLLineItem
and _PrjBlgElmEntrJrnlEntrLink.FiscalYear = _GLAccountLineItemRawData.FiscalYear
and _PrjBlgElmEntrJrnlEntrLink.CompanyCode = _GLAccountLineItemRawData.CompanyCode
and _PrjBlgElmEntrJrnlEntrLink.Ledger = _GLAccountLineItemRawData.SourceLedger
association [0..1] to I_WorkForcePersonMappings as _WorkForcePersonMappings on $projection.Personnel = _WorkForcePersonMappings.WorkforceAssignment
// association [0..1] to I_BusinessPartner as _BusinessPartner on $projection.BusinessPartner = _BusinessPartner.BusinessPartner
// and _BusinessPartner.BusinessPartnerCategory = '1'
association [1..1] to I_SalesOrderItem as _SalesOrderItem on _ProjectBillingElement.BillingWBSElementInternalID = _SalesOrderItem.WBSElementInternalID
and _SalesOrderItem.WBSElementInternalID <> '00000000'
// Below association will not fetch data for T&E as table _PrjBlgElmEntrBillgPlnLink stores data for Fixed price and on Account
// association [0..*] to I_SalesDocItemBillingPlanItem as _SlsDocItemBillingPlanDueDate on $projection.PbeeLnkBP = _SlsDocItemBillingPlanDueDate.BillingPlan // $projection.BillingPlan = _SlsDocItemBillingPlanDueDate.BillingPlan
// and _PrjBlgElmEntrBillgPlnLink.BillingPlanItem = _SlsDocItemBillingPlanDueDate.BillingPlanItem
association [0..1] to I_SDBillingPlanItem as _SlsDocItemBillingPlanDueDate on _PrjBlgElmEntrBillgPlnLink.BillingPlan = _SlsDocItemBillingPlanDueDate.BillingPlan // $projection.BillingPlan = _SlsDocItemBillingPlanDueDate.BillingPlan
and _PrjBlgElmEntrBillgPlnLink.BillingPlanItem = _SlsDocItemBillingPlanDueDate.BillingPlanItem
association [0..1] to I_MaterialText as _MaterialText on $projection.Material = _MaterialText.Material
and _MaterialText.Language = $session.system_language
association [0..1] to I_JournalEntry as _JournalEntry on _PrjBlgElmEntrJrnlEntrLink.CompanyCode = _JournalEntry.CompanyCode
and $projection.FiscalYear = _JournalEntry.FiscalYear
and _PrjBlgElmEntrJrnlEntrLink.AccountingDocument = _JournalEntry.AccountingDocument
association [1..1] to I_BillingDocumentRequestItem as _BillingDocumentReqItem on $projection.BillingDocument = _BillingDocumentReqItem.BillingDocumentRequest
and $projection.BillingDocumentItem = _BillingDocumentReqItem.BillingDocumentRequestItem
// association [0..1] to I_Employment as _Employment on $projection.Personnel = _Employment.EmploymentInternalID
// association [0..1] to I_PersonWorkAgreement_1 as _PersonWorkAgreement_1 on $projection.Personnel = _PersonWorkAgreement_1.PersonWorkAgreement
// association [0..1] to I_WorkForceEmployeeDetails as _WorkForceEmployeeDetails on $projection.Personnel = _WorkForceEmployeeDetails.WorkforceAssignmentID
association [0..1] to I_TimeSheetOvertimeCatText as _TimeSheetOvertimeCatText on $projection.timesheetovertimecategory = _TimeSheetOvertimeCatText.TimeSheetOvertimeCategory
and _TimeSheetOvertimeCatText.Language = $session.system_language
association [0..1] to I_CostCenterActivityTypeText as _CostCenterActivityTypeText on $projection.ControllingArea = _CostCenterActivityTypeText.ControllingArea
and $projection.CostCtrActivityType = _CostCenterActivityTypeText.CostCtrActivityType
and _CostCenterActivityTypeText.ValidityEndDate >= $session.system_date
and _CostCenterActivityTypeText.Language = $session.system_language
association [0..1] to I_WBSElementBasicData as _WBSElement on $projection.WBSElementInternalID = _WBSElement.WBSElementInternalID
-- Performance improvement POC
// association [0..*] to I_ProjectBillingRequestItem as _ProjectBillingRequestItem on $projection.ProjectBillingRequest = _ProjectBillingRequestItem.ProjectBillingRequest
// and $projection.ProjBillgElmntEntrItmUUID = _ProjectBillingRequestItem.ProjBillgElmntEntrItmUUID
association [0..1] to I_EnterpriseProjectElement as _EnterpriseProjectElement on $projection.WBSElementInternalID = _EnterpriseProjectElement.WBSElementInternalID
and _EnterpriseProjectElement.WBSElementInternalID <> '00000000'
{
key _ProjBillingElemEntrFlw.ProjBillgElmntEntrItmFlowUUID,
key _GLAccountLineItemRawData.SalesDocument,
key _GLAccountLineItemRawData.SalesDocumentItem,
key _ProjBillingElemEntrFlw.BillingDocument,
key _ProjBillingElemEntrFlw.BillingDocumentItem,
_ProjBillingElemEntrFlw.ProjectBillingRequest,
_ProjBillingElemEntrFlw.ProjBillgElmntEntrItmUUID,
_ProjBillingElemEntrFlw.SDDocumentCategory,
_ProjBillingElemEntrFlw.TransactionCurrency,
_ProjBillingElemEntrFlw.QuantityUnit,
-- For ICO Update Billing requested quantity from Original quantity
case
when _ProjectBillingElement.ProjectBillingCategory != 'ICBL' then _ProjBillingElemEntrFlw.BillingRequestedQuantity
when _ProjectBillingElement.ProjectBillingCategory = 'ICBL' then ProjectBillingElementEntry.OriginalTotalQuantity
end as BillingRequestedQuantity,
--_ProjBillingElemEntrFlw.BillingRequestedQuantity,
_ProjBillingElemEntrFlw.BillgReqdAmtInTransacCrcy,
_ProjBillingElemEntrFlw.BillgReqdAmtInGlobalCrcy,
_ProjBillingElemEntrFlw.WrittenOffQuantity,
_ProjBillingElemEntrFlw.WrittenOffAmtInTransacCrcy,
_ProjBillingElemEntrFlw.WrittenOffAmtInGlobalCrcy,
_ProjBillingElemEntrFlw.PostponedQuantity,
_ProjBillingElemEntrFlw.PostponedAmountInTransacCrcy,
_ProjBillingElemEntrFlw.PostponedAmountInGlobalCrcy,
_ProjBillingElemEntrFlw.GlobalCurrency,
_ProjBillingElemEntrFlw.NetPriceAmountInDocCrcy,
_ProjBillingElemEntrFlw.DocumentCurrency,
_ProjBillingElemEntrFlw.BillgReqdRevenueAmtInDocCrcy as NetAmount,
_ProjBillingElemEntrFlw.BillingDocumentType,
_PrjBlgElmEntrJrnlEntrLink.Ledger,
_PrjBlgElmEntrJrnlEntrLink.FiscalYear,
_PrjBlgElmEntrJrnlEntrLink.AccountingDocument,
_PrjBlgElmEntrJrnlEntrLink.LedgerGLLineItem,
_PrjBlgElmEntrJrnlEntrLink.CompanyCode,
// _SalesOrderItem.BillingPlan as BillingPlan,
_PrjBlgElmEntrBillgPlnLink.BillingPlan, // as PbeeLnkBP,
_PrjBlgElmEntrBillgPlnLink.BillingPlanItem,
_GLAccountLineItemRawData.ControllingObject,
// _GLAccountLineItemRawData.CostCtrActivityType,
_GLAccountLineItemRawData.OriginCostCtrActivityType as CostCtrActivityType,
_GLAccountLineItemRawData.DocumentItemText,
_GLAccountLineItemRawData.TimeSheetOvertimeCategory,
_GLAccountLineItemRawData.SourceLedger,
_GLAccountLineItemRawData.ServicesRenderedDate,
_GLAccountLineItemRawData.PostingDate,
_GLAccountLineItemRawData.DocumentDate,
_GLAccountLineItemRawData.PartnerCompanyCode,
_GLAccountLineItemRawData.PersonnelNumber as Personnel,
_GLAccountLineItemRawData.WorkItem as WorkItem,
//_GLAccountLineItemRawData._WorkPackageWorkItem.WorkItemName,
_GLAccountLineItemRawData.GLAccount as CostElement,
_GLAccountLineItemRawData.ReferenceDocument,
_GLAccountLineItemRawData.ReferenceDocumentItem,
// _GLAccountLineItemRawData.WBSElementInternalID,
// _GLAccountLineItemRawData._WBSElementBasicData.WBSDescription,
// _GLAccountLineItemRawData._WBSElementBasicData.WBSElementExternalID as WBSElement,
// _GLAccountLineItemRawData._WBSElementBasicData.WBSElementObject,
// _GLAccountLineItemRawData._WBSElementBasicData._Project.ProjectDescription,
// _GLAccountLineItemRawData.Project,
_WBSElement.WBSElementExternalID as WBSElement,
_WBSElement._Project.Project as Project,
_WBSElement._Project.ProjectDescription as ProjectDescription,
_WBSElement.WBSDescription as WBSDescription,
//_EnterpriseProjectElement.ProjectElementDescription as WBSDescription,
_WBSElement.WBSElementObject as WBSElementObject,
//_WBSElement.ControllingArea as ControllingArea,
--For intercompany scenario we won't have WBS Element all the time, in that case retrieve from ACDOCA
case
when _ProjectBillingElement.ProjectBillingCategory != 'ICBL' then _WBSElement.ControllingArea
when _ProjectBillingElement.ProjectBillingCategory = 'ICBL' then _GLAccountLineItemRawData.ControllingArea
end as ControllingArea,
_WBSElement.ProfitCenter as ProfitCenter,
_WBSElement.ResponsibleCostCenter as ResponsibleCostCenter,
ProjectBillingElementEntry.WBSElementInternalID,
--ProjectBillingElementEntry.OriginalTotalQuantity,
_BillingDocumentReqItem.Product as Material,
_BillingDocumentReqItem.ProductGroup as MaterialGroup,
//ProjectBillingElementEntry.PrjBlgElmEntryLongText as PrjBlgElmEntryLongText,
//_ProjectBillingRequestItem.ProjBillingRequestItemLongText as ProjBillingRequestItemLongText, -- moved to upper layer
//_ProjBillingElemEntrFlw.ProjBillingRequestItemLongText as ProjBillingRequestItemLongText,
_ProjBillingElemEntrFlw.ProjBillingRequestItemLongText as ProjBillingRequestItemLongText,
//_SalesOrderItem._BillingPlan.BillingPlanUsageCategory as BillingPlanUsageCategory,
_ProjectBillingElement.BillingWBSElementInternalID as BillingWBSElementInternalID,
_ProjectBillingElement.ProjectBillingCategory,
_JournalEntry.DocumentReferenceID,
_GLAccountLineItemRawData.ReferenceDocumentType as ReferenceDocumentType,
_GLAccountLineItemRawData.IsReversal as IsReversal,
_GLAccountLineItemRawData.IsReversed as IsReversed,
-- Performance Improvement
//_ProjectBillingRequest.ProjectBillingRequestUUID as ProjectBillingRequestUUID,
// _BusinessPartner.BusinessPartner as BusinessPartner,
-- Performance Improvement
// Expose the associations
// _SlsDocItemBillingPlan,
_SlsDocItemBillingPlanDueDate,
// _PersonWorkAgreement_1, -- replaced by _WorkForcePersonMappings
_WorkForcePersonMappings,
_SalesOrderItem,
_MaterialText,
_WBSElement,
_CostCenterActivityTypeText,
_TimeSheetOvertimeCatText,
_JournalEntry
//_ProjectBillingRequestItem
}
where -- Performance POC
ProjectBillingElementEntry.ProjBillgElmntEntrSourceType = 'A'
or ProjectBillingElementEntry.ProjBillgElmntEntrSourceType = 'B'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BILLINGDOCUMENTREQUESTITEM",
"I_GLACCOUNTLINEITEMRAWDATA",
"I_JOURNALENTRY",
"I_PRJBLGELMENTRBILLGPLNLINK",
"I_PRJBLGELMENTRJRNLENTRLINK",
"I_PROJECTBASICDATA",
"I_PROJECTBILLINGELEMENT",
"I_PROJECTBILLINGELEMENTENTRFLW",
"I_PROJECTBILLINGELEMENTENTRY",
"I_WBSELEMENTBASICDATA"
],
"ASSOCIATED":
[
"I_BILLINGDOCUMENTREQUESTITEM",
"I_COSTCENTERACTIVITYTYPETEXT",
"I_ENTERPRISEPROJECTELEMENT",
"I_GLACCOUNTLINEITEMRAWDATA",
"I_JOURNALENTRY",
"I_MATERIALTEXT",
"I_SALESORDERITEM",
"I_SDBILLINGPLANITEM",
"I_TIMESHEETOVERTIMECATTEXT",
"I_WBSELEMENTBASICDATA",
"I_WORKFORCEPERSONMAPPINGS"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/