[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.
- ---------------------------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:
File Description
- Payworld
- Paste Code
- 09 Jun-2023
- 27.02 Kb
You can Share it: