-- RISK303
-- if( (txnAmount > 100000 && ((payeeType == ""ENTITY"") || (payeeMccCode == ""7407"")) && txnType == ""PAY"")){""PASS""}
-- if((in_oc_185_mcc_list == false) && (in_oc_86_mcc_list == false) && !(initiationMode == ""12"" || ((purposeCode == ""15"" && payeeMccCode !=""5969"")))){""BLOCK""}
INSERT INTO team_kingkong.tpap_risk303_breaches_new
WITH
txn_info AS (
SELECT
txn_id,
category,
"type" AS txn_type
-- json_extract_scalar(extended_info, '$.initiationMode') AS initiation_mode
FROM switch.txn_info_snapshot_v3
WHERE dl_last_updated BETWEEN DATE '2026-01-01' AND DATE '2026-01-31'
AND status = 'SUCCESS'
AND category IN ('VPA2MERCHANT', 'VPA2VPA')
AND "type" = 'PAY'
),
risk_data AS (
SELECT
txnid,
json_extract_scalar(request, '$.evaluationType') AS upi_subtype,
json_extract_scalar(request, '$.requestPayload.payeeType') AS payee_type,
json_extract_scalar(request, '$.requestPayload.purposeCode') AS purpose_code,
json_extract_scalar(request, '$.requestPayload.payeeMccCode') AS payee_mcc,
json_extract_scalar(request, '$.requestPayload.payerVpa') AS payer_vpa,
json_extract_scalar(request, '$.requestPayload.payeeVpa') AS payee_vpa,
JSON_EXTRACT_SCALAR(request, '$.requestPayload.initiationMode') AS initiation_mode,
CAST(json_extract_scalar(request, '$.requestPayload.amount') AS DOUBLE) as txn_amount,
createdon AS txn_time
FROM tpap_hss.upi_switchv2_dwh_risk_data_snapshot_v3
WHERE dl_last_updated BETWEEN DATE '2026-01-01' AND DATE '2026-01-31'
AND json_extract_scalar(request, '$.evaluationType') = 'UPI_TRANSACTION'
AND json_extract_scalar(response, '$.action_recommended') <> 'BLOCK'
AND json_extract_scalar(request, '$.requestPayload.payerType') = 'PERSON'
AND (
LOWER(json_extract_scalar(request, '$.requestPayload.payerVpa')) LIKE '%@paytm%'
OR LOWER(json_extract_scalar(request, '$.requestPayload.payerVpa')) LIKE '%@pt%'
)
AND json_extract_scalar(request, '$.requestPayload.payerVpa') IS NOT NULL
AND json_extract_scalar(request, '$.requestPayload.payerVpa') <> ''
)
SELECT
d.txnid,
d.payer_vpa,
d.payee_vpa,
d.txn_amount,
d.txn_time,
c.category,
c.txn_type,
d.initiation_mode,
d.payee_type,
IF(
d.upi_subtype IS NOT NULL,
d.upi_subtype,
IF(c.category = 'LITE_MANDATE', 'UPI_LITE_MANDATE', '')
) AS upi_subtype,
d.purpose_code,
'oc_120_p2m_p2pm_txn_limit' AS rule_name,
'NA' AS breach_reason
FROM --base_txn b
--JOIN
txn_info c
-- ON b.txn_id = c.txn_id
JOIN risk_data d
ON c.txn_id = d.txnid
WHERE
d.payer_vpa IS NOT NULL
AND d.txn_amount > 100000
AND (d.payee_type = 'ENTITY' OR d.payee_mcc = '7407')
AND d.payee_mcc NOT IN (
'8299','8042','8220','8099','742','8049','8021','8244','8211',
'9311','8031','8041','8071','8062','8249','8011','8241','8050'
)
AND d.payee_mcc NOT IN (
'6012','6529','6300','5960','6211','7322','5413'
)
AND NOT (
d.initiation_mode = '12'
OR (d.purpose_code = '15' AND d.payee_mcc <> '5969')
)
------------------------------------------------------------------------------
-- DROP TABLE team_kingkong.tpap_risk303_breaches;
-- CREATE TABLE team_kingkong.tpap_risk303_breaches AS
INSERT INTO team_kingkong.tpap_risk303_breaches
SELECT DISTINCT B.*, C.category, C.txn_type, C.initiationMode, D.payeeType
, IF(D.upi_subtype IS NOT NULL, D.upi_subtype, IF(C.category = 'LITE_MANDATE', 'UPI_LITE_MANDATE', '')) AS upi_subtype
, D.purposeCode
, 'oc_120_p2m_p2pm_txn_limit' as rule_name
, 'NA' 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 payeeMccCode,
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)B
inner join
(select DISTINCT txn_id, category, "type" AS txn_type
, 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 upper(status) = 'SUCCESS' AND category IN ('VPA2MERCHANT', 'VPA2VPA')
AND "type" = 'PAY') C
on B.txn_id = C.txn_id
INNER JOIN
(SELECT txnid
, regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') AS upi_subtype
-- , JSON_EXTRACT_SCALAR(request, '$.requestPayload.initiationMode') AS initiationMode
, regexp_replace(cast(json_extract(request, '$.requestPayload.payeeType') AS varchar),'"','') AS payeeType
, json_extract_scalar(request, '$.requestPayload.purposeCode') AS purposeCode
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 (lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '')) LIKE '%@paytm%'
or lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '')) like '%@pt%')
-- AND (lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payeeVpa') as varchar), '"', '')) LIKE '%@paytm%'
-- or lower(regexp_replace(cast(json_extract(request, '$.requestPayload.payeeVpa') as varchar), '"', '')) like '%@pt%')
-- AND regexp_replace(cast(json_extract(request, '$.requestPayload.payeeVpa') as varchar), '"', '') IS NOT NULL
AND regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '') IS NOT NULL
-- AND regexp_replace(cast(json_extract(request, '$.requestPayload.payeeVpa') as varchar), '"', '') <> ''
AND regexp_replace(cast(json_extract(request, '$.requestPayload.payerVpa') as varchar), '"', '') <> ''
AND json_extract_scalar(response, '$.action_recommended') <> 'BLOCK'
AND regexp_replace(cast(json_extract(request, '$.requestPayload.payerType') AS varchar),'"','') = 'PERSON'
AND regexp_replace(cast(json_extract(request, '$.evaluationType') as varchar), '"', '') = 'UPI_TRANSACTION')D
ON B.txn_id = D.txnid
WHERE payer_vpa IS NOT NULL
AND txn_amount > 100000
AND (payeeType = 'ENTITY' OR payeeMccCode = '7407')
AND payeeMccCode NOT IN ('8299', '8042', '8220', '8099', '742', '8049', '8021', '8244', '8211', '9311', '8031', '8041', '8071', '8062', '8249', '8011', '8241', '8050')
AND payeeMccCode NOT IN ('6012', '6529', '6300', '5960', '6211', '7322', '5413')
AND NOT (C.initiationMode = '12' OR (purposeCode = '15' AND payeeMccCode != '5969'))
;
