[mysql] Payworld

Viewer

*** This page was generated with the meta tag "noindex, nofollow". This happened because you selected this option before saving or the system detected it as spam. This means that this page will never get into the search engines and the search bot will not crawl it. There is nothing to worry about, you can still share it with anyone.

copy download embed printName: Payworld

---------------------------YBL ENGINE---------------------
MERGE INTO kosh-ai.kosh_dataset_payworldtestkrqrm.interim_payworld_ybl_engine_snapshot T
USING (
SELECT * from (
SELECT
transaction_date,
merchant,
channel,
merchant_user_id,
merchant_transaction_no,
engine_transaction_no,
aadhar_no,
customer_mobile_no,
provider,
mrp,
total_comm,
provider_comm,
rrn,
gst,
gst_value,
bank_name,
provider_rate_mode,
device_serno,
device_info,
response_code,
response_message,
client_ip,
settle_date,
status,
requery_settle_date,
row_number() over(partition by engine_transaction_no order by transaction_date desc) as rownum
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.payworld_ybl_engine_snapshot)
WHERE rownum=1
) AS S
ON T.engine_transaction_no = S.engine_transaction_no
WHEN NOT MATCHED THEN
INSERT (
transaction_date,
merchant,
channel,
merchant_user_id,
merchant_transaction_no,
engine_transaction_no,
aadhar_no,
customer_mobile_no,
provider,
mrp,
total_comm,
provider_comm,
rrn,
gst,
gst_value,
bank_name,
provider_rate_mode,
device_serno,
device_info,
response_code,
response_message,
client_ip,
settle_date,
status,
requery_settle_date
) VALUES (
transaction_date,
merchant,
channel,
merchant_user_id,
merchant_transaction_no,
engine_transaction_no,
aadhar_no,
customer_mobile_no,
provider,
mrp,
total_comm,
provider_comm,
rrn,
gst,
gst_value,
bank_name,
provider_rate_mode,
device_serno,
device_info,
response_code,
response_message,
client_ip,
settle_date,
status,
requery_settle_date
)
WHEN MATCHED THEN
UPDATE SET
T.transaction_date = S.transaction_date,
T.merchant = S.merchant,
T.channel = S.channel,
T.merchant_user_id = S.merchant_user_id,
T.merchant_transaction_no = S.merchant_transaction_no,
T.engine_transaction_no = S.engine_transaction_no,
T.aadhar_no = S.aadhar_no,
T.customer_mobile_no = S.customer_mobile_no,
T.provider = S.provider,
T.mrp = S.mrp,
T.total_comm = S.total_comm,
T.provider_comm = S.provider_comm,
T.rrn = S.rrn,
T.gst = S.gst,
T.gst_value = S.gst_value,
T.bank_name = S.bank_name,
T.provider_rate_mode = S.provider_rate_mode,
T.device_serno = S.device_serno,
T.device_info = S.device_info,
T.response_code = S.response_code,
T.response_message = S.response_message,
T.client_ip = S.client_ip,
T.settle_date = S.settle_date,
T.status = S.status,
T.requery_settle_date = S.requery_settle_date;
MERGE into kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon T
USING (
SELECT
merchant,
channel,
merchant_user_id,
merchant_transaction_no,
engine_transaction_no,
mrp,
rrn,
gst,
gst_value,
bank_name,
provider_rate_mode,
status,
requery_settle_date,
settle_date,
transaction_date,
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.interim_payworld_ybl_engine_snapshot
) AS S
ON
T.transaction_id = S.engine_transaction_no
WHEN NOT MATCHED THEN
INSERT (
transaction_date,
transaction_id,
engine_transaction_no,
mrp,
rrn,
merchant,
channel,
merchant_user_id,
merchant_transaction_no,
gst,
gst_value,
bank_name,
provider_rate_mode,
settle_date,
requery_settle_date,
status,
source,
created_at
) VALUES (
transaction_date,
engine_transaction_no,
engine_transaction_no,
mrp,
rrn,
merchant,
channel,
merchant_user_id,
merchant_transaction_no,
gst,
gst_value,
bank_name,
provider_rate_mode,
settle_date,
requery_settle_date,
status,
'ybl',
transaction_date
)
WHEN MATCHED THEN
UPDATE SET
T.transaction_date = S.transaction_date,
T.transaction_id = S.engine_transaction_no,
T.engine_transaction_no = S.engine_transaction_no,
T.mrp = S.mrp,
T.rrn = S.rrn,
T.merchant = S.merchant,
T.channel = S.channel,
T.merchant_user_id = S.merchant_user_id,
T.merchant_transaction_no = S.merchant_transaction_no,
T.gst = S.gst,
T.gst_value = S.gst_value,
T.bank_name = S.bank_name,
T.provider_rate_mode = S.provider_rate_mode,
T.settle_date = S.settle_date,
T.requery_settle_date = S.requery_settle_date,
T.status = S.status,
T.created_at = S.transaction_date;
----------------------------------YBL HO-----------------------------------
MERGE into kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon T
USING (
SELECT
transaction_date,
engine_transaction_number,
sale_amount,
provider_status
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.payworld_ybl_ho_snapshot
) AS S
ON
T.transaction_id = S.engine_transaction_number
WHEN NOT MATCHED THEN
INSERT (
transaction_date,
transaction_id,
ho_transaction_no,
ho_amt,
ho_provider_status,
source,
created_at)
VALUES (
transaction_date,
engine_transaction_number,
engine_transaction_number,
sale_amount,
provider_status,
'ybl',
transaction_date
)
WHEN MATCHED THEN
UPDATE SET
T.transaction_date = S.transaction_date,
T.transaction_id = S.engine_transaction_number,
T.ho_transaction_no = S.engine_transaction_number,
T.ho_provider_status = S.provider_status,
T.ho_amt = S.sale_amount;
------------------------------------YBL PROVIDER--------------------------------
MERGE into kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon T
USING (
SELECT
transdate,
retailertxnid,
transamount
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.payworld_ybl_provider_snapshot) AS S
ON
T.transaction_id = S.retailertxnid
WHEN NOT MATCHED THEN
INSERT
(
transaction_date,
transaction_id,
provider_transaction_no,
provider_amt,
source,
created_at
) VALUES (
transdate,
retailertxnid,
retailertxnid,
transamount,
'ybl'
,transdate
)
WHEN MATCHED THEN
UPDATE SET
T.transaction_date = S.transdate,
T.transaction_id = S.retailertxnid,
T.provider_transaction_no = S.retailertxnid,
T.provider_amt = S.transamount;
----------------------------------------YBL BANK--------------------------------------
MERGE INTO kosh-ai.kosh_dataset_payworldtestkrqrm.interim_payworld_ybl_bank_statement_snapshot T
USING (
SELECT * from (
SELECT
txn_date
,cod_acct_no
,narration
,value_date
,cheque_no
,dr_cr
,amount
,dat_post
,running_balance
,urn
,bank_reference_no
,tenant
,sourceId
,concat(running_balance,'-',bank_reference_no) as bank_key
,row_number() over(partition by concat(running_balance,'-',bank_reference_no) order by txn_date desc) as rownum
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.payworld_ybl_bank_statement_snapshot)
WHERE rownum=1
) AS S
ON T.bank_key = S.bank_key
WHEN NOT MATCHED THEN
INSERT (
txn_date
,cod_acct_no
,narration
,value_date
,cheque_no
,dr_cr
,amount
,dat_post
,running_balance
,urn
,bank_reference_no
,tenant
,sourceId
,bank_key
) VALUES (
txn_date
,cod_acct_no
,narration
,value_date
,cheque_no
,dr_cr
,amount
,dat_post
,running_balance
,urn
,bank_reference_no
,tenant
,sourceId
,bank_key
)
WHEN MATCHED THEN
UPDATE SET
T.txn_date = S.txn_date
,T.cod_acct_no = S.cod_acct_no
,T.narration = S.narration
,T.value_date = S.value_date
,T.cheque_no = S.cheque_no
,T.dr_cr = S.dr_cr
,T.amount = S.amount
,T.dat_post = S.dat_post
,T.running_balance = S.running_balance
,T.urn = S.urn
,T.bank_reference_no = S.bank_reference_no
,T.tenant = S.tenant
,T.sourceId = S.sourceId
,T.bank_key = S.bank_key;
MERGE into kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon T
USING (
SELECT
max(txn_date) as txn_date,
bank_reference_no,
SUM(amount) AS amt,
abs(SUM(CASE WHEN amt_sign = 'positive' THEN amount ELSE NULL END)) AS bank_refund_amt,
abs(SUM(CASE WHEN amt_sign = 'negative' THEN amount ELSE NULL END)) AS bank_charge_amt,
COUNT(CASE WHEN amt_sign = 'positive' THEN amount ELSE NULL END) AS bank_refund_cnt,
COUNT(CASE WHEN amt_sign = 'negative' THEN amount ELSE NULL END) AS bank_charge_cnt,
MAX(CASE WHEN amt_sign = 'positive' THEN txn_date ELSE NULL END) AS bank_refund_date,
MAX(CASE WHEN amt_sign = 'negative' THEN txn_date ELSE NULL END) AS bank_charge_date
FROM (
SELECT
txn_date,
amount,
bank_reference_no,
CASE WHEN SIGN(amount) = -1 THEN 'negative' ELSE 'positive' END AS amt_sign
FROM `kosh-ai.kosh_dataset_payworldtestkrqrm.interim_payworld_ybl_bank_statement_snapshot`
) AS subquery
GROUP BY bank_reference_no) AS S
ON
T.transaction_id = S.bank_reference_no
WHEN NOT MATCHED THEN
INSERT (
transaction_date,
transaction_id,
bank_transaction_no,
bank_amt,
source,
created_at,
bank_refund_amt,
bank_charge_amt,
bank_refund_cnt,
bank_charge_cnt,
bank_refund_date,
bank_charge_date)
VALUES (
txn_date,
bank_reference_no,
bank_reference_no,
amt,
'ybl',
txn_date,
bank_refund_amt,
bank_charge_amt,
bank_refund_cnt,
bank_charge_cnt,
bank_refund_date,
bank_charge_date
)
WHEN MATCHED THEN
UPDATE SET
T.transaction_date = S.txn_date,
T.bank_transaction_no = S.bank_reference_no,
T.bank_amt = S.amt,
T.created_at = S.txn_date,
T.bank_refund_amt = S.bank_refund_amt,
T.bank_charge_amt = S.bank_charge_amt,
T.bank_refund_cnt = S.bank_refund_cnt,
T.bank_charge_cnt = S.bank_charge_cnt,
T.bank_refund_date = S.bank_refund_date,
T.bank_charge_date = s.bank_charge_date;
-------------------------------UPDATE-------------------------------------
UPDATE kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon master_eng
SET status_recon_flag = REPLACE(LTRIM(RTRIM(subquery2.recon_flag, '_'), '_'), '__', '_'),
ho_amt_flag = subquery2.ho_amt_flag,
provider_amt_flag = subquery2.provider_amt_flag,
bank_amt_flag = subquery2.bank_amt_flag
FROM (
WITH eng AS (
SELECT engine_transaction_no AS eng_id, rrn, mrp, status, status_recon_flag
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon where source = 'ybl'
),
ho AS (
SELECT ho_transaction_no AS ho_id, ho_amt as sale_amount
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon where source = 'ybl'
),
pro AS (
SELECT provider_transaction_no AS pro_id, provider_amt as transamount
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon where source = 'ybl'
),
bank AS (
SELECT bank_transaction_no AS bank_id, bank_amt as amount
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon where source = 'ybl'
)
SELECT eng_id,
CASE
WHEN upper(status) = "SUCCESS" or upper(status) = "FAILED"
THEN 'Present_in_' || engine_status || '_' || ho_status || '_' || provider_status || '_' || bank_status
END AS recon_flag ,
ho_amt_flag,
provider_amt_flag,
bank_amt_flag
FROM (
SELECT eng_id, e.status,
CASE WHEN ho_id IS NOT NULL THEN 'HO' ELSE '' END AS ho_status,
CASE WHEN pro_id IS NOT NULL THEN 'Provider' ELSE '' END AS provider_status,
CASE WHEN bank_id IS NOT NULL THEN 'Bank' ELSE '' END AS bank_status,
CASE WHEN eng_id IS NOT NULL THEN 'Engine' ELSE '' END AS engine_status,
CASE WHEN e.mrp = h.sale_amount THEN 'Amount_Matching' ELSE 'Amount_Not_matching' END AS ho_amt_flag,
CASE WHEN e.mrp = cast(p.transamount as Float64) THEN 'Amount_Matching' ELSE 'Amount_Not_matching' END AS provider_amt_flag,
CASE WHEN e.mrp = b.amount THEN 'Amount_Matching' ELSE 'Amount_Not_matching' END AS bank_amt_flag,
FROM eng e
LEFT JOIN ho h ON e.eng_id = h.ho_id
LEFT JOIN pro p ON e.eng_id = p.pro_id
LEFT JOIN bank b ON e.rrn = b.bank_id
)
) subquery2
WHERE master_eng.engine_transaction_no = subquery2.eng_id;
-----------------------------------FINGPAY ENGINE----------------------------------
MERGE INTO kosh-ai.kosh_dataset_payworldtestkrqrm.interim_payworld_fingpay_engine_snapshot T
USING (
SELECT * FROM (
SELECT
transaction_date,
merchant,
channel,
merchant_user_id,
merchant_trans_no,
engine_trans_no,
aadhar_no,
customer_mobile_no,
provider,
mrp,
total_comm,
provider_comm,
rrn,
gst,
gst_value,
bank_name,
provider_rate_mode,
device_serno,
device_info,
response_code,
response_message,
client_ip,
settle_date,
status,
requery_settle,
row_number() over(partition by engine_trans_no order by transaction_date desc) as rownum
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.payworld_fingpay_engine_snapshot)
WHERE rownum=1
) AS S
ON T.engine_trans_no = S.engine_trans_no
WHEN NOT MATCHED THEN
INSERT (
transaction_date,
merchant,
channel,
merchant_user_id,
merchant_trans_no,
engine_trans_no,
aadhar_no,
customer_mobile_no,
provider,
mrp,
total_comm,
provider_comm,
rrn,
gst,
gst_value,
bank_name,
provider_rate_mode,
device_serno,
device_info,
response_code,
response_message,
client_ip,
settle_date,
status,
requery_settle
) VALUES (
transaction_date,
merchant,
channel,
merchant_user_id,
merchant_trans_no,
engine_trans_no,
aadhar_no,
customer_mobile_no,
provider,
mrp,
total_comm,
provider_comm,
rrn,
gst,
gst_value,
bank_name,
provider_rate_mode,
device_serno,
device_info,
response_code,
response_message,
client_ip,
settle_date,
status,
requery_settle
)
WHEN MATCHED THEN
UPDATE SET
T.transaction_date = S.transaction_date,
T.merchant = S.merchant,
T.channel = S.channel,
T.merchant_user_id = S.merchant_user_id,
T.merchant_trans_no = S.merchant_trans_no,
T.engine_trans_no = S.engine_trans_no,
T.aadhar_no = S.aadhar_no,
T.customer_mobile_no = S.customer_mobile_no,
T.provider = S.provider,
T.mrp = S.mrp,
T.total_comm = S.total_comm,
T.provider_comm = S.provider_comm,
T.rrn = S.rrn,
T.gst = S.gst,
T.gst_value = S.gst_value,
T.bank_name = S.bank_name,
T.provider_rate_mode = S.provider_rate_mode,
T.device_serno = S.device_serno,
T.device_info = S.device_info,
T.response_code = S.response_code,
T.response_message = S.response_message,
T.client_ip = S.client_ip,
T.settle_date = S.settle_date,
T.status = S.status,
T.requery_settle = S.requery_settle;
-----------------------------------FINGPAY API ENGINE----------------------------------
MERGE INTO kosh-ai.kosh_dataset_payworldtestkrqrm.interim_payworld_fingpay_engine_snapshot T
USING (
SELECT * FROM (
SELECT
transaction_date,
merchant,
channel,
merchant_user_id,
merchant_trans_no,
engine_trans_no,
aadhar_no,
customer_mobile_no,
provider,
mrp,
total_comm,
provider_comm,
rrn,
gst,
gst_value,
bank_name,
provider_rate_mode,
device_serno,
device_info,
response_code,
response_message,
client_ip,
settle_date,
status,
requery_settle,
row_number() over(partition by engine_trans_no order by transaction_date desc) as rownum
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.payworld_fingpay_api_engine_snapshot)
WHERE rownum=1) AS S
ON T.engine_trans_no = S.engine_trans_no
WHEN NOT MATCHED THEN
INSERT (
transaction_date,
merchant,
channel,
merchant_user_id,
merchant_trans_no,
engine_trans_no,
aadhar_no,
customer_mobile_no,
provider,
mrp,
total_comm,
provider_comm,
rrn,
gst,
gst_value,
bank_name,
provider_rate_mode,
device_serno,
device_info,
response_code,
response_message,
client_ip,
settle_date,
status,
requery_settle
) VALUES (
transaction_date,
merchant,
channel,
merchant_user_id,
merchant_trans_no,
engine_trans_no,
aadhar_no,
customer_mobile_no,
provider,
mrp,
total_comm,
provider_comm,
rrn,
gst,
gst_value,
bank_name,
provider_rate_mode,
device_serno,
device_info,
response_code,
response_message,
client_ip,
settle_date,
status,
requery_settle
)
WHEN MATCHED THEN
UPDATE SET
T.transaction_date = S.transaction_date,
T.merchant = S.merchant,
T.channel = S.channel,
T.merchant_user_id = S.merchant_user_id,
T.merchant_trans_no = S.merchant_trans_no,
T.engine_trans_no = S.engine_trans_no,
T.aadhar_no = S.aadhar_no,
T.customer_mobile_no = S.customer_mobile_no,
T.provider = S.provider,
T.mrp = S.mrp,
T.total_comm = S.total_comm,
T.provider_comm = S.provider_comm,
T.rrn = S.rrn,
T.gst = S.gst,
T.gst_value = S.gst_value,
T.bank_name = S.bank_name,
T.provider_rate_mode = S.provider_rate_mode,
T.device_serno = S.device_serno,
T.device_info = S.device_info,
T.response_code = S.response_code,
T.response_message = S.response_message,
T.client_ip = S.client_ip,
T.settle_date = S.settle_date,
T.status = S.status,
T.requery_settle = S.requery_settle;
MERGE into kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon T
USING (
SELECT
merchant,
channel,
merchant_user_id,
merchant_trans_no,
engine_trans_no,
mrp,
rrn,
gst,
gst_value,
bank_name,
provider_rate_mode,
status,
requery_settle,
settle_date,
transaction_date
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.interim_payworld_fingpay_engine_snapshot) AS S
ON T.transaction_id = S.engine_trans_no
WHEN NOT MATCHED THEN
INSERT (
transaction_date,
transaction_id,
engine_transaction_no,
mrp,
rrn,
merchant,
channel,
merchant_user_id,
merchant_transaction_no,
gst,
gst_value,
bank_name,
provider_rate_mode,
settle_date,
requery_settle_date,
status,
source,
created_at
) VALUES (
transaction_date,
engine_trans_no,
engine_trans_no,
mrp,
rrn,
merchant,
channel,
merchant_user_id,
merchant_trans_no,
gst,
gst_value,
bank_name,
provider_rate_mode,
settle_date,
requery_settle,
status,
'fingpay',
transaction_date
)
WHEN MATCHED THEN
UPDATE SET
T.transaction_date = S.transaction_date,
T.transaction_id = S.engine_trans_no,
T.engine_transaction_no = S.engine_trans_no,
T.mrp = S.mrp,
T.rrn = S.rrn,
T.merchant = S.merchant,
T.channel = S.channel,
T.merchant_user_id = S.merchant_user_id,
T.merchant_transaction_no = S.merchant_trans_no,
T.gst = S.gst,
T.gst_value = S.gst_value,
T.bank_name = S.bank_name,
T.provider_rate_mode = S.provider_rate_mode,
T.settle_date = S.settle_date,
T.requery_settle_date = S.requery_settle,
T.status = S.status,
T.created_at = S.transaction_date;
------------------------------FINGPAY HO--------------------------------------
MERGE into kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon T
USING (
SELECT
transaction_date,
engine_transaction_number,
sale_amount,
provider_status
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.payworld_fingpay_api_ho_snapshot) AS S
ON T.transaction_id = S.engine_transaction_number
WHEN NOT MATCHED THEN
INSERT (
transaction_date,
transaction_id,
ho_transaction_no,
ho_amt,
ho_provider_status,
source,
created_at
) VALUES (
transaction_date,
engine_transaction_number,
engine_transaction_number,
sale_amount,
provider_status,
'fingpay',
transaction_date
)
WHEN MATCHED THEN
UPDATE SET
T.transaction_date = S.transaction_date,
T.transaction_id = S.engine_transaction_number,
T.ho_transaction_no = S.engine_transaction_number,
T.ho_amt = S.sale_amount,
T.ho_provider_status = S.provider_status,
T.created_at = S.transaction_date;
MERGE into kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon T
USING (
SELECT
transaction_date,
engine_transaction_number,
sale_amount,
provider_status
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.payworld_fingpay_ho_snapshot) AS S
ON T.transaction_id = S.engine_transaction_number
WHEN NOT MATCHED THEN
INSERT (
transaction_date,
transaction_id,
ho_transaction_no,
ho_amt,
ho_provider_status,
source,
created_at
) VALUES (
transaction_date,
engine_transaction_number,
engine_transaction_number,
sale_amount,
provider_status,
'fingpay',
transaction_date
)
WHEN MATCHED THEN
UPDATE SET
T.transaction_date = S.transaction_date,
T.transaction_id = S.engine_transaction_number,
T.ho_transaction_no = S.engine_transaction_number,
T.ho_amt = S.sale_amount,
T.ho_provider_status = S.provider_status,
T.created_at = S.transaction_date;
--------------------------------------FINGPAY PROVIDER----------------------------------
MERGE into kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon T
USING (
SELECT
transaction_amount,
merchant_transaction_id,
requested_timestamp
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.payworld_fingpay_provider_snapshot) AS S
ON
T.transaction_id = S.merchant_transaction_id
WHEN NOT MATCHED THEN
INSERT
(
transaction_id,
provider_transaction_no,
provider_amt,
source,
created_at
) VALUES (
merchant_transaction_id,
merchant_transaction_id,
transaction_amount,
'fingpay',
requested_timestamp
)
WHEN MATCHED THEN
UPDATE SET
T.transaction_id = S.merchant_transaction_id,
T.provider_transaction_no = S.merchant_transaction_id,
T.provider_amt = S.transaction_amount,
T.created_at = S.requested_timestamp;
--------------------------------FINGPAY BANK--------------------------------
MERGE into kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon T
USING (
SELECT
transaction_date,
transaction_particulars,
amount
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.payworld_fingpay_axis_bank_statement_snapshot) AS S
ON
T.transaction_id = S.transaction_particulars
WHEN NOT MATCHED THEN
INSERT (
transaction_date,
transaction_id,
bank_transaction_no,
bank_amt,
source,
created_at
) VALUES (
transaction_date,
transaction_particulars,
transaction_particulars,
amount,
'fingpay',
transaction_date
)
WHEN MATCHED THEN
UPDATE SET
T.transaction_date = S.transaction_date,
T.transaction_id = S.transaction_particulars,
T.bank_transaction_no = S.transaction_particulars,
T.bank_amt = S.amount,
T.created_at = S.transaction_date;
------------------------------------UPDATE--------------------------------------------
UPDATE kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon master_eng
SET status_recon_flag = REPLACE(LTRIM(RTRIM(subquery2.recon_flag, '_'), '_'), '__', '_'),
ho_amt_flag = subquery2.ho_amt_flag,
provider_amt_flag = subquery2.provider_amt_flag,
bank_amt_flag = subquery2.bank_amt_flag
FROM (
WITH eng AS (
SELECT engine_transaction_no AS eng_id, rrn, mrp, status, status_recon_flag
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon where source = 'fingpay'
),
ho AS (
SELECT ho_transaction_no AS ho_id, ho_amt as sale_amount
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon where source = 'fingpay'
),
pro AS (
SELECT provider_transaction_no AS pro_id, provider_amt as transaction_amount
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon where source = 'fingpay'
),
bank AS (
SELECT bank_transaction_no AS bank_id, bank_amt as amount
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.master_recon where source = 'fingpay'
)
SELECT eng_id,
CASE
WHEN upper(status) in ("SUCCESS", "FAILED", "INITIATED")
THEN 'Present_in_' || engine_status || '_' || ho_status || '_' || provider_status || '_' || bank_status
END AS recon_flag ,
ho_amt_flag,
provider_amt_flag,
bank_amt_flag
FROM (
SELECT eng_id, e.status,
CASE WHEN ho_id IS NOT NULL THEN 'HO' ELSE '' END AS ho_status,
CASE WHEN pro_id IS NOT NULL THEN 'Provider' ELSE '' END AS provider_status,
CASE WHEN bank_id IS NOT NULL THEN 'Bank' ELSE '' END AS bank_status,
CASE WHEN eng_id IS NOT NULL THEN 'Engine' ELSE '' END AS engine_status,
CASE WHEN e.mrp = h.sale_amount THEN 'Amount_Matching' ELSE 'Amount_Not_matching' END AS ho_amt_flag,
CASE WHEN e.mrp = p.transaction_amount THEN 'Amount_Matching' ELSE 'Amount_Not_matching' END AS provider_amt_flag,
CASE WHEN e.mrp = b.amount THEN 'Amount_Matching' ELSE 'Amount_Not_matching' END AS bank_amt_flag,
FROM eng e
LEFT JOIN ho h ON e.eng_id = h.ho_id
LEFT JOIN pro p ON e.eng_id = p.pro_id
LEFT JOIN bank b ON e.rrn = b.bank_id
)
) subquery2
WHERE master_eng.engine_transaction_no = subquery2.eng_id;
------------------------------------------------------------------------------------------------------------------------------------------------------
MERGE into `kosh_dataset_payworldtestkrqrm.settlement_recon` T
USING (
SELECT
reference_number as transaction_id,
created_timestamp as transaction_date,
settlement_amount as settlement_amount,
transaction_amount as transaction_amount,
service_type as service_type,
created_timestamp as created_at
FROM kosh-ai.kosh_dataset_payworldtestkrqrm.payworld_fingpay_provider_settlement_snapshot where service_type='cash withdrawal') AS S
ON
T.transaction_id = S.transaction_id
WHEN NOT MATCHED THEN
INSERT (
transaction_id,
settlement_amount,
transaction_amount,
service_type,
source,
created_at
) VALUES (
transaction_id,
settlement_amount,
transaction_amount,
service_type,
'fingpay',
transaction_date
)
WHEN MATCHED THEN
UPDATE SET
T.settlement_amount = S.settlement_amount,
T.transaction_amount = S.transaction_amount,
T.service_type = S.service_type,
T.source = 'fingpay',
T.created_at = S.transaction_date;

Editor

You can edit this paste and save as new:

fullscreen copy clear

Syntax Highlighting

Title / Paste Name

Meta robots tag

reCaptcha

File Description

Payworld
Paste Code
09 Jun-2023
27.02 Kb

You can Share it:

Latest Code Pastes

Full list

Tools

[mysql] Payworld

Viewer

Editor