@EndUserText.label: 'SRC: Relevant Affected Objects for Users'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog: {
sqlViewName: 'IEHSSRCOBJUSR2',
preserveKey: true,
compiler.compareFilter: true }
// Client handling by session
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType:{
serviceQuality: #C,
sizeCategory: #L,
dataClass: #MIXED }
define view I_SftyRlvtChgAffcdObjUser_2 as
select from I_SafetyRelevantChgAffcdObj as AffectedObject
inner join I_HzdsSubstInventory as Inventory on AffectedObject.HzdsSubstInventoryUUID = Inventory.HzdsSubstInventoryUUID
{
key SafetyRelevantChgAffcdObjUUID,
key Inventory.CreatedByUser as SafetyRelevantChgNotifProcgUsr,
SafetyRelevantChgAffcdObjType
}
where SafetyRelevantChgAffcdObjType = '01'
union select distinct from I_SafetyRelevantChgAffcdObj as AffectedObject
inner join I_HzdsSubstInventory as Inventory on AffectedObject.HzdsSubstInventoryUUID = Inventory.HzdsSubstInventoryUUID
inner join I_EHSLocationResponsible as HSI_Responsible on Inventory.EHSLocationUUID = HSI_Responsible.EHSLocationUUID
and(
HSI_Responsible.EHSLocationPersonRespRole = 'SAP_EHSM_HSS_HSI_APPROVER'
or HSI_Responsible.EHSLocationPersonRespRole = 'SAP_EHSM_HSS_SFTY_CHNGE_RECEIV'
)
{
key SafetyRelevantChgAffcdObjUUID,
key substring( HSI_Responsible.EHSLocRespTechnicianID,2,12 ) as SafetyRelevantChgNotifProcgUsr,
SafetyRelevantChgAffcdObjType
}
where SafetyRelevantChgAffcdObjType = '01'
// direct Safety Instruction Users
union select from I_SafetyRelevantChgAffcdObj as AffectedObject
inner join I_SafetyInstruction as SafetyInstruction on AffectedObject.SafetyInstructionUUID = SafetyInstruction.SafetyInstructionUUID
{
key SafetyRelevantChgAffcdObjUUID,
key SafetyInstruction.CreatedByUser as SafetyRelevantChgNotifProcgUsr,
SafetyRelevantChgAffcdObjType
}
where SafetyRelevantChgAffcdObjType = '02'
// Safety Instruction Users via location role
union select distinct from I_SafetyRelevantChgAffcdObj as AffectedObject
inner join I_SafetyInstruction as SafetyInstruction on AffectedObject.SafetyInstructionUUID = SafetyInstruction.SafetyInstructionUUID
inner join I_EHSLocationResponsible as SI_Responsible on SafetyInstruction.FactoryEHSLocationUUID = SI_Responsible.EHSLocationUUID
and(
SI_Responsible.EHSLocationPersonRespRole = 'SAP_EHSM_HSS_SI_APPROVER'
or SI_Responsible.EHSLocationPersonRespRole = 'SAP_EHSM_HSS_SFTY_CHNGE_RECEIV'
)
{
key SafetyRelevantChgAffcdObjUUID,
key substring( SI_Responsible.EHSLocRespTechnicianID,2,12 ) as SafetyRelevantChgNotifProcgUsr,
SafetyRelevantChgAffcdObjType
}
where SafetyRelevantChgAffcdObjType = '02'
// direct Risk Assessment Users
union select from I_SafetyRelevantChgAffcdObj as AffectedObject
inner join I_EHSRiskAssessment as RiskAssessment on AffectedObject.EHSRiskAssessmentUUID = RiskAssessment.EHSRiskAssessmentUUID
{
key SafetyRelevantChgAffcdObjUUID,
key RiskAssessment.CreatedByUser as SafetyRelevantChgNotifProcgUsr,
SafetyRelevantChgAffcdObjType
}
where SafetyRelevantChgAffcdObjType = '03'
// Risk Assessment via approval team
union select distinct from I_SafetyRelevantChgAffcdObj as AffectedObject
// Risk Assessment Users
inner join I_EHSRiskAssessment as RiskAssessment on AffectedObject.EHSRiskAssessmentUUID = RiskAssessment.EHSRiskAssessmentUUID
inner join I_EHSRiskAssessmentTeam as RiskAssessmentTeam on RiskAssessment.EHSRiskAssessmentUUID = RiskAssessmentTeam.EHSRiskAssessmentUUID
and RiskAssessmentTeam.EHSRiskAssessmentPersonRole = 'EHHSS_RAS_LEAD'
{
key SafetyRelevantChgAffcdObjUUID,
key substring( RiskAssessmentTeam.EHSRiskAssessmentPersonID,2,12 ) as SafetyRelevantChgNotifProcgUsr,
SafetyRelevantChgAffcdObjType
}
where SafetyRelevantChgAffcdObjType = '03'
union select distinct from I_SafetyRelevantChgAffcdObj as AffectedObject
inner join I_EHSRiskAssessment as RiskAssessment on AffectedObject.EHSRiskAssessmentUUID = RiskAssessment.EHSRiskAssessmentUUID
inner join I_EHSLocationResponsible as Responsible on RiskAssessment.EHSLocationUUID = Responsible.EHSLocationUUID
and Responsible.EHSLocationPersonRespRole = 'SAP_EHSM_HSS_SFTY_CHNGE_RECEIV'
{
key SafetyRelevantChgAffcdObjUUID,
key substring( Responsible.EHSLocRespTechnicianID,2,12 ) as SafetyRelevantChgNotifProcgUsr,
SafetyRelevantChgAffcdObjType
}
where SafetyRelevantChgAffcdObjType = '04'
// direct Chemical Approval
union select from I_SafetyRelevantChgAffcdObj as AffectedObject
inner join I_ChemicalApproval as ChemicalApproval on AffectedObject.ChemicalApprovalUUID = ChemicalApproval.ChemicalApprovalUUID
{
key SafetyRelevantChgAffcdObjUUID,
key ChemicalApproval.CreatedByUser as SafetyRelevantChgNotifProcgUsr,
SafetyRelevantChgAffcdObjType
}
where SafetyRelevantChgAffcdObjType = '04'
// Chemical Approval via role
union select distinct from I_SafetyRelevantChgAffcdObj as AffectedObject
inner join I_ChemicalApprovalRole as ChemicalApprovalRole on AffectedObject.ChemicalApprovalUUID = ChemicalApprovalRole.ChemicalApprovalUUID
inner join I_ProcCtrlChemicalBasicInfo as BasicInfo on ChemicalApprovalRole.ProcessControlChemicalUUID = BasicInfo.ProcessControlChemicalUUID
{
key SafetyRelevantChgAffcdObjUUID,
key substring( BasicInfo.ChemicalApprovalProcessor,2,12 ) as SafetyRelevantChgNotifProcgUsr,
SafetyRelevantChgAffcdObjType
}
where SafetyRelevantChgAffcdObjType = '04'
union select distinct from I_SafetyRelevantChgAffcdObj as AffectedObject
inner join I_ChemicalApprovalLocation as ChemicalApprovalLocation on AffectedObject.ChemicalApprovalUUID = ChemicalApprovalLocation.ChemicalApprovalUUID
inner join I_EHSLocationResponsible as Responsible on ChemicalApprovalLocation.EHSLocationUUID = Responsible.EHSLocationUUID
and Responsible.EHSLocationPersonRespRole = 'SAP_EHSM_HSS_SFTY_CHNGE_RECEIV'
{
key SafetyRelevantChgAffcdObjUUID,
key substring( Responsible.EHSLocRespTechnicianID,2,12 ) as SafetyRelevantChgNotifProcgUsr,
SafetyRelevantChgAffcdObjType
}
where SafetyRelevantChgAffcdObjType = '04'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CHEMICALAPPROVAL",
"I_CHEMICALAPPROVALLOCATION",
"I_CHEMICALAPPROVALROLE",
"I_EHSLOCATIONRESPONSIBLE",
"I_EHSRISKASSESSMENT",
"I_EHSRISKASSESSMENTTEAM",
"I_HZDSSUBSTINVENTORY",
"I_PROCCTRLCHEMICALBASICINFO",
"I_SAFETYINSTRUCTION",
"I_SAFETYRELEVANTCHGAFFCDOBJ"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/