SELECT
zcr.sectionhdr,
TOCHAR(ord, 'FM00000000')||major||REPLACE(minor, 'GR', 'GRP')||zcr.div as roworder,
zcr.rowlabel AS rowtext,
sil.irname AS ins,
REPLACE(
CASE
WHEN ctrv IS NULL THEN
REPLACE(format, '{}', '0')
ELSE
CASE zcr.div
WHEN 'CTR' THEN
REPLACE(format, '{}', NVL(TOCHAR(ctrv, 'FM9999999990.00'), '0') )
WHEN 'AVG' THEN
REPLACE(format, '{}', NVL(TOCHAR(avgv, 'FM9999999990.00'), '0') )
WHEN 'PER' THEN
REPLACE(format, '{}', NVL(TOCHAR(100*ctrv/tot, 'FM9999999990.00'), '0') )
WHEN 'SUM' THEN
REPLACE(format, '{}', NVL(TOCHAR(sumv, 'FM9999999990.00'), '0') )
WHEN 'CVL' THEN
REPLACE(format, '{}', offset + NVL(TOCHAR(ctrv * multip, 'FM9999999990.00'), '0') )
WHEN 'AVL' THEN
REPLACE(format, '{}', offset + NVL(TOCHAR(avgv * multip, 'FM9999999990.00'), '0') )
WHEN 'PVL' THEN
REPLACE(format, '{}', offset + NVL(TOCHAR((100*ctrv/tot) * multip, 'FM9999999990.00'), '0') )
WHEN 'SVL' THEN
REPLACE(format, '{}', offset + NVL(TOCHAR(sumv * multip, 'FM9999999990.00'), '0') )
END
END,
'.00',
''
) AS val
FROM
arb.sysirlicence sil
INNER JOIN
trg.zebconfiguredrows zcr
ON
zcr.inst = sil.irnumber
LEFT OUTER JOIN
(
SELECT
major,
minor,
inst,
ctrv,
avgv,
sumv,
MAX(ctrv) over(PARTITION BY inst, major) as tot
FROM
(
SELECT
substr(id, 1, 3) as major,
DECODE(GROUPING(substr(id, 5, 3)), 1, 'GR', substr(id, 5, 3)) as minor,
ins as inst,
sum(v) as sumv,
avg(v) as avgv,
count(id) as ctrv
FROM
(
SELECT trg.exceptionifdbbusy as id, null as v, null as ins FROM dual
UNION ALL
SELECT
CASE flipper
WHEN 1 THEN
CASE
WHEN ccd.activationdate >= dtFrch AND ccd.activationdate < dtToch THEN
CASE
WHEN tch.dtm10 < dtFrdt THEN
'001-000'
WHEN tch.dtm10 >= dtFrdt AND tch.dtm10 < dtTodt THEN
'001-001'
END
END
WHEN 2 THEN
CASE
WHEN tch.dtm10 >= dtFrdt AND tch.dtm10 < dtTodt THEN
'003-001'
END
WHEN 3 THEN
CASE
WHEN tch.dtm26 >= dtFrdt AND tch.dtm26 < dtTodt THEN
CASE
WHEN tch.ingRejected = 6019 THEN
'005-001'
WHEN tch.ingStatusC = 2 THEN
'005-000'
ELSE
'005-003'
END
END
WHEN 4 THEN
CASE
WHEN tch.dtm08 >= dtFrdt AND tch.dtm08 < dtTodt THEN
CASE ingSent
WHEN '6' THEN
'016-006'
WHEN '5' THEN
'016-005'
WHEN '4' THEN
'016-004'
WHEN '3' THEN
'016-003'
WHEN '2' THEN
'016-002'
WHEN '1' THEN
'016-001'
WHEN '0' THEN
'016-000'
END
END
WHEN 5 THEN
CASE
WHEN ccd.activationdate >= dtFrch AND ccd.activationdate < dtToch THEN
CASE ccd.applicationtype
WHEN '3' THEN
CASE WHEN dtm04 IS NULL THEN
'009-003'
ELSE
'009-002'
END
WHEN '2' THEN
'008-001'
WHEN '1' THEN
'008-000'
END
END
WHEN 6 THEN
CASE
WHEN tch.dtm10 >= dtFrdt AND tch.dtm10 < dtTodt THEN
CASE ingSourceID
WHEN 'STO' THEN
'011-001'
WHEN 'WEB' THEN
'011-002'
ELSE
'011-003'
END
END
WHEN 7 THEN
CASE
WHEN cca.acctstatus = '002' THEN
CASE
WHEN closed.recorddate >= dtFrch AND closed.recorddate < dtToch THEN
CASE csc.contractgroup
WHEN 'DEBIT' THEN
'017-001'
WHEN 'MANUAL' THEN
'017-002'
ELSE
'017-003'
END
END
WHEN cca.acctstatus IS NOT NULL AND ccd.activationdate IS NOT NULL THEN
CASE csc.contractgroup
WHEN 'DEBIT' THEN
'019-001'
WHEN 'MANUAL' THEN
'019-002'
ELSE
'019-003'
END
END
WHEN 8 THEN
CASE
WHEN cca.acctstatus = '001' THEN
CASE
WHEN trancounts.counttrans = 0 THEN
'021-001'
WHEN counttrans IS NOT NULL THEN
'021-002'
END
WHEN cca.acctstatus IS NOT NULL THEN
CASE
WHEN trancounts.counttrans = 0 THEN
'021-003'
WHEN counttrans IS NOT NULL THEN
'021-004'
END
END
WHEN 9 THEN
CASE
WHEN cca.acctstatus = '001' AND tch.dtm30 >= dtFrdt AND tch.dtm30 < dtTodt AND tch.ingUserID <> '100' THEN
CASE
WHEN tch.ingSourceID IN ('TAI', 'TAJ', 'TAK', 'TAL') THEN
'105-002'
ELSE
'105-001'
END
END
WHEN 10 THEN
CASE
WHEN tch.dtm12 >= dtFrdt AND tch.dtm12 < dtTodt THEN
'115-001'
END
WHEN 11 THEN
CASE
WHEN (cca.acctstatus = '003' AND closed.recorddate >= dtFrch AND closed.recorddate < dtToch) OR
cca.acctstatus = '001' THEN
'020-011'
END
WHEN 12 THEN
CASE
WHEN (cca.acctstatus = '003' AND closed.recorddate >= dtFrch AND closed.recorddate < dtToch) OR
cca.acctstatus = '001' THEN
'020-012'
END
WHEN 13 THEN
CASE
WHEN (cca.acctstatus = '003' AND closed.recorddate >= dtFrch AND closed.recorddate < dtToch) OR
cca.acctstatus = '001' THEN
'020-013'
END
WHEN 14 THEN
CASE
WHEN (cca.acctstatus = '003' AND closed.recorddate >= dtFrch AND closed.recorddate < dtToch) OR
cca.acctstatus = '001' THEN
'020-014'
END
WHEN 15 THEN
CASE
WHEN (cca.acctstatus = '003' AND closed.recorddate >= dtFrch AND closed.recorddate < dtToch) OR
cca.acctstatus = '001' THEN
CASE csc.contractgroup
WHEN 'DEBIT' THEN
'020-015'
WHEN 'MANUAL' THEN
'020-016'
END
END
WHEN 16 THEN
CASE
WHEN (tch.dtm26 >= dtFrdt AND tch.dtm26 < dtTodt) OR (ccd.activationdate >= dtFrch AND ccd.activationdate < dtToch) THEN
'006-001'
END
END AS id,
null as v,
zai.instnum AS ins
FROM
trg.zebactiveinstits zai
INNER JOIN
trg.tblClientHeader tch
ON
tch.ingInstitutionID = zai.instnum
LEFT OUTER JOIN
arb.cisclientdetails ccd
ON
ccd.irnumber = tch.ingInstitutionId AND
ccd.uniquenumber = tch.ingContID AND
ccd.recordtype = '002'
LEFT OUTER JOIN
arb.casclientaccount cca
ON
cca.clientnumber = ccd.clientnumber AND
cca.irnumber = ccd.irnumber AND
cca.recordtype = ccd.recordtype AND
cca.billinglevel = '001'
LEFT OUTER JOIN
arb.chtservicecontractid csc
ON
csc.indexfield = cca.servicecontractid AND
csc.irnumber = cca.irnumber
LEFT OUTER JOIN
(
SELECT
DECODE(cca.recordtype, '001', cca.parentclientnumber, '002', cca.clientnumber) as clientnumber,
'002' as recordtype,
cca.irnumber,
count(it.acctnumber) as counttrans
FROM
trg.zebactiveinstits zai
INNER JOIN
arb.casclientaccount cca
ON
cca.irnumber = zai.instnum
LEFT OUTER JOIN
arb.inttransactions it
ON
it.acctnumber = cca.acctnumber
WHERE
cca.recordtype in ('001', '002')
GROUP BY
DECODE(cca.recordtype, '001', cca.parentclientnumber, '002', cca.clientnumber),
cca.irnumber
) trancounts
ON
trancounts.clientnumber = cca.clientnumber AND
trancounts.irnumber = cca.irnumber AND
trancounts.recordtype = cca.recordtype
LEFT OUTER JOIN
(
SELECT
irnumber,
clientnumber,
recordtype,
acctstatus,
recorddate,
rownumber() over(partition by irnumber, clientnumber order by recorddate, recordtime desc) as rank
FROM
arb.casacctstatuschange
WHERE
recorddate >= {?pDateFrom} AND
recorddate < {?pDateTo} AND
acctstatus = '003'
) closed
ON
closed.irnumber = cca.irnumber AND
closed.clientnumber = cca.clientnumber AND
closed.recordtype = cca.recordtype AND
closed.acctstatus = cca.acctstatus AND
closed.rank = 1
CROSS JOIN
(
SELECT
rownum as flipper,
{?pDateFrom} as dtFrch,
{?pDateTo} as dtToch,
TODATE({?pDateFrom},'YYYYMMDD') as dtFrdt,
TODATE({?pDateTo},'YYYYMMDD') as dtTodt
FROM
trg.appsprefs
WHERE
rownum <= 16
) c
UNION ALL
SELECT
CASE chts.servicegroup
WHEN 'NAMED' THEN
CASE chtsci.contractgroup
WHEN 'DEBIT' THEN
'030-001'
WHEN 'MANUAL' THEN
'030-001'
WHEN 'MAILER' THEN
'030-005'
END
WHEN 'BEARER' THEN
'030-003'
WHEN 'PORDER' THEN
'030-002'
WHEN 'INSTANT' THEN
'030-004'
END AS id,
1 as v,
scc.irnumber AS ins
FROM
trg.zebactiveinstits zai
INNER JOIN
arb.svcclientcards scc
ON
scc.irnumber = zai.instnum
INNER JOIN
arb.chtservices chts
on
chts.indexfield = scc.serviceid
INNER JOIN
arb.chtservicecontractid chtsci
on
chtsci.indexfield = scc.servicecontractid
WHERE
scc.recorddate >= {?pDateFrom} AND
scc.recorddate < {?pDateTo} AND
(
(chts.servicegroup = 'NAMED' AND chtsci.contractgroup IN ('DEBIT','MANUAL', 'MAILER')) OR
chts.servicegroup IN ('BEARER','PORDER','INSTANT')
) AND
chtsci.contractgroup <> 'TEST'
UNION ALL
SELECT
CASE scsc.cardeventstatus
WHEN '102' THEN
'032-001'
WHEN '107' THEN
'032-001'
WHEN '109' THEN
'032-002'
WHEN '110' THEN
'032-003'
WHEN '101' THEN
'032-003'
END AS id,
1 as v,
zai.instnum AS ins
FROM
trg.zebactiveinstits zai
INNER JOIN
arb.svccardstatuschange scsc
ON
scsc.irnumber = zai.instnum
INNER JOIN
arb.svcclientcards scc
USING
(cardnumber)
INNER JOIN
arb.chtservicecontractid chtsci
ON
chtsci.indexfield = scc.servicecontractid AND
chtsci.contractgroup <> 'TEST'
WHERE
scsc.cardeventstatus IN ('101','102','107','109','110') AND
scsc.effectivedate >= {?pDateFrom} AND
scsc.effectivedate < {?pDateTo}
UNION ALL
SELECT
CASE flipper
WHEN 1 THEN
CASE
WHEN ccam.acctstatus = '001' AND ccac.acctstatus = '001' AND scc.cardstatus = '001' THEN
'034-001'
WHEN chtsci.contractgroup = 'MAILER' AND ccam.acctstatus <> '001' THEN
'034-002'
WHEN chtsci.contractgroup = 'INSTANT' AND ccam.acctstatus <> '001' THEN
'034-003'
ELSE
'034-004'
END
WHEN 2 THEN
CASE WHEN NOT(ccam.acctstatus = '001' AND ccac.acctstatus = '001' AND scc.cardstatus = '001') THEN
'036-000'
END
END AS id,
1 as v,
zai.instnum AS ins
FROM
trg.zebactiveinstits zai
INNER JOIN
arb.casclientaccount ccam
ON
ccam.irnumber = zai.instnum
INNER JOIN
arb.casclientaccount ccac
ON
ccac.parentclientnumber = ccam.clientnumber AND
ccac.irnumber = ccam.irnumber
INNER JOIN
arb.svcclientcards scc
ON
scc.clientnumber = ccac.clientnumber AND
scc.irnumber = ccac.irnumber
INNER JOIN
arb.chtservicecontractid chtsci
ON
chtsci.indexfield = ccam.servicecontractid AND
chtsci.irnumber = ccam.irnumber
CROSS JOIN
(
SELECT
rownum as flipper
FROM
trg.appsprefs
WHERE
rownum <= 2
) c
WHERE
scc.cardstatus = '001' AND
ccac.recordtype = '001' AND
ccam.recordtype = '002' AND
ccam.accounttypeid = '177'
UNION ALL
SELECT
'038-000' as id,
count(*) v,
irnumber
FROM
arb.casclientaccount cca
INNER JOIN
trg.zebactiveinstits zai
ON
zai.instnum = cca.irnumber
WHERE
cca.recordtype = '001'
GROUP BY
irnumber,
groupnumber
UNION ALL
SELECT
CASE flipper
WHEN 1 THEN
CASE
WHEN iot.responsecode = '000' THEN
CASE
WHEN iot.terminalid <> 'TELER999' THEN
'050-001'
WHEN iot.terminalid = 'TELER999' AND cwa.authoperator = 'WEB' THEN
'051-001'
WHEN iot.terminalid = 'TELER999' AND cwa.authoperator = 'trg' THEN
'052-001'
ELSE
'053-001'
END
ELSE
CASE
WHEN iot.terminalid <> 'TELER999' THEN
'050-002'
WHEN iot.terminalid = 'TELER999' AND cwa.authoperator = 'WEB' THEN
'051-002'
WHEN iot.terminalid = 'TELER999' AND cwa.authoperator = 'trg' THEN
'052-002'
ELSE
'053-002'
END
END
WHEN 2 THEN
CASE
WHEN iot.responsecode = '000' THEN
CASE
WHEN iot.terminalid <> 'TELER999' THEN
'054-001'
WHEN iot.terminalid = 'TELER999' AND cwa.authoperator = 'WEB' THEN
'055-001'
WHEN iot.terminalid = 'TELER999' AND cwa.authoperator = 'trg' THEN
'056-001'
ELSE
'057-001'
END
ELSE
CASE
WHEN iot.terminalid <> 'TELER999' THEN
'054-002'
WHEN iot.terminalid = 'TELER999' AND cwa.authoperator = 'WEB' THEN
'055-002'
WHEN iot.terminalid = 'TELER999' AND cwa.authoperator = 'trg' THEN
'056-002'
ELSE
'057-002'
END
END
WHEN 3 THEN
CASE
WHEN iot.terminalid = 'TELER999' AND cwa.authoperator = 'trg' THEN
'058-001'
ELSE
'058-002'
END
WHEN 4 THEN
CASE
WHEN iot.responsecode <> '000' THEN
'060-001'
ELSE
'060-002'
END
WHEN 5 THEN
CASE
WHEN iot.reversalflag = '000' AND iot.responsecode = '000' THEN
'061-000'
END
WHEN 6 THEN
CASE
WHEN iot.transactionstatus = '005' THEN
'062-000'
END
END as id,
abs(tranamountgr) as v,
iot.irnumber AS ins
FROM
trg.zebactiveinstits zai
INNER JOIN
arb.intonlinetransactions iot
ON
iot.irnumber = zai.instnum
LEFT OUTER JOIN
arb.coswebauth cwa
ON
cwa.authref = iot.retrievalreference
CROSS JOIN
(
SELECT
rownum as flipper
FROM
trg.appsprefs
WHERE
rownum <= 6
) c
WHERE
iot.recorddate >= {?pDateFrom} AND
iot.recorddate < {?pDateTo}
UNION ALL
SELECT
CASE flipper
WHEN 1 THEN
CASE it.transactiontype
WHEN '005' THEN
'064-001'
WHEN '006' THEN
'064-002'
END
WHEN 2 THEN
CASE WHEN it.transactiontype = '005' THEN
'066-001'
END
END as id,
ABS(it.tranamountgr) * DECODE(it.transactiontype, '006', -1, 1) AS v,
irnumber AS ins
FROM
trg.zebactiveinstits zai
INNER JOIN
arb.inttransactions it
ON
it.irnumber = zai.instnum
CROSS JOIN
(
SELECT
rownum as flipper,
{?pDateFrom} as dtFrch,
{?pDateTo} as dtToch,
TOCHAR(ADDMONTHS(TODATE({?pDateFrom},'YYYYMMDD'), -1), 'YYYYMMDD') as dtFrPrevMoch,
TOCHAR(ADDMONTHS(TODATE({?pDateTo},'YYYYMMDD'), -1), 'YYYYMMDD') as dtToPrevMoch
FROM
trg.appsprefs
WHERE
rownum <= 2
) c
WHERE
(
(
it.transactiondate >= dtFrch AND
it.transactiondate < dtToch AND
it.irnumber <> '00000005'
)
OR
(
it.transactiondate >= dtFrPrevMoch AND
it.transactiondate < dtToPrevMoch AND
it.irnumber = '00000005'
)
) AND
it.transactiontype IN ('005','006') AND
it.transactionstatus = '002' AND
it.transactiondestination = '018'
UNION ALL
SELECT
CASE flipper
WHEN 1 THEN
CASE eventgroupdesc
WHEN 'Initial' THEN
CASE
WHEN nthoccur = 1 AND chargeratio > 0 THEN
'100-001'
WHEN nthoccur > 1 AND chargeratio > 0 THEN
'100-002'
ELSE
'100-003'
END
WHEN 'Followup' THEN
CASE
WHEN nthoccur = 1 AND chargeratio > 0 THEN
'101-001'
WHEN nthoccur > 1 AND chargeratio > 0 THEN
'101-002'
ELSE
'101-003'
END
END
WHEN 2 THEN
CASE WHEN ingStatusA = 3 THEN
CASE WHEN nthoccur = 1 AND chargeratio > 0 THEN
'103-001'
WHEN nthoccur > 1 AND chargeratio > 0 THEN
'103-002'
ELSE
'103-003'
END
END
END AS id,
1 as v,
inginstitutionID
FROM
(
SELECT
inginstitutionID,
ingSourceID,
chargeratio,
tch.ingStatusA,
eventgroupdesc,
ROWNUMBER() OVER(PARTITION BY ingContID, chargeratio, eventgroupdesc ORDER BY dtmCallDate, ingCallID) AS nthoccur
FROM
trg.tblClientCall tcc
INNER JOIN
(
SELECT
DISTINCT ingContID, startdate, enddate
FROM
trg.tblClientCall
CROSS JOIN
(
SELECT
TODATE({?pDateFrom},'YYYYMMDD') AS startdate,
TODATE({?pDateTo},'YYYYMMDD') AS enddate
FROM dual
)
WHERE
dtmCallDate >= startdate AND
dtmCallDate < enddate
) activecontids
USING(ingContID)
INNER JOIN
trg.tblClientHeader tch
USING(ingContID)
INNER JOIN
trg.rpteventgroups reg
ON
reg.eventid = tcc.ingEventID
WHERE
dtmCallDate >= startdate AND
dtmCallDate < enddate
)
CROSS JOIN
(
SELECT
rownum as flipper,
{?pDateFrom} as dtFrch,
{?pDateTo} as dtToch,
TOCHAR(ADDMONTHS(TODATE({?pDateFrom},'YYYYMMDD'), -1), 'YYYYMMDD') as dtFrPrevMoch,
TOCHAR(ADDMONTHS(TODATE({?pDateTo},'YYYYMMDD'), -1), 'YYYYMMDD') as dtToPrevMoch
FROM
trg.appsprefs
WHERE
rownum <= 2
) c
UNION ALL
SELECT
CASE flipper
WHEN 1 THEN
CASE
WHEN NVL(SUM(tranamountdecb4prd), 0) < 1000 AND SUM(tranamountdec) >= 1000 THEN
'107-001'
END
WHEN 2 THEN
CASE
WHEN SUM(tranamountdecinprd) IS NOT NULL THEN
'107-002'
END
END AS id,
SUM(tranamountdec) as v,
it.irnumber as ins
FROM
trg.zebactiveinstits zai
INNER JOIN
arb.casclientaccount cca
ON
cca.irnumber = zai.instnum
INNER JOIN
(
SELECT
CASE
WHEN it.transactiondate < dtFrch THEN
DECODE(it.drcrindicator, '001', it.tranamountgr * -1, it.tranamountgr)
END AS tranamountdecb4prd,
CASE
WHEN it.transactiondate >= dtFrch THEN
DECODE(it.drcrindicator, '001', it.tranamountgr * -1, it.tranamountgr)
END AS tranamountdecinprd,
DECODE(it.drcrindicator, '001', it.tranamountgr * -1, it.tranamountgr) AS tranamountdec,
it.*,
c.*
FROM
arb.inttransactions it
CROSS JOIN
(
SELECT
rownum as flipper,
{?pDateFrom} as dtFrch,
{?pDateTo} as dtToch,
TOCHAR(ADDMONTHS(TODATE({?pDateFrom},'YYYYMMDD'), -1), 'YYYYMMDD') as dtFrPrevMoch,
TOCHAR(ADDMONTHS(TODATE({?pDateTo},'YYYYMMDD'), -1), 'YYYYMMDD') as dtToPrevMoch
FROM
trg.appsprefs
WHERE
rownum <= 2
) c
WHERE
it.transactiontype IN ('005', '006', '951', '950') AND
it.transactiondate < dtToch AND
it.transactionstatus = '002' AND
it.transactiondestination = '018'
) it
ON
it.irnumber = cca.irnumber AND
it.acctnumber = cca.acctnumber
INNER JOIN
arb.cisclientdetails ccd
ON
ccd.irnumber = cca.irnumber AND
ccd.clientnumber = DECODE(cca.recordtype, '001', cca.parentclientnumber, '002', cca.clientnumber)
WHERE
ccd.campaignname IN ('TAI','TAJ','TAK','TAL')
GROUP BY
it.irnumber,
it.groupnumber,
flipper
UNION ALL
SELECT
'110-001'
AS id,
SUM(tranamountgr) as v,
irnumber
FROM
arb.casclientpayment ccp
WHERE
recorddate >= {?pDateFrom} AND
recorddate < {?pDateTo}
GROUP BY
irnumber,
clientnumber
UNION ALL
SELECT
'114-001'
AS id,
null as v,
tcn.tInstitutionID
FROM
trg.tblClientNotes tcn
WHERE
tcn.tEventID = '1603' AND
tcn.dtmNotesDate >= TODATE({?pDateFrom}, 'YYYYMMDD') AND
tcn.dtmNotesDate < TODATE({?pDateTo}, 'YYYYMMDD')
UNION ALL
SELECT
'199-001',
1 as v,
zai.instnum as ins
FROM
trg.zebactiveinstits zai
)
WHERE
id IS NOT NULL
GROUP BY
GROUPt SETS
(
(
substr(id, 1, 3),
substr(id, 5, 3),
ins
),
(
substr(id, 1, 3),
ins
)
)
)
)zebra
USt
(inst,major,minor)
WHERE
(zcr.div IN ('CTR', 'CVL') AND NVL(ctrv, 0) >= zcr.lbound AND NVL(ctrv, 0) < zcr.ubound) OR
(zcr.div IN ('SUM', 'SVL') AND NVL(sumv, 0) >= zcr.lbound AND NVL(sumv, 0) < zcr.ubound) OR
(zcr.div IN ('AVG', 'AVL') AND NVL(avgv, 0) >= zcr.lbound AND NVL(avgv, 0) < zcr.ubound) OR
(zcr.div IN ('PER', 'PVL') AND NVL(100*ctrv/tot, 0) >= zcr.lbound AND NVL(100*ctrv/tot, 0) < zcr.ubound)
