@AbapCatalog.sqlViewName: 'PENGPROJCHGDOC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'Engagement Project Change Document'
define view P_EngmntProjChangeDocument
as select from I_ChangeDocumentItem as ChangeDocumentItem
inner join I_ChangeDocument as ChangeDocumentHdr on ChangeDocumentItem.ChangeDocObjectClass = ChangeDocumentHdr.ChangeDocObjectClass
and ChangeDocumentItem.ChangeDocObject = ChangeDocumentHdr.ChangeDocObject
and ChangeDocumentItem.ChangeDocument = ChangeDocumentHdr.ChangeDocument
//For User Full name
left outer to one join I_User as UserDetails on ChangeDocumentHdr.CreatedByUser = UserDetails.UserID
//For Table description
left outer to one join I_ChangeDocTableNames as TableText on ChangeDocumentItem.DatabaseTable = TableText.Value
and TableText.Language = $session.system_language
//For Change type description
left outer to one join I_ChangeDocChangeIndT as ChangeType on ChangeDocumentItem.ChangeDocItemChangeType = ChangeType.Value
and ChangeType.Language = $session.system_language
//For Field name from GFN.
left outer to one join P_EngmntProjFldMappgForChgDoc as FieldMapping on ChangeDocumentItem.ChangeDocDatabaseTableField = FieldMapping.EntProjChgDocFld
//For Staffing Instruction values
left outer to one join I_ChangeDocumentItemExtension as ChangeDocumentString on ChangeDocumentItem.ChangeDocTableKey = ChangeDocumentString.ChangeDocKeyGuid
//For Long Table Key
left outer to one join I_ChangeDocLongTableKey as LongTableKey on LongTableKey.ChangeDocKeyGuid = ChangeDocumentItem.ChangeDocTableKey
{
@Consumption.hidden
key ChangeDocumentItem.ChangeDocObject,
@Consumption.hidden
key ChangeDocumentItem.ChangeDocObjectClass,
@Consumption.hidden
key ChangeDocumentItem.ChangeDocument,
@Consumption.hidden
key ChangeDocumentItem.DatabaseTable,
@Consumption.hidden
key ChangeDocumentItem.ChangeDocTableKey,
@Consumption.hidden
key ChangeDocumentItem.ChangeDocDatabaseTableField,
key ChangeDocumentItem.ChangeDocItemChangeType,
ChangeType.Text as ChangeDocItemChangeTypeText,
TableText.Text as DatabaseTableText,
case
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'KEY'
then cast ( 'Key' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'BUPA_ID'
then cast ( 'BusinessPartner' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'PROJECT_CONTROLLER'
then cast ( 'ProjectController' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'COST_CENTER'
then cast ( 'Customer' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'EMPLOYMENTINTERNALID'
then cast ( 'EmploymentInternalID' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'FIRST_NAME'
then cast ( 'FirstName' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'LAST_NAME'
then cast ( 'LastName' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'MP_ITEM_OKEY'
then cast ( 'ProjectItemKey' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'MP_ITM_OTYP'
then cast ( 'Item Type' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'PROJECT_MANAGER'
then cast ( 'ProjectManager' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'PROJECT_PARTNER'
then cast ( 'ProjectPartner' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'STATUS_ID'
then cast ( 'StatusID' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'SUPPLIED_EMPLOYEE'
then cast ( 'SuppliedEmployee' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'DEMANDED_EMPLOYEE'
then cast ( 'DemandedEmployee' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'MEM_ID'
then cast ( 'MemberID' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'PROJECT_ACCOUNTANT'
then cast ( 'ProjectAccountant' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'ARCHIVING_STATUS'
then cast ( 'ArchivingStatus' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'THRESHOLD_VALUE'
then cast ( 'ThresholdValue' as fieldname )
when ChangeDocumentItem.ChangeDocDatabaseTableField = 'LANGUAGE_CODE'
then cast ( 'LanguageCode' as fieldname )
else
FieldMapping.EntPrjChgDocFldDet
end as ChangeDocGlobalFieldName,
ChangeDocumentHdr.ChangeTransactionCode,
ChangeDocumentHdr.CreatedByUser,
UserDetails.UserDescription as UserName,
@Semantics.systemDate.createdAt: true
ChangeDocumentHdr.CreationDate,
@Semantics.systemTime.createdAt: true
ChangeDocumentHdr.CreationTime,
ChangeDocumentItem.ChangeDocPreviousFieldValue,
ChangeDocumentItem.ChangeDocNewFieldValue,
ChangeDocumentString.ChangeDocStringNew as ChangeDocNewStrFieldValue,
ChangeDocumentString.ChangeDocStringOld as ChangeDocPreviousStrFieldValue,
LongTableKey.ChangeDocLongTableKey,
//For Project ID
case
when (ChangeDocumentItem.DatabaseTable = '/CPD/S_MP_CHNGE_DOC'
or ChangeDocumentItem.DatabaseTable = '/CPD/S_RL_CHNGE_DOC'
or ChangeDocumentItem.DatabaseTable = '/CPD/S_ROLE_CHNGE_DOC'
or ChangeDocumentItem.DatabaseTable = '/CPD/S_PD_CHNGE_DOC' )
then
case
when LongTableKey.ChangeDocLongTableKey is not initial
then
substring(LongTableKey.ChangeDocLongTableKey, 4, 40)
else
substring(ChangeDocumentItem.ChangeDocTableKey, 4, 40) end
when ( ChangeDocumentItem.DatabaseTable = '/CPD/S_DH_CHNGE_DOC' )
then
case
when ChangeDocumentString.ChangeDocTableKey is not initial
then
substring(ChangeDocumentString.ChangeDocTableKey, 4, 40 )
else
substring(ChangeDocumentItem.ChangeDocTableKey, 4, 40) end
when ( ChangeDocumentItem.DatabaseTable = '/CPD/S_PLN_HDR_CD'
or ChangeDocumentItem.DatabaseTable = '/CPD/S_PLN_DET_CD' )
then cast( ChangeDocumentItem.ChangeDocObject as /cpd/mp_id )
end as EngagementProject,
//For Work Package
case
when ( ChangeDocumentItem.DatabaseTable = '/CPD/S_PLN_HDR_CD' )
then
cast ( substring(LongTableKey.ChangeDocLongTableKey, 36, 50) as /cpd/sc_wp_id)
when ( ChangeDocumentItem.DatabaseTable = '/CPD/S_PLN_DET_CD' )
then
cast( substring(LongTableKey.ChangeDocLongTableKey, 44, 50) as /cpd/sc_wp_id )
end as WorkPackage,
//For ResourceDemandUUID
case
when (ChangeDocumentItem.DatabaseTable = '/CPD/S_PD_CHNGE_DOC' )
then
case
when LongTableKey.ChangeDocLongTableKey is not initial
then
substring(LongTableKey.ChangeDocLongTableKey, 44, 32)
else
substring(ChangeDocumentItem.ChangeDocTableKey, 44, 32) end
when (ChangeDocumentItem.DatabaseTable = '/CPD/S_DH_CHNGE_DOC' )
then
case
when ChangeDocumentString.ChangeDocTableKey is not initial
then
substring(ChangeDocumentString.ChangeDocTableKey, 44, 32)
else
substring(ChangeDocumentItem.ChangeDocTableKey, 44, 32) end
when (ChangeDocumentItem.DatabaseTable = '/CPD/S_PLN_HDR_CD'
or ChangeDocumentItem.DatabaseTable = '/CPD/S_PLN_DET_CD' )
then
case
when LongTableKey.ChangeDocLongTableKey is not initial
then
substring(LongTableKey.ChangeDocLongTableKey, 4, 32)
else
substring(ChangeDocumentItem.ChangeDocTableKey, 4, 32) end
end as ResourceDemandUUID,
//For ProjectUUID
case
when ChangeDocumentItem.ChangeDocObjectClass = '/CPD/PWS_BO_MP'
then
substring( ChangeDocumentItem.ChangeDocObject, 1, 32 )
end as EngagementProjectUUID,
//For Fin Plan UUID
case
when ChangeDocumentItem.ChangeDocObjectClass = '/CPD/PLAN_HDR'
then
substring( ChangeDocumentItem.ChangeDocObject, 1, 32 )
end as EngagementProjFinPlanUUID
}
where
ChangeDocumentItem.ChangeDocObjectClass = '/CPD/SS_CHDOC'
or ChangeDocumentItem.ChangeDocObjectClass = '/CPD/PWS_BO_MP'
or ChangeDocumentItem.ChangeDocObjectClass = '/CPD/PLAN_HDR'