VIEW: SYS.HS_ALL_CAPS

Source

View Text - Preformatted

select ic.cap_number, ic.context, ic.translation, ic.additional_info,
  fc.fds_class_name, fi.fds_inst_name
from hs$_inst_caps ic, hs$_fds_inst fi, hs$_fds_class fc
where ic.fds_inst_id = fi.fds_inst_id
and fi.fds_class_id = fc.fds_class_id
union
/*clause for the fds_class level minus the fds_inst level*/
select cc.cap_number, cc.context, cc.translation, cc.additional_info,
  fc2.fds_class_name, fi2.fds_inst_name
from hs$_class_caps cc, hs$_fds_inst fi2, hs$_fds_class fc2
where cc.fds_class_id = fi2.fds_class_id
and fi2.fds_class_id = fc2.fds_class_id
and not exists
  (select 1 from hs$_inst_caps ic2
   where ic2.cap_number = cc.cap_number
   and ic2.fds_inst_id = fi2.fds_inst_id)
union
/*clause for the base level minus fds_inst and fds_class level*/
select bc.cap_number, 0, null, 0, fc.fds_class_name,
  fi.fds_inst_name
from hs$_base_caps bc, hs$_fds_class fc, hs$_fds_inst fi
where fc.fds_class_id = fi.fds_class_id
and not exists
  (select 1 from hs$_inst_caps ic
   where ic.fds_inst_id = fi.fds_inst_id
   and ic.cap_number = bc.cap_number)
and not exists
  (select 1 from hs$_class_caps cc
   where cc.fds_class_id = fc.fds_class_id
   and cc.cap_number = bc.cap_number)

View Text - HTML Formatted

SELECT IC.CAP_NUMBER
, IC.CONTEXT
, IC.TRANSLATION
, IC.ADDITIONAL_INFO
, FC.FDS_CLASS_NAME
, FI.FDS_INST_NAME FROM HS$_INST_CAPS IC
, HS$_FDS_INST FI
, HS$_FDS_CLASS FC WHERE IC.FDS_INST_ID = FI.FDS_INST_ID AND FI.FDS_CLASS_ID = FC.FDS_CLASS_ID UNION /*CLAUSE FOR THE FDS_CLASS LEVEL MINUS THE FDS_INST LEVEL*/ SELECT CC.CAP_NUMBER
, CC.CONTEXT
, CC.TRANSLATION
, CC.ADDITIONAL_INFO
, FC2.FDS_CLASS_NAME
, FI2.FDS_INST_NAME FROM HS$_CLASS_CAPS CC
, HS$_FDS_INST FI2
, HS$_FDS_CLASS FC2 WHERE CC.FDS_CLASS_ID = FI2.FDS_CLASS_ID AND FI2.FDS_CLASS_ID = FC2.FDS_CLASS_ID AND NOT EXISTS (SELECT 1
FROM HS$_INST_CAPS IC2
WHERE IC2.CAP_NUMBER = CC.CAP_NUMBER
AND IC2.FDS_INST_ID = FI2.FDS_INST_ID) UNION /*CLAUSE FOR THE BASE LEVEL MINUS FDS_INST
AND FDS_CLASS LEVEL*/ SELECT BC.CAP_NUMBER
, 0
, NULL
, 0
, FC.FDS_CLASS_NAME
, FI.FDS_INST_NAME FROM HS$_BASE_CAPS BC
, HS$_FDS_CLASS FC
, HS$_FDS_INST FI WHERE FC.FDS_CLASS_ID = FI.FDS_CLASS_ID AND NOT EXISTS (SELECT 1
FROM HS$_INST_CAPS IC
WHERE IC.FDS_INST_ID = FI.FDS_INST_ID
AND IC.CAP_NUMBER = BC.CAP_NUMBER) AND NOT EXISTS (SELECT 1
FROM HS$_CLASS_CAPS CC
WHERE CC.FDS_CLASS_ID = FC.FDS_CLASS_ID
AND CC.CAP_NUMBER = BC.CAP_NUMBER)