TPAP: RISK156
Wed Sep 17 2025 06:47:22 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi.b
-- RISK156 upi_mcc_7407_decline_intent Block if Initiation mode is 04 or 05 Payeemcc is 7407 DROP TABLE team_kingkong.tpap_risk156_breaches; -- CREATE TABLE team_kingkong.tpap_risk156_breaches AS INSERT INTO team_kingkong.tpap_risk156_breaches SELECT DISTINCT B.*, C.category , C.initiationMode as initiationMode_switch -- , D.initiationMode as initiationMode_tpaphss , IF(D.upi_subtype IS NOT NULL, D.upi_subtype, IF(C.category = 'LITE_MANDATE', 'UPI_LITE_MANDATE', '')) AS upi_subtype , 'upi_mcc_7407_decline_intent' AS rule_name , 'payee_mcc = 7407 & init mode in 04, 05' AS breach_reason FROM (SELECT txn_id, 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(CASE WHEN participant_type = 'PAYEE' THEN mcc END) AS payee_mcc, 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'2025-01-01' AND DATE'2025-01-31' AND DATE(created_on) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31' GROUP BY 1 HAVING MAX(CASE WHEN participant_type = 'PAYEE' THEN mcc END) = '7407')B inner join (select txn_id, category , json_extract_scalar(extended_info, '$.initiationMode') as initiationMode from switch.txn_info_snapshot_v3 where DATE(dl_last_updated) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31' and DATE(created_on) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31' AND json_extract_scalar(extended_info, '$.initiationMode') IN ('04', '05')) C on B.txn_id = C.txn_id INNER JOIN (SELECT txnid , regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3 WHERE DATE(dl_last_updated) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31' AND json_extract_scalar(response, '$.action_recommended') <> 'BLOCK' AND JSON_EXTRACT_SCALAR(request, '$.requestPayload.initiationMode') IN ('04', '05') AND regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') = 'UPI_TRANSACTION')D ON B.txn_id = D.txnid ;
Comments