with c2
as
(SELECT  f2.module_id,I2.rec_unit_id, I2.origin, f2.object_type 
                                      from g_cashitem I2 INNER JOIN f_followup f2 on I2.item_id=f2.object_id
                                                          INNER JOIN g_rec_unit r2 ON (I2.rec_unit_id = r2.rec_unit_id)
)                                                          
SELECT /*+ NO_QUERY_TRANSFORMATION */XMLAGG (XMLELEMENT ("Items1",
                           xmlattributes ( rec_unit_id, COUNT ( *) AS total),
                           (SELECT XMLAGG (XMLELEMENT ("Items2",
                                                       xmlattributes ( c2.rec_unit_id,c2.object_type, COUNT ( *) AS total),
                                                       (SELECT XMLAGG ( XMLELEMENT ( "Items3", xmlattributes ( rec_unit_id,object_type,origin, COUNT ( *) AS total) 
                                                                    ))
                                                          FROM ( SELECT  f1.module_id,I3.rec_unit_id, I3.origin, f1.object_type 
                                                            from g_cashitem I3 INNER JOIN f_followup f1 on I3.item_id=f1.object_id
                                                          INNER JOIN g_rec_unit r ON (I3.rec_unit_id = r.rec_unit_id)
                                                          )c3
                                                        WHERE  c3.rec_unit_id = c1.rec_unit_id
                                                        AND c3.object_type=c2.object_type
                                                        GROUP BY rec_unit_id,object_type,origin)
                                                      )
                                          )
                              FROM c2                         
                           GROUP BY rec_unit_id,object_type
                           )
                          )
              )
  FROM g_cashitem c1
 WHERE page_id = 11927
GROUP BY rec_unit_id;

CREATE TABLE G_REC_UNIT
(
  REC_UNIT_ID          NUMBER(38)               NOT NULL
);

CREATE TABLE G_CASHITEM
(
  ITEM_ID                 NUMBER(38)            NOT NULL,
  REC_UNIT_ID             NUMBER(38)            NOT NULL,
  PAGE_ID                 NUMBER(38),
  ORIGIN                  VARCHAR2(1 BYTE)      NOT NULL
);

CREATE TABLE F_FOLLOWUP
(
  MODULE_ID                 VARCHAR2(20 BYTE)   NOT NULL,
  OBJECT_TYPE               VARCHAR2(1 BYTE)    NOT NULL,
  OBJECT_ID                 INTEGER             NOT NULL
);

INSERT INTO G_REC_UNIT(REC_UNIT_ID) VALUES(1);

INSERT INTO G_CASHITEM(ITEM_ID,REC_UNIT_ID,PAGE_ID,ORIGIN) VALUES(10405,1,11927,'N');

INSERT INTO G_CASHITEM(ITEM_ID,REC_UNIT_ID,PAGE_ID,ORIGIN) VALUES(10399,1,11927,'N');

INSERT INTO G_CASHITEM(ITEM_ID,REC_UNIT_ID,PAGE_ID,ORIGIN) VALUES(10400,1,11927,'N');

INSERT INTO F_FOLLOWUP( MODULE_ID,OBJECT_TYPE,OBJECT_ID) VALUES('M1','I',10405);

INSERT INTO F_FOLLOWUP( MODULE_ID,OBJECT_TYPE,OBJECT_ID) VALUES('M1','I',10400);

INSERT INTO F_FOLLOWUP( MODULE_ID,OBJECT_TYPE,OBJECT_ID) VALUES('M1','I',10399);