FUNCTION getTopUserGroupsPerFirm(v_FirmGUID IN VARCHAR, v_ContentSet IN VARCHAR, d_StartDate IN DATE, d_EndDate IN DATE)
   RETURN CLOB
IS
	TYPE data_curs_tab IS TABLE OF SYS.XMLTYPE; -- Added for declaring XMLTYPE Datatype
    	
    	t_data_val data_curs_tab; --Variable for data_curs_tab
	CURSOR data_curs IS
		SELECT XMLForest(ut.user_group_guid as "UserGroupGUID",ut.usergroupname as "UserGroupName", Count(*) as "NumOfTransactions", SUM(ut.internal_price) as "DollarsSpent") as xml_row
		FROM usertransaction ut
		WHERE ut.firm_guid = v_firmGuid
		AND (EVENT_DATETIME between d_StartDate and d_EndDate )
		AND ut.content_type = v_ContentSet
		GROUP by ut.user_group_guid,ut.usergroupname
  		  ORDER BY SUM(ut.internal_price) DESC;
	v_result CLOB;
	v_nodename VARCHAR(64);
BEGIN

v_nodename := 'TopUserGroups_' || v_ContentSet;
	OPEN data_curs;
	 FETCH data_curs bulk collect INTO t_data_val;
	CLOSE data_curs;
	
	FOR i IN t_data_val.first..t_data_val.last LOOP
			v_result := v_result
					|| '<UserGroup>'
					|| t_data_val(i).getClobVal()
					|| '</UserGroup>';
	END LOOP;	
	
RETURN '<' || v_nodename || '>' || v_result || '</' || v_nodename  || '>';

EXCEPTION
WHEN OTHERS THEN
log_oracle_error(SQLCODE,SQLERRM,'Function UsageSummary_Test.getTopUserGroupsPerFirm throws an Exception',SYSDATE,NULL);
      	RETURN '';
END;