1
|
SELECT
|
2
|
m.member_cd,
|
3
|
cus.customer_code,
|
4
|
cus.customer_name,
|
5
|
ih.invoice_no,
|
6
|
sls.order_no,
|
7
|
m.member_cd as 'Sales Order Created by',
|
8
|
sls.created_date as 'Sales Order Created date',
|
9
|
ih.invoice_balance,
|
10
|
p.item_name,
|
11
|
p.item_code,
|
12
|
ih.invoice_status,
|
13
|
ih.created_date as 'Invoice Created date',
|
14
|
ih.updated_date as 'Invoice updated date',
|
15
|
m.member_cd as 'Invoice Updated by',
|
16
|
m.member_cd as 'Invoice Created by',
|
17
|
ih.invoice_status
|
18
|
|
19
|
|
20
|
FROM
|
21
|
txn_invoice_history_header ih
|
22
|
INNER JOIN
|
23
|
customer cus ON cus.customer_id = ih.customer_id
|
24
|
INNER JOIN
|
25
|
txn_invoice_history_detail b ON ih.invoice_id = b.invoice_id
|
26
|
INNER JOIN
|
27
|
txn_sales_order_header sls ON ih.sales_order_id = sls.ord_hdr_id
|
28
|
|
29
|
INNER JOIN
|
30
|
product_item p ON b.item_id = p.item_id
|
31
|
|
32
|
inner join member m on ih.member_id= m.member_id
|
33
|
|
34
|
WHERE
|
35
|
ih.invoice_no LIKE 'INWWV000001%'
|
36
|
OR ih.invoice_no LIKE 'INWWV000003%'
|
37
|
OR ih.invoice_no LIKE 'INWWV000004%'
|
38
|
OR ih.invoice_no LIKE 'INWWV000005%'
|
39
|
ORDER BY
|
40
|
ih.invoice_no DESC;
|
41
|
|
42
|
|
43
|
-----------------------------------
|
44
|
|
45
|
Collection
|
46
|
|
47
|
|
48
|
SELECT m.member_cd, c.customer_code, c.customer_name, ch.col_no,hh.invoice_no , hh.invoice_date, hh.invoice_status, cd.web_paid,cd.mobile_paid, ch.total_col_amt,ch.total_cn_amt
|
49
|
FROM txn_mobile_collection_header ch
|
50
|
inner join txn_mobile_collection_detail cd on ch.col_id= cd.col_id
|
51
|
inner join txn_mobile_collection_invoice ci on cd.col_id= ci.col_id
|
52
|
inner join txn_invoice_history_header hh on ci.inv_id= hh.invoice_id
|
53
|
inner join customer c on hh.customer_id= c.customer_id
|
54
|
inner join member m on hh.member_id= m.member_id
|
55
|
WHERE ch.col_no in (
|
56
|
'CCWWV000004-240130',
|
57
|
'CCWWV000003-240130',
|
58
|
'CCWWV000002-231214',
|
59
|
'CCWWV000001-231213'
|
60
|
)
|