select sa.from_date,sa.to_date,sa.type_code,
sa.subtype_code,sa.amount,sa.discount,sa.subscr_no,sa.discount_id,sb.description_text
from
(
select t1.from_date,t1.to_date,decode(t2.type_code,1,'payment',2,'rc',3,'nrc',4,'adjustment',
5,'Summary',6,'unit credit',7,'usage charge',8,'prepayment',9,'(bonus point') as type_code,
t2.subtype_code,t2.amount,t2.discount,t2.subscr_no,t2.discount_id
from bill_invoice@cu08 t1,bill_invoice_detail@cu08 t2
where
t1.account_no='35514293' and t1.statement_Date=to_date('2008-08-01','yyyy-MM-dd')
and t1.bill_ref_no=t2.bill_ref_no and t1.bill_ref_resets=t2.bill_ref_resets and t2.discount_id<>-2 and t2.discount_id is not null
) sa inner join
(
select t7.discount_id,t7.description_code,t8.description_text
from discount_definitions@cu08 t7,descriptions@cu08 t8
where t7.description_code=t8.description_code
)as sb
on sa.discount_id=sb.discount_id
sa.subtype_code,sa.amount,sa.discount,sa.subscr_no,sa.discount_id,sb.description_text
from
(
select t1.from_date,t1.to_date,decode(t2.type_code,1,'payment',2,'rc',3,'nrc',4,'adjustment',
5,'Summary',6,'unit credit',7,'usage charge',8,'prepayment',9,'(bonus point') as type_code,
t2.subtype_code,t2.amount,t2.discount,t2.subscr_no,t2.discount_id
from bill_invoice@cu08 t1,bill_invoice_detail@cu08 t2
where
t1.account_no='35514293' and t1.statement_Date=to_date('2008-08-01','yyyy-MM-dd')
and t1.bill_ref_no=t2.bill_ref_no and t1.bill_ref_resets=t2.bill_ref_resets and t2.discount_id<>-2 and t2.discount_id is not null
) sa inner join
(
select t7.discount_id,t7.description_code,t8.description_text
from discount_definitions@cu08 t7,descriptions@cu08 t8
where t7.description_code=t8.description_code
)as sb
on sa.discount_id=sb.discount_id
, sa.to_date
, sa.type_code
, sa.subtype_code
, sa.amount
, sa.discount
, sa.subscr_no
, sa.discount_id
, sb.description_text
from ( select t1.from_date
, t1.to_date
, decode( t2.type_code
, 1, 'payment'
, 2, 'rc'
, 3, 'nrc'
, 4, 'adjustment'
, 5, 'Summary'
, 6, 'unit credit'
, 7, 'usage charge'
, 8, 'prepayment'
, 9, '(bonus point'
) as type_code
, t2.subtype_code
, t2.amount
, t2.discount
, t2.subscr_no
, t2.discount_id
from bill_invoice@cu08 t1
, bill_invoice_detail@cu08 t2
where t1.bill_ref_no = t2.bill_ref_no
and t1.bill_ref_resets = t2.bill_ref_resets
and t2.discount_id <> -2
and t2.discount_id is not null
and t1.account_no = '35514293'
and t1.statement_Date = to_date('2008-08-01','yyyy-MM-dd')
) sa
inner join
( select t7.discount_id
, t7.description_code
, t8.description_text
from discount_definitions@cu08 t7
, descriptions@cu08 t8
where t7.description_code = t8.description_code
) sb
on sa.discount_id = sb.discount_idtry