with
resa_tran as
(
select /*+ MATERIALIZE */ sth.store store,s.store_name Store_Name,trunc(tran_datetime) tran_date ,tran_seq_no, ssd.business_date,
sth.day,
value Partial_Val
from sa_tran_head sth,sa_store_day ssd,store s
where s.store = sth.store and s.store between 999 and 9999 and s.store =1318 --and tran_seq_no in (1216648196)
and ssd.store_day_seq_no = sth.store_day_seq_no and
--ssd.business_date >= '01-apr-2012' and
trunc(sth.tran_datetime) = '06-may-2012' and
sth.status = 'P' and tran_type in ('SALE','RETURN')
--ssd.business_date <> trunc(sth.tran_datetime) -- and sth.tran_seq_no = 1199240005
)
select a.Store "Store", a.store_name "Store Name",a.tran_date "Transaction Date",a.tran_seq_no,a.item,a.item_seq_no,
sum(total_retail) "Total Retail",sum(nvl(disc,0)) "Discount", sum(nvl(tax,0)) "Tax Amount"
from
(
select rt.store, rt.store_name, rt.business_date,rt.day, rt.tran_date, rt.tran_seq_no,sti.item,sti.item_seq_no,
sum(sti.qty*sti.unit_retail) total_retail
from resa_tran rt, sa_tran_item sti, item_master im, deps d, groups gp
where
rt.tran_seq_no = sti.TRAN_SEQ_NO and rt.store = sti.store and rt.day = sti.day
and im.dept = d.dept and sti.item = im.item and d.GROUP_NO = gp.group_no
group by rt.store,rt.store_name,rt.tran_date, rt.business_date,rt.day, rt.tran_seq_no, sti.item_seq_no,sti.item
) a,
(
select rt.store, business_date,rt.day, rt.tran_seq_no, std.ITEM_SEQ_NO,
sum(nvl(std.QTY,0)*nvl(std.UNIT_DISCOUNT_AMT,0)) disc
from resa_tran rt, sa_tran_disc std
where rt.tran_seq_no = std.TRAN_SEQ_NO and rt.store = std.store and rt.day = std.day
group by rt.store, business_date ,rt.day, std.item_seq_no, rt.tran_seq_no
) b,
(
select rt.store, business_date,rt.day, rt.tran_seq_no, stg.ITEM_SEQ_NO,
sum(nvl(stg.QTY,0)*nvl(stg.UNIT_IGTAX_AMT,0)) Tax
from resa_tran rt, sa_tran_igtax stg
where rt.tran_seq_no = stg.TRAN_SEQ_NO and rt.store = stg.store and rt.day = stg.day
group by rt.store, business_date ,rt.day, stg.item_seq_no, rt.tran_seq_no
) c
where a.tran_seq_no = b.tran_seq_no(+) and a.item_seq_no = b.item_seq_no(+) and a.store = b.store(+) and a.day = b.day(+) and
a.tran_seq_no = c.tran_seq_no(+) and a.item_seq_no = c.item_seq_no(+) and a.store = c.store(+) and a.day = c.day(+)
group by a.Store, a.store_name,a.tran_date,a.tran_seq_no,a.item,a.item_seq_no
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment