Paste this query in console, replace the contents of the routine (after the “FROM C” and before the “) src”
-- set "Schema" in two places below
-- set name of report by replacing getreport() with newreportname()
create function marksnelson.getpaymentsurcharges() returns json
SET search_path = "marksnelson","$user", public
language sql
as
$$
SELECT JSON_AGG(src) AS returnvalue
FROM (
-- Cash, Objects
SELECT
cbase.key as "ClientID",
concat('AIW-', a.id) as "Check No.",
arl.alloc_amount as "Payment Amount",
abase.id as "Detail/Invoice Number",
p.posted_to_bank_dt as "Date",
case when p.payment_method in ('UNKNOWN', 'PREPAID', 'DEBIT') then 'DEBIT'
else p.payment_method end as "Payment Method",
abase.type as "Type"
FROM payment p
inner join ar_tran a on a.id = p.ar_tran_id
inner join client c on c.id = a.client_id
inner join ar_tran_link arl on arl.ar_tran_mod_id = p.ar_tran_id and arl.active = true
inner join ar_tran abase on abase.id = arl.ar_tran_base_id
left join business_entity be on be.id = a.business_entity_id
left join client cbase on cbase.id = abase.client_id
left join invoice i on i.ar_tran_id = abase.id
WHERE a.is_active
AND a.is_created_by_aiwyn
AND abase.type not in ('Refund','Dispute','LateFee','Invoice')
AND p.posted_to_bank_dt BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()
-- Cash, Refund Objects
UNION
SELECT
cbase.key as "ClientID",
concat('AIW-', a.id) as "Check No.",
arl2.alloc_amount * -1 as "Payment Amount",
abase.id as "Detail/Invoice Number",
r.funds_withdrawn_at as "Date",
case when p.payment_method in ('UNKNOWN', 'PREPAID', 'DEBIT') then 'DEBIT'
else p.payment_method end as "Payment Method",
abase.type as "Type"
from refund r
inner join ar_tran a on a.id = r.ar_tran_id
inner join client c on c.id = a.client_id
inner join ar_tran_link arl on arl.ar_tran_base_id = r.ar_tran_id
inner join ar_tran apayment on apayment.id = arl.ar_tran_mod_id
inner join payment p on apayment.id = p.ar_tran_id
inner join ar_tran_link arl2 on arl2.ar_tran_mod_id = apayment.id and arl2.active is false
inner join ar_tran abase on abase.id = arl2.ar_tran_base_id
inner join client cbase on cbase.id = abase.client_id
left join business_entity be on be.id = apayment.business_entity_id
left join invoice i on i.ar_tran_id = abase.id
where apayment.is_active
AND apayment.is_created_by_aiwyn
AND abase.type not in ('LateFee','Invoice')
AND r.funds_withdrawn_at BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()
-- Cash, Dispute Objects
UNION
SELECT
cbase.key as "ClientID",
concat('AIW-', a.id) as "Check No.",
arl2.alloc_amount * -1 as "Payment Amount",
abase.id as "Detail/Invoice Number",
p.failed_funds_withdrawn_at as "Date",
case when p.payment_method in ('UNKNOWN', 'PREPAID', 'DEBIT') then 'DEBIT'
else p.payment_method end as "Payment Method",
abase.type as "Type"
from dispute d
inner join ar_tran a on a.id = d.ar_tran_id
inner join client c on c.id = a.client_id
inner join ar_tran_link arl on arl.ar_tran_base_id = d.ar_tran_id
inner join ar_tran apayment on apayment.id = arl.ar_tran_mod_id
inner join payment p on apayment.id = p.ar_tran_id
inner join ar_tran_link arl2 on arl2.ar_tran_mod_id = apayment.id and arl2.active is false
inner join ar_tran abase on abase.id = arl2.ar_tran_base_id
inner join client cbase on cbase.id = abase.client_id
left join business_entity be on be.id = a.business_entity_id
left join invoice i on i.ar_tran_id = abase.id
where apayment.is_active
AND apayment.is_created_by_aiwyn
AND abase.type not in ('LateFee','Invoice')
AND p.failed_funds_withdrawn_at BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()
----------------------------------------
---- The following queries outline Reversed payments that are hitting the bank despite having already failed and are scheduled for withdraw.
----------------------------------------
UNION
-- Cash, Invoice, Late Fee, Processing Fee Payments (Refunds and Disputes)
SELECT
cbase.key as "ClientID",
concat('AIW-', a.id) as "Check No.",
arl.alloc_amount as "Payment Amount",
abase.id as "Detail/Invoice Number",
p.posted_to_bank_dt as "Date",
case when p.payment_method in ('UNKNOWN', 'PREPAID', 'DEBIT') then 'DEBIT'
else p.payment_method end as "Payment Method",
abase.type as "Type"
FROM payment p
inner join ar_tran a on a.id = p.ar_tran_id
inner join client c on c.id = a.client_id
inner join ar_tran_link arl on arl.ar_tran_mod_id = p.ar_tran_id and arl.active = false
inner join ar_tran abase on abase.id = arl.ar_tran_base_id
left join client cbase on cbase.id = abase.client_id
left join business_entity be on be.id = a.business_entity_id
left join invoice i on i.ar_tran_id = abase.id
WHERE a.is_active
AND a.is_created_by_aiwyn
AND abase.type not in ('LateFee','Invoice')
AND p.posted_to_bank_dt BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()
) src
;
$$;
Comments
0 comments
Please sign in to leave a comment.