fedspendingtransparency/usaspending-api

DROP VIEW IF EXISTS covid19_faba_view;
CREATE VIEW covid19_faba_view AS
  SELECT
    faba.financial_accounts_by_awards_id,
    faba.piid,
    faba.parent_award_id,
    faba.fain,
    faba.uri,
    faba.ussgl480100_undelivered_orders_obligations_unpaid_fyb,
    faba.ussgl480100_undelivered_orders_obligations_unpaid_cpe,
    faba.ussgl483100_undelivered_orders_oblig_transferred_unpaid_cpe,
    faba.ussgl488100_upward_adjust_pri_undeliv_order_oblig_unpaid_cpe,
    faba.ussgl490100_delivered_orders_obligations_unpaid_fyb,
    faba.ussgl490100_delivered_orders_obligations_unpaid_cpe,
    faba.ussgl493100_delivered_orders_oblig_transferred_unpaid_cpe,
    faba.ussgl498100_upward_adjust_pri_deliv_orders_oblig_unpaid_cpe,
    faba.ussgl480200_undelivered_orders_oblig_prepaid_advanced_fyb,
    faba.ussgl480200_undelivered_orders_oblig_prepaid_advanced_cpe,
    faba.ussgl483200_undeliv_orders_oblig_transferred_prepaid_adv_cpe,
    faba.ussgl488200_up_adjust_pri_undeliv_order_oblig_ppaid_adv_cpe,
    faba.ussgl490200_delivered_orders_obligations_paid_cpe,
    faba.ussgl490800_authority_outlayed_not_yet_disbursed_fyb,
    faba.ussgl490800_authority_outlayed_not_yet_disbursed_cpe,
    faba.ussgl498200_upward_adjust_pri_deliv_orders_oblig_paid_cpe,
    faba.obligations_undelivered_orders_unpaid_total_cpe,
    faba.obligations_delivered_orders_unpaid_total_fyb,
    faba.obligations_delivered_orders_unpaid_total_cpe,
    faba.gross_outlays_undelivered_orders_prepaid_total_fyb,
    faba.gross_outlays_undelivered_orders_prepaid_total_cpe,
    faba.gross_outlays_delivered_orders_paid_total_fyb,
    faba.gross_outlay_amount_by_award_fyb,
    faba.gross_outlay_amount_by_award_cpe,
    faba.obligations_incurred_total_by_award_cpe,
    faba.ussgl487100_down_adj_pri_unpaid_undel_orders_oblig_recov_cpe,
    faba.ussgl497100_down_adj_pri_unpaid_deliv_orders_oblig_recov_cpe,
    faba.ussgl487200_down_adj_pri_ppaid_undel_orders_oblig_refund_cpe,
    faba.ussgl497200_down_adj_pri_paid_deliv_orders_oblig_refund_cpe,
    faba.deobligations_recoveries_refunds_of_prior_year_by_award_cpe,
    faba.obligations_undelivered_orders_unpaid_total_fyb,
    faba.gross_outlays_delivered_orders_paid_total_cpe,
    faba.drv_award_id_field_type,
    faba.drv_obligations_incurred_total_by_award,
    faba.transaction_obligated_amount,
    faba.reporting_period_start,
    faba.reporting_period_end,
    faba.last_modified_date,
    faba.certified_date,
    faba.create_date,
    faba.update_date,
    faba.award_id,
    faba.object_class_id,
    faba.program_activity_id,
    faba.submission_id,
    faba.treasury_account_id,
    faba.disaster_emergency_fund_code,
    faba.distinct_award_key AS financial_account_distinct_award_key,
    sa.submission_window_id,
    sa.is_final_balances_for_fy,
    dabs.submission_fiscal_year,
    dabs.submission_fiscal_quarter,
    dabs.submission_fiscal_month,
    dabs.is_quarter,
    dabs.period_start_date,
    dabs.period_end_date,
    a.id AS funding_toptier_agency_id,
    top_a.toptier_code AS funding_toptier_agency_code,
    top_a.name AS funding_toptier_agency_name,
    taa.tas_rendering_label AS treasury_account_symbol,
    taa.account_title AS treasury_account_title,
    taa.federal_account_id,
    fa.federal_account_code AS federal_account_symbol,
    fa.account_title AS federal_account_title,
    pa.program_activity_code,
    pa.program_activity_name,
    oc.major_object_class,
    oc.major_object_class_name,
    oc.object_class,
    oc.object_class_name,
    oc.direct_reimbursable,
    defc.group_name AS disaster_emergency_fund_code_group_name,
    awd.total_loan_value,
    awd.type,
    awd.generated_unique_award_id
   FROM financial_accounts_by_awards faba
     JOIN submission_attributes sa ON sa.reporting_period_start >= '2020-04-01'::date AND sa.submission_id = faba.submission_id
     JOIN disaster_emergency_fund_code defc ON defc.group_name = 'covid_19'::text AND defc.code::text = faba.disaster_emergency_fund_code::text
     JOIN dabs_submission_window_schedule dabs ON dabs.submission_reveal_date <= now() AND dabs.id = sa.submission_window_id
     LEFT JOIN treasury_appropriation_account taa ON taa.treasury_account_identifier = faba.treasury_account_id
     LEFT JOIN toptier_agency top_a ON top_a.toptier_agency_id = taa.funding_toptier_agency_id
     LEFT JOIN agency a ON a.toptier_agency_id = top_a.toptier_agency_id AND a.toptier_flag = true
     LEFT JOIN federal_account fa ON fa.id = taa.federal_account_id
     LEFT JOIN ref_program_activity pa ON pa.id = faba.program_activity_id
     LEFT JOIN object_class oc ON oc.id = faba.object_class_id
     LEFT JOIN vw_awards awd ON awd.id = faba.award_id
;