RISK334 : oc56_uod_p2p_restrict_check

-- RISK334 : oc56_uod_p2p_restrict_check
-- DROP TABLE team_kingkong.tpap_risk334_breaches
CREATE TABLE team_kingkong.tpap_risk334_breaches AS
-- INSERT INTO team_kingkong.tpap_risk334_breaches
SELECT A.txn_id, A.scope_cust_id, A.payer_vpa, A.payee_vpa, A.txn_date, A.txn_amount, A.txn_time, A.payer_account_type, C.category, D.upi_subtype, D.payee_type,
'oc56_uod_p2p_restrict_check' as rule_name, 'payer_acc_type = UOD & payee_type = PERSON' as breach_reason
FROM 
    (SELECT txn_id, scope_cust_id, 
    MAX(CASE WHEN participant_type = 'PAYER' THEN account_type END) AS payer_account_type,
    MAX(CASE WHEN participant_type = 'PAYER' THEN vpa END) AS payer_vpa,
    MAX(CASE WHEN participant_type = 'PAYEE' THEN vpa END) AS payee_vpa,
    MAX(DATE(created_on)) as txn_date,
    MAX(amount) AS txn_amount,
    MAX(created_on) AS txn_time
    FROM switch.txn_participants_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN DATE'2026-01-01' AND DATE'2026-02-10'
    AND DATE(created_on) BETWEEN DATE'2026-01-01' AND DATE'2026-02-10'
    GROUP BY 1,2)A 
inner join
    (select txn_id, category
    from switch.txn_info_snapshot_v3
    where DATE(dl_last_updated) BETWEEN DATE'2026-01-01' AND DATE'2026-02-10'
    and DATE(created_on) BETWEEN DATE'2026-01-01' AND DATE'2026-02-10'
    and upper(status) = 'SUCCESS') C
on A.txn_id = C.txn_id
INNER JOIN
    (SELECT txnid
    , regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype
    , regexp_replace(cast(json_extract(request, '$.requestPayload.payeeType') AS varchar),'"','') AS payee_type
    FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
    WHERE DATE(dl_last_updated) BETWEEN DATE'2026-01-01' AND DATE'2026-02-10'
    AND json_extract_scalar(response, '$.action_recommended') <> 'BLOCK'
    AND regexp_replace(cast(json_extract(request, '$.requestPayload.payeeType') AS varchar),'"','') = 'PERSON'
    AND regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') = 'UPI_TRANSACTION')D 
ON A.txn_id = D.txnid
WHERE A.payer_account_type = 'UOD'