|
1
|
declare
|
|
2
|
@start_date datetime,
|
|
3
|
@end_date datetime,
|
|
4
|
@sales_type varchar(max),
|
|
5
|
@item_id varchar(max),
|
|
6
|
@tenant_id varchar(50),
|
|
7
|
@salesman varchar(max),
|
|
8
|
@branch varchar(max),
|
|
9
|
@region varchar(max),
|
|
10
|
@channel varchar(max)
|
|
11
|
|
|
12
|
select
|
|
13
|
@start_date='2024-02-01 00:00:00',
|
|
14
|
@end_date='2024-02-26 00:00:00',
|
|
15
|
@sales_type=N'Cash Outlet,Credit Outlet',
|
|
16
|
@item_id=N'e02a3d5f-4ba3-460a-8d15-7c4b6c72ae52,B59D4760CA6C4BB281C398FB6DEB2B4E,6C3018993A1C42819587CB8EE32A7DEB,21FF5E9012B342EC9657569987ACDE74,A3111C696D5743CB94F60B9D7AF03453,79c8e25d-793e-4a41-b5a9-1f1fba9bdc9b,24ddd3bd-2232-4b25-9920-9424db8c8aaa,b0cf3eae-d3e0-4f71-9bf2-7c701b1ff2a7,f6730f64-228e-48b2-88b1-c79c222d1424,f5ad6a67-d4cd-4dda-b1de-1ba882201461,202f7533-0ba1-404e-94dd-1c7517360fcc,c7c29290-1f2a-4d74-9bd6-60277f214937,3c123143-83d6-4f79-a0a6-e7a5ecf7c9c5,9aafc093-518f-43f3-b19e-1ecc6f142e24,15a5d436-7e1d-4644-bb68-13147b02f0da,3717bb53-5a84-4a49-bc0c-76f8a49876e0,b53e692e-009c-470b-9628-7ce3cb3a71f0,b82b3d02-bf86-418e-b086-9195e315d01d,3892b082-042a-4772-aedf-bac1045a55c5,995423e4-5016-428a-80f4-76e29f19d40c,b8088e1d-60cd-4d09-8733-f01ff3d98511,beaba696-7d11-4ccf-91dc-0dec079b086c,5d62cfaa-7a06-49f6-ab98-e449fcdeea82,79d2ac58-f674-459e-898b-d33b25c1762b,e0e23881-3abd-4de5-9e1d-d7f82f93b0f1,6813e7a8-40f3-4bd5-8ff0-61d103c4f08d,402120D582E44D5E9D84CE413D29EF79,920c3403-8498-4e70-85e2-bc97fadd4db7,10c19d6e-d962-4a54-932e-114207ce5ae5,b1591a98-81d0-4855-a76b-30ce706e0e4e,aa3ce849-864a-4480-889c-386d18393608,d7e85a46-4ba3-4b10-b57d-adacac9ce2ae,7892bf73-b31d-431d-87af-65940ccfd57f,d3010a66-0c17-4ea9-8c07-32b0b60024b4,0cd74f11-5134-40ae-83b0-657811975c11,8d797878-dd62-40d4-9d82-e1cf2f3fdffe,80890fbc-8fd7-4121-a19f-3a44e255525c,022F4D8A4DD44621A4CEF142717116AD,20aa97ce-2615-4403-8174-63a1e7f8bc38,1b43fbfe-2bca-46a5-800b-41f0ebaf10e5,093799b8-3d28-4339-8205-8fb7d4a0cef6,9594c88d-2599-4a99-81fc-2f3848042890,cfb0119a-efa6-43b2-a09e-33b10d7f47f2,a4a3d1de-bd0a-43d4-b381-bff211332174,d0fd26f5-2e81-40fb-bcad-6419a9f2d41f,d129b8f9-a515-462a-9ce3-be20df87ff45,f16a7759-a602-42aa-9ac6-e27110245130,a07f1074-faea-4b18-9b61-4e44d38021b7',
|
|
17
|
@tenant_id=N'A000003',
|
|
18
|
@salesman=N'8949db2c1b2c4d27a69d14893d09a47a,A000003-admin,C833C056-0C6F-4807-BAEA-95DDE8896E59,2221C2DD-BDB4-4F06-A66D-A33E5D4C9178,3BF827F2-A389-4D52-B66C-0E8B32F55103,A4BC94F4-B024-48A0-A903-0F289CAEB224,4C157E7C-3AFB-4BBE-9931-2EF40BD9C470,0E40E164-1372-433B-8227-60C44DBAD7A0,2D5D6445-C68E-48DE-A6BF-0CE2A9C9C982,E3A1FE2A-CE95-45D0-ADB1-FFF5EA3FA973,DA6475F6-257E-4BC3-A292-CC4002EBE50C,80260F10-3641-4A33-8AD8-D78802AF5E3F,9AAF8D5E-0C1B-423E-9878-E3236B5726CB,9C991F6E-66BE-49A2-B0D5-F9C64E19590F,536A6E70-D246-4FBF-AF89-2387BA3ED808,BC9752FB-CE69-40D1-838D-CF18B2600498,F2C01F78-60F4-41FB-9FA0-6F8A0845EDA0,ECD07AEB-D7FF-4A18-90A0-A12A3F8B3824,2A957D60-AC26-410F-BBC5-03405C7B5BF5,0E854A27-F2AC-4331-90BA-140A1177119A,23947927-9B66-4A47-ABA2-236DC781B790,620fba2c1362403aa7b0dd41a93b17de,EA51A421-59D0-49C9-8C42-785AB05F102C,68A6FA5B-21B5-48E0-9211-590D0D4B1EF3,2A592DC2-1B07-4B77-928C-9BB46D34B7CE,ECEDDA2C-2337-431D-91DE-77B1C334EAA4,6ED67647-44F7-46DC-AEFD-4C803C75D6C3,e25e5d3c3f894820a884d85d67345e0f,2F3A4D62-554B-48D7-86DA-2901FA880895,64FD9FC7-496A-4FF0-B98C-BA350BD92AE7,00FD7280-0EBA-4559-8327-197756D10F66,82384340-A664-47E1-97A1-EEA0F6691F25,D02649FE-5FD5-4988-BAC0-263665876528,e23c5396e74349efa90424856e5d8904,38BCB24A-DEAF-4A8E-8CAF-75EB45E5D7EA,9D74826B-9023-4349-BDAB-08228045C088,595508EF-9372-46A2-9175-13087E442FC9,42ED5D84-AF1F-4859-B792-165AED114C35,3CF59B15-DC20-4398-BCF0-3D0CF7350F23,8123D517-5714-47AD-B9DA-35472020BC96,B6A3443C-5718-4E5E-9152-0B1BAC227EE2,DCF66768-922D-4A21-96F0-E2E5C5C8CC01,421A6E00-C93D-4D86-B66D-6B5118149C8E,3BD74B37-B5E4-42AD-972C-A8816560F41D,5E485267-6041-4360-8AE9-8F805F033495,7B291602-BE1B-425E-B332-3BBCECD1E809,A3FF16E2-EEFC-46A1-967E-61C4D799EEAB,F7467CEA-63B6-4DD2-8463-91DA1DBC116F,083758C2-A88F-4CB3-90F9-37ABA89CB82C,F955579D-AA63-45F0-991D-8ECC96CA1CE5,5665C89F-CAA9-4463-8CC9-2ADDC7FE4A8B,1E78BB9C-6A26-4722-A225-206CA84A0A77,0DC382EF-6042-47E1-A990-D509741CB691,84F4EE3B-AE54-4FB0-BE89-902B02ABF161,FA94B7FD-3D30-4A39-B3EA-C067E38E2551,E4E0BC74-450B-430A-8E63-430669A1F623,A6638C6C-912A-4882-BB8E-280AA3E89F6E,21551170-F7DF-4517-9770-9B62E2A2D62F,44C2C51C-6A50-47BF-A088-306DE52E1234,4ECF9E99-91A2-43F3-BD35-FAE9F1419B4E,1039B5A3-E711-408A-A05F-868AF45F272F,BDEB46EA-5E99-42F6-BCAF-70FF41383215,27DF6EBE-3509-42F4-AACD-77FC700C7846,5BF1EC30-B7E2-4121-A807-3E275E45C3BF,6E1C18E0-2715-4D94-95F7-CAEB686F549E,D78C299D-984D-424B-92CF-95EF4BE2F15B,C61C3EDB-DB36-43CB-97AD-A81B7B43CE51,DA985559-F296-4EC2-9075-52B54E0497C6,F605A4B0-9D5E-46C8-A647-326EA8922C37,09BE8CB2-C26F-458D-B586-952AFBC80173,E1EF072A-5CC1-4C9E-9D73-BEB2002A1CDF,C8760099-A2C2-48AD-A7C1-9872645E1501,4DBC3AB4-4BC5-4617-8570-F310206A4E5B,E08CE99A-B230-45F4-952F-5F33DFCC21C8,1BE59CA7-A528-4DE4-8FF7-0EB5875488DD,85475EEB-18CD-4468-9B37-8689753280C1,9DD23979-EE15-49AA-9E47-9B6A2D324394,5DD7A882-56C3-46E0-A9F9-4B1BDB424941,13A26606-6F1A-4A4A-B236-A34E43368854,FC93CCD0-A8A7-45A4-99B5-DD663BC84C25,887F2022-E97C-4EB9-BACF-8777AEED8A17,2518ED5B-0A6B-4DD8-B1C4-911E3B3A02F9,3F2BF26B-CABE-49D6-8505-FD12DB0012F6,F1A084C6-6D2D-4060-B754-360B176C5163,6E693B7A-303F-4BCF-A6B0-2FC90B14BF78,a1a9b9f9e04e4ba99677048e60097244,824038C2-AA93-4E79-9DF4-440D8CACAF29,29E3F065-5DF3-4666-8813-055E5C9B1CE0,FF28F78E-F52F-4CB2-AEEA-308340845D49,980939C9-E5DE-47E3-BF20-66DD116B7EFC,201B47D4-2E1B-4B7B-9A96-CA17E529B65D,C929477C-93EA-4833-B59C-11A1BBC20DAD,7803E2FE-05A6-48AE-BACD-220C1318CE58,B1FAD843-E594-4198-BE04-742F94458DD8,C6018F65-475C-4668-A443-0443EA3EC101,F2EBCABF-1883-492B-95C8-1587C9308398,3CF77CA9-7890-4833-B88C-D1F31DEBA723,36AB8D89-0DBB-4142-A8C4-436183A1B0D8,9557C1BD-1713-41A8-A3D3-B82C4ABE4DF5,66037AD5-198A-4230-9C8E-95854ABD4F4E,64BA7741-D802-4F7B-88A3-205F5762EA1D,747735D8-D79D-4987-AA4D-C02B4B715B12,3D16BF23-CB9B-4CB7-B4BB-DEE929101BDA,84E9B138-ECE9-45B8-BC1B-EE4E227C7DE0,16A256C5-9886-458E-890C-8CC74033452A,DDCE6C1A-72E6-4DA5-B9A6-D8147FBAD483,708DEE0C-8D8B-40C4-B13A-E039BAC6B224,813927C5-178F-428D-8365-6841AF465B01,EFE8D604-74BB-406F-8A09-ED92FA59549B,7279466A-BDD9-4189-A607-C93547F89EF6,24DCC8C3-EE18-455B-9FFC-26A6AC6E9EBE,F8E4A55B-070B-4AE5-BF73-6AABFD7623F1,5e237240fc87420dbcd89fdaed40ffbb,0750377A-69A4-4C55-9043-CED6DC88BC8D,ACA4DD6D-2DD2-404A-8C43-84FAF776E88D,9481EB21-A1F4-4F5C-80B2-9C5B49FCFA24,7D8EE357-0951-4736-99C2-AA2BC076D770,EA312294-F848-46C7-94C0-794964FC33E4,C9EAB508-2EDC-414B-A1FB-1A5D6DDBE85F,B24C2D48-7139-4331-8B67-0EBEACEF33D2,643D68E5-43FE-4D35-912F-4D8C6B4BEEA7,F7AAAA1D-058F-4FD2-AC13-11B3FE41B692,D2939FAC-EE3C-430F-81C7-9BC82CFBA561,216E97FF-59BC-4E2B-ABF1-E7CF5FB90F16,1EA2FA93-89EA-45CC-B187-7577F42A895F,DB684936-2289-4B90-ADC5-0DE0C281F7D9,b0b05f7bda7345809f2491b3ba7aca0b,8578AB2E-D4BF-43E3-B95C-A3E29043042A,394edf617cb14fdeaca257290692494f,27880270-65E3-45C5-8FB0-2738EEC24572,5FEADCE3-3347-4237-999F-70C50A6FFABB,41A9178A-2303-4AC7-B0DE-686D5822E640,C86F03A8-9D9B-4772-B5AB-A63EABB25C03,f88d95d953794817b018ac3c0dff0564,7d64bbad504e4ca498cb2ef9dc18ba73,6E586A1E-62A3-433F-921A-191D65E5ED15,3720102A-B0A0-4AD6-AE80-469C1227DF34,224D083F-76BA-49E8-A70B-0F1C56F67E78,463F5897-AE00-4F15-B89B-8CD87A189021,4064E897-99E0-49A1-922B-C7A66D342D94,6A00BFD2-0125-47AA-BA3F-283E4BA0AD9D,08C3AAA3-55DD-44E9-AA72-CB05F90A8359,A4D7D171-B462-42F9-B240-0CC5B496C8C7,635AACA2-98BE-43A7-A3F1-81ED4378BEAD,D8AE2EEB-5770-4066-BF8F-ECF6AF6836D0,D320D101-FF44-4EFE-96B1-8B0AA36FE10B',
|
|
19
|
@branch=N'5D2C27CB-5C4C-4830-991B-FF1A07E8E763,90B3EA6E-12E4-41B0-9B99-582C4B87FE49,DCE84655-784C-490C-BDAC-4AD0E9E72DB2,3502D1DE-C8BE-4D66-86EF-162BF82E8C75,8C605309-BE36-4942-AFE2-9ED64FFF99DD,C701B8F9-E6B1-4173-B930-B729E6419609,9AC42A94-C5A4-4DF9-AC6B-267B0BEFB4B7,9A05388A-5F42-4465-8E96-11A391B6FAE9,AEBD0297-5F6E-4485-B7DF-34DF466B8AA8,A0288389-2F1F-4C3A-BC0A-8E31EB42DDF6,673F6892-1449-46CD-9743-C2E4B73E7DF6,142F3C54-A02B-4A71-8648-4C9A41E199B1,E42F3139-6968-48B6-B54B-B55F9EEC3463,F38018EC-E842-435F-9617-6353D94EF32D,24522F6B-A264-4243-9405-1F5730AC9242,DEF42CF3-AB19-4281-9D2A-7C24F22AB334,BA59752E-E13B-469B-8201-7B6A148BF1DD,EF79C9D1-88C4-4449-82A7-AFE4460BE4D7,0CE7FC9C-8D96-47CF-8073-266B6C843FEA,455E1506-0CA7-4D44-AE26-2E67DC6F4B54,ECA11890-27BD-4A2B-AFDE-BFD376D39232',
|
|
20
|
@region=N'(Empty),AA23C861526E493D9FCBF85FE941F6AB,88AF658A345E4AF78A52CE971DC98B2F,FCBD72CE-3ADA-442E-98F5-53914F406BB5,1C2FBC20C0B345388ADF756B60FB5199,7D9BE394ED344EAAB9240F44644CE7E2',
|
|
21
|
@channel=N'(Empty),DA00B52E-C4BD-45D1-8C13-1505E282764C,36BF5535-9E81-43B0-889A-5775340171D5'
|
|
22
|
|
|
23
|
|
|
24
|
create table #branch
|
|
25
|
(
|
|
26
|
branch_id varchar (50) collate DATABASE_DEFAULT
|
|
27
|
)
|
|
28
|
INSERT into #branch
|
|
29
|
select * from mcc_fn_splitMVParam(@branch,',')
|
|
30
|
|
|
31
|
create table #region
|
|
32
|
(
|
|
33
|
region_id varchar (50) collate DATABASE_DEFAULT
|
|
34
|
)
|
|
35
|
INSERT into #region
|
|
36
|
select * from mcc_fn_splitMVParam(@region,',')
|
|
37
|
INSERT into #region
|
|
38
|
select '' where exists (select 1 from #region where region_id = '(Empty)')
|
|
39
|
|
|
40
|
create table #channel
|
|
41
|
(
|
|
42
|
channel_id varchar (50) collate DATABASE_DEFAULT
|
|
43
|
)
|
|
44
|
INSERT into #channel
|
|
45
|
select * from mcc_fn_splitMVParam(@channel,',')
|
|
46
|
INSERT into #channel
|
|
47
|
select '' where exists (select 1 from #channel where channel_id = '(Empty)')
|
|
48
|
|
|
49
|
create table #salesman
|
|
50
|
(
|
|
51
|
salesman_id varchar (50) collate DATABASE_DEFAULT
|
|
52
|
)
|
|
53
|
INSERT INTO #salesman
|
|
54
|
exec sp_rpt_split_salesman_value @salesman
|
|
55
|
--select * from mcc_fn_splitMVParam(@salesman,',')
|
|
56
|
|
|
57
|
create table #sales_type
|
|
58
|
(
|
|
59
|
sales_type varchar (50) collate DATABASE_DEFAULT
|
|
60
|
)
|
|
61
|
INSERT INTO #sales_type
|
|
62
|
select * from mcc_fn_splitMVParam(@sales_type,',')
|
|
63
|
|
|
64
|
create table #item
|
|
65
|
(
|
|
66
|
item_id varchar (50) collate DATABASE_DEFAULT
|
|
67
|
)
|
|
68
|
INSERT INTO #item select * from mcc_fn_splitMVParam(@item_id,',')
|
|
69
|
|
|
70
|
BEGIN
|
|
71
|
|
|
72
|
-- Pull Invoice data
|
|
73
|
WITH Sales
|
|
74
|
AS (
|
|
75
|
SELECT b.branch_id
|
|
76
|
,b.branch_code
|
|
77
|
,h.customer_id
|
|
78
|
,c.customer_name
|
|
79
|
,h.invoice_no
|
|
80
|
,mr.region_code
|
|
81
|
,ch.channel_code
|
|
82
|
,STUFF((
|
|
83
|
SELECT ', ' + pc.product_category_code
|
|
84
|
FROM mst_chain_product_category cpc
|
|
85
|
INNER JOIN mst_product_category pc ON cpc.product_category_id = pc.product_category_id
|
|
86
|
WHERE c.chain_id = cpc.chain_id
|
|
87
|
ORDER BY pc.product_category_code
|
|
88
|
FOR XML PATH('')
|
|
89
|
,TYPE
|
|
90
|
).value('.', 'varchar(max)'), 1, 1, '') AS product_category
|
|
91
|
,convert(VARCHAR, h.invoice_date, 103) AS invoice_date
|
|
92
|
,m.member_cd
|
|
93
|
,m.first_name
|
|
94
|
,h.currency_value AS amount
|
|
95
|
,m.member_id
|
|
96
|
,c.customer_type
|
|
97
|
,'Sales' AS sales_type
|
|
98
|
,c.customer_code
|
|
99
|
,mh.txn_id
|
|
100
|
,CONVERT(date, h.invoice_date) as sorting_date
|
|
101
|
FROM txn_invoice_history_header h
|
|
102
|
INNER JOIN txn_sales_order_header sh ON sh.ord_hdr_id = h.sales_order_id
|
|
103
|
INNER JOIN txn_mobile_header mh ON mh.txn_id = sh.mobile_hdr_id
|
|
104
|
INNER JOIN customer c ON h.customer_id = c.customer_id
|
|
105
|
INNER JOIN mst_branch b ON h.branch_id = b.branch_id
|
|
106
|
INNER JOIN member m ON h.member_id = m.member_id
|
|
107
|
INNER JOIN txn_invoice_history_detail d ON h.invoice_id = d.invoice_id
|
|
108
|
LEFT JOIN mst_customer_contact_address cca ON c.customer_id = cca.customer_id
|
|
109
|
AND cca.address_type = 'Contact'
|
|
110
|
LEFT JOIN mst_region mr ON mr.region_id = cca.region_id
|
|
111
|
LEFT JOIN channel ch ON c.channel_id = ch.channel_id
|
|
112
|
WHERE EXISTS (
|
|
113
|
SELECT 1
|
|
114
|
FROM #branch tb
|
|
115
|
WHERE b.branch_id = tb.branch_id
|
|
116
|
)
|
|
117
|
AND convert(date, h.invoice_date) BETWEEN @start_date
|
|
118
|
AND @end_date
|
|
119
|
AND EXISTS (
|
|
120
|
SELECT 1
|
|
121
|
FROM #salesman ts
|
|
122
|
WHERE ts.salesman_id = m.member_id
|
|
123
|
)
|
|
124
|
AND EXISTS (
|
|
125
|
SELECT 1
|
|
126
|
FROM #sales_type tst
|
|
127
|
WHERE tst.sales_type = c.customer_type
|
|
128
|
)
|
|
129
|
AND EXISTS (
|
|
130
|
SELECT 1
|
|
131
|
FROM #item ti
|
|
132
|
WHERE ti.item_id = d.item_id
|
|
133
|
)
|
|
134
|
AND EXISTS (
|
|
135
|
SELECT 1
|
|
136
|
FROM mst_customer_contact_address ca
|
|
137
|
INNER JOIN #region r ON isnull(ca.region_id, '(Empty)') = r.region_id
|
|
138
|
WHERE ca.address_type = 'Contact'
|
|
139
|
AND c.customer_id = ca.customer_id
|
|
140
|
)
|
|
141
|
AND EXISTS (
|
|
142
|
SELECT 1
|
|
143
|
FROM #channel ch
|
|
144
|
WHERE isnull(ch.channel_id, '(Empty)') = c.channel_id
|
|
145
|
)
|
|
146
|
AND h.tenant_id = @tenant_id
|
|
147
|
AND h.invoice_status <> 'Cancelled' AND h.invoice_status <> 'Rejected'
|
|
148
|
GROUP BY mh.txn_id
|
|
149
|
,b.branch_id
|
|
150
|
,b.branch_code
|
|
151
|
,h.customer_id
|
|
152
|
,c.customer_name
|
|
153
|
,h.invoice_no
|
|
154
|
,mr.region_code
|
|
155
|
,ch.channel_code
|
|
156
|
,convert(VARCHAR, h.invoice_date, 103)
|
|
157
|
,m.member_cd
|
|
158
|
,m.first_name
|
|
159
|
,h.currency_value
|
|
160
|
,m.member_id
|
|
161
|
,c.customer_type
|
|
162
|
,c.customer_code
|
|
163
|
,c.chain_id
|
|
164
|
,CONVERT(date, h.invoice_date)
|
|
165
|
)
|
|
166
|
|
|
167
|
-- Pull Credit Note data
|
|
168
|
,SalesReturn
|
|
169
|
AS (
|
|
170
|
SELECT b.branch_id
|
|
171
|
,b.branch_code
|
|
172
|
,h.customer_id
|
|
173
|
,c.customer_name
|
|
174
|
,h.credit_note_no
|
|
175
|
,mr.region_code
|
|
176
|
,ch.channel_code
|
|
177
|
,STUFF((
|
|
178
|
SELECT ', ' + pc.product_category_code
|
|
179
|
FROM mst_chain_product_category cpc
|
|
180
|
INNER JOIN mst_product_category pc ON cpc.product_category_id = pc.product_category_id
|
|
181
|
WHERE c.chain_id = cpc.chain_id
|
|
182
|
ORDER BY pc.product_category_code
|
|
183
|
FOR XML PATH('')
|
|
184
|
,TYPE
|
|
185
|
).value('.', 'varchar(max)'), 1, 1, '') AS product_category
|
|
186
|
,convert(VARCHAR, h.credit_note_date, 103) AS return_date
|
|
187
|
,m.member_cd
|
|
188
|
,m.first_name
|
|
189
|
,h.currency_value AS amount
|
|
190
|
,m.member_id
|
|
191
|
,c.customer_type
|
|
192
|
,'Return' AS sales_type
|
|
193
|
,c.customer_code
|
|
194
|
,mh.txn_id
|
|
195
|
,CONVERT(date, h.credit_note_date) as sorting_date
|
|
196
|
FROM txn_credit_note_history_header h
|
|
197
|
INNER JOIN txn_mobile_header mh ON mh.txn_id = h.txn_id
|
|
198
|
INNER JOIN customer c ON h.customer_id = c.customer_id
|
|
199
|
INNER JOIN mst_branch b ON c.branch_id = b.branch_id
|
|
200
|
INNER JOIN member m ON h.member_id = m.member_id
|
|
201
|
INNER JOIN txn_credit_note_history_detail d ON h.credit_note_header_id = d.credit_note_id
|
|
202
|
INNER JOIN product_item i ON d.item_id = i.item_id
|
|
203
|
LEFT JOIN mst_product_uom u1 ON d.uom1_id = u1.product_uom_id
|
|
204
|
LEFT JOIN mst_product_uom u2 ON d.uom2_id = u2.product_uom_id
|
|
205
|
LEFT JOIN mst_product_uom u3 ON d.uom3_id = u3.product_uom_id
|
|
206
|
LEFT JOIN mst_condition co ON d.condition = co.condition_id
|
|
207
|
LEFT JOIN mst_reason r ON d.reason_id = r.reason_id
|
|
208
|
LEFT JOIN warehouse w ON d.warehouse_id = w.warehouse_id
|
|
209
|
LEFT JOIN mst_customer_contact_address cca ON c.customer_id = cca.customer_id
|
|
210
|
AND cca.address_type = 'Contact'
|
|
211
|
LEFT JOIN mst_region mr ON mr.region_id = cca.region_id
|
|
212
|
LEFT JOIN channel ch ON c.channel_id = ch.channel_id
|
|
213
|
WHERE EXISTS (
|
|
214
|
SELECT 1
|
|
215
|
FROM #branch tb
|
|
216
|
WHERE b.branch_id = tb.branch_id
|
|
217
|
)
|
|
218
|
AND convert(date, h.credit_note_date) BETWEEN @start_date
|
|
219
|
AND @end_date
|
|
220
|
AND EXISTS (
|
|
221
|
SELECT 1
|
|
222
|
FROM #salesman ts
|
|
223
|
WHERE ts.salesman_id = m.member_id
|
|
224
|
)
|
|
225
|
AND EXISTS (
|
|
226
|
SELECT 1
|
|
227
|
FROM #sales_type tst
|
|
228
|
WHERE tst.sales_type = c.customer_type
|
|
229
|
)
|
|
230
|
AND EXISTS (
|
|
231
|
SELECT 1
|
|
232
|
FROM #item ti
|
|
233
|
WHERE ti.item_id = d.item_id
|
|
234
|
)
|
|
235
|
AND EXISTS (
|
|
236
|
SELECT 1
|
|
237
|
FROM mst_customer_contact_address ca
|
|
238
|
INNER JOIN #region r ON isnull(ca.region_id, '(Empty)') = r.region_id
|
|
239
|
WHERE ca.address_type = 'Contact'
|
|
240
|
AND c.customer_id = ca.customer_id
|
|
241
|
)
|
|
242
|
AND EXISTS (
|
|
243
|
SELECT 1
|
|
244
|
FROM #channel ch
|
|
245
|
WHERE isnull(ch.channel_id, '(Empty)') = c.channel_id
|
|
246
|
)
|
|
247
|
AND h.tenant_id = @tenant_id
|
|
248
|
AND h.STATUS <> 'Cancelled' and h.status <> 'Rejected'
|
|
249
|
GROUP BY mh.txn_id
|
|
250
|
,b.branch_id
|
|
251
|
,b.branch_code
|
|
252
|
,h.customer_id
|
|
253
|
,c.customer_name
|
|
254
|
,h.currency_value
|
|
255
|
,h.credit_note_no
|
|
256
|
,mr.region_code
|
|
257
|
,ch.channel_code
|
|
258
|
,convert(VARCHAR, h.credit_note_date, 103)
|
|
259
|
,m.member_cd
|
|
260
|
,m.first_name
|
|
261
|
,m.member_id
|
|
262
|
,c.customer_type
|
|
263
|
,c.customer_code
|
|
264
|
,c.chain_id
|
|
265
|
,CONVERT(date, h.credit_note_date)
|
|
266
|
)
|
|
267
|
|
|
268
|
|
|
269
|
-- Join Sales data and Sales Return data
|
|
270
|
,CombinedData
|
|
271
|
AS (
|
|
272
|
SELECT *
|
|
273
|
FROM Sales
|
|
274
|
|
|
275
|
UNION ALL
|
|
276
|
|
|
277
|
SELECT *
|
|
278
|
FROM SalesReturn
|
|
279
|
)
|
|
280
|
|
|
281
|
-- pull record from CombinedData and aggregate the invoice_no that has similar general info (branch, customer, region, channel...)
|
|
282
|
select sum(total) as Total_salesVaule from (
|
|
283
|
SELECT MAX(txn_id) as txn_id
|
|
284
|
,MAX(branch_id) AS branch_id
|
|
285
|
,MAX(branch_code) AS branch_code
|
|
286
|
,MAX(customer_id) AS customer_id
|
|
287
|
,MAX(customer_name) AS customer_name
|
|
288
|
,STRING_AGG(invoice_no, '/ ')WITHIN GROUP (ORDER BY invoice_no desc) AS invoice_no
|
|
289
|
,MAX(region_code) AS region
|
|
290
|
,MAX(channel_code) AS channel
|
|
291
|
,MAX(product_category) AS product_category
|
|
292
|
,MAX(invoice_date) AS invoice_date
|
|
293
|
,MAX(sorting_date) AS sorting_date
|
|
294
|
,MAX(member_cd) AS member_cd
|
|
295
|
,MAX(first_name) AS first_name
|
|
296
|
,MAX(member_id) AS member_id
|
|
297
|
,MAX(customer_type) AS customer_type
|
|
298
|
,sum(CASE
|
|
299
|
WHEN customer_type = 'Cash Outlet'
|
|
300
|
AND sales_type = 'Sales'
|
|
301
|
THEN amount
|
|
302
|
ELSE 0.00
|
|
303
|
END) AS cash_sales
|
|
304
|
,sum(CASE
|
|
305
|
WHEN customer_type = 'Credit Outlet'
|
|
306
|
AND sales_type = 'Sales'
|
|
307
|
THEN amount
|
|
308
|
ELSE 0.00
|
|
309
|
END) AS credit_sales
|
|
310
|
,sum(CASE
|
|
311
|
WHEN sales_type = 'Return'
|
|
312
|
THEN amount
|
|
313
|
ELSE 0.00
|
|
314
|
END) * - 1 AS return_sales
|
|
315
|
,sum(CASE
|
|
316
|
WHEN customer_type = 'Cash Outlet'
|
|
317
|
AND sales_type = 'Sales'
|
|
318
|
THEN amount
|
|
319
|
WHEN customer_type = 'Credit Outlet'
|
|
320
|
AND sales_type = 'Sales'
|
|
321
|
THEN amount
|
|
322
|
ELSE 0.00
|
|
323
|
END) - sum(CASE
|
|
324
|
WHEN sales_type = 'Return'
|
|
325
|
THEN amount
|
|
326
|
ELSE 0.00
|
|
327
|
END) AS total
|
|
328
|
,customer_code
|
|
329
|
FROM CombinedData
|
|
330
|
GROUP BY txn_id
|
|
331
|
,branch_id
|
|
332
|
,branch_code
|
|
333
|
,customer_id
|
|
334
|
,customer_name
|
|
335
|
,region_code
|
|
336
|
,channel_code
|
|
337
|
,product_category
|
|
338
|
,invoice_date
|
|
339
|
,member_cd
|
|
340
|
,first_name
|
|
341
|
,member_id
|
|
342
|
,customer_type
|
|
343
|
,customer_code
|
|
344
|
,sorting_date
|
|
345
|
--ORDER BY sorting_date
|
|
346
|
-- ,invoice_date
|
|
347
|
-- ,branch_code
|
|
348
|
-- ,region_code
|
|
349
|
-- ,member_cd
|
|
350
|
-- ,txn_id
|
|
351
|
-- ,first_name
|
|
352
|
-- ,customer_code
|
|
353
|
-- ,customer_name
|
|
354
|
-- ,channel_code
|
|
355
|
-- ,customer_type
|
|
356
|
|
|
357
|
)x
|
|
358
|
|
|
359
|
END
|
|
360
|
|
|
361
|
|
|
362
|
--drop table #region
|
|
363
|
--drop table #channel
|
|
364
|
|
|
365
|
|