1
|
SELECT * FROM
|
2
|
(
|
3
|
select h.invoice_id, h.invoice_no, h.invoice_balance AS header from txn_invoice_history_header h
|
4
|
inner join txn_invoice_history_detail d ON d.invoice_id = h.invoice_id
|
5
|
WHERE h.invoice_status like '%Approve%'
|
6
|
GROUP BY h.invoice_id, h.invoice_no, h.invoice_balance
|
7
|
) x
|
8
|
INNER JOIN
|
9
|
(
|
10
|
select d.invoice_id, SUM(d.line_total) as line_total, cast(sum(ROUND(web_price_before_discount * total_uom3_qty, 2)) as decimal(18,2)) as line_total_before_discount, cast(sum(ROUND(mprice * total_uom3_qty, 2)) as decimal(18,2)) as line_total_after_discount from txn_invoice_history_header h
|
11
|
inner join txn_invoice_history_detail d ON d.invoice_id = h.invoice_id
|
12
|
WHERE h.invoice_status like '%Approve%'
|
13
|
GROUP BY d.invoice_id
|
14
|
) y ON x.invoice_id = y.invoice_id
|
15
|
WHERE header <> line_total
|
16
|
|
17
|
|
18
|
|
19
|
select * from _txn_invoice_history_header_20240601_SY_header_detail_not_tally
|
20
|
|
21
|
select currency_value, invoice_balance, detail_total from txn_invoice_history_header ih
|
22
|
inner join (select invoice_id, SUM(line_total) as detail_total from txn_invoice_history_detail group by invoice_id) id on ih.invoice_id = id.invoice_id
|
23
|
where invoice_status like '%Approve%'
|
24
|
and invoice_balance <> detail_total
|
25
|
|
26
|
|
27
|
inner join (select invoice_id, SUM(line_total) as detail_total from txn_invoice_history_detail group by invoice_id) id on ih.invoice_id = id.invoice_id
|
28
|
where invoice_status like '%Approve%' and CONVERT(date, invoice_date) >= '2024-05-03' AND CONVERT(date, invoice_date) <= '2024-05-30' and invoice_balance <> detail_total
|
29
|
|
30
|
select * from txn_invoice_history_header where updated_by = 'SHAN YOU'
|
31
|
|
32
|
select order_web_net_amount, detail_total from txn_sales_order_header ih
|
33
|
inner join (select ord_hdr_id, SUM(web_product_net_amount) as detail_total from txn_sales_order_details group by ord_hdr_id) id on ih.ord_hdr_id = id.ord_hdr_id
|
34
|
where status like '%approve%'
|