<commit_before>WITH unlinked_subs AS
    (
        SELECT id,
            prime_id,
            sub_id,
            award_id
        FROM subaward
        WHERE subaward.unique_award_key IS NULL
            AND subaward.subaward_type = 'sub-grant'),
aw_pafa AS
    (SELECT DISTINCT ON (
            UPPER(pafa.fain)
        )
        pafa.fain AS fain,
        pafa.uri AS uri,
        pafa.award_description AS award_description,
        pafa.record_type AS record_type,
        pafa.awarding_agency_code AS awarding_agency_code,
        pafa.awarding_agency_name AS awarding_agency_name,
        pafa.awarding_office_code AS awarding_office_code,
        pafa.awarding_office_name AS awarding_office_name,
        pafa.funding_agency_code AS funding_agency_code,
        pafa.funding_agency_name AS funding_agency_name,
        pafa.funding_office_code AS funding_office_code,
        pafa.funding_office_name AS funding_office_name,
        pafa.business_types_desc AS business_types_desc,
        pafa.awarding_sub_tier_agency_c AS awarding_sub_tier_agency_c,
        pafa.awarding_sub_tier_agency_n AS awarding_sub_tier_agency_n,
        pafa.funding_sub_tier_agency_co AS funding_sub_tier_agency_co,
        pafa.funding_sub_tier_agency_na AS funding_sub_tier_agency_na,
        pafa.unique_award_key AS unique_award_key
    FROM published_award_financial_assistance AS pafa
    WHERE is_active IS TRUE
        AND EXISTS (
            SELECT 1
            FROM unlinked_subs
            WHERE UPPER(TRANSLATE(unlinked_subs.award_id, '-', '')) = UPPER(TRANSLATE(pafa.fain, '-', ''))
                AND pafa.record_type != 1
        )
        {0}
    ORDER BY UPPER(pafa.fain), pafa.action_date)
UPDATE subaward
SET
    -- File F Prime Awards
    unique_award_key = aw_pafa.unique_award_key,
    awarding_agency_code = aw_pafa.awarding_agency_code,
    awarding_agency_name = aw_pafa.awarding_agency_name,
    awarding_sub_tier_agency_n = aw_pafa.awarding_sub_tier_agency_n,
    awarding_office_code = aw_pafa.awarding_office_code,
    awarding_office_name = aw_pafa.awarding_office_name,
    funding_agency_code = aw_pafa.funding_agency_code,
    funding_agency_name = aw_pafa.funding_agency_name,
    funding_sub_tier_agency_co = aw_pafa.funding_sub_tier_agency_co,
    funding_sub_tier_agency_na = aw_pafa.funding_sub_tier_agency_na,
    funding_office_code = aw_pafa.funding_office_code,
    funding_office_name = aw_pafa.funding_office_name,
    business_types = aw_pafa.business_types_desc
FROM unlinked_subs
     JOIN aw_pafa
        ON UPPER(TRANSLATE(unlinked_subs.award_id, '-', '')) = UPPER(TRANSLATE(aw_pafa.fain, '-', ''))
WHERE subaward.id = unlinked_subs.id;<commit_msg>Applying new updates to linking broken subaward grants<commit_after>WITH unlinked_subs AS
    (
        SELECT id,
            prime_id,
            sub_id,
            award_id
        FROM subaward
        WHERE subaward.unique_award_key IS NULL
            AND subaward.subaward_type = 'sub-grant'),
aw_pafa AS
    (SELECT DISTINCT ON (
            UPPER(pafa.fain)
        )
        pafa.fain AS fain,
        pafa.uri AS uri,
        pafa.award_description AS award_description,
        pafa.record_type AS record_type,
        pafa.awarding_agency_code AS awarding_agency_code,
        pafa.awarding_agency_name AS awarding_agency_name,
        pafa.awarding_office_code AS awarding_office_code,
        pafa.awarding_office_name AS awarding_office_name,
        pafa.funding_agency_code AS funding_agency_code,
        pafa.funding_agency_name AS funding_agency_name,
        pafa.funding_office_code AS funding_office_code,
        pafa.funding_office_name AS funding_office_name,
        pafa.business_types_desc AS business_types_desc,
        pafa.awarding_sub_tier_agency_c AS awarding_sub_tier_agency_c,
        pafa.awarding_sub_tier_agency_n AS awarding_sub_tier_agency_n,
        pafa.funding_sub_tier_agency_co AS funding_sub_tier_agency_co,
        pafa.funding_sub_tier_agency_na AS funding_sub_tier_agency_na,
        pafa.unique_award_key AS unique_award_key
    FROM published_award_financial_assistance AS pafa
    WHERE is_active IS TRUE
        AND EXISTS (
            SELECT 1
            FROM unlinked_subs
            WHERE UPPER(TRANSLATE(unlinked_subs.award_id, '-', '')) = UPPER(TRANSLATE(pafa.fain, '-', ''))
                AND pafa.record_type != 1
                AND (unlinked_subs.awarding_sub_tier_agency_c IS NULL OR UPPER(unlinked_subs.awarding_sub_tier_agency_c)=UPPER(pafa.awarding_sub_tier_agency_c))
        )
        {0}
    ORDER BY UPPER(pafa.fain), pafa.action_date)
UPDATE subaward
SET
    -- File F Prime Awards
    unique_award_key = aw_pafa.unique_award_key,
    awarding_agency_code = aw_pafa.awarding_agency_code,
    awarding_agency_name = aw_pafa.awarding_agency_name,
    awarding_sub_tier_agency_n = aw_pafa.awarding_sub_tier_agency_n,
    awarding_office_code = aw_pafa.awarding_office_code,
    awarding_office_name = aw_pafa.awarding_office_name,
    funding_agency_code = aw_pafa.funding_agency_code,
    funding_agency_name = aw_pafa.funding_agency_name,
    funding_sub_tier_agency_co = aw_pafa.funding_sub_tier_agency_co,
    funding_sub_tier_agency_na = aw_pafa.funding_sub_tier_agency_na,
    funding_office_code = aw_pafa.funding_office_code,
    funding_office_name = aw_pafa.funding_office_name,
    business_types = aw_pafa.business_types_desc
FROM unlinked_subs
     JOIN aw_pafa
        ON (UPPER(TRANSLATE(unlinked_subs.award_id, '-', '')) = UPPER(TRANSLATE(aw_pafa.fain, '-', ''))
            AND (unlinked_subs.awarding_sub_tier_agency_c IS NULL OR UPPER(unlinked_subs.awarding_sub_tier_agency_c)=UPPER(pafa.awarding_sub_tier_agency_c))
        )

WHERE subaward.id = unlinked_subs.id;
{'fraction_non_alphanumeric': 0.06049017903702416, 'fraction_numerical': 0.0010429341213280027, 'mean_word_length': 3.3657056145675264, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5105220', 'n_tokens_mistral': 2288, 'n_tokens_neox': 2076, 'n_words': 308}