One of the processes I set up required a merging of a few different data sources. Besides our own data store of user input data, we also had our own data store of operational account data, and went to another system to get account billing data, and yet another system for customer contract data. This query was an aggregation of several queries that, all together, aggregated the exact data we needed for our data warehouse import. Some of the neat features of this query are the company name partition with replace to account for manual data entry discrepencies…
cursor c_contracts(p_start_date date, p_end_date date, p_snapshot_date date) is
Select seq_some.nextval, c.propid, to_char(p_start_date,'YYYYMM'),
PRODUCT_NAME ,
ACCOUNTS_SOLD ,
case when bui_account.bdm_id is not null then trim(csh_account.bdm)
when bui_contract.bdm_id is not null then trim(csh_contract.bdm)
else 'Unknown' end BDM,
trim(c.account_bdm) account_bdm,
trim(c.contract_bdm) contract_bdm,
busunit_id,
c.COMPANY_NAME,
c.maxid COMPANY_ID,
TRUNC(CONTRACT_TERM_MONTHS,2),
c.COUNTER_SIGN_DATE,
TRUNC(TOTAL_CREDIT_COST,2),
CUSTOMERS_SOLD,
c.EDC_ACCTNO,
c.EDC_NAME,
c.EST_POWER_END_DATE,
c.EST_POWER_FLOW_DATE,
ISO,
MW_SOLD,
TRUNC(c.SALES_AGENT_FEE,2),
c.SALESFEE_CONAME,
STATE,
STATUS,
TRUNC(ORIGINATION_GROSSMARGIN,2),
TRUNC(ORIGINATION_NETMARGIN,2),
TRUNC(ORIGINATION_VOLUME,2),
c.ZONE,
c.PRICE_DATE,
substr(1000000000 + nvl(trim(regexp_replace(regexp_replace(c.duns_no,'-|/|[a-z|A-Z]|\.|[[:space:]]',''),'([[:digit:]]{9})[[:digit:]]*','\1')),'999999999'),2,9) DUNS_NO,
c.SIC_CODE,
c.CCM_ID,
somebillingsystem.num_quotes,
ar.AR_Bal_31_60,
ar.AR_Bal_61_90,
ar.AR_Bal_91Plus,
ar.bad_debt_balance,
TRUNC(FM_NET_MARGIN,2) FLOW_Margin,
TRUNC(FM_VOLUME,2) FLOW_Volume,
s_billing.bill_type, ---------- BILL TYPES AND OPTIONS
s_billing.billing_option,
trim(Green_Volume) ,
trim(REC_GREEN_VOLUME),
FM_Sales_Agent_Fee,
c.leg_month,
c.leg_year,
c.annual_mwh,
contract_type,
NON_STD_TERMS_FLG,
TELESALES_FLG,
somebillingsystem.broker2,
somebillingsystem.ou_type_cd,
somebillingsystem.x_sub_type,
ls.Billing_Weight,
somebillingsystem.early_renewal,
c.servzip,
c.acquisition_type,
1, p_snapshot_date
, c.OFFER_TYPE
from ( select e.*, max(e.cust_id) over (partition by replace(replace(e.company_name,','),'.')) maxid
, NULL OFFER_TYPE
from some_UNI_CONTRACT_SOME_EXTRACT e
union
select null customers_sold, null accounts_sold,
r.MASTERCONTRACT_OUR_NUM propid, r.CONTRACT_STATUS status,
r.PRODUCTNAME product_name, r.BDM account_bdm, 'RESI' company_name,
r.BDM contract_bdm, to_char(r.COUNTER_SIGN_DATE,'dd-MON-yy') COUNTER_SIGN_DATE,
'1' cust_id, to_char(r.EST_POWER_FLOW_DATE,'dd-MON-yy') EST_POWER_FLOW_DATE, to_char(r.EST_POWER_END_DATE,'dd-MON-yy') EST_POWER_END_DATE,
r.LEAD_SEGMENT, null mw_Sold, null product_group, r.BUSUNIT_NAME region,
null sales_agent_fee, null salesfee_coname, null sic_code, r.STATE, r.ZONE, null LEG_MONTH, null LEG_YEAR, null fm_net_margin,
null fm_volume, null fm_sales_agent_fee, r.TOTAL_MARGIN origination_netmargin, r.TOTAL_VOLUME origination_volume,
null origination_grossmargin, r.TOTAL_CREDITCOST total_credit_cost, null duns_no, null busunit_id, r.ISO,
null price_date, null ccm_id, null rec_green_volume, null green_volume, r.UDC_NAME edc_name, r.contract_term contract_term_months,
r.EDC_ACCTNO, r.ZIP servzip, r.ANNUAL_MWH, null contract_type, null non_std_terms_flg, null telesales_flg, null acquisition_type, '1'
, r.OFFER_TYPE
from (select distinct MASTERCONTRACT_OUR_NUM, CONTRACT_STATUS, PRODUCTNAME, BDM,
COUNTER_SIGN_DATE, EST_POWER_FLOW_DATE, EST_POWER_END_DATE, LEAD_SEGMENT,
BUSUNIT_NAME, STATE, ZONE,
sum(TOTAL_MARGIN) over (partition by mastercontract_our_num) TOTAL_MARGIN,
sum(TOTAL_VOLUME) over (partition by mastercontract_our_num) TOTAL_VOLUME,
sum(TOTAL_CREDITCOST) over (partition by mastercontract_our_num) TOTAL_CREDITCOST,
ISO, UDC_NAME, contract_term,
EDC_ACCTNO, ZIP, ANNUAL_MWH
, OFFER_TYPE
from cer_res.residential) r
)c
-------------- somebillingsystem
left join (
select ag.agree_num, (select brk.name from s_org_ext@sblreadonly brk where brk.row_id = ag.x_broker_2_account_id) broker2,
(select count(q.row_id) from s_doc_quote@sblreadonly q where q.quote_num = qu.quote_num) num_quotes,
round(months_between(sysdate,co.cust_since_dt),2) cust_accumulated_term, co.x_sub_type, co.ou_type_cd,
case when exists (select ai.row_id from somebillingsystem.s_agree_item@sblreadonly ai
join somebillingsystem.s_org_ext@sblreadonly sa on ai.x_sa_ou_id = sa.row_id
where ag.row_id = ai.doc_agree_id
and ai.itm_stat_cd = 'Pending' and sa.cust_stat_cd = 'Enrolled'
and ag.stat_cd = 'Pending' and ag.x_offer_type = 'Renewal' ) then 1 else 0 end Early_Renewal
from S_DOC_AGREE@sblreadonly AG
JOIN S_DOC_QUOTE@sblreadonly QUON AG.QUOTE_ID = QU.ROW_ID(+)
JOIN S_ORG_EXT@sblreadonly CO ON AG.TARGET_OU_ID = CO.ROW_ID
)somebillingsystem on c.propid = somebillingsystem.agree_num
------------- BILL OPTIONS
left join ( select ag.agree_num,
min(bp.x_group_bill) billing_option, -- Multi over Single
min(bp.bill_pref_cd) bill_type -- SOME (Charges) over UDC (Rate)
from s_doc_agree@sblreadonly ag
join s_agree_item@sblreadonly ai on ag.row_id = ai.doc_agree_id
join s_org_ext@sblreadonly sa on ai.x_sa_ou_id = sa.row_id
join s_party_rel@sblreadonly pr on sa.row_id = pr.rel_party_id
and pr.rel_type_cd = 'Bill Group'
and pr.x_primary_bill_group = 'Y'
join s_org_ext@sblreadonly bg on pr.party_id = bg.row_id
join S_INV_PROF@sblreadonly bp on bg.row_id = bp.accnt_id
group by ag.agree_num
) s_billing on s_billing.agree_num = c.propid
--------- AR_BALANCE
left join (select contract_id, /*ar.edc_acctno, */sum(ar.ar_bal_current) ar_bal_current, sum(ar.ar_bal_1_30) ar_bal_1_30 ,
sum(ar.ar_bal_31_60) ar_bal_31_60, sum(ar.ar_bal_61_90) ar_bal_61_90,
sum(ar.ar_bal_91plus) ar_bal_91plus, ar.snapshot_date, round(sum(bad.balance),2) bad_debt_balance
from (select x.contract_id, x.edc_acctno, sum(b.ar_bal_current) ar_bal_current, sum(b.ar_bal_1_30) ar_bal_1_30 ,
sum(b.ar_bal_31_60) ar_bal_31_60, sum(b.ar_bal_61_90) ar_bal_61_90,
sum(b.ar_bal_91plus) ar_bal_91plus, b.snapshot_date
from some_uni_cust_arb b
join some_uni_edc_x_propid x on b.edc_acctno = x.edc_acctno and x.snapshot_date = b.snapshot_date
where b.snapshot_date = (select max(snapshot_date) from some_uni_cust_arb where snapshot_date <= p_end_date)
--and x.contractid = '1-220606257'
group by x.contract_id, x.edc_acctno, b.snapshot_date
) ar
-- BAD DEBT LOGIC : ave of EDCs, summed up at contract level, so three EDCs in a contract have 24 month avgs of 12, 1250, 650 bad debt show at contract 1912
join (select b.edc_acctno, avg(b.ar_bal_91plus) balance
from (select a.edc_acctno,trunc(a.snapshot_date,'MM'), max(a.snapshot_date) maxdate
from some_uni_cust_arb a group by a.edc_acctno, trunc(snapshot_date, 'MM')
) maxdate
join some_uni_cust_arb b on maxdate.edc_acctno = b.edc_acctno and maxdate.maxdate = b.snapshot_date
where b.snapshot_date between add_months(p_start_date,-23) and p_end_date
group by b.edc_acctno
) bad on ar.edc_acctno = bad.edc_acctno
group by ar.contract_id, ar.snapshot_date
) ar on ar.contract_id = c.propid
--------- WORK QUEUES AND BILLG MODES
left join (
select contractid,
-- for breakout analysis- remove grouping if you run this query alone
/*min(billgroupoption) over(partition by contractid) billgroupoption, -- M trumps S
max(bgbmf) over(partition by contractid) bgbmf, -- in case of multiple just get the highest
max(ssomef) over(partition by contractid) ssomef,
q.uidwqitem, q.openedtime ,q.wqtypecode, q.wqqueuecode, q.weight
,sum(nvl(weight,0)) over(partition by contractid) sumweightpercontract*/
decode(min(billgroupoption),'M',max(bgbmf), max(ssomef)) + sum(weight) Billing_Weight
from (
select con.contractid,
h.billgroupoption,
bg.billingmodeflag bgbmf,
sa.billingmodeflag ssomef,
--wq.uidwqitem, wq.openedtime ,wq.wqtypecode, wq.wqqueuecode,
wq.weight
from (select contractid, uidaccount
from (select c.contractid, ci.uidaccount, max(c.contractid) over (partition by ci.uidaccount) maxid
from (select uidaccount, uidcontract from CUSTOMERSYS.lscmcontractitem@ls_link) ci
join (select contractid, starttime, stoptime, uidcontract, executetime
from CUSTOMERSYS.lscmcontract@ls_link) c on ci.uidcontract = c.uidcontract and c.executetime is not null
where c.starttime < p_end_date and c.stoptime > add_months(p_start_date,-2)
) where contractid = maxid
) con
-- SUBSELECT to pick one(can't tell which is correct) when LS has bad dupes
left join (select uidaccount, accountid, billingmodeflag
from (select a.uidaccount, a.accountid, a.udcacctid, a.billingmodeflag,
a.lstime, max(a.lstime) over (partition by a.udcacctid) maxlstime
from CUSTOMERSYS.ACCOUNT@ls_link a
where a.accounttypecode = 'SERVICE_ACCOUNT' )
where maxlstime = lstime
) sa on sa.uidaccount = con.uidaccount
LEFT JOIN (select lci.accountid, lci.wqtypecode, lci.wqqueuecode, q.weight--, lci.uidwqitem, lci.openedtime
from CUSTOMERSYS.LSWQCLOSEDITEM@ls_link lci
join some_work_queue q on lci.WQTYPECODE = q.type and lci.WQQUEUECODE = q.code
where CLOSEDTIME Between add_months(p_start_date,-2) and p_end_date
) wqON wq.accountid = sa.accountid
-- SUBSELECT to pick one(can't tell which is correct) when LS has bad overlap
left JOIN (select uidaccountfrom, uidaccountto
from (select l.uidaccountfrom, l.uidaccountto,
max(l.uidaccountfrom) over (partition by l.uidaccountto) maxbgacctid
from CUSTOMERSYS.LSACCOUNTREL@ls_link l
where l.starttime < p_end_date and l.stoptime > add_months(p_start_date,-2))
where uidaccountfrom = maxbgacctid
)x ON sa.UIDACCOUNT = x.UIDACCOUNTTO
left join CUSTOMERSYS.ACCOUNT@ls_link bg on bg.UIDACCOUNT = x.UIDACCOUNTFROM
and bg.accounttypecode = 'BILLING_GROUP'
left JOIN (select billgroupoption, uidaccount
from (select ah.billgroupoption, ah.uidaccount,
ah.starttime, min(ah.starttime) over (partition by ah.uidaccount) maxstart
from CUSTOMERSYS.ACCOUNTHISTORY@ls_link ah )
where starttime = maxstart
)h ON bg.UIDACCOUNT = h.UIDACCOUNT
) q group by contractid
) ls on ls.contractid = c.propid
----------- BDM JOIN
left join main_rpt.SOME_sales_hierarchy_new csh_account on UPPER(c.account_bdm) = UPPER(csh_account.bdm_crm)
left join some_uni_bdm bui_account on csh_account.employee_id = bui_account.bdm_id and bui_account.month = to_char(p_start_date,'YYYYMM')
left join main_rpt.SOME_sales_hierarchy_new csh_contract on UPPER(c.contract_bdm) = UPPER(csh_contract.bdm_crm)
left join some_uni_bdm bui_contract on csh_contract.employee_id = bui_contract.bdm_id and bui_contract.month = to_char(p_start_date,'YYYYMM')
where trim(c.propid) is not null
and c.est_power_end_date >= p_start_date
and c.counter_sign_date <= p_end_date;
the subselects for AR balances, and the Bad Debt aggregations over different partitions… the Decode for deciding which weights to use in calculations… and picking distinct values from an external system that had inherent 1-3% data anomalies.