BRM SQL Queries

Account Details

SELECT aa.poid_id0, aa.account_no, aa.cust_seg_list, aa.lineage, aa.group_obj_id0, aa.currency, aa.locale
FROM account_t aa
WHERE aa.account_no = '1234567890';

Billinfo Details

SELECT aa.account_no, bi.poid_id0, bi.bill_info_id, bi.pay_type, bi.status, bi.actg_cycle_dom, bi.next_bill_t, 
bi.payinfo_obj_id0, bi.payinfo_obj_type
FROM account_t aa, billinfo_t bi
WHERE aa.account_no = '1234567890' AND 
aa.poid_id0 = bi.account_obj_id0 ;

Bill Details

SELECT aa.account_no, bb.poid_id0, bb.bill_no, bb.previous_total, bb.subords_total, bb.current_total, bb.total_due, bb.due, bb.bill_no, bb.start_t, bb.end_t, bb.name, bb.billinfo_obj_id0, bb.ar_billinfo_obj_id0
FROM account_t aa, billinfo_t bi, bill_t bb
WHERE aa.account_no = '1234567890' AND
aa.poid_id0 = bi.account_obj_id0 AND
aa.poid_id0 = bb.account_obj_id0 AND
bi.poid_id0 = bb.billinfo_obj_id0 AND
bb.end_t <> 0
ORDER BY bb.end_t DESC;

Balance Group Details

SELECT aa.account_no, bg.billinfo_obj_id0, bgs.rec_id2, valid_from, valid_to, bgs.grantor_obj_id0, bgs.grantor_obj_type
FROM account_t aa, bal_grp_t bg, bal_grp_sub_bals_t bgs
WHERE aa.account_no = '1234567890' and
aa.poid_id0 = bg.account_obj_id0 and
bg.poid_id0 = bgs.obj_id0 ;

Child Account Details

SELECT aa.poid_id0, aa.account_no, aa.cust_seg_list, aa.lineage, aa.group_obj_id0, aa.currency, aa.locale
FROM group_t g, group_billing_members_t gm, account_t aa
WHERE aa.poid_id0 = gm.object_id0 AND g.poid_id0 = gm.obj_id0 and
aa.poid_id0 = pp.account_obj_id0 AND
pp.poid_id0 = pi.obj_id0
START WITH g.account_obj_id0 IN (
SELECT poid_id0 FROM account_t WHERE account_no = '1234567890')
CONNECT BY PRIOR aa.poid_id0 = g.account_obj_id0;

Config GLID Details

SELECT
    cmg.event_type, cmg.reason_code_str_ver reason_ver,
    cmg.reason_code_str_id reason_id,
    ss.domain, ss.locale, ss.string, cga.attribute,
    cga.gl_ar_acct, cga.gl_offset_acct, cg.descr
FROM
    config_map_glid_t cmg, strings_t ss, config_glid_accts_t cga,
    config_glid_t cg
WHERE  
    cmg.reason_code_str_ver = 37 AND
    cmg.reason_code_str_id = 7 AND
    cmg.reason_code_str_ver = ss.version AND
    cmg.reason_code_str_id = ss.string_id AND
    cmg.glid = cga.rec_id2 (+)AND
    cga.rec_id2 = cg.rec_id;

DD Objects Details

SELECT dd.obj_id0, dd.name, df.field_name, df.field_type, df.parent_element_id, df.length, df.sm_item_name
FROM dd_objects_t dd, dd_objects_fields_t df
WHERE dd.name = '/event/billing/payment/dd' AND
dd.obj_id0 = df.obj_id0;

Event Details

SELECT ee.created_t, ee.poid_id0, ee.poid_type, ee.descr, 
ee.name,ee.program_name, ee.sys_descr
FROM  account_t aa, event_t ee
WHERE aa.account_no = '1234567890' AND
ee.account_obj_id0 = aa.poid_id0
ORDER BY ee.created_t DESC;

Event Details (Balance)

SELECT ee.created_t, ee.poid_id0,
replace(replace(replace(ee.poid_type,'/event/billing', '/e/b'), 'product', 'prd'), 'cycle/cycle_forward', 'cyc_fwd') event_type,
ee.descr,  ee.name,ee.program_name, ee.sys_descr, eb.amount, eb.resource_id, eb.quantity, eb.gl_id, eb.rate_tag, eb.offering_obj_type, eb.item_obj_id0
FROM  account_t aa, event_t ee, event_bal_impacts_t eb
WHERE aa.account_no = '1234567890' AND
ee.account_obj_id0 = aa.poid_id0 AND
ee.poid_id0 = eb.obj_id0
ORDER BY ee.created_t DESC;

Event Details (Cycle)

SELECT ee.created_t, ee.poid_id0, ee.sys_descr, eb.amount, eb.resource_id, eb.quantity, eb.gl_id, eb.rate_tag, eb.offering_obj_type, eb.item_obj_id0, efc.cycle_start_t, efc.cycle_end_t, efc.scale, efc.original_scale, efc.flag
FROM  account_t aa, event_t ee, event_bal_impacts_t eb, event_product_fee_cycle_t efc
WHERE aa.account_no = '1234567890' AND
ee.account_obj_id0 = aa.poid_id0 AND
ee.poid_id0 = eb.obj_id0 AND
ee.poid_id0 = efc.obj_id0 (+)
ORDER BY ee.created_t DESC;

Event Details Payment (CC & DD)

SELECT aa.account_no, ee.poid_id0, ee.descr, ee.item_obj_id0, ee.name, ee.program_name, ee.session_obj_id0, ee.sys_descr, ep.amount, ep.pay_type, ep.command, ep.status, ep.currency, ep.merchant, ep.trans_id, ep.ach, ep.channel_id,epd.auth_code, epd.auth_date, epd.result, epd.vendor_results, ee.invoice_data
FROM account_t aa, event_t ee, event_billing_payment_t ep, event_billing_payment_cc_t epd
WHERE aa.account_no = '1234567890' AND
aa.poid_id0 = ee.account_obj_id0 AND
ee.poid_id0 = ep.obj_id0 AND
ee.poid_id0 = epd.obj_id0;
SELECT aa.account_no, ee.poid_id0, ee.descr, ee.item_obj_id0, ee.name, ee.program_name, ee.session_obj_id0, ee.sys_descr, ep.amount, ep.pay_type, ep.command, ep.status, ep.currency, ep.merchant, ep.trans_id, ep.ach, ep.channel_id,epd.auth_code, epd.auth_date, epd.result, epd.vendor_results, ee.invoice_data 
FROM account_t aa, event_t ee, event_billing_payment_t ep, event_billing_payment_dd_t epd
WHERE aa.account_no = '1234567890' AND 
aa.poid_id0 = ee.account_obj_id0 AND 
ee.poid_id0 = ep.obj_id0 AND 
ee.poid_id0 = epd.obj_id0;

Event Details Payment Charge (CC & DD)

SELECT aa.account_no, ee.poid_id0, ee.item_obj_id0, ee.program_name, ee.sys_descr, ebc.amount, ebc.bill_obj_id0, ebc.billinfo_obj_id0, ebc.command, ebc.merchant, ebc.result, ebc.trans_id, ebcc.city, ebcc.country, ebcc.debit_num, ebcc.debit_exp, ebcc.legal_entity
FROM account_t aa, event_t ee, event_billing_charge_t ebc, event_billing_charge_cc_t ebcc
WHERE aa.account_no = '1234567890' AND 
aa.poid_id0 = ee.account_obj_id0 AND 
ee.poid_id0 = ebc.obj_id0 AND 
ebc.obj_id0 = ebcc.obj_id0 
ORDER BY ee.created_t DESC;
SELECT aa.account_no, ee.poid_id0, ee.item_obj_id0, ee.program_name, ee.sys_descr, ebc.amount, ebc.bill_obj_id0, ebc.billinfo_obj_id0, ebc.command, ebc.merchant, ebc.result, ebc.trans_id, ebcd.city, ebcd.country, ebcd.debit_num, ebcd.bank_no, ebcd.fias_id,ebcd.dd_activation_date, ebcd.* 
FROM account_t aa, event_t ee, event_billing_charge_t ebc, event_billing_charge_dd_t ebcd 
WHERE aa.account_no = '1234567890' AND 
aa.poid_id0 = ee.account_obj_id0 AND 
ee.poid_id0 = ebc.obj_id0 AND 
ebc.obj_id0 = ebcd.obj_id0 
ORDER BY ee.created_t DESC;

Data Ledger Report Detail

SELECT dd.poid_type, dd.mod_t, gl_segment, posted_t, last_posted_t
FROM data_t dd, data_ledger_report_t dlr
WHERE dd.poid_id0 = dlr.obj_id0;

GL Process Audit Export GL

SELECT pa.mod_t, pa.program_name, process_start, process_end,  inputrecords, successrecords, failrecords, pg.program_command, pg.status_str, pg.process_audit_obj_id0,  pgr.gl_segment, pgr.type, pgr.end_t, pgr.ledger_report_poid_list
FROM proc_aud_t pa, proc_aud_export_gl_t pg, proc_aud_export_gl_report_t pgr
WHERE pa.poid_id0 = pg.obj_id0 AND 
pg.obj_id0 = pgr.obj_id0
ORDER BY pa.created_t DESC;

Item Details

SELECT aa.account_no, aa.poid_id0 parent_acct_poid, ii.poid_id0 item_poid, ii.poid_type, ii.item_total, ii.due, ii.recvd, ii.adjusted, ii.transfered, ii.status, ii.effective_t
FROM account_t aa, item_t ii
WHERE aa.account_no = '1234567890' AND
aa.poid_id0 = ii.account_obj_id0 AND
ii.item_total <> 0
ORDER BY ii.effective_t DESC

Payinfo Details

SELECT aa.account_no, bi.bill_info_id, pic.debit_num, pic.debit_exp, pid.bank_no, pid.debit_num
FROM account_t aa, billinfo_t bi, payinfo_t pi, payinfo_cc_t pic, payinfo_dd_t pid
WHERE aa.account_no = '1234567890' AND
aa.poid_id0 = bi.account_obj_id0 AND
bi.payinfo_obj_id0 = pi.poid_id0 AND
pi.poid_id0 = pic.obj_id0 (+) AND
pi.poid_id0 = pid.obj_id0 (+);

Plan Details

SELECT pl.name plan_name, pl.descr plan_descr, pr.name prod_name,
pr.descr prod_descr, ds.name discount_name, ds.descr discount_descr
FROM plan_t pl, plan_services_deals_t ps, deal_products_t dp,
product_t pr, deal_discounts_t dd, discount_t ds
WHERE pl.name = 'Plan A' AND
pl.poid_id0 = ps.obj_id0 AND
ps.deal_obj_id0 = dp.obj_id0 AND
dp.product_obj_id0 = pr.poid_id0 AND
ps.deal_obj_id0 = dd.obj_id0 (+) AND
dd.discount_obj_id0 = ds.poid_id0;

Plan Details with Rate

SELECT pl.name plan_name, pl.descr plan_descr, pr.name prod_name, pr.descr prod_descr, rp.event_type, rp.currency,
decode (rr.prorate_first,703, 'no_charge', 702, 'charge_as_used',701, 'charge_full')purchase_prorate,
decode (rr.prorate_last,703, 'full_refund', 702, 'refund_as_used',701, 'no_refund')cancel_prorate,
rr.descr rate_descr, rb.element_id, rb.scaled_amount, rb.fix_amount, rb.gl_id, rb.impact_category, 
rb.start_details, rb.end_details
FROM plan_t pl, plan_services_deals_t ps, deal_products_t dp, product_t pr, rate_plan_t rp, rate_t rr, rate_bal_impacts_t rb
WHERE pl.name = 'Plan A' AND
pl.poid_id0 = ps.obj_id0 AND
ps.deal_obj_id0 = dp.obj_id0 AND
dp.product_obj_id0 = pr.poid_id0 AND
rp.product_obj_id0 = pr.poid_id0 AND
rr.rate_plan_obj_id0 = rp.poid_id0 AND
rr.poid_id0 = rb.obj_id0 ;

Plan Transition Details

SELECT pfrom.name, pto.name, pto.descr, tt.permitted, tt.type, tt.fee_flag
FROM transition_t tt, plan_t pfrom, plan_t pto
WHERE tt.from_obj_id0 = pfrom.poid_id0 AND
tt.to_obj_id0 = pto.poid_id0 AND
pfrom.name = 'Plan A';

Purchase Product Details

SELECT aa.account_no, pp.effective_t, pp.purchase_start_t, purchase_end_t, pp.cycle_start_t, cycle_end_t, pp.instantiated_t, pp.service_obj_type, pp.status,
pp.package_id, pp.flags, pp.descr prod_descr, pl.descr plan_descr
FROM account_t aa, purchased_product_t pp, plan_t pl
WHERE aa.account_no = '1234567890' AND
aa.poid_id0 = pp.account_obj_id0 AND
pp.plan_obj_id0 = pl.poid_id0 (+);

Purchased Discount Details

SELECT aa.account_no, pp.effective_t, pp.purchase_start_t, purchase_end_t, pp.cycle_start_t, cycle_end_t, pp.instantiated_t, pp.service_obj_type, pp.status, pp.package_id, pp.flags, pp.descr disc_descr, pl.descr plan_descr
FROM account_t aa, purchased_discount_t pp, plan_t pl
WHERE aa.account_no = '1234567890' AND
aa.poid_id0 = pp.account_obj_id0 AND
pp.plan_obj_id0 = pl.poid_id0 (+);

Schedule Details

SELECT aa.account_no, sc.poid_id0 schedule_poid, sc.sys_descr, 
sc.created_t, sc.when_t, sc.status, sc.opcode, sc.flags, sc.action_name, scf.*
FROM account_t aa, schedule_t sc, schedule_input_flist_buf scf
WHERE aa.account_no = '1234567890' AND
aa.poid_id0 = sc.account_obj_id0 AND
sc.poid_id0 = scf.obj_id0;

Service Details

SELECT aa.account_no, aa.poid_id0 acct_poid, ss.poid_id0 serv_poid,
ss.poid_type, ss.status, ss.status_flags, service_id,
ss.last_status_t, ss.last_status_t, ss.laststat_cmnt
FROM account_t aa, service_t ss
WHERE aa.account_no = '1234567890' AND
aa.poid_id0 = ss.account_obj_id0 ; 

Get nth Occurrence Data

SELECT msg.account_no,msg.invoice_no, msg.invoice_date, msg.email_addr, q.enq_time, 
replace(regexp_substr(replace(et.name,' ',''),'[[:alnum:]]{0,};',1,8),';') tax_reg_id
FROM BRMAQ01.AQ_SYNC_backup q, brmaq01.aq_msg_data msg, bill_t bb, item_t ii, event_t ee, event_tax_jurisdictions_t et
WHERE msg.msgid = q.msgid and
q.user_data.large_flist_buf like '%<TaxRegion>SA</TaxRegion>%'
and q.enq_time between to_date('16-SEP-19') and to_date('18-SEP-19')
and bb.bill_no =  msg.invoice_no and 
bb.poid_id0 = ii.ar_bill_obj_id0 and 
ii.poid_id0 = ee.item_obj_id0 and 
ee.poid_id0 = et.obj_id0;