Most Pl/Sql code is fairly boring, selecting lots of columns from lots of joins and maybe doing calculations and decodes on the fly, or groupings and other aggregations, and sub queries. But one project I had was pretty neat. Users had two lists of Products, and although the names and attributes were not usually the same, they were the same, just called different things by different companies.
When we started using a new Product, we had to find the two matching ones and map them together. This involved a process that ultimately led to what we called a data fix. No code was changed, but data that the business process relied on had to be created or changed in the production database. To do this required an IT resource to team up with a business resource and first make the changes in a test environment, and then test, and make adjustments, and test again, and then do the same exact cumulative change they did in the test environment, in the production environment.
This was time intensive, and highly manual. I wanted to make this quick, and automatic, without any room for manual errors. So one of the features of the new tool I built was for the users to make these changes themselves, in a test environment, with full auditing and editing capability, and be able to see for themselves the effects their changes had, and when they were ready, push a button to have the exact same results applied to their production environment. This worked wonderfully, and worked with full auditing and history tracking in case any recovery was necessary. Besides some finely tuned controls in the new UI, all it required was an Oracle database link to the test environment.
procedure PushToProd(p_audit_user varchar2,
p_id my_product.id%type,
p_notevarchar2) is
begin
insert into my_product_history
(audit_user, audit_date, type,
id, name, pf_code, pf_type,
cc_code, exch_id, gs_name,
pf_commodity, lot_size, uom_cd,
some_lot_sz, term, opt_style,
product_type, calendar, location,
description_id, trader_group, settle_date_rule,
eot_date_rule, uses_dst, exchange)
(select p_audit_user, sysdate, 'PTP',
nvl(p.id, pp.id), nvl(p.name, pp.name), nvl(p.pf_code, pp.pf_code), nvl(p.pf_type, pp.pf_type),
nvl(p.cc_code, pp.cc_code), nvl(p.exch_id, pp.exch_id), nvl(p.gs_name, pp.gs_name),
nvl(p.pf_commodity, pp.pf_commodity), nvl(p.lot_size, pp.lot_size), nvl(p.uom_cd, p.uom_cd),
nvl(p.some_lot_sz, pp.some_lot_sz), nvl(p.term, pp.term), nvl(p.opt_style, pp.opt_style),
nvl(p.product_type, pp.product_type), nvl(p.calendar, pp.calendar), nvl(p.location, pp.location),
nvl(p.description_id, pp.description_id), nvl(p.trader_group, pp.trader_group), nvl(p.settle_date_rule, pp.settle_date_rule),
nvl(p.eot_date_rule, pp.eot_date_rule), nvl(p.uses_dst, pp.uses_dst), nvl(p.exchange, pp.exchange)
from my_product p
full outer join my_product@SOMEW_LINK pp on p.id =
pp.id -- handles new
where p.id = p_id
or pp.id = p_id);
pkg_SOME_log.WRITEMESSAGE('pkg_ProductMapper.PushToProd',
'DEBUG',
'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
' new record(s) inserted by User ' ||
p_audit_user || ' in my_product_history');
delete from my_product where id = p_id;
delete from some_fee where product_id = p_id;
insert into my_product
(select * from my_product@SOMEW_LINK where id = p_id);
pkg_SOME_log.WRITEMESSAGE('pkg_ProductMapper.PushToProd',
'DEBUG',
'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
' new record(s) inserted by User ' ||
p_audit_user || ' in my_product');
insert into some_fee
(select *
from SOME.some_fee@SOMEW_LINK
where product_id = p_id);
pkg_SOME_log.WRITEMESSAGE('pkg_ProductMapper.PushToProd',
'DEBUG',
'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
' new record(s) inserted by User ' ||
p_audit_user || ' in some_fee');
exception
when others then
rollback;
raise;
end;
procedure PushMappingToProd(p_audit_user varchar2,
p_v_id my_values.id%type,
p_notevarchar2) is
begin
insert into my_values_history
(audit_user, audit_date, type,
id, key, value,
val_type, schema, rule, exch_id)
select p_audit_user, sysdate, 'PTP',
nvl(v.id, vv.id), nvl(v.key, vv.key), nvl(v.value, vv.value),
nvl(v.val_type, vv.val_type), nvl(v.schema, vv.schema),
nvl(v.rule, vv.rule), nvl(v.exch_id, vv.exch_id)
from my_values v
full outer join my_values@SOMEW_LINK vv on v.id =
vv.id
where v.id = p_v_id
or vv.id = p_v_id;
pkg_SOME_log.WRITEMESSAGE('pkg_ProductMapper.PushMappingToProd',
'DEBUG',
'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
' record(s) inserted by User ' ||
p_audit_user || ' in my_values_history');
delete from my_values where id = p_v_id;
insert into my_values
(select * from my_values@SOMEW_LINK where id = p_v_id);
pkg_SOME_log.WRITEMESSAGE('pkg_ProductMapper.PushMappingToProd',
'DEBUG',
'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
' new record(s) inserted by User ' ||
p_audit_user || ' in my_value');
exception
when others then
rollback;
raise;
end;
I also put some auditing features in the new UI. The system relied on the premise that the two databases to start with exact primary key ids and to keep them in sync between the two systems. I built several auditing screens, showing even the detail for each user’s changes in each environment, so that more thatn one user could see what other users had done. Here’s one of the auditing tools that showed the user the differences between some of the aggregations of the two systems. This is the boring sql I was talking about before. This also works with that oracle database link.
procedure ViewLotDiff(p_valuation_date in me_trade.valuation_date%type,
cur_out out cur) is
begin
-- Added condition v.expiration_date >= p_valuation_date
open cur_out for
select gg.anothercompany_name,
max(pf_code),
case
when sum(some_w_lots) <>sum(gs_lots) then
case when sum(some_p_lots) <>sum(gs_lots) then 'BOTH'
else 'some_W' end
else
'some_P'
end as issue,
sum(some_w_lots) total_some_w_lots,
sum(some_p_lots) total_some_p_lots,
sum(gs_lots) total_gs_lots
from (select some_w_lots,
some_p_lots,
gs_lots,
src,
pf_code,
anothercompany_name
from (select round(sum(v.lots),1) some_w_lots,
0 some_p_lots,
0 gs_lots,
'some_W' src,
v.PF_CODE,
v.anothercompany_NAME
from some_.MAP_A_VW v
where 1 = 1
and v.VALUATION_DATE = p_valuation_date
and (v.term <> 'DAY' or
v.contract_month >= p_valuation_date)
and ( ( v.pf_commodity in ('WEA','WTI') and nvl(v.expiration_date,to_date('12/31/9999','mm/dd/yyyy')) >= p_valuation_date )
or
( v.pf_commodity not in ('WEA','WTI') and nvl(v.settle_date,to_date('12/31/9999','mm/dd/yyyy')) >= trunc(to_date(p_valuation_date),'MM')))
group by v.anothercompany_NAME, v.PF_CODE )
union (select 0 some_w_lots,
round(sum(d.lots),1) some_p_lots,
0 gs_lots,
'some_P' src,
d.pf_code,
p.GS_NAME anothercompany_NAME
from some_.me_trade_detail@some_P_LINK d
join some_.me_trade_header@some_P_LINK h on d.eti = h.eti
join some_.me_product@some_P_LINK p on d.pf_code = p.pf_code and d.pf_type = p.pf_type and h.exchange = p.exchange
where 1 = 1
and d.VALUATION_DATE = p_valuation_date
and nvl(d.some__status,'nvl') <> 'ORI'
and (h.term <> 'DAY' or
d.contract_month >= p_valuation_date)
and ( ( p.pf_commodity in ('WEA','WTI') and nvl(d.expiration_date,to_date('12/31/9999','mm/dd/yyyy')) >= p_valuation_date )
or
( p.pf_commodity not in ('WEA','WTI') and nvl(d.settle_date,to_date('12/31/9999','mm/dd/yyyy')) >= trunc(to_date(p_valuation_date),'MM')))
group by p.GS_NAME, d.pf_code )
union (select 0 some_w_lots,
0 some_p_lots,
sum(g.net_qty) gs_lots,
'GS' src,
'' pf_code,
g.prod_description anothercompany_name
from gsd_open_positions g
where 1 = 1
and g.stmt_date = p_valuation_date
and g.expiry_date >= p_valuation_date
group by g.prod_description )
) gg
group by anothercompany_name
having ( (sum(some_w_lots) - sum(some_p_lots) <> 0) or (sum(gs_lots) - sum(some_p_lots) <> 0) or (sum(gs_lots) - sum(some_w_lots) <> 0) )
order by anothercompany_NAME;
end;