CREATE OR REPLACE TYPE xx_note_typ force AS OBJECT 
( 
  note_id      NUMBER, 
  note_scope   NUMBER, 
  note_value   VARCHAR2(4000), 
  note_date    VARCHAR2(100), 
  CONSTRUCTOR FUNCTION xx_note_typ RETURN SELF AS RESULT 
) 

CREATE OR REPLACE TYPE xx_note_arr_typ FORCE AS TABLE OF xx_note_typ; 

create table xx_CUSTOMER_NOTE_ALL 
( 
  id         NUMBER not null, 
  agent_id   NUMBER, 
  debtor_id  VARCHAR2(10), 
  serving_id NUMBER, 
  note_value VARCHAR2(4000), 
  note_scope NUMBER, 
  reg_date   TIMESTAMP(6) WITH TIME ZONE not null, 
  system_id  NUMBER not null, 
  ts         TIMESTAMP(6) WITH TIME ZONE not null 
) 
compress 
nologging

create index xx_CUST_AOUEHXUZSOBKLD on xx_CUSTOMER_NOTE_ALL (SYSTEM_ID, AGENT_ID) 
  nologging

create index xx_CUST_EQXQQPPGHXOJNJ on xx_CUSTOMER_NOTE_ALL (SYSTEM_ID, DEBTOR_ID) 
  nologging

create index xx_CUST_YOXJJKMIJNEGHZ on xx_CUSTOMER_NOTE_ALL (SYSTEM_ID, SERVING_ID) 
  nologging

alter table xx_CUSTOMER_NOTE_ALL 
  add primary key (ID) 

DECLARE 
  l_result xx_note_arr_typ; 
BEGIN 
  SELECT xx_note_typ((SELECT icn.id 
                       FROM dual) 
                    ,(SELECT icn.note_scope 
                       FROM dual) 
                    ,(SELECT icn.note_value 
                       FROM dual) 
                    ,(SELECT icn.reg_date 
                       FROM dual)) 
    BULK COLLECT 
    INTO l_result 
    FROM xx_customer_note_all icn 
   WHERE (icn.note_scope = 0 AND icn.serving_id = 42) 
      OR (icn.note_scope = 1 AND icn.debtor_id = '42'); 
END;