--The full list of DD transactions within the BOB files in August and September.
--This takes ~30-40 seconds to run.
select
cast(prh.post_utc as date) as 'Approx. BOB File Date',
ddt.fil_nbr as 'DD File Number',
prd.ctm_nbr as 'Billing Customer Number',
prd.src_dta as 'Item Source',
case when prd.src_dta = 'CIR' then pub.pub_ttl else sac.scde_dsc end as 'Magazine Title',
ddt.crd_amt as 'DD Amount',
ddt.ddt_sta as 'DDT Status'
from arpprh_m prh
join arpprd_m prd on prd.crx_dte = prh.crx_dte and prd.crx_ops = prh.crx_ops and prd.pay_type = prh.pay_type and prd.pay_code = prh.pay_code and
	prd.ctm_nbr = prh.ctm_nbr and prd.crx_vch = prh.crx_vch and prd.aud_src = prh.aud_src and prd.sub_grp = prh.sub_grp
join arpddt_m ddt on ddt.crx_vch = prd.crx_vch and ddt.ddt_typ = 'PYMT'
join arprdd_m rdd on rdd.ddi_idn = ddt.ddi_idn
left join cdsitm_m itm on itm.itm_num = prd.itm_num
left join invsac_t sac on sac.sac_cde = itm.itm_sac
left join cirpub_t pub on pub.pub_cde = prd.itm_num
where (prd.crx_ops <> 'ARPUDA' or prd.pay_type = 'DD') and
	prh.pay_code <> '' and prd.pad_amt > 0 and
	cast(prh.post_utc as date) between {d'2018-08-01'} and {d'2018-09-30'}
order by cast(prh.post_utc as date), prd.itm_num


--Information about which DDT file numbers can be found in each BOB file. This is an approximation, since
--ARPBGL runs do not correspond 1-to-1 with each day (i.e the previous day's transactions may be found in
--the following day's BOB file; or, if ARPBGL was not run on the approximate date, those lines should be in
--the next BOB file).
select
cast(prh.post_utc as date) as 'Approx. BOB File Date',
ddt.fil_nbr as 'DD File Number',
count(*) as 'Count'
from arpprh_m prh
join arpprd_m prd on prd.crx_dte = prh.crx_dte and prd.crx_ops = prh.crx_ops and prd.pay_type = prh.pay_type and prd.pay_code = prh.pay_code and
	prd.ctm_nbr = prh.ctm_nbr and prd.crx_vch = prh.crx_vch and prd.aud_src = prh.aud_src and prd.sub_grp = prh.sub_grp
join arpddt_m ddt on ddt.crx_vch = prd.crx_vch and ddt.ddt_typ = 'PYMT'
join arprdd_m rdd on rdd.ddi_idn = ddt.ddi_idn
left join cdsitm_m itm on itm.itm_num = prd.itm_num
left join invsac_t sac on sac.sac_cde = itm.itm_sac
left join cirpub_t pub on pub.pub_cde = prd.itm_num
where (prd.crx_ops <> 'ARPUDA' or prd.pay_type = 'DD') and
	prh.pay_code <> '' and prd.pad_amt > 0 and ddt.fil_nbr <> '0' and
	cast(prh.post_utc as date) between {d'2018-08-01'} and {d'2018-09-30'}
group by cast(prh.post_utc as date), ddt.fil_nbr
order by cast(prh.post_utc as date), ddt.fil_nbr


--These are the DDTs where the file number is reported as 0, due to the next processing date not
--being reached yet. Note that the status is R (Requested Outbound) rather than S (Successful).
--These will be picked up by ARP376 the first time it is run after their DDT.NXT-INF.
select
cast(prh.post_utc as date) as 'Approx. BOB File Date',
ddt.fil_nbr as 'DD File Number',
prd.ctm_nbr as 'Billing Customer Number',
prd.src_dta as 'Item Source',
case when prd.src_dta = 'CIR' then pub.pub_ttl else sac.scde_dsc end as 'Magazine Title',
ddt.crd_amt as 'DD Amount',
ddt.ddt_sta as 'DDT Status',
ddt.nxt_inf as 'DDT Next Date'
from arpprh_m prh
join arpprd_m prd on prd.crx_dte = prh.crx_dte and prd.crx_ops = prh.crx_ops and prd.pay_type = prh.pay_type and prd.pay_code = prh.pay_code and
	prd.ctm_nbr = prh.ctm_nbr and prd.crx_vch = prh.crx_vch and prd.aud_src = prh.aud_src and prd.sub_grp = prh.sub_grp
join arpddt_m ddt on ddt.crx_vch = prd.crx_vch and ddt.ddt_typ = 'PYMT'
join arprdd_m rdd on rdd.ddi_idn = ddt.ddi_idn
left join cdsitm_m itm on itm.itm_num = prd.itm_num
left join invsac_t sac on sac.sac_cde = itm.itm_sac
left join cirpub_t pub on pub.pub_cde = prd.itm_num
where (prd.crx_ops <> 'ARPUDA' or prd.pay_type = 'DD') and
	prh.pay_code <> '' and prd.pad_amt > 0 and ddt.fil_nbr = '0' and
	cast(prh.post_utc as date) between {d'2018-08-01'} and {d'2018-09-30'}
order by cast(prh.post_utc as date), prd.itm_num


--These are the DDTs that generated errors during ARP376, identified by the DDT-STA of E they were given.
--These errors occurred because, at the time, the ARPRDD record had a status (RDD-STA) that was not active (e.g. not A, P, or blank).
select
cast(prh.post_utc as date) as 'Approx. BOB File Date',
ddt.fil_nbr as 'DD File Number',
prd.ctm_nbr as 'Billing Customer Number',
prd.src_dta as 'Item Source',
case when prd.src_dta = 'CIR' then pub.pub_ttl else sac.scde_dsc end as 'Magazine Title',
ddt.crd_amt as 'DD Amount',
ddt.ddt_sta as 'DDT Status'
from arpprh_m prh
join arpprd_m prd on prd.crx_dte = prh.crx_dte and prd.crx_ops = prh.crx_ops and prd.pay_type = prh.pay_type and prd.pay_code = prh.pay_code and
	prd.ctm_nbr = prh.ctm_nbr and prd.crx_vch = prh.crx_vch and prd.aud_src = prh.aud_src and prd.sub_grp = prh.sub_grp
join arpddt_m ddt on ddt.crx_vch = prd.crx_vch and ddt.ddt_typ = 'PYMT'
join arprdd_m rdd on rdd.ddi_idn = ddt.ddi_idn
left join cdsitm_m itm on itm.itm_num = prd.itm_num
left join invsac_t sac on sac.sac_cde = itm.itm_sac
left join cirpub_t pub on pub.pub_cde = prd.itm_num
where (prd.crx_ops <> 'ARPUDA' or prd.pay_type = 'DD') and
	prh.pay_code <> '' and prd.pad_amt > 0 and ddt.ddt_sta = 'E' and
	cast(prh.post_utc as date) between {d'2018-08-01'} and {d'2018-09-30'}
order by cast(prh.post_utc as date), prd.itm_num


--Information requested about the transaction lines that were stripped from the BACS files.
select
rem.file_nbr as 'File Number',
rem.sort_code as 'Bank ID Number',
rem.acct_nbr as 'Bank Account Number',
rem.ddi_idn as 'DDI-IDN',
rem.bks_ddi as 'BKS-DDI',
rem.amt_paid as 'Amount Paid',
case when rem.tran_typ = '17' then 'PYMT' else 'CANCL' end as 'Transaction Type'
from DdtRemovedFromBankFile rem
where rem.file_nbr in ('2463', '2465', '2466', '2467', '2469')