C_SubstanceQryTP

Derived SQL interpretation, reconstructed from the parsed view metadata (data sources, associations, and field mappings). SAP annotations are omitted and the structure is reformulated as SQL — this is a functional approximation, not the verbatim SAP source.

-- Derived SQL interpretation of CDS view C_SubstanceQryTP.
-- Reconstructed from parsed metadata (data sources, associations, fields).
-- SAP annotations are omitted and the structure is reformulated as SQL;
-- this is a functional approximation, not the verbatim SAP source. Some join
-- conditions may be unavailable and a few CDS constructs are kept as-is.

CREATE VIEW C_SubstanceQryTP AS
SELECT
  Substance.SubstanceUUID AS SubstanceUUID,
  Substance.ActiveSubstanceUUID AS ActiveSubstanceUUID,
  Substance.SubstanceName AS SubstanceName,
  Substance.SubstanceInternalName AS SubstanceInternalName,
  cast(Substance._ListedSubstance.ListedSubstanceDefaultName as ehfnd_lisu_name_text_long) AS ListedSubstanceName,
  Substance._ListedSubstance.CntntListedSubstanceDfltName AS CntntListedSubstanceDfltName,
  Substance._ListedSubstance.CustListedSubstanceDfltName AS CustListedSubstanceDfltName,
  Substance.CompanySubstanceName AS CompanySubstanceName,
  Substance.ListedSubstanceUUID AS ListedSubstanceUUID,
  Substance._ListedSubstance.ListedSubstance AS ListedSubstance,
  Substance._ListedSubstance.CntntListedSubstance AS CntntListedSubstance,
  Substance._ListedSubstance.CustListedSubstance AS CustListedSubstance,
  Substance._ListedSubstance.CASNumber AS CASNumber,
  Substance._ListedSubstance.CntntCASNumber AS CntntCASNumber,
  Substance._ListedSubstance.CustCASNumber AS CustCASNumber,
  Substance._ListedSubstance.ECNumber AS ECNumber,
  Substance._ListedSubstance.CntntECNumber AS CntntECNumber,
  Substance._ListedSubstance.CustECNumber AS CustECNumber,
  Substance._ListedSubstance.MolecularFormula AS MolecularFormula,
  Substance._ListedSubstance.CntntMolecularFormula AS CntntMolecularFormula,
  Substance._ListedSubstance.CustMolecularFormula AS CustMolecularFormula,
  Substance.ResponsibleUnit AS ResponsibleUnit,
  _ResponsibleUnit.ResponsibleUnitName AS ResponsibleUnitName,
  Substance.CreationUTCDateTime AS CreationUTCDateTime,
  Substance.CreatedByUser AS CreatedByUser,
  Substance.LastChangeUTCDateTime AS LastChangeUTCDateTime,
  Substance.LastChangedByUser AS LastChangedByUser,
  Substance.Specification AS Specification,
  _ListedSubstanceVariantsCt.VariantCount AS NmbrOfAvailLstdSubstVariants,
  _ListedSubstanceVarAssCt.VariantAssignmentCount AS NmbrOfAssgdLstdSubstVariants,
  cast( 'X' as ehfnd_boolean ) AS SubstGrpAssgmtRankingIsVisible,
  _PCEctxProperty.PCPrptyNavgnLinkUUID AS PCEctxPrptyNavgnLinkUUID,
  _PCEctxProperty.PCPrptyIsActive AS PCEctxPrptyIsActive,
  _PCPhysProperty.PCPrptyNavgnLinkUUID AS PCPhysPrptyNavgnLinkUUID,
  _PCPhysProperty.PCPrptyIsActive AS PCPhysPrptyIsActive,
  _PCPToxProperty.PCPrptyNavgnLinkUUID AS PCPrptyNavgnLinkUUID,
  _PCPToxProperty.PCPrptyIsActive AS PCPrptyIsActive,
  _PCPSftyProperty.PCPrptyNavgnLinkUUID AS PCSftyPrptyNavgnLinkUUID,
  _PCPSftyProperty.PCPrptyIsActive AS PCSftyPrptyIsActive,
  Substance.PolymerIsBsdOnOECDDef AS PolymerIsBsdOnOECDDef,
  cast('' as ehfnd_sub_add_identifier ) AS SubstanceAdditionalIdfrType,
  cast('' as ehfnd_sub_add_identif_concat ) AS SubstanceAdditionalIdfrDesc
FROM I_SubstanceTP AS Substance
LEFT OUTER JOIN C_SubstanceVarAssgmtQryTP AS _SubstanceVarAssgmt ON SubstanceUUID = _SubstanceVarAssgmt.SubstanceUUID  -- association [0..*]
LEFT OUTER JOIN C_SubstanceGrpAssgmt AS _SubstanceGrpAsgmt ON listedsubstance = _SubstanceGrpAsgmt.ListedSubstance  -- association [0..*]
LEFT OUTER JOIN C_SubstanceAdditionalIdfrTP AS _SubstanceAdditionalIdfrTP ON SubstanceUUID = _SubstanceAdditionalIdfrTP.SubstanceUUID  -- association [0..*]
LEFT OUTER JOIN C_SubstanceListedSubstanceTxt AS _ListedSubPrimNames ON ListedSubstanceUUID = _ListedSubPrimNames.ListedSubstanceUUID AND ActiveSubstanceUUID = _ListedSubPrimNames.ActiveSubstanceUUID  -- association [0..*]
LEFT OUTER JOIN C_SubstanceTextTP AS _SubstanceText ON SubstanceUUID = _SubstanceText.SubstanceUUID  -- association [0..*]
LEFT OUTER JOIN C_SubstanceTradeSecretTextTP AS _SubstanceTradeSecretText ON SubstanceUUID = _SubstanceTradeSecretText.SubstanceUUID  -- association [0..*]
LEFT OUTER JOIN C_SubstanceObjPgCmplRqVers AS _SubstanceCmplRqVers ON SubstanceUUID = _SubstanceCmplRqVers.SubstanceUUID  -- association [0..*]
LEFT OUTER JOIN C_SubstanceAssgdCmpstn AS _Composition ON SubstanceUUID = _Composition.SubstanceUUID  -- association [0..*]
LEFT OUTER JOIN C_SubstanceAssgdPCPrpty AS _Properties ON _Properties.SubstanceUUID = SubstanceUUID  -- association [0..*]
LEFT OUTER JOIN P_SubstanceAssgdPCEctx AS _PCEctxProperty ON _PCEctxProperty.SubstanceUUID = SubstanceUUID  -- association [0..1]
LEFT OUTER JOIN P_SubstanceAssgdPCPhys AS _PCPhysProperty ON _PCPhysProperty.SubstanceUUID = SubstanceUUID  -- association [0..1]
LEFT OUTER JOIN P_SubstanceAssgdPCTx AS _PCPToxProperty ON _PCPToxProperty.SubstanceUUID = SubstanceUUID  -- association [0..1]
LEFT OUTER JOIN P_SubstanceAssgdPCSfty AS _PCPSftyProperty ON _PCPSftyProperty.SubstanceUUID = SubstanceUUID  -- association [0..1]
LEFT OUTER JOIN P_SubstanceVariantsCt AS _ListedSubstanceVariantsCt ON ListedSubstanceUUID = _ListedSubstanceVariantsCt.ListedSubstanceUUID  -- association [0..1]
LEFT OUTER JOIN P_SubstanceVarAssgmtCt AS _ListedSubstanceVarAssCt ON ActiveSubstanceUUID = _ListedSubstanceVarAssCt.SubstanceUUID  -- association [0..1]
LEFT OUTER JOIN I_RespUnitSubstanceVH AS _ResponsibleUnit ON ResponsibleUnit = _ResponsibleUnit.ResponsibleUnit  -- association [0..1]
;