select * from
(
select sid, invoice_amount, tender_amount, disc_amt, lty_redeem_amt, (invoice_amount-tender_amount-disc_amt-lty_redeem_amt) gap from
(
select sid, NVL(invoice_amount,0) invoice_amount, NVL(tender_amount,0) tender_amount, NVL(disc_amt,0) disc_amt, NVL(lty_redeem_amt,0) lty_redeem_amt from
(((
select sid, sum(ORIG_PRICE*QTY) invoice_amount from
(select d.sid,st.udf1_string store_id,
ii.upc,
sum(decode(di.item_type, 1, di.qty, 2, -di.qty)) qty,
di.orig_price,
REPLACE(NVL(c.udf2_string, 'KHACH_VANG_LAI'),'KHÁCH LẺ','KHACH_LE') cust_type,
di.tax_perc,
nvl(di.orig_tax_amt,0) orig_tax_amt
from rps.document d
join rps.document_item di on d.sid = di.doc_sid
join rps.store st on st.sid = d.store_sid
join rps.invn_sbs_item ii on di.invn_sbs_item_sid = ii.sid
left join rps.customer c on d.bt_cuid = c.sid -- Left join to include records with no customer
where di.item_type = 1 and di.kit_flag in (0,1,4,5)--0-Simple,1-Kit,2-BrokenKit,3-Package,4-KitItem,5-PackageItem,6-GiftCardStoredValue,7-GiftCardPrePaid,8-GiftCertificate,9-Fee,10-GiftCardCentral
and d.doc_no > 0
and d.is_held = 0
--and ii.description2 not like '%GIFTCARD%'-- 87 gift, 83 top up --LBCC-2024-09-30 include gift card
and ii.description2 not like '%DATCOC%' and ii.description2 not like '%TOPUP%'
and d.order_qty = 0 and d.return_qty = 0 and d.sold_qty >0 ----LBCC-2024-09-30 include only bill sale
and d.invc_post_date >= TO_DATE('26/04/2025 23:30:00', 'dd/mm/yyyy HH24:mi:ss')
and d.invc_post_date <= TO_DATE('27/04/2025 12:00:00', 'dd/mm/yyyy HH24:mi:ss')
and d.sbs_no = 1
group by d.sid,st.udf1_string, ii.upc, di.orig_price, c.udf2_string, di.tax_perc, di.orig_tax_amt
having st.udf1_string = 1 )
group by sid) invoice left join
(--O2C-03-2 tender of bill sale--
select sid sid_tender, amount tender_amount from
(select d.sid, st.udf1_string store_id, sum(t.amount) amount, CAST(t.tender_type AS NUMBER(9,0)) tender_type, REPLACE(NVL(c.udf2_string, 'KHACH_VANG_LAI'),'KHÁCH LẺ','KHACH_LE') cust_type
from rps.document d
--rps.document_item di,
join rps.store st on st.sid = d.store_sid
left join rps.customer c on d.bt_cuid = c.sid
--join (select doc_sid, (case when tender_type=10 then 0 else tender_type end) tender_type, amount from RPS.tender) t on t.doc_sid = d.sid
join (select doc_sid, tender_type, amount from RPS.tender) t on t.doc_sid = d.sid
where
d.doc_no >0 and d.is_held = 0
--and (d.sold_qty + d.return_qty + d.order_qty) > 0 --LBCC-2024-09-27 co khi co bill ko co item nao
and d.sid not in (select doc_sid from rps.tender where tender_type = 7)--Truc add to filter deposit type
and t.tender_type in (0,2,9,11)--0-cash, 2-credit card, 11-debit card (bank/QR),9-gift cert (digital wallet - NOT USED),
--and d.order_qty = 0--LBCC-2024-09-27 co khi co bill vua order vua sale, bo dong nay moi khop zout
and ((d.order_qty = 0 and d.return_qty = 0 and d.sold_qty >0 )OR----LBCC-2024-09-30 include only bill sale
((d.sold_qty + d.return_qty + d.order_qty) = 0 --no items in bill
AND not exists (select * from rps.tender td where td.doc_sid = d.sid and (td.tender_type = 5 or td.tender_type = 15) and td.given >0) --bill lam can tender, khong phai bill thu COD)
))
and d.invc_post_date >= TO_DATE('26/04/2025 23:30:00', 'dd/mm/yyyy HH24:mi:ss')
and d.invc_post_date <= TO_DATE('27/04/2025 12:00:00', 'dd/mm/yyyy HH24:mi:ss')
and d.sbs_no = 1
group by d.sid, st.udf1_string, t.tender_type, c.udf2_string
having st.udf1_string = 1 )) tender
on invoice.sid = tender.sid_tender)
left join
(--O2C-03-promotion of bill sale--
select sid sid_disc, disc_amt from
(
select sid, udf1_string store_id,
18 as upc,
1 qty,
sum(disc_amt) disc_amt,
disc_reason,
cust_type
from (
select d.sid, st.udf1_string,
sum(decode(lock_type,0,new_disc_amt,2,-new_disc_amt)*di.qty) disc_amt, --LBCC-2024-09-27
did.disc_reason,
REPLACE(NVL(c.udf2_string, 'KHACH_VANG_LAI'),'KHÁCH LẺ','KHACH_LE') cust_type
from rps.document d
join rps.document_item di on d.sid = di.doc_sid
join rps.store st on st.sid = d.store_sid
left join rps.customer c on d.bt_cuid = c.sid
join RPS.document_item_disc did on did.doc_item_sid = di.sid
where
di.qty > 0 and did.lock_type = 0 --giam gia hang ban
and d.doc_no >0 and d.is_held = 0
--and di.item_type = 1 --LongNV: get type=sale, --LBCC-2024-09-27 need to include sale + return to match with zout
and di.description2 not like '%DATCOC%' and di.description2 not like '%TOPUP%'
and d.order_qty = 0
and d.return_qty = 0 and d.sold_qty >0 ----LBCC-2024-09-30 include only bill sale
and d.invc_post_date >= TO_DATE('26/04/2025 23:30:00', 'dd/mm/yyyy HH24:mi:ss')
and d.invc_post_date <= TO_DATE('27/04/2025 12:00:00', 'dd/mm/yyyy HH24:mi:ss')
and d.sbs_no = 1
group by d.sid,st.udf1_string, did.disc_reason, c.udf2_string
UNION ALL
select d.sid,st.udf1_string, sum(dd.new_disc_amt) disc_amt,dd.disc_reason, REPLACE(NVL(c.udf2_string, 'KHACH_VANG_LAI'),'KHÁCH LẺ','KHACH_LE') cust_type
from rps.document d
--rps.document_item di,
join rps.store st on st.sid = d.store_sid
left join rps.customer c on d.bt_cuid = c.sid
join RPS.document_disc dd on dd.doc_sid = d.sid
where
d.doc_no >0 and d.is_held = 0
and d.order_qty = 0
and d.return_qty = 0 and d.sold_qty >0 ----LBCC-2024-09-30 include only bill sale
and d.invc_post_date >= TO_DATE('26/04/2025 23:30:00', 'dd/mm/yyyy HH24:mi:ss')
and d.invc_post_date <= TO_DATE('27/04/2025 12:00:00', 'dd/mm/yyyy HH24:mi:ss')
and d.sbs_no = 1
group by d.sid,st.udf1_string, dd.disc_reason, c.udf2_string
) group by sid, udf1_string, disc_reason, cust_type
having udf1_string = 1 )) promotion
on invoice.sid = promotion.sid_disc)
LEFT JOIN
(select d.sid sid_lty, sum(nvl(d.lty_redeem_amt,0)) lty_redeem_amt
from rps.document d
--rps.document_item di,
join rps.store st on st.sid = d.store_sid
left join rps.customer c on d.bt_cuid = c.sid
where
d.doc_no >0 and d.is_held = 0
and d.order_qty = 0
and d.return_qty = 0
and d.sold_qty > 0
and d.invc_post_date >= TO_DATE('26/04/2025 23:30:00', 'dd/mm/yyyy HH24:mi:ss')
and d.invc_post_date <= TO_DATE('27/04/2025 12:00:00', 'dd/mm/yyyy HH24:mi:ss')
and d.sbs_no = 1
group by st.udf1_string, c.udf2_string, d.sid
having sum(nvl(d.lty_redeem_amt,0)) <>0 and st.udf1_string = 1
) lty ON invoice.sid = lty.sid_lty
)
)
order by gap desc