Oracle BRM Sample Queries

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;

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 ;

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;

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' ;

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 ;

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;