RISK339 : oc220_p2p_collect_decline

-- RISK339 : oc220_p2p_collect_decline
-- DROP TABLE team_kingkong.tpap_risk339_breaches; 

CREATE TABLE team_kingkong.tpap_risk339_breaches AS 
-- INSERT INTO team_kingkong.tpap_risk339_breaches
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
, json_extract_scalar(request, '$.requestPayload.txnType') as txnType
, CAST(json_extract_scalar(request, '$.requestPayload.amount') AS DOUBLE) as txn_amt 
, dl_last_updated as txn_date 
, createdon as txn_time
, 'oc220_p2p_collect_decline' as rule_name
, 'payee_type = PERSON & txnType = COLLECT' as breach_reason
FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
WHERE DATE(dl_last_updated) = DATE(CURRENT_DATE - INTERVAL '1' DAY)
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'
AND json_extract_scalar(request, '$.requestPayload.txnType') = 'COLLECT'