# View: dt_oa_banking_migration

**View Name:** dt_oa_banking_migration
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Orchadmin_Users, Art_Relations_Prod_Art_Relations.Owner, Art_Relations_Prod_Art_Relations.Product_Manager_Mapping_Vendor, Dbt_Prod.Active_Vendor_Contracts, Dbt_Prod.Awal_Tier_Groups + 3 more`
**File Path:** `dt_oa_banking_migration.view.lkml`

## Overview

- **File Size:** 5337 bytes
- **Lines of Code:** 136
- **Dimensions:** 13
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `label_id` | number |
| `label_name` | string |
| `status` | string |
| `owner` | string |
| `relationship_manager` | string |
| `solutions` | string |
| `tier` | string |
| `closer` | string |
| `contact` | string |
| `contact_email` | string |
| `oa_hold_status` | string |
| `oa_hold_description` | string |
| `eligibility_status` | string |

## SQL Comments

- royalty_accounting.prod.vw_eligible_legacy_contract_migration lcm ***Update table name 3 July 2025
- and case when v.status = 'deletion' then 1 when iff(ac.vendor_contract_id is NULL, FALSE, TRUE) then 1 else 0 end = 1

## Derived Table

```sql
sql:
      select
    lcm.vendor_id as label_id,
    IFF(v.company is NULL or v.company  = '', v.name, v.company) as label_name,
    v.status as status,
    v.owner as owner,
    lm.f_name||' '||lm.l_name as relationship_manager,
    pm.f_name||' '||pm.l_name as product_manager,
    atg.service_tier_name as tier,
    c.f_name||' '||c.l_name as closer,
    contact.contact_first_name||' '||contact.contact_last_name as contact,
    contact.contact_email,
        CASE
        WHEN vph.status = 'active' then 'On Hold'
        ELSE NULL
    END AS oa_hold_status,
    vph.description as oa_hold_description,
        CASE
        WHEN bdst.action_status IS NULL AND pest.action_status = 'init' THEN 'No Banking Details'
        WHEN bdst.action_status = 'init' AND pest.action_status = 'init' THEN 'Banking Details Under Review (KYC)'
        WHEN bdst.action_status = 'running' AND pest.action_status = 'init' THEN 'KYC Follow-up Required'
        WHEN bdst.action_status = 'approved' AND pest.action_status = 'running' THEN 'Banking Details Approved by Payoneer (Waiting Finance Review)'
        WHEN bdst.action_status = 'rejected' AND pest.action_status = 'init' THEN 'Banking Details Rejected by Payoneer'
        WHEN bdst.action_status = 'approved' AND pest.action_status = 'approved' THEN 'Banking Details Approved by Finance (Payment Eligible)'
        WHEN bdst.action_status = 'approved' AND pest.action_status = 'rejected' THEN 'Banking Details Rejected by Finance'
        ELSE 'No Banking Details'
    END AS eligibility_status
FROM ROYALTY_ACCOUNTING_REPORTING.prod.vw_oa_migrated_accounts_contracts_report lcm
--royalty_accounting.prod.vw_eligible_legacy_contract_migration lcm ***Update table name 3 July 2025
LEFT JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v on lcm.vendor_id = v.vendor_id
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.owner o on lower(o.owner_abbrivation) = lower(v.owner)
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users lm on v.assigned_to = lm.id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.product_manager_mapping_vendor pmmp on v.vendor_id = pmmp.vendor_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users pm on pmmp.product_manager_id = pm.id
LEFT JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor_contract vc on v.vendor_id = vc.vendor_id
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users c on (TRY_TO_NUMERIC(vc.orchrep_name)) = c.id
LEFT JOIN intelligence.dbt_prod.active_vendor_contracts ac ON vc.id = ac.vendor_contract_id
LEFT JOIN intelligence.dbt_prod.awal_tier_groups atg on v.vendor_id = atg.labelid
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vend_contact vcontact ON vcontact.vendor_id = v.vendor_id
LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.account_payee ap on ap.account_id = lcm.vendor_id
LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.abacus_state bdst on bdst.parent_table_id = ap.account_payee_id
    and bdst.parent_table_name = 'account_payee' and bdst.action_name = 'banking_details_review'
LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.abacus_state pest on pest.parent_table_id = ap.account_payee_id
    and pest.parent_table_name = 'account_payee' and pest.action_name = 'payment_eligibility'
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.contact contact on vcontact.contact_id = contact.contact_id
LEFT JOIN (select status, description, vendor_id, rank() over(partition by vendor_id order by vendor_id, id desc) rnk from orchard_app_reporting_v2.prod_ows_accounting_payment_holds.vendor_payment_hold) vph on lcm.vendor_id = vph.vendor_id
where (vcontact.master = 'Y' or vcontact.master is NULL)
    and (rnk is NULL or rnk = 1)
    and v.migrated_to_abacus
    --and case when v.status = 'deletion' then 1 when iff(ac.vendor_contract_id is NULL, FALSE, TRUE) then 1 else 0 end = 1
GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13
ORDER BY
    5,7,2 desc
    ;;
```