Below queries are useful in all BRM projects :

 Fetch Purchased Products for one account:
select pp.account_obj_id0,pp.poid_id0 pp_poid,product_obj_id0,p.name,p.descr,pl.name plan, pp.service_obj_type, pp.service_obj_id0,s.login,s.subscription_obj_id0, pp.status,pp.plan_obj_id0,pp.package_id, pin_unix_to_date(pp.cycle_start_t,0) cycle_start, pin_unix_to_date(pp.cycle_end_t,0) cycle_end,quantity ,pin_unix_to_date(ppc.charged_from_t,0) charged_from,pin_unix_to_date(ppc.charged_to_t,0) charged_to FROM
purchased_product_t pp, product_t p,service_t s,account_t a,plan_t pl,purchased_product_cycle_fees_t ppc
WHERE a.account_no='<accountno>' and pp.product_obj_id0=p.poid_id0 and s.poid_id0=pp.service_obj_id0 and pp.account_obj_id0=a.poid_id0 and pp.plan_obj_id0=pl.poid_id0 and ppc.obj_id0=pp.poid_id0;

 Event Balances for an account:
select e.poid_id0,e.poid_type,e.session_obj_id0,pin_unix_to_date(e.created_t,0) created,pin_unix_to_date(e.earned_start_t,0) estart, pin_unix_to_date(e.earned_end_t,0) eend,eb.rec_id, eb.account_obj_id0 apoid, amount,gl_id, percent,eb.item_obj_type, eb.item_obj_id0,quantity,eb.offering_obj_id0,eb.account_obj_id0,eb.product_obj_id0, eb.product_obj_type,resource_id res,impact_type, rate_tag, i.item_total, i.due,i.status FROM
event_t e, event_bal_impacts_t eb, account_t a, item_t i
WHERE eb.account_obj_id0=a.poid_id0 and a.account_no='<accountno>' and e.poid_id0=eb.obj_id0 and eb.amount !=0 and eb.resource_id<1000 and impact_type != 4 and i.poid_id0=e.item_obj_id0 order by e.created_t ;

 List of Events for an account:
select e.* FROM event_t e, account_t a WHERE e.account_obj_id0=a.poid_id0 and a.account_no='<accountno>' order by e.created_t ;

 List of Bills for an account:
select b.poid_id0,bill_no,b.account_obj_id0,b.ar_billinfo_obj_id0,pin_unix_to_date(start_t,0) start_t,pin_unix_to_date(end_t,0) end_t,pin_unix_to_date(due_t,0) due_t, current_total, subords_total,total_due, due,recvd FROM
bill_t b , account_t a
WHERE b.account_obj_id0=a.poid_id0 and account_no='<accountno>' order by due_t desc;

 Fetch Billinfo Details:
select bi.poid_id0, pin_unix_to_date(last_bill_t,0) last_bill_t,pin_unix_to_date(next_bill_t,0) next_bill_t,scenario_obj_id0, billing_status,billing_state, payinfo_obj_type FROM
billinfo_t bi,account_t a
WHERE bi.account_obj_id0=a.poid_id0 and a.account_no='<accountno>' ;


Function for converting unix date to readable date:

create or replace FUNCTION pin_unix_to_date(
      utc_time   IN NUMBER,
      utc_offset IN NUMBER )
    RETURN DATE
IS
    l_date DATE;
BEGIN
    l_date := TO_DATE('19700101','YYYYMMDD') + ( utc_time / 60 / 60 / 24 ) + ( utc_offset / 24 );
    RETURN (l_date);
END pin_unix_to_date;