# View: orch_app_prod_royalty_accounting_account_payee_kyc_notification

**View Name:** orch_app_prod_royalty_accounting_account_payee_kyc_notification
**Table Source:** `ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNT_PAYEE_KYC_NOTIFICATION`
**File Path:** `views/orch_app_prod_royalty_accounting_account_payee_kyc_notification.view.lkml`

## Overview

- **File Size:** 10935 bytes
- **Lines of Code:** 308
- **Dimensions:** 27
- **Measures:** 1
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- sql_table_name: ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNT_PAYEE_KYC_NOTIFICATION ;;

## Dimensions

| Name | Type |
|------|------|
| `account_id` | number |
| `account_payee_id` | number |
| `sub_requirement_status_id` | number |
| `entity_reference_type_id` | number |
| `requirement_id` | string |
| `file_upload_link` | string |
| `sub_requirement_id` | string |
| `created_at` | string |
| `created_by` | string |
| `kyc_notif_last_modified` | string |
| `eligibility_status_last_modified` | string |
| `tax_eligibility_last_modified` | string |
| `last_modified_by` | string |
| `banking_details_review` | string |
| `banking_details_message` | string |
| `requirement_type_id` | string |
| `possible_sub_requirement_types` | string |
| `entity_reference_id` | string |
| `reason_for_ineligibility` | string |
| `eligibility_status` | string |
| `client_service_tier` | string |
| `payment_eligibility_message` | string |
| `payment_eligibility_status` | string |
| `tax_eligibility_status` | string |
| `tax_eligibility_status_funnel` | string |
| `tax_eligibility_message` | string |
| `contact_email` | string |

## Measures

| Name | Type |
|------|------|
| `sum_of_positive_contract_balances` | sum |

## SQL Comments

- Eligibility status
- Reason for ineligibility
- Reason for rejection from Payoneer
- Reason for rejection from Finance
- Additional columns

## Derived Table

```sql
sql:
      with vendor_contact as (
        select distinct vendor_id, contact_email, row_number() over (partition by vendor_id order by vc.id desc, vc.contact_id desc) as rn from
        ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.VEND_CONTACT vc
        left JOIN orchard_app_reporting_v2.ART_RELATIONS_PROD_ART_RELATIONS.CONTACT c on vc.contact_id= c.contact_id where master='Y'
        QUALIFY rn=1
        ),
        kyc_notif as (
        select
          *,
          row_number() over (
              partition by account_payee_id
              order by
                  account_payee_kyc_notification_id desc
          ) rn
          from
          ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNT_PAYEE_KYC_NOTIFICATION
          QUALIFY rn = 1
          )

      SELECT
          ap.account_id,
          st.display_name AS client_service_tier,
          ap.account_payee_id,
          -- Eligibility status
          CASE
              WHEN bdst.action_status IS NULL AND pest.action_status = 'init' THEN 'Not Provided Banking Details'
              WHEN bdst.action_status = 'init' AND pest.action_status = 'init' THEN 'Banking Details Under Review (KYC In Progress)'
              WHEN bdst.action_status = 'running' AND pest.action_status = 'init' THEN 'KYC Screening Detects Invalid/Incomplete Information'
              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 Approved by Finance (Payment Rejected)'
              ELSE 'Not Provided Banking Details'
          END AS eligibility_status,

      -- Reason for ineligibility
      CASE
      WHEN bdst.action_status = 'rejected' AND pest.action_status = 'init' THEN bdst.message  -- Reason for rejection from Payoneer
      WHEN bdst.action_status = 'approved' AND pest.action_status = 'rejected' THEN pest.message  -- Reason for rejection from Finance
      ELSE NULL
      END AS reason_for_ineligibility,
      CASE
      WHEN bdst.action_status IS NULL
      AND pest.action_status = 'init' THEN NULL
      WHEN bdst.action_status = 'init'
      AND pest.action_status = 'init' THEN MAX(Coalesce(pest.last_modified, bdst.last_modified))
      WHEN bdst.action_status = 'running'
      AND pest.action_status = 'init' THEN MAX(Coalesce(bdst.last_modified, pest.last_modified))
      WHEN bdst.action_status = 'approved'
      AND pest.action_status = 'running' THEN MAX(Coalesce(bdst.last_modified, pest.last_modified))
      WHEN bdst.action_status = 'rejected'
      AND pest.action_status = 'running' THEN MAX(Coalesce(bdst.last_modified, pest.last_modified))
      WHEN bdst.action_status = 'approved'
      AND pest.action_status = 'approved' THEN MAX(Coalesce(bdst.last_modified, pest.last_modified))
      WHEN bdst.action_status = 'approved'
      AND pest.action_status = 'rejected' THEN MAX(Coalesce(bdst.last_modified, pest.last_modified))
      ELSE NULL
      END AS eligibility_status_last_modified,
      CASE
        WHEN test.action_status IN ('init', 'running') or test.action_status is NULL THEN 'Client has not provided tax details yet'
        WHEN test.action_status = 'complete' THEN 'Tax details approved by Finance Team'
        WHEN test.action_status = 'rejected' THEN 'Tax details rejected  by Finance Team'
        ELSE NULL
      END AS tax_eligibility_status_funnel,

      -- Additional columns
      bdst.action_status AS banking_details_review,
      bdst.message AS banking_details_message,
      pest.action_status AS payment_eligibility_status,
      pest.message AS payment_eligibility_message,
      test.action_status AS tax_eligibility_status,
      test.message AS tax_eligibility_message,
      test.last_modified AS tax_eligibility_last_modified,
      kyc_notif.file_upload_link,
      kyc_notif.created_at,
      kyc_notif.created_by,
      kyc_notif.entity_reference_id,
      kyc_notif.entity_reference_type_id,
      kyc_notif.last_modified as kyc_notif_last_modified,
      kyc_notif.last_modified_by,
      kyc_notif.possible_sub_requirement_types,
      kyc_notif.sub_requirement_status_id,
      kyc_notif.sub_requirement_id,
      kyc_notif.requirement_id,
      kyc_notif.requirement_type_id,
      vc.contact_email,
      -- Sum of positive contract balances on the account
      SUM(COALESCE(lac.current_balance, 0)) AS sum_of_positive_contract_balances

      FROM
      ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.account_payee ap

      -- Join service tier information
      LEFT JOIN FACTS.PROD.VENDOR_IN_SERVICE_TIER_SERVICE_TIER vist
      ON ap.account_id = vist.vendor_id
      LEFT JOIN FACTS.PROD.SERVICE_TIER st
      ON st.uuid=vist.service_tier_uuid

      -- Join the payment type to check payment service
      INNER JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.reference_payment_type rpt
      ON ap.reference_payment_type_id = rpt.reference_payment_type_id

      -- Join abacus_state table for banking details review
      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'

      -- Join abacus_state table for payment eligibility status
      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'

      -- Join abacus_state table for tax eligibility status
      LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.abacus_state test
      ON test.parent_table_id = ap.account_payee_id
      AND test.parent_table_name = 'account_payee'
      AND test.action_name = 'tax_eligibility'

      -- Join to ledger account balances
      LEFT JOIN orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.ledger_account_contract_current_balance bal
      ON bal.account_id = ap.account_id

      LEFT JOIN orchard_app_reporting_v2.PROD_royalty_accounting_royalty_accounting.ledger_account_contract lac
      ON bal.ledger_account_contract_id = lac.ledger_account_contract_id
      AND lac.current_balance > 0

      LEFT JOIN kyc_notif
      ON ap.account_payee_id = kyc_notif.account_payee_id

      LEFT JOIN vendor_contact vc on vc.vendor_id=ap.account_id

      -- Filter by payment service
      WHERE rpt.payment_service in ('payoneer_whitelabel', 'paychex', 'payoneer_wire')

      -- Group by the necessary columns (explicitly list non-aggregated columns)
      GROUP BY
      ap.account_id,
      st.display_name,
      bdst.action_status,
      bdst.message,
      pest.action_status,
      pest.message,
      test.action_status,
      test.message,
      test.last_modified,
      kyc_notif.file_upload_link,
      kyc_notif.created_at,
      kyc_notif.created_by,
      kyc_notif.entity_reference_id,
      kyc_notif.entity_reference_type_id,
      kyc_notif.file_upload_link,
      kyc_notif.last_modified,
      kyc_notif.last_modified_by,
      kyc_notif.possible_sub_requirement_types,
      kyc_notif.sub_requirement_status_id,
      kyc_notif.sub_requirement_id,
      kyc_notif.requirement_id,
      ap.account_payee_id,
      kyc_notif.requirement_type_id,
      vc.contact_email;;
```