select "MODEL_NAME", "MODEL_DESC", "MODEL_VERSION", "MODEL_ID", "CATEGORY_NAME", "CATEGORY_ID", "NODE_ID", "NODE_NAME", "NODE_TYPE", "CREATED_BY", "MODEL_STATE", "MODEL_STATUS", "APPROVE_DATE", "MODEL_CREATER", "DOCUMENT_ATTACHED", "SINGAPORE_TIME", "TRADING_PARTY_ID", "'Name'" Name, "'Description'" Description, "'System Name'" System_Name, "'Activity Type'" Active_Type, "'Module'" Module, "'Function Name'" Function_Name, "'Concurrent Program'" Concurrent_Program, "'Transmission Mode'" Transmission_Mode, "'SOX Control ID'" SOX_Control_ID, "'SOX Control'" SOX_Control, "'SOX Control Type'" SOX_Control_Type, "'SOX Owner'" SOX_Owner, "'Assignee Team Name'" Assignee_Team_Name, "'Assigned Business Analyst'" Assigned_Business_Analyst, "'Assigned Business Consultant'" Assigned_Business_Consultant, "'Assigned System Analyst'" Assigned_System_Analyst, "'Assigned Solution Architect'" Assigned_Solution_Architect, "'User Department'" User_Department, "'User Role'" User_Role, "'Site'" Site, "'Created By Name'" Created_By_Name, "'Created Date'" Created_Date, "'Last Updated By Name'" Last_Updated_By_Name, "'Last Updated Date'" Last_Updated_Date from ( select "MODEL_NAME","MODEL_DESC","MODEL_VERSION","MODEL_ID","CATEGORY_NAME","CATEGORY_ID","NODE_ID","NODE_NAME","NODE_TYPE","CREATED_BY","MODEL_STATE","MODEL_STATUS","APPROVE_DATE","MODEL_CREATER","DOCUMENT_ATTACHED","SINGAPORE_TIME","TRADING_PARTY_ID","'Name'","'Description'","'System Name'","'Activity Type'","'Module'","'Function Name'","'Concurrent Program'","'Transmission Mode'","'SOX Control ID'","'SOX Control'","'SOX Control Type'","'SOX Owner'","'Assignee Team Name'","'Assigned Business Analyst'","'Assigned Business Consultant'","'Assigned System Analyst'","'Assigned Solution Architect'","'User Department'","'User Role'","'Site'","'Created By Name'","'Created Date'","'Last Updated By Name'","'Last Updated Date'" from (select y.*, b.property_alias_name, b.prop_val from (select M1.Model_name MODEL_name, /* trim(decode(upper(substr(m1.description, 1, 2)), '<H', extractValue(xmltype(m1.description), '/html/body/text()'), m1.description)) Model_description,*/ m1.description Model_desc, m1.model_status model_Version, m1.model_id model_id, Cat.category_name Category_name, m1.category_id, n1.node_id, tgn.description Node_name, n1.node_type, M1.CREATED_BY, tgws.state_name mODEL_STATE, decode(m1.model_id, max(m1.model_id) over(partition by m1.model_name), 'Latest', 'Old') mODEL_STATUS, /* nvl(tgws.state_name, decode(m1.model_id, max(m1.model_id) over(partition by m1.model_name), 'Latest_REV', 'Old_REV')) Model_Status,*/ tbar.action_taken_date approve_date, -- tbar.action_taken_date - interval '15' hour approval_Date, (TU.FIRST_NAME || ' ' || TU.LAST_NAME) MODEL_CREATER, (select decode(count(property_name), 0, 'N', 'Y') from T_GPS_NODE_CLASS_PROPERTIES where node_id = n1.node_id and property_name = 'filename') document_attached, m1.created_date Singapore_Time, Cat.trading_party_id --, -- m1.created_date - interval '15' hour Pacific_Time from t_gps_models m1, t_gps_nodes n1, T_CATEGORIES Cat, T_BPM_APR_REQUESTS tbar, t_gps_instances tgi, T_GPS_WORKFLOW_STATES tgws, T_USERS TU, t_gps_nodetypes tgn where m1.model_id = n1.model_id and Cat.category_id = m1.category_id and n1.node_type <> 'LINE' and m1.model_id = tbar.model_id(+) and tbar.instance_id = tgi.instance_id(+) and tgi.current_state_id = tgws.state_id(+) and tgi.instance_present(+) = 1 and Cat.trading_party_id= (select trading_party_id from t_trading_party ttp where ttp.trading_party_name= 'ResMed' )--127-- Resmed Party ID --AND Cat.CATEGORY_ID = 1655 -- ResMed Models() and tgn.nodetype = n1.node_type AND M1.CREATED_BY = TU.USER_NAME(+)) y, (select tgnt.nodetype, tga.application_name, tgnsc.class_name, tgnsc.alias_name class_alias_name, tgnscp.property_name property_name, tgnscp.display, tgnscp.alias_name property_alias_name, tgnscp.tab_name, tgnsc.class_id, tgctg.tab_id, --tgpo.tab_id, tgpo.property_id prop_id, d.node_id nod_id, d.property_value prop_val from t_gps_nodetypes tgnt, t_gps_nodetype_states tgnts, t_gps_nt_state_classes tgnsc, t_gps_nt_st_class_props tgnscp, t_gps_applications tga, t_gps_class_tabgroups tgctg, t_gps_class_tabgroup_childs tgctc, t_gps_property_order tgpo, T_GPS_NODE_CLASSES c, T_GPS_NODE_CLASS_PROPERTIES d where tgnt.nodetype_id = tgnts.nodetype_id and tgnts.application_id = tga.application_id and tgnts.nodetype_state_id = tgnsc.nodetype_state_id and tgnsc.class_id = tgnscp.class_id and tgnscp.display = 'true' and tgnts.visible_flag = 'true' and tgnscp.class_id = tgctg.class_id and tgnscp.tab_name = tgctg.tab_name and tgnscp.property_name = tgpo.property and tgctg.tab_id = tgctc.childtab_id and tgctg.tab_id = tgpo.tab_id and tgnscp.display = 'true' and tga.application_name = 'Business Process Mapping' and c.class_name = 'eDynamo.classes.' || tgnsc.class_name and d.property_name = tgnscp.property_name and tgnscp.alias_name in ('Name', 'Description', 'System Name', 'Activity Type', 'Module', 'Function Name', 'Concurrent Program', 'Transmission Mode', 'SOX Control ID', 'SOX Control', 'SOX Control Type', 'SOX Owner', 'Assignee Team Name', 'Assigned Business Analyst', 'Assigned Business Consultant', 'Assigned System Analyst', 'Assigned Solution Architect', 'User Department', 'User Role', 'Site', 'Created By Name', 'Created Date', 'Last Updated By Name', 'Last Updated Date') and c.node_id = d.node_id and c.class_id = d.class_id) b where y.node_id = b.nod_id and y.node_type = b.nodetype) PIVOT(max(prop_val) FOR property_alias_name IN('Name', 'Description', 'System Name', 'Activity Type', 'Module', 'Function Name', 'Concurrent Program', 'Transmission Mode', 'SOX Control ID', 'SOX Control', 'SOX Control Type', 'SOX Owner', 'Assignee Team Name', 'Assigned Business Analyst', 'Assigned Business Consultant', 'Assigned System Analyst', 'Assigned Solution Architect', 'User Department', 'User Role', 'Site', 'Created By Name', 'Created Date', 'Last Updated By Name', 'Last Updated Date')) --order by model_name asc )