CREATE OR REPLACE TYPE ProtocolType AS OBJECT ("ID" VARCHAR2(100),
                                                    "Comment" CLOB, "CHECKIN" VARCHAR2(255))
CREATE OR REPLACE TYPE ProtocolTableType AS TABLE OF ProtocolType
CREATE OR REPLACE VIEW ProtocolView AS
SELECT CAST(MULTISET
              (SELECT t."ID", t."COMMENTS", t.atc_portal_checkin
               FROM HPCPROTOCOLM1 t) AS ProtocolTableType) ppdd
FROM dual
SELECT VALUE(p)
FROM TABLE
  (SELECT ppdd
   FROM ProtocolView) p
SELECT *
FROM protocolview
SELECT VALUE(p).ProtocolComment
FROM TABLE
  (SELECT ppdd
   FROM ProtocolView) p
CREATE OR REPLACE FUNCTION get_mass_inc (userid IN varchar2) RETURN ProtocolTableType AS v_ret ProtocolTableType;

 BEGIN
SELECT CAST ( multiset (
SELECT CAST(MULTISET
              (SELECT t."ID", t."COMMENTS", t.atc_portal_checkin
               FROM HPCPROTOCOLM1 t) AS ProtocolTableType) ppdd INTO v_ret
FROM dual;

 RETURN v_ret;

 END get_mass_inc;

 ----------------------------

CREATE OR REPLACE TYPE mass_inc_type AS OBJECT ("IncidentID" VARCHAR2(100),
                                                    InteractionID VARCHAR2(10),
                                                     "STATUS" VARCHAR2(60),
                                                     ASSIGNMENT VARCHAR2(255),
                                                     ASSIGNEE VARCHAR2(255),
                                                     DESCRIPTION CLOB, CI VARCHAR2(255),
                                                     SERVICE VARCHAR2(255),
                                                     "PRIORITY" VARCHAR2(40),
                                                     "CATEGORY" VARCHAR2(60),
                                                     SOLUTION CLOB,
                                                     CREATED_DATE DATE,
                                                     ACTIVE CHAR(1),
                                                     DATE_DEADLINE DATE)
CREATE OR REPLACE TYPE mass_inc_table_type AS TABLE OF mass_inc_type
CREATE OR REPLACE FUNCTION get_mass_inc (userid IN varchar2) RETURN mass_inc_table_type AS v_ret mass_inc_table_type;

 BEGIN
SELECT CAST(MULTISET (
SELECT i."NUMBER" AS "ID",
       i4.IntId AS "InteractionID",
       i2.HPC_STATUS AS "STATUS",
       i2.HPC_ASSIGNMENT AS "ASSIGNMENT",
       i2.HPC_ASSIGNEE AS "ASSIGNEE",
       i.ACTION AS "DESCRIPTION",
       i.logical_name AS "CI",
       i2.affected_item AS "SERVICE",
       i.priority_code AS "PRIORITY",
       i.CATEGORY AS "CATEGORY",
       i.resolution AS "SOLUTION",
       i.open_time AS "CREATED_DATE",
       i.flag AS "ACTIVE",
       i3.hpc_next_breach
FROM probsummarym1 i
LEFT OUTER JOIN PROBSUMMARYM2 i2 ON i2."NUMBER"=i."NUMBER"
LEFT OUTER JOIN PROBSUMMARYM3 i3 ON i3."NUMBER"=i."NUMBER"
LEFT OUTER JOIN
  (SELECT t1.INCIDENT_ID AS IntId,
          t2.ITC_RELATEDINCID AS RelInc
   FROM INCIDENTSM1 t1
   LEFT OUTER JOIN INCIDENTSM2 t2 ON t1.INCIDENT_ID=t2.INCIDENT_ID
   WHERE t1.CONTACT_NAME=userid) i4 ON i4.RelInc=i."NUMBER"
WHERE i3.HPC_IS_MASS='t'

 ) AS mass_inc_table_type) INTO v_ret
FROM dual;

 RETURN v_ret;

 END get_mass_inc;