GMS_AWARD_IMPORT_SUMMARY_V

Details

• Schema: FUSION

• Object owner: GMS

• Object type: VIEW

Columns

Name
LOAD_REQUEST_ID AWARD_NAME AWARD_NUMBER NODE_TYPE CURRENT_LEVEL PARENT_LEVEL FAILURE_COUNT

Query

SQL_Statement

SELECT b.load_request_id,b.award_name,b.award_number,b.award_name ||' ('||b.award_number||')' AS node_type, 1 AS current_level, NULL AS parent_level,(SELECT COUNT(a.award_number) FROM gms_award_headers_int a WHERE processed_status='FAILURE' and a.load_request_id=b.load_request_id group by load_request_id) AS failure_count FROM gms_award_headers_int b where b.processed_status='FAILURE' GROUP BY load_request_id, award_number, award_name union all SELECT b.load_request_id,null as award_name,b.award_number,'Award Keyword' as sub_node_type,2 as current_level,1 as parent_level, (SELECT COUNT(a.award_keyword_interface_id) FROM gms_award_keywords_int a WHERE a.processed_status='FAILURE' ) AS failure_count FROM gms_award_keywords_int b group by load_request_id,award_number union all SELECT b.load_request_id,null as award_name,b.award_number,'Award Reference Types' as sub_node_type,2 as current_level,1 as parent_level, (SELECT COUNT(a.award_ref_interface_id) FROM gms_award_references_int a WHERE a.processed_status='FAILURE' ) AS failure_count FROM gms_award_references_int b group by load_request_id,award_number union all SELECT b.load_request_id,null as award_name,b.award_number,'Award Certifications' as sub_node_type,2 as current_level,1 as parent_level, (SELECT COUNT(a.award_cert_interface_id) FROM gms_award_certs_int a WHERE a.processed_status='FAILURE' ) AS failure_count FROM gms_award_certs_int b group by load_request_id,award_number union all SELECT b.load_request_id,null as award_name,b.award_number,'Award Terms' as sub_node_type,2 as current_level,1 as parent_level, (SELECT COUNT(a.award_term_interface_id) FROM gms_award_terms_int a WHERE a.processed_status='FAILURE' ) AS failure_count FROM gms_award_terms_int b group by load_request_id,award_number union all SELECT b.load_request_id,null as award_name,b.award_number,'Award CFDA' as sub_node_type,2 as current_level,1 as parent_level, (SELECT COUNT(a.award_cfda_interface_id) FROM gms_award_cfdas_int a WHERE a.processed_status='FAILURE' ) AS failure_count FROM gms_award_cfdas_int b group by load_request_id,award_number union all SELECT b.load_request_id,null as award_name,b.award_number,'Award Budget Periods' as sub_node_type,2 as current_level,1 as parent_level, (SELECT COUNT(a.BDGT_PRD_INTERFACE_ID) FROM gms_award_bdgt_prds_int a WHERE a.processed_status='FAILURE' ) AS failure_count FROM gms_award_bdgt_prds_int b group by load_request_id,award_number union all SELECT b.load_request_id,null as award_name,b.award_number,'Award Department Credits' as sub_node_type,2 as current_level,1 as parent_level, (SELECT COUNT(a.AWARD_DEPT_CREDIT_ID) FROM gms_award_dept_crdts_int a WHERE a.processed_status='FAILURE' ) AS failure_count FROM gms_award_dept_crdts_int b group by load_request_id,award_number union all SELECT b.load_request_id,null as award_name,b.award_number,'Award Personnel' as sub_node_type,2 as current_level,1 as parent_level, (SELECT COUNT(a.AWARD_PERS_INTERFACE_ID) FROM gms_award_personnel_int a WHERE a.processed_status='FAILURE' ) AS failure_count FROM gms_award_personnel_int b group by load_request_id,award_number union all SELECT b.load_request_id,null as award_name,b.award_number,'Award Projects' as sub_node_type,2 as current_level,1 as parent_level, (SELECT COUNT(a.AWARD_PROJ_INTERFACE_ID) FROM gms_award_projects_int a WHERE a.processed_status='FAILURE' ) AS failure_count FROM gms_award_projects_int b group by load_request_id,award_number