GMS_AWARD_IMPORT_OVERVIEW

Details

Schema: FUSION
Object owner: GMS
Object type: VIEW

Columns

Name
LOAD_REQUEST_ID NODE_TYPE CURRENT_LEVEL PARENT_LEVEL SUCCESS_COUNT FAILURE_COUNT SKIPPED_COUNT

Name

LOAD_REQUEST_ID

NODE_TYPE

CURRENT_LEVEL

PARENT_LEVEL

SUCCESS_COUNT

FAILURE_COUNT

SKIPPED_COUNT

Query

SQL_Statement
SELECT a.load_request_id,'Award Header' Node_Type,1 AS current_level,NULL AS parent_level,(SELECT COUNT(b.award_interface_id) FROM gms_award_headers_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count,(SELECT COUNT(c.award_interface_id) FROM gms_award_headers_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count,(SELECT COUNT(d.award_interface_id) FROM gms_award_headers_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id) AS skipped_count FROM gms_award_headers_int a GROUP BY load_request_id union all SELECT a.load_request_id, 'Award Keywords' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.award_keyword_interface_id) FROM gms_award_keywords_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.award_keyword_interface_id) FROM gms_award_keywords_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id ) AS failure_count, (SELECT COUNT(d.award_keyword_interface_id) FROM gms_award_keywords_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id) AS skipped_count FROM gms_award_keywords_int a group by load_request_id union all SELECT a.load_request_id, 'Award Reference Types' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.award_ref_interface_id) FROM gms_award_references_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.award_ref_interface_id) FROM gms_award_references_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.award_ref_interface_id) FROM gms_award_references_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id) AS skipped_count FROM gms_award_references_int a group by load_request_id union all SELECT a.load_request_id, 'Award Certifications' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.award_cert_interface_id) FROM gms_award_certs_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.award_cert_interface_id) FROM gms_award_certs_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.award_cert_interface_id) FROM gms_award_certs_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_certs_int a group by load_request_id union all SELECT a.load_request_id, 'Award Terms' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.award_term_interface_id) FROM gms_award_terms_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.award_term_interface_id) FROM gms_award_terms_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.award_term_interface_id) FROM gms_award_terms_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_terms_int a group by load_request_id union all SELECT a.load_request_id, 'Award CFDA' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.award_cfda_interface_id) FROM gms_award_cfdas_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.award_cfda_interface_id) FROM gms_award_cfdas_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.award_cfda_interface_id) FROM gms_award_cfdas_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_cfdas_int a group by load_request_id union all SELECT a.load_request_id, 'Award Budget Periods' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.BDGT_PRD_INTERFACE_ID) FROM gms_award_bdgt_prds_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.BDGT_PRD_INTERFACE_ID) FROM gms_award_bdgt_prds_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.BDGT_PRD_INTERFACE_ID) FROM gms_award_bdgt_prds_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_bdgt_prds_int a group by load_request_id union all SELECT a.load_request_id, 'Award Department Credits' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.AWARD_DEPT_CREDIT_ID) FROM gms_award_dept_crdts_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.AWARD_DEPT_CREDIT_ID) FROM gms_award_dept_crdts_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.AWARD_DEPT_CREDIT_ID) FROM gms_award_dept_crdts_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_dept_crdts_int a group by load_request_id union all SELECT a.load_request_id, 'Award Personnel' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.AWARD_PERS_INTERFACE_ID) FROM gms_award_personnel_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.AWARD_PERS_INTERFACE_ID) FROM gms_award_personnel_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.AWARD_PERS_INTERFACE_ID) FROM gms_award_personnel_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_personnel_int a group by load_request_id union all SELECT a.load_request_id, 'Award Projects' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.AWARD_PROJ_INTERFACE_ID) FROM gms_award_projects_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.AWARD_PROJ_INTERFACE_ID) FROM gms_award_projects_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.AWARD_PROJ_INTERFACE_ID) FROM gms_award_projects_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_projects_int a group by load_request_id

SQL_Statement

SELECT a.load_request_id,'Award Header' Node_Type,1 AS current_level,NULL AS parent_level,(SELECT COUNT(b.award_interface_id) FROM gms_award_headers_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count,(SELECT COUNT(c.award_interface_id) FROM gms_award_headers_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count,(SELECT COUNT(d.award_interface_id) FROM gms_award_headers_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id) AS skipped_count FROM gms_award_headers_int a GROUP BY load_request_id

union all

SELECT a.load_request_id, 'Award Keywords' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.award_keyword_interface_id) FROM gms_award_keywords_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.award_keyword_interface_id) FROM gms_award_keywords_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id ) AS failure_count, (SELECT COUNT(d.award_keyword_interface_id) FROM gms_award_keywords_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id) AS skipped_count FROM gms_award_keywords_int a group by load_request_id

union all

SELECT a.load_request_id, 'Award Reference Types' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.award_ref_interface_id) FROM gms_award_references_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.award_ref_interface_id) FROM gms_award_references_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.award_ref_interface_id) FROM gms_award_references_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id) AS skipped_count FROM gms_award_references_int a group by load_request_id

union all

SELECT a.load_request_id, 'Award Certifications' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.award_cert_interface_id) FROM gms_award_certs_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.award_cert_interface_id) FROM gms_award_certs_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.award_cert_interface_id) FROM gms_award_certs_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_certs_int a group by load_request_id

union all

SELECT a.load_request_id, 'Award Terms' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.award_term_interface_id) FROM gms_award_terms_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.award_term_interface_id) FROM gms_award_terms_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.award_term_interface_id) FROM gms_award_terms_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_terms_int a group by load_request_id

union all

SELECT a.load_request_id, 'Award CFDA' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.award_cfda_interface_id) FROM gms_award_cfdas_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.award_cfda_interface_id) FROM gms_award_cfdas_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.award_cfda_interface_id) FROM gms_award_cfdas_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_cfdas_int a group by load_request_id

union all

SELECT a.load_request_id, 'Award Budget Periods' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.BDGT_PRD_INTERFACE_ID) FROM gms_award_bdgt_prds_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.BDGT_PRD_INTERFACE_ID) FROM gms_award_bdgt_prds_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.BDGT_PRD_INTERFACE_ID) FROM gms_award_bdgt_prds_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_bdgt_prds_int a group by load_request_id

union all

SELECT a.load_request_id, 'Award Department Credits' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.AWARD_DEPT_CREDIT_ID) FROM gms_award_dept_crdts_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.AWARD_DEPT_CREDIT_ID) FROM gms_award_dept_crdts_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.AWARD_DEPT_CREDIT_ID) FROM gms_award_dept_crdts_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_dept_crdts_int a group by load_request_id

union all

SELECT a.load_request_id, 'Award Personnel' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.AWARD_PERS_INTERFACE_ID) FROM gms_award_personnel_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.AWARD_PERS_INTERFACE_ID) FROM gms_award_personnel_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.AWARD_PERS_INTERFACE_ID) FROM gms_award_personnel_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_personnel_int a group by load_request_id

union all

SELECT a.load_request_id, 'Award Projects' Node_Type,2 as current_level,1 as parent_level, (SELECT COUNT(b.AWARD_PROJ_INTERFACE_ID) FROM gms_award_projects_int b WHERE processed_status='SUCCESS' and b.load_request_id=a.load_request_id) AS success_count, (SELECT COUNT(c.AWARD_PROJ_INTERFACE_ID) FROM gms_award_projects_int c WHERE processed_status='FAILURE' and c.load_request_id=a.load_request_id) AS failure_count, (SELECT COUNT(d.AWARD_PROJ_INTERFACE_ID) FROM gms_award_projects_int d WHERE processed_status='NEW' and d.load_request_id=a.load_request_id ) AS skipped_count FROM gms_award_projects_int a group by load_request_id