merge_queries.py

queries_merged_01 = {
    # Query 1
    '''
    merge
    `fynd-db.finance_recon_tool_asia.09_seller_net_collection_daily` as A
    using
    `fynd-db.CN_Working.settlement_id_update` as B
    on
    A.bag_id = B.bag_id
    and A.settlement_type = B.settlement_type
    and A.sett_id = B.sett_id
    when matched then 
    update set 
    A.sett_id = B.New_sett_id
    '''
}

queries_merged_02 = {
    # Query 1
    '''
    merge
    `fynd-db.finance_recon_tool_asia.11_seller_fees_daily` as A
    using
    `fynd-db.CN_Working.settlement_id_update` as B
    on
    A.bag_id = B.bag_id
    and A.transaction_type = B.transaction_type
    and A.sett_id = B.sett_id
    when matched then 
    update set 
    A.sett_id = B.New_sett_id
    '''
}


queries_merged_03 = {
    # Query 1
    '''
    merge
    `fynd-db.finance_recon_tool_asia.12_seller_claims_daily` as A
    using
    `fynd-db.CN_Working.settlement_id_update` as B
    on
    concat(A.current_shipment_id,A.recon_status) = B.merged
    --and A.recon_status = B.recon_status
    and A.sett_id = B.sett_id
    when matched then 
    update set 
    A.sett_id = B.New_sett_id
    '''
}

conso_query = '''
    SELECT
    sett_id,
    SUM(seller_net_collection),
    "NC" AS Type
    FROM
    `fynd-db.Outstanding.09_Payable_File_table`
    WHERE
    expected_payout_date <= @selected_date
    GROUP BY 1

    UNION ALL
    SELECT
    sett_id,
    SUM(total_charges),
    "FS" AS Type
    FROM
    `fynd-db.Outstanding.11_fees_payable`
    WHERE
    expected_payout_date <= @selected_date
    GROUP BY 1

    UNION ALL
    SELECT
    sett_id,
    SUM(claimable_amt),
    "CS" AS Type
    FROM
    `fynd-db.Outstanding.12_claim_payable`
    WHERE
    claim_settle_date <= @selected_date  
    GROUP BY 1

    UNION ALL
    SELECT
    sett_id,
    SUM(dispute_amount),
    "MD" AS Type
    FROM
    `fynd-db.Outstanding.Manual_Dispute`
    GROUP BY 1
'''

disburse_query = '''
    select 
    *,
    CASE
    WHEN company_Id in (269,
    334,
    43,
    1103,
    1115,
    2704,
    7151,
    1202,
    4204,
    5275,
    1951,
    644,
    46,
    1708,
    442,
    5842,
    480,
    2250,
    5234,
    2196,
    4770) THEN 'RBL' ELSE 'Non RBL'
    END as company_type,

    from
    `finance_recon_tool_asia.disbursement_summary`
    where sett_id in (
    SELECT
        sett_id
        FROM
        `fynd-db.Outstanding.09_Payable_File_table`
        WHERE
        expected_payout_date <= @selected_date
        GROUP BY 1

        UNION ALL
        SELECT
        sett_id
        FROM
        `fynd-db.Outstanding.11_fees_payable`
        WHERE
        expected_payout_date <= @selected_date
        GROUP BY 1

        UNION ALL
        SELECT
        sett_id,
        FROM
        `fynd-db.Outstanding.12_claim_payable`
        WHERE
        claim_settle_date <= @selected_date
        GROUP BY 1

        UNION ALL
        SELECT
        sett_id,
        FROM
        `fynd-db.Outstanding.Manual_Dispute`
        GROUP BY 1
    )
'''