Some BRM SQLs

Here I'm sharing few SQLs which are very useful for specific BRM scenarios during debugging and testing. Please contribute if you have some SQLs apart from the listed ones :

1). Searching the resources associated with an account with their balances:

SELECT BG.OBJ_ID0,BG.REC_ID,BG.REC_ID2,CB.NAME,CB.SYMBOL, DATE '1970-01-01'+BG.VALID_FROM/86400,DATE '1970-01-01'+BG.VALID_TO/86400,BG.CURRENT_BAL FROM BAL_GRP_SUB_BALS_T BG,CONFIG_BEID_BALANCES_T CB WHERE BG.OBJ_ID0=(SELECT POID_ID0 FROM BAL_GRP_T WHERE ACCOUNT_OBJ_ID0=30651) AND BG.REC_ID2=CB.REC_ID

Change the ACCOUNT_OBJ_ID0 and use this query.


2). Getting discount details with discount model

select d.name,dum.EVENT_TYPE, -- Discount Name
idm.CODE, -- discount model
idt.CODE "Trigger", idcndn.COND_EXPRESSION ||' '||idcndn.COND_OPERATOR||' '||idcndn.CONDITION_VALUE "Trigger value", -- Trigger details
idmstr.CODE "DiscountMaster", iddtl.ZONEMODEL, iddtl.IMPACT_CATEGORY, iddtl.SERVICECODE, iddtl.SERVICECLASS, -- Master details
iddtl.TIMEMODEL, iddtl.TIMEZONE, iddtl.PRICEMODEL, iddtl.RESSOURCE, iddtl.RUM, iddtl.RECORDTYPE, -- Master details cont
iddtl.USAGETYPE,iddtl.USAGECLASS, iddtl.RATE_PLAN, iddtl.RATE_TAG, iddtl.GLID, -- Master details cont
idrl.CODE "DISC RULE", idrl.DRUM_EXPRESSION, idrl.RULE_TYPE, idrl.DRUM_TYPE, -- Discount rule
idstp.CODE, idstp.RANK, idstp.THRESHOLD_FROM, idstp.THRESHOLD_TO, -- Discount step
idblimpct.RESOURCE_ID, idblimpct.DISCOUNT_OWNER_FLAG, idblimpct.DISCOUNT_TYPE, idblimpct.BASE_EXPRESSION, -- discount balance impact
idblimpct.GRANT_BEAT, idblimpct.GRANT_PRORATEDBEAT, idblimpct.GRANT_UNITS, idblimpct.EVENT_BALANCE_ID -- discount balance impact
from discount_t d, discount_usage_map_t dum, IFW_DISCOUNTMODEL idm,
IFW_DSCMDL_CNF idmc, IFW_DSCTRIGGER idt, IFW_DISCOUNTRULE idrl, IFW_DSCCONDITION idcndn, IFW_DISCOUNTMASTER idmstr, IFW_DISCOUNTDETAIL iddtl,
IFW_DISCOUNTSTEP idstp, IFW_DSCBALIMPACT idblimpct--, IFW_DSCMDL_CNF idscnf
where d.POID_ID0=dum.OBJ_ID0 and dum.DISCOUNT_MODEL=idm.CODE and idmc.DISCOUNTMODEL=idm.DISCOUNTMODEL
and idmc.DISCOUNTTRIGGER=idt.DISCOUNTTRIGGER and idmc.DISCOUNTRULE=idrl.DISCOUNTRULE
and idcndn.DISCOUNTTRIGGER=idt.DISCOUNTTRIGGER and idmstr.DISCOUNTMASTER=idrl.DISCOUNTMASTER
and idmstr.DISCOUNTMASTER=iddtl.DISCOUNTMASTER and idstp.DISCOUNTRULE=idrl.DISCOUNTRULE
and idblimpct.DISCOUNTSTEP=idstp.DISCOUNTSTEP ;






3). Discount details with discount model selector

select d.name,dum.EVENT_TYPE, -- Discount Name
idm.CODE, -- discount model
idt.CODE "Trigger", idcndn.COND_EXPRESSION ||' '||idcndn.COND_OPERATOR||' '||idcndn.CONDITION_VALUE "Trigger value", -- Trigger details
idmstr.CODE "DiscountMaster", iddtl.ZONEMODEL, iddtl.IMPACT_CATEGORY, iddtl.SERVICECODE, iddtl.SERVICECLASS, -- Master details
iddtl.TIMEMODEL, iddtl.TIMEZONE, iddtl.PRICEMODEL, iddtl.RESSOURCE, iddtl.RUM, iddtl.RECORDTYPE, -- Master details cont
iddtl.USAGETYPE,iddtl.USAGECLASS, iddtl.RATE_PLAN, iddtl.RATE_TAG, iddtl.GLID, -- Master details cont
idrl.CODE "DISC RULE", idrl.DRUM_EXPRESSION, idrl.RULE_TYPE, idrl.DRUM_TYPE, -- Discount rule
idstp.CODE, idstp.RANK, idstp.THRESHOLD_FROM, idstp.THRESHOLD_TO, -- Discount step
idblimpct.RESOURCE_ID, idblimpct.DISCOUNT_OWNER_FLAG, idblimpct.DISCOUNT_TYPE, idblimpct.BASE_EXPRESSION, -- discount balance impact
idblimpct.GRANT_BEAT, idblimpct.GRANT_PRORATEDBEAT, idblimpct.GRANT_UNITS, idblimpct.EVENT_BALANCE_ID -- discount balance impact
from discount_t d, discount_usage_map_t dum, IFW_MODEL_SELECTOR ims, IFW_SELECTOR_RULESET isrlst, IFW_DISCOUNTMODEL idm,
IFW_DSCMDL_CNF idmc, IFW_DSCTRIGGER idt, IFW_DISCOUNTRULE idrl, IFW_DSCCONDITION idcndn, IFW_DISCOUNTMASTER idmstr, IFW_DISCOUNTDETAIL iddtl,
IFW_DISCOUNTSTEP idstp, IFW_DSCBALIMPACT idblimpct,
IFW_SELECTOR_RULE_LNK isrl, IFW_SELECTOR_RULE isl, IFW_SELECTOR_DETAIL isd
where d.POID_ID0=dum.OBJ_ID0 and dum.MODEL_SELECTOR=ims.NAME and idmc.DISCOUNTMODEL=idm.DISCOUNTMODEL
and idmc.DISCOUNTTRIGGER=idt.DISCOUNTTRIGGER and idmc.DISCOUNTRULE=idrl.DISCOUNTRULE
and idcndn.DISCOUNTTRIGGER=idt.DISCOUNTTRIGGER and idmstr.DISCOUNTMASTER=idrl.DISCOUNTMASTER
and idmstr.DISCOUNTMASTER=iddtl.DISCOUNTMASTER and idstp.DISCOUNTRULE=idrl.DISCOUNTRULE
and idblimpct.DISCOUNTSTEP=idstp.DISCOUNTSTEP
and dum.MODEL_SELECTOR=ims.NAME
and isrlst.MODEL_SELECTOR=ims.MODEL_SELECTOR
and isrlst.MODEL=idm.DISCOUNTMODEL
and isrlst.SELECTOR_RULE=isrl.SELECTOR_RULE
and isrl.SELECTOR_RULE=isl.SELECTOR_RULE and isrl.SELECTOR_DETAIL=isd.SELECTOR_DETAIL

4). SQL to find pipeline rate plan details with product poid
select p.name,pum.EVENT_TYPE, isrl.*, ipm.*, ims.*
from product_t p, PRODUCT_USAGE_MAP_T pum, RATE_PLAN_T rp, IFW_RATEPLAN irp,
IFW_RATEPLAN_VER irpv, IFW_RATEPLAN_CNF irpc, IFW_MODEL_SELECTOR ims, IFW_SELECTOR_RULESET isr,
IFW_SELECTOR_RULE isrl, IFW_PRICEMODEL ipm
where p.POID_ID0=pum.OBJ_ID0 and pum.RATE_PLAN_CODE=rp.CODE and rp.CODE=irp.CODE
and irp.RATEPLAN=irpv.RATEPLAN and irpv.RATEPLAN=irpc.RATEPLAN and irpc.MODEL_SELECTOR=ims.MODEL_SELECTOR
and isr.MODEL_SELECTOR=ims.MODEL_SELECTOR
and isrl.SELECTOR_RULE=isr.SELECTOR_RULE
and isr.MODEL=ipm.PRICEMODEL
and p.poid = <<PRODUCT_POID>>;


5). SQL to find discount details

select dsc.poid_id0, dsc.NAME,
decode(idb.discount_type,'P',idb.GRANT_UNITS*100||'%', idb.GRANT_UNITS||' Fixed') discount
, idd.*
from
DISCOUNT_T dsc, DISCOUNT_USAGE_MAP_T dum
,IFW_DISCOUNTMODEL idm
,IFW_DSCMDL_CNF idmc
,IFW_DISCOUNTRULE idr
,IFW_DISCOUNTSTEP ids
,IFW_DSCBALIMPACT idb
,IFW_DISCOUNTDETAIL idd
where dsc.POID_ID0=dum.OBJ_ID0
and idm.code=dum.DISCOUNT_MODEL
and idmc.discountmodel = idm.discountmodel
and idr.DISCOUNTRULE = idmc.DISCOUNTRULE
and ids.discountrule = idr.discountrule
and idb.discountstep = ids.discountstep
and idr.DISCOUNTMASTER = idd.DISCOUNTMASTER
--and dsc.name like '%<<DISCOUNT_PATTERN%'
and dum.EVENT_TYPE='<<EVENT_TYPE>>'
;